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