VIEW: APPS.PJM_PROJECT_PROJ_SUMMARY_V

Source

View Text - Preformatted

SELECT P.PROJECT_ID , P.SEGMENT1 , P.NAME , P.DESCRIPTION , P.START_DATE , P.COMPLETION_DATE , P.CLOSED_DATE , PROJMGR.PERSON_ID , PROJMGR.FULL_NAME , PS.PROJECT_STATUS_NAME , P.PM_PRODUCT_CODE , P.PM_PROJECT_REFERENCE , P.CARRYING_OUT_ORGANIZATION_ID , OU1.NAME , P.PROJECT_TYPE , PT.PROJECT_TYPE_CLASS_CODE , P.ORG_ID , OU2.NAME , TO_CHAR( ROUND( S.REVENUE_PTD ) , 'FM999G999G999G999G999G990' ) , TO_CHAR( ROUND( S.BURDENED_COST_PTD ) , 'FM999G999G999G999G999G990' ) , TO_CHAR( ROUND( NVL(S.REVENUE_PTD , 0) - NVL(S.BURDENED_COST_PTD , 0) ) , 'FM999G999G999G999G999G990' ) , DECODE( NVL(S.REVENUE_PTD , 0) , 0 , 0 , ( ROUND( ( ( S.REVENUE_PTD - NVL(S.BURDENED_COST_PTD , 0) ) / S.REVENUE_PTD) * 100 ) ) ) , TO_CHAR( ROUND( S.REVENUE_YTD ) , 'FM999G999G999G999G999G990' ) , TO_CHAR( ROUND( S.BURDENED_COST_YTD ) , 'FM999G999G999G999G999G990' ) , TO_CHAR( ROUND( NVL(S.REVENUE_YTD , 0) - NVL(S.BURDENED_COST_YTD , 0) ) , 'FM999G999G999G999G999G990' ) , DECODE( NVL(S.REVENUE_YTD , 0) , 0 , 0 , ( ROUND( ( ( S.REVENUE_YTD - NVL(S.BURDENED_COST_YTD , 0) ) / S.REVENUE_YTD) * 100 ) ) ) , TO_CHAR( ROUND( S.REVENUE_ITD ) , 'FM999G999G999G999G999G990' ) , TO_CHAR( ROUND( S.BURDENED_COST_ITD ) , 'FM999G999G999G999G999G990' ) , TO_CHAR( ROUND( NVL(S.REVENUE_ITD , 0) - NVL(S.BURDENED_COST_ITD , 0) ) , 'FM999G999G999G999G999G990' ) , DECODE( NVL(S.REVENUE_ITD , 0) , 0 , 0 , ( ROUND( ( ( S.REVENUE_ITD - NVL(S.BURDENED_COST_ITD , 0) ) / S.REVENUE_ITD) *100 ) ) ) , TO_CHAR( S.BASE_REVENUE_TOT3 , 'FM999G999G999G999G999G990' ) , TO_CHAR( ROUND( DECODE( SIGN( NVL(S.BASE_REVENUE_TOT3 , 0) - NVL(S.REVENUE_ITD , 0) ) , -1 , TO_NUMBER(NULL) , ( NVL(S.BASE_REVENUE_TOT3 , 0) - NVL(S.REVENUE_ITD , 0) ) ) ) , 'FM999G999G999G999G999G990' ) , DECODE( NVL(S.BASE_REVENUE_TOT3 , 0) , 0 , TO_NUMBER(NULL) , DECODE( SIGN( NVL(S.BASE_REVENUE_TOT3 , 0) - NVL(S.REVENUE_ITD , 0)) , -1 , TO_NUMBER(NULL) , ROUND( ( ( S.BASE_REVENUE_TOT3 - NVL(S.REVENUE_ITD , 0) ) / S.BASE_REVENUE_TOT3 * 100 ) ) ) ) , TO_CHAR( ROUND( S.CMT_BURDENED_COST ) , 'FM999G999G999G999G999G990' ) , TO_CHAR( ROUND( NVL(S.BURDENED_COST_ITD , 0) + NVL(S.CMT_BURDENED_COST , 0) ) , 'FM999G999G999G999G999G990' ) , S.RAW_COST_ITD , S.RAW_COST_YTD , S.RAW_COST_PTD , S.BILLABLE_RAW_COST_ITD , S.BILLABLE_RAW_COST_YTD , S.BILLABLE_RAW_COST_PTD , S.BILLABLE_BURDENED_COST_ITD , S.BILLABLE_BURDENED_COST_YTD , S.BILLABLE_BURDENED_COST_PTD , S.LABOR_HOURS_ITD , S.LABOR_HOURS_YTD , S.LABOR_HOURS_PTD , S.BILLABLE_LABOR_HOURS_ITD , S.BILLABLE_LABOR_HOURS_YTD , S.BILLABLE_LABOR_HOURS_PTD , S.BUDGET_TYPE_CODE1 , S.BUDGET_TYPE1 , S.BASE_RAW_COST_ITD1 , S.BASE_RAW_COST_YTD1 , S.BASE_RAW_COST_PTD1 , S.BASE_RAW_COST_TOT1 , S.ORIG_RAW_COST_ITD1 , S.ORIG_RAW_COST_YTD1 , S.ORIG_RAW_COST_PTD1 , S.ORIG_RAW_COST_TOT1 , S.BASE_BURDENED_COST_ITD1 , S.BASE_BURDENED_COST_YTD1 , S.BASE_BURDENED_COST_PTD1 , S.BASE_BURDENED_COST_TOT1 , S.ORIG_BURDENED_COST_ITD1 , S.ORIG_BURDENED_COST_YTD1 , S.ORIG_BURDENED_COST_PTD1 , S.ORIG_BURDENED_COST_TOT1 , S.ORIG_LABOR_HOURS_ITD1 , S.ORIG_LABOR_HOURS_YTD1 , S.ORIG_LABOR_HOURS_PTD1 , S.ORIG_LABOR_HOURS_TOT1 , S.BASE_LABOR_HOURS_ITD1 , S.BASE_LABOR_HOURS_YTD1 , S.BASE_LABOR_HOURS_PTD1 , S.BASE_LABOR_HOURS_TOT1 , S.BUDGET_TYPE_CODE3 , S.BUDGET_TYPE3 , S.BASE_REVENUE_ITD3 , S.BASE_REVENUE_YTD3 , S.BASE_REVENUE_PTD3 , S.ORIG_REVENUE_ITD3 , S.ORIG_REVENUE_YTD3 , S.ORIG_REVENUE_PTD3 , S.ORIG_REVENUE_TOT3 , S.ORIG_LABOR_HOURS_ITD3 , S.ORIG_LABOR_HOURS_YTD3 , S.ORIG_LABOR_HOURS_PTD3 , S.ORIG_LABOR_HOURS_TOT3 , S.BASE_LABOR_HOURS_ITD3 , S.BASE_LABOR_HOURS_YTD3 , S.BASE_LABOR_HOURS_PTD3 , S.BASE_LABOR_HOURS_TOT3 , S.CMT_RAW_COST , TO_NUMBER(NULL) /* FORMULA1 */ , TO_NUMBER(NULL) /* FORMULA2 */ , TO_NUMBER(NULL) /* FORMULA3 */ , TO_NUMBER(NULL) /* FORMULA4 */ , TO_NUMBER(NULL) /* FORMULA5 */ , TO_NUMBER(NULL) /* FORMULA6 */ , TO_NUMBER(NULL) /* FORMULA7 */ , TO_NUMBER(NULL) /* FORMULA8 */ , TO_NUMBER(NULL) /* FORMULA9 */ , TO_NUMBER(NULL) /* FORMULA10 */ , TO_NUMBER(NULL) /* FORMULA11 */ , TO_NUMBER(NULL) /* FORMULA12 */ , TO_NUMBER(NULL) /* FORMULA13 */ , TO_NUMBER(NULL) /* FORMULA14 */ , TO_NUMBER(NULL) /* FORMULA15 */ , TO_NUMBER(NULL) /* FORMULA16 */ , TO_NUMBER(NULL) /* FORMULA17 */ , TO_NUMBER(NULL) /* FORMULA18 */ , TO_NUMBER(NULL) /* FORMULA19 */ , TO_NUMBER(NULL) /* FORMULA20 */ FROM PA_PROJECTS_ALL P , PA_PROJECT_TYPES_ALL PT , HR_ALL_ORGANIZATION_UNITS_TL OU1 , HR_ALL_ORGANIZATION_UNITS_TL OU2 , PA_PROJECT_STATUSES PS , ( SELECT PLAYER.PROJECT_ID , PLAYER.PERSON_ID , PEOPLE.FULL_NAME FROM PER_ALL_PEOPLE_F PEOPLE , PA_PROJECT_PLAYERS PLAYER WHERE PLAYER.PROJECT_ROLE_TYPE = 'PROJECT MANAGER' AND TRUNC(SYSDATE) BETWEEN PLAYER.START_DATE_ACTIVE AND NVL( PLAYER.END_DATE_ACTIVE , SYSDATE + 1 ) AND PEOPLE.PERSON_ID = PLAYER.PERSON_ID AND TRUNC(SYSDATE) BETWEEN PEOPLE.EFFECTIVE_START_DATE AND PEOPLE.EFFECTIVE_END_DATE ) PROJMGR , ( SELECT PROJECT_ID , SUM(RAW_COST_ITD) RAW_COST_ITD , SUM(RAW_COST_YTD) RAW_COST_YTD , SUM(RAW_COST_PTD) RAW_COST_PTD , SUM(BILLABLE_RAW_COST_ITD) BILLABLE_RAW_COST_ITD , SUM(BILLABLE_RAW_COST_YTD) BILLABLE_RAW_COST_YTD , SUM(BILLABLE_RAW_COST_PTD) BILLABLE_RAW_COST_PTD , SUM(BURDENED_COST_ITD) BURDENED_COST_ITD , SUM(BURDENED_COST_YTD) BURDENED_COST_YTD , SUM(BURDENED_COST_PTD) BURDENED_COST_PTD , SUM(BILLABLE_BURDENED_COST_ITD) BILLABLE_BURDENED_COST_ITD , SUM(BILLABLE_BURDENED_COST_YTD) BILLABLE_BURDENED_COST_YTD , SUM(BILLABLE_BURDENED_COST_PTD) BILLABLE_BURDENED_COST_PTD , SUM(REVENUE_ITD) REVENUE_ITD , SUM(REVENUE_YTD) REVENUE_YTD , SUM(REVENUE_PTD) REVENUE_PTD , SUM(LABOR_HOURS_ITD) LABOR_HOURS_ITD , SUM(LABOR_HOURS_YTD) LABOR_HOURS_YTD , SUM(LABOR_HOURS_PTD) LABOR_HOURS_PTD , SUM(BILLABLE_LABOR_HOURS_ITD) BILLABLE_LABOR_HOURS_ITD , SUM(BILLABLE_LABOR_HOURS_YTD) BILLABLE_LABOR_HOURS_YTD , SUM(BILLABLE_LABOR_HOURS_PTD) BILLABLE_LABOR_HOURS_PTD , SUM(CMT_RAW_COST) CMT_RAW_COST , SUM(CMT_BURDENED_COST) CMT_BURDENED_COST , MAX(BUDGET_TYPE_CODE1) BUDGET_TYPE_CODE1 , MAX(BUDGET_TYPE1) BUDGET_TYPE1 , SUM(BASE_RAW_COST_ITD1) BASE_RAW_COST_ITD1 , SUM(BASE_RAW_COST_YTD1) BASE_RAW_COST_YTD1 , SUM(BASE_RAW_COST_PTD1) BASE_RAW_COST_PTD1 , SUM(BASE_RAW_COST_TOT1) BASE_RAW_COST_TOT1 , SUM(ORIG_RAW_COST_ITD1) ORIG_RAW_COST_ITD1 , SUM(ORIG_RAW_COST_YTD1) ORIG_RAW_COST_YTD1 , SUM(ORIG_RAW_COST_PTD1) ORIG_RAW_COST_PTD1 , SUM(ORIG_RAW_COST_TOT1) ORIG_RAW_COST_TOT1 , SUM(BASE_BURDENED_COST_ITD1) BASE_BURDENED_COST_ITD1 , SUM(BASE_BURDENED_COST_YTD1) BASE_BURDENED_COST_YTD1 , SUM(BASE_BURDENED_COST_PTD1) BASE_BURDENED_COST_PTD1 , SUM(BASE_BURDENED_COST_TOT1) BASE_BURDENED_COST_TOT1 , SUM(ORIG_BURDENED_COST_ITD1) ORIG_BURDENED_COST_ITD1 , SUM(ORIG_BURDENED_COST_YTD1) ORIG_BURDENED_COST_YTD1 , SUM(ORIG_BURDENED_COST_PTD1) ORIG_BURDENED_COST_PTD1 , SUM(ORIG_BURDENED_COST_TOT1) ORIG_BURDENED_COST_TOT1 , SUM(ORIG_LABOR_HOURS_ITD1) ORIG_LABOR_HOURS_ITD1 , SUM(ORIG_LABOR_HOURS_YTD1) ORIG_LABOR_HOURS_YTD1 , SUM(ORIG_LABOR_HOURS_PTD1) ORIG_LABOR_HOURS_PTD1 , SUM(ORIG_LABOR_HOURS_TOT1) ORIG_LABOR_HOURS_TOT1 , SUM(BASE_LABOR_HOURS_ITD1) BASE_LABOR_HOURS_ITD1 , SUM(BASE_LABOR_HOURS_YTD1) BASE_LABOR_HOURS_YTD1 , SUM(BASE_LABOR_HOURS_PTD1) BASE_LABOR_HOURS_PTD1 , SUM(BASE_LABOR_HOURS_TOT1) BASE_LABOR_HOURS_TOT1 , MAX(BUDGET_TYPE_CODE3) BUDGET_TYPE_CODE3 , MAX(BUDGET_TYPE3) BUDGET_TYPE3 , SUM(BASE_REVENUE_ITD3) BASE_REVENUE_ITD3 , SUM(BASE_REVENUE_YTD3) BASE_REVENUE_YTD3 , SUM(BASE_REVENUE_PTD3) BASE_REVENUE_PTD3 , SUM(BASE_REVENUE_TOT3) BASE_REVENUE_TOT3 , SUM(ORIG_REVENUE_ITD3) ORIG_REVENUE_ITD3 , SUM(ORIG_REVENUE_YTD3) ORIG_REVENUE_YTD3 , SUM(ORIG_REVENUE_PTD3) ORIG_REVENUE_PTD3 , SUM(ORIG_REVENUE_TOT3) ORIG_REVENUE_TOT3 , SUM(ORIG_LABOR_HOURS_ITD3) ORIG_LABOR_HOURS_ITD3 , SUM(ORIG_LABOR_HOURS_YTD3) ORIG_LABOR_HOURS_YTD3 , SUM(ORIG_LABOR_HOURS_PTD3) ORIG_LABOR_HOURS_PTD3 , SUM(ORIG_LABOR_HOURS_TOT3) ORIG_LABOR_HOURS_TOT3 , SUM(BASE_LABOR_HOURS_ITD3) BASE_LABOR_HOURS_ITD3 , SUM(BASE_LABOR_HOURS_YTD3) BASE_LABOR_HOURS_YTD3 , SUM(BASE_LABOR_HOURS_PTD3) BASE_LABOR_HOURS_PTD3 , SUM(BASE_LABOR_HOURS_TOT3) BASE_LABOR_HOURS_TOT3 FROM ( SELECT H.PROJECT_ID , RAW_COST_ITD , RAW_COST_YTD , RAW_COST_PTD , BILLABLE_RAW_COST_ITD , BILLABLE_RAW_COST_YTD , BILLABLE_RAW_COST_PTD , BURDENED_COST_ITD , BURDENED_COST_YTD , BURDENED_COST_PTD , BILLABLE_BURDENED_COST_ITD , BILLABLE_BURDENED_COST_YTD , BILLABLE_BURDENED_COST_PTD , REVENUE_ITD , REVENUE_YTD , REVENUE_PTD , LABOR_HOURS_ITD , LABOR_HOURS_YTD , LABOR_HOURS_PTD , BILLABLE_LABOR_HOURS_ITD , BILLABLE_LABOR_HOURS_YTD , BILLABLE_LABOR_HOURS_PTD , 0 CMT_RAW_COST , 0 CMT_BURDENED_COST , NULL BUDGET_TYPE_CODE1 , NULL BUDGET_TYPE1 , 0 BASE_RAW_COST_ITD1 , 0 BASE_RAW_COST_YTD1 , 0 BASE_RAW_COST_PTD1 , 0 BASE_RAW_COST_TOT1 , 0 ORIG_RAW_COST_ITD1 , 0 ORIG_RAW_COST_YTD1 , 0 ORIG_RAW_COST_PTD1 , 0 ORIG_RAW_COST_TOT1 , 0 BASE_BURDENED_COST_ITD1 , 0 BASE_BURDENED_COST_YTD1 , 0 BASE_BURDENED_COST_PTD1 , 0 BASE_BURDENED_COST_TOT1 , 0 ORIG_BURDENED_COST_ITD1 , 0 ORIG_BURDENED_COST_YTD1 , 0 ORIG_BURDENED_COST_PTD1 , 0 ORIG_BURDENED_COST_TOT1 , 0 ORIG_LABOR_HOURS_ITD1 , 0 ORIG_LABOR_HOURS_YTD1 , 0 ORIG_LABOR_HOURS_PTD1 , 0 ORIG_LABOR_HOURS_TOT1 , 0 BASE_LABOR_HOURS_ITD1 , 0 BASE_LABOR_HOURS_YTD1 , 0 BASE_LABOR_HOURS_PTD1 , 0 BASE_LABOR_HOURS_TOT1 , NULL BUDGET_TYPE_CODE3 , NULL BUDGET_TYPE3 , 0 BASE_REVENUE_ITD3 , 0 BASE_REVENUE_YTD3 , 0 BASE_REVENUE_PTD3 , 0 BASE_REVENUE_TOT3 , 0 ORIG_REVENUE_ITD3 , 0 ORIG_REVENUE_YTD3 , 0 ORIG_REVENUE_PTD3 , 0 ORIG_REVENUE_TOT3 , 0 ORIG_LABOR_HOURS_ITD3 , 0 ORIG_LABOR_HOURS_YTD3 , 0 ORIG_LABOR_HOURS_PTD3 , 0 ORIG_LABOR_HOURS_TOT3 , 0 BASE_LABOR_HOURS_ITD3 , 0 BASE_LABOR_HOURS_YTD3 , 0 BASE_LABOR_HOURS_PTD3 , 0 BASE_LABOR_HOURS_TOT3 FROM PA_PROJECT_ACCUM_ACTUALS A , PA_PROJECTS_ALL P , PA_PROJECT_STATUS_CONTROLS PSC , PA_PROJECT_ACCUM_HEADERS H WHERE P.PROJECT_ID = H.PROJECT_ID AND PSC.PROJECT_STATUS_CODE = P.PROJECT_STATUS_CODE AND PSC.ACTION_CODE = 'STATUS_REPORTING' AND PSC.ENABLED_FLAG = 'Y' AND A.PROJECT_ACCUM_ID = H.PROJECT_ACCUM_ID AND H.RESOURCE_LIST_MEMBER_ID = 0 AND H.TASK_ID = 0 UNION ALL SELECT /* Only Budget Type 1 (Cost) */ H.PROJECT_ID , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , B.BUDGET_TYPE_CODE , T.BUDGET_TYPE , BASE_RAW_COST_ITD , BASE_RAW_COST_YTD , BASE_RAW_COST_PTD , BASE_RAW_COST_TOT , ORIG_RAW_COST_ITD , ORIG_RAW_COST_YTD , ORIG_RAW_COST_PTD , ORIG_RAW_COST_TOT , BASE_BURDENED_COST_ITD , BASE_BURDENED_COST_YTD , BASE_BURDENED_COST_PTD , BASE_BURDENED_COST_TOT , ORIG_BURDENED_COST_ITD , ORIG_BURDENED_COST_YTD , ORIG_BURDENED_COST_PTD , ORIG_BURDENED_COST_TOT , ORIG_LABOR_HOURS_ITD , ORIG_LABOR_HOURS_YTD , ORIG_LABOR_HOURS_PTD , ORIG_LABOR_HOURS_TOT , BASE_LABOR_HOURS_ITD , BASE_LABOR_HOURS_YTD , BASE_LABOR_HOURS_PTD , BASE_LABOR_HOURS_TOT , NULL , NULL , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 FROM PA_PROJECT_ACCUM_BUDGETS B , PA_PROJECTS_ALL P , PA_PROJECT_STATUS_CONTROLS PSC , PA_PROJECT_ACCUM_HEADERS H , PA_BUDGET_TYPES T WHERE B.BUDGET_TYPE_CODE = PA_RPT_UTILS.GET_RPT_BUDGET_TYPE('C' , 1) AND P.PROJECT_ID = H.PROJECT_ID AND PSC.PROJECT_STATUS_CODE = P.PROJECT_STATUS_CODE AND PSC.ACTION_CODE = 'STATUS_REPORTING' AND PSC.ENABLED_FLAG = 'Y' AND B.PROJECT_ACCUM_ID = H.PROJECT_ACCUM_ID AND B.BUDGET_TYPE_CODE = T.BUDGET_TYPE_CODE AND H.TASK_ID = 0 AND H.RESOURCE_LIST_MEMBER_ID = 0 UNION ALL SELECT /* Only Budget Type 3 (Revenue) */ H.PROJECT_ID , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , B.BUDGET_TYPE_CODE , T.BUDGET_TYPE , BASE_RAW_COST_ITD , BASE_RAW_COST_YTD , BASE_RAW_COST_PTD , BASE_RAW_COST_TOT , ORIG_RAW_COST_ITD , ORIG_RAW_COST_YTD , ORIG_RAW_COST_PTD , ORIG_RAW_COST_TOT , BASE_BURDENED_COST_ITD , BASE_BURDENED_COST_YTD , BASE_BURDENED_COST_PTD , BASE_BURDENED_COST_TOT , ORIG_BURDENED_COST_ITD , ORIG_BURDENED_COST_YTD , ORIG_BURDENED_COST_PTD , ORIG_BURDENED_COST_TOT , ORIG_LABOR_HOURS_ITD , ORIG_LABOR_HOURS_YTD , ORIG_LABOR_HOURS_PTD , ORIG_LABOR_HOURS_TOT , BASE_LABOR_HOURS_ITD , BASE_LABOR_HOURS_YTD , BASE_LABOR_HOURS_PTD , BASE_LABOR_HOURS_TOT , NULL , NULL , 0 , 0 , 0 , B.BASE_REVENUE_TOT, 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 FROM PA_PROJECT_ACCUM_BUDGETS B , PA_PROJECTS_ALL P , PA_PROJECT_STATUS_CONTROLS PSC , PA_PROJECT_ACCUM_HEADERS H , PA_BUDGET_TYPES T WHERE B.BUDGET_TYPE_CODE = PA_RPT_UTILS.GET_RPT_BUDGET_TYPE('R' , 1) AND P.PROJECT_ID = H.PROJECT_ID AND PSC.PROJECT_STATUS_CODE = P.PROJECT_STATUS_CODE AND PSC.ACTION_CODE = 'STATUS_REPORTING' AND PSC.ENABLED_FLAG = 'Y' AND B.PROJECT_ACCUM_ID = H.PROJECT_ACCUM_ID AND B.BUDGET_TYPE_CODE = T.BUDGET_TYPE_CODE AND H.TASK_ID = 0 AND H.RESOURCE_LIST_MEMBER_ID = 0 UNION ALL SELECT H.PROJECT_ID , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , CMT_RAW_COST_PTD , CMT_BURDENED_COST_PTD , NULL , NULL , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , NULL , NULL , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 FROM PA_PROJECT_ACCUM_COMMITMENTS C , PA_PROJECTS_ALL P , PA_PROJECT_STATUS_CONTROLS PSC , PA_PROJECT_ACCUM_HEADERS H WHERE P.PROJECT_ID = H.PROJECT_ID AND H.PROJECT_ACCUM_ID = C.PROJECT_ACCUM_ID AND H.TASK_ID = 0 AND H.RESOURCE_LIST_MEMBER_ID = 0 AND PSC.PROJECT_STATUS_CODE = P.PROJECT_STATUS_CODE AND PSC.ACTION_CODE = 'STATUS_REPORTING' AND PSC.ENABLED_FLAG = 'Y' UNION ALL SELECT PROJECT_ID , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , NULL , NULL , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , NULL , NULL , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 FROM PA_PROJECTS_ALL P WHERE P.TEMPLATE_FLAG = 'N' ) SB GROUP BY PROJECT_ID ) S WHERE P.PROJECT_TYPE = PT.PROJECT_TYPE AND P.PROJECT_STATUS_CODE = PS.PROJECT_STATUS_CODE AND P.CARRYING_OUT_ORGANIZATION_ID = OU1.ORGANIZATION_ID AND OU1.LANGUAGE = USERENV('LANG') AND P.ORG_ID = OU2.ORGANIZATION_ID (+) AND OU2.LANGUAGE (+) = USERENV('LANG') AND P.TEMPLATE_FLAG = 'N' AND PA_SECURITY.ALLOW_QUERY(P.PROJECT_ID) = 'Y' AND P.PROJECT_ID = S.PROJECT_ID AND PROJMGR.PROJECT_ID (+) = P.PROJECT_ID AND P.ORG_ID = PT.ORG_ID

