[Home] [Help]
1: PACKAGE BODY hxt_time_summary AS
2: /* $Header: hxttsum.pkb 120.15.12020000.2 2013/02/12 12:08:42 asrajago ship $ */
3:
4: --Global variables for package
5: --Used for parameters received that are not changed
1407:
1408: BEGIN
1409:
1410: IF g_debug THEN
1411: hr_utility.set_location('hxt_time_summary.insert_non_explodable_hrs',10);
1412: END IF;
1413:
1414: l_hours := p_hours ; -- SIR092
1415:
1479: ,l_sqlerrm);
1480:
1481: IF l_error_status = 0 THEN
1482: IF g_debug then
1483: hr_utility.set_location('hxt_time_summary.insert_non_explodable_hrs',20);
1484: END IF;
1485:
1486: HXT_DML.INSERT_HXT_DET_HOURS_WORKED (
1487: p_rowid => l_rowid,
1531: p_ZIP_CODE => g_ZIP_CODE);
1532:
1533: ELSE /* l_error_status <> 0 */
1534: IF g_debug then
1535: hr_utility.set_location('hxt_time_summary.insert_non_explodable_hrs', 30);
1536: END IF;
1537: -- Insert record in error table.
1538: FND_MESSAGE.SET_NAME('HXT','HXT_39421_GET_RETRO_ERR');
1539: END IF;
1540:
1541: EXCEPTION
1542: WHEN OTHERS THEN
1543: IF g_debug THEN
1544: hr_utility.set_location('hxt_time_summary.insert_non_explodable_hrs', 40);
1545: END IF;
1546: hr_utility.trace(dbms_utility.format_error_backtrace);
1547: -- Insert record in error table.
1548: FND_MESSAGE.SET_NAME('HXT','HXT_39313_OR_ERR_INS_REC');
1582:
1583: BEGIN
1584: g_debug :=hr_utility.debug_enabled;
1585: if g_debug then
1586: hr_utility.set_location('hxt_time_summary.generate_details',10);
1587: end if;
1588:
1589: -- Set the flags for checking which shift diff premium gets paid for a
1590: -- segment chunk.
1684: -- Context of the Preference is TC_W_RULES_EVALUATION
1685: -- Segment is attribute 3 OT_RECURRING_PERIOD
1686: --
1687: if g_debug then
1688: hr_utility.set_location('hxt_time_summary.generate_details',20);
1689: end if;
1690:
1691: -- Changed the following call for Bug 12689562
1692: IF NOT g_rec_period_id.EXISTS(to_char(p_tim_id))
1706:
1707: END IF;
1708:
1709: if g_debug then
1710: hr_utility.set_location('hxt_time_summary.generate_details',30);
1711: hr_utility.trace('otl_recurring_period :'||otl_recurring_period);
1712: end if;
1713: --
1714: -- Now calculate the start day as per the otl recurring period
1713: --
1714: -- Now calculate the start day as per the otl recurring period
1715: --
1716: if g_debug then
1717: hr_utility.set_location('hxt_time_summary.generate_details',40);
1718: end if;
1719: IF otl_recurring_period is NOT NULL THEN
1720: --
1721: -- Get the period start_date, period_type for the recurring_period
1720: --
1721: -- Get the period start_date, period_type for the recurring_period
1722: --
1723: if g_debug then
1724: hr_utility.set_location('hxt_time_summary.generate_details',50);
1725: end if;
1726: SELECT start_date,period_type
1727: INTO l_period_start_date,l_period_type
1728: FROM hxc_recurring_periods
1733: || to_char(l_period_start_date,'DD-MON-YYYY HH24:MI:SS'));
1734: hr_utility.trace('l_period_type :'|| l_period_type);
1735: hr_utility.trace('g_date_worked :'
1736: || to_char(g_date_worked,'DD-MON-YYYY HH24:MI:SS'));
1737: hr_utility.set_location('hxt_time_summary.generate_details',60);
1738: end if;
1739: hxc_period_evaluation.period_start_stop
1740: (g_date_worked
1741: ,l_period_start_date
1743: ,l_period_end
1744: ,l_period_type);
1745:
1746: if g_debug then
1747: hr_utility.set_location('hxt_time_summary.generate_details',70);
1748: hr_utility.trace('l_period_start :'
1749: || to_char(l_period_start,'DD-MON-YYYY HH24:MI:SS'));
1750: hr_utility.trace('l_period_end :'
1751: || to_char(l_period_end,'DD-MON-YYYY HH24:MI:SS'));
1752: end if;
1753: g_start_day_of_week := TO_CHAR(l_period_start,'DAY');
1754: if g_debug then
1755: hr_utility.trace('g_start_day_of_week :'|| g_start_day_of_week);
1756: hr_utility.set_location('hxt_time_summary.generate_details',80);
1757: end if;
1758:
1759: -- Bug 11837942
1760: -- Added this construct here to store the previous id of the timecard
1784:
1785:
1786: ELSE
1787: if g_debug then
1788: hr_utility.set_location('hxt_time_summary.generate_details',90);
1789: hr_utility.trace('p_PERIOD_START_DATE :'
1790: || to_char(p_PERIOD_START_DATE,'DD-MON-YYYY HH24:MI:SS'));
1791: end if;
1792: g_start_day_of_week := TO_CHAR(p_PERIOD_START_DATE,'DAY');
1798:
1799: END IF;
1800:
1801: if g_debug then
1802: hr_utility.set_location('hxt_time_summary.generate_details',100);
1803: end if;
1804:
1805: -- Bug 7359347
1806: -- Execute valid data and capture that.
1808:
1809: IF l_vd_retcode NOT IN (0,1) THEN
1810: -- check for time in/out and hour values
1811: if g_debug then
1812: hr_utility.set_location('hxt_time_summary.generate_details',110);
1813: end if;
1814: RETURN(2);
1815: ELSIF l_vd_retcode = 1 THEN
1816: if g_debug then
1813: end if;
1814: RETURN(2);
1815: ELSIF l_vd_retcode = 1 THEN
1816: if g_debug then
1817: hr_utility.set_location('hxt_time_summary.generate_details',120);
1818: end if;
1819: RETURN(11);
1820: END IF;
1821:
1820: END IF;
1821:
1822: IF g_element_type_id is not null THEN
1823: if g_debug then
1824: hr_utility.set_location('hxt_time_summary.generate_details',130);
1825: end if;
1826: -- Determine if earning is in assigned INCLUDE earning group
1827: g_include_yn := Get_Include(l_location
1828: ,g_egt_id
1830: ,g_date_worked);
1831:
1832: IF g_include_yn = 'E' THEN
1833: if g_debug then
1834: hr_utility.set_location('hxt_time_summary.generate_details',140);
1835: end if;
1836: RETURN 2;
1837:
1838: END IF;
1861: END IF;
1862:
1863: IF l_error_code < 2 THEN
1864: if g_debug then
1865: hr_utility.set_location('hxt_time_summary.generate_details',150);
1866: end if;
1867:
1868: /* Bug: 4489952 changes starts here */
1869:
1868: /* Bug: 4489952 changes starts here */
1869:
1870: IF check_non_explosion_entry (g_element_type_id, g_date_worked) THEN
1871: IF g_debug then
1872: hr_utility.set_location('hxt_time_summary.generate_details',151);
1873: END IF;
1874:
1875: insert_non_explodable_hrs;
1876:
1876:
1877: ELSE /* check_non_explosion_entry = FALSE */
1878:
1879: IF g_debug then
1880: hr_utility.set_location('hxt_time_summary.generate_details',155);
1881: END IF;
1882:
1883: rebuild_details( l_location, l_rebuild_code);
1884: END IF;
1888: END IF;
1889:
1890: IF l_rebuild_code <> 0 THEN
1891: if g_debug then
1892: hr_utility.set_location('hxt_time_summary.generate_details',160);
1893: end if;
1894: RETURN l_rebuild_code;
1895: ELSE
1896: if g_debug then
1893: end if;
1894: RETURN l_rebuild_code;
1895: ELSE
1896: if g_debug then
1897: hr_utility.set_location('hxt_time_summary.generate_details',170);
1898: end if;
1899: -- Bug 8600894
1900: IF NVL(FND_PROFILE.VALUE('HXT_HOLIDAY_EXPLOSION'),'EX') <> 'EX'
1901: THEN
1907: EXCEPTION
1908:
1909: WHEN OTHERS THEN
1910: if g_debug then
1911: hr_utility.set_location('hxt_time_summary.generate_details',180);
1912: end if;
1913: hr_utility.trace(dbms_utility.format_error_backtrace);
1914: FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR');
1915: RETURN call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,l_location, '', sqlerrm);
1927:
1928: BEGIN
1929: g_debug :=hr_utility.debug_enabled;
1930: if g_debug then
1931: hr_utility.set_location('hxt_time_summary.time_in_dates',10);
1932: hr_utility.trace('ln_start :'||ln_start);
1933: hr_utility.trace('ln_stop :'||ln_stop);
1934: hr_utility.trace('ln_carryover :'||ln_carryover);
1935: end if;
1942: IF ln_start < ln_stop OR
1943: (ln_start = 0 AND
1944: ln_stop = 0) THEN
1945: if g_debug then
1946: hr_utility.set_location('hxt_time_summary.time_in_dates',20);
1947: end if;
1948: time_out := to_date((to_char(l_date_worked,'DDMMYYYY')
1949: ||lpad(to_char(ln_stop),4,'0')),'DDMMYYYYHH24MI');
1950: if g_debug then
1952: || to_char(time_out,'DD-MON-YYYY HH24:MI:SS'));
1953: end if;
1954: ELSE --IF ln_start >= ln_stop
1955: if g_debug then
1956: hr_utility.set_location('hxt_time_summary.time_in_dates',30);
1957: end if;
1958: time_out := to_date((to_char(l_date_worked + 1,'DDMMYYYY')
1959: ||lpad(to_char(ln_stop),4,'0')),'DDMMYYYYHH24MI');
1960: if g_debug then
1964: END IF;
1965:
1966: IF ln_carryover is NOT NULL THEN
1967: if g_debug then
1968: hr_utility.set_location('hxt_time_summary.time_in_dates',35);
1969: end if;
1970: IF ln_start < ln_carryover THEN
1971: if g_debug then
1972: hr_utility.set_location('hxt_time_summary.time_in_dates',40);
1968: hr_utility.set_location('hxt_time_summary.time_in_dates',35);
1969: end if;
1970: IF ln_start < ln_carryover THEN
1971: if g_debug then
1972: hr_utility.set_location('hxt_time_summary.time_in_dates',40);
1973: end if;
1974: carryover_time := to_date((to_char(l_date_worked,'DDMMYYYY')
1975: ||lpad(to_char(ln_carryover),4,'0')),'DDMMYYYYHH24MI');
1976:
1979: || to_char(carryover_time,'DD-MON-YYYY HH24:MI:SS'));
1980: end if;
1981: ELSE --IF ln_start >= ln_carryover
1982: if g_debug then
1983: hr_utility.set_location('hxt_time_summary.time_in_dates',50);
1984: end if;
1985: carryover_time := to_date((to_char(l_date_worked + 1,'DDMMYYYY')
1986: ||lpad(to_char(ln_carryover),4,'0')),'DDMMYYYYHH24MI');
1987:
2006:
2007: BEGIN
2008:
2009: if g_debug then
2010: hr_utility.set_location('hxt_time_summary.call_gen_error',10);
2011: end if;
2012: --Checking of the hours worked table for the current id is done so that
2013: --validation is not done on a record being deleted.
2014:
2022: ,g_EFFECTIVE_END_DATE
2023: ,'ERR');
2024:
2025: if g_debug then
2026: hr_utility.set_location('hxt_time_summary.call_gen_error',20);
2027: end if;
2028: RETURN 2;
2029:
2030: EXCEPTION
2030: EXCEPTION
2031:
2032: WHEN NO_DATA_FOUND THEN
2033: if g_debug then
2034: hr_utility.set_location('hxt_time_summary.call_gen_error',30);
2035: end if;
2036: RETURN 0;
2037:
2038: WHEN OTHERS THEN
2036: RETURN 0;
2037:
2038: WHEN OTHERS THEN
2039: if g_debug then
2040: hr_utility.set_location('hxt_time_summary.call_gen_error',40);
2041: end if;
2042: hxt_util.gen_error(g_tim_id
2043: ,g_id
2044: ,NULL
2048: ,g_EFFECTIVE_START_DATE
2049: ,g_EFFECTIVE_END_DATE
2050: ,'ERR');
2051: if g_debug then
2052: hr_utility.set_location('hxt_time_summary.call_gen_error',50);
2053: end if;
2054: hr_utility.trace(dbms_utility.format_error_backtrace);
2055: RETURN 2;
2056: END;
2069:
2070: BEGIN
2071:
2072: if g_debug then
2073: hr_utility.set_location('hxt_time_summary.call_gen_error',10);
2074: end if;
2075: --Checking of the hours worked table for the current id is done so that
2076: --validation is not done on a record being deleted.
2077:
2098: p_time_attribute_ovn=> NULL
2099: );
2100: if g_debug then
2101: hr_utility.trace('Adding to g_otm_messages'||p_msg_name);
2102: hr_utility.set_location('hxt_time_summary.call_gen_error',20);
2103: end if;
2104: RETURN 2;
2105:
2106: EXCEPTION
2106: EXCEPTION
2107:
2108: WHEN NO_DATA_FOUND THEN
2109: if g_debug then
2110: hr_utility.set_location('hxt_time_summary.call_gen_error',30);
2111: end if;
2112: RETURN 0;
2113:
2114: WHEN OTHERS THEN
2112: RETURN 0;
2113:
2114: WHEN OTHERS THEN
2115: if g_debug then
2116: hr_utility.set_location('hxt_time_summary.call_gen_error',40);
2117: end if;
2118: hxt_util.gen_error(g_tim_id
2119: ,g_id
2120: ,NULL
2138: p_time_attribute_ovn=> NULL
2139: );
2140:
2141: if g_debug then
2142: hr_utility.set_location('hxt_time_summary.call_gen_error',50);
2143: end if;
2144: hr_utility.trace(dbms_utility.format_error_backtrace);
2145: RETURN 2;
2146: END;
2166: error_code := hxt_tim_col_util.get_session_date(g_sum_session_date);
2167: END IF;
2168:
2169: if g_debug then
2170: hr_utility.set_location('hxt_time_summary.valid_data',10);
2171: end if;
2172: IF g_time_in IS NULL AND g_time_out IS NOT NULL THEN
2173:
2174: if g_debug then
2171: end if;
2172: IF g_time_in IS NULL AND g_time_out IS NOT NULL THEN
2173:
2174: if g_debug then
2175: hr_utility.set_location('hxt_time_summary.valid_data',20);
2176: end if;
2177: FND_MESSAGE.SET_NAME('HXT','HXT_39327_TIME_IN_OT_REQ');
2178: RETURN call_hxthxc_gen_error('HXT','HXT_39327_TIME_IN_OT_REQ',NULL,location, '');
2179:
2181:
2182: IF g_time_out IS NULL AND g_time_in IS NOT NULL THEN
2183:
2184: if g_debug then
2185: hr_utility.set_location('hxt_time_summary.valid_data',30);
2186: end if;
2187: FND_MESSAGE.SET_NAME('HXT','HXT_39327_TIME_IN_OT_REQ');
2188: RETURN call_hxthxc_gen_error('HXT','HXT_39327_TIME_IN_OT_REQ',NULL,location, '');
2189:
2191:
2192: IF g_time_in IS NULL AND g_hours IS NULL THEN
2193:
2194: if g_debug then
2195: hr_utility.set_location('hxt_time_summary.valid_data',40);
2196: end if;
2197: FND_MESSAGE.SET_NAME('HXT','HXT_39328_TIME_OR_TOT_HRS_REQ');
2198: RETURN call_hxthxc_gen_error('HXT','HXT_39328_TIME_OR_TOT_HRS_REQ',NULL,location, '');
2199:
2201:
2202: IF g_element_type_id IS NOT NULL THEN
2203:
2204: if g_debug then
2205: hr_utility.set_location('hxt_time_summary.valid_data',50);
2206: end if;
2207: l_cat := hxt_util.element_cat(g_element_type_id
2208: ,g_date_worked);
2209:
2213:
2214: BEGIN
2215: g_debug :=hr_utility.debug_enabled;
2216: if g_debug then
2217: hr_utility.set_location('hxt_time_summary.valid_data',60);
2218: end if;
2219:
2220: /* Bug: 4489952 changes starts here */
2221:
2281:
2282: EXCEPTION
2283: WHEN NO_DATA_FOUND THEN
2284: if g_debug then
2285: hr_utility.set_location('hxt_time_summary.valid_data',90);
2286: end if;
2287: NULL;
2288: END;
2289:
2288: END;
2289:
2290: IF error_code = 1 THEN
2291: if g_debug then
2292: hr_utility.set_location('hxt_time_summary.valid_data',100);
2293: end if;
2294: FND_MESSAGE.SET_NAME('HXT','HXT_39329_INC_TIM_HR_ENTRIES');
2295:
2296: END IF;
2300: EXCEPTION
2301:
2302: WHEN OTHERS THEN
2303: if g_debug then
2304: hr_utility.set_location('hxt_time_summary.valid_data',110);
2305: end if;
2306: hr_utility.trace(dbms_utility.format_error_backtrace);
2307: FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR');
2308: RETURN call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,location, '', sqlerrm);
2323:
2324: BEGIN
2325:
2326: if g_debug then
2327: hr_utility.set_location('hxt_time_summary.Delete_Details',10);
2328: end if;
2329: p_error_code := 0;
2330:
2331: --Add local code to location variable
2332: l_location := p_location||':DD';
2333:
2334: IF nvl(g_DT_UPDATE_MODE, 'CORRECTION') = 'CORRECTION' THEN
2335: if g_debug then
2336: hr_utility.set_location('hxt_time_summary.Delete_Details',20);
2337: end if;
2338: DELETE from hxt_det_hours_worked_f
2339: WHERE parent_id = g_id;
2340:
2341: DELETE from hxt_errors_f where --SPR C153
2342: hrw_id = g_id; --SPR C153
2343: ELSE
2344: if g_debug then
2345: hr_utility.set_location('hxt_time_summary.Delete_Details',30);
2346: end if;
2347: -- Delete details for this entry and all others that follow
2348: -- on this day for this person that are non-absence or are in
2349: -- the person's include group
2379: EXCEPTION
2380:
2381: WHEN OTHERS THEN
2382: if g_debug then
2383: hr_utility.set_location('hxt_time_summary.Delete_Details',40);
2384: end if;
2385: hr_utility.trace(dbms_utility.format_error_backtrace);
2386: -- Write to error table - do not generate details
2387: FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR'); -- HXT11
2395:
2396: BEGIN
2397:
2398: if g_debug then
2399: hr_utility.set_location('hxt_time_summary.shift_adjust_times',10);
2400: end if;
2401: p_shift_adjusted_time_in := g_TIME_IN;
2402: p_shift_adjusted_time_out := g_TIME_OUT;
2403:
2402: p_shift_adjusted_time_out := g_TIME_OUT;
2403:
2404: IF (g_EARLY_START IS NOT NULL AND g_STANDARD_START IS NOT NULL) THEN
2405: if g_debug then
2406: hr_utility.set_location('hxt_time_summary.shift_adjust_times',20);
2407: end if;
2408: IF g_EARLY_START > g_STANDARD_START THEN -- spans midnight
2409: if g_debug then
2410: hr_utility.set_location('hxt_time_summary.shift_adjust_times',30);
2406: hr_utility.set_location('hxt_time_summary.shift_adjust_times',20);
2407: end if;
2408: IF g_EARLY_START > g_STANDARD_START THEN -- spans midnight
2409: if g_debug then
2410: hr_utility.set_location('hxt_time_summary.shift_adjust_times',30);
2411: end if;
2412: IF to_number(to_char(g_TIME_IN, 'HH24MI')) < g_STANDARD_START THEN
2413: if g_debug then
2414: hr_utility.set_location('hxt_time_summary.shift_adjust_times',40);
2410: hr_utility.set_location('hxt_time_summary.shift_adjust_times',30);
2411: end if;
2412: IF to_number(to_char(g_TIME_IN, 'HH24MI')) < g_STANDARD_START THEN
2413: if g_debug then
2414: hr_utility.set_location('hxt_time_summary.shift_adjust_times',40);
2415: end if;
2416: p_shift_adjusted_time_in := g_TIME_IN + ((hxt_util.time_to_hours(g_STANDARD_START)
2417: - hxt_util.time_to_hours(to_number(to_char(g_TIME_IN, 'HH24MI')))) / 24);
2418: ELSIF to_number(to_char(g_TIME_IN, 'HH24MI')) > g_EARLY_START THEN
2416: p_shift_adjusted_time_in := g_TIME_IN + ((hxt_util.time_to_hours(g_STANDARD_START)
2417: - hxt_util.time_to_hours(to_number(to_char(g_TIME_IN, 'HH24MI')))) / 24);
2418: ELSIF to_number(to_char(g_TIME_IN, 'HH24MI')) > g_EARLY_START THEN
2419: if g_debug then
2420: hr_utility.set_location('hxt_time_summary.shift_adjust_times',50);
2421: end if;
2422: p_shift_adjusted_time_in := g_TIME_IN + ((hxt_util.time_to_hours(g_STANDARD_START)
2423: + (hxt_util.time_to_hours(2400) -
2424: hxt_util.time_to_hours(to_number(to_char(g_TIME_IN, 'HH24MI'))))) / 24);
2424: hxt_util.time_to_hours(to_number(to_char(g_TIME_IN, 'HH24MI'))))) / 24);
2425: END IF;
2426: ELSE -- no midnight span
2427: if g_debug then
2428: hr_utility.set_location('hxt_time_summary.shift_adjust_times',60);
2429: end if;
2430: IF to_number(to_char(g_TIME_IN, 'HH24MI')) BETWEEN g_EARLY_START
2431: AND g_STANDARD_START THEN
2432: if g_debug then
2429: end if;
2430: IF to_number(to_char(g_TIME_IN, 'HH24MI')) BETWEEN g_EARLY_START
2431: AND g_STANDARD_START THEN
2432: if g_debug then
2433: hr_utility.set_location('hxt_time_summary.shift_adjust_times',70);
2434: end if;
2435: p_shift_adjusted_time_in := g_TIME_IN + ((hxt_util.time_to_hours(g_STANDARD_START)
2436: - hxt_util.time_to_hours(to_number(to_char(g_TIME_IN, 'HH24MI')))) / 24);
2437: END IF;
2439: END IF;
2440:
2441: IF (g_LATE_STOP IS NOT NULL AND g_STANDARD_STOP IS NOT NULL) THEN
2442: if g_debug then
2443: hr_utility.set_location('hxt_time_summary.shift_adjust_times',80);
2444: end if;
2445: IF g_LATE_STOP < g_STANDARD_STOP THEN -- spans midnight
2446: if g_debug then
2447: hr_utility.set_location('hxt_time_summary.shift_adjust_times',90);
2443: hr_utility.set_location('hxt_time_summary.shift_adjust_times',80);
2444: end if;
2445: IF g_LATE_STOP < g_STANDARD_STOP THEN -- spans midnight
2446: if g_debug then
2447: hr_utility.set_location('hxt_time_summary.shift_adjust_times',90);
2448: end if;
2449: IF to_number(to_char(g_TIME_OUT, 'HH24MI')) > g_STANDARD_STOP THEN
2450: if g_debug then
2451: hr_utility.set_location('hxt_time_summary.shift_adjust_times',100);
2447: hr_utility.set_location('hxt_time_summary.shift_adjust_times',90);
2448: end if;
2449: IF to_number(to_char(g_TIME_OUT, 'HH24MI')) > g_STANDARD_STOP THEN
2450: if g_debug then
2451: hr_utility.set_location('hxt_time_summary.shift_adjust_times',100);
2452: end if;
2453: p_shift_adjusted_time_out := g_TIME_OUT -
2454: (( hxt_util.time_to_hours(to_number(to_char(g_TIME_OUT, 'HH24MI')))
2455: - (hxt_util.time_to_hours(g_STANDARD_STOP))) / 24);
2454: (( hxt_util.time_to_hours(to_number(to_char(g_TIME_OUT, 'HH24MI')))
2455: - (hxt_util.time_to_hours(g_STANDARD_STOP))) / 24);
2456: ELSIF to_number(to_char(g_TIME_OUT, 'HH24MI')) < g_LATE_STOP THEN
2457: if g_debug then
2458: hr_utility.set_location('hxt_time_summary.shift_adjust_times',110);
2459: end if;
2460: p_shift_adjusted_time_out := g_TIME_OUT -
2461: (( hxt_util.time_to_hours(to_number(to_char(g_TIME_OUT, 'HH24MI')))
2462: + (hxt_util.time_to_hours(2400) - (hxt_util.time_to_hours(g_STANDARD_STOP)))) / 24);
2462: + (hxt_util.time_to_hours(2400) - (hxt_util.time_to_hours(g_STANDARD_STOP)))) / 24);
2463: END IF;
2464: ELSE -- no midnight span
2465: if g_debug then
2466: hr_utility.set_location('hxt_time_summary.shift_adjust_times',120);
2467: end if;
2468: IF to_number(to_char(g_TIME_OUT, 'HH24MI')) BETWEEN g_STANDARD_STOP
2469: AND g_LATE_STOP THEN
2470: if g_debug then
2467: end if;
2468: IF to_number(to_char(g_TIME_OUT, 'HH24MI')) BETWEEN g_STANDARD_STOP
2469: AND g_LATE_STOP THEN
2470: if g_debug then
2471: hr_utility.set_location('hxt_time_summary.shift_adjust_times',130);
2472: end if;
2473: p_shift_adjusted_time_out := g_TIME_OUT -
2474: (( hxt_util.time_to_hours(to_number(to_char(g_TIME_OUT, 'HH24MI')))
2475: - (hxt_util.time_to_hours(g_STANDARD_STOP))) / 24);
2497:
2498: BEGIN
2499:
2500: if g_debug then
2501: hr_utility.set_location('hxt_time_summary.Rebuild_Details',10);
2502: end if;
2503: --Add local code to location variable
2504: l_location := p_location||':RB';
2505:
2512: ,l_hdy_id
2513: ,l_hours
2514: ,l_retcode);
2515: if g_debug then
2516: hr_utility.set_location('hxt_time_summary.Rebuild_Details',20);
2517: end if;
2518: IF l_retcode = 1 THEN
2519: if g_debug then
2520: hr_utility.set_location('hxt_time_summary.Rebuild_Details',30);
2516: hr_utility.set_location('hxt_time_summary.Rebuild_Details',20);
2517: end if;
2518: IF l_retcode = 1 THEN
2519: if g_debug then
2520: hr_utility.set_location('hxt_time_summary.Rebuild_Details',30);
2521: end if;
2522: g_hol_yn := 'Y';
2523: ELSE
2524: if g_debug then
2521: end if;
2522: g_hol_yn := 'Y';
2523: ELSE
2524: if g_debug then
2525: hr_utility.set_location('hxt_time_summary.Rebuild_Details',40);
2526: end if;
2527: g_hol_yn := 'N';
2528: END IF;
2529:
2530: l_error_code := Gen_Details(l_location, g_time_in, g_time_out);
2531:
2532: IF l_error_code <> 0 THEN
2533: if g_debug then
2534: hr_utility.set_location('hxt_time_summary.Rebuild_Details',50);
2535: end if;
2536: p_error_code := l_error_code;
2537: ELSE
2538: if g_debug then
2535: end if;
2536: p_error_code := l_error_code;
2537: ELSE
2538: if g_debug then
2539: hr_utility.set_location('hxt_time_summary.Rebuild_Details',60);
2540: end if;
2541: p_error_code := change_warning_flag;
2542: END IF;
2543:
2544: EXCEPTION
2545:
2546: WHEN OTHERS THEN
2547: if g_debug then
2548: hr_utility.set_location('hxt_time_summary.Rebuild_Details',70);
2549: end if;
2550: hr_utility.trace(dbms_utility.format_error_backtrace);
2551: FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR'); -- HXT11
2552: p_error_code := call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,l_location, '', sqlerrm); -- HXT11
2688:
2689: BEGIN
2690: g_debug :=hr_utility.debug_enabled;
2691: if g_debug then
2692: hr_utility.set_location('hxt_time_summary.time_in_dates',10);
2693: hr_utility.trace('ln_start :'||ln_start);
2694: hr_utility.trace('ln_stop :'||ln_stop);
2695: hr_utility.trace('ln_carryover :'||ln_carryover);
2696: end if;
2703: IF ln_start < ln_stop OR
2704: (ln_start = 0 AND
2705: ln_stop = 0) THEN
2706: if g_debug then
2707: hr_utility.set_location('hxt_time_summary.time_in_dates',20);
2708: end if;
2709: time_out := to_date((to_char(l_date_worked,'DDMMYYYY')
2710: ||lpad(to_char(ln_stop),4,'0')),'DDMMYYYYHH24MI');
2711: if g_debug then
2713: || to_char(time_out,'DD-MON-YYYY HH24:MI:SS'));
2714: end if;
2715: ELSE --IF ln_start >= ln_stop
2716: if g_debug then
2717: hr_utility.set_location('hxt_time_summary.time_in_dates',30);
2718: end if;
2719: time_out := to_date((to_char(l_date_worked + 1,'DDMMYYYY')
2720: ||lpad(to_char(ln_stop),4,'0')),'DDMMYYYYHH24MI');
2721: if g_debug then
2725: END IF;
2726:
2727: IF ln_carryover is NOT NULL THEN
2728: if g_debug then
2729: hr_utility.set_location('hxt_time_summary.time_in_dates',35);
2730: end if;
2731: IF ln_start < ln_carryover THEN
2732: if g_debug then
2733: hr_utility.set_location('hxt_time_summary.time_in_dates',40);
2729: hr_utility.set_location('hxt_time_summary.time_in_dates',35);
2730: end if;
2731: IF ln_start < ln_carryover THEN
2732: if g_debug then
2733: hr_utility.set_location('hxt_time_summary.time_in_dates',40);
2734: end if;
2735: carryover_time := to_date((to_char(l_date_worked,'DDMMYYYY')
2736: ||lpad(to_char(ln_carryover),4,'0')),'DDMMYYYYHH24MI');
2737:
2740: || to_char(carryover_time,'DD-MON-YYYY HH24:MI:SS'));
2741: end if;
2742: ELSE --IF ln_start >= ln_carryover
2743: if g_debug then
2744: hr_utility.set_location('hxt_time_summary.time_in_dates',50);
2745: end if;
2746: carryover_time := to_date((to_char(l_date_worked + 1,'DDMMYYYY')
2747: ||lpad(to_char(ln_carryover),4,'0')),'DDMMYYYYHH24MI');
2748:
2765:
2766: BEGIN
2767:
2768: if g_debug then
2769: hr_utility.set_location('hxt_time_summary.sort',10);
2770: end if;
2771: if segment_chunks.count > 0 then
2772: -- We do not need to copy each member of the array one by one.
2773: /*
2773: /*
2774: For i in segment_chunks.first .. segment_chunks.LAST
2775: Loop
2776: if g_debug then
2777: hr_utility.set_location('hxt_time_summary.sort',20);
2778: end if;
2779: sorted_chunks(i):= segment_chunks(i);
2780: End loop;
2781: */
2779: sorted_chunks(i):= segment_chunks(i);
2780: End loop;
2781: */
2782: if g_debug then
2783: hr_utility.set_location('hxt_time_summary.sort',20);
2784: end if;
2785: sorted_chunks := segment_chunks;
2786: end if;
2787: /*
2790: hr_utility.trace('FYI');
2791: end if;
2792: if sorted_chunks.count <> 0 then
2793: if g_debug then
2794: hr_utility.set_location('hxt_time_summary.sort',30);
2795: end if;
2796: for l_cnt in sorted_chunks.first .. sorted_chunks.last loop
2797: if g_debug then
2798: hr_utility.trace('sorted_chunks is:'||to_char(sorted_chunks(l_cnt),'DD-MON-YYYY HH24:MI:SS'));
2807:
2808: if g_debug then
2809: hr_utility.trace('FYI');
2810: if sorted_chunks.count <> 0 then
2811: hr_utility.set_location('hxt_time_summary.sort',30);
2812: for l_cnt in sorted_chunks.first .. sorted_chunks.last loop
2813: hr_utility.trace('sorted_chunks is:'||to_char(sorted_chunks(l_cnt),'DD-MON-YYYY HH24:MI:SS'));
2814: end loop;
2815: end if;
2820:
2821: For i in sorted_chunks.First+1 .. sorted_chunks.LAST
2822: Loop
2823: if g_debug then
2824: hr_utility.set_location('hxt_time_summary.sort',40);
2825: end if;
2826: v_temp:= sorted_chunks(i);
2827: if g_debug then
2828: hr_utility.trace('v_temp :'||to_char(v_temp,'DD-MON-YYYY HH24:MI:SS'));
2828: hr_utility.trace('v_temp :'||to_char(v_temp,'DD-MON-YYYY HH24:MI:SS'));
2829: end if;
2830: <
2831: if g_debug then
2832: hr_utility.set_location('hxt_time_summary.sort',50);
2833: end if;
2834: For j in REVERSE sorted_chunks.First .. (i-1)
2835: Loop
2836: if g_debug then
2833: end if;
2834: For j in REVERSE sorted_chunks.First .. (i-1)
2835: Loop
2836: if g_debug then
2837: hr_utility.set_location('hxt_time_summary.sort',60);
2838: hr_utility.trace('sorted_chunks(j) :'||to_char(sorted_chunks(j),'DD-MON-YYYY HH24:MI:SS'));
2839: end if;
2840: If sorted_chunks(j) >= v_temp then
2841: if g_debug then
2838: hr_utility.trace('sorted_chunks(j) :'||to_char(sorted_chunks(j),'DD-MON-YYYY HH24:MI:SS'));
2839: end if;
2840: If sorted_chunks(j) >= v_temp then
2841: if g_debug then
2842: hr_utility.set_location('hxt_time_summary.sort',70);
2843: end if;
2844: sorted_chunks(j+1) := sorted_chunks(j);
2845: sorted_chunks(j) := v_temp;
2846: end if;
2844: sorted_chunks(j+1) := sorted_chunks(j);
2845: sorted_chunks(j) := v_temp;
2846: end if;
2847: if g_debug then
2848: hr_utility.set_location('hxt_time_summary.sort',80);
2849: end if;
2850: end loop inner_loop;
2851: if g_debug then
2852: hr_utility.set_location('hxt_time_summary.sort',90);
2848: hr_utility.set_location('hxt_time_summary.sort',80);
2849: end if;
2850: end loop inner_loop;
2851: if g_debug then
2852: hr_utility.set_location('hxt_time_summary.sort',90);
2853: end if;
2854: end loop;
2855: IF p_order ='ASC' then
2856: if g_debug then
2853: end if;
2854: end loop;
2855: IF p_order ='ASC' then
2856: if g_debug then
2857: hr_utility.set_location('hxt_time_summary.sort',100);
2858: end if;
2859: RETURN sorted_chunks;
2860: END IF;
2861: if g_debug then
2858: end if;
2859: RETURN sorted_chunks;
2860: END IF;
2861: if g_debug then
2862: hr_utility.set_location('hxt_time_summary.sort',110);
2863: end if;
2864: END;
2865:
2866: FUNCTION segment_earning (segment_start in date
2926:
2927:
2928: if g_debug then
2929:
2930: hr_utility.set_location('hxt_time_summary.check_eligibility',10);
2931: end if;
2932:
2933: IF (
2934: (lv_time_in <= lv_segment_start
2954: -- lv_time_in lv_segment_start | lv_segment_stop lv_time_out
2955: )
2956: THEN
2957: if g_debug then
2958: hr_utility.set_location('hxt_time_summary.check_eligibility',20);
2959: end if;
2960: RETURN TRUE;
2961: ELSE
2962: if g_debug then
2959: end if;
2960: RETURN TRUE;
2961: ELSE
2962: if g_debug then
2963: hr_utility.set_location('hxt_time_summary.check_eligibility',30);
2964: end if;
2965: RETURN FALSE;
2966: END IF;
2967: if g_debug then
2964: end if;
2965: RETURN FALSE;
2966: END IF;
2967: if g_debug then
2968: hr_utility.set_location('hxt_time_summary.check_eligibility',40);
2969: end if;
2970: END;
2971:
2972: ----------------------------segment_earning main module-------------------------
2973:
2974: BEGIN
2975:
2976: if g_debug then
2977: l_proc := 'hxt_time_summary.segment_earning';
2978: hr_utility.set_location(l_proc,10);
2979:
2980: -- hr_utility.trace('worked_time_in :'||worked_time_in);
2981: -- hr_utility.trace('worked_time_out :'||worked_time_out);
3408:
3409: BEGIN
3410:
3411: if g_debug then
3412: hr_utility.set_location('hxt_time_summary.segment_start_in_rule',10);
3413: end if;
3414: OPEN sd_rules;
3415: FETCH sd_rules INTO p_rule_earning_type, p_carryover;
3416: if g_debug then
3418: hr_utility.trace('p_carryover :'||p_carryover);
3419: end if;
3420: IF sd_rules%NOTFOUND THEN
3421: if g_debug then
3422: hr_utility.set_location('hxt_time_summary.segment_start_in_rule',20);
3423: end if;
3424: CLOSE sd_rules;
3425: RETURN FALSE;
3426: END IF;
3424: CLOSE sd_rules;
3425: RETURN FALSE;
3426: END IF;
3427: if g_debug then
3428: hr_utility.set_location('hxt_time_summary.segment_start_in_rule',30);
3429: end if;
3430: CLOSE sd_rules;
3431: RETURN TRUE;
3432:
3459:
3460: BEGIN
3461:
3462: if g_debug then
3463: hr_utility.set_location('hxt_time_summary.rule_start_in_segment',10);
3464: end if;
3465: OPEN sd_rules;
3466: FETCH sd_rules INTO p_rule_start;
3467: if g_debug then
3468: hr_utility.trace('p_rule_start :'||p_rule_start);
3469: end if;
3470: IF sd_rules%NOTFOUND THEN
3471: if g_debug then
3472: hr_utility.set_location('hxt_time_summary.rule_start_in_segment',20);
3473: end if;
3474: CLOSE sd_rules;
3475: RETURN FALSE;
3476: END IF;
3495:
3496: BEGIN
3497:
3498: if g_debug then
3499: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',10);
3500: end if;
3501: p_segment_stop_time := NULL;
3502: if g_debug then
3503: hr_utility.trace('sd_rule_carryover :'||sd_rule_carryover);
3507: hr_utility.trace('segment_start_time :'||segment_start_time);
3508: end if;
3509: IF sd_rule_carryover >= time_out_hours THEN
3510: if g_debug then
3511: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',20);
3512: end if;
3513: IF ((time_out_hours > time_in_hours) OR (time_in_hours > sd_rule_carryover)) THEN
3514: if g_debug then
3515: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',30);
3511: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',20);
3512: end if;
3513: IF ((time_out_hours > time_in_hours) OR (time_in_hours > sd_rule_carryover)) THEN
3514: if g_debug then
3515: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',30);
3516: end if;
3517: -- | S ST C S | ST C
3518: p_segment_stop_time := p_shift_adjusted_time_out;
3519: if g_debug then
3520: hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
3521: end if;
3522: ELSIF time_in_hours >= time_out_hours THEN
3523: if g_debug then
3524: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',40);
3525: end if;
3526: -- S C | ST
3527: p_segment_stop_time := segment_start_time +
3528: ((hxt_util.time_to_hours(sd_rule_carryover) -
3533: END IF;
3534: ELSE -- carryover < time_out_hours
3535: IF time_in_hours < sd_rule_carryover THEN
3536: if g_debug then
3537: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',50);
3538: end if;
3539: -- S C ST |
3540: p_segment_stop_time := segment_start_time +
3541: ((hxt_util.time_to_hours(sd_rule_carryover) -
3545: end if;
3546:
3547: ELSIF ((time_in_hours > sd_rule_carryover) AND (time_in_hours > time_out_hours)) THEN
3548: if g_debug then
3549: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',60);
3550: end if;
3551: -- S | C ST
3552: p_segment_stop_time := segment_start_time +
3553: ((hxt_util.time_to_hours(sd_rule_carryover) +
3556: hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
3557: end if;
3558: ELSIF ((time_in_hours > sd_rule_carryover) AND (time_in_hours < time_out_hours)) THEN
3559: if g_debug then
3560: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',70);
3561: end if;
3562: -- S ST | C
3563: p_segment_stop_time := p_shift_adjusted_time_out;
3564: if g_debug then
3567: ELSIF ((time_in_hours > sd_rule_carryover)
3568: AND (time_in_hours = time_out_hours)) THEN --SIR523
3569: -- S ST | C
3570: if g_debug then
3571: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',80);
3572: end if;
3573: p_segment_stop_time := p_shift_adjusted_time_out;
3574: if g_debug then
3575: hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
3575: hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
3576: end if;
3577: ELSIF time_in_hours = sd_rule_carryover THEN
3578: if g_debug then
3579: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',90);
3580: end if;
3581: p_segment_stop_time := p_shift_adjusted_time_out;
3582: if g_debug then
3583: hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
3591: end if;
3592:
3593: IF p_segment_stop_time IS NULL THEN
3594: if g_debug then
3595: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',100);
3596: end if;
3597: FND_MESSAGE.SET_NAME('HXT','HXT_39314_SEG_STOP_TIME_NF'); -- HXT11
3598: RETURN call_hxthxc_gen_error('HXT','HXT_39314_SEG_STOP_TIME_NF',NULL,location, ''); -- HXT11
3599: --2278400 RETURN call_gen_error(location, ''); -- HXT11
3615:
3616: BEGIN
3617:
3618: if g_debug then
3619: hr_utility.set_location('hxt_time_summary.set_stop_at_rule',10);
3620: end if;
3621: l_stop_time := NULL;
3622:
3623: if g_debug then
3625: hr_utility.trace('time_in_hours :'||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3626: end if;
3627: IF sd_rule_start > time_in_hours THEN
3628: if g_debug then
3629: hr_utility.set_location('hxt_time_summary.set_stop_at_rule',20);
3630: hr_utility.trace('segment_start_time:'||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
3631: end if;
3632: l_stop_time := segment_start_time + ((hxt_util.time_to_hours(sd_rule_start)
3633: - hxt_util.time_to_hours(time_in_hours)) / 24);
3636: hr_utility.trace('l_stop_time :'||to_char(l_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3637: end if;
3638: ELSE
3639: if g_debug then
3640: hr_utility.set_location('hxt_time_summary.set_stop_at_rule',30);
3641: hr_utility.trace('segment_start_time:'||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
3642: end if;
3643: l_stop_time := segment_start_time + ((hxt_util.time_to_hours(sd_rule_start) +
3644: (hxt_util.time_to_hours(2400) - hxt_util.time_to_hours(time_in_hours))) / 24);
3659: hr_utility.trace('p_segment_stop_time :'||to_char(p_segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3660: end if;
3661: IF l_stop_time IS NULL THEN
3662: if g_debug then
3663: hr_utility.set_location('hxt_time_summary.set_stop_at_rule',40);
3664: end if;
3665: FND_MESSAGE.SET_NAME('HXT','HXT_39314_SEG_STOP_TIME_NF'); -- HXT11
3666: RETURN call_hxthxc_gen_error('HXT','HXT_39314_SEG_STOP_TIME_NF',NULL,location, ''); -- HXT11
3667: --2278400 RETURN call_gen_error(location, ''); -- HXT11
3673: ----------------------------GEN DETAILS MAIN MODULE----------------------------
3674: BEGIN
3675: g_debug :=hr_utility.debug_enabled;
3676: if g_debug then
3677: hr_utility.set_location('hxt_time_summary.gen_details',10);
3678: end if;
3679: IF segment_chunks.count > 0 THEN
3680: if g_debug then
3681: hr_utility.set_location('hxt_time_summary.gen_details',11);
3677: hr_utility.set_location('hxt_time_summary.gen_details',10);
3678: end if;
3679: IF segment_chunks.count > 0 THEN
3680: if g_debug then
3681: hr_utility.set_location('hxt_time_summary.gen_details',11);
3682: end if;
3683: -- Bug 10123886
3684: -- No need to NULL out every member before we delete the array
3685: /*
3685: /*
3686:
3687: FOR l in segment_chunks.first .. segment_chunks.last LOOP
3688: if g_debug then
3689: hr_utility.set_location('hxt_time_summary.gen_details',12);
3690: end if;
3691: segment_chunks(l) := null;
3692: END LOOP;
3693: */
3692: END LOOP;
3693: */
3694: segment_chunks.delete;
3695: if g_debug then
3696: hr_utility.set_location('hxt_time_summary.gen_details',13);
3697: end if;
3698: END IF;
3699:
3700: IF sorted_chunks.count > 0 THEN
3698: END IF;
3699:
3700: IF sorted_chunks.count > 0 THEN
3701: if g_debug then
3702: hr_utility.set_location('hxt_time_summary.gen_details',14);
3703: end if;
3704: -- Bug 10123886
3705: -- No need to NULL out every member before we delete the array
3706: /*
3705: -- No need to NULL out every member before we delete the array
3706: /*
3707: FOR l in sorted_chunks.first .. sorted_chunks.last LOOP
3708: if g_debug then
3709: hr_utility.set_location('hxt_time_summary.gen_details',15);
3710: end if;
3711: sorted_chunks(l) := null;
3712: END LOOP;
3713: */
3713: */
3714:
3715: sorted_chunks.delete;
3716: if g_debug then
3717: hr_utility.set_location('hxt_time_summary.gen_details',16);
3718: end if;
3719: END IF;
3720:
3721: -- Takes the incoming time in and out from the summary record and splits into
3739: AND g_CALL_ADJUST_ABS = 'Y'
3740: -- Bug 10123886
3741: AND hxt_time_detail.g_holiday_sdp_paid = FALSE ) THEN
3742: if g_debug then
3743: hr_utility.set_location('hxt_time_summary.gen_details',20);
3744: end if;
3745: -- If there is no shift diff policy or only hours entered or override hours
3746: -- type entered
3747: segment_start_time := p_shift_adjusted_time_in;
3755: ,NULL);
3756: ELSE
3757:
3758: if g_debug then
3759: hr_utility.set_location('hxt_time_summary.gen_details',21);
3760: end if;
3761:
3762: segment_start_time := p_shift_adjusted_time_in;
3763: segment_stop_time := p_shift_adjusted_time_out;
3775: ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
3776: hr_utility.trace('segment_stop_time :'
3777: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3778:
3779: hr_utility.set_location('hxt_time_summary.gen_details',22);
3780: end if;
3781: -- Insert these rows for hours worked start stop into the
3782: -- pl sql table segment_chunks.
3783: -- These start and stop times are entered into a single column
3822: populate_plsql_table(wp_start_time);
3823: populate_plsql_table(wp_stop_time);
3824:
3825: if g_debug then
3826: hr_utility.set_location('hxt_time_summary.gen_details',23);
3827: end if;
3828: wplan_date_worked := TRUNC(wplan_date_worked + 1, 'DD');
3829: END LOOP;
3830:
3828: wplan_date_worked := TRUNC(wplan_date_worked + 1, 'DD');
3829: END LOOP;
3830:
3831: if g_debug then
3832: hr_utility.set_location('hxt_time_summary.gen_details',23.4);
3833: end if;
3834: open get_sd_rules;
3835: LOOP
3836: -- fetch get_sd_rules into lv_sd_start,lv_sd_stop;
3847: sd_date_worked := TRUNC(p_shift_adjusted_time_in - 1, 'DD');
3848:
3849: FOR i in 1 .. 3 LOOP
3850: if g_debug then
3851: hr_utility.set_location('hxt_time_summary.gen_details',23.5);
3852: hr_utility.trace('sd_date_worked :'
3853: || to_char(sd_date_worked,'DD-MON-YYYY HH24:MI:SS'));
3854: end if;
3855: time_in_dates(ln_sd_start
3869: populate_plsql_table(sd_start_time);
3870: populate_plsql_table(sd_carryover_time);
3871:
3872: if g_debug then
3873: hr_utility.set_location('hxt_time_summary.gen_details',23.6);
3874: end if;
3875: sd_date_worked := TRUNC(sd_date_worked + 1, 'DD');
3876:
3877: END LOOP;
3876:
3877: END LOOP;
3878:
3879: if g_debug then
3880: hr_utility.set_location('hxt_time_summary.gen_details',23.8);
3881: end if;
3882: END LOOP;
3883: close get_sd_rules;
3884:
3882: END LOOP;
3883: close get_sd_rules;
3884:
3885: if g_debug then
3886: hr_utility.set_location('hxt_time_summary.gen_details',24);
3887:
3888: hr_utility.trace('FYI');
3889: end if;
3890: if segment_chunks.count <> 0 then
3888: hr_utility.trace('FYI');
3889: end if;
3890: if segment_chunks.count <> 0 then
3891: if g_debug then
3892: hr_utility.set_location('hxt_time_summary.gen_details',26);
3893: end if;
3894: for l_cnt in segment_chunks.first .. segment_chunks.last loop
3895: if g_debug then
3896: hr_utility.trace('segment_chunks is:'
3899: end loop;
3900: end if;
3901: if g_debug then
3902: hr_utility.trace('END FYI');
3903: hr_utility.set_location('hxt_time_summary.gen_details',27);
3904: end if;
3905: -- Get the sorted pl sql table
3906: sorted_chunks := sort( segment_chunks , 'ASC');
3907:
3909: hr_utility.trace('FYI');
3910: end if;
3911: if sorted_chunks.count <> 0 then
3912: if g_debug then
3913: hr_utility.set_location('hxt_time_summary.gen_details',28);
3914: end if;
3915: for l_cnt in sorted_chunks.first .. sorted_chunks.last loop
3916: if g_debug then
3917: hr_utility.trace('sorted_chunks is:'
3918: ||to_char(sorted_chunks(l_cnt),'DD-MON-YYYY HH24:MI:SS'));
3919: end if;
3920: end loop;
3921: if g_debug then
3922: hr_utility.set_location('hxt_time_summary.gen_details',29);
3923: end if;
3924: end if;
3925: if g_debug then
3926: hr_utility.trace('END FYI');
3928:
3929:
3930: if sorted_chunks.count > 0 then
3931: if g_debug then
3932: hr_utility.set_location('hxt_time_summary.gen_details',30);
3933: end if;
3934: for i in sorted_chunks.first .. sorted_chunks.last-1 loop
3935: if g_debug then
3936: hr_utility.set_location('hxt_time_summary.gen_details',31);
3932: hr_utility.set_location('hxt_time_summary.gen_details',30);
3933: end if;
3934: for i in sorted_chunks.first .. sorted_chunks.last-1 loop
3935: if g_debug then
3936: hr_utility.set_location('hxt_time_summary.gen_details',31);
3937: end if;
3938: chunk_start := sorted_chunks(i);
3939:
3940: if g_debug then
3955: ||to_char(chunk_stop,'DD-MON-YYYY HH24:MI:SS'));
3956: end if;
3957: -- end if;
3958: if g_debug then
3959: hr_utility.set_location('hxt_time_summary.gen_details',32);
3960: end if;
3961: IF segment_earning(chunk_start
3962: ,chunk_stop
3963: ,p_sdp_earning_type
3963: ,p_sdp_earning_type
3964: ,p_sdovr_earning_type
3965: ) THEN
3966: if g_debug then
3967: hr_utility.set_location('hxt_time_summary.gen_details',33);
3968: end if;
3969: -- hours_worked:= ((chunk_stop - chunk_start) * 24);
3970: hours_worked:= ROUND((chunk_stop - chunk_start) * 24,3);
3971:
3970: hours_worked:= ROUND((chunk_stop - chunk_start) * 24,3);
3971:
3972: if g_debug then
3973: hr_utility.trace('hours_worked :'|| hours_worked);
3974: hr_utility.set_location('hxt_time_summary.gen_details',34);
3975: end if;
3976: return_code := gen_special(location
3977: ,chunk_start
3978: ,chunk_stop
3979: ,hours_worked
3980: ,p_sdp_earning_type
3981: ,p_sdovr_earning_type);
3982: if g_debug then
3983: hr_utility.set_location('hxt_time_summary.gen_details',35);
3984: end if;
3985: END IF; -- segment_earning
3986:
3987: end loop;
3986:
3987: end loop;
3988:
3989: if g_debug then
3990: hr_utility.set_location('hxt_time_summary.gen_details',36);
3991: end if;
3992:
3993: end if;
3994:
3996:
3997: /*
3998:
3999: if g_debug then
4000: hr_utility.set_location('hxt_time_summary.gen_details',30);
4001: end if;
4002: segment_stop_time := p_shift_adjusted_time_in;
4003: if g_debug then
4004: hr_utility.trace('p_shift_adjusted_time_in :'
4012: end if;
4013: WHILE (segment_stop_time < p_shift_adjusted_time_out) AND return_code = 0
4014: LOOP
4015: if g_debug then
4016: hr_utility.set_location('hxt_time_summary.gen_details',40);
4017: end if;
4018: --while the end of the last segment is not end of time
4019: segment_start_time := segment_stop_time; -- start each segment with
4020: -- stop time of last
4023: ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
4024: end if;
4025: IF segment_start_in_rule(sd_rule_earning,sd_rule_carryover) THEN
4026: if g_debug then
4027: hr_utility.set_location('hxt_time_summary.gen_details',50);
4028: end if;
4029: -- if the start is in a rule
4030: if g_debug then
4031: hr_utility.trace('segment_stop_time :'
4040: hr_utility.trace('return_code :'||return_code);
4041: end if;
4042: ELSIF rule_start_in_segment(sd_rule_start) THEN
4043: if g_debug then
4044: hr_utility.set_location('hxt_time_summary.gen_details',60);
4045: end if;
4046: -- if a rule starts in the segment
4047: return_code := set_stop_at_rule(segment_stop_time,hours_worked);
4048: if g_debug then
4068: -- from 1200 to 1700 (shift diff defined from 0800 to 1200) and
4069: -- shift diff Override from 1700 to 2300
4070: IF to_number(to_char(segment_stop_time,'HH24MI')) < lv_standard_stop THEN
4071: if g_debug then
4072: hr_utility.set_location('hxt_time_summary.gen_details',75);
4073: end if;
4074: -- segment_stop_time := lv_standard_stop;
4075: segment_stop_time := to_date((to_char(g_date_worked, 'DDMMYYYY')||lv_standard_stop), 'DDMMYYYYHH24MI');
4076: if g_debug then
4085: end if;
4086: sd_rule_earning := NULL;
4087: ELSE -- segment_stop_time >= lv_standard_stop
4088: if g_debug then
4089: hr_utility.set_location('hxt_time_summary.gen_details',76);
4090: end if;
4091: segment_stop_time := p_shift_adjusted_time_out;
4092: if g_debug then
4093: hr_utility.trace('segment_stop_time :'
4105: -- logic gets called to insert the data into hxt_det_hours_worked
4106: sd_rule_earning := g_sdovr_id;
4107: END IF;
4108: if g_debug then
4109: hr_utility.set_location('hxt_time_summary.gen_details',77);
4110: end if;
4111: -- END g_sdovr_id is NOT NULL
4112:
4113: ELSE
4118: hr_utility.trace('segment_stop_time :'
4119: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4120: hr_utility.trace('p_shift_adjusted_time_out :'
4121: ||to_char(p_shift_adjusted_time_out,'DD-MON-YYYY HH24:MI:SS'));
4122: hr_utility.set_location('hxt_time_summary.gen_details',78);
4123: end if;
4124: segment_stop_time := p_shift_adjusted_time_out;
4125: if g_debug then
4126: hr_utility.trace('segment_stop_time :'
4146: hr_utility.trace('return_code :'||return_code);
4147: end if;
4148: IF return_code = 0 THEN
4149: if g_debug then
4150: hr_utility.set_location('hxt_time_summary.gen_details',80);
4151: end if;
4152: loop_count := loop_count + 1;
4153: if g_debug then
4154: hr_utility.trace('loop_count :'||loop_count);
4160: ,sd_rule_earning);
4161: END IF;
4162: IF loop_count > 50 THEN
4163: if g_debug then
4164: hr_utility.set_location('hxt_time_summary.gen_details',90);
4165: end if;
4166: FND_MESSAGE.SET_NAME('HXT','HXT_39300_GEN_DTAIL_EXC_LOOP'); -- HXT11
4167: return_code := call_gen_error(location, ''); -- HXT11
4168: END IF;
4167: return_code := call_gen_error(location, ''); -- HXT11
4168: END IF;
4169: IF return_code > 0 THEN
4170: if g_debug then
4171: hr_utility.set_location('hxt_time_summary.gen_details',100);
4172: end if;
4173: EXIT;
4174: END IF;
4175: END LOOP;
4176: */
4177:
4178:
4179: if g_debug then
4180: hr_utility.set_location('hxt_time_summary.gen_details',40);
4181: end if;
4182: END IF; -- there is a shift diff policy
4183:
4184: if g_debug then
4181: end if;
4182: END IF; -- there is a shift diff policy
4183:
4184: if g_debug then
4185: hr_utility.set_location('hxt_time_summary.gen_details',45);
4186: end if;
4187: RETURN return_code;
4188:
4189: EXCEPTION
4189: EXCEPTION
4190:
4191: WHEN OTHERS THEN
4192: if g_debug then
4193: hr_utility.set_location('hxt_time_summary.gen_details',110);
4194: end if;
4195: hr_utility.trace(dbms_utility.format_error_backtrace);
4196: FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR'); -- HXT11
4197: RETURN call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,location, '', sqlerrm); -- HXT11
4219:
4220: BEGIN
4221:
4222: if g_debug then
4223: hr_utility.set_location('hxt_time_summary.Get_Include',10);
4224: end if;
4225: -- Check if no earn group passed
4226: IF p_egt_id is null THEN
4227: if g_debug then
4224: end if;
4225: -- Check if no earn group passed
4226: IF p_egt_id is null THEN
4227: if g_debug then
4228: hr_utility.set_location('hxt_time_summary.Get_Include',20);
4229: end if;
4230: RETURN l_retcode;
4231: ELSE
4232: BEGIN
4230: RETURN l_retcode;
4231: ELSE
4232: BEGIN
4233: if g_debug then
4234: hr_utility.set_location('hxt_time_summary.Get_Include',30);
4235: end if;
4236: -- Check if element_type exists in earning group
4237: SELECT 'Y'
4238: INTO l_retcode
4250:
4251: EXCEPTION
4252: WHEN no_data_found THEN
4253: if g_debug then
4254: hr_utility.set_location('hxt_time_summary.Get_Include',40);
4255: end if;
4256: RETURN l_retcode;
4257: END;
4258: END IF;
4260: EXCEPTION
4261: -- Return Oracle error number
4262: WHEN others THEN
4263: if g_debug then
4264: hr_utility.set_location('hxt_time_summary.Get_Include',50);
4265: end if;
4266: hr_utility.trace(dbms_utility.format_error_backtrace);
4267: FND_MESSAGE.SET_NAME('HXT','HXT_39270_OR_ERR_G_GROUP'); -- HXT11
4268: l_error_code := call_hxthxc_gen_error('HXT','HXT_39270_OR_ERR_G_GROUP',l_location,'', sqlerrm); -- HXT11
4322:
4323: BEGIN
4324:
4325: if g_debug then
4326: hr_utility.set_location('hxt_time_summary.GEN_SPECIAL',10);
4327: hr_utility.trace('p_time_in :'||to_char(p_time_in,'DD-MON-YYYY HH24:MI:SS'));
4328: hr_utility.trace('p_time_out :'||to_char(p_time_out,'DD-MON-YYYY HH24:MI:SS'));
4329: hr_utility.trace('p_hours_worked :'||p_hours_worked);
4330: hr_utility.trace('p_shift_diff_earning_id :'||p_shift_diff_earning_id);
4458: --,g_GROUP_ID
4459: );
4460:
4461: if g_debug then
4462: hr_utility.set_location('hxt_time_summary.GEN_SPECIAL',20);
4463: end if;
4464: END; -- gen special
4465:
4466: -- begin