[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pmb.from_value,
pmb.to_value
BULK COLLECT INTO
l_min_bucket_tbl,
l_max_bucket_tbl
FROM pji_mt_buckets pmb
WHERE pmb.bucket_set_code = 'PJI_RES_AVL_DAYS'
ORDER BY pmb.seq;
SELECT count(*)
INTO g_no_of_user_def_threshold
FROM PJI_MT_BUCKETS
WHERE BUCKET_SET_CODE = 'PJI_RESOURCE_AVAILABILITY';
SELECT MIN(to_char(fiik.start_date,'j'))
INTO g_min_wk_j_st_date
FROM fii_time_week fiik;
SELECT rolling_weeks
INTO g_no_of_roll_week
FROM PJI_SYSTEM_SETTINGS;
SELECT count(*)
INTO l_roll_week_offset_cnt
FROM PJI_ROLL_WEEK_OFFSET;
SELECT rolling_weeks
INTO l_no_of_roll_week
FROM PJI_SYSTEM_SETTINGS;
INSERT INTO PJI_ROLL_WEEK_OFFSET
(
GLOBAL_SEQUENCE_ID,
OFFSET
)
VALUES
(
i,
-(l_no_of_roll_week - i)
);
INSERT INTO PJI_ROLL_WEEK_OFFSET
(
GLOBAL_SEQUENCE_ID,
OFFSET
)
VALUES
(
l_no_of_roll_week,
0
);
INSERT INTO PJI_ROLL_WEEK_OFFSET
(
GLOBAL_SEQUENCE_ID,
OFFSET
)
VALUES
(
l_no_of_roll_week + j,
j
);
g_exp_organization_id_in_tbl.DELETE;
g_exp_org_id_in_tbl.DELETE;
g_period_type_id_in_tbl.DELETE;
g_time_id_in_tbl.DELETE;
g_person_id_in_tbl.DELETE;
g_calendar_type_in_tbl.DELETE;
g_threshold_in_tbl.DELETE;
g_as_of_date_in_tbl.DELETE;
g_bckt_1_cs_in_tbl.DELETE;
g_bckt_2_cs_in_tbl.DELETE;
g_bckt_3_cs_in_tbl.DELETE;
g_bckt_4_cs_in_tbl.DELETE;
g_bckt_5_cs_in_tbl.DELETE;
g_bckt_1_cm_in_tbl.DELETE;
g_bckt_2_cm_in_tbl.DELETE;
g_bckt_3_cm_in_tbl.DELETE;
g_bckt_4_cm_in_tbl.DELETE;
g_bckt_5_cm_in_tbl.DELETE;
g_total_res_cnt_in_tbl.DELETE;
PROCEDURE BULK_INSERT_RS_AVL3
(
p_exp_organization_id_in_tbl IN N_TYPE_TAB,
p_exp_org_id_in_tbl IN N_TYPE_TAB,
p_period_type_id_in_tbl IN N_TYPE_TAB,
p_time_id_in_tbl IN N_TYPE_TAB,
p_person_id_in_tbl IN N_TYPE_TAB,
p_calendar_type_in_tbl IN V_TYPE_TAB,
p_threshold_in_tbl IN N_TYPE_TAB,
p_as_of_date_in_tbl IN N_TYPE_TAB,
p_bckt_1_cs_in_tbl IN N_TYPE_TAB,
p_bckt_2_cs_in_tbl IN N_TYPE_TAB,
p_bckt_3_cs_in_tbl IN N_TYPE_TAB,
p_bckt_4_cs_in_tbl IN N_TYPE_TAB,
p_bckt_5_cs_in_tbl IN N_TYPE_TAB,
p_bckt_1_cm_in_tbl IN N_TYPE_TAB,
p_bckt_2_cm_in_tbl IN N_TYPE_TAB,
p_bckt_3_cm_in_tbl IN N_TYPE_TAB,
p_bckt_4_cm_in_tbl IN N_TYPE_TAB,
p_bckt_5_cm_in_tbl IN N_TYPE_TAB,
p_total_res_cnt_in_tbl IN N_TYPE_TAB,
p_run_mode IN VARCHAR2,
p_blind_insert_flag IN VARCHAR2
)
IS
--Defining local variables
l_curr_count NUMBER := 0;
IF (p_blind_insert_flag = 'Y') THEN
IF (p_run_mode = 'OLD_FACT_RECORDS') THEN
FORALL k IN 1.. g_exp_organization_id_in_tbl.count
INSERT INTO PJI_RM_AGGR_AVL3
(
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_ORG_ID,
PERIOD_TYPE_ID,
TIME_ID ,
PERSON_ID,
CALENDAR_TYPE,
THRESHOLD,
AS_OF_DATE,
BCKT_1_CS,
BCKT_2_CS,
BCKT_3_CS,
BCKT_4_CS,
BCKT_5_CS,
BCKT_1_CM,
BCKT_2_CM,
BCKT_3_CM,
BCKT_4_CM,
BCKT_5_CM,
TOTAL_RES_COUNT
)
VALUES
(
g_exp_organization_id_in_tbl(k),
g_exp_org_id_in_tbl(k),
g_period_type_id_in_tbl(k),
g_time_id_in_tbl(k),
g_person_id_in_tbl(k),
g_calendar_type_in_tbl(k),
g_threshold_in_tbl(k),
g_as_of_date_in_tbl(k),
-g_bckt_1_cs_in_tbl(k),
-g_bckt_2_cs_in_tbl(k),
-g_bckt_3_cs_in_tbl(k),
-g_bckt_4_cs_in_tbl(k),
-g_bckt_5_cs_in_tbl(k),
-g_bckt_1_cm_in_tbl(k),
-g_bckt_2_cm_in_tbl(k),
-g_bckt_3_cm_in_tbl(k),
-g_bckt_4_cm_in_tbl(k),
-g_bckt_5_cm_in_tbl(k),
-g_total_res_cnt_in_tbl(k)
);
INSERT INTO PJI_RM_AGGR_AVL3
(
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_ORG_ID,
PERIOD_TYPE_ID,
TIME_ID ,
PERSON_ID,
CALENDAR_TYPE,
THRESHOLD,
AS_OF_DATE,
BCKT_1_CS,
BCKT_2_CS,
BCKT_3_CS,
BCKT_4_CS,
BCKT_5_CS,
BCKT_1_CM,
BCKT_2_CM,
BCKT_3_CM,
BCKT_4_CM,
BCKT_5_CM,
TOTAL_RES_COUNT
)
VALUES
(
g_exp_organization_id_in_tbl(k),
g_exp_org_id_in_tbl(k),
g_period_type_id_in_tbl(k),
g_time_id_in_tbl(k),
g_person_id_in_tbl(k),
g_calendar_type_in_tbl(k),
g_threshold_in_tbl(k),
g_as_of_date_in_tbl(k),
g_bckt_1_cs_in_tbl(k),
g_bckt_2_cs_in_tbl(k),
g_bckt_3_cs_in_tbl(k),
g_bckt_4_cs_in_tbl(k),
g_bckt_5_cs_in_tbl(k),
g_bckt_1_cm_in_tbl(k),
g_bckt_2_cm_in_tbl(k),
g_bckt_3_cm_in_tbl(k),
g_bckt_4_cm_in_tbl(k),
g_bckt_5_cm_in_tbl(k),
g_total_res_cnt_in_tbl(k)
);
INSERT INTO PJI_RM_AGGR_AVL3
(
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_ORG_ID,
PERIOD_TYPE_ID,
TIME_ID ,
PERSON_ID,
CALENDAR_TYPE,
THRESHOLD,
AS_OF_DATE,
BCKT_1_CS,
BCKT_2_CS,
BCKT_3_CS,
BCKT_4_CS,
BCKT_5_CS,
BCKT_1_CM,
BCKT_2_CM,
BCKT_3_CM,
BCKT_4_CM,
BCKT_5_CM,
TOTAL_RES_COUNT
)
VALUES
(
g_exp_organization_id_in_tbl(k),
g_exp_org_id_in_tbl(k),
g_period_type_id_in_tbl(k),
g_time_id_in_tbl(k),
g_person_id_in_tbl(k),
g_calendar_type_in_tbl(k),
g_threshold_in_tbl(k),
g_as_of_date_in_tbl(k),
-g_bckt_1_cs_in_tbl(k),
-g_bckt_2_cs_in_tbl(k),
-g_bckt_3_cs_in_tbl(k),
-g_bckt_4_cs_in_tbl(k),
-g_bckt_5_cs_in_tbl(k),
-g_bckt_1_cm_in_tbl(k),
-g_bckt_2_cm_in_tbl(k),
-g_bckt_3_cm_in_tbl(k),
-g_bckt_4_cm_in_tbl(k),
-g_bckt_5_cm_in_tbl(k),
-g_total_res_cnt_in_tbl(k)
);
INSERT INTO PJI_RM_AGGR_AVL3
(
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_ORG_ID,
PERIOD_TYPE_ID,
TIME_ID ,
PERSON_ID,
CALENDAR_TYPE,
THRESHOLD,
AS_OF_DATE,
BCKT_1_CS,
BCKT_2_CS,
BCKT_3_CS,
BCKT_4_CS,
BCKT_5_CS,
BCKT_1_CM,
BCKT_2_CM,
BCKT_3_CM,
BCKT_4_CM,
BCKT_5_CM,
TOTAL_RES_COUNT
)
VALUES
(
g_exp_organization_id_in_tbl(k),
g_exp_org_id_in_tbl(k),
g_period_type_id_in_tbl(k),
g_time_id_in_tbl(k),
g_person_id_in_tbl(k),
g_calendar_type_in_tbl(k),
g_threshold_in_tbl(k),
g_as_of_date_in_tbl(k),
g_bckt_1_cs_in_tbl(k),
g_bckt_2_cs_in_tbl(k),
g_bckt_3_cs_in_tbl(k),
g_bckt_4_cs_in_tbl(k),
g_bckt_5_cs_in_tbl(k),
g_bckt_1_cm_in_tbl(k),
g_bckt_2_cm_in_tbl(k),
g_bckt_3_cm_in_tbl(k),
g_bckt_4_cm_in_tbl(k),
g_bckt_5_cm_in_tbl(k),
g_total_res_cnt_in_tbl(k)
);
END BULK_INSERT_RS_AVL3;
p_blind_insert_flag IN VARCHAR2,
x_zero_bkt_cnt_flag OUT NOCOPY VARCHAR2
)
IS
--Defining PL/SQL Table variables for bulk insert in PJI_RM_AGGR_AVL3
l_worker_id_in_tbl N_TYPE_TAB;
IF (p_blind_insert_flag = 'Y') THEN
BULK_INSERT_RS_AVL3
(
p_exp_organization_id_in_tbl => l_exp_organization_id_in_tbl,
p_exp_org_id_in_tbl => l_exp_org_id_in_tbl,
p_period_type_id_in_tbl => l_period_type_id_in_tbl,
p_time_id_in_tbl => l_time_id_in_tbl,
p_person_id_in_tbl => l_person_id_in_tbl,
p_calendar_type_in_tbl => l_calendar_type_in_tbl,
p_threshold_in_tbl => l_threshold_in_tbl,
p_as_of_date_in_tbl => l_pd_org_st_date_in_tbl,
p_bckt_1_cs_in_tbl => l_bckt_1_cs_in_tbl,
p_bckt_2_cs_in_tbl => l_bckt_2_cs_in_tbl,
p_bckt_3_cs_in_tbl => l_bckt_3_cs_in_tbl,
p_bckt_4_cs_in_tbl => l_bckt_4_cs_in_tbl,
p_bckt_5_cs_in_tbl => l_bckt_5_cs_in_tbl,
p_bckt_1_cm_in_tbl => l_bckt_1_cm_in_tbl,
p_bckt_2_cm_in_tbl => l_bckt_2_cm_in_tbl,
p_bckt_3_cm_in_tbl => l_bckt_3_cm_in_tbl,
p_bckt_4_cm_in_tbl => l_bckt_4_cm_in_tbl,
p_bckt_5_cm_in_tbl => l_bckt_5_cm_in_tbl,
p_total_res_cnt_in_tbl => l_total_res_cnt_in_tbl,
p_run_mode => p_run_mode,
p_blind_insert_flag => p_blind_insert_flag
);
--BULK INSERT at the end of processing
FOR j in 1.. g_no_of_user_def_threshold
LOOP
--DBMS_OUTPUT.PUT_LINE('3');
--Call the bulk insert to insert rows for this particular
--period and person id for all thresholds
BULK_INSERT_RS_AVL3
(
p_exp_organization_id_in_tbl => l_exp_organization_id_in_tbl,
p_exp_org_id_in_tbl => l_exp_org_id_in_tbl,
p_period_type_id_in_tbl => l_period_type_id_in_tbl,
p_time_id_in_tbl => l_time_id_in_tbl,
p_person_id_in_tbl => l_person_id_in_tbl,
p_calendar_type_in_tbl => l_calendar_type_in_tbl,
p_threshold_in_tbl => l_threshold_in_tbl,
p_as_of_date_in_tbl => l_pd_org_st_date_in_tbl,
p_bckt_1_cs_in_tbl => l_bckt_1_cs_in_tbl,
p_bckt_2_cs_in_tbl => l_bckt_2_cs_in_tbl,
p_bckt_3_cs_in_tbl => l_bckt_3_cs_in_tbl,
p_bckt_4_cs_in_tbl => l_bckt_4_cs_in_tbl,
p_bckt_5_cs_in_tbl => l_bckt_5_cs_in_tbl,
p_bckt_1_cm_in_tbl => l_bckt_1_cm_in_tbl,
p_bckt_2_cm_in_tbl => l_bckt_2_cm_in_tbl,
p_bckt_3_cm_in_tbl => l_bckt_3_cm_in_tbl,
p_bckt_4_cm_in_tbl => l_bckt_4_cm_in_tbl,
p_bckt_5_cm_in_tbl => l_bckt_5_cm_in_tbl,
p_total_res_cnt_in_tbl => l_total_res_cnt_in_tbl,
p_run_mode => p_run_mode,
p_blind_insert_flag => p_blind_insert_flag
);
BULK_INSERT_RS_AVL3
(
p_exp_organization_id_in_tbl => l_exp_organization_id_in_tbl,
p_exp_org_id_in_tbl => l_exp_org_id_in_tbl,
p_period_type_id_in_tbl => l_period_type_id_in_tbl,
p_time_id_in_tbl => l_time_id_in_tbl,
p_person_id_in_tbl => l_person_id_in_tbl,
p_calendar_type_in_tbl => l_calendar_type_in_tbl,
p_threshold_in_tbl => l_threshold_in_tbl,
p_as_of_date_in_tbl => l_as_of_date_in_tbl,
p_bckt_1_cs_in_tbl => l_bckt_1_cs_in_tbl,
p_bckt_2_cs_in_tbl => l_bckt_2_cs_in_tbl,
p_bckt_3_cs_in_tbl => l_bckt_3_cs_in_tbl,
p_bckt_4_cs_in_tbl => l_bckt_4_cs_in_tbl,
p_bckt_5_cs_in_tbl => l_bckt_5_cs_in_tbl,
p_bckt_1_cm_in_tbl => l_bckt_1_cm_in_tbl,
p_bckt_2_cm_in_tbl => l_bckt_2_cm_in_tbl,
p_bckt_3_cm_in_tbl => l_bckt_3_cm_in_tbl,
p_bckt_4_cm_in_tbl => l_bckt_4_cm_in_tbl,
p_bckt_5_cm_in_tbl => l_bckt_5_cm_in_tbl,
p_total_res_cnt_in_tbl => l_total_res_cnt_in_tbl,
p_run_mode => p_run_mode,
p_blind_insert_flag => p_blind_insert_flag
);
BULK_INSERT_RS_AVL3
(
p_exp_organization_id_in_tbl => l_exp_organization_id_in_tbl,
p_exp_org_id_in_tbl => l_exp_org_id_in_tbl,
p_period_type_id_in_tbl => l_period_type_id_in_tbl,
p_time_id_in_tbl => l_time_id_in_tbl,
p_person_id_in_tbl => l_person_id_in_tbl,
p_calendar_type_in_tbl => l_calendar_type_in_tbl,
p_threshold_in_tbl => l_threshold_in_tbl,
p_as_of_date_in_tbl => l_pd_org_st_date_in_tbl,
p_bckt_1_cs_in_tbl => l_bckt_1_cs_in_tbl,
p_bckt_2_cs_in_tbl => l_bckt_2_cs_in_tbl,
p_bckt_3_cs_in_tbl => l_bckt_3_cs_in_tbl,
p_bckt_4_cs_in_tbl => l_bckt_4_cs_in_tbl,
p_bckt_5_cs_in_tbl => l_bckt_5_cs_in_tbl,
p_bckt_1_cm_in_tbl => l_bckt_1_cm_in_tbl,
p_bckt_2_cm_in_tbl => l_bckt_2_cm_in_tbl,
p_bckt_3_cm_in_tbl => l_bckt_3_cm_in_tbl,
p_bckt_4_cm_in_tbl => l_bckt_4_cm_in_tbl,
p_bckt_5_cm_in_tbl => l_bckt_5_cm_in_tbl,
p_total_res_cnt_in_tbl => l_total_res_cnt_in_tbl,
p_run_mode => p_run_mode,
p_blind_insert_flag => p_blind_insert_flag
);
gw_exp_organization_id_in_tbl.DELETE;
gw_exp_org_id_in_tbl.DELETE;
gw_period_type_id_in_tbl.DELETE;
gw_time_id_in_tbl.DELETE;
gw_person_id_in_tbl.DELETE;
gw_calendar_type_in_tbl.DELETE;
gw_threshold_in_tbl.DELETE;
gw_availability_in_tbl.DELETE;
gw_total_res_cnt_in_tbl.DELETE;
PROCEDURE BULK_INSERT_RS_AVL4
(
p_exp_organization_id_in_tbl IN N_TYPE_TAB,
p_exp_org_id_in_tbl IN N_TYPE_TAB,
p_period_type_id_in_tbl IN N_TYPE_TAB,
p_time_id_in_tbl IN N_TYPE_TAB,
p_person_id_in_tbl IN N_TYPE_TAB,
p_calendar_type_in_tbl IN V_TYPE_TAB,
p_threshold_in_tbl IN N_TYPE_TAB,
p_as_of_date_in_tbl IN N_TYPE_TAB,
p_availability_in_tbl IN N_TYPE_TAB,
p_total_res_cnt_in_tbl IN N_TYPE_TAB,
p_run_mode IN VARCHAR2,
p_blind_insert_flag IN VARCHAR2
)
IS
--Defining local variables
l_curr_count NUMBER := 0;
IF (p_blind_insert_flag = 'Y') THEN
IF (p_run_mode = 'OLD_FACT_RECORDS') THEN
FORALL k IN 1.. gw_exp_organization_id_in_tbl.count
INSERT INTO PJI_RM_AGGR_AVL4
(
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_ORG_ID,
PERIOD_TYPE_ID,
TIME_ID ,
PERSON_ID,
CALENDAR_TYPE,
THRESHOLD,
AS_OF_DATE,
AVAILABILITY,
TOTAL_RES_COUNT
)
VALUES
(
gw_exp_organization_id_in_tbl(k),
gw_exp_org_id_in_tbl(k),
gw_period_type_id_in_tbl(k),
gw_time_id_in_tbl(k),
gw_person_id_in_tbl(k),
gw_calendar_type_in_tbl(k),
gw_threshold_in_tbl(k),
gw_as_of_date_in_tbl(k),
-gw_availability_in_tbl(k),
-gw_total_res_cnt_in_tbl(k)
);
INSERT INTO PJI_RM_AGGR_AVL4
(
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_ORG_ID,
PERIOD_TYPE_ID,
TIME_ID ,
PERSON_ID,
CALENDAR_TYPE,
THRESHOLD,
AS_OF_DATE,
AVAILABILITY,
TOTAL_RES_COUNT
)
VALUES
(
gw_exp_organization_id_in_tbl(k),
gw_exp_org_id_in_tbl(k),
gw_period_type_id_in_tbl(k),
gw_time_id_in_tbl(k),
gw_person_id_in_tbl(k),
gw_calendar_type_in_tbl(k),
gw_threshold_in_tbl(k),
gw_as_of_date_in_tbl(k),
gw_availability_in_tbl(k),
gw_total_res_cnt_in_tbl(k)
);
INSERT INTO PJI_RM_AGGR_AVL4
(
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_ORG_ID,
PERIOD_TYPE_ID,
TIME_ID ,
PERSON_ID,
CALENDAR_TYPE,
THRESHOLD,
AS_OF_DATE,
AVAILABILITY,
TOTAL_RES_COUNT
)
VALUES
(
gw_exp_organization_id_in_tbl(k),
gw_exp_org_id_in_tbl(k),
gw_period_type_id_in_tbl(k),
gw_time_id_in_tbl(k),
gw_person_id_in_tbl(k),
gw_calendar_type_in_tbl(k),
gw_threshold_in_tbl(k),
gw_as_of_date_in_tbl(k),
-gw_availability_in_tbl(k),
-gw_total_res_cnt_in_tbl(k)
);
INSERT INTO PJI_RM_AGGR_AVL4
(
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_ORG_ID,
PERIOD_TYPE_ID,
TIME_ID ,
PERSON_ID,
CALENDAR_TYPE,
THRESHOLD,
AS_OF_DATE,
AVAILABILITY,
TOTAL_RES_COUNT
)
VALUES
(
gw_exp_organization_id_in_tbl(k),
gw_exp_org_id_in_tbl(k),
gw_period_type_id_in_tbl(k),
gw_time_id_in_tbl(k),
gw_person_id_in_tbl(k),
gw_calendar_type_in_tbl(k),
gw_threshold_in_tbl(k),
gw_as_of_date_in_tbl(k),
gw_availability_in_tbl(k),
gw_total_res_cnt_in_tbl(k)
);
END BULK_INSERT_RS_AVL4;
p_blind_insert_flag IN VARCHAR2,
x_zero_bkt_cnt_flag OUT NOCOPY VARCHAR2
)
IS
--Defining PL/SQL Table variables for bulk insert in PJI_RM_AGGR_AVL4
l_exp_organization_id_in_tbl N_TYPE_TAB;
IF (p_blind_insert_flag = 'Y') THEN
BULK_INSERT_RS_AVL4
(
p_exp_organization_id_in_tbl => l_exp_organization_id_in_tbl,
p_exp_org_id_in_tbl => l_exp_org_id_in_tbl,
p_period_type_id_in_tbl => l_period_type_id_in_tbl,
p_time_id_in_tbl => l_time_id_in_tbl,
p_person_id_in_tbl => l_person_id_in_tbl,
p_calendar_type_in_tbl => l_calendar_type_in_tbl,
p_threshold_in_tbl => l_threshold_in_tbl,
p_as_of_date_in_tbl => l_pd_org_st_date_in_tbl,
p_availability_in_tbl => l_availability_in_tbl,
p_total_res_cnt_in_tbl => l_total_res_cnt_in_tbl,
p_run_mode => p_run_mode,
p_blind_insert_flag => p_blind_insert_flag
);
--BULK INSERT at the end of processing
FOR j in 1.. g_no_of_user_def_threshold
LOOP
l_exp_organization_id_in_tbl(j) :=p_exp_organization_id;
--Call the bulk insert to insert rows for this particular
--period and person id for all thresholds
BULK_INSERT_RS_AVL4
(
p_exp_organization_id_in_tbl => l_exp_organization_id_in_tbl,
p_exp_org_id_in_tbl => l_exp_org_id_in_tbl,
p_period_type_id_in_tbl => l_period_type_id_in_tbl,
p_time_id_in_tbl => l_time_id_in_tbl,
p_person_id_in_tbl => l_person_id_in_tbl,
p_calendar_type_in_tbl => l_calendar_type_in_tbl,
p_threshold_in_tbl => l_threshold_in_tbl,
p_as_of_date_in_tbl => l_pd_org_st_date_in_tbl,
p_availability_in_tbl => l_availability_in_tbl,
p_total_res_cnt_in_tbl => l_total_res_cnt_in_tbl,
p_run_mode => p_run_mode,
p_blind_insert_flag => p_blind_insert_flag
);
BULK_INSERT_RS_AVL4
(
p_exp_organization_id_in_tbl => l_exp_organization_id_in_tbl,
p_exp_org_id_in_tbl => l_exp_org_id_in_tbl,
p_period_type_id_in_tbl => l_period_type_id_in_tbl,
p_time_id_in_tbl => l_time_id_in_tbl,
p_person_id_in_tbl => l_person_id_in_tbl,
p_calendar_type_in_tbl => l_calendar_type_in_tbl,
p_threshold_in_tbl => l_threshold_in_tbl,
p_as_of_date_in_tbl => l_as_of_date_in_tbl,
p_availability_in_tbl => l_availability_in_tbl,
p_total_res_cnt_in_tbl => l_total_res_cnt_in_tbl,
p_run_mode => p_run_mode,
p_blind_insert_flag => p_blind_insert_flag
);
BULK_INSERT_RS_AVL4
(
p_exp_organization_id_in_tbl => l_exp_organization_id_in_tbl,
p_exp_org_id_in_tbl => l_exp_org_id_in_tbl,
p_period_type_id_in_tbl => l_period_type_id_in_tbl,
p_time_id_in_tbl => l_time_id_in_tbl,
p_person_id_in_tbl => l_person_id_in_tbl,
p_calendar_type_in_tbl => l_calendar_type_in_tbl,
p_threshold_in_tbl => l_threshold_in_tbl,
p_as_of_date_in_tbl => l_pd_org_st_date_in_tbl,
p_availability_in_tbl => l_availability_in_tbl,
p_total_res_cnt_in_tbl => l_total_res_cnt_in_tbl,
p_run_mode => p_run_mode,
p_blind_insert_flag => p_blind_insert_flag
);
SELECT
WORKER_ID,
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_ORG_ID,
PERSON_ID,
TIME_ID,
WEEK_ID,
ENT_PERIOD,
ENT_QTR ,
GL_PERIOD,
GL_QTR ,
ROLL_X_WEEK_1,
ROLL_X_WEEK_2,
ROLL_X_WEEK_3,
ROLL_X_WEEK_4,
ROLL_X_WEEK_5,
ROLL_X_WEEK_6,
ROLL_X_WEEK_7,
ROLL_X_WEEK_8,
ROLL_X_WEEK_9,
ROLL_X_WEEK_10,
ROLL_X_WEEK_11,
ROLL_X_WEEK_12,
ROLL_X_WEEK_13,
AVL_RES_COUNT_BKT1,
AVL_RES_COUNT_BKT2,
AVL_RES_COUNT_BKT3,
AVL_RES_COUNT_BKT4,
AVL_RES_COUNT_BKT5
FROM
PJI_RM_AGGR_AVL2 avl2
WHERE avl2.person_id = p_person_id
UNION ALL
SELECT
p_worker_id as WORKER_ID,
power(2,49) as EXPENDITURE_ORGANIZATION_ID,
power(2,49) as EXPENDITURE_ORG_ID,
power(2,49) as PERSON_ID,
power(2,49) as TIME_ID,
-power(2,49) as WEEK_ID,
-power(2,49) as ENT_PERIOD,
-power(2,49) as ENT_QTR ,
-power(2,49) as GL_PERIOD,
-power(2,49) as GL_QTR ,
365243 as ROLL_X_WEEK_1,
365243 as ROLL_X_WEEK_2,
365243 as ROLL_X_WEEK_3,
365243 as ROLL_X_WEEK_4,
365243 as ROLL_X_WEEK_5,
365243 as ROLL_X_WEEK_6,
365243 as ROLL_X_WEEK_7,
365243 as ROLL_X_WEEK_8,
365243 as ROLL_X_WEEK_9,
365243 as ROLL_X_WEEK_10,
365243 as ROLL_X_WEEK_11,
365243 as ROLL_X_WEEK_12,
365243 as ROLL_X_WEEK_13,
-power(2,0) as AVL_RES_COUNT_BKT1,
-power(2,0) as AVL_RES_COUNT_BKT2,
-power(2,0) as AVL_RES_COUNT_BKT3,
-power(2,0) as AVL_RES_COUNT_BKT4,
-power(2,0) as AVL_RES_COUNT_BKT5
FROM
DUAL
ORDER BY TIME_ID,
EXPENDITURE_ORGANIZATION_ID;
delete from PJI_RM_AGGR_AVL1 where worker_id = p_worker_id;
delete from PJI_RM_AGGR_AVL2 where worker_id = p_worker_id;
So, for Enterprise periods, we select from PJI_RM_AGGR_RES2
and join it to FII_TIME_DAY to get appropriate distinct records
for the periods/quarters/weeks affected. Similarly, for GL periods,
we select from PJI_RM_AGGR_RES2 and join it to
FII_TIME_CAL_DAY_MV and PJI_ORG_EXTR_INFO to get appropriate
distinct records for the periods/quarters affected.
*/
/*
Populate AVL1 only for old fact records
and use the stored values from AVL5
to get values in AVL1 for the new (after
the change is applied) fact records
*/
IF (p_run_mode = 'OLD_FACT_RECORDS') THEN
--Insert into TMP1 table
INSERT INTO PJI_RM_AGGR_AVL1
(
EXPENDITURE_ORG_ID,
WORKER_ID,
PERSON_ID,
CALENDAR_TYPE,
GL_CALENDAR_ID,
PERIOD_TYPE_ID,
PERIOD_ID,
PERIOD_TYPE
)
SELECT
cur1.expenditure_org_id as expenditure_org_id,
cur1.worker_id as worker_id,
cur1.person_id as person_id,
cur1.calendar_type as calendar_type,
cur1.gl_calendar_id as gl_calendar_id,
cur1.period_type_id as period_type_id,
cur1.period_id as period_id,
cur1.period_type as period_type
FROM
(
SELECT /*+ no_merge(rt1) */
DISTINCT
rt1.expenditure_org_id as expenditure_org_id,
rt1.worker_id as worker_id,
rt1.person_id as person_id,
rt1.calendar_type as calendar_type,
rt1.gl_calendar_id as gl_calendar_id,
case when rt2.tmp_index = 1 then
16
when rt2.tmp_index = 2 then
64
end period_type_id,
case when rt2.tmp_index = 1 then
rt1.week_id
when rt2.tmp_index = 2 then
rt1.qtr_id
end period_id,
case when rt2.tmp_index = 1 then
'W'
when rt2.tmp_index = 2 then
'E'
end period_type
FROM
(
SELECT /*+ ordered
index(tmp2. PJI_RM_AGGR_RES2_N1)
full(fiit) use_hash(fiit) */
DISTINCT
tmp2.expenditure_org_id as expenditure_org_id,
p_worker_id as worker_id,
tmp2.person_id as person_id,
tmp2.calendar_type as calendar_type,
tmp2.gl_calendar_id as gl_calendar_id,
fiit.ent_qtr_id as qtr_id,
(to_char(fiit.week_start_date,'j') - g_min_wk_j_st_date)/7 + 1 as week_id
FROM
PJI_RM_AGGR_RES2 tmp2,
FII_TIME_DAY fiit
WHERE
tmp2.person_id = p_person_id
and tmp2.time_id = fiit.report_date_julian
and (
ABS(nvl(tmp2.capacity_hrs, 0)) + ABS(nvl(tmp2.available_res_count_bkt1_s, 0)) +
ABS(nvl(tmp2.available_res_count_bkt2_s, 0)) + ABS(nvl(tmp2.available_res_count_bkt3_s, 0)) +
ABS(nvl(tmp2.available_res_count_bkt4_s, 0)) + ABS(nvl(tmp2.available_res_count_bkt5_s, 0))
) > 0
) rt1,
(
SELECT 1 as tmp_index from dual
UNION ALL
SELECT 2 as tmp_index from dual
) rt2
UNION ALL
SELECT /*+ ordered
index(tmp2, PJI_RM_AGGR_RES2_N1)
index(fiit, FII_TIME_CAL_DAY_MV_U1) */
DISTINCT
tmp2.expenditure_org_id as expenditure_org_id,
p_worker_id as worker_id,
tmp2.person_id as person_id,
tmp2.calendar_type as calendar_type,
tmp2.gl_calendar_id as gl_calendar_id,
64 as period_type_id,
fiit.cal_qtr_id as period_id,
'G' as period_type
FROM
PJI_RM_AGGR_RES2 tmp2,
FII_TIME_CAL_DAY_MV fiit
WHERE
tmp2.person_id = p_person_id
and to_date(to_char(tmp2.time_id), 'J') = fiit.report_date
and (
ABS(nvl(tmp2.capacity_hrs, 0)) + ABS(nvl(tmp2.available_res_count_bkt1_s, 0)) +
ABS(nvl(tmp2.available_res_count_bkt2_s, 0)) + ABS(nvl(tmp2.available_res_count_bkt3_s, 0)) +
ABS(nvl(tmp2.available_res_count_bkt4_s, 0)) + ABS(nvl(tmp2.available_res_count_bkt5_s, 0))
)> 0
and tmp2.gl_calendar_id = fiit.calendar_id
) cur1;
INSERT INTO PJI_RM_AGGR_AVL5
(
EXPENDITURE_ORG_ID,
PERSON_ID,
CALENDAR_TYPE,
GL_CALENDAR_ID,
PERIOD_TYPE_ID,
PERIOD_ID,
PERIOD_TYPE
)
SELECT
avl1.expenditure_org_id as expenditure_org_id,
avl1.person_id as person_id,
avl1.calendar_type as calendar_type,
avl1.gl_calendar_id as gl_calendar_id,
avl1.period_type_id as period_type_id,
avl1.period_id as period_id,
avl1.period_type as period_type
FROM PJI_RM_AGGR_AVL1 avl1;
INSERT INTO PJI_RM_AGGR_AVL1
(
EXPENDITURE_ORG_ID,
WORKER_ID,
PERSON_ID,
CALENDAR_TYPE,
GL_CALENDAR_ID,
PERIOD_TYPE_ID,
PERIOD_ID,
PERIOD_TYPE
)
SELECT
avl5.expenditure_org_id as expenditure_org_id,
p_worker_id as worker_id,
avl5.person_id as person_id,
avl5.calendar_type as calendar_type,
avl5.gl_calendar_id as gl_calendar_id,
avl5.period_type_id as period_type_id,
avl5.period_id as period_id,
avl5.period_type as period_type
FROM PJI_RM_AGGR_AVL5 avl5
WHERE avl5.person_id = p_person_id;
INSERT INTO PJI_RM_AGGR_AVL2
(
WORKER_ID,
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_ORG_ID,
PERSON_ID,
TIME_ID,
WEEK_ID,
ENT_PERIOD,
ENT_QTR ,
GL_PERIOD,
GL_QTR ,
ROLL_X_WEEK_1,
ROLL_X_WEEK_2,
ROLL_X_WEEK_3,
ROLL_X_WEEK_4,
ROLL_X_WEEK_5,
ROLL_X_WEEK_6,
ROLL_X_WEEK_7,
ROLL_X_WEEK_8,
ROLL_X_WEEK_9,
ROLL_X_WEEK_10,
ROLL_X_WEEK_11,
ROLL_X_WEEK_12,
ROLL_X_WEEK_13,
AVL_RES_COUNT_BKT1,
AVL_RES_COUNT_BKT2,
AVL_RES_COUNT_BKT3,
AVL_RES_COUNT_BKT4,
AVL_RES_COUNT_BKT5
)
SELECT /*+ full(cur2) use_hash(cur2) index(fct, PJI_RM_RES_F_N2) */
cur2.worker_id as worker_id,
fct.expenditure_organization_id,
fct.expenditure_org_id,
cur2.person_id as person_id,
cur2.time_id as time_id,
cur2.week_id as week_id,
cur2.ent_period as ent_period,
cur2.ent_qtr as ent_qtr,
cur2.gl_period as gl_period,
cur2.gl_qtr as gl_qtr,
cur2.ROLL_X_WEEK_1 as ROLL_X_WEEK_1,
cur2.ROLL_X_WEEK_2 as ROLL_X_WEEK_2,
cur2.ROLL_X_WEEK_3 as ROLL_X_WEEK_3,
cur2.ROLL_X_WEEK_4 as ROLL_X_WEEK_4,
cur2.ROLL_X_WEEK_5 as ROLL_X_WEEK_5,
cur2.ROLL_X_WEEK_6 as ROLL_X_WEEK_6,
cur2.ROLL_X_WEEK_7 as ROLL_X_WEEK_7,
cur2.ROLL_X_WEEK_8 as ROLL_X_WEEK_8,
cur2.ROLL_X_WEEK_9 as ROLL_X_WEEK_9,
cur2.ROLL_X_WEEK_10 as ROLL_X_WEEK_10,
cur2.ROLL_X_WEEK_11 as ROLL_X_WEEK_11,
cur2.ROLL_X_WEEK_12 as ROLL_X_WEEK_12,
cur2.ROLL_X_WEEK_13 as ROLL_X_WEEK_13,
NVL(fct.AVAILABLE_RES_COUNT_BKT1_S, 0) as AVL_RES_COUNT_BKT1,
NVL(fct.AVAILABLE_RES_COUNT_BKT2_S, 0) as AVL_RES_COUNT_BKT2,
NVL(fct.AVAILABLE_RES_COUNT_BKT3_S, 0) as AVL_RES_COUNT_BKT3,
NVL(fct.AVAILABLE_RES_COUNT_BKT4_S, 0) as AVL_RES_COUNT_BKT4,
NVL(fct.AVAILABLE_RES_COUNT_BKT5_S, 0) as AVL_RES_COUNT_BKT5
FROM
(
SELECT
cur1.worker_id as worker_id,
cur1.person_id as person_id,
cur1.time_id as time_id,
sum(cur1.week_id) as week_id,
sum(cur1.ent_period) as ent_period,
sum(cur1.ent_qtr) as ent_qtr,
sum(cur1.gl_period) as gl_period,
sum(cur1.gl_qtr) as gl_qtr,
sum(cur1.ROLL_X_WEEK_1) as ROLL_X_WEEK_1,
sum(cur1.ROLL_X_WEEK_2) as ROLL_X_WEEK_2,
sum(cur1.ROLL_X_WEEK_3) as ROLL_X_WEEK_3,
sum(cur1.ROLL_X_WEEK_4) as ROLL_X_WEEK_4,
sum(cur1.ROLL_X_WEEK_5) as ROLL_X_WEEK_5,
sum(cur1.ROLL_X_WEEK_6) as ROLL_X_WEEK_6,
sum(cur1.ROLL_X_WEEK_7) as ROLL_X_WEEK_7,
sum(cur1.ROLL_X_WEEK_8) as ROLL_X_WEEK_8,
sum(cur1.ROLL_X_WEEK_9) as ROLL_X_WEEK_9,
sum(cur1.ROLL_X_WEEK_10) as ROLL_X_WEEK_10,
sum(cur1.ROLL_X_WEEK_11) as ROLL_X_WEEK_11,
sum(cur1.ROLL_X_WEEK_12) as ROLL_X_WEEK_12,
sum(cur1.ROLL_X_WEEK_13) as ROLL_X_WEEK_13
FROM
(
SELECT /*+ cardinality(avl_tmp1, 1) cache(fiit) */ DISTINCT
p_worker_id as worker_id,
avl_tmp1.person_id as person_id,
fiit.report_date_julian as time_id,
0 as week_id,
fiit.ent_period_id as ent_period,
fiit.ent_qtr_id as ent_qtr,
0 as gl_period,
0 as gl_qtr,
0 as ROLL_X_WEEK_1,
0 as ROLL_X_WEEK_2,
0 as ROLL_X_WEEK_3,
0 as ROLL_X_WEEK_4,
0 as ROLL_X_WEEK_5,
0 as ROLL_X_WEEK_6,
0 as ROLL_X_WEEK_7,
0 as ROLL_X_WEEK_8,
0 as ROLL_X_WEEK_9,
0 as ROLL_X_WEEK_10,
0 as ROLL_X_WEEK_11,
0 as ROLL_X_WEEK_12,
0 as ROLL_X_WEEK_13
FROM
PJI_RM_AGGR_AVL1 avl_tmp1,
FII_TIME_DAY fiit
WHERE
avl_tmp1.PERIOD_ID = fiit.ENT_QTR_ID
and avl_tmp1.period_type_id = 64
and avl_tmp1.period_type = 'E'
and avl_tmp1.worker_id = p_worker_id
UNION ALL
SELECT /*+ cardinality(avl_tmp1, 1) */
DISTINCT
p_worker_id as worker_id,
avl_tmp1.person_id as person_id,
fiit.report_date_julian as time_id,
0 as week_id,
0 as ent_period,
0 as ent_qtr,
fiit.cal_period_id as gl_period,
fiit.cal_qtr_id as gl_qtr,
0 as ROLL_X_WEEK_1,
0 as ROLL_X_WEEK_2,
0 as ROLL_X_WEEK_3,
0 as ROLL_X_WEEK_4,
0 as ROLL_X_WEEK_5,
0 as ROLL_X_WEEK_6,
0 as ROLL_X_WEEK_7,
0 as ROLL_X_WEEK_8,
0 as ROLL_X_WEEK_9,
0 as ROLL_X_WEEK_10,
0 as ROLL_X_WEEK_11,
0 as ROLL_X_WEEK_12,
0 as ROLL_X_WEEK_13
FROM
PJI_RM_AGGR_AVL1 avl_tmp1,
FII_TIME_CAL_PERIOD per,
FII_TIME_CAL_DAY_MV fiit
WHERE
avl_tmp1.PERIOD_ID = per.CAL_QTR_ID
and avl_tmp1.gl_calendar_id = per.calendar_id
and per.cal_period_id = fiit.cal_period_id
and per.calendar_id = fiit.calendar_id
and avl_tmp1.period_type_id = 64
and avl_tmp1.period_type = 'G'
and avl_tmp1.worker_id = p_worker_id
UNION ALL
SELECT
cur.worker_id as worker_id,
cur.person_id as person_id,
fiid.report_date_julian as time_id,
cur.week_id as week_id,
0 as ent_period,
0 as ent_qtr,
0 as gl_period,
0 as gl_qtr,
cur.rw1 as ROLL_X_WEEK_1,
cur.rw2 as ROLL_X_WEEK_2,
cur.rw3 as ROLL_X_WEEK_3,
cur.rw4 as ROLL_X_WEEK_4,
cur.rw5 as ROLL_X_WEEK_5,
cur.rw6 as ROLL_X_WEEK_6,
cur.rw7 as ROLL_X_WEEK_7,
cur.rw8 as ROLL_X_WEEK_8,
cur.rw9 as ROLL_X_WEEK_9,
cur.rw10 as ROLL_X_WEEK_10,
cur.rw11 as ROLL_X_WEEK_11,
cur.rw12 as ROLL_X_WEEK_12,
cur.rw13 as ROLL_X_WEEK_13
FROM
(
SELECT /*+ no_merge cardinality(1) */
cur4.worker_id as worker_id,
cur4.person_id as person_id,
fweek.week_id as period_id,
fweek1.week_id as week_id,
cur4.rw1 as rw1,
cur4.rw2 as rw2,
cur4.rw3 as rw3,
cur4.rw4 as rw4,
cur4.rw5 as rw5,
cur4.rw6 as rw6,
cur4.rw7 as rw7,
cur4.rw8 as rw8,
cur4.rw9 as rw9,
cur4.rw10 as rw10,
cur4.rw11 as rw11,
cur4.rw12 as rw12,
cur4.rw13 as rw13
FROM
(
SELECT
cur5.worker_id as worker_id,
cur5.person_id as person_id,
cur5.sequence_id as period_id,
sum(NVL(cur5.week_id,0)) as week_id,
DECODE(sign(g_no_of_roll_week-0),1,( (FLOOR(( cur5.sequence_id - 0 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 0 ) * decode( sign(sum(cur5.rw1_flag)), 0, NULL, 1),null) rw1
, DECODE(sign(g_no_of_roll_week-1),1,( (FLOOR(( cur5.sequence_id - 1 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 1 ) * decode( sign(sum(cur5.rw2_flag)), 0, NULL, 1),null) rw2
, DECODE(sign(g_no_of_roll_week-2),1,( (FLOOR(( cur5.sequence_id - 2 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 2 ) * decode( sign(sum(cur5.rw3_flag)), 0, NULL, 1),null) rw3
, DECODE(sign(g_no_of_roll_week-3),1,( (FLOOR(( cur5.sequence_id - 3 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 3 ) * decode( sign(sum(cur5.rw4_flag)), 0, NULL, 1),null) rw4
, DECODE(sign(g_no_of_roll_week-4),1,( (FLOOR(( cur5.sequence_id - 4 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 4 ) * decode( sign(sum(cur5.rw5_flag)), 0, NULL, 1),null) rw5
, DECODE(sign(g_no_of_roll_week-5),1,( (FLOOR(( cur5.sequence_id - 5 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 5 ) * decode( sign(sum(cur5.rw6_flag)), 0, NULL, 1),null) rw6
, DECODE(sign(g_no_of_roll_week-6),1,( (FLOOR(( cur5.sequence_id - 6 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 6 ) * decode( sign(sum(cur5.rw7_flag)), 0, NULL, 1),null) rw7
, DECODE(sign(g_no_of_roll_week-7),1,( (FLOOR(( cur5.sequence_id - 7 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 7 ) * decode( sign(sum(cur5.rw8_flag)), 0, NULL, 1),null) rw8
, DECODE(sign(g_no_of_roll_week-8),1,( (FLOOR(( cur5.sequence_id - 8 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 8 ) * decode( sign(sum(cur5.rw9_flag)), 0, NULL, 1),null) rw9
, DECODE(sign(g_no_of_roll_week-9),1,( (FLOOR(( cur5.sequence_id - 9 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 9 ) * decode( sign(sum(cur5.rw10_flag)), 0, NULL, 1),null) rw10
, DECODE(sign(g_no_of_roll_week-10),1,( (FLOOR(( cur5.sequence_id - 10 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 10 ) * decode( sign(sum(cur5.rw11_flag)), 0, NULL, 1),null) rw11
, DECODE(sign(g_no_of_roll_week-11),1,( (FLOOR(( cur5.sequence_id - 11 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 11 ) * decode( sign(sum(cur5.rw12_flag)), 0, NULL, 1),null) rw12
, DECODE(sign(g_no_of_roll_week-12),1,( (FLOOR(( cur5.sequence_id - 12 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 12 ) * decode( sign(sum(cur5.rw13_flag)), 0, NULL, 1),null) rw13
FROM
(
SELECT DISTINCT
p_worker_id as worker_id,
w.person_id as person_id,
w.period_id + rw.offset sequence_id
, case when rw.offset = 0 then
w.period_id
else
null
end as week_id
, DECODE(sign(g_no_of_roll_week-0),1,1-abs(sign( FLOOR((w.period_id-( (FLOOR(( w.period_id + rw.offset - 0 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 0 ))/g_no_of_roll_week) )),null) rw1_flag
, DECODE(sign(g_no_of_roll_week-1),1,1-abs(sign( FLOOR((w.period_id-( (FLOOR(( w.period_id + rw.offset - 1 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 1 ))/g_no_of_roll_week) )),null) rw2_flag
, DECODE(sign(g_no_of_roll_week-2),1,1-abs(sign( FLOOR((w.period_id-( (FLOOR(( w.period_id + rw.offset - 2 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 2 ))/g_no_of_roll_week) )),null) rw3_flag
, DECODE(sign(g_no_of_roll_week-3),1,1-abs(sign( FLOOR((w.period_id-( (FLOOR(( w.period_id + rw.offset - 3 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 3 ))/g_no_of_roll_week) )),null) rw4_flag
, DECODE(sign(g_no_of_roll_week-4),1,1-abs(sign( FLOOR((w.period_id-( (FLOOR(( w.period_id + rw.offset - 4 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 4 ))/g_no_of_roll_week) )),null) rw5_flag
, DECODE(sign(g_no_of_roll_week-5),1,1-abs(sign( FLOOR((w.period_id-( (FLOOR(( w.period_id + rw.offset - 5 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 5 ))/g_no_of_roll_week) )),null) rw6_flag
, DECODE(sign(g_no_of_roll_week-6),1,1-abs(sign( FLOOR((w.period_id-( (FLOOR(( w.period_id + rw.offset - 6 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 6 ))/g_no_of_roll_week) )),null) rw7_flag
, DECODE(sign(g_no_of_roll_week-7),1,1-abs(sign( FLOOR((w.period_id-( (FLOOR(( w.period_id + rw.offset - 7 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 7 ))/g_no_of_roll_week) )),null) rw8_flag
, DECODE(sign(g_no_of_roll_week-8),1,1-abs(sign( FLOOR((w.period_id-( (FLOOR(( w.period_id + rw.offset - 8 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 8 ))/g_no_of_roll_week) )),null) rw9_flag
, DECODE(sign(g_no_of_roll_week-9),1,1-abs(sign( FLOOR((w.period_id-( (FLOOR(( w.period_id + rw.offset - 9 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 9 ))/g_no_of_roll_week) )),null) rw10_flag
, DECODE(sign(g_no_of_roll_week-10),1,1-abs(sign( FLOOR((w.period_id-( (FLOOR(( w.period_id + rw.offset - 10 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 10 ))/g_no_of_roll_week) )),null) rw11_flag
, DECODE(sign(g_no_of_roll_week-11),1,1-abs(sign( FLOOR((w.period_id-( (FLOOR(( w.period_id + rw.offset - 11 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 11 ))/g_no_of_roll_week) )),null) rw12_flag
, DECODE(sign(g_no_of_roll_week-12),1,1-abs(sign( FLOOR((w.period_id-( (FLOOR(( w.period_id + rw.offset - 12 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 12 ))/g_no_of_roll_week) )),null) rw13_flag
FROM
PJI_RM_AGGR_AVL1 w
, PJI_ROLL_WEEK_OFFSET rw
WHERE
w.period_type = 'W'
and w.worker_id = p_worker_id
) cur5
GROUP BY worker_id,
person_id,
sequence_id
) cur4,
(
SELECT /*+ cache(fiit) */
fiit.week_id as week_id,
(to_char(fiit.start_date,'j') - g_min_wk_j_st_date)/7 + 1 as sequence_id
FROM
FII_TIME_WEEK fiit
) fweek,
(
SELECT /*+ cache(fiii) */
fiii.week_id as week_id,
(to_char(fiii.start_date,'j') - g_min_wk_j_st_date)/7 + 1 as sequence_id
FROM
FII_TIME_WEEK fiii
) fweek1
WHERE
cur4.period_id = fweek.sequence_id
and cur4.week_id = fweek1.sequence_id (+)
) cur
, FII_TIME_DAY fiid
WHERE
cur.PERIOD_ID = fiid.WEEK_ID
) cur1
GROUP BY
cur1.worker_id,
cur1.person_id,
cur1.time_id
) cur2,
PJI_RM_RES_F fct
WHERE
cur2.time_id = fct.time_id
and cur2.person_id = fct.person_id
and 'C' = fct.calendar_type
and 0 <> nvl(fct.capacity_hrs, 0)
and p_person_id = fct.person_id;
-- Delete existing records from the PL/SQL tables
l_worker_id_tbl.DELETE;
l_exp_organization_id_tbl.DELETE;
l_exp_org_id_tbl.DELETE;
l_person_id_tbl.DELETE;
l_time_id_tbl.DELETE;
l_week_id_tbl.DELETE;
l_ent_period_id_tbl.DELETE;
l_ent_qtr_id_tbl.DELETE;
l_gl_period_id_tbl.DELETE;
l_gl_qtr_id_tbl.DELETE;
l_roll_x_week_1_tbl.DELETE;
l_roll_x_week_2_tbl.DELETE;
l_roll_x_week_3_tbl.DELETE;
l_roll_x_week_4_tbl.DELETE;
l_roll_x_week_5_tbl.DELETE;
l_roll_x_week_6_tbl.DELETE;
l_roll_x_week_7_tbl.DELETE;
l_roll_x_week_8_tbl.DELETE;
l_roll_x_week_9_tbl.DELETE;
l_roll_x_week_10_tbl.DELETE;
l_roll_x_week_11_tbl.DELETE;
l_roll_x_week_12_tbl.DELETE;
l_roll_x_week_13_tbl.DELETE;
l_avl_res_cnt_1_tbl.DELETE;
l_avl_res_cnt_2_tbl.DELETE;
l_avl_res_cnt_3_tbl.DELETE;
l_avl_res_cnt_4_tbl.DELETE;
l_avl_res_cnt_5_tbl.DELETE;
--INSERT Records for this particular
--person id and enterprise period in
--PJI_RM_AGGR_AVL3 table
PREPARE_TO_INS_INTO_AVL3
(
p_exp_organization_id => l_old_exp_orgnztion_id,
p_exp_org_id => l_old_exp_org_id,
p_person_id => l_old_person_id,
p_time_id => l_old_ent_period_id,
p_curr_pd => l_ent_period_id,
p_as_of_date => l_time_id,
p_pd_org_st_date => l_start_date_org_ent_pd,
p_period_type_id => 32,
p_calendar_type => 'E',
p_res_cnt_tbl => l_ent_pd_res_cnt_tbl,
p_run_mode => p_run_mode,
p_blind_insert_flag => 'N',
x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
);
--After insert SET ALL count and values to 0
FOR m in l_ent_pd_res_cnt_tbl.FIRST.. l_ent_pd_res_cnt_tbl.LAST
LOOP
l_ent_pd_res_cnt_tbl(m) := 0;
SELECT to_char(fiit.start_date,'j')
INTO l_start_date_org_ent_pd
FROM fii_time_ent_period fiit
WHERE ent_period_id = l_ent_period_id;
--INSERT Records for this particular
--person id and ENTERPRISE QUARTER in
--PJI_RM_AGGR_AVL3 table
PREPARE_TO_INS_INTO_AVL3
(
p_exp_organization_id => l_old_exp_orgnztion_id,
p_exp_org_id => l_old_exp_org_id,
p_person_id => l_old_person_id,
p_time_id => l_old_ent_qtr_id,
p_curr_pd => l_ent_qtr_id,
p_as_of_date => l_time_id,
p_pd_org_st_date => l_start_date_org_ent_qtr,
p_period_type_id => 64,
p_calendar_type => 'E',
p_res_cnt_tbl => l_ent_qtr_res_cnt_tbl,
p_run_mode => p_run_mode,
p_blind_insert_flag => 'N',
x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
);
--After insert SET ALL count and values to 0
FOR m in l_ent_qtr_res_cnt_tbl.FIRST.. l_ent_qtr_res_cnt_tbl.LAST
LOOP
l_ent_qtr_res_cnt_tbl(m) := 0;
SELECT to_char(fiit.start_date,'j')
INTO l_start_date_org_ent_qtr
FROM fii_time_ent_qtr fiit
WHERE ent_qtr_id = l_ent_qtr_id;
--INSERT Records for this particular
--person id and GL PERIOD in
--PJI_RM_AGGR_AVL3 table
PREPARE_TO_INS_INTO_AVL3
(
p_exp_organization_id => l_old_exp_orgnztion_id,
p_exp_org_id => l_old_exp_org_id,
p_person_id => l_old_person_id,
p_time_id => l_old_gl_period_id,
p_curr_pd => l_gl_period_id,
p_as_of_date => l_time_id,
p_pd_org_st_date => l_start_date_org_gl_pd,
p_period_type_id => 32,
p_calendar_type => 'G',
p_res_cnt_tbl => l_gl_pd_res_cnt_tbl,
p_run_mode => p_run_mode,
p_blind_insert_flag => 'N',
x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
);
--After insert SET ALL count and values to 0
FOR m in l_gl_pd_res_cnt_tbl.FIRST.. l_gl_pd_res_cnt_tbl.LAST
LOOP
l_gl_pd_res_cnt_tbl(m) := 0;
SELECT to_char(fiit.start_date,'j')
INTO l_start_date_org_gl_pd
FROM fii_time_cal_period fiit
WHERE cal_period_id = l_gl_period_id;
--INSERT Records for this particular
--person id and GL QUARTER in
--PJI_RM_AGGR_AVL3 table
PREPARE_TO_INS_INTO_AVL3
(
p_exp_organization_id => l_old_exp_orgnztion_id,
p_exp_org_id => l_old_exp_org_id,
p_person_id => l_old_person_id,
p_time_id => l_old_gl_qtr_id,
p_curr_pd => l_gl_qtr_id,
p_as_of_date => l_time_id,
p_pd_org_st_date => l_start_date_org_gl_qtr,
p_period_type_id => 64,
p_calendar_type => 'G',
p_res_cnt_tbl => l_gl_qtr_res_cnt_tbl,
p_run_mode => p_run_mode,
p_blind_insert_flag => 'N',
x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
);
--After insert SET ALL count and values to 0
FOR m in l_gl_qtr_res_cnt_tbl.FIRST.. l_gl_qtr_res_cnt_tbl.LAST
LOOP
l_gl_qtr_res_cnt_tbl(m) := 0;
SELECT to_char(fiit.start_date,'j')
INTO l_start_date_org_gl_qtr
FROM fii_time_cal_qtr fiit
WHERE cal_qtr_id = l_gl_qtr_id;
--INSERT Records for this particular
--person id and ROLLING WEEK 1 in
--PJI_RM_AGGR_AVL3 table
PREPARE_TO_INS_INTO_AVL3
(
p_exp_organization_id => l_old_exp_orgnztion_id,
p_exp_org_id => l_old_exp_org_id,
p_person_id => l_old_person_id,
p_time_id => l_old_roll_x_week1,
p_curr_pd => l_roll_x_week1,
p_as_of_date => l_time_id,
p_pd_org_st_date => l_start_date_org_roll_x_wk1,
p_period_type_id => 16,
p_calendar_type => 'E',
p_res_cnt_tbl => l_roll_x_wk1_res_cnt_tbl,
p_run_mode => p_run_mode,
p_blind_insert_flag => 'N',
x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
);
--After insert SET ALL count and values to 0
FOR m in l_roll_x_wk1_res_cnt_tbl.FIRST.. l_roll_x_wk1_res_cnt_tbl.LAST
LOOP
l_roll_x_wk1_res_cnt_tbl(m) := 0;
SELECT to_char(fiit.start_date,'j')
INTO l_start_date_org_roll_x_wk1
FROM FII_TIME_WEEK fiit
WHERE fiit.start_date = to_date(to_char(((l_roll_x_week1 - 1) * 7) + -- Bug#4903567
g_min_wk_j_st_date), 'J');
--INSERT Records for this particular
--person id and ROLLING WEEK 2 in
--PJI_RM_AGGR_AVL3 table
PREPARE_TO_INS_INTO_AVL3
(
p_exp_organization_id => l_old_exp_orgnztion_id,
p_exp_org_id => l_old_exp_org_id,
p_person_id => l_old_person_id,
p_time_id => l_old_roll_x_week2,
p_curr_pd => l_roll_x_week2,
p_as_of_date => l_time_id,
p_pd_org_st_date => l_start_date_org_roll_x_wk2,
p_period_type_id => 16,
p_calendar_type => 'E',
p_res_cnt_tbl => l_roll_x_wk2_res_cnt_tbl,
p_run_mode => p_run_mode,
p_blind_insert_flag => 'N',
x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
);
--After insert SET ALL count and values to 0
FOR m in l_roll_x_wk2_res_cnt_tbl.FIRST.. l_roll_x_wk2_res_cnt_tbl.LAST
LOOP
l_roll_x_wk2_res_cnt_tbl(m) := 0;
SELECT to_char(fiit.start_date,'j')
INTO l_start_date_org_roll_x_wk2
FROM FII_TIME_WEEK fiit
WHERE fiit.start_date = to_date(to_char(((l_roll_x_week2 - 1) * 7) + -- Bug#4903567
g_min_wk_j_st_date), 'J');
--INSERT Records for this particular
--person id and ROLLING WEEK 3 in
--PJI_RM_AGGR_AVL3 table
PREPARE_TO_INS_INTO_AVL3
(
p_exp_organization_id => l_old_exp_orgnztion_id,
p_exp_org_id => l_old_exp_org_id,
p_person_id => l_old_person_id,
p_time_id => l_old_roll_x_week3,
p_curr_pd => l_roll_x_week3,
p_as_of_date => l_time_id,
p_pd_org_st_date => l_start_date_org_roll_x_wk3,
p_period_type_id => 16,
p_calendar_type => 'E',
p_res_cnt_tbl => l_roll_x_wk3_res_cnt_tbl,
p_run_mode => p_run_mode,
p_blind_insert_flag => 'N',
x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
);
--After insert SET ALL count and values to 0
FOR m in l_roll_x_wk3_res_cnt_tbl.FIRST.. l_roll_x_wk3_res_cnt_tbl.LAST
LOOP
l_roll_x_wk3_res_cnt_tbl(m) := 0;
SELECT to_char(fiit.start_date,'j')
INTO l_start_date_org_roll_x_wk3
FROM FII_TIME_WEEK fiit
WHERE fiit.start_date = to_date(to_char(((l_roll_x_week3 - 1) * 7) + -- Bug#4903567
g_min_wk_j_st_date), 'J');
--INSERT Records for this particular
--person id and ROLLING WEEK 4 in
--PJI_RM_AGGR_AVL3 table
PREPARE_TO_INS_INTO_AVL3
(
p_exp_organization_id => l_old_exp_orgnztion_id,
p_exp_org_id => l_old_exp_org_id,
p_person_id => l_old_person_id,
p_time_id => l_old_roll_x_week4,
p_curr_pd => l_roll_x_week4,
p_as_of_date => l_time_id,
p_pd_org_st_date => l_start_date_org_roll_x_wk4,
p_period_type_id => 16,
p_calendar_type => 'E',
p_res_cnt_tbl => l_roll_x_wk4_res_cnt_tbl,
p_run_mode => p_run_mode,
p_blind_insert_flag => 'N',
x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
);
--After insert SET ALL count and values to 0
FOR m in l_roll_x_wk4_res_cnt_tbl.FIRST.. l_roll_x_wk4_res_cnt_tbl.LAST
LOOP
l_roll_x_wk4_res_cnt_tbl(m) := 0;
SELECT to_char(fiit.start_date,'j')
INTO l_start_date_org_roll_x_wk4
FROM FII_TIME_WEEK fiit
WHERE fiit.start_date = to_date(to_char(((l_roll_x_week4 - 1) * 7) + -- Bug#4903567
g_min_wk_j_st_date), 'J');
--INSERT Records for this particular
--person id and ROLLING WEEK 5 in
--PJI_RM_AGGR_AVL3 table
PREPARE_TO_INS_INTO_AVL3
(
p_exp_organization_id => l_old_exp_orgnztion_id,
p_exp_org_id => l_old_exp_org_id,
p_person_id => l_old_person_id,
p_time_id => l_old_roll_x_week5,
p_curr_pd => l_roll_x_week5,
p_as_of_date => l_time_id,
p_pd_org_st_date => l_start_date_org_roll_x_wk5,
p_period_type_id => 16,
p_calendar_type => 'E',
p_res_cnt_tbl => l_roll_x_wk5_res_cnt_tbl,
p_run_mode => p_run_mode,
p_blind_insert_flag => 'N',
x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
);
--After insert SET ALL count and values to 0
FOR m in l_roll_x_wk5_res_cnt_tbl.FIRST.. l_roll_x_wk5_res_cnt_tbl.LAST
LOOP
l_roll_x_wk5_res_cnt_tbl(m) := 0;
SELECT to_char(fiit.start_date,'j')
INTO l_start_date_org_roll_x_wk5
FROM FII_TIME_WEEK fiit
WHERE fiit.start_date = to_date(to_char(((l_roll_x_week5 - 1) * 7) + -- Bug#4903567
g_min_wk_j_st_date), 'J');
--INSERT Records for this particular
--person id and ROLLING WEEK 6 in
--PJI_RM_AGGR_AVL3 table
PREPARE_TO_INS_INTO_AVL3
(
p_exp_organization_id => l_old_exp_orgnztion_id,
p_exp_org_id => l_old_exp_org_id,
p_person_id => l_old_person_id,
p_time_id => l_old_roll_x_week6,
p_curr_pd => l_roll_x_week6,
p_as_of_date => l_time_id,
p_pd_org_st_date => l_start_date_org_roll_x_wk6,
p_period_type_id => 16,
p_calendar_type => 'E',
p_res_cnt_tbl => l_roll_x_wk6_res_cnt_tbl,
p_run_mode => p_run_mode,
p_blind_insert_flag => 'N',
x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
);
--After insert SET ALL count and values to 0
FOR m in l_roll_x_wk6_res_cnt_tbl.FIRST.. l_roll_x_wk6_res_cnt_tbl.LAST
LOOP
l_roll_x_wk6_res_cnt_tbl(m) := 0;
SELECT to_char(fiit.start_date,'j')
INTO l_start_date_org_roll_x_wk6
FROM FII_TIME_WEEK fiit
WHERE fiit.start_date = to_date(to_char(((l_roll_x_week6 - 1) * 7) + -- Bug#4903567
g_min_wk_j_st_date), 'J');
--INSERT Records for this particular
--person id and ROLLING WEEK 7 in
--PJI_RM_AGGR_AVL3 table
PREPARE_TO_INS_INTO_AVL3
(
p_exp_organization_id => l_old_exp_orgnztion_id,
p_exp_org_id => l_old_exp_org_id,
p_person_id => l_old_person_id,
p_time_id => l_old_roll_x_week7,
p_curr_pd => l_roll_x_week7,
p_as_of_date => l_time_id,
p_pd_org_st_date => l_start_date_org_roll_x_wk7,
p_period_type_id => 16,
p_calendar_type => 'E',
p_res_cnt_tbl => l_roll_x_wk7_res_cnt_tbl,
p_run_mode => p_run_mode,
p_blind_insert_flag => 'N',
x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
);
--After insert SET ALL count and values to 0
FOR m in l_roll_x_wk7_res_cnt_tbl.FIRST.. l_roll_x_wk7_res_cnt_tbl.LAST
LOOP
l_roll_x_wk7_res_cnt_tbl(m) := 0;
SELECT to_char(fiit.start_date,'j')
INTO l_start_date_org_roll_x_wk7
FROM FII_TIME_WEEK fiit
WHERE fiit.start_date = to_date(to_char(((l_roll_x_week7 - 1) * 7) + -- Bug#4903567
g_min_wk_j_st_date), 'J');
--INSERT Records for this particular
--person id and ROLLING WEEK 8 in
--PJI_RM_AGGR_AVL3 table
PREPARE_TO_INS_INTO_AVL3
(
p_exp_organization_id => l_old_exp_orgnztion_id,
p_exp_org_id => l_old_exp_org_id,
p_person_id => l_old_person_id,
p_time_id => l_old_roll_x_week8,
p_curr_pd => l_roll_x_week8,
p_as_of_date => l_time_id,
p_pd_org_st_date => l_start_date_org_roll_x_wk8,
p_period_type_id => 16,
p_calendar_type => 'E',
p_res_cnt_tbl => l_roll_x_wk8_res_cnt_tbl,
p_run_mode => p_run_mode,
p_blind_insert_flag => 'N',
x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
);
--After insert SET ALL count and values to 0
FOR m in l_roll_x_wk8_res_cnt_tbl.FIRST.. l_roll_x_wk8_res_cnt_tbl.LAST
LOOP
l_roll_x_wk8_res_cnt_tbl(m) := 0;
SELECT to_char(fiit.start_date,'j')
INTO l_start_date_org_roll_x_wk8
FROM FII_TIME_WEEK fiit
WHERE fiit.start_date = to_date(to_char(((l_roll_x_week8 - 1) * 7) + -- Bug#4903567
g_min_wk_j_st_date), 'J');
--INSERT Records for this particular
--person id and ROLLING WEEK 9 in
--PJI_RM_AGGR_AVL3 table
PREPARE_TO_INS_INTO_AVL3
(
p_exp_organization_id => l_old_exp_orgnztion_id,
p_exp_org_id => l_old_exp_org_id,
p_person_id => l_old_person_id,
p_time_id => l_old_roll_x_week9,
p_curr_pd => l_roll_x_week9,
p_as_of_date => l_time_id,
p_pd_org_st_date => l_start_date_org_roll_x_wk9,
p_period_type_id => 16,
p_calendar_type => 'E',
p_res_cnt_tbl => l_roll_x_wk9_res_cnt_tbl,
p_run_mode => p_run_mode,
p_blind_insert_flag => 'N',
x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
);
--After insert SET ALL count and values to 0
FOR m in l_roll_x_wk9_res_cnt_tbl.FIRST.. l_roll_x_wk9_res_cnt_tbl.LAST
LOOP
l_roll_x_wk9_res_cnt_tbl(m) := 0;
SELECT to_char(fiit.start_date,'j')
INTO l_start_date_org_roll_x_wk9
FROM FII_TIME_WEEK fiit
WHERE fiit.start_date = to_date(to_char(((l_roll_x_week9 - 1) * 7) + -- Bug#4903567
g_min_wk_j_st_date), 'J');
--INSERT Records for this particular
--person id and ROLLING WEEK 10 in
--PJI_RM_AGGR_AVL3 table
PREPARE_TO_INS_INTO_AVL3
(
p_exp_organization_id => l_old_exp_orgnztion_id,
p_exp_org_id => l_old_exp_org_id,
p_person_id => l_old_person_id,
p_time_id => l_old_roll_x_week10,
p_curr_pd => l_roll_x_week10,
p_as_of_date => l_time_id,
p_pd_org_st_date => l_start_date_org_roll_x_wk10,
p_period_type_id => 16,
p_calendar_type => 'E',
p_res_cnt_tbl => l_roll_x_wk10_res_cnt_tbl,
p_run_mode => p_run_mode,
p_blind_insert_flag => 'N',
x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
);
--After insert SET ALL count and values to 0
FOR m in l_roll_x_wk10_res_cnt_tbl.FIRST.. l_roll_x_wk10_res_cnt_tbl.LAST
LOOP
l_roll_x_wk10_res_cnt_tbl(m) := 0;
SELECT to_char(fiit.start_date,'j')
INTO l_start_date_org_roll_x_wk10
FROM FII_TIME_WEEK fiit
WHERE fiit.start_date = to_date(to_char(((l_roll_x_week10 - 1) * 7) + -- Bug#4903567
g_min_wk_j_st_date), 'J');
--INSERT Records for this particular
--person id and ROLLING WEEK 11 in
--PJI_RM_AGGR_AVL3 table
PREPARE_TO_INS_INTO_AVL3
(
p_exp_organization_id => l_old_exp_orgnztion_id,
p_exp_org_id => l_old_exp_org_id,
p_person_id => l_old_person_id,
p_time_id => l_old_roll_x_week11,
p_curr_pd => l_roll_x_week11,
p_as_of_date => l_time_id,
p_pd_org_st_date => l_start_date_org_roll_x_wk11,
p_period_type_id => 16,
p_calendar_type => 'E',
p_res_cnt_tbl => l_roll_x_wk11_res_cnt_tbl,
p_run_mode => p_run_mode,
p_blind_insert_flag => 'N',
x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
);
--After insert SET ALL count and values to 0
FOR m in l_roll_x_wk11_res_cnt_tbl.FIRST.. l_roll_x_wk11_res_cnt_tbl.LAST
LOOP
l_roll_x_wk11_res_cnt_tbl(m) := 0;
SELECT to_char(fiit.start_date,'j')
INTO l_start_date_org_roll_x_wk11
FROM FII_TIME_WEEK fiit
WHERE fiit.start_date = to_date(to_char(((l_roll_x_week11 - 1) * 7) + -- Bug#4903567
g_min_wk_j_st_date), 'J');
--INSERT Records for this particular
--person id and ROLLING WEEK 12 in
--PJI_RM_AGGR_AVL3 table
PREPARE_TO_INS_INTO_AVL3
(
p_exp_organization_id => l_old_exp_orgnztion_id,
p_exp_org_id => l_old_exp_org_id,
p_person_id => l_old_person_id,
p_time_id => l_old_roll_x_week12,
p_curr_pd => l_roll_x_week12,
p_as_of_date => l_time_id,
p_pd_org_st_date => l_start_date_org_roll_x_wk12,
p_period_type_id => 16,
p_calendar_type => 'E',
p_res_cnt_tbl => l_roll_x_wk12_res_cnt_tbl,
p_run_mode => p_run_mode,
p_blind_insert_flag => 'N',
x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
);
--After insert SET ALL count and values to 0
FOR m in l_roll_x_wk12_res_cnt_tbl.FIRST.. l_roll_x_wk12_res_cnt_tbl.LAST
LOOP
l_roll_x_wk12_res_cnt_tbl(m) := 0;
SELECT to_char(fiit.start_date,'j')
INTO l_start_date_org_roll_x_wk12
FROM FII_TIME_WEEK fiit
WHERE fiit.start_date = to_date(to_char(((l_roll_x_week12 - 1) * 7) + -- Bug#4903567
g_min_wk_j_st_date), 'J');
--INSERT Records for this particular
--person id and ROLLING WEEK 13 in
--PJI_RM_AGGR_AVL3 table
PREPARE_TO_INS_INTO_AVL3
(
p_exp_organization_id => l_old_exp_orgnztion_id,
p_exp_org_id => l_old_exp_org_id,
p_person_id => l_old_person_id,
p_time_id => l_old_roll_x_week13,
p_curr_pd => l_roll_x_week13,
p_as_of_date => l_time_id,
p_pd_org_st_date => l_start_date_org_roll_x_wk13,
p_period_type_id => 16,
p_calendar_type => 'E',
p_res_cnt_tbl => l_roll_x_wk13_res_cnt_tbl,
p_run_mode => p_run_mode,
p_blind_insert_flag => 'N',
x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
);
--After insert SET ALL count and values to 0
FOR m in l_roll_x_wk13_res_cnt_tbl.FIRST.. l_roll_x_wk13_res_cnt_tbl.LAST
LOOP
l_roll_x_wk13_res_cnt_tbl(m) := 0;
SELECT to_char(fiit.start_date,'j')
INTO l_start_date_org_roll_x_wk13
FROM FII_TIME_WEEK fiit
WHERE fiit.start_date = to_date(to_char(((l_roll_x_week13 - 1) * 7) + -- Bug#4903567
g_min_wk_j_st_date), 'J');
--INSERT Records for this particular
--person id and WEEK in
--PJI_RM_AGGR_AVL4 table
PREPARE_TO_INS_INTO_AVL4
(
p_exp_organization_id => l_old_exp_orgnztion_id,
p_exp_org_id => l_old_exp_org_id,
p_person_id => l_old_person_id,
p_time_id => l_old_week_id,
p_curr_pd => l_week_id,
p_as_of_date => l_time_id,
p_pd_org_st_date => l_start_date_org_week,
p_period_type_id => 16,
p_calendar_type => 'E',
p_res_cnt_tbl => l_week_res_cnt_tbl,
p_run_mode => p_run_mode,
p_blind_insert_flag => 'N',
x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
);
--After insert SET ALL count and values to 0
FOR m in l_week_res_cnt_tbl.FIRST.. l_week_res_cnt_tbl.LAST
LOOP
l_week_res_cnt_tbl(m) := 0;
SELECT to_char(fiit.start_date,'j')
INTO l_start_date_org_week
FROM fii_time_week fiit
WHERE l_week_id = fiit.week_id;
inserting in PJI_RM_AGGR_AVL3 and PJI_RM_AGGR_AVL4 table are inserted.
If the number of PL/SQL records did not reach 200
it may not be inserted. So, call the Bulk insert API
with blind insert flag = 'Y' and all variables and
tables as empty. Also, no processing is done on any
of the parameters other than P_BLIND_INSERT_FLAG,
so passing everything as null and dummy PL/SQL tables
*/
PREPARE_TO_INS_INTO_AVL3
(
p_exp_organization_id => null,
p_exp_org_id => null,
p_person_id => null,
p_time_id => null,
p_curr_pd => null,
p_as_of_date => null,
p_pd_org_st_date => null,
p_period_type_id => null,
p_calendar_type => null,
p_res_cnt_tbl => l_dummy_res_tbl,
p_run_mode => p_run_mode,
p_blind_insert_flag => 'Y',
x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
);
p_blind_insert_flag => 'Y',
x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
);
SELECT COUNT(*)
INTO g_curr_res_left_count
FROM PJI_RM_RES_BATCH_MAP
WHERE worker_status IS NULL
and worker_id IS NULL;
l_last_update_date DATE := sysdate;
l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
SELECT MIN(to_char(fiik.start_date,'j'))
INTO g_min_wk_j_st_date
FROM fii_time_week fiik;
SELECT
rtmp.EXPENDITURE_ORGANIZATION_ID as EXPENDITURE_ORGANIZATION_ID,
rtmp.EXPENDITURE_ORG_ID as EXPENDITURE_ORG_ID,
rtmp.TIME_ID as TIME_ID,
rtmp.PERIOD_TYPE_ID as PERIOD_TYPE_ID,
rtmp.CALENDAR_TYPE as CALENDAR_TYPE,
rtmp.THRESHOLD as THRESHOLD,
rtmp.AS_OF_DATE as AS_OF_DATE,
sum(rtmp.BCKT_1_CS) BCKT_1_CS,
sum(rtmp.BCKT_2_CS) BCKT_2_CS,
sum(rtmp.BCKT_3_CS) BCKT_3_CS,
sum(rtmp.BCKT_4_CS) BCKT_4_CS,
sum(rtmp.BCKT_5_CS) BCKT_5_CS,
sum(rtmp.BCKT_1_CM) BCKT_1_CM,
sum(rtmp.BCKT_2_CM) BCKT_2_CM,
sum(rtmp.BCKT_3_CM) BCKT_3_CM,
sum(rtmp.BCKT_4_CM) BCKT_4_CM,
sum(rtmp.BCKT_5_CM) BCKT_5_CM,
sum(rtmp.TOTAL_RES_COUNT) TOTAL_RES_COUNT,
l_last_update_date LAST_UPDATE_DATE,
l_last_updated_by LAST_UPDATED_BY,
l_creation_date CREATION_DATE,
l_created_by CREATED_BY,
l_last_update_login LAST_UPDATE_LOGIN
FROM
(
SELECT
rtmp1.EXPENDITURE_ORGANIZATION_ID as EXPENDITURE_ORGANIZATION_ID,
rtmp1.EXPENDITURE_ORG_ID as EXPENDITURE_ORG_ID,
case when rtmp1.period_type_id = 16 then
fwk.WEEK_ID
when rtmp1.period_type_id <> 16 then
rtmp1.TIME_ID
end TIME_ID,
rtmp1.PERIOD_TYPE_ID as PERIOD_TYPE_ID,
rtmp1.PERSON_ID as PERSON_ID,
rtmp1.CALENDAR_TYPE as CALENDAR_TYPE,
rtmp1.THRESHOLD as THRESHOLD,
rtmp1.AS_OF_DATE as AS_OF_DATE,
rtmp1.BCKT_1_CS as BCKT_1_CS,
rtmp1.BCKT_2_CS as BCKT_2_CS,
rtmp1.BCKT_3_CS as BCKT_3_CS,
rtmp1.BCKT_4_CS as BCKT_4_CS,
rtmp1.BCKT_5_CS as BCKT_5_CS,
rtmp1.BCKT_1_CM as BCKT_1_CM,
rtmp1.BCKT_2_CM as BCKT_2_CM,
rtmp1.BCKT_3_CM as BCKT_3_CM,
rtmp1.BCKT_4_CM as BCKT_4_CM,
rtmp1.BCKT_5_CM as BCKT_5_CM,
rtmp1.TOTAL_RES_COUNT as TOTAL_RES_COUNT
FROM
(
SELECT
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_ORG_ID,
TIME_ID,
PERIOD_TYPE_ID,
PERSON_ID,
CALENDAR_TYPE,
THRESHOLD,
AS_OF_DATE,
BCKT_1_CS,
BCKT_2_CS,
BCKT_3_CS,
BCKT_4_CS,
BCKT_5_CS,
BCKT_1_CM,
BCKT_2_CM,
BCKT_3_CM,
BCKT_4_CM,
BCKT_5_CM,
TOTAL_RES_COUNT
FROM
PJI_RM_AGGR_AVL3
) rtmp1,
(
SELECT
fiit.WEEK_ID as WEEK_ID,
(to_char(fiit.start_date,'j') - g_min_wk_j_st_date)/7 + 1 as SEQUENCE_ID
FROM
FII_TIME_WEEK fiit
ORDER BY SEQUENCE_ID
) fwk
WHERE
rtmp1.time_id = fwk.sequence_id (+)
ORDER BY 1,2,3,4,5
) rtmp
GROUP BY
rtmp.EXPENDITURE_ORGANIZATION_ID,
rtmp.EXPENDITURE_ORG_ID,
rtmp.PERIOD_TYPE_ID,
rtmp.TIME_ID,
rtmp.CALENDAR_TYPE,
rtmp.THRESHOLD,
rtmp.AS_OF_DATE
) tmp1
ON
(
tmp1.EXPENDITURE_ORGANIZATION_ID = rmr.EXPENDITURE_ORGANIZATION_ID and
tmp1.EXPENDITURE_ORG_ID = rmr.EXPENDITURE_ORG_ID and
tmp1.PERIOD_TYPE_ID = rmr.PERIOD_TYPE_ID and
tmp1.TIME_ID = rmr.TIME_ID and
tmp1.CALENDAR_TYPE = rmr.CALENDAR_TYPE and
tmp1.THRESHOLD = rmr.THRESHOLD and
tmp1.AS_OF_DATE = rmr.AS_OF_DATE
)
WHEN MATCHED THEN UPDATE SET
rmr.BCKT_1_CS = rmr.BCKT_1_CS + tmp1.BCKT_1_CS,
rmr.BCKT_2_CS = rmr.BCKT_2_CS + tmp1.BCKT_2_CS,
rmr.BCKT_3_CS = rmr.BCKT_3_CS + tmp1.BCKT_3_CS,
rmr.BCKT_4_CS = rmr.BCKT_4_CS + tmp1.BCKT_4_CS,
rmr.BCKT_5_CS = rmr.BCKT_5_CS + tmp1.BCKT_5_CS,
rmr.BCKT_1_CM = rmr.BCKT_1_CM + tmp1.BCKT_1_CM,
rmr.BCKT_2_CM = rmr.BCKT_2_CM + tmp1.BCKT_2_CM,
rmr.BCKT_3_CM = rmr.BCKT_3_CM + tmp1.BCKT_3_CM,
rmr.BCKT_4_CM = rmr.BCKT_4_CM + tmp1.BCKT_4_CM,
rmr.BCKT_5_CM = rmr.BCKT_5_CM + tmp1.BCKT_5_CM,
rmr.TOTAL_RES_COUNT = rmr.TOTAL_RES_COUNT + tmp1.TOTAL_RES_COUNT,
rmr.LAST_UPDATE_DATE = tmp1.LAST_UPDATE_DATE,
rmr.LAST_UPDATED_BY = tmp1.LAST_UPDATED_BY,
rmr.LAST_UPDATE_LOGIN = tmp1.LAST_UPDATE_LOGIN
WHEN NOT MATCHED THEN INSERT
(
rmr.EXPENDITURE_ORGANIZATION_ID,
rmr.EXPENDITURE_ORG_ID,
rmr.PERIOD_TYPE_ID,
rmr.TIME_ID,
rmr.CALENDAR_TYPE,
rmr.THRESHOLD,
rmr.AS_OF_DATE,
rmr.CREATION_DATE,
rmr.CREATED_BY,
rmr.LAST_UPDATE_DATE,
rmr.LAST_UPDATED_BY,
rmr.LAST_UPDATE_LOGIN,
rmr.BCKT_1_CS,
rmr.BCKT_2_CS,
rmr.BCKT_3_CS,
rmr.BCKT_4_CS,
rmr.BCKT_5_CS,
rmr.BCKT_1_CM,
rmr.BCKT_2_CM,
rmr.BCKT_3_CM,
rmr.BCKT_4_CM,
rmr.BCKT_5_CM,
rmr.TOTAL_RES_COUNT
)
values
(
tmp1.EXPENDITURE_ORGANIZATION_ID,
tmp1.EXPENDITURE_ORG_ID,
tmp1.PERIOD_TYPE_ID,
tmp1.TIME_ID,
tmp1.CALENDAR_TYPE,
tmp1.THRESHOLD,
tmp1.AS_OF_DATE,
tmp1.CREATION_DATE,
tmp1.CREATED_BY,
tmp1.LAST_UPDATE_DATE,
tmp1.LAST_UPDATED_BY,
tmp1.LAST_UPDATE_LOGIN,
tmp1.BCKT_1_CS,
tmp1.BCKT_2_CS,
tmp1.BCKT_3_CS,
tmp1.BCKT_4_CS,
tmp1.BCKT_5_CS,
tmp1.BCKT_1_CM,
tmp1.BCKT_2_CM,
tmp1.BCKT_3_CM,
tmp1.BCKT_4_CM,
tmp1.BCKT_5_CM,
tmp1.TOTAL_RES_COUNT
);
l_last_update_date DATE := sysdate;
l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
SELECT MIN(to_char(fiik.start_date,'j'))
INTO g_min_wk_j_st_date
FROM fii_time_week fiik;
SELECT
rtmp.EXPENDITURE_ORGANIZATION_ID as EXPENDITURE_ORGANIZATION_ID,
rtmp.EXPENDITURE_ORG_ID as EXPENDITURE_ORG_ID,
rtmp.TIME_ID as TIME_ID,
rtmp.PERIOD_TYPE_ID as PERIOD_TYPE_ID,
rtmp.CALENDAR_TYPE as CALENDAR_TYPE,
rtmp.THRESHOLD as THRESHOLD,
rtmp.AS_OF_DATE as AS_OF_DATE,
sum(rtmp.AVAILABILITY) AVAILABILITY,
sum(rtmp.TOTAL_RES_COUNT) TOTAL_RES_COUNT,
l_last_update_date LAST_UPDATE_DATE,
l_last_updated_by LAST_UPDATED_BY,
l_creation_date CREATION_DATE,
l_created_by CREATED_BY,
l_last_update_login LAST_UPDATE_LOGIN
FROM
PJI_RM_AGGR_AVL4 rtmp
GROUP BY
rtmp.EXPENDITURE_ORGANIZATION_ID,
rtmp.EXPENDITURE_ORG_ID,
rtmp.PERIOD_TYPE_ID,
rtmp.TIME_ID,
rtmp.CALENDAR_TYPE,
rtmp.THRESHOLD,
rtmp.AS_OF_DATE
) tmp1
ON
(
tmp1.EXPENDITURE_ORGANIZATION_ID = rmr.EXPENDITURE_ORGANIZATION_ID and
tmp1.EXPENDITURE_ORG_ID = rmr.EXPENDITURE_ORG_ID and
tmp1.PERIOD_TYPE_ID = rmr.PERIOD_TYPE_ID and
tmp1.TIME_ID = rmr.TIME_ID and
tmp1.CALENDAR_TYPE = rmr.CALENDAR_TYPE and
tmp1.THRESHOLD = rmr.THRESHOLD and
tmp1.AS_OF_DATE = rmr.AS_OF_DATE
)
WHEN MATCHED THEN UPDATE SET
rmr.AVAILABILITY = rmr.AVAILABILITY + tmp1.AVAILABILITY,
rmr.TOTAL_RES_COUNT = rmr.TOTAL_RES_COUNT + tmp1.TOTAL_RES_COUNT,
rmr.LAST_UPDATE_DATE = tmp1.LAST_UPDATE_DATE,
rmr.LAST_UPDATED_BY = tmp1.LAST_UPDATED_BY,
rmr.LAST_UPDATE_LOGIN = tmp1.LAST_UPDATE_LOGIN
WHEN NOT MATCHED THEN INSERT
(
rmr.EXPENDITURE_ORGANIZATION_ID,
rmr.EXPENDITURE_ORG_ID,
rmr.PERIOD_TYPE_ID,
rmr.TIME_ID,
rmr.CALENDAR_TYPE,
rmr.THRESHOLD,
rmr.AS_OF_DATE,
rmr.CREATION_DATE,
rmr.CREATED_BY,
rmr.LAST_UPDATE_DATE,
rmr.LAST_UPDATED_BY,
rmr.LAST_UPDATE_LOGIN,
rmr.AVAILABILITY,
rmr.TOTAL_RES_COUNT
)
values
(
tmp1.EXPENDITURE_ORGANIZATION_ID,
tmp1.EXPENDITURE_ORG_ID,
tmp1.PERIOD_TYPE_ID,
tmp1.TIME_ID,
tmp1.CALENDAR_TYPE,
tmp1.THRESHOLD,
tmp1.AS_OF_DATE,
tmp1.CREATION_DATE,
tmp1.CREATED_BY,
tmp1.LAST_UPDATE_DATE,
tmp1.LAST_UPDATED_BY,
tmp1.LAST_UPDATE_LOGIN,
tmp1.AVAILABILITY,
tmp1.TOTAL_RES_COUNT
);
This procedure updates the resource status
for which the error occured (if it occured
at all) and processing has not been done.
After this update, the resource can be
picked up by any worker in the current run
*/
PROCEDURE UPDATE_RES_STATUS
IS
BEGIN
--Update status table to make sure that any resource
--that was not processed last time is processed this
--time
UPDATE PJI_RM_RES_BATCH_MAP
SET worker_id = null
WHERE worker_status IS NULL
AND worker_id IS NOT NULL;
END UPDATE_RES_STATUS;
SELECT COUNT(*)
INTO l_count_res_status
FROM PJI_RM_RES_BATCH_MAP;
UPDATE PJI_RM_RES_BATCH_MAP
SET worker_id = p_worker_id
WHERE worker_status IS NULL
and worker_id IS NULL
and rownum < 2
RETURNING person_id
INTO l_person_id;
UPDATE PJI_RM_RES_BATCH_MAP
SET worker_id = null
WHERE person_id = l_person_id;
UPDATE PJI_RM_RES_BATCH_MAP
SET worker_status = 'C'
WHERE person_id = l_person_id
and worker_id = p_worker_id;
select count(*)
into l_row_count
from PJI_RM_RES_BATCH_MAP
where nvl(WORKER_STATUS, 'X') <> 'C';
update PJI_SYSTEM_PRC_STATUS
set STEP_STATUS = 'C'
where PROCESS_NAME = PJI_RM_SUM_MAIN.g_process || to_char(x)
and STEP_NAME = 'PJI_RM_SUM_AVL.START_RES_AVL_CALC_R1(p_worker_id);';
SELECT COUNT(*)
INTO l_count_res_status
FROM PJI_RM_RES_BATCH_MAP;
UPDATE PJI_RM_RES_BATCH_MAP
SET worker_id = p_worker_id
WHERE worker_status IS NULL
and worker_id IS NULL
and rownum < 2
RETURNING person_id
INTO l_person_id;
UPDATE PJI_RM_RES_BATCH_MAP
SET worker_id = null
WHERE person_id = l_person_id;
UPDATE PJI_RM_RES_BATCH_MAP
SET worker_status = 'C'
WHERE person_id = l_person_id
and worker_id = p_worker_id;
select count(*)
into l_row_count
from PJI_RM_RES_BATCH_MAP
where nvl(WORKER_STATUS, 'X') <> 'C';
update PJI_SYSTEM_PRC_STATUS
set STEP_STATUS = 'C'
where PROCESS_NAME = PJI_RM_SUM_MAIN.g_process || to_char(x)
and STEP_NAME = 'PJI_RM_SUM_AVL.START_RES_AVL_CALC_R2(p_worker_id);';
This procedure updates the resource status
table for run 2 with new fact records
*/
PROCEDURE UPDATE_RES_STA_FOR_RUN2
(p_worker_id IN NUMBER)
IS
l_process VARCHAR2(30);
'PJI_RM_SUM_AVL.UPDATE_RES_STA_FOR_RUN2(p_worker_id);'
SELECT count(*)
INTO l_res_process_cnt
FROM PJI_RM_RES_BATCH_MAP
where worker_id IS NOT NULL
AND worker_status IS NOT NULL;
SELECT count(*)
INTO l_res_full_cnt
FROM PJI_RM_RES_BATCH_MAP;
--update all resources with null values for
--worker and status to make it available
--for next run with new fact records
UPDATE PJI_RM_RES_BATCH_MAP
SET worker_id = null,
worker_status = null;
'PJI_RM_SUM_AVL.UPDATE_RES_STA_FOR_RUN2(p_worker_id);'
END UPDATE_RES_STA_FOR_RUN2;
This procedure is used to insert rows
in the resource status table. The
population and constant update of this
status table helps in maintaining dynamic
pooling of workers and also help in starting
process just prior to the point of error
during run time
*/
PROCEDURE INS_INTO_RES_STATUS
(p_worker_id IN NUMBER)
IS
--Defining local variables
l_process VARCHAR2(30);
SELECT COUNT(*)
INTO l_count_res_status
FROM PJI_RM_RES_BATCH_MAP;
INSERT INTO PJI_RM_RES_BATCH_MAP(person_id)
SELECT DISTINCT person_id from PJI_RM_AGGR_RES2;