预付发票未核销余额计算SQL.

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

预付发票未核销余额计算SQL. 

SELECT   details."Vendor_Num", details."Vendor_Name",
         details."Prepayment_Num", details."Prepayment_Currency",
         details."Prepayment_Amount" "Prepayment_Amount",
         SUM (details."Apply_Amount") "Apply_Amount_Sum"
    FROM (SELECT pv.segment1 "Vendor_Num", pv.vendor_name "Vendor_Name",
                 ai1.invoice_num "Prepayment_Num",
                 ai1.invoice_currency_code "Prepayment_Currency",
                 ai2.invoice_num "Invoice_Num",
                 ai2.invoice_currency_code "Invoice_Currency",
                 aid1.amount "Prepayment_Amount",
                 NVL (aid2.amount, 0) "Apply_Amount"
            FROM ap.ap_invoices_all ai1,
                 ap.ap_invoices_all ai2,
                 ap.ap_invoice_distributions_all aid1,
                 ap.ap_invoice_distributions_all aid2,
                 po.po_vendors pv
           WHERE ai1.set_of_books_id = &sob

             AND ai1.invoice_id = aid1.invoice_id
             AND ai2.invoice_id(+) = aid2.invoice_id
             AND aid1.invoice_distribution_id = aid2.prepay_distribution_id(+)
             AND TRUNC (ai1.invoice_date) > TRUNC (SYSDATE - 3650)
             AND ai1.invoice_type_lookup_code = 'PREPAYMENT'
             AND ai1.cancelled_date IS NULL
--             AND ai1.invoice_num = '071230'
             AND aid1.amount + NVL (aid2.amount, 0) <> 0
             AND aid1.amount <> NVL (aid2.amount, 0)
             AND ai1.vendor_id = pv.vendor_id) details
  HAVING (details."Prepayment_Amount" + SUM (details."Apply_Amount") <> 0)
     AND (details."Prepayment_Amount" <> SUM (details."Apply_Amount"))
GROUP BY details."Prepayment_Num",
         details."Prepayment_Currency",
         details."Prepayment_Amount",
         details."Vendor_Num",
         details."Vendor_Name"