[Home] [Help]
1: PACKAGE BODY FII_TIME_C AS
2: /*$Header: FIICMT1B.pls 120.49 2007/03/01 07:22:08 arcdixit ship $*/
3:
4: g_schema varchar2(30);
5: g_period_set_name varchar2(15) := null;
1970: -- determine if any rows will be different between current set per
1971: -- gl tables and current set per fii tables
1972: --Bug 3543939. Get the Calendar id's into a temporary table for use in Load_Time_Cal_Rpt_Struct
1973:
1974: Insert into fii_time_cal_gt(calendar_id)
1975: select calendar_id
1976: from (
1977: (select
1978: to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year||glp.quarter_num||decode(length(glp.period_num),1,'0'||glp.period_num, glp.period_num))
1984: , glp.start_date
1985: , glp.end_date
1986: from
1987: gl_periods glp
1988: , fii_time_cal_name cal_name
1989: where glp.adjustment_period_flag = 'N'
1990: and glp.period_set_name = cal_name.period_set_name
1991: and glp.period_type = cal_name.period_type
1992: minus
1998: , name
1999: , start_date
2000: , end_date
2001: from
2002: fii_time_cal_period
2003: )
2004: union all
2005: (select
2006: cal_period_id
2010: , name
2011: , start_date
2012: , end_date
2013: from
2014: fii_time_cal_period
2015: minus
2016: select
2017: to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year||glp.quarter_num||decode(length(glp.period_num),1,'0'||glp.period_num, glp.period_num))
2018: -- lpad(gl.period_num,2,'0')) bug 3370185
2023: , glp.start_date
2024: , glp.end_date
2025: from
2026: gl_periods glp
2027: , fii_time_cal_name cal_name
2028: where glp.adjustment_period_flag = 'N'
2029: and glp.period_set_name = cal_name.period_set_name
2030: and glp.period_type = cal_name.period_type
2031: )
2037: if g_debug_flag = 'Y' then
2038: fii_util.put_line('LOAD_CAL_PERIOD : '||to_char(l_period_changed)||' Calendars have changed in GL');
2039: end if;
2040:
2041: --For Bug 3543939. If there is a difference then truncate and repopulate FII_TIME_CAL_PERIOD
2042:
2043: -- Bug 4966868: Changed not to count rows
2044: BEGIN
2045: select 1 into l_diff_rows
2042:
2043: -- Bug 4966868: Changed not to count rows
2044: BEGIN
2045: select 1 into l_diff_rows
2046: from fii_time_cal_gt
2047: where rownum = 1;
2048: EXCEPTION
2049: WHEN NO_DATA_FOUND THEN
2050: l_diff_rows := 0;
2061: return;
2062:
2063: end if;
2064:
2065: truncate_table('FII_TIME_CAL_PERIOD');
2066:
2067: -- ---------------------------------------------------------
2068: -- Variable initialization
2069: -- ---------------------------------------------------------
2071:
2072: -- ----------------------
2073: -- Populate Financial Period Level
2074: -- ----------------------
2075: insert into fii_time_cal_period
2076: (cal_period_id,
2077: cal_qtr_id,
2078: calendar_id,
2079: sequence,
2097: sysdate,
2098: g_user_id,
2099: g_user_id,
2100: g_login_id
2101: from gl_periods glp, fii_time_cal_name cal_name
2102: where glp.adjustment_period_flag='N'
2103: and glp.period_set_name=cal_name.period_set_name
2104: and glp.period_type=cal_name.period_type;
2105:
2110: if g_debug_flag = 'Y' then
2111: fii_util.put_line('LOAD_CAL_PERIOD : '||to_char(l_period_row)||' records has been populated to Financial Period Level');
2112: end if;
2113:
2114: gather_table_stats('FII_TIME_CAL_PERIOD');
2115:
2116: if g_debug_flag = 'Y' then
2117: fii_util.put_line('LOAD_CAL_PERIOD : '||'Gathered statistics for Financial Period Level');
2118: end if;
2149: , min(gl2.start_date)
2150: , max(gl2.end_date)
2151: from
2152: gl_periods glp
2153: , fii_time_cal_name cal_name
2154: , (select period_set_name
2155: , period_type
2156: , period_year
2157: , quarter_num
2188: , name
2189: , start_date
2190: , end_date
2191: from
2192: fii_time_cal_qtr
2193: )
2194: union all
2195: (select
2196: cal_qtr_id
2200: , name
2201: , start_date
2202: , end_date
2203: from
2204: fii_time_cal_qtr
2205: minus
2206: select
2207: distinct to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year||glp.quarter_num)
2208: , to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year)
2212: , min(gl2.start_date)
2213: , max(gl2.end_date)
2214: from
2215: gl_periods glp
2216: , fii_time_cal_name cal_name
2217: , (select period_set_name
2218: , period_type
2219: , period_year
2220: , quarter_num
2261: return;
2262:
2263: end if;
2264:
2265: truncate_table('FII_TIME_CAL_QTR');
2266:
2267: -- ---------------------------------------------------------
2268: -- Variable initialization
2269: -- ---------------------------------------------------------
2271:
2272: -- ----------------------
2273: -- Populate Financial Quarter Level
2274: -- ----------------------
2275: insert into fii_time_cal_qtr
2276: (cal_qtr_id,
2277: cal_year_id,
2278: calendar_id,
2279: sequence,
2296: sysdate,
2297: g_user_id,
2298: g_user_id,
2299: g_login_id
2300: from gl_periods glp, fii_time_cal_name cal_name,
2301: (select period_set_name, period_type, period_year, quarter_num, min(start_date) start_date, max(end_date) end_date
2302: from gl_periods
2303: where adjustment_period_flag='N'
2304: group by period_set_name, period_type, period_year, quarter_num) gl2
2322: if g_debug_flag = 'Y' then
2323: fii_util.put_line('LOAD_CAL_QUARTER : '||to_char(l_qtr_row)||' records has been populated to Financial Quarter Level');
2324: end if;
2325:
2326: gather_table_stats('FII_TIME_CAL_QTR');
2327:
2328: if g_debug_flag = 'Y' then
2329: fii_util.put_line('LOAD_CAL_QUARTER : '||'Gathered statistics for Financial Quarter Level');
2330: end if;
2360: , min(gl2.start_date)
2361: , max(gl2.end_date)
2362: from
2363: gl_periods glp
2364: , fii_time_cal_name cal_name
2365: , (select period_set_name
2366: , period_type
2367: , period_year
2368: , min(start_date) start_date
2392: , name
2393: , start_date
2394: , end_date
2395: from
2396: fii_time_cal_year
2397: )
2398: union all
2399: (select
2400: cal_year_id
2403: , name
2404: , start_date
2405: , end_date
2406: from
2407: fii_time_cal_year
2408: minus
2409: select
2410: distinct to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year)
2411: , to_number(lpad(cal_name.calendar_id,3,'0'))
2414: , min(gl2.start_date)
2415: , max(gl2.end_date)
2416: from
2417: gl_periods glp
2418: , fii_time_cal_name cal_name
2419: , (select period_set_name
2420: , period_type
2421: , period_year
2422: , min(start_date) start_date
2457: return;
2458:
2459: end if;
2460:
2461: truncate_table('FII_TIME_CAL_YEAR');
2462:
2463: -- ---------------------------------------------------------
2464: -- Variable initialization
2465: -- ---------------------------------------------------------
2467:
2468: -- ----------------------
2469: -- Populate Financial Year Level
2470: -- ----------------------
2471: insert into fii_time_cal_year
2472: (cal_year_id,
2473: calendar_id,
2474: sequence,
2475: name,
2490: sysdate,
2491: g_user_id,
2492: g_user_id,
2493: g_login_id
2494: from gl_periods glp, fii_time_cal_name cal_name,
2495: (select period_set_name, period_type, period_year, min(start_date) start_date, max(end_date) end_date
2496: from gl_periods
2497: where adjustment_period_flag='N'
2498: group by period_set_name, period_type, period_year) gl2
2511: if g_debug_flag = 'Y' then
2512: fii_util.put_line('LOAD_CAL_YEAR : '||to_char(l_year_row)||' records has been populated to Financial Year Level');
2513: end if;
2514:
2515: gather_table_stats('FII_TIME_CAL_YEAR');
2516:
2517: if g_debug_flag = 'Y' then
2518: fii_util.put_line('LOAD_CAL_YEAR : '||'Gathered statistics for Financial Year Level');
2519: end if;
2724: truncate_table('FII_TIME_YEAR445');
2725: truncate_table('FII_TIME_ENT_PERIOD');
2726: truncate_table('FII_TIME_ENT_QTR');
2727: truncate_table('FII_TIME_ENT_YEAR');
2728: truncate_table('FII_TIME_CAL_NAME');
2729: truncate_table('FII_TIME_CAL_PERIOD');
2730: truncate_table('FII_TIME_CAL_QTR');
2731: truncate_table('FII_TIME_CAL_YEAR');
2732: truncate_table('FII_TIME_RPT_STRUCT');
2725: truncate_table('FII_TIME_ENT_PERIOD');
2726: truncate_table('FII_TIME_ENT_QTR');
2727: truncate_table('FII_TIME_ENT_YEAR');
2728: truncate_table('FII_TIME_CAL_NAME');
2729: truncate_table('FII_TIME_CAL_PERIOD');
2730: truncate_table('FII_TIME_CAL_QTR');
2731: truncate_table('FII_TIME_CAL_YEAR');
2732: truncate_table('FII_TIME_RPT_STRUCT');
2733: truncate_table('FII_TIME_CAL_RPT_STRUCT');
2726: truncate_table('FII_TIME_ENT_QTR');
2727: truncate_table('FII_TIME_ENT_YEAR');
2728: truncate_table('FII_TIME_CAL_NAME');
2729: truncate_table('FII_TIME_CAL_PERIOD');
2730: truncate_table('FII_TIME_CAL_QTR');
2731: truncate_table('FII_TIME_CAL_YEAR');
2732: truncate_table('FII_TIME_RPT_STRUCT');
2733: truncate_table('FII_TIME_CAL_RPT_STRUCT');
2734: truncate_table('FII_TIME_STRUCTURES');
2727: truncate_table('FII_TIME_ENT_YEAR');
2728: truncate_table('FII_TIME_CAL_NAME');
2729: truncate_table('FII_TIME_CAL_PERIOD');
2730: truncate_table('FII_TIME_CAL_QTR');
2731: truncate_table('FII_TIME_CAL_YEAR');
2732: truncate_table('FII_TIME_RPT_STRUCT');
2733: truncate_table('FII_TIME_CAL_RPT_STRUCT');
2734: truncate_table('FII_TIME_STRUCTURES');
2735: truncate_table('FII_TIME_ROLLING_OFFSETS');
2729: truncate_table('FII_TIME_CAL_PERIOD');
2730: truncate_table('FII_TIME_CAL_QTR');
2731: truncate_table('FII_TIME_CAL_YEAR');
2732: truncate_table('FII_TIME_RPT_STRUCT');
2733: truncate_table('FII_TIME_CAL_RPT_STRUCT');
2734: truncate_table('FII_TIME_STRUCTURES');
2735: truncate_table('FII_TIME_ROLLING_OFFSETS');
2736: else
2737: -- if it is incremental loading, check if there is any modified period
2971: commit;
2972:
2973: ------------------------------------------------------------------------------
2974: --Bug 3155474: call BIS wrapper to handle force parallel on MVs
2975: ----- dbms_mview.REFRESH('FII_TIME_CAL_DAY_MV','C');
2976:
2977: BIS_MV_REFRESH.refresh_wrapper ('FII_TIME_CAL_DAY_MV', 'C');
2978: ------------------------------------------------------------------------------
2979:
2973: ------------------------------------------------------------------------------
2974: --Bug 3155474: call BIS wrapper to handle force parallel on MVs
2975: ----- dbms_mview.REFRESH('FII_TIME_CAL_DAY_MV','C');
2976:
2977: BIS_MV_REFRESH.refresh_wrapper ('FII_TIME_CAL_DAY_MV', 'C');
2978: ------------------------------------------------------------------------------
2979:
2980: if g_debug_flag = 'Y' then
2981: fii_util.stop_timer;
3116: truncate_table('FII_TIME_YEAR445');
3117: truncate_table('FII_TIME_ENT_PERIOD');
3118: truncate_table('FII_TIME_ENT_QTR');
3119: truncate_table('FII_TIME_ENT_YEAR');
3120: truncate_table('FII_TIME_CAL_PERIOD');
3121: truncate_table('FII_TIME_CAL_QTR');
3122: truncate_table('FII_TIME_CAL_YEAR');
3123: truncate_table('FII_TIME_RPT_STRUCT');
3124: truncate_table('FII_TIME_CAL_RPT_STRUCT');
3117: truncate_table('FII_TIME_ENT_PERIOD');
3118: truncate_table('FII_TIME_ENT_QTR');
3119: truncate_table('FII_TIME_ENT_YEAR');
3120: truncate_table('FII_TIME_CAL_PERIOD');
3121: truncate_table('FII_TIME_CAL_QTR');
3122: truncate_table('FII_TIME_CAL_YEAR');
3123: truncate_table('FII_TIME_RPT_STRUCT');
3124: truncate_table('FII_TIME_CAL_RPT_STRUCT');
3125:
3118: truncate_table('FII_TIME_ENT_QTR');
3119: truncate_table('FII_TIME_ENT_YEAR');
3120: truncate_table('FII_TIME_CAL_PERIOD');
3121: truncate_table('FII_TIME_CAL_QTR');
3122: truncate_table('FII_TIME_CAL_YEAR');
3123: truncate_table('FII_TIME_RPT_STRUCT');
3124: truncate_table('FII_TIME_CAL_RPT_STRUCT');
3125:
3126: if g_debug_flag = 'Y' then
3120: truncate_table('FII_TIME_CAL_PERIOD');
3121: truncate_table('FII_TIME_CAL_QTR');
3122: truncate_table('FII_TIME_CAL_YEAR');
3123: truncate_table('FII_TIME_RPT_STRUCT');
3124: truncate_table('FII_TIME_CAL_RPT_STRUCT');
3125:
3126: if g_debug_flag = 'Y' then
3127: fii_util.put_line(' ');
3128: end if;
3240: FROM ( SELECT DISTINCT glp.period_set_name , glp.period_type
3241: FROM gl_periods glp
3242: MINUS
3243: SELECT DISTINCT cal.period_set_name, cal.period_type
3244: FROM fii_time_cal_name cal
3245: ) inline_view,
3246: gl_periods gp
3247: WHERE inline_view.period_set_name = gp.period_set_name
3248: and inline_view.period_type = gp.period_type
3258: l_name_row := 0;
3259:
3260: select nvl(max(calendar_id),0)
3261: into l_max_cal_name
3262: from fii_time_cal_name;
3263:
3264: if g_schema is null then
3265: IF(FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry, g_schema)) THEN
3266: NULL;
3271: -- Populate Calendar Name Level
3272: -- ----------------------
3273: FOR new_cal_rec IN new_cal LOOP
3274:
3275: insert into fii_time_cal_name
3276: (calendar_id,
3277: period_set_name,
3278: period_type,
3279: name,
3305: end if;
3306:
3307: if l_name_row > 0 then
3308:
3309: gather_table_stats('FII_TIME_CAL_NAME');
3310:
3311: if g_debug_flag = 'Y' then
3312: fii_util.put_line('LOAD_CAL_NAME : '||'Gathered statistics for Calendar Name Level');
3313: end if;
3748:
3749: --Bug 3543939. Get the minimum from date and maximum to date
3750: SELECT nvl(least(min(report_date),l_from_date),l_from_date),nvl(greatest(max(report_date),l_to_date),l_to_date)
3751: INTO l_min_date,l_max_date
3752: from FII_TIME_CAL_RPT_STRUCT;
3753:
3754: --Bug 3543939. The Calendar id's to be picked from the temporary table populated in Load_Cal_Period
3755:
3756: For cur_Fiscal_Calendar IN (
3754: --Bug 3543939. The Calendar id's to be picked from the temporary table populated in Load_Cal_Period
3755:
3756: For cur_Fiscal_Calendar IN (
3757: SELECT distinct calendar_id
3758: FROM fii_time_cal_gt) LOOP
3759:
3760: --Reset the l_from_date and l_to_date to the parameters passed
3761:
3762: l_from_date:=p_from_date;
3767: BEGIN
3768:
3769: SELECT 'T'
3770: INTO l_full_extraction_flag
3771: FROM FII_TIME_CAL_RPT_STRUCT
3772: WHERE REPORT_DATE >= p_from_date
3773: AND CALENDAR_ID = cur_Fiscal_Calendar.CALENDAR_ID
3774: AND ROWNUM <= 1;
3775: EXCEPTION
3783: -- Delete all records for the calendar
3784: -- and reset the from and to date for
3785: -- extraction
3786:
3787: DELETE FII_TIME_CAL_RPT_STRUCT
3788: WHERE CALENDAR_ID = cur_Fiscal_Calendar.CALENDAR_ID;
3789: l_from_date := l_min_date;
3790: l_to_date := l_max_date;
3791:
3796: , report_date
3797: , cal_period_start_date period_start_date
3798: , cal_qtr_start_date qtr_start_date
3799: , cal_year_start_date year_start_date
3800: FROM fii_time_cal_day_mv
3801: WHERE calendar_id = cur_Fiscal_Calendar.calendar_id
3802: AND report_date BETWEEN l_from_date AND l_to_date) LOOP
3803:
3804: INSERT INTO FII_TIME_CAL_RPT_STRUCT
3800: FROM fii_time_cal_day_mv
3801: WHERE calendar_id = cur_Fiscal_Calendar.calendar_id
3802: AND report_date BETWEEN l_from_date AND l_to_date) LOOP
3803:
3804: INSERT INTO FII_TIME_CAL_RPT_STRUCT
3805: ( calendar_id
3806: , calendar_type
3807: , report_date
3808: , time_id
3853: AND start_date < l_Week_Start_Date;
3854:
3855: WHILE l_Day >= cur_Fiscal_Days.period_start_date LOOP
3856: IF l_Day >= l_Week_Start_Date THEN
3857: INSERT INTO FII_TIME_CAL_RPT_STRUCT
3858: ( calendar_id
3859: , calendar_type
3860: , report_date
3861: , time_id
3881:
3882: l_Row := l_Row + SQL%ROWCOUNT;
3883:
3884: ELSIF l_Day >= cur_Fiscal_Days.period_start_date AND l_Day < l_Earliest_Week THEN
3885: INSERT INTO FII_TIME_CAL_RPT_STRUCT
3886: ( calendar_id
3887: , calendar_type
3888: , report_date
3889: , time_id
3915: END LOOP;
3916:
3917: COMMIT;
3918:
3919: INSERT INTO FII_TIME_CAL_RPT_STRUCT
3920: ( calendar_id
3921: , calendar_type
3922: , report_date
3923: , time_id
3948:
3949: COMMIT;
3950:
3951: ELSE
3952: INSERT INTO FII_TIME_CAL_RPT_STRUCT
3953: ( calendar_id
3954: , calendar_type
3955: , report_date
3956: , time_id
3972: , SYSDATE
3973: , g_user_id
3974: , g_user_id
3975: , g_login_id
3976: FROM FII_TIME_CAL_DAY_MV
3977: WHERE calendar_id = cur_Fiscal_Days.calendar_id
3978: AND report_date BETWEEN cur_Fiscal_Days.period_start_date AND l_Day;
3979:
3980: l_Row := l_Row + SQL%ROWCOUNT;
3981:
3982: COMMIT;
3983: END IF;
3984:
3985: INSERT INTO FII_TIME_CAL_RPT_STRUCT
3986: ( calendar_id
3987: , calendar_type
3988: , report_date
3989: , time_id
4005: , SYSDATE
4006: , g_user_id
4007: , g_user_id
4008: , g_login_id
4009: FROM FII_TIME_CAL_PERIOD
4010: WHERE start_date >= cur_Fiscal_Days.qtr_start_date
4011: AND start_date <= cur_Fiscal_Days.period_start_date
4012: AND end_date < cur_Fiscal_Days.report_date
4013: AND calendar_id = cur_Fiscal_Days.calendar_id
4023: , SYSDATE
4024: , g_user_id
4025: , g_user_id
4026: , g_login_id
4027: FROM FII_TIME_CAL_PERIOD
4028: WHERE start_date >= cur_Fiscal_Days.qtr_start_date
4029: AND start_date <= cur_Fiscal_Days.period_start_date
4030: AND end_date >= cur_Fiscal_Days.report_date
4031: AND calendar_id = cur_Fiscal_Days.calendar_id;
4033: l_Row := l_Row + SQL%ROWCOUNT;
4034:
4035: COMMIT;
4036:
4037: INSERT INTO FII_TIME_CAL_RPT_STRUCT
4038: ( calendar_id
4039: , calendar_type
4040: , report_date
4041: , time_id
4057: , SYSDATE
4058: , g_user_id
4059: , g_user_id
4060: , g_login_id
4061: FROM FII_TIME_CAL_QTR
4062: WHERE start_date >= cur_Fiscal_Days.year_start_date
4063: AND start_date <= cur_Fiscal_Days.qtr_start_date
4064: AND end_date < cur_Fiscal_Days.report_date
4065: AND calendar_id = cur_Fiscal_Days.calendar_id
4075: , SYSDATE
4076: , g_user_id
4077: , g_user_id
4078: , g_login_id
4079: FROM FII_TIME_CAL_QTR
4080: WHERE start_date >= cur_Fiscal_Days.year_start_date
4081: AND start_date <= cur_Fiscal_Days.qtr_start_date
4082: AND end_date >= cur_Fiscal_Days.report_date
4083: AND calendar_id = cur_Fiscal_Days.calendar_id;
4085: l_Row := l_Row + SQL%ROWCOUNT;
4086:
4087: COMMIT;
4088:
4089: INSERT INTO FII_TIME_CAL_RPT_STRUCT
4090: ( calendar_id
4091: , calendar_type
4092: , report_date
4093: , time_id
4109: , SYSDATE
4110: , g_user_id
4111: , g_user_id
4112: , g_login_id
4113: FROM FII_TIME_CAL_YEAR
4114: WHERE calendar_id = cur_Fiscal_Days.calendar_id
4115: AND cur_Fiscal_Days.report_date BETWEEN start_date AND end_date
4116: UNION ALL
4117: SELECT
4125: , SYSDATE
4126: , g_user_id
4127: , g_user_id
4128: , g_login_id
4129: FROM FII_TIME_CAL_YEAR
4130: WHERE calendar_id = cur_Fiscal_Days.calendar_id
4131: AND end_date < cur_Fiscal_Days.report_date
4132: AND end_date >= g_global_start_date;
4133:
4144: END IF;
4145:
4146: if l_row > 0 then
4147:
4148: gather_table_stats('FII_TIME_CAL_RPT_STRUCT');
4149:
4150: if g_debug_flag = 'Y' then
4151: fii_util.put_line('LOAD_TIME_CAL_RPT_STRUCT :'||'Gathered statistics for Financial Calendars');
4152: end if;
4211: return fnd_date.date_to_displaydt(l_return_date);
4212:
4213: end DEFAULT_LOAD_TO_DATE;
4214:
4215: END FII_TIME_C;