Project OSM Detailed Report Test

WITH OSM_ORDERS_DATA AS (SELECT PA.PROJECT_ID 
                            , PA.SEGMENT1 PROJECT_NUMBER
							, PA.NAME PROJECT_NAME
							, OU.NAME ORG_NAME
							, AC.CUSTOMER_NAME 
							, PAA.AGREEMENT_NUM AGREEMENT_NUMBER 
                            , PAA.AGREEMENT_ID 
							, PPF.PROJFUNC_ALLOCATED_AMOUNT AGREEMENT_AMOUNT 
                            , (SELECT SUM(PROJFUNC_ALLOCATED_AMOUNT) FROM PA_PROJECT_FUNDINGS WHERE PA_PROJECT_FUNDINGS.PROJECT_ID=PA.PROJECT_ID) TOTAL_AGREEMENT_AMOUNT 
							, PPF.DATE_ALLOCATED AGREEMENT_CREATE_DATE 
							, PAA.ATTRIBUTE6 CUSTOMER_PO_DATE
							, NVL(PPF.PROJFUNC_EXCHANGE_RATE,1) PROJFUNC_EXCHANGE_RATE
						FROM  PA_PROJECTS_ALL PA
                            , PA_AGREEMENTS_ALL PAA
                            , PA_PROJECT_FUNDINGS PPF
                            , AR_CUSTOMERS AC
                            , HR_ALL_ORGANIZATION_UNITS_TL OU
			  WHERE PA.PROJECT_ID = PPF.PROJECT_ID
                          AND PPF.AGREEMENT_ID =PAA.AGREEMENT_ID
                          AND PAA.CUSTOMER_ID = AC.CUSTOMER_ID
                          AND PA.CARRYING_OUT_ORGANIZATION_ID = OU.ORGANIZATION_ID )
 
   SELECT   PROJECT_ID
          , PROJECT_NUMBER
		  , PROJECT_NAME
          , NVL((SELECT MANAGER_NAME FROM PA_PROJ_INFO_VIEW WHERE (PROJECT_ID = A.PROJECT_ID)), ' ') AS PROJECT_MANAGER
          , CUSTOMER_NAME
          , AGREEMENT_NUMBER
		  , AGREEMENT_AMOUNT
		  , TOTAL_AGREEMENT_AMOUNT
          , NVL((SELECT PAA.ATTRIBUTE5 FROM PA_AGREEMENTS_ALL PAA WHERE PAA.AGREEMENT_ID = A.AGREEMENT_ID),' ') AS CRM_NUMBER
          , NVL((SELECT PAA.ATTRIBUTE1 FROM PA_AGREEMENTS_ALL PAA WHERE PAA.AGREEMENT_ID = A.AGREEMENT_ID),' ') AS INCO_TERMS
		  , AGREEMENT_CREATE_DATE
		  , CUSTOMER_PO_DATE
          , (SELECT SUM(RAW_COST) FROM PA_BUDGET_VERSIONS_BASELINED_V 
                WHERE budget_type_code = 'AC'
                AND PROJECT_ID = A.PROJECT_ID
                AND BUDGET_STATUS_CODE IN ('B')
                AND CURRENT_ORIGINAL_FLAG = 'Y' 
                 ) AS PROJECT_ORIGINAL_BUDGET_COST
          , (SELECT SUM(REVENUE) FROM PA_BUDGET_VERSIONS_BASELINED_V 
                WHERE budget_type_code = 'AR'
                AND PROJECT_ID = A.PROJECT_ID
                AND BUDGET_STATUS_CODE IN ('B')
                AND CURRENT_ORIGINAL_FLAG = 'Y' 
                 ) AS PROJECT_ORIGINAL_REVENUE_AMOUNT
          FROM OSM_ORDERS_DATA A
        where 1 = 1