前提:
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
阅读(1600) | 评论(0) | 转发(0) |