[Home] [Help]
1: PACKAGE BODY HRI_TIME_C AS
2: /*$Header: HRICMT1B.pls 120.0 2011/11/18 10:15:05 vepravee noship $*/
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 HRI 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 HRI_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: , HRI_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: HRI_time_cal_period
2003: )
2004: union all
2005: (select
2006: cal_period_id
2010: , name
2011: , start_date
2012: , end_date
2013: from
2014: HRI_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: , HRI_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: HRI_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 HRI_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 HRI_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('HRI_TIME_CAL_PERIOD');
2066:
2067: -- ---------------------------------------------------------
2068: -- Variable initialization
2069: -- ---------------------------------------------------------
2071:
2072: -- ----------------------
2073: -- Populate Financial Period Level
2074: -- ----------------------
2075: insert into HRI_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, HRI_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: HRI_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('HRI_TIME_CAL_PERIOD');
2115:
2116: if g_debug_flag = 'Y' then
2117: HRI_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: , HRI_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: HRI_time_cal_qtr
2193: )
2194: union all
2195: (select
2196: cal_qtr_id
2200: , name
2201: , start_date
2202: , end_date
2203: from
2204: HRI_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: , HRI_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('HRI_TIME_CAL_QTR');
2266:
2267: -- ---------------------------------------------------------
2268: -- Variable initialization
2269: -- ---------------------------------------------------------
2271:
2272: -- ----------------------
2273: -- Populate Financial Quarter Level
2274: -- ----------------------
2275: insert into HRI_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, HRI_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: HRI_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('HRI_TIME_CAL_QTR');
2327:
2328: if g_debug_flag = 'Y' then
2329: HRI_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: , HRI_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: HRI_time_cal_year
2397: )
2398: union all
2399: (select
2400: cal_year_id
2403: , name
2404: , start_date
2405: , end_date
2406: from
2407: HRI_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: , HRI_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('HRI_TIME_CAL_YEAR');
2462:
2463: -- ---------------------------------------------------------
2464: -- Variable initialization
2465: -- ---------------------------------------------------------
2467:
2468: -- ----------------------
2469: -- Populate Financial Year Level
2470: -- ----------------------
2471: insert into HRI_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, HRI_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: HRI_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('HRI_TIME_CAL_YEAR');
2516:
2517: if g_debug_flag = 'Y' then
2518: HRI_util.put_line('LOAD_CAL_YEAR : '||'Gathered statistics for Financial Year Level');
2519: end if;
2724: truncate_table('HRI_TIME_YEAR445');
2725: truncate_table('HRI_TIME_ENT_PERIOD');
2726: truncate_table('HRI_TIME_ENT_QTR');
2727: truncate_table('HRI_TIME_ENT_YEAR');
2728: truncate_table('HRI_TIME_CAL_NAME');
2729: truncate_table('HRI_TIME_CAL_PERIOD');
2730: truncate_table('HRI_TIME_CAL_QTR');
2731: truncate_table('HRI_TIME_CAL_YEAR');
2732: truncate_table('HRI_TIME_RPT_STRUCT');
2725: truncate_table('HRI_TIME_ENT_PERIOD');
2726: truncate_table('HRI_TIME_ENT_QTR');
2727: truncate_table('HRI_TIME_ENT_YEAR');
2728: truncate_table('HRI_TIME_CAL_NAME');
2729: truncate_table('HRI_TIME_CAL_PERIOD');
2730: truncate_table('HRI_TIME_CAL_QTR');
2731: truncate_table('HRI_TIME_CAL_YEAR');
2732: truncate_table('HRI_TIME_RPT_STRUCT');
2733: truncate_table('HRI_TIME_CAL_RPT_STRUCT');
2726: truncate_table('HRI_TIME_ENT_QTR');
2727: truncate_table('HRI_TIME_ENT_YEAR');
2728: truncate_table('HRI_TIME_CAL_NAME');
2729: truncate_table('HRI_TIME_CAL_PERIOD');
2730: truncate_table('HRI_TIME_CAL_QTR');
2731: truncate_table('HRI_TIME_CAL_YEAR');
2732: truncate_table('HRI_TIME_RPT_STRUCT');
2733: truncate_table('HRI_TIME_CAL_RPT_STRUCT');
2734: truncate_table('HRI_TIME_STRUCTURES');
2727: truncate_table('HRI_TIME_ENT_YEAR');
2728: truncate_table('HRI_TIME_CAL_NAME');
2729: truncate_table('HRI_TIME_CAL_PERIOD');
2730: truncate_table('HRI_TIME_CAL_QTR');
2731: truncate_table('HRI_TIME_CAL_YEAR');
2732: truncate_table('HRI_TIME_RPT_STRUCT');
2733: truncate_table('HRI_TIME_CAL_RPT_STRUCT');
2734: truncate_table('HRI_TIME_STRUCTURES');
2735: truncate_table('HRI_TIME_ROLLING_OFFSETS');
2729: truncate_table('HRI_TIME_CAL_PERIOD');
2730: truncate_table('HRI_TIME_CAL_QTR');
2731: truncate_table('HRI_TIME_CAL_YEAR');
2732: truncate_table('HRI_TIME_RPT_STRUCT');
2733: truncate_table('HRI_TIME_CAL_RPT_STRUCT');
2734: truncate_table('HRI_TIME_STRUCTURES');
2735: truncate_table('HRI_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('HRI_TIME_CAL_DAY_MV','C');
2976:
2977: BIS_MV_REFRESH.refresh_wrapper ('HRI_TIME_CAL_DAY_MV', 'C');
2978: ------------------------------------------------------------------------------
2979:
2973: ------------------------------------------------------------------------------
2974: --Bug 3155474: call BIS wrapper to handle force parallel on MVs
2975: ----- dbms_mview.REFRESH('HRI_TIME_CAL_DAY_MV','C');
2976:
2977: BIS_MV_REFRESH.refresh_wrapper ('HRI_TIME_CAL_DAY_MV', 'C');
2978: ------------------------------------------------------------------------------
2979:
2980: if g_debug_flag = 'Y' then
2981: HRI_util.stop_timer;
3116: truncate_table('HRI_TIME_YEAR445');
3117: truncate_table('HRI_TIME_ENT_PERIOD');
3118: truncate_table('HRI_TIME_ENT_QTR');
3119: truncate_table('HRI_TIME_ENT_YEAR');
3120: truncate_table('HRI_TIME_CAL_PERIOD');
3121: truncate_table('HRI_TIME_CAL_QTR');
3122: truncate_table('HRI_TIME_CAL_YEAR');
3123: truncate_table('HRI_TIME_RPT_STRUCT');
3124: truncate_table('HRI_TIME_CAL_RPT_STRUCT');
3117: truncate_table('HRI_TIME_ENT_PERIOD');
3118: truncate_table('HRI_TIME_ENT_QTR');
3119: truncate_table('HRI_TIME_ENT_YEAR');
3120: truncate_table('HRI_TIME_CAL_PERIOD');
3121: truncate_table('HRI_TIME_CAL_QTR');
3122: truncate_table('HRI_TIME_CAL_YEAR');
3123: truncate_table('HRI_TIME_RPT_STRUCT');
3124: truncate_table('HRI_TIME_CAL_RPT_STRUCT');
3125:
3118: truncate_table('HRI_TIME_ENT_QTR');
3119: truncate_table('HRI_TIME_ENT_YEAR');
3120: truncate_table('HRI_TIME_CAL_PERIOD');
3121: truncate_table('HRI_TIME_CAL_QTR');
3122: truncate_table('HRI_TIME_CAL_YEAR');
3123: truncate_table('HRI_TIME_RPT_STRUCT');
3124: truncate_table('HRI_TIME_CAL_RPT_STRUCT');
3125:
3126: if g_debug_flag = 'Y' then
3120: truncate_table('HRI_TIME_CAL_PERIOD');
3121: truncate_table('HRI_TIME_CAL_QTR');
3122: truncate_table('HRI_TIME_CAL_YEAR');
3123: truncate_table('HRI_TIME_RPT_STRUCT');
3124: truncate_table('HRI_TIME_CAL_RPT_STRUCT');
3125:
3126: if g_debug_flag = 'Y' then
3127: HRI_util.put_line(' ');
3128: end if;
3241: FROM ( SELECT DISTINCT glp.period_set_name , glp.period_type
3242: FROM gl_periods glp
3243: MINUS
3244: SELECT DISTINCT cal.period_set_name, cal.period_type
3245: FROM HRI_time_cal_name cal
3246: ) inline_view,
3247: gl_periods gp
3248: WHERE inline_view.period_set_name = gp.period_set_name
3249: and inline_view.period_type = gp.period_type
3259: l_name_row := 0;
3260:
3261: select nvl(max(calendar_id),0)
3262: into l_max_cal_name
3263: from HRI_time_cal_name;
3264:
3265: if g_schema is null then
3266: IF(FND_INSTALLATION.GET_APP_INFO('HRI', l_status, l_industry, g_schema)) THEN
3267: NULL;
3272: -- Populate Calendar Name Level
3273: -- ----------------------
3274: FOR new_cal_rec IN new_cal LOOP
3275:
3276: insert into HRI_time_cal_name
3277: (calendar_id,
3278: period_set_name,
3279: period_type,
3280: name,
3306: end if;
3307:
3308: if l_name_row > 0 then
3309:
3310: gather_table_stats('HRI_TIME_CAL_NAME');
3311:
3312: if g_debug_flag = 'Y' then
3313: HRI_util.put_line('LOAD_CAL_NAME : '||'Gathered statistics for Calendar Name Level');
3314: end if;
3749:
3750: --Bug 3543939. Get the minimum from date and maximum to date
3751: SELECT nvl(least(min(report_date),l_from_date),l_from_date),nvl(greatest(max(report_date),l_to_date),l_to_date)
3752: INTO l_min_date,l_max_date
3753: from HRI_TIME_CAL_RPT_STRUCT;
3754:
3755: --Bug 3543939. The Calendar id's to be picked from the temporary table populated in Load_Cal_Period
3756:
3757: For cur_Fiscal_Calendar IN (
3755: --Bug 3543939. The Calendar id's to be picked from the temporary table populated in Load_Cal_Period
3756:
3757: For cur_Fiscal_Calendar IN (
3758: SELECT distinct calendar_id
3759: FROM HRI_time_cal_gt) LOOP
3760:
3761: --Reset the l_from_date and l_to_date to the parameters passed
3762:
3763: l_from_date:=p_from_date;
3768: BEGIN
3769:
3770: SELECT 'T'
3771: INTO l_full_extraction_flag
3772: FROM HRI_TIME_CAL_RPT_STRUCT
3773: WHERE REPORT_DATE >= p_from_date
3774: AND CALENDAR_ID = cur_Fiscal_Calendar.CALENDAR_ID
3775: AND ROWNUM <= 1;
3776: EXCEPTION
3784: -- Delete all records for the calendar
3785: -- and reset the from and to date for
3786: -- extraction
3787:
3788: DELETE HRI_TIME_CAL_RPT_STRUCT
3789: WHERE CALENDAR_ID = cur_Fiscal_Calendar.CALENDAR_ID;
3790: l_from_date := l_min_date;
3791: l_to_date := l_max_date;
3792:
3797: , report_date
3798: , cal_period_start_date period_start_date
3799: , cal_qtr_start_date qtr_start_date
3800: , cal_year_start_date year_start_date
3801: FROM HRI_time_cal_day_mv
3802: WHERE calendar_id = cur_Fiscal_Calendar.calendar_id
3803: AND report_date BETWEEN l_from_date AND l_to_date) LOOP
3804:
3805: INSERT INTO HRI_TIME_CAL_RPT_STRUCT
3801: FROM HRI_time_cal_day_mv
3802: WHERE calendar_id = cur_Fiscal_Calendar.calendar_id
3803: AND report_date BETWEEN l_from_date AND l_to_date) LOOP
3804:
3805: INSERT INTO HRI_TIME_CAL_RPT_STRUCT
3806: ( calendar_id
3807: , calendar_type
3808: , report_date
3809: , time_id
3854: AND start_date < l_Week_Start_Date;
3855:
3856: WHILE l_Day >= cur_Fiscal_Days.period_start_date LOOP
3857: IF l_Day >= l_Week_Start_Date THEN
3858: INSERT INTO HRI_TIME_CAL_RPT_STRUCT
3859: ( calendar_id
3860: , calendar_type
3861: , report_date
3862: , time_id
3882:
3883: l_Row := l_Row + SQL%ROWCOUNT;
3884:
3885: ELSIF l_Day >= cur_Fiscal_Days.period_start_date AND l_Day < l_Earliest_Week THEN
3886: INSERT INTO HRI_TIME_CAL_RPT_STRUCT
3887: ( calendar_id
3888: , calendar_type
3889: , report_date
3890: , time_id
3916: END LOOP;
3917:
3918: COMMIT;
3919:
3920: INSERT INTO HRI_TIME_CAL_RPT_STRUCT
3921: ( calendar_id
3922: , calendar_type
3923: , report_date
3924: , time_id
3949:
3950: COMMIT;
3951:
3952: ELSE
3953: INSERT INTO HRI_TIME_CAL_RPT_STRUCT
3954: ( calendar_id
3955: , calendar_type
3956: , report_date
3957: , time_id
3973: , SYSDATE
3974: , g_user_id
3975: , g_user_id
3976: , g_login_id
3977: FROM HRI_TIME_CAL_DAY_MV
3978: WHERE calendar_id = cur_Fiscal_Days.calendar_id
3979: AND report_date BETWEEN cur_Fiscal_Days.period_start_date AND l_Day;
3980:
3981: l_Row := l_Row + SQL%ROWCOUNT;
3982:
3983: COMMIT;
3984: END IF;
3985:
3986: INSERT INTO HRI_TIME_CAL_RPT_STRUCT
3987: ( calendar_id
3988: , calendar_type
3989: , report_date
3990: , time_id
4006: , SYSDATE
4007: , g_user_id
4008: , g_user_id
4009: , g_login_id
4010: FROM HRI_TIME_CAL_PERIOD
4011: WHERE start_date >= cur_Fiscal_Days.qtr_start_date
4012: AND start_date <= cur_Fiscal_Days.period_start_date
4013: AND end_date < cur_Fiscal_Days.report_date
4014: AND calendar_id = cur_Fiscal_Days.calendar_id
4024: , SYSDATE
4025: , g_user_id
4026: , g_user_id
4027: , g_login_id
4028: FROM HRI_TIME_CAL_PERIOD
4029: WHERE start_date >= cur_Fiscal_Days.qtr_start_date
4030: AND start_date <= cur_Fiscal_Days.period_start_date
4031: AND end_date >= cur_Fiscal_Days.report_date
4032: AND calendar_id = cur_Fiscal_Days.calendar_id;
4034: l_Row := l_Row + SQL%ROWCOUNT;
4035:
4036: COMMIT;
4037:
4038: INSERT INTO HRI_TIME_CAL_RPT_STRUCT
4039: ( calendar_id
4040: , calendar_type
4041: , report_date
4042: , time_id
4058: , SYSDATE
4059: , g_user_id
4060: , g_user_id
4061: , g_login_id
4062: FROM HRI_TIME_CAL_QTR
4063: WHERE start_date >= cur_Fiscal_Days.year_start_date
4064: AND start_date <= cur_Fiscal_Days.qtr_start_date
4065: AND end_date < cur_Fiscal_Days.report_date
4066: AND calendar_id = cur_Fiscal_Days.calendar_id
4076: , SYSDATE
4077: , g_user_id
4078: , g_user_id
4079: , g_login_id
4080: FROM HRI_TIME_CAL_QTR
4081: WHERE start_date >= cur_Fiscal_Days.year_start_date
4082: AND start_date <= cur_Fiscal_Days.qtr_start_date
4083: AND end_date >= cur_Fiscal_Days.report_date
4084: AND calendar_id = cur_Fiscal_Days.calendar_id;
4086: l_Row := l_Row + SQL%ROWCOUNT;
4087:
4088: COMMIT;
4089:
4090: INSERT INTO HRI_TIME_CAL_RPT_STRUCT
4091: ( calendar_id
4092: , calendar_type
4093: , report_date
4094: , time_id
4110: , SYSDATE
4111: , g_user_id
4112: , g_user_id
4113: , g_login_id
4114: FROM HRI_TIME_CAL_YEAR
4115: WHERE calendar_id = cur_Fiscal_Days.calendar_id
4116: AND cur_Fiscal_Days.report_date BETWEEN start_date AND end_date
4117: UNION ALL
4118: SELECT
4126: , SYSDATE
4127: , g_user_id
4128: , g_user_id
4129: , g_login_id
4130: FROM HRI_TIME_CAL_YEAR
4131: WHERE calendar_id = cur_Fiscal_Days.calendar_id
4132: AND end_date < cur_Fiscal_Days.report_date
4133: AND end_date >= g_global_start_date;
4134:
4145: END IF;
4146:
4147: if l_row > 0 then
4148:
4149: gather_table_stats('HRI_TIME_CAL_RPT_STRUCT');
4150:
4151: if g_debug_flag = 'Y' then
4152: HRI_util.put_line('LOAD_TIME_CAL_RPT_STRUCT :'||'Gathered statistics for Financial Calendars');
4153: end if;
4212: return fnd_date.date_to_displaydt(l_return_date);
4213:
4214: end DEFAULT_LOAD_TO_DATE;
4215:
4216: END HRI_TIME_C;