[Home] [Help]
PACKAGE BODY: APPS.PAY_ELE_SHD
Source
1 Package Body pay_ele_shd as
2 /* $Header: pyelerhi.pkb 120.1 2005/05/30 05:19:19 rajeesha noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_ele_shd.'; -- Global package name
9 g_counter number;
10 --
11 -- ----------------------------------------------------------------------------
12 -- |------------------------< return_api_dml_status >-------------------------|
13 -- ----------------------------------------------------------------------------
14 Function return_api_dml_status Return Boolean Is
15 --
16 Begin
17 --
18 Return (nvl(g_api_dml, false));
19 --
20 End return_api_dml_status;
21 --
22 -- ----------------------------------------------------------------------------
23 -- |---------------------------< constraint_error >---------------------------|
24 -- ----------------------------------------------------------------------------
25 Procedure constraint_error
26 (p_constraint_name in all_constraints.constraint_name%TYPE
27 ) Is
28 --
29 l_proc varchar2(72) := g_package||'constraint_error';
30 --
31 Begin
32 --
33 If (p_constraint_name = 'PAY_ELEMENT_ENTRIES_F_FK1') Then
34 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
35 fnd_message.set_token('PROCEDURE', l_proc);
36 fnd_message.set_token('STEP','5');
37 fnd_message.raise_error;
38 ElsIf (p_constraint_name = 'PAY_ELEMENT_ENTRIES_F_FK3') Then
39 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
40 fnd_message.set_token('PROCEDURE', l_proc);
41 fnd_message.set_token('STEP','10');
42 fnd_message.raise_error;
43 ElsIf (p_constraint_name = 'PAY_ELEMENT_ENTRIES_F_PK') Then
44 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
45 fnd_message.set_token('PROCEDURE', l_proc);
46 fnd_message.set_token('STEP','15');
47 fnd_message.raise_error;
48 ElsIf (p_constraint_name = 'PAY_EL_ENTRY_CREATOR_TYPE_CHK') Then
49 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
50 fnd_message.set_token('PROCEDURE', l_proc);
51 fnd_message.set_token('STEP','20');
52 fnd_message.raise_error;
53 ElsIf (p_constraint_name = 'PAY_EL_ENTRY_ENTRY_TYPE_CHK') Then
54 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
55 fnd_message.set_token('PROCEDURE', l_proc);
56 fnd_message.set_token('STEP','25');
57 fnd_message.raise_error;
58 Else
59 fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
60 fnd_message.set_token('PROCEDURE', l_proc);
61 fnd_message.set_token('CONSTRAINT_NAME', p_constraint_name);
62 fnd_message.raise_error;
63 End If;
64 --
65 End constraint_error;
66 --
67 -- ----------------------------------------------------------------------------
68 -- |-----------------------------< api_updating >-----------------------------|
69 -- ----------------------------------------------------------------------------
70 Function api_updating
71 (p_effective_date in date
72 ,p_element_entry_id in number
73 ,p_object_version_number in number
74 ) Return Boolean Is
75 --
76 -- Cursor selects the 'current' row from the HR Schema
77 --
78 Cursor C_Sel1 is
79 select
80 element_entry_id
81 ,effective_start_date
82 ,effective_end_date
83 ,cost_allocation_keyflex_id
84 ,assignment_id
85 ,updating_action_id
86 ,updating_action_type
87 ,element_link_id
88 ,original_entry_id
89 ,creator_type
90 ,entry_type
91 ,comment_id
92 ,null
93 ,creator_id
94 ,reason
95 ,target_entry_id
96 ,attribute_category
97 ,attribute1
98 ,attribute2
99 ,attribute3
100 ,attribute4
101 ,attribute5
102 ,attribute6
103 ,attribute7
104 ,attribute8
105 ,attribute9
106 ,attribute10
107 ,attribute11
108 ,attribute12
109 ,attribute13
110 ,attribute14
111 ,attribute15
112 ,attribute16
113 ,attribute17
114 ,attribute18
115 ,attribute19
116 ,attribute20
117 -- --
118 ,
119 entry_information_category,
120 entry_information1,
121 entry_information2,
122 entry_information3,
123 entry_information4,
124 entry_information5,
125 entry_information6,
126 entry_information7,
127 entry_information8,
128 entry_information9,
129 entry_information10,
130 entry_information11,
131 entry_information12,
132 entry_information13,
133 entry_information14,
134 entry_information15,
135 entry_information16,
136 entry_information17,
137 entry_information18,
138 entry_information19,
139 entry_information20,
140 entry_information21,
141 entry_information22,
142 entry_information23,
143 entry_information24,
144 entry_information25,
145 entry_information26,
146 entry_information27,
147 entry_information28,
148 entry_information29,
149 entry_information30
150 ,subpriority
151 ,personal_payment_method_id
152 ,date_earned
153 ,object_version_number
154 ,source_id
155 ,balance_adj_cost_flag
156 ,element_type_id
157 ,all_entry_values_null
158 from pay_element_entries_f
159 where element_entry_id = p_element_entry_id
160 and p_effective_date
161 between effective_start_date and effective_end_date;
162 --
163
164 l_fct_ret boolean;
165 --
166 Begin
167 --
168 If (p_effective_date is null or
169 p_element_entry_id is null or
170 p_object_version_number is null) Then
171 --
172 -- One of the primary key arguments is null therefore we must
173 -- set the returning function value to false
174 --
175 l_fct_ret := false;
176 Else
177 If (p_element_entry_id =
178 pay_ele_shd.g_old_rec.element_entry_id and
179 p_object_version_number =
180 pay_ele_shd.g_old_rec.object_version_number) Then
181 --
182 -- The g_old_rec is current therefore we must
183 -- set the returning function to true
184 --
185 l_fct_ret := true;
186 Else
187 --
188 -- Select the current row
189 --
190 Open C_Sel1;
191 Fetch C_Sel1 Into pay_ele_shd.g_old_rec;
192 If C_Sel1%notfound Then
193 Close C_Sel1;
194 --
195 -- The primary key is invalid therefore we must error
196 --
197 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
198 fnd_message.raise_error;
199 End If;
200 Close C_Sel1;
201
202 -- 11-NOV-03
203 -- Caching for Hard calls to DYT_PKG removed
204
205 If (p_object_version_number
206 <> pay_ele_shd.g_old_rec.object_version_number) Then
207 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
208 fnd_message.raise_error;
209 End If;
210 l_fct_ret := true;
211 End If;
212 End If;
213 Return (l_fct_ret);
214 --
215 End api_updating;
216 --
217 -- ----------------------------------------------------------------------------
218 -- |---------------------------< find_dt_upd_modes >--------------------------|
219 -- ----------------------------------------------------------------------------
220 Procedure find_dt_upd_modes
221 (p_effective_date in date
222 ,p_base_key_value in number
223 ,p_correction out nocopy boolean
224 ,p_update out nocopy boolean
225 ,p_update_override out nocopy boolean
226 ,p_update_change_insert out nocopy boolean
227 ) is
228 --
229 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
230 --
231 l_type varchar2(1);
232 --
233 Cursor C_chk_type Is
234 select pet.processing_type
235 from pay_element_types_f pet,
236 pay_element_entries_f pee,
237 pay_element_links_f pel
238 where pet.element_type_id = pel.element_type_id
239 and pel.element_link_id = pee.element_link_id
240 and pee.element_entry_id = p_base_key_value
241 and p_effective_date
242 between pee.effective_start_date and pee.effective_end_date
243 and p_effective_date
244 between pel.effective_start_date and pel.effective_end_date
245 and p_effective_date
246 between pet.effective_start_date and pet.effective_end_date;
247 --
248 Begin
249 hr_utility.set_location('Entering:'||l_proc, 5);
250 --
251 -- Call the corresponding datetrack api
252 --
253 dt_api.find_dt_upd_modes
254 (p_effective_date => p_effective_date
255 ,p_base_table_name => 'pay_element_entries_f'
256 ,p_base_key_column => 'element_entry_id'
257 ,p_base_key_value => p_base_key_value
258 ,p_correction => p_correction
259 ,p_update => p_update
260 ,p_update_override => p_update_override
261 ,p_update_change_insert => p_update_change_insert
262 );
263 --
264 -- Entity modifications
265 -- For UPDATES: All Datetrack update functions can be performed for
266 -- Recurring Entries, while only 'CORRECTION' is valid for Non Recurring
267 -- Entries (since they are only valid over a single Payroll period)
268 --
269 Open C_chk_type;
270 Fetch C_chk_type Into l_type;
271 If C_chk_type%notfound then
272 Close C_chk_type;
273 hr_utility.set_message(800, 'HR_6153_ALL_PROCEDURE_FAIL');
274 hr_utility.set_message_token('PROCEDURE', l_proc);
275 hr_utility.set_message_token('STEP','5');
276 hr_utility.raise_error;
277 End If;
278 Close C_chk_type;
279 --
280 if l_type = 'N' then
281 p_update := FALSE;
282 p_update_override := FALSE;
283 p_update_change_insert := FALSE;
284 end if;
285 --
286 hr_utility.set_location(' Leaving:'||l_proc, 10);
287 End find_dt_upd_modes;
288 --
289 -- ----------------------------------------------------------------------------
290 -- |---------------------------< find_dt_del_modes >--------------------------|
291 -- ----------------------------------------------------------------------------
292 Procedure find_dt_del_modes
293 (p_effective_date in date
294 ,p_base_key_value in number
295 ,p_zap out nocopy boolean
296 ,p_delete out nocopy boolean
297 ,p_future_change out nocopy boolean
298 ,p_delete_next_change out nocopy boolean
299 ) is
300 --
301 l_proc varchar2(72) := g_package||'find_dt_del_modes';
302 --
303 l_parent_key_value1 number;
304 l_parent_key_value2 number;
305 l_type varchar2(1);
306 --
307 Cursor C_Sel1 Is
308 select
309 t.assignment_id
310 ,t.element_link_id
311 from pay_element_entries_f t
312 where t.element_entry_id = p_base_key_value
313 and p_effective_date
314 between t.effective_start_date and t.effective_end_date;
315 --
316 Cursor C_chk_type Is
317 select pet.processing_type
318 from pay_element_types_f pet,
319 pay_element_entries_f pee,
320 pay_element_links_f pel
321 where pet.element_type_id = pel.element_type_id
322 and pel.element_link_id = pee.element_link_id
323 and pee.element_entry_id = p_base_key_value
324 and p_effective_date
325 between pee.effective_start_date and pee.effective_end_date
326 and p_effective_date
327 between pel.effective_start_date and pel.effective_end_date
328 and p_effective_date
329 between pet.effective_start_date and pet.effective_end_date;
330 --
331 Begin
332 hr_utility.set_location('Entering:'||l_proc, 5);
333 Open C_sel1;
334 Fetch C_Sel1 Into
335 l_parent_key_value1
336 ,l_parent_key_value2;
337 If C_Sel1%NOTFOUND then
338 Close C_Sel1;
339 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
340 fnd_message.set_token('PROCEDURE',l_proc);
341 fnd_message.set_token('STEP','10');
342 fnd_message.raise_error;
343 End If;
344 Close C_Sel1;
345 --
346 -- Call the corresponding datetrack api
347 --
348 dt_api.find_dt_del_modes
349 (p_effective_date => p_effective_date
350 ,p_base_table_name => 'pay_element_entries_f'
351 ,p_base_key_column => 'element_entry_id'
352 ,p_base_key_value => p_base_key_value
353 ,p_parent_table_name1 => 'per_all_assignments_f'
354 ,p_parent_key_column1 => 'assignment_id'
355 ,p_parent_key_value1 => l_parent_key_value1
356 ,p_parent_table_name2 => 'pay_element_links_f'
357 ,p_parent_key_column2 => 'element_link_id'
358 ,p_parent_key_value2 => l_parent_key_value2
359 ,p_zap => p_zap
360 ,p_delete => p_delete
361 ,p_future_change => p_future_change
362 ,p_delete_next_change => p_delete_next_change
363 );
364 --
365 -- Entity specific modifications
366 --
367 -- For DELETES: All Datetrack delete functions can be performed for
368 -- Recurring Entries while only 'Purge' is valid for Non Recurring Entries
369 -- since these Entries are only valid over a single Payroll period.
370 --
371 Open C_chk_type;
372 Fetch C_chk_type Into l_type;
373 If C_chk_type%notfound then
374 Close C_chk_type;
375 hr_utility.set_message(800, 'HR_6153_ALL_PROCEDURE_FAIL');
376 hr_utility.set_message_token('PROCEDURE', l_proc);
377 hr_utility.set_message_token('STEP','5');
378 hr_utility.raise_error;
379 End If;
380 Close C_chk_type;
381 --
382 if l_type = 'N' then
383 p_delete := FALSE;
384 p_future_change := FALSE;
385 p_delete_next_change := FALSE;
386 end if;
387 --
388 hr_utility.set_location(' Leaving:'||l_proc, 10);
389 End find_dt_del_modes;
390 --
394 Procedure upd_effective_end_date
391 -- ----------------------------------------------------------------------------
392 -- |-----------------------< upd_effective_end_date >-------------------------|
393 -- ----------------------------------------------------------------------------
395 (p_effective_date in date
396 ,p_base_key_value in number
397 ,p_new_effective_end_date in date
398 ,p_validation_start_date in date
399 ,p_validation_end_date in date
400 ,p_object_version_number out nocopy number
401 ) is
402 --
403 l_proc varchar2(72) := g_package||'upd_effective_end_date';
404 l_object_version_number number;
405 --
406 Begin
407 hr_utility.set_location('Entering:'||l_proc, 5);
408 --
409 -- Because we are updating a row we must get the next object
410 -- version number.
411 --
412 l_object_version_number :=
413 dt_api.get_object_version_number
414 (p_base_table_name => 'pay_element_entries_f'
415 ,p_base_key_column => 'element_entry_id'
416 ,p_base_key_value => p_base_key_value
417 );
418 --
419 hr_utility.set_location(l_proc, 10);
420 pay_ele_shd.g_api_dml := true; -- Set the api dml status
421 --
422 -- Update the specified datetrack row setting the effective
423 -- end date to the specified new effective end date.
424 --
425 update pay_element_entries_f t
426 set t.effective_end_date = p_new_effective_end_date
427 , t.object_version_number = l_object_version_number
428 where t.element_entry_id = p_base_key_value
429 and p_effective_date
430 between t.effective_start_date and t.effective_end_date;
431 --
432 pay_ele_shd.g_api_dml := false; -- Unset the api dml status
433 p_object_version_number := l_object_version_number;
434 hr_utility.set_location(' Leaving:'||l_proc, 15);
435 --
436 Exception
437 When Others Then
438 pay_ele_shd.g_api_dml := false; -- Unset the api dml status
439 Raise;
440 --
441 End upd_effective_end_date;
442 --
443 -- ----------------------------------------------------------------------------
444 -- |---------------------------------< lck >----------------------------------|
445 -- ----------------------------------------------------------------------------
446 Procedure lck
447 (p_effective_date in date
448 ,p_datetrack_mode in varchar2
449 ,p_element_entry_id in number
450 ,p_object_version_number in number
451 ,p_validation_start_date out nocopy date
452 ,p_validation_end_date out nocopy date
453 ) is
454 --
455 l_proc varchar2(72) := g_package||'lck';
456 l_validation_start_date date;
457 l_validation_end_date date;
458 l_argument varchar2(30);
459
460
461 v_assignment_id number;
462 cur_id number;
463 --
464 -- Cursor C_Sel1 selects the current locked row as of session date
465 -- ensuring that the object version numbers match.
466 --
467 Cursor C_Sel1 is
468 select
469 element_entry_id
470 ,effective_start_date
471 ,effective_end_date
472 ,cost_allocation_keyflex_id
473 ,assignment_id
474 ,updating_action_id
475 ,updating_action_type
476 ,element_link_id
477 ,original_entry_id
478 ,creator_type
479 ,entry_type
480 ,comment_id
481 ,null
482 ,creator_id
483 ,reason
484 ,target_entry_id
485 ,attribute_category
486 ,attribute1
487 ,attribute2
488 ,attribute3
489 ,attribute4
490 ,attribute5
491 ,attribute6
492 ,attribute7
493 ,attribute8
494 ,attribute9
495 ,attribute10
496 ,attribute11
497 ,attribute12
498 ,attribute13
499 ,attribute14
500 ,attribute15
501 ,attribute16
502 ,attribute17
503 ,attribute18
504 ,attribute19
505 ,attribute20
506 -- --
507 ,
508 entry_information_category,
509 entry_information1,
510 entry_information2,
511 entry_information3,
512 entry_information4,
513 entry_information5,
514 entry_information6,
515 entry_information7,
516 entry_information8,
517 entry_information9,
518 entry_information10,
519 entry_information11,
520 entry_information12,
521 entry_information13,
522 entry_information14,
523 entry_information15,
524 entry_information16,
525 entry_information17,
526 entry_information18,
527 entry_information19,
528 entry_information20,
529 entry_information21,
530 entry_information22,
531 entry_information23,
532 entry_information24,
533 entry_information25,
534 entry_information26,
535 entry_information27,
536 entry_information28,
537 entry_information29,
538 entry_information30
539 ,subpriority
540 ,personal_payment_method_id
541 ,date_earned
542 ,object_version_number
543 ,source_id
544 ,balance_adj_cost_flag
548 where element_entry_id = p_element_entry_id
545 ,element_type_id
546 ,all_entry_values_null
547 from pay_element_entries_f
549 and p_effective_date
550 between effective_start_date and effective_end_date
551 for update nowait;
552 --
553 -- Cursor C_Sel3 select comment text
554 --
555 Cursor C_Sel3 is
556 select hc.comment_text
557 from hr_comments hc
558 where hc.comment_id = pay_ele_shd.g_old_rec.comment_id;
559 --
560 -- Assignment locking cursor
561 --
562 cursor c_sel4 is
563 select assignment_id
564 from per_all_assignments_f
565 where assignment_id = pay_ele_shd.g_old_rec.assignment_id
566 for update nowait;
567 --
568
569 Begin
570 hr_utility.set_location('Entering:'||l_proc, 5);
571 --
572 -- Ensure that all the mandatory arguments are not null
573 --
574 hr_api.mandatory_arg_error(p_api_name => l_proc
575 ,p_argument => 'effective_date'
576 ,p_argument_value => p_effective_date
577 );
578 --
579 hr_api.mandatory_arg_error(p_api_name => l_proc
580 ,p_argument => 'datetrack_mode'
581 ,p_argument_value => p_datetrack_mode
582 );
583 --
584 hr_api.mandatory_arg_error(p_api_name => l_proc
585 ,p_argument => 'element_entry_id'
586 ,p_argument_value => p_element_entry_id
587 );
588 --
589 hr_api.mandatory_arg_error(p_api_name => l_proc
590 ,p_argument => 'object_version_number'
591 ,p_argument_value => p_object_version_number
592 );
593
594 hr_utility.set_location('done arg error check'||l_proc, 51);
595
596 --
597 -- Check to ensure the datetrack mode is not INSERT.
598 --
599
600 If (p_datetrack_mode <> hr_api.g_insert) then
601 --
602 -- We must select and lock the current row.
603 --
604 Open C_Sel1;
605 Fetch C_Sel1 Into pay_ele_shd.g_old_rec;
606 If C_Sel1%notfound then
607 Close C_Sel1;
608 --
609 -- The primary key is invalid therefore we must error
610 --
611 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
612 fnd_message.raise_error;
613 End If;
614 Close C_Sel1;
615 hr_utility.set_location('Entering ovn check:'||l_proc, 52);
616
617 If (p_object_version_number
618 <> pay_ele_shd.g_old_rec.object_version_number) Then
619 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
620 fnd_message.raise_error;
621 End If;
622 --
623 -- Providing we are doing an update and a comment_id exists then
624 -- we select the comment text.
625 --
626 If ((pay_ele_shd.g_old_rec.comment_id is not null) and
627 (p_datetrack_mode = hr_api.g_update or
628 p_datetrack_mode = hr_api.g_correction or
629 p_datetrack_mode = hr_api.g_update_override or
630 p_datetrack_mode = hr_api.g_update_change_insert)) then
631 Open C_Sel3;
632 Fetch C_Sel3 Into pay_ele_shd.g_old_rec.comments;
633 If C_Sel3%notfound then
634 --
635 -- The comments for the specified comment_id does not exist.
636 -- We must error due to data integrity problems.
637 --
638 Close C_Sel3;
639 fnd_message.set_name('PAY', 'HR_7202_COMMENT_TEXT_NOT_EXIST');
640 fnd_message.raise_error;
641 End If;
642 Close C_Sel3;
643 End If;
644
645 --
646 -- Note, we are doing the foreign table locking ourselves now
647 -- First, lock all rows with given assignment ID on
648 -- PER_ALL_ASSIGNMENTS_F table with exclusive row locks
649 --
650 -- Lock rows in per_all_assignments_f based on passed assignment_id.
651 -- Simply select the row for update to lock it in exclusive mode
652 -- as we are taking this functionality out of the validate_dt_mode
653 -- below
654 for i in c_sel4 loop
655 null;
656 end loop;
657
658 -- We must also lock the pay_element_links_f in shared mode.
659 lock table pay_element_links_f in row share mode nowait;
660
661 --
662 -- Validate the datetrack mode mode getting the validation start
663 -- and end dates for the specified datetrack operation.
664 --
665 dt_api.validate_dt_mode
666 (p_effective_date => p_effective_date
667 ,p_datetrack_mode => p_datetrack_mode
668 ,p_base_table_name => 'pay_element_entries_f'
669 ,p_base_key_column => 'element_entry_id'
670 ,p_base_key_value => p_element_entry_id
671 ,p_parent_table_name1 => 'per_all_assignments_f'
672 ,p_parent_key_column1 => 'assignment_id'
673 ,p_parent_key_value1 => pay_ele_shd.g_old_rec.assignment_id
674 ,p_parent_table_name2 => 'pay_element_links_f'
675 ,p_parent_key_column2 => 'element_link_id'
676 ,p_parent_key_value2 => pay_ele_shd.g_old_rec.element_link_id
680 );
677 ,p_enforce_foreign_locking => false
678 ,p_validation_start_date => l_validation_start_date
679 ,p_validation_end_date => l_validation_end_date
681 Else
682 --
683 -- We are doing a datetrack 'INSERT' which is illegal within this
684 -- procedure therefore we must error (note: to lck on insert the
685 -- private procedure ins_lck should be called).
686 --
687 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
688 fnd_message.set_token('PROCEDURE', l_proc);
689 fnd_message.set_token('STEP','20');
690 fnd_message.raise_error;
691 End If;
692 --
693 -- Set the validation start and end date OUT arguments
694 --
695 p_validation_start_date := l_validation_start_date;
696 p_validation_end_date := l_validation_end_date;
697 --
698 hr_utility.set_location(' Leaving:'||l_proc, 30);
699 --
700 -- We need to trap the ORA LOCK exception
701 --
702 Exception
703 When HR_Api.Object_Locked then
704 --
705 -- The object is locked therefore we need to supply a meaningful
706 -- error message.
707 --
708 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
709 fnd_message.set_token('TABLE_NAME', 'pay_element_entries_f');
710 fnd_message.raise_error;
711 End lck;
712 --
713 -- ----------------------------------------------------------------------------
714 -- |---------------------------< convert_lookups >----------------------------|
715 -- ----------------------------------------------------------------------------
716 FUNCTION convert_lookups
717 (
718 p_input_value_id number,
719 p_entry_value varchar2,
720 p_effective_date date
721 ) RETURN VARCHAR2 IS
722 --
723 CURSOR C_Lookup IS
724 SELECT piv.lookup_type,
725 piv.value_set_id
726 FROM pay_input_values_f piv
727 WHERE piv.input_value_id = p_input_value_id
728 AND nvl(p_effective_date,sysdate)
729 between piv.effective_start_date
730 and piv.effective_end_date;
731 --
732 l_proc varchar2(72) := g_package||'convert_lookups';
733 l_lookup_type hr_lookups.lookup_type%type := NULL;
734 l_meaning varchar2(240) := NULL;
735 l_value_set_id number(10) := NULL;
736 --
737 BEGIN
738 hr_utility.set_location('Entering:'||l_proc, 5);
739 --
740 -- Validate the length of the entry
741 --
742 IF LENGTH(p_entry_value) > 60 THEN
743 hr_utility.set_location('Entering:'||l_proc, 7);
744 hr_utility.set_message(801, 'HR_7049_ELE_ENTRY_LENGTH');
745 hr_utility.raise_error;
746 END IF;
747 --
748 hr_utility.set_location('Entering:'||l_proc, 9);
749 OPEN C_Lookup;
750 FETCH C_Lookup
751 INTO l_lookup_type, l_value_set_id;
752 IF l_lookup_type IS NOT NULL THEN
753 hr_utility.set_location('Entering:'||l_proc, 11);
754 l_meaning := hr_general.decode_lookup(l_lookup_type, p_entry_value);
755 --
756 IF l_meaning IS NULL THEN
757 --
758 -- Bugfix 2678606
759 -- No matching meaning was found, therefore entry value must be
760 -- invalid. Raise appropriate error
761 --
762 hr_utility.set_message(801, 'HR_7033_ELE_ENTRY_LKUP_INVLD');
763 hr_utility.set_message_token('LOOKUP_TYPE',l_lookup_type);
764 hr_utility.raise_error;
765 --
766 END IF;
767 --
768 ELSIF l_value_set_id IS NOT NULL THEN
769 --
770 -- Enhancement 2793978
771 -- Convert value set value
772 --
773 hr_utility.set_location('Entering:'||l_proc, 12);
774 l_meaning := pay_input_values_pkg.decode_vset_value(
775 l_value_set_id,
776 p_entry_value);
777 --
778 IF l_meaning IS NULL THEN
779 --
780 -- No matching meaning was found, therefore entry value must be
781 -- invalid. Raise appropriate error.
782 --
783 hr_utility.set_message(800, 'HR_34927_ELE_ENTRY_VSET_INVLD');
784 hr_utility.set_message_token('VALUE',p_entry_value);
785 hr_utility.raise_error;
786 --
787 END IF;
788 --
789 ELSE
790 l_meaning := p_entry_value;
791 END IF;
792 CLOSE C_Lookup;
793 --
794 RETURN(l_meaning);
795 hr_utility.set_location(' Leaving:'||l_proc, 20);
796 --
797 END convert_lookups;
798 --
799 -- ----------------------------------------------------------------------------
800 -- |-----------------------------< convert_args >-----------------------------|
801 -- ----------------------------------------------------------------------------
802 Function convert_args
803 (p_element_entry_id in number
804 ,p_effective_start_date in date
805 ,p_effective_end_date in date
806 ,p_cost_allocation_keyflex_id in number
807 ,p_assignment_id in number
808 ,p_updating_action_id in number
809 ,p_updating_action_type in varchar2
810 ,p_element_link_id in number
811 ,p_original_entry_id in number
812 ,p_creator_type in varchar2
813 ,p_entry_type in varchar2
814 ,p_comment_id in number
815 ,p_comments in varchar2
816 ,p_creator_id in number
817 ,p_reason in varchar2
818 ,p_target_entry_id in number
819 ,p_attribute_category in varchar2
820 ,p_attribute1 in varchar2
821 ,p_attribute2 in varchar2
822 ,p_attribute3 in varchar2
823 ,p_attribute4 in varchar2
824 ,p_attribute5 in varchar2
825 ,p_attribute6 in varchar2
826 ,p_attribute7 in varchar2
827 ,p_attribute8 in varchar2
828 ,p_attribute9 in varchar2
829 ,p_attribute10 in varchar2
830 ,p_attribute11 in varchar2
831 ,p_attribute12 in varchar2
832 ,p_attribute13 in varchar2
833 ,p_attribute14 in varchar2
834 ,p_attribute15 in varchar2
835 ,p_attribute16 in varchar2
836 ,p_attribute17 in varchar2
837 ,p_attribute18 in varchar2
838 ,p_attribute19 in varchar2
839 ,p_attribute20 in varchar2
840 ,p_subpriority in number
841 ,p_personal_payment_method_id in number
842 ,p_date_earned in date
843 ,p_object_version_number in number
844 ,p_source_id in number
845 ,p_balance_adj_cost_flag in varchar2
846 ,p_element_type_id in number
847 ,p_all_entry_values_null in varchar2
848 )
849 Return g_rec_type is
850 --
851 l_rec g_rec_type;
852 --
853 Begin
854 --
855 -- Convert arguments into local l_rec structure.
856 --
857 l_rec.element_entry_id := p_element_entry_id;
858 l_rec.effective_start_date := p_effective_start_date;
859 l_rec.effective_end_date := p_effective_end_date;
860 l_rec.cost_allocation_keyflex_id := p_cost_allocation_keyflex_id;
861 l_rec.assignment_id := p_assignment_id;
862 l_rec.updating_action_id := p_updating_action_id;
863 l_rec.updating_action_type := p_updating_action_type;
864 l_rec.element_link_id := p_element_link_id;
865 l_rec.original_entry_id := p_original_entry_id;
866 l_rec.creator_type := p_creator_type;
867 l_rec.entry_type := p_entry_type;
868 l_rec.comment_id := p_comment_id;
869 l_rec.comments := p_comments;
870 l_rec.creator_id := p_creator_id;
871 l_rec.reason := p_reason;
872 l_rec.target_entry_id := p_target_entry_id;
873 l_rec.attribute_category := p_attribute_category;
874 l_rec.attribute1 := p_attribute1;
875 l_rec.attribute2 := p_attribute2;
876 l_rec.attribute3 := p_attribute3;
877 l_rec.attribute4 := p_attribute4;
878 l_rec.attribute5 := p_attribute5;
879 l_rec.attribute6 := p_attribute6;
880 l_rec.attribute7 := p_attribute7;
881 l_rec.attribute8 := p_attribute8;
882 l_rec.attribute9 := p_attribute9;
883 l_rec.attribute10 := p_attribute10;
884 l_rec.attribute11 := p_attribute11;
885 l_rec.attribute12 := p_attribute12;
886 l_rec.attribute13 := p_attribute13;
887 l_rec.attribute14 := p_attribute14;
888 l_rec.attribute15 := p_attribute15;
889 l_rec.attribute16 := p_attribute16;
890 l_rec.attribute17 := p_attribute17;
891 l_rec.attribute18 := p_attribute18;
892 l_rec.attribute19 := p_attribute19;
893 l_rec.attribute20 := p_attribute20;
894 l_rec.subpriority := p_subpriority;
895 l_rec.personal_payment_method_id := p_personal_payment_method_id;
896 l_rec.date_earned := p_date_earned;
897 l_rec.object_version_number := p_object_version_number;
898 l_rec.source_id := p_source_id;
899 l_rec.balance_adj_cost_flag := p_balance_adj_cost_flag;
900 l_rec.element_type_id := p_element_type_id;
901 l_rec.all_entry_values_null := p_all_entry_values_null;
902 --
903 -- Return the plsql record structure.
904 --
905 Return(l_rec);
906 --
907 End convert_args;
908 --
909 end pay_ele_shd;