AP_INVOICE_PAYMENT_QUERY

                            /* Formatted on 08-08-2011 20:05:50 (QP5 v5.115.810.9015) */
  SELECT  --SUM (aia.INVOICE_AMOUNT)
 aia.invoice_id,
    hou.name Operating_unit, pov.VENDOR_ID,pov.segment1 Vendor_Code,
    aia.INVOICE_NUM H_INVOICE_NUM,
             aia.INVOICE_TYPE_LOOKUP_CODE H_INVOICE_TYPE,
             pov.VENDOR_NAME H_VENDOR_NAME,
             sites.VENDOR_SITE_CODE H_VENDOR_SITE_CODE,
             aia.INVOICE_DATE H_INVOICE_DATE,
             aia.INVOICE_AMOUNT H_INVOICE_AMOUNT,
             aia.INVOICE_CURRENCY_CODE H_INVOICE_CURRENCY_CODE,
             aia.GL_DATE H_GL_DATE,
             term.NAME H_TERMS_CODE,
             'Appload' H_SOURCE,
             glcc.CONCATENATED_SEGMENTS H_LIB_ACCT,
             aia.GOODS_RECEIVED_DATE H_GOODS_RECEIVED_DATE,
                    AIA.DOC_SEQUENCE_ID,
                    AIA.DOC_SEQUENCE_VALUE    
    FROM   ap_invoices_all aia,
           apps.po_vendors pov,
           hr_operating_units hou,
           apps.ap_terms_tl term,
           apps.po_vendor_sites_all sites,
           apps.gl_code_combinations_kfv glcc
   WHERE       aia.vendor_id = pov.vendor_id
           AND hou.organization_id = aia.org_id
           AND aia.terms_id = term.term_id
           AND aia.vendor_site_id = sites.vendor_site_id
           AND aia.accts_pay_code_combination_id = glcc.code_combination_id
           AND hou.set_of_books_id = 5
           AND TRUNC (aia.GL_DATE) BETWEEN '01-AUG-2011' AND '13-AUG-2011'
           --         AND TRUNC (aia.INVOICE_DATE) BETWEEN '01-APR-2011' AND '30-JUN-2011'
           AND (aia.INVOICE_AMOUNT - aia.AMOUNT_PAID) > 0
           AND aia.invoice_type_lookup_code != 'PREPAYMENT'
           /* AND AIA.INVOICE_ID IN
                    (  SELECT   DISTINCT invoice_id FROM xx_invoice_id1) */
ORDER BY  aia.invoice_id, aia.INVOICE_DATE

 ===============     ******************************   ========================


 /* Formatted on 2/26/2013 11:34:38 AM (QP5 v5.114.809.3010) */
  SELECT   hou.name Operating_unit,
           aia.GL_DATE,
           cat.NAME Document_Category_Name,
           seq.NAME Document_Sequence_Name,
           -- aia.INVOICE_TYPE_LOOKUP_CODE H_INVOICE_TYPE,

           glcc.CONCATENATED_SEGMENTS H_LIB_ACCT,
           pov.VENDOR_NAME H_VENDOR_NAME,
           pov.VENDOR_ID,
           sites.VENDOR_SITE_CODE H_VENDOR_SITE_CODE,
           aia.INVOICE_DATE H_INVOICE_DATE,
           aia.INVOICE_NUM H_INVOICE_NUM,
           aia.INVOICE_CURRENCY_CODE H_INVOICE_CURRENCY_CODE,
           aia.INVOICE_AMOUNT H_INVOICE_AMOUNT,
           aia.GL_DATE H_GL_DATE,
           aia.PAYMENT_METHOD_CODE,
           aia.TERMS_DATE,
           apt.NAME Terms,
           aia.PAYMENT_METHOD_CODE,
           AP_INVOICES_PKG.GET_APPROVAL_STATUS (AIA.INVOICE_ID,
                                                AIA.INVOICE_AMOUNT,
                                                AIA.PAYMENT_STATUS_FLAG,
                                                AIA.INVOICE_TYPE_LOOKUP_CODE)
              APPROVAL_STATUS_LOOKUP_CODE,
           glcc.CONCATENATED_SEGMENTS H_LIB_ACCT,
           gcc.CONCATENATED_SEGMENTS Distribution_account,
           aia.GOODS_RECEIVED_DATE H_GOODS_RECEIVED_DATE,
           AIA.DOC_SEQUENCE_ID,
           aia.PAYMENT_CURRENCY_CODE,
           aia.po_header_id,
           DECODE (aia.payment_status_flag,
                   'Y',
                   'Paid',
                   'N',
                   'Unpaid',
                   'P',
                   'Partial Paid')
              inv_payment_status,
           AIA.DOC_SEQUENCE_VALUE
    FROM   ap_invoices_all aia,
           ap_invoice_lines_all aial,
           apps.po_vendors pov,
           po_headers_all poh,
           -- po_lines_all pol,

           -- po_releases_all prl,

           hr_operating_units hou,
           apps.ap_terms_tl term,
           apps.po_vendor_sites_all sites,
           apps.gl_code_combinations_kfv glcc,
           gl_code_combinations_kfv gcc,
           fnd_document_sequences seq,
           ap_invoice_distributions_all aid,
           FND_DOC_SEQUENCE_CATEGORIES cat,
           ap_terms apt
   WHERE       aia.vendor_id = pov.vendor_id
           AND hou.organization_id = aia.org_id
           AND aia.INVOICE_ID = aid.INVOICE_ID
           AND aia.terms_id = term.term_id
           AND aia.vendor_site_id = sites.vendor_site_id
           AND aial.INVOICE_ID = aia.INVOICE_ID
           AND aia.accts_pay_code_combination_id = glcc.code_combination_id
           AND aid.DIST_CODE_COMBINATION_ID = gcc.code_combination_id
           AND poh.po_header_id(+) = aia.po_header_id
           --   AND poh.po_header_id = pol.po_header_id(+)

           --  AND poh.po_header_id(+) = prl.po_header_id

           --  AND hou.set_of_books_id = 5

           AND seq.DOC_SEQUENCE_ID(+) = aia.DOC_SEQUENCE_ID
           AND aia.DOC_CATEGORY_CODE = cat.CODE
           AND apt.TERM_ID = aia.TERMS_ID
           AND TRUNC (aia.GL_DATE) <= '31-MAR-2011'
           --and aia.invoice_num = 'bpu test-1'

           AND hou.ORGANIZATION_ID IN ('90')                      --, '91'  --
           -- BETWEEN '01-AUG-2011' AND '13-AUG-2011'

           --         AND TRUNC (aia.INVOICE_DATE) BETWEEN '01-APR-2011' AND '30-JUN-2011'

           --           AND (aia.INVOICE_AMOUNT - aia.AMOUNT_PAID) > 0

           AND aia.invoice_type_lookup_code IN
                    ('CREDIT', 'DEBIT', 'PREPAYMENT', 'AWT', 'STANDARD') -- 1694
/* AND AIA.INVOICE_ID IN
                                                                            (  SELECT   DISTINCT invoice_id FROM xx_invoice_id1) */

ORDER BY   aia.invoice_id, aia.INVOICE_DATE