Sales cost and procurement cost analysis SQL Sscripts

Prerequisites: 1> for the cost type of using FIFO/LIFO; 2> you must specify your own query parameters; 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 cts. trx_number "AR_Num", msi. segment1 "Item ",
RDBMS. trx_date "AR_Date", MT. inventory_item_id,
Rctl. quantity_invoiced "AR_Qty ",
Pencil. inv_layer_id "Lay_Id", pencil. layer_cost "Item_Cost ",
Rctl. quantity_invoiced * Cer. 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 RDBMS,
Ar. ra_customer_trx_lines_all rctl,
Inv. mtl_material_transactions Mt,
Inv. mtl_cst_layer_act_cost_details mcl,
Bom. cst_inv_layers pencil,
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 idc. set_of_books_id = & sob and rdbms. customer_trx_id = rctl. customer_trx_id
AND rctl. line_type = 'line'
And exists (
SELECT *
FROM ont. oe_order_headers_all Oh
WHERE ooh.org _ id = & ou_id
AND oh. 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 oh. 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 = '000000') --- Select your system elastic domain segment for the sales cost account, which may not necessarily be segment4;
AND TO_CHAR (RDBMS. trx_date, 'yyyy-mm') = '& date'
AND Mt. transaction_id = mcl. transaction_id
AND mcl. cost_element_id = 1
AND mcl. layer_id = Cer. layer_id
AND mcl. inv_layer_id = Cer. inv_layer_id
AND rctl. interface_line_attribute6 =
Mt. trx_source_line_id
AND Mt. trx_source_line_id = Mt. source_line_id
AND rctl. inventory_item_id = Mt. inventory_item_id
AND msi. organization_id = & org_id -- master item organization
AND Mt. 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 = Cer. 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