Overview: This query is used to fetch Project Cost data, during AP invoice matching project is tagged to invoices for project costing in Oracle R12 Applications.
SELECT Operating_units, gl_date, Supplier_number, vendor_name, invoice_number, invoice_date, Po_Number, project_number, project_name, task_id, EXPENDITURE_TYPE, PROJECT_STATUS_CODE, FUNDING_SOURCE, FUNDING_SOURCE_TYPE, INVOICE_CURRENCY_CODE, AMOUNT, FUNCTIONAL_AMOUNT, INVOICE_LINE_DESCRIPTION FROM ( SELECT (select NAME from hr_operating_units where organization_id= aia.org_id) Operating_units, aia.gl_date, (select SEGMENT1 from ap_suppliers where vendor_id = aia.vendor_id)Supplier_number, (select VENDOR_NAME from ap_suppliers where vendor_id = aia.vendor_id) vendor_name, aia.invoice_num invoice_number, aia.invoice_date, (select segment1 from po_headers_all where po_header_id = AIL.PO_HEADER_ID)Po_Number, (select segment1 from pa_projects_all where project_id = (select distinct pda.project_id from po_lines_all pla, po_distributions_all pda where pla.PO_LINE_ID = pda.PO_LINE_ID and pla.po_line_id= AIL.PO_LINE_ID and rownum=1))project_number, (select name from pa_projects_all where project_id = (select distinct pda.project_id from po_lines_all pla, po_distributions_all pda where pla.PO_LINE_ID = pda.PO_LINE_ID and pla.po_line_id= AIL.PO_LINE_ID and rownum=1))project_name, (select distinct pda.TASK_ID from po_lines_all pla, po_distributions_all pda where pla.PO_LINE_ID = pda.PO_LINE_ID and pla.po_line_id= AIL.PO_LINE_ID and rownum=1) task_id, (select distinct pda.EXPENDITURE_TYPE from po_lines_all pla, po_distributions_all pda where pla.PO_LINE_ID = pda.PO_LINE_ID and pla.po_line_id= AIL.PO_LINE_ID and rownum=1) EXPENDITURE_TYPE, (select PROJECT_STATUS_CODE from pa_projects_all where project_id = (select distinct pda.project_id from po_lines_all pla, po_distributions_all pda where pla.PO_LINE_ID = pda.PO_LINE_ID and pla.po_line_id= AIL.PO_LINE_ID and rownum=1))PROJECT_STATUS_CODE, NULL FUNDING_SOURCE, NULL FUNDING_SOURCE_TYPE, AIA.INVOICE_CURRENCY_CODE, AIL.AMOUNT AMOUNT, AIL.BASE_AMOUNT FUNCTIONAL_AMOUNT, AIL.DESCRIPTION INVOICE_LINE_DESCRIPTION FROM AP_INVOICES_ALL AIA, AP_INVOICE_LINES_ALL AIL WHERE aia.invoice_id = ail.invoice_id and aia.invoice_date between :P_FROM_DATE and :P_TO_DATE --'14-MAR-2020' and ail.PROJECT_ID is not null)a where a.PROJECT_NUMBER is not null