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