[Home] [Help]
PACKAGE BODY: APPS.PAY_KR_ENTRIES_PKG
Source
4 --
1 package body pay_kr_entries_pkg as
2 /* $Header: pykretr.pkb 120.10.12020000.3 2012/08/31 13:11:44 scireddy ship $ */
3
5 -- Constant values to store element name
6 --
7 c_tax_en constant pay_element_types_f.element_name%TYPE := 'TAX';
8 c_hi_prem_en constant pay_element_types_f.element_name%TYPE := 'HI_PREM';
9 c_np_prem_en constant pay_element_types_f.element_name%TYPE := 'NP_PREM';
10 c_ei_prem_en constant pay_element_types_f.element_name%TYPE := 'EI_PREM'; -- Bug 10434174
11
12 -- Global record definition
13 g_old_rec pay_element_entries_f%ROWTYPE;
14
15 -- Global package name
16 g_package varchar2(33) := ' pay_kr_entries_pkg.';
17 g_debug boolean := hr_utility.debug_enabled;
18
19
20 -- ---------------------------------------------------------------------
21 -- |------------------------< calc_age >-------------------------------|
22 -- ---------------------------------------------------------------------
23 --
24 function calc_age
25 (
26 p_national_identifier in varchar2,
27 p_date_of_birth in date,
28 p_effective_date in date
29 )
30 return number
31 is
32 l_age number;
33 l_date varchar2(10);
34 begin
35
36 if p_national_identifier is null then
37 l_age := trunc(
38 months_between( p_effective_date, p_date_of_birth )/12 );
39 return l_age;
40 end if;
41
42 -- Modified for Bug2734338
43 -- The usage of the number 5,6,7,8 are changed to identify Foreigner
44 --
45 select decode( substr( p_national_identifier, 8, 1 ),
46 '1', '19', '2', '19',
47 '3', '20', '4', '20',
48 '5', '19', '6', '19',
49 '7', '20', '8', '20',
50 '9', '18', '0', '18', '19' )
51 || substr( p_national_identifier, 1, 6 )
52 into l_date
53 from sys.dual;
54
58
55 l_age := trunc( months_between( p_effective_date,
56 to_date( l_date, 'YYYYMMDD' ))/12 );
57 return l_age;
59 end;
60 -- ---------------------------------------------------------------------
61 -- |------------------------< element_name >---------------------------|
62 -- ---------------------------------------------------------------------
63 --
64 -- This function returns element_name corresponding to element_code.
65 -- This is temporary solution in R11i.
66 --
67 function element_name
68 (
69 p_element_code in varchar2
70 )
71 return varchar2
72 is
73 l_element_name pay_element_types_f.element_name%TYPE;
74 begin
75 if p_element_code = 'TAX' then
76 l_element_name := c_tax_en;
77 elsif p_element_code = 'HI_PREM' then
78 l_element_name := c_hi_prem_en;
79 elsif p_element_code = 'NP_PREM' then
80 l_element_name := c_np_prem_en;
81 elsif p_element_code = 'EI_PREM' then -- Bug 10434174
82 l_element_name := c_ei_prem_en;
83 end if;
84
85 return l_element_name;
86
87 end element_name;
88
89 -- ---------------------------------------------------------------------
90 -- |------------------------< element_type_rec >-----------------------|
91 -- ---------------------------------------------------------------------
92 --
93 function element_type_rec
94 (
95 p_element_name in varchar2,
96 p_business_group_id in number default null,
97 p_effective_date in date default hr_api.g_sys
98 ) return PAY_ELEMENT_TYPES_F%ROWTYPE
99 is
100
101 l_legislation_code varchar2(30);
102 l_element_type_rec pay_element_types_f%rowtype;
103
104 cursor csr( p_element_name varchar2,
105 p_business_group_id number,
106 p_legislation_code varchar2,
107 p_effective_date date ) is
108 select *
109 from pay_element_types_f pet
110 where pet.element_name = p_element_name
111 and p_effective_date between pet.effective_start_date
112 and pet.effective_end_date
113 and ( pet.legislation_code = p_legislation_code
114 or pet.business_group_id = p_business_group_id);
115
116 begin
117 l_legislation_code
118 := hr_api.return_legislation_code(p_business_group_id);
119
120 open csr( p_element_name,
121 p_business_group_id,
122 l_legislation_code,
123 p_effective_date );
124 fetch csr into l_element_type_rec;
125
126 if csr%notfound then
127 l_element_type_rec.element_type_id := null;
128 end if;
129
130 close csr;
131
132 return l_element_type_rec;
133 end;
134
135 -- ---------------------------------------------------------------------
136 -- |------------------------< derive_attributes >----------------------|
137 -- ---------------------------------------------------------------------
138 --
139 -- This procedure returns element and input value attributes for
140 -- p_elm_code_tbl input parameters like INPUT_CURRENCY_CODE, UOM etc.
141 -- p_business_group_id must be not null.
142 --
143 procedure derive_attributes
144 (
145 p_elm_code_tbl in elm_code_tbl,
146 p_effective_date in date,
147 p_business_group_id in number,
148 p_elm_rec_tbl out NOCOPY elm_rec_tbl,
149 p_iv_rec_tbl out NOCOPY iv_rec_tbl
150 )
151 is
152
153 l_index number;
154 l_elm_rec pay_element_types_f%ROWTYPE;
155
156 cursor csr_iv( p_element_type_id number ) is
157 select piv.input_value_id,
158 piv.display_sequence,
159 piv.uom,
160 piv.mandatory_flag
161 from pay_input_values_f piv
162 where piv.element_type_id = p_element_type_id
163 and p_effective_date between piv.effective_start_date
164 and piv.effective_end_date;
165
166 begin
167 l_index := p_elm_code_tbl.first;
168 --
169 -- Fetch element attributes.
170 --
171 while l_index is not NULL loop
172 /*
173 l_elm_rec := hr_jp_id_pkg.element_type_rec(
174 element_name(p_elm_code_tbl(l_index)),
175 p_business_group_id,
176 NULL,
177 p_effective_date,
178 'FALSE');
179 */
180 l_elm_rec := element_type_rec(
181 element_name(p_elm_code_tbl(l_index)),
182 p_business_group_id,
183 p_effective_date);
184 --
185 -- When not found, raise error.
186 --
187 if l_elm_rec.element_type_id is NULL then
188 hr_utility.set_message(801,'HR_7478_PLK_INCONSISTENT_ELE');
189 hr_utility.set_message_token('ELEMENT_TYPE_ID',NULL);
190 hr_utility.set_message_token('ELEMENT_NAME',
191 p_elm_code_tbl(l_index));
192 hr_utility.raise_error;
193 else
194 p_elm_rec_tbl(l_elm_rec.element_type_id).element_code
195 := p_elm_code_tbl(l_index);
196 p_elm_rec_tbl(l_elm_rec.element_type_id).input_currency_code
197 := l_elm_rec.input_currency_code;
198 p_elm_rec_tbl(l_elm_rec.element_type_id).multiple_entries_allowed_flag := l_elm_rec.multiple_entries_allowed_flag;
199 end if;
200 --
201 -- Fetch input value attributes.
202 --
203 for l_rec in csr_iv(l_elm_rec.element_type_id) loop
204 p_iv_rec_tbl(l_rec.input_value_id).element_type_id
208 p_iv_rec_tbl(l_rec.input_value_id).uom := l_rec.uom;
205 := l_elm_rec.element_type_id;
206 p_iv_rec_tbl(l_rec.input_value_id).display_sequence
207 := l_rec.display_sequence;
209 p_iv_rec_tbl(l_rec.input_value_id).mandatory_flag
210 := l_rec.mandatory_flag;
211 end loop;
212 --
213 -- Increment counter.
214 --
215 l_index := p_elm_code_tbl.next(l_index);
216 end loop;
217
218 end derive_attributes;
219
220 -- ---------------------------------------------------------------------
221 -- |------------------------< derive_format_mask >---------------------|
222 -- ---------------------------------------------------------------------
223 --
224 -- Derive format mask for p_iv_rec_tbl input parameter.
225 -- This procedure is designed to reduce network traffic because
226 -- fnd_currency.get_format_mask function accesses to DB.
227 --
228 procedure derive_format_mask
229 (
230 p_elm_rec_tbl in elm_rec_tbl,
231 p_iv_rec_tbl in out NOCOPY iv_rec_tbl
232 )
233 is
234 l_index number;
235 begin
236
237 l_index := p_iv_rec_tbl.first;
238
239 while l_index is not NULL loop
240 --
241 -- Only supported with uom = 'M'(Money) currently.
242 --
243 if p_iv_rec_tbl(l_index).uom = 'M' then
244 if p_iv_rec_tbl(l_index).max_length is not NULL then
245 p_iv_rec_tbl(l_index).format_mask
246 := fnd_currency.get_format_mask(
247 p_elm_rec_tbl(p_iv_rec_tbl(l_index).element_type_id).input_currency_code,
248 p_iv_rec_tbl(l_index).max_length);
249 end if;
250 end if;
251 --
252 -- Increment counter.
253 --
254 l_index := p_iv_rec_tbl.next(l_index);
255 end loop;
256
257 end derive_format_mask;
258
259 -- ---------------------------------------------------------------------
260 -- |-----------------------------< chk_entry >-------------------------|
261 -- ---------------------------------------------------------------------
262 --
263 -- This procedure checks entry can be created or not.
264 -- This is interface for hr_entry.check_element_entry procedure.
265 --
266 procedure chk_entry
267 (
268 p_element_entry_id in number,
269 p_assignment_id in number,
270 p_element_link_id in number,
271 p_entry_type in varchar2,
272 p_original_entry_id in number default null,
273 p_target_entry_id in number default null,
274 p_effective_date in date,
275 p_validation_start_date in date,
276 p_validation_end_date in date,
277 p_effective_start_date in out NOCOPY date,
278 p_effective_end_date in out NOCOPY date,
279 p_usage in varchar2,
280 p_dt_update_mode in varchar2,
281 p_dt_delete_mode in varchar2
282 )
283 is
284 begin
285 hr_entry.chk_element_entry(
286 p_element_entry_id => p_element_entry_id,
287 p_original_entry_id => p_original_entry_id,
288 p_session_date => p_effective_date,
289 p_element_link_id => p_element_link_id,
290 p_assignment_id => p_assignment_id,
291 p_entry_type => p_entry_type,
292 p_effective_start_date => p_effective_start_date,
293 p_effective_end_date => p_effective_end_date,
294 p_validation_start_date => p_validation_start_date,
295 p_validation_end_date => p_validation_end_date,
296 p_dt_update_mode => p_dt_update_mode,
297 p_dt_delete_mode => p_dt_delete_mode,
298 p_usage => p_usage,
299 p_target_entry_id => p_target_entry_id
300 );
301
302 end chk_entry;
303
304 -- ---------------------------------------------------------------------
305 -- |---------------------< derive_default_values >---------------------|
306 -- ---------------------------------------------------------------------
307 --
308 -- This procedure derive default values
309 -- for p_element_code input parameter.
310 --
311 procedure derive_default_values
312 (
313 p_assignment_id in number,
314 p_element_code in varchar2,
315 p_business_group_id in varchar2,
316 p_entry_type in varchar2,
317 p_element_link_id out NOCOPY number,
318 p_ev_rec_tbl out NOCOPY ev_rec_tbl,
319 p_effective_date in date,
320 p_effective_start_date in out NOCOPY date,
321 p_effective_end_date in out NOCOPY date
322 )
323 is
324
325 l_element_type_id number;
326 l_element_type_rec pay_element_types_f%rowtype;
327 l_counter number;
328
329 cursor csr_default_value is
330 select piv.input_value_id,
331 piv.display_sequence,
332 decode(piv.hot_default_flag,
333 'Y',nvl(pliv.default_value,piv.default_value),
334 pliv.default_value) DEFAULT_VALUE,
335 decode(piv.lookup_type,NULL,NULL,
336 hr_general.decode_lookup(
337 piv.lookup_type,
338 decode(piv.hot_default_flag,
339 'Y',nvl(pliv.default_value,piv.default_value),
340 pliv.default_value))) D_DEFAULT_VALUE
341 from pay_input_values_f piv,
342 pay_link_input_values_f pliv
343 where pliv.element_link_id = p_element_link_id
344 and p_effective_date between pliv.effective_start_date
345 and pliv.effective_end_date
346 and piv.input_value_id = pliv.input_value_id
350
347 and p_effective_date between piv.effective_start_date
348 and piv.effective_end_date
349 order by piv.display_sequence;
351 begin
352 --
353 -- Fetch eligible element_link_id for the assignment.
354 --
355 l_element_type_rec := element_type_rec(
356 element_name(p_element_code),
357 p_business_group_id,
358 p_effective_date);
359 l_element_type_id := l_element_type_rec.element_type_id;
360 /*
361 l_element_type_id := hr_jp_id_pkg.element_type_id(
362 element_name(p_element_code),p_business_group_id);
363 */
364 p_element_link_id := hr_entry_api.get_link(
365 p_assignment_id => p_assignment_id,
366 p_element_type_id => l_element_type_id,
367 p_session_date => p_effective_date);
368 if p_element_link_id is NULL then
369 hr_utility.set_message(801,'HR_7027_ELE_ENTRY_EL_NOT_EXST');
370 hr_utility.set_message_token('DATE',
371 fnd_date.date_to_displaydate(p_effective_date));
372 hr_utility.raise_error;
373 end if;
374 --
375 -- At first, checks whether the entry is available.
376 --
377 chk_entry(
378 p_element_entry_id => NULL,
379 p_assignment_id => p_assignment_id,
380 p_element_link_id => p_element_link_id,
381 p_entry_type => p_entry_type,
382 p_effective_date => p_effective_date,
383 p_validation_start_date => NULL,
384 p_validation_end_date => NULL,
385 p_effective_start_date => p_effective_start_date,
386 p_effective_end_date => p_effective_end_date,
387 p_usage => 'INSERT',
388 p_dt_update_mode => NULL,
389 p_dt_delete_mode => NULL);
390 --
391 -- If entry is available, fetch default values.
392 -- Must initialize varray variables.
393 --
394 l_counter := 0;
395 for l_rec in csr_default_value loop
396 l_counter := l_counter + 1;
397 p_ev_rec_tbl(l_counter).input_value_id := l_rec.input_value_id;
398 p_ev_rec_tbl(l_counter).entry_value := l_rec.default_value;
399 p_ev_rec_tbl(l_counter).d_entry_value := l_rec.d_default_value;
400 end loop;
401
402 end derive_default_values;
403
404 -- ---------------------------------------------------------------------
405 -- |-----------------------------< chk_formula >-----------------------|
406 -- ---------------------------------------------------------------------
407 --
408 -- This procedure execute formula validation for input value.
409 --
410 procedure chk_formula
411 (
412 p_formula_id in number,
413 p_entry_value in varchar2,
414 p_business_group_id in number,
415 p_assignment_id in number,
416 p_date_earned in date,
417 p_formula_status out NOCOPY varchar2,
418 p_formula_message out NOCOPY varchar2
419 )
420 is
421
422 l_counter number := 0;
423 l_inputs ff_exec.inputs_t;
424 l_outputs ff_exec.outputs_t;
425
426 cursor csr_fdi is
427 select item_name NAME,
428 decode(data_type,'T','TEXT','N','NUMBER','D','DATE') DATATYPE,
429 decode(usage,'U','CONTEXT','INPUT') CLASS
430 from ff_fdi_usages_f
431 where formula_id = p_formula_id
432 and p_date_earned between effective_start_date
433 and effective_end_date;
434 begin
435 --
436 -- Initialize formula informations.
437 --
438 ff_exec.init_formula(
439 p_formula_id => p_formula_id,
440 p_effective_date => p_date_earned,
441 p_inputs => l_inputs,
442 p_outputs => l_outputs
443 );
444 --
445 -- Setup input variables.
446 --
447 l_counter := l_inputs.first;
448 while l_counter is not NULL loop
449 if l_inputs(l_counter).name = 'BUSINESS_GROUP_ID' then
450 l_inputs(l_counter).value := fnd_number.number_to_canonical(p_business_group_id);
451 elsif l_inputs(l_counter).name = 'ASSIGNMENT_ID' then
452 l_inputs(l_counter).value := fnd_number.number_to_canonical(p_assignment_id);
453 elsif l_inputs(l_counter).name = 'DATE_EARNED' then
454 l_inputs(l_counter).value := fnd_date.date_to_canonical(p_date_earned);
455 elsif l_inputs(l_counter).name = 'ENTRY_VALUE' then
456 l_inputs(l_counter).value := p_entry_value;
457 end if;
458 l_counter := l_inputs.next(l_counter);
459 end loop;
460 --
461 -- Execute formula. Formula unexpected error is raised by ffexec,
462 -- so not necessary to handle error.
463 --
464 ff_exec.run_formula(
465 p_inputs => l_inputs,
466 p_outputs => l_outputs,
467 p_use_dbi_cache => FALSE
468 );
469 --
470 -- Setup output variables.
471 --
472 l_counter := l_outputs.first;
473 while l_counter is not NULL loop
474 if l_outputs(l_counter).name = 'FORMULA_STATUS' then
475 p_formula_status := l_outputs(l_counter).value;
476 elsif l_outputs(l_counter).name = 'FORMULA_MESSAGE' then
477 p_formula_message := l_outputs(l_counter).value;
478 end if;
479 l_counter := l_outputs.next(l_counter);
480 end loop;
481
482 end chk_formula;
483
484 -- ---------------------------------------------------------------------
485 -- |---------------------------< chk_entry_value >---------------------|
486 -- ---------------------------------------------------------------------
487 --
488 -- This function can not validate "user enterable flag".
489 -- Never call this procedure when p_display_value is NULL on Forms
490 -- WHEN-VALIDATE-ITEM trigger which will raise unexpected error.
491 -- Remeber hot defaulted value is not validated.
492 --
493 procedure chk_entry_value
494 (
495 p_element_link_id in number,
496 p_input_value_id in number,
497 p_effective_date in date,
498 p_business_group_id in number,
499 p_assignment_id in number,
500 p_user_value in out NOCOPY varchar2,
501 p_canonical_value out NOCOPY varchar2,
502 p_hot_defaulted out NOCOPY boolean,
503 p_min_max_warning out NOCOPY boolean,
504 p_user_min_value out NOCOPY varchar2,
505 p_user_max_value out NOCOPY varchar2,
506 p_formula_warning out NOCOPY boolean,
507 p_formula_message out NOCOPY varchar2
508 )
509 is
510
511 l_min_max_status varchar2(1);
512 l_formula_status varchar2(1);
513
514 cursor csr_iv is
515 select pivtl.name,
516 piv.uom,
517 piv.mandatory_flag,
518 piv.hot_default_flag,
519 piv.lookup_type,
520 decode(piv.hot_default_flag,
521 'Y',nvl(pliv.default_value,piv.default_value),
522 pliv.default_value) DEFAULT_VALUE,
523 decode(piv.hot_default_flag,
524 'Y',nvl(pliv.min_value,piv.min_value),
525 pliv.min_value) MIN_VALUE,
526 decode(piv.hot_default_flag,
527 'Y',nvl(pliv.max_value,piv.max_value),
528 pliv.max_value) MAX_VALUE,
529 piv.formula_id,
530 decode(piv.hot_default_flag,
531 'Y',nvl(pliv.warning_or_error,piv.warning_or_error),
532 pliv.warning_or_error) WARNING_OR_ERROR,
533 pet.input_currency_code
534 from pay_element_types_f pet,
535 pay_input_values_f_tl pivtl,
536 pay_input_values_f piv,
537 pay_link_input_values_f pliv
538 where pliv.element_link_id = p_element_link_id
539 and pliv.input_value_id = p_input_value_id
540 and p_effective_date between pliv.effective_start_date
541 and pliv.effective_end_date
542 and piv.input_value_id = pliv.input_value_id
543 and p_effective_date between piv.effective_start_date
544 and piv.effective_end_date
545 and pivtl.input_value_id = piv.input_value_id
546 and pivtl.language = userenv('LANG')
547 and pet.element_type_id = piv.element_type_id
548 and p_effective_date between pet.effective_start_date
549 and pet.effective_end_date;
550
551 l_rec csr_iv%ROWTYPE;
552 l_d_uom hr_lookups.meaning%TYPE;
553
554 begin
555 --
556 -- Initialize output variables.
557 --
558 p_canonical_value := NULL;
559 p_hot_defaulted := FALSE;
560 p_min_max_warning := FALSE;
561 p_user_min_value := NULL;
562 p_user_max_value := NULL;
563 p_formula_warning := FALSE;
564 p_formula_message := NULL;
565
566 if p_input_value_id is NULL then
567 p_user_value := NULL;
568 return;
569 end if;
570 --
571 -- Fetch input value attributes.
572 --
573 open csr_iv;
574 fetch csr_iv into l_rec;
575 if csr_iv%NOTFOUND then
576 close csr_iv;
577 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
578 hr_utility.set_message_token('PROCEDURE','hr_entry.check_format');
579 hr_utility.set_message_token('STEP','1');
580 hr_utility.raise_error;
581 end If;
582 close csr_iv;
583 --
584 -- When user entered value is NULL.
585 --
586 if p_user_value is NULL then
587 --
588 -- Mandatory Validation.
589 --
590 if l_rec.mandatory_flag = 'Y' then
591 --
592 -- When not hot defaulted.
593 --
594 if l_rec.hot_default_flag = 'N' then
595 hr_utility.set_message(801,'HR_6127_ELE_ENTRY_VALUE_MAND');
596 hr_utility.set_message_token('INPUT_VALUE_NAME',l_rec.name);
597 hr_utility.raise_error;
598 end if;
599 --
600 -- When hot defaulted.
601 --
602 if l_rec.default_value is NULL then
603 hr_utility.set_message(801,'HR_6128_ELE_ENTRY_MAND_HOT');
604 hr_utility.set_message_token('INPUT_VALUE_NAME',l_rec.name);
605 hr_utility.raise_error;
606 end if;
607
608 p_canonical_value := l_rec.default_value;
609 hr_chkfmt.changeformat(
610 input => p_canonical_value,
611 output => p_user_value,
612 format => l_rec.uom,
613 curcode => l_rec.input_currency_code
614 );
615 end if;
616 end if;
617 --
618 -- When p_user_value is not NULL.
619 -- Hot defaulted value is validated again in the following routine.
620 --
621 if p_user_value is not NULL then
622 --
623 -- Check format validation(format, min and max validations).
624 -- Hot defaulted value is validated again for range validation.
625 --
626 begin
627 hr_chkfmt.checkformat(
628 value => p_user_value,
629 format => l_rec.uom,
630 output => p_canonical_value,
631 minimum => l_rec.min_value,
632 maximum => l_rec.max_value,
633 nullok => 'Y',
634 rgeflg => l_min_max_status,
635 curcode => l_rec.input_currency_code
636 );
637 exception
638 --
639 -- In case the value input is incorrect format.
640 --
641 when others then
642 l_d_uom := hr_general.decode_lookup('UNITS',l_rec.uom);
643 hr_utility.set_message(801,'PAY_6306_INPUT_VALUE_FORMAT');
644 hr_utility.set_message_token('UNIT_OF_MEASURE',l_d_uom);
645 hr_utility.raise_error;
646 end;
647 --
648 -- Format min_value and max_value for output parameters.
649 -- These parameters should be used for message only.
650 --
651 if l_rec.min_value is not NULL then
652 hr_chkfmt.changeformat(
653 input => l_rec.min_value,
654 output => p_user_min_value,
655 format => l_rec.uom,
656 curcode => l_rec.input_currency_code
657 );
658 end if;
659
660 if l_rec.max_value is not NULL then
661 hr_chkfmt.changeformat(
662 input => l_rec.max_value,
663 output => p_user_max_value,
664 format => l_rec.uom,
665 curcode => l_rec.input_currency_code
666 );
667 end if;
668 --
669 -- If warning_or_error= E'(Error) and l_min_max_status='F'(Fatal),
670 -- then raise error. In case of 'W'(Warning), Forms should warn
671 -- to user with fnd_message.warn procedure.
672 --
673 if l_min_max_status = 'F' and l_rec.warning_or_error = 'E' then
674 if l_rec.max_value is NULL then
675 hr_utility.set_message(801,'PAY_KR_INPUTV_MIN_WARN');
676 hr_utility.set_message_token('MIN_VALUE',p_user_min_value);
677 hr_utility.raise_error;
678 end if;
679 if l_rec.min_value is NULL then
680 hr_utility.set_message(801,'PAY_KR_INPUTV_MAX_WARN');
681 hr_utility.set_message_token('MAX_VALUE',p_user_max_value);
682 hr_utility.raise_error;
683 end if;
684 hr_utility.set_message(801,'HR_ELE_ENTRY_MIN_MAX_WARN');
685 hr_utility.set_message_token('MIN_VALUE',p_user_min_value);
686 hr_utility.set_message_token('MAX_VALUE',p_user_max_value);
687 hr_utility.raise_error;
688 end if;
689 --
690 -- Execute formula validation.
691 --
692 if l_rec.formula_id is not NULL then
693 chk_formula(
694 p_formula_id => l_rec.formula_id,
695 p_entry_value => p_canonical_value,
696 p_business_group_id => p_business_group_id,
697 p_assignment_id => p_assignment_id,
698 p_date_earned => p_effective_date,
699 p_formula_status => l_formula_status,
700 p_formula_message => p_formula_message
701 );
702 end if;
703 --
704 -- If warning_or_error='E'(Error) and l_formula_status='E'(Error),
705 -- then raise error. In case of 'W'(Warning), Forms should warn
706 -- to user with fnd_message.warn procedure.
707 --
708 if l_formula_status = 'E' and l_rec.warning_or_error = 'E' then
709 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
710 hr_utility.set_message_token('FORMULA_TEXT',p_formula_message);
711 hr_utility.raise_error;
712 end if;
713 --
714 -- In case lookup_type validation is applied.
715 --
716 if l_rec.lookup_type is not NULL then
717 --
718 -- Lookup_type validation with effective_date.
719 --
720 if hr_api.not_exists_in_hr_lookups(
721 p_effective_date => p_effective_date,
722 p_lookup_type => l_rec.lookup_type,
723 p_lookup_code => p_canonical_value) then
724 hr_utility.set_message(801,'HR_7033_ELE_ENTRY_LKUP_INVLD');
725 hr_utility.set_message_token('LOOKUP_TYPE',l_rec.lookup_type);
726 hr_utility.raise_error;
727 end if;
728 end if;
729 end if;
730 --
731 -- Set output variables.
732 --
733 if l_min_max_status = 'F' then
734 p_min_max_warning := TRUE;
735 end if;
736 if l_formula_status = 'E' then
737 p_formula_warning := TRUE;
738 end If;
739 if l_rec.hot_default_flag = 'Y'
740 and p_canonical_value = l_rec.default_value then
741 p_hot_defaulted := TRUE;
742 end if;
743
744 end chk_entry_value;
745
746 -- ---------------------------------------------------------------------
747 -- |--------------------------< find_dt_upd_modes >--------------------|
748 -- ---------------------------------------------------------------------
749 --
750 -- Mandatory procedure to use DTCSAPI.pll forms library.
751 -- This procedure returns which datetrack modes are available
752 -- when updating.
753 --
754 procedure find_dt_upd_modes
755 (
756 p_effective_date in date,
757 p_base_key_value in number,
758 p_correction out NOCOPY boolean,
759 p_update out NOCOPY boolean,
760 p_update_override out NOCOPY boolean,
761 p_update_change_insert out NOCOPY boolean
762 )
763 is
764
765 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
766 l_entry_type pay_element_entries_f.entry_type%TYPE;
767 l_processing_type pay_element_types_f.processing_type%TYPE;
768
769 cursor c_sel1 is
770 select pee.entry_type,
771 pet.processing_type
772 from pay_element_types_f pet,
773 pay_element_links_f pel,
774 pay_element_entries_f pee
775 where pee.element_entry_id = p_base_key_value
776 and p_effective_date between pee.effective_start_date
777 and pee.effective_end_date
778 and pel.element_link_id = pee.element_link_id
779 and p_effective_date between pel.effective_start_date
780 and pel.effective_end_date
781 and pet.element_type_id = pel.element_type_id
782 and p_effective_date between pet.effective_start_date
783 and pet.effective_end_date;
784 --
785 begin
786 if g_debug then
787 hr_utility.set_location('Entering:'||l_proc, 5);
788 end if;
789
790 open c_sel1;
791 fetch c_sel1 into l_entry_type, l_processing_type;
792
793 if c_sel1%notfound then
794 close c_sel1;
795 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
796 hr_utility.set_message_token('PROCEDURE', l_proc);
797 hr_utility.set_message_token('STEP','10');
798 hr_utility.raise_error;
799 end if;
800
801 close c_sel1;
802 --
803 if l_processing_type = 'N' or l_entry_type <> 'E' then
804 p_correction := true;
805 p_update := false;
806 p_update_override := false;
807 p_update_change_insert := false;
808 else
809 --
810 -- Call the corresponding datetrack api
811 --
812 dt_api.find_dt_upd_modes(
813 p_effective_date => p_effective_date,
814 p_base_table_name => 'pay_element_entries_f',
815 p_base_key_column => 'element_entry_id',
816 p_base_key_value => p_base_key_value,
817 p_correction => p_correction,
818 p_update => p_update,
819 p_update_override => p_update_override,
820 p_update_change_insert => p_update_change_insert
821 );
822 end if;
823 --
824 if g_debug then
825 hr_utility.set_location(' Leaving:'||l_proc, 10);
826 end if;
827
828 end find_dt_upd_modes;
829
830 -- ---------------------------------------------------------------------
831 -- |--------------------------< find_dt_del_modes >--------------------|
832 -- ---------------------------------------------------------------------
833 --
834 -- Mandatory procedure to use DTCSAPI.pll forms library.
835 -- This procedure returns which datetrack modes are available
836 -- when deleting.
837 --
838 procedure find_dt_del_modes
839 (
840 p_effective_date in date,
841 p_base_key_value in number,
842 p_zap out NOCOPY boolean,
843 p_delete out NOCOPY boolean,
844 p_future_change out NOCOPY boolean,
845 p_delete_next_change out NOCOPY boolean
846 )
847 is
848
849 l_proc varchar2(72) := g_package || 'find_dt_del_modes';
850 l_parent_key_value1 number;
851 l_parent_key_value2 number;
852 l_entry_type pay_element_entries_f.entry_type%TYPE;
853 l_processing_type pay_element_types_f.processing_type%TYPE;
854
855 cursor c_sel1 is
856 select pee.assignment_id,
857 pee.element_link_id,
858 pee.entry_type,
859 pet.processing_type
860 from pay_element_types_f pet,
861 pay_element_links_f pel,
862 pay_element_entries_f pee
863 where pee.element_entry_id = p_base_key_value
864 and p_effective_date between pee.effective_start_date
865 and pee.effective_end_date
866 and pel.element_link_id = pee.element_link_id
867 and p_effective_date between pel.effective_start_date
868 and pel.effective_end_date
869 and pet.element_type_id = pel.element_type_id
870 and p_effective_date between pet.effective_start_date
871 and pet.effective_end_date;
872 --
873 begin
874 if g_debug then
875 hr_utility.set_location('Entering:'||l_proc, 5);
876 end if;
877
878 open c_sel1;
879 fetch c_sel1 into l_parent_key_value1,
880 l_parent_key_value2,
881 l_entry_type,
882 l_processing_type;
883
884 if c_sel1%notfound then
885 close c_sel1;
886 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
887 hr_utility.set_message_token('PROCEDURE', l_proc);
888 hr_utility.set_message_token('STEP','10');
889 hr_utility.raise_error;
890 end if;
891 close c_sel1;
892 --
893
894 if l_processing_type = 'N' or l_entry_type <> 'E' then
895 p_zap := true;
896 p_delete := false;
897 p_future_change := false;
898 p_delete_next_change := false;
899 else
900 --
901 -- Call the corresponding datetrack api
902 --
903 dt_api.find_dt_del_modes(
904 p_effective_date => p_effective_date,
905 p_base_table_name => 'pay_element_entries_f',
906 p_base_key_column => 'element_entry_id',
907 p_base_key_value => p_base_key_value,
908 p_parent_table_name1 => 'per_all_assignments_f',
909 p_parent_key_column1 => 'assignment_id',
910 p_parent_key_value1 => l_parent_key_value1,
911 p_parent_table_name2 => 'pay_element_links_f',
912 p_parent_key_column2 => 'element_link_id',
913 p_parent_key_value2 => l_parent_key_value2,
914 p_zap => p_zap,
915 p_delete => p_delete,
916 p_future_change => p_future_change,
917 p_delete_next_change => p_delete_next_change
918 );
919 end if;
920
921 if g_debug then
922 hr_utility.set_location(' Leaving:' || l_proc, 10 );
923 end if;
924
925 end find_dt_del_modes;
926
927 -- ---------------------------------------------------------------------
928 -- |-------------------------------< ins_lck >-------------------------|
929 -- ---------------------------------------------------------------------
930 --
931 -- Optional procedure to use DTCSAPI.pll forms library.
932 -- This procedure is used to lock parent tables
933 -- when inserting not to violate locking ladder.
934 --
935 procedure ins_lck
936 (
937 p_effective_date in date,
938 p_datetrack_mode in varchar2,
939 p_rec in pay_element_entries_f%ROWTYPE,
940 p_validation_start_date out NOCOPY date,
941 p_validation_end_date out NOCOPY date
942 )
943 is
944
945 l_proc varchar2(72) := g_package || 'ins_lck' ;
946 l_validation_start_date date;
947 l_validation_end_date date;
948
949 begin
950 if g_debug then
951 hr_utility.set_location( 'Entering:' || l_proc, 5 );
952 end if;
953 --
954 -- Validate the datetrack mode getting the validation start
955 -- and end dates for the specified datetrack operation.
956 --
957 dt_api.validate_dt_mode(
958 p_effective_date => p_effective_date,
959 p_datetrack_mode => p_datetrack_mode,
960 p_base_table_name => 'pay_element_entries_f',
961 p_base_key_column => 'element_entry_id',
962 p_base_key_value => p_rec.element_entry_id,
963 p_parent_table_name1 => 'per_all_assignments_f',
964 p_parent_key_column1 => 'assignment_id',
965 p_parent_key_value1 => p_rec.assignment_id,
966 p_parent_table_name2 => 'pay_element_links_f',
967 p_parent_key_column2 => 'element_link_id',
968 p_parent_key_value2 => p_rec.element_link_id,
969 p_enforce_foreign_locking => true,
970 p_validation_start_date => l_validation_start_date,
971 p_validation_end_date => l_validation_end_date
972 );
973 --
974 -- Set the validation start and end date OUT arguments
975 --
976 p_validation_start_date := l_validation_start_date;
977 p_validation_end_date := l_validation_end_date;
978 --
979 if g_debug then
980 hr_utility.set_location(' Leaving:'||l_proc, 15);
981 end if;
982 end ins_lck;
983
984 -- ---------------------------------------------------------------------
985 -- |---------------------------------< lck >---------------------------|
986 -- ---------------------------------------------------------------------
987 --
988 -- Mandatory procedure to use DTCSAPI.pll forms library.
989 -- This procedure is used to lock parent and child tables
990 -- when updating or deleting not to violate locking ladder.
991 --
992 procedure lck
993 (
994 p_effective_date in date,
995 p_datetrack_mode in varchar2,
996 p_element_entry_id in number,
997 p_object_version_number in number,
998 p_validation_start_date out NOCOPY date,
999 p_validation_end_date out NOCOPY date
1000 )
1001 is
1002
1003 l_proc varchar2(72) := g_package || 'lck';
1004
1005 l_validation_start_date date;
1006 l_validation_end_date date;
1007 l_object_invalid exception;
1008 l_argument varchar2(30);
1009
1010 --
1011 -- Cursor C_Sel1 fetches the current locked row as of session date
1012 -- ensuring that the object version numbers match.
1013 --
1014 cursor c_sel1 is
1015 select *
1016 from pay_element_entries_f
1017 where element_entry_id = p_element_entry_id
1018 and p_effective_date between effective_start_date
1019 and effective_end_date
1020 for update nowait;
1021
1022 begin
1023 if g_debug then
1024 hr_utility.set_location('Entering:'||l_proc, 5);
1025 end if;
1026 --
1027 -- Ensure that all the mandatory arguments are not null
1028 --
1029 hr_api.mandatory_arg_error(p_api_name => l_proc,
1030 p_argument => 'effective_date',
1031 p_argument_value => p_effective_date);
1032 --
1033 hr_api.mandatory_arg_error(p_api_name => l_proc,
1034 p_argument => 'datetrack_mode',
1035 p_argument_value => p_datetrack_mode);
1039 p_argument_value => p_element_entry_id);
1036 --
1037 hr_api.mandatory_arg_error(p_api_name => l_proc,
1038 p_argument => 'element_entry_id',
1040 --
1041 hr_api.mandatory_arg_error(p_api_name => l_proc,
1042 p_argument => 'object_version_number',
1043 p_argument_value => p_object_version_number);
1044 --
1045 -- Check to ensure the datetrack mode is not INSERT.
1046 --
1047 If (p_datetrack_mode <> 'INSERT') then
1048 --
1049 -- We must select and lock the current row.
1050 --
1051 open c_sel1;
1052 fetch c_sel1 into g_old_rec;
1053
1054 if c_sel1%notfound then
1055 close c_sel1;
1056 --
1057 -- The primary key is invalid therefore we must error
1058 --
1059 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1060 hr_utility.raise_error;
1061 end if;
1062
1063 close c_sel1;
1064 --
1065 -- Check if the set object version number is the same
1066 -- as the existing object version number
1067 --
1068 if (p_object_version_number <> g_old_rec.object_version_number) then
1069 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
1070 hr_utility.raise_error;
1071 end if;
1072
1073 if g_debug then
1074 hr_utility.set_location('Entering validation_dt_mode', 15);
1075 end if;
1076
1077 dt_api.validate_dt_mode(
1078 p_effective_date => p_effective_date,
1079 p_datetrack_mode => p_datetrack_mode,
1080 p_base_table_name => 'pay_element_entries_f',
1081 p_base_key_column => 'element_entry_id',
1082 p_base_key_value => p_element_entry_id,
1083 p_parent_table_name1 => 'per_all_assignments_f',
1084 p_parent_key_column1 => 'assignment_id',
1085 p_parent_key_value1 => g_old_rec.assignment_id,
1086 p_parent_table_name2 => 'pay_element_links_f',
1087 p_parent_key_column2 => 'element_link_id',
1088 p_parent_key_value2 => g_old_rec.element_link_id,
1089 p_enforce_foreign_locking => true,
1090 p_validation_start_date => l_validation_start_date,
1091 p_validation_end_date => l_validation_end_date
1092 );
1093 else
1094 --
1095 -- We are doing a datetrack 'INSERT' which is illegal within this
1096 -- procedure therefore we must error (note: to lck on insert the
1097 -- private procedure ins_lck should be called).
1098 --
1099 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1100 hr_utility.set_message_token('PROCEDURE', l_proc);
1101 hr_utility.set_message_token('STEP','20');
1102 hr_utility.raise_error;
1103
1104 end if;
1105 --
1106 -- Set the validation start and end date OUT arguments
1107 --
1108 p_validation_start_date := l_validation_start_date;
1109 p_validation_end_date := l_validation_end_date;
1110
1111 if g_debug then
1112 hr_utility.set_location(' Leaving:'||l_proc, 30);
1113 end if;
1114 --
1115 -- We need to trap the ORA LOCK exception
1116 --
1117 exception
1118 when hr_api.object_locked then
1119 --
1120 -- The object is locked therefore we need to supply a meaningful
1121 -- error message.
1122 --
1123 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
1124 hr_utility.set_message_token('TABLE_NAME', 'pay_element_entries_f');
1125 hr_utility.raise_error;
1126 when l_object_invalid then
1127 --
1128 -- The object doesn't exist or is invalid
1129 --
1130 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
1131 hr_utility.set_message_token('TABLE_NAME', 'pay_element_entries_f');
1132 hr_utility.raise_error;
1133
1134 end lck;
1135
1136 -- ---------------------------------------------------------------------
1137 -- |---------------------------< init_varray >-------------------------|
1138 -- ---------------------------------------------------------------------
1139 --
1140 -- Currently not used because forms6 can not handle varray correctly.
1141 --
1142 procedure init_varray
1143 (
1144 p_ev_rec_tbl in out NOCOPY ev_rec_tbl
1145 )
1146 is
1147 l_counter number;
1148 begin
1149 --
1150 -- Extend varray variable up to g_iv_max global variable.
1151 --
1152 l_counter := p_ev_rec_tbl.count;
1153 for i in l_counter + 1..g_iv_max loop
1154 p_ev_rec_tbl(i).input_value_id := NULL;
1155 end loop;
1156
1157 end init_varray;
1158
1159 -- ---------------------------------------------------------------------
1160 -- |-------------------------------< ins >-----------------------------|
1161 -- ---------------------------------------------------------------------
1162 --
1163 -- Procedure which issues insert dml.
1164 --
1165 procedure ins
1166 (
1167 p_validate in boolean,
1168 p_effective_date in date,
1169 p_assignment_id in number,
1170 p_element_link_id in number,
1171 p_ev_rec_tbl in ev_rec_tbl,
1172 p_business_group_id in number,
1173 p_element_entry_id out NOCOPY number,
1174 p_effective_start_date out NOCOPY date,
1175 p_effective_end_date out NOCOPY date,
1176 p_object_version_number out NOCOPY number
1177 )
1178 is
1179 l_warning boolean;
1180 l_ev_rec_tbl ev_rec_tbl := p_ev_rec_tbl;
1181 begin
1182 init_varray(l_ev_rec_tbl);
1183 py_element_entry_api.create_element_entry(
1184 P_VALIDATE => p_validate,
1185 P_EFFECTIVE_DATE => p_effective_date,
1186 P_BUSINESS_GROUP_ID => p_business_group_id,
1187 P_ASSIGNMENT_ID => p_assignment_id,
1188 P_ELEMENT_LINK_ID => p_element_link_id,
1189 P_ENTRY_TYPE => 'E',
1190 P_INPUT_VALUE_ID1 => l_ev_rec_tbl(1).input_value_id,
1191 P_INPUT_VALUE_ID2 => l_ev_rec_tbl(2).input_value_id,
1192 P_INPUT_VALUE_ID3 => l_ev_rec_tbl(3).input_value_id,
1193 P_INPUT_VALUE_ID4 => l_ev_rec_tbl(4).input_value_id,
1194 P_INPUT_VALUE_ID5 => l_ev_rec_tbl(5).input_value_id,
1195 P_INPUT_VALUE_ID6 => l_ev_rec_tbl(6).input_value_id,
1196 P_INPUT_VALUE_ID7 => l_ev_rec_tbl(7).input_value_id,
1197 P_INPUT_VALUE_ID8 => l_ev_rec_tbl(8).input_value_id,
1198 P_INPUT_VALUE_ID9 => l_ev_rec_tbl(9).input_value_id,
1199 P_INPUT_VALUE_ID10 => l_ev_rec_tbl(10).input_value_id,
1200 P_INPUT_VALUE_ID11 => l_ev_rec_tbl(11).input_value_id,
1201 P_INPUT_VALUE_ID12 => l_ev_rec_tbl(12).input_value_id,
1202 P_INPUT_VALUE_ID13 => l_ev_rec_tbl(13).input_value_id,
1203 P_INPUT_VALUE_ID14 => l_ev_rec_tbl(14).input_value_id,
1204 P_INPUT_VALUE_ID15 => l_ev_rec_tbl(15).input_value_id,
1205 P_ENTRY_VALUE1 => l_ev_rec_tbl(1).entry_value,
1206 P_ENTRY_VALUE2 => l_ev_rec_tbl(2).entry_value,
1207 P_ENTRY_VALUE3 => l_ev_rec_tbl(3).entry_value,
1208 P_ENTRY_VALUE4 => l_ev_rec_tbl(4).entry_value,
1209 P_ENTRY_VALUE5 => l_ev_rec_tbl(5).entry_value,
1210 P_ENTRY_VALUE6 => l_ev_rec_tbl(6).entry_value,
1211 P_ENTRY_VALUE7 => l_ev_rec_tbl(7).entry_value,
1212 P_ENTRY_VALUE8 => l_ev_rec_tbl(8).entry_value,
1213 P_ENTRY_VALUE9 => l_ev_rec_tbl(9).entry_value,
1214 P_ENTRY_VALUE10 => l_ev_rec_tbl(10).entry_value,
1215 P_ENTRY_VALUE11 => l_ev_rec_tbl(11).entry_value,
1216 P_ENTRY_VALUE12 => l_ev_rec_tbl(12).entry_value,
1217 P_ENTRY_VALUE13 => l_ev_rec_tbl(13).entry_value,
1218 P_ENTRY_VALUE14 => l_ev_rec_tbl(14).entry_value,
1219 P_ENTRY_VALUE15 => l_ev_rec_tbl(15).entry_value,
1220 P_EFFECTIVE_START_DATE => p_effective_start_date,
1221 P_EFFECTIVE_END_DATE => p_effective_end_date,
1222 P_ELEMENT_ENTRY_ID => p_element_entry_id,
1223 P_OBJECT_VERSION_NUMBER => p_object_version_number,
1224 P_CREATE_WARNING => l_warning
1225 );
1226 end ins;
1227
1228 -- ---------------------------------------------------------------------
1229 -- |-------------------------------< upd >-----------------------------|
1230 -- ---------------------------------------------------------------------
1231 --
1232 -- Procedure which issues update dml.
1233 --
1234 procedure upd
1235 (
1236 p_validate in boolean,
1237 p_effective_date in date,
1238 p_datetrack_update_mode in varchar2,
1239 p_element_entry_id in number,
1240 p_object_version_number in out NOCOPY number,
1241 p_ev_rec_tbl in ev_rec_tbl,
1242 p_business_group_id in number,
1243 p_effective_start_date out NOCOPY date,
1244 p_effective_end_date out NOCOPY date
1245 )
1246 is
1247 l_warning boolean;
1248 l_ev_rec_tbl ev_rec_tbl := p_ev_rec_tbl;
1249 begin
1250 init_varray(l_ev_rec_tbl);
1251 py_element_entry_api.update_element_entry(
1252 P_VALIDATE => p_validate,
1253 P_DATETRACK_UPDATE_MODE => p_datetrack_update_mode,
1254 P_EFFECTIVE_DATE => p_effective_date,
1255 P_BUSINESS_GROUP_ID => p_business_group_id,
1256 P_ELEMENT_ENTRY_ID => p_element_entry_id,
1257 P_OBJECT_VERSION_NUMBER => p_object_version_number,
1258 P_INPUT_VALUE_ID1 => l_ev_rec_tbl(1).input_value_id,
1259 P_INPUT_VALUE_ID2 => l_ev_rec_tbl(2).input_value_id,
1260 P_INPUT_VALUE_ID3 => l_ev_rec_tbl(3).input_value_id,
1261 P_INPUT_VALUE_ID4 => l_ev_rec_tbl(4).input_value_id,
1262 P_INPUT_VALUE_ID5 => l_ev_rec_tbl(5).input_value_id,
1263 P_INPUT_VALUE_ID6 => l_ev_rec_tbl(6).input_value_id,
1264 P_INPUT_VALUE_ID7 => l_ev_rec_tbl(7).input_value_id,
1265 P_INPUT_VALUE_ID8 => l_ev_rec_tbl(8).input_value_id,
1266 P_INPUT_VALUE_ID9 => l_ev_rec_tbl(9).input_value_id,
1267 P_INPUT_VALUE_ID10 => l_ev_rec_tbl(10).input_value_id,
1268 P_INPUT_VALUE_ID11 => l_ev_rec_tbl(11).input_value_id,
1269 P_INPUT_VALUE_ID12 => l_ev_rec_tbl(12).input_value_id,
1270 P_INPUT_VALUE_ID13 => l_ev_rec_tbl(13).input_value_id,
1271 P_INPUT_VALUE_ID14 => l_ev_rec_tbl(14).input_value_id,
1272 P_INPUT_VALUE_ID15 => l_ev_rec_tbl(15).input_value_id,
1273 P_ENTRY_VALUE1 => l_ev_rec_tbl(1).entry_value,
1274 P_ENTRY_VALUE2 => l_ev_rec_tbl(2).entry_value,
1275 P_ENTRY_VALUE3 => l_ev_rec_tbl(3).entry_value,
1276 P_ENTRY_VALUE4 => l_ev_rec_tbl(4).entry_value,
1277 P_ENTRY_VALUE5 => l_ev_rec_tbl(5).entry_value,
1278 P_ENTRY_VALUE6 => l_ev_rec_tbl(6).entry_value,
1279 P_ENTRY_VALUE7 => l_ev_rec_tbl(7).entry_value,
1280 P_ENTRY_VALUE8 => l_ev_rec_tbl(8).entry_value,
1281 P_ENTRY_VALUE9 => l_ev_rec_tbl(9).entry_value,
1282 P_ENTRY_VALUE10 => l_ev_rec_tbl(10).entry_value,
1283 P_ENTRY_VALUE11 => l_ev_rec_tbl(11).entry_value,
1284 P_ENTRY_VALUE12 => l_ev_rec_tbl(12).entry_value,
1285 P_ENTRY_VALUE13 => l_ev_rec_tbl(13).entry_value,
1286 P_ENTRY_VALUE14 => l_ev_rec_tbl(14).entry_value,
1287 P_ENTRY_VALUE15 => l_ev_rec_tbl(15).entry_value,
1288 P_EFFECTIVE_START_DATE => p_effective_start_date,
1289 P_EFFECTIVE_END_DATE => p_effective_end_date,
1290 P_UPDATE_WARNING => l_warning
1291 );
1292 end upd;
1293
1294 -- ---------------------------------------------------------------------
1295 -- |-------------------------------< del >-----------------------------|
1296 -- ---------------------------------------------------------------------
1297 --
1298 -- Procedure which issues delete dml.
1299 --
1300 procedure del
1301 (
1302 p_validate in boolean,
1303 p_effective_date in date,
1304 p_datetrack_delete_mode in varchar2,
1305 p_element_entry_id in number,
1306 p_object_version_number in out NOCOPY number,
1307 p_effective_start_date out NOCOPY date,
1308 p_effective_end_date out NOCOPY date
1309 )
1310 is
1311 l_warning BOOLEAN;
1312 begin
1313 py_element_entry_api.delete_element_entry(
1314 P_VALIDATE => p_validate,
1315 P_DATETRACK_DELETE_MODE => p_datetrack_delete_mode,
1316 P_EFFECTIVE_DATE => p_effective_date,
1317 P_ELEMENT_ENTRY_ID => p_element_entry_id,
1318 P_OBJECT_VERSION_NUMBER => p_object_version_number,
1319 P_EFFECTIVE_START_DATE => p_effective_start_date,
1320 P_EFFECTIVE_END_DATE => p_effective_end_date,
1321 P_DELETE_WARNING => l_warning
1322 );
1323
1324 end del;
1325 --
1326 -- ---------------------------------------------------------------------
1327 -- Function to handle contact relationship data
1328 -- ---------------------------------------------------------------------
1329 -- upd_contact_info
1330 -- ---------------------------------------------------------------------
1331 procedure upd_contact_info
1332 ( p_validate in boolean default null
1333 ,p_effective_date in date
1334 ,p_contact_relationship_id in number
1335 ,p_object_version_number in out NOCOPY number
1336 ,p_cont_information2 in varchar2 default null
1337 ,p_cont_information3 in varchar2 default null
1338 ,p_cont_information4 in varchar2 default null
1339 ,p_cont_information5 in varchar2 default null
1340 ,p_cont_information7 in varchar2 default null
1341 ,p_cont_information8 in varchar2 default null
1342 ,p_cont_information10 in varchar2 default null
1343 ,p_cont_information12 in varchar2 default null
1344 ,p_cont_information13 in varchar2 default null
1345 ,p_cont_information14 in varchar2 default null
1346 ) is
1347 l_proc varchar2(72) := g_package||'upd_contact_info';
1348 begin
1349 if g_debug then
1350 hr_utility.set_location('Entering:'|| l_proc, 10);
1351 hr_utility.set_location(l_proc, 6);
1352 end if;
1353 --
1354 -- Call the contact relationships API
1355 --
1356 hr_contact_rel_api.update_contact_relationship
1357 (p_validate => p_validate
1358 ,p_effective_date => p_effective_date
1359 ,p_contact_relationship_id => p_contact_relationship_id
1360 ,p_object_version_number => p_object_version_number
1361 ,p_cont_information2 => p_cont_information2
1362 ,p_cont_information3 => p_cont_information3
1363 ,p_cont_information4 => p_cont_information4
1364 ,p_cont_information5 => p_cont_information5
1365 ,p_cont_information7 => p_cont_information7
1366 ,p_cont_information8 => p_cont_information8
1367 ,p_cont_information10 => p_cont_information10
1368 ,p_cont_information12 => p_cont_information12
1369 ,p_cont_information13 => p_cont_information13
1370 ,p_cont_information14 => p_cont_information14
1371 );
1372
1373 if g_debug then
1374 hr_utility.set_location(' Leaving:'||l_proc, 40);
1375 end if;
1376
1377 end upd_contact_info;
1378
1379 -- ---------------------------------------------------------------------
1380 -- Procedure to handle Contact Extra Information Data
1381 -- ---------------------------------------------------------------------
1382 -- upd_contact_extra_info
1383 -- ---------------------------------------------------------------------
1384 procedure upd_contact_extra_info
1385 ( p_effective_date IN DATE,
1386 p_contact_extra_info_id IN NUMBER,
1387 p_contact_relationship_id IN NUMBER,
1388 p_contact_ovn IN OUT NOCOPY NUMBER,
1389 p_cei_information1 IN VARCHAR2,
1390 p_cei_information2 IN VARCHAR2,
1391 p_cei_information3 IN VARCHAR2,
1392 p_cei_information4 IN VARCHAR2,
1393 p_cei_information5 IN VARCHAR2,
1394 p_cei_information6 IN VARCHAR2,
1395 p_cei_information7 IN VARCHAR2,
1396 p_cei_information8 IN VARCHAR2,
1397 p_cei_information9 IN VARCHAR2,
1398 p_cei_information10 IN VARCHAR2, -- Bug 5667762
1399 p_cei_information11 IN VARCHAR2,
1400 p_cei_information12 IN VARCHAR2, -- Bug 6630135
1401 p_cei_information13 IN VARCHAR2, -- Bug 6705170
1402 p_cei_information14 IN VARCHAR2, -- Bug 7142612
1403 p_cei_information15 IN VARCHAR2, -- Bug 7142612
1404 p_cei_information16 IN VARCHAR2, -- Bug 9737699
1405 p_cei_information17 IN VARCHAR2, -- Bug 9737699
1406 p_cei_information18 IN VARCHAR2, -- Bug 14219478
1407 p_cei_information19 IN VARCHAR2, -- Bug 14219478
1408 p_cei_information20 IN VARCHAR2, -- Bug 14219478
1409 p_cei_information21 IN VARCHAR2, -- Bug 14219478
1410 p_cei_information22 IN VARCHAR2, -- Bug 14502155
1411 p_cei_information23 IN VARCHAR2, -- Bug 14502155
1412 p_cei_information24 IN VARCHAR2, -- Bug 14502155
1413 p_cei_effective_start_date OUT NOCOPY DATE,
1414 p_cei_effective_end_date OUT NOCOPY DATE
1415 )
1416 is
1417 l_proc varchar2(72) := g_package||'upd_contact_extra_info';
1418 begin
1419
1420 if g_debug then
1421 hr_utility.set_location('Entering:'|| l_proc, 25);
1422 hr_utility.set_location(l_proc, 6);
1423 end if;
1424
1425 hr_contact_extra_info_api.update_contact_extra_info
1426 ( p_effective_date => p_effective_date,
1427 p_datetrack_update_mode => 'CORRECTION',
1428 p_contact_extra_info_id => p_contact_extra_info_id,
1429 p_contact_relationship_id => p_contact_relationship_id,
1430 p_object_version_number => p_contact_ovn,
1431 p_information_type => 'KR_DPNT_EXPENSE_INFO',
1432 p_cei_information1 => p_cei_information1,
1433 p_cei_information2 => p_cei_information2,
1434 p_cei_information3 => p_cei_information3,
1435 p_cei_information4 => p_cei_information4,
1436 p_cei_information5 => p_cei_information5,
1437 p_cei_information6 => p_cei_information6,
1438 p_cei_information7 => p_cei_information7,
1439 p_cei_information8 => p_cei_information8,
1440 p_cei_information9 => p_cei_information9,
1441 p_cei_information10 => p_cei_information10, -- Bug 5667762
1442 p_cei_information11 => p_cei_information11,
1443 p_cei_information12 => p_cei_information12, -- Bug 6630135
1444 p_cei_information13 => p_cei_information13, -- Bug 6705170
1445 p_cei_information14 => p_cei_information14, -- Bug 7142612
1446 p_cei_information15 => p_cei_information15, -- Bug 7142612
1447 p_cei_information16 => p_cei_information16, -- Bug 9737699
1448 p_cei_information17 => p_cei_information17, -- Bug 9737699
1449 p_cei_information18 => p_cei_information18, -- Bug 14219478
1450 p_cei_information19 => p_cei_information19, -- Bug 14219478
1451 p_cei_information20 => p_cei_information20, -- Bug 14219478
1452 p_cei_information21 => p_cei_information21, -- Bug 14219478
1453 p_cei_information22 => p_cei_information22, -- Bug 14502155
1454 p_cei_information23 => p_cei_information23, -- Bug 14502155
1455 p_cei_information24 => p_cei_information24, -- Bug 14502155
1456 p_effective_start_date => p_cei_effective_start_date,
1457 p_effective_end_date => p_cei_effective_end_date
1458 );
1459 --
1460 if g_debug then
1461 hr_utility.set_location(' Leaving:'||l_proc, 40);
1462 end if;
1463 --
1464 end upd_contact_extra_info;
1465
1466 -- ---------------------------------------------------------------------
1467 -- Procedure to handle Contact Extra Information Data
1468 -- ---------------------------------------------------------------------
1469 -- create_contact_extra_info
1470 -- ---------------------------------------------------------------------
1471
1472 procedure create_contact_extra_info
1473 ( p_effective_date IN DATE,
1474 p_contact_extra_info_id OUT NOCOPY NUMBER,
1475 p_contact_relationship_id IN NUMBER,
1476 p_contact_ovn OUT NOCOPY NUMBER,
1477 p_cei_information1 IN VARCHAR2,
1478 p_cei_information2 IN VARCHAR2,
1479 p_cei_information3 IN VARCHAR2,
1480 p_cei_information4 IN VARCHAR2,
1481 p_cei_information5 IN VARCHAR2,
1482 p_cei_information6 IN VARCHAR2,
1483 p_cei_information7 IN VARCHAR2,
1484 p_cei_information8 IN VARCHAR2,
1485 p_cei_information9 IN VARCHAR2,
1486 p_cei_information10 IN VARCHAR2, -- Bug 5667762
1487 p_cei_information11 IN VARCHAR2,
1488 p_cei_information12 IN VARCHAR2, -- Bug 6630135
1489 p_cei_information13 IN VARCHAR2, -- Bug 6705170
1490 p_cei_information14 IN VARCHAR2, -- Bug 7142612
1491 p_cei_information15 IN VARCHAR2, -- Bug 7142612
1492 p_cei_information16 IN VARCHAR2, -- Bug 9737699
1493 p_cei_information17 IN VARCHAR2, -- Bug 9737699
1494 p_cei_information18 IN VARCHAR2, -- Bug 14219478
1495 p_cei_information19 IN VARCHAR2, -- Bug 14219478
1496 p_cei_information20 IN VARCHAR2, -- Bug 14219478
1497 p_cei_information21 IN VARCHAR2, -- Bug 14219478
1498 p_cei_information22 IN VARCHAR2, -- Bug 14502155
1499 p_cei_information23 IN VARCHAR2, -- Bug 14502155
1500 p_cei_information24 IN VARCHAR2, -- Bug 14502155
1501 p_cei_effective_start_date OUT NOCOPY DATE,
1502 p_cei_effective_end_date OUT NOCOPY DATE
1503 )
1504 is
1505 l_proc varchar2(72) := g_package||'create_contact_extra_info';
1506 begin
1507 --
1508 if g_debug then
1509 hr_utility.set_location('Entering:'|| l_proc, 25);
1510 hr_utility.set_location(l_proc, 6);
1511 end if;
1512
1513 hr_contact_extra_info_api.create_contact_extra_info
1514 ( p_effective_date => p_effective_date,
1515 p_contact_extra_info_id => p_contact_extra_info_id,
1516 p_contact_relationship_id => p_contact_relationship_id,
1517 p_object_version_number => p_contact_ovn,
1518 p_information_type => 'KR_DPNT_EXPENSE_INFO',
1519 p_cei_information_category => 'KR_DPNT_EXPENSE_INFO',
1520 p_cei_information1 => p_cei_information1,
1521 p_cei_information2 => p_cei_information2,
1522 p_cei_information3 => p_cei_information3,
1523 p_cei_information4 => p_cei_information4,
1524 p_cei_information5 => p_cei_information5,
1525 p_cei_information6 => p_cei_information6,
1526 p_cei_information7 => p_cei_information7,
1527 p_cei_information8 => p_cei_information8,
1528 p_cei_information9 => p_cei_information9,
1529 p_cei_information10 => p_cei_information10, -- Bug 5667762
1530 p_cei_information11 => p_cei_information11,
1531 p_cei_information12 => p_cei_information12, -- Bug 6630135
1532 p_cei_information13 => p_cei_information13, -- Bug 6705170
1533 p_cei_information14 => p_cei_information14, -- Bug 7142612
1534 p_cei_information15 => p_cei_information15, -- Bug 7142612
1535 p_cei_information16 => p_cei_information16, -- Bug 9737699
1536 p_cei_information17 => p_cei_information17, -- Bug 9737699
1537 p_cei_information18 => p_cei_information18, -- Bug 14219478
1538 p_cei_information19 => p_cei_information19, -- Bug 14219478
1539 p_cei_information20 => p_cei_information20, -- Bug 14219478
1540 p_cei_information21 => p_cei_information21, -- Bug 14219478
1541 p_cei_information22 => p_cei_information22, -- Bug 14502155
1542 p_cei_information23 => p_cei_information23, -- Bug 14502155
1543 p_cei_information24 => p_cei_information24, -- Bug 14502155
1544 p_effective_start_date => p_cei_effective_start_date,
1545 p_effective_end_date => p_cei_effective_end_date
1546 );
1547 --
1548 if g_debug then
1549 hr_utility.set_location(' Leaving:'||l_proc, 40);
1550 end if;
1551 --
1552 end create_contact_extra_info;
1553 --
1554 end pay_kr_entries_pkg;