SELECT api.invoice_num "Invoice_Num", api.invoice_date "Invoice_Date", aps.due_date "Due_Date", ROUND (SYSDATE - api.invoice_date) "Days", pv.segment1 "Vendor_Num", pv.vendor_name "Vendor_Name", term.NAME "Payment_Terms", api.invoice_currency_code "Invoice_Currency", api.payment_currency_code "Payment_Currency", aps.gross_amount "Invoice_Amount", aps.amount_remaining "Amount_Remaining", NVL (aps.amount_remaining, 0) * NVL (api.exchange_rate, 1) "Amount_Remaining(Base)", fu.user_name "User_Name" FROM ap.ap_payment_schedules_all aps, ap.ap_invoices_all api, po.po_vendors pv, po.po_vendor_sites_all pvs, ap.ap_terms_tl term, apps.fnd_user fu WHERE aps.org_id = '&ORG_ID' AND aps.payment_status_flag IN ('N', 'P') AND aps.invoice_id = api.invoice_id AND api.cancelled_date IS NULL AND aps.amount_remaining <> 0 AND api.vendor_id = pv.vendor_id AND api.vendor_site_id = pvs.vendor_site_id AND pv.vendor_id = pvs.vendor_id AND api.terms_id = term.term_id AND api.created_by = fu.user_id AND DECODE ((SELECT DISTINCT aid.match_status_flag FROM ap.ap_invoice_distributions_all aid WHERE api.invoice_id = aid.invoice_id(+)), 'A', 'A', 'N', 'N', 'T', 'T', 'X' ) <> 'X' ORDER BY 5; --===========修改过的AP_Aging_Report,更便于会计人员核对数据============== SELECT details."Vendor_Num", details."Vendor_Name", details."Invoice_Currency", SUM (details."Amount_Remaining") "Balances", SUM (DECODE (SIGN (details."Days" - 31), 1, 0, DECODE (SIGN (details."Days"-0 ), 1, details."Amount_Remaining" ) ) ) AS "1-30 Days", SUM (DECODE (SIGN (details."Days" - 61), 1, 0, DECODE (SIGN (details."Days" - 31 ), 1, details."Amount_Remaining" ) ) ) AS "31-60 Days", SUM (DECODE (SIGN (details."Days" - 91), 1, 0, DECODE (SIGN (details."Days" -61), 1, details."Amount_Remaining" ) ) ) AS "61-90 Days", SUM (DECODE (SIGN (details."Days" - 36500), 1, 0, DECODE (SIGN (details."Days" - 91 ), 1, details."Amount_Remaining" ) ) ) AS "Over 90 Days" FROM (SELECT api.invoice_num "Invoice_Num", api.invoice_date "Invoice_Date", aps.due_date "Due_Date", ROUND (SYSDATE - api.invoice_date) "Days", pv.segment1 "Vendor_Num", pv.vendor_name "Vendor_Name", term.NAME "Payment_Terms", api.invoice_currency_code "Invoice_Currency", api.payment_currency_code "Payment_Currency", aps.gross_amount "Invoice_Amount", aps.amount_remaining "Amount_Remaining", NVL (aps.amount_remaining, 0) * NVL (api.exchange_rate, 1) "Amount_Remaining(Base)", fu.user_name "User_Name" FROM ap.ap_payment_schedules_all aps, ap.ap_invoices_all api, po.po_vendors pv, po.po_vendor_sites_all pvs, ap.ap_terms_tl term, apps.fnd_user fu WHERE aps.org_id = '&org_id' AND aps.payment_status_flag IN ('N', 'P') AND aps.invoice_id = api.invoice_id AND api.cancelled_date IS NULL AND aps.amount_remaining <> 0 AND api.vendor_id = pv.vendor_id AND api.vendor_site_id = pvs.vendor_site_id AND pv.vendor_id = pvs.vendor_id AND api.terms_id = term.term_id AND api.created_by = fu.user_id /*AND DECODE ((SELECT DISTINCT aid.match_status_flag FROM ap.ap_invoice_distributions_all aid WHERE api.invoice_id = aid.invoice_id(+)), 'A', 'A', 'N', 'N', 'T', 'T', 'X' ) <> 'X'*/ ) details GROUP BY details."Vendor_Num", details."Vendor_Name", details."Invoice_Currency" HAVING SUM (details."Amount_Remaining") <> 0 ORDER BY 1, 3;
转载于:https://www.cnblogs.com/quanweiru/archive/2012/12/21/2827873.html