[Home] [Help]
PACKAGE BODY: APPS.PAY_KR_SEP_FORM_PKG
Source
1 package body pay_kr_sep_form_pkg as
2 /* $Header: pykrsepf.pkb 120.2 2006/10/09 11:38:33 vaisriva noship $ */
3 --
4 -- Global Variables.
5 --
6 g_package varchar2(33) := 'pay_kr_sep_form_pkg.'; -- Global package name
7 g_debug boolean := hr_utility.debug_enabled;
8 g_old_rec pay_element_entries_f%ROWTYPE; -- Global record definition
9 g_run_type_id number;
10 g_run_type_name pay_run_types_f_tl.run_type_name%type;
11 g_entry_type varchar2(1) := 'E';
12 --
13 g_business_group_id number;
14 g_legislation_code varchar2(2);
15 g_session_date date;
16 g_element_entry_id number;
17 g_input_value_index number;
18 type screen_entry_value_tbl is table of pay_element_entry_values_f.screen_entry_value%type index by binary_integer;
19 g_screen_entry_value_tbl screen_entry_value_tbl;
20 type element_name_tbl is table of pay_element_types_f.element_name%type index by binary_integer;
21 type element_type_id_tbl is table of pay_element_types_f.element_type_id%type index by binary_integer;
22 type input_value_id_tbl is table of pay_input_values_f.input_value_id%type index by binary_integer;
23 type display_sequence_tbl is table of pay_input_values_f.display_sequence%type index by binary_integer;
24 type lookup_type_tbl is table of pay_input_values_f.lookup_type%type index by binary_integer;
25 type mandatory_flag_tbl is table of pay_input_values_f.mandatory_flag%type index by binary_integer;
26 type input_value_name_tbl is table of pay_input_values_f.name%type index by binary_integer;
27 type input_value_d_name_tbl is table of pay_input_values_f_tl.name%type index by binary_integer;
28 type get_input_value_id_rec is record(
29 element_type_id element_type_id_tbl,
30 input_value_id input_value_id_tbl,
31 display_sequence display_sequence_tbl,
32 lookup_type lookup_type_tbl,
33 mandatory_flag mandatory_flag_tbl,
34 name input_value_name_tbl,
35 d_name input_value_d_name_tbl);
36 g_get_input_value_id get_input_value_id_rec;
37 type get_element_type_id_rec is record(
38 element_name element_name_tbl,
39 element_type_id element_type_id_tbl);
40 g_get_element_type_id get_element_type_id_rec;
41 --
42 --------------------------------------------------------------------------------
43 function get_run_type_name(p_run_type_id in number,
44 p_effective_date in date) return varchar2
45 --------------------------------------------------------------------------------
46 is
47 --
48 l_run_type_name pay_run_types_f_tl.run_type_name%type;
49 --
50 cursor csr_run_type_name
51 is
52 select prtt.run_type_name
53 from pay_run_types_f_tl prtt,
54 pay_run_types_f prt
55 where prt.run_type_id = p_run_type_id
56 and p_effective_date
57 between prt.effective_start_date and prt.effective_end_date
58 and prtt.run_type_id = prt.run_type_id
59 and prtt.language = userenv('LANG');
60 --
61 begin
62 --
63 if g_run_type_id = p_run_type_id then
64 l_run_type_name := g_run_type_name;
65 else
66 open csr_run_type_name;
67 fetch csr_run_type_name into l_run_type_name;
68 close csr_run_type_name;
69 end if;
70 --
71 return l_run_type_name;
72 --
73 end get_run_type_name;
74 --
75 --------------------------------------------------------------------------------
76 function get_kr_d_address_line1(p_address_line1 in varchar2) return varchar2
77 --------------------------------------------------------------------------------
78 is
79 --
80 l_postal_code_id number;
81 l_d_address_line1 varchar2(200);
82 --
83 cursor csr_kr_d_address
84 is
85 select pka.city_province||' '||
86 pka.district||' '||
87 pka.town_village
88 -- pka.house_number -- Commented for Bug# 2506248
89 from per_kr_addresses pka
90 where pka.postal_code_id = l_postal_code_id;
91 --
92 begin
93 --
94 l_postal_code_id := to_number(p_address_line1);
95 --
96 open csr_kr_d_address;
97 fetch csr_kr_d_address into l_d_address_line1;
98 close csr_kr_d_address;
99 --
100 return l_d_address_line1;
101 --
102 end get_kr_d_address_line1;
103 --------------------------------------------------------------------------------
104 procedure process_run(p_payroll_id in number,
105 p_consolidation_set_id in number,
106 p_earned_date in varchar2,
107 p_date_paid in varchar2,
108 p_ele_set_id in number,
109 p_assignment_set_id in number,
110 p_run_type_id in number,
111 p_leg_params in varchar2,
112 p_payout_date in varchar2, -- Bug # 5559330
113 p_req_id in out NOCOPY number,
114 p_success out NOCOPY boolean,
115 errbuf out NOCOPY varchar2)
116 --------------------------------------------------------------------------------
117 -- /* This code is copied from hr_rungen.perform_run source. */
118 is
119 --
120 l_wait_outcome boolean;
121 l_phase varchar2(80);
122 l_status varchar2(80);
123 l_dev_phase varchar2(80);
124 l_dev_status varchar2(80);
125 l_message varchar2(80);
126 l_errbuf varchar2(240);
127 --
128 -- Bug # 5559330: Adding new parameter to pass the profile option value and payout date
129 --
130 l_action_parameter_group varchar2(80);
131 l_payout_date varchar2(80);
132 --
133 begin
134 --
135 -- Bug # 5559330: Fetching and passing the profile option value to KR Separation Pay Payroll
136 --
137 l_action_parameter_group := fnd_profile.value('ACTION_PARAMETER_GROUPS');
138 --
139 -- Bug # 5559330: Assigning value for the hidden parameter PAYOUTDATE
140 --
141 if p_payout_date is not null then
142 l_payout_date := 'PAYOUTDATE='||p_payout_date;
143 else
144 l_payout_date := null;
145 end if;
146 --
147 p_req_id := fnd_request.submit_request(
148 application => 'PAY',
149 program => 'PAYKRSEP',
150 argument1 => 'RUN',
151 argument2 => p_payroll_id,
152 argument3 => p_consolidation_set_id,
153 argument4 => p_earned_date,
154 argument5 => p_date_paid,
155 argument6 => p_ele_set_id,
156 argument7 => p_assignment_set_id,
157 argument8 => p_run_type_id,
158 argument9 => l_action_parameter_group, -- Bug # 5559330
159 argument10 => p_leg_params,
160 argument11 => p_payout_date,
161 argument12 => l_payout_date); -- Bug # 5559330
162 if p_req_id = 0 then
163 p_success := false;
164 fnd_message.retrieve(l_errbuf);
165 hr_utility.trace('Error when submitting request: ' || SQLERRM || ' ' || SQLCODE);
166 raise zero_req_id;
167 else
168 --
169 commit;
170 --
171 l_wait_outcome := fnd_concurrent.wait_for_request(
172 request_id => p_req_id,
173 interval => 2,
174 phase => l_phase,
175 status => l_status,
176 dev_phase => l_dev_phase,
177 dev_status => l_dev_status,
178 message => l_message);
179 --
180 p_success := true;
181 --
182 end if;
183 --
184 errbuf := l_errbuf;
185 --
186 exception
187 when zero_req_id then
188 raise;
189 when others then
190 p_success := false;
191 l_errbuf := sqlerrm;
192 errbuf := l_errbuf;
193 hr_utility.trace('Error when submitting request: ' || SQLERRM || ' ' || SQLCODE);
194 --
195 end process_run;
196 --------------------------------------------------------------------------------
197 procedure archive_run(p_business_group_id in number,
198 p_start_date in varchar2,
199 p_effective_date in varchar2,
200 p_payroll_id in number,
201 p_payroll_id_hd in varchar2,
202 p_req_id in out NOCOPY number,
203 p_success out NOCOPY boolean,
204 errbuf out NOCOPY varchar2)
205 --------------------------------------------------------------------------------
206 -- /* This code is copied from hr_rungen.perform_run source. */
207 is
208 --
209 l_wait_outcome boolean;
210 l_phase varchar2(80);
211 l_status varchar2(80);
212 l_dev_phase varchar2(80);
213 l_dev_status varchar2(80);
214 l_message varchar2(80);
215 l_errbuf varchar2(240);
216 l_report_type varchar2(30) := 'KR_SEP';
217 l_report_qualifier varchar2(30) := 'KR';
218 l_report_category varchar2(30) := 'KR_SEP';
219 l_magnetic_file_name varchar2(50);
220 l_report_file_name varchar2(50);
221 --
222 begin
223 --
224 p_req_id := fnd_request.submit_request(
225 application => 'PAY',
226 program => 'PAYKRSAV',
227 argument1 => 'ARCHIVE',
228 argument2 => l_report_type,
229 argument3 => l_report_qualifier,
230 argument4 => p_start_date,
231 argument5 => p_effective_date,
232 argument6 => l_report_category,
233 argument7 => p_business_group_id,
234 argument8 => l_magnetic_file_name,
235 argument9 => l_report_file_name,
236 argument10 => p_payroll_id,
237 argument11 => p_payroll_id_hd);
238 if p_req_id = 0 then
239 p_success := false;
240 fnd_message.retrieve(l_errbuf);
241 hr_utility.trace('Error when submitting request: ' || SQLERRM || ' ' || SQLCODE);
242 raise zero_req_id;
243 else
244 --
245 commit;
246 --
247 l_wait_outcome := fnd_concurrent.wait_for_request(
248 request_id => p_req_id,
249 interval => 2,
250 phase => l_phase,
251 status => l_status,
252 dev_phase => l_dev_phase,
253 dev_status => l_dev_status,
254 message => l_message);
255 --
256 p_success := true;
257 --
258 end if;
259 --
260 errbuf := l_errbuf;
261 --
262 exception
263 when zero_req_id then
264 raise;
265 when others then
266 p_success := false;
267 l_errbuf := sqlerrm;
268 errbuf := l_errbuf;
269 hr_utility.trace('Error when submitting request: ' || SQLERRM || ' ' || SQLCODE);
270 --
271 end archive_run;
272 --------------------------------------------------------------------------------
273 procedure delete_action(p_source_action_id in number,
274 p_dml_mode in varchar2 /* NO_COMMIT, NONE, FULL */)
275 --------------------------------------------------------------------------------
276 is
277 --
278 begin
279 --
280 py_rollback_pkg.rollback_ass_action(p_assignment_action_id => p_source_action_id,
281 p_rollback_mode => 'ROLLBACK',
282 p_leave_base_table_row => false,
283 p_all_or_nothing => true,
284 p_dml_mode => p_dml_mode,
285 p_multi_thread => false);
286 --
287 end delete_action;
288 --------------------------------------------------------------------------------
289 procedure lock_action(p_source_action_id in number)
290 --------------------------------------------------------------------------------
291 is
292 --
293 cursor csr_assact
294 is
295 select *
296 from pay_assignment_actions
297 where assignment_action_id = p_source_action_id
298 for update nowait;
299 --
300 l_csr_assact csr_assact%rowtype;
301 --
302 begin
303 --
304 open csr_assact;
305 fetch csr_assact into l_csr_assact;
306 if csr_assact%notfound then
307 close csr_assact;
308 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
309 hr_utility.raise_error;
310 end if;
311 close csr_assact;
312 --exception
313 -- when others then
314 --
315 end lock_action;
316 --------------------------------------------------------------------------------
317 procedure find_dt_upd_modes(
318 p_effective_date in date,
319 p_base_key_value in number,
320 p_correction out NOCOPY boolean,
321 p_update out NOCOPY boolean,
322 p_update_override out NOCOPY boolean,
323 p_update_change_insert out NOCOPY boolean)
324 --------------------------------------------------------------------------------
325 is
326 --
327 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
328 --
329 l_entry_type pay_element_entries_f.entry_type%TYPE;
330 l_processing_type pay_element_types_f.processing_type%TYPE;
331 --
332 cursor C_Sel1 is
333 select pee.entry_type,
334 pet.processing_type
335 from pay_element_types_f pet,
336 pay_element_links_f pel,
337 pay_element_entries_f pee
338 where pee.element_entry_id = p_base_key_value
339 and p_effective_date
340 between pee.effective_start_date and pee.effective_end_date
341 and pel.element_link_id = pee.element_link_id
342 and p_effective_date
343 between pel.effective_start_date and pel.effective_end_date
344 and pet.element_type_id = pel.element_type_id
345 and p_effective_date
346 between pet.effective_start_date and pet.effective_end_date;
347 --
348 begin
349 if g_debug then
350 hr_utility.set_location('Entering:'||l_proc, 5);
351 end if;
352
353 open C_Sel1;
354 fetch C_Sel1 into l_entry_type,
355 l_processing_type;
356 if C_Sel1%notfound then
357 Close C_Sel1;
358 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
359 hr_utility.set_message_token('PROCEDURE', l_proc);
360 hr_utility.set_message_token('STEP','10');
361 hr_utility.raise_error;
362 end if;
363 close C_Sel1;
364 --
365 if l_processing_type = 'N' or
366 l_entry_type <> 'E' then
367 p_correction := true;
368 p_update := false;
369 p_update_override := false;
370 p_update_change_insert := false;
371 else
372 --
373 -- Call the corresponding datetrack api
374 --
375 dt_api.find_dt_upd_modes
376 (p_effective_date => p_effective_date,
377 p_base_table_name => 'pay_element_entries_f',
378 p_base_key_column => 'element_entry_id',
379 p_base_key_value => p_base_key_value,
380 p_correction => p_correction,
381 p_update => p_update,
382 p_update_override => p_update_override,
383 p_update_change_insert => p_update_change_insert);
384 end if;
385 --
386 if g_debug then
387 hr_utility.set_location(' Leaving:'||l_proc, 10);
388 end if;
389 end find_dt_upd_modes;
390 --------------------------------------------------------------------------------
391 procedure find_dt_del_modes(
392 p_effective_date in date,
393 p_base_key_value in number,
394 p_zap out NOCOPY boolean,
395 p_delete out NOCOPY boolean,
396 p_future_change out NOCOPY boolean,
397 p_delete_next_change out NOCOPY boolean)
398 --------------------------------------------------------------------------------
399 is
400 --
401 l_proc varchar2(72) := g_package||'find_dt_del_modes';
402 --
403 l_parent_key_value1 number;
404 l_parent_key_value2 number;
405 l_entry_type pay_element_entries_f.entry_type%TYPE;
406 l_processing_type pay_element_types_f.processing_type%TYPE;
407 --
408 cursor C_Sel1 is
409 select pee.assignment_id,
410 pee.element_link_id,
411 pee.entry_type,
412 pet.processing_type
413 from pay_element_types_f pet,
414 pay_element_links_f pel,
415 pay_element_entries_f pee
416 where pee.element_entry_id = p_base_key_value
417 and p_effective_date
418 between pee.effective_start_date and pee.effective_end_date
419 and pel.element_link_id = pee.element_link_id
420 and p_effective_date
421 between pel.effective_start_date and pel.effective_end_date
422 and pet.element_type_id = pel.element_type_id
423 and p_effective_date
424 between pet.effective_start_date and pet.effective_end_date;
425 --
426 begin
427 if g_debug then
428 hr_utility.set_location('Entering:'||l_proc, 5);
429 end if;
430
431 open C_Sel1;
432 fetch C_Sel1 into l_parent_key_value1,
433 l_parent_key_value2,
434 l_entry_type,
435 l_processing_type;
436 if C_Sel1%notfound then
437 close C_Sel1;
438 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
439 hr_utility.set_message_token('PROCEDURE', l_proc);
440 hr_utility.set_message_token('STEP','10');
441 hr_utility.raise_error;
442 end if;
443 close C_Sel1;
444 --
445 if l_processing_type = 'N' or
446 l_entry_type <> 'E' then
447 p_zap := true;
448 p_delete := false;
449 p_future_change := false;
450 p_delete_next_change := false;
451 else
452 --
453 -- Call the corresponding datetrack api
454 --
455 dt_api.find_dt_del_modes
456 (p_effective_date => p_effective_date,
457 p_base_table_name => 'pay_element_entries_f',
458 p_base_key_column => 'element_entry_id',
459 p_base_key_value => p_base_key_value,
460 p_parent_table_name1 => 'per_all_assignments_f',
461 p_parent_key_column1 => 'assignment_id',
462 p_parent_key_value1 => l_parent_key_value1,
463 p_parent_table_name2 => 'pay_element_links_f',
464 p_parent_key_column2 => 'element_link_id',
465 p_parent_key_value2 => l_parent_key_value2,
466 p_zap => p_zap,
467 p_delete => p_delete,
468 p_future_change => p_future_change,
469 p_delete_next_change => p_delete_next_change);
470 end if;
471 --
472 if g_debug then
473 hr_utility.set_location(' Leaving:'||l_proc, 10);
474 end if;
475 end find_dt_del_modes;
476 --------------------------------------------------------------------------------
477 procedure lock_element_entry(
478 p_effective_date in date,
479 p_datetrack_mode in varchar2,
480 p_element_entry_id in number,
481 p_object_version_number in number,
482 p_validation_start_date out NOCOPY date,
483 p_validation_end_date out NOCOPY date)
484 --------------------------------------------------------------------------------
485 is
486 --
487 l_proc varchar2(72) := g_package||'lock_element_entry';
488 l_validation_start_date date;
489 l_validation_end_date date;
490 l_object_invalid exception;
491 l_argument varchar2(30);
492 --
493 -- Cursor C_Sel1 selects the current locked row as of session date
494 -- ensuring that the object version numbers match.
495 --
496 cursor C_Sel1 is
497 select *
498 from pay_element_entries_f
499 where element_entry_id = p_element_entry_id
500 and p_effective_date
501 between effective_start_date and effective_end_date
502 for update nowait;
503 --
504 -- The following code is not supported in this package.
505 --
506 -- cursor C_Sel3 select comment text
507 --
508 -- cursor C_Sel3 is
509 -- select hc.comment_text
510 -- from hr_comments hc
511 -- where hc.comment_id = g_old_rec.comment_id;
512 --
513 begin
514 if g_debug then
515 hr_utility.set_location('Entering:'||l_proc, 5);
516 end if;
517 --
518 -- Ensure that all the mandatory arguments are not null
519 --
520 hr_api.mandatory_arg_error(p_api_name => l_proc,
521 p_argument => 'effective_date',
522 p_argument_value => p_effective_date);
523 --
524 hr_api.mandatory_arg_error(p_api_name => l_proc,
525 p_argument => 'datetrack_mode',
526 p_argument_value => p_datetrack_mode);
527 --
528 hr_api.mandatory_arg_error(p_api_name => l_proc,
529 p_argument => 'element_entry_id',
530 p_argument_value => p_element_entry_id);
531 --
532 hr_api.mandatory_arg_error(p_api_name => l_proc,
533 p_argument => 'object_version_number',
534 p_argument_value => p_object_version_number);
535 --
536 -- Check to ensure the datetrack mode is not INSERT.
537 --
538 if (p_datetrack_mode <> 'INSERT') then
539 --
540 -- We must select and lock the current row.
541 --
542 open C_Sel1;
543 fetch C_Sel1 into g_old_rec;
544 if C_Sel1%notfound then
545 close C_Sel1;
546 --
547 -- The primary key is invalid therefore we must error
548 --
549 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
550 hr_utility.raise_error;
551 --
552 end if;
553 close C_Sel1;
554 --
555 -- Check if the set object version number is the same as the existing
556 -- object version number
557 --
558 if (p_object_version_number <> g_old_rec.object_version_number) Then
559 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
560 hr_utility.raise_error;
561 end if;
562 --
563 if g_debug then
564 hr_utility.set_location(l_proc, 15);
565 end if;
566 --
567 -- Providing we are doing an update and a comment_id exists then
568 -- we select the comment text.
569 --
570 -- The following code is not supported in this package.
571 --
572 -- if ((g_old_rec.comment_id is not null) and
573 -- (p_datetrack_mode = 'UPDATE' or
574 -- p_datetrack_mode = 'CORRECTION' or
575 -- p_datetrack_mode = 'UPDATE_OVERRIDE' or
576 -- p_datetrack_mode = 'UPDATE_CHANGE_INSERT')) then
577 -- open C_Sel3;
578 -- fetch C_Sel3 into g_old_rec.comment_text;
579 -- if C_Sel3%notfound then
580 -- --
581 -- -- The comment_text for the specified comment_id does not exist.
582 -- -- We must error due to data integrity problems.
583 -- --
584 -- close C_Sel3;
585 -- hr_utility.set_message(801, 'HR_7202_COMMENT_TEXT_NOT_EXIST');
586 -- hr_utility.raise_error;
587 -- end if;
588 -- close C_Sel3;
589 -- end if;
590 --
591 -- Validate the datetrack mode mode getting the validation start
592 -- and end dates for the specified datetrack operation.
593 --
594 if g_debug then
595 hr_utility.set_location('Entering validation_dt_mode', 15);
596 end if;
597
598 dt_api.validate_dt_mode(
599 p_effective_date => p_effective_date,
600 p_datetrack_mode => p_datetrack_mode,
601 p_base_table_name => 'pay_element_entries_f',
602 p_base_key_column => 'element_entry_id',
603 p_base_key_value => p_element_entry_id,
604 p_parent_table_name1 => 'per_all_assignments_f',
605 p_parent_key_column1 => 'assignment_id',
606 p_parent_key_value1 => g_old_rec.assignment_id,
607 p_parent_table_name2 => 'pay_element_links_f',
608 p_parent_key_column2 => 'element_link_id',
609 p_parent_key_value2 => g_old_rec.element_link_id,
610 p_enforce_foreign_locking => true,
611 p_validation_start_date => l_validation_start_date,
612 p_validation_end_date => l_validation_end_date);
613 else
614 --
615 -- We are doing a datetrack 'INSERT' which is illegal within this
616 -- procedure therefore we must error (note: to lck on insert the
617 -- private procedure ins_lck should be called).
618 --
619 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
620 hr_utility.set_message_token('PROCEDURE', l_proc);
621 hr_utility.set_message_token('STEP','20');
622 hr_utility.raise_error;
623 --
624 end If;
625 --
626 -- Set the validation start and end date OUT arguments
627 --
628 p_validation_start_date := l_validation_start_date;
629 p_validation_end_date := l_validation_end_date;
630 --
631 if g_debug then
632 hr_utility.set_location(' Leaving:'||l_proc, 30);
633 end if;
634 --
635 -- We need to trap the ORA LOCK exception
636 --
637 exception
638 when HR_Api.Object_Locked then
639 --
640 -- The object is locked therefore we need to supply a meaningful
641 -- error message.
642 --
643 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
644 hr_utility.set_message_token('TABLE_NAME', 'pay_element_entries_f');
645 hr_utility.raise_error;
646 when l_object_invalid then
647 --
648 -- The object doesn't exist or is invalid
649 --
650 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
651 hr_utility.set_message_token('TABLE_NAME', 'pay_element_entries_f');
652 hr_utility.raise_error;
653 end lock_element_entry;
654 --------------------------------------------------------------------------------
655 procedure insert_element_entry(
656 p_validate in boolean,
657 p_assignment_id in number,
658 p_business_group_id in number,
659 p_effective_date in date,
660 p_element_link_id in number,
661 p_input_value_id1 in number,
662 p_input_value_id2 in number,
663 p_input_value_id3 in number,
664 p_input_value_id4 in number,
665 p_input_value_id5 in number,
666 p_input_value_id6 in number,
667 p_input_value_id7 in number,
668 p_input_value_id8 in number,
669 p_input_value_id9 in number,
670 p_input_value_id10 in number,
671 p_input_value_id11 in number,
672 p_input_value_id12 in number,
673 p_input_value_id13 in number,
674 p_input_value_id14 in number,
675 p_input_value_id15 in number,
676 p_entry_value1 in varchar2,
677 p_entry_value2 in varchar2,
678 p_entry_value3 in varchar2,
679 p_entry_value4 in varchar2,
680 p_entry_value5 in varchar2,
681 p_entry_value6 in varchar2,
682 p_entry_value7 in varchar2,
683 p_entry_value8 in varchar2,
684 p_entry_value9 in varchar2,
685 p_entry_value10 in varchar2,
686 p_entry_value11 in varchar2,
687 p_entry_value12 in varchar2,
688 p_entry_value13 in varchar2,
689 p_entry_value14 in varchar2,
690 p_entry_value15 in varchar2,
691 p_element_entry_id out NOCOPY number,
692 p_effective_start_date out NOCOPY date,
693 p_effective_end_date out NOCOPY date,
694 p_object_version_number out NOCOPY number)
695 --------------------------------------------------------------------------------
696 is
697 --
698 l_warning boolean;
699 --
700 begin
701 --
702 pay_element_entry_api.create_element_entry(
703 p_validate => p_validate,
704 p_effective_date => p_effective_date,
705 p_business_group_id => p_business_group_id,
706 p_assignment_id => p_assignment_id,
707 p_element_link_id => p_element_link_id,
708 p_entry_type => g_entry_type,
709 p_input_value_id1 => p_input_value_id1,
710 p_input_value_id2 => p_input_value_id2,
711 p_input_value_id3 => p_input_value_id3,
712 p_input_value_id4 => p_input_value_id4,
713 p_input_value_id5 => p_input_value_id5,
714 p_input_value_id6 => p_input_value_id6,
715 p_input_value_id7 => p_input_value_id7,
716 p_input_value_id8 => p_input_value_id8,
717 p_input_value_id9 => p_input_value_id9,
718 p_input_value_id10 => p_input_value_id10,
719 p_input_value_id11 => p_input_value_id11,
720 p_input_value_id12 => p_input_value_id12,
721 p_input_value_id13 => p_input_value_id13,
722 p_input_value_id14 => p_input_value_id14,
723 p_input_value_id15 => p_input_value_id15,
724 p_entry_value1 => p_entry_value1,
725 p_entry_value2 => p_entry_value2,
726 p_entry_value3 => p_entry_value3,
727 p_entry_value4 => p_entry_value4,
728 p_entry_value5 => p_entry_value5,
729 p_entry_value6 => p_entry_value6,
730 p_entry_value7 => p_entry_value7,
731 p_entry_value8 => p_entry_value8,
732 p_entry_value9 => p_entry_value9,
733 p_entry_value10 => p_entry_value10,
734 p_entry_value11 => p_entry_value11,
735 p_entry_value12 => p_entry_value12,
736 p_entry_value13 => p_entry_value13,
737 p_entry_value14 => p_entry_value14,
738 p_entry_value15 => p_entry_value15,
739 p_effective_start_date => p_effective_start_date,
740 p_effective_end_date => p_effective_end_date,
741 p_element_entry_id => p_element_entry_id,
742 p_object_version_number => p_object_version_number,
743 p_create_warning => l_warning);
744 --
745 end insert_element_entry;
746 ----------------------------------------------------------------------------------
747 procedure update_element_entry(
748 p_validate in boolean,
749 p_dt_update_mode in varchar2, /* UPDATE,UPDATE_CHANGE_INSERT,UPDATE_OVERRIDE,CORRECTION */
750 p_effective_date in date,
751 p_business_group_id in number,
752 p_element_entry_id in number,
753 p_object_version_number in out NOCOPY number,
754 p_input_value_id1 in number,
755 p_input_value_id2 in number,
756 p_input_value_id3 in number,
757 p_input_value_id4 in number,
758 p_input_value_id5 in number,
759 p_input_value_id6 in number,
760 p_input_value_id7 in number,
761 p_input_value_id8 in number,
762 p_input_value_id9 in number,
763 p_input_value_id10 in number,
764 p_input_value_id11 in number,
765 p_input_value_id12 in number,
766 p_input_value_id13 in number,
767 p_input_value_id14 in number,
768 p_input_value_id15 in number,
769 p_entry_value1 in varchar2,
770 p_entry_value2 in varchar2,
771 p_entry_value3 in varchar2,
772 p_entry_value4 in varchar2,
773 p_entry_value5 in varchar2,
774 p_entry_value6 in varchar2,
775 p_entry_value7 in varchar2,
776 p_entry_value8 in varchar2,
777 p_entry_value9 in varchar2,
778 p_entry_value10 in varchar2,
779 p_entry_value11 in varchar2,
780 p_entry_value12 in varchar2,
781 p_entry_value13 in varchar2,
782 p_entry_value14 in varchar2,
783 p_entry_value15 in varchar2,
784 p_effective_start_date out NOCOPY date,
785 p_effective_end_date out NOCOPY date)
786 --------------------------------------------------------------------------------
787 is
788 --
789 l_warning boolean;
790 --
791 begin
792 --
793 pay_element_entry_api.update_element_entry(
794 p_validate => p_validate,
795 p_datetrack_update_mode => p_dt_update_mode,
796 p_effective_date => p_effective_date,
797 p_business_group_id => p_business_group_id,
798 p_element_entry_id => p_element_entry_id,
799 p_object_version_number => p_object_version_number,
800 p_input_value_id1 => p_input_value_id1,
801 p_input_value_id2 => p_input_value_id2,
802 p_input_value_id3 => p_input_value_id3,
803 p_input_value_id4 => p_input_value_id4,
804 p_input_value_id5 => p_input_value_id5,
805 p_input_value_id6 => p_input_value_id6,
806 p_input_value_id7 => p_input_value_id7,
807 p_input_value_id8 => p_input_value_id8,
808 p_input_value_id9 => p_input_value_id9,
809 p_input_value_id10 => p_input_value_id10,
810 p_input_value_id11 => p_input_value_id11,
811 p_input_value_id12 => p_input_value_id12,
812 p_input_value_id13 => p_input_value_id13,
813 p_input_value_id14 => p_input_value_id14,
814 p_input_value_id15 => p_input_value_id15,
815 p_entry_value1 => p_entry_value1,
816 p_entry_value2 => p_entry_value2,
817 p_entry_value3 => p_entry_value3,
818 p_entry_value4 => p_entry_value4,
819 p_entry_value5 => p_entry_value5,
820 p_entry_value6 => p_entry_value6,
821 p_entry_value7 => p_entry_value7,
822 p_entry_value8 => p_entry_value8,
823 p_entry_value9 => p_entry_value9,
824 p_entry_value10 => p_entry_value10,
825 p_entry_value11 => p_entry_value11,
826 p_entry_value12 => p_entry_value12,
827 p_entry_value13 => p_entry_value13,
828 p_entry_value14 => p_entry_value14,
829 p_entry_value15 => p_entry_value15,
830 p_effective_start_date => p_effective_start_date,
831 p_effective_end_date => p_effective_end_date,
832 p_update_warning => l_warning);
833 --
834 end update_element_entry;
835 --------------------------------------------------------------------------------
836 procedure delete_element_entry(
837 p_validate in boolean,
838 p_dt_delete_mode in varchar2, /* DELETE,ZAP,DELETE_NEXT_CHANGE,FUTURE_CHANGE */
839 p_effective_date in date,
840 p_element_entry_id in number,
841 p_object_version_number in out NOCOPY number,
842 p_effective_start_date out NOCOPY date,
843 p_effective_end_date out NOCOPY date)
844 --------------------------------------------------------------------------------
845 is
846 --
847 l_warning boolean;
848 --
849 begin
850 --
851 pay_element_entry_api.delete_element_entry(
852 p_validate => p_validate,
853 p_datetrack_delete_mode => p_dt_delete_mode,
854 p_effective_date => p_effective_date,
855 p_element_entry_id => p_element_entry_id,
856 p_object_version_number => p_object_version_number,
857 p_effective_start_date => p_effective_start_date,
858 p_effective_end_date => p_effective_end_date,
859 p_delete_warning => l_warning);
860 --
861 end delete_element_entry;
862 --------------------------------------------------------------------------------
863 procedure chk_entry(
864 p_element_entry_id in number,
865 p_assignment_id in number,
866 p_element_link_id in number,
867 p_entry_type in varchar2,
868 p_original_entry_id in number,
869 p_target_entry_id in number,
870 p_effective_date in date,
871 p_validation_start_date in date,
872 p_validation_end_date in date,
873 p_effective_start_date in out NOCOPY date,
874 p_effective_end_date in out NOCOPY date,
875 p_usage in varchar2,
876 p_dt_update_mode in varchar2,
877 p_dt_delete_mode in varchar2)
878 --------------------------------------------------------------------------------
879 is
880 begin
881 hr_entry.chk_element_entry(
882 p_element_entry_id => p_element_entry_id,
883 p_original_entry_id => p_original_entry_id,
884 p_session_date => p_effective_date,
885 p_element_link_id => p_element_link_id,
886 p_assignment_id => p_assignment_id,
887 p_entry_type => p_entry_type,
888 p_effective_start_date => p_effective_start_date,
889 p_effective_end_date => p_effective_end_date,
890 p_validation_start_date => p_validation_start_date,
891 p_validation_end_date => p_validation_end_date,
892 p_dt_update_mode => p_dt_update_mode,
893 p_dt_delete_mode => p_dt_delete_mode,
894 p_usage => p_usage,
895 p_target_entry_id => p_target_entry_id);
896 end chk_entry;
897 --------------------------------------------------------------------------------
898 procedure chk_formula(
899 p_formula_id in number,
900 p_entry_value in varchar2,
901 p_business_group_id in number,
902 p_assignment_id in number,
903 p_date_earned in date,
904 p_formula_status out NOCOPY varchar2,
905 p_formula_message out NOCOPY varchar2)
906 --------------------------------------------------------------------------------
907 is
908 --
909 l_counter NUMBER := 0;
910 l_inputs ff_exec.inputs_t;
911 l_outputs ff_exec.outputs_t;
912 --
913 cursor csr_fdi
914 is
915 select item_name NAME,
916 decode(data_type,'T','TEXT','N','NUMBER','D','DATE') DATATYPE,
917 decode(usage,'U','CONTEXT','INPUT') CLASS
918 from ff_fdi_usages_f
919 where formula_id = p_formula_id
920 and p_date_earned
921 between effective_start_date and effective_end_date;
922 --
923 begin
924 --
925 -- Initialize formula informations.
926 --
927 ff_exec.init_formula(
928 p_formula_id => p_formula_id,
929 p_effective_date => p_date_earned,
930 p_inputs => l_inputs,
931 p_outputs => l_outputs);
932 --
933 -- Setup input variables.
934 --
935 l_counter := l_inputs.first;
936 while l_counter is not NULL loop
937 if l_inputs(l_counter).name = 'BUSINESS_GROUP_ID' then
938 l_inputs(l_counter).value := fnd_number.number_to_canonical(p_business_group_id);
939 elsif l_inputs(l_counter).name = 'ASSIGNMENT_ID' then
940 l_inputs(l_counter).value := fnd_number.number_to_canonical(p_assignment_id);
941 elsif l_inputs(l_counter).name = 'DATE_EARNED' then
942 l_inputs(l_counter).value := fnd_date.date_to_canonical(p_date_earned);
943 elsif l_inputs(l_counter).name = 'ENTRY_VALUE' then
944 l_inputs(l_counter).value := p_entry_value;
945 end if;
946 l_counter := l_inputs.next(l_counter);
947 end loop;
948 --
949 -- Execute formula. Formula unexpected error is raised by ffexec,
950 -- so not necessary to handle error.
951 --
952 ff_exec.run_formula(
953 p_inputs => l_inputs,
954 p_outputs => l_outputs,
955 p_use_dbi_cache => FALSE);
956 --
957 -- Setup output variables.
958 --
959 l_counter := l_outputs.first;
960 while l_counter is not NULL loop
961 if l_outputs(l_counter).name = 'FORMULA_STATUS' then
962 p_formula_status := l_outputs(l_counter).value;
963 elsif l_outputs(l_counter).name = 'FORMULA_MESSAGE' then
964 p_formula_message := l_outputs(l_counter).value;
965 end if;
966 l_counter := l_outputs.next(l_counter);
967 end loop;
968 end chk_formula;
969 --------------------------------------------------------------------------------
970 procedure validate_entry_value(
971 p_element_link_id in number,
972 p_input_value_id in number,
973 p_effective_date in date,
974 p_business_group_id in number,
975 p_assignment_id in number,
976 p_user_value in out NOCOPY varchar2,
977 p_canonical_value out NOCOPY varchar2,
978 p_hot_defaulted out NOCOPY boolean,
979 p_min_max_warning out NOCOPY boolean,
980 p_user_min_value out NOCOPY varchar2,
981 p_user_max_value out NOCOPY varchar2,
982 p_formula_warning out NOCOPY boolean,
983 p_formula_message out NOCOPY varchar2)
984 --------------------------------------------------------------------------------
985 is
986 --
987 l_min_max_status varchar2(1);
988 l_formula_status varchar2(1);
989 --
990 cursor csr_iv
991 is
992 select pivtl.name,
993 piv.uom,
994 piv.mandatory_flag,
995 piv.hot_default_flag,
996 piv.lookup_type,
997 decode(piv.hot_default_flag,
998 'Y',nvl(pliv.default_value,piv.default_value),
999 pliv.default_value) DEFAULT_VALUE,
1000 -- decode(piv.lookup_type,NULL,NULL,
1001 -- hr_general.decode_lookup(
1002 -- piv.lookup_type,
1003 -- decode(piv.hot_default_flag,
1004 -- 'Y',nvl(pliv.default_value,piv.default_value),
1005 -- pliv.default_value))) D_DEFAULT_VALUE,
1006 decode(piv.hot_default_flag,
1007 'Y',nvl(pliv.min_value,piv.min_value),
1008 pliv.min_value) MIN_VALUE,
1009 decode(piv.hot_default_flag,
1010 'Y',nvl(pliv.max_value,piv.max_value),
1011 pliv.max_value) MAX_VALUE,
1012 piv.formula_id,
1013 decode(piv.hot_default_flag,
1014 'Y',nvl(pliv.warning_or_error,piv.warning_or_error),
1015 pliv.warning_or_error) WARNING_OR_ERROR,
1016 pet.input_currency_code
1017 from pay_element_types_f pet,
1018 pay_input_values_f_tl pivtl,
1019 pay_input_values_f piv,
1020 pay_link_input_values_f pliv
1021 where pliv.element_link_id = p_element_link_id
1022 and pliv.input_value_id = p_input_value_id
1023 and p_effective_date
1024 between pliv.effective_start_date and pliv.effective_end_date
1025 and piv.input_value_id = pliv.input_value_id
1026 and p_effective_date
1027 between piv.effective_start_date and piv.effective_end_date
1028 and pivtl.input_value_id = piv.input_value_id
1029 and pivtl.language = userenv('LANG')
1030 and pet.element_type_id = piv.element_type_id
1031 and p_effective_date
1032 between pet.effective_start_date and pet.effective_end_date;
1033 --
1034 l_rec csr_iv%ROWTYPE;
1035 l_d_uom hr_lookups.meaning%TYPE;
1036 --
1037 begin
1038 --
1039 -- Initialize output variables.
1040 --
1041 p_canonical_value := NULL;
1042 p_hot_defaulted := FALSE;
1043 p_min_max_warning := FALSE;
1044 p_user_min_value := NULL;
1045 p_user_max_value := NULL;
1046 p_formula_warning := FALSE;
1047 p_formula_message := NULL;
1048 --
1049 -- When p_input_value_id is not NULL then validate.
1050 --
1051 if p_input_value_id is not NULL then
1052 --
1053 -- Fetch input value attributes.
1054 --
1055 open csr_iv;
1056 fetch csr_iv into l_rec;
1057 if csr_iv%NOTFOUND then
1058 close csr_iv;
1059 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1060 hr_utility.set_message_token('PROCEDURE','hr_entry.check_format');
1061 hr_utility.set_message_token('STEP','1');
1062 hr_utility.raise_error;
1063 end If;
1064 close csr_iv;
1065 --
1066 -- When user entered value is NULL.
1067 --
1068 if p_user_value is NULL then
1069 --
1070 -- Mandatory Validation.
1071 --
1072 if l_rec.mandatory_flag = 'Y' then
1073 --
1074 -- When not hot defaulted.
1075 --
1076 if l_rec.hot_default_flag = 'N' then
1077 hr_utility.set_message(801,'HR_6127_ELE_ENTRY_VALUE_MAND');
1078 hr_utility.set_message_token('INPUT_VALUE_NAME',l_rec.name);
1079 hr_utility.raise_error;
1080 --
1081 -- When hot defaulted.
1082 --
1083 else
1084 if l_rec.default_value is NULL then
1085 hr_utility.set_message(801,'HR_6128_ELE_ENTRY_MAND_HOT');
1086 hr_utility.set_message_token('INPUT_VALUE_NAME',l_rec.name);
1087 hr_utility.raise_error;
1088 else
1089 p_canonical_value := l_rec.default_value;
1090 hr_chkfmt.changeformat(
1091 input => p_canonical_value,
1092 output => p_user_value,
1093 format => l_rec.uom,
1094 curcode => l_rec.input_currency_code);
1095 end if;
1096 end if;
1097 end if;
1098 end if;
1099 --
1100 -- When p_user_value is not NULL.
1101 -- Hot defaulted value is validated again in the following routine.
1102 --
1103 if p_user_value is not NULL then
1104 --
1105 -- Check format validation(format, min and max validations).
1106 -- Hot defaulted value is validated again for range validation.
1107 --
1108 begin
1109 hr_chkfmt.checkformat(
1110 value => p_user_value,
1111 format => l_rec.uom,
1112 output => p_canonical_value,
1113 minimum => l_rec.min_value,
1114 maximum => l_rec.max_value,
1115 nullok => 'Y',
1116 rgeflg => l_min_max_status,
1117 curcode => l_rec.input_currency_code);
1118 exception
1119 --
1120 -- In case the value input is incorrect format.
1121 --
1122 when others then
1123 l_d_uom := hr_general.decode_lookup('UNITS',l_rec.uom);
1124 hr_utility.set_message(801,'PAY_6306_INPUT_VALUE_FORMAT');
1125 hr_utility.set_message_token('UNIT_OF_MEASURE',l_d_uom);
1126 hr_utility.raise_error;
1127 end;
1128 --
1129 -- Format min_value and max_value for output parameters.
1130 -- These parameters should be used for message only.
1131 --
1132 if l_rec.min_value is not NULL then
1133 hr_chkfmt.changeformat(
1134 input => l_rec.min_value,
1135 output => p_user_min_value,
1136 format => l_rec.uom,
1137 curcode => l_rec.input_currency_code);
1138 end if;
1139 if l_rec.max_value is not NULL then
1140 hr_chkfmt.changeformat(
1141 input => l_rec.max_value,
1142 output => p_user_max_value,
1143 format => l_rec.uom,
1144 curcode => l_rec.input_currency_code);
1145 end if;
1146 --
1147 -- If warning_or_error = 'E'(Error) and l_min_max_status = 'F'(Fatal),
1148 -- then raise error. In case of 'W'(Warning), Forms should warn to user
1149 -- with fnd_message.warn procedure.
1150 --
1151 if l_min_max_status = 'F' and l_rec.warning_or_error = 'E' then
1152 hr_utility.set_message(801,'PAY_6303_INPUT_VALUE_OUT_RANGE');
1153 hr_utility.raise_error;
1154 end If;
1155 --
1156 -- Execute formula validation.
1157 --
1158 if l_rec.formula_id is not NULL then
1159 chk_formula(
1160 p_formula_id => l_rec.formula_id,
1161 p_entry_value => p_canonical_value,
1162 p_business_group_id => p_business_group_id,
1163 p_assignment_id => p_assignment_id,
1164 p_date_earned => p_effective_date,
1165 p_formula_status => l_formula_status,
1166 p_formula_message => p_formula_message);
1167 end if;
1168 --
1169 -- If warning_or_error = 'E'(Error) and l_formula_status = 'E'(Error),
1170 -- then raise error. In case of 'W'(Warning), Forms should warn to user
1171 -- with fnd_message.warn procedure.
1172 --
1173 if l_formula_status = 'E' and l_rec.warning_or_error = 'E' then
1174 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1175 hr_utility.set_message_token('FORMULA_TEXT',p_formula_message);
1176 hr_utility.raise_error;
1177 end If;
1178 --
1179 -- In case lookup_type validation is applied.
1180 --
1181 if l_rec.lookup_type is not NULL then
1182 --
1183 -- Lookup_type validation with effective_date.
1184 --
1185 if hr_api.not_exists_in_hr_lookups(
1186 p_effective_date => p_effective_date,
1187 p_lookup_type => l_rec.lookup_type,
1188 p_lookup_code => p_canonical_value) then
1189 hr_utility.set_message(801,'HR_7033_ELE_ENTRY_LKUP_INVLD');
1190 hr_utility.set_message_token('LOOKUP_TYPE',l_rec.lookup_type);
1191 hr_utility.raise_error;
1192 end if;
1193 end if;
1194 end if;
1195 --
1196 -- Set output variables.
1197 --
1198 if l_min_max_status = 'F' then
1199 p_min_max_warning := TRUE;
1200 end if;
1201 if l_formula_status = 'E' then
1202 p_formula_warning := TRUE;
1203 end if;
1204 if l_rec.hot_default_flag = 'Y' and p_canonical_value = l_rec.default_value then
1205 p_hot_defaulted := TRUE;
1206 end if;
1207 --
1208 -- When p_input_value_id is NULL.
1209 --
1210 else
1211 p_user_value := NULL;
1212 end if;
1213 end validate_entry_value;
1214 --------------------------------------------------------------------------------
1215 function get_session_date return date
1216 --------------------------------------------------------------------------------
1217 is
1218 --
1219 l_effective_date date;
1220 --
1221 cursor session_date
1222 is
1223 select effective_date
1224 from fnd_sessions
1225 where session_id = userenv('sessionid');
1226 --
1227 begin
1228 --
1229 l_effective_date := null;
1230 --
1231 open session_date;
1232 fetch session_date into l_effective_date;
1233 close session_date;
1234 --
1235 return l_effective_date;
1236 --
1237 end get_session_date;
1238 --------------------------------------------------------------------------------
1239 function get_element_type_id(
1240 p_element_name in varchar2,
1241 p_business_group_id in number,
1242 p_effective_date in date) return number
1243 --------------------------------------------------------------------------------
1244 is
1245 --
1246 l_effective_date date;
1247 l_element_type_id number;
1248 l_reset boolean := false;
1249 l_found boolean := false;
1250 l_tbl_index number;
1251 --
1252 cursor csr_element_type_id
1253 is
1254 select pet.element_type_id
1255 from pay_element_types_f pet
1256 where pet.element_name = p_element_name
1257 and l_effective_date
1258 between pet.effective_start_date and pet.effective_end_date
1259 and nvl(pet.legislation_code,g_legislation_code) = g_legislation_code
1260 and nvl(pet.business_group_id,g_business_group_id) = g_business_group_id;
1261 --
1262 begin
1263 --
1264 l_element_type_id := null;
1265 --
1266 if p_effective_date is null then
1267 l_effective_date := get_session_date;
1268 else
1269 l_effective_date := p_effective_date;
1270 end if;
1271 --
1272 if g_business_group_id is null
1273 or g_business_group_id <> p_business_group_id
1274 or g_session_date <> l_effective_date then
1275 --
1276 g_business_group_id := p_business_group_id;
1277 g_legislation_code := pay_kr_report_pkg.legislation_code(p_business_group_id => g_business_group_id);
1278 g_session_date := l_effective_date;
1279 g_get_element_type_id.element_name.delete;
1280 g_get_element_type_id.element_type_id.delete;
1281 --
1282 l_reset := true;
1283 --
1284 end if;
1285 --
1286 l_tbl_index := g_get_element_type_id.element_type_id.count;
1287 --
1288 if not l_reset then
1289 for i in 1..l_tbl_index loop
1290 if g_get_element_type_id.element_name(i) = p_element_name then
1291 l_found := true;
1292 l_element_type_id := g_get_element_type_id.element_type_id(i);
1293 exit;
1294 end if;
1295 end loop;
1296 end if;
1297 --
1298 if not l_found or l_reset then
1299 --
1300 open csr_element_type_id;
1301 fetch csr_element_type_id into l_element_type_id;
1302 close csr_element_type_id;
1303 --
1304 if l_element_type_id is not null then
1305 g_get_element_type_id.element_type_id(l_tbl_index + 1) := l_element_type_id;
1306 g_get_element_type_id.element_name(l_tbl_index + 1) := p_element_name;
1307 end if;
1308 --
1309 end if;
1310 --
1311 return l_element_type_id;
1312 --
1313 end get_element_type_id;
1314 --------------------------------------------------------------------------------
1315 function get_input_value_id(
1316 p_element_type_id in number,
1317 p_sequence in number, /* Different from display sequence */
1318 p_business_group_id in number,
1319 p_effective_date in date) return number
1320 --------------------------------------------------------------------------------
1321 is
1322 --
1323 l_effective_date date;
1324 l_input_value_id number;
1325 l_get_input_value_id get_input_value_id_rec;
1326 l_reset boolean := false;
1327 l_found boolean := false;
1328 l_tbl_index number;
1329 l_get_index number;
1330 l_seq_index number := 0;
1331 --
1332 cursor csr_input_value_id
1333 is
1334 select piv.element_type_id element_type_id,
1335 piv.input_value_id input_value_id,
1336 piv.display_sequence display_sequence,
1337 piv.lookup_type lookup_type,
1338 piv.mandatory_flag mandatory_flag,
1339 piv.name name,
1340 pivtl.name d_name
1341 from pay_input_values_f_tl pivtl,
1342 pay_input_values_f piv
1343 where piv.element_type_id = p_element_type_id
1344 and l_effective_date
1345 between piv.effective_start_date and piv.effective_end_date
1346 and pivtl.input_value_id = piv.input_value_id
1347 and pivtl.language = userenv('LANG')
1348 order by piv.display_sequence, piv.name;
1349 --
1350 begin
1351 --
1352 l_input_value_id := null;
1353 --
1354 if p_effective_date is null then
1355 l_effective_date := get_session_date;
1356 else
1357 l_effective_date := p_effective_date;
1358 end if;
1359 --
1360 if g_business_group_id is null
1361 or g_business_group_id <> p_business_group_id
1362 or g_session_date <> l_effective_date then
1363 --
1364 g_business_group_id := p_business_group_id;
1365 g_legislation_code := pay_kr_report_pkg.legislation_code(p_business_group_id => g_business_group_id);
1366 g_session_date := l_effective_date;
1367 g_get_input_value_id.element_type_id.delete;
1368 g_get_input_value_id.input_value_id.delete;
1369 g_get_input_value_id.display_sequence.delete;
1370 g_get_input_value_id.lookup_type.delete;
1371 g_get_input_value_id.mandatory_flag.delete;
1372 g_get_input_value_id.name.delete;
1373 g_get_input_value_id.d_name.delete;
1374 g_input_value_index := null;
1375 --
1376 l_reset := true;
1377 --
1378 end if;
1379 --
1380 l_tbl_index := g_get_input_value_id.input_value_id.count;
1381 --
1382 /* Check the start point */
1383 if not l_reset then
1384 for i in 1..l_tbl_index loop
1385 if g_get_input_value_id.element_type_id(i) = p_element_type_id then
1386 l_found := true;
1387 g_input_value_index := i;
1388 exit;
1389 end if;
1390 end loop;
1391 end if;
1392 --
1393 if not l_found or l_reset then
1394 --
1395 open csr_input_value_id;
1396 fetch csr_input_value_id bulk collect into
1397 l_get_input_value_id.element_type_id,
1398 l_get_input_value_id.input_value_id,
1399 l_get_input_value_id.display_sequence,
1400 l_get_input_value_id.lookup_type,
1401 l_get_input_value_id.mandatory_flag,
1402 l_get_input_value_id.name,
1403 l_get_input_value_id.d_name;
1404 close csr_input_value_id;
1405 --
1406 l_get_index := l_get_input_value_id.input_value_id.count;
1407 --
1408 if 0 < l_get_index and l_get_index >= p_sequence then
1409 for j in 1..l_get_index loop
1410 g_get_input_value_id.element_type_id(l_tbl_index + j) := l_get_input_value_id.element_type_id(j);
1411 g_get_input_value_id.input_value_id(l_tbl_index + j) := l_get_input_value_id.input_value_id(j);
1412 g_get_input_value_id.display_sequence(l_tbl_index + j) := l_get_input_value_id.display_sequence(j);
1413 g_get_input_value_id.lookup_type(l_tbl_index + j) := l_get_input_value_id.lookup_type(j);
1414 g_get_input_value_id.mandatory_flag(l_tbl_index + j) := l_get_input_value_id.mandatory_flag(j);
1415 g_get_input_value_id.name(l_tbl_index + j) := l_get_input_value_id.name(j);
1416 g_get_input_value_id.d_name(l_tbl_index + j) := l_get_input_value_id.d_name(j);
1417 if j = p_sequence then
1418 l_input_value_id := l_get_input_value_id.input_value_id(j);
1419 g_input_value_index := l_tbl_index + j;
1420 end if;
1421 end loop;
1422 end if;
1423 else
1424 if g_input_value_index + p_sequence - 1 <= l_tbl_index then
1425 if g_get_input_value_id.element_type_id(g_input_value_index + p_sequence - 1) = p_element_type_id then
1426 l_input_value_id := g_get_input_value_id.input_value_id(g_input_value_index + p_sequence - 1);
1427 g_input_value_index := g_input_value_index + p_sequence - 1;
1428 else
1429 /* Re Check All */
1430 g_input_value_index := null;
1431 for k in 1..l_tbl_index loop
1432 if g_get_input_value_id.element_type_id(k) = p_element_type_id then
1433 l_seq_index := l_seq_index + 1;
1434 if l_seq_index = p_sequence then
1435 l_input_value_id := g_get_input_value_id.input_value_id(k);
1436 g_input_value_index := k;
1437 exit;
1438 end if;
1439 end if;
1440 end loop;
1441 end if;
1442 --/* p_sequence should be 1..15 */
1443 ----for k in g_input_value_index..l_tbl_index loop
1444 ---- if k = g_input_value_index + 15 then
1445 ---- exit;
1446 ---- end if;
1447 --for k in g_input_value_index..g_input_value_index + 14 loop
1448 -- if k = g_input_value_index + p_sequence - 1 then
1449 -- if g_get_input_value_id.element_type_id(k) = p_element_type_id then
1450 -- l_input_value_id := g_get_input_value_id.input_value_id(k);
1451 -- g_input_value_index := j;
1452 -- exit;
1453 -- else
1454 -- g_input_value_index := null;
1455 -- exit;
1456 -- end if;
1457 -- end if;
1458 --end loop;
1459 end if;
1460 end if;
1461 --
1462 if l_input_value_id is null then
1463 g_input_value_index := null;
1464 end if;
1465 --
1466 return l_input_value_id;
1467 --
1468 end get_input_value_id;
1469 --------------------------------------------------------------------------------
1470 function get_input_value_name(
1471 p_element_type_id in number,
1472 p_sequence in number, /* Different from display sequence */
1473 p_business_group_id in number,
1474 p_effective_date in date) return varchar2
1475 --------------------------------------------------------------------------------
1476 is
1477 --
1478 l_effective_date date;
1479 l_input_value_id number;
1480 l_input_value_name pay_input_values_f_tl.name%type;
1481 l_index number;
1482 l_csr_index number := 0;
1483 --
1484 cursor csr_input_value_name
1485 is
1486 select piv.name name,
1487 pivtl.name d_name
1488 from pay_input_values_f_tl pivtl,
1489 pay_input_values_f piv
1490 where piv.element_type_id = p_element_type_id
1491 and l_effective_date
1492 between piv.effective_start_date and piv.effective_end_date
1493 and pivtl.input_value_id = piv.input_value_id
1494 and pivtl.language = userenv('LANG')
1495 order by piv.display_sequence, piv.name;
1496 --
1497 l_csr_input_value_name csr_input_value_name%rowtype;
1498 --
1499 begin
1500 --
1501 l_input_value_id := get_input_value_id(
1502 p_element_type_id => p_element_type_id,
1503 p_sequence => p_sequence,
1504 p_business_group_id => p_business_group_id,
1505 p_effective_date => p_effective_date);
1506 --
1507 if l_input_value_id is not null then
1508 --
1509 l_index := g_get_input_value_id.input_value_id.count;
1510 --
1511 if g_input_value_index is not null then
1512 l_input_value_name := g_get_input_value_id.d_name(g_input_value_index);
1513 else
1514 if l_index > 0 then
1515 for i in 1..l_index loop
1516 if g_get_input_value_id.input_value_id(i) = l_input_value_id then
1517 l_input_value_name := g_get_input_value_id.d_name(i);
1518 exit;
1519 end if;
1520 end loop;
1521 else
1522 if p_effective_date is null then
1523 l_effective_date := get_session_date;
1524 else
1525 l_effective_date := p_effective_date;
1526 end if;
1527 open csr_input_value_name;
1528 loop
1529 fetch csr_input_value_name into l_csr_input_value_name;
1530 exit when csr_input_value_name%notfound;
1531 l_csr_index := l_csr_index + 1;
1532 if l_csr_index = p_sequence then
1533 l_input_value_name := l_csr_input_value_name.d_name;
1534 exit;
1535 --elsif l_csr_index > p_sequence then
1536 -- exit;
1537 end if;
1538 end loop;
1539 close csr_input_value_name;
1540 end if;
1541 end if;
1542 end if;
1543 --
1544 return l_input_value_name;
1545 --
1546 end get_input_value_name;
1547 --------------------------------------------------------------------------------
1548 function get_input_value_d_sequence(
1549 p_element_type_id in number,
1550 p_sequence in number, /* Different from display sequence */
1551 p_business_group_id in number,
1552 p_effective_date in date) return number
1553 --------------------------------------------------------------------------------
1554 is
1555 --
1556 l_effective_date date;
1557 l_input_value_id number;
1558 l_input_value_d_sequence number;
1559 l_index number;
1560 l_csr_index number := 0;
1561 --
1562 cursor csr_input_value_d_sequence
1563 is
1564 select piv.display_sequence display_sequence
1565 from pay_input_values_f piv
1566 where piv.element_type_id = p_element_type_id
1567 and l_effective_date
1568 between piv.effective_start_date and piv.effective_end_date
1569 order by piv.display_sequence, piv.name;
1570 --
1571 l_csr_input_value_d_sequence csr_input_value_d_sequence%rowtype;
1572 --
1573 begin
1574 --
1575 l_input_value_id := get_input_value_id(
1576 p_element_type_id => p_element_type_id,
1577 p_sequence => p_sequence,
1578 p_business_group_id => p_business_group_id,
1579 p_effective_date => p_effective_date);
1580 --
1581 if l_input_value_id is not null then
1582 --
1583 l_index := g_get_input_value_id.input_value_id.count;
1584 --
1585 if g_input_value_index is not null then
1586 l_input_value_d_sequence := g_get_input_value_id.display_sequence(g_input_value_index);
1587 else
1588 if l_index > 0 then
1589 for i in 1..l_index loop
1590 if g_get_input_value_id.input_value_id(i) = l_input_value_id then
1591 l_input_value_d_sequence := g_get_input_value_id.display_sequence(i);
1592 exit;
1593 end if;
1594 end loop;
1595 else
1596 if p_effective_date is null then
1597 l_effective_date := get_session_date;
1598 else
1599 l_effective_date := p_effective_date;
1600 end if;
1601 open csr_input_value_d_sequence;
1602 loop
1603 fetch csr_input_value_d_sequence into l_csr_input_value_d_sequence;
1604 exit when csr_input_value_d_sequence%notfound;
1605 l_csr_index := l_csr_index + 1;
1606 if l_csr_index = p_sequence then
1607 l_input_value_d_sequence := l_csr_input_value_d_sequence.display_sequence;
1608 exit;
1609 --elsif l_csr_index > p_sequence then
1610 -- exit;
1611 end if;
1612 end loop;
1613 close csr_input_value_d_sequence;
1614 end if;
1615 end if;
1616 end if;
1617 --
1618 return l_input_value_d_sequence;
1619 --
1620 end get_input_value_d_sequence;
1621 --------------------------------------------------------------------------------
1622 function get_input_value_lookup_type(
1623 p_element_type_id in number,
1624 p_sequence in number, /* Different from display sequence */
1625 p_business_group_id in number,
1626 p_effective_date in date) return varchar2
1627 --------------------------------------------------------------------------------
1628 is
1629 --
1630 l_effective_date date;
1631 l_input_value_id number;
1632 l_input_value_lookup_type pay_input_values_f.lookup_type%type;
1633 l_index number;
1634 l_csr_index number := 0;
1635 --
1636 cursor csr_input_value_lookup_type
1637 is
1638 select piv.lookup_type lookup_type
1639 from pay_input_values_f piv
1640 where piv.element_type_id = p_element_type_id
1641 and l_effective_date
1642 between piv.effective_start_date and piv.effective_end_date
1643 order by piv.display_sequence, piv.name;
1644 --
1645 l_csr_input_value_lookup_type csr_input_value_lookup_type%rowtype;
1646 --
1647 begin
1648 --
1649 l_input_value_id := get_input_value_id(
1650 p_element_type_id => p_element_type_id,
1651 p_sequence => p_sequence,
1652 p_business_group_id => p_business_group_id,
1653 p_effective_date => p_effective_date);
1654 --
1655 if l_input_value_id is not null then
1656 --
1657 l_index := g_get_input_value_id.input_value_id.count;
1658 --
1659 if g_input_value_index is not null then
1660 l_input_value_lookup_type := g_get_input_value_id.lookup_type(g_input_value_index);
1661 else
1662 if l_index > 0 then
1663 for i in 1..l_index loop
1664 if g_get_input_value_id.input_value_id(i) = l_input_value_id then
1665 l_input_value_lookup_type := g_get_input_value_id.lookup_type(i);
1666 exit;
1667 end if;
1668 end loop;
1669 else
1670 if p_effective_date is null then
1671 l_effective_date := get_session_date;
1672 else
1673 l_effective_date := p_effective_date;
1674 end if;
1675 open csr_input_value_lookup_type;
1676 loop
1677 fetch csr_input_value_lookup_type into l_csr_input_value_lookup_type;
1678 exit when csr_input_value_lookup_type%notfound;
1679 l_csr_index := l_csr_index + 1;
1680 if l_csr_index = p_sequence then
1681 l_input_value_lookup_type := l_csr_input_value_lookup_type.lookup_type;
1682 exit;
1683 --elsif l_csr_index > p_sequence then
1684 -- exit;
1685 end if;
1686 end loop;
1687 close csr_input_value_lookup_type;
1688 end if;
1689 end if;
1690 end if;
1691 --
1692 return l_input_value_lookup_type;
1693 --
1694 end get_input_value_lookup_type;
1695 --------------------------------------------------------------------------------
1696 function get_input_value_mandatory(
1697 p_element_type_id in number,
1698 p_sequence in number, /* Different from display sequence */
1699 p_business_group_id in number,
1700 p_effective_date in date) return varchar2
1701 --------------------------------------------------------------------------------
1702 is
1703 --
1704 l_effective_date date;
1705 l_input_value_id number;
1706 l_input_value_mandatory pay_input_values_f.mandatory_flag%type;
1707 l_index number;
1708 l_csr_index number := 0;
1709 --
1710 cursor csr_input_value_mandatory
1711 is
1712 select piv.mandatory_flag mandatory_flag
1713 from pay_input_values_f piv
1714 where piv.element_type_id = p_element_type_id
1715 and l_effective_date
1716 between piv.effective_start_date and piv.effective_end_date
1717 order by piv.display_sequence, piv.name;
1718 --
1719 l_csr_input_value_mandatory csr_input_value_mandatory%rowtype;
1720 --
1721 begin
1722 --
1723 l_input_value_id := get_input_value_id(
1724 p_element_type_id => p_element_type_id,
1725 p_sequence => p_sequence,
1726 p_business_group_id => p_business_group_id,
1727 p_effective_date => p_effective_date);
1728 --
1729 if l_input_value_id is not null then
1730 --
1731 l_index := g_get_input_value_id.input_value_id.count;
1732 --
1733 if g_input_value_index is not null then
1734 l_input_value_mandatory := g_get_input_value_id.mandatory_flag(g_input_value_index);
1735 else
1736 if l_index > 0 then
1737 for i in 1..l_index loop
1738 if g_get_input_value_id.input_value_id(i) = l_input_value_id then
1739 l_input_value_mandatory := g_get_input_value_id.mandatory_flag(i);
1740 exit;
1741 end if;
1742 end loop;
1743 else
1744 if p_effective_date is null then
1745 l_effective_date := get_session_date;
1746 else
1747 l_effective_date := p_effective_date;
1748 end if;
1749 open csr_input_value_mandatory;
1750 loop
1751 fetch csr_input_value_mandatory into l_csr_input_value_mandatory;
1752 exit when csr_input_value_mandatory%notfound;
1753 l_csr_index := l_csr_index + 1;
1754 if l_csr_index = p_sequence then
1755 l_input_value_mandatory := l_csr_input_value_mandatory.mandatory_flag;
1756 exit;
1757 --elsif l_csr_index > p_sequence then
1758 -- exit;
1759 end if;
1760 end loop;
1761 close csr_input_value_mandatory;
1762 end if;
1763 end if;
1764 end if;
1765 --
1766 return l_input_value_mandatory;
1767 --
1768 end get_input_value_mandatory;
1769 --------------------------------------------------------------------------------
1770 procedure get_default_value(
1771 p_assignment_id in number,
1772 p_element_type_id in number,
1773 p_business_group_id in varchar2,
1774 p_entry_type in varchar2,
1775 p_effective_date in date,
1776 p_element_link_id out NOCOPY number,
1777 p_input_value_id1 out NOCOPY number,
1778 p_input_value_id2 out NOCOPY number,
1779 p_input_value_id3 out NOCOPY number,
1780 p_input_value_id4 out NOCOPY number,
1781 p_input_value_id5 out NOCOPY number,
1782 p_input_value_id6 out NOCOPY number,
1783 p_input_value_id7 out NOCOPY number,
1784 p_input_value_id8 out NOCOPY number,
1785 p_input_value_id9 out NOCOPY number,
1786 p_input_value_id10 out NOCOPY number,
1787 p_input_value_id11 out NOCOPY number,
1788 p_input_value_id12 out NOCOPY number,
1789 p_input_value_id13 out NOCOPY number,
1790 p_input_value_id14 out NOCOPY number,
1791 p_input_value_id15 out NOCOPY number,
1792 p_default_value1 out NOCOPY varchar2,
1793 p_default_value2 out NOCOPY varchar2,
1794 p_default_value3 out NOCOPY varchar2,
1795 p_default_value4 out NOCOPY varchar2,
1796 p_default_value5 out NOCOPY varchar2,
1797 p_default_value6 out NOCOPY varchar2,
1798 p_default_value7 out NOCOPY varchar2,
1799 p_default_value8 out NOCOPY varchar2,
1800 p_default_value9 out NOCOPY varchar2,
1801 p_default_value10 out NOCOPY varchar2,
1802 p_default_value11 out NOCOPY varchar2,
1803 p_default_value12 out NOCOPY varchar2,
1804 p_default_value13 out NOCOPY varchar2,
1805 p_default_value14 out NOCOPY varchar2,
1806 p_default_value15 out NOCOPY varchar2,
1807 p_b_default_value1 out NOCOPY varchar2,
1808 p_b_default_value2 out NOCOPY varchar2,
1809 p_b_default_value3 out NOCOPY varchar2,
1810 p_b_default_value4 out NOCOPY varchar2,
1811 p_b_default_value5 out NOCOPY varchar2,
1812 p_b_default_value6 out NOCOPY varchar2,
1813 p_b_default_value7 out NOCOPY varchar2,
1814 p_b_default_value8 out NOCOPY varchar2,
1815 p_b_default_value9 out NOCOPY varchar2,
1816 p_b_default_value10 out NOCOPY varchar2,
1817 p_b_default_value11 out NOCOPY varchar2,
1818 p_b_default_value12 out NOCOPY varchar2,
1819 p_b_default_value13 out NOCOPY varchar2,
1820 p_b_default_value14 out NOCOPY varchar2,
1821 p_b_default_value15 out NOCOPY varchar2,
1822 p_effective_start_date in out NOCOPY date,
1823 p_effective_end_date in out NOCOPY date)
1824 is
1825 --
1826 l_element_type_id number;
1827 l_csr_index number := 0;
1828 type input_value_tbl_rec is record(
1829 input_value_id input_value_id_tbl,
1830 -- display_sequence display_sequence_tbl,
1831 default_value screen_entry_value_tbl,
1832 b_default_value screen_entry_value_tbl);
1833 l_input_value_tbl input_value_tbl_rec;
1834 --
1835 cursor csr_default_value
1836 is
1837 select piv.input_value_id input_value_id,
1838 piv.display_sequence display_sequence,
1839 hr_chkfmt.changeformat(
1840 decode(piv.lookup_type,
1841 null,
1842 decode(piv.hot_default_flag,
1843 'Y',nvl(pliv.default_value,piv.default_value),
1844 pliv.default_value),
1845 hr_general.decode_lookup(piv.lookup_type,
1846 decode(piv.hot_default_flag,
1847 'Y',nvl(pliv.default_value,piv.default_value),
1848 pliv.default_value))),
1849 piv.uom,
1850 pet.output_currency_code) default_value,
1851 decode(piv.lookup_type,
1852 null,
1853 null,
1854 decode(piv.hot_default_flag,
1855 'Y',nvl(pliv.default_value,piv.default_value),
1856 pliv.default_value)) b_default_value
1857 from pay_element_types_f pet,
1858 pay_input_values_f piv,
1859 pay_link_input_values_f pliv
1860 where pliv.element_link_id = p_element_link_id
1861 and p_effective_date
1862 between pliv.effective_start_date and pliv.effective_end_date
1863 and piv.input_value_id = pliv.input_value_id
1864 and p_effective_date
1865 between piv.effective_start_date and piv.effective_end_date
1866 and pet.element_type_id = piv.element_type_id
1867 and p_effective_date
1868 between pet.effective_start_date and pet.effective_end_date
1869 order by piv.display_sequence, piv.name;
1870 --
1871 l_csr_default_value csr_default_value%rowtype;
1872 --
1873 begin
1874 --
1875 -- if p_element_type_id is null then
1876 -- l_element_type_id := pay_kr_sep_form_pkg.get_element_type_id(p_element_name,p_business_group_id,p_effective_date);
1877 -- else
1878 -- l_element_type_id := p_element_type_id;
1879 -- end if;
1880 --
1881 p_element_link_id := hr_entry_api.get_link(
1882 p_assignment_id => p_assignment_id,
1883 -- p_element_type_id => l_element_type_id,
1884 p_element_type_id => p_element_type_id,
1885 p_session_date => p_effective_date);
1886 --
1887 if p_element_link_id is null then
1888 hr_utility.set_message(801,'HR_7027_ELE_ENTRY_EL_NOT_EXST');
1889 hr_utility.set_message_token('DATE',fnd_date.date_to_displaydate(p_effective_date));
1890 hr_utility.raise_error;
1891 end if;
1892 --
1893 chk_entry(
1894 p_element_entry_id => NULL,
1895 p_assignment_id => p_assignment_id,
1896 p_element_link_id => p_element_link_id,
1897 p_entry_type => p_entry_type,
1898 p_effective_date => p_effective_date,
1899 p_validation_start_date => NULL,
1900 p_validation_end_date => NULL,
1901 p_effective_start_date => p_effective_start_date,
1902 p_effective_end_date => p_effective_end_date,
1903 p_usage => 'INSERT',
1904 p_dt_update_mode => NULL,
1905 p_dt_delete_mode => NULL);
1906 --
1907 open csr_default_value;
1908 loop
1909 fetch csr_default_value into l_csr_default_value;
1910 exit when csr_default_value%notfound;
1911 l_csr_index := l_csr_index + 1;
1912 l_input_value_tbl.input_value_id(l_csr_index) := l_csr_default_value.input_value_id;
1913 -- l_input_value_tbl.display_sequence(l_csr_index) := l_csr_default_value.display_sequence;
1914 l_input_value_tbl.default_value(l_csr_index) := l_csr_default_value.default_value;
1915 l_input_value_tbl.b_default_value(l_csr_index) := l_csr_default_value.b_default_value;
1916 end loop;
1917 close csr_default_value;
1918 --
1919 if l_csr_index < 15 then
1920 for i in l_csr_index + 1..15 loop
1921 l_input_value_tbl.input_value_id(i) := null;
1922 -- l_input_value_tbl.display_sequence(i) := null;
1923 l_input_value_tbl.default_value(i) := null;
1924 l_input_value_tbl.b_default_value(i) := null;
1925 end loop;
1926 end if;
1927 --
1928 p_input_value_id1 := l_input_value_tbl.input_value_id(1);
1929 p_input_value_id2 := l_input_value_tbl.input_value_id(2);
1930 p_input_value_id3 := l_input_value_tbl.input_value_id(3);
1931 p_input_value_id4 := l_input_value_tbl.input_value_id(4);
1932 p_input_value_id5 := l_input_value_tbl.input_value_id(5);
1933 p_input_value_id6 := l_input_value_tbl.input_value_id(6);
1934 p_input_value_id7 := l_input_value_tbl.input_value_id(7);
1935 p_input_value_id8 := l_input_value_tbl.input_value_id(8);
1936 p_input_value_id9 := l_input_value_tbl.input_value_id(9);
1937 p_input_value_id10 := l_input_value_tbl.input_value_id(10);
1938 p_input_value_id11 := l_input_value_tbl.input_value_id(11);
1939 p_input_value_id12 := l_input_value_tbl.input_value_id(12);
1940 p_input_value_id13 := l_input_value_tbl.input_value_id(13);
1941 p_input_value_id14 := l_input_value_tbl.input_value_id(14);
1942 p_input_value_id15 := l_input_value_tbl.input_value_id(15);
1943 --
1944 p_default_value1 := l_input_value_tbl.default_value(1);
1945 p_default_value2 := l_input_value_tbl.default_value(2);
1946 p_default_value3 := l_input_value_tbl.default_value(3);
1947 p_default_value4 := l_input_value_tbl.default_value(4);
1948 p_default_value5 := l_input_value_tbl.default_value(5);
1949 p_default_value6 := l_input_value_tbl.default_value(6);
1950 p_default_value7 := l_input_value_tbl.default_value(7);
1951 p_default_value8 := l_input_value_tbl.default_value(8);
1952 p_default_value9 := l_input_value_tbl.default_value(9);
1953 p_default_value10 := l_input_value_tbl.default_value(10);
1954 p_default_value11 := l_input_value_tbl.default_value(11);
1955 p_default_value12 := l_input_value_tbl.default_value(12);
1956 p_default_value13 := l_input_value_tbl.default_value(13);
1957 p_default_value14 := l_input_value_tbl.default_value(14);
1958 p_default_value15 := l_input_value_tbl.default_value(15);
1959 --
1960 p_b_default_value1 := l_input_value_tbl.b_default_value(1);
1961 p_b_default_value2 := l_input_value_tbl.b_default_value(2);
1962 p_b_default_value3 := l_input_value_tbl.b_default_value(3);
1963 p_b_default_value4 := l_input_value_tbl.b_default_value(4);
1964 p_b_default_value5 := l_input_value_tbl.b_default_value(5);
1965 p_b_default_value6 := l_input_value_tbl.b_default_value(6);
1966 p_b_default_value7 := l_input_value_tbl.b_default_value(7);
1967 p_b_default_value8 := l_input_value_tbl.b_default_value(8);
1968 p_b_default_value9 := l_input_value_tbl.b_default_value(9);
1969 p_b_default_value10 := l_input_value_tbl.b_default_value(10);
1970 p_b_default_value11 := l_input_value_tbl.b_default_value(11);
1971 p_b_default_value12 := l_input_value_tbl.b_default_value(12);
1972 p_b_default_value13 := l_input_value_tbl.b_default_value(13);
1973 p_b_default_value14 := l_input_value_tbl.b_default_value(14);
1974 p_b_default_value15 := l_input_value_tbl.b_default_value(15);
1975 --
1976 end get_default_value;
1977 --------------------------------------------------------------------------------
1978 function get_screen_entry_value(
1979 p_element_type_id in number,
1980 p_sequence in number, /* Different from display sequence */
1981 p_business_group_id in number,
1982 p_ee_element_entry_id in number,
1983 p_ee_effective_start_date in date,
1984 p_ee_effective_end_date in date) return varchar2
1985 --------------------------------------------------------------------------------
1986 is
1987 --
1988 l_value pay_element_entry_values_f.screen_entry_value%type;
1989 l_input_value_id number;
1990 l_input_value_id_tbl input_value_id_tbl;
1991 l_screen_entry_value_tbl screen_entry_value_tbl;
1992 --
1993 cursor csr_entry_value
1994 is
1995 select peev.input_value_id input_value_id,
1996 peev.screen_entry_value screen_entry_value
1997 from pay_element_entry_values_f peev
1998 where peev.element_entry_id = p_ee_element_entry_id
1999 and peev.effective_start_date = p_ee_effective_start_date
2000 and peev.effective_end_date = p_ee_effective_end_date;
2001 --
2002 begin
2003 --
2004 l_value := null;
2005 --
2006 if g_element_entry_id is null or p_ee_element_entry_id <> g_element_entry_id then
2007 --
2008 open csr_entry_value;
2009 fetch csr_entry_value bulk collect into l_input_value_id_tbl,
2010 l_screen_entry_value_tbl;
2011 close csr_entry_value;
2012 --
2013 g_screen_entry_value_tbl.delete;
2014 for i in 1..l_input_value_id_tbl.count loop
2015 g_screen_entry_value_tbl(l_input_value_id_tbl(i)) := l_screen_entry_value_tbl(i);
2016 end loop;
2017 g_element_entry_id := p_ee_element_entry_id;
2018 end if;
2019 --
2020 l_input_value_id := get_input_value_id(p_element_type_id, p_sequence, p_business_group_id);
2021 --
2022 if g_screen_entry_value_tbl.exists(l_input_value_id) then
2023 l_value := g_screen_entry_value_tbl(l_input_value_id);
2024 end if;
2025 --
2026 return l_value;
2027 --
2028 end get_screen_entry_value;
2029 --------------------------------------------------------------------------------
2030 function get_entry_value(
2031 p_element_type_id in number,
2032 p_sequence in number, /* Different from display sequence */
2033 p_business_group_id in number,
2034 p_ee_element_entry_id in number,
2035 p_ee_effective_start_date in date,
2036 p_ee_effective_end_date in date,
2037 p_el_element_link_id in number) return varchar2
2038 --------------------------------------------------------------------------------
2039 is
2040 --
2041 l_input_value_id number;
2042 l_screen_value pay_element_entry_values_f.screen_entry_value%type;
2043 l_value varchar2(240);
2044 --
2045 cursor csr_entry_value
2046 is
2047 select substr(hr_chkfmt.changeformat(
2048 decode(piv.lookup_type,
2049 null,
2050 nvl(l_screen_value,decode(piv.hot_default_flag,
2051 'Y',nvl(pliv.default_value,piv.default_value),
2052 pliv.default_value)),
2053 hr_general.decode_lookup(piv.lookup_type,
2054 nvl(l_screen_value,decode(piv.hot_default_flag,
2055 'Y',nvl(pliv.default_value,piv.default_value),
2056 pliv.default_value)))),piv.uom,pet.output_currency_code),1,60) entry_value
2057 from pay_element_types_f pet,
2058 pay_input_values_f piv,
2059 pay_link_input_values_f pliv
2060 where pliv.element_link_id = p_el_element_link_id
2061 and pliv.input_value_id = l_input_value_id
2062 and g_session_date /* set by get_input_value_id */
2063 between pliv.effective_start_date and pliv.effective_end_date
2064 and piv.input_value_id = pliv.input_value_id
2065 and g_session_date
2066 between piv.effective_start_date and piv.effective_end_date
2067 and pet.element_type_id = piv.element_type_id
2068 and g_session_date
2069 between pet.effective_start_date and pet.effective_end_date;
2070 --
2071 begin
2072 --
2073 l_input_value_id := get_input_value_id(p_element_type_id, p_sequence, p_business_group_id);
2074 --
2075 l_screen_value := get_screen_entry_value(p_element_type_id => p_element_type_id,
2076 p_sequence => p_sequence,
2077 p_business_group_id => p_business_group_id,
2078 p_ee_element_entry_id => p_ee_element_entry_id,
2079 p_ee_effective_start_date => p_ee_effective_start_date,
2080 p_ee_effective_end_date => p_ee_effective_end_date);
2081 --
2082 open csr_entry_value;
2083 fetch csr_entry_value into l_value;
2084 close csr_entry_value;
2085 --
2086 return l_value;
2087 --
2088 end get_entry_value;
2089 --------------------------------------------------------------------------------
2090 --
2091 -- Bug# 2425705
2092 -- Procedure create_entries gets the element_type_ids for the assignments_ids which
2093 -- are passed from the form and calls procedure create_entry_for_assignment to create.
2094 -- an entry for each selected element_type_id.
2095 -- This procedure is overloaded.
2096 --
2097 --------------------------------------------------------------------------------
2098 procedure create_entries(p_assignment_id_tbl g_assignment_id_tbl%type,
2099 p_element_set_id pay_element_type_rules.element_set_id%type,
2100 p_run_type_id pay_run_types.run_type_id%type,
2101 p_business_group_id hr_assignment_sets.business_group_id%type,
2102 p_session_date date)
2103 is
2104 i number;
2105 p_element_type_id pay_element_types.element_type_id%type;
2106 p_assignment_id pay_assignment_actions.assignment_id%type;
2107 l_element_entry_id number;
2108 l_effective_start_date date;
2109 l_effective_end_date date;
2110 l_object_version_number number;
2111 cursor csr_get_element_type_id(p_assignment_id pay_assignment_actions.assignment_id%type,
2112 p_element_set_id pay_element_type_rules.element_set_id%type,
2113 p_run_type_id pay_run_types.run_type_id%type,
2114 p_business_group_id pay_element_types.business_group_id%type,
2115 p_session_date date)
2116 is
2117 select petr.element_type_id
2118 from pay_element_type_rules petr
2119 where petr.element_set_id = p_element_set_id
2120 and petr.include_or_exclude = 'I'
2121 and not exists(select null
2122 from pay_element_type_rules petr
2123 where petr.element_set_id = p_element_set_id
2124 and petr.include_or_exclude = 'E')
2125 and not exists(select null
2126 from pay_element_type_usages_f npetu
2127 where npetu.element_type_id = petr.element_type_id
2128 and p_session_date between npetu.effective_start_date and npetu.effective_end_date
2129 and npetu.inclusion_flag = 'N'
2130 and npetu.run_type_id = p_run_type_id
2131 and npetu.business_group_id = p_business_group_id)
2132 and nvl(hr_entry_api.get_link(p_assignment_id,petr.element_type_id,p_session_date),0) <> 0
2133 and petr.element_type_id not in(
2134 select distinct pet.element_type_id
2135 from pay_element_entries_f pee,
2136 pay_element_types_f pet,
2137 pay_element_links_f pel
2138 where assignment_id = p_assignment_id
2139 and pet.element_type_id = pel.element_type_id
2140 and pee.element_link_id = pel.element_link_id
2141 and p_session_date between pet.effective_start_date and pet.effective_end_date
2142 and p_session_date between pel.effective_start_date and pel.effective_end_date
2143 and p_session_date between pee.effective_start_date and pee.effective_end_date
2144 and (pet.business_group_id is null or pet.business_group_id = p_business_group_id)
2145 and (pel.business_group_id is null or pel.business_group_id = p_business_group_id));
2146 begin
2147 for i in p_assignment_id_tbl.first..p_assignment_id_tbl.last
2148 loop
2149 open csr_get_element_type_id(p_assignment_id_tbl(i),p_element_set_id,p_run_type_id,p_business_group_id,p_session_date);
2150 loop
2151 fetch csr_get_element_type_id into p_element_type_id;
2152 exit when csr_get_element_type_id%notfound;
2153 create_entry_for_assignment(p_assignment_id => p_assignment_id_tbl(i),
2154 p_element_type_id => p_element_type_id,
2155 p_business_group_id => p_business_group_id,
2156 p_entry_type => 'E',
2157 p_effective_date => p_session_date,
2158 p_effective_start_date => l_effective_start_date,
2159 p_effective_end_date => l_effective_end_date,
2160 p_element_entry_id => l_element_entry_id,
2161 p_object_version_number => l_object_version_number);
2162 end loop;
2163 close csr_get_element_type_id;
2164 end loop;
2165 exception
2166 when others then
2167 if csr_get_element_type_id%isopen then
2168 close csr_get_element_type_id;
2169 end if;
2170 end create_entries;
2171 --------------------------------------------------------------------------------
2172 procedure create_entries(p_assignment_set_id hr_assignment_sets.assignment_set_id%type,
2173 p_element_set_id pay_element_type_rules.element_set_id%type,
2174 p_run_type_id pay_run_types.run_type_id%type,
2175 p_business_group_id hr_assignment_sets.business_group_id%type,
2176 p_payroll_id hr_assignment_sets.payroll_id%type,
2177 p_session_date date)
2178 is
2179 p_element_type_id pay_element_types.element_type_id%type;
2180 l_element_entry_id number;
2181 l_effective_start_date date;
2182 l_effective_end_date date;
2183 l_object_version_number number;
2184
2185 cursor csr_get_assignments
2186 is
2187 select hasa.assignment_id
2188 from hr_assignment_sets has,
2189 hr_assignment_set_amendments hasa
2190 where business_group_id = p_business_group_id
2191 and has.assignment_set_id = p_assignment_set_id
2192 and has.payroll_id = p_payroll_id
2193 and hasa.assignment_set_id = has.assignment_set_id
2194 and hasa.include_or_exclude = 'I';
2195 cursor csr_get_element_type_id(p_assignment_id pay_assignment_actions.assignment_id%type,
2196 p_element_set_id pay_element_type_rules.element_set_id%type,
2197 p_run_type_id pay_run_types.run_type_id%type,
2198 p_business_group_id pay_element_types.business_group_id%type,
2199 p_session_date date)
2200 is
2201 select petr.element_type_id
2202 from pay_element_type_rules petr
2203 where petr.element_set_id = p_element_set_id
2204 and petr.include_or_exclude = 'I'
2205 and not exists(select null
2206 from pay_element_type_rules petr
2207 where petr.element_set_id = p_element_set_id
2208 and petr.include_or_exclude = 'E')
2209 and not exists(select null
2210 from pay_element_type_usages_f npetu
2211 where npetu.element_type_id = petr.element_type_id
2212 and p_session_date between npetu.effective_start_date and npetu.effective_end_date
2213 and npetu.inclusion_flag = 'N'
2214 and npetu.run_type_id = p_run_type_id
2215 and npetu.business_group_id = p_business_group_id)
2216 and nvl(hr_entry_api.get_link(p_assignment_id,petr.element_type_id,p_session_date),0) <> 0
2217 and petr.element_type_id not in(
2218 select distinct pet.element_type_id
2219 from pay_element_entries_f pee,
2220 pay_element_types_f pet,
2221 pay_element_links_f pel
2222 where assignment_id = p_assignment_id
2223 and pet.element_type_id = pel.element_type_id
2224 and pee.element_link_id = pel.element_link_id
2225 and p_session_date between pet.effective_start_date and pet.effective_end_date
2226 and p_session_date between pel.effective_start_date and pel.effective_end_date
2227 and p_session_date between pee.effective_start_date and pee.effective_end_date
2228 and (pet.business_group_id is null or pet.business_group_id = p_business_group_id)
2229 and (pel.business_group_id is null or pel.business_group_id = p_business_group_id));
2230 begin
2231 open csr_get_assignments;
2232 fetch csr_get_assignments bulk collect into g_assignment_id_tbl;
2233 close csr_get_assignments;
2234
2235 for i in g_assignment_id_tbl.first..g_assignment_id_tbl.last
2236 loop
2237 open csr_get_element_type_id(g_assignment_id_tbl(i),p_element_set_id,p_run_type_id,p_business_group_id,p_session_date);
2238 loop
2239 fetch csr_get_element_type_id into p_element_type_id;
2240 exit when csr_get_element_type_id%notfound;
2241 create_entry_for_assignment(p_assignment_id => g_assignment_id_tbl(i),
2242 p_element_type_id => p_element_type_id,
2243 p_business_group_id => p_business_group_id,
2244 p_entry_type => 'E',
2245 p_effective_date => p_session_date,
2246 p_effective_start_date => l_effective_start_date,
2247 p_effective_end_date => l_effective_end_date,
2248 p_element_entry_id => l_element_entry_id,
2249 p_object_version_number => l_object_version_number);
2250 end loop;
2251 close csr_get_element_type_id;
2252 end loop;
2253 exception
2254 when others then
2255 if csr_get_element_type_id%isopen then
2256 close csr_get_element_type_id;
2257 end if;
2258 end create_entries;
2259 --------------------------------------------------------------------------------
2260 --
2261 -- Bug# 2425705
2262 -- Procedure create_entry_for_assignment creates an entry for the assignments which
2263 -- are passed from the Separation Pay form.
2264 --
2265 --------------------------------------------------------------------------------
2266 procedure create_entry_for_assignment(p_assignment_id in pay_assignment_actions.assignment_id%type,
2267 p_element_type_id in pay_element_types.element_type_id%type,
2268 p_business_group_id in pay_element_types.business_group_id%type,
2269 p_entry_type in pay_element_entries_f.entry_type%type,
2270 p_effective_date in date,
2271 p_effective_start_date in out NOCOPY date,
2272 p_effective_end_date in out NOCOPY date,
2273 p_element_entry_id out NOCOPY pay_element_entries_f.element_entry_id%type,
2274 p_object_version_number out NOCOPY number)
2275 is
2276 l_element_link_id number;
2277 l_input_value_id1 number;
2278 l_input_value_id2 number;
2279 l_input_value_id3 number;
2280 l_input_value_id4 number;
2281 l_input_value_id5 number;
2282 l_input_value_id6 number;
2283 l_input_value_id7 number;
2284 l_input_value_id8 number;
2285 l_input_value_id9 number;
2286 l_input_value_id10 number;
2287 l_input_value_id11 number;
2288 l_input_value_id12 number;
2289 l_input_value_id13 number;
2290 l_input_value_id14 number;
2291 l_input_value_id15 number;
2292 l_default_value1 varchar2(1000);
2293 l_default_value2 varchar2(1000);
2294 l_default_value3 varchar2(1000);
2295 l_default_value4 varchar2(1000);
2296 l_default_value5 varchar2(1000);
2297 l_default_value6 varchar2(1000);
2298 l_default_value7 varchar2(1000);
2299 l_default_value8 varchar2(1000);
2300 l_default_value9 varchar2(1000);
2301 l_default_value10 varchar2(1000);
2302 l_default_value11 varchar2(1000);
2303 l_default_value12 varchar2(1000);
2304 l_default_value13 varchar2(1000);
2305 l_default_value14 varchar2(1000);
2306 l_default_value15 varchar2(1000);
2307 l_b_default_value1 varchar2(1000);
2308 l_b_default_value2 varchar2(1000);
2309 l_b_default_value3 varchar2(1000);
2310 l_b_default_value4 varchar2(1000);
2311 l_b_default_value5 varchar2(1000);
2312 l_b_default_value6 varchar2(1000);
2313 l_b_default_value7 varchar2(1000);
2314 l_b_default_value8 varchar2(1000);
2315 l_b_default_value9 varchar2(1000);
2316 l_b_default_value10 varchar2(1000);
2317 l_b_default_value11 varchar2(1000);
2318 l_b_default_value12 varchar2(1000);
2319 l_b_default_value13 varchar2(1000);
2320 l_b_default_value14 varchar2(1000);
2321 l_b_default_value15 varchar2(1000);
2322 begin
2323 savepoint period_not_exists;
2324 pay_kr_sep_form_pkg.get_default_value(
2325 p_assignment_id => p_assignment_id,
2326 p_element_type_id => p_element_type_id,
2327 p_business_group_id => p_business_group_id,
2328 p_entry_type => 'E',
2329 p_effective_date => p_effective_date,
2330 p_element_link_id => l_element_link_id,
2331 p_input_value_id1 => l_input_value_id1,
2332 p_input_value_id2 => l_input_value_id2,
2333 p_input_value_id3 => l_input_value_id3,
2334 p_input_value_id4 => l_input_value_id4,
2335 p_input_value_id5 => l_input_value_id5,
2336 p_input_value_id6 => l_input_value_id6,
2337 p_input_value_id7 => l_input_value_id7,
2338 p_input_value_id8 => l_input_value_id8,
2339 p_input_value_id9 => l_input_value_id9,
2340 p_input_value_id10 => l_input_value_id10,
2341 p_input_value_id11 => l_input_value_id11,
2342 p_input_value_id12 => l_input_value_id12,
2343 p_input_value_id13 => l_input_value_id13,
2344 p_input_value_id14 => l_input_value_id14,
2345 p_input_value_id15 => l_input_value_id15,
2346 p_default_value1 => l_default_value1 ,
2347 p_default_value2 => l_default_value2 ,
2348 p_default_value3 => l_default_value3 ,
2349 p_default_value4 => l_default_value4 ,
2350 p_default_value5 => l_default_value5 ,
2351 p_default_value6 => l_default_value6 ,
2352 p_default_value7 => l_default_value7 ,
2353 p_default_value8 => l_default_value8 ,
2354 p_default_value9 => l_default_value9 ,
2355 p_default_value10 => l_default_value10 ,
2356 p_default_value11 => l_default_value11 ,
2357 p_default_value12 => l_default_value12 ,
2358 p_default_value13 => l_default_value13 ,
2359 p_default_value14 => l_default_value14 ,
2360 p_default_value15 => l_default_value15 ,
2361 p_b_default_value1 => l_b_default_value1,
2362 p_b_default_value2 => l_b_default_value2,
2363 p_b_default_value3 => l_b_default_value3,
2364 p_b_default_value4 => l_b_default_value4,
2365 p_b_default_value5 => l_b_default_value5,
2366 p_b_default_value6 => l_b_default_value6,
2367 p_b_default_value7 => l_b_default_value7,
2368 p_b_default_value8 => l_b_default_value8,
2369 p_b_default_value9 => l_b_default_value9,
2370 p_b_default_value10 => l_b_default_value10,
2371 p_b_default_value11 => l_b_default_value11,
2372 p_b_default_value12 => l_b_default_value12,
2373 p_b_default_value13 => l_b_default_value13,
2374 p_b_default_value14 => l_b_default_value14,
2375 p_b_default_value15 => l_b_default_value15,
2376 p_effective_start_date => p_effective_start_date,
2377 p_effective_end_date => p_effective_end_date);
2378 pay_kr_sep_form_pkg.insert_element_entry(
2379 p_validate => false,
2380 p_assignment_id => p_assignment_id,
2381 p_business_group_id => p_business_group_id,
2382 p_effective_date => p_effective_date,
2383 p_element_link_id => l_element_link_id,
2384 p_input_value_id1 => l_input_value_id1,
2385 p_input_value_id2 => l_input_value_id2,
2386 p_input_value_id3 => l_input_value_id3,
2387 p_input_value_id4 => l_input_value_id4,
2388 p_input_value_id5 => l_input_value_id5,
2389 p_input_value_id6 => l_input_value_id6,
2390 p_input_value_id7 => l_input_value_id7,
2391 p_input_value_id8 => l_input_value_id8,
2392 p_input_value_id9 => l_input_value_id9,
2393 p_input_value_id10 => l_input_value_id10,
2394 p_input_value_id11 => l_input_value_id11,
2395 p_input_value_id12 => l_input_value_id12,
2396 p_input_value_id13 => l_input_value_id13,
2397 p_input_value_id14 => l_input_value_id14,
2398 p_input_value_id15 => l_input_value_id15,
2399 p_entry_value1 => l_default_value1 ,
2400 p_entry_value2 => l_default_value2 ,
2401 p_entry_value3 => l_default_value3 ,
2402 p_entry_value4 => l_default_value4 ,
2403 p_entry_value5 => l_default_value5 ,
2404 p_entry_value6 => l_default_value6 ,
2405 p_entry_value7 => l_default_value7 ,
2406 p_entry_value8 => l_default_value8 ,
2407 p_entry_value9 => l_default_value9 ,
2408 p_entry_value10 => l_default_value10 ,
2409 p_entry_value11 => l_default_value11 ,
2410 p_entry_value12 => l_default_value12 ,
2411 p_entry_value13 => l_default_value13 ,
2412 p_entry_value14 => l_default_value14 ,
2413 p_entry_value15 => l_default_value15 ,
2414 p_element_entry_id => p_element_entry_id,
2415 p_effective_start_date => p_effective_start_date,
2416 p_effective_end_date => p_effective_end_date,
2417 p_object_version_number => p_object_version_number);
2418 exception
2419 when others then
2420 rollback to period_not_exists;
2421 end create_entry_for_assignment;
2422 --------------------------------------------------------------------------------
2423 --
2424 -- Bug# 2425705
2425 -- get_employee_status returns the status of an employee.
2426 -- 'U' if an employee does not have an Interim Separation Pay run or Separation Pay run.
2427 -- 'P' if an employee has an Interim Separation Pay run or Separation Pay run.
2428 --
2429 --------------------------------------------------------------------------------
2430 function get_employee_status(p_assignment_id pay_assignment_actions.assignment_id%type,
2431 p_run_type_name pay_run_types.run_type_name%type,
2432 p_date_earned date) return varchar2
2433 is
2434 status varchar2(1):='U';
2435 cursor csr_get_status
2436 is
2437 select decode(prt.run_type_name,'SEP','P','SEP_I','P','U')
2438 from pay_assignment_actions paa,
2439 pay_payroll_actions ppa,
2440 pay_run_types_f prt
2441 where ppa.payroll_action_id = paa.payroll_action_id
2442 and ppa.run_type_id = prt.run_type_id
2443 and prt.run_type_name = p_run_type_name
2444 and paa.assignment_id = p_assignment_id
2445 and paa.source_action_id is not null
2446 and ppa.effective_date between trunc(p_date_earned,'YYYY') and p_date_earned
2447 order by prt.run_type_name desc;
2448
2449 begin
2450 open csr_get_status;
2451 fetch csr_get_status into status;
2452 if csr_get_status%notfound then
2453 status:='U';
2454 end if;
2455 close csr_get_status;
2456 return status;
2457 end;
2458 ------------------------------------------------------------------------------------
2459 end pay_kr_sep_form_pkg;