Ap_Aging_Report SQL月底结账使用

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;