To Select Prepayment Invoice Status in Oracle Payables

/* Formatted on 7/8/2014 9:02:43 AM (QP5 v5.115.810.9015) */
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';