[Home] [Help]
PACKAGE BODY: APPS.PQP_GB_OSP_TEMPLATE
Source
1 PACKAGE BODY pqp_gb_osp_template AS
2 /* $Header: pqpgbosd.pkb 120.0 2005/05/29 02:00:18 appldev noship $ */
3
4 g_package_name VARCHAR2(61) := 'pqp_gb_osp_template.';
5 g_debug BOOLEAN;
6
7 --
8 --
9 --
10 PROCEDURE debug(
11 p_trace_message IN VARCHAR2
12 ,p_trace_location IN NUMBER DEFAULT NULL
13 )
14 IS
15 BEGIN
16 pqp_utilities.debug(p_trace_message, p_trace_location);
17 END debug;
18 --
19 --
20 --
21 PROCEDURE debug(p_trace_number IN NUMBER)
22 IS
23 BEGIN
24 pqp_utilities.debug(p_trace_number);
25 END debug;
26
27 --
28 --
29 --
30 PROCEDURE debug(p_trace_date IN DATE)
31 IS
32 BEGIN
33 pqp_utilities.debug(p_trace_date);
34 END debug;
35
36 --
37 --
38 --
39 PROCEDURE debug_enter(
40 p_proc_name IN VARCHAR2
41 ,p_trace_on IN VARCHAR2 DEFAULT NULL
42 )
43 IS
44 BEGIN
45 pqp_utilities.debug_enter(p_proc_name, p_trace_on);
46 END debug_enter;
47
48 --
49 --
50 --
51 PROCEDURE debug_exit(
52 p_proc_name IN VARCHAR2
53 ,p_trace_off IN VARCHAR2 DEFAULT NULL
54 )
55 IS
56 BEGIN
57 pqp_utilities.debug_exit(p_proc_name, p_trace_off);
58 END debug_exit;
59
60 --
61 --
62 --
63 PROCEDURE debug_others(
64 p_proc_name IN VARCHAR2
65 ,p_proc_step IN NUMBER DEFAULT NULL
66 )
67 IS
68 BEGIN
69 pqp_utilities.debug_others(p_proc_name, p_proc_step);
70 END debug_others;
71 --
72 --
73 --
74 PROCEDURE check_error_code
75 (p_error_code IN NUMBER
76 ,p_error_message IN VARCHAR2
77 )
78 IS
79 BEGIN
80 pqp_utilities.check_error_code(p_error_code, p_error_message);
81 END;
82 --
83 --
84 --
85 PROCEDURE clear_cache
86 IS
87 BEGIN
88 NULL;
89 END;
90 --
91 --
92 --
93
94 PROCEDURE create_udt_entry
95 (p_bg_id IN NUMBER
96 ,p_band IN VARCHAR2
97 ,p_entit IN VARCHAR2
98 ,p_lower IN VARCHAR2
99 ,p_user_tbl_id IN NUMBER
100 )
101 IS
102
103 BEGIN
104 INSERT INTO pay_user_column_instances_f
105 (user_column_instance_id
106 ,effective_start_date
107 ,effective_end_date
108 ,user_row_id
109 ,user_column_id
110 ,business_group_id
111 ,legislation_code
112 ,legislation_subgroup
113 ,value
114 )
115 SELECT
116 pay_user_column_instances_s.NEXTVAL
117 ,urws.effective_start_date
118 ,urws.effective_end_date
119 ,urws.user_row_id
120 ,ucol.user_column_id
121 ,p_bg_id
122 ,NULL
123 ,NULL
124 ,p_entit -- -999999 -999999 Band1
125 FROM pay_user_columns ucol
126 ,pay_user_rows_f urws
127 WHERE ucol.user_table_id = p_user_tbl_id --fnd_number.canonical_to_number(l_eei_information9)
128 AND ucol.user_column_name = p_band -- bad bad refer 'PQP_GAP_ENTITLEMENT_BANDS'.'BAND1'
129 AND urws.user_table_id = ucol.user_table_id
130 AND urws.row_low_range_or_name = p_lower -- bad bad refer lookup code 'PQP_GAP_ENTITLEMENT_ROWS'.'GB_GAP_PERCENTAGE_ROW'
131 AND SYSDATE BETWEEN urws.effective_start_date
132 AND urws.effective_end_date;
133
134 END;
135
136
137
138 --
139 --
140 --
141
142 PROCEDURE del_automated_plan_setup_data
143 (p_pl_id IN NUMBER
144 ,p_business_group_id IN NUMBER
145 ,p_effective_date IN DATE
146 ,p_base_name IN VARCHAR2
147 )
148 IS
149
150 l_acty_base_rt_id ben_acty_base_rt_f.acty_base_rt_id%TYPE;
151 l_proc_step NUMBER(20,10);
152 l_proc_name VARCHAR2(61):=
153 g_package_name||'del_automated_plan_setup_data';
154
155 BEGIN
156
157 l_proc_step := 10;
158 IF g_debug THEN
159 debug(l_proc_name,l_proc_step);
160 END IF;
161
162 -- 3. delete the ben_benfts_grp, unless its linked to a person or elig profile
163
164 -- DELETE FROM ben_benfts_grp
165 -- WHERE business_group_id = p_business_group_id
166 -- AND name = p_base_name||'OSP Scheme Member';
167 --
168 -- can't validate easily, impact of delete unknown, hence not deleting
169 -- instead insert has a check to see that it doesn't try an insert duplicates
170 -- if the same base is used again
171 --
172
173
174 -- 2b) delete the inserted extra inputs
175
176 l_proc_step := 20;
177 IF g_debug THEN
178 debug(l_proc_name,l_proc_step);
179 END IF;
180
181 SELECT acty_base_rt_id
182 INTO l_acty_base_rt_id
183 FROM ben_acty_base_rt_f
184 WHERE pl_id = p_pl_id
185 AND p_effective_date BETWEEN effective_start_date
186 AND effective_end_date;
187 -- the above select will fail if there is more than one standard rate
188 -- thus if the user has manually setup additional rates then
189 -- they must first manually delink them
190 -- leaving only the base standard rate created by plan copy in place
191
192
193 l_proc_step := 25;
194 IF g_debug THEN
195 debug(l_proc_name,l_proc_step);
196 END IF;
197
198
199 -- delete all extra inputs for this standard rate
200 DELETE FROM ben_extra_input_values
201 WHERE acty_base_rt_id = l_acty_base_rt_id;
202
203
204 -- 2a) remove the link of the base element from the standard rate
205 l_proc_step := 30;
206 IF g_debug THEN
207 debug(l_proc_name,l_proc_step);
208 END IF;
209
210 UPDATE ben_acty_base_rt_f
211 SET element_type_id = NULL
212 ,input_value_id = NULL
213 ,ele_rqd_flag = 'N'
214 ,object_version_number = object_version_number + 1
215 WHERE acty_base_rt_id = l_acty_base_rt_id
216 AND p_effective_date BETWEEN effective_start_date
217 AND effective_end_date;
218
219
220 l_proc_step := 40;
221 IF g_debug THEN
222 debug(l_proc_name,l_proc_step);
223 END IF;
224
225
226 -- 1. making plans pending -- unusable untill it is resetup as a scheme
227
228 -- call api here....making do with hard update to get over date track problems
229 UPDATE ben_pl_f
230 SET pl_stat_cd = 'P'
231 ,object_version_number = object_version_number + 1
232 WHERE pl_id = p_pl_id
233 AND p_effective_date BETWEEN effective_start_date
234 AND effective_end_date;
235
236 debug_exit(l_proc_name);
237
238 EXCEPTION
239 WHEN OTHERS THEN
240 clear_cache;
241 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
242 debug_others(l_proc_name,l_proc_step);
243 IF g_debug THEN
244 debug('Leaving: '||l_proc_name,-999);
245 END IF;
246 fnd_message.raise_error;
247 ELSE
248 RAISE;
249 END IF;
250 END del_automated_plan_setup_data;
251
252
253 PROCEDURE automate_plan_setup
254 (p_pl_id IN NUMBER
255 ,p_business_group_id IN NUMBER
256 ,p_element_type_id IN NUMBER --
257 ,p_effective_date IN DATE
258 ,p_base_name IN VARCHAR2
259 ,p_plan_class IN VARCHAR2 DEFAULT 'OSP'
260 )
261 IS
262
263 l_acty_base_rt_id ben_acty_base_rt_f.acty_base_rt_id%TYPE;
264 l_proc_step NUMBER(20,10);
265 l_proc_name VARCHAR2(61):=
266 g_package_name||'automate_plan_setup';
267 l_input_name VARCHAR2(40);
268
269 BEGIN
270
271 g_debug := hr_utility.debug_enabled;
272 debug_enter(l_proc_name);
273
274 -- what to automate ?
275 -- 1. making plans active
276 -- 2. setting up standard rates
277 -- 3. setup a benefits group for quick eligibity setup
278 -- 4. linking life events to person changes -- once only
279 -- what is not automated
280 -- is the delete half of this
281 --
282
283 -- 1. making plans active
284
285 l_proc_step := 10;
286 IF g_debug THEN
287 debug(l_proc_name,l_proc_step);
288 END IF;
289
290 -- call api here....making do with hard update to get over date track problems
291 UPDATE ben_pl_f
292 SET pl_stat_cd = 'A'
293 ,object_version_number = object_version_number + 1
294 WHERE pl_id = p_pl_id
295 AND p_effective_date BETWEEN effective_start_date
296 AND effective_end_date;
297
298 -- 2. setting up standard rates a) update standard rate
299
300 l_proc_step := 20;
301 IF g_debug THEN
302 debug(l_proc_name,l_proc_step);
303 END IF;
304
305
306 SELECT acty_base_rt_id
307 INTO l_acty_base_rt_id
308 FROM ben_acty_base_rt_f
309 WHERE pl_id = p_pl_id
310 AND p_effective_date BETWEEN effective_start_date
311 AND effective_end_date;
312
313
314 l_proc_step := 25;
315 IF g_debug THEN
316 debug(l_proc_name,l_proc_step);
317 END IF;
318
319 --Set Standard Input Value as Absence Days
320 --as LOS dosent make any sense for UNP
321 IF p_plan_class='UNP' THEN
322 l_input_name := 'ABSENCE DAYS' ;
323 ELSE
324 l_input_name := 'LENGTH OF SERVICE' ;
325 END IF;
326
327 UPDATE ben_acty_base_rt_f
328 SET element_type_id = p_element_type_id
329 ,input_value_id = (SELECT input_value_id
330 FROM pay_input_values_f
331 WHERE element_type_id = p_element_type_id
332 AND UPPER(name) = l_input_name
333 AND p_effective_date BETWEEN effective_start_date
334 AND effective_end_date)
335 ,ele_rqd_flag = 'Y'
336 ,object_version_number = object_version_number + 1
337 WHERE acty_base_rt_id = l_acty_base_rt_id
338 AND p_effective_date BETWEEN effective_start_date
339 AND effective_end_date;
340
341
342 l_proc_step := 30;
343 IF g_debug THEN
344 debug(l_proc_name,l_proc_step);
345 END IF;
346
347 -- 2. setting up standard rates b) insert extra inputs
348
349 IF p_plan_class='OSP' OR p_plan_class='UNP' THEN
350 INSERT INTO ben_extra_input_values
351 (extra_input_value_id -- NOT NULL NUMBER(15)
352 ,acty_base_rt_id -- NOT NULL NUMBER(15)
353 ,input_value_id -- NOT NULL NUMBER(15)
354 ,input_text -- VARCHAR2(240)
355 ,upd_when_ele_ended_cd -- VARCHAR2(30)
356 ,return_var_name -- NOT NULL VARCHAR2(240)
357 ,business_group_id -- NOT NULL NUMBER(15)
358 ,object_version_number
359 )
360 (SELECT
361 ben_extra_input_values_s.NEXTVAL
362 ,l_acty_base_rt_id
363 ,input_value_id
364 ,NULL
365 ,DECODE(UPPER(name),'ABSENCE END DATE','U','C')
366 ,'L_'||TRANSLATE(UPPER(name),' ','_')
367 ,business_group_id
368 ,1
369 FROM pay_input_values_f
370 WHERE element_type_id = p_element_type_id
371 AND p_effective_date BETWEEN effective_start_date AND effective_end_date
372 AND UPPER(name) in ('ABSENCE TYPE','ABSENCE END DATE','ABSENCE START DATE','PLAN ID','ABSENCE ID')
373 );
374 ELSE
375 INSERT INTO ben_extra_input_values
376 (extra_input_value_id -- NOT NULL NUMBER(15)
377 ,acty_base_rt_id -- NOT NULL NUMBER(15)
378 ,input_value_id -- NOT NULL NUMBER(15)
379 ,input_text -- VARCHAR2(240)
380 ,upd_when_ele_ended_cd -- VARCHAR2(30)
381 ,return_var_name -- NOT NULL VARCHAR2(240)
382 ,business_group_id -- NOT NULL NUMBER(15)
383 ,object_version_number
384 )
385 (SELECT
386 ben_extra_input_values_s.NEXTVAL
387 ,l_acty_base_rt_id
388 ,input_value_id
389 ,NULL
390 ,DECODE(UPPER(name),'MATERNITY END DATE','U','C')
391 ,'L_'||TRANSLATE(UPPER(name),' ','_')
392 ,business_group_id
393 ,1
394 FROM pay_input_values_f
395 WHERE element_type_id = p_element_type_id
396 AND p_effective_date BETWEEN effective_start_date AND effective_end_date
397 AND UPPER(name) in ('ABSENCE TYPE','MATERNITY END DATE','MATERNITY START DATE','PLAN ID','ABSENCE ID','EWC')
398 );
399
400 END IF;
401
402 l_proc_step := 40;
403 IF g_debug THEN
404 debug(l_proc_name,l_proc_step);
405 END IF;
406
407 -- 3. setup a benefits group for quick eligibity setup
408
409 --INSERT INTO ben_benfts_grp
410 -- (benfts_grp_id -- not null number(15)
411 -- ,name -- not null varchar2(240)
412 -- ,business_group_id -- not null number(15)
413 -- ,bng_desc -- varchar2(240)
414 -- ,object_version_number -- number
415 -- )
416 --SELECT
417 -- ben_benfts_grp_s.NEXTVAL
418 -- ,p_base_name||'OSP Scheme Member'
419 -- ,p_business_group_id
420 -- ,p_base_name||'OSP Scheme Members Group'
421 -- ,1
422 --FROM DUAL
423 --WHERE NOT EXISTS
424 -- (SELECT 1
425 -- FROM ben_benfts_grp
426 -- WHERE business_group_id = p_business_group_id
427 -- AND name = p_base_name||'OSP Scheme Member'
428 -- );
429
430 -- 4. linking life events to person changes -- once only
431
432 l_proc_step := 50;
433 IF g_debug THEN
434 debug(l_proc_name,l_proc_step);
435 END IF;
436
437 INSERT INTO ben_ler_per_info_cs_ler_f
438 (ler_per_info_cs_ler_id -- NOT NULL NUMBER(15)
439 ,effective_start_date -- NOT NULL DATE
440 ,effective_end_date -- NOT NULL DATE
441 ,business_group_id -- NOT NULL NUMBER(15)
442 ,ler_id -- NOT NULL NUMBER(15)
443 ,per_info_chg_cs_ler_id -- NOT NULL NUMBER(15)
444 ,object_version_number -- NUMBER(9)
445 )
446 SELECT ben_ler_per_info_cs_ler_f_s.NEXTVAL
447 ,p_effective_date
448 ,ler.effective_end_date
449 ,p_business_group_id
450 ,ler.ler_id
451 ,pcd.per_info_chg_cs_ler_id
452 ,1
453 FROM ben_ler_f ler
454 ,ben_per_info_chg_cs_ler_f pcd
455 WHERE ler.business_group_id = p_business_group_id
456 AND ler.typ_cd = 'ABS'
457 AND pcd.name = ler.name
458 AND pcd.business_group_id = ler.business_group_id
459 AND p_effective_date BETWEEN ler.effective_start_date
460 AND ler.effective_end_date
461 AND p_effective_date BETWEEN pcd.effective_start_date
462 AND pcd.effective_end_date
463 AND NOT EXISTS -- do not insert if there a person change allready for this ler
464 (SELECT 1
465 FROM ben_ler_per_info_cs_ler_f pchg
466 WHERE pchg.ler_id = ler.ler_id -- no need to check effectiveness
467 );
468
469 debug_exit(l_proc_name);
470
471 EXCEPTION
472 WHEN OTHERS THEN
473 clear_cache;
474 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
475 debug_others(l_proc_name,l_proc_step);
476 IF g_debug THEN
477 debug('Leaving: '||l_proc_name,-999);
478 END IF;
479 fnd_message.raise_error;
480 ELSE
481 RAISE;
482 END IF;
483 END automate_plan_setup;
484
485
486 --======================================================================
487 -- PROCEDURE create_config_data
488 --======================================================================
489 PROCEDURE create_config_data IS
490
491 l_module_id pqp_configuration_modules.module_id%TYPE ;
492 l_proc_step NUMBER(20,10);
493 l_proc_name VARCHAR2(61):=
494 g_package_name||'create_config_data';
495
496 BEGIN
497
498 debug_enter(l_proc_name);
499
500
501 INSERT INTO PQP_CONFIGURATION_MODULES
502 ( MODULE_ID -- pqp_configuration_modules_s.nextval
503 ,MODULE_NAME -- 'Absence Schemes'
504 ,BUSINESS_GROUP_ID -- NULL
505 ,LEGISLATION_CODE -- 'GB'
506 ,DESCRIPTION -- 'GB Absence Schemes OSP/OMP'
507 ,APPLICATION_ID -- 8303
508 ,OBJECT_VERSION_NUMBER -- 1
509 ,LAST_UPDATE_DATE -- hr_api.g_sot -- DO NOT USE SYSDATE
510 ,LAST_UPDATED_BY -- 2
511 ,LAST_UPDATE_LOGIN -- 2
512 ,CREATED_BY -- 2
513 ,CREATION_DATE -- hr_api.g_sot -- DO NOT USE SYSDATE
514 )
515 SELECT
516 pqp_configuration_modules_s.nextval
517 ,'Absence Schemes'
518 ,NULL
519 ,'GB'
520 ,'GB Absence Schemes OSP/OMP'
521 ,8303
522 ,1
523 ,hr_api.g_sot
524 ,2
525 ,2
526 ,2
527 ,hr_api.g_sot
528 FROM DUAL
529 WHERE NOT EXISTS
530 ( SELECT 1 FROM pqp_configuration_modules
531 WHERE module_name = 'Absence Schemes'
532 and legislation_code = 'GB'
533 );
534
535
536 SELECT module_id
537 INTO l_module_id
538 FROM pqp_configuration_modules
539 where module_name = 'Absence Schemes'
540 and legislation_code = 'GB' ;
541
542
543 INSERT INTO PQP_CONFIGURATION_TYPES
544 ( CONFIGURATION_TYPE -- 'PQP_GB_OSP_OMP_CONFIG'
545 ,MODULE_ID -- l_module_id -- sequence of above row
546 ,ACTIVE_INACTIVE_FLAG -- 'Y'
547 ,DESCRIPTION -- 'OSP/OMP Absence Schemes Configuration'
548 ,MULTIPLE_OCCURENCES_FLAG -- 'N'
549 ,LEGISLATION_CODE -- 'GB'
550 ,PROTECTED_FLAG -- 'Y'
551 ,PROGRAM_APPLICATION_ID -- NULL
552 ,PROGRAM_ID -- NULL
553 ,REQUEST_ID -- NULL
554 ,PROGRAM_UPDATE_DATE -- NULL
555 ,OBJECT_VERSION_NUMBER -- 1
556 ,LAST_UPDATE_DATE -- hr_api.g_sot -- DO NOT USE SYSDATE
557 ,LAST_UPDATED_BY -- 2
558 ,LAST_UPDATE_LOGIN -- 2
559 ,CREATED_BY -- 2
560 ,CREATION_DATE -- hr_api.g_sot -- DO NOT USE SYSDATE
561 ,TOTAL_UNIQUE_COLUMNS -- NULL
562 )
563 SELECT
564 'PQP_GB_OSP_OMP_CONFIG'
565 ,l_module_id
566 ,'Y'
567 ,'OSP/OMP Absence Schemes Configuration'
568 ,'N'
569 ,'GB'
570 ,'Y'
571 ,NULL
572 ,NULL
573 ,NULL
574 ,NULL
575 ,1
576 ,hr_api.g_sot
577 ,2
578 ,2
579 ,2
580 ,hr_api.g_sot
581 ,NULL
582 FROM DUAL
583 WHERE NOT EXISTS
584 ( SELECT 1 FROM pqp_configuration_types
585 WHERE configuration_type = 'PQP_GB_OSP_OMP_CONFIG'
586 AND module_id = l_module_id
587 AND legislation_code = 'GB'
588 ) ;
589
590 debug_exit(l_proc_name);
591
592 END create_config_data ;
593
594
595
596 /*========================================================================
597 * CREATE_USER_TEMPLATE
598 *=======================================================================*/
599 FUNCTION create_user_template
600 (p_plan_id in number
601 ,p_plan_description in varchar2
602 ,p_sch_cal_type in varchar2
603 ,p_sch_cal_duration in number
604 ,p_sch_cal_uom in varchar2
605 ,p_sch_cal_start_date in date
606 ,p_sch_cal_end_date in date
607 ,p_abs_days in varchar2
608 ,p_abs_ent_sick_leaves in number
609 ,p_abs_ent_holidays in number
610 ,p_abs_daily_rate_calc_method in varchar2
611 ,p_abs_daily_rate_calc_period in varchar2
612 ,p_abs_daily_rate_calc_divisor in number
613 ,p_abs_working_pattern in varchar2
614 ,p_abs_overlap_rule in varchar2
615 ,p_abs_ele_name in varchar2
616 ,p_abs_ele_reporting_name in varchar2
617 ,p_abs_ele_description in varchar2
618 ,p_abs_ele_processing_priority in number
619 ,p_abs_primary_yn in varchar2
620 ,p_pay_ele_reporting_name in varchar2
621 ,p_pay_ele_description in varchar2
622 ,p_pay_ele_processing_priority in number
623 ,p_pay_src_pay_component in varchar2
624 ,p_bnd1_ele_sub_name in varchar2
625 ,p_bnd2_ele_sub_name in varchar2
626 ,p_bnd3_ele_sub_name in varchar2
627 ,p_bnd4_ele_sub_name in varchar2
628 ,p_ele_eff_start_date in date
629 ,p_ele_eff_end_date in date
630 ,p_abs_type_lookup_type in varchar2
631 ,p_abs_type_lookup_value in t_abs_types
632 ,p_security_group_id in number
633 ,p_bg_id in number
634 ,p_plan_type_lookup_type in varchar2 default null -- LG
635 ,p_plan_type_lookup_value in t_plan_types -- LG
636 ,p_enable_ent_proration in varchar2 default null -- LG
637 ,p_scheme_type in varchar2 default null -- LG
638 ,p_abs_schedule_wp in varchar2 default null -- LG
639 -- Added additional segments for CSS
640 ,p_dual_rolling_duration in number default null
641 ,p_dual_rolling_UOM in varchar2 default null
642 ,p_ft_round_config in varchar2 default null
643 ,p_pt_round_config in varchar2 default null
644
645 )
646 RETURN NUMBER IS
647 --
648
649
650 /*--------------------------------------------------------------------
651 The input values are explained below : V-varchar2, D-Date, N-number
652 Input-Name Type Valid Values/Explaination
653 ---------- ----
654 --------------------------------------
655 p_plan_id (N) - LOV based i/p
656 p_plan_description (V) - User i/p Description
657 p_sch_cal_type (V) - LOV based i/p (Fixed/Rolling)
658 p_sch_cal_duration (N) - LOV based i/p
659 p_sch_cal_uom (V) - LOV based i/p
660 (Days/Weeks/Months/Years)
661 p_sch_cal_start_date (D) - User i/p Date
662 p_sch_cal_end_date (D) - User i/p Date
663 p_abs_days (V) - Radio Button based i/p
664 (Working/Calendar/User Provided)
665 p_abs_ent_sick_leaves (N) - User i/p UDT Id
666 p_abs_ent_holidays (N) - User i/p UDT Id
667 p_abs_daily_rate_calc_method (V) - Radio Button based i/p
668 (Working/Calendar)
669 p_abs_daily_rate_calc_period (V) - LOV based i/p (Annual/Pay Period)
670 p_abs_daily_rate_calc_divisor (N) - 365/User Provided Default 365
671 p_abs_working_pattern (V) - User i/p Working Pattern Name
672 p_abs_overlap_rule (V) - User i/p Absence Overlap Rule
673 p_abs_ele_name (V) - User i/p Element Name
674 p_abs_ele_reporting_name (V) - User i/p Reporting Name
675 p_abs_ele_description (V) - User i/p Description
676 p_abs_ele_processing_priority (N) - User provided
677 p_abs_primary_yn (V) - 'Y'/'N'
678 p_pay_ele_reporting_name (V) - User i/p Reporting Name
679 p_pay_ele_description (V) - User i/p Description
680 p_pay_ele_processing_priority (N) - User provided
681 p_pay_src_pay_component (V) - LOV based i/p
682 p_bnd1_ele_sub_name (V) - User i/p Band1 Sub Name
683 p_bnd2_ele_sub_name (V) - User i/p Band2 Sub Name
684 p_bnd3_ele_sub_name (V) - User i/p Band3 Sub Name
685 p_bnd4_ele_sub_name (V) - User i/p Band4 Sub Name
686 p_ele_eff_start_date (D) - User i/p Effective Start Date
687 p_ele_eff_end_date (D) - User i/p Effective End Date
688 p_abs_type_lookup_type (V) - Absence Type Lookup Name
689 p_abs_type_lookup_value (C) - Collection of Absence Types
690 p_bg_id (N) - Business group id
691 p_plan_type_lookup_type (V) varchar2 default null -- LG
692 p_plan_type_lookup_value t_plan_types -- LG
693 p_enable_ent_proration (V) LG
694 p_scheme_type (V) default null -- LG
695 p_abs_schedule_wp (V) default null -- LG
696 -- Added additional segments for CSS
697 p_dual_rolling_duration (N)
698 p_dual_rolling_UOM (N)
699 p_ft_round_config (V)
700 p_pt_round_config (V)
701 ----------------------------------------------------------------------*/
702 --
703
704
705 l_template_id pay_shadow_element_types.template_id%TYPE;
706 l_base_element_type_id pay_template_core_objects.core_object_id%TYPE;
707 l_source_template_id pay_element_templates.template_id%TYPE;
708 l_object_version_number pay_element_types_f.object_version_number%TYPE;
709
710 l_proc_step NUMBER(20,10);
711 l_proc_name VARCHAR2(80) :=
712 g_package_name || 'create_user_template';
713 l_element_type_id NUMBER;
714 l_balance_type_id NUMBER;
715 l_eei_element_type_id NUMBER;
716 l_ele_obj_ver_number NUMBER;
717 l_bal_obj_ver_number NUMBER;
718 i NUMBER;
719 l_eei_info_id NUMBER;
720 l_ovn_eei NUMBER;
721 l_abs_ele_correction_pp NUMBER := p_abs_ele_processing_priority - 50;
722 l_pay_ele_correction_pp NUMBER := p_pay_ele_processing_priority - 50;
723 l_formula_name pay_shadow_formulas.formula_name%TYPE;
724 l_formula_id NUMBER;
725 l_lookup_type fnd_lookup_types_vl.lookup_type%TYPE;
726 l_lookup_meaning fnd_lookup_types_vl.meaning%TYPE;
727 l_exists VARCHAR2(1);
728 l_display_sequence NUMBER;
729 l_base_name pay_element_templates.base_name%TYPE
730 := UPPER(TRANSLATE(TRIM(p_abs_ele_name),' ','_'));
731
732 l_exc_sec_days_bf VARCHAR2(1);
733
734 l_days_hours VARCHAR2(10) ; -- Added For Hours
735 l_template_name pay_element_templates.template_name%TYPE ;
736 l_configuration_information2 pay_element_templates.configuration_information2%TYPE;
737
738
739 TYPE t_ele_name IS TABLE OF pay_element_types_f.element_name%TYPE
740 INDEX BY BINARY_INTEGER;
741
742 l_ele_name t_ele_name;
743 l_ele_new_name t_ele_name;
744 l_main_ele_name t_ele_name;
745 l_retro_ele_name t_ele_name;
746
747 TYPE t_bal_name IS TABLE OF pay_balance_types.balance_name%TYPE
748 INDEX BY BINARY_INTEGER;
749
750 l_bal_name t_bal_name;
751 l_bal_new_name t_bal_name;
752
753
754 TYPE t_ele_reporting_name IS TABLE OF pay_element_types_f.reporting_name%TYPE
755 INDEX BY BINARY_INTEGER;
756
757 l_ele_reporting_name t_ele_reporting_name;
758
759 TYPE t_ele_description IS TABLE OF pay_element_types_f.description%TYPE
760 INDEX BY BINARY_INTEGER;
761
762 l_ele_description t_ele_description;
763
764 TYPE t_ele_pp IS TABLE OF pay_element_types_f.processing_priority%TYPE
765 INDEX BY BINARY_INTEGER;
766
767 l_ele_pp t_ele_pp;
768
769 TYPE t_eei_info IS TABLE OF pay_element_type_extra_info.eei_information19%
770 TYPE
771 INDEX BY BINARY_INTEGER;
772
773 l_main_eei_info19 t_eei_info;
774 l_retro_eei_info19 t_eei_info;
775
776 TYPE r_udt_type IS RECORD
777 (user_table_name VARCHAR2(80)
778 ,range_or_match VARCHAR2(30)
779 ,user_key_units VARCHAR2(30)
780 ,user_row_title VARCHAR2(80)
781 );
782
783 l_udt_type r_udt_type;
784
785 TYPE r_udt_cols_type IS RECORD
786 (user_column_name pay_user_columns.user_column_name%TYPE
787 ,formula_id pay_user_columns.formula_id%TYPE
788 ,business_group_id pay_user_columns.business_group_id%TYPE
789 ,legislation_code pay_user_columns.legislation_code%TYPE
790 );
791
792 TYPE t_udt_cols IS TABLE OF r_udt_cols_type
793 INDEX BY BINARY_INTEGER;
794
795 l_udt_cols t_udt_cols;
796
797 TYPE r_udt_rows_type IS RECORD
798 (row_low_range_or_name pay_user_rows_f.row_low_range_or_name%TYPE
799 ,display_sequence pay_user_rows_f.display_sequence%TYPE
800 ,row_high_range pay_user_rows_f.row_high_range%TYPE
801 ,business_group_id pay_user_rows.business_group_id%TYPE
802 ,legislation_code pay_user_rows.legislation_code%TYPE
803 );
804
805 TYPE t_udt_rows IS TABLE OF r_udt_rows_type
806 INDEX BY BINARY_INTEGER;
807
808 l_udt_rows t_udt_rows;
809
810 TYPE t_number IS TABLE OF NUMBER
811 INDEX BY BINARY_INTEGER;
812
813 l_ele_core_id pay_template_core_objects.core_object_id%TYPE:=
814 -1;
815
816 -- Extra Information variables
817 l_eei_information9 pay_element_type_extra_info.eei_information9%
818 TYPE;
819 l_eei_information10 pay_element_type_extra_info.eei_information10%
820 TYPE;
821 l_eei_information18 pay_element_type_extra_info.eei_information18%
822 TYPE;
823
824 l_eei_information30 pay_element_type_extra_info.eei_information30%
825 TYPE :='Sickness'; -- 'S';--ickness'; -- used to be Sickness Previously
826
827
828 l_eei_information29 pay_element_type_extra_info.eei_information29%
829 TYPE := 'OCCUPATIONAL';
830
831 l_eei_information28 pay_element_type_extra_info.eei_information28%
832 TYPE := 'PQP_GAP_ENTITLEMENT_BANDS';
833
834 l_eei_information27 pay_element_type_extra_info.eei_information27%
835 TYPE := 'PQP_GB_OSP_CALENDAR_RULES';
836
837
838 l_eei_information20 pay_element_type_extra_info.eei_information29%
839 TYPE := p_dual_rolling_duration ; --'4';
840
841
842 l_eei_information21 pay_element_type_extra_info.eei_information29%
843 TYPE := p_dual_rolling_UOM ; --'YEARS';
844
845
846 l_eei_information22 pay_element_type_extra_info.eei_information22%
847 TYPE := p_enable_ent_proration ; -- LG
848 l_eei_information23 pay_element_type_extra_info.eei_information23%
849 TYPE := p_abs_schedule_wp ; -- LG
850 l_eei_information24 pay_element_type_extra_info.eei_information24%
851 TYPE := p_plan_type_lookup_type ; -- LG
852 l_eei_information14 pay_element_type_extra_info.eei_information14%
853 TYPE := p_ft_round_config ;
854 l_eei_information25 pay_element_type_extra_info.eei_information25%
855 TYPE := p_pt_round_config ;
856
857
858
859
860
861 l_ctr BINARY_INTEGER:=0;
862 l_idx BINARY_INTEGER:=0;
863
864
865 --
866
867 CURSOR csr_get_ele_info (c_ele_name varchar2) is
868 SELECT element_type_id
869 ,object_version_number
870 FROM pay_shadow_element_types
871 WHERE template_id = l_template_id
872 AND element_name = c_ele_name;
873
874 CURSOR csr_get_bal_info (c_bal_name varchar2) is
875 SELECT balance_type_id
876 ,object_version_number
877 FROM pay_shadow_balance_types
878 WHERE template_id = l_template_id
879 AND balance_name = c_bal_name;
880
881 CURSOR csr_chk_primary_exists is
882 SELECT 'X'
883 FROM pay_element_type_extra_info
884 WHERE eei_information1 = fnd_number.number_to_canonical(p_plan_id)
885 AND eei_information16 = 'Y'
886 AND information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
887 AND rownum = 1;
888
889 --
890 --======================================================================
891 -- FUNCTION GET_TEMPLATE_ID
892 --======================================================================
893 FUNCTION get_template_id ( p_template_name IN VARCHAR2
894 ,p_legislation_code IN VARCHAR2 )
895 RETURN number IS
896 --
897 -- l_template_id NUMBER(9);
898 l_template_name VARCHAR2(80);
899 l_proc_step NUMBER(20,10);
900 l_proc_name VARCHAR2(72) := g_package_name || 'get_template_id';
901 --
902 CURSOR csr_get_temp_id is
903 SELECT template_id
904 FROM pay_element_templates
905 WHERE template_name = l_template_name
906 AND legislation_code = p_legislation_code
907 AND template_type = 'T'
908 AND business_group_id is NULL;
909 --
910 BEGIN
911 --
912 debug('Entering: '||l_proc_name, 10);
913 --
914 l_template_name := p_template_name ; -- 'PQP OSP';
915 --
916 l_proc_step := 20;
917 IF g_debug THEN
918 debug(l_proc_name, l_proc_step);
919 END IF;
920
921 --
922 for csr_get_temp_id_rec in csr_get_temp_id loop
923 l_template_id := csr_get_temp_id_rec.template_id;
924 end loop;
925 --
926 debug('Leaving: '||l_proc_name, 30);
927 --
928 RETURN l_template_id;
929 --
930 END get_template_id;
931
932 -----------------------------------------------------------------------------
933
934 --
935 --=======================================================================
936 -- FUNCTION GET_OBJECT_ID
937 --=======================================================================
938 FUNCTION get_object_id (p_object_type in varchar2,
939 p_object_name in varchar2)
940 RETURN NUMBER is
941 --
942 l_object_id NUMBER := NULL;
943 l_proc_step NUMBER(20,10);
944 l_proc_name varchar2(72) := g_package_name || 'get_object_id';
945 --
946 CURSOR c2 (c_object_name varchar2) is
947 SELECT element_type_id
948 FROM pay_element_types_f
949 WHERE element_name = c_object_name
950 AND business_group_id = p_bg_id;
951 --
952 CURSOR c3 (c_object_name in varchar2) is
953 SELECT ptco.core_object_id
954 FROM pay_shadow_balance_types psbt,
955 pay_template_core_objects ptco
956 WHERE psbt.template_id = l_template_id
957 AND psbt.balance_name = c_object_name
958 AND ptco.template_id = psbt.template_id
959 AND ptco.shadow_object_id = psbt.balance_type_id;
960 --
961 BEGIN
962 debug('Entering: '||l_proc_name, 10);
963 --
964 if p_object_type = 'ELE' then
965 for c2_rec in c2 (p_object_name) loop
966 l_object_id := c2_rec.element_type_id; -- element id
967 end loop;
968 elsif p_object_type = 'BAL' then
969 for c3_rec in c3 (p_object_name) loop
970 l_object_id := c3_rec.core_object_id; -- balance id
971 end loop;
972 end if;
973 --
974 debug('Leaving: '||l_proc_name, 20);
975 --
976 RETURN l_object_id;
977 --
978 END get_object_id;
979 --
980
981 --
982 --========================================================================
983 -- PROCEDURE Update Element Type with Retro Ele Info
984 --========================================================================
985 PROCEDURE update_ele_retro_info (p_main_ele_name in varchar2
986 ,p_retro_ele_name in varchar2
987 ) IS
988 --
989
990 l_main_ele_type_id pay_element_types_f.element_type_id%TYPE;
991 l_retro_ele_type_id pay_element_types_f.element_type_id%TYPE;
992 l_proc_step NUMBER(20,10);
993 l_proc_name VARCHAR2(72) := g_package_name ||
994 'update_ele_retro_info';
995
996 --
997 BEGIN
998
999 --
1000 debug ('Entering '||l_proc_name, 10);
1001 --
1002
1003 -- Get element type id for retro element
1004 l_retro_ele_type_id := get_object_id (p_object_type => 'ELE'
1005 ,p_object_name => p_retro_ele_name
1006 );
1007
1008
1009 l_proc_step := 20;
1010 IF g_debug THEN
1011 debug(l_proc_name, l_proc_step);
1012 END IF;
1013
1014 -- Get element type id for main element
1015 l_main_ele_type_id := get_object_id (p_object_type => 'ELE'
1016 ,p_object_name => p_main_ele_name
1017 );
1018
1019 -- Update main element with retro element info
1020
1021 l_proc_step := 30;
1022 IF g_debug THEN
1023 debug(l_proc_name, l_proc_step);
1024 END IF;
1025
1026
1027 UPDATE pay_element_types_f
1028 SET retro_summ_ele_id = l_retro_ele_type_id
1029 WHERE element_type_id = l_main_ele_type_id;
1030
1031 --
1032 debug ('Leaving '||l_proc_name, 40);
1033 --
1034
1035 END update_ele_retro_info;
1036 --
1037
1038
1039 -- -----------------------------------------------------------------------------
1040 -- --- FUNCTION get_formula_id
1041 -- -----------------------------------------------------------------------------
1042 -- FUNCTION get_formula_id (p_formula_name IN VARCHAR2)
1043 -- RETURN NUMBER
1044 -- IS
1045 --
1046 -- CURSOR csr_get_formula_id
1047 -- IS
1048 -- SELECT formula_id
1049 -- FROM pay_shadow_formulas
1050 -- WHERE formula_name = p_formula_name
1051 -- AND template_type = 'T';
1052 ---- AND ((business_group_id is not null AND business_group_id = p_bg_id) OR
1053 ---- (legislation_code is not null AND legislation_code = 'GB') OR
1054 ---- (business_group_id is null AND legislation_code is null));
1055 --
1056 -- l_proc_step NUMBER(20,10);
1057 -- l_proc_name VARCHAR2(72) := g_package_name || 'get_formula_id';
1058 -- l_formula_id NUMBER;
1059 --
1060 -- --
1061 -- BEGIN
1062 -- --
1063 -- debug ('Entering '||l_proc_name, 10);
1064 -- --
1065 --
1066 -- OPEN csr_get_formula_id;
1067 -- FETCH csr_get_formula_id INTO l_formula_id;
1068 -- CLOSE csr_get_formula_id;
1069 --
1070 -- --
1071 -- debug ('Leaving '||l_proc_name, 20);
1072 -- --
1073 --
1074 -- RETURN l_formula_id;
1075 --
1076 -- --
1077 -- END get_formula_id;
1078 -- --
1079
1080 -----------------------------------------------------------------------------
1081 --- PROCEDURE update input value default value
1082 -----------------------------------------------------------------------------
1083 PROCEDURE update_ipval_defval(p_ele_name IN VARCHAR2
1084 ,p_ip_name IN VARCHAR2
1085 ,p_def_value IN VARCHAR2)
1086 IS
1087
1088 CURSOR csr_getinput(c_ele_name varchar2
1089 ,c_iv_name varchar2)
1090 IS
1091 SELECT input_value_id
1092 ,piv.name
1093 ,piv.element_type_id
1094 FROM pay_input_values_f piv
1095 ,pay_element_types_f pet
1096 WHERE element_name = c_ele_name
1097 AND piv.element_type_id = pet.element_type_id
1098 AND (piv.business_group_id = p_bg_id OR piv.business_group_id IS NULL)
1099 AND piv.name = c_iv_name
1100 AND (piv.legislation_code = 'GB' OR piv.legislation_code IS NULL);
1101
1102 CURSOR csr_updinput(c_ip_id number
1103 ,c_element_type_id number)
1104 IS
1105 SELECT rowid
1106 FROM pay_input_values_f
1107 WHERE input_value_id = c_ip_id
1108 AND element_type_id = c_element_type_id
1109 FOR UPDATE NOWAIT;
1110
1111 csr_getinput_rec csr_getinput%rowtype;
1112 csr_updinput_rec csr_updinput%rowtype;
1113
1114
1115 l_proc_step NUMBER(20,10);
1116 l_proc_name VARCHAR2(72) := g_package_name ||
1117 'update_ipval_defval';
1118 --
1119 BEGIN
1120 --
1121
1122 --
1123 debug ('Entering '||l_proc_name, 10);
1124 --
1125 OPEN csr_getinput(p_ele_name
1126 ,p_ip_name);
1127 LOOP
1128
1129 FETCH csr_getinput INTO csr_getinput_rec;
1130 EXIT WHEN csr_getinput%NOTFOUND;
1131
1132 --
1133 l_proc_step := 20;
1134 IF g_debug THEN
1135 debug(l_proc_name, l_proc_step);
1136 END IF;
1137
1138 --
1139
1140 OPEN csr_updinput(csr_getinput_rec.input_value_id
1141 ,csr_getinput_rec.element_type_id);
1142 LOOP
1143
1144 FETCH csr_updinput INTO csr_updinput_rec;
1145 EXIT WHEN csr_updinput%NOTFOUND;
1146
1147 --
1148 l_proc_step := 30;
1149 IF g_debug THEN
1150 debug(l_proc_name, l_proc_step);
1151 END IF;
1152
1153 --
1154
1155 UPDATE pay_input_values_f
1156 SET default_value = p_def_value
1157 WHERE rowid = csr_updinput_rec.rowid;
1158
1159 END LOOP;
1160 CLOSE csr_updinput;
1161
1162 END LOOP;
1163 CLOSE csr_getinput;
1164
1165 --
1166 debug ('Leaving '||l_proc_name, 40);
1167 --
1168
1169 END update_ipval_defval;
1170 --
1171 --
1172 --======================================================================
1173 -- FUNCTION get_user_table_id
1174 --======================================================================
1175 FUNCTION get_user_table_id (p_udt_name in varchar2)
1176 RETURN NUMBER IS
1177 --
1178
1179 CURSOR csr_get_udt_id
1180 IS
1181 SELECT user_table_id
1182 FROM pay_user_tables
1183 WHERE user_table_name = p_udt_name
1184 AND (business_group_id = p_bg_id OR
1185 business_group_id IS NULL);
1186
1187 l_proc_step NUMBER(20,10);
1188 l_proc_name VARCHAR2(72) := g_package_name || 'get_user_table_id';
1189 l_user_table_id pay_user_tables.user_table_id%TYPE;
1190
1191 --
1192 BEGIN
1193 --
1194 debug('Entering '||l_proc_name, 10);
1195 --
1196 OPEN csr_get_udt_id;
1197 FETCH csr_get_udt_id INTO l_user_table_id;
1198 CLOSE csr_get_udt_id;
1199
1200 debug('Leaving '||l_proc_name, 20);
1201
1202 RETURN l_user_table_id;
1203
1204 END get_user_table_id;
1205 --
1206
1207 --
1208 --======================================================================
1209 -- FUNCTION get_udt_col_info
1210 --======================================================================
1211 PROCEDURE get_udt_col_info (p_lookup_type in varchar2
1212 ,p_lookup_code in varchar2
1213 ,p_formula_id in number
1214 ,p_business_group_id in number
1215 ,p_legislation_code in varchar2
1216 ,p_udt_cols out nocopy t_udt_cols
1217 )
1218 IS
1219 --
1220
1221 CURSOR csr_get_lookup_info is
1222 SELECT meaning
1223 FROM hr_lookups
1224 WHERE lookup_type = p_lookup_type
1225 AND lookup_code like p_lookup_code
1226 AND enabled_flag = 'Y'
1227 ORDER BY lookup_code;
1228
1229 l_proc_step NUMBER(20,10);
1230 l_proc_name VARCHAR2(72) := g_package_name || 'get_udt_col_info';
1231 l_udt_col_name pay_user_columns.user_column_name%TYPE;
1232 l_udt_cols t_udt_cols;
1233 i number;
1234
1235 --
1236 BEGIN
1237
1238 --
1239 debug ('Entering ' || l_proc_name, 10);
1240 --
1241
1242 -- Get information from Lookup
1243
1244 i := 0;
1245 OPEN csr_get_lookup_info;
1246 LOOP
1247
1248 FETCH csr_get_lookup_info INTO l_udt_col_name;
1249 EXIT WHEN csr_get_lookup_info%NOTFOUND;
1250
1251 i := i + 1;
1252 l_udt_cols(i).user_column_name := l_udt_col_name;
1253 l_udt_cols(i).formula_id := p_formula_id;
1254 l_udt_cols(i).business_group_id := p_business_group_id;
1255 l_udt_cols(i).legislation_code := p_legislation_code;
1256
1257 END LOOP;
1258
1259 p_udt_cols := l_udt_cols;
1260
1261 --
1262 debug ('Leaving '||l_proc_name, 20);
1263 -- Added by tmehra for nocopy changes Feb'03
1264
1265 EXCEPTION
1266 WHEN OTHERS THEN
1267 debug('Entering excep:'||l_proc_name, 35);
1268 p_udt_cols.delete;
1269 raise;
1270 --
1271
1272 END get_udt_col_info;
1273 --
1274
1275 --
1276 --======================================================================
1277 -- FUNCTION get_udt_row_info
1278 --======================================================================
1279 PROCEDURE get_udt_row_info (p_lookup_type in varchar2
1280 ,p_lookup_code in varchar2
1281 ,p_udt_type in varchar2
1282 ,p_display_sequence in out nocopy number
1283 ,p_business_group_id in number
1284 ,p_legislation_code in varchar2
1285 ,p_udt_rows in out nocopy t_udt_rows
1286 )
1287 IS
1288 --
1289
1290 CURSOR csr_get_lookup_info is
1291 SELECT meaning
1292 FROM hr_lookups
1293 WHERE lookup_type = p_lookup_type
1294 AND lookup_code like p_lookup_code
1295 AND enabled_flag = 'Y'
1296 ORDER BY lookup_code;
1297
1298 l_proc_name VARCHAR2(72) := g_package_name || 'get_udt_row_info';
1299 l_udt_row_name pay_user_rows_f.row_low_range_or_name%TYPE;
1300 l_udt_rows t_udt_rows;
1301 -- Nocopy changes
1302 l_udt_rows_nc t_udt_rows;
1303 l_display_seq_nc NUMBER;
1304
1305 i number;
1306 l_display_sequence number := p_display_sequence;
1307
1308 --
1309 BEGIN
1310
1311 --
1312 debug ('Entering ' || l_proc_name, 10);
1313 --
1314
1315 -- Nocopy changes
1316 l_udt_rows_nc := p_udt_rows;
1317 l_display_seq_nc := p_display_sequence;
1318
1319 -- Get information from Lookup
1320
1321 IF p_udt_rows.count > 0 THEN
1322 i := p_udt_rows.LAST;
1323 l_udt_rows := p_udt_rows;
1324
1325 ELSE
1326 i := 0;
1327
1328 END IF; -- End if of count check ...
1329
1330 OPEN csr_get_lookup_info;
1331 LOOP
1332
1333 FETCH csr_get_lookup_info INTO l_udt_row_name;
1334 EXIT WHEN csr_get_lookup_info%NOTFOUND;
1335
1336 i := i + 1;
1337
1338 l_udt_rows(i).row_low_range_or_name := l_udt_row_name;
1339
1340 IF p_udt_type = 'R' THEN
1341 l_udt_rows(i).row_high_range := l_udt_row_name;
1342 END IF;
1343
1344 l_udt_rows(i).business_group_id := p_business_group_id;
1345 l_udt_rows(i).legislation_code := p_legislation_code;
1346 l_udt_rows(i).display_sequence := l_display_sequence;
1347
1348 l_display_sequence := l_display_sequence + 1;
1349
1350 END LOOP;
1351
1352 p_display_sequence := l_display_sequence;
1353
1354 p_udt_rows := l_udt_rows;
1355
1356 --
1357 debug ('Leaving '||l_proc_name, 20);
1358 --
1359
1360 -- Added by tmehra for nocopy changes Feb'03
1361
1362 EXCEPTION
1363 WHEN OTHERS THEN
1364 debug('Entering excep:'||l_proc_name, 35);
1365 p_udt_rows := l_udt_rows_nc;
1366 p_display_sequence := l_display_seq_nc;
1367 raise;
1368
1369 END get_udt_row_info;
1370 --
1371
1372 --
1373 --======================================================================
1374 -- FUNCTION create_udt
1375 --======================================================================
1376 FUNCTION create_udt (p_udt_type r_udt_type
1377 ,p_udt_cols t_udt_cols
1378 ,p_udt_rows t_udt_rows
1379 )
1380 RETURN NUMBER IS
1381 --
1382
1383 CURSOR csr_get_next_udt_row_seq
1384 IS
1385 SELECT pay_user_rows_s.NEXTVAL
1386 FROM dual;
1387
1388 l_proc_name VARCHAR2(72) := g_package_name || 'create_udt';
1389 l_user_table_id pay_user_tables.user_table_id%TYPE;
1390 l_user_column_id pay_user_columns.user_column_id%TYPE;
1391 l_user_row_id pay_user_rows_f.user_row_id%TYPE;
1392 l_udt_rowid rowid ;
1393 l_udt_cols_rowid rowid;
1394 l_udt_rows_rowid rowid;
1395
1396 --
1397 BEGIN
1398
1399 --
1400 debug ('Entering '||l_proc_name, 10);
1401 --
1402
1403 -- Create the UDT
1404
1405 l_proc_step := 20;
1406 IF g_debug THEN
1407 debug(l_proc_name, l_proc_step);
1408 END IF;
1409
1410
1411 pay_user_tables_pkg.insert_row
1412 (p_rowid => l_udt_rowid
1413 ,p_user_table_id => l_user_table_id
1414 ,p_business_group_id => p_bg_id
1415 ,p_legislation_code => NULL
1416 ,p_legislation_subgroup => NULL
1417 ,p_range_or_match => p_udt_type.range_or_match
1418 ,p_user_key_units => p_udt_type.user_key_units
1419 ,p_user_table_name => p_udt_type.user_table_name
1420 ,p_user_row_title => p_udt_type.user_row_title
1421 );
1422
1423 IF p_udt_cols.count > 0 THEN
1424
1425 -- Create the columns
1426 l_proc_step := 30;
1427 IF g_debug THEN
1428 debug(l_proc_name, l_proc_step);
1429 END IF;
1430
1431
1432 i := p_udt_cols.FIRST;
1433
1434 WHILE i IS NOT NULL
1435 LOOP
1436
1437 pay_user_columns_pkg.insert_row
1438 (p_rowid => l_udt_cols_rowid
1439 ,p_user_column_id => l_user_column_id
1440 ,p_user_table_id => l_user_table_id
1441 ,p_business_group_id => p_udt_cols(i).business_group_id
1442 ,p_legislation_code => p_udt_cols(i).legislation_code
1443 ,p_legislation_subgroup => NULL
1444 ,p_user_column_name => p_udt_cols(i).user_column_name
1445 ,p_formula_id => p_udt_cols(i).formula_id
1446 );
1447
1448 i := p_udt_cols.NEXT(i);
1449 END LOOP;
1450
1451 END IF; -- End if of user cols > 1 check ...
1452
1453 IF p_udt_rows.count > 0 THEN
1454
1455 l_proc_step := 40;
1456 IF g_debug THEN
1457 debug(l_proc_name, l_proc_step);
1458 END IF;
1459
1460 -- Create the rows
1461
1462 i := p_udt_rows.FIRST;
1463
1464 WHILE i IS NOT NULL
1465 LOOP
1466
1467 OPEN csr_get_next_udt_row_seq;
1468 FETCH csr_get_next_udt_row_seq INTO l_user_row_id;
1469 CLOSE csr_get_next_udt_row_seq;
1470
1471 pay_user_rows_pkg.pre_insert
1472 (p_rowid => l_udt_rows_rowid
1473 ,p_user_table_id => l_user_table_id
1474 ,p_row_low_range_or_name => p_udt_rows(i).row_low_range_or_name
1475 ,p_user_row_id => l_user_row_id
1476 ,p_business_group_id => p_bg_id
1477 );
1478
1479 INSERT INTO pay_user_rows_f
1480 (user_row_id
1481 ,effective_start_date
1482 ,effective_end_date
1483 ,business_group_id
1484 ,legislation_code
1485 ,user_table_id
1486 ,row_low_range_or_name
1487 ,display_sequence
1488 ,legislation_subgroup
1489 ,row_high_range
1490 )
1491 VALUES
1492 (l_user_row_id
1493 ,p_ele_eff_start_date
1494 ,nvl(p_ele_eff_end_date, hr_api.g_eot)
1495 ,p_udt_rows(i).business_group_id
1496 ,p_udt_rows(i).legislation_code
1497 ,l_user_table_id
1498 ,p_udt_rows(i).row_low_range_or_name
1499 ,p_udt_rows(i).display_sequence
1500 ,NULL
1501 ,p_udt_rows(i).row_high_range
1502 );
1503
1504 i := p_udt_rows.NEXT(i);
1505
1506 END LOOP; -- End Loop for user rows...
1507 END IF; -- End if of user rows if present check...
1508
1509 debug ('Leaving '||l_proc_name, 50);
1510
1511 RETURN l_user_table_id;
1512
1513 --
1514 END create_udt;
1515 --
1516
1517 --
1518 --======================================================================
1519 -- PROCEDURE create_lookup
1520 --======================================================================
1521 PROCEDURE create_lookup (p_lookup_type varchar2
1522 ,p_lookup_meaning varchar2
1523 ,p_lookup_values t_abs_types
1524 ) IS
1525 --
1526
1527 CURSOR csr_chk_uniq_type
1528 IS
1529 SELECT 'X'
1530 FROM fnd_lookup_types_vl
1531 WHERE lookup_type = p_lookup_type
1532 AND security_group_id = p_security_group_id
1533 AND view_application_id = 3;
1534
1535 CURSOR csr_chk_uniq_meaning
1536 IS
1537 SELECT 'X'
1538 FROM fnd_lookup_types_vl
1539 WHERE meaning = p_lookup_meaning
1540 AND security_group_id = p_security_group_id
1541 AND view_application_id = 3;
1542
1543 l_proc_step NUMBER(20,10);
1544 l_proc_name VARCHAR2(72) := g_package_name || 'create_lookup';
1545 l_exists VARCHAR2(1);
1546 l_rowid fnd_lookup_types_vl.row_id%type;
1547 l_user_id number := fnd_global.user_id;
1548 l_login_id number := fnd_global.login_id;
1549
1550 --
1551 BEGIN
1552 --
1553 debug('Entering '||l_proc_name, 10);
1554 --
1555
1556 -- Check unique lookup type
1557 OPEN csr_chk_uniq_type;
1558 FETCH csr_chk_uniq_type INTO l_exists;
1559
1560 IF csr_chk_uniq_type%FOUND THEN
1561
1562 -- Raise error
1563 CLOSE csr_chk_uniq_type;
1564 hr_utility.set_message(0, 'QC-DUPLICATE TYPE');
1565 hr_utility.raise_error;
1566
1567 END IF; -- End if of unique lookup type check ...
1568 CLOSE csr_chk_uniq_type;
1569
1570 l_proc_step := 20;
1571 IF g_debug THEN
1572 debug(l_proc_name, l_proc_step);
1573 END IF;
1574
1575
1576 -- Check unique lookup type meaning
1577 OPEN csr_chk_uniq_meaning;
1578 FETCH csr_chk_uniq_meaning INTO l_exists;
1579
1580 IF csr_chk_uniq_meaning%FOUND THEN
1581
1582 -- Raise error
1583 CLOSE csr_chk_uniq_meaning;
1584 hr_utility.set_message(0, 'QC-DUPLICATE TYPE MEANING');
1585 hr_utility.raise_error;
1586
1587 END IF; -- End if of unique lookup type meaning check ...
1588 CLOSE csr_chk_uniq_meaning;
1589
1590 -- Create Lookup type
1591 l_proc_step := 30;
1592 IF g_debug THEN
1593 debug(l_proc_name, l_proc_step);
1594 END IF;
1595
1596
1597 fnd_lookup_types_pkg.insert_row
1598 (
1599 x_rowid => l_rowid
1600 ,x_lookup_type => p_lookup_type
1601 ,x_security_group_id => p_security_group_id
1602 ,x_view_application_id => 3
1603 ,x_application_id => 800
1604 ,x_customization_level => 'U'
1605 ,x_meaning => p_lookup_meaning
1606 ,x_description => NULL
1607 ,x_creation_date => SYSDATE
1608 ,x_created_by => l_user_id
1609 ,x_last_update_date => SYSDATE
1610 ,x_last_updated_by => l_user_id
1611 ,x_last_update_login => l_login_id
1612 );
1613
1614 -- Create Lookup Values
1615 -- The validation for lookup values should've been taken care in the
1616 -- form
1617 l_proc_step := 40;
1618 IF g_debug THEN
1619 debug(l_proc_name, l_proc_step);
1620 END IF;
1621
1622 IF p_lookup_values.count > 0 THEN
1623
1624 i := p_lookup_values.FIRST;
1625 WHILE i IS NOT NULL
1626 LOOP
1627 fnd_lookup_values_pkg.insert_row
1628 (
1629 x_rowid => l_rowid
1630 ,x_lookup_type => p_lookup_type
1631 ,x_security_group_id => p_security_group_id
1632 ,x_view_application_id => 3
1633 ,x_lookup_code => fnd_number.number_to_canonical(
1634 p_lookup_values(i).abs_type_id)
1635 ,x_tag => NULL
1636 ,x_attribute_category => NULL
1637 ,x_attribute1 => NULL
1638 ,x_attribute2 => NULL
1639 ,x_attribute3 => NULL
1640 ,x_attribute4 => NULL
1641 ,x_attribute5 => NULL
1642 ,x_attribute6 => NULL
1643 ,x_attribute7 => NULL
1644 ,x_attribute8 => NULL
1645 ,x_attribute9 => NULL
1646 ,x_attribute10 => NULL
1647 ,x_attribute11 => NULL
1648 ,x_attribute12 => NULL
1649 ,x_attribute13 => NULL
1650 ,x_attribute14 => NULL
1651 ,x_attribute15 => NULL
1652 ,x_enabled_flag => 'Y'
1653 ,x_start_date_active => p_ele_eff_start_date
1654 ,x_end_date_active => NULL
1655 ,x_territory_code => NULL
1656 ,x_meaning => p_lookup_values(i).abs_type_name
1657 ,x_description => NULL
1658 ,x_creation_date => SYSDATE
1659 ,x_created_by => l_user_id
1660 ,x_last_update_date => SYSDATE
1661 ,x_last_updated_by => l_user_id
1662 ,x_last_update_login => l_login_id
1663 );
1664
1665 i := p_lookup_values.NEXT(i);
1666
1667 END LOOP;
1668
1669 END IF; -- End if of p_lookup_values check ...
1670
1671 --
1672 debug('Leaving '||l_proc_name, 60);
1673 --
1674 END create_lookup;
1675 --
1676
1677 --
1678 --======================================================================
1679 -- PROCEDURE create_plan_lookup
1680 --======================================================================
1681 PROCEDURE create_plan_lookup (p_lookup_type varchar2
1682 ,p_lookup_meaning varchar2
1683 ,p_lookup_values t_plan_types
1684 ) IS
1685 --
1686
1687 CURSOR csr_chk_uniq_type
1688 IS
1689 SELECT 'X'
1690 FROM fnd_lookup_types_vl
1691 WHERE lookup_type = p_lookup_type
1692 AND security_group_id = p_security_group_id
1693 AND view_application_id = 3;
1694
1695 CURSOR csr_chk_uniq_meaning
1696 IS
1697 SELECT 'X'
1698 FROM fnd_lookup_types_vl
1699 WHERE meaning = p_lookup_meaning
1700 AND security_group_id = p_security_group_id
1701 AND view_application_id = 3;
1702
1703 l_proc_step NUMBER(20,10);
1704 l_proc_name VARCHAR2(72) := g_package_name || 'create_plan_lookup';
1705 l_exists VARCHAR2(1);
1706 l_rowid fnd_lookup_types_vl.row_id%type;
1707 l_user_id number := fnd_global.user_id;
1708 l_login_id number := fnd_global.login_id;
1709
1710 --
1711 BEGIN
1712 --
1713 debug('Entering '||l_proc_name, 10);
1714 --
1715
1716 -- Check unique lookup type
1717 OPEN csr_chk_uniq_type;
1718 FETCH csr_chk_uniq_type INTO l_exists;
1719
1720 IF csr_chk_uniq_type%FOUND THEN
1721
1722 -- Raise error
1723 CLOSE csr_chk_uniq_type;
1724 hr_utility.set_message(0, 'QC-DUPLICATE TYPE');
1725 hr_utility.raise_error;
1726
1727 END IF; -- End if of unique lookup type check ...
1728 CLOSE csr_chk_uniq_type;
1729
1730 l_proc_step := 20;
1731 IF g_debug THEN
1732 debug(l_proc_name, l_proc_step);
1733 END IF;
1734
1735
1736 -- Check unique lookup type meaning
1737 OPEN csr_chk_uniq_meaning;
1738 FETCH csr_chk_uniq_meaning INTO l_exists;
1739
1740 IF csr_chk_uniq_meaning%FOUND THEN
1741
1742 -- Raise error
1743 CLOSE csr_chk_uniq_meaning;
1744 hr_utility.set_message(0, 'QC-DUPLICATE TYPE MEANING');
1745 hr_utility.raise_error;
1746
1747 END IF; -- End if of unique lookup type meaning check ...
1748 CLOSE csr_chk_uniq_meaning;
1749
1750 -- Create Lookup type
1751 l_proc_step := 30;
1752 IF g_debug THEN
1753 debug(l_proc_name, l_proc_step);
1754 END IF;
1755
1756
1757 fnd_lookup_types_pkg.insert_row
1758 (
1759 x_rowid => l_rowid
1760 ,x_lookup_type => p_lookup_type
1761 ,x_security_group_id => p_security_group_id
1762 ,x_view_application_id => 3
1763 ,x_application_id => 800
1764 ,x_customization_level => 'U'
1765 ,x_meaning => p_lookup_meaning
1766 ,x_description => NULL
1767 ,x_creation_date => SYSDATE
1768 ,x_created_by => l_user_id
1769 ,x_last_update_date => SYSDATE
1770 ,x_last_updated_by => l_user_id
1771 ,x_last_update_login => l_login_id
1772 );
1773
1774 -- Create Lookup Values
1775 -- The validation for lookup values should've been taken care in the
1776 -- form
1777 l_proc_step := 40;
1778 IF g_debug THEN
1779 debug(l_proc_name, l_proc_step);
1780 END IF;
1781
1782 IF p_lookup_values.count > 0 THEN
1783
1784 i := p_lookup_values.FIRST;
1785 WHILE i IS NOT NULL
1786 LOOP
1787 fnd_lookup_values_pkg.insert_row
1788 (
1789 x_rowid => l_rowid
1790 ,x_lookup_type => p_lookup_type
1791 ,x_security_group_id => p_security_group_id
1792 ,x_view_application_id => 3
1793 ,x_lookup_code => fnd_number.number_to_canonical(
1794 p_lookup_values(i).plan_type_id)
1795 ,x_tag => NULL
1796 ,x_attribute_category => NULL
1797 ,x_attribute1 => NULL
1798 ,x_attribute2 => NULL
1799 ,x_attribute3 => NULL
1800 ,x_attribute4 => NULL
1801 ,x_attribute5 => NULL
1802 ,x_attribute6 => NULL
1803 ,x_attribute7 => NULL
1804 ,x_attribute8 => NULL
1805 ,x_attribute9 => NULL
1806 ,x_attribute10 => NULL
1807 ,x_attribute11 => NULL
1808 ,x_attribute12 => NULL
1809 ,x_attribute13 => NULL
1810 ,x_attribute14 => NULL
1811 ,x_attribute15 => NULL
1812 ,x_enabled_flag => 'Y'
1813 ,x_start_date_active => p_ele_eff_start_date
1814 ,x_end_date_active => NULL
1815 ,x_territory_code => NULL
1816 ,x_meaning => p_lookup_values(i).name
1817 ,x_description => NULL
1818 ,x_creation_date => SYSDATE
1819 ,x_created_by => l_user_id
1820 ,x_last_update_date => SYSDATE
1821 ,x_last_updated_by => l_user_id
1822 ,x_last_update_login => l_login_id
1823 );
1824
1825 i := p_lookup_values.NEXT(i);
1826
1827 END LOOP;
1828
1829 END IF; -- End if of p_lookup_values check ...
1830
1831 --
1832 debug('Leaving '||l_proc_name, 60);
1833 --
1834 END create_plan_lookup;
1835 --
1836 ---------------
1837 --==============================================================================
1838 -- MAIN FUNCTION
1839 --==============================================================================
1840
1841 BEGIN
1842
1843
1844 g_debug := hr_utility.debug_enabled;
1845
1846 debug_enter(l_proc_name);
1847
1848 ---------------------
1849 -- Set session date
1850 ---------------------
1851
1852 pay_db_pay_setup.set_session_date(nvl(p_ele_eff_start_date, sysdate));
1853 --
1854
1855
1856 l_proc_step := 20;
1857 IF g_debug THEN
1858 debug(l_proc_name, l_proc_step);
1859 END IF;
1860
1861 --
1862
1863 IF (hr_utility.chk_product_install('Oracle Payroll',g_template_leg_code))
1864 THEN
1865
1866 l_exc_sec_days_bf := NULL;
1867
1868 OPEN csr_chk_primary_exists;
1869 FETCH csr_chk_primary_exists INTO l_exists;
1870
1871 -- Check whether Primary Plan Exists when creating Secondary Plans
1872 IF p_abs_primary_yn = 'N' THEN
1873
1874 l_proc_step := 25;
1875 IF g_debug THEN
1876 debug(l_proc_name, l_proc_step);
1877 END IF;
1878
1879
1880
1881 IF csr_chk_primary_exists%NOTFOUND THEN
1882
1883 -- Raise Error
1884 CLOSE csr_chk_primary_exists;
1885 hr_utility.set_message(8303, 'PQP_230608_OSP_PRIM_NOT_FOUND');
1886 hr_utility.raise_error;
1887
1888 END IF; -- End if of primary element check...
1889
1890 -- Exclude balance feeds to generic days balance for secondary elements
1891 l_exc_sec_days_bf := 'N';
1892
1893 -- Check whether Primary Elements exists for this plan
1894 -- when creating Primary Scheme
1895
1896 ELSIF p_abs_primary_yn = 'Y' THEN
1897
1898 IF csr_chk_primary_exists%FOUND THEN
1899
1900 -- Raise Error
1901 CLOSE csr_chk_primary_exists;
1902 hr_utility.set_message(8303, 'PQP_230666_OSP_PRIMARY_EXISTS');
1903 hr_utility.raise_error;
1904
1905 END IF; -- End if of primary element check...
1906
1907 END IF; -- End if of abs primary yes or no check...
1908 CLOSE csr_chk_primary_exists;
1909
1910
1911 ---------------------------
1912 -- Get Source Template ID
1913 ---------------------------
1914
1915
1916 -- Added for Hours
1917
1918 -- Check which Template to call
1919 -- If p_abs_days = 'H' or p_abs_daily_rate_calc_method = 'H'
1920 -- then Call 'OSP Hours Template' else 'OSP Template'
1921
1922 IF p_abs_days = 'H' OR p_abs_daily_rate_calc_method = 'H' THEN
1923 l_template_name := 'PQP OSP HOURS' ;
1924 l_days_hours := 'Hours ' ;
1925 ELSE
1926 l_template_name := 'PQP OSP' ;
1927 l_days_hours := NULL ;
1928 END IF ;
1929
1930 -- Added for Hours
1931
1932
1933 l_source_template_id := get_template_id
1934 (p_template_name => l_template_name
1935 ,p_legislation_code => g_template_leg_code
1936 );
1937
1938
1939
1940 /*--------------------------------------------------------------------------
1941 Create the user Structure
1942 The Configuration Flex segments for the Exclusion Rules are as follows:
1943 ---------------------------------------------------------------------------
1944 Config1 --
1945 Config2 --
1946 ---------------------------------------------------------------------------*/
1947
1948 l_proc_step := 40;
1949 IF g_debug THEN
1950 debug(l_proc_name, l_proc_step);
1951 END IF;
1952
1953
1954 --
1955 -- create user structure from the template
1956 --
1957
1958 IF p_sch_cal_type = 'DUALROLLING' THEN
1959 l_configuration_information2 := 'CIVILSERVICE_OSP';
1960 ELSE
1961 l_configuration_information2 := 'REGULAR_OSP';
1962 END IF;
1963
1964 pay_element_template_api.create_user_structure
1965 (p_validate => false
1966 ,p_effective_date => p_ele_eff_start_date
1967 ,p_business_group_id => p_bg_id
1968 ,p_source_template_id => l_source_template_id
1969 ,p_base_name => p_abs_ele_name
1970 ,p_configuration_information1 => l_exc_sec_days_bf
1971 ,p_configuration_information2 => l_configuration_information2
1972 ,p_template_id => l_template_id
1973 ,p_allow_base_name_reuse => true
1974 ,p_object_version_number => l_object_version_number
1975 );
1976 --
1977
1978 l_proc_step := 50;
1979 IF g_debug THEN
1980 debug(l_proc_name, l_proc_step);
1981 END IF;
1982
1983 ---------------------------------------------------------------------------
1984 ---------------------------- Update Shadow Structure ----------------------
1985 --
1986
1987
1988 l_ctr := l_ctr + 1;
1989
1990
1991 l_ele_name(l_ctr) := p_abs_ele_name || ' OSP '||l_days_hours||'Absence';
1992 l_ele_reporting_name(l_ctr) := p_abs_ele_reporting_name;
1993 l_ele_description(l_ctr) := p_abs_ele_description;
1994 l_ele_pp(l_ctr) := p_abs_ele_processing_priority;
1995
1996 l_ctr := l_ctr + 1;
1997
1998 l_ele_name(l_ctr) := p_abs_ele_name || ' OSP '||l_days_hours||'Pay';
1999 l_ele_reporting_name(l_ctr) := p_pay_ele_reporting_name;
2000 l_ele_description(l_ctr) := p_pay_ele_description;
2001 l_ele_pp(l_ctr) := p_pay_ele_processing_priority;
2002
2003
2004 l_idx := l_ele_name.FIRST;
2005 WHILE l_idx IS NOT NULL
2006 LOOP
2007
2008 OPEN csr_get_ele_info(l_ele_name(l_idx));
2009 LOOP
2010 FETCH csr_get_ele_info INTO l_element_type_id,l_ele_obj_ver_number;
2011 EXIT WHEN csr_get_ele_info%NOTFOUND;
2012 if i = 1 then
2013 l_base_element_type_id := l_element_type_id;
2014 end if;
2015
2016 pay_shadow_element_api.update_shadow_element
2017 (p_validate => false
2018 ,p_effective_date => p_ele_eff_start_date
2019 ,p_element_type_id => l_element_type_id
2020 ,p_element_name => l_ele_name(l_idx)
2021 ,p_reporting_name => l_ele_reporting_name(l_idx)
2022 ,p_description => l_ele_description(l_idx)
2023 ,p_relative_processing_priority => l_ele_pp(l_idx)
2024 ,p_object_version_number => l_ele_obj_ver_number
2025 );
2026
2027 END LOOP;
2028 CLOSE csr_get_ele_info;
2029
2030 l_idx := l_ele_name.NEXT(l_idx);
2031
2032 END LOOP; -- WHILE l_idx IS NOT NULL
2033
2034
2035 l_ctr := 0;
2036 l_ctr := l_ctr + 1; --1
2037
2038 l_ele_name(l_ctr) := p_abs_ele_name || ' OSP '||l_days_hours||'Absence Retro';
2039 l_ele_new_name(l_ctr) := l_ele_name(l_ctr);
2040 l_ele_pp(l_ctr) := l_abs_ele_correction_pp;
2041
2042 l_ctr := l_ctr + 1; --2
2043
2044 l_ele_name(l_ctr) := p_abs_ele_name || ' OSP '||l_days_hours||'Pay Retro';
2045 l_ele_new_name(l_ctr) := l_ele_name(l_ctr);
2046 l_ele_pp(l_ctr) := l_pay_ele_correction_pp;
2047
2048 l_ctr := l_ctr + 1; --3
2049
2050 l_ele_name(l_ctr) := p_abs_ele_name || ' OSP '||l_days_hours||'Band1 Pay';
2051 l_ele_new_name(l_ctr) := l_ele_name(l_ctr);
2052 IF p_bnd1_ele_sub_name IS NOT NULL THEN
2053 l_ele_new_name(l_ctr) := p_abs_ele_name || ' ' || p_bnd1_ele_sub_name ||
2054 ' OSP '||l_days_hours||'Band1 Pay';
2055 END IF; -- End if of bnd1 sub name not null check...
2056 l_ele_pp(l_ctr) := p_pay_ele_processing_priority;
2057
2058 l_ctr := l_ctr + 1; --4
2059
2060 l_ele_name(l_ctr) := p_abs_ele_name || ' OSP '||l_days_hours||'Band1 Pay Retro';
2061 l_ele_new_name(l_ctr) := l_ele_name(l_ctr);
2062 IF p_bnd1_ele_sub_name IS NOT NULL THEN
2063 l_ele_new_name(l_ctr) := p_abs_ele_name || ' ' || p_bnd1_ele_sub_name ||
2064 ' OSP '||l_days_hours||'Band1 Pay Retro';
2065 END IF; -- End if of bnd1 sub name not null check...
2066 l_ele_pp(l_ctr) := l_pay_ele_correction_pp;
2067
2068 l_ctr := l_ctr + 1; --5
2069
2070 l_ele_name(l_ctr) := p_abs_ele_name || ' OSP '||l_days_hours||'Band2 Pay';
2071 l_ele_new_name(l_ctr) := l_ele_name(l_ctr);
2072 IF p_bnd2_ele_sub_name IS NOT NULL THEN
2073 l_ele_new_name(l_ctr) := p_abs_ele_name || ' ' || p_bnd2_ele_sub_name ||
2074 ' OSP '||l_days_hours||'Band2 Pay';
2075 END IF; -- End if of bnd2 sub name not null check ...
2076 l_ele_pp(l_ctr) := p_pay_ele_processing_priority;
2077
2078 l_ctr := l_ctr + 1; --6
2079
2080 l_ele_name(l_ctr) := p_abs_ele_name || ' OSP '||l_days_hours||'Band2 Pay Retro';
2081 l_ele_new_name(l_ctr) := l_ele_name(l_ctr);
2082 IF p_bnd2_ele_sub_name IS NOT NULL THEN
2083 l_ele_new_name(l_ctr) := p_abs_ele_name || ' ' || p_bnd2_ele_sub_name ||
2084 ' OSP '||l_days_hours||'Band2 Pay Retro';
2085 END IF; -- End if of bnd2 sub name not null check ...
2086 l_ele_pp(l_ctr) := l_pay_ele_correction_pp;
2087
2088
2089 IF p_sch_cal_type <> 'DUALROLLING' -- In Dual Rolling Schemes we do not support band3 and 4
2090 THEN
2091
2092 l_ctr := l_ctr + 1; --7
2093
2094 l_ele_name(l_ctr) := p_abs_ele_name || ' OSP '||l_days_hours||'Band3 Pay';
2095 l_ele_new_name(l_ctr) := l_ele_name(l_ctr);
2096 IF p_bnd3_ele_sub_name IS NOT NULL THEN
2097 l_ele_new_name(l_ctr) := p_abs_ele_name || ' ' || p_bnd3_ele_sub_name ||
2098 ' OSP '||l_days_hours||'Band3 Pay';
2099 END IF; -- End if of bnd3 sub name not null check...
2100 l_ele_pp(l_ctr) := p_pay_ele_processing_priority;
2101
2102 l_ctr := l_ctr + 1; --8
2103
2104 l_ele_name(l_ctr) := p_abs_ele_name || ' OSP '||l_days_hours||'Band3 Pay Retro';
2105 l_ele_new_name(l_ctr) := l_ele_name(l_ctr);
2106 IF p_bnd3_ele_sub_name IS NOT NULL THEN
2107 l_ele_new_name(l_ctr) := p_abs_ele_name || ' ' || p_bnd3_ele_sub_name ||
2108 ' OSP '||l_days_hours||'Band3 Pay Retro';
2109 END IF; -- End if of bnd3 sub name not null check...
2110 l_ele_pp(l_ctr) := l_pay_ele_correction_pp;
2111
2112 l_ctr := l_ctr + 1; --9
2113
2114 l_ele_name(l_ctr) := p_abs_ele_name || ' OSP '||l_days_hours||'Band4 Pay';
2115 l_ele_new_name(l_ctr) := l_ele_name(l_ctr);
2116 IF p_bnd4_ele_sub_name IS NOT NULL THEN
2117 l_ele_new_name(l_ctr) := p_abs_ele_name || ' ' || p_bnd4_ele_sub_name ||
2118 ' OSP '||l_days_hours||'Band4 Pay';
2119 END IF; -- End if of bnd4 sub name not null check...
2120 l_ele_pp(l_ctr) := p_pay_ele_processing_priority;
2121
2122 l_ctr := l_ctr + 1; --10
2123
2124 l_ele_name(l_ctr) := p_abs_ele_name || ' OSP '||l_days_hours||'Band4 Pay Retro';
2125 l_ele_new_name(l_ctr) := l_ele_name(l_ctr);
2126 IF p_bnd4_ele_sub_name IS NOT NULL THEN
2127 l_ele_new_name(l_ctr) := p_abs_ele_name || ' ' || p_bnd4_ele_sub_name ||
2128 ' OSP '||l_days_hours||'Band4 Pay Retro';
2129 END IF; -- End if of bnd4 sub name not null check...
2130 l_ele_pp(l_ctr) := l_pay_ele_correction_pp;
2131
2132
2133 END IF; -- IF p_sch_cal_type <> 'DUALROLLING'
2134
2135 l_ctr := l_ctr + 1; --11 or 7
2136
2137 IF l_days_hours IS NULL
2138 THEN
2139 l_ele_name(l_ctr) := p_abs_ele_name || ' OSP Minimum Pay';
2140 -- once we support this in hours
2141 --l_ele_name(l_ctr) := p_abs_ele_name || ' OSP '||l_days_hours||'OSP Minimum Pay';
2142 l_ele_new_name(l_ctr) := l_ele_name(l_ctr);
2143 l_ele_pp(l_ctr) := p_pay_ele_processing_priority;
2144
2145
2146 --once I add the twin retro element
2147 l_ctr := l_ctr + 1; --12 or 8
2148 l_ele_name(l_ctr) := p_abs_ele_name || ' OSP Minimum Pay Retro';
2149 -- once we support this in hours
2150 --l_ele_name(l_ctr) := p_abs_ele_name || ' OSP '||l_days_hours||'OSP Minimum Pay';
2151 l_ele_new_name(l_ctr) := l_ele_name(l_ctr);
2152 l_ele_pp(l_ctr) := l_pay_ele_correction_pp;
2153
2154 END IF;
2155
2156
2157 l_proc_step := 60;
2158 IF g_debug THEN
2159 debug(l_proc_name, l_proc_step);
2160 END IF;
2161
2162
2163 l_idx := l_ele_name.FIRST;
2164 WHILE l_idx IS NOT NULL
2165 LOOP
2166
2167 OPEN csr_get_ele_info(l_ele_name(l_idx));
2168 LOOP
2169 FETCH csr_get_ele_info INTO l_element_type_id,l_ele_obj_ver_number;
2170 EXIT WHEN csr_get_ele_info%NOTFOUND;
2171
2172 pay_shadow_element_api.update_shadow_element
2173 (p_validate => false
2174 ,p_effective_date => p_ele_eff_start_date
2175 ,p_element_type_id => l_element_type_id
2176 ,p_element_name => l_ele_new_name(l_idx)
2177 ,p_relative_processing_priority => l_ele_pp(l_idx)
2178 ,p_object_version_number => l_ele_obj_ver_number
2179 );
2180
2181 END LOOP;
2182 CLOSE csr_get_ele_info;
2183
2184 l_idx := l_ele_name.NEXT(l_idx);
2185
2186 END LOOP; --
2187
2188 -- Update shadow structure for Balances
2189
2190 l_proc_step := 70;
2191 IF g_debug THEN
2192 debug(l_proc_name, l_proc_step);
2193 END IF;
2194
2195
2196 l_ctr := 0;
2197 IF p_bnd1_ele_sub_name IS NOT NULL THEN
2198
2199 l_ctr := l_ctr + 1;
2200 l_bal_name(l_ctr) := p_abs_ele_name||' Band1 Pay Paid';
2201 l_bal_new_name(l_ctr) := p_abs_ele_name||' '||p_bnd1_ele_sub_name||' Band1 Pay Paid';
2202
2203 l_ctr := l_ctr + 1;
2204 l_bal_name(l_ctr) := p_abs_ele_name||' Band1 Hours Pay Entitlement';
2205 l_bal_new_name(l_ctr) := p_abs_ele_name||' '||p_bnd1_ele_sub_name||' Band1 Hours Pay Entitlement';
2206
2207 IF p_bnd2_ele_sub_name IS NOT NULL THEN
2208
2209 l_ctr := l_ctr + 1;
2210 l_bal_name(l_ctr) := p_abs_ele_name||' Band2 Pay Paid';
2211 l_bal_new_name(l_ctr) := p_abs_ele_name||' '||p_bnd2_ele_sub_name||' Band2 Pay Paid';
2212
2213 l_ctr := l_ctr + 1;
2214 l_bal_name(l_ctr) := p_abs_ele_name||' Band2 Hours Pay Entitlement';
2215 l_bal_new_name(l_ctr) := p_abs_ele_name||' '||p_bnd2_ele_sub_name||' Band2 Hours Pay Entitlement';
2216
2217 IF p_bnd3_ele_sub_name IS NOT NULL AND p_sch_cal_type <> 'DUALROLLING' THEN
2218
2219
2220 l_ctr := l_ctr + 1;
2221 l_bal_name(l_ctr) := p_abs_ele_name||' Band3 Pay Paid';
2222 l_bal_new_name(l_ctr) := p_abs_ele_name||' '||p_bnd3_ele_sub_name||' Band3 Pay Paid';
2223
2224 l_ctr := l_ctr + 1;
2225 l_bal_name(l_ctr) := p_abs_ele_name||' Band3 Hours Pay Entitlement';
2226 l_bal_new_name(l_ctr) := p_abs_ele_name||' '||p_bnd3_ele_sub_name||' Band3 Hours Pay Entitlement';
2227
2228 IF p_bnd4_ele_sub_name IS NOT NULL THEN
2229
2230 l_ctr := l_ctr + 1;
2231 l_bal_name(l_ctr) := p_abs_ele_name||' Band4 Pay Paid';
2232 l_bal_new_name(l_ctr) := p_abs_ele_name||' '||p_bnd4_ele_sub_name||' Band4 Pay Paid';
2233
2234 l_ctr := l_ctr + 1;
2235 l_bal_name(l_ctr) := p_abs_ele_name||' Band4 Hours Pay Entitlement';
2236 l_bal_new_name(l_ctr) := p_abs_ele_name||' '||p_bnd4_ele_sub_name||' Band4 Hours Pay Entitlement';
2237
2238 END IF; -- END IF of bnd4 sub name check ...
2239
2240 END IF; -- END IF of bnd3 sub name check ...
2241
2242 END IF; -- END IF of bnd2 sub name check ...
2243
2244 END IF; -- END IF of bnd1 sub name check ...
2245
2246 l_proc_step := 80;
2247 IF g_debug THEN
2248 debug(l_proc_name, l_proc_step);
2249 END IF;
2250
2251
2252 l_idx := l_bal_name.FIRST;
2253 WHILE l_idx IS NOT NULL
2254 LOOP
2255
2256 OPEN csr_get_bal_info(l_bal_name(i));
2257 LOOP
2258 FETCH csr_get_bal_info INTO l_balance_type_id,l_bal_obj_ver_number;
2259 EXIT WHEN csr_get_bal_info%NOTFOUND;
2260
2261 pay_sbt_upd.upd
2262 (p_effective_date => p_ele_eff_start_date
2263 ,p_balance_type_id => l_balance_type_id
2264 ,p_balance_name => l_bal_new_name(i)
2265 ,p_object_version_number => l_bal_obj_ver_number
2266 );
2267
2268 END LOOP;
2269 CLOSE csr_get_bal_info;
2270
2271 l_idx := l_bal_name.NEXT(l_idx);
2272
2273 END LOOP; -- l_idx := l_bal_name.FIRST;
2274
2275
2276 -------------------------------------------------------------------------
2277 --
2278 --
2279 l_proc_step := 90;
2280 IF g_debug THEN
2281 debug(l_proc_name, l_proc_step);
2282 END IF;
2283
2284 ---------------------------------------------------------------------------
2285 ---------------------------- Generate Core Objects ------------------------
2286 ---------------------------------------------------------------------------
2287
2288 pay_element_template_api.generate_part1
2289 (p_validate => false
2290 ,p_effective_date => p_ele_eff_start_date
2291 ,p_hr_only => false
2292 ,p_hr_to_payroll => false
2293 ,p_template_id => l_template_id);
2294 --
2295 l_proc_step := 100;
2296 IF g_debug THEN
2297 debug(l_proc_name, l_proc_step);
2298 END IF;
2299
2300 --
2301 pay_element_template_api.generate_part2
2302 (p_validate => false
2303 ,p_effective_date => p_ele_eff_start_date
2304 ,p_template_id => l_template_id);
2305 --
2306
2307 -- Update Main Elements with the Correction Element Information
2308
2309 l_proc_step := 110;
2310 IF g_debug THEN
2311 debug(l_proc_name, l_proc_step);
2312 END IF;
2313
2314
2315 -- Absence (Create)--lctr
2316 -- Pay (Create)
2317 -- Absence Retro --l_idx.FIRST
2318 -- Pay Retro Retro
2319 -- Band1 Pay
2320 -- Band1 Pay Retro
2321 -- Band2 Pay
2322 -- Band2 Pay Retro
2323 -- Band3 Pay
2324 -- Band3 Pay Retro
2325 -- Band4 Pay
2326 -- Band4 Pay Retro
2327 -- Minimum Pay
2328 -- Minimum Pay Retro
2329
2330 l_ctr := 0;
2331
2332 --1
2333 l_ctr := l_ctr + 1; --1 -- create manual entry as it does not exist in source array
2334 l_main_ele_name(l_ctr) := p_abs_ele_name || ' OSP '||l_days_hours||'Absence';
2335 l_main_eei_info19(l_ctr) := 'Absence Info';
2336
2337 --create main and retro entries at the same index
2338
2339 l_idx := l_ele_new_name.FIRST;
2340 l_retro_ele_name(l_ctr) := l_ele_new_name(l_idx); -- create from source array
2341 l_retro_eei_info19(l_ctr) := 'Absence Correction Info';
2342
2343
2344 --2
2345 l_ctr := l_ctr + 1; -- increment l_ctr after each pair
2346
2347 --create manual entry as it does not exist in source array
2348 l_main_ele_name(l_ctr) := p_abs_ele_name || ' OSP '||l_days_hours||'Pay';
2349 l_main_eei_info19(l_ctr) := 'Pay Info';
2350
2351 l_idx := l_ele_new_name.NEXT(l_idx); -- next in source
2352 l_retro_ele_name(l_ctr) := l_ele_new_name(l_idx); -- copy from source
2353 l_retro_eei_info19(l_ctr) := 'Pay Correction Info';
2354
2355
2356 --3
2357 l_ctr := l_ctr + 1;
2358
2359 l_idx := l_ele_new_name.NEXT(l_idx);
2360 l_main_ele_name(l_ctr) := l_ele_new_name(l_idx);
2361 l_main_eei_info19(l_ctr) := 'Band1 Info';
2362
2363 l_idx := l_ele_new_name.NEXT(l_idx);
2364 l_retro_ele_name(l_ctr) := l_ele_new_name(l_idx);
2365 l_retro_eei_info19(l_ctr) := 'Band1 Correction Info';
2366
2367
2368 --4
2369 l_ctr := l_ctr + 1;
2370
2371 l_idx := l_ele_new_name.NEXT(l_idx);
2372 l_main_ele_name(l_ctr) := l_ele_new_name(l_idx);
2373 l_main_eei_info19(l_ctr) := 'Band2 Info';
2374
2375 l_idx := l_ele_new_name.NEXT(l_idx);
2376 l_retro_ele_name(l_ctr) := l_ele_new_name(l_idx);
2377 l_retro_eei_info19(l_ctr) := 'Band2 Correction Info';
2378
2379
2380 IF p_sch_cal_type <> 'DUALROLLING'
2381 THEN
2382
2383 --5
2384 l_ctr := l_ctr + 1;
2385
2386 l_idx := l_ele_new_name.NEXT(l_idx);
2387 l_main_ele_name(l_ctr) := l_ele_new_name(l_idx);
2388 l_main_eei_info19(l_ctr) := 'Band3 Info';
2389
2390 l_idx := l_ele_new_name.NEXT(l_idx);
2391 l_retro_ele_name(l_ctr) := l_ele_new_name(l_idx);
2392 l_retro_eei_info19(l_ctr) := 'Band3 Correction Info';
2393
2394 --6
2395 l_ctr := l_ctr + 1;
2396
2397 l_idx := l_ele_new_name.NEXT(l_idx);
2398 l_main_ele_name(l_ctr) := l_ele_new_name(l_idx);
2399 l_main_eei_info19(l_ctr) := 'Band4 Info';
2400
2401 l_idx := l_ele_new_name.NEXT(l_idx);
2402 l_retro_ele_name(l_ctr) := l_ele_new_name(l_idx);
2403 l_retro_eei_info19(l_ctr) := 'Band4 Correction Info';
2404
2405 END IF; -- IF p_sch_cal_type <> 'DUALROLLING'
2406
2407 -- Added the IF check, as this is created only for DAYS
2408 IF l_days_hours IS NULL
2409 THEN
2410 --7 or 5
2411 l_ctr := l_ctr + 1;
2412
2413 l_idx := l_ele_new_name.NEXT(l_idx);
2414 l_main_ele_name(l_ctr) := l_ele_new_name(l_idx);
2415 l_main_eei_info19(l_ctr) := 'Minimum Pay Info';
2416
2417
2418 l_idx := l_ele_new_name.NEXT(l_idx);
2419 l_retro_ele_name(l_ctr) := l_ele_new_name(l_idx);
2420 l_retro_eei_info19(l_ctr) := 'Minimum Pay Correction Info';
2421 END IF ;
2422
2423 l_idx := l_main_ele_name.FIRST;
2424 WHILE l_idx IS NOT NULL
2425 LOOP
2426
2427 update_ele_retro_info
2428 (p_main_ele_name => l_main_ele_name(l_idx)
2429 ,p_retro_ele_name => l_retro_ele_name(l_idx)
2430 );
2431
2432 l_idx := l_main_ele_name.NEXT(l_idx);
2433
2434 END LOOP; -- l_idx := l_main_ele_name.FIRST;
2435
2436
2437 -- 5 because I'm testing only for dual rolling so 3 and 4 won't exist
2438 -- l_ctr := l_ctr + 1;
2439 -- temporarily after loop as min pay does't have a retro twin yet
2440 -- l_main_ele_name(l_ctr) := l_ele_new_name(l_ele_new_name.LAST); --it is the last to be added
2441 -- l_main_eei_info19(l_ctr) := 'Minimum Pay Info';
2442 -- temporarily after loop as min pay does't have a retro twin yet
2443
2444 -- Update the pay component rate type input value for base element
2445
2446 IF p_pay_src_pay_component IS NOT NULL THEN
2447
2448 --
2449 l_proc_step := 120;
2450 IF g_debug THEN
2451 debug(l_proc_name, l_proc_step);
2452 END IF;
2453
2454 --
2455 update_ipval_defval (p_ele_name => l_main_ele_name(l_main_ele_name.FIRST)
2456 ,p_ip_name => 'Pay Component Rate Type'
2457 ,p_def_value => p_pay_src_pay_component
2458 );
2459
2460 END IF; -- End of of pay src comp not null check ...
2461
2462 l_proc_step := 130;
2463 IF g_debug THEN
2464 debug(l_proc_name, l_proc_step);
2465 END IF;
2466
2467
2468 l_base_element_type_id := get_object_id ('ELE', l_main_ele_name(l_main_ele_name.FIRST));
2469
2470 l_proc_step := 140;
2471 IF g_debug THEN
2472 debug(l_proc_name, l_proc_step);
2473 END IF;
2474
2475 IF p_abs_ent_sick_leaves IS NULL THEN
2476
2477 -- Create UDT for Sickness Absence Entitlements
2478
2479 l_udt_type.user_table_name := l_base_name ||
2480 '_SICKNESS_ABSENCE_ENTITLEMENTS';
2481 l_udt_type.range_or_match := 'R'; -- Range
2482 l_udt_type.user_key_units := 'N';
2483 l_udt_type.user_row_title := NULL;
2484
2485 -- columns
2486
2487 l_udt_cols.DELETE;
2488
2489 -- Get the column names from the Lookup Type 'PQP_GAP_ENTITLED_BANDS'
2490
2491 l_proc_step := 145;
2492 IF g_debug THEN
2493 debug(l_proc_name, l_proc_step);
2494 END IF;
2495
2496
2497 get_udt_col_info (p_lookup_type => 'PQP_GAP_ENTITLEMENT_BANDS'
2498 ,p_lookup_code => 'BAND%'
2499 ,p_formula_id => NULL
2500 ,p_business_group_id => p_bg_id
2501 ,p_legislation_code => NULL
2502 ,p_udt_cols => l_udt_cols
2503 );
2504
2505
2506 -- rows
2507
2508 l_udt_rows.DELETE;
2509
2510 -- Get the row names from the Lookup Type 'PQP_GAP_ENTITLEMENT_ROWS'
2511 -- and GB_GAP_PERCENTAGE_ROW lookup code
2512
2513 l_proc_step := 146;
2514 IF g_debug THEN
2515 debug(l_proc_name, l_proc_step);
2516 END IF;
2517
2518
2519
2520 l_display_sequence := 1;
2521 get_udt_row_info (p_lookup_type => 'PQP_GAP_ENTITLEMENT_ROWS'
2522 ,p_lookup_code => 'GB_GAP_PERCENTAGE_ROW'
2523 ,p_udt_type => 'R'
2524 ,p_display_sequence => l_display_sequence
2525 ,p_business_group_id => NULL
2526 ,p_legislation_code => 'GB'
2527 ,p_udt_rows => l_udt_rows
2528 );
2529
2530 -- Get the row names from the Lookup Type 'PQP_GAP_ENTITLEMENT_ROWS'
2531 -- and GB_OSP% lookup code
2532
2533 l_proc_step := 147;
2534 IF g_debug THEN
2535 debug(l_proc_name, l_proc_step);
2536 END IF;
2537
2538 get_udt_row_info
2539 (p_lookup_type => 'PQP_GAP_ENTITLEMENT_ROWS'
2540 ,p_lookup_code => 'GB_OSP%'
2541 ,p_udt_type => 'R'
2542 ,p_display_sequence => l_display_sequence
2543 ,p_business_group_id => NULL
2544 ,p_legislation_code => 'GB'
2545 ,p_udt_rows => l_udt_rows
2546 );
2547
2548 IF p_sch_cal_type = 'DUALROLLING' THEN
2549 -- insert a LOS range of 0 to 999999
2550 l_idx := l_udt_rows.LAST;
2551 l_udt_rows(l_idx+1).row_low_range_or_name := '0';
2552 l_udt_rows(l_idx+1).row_high_range := '999999';
2553 l_udt_rows(l_idx+1).business_group_id := p_bg_id;
2554 l_udt_rows(l_idx+1).legislation_code := NULL;
2555 l_udt_rows(l_idx+1).display_sequence := l_udt_rows(l_idx).display_sequence+1;
2556 END IF; -- IF p_sch_cal_type = 'DUALROLLING' THEN
2557
2558 IF p_scheme_type = 'LOCALGOVT' THEN
2559 -- insert a LOS range of 0 to 999999
2560 l_idx := l_udt_rows.LAST;
2561 l_udt_rows(l_idx+1).row_low_range_or_name := '0';
2562 l_udt_rows(l_idx+1).row_high_range := '3';
2563 l_udt_rows(l_idx+1).business_group_id := p_bg_id;
2564 l_udt_rows(l_idx+1).legislation_code := NULL;
2565 l_udt_rows(l_idx+1).display_sequence := l_udt_rows(l_idx).display_sequence+1;
2566
2567 l_idx := l_udt_rows.LAST;
2568 l_udt_rows(l_idx+1).row_low_range_or_name := '4';
2569 l_udt_rows(l_idx+1).row_high_range := '11';
2570 l_udt_rows(l_idx+1).business_group_id := p_bg_id;
2571 l_udt_rows(l_idx+1).legislation_code := NULL;
2572 l_udt_rows(l_idx+1).display_sequence := l_udt_rows(l_idx).display_sequence+1;
2573
2574 l_idx := l_udt_rows.LAST;
2575 l_udt_rows(l_idx+1).row_low_range_or_name := '12';
2576 l_udt_rows(l_idx+1).row_high_range := '23';
2577 l_udt_rows(l_idx+1).business_group_id := p_bg_id;
2578 l_udt_rows(l_idx+1).legislation_code := NULL;
2579 l_udt_rows(l_idx+1).display_sequence := l_udt_rows(l_idx).display_sequence+1;
2580
2581 l_idx := l_udt_rows.LAST;
2582 l_udt_rows(l_idx+1).row_low_range_or_name := '24';
2583 l_udt_rows(l_idx+1).row_high_range := '35';
2584 l_udt_rows(l_idx+1).business_group_id := p_bg_id;
2585 l_udt_rows(l_idx+1).legislation_code := NULL;
2586 l_udt_rows(l_idx+1).display_sequence := l_udt_rows(l_idx).display_sequence+1;
2587
2588 l_idx := l_udt_rows.LAST;
2589 l_udt_rows(l_idx+1).row_low_range_or_name := '36';
2590 l_udt_rows(l_idx+1).row_high_range := '59';
2591 l_udt_rows(l_idx+1).business_group_id := p_bg_id;
2592 l_udt_rows(l_idx+1).legislation_code := NULL;
2593 l_udt_rows(l_idx+1).display_sequence := l_udt_rows(l_idx).display_sequence+1;
2594
2595 l_idx := l_udt_rows.LAST;
2596 l_udt_rows(l_idx+1).row_low_range_or_name := '60';
2597 l_udt_rows(l_idx+1).row_high_range := '999999';
2598 l_udt_rows(l_idx+1).business_group_id := p_bg_id;
2599 l_udt_rows(l_idx+1).legislation_code := NULL;
2600 l_udt_rows(l_idx+1).display_sequence := l_udt_rows(l_idx).display_sequence+1;
2601
2602 END IF; -- IF p_scheme_type = ' 'LOCALGOVT' THEN
2603
2604
2605
2606
2607 /*
2608 -- CS Table
2609 -- Band1 Band2
2610 -- -999999 -999999 100 50
2611 -- 0 999999 182 183
2612
2613 */
2614
2615 -- l_udt_rows(1).row_low_range_or_name := '-1';
2616 -- l_udt_rows(1).display_sequence := 1;
2617 -- l_udt_rows(1).row_high_range := '-1';
2618 -- l_udt_rows(1).business_group_id := NULL;
2619 -- l_udt_rows(1).legislation_code := 'GB';
2620
2621 l_eei_information9 := fnd_number.number_to_canonical
2622 (create_udt (p_udt_type => l_udt_type
2623 ,p_udt_cols => l_udt_cols
2624 ,p_udt_rows => l_udt_rows
2625 )
2626 );
2627
2628
2629 IF p_sch_cal_type = 'DUALROLLING' THEN
2630
2631 --Insert four column instances
2632 --Two for row -99999 -99999 and columns Band1 and Band2 values 100 and 50 respectively
2633 --Two for row 0 99999 and columsn Band1 and Band2 values 182 and 183 respectively
2634 /*
2635 APPS@hrukps:SQL>desc pay_user_column_instances_pkg
2636 PROCEDURE DELETE_ROW
2637 Argument Name Type In/Out Default?
2638 ------------------------------ ----------------------- ------ --------
2639 P_ROWID VARCHAR2 IN
2640 PROCEDURE INSERT_ROW
2641 Argument Name Type In/Out Default?
2642 ------------------------------ ----------------------- ------ --------
2643 P_ROWID VARCHAR2 IN/OUT
2644 P_USER_COLUMN_INSTANCE_ID NUMBER IN/OUT
2645 P_EFFECTIVE_START_DATE DATE IN
2646 P_EFFECTIVE_END_DATE DATE IN
2647 P_USER_ROW_ID NUMBER IN
2648 P_USER_COLUMN_ID NUMBER IN
2649 P_BUSINESS_GROUP_ID NUMBER IN
2650 P_LEGISLATION_CODE VARCHAR2 IN
2651 P_LEGISLATION_SUBGROUP VARCHAR2 IN
2652 P_VALUE VARCHAR2 IN
2653 */
2654
2655 INSERT INTO pay_user_column_instances_f
2656 (user_column_instance_id
2657 ,effective_start_date
2658 ,effective_end_date
2659 ,user_row_id
2660 ,user_column_id
2661 ,business_group_id
2662 ,legislation_code
2663 ,legislation_subgroup
2664 ,value
2665 )
2666 SELECT
2667 pay_user_column_instances_s.NEXTVAL
2668 ,urws.effective_start_date
2669 ,urws.effective_end_date
2670 ,urws.user_row_id
2671 ,ucol.user_column_id
2672 ,p_bg_id
2673 ,NULL
2674 ,NULL
2675 ,'100' -- -999999 -999999 Band1
2676 FROM pay_user_columns ucol
2677 ,pay_user_rows_f urws
2678 WHERE ucol.user_table_id = fnd_number.canonical_to_number(l_eei_information9)
2679 AND ucol.user_column_name = 'Band1' -- bad bad refer 'PQP_GAP_ENTITLEMENT_BANDS'.'BAND1'
2680 AND urws.user_table_id = ucol.user_table_id
2681 AND urws.row_low_range_or_name = '-999999' -- bad bad refer lookup code 'PQP_GAP_ENTITLEMENT_ROWS'.'GB_GAP_PERCENTAGE_ROW'
2682 AND SYSDATE BETWEEN urws.effective_start_date
2683 AND urws.effective_end_date;
2684
2685
2686 INSERT INTO pay_user_column_instances_f
2687 (user_column_instance_id
2688 ,effective_start_date
2689 ,effective_end_date
2690 ,user_row_id
2691 ,user_column_id
2692 ,business_group_id
2693 ,legislation_code
2694 ,legislation_subgroup
2695 ,value
2696 )
2697 SELECT
2698 pay_user_column_instances_s.NEXTVAL
2699 ,urws.effective_start_date
2700 ,urws.effective_end_date
2701 ,urws.user_row_id
2702 ,ucol.user_column_id
2703 ,p_bg_id
2704 ,NULL
2705 ,NULL
2706 ,'50' -- -999999 -999999 Band2
2707 FROM pay_user_columns ucol
2708 ,pay_user_rows_f urws
2709 WHERE ucol.user_table_id = fnd_number.canonical_to_number(l_eei_information9)
2710 AND ucol.user_column_name = 'Band2' -- bad bad refer 'PQP_GAP_ENTITLEMENT_BANDS'.'BAND1'
2711 AND urws.user_table_id = ucol.user_table_id
2712 AND urws.row_low_range_or_name = '-999999' -- bad bad refer lookup code 'PQP_GAP_ENTITLEMENT_ROWS'.'GB_GAP_PERCENTAGE_ROW'
2713 AND SYSDATE BETWEEN urws.effective_start_date
2714 AND urws.effective_end_date;
2715
2716
2717
2718 INSERT INTO pay_user_column_instances_f
2719 (user_column_instance_id
2720 ,effective_start_date
2721 ,effective_end_date
2722 ,user_row_id
2723 ,user_column_id
2724 ,business_group_id
2725 ,legislation_code
2726 ,legislation_subgroup
2727 ,value
2728 )
2729 SELECT
2730 pay_user_column_instances_s.NEXTVAL
2731 ,urws.effective_start_date
2732 ,urws.effective_end_date
2733 ,urws.user_row_id
2734 ,ucol.user_column_id
2735 ,p_bg_id
2736 ,NULL
2737 ,NULL
2738 ,'182' -- 0 999999 Band1
2739 FROM pay_user_columns ucol
2740 ,pay_user_rows_f urws
2741 WHERE ucol.user_table_id = fnd_number.canonical_to_number(l_eei_information9)
2742 AND ucol.user_column_name = 'Band1' -- bad bad refer 'PQP_GAP_ENTITLEMENT_BANDS'.'BAND1'
2743 AND urws.user_table_id = ucol.user_table_id
2744 AND urws.row_low_range_or_name = '0' -- bad bad refer lookup code 'PQP_GAP_ENTITLEMENT_ROWS'.'GB_GAP_PERCENTAGE_ROW'
2745 AND SYSDATE BETWEEN urws.effective_start_date
2746 AND urws.effective_end_date;
2747
2748
2749
2750 INSERT INTO pay_user_column_instances_f
2751 (user_column_instance_id
2752 ,effective_start_date
2753 ,effective_end_date
2754 ,user_row_id
2755 ,user_column_id
2756 ,business_group_id
2757 ,legislation_code
2758 ,legislation_subgroup
2759 ,value
2760 )
2761 SELECT
2762 pay_user_column_instances_s.NEXTVAL
2763 ,urws.effective_start_date
2764 ,urws.effective_end_date
2765 ,urws.user_row_id
2766 ,ucol.user_column_id
2767 ,p_bg_id
2768 ,NULL
2769 ,NULL
2770 ,'183' -- 0 999999 Band2
2771 FROM pay_user_columns ucol
2772 ,pay_user_rows_f urws
2773 WHERE ucol.user_table_id = fnd_number.canonical_to_number(l_eei_information9)
2774 AND ucol.user_column_name = 'Band2' -- bad bad refer 'PQP_GAP_ENTITLEMENT_BANDS'.'BAND1'
2775 AND urws.user_table_id = ucol.user_table_id
2776 AND urws.row_low_range_or_name = '0' -- bad bad refer a variable set in prev section when creating this row
2777 AND SYSDATE BETWEEN urws.effective_start_date
2778 AND urws.effective_end_date;
2779
2780
2781 END IF; --IF p_sch_cal_type = 'DUALROLLING' THEN create the entitlement values also
2782
2783
2784 IF p_scheme_type = 'LOCALGOVT' THEN
2785
2786 --Insert four column instances
2787 --Two for row -99999 -99999 and columns Band1 and Band2 values 100 and 50 respectively
2788 --Two for row 0 99999 and columsn Band1 and Band2 values 182 and 183 respectively
2789 /*
2790 APPS@hrukps:SQL>desc pay_user_column_instances_pkg
2791 PROCEDURE DELETE_ROW
2792 Argument Name Type In/Out Default?
2793 ------------------------------ ----------------------- ------ --------
2794 P_ROWID VARCHAR2 IN
2795 PROCEDURE INSERT_ROW
2796 Argument Name Type In/Out Default?
2797 ------------------------------ ----------------------- ------ --------
2798 P_ROWID VARCHAR2 IN/OUT
2799 P_USER_COLUMN_INSTANCE_ID NUMBER IN/OUT
2800 P_EFFECTIVE_START_DATE DATE IN
2801 P_EFFECTIVE_END_DATE DATE IN
2802 P_USER_ROW_ID NUMBER IN
2803 P_USER_COLUMN_ID NUMBER IN
2804 P_BUSINESS_GROUP_ID NUMBER IN
2805 P_LEGISLATION_CODE VARCHAR2 IN
2806 P_LEGISLATION_SUBGROUP VARCHAR2 IN
2807 P_VALUE VARCHAR2 IN
2808 */
2809 create_udt_entry(
2810 p_bg_id =>p_bg_id
2811 ,p_band =>'Band1'
2812 ,p_entit =>'100'
2813 ,p_lower =>'-999999'
2814 ,p_user_tbl_id => fnd_number.canonical_to_number(l_eei_information9)
2815 );
2816
2817 create_udt_entry(
2818 p_bg_id =>p_bg_id
2819 ,p_band =>'Band2'
2820 ,p_entit =>'50'
2821 ,p_lower =>'-999999'
2822 ,p_user_tbl_id => fnd_number.canonical_to_number(l_eei_information9)
2823 );
2824
2825 create_udt_entry(
2826 p_bg_id =>p_bg_id
2827 ,p_band =>'Band1'
2828 ,p_entit =>'26'
2829 ,p_lower =>'0'
2830 ,p_user_tbl_id => fnd_number.canonical_to_number(l_eei_information9)
2831 );
2832
2833
2834 create_udt_entry(
2835 p_bg_id =>p_bg_id
2836 ,p_band =>'Band1'
2837 ,p_entit =>'26'
2838 ,p_lower =>'4'
2839 ,p_user_tbl_id => fnd_number.canonical_to_number(l_eei_information9)
2840 );
2841
2842 create_udt_entry(
2843 p_bg_id =>p_bg_id
2844 ,p_band =>'Band2'
2845 ,p_entit =>'52'
2846 ,p_lower =>'4'
2847 ,p_user_tbl_id => fnd_number.canonical_to_number(l_eei_information9)
2848 );
2849
2850 create_udt_entry(
2851 p_bg_id =>p_bg_id
2852 ,p_band =>'Band1'
2853 ,p_entit =>'52'
2854 ,p_lower =>'12'
2855 ,p_user_tbl_id => fnd_number.canonical_to_number(l_eei_information9)
2856 );
2857
2858 create_udt_entry(
2859 p_bg_id =>p_bg_id
2860 ,p_band =>'Band2'
2861 ,p_entit =>'52'
2862 ,p_lower =>'12'
2863 ,p_user_tbl_id => fnd_number.canonical_to_number(l_eei_information9)
2864 );
2865
2866 create_udt_entry(
2867 p_bg_id =>p_bg_id
2868 ,p_band =>'Band1'
2869 ,p_entit =>'104'
2870 ,p_lower =>'24'
2871 ,p_user_tbl_id => fnd_number.canonical_to_number(l_eei_information9)
2872 );
2873
2874 create_udt_entry(
2875 p_bg_id =>p_bg_id
2876 ,p_band =>'Band2'
2877 ,p_entit =>'104'
2878 ,p_lower =>'24'
2879 ,p_user_tbl_id => fnd_number.canonical_to_number(l_eei_information9)
2880 );
2881
2882 create_udt_entry(
2883 p_bg_id =>p_bg_id
2884 ,p_band =>'Band1'
2885 ,p_entit =>'130'
2886 ,p_lower =>'36'
2887 ,p_user_tbl_id => fnd_number.canonical_to_number(l_eei_information9)
2888 );
2889
2890 create_udt_entry(
2891 p_bg_id =>p_bg_id
2892 ,p_band =>'Band2'
2893 ,p_entit =>'130'
2894 ,p_lower =>'36'
2895 ,p_user_tbl_id => fnd_number.canonical_to_number(l_eei_information9)
2896 );
2897
2898 create_udt_entry(
2899 p_bg_id =>p_bg_id
2900 ,p_band =>'Band1'
2901 ,p_entit =>'156'
2902 ,p_lower =>'60'
2903 ,p_user_tbl_id => fnd_number.canonical_to_number(l_eei_information9)
2904 );
2905
2906 create_udt_entry(
2907 p_bg_id =>p_bg_id
2908 ,p_band =>'Band2'
2909 ,p_entit =>'156'
2910 ,p_lower =>'60'
2911 ,p_user_tbl_id => fnd_number.canonical_to_number(l_eei_information9)
2912 );
2913
2914
2915
2916
2917 END IF; -- IF p_scheme_type = 'LOCALGOVT' THEN
2918
2919
2920
2921 ELSE
2922
2923 -- Store the user_table_id for this udt name
2924 l_eei_information9 := fnd_number.number_to_canonical
2925 (p_abs_ent_sick_leaves);
2926
2927 END IF; -- End if of p_abs_ent_sick_leaves null check ...
2928
2929 l_proc_step := 150;
2930 IF g_debug THEN
2931 debug(l_proc_name, l_proc_step);
2932 END IF;
2933
2934
2935 l_eei_information10 := NULL;
2936 IF NVL(p_abs_ent_holidays, 0) <> -1 THEN
2937
2938 IF p_abs_ent_holidays IS NOT NULL THEN
2939
2940 -- Store the user_table_id for this udt name
2941 l_eei_information10 := fnd_number.number_to_canonical
2942 (p_abs_ent_holidays);
2943
2944 ELSE -- create the udt
2945
2946 -- Create UDT for Calendar
2947
2948 l_udt_type.user_table_name := l_base_name ||'_CALENDAR';
2949 l_udt_type.range_or_match := 'M'; -- Match
2950 l_udt_type.user_key_units := 'T';
2951 l_udt_type.user_row_title := NULL;
2952
2953 -- columns
2954
2955 l_udt_cols.DELETE;
2956
2957 -- Get the column names from the Lookup Type 'PQP_GB_OSP_CALENDAR_RULES'
2958
2959 l_proc_step := 155;
2960 IF g_debug THEN
2961 debug(l_proc_name, l_proc_step);
2962 END IF;
2963
2964
2965 get_udt_col_info (p_lookup_type => 'PQP_GB_OSP_CALENDAR_RULES'
2966 ,p_lookup_code => '%'
2967 ,p_formula_id => NULL
2968 ,p_business_group_id => NULL
2969 ,p_legislation_code => 'GB'
2970 ,p_udt_cols => l_udt_cols
2971 );
2972
2973 l_udt_rows.DELETE;
2974
2975
2976 l_eei_information10 := fnd_number.number_to_canonical(
2977 create_udt (p_udt_type => l_udt_type
2978 ,p_udt_cols => l_udt_cols
2979 ,p_udt_rows => l_udt_rows
2980 ) );
2981
2982
2983 END IF; -- End if of p_abs_ent_holidays null check ...
2984
2985 END IF; -- End if of ent holidays <> -1 check...
2986
2987 --
2988 l_proc_step := 160;
2989 IF g_debug THEN
2990 debug(l_proc_name, l_proc_step);
2991 END IF;
2992
2993 --
2994 l_eei_information18 := p_abs_type_lookup_type;
2995
2996 IF p_abs_type_lookup_type IS NULL THEN
2997
2998 -- Create Lookup dynamically
2999 l_lookup_type := l_base_name || '_LIST';
3000 l_lookup_meaning := l_base_name || '_ABSENCE_ATTENDANCE_TYPES';
3001 create_lookup (p_lookup_type => l_lookup_type
3002 ,p_lookup_meaning => l_lookup_meaning
3003 ,p_lookup_values => p_abs_type_lookup_value
3004 );
3005 l_eei_information18 := l_lookup_type;
3006
3007 -- Create GAP lookup dynamically
3008 l_lookup_type := 'PQP_GAP_ABSENCE_TYPES_LIST';
3009 l_lookup_meaning := l_lookup_type;
3010 create_gap_lookup (p_security_group_id => p_security_group_id
3011 ,p_ele_eff_start_date => p_ele_eff_start_date
3012 ,p_lookup_type => l_lookup_type
3013 ,p_lookup_meaning => l_lookup_meaning
3014 ,p_lookup_values => p_abs_type_lookup_value
3015 );
3016
3017 END IF; -- End if of abs type lookup type not null ...
3018
3019
3020 -- LG Create a Lookup with Plan Types
3021 -- that are used to extend the Rolling Period.
3022
3023 IF p_plan_type_lookup_type IS NULL THEN
3024
3025 -- Create Lookup dynamically
3026 l_lookup_type := l_base_name || '_PLTP';
3027 l_lookup_meaning := l_lookup_type ;
3028 create_plan_lookup (p_lookup_type => l_lookup_type
3029 ,p_lookup_meaning => l_lookup_meaning
3030 ,p_lookup_values => p_plan_type_lookup_value
3031 ) ;
3032 l_eei_information24 := l_lookup_type;
3033
3034 END IF; -- End if of abs type lookup type not null ...
3035
3036
3037
3038
3039 l_idx := l_main_ele_name.FIRST;
3040 WHILE l_idx IS NOT NULL
3041 LOOP
3042
3043 l_proc_step := 170;
3044
3045 IF g_debug THEN
3046 debug(l_proc_name, l_proc_step);
3047 debug('ELE:'||l_main_ele_name(l_idx));
3048 debug('l_eei_information22:'||l_eei_information22);
3049 debug('l_eei_information23:'||l_eei_information23);
3050 END IF;
3051
3052 l_eei_element_type_id := get_object_id ('ELE', l_main_ele_name(l_idx));
3053
3054 -- Create a row in pay_element_extra_info with all the element information
3055 pay_element_extra_info_api.create_element_extra_info
3056 (p_element_type_id => l_eei_element_type_id
3057 ,p_information_type => 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
3058 ,P_EEI_INFORMATION_CATEGORY => 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
3059 ,p_eei_information1 => fnd_number.number_to_canonical(p_plan_id)
3060 ,p_eei_information2 => p_plan_description
3061 ,p_eei_information3 => p_sch_cal_type
3062 ,p_eei_information4 => p_sch_cal_duration
3063 ,p_eei_information5 => p_sch_cal_uom
3064 ,p_eei_information6 => fnd_date.date_to_canonical(p_sch_cal_start_date)
3065 ,p_eei_information7 => fnd_date.date_to_canonical(p_sch_cal_end_date)
3066 ,p_eei_information8 => p_abs_days
3067 ,p_eei_information9 => l_eei_information9
3068 ,p_eei_information10 => l_eei_information10
3069 ,p_eei_information11 => p_abs_daily_rate_calc_method
3070 ,p_eei_information12 => p_abs_daily_rate_calc_period
3071 ,p_eei_information13 => p_abs_daily_rate_calc_divisor
3072 ,p_eei_information14 => p_ft_round_config
3073 ,p_eei_information15 => p_pay_src_pay_component
3074 ,p_eei_information16 => p_abs_primary_yn
3075 ,p_eei_information17 => p_abs_working_pattern
3076 ,p_eei_information18 => l_eei_information18
3077 ,p_eei_information19 => l_main_eei_info19(l_idx)
3078 ,p_eei_information20 => l_eei_information20
3079 ,p_eei_information21 => l_eei_information21
3080 ,p_eei_information22 => l_eei_information22 -- LG
3081 ,p_eei_information23 => l_eei_information23 -- LG
3082 ,p_eei_information24 => l_eei_information24 -- LG
3083 ,p_eei_information25 => p_pt_round_config
3084 ,p_eei_information26 => p_abs_overlap_rule
3085 ,p_eei_information27 => l_eei_information27
3086 ,p_eei_information28 => l_eei_information28
3087 ,p_eei_information29 => l_eei_information29
3088 ,p_eei_information30 => l_eei_information30
3089 ,p_element_type_extra_info_id => l_eei_info_id
3090 ,p_object_version_number => l_ovn_eei
3091 );
3092
3093
3094 IF l_retro_ele_name.EXISTS(l_idx) THEN
3095 l_eei_element_type_id := get_object_id ('ELE', l_retro_ele_name(l_idx));
3096
3097 l_proc_step := 180;
3098 IF g_debug THEN
3099 debug(l_proc_name, l_proc_step);
3100 END IF;
3101
3102
3103 -- Create a row in pay_element_extra_info with all the element information
3104 pay_element_extra_info_api.create_element_extra_info
3105 (p_element_type_id => l_eei_element_type_id
3106 ,p_information_type => 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
3107 ,P_EEI_INFORMATION_CATEGORY => 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
3108 ,p_eei_information1 => fnd_number.number_to_canonical(p_plan_id)
3109 ,p_eei_information2 => p_plan_description
3110 ,p_eei_information3 => p_sch_cal_type
3111 ,p_eei_information4 => p_sch_cal_duration
3112 ,p_eei_information5 => p_sch_cal_uom
3113 ,p_eei_information6 => fnd_date.date_to_canonical(p_sch_cal_start_date)
3114 ,p_eei_information7 => fnd_date.date_to_canonical(p_sch_cal_end_date)
3115 ,p_eei_information8 => p_abs_days
3116 ,p_eei_information9 => l_eei_information9
3117 ,p_eei_information10 => l_eei_information10
3118 ,p_eei_information11 => p_abs_daily_rate_calc_method
3119 ,p_eei_information12 => p_abs_daily_rate_calc_period
3120 ,p_eei_information13 => p_abs_daily_rate_calc_divisor
3121 ,p_eei_information14 => p_ft_round_config
3122 ,p_eei_information15 => p_pay_src_pay_component
3123 ,p_eei_information16 => p_abs_primary_yn
3124 ,p_eei_information17 => p_abs_working_pattern
3125 ,p_eei_information18 => l_eei_information18
3126 ,p_eei_information19 => l_retro_eei_info19(l_idx)
3127 ,p_eei_information20 => l_eei_information20
3128 ,p_eei_information21 => l_eei_information21
3129 ,p_eei_information22 => l_eei_information22 -- LG
3130 ,p_eei_information23 => l_eei_information23 -- LG
3131 ,p_eei_information24 => l_eei_information24 -- LG
3132 ,p_eei_information25 => p_pt_round_config
3133 ,p_eei_information26 => p_abs_overlap_rule
3134 ,p_eei_information27 => l_eei_information27
3135 ,p_eei_information28 => l_eei_information28
3136 ,p_eei_information29 => l_eei_information29
3137 ,p_eei_information30 => l_eei_information30
3138 ,p_element_type_extra_info_id => l_eei_info_id
3139 ,p_object_version_number => l_ovn_eei
3140 );
3141
3142 END IF; -- if retro exists -- min pay testing only
3143
3144 l_idx := l_main_ele_name.NEXT(l_idx);
3145
3146
3147 END LOOP; --l_idx := l_main_ele_name.FIRST;
3148
3149
3150 -- Elements Links are created Here
3151 -- pqp_gb_omp_template.create_element_links (
3152 -- p_business_group_id => p_bg_id
3153 -- , p_effective_start_date => p_ele_eff_start_date
3154 -- , p_effective_end_date => p_ele_eff_end_date
3155 -- , p_legislation_code => 'GB'
3156 -- , p_base_name => p_abs_ele_name
3157 -- , p_abs_type => ' OSP '||l_days_hours
3158 -- ) ;
3159
3160 pqp_gb_omp_template.create_element_links
3161 (p_business_group_id => p_bg_id
3162 ,p_effective_start_date => p_ele_eff_start_date
3163 ,p_effective_end_date => p_ele_eff_end_date
3164 ,p_template_id => l_template_id
3165 ) ;
3166
3167 -- Elements Links are created Here
3168 -- creates values in pqp_configuration_modules
3169 -- ,pqp_configuration_types table.
3170 -- once the lct, ldt are finalized to ship these values this code
3171 -- will be replaced by a proper ldt.
3172
3173 create_config_data ;
3174
3175
3176 IF p_abs_primary_yn = 'Y' THEN
3177 automate_plan_setup
3178 (p_pl_id => p_plan_id
3179 ,p_business_group_id => p_bg_id
3180 ,p_element_type_id => l_base_element_type_id
3181 ,p_effective_date => p_ele_eff_start_date
3182 ,p_base_name => l_base_name
3183 );
3184 END IF;
3185
3186
3187 ELSE
3188
3189 hr_utility.set_message(8303, 'PQP_230535_GBORAPAY_NOT_FOUND');
3190 hr_utility.raise_error;
3191
3192
3193 END IF; -- IF chk_product_install('Oracle Payroll',g_template_leg_code))
3194
3195 debug_exit(l_proc_name);
3196
3197 RETURN l_base_element_type_id;
3198
3199 EXCEPTION
3200 WHEN OTHERS THEN
3201 clear_cache;
3202 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
3203 debug_others(l_proc_name,l_proc_step);
3204 IF g_debug THEN
3205 debug('Leaving: '||l_proc_name,-999);
3206 END IF;
3207 fnd_message.raise_error;
3208 ELSE
3209 RAISE;
3210 END IF;
3211 END create_user_template;
3212 --
3213 --
3214 --==========================================================================
3215 -- Deletion procedure
3216 --==========================================================================
3217 --
3218 PROCEDURE delete_user_template
3219 (p_plan_id in number
3220 ,p_business_group_id in number
3221 ,p_abs_ele_name in varchar2
3222 ,p_abs_ele_type_id in number
3223 ,p_abs_primary_yn in varchar2
3224 ,p_security_group_id in number
3225 ,p_effective_date in date
3226 ) IS
3227 --
3228 l_template_id NUMBER(9);
3229 l_proc_step NUMBER(20,10);
3230 l_proc_name varchar2(72) := g_package_name || 'delete_user_template';
3231 l_eei_info_id number;
3232 l_ovn_eei number;
3233 l_entudt_id pay_user_tables.user_table_id%TYPE;
3234 l_caludt_id pay_user_tables.user_table_id%TYPE;
3235 l_lookup_type fnd_lookup_types_vl.lookup_type%TYPE;
3236 -- to delete plan types lookup
3237 l_plan_type_lookup fnd_lookup_types_vl.lookup_type%TYPE;
3238
3239 l_lookup_code fnd_lookup_values_vl.lookup_code%TYPE;
3240 l_exists VARCHAR2(1);
3241 l_element_type_id pay_element_types_f.element_type_id%TYPE;
3242
3243 TYPE t_number IS TABLE OF NUMBER
3244 INDEX BY BINARY_INTEGER;
3245
3246 l_lookup_collection t_number;
3247 l_plan_type_lookup_collection t_number;
3248
3249
3250 -- Added For Hours
3251
3252 l_entitlements_uom VARCHAR2(1) ;
3253 l_daily_rate_uom pay_element_type_extra_info.eei_information13%TYPE ;
3254 l_days_hours VARCHAR2(10) ;
3255 l_template_name pay_element_templates.template_name%TYPE ;
3256
3257
3258 CURSOR csr_get_scheme_type(p_ele_type_id IN NUMBER) IS
3259 SELECT pee.eei_information8 entitlements_uom
3260 ,pee.eei_information11 daily_rate_uom
3261 FROM pay_element_type_extra_info pee
3262 WHERE element_type_id = p_ele_type_id
3263 AND information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO' ;
3264
3265 -- Added For Hours
3266
3267
3268 CURSOR csr_get_ele_type_id (c_template_id number)
3269 IS
3270 SELECT element_type_id
3271 FROM pay_template_core_objects pet
3272 ,pay_element_types_f petf
3273 WHERE pet.template_id = c_template_id
3274 AND petf.element_type_id = pet.core_object_id
3275 AND pet.core_object_type = 'ET';
3276
3277 CURSOR csr_get_eei_info (c_element_type_id number)
3278 IS
3279 SELECT element_type_extra_info_id
3280 ,fnd_number.canonical_to_number(eei_information9) entitlement_udt
3281 ,fnd_number.canonical_to_number(eei_information10) calendar_udt
3282 ,eei_information18 lookup_type
3283 ,eei_information24 plan_type_lookup
3284 FROM pay_element_type_extra_info petei
3285 WHERE element_type_id = c_element_type_id ;
3286
3287 CURSOR csr_chk_eei_for_entudt (c_udt_id number)
3288 IS
3289 SELECT 'X'
3290 FROM pay_element_type_extra_info
3291 WHERE eei_information1 <> fnd_number.number_to_canonical(p_plan_id)
3292 AND eei_information9 = fnd_number.number_to_canonical(c_udt_id)
3293 AND information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
3294 AND rownum = 1;
3295
3296 CURSOR csr_chk_eei_for_caludt (c_udt_id number)
3297 IS
3298 SELECT 'X'
3299 FROM pay_element_type_extra_info
3300 WHERE eei_information1 <> fnd_number.number_to_canonical(p_plan_id)
3301 AND eei_information10 = fnd_number.number_to_canonical(c_udt_id)
3302 AND information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
3303 AND rownum = 1;
3304
3305 CURSOR csr_chk_eei_for_lkt (c_lookup_type varchar2)
3306 IS
3307 SELECT 'X'
3308 FROM pay_element_type_extra_info
3309 WHERE eei_information1 <> fnd_number.number_to_canonical(p_plan_id)
3310 AND eei_information18 = c_lookup_type
3311 AND information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
3312 AND rownum = 1;
3313
3314
3315 CURSOR csr_chk_sec_ele (c_te_usrstr_id NUMBER
3316 ,p_template_name VARCHAR2
3317 ,p_days_hours VARCHAR2
3318 ) IS
3319 SELECT 'X'
3320 FROM pay_element_templates pets
3321 ,pay_shadow_element_types pset
3322 ,pay_template_core_objects ptco
3323 ,pay_element_type_extra_info peei
3324 WHERE pets.template_id <> c_te_usrstr_id
3325 -- For the given user structure
3326 AND pets.template_name = p_template_name -- 'PQP OSP'
3327 AND pets.template_type = 'U'
3328 AND pets.business_group_id = p_business_group_id
3329 AND pset.template_id = pets.template_id -- find the base element
3330 AND pset.element_name = pets.base_name || ' OSP '||p_days_hours||'Absence'
3331 AND ptco.template_id = pset.template_id -- For the base element
3332 AND ptco.shadow_object_id = pset.element_type_id -- find the core element
3333 AND ptco.core_object_type = 'ET'
3334 AND ptco.core_object_id = peei.element_type_id -- For the core element
3335 AND peei.eei_information1 = fnd_number.number_to_canonical(p_plan_id)
3336 AND peei.information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO';
3337 -- find the eei info
3338
3339 CURSOR csr_get_template_id (p_template_name IN VARCHAR2) is
3340 SELECT template_id
3341 FROM pay_element_templates
3342 WHERE base_name = p_abs_ele_name
3343 AND template_name = p_template_name --'PQP OSP'
3344 AND business_group_id = p_business_group_id
3345 AND template_type = 'U';
3346
3347 -- Cursor to check whether elements are attached to
3348 -- benefit standard rates
3349
3350 CURSOR csr_chk_ele_in_ben (c_element_type_id number)
3351 IS
3352 SELECT 'X'
3353 FROM ben_acty_base_rt_f
3354 WHERE pl_id = p_plan_id
3355 AND element_type_id = c_element_type_id
3356 AND business_group_id = p_business_group_id;
3357
3358
3359 -- Cursor to retrieve lookup code for a given
3360 -- lookup type
3361
3362 CURSOR csr_get_lookup_code (c_lookup_type varchar2)
3363 IS
3364 SELECT lookup_code
3365 FROM fnd_lookup_values_vl
3366 WHERE lookup_type = c_lookup_type
3367 AND security_group_id = p_security_group_id
3368 AND view_application_id = 3;
3369
3370 --
3371 --========================================================================
3372 -- PROCEDURE get_other_lookups
3373 --========================================================================
3374
3375 PROCEDURE get_other_lookups (p_business_group_id in number
3376 ,p_lookup_collection out nocopy t_number
3377 ,p_template_name IN VARCHAR2
3378 ,p_days_hours IN VARCHAR2
3379 )
3380 IS
3381
3382 -- The original query is split into 2 queries
3383 -- to avoid Merge joins and make use of Indexes.
3384 -- There is no effective date check on table pay_element_types_f
3385 -- as we are interested in data irrespective of date.
3386 -- Cursor to retrieve lookup type information
3387
3388 CURSOR csr_get_lookup_type(c_base_name in varchar2)
3389 IS
3390 SELECT DISTINCT(pete.eei_information18) lookup_type
3391 FROM pay_element_type_extra_info pete
3392 ,pay_element_types_f petf
3393 -- ,pay_element_templates pet
3394 WHERE pete.element_type_id = petf.element_type_id
3395 AND pete.information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
3396 AND pete.eei_information16 = 'Y'
3397 AND petf.element_name = c_base_name || ' OSP '||p_days_hours||'Absence'
3398 -- pet.base_name || ' OSP '||p_days_hours||'Absence'
3399 AND petf.business_group_id = p_business_group_id
3400 --AND pet.template_name = p_template_name -- 'PQP OSP'
3401 --AND pet.template_type = 'U'
3402 --AND pet.business_group_id = p_business_group_id;
3403 ;
3404
3405 CURSOR csr_template_names IS
3406 SELECT pet.base_name
3407 FROM pay_element_templates pet
3408 WHERE pet.template_name = p_template_name
3409 AND pet.template_type = 'U'
3410 AND pet.business_group_id = p_business_group_id ;
3411
3412 l_lookup_collection t_number;
3413 l_number NUMBER;
3414 l_lookup_code fnd_lookup_values_vl.lookup_code%TYPE;
3415 l_lookup_type fnd_lookup_types_vl.lookup_type%TYPE;
3416 l_proc_step NUMBER(20,10);
3417 l_proc_name VARCHAR2(72) := g_package_name || 'get_other_lookups';
3418 l_base_name pay_element_templates.base_name%TYPE ;
3419
3420 --
3421 BEGIN
3422
3423 --
3424 debug('Entering '||l_proc_name, 10);
3425
3426 -- get the template base names
3427 OPEN csr_template_names ;
3428 LOOP
3429 FETCH csr_template_names INTO l_base_name ;
3430 EXIT WHEN csr_template_names%NOTFOUND ;
3431
3432 -- Get the lookup type information
3433
3434 OPEN csr_get_lookup_type(c_base_name => l_base_name);
3435 LOOP
3436
3437 FETCH csr_get_lookup_type INTO l_lookup_type;
3438 EXIT WHEN csr_get_lookup_type%NOTFOUND;
3439
3440 -- Get the lookup code for this lookup type
3441
3442 l_proc_step := 20;
3443 IF g_debug THEN
3444 debug(l_proc_name, l_proc_step);
3445 END IF;
3446
3447
3448 OPEN csr_get_lookup_code(l_lookup_type);
3449 LOOP
3450
3451 FETCH csr_get_lookup_code INTO l_lookup_code;
3452 EXIT WHEN csr_get_lookup_code%NOTFOUND;
3453
3454 -- Check whether this lookup code is already added to
3455 -- the collection
3456
3457 l_number := fnd_number.canonical_to_number(l_lookup_code);
3458
3459 IF NOT l_lookup_collection.EXISTS(l_number) THEN
3460
3461 l_lookup_collection(l_number) := l_number;
3462
3463 END IF; -- End if of lookup collection exists check ...
3464
3465 END LOOP;
3466 CLOSE csr_get_lookup_code;
3467
3468 END LOOP;
3469 CLOSE csr_get_lookup_type;
3470 END LOOP ;
3471 CLOSE csr_template_names;
3472
3473 p_lookup_collection := l_lookup_collection;
3474
3475 debug('Leaving '||l_proc_name, 30);
3476
3477 -- Added by tmehra for nocopy changes Feb'03
3478
3479 EXCEPTION
3480 WHEN OTHERS THEN
3481 debug('Entering excep:'||l_proc_name, 35);
3482 p_lookup_collection.delete;
3483 raise;
3484
3485 --
3486 END get_other_lookups;
3487 --
3488
3489
3490 --
3491 --========================================================================
3492 -- PROCEDURE delete_lookup
3493 --========================================================================
3494
3495 PROCEDURE delete_lookup (p_lookup_type in varchar2
3496 ,p_security_group_id in number
3497 ,p_view_application_id in number
3498 ,p_lookup_collection in t_number
3499 ,p_delete_gap_entry in boolean default true )
3500 IS
3501
3502 --
3503
3504 CURSOR csr_get_lkt_info
3505 IS
3506 SELECT 'X'
3507 FROM fnd_lookup_types_vl
3508 WHERE lookup_type = p_lookup_type
3509 AND security_group_id = p_security_group_id
3510 AND view_application_id = p_view_application_id;
3511
3512 CURSOR csr_get_lkv_info
3513 IS
3514 SELECT lookup_code
3515 FROM fnd_lookup_values_vl
3516 WHERE lookup_type = p_lookup_type
3517 AND security_group_id = p_security_group_id
3518 AND view_application_id = p_view_application_id;
3519
3520 l_proc_step NUMBER(20,10);
3521 l_proc_name VARCHAR2(72) := g_package_name || 'delete_lookup';
3522 l_exists VARCHAR2(1);
3523 l_lookup_code fnd_lookup_values_vl.lookup_code%TYPE;
3524
3525 BEGIN
3526 --
3527 debug ('Entering '||l_proc_name, 10);
3528 --
3529
3530 debug('Security Group' || to_char(p_security_group_id),
3531 15);
3532 debug('Lookup Type' || p_lookup_type, 16);
3533
3534 OPEN csr_get_lkt_info;
3535 FETCH csr_get_lkt_info into l_exists;
3536
3537 IF csr_get_lkt_info%FOUND THEN
3538
3539 -- Get Lookup Value Info
3540 l_proc_step := 20;
3541 IF g_debug THEN
3542 debug(l_proc_name, l_proc_step);
3543 END IF;
3544
3545
3546 OPEN csr_get_lkv_info;
3547 LOOP
3548 FETCH csr_get_lkv_info INTO l_lookup_code;
3549 EXIT WHEN csr_get_lkv_info%NOTFOUND;
3550
3551 -- Check whether this lookup code has to be deleted
3552 -- from PQP_GAP_ABSENCE_TYPES_LIST lookup type
3553
3554 l_proc_step := 25;
3555 IF g_debug THEN
3556 debug(l_proc_name, l_proc_step);
3557 END IF;
3558
3559 -- Added p_delete_gap_entry cehck, to use the same function to delete
3560 -- any lookup.
3561 IF p_delete_gap_entry THEN
3562
3563 IF NOT p_lookup_collection.EXISTS(fnd_number.canonical_to_number(
3564 l_lookup_code)) THEN
3565 fnd_lookup_values_pkg.delete_row
3566 (x_lookup_type => 'PQP_GAP_ABSENCE_TYPES_LIST'
3567 ,x_security_group_id => p_security_group_id
3568 ,x_view_application_id => p_view_application_id
3569 ,x_lookup_code => l_lookup_code
3570 );
3571
3572 END IF; -- End if of absence type exists in this collection check...
3573
3574 END IF ; -- IF p_delete_gap_entry THEN
3575
3576 -- Delete the lookup code
3577
3578 l_proc_step := 30;
3579 IF g_debug THEN
3580 debug(l_proc_name, l_proc_step);
3581 END IF;
3582
3583
3584
3585 fnd_lookup_values_pkg.delete_row
3586 (x_lookup_type => p_lookup_type
3587 ,x_security_group_id => p_security_group_id
3588 ,x_view_application_id => p_view_application_id
3589 ,x_lookup_code => l_lookup_code
3590 );
3591 END LOOP;
3592 CLOSE csr_get_lkv_info;
3593
3594 -- Delete the lookup type
3595 l_proc_step := 40;
3596 IF g_debug THEN
3597 debug(l_proc_name, l_proc_step);
3598 END IF;
3599
3600
3601 fnd_lookup_types_pkg.delete_row
3602 (x_lookup_type => p_lookup_type
3603 ,x_security_group_id => p_security_group_id
3604 ,x_view_application_id => p_view_application_id
3605 );
3606
3607 END IF; -- End if of row found check ...
3608 CLOSE csr_get_lkt_info;
3609
3610 --
3611 debug('Leaving '||l_proc_name, 50);
3612 --
3613
3614 END delete_lookup;
3615 --
3616
3617 --
3618 --========================================================================
3619 -- PROCEDURE delete_udt
3620 --========================================================================
3621
3622 PROCEDURE delete_udt (p_udt_id in number)
3623 IS
3624
3625 --
3626
3627 CURSOR csr_get_usr_table_id
3628 IS
3629 SELECT rowid
3630 FROM pay_user_tables
3631 WHERE user_table_id = p_udt_id
3632 AND business_group_id = p_business_group_id;
3633
3634 CURSOR csr_get_usr_col_id
3635 IS
3636 SELECT user_column_id
3637 FROM pay_user_columns
3638 WHERE user_table_id = p_udt_id;
3639
3640 CURSOR csr_get_usr_row_id
3641 IS
3642 SELECT user_row_id
3643 FROM pay_user_rows_f
3644 WHERE user_table_id = p_udt_id;
3645
3646 --
3647 l_proc_step NUMBER(20,10);
3648 l_proc_name VARCHAR(72) := g_package_name || 'delete_udt';
3649 l_rowid rowid;
3650 l_usr_row_id pay_user_rows.user_row_id%TYPE;
3651 l_usr_col_id pay_user_columns.user_column_id%TYPE;
3652 --
3653 --
3654 BEGIN
3655
3656 --
3657 debug ('Entering '||l_proc_name, 10);
3658 --
3659
3660 -- Get user_table_id from pay_user_tables
3661 OPEN csr_get_usr_table_id;
3662 FETCH csr_get_usr_table_id INTO l_rowid;
3663
3664 IF csr_get_usr_table_id%FOUND THEN
3665
3666 -- Get user_column_id from pay_user_columns
3667 l_proc_step := 20;
3668 IF g_debug THEN
3669 debug(l_proc_name, l_proc_step);
3670 END IF;
3671
3672
3673 OPEN csr_get_usr_col_id;
3674 LOOP
3675 FETCH csr_get_usr_col_id INTO l_usr_col_id;
3676 EXIT WHEN csr_get_usr_col_id%NOTFOUND;
3677
3678 -- Delete pay_user_column_instances_f for this column_id
3679 l_proc_step := 30;
3680 IF g_debug THEN
3681 debug(l_proc_name, l_proc_step);
3682 END IF;
3683
3684
3685 DELETE pay_user_column_instances_f
3686 WHERE user_column_id = l_usr_col_id;
3687
3688 END LOOP;
3689 CLOSE csr_get_usr_col_id;
3690
3691 -- Delete pay_user_columns for this table_id
3692 l_proc_step := 40;
3693 IF g_debug THEN
3694 debug(l_proc_name, l_proc_step);
3695 END IF;
3696
3697
3698 DELETE pay_user_columns
3699 WHERE user_table_id = p_udt_id;
3700
3701 OPEN csr_get_usr_row_id;
3702 LOOP
3703 FETCH csr_get_usr_row_id INTO l_usr_row_id;
3704 EXIT WHEN csr_get_usr_row_id%NOTFOUND;
3705
3706 -- Delete pay_user_rows_f for this table id
3707 l_proc_step := 50;
3708 IF g_debug THEN
3709 debug(l_proc_name, l_proc_step);
3710 END IF;
3711
3712
3713 pay_user_rows_pkg.check_delete_row
3714 (p_user_row_id => l_usr_row_id
3715 ,p_validation_start_date => NULL
3716 ,p_dt_delete_mode => 'ZAP'
3717 );
3718
3719 DELETE pay_user_rows_f
3720 WHERE user_row_id = l_usr_row_id;
3721
3722 END LOOP;
3723 CLOSE csr_get_usr_row_id;
3724
3725
3726 -- Delete pay_user_tables for this table id
3727 l_proc_step := 60;
3728 IF g_debug THEN
3729 debug(l_proc_name, l_proc_step);
3730 END IF;
3731
3732 pay_user_tables_pkg.delete_row
3733 (p_rowid => l_rowid
3734 ,p_user_table_id => p_udt_id
3735 );
3736
3737
3738 END IF; -- End of of user_table found check ...
3739 CLOSE csr_get_usr_table_id;
3740
3741 --
3742 debug ('Leaving '||l_proc_name, 70);
3743 --
3744 --
3745 END delete_udt;
3746 --
3747
3748 --
3749 BEGIN -- delete_user_template
3750
3751 -- for Multi Messages
3752 hr_multi_message.enable_message_list;
3753
3754 --
3755 g_debug := hr_utility.debug_enabled;
3756 IF g_debug THEN
3757 debug_enter(l_proc_name);
3758 END IF;
3759 --
3760
3761 --- Added for Hours
3762
3763
3764 FOR csr_get_scheme_type_rec IN csr_get_scheme_type
3765 (
3766 p_ele_type_id => p_abs_ele_type_id
3767 )
3768 LOOP
3769 l_entitlements_uom := csr_get_scheme_type_rec.entitlements_uom ;
3770 l_daily_rate_uom := csr_get_scheme_type_rec.daily_rate_uom ;
3771 END LOOP ;
3772
3773 IF l_entitlements_uom = 'H' or l_daily_rate_uom = 'H' THEN
3774 l_template_name := 'PQP OSP HOURS' ;
3775 l_days_hours := 'Hours ';
3776 ELSE
3777 l_template_name := 'PQP OSP' ;
3778 l_days_hours := NULL ;
3779 END IF ;
3780
3781
3782 --- Added for Hours
3783
3784
3785
3786
3787 FOR csr_get_template_id_rec IN csr_get_template_id
3788 (
3789 p_template_name => l_template_name
3790 )
3791 LOOP
3792 l_template_id := csr_get_template_id_rec.template_id;
3793 END LOOP;
3794
3795 l_proc_step := 20;
3796 IF g_debug THEN
3797 debug(l_proc_name, l_proc_step);
3798 END IF;
3799
3800
3801 -- Check whether this is primary element
3802
3803 IF p_abs_primary_yn = 'Y' THEN
3804
3805 -- Check whether there are any secondary elements
3806 l_proc_step := 40;
3807 IF g_debug THEN
3808 debug(l_proc_name, l_proc_step);
3809 END IF;
3810
3811
3812 OPEN csr_chk_sec_ele (l_template_id
3813 ,l_template_name
3814 ,l_days_hours);
3815 FETCH csr_chk_sec_ele INTO l_exists;
3816
3817 IF csr_chk_sec_ele%FOUND THEN
3818
3819 -- Raise error
3820 CLOSE csr_chk_sec_ele;
3821 hr_utility.set_message (8303,'PQP_230607_OSP_SEC_ELE_EXISTS');
3822 hr_utility.raise_error;
3823
3824 END IF; -- End if of sec element check ...
3825 CLOSE csr_chk_sec_ele;
3826
3827 END IF; -- End if of abs primary yn check ...
3828
3829
3830 --
3831
3832 IF p_abs_primary_yn = 'Y'
3833 THEN
3834 del_automated_plan_setup_data
3835 (p_pl_id => p_plan_id
3836 ,p_business_group_id => p_business_group_id
3837 ,p_effective_date => p_effective_date
3838 ,p_base_name => p_abs_ele_name
3839 );
3840 END IF;
3841
3842 --
3843
3844
3845 -- Get Element type Id's from template core object
3846
3847 OPEN csr_get_ele_type_id (l_template_id);
3848 LOOP
3849
3850 FETCH csr_get_ele_type_id INTO l_element_type_id;
3851 EXIT WHEN csr_get_ele_type_id%NOTFOUND;
3852
3853 -- Check whether elements are attached to benefits
3854 -- standard rate formula before deleting them
3855
3856 l_proc_step := 25;
3857 IF g_debug THEN
3858 debug(l_proc_name, l_proc_step);
3859 END IF;
3860
3861
3862 OPEN csr_chk_ele_in_ben (l_element_type_id);
3863 FETCH csr_chk_ele_in_ben INTO l_exists;
3864
3865 IF csr_chk_ele_in_ben%FOUND THEN
3866
3867 -- Raise Error
3868 Close csr_chk_ele_in_ben;
3869 hr_utility.set_message (800,'PER_74880_CHILD_RECORD');
3870 hr_utility.set_message_token('TYPE','Standard Rates, Table: BEN_ACTY_BASE_RT_F');
3871 hr_utility.raise_error;
3872
3873 END IF; -- End if of element in ben check ...
3874 CLOSE csr_chk_ele_in_ben;
3875
3876 -- Get Element extra info id for this element type id
3877
3878 OPEN csr_get_eei_info (l_element_type_id);
3879 FETCH csr_get_eei_info INTO l_eei_info_id
3880 ,l_entudt_id
3881 ,l_caludt_id
3882 ,l_lookup_type
3883 ,l_plan_type_lookup ;
3884 IF csr_get_eei_info%FOUND -- if an EIT exists only then delete else ignore
3885 THEN
3886
3887 -- Delete the EEI row
3888 l_proc_step := 50;
3889 IF g_debug THEN
3890 debug(l_proc_name, l_proc_step);
3891 debug('l_element_type_id:'||l_element_type_id);
3892 debug('l_eei_info_id:'||l_eei_info_id);
3893 END IF;
3894
3895
3896
3897 pay_element_extra_info_api.delete_element_extra_info
3898 (p_validate => FALSE
3899 ,p_element_type_extra_info_id => l_eei_info_id
3900 ,p_object_version_number => l_ovn_eei);
3901 END IF;
3902 CLOSE csr_get_eei_info;
3903
3904 END LOOP;
3905 CLOSE csr_get_ele_type_id;
3906
3907 -- Delete Ent UDT
3908
3909 IF l_entudt_id IS NOT NULL AND
3910 p_abs_primary_yn = 'Y'
3911 THEN
3912
3913 OPEN csr_chk_eei_for_entudt (l_entudt_id);
3914 FETCH csr_chk_eei_for_entudt INTO l_exists;
3915
3916 IF csr_chk_eei_for_entudt%NOTFOUND THEN
3917
3918 -- Delete UDT
3919
3920 l_proc_step := 60;
3921 IF g_debug THEN
3922 debug(l_proc_name, l_proc_step);
3923 END IF;
3924
3925
3926 delete_udt (p_udt_id => l_entudt_id);
3927
3928 END IF; -- End if of eei row found check...
3929 CLOSE csr_chk_eei_for_entudt;
3930
3931 END IF; -- End if of ent udt name not null check ...
3932
3933 -- Delete Cal UDT
3934
3935 IF l_caludt_id IS NOT NULL AND
3936 p_abs_primary_yn = 'Y'
3937 THEN
3938
3939 OPEN csr_chk_eei_for_caludt (l_caludt_id);
3940 FETCH csr_chk_eei_for_caludt INTO l_exists;
3941
3942 IF csr_chk_eei_for_caludt%NOTFOUND THEN
3943
3944 -- Delete UDT
3945
3946 l_proc_step := 70;
3947 IF g_debug THEN
3948 debug(l_proc_name, l_proc_step);
3949 END IF;
3950
3951
3952 delete_udt (p_udt_id => l_caludt_id);
3953
3954 END IF; -- End if of eei row found check...
3955 CLOSE csr_chk_eei_for_caludt;
3956
3957 END IF; -- End if of cal udt name not null check ...
3958
3959
3960 -- Delete Lookup Type
3961
3962 IF l_lookup_type IS NOT NULL AND
3963 p_abs_primary_yn = 'Y'
3964 THEN
3965
3966 OPEN csr_chk_eei_for_lkt (l_lookup_type);
3967 FETCH csr_chk_eei_for_lkt INTO l_exists;
3968
3969 IF csr_chk_eei_for_lkt%NOTFOUND THEN
3970
3971 -- Get Other Lookup Information
3972
3973 l_proc_step := 75;
3974 IF g_debug THEN
3975 debug(l_proc_name, l_proc_step);
3976 END IF;
3977
3978
3979 get_other_lookups (p_business_group_id => p_business_group_id
3980 ,p_lookup_collection => l_lookup_collection
3981 ,p_template_name => l_template_name
3982 ,p_days_hours => l_days_hours
3983 );
3984
3985 -- Delete Lookup Type
3986
3987 l_proc_step := 80;
3988 IF g_debug THEN
3989 debug(l_proc_name, l_proc_step);
3990 END IF;
3991
3992
3993 delete_lookup (p_lookup_type => l_lookup_type
3994 ,p_security_group_id => p_security_group_id
3995 ,p_view_application_id => 3
3996 ,p_lookup_collection => l_lookup_collection
3997 );
3998 -- Delete the lookup that contains the plan types to be extended
3999 IF l_plan_type_lookup IS NOT NULL THEN
4000 l_proc_step := 81;
4001 IF g_debug THEN
4002 debug(l_proc_name, l_proc_step);
4003 END IF;
4004 delete_lookup (p_lookup_type => l_plan_type_lookup
4005 ,p_security_group_id => p_security_group_id
4006 ,p_view_application_id => 3
4007 ,p_lookup_collection => l_plan_type_lookup_collection
4008 ,p_delete_gap_entry => FALSE
4009 );
4010 END IF;
4011
4012
4013 -- Check whether PQP_GAP_ABSENCE_TYPES_LIST lookup type
4014 -- has atleast one lookup code
4015 -- Check whether PQP_GAP_ABSENCE_TYPES_LIST lookup type
4016 -- has atleast one lookup code
4017 -- Commented as a workaround.
4018 --Need to add the logic to check if the absence type is attached
4019 -- with other plans.If not dlete it else leave it as it is.
4020
4021 /* OPEN csr_get_lookup_code('PQP_GAP_ABSENCE_TYPES_LIST');
4022 FETCH csr_get_lookup_code INTO l_lookup_code;
4023
4024 IF csr_get_lookup_code%FOUND THEN
4025
4026 -- Delete this lookup type
4027 l_proc_step := 85;
4028 IF g_debug THEN
4029 debug(l_proc_name, l_proc_step);
4030 END IF;
4031
4032
4033 fnd_lookup_types_pkg.delete_row
4034 (x_lookup_type => 'PQP_GAP_ABSENCE_TYPES_LIST'
4035 ,x_security_group_id => p_security_group_id
4036 ,x_view_application_id => 3
4037 );
4038
4039 END IF; -- End if of lookup code check ...
4040 CLOSE csr_get_lookup_code;
4041 */
4042 END IF; -- End if of eei row found check...
4043 CLOSE csr_chk_eei_for_lkt;
4044
4045 END IF; -- End of of udt name not null check ...
4046
4047 l_proc_step := 90;
4048 IF g_debug THEN
4049 debug(l_proc_name, l_proc_step);
4050 END IF;
4051
4052
4053
4054 ---- Delete Element Links Before Deleting the Template
4055 -- pqp_gb_omp_template.delete_element_links
4056 -- ( p_business_group_id => p_business_group_id
4057 -- ,p_effective_start_date => p_effective_date
4058 -- ,p_effective_end_date => p_effective_date
4059 -- ,p_base_name => p_abs_ele_name
4060 -- ,p_abs_type => ' OSP '||l_days_hours );
4061
4062 pqp_gb_omp_template.delete_element_links
4063 (p_business_group_id => p_business_group_id
4064 ,p_effective_start_date => p_effective_date
4065 ,p_effective_end_date => p_effective_date
4066 ,p_template_id => l_template_id
4067 ) ;
4068
4069 ---- Delete Links
4070
4071
4072 pay_element_template_api.delete_user_structure
4073 (p_validate => false
4074 ,p_drop_formula_packages => true
4075 ,p_template_id => l_template_id);
4076 --
4077
4078 IF g_debug THEN
4079 debug_exit(l_proc_name);
4080 END IF;
4081
4082 --
4083 EXCEPTION
4084 WHEN hr_multi_message.error_message_exist THEN
4085
4086 --
4087 -- Catch the Multiple Message List exception which
4088 -- indicates API processing has been aborted because
4089 -- at least one message exists in the list.
4090 --
4091 debug ( ' Leaving:'
4092 || l_proc_name, 40);
4093 WHEN OTHERS
4094 THEN
4095
4096 --
4097 -- When Multiple Message Detection is enabled catch
4098 -- any Application specific or other unexpected
4099 -- exceptions. Adding appropriate details to the
4100 -- Multiple Message List. Otherwise re-raise the
4101 -- error.
4102 --
4103 IF hr_multi_message.unexpected_error_add (l_proc_name)
4104 THEN
4105 debug ( ' Leaving:'
4106 || l_proc_name, 50);
4107 RAISE;
4108 END IF;
4109
4110 END delete_user_template;
4111 --
4112
4113 --======================================================================
4114 -- PROCEDURE create_gap_lookup
4115 --======================================================================
4116 PROCEDURE create_gap_lookup (p_security_group_id IN NUMBER
4117 ,p_ele_eff_start_date IN DATE
4118 ,p_lookup_type IN VARCHAR2
4119 ,p_lookup_meaning IN VARCHAR2
4120 ,p_lookup_values IN t_abs_types
4121 ) IS
4122 --
4123
4124 CURSOR csr_chk_uniq_type
4125 IS
4126 SELECT 'X'
4127 FROM fnd_lookup_types_vl
4128 WHERE lookup_type = p_lookup_type
4129 AND security_group_id = p_security_group_id
4130 AND view_application_id = 3;
4131
4132 CURSOR csr_chk_uniq_meaning
4133 IS
4134 SELECT 'X'
4135 FROM fnd_lookup_types_vl
4136 WHERE meaning = p_lookup_meaning
4137 AND security_group_id = p_security_group_id
4138 AND view_application_id = 3;
4139
4140 CURSOR csr_chk_uniq_value (c_lookup_code varchar2)
4141 IS
4142 SELECT 'X'
4143 FROM fnd_lookup_values_vl
4144 WHERE lookup_type = p_lookup_type
4145 AND lookup_code = c_lookup_code
4146 AND security_group_id = p_security_group_id
4147 AND view_application_id = 3;
4148
4149 CURSOR csr_chk_uniq_value_meaning (c_lookup_meaning varchar2)
4150 IS
4151 SELECT 'X'
4152 FROM fnd_lookup_values_vl
4153 WHERE lookup_type = p_lookup_type
4154 AND meaning = c_lookup_meaning
4155 AND security_group_id = p_security_group_id
4156 AND view_application_id = 3;
4157
4158 l_proc_step NUMBER(20,10);
4159 l_proc_name VARCHAR2(72) := g_package_name || 'create_gap_lookup';
4160 l_exists VARCHAR2(1);
4161 l_rowid fnd_lookup_types_vl.row_id%type;
4162 l_user_id number := fnd_global.user_id;
4163 l_login_id number := fnd_global.login_id;
4164 i number ;
4165 --
4166 BEGIN
4167 --
4168 debug('Entering '||l_proc_name, 10);
4169 --
4170
4171 -- Check lookup type exists
4172 OPEN csr_chk_uniq_type;
4173 FETCH csr_chk_uniq_type INTO l_exists;
4174
4175 IF csr_chk_uniq_type%NOTFOUND THEN
4176
4177 l_proc_step := 20;
4178 IF g_debug THEN
4179 debug(l_proc_name, l_proc_step);
4180 END IF;
4181
4182
4183 -- Check unique lookup type meaning
4184 OPEN csr_chk_uniq_meaning;
4185 FETCH csr_chk_uniq_meaning INTO l_exists;
4186
4187 IF csr_chk_uniq_meaning%FOUND THEN
4188
4189 -- Raise error
4190 CLOSE csr_chk_uniq_meaning;
4191 hr_utility.set_message(0, 'QC-DUPLICATE TYPE MEANING');
4192 hr_utility.raise_error;
4193
4194 END IF; -- End if of unique lookup type meaning check ...
4195 CLOSE csr_chk_uniq_meaning;
4196
4197 -- Create Lookup type
4198 l_proc_step := 30;
4199 IF g_debug THEN
4200 debug(l_proc_name, l_proc_step);
4201 END IF;
4202
4203
4204 fnd_lookup_types_pkg.insert_row
4205 (
4206 x_rowid => l_rowid
4207 ,x_lookup_type => p_lookup_type
4208 ,x_security_group_id => p_security_group_id
4209 ,x_view_application_id => 3
4210 ,x_application_id => 800
4211 ,x_customization_level => 'U'
4212 ,x_meaning => p_lookup_meaning
4213 ,x_description => NULL
4214 ,x_creation_date => SYSDATE
4215 ,x_created_by => l_user_id
4216 ,x_last_update_date => SYSDATE
4217 ,x_last_updated_by => l_user_id
4218 ,x_last_update_login => l_login_id
4219 );
4220
4221 END IF; -- End if of lookup type exists check ...
4222 CLOSE csr_chk_uniq_type;
4223
4224 l_proc_step := 40;
4225 IF g_debug THEN
4226 debug(l_proc_name, l_proc_step);
4227 END IF;
4228
4229 IF p_lookup_values.count > 0 THEN
4230
4231 i := p_lookup_values.FIRST;
4232 WHILE i IS NOT NULL
4233 LOOP
4234
4235 l_proc_step := 50;
4236 IF g_debug THEN
4237 debug(l_proc_name, l_proc_step);
4238 END IF;
4239
4240 -- Check whether this lookup code already exists
4241
4242 OPEN csr_chk_uniq_value (fnd_number.number_to_canonical(
4243 p_lookup_values(i).abs_type_id));
4244 FETCH csr_chk_uniq_value INTO l_exists;
4245
4246 IF csr_chk_uniq_value%NOTFOUND THEN
4247
4248 l_proc_step := 60;
4249 IF g_debug THEN
4250 debug(l_proc_name, l_proc_step);
4251 END IF;
4252
4253 -- Check whether the lookup code meaning is unique
4254 OPEN csr_chk_uniq_value_meaning (p_lookup_values(i).abs_type_name);
4255 FETCH csr_chk_uniq_value_meaning INTO l_exists;
4256
4257 IF csr_chk_uniq_value_meaning%FOUND THEN
4258
4259 -- Raise error
4260 CLOSE csr_chk_uniq_value_meaning;
4261 hr_utility.set_message(0, 'QC-DUPLICATE MEANING');
4262 hr_utility.raise_error;
4263
4264 END IF; -- End if of lookup code meaning check ...
4265 CLOSE csr_chk_uniq_value_meaning;
4266
4267 l_proc_step := 70;
4268 IF g_debug THEN
4269 debug(l_proc_name, l_proc_step);
4270 END IF;
4271
4272
4273 fnd_lookup_values_pkg.insert_row
4274 (
4275 x_rowid => l_rowid
4276 ,x_lookup_type => p_lookup_type
4277 ,x_security_group_id => p_security_group_id
4278 ,x_view_application_id => 3
4279 ,x_lookup_code => fnd_number.number_to_canonical(
4280 p_lookup_values(i).abs_type_id)
4281 ,x_tag => NULL
4282 ,x_attribute_category => NULL
4283 ,x_attribute1 => NULL
4284 ,x_attribute2 => NULL
4285 ,x_attribute3 => NULL
4286 ,x_attribute4 => NULL
4287 ,x_attribute5 => NULL
4288 ,x_attribute6 => NULL
4289 ,x_attribute7 => NULL
4290 ,x_attribute8 => NULL
4291 ,x_attribute9 => NULL
4292 ,x_attribute10 => NULL
4293 ,x_attribute11 => NULL
4294 ,x_attribute12 => NULL
4295 ,x_attribute13 => NULL
4296 ,x_attribute14 => NULL
4297 ,x_attribute15 => NULL
4298 ,x_enabled_flag => 'Y'
4299 ,x_start_date_active => p_ele_eff_start_date
4300 ,x_end_date_active => NULL
4301 ,x_territory_code => NULL
4302 ,x_meaning => p_lookup_values(i).abs_type_name
4303 ,x_description => NULL
4304 ,x_creation_date => SYSDATE
4305 ,x_created_by => l_user_id
4306 ,x_last_update_date => SYSDATE
4307 ,x_last_updated_by => l_user_id
4308 ,x_last_update_login => l_login_id
4309 );
4310
4311 END IF; -- End if of lookup code check ...
4312 CLOSE csr_chk_uniq_value;
4313
4314 i := p_lookup_values.NEXT(i);
4315
4316 END LOOP;
4317
4318 END IF; -- End if of p_lookup_values check ...
4319
4320 --
4321 debug_exit(l_proc_name);
4322 END create_gap_lookup;
4323
4324
4325 END pqp_gb_osp_template;