Query to extract Open PO's in Oracle APPS

SELECT pha.segment1 po_number

      ,pha.revision_num

      ,comments description

      ,pha.type_lookup_code po_type

      ,TO_CHAR(pha.creation_date, 'DD-MON-RRRR') order_date

      ,pv.vendor_name vendor_name

      ,pv.segment1 vendor_number

      ,pvs.vendor_site_code supplier_site

      ,pha.currency_code

      ,pha.rate_type currency_rate_type

      ,TO_CHAR(pha.rate_date, 'DD-MON-RRRR') currency_rate_date

      ,pha.rate currency_rate

  ,(SELECT SUM(unit_price * quantity) 

      FROM po_lines_all pla

     WHERE pla.po_header_id = pha.po_header_id

   AND pha.org_id = pla.org_id

       AND NVL(pla.closed_code, 'OPEN') <> 'FINALLY CLOSED'

  ) po_header_amount

  ,(SELECT SUM(aiDa.amount)

  FROM po_headers_all pha1

  ,po_distributions_all pda

  ,ap_invoice_distributions_all aida

  ,ap_invoices_all aia

WHERE 1=1

  AND pha1.po_header_id = pha.po_header_id

  AND  pha.po_header_id =  pda.po_header_id

  AND pda.po_distribution_id = aida.po_distribution_id

  AND PDA.PO_LINE_ID = lines.PO_LINE_ID

  AND pda.po_distribution_id = po_dist.po_distribution_id

  AND pda.org_id =aida.org_id

  AND aida.invoice_id = aia.invoice_id) matched_amount

  ,pap.full_name agent_name

      ,pap.employee_number buyer_number

      ,NVL (PHA.CLOSED_CODE, 'OPEN') closure_status    

      ,att.name payment_term

  ,(SELECT location_code 

  FROM hr_locations

WHERE location_id = pha.ship_to_location_id) ship_to_location    

  ,(SELECT location_code 

  FROM hr_locations

WHERE location_id = pha.bill_to_location_id) bill_to_location

      ,lines.line_num

      ,(SELECT line_type 

      FROM po_line_types 

WHERE line_type_id = lines.line_type_id) line_type

      ,(SELECT segment1 

      FROM mtl_system_items_b 

WHERE inventory_item_id = lines.item_id 

   AND organization_id = line_loc.ship_to_organization_id) item

      ,lines.item_id

      ,REPLACE(TRIM(lines.item_description), CHR(10), '')  existing_line_description

      ,lines.quantity

      ,lines.unit_meas_lookup_code uom

      ,lines.unit_price

      ,(lines.quantity * lines.unit_price) price

      ,line_loc.shipment_num

      ,(SELECT organization_code 

      FROM org_organization_definitions 

WHERE organization_id  = line_loc.ship_to_organization_id) ship_to_organization_code

      ,(SELECT location_code 

      FROM hr_locations 

WHERE location_id = line_loc.ship_to_location_id) shipment_ship_to_location

      ,line_loc.ship_to_location_id

      ,line_loc.ship_to_organization_id

      ,TO_CHAR(line_loc.need_by_date, 'DD-MON-RRRR') need_by_date

      ,TO_CHAR(line_loc.PROMISED_DATE, 'DD-MON-RRRR') PROMISED_DATE

      ,line_loc.quantity shipment_quantity

      ,line_loc.QUANTITY_RECEIVED shipment_quantity_received

      ,(SELECT meaning 

      FROM fnd_lookup_values 

         WHERE 1=1

           AND lookup_code = line_loc.match_option

           AND lookup_type LIKE '%POS%INVOICE%MATCH%' 

   AND enabled_flag = 'Y' AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE) AND NVL(end_date_active, SYSDATE)

           ) match_option

  ,line_loc.receipt_required_flag

  ,line_loc.inspection_required_flag

  ,line_loc.accrue_on_receipt_flag

  ,line_loc.receiving_routing_id

  ,line_loc.unit_meas_lookup_code

      ,po_dist.distribution_num

      ,(SELECT location_code 

      FROM hr_locations 

WHERE location_id = po_dist.deliver_to_location_id) deliver_to_location

      ,po_dist.destination_type_code

      ,po_dist.quantity_ordered

      ,po_dist.destination_subinventory

      ,(SELECT full_name 

      FROM per_all_people_f 

WHERE person_id = deliver_to_person_id AND SYSDATE BETWEEN effective_start_date AND effective_end_date) deliver_to_person_name

      ,(SELECT employee_number 

      FROM per_all_people_f 

WHERE person_id = deliver_to_person_id AND SYSDATE BETWEEN effective_start_date AND effective_end_date) deliver_to_person_number

      ,(SELECT employee_number 

      FROM per_all_people_f 

WHERE person_id = deliver_to_person_id AND SYSDATE BETWEEN effective_start_date AND effective_end_date) deliver_to_person_id

      ,code_comb.segment1   business_unit

      ,code_comb.segment2   department

      ,code_comb.segment3   account

      ,code_comb.segment4   product

      ,code_comb.segment5   project

      ,code_comb.segment6   property 

      ,code_comb.segment7   spare

      ,code_comb.concatenated_segments

  FROM apps.po_headers_all pha

  ,apps.hr_operating_units hou

  --,fnd_lookup_values po_type

  ,apps.per_all_people_f pap

  ,apps.ap_suppliers pv

  ,apps.ap_supplier_sites_all pvs

  ,apps.ap_terms_tl att

  ,po_lines_all lines

  ,po_line_locations_all line_loc

  ,po_distributions_all po_dist

  ,gl_code_combinations_kfv code_comb

 WHERE     1 = 1

   AND pha.approved_flag = 'Y'

   AND NVL (pha.closed_code, 'OPEN')= 'OPEN'

   AND NVL (pha.cancel_flag, 'N') = 'N'

   AND NVL(lines.cancel_flag, 'N') = 'N'

   AND NVL(line_loc.cancel_flag, 'N') = 'N'

   --AND pha.type_lookup_code IN ('STANDARD')

   AND pha.org_id = hou.organization_id

   --AND pha.type_lookup_code = po_type.lookup_code

   --AND po_type.lookup_type = 'POO:DOCUMENT_TYPE'

   AND pha.agent_id = pap.person_id

   AND pha.vendor_id = pv.vendor_id(+)

   AND pha.vendor_site_id = pvs.vendor_site_id(+)

   AND pha.terms_id = att.term_id(+)

   AND ATT.language = USERENV ('LANG')      

   AND pha.po_header_id = lines.po_header_id

   AND pha.org_id = lines.org_id

   AND line_loc.po_line_id = lines.po_line_id

   AND line_loc.po_header_id = pha.po_header_id

   AND po_dist.po_header_id = pha.po_header_id

   AND po_dist.po_line_id = lines.po_line_id

   AND po_dist.line_location_id = line_loc.line_location_id

   AND po_dist.org_id = line_loc.org_id

   AND code_comb.code_combination_id = po_dist.code_combination_id

   AND pha.org_id = :p_org_id

   AND SYSDATE BETWEEN effective_start_Date AND effective_end_date

   --AND pha.segment1 = '20'

ORDER BY pha.segment1, lines.line_num

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

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