[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
,P_SELECT_LIST =>
'FACT.CAPACITY "PJI_REP_MSR_3",
FACT.CONFIRMED "PJI_REP_MSR_4",
FACT.PROVISIONAL "PJI_REP_MSR_5",
FACT.UNASSIGNED "PJI_REP_MSR_11",
FACT.AVAILABLE "PJI_REP_MSR_12",
FACT.PERCENT_AVAILABLE "PJI_REP_MSR_13",
FACT.PERCENT_ACTUAL_UTILIZATION "PJI_REP_MSR_14",
FACT.PERCENT_SCHEDULED_UTILIZATION "PJI_REP_MSR_15",
FACT.REDUCE_CAP_A "PJI_REP_MSR_27",
FACT.REDUCE_CAP_S "PJI_REP_MSR_28",
FACT.TOT_WTD_A "PJI_REP_MSR_29",
FACT.CONF_WTD_S "PJI_REP_MSR_30",
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_RA1',
P_PLSQL_DRIVER => 'PJI_PMV_AVL.PLSQLDRIVER_RA1',
P_PLSQL_DRIVER_PARAMS => '<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, ' ||
'<>');
SELECT REPORT_LABOR_UNITS
INTO G_LABOUR_UNITS
FROM PJI_SYSTEM_SETTINGS;
SELECT VALUE
INTO G_AVL_THRESHOLD_VAL
FROM PJI_AVL_THRESHOLDS_V
WHERE ID = P_THRESHOLD;
SELECT DISTINCT
FIRST_VALUE(ID) OVER (ORDER BY VALUE DESC) INTO L_THRESHOLD
FROM PJI_AVL_THRESHOLDS_V;
SELECT PJI_REP_RA1(
ORGANIZATION_ID
, SUM(CAPACITY)
, SUM(CAPACITY_A)
, SUM(CONFIRMED)
, SUM(PROVISIONAL)
, SUM(UNASSIGNED)
, DECODE (L_THRESHOLD, 1, SUM(AVL_BKT1)
, 2, SUM(AVL_BKT2)
, 3, SUM(AVL_BKT3)
, 4, SUM(AVL_BKT4)
, 5, SUM(AVL_BKT5)
, 0)
, 0
, 0
, 0
, SUM(REDUCE_CAP_A)
, SUM(REDUCE_CAP_S)
, SUM(TOT_WTD_A)
, SUM(CONF_WTD_S)
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0)
BULK COLLECT INTO L_AVL_DAYS_SUM_TBL
FROM (
/* Bug 3515594 */
SELECT /*+ ORDERED */
HORG.NAME ORGANIZATION_ID
,CAPACITY_HRS /
DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY,
'WEEKS',IMP.FTE_WEEK, 1) CAPACITY
,0 CAPACITY_A
,(CONF_HRS_S - NVL(CONF_OVERCOM_HRS_S,0)) /
DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY,
'WEEKS',IMP.FTE_WEEK, 1) CONFIRMED
,(PROV_HRS_S - NVL(PROV_OVERCOM_HRS_S,0)) /
DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY,
'WEEKS',IMP.FTE_WEEK, 1) PROVISIONAL
,UNASSIGNED_HRS_S /
DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY,
'WEEKS',IMP.FTE_WEEK, 1) UNASSIGNED
,0 REDUCE_CAP_A
,REDUCE_CAPACITY_HRS_S /
DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY,
'WEEKS',IMP.FTE_WEEK, 1) REDUCE_CAP_S
,0 TOT_WTD_A
,CONF_WTD_ORG_HRS_S /
DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY,
'WEEKS',IMP.FTE_WEEK, 1) CONF_WTD_S
,CASE WHEN (AVAILABLE_RES_COUNT_BKT1_S = 0) THEN 0 ELSE
AVAILABLE_HRS_BKT1_S /
DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY,
'WEEKS',IMP.FTE_WEEK, 1) END AVL_BKT1
,CASE WHEN (AVAILABLE_RES_COUNT_BKT2_S = 0) THEN 0 ELSE
AVAILABLE_HRS_BKT2_S /
DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY,
'WEEKS',IMP.FTE_WEEK, 1) END AVL_BKT2
,CASE WHEN (AVAILABLE_RES_COUNT_BKT3_S = 0) THEN 0 ELSE
AVAILABLE_HRS_BKT3_S /
DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY,
'WEEKS',IMP.FTE_WEEK, 1) END AVL_BKT3
,CASE WHEN (AVAILABLE_RES_COUNT_BKT4_S = 0) THEN 0 ELSE
AVAILABLE_HRS_BKT4_S /
DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY,
'WEEKS',IMP.FTE_WEEK, 1) END AVL_BKT4
,CASE WHEN (AVAILABLE_RES_COUNT_BKT5_S = 0) THEN 0 ELSE
AVAILABLE_HRS_BKT5_S /
DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY,
'WEEKS',IMP.FTE_WEEK, 1) END AVL_BKT5
FROM PJI_PMV_TIME_DIM_TMP TIME,
PJI_PMV_ORGZ_DIM_TMP HORG,
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 HOU.ID = IMP.ORG_ID
AND FCT.TIME_ID = TIME.ID
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND TIME.AMOUNT_TYPE=2
UNION ALL
SELECT /*+ ORDERED */
HORG.NAME ORGANIZATION_ID
,0 CAPACITY
,CAPACITY_HRS /
DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY,
'WEEKS',IMP.FTE_WEEK, 1) CAPACITY_A
,0 CONFIRMED
,0 PROVISIONAL
,0 UNASSIGNED
,REDUCE_CAPACITY_HRS_A /
DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY,
'WEEKS',IMP.FTE_WEEK, 1) REDUCE_CAP_A
,0 REDUCE_CAP_S
,TOTAL_WTD_ORG_HRS_A /
DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY,
'WEEKS',IMP.FTE_WEEK, 1) TOT_WTD_A
,0 CONF_WTD_S
,0 AVL_BKT1
,0 AVL_BKT2
,0 AVL_BKT3
,0 AVL_BKT4
,0 AVL_BKT5
FROM PJI_PMV_TIME_DIM_TMP TIME,
PJI_PMV_ORGZ_DIM_TMP HORG,
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 HOU.ID = IMP.ORG_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.AMOUNT_TYPE=1
UNION ALL
SELECT NAME ORGANIZATION_ID
, 0 CAPACITY
, 0 CAPACITY_A
, 0 CONFIRMED
, 0 PROVISIONAL
, 0 UNASSIGNED
, 0 REDUCE_CAP_A
, 0 REDUCE_CAP_S
, 0 TOT_WTD_A
, 0 CONF_WTD_S
, 0 AVL_BKT1
, 0 AVL_BKT2
, 0 AVL_BKT3
, 0 AVL_BKT4
, 0 AVL_BKT5
FROM PJI_PMV_ORGZ_DIM_TMP
WHERE NAME <> '-1'
) GROUP BY ORGANIZATION_ID;
L_AVL_DAYS_SUM_TBL.DELETE(L_TOP_ORG_INDEX);
,P_SELECT_LIST =>
'FACT.CURR_AVL_RES "PJI_REP_MSR_2",
FACT.CURR_AVL_RES_URL_PARAMS "PJI_REP_MSR_12",
FACT.TOTAL_RESOURCES "PJI_REP_MSR_3",
FACT.CURR_AVL_RES_PER "PJI_REP_MSR_4",
FACT.W1 "PJI_REP_MSR_5",
FACT.W1_URL_PARAMS "PJI_REP_MSR_15",
FACT.W2 "PJI_REP_MSR_6",
FACT.W2_URL_PARAMS "PJI_REP_MSR_16",
FACT.W3 "PJI_REP_MSR_7",
FACT.W3_URL_PARAMS "PJI_REP_MSR_17",
FACT.W4 "PJI_REP_MSR_8",
FACT.W4_URL_PARAMS "PJI_REP_MSR_18",
FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_1",
FACT.PJI_REP_TOTAL_2 "PJI_REP_TOTAL_2",
FACT.PJI_REP_TOTAL_7 "PJI_REP_TOTAL_3",
FACT.PJI_REP_TOTAL_3 "PJI_REP_TOTAL_4",
FACT.PJI_REP_TOTAL_4 "PJI_REP_TOTAL_5",
FACT.PJI_REP_TOTAL_5 "PJI_REP_TOTAL_6",
FACT.PJI_REP_TOTAL_6 "PJI_REP_TOTAL_7"'
,P_SQL_STATEMENT => X_PMV_SQL
,P_PMV_OUTPUT => X_PMV_OUTPUT,
P_REGION_CODE => 'PJI_REP_RA2',
P_PLSQL_DRIVER => 'PJI_PMV_AVL.PLSQLDRIVER_RA2',
P_PLSQL_DRIVER_PARAMS => '<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, ' ||
'<>');
DELETE PJI_PMV_TIME_DIM_TMP;
SELECT WEEK_ID , TO_CHAR(END_DATE,'J')
BULK COLLECT INTO
L_WEEK_IDS , L_WEEK_END_DATE
FROM FII_TIME_WEEK
WHERE 1 = 1
AND TO_DATE(P_AS_OF_DATE+28,'J') >= START_DATE
AND TO_DATE(P_AS_OF_DATE,'J') <= END_DATE;
SELECT DISTINCT
FIRST_VALUE(ID) OVER (ORDER BY VALUE DESC) INTO L_THRESHOLD
FROM PJI_AVL_THRESHOLDS_V;
SELECT PJI_REP_RA2(
ORGANIZATION_ID
,SUM(CURR_AVL)
,PJI_PMV_UTIL.ra2_ra5_url(p_as_of_date,'W0',ID,p_operating_unit,l_threshold)
,SUM(TOTAL_RESOURCES)
,SUM(WEEK_1)
,PJI_PMV_UTIL.ra2_ra5_url(p_as_of_date,'W1',ID,p_operating_unit,l_threshold)
,SUM(WEEK_2)
,PJI_PMV_UTIL.ra2_ra5_url(p_as_of_date,'W2',ID,p_operating_unit,l_threshold)
,SUM(WEEK_3)
,PJI_PMV_UTIL.ra2_ra5_url(p_as_of_date,'W3',ID,p_operating_unit,l_threshold)
,SUM(WEEK_4)
,PJI_PMV_UTIL.ra2_ra5_url(p_as_of_date,'W4',ID,p_operating_unit,l_threshold)
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL)
BULK COLLECT INTO L_CUR_AVL_RES_TBL
FROM (
SELECT /*+ ORDERED */
HORG.NAME ORGANIZATION_ID
,HORG.ID ID
,TOTAL_RES_COUNT TOTAL_RESOURCES
,FCT.AVAILABILITY CURR_AVL
,0 WEEK_1
,0 WEEK_2
,0 WEEK_3
,0 WEEK_4
FROM PJI_PMV_ORGZ_DIM_TMP HORG,
PJI_CA_ORGO_F_MV FCT,
PJI_PMV_ORG_DIM_TMP HOU
WHERE FCT.EXPENDITURE_ORG_ID = HOU.ID
AND FCT.EXPENDITURE_ORGANIZATION_ID = HORG.ID
AND FCT.TIME_ID = L_WEEK_IDS(1)
AND FCT.CALENDAR_TYPE = L_CALENDAR_TYPE
AND FCT.PERIOD_TYPE_ID = L_PERIOD_TYPE_ID
AND FCT.THRESHOLD = L_THRESHOLD
AND FCT.AS_OF_DATE <= L_WEEK_END_DATE(1)
UNION ALL
SELECT /*+ ORDERED */
HORG.NAME ORGANIZATION_ID
,HORG.ID ID
,0 TOTAL_RESOURCES
,0 CURR_AVL
,FCT.AVAILABILITY WEEK_1
,0 WEEK_2
,0 WEEK_3
,0 WEEK_4
FROM PJI_PMV_ORGZ_DIM_TMP HORG,
PJI_CA_ORGO_F_MV FCT,
PJI_PMV_ORG_DIM_TMP HOU
WHERE FCT.EXPENDITURE_ORG_ID = HOU.ID
AND FCT.EXPENDITURE_ORGANIZATION_ID = HORG.ID
AND FCT.TIME_ID = L_WEEK_IDS(2)
AND FCT.CALENDAR_TYPE = L_CALENDAR_TYPE
AND FCT.PERIOD_TYPE_ID = L_PERIOD_TYPE_ID
AND FCT.THRESHOLD = L_THRESHOLD
AND FCT.AS_OF_DATE <= L_WEEK_END_DATE(2)
UNION ALL
SELECT /*+ ORDERED */
HORG.NAME ORGANIZATION_ID
,HORG.ID ID
,0 TOTAL_RESOURCES
,0 CURR_AVL
,0 WEEK_1
,FCT.AVAILABILITY WEEK_2
,0 WEEK_3
,0 WEEK_4
FROM PJI_PMV_ORGZ_DIM_TMP HORG,
PJI_CA_ORGO_F_MV FCT,
PJI_PMV_ORG_DIM_TMP HOU
WHERE FCT.EXPENDITURE_ORG_ID = HOU.ID
AND FCT.EXPENDITURE_ORGANIZATION_ID = HORG.ID
AND FCT.TIME_ID = L_WEEK_IDS(3)
AND FCT.CALENDAR_TYPE = L_CALENDAR_TYPE
AND FCT.PERIOD_TYPE_ID = L_PERIOD_TYPE_ID
AND FCT.THRESHOLD = L_THRESHOLD
AND FCT.AS_OF_DATE <= L_WEEK_END_DATE(3)
UNION ALL
SELECT /*+ ORDERED */
HORG.NAME ORGANIZATION_ID
,HORG.ID ID
,0 TOTAL_RESOURCES
,0 CURR_AVL
,0 WEEK_1
,0 WEEK_2
,FCT.AVAILABILITY WEEK_3
,0 WEEK_4
FROM PJI_PMV_ORGZ_DIM_TMP HORG,
PJI_CA_ORGO_F_MV FCT,
PJI_PMV_ORG_DIM_TMP HOU
WHERE FCT.EXPENDITURE_ORG_ID = HOU.ID
AND FCT.EXPENDITURE_ORGANIZATION_ID = HORG.ID
AND FCT.TIME_ID = L_WEEK_IDS(4)
AND FCT.CALENDAR_TYPE = L_CALENDAR_TYPE
AND FCT.PERIOD_TYPE_ID = L_PERIOD_TYPE_ID
AND FCT.THRESHOLD = L_THRESHOLD
AND FCT.AS_OF_DATE <= L_WEEK_END_DATE(4)
UNION ALL
SELECT /*+ ORDERED */
HORG.NAME ORGANIZATION_ID
,HORG.ID ID
,0 TOTAL_RESOURCES
,0 CURR_AVL
,0 WEEK_1
,0 WEEK_2
,0 WEEK_3
,FCT.AVAILABILITY WEEK_4
FROM PJI_PMV_ORGZ_DIM_TMP HORG,
PJI_CA_ORGO_F_MV FCT,
PJI_PMV_ORG_DIM_TMP HOU
WHERE FCT.EXPENDITURE_ORG_ID = HOU.ID
AND FCT.EXPENDITURE_ORGANIZATION_ID = HORG.ID
AND FCT.TIME_ID = L_WEEK_IDS(5)
AND FCT.CALENDAR_TYPE = L_CALENDAR_TYPE
AND FCT.PERIOD_TYPE_ID = L_PERIOD_TYPE_ID
AND FCT.THRESHOLD = L_THRESHOLD
AND FCT.AS_OF_DATE <= L_WEEK_END_DATE(5)
UNION ALL
SELECT
NAME ORGANIZATION_ID
,ID ID
,0 TOTAL_RESOURCES
,0 CURR_AVL
,0 WEEK_1
,0 WEEK_2
,0 WEEK_3
,0 WEEK_4
FROM PJI_PMV_ORGZ_DIM_TMP
WHERE NAME <> '-1'
) GROUP BY ORGANIZATION_ID, ID;
L_CUR_AVL_RES_TBL.DELETE(L_TOP_ORG_INDEX);
,P_SELECT_LIST =>
'FACT.EXP_ORGANIZATION_ID "VIEWBYID",
FACT.CURR_AVL_RES "PJI_REP_MSR_2",
FACT.TOTAL_AVL_RES "PJI_REP_MSR_3",
FACT.TOTAL_RESOURCES "PJI_REP_MSR_4",
FACT.TOTAL_AVL_RES_PERCENT "PJI_REP_MSR_5",
FACT.BUCKET1 "PJI_REP_MSR_11",
FACT.BUCKET2 "PJI_REP_MSR_12",
FACT.BUCKET3 "PJI_REP_MSR_13",
FACT.BUCKET4 "PJI_REP_MSR_14",
FACT.BUCKET5 "PJI_REP_MSR_15",
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"'
,P_SQL_STATEMENT => X_PMV_SQL
,P_PMV_OUTPUT => X_PMV_OUTPUT,
P_REGION_CODE => 'PJI_REP_RA3',
P_PLSQL_DRIVER => 'PJI_PMV_AVL.PLSQLDRIVER_RA3',
P_PLSQL_DRIVER_PARAMS => '<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, ' ||
'<>');
SELECT DISTINCT
FIRST_VALUE(ID) OVER (ORDER BY VALUE DESC) INTO L_THRESHOLD
FROM PJI_AVL_THRESHOLDS_V;
SELECT PJI_REP_RA3 ( ORGANIZATION_ID
, EXP_ORGANIZATION_ID
,SUM(AVAILABILITY)
,SUM(CUR_AVL_1)
+ SUM(CUR_AVL_2)
+ SUM(CUR_AVL_3)
+ SUM(CUR_AVL_4)
+ SUM(CUR_AVL_5)
,SUM(TOTAL_RES_COUNT)
, 0
,SUM(CUR_AVL_1)
,SUM(CUR_AVL_2)
,SUM(CUR_AVL_3)
,SUM(CUR_AVL_4)
,SUM(CUR_AVL_5)
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0)
BULK COLLECT INTO L_AVL_RES_DUR_TBL
FROM (
SELECT /*+ ORDERED */
HORG.NAME ORGANIZATION_ID
, HORG.ID EXP_ORGANIZATION_ID
, DECODE (L_THRESHOLD, 1, available_res_count_bkt1_s,
2, available_res_count_bkt2_s,
3, available_res_count_bkt3_s,
4, available_res_count_bkt4_s,
5, available_res_count_bkt5_s) AVAILABILITY
, 0 TOTAL_RES_COUNT
, 0 CUR_AVL_1
, 0 CUR_AVL_2
, 0 CUR_AVL_3
, 0 CUR_AVL_4
, 0 CUR_AVL_5
FROM PJI_PMV_ORGZ_DIM_TMP HORG,
PJI_RM_ORGO_F_MV FCT,
PJI_PMV_ORG_DIM_TMP HOU
WHERE FCT.EXPENDITURE_ORG_ID = HOU.ID
AND FCT.EXPENDITURE_ORGANIZATION_ID = HORG.ID
AND FCT.PERIOD_TYPE_ID = l_DAY_PERIOD_TYPE
AND FCT.CALENDAR_TYPE = l_DAY_CALENDAR_TYPE
AND FCT.TIME_ID = L_AS_OF_DATE
UNION ALL
SELECT /*+ ORDERED */
HORG.NAME ORGANIZATION_ID
, HORG.ID EXP_ORGANIZATION_ID
, 0 AVAILABILITY
, TOTAL_RES_COUNT TOTAL_RES_COUNT
, DECODE (L_AVL_TYPE, 'CUMULATIVE', BCKT_1_CM,
'CONSECUTIVE', BCKT_1_CS, 0) CUR_AVL_1
, DECODE (L_AVL_TYPE, 'CUMULATIVE', BCKT_2_CM,
'CONSECUTIVE', BCKT_2_CS, 0) CUR_AVL_2
, DECODE (L_AVL_TYPE, 'CUMULATIVE', BCKT_3_CM,
'CONSECUTIVE', BCKT_3_CS, 0) CUR_AVL_3
, DECODE (L_AVL_TYPE, 'CUMULATIVE', BCKT_4_CM,
'CONSECUTIVE', BCKT_4_CS, 0) CUR_AVL_4
, DECODE (L_AVL_TYPE, 'CUMULATIVE', BCKT_5_CM,
'CONSECUTIVE', BCKT_5_CS, 0) CUR_AVL_5
FROM PJI_PMV_ORGZ_DIM_TMP HORG,
PJI_PMV_TIME_DIM_TMP TIME,
PJI_AV_ORGO_F_MV FCT,
PJI_PMV_ORG_DIM_TMP HOU
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 = TIME.CALENDAR_TYPE
AND FCT.THRESHOLD = L_THRESHOLD
AND FCT.AS_OF_DATE <= L_AS_OF_DATE
AND TIME.AMOUNT_TYPE=2
UNION ALL
SELECT /*+ ORDERED */
NAME
, ID
, 0 AVAILABILITY
, 0 TOTAL_RES_COUNT
, 0 CUR_AVL_1
, 0 CUR_AVL_2
, 0 CUR_AVL_3
, 0 CUR_AVL_4
, 0 CUR_AVL_5
FROM PJI_PMV_ORGZ_DIM_TMP HORG
WHERE NAME <> '-1')
GROUP BY ORGANIZATION_ID,
EXP_ORGANIZATION_ID;
L_AVL_RES_DUR_TBL.DELETE(L_TOP_ORG_INDEX);
, P_SELECT_LIST =>
'FACT.UNASSIGNED "PJI_REP_MSR_29",
FACT.AVAILABLE "PJI_REP_MSR_28",
FACT.SCHEDULED_UTIL "PJI_REP_MSR_30",
FACT.CAPACITY "PJI_REP_MSR_3",
FACT.CONFIRMED "PJI_REP_MSR_4",
FACT.PROVISIONAL "PJI_REP_MSR_5",
FACT.UNASSIGNED "PJI_REP_MSR_11",
FACT.AVAILABLE "PJI_REP_MSR_12",
FACT.AVAILABLE_URL "PJI_REP_MSR_22",
FACT.PER_HRS_AVAILABLE "PJI_REP_MSR_13",
FACT.SCHEDULED_UTIL_PER "PJI_REP_MSR_15"'
,P_SQL_STATEMENT => X_PMV_SQL
,P_PMV_OUTPUT => X_PMV_OUTPUT,
P_REGION_CODE => 'PJI_REP_RA4',
P_PLSQL_DRIVER => 'PJI_PMV_AVL.PLSQLDRIVER_RA4',
P_PLSQL_DRIVER_PARAMS => '<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, ' ||
'<>');
SELECT REPORT_LABOR_UNITS
INTO G_LABOUR_UNITS
FROM PJI_SYSTEM_SETTINGS;
SELECT DISTINCT
FIRST_VALUE(ID) OVER (ORDER BY VALUE DESC) INTO L_THRESHOLD
FROM PJI_AVL_THRESHOLDS_V;
SELECT PJI_REP_RA4(
ORG_ID
,ORGANIZATION_ID
,TIME_ID
,TIME_KEY
,SUM(CAPACITY)
,SUM(CONFIRMED)
,SUM(PROVISIONAL)
,SUM(UNASSIGNED)
,SUM(SCHEDULED_UTIL)
,SUM(DECODE(L_THRESHOLD, 1, AVL_BKT1
, 2, AVL_BKT2
, 3, AVL_BKT3
, 4, AVL_BKT4
, 5, AVL_BKT5
, 0))
,PJI_PMV_UTIL.ra4_ra5_url(TIME_ID, p_organization, p_operating_unit, l_threshold, p_period_type)
,NULL
,NULL)
BULK COLLECT INTO L_RA4_TBL
FROM ( /* 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
,CAPACITY_HRS / DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1)
CAPACITY
,(CONF_HRS_S - CONF_OVERCOM_HRS_S) / DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1)
CONFIRMED
,(PROV_HRS_S - PROV_OVERCOM_HRS_S) / DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1)
PROVISIONAL
,UNASSIGNED_HRS_S / DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1)
UNASSIGNED
,CONF_WTD_ORG_HRS_S / DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1)
SCHEDULED_UTIL
,(CASE WHEN (AVAILABLE_RES_COUNT_BKT1_S = 0)
THEN 0 ELSE AVAILABLE_HRS_BKT1_S /
DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1) END) AVL_BKT1
,(CASE WHEN (AVAILABLE_RES_COUNT_BKT2_S = 0)
THEN 0 ELSE AVAILABLE_HRS_BKT2_S /
DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1) END) AVL_BKT2
,(CASE WHEN (AVAILABLE_RES_COUNT_BKT3_S = 0)
THEN 0 ELSE AVAILABLE_HRS_BKT3_S /
DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1) END) AVL_BKT3
,(CASE WHEN (AVAILABLE_RES_COUNT_BKT4_S = 0)
THEN 0 ELSE AVAILABLE_HRS_BKT4_S /
DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1) END) AVL_BKT4
,(CASE WHEN (AVAILABLE_RES_COUNT_BKT5_S = 0)
THEN 0 ELSE AVAILABLE_HRS_BKT5_S /
DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1) END) AVL_BKT5
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 HOU.ID = IMP.ORG_ID
AND FCT.TIME_ID = TIME.ID
AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
AND (TIME.AMOUNT_TYPE = 2
OR TIME.AMOUNT_TYPE IS NULL)
UNION ALL
SELECT '-1' ORG_ID
,'-1' ORGANIZATION_ID
,NAME TIME_ID
,ORDER_BY_ID TIME_KEY
,0 CAPACITY
,0 CONFIRMED
,0 PROVISIONAL
,0 UNASSIGNED
,0 SCHEDULED_UTIL
,0 AVL_BKT1
,0 AVL_BKT2
,0 AVL_BKT3
,0 AVL_BKT4
,0 AVL_BKT5
FROM PJI_PMV_TIME_DIM_TMP
WHERE NAME <> '-1'
) GROUP BY ORG_ID
,ORGANIZATION_ID
,TIME_KEY
,TIME_ID ORDER BY TIME_KEY ASC;
,P_SELECT_LIST =>
'FACT.PERSON_NAME "VIEWBY",
FACT.JOB_LEVEL "PJI_REP_MSR_2",
FACT.CAPACITY "PJI_REP_MSR_3",
FACT.CONFIRMED "PJI_REP_MSR_4",
FACT.PROVISIONAL "PJI_REP_MSR_5",
FACT.UNASSIGNED "PJI_REP_MSR_11",
FACT.AVAILABLE_HOURS "PJI_REP_MSR_12",
FACT.ACT_UTIL_PER "PJI_REP_MSR_13",
FACT.SCH_UTIL_PER "PJI_REP_MSR_14",
TO_DATE(FACT.AVAILABLE_SINCE, ''RRRR/MM/DD'') "PJI_REP_MSR_15",
FACT.CURR_LAST_PROJ "PJI_REP_MSR_16",
FACT.NEXT_PROJ "PJI_REP_MSR_17",
TO_DATE(FACT.NEXT_ASGMT_DATE, ''RRRR/MM/DD'') "PJI_REP_MSR_18",
FACT.PERSON_ID "PJI_REP_MSR_25",
FACT.REDUCE_CAP_S "PJI_REP_MSR_26",
FACT.REDUCE_CAP_A "PJI_REP_MSR_27",
FACT.TOT_WTD_A "PJI_REP_MSR_29",
FACT.CONF_WTD_S "PJI_REP_MSR_30"'
,P_SQL_STATEMENT => X_PMV_SQL
,P_PMV_OUTPUT => X_PMV_OUTPUT,
P_REGION_CODE => 'PJI_REP_RA5',
P_PLSQL_DRIVER => 'PJI_PMV_AVL.PLSQLDRIVER_RA5',
P_PLSQL_DRIVER_PARAMS => '<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, ' ||
'<>, '||
'<>');
SELECT FROM_VALUE FROM_VALUE
, NVL(TO_VALUE,POWER(2,32)) TO_VALUE
INTO L_MIN_DAYS
, L_MAX_DAYS
FROM PJI_MT_BUCKETS
WHERE
BUCKET_SET_CODE = 'PJI_RES_AVL_DAYS'
AND SEQ = P_AVL_DAYS;
SELECT REPORT_LABOR_UNITS
INTO G_LABOUR_UNITS
FROM PJI_SYSTEM_SETTINGS;
SELECT PJI_REP_RA5(
PERSON_NAME
,PERSON_ID
,NULL
,SUM(CAPACITY)
,SUM(CAPACITY_A)
,SUM(CONFIRMED)
,SUM(PROVISIONAL)
,SUM(UNASSIGNED)
,SUM(AVAILABLE)
,SUM(AVAILABLE_HOURS)
,SUM(AVAILABLE_DAYS)
,SUM(TOT_WTD_A)
,SUM(CONF_WTD_S)
,SUM(REDUCE_CAP_A)
,SUM(REDUCE_CAP_S)
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL)
BULK COLLECT INTO L_AVL_RES_DET_TBL
FROM ( /* Bug 3515594 */
SELECT /*+ ORDERED */
NULL PERSON_NAME
,FCT.PERSON_ID PERSON_ID
,CAPACITY_HRS / DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1) CAPACITY
,0 CAPACITY_A
,CONF_HRS_S / DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1) CONFIRMED
,PROV_HRS_S / DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1) PROVISIONAL
,UNASSIGNED_HRS_S / DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1) UNASSIGNED
,0 TOT_WTD_A
,CONF_WTD_ORG_HRS_S / DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1) CONF_WTD_S
,0 REDUCE_CAP_A
,REDUCE_CAPACITY_HRS_S / DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1) REDUCE_CAP_S
,0 AVAILABLE
,DECODE(P_THRESHOLD, 1, DECODE(NVL(AVAILABLE_RES_COUNT_BKT1_S, 0),0,0,AVAILABLE_HRS_BKT1_S)
, 2, DECODE(NVL(AVAILABLE_RES_COUNT_BKT2_S, 0),0,0,AVAILABLE_HRS_BKT2_S)
, 3, DECODE(NVL(AVAILABLE_RES_COUNT_BKT3_S, 0),0,0,AVAILABLE_HRS_BKT3_S)
, 4, DECODE(NVL(AVAILABLE_RES_COUNT_BKT4_S, 0),0,0,AVAILABLE_HRS_BKT4_S)
, 5, DECODE(NVL(AVAILABLE_RES_COUNT_BKT5_S, 0),0,0,AVAILABLE_HRS_BKT5_S)
,DECODE(NVL(AVAILABLE_RES_COUNT_BKT5_S, 0),0,0,AVAILABLE_HRS_BKT5_S)
) / DECODE(G_LABOUR_UNITS , 'DAYS', IMP.FTE_DAY
, 'WEEKS', IMP.FTE_WEEK
, 1) AVAILABLE_HOURS
,DECODE(P_THRESHOLD, 1, AVAILABLE_RES_COUNT_BKT1_S
, 2, AVAILABLE_RES_COUNT_BKT2_S
, 3, AVAILABLE_RES_COUNT_BKT3_S
, 4, AVAILABLE_RES_COUNT_BKT4_S
, 5, AVAILABLE_RES_COUNT_BKT5_S
, AVAILABLE_RES_COUNT_BKT5_S
) AVAILABLE_DAYS
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_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 = TIME.CALENDAR_TYPE
AND TIME.AMOUNT_TYPE = 2
AND HOU.ID = IMP.ORG_ID
UNION ALL
/* Bug 3515594 */
SELECT /*+ ORDERED */
NULL PERSON_NAME
,FCT.PERSON_ID PERSON_ID
,0 CAPACITY
,CAPACITY_HRS / DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1) CAPACITY_A
,0 CONFIRMED
,0 PROVISIONAL
,0 UNASSIGNED
,TOTAL_WTD_ORG_HRS_A / DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1) TOT_WTD_A
,0 CONF_WTD_S
,REDUCE_CAPACITY_HRS_A / DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1) REDUCE_CAP_A
,0 REDUCE_CAP_S
,0 AVAILABLE
,0 AVAILABLE_HOURS
,0 AVAILABLE_DAYS
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_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 = TIME.CALENDAR_TYPE
AND TIME.AMOUNT_TYPE = 1
AND HOU.ID = IMP.ORG_ID
UNION ALL
SELECT /*+ ORDERED */
NULL PERSON_NAME
,FCT.PERSON_ID PERSON_ID
,0 CAPACITY
,0 CAPACITY_A
,0 CONFIRMED
,0 PROVISIONAL
,0 UNASSIGNED
,0 TOT_WTD_A
,0 CONF_WTD_S
,0 REDUCE_CAP_A
,0 REDUCE_CAP_S
,DECODE(P_THRESHOLD, 1, AVAILABLE_RES_COUNT_BKT1_S
, 2, AVAILABLE_RES_COUNT_BKT2_S
, 3, AVAILABLE_RES_COUNT_BKT3_S
, 4, AVAILABLE_RES_COUNT_BKT4_S
, 5, AVAILABLE_RES_COUNT_BKT5_S
, AVAILABLE_RES_COUNT_BKT5_S
) AVAILABLE
,0 AVAILABLE_HOURS
,0 AVAILABLE_DAYS
FROM PJI_PMV_ORGZ_DIM_TMP HORG,
PJI_RM_RES_F FCT,
PJI_PMV_ORG_DIM_TMP HOU
WHERE FCT.EXPENDITURE_ORG_ID = HOU.ID
AND FCT.EXPENDITURE_ORGANIZATION_ID = HORG.ID
AND FCT.TIME_ID = P_AS_OF_DATE
AND FCT.PERIOD_TYPE_ID = L_DAY_PERIOD_TYPE
AND FCT.CALENDAR_TYPE = L_DAY_CALENDAR_TYPE
)
GROUP BY PERSON_NAME, PERSON_ID
HAVING SUM(AVAILABLE_DAYS) BETWEEN L_MIN_DAYS AND L_MAX_DAYS
ORDER BY PERSON_ID;
SELECT RESOURCE_NAME
INTO L_AVL_RES_DET_TBL(I).PERSON_NAME
FROM PA_RESOURCES_DENORM
WHERE PERSON_ID = L_AVL_RES_DET_TBL(I).PERSON_ID
AND ROWNUM=1;
FOR cur_Projects_List IN (SELECT DISTINCT prj.name name
FROM pji_rm_res_wt_f fct
,pa_projects_all prj
WHERE
prj.project_id = fct.project_id
AND fct.person_id = p_Person_ID
AND fct.time_id = l_Time_ID
AND fct.period_type_id = l_Period_Type_ID
AND fct.calendar_type = l_Calendar_Type
AND fct.record_type = l_Record_Type)
LOOP
IF NVL(LENGTH(l_Project_List), 0) < 240 THEN
IF l_Project_List IS NULL THEN
l_Project_List := cur_Projects_List.name;
SELECT week_id
, start_date
INTO l_Start_Week_ID
,l_Start_date
FROM
fii_time_week
WHERE
l_As_Of_Date BETWEEN start_date AND end_date;
SELECT distinct
first_value(ID) over (ORDER BY VALUE DESC) into l_Threshold
FROM PJI_AVL_THRESHOLDS_V;
SELECT TO_CHAR(TO_DATE(MAX(TIME_ID),'j'),'RRRR/MM/DD')
INTO P_AVL_RES_DET_TBL(i).available_since
FROM pji_rm_res_f
WHERE 1=1
AND person_id = P_AVL_RES_DET_TBL(i).person_id
AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
AND calendar_type = l_Day_Calendar_Type
AND period_type_id = l_Day_Period_Type_ID
AND total_res_count <> available_res_count_bkt1_s
AND capacity_hrs <> 0;
SELECT TO_CHAR(TO_DATE(MAX(TIME_ID),'j'),'RRRR/MM/DD')
INTO P_AVL_RES_DET_TBL(i).available_since
FROM pji_rm_res_f
WHERE 1=1
AND person_id = P_AVL_RES_DET_TBL(i).person_id
AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
AND calendar_type = l_Day_Calendar_Type
AND period_type_id = l_Day_Period_Type_ID
AND total_res_count <> available_res_count_bkt2_s
AND capacity_hrs <> 0;
SELECT TO_CHAR(TO_DATE(MAX(TIME_ID),'j'),'RRRR/MM/DD')
INTO P_AVL_RES_DET_TBL(i).available_since
FROM pji_rm_res_f
WHERE 1=1
AND person_id = P_AVL_RES_DET_TBL(i).person_id
AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
AND calendar_type = l_Day_Calendar_Type
AND period_type_id = l_Day_Period_Type_ID
AND total_res_count <> available_res_count_bkt3_s
AND capacity_hrs <> 0;
SELECT TO_CHAR(TO_DATE(MAX(TIME_ID),'j'),'RRRR/MM/DD')
INTO P_AVL_RES_DET_TBL(i).available_since
FROM pji_rm_res_f
WHERE 1=1
AND person_id = P_AVL_RES_DET_TBL(i).person_id
AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
AND calendar_type = l_Day_Calendar_Type
AND period_type_id = l_Day_Period_Type_ID
AND total_res_count <> available_res_count_bkt4_s
AND capacity_hrs <> 0;
SELECT TO_CHAR(TO_DATE(MAX(TIME_ID),'j'),'RRRR/MM/DD')
INTO P_AVL_RES_DET_TBL(i).available_since
FROM pji_rm_res_f
WHERE 1=1
AND person_id = P_AVL_RES_DET_TBL(i).person_id
AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
AND calendar_type = l_Day_Calendar_Type
AND period_type_id = l_Day_Period_Type_ID
AND total_res_count <> available_res_count_bkt5_s
AND capacity_hrs <> 0;
SELECT MAX(TIME_ID)
INTO l_Available_Week
FROM pji_rm_res_f fct
, fii_time_week time
WHERE 1=1
AND time.start_date>=l_Start_Date-(7*l_Num_of_Weeks)
AND time.end_date<=l_Start_Date
AND fct.time_id = time.week_id
AND fct.person_id = P_AVL_RES_DET_TBL(i).person_id
AND fct.calendar_type = l_Week_Calendar_Type
AND fct.period_type_id = l_Week_Period_Type_ID
AND fct.total_res_count <> fct.available_res_count_bkt1_s
AND fct.capacity_hrs <> 0;
SELECT MAX(TIME_ID)
INTO l_Available_Week
FROM pji_rm_res_f fct
, fii_time_week time
WHERE 1=1
AND time.start_date>=l_Start_Date-(7*l_Num_of_Weeks)
AND time.end_date<=l_Start_Date
AND fct.time_id = time.week_id
AND fct.person_id = P_AVL_RES_DET_TBL(i).person_id
AND fct.calendar_type = l_Week_Calendar_Type
AND fct.period_type_id = l_Week_Period_Type_ID
AND fct.total_res_count <> fct.available_res_count_bkt2_s
AND fct.capacity_hrs <> 0;
SELECT MAX(TIME_ID)
INTO l_Available_Week
FROM pji_rm_res_f fct
, fii_time_week time
WHERE 1=1
AND time.start_date>=l_Start_Date-(7*l_Num_of_Weeks)
AND time.end_date<=l_Start_Date
AND fct.time_id = time.week_id
AND fct.person_id = P_AVL_RES_DET_TBL(i).person_id
AND fct.calendar_type = l_Week_Calendar_Type
AND fct.period_type_id = l_Week_Period_Type_ID
AND fct.total_res_count <> fct.available_res_count_bkt3_s
AND fct.capacity_hrs <> 0;
SELECT MAX(TIME_ID)
INTO l_Available_Week
FROM pji_rm_res_f fct
, fii_time_week time
WHERE 1=1
AND time.start_date>=l_Start_Date-(7*l_Num_of_Weeks)
AND time.end_date<=l_Start_Date
AND fct.time_id = time.week_id
AND fct.person_id = P_AVL_RES_DET_TBL(i).person_id
AND fct.calendar_type = l_Week_Calendar_Type
AND fct.period_type_id = l_Week_Period_Type_ID
AND fct.total_res_count <> fct.available_res_count_bkt4_s
AND fct.capacity_hrs <> 0;
SELECT MAX(TIME_ID)
INTO l_Available_Week
FROM pji_rm_res_f fct
, fii_time_week time
WHERE 1=1
AND time.start_date>=l_Start_Date-(7*l_Num_of_Weeks)
AND time.end_date<=l_Start_Date
AND fct.time_id = time.week_id
AND fct.person_id = P_AVL_RES_DET_TBL(i).person_id
AND fct.calendar_type = l_Week_Calendar_Type
AND fct.period_type_id = l_Week_Period_Type_ID
AND fct.total_res_count <> fct.available_res_count_bkt5_s
AND fct.capacity_hrs <> 0;
SELECT MAX(TIME_ID)
INTO l_Available_Week
FROM pji_rm_res_f fct
, fii_time_week time
WHERE 1=1
AND time.start_date>=l_Start_Date-(7*l_Max_Num_of_Weeks)
AND time.end_date<=l_Start_Date-(7*l_Num_of_Weeks)
AND fct.time_id = time.week_id
AND fct.person_id = P_AVL_RES_DET_TBL(i).person_id
AND fct.calendar_type = l_Week_Calendar_Type
AND fct.period_type_id = l_Week_Period_Type_ID
AND fct.total_res_count <> fct.available_res_count_bkt1_s
AND fct.capacity_hrs <> 0;
SELECT MAX(TIME_ID)
INTO l_Available_Week
FROM pji_rm_res_f fct
, fii_time_week time
WHERE 1=1
AND time.start_date>=l_Start_Date-(7*l_Max_Num_of_Weeks)
AND time.end_date<=l_Start_Date-(7*l_Num_of_Weeks)
AND fct.time_id = time.week_id
AND fct.person_id = P_AVL_RES_DET_TBL(i).person_id
AND fct.calendar_type = l_Week_Calendar_Type
AND fct.period_type_id = l_Week_Period_Type_ID
AND fct.total_res_count <> fct.available_res_count_bkt2_s
AND fct.capacity_hrs <> 0;
SELECT MAX(TIME_ID)
INTO l_Available_Week
FROM pji_rm_res_f fct
, fii_time_week time
WHERE 1=1
AND time.start_date>=l_Start_Date-(7*l_Max_Num_of_Weeks)
AND time.end_date<=l_Start_Date-(7*l_Num_of_Weeks)
AND fct.time_id = time.week_id
AND fct.person_id = P_AVL_RES_DET_TBL(i).person_id
AND fct.calendar_type = l_Week_Calendar_Type
AND fct.period_type_id = l_Week_Period_Type_ID
AND fct.total_res_count <> fct.available_res_count_bkt3_s
AND fct.capacity_hrs <> 0;
SELECT MAX(TIME_ID)
INTO l_Available_Week
FROM pji_rm_res_f fct
, fii_time_week time
WHERE 1=1
AND time.start_date>=l_Start_Date-(7*l_Max_Num_of_Weeks)
AND time.end_date<=l_Start_Date-(7*l_Num_of_Weeks)
AND fct.time_id = time.week_id
AND fct.person_id = P_AVL_RES_DET_TBL(i).person_id
AND fct.calendar_type = l_Week_Calendar_Type
AND fct.period_type_id = l_Week_Period_Type_ID
AND fct.total_res_count <> fct.available_res_count_bkt4_s
AND fct.capacity_hrs <> 0;
SELECT MAX(TIME_ID)
INTO l_Available_Week
FROM pji_rm_res_f fct
, fii_time_week time
WHERE 1=1
AND time.start_date>=l_Start_Date-(7*l_Max_Num_of_Weeks)
AND time.end_date<=l_Start_Date-(7*l_Num_of_Weeks)
AND fct.time_id = time.week_id
AND fct.person_id = P_AVL_RES_DET_TBL(i).person_id
AND fct.calendar_type = l_Week_Calendar_Type
AND fct.period_type_id = l_Week_Period_Type_ID
AND fct.total_res_count <> fct.available_res_count_bkt5_s
AND fct.capacity_hrs <> 0;
SELECT TO_CHAR(start_date, 'j')
, TO_CHAR(end_date, 'j')
INTO
l_To_Time_ID
, l_From_Time_ID
FROM FII_TIME_WEEK
WHERE week_id = l_Available_Week;
SELECT TO_CHAR(TO_DATE(MAX(TIME_ID),'j'),'RRRR/MM/DD')
INTO P_AVL_RES_DET_TBL(i).available_since
FROM pji_rm_res_f
WHERE 1=1
AND person_id = P_AVL_RES_DET_TBL(i).person_id
AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
AND calendar_type = l_Day_Calendar_Type
AND period_type_id = l_Day_Period_Type_ID
AND total_res_count <> available_res_count_bkt1_s
AND capacity_hrs <> 0;
SELECT TO_CHAR(TO_DATE(MAX(TIME_ID),'j'),'RRRR/MM/DD')
INTO P_AVL_RES_DET_TBL(i).available_since
FROM pji_rm_res_f
WHERE 1=1
AND person_id = P_AVL_RES_DET_TBL(i).person_id
AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
AND calendar_type = l_Day_Calendar_Type
AND period_type_id = l_Day_Period_Type_ID
AND total_res_count <> available_res_count_bkt2_s
AND capacity_hrs <> 0;
SELECT TO_CHAR(TO_DATE(MAX(TIME_ID),'j'),'RRRR/MM/DD')
INTO P_AVL_RES_DET_TBL(i).available_since
FROM pji_rm_res_f
WHERE 1=1
AND person_id = P_AVL_RES_DET_TBL(i).person_id
AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
AND calendar_type = l_Day_Calendar_Type
AND period_type_id = l_Day_Period_Type_ID
AND total_res_count <> available_res_count_bkt3_s
AND capacity_hrs <> 0;
SELECT TO_CHAR(TO_DATE(MAX(TIME_ID),'j'),'RRRR/MM/DD')
INTO P_AVL_RES_DET_TBL(i).available_since
FROM pji_rm_res_f
WHERE 1=1
AND person_id = P_AVL_RES_DET_TBL(i).person_id
AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
AND calendar_type = l_Day_Calendar_Type
AND period_type_id = l_Day_Period_Type_ID
AND total_res_count <> available_res_count_bkt4_s
AND capacity_hrs <> 0;
SELECT TO_CHAR(TO_DATE(MAX(TIME_ID),'j'),'RRRR/MM/DD')
INTO P_AVL_RES_DET_TBL(i).available_since
FROM pji_rm_res_f
WHERE 1=1
AND person_id = P_AVL_RES_DET_TBL(i).person_id
AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
AND calendar_type = l_Day_Calendar_Type
AND period_type_id = l_Day_Period_Type_ID
AND total_res_count <> available_res_count_bkt5_s
AND capacity_hrs <> 0;
SELECT TO_CHAR(TO_DATE(MIN(TIME_ID),'j'),'RRRR/MM/DD')
INTO P_AVL_RES_DET_TBL(i).available_since
FROM pji_rm_res_f
WHERE 1=1
AND person_id = P_AVL_RES_DET_TBL(i).person_id
AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
AND calendar_type = l_Day_Calendar_Type
AND period_type_id = l_Day_Period_Type_ID
AND total_res_count = available_res_count_bkt1_s
AND capacity_hrs <> 0;
SELECT TO_CHAR(TO_DATE(MIN(TIME_ID),'j'),'RRRR/MM/DD')
INTO P_AVL_RES_DET_TBL(i).available_since
FROM pji_rm_res_f
WHERE 1=1
AND person_id = P_AVL_RES_DET_TBL(i).person_id
AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
AND calendar_type = l_Day_Calendar_Type
AND period_type_id = l_Day_Period_Type_ID
AND total_res_count = available_res_count_bkt2_s
AND capacity_hrs <> 0;
SELECT TO_CHAR(TO_DATE(MIN(TIME_ID),'j'),'RRRR/MM/DD')
INTO P_AVL_RES_DET_TBL(i).available_since
FROM pji_rm_res_f
WHERE 1=1
AND person_id = P_AVL_RES_DET_TBL(i).person_id
AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
AND calendar_type = l_Day_Calendar_Type
AND period_type_id = l_Day_Period_Type_ID
AND total_res_count = available_res_count_bkt3_s
AND capacity_hrs <> 0;
SELECT TO_CHAR(TO_DATE(MIN(TIME_ID),'j'),'RRRR/MM/DD')
INTO P_AVL_RES_DET_TBL(i).available_since
FROM pji_rm_res_f
WHERE 1=1
AND person_id = P_AVL_RES_DET_TBL(i).person_id
AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
AND calendar_type = l_Day_Calendar_Type
AND period_type_id = l_Day_Period_Type_ID
AND total_res_count = available_res_count_bkt4_s
AND capacity_hrs <> 0;
SELECT TO_CHAR(TO_DATE(MIN(TIME_ID),'j'),'RRRR/MM/DD')
INTO P_AVL_RES_DET_TBL(i).available_since
FROM pji_rm_res_f
WHERE 1=1
AND person_id = P_AVL_RES_DET_TBL(i).person_id
AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
AND calendar_type = l_Day_Calendar_Type
AND period_type_id = l_Day_Period_Type_ID
AND total_res_count = available_res_count_bkt5_s
AND capacity_hrs <> 0;
SELECT TO_CHAR(TO_DATE(MIN(TIME_ID),'j'),'RRRR/MM/DD')
INTO P_AVL_RES_DET_TBL(i).available_since
FROM pji_rm_res_f
WHERE 1=1
AND person_id = P_AVL_RES_DET_TBL(i).person_id
AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
AND calendar_type = l_Day_Calendar_Type
AND period_type_id = l_Day_Period_Type_ID
AND total_res_count = available_res_count_bkt1_s
AND capacity_hrs <> 0;
SELECT TO_CHAR(TO_DATE(MIN(TIME_ID),'j'),'RRRR/MM/DD')
INTO P_AVL_RES_DET_TBL(i).available_since
FROM pji_rm_res_f
WHERE 1=1
AND person_id = P_AVL_RES_DET_TBL(i).person_id
AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
AND calendar_type = l_Day_Calendar_Type
AND period_type_id = l_Day_Period_Type_ID
AND total_res_count = available_res_count_bkt2_s
AND capacity_hrs <> 0;
SELECT TO_CHAR(TO_DATE(MIN(TIME_ID),'j'),'RRRR/MM/DD')
INTO P_AVL_RES_DET_TBL(i).available_since
FROM pji_rm_res_f
WHERE 1=1
AND person_id = P_AVL_RES_DET_TBL(i).person_id
AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
AND calendar_type = l_Day_Calendar_Type
AND period_type_id = l_Day_Period_Type_ID
AND total_res_count = available_res_count_bkt3_s
AND capacity_hrs <> 0;
SELECT TO_CHAR(TO_DATE(MIN(TIME_ID),'j'),'RRRR/MM/DD')
INTO P_AVL_RES_DET_TBL(i).available_since
FROM pji_rm_res_f
WHERE 1=1
AND person_id = P_AVL_RES_DET_TBL(i).person_id
AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
AND calendar_type = l_Day_Calendar_Type
AND period_type_id = l_Day_Period_Type_ID
AND total_res_count = available_res_count_bkt4_s
AND capacity_hrs <> 0;
SELECT TO_CHAR(TO_DATE(MIN(TIME_ID),'j'),'RRRR/MM/DD')
INTO P_AVL_RES_DET_TBL(i).available_since
FROM pji_rm_res_f
WHERE 1=1
AND person_id = P_AVL_RES_DET_TBL(i).person_id
AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
AND calendar_type = l_Day_Calendar_Type
AND period_type_id = l_Day_Period_Type_ID
AND total_res_count = available_res_count_bkt5_s
AND capacity_hrs <> 0;
SELECT week_id
, start_date
INTO l_Start_Week_ID
,l_Start_date
FROM
fii_time_week
WHERE
l_As_Of_Date BETWEEN start_date AND end_date;
SELECT TO_DATE(MAX(TIME_ID),'j')
INTO l_Available_Date
FROM pji_rm_res_f
WHERE 1=1
AND person_id = P_AVL_RES_DET_TBL(i).person_id
AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
AND calendar_type = l_Day_Calendar_Type
AND period_type_id = l_Day_Period_Type_ID
AND capacity_hrs<>available_hrs_bkt1_s
AND capacity_hrs <> 0;
SELECT MAX(TIME_ID)
INTO l_Available_Week
FROM pji_rm_res_f fct
, fii_time_week time
WHERE 1=1
AND time.start_date>=l_Start_Date-(7*l_Num_of_Weeks)
AND time.end_date<=l_Start_Date
AND fct.time_id = time.week_id
AND fct.person_id = P_AVL_RES_DET_TBL(i).person_id
AND fct.calendar_type = l_Week_Calendar_Type
AND fct.period_type_id = l_Week_Period_Type_ID
AND fct.capacity_hrs <> fct.available_hrs_bkt1_s
AND fct.capacity_hrs <> 0;
SELECT MAX(TIME_ID)
INTO l_Available_Week
FROM pji_rm_res_f fct
, fii_time_week time
WHERE 1=1
AND time.start_date>=l_Start_Date-(7*l_Max_Num_of_Weeks)
AND time.end_date<=l_Start_Date-(7*l_Num_of_Weeks)
AND fct.time_id = time.week_id
AND fct.person_id = P_AVL_RES_DET_TBL(i).person_id
AND fct.calendar_type = l_Week_Calendar_Type
AND fct.period_type_id = l_Week_Period_Type_ID
AND fct.capacity_hrs <> fct.available_hrs_bkt1_s
AND fct.capacity_hrs <> 0;
SELECT TO_CHAR(start_date, 'j')
, TO_CHAR(end_date, 'j')
INTO
l_To_Time_ID
, l_From_Time_ID
FROM FII_TIME_WEEK
WHERE week_id = l_Available_Week;
SELECT TO_DATE(MAX(TIME_ID),'j')
INTO l_Available_Date
FROM pji_rm_res_f
WHERE 1=1
AND person_id = P_AVL_RES_DET_TBL(i).person_id
AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
AND calendar_type = l_Day_Calendar_Type
AND period_type_id = l_Day_Period_Type_ID
AND capacity_hrs <> available_hrs_bkt1_s
AND capacity_hrs <> 0;
SELECT week_id
, end_date
INTO l_End_Week_ID
,l_End_date
FROM
fii_time_week
WHERE
l_As_Of_Date BETWEEN start_date AND end_date;
SELECT TO_CHAR(TO_DATE(MIN(TIME_ID),'j'),'RRRR/MM/DD')
INTO P_AVL_RES_DET_TBL(i).next_asgmt_date
FROM pji_rm_res_f
WHERE 1=1
AND person_id = P_AVL_RES_DET_TBL(i).person_id
AND time_id BETWEEN l_From_Time_ID AND l_To_Time_ID
AND calendar_type = l_Day_Calendar_Type
AND period_type_id = l_Day_Period_Type_ID
AND capacity_hrs<>available_hrs_bkt1_s
AND capacity_hrs <> 0;
SELECT MIN(TIME_ID)
INTO l_Available_Week
FROM pji_rm_res_f fct
, fii_time_week time
WHERE 1=1
AND time.start_date>=l_End_Date
AND time.end_date<=l_End_Date+(7*l_Num_of_Weeks)
AND fct.time_id = time.week_id
AND fct.person_id = P_AVL_RES_DET_TBL(i).person_id
AND fct.calendar_type = l_Week_Calendar_Type
AND fct.period_type_id = l_Week_Period_Type_ID
AND fct.capacity_hrs <> fct.available_hrs_bkt1_s
AND fct.capacity_hrs <> 0;
SELECT MIN(TIME_ID)
INTO l_Available_Week
FROM pji_rm_res_f fct
, fii_time_week time
WHERE 1=1
AND time.start_date>=l_End_Date+(7*l_Num_of_Weeks)
AND time.end_date<=l_End_Date+(7*l_Max_Num_of_Weeks)
AND fct.time_id = time.week_id
AND fct.person_id = P_AVL_RES_DET_TBL(i).person_id
AND fct.calendar_type = l_Week_Calendar_Type
AND fct.period_type_id = l_Week_Period_Type_ID
AND fct.capacity_hrs <> fct.available_hrs_bkt1_s
AND fct.capacity_hrs <> 0;
SELECT TO_CHAR(start_date, 'j')
, TO_CHAR(end_date, 'j')
INTO
l_To_Time_ID
, l_From_Time_ID
FROM FII_TIME_WEEK
WHERE week_id = l_Available_Week;
SELECT TO_CHAR(TO_DATE(MIN(TIME_ID),'j'),'RRRR/MM/DD')
INTO P_AVL_RES_DET_TBL(i).next_asgmt_date
FROM pji_rm_res_f
WHERE 1=1
AND person_id = P_AVL_RES_DET_TBL(i).person_id
AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
AND calendar_type = l_Day_Calendar_Type
AND period_type_id = l_Day_Period_Type_ID
AND capacity_hrs <> available_hrs_bkt1_s
AND capacity_hrs <> 0;