[Home] [Help]
387:
388: ---------------------------------------------------
389: -- PRIVATE PROCEDURE load_ent_qtr
390: -- This procedure incrementally maintains quarter
391: -- entries in PJI_TIME_ENT_QTR.
392: ---------------------------------------------------
393: PROCEDURE LOAD_ENT_QTR IS
394: l_no_rows_deleted NUMBER := 0;
395: l_no_rows_inserted NUMBER := 0;
408: l_start_qtr_id
409: , l_end_qtr_id
410: , l_earliest_qtr_end_date
411: , l_latest_qtr_start_date
412: FROM pji_time_ent_qtr
413: WHERE 1=1
414: AND (g_earliest_start_date BETWEEN start_date AND end_date)
415: OR (g_latest_end_date BETWEEN start_date AND end_date);
416: IF g_debug_flag = 'Y' THEN
420: pji_utils.write2log('l_latest_qtr_start_date: '||l_latest_qtr_start_date);
421: END IF;
422: END IF;
423:
424: DELETE FROM pji_time_ent_qtr
425: WHERE ent_qtr_id in (SELECT DISTINCT period_year||quarter_num FROM pji_time_extr_tmp);
426:
427: l_no_rows_deleted := SQL%rowcount;
428:
426:
427: l_no_rows_deleted := SQL%rowcount;
428:
429: IF g_debug_flag = 'Y' THEN
430: pji_utils.write2log('Some records have to be refreshed in PJI_TIME_ENT_QTR table.');
431: pji_utils.write2log(TO_CHAR(l_no_rows_deleted)||' records have been deleted from PJI_TIME_ENT_QTR table.');
432: END IF;
433:
434: INSERT INTO pji_time_ent_qtr
427: l_no_rows_deleted := SQL%rowcount;
428:
429: IF g_debug_flag = 'Y' THEN
430: pji_utils.write2log('Some records have to be refreshed in PJI_TIME_ENT_QTR table.');
431: pji_utils.write2log(TO_CHAR(l_no_rows_deleted)||' records have been deleted from PJI_TIME_ENT_QTR table.');
432: END IF;
433:
434: INSERT INTO pji_time_ent_qtr
435: (ent_qtr_id
430: pji_utils.write2log('Some records have to be refreshed in PJI_TIME_ENT_QTR table.');
431: pji_utils.write2log(TO_CHAR(l_no_rows_deleted)||' records have been deleted from PJI_TIME_ENT_QTR table.');
432: END IF;
433:
434: INSERT INTO pji_time_ent_qtr
435: (ent_qtr_id
436: , ent_year_id
437: , SEQUENCE
438: , NAME
467:
468: l_no_rows_inserted := SQL%rowcount;
469:
470: IF g_debug_flag = 'Y' THEN
471: pji_utils.write2log(TO_CHAR(l_no_rows_inserted)||' records have been inserted into PJI_TIME_ENT_QTR table.');
472: END IF;
473:
474: EXCEPTION
475: WHEN DUP_VAL_ON_INDEX THEN
473:
474: EXCEPTION
475: WHEN DUP_VAL_ON_INDEX THEN
476: IF g_debug_flag = 'Y' THEN
477: pji_utils.write2log('Duplicate records. No records have been inserted into PJI_TIME_ENT_QTR table.');
478: END IF;
479: END LOAD_ENT_QTR;
480:
481: ---------------------------------------------------
916: , l_qtr_end_date
917: , l_period_end_date
918: , l_year_end_date
919: FROM pji_time_ent_period period
920: , pji_time_ent_qtr qtr
921: , pji_time_ent_year yr
922: WHERE 1=1
923: AND period.ent_qtr_id = qtr.ent_qtr_id
924: AND qtr.ent_year_id = yr.ent_year_id
986: , SYSDATE
987: , g_user_id
988: , g_user_id
989: , g_login_id
990: FROM PJI_TIME_ENT_QTR qtr
991: , PJI_TIME_ENT_PERIOD period
992: WHERE 1=1
993: AND qtr.end_date < l_qtr_start_date
994: AND qtr.start_date >= l_year_start_date
1054: , qtr.end_date qtr_end_date
1055: , YEAR.end_date year_end_date
1056: FROM pji_time_extr_tmp extr
1057: , pji_time_ent_period period
1058: , pji_time_ent_qtr qtr
1059: , pji_time_ent_year YEAR
1060: WHERE 1=1
1061: AND TO_NUMBER(extr.period_year||extr.quarter_num||DECODE(LENGTH(extr.period_num),1,'0'||extr.period_num, extr.period_num)) = period.ent_period_id
1062: AND period.ent_qtr_id = qtr.ent_qtr_id
1133: , SYSDATE
1134: , g_user_id
1135: , g_user_id
1136: , g_login_id
1137: FROM PJI_TIME_ENT_QTR
1138: WHERE start_date >= extr_rec.year_start_date
1139: AND start_date <= extr_rec.qtr_start_date
1140: AND end_date < extr_rec.report_date
1141: UNION ALL
1150: , SYSDATE
1151: , g_user_id
1152: , g_user_id
1153: , g_login_id
1154: FROM PJI_TIME_ENT_QTR
1155: WHERE start_date >= extr_rec.year_start_date
1156: AND start_date <= extr_rec.qtr_start_date
1157: AND end_date >= extr_rec.report_date;
1158: