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