[Home] [Help]
1: package body hr_entry as
2: /* $Header: pyeentry.pkb 120.14.12010000.1 2008/07/27 22:29:50 appldev ship $ */
3: --
4: -- NAME
5: -- hr_entry.return_termination_date
1: package body hr_entry as
2: /* $Header: pyeentry.pkb 120.14.12010000.1 2008/07/27 22:29:50 appldev ship $ */
3: --
4: -- NAME
5: -- hr_entry.return_termination_date
6: --
7: -- DESCRIPTION
8: -- Returns the actual_termination_date if an assignment has been
9: -- terminated.
20: -- Select the actual termination date is the assignment has been
21: -- terminated.
22: --
23: if g_debug then
24: hr_utility.set_location('hr_entry.return_termination_date', 1);
25: end if;
26: begin
27: select pos.actual_termination_date
28: into l_actual_termination_date
43: --
44: end return_termination_date;
45: --
46: -- NAME
47: -- hr_entry.get_nonrecurring_dates
48: --
49: -- DESCRIPTION
50: -- Called when a nonrecurring entry is about to be created. Makes sure that
51: -- the assignment is to a payroll and also a time period exists. Returns the
75: begin
76: g_debug := hr_utility.debug_enabled;
77: --
78: if g_debug then
79: hr_utility.set_location('hr_entry.get_nonrecurring_dates',5);
80: end if;
81: --
82: -- Retrieve the payroll for the assignment on the date the nonrecurring
83: -- entry is being created NB if there is no payroll then it is invalid to
102: hr_utility.raise_error;
103: end;
104: --
105: if g_debug then
106: hr_utility.set_location('hr_entry.get_nonrecurring_dates',10);
107: end if;
108: --
109: -- Retrieve the start and end dates of the period for the payroll on the
110: -- date on which the nonrecurring entry is being created NB. a payroll
126: hr_utility.raise_error;
127: end;
128: --
129: if g_debug then
130: hr_utility.set_location('hr_entry.get_nonrecurring_dates',15);
131: end if;
132: --
133: -- Current assignment record starts after the beginning of the time period.
134: if v_asg_effective_start_date > v_time_period_start_date then
156: --
157: end if;
158: --
159: if g_debug then
160: hr_utility.set_location('hr_entry.get_nonrecurring_dates',20);
161: end if;
162: --
163: -- Current assignment record ends before the finish of the time period.
164: if v_asg_effective_end_date < v_time_period_end_date then
186: --
187: end if;
188: --
189: if g_debug then
190: hr_utility.set_location('hr_entry.get_nonrecurring_dates',25);
191: end if;
192: --
193: -- Return the start and end dates of the nonrecurring entry.
194: p_effective_start_date := greatest(v_asg_effective_start_date,
203: --
204: end get_nonrecurring_dates;
205: --
206: -- NAME
207: -- hr_entry.chk_entry_overlap
208: --
209: -- DESCRIPTION
210: -- When multiple entries are not allowed then make sure there are no overlaps
211: -- of normal entries of ther same type ie. entry_type = 'E'. For nonrecurring
241: --
242: begin
243: --
244: if g_debug then
245: hr_utility.set_location('hr_entry.chk_entry_overlap',5);
246: end if;
247: --
248: -- Only do check if the entry being altered is a normal entry ie. not
249: -- adjustment, additional etc ... If multiple concurrent entries are not
357: p_element_link_id is null or
358: p_session_date is null) then
359: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
360: hr_utility.set_message_token('PROCEDURE',
361: 'hr_entry.return_qualifying_conditions');
362: hr_utility.set_message_token('STEP','1');
363: hr_utility.raise_error;
364: end if;
365: --
366: -- select the qualifying conditions for los.
367: --
368: begin
369: if g_debug then
370: hr_utility.set_location('hr_entry.return_qualifying_conditions', 5);
371: end if;
372: select pel.qualifying_age,
373: pel.qualifying_length_of_service,
374: pel.qualifying_units
383: exception
384: when NO_DATA_FOUND then
385: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
386: hr_utility.set_message_token('PROCEDURE',
387: 'hr_entry.return_qualifying_conditions');
388: hr_utility.set_message_token('STEP','5');
389: hr_utility.raise_error;
390: end;
391: --
394: -- Need to select the valid los date
395: --
396: begin
397: if g_debug then
398: hr_utility.set_location('hr_entry.return_qualifying_conditions', 10);
399: end if;
400: open csr_los_date;
401: fetch csr_los_date into p_los_date;
402: close csr_los_date;
403: exception
404: when NO_DATA_FOUND then
405: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
406: hr_utility.set_message_token('PROCEDURE',
407: 'hr_entry.return_qualifying_conditions');
408: hr_utility.set_message_token('STEP','10');
409: hr_utility.raise_error;
410: end;
411: --
420: if (l_qualifying_age is not null) then
421: --
422: begin
423: if g_debug then
424: hr_utility.set_location('hr_entry.return_qualifying_conditions', 15);
425: end if;
426: open csr_age_date;
427: fetch csr_age_date into p_age_date;
428: close csr_age_date;
435: --
436: end return_qualifying_conditions;
437: --
438: -- NAME
439: -- hr_entry.generate_entry_id
440: --
441: -- DESCRIPTION
442: -- Generates then next sequence value for inserting an element entry into the
443: -- PAY_ELEMENT_ENTRIES_F base table.
455: FROM SYS.DUAL;
456: exception
457: when NO_DATA_FOUND then
458: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
459: hr_utility.set_message_token('PROCEDURE','hr_entry.generate_entry_id');
460: hr_utility.set_message_token('STEP','1');
461: hr_utility.raise_error;
462: end;
463: --
467: --
468: end generate_entry_id;
469: --
470: -- NAME
471: -- hr_entry.generate_run_result_id
472: --
473: -- DESCRIPTION
474: -- Generates then next sequence value for inserting a run result into the
475: -- PAY_RUN_RESULTS base table.
487: FROM SYS.DUAL;
488: exception
489: when NO_DATA_FOUND then
490: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
491: hr_utility.set_message_token('PROCEDURE','hr_entry.generate_run_result_id');
492: hr_utility.set_message_token('STEP','1');
493: hr_utility.raise_error;
494: end;
495: --
499: --
500: end generate_run_result_id;
501: --
502: -- NAME
503: -- hr_entry.entry_process_in_run
504: --
505: -- DESCRIPTION
506: -- This function return a boolean value for the specified
507: -- element_type_id depending on the process_in_run_flag attribute.
536: end entry_process_in_run;
537: --------------------------------------------------------------------------------
538: --
539: -- NAME
540: -- hr_entry.Assignment_eligible_for_link
541: --
542: -- DESCRIPTION
543: -- Returns 'Y' if the specified assignment and link match as at the
544: -- specified date. A match indicates that the assignment is eligible for
675: --
676: end assignment_eligible_for_link;
677: --
678: -- NAME
679: -- hr_entry.chk_asg_visible
680: --
681: -- DESCRIPTION
682: -- Raise error PAY_34811_ENTRY_MAINT_SEC_ASG if the user does not have the
683: -- appropriate privileges to see the assignment identifed by p_assignment_id.
694: v_asg_visible number;
695: begin
696: --
697: if g_debug then
698: hr_utility.set_location('hr_entry.chk_asg_visible', 1);
699: end if;
700: --
701: -- Check to see if this assignment is visible to a secure user.
702: -- If not then raise error.
707: --
708: if csr_sec_asg%notfound then
709: --
710: if g_debug then
711: hr_utility.set_location('hr_entry.chk_asg_visible', 2);
712: hr_utility.trace('Assignment ID Not Found: '||p_assignment_id);
713: end if;
714: --
715: -- The user is not authorized to process this assignment.
723: --
724: close csr_sec_asg;
725: --
726: if g_debug then
727: hr_utility.set_location('hr_entry.chk_asg_visible', 3);
728: end if;
729: --
730: end chk_asg_visible;
731: --------------------------------------------------------------------------------
729: --
730: end chk_asg_visible;
731: --------------------------------------------------------------------------------
732: -- NAME
733: -- hr_entry.get_eligibility_period
734: --
735: -- DESCRIPTION
736: -- This procedure selects the minimum or maximum (or both) effective assignment
737: -- dates where the assignment is eligible for a given element link.
825: l_time_period_end_date date;
826: --
827: no_current_eligibility exception;
828: --
829: l_procedure_name constant varchar2 (80) := 'hr_entry.get_eligibility_period';
830: lpi_session_date constant date := trunc (p_session_date);
831: --
832: all_parameters_are_valid constant boolean :=
833: (
900: hr_utility.set_location(l_procedure_name,4);
901: end if;
902: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
903: hr_utility.set_message_token('PROCEDURE',
904: 'hr_entry.get_eligibility_period');
905: hr_utility.set_message_token('STEP','4');
906: hr_utility.raise_error;
907: --
908: end if;
975: -- Bugfix 5135065
976: -- We are not interested in assignment pieces that end before the beginning
977: -- of the time period, so we pass the time period start date to csr_minimum
978: for c1rec in csr_minimum(p_assignment_id, lpi_session_date, l_time_period_start_date) loop
979: if(hr_entry.assignment_eligible_for_link(
980: p_assignment_id,
981: p_element_link_id,
982: least (c1rec.effective_end_date, l_link.link_end)) = 'N')
983: then
1028: -- This should reduce the number of times we need to call the expensive
1029: -- assignment_eligible_for_link function.
1030: -- See above explanation.
1031: for c1rec in csr_maximum(p_assignment_id, lpi_session_date, l_time_period_end_date) loop
1032: if(hr_entry.assignment_eligible_for_link(
1033: p_assignment_id,
1034: p_element_link_id,
1035: greatest (c1rec.effective_start_date, l_link.link_start)) = 'N')
1036: then
1130: --
1131: end get_eligibility_period;
1132: --------------------------------------------------------------------------------
1133: -- NAME
1134: -- hr_entry.get_eligibility_period
1135: --
1136: -- DESCRIPTION
1137: -- Bugfix 5135065
1138: -- Overloaded version provided for backwards compatibility. Refer to new
1164: --
1165: end get_eligibility_period;
1166: --------------------------------------------------------------------------------
1167: -- NAME
1168: -- hr_entry.entry_asg_pay_link_dates
1169: --
1170: -- DESCRIPTION
1171: -- This procedure returns the min(effective_start/end_date) for a specified
1172: -- element link and payroll. Also, if the specified employee assignment has
1235: and p_session_date = trunc (p_session_date));
1236: --
1237: if g_debug then
1238: --
1239: hr_utility.trace('begin hr_entry.entry_asg_pay_link_dates');
1240: hr_utility.trace(' p_session_date:' || To_Char(p_session_date,'DD-MON-YYYY'));
1241: hr_utility.trace(' p_assignment_id:' || p_assignment_id);
1242: hr_utility.trace(' p_element_link_id:' || p_element_link_id);
1243: --
1246: -- Select the element termination processing rule, assignment period of
1247: -- service and assignment primary flag
1248: --
1249: if g_debug then
1250: hr_utility.set_location('hr_entry.entry_asg_pay_link_dates', 1);
1251: end if;
1252: --
1253: begin
1254: --
1287: --
1288: when NO_DATA_FOUND then
1289: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1290: hr_utility.set_message_token('PROCEDURE',
1291: 'hr_entry.entry_asg_pay_link_dates');
1292: hr_utility.set_message_token('STEP','2');
1293: end;
1294: --
1295: if g_debug then
1361: (v_orig_term_rule_date_func = 'Y' and v_primary_flag = 'Y') then -- Old behaviour
1362: --
1363: if g_debug then
1364: hr_utility.trace(' employee terminated');
1365: hr_utility.set_location('hr_entry.entry_asg_pay_link_dates', 2);
1366: hr_utility.trace('v_post_termination_rule :' || v_post_termination_rule);
1367: end if;
1368: --
1369: -- Get the termination rule date
1426: -- First we need to check if the current or future assignment rows have
1427: -- a 'TERM_ASSIGN' status.
1428: --
1429: if g_debug then
1430: hr_utility.set_location('hr_entry.entry_asg_pay_link_dates', 3);
1431: end if;
1432: begin
1433: select min(asg.effective_start_date)
1434: into v_asg_term_date
1445: --
1446: if v_asg_term_date is null then
1447: --
1448: if g_debug then
1449: hr_utility.set_location('hr_entry.entry_asg_pay_link_dates', 4);
1450: end if;
1451: --
1452: begin
1453: select asg.effective_start_date
1517: v_element_term_rule_date := v_asg_term_date - 1;
1518: end if;
1519: elsif (v_post_termination_rule = 'F') then
1520: if g_debug then
1521: hr_utility.set_location('hr_entry.entry_asg_pay_link_dates', 5);
1522: end if;
1523: begin
1524: select max(asg.effective_end_date)
1525: into v_element_term_rule_date
1528: and asg.effective_start_date >= v_asg_term_date;
1529: end;
1530: elsif (v_post_termination_rule = 'L') then
1531: if g_debug then
1532: hr_utility.set_location('hr_entry.entry_asg_pay_link_dates', 6);
1533: end if;
1534: /*
1535: begin
1536: select ptp.end_date
1606: --
1607: -- Select the minimum and maximum element link dates.
1608: --
1609: if g_debug then
1610: hr_utility.set_location('hr_entry.entry_asg_pay_link_dates', 7);
1611: end if;
1612: --
1613: begin
1614: select min(pel.effective_start_date),
1628: -- If the assignment is to a payroll then,
1629: -- we must select the minimum and maximum effective dates of the payroll.
1630: --
1631: if g_debug then
1632: hr_utility.set_location('hr_entry.entry_asg_pay_link_dates', 8);
1633: end if;
1634: begin
1635: select min(pay.effective_start_date),
1636: max(pay.effective_end_date)
1664: p_payroll_start_date := v_payroll_start_date;
1665: p_payroll_end_date := v_payroll_end_date;
1666: --
1667: if g_debug then
1668: hr_utility.trace('end hr_entry.entry_asg_pay_link_dates');
1669: end if;
1670: --
1671: end entry_asg_pay_link_dates;
1672: --
1670: --
1671: end entry_asg_pay_link_dates;
1672: --
1673: -- NAME
1674: -- hr_entry.recurring_entry_end_date
1675: --
1676: -- DESCRIPTION
1677: -- This function is used to return the valid effective end of a recurring entry.
1678: -- The effective end date is determined by selecting the least date of:
1753: -- delete next/future changes therefore set the current_effective_end_date
1754: --
1755: if p_element_entry_id is not null then
1756: if g_debug then
1757: hr_utility.set_location('hr_entry.recurring_entry_end_date', 0);
1758: end if;
1759: begin
1760: select e.effective_end_date
1761: into v_current_effective_end_date
1774: -- If yes, then we must take the min(effective_start_date) -1
1775: --
1776: if upper (p_overlap_chk) = 'Y' then
1777: if g_debug then
1778: hr_utility.set_location('hr_entry.recurring_entry_end_date', 1);
1779: end if;
1780: begin
1781: -- INDEX hint added following NHS project recommendation
1782: select /*+ INDEX(pee, pay_element_entries_f_n51) */
1817: chk_asg_visible(p_assignment_id, p_session_date);
1818: --
1819: -- Get the end date of the link.
1820: --
1821: hr_entry.entry_asg_pay_link_dates (p_assignment_id,
1822: p_element_link_id,
1823: p_session_date,
1824: v_element_term_rule_date,
1825: v_out_date_not_required,
1830: --
1831: -- Find the minimum assignment (effective_start_date - 1) when the current or
1832: -- future assignment changes is NOT eligible to the element link.
1833: --
1834: hr_entry.get_eligibility_period (p_assignment_id,
1835: p_element_link_id,
1836: p_session_date,
1837: v_out_date_not_required,
1838: v_asg_max_eligibility_date);
1883: --
1884: end recurring_entry_end_date;
1885: --
1886: -- NAME
1887: -- hr_entry.chk_element_entry_eligibility
1888: --
1889: -- DESCRIPTION
1890: -- This procedure is used to check if entries (which are defined below) are
1891: -- eligble to be inserted/deleted.
1982: chk_asg_visible(p_assignment_id, p_session_date);
1983: --
1984: -- We must ensure that the entry is eligible through the payroll and link dates.
1985: --
1986: hr_entry.entry_asg_pay_link_dates (p_assignment_id,
1987: p_element_link_id,
1988: p_session_date,
1989: v_element_term_rule_date,
1990: v_element_link_start_date,
1994: --
1995: -- Ensure that the element entry which is being inserted is eligible
1996: -- through its link/assignment criteria.
1997: --
1998: hr_entry.get_eligibility_period (p_assignment_id,
1999: p_element_link_id,
2000: p_session_date,
2001: -- Bugfix 5135065
2002: p_time_period_start_date,
2068: --
2069: end chk_element_entry_eligibility;
2070: --
2071: -- NAME
2072: -- hr_entry.chk_element_entry_eligbility
2073: --
2074: -- DESCRIPTION
2075: -- Bugfix 5135065
2076: -- Overloaded version provided for backwards compatibility
2106: --
2107: end chk_element_entry_eligbility;
2108: --
2109: -- NAME
2110: -- hr_entry.chk_element_entry_open
2111: --
2112: -- DESCRIPTION
2113: -- This procedure does the following checks:
2114: -- 1) Ensure that the element type is not closed for entry currently
2199: -- stable view of the time period table. See if a time period has been
2200: -- closed over the period of change. If it has then error !
2201: --lock table pay_payrolls_f in share mode;
2202: --
2203: if hr_entry.entry_process_in_run(p_element_type_id, p_session_date) then
2204: --
2205: for v_time_period in csr_time_period(p_assignment_id,
2206: p_validation_start_date,
2207: p_validation_end_date) loop
2233: --
2234: end chk_element_entry_open;
2235: --
2236: -- NAME
2237: -- hr_entry.derive_default_value
2238: --
2239: -- DESCRIPTION
2240: -- This procedure is used to return default screen and database formatted
2241: -- values in either a cold or hot format for the specified link and
2308: p_min_max_def = 'DEF') then
2309: null;
2310: else
2311: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2312: hr_utility.set_message_token('PROCEDURE','hr_entry.derive_default_value');
2313: hr_utility.set_message_token('STEP',1);
2314: hr_utility.raise_error;
2315: end if;
2316: --
2317: -- Need to determine the input value unit of measure and if it is using
2318: -- hot or cold defaults.
2319: --
2320: if g_debug then
2321: hr_utility.set_location('hr_entry.derive_default_value', 2);
2322: end if;
2323: begin
2324: select iv.uom,
2325: iv.hot_default_flag,
2336: exception
2337: when NO_DATA_FOUND then
2338: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2339: hr_utility.set_message_token('PROCEDURE',
2340: 'hr_entry.derive_default_value');
2341: hr_utility.set_message_token('STEP','2');
2342: hr_utility.raise_error;
2343: end;
2344: --
2345: -- If using cold defaults then, we must select the value at the link level.
2346: --
2347: if v_hot_default_flag = 'N' then
2348: if g_debug then
2349: hr_utility.set_location('hr_entry.derive_default_value', 3);
2350: end if;
2351: begin
2352: -- INDEX hint added following NHS project recommendation
2353: select /*+ INDEX(l, pay_link_input_values_f_n2) */
2365: exception
2366: when NO_DATA_FOUND then
2367: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2368: hr_utility.set_message_token('PROCEDURE',
2369: 'hr_entry.derive_default_value');
2370: hr_utility.set_message_token('STEP','3');
2371: hr_utility.raise_error;
2372: end;
2373: else
2377: -- the hot default values and encapsulated in quotations (").
2378: --
2379: if (p_min_max_def = 'DEF') then
2380: if g_debug then
2381: hr_utility.set_location('hr_entry.derive_default_value', 4);
2382: end if;
2383: begin
2384: select decode(l.default_value,
2385: '',i.default_value,
2397: exception
2398: when NO_DATA_FOUND then
2399: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2400: hr_utility.set_message_token('PROCEDURE',
2401: 'hr_entry.derive_default_value');
2402: hr_utility.set_message_token('STEP','4');
2403: hr_utility.raise_error;
2404: end;
2405: else
2403: hr_utility.raise_error;
2404: end;
2405: else
2406: if g_debug then
2407: hr_utility.set_location('hr_entry.derive_default_value', 5);
2408: end if;
2409: begin
2410: select i.default_value,
2411: i.min_value,
2420: exception
2421: when NO_DATA_FOUND then
2422: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2423: hr_utility.set_message_token('PROCEDURE',
2424: 'hr_entry.derive_default_value');
2425: hr_utility.set_message_token('STEP','5');
2426: hr_utility.raise_error;
2427: end;
2428: end if;
2446: --
2447: if (v_lookup_type is not null and
2448: v_value_format_in is not null) then
2449: if g_debug then
2450: hr_utility.set_location('hr_entry.derive_default_value', 6);
2451: end if;
2452: begin
2453: select h.meaning
2454: into v_value_format_out
2458: exception
2459: when NO_DATA_FOUND then
2460: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2461: hr_utility.set_message_token('PROCEDURE',
2462: 'hr_entry.derive_default_value');
2463: hr_utility.set_message_token('STEP','6');
2464: hr_utility.raise_error;
2465: end;
2466: --
2470: --
2471: elsif (v_value_set_id is not null and
2472: v_value_format_in is not null) then
2473: if g_debug then
2474: hr_utility.set_location('hr_entry.derive_default_value', 7);
2475: end if;
2476: v_value_format_out := pay_input_values_pkg.decode_vset_value(
2477: v_value_set_id,v_value_format_in);
2478: if v_value_format_out is null then
2481: -- corresponding meaning was found, raise an error
2482: --
2483: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2484: hr_utility.set_message_token('PROCEDURE',
2485: 'hr_entry.derive_default_value');
2486: hr_utility.set_message_token('STEP','7');
2487: hr_utility.raise_error;
2488: end if;
2489: else
2515: --
2516: end derive_default_value;
2517: --
2518: -- NAME
2519: -- hr_entry.chk_mandatory_input_value
2520: --
2521: -- DESCRIPTION
2522: -- This procedure produces an error is any input value which is defined as
2523: -- having a mandatory value is null.
2533: --
2534: begin
2535: g_debug := hr_utility.debug_enabled;
2536: if g_debug then
2537: hr_utility.set_location ('hr_entry.chk_mandatory_input_value',1);
2538: end if;
2539: hr_general.assert_condition (p_session_date = trunc (p_session_date)
2540: and p_input_value_id is not null
2541: and p_session_date is not null);
2542: --
2543: if (p_input_value_id is not null and
2544: p_entry_value is null) then
2545: if g_debug then
2546: hr_utility.set_location('hr_entry.chk_mandatory_input_value', 1);
2547: end if;
2548: --
2549: -- Select the hot/mandatory flag details.
2550: --
2566: exception
2567: when NO_DATA_FOUND then
2568: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2569: hr_utility.set_message_token('PROCEDURE',
2570: 'hr_entry.chk_mandatory_input_value');
2571: hr_utility.set_message_token('STEP','1');
2572: hr_utility.raise_error;
2573: end;
2574: --
2586: --
2587: -- Hot check.
2588: --
2589: if g_debug then
2590: hr_utility.set_location('hr_entry.chk_mandatory_input_value', 5);
2591: end if;
2592: begin
2593: select nvl(l.default_value,i.default_value)
2594: into v_default_value
2617: (
2618: p_element_link_id number,
2619: p_validation_start_date date,
2620: p_num_entry_values number,
2621: p_input_value_id_tbl hr_entry.number_table,
2622: p_entry_value_tbl hr_entry.varchar2_table
2623: ) is
2624: --
2625: begin
2618: p_element_link_id number,
2619: p_validation_start_date date,
2620: p_num_entry_values number,
2621: p_input_value_id_tbl hr_entry.number_table,
2622: p_entry_value_tbl hr_entry.varchar2_table
2623: ) is
2624: --
2625: begin
2626: if g_debug then
2623: ) is
2624: --
2625: begin
2626: if g_debug then
2627: hr_utility.set_location ('hr_entry.chk_mandatory_entry_values',1);
2628: end if;
2629: hr_general.assert_condition (
2630: p_validation_start_date = trunc (p_validation_start_date));
2631: --
2634: if p_num_entry_values > 0 then
2635: --
2636: for v_loop in 1..p_num_entry_values loop
2637: --
2638: hr_entry.chk_mandatory_input_value(p_input_value_id_tbl(v_loop),
2639: p_entry_value_tbl(v_loop),
2640: p_validation_start_date,
2641: p_element_link_id);
2642: --
2646: --
2647: end chk_mandatory_entry_values;
2648: --
2649: -- NAME
2650: -- hr_entry.chk_element_entry
2651: --
2652: -- DESCRIPTION
2653: -- This procedure is used for referential/standard checks when inserting/
2654: -- updating or deleteing element enries.
2717: --
2718: end chk_element_entry;
2719: --
2720: -- NAME
2721: -- hr_entry.chk_element_entry_main
2722: --
2723: -- DESCRIPTION
2724: -- This procedure is used for referential/standard checks when inserting/
2725: -- updating or deleteing element enries.
2835: begin
2836: g_debug := hr_utility.debug_enabled;
2837: --
2838: if g_debug then
2839: hr_utility.set_location('hr_entry.chk_element_entry_main', 5);
2840: end if;
2841: --
2842: -- Fetch element type information relevent to the creation of element
2843: -- entries
2881: exception
2882: when no_data_found then
2883: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2884: hr_utility.set_message_token('PROCEDURE',
2885: 'hr_entry.chk_element_entry');
2886: hr_utility.set_message_token('STEP','1');
2887: hr_utility.raise_error;
2888: end;
2889: --
2938: (p_dt_delete_mode = 'DELETE_NEXT_CHANGE' and
2939: p_validation_end_date = hr_general.end_of_time)) then
2940: --
2941: if g_debug then
2942: hr_utility.set_location('hr_entry.chk_element_entry_main', 10);
2943: end if;
2944: --
2945: -- If we are inserting or doing date-effective delete next/future
2946: -- changes then we must set the effective_end_date to be passed back
2946: -- changes then we must set the effective_end_date to be passed back
2947: -- to the form and, also set the validation_end_date NB. this will take
2948: -- into account if multiple entries are allowed.
2949: --
2950: v_validation_end_date := hr_entry.recurring_entry_end_date
2951: (p_assignment_id,
2952: p_element_link_id,
2953: p_session_date,
2954: 'Y',
2975: --
2976: else
2977: --
2978: if g_debug then
2979: hr_utility.set_location('hr_entry.chk_element_entry_main', 15);
2980: end if;
2981: --
2982: -- Validate that it is OK to create a nonrecurring entry ie. assignment
2983: -- is to a payroll and also a time period exists. Also calculate the start
2983: -- is to a payroll and also a time period exists. Also calculate the start
2984: -- and end dates of the nonrecurring entry taking into account changes in
2985: -- payroll.
2986: --
2987: hr_entry.get_nonrecurring_dates(p_assignment_id,
2988: p_session_date,
2989: v_validation_start_date,
2990: v_validation_end_date,
2991: v_payroll_id,
3000: -- wmcveagh, bug 493056. Use element effective start date rather than
3001: -- validation start date if vsd is later.
3002: --
3003: if g_debug then
3004: hr_utility.set_location('hr_entry.chk_element_entry_main', 17);
3005: end if;
3006: --
3007: open c_element_start_date ;
3008: fetch c_element_start_date into v_element_effective_start_date;
3009: close c_element_start_date;
3010: --
3011: if v_validation_start_date < v_element_effective_start_date then
3012: if g_debug then
3013: hr_utility.set_location('hr_entry.chk_element_entry_main', 18);
3014: end if;
3015: v_validation_start_date := v_element_effective_start_date;
3016: end if;
3017: --
3024: (p_dt_delete_mode = 'DELETE_NEXT_CHANGE' and
3025: p_validation_end_date = hr_general.end_of_time)) then
3026: --
3027: if g_debug then
3028: hr_utility.set_location('hr_entry.chk_element_entry_main', 20);
3029: end if;
3030: -- start 115.26
3031: -- bugfix 1827998
3032: declare
3053: -- Make sure the entry does not overlap if multiple entries are not
3054: -- allowed.
3055: --
3056: if p_creator_type not in ('EE', 'RR', 'AD', 'AE', 'NR', 'PR') then
3057: hr_entry.chk_entry_overlap(p_element_entry_id,
3058: p_assignment_id,
3059: p_element_link_id,
3060: v_processing_type,
3061: p_entry_type,
3068: end if;
3069: end if;
3070: --
3071: if g_debug then
3072: hr_utility.set_location('hr_entry.chk_element_entry_main', 25);
3073: end if;
3074: --
3075: -- Call the procedure: chk_element_entry_open
3076: -- This procedure does common checks for insert/update/delete
3075: -- Call the procedure: chk_element_entry_open
3076: -- This procedure does common checks for insert/update/delete
3077: -- commit actions.
3078: --
3079: hr_entry.chk_element_entry_open(v_element_type_id,
3080: p_session_date,
3081: v_validation_start_date,
3082: v_validation_end_date,
3083: p_assignment_id);
3091: p_entry_type <> 'E') or
3092: v_processing_type = 'N')) then
3093: --
3094: if g_debug then
3095: hr_utility.set_location('hr_entry.chk_element_entry_main', 30);
3096: end if;
3097: --
3098: -- We must ensure that the entry is eligible to be inserted.
3099: --
3103: -- These should only have been derived if the entry is non-recurring
3104: -- In this case we can greatly reduce the number of calls made
3105: -- by get_eligibility_period to the expensive assignment_eligible_for_link
3106: -- function.
3107: hr_entry.chk_element_entry_eligibility(p_assignment_id,
3108: p_element_link_id,
3109: p_session_date,
3110: p_usage,
3111: v_validation_start_date,
3123: p_creator_type not in ('EE', 'RR', 'AD', 'AE', 'NR', 'PR') and
3124: p_usage = 'INSERT') then
3125: --
3126: if g_debug then
3127: hr_utility.set_location('hr_entry.chk_element_entry_main', 35);
3128: end if;
3129: --
3130: -- Additional Entry is trying to be inserted therefore, check for
3131: -- another existing Additional Entry within the current period.
3161: (p_dt_delete_mode = 'ZAP' or
3162: p_dt_delete_mode = 'DELETE')) then
3163: --
3164: if g_debug then
3165: hr_utility.set_location('hr_entry.chk_element_entry_main', 40);
3166: end if;
3167: --
3168: -- If the p_dt_delete_mode is not null then need to ensure that
3169: -- the entry being deleted does not orphan any adjustments.
3197: if (p_entry_type = 'S' and
3198: p_usage = 'INSERT') then
3199: --
3200: if g_debug then
3201: hr_utility.set_location('hr_entry.chk_element_entry_main', 45);
3202: end if;
3203: --
3204: -- Ensure that no other override exists within the current
3205: -- period for the same element for the assignment.
3226: hr_utility.raise_error;
3227: end if;
3228: --
3229: if g_debug then
3230: hr_utility.set_location('hr_entry.chk_element_entry_main', 50);
3231: end if;
3232: --
3233: -- Ensure that an adjustment entry does not exist when trying to
3234: -- insert override.
3272: p_entry_type = 'A') and
3273: p_usage = 'INSERT') then
3274: --
3275: if g_debug then
3276: hr_utility.set_location('hr_entry.chk_element_entry_main', 55);
3277: end if;
3278: --
3279: -- Ensure that the parent entry of the adjustment exists for the
3280: -- duration of the adjustment.
3302: hr_utility.raise_error;
3303: end if;
3304: --
3305: if g_debug then
3306: hr_utility.set_location('hr_entry.chk_element_entry_main', 60);
3307: end if;
3308: --
3309: -- Ensure that an override does not exist for the entry which is to be
3310: -- adjusted.
3332: hr_utility.raise_error;
3333: end if;
3334: --
3335: if g_debug then
3336: hr_utility.set_location('hr_entry.chk_element_entry_main', 65);
3337: end if;
3338: --
3339: -- Ensure that an existing adjustment for this entry does not exist for
3340: -- the current period/assignment.
3394: --
3395: end chk_element_entry_main;
3396: --
3397: -- NAME
3398: -- hr_entry.ins_3p_ent_values
3399: --
3400: -- DESCRIPTION
3401: -- This function is used for third party inserts into:
3402: -- PAY_ELEMENT_ENTRIES_F (If an abscence, or DT functions are being used).
3410: p_element_link_id number,
3411: p_element_entry_id number,
3412: p_session_date date,
3413: p_num_entry_values number,
3414: p_input_value_id_tbl hr_entry.number_table,
3415: p_entry_value_tbl hr_entry.varchar2_table
3416: ) is
3417: --
3418: -- cursor to fetch balance adjustment element entry values
3411: p_element_entry_id number,
3412: p_session_date date,
3413: p_num_entry_values number,
3414: p_input_value_id_tbl hr_entry.number_table,
3415: p_entry_value_tbl hr_entry.varchar2_table
3416: ) is
3417: --
3418: -- cursor to fetch balance adjustment element entry values
3419: cursor get_b_eevs(p_element_entry_id number,
3455: --
3456: begin
3457: --
3458: if g_debug then
3459: hr_utility.set_location('hr_entry.ins_3p_entry_values', 1);
3460: end if;
3461: begin
3462: --
3463: select ee.element_entry_id,
3500: exception
3501: when NO_DATA_FOUND then
3502: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3503: hr_utility.set_message_token('PROCEDURE',
3504: 'hr_entry.ins_3p_entry_values');
3505: hr_utility.set_message_token('STEP','6');
3506: hr_utility.raise_error;
3507: end;
3508: --
3506: hr_utility.raise_error;
3507: end;
3508: --
3509: if g_debug then
3510: hr_utility.set_location('hr_entry.ins_3p_entry_values', 2);
3511: end if;
3512: --
3513: -- Insert all the entry values if there are any.
3514: if p_num_entry_values > 0 then
3515: --
3516: -- NB. mandatory checks are not applied to adjustments.
3517: if not v_entry_type in ('A','R') then
3518: --
3519: hr_entry.chk_mandatory_entry_values
3520: (p_element_link_id,
3521: p_session_date,
3522: p_num_entry_values,
3523: p_input_value_id_tbl,
3610: v_input_currency_code = v_output_currency_code) or
3611: v_entry_type = 'B') then
3612: --
3613: -- Ensure that the entry can be processed in a run.
3614: if hr_entry.entry_process_in_run(v_element_type_id, p_session_date) then
3615: --
3616: -- Set the processing status. If the entry is a balance adjustment then
3617: -- the run result status must be set to processed.
3618: if v_entry_type = 'B' then
3623: -- rate
3624: if v_input_currency_code <> v_output_currency_code then
3625: begin
3626: if g_debug then
3627: hr_utility.set_location('hr_entry.ins_3p_entry_values', 4);
3628: end if;
3629: --
3630: select business_group_id
3631: into v_bg_id
3652: --
3653: end if;
3654: --
3655: -- Get the next sequenced run_result_id. Step 5.
3656: v_run_result_id := hr_entry.generate_run_result_id;
3657: --
3658: -- Insert Run Result. Step 6.
3659: if g_debug then
3660: hr_utility.set_location('hr_entry.ins_3p_entry_values', 6);
3656: v_run_result_id := hr_entry.generate_run_result_id;
3657: --
3658: -- Insert Run Result. Step 6.
3659: if g_debug then
3660: hr_utility.set_location('hr_entry.ins_3p_entry_values', 6);
3661: end if;
3662: -- First get the Jurisdiction if one exists.
3663: begin
3664: select eev.screen_entry_value
3705: exception
3706: when NO_DATA_FOUND then
3707: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3708: hr_utility.set_message_token('PROCEDURE',
3709: 'hr_entry.ins_3p_entry_values');
3710: hr_utility.set_message_token('STEP','6');
3711: hr_utility.raise_error;
3712: end;
3713: --
3717: --
3718: -- insert run results values converting all money uom's to the output
3719: -- currency value.
3720: if g_debug then
3721: hr_utility.set_location('hr_entry.ins_3p_entry_values', 7);
3722: end if;
3723: begin
3724:
3725: for peev in get_b_eevs(p_element_entry_id, p_session_date) loop
3761: exception
3762: when no_data_found then
3763: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3764: hr_utility.set_message_token('PROCEDURE',
3765: 'hr_entry.ins_3p_entry_values');
3766: hr_utility.set_message_token('STEP','7');
3767: hr_utility.raise_error;
3768: end;
3769:
3771: -- insert run result values which do not have to be converted.
3772: else
3773: --
3774: if g_debug then
3775: hr_utility.set_location('hr_entry.ins_3p_entry_values', 8);
3776: end if;
3777: begin
3778: --
3779: insert into pay_run_result_values
3792: exception
3793: when no_data_found then
3794: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3795: hr_utility.set_message_token('PROCEDURE',
3796: 'hr_entry.ins_3p_entry_values');
3797: hr_utility.set_message_token('STEP','8');
3798: hr_utility.raise_error;
3799: end;
3800: --
3813: p_element_link_id number,
3814: p_element_entry_id number,
3815: p_session_date date,
3816: p_num_entry_values number,
3817: p_input_value_id_tbl hr_entry.number_table,
3818: p_entry_value_tbl hr_entry.varchar2_table
3819: ) is
3820: --
3821: -- Local Variables
3814: p_element_entry_id number,
3815: p_session_date date,
3816: p_num_entry_values number,
3817: p_input_value_id_tbl hr_entry.number_table,
3818: p_entry_value_tbl hr_entry.varchar2_table
3819: ) is
3820: --
3821: -- Local Variables
3822: v_num_entry_values number;
3819: ) is
3820: --
3821: -- Local Variables
3822: v_num_entry_values number;
3823: v_input_value_id_tbl hr_entry.number_table;
3824: v_entry_value_tbl hr_entry.varchar2_table;
3825: --
3826: begin
3827: --
3820: --
3821: -- Local Variables
3822: v_num_entry_values number;
3823: v_input_value_id_tbl hr_entry.number_table;
3824: v_entry_value_tbl hr_entry.varchar2_table;
3825: --
3826: begin
3827: --
3828: v_num_entry_values := p_num_entry_values;
3828: v_num_entry_values := p_num_entry_values;
3829: v_input_value_id_tbl := p_input_value_id_tbl;
3830: v_entry_value_tbl := p_entry_value_tbl;
3831: --
3832: -- hr_entry_api.conv_table_to_table
3833: -- ('DB',
3834: -- p_session_date,
3835: -- null,
3836: -- p_element_link_id,
3837: -- v_num_entry_values,
3838: -- v_input_value_id_tbl,
3839: -- v_entry_value_tbl);
3840: --
3841: hr_entry.ins_3p_ent_values
3842: (p_element_link_id,
3843: p_element_entry_id,
3844: p_session_date,
3845: v_num_entry_values,
3889: ) is
3890: --
3891: -- Local variables
3892: v_num_entry_values number;
3893: v_input_value_id_tbl hr_entry.number_table;
3894: v_entry_value_tbl hr_entry.varchar2_table;
3895: --
3896: begin
3897: --
3890: --
3891: -- Local variables
3892: v_num_entry_values number;
3893: v_input_value_id_tbl hr_entry.number_table;
3894: v_entry_value_tbl hr_entry.varchar2_table;
3895: --
3896: begin
3897: --
3898: -- Convert entry value details ie. INPUT_VALUE_ID and SCREEN_ENTRY_VALUE
3898: -- Convert entry value details ie. INPUT_VALUE_ID and SCREEN_ENTRY_VALUE
3899: -- into two tables to be passed into the overloaded version of
3900: -- ins_3p_entry_values. The overloaded version is capable of handling
3901: -- unlimited numbers of entry values.
3902: hr_entry_api.conv_entry_values_to_table
3903: ('DB',
3904: null,
3905: p_element_link_id,
3906: p_session_date,
3940: v_num_entry_values,
3941: v_input_value_id_tbl,
3942: v_entry_value_tbl);
3943: --
3944: hr_entry.ins_3p_ent_values
3945: (p_element_link_id,
3946: p_element_entry_id,
3947: p_session_date,
3948: v_num_entry_values,
3956: -- NAME delete_covered_dependants
3957: --
3958: -- DESCRIPTION Deals with calls to update the covered dependents of an
3959: -- Element entry.
3960: -- Called by : hr_entry.del_3p_entry_values
3961: -- : hrentmnt.validate_adjust_entry
3962: --
3963: ----------------------------------------------------------------------------
3964:
4022: end delete_covered_dependants;
4023:
4024: ----------------------------------------------------------------------------
4025: --
4026: -- NAME hr_entry.delete_beneficiaries
4027: --
4028: -- DESCRIPTION deals with calls to update the beneficiaries of a given
4029: -- element entry.
4030: -- Called by :- hr_entry.del_3p_entry_values
4026: -- NAME hr_entry.delete_beneficiaries
4027: --
4028: -- DESCRIPTION deals with calls to update the beneficiaries of a given
4029: -- element entry.
4030: -- Called by :- hr_entry.del_3p_entry_values
4031: -- hrentmnt.validate_adjust_entry
4032: --
4033: ----------------------------------------------------------------------------
4034:
4096: end delete_beneficiaries;
4097: -------------------------
4098:
4099: -- NAME
4100: -- hr_entry.del_3p_entry_values
4101: --
4102: -- DESCRIPTION
4103: -- This procedure is used for third party deletes from:
4104: -- PAY_ELEMENT_ENTRIES_F (If an abscence, or DT functions are being used).
4221: begin
4222: g_debug := hr_utility.debug_enabled;
4223: --
4224: if g_debug then
4225: hr_utility.set_location('hr_entry.del_3p_entry_values', 1);
4226: end if;
4227: --
4228: -- Fix for 1904110.
4229: -- The delete mode is always set to the parameter passed in because
4227: --
4228: -- Fix for 1904110.
4229: -- The delete mode is always set to the parameter passed in because
4230: -- the delete of the entry values should always use the same mode
4231: -- as the entry. See hr_entry_api.del_ele_entry_param_val
4232: --
4233: v_dt_delete_mode := p_dt_delete_mode;
4234: --
4235: if (v_dt_delete_mode = 'ZAP'
4237: --
4238: -- Delete rows in child tables which would be orphaned by the entry deletion
4239: --
4240: -- Bug fix 519738 - call procedure with new list of parameters, p_start_date defaults to null
4241: hr_entry.delete_beneficiaries(
4242: p_element_entry_id => p_element_entry_id,
4243: p_end_date => p_session_date,
4244: p_validation_start_date => p_validation_start_date);
4245: -- Bug fix 519738 - call procedure with new list of parameters, p_start_date defaults to NULL
4242: p_element_entry_id => p_element_entry_id,
4243: p_end_date => p_session_date,
4244: p_validation_start_date => p_validation_start_date);
4245: -- Bug fix 519738 - call procedure with new list of parameters, p_start_date defaults to NULL
4246: hr_entry.delete_covered_dependants(
4247: p_element_entry_id => p_element_entry_id,
4248: p_end_date => p_session_date,
4249: p_validation_start_date => p_validation_start_date);
4250: --
4249: p_validation_start_date => p_validation_start_date);
4250: --
4251: -- Find out if the element is processable in a payroll run
4252: --
4253: if hr_entry.entry_process_in_run(p_element_type_id, p_session_date) then
4254: v_process_in_run_flag := 'Y';
4255: else
4256: v_process_in_run_flag := 'N';
4257: end if;
4281: -- Step 2:
4282: --
4283: if v_process_in_run_flag = 'Y' then
4284: if g_debug then
4285: hr_utility.set_location('hr_entry.del_3p_entry_values', 2);
4286: end if;
4287: begin
4288: delete from pay_run_result_values rrv
4289: where rrv.run_result_id =
4300: -- DT_DELETE_MODE: ZAP
4301: -- Step 3:
4302: --
4303: if g_debug then
4304: hr_utility.set_location('hr_entry.del_3p_entry_values', 3);
4305: end if;
4306: begin
4307: delete from pay_run_results rr
4308: where rr.element_type_id + 0 = p_element_type_id
4322: --
4323: if (p_creator_type = 'F'
4324: or p_element_entry_id is not null) then
4325: if g_debug then
4326: hr_utility.set_location('hr_entry.del_3p_entry_values', 4);
4327: end if;
4328: begin
4329: delete from pay_element_entry_values_f eev
4330: where eev.element_entry_id = p_element_entry_id;
4339: -- 2) This is specific to absences.
4340: -- Step 5:
4341: --
4342: if g_debug then
4343: hr_utility.set_location('hr_entry.del_3p_entry_values', 5);
4344: end if;
4345: begin
4346: delete from pay_element_entry_values_f eev
4347: where eev.element_entry_id in
4365: -- DT_DELETE_MODE: ZAP
4366: -- Step 6:
4367: --
4368: if g_debug then
4369: hr_utility.set_location('hr_entry.del_3p_entry_values', 6);
4370: end if;
4371: begin
4372: delete from pay_element_entries_f ee
4373: where ee.element_entry_id = p_element_entry_id;
4380: -- As the entry is an absence then delete all entries for the absence
4381: -- attendence.
4382: --
4383: if g_debug then
4384: hr_utility.set_location('hr_entry.del_3p_entry_values', 7);
4385: end if;
4386: begin
4387: delete from pay_element_entries_f ee
4388: where ee.creator_type = p_creator_type
4399: -- DT_DELETE_MODE: DELETE
4400: -- Step 8:
4401: --
4402: if g_debug then
4403: hr_utility.set_location('hr_entry.del_3p_entry_values', 8);
4404: end if;
4405: begin
4406: update pay_element_entry_values_f eev
4407: set eev.effective_end_date = p_session_date
4410: and eev.effective_end_date;
4411: exception
4412: when NO_DATA_FOUND then
4413: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
4414: hr_utility.set_message_token('PROCEDURE','hr_entry.del_3p_entry_values');
4415: hr_utility.set_message_token('STEP','8');
4416: hr_utility.raise_error;
4417: end;
4418: --
4416: hr_utility.raise_error;
4417: end;
4418: --
4419: if g_debug then
4420: hr_utility.set_location('hr_entry.del_3p_entry_values', 9);
4421: end if;
4422: begin
4423: delete from pay_element_entry_values_f eev
4424: where eev.element_entry_id = p_element_entry_id
4434: -- DT_DELETE_MODE: DELETE_NEXT_CHANGE/FUTURE_CHANGE
4435: -- Step 9:
4436: --
4437: if g_debug then
4438: hr_utility.set_location('hr_entry.del_3p_entry_values', 9);
4439: end if;
4440: begin
4441: delete from pay_element_entry_values_f eev
4442: where eev.element_entry_id = p_element_entry_id
4450: -- Update the current effective_end_date on the entry values rows.
4451: -- DT_DELETE_MODE: DELETE_NEXT_CHANGE/FUTURE_CHANGE
4452: -- Step 10:
4453: if g_debug then
4454: hr_utility.set_location('hr_entry.del_3p_entry_values', 10);
4455: end if;
4456: begin
4457: update pay_element_entry_values_f eev
4458: -- bug 384948. Changed set clouse to supply effective_end_date of the
4466: and eev.effective_end_date;
4467: exception
4468: when NO_DATA_FOUND then
4469: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
4470: hr_utility.set_message_token('PROCEDURE','hr_entry.del_3p_entry_values');
4471: hr_utility.set_message_token('STEP','10');
4472: hr_utility.raise_error;
4473: end;
4474: --
4496: p_validation_end_date date,
4497: p_session_date date,
4498: p_dt_update_mode varchar2,
4499: p_num_entry_values number,
4500: p_input_value_id_tbl hr_entry.number_table,
4501: p_entry_value_tbl hr_entry.varchar2_table
4502: ) is
4503: --
4504: -- Local Variables
4497: p_session_date date,
4498: p_dt_update_mode varchar2,
4499: p_num_entry_values number,
4500: p_input_value_id_tbl hr_entry.number_table,
4501: p_entry_value_tbl hr_entry.varchar2_table
4502: ) is
4503: --
4504: -- Local Variables
4505: v_return_entry_id number;
4505: v_return_entry_id number;
4506: --
4507: begin
4508: if g_debug then
4509: hr_utility.set_location('hr_entry.upd_3p_entry_values', 1);
4510: end if;
4511: hr_general.assert_condition (p_processing_type is not null
4512: and p_dt_update_mode is not null
4513: and p_element_entry_id is not null
4540: --
4541: -- NB. mandatory checks are not applied to adjustments.
4542: if not p_entry_type in ('A','R') then
4543: --
4544: hr_entry.chk_mandatory_entry_values
4545: (p_element_link_id,
4546: p_session_date,
4547: p_num_entry_values,
4548: p_input_value_id_tbl,
4569: exception
4570: when NO_DATA_FOUND then
4571: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
4572: hr_utility.set_message_token('PROCEDURE',
4573: 'hr_entry.upd_3p_entry_values');
4574: hr_utility.set_message_token('STEP','1');
4575: hr_utility.raise_error;
4576: end;
4577: --
4589: if (p_processing_type = 'N' and
4590: -- p_creator_type <> 'A' and
4591: p_entry_type = 'E' and
4592: p_input_currency_code = p_output_currency_code and
4593: hr_entry.entry_process_in_run(p_element_type_id, p_session_date)) then
4594: if g_debug then
4595: hr_utility.set_location('hr_entry.upd_3p_entry_values', 3);
4596: end if;
4597: begin
4591: p_entry_type = 'E' and
4592: p_input_currency_code = p_output_currency_code and
4593: hr_entry.entry_process_in_run(p_element_type_id, p_session_date)) then
4594: if g_debug then
4595: hr_utility.set_location('hr_entry.upd_3p_entry_values', 3);
4596: end if;
4597: begin
4598: UPDATE PAY_RUN_RESULT_VALUES PRRV1
4599: SET PRRV1.RESULT_VALUE =
4624: p_dt_update_mode = 'UPDATE_CHANGE_INSERT' or
4625: p_dt_update_mode = 'UPDATE_OVERRIDE') and
4626: p_processing_type = 'R') then
4627: if g_debug then
4628: hr_utility.set_location('hr_entry.upd_3p_entry_values', 4);
4629: end if;
4630: begin
4631: UPDATE PAY_ELEMENT_ENTRY_VALUES_F PEEV1
4632: SET PEEV1.EFFECTIVE_END_DATE = p_validation_start_date - 1
4635: BETWEEN PEEV1.EFFECTIVE_START_DATE AND PEEV1.EFFECTIVE_END_DATE;
4636: exception
4637: when NO_DATA_FOUND then
4638: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
4639: hr_utility.set_message_token('PROCEDURE','hr_entry.upd_3p_entry_values');
4640: hr_utility.set_message_token('STEP','4');
4641: hr_utility.raise_error;
4642: end;
4643: --
4646: -- Step 4:
4647: --
4648: if p_dt_update_mode = 'UPDATE_OVERRIDE' then
4649: if g_debug then
4650: hr_utility.set_location('hr_entry.upd_3p_entry_values', 5);
4651: end if;
4652: begin
4653: DELETE FROM PAY_ELEMENT_ENTRY_VALUES_F PEEV1
4654: WHERE PEEV1.ELEMENT_ENTRY_ID = p_element_entry_id
4657: end if;
4658: --
4659: -- Insert new updated element entry rows.
4660: --
4661: hr_entry.ins_3p_entry_values
4662: (p_element_link_id,
4663: p_element_entry_id,
4664: p_session_date,
4665: p_num_entry_values,
4670: --
4671: end upd_3p_ent_values;
4672: --
4673: -- NAME
4674: -- hr_entry.upd_3p_entry_values
4675: --
4676: -- DESCRIPTION
4677: -- This procedure is used for third party updates into:
4678: -- PAY_ELEMENT_ENTRY_VALUES_F
4698: p_validation_end_date date,
4699: p_session_date date,
4700: p_dt_update_mode varchar2,
4701: p_num_entry_values number,
4702: p_input_value_id_tbl hr_entry.number_table,
4703: p_entry_value_tbl hr_entry.varchar2_table
4704: ) is
4705: --
4706: -- Local Variables
4699: p_session_date date,
4700: p_dt_update_mode varchar2,
4701: p_num_entry_values number,
4702: p_input_value_id_tbl hr_entry.number_table,
4703: p_entry_value_tbl hr_entry.varchar2_table
4704: ) is
4705: --
4706: -- Local Variables
4707: v_return_entry_id number;
4705: --
4706: -- Local Variables
4707: v_return_entry_id number;
4708: v_num_entry_values number;
4709: v_input_value_id_tbl hr_entry.number_table;
4710: v_entry_value_tbl hr_entry.varchar2_table;
4711: --
4712: begin
4713: --
4706: -- Local Variables
4707: v_return_entry_id number;
4708: v_num_entry_values number;
4709: v_input_value_id_tbl hr_entry.number_table;
4710: v_entry_value_tbl hr_entry.varchar2_table;
4711: --
4712: begin
4713: --
4714: v_num_entry_values := p_num_entry_values;
4714: v_num_entry_values := p_num_entry_values;
4715: v_input_value_id_tbl := p_input_value_id_tbl;
4716: v_entry_value_tbl := p_entry_value_tbl;
4717: --
4718: hr_entry_api.conv_table_to_table
4719: ('DB',
4720: p_session_date,
4721: p_element_entry_id,
4722: null,
4726: p_entry_type,
4727: v_input_value_id_tbl,
4728: v_entry_value_tbl);
4729: --
4730: hr_entry.upd_3p_ent_values
4731: (p_element_entry_id,
4732: p_element_type_id,
4733: p_element_link_id,
4734: p_cost_allocation_keyflex_id,
4749: --
4750: end upd_3p_entry_values;
4751: --
4752: -- NAME
4753: -- hr_entry.upd_3p_entry_values
4754: --
4755: -- DESCRIPTION
4756: -- This procedure is used for third party updates into:
4757: -- PAY_ELEMENT_ENTRY_VALUES_F
4810: ) is
4811: --
4812: -- Local variables
4813: v_num_entry_values number;
4814: v_input_value_id_tbl hr_entry.number_table;
4815: v_entry_value_tbl hr_entry.varchar2_table;
4816: --
4817: begin
4818: g_debug := hr_utility.debug_enabled;
4811: --
4812: -- Local variables
4813: v_num_entry_values number;
4814: v_input_value_id_tbl hr_entry.number_table;
4815: v_entry_value_tbl hr_entry.varchar2_table;
4816: --
4817: begin
4818: g_debug := hr_utility.debug_enabled;
4819: --
4817: begin
4818: g_debug := hr_utility.debug_enabled;
4819: --
4820: if g_debug then
4821: hr_utility.set_location('hr_entry.upd_3p_entry_values',100);
4822: end if;
4823: --
4824: -- Convert entry value details ie. INPUT_VALUE_ID and SCREEN_ENTRY_VALUE
4825: -- into two tables to be passed into the overloaded version of
4824: -- Convert entry value details ie. INPUT_VALUE_ID and SCREEN_ENTRY_VALUE
4825: -- into two tables to be passed into the overloaded version of
4826: -- upd_3p_entry_values. The overloaded version is capable of handling
4827: -- unlimited numbers of entry values.
4828: hr_entry_api.conv_entry_values_to_table
4829: ('DB',
4830: p_element_entry_id,
4831: null,
4832: p_session_date,
4867: v_input_value_id_tbl,
4868: v_entry_value_tbl);
4869: --
4870: if g_debug then
4871: hr_utility.set_location('hr_entry.upd_3p_entry_values',105);
4872: end if;
4873: --
4874: hr_entry.upd_3p_ent_values
4875: (p_element_entry_id,
4870: if g_debug then
4871: hr_utility.set_location('hr_entry.upd_3p_entry_values',105);
4872: end if;
4873: --
4874: hr_entry.upd_3p_ent_values
4875: (p_element_entry_id,
4876: p_element_type_id,
4877: p_element_link_id,
4878: p_cost_allocation_keyflex_id,
4893: --
4894: end upd_3p_entry_values;
4895: --
4896: -- NAME
4897: -- hr_entry.trigger_workload_shifting
4898: --
4899: -- DESCRIPTION
4900: -- This procedure is used for triggering workload shifting.
4901: --
4988: --
4989: end trigger_workload_shifting;
4990: --
4991: -- NAME
4992: -- hr_entry.check_format
4993: --
4994: -- DESCRIPTION
4995: -- Makes sure that the entry value is correct for the UOM and also convert the
4996: -- screen value into the database value ie. internal format.
5027: begin
5028: g_debug := hr_utility.debug_enabled;
5029: --
5030: if g_debug then
5031: hr_utility.set_location('hr_entry.check_format',5);
5032: end if;
5033: --
5034: -- Get uom , min / max and hot default details for the entry value
5035: -- being validated.
5081: exception
5082: when no_data_found then
5083: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
5084: hr_utility.set_message_token('PROCEDURE',
5085: 'hr_entry.check_format');
5086: hr_utility.set_message_token('STEP','1');
5087: hr_utility.raise_error;
5088: end;
5089: --
5087: hr_utility.raise_error;
5088: end;
5089: --
5090: if g_debug then
5091: hr_utility.set_location('hr_entry.check_format',10);
5092: end if;
5093: --
5094: IF ((v_uom = 'M') AND (v_input_currency_code IS NULL)) THEN
5095: hr_utility.set_message (801,'HR_51106_ELEMENT_CURR_REQ');
5129: v_checkformat_error := true;
5130: end;
5131: --
5132: if g_debug then
5133: hr_utility.set_location('hr_entry.check_format',10);
5134: end if;
5135: --
5136: -- Value is not correct for unit of measure
5137: if (v_checkformat_error) then
5146: --
5147: exception
5148: when no_data_found then
5149: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
5150: hr_utility.set_message_token('PROCEDURE', 'hr_entry.check_format');
5151: hr_utility.set_message_token('STEP', '2');
5152: hr_utility.raise_error;
5153: end;
5154: --
5158: --
5159: end if;
5160: --
5161: if g_debug then
5162: hr_utility.set_location('hr_entry.check_format',15);
5163: end if;
5164: --
5165: -- Minimum / maximum conditions have been broken by value
5166: if p_min_max_failure = 'F' then
5165: -- Minimum / maximum conditions have been broken by value
5166: if p_min_max_failure = 'F' then
5167: --
5168: if g_debug then
5169: hr_utility.set_location('hr_entry.check_format',20);
5170: end if;
5171: --
5172: -- If minimum value was specified, translate into screen format for use
5173: -- in error meessages
5175: p_minimum_value := v_formatted_min_value;
5176: end if;
5177: --
5178: if g_debug then
5179: hr_utility.set_location('hr_entry.check_format',25);
5180: end if;
5181: --
5182: -- If maximum value was specified, translate into screen format for use
5183: -- in error meessages
5189: --
5190: end check_format;
5191: --
5192: -- NAME
5193: -- hr_entry.maintain_cost_keyflex
5194: --
5195: -- DESCRIPTION
5196: --
5197: function maintain_cost_keyflex(
5296: --
5297: -- Need to check for a partial value.
5298: --
5299: if g_debug then
5300: hr_utility.set_location('hr_entry.maintain_cost_keyflex', 1);
5301: end if;
5302: --
5303: segment(1) := p_segment1;
5304: segment(2) := p_segment2;
5345: end if;
5346: end loop;
5347: --
5348: if g_debug then
5349: hr_utility.set_location('hr_entry.maintain_cost_keyflex', 2);
5350: end if;
5351: --
5352: sql_curs := dbms_sql.open_cursor;
5353: --
5364: end loop;
5365: dbms_sql.define_column(sql_curs, 1, l_cost_allocation_keyflex_id);
5366: --
5367: if g_debug then
5368: hr_utility.set_location('hr_entry.maintain_cost_keyflex', 3);
5369: end if;
5370: --
5371: rows_processed := dbms_sql.execute(sql_curs);
5372: --
5370: --
5371: rows_processed := dbms_sql.execute(sql_curs);
5372: --
5373: if g_debug then
5374: hr_utility.set_location('hr_entry.maintain_cost_keyflex ', 4);
5375: end if;
5376: --
5377: if dbms_sql.fetch_rows(sql_curs) > 0 then
5378: --
5376: --
5377: if dbms_sql.fetch_rows(sql_curs) > 0 then
5378: --
5379: if g_debug then
5380: hr_utility.set_location('hr_entry.maintain_cost_keyflex', 5);
5381: end if;
5382: dbms_sql.column_value(sql_curs, 1, l_cost_allocation_keyflex_id);
5383: --
5384: if (l_cost_allocation_keyflex_id is null)
5383: --
5384: if (l_cost_allocation_keyflex_id is null)
5385: then
5386: if g_debug then
5387: hr_utility.set_location('hr_entry.maintain_cost_keyflex', 6);
5388: end if;
5389: l_cost_allocation_keyflex_id := -1;
5390: end if;
5391: else
5389: l_cost_allocation_keyflex_id := -1;
5390: end if;
5391: else
5392: if g_debug then
5393: hr_utility.set_location('hr_entry.maintain_cost_keyflex', 7);
5394: end if;
5395: l_cost_allocation_keyflex_id := -1;
5396: end if;
5397: --
5419: and dynamic_inserts_allowed_flag = 'Y';
5420:
5421: exception
5422: when no_data_found then
5423: hr_utility.set_location('hr_entry.maintain_cost_keyflex', 7);
5424: hr_utility.set_message(801, 'PAY_34809_CANT_INS_INTO_CSTKFF');
5425: hr_utility.raise_error;
5426: end;
5427:
5428: --
5429: -- Select the next sequence value for the cost allocation keyflex.
5430: --
5431: if g_debug then
5432: hr_utility.set_location('hr_entry.maintain_cost_keyflex', 8);
5433: end if;
5434: begin
5435: select pay_cost_allocation_keyflex_s.nextval
5436: into l_cost_allocation_keyflex_id
5438: exception
5439: when NO_DATA_FOUND then
5440: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
5441: hr_utility.set_message_token('PROCEDURE',
5442: 'hr_entry.maintain_cost_keyflex');
5443: hr_utility.set_message_token('STEP','5');
5444: hr_utility.raise_error;
5445: end;
5446: --
5448: --
5449: if p_concatenated_segments is null then
5450: --
5451: if g_debug then
5452: hr_utility.set_location('hr_entry.maintain_cost_keyflex', 15);
5453: end if;
5454: -- get delimiter
5455: l_delimiter := fnd_flex_ext.get_delimiter
5456: ('PAY'
5458: ,p_cost_keyflex_structure
5459: );
5460: --
5461: if g_debug then
5462: hr_utility.set_location('hr_entry.maintain_cost_keyflex', 20);
5463: end if;
5464: --
5465: first_seg := true;
5466:
5485: l_concatenated_segments := substr(l_concat_string, 1, 240);
5486: else
5487: --
5488: if g_debug then
5489: hr_utility.set_location('hr_entry.maintain_cost_keyflex', 25);
5490: end if;
5491: --
5492: l_concatenated_segments := p_concatenated_segments;
5493: end if;
5517: ) = FALSE
5518: then
5519: --
5520: if g_debug then
5521: hr_utility.set_location('hr_entry.maintain_cost_keyflex', 27);
5522: end if;
5523: --
5524: -- Handle error raised to create a nice error message!
5525: --
5527: FND_FLEX_KEYVAL.encoded_error_message);
5528: fnd_message.raise_error;
5529: else
5530: if g_debug then
5531: hr_utility.set_location('hr_entry.maintain_cost_keyflex', 28);
5532: end if;
5533:
5534: end if;
5535: end if;
5536: --
5537: -- Insert the new row.
5538: --
5539: if g_debug then
5540: hr_utility.set_location('hr_entry.maintain_cost_keyflex', 30);
5541: end if;
5542: begin
5543: insert into pay_cost_allocation_keyflex
5544: (cost_allocation_keyflex_id
5633: end maintain_cost_keyflex;
5634: --
5635: --
5636: -- NAME
5637: -- hr_entry.return_entry_display_status
5638: --
5639: -- DESCRIPTION
5640: -- Used by PAYEEMEE/PAYWSMEE to return current entry statuses during a
5641: -- post-query.
5662: --
5663: -- We need to get the standard assignment payroll and time period details
5664: --
5665: if g_debug then
5666: hr_utility.set_location('hr_entry.return_entry_display_status', 1);
5667: end if;
5668: begin
5669: select a.payroll_id,
5670: t.start_date,
5690: --
5691: -- 1: lets set the additional value.
5692: --
5693: if g_debug then
5694: hr_utility.set_location('hr_entry.return_entry_display_status', 5);
5695: end if;
5696: if (p_entry_type = 'D') then
5697: p_additional := 'Y';
5698: else
5701: --
5702: -- 2: lets see if current entry has been processed.
5703: --
5704: if g_debug then
5705: hr_utility.set_location('hr_entry.return_entry_display_status', 10);
5706: end if;
5707: if (l_payroll_id is not null) then
5708: begin
5709: select max(ppr.run_result_id)
5733: -- If the run result was processed and is an override then it cannot itself
5734: -- be overridden or adjusted.
5735: --
5736: if g_debug then
5737: hr_utility.set_location('hr_entry.return_entry_display_status', 15);
5738: end if;
5739: if (l_run_result_id is not null and
5740: p_entry_type = 'S') then
5741: p_processed := 'Y';
5750: -- As the entry was processed we need to check if it was overridden or
5751: -- Adjusted.
5752: --
5753: if g_debug then
5754: hr_utility.set_location('hr_entry.return_entry_display_status', 20);
5755: end if;
5756: begin
5757: select prr.status
5758: into l_run_result_status
5766: -- If the l_run_result_status is NOT in 'PA', 'R', 'O' then set the
5767: -- l_override, l_adjusted to 'N' and to don't do any further checks.
5768: --
5769: if g_debug then
5770: hr_utility.set_location('hr_entry.return_entry_display_status', 25);
5771: end if;
5772: if (l_run_result_status <> 'PA' or
5773: l_run_result_status <> 'R' or
5774: l_run_result_status <> 'O') then
5786: --
5787: -- 3: Check to see if the entry is overridden.
5788: --
5789: if g_debug then
5790: hr_utility.set_location('hr_entry.return_entry_display_status', 30);
5791: end if;
5792: begin
5793: select 'Y'
5794: into l_overridden
5809: -- 4: If the entry is NOT overridden then check to see if has been adjusted.
5810: --
5811: if (l_overridden = 'N') then
5812: if g_debug then
5813: hr_utility.set_location('hr_entry.return_entry_display_status', 35);
5814: end if;
5815: begin
5816: select 'Y'
5817: into p_adjustment
5833: --
5834: end return_entry_display_status;
5835: --
5836: -- NAME
5837: -- hr_entry.chk_creator_type
5838: --
5839: -- DESCRIPTION
5840: -- Used by PAYEEMEE/PAYWSMEE to restrict DT operations according to the
5841: -- creator type ie. cannot update a balance adjustment etc ...
5879: -- B: Balance Adjustment
5880: -- SP: Salary Admin
5881: --
5882: if g_debug then
5883: hr_utility.set_location('hr_entry.chk_creator_type', 5);
5884: end if;
5885: begin
5886: select h.meaning
5887: into l_creator_meaning
5924: (p_dml_operation = 'UPDATE' and
5925: l_dt_update_mode = 'UPDATE_OVERRIDE')))) then
5926: --
5927: if g_debug then
5928: hr_utility.set_location('hr_entry.chk_creator_type', 10);
5929: end if;
5930: begin
5931: select 'Y'
5932: into l_error_flag
5951: end if;
5952: --
5953: end chk_creator_type;
5954: --
5955: end hr_entry;