[Home] [Help]
PACKAGE BODY: APPS.PQP_UK_VEHICLE_TEMPLATE
Source
1 PACKAGE BODY pqp_uk_vehicle_template AS
2 /* $Header: pqukcmtp.pkb 120.0 2005/05/29 02:12:56 appldev noship $ */
3
4 /*========================================================================
5 * CREATE_USER_INIT_TEMPLATE
6 *=======================================================================*/
7 FUNCTION create_user_template
8 (p_ele_name in varchar2
9 ,p_ele_reporting_name in varchar2
10 ,p_ele_description in varchar2 default NULL
11 ,p_ele_classification in varchar2
12 ,p_ele_processing_type in varchar2
13 ,p_ele_priority in number default NULL
14 ,p_ele_standard_link in varchar2 default 'N'
15 ,p_veh_type in varchar2
16 ,p_table_indicator_flg in varchar2
17 ,p_table_name in varchar2
18 ,p_ele_eff_start_date in date default NULL
19 ,p_ele_eff_end_date in date default NULL
20 ,p_bg_id in number
21 )
22 RETURN NUMBER IS
23 --
24
25
26 /*--------------------------------------------------------------------
27 The input values are explained below : V-varchar2, D-Date, N-number
28 Input-Name Type Valid Values/Explaination
29 ---------- ---- --------------------------------------
30 p_ele_name (V) - User i/p Element name
31 p_ele_reporting_name (V) - User i/p reporting name
32 p_ele_description (V) - User i/p Description
33 p_ele_classification (V) - 'Pre-Tax Deductions'
34 p_ben_class_id (N) - '' - not used
35 p_ele_category (V) - 'E'/'G' (403B/457)
36 p_ele_processing_type (V) - 'R'/'N' (Recurring/Non-recurring)
37 p_ele_priority (N) - User i/p priority
38 p_ele_standard_link (V) - 'Y'/'N' (default N)
39 p_ele_proc_runtype (V) - 'REG'/'ALL'
40 p_ele_calc_rule (V) - 'FA'/'PE' (Flat amount/Percentage)
41 p_ele_eff_start_date (D) - Trunc(start date)
42 p_ele_eff_end_date (D) - Trunc(end date)
43 p_bg_id (N) - Business group id
44 ----------------------------------------------------------------------*/
45 --
46 l_mileage_rs_element_type_id number;
47 l_template_id NUMBER(9);
48 l_base_element_type_id NUMBER(9);
49 l_source_template_id NUMBER(9);
50 l_object_version_number NUMBER(9);
51 l_proc VARCHAR2(80) :=
52 'pqp_uk_vehicle_template.create_user_template';
53 l_co_car VARCHAR2(3);
54 l_priv_car VARCHAR2(3);
55 l_lumpsum VARCHAR2(3);
56 l_covan VARCHAR2(3);
57 l_result VARCHAR2(3);
58 l_twowheel VARCHAR2(3);
59 l_ip_name VARCHAR2(40);
60 l_pedal VARCHAR2(3);
61 l_excomp_id VARCHAR2(3);
62 l_balfeed_excar VARCHAR2(3);
63 l_balfeed_exmc VARCHAR2(3);
64 l_balfeed_expc VARCHAR2(3);
65
66 l_eei_info_id NUMBER;
67 l_ovn_eei NUMBER;
68 l_sub VARCHAR2(30);
69 l_element_type_id NUMBER;
70 l_lumptemp VARCHAR2(40);
71 l_ele_obj_ver_number NUMBER;
72 l_input_id NUMBER;
73 l_ip_object_version_number NUMBER;
74 --
75 TYPE t_lump_bal IS TABLE OF VARCHAR2(80)
76 INDEX BY BINARY_INTEGER;
77
78 l_lump t_lump_bal;
79
80 CURSOR c1 (c_ele_name varchar2) is
81 SELECT element_type_id, object_version_number
82 FROM pay_shadow_element_types
83 WHERE template_id = l_template_id
84 AND element_name = c_ele_name;
85 --
86 -- cursor to fetch the core element id
87 --
88 CURSOR c5 (c_element_name in varchar2) is
89 SELECT ptco.core_object_id
90 FROM pay_shadow_element_types psbt,
91 pay_template_core_objects ptco
92 WHERE psbt.template_id = l_template_id
93 AND psbt.element_name = c_element_name
94 AND ptco.template_id = psbt.template_id
95 AND ptco.shadow_object_id = psbt.element_type_id
96 AND ptco.core_object_type = 'ET';
97
98 CURSOR c_input_id (c_element_type_id NUMBER) IS
99 SELECT name,input_value_id,object_version_number
100 FROM pay_shadow_input_values
101 WHERE element_type_id= c_element_type_id
102 AND name IN ('Two Wheeler Type','User Rates Table');
103 --
104 --======================================================================
105 -- FUNCTION GET_TEMPLATE_ID
106 --======================================================================
107 FUNCTION get_template_id (p_legislation_code in varchar2 )
108 RETURN number IS
109 --
110 -- l_template_id NUMBER(9);
111 l_template_name VARCHAR2(80);
112 l_proc varchar2(60) := 'pqp_uk_vehicle_template.get_template_id';
113 --
114 CURSOR c4 is
115 SELECT template_id
116 FROM pay_element_templates
117 WHERE template_name = l_template_name
118 AND legislation_code = p_legislation_code
119 AND template_type = 'T'
120 AND business_group_id is NULL;
121 --
122 BEGIN
123 --
124 hr_utility.set_location('Entering: '||l_proc, 10);
125 --
126 l_template_name := 'PQP MILEAGE CLAIM';
127 --
128 hr_utility.set_location(l_proc, 30);
129 --
130 for c4_rec in c4 loop
131 l_template_id := c4_rec.template_id;
132 end loop;
133 --
134 hr_utility.set_location('Leaving: '||l_proc, 100);
135 --
136 RETURN l_template_id;
137 --
138 END get_template_id;
139
140 -----------------------------------------------------------------------------
141 --- Procedure Delete balance feeds
142 -----------------------------------------------------------------------------
143 /* PROCEDURE delete_balance_feeds(l_sub_type IN VARCHAR2,l_name IN VARCHAR2)
144 is
145 l_reg_earn_input_value_id number;
146 l_reg_earn_element_type_id number;
147 l_reg_earn_classification_id number;
148 l_scale number;
149 l_balance_type_id number;
150 TYPE t_balance_name IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
151 l_chk_stat varchar2(10):='TRUE' ;
152 l_balance_name t_balance_name ;
153
154 CURSOR c1_delinput(lc_sub_type Varchar2,lc_name varchar2) IS
155 SELECT input_value_id,
156 piv.name,
157 piv.element_type_id
158 FROM pay_input_values_f piv,
159 pay_element_types_f pet
160 WHERE element_name= p_ele_name||' '||lc_sub_type
161 AND piv.element_type_id=pet.element_type_id
162 AND (piv.business_group_id =p_bg_id OR piv.business_group_id IS NULL)
163 AND piv.name =lc_name
164 AND (piv.legislation_code='GB' OR piv.legislation_code IS NULL);
165
166 CURSOR c2_delbal (p_balance_name varchar2) IS
167 SELECT pbt.BALANCE_TYPE_ID
168 FROM pay_balance_types pbt
169 WHERE pbt.BALANCE_NAME = p_balance_name
170 AND pbt.lEGISLATION_CODE = 'GB'
171 AND (pbt.legislation_code='GB' OR pbt.legislation_code IS NULL);
172
173 CURSOR c3_delfeed (p_input number,p_bal_type_id number) IS
174 SELECT balance_feed_id
175 FROM pay_balance_feeds
176 WHERE input_value_id=p_input
177 AND balance_type_id=p_bal_type_id
178 AND (business_group_id =p_bg_id OR business_group_id IS NULL)
179 AND (legislation_code='GB' OR legislation_code IS NULL);
180
181 c1_rec c1_delinput%rowtype;
182 c2_rec c2_delbal%rowtype;
183 c3_rec c3_delfeed%rowtype;
184
185 BEGIN
186
187 IF l_name ='Pay Value'THEN
188
189 l_balance_name(1) :='Taxable Pay';
190 l_balance_name(2) :='Attachable';
191
192 END IF;
193
194
195 OPEN c1_delinput(l_sub_type,l_name);
196
197
198 LOOP
199
200 FETCH c1_delinput INTO c1_rec;
201 EXIT WHEN c1_delinput%NOTFOUND;
202
203 l_reg_earn_input_value_id :=c1_rec.input_value_id;
204
205 FOR i IN 1..l_balance_name.count
206 LOOP
207
208 OPEN c2_delbal(l_balance_name(i));
209 LOOP
210 FETCH c2_delbal INTO c2_rec;
211 EXIT WHEN c2_delbal%NOTFOUND;
212 l_balance_type_id :=c2_rec.BALANCE_TYPE_ID;
213
214
215 OPEN c3_delfeed(l_reg_earn_input_value_id ,l_balance_type_id );
216 LOOP
217 FETCH c3_delfeed INTO c3_rec;
218 EXIT WHEN c3_delfeed%NOTFOUND;
219
220 hr_balances.del_balance_feed(
221 p_option => 'DEL_MANUAL_FEED' ,
222 P_delete_mode => 'DELETE' ,
223 P_balance_feed_id => c3_rec.balance_feed_id ,
224 P_input_value_id => c1_rec.input_value_id ,
225 P_element_type_id => c1_rec.element_type_id ,
226 P_primary_classification_id => NULL ,
227 P_sub_classification_id => NULL ,
228 P_sub_classification_rule_id => NULL ,
229 P_balance_type_id => c2_rec.BALANCE_TYPE_ID ,
230 P_session_date => p_ele_eff_start_date ,
231 P_effective_end_date => p_ele_eff_start_date ,
232 P_legislation_code => NULL,
233 P_mode => 'USER');
234
235 END LOOP;
236 CLOSE c3_delfeed;
237 END LOOP;
238 CLOSE c2_delbal;
239 END LOOP;
240 END LOOP;
241 CLOSE c1_delinput ;
242
243
244
245 END delete_balance_feeds;*/
246
247 -----------------------------------------------------------------------------
248 --- End Procedure Delete balance feeds
249 -----------------------------------------------------------------------------
250 --------------------------------------------------------------------------------------
251 ---Procedure Create balance feeds
252 ------------------------------------------------------------------------------------
253
254 PROCEDURE create_balance_feeds
255 is
256 l_reg_earn_input_value_id number;
257 l_reg_earn_element_type_id number;
258 l_reg_earn_classification_id number;
259 l_scale number;
260 l_balance_type_id number;
261 TYPE t_balance_name IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
262 l_chk_stat varchar2(10):='TRUE' ;
263 l_balance_name VARCHAR2(15); ---t_balance_name ;
264 l_balance_name1 VARCHAR2(15); ---t_balance_name ;
265 CURSOR c1 IS SELECT input_value_id,piv.name,
266 element_name
267 FROM pay_input_values piv,
268 pay_element_types pet
269 WHERE element_name IN ( p_ele_name||' NIable'
270 )
271 AND piv.element_type_id=pet.element_type_id
272 AND piv.business_group_id =p_bg_id
273 AND piv.name IN ('Pay Value');
274
275 CURSOR c2 (p_balance_name varchar2) IS
276 SELECT pbt.BALANCE_TYPE_ID
277 FROM pay_balance_types pbt
278 WHERE pbt.BALANCE_NAME = p_balance_name
279 AND pbt.lEGISLATION_CODE = 'GB';
280
281 c1_rec c1%rowtype;
282 c2_rec c2%rowtype;
283
284
285 BEGIN
286 -- l_balance_name :='NIable Pay';
287
288 -- l_balance_name1 :='Taxable Pay';
289
290 OPEN c1;
291 LOOP
292 FETCH c1 INTO c1_rec;
293 EXIT WHEN c1%NOTFOUND;
294
295 l_reg_earn_input_value_id :=c1_rec.input_value_id;
296
297 IF c1_rec.element_name= p_ele_name||' NIable'THEN
298 l_balance_name :='NIable Pay';
299
300
301 END IF;
302
303 OPEN c2(l_balance_name);
304 LOOP
305 FETCH c2 INTO c2_rec;
306 EXIT WHEN c2%NOTFOUND;
307
308 l_balance_type_id :=c2_rec.BALANCE_TYPE_ID;
309
310
311 hr_balances.ins_balance_feed(
312 p_option => 'INS_MANUAL_FEED',
313 p_input_value_id => l_reg_earn_input_value_id,
314 p_element_type_id => NULL,
315 p_primary_classification_id => NULL,
316 p_sub_classification_id => NULL,
317 p_sub_classification_rule_id => NULL,
318 p_balance_type_id => l_balance_type_id,
319 p_scale => 1,
320 p_session_date => p_ele_eff_start_date,
321 p_business_group => p_bg_id,
322 p_legislation_code => NULL,
323 p_mode => 'USER');
324
325 END LOOP;
326 CLOSE c2;
327
328 END LOOP;
329 CLOSE c1;
330
331 END;
332
333
334
335
336
337
338
339 ------------------------------------------------------------------------------------
340 ----Create balance feed ends
341
342 ----------------------------------------------------------------------------------
343
344
345
346 ------------------------------------------------------------------------------------
347 -----------------------------------------------------------------------------
348 --- Procedure Update Formula id for an Input value
349 -----------------------------------------------------------------------------
350 PROCEDURE upd_inputval_formula(p_ele_type IN NUMBER,
351 p_inputname IN VARCHAR2,
352 p_formula_name IN VARCHAR2)
353 IS
354 CURSOR c_get_inputval is
355 SELECT input_value_id,
356 piv.name,
357 piv.element_type_id
358 FROM pay_input_values_f piv,
359 pay_element_types_f pet
360 WHERE piv.element_type_id =p_ele_type
361 AND piv.element_type_id = pet.element_type_id
362 AND piv.business_group_id = p_bg_id
363 AND piv.name =p_inputname;
364
365
366 CURSOR c_get_id IS
367 SELECT formula_id
368 FROM ff_formulas
369 WHERE FORMULA_name=p_formula_name
370 AND p_ele_eff_start_date
371 BETWEEN effective_start_date
372 AND effective_end_date
373 AND legislation_code='GB';
374
375 CURSOR c_get_valueset IS
376 SELECT ffvs.flex_value_set_id
377 FROM fnd_flex_value_sets ffvs
378 WHERE flex_value_set_name = 'PQP_PURPOSE_LIST';
379
380 c3_rec c_get_valueset%ROWTYPE;
381 c1_rec c_get_inputval%ROWTYPE;
382 c2_rec c_get_id%ROWTYPE;
383 BEGIN
384 IF p_inputname = 'Purpose' THEN
385
386 OPEN c_get_valueset;
387 FETCH c_get_valueset INTO c3_rec;
388 CLOSE c_get_valueset;
389
390 OPEN c_get_inputval ;
391 FETCH c_get_inputval INTO c1_rec;
392 CLOSE c_get_inputval;
393
394 UPDATE pay_input_values_f
395 SET value_set_id=c3_rec.flex_value_set_id,
396 warning_or_error='W'
397 WHERE input_value_id=c1_rec.input_value_id
398 AND element_type_id=p_ele_type;
399 ELSE
400
401
402 OPEN c_get_inputval ;
403 LOOP
404 FETCH c_get_inputval INTO c1_rec;
405 EXIT WHEN c_get_inputval%NOTFOUND;
406 OPEN c_get_id ;
407 LOOP
408 FETCH c_get_id INTO c2_rec;
409 EXIT WHEN c_get_id %NOTFOUND;
410
411 UPDATE pay_input_values_f
412 SET formula_id=c2_rec.formula_id,
413 warning_or_error='E'
414 WHERE input_value_id=c1_rec.input_value_id
415 AND element_type_id=p_ele_type;
416 END LOOP;
417 CLOSE c_get_id;
418 END LOOP;
419 CLOSE c_get_inputval ;
420 END IF;
421
422
423
424 EXCEPTION
425 --------
426 WHEN OTHERS THEN
427 NULL;
428
429
430
431 END upd_inputval_formula;
432 -----------------------------------------------------------------------------
433 --- End Procedure Update Formula id for an Input value
434 -----------------------------------------------------------------------------
435
436 --
437 --=======================================================================
438 -- FUNCTION GET_OBJECT_ID
439 --=======================================================================
440 FUNCTION get_object_id (p_object_type in varchar2,
441 p_object_name in varchar2)
442 RETURN NUMBER is
443 --
444 l_object_id NUMBER := NULL;
445 l_proc varchar2(60) := 'pqp_uk_vehicle_template.get_object_id';
446 --
447 CURSOR c2 (c_object_name varchar2) is
448 SELECT element_type_id
449 FROM pay_element_types_f
450 WHERE element_name = c_object_name
451 AND business_group_id = p_bg_id;
452 --
453 CURSOR c3 (c_object_name in varchar2) is
454 SELECT ptco.core_object_id
455 FROM pay_shadow_balance_types psbt,
456 pay_template_core_objects ptco
457 WHERE psbt.template_id = l_template_id
458 AND psbt.balance_name = c_object_name
459 AND ptco.template_id = psbt.template_id
460 AND ptco.shadow_object_id = psbt.balance_type_id;
461 --
462 BEGIN
463 hr_utility.set_location('Entering: '||l_proc, 10);
464 --
465 if p_object_type = 'ELE' then
466 for c2_rec in c2 (p_object_name) loop
467 l_object_id := c2_rec.element_type_id; -- element id
468 end loop;
469 elsif p_object_type = 'BAL' then
470 for c3_rec in c3 (p_object_name) loop
471 l_object_id := c3_rec.core_object_id; -- balance id
472 end loop;
473 end if;
474 --
475 hr_utility.set_location('Leaving: '||l_proc, 50);
476 --
477 RETURN l_object_id;
478 --
479 END get_object_id;
480 --
481 --===============================================================================
482 -- MAIN FUNCTION
483 --===============================================================================
484 BEGIN
485 hr_utility.set_location('Entering : '||l_proc, 10);
486 ---------------------
487 -- Set session date
488 ---------------------
489
490 pay_db_pay_setup.set_session_date(nvl(p_ele_eff_start_date, sysdate));
491 --
492 hr_utility.set_location(l_proc, 20);
493 ---------------------------
494 -- Get Source Template ID
495 ---------------------------
496 l_source_template_id := get_template_id
497 (p_legislation_code => 'GB'
498 );
499 hr_utility.set_location(l_proc, 30);
500 --
501 /*--------------------------------------------------------------------------
502 Create the user Structure
503 The Configuration Flex segments for the Exclusion Rules are as follows:
504 ---------------------------------------------------------------------------
505 Config1 --
506 Config2 --
507 ---------------------------------------------------------------------------*/
508
509
510
511 IF p_veh_type='C' THEN
512 l_balfeed_excar :='N';
513 l_balfeed_exmc :='Y';
514 l_balfeed_expc :='Y';
515 l_priv_car:='N';
516 l_result:=NULL;
517 l_lumpsum:='N';
518 l_co_car:=NULL;
519 l_sub:='Company';
520 l_covan:='N';
521 l_twowheel:='N';
522 l_pedal:=NULL;
523 l_excomp_id:='N';
524 ELSIF p_veh_type='CM' THEN
525 l_balfeed_excar :='Y';
526 l_balfeed_exmc :='N';
527 l_balfeed_expc :='Y';
528 l_priv_car:='N';
529 l_result:=NULL;
530 l_lumpsum:='N';
531 l_co_car:=NULL;
532 l_sub:='Company';
533 l_covan:='N';
534 l_twowheel:='CM';
535 l_pedal:=NULL;
536 l_excomp_id:='N';
537 ELSIF p_veh_type='CP' THEN
538 l_balfeed_excar :='Y';
539 l_balfeed_exmc :='Y';
540 l_balfeed_expc :='N';
541 l_priv_car:='N';
542 l_result:=NULL;
543 l_lumpsum:='N';
544 l_co_car:=NULL;
545 l_sub:='Company';
546 l_covan:='N';
547 l_twowheel:='CP';
548 l_pedal:='N';
549 l_excomp_id:='N';
550 ELSIF p_veh_type='P' THEN
551 l_co_car:='N';
552 l_priv_car :=NULL;
553 l_result:=NULL;
554 l_lumpsum:='N';
555 l_sub:='Private';
556 l_covan:='N';
557 l_twowheel:='N';
558 l_pedal:=NULL;
559 l_balfeed_excar :='N';
560 l_balfeed_exmc :='Y';
561 l_balfeed_expc :='Y';
562 ELSIF p_veh_type='PM' THEN
563 l_co_car:='N';
564 l_priv_car :=NULL;
565 l_result:=NULL;
566 l_lumpsum:='N';
567 l_sub:='Private';
568 l_covan:='N';
569 l_twowheel:='PM';
570 l_pedal:=NULL;
571 l_balfeed_excar :='Y';
572 l_balfeed_exmc :='N';
573 l_balfeed_expc :='Y';
574 ELSIF p_veh_type='PP' THEN
575 l_balfeed_excar :='Y';
576 l_balfeed_exmc :='Y';
577 l_balfeed_expc :='N';
578 l_co_car:='N';
579 l_priv_car :=NULL;
580 l_result:=NULL;
581 l_lumpsum:='N';
582 l_sub:='Private';
583 l_covan:='N';
584 l_twowheel:='PP';
585 l_pedal:='N';
586 ELSIF p_veh_type='L' THEN
587
588 l_pedal:='N';
589 l_balfeed_excar :='Y';
590 l_balfeed_exmc :='Y';
591 l_balfeed_expc :='N';
592 l_co_car:='N';
593 l_priv_car :='N';
594 l_result:='N';
595 l_lumpsum:=NULL;
596 l_covan:='N';
597 ELSIF p_veh_type='V' THEN
598
599 l_pedal:='N';
600 l_balfeed_excar :='Y';
601 l_balfeed_exmc :='Y';
602 l_balfeed_expc :='N';
603
604 l_co_car:='N';
605 l_priv_car :='N';
606 l_result:='N';
607 l_lumpsum:='N';
608 l_covan:=NULL;
609 l_sub:='Company Van';
610
611 END IF;
612
613
614
615
616
617 --
618 -- create user structure from the template
619 --
620 pay_element_template_api.create_user_structure
621 (p_validate => false
622 ,p_effective_date => p_ele_eff_start_date
623 ,p_business_group_id => p_bg_id
624 ,p_source_template_id => l_source_template_id
625 ,p_base_name => p_ele_name
626 ,p_base_processing_priority => p_ele_priority
627 ,p_configuration_information1 => l_co_car
628 ,p_configuration_information2 => l_priv_car
629 ,p_configuration_information3 => l_lumpsum
630 ,p_configuration_information4 => l_result
631 ,p_configuration_information5 => l_covan
632 ,p_configuration_information6 => l_twowheel
633 ,p_configuration_information7 => l_pedal
634 ,p_configuration_information8 => l_excomp_id
635 ,p_configuration_information9 => l_balfeed_excar
636 ,p_configuration_information10 => l_balfeed_exmc
637 ,p_configuration_information11 => l_balfeed_expc
638 ,p_template_id => l_template_id
639 ,p_object_version_number => l_object_version_number
640 );
641 --
642
643
644 hr_utility.set_location(l_proc, 80);
645 ---------------------------------------------------------------------------
646 ---------------------------- Update Shadow Structure ----------------------
647 --
648
649
650 OPEN c1(p_ele_name||l_sub);
651 LOOP
652 FETCH c1 INTO l_element_type_id,l_ele_obj_ver_number;
653 EXIT WHEN c1%NOTFOUND;
654
655 pay_shadow_element_api.update_shadow_element
656 (p_validate => false
657 ,p_effective_date => p_ele_eff_start_date
658 ,p_element_type_id => l_element_type_id
659 ,p_element_name => p_ele_name
660 ,p_description => p_ele_description
661 ,p_reporting_name =>p_ele_reporting_name
662 ,p_object_version_number => l_ele_obj_ver_number
663 );
664
665
666
667 END LOOP;
668 OPEN c_input_id(l_element_type_id);
669 LOOP
670 FETCH c_input_id INTO l_ip_name,l_input_id,l_ip_object_version_number;
671 EXIT WHEN c_input_id%NOTFOUND;
672 IF p_veh_type <>'P' OR p_veh_type <> 'C'
673 OR p_veh_type<>'L' OR p_veh_type <> 'V' THEN
674 IF l_ip_name='Two Wheeler Type' THEN
675 pay_siv_upd.upd( p_effective_date => p_ele_eff_start_date
676 ,p_input_value_id => l_input_id
677 ,p_element_type_id => l_element_type_id
678 ,p_default_value => l_twowheel
679 ,p_object_version_number => l_ip_object_version_number );
680 END IF;
681
682 END IF;
683
684 IF l_ip_name='User Rates Table' THEN
685
686 -- The condition to check if the business group wants sliding rates table or
687 -- just a simple rates tables.
688 IF p_table_indicator_flg = 'N' THEN
689 pay_siv_upd.upd( p_effective_date => p_ele_eff_start_date
690 ,p_input_value_id => l_input_id
691 ,p_element_type_id => l_element_type_id
692 ,p_default_value => p_table_name
693 ,p_object_version_number => l_ip_object_version_number );
694 ELSE
695 pay_siv_upd.upd( p_effective_date => p_ele_eff_start_date
696 ,p_input_value_id => l_input_id
697 ,p_element_type_id => l_element_type_id
698 ,p_name => 'Sliding Rates Table'
699 ,p_default_value => p_table_name
700 ,p_object_version_number => l_ip_object_version_number );
701
702 END IF;
703 END IF;
704
705
706
707 END LOOP;
708 CLOSE c_input_id;
709 CLOSE c1;
710
711
712
713 IF p_veh_type='L' THEN
714 l_lump(1):=' NIable LumpSum';
715 l_lump(2):=' Direct LumpSum';
716
717
718 FOR i in 1..l_lump.count
719 LOOP
720 OPEN c1(p_ele_name||l_lump(i));
721 LOOP
722
723 FETCH c1 INTO l_element_type_id,l_ele_obj_ver_number;
724 EXIT WHEN c1%NOTFOUND;
725
726 IF i=1 THEN
727 l_lumptemp:=p_ele_name||' NIable';
728 ELSE
729 l_lumptemp:=p_ele_name||' Direct Payment';
730 END IF;
731
732 pay_shadow_element_api.update_shadow_element
733 (p_validate => false
734 ,p_effective_date => p_ele_eff_start_date
735 ,p_element_type_id => l_element_type_id
736 ,p_element_name => l_lumptemp
737 ,p_object_version_number => l_ele_obj_ver_number
738 );
739
740 END LOOP;
741 CLOSE c1;
742 END LOOP;
743 END IF;
744
745
746
747
748
749
750 -------------------------------------------------------------------------
751 --
752
753
754
755
756
757 hr_utility.set_location(l_proc, 90);
758 --
759 --
760 hr_utility.set_location(l_proc, 110);
761 ---------------------------------------------------------------------------
762 ---------------------------- Generate Core Objects ------------------------
763 ---------------------------------------------------------------------------
764
765 pay_element_template_api.generate_part1
766 (p_validate => false
767 ,p_effective_date => p_ele_eff_start_date
768 ,p_hr_only => false
769 ,p_hr_to_payroll => false
770 ,p_template_id => l_template_id);
771 --
772 hr_utility.set_location(l_proc, 120);
773 --
774 pay_element_template_api.generate_part2
775 (p_validate => false
776 ,p_effective_date => p_ele_eff_start_date
777 ,p_template_id => l_template_id);
778 --
779
780 hr_utility.set_location(l_proc, 130);
781
782 --IF p_veh_type='C' OR p_veh_type='P' THEN
783 ---delete_balance_feeds('NIable','Pay Value');
784 create_balance_feeds;
785 --END IF;
786
787 --
788
789 l_base_element_type_id := get_object_id ('ELE', p_ele_name);
790
791 --Update input values with the formula for validation
792
793 IF p_veh_type='C' OR p_veh_type='P'
794 OR p_veh_type='CP' OR p_veh_type='CM' OR p_veh_type='PP'
795 OR p_veh_type='PM' THEN
796 upd_inputval_formula(l_base_element_type_id
797 ,'Claim End Date'
798 ,'PQP_VALIDATE_DATE');
799 upd_inputval_formula(l_base_element_type_id
800 ,'Purpose'
801 ,NULL);
802
803 END IF;
804
805 IF p_veh_type='P' THEN
806 upd_inputval_formula(l_base_element_type_id
807 ,'CO2 Emissions'
808 ,'CO2_EMISSIONS');
809
810 END IF;
811
812 -- IF p_veh_type='L' OR p_veh_type='P' THEN
813 -- upd_inputval_formula(l_base_element_type_id
814 -- ,'Table Name'
815 -- ,'PQP_VALIDATE_RATES_TABLE');
816
817 --END IF;
818
819 IF (p_veh_type='L' OR p_veh_type='P'OR p_veh_type='C') AND
820 p_table_indicator_flg= 'N' THEN
821 upd_inputval_formula(l_base_element_type_id
822 ,'User Rates Table'
823 ,'PQP_VALIDATE_RATES_TABLE');
824 ELSIF (p_veh_type='L' OR p_veh_type='P'OR p_veh_type='C') AND
825 p_table_indicator_flg= 'Y' THEN
826
827 NULL;
828
829 END IF;
830
831 pay_element_extra_info_api.create_element_extra_info
832 (p_element_type_id =>l_base_element_type_id
833 ,p_information_type => 'PQP_VEHICLE_MILEAGE_INFO'
834 , P_EEI_INFORMATION_CATEGORY =>'PQP_VEHICLE_MILEAGE_INFO'
835 ,p_eei_information1 => p_veh_type
836 ,p_eei_information2 => p_table_indicator_flg
837 ,p_eei_information3 => 'Y'
838 ,p_element_type_extra_info_id => l_eei_info_id
839 ,p_object_version_number => l_ovn_eei);
840
841
842 RETURN l_base_element_type_id;
843
844
845 --
846 END create_user_template;
847 --
848 --
849 --==========================================================================
850 -- Deletion procedure
851 --==========================================================================
852 --
853 PROCEDURE delete_user_template
854 (p_business_group_id in number
855 ,p_ele_type_id in number
856 ,p_ele_name in varchar2
857 ,p_effective_date in date
858 ) IS
859 --
860 l_template_id NUMBER(9);
861 l_proc varchar2(60) :='pay_uk_vehicle_template.delete_user_template';
862 l_eei_info_id number;
863 l_ovn_eei number;
864 --
865 CURSOR eei is
866 SELECT element_type_extra_info_id
867 FROM pay_element_type_extra_info petei
868 WHERE element_type_id=p_ele_type_id ;
869
870
871 CURSOR c1 is
872 SELECT template_id
873 FROM pay_element_templates
874 WHERE base_name = p_ele_name
875 AND business_group_id = p_business_group_id
876 AND template_type = 'U';
877 --
878 BEGIN
879 --
880 hr_utility.set_location('Entering :'||l_proc, 10);
881 --
882 OPEN eei;
883 LOOP
884 FETCH eei INTO l_eei_info_id ;
885 EXIT WHEN eei%NOTFOUND;
886
887
888 pay_element_extra_info_api.delete_element_extra_info
889 (p_validate => FALSE
890 ,p_element_type_extra_info_id => l_eei_info_id
891 ,p_object_version_number => l_ovn_eei);
892
893
894 END LOOP;
895 CLOSE eei;
896
897
898 FOR c1_rec in c1 loop
899 l_template_id := c1_rec.template_id;
900 END LOOP;
901 --
902
903 pay_element_template_api.delete_user_structure
904 (p_validate => false
905 ,p_drop_formula_packages => true
906 ,p_template_id => l_template_id);
907 --
908
909 hr_utility.set_location('Leaving :'||l_proc, 50);
910 --
911 END delete_user_template;
912 --
913 END pqp_uk_vehicle_template ;
914