[Home] [Help]
470: -- first check if the current day is loaded
471: -- Bug 4966868: Changed not to count rows
472: BEGIN
473: select 1 into l_count
474: from fii_time_day
475: where report_date = trunc(l_day)
476: and rownum = 1;
477: EXCEPTION
478: WHEN NO_DATA_FOUND THEN
481:
482: -- do an incremental update/insert
483: if l_count = 0 then -- new record, insert
484:
485: insert into fii_time_day
486: (report_date,
487: report_date_julian,
488: start_date,
489: end_date,
534: l_day_row := l_day_row+1;
535:
536: else -- the day has been loaded, update those changed records only
537:
538: update fii_time_day
539: set
540: ent_period_id = l_period_year||l_quarter_num||decode(length(l_period_num),1,'0'||l_period_num, l_period_num),
541: -- lpad(l_period_num,2,'0'), bug 3370185
542: ent_period_start_date = l_start_date,
644: and day between start_date and end_date;
645:
646: begin
647:
648: truncate_table('FII_TIME_DAY');
649:
650: -- ---------------------------------------------------------
651: -- Variable initialization
652: -- ---------------------------------------------------------
668: else
669: l_week_num := get_week_num(l_day,g_week_offset);
670: l_p445_num := get_period_num(l_week_num);
671: l_year_num := to_char(l_day-g_week_offset,'iyyy');
672: insert into fii_time_day
673: (report_date,
674: report_date_julian,
675: start_date,
676: end_date,
1165: fii_util.put_line('LOAD_WEEK : '||to_char(l_week_row)||' records has been populated to Week Level');
1166: end if;
1167:
1168: -- Bug 5624487
1169: update fii_time_day
1170: set week_id = l_year_num||lpad(l_period_num,2,'0')||lpad(l_week_num,2,'0'),
1171: week_start_date = l_week,
1172: week_end_date = l_week_end,
1173: last_update_date = sysdate,
1612:
1613: commit;
1614:
1615: -- Bug 5624487
1616: update fii_time_day
1617: set ent_period_id = to_number(g_una_ent_period_year||g_una_ent_quarter_num||decode(length(g_una_ent_period_num),1,'0'||g_una_ent_period_num, g_una_ent_period_num)),
1618: ent_period_start_date = g_unassigned_day,
1619: ent_period_end_date = g_unassigned_day,
1620: ent_qtr_id = to_number(g_una_ent_period_year||g_una_ent_quarter_num),
2599:
2600: -- Bug 5624487
2601: select max(report_date), min(report_date)
2602: into l_max_date, l_min_date
2603: from fii_time_day
2604: where report_date <> g_unassigned_day;
2605:
2606: if p_load_mode = 'INIT' then
2607:
2714: if g_debug_flag = 'Y' then
2715: fii_util.put_line('LOAD : '||'This is an initial load, all tables will be truncated and re-populated.');
2716: end if;
2717:
2718: truncate_table('FII_TIME_DAY');
2719: truncate_table('FII_TIME_MONTH');
2720: truncate_table('FII_TIME_QTR');
2721: truncate_table('FII_TIME_YEAR');
2722: truncate_table('FII_TIME_WEEK');
2765: fii_util.put_line(' ');
2766: fii_util.start_timer;
2767: end if;
2768:
2769: -- we compare the latest end date defined in GL and the latest end date in fii_time_day
2770: -- to determine if there is new gl period added or not
2771: select max(end_date)
2772: into l_max_gl_date
2773: from gl_periods
2779: -- Bug 4966868: Changed not to count rows
2780: begin
2781: -- Bug 5624487
2782: select 1 into l_count
2783: from fii_time_day
2784: where ent_period_end_date is null
2785: and report_date <> g_unassigned_day
2786: and rownum = 1;
2787: exception
2789: l_count := 0;
2790: end;
2791:
2792: if l_period_updated = 'Y' or l_max_gl_date > l_max_date or l_count > 0 then
2793: -- we populate data from the earliest date in FII_TIME_DAY or the from date parameter, see which one is the earliest
2794: -- to the latest date in FII_TIME_DAY or the to date parameter, see which one is the latest. This is to make sure
2795: -- we will insert new records as well as modify existing records if necessary. If new gl period has been added to
2796: -- an existing quarter, the quarter end date and the year end date needs to be updated.
2797: LOAD_DAY_INC(least(nvl(l_min_date,l_from_date),l_from_date), greatest(nvl(l_max_date,l_to_date),l_to_date));
2790: end;
2791:
2792: if l_period_updated = 'Y' or l_max_gl_date > l_max_date or l_count > 0 then
2793: -- we populate data from the earliest date in FII_TIME_DAY or the from date parameter, see which one is the earliest
2794: -- to the latest date in FII_TIME_DAY or the to date parameter, see which one is the latest. This is to make sure
2795: -- we will insert new records as well as modify existing records if necessary. If new gl period has been added to
2796: -- an existing quarter, the quarter end date and the year end date needs to be updated.
2797: LOAD_DAY_INC(least(nvl(l_min_date,l_from_date),l_from_date), greatest(nvl(l_max_date,l_to_date),l_to_date));
2798: else
2876: if g_debug_flag = 'Y' then
2877: fii_util.start_timer;
2878: end if;
2879: if l_period_updated = 'Y' then
2880: -- we populate data from the earliest date in FII_TIME_DAY or the from date parameter, see which one is the earliest
2881: -- to the latest date in FII_TIME_DAY or the to date parameter, see which one is the latest. This is to make sure
2882: -- we will insert new records as well as existing records that we have been truncated
2883: LOAD_ENT_PERIOD(least(nvl(l_min_date,l_from_date),l_from_date), greatest(nvl(l_max_date,l_to_date),l_to_date));
2884: else
2877: fii_util.start_timer;
2878: end if;
2879: if l_period_updated = 'Y' then
2880: -- we populate data from the earliest date in FII_TIME_DAY or the from date parameter, see which one is the earliest
2881: -- to the latest date in FII_TIME_DAY or the to date parameter, see which one is the latest. This is to make sure
2882: -- we will insert new records as well as existing records that we have been truncated
2883: LOAD_ENT_PERIOD(least(nvl(l_min_date,l_from_date),l_from_date), greatest(nvl(l_max_date,l_to_date),l_to_date));
2884: else
2885: LOAD_ENT_PERIOD(l_from_date, l_to_date);
2893: g_phase := 'Load Enterprise Quarter Level';
2894: if g_debug_flag = 'Y' then
2895: fii_util.start_timer;
2896: end if;
2897: -- we populate data from the earliest date in FII_TIME_DAY or the from date parameter, see which one is the earliest
2898: -- to the latest date in FII_TIME_DAY or the to date parameter, see which one is the latest. This is to make sure
2899: -- we will insert new records as well as existing records that we have been truncated
2900: LOAD_ENT_QUARTER(least(nvl(l_min_date,l_from_date),l_from_date), greatest(nvl(l_max_date,l_to_date),l_to_date));
2901: if g_debug_flag = 'Y' then
2894: if g_debug_flag = 'Y' then
2895: fii_util.start_timer;
2896: end if;
2897: -- we populate data from the earliest date in FII_TIME_DAY or the from date parameter, see which one is the earliest
2898: -- to the latest date in FII_TIME_DAY or the to date parameter, see which one is the latest. This is to make sure
2899: -- we will insert new records as well as existing records that we have been truncated
2900: LOAD_ENT_QUARTER(least(nvl(l_min_date,l_from_date),l_from_date), greatest(nvl(l_max_date,l_to_date),l_to_date));
2901: if g_debug_flag = 'Y' then
2902: fii_util.stop_timer;
2907: g_phase := 'Load Enterprise Year Level';
2908: if g_debug_flag = 'Y' then
2909: fii_util.start_timer;
2910: end if;
2911: -- we populate data from the earliest date in FII_TIME_DAY or the from date parameter, see which one is the earliest
2912: -- to the latest date in FII_TIME_DAY or the to date parameter, see which one is the latest. This is to make sure
2913: -- we will insert new records as well as existing records that we have been truncated
2914: LOAD_ENT_YEAR(least(nvl(l_min_date,l_from_date),l_from_date), greatest(nvl(l_max_date,l_to_date),l_to_date));
2915: if g_debug_flag = 'Y' then
2908: if g_debug_flag = 'Y' then
2909: fii_util.start_timer;
2910: end if;
2911: -- we populate data from the earliest date in FII_TIME_DAY or the from date parameter, see which one is the earliest
2912: -- to the latest date in FII_TIME_DAY or the to date parameter, see which one is the latest. This is to make sure
2913: -- we will insert new records as well as existing records that we have been truncated
2914: LOAD_ENT_YEAR(least(nvl(l_min_date,l_from_date),l_from_date), greatest(nvl(l_max_date,l_to_date),l_to_date));
2915: if g_debug_flag = 'Y' then
2916: fii_util.stop_timer;
3080: if g_debug_flag = 'Y' then
3081: fii_util.start_timer;
3082: end if;
3083:
3084: -- note: we don't gather stats on FII_TIME_DAY
3085: -- as this should be done by RSG
3086: gather_table_stats('FII_TIME_MONTH');
3087:
3088: if g_debug_flag = 'Y' then
3350: -- Bug 5624487
3351: cursor c1 is
3352: select report_date, ent_period_start_date, ent_qtr_start_date,
3353: ent_year_start_date, week_start_date
3354: from FII_TIME_DAY
3355: where report_date <> g_unassigned_day;
3356: --*this would be incorrect: where report_date between l_from_date and l_to_date
3357:
3358: begin
3834:
3835: BEGIN
3836: SELECT week_start_date
3837: INTO l_Week_Start_Date
3838: FROM fii_time_day
3839: WHERE report_date = cur_Fiscal_Days.report_date;
3840:
3841: l_No_Week_Info := 'N';
3842:
4178: l_return_date := bis_common_parameters.get_global_start_date;
4179: end if;
4180: else
4181: select least(nvl(min(start_date),bis_common_parameters.get_global_start_date) , bis_common_parameters.get_global_start_date) into l_return_date
4182: from fii_time_day;
4183: end if;
4184:
4185: return fnd_date.date_to_displaydt(l_return_date);
4186: