[Home] [Help]
PACKAGE BODY: APPS.PQP_GB_UNPAID_TEMPLATE
Source
1 PACKAGE BODY pqp_gb_unpaid_template AS
2 /* $Header: pqpgbupd.pkb 120.0 2005/05/29 02:03:20 appldev noship $ */
3
4 g_package_name VARCHAR2(61) := 'pqp_gb_unpaid_template.';
5 g_debug BOOLEAN;
6
7
8
9 TYPE t_ele_name IS TABLE OF pay_element_types_f.element_name%TYPE
10 INDEX BY BINARY_INTEGER;
11
12 TYPE t_bal_name IS TABLE OF pay_balance_types.balance_name%TYPE
13 INDEX BY BINARY_INTEGER;
14
15
16 TYPE t_ele_reporting_name IS TABLE OF pay_element_types_f.reporting_name%TYPE
17 INDEX BY BINARY_INTEGER;
18
19 TYPE t_ele_description IS TABLE OF pay_element_types_f.description%TYPE
20 INDEX BY BINARY_INTEGER;
21
22 TYPE t_ele_pp IS TABLE OF pay_element_types_f.processing_priority%TYPE
23 INDEX BY BINARY_INTEGER;
24
25
26 TYPE t_eei_info IS TABLE OF pay_element_type_extra_info.eei_information19%
27 TYPE
28 INDEX BY BINARY_INTEGER;
29
30 TYPE r_udt_type IS RECORD
31 (user_table_name VARCHAR2(80)
32 ,range_or_match VARCHAR2(30)
33 ,user_key_units VARCHAR2(30)
34 ,user_row_title VARCHAR2(80)
35 );
36
37 TYPE r_udt_cols_type IS RECORD
38 (user_column_name pay_user_columns.user_column_name%TYPE
39 ,formula_id pay_user_columns.formula_id%TYPE
40 ,business_group_id pay_user_columns.business_group_id%TYPE
41 ,legislation_code pay_user_columns.legislation_code%TYPE
42 );
43
44 TYPE t_udt_cols IS TABLE OF r_udt_cols_type
45 INDEX BY BINARY_INTEGER;
46
47 TYPE r_udt_rows_type IS RECORD
48 (row_low_range_or_name pay_user_rows_f.row_low_range_or_name%TYPE
49 ,display_sequence pay_user_rows_f.display_sequence%TYPE
50 ,row_high_range pay_user_rows_f.row_high_range%TYPE
51 ,business_group_id pay_user_rows.business_group_id%TYPE
52 ,legislation_code pay_user_rows.legislation_code%TYPE
53 );
54
55 TYPE t_udt_rows IS TABLE OF r_udt_rows_type
56 INDEX BY BINARY_INTEGER;
57
58 TYPE t_number IS TABLE OF NUMBER
59 INDEX BY BINARY_INTEGER;
60
61
62
63 --
64 --
65 --
66 PROCEDURE debug(
67 p_trace_message IN VARCHAR2
68 ,p_trace_location IN NUMBER DEFAULT NULL
69 )
70 IS
71 BEGIN
72 pqp_utilities.debug(p_trace_message, p_trace_location);
73 END debug;
74 --
75 --
76 --
77 PROCEDURE debug(p_trace_number IN NUMBER)
78 IS
79 BEGIN
80 pqp_utilities.debug(p_trace_number);
81 END debug;
82
83 --
84 --
85 --
86 PROCEDURE debug(p_trace_date IN DATE)
87 IS
88 BEGIN
89 pqp_utilities.debug(p_trace_date);
90 END debug;
91
92 --
93 --
94 --
95 PROCEDURE debug_enter(
96 p_proc_name IN VARCHAR2
97 ,p_trace_on IN VARCHAR2 DEFAULT NULL
98 )
99 IS
100 BEGIN
101 pqp_utilities.debug_enter(p_proc_name, p_trace_on);
102 END debug_enter;
103
104 --
105 --
106 --
107 PROCEDURE debug_exit(
108 p_proc_name IN VARCHAR2
109 ,p_trace_off IN VARCHAR2 DEFAULT NULL
110 )
111 IS
112 BEGIN
113 pqp_utilities.debug_exit(p_proc_name, p_trace_off);
114 END debug_exit;
115
116 --
117 --
118 --
119 PROCEDURE debug_others(
120 p_proc_name IN VARCHAR2
121 ,p_proc_step IN NUMBER DEFAULT NULL
122 )
123 IS
124 BEGIN
125 pqp_utilities.debug_others(p_proc_name, p_proc_step);
126 END debug_others;
127 --
128 --
129 --
130 PROCEDURE check_error_code
131 (p_error_code IN NUMBER
132 ,p_error_message IN VARCHAR2
133 )
134 IS
135 BEGIN
136 pqp_utilities.check_error_code(p_error_code, p_error_message);
137 END;
138 --
139 --
140 --
141 PROCEDURE clear_cache
142 IS
143 BEGIN
144 NULL;
145 END;
146 --
147 --
148 --
149
150
151 --
152 --======================================================================
153 -- FUNCTION GET_TEMPLATE_ID
154 --======================================================================
155 FUNCTION get_template_id ( p_template_name IN VARCHAR2
156 ,p_legislation_code IN VARCHAR2 )
157 RETURN number IS
158 --
159 l_template_id pay_element_templates.template_id%TYPE ;
160 l_proc_step NUMBER(20,10);
161 l_proc_name VARCHAR2(72) := g_package_name || 'get_template_id';
162 --
163 CURSOR csr_get_temp_id is
164 SELECT template_id
165 FROM pay_element_templates
166 WHERE template_name = p_template_name
167 AND legislation_code = p_legislation_code
168 AND template_type = 'T'
169 AND business_group_id is NULL;
170 --
171 BEGIN
172
173 debug('Entering: '||l_proc_name, 10);
174
175 l_proc_step := 20;
176 IF g_debug THEN
177 debug(l_proc_name, l_proc_step);
178 END IF;
179
180 for csr_get_temp_id_rec in csr_get_temp_id
181 loop
182 l_template_id := csr_get_temp_id_rec.template_id;
183 end loop;
184
185 debug('Leaving: '||l_proc_name, 30);
186
187 RETURN l_template_id;
188
189 END get_template_id;
190
191 -----------------------------------------------------------------------------
192
193 --
194 --=======================================================================
195 -- FUNCTION GET_OBJECT_ID
196 --=======================================================================
197 FUNCTION get_object_id (p_object_type in varchar2
198 ,p_object_name in varchar2
199 ,p_business_group_id in number
200 ,p_template_id in number )
201 RETURN NUMBER is
202 --
203 l_object_id NUMBER := NULL;
204 l_proc_step NUMBER(20,10);
205 l_proc_name varchar2(72) := g_package_name || 'get_object_id';
206 --
207 CURSOR c2 (c_object_name varchar2) is
208 SELECT element_type_id
209 FROM pay_element_types_f
210 WHERE element_name = c_object_name
211 AND business_group_id = p_business_group_id;
212 --
213 CURSOR c3 (c_object_name in varchar2) is
214 SELECT ptco.core_object_id
215 FROM pay_shadow_balance_types psbt,
216 pay_template_core_objects ptco
217 WHERE psbt.template_id = p_template_id
218 AND psbt.balance_name = c_object_name
219 AND ptco.template_id = psbt.template_id
220 AND ptco.shadow_object_id = psbt.balance_type_id;
221 --
222 BEGIN
223 debug('Entering: '||l_proc_name, 10);
224 --
225 if p_object_type = 'ELE' then
226 for c2_rec in c2 (p_object_name) loop
227 l_object_id := c2_rec.element_type_id; -- element id
228 end loop;
229 elsif p_object_type = 'BAL' then
230 for c3_rec in c3 (p_object_name) loop
231 l_object_id := c3_rec.core_object_id; -- balance id
232 end loop;
233 end if;
234 --
235 debug('Leaving: '||l_proc_name, 20);
236 --
237 RETURN l_object_id;
238 --
239 END get_object_id;
240 --
241
242 --
243 --========================================================================
244 -- PROCEDURE Update Element Type with Retro Ele Info
245 --========================================================================
246 PROCEDURE update_ele_retro_info (p_main_ele_name in varchar2
247 ,p_retro_ele_name in varchar2
248 ,p_business_group_id in number
249 ,p_template_id in number
250 ) IS
251 --
252
253 l_main_ele_type_id pay_element_types_f.element_type_id%TYPE;
254 l_retro_ele_type_id pay_element_types_f.element_type_id%TYPE;
255 l_proc_step NUMBER(20,10);
256 l_proc_name VARCHAR2(72) := g_package_name ||
257 'update_ele_retro_info';
258
259 --
260 BEGIN
261
262 --
263 debug ('Entering '||l_proc_name, 10);
264 --
265
266 -- Get element type id for retro element
267 l_retro_ele_type_id := get_object_id (p_object_type => 'ELE'
268 ,p_object_name => p_retro_ele_name
269 ,p_business_group_id => p_business_group_id
270 ,p_template_id => p_template_id
271 );
272
273 l_proc_step := 20;
274 IF g_debug THEN
275 debug(l_proc_name, l_proc_step);
276 END IF;
277
278 -- Get element type id for main element
279 l_main_ele_type_id := get_object_id (p_object_type => 'ELE'
280 ,p_object_name => p_main_ele_name
281 ,p_business_group_id => p_business_group_id
282 ,p_template_id => p_template_id
283 );
284
285 -- Update main element with retro element info
286
287 l_proc_step := 30;
288 IF g_debug THEN
289 debug(l_proc_name, l_proc_step);
290 END IF;
291
292
293 UPDATE pay_element_types_f
294 SET retro_summ_ele_id = l_retro_ele_type_id
295 WHERE element_type_id = l_main_ele_type_id;
296
297 --
298 debug ('Leaving '||l_proc_name, 40);
299 --
300
301 END update_ele_retro_info;
302 --
303
304
305 -----------------------------------------------------------------------------
306 --- PROCEDURE update input value default value
307 -----------------------------------------------------------------------------
308 PROCEDURE update_ipval_defval(p_ele_name IN VARCHAR2
309 ,p_ip_name IN VARCHAR2
310 ,p_def_value IN VARCHAR2
311 ,p_bg_id IN NUMBER)
312 IS
313
314 CURSOR csr_getinput(c_ele_name varchar2
315 ,c_iv_name varchar2)
316 IS
317 SELECT input_value_id
318 ,piv.name
319 ,piv.element_type_id
320 FROM pay_input_values_f piv
321 ,pay_element_types_f pet
322 WHERE element_name = c_ele_name
323 AND piv.element_type_id = pet.element_type_id
324 AND (piv.business_group_id = p_bg_id OR piv.business_group_id IS NULL)
325 AND piv.name = c_iv_name
326 AND (piv.legislation_code = 'GB' OR piv.legislation_code IS NULL);
327
328 CURSOR csr_updinput(c_ip_id number
329 ,c_element_type_id number)
330 IS
331 SELECT rowid
332 FROM pay_input_values_f
333 WHERE input_value_id = c_ip_id
334 AND element_type_id = c_element_type_id
335 FOR UPDATE NOWAIT;
336
337 csr_getinput_rec csr_getinput%rowtype;
338 csr_updinput_rec csr_updinput%rowtype;
339
340
341 l_proc_step NUMBER(20,10);
342 l_proc_name VARCHAR2(72) := g_package_name ||
343 'update_ipval_defval';
344 --
345 BEGIN
346 --
347
348 --
349 debug ('Entering '||l_proc_name, 10);
350 --
351 OPEN csr_getinput(p_ele_name
352 ,p_ip_name);
353 LOOP
354
355 FETCH csr_getinput INTO csr_getinput_rec;
356 EXIT WHEN csr_getinput%NOTFOUND;
357
358 --
359 l_proc_step := 20;
360 IF g_debug THEN
361 debug(l_proc_name, l_proc_step);
362 END IF;
363
364 --
365
366 OPEN csr_updinput(csr_getinput_rec.input_value_id
367 ,csr_getinput_rec.element_type_id);
368 LOOP
369
370 FETCH csr_updinput INTO csr_updinput_rec;
371 EXIT WHEN csr_updinput%NOTFOUND;
372
373 --
374 l_proc_step := 30;
375 IF g_debug THEN
376 debug(l_proc_name, l_proc_step);
377 END IF;
378
379 --
380
381 UPDATE pay_input_values_f
382 SET default_value = p_def_value
383 WHERE rowid = csr_updinput_rec.rowid;
384
385 END LOOP;
386 CLOSE csr_updinput;
387
388 END LOOP;
389 CLOSE csr_getinput;
390
391 --
392 debug ('Leaving '||l_proc_name, 40);
393 --
394
395 END update_ipval_defval;
396 --
397 --
398 --======================================================================
399 -- FUNCTION get_udt_col_info
400 --======================================================================
401 PROCEDURE get_udt_col_info (p_lookup_type in varchar2
402 ,p_lookup_code in varchar2
403 ,p_formula_id in number
404 ,p_business_group_id in number
405 ,p_legislation_code in varchar2
406 ,p_udt_cols out nocopy t_udt_cols
407 )
408 IS
409 --
410
411 CURSOR csr_get_lookup_info is
412 SELECT meaning
413 FROM hr_lookups
414 WHERE lookup_type = p_lookup_type
415 AND lookup_code like p_lookup_code
416 AND enabled_flag = 'Y'
417 ORDER BY lookup_code;
418
419 l_proc_step NUMBER(20,10);
420 l_proc_name VARCHAR2(72) := g_package_name || 'get_udt_col_info';
421 l_udt_col_name pay_user_columns.user_column_name%TYPE;
422 l_udt_cols t_udt_cols;
423 i number;
424
425 --
426 BEGIN
427
428 --
429 debug ('Entering ' || l_proc_name, 10);
430 --
431
432 -- Get information from Lookup
433
434 i := 0;
435 OPEN csr_get_lookup_info;
436 LOOP
437
438 FETCH csr_get_lookup_info INTO l_udt_col_name;
439 EXIT WHEN csr_get_lookup_info%NOTFOUND;
440
441 i := i + 1;
442 l_udt_cols(i).user_column_name := l_udt_col_name;
443 l_udt_cols(i).formula_id := p_formula_id;
444 l_udt_cols(i).business_group_id := p_business_group_id;
445 l_udt_cols(i).legislation_code := p_legislation_code;
446
447 END LOOP;
448
449 p_udt_cols := l_udt_cols;
450
451 --
452 debug ('Leaving '||l_proc_name, 20);
453 -- Added by tmehra for nocopy changes Feb'03
454
455 EXCEPTION
456 WHEN OTHERS THEN
457 debug('Entering excep:'||l_proc_name, 35);
458 p_udt_cols.delete;
459 raise;
460 --
461
462 END get_udt_col_info;
463 --
464 --======================================================================
465 -- FUNCTION create_udt
466 --======================================================================
467 FUNCTION create_udt (p_udt_type r_udt_type
468 ,p_udt_cols t_udt_cols
469 ,p_udt_rows t_udt_rows
470 ,p_business_group_id number
471 ,p_effective_start_date date
472 ,p_effective_end_date date
473 )
474 RETURN NUMBER IS
475 --
476
477 CURSOR csr_get_next_udt_row_seq
478 IS
479 SELECT pay_user_rows_s.NEXTVAL
480 FROM dual;
481
482 l_proc_name VARCHAR2(72) := g_package_name || 'create_udt';
483 l_proc_step NUMBER(20,10);
484
485 l_user_table_id pay_user_tables.user_table_id%TYPE;
486 l_user_column_id pay_user_columns.user_column_id%TYPE;
487 l_user_row_id pay_user_rows_f.user_row_id%TYPE;
488 l_udt_rowid rowid ;
489 l_udt_cols_rowid rowid;
490 l_udt_rows_rowid rowid;
491 i number;
492
493 --
494 BEGIN
495
496 --
497 debug ('Entering '||l_proc_name, 10);
498 --
499
500 -- Create the UDT
501
502 l_proc_step := 20;
503 IF g_debug THEN
504 debug(l_proc_name, l_proc_step);
505 END IF;
506
507
508 pay_user_tables_pkg.insert_row
509 (p_rowid => l_udt_rowid
510 ,p_user_table_id => l_user_table_id
511 ,p_business_group_id => p_business_group_id
512 ,p_legislation_code => NULL
513 ,p_legislation_subgroup => NULL
514 ,p_range_or_match => p_udt_type.range_or_match
515 ,p_user_key_units => p_udt_type.user_key_units
516 ,p_user_table_name => p_udt_type.user_table_name
517 ,p_user_row_title => p_udt_type.user_row_title
518 );
519
520 IF p_udt_cols.count > 0 THEN
521
522 -- Create the columns
523 l_proc_step := 30;
524 IF g_debug THEN
525 debug(l_proc_name, l_proc_step);
526 END IF;
527
528
529 i := p_udt_cols.FIRST;
530
531 WHILE i IS NOT NULL
532 LOOP
533
534 pay_user_columns_pkg.insert_row
535 (p_rowid => l_udt_cols_rowid
536 ,p_user_column_id => l_user_column_id
537 ,p_user_table_id => l_user_table_id
538 ,p_business_group_id => p_udt_cols(i).business_group_id
539 ,p_legislation_code => p_udt_cols(i).legislation_code
540 ,p_legislation_subgroup => NULL
541 ,p_user_column_name => p_udt_cols(i).user_column_name
542 ,p_formula_id => p_udt_cols(i).formula_id
543 );
544
545 i := p_udt_cols.NEXT(i);
546 END LOOP;
547
548 END IF; -- End if of user cols > 1 check ...
549
550 IF p_udt_rows.count > 0 THEN
551
552 l_proc_step := 40;
553 IF g_debug THEN
554 debug(l_proc_name, l_proc_step);
555 END IF;
556
557 -- Create the rows
558
559 i := p_udt_rows.FIRST;
560
561 WHILE i IS NOT NULL
562 LOOP
563
564 OPEN csr_get_next_udt_row_seq;
565 FETCH csr_get_next_udt_row_seq INTO l_user_row_id;
566 CLOSE csr_get_next_udt_row_seq;
567
568 pay_user_rows_pkg.pre_insert
569 (p_rowid => l_udt_rows_rowid
570 ,p_user_table_id => l_user_table_id
571 ,p_row_low_range_or_name => p_udt_rows(i).row_low_range_or_name
572 ,p_user_row_id => l_user_row_id
573 ,p_business_group_id => p_business_group_id
574 );
575
576 INSERT INTO pay_user_rows_f
577 (user_row_id
578 ,effective_start_date
579 ,effective_end_date
580 ,business_group_id
581 ,legislation_code
582 ,user_table_id
583 ,row_low_range_or_name
584 ,display_sequence
585 ,legislation_subgroup
586 ,row_high_range
587 )
588 VALUES
589 (l_user_row_id
590 ,p_effective_start_date
591 ,nvl(p_effective_end_date, hr_api.g_eot)
592 ,p_udt_rows(i).business_group_id
593 ,p_udt_rows(i).legislation_code
594 ,l_user_table_id
595 ,p_udt_rows(i).row_low_range_or_name
596 ,p_udt_rows(i).display_sequence
597 ,NULL
598 ,p_udt_rows(i).row_high_range
599 );
600
601 i := p_udt_rows.NEXT(i);
602
603 END LOOP; -- End Loop for user rows...
604 END IF; -- End if of user rows if present check...
605
606 debug ('Leaving '||l_proc_name, 50);
607
608 RETURN l_user_table_id;
609
610 --
611 END create_udt;
612 --
613
614 --
615 --======================================================================
616 -- PROCEDURE create_lookup
617 --======================================================================
618 PROCEDURE create_lookup (p_lookup_type varchar2
619 ,p_lookup_meaning varchar2
620 ,p_lookup_values pqp_gb_osp_Template.t_abs_types
621 ,p_security_group_id in number
622 ,p_effective_start_date in date
623 ) IS
624 --
625
626 CURSOR csr_chk_uniq_type
627 IS
628 SELECT 'X'
629 FROM fnd_lookup_types_vl
630 WHERE lookup_type = p_lookup_type
631 AND security_group_id = p_security_group_id
632 AND view_application_id = 3;
633
634 CURSOR csr_chk_uniq_meaning
635 IS
636 SELECT 'X'
637 FROM fnd_lookup_types_vl
638 WHERE meaning = p_lookup_meaning
639 AND security_group_id = p_security_group_id
640 AND view_application_id = 3;
641
642 l_proc_step NUMBER(20,10);
643 l_proc_name VARCHAR2(72) := g_package_name || 'create_lookup';
644 l_exists VARCHAR2(1);
645 l_rowid fnd_lookup_types_vl.row_id%type;
646 l_user_id number := fnd_global.user_id;
647 l_login_id number := fnd_global.login_id;
648 i number ;
649
650 --
651 BEGIN
652 --
653 debug('Entering '||l_proc_name, 10);
654 --
655
656 -- Check unique lookup type
657 OPEN csr_chk_uniq_type;
658 FETCH csr_chk_uniq_type INTO l_exists;
659
660 IF csr_chk_uniq_type%FOUND THEN
661
662 -- Raise error
663 CLOSE csr_chk_uniq_type;
664 hr_utility.set_message(0, 'QC-DUPLICATE TYPE');
665 hr_utility.raise_error;
666
667 END IF; -- End if of unique lookup type check ...
668 CLOSE csr_chk_uniq_type;
669
670 l_proc_step := 20;
671 IF g_debug THEN
672 debug(l_proc_name, l_proc_step);
673 END IF;
674
675
676 -- Check unique lookup type meaning
677 OPEN csr_chk_uniq_meaning;
678 FETCH csr_chk_uniq_meaning INTO l_exists;
679
680 IF csr_chk_uniq_meaning%FOUND THEN
681
682 -- Raise error
683 CLOSE csr_chk_uniq_meaning;
684 hr_utility.set_message(0, 'QC-DUPLICATE TYPE MEANING');
685 hr_utility.raise_error;
686
687 END IF; -- End if of unique lookup type meaning check ...
688 CLOSE csr_chk_uniq_meaning;
689
690 -- Create Lookup type
691 l_proc_step := 30;
692 IF g_debug THEN
693 debug(l_proc_name, l_proc_step);
694 debug('p_lookup_type:'||p_lookup_type);
695 END IF;
696
697
698 fnd_lookup_types_pkg.insert_row
699 (
700 x_rowid => l_rowid
701 ,x_lookup_type => p_lookup_type
702 ,x_security_group_id => p_security_group_id
703 ,x_view_application_id => 3
704 ,x_application_id => 800
705 ,x_customization_level => 'U'
706 ,x_meaning => p_lookup_meaning
707 ,x_description => NULL
708 ,x_creation_date => SYSDATE
709 ,x_created_by => l_user_id
710 ,x_last_update_date => SYSDATE
711 ,x_last_updated_by => l_user_id
712 ,x_last_update_login => l_login_id
713 );
714
715 -- Create Lookup Values
716 -- The validation for lookup values should've been taken care in the
717 -- form
718 l_proc_step := 40;
719 IF g_debug THEN
720 debug(l_proc_name, l_proc_step);
721 END IF;
722
723 IF p_lookup_values.count > 0 THEN
724
725 i := p_lookup_values.FIRST;
726 WHILE i IS NOT NULL
727 LOOP
728 IF g_debug THEN
729 debug('abs_type_name:'||p_lookup_values(i).abs_type_name);
730 END IF;
731 fnd_lookup_values_pkg.insert_row
732 (
733 x_rowid => l_rowid
734 ,x_lookup_type => p_lookup_type
735 ,x_security_group_id => p_security_group_id
736 ,x_view_application_id => 3
737 ,x_lookup_code => fnd_number.number_to_canonical(
738 p_lookup_values(i).abs_type_id)
739 ,x_tag => NULL
740 ,x_attribute_category => NULL
741 ,x_attribute1 => NULL
742 ,x_attribute2 => NULL
743 ,x_attribute3 => NULL
744 ,x_attribute4 => NULL
745 ,x_attribute5 => NULL
746 ,x_attribute6 => NULL
747 ,x_attribute7 => NULL
748 ,x_attribute8 => NULL
749 ,x_attribute9 => NULL
750 ,x_attribute10 => NULL
751 ,x_attribute11 => NULL
752 ,x_attribute12 => NULL
753 ,x_attribute13 => NULL
754 ,x_attribute14 => NULL
755 ,x_attribute15 => NULL
756 ,x_enabled_flag => 'Y'
757 ,x_start_date_active => p_effective_start_date
758 ,x_end_date_active => NULL
759 ,x_territory_code => NULL
760 ,x_meaning => p_lookup_values(i).abs_type_name
761 ,x_description => NULL
762 ,x_creation_date => SYSDATE
763 ,x_created_by => l_user_id
764 ,x_last_update_date => SYSDATE
765 ,x_last_updated_by => l_user_id
766 ,x_last_update_login => l_login_id
767 );
768
769 i := p_lookup_values.NEXT(i);
770
771 END LOOP;
772
773 END IF; -- End if of p_lookup_values check ...
774
775 --
776 debug('Leaving '||l_proc_name, 60);
777 --
778 END create_lookup;
779 --
780 ---------------
781
782
783
784 /*========================================================================
785 * CREATE_USER_TEMPLATE
786 *=======================================================================*/
787 FUNCTION create_user_template
788 (p_plan_id in number
789 ,p_plan_description in varchar2
790 ,p_abs_days in varchar2
791 ,p_abs_ent_sick_leaves in number
792 ,p_abs_ent_holidays in number
793 ,p_abs_daily_rate_calc_method in varchar2
794 ,p_abs_daily_rate_calc_period in varchar2
795 ,p_abs_daily_rate_calc_divisor in number
796 ,p_abs_working_pattern in varchar2
797 ,p_abs_ele_name in varchar2
798 ,p_abs_ele_reporting_name in varchar2
799 ,p_abs_ele_description in varchar2
800 ,p_abs_ele_processing_priority in number
801 ,p_abs_primary_yn in varchar2
802 ,p_pay_ele_reporting_name in varchar2
803 ,p_pay_ele_description in varchar2
804 ,p_pay_ele_processing_priority in number
805 ,p_pay_src_pay_component in varchar2
806 ,p_ele_eff_start_date in date
807 ,p_ele_eff_end_date in date
808 ,p_abs_type_lookup_type in varchar2
809 ,p_abs_type_lookup_value in pqp_gb_osp_template.t_abs_types
810 ,p_security_group_id in number
811 ,p_bg_id in number
812 )
813 RETURN NUMBER IS
814 --
815
816
817 /*--------------------------------------------------------------------
818 The input values are explained below : V-varchar2, D-Date, N-number
819 Input-Name Type Valid Values/Explaination
820 ---------- ----
821 --------------------------------------
822 p_plan_id (N) - LOV based i/p
823 p_plan_description (V) - User i/p Description
824 p_sch_cal_type (V) - LOV based i/p (Fixed/Rolling)
825 p_sch_cal_duration (N) - LOV based i/p
826 p_sch_cal_uom (V) - LOV based i/p
827 (Days/Weeks/Months/Years)
828 p_sch_cal_start_date (D) - User i/p Date
829 p_sch_cal_end_date (D) - User i/p Date
830 p_abs_days (V) - Radio Button based i/p
831 (Working/Calendar/User Provided)
832 p_abs_ent_sick_leaves (N) - User i/p UDT Id
833 p_abs_ent_holidays (N) - User i/p UDT Id
834 p_abs_daily_rate_calc_method (V) - Radio Button based i/p
835 (Working/Calendar)
836 p_abs_daily_rate_calc_period (V) - LOV based i/p (Annual/Pay Period)
837 p_abs_daily_rate_calc_divisor (N) - 365/User Provided Default 365
838 p_abs_working_pattern (V) - User i/p Working Pattern Name
839 p_abs_overlap_rule (V) - User i/p Absence Overlap Rule
840 p_abs_ele_name (V) - User i/p Element Name
841 p_abs_ele_reporting_name (V) - User i/p Reporting Name
842 p_abs_ele_description (V) - User i/p Description
843 p_abs_ele_processing_priority (N) - User provided
844 p_abs_primary_yn (V) - 'Y'/'N'
845 p_pay_ele_reporting_name (V) - User i/p Reporting Name
846 p_pay_ele_description (V) - User i/p Description
847 p_pay_ele_processing_priority (N) - User provided
848 p_pay_src_pay_component (V) - LOV based i/p
849 p_bnd1_ele_sub_name (V) - User i/p Band1 Sub Name
850 p_bnd2_ele_sub_name (V) - User i/p Band2 Sub Name
851 p_bnd3_ele_sub_name (V) - User i/p Band3 Sub Name
852 p_bnd4_ele_sub_name (V) - User i/p Band4 Sub Name
853 p_ele_eff_start_date (D) - User i/p Effective Start Date
854 p_ele_eff_end_date (D) - User i/p Effective End Date
855 p_abs_type_lookup_type (V) - Absence Type Lookup Name
856 p_abs_type_lookup_value (C) - Collection of Absence Types
857 p_bg_id (N) - Business group id
858 ----------------------------------------------------------------------*/
859 --
860
861
862 l_template_id pay_shadow_element_types.template_id%TYPE;
863 l_base_element_type_id pay_template_core_objects.core_object_id%TYPE;
864 l_source_template_id pay_element_templates.template_id%TYPE;
865 l_object_version_number pay_element_types_f.object_version_number%TYPE;
866
867 l_proc_step NUMBER(20,10);
868 l_proc_name VARCHAR2(80) :=
869 g_package_name || 'create_user_template';
870 l_element_type_id pay_element_types_f.element_type_id%TYPE;
871 l_balance_type_id pay_balance_types.balance_type_id%TYPE ;
872 l_eei_element_type_id pay_element_types_f.element_type_id%TYPE;
873 l_ele_obj_ver_number pay_element_types_f.object_version_number%TYPE;
874 l_bal_obj_ver_number pay_element_types_f.object_version_number%TYPE;
875 i NUMBER;
876 l_eei_info_id pay_element_type_extra_info.
877 element_type_extra_info_id%TYPE ;
878 l_ovn_eei pay_element_types_f.object_version_number%TYPE;
879 l_abs_ele_correction_pp NUMBER := p_abs_ele_processing_priority - 50;
880 l_pay_ele_correction_pp NUMBER := p_pay_ele_processing_priority - 50;
881 l_formula_name pay_shadow_formulas.formula_name%TYPE;
882 l_formula_id ff_formulas_f.formula_id%TYPE ;
883 l_lookup_type fnd_lookup_types_vl.lookup_type%TYPE;
884 l_lookup_meaning fnd_lookup_types_vl.meaning%TYPE;
885 l_exists VARCHAR2(1);
886 l_display_sequence NUMBER;
887 l_base_name pay_element_templates.base_name%TYPE
888 := UPPER(TRANSLATE(TRIM(p_abs_ele_name),' ','_'));
889
890 l_exc_sec_days_bf VARCHAR2(1);
891
892 l_days_hours VARCHAR2(10) ;
893 l_template_name pay_element_templates.template_name%TYPE ;
894 l_configuration_information2 pay_element_templates.configuration_information2%TYPE;
895
896 l_ele_name t_ele_name;
897 l_ele_new_name t_ele_name;
898 l_main_ele_name t_ele_name;
899 l_retro_ele_name t_ele_name;
900
901 l_bal_name t_bal_name;
902 l_bal_new_name t_bal_name;
903
904
905 l_ele_reporting_name t_ele_reporting_name;
906
907 l_ele_description t_ele_description;
908
909 l_ele_pp t_ele_pp;
910
911 l_main_eei_info19 t_eei_info;
912 l_retro_eei_info19 t_eei_info;
913
914 l_udt_type r_udt_type;
915
916 l_udt_cols t_udt_cols;
917
918 l_udt_rows t_udt_rows;
919
920 l_ele_core_id pay_template_core_objects.core_object_id%TYPE:=
921 -1;
922
923 -- Extra Information variables
924 l_eei_information9 pay_element_type_extra_info.eei_information9%
925 TYPE;
926 l_eei_information10 pay_element_type_extra_info.eei_information10%
927 TYPE;
928 l_eei_information18 pay_element_type_extra_info.eei_information18%
929 TYPE;
930 l_eei_information30 pay_element_type_extra_info.eei_information30%
931 TYPE :='UNPAID';
932 l_eei_information29 pay_element_type_extra_info.eei_information29%
933 TYPE := 'OCCUPATIONAL';
934 l_eei_information28 pay_element_type_extra_info.eei_information28%
935 TYPE := 'PQP_GAP_ENTITLEMENT_BANDS';
936 l_eei_information27 pay_element_type_extra_info.eei_information27%
937 TYPE := 'PQP_GB_OSP_CALENDAR_RULES';
938
939 l_ctr BINARY_INTEGER:=0;
940 l_idx BINARY_INTEGER:=0;
941
942
943 --
944
945 CURSOR csr_get_ele_info (c_ele_name varchar2) is
946 SELECT element_type_id
947 ,object_version_number
948 FROM pay_shadow_element_types
949 WHERE template_id = l_template_id
950 AND element_name = c_ele_name;
951
952 CURSOR csr_get_bal_info (c_bal_name varchar2) is
953 SELECT balance_type_id
954 ,object_version_number
955 FROM pay_shadow_balance_types
956 WHERE template_id = l_template_id
957 AND balance_name = c_bal_name;
958
959 CURSOR csr_chk_primary_exists is
960 SELECT 'X'
961 FROM pay_element_type_extra_info
962 WHERE eei_information1 = fnd_number.number_to_canonical(p_plan_id)
963 AND eei_information16 = 'Y'
964 AND information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
965 AND rownum = 1;
966
967
968 BEGIN
969
970
971 g_debug := hr_utility.debug_enabled;
972
973 debug_enter(l_proc_name);
974
975 ---------------------
976 -- Set session date
977 ---------------------
978
979 pay_db_pay_setup.set_session_date(nvl(p_ele_eff_start_date, sysdate));
980 --
981
982
983 l_proc_step := 20;
984 IF g_debug THEN
985 debug(l_proc_name, l_proc_step);
986 END IF;
987
988 --
989
990 IF (hr_utility.chk_product_install('Oracle Payroll',g_template_leg_code))
991 THEN
992
993 l_exc_sec_days_bf := NULL;
994
995 OPEN csr_chk_primary_exists;
996 FETCH csr_chk_primary_exists INTO l_exists;
997
998 -- Check whether Primary Plan Exists when creating Secondary Plans
999 IF p_abs_primary_yn = 'N' THEN
1000
1001 l_proc_step := 25;
1002 IF g_debug THEN
1003 debug(l_proc_name, l_proc_step);
1004 END IF;
1005
1006
1007
1008 IF csr_chk_primary_exists%NOTFOUND THEN
1009
1010 -- Raise Error
1011 CLOSE csr_chk_primary_exists;
1012 hr_utility.set_message(8303, 'PQP_230608_OSP_PRIM_NOT_FOUND');
1013 hr_utility.raise_error;
1014
1015 END IF; -- End if of primary element check...
1016
1017 -- Exclude balance feeds to generic days balance for secondary elements
1018 l_exc_sec_days_bf := 'N';
1019
1020 -- Check whether Primary Elements exists for this plan
1021 -- when creating Primary Scheme
1022
1023 ELSIF p_abs_primary_yn = 'Y' THEN
1024
1025 IF csr_chk_primary_exists%FOUND THEN
1026
1027 -- Raise Error
1028 CLOSE csr_chk_primary_exists;
1029 hr_utility.set_message(8303, 'PQP_230666_OSP_PRIMARY_EXISTS');
1030 hr_utility.raise_error;
1031
1032 END IF; -- End if of primary element check...
1033
1034 END IF; -- End if of abs primary yes or no check...
1035 CLOSE csr_chk_primary_exists;
1036
1037
1038 ---------------------------
1039 -- Get Source Template ID
1040 ---------------------------
1041
1042 l_template_name := 'PQP UNPAID' ;
1043
1044 l_source_template_id := get_template_id
1045 (p_template_name => l_template_name
1046 ,p_legislation_code => g_template_leg_code
1047 );
1048
1049
1050
1051 /*--------------------------------------------------------------------------
1052 Create the user Structure
1053 The Configuration Flex segments for the Exclusion Rules are as follows:
1054 ---------------------------------------------------------------------------
1055 Config1 --
1056 Config2 --
1057 ---------------------------------------------------------------------------*/
1058
1059 l_proc_step := 40;
1060 IF g_debug THEN
1061 debug(l_proc_name, l_proc_step);
1062 END IF;
1063
1064
1065 --
1066 -- create user structure from the template
1067 --
1068
1069 pay_element_template_api.create_user_structure
1070 (p_validate => false
1071 ,p_effective_date => p_ele_eff_start_date
1072 ,p_business_group_id => p_bg_id
1073 ,p_source_template_id => l_source_template_id
1074 ,p_base_name => p_abs_ele_name
1075 ,p_configuration_information1 => l_exc_sec_days_bf
1076 ,p_template_id => l_template_id
1077 ,p_allow_base_name_reuse => true
1078 ,p_object_version_number => l_object_version_number
1079 );
1080 --
1081
1082 l_proc_step := 50;
1083 IF g_debug THEN
1084 debug(l_proc_name, l_proc_step);
1085 END IF;
1086
1087 ---------------------------------------------------------------------------
1088 ---------------------------- Update Shadow Structure ----------------------
1089 --
1090
1091
1092 l_ctr := l_ctr + 1;
1093
1094
1095 l_ele_name(l_ctr) := p_abs_ele_name || ' Unpaid Absence';
1096 l_ele_reporting_name(l_ctr) := p_abs_ele_reporting_name;
1097 l_ele_description(l_ctr) := p_abs_ele_description;
1098 l_ele_pp(l_ctr) := p_abs_ele_processing_priority;
1099
1100 l_ctr := l_ctr + 1;
1101
1102 l_ele_name(l_ctr) := p_abs_ele_name || ' Unpaid Pay';
1103 l_ele_reporting_name(l_ctr) := p_pay_ele_reporting_name;
1104 l_ele_description(l_ctr) := p_pay_ele_description;
1105 l_ele_pp(l_ctr) := p_pay_ele_processing_priority;
1106
1107
1108 l_idx := l_ele_name.FIRST;
1109 WHILE l_idx IS NOT NULL
1110 LOOP
1111
1112 OPEN csr_get_ele_info(l_ele_name(l_idx));
1113 LOOP
1114 FETCH csr_get_ele_info INTO l_element_type_id,l_ele_obj_ver_number;
1115 EXIT WHEN csr_get_ele_info%NOTFOUND;
1116 if i = 1 then
1117 l_base_element_type_id := l_element_type_id;
1118 end if;
1119
1120 pay_shadow_element_api.update_shadow_element
1121 (p_validate => false
1122 ,p_effective_date => p_ele_eff_start_date
1123 ,p_element_type_id => l_element_type_id
1124 ,p_element_name => l_ele_name(l_idx)
1125 ,p_reporting_name => l_ele_reporting_name(l_idx)
1126 ,p_description => l_ele_description(l_idx)
1127 ,p_relative_processing_priority => l_ele_pp(l_idx)
1128 ,p_object_version_number => l_ele_obj_ver_number
1129 );
1130
1131 END LOOP;
1132 CLOSE csr_get_ele_info;
1133
1134 l_idx := l_ele_name.NEXT(l_idx);
1135
1136 END LOOP; -- WHILE l_idx IS NOT NULL
1137
1138
1139 l_ctr := 0;
1140 l_ctr := l_ctr + 1; --1
1141
1142 l_ele_name(l_ctr) := p_abs_ele_name || ' Unpaid Absence Retro';
1143 l_ele_new_name(l_ctr) := l_ele_name(l_ctr);
1144 l_ele_pp(l_ctr) := l_abs_ele_correction_pp;
1145
1146 l_ctr := l_ctr + 1; --2
1147
1148 l_ele_name(l_ctr) := p_abs_ele_name || ' Unpaid Pay Retro';
1149 l_ele_new_name(l_ctr) := l_ele_name(l_ctr);
1150 l_ele_pp(l_ctr) := l_pay_ele_correction_pp;
1151
1152
1153 l_proc_step := 60;
1154 IF g_debug THEN
1155 debug(l_proc_name, l_proc_step);
1156 END IF;
1157
1158
1159 l_idx := l_ele_name.FIRST;
1160 WHILE l_idx IS NOT NULL
1161 LOOP
1162
1163 OPEN csr_get_ele_info(l_ele_name(l_idx));
1164 LOOP
1165 FETCH csr_get_ele_info INTO l_element_type_id,l_ele_obj_ver_number;
1166 EXIT WHEN csr_get_ele_info%NOTFOUND;
1167
1168 pay_shadow_element_api.update_shadow_element
1169 (p_validate => false
1170 ,p_effective_date => p_ele_eff_start_date
1171 ,p_element_type_id => l_element_type_id
1172 ,p_element_name => l_ele_new_name(l_idx)
1173 ,p_relative_processing_priority => l_ele_pp(l_idx)
1174 ,p_object_version_number => l_ele_obj_ver_number
1175 );
1176
1177 END LOOP;
1178 CLOSE csr_get_ele_info;
1179
1180 l_idx := l_ele_name.NEXT(l_idx);
1181
1182 END LOOP; --
1183
1184 -- Update shadow structure for Balances
1185
1186 l_proc_step := 70;
1187 IF g_debug THEN
1188 debug(l_proc_name, l_proc_step);
1189 END IF;
1190
1191
1192 -------------------------------------------------------------------------
1193 --
1194 --
1195 l_proc_step := 90;
1196 IF g_debug THEN
1197 debug(l_proc_name, l_proc_step);
1198 END IF;
1199
1200 ---------------------------------------------------------------------------
1201 ---------------------------- Generate Core Objects ------------------------
1202 ---------------------------------------------------------------------------
1203
1204 pay_element_template_api.generate_part1
1205 (p_validate => false
1206 ,p_effective_date => p_ele_eff_start_date
1207 ,p_hr_only => false
1208 ,p_hr_to_payroll => false
1209 ,p_template_id => l_template_id);
1210 --
1211 l_proc_step := 100;
1212 IF g_debug THEN
1213 debug(l_proc_name, l_proc_step);
1214 END IF;
1215
1216 --
1217 pay_element_template_api.generate_part2
1218 (p_validate => false
1219 ,p_effective_date => p_ele_eff_start_date
1220 ,p_template_id => l_template_id);
1221 --
1222
1223 -- Update Main Elements with the Correction Element Information
1224
1225 l_proc_step := 110;
1226 IF g_debug THEN
1227 debug(l_proc_name, l_proc_step);
1228 END IF;
1229
1230
1231 -- Absence (Create)--lctr
1232 -- Pay (Create)
1233 -- Absence Retro --l_idx.FIRST
1234 -- Pay Retro Retro
1235
1236 l_ctr := 0;
1237
1238 --1
1239 l_ctr := l_ctr + 1; --1 -- create manual entry as it does not exist in source array
1240 l_main_ele_name(l_ctr) := p_abs_ele_name || ' Unpaid Absence';
1241 l_main_eei_info19(l_ctr) := 'Absence Info';
1242
1243 --create main and retro entries at the same index
1244
1245 l_idx := l_ele_new_name.FIRST;
1246 l_retro_ele_name(l_ctr) := l_ele_new_name(l_idx); -- create from source array
1247 l_retro_eei_info19(l_ctr) := 'Absence Correction Info';
1248
1249 --2
1250 l_ctr := l_ctr + 1; -- increment l_ctr after each pair
1251
1252 --create manual entry as it does not exist in source array
1253 l_main_ele_name(l_ctr) := p_abs_ele_name || ' Unpaid Pay';
1254 l_main_eei_info19(l_ctr) := 'Pay Info';
1255
1256 l_idx := l_ele_new_name.NEXT(l_idx); -- next in source
1257 l_retro_ele_name(l_ctr) := l_ele_new_name(l_idx); -- copy from source
1258 l_retro_eei_info19(l_ctr) := 'Pay Correction Info';
1259
1260
1261 l_idx := l_main_ele_name.FIRST;
1262 WHILE l_idx IS NOT NULL
1263 LOOP
1264
1265 update_ele_retro_info
1266 (p_main_ele_name => l_main_ele_name(l_idx)
1267 ,p_retro_ele_name => l_retro_ele_name(l_idx)
1268 ,p_business_group_id => p_bg_id
1269 ,p_template_id => l_template_id
1270 );
1271
1272 l_idx := l_main_ele_name.NEXT(l_idx);
1273
1274 END LOOP; -- l_idx := l_main_ele_name.FIRST;
1275
1276 -- Update the pay component rate type input value for base element
1277
1278 IF p_pay_src_pay_component IS NOT NULL THEN
1279
1280 --
1281 l_proc_step := 120;
1282 IF g_debug THEN
1283 debug(l_proc_name, l_proc_step);
1284 END IF;
1285
1286 --
1287 update_ipval_defval (p_ele_name => l_main_ele_name(l_main_ele_name.FIRST)
1288 ,p_ip_name => 'Pay Component Rate Type'
1289 ,p_def_value => p_pay_src_pay_component
1290 ,p_bg_id => p_bg_id
1291 );
1292
1293 END IF; -- End of of pay src comp not null check ...
1294
1295 l_proc_step := 130;
1296 IF g_debug THEN
1297 debug(l_proc_name, l_proc_step);
1298 END IF;
1299
1300
1301 l_base_element_type_id :=
1302 get_object_id (
1303 p_object_type => 'ELE'
1304 ,p_object_name => l_main_ele_name(l_main_ele_name.FIRST)
1305 ,p_business_group_id => p_bg_id
1306 ,p_template_id => l_template_id
1307 );
1308
1309 l_proc_step := 140;
1310 IF g_debug THEN
1311 debug(l_proc_name, l_proc_step);
1312 END IF;
1313
1314 l_eei_information9 := fnd_number.number_to_canonical
1315 (p_abs_ent_sick_leaves);
1316
1317 l_proc_step := 150;
1318 IF g_debug THEN
1319 debug(l_proc_name, l_proc_step);
1320 END IF;
1321
1322
1323 l_eei_information10 := NULL;
1324 IF NVL(p_abs_ent_holidays, 0) <> -1 THEN
1325
1326 IF p_abs_ent_holidays IS NOT NULL THEN
1327
1328 -- Store the user_table_id for this udt name
1329 l_eei_information10 := fnd_number.number_to_canonical
1330 (p_abs_ent_holidays);
1331
1332 ELSE -- create the udt
1333
1334 -- Create UDT for Calendar
1335
1336 l_udt_type.user_table_name := l_base_name ||'_CALENDAR';
1337 l_udt_type.range_or_match := 'M'; -- Match
1338 l_udt_type.user_key_units := 'T';
1339 l_udt_type.user_row_title := NULL;
1340
1341 -- columns
1342
1343 l_udt_cols.DELETE;
1344
1345 -- Get the column names from the Lookup Type 'PQP_GB_OSP_CALENDAR_RULES'
1346
1347 l_proc_step := 155;
1348 IF g_debug THEN
1349 debug(l_proc_name, l_proc_step);
1350 END IF;
1351
1352
1353 get_udt_col_info (p_lookup_type => 'PQP_GB_OSP_CALENDAR_RULES'
1354 ,p_lookup_code => '%'
1355 ,p_formula_id => NULL
1356 ,p_business_group_id => NULL
1357 ,p_legislation_code => 'GB'
1358 ,p_udt_cols => l_udt_cols
1359 );
1360
1361 l_udt_rows.DELETE;
1362
1363
1364 l_eei_information10 :=
1365 fnd_number.number_to_canonical(
1366 create_udt (
1367 p_udt_type => l_udt_type
1368 ,p_udt_cols => l_udt_cols
1369 ,p_udt_rows => l_udt_rows
1370 ,p_business_group_id => p_bg_id
1371 ,p_effective_start_date => p_ele_eff_start_date
1372 ,p_effective_end_date => p_ele_eff_end_date
1373 ) );
1374
1375
1376 END IF; -- End if of p_abs_ent_holidays null check ...
1377
1378 END IF; -- End if of ent holidays <> -1 check...
1379
1380 --
1381 l_proc_step := 160;
1382 IF g_debug THEN
1383 debug(l_proc_name, l_proc_step);
1384 END IF;
1385
1386 --
1387 l_eei_information18 := p_abs_type_lookup_type;
1388
1389 IF p_abs_type_lookup_type IS NULL THEN
1390
1391 -- Create Lookup dynamically
1392 l_lookup_type := l_base_name || '_LIST';
1393 l_lookup_meaning := l_base_name || '_ABSENCE_ATTENDANCE_TYPES';
1394 create_lookup (p_lookup_type => l_lookup_type
1395 ,p_lookup_meaning => l_lookup_meaning
1396 ,p_lookup_values => p_abs_type_lookup_value
1397 ,p_security_group_id => p_security_group_id
1398 ,p_effective_start_date => p_ele_eff_start_date
1399 );
1400 l_eei_information18 := l_lookup_type;
1401
1402 -- Create GAP lookup dynamically
1403 l_lookup_type := 'PQP_GAP_ABSENCE_TYPES_LIST';
1404 l_lookup_meaning := l_lookup_type;
1405 pqp_gb_osp_template.create_gap_lookup (
1406 p_security_group_id => p_security_group_id
1407 ,p_ele_eff_start_date => p_ele_eff_start_date
1408 ,p_lookup_type => l_lookup_type
1409 ,p_lookup_meaning => l_lookup_meaning
1410 ,p_lookup_values => p_abs_type_lookup_value
1411 );
1412
1413 END IF; -- End if of abs type lookup type not null ...
1414
1415
1416
1417 l_idx := l_main_ele_name.FIRST;
1418 WHILE l_idx IS NOT NULL
1419 LOOP
1420
1421 l_proc_step := 170;
1422
1423 IF g_debug THEN
1424 debug(l_proc_name, l_proc_step);
1425 debug('ELE:'||l_main_ele_name(l_idx));
1426 END IF;
1427
1428 l_eei_element_type_id :=
1429 get_object_id (
1430 p_object_type => 'ELE'
1431 ,p_object_name => l_main_ele_name(l_idx)
1432 ,p_business_group_id => p_bg_id
1433 ,p_template_id => l_template_id
1434 );
1435
1436 -- Create a row in pay_element_extra_info with all the element information
1437 pay_element_extra_info_api.create_element_extra_info
1438 (p_element_type_id => l_eei_element_type_id
1439 ,p_information_type => 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
1440 ,P_EEI_INFORMATION_CATEGORY => 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
1441 ,p_eei_information1 => fnd_number.number_to_canonical(p_plan_id)
1442 ,p_eei_information2 => p_plan_description
1443 ,p_eei_information8 => p_abs_days
1444 ,p_eei_information9 => l_eei_information9
1445 ,p_eei_information10 => l_eei_information10
1446 ,p_eei_information11 => p_abs_daily_rate_calc_method
1447 ,p_eei_information12 => p_abs_daily_rate_calc_period
1448 ,p_eei_information13 => p_abs_daily_rate_calc_divisor
1449 ,p_eei_information15 => p_pay_src_pay_component
1450 ,p_eei_information16 => p_abs_primary_yn
1451 ,p_eei_information17 => p_abs_working_pattern
1452 ,p_eei_information18 => l_eei_information18
1453 ,p_eei_information19 => l_main_eei_info19(l_idx)
1454 ,p_eei_information27 => l_eei_information27
1455 ,p_eei_information28 => l_eei_information28
1456 ,p_eei_information29 => l_eei_information29
1457 ,p_eei_information30 => l_eei_information30
1458 ,p_element_type_extra_info_id => l_eei_info_id
1459 ,p_object_version_number => l_ovn_eei
1460 );
1461
1462
1463 IF l_retro_ele_name.EXISTS(l_idx) THEN
1464 l_eei_element_type_id :=
1465 get_object_id (
1466 p_object_type => 'ELE'
1467 ,p_object_name => l_retro_ele_name(l_idx)
1468 ,p_business_group_id => p_bg_id
1469 ,p_template_id => l_template_id
1470 );
1471
1472 l_proc_step := 180;
1473 IF g_debug THEN
1474 debug(l_proc_name, l_proc_step);
1475 END IF;
1476
1477
1478 -- Create a row in pay_element_extra_info with all the element information
1479 pay_element_extra_info_api.create_element_extra_info
1480 (p_element_type_id => l_eei_element_type_id
1481 ,p_information_type => 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
1482 ,P_EEI_INFORMATION_CATEGORY => 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
1483 ,p_eei_information1 => fnd_number.number_to_canonical(p_plan_id)
1484 ,p_eei_information2 => p_plan_description
1485 ,p_eei_information8 => p_abs_days
1486 ,p_eei_information9 => l_eei_information9
1487 ,p_eei_information10 => l_eei_information10
1488 ,p_eei_information11 => p_abs_daily_rate_calc_method
1489 ,p_eei_information12 => p_abs_daily_rate_calc_period
1490 ,p_eei_information13 => p_abs_daily_rate_calc_divisor
1491 ,p_eei_information15 => p_pay_src_pay_component
1492 ,p_eei_information16 => p_abs_primary_yn
1493 ,p_eei_information17 => p_abs_working_pattern
1494 ,p_eei_information18 => l_eei_information18
1495 ,p_eei_information19 => l_retro_eei_info19(l_idx)
1496 ,p_eei_information27 => l_eei_information27
1497 ,p_eei_information28 => l_eei_information28
1498 ,p_eei_information29 => l_eei_information29
1499 ,p_eei_information30 => l_eei_information30
1500 ,p_element_type_extra_info_id => l_eei_info_id
1501 ,p_object_version_number => l_ovn_eei
1502 );
1503
1504 END IF; -- if retro exists -- min pay testing only
1505
1506 l_idx := l_main_ele_name.NEXT(l_idx);
1507
1508
1509 END LOOP; --l_idx := l_main_ele_name.FIRST;
1510
1511 pqp_gb_omp_template.create_element_links
1512 (p_business_group_id => p_bg_id
1513 ,p_effective_start_date => p_ele_eff_start_date
1514 ,p_effective_end_date => p_ele_eff_end_date
1515 ,p_template_id => l_template_id
1516 ) ;
1517
1518 --------
1519 IF p_abs_primary_yn = 'Y' THEN
1520 pqp_gb_osp_template.automate_plan_setup
1521 (p_pl_id => p_plan_id
1522 ,p_business_group_id => p_bg_id
1523 ,p_element_type_id => l_base_element_type_id
1524 ,p_effective_date => p_ele_eff_start_date
1525 ,p_base_name => l_base_name
1526 ,p_plan_class => 'UNP'
1527 );
1528 END IF;
1529
1530 ELSE
1531
1532 hr_utility.set_message(8303, 'PQP_230535_GBORAPAY_NOT_FOUND');
1533 hr_utility.raise_error;
1534
1535
1536 END IF; -- IF chk_product_install('Oracle Payroll',g_template_leg_code))
1537
1538 debug_exit(l_proc_name);
1539
1540 RETURN l_base_element_type_id;
1541
1542 EXCEPTION
1543 WHEN OTHERS THEN
1544 clear_cache;
1545 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
1546 debug_others(l_proc_name,l_proc_step);
1547 IF g_debug THEN
1548 debug('Leaving: '||l_proc_name,-999);
1549 END IF;
1550 fnd_message.raise_error;
1551 ELSE
1552 RAISE;
1553 END IF;
1554 END create_user_template;
1555 --
1556 --
1557
1558
1559
1560
1561
1562 --
1563 --========================================================================
1564 -- PROCEDURE get_other_lookups
1565 --========================================================================
1566
1567 PROCEDURE get_other_lookups (p_business_group_id in number
1568 ,p_lookup_collection out nocopy t_number
1569 ,p_template_name IN VARCHAR2
1570 ,p_security_group_id IN NUMBER
1571 )
1572 IS
1573
1574 -- The original query is split into 2 queries
1575 -- to avoid Merge joins and make use of Indexes.
1576 -- There is no effective date check on table pay_element_types_f
1577 -- as we are interested in data irrespective of date.
1578 -- Cursor to retrieve lookup type information
1579
1580 CURSOR csr_get_lookup_type(c_base_name in varchar2)
1581 IS
1582 SELECT DISTINCT(pete.eei_information18) lookup_type
1583 FROM pay_element_type_extra_info pete
1584 ,pay_element_types_f petf
1585 -- ,pay_element_templates pet
1586 WHERE pete.element_type_id = petf.element_type_id
1587 AND pete.information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
1588 AND pete.eei_information16 = 'Y'
1589 AND petf.element_name = c_base_name || ' Unpaid Absence'
1590 AND petf.business_group_id = p_business_group_id
1591 ;
1592
1593 CURSOR csr_template_names IS
1594 SELECT pet.base_name
1595 FROM pay_element_templates pet
1596 WHERE pet.template_name = p_template_name
1597 AND pet.template_type = 'U'
1598 AND pet.business_group_id = p_business_group_id ;
1599
1600 CURSOR csr_get_lookup_code (c_lookup_type varchar2)
1601 IS
1602 SELECT lookup_code
1603 FROM fnd_lookup_values_vl
1604 WHERE lookup_type = c_lookup_type
1605 AND security_group_id = p_security_group_id
1606 AND view_application_id = 3;
1607
1608
1609 l_lookup_collection t_number;
1610 l_number NUMBER;
1611 l_lookup_code fnd_lookup_values_vl.lookup_code%TYPE;
1612 l_lookup_type fnd_lookup_types_vl.lookup_type%TYPE;
1613 l_proc_step NUMBER(20,10);
1614 l_proc_name VARCHAR2(72) := g_package_name || 'get_other_lookups';
1615 l_base_name pay_element_templates.base_name%TYPE ;
1616
1617 --
1618 BEGIN
1619
1620 --
1621 debug('Entering '||l_proc_name, 10);
1622
1623 -- get the template base names
1624 OPEN csr_template_names ;
1625 LOOP
1626 FETCH csr_template_names INTO l_base_name ;
1627 EXIT WHEN csr_template_names%NOTFOUND ;
1628
1629 -- Get the lookup type information
1630
1631 OPEN csr_get_lookup_type(c_base_name => l_base_name);
1632 LOOP
1633
1634 FETCH csr_get_lookup_type INTO l_lookup_type;
1635 EXIT WHEN csr_get_lookup_type%NOTFOUND;
1636
1637 -- Get the lookup code for this lookup type
1638
1639 l_proc_step := 20;
1640 IF g_debug THEN
1641 debug(l_proc_name, l_proc_step);
1642 END IF;
1643
1644
1645 OPEN csr_get_lookup_code(l_lookup_type);
1646 LOOP
1647
1648 FETCH csr_get_lookup_code INTO l_lookup_code;
1649 EXIT WHEN csr_get_lookup_code%NOTFOUND;
1650
1651 -- Check whether this lookup code is already added to
1652 -- the collection
1653
1654 l_number := fnd_number.canonical_to_number(l_lookup_code);
1655
1656 IF NOT l_lookup_collection.EXISTS(l_number) THEN
1657
1658 l_lookup_collection(l_number) := l_number;
1659
1660 END IF; -- End if of lookup collection exists check ...
1661
1662 END LOOP;
1663 CLOSE csr_get_lookup_code;
1664
1665 END LOOP;
1666 CLOSE csr_get_lookup_type;
1667 END LOOP ;
1668 CLOSE csr_template_names;
1669
1670 p_lookup_collection := l_lookup_collection;
1671
1672 debug('Leaving '||l_proc_name, 30);
1673
1674 EXCEPTION
1675 WHEN OTHERS THEN
1676 debug('Entering excep:'||l_proc_name, 35);
1677 p_lookup_collection.delete;
1678 raise;
1679
1680 --
1681 END get_other_lookups;
1682 --
1683
1684
1685 --
1686 --========================================================================
1687 -- PROCEDURE delete_lookup
1688 --========================================================================
1689
1690 PROCEDURE delete_lookup (p_lookup_type in varchar2
1691 ,p_security_group_id in number
1692 ,p_view_application_id in number
1693 ,p_lookup_collection in t_number)
1694 IS
1695
1696 --
1697
1698 CURSOR csr_get_lkt_info
1699 IS
1700 SELECT 'X'
1701 FROM fnd_lookup_types_vl
1702 WHERE lookup_type = p_lookup_type
1703 AND security_group_id = p_security_group_id
1704 AND view_application_id = p_view_application_id;
1705
1706 CURSOR csr_get_lkv_info
1707 IS
1708 SELECT lookup_code
1709 FROM fnd_lookup_values_vl
1710 WHERE lookup_type = p_lookup_type
1711 AND security_group_id = p_security_group_id
1712 AND view_application_id = p_view_application_id;
1713
1714 l_proc_step NUMBER(20,10);
1715 l_proc_name VARCHAR2(72) := g_package_name || 'delete_lookup';
1716 l_exists VARCHAR2(1);
1717 l_lookup_code fnd_lookup_values_vl.lookup_code%TYPE;
1718
1719 BEGIN
1720 --
1721 debug ('Entering '||l_proc_name, 10);
1722 debug('Security Group' || to_char(p_security_group_id),15);
1723 debug('Lookup Type' || p_lookup_type, 16);
1724
1725 OPEN csr_get_lkt_info;
1726 FETCH csr_get_lkt_info into l_exists;
1727
1728 IF csr_get_lkt_info%FOUND THEN
1729
1730 -- Get Lookup Value Info
1731 l_proc_step := 20;
1732 IF g_debug THEN
1733 debug(l_proc_name, l_proc_step);
1734 END IF;
1735
1736
1737 OPEN csr_get_lkv_info;
1738 LOOP
1739 FETCH csr_get_lkv_info INTO l_lookup_code;
1740 EXIT WHEN csr_get_lkv_info%NOTFOUND;
1741
1742 -- Check whether this lookup code has to be deleted
1743 -- from PQP_GAP_ABSENCE_TYPES_LIST lookup type
1744
1745 l_proc_step := 25;
1746 IF g_debug THEN
1747 debug(l_proc_name, l_proc_step);
1748 END IF;
1749
1750
1751 IF NOT p_lookup_collection.EXISTS(fnd_number.canonical_to_number(
1752 l_lookup_code)) THEN
1753 fnd_lookup_values_pkg.delete_row
1754 (x_lookup_type => 'PQP_GAP_ABSENCE_TYPES_LIST'
1755 ,x_security_group_id => p_security_group_id
1756 ,x_view_application_id => p_view_application_id
1757 ,x_lookup_code => l_lookup_code
1758 );
1759
1760 END IF; -- End if of absence type exists in this collection check...
1761
1762 -- Delete the lookup code
1763
1764 l_proc_step := 30;
1765 IF g_debug THEN
1766 debug(l_proc_name, l_proc_step);
1767 END IF;
1768
1769
1770
1771 fnd_lookup_values_pkg.delete_row
1772 (x_lookup_type => p_lookup_type
1773 ,x_security_group_id => p_security_group_id
1774 ,x_view_application_id => p_view_application_id
1775 ,x_lookup_code => l_lookup_code
1776 );
1777 END LOOP;
1778 CLOSE csr_get_lkv_info;
1779
1780 -- Delete the lookup type
1781 l_proc_step := 40;
1782 IF g_debug THEN
1783 debug(l_proc_name, l_proc_step);
1784 END IF;
1785
1786
1787 fnd_lookup_types_pkg.delete_row
1788 (x_lookup_type => p_lookup_type
1789 ,x_security_group_id => p_security_group_id
1790 ,x_view_application_id => p_view_application_id
1791 );
1792
1793 END IF; -- End if of row found check ...
1794 CLOSE csr_get_lkt_info;
1795
1796 --
1797 debug('Leaving '||l_proc_name, 50);
1798 --
1799
1800 END delete_lookup;
1801 --
1802
1803 --
1804 --========================================================================
1805 -- PROCEDURE delete_udt
1806 --========================================================================
1807
1808 PROCEDURE delete_udt (p_udt_id in number
1809 ,p_business_group_id in number)
1810 IS
1811
1812 --
1813
1814 CURSOR csr_get_usr_table_id
1815 IS
1816 SELECT rowid
1817 FROM pay_user_tables
1818 WHERE user_table_id = p_udt_id
1819 AND business_group_id = p_business_group_id;
1820
1821 CURSOR csr_get_usr_col_id
1822 IS
1823 SELECT user_column_id
1824 FROM pay_user_columns
1825 WHERE user_table_id = p_udt_id;
1826
1827 CURSOR csr_get_usr_row_id
1828 IS
1829 SELECT user_row_id
1830 FROM pay_user_rows_f
1831 WHERE user_table_id = p_udt_id;
1832
1833 --
1834 l_proc_step NUMBER(20,10);
1835 l_proc_name VARCHAR(72) := g_package_name || 'delete_udt';
1836 l_rowid rowid;
1837 l_usr_row_id pay_user_rows.user_row_id%TYPE;
1838 l_usr_col_id pay_user_columns.user_column_id%TYPE;
1839 --
1840 --
1841 BEGIN
1842
1843 --
1844 debug ('Entering '||l_proc_name, 10);
1845 --
1846
1847 -- Get user_table_id from pay_user_tables
1848 OPEN csr_get_usr_table_id;
1849 FETCH csr_get_usr_table_id INTO l_rowid;
1850
1851 IF csr_get_usr_table_id%FOUND THEN
1852
1853 -- Get user_column_id from pay_user_columns
1854 l_proc_step := 20;
1855 IF g_debug THEN
1856 debug(l_proc_name, l_proc_step);
1857 END IF;
1858
1859
1860 OPEN csr_get_usr_col_id;
1861 LOOP
1862 FETCH csr_get_usr_col_id INTO l_usr_col_id;
1863 EXIT WHEN csr_get_usr_col_id%NOTFOUND;
1864
1865 -- Delete pay_user_column_instances_f for this column_id
1866 l_proc_step := 30;
1867 IF g_debug THEN
1868 debug(l_proc_name, l_proc_step);
1869 END IF;
1870
1871
1872 DELETE pay_user_column_instances_f
1873 WHERE user_column_id = l_usr_col_id;
1874
1875 END LOOP;
1876 CLOSE csr_get_usr_col_id;
1877
1878 -- Delete pay_user_columns for this table_id
1879 l_proc_step := 40;
1880 IF g_debug THEN
1881 debug(l_proc_name, l_proc_step);
1882 END IF;
1883
1884
1885 DELETE pay_user_columns
1886 WHERE user_table_id = p_udt_id;
1887
1888 OPEN csr_get_usr_row_id;
1889 LOOP
1890 FETCH csr_get_usr_row_id INTO l_usr_row_id;
1891 EXIT WHEN csr_get_usr_row_id%NOTFOUND;
1892
1893 -- Delete pay_user_rows_f for this table id
1894 l_proc_step := 50;
1895 IF g_debug THEN
1896 debug(l_proc_name, l_proc_step);
1897 END IF;
1898
1899
1900 pay_user_rows_pkg.check_delete_row
1901 (p_user_row_id => l_usr_row_id
1902 ,p_validation_start_date => NULL
1903 ,p_dt_delete_mode => 'ZAP'
1904 );
1905
1906 DELETE pay_user_rows_f
1907 WHERE user_row_id = l_usr_row_id;
1908
1909 END LOOP;
1910 CLOSE csr_get_usr_row_id;
1911
1912
1913 -- Delete pay_user_tables for this table id
1914 l_proc_step := 60;
1915 IF g_debug THEN
1916 debug(l_proc_name, l_proc_step);
1917 END IF;
1918
1919 pay_user_tables_pkg.delete_row
1920 (p_rowid => l_rowid
1921 ,p_user_table_id => p_udt_id
1922 );
1923
1924
1925 END IF; -- End of of user_table found check ...
1926 CLOSE csr_get_usr_table_id;
1927
1928 --
1929 debug ('Leaving '||l_proc_name, 70);
1930 --
1931 --
1932 END delete_udt;
1933
1934
1935
1936 --==========================================================================
1937 -- Deletion procedure
1938 --==========================================================================
1939 --
1940 PROCEDURE delete_user_template
1941 (p_plan_id in number
1942 ,p_business_group_id in number
1943 ,p_abs_ele_name in varchar2
1944 ,p_abs_ele_type_id in number
1945 ,p_abs_primary_yn in varchar2
1946 ,p_security_group_id in number
1947 ,p_effective_date in date
1948 ) IS
1949 --
1950 l_template_id NUMBER(9);
1951 l_proc_step NUMBER(20,10);
1952 l_proc_name varchar2(72) := g_package_name || 'delete_user_template';
1953 l_eei_info_id number;
1954 l_ovn_eei number;
1955 l_entudt_id pay_user_tables.user_table_id%TYPE;
1956 l_caludt_id pay_user_tables.user_table_id%TYPE;
1957 l_lookup_type fnd_lookup_types_vl.lookup_type%TYPE;
1958 l_lookup_code fnd_lookup_values_vl.lookup_code%TYPE;
1959 l_exists VARCHAR2(1);
1960 l_element_type_id pay_element_types_f.element_type_id%TYPE;
1961
1962 l_lookup_collection t_number;
1963
1964
1965 -- Added For Hours
1966
1967 l_entitlements_uom VARCHAR2(1) ;
1968 l_daily_rate_uom pay_element_type_extra_info.eei_information13%TYPE ;
1969 l_days_hours VARCHAR2(10) ;
1970 l_template_name pay_element_templates.template_name%TYPE ;
1971
1972
1973 CURSOR csr_get_scheme_type(p_ele_type_id IN NUMBER) IS
1974 SELECT pee.eei_information8 entitlements_uom
1975 ,pee.eei_information11 daily_rate_uom
1976 FROM pay_element_type_extra_info pee
1977 WHERE element_type_id = p_ele_type_id
1978 AND information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO' ;
1979
1980 -- Added For Hours
1981
1982
1983 CURSOR csr_get_ele_type_id (c_template_id number)
1984 IS
1985 SELECT element_type_id
1986 FROM pay_template_core_objects pet
1987 ,pay_element_types_f petf
1988 WHERE pet.template_id = c_template_id
1989 AND petf.element_type_id = pet.core_object_id
1990 AND pet.core_object_type = 'ET';
1991
1992 CURSOR csr_get_eei_info (c_element_type_id number)
1993 IS
1994 SELECT element_type_extra_info_id
1995 ,fnd_number.canonical_to_number(eei_information9) entitlement_udt
1996 ,fnd_number.canonical_to_number(eei_information10) calendar_udt
1997 ,eei_information18 lookup_type
1998 FROM pay_element_type_extra_info petei
1999 WHERE element_type_id = c_element_type_id ;
2000
2001 CURSOR csr_chk_eei_for_entudt (c_udt_id number)
2002 IS
2003 SELECT 'X'
2004 FROM pay_element_type_extra_info
2005 WHERE eei_information1 <> fnd_number.number_to_canonical(p_plan_id)
2006 AND eei_information9 = fnd_number.number_to_canonical(c_udt_id)
2007 AND information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
2008 AND rownum = 1;
2009
2010 CURSOR csr_chk_eei_for_caludt (c_udt_id number)
2011 IS
2012 SELECT 'X'
2013 FROM pay_element_type_extra_info
2014 WHERE eei_information1 <> fnd_number.number_to_canonical(p_plan_id)
2015 AND eei_information10 = fnd_number.number_to_canonical(c_udt_id)
2016 AND information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
2017 AND rownum = 1;
2018
2019 CURSOR csr_chk_eei_for_lkt (c_lookup_type varchar2)
2020 IS
2021 SELECT 'X'
2022 FROM pay_element_type_extra_info
2023 WHERE eei_information1 <> fnd_number.number_to_canonical(p_plan_id)
2024 AND eei_information18 = c_lookup_type
2025 AND information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
2026 AND rownum = 1;
2027
2028
2029 CURSOR csr_chk_sec_ele (c_te_usrstr_id NUMBER
2030 ,p_template_name VARCHAR2
2031 ) IS
2032 SELECT 'X'
2033 FROM pay_element_templates pets
2034 ,pay_shadow_element_types pset
2035 ,pay_template_core_objects ptco
2036 ,pay_element_type_extra_info peei
2037 WHERE pets.template_id <> c_te_usrstr_id
2038 -- For the given user structure
2039 AND pets.template_name = p_template_name -- 'PQP OSP'
2040 AND pets.template_type = 'U'
2041 AND pets.business_group_id = p_business_group_id
2042 AND pset.template_id = pets.template_id -- find the base element
2043 AND pset.element_name = pets.base_name || ' Unpaid Absence'
2044 AND ptco.template_id = pset.template_id -- For the base element
2045 AND ptco.shadow_object_id = pset.element_type_id -- find the core element
2046 AND ptco.core_object_type = 'ET'
2047 AND ptco.core_object_id = peei.element_type_id -- For the core element
2048 AND peei.eei_information1 = fnd_number.number_to_canonical(p_plan_id)
2049 AND peei.information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO';
2050 -- find the eei info
2051
2052 CURSOR csr_get_template_id (p_template_name IN VARCHAR2) is
2053 SELECT template_id
2054 FROM pay_element_templates
2055 WHERE base_name = p_abs_ele_name
2056 AND template_name = p_template_name --'PQP OSP'
2057 AND business_group_id = p_business_group_id
2058 AND template_type = 'U';
2059
2060 -- Cursor to check whether elements are attached to
2061 -- benefit standard rates
2062
2063 CURSOR csr_chk_ele_in_ben (c_element_type_id number)
2064 IS
2065 SELECT 'X'
2066 FROM ben_acty_base_rt_f
2067 WHERE pl_id = p_plan_id
2068 AND element_type_id = c_element_type_id
2069 AND business_group_id = p_business_group_id;
2070
2071
2072 -- Cursor to retrieve lookup code for a given
2073 -- lookup type
2074
2075 CURSOR csr_get_lookup_code (c_lookup_type varchar2)
2076 IS
2077 SELECT lookup_code
2078 FROM fnd_lookup_values_vl
2079 WHERE lookup_type = c_lookup_type
2080 AND security_group_id = p_security_group_id
2081 AND view_application_id = 3;
2082
2083 --
2084
2085 --
2086 BEGIN -- delete_user_template
2087
2088 -- for Multi Messages
2089 hr_multi_message.enable_message_list;
2090
2091 --
2092 g_debug := hr_utility.debug_enabled;
2093 IF g_debug THEN
2094 debug_enter(l_proc_name);
2095 END IF;
2096 --
2097
2098 FOR csr_get_scheme_type_rec IN csr_get_scheme_type
2099 (
2100 p_ele_type_id => p_abs_ele_type_id
2101 )
2102 LOOP
2103 l_entitlements_uom := csr_get_scheme_type_rec.entitlements_uom ;
2104 l_daily_rate_uom := csr_get_scheme_type_rec.daily_rate_uom ;
2105 END LOOP ;
2106
2107 l_template_name := 'PQP UNPAID' ;
2108
2109 FOR csr_get_template_id_rec IN csr_get_template_id
2110 (
2111 p_template_name => l_template_name
2112 )
2113 LOOP
2114 l_template_id := csr_get_template_id_rec.template_id;
2115 END LOOP;
2116
2117 l_proc_step := 20;
2118 IF g_debug THEN
2119 debug(l_proc_name, l_proc_step);
2120 END IF;
2121
2122
2123 -- Check whether this is primary element
2124
2125 IF p_abs_primary_yn = 'Y' THEN
2126
2127 -- Check whether there are any secondary elements
2128 l_proc_step := 40;
2129 IF g_debug THEN
2130 debug(l_proc_name, l_proc_step);
2131 END IF;
2132
2133
2134 OPEN csr_chk_sec_ele (l_template_id
2135 ,l_template_name);
2136
2137 FETCH csr_chk_sec_ele INTO l_exists;
2138
2139 IF csr_chk_sec_ele%FOUND THEN
2140
2141 -- Raise error
2142 CLOSE csr_chk_sec_ele;
2143 hr_utility.set_message (8303,'PQP_230607_OSP_SEC_ELE_EXISTS');
2144 hr_utility.raise_error;
2145
2146 END IF; -- End if of sec element check ...
2147 CLOSE csr_chk_sec_ele;
2148
2149 END IF; -- End if of abs primary yn check ...
2150
2151 --Delete data created by auto plan setup
2152
2153 IF p_abs_primary_yn = 'Y'
2154 THEN
2155 pqp_gb_osp_template.del_automated_plan_setup_data
2156 (p_pl_id => p_plan_id
2157 ,p_business_group_id => p_business_group_id
2158 ,p_effective_date => p_effective_date
2159 ,p_base_name => p_abs_ele_name
2160 );
2161 END IF;
2162 --
2163
2164
2165 -- Get Element type Id's from template core object
2166
2167 OPEN csr_get_ele_type_id (l_template_id);
2168 LOOP
2169
2170 FETCH csr_get_ele_type_id INTO l_element_type_id;
2171 EXIT WHEN csr_get_ele_type_id%NOTFOUND;
2172
2173 -- Check whether elements are attached to benefits
2174 -- standard rate formula before deleting them
2175
2176 l_proc_step := 25;
2177 IF g_debug THEN
2178 debug(l_proc_name, l_proc_step);
2179 END IF;
2180
2181
2182 OPEN csr_chk_ele_in_ben (l_element_type_id);
2183 FETCH csr_chk_ele_in_ben INTO l_exists;
2184
2185 IF csr_chk_ele_in_ben%FOUND THEN
2186
2187 -- Raise Error
2188 Close csr_chk_ele_in_ben;
2189 hr_utility.set_message (800,'PER_74880_CHILD_RECORD');
2190 hr_utility.set_message_token('TYPE','Standard Rates, Table: BEN_ACTY_BASE_RT_F');
2191 hr_utility.raise_error;
2192
2193 END IF; -- End if of element in ben check ...
2194 CLOSE csr_chk_ele_in_ben;
2195
2196 -- Get Element extra info id for this element type id
2197
2198 OPEN csr_get_eei_info (l_element_type_id);
2199 FETCH csr_get_eei_info INTO l_eei_info_id
2200 ,l_entudt_id
2201 ,l_caludt_id
2202 ,l_lookup_type;
2203 IF csr_get_eei_info%FOUND -- if an EIT exists only then delete else ignore
2204 THEN
2205
2206 -- Delete the EEI row
2207 l_proc_step := 50;
2208 IF g_debug THEN
2209 debug(l_proc_name, l_proc_step);
2210 debug('l_element_type_id:'||l_element_type_id);
2211 debug('l_eei_info_id:'||l_eei_info_id);
2212 END IF;
2213
2214
2215
2216 pay_element_extra_info_api.delete_element_extra_info
2217 (p_validate => FALSE
2218 ,p_element_type_extra_info_id => l_eei_info_id
2219 ,p_object_version_number => l_ovn_eei);
2220 END IF;
2221 CLOSE csr_get_eei_info;
2222
2223 END LOOP;
2224 CLOSE csr_get_ele_type_id;
2225
2226 IF l_caludt_id IS NOT NULL AND
2227 p_abs_primary_yn = 'Y'
2228 THEN
2229
2230 OPEN csr_chk_eei_for_caludt (l_caludt_id);
2231 FETCH csr_chk_eei_for_caludt INTO l_exists;
2232
2233 IF csr_chk_eei_for_caludt%NOTFOUND THEN
2234
2235 -- Delete UDT
2236
2237 l_proc_step := 70;
2238 IF g_debug THEN
2239 debug(l_proc_name, l_proc_step);
2240 END IF;
2241
2242
2243 delete_udt (p_udt_id => l_caludt_id
2244 ,p_business_group_id => p_business_group_id);
2245
2246 END IF; -- End if of eei row found check...
2247 CLOSE csr_chk_eei_for_caludt;
2248
2249 END IF; -- End if of cal udt name not null check ...
2250
2251
2252 -- Delete Lookup Type
2253
2254 IF l_lookup_type IS NOT NULL AND
2255 p_abs_primary_yn = 'Y'
2256 THEN
2257
2258 OPEN csr_chk_eei_for_lkt (l_lookup_type);
2259 FETCH csr_chk_eei_for_lkt INTO l_exists;
2260
2261 IF csr_chk_eei_for_lkt%NOTFOUND THEN
2262
2263 -- Get Other Lookup Information
2264
2265 l_proc_step := 75;
2266 IF g_debug THEN
2267 debug(l_proc_name, l_proc_step);
2268 END IF;
2269
2270
2271 get_other_lookups (p_business_group_id => p_business_group_id
2272 ,p_lookup_collection => l_lookup_collection
2273 ,p_template_name => l_template_name
2274 ,p_security_group_id => p_security_group_id
2275 );
2276
2277 -- Delete Lookup Type
2278
2279 l_proc_step := 80;
2280 IF g_debug THEN
2281 debug(l_proc_name, l_proc_step);
2282 END IF;
2283
2284
2285 delete_lookup (p_lookup_type => l_lookup_type
2286 ,p_security_group_id => p_security_group_id
2287 ,p_view_application_id => 3
2288 ,p_lookup_collection => l_lookup_collection
2289 );
2290
2291 -- Check whether PQP_GAP_ABSENCE_TYPES_LIST lookup type
2292 -- has atleast one lookup code
2293
2294 OPEN csr_get_lookup_code('PQP_GAP_ABSENCE_TYPES_LIST');
2295 FETCH csr_get_lookup_code INTO l_lookup_code;
2296
2297 IF csr_get_lookup_code%FOUND THEN
2298
2299 -- Delete this lookup type
2300 l_proc_step := 85;
2301 IF g_debug THEN
2302 debug(l_proc_name, l_proc_step);
2303 END IF;
2304
2305
2306 fnd_lookup_types_pkg.delete_row
2307 (x_lookup_type => 'PQP_GAP_ABSENCE_TYPES_LIST'
2308 ,x_security_group_id => p_security_group_id
2309 ,x_view_application_id => 3
2310 );
2311
2312 END IF; -- End if of lookup code check ...
2313 CLOSE csr_get_lookup_code;
2314
2315 END IF; -- End if of eei row found check...
2316 CLOSE csr_chk_eei_for_lkt;
2317
2318 END IF; -- End of of udt name not null check ...
2319
2320 l_proc_step := 90;
2321 IF g_debug THEN
2322 debug(l_proc_name, l_proc_step);
2323 END IF;
2324
2325 ---- Delete Links
2326 pqp_gb_omp_template.delete_element_links
2327 (p_business_group_id => p_business_group_id
2328 ,p_effective_start_date => p_effective_date
2329 ,p_effective_end_date => p_effective_date
2330 ,p_template_id => l_template_id
2331 ) ;
2332
2333
2334 pay_element_template_api.delete_user_structure
2335 (p_validate => false
2336 ,p_drop_formula_packages => true
2337 ,p_template_id => l_template_id);
2338 --
2339
2340 IF g_debug THEN
2341 debug_exit(l_proc_name);
2342 END IF;
2343
2344 --
2345 EXCEPTION
2346 WHEN hr_multi_message.error_message_exist THEN
2347
2348 --
2349 -- Catch the Multiple Message List exception which
2350 -- indicates API processing has been aborted because
2351 -- at least one message exists in the list.
2352 --
2353 debug ( ' Leaving:'
2354 || l_proc_name, 40);
2355 WHEN OTHERS
2356 THEN
2357
2358 --
2359 -- When Multiple Message Detection is enabled catch
2360 -- any Application specific or other unexpected
2361 -- exceptions. Adding appropriate details to the
2362 -- Multiple Message List. Otherwise re-raise the
2363 -- error.
2364 --
2365 IF hr_multi_message.unexpected_error_add (l_proc_name)
2366 THEN
2367 debug ( ' Leaving:'
2368 || l_proc_name, 50);
2369 RAISE;
2370 END IF;
2371
2372 END delete_user_template;
2373 --
2374
2375 END pqp_gb_unpaid_template;