[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
, P_SELECT_LIST =>'
COST "PJI_REP_MSR_1"
, COST "PJI_REP_MSR_19"
, CT_COST "PJI_REP_MSR_2"
, COST_CHANGE_PRCNT "PJI_REP_MSR_3"
, BILL_COST "PJI_REP_MSR_4"
, BILL_COST "PJI_REP_MSR_20"
, CT_BILL_COST "PJI_REP_MSR_5"
, BILL_COST_CHANGE_PRCNT "PJI_REP_MSR_6"
, BILL_PRCNT_OF_COST "PJI_REP_MSR_7"
, BILL_PRCNT_OF_COST "PJI_REP_MSR_23"
, CT_BILL_PRCNT_OF_COST "PJI_REP_MSR_8"
, PRCNT_OF_COST_CHANGE "PJI_REP_MSR_9"
, BUDGET "PJI_REP_MSR_10"
, CT_BUDGET "PJI_REP_MSR_11"
, BUDGET_CHANGE_PRCNT "PJI_REP_MSR_12"
, BILL_COST_PRCNT_OF_BUDGET "PJI_REP_MSR_13"
, BILL_COST_PRCNT_OF_BUDGET "PJI_REP_MSR_21"
, CT_BILL_COST_PRCNT_OF_BUDGET "PJI_REP_MSR_14"
, CT_BILL_COST_PRCNT_OF_BUDGET "PJI_REP_MSR_22"
, CHANGE "PJI_REP_MSR_15"
, NON_BILL_COST "PJI_REP_MSR_16"
, NON_BILL_COST "PJI_REP_MSR_24"
, CT_NON_BILL_COST "PJI_REP_MSR_17"
, NON_BILL_COST_CHANGE_PRCNT "PJI_REP_MSR_18"
, FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_1"
, FACT.PJI_REP_TOTAL_2 "PJI_REP_TOTAL_2"
, FACT.PJI_REP_TOTAL_3 "PJI_REP_TOTAL_3"
, FACT.PJI_REP_TOTAL_4 "PJI_REP_TOTAL_4"
, FACT.PJI_REP_TOTAL_5 "PJI_REP_TOTAL_5"
, FACT.PJI_REP_TOTAL_6 "PJI_REP_TOTAL_6"
, FACT.PJI_REP_TOTAL_7 "PJI_REP_TOTAL_7"
, FACT.PJI_REP_TOTAL_8 "PJI_REP_TOTAL_8"
, FACT.PJI_REP_TOTAL_9 "PJI_REP_TOTAL_9"
, FACT.PJI_REP_TOTAL_10 "PJI_REP_TOTAL_10"
, FACT.PJI_REP_TOTAL_11 "PJI_REP_TOTAL_11"
, FACT.PJI_REP_TOTAL_12 "PJI_REP_TOTAL_12"
, FACT.PJI_REP_TOTAL_13 "PJI_REP_TOTAL_13"
, FACT.PJI_REP_TOTAL_14 "PJI_REP_TOTAL_14"
, FACT.PJI_REP_TOTAL_15 "PJI_REP_TOTAL_15"
, FACT.PJI_REP_TOTAL_16 "PJI_REP_TOTAL_16"
, FACT.PJI_REP_TOTAL_17 "PJI_REP_TOTAL_17"
, FACT.PJI_REP_TOTAL_18 "PJI_REP_TOTAL_18"
, FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_19"
, FACT.PJI_REP_TOTAL_4 "PJI_REP_TOTAL_20"
, FACT.PJI_REP_TOTAL_13 "PJI_REP_TOTAL_21"
, FACT.PJI_REP_TOTAL_7 "PJI_REP_TOTAL_23"
, FACT.PJI_REP_TOTAL_16 "PJI_REP_TOTAL_24"'
, P_SQL_STATEMENT => x_PMV_Sql
, P_PMV_OUTPUT => x_PMV_Output
, p_Region_Code => 'PJI_REP_PC10'
, p_PLSQL_Driver => 'PJI_PMV_COST.PLSQLDriver_PJI_REP_PC10'
, p_PLSQL_Driver_Params =>
' <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <> '||
', <> '||
', <> '||
', <> '
);
, P_SELECT_LIST =>
'FACT.COST "PJI_REP_MSR_1"
,FACT.COST "PJI_REP_MSR_19"
,FACT.ct_COST "PJI_REP_MSR_2"
,FACT.ct_COST "PJI_REP_MSR_20"
,FACT.COST_CHANGE_PRCNT "PJI_REP_MSR_3"
,FACT.BILL_COST "PJI_REP_MSR_4"
,FACT.BILL_COST "PJI_REP_MSR_21"
,FACT.ct_BILL_COST "PJI_REP_MSR_5"
,FACT.ct_BILL_COST "PJI_REP_MSR_22"
,FACT.BILL_COST_CHANGE_PRCNT "PJI_REP_MSR_6"
,FACT.BILL_PRCNT_OF_COST "PJI_REP_MSR_7"
,FACT.ct_BILL_PRCNT_OF_COST "PJI_REP_MSR_8"
,FACT.PRCNT_OF_COST_CHANGE "PJI_REP_MSR_9"
,FACT.BUDGET "PJI_REP_MSR_10"
,FACT.ct_BUDGET "PJI_REP_MSR_11"
,FACT.BUDGET_CHANGE_PRCNT "PJI_REP_MSR_12"
,FACT.BILL_COST_PRCNT_OF_BUDGET "PJI_REP_MSR_13"
,FACT.BILL_COST_PRCNT_OF_BUDGET "PJI_REP_MSR_23"
,FACT.ct_BILL_COST_PRCNT_OF_BUDGET "PJI_REP_MSR_14"
,FACT.ct_BILL_COST_PRCNT_OF_BUDGET "PJI_REP_MSR_24"
,FACT.CHANGE "PJI_REP_MSR_15"
,FACT.NON_BILL_COST "PJI_REP_MSR_16"
,FACT.ct_NON_BILL_COST "PJI_REP_MSR_17"
,FACT.NON_BILL_COST_CHANGE_PRCNT "PJI_REP_MSR_18"'
, P_PMV_OUTPUT => x_PMV_Output
, P_REGION_CODE => 'PJI_REP_PC11'
, P_PLSQL_DRIVER => 'PJI_PMV_COST.PLSQLDriver_PJI_REP_PC11'
, P_PLSQL_DRIVER_PARAMS => ' <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <> '||
', <> '||
', <> ');
, P_SELECT_LIST =>
' FACT.PROJECT_ID "PJI_REP_MSR_18"
, FACT.PROJECT_NAME "VIEWBY"
, FACT.PROJECT_NUMBER "PJI_REP_MSR_2"
, FACT.URL_PARAMETERS01 "PJI_REP_MSR_10"
, FACT.URL_PARAMETERS01 "PJI_REP_MSR_30"
, FACT.PRIMARY_CUSTOMER_NAME "PJI_REP_MSR_3"
, FACT.PROJECT_TYPE "PJI_REP_MSR_4"
, FACT.ORGANIZATION_NAME "PJI_REP_MSR_5"
, FACT.PERSON_MANAGER_NAME "PJI_REP_MSR_6"
, FACT.COST "PJI_REP_MSR_8"
, FACT.BILL_COST "PJI_REP_MSR_16"
, FACT.BILL_COST_PRCNT_OF_COST "PJI_REP_MSR_17"
, FACT.BUDGET "PJI_REP_MSR_12"
, FACT.BILL_PRCNT_OF_BUDGET_COST "PJI_REP_MSR_13"
, FACT.NON_BILL_COST "PJI_REP_MSR_14"
, FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_1"
, FACT.PJI_REP_TOTAL_2 "PJI_REP_TOTAL_2"
, FACT.PJI_REP_TOTAL_3 "PJI_REP_TOTAL_3"
, FACT.PJI_REP_TOTAL_4 "PJI_REP_TOTAL_4"
, FACT.PJI_REP_TOTAL_5 "PJI_REP_TOTAL_5"
, FACT.PJI_REP_TOTAL_6 "PJI_REP_TOTAL_6" '
, P_SQL_STATEMENT => x_PMV_Sql
, P_PMV_OUTPUT => x_PMV_Output
, p_Region_Code => 'PJI_REP_PC13'
, p_PLSQL_Driver => 'PJI_PMV_COST.PLSQLDriver_PJI_REP_PC13'
, p_PLSQL_Driver_Params => ' <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <> '||
', <> '||
', <> '||
', <> '||
', <> '
);
, P_SELECT_LIST =>
' FACT.COST "PJI_REP_MSR_1"
, FACT.COST "PJI_REP_MSR_19"
, FACT.CT_COST "PJI_REP_MSR_2"
, FACT.CT_COST "PJI_REP_MSR_20"
, FACT.COST_CHANGE_PRCNT "PJI_REP_MSR_3"
, FACT.BILL_COST "PJI_REP_MSR_4"
, FACT.BILL_COST "PJI_REP_MSR_21"
, FACT.CT_BILL_COST "PJI_REP_MSR_5"
, FACT.CT_BILL_COST "PJI_REP_MSR_22"
, FACT.BILL_COST_CHANGE_PRCNT "PJI_REP_MSR_6"
, FACT.BILL_PRCNT_OF_COST "PJI_REP_MSR_7"
, FACT.CT_BILL_PRCNT_OF_COST "PJI_REP_MSR_8"
, FACT.PRCNT_OF_COST_CHANGE "PJI_REP_MSR_9"
, FACT.BUDGET "PJI_REP_MSR_10"
, FACT.CT_BUDGET "PJI_REP_MSR_11"
, FACT.BUDGET_CHANGE_PRCNT "PJI_REP_MSR_12"
, FACT.BILL_COST_PRCNT_OF_BUDGET "PJI_REP_MSR_13"
, FACT.BILL_COST_PRCNT_OF_BUDGET "PJI_REP_MSR_23"
, FACT.CT_BILL_COST_PRCNT_OF_BUDGET "PJI_REP_MSR_14"
, FACT.CT_BILL_COST_PRCNT_OF_BUDGET "PJI_REP_MSR_24"
, FACT.CHANGE "PJI_REP_MSR_15"
, FACT.NON_BILL_COST "PJI_REP_MSR_16"
, FACT.CT_Non_Bill_Cost "PJI_REP_MSR_17"
, FACT.NON_BILL_COST_CHANGE_PRCNT "PJI_REP_MSR_18"'
, P_SQL_STATEMENT => x_PMV_Sql
, P_PMV_OUTPUT => x_PMV_Output
, P_REGION_CODE => 'PJI_REP_PC12'
, P_PLSQL_DRIVER => 'PJI_PMV_COST.PLSQLDriver_PJI_REP_PC11'
, P_PLSQL_DRIVER_PARAMS => ' <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', :PJI_EXTRA_BND_01'||
', NULL '||
', NULL '||
', NULL ');
SELECT report_cost_type
INTO G_Report_Cost_Type
FROM pji_system_settings;
SELECT PJI_REP_PC10
( ORG_ID
, ORGANIZATION_ID
, PROJECT_CLASS_ID
, EXPENDITURE_CATEGORY
, EXPENDITURE_TYPE_ID
, WORK_TYPE_ID
, SUM ( COST )
, SUM ( CT_COST )
, NULL
, SUM ( BILL_COST )
, SUM ( CT_BILL_COST )
, NULL
, NULL
, NULL
, NULL
, SUM ( BUDGET )
, SUM ( CT_BUDGET )
, NULL
, NULL
, NULL
, NULL
, SUM ( NON_BILL_COST )
, SUM ( CT_NON_BILL_COST )
, NULL
, NULL , NULL , NULL , NULL , NULL, NULL
, NULL , NULL , NULL , NULL , NULL, NULL
, NULL , NULL , NULL , NULL , NULL, NULL )
BULK COLLECT INTO l_lines_tab
FROM
( SELECT /*+ ORDERED */
HOU.NAME AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' as EXPENDITURE_CATEGORY
, '-1' as EXPENDITURE_TYPE_ID
, '-1' as WORK_TYPE_ID
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
'RC', FCT.CONTRACT_RAW_COST, 0),0) AS COST
, 0 CT_COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
'RC', FCT.BILL_RAW_COST, 0),0) AS BILL_COST
, 0 CT_BILL_COST
, DECODE(TIME.amount_type,2,
DECODE(G_Report_Cost_Type, 'BC', FCT.CON_CURR_BGT_BRDN_COST,
'RC', FCT.CON_CURR_BGT_RAW_COST, 0),0) AS BUDGET
, 0 CT_BUDGET
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS NON_BILL_COST
, 0 CT_NON_BILL_COST
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_FP_ORGO_F_MV FCT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
fct.org_id = hou.id
AND FCT.ORGANIZATION_ID = HORG.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL -- PRIOR Actuals
SELECT /*+ ORDERED */
HOU.NAME AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' as EXPENDITURE_CATEGORY
, '-1' as EXPENDITURE_TYPE_ID
, '-1' as WORK_TYPE_ID
, 0 AS COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
'RC', FCT.CONTRACT_RAW_COST, 0),0) AS CT_COST
, 0 AS BILL_COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
'RC', FCT.BILL_RAW_COST, 0),0) AS CT_BILL_COST
, 0 AS BUDGET
, DECODE(TIME.amount_type,2,
DECODE(G_Report_Cost_Type, 'BC', FCT.CON_CURR_BGT_BRDN_COST,
'RC', FCT.CON_CURR_BGT_RAW_COST, 0),0) AS CT_BUDGET
, 0 AS NON_BILL_COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS CT_NON_BILL_COST
FROM
PJI_PMV_TCMP_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_FP_ORGO_F_MV fct
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
fct.org_id = hou.id
AND FCT.ORGANIZATION_ID = HORG.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL -- FORCE Creation of Org rows
SELECT HOU.NAME AS ORG_ID
, '-1' AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS COST
, 0 AS CT_COST
, 0 AS BILL_COST
, 0 AS CT_BILL_COST
, 0 AS BUDGET
, 0 AS CT_BUDGET
, 0 AS NON_BILL_COST
, 0 AS CT_NON_BILL_COST
FROM PJI_PMV_ORG_DIM_TMP HOU
WHERE HOU.NAME <> '-1'
UNION ALL -- FORCE Creation of Organization Rows
SELECT '-1' AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' as EXPENDITURE_CATEGORY
, '-1' as EXPENDITURE_TYPE_ID
, '-1' as WORK_TYPE_ID
, 0 AS COST
, 0 AS CT_COST
, 0 AS BILL_COST
, 0 AS CT_BILL_COST
, 0 AS BUDGET
, 0 AS CT_BUDGET
, 0 AS NON_BILL_COST
, 0 AS CT_NON_BILL_COST
FROM PJI_PMV_ORGZ_DIM_TMP HORG
WHERE HORG.NAME <> '-1'
) WHERE 1=1
GROUP BY ORG_ID, ORGANIZATION_ID, PROJECT_CLASS_ID, EXPENDITURE_CATEGORY,
EXPENDITURE_TYPE_ID,WORK_TYPE_ID;
SELECT PJI_REP_PC10
( ORG_ID
, ORGANIZATION_ID
, PROJECT_CLASS_ID
, EXPENDITURE_CATEGORY
, EXPENDITURE_TYPE_ID
, WORK_TYPE_ID
, SUM ( COST )
, SUM ( CT_COST )
, NULL
, SUM ( BILL_COST )
, SUM ( CT_BILL_COST )
, NULL
, NULL
, NULL
, NULL
, SUM ( BUDGET )
, SUM ( CT_BUDGET )
, NULL
, NULL
, NULL
, NULL
, SUM ( NON_BILL_COST )
, SUM ( CT_NON_BILL_COST )
, NULL
, NULL , NULL , NULL , NULL , NULL, NULL
, NULL , NULL , NULL , NULL , NULL, NULL
, NULL , NULL , NULL , NULL , NULL, NULL )
BULK COLLECT INTO l_lines_tab
FROM
( SELECT /*+ ORDERED */
HOU.NAME AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, CLS.NAME AS PROJECT_CLASS_ID
, '-1' as EXPENDITURE_CATEGORY
, '-1' as EXPENDITURE_TYPE_ID
, '-1' as WORK_TYPE_ID
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
'RC', FCT.CONTRACT_RAW_COST, 0),0) AS COST
, 0 CT_COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
'RC', FCT.BILL_RAW_COST, 0),0) AS BILL_COST
, 0 CT_BILL_COST
, DECODE(TIME.amount_type,2,
DECODE(G_Report_Cost_Type, 'BC', FCT.CON_CURR_BGT_BRDN_COST,
'RC', FCT.CON_CURR_BGT_RAW_COST, 0),0) AS BUDGET
, 0 CT_BUDGET
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS NON_BILL_COST
, 0 CT_NON_BILL_COST
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_FP_CLSO_F_MV FCT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
FCT.PROJECT_ORG_ID = HOU.ID
AND FCT.PROJECT_ORGANIZATION_ID = HORG.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.PROJECT_CLASS_ID = CLS.ID
UNION ALL -- PRIOR Actuals
SELECT /*+ ORDERED */
HOU.NAME AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, CLS.NAME AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
'RC', FCT.CONTRACT_RAW_COST, 0),0) AS CT_COST
, 0 AS BILL_COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
'RC', FCT.BILL_RAW_COST, 0),0) AS CT_BILL_COST
, 0 AS BUDGET
, DECODE(TIME.amount_type,2,
DECODE(G_Report_Cost_Type, 'BC', FCT.CON_CURR_BGT_BRDN_COST,
'RC', FCT.CON_CURR_BGT_RAW_COST, 0),0) AS CT_BUDGET
, 0 AS NON_BILL_COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS CT_NON_BILL_COST
FROM
PJI_PMV_TCMP_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_FP_CLSO_F_MV FCT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
FCT.PROJECT_ORG_ID = HOU.ID
AND FCT.PROJECT_ORGANIZATION_ID = HORG.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.PROJECT_CLASS_ID = CLS.ID
UNION ALL -- FORCE Creation of Org rows
SELECT HOU.NAME AS ORG_ID
, '-1' AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS COST
, 0 AS CT_COST
, 0 AS BILL_COST
, 0 AS CT_BILL_COST
, 0 AS BUDGET
, 0 AS CT_BUDGET
, 0 AS NON_BILL_COST
, 0 AS CT_NON_BILL_COST
FROM PJI_PMV_ORG_DIM_TMP HOU
WHERE HOU.NAME <> '-1'
UNION ALL -- FORCE Creation of Organization Rows
SELECT '-1' AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' as EXPENDITURE_CATEGORY
, '-1' as EXPENDITURE_TYPE_ID
, '-1' as WORK_TYPE_ID
, 0 AS COST
, 0 AS CT_COST
, 0 AS BILL_COST
, 0 AS CT_BILL_COST
, 0 AS BUDGET
, 0 AS CT_BUDGET
, 0 AS NON_BILL_COST
, 0 AS CT_NON_BILL_COST
FROM PJI_PMV_ORGZ_DIM_TMP HORG
WHERE HORG.NAME <> '-1'
UNION ALL
SELECT '-1' AS ORG_ID
, '-1' AS ORGANIZATION_ID
, CLS.NAME AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS COST
, 0 AS CT_COST
, 0 AS BILL_COST
, 0 AS CT_BILL_COST
, 0 AS BUDGET
, 0 AS CT_BUDGET
, 0 AS NON_BILL_COST
, 0 AS CT_NON_BILL_COST
FROM PJI_PMV_CLS_DIM_TMP CLS
WHERE CLS.NAME <> '-1'
) WHERE 1=1
GROUP BY ORG_ID, ORGANIZATION_ID, PROJECT_CLASS_ID, EXPENDITURE_CATEGORY,
EXPENDITURE_TYPE_ID,WORK_TYPE_ID;
SELECT PJI_REP_PC10
( ORG_ID
, ORGANIZATION_ID
, PROJECT_CLASS_ID
, EXPENDITURE_CATEGORY
, EXPENDITURE_TYPE_ID
, WORK_TYPE_ID
, SUM ( COST )
, SUM ( CT_COST )
, NULL
, SUM ( BILL_COST )
, SUM ( CT_BILL_COST )
, NULL
, NULL
, NULL
, NULL
, SUM ( BUDGET )
, SUM ( CT_BUDGET )
, NULL
, NULL
, NULL
, NULL
, SUM ( NON_BILL_COST )
, SUM ( CT_NON_BILL_COST )
, NULL
, NULL , NULL , NULL , NULL , NULL, NULL
, NULL , NULL , NULL , NULL , NULL, NULL
, NULL , NULL , NULL , NULL , NULL, NULL )
BULK COLLECT INTO l_lines_tab
FROM
( SELECT /*+ ORDERED */
HOU.NAME AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
, decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
, '-1' as WORK_TYPE_ID
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
'RC', FCT.CONTRACT_RAW_COST, 0),0) AS COST
, 0 CT_COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
'RC', FCT.BILL_RAW_COST, 0),0) AS BILL_COST
, 0 CT_BILL_COST
, NULL AS BUDGET
, NULL AS CT_BUDGET
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS NON_BILL_COST
, 0 CT_NON_BILL_COST
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_ORGO_ET_F_MV FCT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
FCT.ORG_ID = HOU.ID
AND FCT.ORGANIZATION_ID = HORG.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND EXP_EVT_TYPE_ID= ET.ID
and ET.record_type = 'ET'
UNION ALL -- PRIOR Actuals
SELECT /*+ ORDERED */
HOU.NAME AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
, decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
'RC', FCT.CONTRACT_RAW_COST, 0),0) AS CT_COST
, 0 AS BILL_COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
'RC', FCT.BILL_RAW_COST, 0),0) AS CT_BILL_COST
, NULL AS BUDGET
, NULL AS CT_BUDGET
, 0 AS NON_BILL_COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS CT_NON_BILL_COST
FROM
PJI_PMV_TCMP_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_ORGO_ET_F_MV FCT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
FCT.ORG_ID = HOU.ID
AND FCT.ORGANIZATION_ID = HORG.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.EXP_EVT_TYPE_ID = ET.ID
and ET.record_type = 'ET'
UNION ALL -- FORCE Creation of Org rows
SELECT HOU.NAME AS ORG_ID
, '-1' AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS COST
, 0 AS CT_COST
, 0 AS BILL_COST
, 0 AS CT_BILL_COST
, 0 AS BUDGET
, 0 AS CT_BUDGET
, 0 AS NON_BILL_COST
, 0 AS CT_NON_BILL_COST
FROM PJI_PMV_ORG_DIM_TMP HOU
WHERE HOU.NAME <> '-1'
UNION ALL -- FORCE Creation of Organization Rows
SELECT '-1' AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' as EXPENDITURE_CATEGORY
, '-1' as EXPENDITURE_TYPE_ID
, '-1' as WORK_TYPE_ID
, 0 AS COST
, 0 AS CT_COST
, 0 AS BILL_COST
, 0 AS CT_BILL_COST
, 0 AS BUDGET
, 0 AS CT_BUDGET
, 0 AS NON_BILL_COST
, 0 AS CT_NON_BILL_COST
FROM PJI_PMV_ORGZ_DIM_TMP HORG
WHERE HORG.NAME <> '-1'
UNION ALL
SELECT '-1' AS ORG_ID
, '-1' AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
, decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS COST
, 0 AS CT_COST
, 0 AS BILL_COST
, 0 AS CT_BILL_COST
, 0 AS BUDGET
, 0 AS CT_BUDGET
, 0 AS NON_BILL_COST
, 0 AS CT_NON_BILL_COST
FROM PJI_PMV_ET_RT_DIM_TMP ET
WHERE ET.NAME <> '-1'
) WHERE 1=1
GROUP BY ORG_ID, ORGANIZATION_ID, PROJECT_CLASS_ID, EXPENDITURE_CATEGORY,
EXPENDITURE_TYPE_ID,WORK_TYPE_ID;
SELECT PJI_REP_PC10
( ORG_ID
, ORGANIZATION_ID
, PROJECT_CLASS_ID
, EXPENDITURE_CATEGORY
, EXPENDITURE_TYPE_ID
, WORK_TYPE_ID
, SUM ( COST )
, SUM ( CT_COST )
, NULL
, SUM ( BILL_COST )
, SUM ( CT_BILL_COST )
, NULL
, NULL
, NULL
, NULL
, SUM ( BUDGET )
, SUM ( CT_BUDGET )
, NULL
, NULL
, NULL
, NULL
, SUM ( NON_BILL_COST )
, SUM ( CT_NON_BILL_COST )
, NULL
, NULL , NULL , NULL , NULL , NULL, NULL
, NULL , NULL , NULL , NULL , NULL, NULL
, NULL , NULL , NULL , NULL , NULL, NULL )
BULK COLLECT INTO l_lines_tab
FROM
( SELECT /*+ ORDERED */
HOU.NAME AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, WT.NAME AS WORK_TYPE_ID
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
'RC', FCT.CONTRACT_RAW_COST, 0),0) AS COST
, 0 CT_COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
'RC', FCT.BILL_RAW_COST, 0),0) AS BILL_COST
, 0 CT_BILL_COST
, NULL AS BUDGET
, NULL AS CT_BUDGET
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS NON_BILL_COST
, 0 CT_NON_BILL_COST
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_WT_DIM_TMP WT
, PJI_FP_ORGO_ET_WT_F_MV FCT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
FCT.ORG_ID = HOU.ID
AND FCT.ORGANIZATION_ID = HORG.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND WORK_TYPE_ID= WT.ID
UNION ALL -- PRIOR Actuals
SELECT /*+ ORDERED */
HOU.NAME AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, WT.NAME AS WORK_TYPE_ID
, 0 AS COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
'RC', FCT.CONTRACT_RAW_COST, 0),0) AS CT_COST
, 0 AS BILL_COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
'RC', FCT.BILL_RAW_COST, 0),0) AS CT_BILL_COST
, NULL AS BUDGET
, NULL AS CT_BUDGET
, 0 AS NON_BILL_COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS CT_NON_BILL_COST
FROM
PJI_PMV_TCMP_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_WT_DIM_TMP WT
, PJI_FP_ORGO_ET_WT_F_MV FCT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
FCT.ORG_ID = HOU.ID
AND FCT.ORGANIZATION_ID = HORG.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.WORK_TYPE_ID = WT.ID
UNION ALL -- FORCE Creation of Org rows
SELECT HOU.NAME AS ORG_ID
, '-1' AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS COST
, 0 AS CT_COST
, 0 AS BILL_COST
, 0 AS CT_BILL_COST
, 0 AS BUDGET
, 0 AS CT_BUDGET
, 0 AS NON_BILL_COST
, 0 AS CT_NON_BILL_COST
FROM PJI_PMV_ORG_DIM_TMP HOU
WHERE HOU.NAME <> '-1'
UNION ALL -- FORCE Creation of Organization Rows
SELECT '-1' AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' as EXPENDITURE_CATEGORY
, '-1' as EXPENDITURE_TYPE_ID
, '-1' as WORK_TYPE_ID
, 0 AS COST
, 0 AS CT_COST
, 0 AS BILL_COST
, 0 AS CT_BILL_COST
, 0 AS BUDGET
, 0 AS CT_BUDGET
, 0 AS NON_BILL_COST
, 0 AS CT_NON_BILL_COST
FROM PJI_PMV_ORGZ_DIM_TMP HORG
WHERE HORG.NAME <> '-1'
UNION ALL
SELECT '-1' AS ORG_ID
, '-1' AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, WT.NAME AS WORK_TYPE_ID
, 0 AS COST
, 0 AS CT_COST
, 0 AS BILL_COST
, 0 AS CT_BILL_COST
, 0 AS BUDGET
, 0 AS CT_BUDGET
, 0 AS NON_BILL_COST
, 0 AS CT_NON_BILL_COST
FROM PJI_PMV_WT_DIM_TMP WT
WHERE WT.NAME <> '-1'
) WHERE 1=1
GROUP BY ORG_ID, ORGANIZATION_ID, PROJECT_CLASS_ID, EXPENDITURE_CATEGORY,
EXPENDITURE_TYPE_ID,WORK_TYPE_ID;
SELECT PJI_REP_PC10
( ORG_ID
, ORGANIZATION_ID
, PROJECT_CLASS_ID
, EXPENDITURE_CATEGORY
, EXPENDITURE_TYPE_ID
, WORK_TYPE_ID
, SUM ( COST )
, SUM ( CT_COST )
, NULL
, SUM ( BILL_COST )
, SUM ( CT_BILL_COST )
, NULL
, NULL
, NULL
, NULL
, SUM ( BUDGET )
, SUM ( CT_BUDGET )
, NULL
, NULL
, NULL
, NULL
, SUM ( NON_BILL_COST )
, SUM ( CT_NON_BILL_COST )
, NULL
, NULL , NULL , NULL , NULL , NULL, NULL
, NULL , NULL , NULL , NULL , NULL, NULL
, NULL , NULL , NULL , NULL , NULL, NULL )
BULK COLLECT INTO l_lines_tab
FROM
( SELECT /*+ ORDERED */
HOU.NAME AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, CLS.NAME AS PROJECT_CLASS_ID
, decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
, decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
'RC', FCT.CONTRACT_RAW_COST, 0),0) AS COST
, 0 CT_COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
'RC', FCT.BILL_RAW_COST, 0),0) AS BILL_COST
, 0 CT_BILL_COST
, NULL AS BUDGET
, NULL AS CT_BUDGET
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS NON_BILL_COST
, 0 CT_NON_BILL_COST
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_CLSO_ET_F_MV FCT
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
FCT.PROJECT_ORG_ID = HOU.ID
AND FCT.PROJECT_ORGANIZATION_ID = HORG.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND PROJECT_CLASS_ID= CLS.ID
and FCT.EXP_EVT_TYPE_ID = ET.ID
and ET.record_type = 'ET'
UNION ALL -- PRIOR Actuals
SELECT /*+ ORDERED */
HOU.NAME AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, CLS.NAME AS PROJECT_CLASS_ID
, decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
, decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
'RC', FCT.CONTRACT_RAW_COST, 0),0) AS CT_COST
, 0 AS BILL_COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
'RC', FCT.BILL_RAW_COST, 0),0) AS CT_BILL_COST
, NULL AS BUDGET
, NULL AS CT_BUDGET
, 0 AS NON_BILL_COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS CT_NON_BILL_COST
FROM
PJI_PMV_TCMP_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_CLSO_ET_F_MV FCT
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
FCT.PROJECT_ORG_ID = HOU.ID
AND FCT.PROJECT_ORGANIZATION_ID = HORG.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND PROJECT_CLASS_ID = CLS.ID
and FCT.EXP_EVT_TYPE_ID = ET.ID
and ET.record_type = 'ET'
UNION ALL -- FORCE Creation of Org rows
SELECT HOU.NAME AS ORG_ID
, '-1' AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS COST
, 0 AS CT_COST
, 0 AS BILL_COST
, 0 AS CT_BILL_COST
, 0 AS BUDGET
, 0 AS CT_BUDGET
, 0 AS NON_BILL_COST
, 0 AS CT_NON_BILL_COST
FROM PJI_PMV_ORG_DIM_TMP HOU
WHERE HOU.NAME <> '-1'
UNION ALL -- FORCE Creation of Organization Rows
SELECT '-1' AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' as EXPENDITURE_CATEGORY
, '-1' as EXPENDITURE_TYPE_ID
, '-1' as WORK_TYPE_ID
, 0 AS COST
, 0 AS CT_COST
, 0 AS BILL_COST
, 0 AS CT_BILL_COST
, 0 AS BUDGET
, 0 AS CT_BUDGET
, 0 AS NON_BILL_COST
, 0 AS CT_NON_BILL_COST
FROM PJI_PMV_ORGZ_DIM_TMP HORG
WHERE HORG.NAME <> '-1'
UNION ALL
SELECT '-1' AS ORG_ID
, '-1' AS ORGANIZATION_ID
, CLS.NAME AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS COST
, 0 AS CT_COST
, 0 AS BILL_COST
, 0 AS CT_BILL_COST
, 0 AS BUDGET
, 0 AS CT_BUDGET
, 0 AS NON_BILL_COST
, 0 AS CT_NON_BILL_COST
FROM PJI_PMV_CLS_DIM_TMP CLS
WHERE CLS.NAME <> '-1'
UNION ALL
SELECT '-1' AS ORG_ID
, '-1' AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
, decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS COST
, 0 AS CT_COST
, 0 AS BILL_COST
, 0 AS CT_BILL_COST
, 0 AS BUDGET
, 0 AS CT_BUDGET
, 0 AS NON_BILL_COST
, 0 AS CT_NON_BILL_COST
FROM PJI_PMV_ET_RT_DIM_TMP ET
WHERE ET.NAME <> '-1'
) WHERE 1=1
GROUP BY ORG_ID, ORGANIZATION_ID, PROJECT_CLASS_ID, EXPENDITURE_CATEGORY,
EXPENDITURE_TYPE_ID,WORK_TYPE_ID;
SELECT PJI_REP_PC10
( ORG_ID
, ORGANIZATION_ID
, PROJECT_CLASS_ID
, EXPENDITURE_CATEGORY
, EXPENDITURE_TYPE_ID
, WORK_TYPE_ID
, SUM ( COST )
, SUM ( CT_COST )
, NULL
, SUM ( BILL_COST )
, SUM ( CT_BILL_COST )
, NULL
, NULL
, NULL
, NULL
, SUM ( BUDGET )
, SUM ( CT_BUDGET )
, NULL
, NULL
, NULL
, NULL
, SUM ( NON_BILL_COST )
, SUM ( CT_NON_BILL_COST )
, NULL
, NULL , NULL , NULL , NULL , NULL, NULL
, NULL , NULL , NULL , NULL , NULL, NULL
, NULL , NULL , NULL , NULL , NULL, NULL )
BULK COLLECT INTO l_lines_tab
FROM
( SELECT /*+ ORDERED */
HOU.NAME AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
, decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
, WT.NAME AS WORK_TYPE_ID
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
'RC', FCT.CONTRACT_RAW_COST, 0),0) AS COST
, 0 CT_COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
'RC', FCT.BILL_RAW_COST, 0),0) AS BILL_COST
, 0 CT_BILL_COST
, NULL AS BUDGET
, NULL AS CT_BUDGET
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS NON_BILL_COST
, 0 CT_NON_BILL_COST
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_ORGO_ET_WT_F_MV FCT
, PJI_PMV_WT_DIM_TMP WT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
FCT.ORG_ID = HOU.ID
AND FCT.ORGANIZATION_ID = HORG.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND WORK_TYPE_ID= WT.ID
and FCT.EXP_EVT_TYPE_ID = ET.ID
and ET.record_type = 'ET'
UNION ALL -- PRIOR Actuals
SELECT /*+ ORDERED */
HOU.NAME AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
, decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
, WT.NAME AS WORK_TYPE_ID
, 0 AS COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
'RC', FCT.CONTRACT_RAW_COST, 0),0) AS CT_COST
, 0 AS BILL_COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
'RC', FCT.BILL_RAW_COST, 0),0) AS CT_BILL_COST
, NULL AS BUDGET
, NULL AS CT_BUDGET
, 0 AS NON_BILL_COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS CT_NON_BILL_COST
FROM
PJI_PMV_TCMP_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_ORGO_ET_WT_F_MV FCT
, PJI_PMV_WT_DIM_TMP WT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
FCT.ORG_ID = HOU.ID
AND FCT.ORGANIZATION_ID = HORG.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND WORK_TYPE_ID = WT.ID
and FCT.EXP_EVT_TYPE_ID = ET.ID
and ET.record_type = 'ET'
UNION ALL -- FORCE Creation of Org rows
SELECT HOU.NAME AS ORG_ID
, '-1' AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS COST
, 0 AS CT_COST
, 0 AS BILL_COST
, 0 AS CT_BILL_COST
, 0 AS BUDGET
, 0 AS CT_BUDGET
, 0 AS NON_BILL_COST
, 0 AS CT_NON_BILL_COST
FROM PJI_PMV_ORG_DIM_TMP HOU
WHERE HOU.NAME <> '-1'
UNION ALL -- FORCE Creation of Organization Rows
SELECT '-1' AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' as EXPENDITURE_CATEGORY
, '-1' as EXPENDITURE_TYPE_ID
, '-1' as WORK_TYPE_ID
, 0 AS COST
, 0 AS CT_COST
, 0 AS BILL_COST
, 0 AS CT_BILL_COST
, 0 AS BUDGET
, 0 AS CT_BUDGET
, 0 AS NON_BILL_COST
, 0 AS CT_NON_BILL_COST
FROM PJI_PMV_ORGZ_DIM_TMP HORG
WHERE HORG.NAME <> '-1'
UNION ALL
SELECT '-1' AS ORG_ID
, '-1' AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, WT.NAME AS WORK_TYPE_ID
, 0 AS COST
, 0 AS CT_COST
, 0 AS BILL_COST
, 0 AS CT_BILL_COST
, 0 AS BUDGET
, 0 AS CT_BUDGET
, 0 AS NON_BILL_COST
, 0 AS CT_NON_BILL_COST
FROM PJI_PMV_WT_DIM_TMP WT
WHERE WT.NAME <> '-1'
UNION ALL
SELECT '-1' AS ORG_ID
, '-1' AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
, decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS COST
, 0 AS CT_COST
, 0 AS BILL_COST
, 0 AS CT_BILL_COST
, 0 AS BUDGET
, 0 AS CT_BUDGET
, 0 AS NON_BILL_COST
, 0 AS CT_NON_BILL_COST
FROM PJI_PMV_ET_RT_DIM_TMP ET
WHERE ET.NAME <> '-1'
) WHERE 1=1
GROUP BY ORG_ID, ORGANIZATION_ID, PROJECT_CLASS_ID, EXPENDITURE_CATEGORY,
EXPENDITURE_TYPE_ID,WORK_TYPE_ID;
SELECT PJI_REP_PC10
( ORG_ID
, ORGANIZATION_ID
, PROJECT_CLASS_ID
, EXPENDITURE_CATEGORY
, EXPENDITURE_TYPE_ID
, WORK_TYPE_ID
, SUM ( COST )
, SUM ( CT_COST )
, NULL
, SUM ( BILL_COST )
, SUM ( CT_BILL_COST )
, NULL
, NULL
, NULL
, NULL
, SUM ( BUDGET )
, SUM ( CT_BUDGET )
, NULL
, NULL
, NULL
, NULL
, SUM ( NON_BILL_COST )
, SUM ( CT_NON_BILL_COST )
, NULL
, NULL , NULL , NULL , NULL , NULL, NULL
, NULL , NULL , NULL , NULL , NULL, NULL
, NULL , NULL , NULL , NULL , NULL, NULL )
BULK COLLECT INTO l_lines_tab
FROM
( SELECT /*+ ORDERED */
HOU.NAME AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, CLS.NAME AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, WT.NAME AS WORK_TYPE_ID
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
'RC', FCT.CONTRACT_RAW_COST, 0),0) AS COST
, 0 CT_COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
'RC', FCT.BILL_RAW_COST, 0),0) AS BILL_COST
, 0 CT_BILL_COST
, NULL AS BUDGET
, NULL AS CT_BUDGET
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS NON_BILL_COST
, 0 CT_NON_BILL_COST
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_FP_CLSO_ET_WT_F_MV FCT
, PJI_PMV_WT_DIM_TMP WT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
FCT.PROJECT_ORG_ID = HOU.ID
AND FCT.PROJECT_ORGANIZATION_ID = HORG.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND WORK_TYPE_ID= WT.ID
and FCT.PROJECT_CLASS_ID = CLS.ID
UNION ALL -- PRIOR Actuals
SELECT /*+ ORDERED */
HOU.NAME AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, CLS.NAME AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, WT.NAME AS WORK_TYPE_ID
, 0 AS COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
'RC', FCT.CONTRACT_RAW_COST, 0),0) AS CT_COST
, 0 AS BILL_COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
'RC', FCT.BILL_RAW_COST, 0),0) AS CT_BILL_COST
, NULL AS BUDGET
, NULL AS CT_BUDGET
, 0 AS NON_BILL_COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS CT_NON_BILL_COST
FROM
PJI_PMV_TCMP_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_FP_CLSO_ET_WT_F_MV FCT
, PJI_PMV_WT_DIM_TMP WT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
FCT.PROJECT_ORG_ID = HOU.ID
AND FCT.PROJECT_ORGANIZATION_ID = HORG.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND WORK_TYPE_ID = WT.ID
and FCT.PROJECT_CLASS_ID = CLS.ID
UNION ALL -- FORCE Creation of Org rows
SELECT HOU.NAME AS ORG_ID
, '-1' AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS COST
, 0 AS CT_COST
, 0 AS BILL_COST
, 0 AS CT_BILL_COST
, 0 AS BUDGET
, 0 AS CT_BUDGET
, 0 AS NON_BILL_COST
, 0 AS CT_NON_BILL_COST
FROM PJI_PMV_ORG_DIM_TMP HOU
WHERE HOU.NAME <> '-1'
UNION ALL -- FORCE Creation of Organization Rows
SELECT '-1' AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' as EXPENDITURE_CATEGORY
, '-1' as EXPENDITURE_TYPE_ID
, '-1' as WORK_TYPE_ID
, 0 AS COST
, 0 AS CT_COST
, 0 AS BILL_COST
, 0 AS CT_BILL_COST
, 0 AS BUDGET
, 0 AS CT_BUDGET
, 0 AS NON_BILL_COST
, 0 AS CT_NON_BILL_COST
FROM PJI_PMV_ORGZ_DIM_TMP HORG
WHERE HORG.NAME <> '-1'
UNION ALL
SELECT '-1' AS ORG_ID
, '-1' AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, WT.NAME AS WORK_TYPE_ID
, 0 AS COST
, 0 AS CT_COST
, 0 AS BILL_COST
, 0 AS CT_BILL_COST
, 0 AS BUDGET
, 0 AS CT_BUDGET
, 0 AS NON_BILL_COST
, 0 AS CT_NON_BILL_COST
FROM PJI_PMV_WT_DIM_TMP WT
WHERE WT.NAME <> '-1'
UNION ALL
SELECT '-1' AS ORG_ID
, '-1' AS ORGANIZATION_ID
, CLS.NAME AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS COST
, 0 AS CT_COST
, 0 AS BILL_COST
, 0 AS CT_BILL_COST
, 0 AS BUDGET
, 0 AS CT_BUDGET
, 0 AS NON_BILL_COST
, 0 AS CT_NON_BILL_COST
FROM PJI_PMV_CLS_DIM_TMP CLS
WHERE CLS.NAME <> '-1'
) WHERE 1=1
GROUP BY ORG_ID, ORGANIZATION_ID, PROJECT_CLASS_ID, EXPENDITURE_CATEGORY,
EXPENDITURE_TYPE_ID,WORK_TYPE_ID;
SELECT PJI_REP_PC10
( ORG_ID
, ORGANIZATION_ID
, PROJECT_CLASS_ID
, EXPENDITURE_CATEGORY
, EXPENDITURE_TYPE_ID
, WORK_TYPE_ID
, SUM ( COST )
, SUM ( CT_COST )
, NULL
, SUM ( BILL_COST )
, SUM ( CT_BILL_COST )
, NULL
, NULL
, NULL
, NULL
, SUM ( BUDGET )
, SUM ( CT_BUDGET )
, NULL
, NULL
, NULL
, NULL
, SUM ( NON_BILL_COST )
, SUM ( CT_NON_BILL_COST )
, NULL
, NULL , NULL , NULL , NULL , NULL, NULL
, NULL , NULL , NULL , NULL , NULL, NULL
, NULL , NULL , NULL , NULL , NULL, NULL )
BULK COLLECT INTO l_lines_tab
FROM
( SELECT /*+ ORDERED */
HOU.NAME AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, CLS.NAME AS PROJECT_CLASS_ID
, decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
, decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
, WT.NAME AS WORK_TYPE_ID
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
'RC', FCT.CONTRACT_RAW_COST, 0),0) AS COST
, 0 CT_COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
'RC', FCT.BILL_RAW_COST, 0),0) AS BILL_COST
, 0 CT_BILL_COST
, NULL AS BUDGET
, NULL AS CT_BUDGET
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS NON_BILL_COST
, 0 CT_NON_BILL_COST
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_CLSO_ET_WT_F_MV FCT
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_WT_DIM_TMP WT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
FCT.PROJECT_ORG_ID = HOU.ID
AND FCT.PROJECT_ORGANIZATION_ID = HORG.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND WORK_TYPE_ID= WT.ID
and FCT.PROJECT_CLASS_ID = CLS.ID
and FCT.EXP_EVT_TYPE_ID = ET.ID
and ET.record_type = 'ET'
UNION ALL -- PRIOR Actuals
SELECT /*+ ORDERED */
HOU.NAME AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, CLS.NAME AS PROJECT_CLASS_ID
, decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
, decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
, WT.NAME AS WORK_TYPE_ID
, 0 AS COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
'RC', FCT.CONTRACT_RAW_COST, 0),0) AS CT_COST
, 0 AS BILL_COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
'RC', FCT.BILL_RAW_COST, 0),0) AS CT_BILL_COST
, NULL AS BUDGET
, NULL AS CT_BUDGET
, 0 AS NON_BILL_COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS CT_NON_BILL_COST
FROM
PJI_PMV_TCMP_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_CLSO_ET_WT_F_MV FCT
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_WT_DIM_TMP WT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
FCT.PROJECT_ORG_ID = HOU.ID
AND FCT.PROJECT_ORGANIZATION_ID = HORG.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND WORK_TYPE_ID = WT.ID
and FCT.PROJECT_CLASS_ID = CLS.ID
and FCT.EXP_EVT_TYPE_ID = ET.ID
and ET.record_type = 'ET'
UNION ALL -- FORCE Creation of Org rows
SELECT HOU.NAME AS ORG_ID
, '-1' AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS COST
, 0 AS CT_COST
, 0 AS BILL_COST
, 0 AS CT_BILL_COST
, 0 AS BUDGET
, 0 AS CT_BUDGET
, 0 AS NON_BILL_COST
, 0 AS CT_NON_BILL_COST
FROM PJI_PMV_ORG_DIM_TMP HOU
WHERE HOU.NAME <> '-1'
UNION ALL -- FORCE Creation of Organization Rows
SELECT '-1' AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' as EXPENDITURE_CATEGORY
, '-1' as EXPENDITURE_TYPE_ID
, '-1' as WORK_TYPE_ID
, 0 AS COST
, 0 AS CT_COST
, 0 AS BILL_COST
, 0 AS CT_BILL_COST
, 0 AS BUDGET
, 0 AS CT_BUDGET
, 0 AS NON_BILL_COST
, 0 AS CT_NON_BILL_COST
FROM PJI_PMV_ORGZ_DIM_TMP HORG
WHERE HORG.NAME <> '-1'
UNION ALL
SELECT '-1' AS ORG_ID
, '-1' AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
, decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS COST
, 0 AS CT_COST
, 0 AS BILL_COST
, 0 AS CT_BILL_COST
, 0 AS BUDGET
, 0 AS CT_BUDGET
, 0 AS NON_BILL_COST
, 0 AS CT_NON_BILL_COST
FROM PJI_PMV_ET_RT_DIM_TMP ET
WHERE ET.NAME <> '-1'
UNION ALL
SELECT '-1' AS ORG_ID
, '-1' AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, WT.NAME AS WORK_TYPE_ID
, 0 AS COST
, 0 AS CT_COST
, 0 AS BILL_COST
, 0 AS CT_BILL_COST
, 0 AS BUDGET
, 0 AS CT_BUDGET
, 0 AS NON_BILL_COST
, 0 AS CT_NON_BILL_COST
FROM PJI_PMV_WT_DIM_TMP WT
WHERE WT.NAME <> '-1'
UNION ALL
SELECT '-1' AS ORG_ID
, '-1' AS ORGANIZATION_ID
, CLS.NAME AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS COST
, 0 AS CT_COST
, 0 AS BILL_COST
, 0 AS CT_BILL_COST
, 0 AS BUDGET
, 0 AS CT_BUDGET
, 0 AS NON_BILL_COST
, 0 AS CT_NON_BILL_COST
FROM PJI_PMV_CLS_DIM_TMP CLS
WHERE CLS.NAME <> '-1'
) WHERE 1=1
GROUP BY ORG_ID, ORGANIZATION_ID, PROJECT_CLASS_ID, EXPENDITURE_CATEGORY,
EXPENDITURE_TYPE_ID,WORK_TYPE_ID;
l_lines_tab.DELETE(l_Top_Org_Index);
SELECT report_cost_type
INTO l_Report_Cost_Type
FROM pji_system_settings;
SELECT PJI_REP_PC11(
TIME_ID
, SUM( COST )
, SUM( CT_COST )
, NULL
, SUM( BILL_COST )
, SUM( CT_BILL_COST )
, NULL
, NULL
, NULL
, NULL
, SUM ( BUDGET )
, SUM ( CT_BUDGET )
, NULL
, NULL
, NULL
, NULL
, SUM( NON_BILL_COST )
, SUM( CT_Non_Bill_Cost )
, NULL)
BULK COLLECT INTO l_Project_Cost_Trend_Tab
FROM (
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost
,'BC', contract_brdn_cost),0) cost
, 0 CT_COST
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
'BC', bill_burdened_cost),0) bill_cost
, 0 CT_BILL_COST
, DECODE(NVL(TIME.amount_type,2),2,
DECODE(l_Report_Cost_Type,'RC', con_curr_bgt_raw_cost,
'BC', con_curr_bgt_brdn_cost),0) budget
, 0 CT_BUDGET
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
'BC', contract_brdn_cost - bill_burdened_cost),0) non_bill_cost
, 0 CT_Non_Bill_Cost
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_FP_ORGO_F_MV FCT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
FCT.org_id = HOU.id
AND FCT.organization_id = HORG.id
AND FCT.time_id = TIME.id
AND TIME.id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, 0 cost
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost ,'BC', contract_brdn_cost),0) CT_COST
, 0 bill_cost
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
'BC', bill_burdened_cost),0) CT_BILL_COST
, 0 budget
, DECODE(NVL(TIME.amount_type,2),2,
DECODE(l_Report_Cost_Type,'RC', con_curr_bgt_raw_cost,
'BC', con_curr_bgt_brdn_cost),0) CT_BUDGET
, 0 non_bill_cost
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
'BC', contract_brdn_cost - bill_burdened_cost),0) CT_Non_Bill_Cost
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_FP_ORGO_F_MV FCT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
FCT.org_id = HOU.id
AND FCT.organization_id = HORG.id
AND FCT.time_id = TIME.prior_id
AND TIME.prior_id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT
name time_id
, order_by_id time_key
, 0 cost
, 0 CT_COST
, 0 bill_cost
, 0 CT_BILL_COST
, 0 budget
, 0 CT_BUDGET
, 0 non_bill_cost
, 0 CT_Non_Bill_Cost
FROM pji_pmv_time_dim_tmp
WHERE name <> '-1')
GROUP BY
TIME_KEY
, TIME_ID ORDER BY TIME_KEY ASC;
SELECT PJI_REP_PC11(
TIME_ID
, SUM( COST )
, SUM( CT_COST )
, NULL
, SUM( BILL_COST )
, SUM( CT_BILL_COST )
, NULL
, NULL
, NULL
, NULL
, SUM ( BUDGET )
, SUM ( CT_BUDGET )
, NULL
, NULL
, NULL
, NULL
, SUM( NON_BILL_COST )
, SUM( CT_Non_Bill_Cost )
, NULL)
BULK COLLECT INTO l_Project_Cost_Trend_Tab
FROM (
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost ,'BC', contract_brdn_cost),0) cost
, 0 CT_COST
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
'BC', bill_burdened_cost),0) bill_cost
, 0 CT_BILL_COST
, DECODE(NVL(TIME.amount_type,2),2,
DECODE(l_Report_Cost_Type,'RC', con_curr_bgt_raw_cost,
'BC', con_curr_bgt_brdn_cost),0) budget
, 0 CT_BUDGET
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
'BC', contract_brdn_cost - bill_burdened_cost),0) non_bill_cost
, 0 CT_Non_Bill_Cost
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_FP_CLSO_F_MV FCT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
FCT.PROJECT_ORG_ID = HOU.id
AND FCT.PROJECT_organization_id = HORG.id
AND FCT.time_id = TIME.id
AND TIME.id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.PROJECT_CLASS_ID = CLS.ID
UNION ALL
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, 0 cost
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost ,'BC', contract_brdn_cost),0) CT_COST
, 0 bill_cost
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
'BC', bill_burdened_cost),0) CT_BILL_COST
, 0 budget
, DECODE(NVL(TIME.amount_type,2),2,
DECODE(l_Report_Cost_Type,'RC', con_curr_bgt_raw_cost,
'BC', con_curr_bgt_brdn_cost),0) CT_BUDGET
, 0 non_bill_cost
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
'BC', contract_brdn_cost - bill_burdened_cost),0) CT_Non_Bill_Cost
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_FP_CLSO_F_MV FCT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
FCT.PROJECT_org_id = HOU.id
AND FCT.PROJECT_organization_id = HORG.id
AND FCT.time_id = TIME.prior_id
AND TIME.prior_id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.PROJECT_CLASS_ID = CLS.ID
UNION ALL
SELECT
name time_id
, order_by_id time_key
, 0 cost
, 0 CT_COST
, 0 bill_cost
, 0 CT_BILL_COST
, 0 budget
, 0 CT_BUDGET
, 0 non_bill_cost
, 0 CT_Non_Bill_Cost
FROM pji_pmv_time_dim_tmp
WHERE name <> '-1')
GROUP BY
TIME_KEY
, TIME_ID ORDER BY TIME_KEY ASC;
SELECT PJI_REP_PC11(
TIME_ID
, SUM( COST )
, SUM( CT_COST )
, NULL
, SUM( BILL_COST )
, SUM( CT_BILL_COST )
, NULL
, NULL
, NULL
, NULL
, SUM( BUDGET )
, SUM( CT_BUDGET )
, NULL
, NULL
, NULL
, NULL
, SUM( NON_BILL_COST )
, SUM( CT_Non_Bill_Cost )
, NULL)
BULK COLLECT INTO l_Project_Cost_Trend_Tab
FROM (
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost ,'BC', contract_brdn_cost),0) cost
, 0 CT_COST
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
'BC', bill_burdened_cost),0) bill_cost
, 0 CT_BILL_COST
, NULL AS budget
, NULL AS ct_budget
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
'BC', contract_brdn_cost - bill_burdened_cost),0) non_bill_cost
, 0 CT_Non_Bill_Cost
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_ORGO_ET_F_MV FCT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
FCT.org_id = HOU.id
AND FCT.organization_id = HORG.id
AND FCT.time_id = TIME.id
AND TIME.id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'ET'
UNION ALL
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, 0 cost
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost ,'BC', contract_brdn_cost),0) CT_COST
, 0 bill_cost
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
'BC', bill_burdened_cost),0) CT_BILL_COST
, NULL AS budget
, NULL AS CT_BUDGET
, 0 non_bill_cost
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
'BC', contract_brdn_cost - bill_burdened_cost),0) CT_Non_Bill_Cost
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_ORGO_ET_F_MV FCT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
FCT.org_id = HOU.id
AND FCT.organization_id = HORG.id
AND FCT.time_id = TIME.prior_id
AND TIME.prior_id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'ET'
UNION ALL
SELECT
name time_id
, order_by_id time_key
, 0 cost
, 0 CT_COST
, 0 bill_cost
, 0 CT_BILL_COST
, NULL AS budget
, NULL AS CT_BUDGET
, 0 non_bill_cost
, 0 CT_Non_Bill_Cost
FROM pji_pmv_time_dim_tmp
WHERE name <> '-1')
GROUP BY
TIME_KEY
, TIME_ID ORDER BY TIME_KEY ASC;
SELECT PJI_REP_PC11(
TIME_ID
, SUM( COST )
, SUM( CT_COST )
, NULL
, SUM( BILL_COST )
, SUM( CT_BILL_COST )
, NULL
, NULL
, NULL
, NULL
, SUM( BUDGET )
, SUM( CT_BUDGET )
, NULL
, NULL
, NULL
, NULL
, SUM( NON_BILL_COST )
, SUM( CT_Non_Bill_Cost )
, NULL)
BULK COLLECT INTO l_Project_Cost_Trend_Tab
FROM (
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost ,'BC', contract_brdn_cost),0) cost
, 0 CT_COST
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
'BC', bill_burdened_cost),0) bill_cost
, 0 CT_BILL_COST
, NULL AS budget
, NULL AS CT_BUDGET
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
'BC', contract_brdn_cost - bill_burdened_cost),0) non_bill_cost
, 0 CT_Non_Bill_Cost
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_WT_DIM_TMP WT
, PJI_FP_ORGO_ET_WT_F_MV FCT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
FCT.org_id = HOU.id
AND FCT.organization_id = HORG.id
AND FCT.time_id = TIME.id
AND TIME.id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.WORK_TYPE_ID = WT.ID
UNION ALL
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, 0 cost
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost ,
'BC', contract_brdn_cost),0) CT_COST
, 0 bill_cost
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
'BC', bill_burdened_cost),0) CT_BILL_COST
, NULL AS budget
, NULL AS CT_BUDGET
, 0 non_bill_cost
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
'BC', contract_brdn_cost - bill_burdened_cost),0) CT_Non_Bill_Cost
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_WT_DIM_TMP WT
, PJI_FP_ORGO_ET_WT_F_MV FCT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
FCT.org_id = HOU.id
AND FCT.organization_id = HORG.id
AND FCT.time_id = TIME.prior_id
AND TIME.prior_id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.WORK_TYPE_ID = WT.ID
UNION ALL
SELECT
name time_id
, order_by_id time_key
, 0 cost
, 0 CT_COST
, 0 bill_cost
, 0 CT_BILL_COST
, NULL AS budget
, NULL AS CT_BUDGET
, 0 non_bill_cost
, 0 CT_Non_Bill_Cost
FROM pji_pmv_time_dim_tmp
WHERE name <> '-1')
GROUP BY
TIME_KEY
, TIME_ID ORDER BY TIME_KEY ASC;
SELECT PJI_REP_PC11(
TIME_ID
, SUM( COST )
, SUM( CT_COST )
, NULL
, SUM( BILL_COST )
, SUM( CT_BILL_COST )
, NULL
, NULL
, NULL
, NULL
, SUM( BUDGET )
, SUM( CT_BUDGET )
, NULL
, NULL
, NULL
, NULL
, SUM( NON_BILL_COST )
, SUM( CT_Non_Bill_Cost )
, NULL)
BULK COLLECT INTO l_Project_Cost_Trend_Tab
FROM (
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost ,'BC', contract_brdn_cost),0) cost
, 0 CT_COST
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
'BC', bill_burdened_cost),0) bill_cost
, 0 CT_BILL_COST
, NULL AS budget
, NULL AS CT_BUDGET
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
'BC', contract_brdn_cost - bill_burdened_cost),0) non_bill_cost
, 0 CT_Non_Bill_Cost
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_CLSO_ET_F_MV FCT
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
FCT.PROJECT_org_id = HOU.id
AND FCT.PROJECT_organization_id = HORG.id
AND FCT.time_id = TIME.id
AND TIME.id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.PROJECT_CLASS_ID = CLS.ID
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'ET'
UNION ALL
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, 0 cost
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost ,
'BC', contract_brdn_cost),0) CT_COST
, 0 bill_cost
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
'BC', bill_burdened_cost),0) CT_BILL_COST
, NULL AS budget
, NULL AS CT_BUDGET
, 0 non_bill_cost
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
'BC', contract_brdn_cost - bill_burdened_cost),0) CT_Non_Bill_Cost
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_CLSO_ET_F_MV FCT
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
FCT.PROJECT_org_id = HOU.id
AND FCT.PROJECT_organization_id = HORG.id
AND FCT.time_id = TIME.prior_id
AND TIME.prior_id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.PROJECT_CLASS_ID = CLS.ID
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'ET'
UNION ALL
SELECT
name time_id
, order_by_id time_key
, 0 cost
, 0 CT_COST
, 0 bill_cost
, 0 CT_BILL_COST
, NULL AS budget
, NULL AS CT_BUDGET
, 0 non_bill_cost
, 0 CT_Non_Bill_Cost
FROM pji_pmv_time_dim_tmp
WHERE name <> '-1')
GROUP BY
TIME_KEY
, TIME_ID ORDER BY TIME_KEY ASC;
SELECT PJI_REP_PC11(
TIME_ID
, SUM( COST )
, SUM( CT_COST )
, NULL
, SUM( BILL_COST )
, SUM( CT_BILL_COST )
, NULL
, NULL
, NULL
, NULL
, SUM( BUDGET )
, SUM( CT_BUDGET )
, NULL
, NULL
, NULL
, NULL
, SUM( NON_BILL_COST )
, SUM( CT_Non_Bill_Cost )
, NULL)
BULK COLLECT INTO l_Project_Cost_Trend_Tab
FROM (
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost,
'BC', contract_brdn_cost),0) cost
, 0 CT_COST
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
'BC', bill_burdened_cost),0) bill_cost
, 0 CT_BILL_COST
, NULL AS budget
, NULL AS CT_BUDGET
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
'BC', contract_brdn_cost - bill_burdened_cost),0) non_bill_cost
, 0 CT_Non_Bill_Cost
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_ORGO_ET_WT_F_MV FCT
, PJI_PMV_WT_DIM_TMP WT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
FCT.org_id = HOU.id
AND FCT.organization_id = HORG.id
AND FCT.time_id = TIME.id
AND TIME.id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND FCT.WORK_TYPE_ID = WT.ID
and ET.record_type = 'ET'
UNION ALL
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, 0 cost
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost ,'BC', contract_brdn_cost),0) CT_COST
, 0 bill_cost
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
'BC', bill_burdened_cost),0) CT_BILL_COST
, NULL AS budget
, NULL AS CT_BUDGET
, 0 non_bill_cost
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
'BC', contract_brdn_cost - bill_burdened_cost),0) CT_Non_Bill_Cost
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_ORGO_ET_WT_F_MV FCT
, PJI_PMV_WT_DIM_TMP WT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
FCT.org_id = HOU.id
AND FCT.organization_id = HORG.id
AND FCT.time_id = TIME.prior_id
AND TIME.prior_id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND FCT.WORK_TYPE_ID = WT.ID
and ET.record_type = 'ET'
UNION ALL
SELECT
name time_id
, order_by_id time_key
, 0 cost
, 0 CT_COST
, 0 bill_cost
, 0 CT_BILL_COST
, NULL AS budget
, NULL AS CT_BUDGET
, 0 non_bill_cost
, 0 CT_Non_Bill_Cost
FROM pji_pmv_time_dim_tmp
WHERE name <> '-1')
GROUP BY
TIME_KEY
, TIME_ID ORDER BY TIME_KEY ASC;
SELECT PJI_REP_PC11(
TIME_ID
, SUM( COST )
, SUM( CT_COST )
, NULL
, SUM( BILL_COST )
, SUM( CT_BILL_COST )
, NULL
, NULL
, NULL
, NULL
, SUM( BUDGET )
, SUM( CT_BUDGET )
, NULL
, NULL
, NULL
, NULL
, SUM( NON_BILL_COST )
, SUM( CT_Non_Bill_Cost )
, NULL)
BULK COLLECT INTO l_Project_Cost_Trend_Tab
FROM (
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost ,'BC', contract_brdn_cost),0) cost
, 0 CT_COST
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
'BC', bill_burdened_cost),0) bill_cost
, 0 CT_BILL_COST
, NULL AS budget
, NULL AS CT_BUDGET
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
'BC', contract_brdn_cost - bill_burdened_cost),0) non_bill_cost
, 0 CT_Non_Bill_Cost
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_FP_CLSO_ET_WT_F_MV FCT
, PJI_PMV_WT_DIM_TMP WT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
FCT.PROJECT_org_id = HOU.id
AND FCT.PROJECT_organization_id = HORG.id
AND FCT.time_id = TIME.id
AND TIME.id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.PROJECT_CLASS_ID = CLS.ID
AND FCT.WORK_TYPE_ID = WT.ID
UNION ALL
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, 0 cost
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost ,'BC', contract_brdn_cost),0) CT_COST
, 0 bill_cost
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
'BC', bill_burdened_cost),0) CT_BILL_COST
, NULL AS budget
, NULL AS CT_BUDGET
, 0 non_bill_cost
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
'BC', contract_brdn_cost - bill_burdened_cost),0) CT_Non_Bill_Cost
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_FP_CLSO_ET_WT_F_MV FCT
, PJI_PMV_WT_DIM_TMP WT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
FCT.PROJECT_org_id = HOU.id
AND FCT.PROJECT_organization_id = HORG.id
AND FCT.time_id = TIME.prior_id
AND TIME.prior_id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.PROJECT_CLASS_ID = CLS.ID
AND FCT.WORK_TYPE_ID = WT.ID
UNION ALL
SELECT
name time_id
, order_by_id time_key
, 0 cost
, 0 CT_COST
, 0 bill_cost
, 0 CT_BILL_COST
, NULL AS budget
, NULL AS CT_BUDGET
, 0 non_bill_cost
, 0 CT_Non_Bill_Cost
FROM pji_pmv_time_dim_tmp
WHERE name <> '-1')
GROUP BY
TIME_KEY
, TIME_ID ORDER BY TIME_KEY ASC;
SELECT PJI_REP_PC11(
TIME_ID
, SUM( COST )
, SUM( CT_COST )
, NULL
, SUM( BILL_COST )
, SUM( CT_BILL_COST )
, NULL
, NULL
, NULL
, NULL
, SUM( BUDGET )
, SUM( CT_BUDGET )
, NULL
, NULL
, NULL
, NULL
, SUM( NON_BILL_COST )
, SUM( CT_Non_Bill_Cost )
, NULL)
BULK COLLECT INTO l_Project_Cost_Trend_Tab
FROM (
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost ,'BC', contract_brdn_cost),0) cost
, 0 CT_COST
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
'BC', bill_burdened_cost),0) bill_cost
, 0 CT_BILL_COST
, NULL AS budget
, NULL AS CT_BUDGET
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
'BC', contract_brdn_cost - bill_burdened_cost),0) non_bill_cost
, 0 CT_Non_Bill_Cost
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_CLSO_ET_WT_F_MV FCT
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_WT_DIM_TMP WT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
FCT.PROJECT_org_id = HOU.id
AND FCT.PROJECT_organization_id = HORG.id
AND FCT.time_id = TIME.id
AND TIME.id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.PROJECT_CLASS_ID = CLS.ID
AND FCT.WORK_TYPE_ID = WT.ID
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'ET'
UNION ALL
SELECT /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, 0 cost
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost ,'BC', contract_brdn_cost),0) CT_COST
, 0 bill_cost
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
'BC', bill_burdened_cost),0) CT_BILL_COST
, NULL AS budget
, NULL AS CT_BUDGET
, 0 non_bill_cost
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
'BC', contract_brdn_cost - bill_burdened_cost),0) CT_Non_Bill_Cost
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_CLSO_ET_WT_F_MV FCT
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_WT_DIM_TMP WT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE
FCT.PROJECT_org_id = HOU.id
AND FCT.PROJECT_organization_id = HORG.id
AND FCT.time_id = TIME.prior_id
AND TIME.prior_id IS NOT NULL
AND FCT.period_type_id = TIME.period_type
AND FCT.calendar_type = TIME.calendar_type
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.PROJECT_CLASS_ID = CLS.ID
AND FCT.WORK_TYPE_ID = WT.ID
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'ET'
UNION ALL
SELECT
name time_id
, order_by_id time_key
, 0 cost
, 0 CT_COST
, 0 bill_cost
, 0 CT_BILL_COST
, NULL AS budget
, NULL AS CT_BUDGET
, 0 non_bill_cost
, 0 CT_Non_Bill_Cost
FROM pji_pmv_time_dim_tmp
WHERE name <> '-1')
GROUP BY
TIME_KEY
, TIME_ID ORDER BY TIME_KEY ASC;
SELECT report_cost_type
INTO G_Report_Cost_Type
FROM pji_system_settings;
DELETE pji_pmv_prj_dim_tmp;
INSERT INTO pji_pmv_prj_dim_tmp (id, name)
SELECT DISTINCT prj.project_id, '-1' name
FROM
pji_project_classes PJM
, pji_pmv_cls_dim_tmp PTM
, pji_pmv_orgz_dim_tmp org
, pa_projects_all prj
WHERE
pjm.project_class_id = ptm.id
AND prj.project_id = pjm.project_id
AND prj.carrying_out_organization_id = org.ID;
DELETE pji_pmv_prj_dim_tmp;
INSERT INTO pji_pmv_prj_dim_tmp (id, name)
SELECT DISTINCT prj.project_id, '-1' name
FROM
pji_project_classes PJM
, (SELECT project_id
FROM pji_project_classes
WHERE class_category = '$PROJECT_TYPE$CONTRACT') PJC
, pji_pmv_cls_dim_tmp PTM
, pji_pmv_orgz_dim_tmp org
, pa_projects_all prj
WHERE 1=1
AND pjm.project_class_id = ptm.id
AND prj.project_id = pjc.project_id
AND prj.project_id = pjm.project_id
AND prj.carrying_out_organization_id = org.ID;
INSERT INTO pji_pmv_prj_dim_tmp (id, name)
SELECT DISTINCT prj.project_id, '-1' name
FROM
pji_project_classes PJM
, pji_pmv_orgz_dim_tmp org
, pa_projects_all prj
WHERE
prj.project_id = pjm.project_id
AND prj.carrying_out_organization_id = org.ID
AND pjm.class_category = '$PROJECT_TYPE$CONTRACT';
SELECT PJI_REP_PC13 (PROJECT_ID
, NULL
, NULL
, PJI_PMV_UTIL.Drill_To_Proj_Perf_URL(PROJECT_ID, l_curr_record_type_id, p_As_of_Date,p_Period_Type)
, NULL
, NULL
, ORGANIZATION_ID
, NULL
, SUM (COST)
, SUM (BILL_COST)
, NULL
, SUM (BUDGET)
, NULL
, SUM(NON_BILL_COST)
, 0, 0, 0, 0, 0, 0)
BULK COLLECT INTO l_detail_tab
FROM
(SELECT /*+ ORDERED */
FCT.PROJECT_ID AS PROJECT_ID
, FCT.PROJECT_ORGANIZATION_ID AS ORGANIZATION_ID
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.bill_burdened_cost,
'RC', fct.bill_raw_cost, 0), 0) AS BILL_COST
, DECODE(NVL(TIME.amount_type,2),2,
DECODE(G_Report_Cost_Type, 'BC', fct.CURR_BGT_BURDENED_COST,
'RC', fct.CURR_BGT_RAW_COST, 0), 0) AS BUDGET
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.BURDENED_COST - fct.bill_burdened_cost,
'RC', fct.RAW_COST - fct.bill_raw_cost, 0), 0) AS NON_BILL_COST
FROM PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_PRJ_DIM_TMP PRJ
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_FP_PROJ_F FCT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE 1=1
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.PROJECT_ID = PRJ.ID
AND FCT.PROJECT_ORG_ID = HOU.ID
AND FCT.PROJECT_ORGANIZATION_ID = HORG.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
) FCT
GROUP BY PROJECT_ID,ORGANIZATION_ID;
SELECT PJI_REP_PC13 (PROJECT_ID
, NULL
, NULL
, PJI_PMV_UTIL.Drill_To_Proj_Perf_URL(PROJECT_ID, l_curr_record_type_id, p_As_of_Date,p_Period_Type)
, NULL
, NULL
, ORGANIZATION_ID
, NULL
, SUM (COST)
, SUM (BILL_COST)
, NULL
, SUM (BUDGET)
, NULL
, SUM(NON_BILL_COST)
, 0, 0, 0, 0, 0, 0)
BULK COLLECT INTO l_detail_tab
FROM
(SELECT /*+ ORDERED */
FCT.PROJECT_ID AS PROJECT_ID
, FCT.PROJECT_ORGANIZATION_ID AS ORGANIZATION_ID
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.bill_burdened_cost,
'RC', fct.bill_raw_cost, 0), 0) AS BILL_COST
, DECODE(NVL(TIME.amount_type,2),2,
DECODE(G_Report_Cost_Type, 'BC', fct.CURR_BGT_BURDENED_COST,
'RC', fct.CURR_BGT_RAW_COST, 0), 0) AS BUDGET
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.BURDENED_COST - fct.bill_burdened_cost,
'RC', fct.RAW_COST - fct.bill_raw_cost, 0), 0) AS NON_BILL_COST
FROM PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_PRJ_DIM_TMP PRJ
, PJI_FP_PROJ_F FCT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE 1=1
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.PROJECT_ID = PRJ.ID
AND FCT.PROJECT_ORG_ID = HOU.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
) FCT
GROUP BY PROJECT_ID,ORGANIZATION_ID;
SELECT PJI_REP_PC13 (PROJECT_ID
, NULL
, NULL
, PJI_PMV_UTIL.Drill_To_Proj_Perf_URL(PROJECT_ID, l_curr_record_type_id, p_As_of_Date,p_Period_Type)
, NULL
, NULL
, ORGANIZATION_ID
, NULL
, SUM (COST)
, SUM (BILL_COST)
, NULL
, SUM (BUDGET)
, NULL
, SUM(NON_BILL_COST)
, 0, 0, 0, 0, 0, 0)
BULK COLLECT INTO l_detail_tab
FROM
(SELECT /*+ ORDERED */
FCT.PROJECT_ID AS PROJECT_ID
, FCT.PROJECT_ORGANIZATION_ID AS ORGANIZATION_ID
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.bill_burdened_cost,
'RC', fct.bill_raw_cost, 0), 0) AS BILL_COST
, NULL AS BUDGET
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.BURDENED_COST - fct.bill_burdened_cost,
'RC', fct.RAW_COST - fct.bill_raw_cost, 0), 0) AS NON_BILL_COST
FROM PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_PRJ_DIM_TMP PRJ
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_PROJ_ET_F FCT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE 1=1
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.PROJECT_ID = PRJ.ID
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type ='ET'
AND FCT.PROJECT_ORG_ID = HOU.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
) FCT
GROUP BY PROJECT_ID,ORGANIZATION_ID;
SELECT PJI_REP_PC13 (PROJECT_ID
, NULL
, NULL
, PJI_PMV_UTIL.Drill_To_Proj_Perf_URL(PROJECT_ID, l_curr_record_type_id, p_As_of_Date,p_Period_Type)
, NULL
, NULL
, ORGANIZATION_ID
, NULL
, SUM (COST)
, SUM (BILL_COST)
, NULL
, SUM (BUDGET)
, NULL
, SUM(NON_BILL_COST)
, 0, 0, 0, 0, 0, 0)
BULK COLLECT INTO l_detail_tab
FROM
(SELECT /*+ ORDERED */
FCT.PROJECT_ID AS PROJECT_ID
, FCT.PROJECT_ORGANIZATION_ID AS ORGANIZATION_ID
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.bill_burdened_cost,
'RC', fct.bill_raw_cost, 0), 0) AS BILL_COST
, NULL AS BUDGET
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.BURDENED_COST - fct.bill_burdened_cost,
'RC', fct.RAW_COST - fct.bill_raw_cost, 0), 0) AS NON_BILL_COST
FROM PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_PRJ_DIM_TMP PRJ
, PJI_PMV_WT_DIM_TMP WT
, PJI_FP_PROJ_ET_WT_F FCT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE 1=1
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.PROJECT_ID = PRJ.ID
AND FCT.WORK_TYPE_ID = WT.ID
AND FCT.PROJECT_ORG_ID = HOU.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
) FCT
GROUP BY PROJECT_ID,ORGANIZATION_ID;
SELECT PJI_REP_PC13 (PROJECT_ID
, NULL
, NULL
, PJI_PMV_UTIL.Drill_To_Proj_Perf_URL(PROJECT_ID, l_curr_record_type_id, p_As_of_Date,p_Period_Type)
, NULL
, NULL
, ORGANIZATION_ID
, NULL
, SUM (COST)
, SUM (BILL_COST)
, NULL
, SUM (BUDGET)
, NULL
, SUM(NON_BILL_COST)
, 0, 0, 0, 0, 0, 0)
BULK COLLECT INTO l_detail_tab
FROM
(SELECT /*+ ORDERED */
FCT.PROJECT_ID AS PROJECT_ID
, FCT.PROJECT_ORGANIZATION_ID AS ORGANIZATION_ID
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.bill_burdened_cost,
'RC', fct.bill_raw_cost, 0), 0) AS BILL_COST
, NULL AS BUDGET
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.BURDENED_COST - fct.bill_burdened_cost,
'RC', fct.RAW_COST - fct.bill_raw_cost, 0), 0) AS NON_BILL_COST
FROM PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_PRJ_DIM_TMP PRJ
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_PROJ_ET_F FCT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE 1=1
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.PROJECT_ID = PRJ.ID
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type ='ET'
AND FCT.PROJECT_ORG_ID = HOU.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
) FCT
GROUP BY PROJECT_ID,ORGANIZATION_ID;
SELECT PJI_REP_PC13 (PROJECT_ID
, NULL
, NULL
, PJI_PMV_UTIL.Drill_To_Proj_Perf_URL(PROJECT_ID, l_curr_record_type_id, p_As_of_Date,p_Period_Type)
, NULL
, NULL
, ORGANIZATION_ID
, NULL
, SUM (COST)
, SUM (BILL_COST)
, NULL
, SUM (BUDGET)
, NULL
, SUM(NON_BILL_COST)
, 0, 0, 0, 0, 0, 0)
BULK COLLECT INTO l_detail_tab
FROM
(SELECT /*+ ORDERED */
FCT.PROJECT_ID AS PROJECT_ID
, FCT.PROJECT_ORGANIZATION_ID AS ORGANIZATION_ID
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.bill_burdened_cost,
'RC', fct.bill_raw_cost, 0), 0) AS BILL_COST
, NULL AS BUDGET
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.BURDENED_COST - fct.bill_burdened_cost,
'RC', fct.RAW_COST - fct.bill_raw_cost, 0), 0) AS NON_BILL_COST
FROM PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_PRJ_DIM_TMP PRJ
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_PMV_WT_DIM_TMP WT
, PJI_FP_PROJ_ET_WT_F FCT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE 1=1
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.PROJECT_ID = PRJ.ID
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type ='ET'
AND FCT.WORK_TYPE_ID = WT.ID
AND FCT.PROJECT_ORG_ID = HOU.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
) FCT
GROUP BY PROJECT_ID,ORGANIZATION_ID;
THEN SELECT PJI_REP_PC13 (PROJECT_ID
, NULL
, NULL
, PJI_PMV_UTIL.Drill_To_Proj_Perf_URL(PROJECT_ID, l_curr_record_type_id, p_As_of_Date,p_Period_Type)
, NULL
, NULL
, ORGANIZATION_ID
, NULL
, SUM (COST)
, SUM (BILL_COST)
, NULL
, SUM (BUDGET)
, NULL
, SUM(NON_BILL_COST)
, 0, 0, 0, 0, 0, 0)
BULK COLLECT INTO l_detail_tab
FROM
(SELECT /*+ ORDERED */
FCT.PROJECT_ID AS PROJECT_ID
, FCT.PROJECT_ORGANIZATION_ID AS ORGANIZATION_ID
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.bill_burdened_cost,
'RC', fct.bill_raw_cost, 0), 0) AS BILL_COST
, NULL AS BUDGET
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.BURDENED_COST - fct.bill_burdened_cost,
'RC', fct.RAW_COST - fct.bill_raw_cost, 0), 0) AS NON_BILL_COST
FROM PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_PRJ_DIM_TMP PRJ
, PJI_PMV_WT_DIM_TMP WT
, PJI_FP_PROJ_ET_WT_F FCT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE 1=1
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.PROJECT_ID = PRJ.ID
AND FCT.WORK_TYPE_ID = WT.ID
AND FCT.PROJECT_ORG_ID = HOU.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
) FCT
GROUP BY PROJECT_ID,ORGANIZATION_ID;
SELECT PJI_REP_PC13 (PROJECT_ID
, NULL
, NULL
, PJI_PMV_UTIL.Drill_To_Proj_Perf_URL(PROJECT_ID, l_curr_record_type_id, p_As_of_Date,p_Period_Type)
, NULL
, NULL
, ORGANIZATION_ID
, NULL
, SUM (COST)
, SUM (BILL_COST)
, NULL
, SUM (BUDGET)
, NULL
, SUM(NON_BILL_COST)
, 0, 0, 0, 0, 0, 0)
BULK COLLECT INTO l_detail_tab
FROM
(SELECT /*+ ORDERED */
FCT.PROJECT_ID AS PROJECT_ID
, FCT.PROJECT_ORGANIZATION_ID AS ORGANIZATION_ID
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS COST
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.bill_burdened_cost,
'RC', fct.bill_raw_cost, 0), 0) AS BILL_COST
, NULL AS BUDGET
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.BURDENED_COST - fct.bill_burdened_cost,
'RC', fct.RAW_COST - fct.bill_raw_cost, 0), 0) AS NON_BILL_COST
FROM PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_PRJ_DIM_TMP PRJ
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_PMV_WT_DIM_TMP WT
, PJI_FP_PROJ_ET_WT_F FCT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE 1=1
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.PROJECT_ID = PRJ.ID
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type ='ET'
AND FCT.WORK_TYPE_ID = WT.ID
AND FCT.PROJECT_ORG_ID = HOU.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
) FCT
GROUP BY PROJECT_ID,ORGANIZATION_ID;
SELECT NAME
, SEGMENT1
, PROJECT_TYPE
INTO l_detail_tab(i).PROJECT_NAME
, l_detail_tab(i).PROJECT_NUMBER
, l_detail_tab(i).PROJECT_TYPE
FROM PA_PROJECTS_ALL
WHERE PROJECT_ID = l_detail_tab(i).PROJECT_ID;
SELECT NAME
INTO l_detail_tab(i).ORGANIZATION_NAME
FROM HR_ALL_ORGANIZATION_UNITS_TL
WHERE LANGUAGE = USERENV ('LANG')
AND ORGANIZATION_ID = l_detail_tab(i).ORGANIZATION_NAME;