Monday, 3 October 2016

Oracle project link with AP and PO

 Payable to Projects :-

SELECT  -- 'AP' to 'PA'
           pl.description expenditure_category,
           pp.segment1,
           pei.project_burdened_cost,
           api.vendor_name TYPE,
           api.invoice_num description,
           api.description comments,
           pei.expenditure_type
           haou.name,
           pp.carrying_out_organization_id,
           pp.project_id,
           pei.system_linkage_function,
           pet.description,
           pei.quantity,
      FROM pa_projects pp,
           pa_expenditure_items pei,
           pa_expenditures pe,
           pa_expenditure_types pet,
           hr_all_organization_units haou,
           ap_invoices_v api, -
           pa_lookups pl
     WHERE 1 = 1
       AND pei.expenditure_id = pe.expenditure_id
       AND pet.expenditure_type = pei.expenditure_type
       AND haou.organization_id = pp.carrying_out_organization_id
       AND pp.project_id = pei.project_id
       AND pet.expenditure_type = pl.lookup_code



Purchase to Projects :-

SELECT  -- 'PA' to 'PO'
           pl.description expenditure_category,
           pp.segment1,
           pei.expenditure_type,
           haou.name,
           pp.carrying_out_organization_id,
           pp.project_id,
           pei.system_linkage_function,
           pet.description,
           pei.quantity,
      FROM pa_projects pp,
           pa_expenditure_items pei,
           pa_expenditures pe,
           pa_expenditure_types pet,
           hr_all_organization_units haou,
           po_distributions_all pda,
           po_headers_all pha,
           pa_lookups pl,
           po_lines_all pla
     WHERE 1 = 1
       AND pei.expenditure_id = pe.expenditure_id
       AND pet.expenditure_type = pei.expenditure_type
       AND haou.organization_id = pp.carrying_out_organization_id
       AND pp.project_id = pei.project_id
       AND pet.expenditure_type = pl.lookup_code
       AND pda.project_id = pei.project_id
       AND pda.task_id = pei.task_id
       AND pda.expenditure_type = pei.expenditure_type
       AND pda.expenditure_item_date = pei.expenditure_item_date
       AND pha.po_header_id = pda.po_header_id
       AND pla.po_header_id = pei.document_header_id
       AND pda.po_distribution_id = pei.document_line_number
       AND pla.po_header_id = pda.po_header_id
       AND pla.po_line_id = pda.po_line_id
      

No comments:

Post a Comment