[Home] [Help]
PACKAGE BODY: APPS.PAY_US_VERTEX_INTERFACE
Source
1 package body pay_us_vertex_interface as
2 /* $Header: payusvertexetusg.pkb 120.6 2007/12/06 21:32:24 tclewis noship $ */
3 /*
4 +======================================================================+
5 | Copyright (c) 1993 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +======================================================================+
9
10 Name :
11 Filename : payusvertexetusg.pkh
12 Description : This Package contains Procedures and Funbction required for
13 managing lement_type_usages for VERTEX and US_TAX_VERTEX
14 elements
15 Change List
16 -----------
17 Date Name Vers Bug No Description
18 ---- ---- ---- ------ -----------
19 12-DEC-2004 ppanda 115.0 Initial Version
20 02-FEB-2005 ppanda 115.2 4155064 Modified various procedure and
21 function to support Business
22 Group
23 04-MAR-2005 saikrish 115.4 4220606 Added Workers Compensation
24 30-DEC-2005 saurgupt 115.5 4896715 Modified the query in function
25 payroll_run_exist to improve performance.
26 24-MAY-2007 jdevasah 115.7 5937604 Modified select_tax_interface procedure
27 and Current_Tax_Interface function for
28 the new tax interface
29 Enahced+wage Accumulation.
30 05-DEC-2007 rnestor 115.8 6658836 PLS-00103 ERROR WHEN COMPILING
31 Had a Select inside the decode
32 */
33
34 PROCEDURE create_ele_tp_usg ( p_element_type_id in number
35 ,p_run_type_id in number
36 ,p_element_name in varchar2
37 ,p_run_type_name in varchar2
38 ,p_inclusion_flag in varchar2
39 ,p_effective_date in date
40 ,p_legislation_code in varchar2
41 ,p_business_group_id in number
42 ) IS
43
44 cursor c_check_ele_tp_usg ( cp_legislation_code varchar2
45 ,cp_business_group_id number
46 ,cp_element_type_id number
47 ,cp_run_type_id number
48 ,cp_effective_date date ) is
49
50
51
52 select 1
53 from pay_element_type_usages_f petu
54 where petu.element_type_id = cp_element_type_id
55 and petu.run_type_id = cp_run_type_id
56 and cp_effective_date between petu.effective_start_date
57 and petu.effective_end_date
58 and ( cp_legislation_code is not null and
59 petu.legislation_code = cp_legislation_code )
60 and ( cp_business_group_id is not null and
61 petu.business_group_id = cp_business_group_id );
62
63 l_element_type_usage_id number;
64 l_object_version_number number;
65 l_effective_start_date date;
66 l_effective_end_date date;
67 ln_exists number;
68 lv_usage_type pay_element_type_usages_f.usage_type%type := NULL;
69
70
71
72 BEGIN
73 --{
74 hr_utility.trace('3000 -> Start create_ele_tp_usg ');
75 hr_utility.trace('3010 -> Parameter Values ');
76 hr_utility.trace(' p_legislation_code : '||p_legislation_code);
77 hr_utility.trace(' p_business_group_id : '||p_business_group_id);
78 hr_utility.trace(' p_element_type_id : '||p_element_type_id);
79 hr_utility.trace(' p_element_name : '||p_element_name);
80 hr_utility.trace(' run_type_id : '||p_run_type_id);
81 hr_utility.trace(' run_type_Name : '||p_run_type_name);
82 hr_utility.trace(' p_effective_date : '||p_effective_date);
83
84 ln_exists := 0;
85 hr_utility.trace('3020 -> Checking for the existence of Element Type Usages ');
86 open c_check_ele_tp_usg( p_legislation_code
87 ,p_business_group_id
88 ,p_element_type_id
89 ,p_run_type_id
90 ,p_effective_date);
91 fetch c_check_ele_tp_usg into ln_exists;
92 close c_check_ele_tp_usg;
93 hr_utility.trace('3030 Element Type Usages Exists Flag : '||ln_exists);
94 if ln_exists = 0 then
95 --{
96 hr_utility.trace('3040 -> Creating Element_Type_USages for Element: '||
97 p_element_name ||' Run Type Type: '||p_run_type_name );
98 --
99 -- Calling API to create Element Type Usages
100 --
101 pay_element_type_usage_api.create_element_type_usage(
102 p_effective_date => p_effective_date
103 ,p_run_type_id => p_run_type_id
104 ,p_element_type_id => p_element_type_id
105 ,p_business_group_id => p_business_group_id
106 ,p_legislation_code => p_legislation_code
107 ,p_usage_type => lv_usage_type
108 ,p_inclusion_flag => p_inclusion_flag
109 ,p_element_type_usage_id => l_element_type_usage_id
110 ,p_object_version_number => l_object_version_number
111 ,p_effective_start_date => l_effective_start_date
112 ,p_effective_end_date => l_effective_end_date);
113 --
114 hr_utility.trace('3050 -> API Calls to Create Element_Type_Usages Ended ');
115 hr_utility.trace('3060 -> Successfully Element Type Usages created ');
116 --}
117 else
118 hr_utility.trace('3040 -> Element Type Exist creation skipped ');
119 end if;
120 hr_utility.trace('3999 -> End create_ele_tp_usg ');
121 --}
122 END create_ele_tp_usg;
123
124
125 PROCEDURE delete_ele_type_usages (p_element_name in varchar2,
126 p_business_group_id in number)
127 IS
128 BEGIN
129 --{
130 hr_utility.trace('1010 -> Start delete_ele_type_usages ');
131 delete from PAY_ELEMENT_TYPE_USAGES_F peu1
132 where EXISTS
133 (select 'x'
134 from PAY_ELEMENT_TYPE_USAGES_F peu2,
135 pay_run_types_f prt,
136 pay_element_types_F pet
137 where pet.element_name = p_element_name
138 and peu2.element_type_id = pet.element_type_id
139 and peu2.run_type_id = prt.run_type_id
140 and peu2.ELEMENT_TYPE_USAGE_ID = peu1.ELEMENT_TYPE_USAGE_ID
141 and peu2.legislation_code IS NULL
142 and peu2.business_group_id = p_business_group_id
143 );
144 hr_utility.trace('1020 -> End delete_ele_type_usages ');
145 --}
146 EXCEPTION
147 WHEN OTHERS THEN
148 hr_utility.trace('1030 -> WARNING: in delete_ele_type_usages ');
149 hr_utility.trace('1040 -> ETU for VERTEX Element <'||p_element_name ||
150 '> Does not exist ');
151 END delete_ele_type_usages;
152
153 FUNCTION payroll_run_exist (p_business_group_id in number
154 )Return varchar2
155 IS
156 l_payroll_exist varchar2(10);
157 BEGIN
158 l_payroll_exist := 'N';
159 --{
160 hr_utility.trace('1100 -> Start payroll_run_exist ');
161 hr_utility.trace('1105 -> Fetching records from PAY_PAYROLL_ACTIONS ');
162 select 'Y'
163 into l_payroll_exist
164 from dual
165 where exists( select 1
166 from PAY_PAYROLL_ACTIONS PPA
167 where PPA.action_type IN ('R','Q')
168 and PPA.action_status = 'C'
169 and PPA.business_group_id = p_business_group_id);
170 /*
171 select 'Y'
172 into l_payroll_exist
173 from PAY_PAYROLL_ACTIONS PPA
174 where PPA.action_type IN ('R','Q')
175 and PPA.action_status = 'C'
176 and PPA.business_group_id = p_business_group_id;
177 */
178 hr_utility.trace('1110 -> End payroll_run_exist ');
179 return l_payroll_exist;
180 --}
181 EXCEPTION
182 WHEN TOO_MANY_ROWS THEN
183 l_payroll_exist := 'Y';
184 hr_utility.trace('1130 -> TOO_MANY_ROWS_EXCEPTION In Function'||
185 ' payroll_run_exist ');
186 hr_utility.trace('1120 -> Payroll RUN Exist in this instance');
187 return l_payroll_exist;
188 WHEN OTHERS THEN
189 l_payroll_exist := 'N';
190 hr_utility.trace('1130 -> OTHER Exception payroll_run_exist ');
191 hr_utility.trace('1140 -> Payroll RUN Does not exist in this instance');
192 return l_payroll_exist;
193 END payroll_run_exist;
194
195 FUNCTION Current_Tax_Interface (p_lookup_code in varchar2,
196 p_business_group_id in number)
197 Return varchar2
198 IS
199 l_current_tax_interface varchar2(100);
200 l_ret_value varchar2(100);
201
202
203 l_WAGEACCUM_parm varchar2(2);
204
205 cursor c_parm_val_usg is
206
207 select parameter_value
208 from pay_action_parameters
209 where parameter_name = 'WAGE_ACCUMULATION_ENABLED';
210
211 BEGIN
212 -- hr_utility.trace_on(null, 'PPSELECTINTERFACE');
213 l_current_tax_interface := 'VERTEX';
214 l_ret_value := ' ';
215 --{
216 hr_utility.trace('1200 -> Start Current_Tax_Interface ');
217 hr_utility.trace('1205 -> Default Tax Interface ' ||l_current_tax_interface );
218 /* Bug#5937604: Need to set the status for the new tax interface WAGEACCUM.
219 Logic to calculate status of ENHACED interface is also modified */
220 /* Bug 6658836 PLS-00103 ERROR WHEN COMPILING PAYUSVERTEXETUSG.PKB
221 Had a select inside a decode statment RLN*/
222
223
224 open c_parm_val_usg;
225 fetch c_parm_val_usg into l_WAGEACCUM_parm;
226 if c_parm_val_usg%NOTFOUND THEN
227 l_WAGEACCUM_parm := 'N';
228 end if;
229 close c_parm_val_usg;
230
231 select --pet.element_name current_interface
232 decode(p_lookup_code,
233 'STANDARD', decode(pet.element_name,
234 'VERTEX','In Use','Not in Use'),
235 'ENHANCED', decode(pet.element_name,
236 'US_TAX_VERTEX',decode(l_WAGEACCUM_parm
237 ,'N','In Use','Not In Use')
238 ,'Not in Use'),
239 'WAGEACCUM', decode(pet.element_name,
240 'US_TAX_VERTEX',decode(l_WAGEACCUM_parm
241 ,'Y','In Use','Not In Use')
242 ,'Not in Use') ,' ')
243 into l_ret_value
244 from pay_element_types_f pet
245 where pet.element_name IN ('VERTEX',
246 'US_TAX_VERTEX')
247 and pet.legislation_code = 'US'
248 and pet.business_group_id IS NULL
249 and NOT EXISTS
250 ( select 'x'
251 from pay_element_type_usages_f petu
252 ,pay_element_types_f pet1
253 ,pay_run_types_f prt
254 where petu.element_type_id = pet1.element_type_id
255 and pet1.legislation_code = 'US'
256 and pet1.business_group_id IS NULL
257 and prt.legislation_code = 'US'
258 and prt.business_group_id IS NULL
259 and petu.run_type_id = prt.run_type_id
260 and petu.business_group_id = p_business_group_id --IS NULL
261 and petu.element_Type_id = pet.element_type_id
262 --and petu.legislation_code = 'US'
263 and petu.legislation_code IS NULL
264 and pet1.business_group_id IS NULL
265 and pet1.legislation_code = 'US'
266 and prt.legislation_code = 'US'
267 and exists
268 ( select 'x' from pay_run_types_f prt1
269 where prt1.run_type_name IN ('Regular Standard Run',
270 'Separate Payment Run',
271 'Tax Separate Run',
272 'Supplemental Standard Run',
273 'Regular',
274 'Supplemental')
275 and prt1.business_group_id IS NULL
276 and prt1.legislation_code = 'US'
277 and prt1.run_type_id = petu.run_type_id ));
278
279 hr_utility.trace('1207 -> Return Value '||l_ret_value);
280 hr_utility.trace('1210 -> End Current_Tax_Interface');
281 return l_ret_value;
282 --}
283 EXCEPTION
284 WHEN NO_DATA_FOUND
285 THEN
286 --{
287 select decode(p_lookup_code,
288 'STANDARD', 'In Use','Not in Use')
289 into l_ret_value
290 from DUAL;
291 hr_utility.trace('1215 -> Current Tax Interface element is VERTEX');
292 hr_utility.trace('1220 -> Return Value '||l_ret_value);
293 return l_ret_value;
294 --}
295 WHEN OTHERS THEN
296 --{
297
298 hr_utility.trace('1225 -> Other Exception'||substr(sqlerrm,1,30));
299 hr_utility.trace('1226 -> '||substr(sqlerrm,31,40));
300 hr_utility.trace('1230 -> Current Tax Interface element is VERTEX');
301 select decode(p_lookup_code,
302 'STANDARD', 'In Use','Not in Use')
303 into l_ret_value
304 from DUAL;
305 hr_utility.trace('1240 -> Return Value '||l_ret_value);
306 return l_ret_value;
307
308 --}
309 END Current_Tax_Interface;
310
311
312 FUNCTION vertex_eletype_usage_exist (p_element_name varchar2,
313 p_business_group_id number)
314 Return varchar2
315 IS
316 l_element_type_usage_exist varchar2(10);
317 BEGIN
318 l_element_type_usage_exist := 'N';
319 --{
320 hr_utility.trace('1300 -> Entering vertex_eletype_usage_exist ');
321 select 'Y'
322 into l_element_type_usage_exist
323 from pay_element_types_f pet
324 where element_name = p_element_name
325 and business_group_id IS NULL
326 and legislation_code = 'US'
327 and exists
328 ( select 'x'
329 from pay_element_type_usages_f petu
330 ,pay_element_types_f pet1
331 ,pay_run_types_f prt
332 where petu.element_type_id = pet1.element_type_id
333 and pet1.legislation_code = 'US'
334 and prt.legislation_code = 'US'
335 and petu.run_type_id = prt.run_type_id
336 and petu.business_group_id = p_business_group_id --IS NULL
337 and petu.legislation_code IS NULL --= 'US'
338 and petu.element_Type_id = pet.element_type_id
339 and exists
340 ( select 'x' from pay_run_types_f prt1
341 where prt1.run_type_name IN ('Regular Standard Run',
342 'Separate Payment Run',
343 'Tax Separate Run',
344 'Supplemental Standard Run',
345 'Regular',
346 'Supplemental')
347 and prt1.business_group_id IS NULL
348 and prt1.legislation_code = 'US'
349 and prt1.run_type_id = petu.run_type_id ));
350 hr_utility.trace('1310 -> Element Type Usage Exist for Element '|| p_element_name);
351 hr_utility.trace('1320 -> Leaving vertex_eletype_usage_exist ');
352 return l_element_type_usage_exist;
353 --}
354 EXCEPTION
355 WHEN NO_DATA_FOUND
356 THEN
357 --{
358 l_element_type_usage_exist := 'N';
359 hr_utility.trace('1330 -> Exception NO_DATA_FOUND ');
360 hr_utility.trace('1340 -> Element Type Usages do not Exist for Element '|| p_element_name);
361 hr_utility.trace('1350 -> Leaving vertex_eletype_usage_exist ');
362 return l_element_type_usage_exist;
363 --}
364 WHEN OTHERS THEN
365 --{
366 l_element_type_usage_exist := 'N';
367 hr_utility.trace('1330 -> Exception OTHERS ');
368 hr_utility.trace('1340 -> Element Type Usages do not Exist for Element '|| p_element_name);
369 hr_utility.trace('1350 -> Leaving vertex_eletype_usage_exist ');
370 return l_element_type_usage_exist;
371 --}
372 END vertex_eletype_usage_exist;
373
374
375
376
377 -- This procedure Excudes the TAX Element from processing depending on
378 -- customer selection
379 -- IF Customer selection is STANDARD interface
380 -- New Tax Element US_TAX_VERTEX will be excluded
381 -- ELSIF Customer selection is ENHANCED interface
382 -- Old Tax Element VERTEX will be excluded
383 PROCEDURE select_tax_interface(errbuf OUT nocopy VARCHAR2,
384 retcode OUT nocopy NUMBER,
385 p_business_group_id IN NUMBER,
386 p_vertex_interface IN VARCHAR2)
387 IS
388 --
389 -- Run Types used for VERTEX or US_TAX_VERTEX Elements
390
391 --
392 CURSOR c_run_type is
393 select prt.* from pay_run_types_f prt
394 where prt.run_type_name IN ('Regular Standard Run',
395 'Separate Payment Run',
396 'Tax Separate Run',
397 'Supplemental Standard Run',
398 'Regular',
399 'Supplemental')
400 and prt.business_group_id IS NULL
401 and prt.legislation_code = 'US';
402 --
403 -- Element Type details
404 --
405 cursor c_vertex_elements (c_element_name varchar2)
406 is
407 SELECT pet.ELEMENT_TYPE_ID,
408 pet.element_name,
409 pet.business_group_id,
410 pet.legislation_code,
411 pet.effective_start_date effective_date
412 FROM PAY_ELEMENT_TYPES_F pet
413 WHERE pet.ELEMENT_NAME = c_element_name
414 AND pet.LEGISLATION_CODE = 'US'
415 AND pet.business_group_id IS NULL
416 ORDER by pet.element_name;
417 --
418 -- Element Type Usages Details
419 --
420 ln_commit_counter number;
421 l_wage_exists number;
422 lv_exists varchar2(1);
423 lv_interoperable_flag varchar2(10);
424 l_field_name PAY_LEGISLATIVE_FIELD_INFO.field_name%type;
425 l_payroll_exist varchar2(10);
426 l_vertex_etu_exist varchar2(10);
427 l_us_tax_vertex_etu_exist varchar2(10);
428 lv_inclusion_flag pay_element_type_usages_f.inclusion_flag%type;
429 l_null_legislation_code pay_element_types_f.legislation_code%type;
430 BEGIN
431 -- hr_utility.trace_on(null, 'PPSELTAX');
432
433 hr_utility.trace('2000 -> Start payustaxinterface.sql ');
434 hr_utility.trace('2005 -> Parameter Values ');
435 hr_utility.trace('2006 -> Vertex_Interface Required '||p_vertex_interface);
436 hr_utility.trace('2006 -> setting for Business Group '||to_char(p_business_group_id));
437 l_null_legislation_code := NULL;
438 --{
439 -- This section used to set the env for Running the Startup API
440 --
441 hr_utility.trace('2010 -> Start Setting env for Running API ');
442 hr_startup_data_api_support.enable_startup_mode('USER');
443 hr_startup_data_api_support.create_owner_definition('PAY');
444 BEGIN
445 SELECT field_name
446 INTO l_field_name
447 FROM PAY_LEGISLATIVE_FIELD_INFO
448 WHERE FIELD_NAME = 'ET_USAGE'
449 AND LEGISLATION_CODE = 'US';
450 EXCEPTION
451 WHEN NO_DATA_FOUND THEN
452 INSERT INTO PAY_LEGISLATIVE_FIELD_INFO
453 (FIELD_NAME,
454 LEGISLATION_CODE,
455 RULE_TYPE,
456 RULE_MODE)
457 VALUES
458 ('ET_USAGE',
459 'US',
460 'ENABLE',
461 'Y');
462 END;
463 hr_utility.trace('2020 -> Setup completed for Running API ');
464
465 IF p_vertex_interface = 'STANDARD'
466 THEN
467 hr_utility.trace('2050 -> Payroll Run exist in this Instance');
468 /*Bug#5937604: Disable WAGE_ACCUMULATION_ENABLED parameter */
469 update pay_action_parameters
470 set parameter_value= 'N'
471 where parameter_name='WAGE_ACCUMULATION_ENABLED';
472
473 l_vertex_etu_exist :=
474 pay_us_vertex_interface.vertex_eletype_usage_exist('VERTEX',
475 p_business_group_id);
476 l_us_tax_vertex_etu_exist :=
477 pay_us_vertex_interface.vertex_eletype_usage_exist('US_TAX_VERTEX',
478 p_business_group_id);
479 IF l_us_tax_vertex_etu_exist = 'N'
480 THEN
481 --{
482 IF l_vertex_etu_exist = 'Y'
483 THEN
484 hr_utility.trace('2060 -> Deleting Element Type Usages ');
485 pay_us_vertex_interface.delete_ele_type_usages('VERTEX', p_business_group_id);
486 /* 4220606*/
487 pay_us_vertex_interface.delete_ele_type_usages('Workers Compensation', p_business_group_id);
488
489 END IF;
490 --
491 -- This piece of code for creating Element_Type_Usages for
492 -- Vertex Element US_TAX_VERTEX
493 hr_utility.trace('2070 -> Tax Interface Script for creating');
494 hr_utility.trace('2080 -> Element_Type_Usages for US_TAX_VERTEX');
495 for earn in c_vertex_elements('US_TAX_VERTEX')
496 loop
497 lv_inclusion_flag := 'N';
498 hr_utility.trace('2090 -> Inclusion Flag '||lv_inclusion_flag);
499 for run_type in c_run_type
500 loop
501 --{
502 hr_utility.trace('2100 -> Element Type Name '||
503 earn.element_name);
504 hr_utility.trace('2110 -> Run Type Name '||
505 run_type.run_type_name);
506 hr_utility.trace('2120 -> Calling '
507 || 'pay_us_vertex_interface.create_ele_tp_usg ');
508 hr_utility.trace('2130 -> For creating element_type_usages '
509 || 'for '|| earn.element_name);
510 pay_us_vertex_interface.create_ele_tp_usg
511 ( p_element_type_id => earn.element_type_id
512 ,p_run_type_id => run_type.run_type_id
513 ,p_element_name => earn.element_name
514 ,p_run_type_name => run_type.run_type_name
515 ,p_inclusion_flag => lv_inclusion_flag
516 ,p_effective_date =>
517 run_type.effective_start_date
518 ,p_legislation_code => l_null_legislation_code
519 --earn.legislation_code
520 ,p_business_group_id => p_business_group_id);
521 -- earn.business_group_id);
522 hr_utility.trace('2130 -> US_TAX_VERTEX element excluded');
523 --}
524 end loop;
525 end loop;
526 ELSIF l_us_tax_vertex_etu_exist = 'Y'
527 THEN
528 --{
529 IF l_vertex_etu_exist = 'Y'
530 THEN
531 hr_utility.trace('2060 -> Deleting Element Type Usages ');
532 pay_us_vertex_interface.delete_ele_type_usages('VERTEX',p_business_group_id);
533 /* 4220606 */
534 pay_us_vertex_interface.delete_ele_type_usages('Workers Compensation',p_business_group_id);
535 END IF;
536 -- VERTEX Element is excluded and US_TAX_VERTEX is in use
537 hr_utility.trace('2060 -> Tax Element US_TAX_VERTEX is Excluded');
538 hr_utility.trace('2070 -> Tax element VERTEX is in use');
539 END IF;
540 ELSIF p_vertex_interface = 'ENHANCED' or p_vertex_interface = 'WAGEACCUM'
541 THEN
542 hr_utility.trace('2050 -> Payroll Run exist in this Instance');
543
544 IF p_vertex_interface = 'ENHANCED' THEN
545 /*Bug#5937604: update pay_action_paramters */
546 update pay_action_parameters
547 set parameter_value= 'N'
548 where parameter_name='WAGE_ACCUMULATION_ENABLED';
549 ELSIF p_vertex_interface = 'WAGEACCUM' then
550 /*Bug#5937604: Insert on pay_action_paramters */
551 select count(*) into l_wage_exists from pay_action_parameters
552 where parameter_name='WAGE_ACCUMULATION_ENABLED';
553
554 if l_wage_exists = 0 then
555 insert into pay_action_parameters
556 (
557 parameter_name
558 ,parameter_value
559 )
560 values
561 (
562 'WAGE_ACCUMULATION_ENABLED'
563 ,'Y'
564 );
565
566 else
567 update pay_action_parameters
568 set parameter_value= 'Y'
569 where parameter_name='WAGE_ACCUMULATION_ENABLED';
570 end if;
571
572 END IF;
573
574 l_vertex_etu_exist :=
575 pay_us_vertex_interface.vertex_eletype_usage_exist('VERTEX',
576 p_business_group_id);
577 l_us_tax_vertex_etu_exist :=
578 pay_us_vertex_interface.vertex_eletype_usage_exist('US_TAX_VERTEX',
579 p_business_group_id);
580 IF l_vertex_etu_exist = 'N'
581 THEN
582 --{
583 IF l_us_tax_vertex_etu_exist = 'Y'
584 THEN
585 hr_utility.trace(
586 '2060 -> Deleting Element Type Usages US_TAX_VERTEX');
587 pay_us_vertex_interface.delete_ele_type_usages('US_TAX_VERTEX',
588 p_business_group_id);
589 END IF;
590 --
591 -- This piece of code for creating Element_Type_Usages for
592 -- Vertex Element VERTEX
593 hr_utility.trace('2070 -> Tax Interface Script for creating');
594 hr_utility.trace('2080 -> Element_Type_Usages for VERTEX');
595 for earn in c_vertex_elements('VERTEX')
596 loop
597 lv_inclusion_flag := 'N';
598 hr_utility.trace('2090 -> Inclusion Flag '||lv_inclusion_flag);
599 for run_type in c_run_type
600 loop
601 --{
602 hr_utility.trace('2100 -> Element Type Name '||
603 earn.element_name);
604 hr_utility.trace('2110 -> Run Type Name '||
605 run_type.run_type_name);
606 hr_utility.trace('2120 -> Calling '
607 || 'pay_us_vertex_interface.create_ele_tp_usg ');
608 hr_utility.trace('2130 -> For creating element_type_usages '
609 || 'for '|| earn.element_name);
610 pay_us_vertex_interface.create_ele_tp_usg
611 ( p_element_type_id => earn.element_type_id
612 ,p_run_type_id => run_type.run_type_id
613 ,p_element_name => earn.element_name
614 ,p_run_type_name => run_type.run_type_name
615 ,p_inclusion_flag => lv_inclusion_flag
616 ,p_effective_date =>
617 run_type.effective_start_date
618 ,p_legislation_code => l_null_legislation_code
619 --earn.legislation_code
620 ,p_business_group_id => p_business_group_id);
621 -- earn.business_group_id);
622 hr_utility.trace('2130 -> VERTEX element excluded');
623 --}
624 end loop;
625 end loop;
626
627 /* 4220606 */
628 --
629 -- This piece of code for creating Element_Type_Usages for
630 -- Workers Compensation
631 hr_utility.trace('2070 -> Tax Interface Script for creating');
632 hr_utility.trace('2080 -> Element_Type_Usages for Workers Compensation');
633 for earn in c_vertex_elements('Workers Compensation')
634 loop
635 lv_inclusion_flag := 'N';
636 hr_utility.trace('2090 -> Inclusion Flag '||lv_inclusion_flag);
637 for run_type in c_run_type
638 loop
639 --{
640 hr_utility.trace('2100 -> Element Type Name '||
641 earn.element_name);
642 hr_utility.trace('2110 -> Run Type Name '||
643 run_type.run_type_name);
644 hr_utility.trace('2120 -> Calling '
645 || 'pay_us_vertex_interface.create_ele_tp_usg ');
646 hr_utility.trace('2130 -> For creating element_type_usages '
647 || 'for '|| earn.element_name);
648 pay_us_vertex_interface.create_ele_tp_usg
649 ( p_element_type_id => earn.element_type_id
650 ,p_run_type_id => run_type.run_type_id
651 ,p_element_name => earn.element_name
652 ,p_run_type_name => run_type.run_type_name
653 ,p_inclusion_flag => lv_inclusion_flag
654 ,p_effective_date =>
655 run_type.effective_start_date
656 ,p_legislation_code => l_null_legislation_code
657 --earn.legislation_code
658 ,p_business_group_id => p_business_group_id);
659 -- earn.business_group_id);
660 hr_utility.trace('2130 -> VERTEX element excluded');
661 --}
662 end loop;
663 end loop;
664 /* 42206060 */
665 ELSIF l_vertex_etu_exist = 'Y'
666 THEN
667 --{
668 IF l_us_tax_vertex_etu_exist = 'Y'
669 THEN
670 hr_utility.trace('2060 -> Deleting Element Type Usages ');
671 pay_us_vertex_interface.delete_ele_type_usages('US_TAX_VERTEX',
672 p_business_group_id);
673 END IF;
674 -- VERTEX Element is excluded and US_TAX_VERTEX is in use
675 hr_utility.trace('2060 -> Tax Element VERTEX is Excluded');
676 hr_utility.trace('2070 -> Tax element US_TAX_VERTEX is in use');
677 END IF;
678 END IF;
679 --}
680 END select_tax_interface;
681 END pay_us_vertex_interface;