Friday 27 December 2013

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'

1 comment:

  1. hi unable to find rec_line.po_number this table in first query

    ReplyDelete