[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
,P_SELECT_LIST =>
' FACT.BILL_HOURS_PERCENT "PJI_REP_MSR_3",
FACT.CHANGE_PERCENT_2 "PJI_REP_MSR_20",
FACT.ACT_UTIL_PERCENT "PJI_REP_MSR_2",
FACT.CHANGE_PERCENT_1 "PJI_REP_MSR_10",
FACT.RES_HOURS_PERCENT "PJI_REP_MSR_4",
FACT.CHANGE_PERCENT_3 "PJI_REP_MSR_21",
FACT.ACTUAL_UTIL_HOURS "PJI_REP_MSR_5",
FACT.ACTUAL_DENOMINATOR "PJI_REP_MSR_6",
FACT.RESOURCE_HOURS "PJI_REP_MSR_7",
FACT.TOTAL_RESOURCE_HOURS "PJI_REP_MSR_8",
FACT.BILLABLE_HOURS "PJI_REP_MSR_9",
FACT.PRIOR_ACTUAL_UTIL_HOURS "PJI_REP_MSR_11",
FACT.PRIOR_ACTUAL_DENOMINATOR "PJI_REP_MSR_12",
FACT.PRIOR_RESOURCE_HOURS "PJI_REP_MSR_13",
FACT.PRIOR_TOTAL_RESOURCE_HOURS "PJI_REP_MSR_14",
FACT.PRIOR_BILLABLE_HOURS "PJI_REP_MSR_15",
FACT.PRIOR_RES_HOURS_PERCENT "PJI_REP_MSR_16",
FACT.PRIOR_ACT_UTIL_PERCENT "PJI_REP_MSR_17",
FACT.PRIOR_BILL_HOURS_PERCENT "PJI_REP_MSR_18",
FACT.BILL_HOURS_PERCENT "PJI_REP_MSR_19",
FACT.ACT_UTIL_PERCENT "PJI_REP_MSR_22",
FACT.RES_HOURS_PERCENT "PJI_REP_MSR_23",
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_1 "PJI_REP_TOTAL_12",
FACT.PJI_REP_TOTAL_3 "PJI_REP_TOTAL_14",
FACT.PJI_REP_TOTAL_5 "PJI_REP_TOTAL_16" '
,P_SQL_STATEMENT => x_PMV_Sql
,P_PMV_OUTPUT => x_PMV_Output,
P_REGION_CODE => 'PJI_REP_UAP1',
P_PLSQL_DRIVER => 'PJI_PMV_UTLZ.PLSQLDriver_U1',
P_PLSQL_DRIVER_PARAMS => '<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, ' ||
'<>');
,P_SELECT_LIST =>
' FACT.ACT_UTIL_PERCENT "PJI_REP_MSR_1",
FACT.CHANGE_PERCENT_1 "PJI_REP_MSR_19",
FACT.SCH_UTIL_PERCENT "PJI_REP_MSR_2",
FACT.SCH_VAR_PERCENT "PJI_REP_MSR_10",
FACT.BILL_HOURS_PERCENT "PJI_REP_MSR_3",
FACT.CHANGE_PERCENT_2 "PJI_REP_MSR_20",
FACT.MISSING_HOURS "PJI_REP_MSR_4",
FACT.MISSING_HOURS_PERCENT "PJI_REP_MSR_21",
FACT.ACTUAL_UTIL_HOURS "PJI_REP_MSR_5",
FACT.ACTUAL_DENOMINATOR "PJI_REP_MSR_6",
FACT.SCHEDULED_UTIL_HOURS "PJI_REP_MSR_7",
FACT.SCHEDULED_DENOMINATOR "PJI_REP_MSR_8",
FACT.BILLABLE_HOURS "PJI_REP_MSR_9",
FACT.PRIOR_ACTUAL_UTIL_HOURS "PJI_REP_MSR_11",
FACT.PRIOR_ACTUAL_DENOMINATOR "PJI_REP_MSR_12",
FACT.PRIOR_SCH_UTIL_HOURS "PJI_REP_MSR_13",
FACT.PRIOR_SCHEDULED_DENOMINATOR "PJI_REP_MSR_14",
FACT.PRIOR_BILLABLE_HOURS "PJI_REP_MSR_15",
FACT.PRIOR_MISSING_HOURS "PJI_REP_MSR_16",
FACT.PRIOR_ACT_UTIL_PERCENT "PJI_REP_MSR_17",
FACT.PRIOR_BILL_HOURS_PERCENT "PJI_REP_MSR_18",
FACT.ACT_UTIL_PERCENT "PJI_REP_MSR_23",
FACT.SCH_UTIL_PERCENT "PJI_REP_MSR_24",
FACT.BILL_HOURS_PERCENT "PJI_REP_MSR_25",
FACT.MISSING_HOURS "PJI_REP_MSR_26",
FACT.PJI_REP_TOTAL_3 "PJI_REP_TOTAL_1",
FACT.PJI_REP_TOTAL_8 "PJI_REP_TOTAL_2",
FACT.PJI_REP_TOTAL_10 "PJI_REP_TOTAL_3",
FACT.PJI_REP_TOTAL_12 "PJI_REP_TOTAL_4",
FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_5",
FACT.PJI_REP_TOTAL_7 "PJI_REP_TOTAL_6",
FACT.PJI_REP_TOTAL_13 "PJI_REP_TOTAL_7",
FACT.PJI_REP_TOTAL_11 "PJI_REP_TOTAL_8" '
,P_SQL_STATEMENT => x_PMV_Sql
,P_PMV_OUTPUT => x_PMV_Output,
P_REGION_CODE => 'PJI_REP_U1',
P_PLSQL_DRIVER => 'PJI_PMV_UTLZ.PLSQLDriver_U1',
P_PLSQL_DRIVER_PARAMS => '<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, ' ||
'<>,'||
'<>, ' ||
'<>, ' ||
'<>');
,P_SELECT_LIST =>
' FACT.ACTUAL_CAPACITY_HOURS "PJI_REP_MSR_1",
FACT.MISSING_HOURS "PJI_REP_MSR_2",
FACT.ACTUAL_UTILIZATION_HOURS "PJI_REP_MSR_3",
FACT.ACT_UTIL_PERCENT "PJI_REP_MSR_4",
FACT.BILL_UTIL_PERCENT "PJI_REP_MSR_6",
FACT.NONBILL_UTIL_PERCENT "PJI_REP_MSR_7",
FACT.TRAINING_PERCENT "PJI_REP_MSR_20",
FACT.SCHEDULED_CAPACITY_HOURS "PJI_REP_MSR_5",
FACT.CONF_SCHEDULED_HOURS "PJI_REP_MSR_8",
FACT.PROV_SCHEDULED_HOURS "PJI_REP_MSR_9",
FACT.SCHEDULED_UTILIZATION_HOURS "PJI_REP_MSR_10",
FACT.SCH_UTIL_PERCENT "PJI_REP_MSR_17",
FACT.PRIOR_UTIL_PERCENT "PJI_REP_MSR_18",
FACT.BILLABLE_HOURS "PJI_REP_MSR_11",
FACT.NONBILLABLE_HOURS "PJI_REP_MSR_12",
FACT.TRAINING_HOURS "PJI_REP_MSR_13",
FACT.PRIOR_ACTUAL_UTIL_HOURS "PJI_REP_MSR_14",
FACT.PRIOR_ACTUAL_DENOMINATOR "PJI_REP_MSR_15",
FACT.SCHEDULED_DENOMINATOR "PJI_REP_MSR_19",
FACT.ACTUAL_DENOMINATOR "PJI_REP_MSR_23",
FACT.PRIOR_UTIL_PERCENT "PJI_REP_MSR_24",
FACT.ACT_UTIL_PERCENT "PJI_REP_MSR_25",
FACT.SCH_UTIL_PERCENT "PJI_REP_MSR_26" '
,P_SQL_STATEMENT => x_PMV_Sql
,P_PMV_OUTPUT => x_PMV_Output,
P_REGION_CODE => 'PJI_REP_U2',
P_PLSQL_DRIVER => 'PJI_PMV_UTLZ.PLSQLDriver_U2',
P_PLSQL_DRIVER_PARAMS => '<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, ' ||
'<>');
,P_SELECT_LIST =>
' FACT.ACTUAL_HOURS "PJI_REP_MSR_11",
FACT.CAPACITY_HOURS "PJI_REP_MSR_16",
FACT.MISSING_HOURS "PJI_REP_MSR_15",
FACT.UTILIZATION_HOURS "PJI_REP_MSR_1",
FACT.UTIL_PERCENT "PJI_REP_MSR_2",
FACT.BILL_PERCENT "PJI_REP_MSR_3",
FACT.NON_BILL_PERCENT "PJI_REP_MSR_4",
FACT.TRAINING_PERCENT "PJI_REP_MSR_5",
FACT.PRIOR_ACTUAL_HOURS "PJI_REP_MSR_12",
FACT.PRIOR_CAPACITY_HOURS "PJI_REP_MSR_19",
FACT.PRIOR_UTIL_PERCENT "PJI_REP_MSR_20",
FACT.PRIOR_BILL_PERCENT "PJI_REP_MSR_21",
FACT.PRIOR_NON_BILL_PERCENT "PJI_REP_MSR_22",
FACT.PRIOR_TRAINING_PERCENT "PJI_REP_MSR_23",
FACT.PRIOR_BILL_PERCENT "PJI_REP_MSR_10",
FACT.BILL_PERCENT "PJI_REP_MSR_30",
FACT.PRIOR_NON_BILL_PERCENT "PJI_REP_MSR_8",
FACT.NON_BILL_PERCENT "PJI_REP_MSR_9",
FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_1",
FACT.PJI_REP_TOTAL_2 "PJI_REP_TOTAL_2",
FACT.PJI_REP_TOTAL_3 "PJI_REP_TOTAL_3",
FACT.PJI_REP_TOTAL_4 "PJI_REP_TOTAL_4",
FACT.PJI_REP_TOTAL_5 "PJI_REP_TOTAL_5",
FACT.PJI_REP_TOTAL_6 "PJI_REP_TOTAL_6",
FACT.PJI_REP_TOTAL_7 "PJI_REP_TOTAL_7",
FACT.PJI_REP_TOTAL_8 "PJI_REP_TOTAL_8",
FACT.PJI_REP_TOTAL_9 "PJI_REP_TOTAL_9",
FACT.PJI_REP_TOTAL_10 "PJI_REP_TOTAL_10",
FACT.PJI_REP_TOTAL_11 "PJI_REP_TOTAL_11",
FACT.PJI_REP_TOTAL_12 "PJI_REP_TOTAL_12",
FACT.PJI_REP_TOTAL_13 "PJI_REP_TOTAL_13",
FACT.PJI_REP_TOTAL_14 "PJI_REP_TOTAL_14" '
,P_SQL_STATEMENT => x_PMV_Sql
,P_PMV_OUTPUT => x_PMV_Output,
P_REGION_CODE => 'PJI_REP_U3',
P_PLSQL_DRIVER => 'PJI_PMV_UTLZ.PLSQLDriver_U3',
P_PLSQL_DRIVER_PARAMS => '<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, ' ||
'<>,'||
'<>, ' ||
'<>, ' ||
'<>');
,P_SELECT_LIST =>
' FACT.SCHEDULED_HOURS "PJI_REP_MSR_17",
FACT.SCHEDULED_CAPACITY_HOURS "PJI_REP_MSR_18",
FACT.SCHEDULED_UTIL_HOURS "PJI_REP_MSR_1",
FACT.PROVISIONAL_HOURS "PJI_REP_MSR_2",
FACT.PRIOR_SCHEDULED_HOURS "PJI_REP_MSR_3",
FACT.SCH_UTIL_PERCENT "PJI_REP_MSR_4",
FACT.BILL_UTIL_PERCENT "PJI_REP_MSR_7",
FACT.NONBILL_UTIL_PERCENT "PJI_REP_MSR_5",
FACT.UNASSIGNED_PERCENT "PJI_REP_MSR_6",
FACT.TRAINING_PERCENT "PJI_REP_MSR_13",
FACT.PROV_BILL_PERCENT "PJI_REP_MSR_11",
FACT.PROV_NONBILL_PERCENT "PJI_REP_MSR_12",
FACT.PRIOR_SCH_UTIL_PERCENT "PJI_REP_MSR_14",
FACT.PRIOR_BILL_UTIL_PERCENT "PJI_REP_MSR_15",
FACT.PRIOR_NONBILL_UTIL_PERCENT "PJI_REP_MSR_16",
FACT.PRIOR_BILL_UTIL_PERCENT "PJI_MSR2",
FACT.BILL_UTIL_PERCENT "PJI_REP_MSR_29",
FACT.PRIOR_NONBILL_UTIL_PERCENT "PJI_MSR3",
FACT.NONBILL_UTIL_PERCENT "PJI_MSR1",
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_7 "PJI_REP_TOTAL_5",
FACT.PJI_REP_TOTAL_8 "PJI_REP_TOTAL_6",
FACT.PJI_REP_TOTAL_9 "PJI_REP_TOTAL_7",
FACT.PJI_REP_TOTAL_10 "PJI_REP_TOTAL_8",
FACT.PJI_REP_TOTAL_11 "PJI_REP_TOTAL_9",
FACT.PJI_REP_TOTAL_12 "PJI_REP_TOTAL_10",
FACT.PJI_REP_TOTAL_13 "PJI_REP_TOTAL_11",
FACT.PJI_REP_TOTAL_14 "PJI_REP_TOTAL_12",
FACT.PJI_REP_TOTAL_15 "PJI_REP_TOTAL_13",
FACT.PJI_REP_TOTAL_16 "PJI_REP_TOTAL_14",
FACT.PJI_REP_TOTAL_17 "PJI_REP_TOTAL_15" '
,P_SQL_STATEMENT => x_PMV_Sql
,P_PMV_OUTPUT => x_PMV_Output,
P_REGION_CODE => 'PJI_REP_U4',
P_PLSQL_DRIVER => 'PJI_PMV_UTLZ.PLSQLDriver_U4',
P_PLSQL_DRIVER_PARAMS => '<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, ' ||
'<>,'||
'<>, ' ||
'<>, ' ||
'<>');
,P_SELECT_LIST =>
' FACT.EXPECTED_HOURS "PJI_REP_MSR_17",
FACT.EXPECTED_CAPACITY_HOURS "PJI_REP_MSR_18",
FACT.EXP_ACT_UTIL_PERCENT "PJI_REP_MSR_1",
FACT.EXP_SCH_UTIL_PERCENT "PJI_REP_MSR_2",
FACT.EXP_UTIL_PERCENT "PJI_REP_MSR_3",
FACT.PROV_SCH_UTIL_PERCENT "PJI_REP_MSR_4",
FACT.EXP_TOTAL_UTIL_PERCENT "PJI_REP_MSR_6",
FACT.PRIOR_ACT_UTIL_PERCENT "PJI_REP_MSR_7",
FACT.EXP_UTIL_PERCENT "PJI_REP_MSR_23",
FACT.EXP_TOTAL_UTIL_PERCENT "PJI_REP_MSR_24",
FACT.PJI_REP_TOTAL_5 "PJI_REP_TOTAL_1",
FACT.PJI_REP_TOTAL_6 "PJI_REP_TOTAL_2",
FACT.PJI_REP_TOTAL_18 "PJI_REP_TOTAL_3",
FACT.PJI_REP_TOTAL_19 "PJI_REP_TOTAL_4",
FACT.PJI_REP_TOTAL_20 "PJI_REP_TOTAL_5",
FACT.PJI_REP_TOTAL_21 "PJI_REP_TOTAL_6",
FACT.PJI_REP_TOTAL_22 "PJI_REP_TOTAL_7",
FACT.PJI_REP_TOTAL_23 "PJI_REP_TOTAL_8" '
,P_SQL_STATEMENT => x_PMV_Sql
,P_PMV_OUTPUT => x_PMV_Output,
P_REGION_CODE => 'PJI_REP_U5',
P_PLSQL_DRIVER => 'PJI_PMV_UTLZ.PLSQLDriver_U4',
P_PLSQL_DRIVER_PARAMS => '<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, ' ||
'<>,'||
'<>, ' ||
'<>, ' ||
'<>');
SELECT fnd_profile.value('PA_ORG_UTIL_DEF_CALC_METHOD')
INTO l_denominator
from dual;
select report_labor_units
into l_labor_unit
from pji_system_settings;
SELECT seq
INTO l_sequence
FROM pji_mt_buckets
WHERE bucket_set_code = 'PJI_RESOURCE_AVAILABILITY'
AND default_flag = 'Y';
SELECT PJI_REP_U1(org_id,
organization_id,
time_id,
time_key,
util_category_id,
work_type_id,
job_level_id,
sum(actual_capacity_hours),
sum(missing_hours),
sum(actual_util_hours),
sum(billable_hours),
sum(scheduled_util_hours),
sum(scheduled_capacity_hours),
sum(resource_hours),
sum(total_resource_hours),
sum(decode(l_denominator, 'CAPACITY', actual_capacity_hours, actual_hours)),
sum(decode(l_denominator, 'CAPACITY', scheduled_capacity_hours, actual_hours)),
sum(prior_actual_capacity_hours),
sum(prior_missing_hours),
sum(prior_actual_util_hours),
sum(prior_billable_hours),
sum(prior_sch_util_hours),
sum(prior_sch_capacity_hours),
sum(prior_resource_hours),
sum(prior_total_resource_hours),
sum(decode(l_denominator, 'CAPACITY', prior_actual_capacity_hours, prior_actual_hours)),
sum(decode(l_denominator, 'CAPACITY', prior_sch_capacity_hours, prior_actual_hours)),
null,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_u1_tbl
FROM ( /* Bug 3515594 */
SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
'-1' util_category_id,
'-1' work_type_id,
'-1' job_level_id,
(capacity_hrs - reduce_capacity_hrs_a) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) actual_capacity_hours,
missing_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) missing_hours,
total_wtd_org_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
actual_util_hours,
bill_wtd_org_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
billable_hours,
conf_wtd_org_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
scheduled_util_hours,
(capacity_hrs - reduce_capacity_hrs_s) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
scheduled_capacity_hours,
total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
actual_hours,
conf_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
scheduled_hours,
DECODE (l_sequence, 1, available_hrs_bkt1_s,
2, available_hrs_bkt2_s,
3, available_hrs_bkt3_s,
4, available_hrs_bkt4_s,
5, available_hrs_bkt5_s)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
resource_hours,
capacity_hrs / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
total_resource_hours,
0 prior_actual_capacity_hours,
0 prior_missing_hours,
0 prior_actual_util_hours,
0 prior_billable_hours,
0 prior_sch_util_hours,
0 prior_sch_capacity_hours,
0 prior_actual_hours,
0 prior_scheduled_hours,
0 prior_resource_hours,
0 prior_total_resource_hours
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_RM_ORGO_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND hou.id = imp.org_id
UNION ALL
/* Bug 3515594 */
SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
'-1' util_category_id,
'-1' work_type_id,
'-1' job_level_id,
0 actual_capacity_hours,
0 missing_hours,
0 actual_util_hours,
0 billable_hours,
0 scheduled_util_hours,
0 scheduled_capacity_hours,
0 actual_hours,
0 scheduled_hours,
0 resource_hours,
0 total_resource_hours,
(capacity_hrs - reduce_capacity_hrs_a) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_actual_capacity_hours,
missing_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_missing_hours,
total_wtd_org_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_actual_util_hours,
bill_wtd_org_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_billable_hours,
conf_wtd_org_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_sch_util_hours,
(capacity_hrs - reduce_capacity_hrs_s) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_sch_capacity_hours,
total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_actual_hours,
conf_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_scheduled_hours,
DECODE (l_sequence, 1, available_hrs_bkt1_s,
2, available_hrs_bkt2_s,
3, available_hrs_bkt3_s,
4, available_hrs_bkt4_s,
5, available_hrs_bkt5_s)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_resource_hours,
capacity_hrs / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_total_resource_hours
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TCMP_DIM_TMP time,
PJI_RM_ORGO_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND hou.id = imp.org_id
UNION ALL
SELECT '-1' org_id,
name organization_id,
'-1' time_id,
-1 time_key,
'-1' util_category_id,
'-1' work_type_id,
'-1' job_level_id,
0 actual_capacity_hours,
0 missing_hours,
0 actual_util_hours,
0 billable_hours,
0 scheduled_util_hours,
0 scheduled_capacity_hours,
0 actual_hours,
0 scheduled_hours,
0 resource_hours,
0 total_resource_hours,
0 prior_actual_capacity_hours,
0 prior_missing_hours,
0 prior_actual_util_hours,
0 prior_billable_hours,
0 prior_sch_util_hours,
0 prior_sch_capacity_hours,
0 prior_actual_hours,
0 prior_scheduled_hours,
0 prior_resource_hours,
0 prior_total_resource_hours
FROM PJI_PMV_ORGZ_DIM_TMP
WHERE name <> '-1'
UNION ALL
SELECT '-1' org_id,
'-1' organization_id,
name time_id,
order_by_id time_key,
'-1' util_category_id,
'-1' work_type_id,
'-1' job_level_id,
0 actual_capacity_hours,
0 missing_hours,
0 actual_util_hours,
0 billable_hours,
0 scheduled_utilization_hours,
0 scheduled_capacity_hours,
0 actual_hours,
0 scheduled_hours,
0 resource_hours,
0 total_resource_hours,
0 prior_actual_capacity_hours,
0 prior_missing_hours,
0 prior_actual_util_hours,
0 prior_billable_hours,
0 prior_sch_util_hours,
0 prior_sch_capacity_hours,
0 prior_actual_hours,
0 prior_scheduled_hours,
0 prior_resource_hours,
0 prior_total_resource_hours
FROM PJI_PMV_TIME_DIM_TMP
WHERE name <> '-1'
)
GROUP BY org_id,
organization_id,
time_id,
time_key,
util_category_id,
work_type_id,
job_level_id;
SELECT PJI_REP_U1(org_id,
organization_id,
time_id,
time_key,
util_category_id,
work_type_id,
job_level_id,
sum(capacity_hours-act_reduce_capacity_hours),
sum(decode (p_view_by, 'UC', null, 'WT',null, missing_hours)),
sum(actual_util_hours),
sum(billable_hours),
sum(scheduled_util_hours),
sum(capacity_hours-sch_reduce_capacity_hours),
0, -- resource_hours
0, -- total_resource_hours,
sum(decode(l_denominator, 'CAPACITY', capacity_hours-act_reduce_capacity_hours, total_actual_hours)),
sum(decode(l_denominator, 'CAPACITY', capacity_hours-sch_reduce_capacity_hours, total_actual_hours)),
sum(prior_act_capacity_hours-prior_act_red_capacity_hours),
sum(decode (p_view_by, 'UC', null, 'WT',null, prior_missing_hours)),
sum(prior_actual_util_hours),
sum(prior_billable_hours),
sum(prior_sch_util_hours),
sum(prior_act_capacity_hours-prior_act_red_capacity_hours),
0, -- prior_resource_hours
0, -- prior_total_resource_hours
sum(decode(l_denominator, 'CAPACITY', prior_act_capacity_hours-prior_act_red_capacity_hours,
prior_total_actual_hours)),
sum(decode(l_denominator, 'CAPACITY', prior_act_capacity_hours-prior_act_red_capacity_hours,
prior_total_actual_hours)),
null,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_u1_tbl
FROM ( /* Bug 3515594 */
SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
decode(p_view_by, 'UC', wt.name, '-1') util_category_id,
decode(p_view_by, 'WT', wt.name, '-1') work_type_id,
'-1' job_level_id,
0 capacity_hours,
0 act_reduce_capacity_hours,
null missing_hours,
0 total_actual_hours,
(fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)) *
worktype.org_utilization_percentage / 100
actual_util_hours,
(fct.bill_hrs_a * worktype.org_utilization_percentage / 100)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
billable_hours,
(fct.conf_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)) *
worktype.org_utilization_percentage / 100
scheduled_util_hours,
0 sch_reduce_capacity_hours,
fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
actual_hours,
fct.conf_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
scheduled_hours,
0 prior_act_capacity_hours,
0 prior_act_red_capacity_hours,
null prior_missing_hours,
0 prior_total_actual_hours,
0 prior_actual_util_hours,
0 prior_billable_hours,
0 prior_sch_util_hours,
0 prior_actual_hours,
0 prior_scheduled_hours
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_PMV_WT_DIM_TMP wt,
PJI_RM_WTO_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_WORK_TYPES_B worktype,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND wt.id = worktype.work_type_id
AND fct.work_type_id = wt.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND hou.id = imp.org_id
UNION ALL
/* Bug 3515594 */
SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
decode(p_view_by, 'UC', wt.name, '-1') util_category_id,
decode(p_view_by, 'WT', wt.name, '-1') work_type_id,
'-1' job_level_id,
0 capacity_hours,
0 act_reduce_capacity_hours,
null missing_hours,
0 total_actual_hours,
0 actual_util_hours,
0 billable_hours,
0 scheduled_util_hours,
0 sch_reduce_capacity_hours,
0 actual_hours,
0 scheduled_hours,
0 prior_act_capacity_hours,
0 prior_act_red_capacity_hours,
null prior_missing_hours,
0 prior_total_actual_hours,
(fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1))
* worktype.org_utilization_percentage / 100
prior_actual_util_hours,
(fct.bill_hrs_a * worktype.org_utilization_percentage / 100)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_billable_hours,
(fct.conf_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)) *
worktype.org_utilization_percentage / 100
prior_sch_util_hours,
fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_actual_hours,
fct.conf_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_scheduled_hours
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TCMP_DIM_TMP time,
PJI_PMV_WT_DIM_TMP wt,
PJI_RM_WTO_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_WORK_TYPES_B worktype,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND wt.id = worktype.work_type_id
AND fct.work_type_id = wt.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND hou.id = imp.org_id
UNION ALL
SELECT '-1' org_id,
name organization_id,
'-1' time_id,
-1 time_key,
'-1' util_category_id,
'-1' work_type_id,
'-1' job_level_id,
0 capacity_hours,
0 act_reduce_capacity_hours,
null missing_hours,
0 total_actual_hours,
0 actual_util_hours,
0 billable_hours,
0 scheduled_util_hours,
0 sch_reduce_capacity_hours,
0 actual_hours,
0 scheduled_hours,
0 prior_act_capacity_hours,
0 prior_act_red_capacity_hours,
null prior_missing_hours,
0 prior_total_actual_hours,
0 prior_actual_util_hours,
0 prior_billable_hours,
0 prior_sch_util_hours,
0 prior_actual_hours,
0 prior_scheduled_hours
FROM PJI_PMV_ORGZ_DIM_TMP
WHERE name <> '-1'
UNION ALL
SELECT '-1' org_id,
'-1' organization_id,
'-1' time_id,
-1 time_key,
decode(p_view_by, 'UC', name, '-1') util_category_id,
decode(p_view_by, 'WT', name, '-1') work_type_id,
'-1' job_level_id,
-- 0 actual_capacity_hours,
0 capacity_hours,
0 act_reduce_capacity_hours,
null missing_hours,
0 total_actual_hours,
0 actual_util_hours,
0 billable_hours,
0 scheduled_util_hours,
0 sch_reduce_capacity_hours,
0 actual_hours,
0 scheduled_hours,
0 prior_act_capacity_hours,
0 prior_act_red_capacity_hours,
null prior_missing_hours,
0 prior_total_actual_hours,
0 prior_actual_util_hours,
0 prior_billable_hours,
0 prior_sch_util_hours,
0 prior_actual_hours,
0 prior_scheduled_hours
FROM PJI_PMV_WT_DIM_TMP
WHERE name <> '-1'
UNION ALL
SELECT '-1' org_id,
'-1' organization_id,
name time_id,
order_by_id time_key,
'-1' util_category_id,
'-1' work_type_id,
'-1' job_level_id,
0 capacity_hours,
0 act_reduce_capacity_hours,
null missing_hours,
0 total_actual_hours,
0 actual_util_hours,
0 billable_hours,
0 scheduled_util_hours,
0 sch_reduce_capacity_hours,
0 actual_hours,
0 scheduled_hours,
0 prior_act_capacity_hours,
0 prior_act_red_capacity_hours,
null prior_missing_hours,
0 prior_total_actual_hours,
0 prior_actual_util_hours,
0 prior_billable_hours,
0 prior_sch_util_hours,
0 prior_actual_hours,
0 prior_scheduled_hours
FROM PJI_PMV_TIME_DIM_TMP
WHERE name <> '-1'
UNION ALL -- added for current year capacity_hours
SELECT
FACT.org_id,
FACT.organization_id,
FACT.time_id,
FACT.time_key,
decode(p_view_by, 'UC', WT.name, '-1') util_category_id,
decode(p_view_by, 'WT', WT.name, '-1') work_type_id,
'-1' job_level_id,
FACT.capacity_hours,
FACT.reduce_capacity_hrs_a act_reduce_capacity_hours,
FACT.missing_hrs_a missing_hours,
FACT.total_actual_hours total_actual_hours,
0 actual_util_hours,
0 billable_hours,
0 scheduled_util_hours,
FACT.reduce_capacity_hrs_s sch_reduce_capacity_hours,
0 actual_hours,
0 scheduled_hours,
0 prior_act_capacity_hours,
0 prior_act_red_capacity_hours,
null prior_missing_hours,
0 prior_total_actual_hours,
0 prior_actual_util_hours,
0 prior_billable_hours,
0 prior_sch_util_hours,
0 prior_actual_hours,
0 prior_scheduled_hours
FROM
/* Bug 3515594 */
(SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
fct.capacity_hrs / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
capacity_hours,
fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
total_actual_hours,
fct.missing_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
missing_hrs_a,
fct.reduce_capacity_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
reduce_capacity_hrs_a,
fct.reduce_capacity_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
reduce_capacity_hrs_s
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_RM_ORGO_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND hou.id = imp.org_id ) FACT,
(SELECT distinct WT.name
FROM PJI_PMV_WT_DIM_TMP wt ) WT
UNION ALL -- added for prior year capacity_hours
/* Bug 3515594 */
SELECT
FACT.org_id,
FACT.organization_id,
FACT.time_id,
FACT.time_key,
decode(p_view_by, 'UC', WT.name, '-1') util_category_id,
decode(p_view_by, 'WT', WT.name, '-1') work_type_id,
'-1' job_level_id,
0 capacity_hours,
0 act_reduce_capacity_hours,
null missing_hours,
0 total_actual_hours,
0 actual_util_hours,
0 billable_hours,
0 scheduled_util_hours,
0 sch_reduce_capacity_hours,
0 actual_hours,
0 scheduled_hours,
FACT.capacity_hours prior_act_capacity_hours,
FACT.reduce_capacity_hrs_a prior_act_red_capacity_hours,
FACT.missing_hrs_a prior_missing_hours,
FACT.total_actual_hours prior_total_actual_hours,
0 prior_actual_util_hours,
0 prior_billable_hours,
0 prior_sch_util_hours,
0 prior_actual_hours,
0 prior_scheduled_hours
FROM
(SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
fct.capacity_hrs / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
capacity_hours,
fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
total_actual_hours,
fct.missing_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
missing_hrs_a,
fct.reduce_capacity_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
reduce_capacity_hrs_a
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TCMP_DIM_TMP time,
PJI_RM_ORGO_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND hou.id = imp.org_id ) FACT,
(SELECT distinct WT.name
FROM PJI_PMV_WT_DIM_TMP wt ) WT
)
GROUP BY org_id,
organization_id,
time_id,
time_key,
util_category_id,
work_type_id,
job_level_id;
SELECT PJI_REP_U1(org_id,
organization_id,
time_id,
time_key,
util_category_id,
work_type_id,
job_level_id,
sum(capacity_hrs - reduce_capacity_hrs_a),
--null,
sum(missing_hours),
sum(actual_util_hours),
sum(billable_hours),
sum(scheduled_util_hours),
sum(capacity_hrs - reduce_capacity_hrs_s),
0, -- resource_hours
0, -- total_resource_hours,
sum(decode(l_denominator, 'CAPACITY', capacity_hrs - reduce_capacity_hrs_a, actual_hours)),
sum(decode(l_denominator, 'CAPACITY', capacity_hrs - reduce_capacity_hrs_s, actual_hours)),
sum (prior_capacity_hours - prior_reduce_capacity_hrs_a),
sum(prior_missing_hours),
--null,
sum(prior_actual_util_hours),
sum(prior_billable_hours),
sum(prior_sch_util_hours),
sum(prior_capacity_hours - prior_reduce_capacity_hrs_s),
0, -- prior_resource_hours
0, -- prior_total_resource_hours
sum(decode(l_denominator, 'CAPACITY', prior_capacity_hours - prior_reduce_capacity_hrs_a, prior_actual_hours)),
sum(decode(l_denominator, 'CAPACITY', prior_capacity_hours - prior_reduce_capacity_hrs_s, prior_actual_hours)),
null,null,null,null,null,null,
null,null,null,null,null,null,
null,null,null,null,null,null,
null,null,null,null,null,null,null
)
/* Bug 3515594 */
BULK COLLECT INTO l_u1_tbl
FROM (
SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
'-1' util_category_id,
'-1' work_type_id,
job.name job_level_id,
capacity_hrs capacity_hrs,
reduce_capacity_hrs_a reduce_capacity_hrs_a,
missing_hrs_a missing_hours,
total_wtd_org_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
actual_util_hours,
bill_wtd_org_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
billable_hours,
conf_wtd_org_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
scheduled_util_hours,
reduce_capacity_hrs_s reduce_capacity_hrs_s,
total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
actual_hours,
conf_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
scheduled_hours,
0 prior_capacity_hours,
0 prior_reduce_capacity_hrs_a,
0 prior_reduce_capacity_hrs_s,
0 prior_missing_hours,
0 prior_actual_util_hours,
0 prior_billable_hours,
0 prior_sch_util_hours,
0 prior_actual_hours,
0 prior_scheduled_hours
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_PMV_JB_DIM_TMP job,
PJI_RM_JOBO_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.job_id = job.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND hou.id = imp.org_id
UNION ALL
/* Bug 3515594 */
SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
'-1' util_category_id,
'-1' work_type_id,
job.name job_level_id,
0 capacity_hours,
0 reduce_capacity_hrs_a,
0 reduce_capacity_hrs_s,
0 missing_hours,
0 actual_util_hours,
0 billable_hours,
0 scheduled_util_hours,
0 actual_hours,
0 scheduled_hours,
0 prior_capacity_hours,
reduce_capacity_hrs_a prior_reduce_capacity_hrs_a,
missing_hrs_a prior_missing_hours,
total_wtd_org_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_actual_util_hours,
bill_wtd_org_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_billable_hours,
conf_wtd_org_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_sch_util_hours,
reduce_capacity_hrs_s prior_reduce_capacity_hrs_s,
total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_actual_hours,
conf_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_scheduled_hours
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TCMP_DIM_TMP time,
PJI_PMV_JB_DIM_TMP job,
PJI_RM_JOBO_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.job_id = job.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND hou.id = imp.org_id
UNION ALL
SELECT '-1' org_id,
name organization_id,
'-1' time_id,
-1 time_key,
'-1' util_category_id,
'-1' work_type_id,
'-1' job_level_id,
0 capacity_hours,
0 reduce_capacity_hrs_a,
0 reduce_capacity_hrs_s,
null missing_hours,
0 actual_util_hours,
0 billable_hours,
0 scheduled_util_hours,
0 actual_hours,
0 scheduled_hours,
0 prior_capacity_hours,
0 prior_reduce_capacity_hrs_a,
0 prior_reduce_capacity_hrs_s,
null prior_missing_hours,
0 prior_actual_util_hours,
0 prior_billable_hours,
0 prior_sch_util_hours,
0 prior_actual_hours,
0 prior_scheduled_hours
FROM PJI_PMV_ORGZ_DIM_TMP
WHERE name <> '-1'
UNION ALL
SELECT '-1' org_id,
'-1' organization_id,
'-1' time_id,
-1 time_key,
'-1' util_category_id,
'-1' work_type_id,
name job_level_id,
0 capacity_hours,
0 reduce_capacity_hrs_a,
0 reduce_capacity_hrs_s,
null missing_hours,
0 actual_util_hours,
0 billable_hours,
0 scheduled_util_hours,
0 actual_hours,
0 scheduled_hours,
0 prior_capacity_hours,
0 prior_reduce_capacity_hrs_a,
0 prior_reduce_capacity_hrs_s,
null prior_missing_hours,
0 prior_actual_util_hours,
0 prior_billable_hours,
0 prior_sch_util_hours,
0 prior_actual_hours,
0 prior_scheduled_hours
FROM PJI_PMV_JB_DIM_TMP
WHERE name <> '-1'
UNION ALL
SELECT '-1' org_id,
'-1' organization_id,
name time_id,
order_by_id time_key,
'-1' util_category_id,
'-1' work_type_id,
'-1' job_level_id,
0 capacity_hours,
0 reduce_capacity_hrs_a,
0 reduce_capacity_hrs_s,
null missing_hours,
0 actual_util_hours,
0 billable_hours,
0 scheduled_util_hours,
0 actual_hours,
0 scheduled_hours,
0 prior_capacity_hours,
0 prior_reduce_capacity_hrs_a,
0 prior_reduce_capacity_hrs_s,
null prior_missing_hours,
0 prior_actual_util_hours,
0 prior_billable_hours,
0 prior_sch_util_hours,
0 prior_actual_hours,
0 prior_scheduled_hours
FROM PJI_PMV_TIME_DIM_TMP
WHERE name <> '-1'
)
GROUP BY org_id,
organization_id,
time_id,
time_key,
util_category_id,
work_type_id,
job_level_id;
SELECT PJI_REP_U1(org_id,
organization_id,
time_id,
time_key,
util_category_id,
work_type_id,
job_level_id,
sum(capacity_hours-act_reduce_capacity_hours),
sum(decode (p_view_by, 'UC', null, 'WT',null, missing_hours)),
sum(actual_util_hours),
sum(billable_hours),
sum(scheduled_util_hours),
sum(capacity_hours-sch_reduce_capacity_hours),
0, -- resource_hours
0, -- total_resource_hours,
sum(decode(l_denominator, 'CAPACITY', capacity_hours-act_reduce_capacity_hours, total_actual_hours)),
sum(decode(l_denominator, 'CAPACITY', capacity_hours-sch_reduce_capacity_hours, total_actual_hours)),
sum(prior_act_capacity_hours-prior_act_red_capacity_hours),
sum(decode (p_view_by, 'UC', null, 'WT',null, prior_missing_hours)),
sum(prior_actual_util_hours),
sum(prior_billable_hours),
sum(prior_sch_util_hours),
sum(prior_act_capacity_hours-prior_act_red_capacity_hours),
0, -- prior_resource_hours
0, -- prior_total_resource_hours
sum(decode(l_denominator, 'CAPACITY', prior_act_capacity_hours-prior_act_red_capacity_hours,
prior_total_actual_hours)),
sum(decode(l_denominator, 'CAPACITY', prior_act_capacity_hours-prior_act_red_capacity_hours,
prior_total_actual_hours)),
null,null,null,null,null,null,
null,null,null,null,null,null,
null,null,null,null,null,null,
null,null,null,null,null,null,null
)
/* Bug 3515594 */
BULK COLLECT INTO l_u1_tbl
FROM (
SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
decode(p_view_by, 'UC', wt.name, '-1') util_category_id,
decode(p_view_by, 'WT', wt.name, '-1') work_type_id,
job.name job_level_id,
0 capacity_hours,
0 act_reduce_capacity_hours,
null missing_hours,
0 total_actual_hours,
(fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)) *
worktype.org_utilization_percentage / 100
actual_util_hours,
(fct.bill_hrs_a * worktype.org_utilization_percentage / 100)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
billable_hours,
(fct.conf_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)) *
worktype.org_utilization_percentage / 100
scheduled_util_hours,
0 sch_reduce_capacity_hours,
fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
actual_hours,
fct.conf_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
scheduled_hours,
0 prior_act_capacity_hours,
0 prior_act_red_capacity_hours,
null prior_missing_hours,
0 prior_total_actual_hours,
0 prior_actual_util_hours,
0 prior_billable_hours,
0 prior_sch_util_hours,
-- 0 prior_sch_capacity_hours,
0 prior_actual_hours,
0 prior_scheduled_hours
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_PMV_WT_DIM_TMP wt,
PJI_RM_RES_WT_F fct,
PJI_PMV_ORG_DIM_TMP hou,
PJI_PMV_JB_DIM_TMP job,
PA_WORK_TYPES_B worktype,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND wt.id = worktype.work_type_id
AND fct.work_type_id = wt.id
AND fct.job_id = job.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND hou.id = imp.org_id
UNION ALL
/* Bug 3515594 */
SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
decode(p_view_by, 'UC', wt.name, '-1') util_category_id,
decode(p_view_by, 'WT', wt.name, '-1') work_type_id,
job.name job_level_id,
0 capacity_hours,
0 act_reduce_capacity_hours,
null missing_hours,
0 total_actual_hours,
0 actual_util_hours,
0 billable_hours,
0 scheduled_util_hours,
0 sch_reduce_capacity_hours,
0 actual_hours,
0 scheduled_hours,
0 prior_act_capacity_hours,
0 prior_act_red_capacity_hours,
null prior_missing_hours,
0 prior_total_actual_hours,
(fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)) *
worktype.org_utilization_percentage / 100
prior_actual_util_hours,
(fct.bill_hrs_a * worktype.org_utilization_percentage / 100)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_billable_hours,
(fct.conf_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)) *
worktype.org_utilization_percentage / 100
prior_sch_util_hours,
fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_actual_hours,
fct.conf_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_scheduled_hours
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TCMP_DIM_TMP time,
PJI_PMV_WT_DIM_TMP wt,
PJI_RM_RES_WT_F fct,
PJI_PMV_ORG_DIM_TMP hou,
PJI_PMV_JB_DIM_TMP job,
PA_WORK_TYPES_B worktype,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND wt.id = worktype.work_type_id
AND fct.work_type_id = wt.id
AND fct.job_id = job.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND hou.id = imp.org_id
UNION ALL
SELECT '-1' org_id,
name organization_id,
'-1' time_id,
-1 time_key,
'-1' util_category_id,
'-1' work_type_id,
'-1' job_level_id,
0 capacity_hours,
0 act_reduce_capacity_hours,
null missing_hours,
0 total_actual_hours,
0 actual_util_hours,
0 billable_hours,
0 scheduled_util_hours,
0 sch_reduce_capacity_hours,
0 actual_hours,
0 scheduled_hours,
0 prior_act_capacity_hours,
0 prior_act_red_capacity_hours,
null prior_missing_hours,
0 prior_total_actual_hours,
0 prior_actual_util_hours,
0 prior_billable_hours,
0 prior_sch_util_hours,
0 prior_actual_hours,
0 prior_scheduled_hours
FROM PJI_PMV_ORGZ_DIM_TMP
WHERE name <> '-1'
UNION ALL
SELECT '-1' org_id,
'-1' organization_id,
'-1' time_id,
-1 time_key,
decode(p_view_by, 'UC', name, '-1') util_category_id,
decode(p_view_by, 'WT', name, '-1') work_type_id,
'-1' job_level_id,
0 capacity_hours,
0 act_reduce_capacity_hours,
null missing_hours,
0 total_actual_hours,
0 actual_util_hours,
0 billable_hours,
0 scheduled_util_hours,
0 sch_reduce_capacity_hours,
0 actual_hours,
0 scheduled_hours,
0 prior_act_capacity_hours,
0 prior_act_red_capacity_hours,
null prior_missing_hours,
0 prior_total_actual_hours,
0 prior_actual_util_hours,
0 prior_billable_hours,
0 prior_sch_util_hours,
0 prior_actual_hours,
0 prior_scheduled_hours
FROM PJI_PMV_WT_DIM_TMP
WHERE name <> '-1'
UNION ALL
SELECT '-1' org_id,
'-1' organization_id,
'-1' time_id,
-1 time_key,
'-1' util_category_id,
'-1' work_typd_id,
name job_level_id,
0 capacity_hours,
0 act_reduce_capacity_hours,
null missing_hours,
0 total_actual_hours,
0 actual_util_hours,
0 billable_hours,
0 scheduled_util_hours,
0 sch_reduce_capacity_hours,
0 actual_hours,
0 scheduled_hours,
0 prior_act_capacity_hours,
0 prior_act_red_capacity_hours,
null prior_missing_hours,
0 prior_total_actual_hours,
0 prior_actual_util_hours,
0 prior_billable_hours,
0 prior_sch_util_hours,
0 prior_actual_hours,
0 prior_scheduled_hours
FROM PJI_PMV_JB_DIM_TMP
WHERE name <> '-1'
UNION ALL
SELECT '-1' org_id,
'-1' organization_id,
name time_id,
order_by_id time_key,
'-1' util_category_id,
'-1' work_type_id,
'-1' job_level_id,
0 capacity_hours,
0 act_reduce_capacity_hours,
null missing_hours,
0 total_actual_hours,
0 actual_util_hours,
0 billable_hours,
0 scheduled_util_hours,
0 sch_reduce_capacity_hours,
0 actual_hours,
0 scheduled_hours,
0 prior_act_capacity_hours,
0 prior_act_red_capacity_hours,
null prior_missing_hours,
0 prior_total_actual_hours,
0 prior_actual_util_hours,
0 prior_billable_hours,
0 prior_sch_util_hours,
0 prior_actual_hours,
0 prior_scheduled_hours
FROM PJI_PMV_TIME_DIM_TMP
WHERE name <> '-1'
UNION ALL -- added for current year capacity_hours
/* Bug 3515594 */
SELECT
FACT.org_id,
FACT.organization_id,
FACT.time_id,
FACT.time_key,
decode(p_view_by, 'UC', WT.name, '-1') util_category_id,
decode(p_view_by, 'WT', WT.name, '-1') work_type_id,
FACT.job_level_id job_level_id,
FACT.capacity_hours,
FACT.reduce_capacity_hrs_a act_reduce_capacity_hours,
FACT.missing_hrs_a missing_hours,
FACT.total_actual_hours total_actual_hours,
0 actual_util_hours,
0 billable_hours,
0 scheduled_util_hours,
FACT.reduce_capacity_hrs_s sch_reduce_capacity_hours,
0 actual_hours,
0 scheduled_hours,
0 prior_act_capacity_hours,
0 prior_act_red_capacity_hours,
null prior_missing_hours,
0 prior_total_actual_hours,
0 prior_actual_util_hours,
0 prior_billable_hours,
0 prior_sch_util_hours,
0 prior_actual_hours,
0 prior_scheduled_hours
FROM
(SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
job.name job_level_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
capacity_hrs / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
capacity_hours,
total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
total_actual_hours,
missing_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
missing_hrs_a,
reduce_capacity_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
reduce_capacity_hrs_a,
reduce_capacity_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
reduce_capacity_hrs_s
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_PMV_JB_DIM_TMP job,
PJI_RM_JOB_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND time.id is not null
AND fct.job_id = job.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND hou.id = imp.org_id ) FACT,
(SELECT distinct WT.name
FROM PJI_PMV_WT_DIM_TMP wt ) WT
UNION ALL -- added for prior year capacity_hours
/* Bug 3515594 */
SELECT
FACT.org_id,
FACT.organization_id,
FACT.time_id,
FACT.time_key,
decode(p_view_by, 'UC', WT.name, '-1') util_category_id,
decode(p_view_by, 'WT', WT.name, '-1') work_type_id,
FACT.job_level_id job_level_id,
0 capacity_hours,
0 act_reduce_capacity_hours,
null missing_hours,
0 total_actual_hours,
0 actual_util_hours,
0 billable_hours,
0 scheduled_util_hours,
0 sch_reduce_capacity_hours,
0 actual_hours,
0 scheduled_hours,
FACT.capacity_hours prior_act_capacity_hours,
FACT.prior_reduce_capacity_hrs_a prior_act_red_capacity_hours,
FACT.missing_hrs_a prior_missing_hours,
FACT.total_actual_hours prior_total_actual_hours,
0 prior_actual_util_hours,
0 prior_billable_hours,
0 prior_sch_util_hours,
0 prior_actual_hours,
0 prior_scheduled_hours
FROM
(SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
job.name job_level_id,
capacity_hrs / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
capacity_hours,
total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
total_actual_hours,
missing_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
missing_hrs_a,
reduce_capacity_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_reduce_capacity_hrs_a
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TCMP_DIM_TMP time,
PJI_PMV_JB_DIM_TMP job,
PJI_RM_JOB_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND fct.job_id = job.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND hou.id = imp.org_id ) FACT,
(SELECT distinct WT.name
FROM PJI_PMV_WT_DIM_TMP wt ) WT
)
GROUP BY org_id,
organization_id,
time_id,
time_key,
util_category_id,
work_type_id,
job_level_id;
l_u1_tbl.DELETE(l_Top_Org_Index);
SELECT fnd_profile.value('PA_ORG_UTIL_DEF_CALC_METHOD')
INTO l_denominator
from dual;
select report_labor_units
into l_labor_unit
from pji_system_settings;
SELECT PJI_REP_U2(org_id,
organization_id,
time_id,
time_key,
sum(actual_capacity_hours),
sum(missing_hours),
sum(actual_utilization_hours),
sum(billable_hours),
sum(nonbillable_hours),
sum(training_hours),
sum(scheduled_capacity_hours),
sum(conf_scheduled_hours),
sum(prov_scheduled_hours),
sum(scheduled_utilization_hours),
sum(decode(l_denominator, 'CAPACITY', actual_capacity_hours, actual_hours)),
sum(decode(l_denominator, 'CAPACITY', scheduled_capacity_hours, actual_hours)),
sum(prior_actual_util_hours),
sum(prior_actual_capacity_hours),
sum(decode(l_denominator, 'CAPACITY', prior_actual_capacity_hours, prior_actual_hours)),
null,null,null,null,null,null)
BULK COLLECT INTO l_u2_tbl
/* Bug 3515594 */
FROM (
SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.order_by_id, -1) time_key,
(capacity_hrs - reduce_capacity_hrs_a) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
actual_capacity_hours,
missing_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
missing_hours,
total_wtd_org_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
actual_utilization_hours,
bill_wtd_org_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
billable_hours,
(total_wtd_org_hrs_a - bill_wtd_org_hrs_a) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
nonbillable_hours,
training_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
training_hours,
(capacity_hrs - reduce_capacity_hrs_s) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
scheduled_capacity_hours,
conf_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
conf_scheduled_hours,
prov_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prov_scheduled_hours,
conf_wtd_org_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
scheduled_utilization_hours,
total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
actual_hours,
0 prior_actual_util_hours,
0 prior_actual_capacity_hours,
0 prior_actual_hours
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_RM_ORGO_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND hou.id = imp.org_id
UNION ALL
/* Bug 3515594 */
SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.order_by_id, -1) time_key,
0 actual_capacity_hours,
0 missing_hours,
0 actual_utilization_hours,
0 billable_hours,
0 nonbillable_hours,
0 training_hours,
0 scheduled_capacity_hours,
0 conf_scheduled_hours,
0 prov_scheduled_hours,
0 scheduled_utilization_hours,
0 actual_hours,
total_wtd_org_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_actual_util_hours,
(capacity_hrs - reduce_capacity_hrs_a) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_actual_capacity_hours,
total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_actual_hours
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_RM_ORGO_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.prior_id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.prior_id is not null
AND hou.id = imp.org_id
UNION ALL
SELECT '-1' org_id,
'-1' organization_id,
name time_id,
order_by_id time_key,
0 actual_capacity_hours,
0 missing_hours,
0 actual_utilization_hours,
0 billable_hours,
0 nonbillable_hours,
0 training_hours,
0 scheduled_capacity_hours,
0 conf_scheduled_hours,
0 prov_scheduled_hours,
0 scheduled_utilization_hours,
0 actual_hours,
0 prior_actual_util_hours,
0 prior_actual_capacity_hours,
0 prior_actual_hours
FROM PJI_PMV_TIME_DIM_TMP
WHERE name <> '-1'
)
GROUP BY org_id,
organization_id,
time_key,
time_id ORDER BY TIME_KEY ASC;
SELECT fnd_profile.value('PA_ORG_UTIL_DEF_CALC_METHOD')
INTO l_denominator
from dual;
select report_labor_units
into l_labor_unit
from pji_system_settings;
SELECT PJI_REP_U3(org_id,
organization_id,
time_id,
time_key,
util_category_id,
work_type_id,
job_level_id,
sum(actual_hours),
sum(capacity_hours),
sum(missing_hours),
sum(utilization_hours),
sum(billable_hours),
sum(nonbillable_hours),
sum(training_hours),
sum(decode(l_denominator, 'CAPACITY', capacity_hours, actual_hours)),
sum(prior_actual_hours),
sum(prior_capacity_hours),
sum(prior_utilization_hours),
sum(prior_billable_hours),
sum(prior_nonbillable_hours),
sum(prior_training_hours),
sum(decode(l_denominator, 'CAPACITY', prior_capacity_hours, prior_actual_hours)),
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_u3_tbl
/* Bug 3515594 */
FROM (
SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
'-1' util_category_id,
'-1' work_type_id,
'-1' job_level_id,
total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
actual_hours,
(capacity_hrs - reduce_capacity_hrs_a) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
capacity_hours,
missing_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
missing_hours,
total_wtd_org_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
utilization_hours,
bill_wtd_org_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
billable_hours,
(total_wtd_org_hrs_a - bill_wtd_org_hrs_a) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
nonbillable_hours,
training_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
training_hours,
0 prior_actual_hours,
0 prior_capacity_hours,
0 prior_utilization_hours,
0 prior_billable_hours,
0 prior_nonbillable_hours,
0 prior_training_hours
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_RM_ORGO_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND hou.id = imp.org_id
UNION ALL
/* Bug 3515594 */
SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
'-1' util_category_id,
'-1' work_type_id,
'-1' job_level_id,
0 actual_hours,
0 capacity_hours,
0 missing_hours,
0 utilization_hours,
0 billable_hours,
0 nonbillable_hours,
0 training_hours,
total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_actual_hours,
(capacity_hrs - reduce_capacity_hrs_a) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_capacity_hours,
total_wtd_org_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_utilization_hours,
bill_wtd_org_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_billable_hours,
(total_wtd_org_hrs_a - bill_wtd_org_hrs_a) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_nonbillable_hours,
training_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_training_hours
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_RM_ORGO_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.prior_id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.prior_id is not null
AND hou.id = imp.org_id
UNION ALL
SELECT '-1' org_id,
name organization_id,
'-1' time_id,
-1 time_key,
'-1' util_category_id,
'-1' work_type_id,
'-1' job_level_id,
0 actual_hours,
0 capacity_hours,
0 missing_hours,
0 utilization_hours,
0 billable_hours,
0 nonbillable_hours,
0 training_hours,
0 prior_actual_hours,
0 prior_capacity_hours,
0 prior_utilization_hours,
0 prior_billable_hours,
0 prior_nonbillable_hours,
0 prior_training_hours
FROM PJI_PMV_ORGZ_DIM_TMP
WHERE name <> '-1'
UNION ALL
SELECT '-1' org_id,
'-1' organization_id,
name time_id,
order_by_id time_key,
'-1' util_category_id,
'-1' work_type_id,
'-1' job_level_id,
0 actual_hours,
0 capacity_hours,
0 missing_hours,
0 utilization_hours,
0 billable_hours,
0 nonbillable_hours,
0 training_hours,
0 prior_actual_hours,
0 prior_capacity_hours,
0 prior_utilization_hours,
0 prior_billable_hours,
0 prior_nonbillable_hours,
0 prior_training_hours
FROM PJI_PMV_TIME_DIM_TMP
WHERE name <> '-1'
)
GROUP BY org_id,
organization_id,
time_id,
time_key,
util_category_id,
work_type_id,
job_level_id;
SELECT PJI_REP_U3(org_id,
organization_id,
time_id,
time_key,
util_category_id,
work_type_id,
job_level_id,
sum(actual_hours),
sum(decode (p_view_by, 'UC', null, 'WT',null, capacity_hours-act_reduce_capacity_hours)),
sum(decode (p_view_by, 'UC', null, 'WT',null, missing_hours)),
sum(utilization_hours),
sum(billable_hours),
sum(nonbillable_hours),
sum(training_hours),
sum(decode(l_denominator, 'CAPACITY', capacity_hours-act_reduce_capacity_hours, total_actual_hours)),
sum(prior_actual_hours),
sum(decode (p_view_by, 'UC', null, 'WT',null, prior_act_capacity_hours-prior_act_red_capacity_hours)),
sum(prior_utilization_hours),
sum(prior_billable_hours),
sum(prior_nonbillable_hours),
sum(prior_training_hours),
sum(decode(l_denominator, 'CAPACITY', prior_act_capacity_hours-prior_act_red_capacity_hours,
prior_total_actual_hours)),
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_u3_tbl
/* Bug 3515594 */
FROM (
SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
decode(p_view_by, 'UC', wt.name, '-1') util_category_id,
decode(p_view_by, 'WT', wt.name, '-1') work_type_id,
'-1' job_level_id,
total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
actual_hours,
0 capacity_hours,
0 act_reduce_capacity_hours,
null missing_hours,
0 total_actual_hours,
fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) *
worktype.org_utilization_percentage / 100
utilization_hours,
(fct.bill_hrs_a * worktype.org_utilization_percentage / 100)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
billable_hours,
((fct.total_hrs_a - fct.bill_hrs_a) * worktype.org_utilization_percentage / 100)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
nonbillable_hours,
(CASE WHEN worktype.training_flag = 'Y' THEN
fct.total_hrs_a
ELSE 0 END) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
training_hours,
0 prior_actual_hours,
0 prior_act_capacity_hours,
0 prior_act_red_capacity_hours,
0 prior_total_actual_hours,
0 prior_utilization_hours,
0 prior_billable_hours,
0 prior_nonbillable_hours,
0 prior_training_hours
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_PMV_WT_DIM_TMP wt,
PJI_RM_WTO_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_WORK_TYPES_B worktype,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND wt.id = worktype.work_type_id
AND fct.work_type_id = wt.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND hou.id = imp.org_id
UNION ALL
/* Bug 3515594 */
SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
decode(p_view_by, 'UC', wt.name, '-1') util_category_id,
decode(p_view_by, 'WT', wt.name, '-1') work_type_id,
'-1' job_level_id,
0 actual_hours,
0 capacity_hours,
0 act_reduce_capacity_hours,
null missing_hours,
0 total_actual_hours,
0 utilization_hours,
0 billable_hours,
0 nonbillable_hours,
0 training_hours,
total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_actual_hours,
0 prior_act_capacity_hours,
0 prior_act_red_capacity_hours,
0 prior_total_actual_hours,
(fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)) *
worktype.org_utilization_percentage / 100
prior_utilization_hours,
(fct.bill_hrs_a * worktype.org_utilization_percentage / 100)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_billable_hours,
((fct.total_hrs_a-fct.bill_hrs_a) * worktype.org_utilization_percentage / 100)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_nonbillable_hours,
(CASE WHEN worktype.training_flag = 'Y' THEN
fct.total_hrs_a
ELSE 0 END) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_training_hours
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_PMV_WT_DIM_TMP wt,
PJI_RM_WTO_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_WORK_TYPES_B worktype,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND wt.id = worktype.work_type_id
AND fct.work_type_id = wt.id
AND fct.time_id = time.prior_id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.prior_id is not null
AND hou.id = imp.org_id
UNION ALL
SELECT '-1' org_id,
name organization_id,
'-1' time_id,
-1 time_key,
'-1' util_category_id,
'-1' work_type_id,
'-1' job_level_id,
0 actual_hours,
0 capacity_hours,
0 act_reduce_capacity_hours,
null missing_hours,
0 total_actual_hours,
0 utilization_hours,
0 billable_hours,
0 nonbillable_hours,
0 training_hours,
0 prior_actual_hours,
0 prior_act_capacity_hours,
0 prior_act_red_capacity_hours,
0 prior_total_actual_hours,
0 prior_utilization_hours,
0 prior_billable_hours,
0 prior_nonbillable_hours,
0 prior_training_hours
FROM PJI_PMV_ORGZ_DIM_TMP
WHERE name <> '-1'
UNION ALL
SELECT '-1' org_id,
'-1' organization_id,
'-1' time_id,
-1 time_key,
decode(p_view_by, 'UC', name, '-1') util_category_id,
decode(p_view_by, 'WT', name, '-1') work_type_id,
'-1' job_level_id,
0 actual_hours,
0 capacity_hours,
0 act_reduce_capacity_hours,
null missing_hours,
0 total_actual_hours,
0 utilization_hours,
0 billable_hours,
0 nonbillable_hours,
0 training_hours,
0 prior_actual_hours,
0 prior_act_capacity_hours,
0 prior_act_red_capacity_hours,
0 prior_total_actual_hours,
0 prior_utilization_hours,
0 prior_billable_hours,
0 prior_nonbillable_hours,
0 prior_training_hours
FROM PJI_PMV_WT_DIM_TMP
WHERE name <> '-1'
UNION ALL
SELECT '-1' org_id,
'-1' organization_id,
name time_id,
order_by_id time_key,
'-1' util_category_id,
'-1' work_type_id,
'-1' job_level_id,
0 actual_hours,
0 capacity_hours,
0 act_reduce_capacity_hours,
null missing_hours,
0 total_actual_hours,
0 utilization_hours,
0 billable_hours,
0 nonbillable_hours,
0 training_hours,
0 prior_actual_hours,
0 prior_act_capacity_hours,
0 prior_act_red_capacity_hours,
0 prior_total_actual_hours,
0 prior_utilization_hours,
0 prior_billable_hours,
0 prior_nonbillable_hours,
0 prior_training_hours
FROM PJI_PMV_TIME_DIM_TMP
WHERE name <> '-1'
UNION ALL -- added for current year capacity_hours
SELECT
FACT.org_id,
FACT.organization_id,
FACT.time_id,
FACT.time_key,
decode(p_view_by, 'UC', WT.name, '-1') util_category_id,
decode(p_view_by, 'WT', WT.name, '-1') work_type_id,
'-1' job_level_id,
0 actual_hours,
FACT.capacity_hours,
FACT.reduce_capacity_hrs_a act_reduce_capacity_hours,
FACT.missing_hrs_a missing_hours,
FACT.total_actual_hours total_actual_hours,
0 utilization_hours,
0 billable_hours,
0 nonbillable_hours,
0 training_hours,
0 prior_actual_hours,
0 prior_act_capacity_hours,
0 prior_act_red_capacity_hours,
0 prior_total_actual_hours,
0 prior_utilization_hours,
0 prior_billable_hours,
0 prior_nonbillable_hours,
0 prior_training_hours
FROM
/* Bug 3515594 */
(SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
fct.capacity_hrs / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
capacity_hours,
fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
total_actual_hours,
fct.missing_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
missing_hrs_a,
fct.reduce_capacity_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
reduce_capacity_hrs_a
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_RM_ORGO_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND hou.id = imp.org_id ) FACT,
(SELECT distinct WT.name
FROM PJI_PMV_WT_DIM_TMP wt ) WT
UNION ALL -- added for prior year capacity_hours
SELECT
FACT.org_id,
FACT.organization_id,
FACT.time_id,
FACT.time_key,
decode(p_view_by, 'UC', WT.name, '-1') util_category_id,
decode(p_view_by, 'WT', WT.name, '-1') work_type_id,
'-1' job_level_id,
0 actual_hours,
0 capacity_hours,
0 act_reduce_capacity_hours,
null missing_hours,
0 total_actual_hours,
0 utilization_hours,
0 billable_hours,
0 nonbillable_hours,
0 training_hours,
0 prior_actual_hours,
FACT.capacity_hours prior_act_capacity_hours,
FACT.reduce_capacity_hrs_a prior_act_red_capacity_hours,
FACT.total_actual_hours prior_total_actual_hours,
0 prior_utilization_hours,
0 prior_billable_hours,
0 prior_nonbillable_hours,
0 prior_training_hours
FROM
/* Bug 3515594 */
(SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
fct.capacity_hrs / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
capacity_hours,
fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
total_actual_hours,
fct.reduce_capacity_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
reduce_capacity_hrs_a
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_RM_ORGO_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.prior_id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.prior_id is not null
AND hou.id = imp.org_id ) FACT,
(SELECT distinct WT.name
FROM PJI_PMV_WT_DIM_TMP wt ) WT
)
GROUP BY org_id,
organization_id,
time_id,
time_key,
util_category_id,
work_type_id,
job_level_id;
SELECT PJI_REP_U3(org_id,
organization_id,
time_id,
time_key,
util_category_id,
work_type_id,
job_level_id,
sum(actual_hours),
sum(capacity_hours - reduce_capacity_hrs_a),
sum(missing_hours),
sum(utilization_hours),
sum(billable_hours),
sum(nonbillable_hours),
sum(training_hours),
sum(decode(l_denominator, 'CAPACITY', capacity_hours - reduce_capacity_hrs_a, actual_hours)),
sum(prior_actual_hours),
sum(prior_capacity_hours - prior_reduce_capacity_hrs_a),
sum(prior_utilization_hours),
sum(prior_billable_hours),
sum(prior_nonbillable_hours),
sum(prior_training_hours),
sum(decode(l_denominator, 'CAPACITY', prior_capacity_hours - prior_reduce_capacity_hrs_a, prior_actual_hours)),
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_u3_tbl
/* Bug 3515594 */
FROM (
SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
'-1' util_category_id,
'-1' work_type_id,
job.name job_level_id,
total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
actual_hours,
capacity_hrs capacity_hours,
reduce_capacity_hrs_a reduce_capacity_hrs_a,
missing_hrs_a missing_hours,
total_wtd_org_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
utilization_hours,
bill_wtd_org_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
billable_hours,
(total_wtd_org_hrs_a - bill_wtd_org_hrs_a) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
nonbillable_hours,
training_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
training_hours,
0 prior_actual_hours,
0 prior_capacity_hours,
0 prior_reduce_capacity_hrs_a,
0 prior_utilization_hours,
0 prior_billable_hours,
0 prior_nonbillable_hours,
0 prior_training_hours
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_PMV_JB_DIM_TMP job,
PJI_RM_JOBO_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.job_id = job.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND hou.id = imp.org_id
UNION ALL
/* Bug 3515594 */
SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
'-1' util_category_id,
'-1' work_type_id,
job.name job_level_id,
0 actual_hours,
0 capacity_hours,
0 reduce_capacity_hrs_a,
null missing_hours,
0 utilization_hours,
0 billable_hours,
0 nonbillable_hours,
0 training_hours,
total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_actual_hours,
capacity_hrs prior_capacity_hours,
reduce_capacity_hrs_a prior_reduce_capacity_hrs_a,
total_wtd_org_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_utilization_hours,
bill_wtd_org_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_billable_hours,
(total_wtd_org_hrs_a - bill_wtd_org_hrs_a) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_nonbillable_hours,
training_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_training_hours
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_PMV_JB_DIM_TMP job,
PJI_RM_JOBO_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.job_id = job.id
AND fct.time_id = time.prior_id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.prior_id is not null
AND hou.id = imp.org_id
UNION ALL
SELECT '-1' org_id,
name organization_id,
'-1' time_id,
-1 time_key,
'-1' util_category_id,
'-1' work_type_id,
'-1' job_level_id,
0 actual_hours,
0 capacity_hours,
0 reduce_capacity_hrs_a,
null missing_hours,
0 utilization_hours,
0 billable_hours,
0 nonbillable_hours,
0 training_hours,
0 prior_actual_hours,
0 prior_capacity_hours,
0 prior_reduce_capacity_hrs_a,
0 prior_utilization_hours,
0 prior_billable_hours,
0 prior_nonbillable_hours,
0 prior_training_hours
FROM PJI_PMV_ORGZ_DIM_TMP
WHERE name <> '-1'
UNION ALL
SELECT '-1' org_id,
'-1' organization_id,
'-1' time_id,
-1 time_key,
'-1' util_category_id,
'-1' work_type_id,
name job_level_id,
0 actual_hours,
0 capacity_hours,
0 reduce_capacity_hrs_a,
null missing_hours,
0 utilization_hours,
0 billable_hours,
0 nonbillable_hours,
0 training_hours,
0 prior_actual_hours,
0 prior_capacity_hours,
0 prior_reduce_capacity_hrs_a,
0 prior_utilization_hours,
0 prior_billable_hours,
0 prior_nonbillable_hours,
0 prior_training_hours
FROM PJI_PMV_JB_DIM_TMP
WHERE name <> '-1'
UNION ALL
SELECT '-1' org_id,
'-1' organization_id,
name time_id,
order_by_id time_key,
'-1' util_category_id,
'-1' work_type_id,
'-1' job_level_id,
0 actual_hours,
0 capacity_hours,
0 reduce_capacity_hrs_a,
null missing_hours,
0 utilization_hours,
0 billable_hours,
0 nonbillable_hours,
0 training_hours,
0 prior_actual_hours,
0 prior_capacity_hours,
0 prior_reduce_capacity_hrs_a,
0 prior_utilization_hours,
0 prior_billable_hours,
0 prior_nonbillable_hours,
0 prior_training_hours
FROM PJI_PMV_TIME_DIM_TMP
WHERE name <> '-1'
)
GROUP BY org_id,
organization_id,
time_id,
time_key,
util_category_id,
work_type_id,
job_level_id;
SELECT PJI_REP_U3(org_id,
organization_id,
time_id,
time_key,
util_category_id,
work_type_id,
job_level_id,
sum(actual_hours),
sum(decode (p_view_by, 'UC', null, 'WT',null, capacity_hours-act_reduce_capacity_hours)),
sum(decode (p_view_by, 'UC', null, 'WT',null, missing_hours)),
sum(utilization_hours),
sum(billable_hours),
sum(nonbillable_hours),
sum(training_hours),
sum(decode(l_denominator, 'CAPACITY', capacity_hours-act_reduce_capacity_hours, total_actual_hours)),
sum(prior_actual_hours),
sum(decode (p_view_by, 'UC', null, 'WT',null, prior_act_capacity_hours-prior_act_red_capacity_hours)),
sum(prior_utilization_hours),
sum(prior_billable_hours),
sum(prior_nonbillable_hours),
sum(prior_training_hours),
sum(decode(l_denominator, 'CAPACITY', prior_act_capacity_hours-prior_act_red_capacity_hours,
prior_total_actual_hours)),
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_u3_tbl
/* Bug 3515594 */
FROM (
SELECT /*+ ORDERED */
hou.name org_id,
decode(p_view_by, 'OG', horg.name, -1) organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
decode(p_view_by, 'UC', wt.name, '-1') util_category_id,
decode(p_view_by, 'WT', wt.name, '-1') work_type_id,
decode(p_view_by, 'JL', job.name , '-1') job_level_id,
total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
actual_hours,
0 capacity_hours,
0 act_reduce_capacity_hours,
null missing_hours,
0 total_actual_hours,
(fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)) *
worktype.org_utilization_percentage / 100
utilization_hours,
(fct.bill_hrs_a * worktype.org_utilization_percentage / 100)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
billable_hours,
((fct.total_hrs_a - fct.bill_hrs_a) * worktype.org_utilization_percentage / 100)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
nonbillable_hours,
(CASE WHEN worktype.training_flag = 'Y' THEN
fct.total_hrs_a
ELSE 0 END) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
training_hours,
0 prior_actual_hours,
0 prior_act_capacity_hours,
0 prior_act_red_capacity_hours,
0 prior_total_actual_hours,
0 prior_utilization_hours,
0 prior_billable_hours,
0 prior_nonbillable_hours,
0 prior_training_hours
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_PMV_WT_DIM_TMP wt,
PJI_RM_RES_WT_F fct,
PJI_PMV_ORG_DIM_TMP hou,
PJI_PMV_JB_DIM_TMP job,
PA_WORK_TYPES_B worktype,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND wt.id = worktype.work_type_id
AND fct.work_type_id = wt.id
AND fct.job_id = job.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND hou.id = imp.org_id
UNION ALL
/* Bug 3515594 */
SELECT /*+ ORDERED */
hou.name org_id,
decode(p_view_by, 'OG', horg.name, -1) organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
decode(p_view_by, 'UC', wt.name, '-1') util_category_id,
decode(p_view_by, 'WT', wt.name, '-1') work_type_id,
decode(p_view_by, 'JL', job.name , '-1') job_level_id,
0 actual_hours,
0 capacity_hours,
0 act_reduce_capacity_hours,
null missing_hours,
0 total_actual_hours,
0 utilization_hours,
0 billable_hours,
0 nonbillable_hours,
0 training_hours,
total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_actual_hours,
0 prior_act_capacity_hours,
0 prior_act_red_capacity_hours,
0 prior_total_actual_hours,
fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) *
worktype.org_utilization_percentage / 100
prior_utilization_hours,
(fct.bill_hrs_a * worktype.org_utilization_percentage / 100)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_billable_hours,
((fct.total_hrs_a-fct.bill_hrs_a) * worktype.org_utilization_percentage / 100)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_nonbillable_hours,
(CASE WHEN worktype.training_flag = 'Y' THEN
fct.total_hrs_a
ELSE 0 END) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_training_hours
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_PMV_WT_DIM_TMP wt,
PJI_RM_RES_WT_F fct,
PJI_PMV_ORG_DIM_TMP hou,
PJI_PMV_JB_DIM_TMP job,
PA_WORK_TYPES_B worktype,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND wt.id = worktype.work_type_id
AND fct.work_type_id = wt.id
AND fct.job_id = job.id
AND fct.time_id = time.prior_id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.prior_id is not null
AND hou.id = imp.org_id
UNION ALL
SELECT '-1' org_id,
decode(p_view_by, 'OG', name, -1) organization_id,
'-1' time_id,
-1 time_key,
'-1' util_category_id,
'-1' work_type_id,
'-1' job_level_id,
0 actual_hours,
0 capacity_hours,
0 act_reduce_capacity_hours,
null missing_hours,
0 total_actual_hours,
0 utilization_hours,
0 billable_hours,
0 nonbillable_hours,
0 training_hours,
0 prior_actual_hours,
0 prior_act_capacity_hours,
0 prior_act_red_capacity_hours,
0 prior_total_actual_hours,
0 prior_utilization_hours,
0 prior_billable_hours,
0 prior_nonbillable_hours,
0 prior_training_hours
FROM PJI_PMV_ORGZ_DIM_TMP
WHERE name <> '-1'
UNION ALL
SELECT '-1' org_id,
'-1' organization_id,
'-1' time_id,
-1 time_key,
decode(p_view_by, 'UC', name, '-1') util_category_id,
decode(p_view_by, 'WT', name, '-1') work_type_id,
'-1' job_level_id,
0 actual_hours,
0 capacity_hours,
0 act_reduce_capacity_hours,
null missing_hours,
0 total_actual_hours,
0 utilization_hours,
0 billable_hours,
0 nonbillable_hours,
0 training_hours,
0 prior_actual_hours,
0 prior_act_capacity_hours,
0 prior_act_red_capacity_hours,
0 prior_total_actual_hours,
0 prior_utilization_hours,
0 prior_billable_hours,
0 prior_nonbillable_hours,
0 prior_training_hours
FROM PJI_PMV_WT_DIM_TMP
WHERE name <> '-1'
UNION ALL
SELECT '-1' org_id,
'-1' organization_id,
'-1' time_id,
-1 time_key,
'-1' util_category_id,
'-1' work_type_id,
name job_level_id,
0 actual_hours,
0 capacity_hours,
0 act_reduce_capacity_hours,
null missing_hours,
0 total_actual_hours,
0 utilization_hours,
0 billable_hours,
0 nonbillable_hours,
0 training_hours,
0 prior_actual_hours,
0 prior_act_capacity_hours,
0 prior_act_red_capacity_hours,
0 prior_total_actual_hours,
0 prior_utilization_hours,
0 prior_billable_hours,
0 prior_nonbillable_hours,
0 prior_training_hours
FROM PJI_PMV_JB_DIM_TMP
WHERE name <> '-1'
UNION ALL
SELECT '-1' org_id,
'-1' organization_id,
name time_id,
order_by_id time_key,
'-1' util_category_id,
'-1' work_type_id,
'-1' job_level_id,
0 actual_hours,
0 capacity_hours,
0 act_reduce_capacity_hours,
null missing_hours,
0 total_actual_hours,
0 utilization_hours,
0 billable_hours,
0 nonbillable_hours,
0 training_hours,
0 prior_actual_hours,
-- 0 prior_capacity_hours,
0 prior_act_capacity_hours,
0 prior_act_red_capacity_hours,
0 prior_total_actual_hours,
0 prior_utilization_hours,
0 prior_billable_hours,
0 prior_nonbillable_hours,
0 prior_training_hours
FROM PJI_PMV_TIME_DIM_TMP
WHERE name <> '-1'
UNION ALL -- added for current year capacity_hours
SELECT
FACT.org_id,
FACT.organization_id,
FACT.time_id,
FACT.time_key,
decode(p_view_by, 'UC', WT.name, '-1') util_category_id,
decode(p_view_by, 'WT', WT.name, '-1') work_type_id,
decode(p_view_by, 'JL', FACT.job_level_id , '-1') job_level_id,
0 actual_hours,
FACT.capacity_hours capacity_hours,
FACT.reduce_capacity_hrs_a act_reduce_capacity_hours,
FACT.missing_hrs_a missing_hours,
FACT.total_actual_hours total_actual_hours,
0 utilization_hours,
0 billable_hours,
0 nonbillable_hours,
0 training_hours,
0 prior_actual_hours,
0 prior_act_capacity_hours,
0 prior_act_red_capacity_hours,
0 prior_total_actual_hours,
0 prior_utilization_hours,
0 prior_billable_hours,
0 prior_nonbillable_hours,
0 prior_training_hours
FROM
/* Bug 3515594 */
(SELECT /*+ ORDERED */
hou.name org_id,
decode(p_view_by, 'OG', horg.name, -1) organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
job.name job_level_id,
fct.capacity_hrs / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
capacity_hours,
fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
total_actual_hours,
fct.missing_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
missing_hrs_a,
fct.reduce_capacity_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
reduce_capacity_hrs_a
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_PMV_JB_DIM_TMP job,
PJI_RM_JOB_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.job_id = job.id
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND hou.id = imp.org_id ) FACT,
(SELECT distinct WT.name
FROM PJI_PMV_WT_DIM_TMP wt ) WT
UNION ALL -- added for prior year capacity_hours
/* Bug 3515594 */
SELECT
FACT.org_id,
FACT.organization_id,
FACT.time_id,
FACT.time_key,
decode(p_view_by, 'UC', WT.name, '-1') util_category_id,
decode(p_view_by, 'WT', WT.name, '-1') work_type_id,
decode(p_view_by, 'JL', FACT.job_level_id , '-1') job_level_id,
0 actual_hours,
0 capacity_hours,
0 act_reduce_capacity_hours,
null missing_hours,
0 total_actual_hours,
0 utilization_hours,
0 billable_hours,
0 nonbillable_hours,
0 training_hours,
0 prior_actual_hours,
FACT.capacity_hours prior_act_capacity_hours,
FACT.reduce_capacity_hrs_a prior_act_red_capacity_hours,
FACT.total_actual_hours prior_total_actual_hours,
0 prior_utilization_hours,
0 prior_billable_hours,
0 prior_nonbillable_hours,
0 prior_training_hours
FROM
(SELECT /*+ ORDERED */
hou.name org_id,
decode(p_view_by, 'OG', horg.name, -1) organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
job.name job_level_id,
fct.capacity_hrs / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
capacity_hours,
fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
total_actual_hours,
fct.reduce_capacity_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
reduce_capacity_hrs_a
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_PMV_JB_DIM_TMP job,
PJI_RM_JOB_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.prior_id
AND fct.period_type_id = time.period_type
AND fct.job_id = job.id
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.prior_id is not null
AND hou.id = imp.org_id ) FACT,
(SELECT distinct WT.name
FROM PJI_PMV_WT_DIM_TMP wt ) WT
)
GROUP BY org_id,
organization_id,
time_id,
time_key,
util_category_id,
work_type_id,
job_level_id;
l_u3_tbl.DELETE(l_Top_Org_Index);
SELECT fnd_profile.value('PA_ORG_UTIL_DEF_CALC_METHOD')
INTO l_denominator
from dual;
select report_labor_units
into l_labor_unit
from pji_system_settings;
SELECT PJI_REP_U4(org_id,
organization_id,
time_id,
time_key,
util_category_id,
work_type_id,
job_level_id,
sum(scheduled_hours),
sum(scheduled_capacity_hours),
sum(scheduled_util_hours),
sum(provisional_hours),
sum(unassigned_hours),
sum(conf_billable_hours),
sum(conf_nonbillable_hours),
sum(prov_billable_hours),
sum(prov_nonbillable_hours),
sum(training_hours),
sum(expected_hours),
sum(expected_util_hours),
sum(expected_total_util_hours),
sum(actual_util_hours),
sum(actual_capacity_hours),
sum(expected_capacity_hours),
sum(prov_util_hours),
sum(exp_ac_util_hours),
sum(exp_sch_util_hours),
sum(decode(l_denominator, 'CAPACITY', exp_ac_capacity_hours, exp_ac_actual_hours)),
sum(decode(l_denominator, 'CAPACITY', exp_sch_capacity_hours, exp_sch_actual_hours)),
sum(decode(l_denominator, 'CAPACITY', actual_capacity_hours, actual_hours)),
sum(decode(l_denominator, 'CAPACITY', scheduled_capacity_hours, actual_hours)),
sum(decode(l_denominator, 'CAPACITY', expected_capacity_hours, actual_hours)),
sum(prior_scheduled_hours),
sum(prior_sch_capacity_hours),
sum(prior_sch_util_hours),
sum(prior_conf_billable_hours),
sum(prior_conf_nonbillable_hours),
sum(prior_actual_capacity_hours),
sum(prior_actual_util_hours),
sum(decode(l_denominator, 'CAPACITY', prior_actual_capacity_hours, prior_actual_hours)),
sum(decode(l_denominator, 'CAPACITY', prior_sch_capacity_hours, prior_actual_hours)),
null,null,null,null,null,null,
null,null,null,null,null,null,
null,null,null,null,null,null,
null,null,null,null,null,null,
null,null,null,null,null,null,
null,null,null,null,null,null,
null,null,null
)
/* Bug 3515594 */
BULK COLLECT INTO l_u4_tbl
FROM (
SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
'-1' util_category_id,
'-1' work_type_id,
'-1' job_level_id,
conf_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
scheduled_hours,
(capacity_hrs - reduce_capacity_hrs_s) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
scheduled_capacity_hours,
conf_wtd_org_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
scheduled_util_hours,
prov_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
provisional_hours,
unassigned_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
unassigned_hours,
conf_bill_wtd_org_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
conf_billable_hours,
(conf_wtd_org_hrs_s - conf_bill_wtd_org_hrs_s) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
conf_nonbillable_hours,
prov_wtd_org_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prov_billable_hours,
(prov_wtd_org_hrs_s - prov_bill_wtd_org_hrs_s) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prov_nonbillable_hours,
training_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
training_hours,
decode(time.amount_type, 0, total_hrs_a, conf_hrs_s)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
expected_hours,
decode(time.amount_type, 0, total_wtd_org_hrs_a, conf_wtd_org_hrs_s)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
expected_util_hours,
decode(time.amount_type, 0, total_wtd_org_hrs_a, conf_wtd_org_hrs_s + prov_wtd_org_hrs_s)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
expected_total_util_hours,
decode(time.amount_type, 0, capacity_hrs-reduce_capacity_hrs_a,
capacity_hrs-reduce_capacity_hrs_s)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
expected_capacity_hours,
total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
actual_hours,
total_wtd_org_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
actual_util_hours,
(capacity_hrs - reduce_capacity_hrs_a) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
actual_capacity_hours,
decode(time.amount_type, 0, 0, prov_wtd_org_hrs_s) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prov_util_hours,
decode(time.amount_type, 0, total_wtd_org_hrs_a, 0) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_ac_util_hours,
decode(time.amount_type, 0, 0, conf_wtd_org_hrs_s) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_sch_util_hours,
decode(time.amount_type, 0, total_hrs_a, 0) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_ac_actual_hours,
decode(time.amount_type, 0, 0, total_hrs_a) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_sch_actual_hours,
decode(time.amount_type, 0, capacity_hrs - reduce_capacity_hrs_a, capacity_hrs)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_ac_capacity_hours,
decode(time.amount_type, 0, capacity_hrs, capacity_hrs - reduce_capacity_hrs_s)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_sch_capacity_hours,
0 prior_scheduled_hours,
0 prior_sch_capacity_hours,
0 prior_sch_util_hours,
0 prior_conf_billable_hours,
0 prior_conf_nonbillable_hours,
0 prior_actual_capacity_hours,
0 prior_actual_util_hours,
0 prior_actual_hours
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_RM_ORGO_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND hou.id = imp.org_id
UNION ALL
/* Bug 3515594 */
SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
'-1' util_category_id,
'-1' work_type_id,
'-1' job_level_id,
0 scheduled_hours,
0 scheduled_capacity_hours,
0 scheduled_util_hours,
0 provisional_hours,
0 unassigned_hours,
0 conf_billable_hours,
0 conf_nonbillable_hours,
0 prov_billable_hours,
0 prov_nonbillable_hours,
0 training_hours,
0 expected_hours,
0 expected_util_hours,
0 expected_total_util_hours,
0 expected_capacity_hours,
0 actual_hours,
0 actual_util_hours,
0 actual_capacity_hours,
0 prov_util_hours,
0 exp_ac_util_hours,
0 exp_sch_util_hours,
0 exp_ac_actual_hours,
0 exp_sch_actual_hours,
0 exp_ac_capacity_hours,
0 exp_sch_capacity_hours,
total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_scheduled_hours,
(capacity_hrs - reduce_capacity_hrs_a) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_sch_capacity_hours,
total_wtd_org_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_sch_util_hours,
bill_wtd_org_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_conf_billable_hours,
(total_wtd_org_hrs_a - bill_wtd_org_hrs_a) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_conf_nonbillable_hours,
(capacity_hrs - reduce_capacity_hrs_a) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_actual_capacity_hours,
total_wtd_org_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_actual_util_hours,
total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_actual_hours
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_RM_ORGO_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.prior_id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.prior_id is not null
AND hou.id = imp.org_id
UNION ALL
SELECT '-1' org_id,
name organization_id,
'-1' time_id,
-1 time_key,
'-1' util_category_id,
'-1' work_type_id,
'-1' job_level_id,
0 scheduled_hours,
0 scheduled_capacity_hours,
0 scheduled_util_hours,
0 provisional_hours,
0 unassigned_hours,
0 conf_billable_hours,
0 conf_nonbillable_hours,
0 prov_billable_hours,
0 prov_nonbillable_hours,
0 training_hours,
0 expected_hours,
0 expected_util_hours,
0 expected_total_util_hours,
0 expected_capacity_hours,
0 actual_hours,
0 actual_util_hours,
0 actual_capacity_hours,
0 prov_util_hours,
0 exp_ac_util_hours,
0 exp_sch_util_hours,
0 exp_ac_actual_hours,
0 exp_sch_actual_hours,
0 exp_ac_capacity_hours,
0 exp_sch_capacity_hours,
0 prior_scheduled_hours,
0 prior_sch_capacity_hours,
0 prior_sch_util_hours,
0 prior_conf_billable_hours,
0 prior_conf_nonbillable_hours,
0 prior_actual_capacity_hours,
0 prior_actual_util_hours,
0 prior_actual_hours
FROM PJI_PMV_ORGZ_DIM_TMP
WHERE name <> '-1'
UNION ALL
SELECT '-1' org_id,
'-1' organization_id,
name time_id,
order_by_id time_key,
'-1' util_category_id,
'-1' work_type_id,
'-1' job_level_id,
0 scheduled_hours,
0 scheduled_capacity_hours,
0 scheduled_util_hours,
0 provisional_hours,
0 unassigned_hours,
0 conf_billable_hours,
0 conf_nonbillable_hours,
0 prov_billable_hours,
0 prov_nonbillable_hours,
0 training_hours,
0 expected_hours,
0 expected_util_hours,
0 expected_total_util_hours,
0 expected_capacity_hours,
0 actual_hours,
0 actual_util_hours,
0 actual_capacity_hours,
0 prov_util_hours,
0 exp_ac_util_hours,
0 exp_sch_util_hours,
0 exp_ac_actual_hours,
0 exp_sch_actual_hours,
0 exp_ac_capacity_hours,
0 exp_sch_capacity_hours,
0 prior_scheduled_hours,
0 prior_sch_capacity_hours,
0 prior_sch_util_hours,
0 prior_conf_billable_hours,
0 prior_conf_nonbillable_hours,
0 prior_actual_capacity_hours,
0 prior_actual_util_hours,
0 prior_actual_hours
FROM PJI_PMV_TIME_DIM_TMP
WHERE name <> '-1'
)
GROUP BY org_id,
organization_id,
time_id,
time_key,
util_category_id,
work_type_id,
job_level_id;
SELECT PJI_REP_U4(org_id,
organization_id,
time_id,
time_key,
util_category_id,
work_type_id,
job_level_id,
sum(scheduled_hours),
sum(decode (p_view_by, 'UC', null, 'WT',null, capacity_hours-sch_reduce_capacity_hours)),
sum(scheduled_util_hours),
sum(provisional_hours),
sum(decode (p_view_by, 'UC', null, 'WT',null, unassigned_hours)),
sum(conf_billable_hours),
sum(conf_nonbillable_hours),
sum(prov_billable_hours),
sum(prov_nonbillable_hours),
sum(training_hours),
sum(expected_hours),
sum(expected_util_hours),
sum(expected_total_util_hours),
sum(actual_util_hours),
sum(decode (p_view_by, 'UC', null, 'WT',null, capacity_hours-act_reduce_capacity_hours)),
sum(decode (p_view_by, 'UC', null, 'WT',null, capacity_hours-exp_reduce_capacity_hours)),
sum(prov_util_hours),
sum(exp_ac_util_hours),
sum(exp_sch_util_hours),
sum(decode(l_denominator, 'CAPACITY', capacity_hours-exp_ac_red_capacity_hours,
exp_ac_actual_hours)),
sum(decode(l_denominator, 'CAPACITY', capacity_hours-exp_sch_red_capacity_hours,
exp_ac_actual_hours)),
sum(decode(l_denominator, 'CAPACITY', capacity_hours-act_reduce_capacity_hours, total_actual_hours)),
sum(decode(l_denominator, 'CAPACITY', capacity_hours-sch_reduce_capacity_hours, total_actual_hours)),
sum(decode(l_denominator, 'CAPACITY', capacity_hours-exp_reduce_capacity_hours, total_actual_hours)),
sum(prior_scheduled_hours),
sum(prior_capacity_hours-prior_red_capacity_hours),
sum(prior_sch_util_hours),
sum(prior_conf_billable_hours),
sum(prior_conf_nonbillable_hours),
sum(prior_capacity_hours-prior_red_capacity_hours),
sum(prior_actual_util_hours),
sum(decode(l_denominator, 'CAPACITY', prior_capacity_hours-prior_red_capacity_hours,
prior_total_actual_hours)),
sum(decode(l_denominator, 'CAPACITY', prior_capacity_hours-prior_red_capacity_hours,
prior_total_actual_hours)),
null,null,null,null,null,null,
null,null,null,null,null,null,
null,null,null,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_u4_tbl
/* Bug 3515594 */
FROM (
SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
decode(p_view_by, 'UC', wt.name, '-1') util_category_id,
decode(p_view_by, 'WT', wt.name, '-1') work_type_id,
'-1' job_level_id,
conf_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
scheduled_hours,
0 capacity_hours,
0 sch_reduce_capacity_hours,
0 total_actual_hours,
(fct.conf_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)) *
worktype.org_utilization_percentage / 100
scheduled_util_hours,
prov_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
provisional_hours,
unassigned_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
unassigned_hours,
(CASE WHEN worktype.billable_capitalizable_flag = 'Y' THEN
fct.conf_hrs_s * worktype.org_utilization_percentage / 100
ELSE 0 END) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
conf_billable_hours,
(CASE WHEN worktype.billable_capitalizable_flag = 'N' THEN
fct.conf_hrs_s * worktype.org_utilization_percentage / 100
ELSE 0 END) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
conf_nonbillable_hours,
(CASE WHEN worktype.billable_capitalizable_flag = 'Y' THEN
fct.prov_hrs_s * worktype.org_utilization_percentage / 100
ELSE 0 END) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prov_billable_hours,
(CASE WHEN worktype.billable_capitalizable_flag = 'N' THEN
fct.prov_hrs_s * worktype.org_utilization_percentage / 100
ELSE 0 END) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prov_nonbillable_hours,
(CASE WHEN worktype.training_flag = 'Y' THEN
fct.conf_hrs_s
ELSE 0 END) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
training_hours,
decode(time.amount_type, 0, fct.total_hrs_a, fct.conf_hrs_s)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
expected_hours,
(decode(time.amount_type, 0, fct.total_hrs_a, fct.conf_hrs_s) * worktype.org_utilization_percentage / 100)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
expected_util_hours,
(decode(time.amount_type, 0, fct.total_hrs_a, fct.conf_hrs_s + fct.prov_hrs_s)
* worktype.org_utilization_percentage / 100) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
expected_total_util_hours,
0 exp_reduce_capacity_hours,
fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
actual_hours,
(fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)) *
worktype.org_utilization_percentage / 100
actual_util_hours,
0 act_reduce_capacity_hours,
(decode(time.amount_type, 0, 0, fct.prov_hrs_s) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)) *
worktype.org_utilization_percentage / 100
prov_util_hours,
(decode(time.amount_type, 0, fct.total_hrs_a, 0) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)) *
worktype.org_utilization_percentage / 100
exp_ac_util_hours,
(decode(time.amount_type, 0, 0, fct.conf_hrs_s) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)) *
worktype.org_utilization_percentage / 100
exp_sch_util_hours,
decode(time.amount_type, 0, fct.total_hrs_a, 0) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_ac_actual_hours,
decode(time.amount_type, 0, 0, fct.total_hrs_a) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_sch_actual_hours,
0 exp_ac_red_capacity_hours,
0 exp_sch_red_capacity_hours,
0 prior_scheduled_hours,
0 prior_capacity_hours,
0 prior_red_capacity_hours,
0 prior_total_actual_hours,
0 prior_sch_util_hours,
0 prior_conf_billable_hours,
0 prior_conf_nonbillable_hours,
0 prior_actual_util_hours,
0 prior_actual_hours
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_PMV_WT_DIM_TMP wt,
PJI_RM_WTO_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_WORK_TYPES_B worktype,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.work_type_id = wt.id
AND wt.id = worktype.work_type_id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND hou.id = imp.org_id
UNION ALL
/* Bug 3515594 */
SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
decode(p_view_by, 'UC', wt.name, '-1') util_category_id,
decode(p_view_by, 'WT', wt.name, '-1') work_type_id,
'-1' job_level_id,
0 scheduled_hours,
0 capacity_hours,
0 sch_reduce_capacity_hours,
0 total_actual_hours,
0 scheduled_util_hours,
0 provisional_hours,
0 unassigned_hours,
0 conf_billable_hours,
0 conf_nonbillable_hours,
0 prov_billable_hours,
0 prov_nonbillable_hours,
0 training_hours,
0 expected_hours,
0 expected_util_hours,
0 expected_total_util_hours,
0 exp_reduce_capacity_hours,
0 actual_hours,
0 actual_util_hours,
0 act_reduce_capacity_hours,
0 prov_util_hours,
0 exp_ac_util_hours,
0 exp_sch_util_hours,
0 exp_ac_actual_hours,
0 exp_sch_actual_hours,
0 exp_ac_red_capacity_hours,
0 exp_sch_red_capacity_hours,
fct.total_hrs_a * decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_scheduled_hours,
0 prior_capacity_hours,
0 prior_red_capacity_hours,
0 prior_total_actual_hours,
fct.total_hrs_a * decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) *
worktype.org_utilization_percentage / 100 prior_sch_util_hours,
(CASE WHEN worktype.billable_capitalizable_flag = 'Y' THEN
fct.total_hrs_a * worktype.org_utilization_percentage / 100
ELSE 0 END) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_conf_billable_hours,
(CASE WHEN worktype.billable_capitalizable_flag = 'N' THEN
fct.total_hrs_a * worktype.org_utilization_percentage / 100
ELSE 0 END) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_conf_nonbillable_hours,
fct.total_hrs_a * decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) *
worktype.org_utilization_percentage / 100 prior_actual_util_hours,
fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_actual_hours
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_PMV_WT_DIM_TMP wt,
PJI_RM_WTO_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_WORK_TYPES_B worktype,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.work_type_id = wt.id
AND wt.id = worktype.work_type_id
AND fct.time_id = time.prior_id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.prior_id is not null
AND hou.id = imp.org_id
UNION ALL
SELECT '-1' org_id,
name organization_id,
'-1' time_id,
-1 time_key,
'-1' util_category_id,
'-1' work_type_id,
'-1' job_level_id,
0 scheduled_hours,
0 capacity_hours,
0 sch_reduce_capacity_hours,
0 total_actual_hours,
0 scheduled_util_hours,
0 provisional_hours,
0 unassigned_hours,
0 conf_billable_hours,
0 conf_nonbillable_hours,
0 prov_billable_hours,
0 prov_nonbillable_hours,
0 training_hours,
0 expected_hours,
0 expected_util_hours,
0 expected_total_util_hours,
0 exp_reduce_capacity_hours,
0 actual_hours,
0 actual_util_hours,
0 act_reduce_capacity_hours,
0 prov_util_hours,
0 exp_ac_util_hours,
0 exp_sch_util_hours,
0 exp_ac_actual_hours,
0 exp_sch_actual_hours,
0 exp_ac_red_capacity_hours,
0 exp_sch_red_capacity_hours,
0 prior_scheduled_hours,
0 prior_capacity_hours,
0 prior_red_capacity_hours,
0 prior_total_actual_hours,
0 prior_sch_util_hours,
0 prior_conf_billable_hours,
0 prior_conf_nonbillable_hours,
0 prior_actual_util_hours,
0 prior_actual_hours
FROM PJI_PMV_ORGZ_DIM_TMP
WHERE name <> '-1'
UNION ALL
SELECT '-1' org_id,
'-1' organization_id,
'-1' time_id,
-1 time_key,
decode(p_view_by, 'UC', name, '-1') util_category_id,
decode(p_view_by, 'WT', name, '-1') work_type_id,
'-1' job_level_id,
0 scheduled_hours,
0 capacity_hours,
0 sch_reduce_capacity_hours,
0 total_actual_hours,
0 scheduled_util_hours,
0 provisional_hours,
0 unassigned_hours,
0 conf_billable_hours,
0 conf_nonbillable_hours,
0 prov_billable_hours,
0 prov_nonbillable_hours,
0 training_hours,
0 expected_hours,
0 expected_util_hours,
0 expected_total_util_hours,
0 exp_reduce_capacity_hours,
0 actual_hours,
0 actual_util_hours,
0 act_reduce_capacity_hours,
0 prov_util_hours,
0 exp_ac_util_hours,
0 exp_sch_util_hours,
0 exp_ac_actual_hours,
0 exp_sch_actual_hours,
0 exp_ac_red_capacity_hours,
0 exp_sch_red_capacity_hours,
0 prior_scheduled_hours,
0 prior_capacity_hours,
0 prior_red_capacity_hours,
0 prior_total_actual_hours,
0 prior_sch_util_hours,
0 prior_conf_billable_hours,
0 prior_conf_nonbillable_hours,
0 prior_actual_util_hours,
0 prior_actual_hours
FROM PJI_PMV_WT_DIM_TMP
WHERE name <> '-1'
UNION ALL
SELECT '-1' org_id,
'-1' organization_id,
name time_id,
order_by_id time_key,
'-1' util_category_id,
'-1' work_type_id,
'-1' job_level_id,
0 scheduled_hours,
0 capacity_hours,
0 sch_reduce_capacity_hours,
0 total_actual_hours,
0 scheduled_util_hours,
0 provisional_hours,
0 unassigned_hours,
0 conf_billable_hours,
0 conf_nonbillable_hours,
0 prov_billable_hours,
0 prov_nonbillable_hours,
0 training_hours,
0 expected_hours,
0 expected_util_hours,
0 expected_total_util_hours,
0 exp_reduce_capacity_hours,
0 actual_hours,
0 actual_util_hours,
0 act_reduce_capacity_hours,
0 prov_util_hours,
0 exp_ac_util_hours,
0 exp_sch_util_hours,
0 exp_ac_actual_hours,
0 exp_sch_actual_hours,
0 exp_ac_red_capacity_hours,
0 exp_sch_red_capacity_hours,
0 prior_scheduled_hours,
0 prior_capacity_hours,
0 prior_red_capacity_hours,
0 prior_total_actual_hours,
0 prior_sch_util_hours,
0 prior_conf_billable_hours,
0 prior_conf_nonbillable_hours,
0 prior_actual_util_hours,
0 prior_actual_hours
FROM PJI_PMV_TIME_DIM_TMP
WHERE name <> '-1'
UNION ALL -- added for current year capacity_hours
SELECT
FACT.org_id,
FACT.organization_id,
FACT.time_id,
FACT.time_key,
decode(p_view_by, 'UC', WT.name, '-1') util_category_id,
decode(p_view_by, 'WT', WT.name, '-1') work_type_id,
'-1' job_level_id,
0 scheduled_hours,
FACT.capacity_hours,
FACT.sch_reduce_capacity_hours sch_reduce_capacity_hours,
FACT.total_actual_hours total_actual_hours,
0 scheduled_util_hours,
0 provisional_hours,
0 unassigned_hours,
0 conf_billable_hours,
0 conf_nonbillable_hours,
0 prov_billable_hours,
0 prov_nonbillable_hours,
0 training_hours,
0 expected_hours,
0 expected_util_hours,
0 expected_total_util_hours,
FACT.exp_reduce_capacity_hours exp_reduce_capacity_hours,
0 actual_hours,
0 actual_util_hours,
FACT.act_reduce_capacity_hours act_reduce_capacity_hours,
0 prov_util_hours,
0 exp_ac_util_hours,
0 exp_sch_util_hours,
0 exp_ac_actual_hours,
0 exp_sch_actual_hours,
FACT.exp_ac_red_capacity_hours exp_ac_red_capacity_hours,
FACT.exp_sch_red_capacity_hours exp_sch_red_capacity_hours,
0 prior_scheduled_hours,
0 prior_capacity_hours,
0 prior_red_capacity_hours,
0 prior_total_actual_hours,
0 prior_sch_util_hours,
0 prior_conf_billable_hours,
0 prior_conf_nonbillable_hours,
0 prior_actual_util_hours,
0 prior_actual_hours
FROM
/* Bug 3515594 */
(SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
fct.capacity_hrs / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
capacity_hours,
fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
total_actual_hours,
fct.reduce_capacity_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
sch_reduce_capacity_hours,
decode(time.amount_type, 0, fct.reduce_capacity_hrs_a, fct.reduce_capacity_hrs_s)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_reduce_capacity_hours,
fct.reduce_capacity_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
act_reduce_capacity_hours,
decode(time.amount_type, 0, fct.reduce_capacity_hrs_a, 0)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_ac_red_capacity_hours,
decode(time.amount_type, 1, fct.reduce_capacity_hrs_s, 0)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_sch_red_capacity_hours
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_RM_ORGO_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND hou.id = imp.org_id ) FACT,
(SELECT distinct WT.name
FROM PJI_PMV_WT_DIM_TMP wt ) WT
UNION ALL -- added for prior year capacity_hours
SELECT
FACT.org_id,
FACT.organization_id,
FACT.time_id,
FACT.time_key,
decode(p_view_by, 'UC', WT.name, '-1') util_category_id,
decode(p_view_by, 'WT', WT.name, '-1') work_type_id,
'-1' job_level_id,
0 scheduled_hours,
0 capacity_hours,
0 sch_reduce_capacity_hours,
0 total_actual_hours,
0 scheduled_util_hours,
0 provisional_hours,
0 unassigned_hours,
0 conf_billable_hours,
0 conf_nonbillable_hours,
0 prov_billable_hours,
0 prov_nonbillable_hours,
0 training_hours,
0 expected_hours,
0 expected_util_hours,
0 expected_total_util_hours,
0 exp_reduce_capacity_hours,
0 actual_hours,
0 actual_util_hours,
0 act_reduce_capacity_hours,
0 prov_util_hours,
0 exp_ac_util_hours,
0 exp_sch_util_hours,
0 exp_ac_actual_hours,
0 exp_sch_actual_hours,
0 exp_ac_red_capacity_hours,
0 exp_sch_red_capacity_hours,
0 prior_scheduled_hours,
FACT.capacity_hours prior_capacity_hours,
FACT.prior_red_capacity_hours prior_red_capacity_hours,
FACT.total_actual_hours prior_total_actual_hours,
0 prior_sch_util_hours,
0 prior_conf_billable_hours,
0 prior_conf_nonbillable_hours,
0 prior_actual_util_hours,
0 prior_actual_hours
FROM
(SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
fct.capacity_hrs / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
capacity_hours,
fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
total_actual_hours,
fct.reduce_capacity_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_red_capacity_hours
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_RM_ORGO_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.prior_id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.prior_id is not null
AND hou.id = imp.org_id ) FACT,
(SELECT distinct WT.name
FROM PJI_PMV_WT_DIM_TMP wt ) WT
)
GROUP BY org_id,
organization_id,
time_id,
time_key,
util_category_id,
work_type_id,
job_level_id;
SELECT PJI_REP_U4(org_id,
organization_id,
time_id,
time_key,
util_category_id,
work_type_id,
job_level_id,
sum(scheduled_hours),
sum(capacity_hours - reduce_capacity_hrs_s),
sum(scheduled_util_hours),
sum(provisional_hours),
sum(unassigned_hours),
sum(conf_billable_hours),
sum(conf_nonbillable_hours),
sum(prov_billable_hours),
sum(prov_nonbillable_hours),
sum(training_hours),
sum(expected_hours),
sum(expected_util_hours),
sum(expected_total_util_hours),
sum(actual_util_hours),
sum(capacity_hours - reduce_capacity_hrs_a),
sum(capacity_hours - reduce_capacity_hrs_e),
sum(prov_util_hours),
sum(exp_ac_util_hours),
sum(exp_sch_util_hours),
sum(decode(l_denominator, 'CAPACITY', capacity_hours - exp_ac_reduce_capacity_hours, exp_ac_actual_hours)),
sum(decode(l_denominator, 'CAPACITY', capacity_hours - exp_sch_reduce_capacity_hours, exp_sch_actual_hours)),
sum(decode(l_denominator, 'CAPACITY', capacity_hours - reduce_capacity_hrs_a, actual_hours)),
sum(decode(l_denominator, 'CAPACITY', capacity_hours - reduce_capacity_hrs_s, actual_hours)),
sum(decode(l_denominator, 'CAPACITY', capacity_hours - reduce_capacity_hrs_e, actual_hours)),
sum(prior_scheduled_hours),
sum(prior_capacity_hours - prior_reduce_capacity_hrs_a),
sum(prior_sch_util_hours),
sum(prior_conf_billable_hours),
sum(prior_conf_nonbillable_hours),
sum(prior_capacity_hours - prior_reduce_capacity_hrs_a),
sum(prior_actual_util_hours),
sum(decode(l_denominator, 'CAPACITY', prior_capacity_hours - prior_reduce_capacity_hrs_a, prior_actual_hours)),
sum(decode(l_denominator, 'CAPACITY', prior_capacity_hours - prior_reduce_capacity_hrs_a, prior_actual_hours)),
null,null,null,null,null,null,
null,null,null,null,null,null,
null,null,null,null,null,null,
null,null,null,null,null,null,
null,null,null,null,null,null,
null,null,null,null,null,null,
null,null,null
)
/* Bug 3515594 */
BULK COLLECT INTO l_u4_tbl
FROM (
SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
'-1' util_category_id,
'-1' work_type_id,
job.name job_level_id,
conf_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
scheduled_hours,
capacity_hrs capacity_hours,
conf_wtd_org_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
scheduled_util_hours,
prov_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
provisional_hours,
unassigned_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
unassigned_hours,
conf_bill_wtd_org_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
conf_billable_hours,
(conf_wtd_org_hrs_s - conf_bill_wtd_org_hrs_s) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
conf_nonbillable_hours,
prov_wtd_org_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prov_billable_hours,
(prov_wtd_org_hrs_s - prov_bill_wtd_org_hrs_s) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prov_nonbillable_hours,
training_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
training_hours,
decode(time.amount_type, 0, total_hrs_a, conf_hrs_s)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
expected_hours,
decode(time.amount_type, 0, total_wtd_org_hrs_a, conf_wtd_org_hrs_s)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
expected_util_hours,
decode(time.amount_type, 0, total_wtd_org_hrs_a, conf_wtd_org_hrs_s + prov_wtd_org_hrs_s)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
expected_total_util_hours,
total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
actual_hours,
total_wtd_org_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
actual_util_hours,
decode(time.amount_type, 0, 0, prov_wtd_org_hrs_s) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prov_util_hours,
decode(time.amount_type, 0, total_wtd_org_hrs_a, 0) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_ac_util_hours,
decode(time.amount_type, 0, 0, conf_wtd_org_hrs_s) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_sch_util_hours,
decode(time.amount_type, 0, total_hrs_a, 0) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_ac_actual_hours,
decode(time.amount_type, 0, 0, total_hrs_a) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_sch_actual_hours,
reduce_capacity_hrs_a
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
reduce_capacity_hrs_a,
reduce_capacity_hrs_s
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
reduce_capacity_hrs_s,
decode(time.amount_type, 0, reduce_capacity_hrs_a,
reduce_capacity_hrs_s)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
reduce_capacity_hrs_e,
decode(time.amount_type, 0, reduce_capacity_hrs_a,
0)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_ac_reduce_capacity_hours,
decode(time.amount_type, 1, reduce_capacity_hrs_s,
0)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_sch_reduce_capacity_hours,
0 prior_reduce_capacity_hrs_a,
0 prior_scheduled_hours,
0 prior_capacity_hours,
0 prior_sch_util_hours,
0 prior_conf_billable_hours,
0 prior_conf_nonbillable_hours,
0 prior_actual_util_hours,
0 prior_actual_hours
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_PMV_JB_DIM_TMP job,
PJI_RM_JOBO_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.job_id = job.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND hou.id = imp.org_id
UNION ALL
/* Bug 3515594 */
SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
'-1' util_category_id,
'-1' work_type_id,
job.name job_level_id,
0 scheduled_hours,
0 capacity_hours,
0 scheduled_util_hours,
0 provisional_hours,
0 unassigned_hours,
0 conf_billable_hours,
0 conf_nonbillable_hours,
0 prov_billable_hours,
0 prov_nonbillable_hours,
0 training_hours,
0 expected_hours,
0 expected_util_hours,
0 expected_total_util_hours,
0 actual_hours,
0 actual_util_hours,
0 prov_util_hours,
0 exp_ac_util_hours,
0 exp_sch_util_hours,
0 exp_ac_actual_hours,
0 exp_sch_actual_hours,
0 reduce_capacity_hrs_a,
0 reduce_capacity_hrs_s,
0 reduce_capacity_hrs_e,
0 exp_ac_reduce_capacity_hours,
0 exp_sch_reduce_capacity_hours,
reduce_capacity_hrs_a prior_reduce_capacity_hrs_a,
total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_scheduled_hours,
capacity_hrs prior_capacity_hours,
total_wtd_org_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_sch_util_hours,
bill_wtd_org_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_conf_billable_hours,
(total_wtd_org_hrs_a - bill_wtd_org_hrs_a) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_conf_nonbillable_hours,
total_wtd_org_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_actual_util_hours,
total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_actual_hours
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_PMV_JB_DIM_TMP job,
PJI_RM_JOBO_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.job_id = job.id
AND fct.time_id = time.prior_id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.prior_id is not null
AND hou.id = imp.org_id
UNION ALL
SELECT '-1' org_id,
name organization_id,
'-1' time_id,
-1 time_key,
'-1' util_category_id,
'-1' work_type_id,
'-1' job_level_id,
0 scheduled_hours,
0 capacity_hours,
0 scheduled_util_hours,
0 provisional_hours,
0 unassigned_hours,
0 conf_billable_hours,
0 conf_nonbillable_hours,
0 prov_billable_hours,
0 prov_nonbillable_hours,
0 training_hours,
0 expected_hours,
0 expected_util_hours,
0 expected_total_util_hours,
0 actual_hours,
0 actual_util_hours,
0 prov_util_hours,
0 exp_ac_util_hours,
0 exp_sch_util_hours,
0 exp_ac_actual_hours,
0 exp_sch_actual_hours,
0 reduce_capacity_hrs_a,
0 reduce_capacity_hrs_s,
0 reduce_capacity_hrs_e,
0 exp_ac_reduce_capacity_hours,
0 exp_sch_reduce_capacity_hours,
0 prior_reduce_capacity_hrs_a,
0 prior_scheduled_hours,
0 prior_capacity_hours,
0 prior_sch_util_hours,
0 prior_conf_billable_hours,
0 prior_conf_nonbillable_hours,
0 prior_actual_util_hours,
0 prior_actual_hours
FROM PJI_PMV_ORGZ_DIM_TMP
WHERE name <> '-1'
UNION ALL
SELECT '-1' org_id,
'-1' organization_id,
'-1' time_id,
-1 time_key,
'-1' util_category_id,
'-1' work_type_id,
name job_level_id,
0 scheduled_hours,
0 capacity_hours,
0 scheduled_util_hours,
0 provisional_hours,
0 unassigned_hours,
0 conf_billable_hours,
0 conf_nonbillable_hours,
0 prov_billable_hours,
0 prov_nonbillable_hours,
0 training_hours,
0 expected_hours,
0 expected_util_hours,
0 expected_total_util_hours,
0 actual_hours,
0 actual_util_hours,
0 prov_util_hours,
0 exp_ac_util_hours,
0 exp_sch_util_hours,
0 exp_ac_actual_hours,
0 exp_sch_actual_hours,
0 reduce_capacity_hrs_a,
0 reduce_capacity_hrs_s,
0 reduce_capacity_hrs_e,
0 exp_ac_reduce_capacity_hours,
0 exp_sch_reduce_capacity_hours,
0 prior_reduce_capacity_hrs_a,
0 prior_scheduled_hours,
0 prior_capacity_hours,
0 prior_sch_util_hours,
0 prior_conf_billable_hours,
0 prior_conf_nonbillable_hours,
0 prior_actual_util_hours,
0 prior_actual_hours
FROM PJI_PMV_JB_DIM_TMP
WHERE name <> '-1'
UNION ALL
SELECT '-1' org_id,
'-1' organization_id,
name time_id,
order_by_id time_key,
'-1' util_category_id,
'-1' work_type_id,
'-1' job_level_id,
0 scheduled_hours,
0 capacity_hours,
0 scheduled_util_hours,
0 provisional_hours,
0 unassigned_hours,
0 conf_billable_hours,
0 conf_nonbillable_hours,
0 prov_billable_hours,
0 prov_nonbillable_hours,
0 training_hours,
0 expected_hours,
0 expected_util_hours,
0 expected_total_util_hours,
0 actual_hours,
0 actual_util_hours,
0 prov_util_hours,
0 exp_ac_util_hours,
0 exp_sch_util_hours,
0 exp_ac_actual_hours,
0 exp_sch_actual_hours,
0 reduce_capacity_hrs_a,
0 reduce_capacity_hrs_s,
0 reduce_capacity_hrs_e,
0 exp_ac_reduce_capacity_hours,
0 exp_sch_reduce_capacity_hours,
0 prior_reduce_capacity_hrs_a,
0 prior_scheduled_hours,
0 prior_capacity_hours,
0 prior_sch_util_hours,
0 prior_conf_billable_hours,
0 prior_conf_nonbillable_hours,
0 prior_actual_util_hours,
0 prior_actual_hours
FROM PJI_PMV_TIME_DIM_TMP
WHERE name <> '-1'
)
GROUP BY org_id,
organization_id,
time_id,
time_key,
util_category_id,
work_type_id,
job_level_id;
SELECT PJI_REP_U4(org_id,
organization_id,
time_id,
time_key,
util_category_id,
work_type_id,
job_level_id,
sum(scheduled_hours),
sum(decode (p_view_by, 'UC', null, 'WT',null, capacity_hours-sch_reduce_capacity_hours)),
sum(scheduled_util_hours),
sum(provisional_hours),
sum(decode (p_view_by, 'UC', null, 'WT',null, unassigned_hours)),
sum(conf_billable_hours),
sum(conf_nonbillable_hours),
sum(prov_billable_hours),
sum(prov_nonbillable_hours),
sum(training_hours),
sum(expected_hours),
sum(expected_util_hours),
sum(expected_total_util_hours),
sum(actual_util_hours),
sum(decode (p_view_by, 'UC', null, 'WT',null, capacity_hours-act_reduce_capacity_hours)),
sum(decode (p_view_by, 'UC', null, 'WT',null, capacity_hours-exp_reduce_capacity_hours)),
sum(prov_util_hours),
sum(exp_ac_util_hours),
sum(exp_sch_util_hours),
sum(decode(l_denominator, 'CAPACITY', capacity_hours-exp_ac_red_capacity_hours,
exp_ac_actual_hours)),
sum(decode(l_denominator, 'CAPACITY', capacity_hours-exp_sch_red_capacity_hours,
exp_ac_actual_hours)),
sum(decode(l_denominator, 'CAPACITY', capacity_hours-act_reduce_capacity_hours, total_actual_hours)),
sum(decode(l_denominator, 'CAPACITY', capacity_hours-sch_reduce_capacity_hours, total_actual_hours)),
sum(decode(l_denominator, 'CAPACITY', capacity_hours-exp_reduce_capacity_hours, total_actual_hours)),
sum(prior_scheduled_hours),
sum(prior_capacity_hours-prior_red_capacity_hours),
sum(prior_sch_util_hours),
sum(prior_conf_billable_hours),
sum(prior_conf_nonbillable_hours),
sum(prior_capacity_hours-prior_red_capacity_hours),
sum(prior_actual_util_hours),
sum(decode(l_denominator, 'CAPACITY', prior_capacity_hours-prior_red_capacity_hours,
prior_total_actual_hours)),
sum(decode(l_denominator, 'CAPACITY', prior_capacity_hours-prior_red_capacity_hours,
prior_total_actual_hours)),
null,null,null,null,null,null,
null,null,null,null,null,null,
null,null,null,null,null,null,
null,null,null,null,null,null,
null,null,null,null,null,null,
null,null,null,null,null,null,
null,null,null
)
/* Bug 3515594 */
BULK COLLECT INTO l_u4_tbl
FROM (
SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
decode(p_view_by, 'UC', wt.name, '-1') util_category_id,
decode(p_view_by, 'WT', wt.name, '-1') work_type_id,
job.name job_level_id,
conf_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
scheduled_hours,
0 capacity_hours,
0 sch_reduce_capacity_hours,
0 total_actual_hours,
(fct.conf_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)) *
worktype.org_utilization_percentage / 100 scheduled_util_hours,
prov_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
provisional_hours,
unassigned_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
unassigned_hours,
(CASE WHEN worktype.billable_capitalizable_flag = 'Y' THEN
fct.conf_hrs_s * worktype.org_utilization_percentage / 100
ELSE 0 END) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
conf_billable_hours,
(CASE WHEN worktype.billable_capitalizable_flag = 'N' THEN
fct.conf_hrs_s * worktype.org_utilization_percentage / 100
ELSE 0 END) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
conf_nonbillable_hours,
(CASE WHEN worktype.billable_capitalizable_flag = 'Y' THEN
fct.prov_hrs_s * worktype.org_utilization_percentage / 100
ELSE 0 END) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prov_billable_hours,
(CASE WHEN worktype.billable_capitalizable_flag = 'N' THEN
fct.prov_hrs_s * worktype.org_utilization_percentage / 100
ELSE 0 END) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prov_nonbillable_hours,
(CASE WHEN worktype.training_flag = 'Y' THEN
fct.conf_hrs_s
ELSE 0 END) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
training_hours,
decode(time.amount_type, 0, total_hrs_a, conf_hrs_s)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
expected_hours,
(decode(time.amount_type, 0, total_hrs_a, conf_hrs_s) * worktype.org_utilization_percentage / 100)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
expected_util_hours,
(decode(time.amount_type, 0, total_hrs_a, conf_hrs_s + prov_hrs_s) * worktype.org_utilization_percentage / 100)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
expected_total_util_hours,
0 exp_reduce_capacity_hours,
total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
actual_hours,
(fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1))*
worktype.org_utilization_percentage / 100 actual_util_hours,
0 act_reduce_capacity_hours,
(decode(time.amount_type, 0, 0, fct.prov_hrs_s) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)) *
worktype.org_utilization_percentage / 100 prov_util_hours,
(decode(time.amount_type, 0, fct.total_hrs_a, 0) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)) *
worktype.org_utilization_percentage / 100
exp_ac_util_hours,
(decode(time.amount_type, 0, 0, fct.conf_hrs_s) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)) *
worktype.org_utilization_percentage / 100
exp_sch_util_hours,
decode(time.amount_type, 0, fct.total_hrs_a, 0) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_ac_actual_hours,
decode(time.amount_type, 0, 0, fct.total_hrs_a) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_sch_actual_hours,
0 exp_ac_red_capacity_hours,
0 exp_sch_red_capacity_hours,
0 prior_scheduled_hours,
0 prior_capacity_hours,
0 prior_red_capacity_hours,
0 prior_total_actual_hours,
0 prior_sch_util_hours,
0 prior_conf_billable_hours,
0 prior_conf_nonbillable_hours,
0 prior_actual_util_hours,
0 prior_actual_hours
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_PMV_WT_DIM_TMP wt,
PJI_RM_RES_WT_F fct,
PJI_PMV_ORG_DIM_TMP hou,
PJI_PMV_JB_DIM_TMP job,
PA_WORK_TYPES_B worktype,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.work_type_id = wt.id
AND wt.id = worktype.work_type_id
AND fct.job_id = job.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND hou.id = imp.org_id
UNION ALL
/* Bug 3515594 */
SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
decode(p_view_by, 'UC', wt.name, '-1') util_category_id,
decode(p_view_by, 'WT', wt.name, '-1') work_type_id,
job.name job_level_id,
0 scheduled_hours,
0 capacity_hours,
0 sch_reduce_capacity_hours,
0 total_actual_hours,
0 scheduled_util_hours,
0 provisional_hours,
0 unassigned_hours,
0 conf_billable_hours,
0 conf_nonbillable_hours,
0 prov_billable_hours,
0 prov_nonbillable_hours,
0 training_hours,
0 expected_hours,
0 expected_util_hours,
0 expected_total_util_hours,
0 exp_reduce_capacity_hours,
0 actual_hours,
0 actual_util_hours,
0 act_reduce_capacity_hours,
0 prov_util_hours,
0 exp_ac_util_hours,
0 exp_sch_util_hours,
0 exp_ac_actual_hours,
0 exp_sch_actual_hours,
0 exp_ac_red_capacity_hours,
0 exp_sch_red_capacity_hours,
total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_scheduled_hours,
0 prior_capacity_hours,
0 prior_red_capacity_hours,
0 prior_total_actual_hours,
(fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)) *
worktype.org_utilization_percentage / 100 prior_sch_util_hours,
(CASE WHEN worktype.billable_capitalizable_flag = 'Y' THEN
fct.total_hrs_a * worktype.org_utilization_percentage / 100
ELSE 0 END) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_conf_billable_hours,
(CASE WHEN worktype.billable_capitalizable_flag = 'N' THEN
fct.conf_hrs_s * worktype.org_utilization_percentage / 100
ELSE 0 END) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_conf_nonbillable_hours,
fct.total_hrs_a * decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) *
worktype.org_utilization_percentage / 100 prior_actual_util_hours,
total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_actual_hours
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_PMV_WT_DIM_TMP wt,
PJI_RM_RES_WT_F fct,
PJI_PMV_ORG_DIM_TMP hou,
PJI_PMV_JB_DIM_TMP job,
PA_WORK_TYPES_B worktype,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.work_type_id = wt.id
AND wt.id = worktype.work_type_id
AND fct.job_id = job.id
AND fct.time_id = time.prior_id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.prior_id is not null
AND hou.id = imp.org_id
UNION ALL
SELECT '-1' org_id,
name organization_id,
'-1' time_id,
-1 time_key,
'-1' util_category_id,
'-1' work_type_id,
'-1' job_level_id,
0 scheduled_hours,
0 capacity_hours,
0 sch_reduce_capacity_hours,
0 total_actual_hours,
0 scheduled_util_hours,
0 provisional_hours,
0 unassigned_hours,
0 conf_billable_hours,
0 conf_nonbillable_hours,
0 prov_billable_hours,
0 prov_nonbillable_hours,
0 training_hours,
0 expected_hours,
0 expected_util_hours,
0 expected_total_util_hours,
0 exp_reduce_capacity_hours,
0 actual_hours,
0 actual_util_hours,
0 act_reduce_capacity_hours,
0 prov_util_hours,
0 exp_ac_util_hours,
0 exp_sch_util_hours,
0 exp_ac_actual_hours,
0 exp_sch_actual_hours,
0 exp_ac_red_capacity_hours,
0 exp_sch_red_capacity_hours,
0 prior_scheduled_hours,
0 prior_capacity_hours,
0 prior_red_capacity_hours,
0 prior_total_actual_hours,
0 prior_sch_util_hours,
0 prior_conf_billable_hours,
0 prior_conf_nonbillable_hours,
0 prior_actual_util_hours,
0 prior_actual_hours
FROM PJI_PMV_ORGZ_DIM_TMP
WHERE name <> '-1'
UNION ALL
SELECT '-1' org_id,
'-1' organization_id,
'-1' time_id,
-1 time_key,
decode(p_view_by, 'UC', name, '-1') util_category_id,
decode(p_view_by, 'WT', name, '-1') work_type_id,
'-1' job_level_id,
0 scheduled_hours,
0 capacity_hours,
0 sch_reduce_capacity_hours,
0 total_actual_hours,
0 scheduled_util_hours,
0 provisional_hours,
0 unassigned_hours,
0 conf_billable_hours,
0 conf_nonbillable_hours,
0 prov_billable_hours,
0 prov_nonbillable_hours,
0 training_hours,
0 expected_hours,
0 expected_util_hours,
0 expected_total_util_hours,
0 exp_reduce_capacity_hours,
0 actual_hours,
0 actual_util_hours,
0 act_reduce_capacity_hours,
0 prov_util_hours,
0 exp_ac_util_hours,
0 exp_sch_util_hours,
0 exp_ac_actual_hours,
0 exp_sch_actual_hours,
0 exp_ac_red_capacity_hours,
0 exp_sch_red_capacity_hours,
0 prior_scheduled_hours,
0 prior_capacity_hours,
0 prior_red_capacity_hours,
0 prior_total_actual_hours,
0 prior_sch_util_hours,
0 prior_conf_billable_hours,
0 prior_conf_nonbillable_hours,
0 prior_actual_util_hours,
0 prior_actual_hours
FROM PJI_PMV_WT_DIM_TMP
WHERE name <> '-1'
UNION ALL
SELECT '-1' org_id,
'-1' organization_id,
'-1' time_id,
-1 time_key,
'-1' util_category_id,
'-1' work_type_id,
name job_level_id,
0 scheduled_hours,
0 capacity_hours,
0 sch_reduce_capacity_hours,
0 total_actual_hours,
0 scheduled_util_hours,
0 provisional_hours,
0 unassigned_hours,
0 conf_billable_hours,
0 conf_nonbillable_hours,
0 prov_billable_hours,
0 prov_nonbillable_hours,
0 training_hours,
0 expected_hours,
0 expected_util_hours,
0 expected_total_util_hours,
0 exp_reduce_capacity_hours,
0 actual_hours,
0 actual_util_hours,
0 act_reduce_capacity_hours,
0 prov_util_hours,
0 exp_ac_util_hours,
0 exp_sch_util_hours,
0 exp_ac_actual_hours,
0 exp_sch_actual_hours,
0 exp_ac_red_capacity_hours,
0 exp_sch_red_capacity_hours,
0 prior_scheduled_hours,
0 prior_capacity_hours,
0 prior_red_capacity_hours,
0 prior_total_actual_hours,
0 prior_sch_util_hours,
0 prior_conf_billable_hours,
0 prior_conf_nonbillable_hours,
0 prior_actual_util_hours,
0 prior_actual_hours
FROM PJI_PMV_JB_DIM_TMP
WHERE name <> '-1'
UNION ALL
SELECT '-1' org_id,
'-1' organization_id,
name time_id,
order_by_id time_key,
'-1' util_category_id,
'-1' work_type_id,
'-1' job_level_id,
0 scheduled_hours,
0 capacity_hours,
0 sch_reduce_capacity_hours,
0 total_actual_hours,
0 scheduled_util_hours,
0 provisional_hours,
0 unassigned_hours,
0 conf_billable_hours,
0 conf_nonbillable_hours,
0 prov_billable_hours,
0 prov_nonbillable_hours,
0 training_hours,
0 expected_hours,
0 expected_util_hours,
0 expected_total_util_hours,
0 exp_reduce_capacity_hours,
0 actual_hours,
0 actual_util_hours,
0 act_reduce_capacity_hours,
0 prov_util_hours,
0 exp_ac_util_hours,
0 exp_sch_util_hours,
0 exp_ac_actual_hours,
0 exp_sch_actual_hours,
0 exp_ac_red_capacity_hours,
0 exp_sch_red_capacity_hours,
0 prior_scheduled_hours,
0 prior_capacity_hours,
0 prior_red_capacity_hours,
0 prior_total_actual_hours,
0 prior_sch_util_hours,
0 prior_conf_billable_hours,
0 prior_conf_nonbillable_hours,
0 prior_actual_util_hours,
0 prior_actual_hours
FROM PJI_PMV_TIME_DIM_TMP
WHERE name <> '-1'
UNION ALL -- added for current year capacity_hours
/* Bug 3515594 */
SELECT
FACT.org_id,
FACT.organization_id,
FACT.time_id,
FACT.time_key,
decode(p_view_by, 'UC', WT.name, '-1') util_category_id,
decode(p_view_by, 'WT', WT.name, '-1') work_type_id,
FACT.job_level_id job_level_id,
0 scheduled_hours,
FACT.capacity_hours capacity_hours,
FACT.sch_reduce_capacity_hours sch_reduce_capacity_hours,
FACT.total_actual_hours total_actual_hours,
0 scheduled_util_hours,
0 provisional_hours,
0 unassigned_hours,
0 conf_billable_hours,
0 conf_nonbillable_hours,
0 prov_billable_hours,
0 prov_nonbillable_hours,
0 training_hours,
0 expected_hours,
0 expected_util_hours,
0 expected_total_util_hours,
FACT.exp_reduce_capacity_hours exp_reduce_capacity_hours,
0 actual_hours,
0 actual_util_hours,
FACT.act_reduce_capacity_hours act_reduce_capacity_hours,
0 prov_util_hours,
0 exp_ac_util_hours,
0 exp_sch_util_hours,
0 exp_ac_actual_hours,
0 exp_sch_actual_hours,
FACT.exp_ac_red_capacity_hours exp_ac_red_capacity_hours,
FACT.exp_sch_red_capacity_hours exp_sch_red_capacity_hours,
0 prior_scheduled_hours,
0 prior_capacity_hours,
0 prior_red_capacity_hours,
0 prior_total_actual_hours,
0 prior_sch_util_hours,
0 prior_conf_billable_hours,
0 prior_conf_nonbillable_hours,
0 prior_actual_util_hours,
0 prior_actual_hours
FROM
(SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
job.name job_level_id,
fct.capacity_hrs / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
capacity_hours,
fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
total_actual_hours,
fct.reduce_capacity_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
sch_reduce_capacity_hours,
decode(time.amount_type, 0, reduce_capacity_hrs_a, reduce_capacity_hrs_s)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_reduce_capacity_hours,
fct.reduce_capacity_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
act_reduce_capacity_hours,
decode(time.amount_type, 0, reduce_capacity_hrs_a,0)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_ac_red_capacity_hours,
decode(time.amount_type, 1, reduce_capacity_hrs_s,0)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_sch_red_capacity_hours
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_PMV_JB_DIM_TMP job,
PJI_RM_JOB_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.job_id = job.id
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND hou.id = imp.org_id ) FACT,
(SELECT distinct WT.name
FROM PJI_PMV_WT_DIM_TMP wt ) WT
UNION ALL -- added for prior year capacity_hours
/* Bug 3515594 */
SELECT
FACT.org_id,
FACT.organization_id,
FACT.time_id,
FACT.time_key,
decode(p_view_by, 'UC', WT.name, '-1') util_category_id,
decode(p_view_by, 'WT', WT.name, '-1') work_type_id,
FACT.job_level_id job_level_id,
0 scheduled_hours,
0 capacity_hours,
0 sch_reduce_capacity_hours,
0 total_actual_hours,
0 scheduled_util_hours,
0 provisional_hours,
0 unassigned_hours,
0 conf_billable_hours,
0 conf_nonbillable_hours,
0 prov_billable_hours,
0 prov_nonbillable_hours,
0 training_hours,
0 expected_hours,
0 expected_util_hours,
0 expected_total_util_hours,
0 exp_reduce_capacity_hours,
0 actual_hours,
0 actual_util_hours,
0 act_reduce_capacity_hours,
0 prov_util_hours,
0 exp_ac_util_hours,
0 exp_sch_util_hours,
0 exp_ac_actual_hours,
0 exp_sch_actual_hours,
0 exp_ac_red_capacity_hours,
0 exp_sch_red_capacity_hours,
0 prior_scheduled_hours,
FACT.capacity_hours prior_capacity_hours,
FACT.prior_red_capacity_hours prior_red_capacity_hours,
FACT.total_actual_hours prior_total_actual_hours,
0 prior_sch_util_hours,
0 prior_conf_billable_hours,
0 prior_conf_nonbillable_hours,
0 prior_actual_util_hours,
0 prior_actual_hours
FROM
(SELECT /*+ ORDERED */
hou.name org_id,
horg.name organization_id,
time.name time_id,
DECODE(p_view_by, 'TM', time.id, -1) time_key,
job.name job_level_id,
fct.capacity_hrs / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
capacity_hours,
fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
total_actual_hours,
fct.reduce_capacity_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_red_capacity_hours
FROM PJI_PMV_ORGZ_DIM_TMP horg,
PJI_PMV_TIME_DIM_TMP time,
PJI_PMV_JB_DIM_TMP job,
PJI_RM_JOB_F_MV fct,
PJI_PMV_ORG_DIM_TMP hou,
PA_IMPLEMENTATIONS_ALL imp
WHERE fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.prior_id
AND fct.period_type_id = time.period_type
AND fct.job_id = job.id
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.prior_id is not null
AND hou.id = imp.org_id ) FACT,
(SELECT distinct WT.name
FROM PJI_PMV_WT_DIM_TMP wt ) WT
)
GROUP BY org_id,
organization_id,
time_id,
time_key,
util_category_id,
work_type_id,
job_level_id;
l_u4_tbl.DELETE(l_Top_Org_Index);
,P_SELECT_LIST =>
' FACT.RESOURCE_NAME "VIEWBY",
FACT.ACTUAL_HOURS "PJI_REP_MSR_2",
FACT.CAPACITY_HOURS "PJI_REP_MSR_3",
FACT.MISSING_HOURS "PJI_REP_MSR_4",
FACT.BILLABLE_HOURS "PJI_REP_MSR_5",
FACT.NONBILLABLE_HOURS "PJI_REP_MSR_6",
FACT.TRAINING_HOURS "PJI_REP_MSR_7",
FACT.ACT_UTIL_PERCENT "PJI_REP_MSR_8",
FACT.BILL_UTIL_PERCENT "PJI_REP_MSR_9",
FACT.NONBILL_UTIL_PERCENT "PJI_REP_MSR_10",
FACT.TRAINING_PERCENT "PJI_REP_MSR_11",
FACT.ACTUAL_WEIGHTED_HOURS "PJI_REP_MSR_20",
FACT.BILLABLE_WEIGHTED_HOURS "PJI_REP_MSR_21",
FACT.UTIL_PERCENT_DENOM_HOURS "PJI_REP_MSR_22",
FACT.RESOURCE_ID "PJI_REP_MSR_24",
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" '
,P_SQL_STATEMENT => x_PMV_Sql
,P_PMV_OUTPUT => x_PMV_Output,
P_REGION_CODE => 'PJI_REP_U6',
P_PLSQL_DRIVER => 'PJI_PMV_UTLZ.PLSQLDriver_PJI_REP_U6',
P_PLSQL_DRIVER_PARAMS => '<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, ' ||
'<>');
SELECT fnd_profile.value('PA_ORG_UTIL_DEF_CALC_METHOD')
INTO l_util_percent_denom_prof
from dual;
select report_labor_units
into l_labor_unit
from pji_system_settings;
SELECT PJI_REP_U6( resource_name
, resource_id
, SUM(actual_hours)
, SUM(capacity_hours)
, SUM(missing_hours)
, SUM(billable_hours)
, SUM(nonbillable_hours)
, SUM(training_hours)
, SUM(actual_weighted_hours)
, SUM(billable_weighted_hours)
, SUM(nonbillable_weighted_hours)
, SUM(DECODE(l_util_percent_denom_prof,'CAPACITY',capacity_hours,actual_hours)) ,
null,null,null,null,null,null,
null,null,null,null,null,null,
null,null)
/* Bug 3515594 */
BULK COLLECT INTO l_u6_tbl
FROM
(
SELECT /*+ ORDERED */
resd.resource_name resource_name
,fct.person_id resource_id
,fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) actual_hours
,(fct.capacity_hrs-NVL(fct.reduce_capacity_hrs_a,0))
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) capacity_hours
,fct.missing_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) missing_hours
,fct.bill_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) billable_hours
,(fct.total_hrs_a-bill_hrs_a)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) nonbillable_hours
,fct.training_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) training_hours
,fct.total_wtd_org_hrs_a
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) actual_weighted_hours
,fct.bill_wtd_org_hrs_a
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) billable_weighted_hours
,(fct.total_wtd_org_hrs_a-fct.bill_wtd_org_hrs_a)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) nonbillable_weighted_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pji_pmv_time_dim_tmp time
,pji_rm_res_f fct
,pji_pmv_org_dim_tmp hou
,pa_resources_denorm resd
,pa_implementations_all imp
WHERE
fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND fct.person_id = resd.person_id
AND resd.resource_organization_id = horg.id
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
AND hou.id = imp.org_id
UNION ALL
SELECT
resd.resource_name resource_name
,resd.person_id resource_id
,0 actual_hours
,0 capacity_hours
,0 missing_hours
,0 billable_hours
,0 nonbillable_hours
,0 training_hours
,0 actual_weighted_hours
,0 billable_weighted_hours
,0 nonbillable_weighted_hours
FROM
pa_resources_denorm resd
,pji_pmv_orgz_dim_tmp horg
WHERE
resd.resource_organization_id = horg.id
AND resd.utilization_flag = 'Y'
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
)
GROUP BY resource_name, resource_id;
SELECT PJI_REP_U6( resource_name
, resource_id
, SUM(actual_hours)
, SUM(capacity_hours)
, SUM(missing_hours)
, SUM(billable_hours)
, SUM(nonbillable_hours)
, SUM(training_hours)
, SUM(actual_weighted_hours)
, SUM(billable_weighted_hours)
, SUM(nonbillable_weighted_hours)
, SUM(DECODE(l_util_percent_denom_prof,'CAPACITY',capacity_hours,actual_hours)) ,
null,null,null,null,null,null,
null,null,null,null,null,null,
null,null)
/* Bug 3515594 */
BULK COLLECT INTO l_u6_tbl
FROM
(
SELECT /*+ ORDERED */
resd.resource_name resource_name
,fct.person_id resource_id
,fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) actual_hours
,(fct.capacity_hrs-NVL(fct.reduce_capacity_hrs_a,0))
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) capacity_hours
,fct.missing_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) missing_hours
,fct.bill_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) billable_hours
,(fct.total_hrs_a-bill_hrs_a)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) nonbillable_hours
,fct.training_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) training_hours
,fct.total_wtd_org_hrs_a
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) actual_weighted_hours
,fct.bill_wtd_org_hrs_a
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) billable_weighted_hours
,(fct.total_wtd_org_hrs_a-fct.bill_wtd_org_hrs_a)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) nonbillable_weighted_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pji_pmv_time_dim_tmp time
,pji_pmv_jb_dim_tmp jbt
,pji_rm_res_f fct
,pji_pmv_org_dim_tmp hou
,pa_resources_denorm resd
,pa_implementations_all imp
WHERE
fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND fct.job_id = jbt.id
AND fct.person_id = resd.person_id
AND resd.resource_organization_id = horg.id
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
AND hou.id = imp.org_id
UNION ALL
SELECT
resource_name resource_name
,resd.person_id resource_id
,0 actual_hours
,0 capacity_hours
,0 missing_hours
,0 billable_hours
,0 nonbillable_hours
,0 training_hours
,0 actual_weighted_hours
,0 billable_weighted_hours
,0 nonbillable_weighted_hours
FROM
pa_resources_denorm resd
,pji_pmv_orgz_dim_tmp horg
,pji_pmv_jb_dim_tmp jbt
WHERE
resd.resource_organization_id = horg.id
AND resd.utilization_flag = 'Y'
AND resd.job_id = jbt.id
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
)
GROUP BY resource_name, resource_id;
SELECT PJI_REP_U6 ( resource_name
, resource_id
, SUM(actual_hours)
, SUM(capacity_hours-act_reduce_capacity_hours)
, SUM(missing_hours)
, SUM(billable_hours)
, SUM(nonbillable_hours)
, SUM(training_hours)
, SUM(actual_weighted_hours)
, SUM(billable_weighted_hours)
, SUM(nonbillable_weighted_hours)
, SUM(DECODE(l_util_percent_denom_prof,'CAPACITY',capacity_hours-act_reduce_capacity_hours,
actual_hours)) ,
null,null,null,null,null,null,
null,null,null,null,null,null,
null,null)
/* Bug 3515594 */
BULK COLLECT INTO l_u6_tbl
FROM
(
SELECT /*+ ORDERED */
resource_name resource_name
,fct.person_id resource_id
,fct.total_hrs_a actual_hours
,0 capacity_hours
,0 act_reduce_capacity_hours
,null missing_hours
,fct.bill_hrs_a
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) billable_hours
,(fct.total_hrs_a-fct.bill_hrs_a)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) nonbillable_hours
,decode(wtb.training_flag,'Y',fct.total_hrs_a, 0)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) training_hours
,fct.total_hrs_a * (wtb.org_utilization_percentage/100)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) actual_weighted_hours
,fct.bill_hrs_a * (wtb.org_utilization_percentage/100)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) billable_weighted_hours
,(fct.total_hrs_a-fct.bill_hrs_a)
*wtb.org_utilization_percentage/100 / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
nonbillable_weighted_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pji_pmv_time_dim_tmp time
,pji_pmv_wt_dim_tmp wt
,pji_rm_res_wt_f fct
,pji_pmv_org_dim_tmp hou
,pa_work_types_b wtb
,pa_resources_denorm resd
,pa_implementations_all imp
WHERE
fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND fct.work_type_id = wt.id
AND wtb.work_type_id = wt.id
AND fct.person_id = resd.person_id
AND resd.resource_organization_id = horg.id
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
AND hou.id = imp.org_id
UNION ALL
SELECT
resource_name resource_name
,resd.person_id resource_id
,0 actual_hours
,0 capacity_hours
,0 act_reduce_capacity_hours
,null missing_hours
,0 billable_hours
,0 nonbillable_hours
,0 training_hours
,0 actual_weighted_hours
,0 billable_weighted_hours
,0 nonbillable_weighted_hours
FROM
pa_resources_denorm resd
,pji_pmv_orgz_dim_tmp horg
WHERE
resd.resource_organization_id = horg.id
AND resd.utilization_flag = 'Y'
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
UNION ALL -- added for current year capacity_hours
SELECT /*+ ORDERED */
resource_name resource_name
,resd.person_id resource_id
,0 actual_hours
,capacity_hrs / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
capacity_hours
,reduce_capacity_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
act_reduce_capacity_hours
,missing_hrs_a missing_hours
,0 billable_hours
,0 nonbillable_hours
,0 training_hours
,0 actual_weighted_hours
,0 billable_weighted_hours
,0 nonbillable_weighted_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pji_pmv_time_dim_tmp time
,pji_rm_res_f fct
,pji_pmv_org_dim_tmp hou
,pa_resources_denorm resd
,pa_implementations_all imp
WHERE
fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND fct.person_id = resd.person_id
AND resd.resource_organization_id = horg.id
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
AND hou.id = imp.org_id
)
GROUP BY resource_name, resource_id;
SELECT PJI_REP_U6 ( resource_name
, resource_id
, SUM(actual_hours)
, SUM(capacity_hours-act_reduce_capacity_hours)
, SUM(missing_hours)
, SUM(billable_hours)
, SUM(nonbillable_hours)
, SUM(training_hours)
, SUM(actual_weighted_hours)
, SUM(billable_weighted_hours)
, SUM(nonbillable_weighted_hours)
, SUM(DECODE(l_util_percent_denom_prof,'CAPACITY',capacity_hours-act_reduce_capacity_hours,
actual_hours)) ,
null,null,null,null,null,null,
null,null,null,null,null,null,
null,null)
/* Bug 3515594 */
BULK COLLECT INTO l_u6_tbl
FROM
(
SELECT /*+ ORDERED */
resd.resource_name resource_name
,fct.person_id resource_id
,fct.total_hrs_a actual_hours
,0 capacity_hours
,0 act_reduce_capacity_hours
,null missing_hours
,fct.bill_hrs_a
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) billable_hours
,(fct.total_hrs_a - fct.bill_hrs_a)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) nonbillable_hours
,decode(wtb.training_flag,'Y',fct.total_hrs_a, 0)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) training_hours
,fct.total_hrs_a * (wtb.org_utilization_percentage/100)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) actual_weighted_hours
,fct.bill_hrs_a * (wtb.org_utilization_percentage/100)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) billable_weighted_hours
,(fct.total_hrs_a - fct.bill_hrs_a)
*wtb.org_utilization_percentage/100 / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
nonbillable_weighted_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pji_pmv_time_dim_tmp time
,pji_pmv_wt_dim_tmp wt
,pji_rm_res_wt_f fct
,pji_pmv_jb_dim_tmp jbt
,pji_pmv_org_dim_tmp hou
,pa_work_types_b wtb
,pa_resources_denorm resd
,pa_implementations_all imp
WHERE
fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND fct.work_type_id = wt.id
AND wtb.work_type_id = wt.id
AND fct.job_id = jbt.id
AND fct.person_id = resd.person_id
AND resd.resource_organization_id = horg.id
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
AND hou.id = imp.org_id
UNION ALL
SELECT
resd.resource_name resource_name
,resd.person_id resource_id
,0 actual_hours
,0 capacity_hours
,0 act_reduce_capacity_hours
,null missing_hours
,0 billable_hours
,0 nonbillable_hours
,0 training_hours
,0 actual_weighted_hours
,0 billable_weighted_hours
,0 nonbillable_weighted_hours
FROM
pa_resources_denorm resd
,pji_pmv_orgz_dim_tmp horg
,pji_pmv_jb_dim_tmp jbt
WHERE
resd.resource_organization_id = horg.id
AND resd.utilization_flag = 'Y'
AND resd.job_id = jbt.id
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
UNION ALL -- added for current year capacity_hours
/* Bug 3515594 */
SELECT /*+ ORDERED */
resource_name resource_name
,resd.person_id resource_id
,0 actual_hours
,capacity_hrs / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
capacity_hours
,reduce_capacity_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
act_reduce_capacity_hours
,missing_hrs_a missing_hours
,0 billable_hours
,0 nonbillable_hours
,0 training_hours
,0 actual_weighted_hours
,0 billable_weighted_hours
,0 nonbillable_weighted_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pji_pmv_time_dim_tmp time
,pji_pmv_jb_dim_tmp jbt
,pji_rm_res_f fct
,pji_pmv_org_dim_tmp hou
,pa_resources_denorm resd
,pa_implementations_all imp
WHERE
fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND fct.person_id = resd.person_id
AND fct.job_id = jbt.id
AND resd.resource_organization_id = horg.id
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
AND hou.id = imp.org_id
)
GROUP BY resource_name, resource_id;
,P_SELECT_LIST =>
' FACT.RESOURCE_NAME "VIEWBY",
FACT.SCHEDULED_HOURS "PJI_REP_MSR_2",
FACT.CAPACITY_HOURS "PJI_REP_MSR_3",
FACT.CONFIRMED_BILLABLE_HOURS "PJI_REP_MSR_4",
FACT.CONFIRMED_NONBILLABLE_HOURS "PJI_REP_MSR_5",
FACT.PROVISIONAL_BILLABLE_HOURS "PJI_REP_MSR_6",
FACT.PROVISIONAL_NONBILLABLE_HOURS "PJI_REP_MSR_7",
FACT.TRAINING_HOURS "PJI_REP_MSR_12",
FACT.SCH_UTIL_PERCENT "PJI_REP_MSR_8",
FACT.BILL_UTIL_PERCENT "PJI_REP_MSR_9",
FACT.NONBILL_UTIL_PERCENT "PJI_REP_MSR_10",
FACT.UNASSIGNED_PERCENT "PJI_REP_MSR_13",
FACT.TRAINING_PERCENT "PJI_REP_MSR_11",
FACT.CONFIRMED_WEIGHTED_HOURS "PJI_REP_MSR_20",
FACT.BILLABLE_CONF_WEIGHTED_HOURS "PJI_REP_MSR_21",
FACT.UTIL_PERCENT_DENOM_HOURS "PJI_REP_MSR_22",
FACT.UNASSIGNED_HOURS "PJI_REP_MSR_23",
FACT.RESOURCE_ID "PJI_REP_MSR_24",
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" '
,P_SQL_STATEMENT => x_PMV_Sql
,P_PMV_OUTPUT => x_PMV_Output,
P_REGION_CODE => 'PJI_REP_U7',
P_PLSQL_DRIVER => 'PJI_PMV_UTLZ.PLSQLDriver_PJI_REP_U7',
P_PLSQL_DRIVER_PARAMS => '<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, ' ||
'<>');
SELECT fnd_profile.value('PA_ORG_UTIL_DEF_CALC_METHOD')
INTO l_util_percent_denom_prof
from dual;
select report_labor_units
into l_labor_unit
from pji_system_settings;
SELECT PJI_REP_U7( resource_name
, resource_id
, SUM(scheduled_hours)
, SUM(capacity_hours)
, SUM(confirmed_billable_hours)
, SUM(confirmed_nonbillable_hours)
, SUM(provisional_billable_hours)
, SUM(provisional_nonbillable_hours)
, SUM(training_hours)
, SUM(confirmed_weighted_hours)
, SUM(billable_conf_weighted_hours)
, SUM(unassigned_hours)
, SUM(DECODE(l_util_percent_denom_prof,'CAPACITY',
capacity_hours,actual_hours)),
null,null,null,null,null,null,
null,null,null,null,null,null,
null,null,null,null,null)
/* Bug 3515594 */
BULK COLLECT INTO l_u7_tbl
FROM
(
SELECT /*+ ORDERED */
resd.resource_name resource_name
,fct.person_id resource_id
,fct.conf_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) scheduled_hours
,(fct.capacity_hrs-NVL(fct.reduce_capacity_hrs_s,0))
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) capacity_hours
,fct.conf_bill_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
confirmed_billable_hours
,(fct.conf_hrs_s-conf_bill_hrs_s) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
confirmed_nonbillable_hours
,fct.prov_bill_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
provisional_billable_hours
,(fct.prov_hrs_s-fct.prov_bill_hrs_s) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
provisional_nonbillable_hours
,fct.training_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) training_hours
,fct.conf_wtd_org_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
confirmed_weighted_hours
,fct.conf_bill_wtd_org_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
billable_conf_weighted_hours
,fct.unassigned_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) unassigned_hours
,fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) actual_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pji_pmv_time_dim_tmp time
,pji_rm_res_f fct
,pji_pmv_org_dim_tmp hou
,pa_resources_denorm resd
,pa_implementations_all imp
WHERE
fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND fct.person_id = resd.person_id
AND resd.resource_organization_id = horg.id
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
AND hou.id = imp.org_id
UNION ALL
SELECT
resd.resource_name resource_name
,resd.person_id resource_id
,0 scheduled_hours
,0 capacity_hours
,0 confirmed_billable_hours
,0 confirmed_nonbillable_hours
,0 provisional_billable_hours
,0 provisional_nonbillable_hours
,0 training_hours
,0 confirmed_weighted_hours
,0 billable_conf_weighted_hours
,0 unassigned_hours
,0 actual_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pa_resources_denorm resd
WHERE
resd.resource_organization_id = horg.id
AND resd.utilization_flag = 'Y'
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
)
GROUP BY resource_name, resource_id;
SELECT PJI_REP_U7( resource_name
, resource_id
, SUM(scheduled_hours)
, SUM(capacity_hours)
, SUM(confirmed_billable_hours)
, SUM(confirmed_nonbillable_hours)
, SUM(provisional_billable_hours)
, SUM(provisional_nonbillable_hours)
, SUM(training_hours)
, SUM(confirmed_weighted_hours)
, SUM(billable_conf_weighted_hours)
, SUM(unassigned_hours)
, SUM(DECODE(l_util_percent_denom_prof,'CAPACITY',capacity_hours,
actual_hours)) ,
null,null,null,null,null,null,
null,null,null,null,null,null,
null,null,null,null,null)
/* Bug 3515594 */
BULK COLLECT INTO l_u7_tbl
FROM
(
SELECT /*+ ORDERED */
resd.resource_name resource_name
,fct.person_id resource_id
,fct.conf_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) scheduled_hours
,(fct.capacity_hrs-NVL(fct.reduce_capacity_hrs_s,0))
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) capacity_hours
,fct.conf_bill_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
confirmed_billable_hours
,(fct.conf_hrs_s-conf_bill_hrs_s) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
confirmed_nonbillable_hours
,fct.prov_bill_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
provisional_billable_hours
,(fct.prov_hrs_s-fct.prov_bill_hrs_s) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
provisional_nonbillable_hours
,fct.training_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) training_hours
,fct.conf_wtd_org_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
confirmed_weighted_hours
,fct.conf_bill_wtd_org_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
billable_conf_weighted_hours
,fct.unassigned_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) unassigned_hours
,fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) actual_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pji_pmv_time_dim_tmp time
,pji_pmv_jb_dim_tmp jbt
,pji_rm_res_f fct
,pji_pmv_org_dim_tmp hou
,pa_resources_denorm resd
,pa_implementations_all imp
WHERE
fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND fct.job_id = jbt.id
AND fct.person_id = resd.person_id
AND resd.resource_organization_id = horg.id
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
AND hou.id = imp.org_id
UNION ALL
SELECT
resd.resource_name resource_name
,resd.person_id resource_id
,0 scheduled_hours
,0 capacity_hours
,0 confirmed_billable_hours
,0 confirmed_nonbillable_hours
,0 provisional_billable_hours
,0 provisional_nonbillable_hours
,0 training_hours
,0 confirmed_weighted_hours
,0 billable_conf_weighted_hours
,0 unassigned_hours
,0 actual_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pji_pmv_jb_dim_tmp jbt
,pa_resources_denorm resd
WHERE
resd.resource_organization_id = horg.id
AND resd.utilization_flag = 'Y'
AND resd.job_id = jbt.id
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
)
GROUP BY resource_name, resource_id;
SELECT PJI_REP_U7( resource_name
, resource_id
, SUM(scheduled_hours)
, SUM(capacity_hours-sch_reduce_capacity_hours)
, SUM(confirmed_billable_hours)
, SUM(confirmed_nonbillable_hours)
, SUM(provisional_billable_hours)
, SUM(provisional_nonbillable_hours)
, SUM(training_hours)
, SUM(confirmed_weighted_hours)
, SUM(billable_conf_weighted_hours)
, SUM(unassigned_hours)
, SUM(DECODE(l_util_percent_denom_prof,'CAPACITY',capacity_hours-sch_reduce_capacity_hours,
actual_hours)) ,
null,null,null,null,null,null,
null,null,null,null,null,null,
null,null,null,null,null)
/* Bug 3515594 */
BULK COLLECT INTO l_u7_tbl
FROM
(
SELECT /*+ ORDERED */
resd.resource_name resource_name
,fct.person_id resource_id
,fct.conf_hrs_s scheduled_hours
,0 capacity_hours
,0 sch_reduce_capacity_hours
,decode(wtb.billable_capitalizable_flag,'Y',fct.conf_hrs_s, 0)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) confirmed_billable_hours
,(fct.conf_hrs_s-decode(wtb.billable_capitalizable_flag,'Y',fct.conf_hrs_s, 0))
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) confirmed_nonbillable_hours
,decode(wtb.billable_capitalizable_flag,'Y',prov_hrs_s,0)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) provisional_billable_hours
,(prov_hrs_s-decode(wtb.billable_capitalizable_flag,'Y',prov_hrs_s,0))
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) provisional_nonbillable_hours
,decode(wtb.training_flag,'Y',fct.conf_hrs_s, 0)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) training_hours
,fct.conf_hrs_s * wtb.org_utilization_percentage/100
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) confirmed_weighted_hours
,decode(wtb.billable_capitalizable_flag,'Y',fct.conf_hrs_s, 0)*wtb.org_utilization_percentage/100
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) billable_conf_weighted_hours
,unassigned_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
unassigned_hours
,fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) actual_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pji_pmv_time_dim_tmp time
,pji_pmv_wt_dim_tmp wt
,pji_rm_res_wt_f fct
,pji_pmv_org_dim_tmp hou
,pa_work_types_b wtb
,pa_resources_denorm resd
,pa_implementations_all imp
WHERE
fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND fct.work_type_id = wt.id
AND wtb.work_type_id = wt.id
AND fct.person_id = resd.person_id
AND resd.resource_organization_id = horg.id
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
AND hou.id = imp.org_id
UNION ALL
SELECT
resd.resource_name resource_name
,resd.person_id resource_id
,0 scheduled_hours
,0 capacity_hours
,0 sch_reduce_capacity_hours
,0 confirmed_billable_hours
,0 confirmed_nonbillable_hours
,0 provisional_billable_hours
,0 provisional_nonbillable_hours
,0 training_hours
,0 confirmed_weighted_hours
,0 billable_conf_weighted_hours
,0 unassigned_hours
,0 actual_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pa_resources_denorm resd
WHERE
resd.resource_organization_id = horg.id
AND resd.utilization_flag = 'Y'
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
UNION ALL -- added for current year capacity_hours
/* Bug 3515594 */
SELECT /*+ ORDERED */
resd.resource_name resource_name
,resd.person_id resource_id
,0 scheduled_hours
,capacity_hrs / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
capacity_hours
,reduce_capacity_hrs_s / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
sch_reduce_capacity_hours
,0 confirmed_billable_hours
,0 confirmed_nonbillable_hours
,0 provisional_billable_hours
,0 provisional_nonbillable_hours
,0 training_hours
,0 confirmed_weighted_hours
,0 billable_conf_weighted_hours
,0 unassigned_hours
,0 actual_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pji_pmv_time_dim_tmp time
,pji_rm_res_f fct
,pji_pmv_org_dim_tmp hou
,pa_resources_denorm resd
,pa_implementations_all imp
WHERE
fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND fct.person_id = resd.person_id
AND resd.resource_organization_id = horg.id
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
AND hou.id = imp.org_id
)
GROUP BY resource_name, resource_id;
SELECT PJI_REP_U7( resource_name
, resource_id
, SUM(scheduled_hours)
, SUM(capacity_hours-sch_reduce_capacity_hours)
, SUM(confirmed_billable_hours)
, SUM(confirmed_nonbillable_hours)
, SUM(provisional_billable_hours)
, SUM(provisional_nonbillable_hours)
, SUM(training_hours)
, SUM(confirmed_weighted_hours)
, SUM(billable_conf_weighted_hours)
, SUM(unassigned_hours)
, SUM(DECODE(l_util_percent_denom_prof,'CAPACITY',capacity_hours-sch_reduce_capacity_hours,
actual_hours)) ,
null,null,null,null,null,null,
null,null,null,null,null,null,
null,null,null,null,null)
/* Bug 3515594 */
BULK COLLECT INTO l_u7_tbl
FROM
(
SELECT /*+ ORDERED */
resd.resource_name resource_name
,fct.person_id resource_id
,fct.conf_hrs_s scheduled_hours
,0 capacity_hours
,0 sch_reduce_capacity_hours
,decode(wtb.billable_capitalizable_flag,'Y',fct.conf_hrs_s, 0)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) confirmed_billable_hours
,(fct.conf_hrs_s-decode(wtb.billable_capitalizable_flag,'Y',fct.conf_hrs_s, 0))
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) confirmed_nonbillable_hours
,decode(wtb.billable_capitalizable_flag,'Y',prov_hrs_s,0)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) provisional_billable_hours
,(prov_hrs_s-decode(wtb.billable_capitalizable_flag,'Y',prov_hrs_s,0))
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) provisional_nonbillable_hours
,decode(wtb.training_flag,'Y',fct.conf_hrs_s, 0)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) training_hours
,fct.conf_hrs_s * wtb.org_utilization_percentage/100
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) confirmed_weighted_hours
,decode(wtb.billable_capitalizable_flag,'Y',fct.conf_hrs_s, 0)*wtb.org_utilization_percentage/100
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) billable_conf_weighted_hours
,unassigned_hrs_s * decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
unassigned_hours
,fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) actual_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pji_pmv_time_dim_tmp time
,pji_pmv_wt_dim_tmp wt
,pji_rm_res_wt_f fct
,pji_pmv_jb_dim_tmp jbt
,pji_pmv_org_dim_tmp hou
,pa_work_types_b wtb
,pa_resources_denorm resd
,pa_implementations_all imp
WHERE
fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND fct.work_type_id = wt.id
AND wtb.work_type_id = wt.id
AND fct.job_id = jbt.id
AND fct.person_id = resd.person_id
AND resd.resource_organization_id = horg.id
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
AND hou.id = imp.org_id
UNION ALL
SELECT
resd.resource_name resource_name
,resd.person_id resource_id
,0 scheduled_hours
,0 capacity_hours
,0 sch_reduce_capacity_hours
,0 confirmed_billable_hours
,0 confirmed_nonbillable_hours
,0 provisional_billable_hours
,0 provisional_nonbillable_hours
,0 training_hours
,0 confirmed_weighted_hours
,0 billable_conf_weighted_hours
,0 unassigned_hours
,0 actual_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pji_pmv_jb_dim_tmp jbt
,pa_resources_denorm resd
WHERE
resd.resource_organization_id = horg.id
AND resd.utilization_flag = 'Y'
AND resd.job_id = jbt.id
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
UNION ALL -- added for current year capacity_hours
/* Bug 3515594 */
SELECT /*+ ORDERED */
resd.resource_name resource_name
,resd.person_id resource_id
,0 scheduled_hours
,capacity_hrs * decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
capacity_hours
,reduce_capacity_hrs_s * decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
sch_reduce_capacity_hours
,0 confirmed_billable_hours
,0 confirmed_nonbillable_hours
,0 provisional_billable_hours
,0 provisional_nonbillable_hours
,0 training_hours
,0 confirmed_weighted_hours
,0 billable_conf_weighted_hours
,0 unassigned_hours
,0 actual_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pji_pmv_time_dim_tmp time
,pji_pmv_jb_dim_tmp jbt
,pji_rm_res_f fct
,pji_pmv_org_dim_tmp hou
,pa_resources_denorm resd
,pa_implementations_all imp
WHERE
fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND fct.job_id = jbt.id
AND fct.person_id = resd.person_id
AND resd.resource_organization_id = horg.id
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
AND hou.id = imp.org_id
)
GROUP BY resource_name, resource_id;
,P_SELECT_LIST =>
' FACT.RESOURCE_NAME "VIEWBY",
FACT.EXPECTED_HOURS "PJI_REP_MSR_2",
FACT.CAPACITY_HOURS "PJI_REP_MSR_3",
FACT.MISSING_HOURS "PJI_REP_MSR_4",
FACT.ACT_UTIL_PERCENT "PJI_REP_MSR_5",
FACT.SCH_UTIL_PERCENT "PJI_REP_MSR_6",
FACT.EXP_UTIL_PERCENT "PJI_REP_MSR_7",
FACT.PROV_SCH_UTIL_PERCENT "PJI_REP_MSR_12",
FACT.EXP_TOTAL_UTIL_PERCENT "PJI_REP_MSR_8",
FACT.PRIOR_UTIL_PERCENT "PJI_REP_MSR_9",
FACT.EXP_BILL_UTIL_PERCENT "PJI_REP_MSR_10",
FACT.EXP_NONBILL_UTIL_PERCENT "PJI_REP_MSR_13",
FACT.EXP_TRAINING_PERCENT "PJI_REP_MSR_11",
FACT.SCH_CONF_WEIGHTED_HOURS "PJI_REP_MSR_21",
FACT.SCH_PROV_WEIGHTED_HOURS "PJI_REP_MSR_23",
FACT.EXPECTED_BILL_WEIGHTED_HOURS "PJI_REP_MSR_14",
FACT.EXPECTED_TRAINING_HOURS "PJI_REP_MSR_15",
FACT.PRIOR_ACTUAL_WEIGHTED_HOURS "PJI_REP_MSR_16",
FACT.PRIOR_CAPACITY_HOURS "PJI_REP_MSR_17",
FACT.UTIL_PERCENT_DENOM_HOURS "PJI_REP_MSR_22",
FACT.PRIOR_UTIL_PERCENT_DENOM_HOURS "PJI_REP_MSR_18",
FACT.ACTUAL_WEIGHTED_HOURS "PJI_REP_MSR_19",
FACT.RESOURCE_ID "PJI_REP_MSR_24",
FACT.EXP_AC_UTIL_PERCENT_DENOM "PJI_REP_MSR_25",
FACT.EXP_SCH_UTIL_PERCENT_DENOM "PJI_REP_MSR_26",
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" '
,P_SQL_STATEMENT => x_PMV_Sql
,P_PMV_OUTPUT => x_PMV_Output,
P_REGION_CODE => 'PJI_REP_U8',
P_PLSQL_DRIVER => 'PJI_PMV_UTLZ.PLSQLDriver_PJI_REP_U8',
P_PLSQL_DRIVER_PARAMS => '<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, ' ||
'<>');
SELECT fnd_profile.value('PA_ORG_UTIL_DEF_CALC_METHOD')
INTO l_util_percent_denom_prof
from dual;
select report_labor_units
into l_labor_unit
from pji_system_settings;
SELECT PJI_REP_U8( resource_name
,resource_id
,SUM(expected_hours)
,SUM(capacity_hours)
,SUM(missing_hours)
,SUM(actual_weighted_hours)
,SUM(sch_conf_weighted_hours)
,SUM(sch_prov_weighted_hours)
,SUM(expected_bill_weighted_hours)
,SUM(expected_training_hours)
,SUM(prior_actual_weighted_hours)
,SUM(prior_capacity_hours)
,SUM(DECODE(l_util_percent_denom_prof,'CAPACITY',
capacity_hours,actual_hours))
,SUM(decode(l_util_percent_denom_prof, 'CAPACITY',
exp_ac_capacity_hours, exp_ac_actual_hours))
,SUM(decode(l_util_percent_denom_prof, 'CAPACITY',
exp_sch_capacity_hours, exp_sch_actual_hours))
,SUM(DECODE(l_util_percent_denom_prof,'CAPACITY',
prior_capacity_hours,prior_actual_hours)),
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_u8_tbl
/* Bug 3515594 */
FROM
(
-- get current year values
SELECT /*+ ORDERED */
resd.resource_name resource_name
,fct.person_id resource_id
,DECODE(time.amount_type,0,fct.total_hrs_a,fct.conf_hrs_s)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) expected_hours
,(fct.capacity_hrs-DECODE(time.amount_type,0,NVL(fct.reduce_capacity_hrs_a,0),
NVL(fct.reduce_capacity_hrs_s,0)))
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) capacity_hours
,fct.missing_hrs_a
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) missing_hours
,DECODE(time.amount_type,0,fct.total_wtd_org_hrs_a,0)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) actual_weighted_hours
,DECODE(time.amount_type,1,conf_wtd_org_hrs_s,0)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) sch_conf_weighted_hours
,DECODE(time.amount_type,1,prov_wtd_org_hrs_s,0)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) sch_prov_weighted_hours
,DECODE(time.amount_type,0,fct.bill_wtd_org_hrs_a,fct.conf_bill_wtd_org_hrs_s)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) expected_bill_weighted_hours
,DECODE(time.amount_type,0,fct.training_hrs_a,fct.training_hrs_s)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) expected_training_hours
,fct.total_hrs_a * decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
actual_hours
,decode(time.amount_type, 0, fct.capacity_hrs - fct.reduce_capacity_hrs_a, fct.capacity_hrs)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_ac_capacity_hours
,decode(time.amount_type, 0, fct.total_hrs_a, 0)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_ac_actual_hours
,decode(time.amount_type, 0, fct.capacity_hrs, fct.capacity_hrs - fct.reduce_capacity_hrs_s)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_sch_capacity_hours
,decode(time.amount_type, 0, 0, fct.total_hrs_a)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_sch_actual_hours
,0 prior_actual_weighted_hours
,0 prior_capacity_hours
,0 prior_actual_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pji_pmv_time_dim_tmp time
,pji_rm_res_f fct
,pji_pmv_org_dim_tmp hou
,pa_resources_denorm resd
,pa_implementations_all imp
WHERE
fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND fct.person_id = resd.person_id
AND resd.resource_organization_id = horg.id
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
AND hou.id = imp.org_id
-- get prior year values
UNION ALL
/* Bug 3515594 */
SELECT /*+ ORDERED */
resd.resource_name resource_name
,fct.person_id resource_id
,0 expected_hours
,0 capacity_hours
,0 missing_hours
,0 actual_weighted_hours
,0 sch_conf_weighted_hours
,0 sch_prov_weighted_hours
,0 expected_bill_weighted_hours
,0 expected_training_hours
,0 actual_hours
,0 exp_ac_capacity_hours
,0 exp_ac_actual_hours
,0 exp_sch_capacity_hours
,0 exp_sch_actual_hours
,fct.total_wtd_org_hrs_a
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) prior_actual_weighted_hours
,(fct.capacity_hrs-NVL(fct.reduce_capacity_hrs_a,0))
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) prior_capacity_hours
,fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_actual_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pji_pmv_time_dim_tmp time
,pji_rm_res_f fct
,pji_pmv_org_dim_tmp hou
,pa_resources_denorm resd
,pa_implementations_all imp
WHERE
fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.prior_id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.prior_id is not null
AND fct.person_id = resd.person_id
AND resd.resource_organization_id = horg.id
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
AND fct.expenditure_org_id = imp.org_id
-- get the data for the resources who doens't have data
UNION ALL
SELECT
resd.resource_name resource_name
,resd.person_id resource_id
,0 expected_hours
,0 capacity_hours
,0 missing_hours
,0 actual_weighted_hours
,0 sch_conf_weighted_hours
,0 sch_prov_weighted_hours
,0 expected_bill_weighted_hours
,0 expected_training_hours
,0 actual_hours
,0 exp_ac_capacity_hours
,0 exp_ac_actual_hours
,0 exp_sch_capacity_hours
,0 exp_sch_actual_hours
,0 prior_actual_weighted_hours
,0 prior_capacity_hours
,0 prior_actual_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pa_resources_denorm resd
WHERE
resd.resource_organization_id = horg.id
AND resd.utilization_flag = 'Y'
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
)
GROUP BY resource_name, resource_id;
SELECT PJI_REP_U8( resource_name
,resource_id
,SUM(expected_hours)
,SUM(capacity_hours)
,SUM(missing_hours)
,SUM(actual_weighted_hours)
,SUM(sch_conf_weighted_hours)
,SUM(sch_prov_weighted_hours)
,SUM(expected_bill_weighted_hours)
,SUM(expected_training_hours)
,SUM(prior_actual_weighted_hours)
,SUM(prior_capacity_hours)
,SUM(DECODE(l_util_percent_denom_prof,'CAPACITY',
capacity_hours,actual_hours))
,SUM(decode(l_util_percent_denom_prof, 'CAPACITY',
exp_ac_capacity_hours, exp_ac_actual_hours))
,SUM(decode(l_util_percent_denom_prof, 'CAPACITY',
exp_sch_capacity_hours, exp_sch_actual_hours))
,SUM(DECODE(l_util_percent_denom_prof,'CAPACITY',
prior_capacity_hours,prior_actual_hours)),
null,null,null,null,null,null,
null,null,null,null,null,null,
null,null,null,null,null,null,
null,null,null
)
/* Bug 3515594 */
BULK COLLECT INTO l_u8_tbl
FROM
(
-- get current year values
SELECT /*+ ORDERED */
resd.resource_name resource_name
,fct.person_id resource_id
,DECODE(time.amount_type,0,fct.total_hrs_a,fct.conf_hrs_s)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) expected_hours
,(fct.capacity_hrs-DECODE(time.amount_type,0,NVL(fct.reduce_capacity_hrs_a,0),
NVL(fct.reduce_capacity_hrs_s,0)))
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) capacity_hours
,fct.missing_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) missing_hours
,DECODE(time.amount_type,0,fct.total_wtd_org_hrs_a,0)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) actual_weighted_hours
,DECODE(time.amount_type,1,conf_wtd_org_hrs_s,0)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) sch_conf_weighted_hours
,DECODE(time.amount_type,1,prov_wtd_org_hrs_s,0)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) sch_prov_weighted_hours
,DECODE(time.amount_type,0,fct.bill_wtd_org_hrs_a,fct.conf_bill_wtd_org_hrs_s)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) expected_bill_weighted_hours
,DECODE(time.amount_type,0,fct.training_hrs_a,fct.training_hrs_s)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) expected_training_hours
,fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
actual_hours
,decode(time.amount_type, 0, fct.capacity_hrs - fct.reduce_capacity_hrs_a, fct.capacity_hrs)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_ac_capacity_hours
,decode(time.amount_type, 0, fct.total_hrs_a, 0)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_ac_actual_hours
,decode(time.amount_type, 0, fct.capacity_hrs, fct.capacity_hrs - fct.reduce_capacity_hrs_s)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_sch_capacity_hours
,decode(time.amount_type, 0, 0, fct.total_hrs_a)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_sch_actual_hours
,0 prior_actual_weighted_hours
,0 prior_capacity_hours
,0 prior_actual_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pji_pmv_time_dim_tmp time
,pji_pmv_jb_dim_tmp jbt
,pji_rm_res_f fct
,pji_pmv_org_dim_tmp hou
,pa_resources_denorm resd
,pa_implementations_all imp
WHERE
fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND fct.job_id = jbt.id
AND fct.person_id = resd.person_id
AND resd.resource_organization_id = horg.id
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
AND hou.id = imp.org_id
-- get prior year values
UNION ALL
SELECT /*+ ORDERED */
resd.resource_name resource_name
,fct.person_id resource_id
,0 expected_hours
,0 capacity_hours
,null missing_hours
,0 actual_weighted_hours
,0 sch_conf_weighted_hours
,0 sch_prov_weighted_hours
,0 expected_bill_weighted_hours
,0 expected_traing_traing_hours
,0 actual_hours
,0 exp_ac_capacity_hours
,0 exp_ac_actual_hours
,0 exp_sch_capacity_hours
,0 exp_sch_actual_hours
,fct.total_wtd_org_hrs_a
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) prior_actual_weighted_hours
,(fct.capacity_hrs-NVL(fct.reduce_capacity_hrs_a,0))
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) prior_capacity_hours
,fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_actual_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pji_pmv_time_dim_tmp time
,pji_pmv_jb_dim_tmp jbt
,pji_rm_res_f fct
,pji_pmv_org_dim_tmp hou
,pa_resources_denorm resd
,pa_implementations_all imp
WHERE
fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.prior_id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.prior_id is not null
AND fct.job_id = jbt.id
AND fct.person_id = resd.person_id
AND resd.resource_organization_id = horg.id
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
AND hou.id = imp.org_id
-- get the data for the resources who doens't have data
UNION ALL
SELECT
resd.resource_name resource_name
,resd.person_id resource_id
,0 expected_hours
,0 capacity_hours
,null missing_hours
,0 actual_weighted_hours
,0 sch_conf_weighted_hours
,0 sch_prov_weighted_hours
,0 expected_bill_weighted_hours
,0 expected_traing_traing_hours
,0 actual_hours
,0 exp_ac_capacity_hours
,0 exp_ac_actual_hours
,0 exp_sch_capacity_hours
,0 exp_sch_actual_hours
,0 prior_actual_weighted_hours
,0 prior_capacity_hours
,0 prior_actual_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pji_pmv_jb_dim_tmp jbt
,pa_resources_denorm resd
WHERE
resd.resource_organization_id = horg.id
AND resd.utilization_flag = 'Y'
AND resd.job_id = jbt.id
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
)
GROUP BY resource_name, resource_id;
SELECT PJI_REP_U8( resource_name
,resource_id
,SUM(expected_hours)
,SUM(capacity_hours-reduce_capacity_hours)
,SUM(missing_hours)
,SUM(actual_weighted_hours)
,SUM(sch_conf_weighted_hours)
,SUM(sch_prov_weighted_hours)
,SUM(expected_bill_weighted_hours)
,SUM(expected_training_hours)
,SUM(prior_actual_weighted_hours)
,SUM(prior_capacity_hours-prior_red_capacity_hours)
,SUM(DECODE(l_util_percent_denom_prof,'CAPACITY',
capacity_hours-reduce_capacity_hours,actual_hours))
,SUM(decode(l_util_percent_denom_prof, 'CAPACITY',
capacity_hours-exp_ac_red_capacity_hours, exp_ac_actual_hours))
,SUM(decode(l_util_percent_denom_prof, 'CAPACITY',
capacity_hours-exp_sch_red_capacity_hours, exp_sch_actual_hours))
,SUM(DECODE(l_util_percent_denom_prof,'CAPACITY',
prior_capacity_hours-prior_red_capacity_hours,prior_actual_hours)),
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_u8_tbl
/* Bug 3515594 */
FROM
(
-- get current year values
SELECT /*+ ORDERED */
resd.resource_name resource_name
,fct.person_id resource_id
,DECODE(time.amount_type,0,fct.total_hrs_a,fct.conf_hrs_s)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) expected_hours
,0 capacity_hours
,0 reduce_capacity_hours
,null missing_hours
,DECODE(time.amount_type,0,fct.total_hrs_a,0)* wtb.org_utilization_percentage/100
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) actual_weighted_hours
,DECODE(time.amount_type,1,fct.conf_hrs_s,0)* wtb.org_utilization_percentage/100
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) sch_conf_weighted_hours
,DECODE(time.amount_type,1,fct.prov_hrs_s,0)* wtb.org_utilization_percentage/100
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) sch_prov_weighted_hours
,(case when time.amount_type=0 then
fct.bill_hrs_a*wtb.org_utilization_percentage/100
when time.amount_type=1 and wtb.billable_capitalizable_flag='Y' then
fct.conf_hrs_s*wtb.org_utilization_percentage/100
else 0 end) * decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
expected_bill_weighted_hours
,(case when time.amount_type=0 and wtb.training_flag='Y' then fct.total_hrs_a
when time.amount_type=1 and wtb.training_flag='Y' then fct.conf_hrs_s
else 0 end) / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
expected_training_hours
,fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
actual_hours
,0 exp_ac_red_capacity_hours
,decode(time.amount_type, 0, fct.total_hrs_a, 0)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_ac_actual_hours
,0 exp_sch_red_capacity_hours
,decode(time.amount_type, 0, 0, fct.total_hrs_a)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_sch_actual_hours
,0 prior_actual_weighted_hours
,0 prior_capacity_hours
,0 prior_red_capacity_hours
,0 prior_actual_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pji_pmv_time_dim_tmp time
,pji_pmv_wt_dim_tmp wt
,pji_rm_res_wt_f fct
,pji_pmv_org_dim_tmp hou
,pa_work_types_b wtb
,pa_resources_denorm resd
,pa_implementations_all imp
WHERE
fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND fct.work_type_id = wt.id
AND wtb.work_type_id = wt.id
AND fct.person_id = resd.person_id
AND resd.resource_organization_id = horg.id
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
AND hou.id = imp.org_id
-- get prior year values
UNION ALL
SELECT
resd.resource_name resource_name
,fct.person_id resource_id
,0 expected_hours
,0 capacity_hours
,0 reduce_capacity_hours
,null missing_hours
,0 actual_weighted_hours
,0 sch_conf_weighted_hours
,0 sch_prov_weighted_hours
,0 expected_bill_weighted_hours
,0 expected_traing_hours
,0 actual_hours
,0 exp_ac_red_capacity_hours
,0 exp_ac_actual_hours
,0 exp_sch_red_capacity_hours
,0 exp_sch_actual_hours
,fct.total_hrs_a * wtb.org_utilization_percentage/100
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) prior_actual_weighted_hours
,0 prior_capacity_hours
,0 prior_red_capacity_hours
,fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_actual_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pji_pmv_time_dim_tmp time
,pji_pmv_wt_dim_tmp wt
,pji_rm_res_wt_f fct
,pji_pmv_org_dim_tmp hou
,pa_work_types_b wtb
,pa_resources_denorm resd
,pa_implementations_all imp
WHERE
fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.prior_id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.prior_id is not null
AND fct.work_type_id = wt.id
AND wtb.work_type_id = wt.id
AND fct.person_id = resd.person_id
AND resd.resource_organization_id = horg.id
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
AND fct.expenditure_org_id = imp.org_id
-- get the data for the resources who doesn't have data
UNION ALL
SELECT
resd.resource_name resource_name
,resd.person_id resource_id
,0 expected_hours
,0 capacity_hours
,0 reduce_capacity_hours
,null missing_hours
,0 actual_weighted_hours
,0 sch_conf_weighted_hours
,0 sch_prov_weighted_hours
,0 expected_bill_weighted_hours
,0 expected_traing_hours
,0 actual_hours
,0 exp_ac_red_capacity_hours
,0 exp_ac_actual_hours
,0 exp_sch_red_capacity_hours
,0 exp_sch_actual_hours
,0 prior_actual_weighted_hours
,0 prior_capacity_hours
,0 prior_red_capacity_hours
,0 prior_actual_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pa_resources_denorm resd
WHERE
resd.resource_organization_id = horg.id
AND resd.utilization_flag = 'Y'
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
UNION ALL -- added for current year capacity_hours
/* Bug 3515594 */
SELECT /*+ ORDERED */
resd.resource_name resource_name
,resd.person_id resource_id
,0 expected_hours
,fct.capacity_hrs
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
capacity_hours
,decode(time.amount_type, 0, fct.reduce_capacity_hrs_a, reduce_capacity_hrs_s)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
reduce_capacity_hours
,fct.missing_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
missing_hours
,0 actual_weighted_hours
,0 sch_conf_weighted_hours
,0 sch_prov_weighted_hours
,0 expected_bill_weighted_hours
,0 expected_traing_hours
,0 actual_hours
,decode(time.amount_type, 0, fct.reduce_capacity_hrs_a, 0)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_ac_red_capacity_hours
,0 exp_ac_actual_hours
,decode(time.amount_type, 1, fct.reduce_capacity_hrs_s, 0)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_sch_red_capacity_hours
,0 exp_sch_actual_hours
,0 prior_actual_weighted_hours
,0 prior_capacity_hours
,0 prior_red_capacity_hours
,0 prior_actual_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pji_pmv_time_dim_tmp time
,pji_rm_res_f fct
,pji_pmv_org_dim_tmp hou
,pa_resources_denorm resd
,pa_implementations_all imp
WHERE
fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND fct.person_id = resd.person_id
AND resd.resource_organization_id = horg.id
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
AND hou.id = imp.org_id
UNION ALL -- added for prior year capacity_hours
SELECT /*+ ORDERED */
resd.resource_name resource_name
,resd.person_id resource_id
,0 expected_hours
,0 capacity_hours
,0 reduce_capacity_hours
,null missing_hours
,0 actual_weighted_hours
,0 sch_conf_weighted_hours
,0 sch_prov_weighted_hours
,0 expected_bill_weighted_hours
,0 expected_traing_hours
,0 actual_hours
,0 exp_ac_red_capacity_hours
,0 exp_ac_actual_hours
,0 exp_sch_red_capacity_hours
,0 exp_sch_actual_hours
,0 prior_actual_weighted_hours
,fct.capacity_hrs / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_capacity_hours
,fct.reduce_capacity_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_red_capacity_hours
,0 prior_actual_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pji_pmv_time_dim_tmp time
,pji_rm_res_f fct
,pji_pmv_org_dim_tmp hou
,pa_resources_denorm resd
,pa_implementations_all imp
WHERE
fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.prior_id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.prior_id is not null
AND fct.person_id = resd.person_id
AND resd.resource_organization_id = horg.id
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
AND hou.id = imp.org_id
)
GROUP BY resource_name, resource_id;
SELECT PJI_REP_U8( resource_name
,resource_id
,SUM(expected_hours)
,SUM(capacity_hours-reduce_capacity_hours)
,SUM(missing_hours)
,SUM(actual_weighted_hours)
,SUM(sch_conf_weighted_hours)
,SUM(sch_prov_weighted_hours)
,SUM(expected_bill_weighted_hours)
,SUM(expected_training_hours)
,SUM(prior_actual_weighted_hours)
,SUM(prior_capacity_hours-prior_red_capacity_hours)
,SUM(DECODE(l_util_percent_denom_prof,'CAPACITY',
capacity_hours-reduce_capacity_hours,actual_hours))
,SUM(decode(l_util_percent_denom_prof, 'CAPACITY',
capacity_hours-exp_ac_red_capacity_hours, exp_ac_actual_hours))
,SUM(decode(l_util_percent_denom_prof, 'CAPACITY',
capacity_hours-exp_sch_red_capacity_hours, exp_sch_actual_hours))
,SUM(DECODE(l_util_percent_denom_prof,'CAPACITY',
prior_capacity_hours-prior_red_capacity_hours,prior_actual_hours)),
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_u8_tbl
/* Bug 3515594 */
FROM
(
-- get current year values
SELECT /*+ ORDERED */
resd.resource_name resource_name
,fct.person_id resource_id
,DECODE(time.amount_type,0,fct.total_hrs_a,fct.conf_hrs_s)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) expected_hours
,0 capacity_hours
,0 reduce_capacity_hours
,null missing_hours
,DECODE(time.amount_type,0,fct.total_hrs_a,0)* wtb.org_utilization_percentage/100
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) actual_weighted_hours
,DECODE(time.amount_type,1,fct.conf_hrs_s,0)* wtb.org_utilization_percentage/100
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) sch_conf_weighted_hours
,DECODE(time.amount_type,1,fct.prov_hrs_s,0)* wtb.org_utilization_percentage/100
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) sch_prov_weighted_hours
,(case when time.amount_type=0 and wtb.billable_capitalizable_flag='Y' then
fct.total_hrs_a*wtb.org_utilization_percentage/100
when time.amount_type=1 and wtb.billable_capitalizable_flag='Y' then
fct.conf_hrs_s*wtb.org_utilization_percentage/100 else 0 end)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
expected_bill_weighted_hours
,(case when time.amount_type=0 and wtb.training_flag='Y' then fct.total_hrs_a
when time.amount_type=1 and wtb.training_flag='Y' then fct.conf_hrs_s else 0 end)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
expected_training_hours
,fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
actual_hours
,0 exp_ac_red_capacity_hours
,decode(time.amount_type, 0, fct.total_hrs_a, 0)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_ac_actual_hours
,0 exp_sch_red_capacity_hours
,decode(time.amount_type, 0, 0, fct.total_hrs_a)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_sch_actual_hours
,0 prior_actual_weighted_hours
,0 prior_capacity_hours
,0 prior_red_capacity_hours
,0 prior_actual_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pji_pmv_time_dim_tmp time
,pji_pmv_wt_dim_tmp wt
,pji_rm_res_wt_f fct
,pji_pmv_jb_dim_tmp jbt
,pji_pmv_org_dim_tmp hou
,pa_work_types_b wtb
,pa_resources_denorm resd
,pa_implementations_all imp
WHERE
fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND fct.work_type_id = wt.id
AND wtb.work_type_id = wt.id
AND fct.job_id = jbt.id
AND fct.person_id = resd.person_id
AND resd.resource_organization_id = horg.id
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
AND hou.id = imp.org_id
-- get prior year values
UNION ALL
SELECT
resd.resource_name resource_name
,fct.person_id resource_id
,0 expected_hours
,0 capacity_hours
,0 reduce_capacity_hours
,null missing_hours
,0 actual_weighted_hours
,0 sch_conf_weighted_hours
,0 sch_prov_weighted_hours
,0 expected_bill_weighted_hours
,0 expected_traing_hours
,0 actual_hours
,0 exp_ac_red_capacity_hours
,0 exp_ac_actual_hours
,0 exp_sch_red_capacity_hours
,0 exp_sch_actual_hours
,(fct.total_hrs_a * wtb.org_utilization_percentage/100)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) prior_actual_weighted_hours
,0 prior_capacity_hours
,(CASE WHEN wtb.reduce_capacity_flag = 'Y' THEN fct.total_hrs_a ELSE 0 END)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) prior_red_capacity_hours
,fct.total_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_actual_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pji_pmv_time_dim_tmp time
,pji_pmv_wt_dim_tmp wt
,pji_rm_res_wt_f fct
,pji_pmv_jb_dim_tmp jbt
,pji_pmv_org_dim_tmp hou
,pa_work_types_b wtb
,pa_resources_denorm resd
,pa_implementations_all imp
WHERE
fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.prior_id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.prior_id is not null
AND fct.work_type_id = wt.id
AND wtb.work_type_id = wt.id
AND fct.job_id = jbt.id
AND fct.person_id = resd.person_id
AND resd.resource_organization_id = horg.id
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
AND fct.expenditure_org_id = imp.org_id
-- get the data for the resources who doesn't have data
UNION ALL
SELECT
resd.resource_name resource_name
,resd.person_id resource_id
,0 expected_hours
,0 capacity_hours
,0 reduce_capacity_hours
,null missing_hours
,0 actual_weighted_hours
,0 sch_conf_weighted_hours
,0 sch_prov_weighted_hours
,0 expected_bill_weighted_hours
,0 expected_traing_hours
,0 actual_hours
,0 exp_ac_red_capacity_hours
,0 exp_ac_actual_hours
,0 exp_sch_red_capacity_hours
,0 exp_sch_actual_hours
,0 prior_actual_weighted_hours
,0 prior_capacity_hours
,0 prior_red_capacity_hours
,0 prior_actual_hours
FROM
pa_resources_denorm resd
,pji_pmv_orgz_dim_tmp horg
,pji_pmv_jb_dim_tmp jbt
WHERE
resd.resource_organization_id = horg.id
AND resd.utilization_flag = 'Y'
AND resd.job_id = jbt.id
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
UNION ALL -- added for current year capacity_hours
/* Bug 3515594 */
SELECT /*+ ORDERED */
resd.resource_name resource_name
,resd.person_id resource_id
,0 expected_hours
,fct.capacity_hrs / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
capacity_hours
,decode(time.amount_type, 0, fct.reduce_capacity_hrs_a, fct.reduce_capacity_hrs_s)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
reduce_capacity_hours
,fct.missing_hrs_a / decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
missing_hours
,0 actual_weighted_hours
,0 sch_conf_weighted_hours
,0 sch_prov_weighted_hours
,0 expected_bill_weighted_hours
,0 expected_traing_hours
,0 actual_hours
,decode(time.amount_type, 0, fct.reduce_capacity_hrs_a, 0)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
exp_ac_red_capacity_hours
,0 exp_ac_actual_hours
,decode(time.amount_type, 1, fct.reduce_capacity_hrs_s, 0)
/ decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1) exp_sch_red_capacity_hours
,0 exp_sch_actual_hours
,0 prior_actual_weighted_hours
,0 prior_capacity_hours
,0 prior_red_capacity_hours
,0 prior_actual_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pji_pmv_time_dim_tmp time
,pji_pmv_jb_dim_tmp jbt
,pji_rm_res_f fct
,pji_pmv_org_dim_tmp hou
,pa_resources_denorm resd
,pa_implementations_all imp
WHERE
fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.id is not null
AND fct.job_id = jbt.id
AND fct.person_id = resd.person_id
AND resd.resource_organization_id = horg.id
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
AND hou.id = imp.org_id
UNION ALL -- added for prior year capacity_hours
SELECT /*+ ORDERED */
resd.resource_name resource_name
,resd.person_id resource_id
,0 expected_hours
,0 capacity_hours
,0 reduce_capacity_hours
,null missing_hours
,0 actual_weighted_hours
,0 sch_conf_weighted_hours
,0 sch_prov_weighted_hours
,0 expected_bill_weighted_hours
,0 expected_traing_hours
,0 actual_hours
,0 exp_ac_red_capacity_hours
,0 exp_ac_actual_hours
,0 exp_sch_red_capacity_hours
,0 exp_sch_actual_hours
,0 prior_actual_weighted_hours
,fct.capacity_hrs * decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_capacity_hours
,fct.reduce_capacity_hrs_a * decode(l_labor_unit, 'DAYS', imp.FTE_DAY, 'WEEKS',imp.FTE_WEEK, 1)
prior_red_capacity_hours
,0 prior_actual_hours
FROM
pji_pmv_orgz_dim_tmp horg
,pji_pmv_time_dim_tmp time
,pji_pmv_jb_dim_tmp jbt
,pji_rm_res_f fct
,pji_pmv_org_dim_tmp hou
,pa_resources_denorm resd
,pa_implementations_all imp
WHERE
fct.expenditure_org_id = hou.id
AND fct.expenditure_organization_id = horg.id
AND fct.time_id = time.prior_id
AND fct.period_type_id = time.period_type
AND fct.calendar_type = decode(fct.period_type_id,1, 'C',time.calendar_type)
AND time.prior_id is not null
AND fct.job_id = jbt.id
AND fct.person_id = resd.person_id
AND resd.resource_organization_id = horg.id
AND TO_DATE(p_as_of_date,'j') between resd.resource_effective_start_date and
resd.resource_effective_end_date
AND hou.id = imp.org_id
)
GROUP BY resource_name, resource_id;