另外,這個僅供參考,以前寫的,沒用上,與要求有出入
/* Formatted on 2008/02/01 15:44 (Formatter Plus v4.8.8) */
SELECT DISTINCT ai.org_id org_id, ai.set_of_books_id set_of_books_id,
ai.creation_date creation_date, msi.segment1 item,
pha.segment1 po_num, rsh.receipt_num,
ai.invoice_num invoice_num, ai.approved_amount ai_amount,
pla.quantity po_quantity, pla.unit_price po_price,
ai.invoice_currency_code, ai.payment_currency_code
FROM ap_invoice_distributions_all aid,
ap_invoices_all ai,
po_headers_all pha,
po_lines_all pla,
rcv_shipment_lines rsl,
mtl_system_items_b msi,
rcv_shipment_headers rsh,
po_distributions_all pda
WHERE aid.po_distribution_id IS NOT NULL
AND aid.invoice_id = ai.invoice_id
AND aid.po_distribution_id = pda.po_distribution_id(+)
AND pda.po_header_id = pha.po_header_id
AND rsl.item_id = msi.inventory_item_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.po_header_id = pha.po_header_id(+)
AND pha.po_header_id = pla.po_header_id
AND aid.org_id =
rg_id
AND TO_CHAR (ai.creation_date, 'yymmdd') between '070101' and '070401' |