1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query to Select Prepayment Invoice Status in Oracle Payables

Discussion in 'Oracle Apps Technical' started by saalmank, Jul 20, 2010.

  1. saalmank

    saalmank Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Multan
    The following is a Query to Select Prepayment Invoice Status in Oracle Payables.

    Code (SQL):
    SELECT   pv.VENDOR_NAME,
               ai.invoice_num,
               NVL (
                  DECODE (
                     SIGN (SUM (amount - NVL (prepay_amount_remaining, amount))),
                     1,
                     DECODE (SUM (prepay_amount_remaining), 0, 'Y', NULL),
                     NULL
                  ),
                  'N'
               )
                  AS PP_F     -- Y is Fully Applied, N is Partially or Not Applied
        FROM   ap_invoice_distributions_all aid, ap_invoices_all ai, po_vendors pv
       WHERE       aid.invoice_id = ai.INVOICE_ID
               AND pv.VENDOR_ID = ai.VENDOR_ID
               AND aid.line_type_lookup_code = 'ITEM'
               AND ai.invoice_type_lookup_code = 'PREPAYMENT'
               AND ai.INVOICE_ID = :P_INVOICE_ID
               AND NVL (reversal_flag, 'N') <> 'Y'
    GROUP BY   pv.vendor_name, ai.invoice_num
      HAVING   NVL (
                  DECODE (
                     SIGN (SUM (amount - NVL (prepay_amount_remaining, amount))),
                     1,
                     DECODE (SUM (prepay_amount_remaining), 0, 'Y', NULL),
                     NULL
                  ),
                  'N'
               ) <> 'Y';
    Hope this is useful for people.