Query to Extract Open AP Invoices in Oracle APPS

Headers Data Extraction Query:

-----------------------------------------

SELECT --hou.name operating_unit

     (SELECT name from hr_operating_units where organization_id = 12345) operating_unit

     ,aps.vendor_name

     ,aps.segment1 vendor_num

     ,apss.vendor_site_code

     ,aia.invoice_id

     ,aia.invoice_num      

     ,aia.invoice_currency_code 

     ,aia.payment_currency_code

     ,to_char(aia.invoice_date,'DD-MON-YYYY') invoice_date

     ,to_char(aia.gl_date,'DD-MON-YYYY') gl_date

     ,aia.source 

     ,aia.description

     ,flv.meaning invoice_type

     ,at.name term_name

     ,to_char(aia.terms_date,'DD-MON-YYYY') terms_date

     ,aia.payment_status_flag

     ,aia.exchange_rate_type

     ,to_char(aia.exchange_date,'DD-MON-YYYY') exchange_date

     ,aia.exchange_rate

     ,aia.payment_method_code payment_method

     ,aia.pay_group_lookup_code

     ,gcc.concatenated_segments liability_account

     ,aia.invoice_amount

     ,nvl(aia.invoice_amount,0) - nvl( aia.amount_paid,0) balance_amount

     ,decode(nvl(aia.payment_status_flag,'N'),'P','Partially Paid','N','Not Paid','Y','Fully Paid') payment_status

     ,decode(nvl(ap_invoices_pkg.get_posting_status(aia.invoice_id),'N'),'P','Partially Accounted','N','Not Accounted','Y','Fully Accounted') accounting_status

     ,(select distinct pha.segment1

       from po_distributions_all pda,

            po_headers_all pha

           ,ap_invoice_distributions_all aid

      where pda.po_distribution_id = aid.po_distribution_id 

        and pda.po_header_id = pha.po_header_id

        and aia.invoice_id = aid.invoice_id) po_number

  FROM ap_invoices_all aia

     , ap_suppliers aps

     , ap_supplier_sites_all apss

     , hr_operating_units hou

     , fnd_lookup_values flv

     , ap_terms at

     , gl_code_combinations_kfv gcc

WHERE aps.vendor_id = aia.vendor_id

  AND aia.vendor_id = apss.vendor_id

  AND aia.vendor_site_id = apss.vendor_site_id

  AND hou.organization_id = aia.org_id

  AND aia.cancelled_date is null

--  AND hou.name ='XX Operating'

  AND flv.lookup_type ='INVOICE TYPE'

  AND flv.lookup_code = aia.invoice_type_lookup_code

  AND ap_invoices_pkg.get_posting_status(invoice_id) in ('Y','P')

  AND aia.terms_id = at.term_id(+)

--  AND to_date(to_char(aia.gl_date,'DD-MON-YYYY') ,'DD-MON-YYYY') >= to_date('01-JAN-2010', 'DD-MON-YYYY')

  AND aia.accts_pay_code_combination_id = gcc.code_combination_id(+)

  AND hou.organization_id = 1

  and nvl(aia.invoice_amount,0) - nvl( aia.amount_paid,0) <> 0

--  and aia.invoice_num = 'INV_NUM_1'

  and aia.WFAPPROVAL_STATUS <> 'REJECTED'

ORDER BY aps.vendor_name,apss.vendor_site_code,aia.invoice_date

Refer AP Invoice Lines Data Extraction Query