[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
, P_SELECT_LIST =>
' FACT.CT_REVENUE "PJI_REP_MSR_14"
, FACT.CT_MARGIN_PERCENT "PJI_REP_MSR_15"
, FACT.REVENUE "PJI_REP_MSR_16"
, FACT.MARGIN_PERCENT "PJI_REP_MSR_17"
, FACT.CT_BURDENED_COST "PJI_REP_MSR_19"
, FACT.BURDENED_COST "PJI_REP_MSR_8"
, FACT.REVENUE "PJI_REP_MSR_1"
, FACT.MARGIN "PJI_REP_MSR_2"
, FACT.REVENUE "PJI_REP_MSR_7"
, FACT.REV_CHANGE_PERCENT "PJI_REP_MSR_11"
, FACT.MARGIN "PJI_REP_MSR_9"
, FACT.MAR_CHANGE_PERCENT "PJI_REP_MSR_12"
, FACT.MARGIN_PERCENT "PJI_REP_MSR_10"
, FACT.MAR_PERCENT_CHANGE "PJI_REP_MSR_13"
, FACT.CT_MARGIN "PJI_REP_MSR_4"
, FACT.MARGIN "PJI_REP_MSR_3"
, FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_1"
, FACT.PJI_REP_TOTAL_11 "PJI_REP_TOTAL_2"
, FACT.PJI_REP_TOTAL_14 "PJI_REP_TOTAL_3"
, FACT.PJI_REP_TOTAL_15 "PJI_REP_TOTAL_4"
, FACT.PJI_REP_TOTAL_16 "PJI_REP_TOTAL_5"
, FACT.PJI_REP_TOTAL_5 "PJI_REP_TOTAL_6"
, FACT.PJI_REP_TOTAL_12 "PJI_REP_TOTAL_7"
, FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_8"
, FACT.PJI_REP_TOTAL_11 "PJI_REP_TOTAL_9"
, FACT.PJI_REP_TOTAL_6 "PJI_REP_TOTAL_10"
, FACT.PJI_REP_TOTAL_9 "PJI_REP_TOTAL_11"
, FACT.PJI_REP_TOTAL_9 "PJI_REP_TOTAL_12"
, FACT.PJI_REP_TOTAL_10 "PJI_REP_TOTAL_13"
, FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_14"
, FACT.PJI_REP_TOTAL_2 "PJI_REP_TOTAL_15"
, FACT.PJI_REP_TOTAL_9 "PJI_REP_TOTAL_16" '
, P_SQL_STATEMENT => x_PMV_Sql
, P_PMV_OUTPUT => x_PMV_Output
, p_Region_Code => 'PJI_REP_PP1'
, p_PLSQL_Driver => 'PJI_PMV_PROFITABILITY.PLSQLDriver_PJI_REP_PPSUM'
, p_PLSQL_Driver_Params => ' <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <> '||
', NULL'||
', NULL'||
', <>'||
', <>'||
', NULL'
);
, P_SELECT_LIST =>
' FACT.CT_FORECAST_REVENUE "PJI_REP_MSR_14"
, FACT.FCST_CT_MARGIN_PERCENT "PJI_REP_MSR_15"
, FACT.FORECAST_REVENUE "PJI_REP_MSR_16"
, FACT.FCST_MARGIN_PERCENT "PJI_REP_MSR_17"
, FACT.FORECAST_BURDENED_COST "PJI_REP_MSR_8"
, FACT.CT_FORECAST_BURDENED_COST "PJI_REP_MSR_19"
, FACT.FORECAST_REVENUE "PJI_REP_MSR_1"
, FACT.FCST_MARGIN_PERCENT "PJI_REP_MSR_2"
, FACT.FORECAST_REVENUE "PJI_REP_MSR_7"
, FACT.FCST_REV_CHANGE_PERCENT "PJI_REP_MSR_11"
, FACT.FCST_MARGIN "PJI_REP_MSR_9"
, FACT.FCST_MAR_CHANGE_PERCENT "PJI_REP_MSR_12"
, FACT.FCST_MARGIN_PERCENT "PJI_REP_MSR_10"
, FACT.FCST_MAR_PERCENT_CHANGE "PJI_REP_MSR_13"
, FACT.FCST_CT_MARGIN "PJI_REP_MSR_3"
, FACT.FCST_MARGIN "PJI_REP_MSR_4"
, FACT.PJI_REP_TOTAL_7 "PJI_REP_TOTAL_1"
, FACT.PJI_REP_TOTAL_20 "PJI_REP_TOTAL_2"
, FACT.PJI_REP_TOTAL_3 "PJI_REP_TOTAL_3"
, FACT.PJI_REP_TOTAL_19 "PJI_REP_TOTAL_4"
, FACT.PJI_REP_TOTAL_4 "PJI_REP_TOTAL_5"
, FACT.PJI_REP_TOTAL_8 "PJI_REP_TOTAL_6"
, FACT.PJI_REP_TOTAL_3 "PJI_REP_TOTAL_7"
, FACT.PJI_REP_TOTAL_19 "PJI_REP_TOTAL_8"
, FACT.PJI_REP_TOTAL_3 "PJI_REP_TOTAL_9"
, FACT.PJI_REP_TOTAL_22 "PJI_REP_TOTAL_10"
, FACT.PJI_REP_TOTAL_17 "PJI_REP_TOTAL_11"
, FACT.PJI_REP_TOTAL_23 "PJI_REP_TOTAL_12"
, FACT.PJI_REP_TOTAL_19 "PJI_REP_TOTAL_13"
, FACT.PJI_REP_TOTAL_24 "PJI_REP_TOTAL_14"
, FACT.PJI_REP_TOTAL_18 "PJI_REP_TOTAL_15"
, FACT.PJI_REP_TOTAL_17 "PJI_REP_TOTAL_16" '
, P_SQL_STATEMENT => x_PMV_Sql
, P_PMV_OUTPUT => x_PMV_Output
, p_Region_Code => 'PJI_REP_PP2'
, p_PLSQL_Driver => 'PJI_PMV_PROFITABILITY.PLSQLDriver_PJI_REP_PPSUM'
, p_PLSQL_Driver_Params => ' <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <> '||
', NULL'||
', NULL'||
', <>'||
', <>'||
', NULL'
);
, P_SELECT_LIST =>
' FACT.AMOUNT_TYPE_LABEL "VIEWBY"
, FACT.P_ACTUAL "PJI_REP_MSR_7"
, FACT.P_ACTUAL1 "PJI_REP_MSR_18"
, FACT.P_ACTUAL2 "PJI_REP_MSR_19"
, FACT.PY_ACTUAL "PJI_REP_MSR_8"
, FACT.PY_ACTUAL1 "PJI_REP_MSR_20"
, FACT.PY_ACTUAL2 "PJI_REP_MSR_21"
, FACT.P_CHANGE1 "PJI_REP_MSR_9"
, FACT.P_CHANGE11 "PJI_REP_MSR_14"
, FACT.P_CHANGE12 "PJI_REP_MSR_15"
, FACT.P_FORECAST "PJI_REP_MSR_10"
, FACT.P_FORECAST1 "PJI_REP_MSR_22"
, FACT.P_FORECAST2 "PJI_REP_MSR_23"
, FACT.PY_FORECAST "PJI_REP_MSR_11"
, FACT.PY_FORECAST1 "PJI_REP_MSR_24"
, FACT.PY_FORECAST2 "PJI_REP_MSR_25"
, FACT.P_CHANGE2 "PJI_REP_MSR_12"
, FACT.P_CHANGE21 "PJI_REP_MSR_16"
, FACT.P_CHANGE22 "PJI_REP_MSR_17"
, FACT.AMOUNT_TYPE_CODE "PJI_REP_MSR_13"
, FACT.P_CHANGE1 "PJI_REP_MSR_1"
, FACT.P_CHANGE2 "PJI_REP_MSR_2"
, FACT.PJI_REP_URL1 "PJI_REP_URL1"
, FACT.PJI_REP_URL2 "PJI_REP_URL2" '
, P_SQL_STATEMENT => x_PMV_Sql
, P_PMV_OUTPUT => x_PMV_Output
, p_Region_Code => 'PJI_REP_PP3'
, p_PLSQL_Driver => 'PJI_PMV_PROFITABILITY.PLSQLDriver_PJI_REP_PP3'
, p_PLSQL_Driver_Params => ' <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <> '||
', NULL'||
', NULL'||
', <>'||
', <>'||
', NULL'
);
, P_SELECT_LIST =>
' FACT.CT_BURDENED_COST "PJI_REP_MSR_14"
, FACT.CT_FORECAST_BURDENED_COST "PJI_REP_MSR_15"
, FACT.CST_CHANGE_PERCENT "PJI_REP_MSR_1"
, FACT.FCST_CST_CHANGE_PERCENT "PJI_REP_MSR_2"
, FACT.BURDENED_COST "PJI_REP_MSR_3"
, FACT.FORECAST_BURDENED_COST "PJI_REP_MSR_4"
, FACT.BURDENED_COST "PJI_REP_MSR_8"
, FACT.CST_CHANGE_PERCENT "PJI_REP_MSR_9"
, FACT.FORECAST_BURDENED_COST "PJI_REP_MSR_12"
, FACT.FCST_CST_CHANGE_PERCENT "PJI_REP_MSR_10"
, FACT.PJI_REP_TOTAL_6 "PJI_REP_TOTAL_1"
, FACT.PJI_REP_TOTAL_8 "PJI_REP_TOTAL_2"
, FACT.PJI_REP_TOTAL_13 "PJI_REP_TOTAL_3"
, FACT.PJI_REP_TOTAL_21 "PJI_REP_TOTAL_4"
, FACT.PJI_REP_TOTAL_2 "PJI_REP_TOTAL_5"
, FACT.PJI_REP_TOTAL_4 "PJI_REP_TOTAL_6"
, FACT.PJI_REP_TOTAL_2 "PJI_REP_TOTAL_7"
, FACT.PJI_REP_TOTAL_13 "PJI_REP_TOTAL_8"
, FACT.PJI_REP_TOTAL_4 "PJI_REP_TOTAL_9"
, FACT.PJI_REP_TOTAL_21 "PJI_REP_TOTAL_10" '
, P_SQL_STATEMENT => x_PMV_Sql
, P_PMV_OUTPUT => x_PMV_Output
, p_Region_Code => 'PJI_REP_PP4'
, p_PLSQL_Driver => 'PJI_PMV_PROFITABILITY.PLSQLDriver_PJI_REP_PPSUM'
, p_PLSQL_Driver_Params => ' <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <> '||
', <>'||
', <>'||
', NULL'||
', NULL'||
', <> '
);
, 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_20"
, 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.REVENUE "PJI_REP_MSR_7"
, FACT.BURDENED_COST "PJI_REP_MSR_8"
, FACT.MARGIN "PJI_REP_MSR_9"
, FACT.FORECAST_REVENUE "PJI_REP_MSR_11"
, FACT.FORECAST_BURDENED_COST "PJI_REP_MSR_12"
, FACT.FORECAST_MARGIN "PJI_REP_MSR_13"
, FACT.BUDGET_MARGIN "PJI_REP_MSR_10"
, FACT.FORECAST_MARGIN_VARIANCE "PJI_REP_MSR_14"
, FACT.CURR_BGT_REVENUE "PJI_REP_MSR_15"
, FACT.CURR_BGT_BURDENED_COST "PJI_REP_MSR_16"
, FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_1"
, FACT.PJI_REP_TOTAL_2 "PJI_REP_TOTAL_2"
, FACT.PJI_REP_TOTAL_7 "PJI_REP_TOTAL_3"
, FACT.PJI_REP_TOTAL_5 "PJI_REP_TOTAL_4"
, FACT.PJI_REP_TOTAL_6 "PJI_REP_TOTAL_5"
, FACT.PJI_REP_TOTAL_10 "PJI_REP_TOTAL_6"
, FACT.PJI_REP_TOTAL_9 "PJI_REP_TOTAL_7"
, FACT.PJI_REP_TOTAL_11 "PJI_REP_TOTAL_8"
, FACT.PJI_REP_TOTAL_3 "PJI_REP_TOTAL_9"
, FACT.PJI_REP_TOTAL_4 "PJI_REP_TOTAL_10" '
, P_SQL_STATEMENT => x_PMV_Sql
, P_PMV_OUTPUT => x_PMV_Output
, p_Region_Code => 'PJI_REP_PP9'
, p_PLSQL_Driver => 'PJI_PMV_PROFITABILITY.PLSQLDriver_PJI_REP_PPDTL'
, p_PLSQL_Driver_Params => ' <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <> '||
', <> '||
', NULL'||
', NULL'||
', <>'||
', <>'||
', NULL'
);
, P_SELECT_LIST =>
' FACT.PROJECT_ID "PJI_REP_MSR18"
, FACT.PROJECT_NAME "VIEWBY"
, FACT.PROJECT_NUMBER "PJI_REP_MSR_2"
, FACT.URL_PARAMETERS01 "PJI_REP_MSR_20"
, 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.BURDENED_COST "PJI_REP_MSR_8"
, FACT.CURR_BGT_BURDENED_COST "PJI_REP_MSR_16"
, FACT.COST_VARIANCE "PJI_REP_MSR_17"
, FACT.FORECAST_BURDENED_COST "PJI_REP_MSR_12"
, FACT.FORECAST_COST_VARIANCE "PJI_REP_MSR_13"
, FACT.PJI_REP_TOTAL_2 "PJI_REP_TOTAL_1"
, FACT.PJI_REP_TOTAL_4 "PJI_REP_TOTAL_2"
, FACT.PJI_REP_TOTAL_8 "PJI_REP_TOTAL_3"
, FACT.PJI_REP_TOTAL_6 "PJI_REP_TOTAL_4"
, FACT.PJI_REP_TOTAL_12 "PJI_REP_TOTAL_5" '
, P_SQL_STATEMENT => x_PMV_Sql
, P_PMV_OUTPUT => x_PMV_Output
, p_Region_Code =>'PJI_REP_PP10'
, p_PLSQL_Driver => 'PJI_PMV_PROFITABILITY.PLSQLDriver_PJI_REP_PPDTL'
, p_PLSQL_Driver_Params => ' <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <> '||
', <> '||
', <>'||
', <>'||
', NULL'||
', NULL'||
', <> '
);
SELECT report_cost_type
INTO G_Report_Cost_Type
FROM pji_system_settings;
SELECT meaning
INTO l_label_rev
FROM pji_lookups
WHERE lookup_type = 'PJI_PROFITABILITY_MEASURES'
AND lookup_code = 'REVENUE';
SELECT meaning
INTO l_label_cost
FROM pji_lookups
WHERE lookup_type = 'PJI_PROFITABILITY_MEASURES'
AND lookup_code = 'COST';
SELECT meaning
INTO l_label_margin
FROM pji_lookups
WHERE lookup_type = 'PJI_PROFITABILITY_MEASURES'
AND lookup_code = 'MARGIN';
SELECT meaning
INTO l_label_mgnpct
FROM pji_lookups
WHERE lookup_type = 'PJI_PROFITABILITY_MEASURES'
AND lookup_code = 'MARGIN_PERCENT';
** -- PHASE I: Insert Separate Current and Prior Year Rows for Revenue, Cost and Margin
*/
/*
** Code the SQL statement for all of the following conditions
** 1. Current Year
** 2. Prior Year
**
** Note: This report does NOT require the generation of rows with zero's for the
** query parameter dimensions.
**
** Bulk-Collect the output into a pl/sql table to be returned to
** pmv.
*/
/*
** ORG Processing ---------------------------------------------------+
*/
/* ----------------------------- Case 1 truth table ------------------------------------ */
IF (l_Convert_Classification = 'N')
and (l_Convert_Event_Revenue_Type = 'N')
and (l_Convert_Work_Type = 'N')
THEN
SELECT PJI_REP_PP3(PHASE_CODE
,AMOUNT_TYPE_CODE
,AMOUNT_TYPE_LABEL
, SUM( P_ACTUAL )
, NULL
, NULL
, SUM( PY_ACTUAL )
, NULL
, NULL
, SUM( P_CHANGE1 )
, SUM( P_CHANGE11 )
, SUM( P_CHANGE12 )
, SUM( P_FORECAST )
, NULL
, NULL
, SUM( PY_FORECAST )
, NULL
, NULL
, SUM( P_CHANGE2 )
, SUM( P_CHANGE21 )
, SUM( P_CHANGE22 )
, NULL
, NULL )
BULK COLLECT INTO l_phase_tab
FROM
( SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 1 AS AMOUNT_TYPE_CODE
, l_label_rev AS AMOUNT_TYPE_LABEL
, DECODE(TIME.amount_type,1, revenue,0) AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, DECODE(TIME.amount_type,2, forecast_revenue,0) AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
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 */
1 AS PHASE_CODE
, 1 AS AMOUNT_TYPE_CODE
, l_label_rev AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, DECODE(TIME.amount_type,1,revenue,0) AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, DECODE(TIME.amount_type,2,forecast_revenue,0) AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
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 /*+ ORDERED */
1 AS PHASE_CODE
, 2 AS AMOUNT_TYPE_CODE
, l_label_cost AS AMOUNT_TYPE_LABEL
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', burdened_cost,
'RC', raw_cost, 0),0) AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, DECODE(TIME.amount_type,2,
DECODE(G_Report_Cost_Type, 'BC', forecast_burdened_cost,
'RC', forecast_raw_cost, 0),0) AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
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 */
1 AS PHASE_CODE
, 2 AS AMOUNT_TYPE_CODE
, l_label_cost AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', burdened_cost,
'RC', raw_cost, 0),0) AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, DECODE(TIME.amount_type,2,
DECODE(G_Report_Cost_Type, 'BC', forecast_burdened_cost,
'RC', forecast_raw_cost, 0),0) AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
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 /*+ ORDERED */
1 AS PHASE_CODE
, 3 AS AMOUNT_TYPE_CODE
, l_label_margin AS AMOUNT_TYPE_LABEL
,DECODE(TIME.amount_type,1,
( NVL(revenue,0) - NVL(DECODE(G_Report_Cost_Type, 'BC', burdened_cost, 'RC', raw_cost, 0),0) ), 0)
AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, DECODE(TIME.amount_type,2, (nvl(forecast_revenue,0) -
NVL( DECODE(G_Report_Cost_Type, 'BC', forecast_burdened_cost,
'RC', forecast_raw_cost, 0),0)), 0) AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
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 */
1 AS PHASE_CODE
, 3 AS AMOUNT_TYPE_CODE
, l_label_margin AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, DECODE(TIME.amount_type,1, (nvl(revenue,0) -
NVL(DECODE(G_Report_Cost_Type, 'BC', burdened_cost,
'RC', raw_cost, 0),0)), 0) AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, DECODE(TIME.amount_type,2, (nvl(forecast_revenue,0) -
NVL(DECODE(G_Report_Cost_Type, 'BC', forecast_burdened_cost,
'RC', forecast_raw_cost, 0),0)), 0) AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
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
1 AS PHASE_CODE
, 1 AS AMOUNT_TYPE_CODE
, l_label_rev AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM dual
UNION ALL
SELECT
1 AS PHASE_CODE
, 2 AS AMOUNT_TYPE_CODE
, l_label_cost AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM dual
UNION ALL
SELECT
1 AS PHASE_CODE
, 3 AS AMOUNT_TYPE_CODE
, l_label_margin AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM dual
)
WHERE 1 = 1
GROUP BY PHASE_CODE,AMOUNT_TYPE_CODE,AMOUNT_TYPE_LABEL;
SELECT PJI_REP_PP3(PHASE_CODE
,AMOUNT_TYPE_CODE
,AMOUNT_TYPE_LABEL
, SUM( P_ACTUAL )
, NULL
, NULL
, SUM( PY_ACTUAL )
, NULL
, NULL
, SUM( P_CHANGE1 )
, SUM( P_CHANGE11 )
, SUM( P_CHANGE12 )
, SUM( P_FORECAST )
, NULL
, NULL
, SUM( PY_FORECAST )
, NULL
, NULL
, SUM( P_CHANGE2 )
, SUM( P_CHANGE21 )
, SUM( P_CHANGE22 )
, NULL
, NULL)
BULK COLLECT INTO l_phase_tab
FROM
( SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 1 AS AMOUNT_TYPE_CODE
, l_label_rev AS AMOUNT_TYPE_LABEL
, DECODE(TIME.amount_type,1, revenue, 0) AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, DECODE(TIME.amount_type,2, forecast_revenue,0) AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
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 FCT.PROJECT_CLASS_ID = CLS.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 1 AS AMOUNT_TYPE_CODE
, l_label_rev AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, DECODE(TIME.amount_type,1, revenue, 0) AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, DECODE(TIME.amount_type,2, forecast_revenue, 0) AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
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 FCT.PROJECT_CLASS_ID = CLS.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 2 AS AMOUNT_TYPE_CODE
, l_label_cost AS AMOUNT_TYPE_LABEL
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', burdened_cost,
'RC', raw_cost, 0), 0) AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, DECODE(TIME.amount_type,2,
DECODE(G_Report_Cost_Type, 'BC', forecast_burdened_cost,
'RC', forecast_raw_cost, 0), 0) AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
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 FCT.PROJECT_CLASS_ID = CLS.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 2 AS AMOUNT_TYPE_CODE
, l_label_cost AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', burdened_cost,
'RC', raw_cost, 0), 0) AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, DECODE(TIME.amount_type,2,
DECODE(G_Report_Cost_Type, 'BC', forecast_burdened_cost,
'RC', forecast_raw_cost, 0), 0) AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
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 FCT.PROJECT_CLASS_ID = CLS.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 3 AS AMOUNT_TYPE_CODE
, l_label_margin AS AMOUNT_TYPE_LABEL
, DECODE(TIME.amount_type,1, (revenue -
DECODE(G_Report_Cost_Type, 'BC', burdened_cost,
'RC', raw_cost, 0)), 0) AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, DECODE(TIME.amount_type,2, (forecast_revenue -
DECODE(G_Report_Cost_Type, 'BC', forecast_burdened_cost,
'RC', forecast_raw_cost, 0)), 0) AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
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 FCT.PROJECT_CLASS_ID = CLS.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 3 AS AMOUNT_TYPE_CODE
, l_label_margin AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, DECODE(TIME.amount_type,1, (revenue -
DECODE(G_Report_Cost_Type, 'BC', burdened_cost,
'RC', raw_cost, 0)), 0) AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, DECODE(TIME.amount_type,2, (forecast_revenue -
DECODE(G_Report_Cost_Type, 'BC', forecast_burdened_cost,
'RC', forecast_raw_cost, 0)), 0) AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
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 FCT.PROJECT_CLASS_ID = CLS.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT
1 AS PHASE_CODE
, 1 AS AMOUNT_TYPE_CODE
, l_label_rev AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM dual
UNION ALL
SELECT
1 AS PHASE_CODE
,2 AS AMOUNT_TYPE_CODE
,l_label_cost AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM dual
UNION ALL
SELECT
1 AS PHASE_CODE
,3 AS AMOUNT_TYPE_CODE
,l_label_margin AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM dual
)
WHERE 1 = 1
GROUP BY PHASE_CODE,AMOUNT_TYPE_CODE,AMOUNT_TYPE_LABEL;
SELECT PJI_REP_PP3(PHASE_CODE
,AMOUNT_TYPE_CODE
,AMOUNT_TYPE_LABEL
, SUM( P_ACTUAL )
, NULL
, NULL
, SUM( PY_ACTUAL )
, NULL
, NULL
, SUM( P_CHANGE1 )
, SUM( P_CHANGE11 )
, SUM( P_CHANGE12 )
, SUM( P_FORECAST )
, NULL
, NULL
, SUM( PY_FORECAST )
, NULL
, NULL
, SUM( P_CHANGE2 )
, SUM( P_CHANGE21 )
, SUM( P_CHANGE22 )
, NULL
, NULL)
BULK COLLECT INTO l_phase_tab
FROM
( SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 1 AS AMOUNT_TYPE_CODE
, l_label_rev AS AMOUNT_TYPE_LABEL
, DECODE(TIME.amount_type,1, revenue, 0) AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, null AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
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 FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'RT'
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 1 AS AMOUNT_TYPE_CODE
, l_label_rev AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, DECODE(TIME.amount_type,1, revenue, 0) AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, null AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
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 FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'RT'
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 2 AS AMOUNT_TYPE_CODE
, l_label_cost AS AMOUNT_TYPE_LABEL
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', burdened_cost,
'RC', raw_cost, 0), 0) AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, null AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
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 FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'RT'
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 2 AS AMOUNT_TYPE_CODE
, l_label_cost AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', burdened_cost,
'RC', raw_cost, 0), 0) AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, null AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
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 FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'RT'
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 3 AS AMOUNT_TYPE_CODE
, l_label_margin AS AMOUNT_TYPE_LABEL
, DECODE(TIME.amount_type,1, (revenue -
DECODE(G_Report_Cost_Type, 'BC', burdened_cost,
'RC', raw_cost, 0)), 0) AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, null AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
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 FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'RT'
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 3 AS AMOUNT_TYPE_CODE
, l_label_margin AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, DECODE(TIME.amount_type,1, (revenue -
DECODE(G_Report_Cost_Type, 'BC', burdened_cost,
'RC', raw_cost, 0)), 0) AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, null AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
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 FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'RT'
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT
1 AS PHASE_CODE
, 1 AS AMOUNT_TYPE_CODE
, l_label_rev AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM dual
UNION ALL
SELECT
1 AS PHASE_CODE
,2 AS AMOUNT_TYPE_CODE
,l_label_cost AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM dual
UNION ALL
SELECT
1 AS PHASE_CODE
,3 AS AMOUNT_TYPE_CODE
,l_label_margin AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM dual
)
WHERE 1 = 1
GROUP BY PHASE_CODE,AMOUNT_TYPE_CODE,AMOUNT_TYPE_LABEL;
SELECT PJI_REP_PP3(PHASE_CODE
,AMOUNT_TYPE_CODE
,AMOUNT_TYPE_LABEL
, SUM( P_ACTUAL )
, NULL
, NULL
, SUM( PY_ACTUAL )
, NULL
, NULL
, SUM( P_CHANGE1 )
, SUM( P_CHANGE11 )
, SUM( P_CHANGE12 )
, SUM( P_FORECAST )
, NULL
, NULL
, SUM( PY_FORECAST )
, NULL
, NULL
, SUM( P_CHANGE2 )
, SUM( P_CHANGE21 )
, SUM( P_CHANGE22 )
, NULL
, NULL)
BULK COLLECT INTO l_phase_tab
FROM
( SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 1 AS AMOUNT_TYPE_CODE
, l_label_rev AS AMOUNT_TYPE_LABEL
, null AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, null AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
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 FCT.WORK_TYPE_ID = WT.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 1 AS AMOUNT_TYPE_CODE
, l_label_rev AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, null AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, null AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
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 FCT.WORK_TYPE_ID = WT.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 2 AS AMOUNT_TYPE_CODE
, l_label_cost AS AMOUNT_TYPE_LABEL
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', burdened_cost,
'RC', raw_cost, 0), 0) AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, null AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
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 FCT.WORK_TYPE_ID = WT.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 2 AS AMOUNT_TYPE_CODE
, l_label_cost AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', burdened_cost,
'RC', raw_cost, 0), 0) AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, null AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
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 FCT.WORK_TYPE_ID = WT.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 3 AS AMOUNT_TYPE_CODE
, l_label_margin AS AMOUNT_TYPE_LABEL
, null AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, null AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
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 FCT.WORK_TYPE_ID = WT.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 3 AS AMOUNT_TYPE_CODE
, l_label_margin AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, null AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, null AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
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 FCT.WORK_TYPE_ID = WT.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT
1 AS PHASE_CODE
, 1 AS AMOUNT_TYPE_CODE
, l_label_rev AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM dual
UNION ALL
SELECT
1 AS PHASE_CODE
,2 AS AMOUNT_TYPE_CODE
,l_label_cost AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM dual
UNION ALL
SELECT
1 AS PHASE_CODE
,3 AS AMOUNT_TYPE_CODE
,l_label_margin AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM dual
)
WHERE 1 = 1
GROUP BY PHASE_CODE,AMOUNT_TYPE_CODE,AMOUNT_TYPE_LABEL;
SELECT PJI_REP_PP3(PHASE_CODE
,AMOUNT_TYPE_CODE
,AMOUNT_TYPE_LABEL
, SUM( P_ACTUAL )
, NULL
, NULL
, SUM( PY_ACTUAL )
, NULL
, NULL
, SUM( P_CHANGE1 )
, SUM( P_CHANGE11 )
, SUM( P_CHANGE12 )
, SUM( P_FORECAST )
, NULL
, NULL
, SUM( PY_FORECAST )
, NULL
, NULL
, SUM( P_CHANGE2 )
, SUM( P_CHANGE21 )
, SUM( P_CHANGE22 )
, NULL
, NULL)
BULK COLLECT INTO l_phase_tab
FROM
( SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 1 AS AMOUNT_TYPE_CODE
, l_label_rev AS AMOUNT_TYPE_LABEL
, DECODE(TIME.amount_type,1, revenue, 0) AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, null AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_CLSO_ET_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 FCT.PROJECT_CLASS_ID = CLS.ID
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'RT'
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 1 AS AMOUNT_TYPE_CODE
, l_label_rev AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, DECODE(TIME.amount_type,1, revenue, 0) AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, null AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_CLSO_ET_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 FCT.PROJECT_CLASS_ID = CLS.ID
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'RT'
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 2 AS AMOUNT_TYPE_CODE
, l_label_cost AS AMOUNT_TYPE_LABEL
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', burdened_cost,
'RC', raw_cost, 0), 0) AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, null AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_CLSO_ET_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 FCT.PROJECT_CLASS_ID = CLS.ID
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'RT'
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 2 AS AMOUNT_TYPE_CODE
, l_label_cost AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', burdened_cost,
'RC', raw_cost, 0), 0) AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, null AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_CLSO_ET_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 FCT.PROJECT_CLASS_ID = CLS.ID
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'RT'
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 3 AS AMOUNT_TYPE_CODE
, l_label_margin AS AMOUNT_TYPE_LABEL
, DECODE(TIME.amount_type,1, (revenue -
DECODE(G_Report_Cost_Type, 'BC', burdened_cost,
'RC', raw_cost, 0)), 0) AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, null AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_CLSO_ET_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 FCT.PROJECT_CLASS_ID = CLS.ID
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'RT'
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 3 AS AMOUNT_TYPE_CODE
, l_label_margin AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, DECODE(TIME.amount_type,1, (revenue -
DECODE(G_Report_Cost_Type, 'BC', burdened_cost,
'RC', raw_cost, 0)), 0) AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, null AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_CLSO_ET_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 FCT.PROJECT_CLASS_ID = CLS.ID
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'RT'
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT
1 AS PHASE_CODE
, 1 AS AMOUNT_TYPE_CODE
, l_label_rev AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM dual
UNION ALL
SELECT
1 AS PHASE_CODE
,2 AS AMOUNT_TYPE_CODE
,l_label_cost AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM dual
UNION ALL
SELECT
1 AS PHASE_CODE
,3 AS AMOUNT_TYPE_CODE
,l_label_margin AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM dual
)
WHERE 1 = 1
GROUP BY PHASE_CODE,AMOUNT_TYPE_CODE,AMOUNT_TYPE_LABEL;
SELECT PJI_REP_PP3(PHASE_CODE
,AMOUNT_TYPE_CODE
,AMOUNT_TYPE_LABEL
, SUM( P_ACTUAL )
, NULL
, NULL
, SUM( PY_ACTUAL )
, NULL
, NULL
, SUM( P_CHANGE1 )
, SUM( P_CHANGE11 )
, SUM( P_CHANGE12 )
, SUM( P_FORECAST )
, NULL
, NULL
, SUM( PY_FORECAST )
, NULL
, NULL
, SUM( P_CHANGE2 )
, SUM( P_CHANGE21 )
, SUM( P_CHANGE22 )
, NULL
, NULL)
BULK COLLECT INTO l_phase_tab
FROM
( SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 1 AS AMOUNT_TYPE_CODE
, l_label_rev AS AMOUNT_TYPE_LABEL
, null AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, null AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, 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 FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'RT'
AND FCT.WORK_TYPE_ID = WT.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 1 AS AMOUNT_TYPE_CODE
, l_label_rev AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, null AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, null AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, 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 FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'RT'
AND FCT.WORK_TYPE_ID = WT.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 2 AS AMOUNT_TYPE_CODE
, l_label_cost AS AMOUNT_TYPE_LABEL
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', burdened_cost,
'RC', raw_cost, 0), 0) AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, null AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, 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 FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'RT'
AND FCT.WORK_TYPE_ID = WT.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 2 AS AMOUNT_TYPE_CODE
, l_label_cost AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', burdened_cost,
'RC', raw_cost, 0), 0) AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, null AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, 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 FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'RT'
AND FCT.WORK_TYPE_ID = WT.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 3 AS AMOUNT_TYPE_CODE
, l_label_margin AS AMOUNT_TYPE_LABEL
, null AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, null AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, 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 FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'RT'
AND FCT.WORK_TYPE_ID = WT.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 3 AS AMOUNT_TYPE_CODE
, l_label_margin AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, null AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, null AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, 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 FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'RT'
AND FCT.WORK_TYPE_ID = WT.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT
1 AS PHASE_CODE
, 1 AS AMOUNT_TYPE_CODE
, l_label_rev AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM dual
UNION ALL
SELECT
1 AS PHASE_CODE
,2 AS AMOUNT_TYPE_CODE
,l_label_cost AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM dual
UNION ALL
SELECT
1 AS PHASE_CODE
,3 AS AMOUNT_TYPE_CODE
,l_label_margin AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM dual
)
WHERE 1 = 1
GROUP BY PHASE_CODE,AMOUNT_TYPE_CODE,AMOUNT_TYPE_LABEL;
SELECT PJI_REP_PP3(PHASE_CODE
,AMOUNT_TYPE_CODE
,AMOUNT_TYPE_LABEL
, SUM( P_ACTUAL )
, NULL
, NULL
, SUM( PY_ACTUAL )
, NULL
, NULL
, SUM( P_CHANGE1 )
, SUM( P_CHANGE11 )
, SUM( P_CHANGE12 )
, SUM( P_FORECAST )
, NULL
, NULL
, SUM( PY_FORECAST )
, NULL
, NULL
, SUM( P_CHANGE2 )
, SUM( P_CHANGE21 )
, SUM( P_CHANGE22 )
, NULL
, NULL)
BULK COLLECT INTO l_phase_tab
FROM
( SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 1 AS AMOUNT_TYPE_CODE
, l_label_rev AS AMOUNT_TYPE_LABEL
, null AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, null AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_WT_DIM_TMP WT
, PJI_FP_CLSO_ET_WT_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 FCT.PROJECT_CLASS_ID = CLS.ID
AND FCT.WORK_TYPE_ID = WT.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 1 AS AMOUNT_TYPE_CODE
, l_label_rev AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, null AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, null AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_WT_DIM_TMP WT
, PJI_FP_CLSO_ET_WT_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 FCT.PROJECT_CLASS_ID = CLS.ID
AND FCT.WORK_TYPE_ID = WT.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 2 AS AMOUNT_TYPE_CODE
, l_label_cost AS AMOUNT_TYPE_LABEL
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', burdened_cost,
'RC', raw_cost, 0), 0) AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, null AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_WT_DIM_TMP WT
, PJI_FP_CLSO_ET_WT_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 FCT.PROJECT_CLASS_ID = CLS.ID
AND FCT.WORK_TYPE_ID = WT.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 2 AS AMOUNT_TYPE_CODE
, l_label_cost AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', burdened_cost,
'RC', raw_cost, 0), 0) AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, null AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_WT_DIM_TMP WT
, PJI_FP_CLSO_ET_WT_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 FCT.PROJECT_CLASS_ID = CLS.ID
AND FCT.WORK_TYPE_ID = WT.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 3 AS AMOUNT_TYPE_CODE
, l_label_margin AS AMOUNT_TYPE_LABEL
, null AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, null AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_WT_DIM_TMP WT
, PJI_FP_CLSO_ET_WT_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 FCT.PROJECT_CLASS_ID = CLS.ID
AND FCT.WORK_TYPE_ID = WT.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 3 AS AMOUNT_TYPE_CODE
, l_label_margin AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, null AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, null AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_WT_DIM_TMP WT
, PJI_FP_CLSO_ET_WT_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 FCT.PROJECT_CLASS_ID = CLS.ID
AND FCT.WORK_TYPE_ID = WT.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT
1 AS PHASE_CODE
, 1 AS AMOUNT_TYPE_CODE
, l_label_rev AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM dual
UNION ALL
SELECT
1 AS PHASE_CODE
,2 AS AMOUNT_TYPE_CODE
,l_label_cost AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM dual
UNION ALL
SELECT
1 AS PHASE_CODE
,3 AS AMOUNT_TYPE_CODE
,l_label_margin AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM dual
)
WHERE 1 = 1
GROUP BY PHASE_CODE,AMOUNT_TYPE_CODE,AMOUNT_TYPE_LABEL;
SELECT PJI_REP_PP3(PHASE_CODE
,AMOUNT_TYPE_CODE
,AMOUNT_TYPE_LABEL
, SUM( P_ACTUAL )
, NULL
, NULL
, SUM( PY_ACTUAL )
, NULL
, NULL
, SUM( P_CHANGE1 )
, SUM( P_CHANGE11 )
, SUM( P_CHANGE12 )
, SUM( P_FORECAST )
, NULL
, NULL
, SUM( PY_FORECAST )
, NULL
, NULL
, SUM( P_CHANGE2 )
, SUM( P_CHANGE21 )
, SUM( P_CHANGE22 )
, NULL
, NULL)
BULK COLLECT INTO l_phase_tab
FROM
( SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 1 AS AMOUNT_TYPE_CODE
, l_label_rev AS AMOUNT_TYPE_LABEL
, null AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, null AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_PMV_WT_DIM_TMP WT
, PJI_FP_CLSO_ET_WT_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 FCT.PROJECT_CLASS_ID = CLS.ID
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'RT'
AND FCT.WORK_TYPE_ID = WT.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 1 AS AMOUNT_TYPE_CODE
, l_label_rev AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, null AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, null AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_PMV_WT_DIM_TMP WT
, PJI_FP_CLSO_ET_WT_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 FCT.PROJECT_CLASS_ID = CLS.ID
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'RT'
AND FCT.WORK_TYPE_ID = WT.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 2 AS AMOUNT_TYPE_CODE
, l_label_cost AS AMOUNT_TYPE_LABEL
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', burdened_cost,
'RC', raw_cost, 0), 0) AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, null AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_PMV_WT_DIM_TMP WT
, PJI_FP_CLSO_ET_WT_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 FCT.PROJECT_CLASS_ID = CLS.ID
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'RT'
AND FCT.WORK_TYPE_ID = WT.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 2 AS AMOUNT_TYPE_CODE
, l_label_cost AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', burdened_cost,
'RC', raw_cost, 0), 0) AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, null AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_PMV_WT_DIM_TMP WT
, PJI_FP_CLSO_ET_WT_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 FCT.PROJECT_CLASS_ID = CLS.ID
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'RT'
AND FCT.WORK_TYPE_ID = WT.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 3 AS AMOUNT_TYPE_CODE
, l_label_margin AS AMOUNT_TYPE_LABEL
, null AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, null AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_PMV_WT_DIM_TMP WT
, PJI_FP_CLSO_ET_WT_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 FCT.PROJECT_CLASS_ID = CLS.ID
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'RT'
AND FCT.WORK_TYPE_ID = WT.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT /*+ ORDERED */
1 AS PHASE_CODE
, 3 AS AMOUNT_TYPE_CODE
, l_label_margin AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, null AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, null AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_PMV_WT_DIM_TMP WT
, PJI_FP_CLSO_ET_WT_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 FCT.PROJECT_CLASS_ID = CLS.ID
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'RT'
AND FCT.WORK_TYPE_ID = WT.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
UNION ALL
SELECT
1 AS PHASE_CODE
, 1 AS AMOUNT_TYPE_CODE
, l_label_rev AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM dual
UNION ALL
SELECT
1 AS PHASE_CODE
,2 AS AMOUNT_TYPE_CODE
,l_label_cost AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM dual
UNION ALL
SELECT
1 AS PHASE_CODE
,3 AS AMOUNT_TYPE_CODE
,l_label_margin AS AMOUNT_TYPE_LABEL
, 0 AS P_ACTUAL
, 0 AS PY_ACTUAL
, null AS P_CHANGE1
, null AS P_CHANGE11
, null AS P_CHANGE12
, 0 AS P_FORECAST
, 0 AS PY_FORECAST
, null AS P_CHANGE2
, null AS P_CHANGE21
, null AS P_CHANGE22
FROM dual
)
WHERE 1 = 1
GROUP BY PHASE_CODE,AMOUNT_TYPE_CODE,AMOUNT_TYPE_LABEL;
SELECT report_cost_type
INTO G_Report_Cost_Type
FROM pji_system_settings;
** PHASE I: Insert Separate Current and Prior Year Rows for Revenue, Cost and Margin
*/
IF p_Project_IDS IS NULL THEN
/*
** Code the SQL statement for all of the following conditions
** 1. Current Year
**
** Bulk-Collect the output into a pl/sql table to be returned to
** pmv.
**
*/
/*
** logic builds an intersection tmp table between the class codes tmp table and the
** project_class mapping table. The intersection table is equi-joined in the following
** select statement.
*/
BEGIN
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;
INSERT into pji_pmv_prj_dim_tmp (id, name)
SELECT DISTINCT prj.project_id, '-1' name
FROM
pji_pmv_orgz_dim_tmp org
, pa_projects_all prj
WHERE
prj.carrying_out_organization_id = org.ID;
SELECT PJI_REP_PPDTL(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(REVENUE)
, SUM(BURDENED_COST)
, SUM(CURR_BGT_REVENUE)
, SUM(CURR_BGT_BURDENED_COST)
, SUM(FORECAST_REVENUE)
, SUM(FORECAST_BURDENED_COST)
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 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,FCT.REVENUE, 0) AS REVENUE
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS BURDENED_COST
, DECODE(TIME.amount_type,2,FCT.CURR_BGT_REVENUE, 0) AS CURR_BGT_REVENUE
, DECODE(TIME.amount_type,2,
DECODE(G_Report_Cost_Type, 'BC', fct.curr_bgt_burdened_cost,
'RC', fct.curr_bgt_raw_cost, 0), 0) AS CURR_BGT_BURDENED_COST
, DECODE(TIME.amount_type,2,FCT.FORECAST_REVENUE, 0) AS FORECAST_REVENUE
, DECODE(TIME.amount_type,2,
DECODE(G_Report_Cost_Type, 'BC', fct.forecast_burdened_cost,
'RC', fct.forecast_raw_cost, 0), 0) AS FORECAST_BURDENED_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_PPDTL(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(REVENUE)
, SUM(BURDENED_COST)
, SUM(CURR_BGT_REVENUE)
, SUM(CURR_BGT_BURDENED_COST)
, SUM(FORECAST_REVENUE)
, SUM(FORECAST_BURDENED_COST)
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 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,FCT.REVENUE, 0) AS REVENUE
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS BURDENED_COST
, DECODE(TIME.amount_type,2,FCT.CURR_BGT_REVENUE, 0) AS CURR_BGT_REVENUE
, DECODE(TIME.amount_type,2,
DECODE(G_Report_Cost_Type, 'BC',fct.curr_bgt_burdened_cost,
'RC',fct.curr_bgt_raw_cost, 0), 0) AS CURR_BGT_BURDENED_COST
, DECODE(TIME.amount_type,2,FCT.FORECAST_REVENUE, 0) AS FORECAST_REVENUE
, DECODE(TIME.amount_type,2,
DECODE(G_Report_Cost_Type, 'BC', fct.forecast_burdened_cost,
'RC', fct.forecast_raw_cost, 0), 0) AS FORECAST_BURDENED_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_PPDTL(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(REVENUE)
, SUM(BURDENED_COST)
, SUM(CURR_BGT_REVENUE)
, SUM(CURR_BGT_BURDENED_COST)
, SUM(FORECAST_REVENUE)
, SUM(FORECAST_BURDENED_COST)
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 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,FCT.REVENUE, 0) AS REVENUE
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS BURDENED_COST
, NULL AS CURR_BGT_REVENUE
, NULL AS CURR_BGT_BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_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 =
decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
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_PPDTL(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(REVENUE)
, SUM(BURDENED_COST)
, SUM(CURR_BGT_REVENUE)
, SUM(CURR_BGT_BURDENED_COST)
, SUM(FORECAST_REVENUE)
, SUM(FORECAST_BURDENED_COST)
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 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
, null AS REVENUE
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS BURDENED_COST
, NULL AS CURR_BGT_REVENUE
, NULL AS CURR_BGT_BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_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_PPDTL(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(REVENUE)
, SUM(BURDENED_COST)
, SUM(CURR_BGT_REVENUE)
, SUM(CURR_BGT_BURDENED_COST)
, SUM(FORECAST_REVENUE)
, SUM(FORECAST_BURDENED_COST)
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 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,FCT.REVENUE, 0) AS REVENUE
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS BURDENED_COST
, NULL AS CURR_BGT_REVENUE
, NULL AS CURR_BGT_BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_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 =
decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
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_PPDTL(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(REVENUE)
, SUM(BURDENED_COST)
, SUM(CURR_BGT_REVENUE)
, SUM(CURR_BGT_BURDENED_COST)
, SUM(FORECAST_REVENUE)
, SUM(FORECAST_BURDENED_COST)
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 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
, null AS REVENUE
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS BURDENED_COST
, NULL AS CURR_BGT_REVENUE
, NULL AS CURR_BGT_BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_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 =
decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
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_PPDTL(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(REVENUE)
, SUM(BURDENED_COST)
, SUM(CURR_BGT_REVENUE)
, SUM(CURR_BGT_BURDENED_COST)
, SUM(FORECAST_REVENUE)
, SUM(FORECAST_BURDENED_COST)
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 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
, null AS REVENUE
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS BURDENED_COST
, NULL AS CURR_BGT_REVENUE
, NULL AS CURR_BGT_BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_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_PPDTL(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(REVENUE)
, SUM(BURDENED_COST)
, SUM(CURR_BGT_REVENUE)
, SUM(CURR_BGT_BURDENED_COST)
, SUM(FORECAST_REVENUE)
, SUM(FORECAST_BURDENED_COST)
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 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
, null AS REVENUE
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS BURDENED_COST
, NULL AS CURR_BGT_REVENUE
, NULL AS CURR_BGT_BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_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 =
decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
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;
SELECT report_cost_type
INTO G_Report_Cost_Type
FROM pji_system_settings;
** 1) The Org select forces creates OU and organization rows.
** 2) The Class/Code select creates OU and organization rows as well as class/code
** rows. The OU/organization forced rows are required because the user can
** VIEWBY OU/Organization and filter by Class/Code.
**
** Bulk-Collect the output into a pl/sql table to be returned to
** pmv.
*/
/*
** ORG Processing ---------------------------------------------------+
*/
/* ----------------------------------- Case 1 truth table ------------------------------------- */
IF (l_Convert_Classification = 'N')
and (l_Convert_Expenditure_Type = 'N')
and (l_Convert_Event_Revenue_Type = 'N')
and (l_Convert_Work_Type = 'N')
THEN
SELECT PJI_REP_PPSUM(ORG_ID
, ORGANIZATION_ID
, PROJECT_CLASS_ID
, EXPENDITURE_CATEGORY
, EXPENDITURE_TYPE_ID
, REVENUE_CATEGORY
, REVENUE_TYPE_ID
, WORK_TYPE_ID
, SUM(REVENUE)
, SUM(BURDENED_COST)
, SUM(FORECAST_REVENUE)
, SUM(FORECAST_BURDENED_COST)
, SUM(CT_REVENUE)
, SUM(CT_BURDENED_COST)
, SUM(CT_FORECAST_REVENUE)
, SUM(CT_FORECAST_BURDENED_COST)
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
)
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 REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, DECODE(TIME.amount_type,1,REVENUE,0) AS REVENUE
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0),0) AS BURDENED_COST
, DECODE(TIME.amount_type,2,FORECAST_REVENUE,0) AS FORECAST_REVENUE
, DECODE(TIME.amount_type,2,
DECODE(G_Report_Cost_Type, 'BC', fct.forecast_burdened_cost,
'RC', fct.forecast_raw_cost, 0),0) AS
FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, 0 AS CT_FORECAST_REVENUE
, 0 AS CT_FORECAST_BURDENED_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 -- CURRENT Approved Budgets
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 REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, 0 AS FORECAST_REVENUE
, 0 AS FORECAST_BURDENED_COST
, DECODE(TIME.amount_type,2,CURR_BGT_REVENUE,0) AS CT_REVENUE
, DECODE(TIME.amount_type,2,
DECODE(G_Report_Cost_Type, 'BC', fct.curr_bgt_burdened_cost,
'RC', fct.curr_bgt_raw_cost, 0)
,0) AS CT_BURDENED_COST
, DECODE(TIME.amount_type,2,CURR_BGT_REVENUE,0) AS CT_FORECAST_REVENUE
, DECODE(TIME.amount_type,2,
DECODE(G_Report_Cost_Type, 'BC', fct.curr_bgt_burdened_cost,
'RC', fct.curr_bgt_raw_cost, 0)
,0) AS CT_FORECAST_BURDENED_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
and p_Time_Comparison_Type = 'BUDGET'
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 REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, 0 AS FORECAST_REVENUE
, 0 AS FORECAST_BURDENED_COST
, DECODE(TIME.amount_type,1,REVENUE,0) AS CT_REVENUE
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0),0) AS CT_BURDENED_COST
, DECODE(TIME.amount_type,2,FORECAST_REVENUE,0) AS CT_FORECAST_REVENUE
, DECODE(TIME.amount_type,2,
DECODE(G_Report_Cost_Type, 'BC', fct.forecast_burdened_cost,
'RC', fct.forecast_raw_cost, 0),0) AS CT_FORECAST_BURDENED_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
and p_Time_Comparison_Type <> 'BUDGET'
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 REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, 0 AS FORECAST_REVENUE
, 0 AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, 0 AS CT_FORECAST_REVENUE
, 0 AS CT_FORECAST_BURDENED_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 REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, 0 AS FORECAST_REVENUE
, 0 AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, 0 AS CT_FORECAST_REVENUE
, 0 AS CT_FORECAST_BURDENED_COST
FROM PJI_PMV_ORGZ_DIM_TMP HORG
WHERE HORG.NAME <> '-1'
)
GROUP BY ORG_ID, ORGANIZATION_ID, PROJECT_CLASS_ID, EXPENDITURE_CATEGORY,EXPENDITURE_TYPE_ID,REVENUE_CATEGORY,
REVENUE_TYPE_ID, WORK_TYPE_ID;
SELECT PJI_REP_PPSUM(ORG_ID
, ORGANIZATION_ID
, PROJECT_CLASS_ID
, EXPENDITURE_CATEGORY
, EXPENDITURE_TYPE_ID
, REVENUE_CATEGORY
, REVENUE_TYPE_ID
, WORK_TYPE_ID
, SUM(REVENUE)
, SUM(BURDENED_COST)
, SUM(FORECAST_REVENUE)
, SUM(FORECAST_BURDENED_COST)
, SUM(CT_REVENUE)
, SUM(CT_BURDENED_COST)
, SUM(CT_FORECAST_REVENUE)
, SUM(CT_FORECAST_BURDENED_COST)
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
)
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 REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, DECODE(TIME.amount_type,1,REVENUE, 0) AS REVENUE
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS BURDENED_COST
, DECODE(TIME.amount_type,2,FORECAST_REVENUE, 0) AS FORECAST_REVENUE
, DECODE(TIME.amount_type,2,
DECODE(G_Report_Cost_Type, 'BC', fct.forecast_burdened_cost,
'RC', fct.forecast_raw_cost, 0), 0) AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, 0 AS CT_FORECAST_REVENUE
, 0 AS CT_FORECAST_BURDENED_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 -- CURRENT Approved Budgets
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 REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, 0 AS FORECAST_REVENUE
, 0 AS FORECAST_BURDENED_COST
, DECODE(TIME.amount_type,2,CURR_BGT_REVENUE, 0) AS CT_REVENUE
, DECODE(TIME.amount_type,2,
DECODE(G_Report_Cost_Type, 'BC', fct.curr_bgt_burdened_cost,
'RC', fct.curr_bgt_raw_cost, 0), 0) AS CT_BURDENED_COST
, DECODE(TIME.amount_type,2,CURR_BGT_REVENUE, 0) AS CT_FORECAST_REVENUE
, DECODE(TIME.amount_type,2,
DECODE(G_Report_Cost_Type, 'BC', fct.curr_bgt_burdened_cost,
'RC', fct.curr_bgt_raw_cost, 0), 0) AS
CT_FORECAST_BURDENED_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
and p_Time_Comparison_Type = 'BUDGET'
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 REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, 0 AS FORECAST_REVENUE
, 0 AS FORECAST_BURDENED_COST
, DECODE(TIME.amount_type,1,REVENUE, 0) AS CT_REVENUE
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS CT_BURDENED_COST
, DECODE(TIME.amount_type,2,FORECAST_REVENUE, 0) AS CT_FORECAST_REVENUE
, DECODE(TIME.amount_type,2,
DECODE(G_Report_Cost_Type, 'BC', fct.forecast_burdened_cost,
'RC', fct.forecast_raw_cost, 0), 0) AS
CT_FORECAST_BURDENED_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
and p_Time_Comparison_Type <> 'BUDGET'
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 REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, 0 AS FORECAST_REVENUE
, 0 AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, 0 AS CT_FORECAST_REVENUE
, 0 AS CT_FORECAST_BURDENED_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 REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, 0 AS FORECAST_REVENUE
, 0 AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, 0 AS CT_FORECAST_REVENUE
, 0 AS CT_FORECAST_BURDENED_COST
FROM PJI_PMV_ORGZ_DIM_TMP HORG
WHERE HORG.NAME <> '-1'
UNION ALL -- FORCE Creation of Class/Code Rows
SELECT '-1' AS ORG_ID
, '-1' AS ORGANIZATION_ID
, NAME AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, 0 AS FORECAST_REVENUE
, 0 AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, 0 AS CT_FORECAST_REVENUE
, 0 AS CT_FORECAST_BURDENED_COST
FROM PJI_PMV_CLS_DIM_TMP HORG
WHERE HORG.NAME <> '-1'
)
WHERE 1 = 1
GROUP BY ORG_ID, ORGANIZATION_ID, PROJECT_CLASS_ID, EXPENDITURE_CATEGORY,EXPENDITURE_TYPE_ID,REVENUE_CATEGORY,
REVENUE_TYPE_ID, WORK_TYPE_ID;
SELECT PJI_REP_PPSUM(ORG_ID
, ORGANIZATION_ID
, PROJECT_CLASS_ID
, EXPENDITURE_CATEGORY
, EXPENDITURE_TYPE_ID
, REVENUE_CATEGORY
, REVENUE_TYPE_ID
, WORK_TYPE_ID
, SUM(REVENUE)
, SUM(BURDENED_COST)
, SUM(FORECAST_REVENUE)
, SUM(FORECAST_BURDENED_COST)
, SUM(CT_REVENUE)
, SUM(CT_BURDENED_COST)
, SUM(CT_FORECAST_REVENUE)
, SUM(CT_FORECAST_BURDENED_COST)
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
)
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
, decode(p_view_by, 'RC', ET.name, '-1') AS REVENUE_CATEGORY
, decode(p_view_by, 'RT', ET.name, '-1') AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, DECODE(TIME.amount_type,1,REVENUE, 0) AS REVENUE
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, NULL AS CT_FORECAST_REVENUE
, NULL AS CT_FORECAST_BURDENED_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 = decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
UNION ALL -- CURRENT Approved Budgets
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
, decode(p_view_by, 'RC', ET.name, '-1') AS REVENUE_CATEGORY
, decode(p_view_by, 'RT', ET.name, '-1') AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, NULL AS CT_FORECAST_REVENUE
, NULL AS CT_FORECAST_BURDENED_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 = decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
and p_Time_Comparison_Type = 'BUDGET'
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
, decode(p_view_by, 'RC', ET.name, '-1') AS REVENUE_CATEGORY
, decode(p_view_by, 'RT', ET.name, '-1') AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, 0 AS FORECAST_REVENUE
, 0 AS FORECAST_BURDENED_COST
, DECODE(TIME.amount_type,1,REVENUE, 0) AS CT_REVENUE
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS CT_BURDENED_COST
, NULL AS CT_FORECAST_REVENUE
, NULL AS CT_FORECAST_BURDENED_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 = decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
and p_Time_Comparison_Type <> 'BUDGET'
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 REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, 0 AS FORECAST_REVENUE
, 0 AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, 0 AS CT_FORECAST_REVENUE
, 0 AS CT_FORECAST_BURDENED_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 REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, 0 AS CT_FORECAST_REVENUE
, 0 AS CT_FORECAST_BURDENED_COST
FROM PJI_PMV_ORGZ_DIM_TMP HORG
WHERE HORG.NAME <> '-1'
UNION ALL -- FORCE Creation of Expenditure Category/Type Rows
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
, decode(p_view_by, 'RC', ET.name, '-1') AS REVENUE_CATEGORY
, decode(p_view_by, 'RT', ET.name, '-1') AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, 0 AS CT_FORECAST_REVENUE
, 0 AS CT_FORECAST_BURDENED_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,REVENUE_CATEGORY,
REVENUE_TYPE_ID, WORK_TYPE_ID;
SELECT PJI_REP_PPSUM(ORG_ID
, ORGANIZATION_ID
, PROJECT_CLASS_ID
, EXPENDITURE_CATEGORY
, EXPENDITURE_TYPE_ID
, REVENUE_CATEGORY
, REVENUE_TYPE_ID
, WORK_TYPE_ID
, SUM(REVENUE)
, SUM(BURDENED_COST)
, SUM(FORECAST_REVENUE)
, SUM(FORECAST_BURDENED_COST)
, SUM(CT_REVENUE)
, SUM(CT_BURDENED_COST)
, SUM(CT_FORECAST_REVENUE)
, SUM(CT_FORECAST_BURDENED_COST)
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
)
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 REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, WT.NAME AS WORK_TYPE_ID
, null AS REVENUE
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, NULL AS CT_FORECAST_REVENUE
, NULL AS CT_FORECAST_BURDENED_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 -- CURRENT Approved Budgets
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 REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, WT.NAME AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, NULL AS CT_FORECAST_REVENUE
, NULL AS CT_FORECAST_BURDENED_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
and p_Time_Comparison_Type = 'BUDGET'
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 REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, WT.NAME AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, 0 AS FORECAST_REVENUE
, 0 AS FORECAST_BURDENED_COST
, null AS CT_REVENUE
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS CT_BURDENED_COST
, NULL AS CT_FORECAST_REVENUE
, NULL AS CT_FORECAST_BURDENED_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
and p_Time_Comparison_Type <> 'BUDGET'
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 REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, 0 AS FORECAST_REVENUE
, 0 AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, 0 AS CT_FORECAST_REVENUE
, 0 AS CT_FORECAST_BURDENED_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 REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, 0 AS CT_FORECAST_REVENUE
, 0 AS CT_FORECAST_BURDENED_COST
FROM PJI_PMV_ORGZ_DIM_TMP HORG
WHERE HORG.NAME <> '-1'
UNION ALL -- FORCE Creation of Work Type Rows
SELECT '-1' AS ORG_ID
, '-1' AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, WT.NAME AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, 0 AS CT_FORECAST_REVENUE
, 0 AS CT_FORECAST_BURDENED_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,REVENUE_CATEGORY,
REVENUE_TYPE_ID, WORK_TYPE_ID;
SELECT PJI_REP_PPSUM(ORG_ID
, ORGANIZATION_ID
, PROJECT_CLASS_ID
, EXPENDITURE_CATEGORY
, EXPENDITURE_TYPE_ID
, REVENUE_CATEGORY
, REVENUE_TYPE_ID
, WORK_TYPE_ID
, SUM(REVENUE)
, SUM(BURDENED_COST)
, SUM(FORECAST_REVENUE)
, SUM(FORECAST_BURDENED_COST)
, SUM(CT_REVENUE)
, SUM(CT_BURDENED_COST)
, SUM(CT_FORECAST_REVENUE)
, SUM(CT_FORECAST_BURDENED_COST)
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
)
BULK COLLECT INTO l_lines_tab
FROM
( SELECT /*+ ORDERED */
HOU.NAME AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, decode(p_view_by, 'CC', CLS.name, '-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
, decode(p_view_by, 'RC', ET.name, '-1') AS REVENUE_CATEGORY
, decode(p_view_by, 'RT', ET.name, '-1') AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, DECODE(TIME.amount_type,1,REVENUE, 0) AS REVENUE
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, NULL AS CT_FORECAST_REVENUE
, NULL AS CT_FORECAST_BURDENED_COST
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_CLSO_ET_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
and fct.exp_evt_type_id = ET.id
and ET.record_type = decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
UNION ALL -- CURRENT Approved Budgets
SELECT /*+ ORDERED */
HOU.NAME AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, decode(p_view_by, 'CC', CLS.name, '-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
, decode(p_view_by, 'RC', ET.name, '-1') AS REVENUE_CATEGORY
, decode(p_view_by, 'RT', ET.name, '-1') AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, NULL AS CT_FORECAST_REVENUE
, NULL AS CT_FORECAST_BURDENED_COST
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_CLSO_ET_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
and fct.exp_evt_type_id = ET.id
and ET.record_type = decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
and p_Time_Comparison_Type = 'BUDGET'
UNION ALL -- PRIOR Actuals
SELECT /*+ ORDERED */
HOU.NAME AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, decode(p_view_by, 'CC', CLS.name, '-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
, decode(p_view_by, 'RC', ET.name, '-1') AS REVENUE_CATEGORY
, decode(p_view_by, 'RT', ET.name, '-1') AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, 0 AS FORECAST_REVENUE
, 0 AS FORECAST_BURDENED_COST
, DECODE(TIME.amount_type,1,REVENUE, 0) AS CT_REVENUE
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS CT_BURDENED_COST
, NULL AS CT_FORECAST_REVENUE
, NULL AS CT_FORECAST_BURDENED_COST
FROM
PJI_PMV_TCMP_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_CLSO_ET_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
and fct.exp_evt_type_id = ET.id
and ET.record_type = decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
and p_Time_Comparison_Type <> 'BUDGET'
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 REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, 0 AS FORECAST_REVENUE
, 0 AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, 0 AS CT_FORECAST_REVENUE
, 0 AS CT_FORECAST_BURDENED_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 REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, 0 AS CT_FORECAST_REVENUE
, 0 AS CT_FORECAST_BURDENED_COST
FROM PJI_PMV_ORGZ_DIM_TMP HORG
WHERE HORG.NAME <> '-1'
UNION ALL -- FORCE Creation of Class/Code Rows
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 REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, 0 AS CT_FORECAST_REVENUE
, 0 AS CT_FORECAST_BURDENED_COST
FROM PJI_PMV_CLS_DIM_TMP CLS
WHERE CLS.NAME <> '-1'
UNION ALL -- FORCE Creation of Expenditure Category/Type Rows
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
, decode(p_view_by, 'RC', ET.name, '-1') AS REVENUE_CATEGORY
, decode(p_view_by, 'RT', ET.name, '-1') AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, 0 AS CT_FORECAST_REVENUE
, 0 AS CT_FORECAST_BURDENED_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,REVENUE_CATEGORY,
REVENUE_TYPE_ID, WORK_TYPE_ID;
SELECT PJI_REP_PPSUM(ORG_ID
, ORGANIZATION_ID
, PROJECT_CLASS_ID
, EXPENDITURE_CATEGORY
, EXPENDITURE_TYPE_ID
, REVENUE_CATEGORY
, REVENUE_TYPE_ID
, WORK_TYPE_ID
, SUM(REVENUE)
, SUM(BURDENED_COST)
, SUM(FORECAST_REVENUE)
, SUM(FORECAST_BURDENED_COST)
, SUM(CT_REVENUE)
, SUM(CT_BURDENED_COST)
, SUM(CT_FORECAST_REVENUE)
, SUM(CT_FORECAST_BURDENED_COST)
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
)
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
, decode(p_view_by, 'RC', ET.name, '-1') AS REVENUE_CATEGORY
, decode(p_view_by, 'RT', ET.name, '-1') AS REVENUE_TYPE_ID
, decode(p_view_by, 'WT', WT.name, '-1') AS WORK_TYPE_ID
, null AS REVENUE
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, NULL AS CT_FORECAST_REVENUE
, NULL AS CT_FORECAST_BURDENED_COST
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, 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.exp_evt_type_id = ET.id
and ET.record_type = decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
and fct.work_type_id = WT.id
UNION ALL -- CURRENT Approved Budgets
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
, decode(p_view_by, 'RC', ET.name, '-1') AS REVENUE_CATEGORY
, decode(p_view_by, 'RT', ET.name, '-1') AS REVENUE_TYPE_ID
, decode(p_view_by, 'WT', WT.name, '-1') AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, NULL AS CT_FORECAST_REVENUE
, NULL AS CT_FORECAST_BURDENED_COST
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, 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.exp_evt_type_id = ET.id
and ET.record_type = decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
and fct.work_type_id = WT.id
and p_Time_Comparison_Type = 'BUDGET'
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
, decode(p_view_by, 'RC', ET.name, '-1') AS REVENUE_CATEGORY
, decode(p_view_by, 'RT', ET.name, '-1') AS REVENUE_TYPE_ID
, decode(p_view_by, 'WT', WT.name, '-1') AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, 0 AS FORECAST_REVENUE
, 0 AS FORECAST_BURDENED_COST
, null AS CT_REVENUE
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS CT_BURDENED_COST
, NULL AS CT_FORECAST_REVENUE
, NULL AS CT_FORECAST_BURDENED_COST
FROM
PJI_PMV_TCMP_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, 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.exp_evt_type_id = ET.id
and ET.record_type = decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
and fct.work_type_id = WT.id
and p_Time_Comparison_Type <> 'BUDGET'
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 REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, 0 AS FORECAST_REVENUE
, 0 AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, 0 AS CT_FORECAST_REVENUE
, 0 AS CT_FORECAST_BURDENED_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 REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, 0 AS CT_FORECAST_REVENUE
, 0 AS CT_FORECAST_BURDENED_COST
FROM PJI_PMV_ORGZ_DIM_TMP HORG
WHERE HORG.NAME <> '-1'
UNION ALL -- FORCE Creation of Expenditure Category/Type Rows
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
, decode(p_view_by, 'RC', ET.name, '-1') AS REVENUE_CATEGORY
, decode(p_view_by, 'RT', ET.name, '-1') AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, 0 AS CT_FORECAST_REVENUE
, 0 AS CT_FORECAST_BURDENED_COST
FROM PJI_PMV_ET_RT_DIM_TMP ET
WHERE ET.NAME <> '-1'
UNION ALL -- FORCE Creation of Work Type Rows
SELECT '-1' AS ORG_ID
, '-1' AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, decode(p_view_by, 'WT', WT.name, '-1') AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, 0 AS CT_FORECAST_REVENUE
, 0 AS CT_FORECAST_BURDENED_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,REVENUE_CATEGORY,
REVENUE_TYPE_ID, WORK_TYPE_ID;
SELECT PJI_REP_PPSUM(ORG_ID
, ORGANIZATION_ID
, PROJECT_CLASS_ID
, EXPENDITURE_CATEGORY
, EXPENDITURE_TYPE_ID
, REVENUE_CATEGORY
, REVENUE_TYPE_ID
, WORK_TYPE_ID
, SUM(REVENUE)
, SUM(BURDENED_COST)
, SUM(FORECAST_REVENUE)
, SUM(FORECAST_BURDENED_COST)
, SUM(CT_REVENUE)
, SUM(CT_BURDENED_COST)
, SUM(CT_FORECAST_REVENUE)
, SUM(CT_FORECAST_BURDENED_COST)
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
)
BULK COLLECT INTO l_lines_tab
FROM
( SELECT /*+ ORDERED */
HOU.NAME AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, decode(p_view_by, 'CC', CLS.name, '-1') AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, decode(p_view_by, 'WT', WT.name, '-1') AS WORK_TYPE_ID
, null AS REVENUE
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, NULL AS CT_FORECAST_REVENUE
, NULL AS CT_FORECAST_BURDENED_COST
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_WT_DIM_TMP WT
, PJI_FP_CLSO_ET_WT_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
and fct.work_type_id = WT.id
UNION ALL -- CURRENT Approved Budgets
SELECT /*+ ORDERED */
HOU.NAME AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, decode(p_view_by, 'CC', CLS.name, '-1') AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, decode(p_view_by, 'WT', WT.name, '-1') AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, NULL AS CT_FORECAST_REVENUE
, NULL AS CT_FORECAST_BURDENED_COST
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_WT_DIM_TMP WT
, PJI_FP_CLSO_ET_WT_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
and fct.work_type_id = WT.id
and p_Time_Comparison_Type = 'BUDGET'
UNION ALL -- PRIOR Actuals
SELECT /*+ ORDERED */
HOU.NAME AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, decode(p_view_by, 'CC', CLS.name, '-1') AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, decode(p_view_by, 'WT', WT.name, '-1') AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, 0 AS FORECAST_REVENUE
, 0 AS FORECAST_BURDENED_COST
, null AS CT_REVENUE
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS CT_BURDENED_COST
, NULL AS CT_FORECAST_REVENUE
, NULL AS CT_FORECAST_BURDENED_COST
FROM
PJI_PMV_TCMP_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_WT_DIM_TMP WT
, PJI_FP_CLSO_ET_WT_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
and fct.work_type_id = WT.id
and p_Time_Comparison_Type <> 'BUDGET'
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 REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, 0 AS FORECAST_REVENUE
, 0 AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, 0 AS CT_FORECAST_REVENUE
, 0 AS CT_FORECAST_BURDENED_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 REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, 0 AS CT_FORECAST_REVENUE
, 0 AS CT_FORECAST_BURDENED_COST
FROM PJI_PMV_ORGZ_DIM_TMP HORG
WHERE HORG.NAME <> '-1'
UNION ALL -- FORCE Creation of Class/Code Rows
SELECT '-1' AS ORG_ID
, '-1' AS ORGANIZATION_ID
, decode(p_view_by, 'CC', CLS.name, '-1') AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, 0 AS CT_FORECAST_REVENUE
, 0 AS CT_FORECAST_BURDENED_COST
FROM PJI_PMV_CLS_DIM_TMP CLS
WHERE CLS.NAME <> '-1'
UNION ALL -- FORCE Creation of Work Type Rows
SELECT '-1' AS ORG_ID
, '-1' AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, decode(p_view_by, 'WT', WT.name, '-1') AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, 0 AS CT_FORECAST_REVENUE
, 0 AS CT_FORECAST_BURDENED_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,REVENUE_CATEGORY,
REVENUE_TYPE_ID, WORK_TYPE_ID;
SELECT PJI_REP_PPSUM(ORG_ID
, ORGANIZATION_ID
, PROJECT_CLASS_ID
, EXPENDITURE_CATEGORY
, EXPENDITURE_TYPE_ID
, REVENUE_CATEGORY
, REVENUE_TYPE_ID
, WORK_TYPE_ID
, SUM(REVENUE)
, SUM(BURDENED_COST)
, SUM(FORECAST_REVENUE)
, SUM(FORECAST_BURDENED_COST)
, SUM(CT_REVENUE)
, SUM(CT_BURDENED_COST)
, SUM(CT_FORECAST_REVENUE)
, SUM(CT_FORECAST_BURDENED_COST)
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
)
BULK COLLECT INTO l_lines_tab
FROM
( SELECT /*+ ORDERED */
HOU.NAME AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, decode(p_view_by, 'CC', CLS.name, '-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
, decode(p_view_by, 'RC', ET.name, '-1') AS REVENUE_CATEGORY
, decode(p_view_by, 'RT', ET.name, '-1') AS REVENUE_TYPE_ID
, decode(p_view_by, 'WT', WT.name, '-1') AS WORK_TYPE_ID
, null AS REVENUE
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, NULL AS CT_FORECAST_REVENUE
, NULL AS CT_FORECAST_BURDENED_COST
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_PMV_WT_DIM_TMP WT
, PJI_FP_CLSO_ET_WT_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
and fct.exp_evt_type_id = ET.id
and ET.record_type = decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
and fct.work_type_id = WT.id
UNION ALL -- CURRENT Approved Budgets
SELECT /*+ ORDERED */
HOU.NAME AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, decode(p_view_by, 'CC', CLS.name, '-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
, decode(p_view_by, 'RC', ET.name, '-1') AS REVENUE_CATEGORY
, decode(p_view_by, 'RT', ET.name, '-1') AS REVENUE_TYPE_ID
, decode(p_view_by, 'WT', WT.name, '-1') AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, NULL AS CT_FORECAST_REVENUE
, NULL AS CT_FORECAST_BURDENED_COST
FROM
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_PMV_WT_DIM_TMP WT
, PJI_FP_CLSO_ET_WT_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
and fct.exp_evt_type_id = ET.id
and ET.record_type = decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
and fct.work_type_id = WT.id
and p_Time_Comparison_Type = 'BUDGET'
UNION ALL -- PRIOR Actuals
SELECT /*+ ORDERED */
HOU.NAME AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, decode(p_view_by, 'CC', CLS.name, '-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
, decode(p_view_by, 'RC', ET.name, '-1') AS REVENUE_CATEGORY
, decode(p_view_by, 'RT', ET.name, '-1') AS REVENUE_TYPE_ID
, decode(p_view_by, 'WT', WT.name, '-1') AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, 0 AS FORECAST_REVENUE
, 0 AS FORECAST_BURDENED_COST
, null AS CT_REVENUE
, DECODE(TIME.amount_type,1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS CT_BURDENED_COST
, NULL AS CT_FORECAST_REVENUE
, NULL AS CT_FORECAST_BURDENED_COST
FROM
PJI_PMV_TCMP_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_PMV_WT_DIM_TMP WT
, PJI_FP_CLSO_ET_WT_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
and fct.exp_evt_type_id = ET.id
and ET.record_type = decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
and fct.work_type_id = WT.id
and p_Time_Comparison_Type <> 'BUDGET'
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 REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, 0 AS FORECAST_REVENUE
, 0 AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, 0 AS CT_FORECAST_REVENUE
, 0 AS CT_FORECAST_BURDENED_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 REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, 0 AS CT_FORECAST_REVENUE
, 0 AS CT_FORECAST_BURDENED_COST
FROM PJI_PMV_ORGZ_DIM_TMP HORG
WHERE HORG.NAME <> '-1'
UNION ALL -- FORCE Creation of Class/Code Rows
SELECT '-1' AS ORG_ID
, '-1' AS ORGANIZATION_ID
, decode(p_view_by, 'CC', CLS.name, '-1') AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, 0 AS CT_FORECAST_REVENUE
, 0 AS CT_FORECAST_BURDENED_COST
FROM PJI_PMV_CLS_DIM_TMP CLS
WHERE CLS.NAME <> '-1'
UNION ALL -- FORCE Creation of Expenditure Category/Type Rows
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
, decode(p_view_by, 'RC', ET.name, '-1') AS REVENUE_CATEGORY
, decode(p_view_by, 'RT', ET.name, '-1') AS REVENUE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, 0 AS CT_FORECAST_REVENUE
, 0 AS CT_FORECAST_BURDENED_COST
FROM PJI_PMV_ET_RT_DIM_TMP ET
WHERE ET.NAME <> '-1'
UNION ALL -- FORCE Creation of Work Type Rows
SELECT '-1' AS ORG_ID
, '-1' AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS REVENUE_CATEGORY
, '-1' AS REVENUE_TYPE_ID
, decode(p_view_by, 'WT', WT.name, '-1') AS WORK_TYPE_ID
, 0 AS REVENUE
, 0 AS BURDENED_COST
, NULL AS FORECAST_REVENUE
, NULL AS FORECAST_BURDENED_COST
, 0 AS CT_REVENUE
, 0 AS CT_BURDENED_COST
, 0 AS CT_FORECAST_REVENUE
, 0 AS CT_FORECAST_BURDENED_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,REVENUE_CATEGORY,
REVENUE_TYPE_ID, WORK_TYPE_ID;
l_lines_tab.DELETE(l_Top_Org_Index);