Query to Extract Open AP Invoice Lines in Oracle APPS

Lines Data Extraction Query:

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

SELECT  (SELECT name from hr_operating_units where organization_id = 1234) operating_unit

     , aps.vendor_name

     , apss.vendor_site_code

     , aia.invoice_id

     , aia.invoice_num      

     , aia.invoice_currency_code 

     , aia.payment_currency_code

     , aia.source 

     , flv.meaning invoice_type

     , aia.payment_method_lookup_code payment_method

     , aia.pay_group_lookup_code

     , aia.invoice_amount

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

     , ail.line_number

     , ail.line_type_lookup_code

     , ail.description

     , ail.line_source

     , aid.amount

     , ail.tax_rate_code

     , gcc.concatenated_segments expense_account

     , ail.assets_tracking_flag

     , (select distinct pha.segment1

        from po_distributions_all pda,

                po_headers_all pha

        where pda.po_distribution_id = aid.po_distribution_id 

           and pda.po_header_id = pha.po_header_id) po_number

         , (select distinct pla.line_num

        from po_distributions_all pda,

                po_headers_all pha,

                po_lines_all pla

        where pda.po_distribution_id = aid.po_distribution_id 

           and pla.po_line_id = pda.po_line_id

           and pda.po_header_id = pha.po_header_id) po_line_no

           ,aid.amount_includes_tax_flag

           --,aid.*

  FROM ap_invoices_all aia

     , ap_invoice_lines_all ail

     , ap_invoice_distributions_all aid

     , ap_suppliers aps

     , ap_supplier_sites_all apss

     , hr_operating_units hou

     , fnd_lookup_values flv

     , gl_code_combinations_kfv gcc

WHERE aps.vendor_id = aia.vendor_id

  AND aia.vendor_id = apss.vendor_id

  AND aia.invoice_id = ail.invoice_id

  AND ail.invoice_id = aid.invoice_id 

  AND ail.line_number= aid.invoice_line_number

  AND aia.vendor_site_id = apss.vendor_site_id

  AND hou.organization_id = aia.org_id

  AND aia.cancelled_date IS NULL

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

--  AND hou.name ='XX Operating'

  AND flv.lookup_type='INVOICE TYPE'

  AND flv.lookup_code = aia.invoice_type_lookup_code

  AND aid.dist_code_combination_id = gcc.code_combination_id(+)

  AND nvl(aid.reversal_flag ,'N') = 'N'

  AND nvl(ail.discarded_flag ,'N') = 'N'

  AND nvl(aid.amount,0) <> 0

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

AND hou.organization_id = 1

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

and aia.WFAPPROVAL_STATUS <> 'REJECTED'

ORDER BY aps.vendor_name,apss.vendor_site_code,aia.invoice_date,aia.invoice_num,ail.line_number

Refer Open AP Invoice Headers Data Extraction Query