View Text - HTML Formatted

SELECT P.PROJECT_ID
, P.SEGMENT1
, P.NAME
, P.DESCRIPTION
, P.START_DATE
, P.COMPLETION_DATE
, P.CLOSED_DATE
, PROJMGR.PERSON_ID
, PROJMGR.FULL_NAME
, PS.PROJECT_STATUS_NAME
, P.PM_PRODUCT_CODE
, P.PM_PROJECT_REFERENCE
, P.CARRYING_OUT_ORGANIZATION_ID
, OU1.NAME
, P.PROJECT_TYPE
, PT.PROJECT_TYPE_CLASS_CODE
, P.ORG_ID
, OU2.NAME
, TO_CHAR( ROUND( S.REVENUE_PTD )
, 'FM999G999G999G999G999G990' )
, TO_CHAR( ROUND( S.BURDENED_COST_PTD )
, 'FM999G999G999G999G999G990' )
, TO_CHAR( ROUND( NVL(S.REVENUE_PTD
, 0) - NVL(S.BURDENED_COST_PTD
, 0) )
, 'FM999G999G999G999G999G990' )
, DECODE( NVL(S.REVENUE_PTD
, 0)
, 0
, 0
, ( ROUND( ( ( S.REVENUE_PTD - NVL(S.BURDENED_COST_PTD
, 0) ) / S.REVENUE_PTD) * 100 ) ) )
, TO_CHAR( ROUND( S.REVENUE_YTD )
, 'FM999G999G999G999G999G990' )
, TO_CHAR( ROUND( S.BURDENED_COST_YTD )
, 'FM999G999G999G999G999G990' )
, TO_CHAR( ROUND( NVL(S.REVENUE_YTD
, 0) - NVL(S.BURDENED_COST_YTD
, 0) )
, 'FM999G999G999G999G999G990' )
, DECODE( NVL(S.REVENUE_YTD
, 0)
, 0
, 0
, ( ROUND( ( ( S.REVENUE_YTD - NVL(S.BURDENED_COST_YTD
, 0) ) / S.REVENUE_YTD) * 100 ) ) )
, TO_CHAR( ROUND( S.REVENUE_ITD )
, 'FM999G999G999G999G999G990' )
, TO_CHAR( ROUND( S.BURDENED_COST_ITD )
, 'FM999G999G999G999G999G990' )
, TO_CHAR( ROUND( NVL(S.REVENUE_ITD
, 0) - NVL(S.BURDENED_COST_ITD
, 0) )
, 'FM999G999G999G999G999G990' )
, DECODE( NVL(S.REVENUE_ITD
, 0)
, 0
, 0
, ( ROUND( ( ( S.REVENUE_ITD - NVL(S.BURDENED_COST_ITD
, 0) ) / S.REVENUE_ITD) *100 ) ) )
, TO_CHAR( S.BASE_REVENUE_TOT3
, 'FM999G999G999G999G999G990' )
, TO_CHAR( ROUND( DECODE( SIGN( NVL(S.BASE_REVENUE_TOT3
, 0) - NVL(S.REVENUE_ITD
, 0) )
, -1
, TO_NUMBER(NULL)
, ( NVL(S.BASE_REVENUE_TOT3
, 0) - NVL(S.REVENUE_ITD
, 0) ) ) )
, 'FM999G999G999G999G999G990' )
, DECODE( NVL(S.BASE_REVENUE_TOT3
, 0)
, 0
, TO_NUMBER(NULL)
, DECODE( SIGN( NVL(S.BASE_REVENUE_TOT3
, 0) - NVL(S.REVENUE_ITD
, 0))
, -1
, TO_NUMBER(NULL)
, ROUND( ( ( S.BASE_REVENUE_TOT3 - NVL(S.REVENUE_ITD
, 0) ) / S.BASE_REVENUE_TOT3 * 100 ) ) ) )
, TO_CHAR( ROUND( S.CMT_BURDENED_COST )
, 'FM999G999G999G999G999G990' )
, TO_CHAR( ROUND( NVL(S.BURDENED_COST_ITD
, 0) + NVL(S.CMT_BURDENED_COST
, 0) )
, 'FM999G999G999G999G999G990' )
, S.RAW_COST_ITD
, S.RAW_COST_YTD
, S.RAW_COST_PTD
, S.BILLABLE_RAW_COST_ITD
, S.BILLABLE_RAW_COST_YTD
, S.BILLABLE_RAW_COST_PTD
, S.BILLABLE_BURDENED_COST_ITD
, S.BILLABLE_BURDENED_COST_YTD
, S.BILLABLE_BURDENED_COST_PTD
, S.LABOR_HOURS_ITD
, S.LABOR_HOURS_YTD
, S.LABOR_HOURS_PTD
, S.BILLABLE_LABOR_HOURS_ITD
, S.BILLABLE_LABOR_HOURS_YTD
, S.BILLABLE_LABOR_HOURS_PTD
, S.BUDGET_TYPE_CODE1
, S.BUDGET_TYPE1
, S.BASE_RAW_COST_ITD1
, S.BASE_RAW_COST_YTD1
, S.BASE_RAW_COST_PTD1
, S.BASE_RAW_COST_TOT1
, S.ORIG_RAW_COST_ITD1
, S.ORIG_RAW_COST_YTD1
, S.ORIG_RAW_COST_PTD1
, S.ORIG_RAW_COST_TOT1
, S.BASE_BURDENED_COST_ITD1
, S.BASE_BURDENED_COST_YTD1
, S.BASE_BURDENED_COST_PTD1
, S.BASE_BURDENED_COST_TOT1
, S.ORIG_BURDENED_COST_ITD1
, S.ORIG_BURDENED_COST_YTD1
, S.ORIG_BURDENED_COST_PTD1
, S.ORIG_BURDENED_COST_TOT1
, S.ORIG_LABOR_HOURS_ITD1
, S.ORIG_LABOR_HOURS_YTD1
, S.ORIG_LABOR_HOURS_PTD1
, S.ORIG_LABOR_HOURS_TOT1
, S.BASE_LABOR_HOURS_ITD1
, S.BASE_LABOR_HOURS_YTD1
, S.BASE_LABOR_HOURS_PTD1
, S.BASE_LABOR_HOURS_TOT1
, S.BUDGET_TYPE_CODE3
, S.BUDGET_TYPE3
, S.BASE_REVENUE_ITD3
, S.BASE_REVENUE_YTD3
, S.BASE_REVENUE_PTD3
, S.ORIG_REVENUE_ITD3
, S.ORIG_REVENUE_YTD3
, S.ORIG_REVENUE_PTD3
, S.ORIG_REVENUE_TOT3
, S.ORIG_LABOR_HOURS_ITD3
, S.ORIG_LABOR_HOURS_YTD3
, S.ORIG_LABOR_HOURS_PTD3
, S.ORIG_LABOR_HOURS_TOT3
, S.BASE_LABOR_HOURS_ITD3
, S.BASE_LABOR_HOURS_YTD3
, S.BASE_LABOR_HOURS_PTD3
, S.BASE_LABOR_HOURS_TOT3
, S.CMT_RAW_COST
, TO_NUMBER(NULL) /* FORMULA1 */
, TO_NUMBER(NULL) /* FORMULA2 */
, TO_NUMBER(NULL) /* FORMULA3 */
, TO_NUMBER(NULL) /* FORMULA4 */
, TO_NUMBER(NULL) /* FORMULA5 */
, TO_NUMBER(NULL) /* FORMULA6 */
, TO_NUMBER(NULL) /* FORMULA7 */
, TO_NUMBER(NULL) /* FORMULA8 */
, TO_NUMBER(NULL) /* FORMULA9 */
, TO_NUMBER(NULL) /* FORMULA10 */
, TO_NUMBER(NULL) /* FORMULA11 */
, TO_NUMBER(NULL) /* FORMULA12 */
, TO_NUMBER(NULL) /* FORMULA13 */
, TO_NUMBER(NULL) /* FORMULA14 */
, TO_NUMBER(NULL) /* FORMULA15 */
, TO_NUMBER(NULL) /* FORMULA16 */
, TO_NUMBER(NULL) /* FORMULA17 */
, TO_NUMBER(NULL) /* FORMULA18 */
, TO_NUMBER(NULL) /* FORMULA19 */
, TO_NUMBER(NULL) /* FORMULA20 */
FROM PA_PROJECTS_ALL P
, PA_PROJECT_TYPES_ALL PT
, HR_ALL_ORGANIZATION_UNITS_TL OU1
, HR_ALL_ORGANIZATION_UNITS_TL OU2
, PA_PROJECT_STATUSES PS
, ( SELECT PLAYER.PROJECT_ID
, PLAYER.PERSON_ID
, PEOPLE.FULL_NAME
FROM PER_ALL_PEOPLE_F PEOPLE
, PA_PROJECT_PLAYERS PLAYER
WHERE PLAYER.PROJECT_ROLE_TYPE = 'PROJECT MANAGER'
AND TRUNC(SYSDATE) BETWEEN PLAYER.START_DATE_ACTIVE
AND NVL( PLAYER.END_DATE_ACTIVE
, SYSDATE + 1 )
AND PEOPLE.PERSON_ID = PLAYER.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PEOPLE.EFFECTIVE_START_DATE
AND PEOPLE.EFFECTIVE_END_DATE ) PROJMGR
, ( SELECT PROJECT_ID
, SUM(RAW_COST_ITD) RAW_COST_ITD
, SUM(RAW_COST_YTD) RAW_COST_YTD
, SUM(RAW_COST_PTD) RAW_COST_PTD
, SUM(BILLABLE_RAW_COST_ITD) BILLABLE_RAW_COST_ITD
, SUM(BILLABLE_RAW_COST_YTD) BILLABLE_RAW_COST_YTD
, SUM(BILLABLE_RAW_COST_PTD) BILLABLE_RAW_COST_PTD
, SUM(BURDENED_COST_ITD) BURDENED_COST_ITD
, SUM(BURDENED_COST_YTD) BURDENED_COST_YTD
, SUM(BURDENED_COST_PTD) BURDENED_COST_PTD
, SUM(BILLABLE_BURDENED_COST_ITD) BILLABLE_BURDENED_COST_ITD
, SUM(BILLABLE_BURDENED_COST_YTD) BILLABLE_BURDENED_COST_YTD
, SUM(BILLABLE_BURDENED_COST_PTD) BILLABLE_BURDENED_COST_PTD
, SUM(REVENUE_ITD) REVENUE_ITD
, SUM(REVENUE_YTD) REVENUE_YTD
, SUM(REVENUE_PTD) REVENUE_PTD
, SUM(LABOR_HOURS_ITD) LABOR_HOURS_ITD
, SUM(LABOR_HOURS_YTD) LABOR_HOURS_YTD
, SUM(LABOR_HOURS_PTD) LABOR_HOURS_PTD
, SUM(BILLABLE_LABOR_HOURS_ITD) BILLABLE_LABOR_HOURS_ITD
, SUM(BILLABLE_LABOR_HOURS_YTD) BILLABLE_LABOR_HOURS_YTD
, SUM(BILLABLE_LABOR_HOURS_PTD) BILLABLE_LABOR_HOURS_PTD
, SUM(CMT_RAW_COST) CMT_RAW_COST
, SUM(CMT_BURDENED_COST) CMT_BURDENED_COST
, MAX(BUDGET_TYPE_CODE1) BUDGET_TYPE_CODE1
, MAX(BUDGET_TYPE1) BUDGET_TYPE1
, SUM(BASE_RAW_COST_ITD1) BASE_RAW_COST_ITD1
, SUM(BASE_RAW_COST_YTD1) BASE_RAW_COST_YTD1
, SUM(BASE_RAW_COST_PTD1) BASE_RAW_COST_PTD1
, SUM(BASE_RAW_COST_TOT1) BASE_RAW_COST_TOT1
, SUM(ORIG_RAW_COST_ITD1) ORIG_RAW_COST_ITD1
, SUM(ORIG_RAW_COST_YTD1) ORIG_RAW_COST_YTD1
, SUM(ORIG_RAW_COST_PTD1) ORIG_RAW_COST_PTD1
, SUM(ORIG_RAW_COST_TOT1) ORIG_RAW_COST_TOT1
, SUM(BASE_BURDENED_COST_ITD1) BASE_BURDENED_COST_ITD1
, SUM(BASE_BURDENED_COST_YTD1) BASE_BURDENED_COST_YTD1
, SUM(BASE_BURDENED_COST_PTD1) BASE_BURDENED_COST_PTD1
, SUM(BASE_BURDENED_COST_TOT1) BASE_BURDENED_COST_TOT1
, SUM(ORIG_BURDENED_COST_ITD1) ORIG_BURDENED_COST_ITD1
, SUM(ORIG_BURDENED_COST_YTD1) ORIG_BURDENED_COST_YTD1
, SUM(ORIG_BURDENED_COST_PTD1) ORIG_BURDENED_COST_PTD1
, SUM(ORIG_BURDENED_COST_TOT1) ORIG_BURDENED_COST_TOT1
, SUM(ORIG_LABOR_HOURS_ITD1) ORIG_LABOR_HOURS_ITD1
, SUM(ORIG_LABOR_HOURS_YTD1) ORIG_LABOR_HOURS_YTD1
, SUM(ORIG_LABOR_HOURS_PTD1) ORIG_LABOR_HOURS_PTD1
, SUM(ORIG_LABOR_HOURS_TOT1) ORIG_LABOR_HOURS_TOT1
, SUM(BASE_LABOR_HOURS_ITD1) BASE_LABOR_HOURS_ITD1
, SUM(BASE_LABOR_HOURS_YTD1) BASE_LABOR_HOURS_YTD1
, SUM(BASE_LABOR_HOURS_PTD1) BASE_LABOR_HOURS_PTD1
, SUM(BASE_LABOR_HOURS_TOT1) BASE_LABOR_HOURS_TOT1
, MAX(BUDGET_TYPE_CODE3) BUDGET_TYPE_CODE3
, MAX(BUDGET_TYPE3) BUDGET_TYPE3
, SUM(BASE_REVENUE_ITD3) BASE_REVENUE_ITD3
, SUM(BASE_REVENUE_YTD3) BASE_REVENUE_YTD3
, SUM(BASE_REVENUE_PTD3) BASE_REVENUE_PTD3
, SUM(BASE_REVENUE_TOT3) BASE_REVENUE_TOT3
, SUM(ORIG_REVENUE_ITD3) ORIG_REVENUE_ITD3
, SUM(ORIG_REVENUE_YTD3) ORIG_REVENUE_YTD3
, SUM(ORIG_REVENUE_PTD3) ORIG_REVENUE_PTD3
, SUM(ORIG_REVENUE_TOT3) ORIG_REVENUE_TOT3
, SUM(ORIG_LABOR_HOURS_ITD3) ORIG_LABOR_HOURS_ITD3
, SUM(ORIG_LABOR_HOURS_YTD3) ORIG_LABOR_HOURS_YTD3
, SUM(ORIG_LABOR_HOURS_PTD3) ORIG_LABOR_HOURS_PTD3
, SUM(ORIG_LABOR_HOURS_TOT3) ORIG_LABOR_HOURS_TOT3
, SUM(BASE_LABOR_HOURS_ITD3) BASE_LABOR_HOURS_ITD3
, SUM(BASE_LABOR_HOURS_YTD3) BASE_LABOR_HOURS_YTD3
, SUM(BASE_LABOR_HOURS_PTD3) BASE_LABOR_HOURS_PTD3
, SUM(BASE_LABOR_HOURS_TOT3) BASE_LABOR_HOURS_TOT3
FROM ( SELECT H.PROJECT_ID
, RAW_COST_ITD
, RAW_COST_YTD
, RAW_COST_PTD
, BILLABLE_RAW_COST_ITD
, BILLABLE_RAW_COST_YTD
, BILLABLE_RAW_COST_PTD
, BURDENED_COST_ITD
, BURDENED_COST_YTD
, BURDENED_COST_PTD
, BILLABLE_BURDENED_COST_ITD
, BILLABLE_BURDENED_COST_YTD
, BILLABLE_BURDENED_COST_PTD
, REVENUE_ITD
, REVENUE_YTD
, REVENUE_PTD
, LABOR_HOURS_ITD
, LABOR_HOURS_YTD
, LABOR_HOURS_PTD
, BILLABLE_LABOR_HOURS_ITD
, BILLABLE_LABOR_HOURS_YTD
, BILLABLE_LABOR_HOURS_PTD
, 0 CMT_RAW_COST
, 0 CMT_BURDENED_COST
, NULL BUDGET_TYPE_CODE1
, NULL BUDGET_TYPE1
, 0 BASE_RAW_COST_ITD1
, 0 BASE_RAW_COST_YTD1
, 0 BASE_RAW_COST_PTD1
, 0 BASE_RAW_COST_TOT1
, 0 ORIG_RAW_COST_ITD1
, 0 ORIG_RAW_COST_YTD1
, 0 ORIG_RAW_COST_PTD1
, 0 ORIG_RAW_COST_TOT1
, 0 BASE_BURDENED_COST_ITD1
, 0 BASE_BURDENED_COST_YTD1
, 0 BASE_BURDENED_COST_PTD1
, 0 BASE_BURDENED_COST_TOT1
, 0 ORIG_BURDENED_COST_ITD1
, 0 ORIG_BURDENED_COST_YTD1
, 0 ORIG_BURDENED_COST_PTD1
, 0 ORIG_BURDENED_COST_TOT1
, 0 ORIG_LABOR_HOURS_ITD1
, 0 ORIG_LABOR_HOURS_YTD1
, 0 ORIG_LABOR_HOURS_PTD1
, 0 ORIG_LABOR_HOURS_TOT1
, 0 BASE_LABOR_HOURS_ITD1
, 0 BASE_LABOR_HOURS_YTD1
, 0 BASE_LABOR_HOURS_PTD1
, 0 BASE_LABOR_HOURS_TOT1
, NULL BUDGET_TYPE_CODE3
, NULL BUDGET_TYPE3
, 0 BASE_REVENUE_ITD3
, 0 BASE_REVENUE_YTD3
, 0 BASE_REVENUE_PTD3
, 0 BASE_REVENUE_TOT3
, 0 ORIG_REVENUE_ITD3
, 0 ORIG_REVENUE_YTD3
, 0 ORIG_REVENUE_PTD3
, 0 ORIG_REVENUE_TOT3
, 0 ORIG_LABOR_HOURS_ITD3
, 0 ORIG_LABOR_HOURS_YTD3
, 0 ORIG_LABOR_HOURS_PTD3
, 0 ORIG_LABOR_HOURS_TOT3
, 0 BASE_LABOR_HOURS_ITD3
, 0 BASE_LABOR_HOURS_YTD3
, 0 BASE_LABOR_HOURS_PTD3
, 0 BASE_LABOR_HOURS_TOT3
FROM PA_PROJECT_ACCUM_ACTUALS A
, PA_PROJECTS_ALL P
, PA_PROJECT_STATUS_CONTROLS PSC
, PA_PROJECT_ACCUM_HEADERS H
WHERE P.PROJECT_ID = H.PROJECT_ID
AND PSC.PROJECT_STATUS_CODE = P.PROJECT_STATUS_CODE
AND PSC.ACTION_CODE = 'STATUS_REPORTING'
AND PSC.ENABLED_FLAG = 'Y'
AND A.PROJECT_ACCUM_ID = H.PROJECT_ACCUM_ID
AND H.RESOURCE_LIST_MEMBER_ID = 0
AND H.TASK_ID = 0 UNION ALL SELECT /* ONLY BUDGET TYPE 1 (COST) */ H.PROJECT_ID
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, B.BUDGET_TYPE_CODE
, T.BUDGET_TYPE
, BASE_RAW_COST_ITD
, BASE_RAW_COST_YTD
, BASE_RAW_COST_PTD
, BASE_RAW_COST_TOT
, ORIG_RAW_COST_ITD
, ORIG_RAW_COST_YTD
, ORIG_RAW_COST_PTD
, ORIG_RAW_COST_TOT
, BASE_BURDENED_COST_ITD
, BASE_BURDENED_COST_YTD
, BASE_BURDENED_COST_PTD
, BASE_BURDENED_COST_TOT
, ORIG_BURDENED_COST_ITD
, ORIG_BURDENED_COST_YTD
, ORIG_BURDENED_COST_PTD
, ORIG_BURDENED_COST_TOT
, ORIG_LABOR_HOURS_ITD
, ORIG_LABOR_HOURS_YTD
, ORIG_LABOR_HOURS_PTD
, ORIG_LABOR_HOURS_TOT
, BASE_LABOR_HOURS_ITD
, BASE_LABOR_HOURS_YTD
, BASE_LABOR_HOURS_PTD
, BASE_LABOR_HOURS_TOT
, NULL
, NULL
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
FROM PA_PROJECT_ACCUM_BUDGETS B
, PA_PROJECTS_ALL P
, PA_PROJECT_STATUS_CONTROLS PSC
, PA_PROJECT_ACCUM_HEADERS H
, PA_BUDGET_TYPES T
WHERE B.BUDGET_TYPE_CODE = PA_RPT_UTILS.GET_RPT_BUDGET_TYPE('C'
, 1)
AND P.PROJECT_ID = H.PROJECT_ID
AND PSC.PROJECT_STATUS_CODE = P.PROJECT_STATUS_CODE
AND PSC.ACTION_CODE = 'STATUS_REPORTING'
AND PSC.ENABLED_FLAG = 'Y'
AND B.PROJECT_ACCUM_ID = H.PROJECT_ACCUM_ID
AND B.BUDGET_TYPE_CODE = T.BUDGET_TYPE_CODE
AND H.TASK_ID = 0
AND H.RESOURCE_LIST_MEMBER_ID = 0 UNION ALL SELECT /* ONLY BUDGET TYPE 3 (REVENUE) */ H.PROJECT_ID
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, B.BUDGET_TYPE_CODE
, T.BUDGET_TYPE
, BASE_RAW_COST_ITD
, BASE_RAW_COST_YTD
, BASE_RAW_COST_PTD
, BASE_RAW_COST_TOT
, ORIG_RAW_COST_ITD
, ORIG_RAW_COST_YTD
, ORIG_RAW_COST_PTD
, ORIG_RAW_COST_TOT
, BASE_BURDENED_COST_ITD
, BASE_BURDENED_COST_YTD
, BASE_BURDENED_COST_PTD
, BASE_BURDENED_COST_TOT
, ORIG_BURDENED_COST_ITD
, ORIG_BURDENED_COST_YTD
, ORIG_BURDENED_COST_PTD
, ORIG_BURDENED_COST_TOT
, ORIG_LABOR_HOURS_ITD
, ORIG_LABOR_HOURS_YTD
, ORIG_LABOR_HOURS_PTD
, ORIG_LABOR_HOURS_TOT
, BASE_LABOR_HOURS_ITD
, BASE_LABOR_HOURS_YTD
, BASE_LABOR_HOURS_PTD
, BASE_LABOR_HOURS_TOT
, NULL
, NULL
, 0
, 0
, 0
, B.BASE_REVENUE_TOT
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
FROM PA_PROJECT_ACCUM_BUDGETS B
, PA_PROJECTS_ALL P
, PA_PROJECT_STATUS_CONTROLS PSC
, PA_PROJECT_ACCUM_HEADERS H
, PA_BUDGET_TYPES T
WHERE B.BUDGET_TYPE_CODE = PA_RPT_UTILS.GET_RPT_BUDGET_TYPE('R'
, 1)
AND P.PROJECT_ID = H.PROJECT_ID
AND PSC.PROJECT_STATUS_CODE = P.PROJECT_STATUS_CODE
AND PSC.ACTION_CODE = 'STATUS_REPORTING'
AND PSC.ENABLED_FLAG = 'Y'
AND B.PROJECT_ACCUM_ID = H.PROJECT_ACCUM_ID
AND B.BUDGET_TYPE_CODE = T.BUDGET_TYPE_CODE
AND H.TASK_ID = 0
AND H.RESOURCE_LIST_MEMBER_ID = 0 UNION ALL SELECT H.PROJECT_ID
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, CMT_RAW_COST_PTD
, CMT_BURDENED_COST_PTD
, NULL
, NULL
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, NULL
, NULL
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
FROM PA_PROJECT_ACCUM_COMMITMENTS C
, PA_PROJECTS_ALL P
, PA_PROJECT_STATUS_CONTROLS PSC
, PA_PROJECT_ACCUM_HEADERS H
WHERE P.PROJECT_ID = H.PROJECT_ID
AND H.PROJECT_ACCUM_ID = C.PROJECT_ACCUM_ID
AND H.TASK_ID = 0
AND H.RESOURCE_LIST_MEMBER_ID = 0
AND PSC.PROJECT_STATUS_CODE = P.PROJECT_STATUS_CODE
AND PSC.ACTION_CODE = 'STATUS_REPORTING'
AND PSC.ENABLED_FLAG = 'Y' UNION ALL SELECT PROJECT_ID
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, NULL
, NULL
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, NULL
, NULL
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
FROM PA_PROJECTS_ALL P
WHERE P.TEMPLATE_FLAG = 'N' ) SB GROUP BY PROJECT_ID ) S
WHERE P.PROJECT_TYPE = PT.PROJECT_TYPE
AND P.PROJECT_STATUS_CODE = PS.PROJECT_STATUS_CODE
AND P.CARRYING_OUT_ORGANIZATION_ID = OU1.ORGANIZATION_ID
AND OU1.LANGUAGE = USERENV('LANG')
AND P.ORG_ID = OU2.ORGANIZATION_ID (+)
AND OU2.LANGUAGE (+) = USERENV('LANG')
AND P.TEMPLATE_FLAG = 'N'
AND PA_SECURITY.ALLOW_QUERY(P.PROJECT_ID) = 'Y'
AND P.PROJECT_ID = S.PROJECT_ID
AND PROJMGR.PROJECT_ID (+) = P.PROJECT_ID
AND P.ORG_ID = PT.ORG_ID