Project Cost Query R12 - Orapoint Orapoint.com

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