vendor应付帐款余额的SQL.

摘自：http://blog.chinaunix.net/uid-324202-id-144844.html

vendor应付帐款余额的SQL. 

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",
         SUM (DECODE (SIGN (details."Days1" - 31),
                      1, 0,
                      DECODE (SIGN (details."Days1"-0 ),
                              1, details."Amount_Remaining"
                             )
                     )
             ) AS "Due with 30 Days"
    FROM (SELECT api.invoice_num "Invoice_Num",
                 api.invoice_date "Invoice_Date", aps.due_date "Due_Date",
                 ROUND (SYSDATE - api.invoice_date) "Days",
                 ROUND (SYSDATE - aps.due_date) "Days1",
                 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
             ) details
GROUP BY details."Vendor_Num",
         details."Vendor_Name",
         details."Invoice_Currency"
  HAVING SUM (details."Amount_Remaining") <> 0
ORDER BY 1, 3