Expenditure Items - linked to AP Invoices

Expenditure Items linked to AP Invoices

As some exp. items can be linked to AP invoices, it's possible to link through to the PA cost distribution lines table...

-- There is no link between exp. items table and AP invoices

-- For that you have to include PA_COST_DISTRIBUTION_LINES_ALL table

      

-- You can join to relevant tables once you've made that join

  

-- pcdla.system_reference1         vendor_id

-- pcdla.system_reference2         inv_id

-- pcdla.system_reference3         inv_distr_line_num

-- EXPENDITURE ITEMS LINKED TO AP INVOICES

  SELECT peia.expenditure_item_id trans_id

       , ppa.segment1 project

       , pt.task_number task

       , pea.expenditure_group batch

       , pet.expenditure_category expend_type

       , pet.expenditure_type exp_type

       , peia.transaction_source tx_src

       , peia.expenditure_item_date exp_date

       , peia.creation_date

       , peia.raw_cost

       , peia.quantity

       , peia.request_id

       , aia.invoice_num

       , pv.vendor_name supplier

       , peia.request_id

       , pcdla.system_reference1 vendor_id

       , pcdla.system_reference2 inv_id

       , pcdla.system_reference3 inv_distr_line_num

    FROM pa.pa_expenditure_items_all peia

       , pa.pa_cost_distribution_lines_all pcdla

       , pa.pa_expenditures_all pea

       , pa.pa_projects_all ppa

       , pa.pa_tasks pt

       , pa.pa_expenditure_types pet

       , pa.pa_expenditure_comments pec

       , applsys.fnd_user fu

       , ap.ap_invoices_all aia

       , po.po_vendors pv

       , ap.ap_invoice_distributions_all aida

   WHERE peia.expenditure_item_id = pcdla.expenditure_item_id

     AND pcdla.system_reference2 = aia.invoice_id(+)

     AND aia.invoice_id = aida.invoice_id

     AND pcdla.system_reference3 = aida.distribution_line_number

     AND pcdla.system_reference1 = pv.vendor_id

     AND peia.expenditure_id = pea.expenditure_id(+)

     AND peia.project_id = ppa.project_id(+)

     AND peia.task_id = pt.task_id(+)

     AND peia.expenditure_type = pet.expenditure_type(+)

     AND peia.expenditure_item_id = pec.expenditure_item_id(+)

     AND ppa.project_id = pt.project_id

     AND peia.created_by = fu.user_id(+)

     and aia.invoice_id = 123456

     AND ppa.segment1 = 'P123456'

--     AND pv.vendor_name LIKE 'P%'

--     AND aia.invoice_id = 123456

--     AND peia.expenditure_item_id = 123456

ORDER BY peia.transaction_source

       , peia.creation_date DESC

       , pt.task_number DESC;