[Home] [Help]
PACKAGE BODY: APPS.PAY_ELEMENT_ENTRY_API
Source
1 PACKAGE BODY pay_element_entry_api AS
2 /* $Header: pyeleapi.pkb 120.5.12010000.2 2008/10/01 06:13:22 ankagarw ship $ */
3 --
4 -- Package Variables
5 --
6 g_debug boolean := hr_utility.debug_enabled;
7 g_package varchar2(33) := 'pay_element_entry_api.';
8 --
9 -- ----------------------------------------------------------------------------
10 -- |-------------------------< create_element_entry >-------------------------|
11 -- ----------------------------------------------------------------------------
12 --
13 PROCEDURE create_element_entry
14 (p_validate in boolean default false
15 ,p_effective_date in date
16 ,p_business_group_id in number
17 ,p_original_entry_id in number default null
18 ,p_assignment_id in number
19 ,p_element_link_id in number
20 ,p_entry_type in varchar2
21 ,p_creator_type in varchar2 default 'F'
22 ,p_cost_allocation_keyflex_id in number default null
23 ,p_updating_action_id in number default null
24 ,p_updating_action_type in varchar2 default null
25 ,p_comment_id in number default null
26 ,p_reason in varchar2 default null
27 ,p_target_entry_id in number default null
28 ,p_subpriority in number default null
29 ,p_date_earned in date default null
30 ,p_personal_payment_method_id in number default null
31 ,p_attribute_category in varchar2 default null
32 ,p_attribute1 in varchar2 default null
33 ,p_attribute2 in varchar2 default null
34 ,p_attribute3 in varchar2 default null
35 ,p_attribute4 in varchar2 default null
36 ,p_attribute5 in varchar2 default null
37 ,p_attribute6 in varchar2 default null
38 ,p_attribute7 in varchar2 default null
39 ,p_attribute8 in varchar2 default null
40 ,p_attribute9 in varchar2 default null
41 ,p_attribute10 in varchar2 default null
42 ,p_attribute11 in varchar2 default null
43 ,p_attribute12 in varchar2 default null
44 ,p_attribute13 in varchar2 default null
45 ,p_attribute14 in varchar2 default null
46 ,p_attribute15 in varchar2 default null
47 ,p_attribute16 in varchar2 default null
48 ,p_attribute17 in varchar2 default null
49 ,p_attribute18 in varchar2 default null
50 ,p_attribute19 in varchar2 default null
51 ,p_attribute20 in varchar2 default null
52 ,p_input_value_id1 in number default null
53 ,p_input_value_id2 in number default null
54 ,p_input_value_id3 in number default null
55 ,p_input_value_id4 in number default null
56 ,p_input_value_id5 in number default null
57 ,p_input_value_id6 in number default null
58 ,p_input_value_id7 in number default null
59 ,p_input_value_id8 in number default null
60 ,p_input_value_id9 in number default null
61 ,p_input_value_id10 in number default null
62 ,p_input_value_id11 in number default null
63 ,p_input_value_id12 in number default null
64 ,p_input_value_id13 in number default null
65 ,p_input_value_id14 in number default null
66 ,p_input_value_id15 in number default null
67 ,p_entry_value1 in varchar2 default null
68 ,p_entry_value2 in varchar2 default null
69 ,p_entry_value3 in varchar2 default null
70 ,p_entry_value4 in varchar2 default null
71 ,p_entry_value5 in varchar2 default null
72 ,p_entry_value6 in varchar2 default null
73 ,p_entry_value7 in varchar2 default null
74 ,p_entry_value8 in varchar2 default null
75 ,p_entry_value9 in varchar2 default null
76 ,p_entry_value10 in varchar2 default null
77 ,p_entry_value11 in varchar2 default null
78 ,p_entry_value12 in varchar2 default null
79 ,p_entry_value13 in varchar2 default null
80 ,p_entry_value14 in varchar2 default null
81 ,p_entry_value15 in varchar2 default null
82 ,p_entry_information_category in varchar2 default null
83 ,p_entry_information1 in varchar2 default null
84 ,p_entry_information2 in varchar2 default null
85 ,p_entry_information3 in varchar2 default null
86 ,p_entry_information4 in varchar2 default null
87 ,p_entry_information5 in varchar2 default null
88 ,p_entry_information6 in varchar2 default null
89 ,p_entry_information7 in varchar2 default null
90 ,p_entry_information8 in varchar2 default null
91 ,p_entry_information9 in varchar2 default null
92 ,p_entry_information10 in varchar2 default null
93 ,p_entry_information11 in varchar2 default null
94 ,p_entry_information12 in varchar2 default null
95 ,p_entry_information13 in varchar2 default null
96 ,p_entry_information14 in varchar2 default null
97 ,p_entry_information15 in varchar2 default null
98 ,p_entry_information16 in varchar2 default null
99 ,p_entry_information17 in varchar2 default null
100 ,p_entry_information18 in varchar2 default null
101 ,p_entry_information19 in varchar2 default null
102 ,p_entry_information20 in varchar2 default null
103 ,p_entry_information21 in varchar2 default null
104 ,p_entry_information22 in varchar2 default null
105 ,p_entry_information23 in varchar2 default null
106 ,p_entry_information24 in varchar2 default null
107 ,p_entry_information25 in varchar2 default null
108 ,p_entry_information26 in varchar2 default null
109 ,p_entry_information27 in varchar2 default null
110 ,p_entry_information28 in varchar2 default null
111 ,p_entry_information29 in varchar2 default null
112 ,p_entry_information30 in varchar2 default null
113 ,p_override_user_ent_chk in varchar2 default 'N'
114 ,p_effective_start_date out nocopy date
115 ,p_effective_end_date out nocopy date
116 ,p_element_entry_id out nocopy number
117 ,p_object_version_number out nocopy number
118 ,p_create_warning out nocopy boolean
119 ) IS
120 --
121 -- Declare cursors and local variables
122 --
123 l_creator_type pay_element_entries_f.creator_type%TYPE;
124 l_creator_id pay_element_entries_f.creator_id%TYPE;
125 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
126 l_object_version_number pay_element_entries_f.object_version_number%TYPE;
127 l_effective_start_date pay_element_entries_f.effective_start_date%TYPE;
128 l_effective_end_date pay_element_entries_f.effective_end_date%TYPE;
129 l_process_in_run_flag pay_element_types_f.process_in_run_flag%TYPE;
130 l_closed_for_entry_flag pay_element_types_f.closed_for_entry_flag%TYPE;
131 l_adjustment_only_flag pay_element_types_f.adjustment_only_flag%TYPE; -- Bug 5872519
132 l_period_status per_time_periods.status%TYPE;
133 --
134 -- Enhancement 2793978
135 -- size of entry_value variables increased to deal with
136 -- screen format of entry values that use value sets
137 --
138 l_entry_value1 varchar2(240);
139 l_entry_value2 varchar2(240);
140 l_entry_value3 varchar2(240);
141 l_entry_value4 varchar2(240);
142 l_entry_value5 varchar2(240);
143 l_entry_value6 varchar2(240);
144 l_entry_value7 varchar2(240);
145 l_entry_value8 varchar2(240);
146 l_entry_value9 varchar2(240);
147 l_entry_value10 varchar2(240);
148 l_entry_value11 varchar2(240);
149 l_entry_value12 varchar2(240);
150 l_entry_value13 varchar2(240);
151 l_entry_value14 varchar2(240);
152 l_entry_value15 varchar2(240);
153 --
154 l_date_on_which_time_served_ok date;
155 l_date_on_which_old_enough date;
156 l_dummy varchar2(1);
157 l_create_warning boolean;
158 l_proc varchar2(72) := g_package||'create_element_entry';
159 l_element_name pay_element_types_f.element_name%TYPE;
160 l_legislation_code pay_element_types_f.legislation_code%TYPE;
161 -- bug 659393, added variables for storing all dates pased in and truncate them
162 l_effective_date date;
163 l_date_earned pay_element_entries_f.date_earned%TYPE;
164 --
165 -- Bugfix 2646060
166 -- l_costable_type needed to hold the costable_type of the element link
167 --
168 l_costable_type pay_element_links_f.costable_type%TYPE;
169 --
170 -- Bugfix 3079267
171 -- l_indirect_only_flag required to hold indirect_only_flag value of the
172 -- element link
173 --
174 l_indirect_only_flag pay_element_types_f.indirect_only_flag%TYPE;
175 --
176 CURSOR c_output_variables IS
177 SELECT ee.object_version_number
178 FROM pay_element_entries_f ee
179 WHERE l_element_entry_id = ee.element_entry_id
180 -- bug 675794, added date condition to select correct row
181 and l_effective_date between ee.effective_start_date
182 and ee.effective_end_date;
183 CURSOR c_assignment_details IS
184 SELECT ptp.status
185 FROM per_time_periods ptp,
186 per_assignments_f pas
187 WHERE pas.assignment_id = p_assignment_id
188 AND pas.payroll_id = ptp.payroll_id
189 AND l_effective_date BETWEEN ptp.start_date
190 AND ptp.end_date
191 AND l_effective_date BETWEEN pas.effective_start_date
192 AND pas.effective_end_date;
193 CURSOR c_entry_exists IS
194 SELECT /*+ LEADING(ee)
195 INDEX(ee pay_element_entries_f_n51) */
196 'X'
197 FROM pay_element_entries_f ee,
198 pay_element_types_f et,
199 pay_element_links_f el
200 WHERE el.element_link_id = ee.element_link_id
201 AND el.element_link_id = p_element_link_id
202 AND el.element_type_id = et.element_type_id
203 AND ee.assignment_id = p_assignment_id
204 AND l_effective_date BETWEEN ee.effective_start_date
205 AND ee.effective_end_date
206 AND l_effective_date BETWEEN el.effective_start_date
207 AND el.effective_end_date
208 AND l_effective_date BETWEEN et.effective_start_date
209 AND et.effective_end_date
210 AND et.multiple_entries_allowed_flag = 'N'
211 AND ee.entry_type = 'E';
212 CURSOR c_element_info IS
213 SELECT et.closed_for_entry_flag,
214 et.adjustment_only_flag, -- Bug 5872519
215 et.process_in_run_flag,
216 et.element_name,
217 et.legislation_code,
218 --
219 -- Bugfix 2646060
220 -- Retrieve the element_link costable_type
221 --
222 el.costable_type,
223 --
224 -- Bugfix 3079267
225 -- Retreive the indirect_only_flag
226 --
227 et.indirect_only_flag
228 FROM pay_element_types_f et,
229 pay_element_links_f el
230 WHERE el.element_link_id = p_element_link_id
231 AND el.element_type_id = et.element_type_id
232 AND l_effective_date BETWEEN el.effective_start_date
233 AND el.effective_end_date
234 AND l_effective_date BETWEEN et.effective_start_date
235 AND et.effective_end_date;
236 --
237 BEGIN
238 g_debug := hr_utility.debug_enabled;
239 if g_debug then
240 hr_utility.set_location('Entering:'|| l_proc, 50);
241 end if;
242 -- bug 659393, added variables for storing all dates pased in and truncate them
243 l_effective_date := trunc(p_effective_date);
244 l_effective_start_date := l_effective_date;
245 l_date_earned := trunc(p_date_earned);
246 --
247 -- Issue a savepoint.
248 --
249 SAVEPOINT create_element_entry;
250 if g_debug then
251 hr_utility.set_location(l_proc, 60);
252 end if;
253 --
254 -- Call Before Process User Hook
255 --
256 begin
257 pay_element_entry_bk1.create_element_entry_b
258 (p_effective_date => l_effective_date
259 ,p_business_group_id => p_business_group_id
260 ,p_original_entry_id => p_original_entry_id
261 ,p_assignment_id => p_assignment_id
262 ,p_element_link_id => p_element_link_id
263 ,p_entry_type => p_entry_type
264 ,p_creator_type => p_creator_type
265 ,p_cost_allocation_keyflex_id => p_cost_allocation_keyflex_id
266 ,p_updating_action_id => p_updating_action_id
267 ,p_updating_action_type => p_updating_action_type
268 ,p_comment_id => p_comment_id
269 ,p_reason => p_reason
270 ,p_target_entry_id => p_target_entry_id
271 ,p_subpriority => p_subpriority
272 ,p_date_earned => l_date_earned
273 ,p_personal_payment_method_id => p_personal_payment_method_id
274 ,p_attribute_category => p_attribute_category
275 ,p_attribute1 => p_attribute1
276 ,p_attribute2 => p_attribute2
277 ,p_attribute3 => p_attribute3
278 ,p_attribute4 => p_attribute4
279 ,p_attribute5 => p_attribute5
280 ,p_attribute6 => p_attribute6
281 ,p_attribute7 => p_attribute7
282 ,p_attribute8 => p_attribute8
283 ,p_attribute9 => p_attribute9
284 ,p_attribute10 => p_attribute10
285 ,p_attribute11 => p_attribute11
286 ,p_attribute12 => p_attribute12
287 ,p_attribute13 => p_attribute13
288 ,p_attribute14 => p_attribute14
289 ,p_attribute15 => p_attribute15
290 ,p_attribute16 => p_attribute16
291 ,p_attribute17 => p_attribute17
292 ,p_attribute18 => p_attribute18
293 ,p_attribute19 => p_attribute19
294 ,p_attribute20 => p_attribute20
295 ,p_input_value_id1 => p_input_value_id1
296 ,p_input_value_id2 => p_input_value_id2
297 ,p_input_value_id3 => p_input_value_id3
298 ,p_input_value_id4 => p_input_value_id4
299 ,p_input_value_id5 => p_input_value_id5
300 ,p_input_value_id6 => p_input_value_id6
301 ,p_input_value_id7 => p_input_value_id7
302 ,p_input_value_id8 => p_input_value_id8
303 ,p_input_value_id9 => p_input_value_id9
304 ,p_input_value_id10 => p_input_value_id10
305 ,p_input_value_id11 => p_input_value_id11
306 ,p_input_value_id12 => p_input_value_id12
307 ,p_input_value_id13 => p_input_value_id13
308 ,p_input_value_id14 => p_input_value_id14
309 ,p_input_value_id15 => p_input_value_id15
310 ,p_entry_value1 => p_entry_value1
311 ,p_entry_value2 => p_entry_value2
312 ,p_entry_value3 => p_entry_value3
313 ,p_entry_value4 => p_entry_value4
314 ,p_entry_value5 => p_entry_value5
315 ,p_entry_value6 => p_entry_value6
316 ,p_entry_value7 => p_entry_value7
317 ,p_entry_value8 => p_entry_value8
318 ,p_entry_value9 => p_entry_value9
319 ,p_entry_value10 => p_entry_value10
320 ,p_entry_value11 => p_entry_value11
321 ,p_entry_value12 => p_entry_value12
322 ,p_entry_value13 => p_entry_value13
323 ,p_entry_value14 => p_entry_value14
324 ,p_entry_value15 => p_entry_value15
325 ,p_entry_information_category => p_entry_information_category
326 ,p_entry_information1 => p_entry_information1
327 ,p_entry_information2 => p_entry_information2
328 ,p_entry_information3 => p_entry_information3
329 ,p_entry_information4 => p_entry_information4
330 ,p_entry_information5 => p_entry_information5
331 ,p_entry_information6 => p_entry_information6
332 ,p_entry_information7 => p_entry_information7
333 ,p_entry_information8 => p_entry_information8
334 ,p_entry_information9 => p_entry_information9
335 ,p_entry_information10 => p_entry_information10
336 ,p_entry_information11 => p_entry_information11
337 ,p_entry_information12 => p_entry_information12
338 ,p_entry_information13 => p_entry_information13
339 ,p_entry_information14 => p_entry_information14
340 ,p_entry_information15 => p_entry_information15
341 ,p_entry_information16 => p_entry_information16
342 ,p_entry_information17 => p_entry_information17
343 ,p_entry_information18 => p_entry_information18
344 ,p_entry_information19 => p_entry_information19
345 ,p_entry_information20 => p_entry_information20
346 ,p_entry_information21 => p_entry_information21
347 ,p_entry_information22 => p_entry_information22
348 ,p_entry_information23 => p_entry_information23
349 ,p_entry_information24 => p_entry_information24
350 ,p_entry_information25 => p_entry_information25
351 ,p_entry_information26 => p_entry_information26
352 ,p_entry_information27 => p_entry_information27
353 ,p_entry_information28 => p_entry_information28
354 ,p_entry_information29 => p_entry_information29
355 ,p_entry_information30 => p_entry_information30
356 ,p_override_user_ent_chk => p_override_user_ent_chk
357 );
358 exception
362 ,p_hook_type => 'BP'
359 when hr_api.cannot_find_prog_unit then
360 hr_api.cannot_find_prog_unit_error
361 (p_module_name => 'CREATE_ELEMENT_ENTRY'
363 );
364 end;
365 --
366 -- Validation in addition to Row Handlers
367 --
368 -- Create all elements as type 'F' with a NULL creator
369 l_creator_type := p_creator_type;
370 l_creator_id := NULL;
371 --
372 -- Process Logic
373 -- For lookups: The API is passed the lookup_code but this form of call to
374 -- the insert_element_entry requires the meaning - convert
375 -- Call existing element entry code
376 --
377 IF p_entry_type = 'E' THEN
378 if g_debug then
379 hr_utility.set_location(l_proc, 70);
380 end if;
381 OPEN c_entry_exists;
382 FETCH c_entry_exists
383 INTO l_dummy;
384 IF c_entry_exists%FOUND THEN
385 CLOSE c_entry_exists;
386 hr_utility.set_message(801,'HR_7455_PLK_ELE_ENTRY_EXISTS');
387 hr_utility.raise_error;
388 END IF;
389 CLOSE c_entry_exists;
390 END IF;
391 --
392 OPEN c_element_info;
393 FETCH c_element_info
394 INTO l_closed_for_entry_flag,
395 l_adjustment_only_flag, -- Bug 5872519
396 l_process_in_run_flag,
397 l_element_name,
398 l_legislation_code,
399 --
400 -- Bugfix 2646060
401 -- Fetch the element_link costable_type
402 --
403 l_costable_type,
404 --
405 -- Bugfix 3079267
406 -- Fetch the indirect_only_flag
407 --
408 l_indirect_only_flag;
409 IF c_element_info%NOTFOUND THEN
410 CLOSE c_element_info;
411 hr_utility.set_message(801,'HR_6132_ELE_ENTRY_LINK_MISSING');
412 hr_utility.raise_error;
413 END IF;
414 CLOSE c_element_info;
415 --
416 if g_debug then
417 hr_utility.set_location(l_proc, 100);
418 end if;
419 OPEN c_assignment_details;
420 FETCH c_assignment_details
421 INTO l_period_status;
422 --
423 -- bug 685930, commented this out as it is done by the api, and for non-recurring entries only.
424 --
425 /*
426 IF c_assignment_details%NOTFOUND THEN
427 CLOSE c_assignment_details;
428 hr_utility.set_message(801,'HR_6047_ELE_ENTRY_NO_PAYROLL');
429 hr_utility.raise_error;
430 END IF;
431 */
432 CLOSE c_assignment_details;
433
434 -- Bug 5872519
435 -- Added check for adjustment only elements.
436 -- Modified for 6599070, 6603455
437
438 IF ( l_adjustment_only_flag = 'Y' AND
439 ( p_entry_type not in ('A','R','B') OR
440 ( p_entry_type in ('A','R') and p_target_entry_id is null )
441 )
442 ) THEN
443
444 hr_utility.set_message(801,'HR_34810_ELE_ENTRY_ADJ_ONLY');
445 hr_utility.raise_error;
446
447 END IF;
448
449 IF l_closed_for_entry_flag = 'Y' THEN
450
451 hr_utility.set_message(801,'HR_6064_ELE_ENTRY_CLOSED_ELE');
452 hr_utility.raise_error;
453
454 -- Error will not be raised for VERTEX, Workers Compensation element with
455 -- Legislation code as US. Bug No 506819
456
457 ELSIF (l_period_status = 'C' AND l_process_in_run_flag = 'Y'
458 AND l_element_name not in ('US_TAX_VERTEX','VERTEX','Workers Compensation')
459 AND l_legislation_code <> 'US') THEN
460
461 hr_utility.set_message(801,'HR_6074_ELE_ENTRY_CLOSE_PERIOD');
462 hr_utility.raise_error;
463
464 --
465 -- Bugfix 2646060
466 -- Ensure that element_link is costable if cost_allocation_keyflex_id
467 -- is not null
468 --
469 ELSIF l_costable_type = 'N' and p_cost_allocation_keyflex_id IS NOT NULL THEN
470 --
471 hr_utility.set_message(801,'HR_7453_PLK_NON_COSTABLE_ELE');
472 hr_utility.set_warning;
473 --
474 --
475 -- Bugfix 3079267
476 -- Ensure we are not creating an entry for an element marked as
477 -- 'Indirect Only'
478 ELSIF l_indirect_only_flag = 'Y' THEN
479 --
480 -- Cannot directly create an entry for this element type
481 -- Create a warning
482 --
483 hr_utility.set_message(801,'HR_33297_EE_API_IND_ONLY_ELE');
484 hr_utility.set_warning;
485 --
486 END IF;
487
488 hr_entry.return_qualifying_conditions (p_assignment_id,
489 p_element_link_id,
490 l_effective_date,
491 l_date_on_which_time_served_ok,
492 l_date_on_which_old_enough );
493
494 IF l_effective_date < l_date_on_which_time_served_ok THEN
495 hr_utility.set_message(801, 'HR_ELE_ENTRY_QUAL_LOS');
496 hr_utility.set_warning;
497 ELSIF l_effective_date < l_date_on_which_old_enough THEN
498 hr_utility.set_message(801, 'HR_ELE_ENTRY_QUAL_AGE');
499 hr_utility.set_warning;
500 END IF;
501
502 IF p_input_value_id1 IS NOT NULL AND
503 p_entry_value1 IS NOT NULL THEN
504 if g_debug then
505 hr_utility.set_location(l_proc, 200);
506 end if;
507 l_entry_value1 := pay_ele_shd.convert_lookups(p_input_value_id1, p_entry_value1, p_effective_date);
508 END IF;
509 IF p_input_value_id2 IS NOT NULL AND
510 p_entry_value2 IS NOT NULL THEN
511 if g_debug then
515 END IF;
512 hr_utility.set_location(l_proc, 210);
513 end if;
514 l_entry_value2 := pay_ele_shd.convert_lookups(p_input_value_id2, p_entry_value2, p_effective_date);
516 IF p_input_value_id3 IS NOT NULL AND
517 p_entry_value3 IS NOT NULL THEN
518 if g_debug then
519 hr_utility.set_location(l_proc, 220);
520 end if;
521 l_entry_value3 := pay_ele_shd.convert_lookups(p_input_value_id3, p_entry_value3, p_effective_date);
522 END IF;
523 IF p_input_value_id4 IS NOT NULL AND
524 p_entry_value4 IS NOT NULL THEN
525 if g_debug then
526 hr_utility.set_location(l_proc, 230);
527 end if;
528 l_entry_value4 := pay_ele_shd.convert_lookups(p_input_value_id4, p_entry_value4, p_effective_date);
529 END IF;
530 IF p_input_value_id5 IS NOT NULL AND
531 p_entry_value5 IS NOT NULL THEN
532 if g_debug then
533 hr_utility.set_location(l_proc, 240);
534 end if;
535 l_entry_value5 := pay_ele_shd.convert_lookups(p_input_value_id5, p_entry_value5, p_effective_date);
536 END IF;
537 IF p_input_value_id6 IS NOT NULL AND
538 p_entry_value6 IS NOT NULL THEN
539 if g_debug then
540 hr_utility.set_location(l_proc, 250);
541 end if;
542 l_entry_value6 := pay_ele_shd.convert_lookups(p_input_value_id6, p_entry_value6, p_effective_date);
543 END IF;
544 IF p_input_value_id7 IS NOT NULL AND
545 p_entry_value7 IS NOT NULL THEN
546 if g_debug then
547 hr_utility.set_location(l_proc, 260);
548 end if;
549 l_entry_value7 := pay_ele_shd.convert_lookups(p_input_value_id7, p_entry_value7, p_effective_date);
550 END IF;
551 IF p_input_value_id8 IS NOT NULL AND
552 p_entry_value8 IS NOT NULL THEN
553 if g_debug then
554 hr_utility.set_location(l_proc, 270);
555 end if;
556 l_entry_value8 := pay_ele_shd.convert_lookups(p_input_value_id8, p_entry_value8, p_effective_date);
557 END IF;
558 IF p_input_value_id9 IS NOT NULL AND
559 p_entry_value9 IS NOT NULL THEN
560 if g_debug then
561 hr_utility.set_location(l_proc, 280);
562 end if;
563 l_entry_value9 := pay_ele_shd.convert_lookups(p_input_value_id9, p_entry_value9, p_effective_date);
564 END IF;
565 IF p_input_value_id10 IS NOT NULL AND
566 p_entry_value10 IS NOT NULL THEN
567 if g_debug then
568 hr_utility.set_location(l_proc, 290);
569 end if;
570 l_entry_value10 := pay_ele_shd.convert_lookups(p_input_value_id10, p_entry_value10, p_effective_date);
571 END IF;
572 IF p_input_value_id11 IS NOT NULL AND
573 p_entry_value11 IS NOT NULL THEN
574 if g_debug then
575 hr_utility.set_location(l_proc, 300);
576 end if;
577 l_entry_value11 := pay_ele_shd.convert_lookups(p_input_value_id11, p_entry_value11, p_effective_date);
578 END IF;
579 IF p_input_value_id12 IS NOT NULL AND
580 p_entry_value12 IS NOT NULL THEN
581 if g_debug then
582 hr_utility.set_location(l_proc, 310);
583 end if;
584 l_entry_value12 := pay_ele_shd.convert_lookups(p_input_value_id12, p_entry_value12, p_effective_date);
585 END IF;
586 IF p_input_value_id13 IS NOT NULL AND
587 p_entry_value13 IS NOT NULL THEN
588 if g_debug then
589 hr_utility.set_location(l_proc, 320);
590 end if;
591 l_entry_value13 := pay_ele_shd.convert_lookups(p_input_value_id13, p_entry_value13, p_effective_date);
592 END IF;
593 IF p_input_value_id14 IS NOT NULL AND
594 p_entry_value14 IS NOT NULL THEN
595 if g_debug then
596 hr_utility.set_location(l_proc, 330);
597 end if;
598 l_entry_value14 := pay_ele_shd.convert_lookups(p_input_value_id14, p_entry_value14, p_effective_date);
599 END IF;
600 IF p_input_value_id15 IS NOT NULL AND
601 p_entry_value15 IS NOT NULL THEN
602 if g_debug then
603 hr_utility.set_location(l_proc, 340);
604 end if;
605 l_entry_value15 := pay_ele_shd.convert_lookups(p_input_value_id15, p_entry_value15, p_effective_date);
606 END IF;
607 --
608 hr_entry_api.insert_element_entry
609 (
610 p_effective_start_date => l_effective_start_date,
611 p_effective_end_date => l_effective_end_date,
612 p_element_entry_id => l_element_entry_id,
613 p_original_entry_id => p_original_entry_id,
614 p_assignment_id => p_assignment_id,
615 p_element_link_id => p_element_link_id,
616 p_creator_type => l_creator_type,
617 p_entry_type => p_entry_type,
618 p_cost_allocation_keyflex_id => p_cost_allocation_keyflex_id,
619 p_updating_action_id => p_updating_action_id,
620 p_updating_action_type => p_updating_action_type,
621 p_comment_id => p_comment_id,
622 p_creator_id => l_creator_id,
623 p_reason => p_reason,
624 p_target_entry_id => p_target_entry_id,
625 p_subpriority => p_subpriority,
626 p_date_earned => l_date_earned,
627 p_personal_payment_method_id => p_personal_payment_method_id,
628 p_attribute_category => p_attribute_category,
629 p_attribute1 => p_attribute1,
630 p_attribute2 => p_attribute2,
631 p_attribute3 => p_attribute3,
632 p_attribute4 => p_attribute4,
636 p_attribute8 => p_attribute8,
633 p_attribute5 => p_attribute5,
634 p_attribute6 => p_attribute6,
635 p_attribute7 => p_attribute7,
637 p_attribute9 => p_attribute9,
638 p_attribute10 => p_attribute10,
639 p_attribute11 => p_attribute11,
640 p_attribute12 => p_attribute12,
641 p_attribute13 => p_attribute13,
642 p_attribute14 => p_attribute14,
643 p_attribute15 => p_attribute15,
644 p_attribute16 => p_attribute16,
645 p_attribute17 => p_attribute17,
646 p_attribute18 => p_attribute18,
647 p_attribute19 => p_attribute19,
648 p_attribute20 => p_attribute20,
649 p_input_value_id1 => p_input_value_id1,
650 p_input_value_id2 => p_input_value_id2,
651 p_input_value_id3 => p_input_value_id3,
652 p_input_value_id4 => p_input_value_id4,
653 p_input_value_id5 => p_input_value_id5,
654 p_input_value_id6 => p_input_value_id6,
655 p_input_value_id7 => p_input_value_id7,
656 p_input_value_id8 => p_input_value_id8,
657 p_input_value_id9 => p_input_value_id9,
658 p_input_value_id10 => p_input_value_id10,
659 p_input_value_id11 => p_input_value_id11,
660 p_input_value_id12 => p_input_value_id12,
661 p_input_value_id13 => p_input_value_id13,
662 p_input_value_id14 => p_input_value_id14,
663 p_input_value_id15 => p_input_value_id15,
664 p_entry_value1 => l_entry_value1,
665 p_entry_value2 => l_entry_value2,
666 p_entry_value3 => l_entry_value3,
667 p_entry_value4 => l_entry_value4,
668 p_entry_value5 => l_entry_value5,
669 p_entry_value6 => l_entry_value6,
670 p_entry_value7 => l_entry_value7,
671 p_entry_value8 => l_entry_value8,
672 p_entry_value9 => l_entry_value9,
673 p_entry_value10 => l_entry_value10,
674 p_entry_value11 => l_entry_value11,
675 p_entry_value12 => l_entry_value12,
676 p_entry_value13 => l_entry_value13,
677 p_entry_value14 => l_entry_value14,
678 p_entry_value15 => l_entry_value15,
679 p_entry_information_category => p_entry_information_category,
680 p_entry_information1 => p_entry_information1,
681 p_entry_information2 => p_entry_information2,
682 p_entry_information3 => p_entry_information3,
683 p_entry_information4 => p_entry_information4,
684 p_entry_information5 => p_entry_information5,
685 p_entry_information6 => p_entry_information6,
686 p_entry_information7 => p_entry_information7,
687 p_entry_information8 => p_entry_information8,
688 p_entry_information9 => p_entry_information9,
689 p_entry_information10 => p_entry_information10,
690 p_entry_information11 => p_entry_information11,
691 p_entry_information12 => p_entry_information12,
692 p_entry_information13 => p_entry_information13,
693 p_entry_information14 => p_entry_information14,
694 p_entry_information15 => p_entry_information15,
695 p_entry_information16 => p_entry_information16,
696 p_entry_information17 => p_entry_information17,
697 p_entry_information18 => p_entry_information18,
698 p_entry_information19 => p_entry_information19,
699 p_entry_information20 => p_entry_information20,
700 p_entry_information21 => p_entry_information21,
701 p_entry_information22 => p_entry_information22,
702 p_entry_information23 => p_entry_information23,
703 p_entry_information24 => p_entry_information24,
704 p_entry_information25 => p_entry_information25,
705 p_entry_information26 => p_entry_information26,
706 p_entry_information27 => p_entry_information27,
707 p_entry_information28 => p_entry_information28,
708 p_entry_information29 => p_entry_information29,
709 p_entry_information30 => p_entry_information30,
710 p_override_user_ent_chk => p_override_user_ent_chk
711 );
712 --
713 if g_debug then
714 hr_utility.set_location(l_proc, 800);
715 end if;
716 --
717 -- When in validation only mode raise the Validate_Enabled exception
718 --
719 IF hr_utility.check_warning THEN
720 l_create_warning := TRUE;
721 hr_utility.clear_warning;
722 END IF;
723 --
724 -- Get all output arguments
725 --
726 OPEN c_output_variables;
727 FETCH c_output_variables
728 INTO l_object_version_number;
729 CLOSE c_output_variables;
730 --
731 -- Call After Process User Hook
732 --
733 begin
734 pay_element_entry_bk1.create_element_entry_a
735 (p_effective_date => l_effective_date
736 ,p_business_group_id => p_business_group_id
737 ,p_original_entry_id => p_original_entry_id
738 ,p_assignment_id => p_assignment_id
739 ,p_element_link_id => p_element_link_id
740 ,p_entry_type => p_entry_type
741 ,p_creator_type => p_creator_type
742 ,p_cost_allocation_keyflex_id => p_cost_allocation_keyflex_id
743 ,p_updating_action_id => p_updating_action_id
744 ,p_updating_action_type => p_updating_action_type
745 ,p_comment_id => p_comment_id
746 ,p_reason => p_reason
750 ,p_personal_payment_method_id => p_personal_payment_method_id
747 ,p_target_entry_id => p_target_entry_id
748 ,p_subpriority => p_subpriority
749 ,p_date_earned => l_date_earned
751 ,p_attribute_category => p_attribute_category
752 ,p_attribute1 => p_attribute1
753 ,p_attribute2 => p_attribute2
754 ,p_attribute3 => p_attribute3
755 ,p_attribute4 => p_attribute4
756 ,p_attribute5 => p_attribute5
757 ,p_attribute6 => p_attribute6
758 ,p_attribute7 => p_attribute7
759 ,p_attribute8 => p_attribute8
760 ,p_attribute9 => p_attribute9
761 ,p_attribute10 => p_attribute10
762 ,p_attribute11 => p_attribute11
763 ,p_attribute12 => p_attribute12
764 ,p_attribute13 => p_attribute13
765 ,p_attribute14 => p_attribute14
766 ,p_attribute15 => p_attribute15
767 ,p_attribute16 => p_attribute16
768 ,p_attribute17 => p_attribute17
769 ,p_attribute18 => p_attribute18
770 ,p_attribute19 => p_attribute19
771 ,p_attribute20 => p_attribute20
772 ,p_input_value_id1 => p_input_value_id1
773 ,p_input_value_id2 => p_input_value_id2
774 ,p_input_value_id3 => p_input_value_id3
775 ,p_input_value_id4 => p_input_value_id4
776 ,p_input_value_id5 => p_input_value_id5
777 ,p_input_value_id6 => p_input_value_id6
778 ,p_input_value_id7 => p_input_value_id7
779 ,p_input_value_id8 => p_input_value_id8
780 ,p_input_value_id9 => p_input_value_id9
781 ,p_input_value_id10 => p_input_value_id10
782 ,p_input_value_id11 => p_input_value_id11
783 ,p_input_value_id12 => p_input_value_id12
784 ,p_input_value_id13 => p_input_value_id13
785 ,p_input_value_id14 => p_input_value_id14
786 ,p_input_value_id15 => p_input_value_id15
787 ,p_entry_value1 => p_entry_value1
788 ,p_entry_value2 => p_entry_value2
789 ,p_entry_value3 => p_entry_value3
790 ,p_entry_value4 => p_entry_value4
791 ,p_entry_value5 => p_entry_value5
792 ,p_entry_value6 => p_entry_value6
793 ,p_entry_value7 => p_entry_value7
794 ,p_entry_value8 => p_entry_value8
795 ,p_entry_value9 => p_entry_value9
796 ,p_entry_value10 => p_entry_value10
797 ,p_entry_value11 => p_entry_value11
798 ,p_entry_value12 => p_entry_value12
799 ,p_entry_value13 => p_entry_value13
800 ,p_entry_value14 => p_entry_value14
801 ,p_entry_value15 => p_entry_value15
802 ,p_entry_information_category => p_entry_information_category
803 ,p_entry_information1 => p_entry_information1
804 ,p_entry_information2 => p_entry_information2
805 ,p_entry_information3 => p_entry_information3
806 ,p_entry_information4 => p_entry_information4
807 ,p_entry_information5 => p_entry_information5
808 ,p_entry_information6 => p_entry_information6
809 ,p_entry_information7 => p_entry_information7
810 ,p_entry_information8 => p_entry_information8
811 ,p_entry_information9 => p_entry_information9
812 ,p_entry_information10 => p_entry_information10
813 ,p_entry_information11 => p_entry_information11
814 ,p_entry_information12 => p_entry_information12
815 ,p_entry_information13 => p_entry_information13
816 ,p_entry_information14 => p_entry_information14
817 ,p_entry_information15 => p_entry_information15
818 ,p_entry_information16 => p_entry_information16
819 ,p_entry_information17 => p_entry_information17
820 ,p_entry_information18 => p_entry_information18
821 ,p_entry_information19 => p_entry_information19
822 ,p_entry_information20 => p_entry_information20
823 ,p_entry_information21 => p_entry_information21
824 ,p_entry_information22 => p_entry_information22
825 ,p_entry_information23 => p_entry_information23
826 ,p_entry_information24 => p_entry_information24
827 ,p_entry_information25 => p_entry_information25
828 ,p_entry_information26 => p_entry_information26
829 ,p_entry_information27 => p_entry_information27
830 ,p_entry_information28 => p_entry_information28
831 ,p_entry_information29 => p_entry_information29
832 ,p_entry_information30 => p_entry_information30
833 ,p_override_user_ent_chk => p_override_user_ent_chk
834 ,p_effective_start_date => l_effective_start_date
835 ,p_effective_end_date => l_effective_end_date
836 ,p_element_entry_id => l_element_entry_id
837 ,p_object_version_number => l_object_version_number
841 when hr_api.cannot_find_prog_unit then
838 ,p_create_warning => l_create_warning
839 );
840 exception
842 hr_api.cannot_find_prog_unit_error
843 (p_module_name => 'CREATE_ELEMENT_ENTRY'
844 ,p_hook_type => 'AP'
845 );
846 end;
847 --
848 IF p_validate THEN
849 RAISE hr_api.validate_enabled;
850 END IF;
851 --
852 -- Set all output arguments
853 --
854 p_element_entry_id := l_element_entry_id;
855 p_object_version_number := l_object_version_number;
856 p_effective_start_date := l_effective_start_date;
857 p_effective_end_date := l_effective_end_date;
858 p_create_warning := l_create_warning;
859 --
860 if g_debug then
861 hr_utility.set_location(' Leaving:'||l_proc, 11);
862 end if;
863 --
864 EXCEPTION
865 WHEN hr_api.validate_enabled THEN
866 --
867 -- As the Validate_Enabled exception has been raised
868 -- we must rollback to the savepoint
869 --
870 ROLLBACK TO create_element_entry;
871 --
872 -- Only set output warning arguments
873 -- (Any key or derived arguments must be set to null
874 -- when validation only mode is being used.)
875 --
876 p_element_entry_id := null;
877 p_object_version_number := null;
878 p_effective_start_date := null;
879 p_effective_end_date := null;
880 p_create_warning := l_create_warning;
881 --
882 when others then
883 --
884 -- A validation or unexpected error has occurred
885 --
886 -- Added as part of fix to bug 632479
887 --
888 ROLLBACK TO create_element_entry;
889 p_effective_start_date := null;
890 p_effective_end_date := null;
891 p_element_entry_id := null;
892 p_object_version_number := null;
893 p_create_warning := null;
894 raise;
895 --
896 -- End of fix.
897 --
898 if g_debug then
899 hr_utility.set_location(' Leaving:'||l_proc, 900);
900 end if;
901 END create_element_entry;
902 --
903 -- ----------------------------------------------------------------------------
904 -- |-------------------------< delete_element_entry >-------------------------|
905 -- ----------------------------------------------------------------------------
906 --
907 PROCEDURE delete_element_entry
908 (p_validate in boolean default false
909 ,p_datetrack_delete_mode in varchar2
910 ,p_effective_date in date
911 ,p_element_entry_id in number
912 ,p_object_version_number in out nocopy number
913 ,p_effective_start_date out nocopy date
914 ,p_effective_end_date out nocopy date
915 ,p_delete_warning out nocopy boolean
916 ) IS
917 --
918 -- Declare cursors and local variables
919 --
920 l_proc varchar2(72) := g_package||'delete_element_entry';
921 l_object_version_number pay_element_entries_f.object_version_number%TYPE;
922 l_effective_start_date pay_element_entries_f.effective_start_date%TYPE;
923 l_effective_end_date pay_element_entries_f.effective_end_date%TYPE;
924 -- bug 659393, added variables for storing dates passed in and truncate them
925 l_effective_date date;
926 -- divicker Added for new lck signature
927 l_validation_start_date date;
928 l_validation_end_date date;
929 --
930 CURSOR C_Output_Variables IS
931 SELECT effective_start_date,
932 effective_end_date,
933 object_version_number
934 FROM pay_element_entries_f
935 WHERE p_element_entry_id = element_entry_id
936 -- bug 675794, added date condition to select correct row
937 and l_effective_date between effective_start_date
938 and effective_end_date;
939 BEGIN
940 g_debug := hr_utility.debug_enabled;
941 if g_debug then
942 hr_utility.set_location('Entering:'|| l_proc, 5);
943 end if;
944 -- bug 659393, added variables for storing dates passed in and truncate them
945 l_effective_date := trunc(p_effective_date);
946 --
947 -- Store initial values for IN OUT parameters
948 --
949 l_object_version_number := p_object_version_number;
950 --
951 -- Issue a savepoint if operating in validation only mode.
952 --
953 IF p_validate THEN
954 SAVEPOINT delete_element_entry;
955 END IF;
956 if g_debug then
957 hr_utility.set_location(l_proc, 6);
958 end if;
959 --
960 -- Call Before Process User Hook
961 --
962 begin
963 pay_element_entry_bk3.delete_element_entry_b
964 (p_datetrack_delete_mode => p_datetrack_delete_mode
965 ,p_effective_date => l_effective_date
966 ,p_element_entry_id => p_element_entry_id
967 ,p_object_version_number => p_object_version_number
968 );
969 exception
970 when hr_api.cannot_find_prog_unit then
971 hr_api.cannot_find_prog_unit_error
972 (p_module_name => 'DELETE_ELEMENT_ENTRY'
973 ,p_hook_type => 'BP'
974 );
975 end;
976 --
977 -- Validation in addition to Row Handlers
978 -- 1. Ensure datetrack mode is valid
979 --
983 hr_utility.set_location(l_proc, 7);
980 dt_api.validate_dt_del_mode(p_datetrack_mode => p_datetrack_delete_mode);
981 --
982 if g_debug then
984 end if;
985 --
986 -- Process Logic
987 -- 1. Lock the required row
988 -- 2. Call delete element entry
989 --
990 pay_ele_shd.lck(p_effective_date => l_effective_date
991 ,p_element_entry_id => p_element_entry_id
992 ,p_object_version_number => l_object_version_number
993 ,p_datetrack_mode => p_datetrack_delete_mode
994 ,p_validation_start_date => l_validation_start_date
995 ,p_validation_end_date => l_validation_end_date);
996 --
997 hr_entry_api.delete_element_entry
998 (
999 p_datetrack_delete_mode,
1000 l_effective_date,
1001 p_element_entry_id
1002 );
1003 --
1004 if g_debug then
1005 hr_utility.set_location(l_proc, 8);
1006 end if;
1007 --
1008 -- Get all output arguments
1009 --
1010 OPEN C_Output_Variables;
1011 FETCH C_Output_Variables
1012 INTO l_effective_start_date,
1013 l_effective_end_date,
1014 l_object_version_number;
1015 CLOSE C_Output_Variables;
1016 --
1017 -- Call After Process User Hook
1018 --
1019 begin
1020 pay_element_entry_bk3.delete_element_entry_a
1021 (p_datetrack_delete_mode => p_datetrack_delete_mode
1022 ,p_effective_date => l_effective_date
1023 ,p_element_entry_id => p_element_entry_id
1024 ,p_object_version_number => l_object_version_number
1025 ,p_effective_start_date => l_effective_start_date
1026 ,p_effective_end_date => l_effective_end_date
1027 ,p_delete_warning => p_delete_warning
1028 );
1029 exception
1030 when hr_api.cannot_find_prog_unit then
1031 hr_api.cannot_find_prog_unit_error
1032 (p_module_name => 'DELETE_ELEMENT_ENTRY'
1033 ,p_hook_type => 'AP'
1034 );
1035 end;
1036 --
1037 -- When in validation only mode raise the Validate_Enabled exception
1038 --
1039 IF p_validate THEN
1040 RAISE hr_api.validate_enabled;
1041 END IF;
1042 --
1043 -- Set all output arguments
1044 --
1045 p_object_version_number := l_object_version_number;
1046 p_effective_start_date := l_effective_start_date;
1047 p_effective_end_date := l_effective_end_date;
1048 -- p_delete_warning := <local_var_set_in_process_logic>
1049 --
1050 if g_debug then
1051 hr_utility.set_location(' Leaving:'||l_proc, 11);
1052 end if;
1053 EXCEPTION
1054 WHEN hr_api.validate_enabled THEN
1055 --
1056 -- As the Validate_Enabled exception has been raised
1057 -- we must rollback to the savepoint
1058 --
1059 ROLLBACK TO delete_element_entry;
1060 --
1061 -- Only set output warning arguments
1062 -- (Any key or derived arguments must be set to null
1063 -- when validation only mode is being used.)
1064 --
1065 p_object_version_number := l_object_version_number;
1066 p_effective_start_date := null;
1067 p_effective_end_date := null;
1068 -- p_delete_warning := <local_var_set_in_process_logic>
1069 if g_debug then
1070 hr_utility.set_location(' Leaving:'||l_proc, 12);
1071 end if;
1072 END delete_element_entry;
1073
1074
1075 --
1076 -- ----------------------------------------------------------------------------
1077 -- |----------------------------< update_validate >----------------------------|
1078 -- ----------------------------------------------------------------------------
1079 -- This procedure is used to validate the update statement. Certain fields are
1080 -- allowed for update and those fields are checked here
1081 procedure update_validate(
1082 p_element_entry_id in number
1083 ,p_effective_date in date
1084 ,p_creator_type in varchar2 default hr_api.g_varchar2
1085 ,p_creator_id in number default hr_api.g_number
1086 ,p_personal_payment_method_id in number default hr_api.g_number
1087 ,p_input_value_id1 in number default null
1088 ,p_input_value_id2 in number default null
1089 ,p_input_value_id3 in number default null
1090 ,p_input_value_id4 in number default null
1091 ,p_input_value_id5 in number default null
1092 ,p_input_value_id6 in number default null
1093 ,p_input_value_id7 in number default null
1094 ,p_input_value_id8 in number default null
1095 ,p_input_value_id9 in number default null
1096 ,p_input_value_id10 in number default null
1097 ,p_input_value_id11 in number default null
1098 ,p_input_value_id12 in number default null
1099 ,p_input_value_id13 in number default null
1100 ,p_input_value_id14 in number default null
1101 ,p_input_value_id15 in number default null
1102 ,p_entry_value1 in varchar2 default null
1103 ,p_entry_value2 in varchar2 default null
1104 ,p_entry_value3 in varchar2 default null
1108 ,p_entry_value7 in varchar2 default null
1105 ,p_entry_value4 in varchar2 default null
1106 ,p_entry_value5 in varchar2 default null
1107 ,p_entry_value6 in varchar2 default null
1109 ,p_entry_value8 in varchar2 default null
1110 ,p_entry_value9 in varchar2 default null
1111 ,p_entry_value10 in varchar2 default null
1112 ,p_entry_value11 in varchar2 default null
1113 ,p_entry_value12 in varchar2 default null
1114 ,p_entry_value13 in varchar2 default null
1115 ,p_entry_value14 in varchar2 default null
1116 ,p_entry_value15 in varchar2 default null
1117 )
1118 is
1119
1120 cursor csr_creator_type (c_element_entry_id in number,
1121 c_effective_date in date) is
1122 select creator_type, count(element_entry_id) total_records
1123 from pay_element_entries_f
1124 where element_entry_id = c_element_entry_id
1125 and c_effective_date between effective_start_date and effective_end_date
1126 group by creator_type;
1127
1128 cursor csr_creator_type_validate (c_element_entry_id in number,
1129 c_effective_date in date,
1130 c_creator_type in varchar2,
1131 c_creator_id in number,
1132 c_personal_payment_method_id in number) is
1133 select count(creator_type)
1134 from pay_element_entries_f
1135 where element_entry_id = c_element_entry_id
1136 and c_effective_date between effective_start_date and effective_end_date
1137 and ( (c_creator_type <> hr_api.g_varchar2
1138 and creator_type <> c_creator_type)
1139 or (c_creator_id <> hr_api.g_number
1140 and ( nvl(creator_id, -1) <> nvl(c_creator_id, -1)
1141 )
1142 )
1143 or (c_personal_payment_method_id <> hr_api.g_number
1144 and (nvl(personal_payment_method_id, -1) <> nvl(c_personal_payment_method_id, -1)
1145 )
1146 )
1147 );
1148
1149 cursor csr_entry_values_check (c_element_entry_id in number,
1150 c_effective_date in date) is
1151 select input_value_id,
1152 screen_entry_value
1153 from pay_element_entry_values_f
1154 where element_entry_id = c_element_entry_id
1155 and c_effective_date between effective_start_date and effective_end_date;
1156
1157 l_total_elt_entry_records number;
1158 l_proc varchar2(300);
1159 l_creator_type pay_element_entries_f.creator_type%type;
1160
1161 -- Creating types for storing input value id and entry values
1162 type input_value_id_tbl is table of number index by binary_integer;
1163 type entry_value_tbl is table of varchar2(80) index by binary_integer;
1164
1165 l_input_value_id_tbl input_value_id_tbl;
1166 l_entry_value_tbl entry_value_tbl;
1167
1168 l_input_id number;
1169 l_screen_entry_value varchar2(80);
1170 l_count number;
1171
1172 procedure conv_entry_into_table (p_input_value_id in number,
1173 p_entry_value in varchar2)
1174 is
1175 l_proc_name varchar2(300);
1176 l_count number;
1177 begin
1178 l_proc_name := 'conv_entry_into_table';
1179 hr_utility.set_location('Entering '||l_proc_name, 10);
1180
1181 if p_input_value_id is not null then
1182
1183 if l_input_value_id_tbl.last is not null then
1184 l_count := l_input_value_id_tbl.last;
1185 else
1186 l_count := 1;
1187 end if;
1188 hr_utility.set_location('l_count '||l_count, 15);
1189 l_input_value_id_tbl(l_count) := p_input_value_id;
1190 l_entry_value_tbl(l_count) := p_entry_value;
1191 end if; --
1192 hr_utility.set_location('Leaving '||l_proc_name, 20);
1193 exception
1194 when others then
1195 hr_utility.set_location('Error '||l_proc_name, 30);
1196 hr_utility.set_location(sqlerrm, 30);
1197 end conv_entry_into_table;
1198
1199 begin
1200
1201 l_proc := 'update_validate';
1202 hr_utility.set_location('Entering '||l_proc, 5);
1203
1204 hr_utility.set_location('obtain creator type ', 10);
1205
1206 open csr_creator_type (p_element_entry_id, p_effective_date);
1207 fetch csr_creator_type into l_creator_type, l_total_elt_entry_records;
1208 close csr_creator_type;
1209
1210 /* Overlapping of records will be validated later
1211 -- Check for overlapping of element entry record
1212 if l_total_elt_entry_records > 1 then
1213 hr_utility.set_message('PAY', 'OVERLAPPING OF RECORDS IS NOT ALLOWED');
1214 hr_utility.raise_error;
1215 end if;
1216 */
1217
1218 hr_utility.set_location('After element entry unique key check '||l_proc, 15);
1219
1220 if l_creator_type in ('EE', 'NR', 'R', 'RR', 'PR', 'D', 'DF', 'AD', 'AE', 'P', 'B') then
1221
1222 -- Check for creator type, creator id and personal_payment_method_id
1223 open csr_creator_type_validate (p_element_entry_id, p_effective_date, p_creator_type, p_creator_id, p_personal_payment_method_id);
1224 fetch csr_creator_type_validate into l_total_elt_entry_records;
1225 if l_total_elt_entry_records > 0 then
1226 hr_utility.set_message(801, 'PAY_33292_CHANGE_PROTECTED');
1227 hr_utility.set_message_token('CREATOR_TYPE', hr_general.decode_lookup('CREATOR_TYPE', l_creator_type));
1228 hr_utility.raise_error;
1229 end if;
1233
1230 close csr_creator_type_validate;
1231
1232 hr_utility.set_location('After creator type, creator id and personal_payment_method_id check '||l_proc, 20);
1234 hr_utility.set_location('Adding input value and entry values in to pl/sql tables ' || l_proc, 30);
1235
1236 conv_entry_into_table (p_input_value_id1, p_entry_value1);
1237 conv_entry_into_table (p_input_value_id2, p_entry_value2);
1238 conv_entry_into_table (p_input_value_id3, p_entry_value3);
1239 conv_entry_into_table (p_input_value_id4, p_entry_value4);
1240 conv_entry_into_table (p_input_value_id5, p_entry_value5);
1241 conv_entry_into_table (p_input_value_id6, p_entry_value6);
1242 conv_entry_into_table (p_input_value_id7, p_entry_value7);
1243 conv_entry_into_table (p_input_value_id8, p_entry_value8);
1244 conv_entry_into_table (p_input_value_id9, p_entry_value9);
1245 conv_entry_into_table (p_input_value_id10, p_entry_value10);
1246 conv_entry_into_table (p_input_value_id11, p_entry_value11);
1247 conv_entry_into_table (p_input_value_id12, p_entry_value12);
1248 conv_entry_into_table (p_input_value_id13, p_entry_value13);
1249 conv_entry_into_table (p_input_value_id14, p_entry_value14);
1250 conv_entry_into_table (p_input_value_id15, p_entry_value15);
1251
1252 hr_utility.set_location('Element Entry check '||l_proc, 35);
1253
1254 open csr_entry_values_check (p_element_entry_id , p_effective_date );
1255 loop
1256 fetch csr_entry_values_check into l_input_id, l_screen_entry_value;
1257 exit when csr_entry_values_check%notfound;
1258
1259 l_count := 1;
1260
1261 while l_count <= l_input_value_id_tbl.last
1262 loop
1263 if l_input_value_id_tbl(l_count) = l_input_id then
1264 if l_entry_value_tbl(l_count) <> l_screen_entry_value then
1265 hr_utility.set_location('Raise Error '||l_proc, 40);
1266
1267 hr_utility.set_message(801, 'PAY_33292_CHANGE_PROTECTED');
1268 hr_utility.set_message_token('CREATOR_TYPE', hr_general.decode_lookup('CREATOR_TYPE', l_creator_type));
1269 hr_utility.raise_error;
1270
1271 end if; -- screen entry value is not equal
1272 end if; -- input value is equal
1273 l_count := l_count + 1;
1274 end loop; -- input value loop
1275 end loop; --element entry value cursor
1276
1277 end if; --Creator type check
1278 hr_utility.set_location('Leaving '||l_proc, 45);
1279
1280 exception
1281 when others then
1282 hr_utility.set_location('Error '||l_proc, 50);
1283 hr_utility.set_location(sqlerrm, 50);
1284 raise;
1285 end update_validate;
1286
1287
1288
1289 --
1290 -- ----------------------------------------------------------------------------
1291 -- |-------------------------< update_element_entry >-------------------------|
1292 -- ----------------------------------------------------------------------------
1293 --
1294 PROCEDURE update_element_entry
1295 (p_validate in boolean default false
1296 ,p_datetrack_update_mode in varchar2
1297 ,p_effective_date in date
1298 ,p_business_group_id in number
1299 ,p_element_entry_id in number
1300 ,p_object_version_number in out nocopy number
1301 ,p_cost_allocation_keyflex_id in number default hr_api.g_number
1302 ,p_updating_action_id in number default hr_api.g_number
1303 ,p_updating_action_type in varchar2 default hr_api.g_varchar2
1304 ,p_original_entry_id in number default hr_api.g_number
1305 ,p_creator_type in varchar2 default hr_api.g_varchar2
1306 ,p_comment_id in number default hr_api.g_number
1307 ,p_creator_id in number default hr_api.g_number
1308 ,p_reason in varchar2 default hr_api.g_varchar2
1309 ,p_subpriority in number default hr_api.g_number
1310 ,p_date_earned in date default hr_api.g_date
1311 ,p_personal_payment_method_id in number default hr_api.g_number
1312 ,p_attribute_category in varchar2 default hr_api.g_varchar2
1313 ,p_attribute1 in varchar2 default hr_api.g_varchar2
1314 ,p_attribute2 in varchar2 default hr_api.g_varchar2
1315 ,p_attribute3 in varchar2 default hr_api.g_varchar2
1316 ,p_attribute4 in varchar2 default hr_api.g_varchar2
1317 ,p_attribute5 in varchar2 default hr_api.g_varchar2
1318 ,p_attribute6 in varchar2 default hr_api.g_varchar2
1319 ,p_attribute7 in varchar2 default hr_api.g_varchar2
1320 ,p_attribute8 in varchar2 default hr_api.g_varchar2
1321 ,p_attribute9 in varchar2 default hr_api.g_varchar2
1322 ,p_attribute10 in varchar2 default hr_api.g_varchar2
1323 ,p_attribute11 in varchar2 default hr_api.g_varchar2
1324 ,p_attribute12 in varchar2 default hr_api.g_varchar2
1325 ,p_attribute13 in varchar2 default hr_api.g_varchar2
1326 ,p_attribute14 in varchar2 default hr_api.g_varchar2
1327 ,p_attribute15 in varchar2 default hr_api.g_varchar2
1328 ,p_attribute16 in varchar2 default hr_api.g_varchar2
1329 ,p_attribute17 in varchar2 default hr_api.g_varchar2
1333 ,p_input_value_id1 in number default null
1330 ,p_attribute18 in varchar2 default hr_api.g_varchar2
1331 ,p_attribute19 in varchar2 default hr_api.g_varchar2
1332 ,p_attribute20 in varchar2 default hr_api.g_varchar2
1334 ,p_input_value_id2 in number default null
1335 ,p_input_value_id3 in number default null
1336 ,p_input_value_id4 in number default null
1337 ,p_input_value_id5 in number default null
1338 ,p_input_value_id6 in number default null
1339 ,p_input_value_id7 in number default null
1340 ,p_input_value_id8 in number default null
1341 ,p_input_value_id9 in number default null
1342 ,p_input_value_id10 in number default null
1343 ,p_input_value_id11 in number default null
1344 ,p_input_value_id12 in number default null
1345 ,p_input_value_id13 in number default null
1346 ,p_input_value_id14 in number default null
1347 ,p_input_value_id15 in number default null
1348 ,p_entry_value1 in varchar2 default null
1349 ,p_entry_value2 in varchar2 default null
1350 ,p_entry_value3 in varchar2 default null
1351 ,p_entry_value4 in varchar2 default null
1352 ,p_entry_value5 in varchar2 default null
1353 ,p_entry_value6 in varchar2 default null
1354 ,p_entry_value7 in varchar2 default null
1355 ,p_entry_value8 in varchar2 default null
1356 ,p_entry_value9 in varchar2 default null
1357 ,p_entry_value10 in varchar2 default null
1358 ,p_entry_value11 in varchar2 default null
1359 ,p_entry_value12 in varchar2 default null
1360 ,p_entry_value13 in varchar2 default null
1361 ,p_entry_value14 in varchar2 default null
1362 ,p_entry_value15 in varchar2 default null
1363 ,p_entry_information_category in varchar2 default hr_api.g_varchar2
1364 ,p_entry_information1 in varchar2 default hr_api.g_varchar2
1365 ,p_entry_information2 in varchar2 default hr_api.g_varchar2
1366 ,p_entry_information3 in varchar2 default hr_api.g_varchar2
1367 ,p_entry_information4 in varchar2 default hr_api.g_varchar2
1368 ,p_entry_information5 in varchar2 default hr_api.g_varchar2
1369 ,p_entry_information6 in varchar2 default hr_api.g_varchar2
1370 ,p_entry_information7 in varchar2 default hr_api.g_varchar2
1371 ,p_entry_information8 in varchar2 default hr_api.g_varchar2
1372 ,p_entry_information9 in varchar2 default hr_api.g_varchar2
1373 ,p_entry_information10 in varchar2 default hr_api.g_varchar2
1374 ,p_entry_information11 in varchar2 default hr_api.g_varchar2
1375 ,p_entry_information12 in varchar2 default hr_api.g_varchar2
1376 ,p_entry_information13 in varchar2 default hr_api.g_varchar2
1377 ,p_entry_information14 in varchar2 default hr_api.g_varchar2
1378 ,p_entry_information15 in varchar2 default hr_api.g_varchar2
1379 ,p_entry_information16 in varchar2 default hr_api.g_varchar2
1380 ,p_entry_information17 in varchar2 default hr_api.g_varchar2
1381 ,p_entry_information18 in varchar2 default hr_api.g_varchar2
1382 ,p_entry_information19 in varchar2 default hr_api.g_varchar2
1383 ,p_entry_information20 in varchar2 default hr_api.g_varchar2
1384 ,p_entry_information21 in varchar2 default hr_api.g_varchar2
1385 ,p_entry_information22 in varchar2 default hr_api.g_varchar2
1386 ,p_entry_information23 in varchar2 default hr_api.g_varchar2
1387 ,p_entry_information24 in varchar2 default hr_api.g_varchar2
1388 ,p_entry_information25 in varchar2 default hr_api.g_varchar2
1389 ,p_entry_information26 in varchar2 default hr_api.g_varchar2
1390 ,p_entry_information27 in varchar2 default hr_api.g_varchar2
1391 ,p_entry_information28 in varchar2 default hr_api.g_varchar2
1392 ,p_entry_information29 in varchar2 default hr_api.g_varchar2
1393 ,p_entry_information30 in varchar2 default hr_api.g_varchar2
1394 ,p_override_user_ent_chk in varchar2 default 'N'
1395 ,p_effective_start_date out nocopy date
1396 ,p_effective_end_date out nocopy date
1397 ,p_update_warning out nocopy boolean
1398 ) IS
1399 --
1400 -- Declare cursors and local variables
1401 --
1402 l_proc varchar2(72) := g_package||'update_element_entry';
1403 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
1404 l_object_version_number pay_element_entries_f.object_version_number%TYPE;
1405 l_effective_start_date pay_element_entries_f.effective_start_date%TYPE;
1406 l_effective_end_date pay_element_entries_f.effective_end_date%TYPE;
1407 --
1408 -- Enhancement 2793978
1409 -- size of entry_value variables increased to deal with
1410 -- screen format of entry values that use value sets
1411 --
1412 l_entry_value1 varchar2(240);
1413 l_entry_value2 varchar2(240);
1414 l_entry_value3 varchar2(240);
1418 l_entry_value7 varchar2(240);
1415 l_entry_value4 varchar2(240);
1416 l_entry_value5 varchar2(240);
1417 l_entry_value6 varchar2(240);
1419 l_entry_value8 varchar2(240);
1420 l_entry_value9 varchar2(240);
1421 l_entry_value10 varchar2(240);
1422 l_entry_value11 varchar2(240);
1423 l_entry_value12 varchar2(240);
1424 l_entry_value13 varchar2(240);
1425 l_entry_value14 varchar2(240);
1426 l_entry_value15 varchar2(240);
1427 --
1428 l_creator_type pay_element_entries_f.creator_type%TYPE;
1429 l_creator_id pay_element_entries_f.creator_id%TYPE;
1430 -- bug 659393, added variables for storing dates passed in and truncate them
1431 l_effective_date date;
1432 l_date_earned pay_element_entries_f.date_earned%TYPE;
1433 -- divicker Added for new lck signature
1434 l_validation_start_date date;
1435 l_validation_end_date date;
1436 --
1437 -- Bugfix 2646060
1438 -- l_costable_type needed to hold the costable_type of the element link
1439 --
1440 l_costable_type pay_element_links_f.costable_type%TYPE;
1441 --
1442 CURSOR c_output_variables IS
1443 SELECT ee.effective_start_date,
1444 ee.effective_end_date,
1445 ee.object_version_number
1446 FROM pay_element_entries_f ee
1447 WHERE p_element_entry_id = ee.element_entry_id
1448 -- bug 675794, added date condition to select correct row
1449 and l_effective_date between ee.effective_start_date
1450 and ee.effective_end_date;
1451 CURSOR c_entry_details IS
1452 SELECT ee.creator_type
1453 FROM pay_element_entries_f ee
1454 WHERE p_element_entry_id = ee.element_entry_id;
1455 --
1456 -- Bugfix 2646060
1457 -- c_link_details required to retrieve the costable_type of the
1458 -- element_link for the element_entry being updated
1459 --
1460 CURSOR c_link_details ( p_element_entry_id NUMBER
1461 , p_effective_date DATE ) IS
1462 SELECT el.costable_type
1463 FROM pay_element_entries_f ee
1464 , pay_element_links_f el
1465 WHERE ee.element_entry_id = p_element_entry_id
1466 AND ee.element_link_id = el.element_link_id
1467 AND p_effective_date BETWEEN ee.effective_start_date
1468 AND ee.effective_end_date
1469 AND p_effective_date BETWEEN el.effective_start_date
1470 AND el.effective_end_date;
1471 --
1472 BEGIN
1473 g_debug := hr_utility.debug_enabled;
1474 if g_debug then
1475 hr_utility.set_location('Entering:'|| l_proc, 5);
1476 end if;
1477 --
1478 -- Store initial values for IN OUT parameters
1479 --
1480 l_object_version_number := p_object_version_number;
1481 l_element_entry_id := p_element_entry_id;
1482 -- bug 659393, added variables for storing dates passed in and truncate them
1483 l_effective_date := trunc(p_effective_date);
1484 l_date_earned := trunc(p_date_earned);
1485 --
1486 -- Issue a savepoint.
1487 --
1488 SAVEPOINT update_element_entry;
1489 if g_debug then
1490 hr_utility.set_location(l_proc, 6);
1491 end if;
1492 --
1493 -- Call Before Process User Hook
1494 --
1495 begin
1496 pay_element_entry_bk2.update_element_entry_b
1497 (p_datetrack_update_mode => p_datetrack_update_mode
1498 ,p_effective_date => l_effective_date
1499 ,p_business_group_id => p_business_group_id
1500 ,p_element_entry_id => p_element_entry_id
1501 ,p_object_version_number => p_object_version_number
1502 ,p_cost_allocation_keyflex_id => p_cost_allocation_keyflex_id
1503 ,p_updating_action_id => p_updating_action_id
1504 ,p_updating_action_type => p_updating_action_type
1505 ,p_original_entry_id => p_original_entry_id
1506 ,p_creator_type => p_creator_type
1507 ,p_comment_id => p_comment_id
1508 ,p_creator_id => p_creator_id
1509 ,p_reason => p_reason
1510 ,p_subpriority => p_subpriority
1511 ,p_date_earned => l_date_earned
1512 ,p_personal_payment_method_id => p_personal_payment_method_id
1513 ,p_attribute_category => p_attribute_category
1514 ,p_attribute1 => p_attribute1
1515 ,p_attribute2 => p_attribute2
1516 ,p_attribute3 => p_attribute3
1517 ,p_attribute4 => p_attribute4
1518 ,p_attribute5 => p_attribute5
1519 ,p_attribute6 => p_attribute6
1520 ,p_attribute7 => p_attribute7
1521 ,p_attribute8 => p_attribute8
1522 ,p_attribute9 => p_attribute9
1523 ,p_attribute10 => p_attribute10
1524 ,p_attribute11 => p_attribute11
1525 ,p_attribute12 => p_attribute12
1526 ,p_attribute13 => p_attribute13
1527 ,p_attribute14 => p_attribute14
1528 ,p_attribute15 => p_attribute15
1529 ,p_attribute16 => p_attribute16
1530 ,p_attribute17 => p_attribute17
1534 ,p_input_value_id1 => p_input_value_id1
1531 ,p_attribute18 => p_attribute18
1532 ,p_attribute19 => p_attribute19
1533 ,p_attribute20 => p_attribute20
1535 ,p_input_value_id2 => p_input_value_id2
1536 ,p_input_value_id3 => p_input_value_id3
1537 ,p_input_value_id4 => p_input_value_id4
1538 ,p_input_value_id5 => p_input_value_id5
1539 ,p_input_value_id6 => p_input_value_id6
1540 ,p_input_value_id7 => p_input_value_id7
1541 ,p_input_value_id8 => p_input_value_id8
1542 ,p_input_value_id9 => p_input_value_id9
1543 ,p_input_value_id10 => p_input_value_id10
1544 ,p_input_value_id11 => p_input_value_id11
1545 ,p_input_value_id12 => p_input_value_id12
1546 ,p_input_value_id13 => p_input_value_id13
1547 ,p_input_value_id14 => p_input_value_id14
1548 ,p_input_value_id15 => p_input_value_id15
1549 ,p_entry_value1 => p_entry_value1
1550 ,p_entry_value2 => p_entry_value2
1551 ,p_entry_value3 => p_entry_value3
1552 ,p_entry_value4 => p_entry_value4
1553 ,p_entry_value5 => p_entry_value5
1554 ,p_entry_value6 => p_entry_value6
1555 ,p_entry_value7 => p_entry_value7
1556 ,p_entry_value8 => p_entry_value8
1557 ,p_entry_value9 => p_entry_value9
1558 ,p_entry_value10 => p_entry_value10
1559 ,p_entry_value11 => p_entry_value11
1560 ,p_entry_value12 => p_entry_value12
1561 ,p_entry_value13 => p_entry_value13
1562 ,p_entry_value14 => p_entry_value14
1563 ,p_entry_value15 => p_entry_value15
1564 ,p_entry_information_category => p_entry_information_category
1565 ,p_entry_information1 => p_entry_information1
1566 ,p_entry_information2 => p_entry_information2
1567 ,p_entry_information3 => p_entry_information3
1568 ,p_entry_information4 => p_entry_information4
1569 ,p_entry_information5 => p_entry_information5
1570 ,p_entry_information6 => p_entry_information6
1571 ,p_entry_information7 => p_entry_information7
1572 ,p_entry_information8 => p_entry_information8
1573 ,p_entry_information9 => p_entry_information9
1574 ,p_entry_information10 => p_entry_information10
1575 ,p_entry_information11 => p_entry_information11
1576 ,p_entry_information12 => p_entry_information12
1577 ,p_entry_information13 => p_entry_information13
1578 ,p_entry_information14 => p_entry_information14
1579 ,p_entry_information15 => p_entry_information15
1580 ,p_entry_information16 => p_entry_information16
1581 ,p_entry_information17 => p_entry_information17
1582 ,p_entry_information18 => p_entry_information18
1583 ,p_entry_information19 => p_entry_information19
1584 ,p_entry_information20 => p_entry_information20
1585 ,p_entry_information21 => p_entry_information21
1586 ,p_entry_information22 => p_entry_information22
1587 ,p_entry_information23 => p_entry_information23
1588 ,p_entry_information24 => p_entry_information24
1589 ,p_entry_information25 => p_entry_information25
1590 ,p_entry_information26 => p_entry_information26
1591 ,p_entry_information27 => p_entry_information27
1592 ,p_entry_information28 => p_entry_information28
1593 ,p_entry_information29 => p_entry_information29
1594 ,p_entry_information30 => p_entry_information30
1595 ,p_override_user_ent_chk => p_override_user_ent_chk
1596 );
1597 exception
1598 when hr_api.cannot_find_prog_unit then
1599 hr_api.cannot_find_prog_unit_error
1600 (p_module_name => 'UPDATE_ELEMENT_ENTRY'
1601 ,p_hook_type => 'BP'
1602 );
1603 end;
1604 --
1605 -- Validation in addition to Row Handlers
1606 -- 1. Ensure that the DateTrack update mode is valid
1607 --
1608 dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_update_mode);
1609 --
1610 -- Bugfix 2646060
1611 -- Fetch the element_link costable_type and ensure that the
1612 -- element_link is costable if cost_allocation_keyflex_id is not null
1613 --
1614 OPEN c_link_details(l_element_entry_id, l_effective_date);
1615 FETCH c_link_details INTO l_costable_type;
1616 CLOSE c_link_details;
1617 --
1618 IF l_costable_type = 'N'
1619 AND p_cost_allocation_keyflex_id IS NOT NULL
1620 AND p_cost_allocation_keyflex_id <> hr_api.g_number THEN
1621 --
1622 hr_utility.set_message(801,'HR_7453_PLK_NON_COSTABLE_ELE');
1623 hr_utility.set_warning;
1624 --
1625 END IF;
1626 --
1627 if g_debug then
1628 hr_utility.set_location(l_proc, 7);
1629 end if;
1630 --
1631 -- Process Logic
1632 -- 1. If updating entries created by MIX - creator_type = 'H' - the creator
1633 -- id must be updated to NULL to stop MIX Rollbacks occurring.
1634 -- 1. Lock the required row
1635 -- 2. Call update element entry
1636 --
1640 CLOSE c_entry_details;
1637 OPEN c_entry_details;
1638 FETCH c_entry_details
1639 INTO l_creator_type;
1641
1642 IF l_creator_type = 'H' THEN
1643 l_creator_type := 'F';
1644 IF p_creator_id = hr_api.g_number THEN
1645 l_creator_id := NULL;
1646 END IF;
1647 END IF;
1648
1649 IF p_input_value_id1 IS NOT NULL AND
1650 p_entry_value1 IS NOT NULL THEN
1651 l_entry_value1 := pay_ele_shd.convert_lookups(p_input_value_id1, p_entry_value1, p_effective_date);
1652 END IF;
1653 IF p_input_value_id2 IS NOT NULL AND
1654 p_entry_value2 IS NOT NULL THEN
1655 l_entry_value2 := pay_ele_shd.convert_lookups(p_input_value_id2, p_entry_value2, p_effective_date);
1656 END IF;
1657 IF p_input_value_id3 IS NOT NULL AND
1658 p_entry_value3 IS NOT NULL THEN
1659 l_entry_value3 := pay_ele_shd.convert_lookups(p_input_value_id3, p_entry_value3, p_effective_date);
1660 END IF;
1661 IF p_input_value_id4 IS NOT NULL AND
1662 p_entry_value4 IS NOT NULL THEN
1663 l_entry_value4 := pay_ele_shd.convert_lookups(p_input_value_id4, p_entry_value4, p_effective_date);
1664 END IF;
1665 IF p_input_value_id5 IS NOT NULL AND
1666 p_entry_value5 IS NOT NULL THEN
1667 l_entry_value5 := pay_ele_shd.convert_lookups(p_input_value_id5, p_entry_value5, p_effective_date);
1668 END IF;
1669 IF p_input_value_id6 IS NOT NULL AND
1670 p_entry_value6 IS NOT NULL THEN
1671 l_entry_value6 := pay_ele_shd.convert_lookups(p_input_value_id6, p_entry_value6, p_effective_date);
1672 END IF;
1673 IF p_input_value_id7 IS NOT NULL AND
1674 p_entry_value7 IS NOT NULL THEN
1675 l_entry_value7 := pay_ele_shd.convert_lookups(p_input_value_id7, p_entry_value7, p_effective_date);
1676 END IF;
1677 IF p_input_value_id8 IS NOT NULL AND
1678 p_entry_value8 IS NOT NULL THEN
1679 l_entry_value8 := pay_ele_shd.convert_lookups(p_input_value_id8, p_entry_value8, p_effective_date);
1680 END IF;
1681 IF p_input_value_id9 IS NOT NULL AND
1682 p_entry_value9 IS NOT NULL THEN
1683 l_entry_value9 := pay_ele_shd.convert_lookups(p_input_value_id9, p_entry_value9, p_effective_date);
1684 END IF;
1685 IF p_input_value_id10 IS NOT NULL AND
1686 p_entry_value10 IS NOT NULL THEN
1687 l_entry_value10 := pay_ele_shd.convert_lookups(p_input_value_id10, p_entry_value10, p_effective_date);
1688 END IF;
1689 IF p_input_value_id11 IS NOT NULL AND
1690 p_entry_value11 IS NOT NULL THEN
1691 l_entry_value11 := pay_ele_shd.convert_lookups(p_input_value_id11, p_entry_value11, p_effective_date);
1692 END IF;
1693 IF p_input_value_id12 IS NOT NULL AND
1694 p_entry_value12 IS NOT NULL THEN
1695 l_entry_value12 := pay_ele_shd.convert_lookups(p_input_value_id12, p_entry_value12, p_effective_date);
1696 END IF;
1697 IF p_input_value_id13 IS NOT NULL AND
1698 p_entry_value13 IS NOT NULL THEN
1699 l_entry_value13 := pay_ele_shd.convert_lookups(p_input_value_id13, p_entry_value13, p_effective_date);
1700 END IF;
1701 IF p_input_value_id14 IS NOT NULL AND
1702 p_entry_value14 IS NOT NULL THEN
1703 l_entry_value14 := pay_ele_shd.convert_lookups(p_input_value_id14, p_entry_value14, p_effective_date);
1704 END IF;
1705 IF p_input_value_id15 IS NOT NULL AND
1706 p_entry_value15 IS NOT NULL THEN
1707 l_entry_value15 := pay_ele_shd.convert_lookups(p_input_value_id15, p_entry_value15, p_effective_date);
1708 END IF;
1709 --
1710
1711 update_validate(p_element_entry_id => p_element_entry_id
1712 ,p_effective_date => l_effective_date
1713 ,p_creator_type => p_creator_type
1714 ,p_creator_id => p_creator_id
1715 ,p_personal_payment_method_id => p_personal_payment_method_id
1716 ,p_input_value_id1 => p_input_value_id1
1717 ,p_input_value_id2 => p_input_value_id2
1718 ,p_input_value_id3 => p_input_value_id3
1719 ,p_input_value_id4 => p_input_value_id4
1720 ,p_input_value_id5 => p_input_value_id5
1721 ,p_input_value_id6 => p_input_value_id6
1722 ,p_input_value_id7 => p_input_value_id7
1723 ,p_input_value_id8 => p_input_value_id8
1724 ,p_input_value_id9 => p_input_value_id9
1725 ,p_input_value_id10 => p_input_value_id10
1726 ,p_input_value_id11 => p_input_value_id11
1727 ,p_input_value_id12 => p_input_value_id12
1728 ,p_input_value_id13 => p_input_value_id13
1729 ,p_input_value_id14 => p_input_value_id14
1730 ,p_input_value_id15 => p_input_value_id15
1731 ,p_entry_value1 => p_entry_value1
1732 ,p_entry_value2 => p_entry_value2
1733 ,p_entry_value3 => p_entry_value3
1734 ,p_entry_value4 => p_entry_value4
1735 ,p_entry_value5 => p_entry_value5
1736 ,p_entry_value6 => p_entry_value6
1737 ,p_entry_value7 => p_entry_value7
1738 ,p_entry_value8 => p_entry_value8
1739 ,p_entry_value9 => p_entry_value9
1740 ,p_entry_value10 => p_entry_value10
1741 ,p_entry_value11 => p_entry_value11
1742 ,p_entry_value12 => p_entry_value12
1743 ,p_entry_value13 => p_entry_value13
1744 ,p_entry_value14 => p_entry_value14
1745 ,p_entry_value15 => p_entry_value15
1746 );
1747
1748
1749 --
1753 ,p_datetrack_mode => p_datetrack_update_mode
1750 pay_ele_shd.lck(p_effective_date => l_effective_date
1751 ,p_element_entry_id => l_element_entry_id
1752 ,p_object_version_number => l_object_version_number
1754 ,p_validation_start_date => l_validation_start_date
1755 ,p_validation_end_date => l_validation_end_date);
1756 --
1757 hr_entry_api.update_element_entry
1758 (
1759 p_dt_update_mode => p_datetrack_update_mode,
1760 p_session_date => l_effective_date,
1761 p_check_for_update => 'Y', -- Bug 7378345
1762 p_element_entry_id => l_element_entry_id,
1763 p_cost_allocation_keyflex_id => p_cost_allocation_keyflex_id,
1764 p_updating_action_id => p_updating_action_id,
1765 p_updating_action_type => p_updating_action_type,
1766 p_original_entry_id => p_original_entry_id,
1767 p_creator_type => p_creator_type,
1768 p_comment_id => p_comment_id,
1769 p_creator_id => p_creator_id,
1770 p_reason => p_reason,
1771 p_subpriority => p_subpriority,
1772 p_date_earned => l_date_earned,
1773 p_personal_payment_method_id => p_personal_payment_method_id,
1774 p_attribute_category => p_attribute_category,
1775 p_attribute1 => p_attribute1,
1776 p_attribute2 => p_attribute2,
1777 p_attribute3 => p_attribute3,
1778 p_attribute4 => p_attribute4,
1779 p_attribute5 => p_attribute5,
1780 p_attribute6 => p_attribute6,
1781 p_attribute7 => p_attribute7,
1782 p_attribute8 => p_attribute8,
1783 p_attribute9 => p_attribute9,
1784 p_attribute10 => p_attribute10,
1785 p_attribute11 => p_attribute11,
1786 p_attribute12 => p_attribute12,
1787 p_attribute13 => p_attribute13,
1788 p_attribute14 => p_attribute14,
1789 p_attribute15 => p_attribute15,
1790 p_attribute16 => p_attribute16,
1791 p_attribute17 => p_attribute17,
1792 p_attribute18 => p_attribute18,
1793 p_attribute19 => p_attribute19,
1794 p_attribute20 => p_attribute20,
1795 p_input_value_id1 => p_input_value_id1,
1796 p_input_value_id2 => p_input_value_id2,
1797 p_input_value_id3 => p_input_value_id3,
1798 p_input_value_id4 => p_input_value_id4,
1799 p_input_value_id5 => p_input_value_id5,
1800 p_input_value_id6 => p_input_value_id6,
1801 p_input_value_id7 => p_input_value_id7,
1802 p_input_value_id8 => p_input_value_id8,
1803 p_input_value_id9 => p_input_value_id9,
1804 p_input_value_id10 => p_input_value_id10,
1805 p_input_value_id11 => p_input_value_id11,
1806 p_input_value_id12 => p_input_value_id12,
1807 p_input_value_id13 => p_input_value_id13,
1808 p_input_value_id14 => p_input_value_id14,
1809 p_input_value_id15 => p_input_value_id15,
1810 p_entry_value1 => l_entry_value1,
1811 p_entry_value2 => l_entry_value2,
1812 p_entry_value3 => l_entry_value3,
1813 p_entry_value4 => l_entry_value4,
1814 p_entry_value5 => l_entry_value5,
1815 p_entry_value6 => l_entry_value6,
1816 p_entry_value7 => l_entry_value7,
1817 p_entry_value8 => l_entry_value8,
1818 p_entry_value9 => l_entry_value9,
1819 p_entry_value10 => l_entry_value10,
1820 p_entry_value11 => l_entry_value11,
1821 p_entry_value12 => l_entry_value12,
1822 p_entry_value13 => l_entry_value13,
1823 p_entry_value14 => l_entry_value14,
1824 p_entry_value15 => l_entry_value15,
1825 p_entry_information_category => p_entry_information_category,
1826 p_entry_information1 => p_entry_information1,
1827 p_entry_information2 => p_entry_information2,
1828 p_entry_information3 => p_entry_information3,
1829 p_entry_information4 => p_entry_information4,
1830 p_entry_information5 => p_entry_information5,
1831 p_entry_information6 => p_entry_information6,
1832 p_entry_information7 => p_entry_information7,
1833 p_entry_information8 => p_entry_information8,
1834 p_entry_information9 => p_entry_information9,
1835 p_entry_information10 => p_entry_information10,
1836 p_entry_information11 => p_entry_information11,
1837 p_entry_information12 => p_entry_information12,
1838 p_entry_information13 => p_entry_information13,
1839 p_entry_information14 => p_entry_information14,
1840 p_entry_information15 => p_entry_information15,
1841 p_entry_information16 => p_entry_information16,
1842 p_entry_information17 => p_entry_information17,
1843 p_entry_information18 => p_entry_information18,
1844 p_entry_information19 => p_entry_information19,
1845 p_entry_information20 => p_entry_information20,
1846 p_entry_information21 => p_entry_information21,
1847 p_entry_information22 => p_entry_information22,
1848 p_entry_information23 => p_entry_information23,
1849 p_entry_information24 => p_entry_information24,
1850 p_entry_information25 => p_entry_information25,
1851 p_entry_information26 => p_entry_information26,
1852 p_entry_information27 => p_entry_information27,
1853 p_entry_information28 => p_entry_information28,
1854 p_entry_information29 => p_entry_information29,
1855 p_entry_information30 => p_entry_information30,
1856 p_override_user_ent_chk => p_override_user_ent_chk
1857 );
1858 --
1859 if g_debug then
1860 hr_utility.set_location(l_proc, 8);
1861 end if;
1862 --
1863 -- Get all output arguments
1864 --
1865 if g_debug then
1866 hr_utility.set_location(l_proc, 9);
1867 end if;
1868 --
1869 OPEN C_Output_Variables;
1870 FETCH C_Output_Variables
1871 INTO l_effective_start_date,
1872 l_effective_end_date,
1876 -- Call After Process User Hook
1873 l_object_version_number;
1874 CLOSE C_Output_Variables;
1875 --
1877 --
1878 begin
1879 pay_element_entry_bk2.update_element_entry_a
1880 (p_datetrack_update_mode => p_datetrack_update_mode
1881 ,p_effective_date => l_effective_date
1882 ,p_business_group_id => p_business_group_id
1883 ,p_element_entry_id => p_element_entry_id
1884 ,p_object_version_number => l_object_version_number
1885 ,p_cost_allocation_keyflex_id => p_cost_allocation_keyflex_id
1886 ,p_updating_action_id => p_updating_action_id
1887 ,p_updating_action_type => p_updating_action_type
1888 ,p_original_entry_id => p_original_entry_id
1889 ,p_creator_type => p_creator_type
1890 ,p_comment_id => p_comment_id
1891 ,p_creator_id => p_creator_id
1892 ,p_reason => p_reason
1893 ,p_subpriority => p_subpriority
1894 ,p_date_earned => l_date_earned
1895 ,p_personal_payment_method_id => p_personal_payment_method_id
1896 ,p_attribute_category => p_attribute_category
1897 ,p_attribute1 => p_attribute1
1898 ,p_attribute2 => p_attribute2
1899 ,p_attribute3 => p_attribute3
1900 ,p_attribute4 => p_attribute4
1901 ,p_attribute5 => p_attribute5
1902 ,p_attribute6 => p_attribute6
1903 ,p_attribute7 => p_attribute7
1904 ,p_attribute8 => p_attribute8
1905 ,p_attribute9 => p_attribute9
1906 ,p_attribute10 => p_attribute10
1907 ,p_attribute11 => p_attribute11
1908 ,p_attribute12 => p_attribute12
1909 ,p_attribute13 => p_attribute13
1910 ,p_attribute14 => p_attribute14
1911 ,p_attribute15 => p_attribute15
1912 ,p_attribute16 => p_attribute16
1913 ,p_attribute17 => p_attribute17
1914 ,p_attribute18 => p_attribute18
1915 ,p_attribute19 => p_attribute19
1916 ,p_attribute20 => p_attribute20
1917 ,p_input_value_id1 => p_input_value_id1
1918 ,p_input_value_id2 => p_input_value_id2
1919 ,p_input_value_id3 => p_input_value_id3
1920 ,p_input_value_id4 => p_input_value_id4
1921 ,p_input_value_id5 => p_input_value_id5
1922 ,p_input_value_id6 => p_input_value_id6
1923 ,p_input_value_id7 => p_input_value_id7
1924 ,p_input_value_id8 => p_input_value_id8
1925 ,p_input_value_id9 => p_input_value_id9
1926 ,p_input_value_id10 => p_input_value_id10
1927 ,p_input_value_id11 => p_input_value_id11
1928 ,p_input_value_id12 => p_input_value_id12
1929 ,p_input_value_id13 => p_input_value_id13
1930 ,p_input_value_id14 => p_input_value_id14
1931 ,p_input_value_id15 => p_input_value_id15
1932 ,p_entry_value1 => p_entry_value1
1933 ,p_entry_value2 => p_entry_value2
1934 ,p_entry_value3 => p_entry_value3
1935 ,p_entry_value4 => p_entry_value4
1936 ,p_entry_value5 => p_entry_value5
1937 ,p_entry_value6 => p_entry_value6
1938 ,p_entry_value7 => p_entry_value7
1939 ,p_entry_value8 => p_entry_value8
1940 ,p_entry_value9 => p_entry_value9
1941 ,p_entry_value10 => p_entry_value10
1942 ,p_entry_value11 => p_entry_value11
1943 ,p_entry_value12 => p_entry_value12
1944 ,p_entry_value13 => p_entry_value13
1945 ,p_entry_value14 => p_entry_value14
1946 ,p_entry_value15 => p_entry_value15
1947 ,p_entry_information_category => p_entry_information_category
1948 ,p_entry_information1 => p_entry_information1
1949 ,p_entry_information2 => p_entry_information2
1950 ,p_entry_information3 => p_entry_information3
1951 ,p_entry_information4 => p_entry_information4
1952 ,p_entry_information5 => p_entry_information5
1953 ,p_entry_information6 => p_entry_information6
1954 ,p_entry_information7 => p_entry_information7
1955 ,p_entry_information8 => p_entry_information8
1956 ,p_entry_information9 => p_entry_information9
1957 ,p_entry_information10 => p_entry_information10
1958 ,p_entry_information11 => p_entry_information11
1959 ,p_entry_information12 => p_entry_information12
1960 ,p_entry_information13 => p_entry_information13
1961 ,p_entry_information14 => p_entry_information14
1962 ,p_entry_information15 => p_entry_information15
1963 ,p_entry_information16 => p_entry_information16
1964 ,p_entry_information17 => p_entry_information17
1965 ,p_entry_information18 => p_entry_information18
1966 ,p_entry_information19 => p_entry_information19
1967 ,p_entry_information20 => p_entry_information20
1968 ,p_entry_information21 => p_entry_information21
1969 ,p_entry_information22 => p_entry_information22
1970 ,p_entry_information23 => p_entry_information23
1971 ,p_entry_information24 => p_entry_information24
1972 ,p_entry_information25 => p_entry_information25
1973 ,p_entry_information26 => p_entry_information26
1974 ,p_entry_information27 => p_entry_information27
1975 ,p_entry_information28 => p_entry_information28
1976 ,p_entry_information29 => p_entry_information29
1977 ,p_entry_information30 => p_entry_information30
1978 ,p_override_user_ent_chk => p_override_user_ent_chk
1979 ,p_effective_start_date => l_effective_start_date
1980 ,p_effective_end_date => l_effective_end_date
1981 ,p_update_warning => p_update_warning
1982 );
1983 exception
1984 when hr_api.cannot_find_prog_unit then
1985 hr_api.cannot_find_prog_unit_error
1986 (p_module_name => 'UPDATE_ELEMENT_ENTRY'
1987 ,p_hook_type => 'AP'
1988 );
1989 end;
1990 --
1991 -- When in validation only mode raise the Validate_Enabled exception
1992 --
1993 IF p_validate THEN
1994 RAISE hr_api.validate_enabled;
1995 END IF;
1996 --
1997 -- Set all output arguments
1998 --
1999 -- p_element_entry_id := l_element_entry_id;
2000 p_object_version_number := l_object_version_number;
2001 p_effective_start_date := l_effective_start_date;
2002 p_effective_end_date := l_effective_end_date;
2003 -- p_update_warning := <local_var_set_in_process_logic>
2004 --
2005 if g_debug then
2006 hr_utility.set_location(' Leaving:'||l_proc, 11);
2007 end if;
2008 EXCEPTION
2009 WHEN hr_api.validate_enabled THEN
2010 --
2011 -- As the Validate_Enabled exception has been raised
2012 -- we must rollback to the savepoint
2013 --
2014 ROLLBACK TO update_element_entry;
2015 --
2016 -- Only set output warning arguments
2017 -- (Any key or derived arguments must be set to null
2018 -- when validation only mode is being used.)
2019 --
2020 -- p_element_entry_id := NULL;
2021 p_object_version_number := l_object_version_number;
2022 -- p_update_warning := <local_var_set_in_process_logic>
2023 p_effective_start_date := NULL;
2024 p_effective_end_date := NULL;
2025 --
2026 WHEN OTHERS THEN
2027 --
2028 -- A validation or unexpected error has occurred
2029 --
2030 -- Added as part of fix to bug 632479
2031 --
2032 ROLLBACK TO update_element_entry;
2033 p_object_version_number := l_object_version_number;
2034 p_effective_start_date := null;
2035 p_effective_end_date := null;
2036 p_update_warning := null;
2037 raise;
2038 --
2039 -- End of fix.
2040 --
2041 if g_debug then
2042 hr_utility.set_location(' Leaving:'||l_proc, 12);
2043 end if;
2044 END update_element_entry;
2045 --
2046 END pay_element_entry_api;