[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
, P_SELECT_LIST =>
'FACT.COST "PJI_REP_MSR_1" , FACT.COST "PJI_REP_MSR_19"
, FACT.CT_COST "PJI_REP_MSR_2" , FACT.COST_CHANGE_PRCNT "PJI_REP_MSR_3"
, FACT.CAP_COST "PJI_REP_MSR_4" , FACT.CAP_COST "PJI_REP_MSR_20"
, FACT.CT_CAP_COST "PJI_REP_MSR_5" , FACT.CAP_COST_CHANGE_PRCNT "PJI_REP_MSR_6"
, FACT.PRCNT_OF_COST "PJI_REP_MSR_7" , FACT.PRCNT_OF_COST "PJI_REP_MSR_21"
, FACT.CT_PRCNT_OF_COST "PJI_REP_MSR_8" , FACT.PRCNT_OF_COST_CHANGE "PJI_REP_MSR_9"
, FACT.EXPENSE "PJI_REP_MSR_10" , FACT.EXPENSE "PJI_REP_MSR_22"
, FACT.CT_EXPENSE "PJI_REP_MSR_11" , FACT.EXPENSE_CHANGE_PRCNT "PJI_REP_MSR_12"
, FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_1" , FACT.PJI_REP_TOTAL_2 "PJI_REP_TOTAL_2"
, FACT.PJI_REP_TOTAL_3 "PJI_REP_TOTAL_3" , FACT.PJI_REP_TOTAL_4 "PJI_REP_TOTAL_4"
, FACT.PJI_REP_TOTAL_5 "PJI_REP_TOTAL_5" , FACT.PJI_REP_TOTAL_6 "PJI_REP_TOTAL_6"
, FACT.PJI_REP_TOTAL_7 "PJI_REP_TOTAL_7" , FACT.PJI_REP_TOTAL_8 "PJI_REP_TOTAL_8"
, FACT.PJI_REP_TOTAL_9 "PJI_REP_TOTAL_9" , FACT.PJI_REP_TOTAL_10 "PJI_REP_TOTAL_10"
, FACT.PJI_REP_TOTAL_11 "PJI_REP_TOTAL_11" , FACT.PJI_REP_TOTAL_12 "PJI_REP_TOTAL_12"
, FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_16"
, FACT.PJI_REP_TOTAL_4 "PJI_REP_TOTAL_17"
, FACT.PJI_REP_TOTAL_7 "PJI_REP_TOTAL_18"'
, P_SQL_STATEMENT => x_PMV_Sql
, P_PMV_OUTPUT => x_PMV_Output
, p_Region_Code => 'PJI_REP_PC6'
, p_PLSQL_Driver => 'PJI_PMV_CAPITAL_COST.PLSQLDriver_PJI_REP_PC6'
, p_PLSQL_Driver_Params =>
' <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <> '||
', <> '||
', <> '||
', <> '
);
, P_SELECT_LIST => 'FACT.COST "PJI_REP_MSR_1"
, FACT.COST "PJI_REP_MSR_19"
, FACT.CT_COST "PJI_REP_MSR_2"
, FACT.CT_COST "PJI_REP_MSR_20"
, FACT.COST_CHANGE_PRCNT "PJI_REP_MSR_3"
, FACT.CAP_COST "PJI_REP_MSR_4"
, FACT.CAP_COST "PJI_REP_MSR_21"
, FACT.CT_CAP_COST "PJI_REP_MSR_5"
, FACT.CT_CAP_COST "PJI_REP_MSR_22"
, FACT.CAP_COST_CHANGE_PRCNT "PJI_REP_MSR_6"
, FACT.PRCNT_OF_COST "PJI_REP_MSR_7"
, FACT.CT_PRCNT_OF_COST "PJI_REP_MSR_8"
, FACT.PRCNT_OF_COST_CHANGE "PJI_REP_MSR_9"
, FACT.EXPENSE "PJI_REP_MSR_10"
, FACT.EXPENSE "PJI_REP_MSR_24"
, FACT.CT_EXPENSE "PJI_REP_MSR_11"
, FACT.CT_EXPENSE "PJI_REP_MSR_25"
, FACT.EXPENSE_CHANGE_PRCNT "PJI_REP_MSR_12" '
, P_PMV_OUTPUT => x_PMV_Output
, P_REGION_CODE => 'PJI_REP_PC7'
, P_PLSQL_DRIVER => 'PJI_PMV_CAPITAL_COST.PLSQLDriver_PJI_REP_PC7'
, P_PLSQL_DRIVER_PARAMS => ' <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', NULL'||
', NULL'||
', NULL'||
', NULL');
, P_SELECT_LIST =>
' FACT.COST "PJI_REP_MSR_1"
, FACT.COST "PJI_REP_MSR_19"
, FACT.CT_COST "PJI_REP_MSR_2"
, FACT.CT_COST "PJI_REP_MSR_20"
, FACT.COST_CHANGE_PRCNT "PJI_REP_MSR_3"
, FACT.CAP_COST "PJI_REP_MSR_4"
, FACT.CAP_COST "PJI_REP_MSR_21"
, FACT.CT_CAP_COST "PJI_REP_MSR_5"
, FACT.CT_CAP_COST "PJI_REP_MSR_22"
, FACT.CAP_COST_CHANGE_PRCNT "PJI_REP_MSR_6"
, FACT.PRCNT_OF_COST "PJI_REP_MSR_7"
, FACT.CT_PRCNT_OF_COST "PJI_REP_MSR_8"
, FACT.PRCNT_OF_COST_CHANGE "PJI_REP_MSR_9"
, FACT.EXPENSE "PJI_REP_MSR_10"
, FACT.EXPENSE "PJI_REP_MSR_24"
, FACT.CT_EXPENSE "PJI_REP_MSR_11"
, FACT.CT_EXPENSE "PJI_REP_MSR_25"
, FACT.EXPENSE_CHANGE_PRCNT "PJI_REP_MSR_12" '
, P_SQL_STATEMENT => x_PMV_Sql
, P_PMV_OUTPUT => x_PMV_Output
, P_REGION_CODE => 'PJI_REP_PC8'
, P_PLSQL_DRIVER => 'PJI_PMV_CAPITAL_COST.PLSQLDriver_PJI_REP_PC7'
, P_PLSQL_DRIVER_PARAMS => ' <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', :PJI_EXTRA_BND_01'||
', NULL'||
', 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.COST "PJI_REP_MSR_8"
, FACT.CAPITAL_COST "PJI_REP_MSR_16"
, FACT.CAP_COST_PERCENT_OF_COST "PJI_REP_MSR_17"
, FACT.EXPENSE "PJI_REP_MSR_12"
, FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_1"
, FACT.PJI_REP_TOTAL_2 "PJI_REP_TOTAL_2"
, FACT.PJI_REP_TOTAL_3 "PJI_REP_TOTAL_3"
, FACT.PJI_REP_TOTAL_4 "PJI_REP_TOTAL_4"'
, P_SQL_STATEMENT => x_PMV_Sql
, P_PMV_OUTPUT => x_PMV_Output
, p_Region_Code => 'PJI_REP_PC9'
, p_PLSQL_Driver => 'PJI_PMV_CAPITAL_COST.PLSQLDriver_PJI_REP_PC9'
, p_PLSQL_Driver_Params => ' <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <>'||
', <> '||
', <> '||
', <> '||
', <> '||
', <> '
);
select report_cost_type
into G_Report_Cost_Type
from pji_system_settings;
select PJI_REP_PC6 ( ORG_ID
, ORGANIZATION_ID
, PROJECT_CLASS_ID
, EXPENDITURE_CATEGORY
, EXPENDITURE_TYPE_ID
, WORK_TYPE_ID
, SUM ( COST )
, SUM ( CT_COST )
, SUM ( COST_CHANGE_PRCNT )
, SUM ( CAP_COST )
, SUM ( CT_CAP_COST )
, SUM ( CAP_COST_CHANGE_PRCNT )
, SUM ( PRCNT_OF_COST )
, SUM ( CT_PRCNT_OF_COST )
, SUM ( PRCNT_OF_COST_CHANGE )
, SUM ( EXPENSE )
, SUM ( CT_EXPENSE )
, SUM ( EXPENSE_CHANGE_PRCNT )
, null, null, null, null, null, null
, null , null , null , null , null
, null , null , null , null , null
, null , null , null , null , null
, null , null, null )
bulk collect into l_lines_tab
from
( select /*+ ORDERED */
HOU.NAME as ORG_ID
, HORG.NAME as ORGANIZATION_ID
, '-1' as PROJECT_CLASS_ID
, '-1' as EXPENDITURE_CATEGORY
, '-1' as EXPENDITURE_TYPE_ID
, '-1' as WORK_TYPE_ID
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type, 'BC', FCT.CAPITAL_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST),0) as COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITALIZABLE_RAW_COST),0) CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_FP_ORGO_F_MV FCT
, PJI_PMV_ORG_DIM_TMP HOU
where
FCT.ORG_ID = HOU.ID
and FCT.ORGANIZATION_ID = HORG.ID
and FCT.TIME_ID = TIME.ID
and TIME.ID is not null
and FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
and FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
union all -- PRIOR Actuals
select /*+ ORDERED */
HOU.NAME as ORG_ID
, HORG.NAME as ORGANIZATION_ID
, '-1' as PROJECT_CLASS_ID
, '-1' as EXPENDITURE_CATEGORY
, '-1' as EXPENDITURE_TYPE_ID
, '-1' as WORK_TYPE_ID
, 0 as COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST),0) as CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITALIZABLE_RAW_COST),0) as CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) as CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_TCMP_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_FP_ORGO_F_MV FCT
, PJI_PMV_ORG_DIM_TMP HOU
where
FCT.ORG_ID = HOU.ID
and FCT.ORGANIZATION_ID = HORG.ID
and FCT.TIME_ID = TIME.ID
and TIME.ID is not null
and FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
and FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
union all -- FORCE Creation of Org rows
select HOU.NAME as ORG_ID
, '-1' as ORGANIZATION_ID
, '-1' as PROJECT_CLASS_ID
, '-1' as EXPENDITURE_CATEGORY
, '-1' as EXPENDITURE_TYPE_ID
, '-1' as WORK_TYPE_ID
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_ORG_DIM_TMP HOU
where HOU.NAME <> '-1'
union all -- FORCE Creation of Organization Rows
select '-1' as ORG_ID
, HORG.NAME as ORGANIZATION_ID
, '-1' as PROJECT_CLASS_ID
, '-1' as EXPENDITURE_CATEGORY
, '-1' as EXPENDITURE_TYPE_ID
, '-1' as WORK_TYPE_ID
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from PJI_PMV_ORGZ_DIM_TMP HORG
where HORG.NAME <> '-1'
) WHERE 1 = 1
group by ORG_ID, ORGANIZATION_ID, PROJECT_CLASS_ID, EXPENDITURE_CATEGORY,
EXPENDITURE_TYPE_ID, WORK_TYPE_ID;
select PJI_REP_PC6 ( ORG_ID
, ORGANIZATION_ID
, PROJECT_CLASS_ID
, EXPENDITURE_CATEGORY
, EXPENDITURE_TYPE_ID
, WORK_TYPE_ID
, SUM ( COST )
, SUM ( CT_COST )
, SUM ( COST_CHANGE_PRCNT )
, SUM ( CAP_COST )
, SUM ( CT_CAP_COST )
, SUM ( CAP_COST_CHANGE_PRCNT )
, SUM ( PRCNT_OF_COST )
, SUM ( CT_PRCNT_OF_COST )
, SUM ( PRCNT_OF_COST_CHANGE )
, SUM ( EXPENSE )
, SUM ( CT_EXPENSE )
, SUM ( EXPENSE_CHANGE_PRCNT )
, null, null, null, null, null, null
, null , null , null , null , null
, null , null , null , null , null
, null , null , null , null , null
, null , null, null )
bulk collect into l_lines_tab
from
( select /*+ ORDERED */
HOU.NAME as ORG_ID
, HORG.NAME as ORGANIZATION_ID
, CLS.NAME as PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type, 'BC', FCT.CAPITAL_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST),0) as COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITALIZABLE_RAW_COST),0) CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_FP_CLSO_F_MV FCT
, PJI_PMV_ORG_DIM_TMP HOU
where
fct.PROJECT_ORG_ID = HOU.ID
and FCT.PROJECT_ORGANIZATION_ID = HORG.ID
and FCT.TIME_ID = TIME.ID
and TIME.ID is not null
and FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
and FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
and FCT.PROJECT_CLASS_ID = CLS.ID
union all -- PRIOR Actuals
select /*+ ORDERED */
HOU.NAME as ORG_ID
, HORG.NAME as ORGANIZATION_ID
, CLS.NAME as PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 as COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST),0) as CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITALIZABLE_RAW_COST),0) as CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) as CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_TCMP_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_FP_CLSO_F_MV FCT
, PJI_PMV_ORG_DIM_TMP HOU
where
FCT.PROJECT_ORG_ID = HOU.ID
and FCT.PROJECT_ORGANIZATION_ID = HORG.ID
and FCT.TIME_ID = TIME.ID
and TIME.ID is not null
and FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
and FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
and FCT.PROJECT_CLASS_ID = CLS.ID
union all -- FORCE Creation of Org rows
select HOU.NAME AS ORG_ID
, '-1' AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_ORG_DIM_TMP HOU
where HOU.NAME <> '-1'
union all -- FORCE Creation of Organization Rows
select '-1' AS ORG_ID
, NAME AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from PJI_PMV_ORGZ_DIM_TMP HORG
where HORG.NAME <> '-1'
union all -- FORCE Creation of Class 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 WORK_TYPE_ID
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from PJI_PMV_CLS_DIM_TMP CLS
where CLS.NAME <> '-1'
) WHERE 1 = 1
group by ORG_ID, ORGANIZATION_ID, PROJECT_CLASS_ID,
EXPENDITURE_CATEGORY, EXPENDITURE_TYPE_ID, WORK_TYPE_ID;
select PJI_REP_PC6 ( ORG_ID
, ORGANIZATION_ID
, PROJECT_CLASS_ID
, EXPENDITURE_CATEGORY
, EXPENDITURE_TYPE_ID
, WORK_TYPE_ID
, SUM ( COST )
, SUM ( CT_COST )
, SUM ( COST_CHANGE_PRCNT )
, SUM ( CAP_COST )
, SUM ( CT_CAP_COST )
, SUM ( CAP_COST_CHANGE_PRCNT )
, SUM ( PRCNT_OF_COST )
, SUM ( CT_PRCNT_OF_COST )
, SUM ( PRCNT_OF_COST_CHANGE )
, SUM ( EXPENSE )
, SUM ( CT_EXPENSE )
, SUM ( EXPENSE_CHANGE_PRCNT )
, null, null, null, null, null, null
, null , null , null , null , null
, null , null , null , null , null
, null , null , null , null , null
, null , null, null )
bulk collect into l_lines_tab
from
( select /*+ ORDERED */
HOU.NAME AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
, decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type, 'BC', FCT.CAPITAL_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST),0) AS COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITALIZABLE_RAW_COST),0) CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_ORGO_ET_F_MV FCT
, PJI_PMV_ORG_DIM_TMP HOU
where
FCT.ORG_ID = HOU.ID
and FCT.ORGANIZATION_ID = HORG.ID
and FCT.TIME_ID = TIME.ID
and TIME.ID is not null
and FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
and FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
and FCT.EXP_EVT_TYPE_ID = ET.ID
and ET.record_type = 'ET'
union all -- PRIOR Actuals
select /*+ ORDERED */
HOU.NAME as ORG_ID
, HORG.NAME as ORGANIZATION_ID
, '-1' as PROJECT_CLASS_ID
, decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
, decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 as COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST),0) as CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITALIZABLE_RAW_COST),0) as CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) as CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
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 FCT.EXP_EVT_TYPE_ID = ET.ID
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
and ET.record_type = 'ET'
union all -- FORCE Creation of Org rows
select HOU.NAME as ORG_ID
, '-1' as ORGANIZATION_ID
, '-1' as PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_ORG_DIM_TMP HOU
where HOU.NAME <> '-1'
union all -- FORCE Creation of Organization Rows
select '-1' AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from PJI_PMV_ORGZ_DIM_TMP HORG
where HORG.NAME <> '-1'
union all -- FORCE Creation of Expenditure 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
, '-1' AS WORK_TYPE_ID
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from PJI_PMV_ET_RT_DIM_TMP ET
where ET.NAME <> '-1'
) WHERE 1 = 1
group by ORG_ID, ORGANIZATION_ID, PROJECT_CLASS_ID, EXPENDITURE_CATEGORY,
EXPENDITURE_TYPE_ID, WORK_TYPE_ID;
select PJI_REP_PC6 ( ORG_ID
, ORGANIZATION_ID
, PROJECT_CLASS_ID
, EXPENDITURE_CATEGORY
, EXPENDITURE_TYPE_ID
, WORK_TYPE_ID
, SUM ( COST )
, SUM ( CT_COST )
, SUM ( COST_CHANGE_PRCNT )
, SUM ( CAP_COST )
, SUM ( CT_CAP_COST )
, SUM ( CAP_COST_CHANGE_PRCNT )
, SUM ( PRCNT_OF_COST )
, SUM ( CT_PRCNT_OF_COST )
, SUM ( PRCNT_OF_COST_CHANGE )
, SUM ( EXPENSE )
, SUM ( CT_EXPENSE )
, SUM ( EXPENSE_CHANGE_PRCNT )
, null, null, null, null, null, null
, null , null , null , null , null
, null , null , null , null , null
, null , null , null , null , null
, null , null, null )
bulk collect into l_lines_tab
from
( select /*+ ORDERED */
HOU.NAME as ORG_ID
, HORG.NAME as ORGANIZATION_ID
, '-1' as PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, WT.NAME AS WORK_TYPE_ID
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type, 'BC', FCT.CAPITAL_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST),0) as COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITALIZABLE_RAW_COST),0) CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
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 -- PRIOR Actuals
select /*+ ORDERED */
HOU.NAME AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, WT.NAME AS WORK_TYPE_ID
, 0 AS COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST),0) AS CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITALIZABLE_RAW_COST),0) AS CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) as CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_TCMP_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_WT_DIM_TMP WT
, PJI_FP_ORGO_ET_WT_F_MV FCT
, PJI_PMV_ORG_DIM_TMP HOU
where
FCT.ORG_ID = HOU.ID
and FCT.ORGANIZATION_ID = HORG.ID
and FCT.TIME_ID = TIME.ID
and TIME.ID is not null
and FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
and FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
and FCT.WORK_TYPE_ID = WT.ID
union all -- FORCE Creation of Org rows
select HOU.NAME AS ORG_ID
, '-1' AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_ORG_DIM_TMP HOU
where HOU.NAME <> '-1'
union all -- FORCE Creation of Organization Rows
select '-1' AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
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
, WT.NAME AS WORK_TYPE_ID
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from PJI_PMV_WT_DIM_TMP WT
where WT.NAME <> '-1'
)
group by ORG_ID, ORGANIZATION_ID, PROJECT_CLASS_ID, EXPENDITURE_CATEGORY,
EXPENDITURE_TYPE_ID, WORK_TYPE_ID;
select PJI_REP_PC6 ( ORG_ID
, ORGANIZATION_ID
, PROJECT_CLASS_ID
, EXPENDITURE_CATEGORY
, EXPENDITURE_TYPE_ID
, WORK_TYPE_ID
, SUM ( COST )
, SUM ( CT_COST )
, SUM ( COST_CHANGE_PRCNT )
, SUM ( CAP_COST )
, SUM ( CT_CAP_COST )
, SUM ( CAP_COST_CHANGE_PRCNT )
, SUM ( PRCNT_OF_COST )
, SUM ( CT_PRCNT_OF_COST )
, SUM ( PRCNT_OF_COST_CHANGE )
, SUM ( EXPENSE )
, SUM ( CT_EXPENSE )
, SUM ( EXPENSE_CHANGE_PRCNT )
, null, null, null, null, null, null
, null , null , null , null , null
, null , null , null , null , null
, null , null , null , null , null
, null , null, null )
bulk collect into l_lines_tab
from
( select /*+ ORDERED */
HOU.NAME as ORG_ID
, HORG.NAME as ORGANIZATION_ID
, CLS.NAME as PROJECT_CLASS_ID
, decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
, decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type, 'BC', FCT.CAPITAL_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST),0) as COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITALIZABLE_RAW_COST),0) CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_CLSO_ET_F_MV FCT
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_ORG_DIM_TMP HOU
where
FCT.PROJECT_ORG_ID = HOU.ID
and FCT.PROJECT_ORGANIZATION_ID = HORG.ID
and FCT.TIME_ID = TIME.ID
and TIME.ID is not null
and FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
and FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
and FCT.EXP_EVT_TYPE_ID = ET.ID
and FCT.PROJECT_CLASS_ID = CLS.ID
union all -- PRIOR Actuals
select /*+ ORDERED */
HOU.NAME AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, CLS.NAME AS PROJECT_CLASS_ID
, decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
, decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 as COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST),0) as CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITALIZABLE_RAW_COST),0) as CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) as CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_TCMP_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_CLSO_ET_F_MV FCT
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_ORG_DIM_TMP HOU
where
FCT.PROJECT_ORG_ID = HOU.ID
and FCT.PROJECT_ORGANIZATION_ID = HORG.ID
and FCT.TIME_ID = TIME.ID
and TIME.ID is not null
and FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
and FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
and FCT.EXP_EVT_TYPE_ID = ET.ID
and FCT.PROJECT_CLASS_ID = CLS.ID
union all -- FORCE Creation of Org rows
select NAME as ORG_ID
, '-1' as ORGANIZATION_ID
, '-1' as PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_ORG_DIM_TMP HOU
where HOU.NAME <> '-1'
union all -- FORCE Creation of Organization Rows
select '-1' as ORG_ID
, HORG.NAME as ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from PJI_PMV_ORGZ_DIM_TMP HORG
where HORG.NAME <> '-1'
union all -- FORCE Creation of Project Class 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 WORK_TYPE_ID
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
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
, '-1' AS WORK_TYPE_ID
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from PJI_PMV_ET_RT_DIM_TMP ET
where ET.NAME <> '-1'
) WHERE 1=1
group by ORG_ID, ORGANIZATION_ID, PROJECT_CLASS_ID, EXPENDITURE_CATEGORY,
EXPENDITURE_TYPE_ID, WORK_TYPE_ID;
select PJI_REP_PC6 ( ORG_ID
, ORGANIZATION_ID
, PROJECT_CLASS_ID
, EXPENDITURE_CATEGORY
, EXPENDITURE_TYPE_ID
, WORK_TYPE_ID
, SUM ( COST )
, SUM ( CT_COST )
, SUM ( COST_CHANGE_PRCNT )
, SUM ( CAP_COST )
, SUM ( CT_CAP_COST )
, SUM ( CAP_COST_CHANGE_PRCNT )
, SUM ( PRCNT_OF_COST )
, SUM ( CT_PRCNT_OF_COST )
, SUM ( PRCNT_OF_COST_CHANGE )
, SUM ( EXPENSE )
, SUM ( CT_EXPENSE )
, SUM ( EXPENSE_CHANGE_PRCNT )
, null, null, null, null, null, null
, null , null , null , null , null
, null , null , null , null , null
, null , null , null , null , null
, null , null, null )
bulk collect into l_lines_tab
from
( select /*+ ORDERED */
HOU.NAME as ORG_ID
, HORG.NAME as ORGANIZATION_ID
, '-1' as PROJECT_CLASS_ID
, decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
, decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
, WT.NAME AS WORK_TYPE_ID
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type, 'BC', FCT.CAPITAL_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST),0) as COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITALIZABLE_RAW_COST),0) CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_ORGO_ET_WT_F_MV FCT
, PJI_PMV_WT_DIM_TMP WT
, PJI_PMV_ORG_DIM_TMP HOU
where
FCT.ORG_ID = HOU.ID
and FCT.ORGANIZATION_ID = HORG.ID
and FCT.TIME_ID = TIME.ID
and TIME.ID is not null
and FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
and FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
and FCT.WORK_TYPE_ID = WT.ID
and FCT.EXP_EVT_TYPE_ID = ET.ID
union all -- PRIOR Actuals
select /*+ ORDERED */
HOU.NAME as ORG_ID
, HORG.NAME as ORGANIZATION_ID
, '-1' as PROJECT_CLASS_ID
, decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
, decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
, WT.NAME AS WORK_TYPE_ID
, 0 as COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST),0) as CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITALIZABLE_RAW_COST),0) as CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) as CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_TCMP_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_ORGO_ET_WT_F_MV FCT
, PJI_PMV_WT_DIM_TMP WT
, PJI_PMV_ORG_DIM_TMP HOU
where
FCT.ORG_ID = HOU.ID
and FCT.ORGANIZATION_ID = HORG.ID
and FCT.TIME_ID = TIME.ID
and TIME.ID is not null
and FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
and FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
and FCT.WORK_TYPE_ID = WT.ID
and FCT.EXP_EVT_TYPE_ID = ET.ID
union all -- FORCE Creation of Org rows
select HOU.NAME as ORG_ID
, '-1' as ORGANIZATION_ID
, '-1' as PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_ORG_DIM_TMP HOU
where HOU.NAME <> '-1'
union all -- FORCE Creation of Organization Rows
select '-1' AS ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from PJI_PMV_ORGZ_DIM_TMP HORG
where HORG.NAME <> '-1'
union all
select '-1' as ORG_ID
, '-1' as ORGANIZATION_ID
, '-1' as PROJECT_CLASS_ID
, decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
, decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_ET_RT_DIM_TMP ET
where ET.NAME <> '-1'
union all
select '-1' as ORG_ID
, '-1' as ORGANIZATION_ID
, '-1' as PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, WT.NAME AS WORK_TYPE
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_WT_DIM_TMP WT
where WT.NAME <> '-1'
) WHERE 1=1
group by ORG_ID, ORGANIZATION_ID, PROJECT_CLASS_ID, EXPENDITURE_CATEGORY,
EXPENDITURE_TYPE_ID, WORK_TYPE_ID;
select PJI_REP_PC6 ( ORG_ID
, ORGANIZATION_ID
, PROJECT_CLASS_ID
, EXPENDITURE_CATEGORY
, EXPENDITURE_TYPE_ID
, WORK_TYPE_ID
, SUM ( COST )
, SUM ( CT_COST )
, SUM ( COST_CHANGE_PRCNT )
, SUM ( CAP_COST )
, SUM ( CT_CAP_COST )
, SUM ( CAP_COST_CHANGE_PRCNT )
, SUM ( PRCNT_OF_COST )
, SUM ( CT_PRCNT_OF_COST )
, SUM ( PRCNT_OF_COST_CHANGE )
, SUM ( EXPENSE )
, SUM ( CT_EXPENSE )
, SUM ( EXPENSE_CHANGE_PRCNT )
, null, null, null, null, null, null
, null , null , null , null , null
, null , null , null , null , null
, null , null , null , null , null
, null , null, null )
bulk collect into l_lines_tab
from
( select /*+ ORDERED */
HOU.NAME as ORG_ID
, HORG.NAME as ORGANIZATION_ID
, CLS.NAME as PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, WT.NAME AS WORK_TYPE_ID
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type, 'BC', FCT.CAPITAL_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST),0) as COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITALIZABLE_RAW_COST),0) CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_FP_CLSO_ET_WT_F_MV FCT
, PJI_PMV_WT_DIM_TMP WT
, PJI_PMV_ORG_DIM_TMP HOU
where
FCT.PROJECT_ORG_ID = HOU.ID
and FCT.PROJECT_ORGANIZATION_ID = HORG.ID
and FCT.TIME_ID = TIME.ID
and TIME.ID is not null
and FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
and FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
and FCT.WORK_TYPE_ID = WT.ID
and FCT.PROJECT_CLASS_ID = CLS.ID
union all -- PRIOR Actuals
select /*+ ORDERED */
HOU.NAME as ORG_ID
, HORG.NAME as ORGANIZATION_ID
, CLS.NAME as PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, WT.NAME AS WORK_TYPE_ID
, 0 as COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST),0) as CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITALIZABLE_RAW_COST),0) as CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) as CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_TCMP_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_FP_CLSO_ET_WT_F_MV FCT
, PJI_PMV_WT_DIM_TMP WT
, PJI_PMV_ORG_DIM_TMP HOU
where
FCT.PROJECT_ORG_ID = HOU.ID
and FCT.PROJECT_ORGANIZATION_ID = HORG.ID
and FCT.TIME_ID = TIME.ID
and TIME.ID is not null
and FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
and FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
and FCT.WORK_TYPE_ID = WT.ID
and FCT.PROJECT_CLASS_ID = CLS.ID
union all -- FORCE Creation of Org rows
select HOU.NAME as ORG_ID
, '-1' as ORGANIZATION_ID
, '-1' as PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_ORG_DIM_TMP HOU
where HOU.NAME <> '-1'
union all -- FORCE Creation of Organization Rows
select '-1' as ORG_ID
, HORG.NAME as ORGANIZATION_ID
, '-1' as PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from PJI_PMV_ORGZ_DIM_TMP HORG
where HORG.NAME <> '-1'
union all -- FORCE Creation of Org 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 WORK_TYPE_ID
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from PJI_PMV_CLS_DIM_TMP CLS
where CLS.NAME <> '-1'
union all -- FORCE Creation of Org rows
select '-1' AS ORG_ID
, '-1' AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, WT.NAME AS WORK_TYPE_ID
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from PJI_PMV_WT_DIM_TMP WT
where WT.NAME <> '-1'
) group by ORG_ID, ORGANIZATION_ID, PROJECT_CLASS_ID, EXPENDITURE_CATEGORY,
EXPENDITURE_TYPE_ID,WORK_TYPE_ID;
select PJI_REP_PC6 ( ORG_ID
, ORGANIZATION_ID
, PROJECT_CLASS_ID
, EXPENDITURE_CATEGORY
, EXPENDITURE_TYPE_ID
, WORK_TYPE_ID
, SUM ( COST )
, SUM ( CT_COST )
, SUM ( COST_CHANGE_PRCNT )
, SUM ( CAP_COST )
, SUM ( CT_CAP_COST )
, SUM ( CAP_COST_CHANGE_PRCNT )
, SUM ( PRCNT_OF_COST )
, SUM ( CT_PRCNT_OF_COST )
, SUM ( PRCNT_OF_COST_CHANGE )
, SUM ( EXPENSE )
, SUM ( CT_EXPENSE )
, SUM ( EXPENSE_CHANGE_PRCNT )
, null, null, null, null, null, null
, null , null , null , null , null
, null , null , null , null , null
, null , null , null , null , null
, null , null, null )
bulk collect into l_lines_tab
from
( select /*+ ORDERED */
HOU.NAME as ORG_ID
, HORG.NAME as ORGANIZATION_ID
, CLS.NAME as PROJECT_CLASS_ID
, decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
, decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
, WT.NAME AS WORK_TYPE_ID
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type, 'BC', FCT.CAPITAL_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST),0) as COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITALIZABLE_RAW_COST),0) CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_CLSO_ET_WT_F_MV FCT
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_WT_DIM_TMP WT
, PJI_PMV_ORG_DIM_TMP HOU
where
FCT.PROJECT_ORG_ID = HOU.ID
and FCT.PROJECT_ORGANIZATION_ID = HORG.ID
and FCT.TIME_ID = TIME.ID
and TIME.ID is not null
and FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
and FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
and FCT.WORK_TYPE_ID = WT.ID
and FCT.PROJECT_CLASS_ID = CLS.ID
and FCT.EXP_EVT_TYPE_ID = ET.ID
and ET.record_type = 'ET'
union all -- PRIOR Actuals
select /*+ ORDERED */
HOU.NAME as ORG_ID
, HORG.NAME as ORGANIZATION_ID
, CLS.NAME as PROJECT_CLASS_ID
, decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
, decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
, WT.NAME AS WORK_TYPE_ID
, 0 as COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST),0) as CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITALIZABLE_RAW_COST),0) as CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) as CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_TCMP_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_CLSO_ET_WT_F_MV FCT
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_WT_DIM_TMP WT
, PJI_PMV_ORG_DIM_TMP HOU
where
FCT.PROJECT_ORG_ID = HOU.ID
and FCT.PROJECT_ORGANIZATION_ID = HORG.ID
and FCT.TIME_ID = TIME.ID
and TIME.ID is not null
and FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
and FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
and FCT.WORK_TYPE_ID = WT.ID
and FCT.PROJECT_CLASS_ID = CLS.ID
and FCT.EXP_EVT_TYPE_ID = ET.ID
and ET.record_type = 'ET'
union all -- FORCE Creation of Org rows
select HOU.NAME AS ORG_ID
, '-1' AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_ORG_DIM_TMP HOU
where HOU.NAME <> '-1'
union all -- FORCE Creation of Organization Rows
select '-1' as ORG_ID
, HORG.NAME AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from PJI_PMV_ORGZ_DIM_TMP HORG
where HORG.NAME <> '-1'
union all
select '-1' as ORG_ID
, '-1' AS ORGANIZATION_ID
, CLS.NAME as PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from PJI_PMV_CLS_DIM_TMP CLS
where CLS.NAME <> '-1'
union all
select '-1' as ORG_ID
, '-1' AS ORGANIZATION_ID
, '-1' as PROJECT_CLASS_ID
, decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
, decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
, '-1' AS WORK_TYPE_ID
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from PJI_PMV_ET_RT_DIM_TMP ET
where ET.NAME <> '-1'
union all
select '-1' as ORG_ID
, '-1' AS ORGANIZATION_ID
, '-1' AS PROJECT_CLASS_ID
, '-1' AS EXPENDITURE_CATEGORY
, '-1' AS EXPENDITURE_TYPE_ID
, WT.NAME AS WORK_TYPE_ID
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from PJI_PMV_WT_DIM_TMP WT
where WT.NAME <> '-1'
) where 1=1
group by ORG_ID, ORGANIZATION_ID, PROJECT_CLASS_ID, EXPENDITURE_CATEGORY,
EXPENDITURE_TYPE_ID, WORK_TYPE_ID;
l_lines_tab.DELETE(l_Top_Org_Index);
select report_cost_type
into G_Report_Cost_Type
from pji_system_settings;
select PJI_REP_PC7(
TIME_ID
, SUM ( COST )
, SUM ( CT_COST )
, SUM ( COST_CHANGE_PRCNT )
, SUM ( CAP_COST )
, SUM ( CT_CAPITAL_COST )
, SUM ( CAP_COST_CHANGE_PRCNT )
, SUM ( PRCNT_OF_COST )
, SUM ( CT_PRCNT_OF_COST )
, SUM ( PRCNT_OF_COST_CHANGE )
, SUM ( EXPENSE )
, SUM ( CT_EXPENSE )
, SUM ( Expense_CHANGE_PRCNT )
, NULL, NULL, NULL, NULL, NULL, NULL)
bulk collect into l_Project_Cost_Trend_Tab
from (
select /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,'RC', capital_raw_cost,
'BC', capital_brdn_cost),0) cost
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,'RC', FCT.CAPITALIZABLE_RAW_COST,
'BC', FCT.CAPITALIZABLE_BRDN_COST),0) CAP_COST
, 0 CT_CAPITAL_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_FP_ORGO_F_MV FCT
, PJI_PMV_ORG_DIM_TMP HOU
where
FCT.ORG_ID = HOU.ID
AND FCT.ORGANIZATION_ID = HORG.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
union all
select /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, 0 COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,'RC', capital_raw_cost,
'BC', capital_brdn_cost),0) CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAPITAL_COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITALIZABLE_RAW_COST),0) CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
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
TIME.name time_id
, TIME.order_by_id time_key
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 Expense_CHANGE_PRCNT
from pji_pmv_time_dim_tmp time
where name <> '-1')
group by
TIME_KEY
, TIME_ID ORDER BY TIME_KEY ASC;
select PJI_REP_PC7(
TIME_ID
, SUM ( COST )
, SUM ( CT_COST )
, SUM ( COST_CHANGE_PRCNT )
, SUM ( CAP_COST )
, SUM ( CT_CAPITAL_COST )
, SUM ( CAP_COST_CHANGE_PRCNT )
, SUM ( PRCNT_OF_COST )
, SUM ( CT_PRCNT_OF_COST )
, SUM ( PRCNT_OF_COST_CHANGE )
, SUM ( EXPENSE )
, SUM ( CT_EXPENSE )
, SUM ( Expense_CHANGE_PRCNT )
, NULL, NULL, NULL, NULL, NULL, NULL)
bulk collect into l_Project_Cost_Trend_Tab
from (
select /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,'RC', capital_raw_cost,
'BC', capital_brdn_cost),0) cost
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,'RC', FCT.CAPITALIZABLE_RAW_COST,
'BC', FCT.CAPITALIZABLE_BRDN_COST),0) CAP_COST
, 0 CT_CAPITAL_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
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 */
TIME.name time_id
, TIME.order_by_id time_key
, 0 COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,'RC', capital_raw_cost,
'BC', capital_brdn_cost),0) CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAPITAL_COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITALIZABLE_RAW_COST),0) CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
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
TIME.name time_id
, TIME.order_by_id time_key
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 Expense_CHANGE_PRCNT
from pji_pmv_time_dim_tmp time
where name <> '-1')
group by
TIME_KEY
, TIME_ID ORDER BY TIME_KEY ASC;
select PJI_REP_PC7(
TIME_ID
, SUM ( COST )
, SUM ( CT_COST )
, SUM ( COST_CHANGE_PRCNT )
, SUM ( CAP_COST )
, SUM ( CT_CAPITAL_COST )
, SUM ( CAP_COST_CHANGE_PRCNT )
, SUM ( PRCNT_OF_COST )
, SUM ( CT_PRCNT_OF_COST )
, SUM ( PRCNT_OF_COST_CHANGE )
, SUM ( EXPENSE )
, SUM ( CT_EXPENSE )
, SUM ( Expense_CHANGE_PRCNT )
, NULL, NULL, NULL, NULL, NULL, NULL)
bulk collect into l_Project_Cost_Trend_Tab
from (
select /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,'RC', capital_raw_cost,
'BC', capital_brdn_cost),0) cost
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,'RC', FCT.CAPITALIZABLE_RAW_COST,
'BC', FCT.CAPITALIZABLE_BRDN_COST),0) CAP_COST
, 0 CT_CAPITAL_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_ORGO_ET_F_MV FCT
, PJI_PMV_ORG_DIM_TMP HOU
where
FCT.ORG_ID = HOU.ID
AND FCT.ORGANIZATION_ID = HORG.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'ET'
union all
select /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, 0 COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,'RC', capital_raw_cost,
'BC', capital_brdn_cost),0) CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAPITAL_COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITALIZABLE_RAW_COST),0) CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_ORGO_ET_F_MV FCT
, PJI_PMV_ORG_DIM_TMP HOU
where
FCT.org_id = HOU.id
and FCT.organization_id = HORG.id
and FCT.time_id = TIME.prior_id
and TIME.prior_id is not null
and FCT.period_type_id = TIME.period_type
and FCT.calendar_type = TIME.calendar_type
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
and FCT.EXP_EVT_TYPE_ID = ET.ID
and ET.record_type = 'ET'
union all
select
TIME.name time_id
, TIME.order_by_id time_key
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 Expense_CHANGE_PRCNT
from pji_pmv_time_dim_tmp time
where name <> '-1')
group by
TIME_KEY
, TIME_ID ORDER BY TIME_KEY ASC;
select PJI_REP_PC7(
TIME_ID
, SUM ( COST )
, SUM ( CT_COST )
, SUM ( COST_CHANGE_PRCNT )
, SUM ( CAP_COST )
, SUM ( CT_CAPITAL_COST )
, SUM ( CAP_COST_CHANGE_PRCNT )
, SUM ( PRCNT_OF_COST )
, SUM ( CT_PRCNT_OF_COST )
, SUM ( PRCNT_OF_COST_CHANGE )
, SUM ( EXPENSE )
, SUM ( CT_EXPENSE )
, SUM ( Expense_CHANGE_PRCNT )
, NULL, NULL, NULL, NULL, NULL, NULL)
bulk collect into l_Project_Cost_Trend_Tab
from (
select /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,'RC', capital_raw_cost,
'BC', capital_brdn_cost),0) cost
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,'RC', FCT.CAPITALIZABLE_RAW_COST,
'BC', FCT.CAPITALIZABLE_BRDN_COST),0) CAP_COST
, 0 CT_CAPITAL_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_WT_DIM_TMP WT
, PJI_FP_ORGO_ET_WT_F_MV FCT
, PJI_PMV_ORG_DIM_TMP HOU
where
FCT.ORG_ID = HOU.ID
AND FCT.ORGANIZATION_ID = HORG.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.WORK_TYPE_ID = WT.ID
union all
select /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, 0 COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,'RC', capital_raw_cost,
'BC', capital_brdn_cost),0) CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAPITAL_COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITALIZABLE_RAW_COST),0) CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_WT_DIM_TMP WT
, PJI_FP_ORGO_ET_WT_F_MV FCT
, PJI_PMV_ORG_DIM_TMP HOU
where
FCT.org_id = HOU.id
and FCT.organization_id = HORG.id
and FCT.time_id = TIME.prior_id
and TIME.prior_id is not null
and FCT.period_type_id = TIME.period_type
and FCT.calendar_type = TIME.calendar_type
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
and FCT.WORK_TYPE_ID = WT.ID
union all
select
TIME.name time_id
, TIME.order_by_id time_key
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 Expense_CHANGE_PRCNT
from pji_pmv_time_dim_tmp time
where name <> '-1')
group by
TIME_KEY
, TIME_ID ORDER BY TIME_KEY ASC;
select PJI_REP_PC7(
TIME_ID
, SUM ( COST )
, SUM ( CT_COST )
, SUM ( COST_CHANGE_PRCNT )
, SUM ( CAP_COST )
, SUM ( CT_CAPITAL_COST )
, SUM ( CAP_COST_CHANGE_PRCNT )
, SUM ( PRCNT_OF_COST )
, SUM ( CT_PRCNT_OF_COST )
, SUM ( PRCNT_OF_COST_CHANGE )
, SUM ( EXPENSE )
, SUM ( CT_EXPENSE )
, SUM ( Expense_CHANGE_PRCNT )
, NULL, NULL, NULL, NULL, NULL, NULL)
bulk collect into l_Project_Cost_Trend_Tab
from (
select /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,'RC', capital_raw_cost,
'BC', capital_brdn_cost),0) cost
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,'RC', FCT.CAPITALIZABLE_RAW_COST,
'BC', FCT.CAPITALIZABLE_BRDN_COST),0) CAP_COST
, 0 CT_CAPITAL_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
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 = 'ET'
union all
select /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, 0 COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,'RC', capital_raw_cost,
'BC', capital_brdn_cost),0) CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAPITAL_COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITALIZABLE_RAW_COST),0) CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
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 bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.PROJECT_CLASS_ID = CLS.ID
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type = 'ET'
union all
select
TIME.name time_id
, TIME.order_by_id time_key
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 Expense_CHANGE_PRCNT
from pji_pmv_time_dim_tmp time
where name <> '-1')
group by
TIME_KEY
, TIME_ID ORDER BY TIME_KEY ASC;
select PJI_REP_PC7(
TIME_ID
, SUM ( COST )
, SUM ( CT_COST )
, SUM ( COST_CHANGE_PRCNT )
, SUM ( CAP_COST )
, SUM ( CT_CAPITAL_COST )
, SUM ( CAP_COST_CHANGE_PRCNT )
, SUM ( PRCNT_OF_COST )
, SUM ( CT_PRCNT_OF_COST )
, SUM ( PRCNT_OF_COST_CHANGE )
, SUM ( EXPENSE )
, SUM ( CT_EXPENSE )
, SUM ( Expense_CHANGE_PRCNT )
, NULL, NULL, NULL, NULL, NULL, NULL)
bulk collect into l_Project_Cost_Trend_Tab
from (
select /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,'RC', capital_raw_cost,
'BC', capital_brdn_cost),0) cost
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,'RC', FCT.CAPITALIZABLE_RAW_COST,
'BC', FCT.CAPITALIZABLE_BRDN_COST),0) CAP_COST
, 0 CT_CAPITAL_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_ORGO_ET_WT_F_MV FCT
, PJI_PMV_WT_DIM_TMP WT
, PJI_PMV_ORG_DIM_TMP HOU
where
FCT.ORG_ID = HOU.ID
AND FCT.ORGANIZATION_ID = HORG.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND FCT.WORK_TYPE_ID = WT.ID
and ET.record_type = 'ET'
union all
select /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, 0 COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,'RC', capital_raw_cost,
'BC', capital_brdn_cost),0) CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAPITAL_COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITALIZABLE_RAW_COST),0) CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_ORGO_ET_WT_F_MV FCT
, PJI_PMV_WT_DIM_TMP WT
, PJI_PMV_ORG_DIM_TMP HOU
where
FCT.org_id = HOU.id
and FCT.organization_id = HORG.id
and FCT.time_id = TIME.prior_id
and TIME.prior_id is not null
and FCT.period_type_id = TIME.period_type
and FCT.calendar_type = TIME.calendar_type
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND FCT.WORK_TYPE_ID = WT.ID
and ET.record_type = 'ET'
union all
select
TIME.name time_id
, TIME.order_by_id time_key
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 Expense_CHANGE_PRCNT
from pji_pmv_time_dim_tmp time
where name <> '-1')
group by
TIME_KEY
, TIME_ID ORDER BY TIME_KEY ASC;
select PJI_REP_PC7(
TIME_ID
, SUM ( COST )
, SUM ( CT_COST )
, SUM ( COST_CHANGE_PRCNT )
, SUM ( CAP_COST )
, SUM ( CT_CAPITAL_COST )
, SUM ( CAP_COST_CHANGE_PRCNT )
, SUM ( PRCNT_OF_COST )
, SUM ( CT_PRCNT_OF_COST )
, SUM ( PRCNT_OF_COST_CHANGE )
, SUM ( EXPENSE )
, SUM ( CT_EXPENSE )
, SUM ( Expense_CHANGE_PRCNT )
, NULL, NULL, NULL, NULL, NULL, NULL)
bulk collect into l_Project_Cost_Trend_Tab
from (
select /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,'RC', capital_raw_cost,
'BC', capital_brdn_cost),0) cost
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,'RC', FCT.CAPITALIZABLE_RAW_COST,
'BC', FCT.CAPITALIZABLE_BRDN_COST),0) CAP_COST
, 0 CT_CAPITAL_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_FP_CLSO_ET_WT_F_MV FCT
, PJI_PMV_WT_DIM_TMP WT
, PJI_PMV_ORG_DIM_TMP HOU
where
FCT.PROJECT_ORG_ID = HOU.ID
AND FCT.PROJECT_ORGANIZATION_ID = HORG.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.WORK_TYPE_ID = WT.ID
AND FCT.PROJECT_CLASS_ID = CLS.ID
union all
select /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, 0 COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,'RC', capital_raw_cost,
'BC', capital_brdn_cost),0) CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAPITAL_COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITALIZABLE_RAW_COST),0) CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_FP_CLSO_ET_WT_F_MV FCT
, PJI_PMV_WT_DIM_TMP WT
, PJI_PMV_ORG_DIM_TMP HOU
where
FCT.PROJECT_org_id = HOU.id
and FCT.PROJECT_organization_id = HORG.id
and FCT.time_id = TIME.prior_id
and TIME.prior_id is not null
and FCT.period_type_id = TIME.period_type
and FCT.calendar_type = TIME.calendar_type
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.PROJECT_CLASS_ID = CLS.ID
AND FCT.WORK_TYPE_ID = WT.ID
union all
select
TIME.name time_id
, TIME.order_by_id time_key
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 Expense_CHANGE_PRCNT
from pji_pmv_time_dim_tmp time
where name <> '-1')
group by
TIME_KEY
, TIME_ID ORDER BY TIME_KEY ASC;
select PJI_REP_PC7(
TIME_ID
, SUM ( COST )
, SUM ( CT_COST )
, SUM ( COST_CHANGE_PRCNT )
, SUM ( CAP_COST )
, SUM ( CT_CAPITAL_COST )
, SUM ( CAP_COST_CHANGE_PRCNT )
, SUM ( PRCNT_OF_COST )
, SUM ( CT_PRCNT_OF_COST )
, SUM ( PRCNT_OF_COST_CHANGE )
, SUM ( EXPENSE )
, SUM ( CT_EXPENSE )
, SUM ( Expense_CHANGE_PRCNT )
, NULL, NULL, NULL, NULL, NULL, NULL)
bulk collect into l_Project_Cost_Trend_Tab
from (
select /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,'RC', capital_raw_cost,
'BC', capital_brdn_cost),0) cost
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,'RC', FCT.CAPITALIZABLE_RAW_COST,
'BC', FCT.CAPITALIZABLE_BRDN_COST),0) CAP_COST
, 0 CT_CAPITAL_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) EXPENSE
, 0 CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_CLSO_ET_WT_F_MV FCT
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_WT_DIM_TMP WT
, PJI_PMV_ORG_DIM_TMP HOU
where
FCT.PROJECT_ORG_ID = HOU.ID
AND FCT.PROJECT_ORGANIZATION_ID = HORG.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.WORK_TYPE_ID = WT.ID
AND FCT.PROJECT_CLASS_ID = CLS.ID
AND FCT.EXP_EVT_TYPE_ID = ET.ID
and ET.record_type = 'ET'
union all
select /*+ ORDERED */
TIME.name time_id
, TIME.order_by_id time_key
, 0 COST
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,'RC', capital_raw_cost,
'BC', capital_brdn_cost),0) CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAPITAL_COST
, decode(NVl(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITALIZABLE_RAW_COST),0) CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, decode(NVL(TIME.amount_type,1),1,
decode(G_Report_Cost_Type,
'BC', FCT.CAPITAL_BRDN_COST - FCT.CAPITALIZABLE_BRDN_COST,
'RC', FCT.CAPITAL_RAW_COST - FCT.CAPITALIZABLE_RAW_COST),0) CT_EXPENSE
, 0 EXPENSE_CHANGE_PRCNT
from
PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_CLSO_ET_WT_F_MV FCT
, PJI_PMV_CLS_DIM_TMP CLS
, PJI_PMV_WT_DIM_TMP WT
, PJI_PMV_ORG_DIM_TMP HOU
where
FCT.PROJECT_org_id = HOU.id
and FCT.PROJECT_organization_id = HORG.id
and FCT.time_id = TIME.prior_id
and TIME.prior_id is not null
and FCT.period_type_id = TIME.period_type
and FCT.calendar_type = TIME.calendar_type
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.WORK_TYPE_ID = WT.ID
AND FCT.PROJECT_CLASS_ID = CLS.ID
AND FCT.EXP_EVT_TYPE_ID = ET.ID
and ET.record_type = 'ET'
union all
select
TIME.name time_id
, TIME.order_by_id time_key
, 0 COST
, 0 CT_COST
, 0 COST_CHANGE_PRCNT
, 0 CAP_COST
, 0 CT_CAP_COST
, 0 CAP_COST_CHANGE_PRCNT
, 0 PRCNT_OF_COST
, 0 CT_PRCNT_OF_COST
, 0 PRCNT_OF_COST_CHANGE
, 0 EXPENSE
, 0 CT_EXPENSE
, 0 Expense_CHANGE_PRCNT
from pji_pmv_time_dim_tmp time
where name <> '-1')
group by
TIME_KEY
, TIME_ID ORDER BY TIME_KEY ASC;
SELECT report_cost_type
INTO G_Report_Cost_Type
FROM pji_system_settings;
DELETE pji_pmv_prj_dim_tmp;
INSERT INTO pji_pmv_prj_dim_tmp (id, name)
SELECT DISTINCT prj.project_id, '-1' name
FROM
pji_project_classes PJM
, pji_pmv_cls_dim_tmp PTM
, pji_pmv_orgz_dim_tmp org
, pa_projects_all prj
WHERE
pjm.project_class_id = ptm.id
AND prj.project_id = pjm.project_id
AND prj.carrying_out_organization_id = org.ID;
DELETE pji_pmv_prj_dim_tmp;
INSERT INTO pji_pmv_prj_dim_tmp (id, name)
SELECT DISTINCT prj.project_id, '-1' name
FROM
pji_project_classes PJM
, (SELECT project_id
FROM pji_project_classes
WHERE class_category = '$PROJECT_TYPE$CAPITAL') PJC
, pji_pmv_cls_dim_tmp PTM
, pji_pmv_orgz_dim_tmp org
, pa_projects_all prj
WHERE 1=1
AND pjm.project_class_id = ptm.id
AND prj.project_id = pjc.project_id
AND prj.project_id = pjm.project_id
AND prj.carrying_out_organization_id = org.ID;
INSERT INTO pji_pmv_prj_dim_tmp (id, name)
SELECT DISTINCT prj.project_id, '-1' name
FROM
pji_project_classes PJM
, pji_pmv_orgz_dim_tmp org
, pa_projects_all prj
WHERE
prj.project_id = pjm.project_id
AND prj.carrying_out_organization_id = org.ID
AND pjm.class_category = '$PROJECT_TYPE$CAPITAL';
SELECT PJI_REP_PC9 (PROJECT_ID
, NULL
, NULL
, PJI_PMV_UTIL.Drill_To_Proj_Perf_URL(PROJECT_ID, l_curr_record_type_id, p_As_of_Date,p_Period_Type)
, NULL
, NULL
, ORGANIZATION_ID
, NULL
, SUM (COST)
, SUM (CAPITAL_COST)
, NULL
, SUM (EXPENSE)
, NULL
, NULL
, 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(NVL(TIME.amount_type,1),1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost, 0), 0) AS COST
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(G_Report_Cost_Type, 'BC', fct.capitalizable_brdn_cost,
'RC', fct.capitalizable_raw_cost, 0), 0) AS CAPITAL_COST
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost - fct.capitalizable_brdn_cost,
'RC', fct.raw_cost - fct.capitalizable_raw_cost, 0), 0) AS EXPENSE
FROM PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ORGZ_DIM_TMP HORG
, 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.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_PC9 (PROJECT_ID
, NULL
, NULL
, PJI_PMV_UTIL.Drill_To_Proj_Perf_URL(PROJECT_ID, l_curr_record_type_id, p_As_of_Date,p_Period_Type)
, NULL
, NULL
, ORGANIZATION_ID
, NULL
, SUM(COST)
, SUM(CAPITAL_COST)
, NULL
, SUM(EXPENSE)
, NULL
, NULL
, 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(NVL(TIME.amount_type,1),1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost),0) AS COST
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(G_Report_Cost_Type, 'BC', fct.capitalizable_brdn_cost,
'RC', fct.capitalizable_raw_cost),0) AS CAPITAL_COST
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost - fct.capitalizable_brdn_cost,
'RC', fct.raw_cost - fct.capitalizable_raw_cost),0) AS EXPENSE
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_PC9 (PROJECT_ID
, NULL
, NULL
, PJI_PMV_UTIL.Drill_To_Proj_Perf_URL(PROJECT_ID, l_curr_record_type_id, p_As_of_Date,p_Period_Type)
, NULL
, NULL
, ORGANIZATION_ID
, NULL
, SUM(COST)
, SUM(CAPITAL_COST)
, NULL
, SUM(EXPENSE)
, NULL
, NULL
, 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(NVL(TIME.amount_type,1),1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost),0) AS COST
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(G_Report_Cost_Type, 'BC', fct.capitalizable_brdn_cost,
'RC', fct.capitalizable_raw_cost),0) AS CAPITAL_COST
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost - fct.capitalizable_brdn_cost,
'RC', fct.raw_cost - fct.capitalizable_raw_cost),0) AS EXPENSE
FROM PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_PRJ_DIM_TMP PRJ
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_PROJ_ET_F FCT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE 1=1
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.PROJECT_ID = PRJ.ID
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type ='ET'
AND FCT.PROJECT_ORG_ID = HOU.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
) FCT
GROUP BY PROJECT_ID,ORGANIZATION_ID;
SELECT PJI_REP_PC9 (PROJECT_ID
, NULL
, NULL
, PJI_PMV_UTIL.Drill_To_Proj_Perf_URL(PROJECT_ID, l_curr_record_type_id, p_As_of_Date,p_Period_Type)
, NULL
, NULL
, ORGANIZATION_ID
, NULL
, SUM(COST)
, SUM(CAPITAL_COST)
, NULL
, SUM(EXPENSE)
, NULL
, NULL
, 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(NVL(TIME.amount_type,1),1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost),0) AS COST
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(G_Report_Cost_Type, 'BC', fct.capitalizable_brdn_cost,
'RC', fct.capitalizable_raw_cost),0) AS CAPITAL_COST
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost - fct.capitalizable_brdn_cost,
'RC', fct.raw_cost - fct.capitalizable_raw_cost),0) AS EXPENSE
FROM PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_WT_DIM_TMP WT
, PJI_PMV_PRJ_DIM_TMP PRJ
, 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_PC9 (PROJECT_ID
, NULL
, NULL
, PJI_PMV_UTIL.Drill_To_Proj_Perf_URL(PROJECT_ID, l_curr_record_type_id, p_As_of_Date,p_Period_Type)
, NULL
, NULL
, ORGANIZATION_ID
, NULL
, SUM(COST)
, SUM(CAPITAL_COST)
, NULL
, SUM(EXPENSE)
, NULL
, NULL
, 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(NVL(TIME.amount_type,1),1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost),0) AS COST
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(G_Report_Cost_Type, 'BC', fct.capitalizable_brdn_cost,
'RC', fct.capitalizable_raw_cost),0) AS CAPITAL_COST
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost - fct.capitalizable_brdn_cost,
'RC', fct.raw_cost - fct.capitalizable_raw_cost),0) AS EXPENSE
FROM PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_PRJ_DIM_TMP PRJ
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_FP_PROJ_ET_F FCT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE 1=1
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.PROJECT_ID = PRJ.ID
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type ='ET'
AND FCT.PROJECT_ORG_ID = HOU.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
) FCT
GROUP BY PROJECT_ID,ORGANIZATION_ID;
SELECT PJI_REP_PC9 (PROJECT_ID
, NULL
, NULL
, PJI_PMV_UTIL.Drill_To_Proj_Perf_URL(PROJECT_ID, l_curr_record_type_id, p_As_of_Date,p_Period_Type)
, NULL
, NULL
, ORGANIZATION_ID
, NULL
, SUM(COST)
, SUM(CAPITAL_COST)
, NULL
, SUM(EXPENSE)
, NULL
, NULL
, 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(NVL(TIME.amount_type,1),1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost),0) AS COST
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(G_Report_Cost_Type, 'BC', fct.capitalizable_brdn_cost,
'RC', fct.capitalizable_raw_cost),0) AS CAPITAL_COST
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost - fct.capitalizable_brdn_cost,
'RC', fct.raw_cost - fct.capitalizable_raw_cost),0) AS EXPENSE
FROM PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_PMV_WT_DIM_TMP WT
, PJI_PMV_PRJ_DIM_TMP PRJ
, PJI_FP_PROJ_ET_WT_F FCT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE 1=1
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.PROJECT_ID = PRJ.ID
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type ='ET'
AND FCT.WORK_TYPE_ID = WT.ID
AND FCT.PROJECT_ORG_ID = HOU.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
) FCT
GROUP BY PROJECT_ID,ORGANIZATION_ID;
SELECT PJI_REP_PC9 (PROJECT_ID
, NULL
, NULL
, PJI_PMV_UTIL.Drill_To_Proj_Perf_URL(PROJECT_ID, l_curr_record_type_id, p_As_of_Date,p_Period_Type)
, NULL
, NULL
, ORGANIZATION_ID
, NULL
, SUM(COST)
, SUM(CAPITAL_COST)
, NULL
, SUM(EXPENSE)
, NULL
, NULL
, 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(NVL(TIME.amount_type,1),1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost), 0) AS COST
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(G_Report_Cost_Type, 'BC', fct.capitalizable_brdn_cost,
'RC', fct.capitalizable_raw_cost), 0) AS CAPITAL_COST
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost - fct.capitalizable_brdn_cost,
'RC', fct.raw_cost - fct.capitalizable_raw_cost), 0) AS EXPENSE
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_PC9 (PROJECT_ID
, NULL
, NULL
, PJI_PMV_UTIL.Drill_To_Proj_Perf_URL(PROJECT_ID, l_curr_record_type_id, p_As_of_Date,p_Period_Type)
, NULL
, NULL
, ORGANIZATION_ID
, NULL
, SUM(COST)
, SUM(CAPITAL_COST)
, NULL
, SUM(EXPENSE)
, NULL
, NULL
, 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(NVL(TIME.amount_type,1),1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
'RC', fct.raw_cost),0) AS COST
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(G_Report_Cost_Type, 'BC', fct.capitalizable_brdn_cost,
'RC', fct.capitalizable_raw_cost),0) AS CAPITAL_COST
, DECODE(NVL(TIME.amount_type,1),1,
DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost - fct.capitalizable_brdn_cost,
'RC', fct.raw_cost - fct.capitalizable_raw_cost),0) AS EXPENSE
FROM PJI_PMV_TIME_DIM_TMP TIME
, PJI_PMV_PRJ_DIM_TMP PRJ
, PJI_PMV_ET_RT_DIM_TMP ET
, PJI_PMV_WT_DIM_TMP WT
, PJI_FP_PROJ_ET_WT_F FCT
, PJI_PMV_ORG_DIM_TMP HOU
WHERE 1=1
AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
AND FCT.PROJECT_ID = PRJ.ID
AND FCT.EXP_EVT_TYPE_ID = ET.ID
AND ET.record_type ='ET'
AND FCT.WORK_TYPE_ID = WT.ID
AND FCT.PROJECT_ORG_ID = HOU.ID
AND FCT.TIME_ID = TIME.ID
AND TIME.ID IS NOT NULL
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
) FCT
GROUP BY PROJECT_ID,ORGANIZATION_ID;
SELECT NAME
, SEGMENT1
, PROJECT_TYPE
INTO l_detail_tab(i).PROJECT_NAME
, l_detail_tab(i).PROJECT_NUMBER
, l_detail_tab(i).PROJECT_TYPE
FROM PA_PROJECTS_ALL
WHERE PROJECT_ID = l_detail_tab(i).PROJECT_ID;
SELECT NAME
INTO l_detail_tab(i).ORGANIZATION_NAME
FROM HR_ALL_ORGANIZATION_UNITS_TL
WHERE LANGUAGE = USERENV ('LANG')
AND ORGANIZATION_ID = l_detail_tab(i).ORGANIZATION_NAME;