销售成本与采购成本分析SQL Sscripts

前提:

1>针对使用FIFO/LIFO的成本类型;

2>要指定自己的查询参数;

SELECT x."AR_Num", x."Item", x."AR_Date", x."AR_Qty", x."Cost_Amount",
       x."Item_Cost", x."Lay_Id", x."Receipt_Num", y."Invoice_Num",
       y."Voucher_Num", y."Unit_Price"
  FROM (SELECT DISTINCT rct.trx_number "AR_Num", msi.segment1 "Item",
                        rct.trx_date "AR_Date", mmt.inventory_item_id,
                        rctl.quantity_invoiced "AR_Qty",
                        cil.inv_layer_id "Lay_Id", cil.layer_cost "Item_Cost",
                        rctl.quantity_invoiced * cil.layer_cost "Cost_Amount",
                        rsh.receipt_num "Receipt_Num",
                        (SELECT rt1.transaction_id
                           FROM po.rcv_transactions rt1
                          WHERE rt1.interface_transaction_id =
                                   rt.interface_transaction_id
                            AND rt1.parent_transaction_id = -1)
                                                           rcv_transaction_id
                   FROM ar.ra_customer_trx_all rct,
                        ar.ra_customer_trx_lines_all rctl,
                        inv.mtl_material_transactions mmt,
                        inv.mtl_cst_layer_act_cost_details mcl,
                        bom.cst_inv_layers cil,
                        inv.mtl_system_items_b msi,
                        po.rcv_transactions rt,
                        po.rcv_shipment_headers rsh,
                        po.rcv_shipment_lines rsl,
                        inv.mtl_material_transactions mmt1
                  WHERE rct.set_of_books_id = &sob

                    AND rct.customer_trx_id = rctl.customer_trx_id
                    AND rctl.line_type = 'LINE'
                    AND EXISTS (
                           SELECT *
                             FROM ont.oe_order_headers_all ooh
                            WHERE ooh.org_id = &ou_id
                              AND ooh.order_number = rctl.sales_order
                              AND EXISTS (
                                     SELECT 'true'
                                       FROM ont.oe_transaction_types_all ott
                                      WHERE ott.org_id = &ou_id
                                        AND ott.cost_of_goods_sold_account IS NOT NULL
                                        AND ott.end_date_active IS NULL
                                        AND ooh.order_type_id =
                                                       ott.transaction_type_id
                                        AND EXISTS (
                                               SELECT 'TRUE'
                                                 FROM gl.gl_code_combinations gcc
                                                WHERE ott.cost_of_goods_sold_account =
                                                         gcc.code_combination_id
                                                  AND gcc.segment4 = '61110')))

---此处是针对销售成本科目,选择自己系统弹性域段,可能不一定是segment4;
                    AND TO_CHAR (rct.trx_date, 'yyyy-mm') = '&date'
                    AND mmt.transaction_id = mcl.transaction_id
                    AND mcl.cost_element_id = 1
                    AND mcl.layer_id = cil.layer_id
                    AND mcl.inv_layer_id = cil.inv_layer_id
                    AND rctl.interface_line_attribute6 =
                                                        mmt.trx_source_line_id
                    AND mmt.trx_source_line_id = mmt.source_line_id
                    AND rctl.inventory_item_id = mmt.inventory_item_id
                    AND msi.organization_id = &org_id  --master item organization
                    AND mmt.inventory_item_id = msi.inventory_item_id
                    AND rt.shipment_header_id = rsh.shipment_header_id
                    AND rt.shipment_line_id = rsl.shipment_line_id
                    AND rt.transaction_id = mmt1.source_line_id
                    AND mmt1.transaction_id = cil.create_transaction_id) x,
       (SELECT ai.invoice_num "Invoice_Num",
               ai.doc_sequence_value "Voucher_Num",
               aid.unit_price "Unit_Price", aid.rcv_transaction_id
          FROM ap.ap_invoices_all ai, ap.ap_invoice_distributions_all aid
         WHERE ai.set_of_books_id = &sob

           AND ai.invoice_id = aid.invoice_id
           AND aid.line_type_lookup_code = 'ITEM') y
 WHERE x.rcv_transaction_id = y.rcv_transaction_id