[Home] [Help]
480:
481: ---------------------------------------------------
482: -- PRIVATE PROCEDURE load_ent_year
483: -- This procedure incrementally maintains year
484: -- entries in PJI_TIME_ENT_YEAR.
485: ---------------------------------------------------
486: PROCEDURE LOAD_ENT_YEAR IS
487: l_no_rows_deleted NUMBER := 0;
488: l_no_rows_inserted NUMBER := 0;
501: l_start_yr_id
502: , l_end_yr_id
503: , l_earliest_yr_end_date
504: , l_latest_yr_start_date
505: FROM pji_time_ent_year
506: WHERE 1=1
507: AND (g_earliest_start_date BETWEEN start_date AND end_date)
508: OR (g_latest_end_date BETWEEN start_date AND end_date);
509: IF g_debug_flag = 'Y' THEN
513: pji_utils.write2log('l_latest_yr_start_date: '||l_latest_yr_start_date);
514: END IF;
515: END IF;
516:
517: DELETE FROM pji_time_ent_year
518: WHERE ent_year_id in (SELECT DISTINCT period_year from pji_time_extr_tmp);
519:
520: l_no_rows_deleted := SQL%rowcount;
521:
519:
520: l_no_rows_deleted := SQL%rowcount;
521:
522: IF g_debug_flag = 'Y' THEN
523: pji_utils.write2log('Some records have to be refreshed in PJI_TIME_ENT_YEAR table.');
524: pji_utils.write2log(TO_CHAR(l_no_rows_deleted)||' records have been deleted from PJI_TIME_ENT_YEAR table.');
525: END IF;
526:
527: INSERT INTO pji_time_ent_year
520: l_no_rows_deleted := SQL%rowcount;
521:
522: IF g_debug_flag = 'Y' THEN
523: pji_utils.write2log('Some records have to be refreshed in PJI_TIME_ENT_YEAR table.');
524: pji_utils.write2log(TO_CHAR(l_no_rows_deleted)||' records have been deleted from PJI_TIME_ENT_YEAR table.');
525: END IF;
526:
527: INSERT INTO pji_time_ent_year
528: (ent_year_id
523: pji_utils.write2log('Some records have to be refreshed in PJI_TIME_ENT_YEAR table.');
524: pji_utils.write2log(TO_CHAR(l_no_rows_deleted)||' records have been deleted from PJI_TIME_ENT_YEAR table.');
525: END IF;
526:
527: INSERT INTO pji_time_ent_year
528: (ent_year_id
529: , period_set_name
530: , period_type
531: , SEQUENCE
557:
558: l_no_rows_inserted := SQL%rowcount;
559:
560: IF g_debug_flag = 'Y' THEN
561: pji_utils.write2log(TO_CHAR(l_no_rows_inserted)||' records have been inserted into PJI_TIME_ENT_YEAR table.');
562: END IF;
563:
564: EXCEPTION
565: WHEN DUP_VAL_ON_INDEX THEN
563:
564: EXCEPTION
565: WHEN DUP_VAL_ON_INDEX THEN
566: IF g_debug_flag = 'Y' THEN
567: pji_utils.write2log('Duplicate records. No records have been inserted into PJI_TIME_ENT_YEAR table.');
568: END IF;
569: END LOAD_ENT_YEAR;
570:
571: FUNCTION LOAD_CAL_PERIOD( p_calendar_id NUMBER
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
925: AND period.start_date = g_earliest_start_date;
951: , SYSDATE
952: , g_user_id
953: , g_user_id
954: , g_login_id
955: FROM PJI_TIME_ENT_YEAR year
956: , PJI_TIME_ENT_PERIOD period
957: WHERE year.end_date < l_year_start_date
958: AND period.start_date >= g_earliest_start_date;
959:
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
1063: AND qtr.ent_year_id = YEAR.ent_year_id)
1181: , SYSDATE
1182: , g_user_id
1183: , g_user_id
1184: , g_login_id
1185: FROM PJI_TIME_ENT_YEAR
1186: WHERE extr_rec.report_date BETWEEN start_date AND end_date
1187: UNION ALL
1188: SELECT
1189: -1
1196: , SYSDATE
1197: , g_user_id
1198: , g_user_id
1199: , g_login_id
1200: FROM PJI_TIME_ENT_YEAR
1201: WHERE end_date < extr_rec.report_date;
1202:
1203: l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
1204: