PO to PA to AP Invoice

Project and PO Link in oracle apps

 

 

/* Formatted on 12/27/2013 9:10:55 AM (QP5 v5.114.809.3010) */
SELECT   DISTINCT c.po_header_id,
                  b.po_line_id,
                  c.segment1,
                  b.line_location_id,
                  b.po_distribution_id,
                  --  b.distribution_num,
                  a.unit_price,
                  (a.quantity - b.quantity_billed),
                  NULL,
                  a.item_id,
                  g.segment1 "Project Number",
                  h.task_name,
                  h.task_id,
                  b.expenditure_type,
                  b.expenditure_item_date,
                  b.project_accounting_context,
                  b.expenditure_organization_id
  --  j.shipment_num
  FROM   po_lines_all a,
         po_distributions_all b,
         po_headers_all c,
         pa_projects_all g,
         pa_tasks h,
         pa_project_statuses i,
         po_line_locations_all j
 WHERE       1 = 1
         AND c.segment1 = rec_line.po_number
         AND a.line_num = NVL (rec_line.po_line_number, a.line_num)
         AND c.po_header_id = a.po_header_id
         AND a.po_header_id = b.po_header_id
         AND a.po_line_id = b.po_line_id
         AND b.project_id = g.project_id
         AND b.task_id = h.task_id
         AND g.project_status_code = i.project_status_code
         AND j.po_line_id = a.po_line_id
         AND j.line_location_id = b.line_location_id
         AND (a.quantity - b.quantity_billed) > 0
         AND NVL (a.cancel_flag, 'N') <> 'Y'




po / project / invoice like in oracle apps

 

  SELECT pha.segment1 po

       , pv.vendor_name supplier

       , pla.line_num line_no

       , pda.distribution_num distr_no

       , pha.currency_code curr

       , pha.creation_date po_date

       , pha.approved_date po_appr_date

       , pda.quantity_ordered qty_ord

       , pda.amount_billed amt_billed

       , pda.encumbered_amount encumb_amt

       , ROUND (pda.amount_billed * pda.rate

              , 2)

            calc_amt

       , pda.rate tx_rate

       , '##### PROJECT ####'

       , ppa.segment1 project_

       , pt.task_number task_no

       , pt.task_name

       , '##### INVOICES ####'

       , aia.invoice_num invoice_number

       , aia.creation_date inv_date

       , aia.gl_date inv_gl_date      

       , aia.invoice_id inv_id

       , aida.distribution_line_number ap_distr_line_no

       , aida.line_type_lookup_code ap_distr_line_type

       , aida.period_name ad_distr_period

       , aida.amount amt

       , aida.base_amount

       , aida.description

    FROM po.po_headers_all pha

       , po.po_lines_all pla

       , po.po_distributions_all pda

       , ap.ap_invoices_all aia

       , ap.ap_invoice_distributions_all aida

       , pa.pa_projects_all ppa

       , pa.pa_tasks pt

       , apps.po_vendors pv

   WHERE pha.po_header_id = pla.po_header_id

     AND pla.po_line_id = pda.po_line_id

     AND aia.invoice_id = aida.invoice_id

     AND aida.po_distribution_id = pda.po_distribution_id

     AND ppa.project_id = pt.project_id

     AND pda.project_id = ppa.project_id

     AND pda.task_id = pt.task_id

     AND aia.vendor_id = pv.vendor_id

     AND aia.invoice_num = '98765'

     AND ppa.segment1 = 'PO_NUM9876'

ORDER BY pha.segment1

       , pla.line_num

       , pda.distribution_num

       , aida.distribution_line_number;

 

inv details without linking to anything else

 

SELECT aia.invoice_num invoice_number

     , aia.invoice_id

     , pv.vendor_name

     , aia.invoice_id inv_id

     , aida.distribution_line_number

     , aida.line_type_lookup_code

     , aida.period_name

     , aida.amount amt

     , aida.description

  FROM ap.ap_invoices_all aia

     , ap.ap_invoice_distributions_all aida

     , apps.po_vendors pv

 WHERE aia.invoice_id = aida.invoice_id

   AND aia.vendor_id = pv.vendor_id

   AND aia.invoice_num = '123456'