[Home] [Help]
PACKAGE BODY: APPS.HR_LEGISLATION_LOCAL
Source
1 PACKAGE body hr_legislation_local AS
2 /* $Header: pelegloc.pkb 120.1.12000000.1 2007/01/21 23:59:37 appldev ship $ */
3 --
4 -- Copyright (c) Oracle Corporation 1991, 1992, 1993. All rights reserved.
5 --
6 -- NAME : hr_legislation_local.pkb
7 --
8 -- DESCRIPTION
9 -- Procedures used for the delivery of legislative startup data. The
10 -- same procedures are also used for legislative refreshes.
11 -- This package is used to make specific calls to packages or procedures
12 -- created by localization teams.
13 -- MODIFIED
14 -- 80.1 Ian Carline 14-09-1993 - Created
15 -- 80.2 Ian Carline 15-11-1993 - Debugged for US Bechtel delivery
16 -- 80.3 Ian Carline 23-11-1993 - Removed Geocodes logic
17 -- 80.4 Ian Carline 07-12-1993 - Added show errors logic Geocodes logic
18 -- 80.5 Rod Fine 16-12-1993 - Put AS on same line as CREATE stmt
19 -- to workaround export WWBUG #178613.
20 --
21 -- 70.6 Ian Carline 06-jun-1994 - per 7.0 and 8.0 merged.
22 -- rewrite.
23 -- 70.8 Ian Carline 04-Aug-1994 - Added GB specific logic to delete
24 -- from some control tables when payroll
25 -- is not installed.
26 -- 70.9 Rod Fine 06-Oct-1994 - Added column FS_LOOKUP_TYPE to
27 -- PAY_STATE_RULES table installation
28 -- procedure.
29 -- 70.10 Rod Fine 23-Mar-1995 - Added column CLASSIFICATION_ID to
30 -- PAY_TAXABILITY_RULES table
31 -- installation procedure.
32 -- 70.11 Rod Fine 07-Apr-1995 - Added extra check on classification_id
33 -- to PAY_TAXABILITY_RULES transfer_row
34 -- procedure.
35 -- 70.12 rfine 27-Mar-96 353225 When delivering PAY_STATE_RULES, update
36 -- the row if it already exists, rather
37 -- than simply not delivering it.
38 -- 70.12 M. Stewart 23-Sep-1996 - Updated table names from STU_ to HR_S_
39 -- 70.13 Tim Eyres 02-Jan-1997 - Moved arcs header to directly after
40 -- 'create or replace' line
41 -- Fix to bug 434902
42 -- 70.15 Tim Eyres 02-Jan-1997 Correction to version number
43 -- 110.1 mstewart 23-07-1997 Removed show error and select from
44 -- user errors statements (R11)
45 -- (R10 version # 70.16)
46 -- 115.1 RAMURTHY 17-03-1999 Modified procedure install_tax_rules
47 -- to insert with taxability_rules_date_id
48 -- and legislation_code, since these new
49 -- column are not in startup yet. This is
50 -- a temporary fix.
51 -- 115.12 VMehta 14-06-1999 Commented out code to get the formula id
52 -- while inserting into
53 -- pay_magnetic_records in
54 -- install_us_new procedure
55 -- 115.13 MReid 24-06-1999 Added missing columns to report format
56 -- mappings
57 -- 115.14 meshah/vmehta 09-29-1999 Modified install_us_new procedure
58 -- added the logic to include hr_report_
59 -- lookups in the startup data.
60 -- Added logic to conditionally insert data into
61 -- JIT Tables. The data is to be inserted only if
62 -- this is a fresh install (not to be done during
63 -- upgrade from a previous release).
64 -- 115.15 tbattoo 27-oct-1999 Removed installation of hr_magnetic_blocks,
65 -- hr_s_magnetic_Records,
66 -- hr_s_report_format_mappings
67 -- They have been added to pelegins.pkb
68 -- 115.16 vmehta 10-nov-1999 Added two new functions
69 -- (decode_us_element_information and
70 -- translate_us_ele_dev_df) to tranfer the
71 -- balance_type_id and element_type_id
72 -- stored in the element_information*
73 -- columns of pay_element_types_f table
74 -- 115.17 vmehta 26-dec-1999 Changed function install_us_new to
75 -- correctly populate the ids in various
76 -- tables to maintain a history of the
77 -- date-tracked changes in the correct
78 -- fashion
79 -- 115.18 vmehta 03-feb-2000 Modified install_us_new to delete from
80 -- the tables only if need to transfer
81 -- data
82 -- 115.19 tbattoo 08-Feb-2000 changed crt_exc so calls
83 -- hr_legislation.insert_hr_stu_exceptions
84 -- 115.20 RThirlby 11-APR-2000 Added translate_ca_ele_dev_df. This is
85 -- a copy of translate_us_ele_dev_df
86 -- modified for CA use.
87 -- 115.22 RThirlby 16-JUN-2000 Modifiled install_tax_rules so that it
88 -- can be used by Canada.
89 -- 115.23 JARTHURT 28-OCT-2000 Modified procedure install_tax_rules
90 -- to insert with taxability_rules_date_id
91 -- and legislation_code for Canadian
92 -- legislation as it currently works for
93 -- US.
94 -- 115.24 VMEHTA 31-OCT-2000 Modified install_us_new to correctly
95 -- install garnishment_fee_rules
96 -- (pay_us_garn_fee_rules_f). Inserted the
97 -- logic to exit from the pgfr loop when
98 -- a record is found and also to set the
99 -- gfr_exist flag to 'N' at the beginning
100 -- of each iteration of the gfr loop.
101 -- Ref Bug 1459362.
102 -- 115.25 ALOGUE 06-NOV-2000 Only installs legislation_rules from
103 -- hr_s tables if the pay table is empty.
104 -- 115.26 SSattini 30-Jan-2001 Modified procedure install_tax_rules
105 -- to fix bug: 1618263. Pay_Taxability_
106 -- rules data not installed for
107 -- legislation_code 'CA' if there is
108 -- another legislation_code data already
109 -- installed.
110 -- 115.27 divicker May 2001 Support for parallel hrglobal
111 -- 115.28 divicker 01-Jun-2001 Fix to install_tax_rules
112 -- 115.29 divicker 05-Jun-2001 Added valid_date_from,to to
113 -- ID selection in install_tax_rules
114 -- 115.30 vmehta 12-Jun-2001 Commented out delivering of US JIT
115 -- data from install_us_new proc.
116 -- This is now done through the
117 -- 'parallel' hrglobal.drv
118 -- 115.31 divicker 25-OCT-2001 remove commented out code
119 -- 115.32 divicker 21-NOV-2001 performance
120 -- 115.33 ekim 06-MAY-2002 Removed update of
121 -- WC_EXECUTIVE_WEEKLY_MAX
122 -- in install_state_rules procedure.
123 -- 115.34 divicker 09-MAY-2002 Support for dual rule_mode LEG_RULES
124 -- 115.35 divicker 09-MAY-2002 dbdrv added
125 -- 115.36 pganguly 31-MAY-2002 Added a call to install_us_new in
126 -- the Canadian section.
127 -- 115.37 pganguly 13-JUN-2002 Added set verify off/whenever
128 -- oserror
129 -- 115.38 mreid 24-JUN-2002 Modified update_uid for wc surcharge
130 -- due to bug 2429360
131 -- 115.39 pganguly 18-JUL-2002 Removed the call to install_us_new
132 -- from Canadian Section.
133 -- 115.40 divicker 15-JUL-2003 Added support for translation of
134 -- CWK_S rule types in pay_leg_rules
135 -- 115.41 ahanda 16-OCT-2004 Changed procedure install_us_new
136 -- The values assigned to hr_s_tab and
137 -- pay_tab were not in sync causing an
138 -- issue (Bug 3955832).
139 -- 115.42 divicker 24-FEB-2005 Trace improvements.
140 -- 115.43 divicker 10-AUG-2005 Debug switch
141 ------------------------------------------------------------------------------
142
143 driving_legislation varchar2(30);
144
145 --****************************************************************************
146 -- INSTALLATION PROCEDURE FOR : PAY_STATE_RULES
147 --****************************************************************************
148
149 PROCEDURE install_state_rules(p_phase IN number)
150 ------------------------------------------------
151 IS
152 -- Install procedure to transfer startup state tax rules into the live
153 -- tables. This routine is written purely for the US localization team.
154 -- The object PAY_STATE_RULES is used only in the US payroll system
155
156 l_null_return varchar2(1); -- For 'select null' statements
157 l_new_surrogate_key number(15); -- New surrogate key for the delivery row
158
159
160 CURSOR stu -- Selects all rows from startup entity
161 IS
162 select STATE_CODE c_true_key
163 , FIPS_CODE
164 , NAME
165 , JURISDICTION_CODE
166 , HEAD_TAX_PERIOD
167 , WC_EXECUTIVE_WEEKLY_MAX
168 , FS_LOOKUP_TYPE
169 , LAST_UPDATE_DATE
170 , LAST_UPDATED_BY
171 , LAST_UPDATE_LOGIN
172 , CREATED_BY
173 , CREATION_DATE
174 , rowid
175 from hr_s_state_rules;
176
177 stu_rec stu%ROWTYPE;
178
179 PROCEDURE remove
180 ----------------
181 IS
182
183 -- Remove a row from either the startup tables or the installed tables
184
185 BEGIN
186
187
188 delete from hr_s_state_rules
189 where rowid = stu_rec.rowid;
190
191 END remove;
192
193 PROCEDURE transfer_row
194 ----------------------
195 IS
196 -- Check if a delivered row is needed and insert into the
197 -- live tables if it is
198
199 BEGIN
200
201
202 IF p_phase = 1 THEN
203 return;
204 END IF;
205
206
207 --
208 -- #353225. See if the state information exists. If so then update it.
209 -- This is new code put in at the request of
210 -- US Pay - previously the code wouldn't handle updates - it
211 -- only inserted new rows. RMF 27-Mar-96.
212 --
213
214 update pay_state_rules
215 set FIPS_CODE = stu_rec.FIPS_CODE
216 , NAME = stu_rec.NAME
217 , JURISDICTION_CODE = stu_rec.JURISDICTION_CODE
218 , HEAD_TAX_PERIOD = stu_rec.HEAD_TAX_PERIOD
219 , FS_LOOKUP_TYPE = stu_rec.FS_LOOKUP_TYPE
220 , LAST_UPDATE_DATE = stu_rec.LAST_UPDATE_DATE
221 , LAST_UPDATED_BY = stu_rec.LAST_UPDATED_BY
222 , LAST_UPDATE_LOGIN = stu_rec.LAST_UPDATE_LOGIN
223 , CREATED_BY = stu_rec.CREATED_BY
224 , CREATION_DATE = stu_rec.CREATION_DATE
225 where state_code = stu_rec.c_true_key;
226
227 IF SQL%NOTFOUND THEN
228
229 -- Row does not exist so insert
230
231
232 insert into pay_state_rules
233 (STATE_CODE
234 ,FIPS_CODE
235 ,NAME
236 ,JURISDICTION_CODE
237 ,HEAD_TAX_PERIOD
238 ,WC_EXECUTIVE_WEEKLY_MAX
239 ,FS_LOOKUP_TYPE
240 ,LAST_UPDATE_DATE
241 ,LAST_UPDATED_BY
242 ,LAST_UPDATE_LOGIN
243 ,CREATED_BY
244 ,CREATION_DATE
245 )
246 values
247 (stu_rec.c_true_key
248 ,stu_rec.FIPS_CODE
249 ,stu_rec.NAME
250 ,stu_rec.JURISDICTION_CODE
251 ,stu_rec.HEAD_TAX_PERIOD
252 ,stu_rec.WC_EXECUTIVE_WEEKLY_MAX
253 ,stu_rec.FS_LOOKUP_TYPE
254 ,stu_rec.LAST_UPDATE_DATE
255 ,stu_rec.LAST_UPDATED_BY
256 ,stu_rec.LAST_UPDATE_LOGIN
257 ,stu_rec.CREATED_BY
258 ,stu_rec.CREATION_DATE
259 );
260
261 END IF;
262
263 remove;
264
265 END transfer_row;
266
267 BEGIN
268 -- This is the main loop to perform the installation logic. A cursor
269 -- is opened to control the loop, and each row returned is placed
270 -- into a record defined within the main procedure so each sub
271 -- procedure has full access to all returned columns. For each
272 -- new row returned, a new savepoint is declared. If at any time
273 -- the row is in error a rollback is performed to the savepoint
274 -- and the next row is returned. Ownership details are checked and
275 -- if the row is required then the surrogate id is updated and the
276 -- main transfer logic is called.
277
278 FOR delivered IN stu LOOP
279
280 -- Uses main cursor stu to impilicity define a record
281
282
283 savepoint new_state_rule;
284
285 stu_rec := delivered;
286
287
288 transfer_row;
289
290 END LOOP;
291
292 END install_state_rules;
293
294 --****************************************************************************
295 -- INSTALLATION PROCEDURE FOR : PAY_TAXABILITY_RULES
296 --****************************************************************************
297
298 PROCEDURE install_tax_rules(p_phase IN number)
299 ----------------------------------------------
300 IS
301 -- Install procedure to transfer startup taxability rules into the live
302 -- tables. This routine is written purely for the US localization team.
303 -- The object PAY_TAXABILITY_RULES is used only in the US payroll system
304 --
305 -- Canadian Payroll also uses pay_taxability_rules, so I have updated
306 -- RM's changes so they can be used for CA.
307
308 l_null_return varchar2(1); -- For 'select null' statements
309 l_new_surrogate_key number(15); -- New surrogate key for the delivery row
310
311
312 /* *** */ l_trd_id number;
313 l_num_rules number;
314 l_leg_code varchar2(5);
315
316 CURSOR stu -- Selects all rows from startup entity
317 IS
318
319 select jurisdiction_code
320 , tax_type
321 , tax_category
322 , classification_id
323 , last_update_date
324 , last_updated_by
325 , last_update_login
326 , created_by
327 , creation_date
328 , rowid
329 , legislation_code
330 , taxability_rules_date_id
331 from hr_s_taxability_rules;
332
333 stu_rec stu%ROWTYPE;
334
335 CURSOR c_legs is
336 select legislation_code
337 from hr_s_history;
338
339 PROCEDURE remove
340 ----------------
341 IS
342 -- Remove a row from either the startup tables or the installed
343 -- tables
344
345 BEGIN
346
347
348 delete from hr_s_taxability_rules
349 where rowid = stu_rec.rowid;
350
351 END remove;
352
353 PROCEDURE transfer_row
354 ----------------------
355 IS
356 -- Check if a delivered row is needed and insert into the
357 -- live tables if it is
358
359 BEGIN
360
361
362 -- See if the taxability information exists. If so then remove from the
363 -- delivery tables, otherwise insert into the live table.
364
365 /* *** Added by RAMURTHY *** */
366
367 BEGIN
368 select taxability_rules_date_id
369 into l_trd_id
370 from pay_taxability_rules_dates
371 where legislation_code = stu_rec.legislation_code
372 and trunc(valid_date_from) = trunc(to_date('0001/01/01', 'YYYY/MM/DD'))
373 and trunc(valid_date_to) = trunc(to_date('4712/12/31', 'YYYY/MM/DD'));
374
375 EXCEPTION
376 when NO_DATA_FOUND then
377
378 select pay_taxability_rules_dates_s.nextval
379 into l_trd_id
380 from dual;
381
382 insert into pay_taxability_rules_dates
383 ( taxability_rules_date_id,
384 valid_date_from,
385 valid_date_to,
386 legislation_code)
387 Values
388 ( l_trd_id,
389 to_date('0001/01/01', 'YYYY/MM/DD'),
390 to_date('4712/12/31', 'YYYY/MM/DD'),
391 stu_rec.legislation_code);
392 END;
393
394 /* *** End of code ddded by RAMURTHY *** */
395
396
397 BEGIN
398
399 select null
400 into l_null_return
401 from pay_taxability_rules
402 where jurisdiction_code = stu_rec.jurisdiction_code
403 and tax_type = stu_rec.tax_type
404 and classification_id = stu_rec.classification_id
405 and tax_category = stu_rec.tax_category;
406
407 -- The row exists
408
409 remove;
410
411 return;
412
413 EXCEPTION WHEN NO_DATA_FOUND THEN
414
415 -- Needs to be inserted
416
417 IF p_phase = 1 THEN
418 return;
419 END IF;
420
421
422 --
423 insert into pay_taxability_rules
424 (jurisdiction_code
425 ,tax_type
426 ,tax_category
427 ,classification_id
428 ,last_update_date
429 ,last_updated_by
430 ,last_update_login
431 ,created_by
432 ,creation_date
433 /* *** */ ,legislation_code
434 /* *** */ ,taxability_rules_date_id
435 )
436 values
437 (stu_rec.jurisdiction_code
438 ,stu_rec.tax_type
439 ,stu_rec.tax_category
440 ,stu_rec.classification_id
441 ,stu_rec.last_update_date
442 ,stu_rec.last_updated_by
443 ,stu_rec.last_update_login
444 ,stu_rec.created_by
445 ,stu_rec.creation_date
446 /* *** */ ,stu_rec.legislation_code
447 /* *** */ ,l_trd_id
448 );
449
450 remove;
451
452 END;
453
454 END transfer_row;
455
456 BEGIN
457
458 --
459 -- If any rows in pay_taxability_rules do not deliver any rows
460 -- from hr_s_taxability_rules
461 --
462 -- Bug fix for bug no: 1618263
463 -- Added this code because it was not populating the records into pay_
464 -- taxability_rules table for legislation_code 'CA',
465 -- if there is another legislation_code data already
466 -- installed . Earlier the count(*) from pay_taxability_rules
467 -- will show the count irrespecitve of legislation_code and deletes the
468 -- hr_s_taxability_rules data and the 'stu' cursor will not
469 -- get executed.
470 /* Started code here for bug fix: 1618263 */
471
472 for legs in c_legs loop
473
474 l_leg_code := legs.legislation_code;
475
476 select count(*)
477 into l_num_rules
478 from pay_taxability_rules
479 where legislation_code = l_leg_code;
480
481 /* End code here for bug fix: 1618263 */
482
483 if (l_num_rules <> 0) then
484
485 delete from hr_s_taxability_rules
486 where legislation_code = l_leg_code;
487
488 end if;
489
490 -- This is the main loop to perform the installation logic. A cursor
491 -- is opened to control the loop, and each row returned is placed
492 -- into a record defined within the main procedure so each sub
493 -- procedure has full access to all returrned columns. For each
494 -- new row returned, a new savepoint is declared. If at any time
495 -- the row is in error a rollback iss performed to the savepoint
496 -- and the next row is returned. Ownership details are checked and
497 -- if the row is required then the surrogate id is updated and the
498 -- main transfer logic is called.
499
500 FOR delivered IN stu LOOP
501
502 -- Uses main cursor stu to impilicity define a record
503
504
505 savepoint new_tax_rule;
506
507 stu_rec := delivered;
508
509
510 transfer_row;
511
512 END LOOP;
513
514 end loop; -- leg loop
515
516 END install_tax_rules;
517
518 --****************************************************************************
519 -- INSTALLATION PROCEDURE FOR : PAY_WC_STATE_SURCHARGES
520 --****************************************************************************
521
522 PROCEDURE install_surcharges(p_phase IN number)
523 -----------------------------------------------
524 IS
525 -- Install procedure to transfer startup wc state surcharges into
526 -- a live account.
527
528 l_null_return varchar2(1); -- For 'select null' statements
529 l_add_to_rt varchar2(30); -- For likeness test
530 l_name varchar2(30); -- ditto
531 l_rate number(10,7); -- ditto
532 l_new_surrogate_key number(9);
533
534 CURSOR stu -- Selects all rows from startup entity
535 IS
536 select SURCHARGE_ID
537 , STATE_CODE
538 , ADD_TO_RT
539 , NAME
540 , POSITION
541 , RATE
542 , LAST_UPDATE_DATE
543 , LAST_UPDATED_BY
544 , LAST_UPDATE_LOGIN
545 , CREATED_BY
546 , CREATION_DATE
547 , rowid
548 from hr_s_wc_state_surcharges;
549
550 stu_rec stu%ROWTYPE;
551
552 PROCEDURE remove
553 ----------------
554 IS
555 -- Remove a row from either the startup tables or the installed tables
556
557 BEGIN
558
559
560 delete from hr_s_wc_state_surcharges
561 where rowid = stu_rec.rowid;
562
563 END remove;
564
565 FUNCTION update_uid RETURN boolean
566 ----------------------------------
567 IS
568 -- Subprogram to update surrogate UID and all occurrences in child rows
569
570 BEGIN
571
572
573 BEGIN
574
575 IF p_phase = 2 THEN
576 RETURN true;
577 END IF;
578
579 select surcharge_id
580 , add_to_rt
581 , rate
582 , name
583 into l_new_surrogate_key
584 , l_add_to_rt
585 , l_rate
586 , l_name
587 from pay_wc_state_surcharges
588 where state_code = stu_rec.state_code
589 and position = stu_rec.position;
590
591 IF stu_rec.add_to_rt = l_add_to_rt AND
592 stu_rec.rate = l_rate AND
593 stu_rec.name = l_name THEN
594
595 -- Delete delivered row
596
597 remove;
598
599 -- Indicates this row is not required
600
601 RETURN false;
602
603 END IF;
604
605 EXCEPTION WHEN NO_DATA_FOUND THEN
606
607 -- Must be a new surcharge
608
609 select pay_wc_state_surcharges_s.nextval
610 into l_new_surrogate_key
611 from dual;
612
613 END;
614
615 -- Update all child entities
616
617 update hr_s_wc_state_surcharges
618 set surcharge_id = l_new_surrogate_key
619 where surcharge_id = stu_rec.surcharge_id;
620
621 IF p_phase = 2 THEN
622 return TRUE;
623 ELSE
624 return FALSE;
625 END IF;
626
627 END update_uid;
628
629 PROCEDURE transfer_row
630 ----------------------
631 IS
632 -- Check if a delivered row is needed and insert into the
633 -- live tables if it is
634
635 BEGIN
636
637
638 IF p_phase = 1 THEN
639 return;
640 END IF;
641
642 -- Attempt update first
643
644 update pay_wc_state_surcharges
645 set SURCHARGE_ID = stu_rec.SURCHARGE_ID
646 , STATE_CODE = stu_rec.STATE_CODE
647 , ADD_TO_RT = stu_rec.ADD_TO_RT
648 , NAME = stu_rec.NAME
649 , POSITION = stu_rec.POSITION
650 , RATE = stu_rec.RATE
651 , LAST_UPDATE_DATE = stu_rec.LAST_UPDATE_DATE
652 , LAST_UPDATED_BY = stu_rec.LAST_UPDATED_BY
653 , LAST_UPDATE_LOGIN = stu_rec.LAST_UPDATE_LOGIN
654 , CREATED_BY = stu_rec.CREATED_BY
655 , CREATION_DATE = stu_rec.CREATION_DATE
656 where surcharge_id = stu_rec.surcharge_id;
657
658 IF SQL%NOTFOUND THEN
659
660 -- Row does not exist so insert
661
662
663 insert into pay_wc_state_surcharges
664 (SURCHARGE_ID
665 ,STATE_CODE
666 ,ADD_TO_RT
667 ,NAME
668 ,POSITION
669 ,RATE
670 ,LAST_UPDATE_DATE
671 ,LAST_UPDATED_BY
672 ,LAST_UPDATE_LOGIN
673 ,CREATED_BY
674 ,CREATION_DATE
675 )
676 values
677 (pay_wc_state_surcharges_s.nextval -- changes for nextval
678 ,stu_rec.STATE_CODE
679 ,stu_rec.ADD_TO_RT
680 ,stu_rec.NAME
681 ,stu_rec.POSITION
682 ,stu_rec.RATE
683 ,stu_rec.LAST_UPDATE_DATE
684 ,stu_rec.LAST_UPDATED_BY
685 ,stu_rec.LAST_UPDATE_LOGIN
686 ,stu_rec.CREATED_BY
687 ,stu_rec.CREATION_DATE
688 );
689
690 END IF;
691
692 -- Delete delivered row now it has been installed
693
694 remove;
695
696 END transfer_row;
697
698 BEGIN
699 -- This is the main loop to perform the installation logic. A cursor
700 -- is opened to control the loop, and each row returned is placed
701 -- into a record defined within the main procedure so each sub
702 -- procedure has full access to all returrned columns. For each
703 -- new row returned, a new savepoint is declared. If at any time
704 -- the row is in error a rollback iss performed to the savepoint
705 -- and the next row is returned. If the row differs to that installed
706 -- or if the delivered row is not present, then the row is required and
707 -- the surrogate id is updated and the main transfer logic is called.
708
709 FOR delivered IN stu LOOP
710
711 -- Uses main cursor stu to impilicity define a record
712
713
714 savepoint new_primary_key;
715
716 stu_rec := delivered;
717
718
719 IF update_uid THEN
720 transfer_row;
721 END IF;
722
723 END LOOP;
724
725 END install_surcharges;
726
727 --****************************************************************************
728 -- INSTALLATION PROCEDURE FOR : PAY_LEGISLATION_RULES
729 --****************************************************************************
730
731 PROCEDURE install_leg_rules(p_phase IN number)
732 ----------------------------------------------
733 IS
734 -- Procedure to install legislation rules for a given legislation. This
735 -- routine will compare the value of RULE_MODE for the same RULE_TYPE
736 -- and legislatrion code. If the delivered row is diferent to the one
737 -- installed, or there is not one installed, the delivered row will be
738 -- placed in the live tables.
739
740 l_null_return varchar2(1); -- For 'select null' statements
741 l_rule_mode varchar2(30); -- Holds the value from live
742 l_hrs_rule_mode varchar2(30); -- Holds the possibly translated
743 -- rule mode from HR_S
744 l_new_surrogate_key number(9);
745
746 CURSOR stu -- selects all rows from startup entity
747 IS
748 select legislation_code
749 , rule_type
750 , rule_mode
751 , rowid
752 from hr_s_legislation_rules;
753
754 stu_rec stu%ROWTYPE;
755
756 PROCEDURE remove
757 ----------------
758 IS
759 -- Remove a row from either the startup tables or the installed tables
760
761 BEGIN
762
763 delete from hr_s_legislation_rules
764 where rowid = stu_rec.rowid;
765
766 END remove;
767
768 PROCEDURE transfer_row
769 ----------------------
770 IS
771 -- Check if a delivered row is needed and insert into the
772 -- live tables if it is
773
774 -- The logic performed is simple. If the rule exists, compare the
775 -- value of the rule_mode. Update the rule mode if the the values
776 -- are different.
777
778 -- If the rule does not exist then insert the row into the live tables.
779 -- Updates and inserts only take place in phase 2.
780
781 BEGIN
782
783 BEGIN
784
785 -- Translate the structure_code to a id_flex_num value if the
786 -- rule_mode has been passed in as such
787
788 select distinct id_flex_num
789 into l_hrs_rule_mode
790 from fnd_id_flex_structures fifs
791 where fifs.id_flex_structure_code = stu_rec.rule_mode
792 and stu_rec.rule_type in ('E', 'S', 'CWK_S')
793 and decode(stu_rec.rule_type, 'E', 'BANK',
794 'S', 'SCL',
795 'CWK_S', 'SCL',
796 'X') = fifs.id_flex_code;
797
798 EXCEPTION WHEN OTHERS THEN
799 -- rule_type was passed in as a id_flex_num anyway so leave.
800 l_hrs_rule_mode := stu_rec.rule_mode;
801 END;
802
803 select rule_mode
804 into l_rule_mode
805 from pay_legislation_rules
806 where rule_type = stu_rec.rule_type
807 and legislation_code = stu_rec.legislation_code;
808
809 IF l_rule_mode = l_hrs_rule_mode THEN
810
811 -- The values are the same, this row not needed
812 remove;
813 return;
814
815 END IF;
816
817 -- The row is different and must be updated
818
819 IF p_phase = 1 THEN --only update in phase 2
820 return;
821 END IF;
822
823 update pay_legislation_rules
824 set rule_mode = l_hrs_rule_mode
825 where rule_type = stu_rec.rule_type
826 and legislation_code = stu_rec.legislation_code;
827
828 -- Delete the delivered row from the delivery tables
829 remove;
830
831 EXCEPTION WHEN NO_DATA_FOUND THEN
832
833 IF p_phase = 1 THEN
834 return;
835 END IF;
836
837 insert into pay_legislation_rules
838 (legislation_code
839 ,rule_mode
840 ,rule_type
841 )
842 values
843 (stu_rec.legislation_code
844 ,l_hrs_rule_mode
845 ,stu_rec.rule_type
846 );
847
848 remove;
849
850 END transfer_row;
851
852 BEGIN
853 -- This is the main loop to perform the installation logic. A cursor
854 -- is opened to control the loop, and each row returned is placed
855 -- into a record defined within the main procedure so each sub
856 -- procedure has full access to all returned columns. For each
857 -- new row returned, a new savepoint is declared. If at any time
858 -- the row is in error a rollback is performed to the savepoint
859 -- and the next row is returned. If the row differs to that installed
860 -- or if the delivered row is not present, then the row is required and
861 -- the surrogate id is updated and the main transfer logic is called.
862
863 FOR delivered IN stu LOOP
864
865 -- Uses main cursor stu to impilicity define a record
866
867
868 savepoint new_primary_key;
869
870 stu_rec := delivered;
871
872 transfer_row;
873
874 END LOOP;
875
876 END install_leg_rules;
877
878
879 PROCEDURE install_us_new(p_phase IN number)
880 ----------------
881 IS
882 hr_s_tab character_data_table;
883 pay_tab character_data_table;
884 cid integer;
885 deltabid integer;
886 lc_cnt number;
887 gfr_exist varchar2(1) := 'N';
888 insert_jit_data varchar2(1) := 'N';
889 l_patch_count number(5);
890 l_max_val number(15) := 0;
891 l_next_val number(15) := 0;
892 l_prev_seq number(15) := 0;
893
894 cursor cti is select * from HR_S_US_CITY_TAX_INFO_F;
895 cursor coti is select * from HR_S_US_COUNTY_TAX_INFO_F;
896 cursor sti is select * from HR_S_US_STATE_TAX_INFO_F;
897 cursor fti is select * from HR_S_US_FEDERAL_TAX_INFO_F;
898 cursor ger is select * from HR_S_US_GARN_EXEMPTION_RULES_F;
899 cursor gfr is select * from HR_S_US_GARN_FEE_RULES_F
900 order by fee_rule_id;
901 cursor pgfr is select creator_type, garn_category, state_code
902 FROM PAY_US_GARN_FEE_RULES_F
903 WHERE sysdate between effective_start_date
904 and effective_end_date;
905
906 cursor glr is select * from HR_S_US_GARN_LIMIT_RULES_F;
907 cursor hrl is select * from HR_S_REPORT_LOOKUPS;
908 l_magnetic_block_id number;
909 l_next_block_id number;
910 l_formula_id number;
911 sqlstr varchar2(2000);
912 BEGIN
913 if p_phase = 2 then
914
915 begin
916 hr_s_tab(1) := 'HR_S_US_CITY_TAX_INFO_F';
917 hr_s_tab(2) := 'HR_S_US_COUNTY_TAX_INFO_F';
918 hr_s_tab(3) := 'HR_S_US_STATE_TAX_INFO_F';
919 hr_s_tab(4) := 'HR_S_US_FEDERAL_TAX_INFO_F';
920 hr_s_tab(5) := 'HR_S_US_GARN_EXEMPTION_RULES_F';
921 hr_s_tab(6) := 'HR_S_US_GARN_FEE_RULES_F';
922 hr_s_tab(7) := 'HR_S_US_GARN_LIMIT_RULES_F';
923 hr_s_tab(8) := 'HR_S_REPORT_LOOKUPS';
924
925 pay_tab(1) := 'PAY_US_CITY_TAX_INFO_F';
926 pay_tab(2) := 'PAY_US_COUNTY_TAX_INFO_F';
927 pay_tab(3) := 'PAY_US_STATE_TAX_INFO_F';
928 pay_tab(4) := 'PAY_US_FEDERAL_TAX_INFO_F';
929 pay_tab(5) := 'PAY_US_GARN_EXEMPTION_RULES_F';
930 pay_tab(6) := 'PAY_US_GARN_FEE_RULES_F';
931 pay_tab(7) := 'PAY_US_GARN_LIMIT_RULES_F';
932 pay_tab(8) := 'HR_REPORT_LOOKUPS';
933
934 select count(*)
935 into l_patch_count
936 from pay_patch_status
937 where patch_name like 'JIT%';
938
939 for lc_cnt in 5..8 loop
940 if pay_tab(lc_cnt) = 'PAY_US_GARN_FEE_RULES_F' then
941 DELETE FROM PAY_US_GARN_FEE_RULES_F
942 WHERE creator_type = 'SYSTEM'
943 AND 0 <> (SELECT COUNT(*) FROM
944 HR_S_US_GARN_FEE_RULES_F);
945 else
946 begin
947 if ((l_patch_count > 0 ) and (pay_tab(lc_cnt) like 'PAY_US%INFO_F' )) then
948 null; /* do not transfer JIT data if not fresh install */
949 else
950
951 sqlstr := 'delete from '|| pay_tab(lc_cnt) || ' where ';
952 sqlstr := sqlstr || ' 0 <> ( ';
953 sqlstr := sqlstr || 'select count(*) from '|| hr_s_tab(lc_cnt)||' )';
954
955 cid := dbms_sql.open_cursor;
956 dbms_sql.parse(cid, sqlstr, dbms_sql.v7);
957 deltabid := dbms_sql.execute(cid);
958 dbms_sql.close_cursor(cid);
959 end if;
960 exception
961 when others then
962 dbms_sql.close_cursor(cid);
963 end;
964
965 end if;
966
967
968 end loop;
969
970 for ger_rec in ger loop
971 savepoint new_primary_key;
972 insert into PAY_US_GARN_EXEMPTION_RULES_F
973 (
974 MIN_WAGE_FACTOR,
975 PRORATION_RULE,
976 LAST_UPDATE_DATE,
977 LAST_UPDATED_BY,
978 LAST_UPDATE_LOGIN,
979 CREATED_BY,
980 CREATION_DATE,
981 EXEMPTION_RULE_ID,
982 EFFECTIVE_START_DATE,
983 EFFECTIVE_END_DATE,
984 GARN_CATEGORY,
985 STATE_CODE,
986 ADDL_DEP_AMOUNT_VALUE,
987 AMOUNT_VALUE,
988 CALC_RULE,
989 CREATOR_TYPE,
990 DEPENDENTS_CALC_RULE,
991 DEPENDENT_AMOUNT_VALUE,
992 DI_PCT,
993 DI_PCT_DEPENDENTS,
994 DI_PCT_DEPENDENTS_IN_ARREARS,
995 DI_PCT_IN_ARREARS,
996 EXEMPTION_BALANCE,
997 EXEMPTION_BALANCE_MAX_PCT,
998 EXEMPTION_BALANCE_MIN_PCT,
999 MARITAL_STATUS)values
1000 (
1001 ger_rec.MIN_WAGE_FACTOR,
1002 ger_rec.PRORATION_RULE,
1003 ger_rec.LAST_UPDATE_DATE,
1004 ger_rec.LAST_UPDATED_BY,
1005 ger_rec.LAST_UPDATE_LOGIN,
1006 ger_rec.CREATED_BY,
1007 ger_rec.CREATION_DATE,
1008 ger_rec.EXEMPTION_RULE_ID,
1009 /* Since we have already deleted all rows from
1010 pay_us_garn_exemption_rules_f, there cannot be any conflict of ids. It is
1011 therefore safe to use the ids from the hr_s table. This will help in
1012 maintainig the correct ids for date-tracked rows. After inserting
1013 all the rows we will set the sequence to start at a value higher than the
1014 max value of the id in pay_us_garn_exemption_rules_f.
1015 PAY_US_GARN_EXEMPTION_RULES_S.nextval, -- changes for nextval */
1016 ger_rec.EFFECTIVE_START_DATE,
1017 ger_rec.EFFECTIVE_END_DATE,
1018 ger_rec.GARN_CATEGORY,
1019 ger_rec.STATE_CODE,
1020 ger_rec.ADDL_DEP_AMOUNT_VALUE,
1021 ger_rec.AMOUNT_VALUE,
1022 ger_rec.CALC_RULE,
1023 ger_rec.CREATOR_TYPE,
1024 ger_rec.DEPENDENTS_CALC_RULE,
1025 ger_rec.DEPENDENT_AMOUNT_VALUE,
1026 ger_rec.DI_PCT,
1027 ger_rec.DI_PCT_DEPENDENTS,
1028 ger_rec.DI_PCT_DEPENDENTS_IN_ARREARS,
1029 ger_rec.DI_PCT_IN_ARREARS,
1030 ger_rec.EXEMPTION_BALANCE,
1031 ger_rec.EXEMPTION_BALANCE_MAX_PCT,
1032 ger_rec.EXEMPTION_BALANCE_MIN_PCT,
1033 ger_rec.MARITAL_STATUS);
1034
1035 end loop;
1036
1037 select max(exemption_rule_id)
1038 into l_max_val
1039 from pay_us_garn_exemption_rules_f;
1040
1041 l_next_val := 0;
1042
1043 /* consume sequence till there is no conflict */
1044 while l_next_val < l_max_val
1045 loop
1046 select PAY_US_GARN_EXEMPTION_RULES_S.nextval
1047 into l_next_val
1048 from dual;
1049 end loop;
1050
1051 /* reset l_max_val, l_next_val for the next table */
1052 l_max_val := 0;
1053 l_next_val := 0;
1054
1055
1056 for gfr_rec in gfr loop
1057 gfr_exist := 'N';
1058 for pgfr_rec in pgfr loop
1059 if (pgfr_rec.state_code = gfr_rec.state_code
1060 and pgfr_rec.garn_category = gfr_rec.garn_category
1061 and pgfr_rec.creator_type <> 'SYSTEM') then
1062 gfr_exist := 'Y';
1063 exit;
1064 end if;
1065 end loop;
1066 /* In order to maintain correct history of date tracked changes
1067 and to also avoid any conflict of ids with user update rows
1068 ( creator_type <> 'SYSTEM'), we need to compare the current
1069 fee_rule_id with the row processed in the previous iteration.
1070 If the ids are the same we will use currval from the sequence as
1071 the new fee rule id else we will use nextval. */
1072 if l_prev_seq = gfr_rec.fee_rule_id
1073 then
1074 select PAY_US_GARN_FEE_RULES_S.currval
1075 into l_next_val
1076 from dual;
1077 else
1078 select PAY_US_GARN_FEE_RULES_S.nextval
1079 into l_next_val
1080 from dual;
1081 end if;
1082
1083 l_prev_seq := gfr_rec.fee_rule_id;
1084
1085 if gfr_exist = 'N' then
1086 savepoint new_primary_key;
1087 insert into PAY_US_GARN_FEE_RULES_F
1088 (
1089 FEE_RULE_ID,
1090 EFFECTIVE_START_DATE,
1091 EFFECTIVE_END_DATE,
1092 GARN_CATEGORY,
1093 STATE_CODE,
1094 ADDL_GARN_FEE_AMOUNT,
1095 CORRESPONDENCE_FEE,
1096 CREATOR_TYPE,
1097 FEE_AMOUNT,
1098 FEE_RULE,
1099 MAX_FEE_AMOUNT,
1100 PCT_CURRENT,
1101 LAST_UPDATE_DATE,
1102 LAST_UPDATED_BY,
1103 LAST_UPDATE_LOGIN,
1104 CREATED_BY,
1105 CREATION_DATE) values
1106 (
1107 /* use the l_next_val generated
1108 in the previous step to populte the
1109 fee_rule_id
1110 pay_us_garn_fee_rules_s.nextval,*/
1111 l_next_val,
1112 to_date('01/01/0001', 'DD/MM/YYYY'),
1113 to_date('31/12/4712', 'DD/MM/YYYY'),
1114 gfr_rec.GARN_CATEGORY,
1115 gfr_rec.STATE_CODE,
1116 gfr_rec.ADDL_GARN_FEE_AMOUNT,
1117 gfr_rec.CORRESPONDENCE_FEE,
1118 'SYSTEM',
1119 gfr_rec.FEE_AMOUNT,
1120 gfr_rec.FEE_RULE,
1121 gfr_rec.MAX_FEE_AMOUNT,
1122 gfr_rec.PCT_CURRENT,
1123 gfr_rec.LAST_UPDATE_DATE,
1124 gfr_rec.LAST_UPDATED_BY,
1125 gfr_rec.LAST_UPDATE_LOGIN,
1126 gfr_rec.CREATED_BY,
1127 gfr_rec.CREATION_DATE);
1128
1129 gfr_exist := 'N';
1130 end if;
1131 end loop;
1132
1133 select max(fee_rule_id)
1134 into l_max_val
1135 from pay_us_garn_fee_rules_f;
1136
1137 l_next_val := 0;
1138
1139 /* consume sequence till there is no conflict */
1140 while l_next_val < l_max_val
1141 loop
1142 select PAY_US_GARN_FEE_RULES_S.nextval
1143 into l_next_val
1144 from dual;
1145 end loop;
1146
1147 /* reset l_max_val, l_next_val for the next table */
1148 l_max_val := 0;
1149 l_next_val := 0;
1150
1151
1152
1153
1154
1155 for glr_rec in glr loop
1156 savepoint new_primary_key;
1157 insert into PAY_US_GARN_LIMIT_RULES_F
1158 (
1159 LIMIT_RULE_ID,
1160 EFFECTIVE_START_DATE,
1161 EFFECTIVE_END_DATE,
1162 GARN_CATEGORY,
1163 STATE_CODE,
1164 MAX_WITHHOLDING_AMOUNT,
1165 MAX_WITHHOLDING_DURATION_DAYS,
1166 MIN_WITHHOLDING_AMOUNT,
1167 LAST_UPDATE_DATE,
1168 LAST_UPDATED_BY,
1169 LAST_UPDATE_LOGIN,
1170 CREATED_BY,
1171 CREATION_DATE) values
1172 (
1173 glr_rec.LIMIT_RULE_ID,
1174 /* Since we have already deleted all rows from
1175 pay_us_garn_limit_rules_f, there cannot be any conflict of ids. It is
1176 therefore safe to use the ids from the hr_s table. This will help in
1177 maintainig the correct ids for date-tracked rows. After inserting
1178 all the rows we will set the sequence to start at a value higher than the
1179 max value of the id in pay_us_garn_limit_rules_f.
1180 PAY_US_GARN_LIMIT_RULES_S.nextval, -- cahnges for nextval */
1181 glr_rec.EFFECTIVE_START_DATE,
1182 glr_rec.EFFECTIVE_END_DATE,
1183 glr_rec.GARN_CATEGORY,
1184 glr_rec.STATE_CODE,
1185 glr_rec.MAX_WITHHOLDING_AMOUNT,
1186 glr_rec.MAX_WITHHOLDING_DURATION_DAYS,
1187 glr_rec.MIN_WITHHOLDING_AMOUNT,
1188 glr_rec.LAST_UPDATE_DATE,
1189 glr_rec.LAST_UPDATED_BY,
1190
1191 glr_rec.LAST_UPDATE_LOGIN,
1192 glr_rec.CREATED_BY,
1193 glr_rec.CREATION_DATE);
1194
1195 end loop;
1196
1197 select max(limit_rule_id)
1198 into l_max_val
1199 from pay_us_garn_limit_rules_f;
1200
1201 l_next_val := 0;
1202
1203 /* consume sequence till there is no conflict */
1204 while l_next_val < l_max_val
1205 loop
1206 select PAY_US_GARN_LIMIT_RULES_S.nextval
1207 into l_next_val
1208 from dual;
1209 end loop;
1210
1211 /* reset l_max_val, l_next_val for the next table */
1212 l_max_val := 0;
1213 l_next_val := 0;
1214
1215
1216 for hrl_rec in hrl loop
1217 savepoint new_primary_key;
1218 insert into hr_report_lookups (
1219 REPORT_NAME,
1220 REPORT_LOOKUP_TYPE,
1221 LOOKUP_CODE,
1222 ENABLED_FLAG,
1223 CREATED_BY,
1224 CREATION_DATE,
1225 LAST_UPDATED_BY,
1226 LAST_UPDATE_DATE,
1227 LAST_UPDATE_LOGIN )
1228 values (
1229 hrl_rec.REPORT_NAME,
1230 hrl_rec.REPORT_LOOKUP_TYPE,
1231 hrl_rec.LOOKUP_CODE,
1232 hrl_rec.ENABLED_FLAG,
1233 hrl_rec.CREATED_BY,
1234 hrl_rec.CREATION_DATE,
1235 hrl_rec.LAST_UPDATED_BY,
1236 hrl_rec.LAST_UPDATE_DATE,
1237 hrl_rec.LAST_UPDATE_LOGIN );
1238 end loop;
1239
1240 EXCEPTION
1241 when others then
1242 rollback to new_primary_key;
1243
1244 /* temp. workaround for unique key violation */
1245 delete HR_STU_EXCEPTIONS
1246 where table_name = 'HR_S_NEW';
1247
1248 hr_legislation.insert_hr_stu_exceptions('HR_S_NEW',
1249 1000,
1250 'Error in new US tables',
1251 null);
1252
1253
1254
1255 end;
1256 end if;
1257
1258 END install_us_new;
1259
1260 FUNCTION decode_elmnt_bal_information (p_legislation_code VARCHAR2,
1261 p_information_type VARCHAR2,
1262 p_code NUMBER DEFAULT NULL,
1263 p_meaning VARCHAR2 DEFAULT NULL)
1264 RETURN VARCHAR2 IS
1265
1266 CURSOR csr_element_name IS
1267 SELECT element_name
1268 FROM hr_s_element_types_f
1269 WHERE element_type_id = p_code;
1270
1271 CURSOR csr_element_id IS
1272 SELECT element_type_id
1273 FROM pay_element_types_f
1274 WHERE element_name = p_meaning
1275 AND legislation_code = p_legislation_code;
1276
1277 CURSOR csr_balance_name IS
1278 SELECT balance_name
1279 FROM hr_s_balance_types
1280 WHERE balance_type_id = p_code;
1281
1282 CURSOR csr_balance_id IS
1283 SELECT balance_type_id
1284 FROM pay_balance_types
1285 WHERE balance_name = p_meaning
1286 AND legislation_code = p_legislation_code;
1287
1288 l_return VARCHAR2(80);
1289
1290 BEGIN
1291 IF p_information_type = 'BALANCE'
1292 THEN
1293 IF p_code IS NULL THEN
1294 OPEN csr_balance_id;
1295
1296 FETCH csr_balance_id
1297 INTO l_return;
1298
1299 IF csr_balance_id%NOTFOUND THEN
1300 l_return := NULL;
1301 END IF;
1302 ELSE
1303 OPEN csr_balance_name;
1304
1305 FETCH csr_balance_name
1306 INTO l_return;
1307
1308 IF csr_balance_name%NOTFOUND THEN
1309 l_return := NULL;
1310 END IF;
1311 END IF;
1312 ELSE
1313 IF p_code IS NULL THEN
1314 OPEN csr_element_id;
1315
1316 FETCH csr_element_id
1317 INTO l_return;
1318
1319 IF csr_element_id%NOTFOUND THEN
1320 l_return := NULL;
1321 END IF;
1322 ELSE
1323 OPEN csr_element_name;
1324
1325 FETCH csr_element_name
1326 INTO l_return;
1327
1328 IF csr_balance_name%NOTFOUND THEN
1329 l_return := NULL;
1330 END IF;
1331 END IF;
1332 END IF;
1333 return l_return;
1334 END decode_elmnt_bal_information;
1335
1336 PROCEDURE translate_us_ele_dev_df(p_mode VARCHAR2)
1337 IS
1338 CURSOR csr_hr_s_element_details IS
1339 SELECT element_type_id,
1340 element_information_category,
1341 element_information10
1342 FROM hr_s_element_types_f
1343 WHERE legislation_code = 'US'
1344 AND element_information_category IS NOT NULL
1345 FOR UPDATE OF element_information10 NOWAIT;
1346
1347 CURSOR csr_pay_element_details IS
1348 SELECT element_type_id,
1349 element_information_category,
1350 element_information10
1351 FROM pay_element_types_f
1352 WHERE legislation_code = 'US'
1353 AND element_information_category IS NOT NULL
1354 FOR UPDATE OF element_information10 NOWAIT;
1355
1356 /*
1357 ** VMehta - Commented out the update for element_tyupe ids for the time being
1358 ** as this runs into mutating table condition in the 'NAME_TO_ID' condition
1359 ** We can get away with this for 11i as we are not delivering any element ids.
1360 ** in the Element Developer DF fort the time being.
1361 **
1362 */
1363
1364 BEGIN
1365 IF p_mode = 'ID_TO_NAME' THEN
1366 FOR hr_s_rec IN csr_hr_s_element_details
1367 LOOP
1368 IF hr_s_rec.element_information_category = 'US_EARNINGS'
1369 THEN
1370
1371 UPDATE hr_s_element_types_f
1372 SET element_information10 = NVL(decode_elmnt_bal_information('US',
1373 'BALANCE'
1374 , element_information10, NULL),
1375 element_information10),
1376 element_information12 = NVL(decode_elmnt_bal_information('US',
1377 'BALANCE'
1378 , element_information12, NULL),
1379 element_information12),
1380 element_information16 = NVL(decode_elmnt_bal_information('US',
1381 'BALANCE'
1382 , element_information16, NULL),
1383 element_information16),
1384 element_information17 = NVL(decode_elmnt_bal_information('US',
1385 'BALANCE'
1386 , element_information17, NULL),
1387 element_information17)--,
1388 --element_information18 = NVL(decode_elmnt_bal_information('US',
1389 -- 'ELEMENT'
1390 -- , element_information18, NULL),
1391 -- element_information18),
1392 --element_information19 = NVL(decode_elmnt_bal_information('US',
1393 -- 'ELEMENT'
1394 -- , element_information19, NULL),
1395 -- element_information19),
1396 --element_information20 = NVL(decode_elmnt_bal_information('US',
1397 -- 'ELEMENT'
1398 -- , element_information20, NULL),
1399 -- element_information20)
1400 WHERE element_type_id = hr_s_rec.element_type_id;
1401
1402 ELSIF hr_s_rec.element_information_category = 'US_IMPUTED EARNINGS'
1403 THEN
1404
1405 UPDATE hr_s_element_types_f
1406 SET element_information10 = NVL(decode_elmnt_bal_information('US',
1407 'BALANCE'
1408 , element_information10, NULL),
1409 element_information10),
1410 element_information12 = NVL(decode_elmnt_bal_information('US',
1411 'BALANCE'
1412 , element_information12, NULL),
1413 element_information12),
1414 element_information16 = NVL(decode_elmnt_bal_information('US',
1415 'BALANCE'
1416 , element_information16, NULL),
1417 element_information16),
1418 element_information17 = NVL(decode_elmnt_bal_information('US',
1419 'BALANCE'
1420 , element_information17, NULL),
1421 element_information17)--,
1422 --element_information18 = NVL(decode_elmnt_bal_information('US',
1423 -- 'ELEMENT'
1424 -- , element_information18, NULL),
1425 -- element_information18),
1426 --element_information19 = NVL(decode_elmnt_bal_information('US',
1427 -- 'ELEMENT'
1428 -- , element_information19, NULL),
1429 -- element_information19),
1430 --element_information20 = NVL(decode_elmnt_bal_information('US',
1431 -- 'ELEMENT'
1432 -- , element_information20, NULL),
1433 -- element_information20)
1434 WHERE element_type_id = hr_s_rec.element_type_id;
1435
1436 ELSIF hr_s_rec.element_information_category = 'US_INVOLUNTARY DEDUCTIONS' THEN
1437 UPDATE hr_s_element_types_f
1438 SET
1439 -- element_information5 = NVL(decode_elmnt_bal_information('US',
1440 -- 'ELEMENT'
1441 -- , element_information5, NULL),
1442 -- element_information5),
1443 element_information8 = NVL(decode_elmnt_bal_information('US',
1444 'BALANCE'
1445 , element_information8, NULL),
1446 element_information8),
1447 element_information10 = NVL(decode_elmnt_bal_information('US',
1448 'BALANCE'
1449 , element_information10, NULL),
1450 element_information10),
1451 element_information11 = NVL(decode_elmnt_bal_information('US',
1452 'BALANCE'
1453 , element_information11, NULL),
1454 element_information11),
1455 element_information12 = NVL(decode_elmnt_bal_information('US',
1456 'BALANCE'
1457 , element_information12, NULL),
1458 element_information12),
1459 element_information13 = NVL(decode_elmnt_bal_information('US',
1460 'BALANCE'
1461 , element_information13, NULL),
1462 element_information13),
1463 element_information14 = NVL(decode_elmnt_bal_information('US',
1464 'BALANCE'
1465 , element_information14, NULL),
1466 element_information14),
1467 element_information15 = NVL(decode_elmnt_bal_information('US',
1468 'BALANCE'
1469 , element_information15, NULL),
1470 element_information15),
1471 element_information16 = NVL(decode_elmnt_bal_information('US',
1472 'BALANCE'
1473 , element_information16, NULL),
1474 element_information16),
1475 element_information17 = NVL(decode_elmnt_bal_information('US',
1476 'BALANCE'
1477 , element_information17, NULL),
1478 element_information17)--,
1479 --element_information18 = NVL(decode_elmnt_bal_information('US',
1480 -- 'ELEMENT'
1481 -- , element_information18, NULL),
1482 -- element_information18),
1483 --element_information19 = NVL(decode_elmnt_bal_information('US',
1484 -- 'ELEMENT'
1485 -- , element_information19, NULL),
1486 -- element_information19),
1487 --element_information20 = NVL(decode_elmnt_bal_information('US',
1488 -- 'ELEMENT'
1489 -- , element_information20, NULL),
1490 -- element_information20)
1491 WHERE element_type_id = hr_s_rec.element_type_id;
1492
1493 ELSIF hr_s_rec.element_information_category = 'US_NON-PAYROLL PAYMENTS'
1494 THEN
1495 UPDATE hr_s_element_types_f
1496 SET element_information10 = NVL(decode_elmnt_bal_information('US',
1497 'BALANCE'
1498 , element_information10, NULL),
1499 element_information10),
1500 element_information16 = NVL(decode_elmnt_bal_information('US',
1501 'BALANCE'
1502 , element_information16, NULL),
1503 element_information16),
1504 element_information17 = NVL(decode_elmnt_bal_information('US',
1505 'BALANCE'
1506 , element_information17, NULL),
1507 element_information17)--,
1508 --element_information18 = NVL(decode_elmnt_bal_information('US',
1509 -- 'ELEMENT'
1510 -- , element_information18, NULL),
1511 -- element_information18),
1512 --element_information19 = NVL(decode_elmnt_bal_information('US',
1513 -- 'ELEMENT'
1514 -- , element_information19, NULL),
1515 -- element_information19),
1516 --element_information20 = NVL(decode_elmnt_bal_information('US',
1517 -- 'ELEMENT'
1518 -- , element_information20, NULL),
1519 -- element_information20)
1520 WHERE element_type_id = hr_s_rec.element_type_id;
1521
1522 ELSIF hr_s_rec.element_information_category = 'US_PRE-TAX DEDUCTIONS'
1523 THEN
1524 UPDATE hr_s_element_types_f
1525 SET element_information10 = NVL(decode_elmnt_bal_information('US',
1526 'BALANCE'
1527 , element_information10, NULL),
1528 element_information10),
1529 element_information11 = NVL(decode_elmnt_bal_information('US',
1530 'BALANCE'
1531 , element_information11, NULL),
1532 element_information11),
1533 element_information12 = NVL(decode_elmnt_bal_information('US',
1534 'BALANCE'
1535 , element_information12, NULL),
1536 element_information12),
1537 element_information13 = NVL(decode_elmnt_bal_information('US',
1538 'BALANCE'
1539 , element_information13, NULL),
1540 element_information13),
1541 element_information14 = NVL(decode_elmnt_bal_information('US',
1542 'BALANCE'
1543 , element_information14, NULL),
1544 element_information14),
1545 element_information15 = NVL(decode_elmnt_bal_information('US',
1546 'BALANCE'
1547 , element_information15, NULL),
1548 element_information15),
1549 element_information16 = NVL(decode_elmnt_bal_information('US',
1550 'BALANCE'
1551 , element_information16, NULL),
1552 element_information16),
1553 element_information17 = NVL(decode_elmnt_bal_information('US',
1554 'BALANCE'
1555 , element_information17, NULL),
1556 element_information17)--,
1557 --element_information18 = NVL(decode_elmnt_bal_information('US',
1558 -- 'ELEMENT'
1559 -- , element_information18, NULL),
1560 -- element_information18),
1561 --element_information19 = NVL(decode_elmnt_bal_information('US',
1562 -- 'ELEMENT'
1563 -- , element_information19, NULL),
1564 -- element_information19),
1565 --element_information20 = NVL(decode_elmnt_bal_information('US',
1566 -- 'ELEMENT'
1567 -- , element_information20, NULL),
1568 -- element_information20)
1569 WHERE element_type_id = hr_s_rec.element_type_id;
1570
1571 ELSIF hr_s_rec.element_information_category = 'US_PTO ACCRUALS'
1572 THEN
1573 UPDATE hr_s_element_types_f
1574 SET element_information10 = NVL(decode_elmnt_bal_information('US',
1575 'BALANCE'
1576 , element_information10, NULL),
1577 element_information10)
1578 WHERE element_type_id = hr_s_rec.element_type_id;
1579
1580 ELSIF hr_s_rec.element_information_category = 'US_SUPPLEMENTAL EARNINGS'
1581 THEN
1582 UPDATE hr_s_element_types_f
1583 SET element_information10 = NVL(decode_elmnt_bal_information('US',
1584 'BALANCE'
1585 , element_information10, NULL),
1586 element_information10),
1587 element_information12 = NVL(decode_elmnt_bal_information('US',
1588 'BALANCE'
1589 , element_information12, NULL),
1590 element_information12),
1591 element_information16 = NVL(decode_elmnt_bal_information('US',
1592 'BALANCE'
1593 , element_information16, NULL),
1594 element_information16),
1595 element_information17 = NVL(decode_elmnt_bal_information('US',
1596 'BALANCE'
1597 , element_information17, NULL),
1598 element_information17)--,
1599 --element_information18 = NVL(decode_elmnt_bal_information('US',
1600 -- 'ELEMENT'
1601 -- , element_information18, NULL),
1602 -- element_information18),
1603 --element_information19 = NVL(decode_elmnt_bal_information('US',
1604 -- 'ELEMENT'
1605 -- , element_information19, NULL),
1606 -- element_information19),
1607 --element_information20 = NVL(decode_elmnt_bal_information('US',
1608 -- 'ELEMENT'
1609 -- , element_information20, NULL),
1610 -- element_information20)
1611 WHERE element_type_id = hr_s_rec.element_type_id;
1612
1613 ELSIF hr_s_rec.element_information_category = 'US_TAX DEDUCTIONS'
1614 THEN
1615 UPDATE hr_s_element_types_f
1616 SET element_information10 = NVL(decode_elmnt_bal_information('US',
1617 'BALANCE'
1618 , element_information10, NULL),
1619 element_information10),
1620 element_information11 = NVL(decode_elmnt_bal_information('US',
1621 'BALANCE'
1622 , element_information11, NULL),
1623 element_information11),
1624 element_information12 = NVL(decode_elmnt_bal_information('US',
1625 'BALANCE'
1626 , element_information12, NULL),
1627 element_information12),
1628 element_information13 = NVL(decode_elmnt_bal_information('US',
1629 'BALANCE'
1630 , element_information13, NULL),
1631 element_information13),
1632 element_information14 = NVL(decode_elmnt_bal_information('US',
1633 'BALANCE'
1634 , element_information14, NULL),
1635 element_information14),
1636 element_information15 = NVL(decode_elmnt_bal_information('US',
1637 'BALANCE'
1638 , element_information15, NULL),
1639 element_information15),
1640 element_information16 = NVL(decode_elmnt_bal_information('US',
1641 'BALANCE'
1642 , element_information16, NULL),
1643 element_information16),
1644 element_information17 = NVL(decode_elmnt_bal_information('US',
1645 'BALANCE'
1646 , element_information17, NULL),
1647 element_information17),
1648 element_information18 = NVL(decode_elmnt_bal_information('US',
1649 'BALANCE'
1650 , element_information18, NULL),
1651 element_information18),
1652 element_information19 = NVL(decode_elmnt_bal_information('US',
1653 'BALANCE'
1654 , element_information19, NULL),
1655 element_information19)
1656 WHERE element_type_id = hr_s_rec.element_type_id;
1657
1658 ELSIF hr_s_rec.element_information_category = 'US_VOLUNTARY DEDUCTIONS'
1659 THEN
1660 UPDATE hr_s_element_types_f
1661 SET element_information10 = NVL(decode_elmnt_bal_information('US',
1662 'BALANCE'
1663 , element_information10, NULL),
1664 element_information10),
1665 element_information11 = NVL(decode_elmnt_bal_information('US',
1666 'BALANCE'
1667 , element_information11, NULL),
1668 element_information11),
1669 element_information12 = NVL(decode_elmnt_bal_information('US',
1670 'BALANCE'
1671 , element_information12, NULL),
1672 element_information12),
1673 element_information13 = NVL(decode_elmnt_bal_information('US',
1674 'BALANCE'
1675 , element_information13, NULL),
1676 element_information13),
1677 element_information14 = NVL(decode_elmnt_bal_information('US',
1678 'BALANCE'
1679 , element_information14, NULL),
1680 element_information14),
1681 element_information16 = NVL(decode_elmnt_bal_information('US',
1682 'BALANCE'
1683 , element_information16, NULL),
1684 element_information16),
1685 element_information17 = NVL(decode_elmnt_bal_information('US',
1686 'BALANCE'
1687 , element_information17, NULL),
1688 element_information17)--,
1689 --element_information18 = NVL(decode_elmnt_bal_information('US',
1690 -- 'ELEMENT'
1691 -- , element_information18, NULL),
1692 -- element_information18),
1693 --element_information19 = NVL(decode_elmnt_bal_information('US',
1694 -- 'ELEMENT'
1695 -- , element_information19, NULL),
1696 -- element_information19),
1697 --element_information20 = NVL(decode_elmnt_bal_information('US',
1698 -- 'ELEMENT'
1699 -- , element_information20, NULL),
1700 -- element_information20)
1701 WHERE element_type_id = hr_s_rec.element_type_id;
1702
1703 END IF;
1704 END LOOP;
1705 ELSE /* NAME_TO_ID */
1706 FOR pay_rec IN csr_pay_element_details
1707 LOOP
1708 IF pay_rec.element_information_category = 'US_EARNINGS'
1709 THEN
1710
1711 UPDATE pay_element_types_f
1712 SET element_information10 = NVL(decode_elmnt_bal_information('US',
1713
1714 'BALANCE', NULL, element_information10),
1715 element_information10),
1716 element_information12 = NVL(decode_elmnt_bal_information('US',
1717
1718 'BALANCE', NULL, element_information12),
1719 element_information12),
1720 element_information16 = NVL(decode_elmnt_bal_information('US',
1721 'BALANCE'
1722 , NULL, element_information16),
1723 element_information16),
1724 element_information17 = NVL(decode_elmnt_bal_information('US',
1725 'BALANCE'
1726 , NULL, element_information17),
1727 element_information17)--,
1728 --element_information18 = NVL(decode_elmnt_bal_information('US',
1729 -- 'ELEMENT'
1730 -- , NULL, element_information18),
1731 -- element_information18),
1732 --element_information19 = NVL(decode_elmnt_bal_information('US',
1733 -- 'ELEMENT'
1734 -- , NULL, element_information19),
1735 -- element_information19),
1736 --element_information20 = NVL(decode_elmnt_bal_information('US',
1737 -- 'ELEMENT'
1738 -- , NULL, element_information20),
1739 -- element_information20)
1740 WHERE element_type_id = pay_rec.element_type_id;
1741
1742 ELSIF pay_rec.element_information_category = 'US_IMPUTED EARNINGS'
1743 THEN
1744
1745 UPDATE pay_element_types_f
1746 SET element_information10 = NVL(decode_elmnt_bal_information('US',
1747 'BALANCE'
1748 , NULL, element_information10),
1749 element_information10),
1750 element_information12 = NVL(decode_elmnt_bal_information('US',
1751 'BALANCE'
1752 , NULL, element_information12),
1753 element_information12),
1754 element_information16 = NVL(decode_elmnt_bal_information('US',
1755 'BALANCE'
1756 , NULL, element_information16),
1757 element_information16),
1758 element_information17 = NVL(decode_elmnt_bal_information('US',
1759 'BALANCE'
1760 , NULL, element_information17),
1761 element_information17)--,
1762 --element_information18 = NVL(decode_elmnt_bal_information('US',
1763 -- 'ELEMENT'
1764 -- , NULL, element_information18),
1765 -- element_information18),
1766 --element_information19 = NVL(decode_elmnt_bal_information('US',
1767 -- 'ELEMENT'
1768 -- , NULL, element_information19),
1769 -- element_information19),
1770 --element_information20 = NVL(decode_elmnt_bal_information('US',
1771 -- 'ELEMENT'
1772 -- , NULL, element_information20),
1773 -- element_information20)
1774 WHERE element_type_id = pay_rec.element_type_id;
1775
1776 ELSIF pay_rec.element_information_category = 'US_INVOLUNTARY DEDUCTIONS' THEN
1777 UPDATE pay_element_types_f
1778 SET
1779 --element_information5 = NVL(decode_elmnt_bal_information('US',
1780 -- 'ELEMENT'
1781 -- , NULL, element_information5),
1782 -- element_information5),
1783 element_information8 = NVL(decode_elmnt_bal_information('US',
1784 'BALANCE'
1785 , NULL, element_information8),
1786 element_information8),
1787 element_information10 = NVL(decode_elmnt_bal_information('US',
1788 'BALANCE'
1789 , NULL, element_information10),
1790 element_information10),
1791 element_information11 = NVL(decode_elmnt_bal_information('US',
1792 'BALANCE'
1793 , NULL, element_information11),
1794 element_information11),
1795 element_information12 = NVL(decode_elmnt_bal_information('US',
1796 'BALANCE'
1797 , NULL, element_information12),
1798 element_information12),
1799 element_information13 = NVL(decode_elmnt_bal_information('US',
1800 'BALANCE'
1801 , NULL, element_information13),
1802 element_information13),
1803 element_information14 = NVL(decode_elmnt_bal_information('US',
1804 'BALANCE'
1805 , NULL, element_information14),
1806 element_information14),
1807 element_information15 = NVL(decode_elmnt_bal_information('US',
1808 'BALANCE'
1809 , NULL, element_information15),
1810 element_information15),
1811 element_information16 = NVL(decode_elmnt_bal_information('US',
1812 'BALANCE'
1813 , NULL, element_information16),
1814 element_information16),
1815 element_information17 = NVL(decode_elmnt_bal_information('US',
1816 'BALANCE'
1817 , NULL, element_information17),
1818 element_information17)--,
1819 --element_information18 = NVL(decode_elmnt_bal_information('US',
1820 -- 'ELEMENT'
1821 -- , NULL, element_information18),
1822 -- element_information18),
1823 --element_information19 = NVL(decode_elmnt_bal_information('US',
1824 -- 'ELEMENT'
1825 -- , NULL, element_information19),
1826 -- element_information19),
1827 --element_information20 = NVL(decode_elmnt_bal_information('US',
1828 -- 'ELEMENT'
1829 -- , NULL, element_information20),
1830 -- element_information20)
1831 WHERE element_type_id = pay_rec.element_type_id;
1832
1833 ELSIF pay_rec.element_information_category = 'US_NON-PAYROLL PAYMENTS'
1834 THEN
1835 UPDATE pay_element_types_f
1836 SET element_information10 = NVL(decode_elmnt_bal_information('US',
1837 'BALANCE'
1838 , NULL, element_information10),
1839 element_information10),
1840 element_information16 = NVL(decode_elmnt_bal_information('US',
1841 'BALANCE'
1842 , NULL, element_information16),
1843 element_information16),
1844 element_information17 = NVL(decode_elmnt_bal_information('US',
1845 'BALANCE'
1846 , NULL, element_information17),
1847 element_information17)--,
1848 --element_information18 = NVL(decode_elmnt_bal_information('US',
1849 -- 'ELEMENT'
1850 -- , NULL, element_information18),
1851 -- element_information18),
1852 --element_information19 = NVL(decode_elmnt_bal_information('US',
1853 -- 'ELEMENT'
1854 -- , NULL, element_information19),
1855 -- element_information19),
1856 --element_information20 = NVL(decode_elmnt_bal_information('US',
1857 -- 'ELEMENT'
1858 -- , NULL, element_information20),
1859 -- element_information20)
1860 WHERE element_type_id = pay_rec.element_type_id;
1861
1862 ELSIF pay_rec.element_information_category = 'US_PRE-TAX DEDUCTIONS'
1863 THEN
1864 UPDATE pay_element_types_f
1865 SET element_information10 = NVL(decode_elmnt_bal_information('US',
1866 'BALANCE'
1867 , NULL, element_information10),
1868 element_information10),
1869 element_information11 = NVL(decode_elmnt_bal_information('US',
1870 'BALANCE'
1871 , NULL, element_information11),
1872 element_information11),
1873 element_information12 = NVL(decode_elmnt_bal_information('US',
1874 'BALANCE'
1875 , NULL, element_information12),
1876 element_information12),
1877 element_information13 = NVL(decode_elmnt_bal_information('US',
1878 'BALANCE'
1879 , NULL, element_information13),
1880 element_information13),
1881 element_information14 = NVL(decode_elmnt_bal_information('US',
1882 'BALANCE'
1883 , NULL, element_information14),
1884 element_information14),
1885 element_information15 = NVL(decode_elmnt_bal_information('US',
1886 'BALANCE'
1887 , NULL, element_information15),
1888 element_information15),
1889 element_information16 = NVL(decode_elmnt_bal_information('US',
1890 'BALANCE'
1891 , NULL, element_information16),
1892 element_information16),
1893 element_information17 = NVL(decode_elmnt_bal_information('US',
1894 'BALANCE'
1895 , NULL, element_information17),
1896 element_information17)--,
1897 --element_information18 = NVL(decode_elmnt_bal_information('US',
1898 -- 'ELEMENT'
1899 -- , NULL, element_information18),
1900 -- element_information18),
1901 --element_information19 = NVL(decode_elmnt_bal_information('US',
1902 -- 'ELEMENT'
1903 -- , NULL, element_information19),
1904 -- element_information19),
1905 --element_information20 = NVL(decode_elmnt_bal_information('US',
1906 -- 'ELEMENT'
1907 -- , NULL, element_information20),
1908 -- element_information20)
1909 WHERE element_type_id = pay_rec.element_type_id;
1910
1911 ELSIF pay_rec.element_information_category = 'US_PTO ACCRUALS'
1912 THEN
1913 UPDATE pay_element_types_f
1914 SET element_information10 = NVL(decode_elmnt_bal_information('US',
1915 'BALANCE'
1916 , NULL, element_information10),
1917 element_information10)
1918 WHERE element_type_id = pay_rec.element_type_id;
1919
1920 ELSIF pay_rec.element_information_category = 'US_SUPPLEMENTAL EARNINGS'
1921 THEN
1922 UPDATE pay_element_types_f
1923 SET element_information10 = NVL(decode_elmnt_bal_information('US',
1924 'BALANCE'
1925 , NULL, element_information10),
1926 element_information10),
1927 element_information12 = NVL(decode_elmnt_bal_information('US',
1928 'BALANCE'
1929 , NULL, element_information12),
1930 element_information12),
1931 element_information16 = NVL(decode_elmnt_bal_information('US',
1932 'BALANCE'
1933 , NULL, element_information16),
1934 element_information16),
1935 element_information17 = NVL(decode_elmnt_bal_information('US',
1936 'BALANCE'
1937 , NULL, element_information17),
1938 element_information17)--,
1939 --element_information18 = NVL(decode_elmnt_bal_information('US',
1940 -- 'ELEMENT'
1941 -- , NULL, element_information18),
1942 -- element_information18),
1943 --element_information19 = NVL(decode_elmnt_bal_information('US',
1944 -- 'ELEMENT'
1945 -- , NULL, element_information19),
1946 -- element_information19),
1947 --element_information20 = NVL(decode_elmnt_bal_information('US',
1948 -- 'ELEMENT'
1949 -- , NULL, element_information20),
1950 -- element_information20)
1951 WHERE element_type_id = pay_rec.element_type_id;
1952
1953 ELSIF pay_rec.element_information_category = 'US_TAX DEDUCTIONS'
1954 THEN
1955 UPDATE pay_element_types_f
1956 SET element_information10 = NVL(decode_elmnt_bal_information('US',
1957 'BALANCE'
1958 , NULL, element_information10),
1959 element_information10),
1960 element_information11 = NVL(decode_elmnt_bal_information('US',
1961 'BALANCE'
1962 , NULL, element_information11),
1963 element_information11),
1964 element_information12 = NVL(decode_elmnt_bal_information('US',
1965 'BALANCE'
1966 , NULL, element_information12),
1967 element_information12),
1968 element_information13 = NVL(decode_elmnt_bal_information('US',
1969 'BALANCE'
1970 , NULL, element_information13),
1971 element_information13),
1972 element_information14 = NVL(decode_elmnt_bal_information('US',
1973 'BALANCE'
1974 , NULL, element_information14),
1975 element_information14),
1976 element_information15 = NVL(decode_elmnt_bal_information('US',
1977 'BALANCE'
1978 , NULL, element_information15),
1979 element_information15),
1980 element_information16 = NVL(decode_elmnt_bal_information('US',
1981 'BALANCE'
1982 , NULL, element_information16),
1983 element_information16),
1984 element_information17 = NVL(decode_elmnt_bal_information('US',
1985 'BALANCE'
1986 , NULL, element_information17),
1987 element_information17),
1988 element_information18 = NVL(decode_elmnt_bal_information('US',
1989 'BALANCE'
1990 , NULL, element_information18),
1991 element_information18),
1992 element_information19 = NVL(decode_elmnt_bal_information('US',
1993 'BALANCE'
1994 , NULL, element_information19),
1995 element_information19)
1996 WHERE element_type_id = pay_rec.element_type_id;
1997
1998 ELSIF pay_rec.element_information_category = 'US_VOLUNTARY DEDUCTIONS'
1999 THEN
2000 UPDATE pay_element_types_f
2001 SET element_information10 = NVL(decode_elmnt_bal_information('US',
2002 'BALANCE'
2003 , NULL, element_information10),
2004 element_information10),
2005 element_information11 = NVL(decode_elmnt_bal_information('US',
2006 'BALANCE'
2007 , NULL, element_information11),
2008 element_information11),
2009 element_information12 = NVL(decode_elmnt_bal_information('US',
2010 'BALANCE'
2011 , NULL, element_information12),
2012 element_information12),
2013 element_information13 = NVL(decode_elmnt_bal_information('US',
2014 'BALANCE'
2015 , NULL, element_information13),
2016 element_information13),
2017 element_information14 = NVL(decode_elmnt_bal_information('US',
2018 'BALANCE'
2019 , NULL, element_information14),
2020 element_information14),
2021 element_information16 = NVL(decode_elmnt_bal_information('US',
2022 'BALANCE'
2023 , NULL, element_information16),
2024 element_information16),
2025 element_information17 = NVL(decode_elmnt_bal_information('US',
2026 'BALANCE'
2027 , NULL, element_information17),
2028 element_information17)--,
2029 --element_information18 = NVL(decode_elmnt_bal_information('US',
2030 -- 'ELEMENT'
2031 -- , NULL, element_information18),
2032 -- element_information18),
2033 --element_information19 = NVL(decode_elmnt_bal_information('US',
2034 -- 'ELEMENT'
2035 -- , NULL, element_information19),
2036 -- element_information19),
2037 --element_information20 = NVL(decode_elmnt_bal_information('US',
2038 -- 'ELEMENT'
2039 -- , NULL, element_information20),
2040 -- element_information20)
2041 WHERE element_type_id = pay_rec.element_type_id;
2042
2043 END IF;
2044 END LOOP;
2045 END IF;
2046
2047 END translate_us_ele_dev_df;
2048 ----------------------------------------------------------------------
2049 -- PROCEDURE TRANSLATE_CA_ELE_DEV_DF
2050 -- This is a copy of translate_us_ele_dev_df, modified for CA use
2051 ----------------------------------------------------------------------
2052 PROCEDURE translate_ca_ele_dev_df(p_mode VARCHAR2)
2053 IS
2054 CURSOR csr_hr_s_element_details IS
2055 SELECT element_type_id,
2056 element_information_category,
2057 element_information10
2058 FROM hr_s_element_types_f
2059 WHERE legislation_code = 'CA'
2060 AND element_information_category IS NOT NULL
2061 FOR UPDATE OF element_information10 NOWAIT;
2062
2063 CURSOR csr_pay_element_details IS
2064 SELECT element_type_id,
2065 element_information_category,
2066 element_information10
2067 FROM pay_element_types_f
2068 WHERE legislation_code = 'CA'
2069 AND element_information_category IS NOT NULL
2070 FOR UPDATE OF element_information10 NOWAIT;
2071
2072 BEGIN
2073 IF p_mode = 'ID_TO_NAME' THEN
2074 FOR hr_s_rec IN csr_hr_s_element_details
2075 LOOP
2076 IF hr_s_rec.element_information_category = 'CA_EARNINGS'
2077 THEN
2078 --
2079 UPDATE hr_s_element_types_f
2080 SET element_information10 = NVL(decode_elmnt_bal_information('CA',
2081 'BALANCE'
2082 , element_information10, NULL),
2083 element_information10),
2084 element_information12 = NVL(decode_elmnt_bal_information('CA',
2085 'BALANCE'
2086 , element_information12, NULL),
2087 element_information12)
2088 WHERE element_type_id = hr_s_rec.element_type_id;
2089
2090 ELSIF hr_s_rec.element_information_category = 'CA_TAXABLE BENEFITS'
2091 THEN
2092
2093 UPDATE hr_s_element_types_f
2094 SET element_information10 = NVL(decode_elmnt_bal_information('CA',
2095 'BALANCE'
2096 , element_information10, NULL),
2097 element_information10),
2098 element_information12 = NVL(decode_elmnt_bal_information('CA',
2099 'BALANCE'
2100 , element_information12, NULL),
2101 element_information12)
2102 WHERE element_type_id = hr_s_rec.element_type_id;
2103
2104 ELSIF hr_s_rec.element_information_category = 'CA_INVOLUNTARY DEDUCTIONS'
2105 THEN
2106 UPDATE hr_s_element_types_f
2107 SET
2108 element_information10 = NVL(decode_elmnt_bal_information('CA',
2109 'BALANCE'
2110 , element_information10, NULL),
2111 element_information10),
2112 element_information11 = NVL(decode_elmnt_bal_information('CA',
2113 'BALANCE'
2114 , element_information11, NULL),
2115 element_information11),
2116 element_information12 = NVL(decode_elmnt_bal_information('CA',
2117 'BALANCE'
2118 , element_information12, NULL),
2119 element_information12),
2120 element_information13 = NVL(decode_elmnt_bal_information('CA',
2121 'BALANCE'
2122 , element_information13, NULL),
2123 element_information13),
2124 element_information15 = NVL(decode_elmnt_bal_information('CA',
2125 'BALANCE'
2126 , element_information15, NULL),
2127 element_information15),
2128 element_information16 = NVL(decode_elmnt_bal_information('CA',
2129 'BALANCE'
2130 , element_information16, NULL),
2131 element_information16),
2132 element_information17 = NVL(decode_elmnt_bal_information('CA',
2133 'BALANCE'
2134 , element_information17, NULL),
2135 element_information17)
2136 WHERE element_type_id = hr_s_rec.element_type_id;
2137
2138 ELSIF hr_s_rec.element_information_category = 'CA_NON-PAYROLL PAYMENTS'
2139 THEN
2140 UPDATE hr_s_element_types_f
2141 SET element_information10 = NVL(decode_elmnt_bal_information('CA',
2142 'BALANCE'
2143 , element_information10, NULL),
2144 element_information10)
2145 WHERE element_type_id = hr_s_rec.element_type_id;
2146
2147 ELSIF hr_s_rec.element_information_category = 'CA_PRE-TAX DEDUCTIONS'
2148 THEN
2149 UPDATE hr_s_element_types_f
2150 SET element_information10 = NVL(decode_elmnt_bal_information('CA',
2151 'BALANCE'
2152 , element_information10, NULL),
2153 element_information10),
2154 element_information11 = NVL(decode_elmnt_bal_information('CA',
2155 'BALANCE'
2156 , element_information11, NULL),
2157 element_information11),
2158 element_information12 = NVL(decode_elmnt_bal_information('CA',
2159 'BALANCE'
2160 , element_information12, NULL),
2161 element_information12),
2162 element_information13 = NVL(decode_elmnt_bal_information('CA',
2163 'BALANCE'
2164 , element_information13, NULL),
2165 element_information13)
2166 WHERE element_type_id = hr_s_rec.element_type_id;
2167
2168
2169 ELSIF hr_s_rec.element_information_category = 'CA_SUPPLEMENTAL EARNINGS'
2170 THEN
2171 UPDATE hr_s_element_types_f
2172 SET element_information10 = NVL(decode_elmnt_bal_information('CA',
2173 'BALANCE'
2174 , element_information10, NULL),
2175 element_information10),
2176 element_information12 = NVL(decode_elmnt_bal_information('CA',
2177 'BALANCE'
2178 , element_information12, NULL),
2179 element_information12)
2180 WHERE element_type_id = hr_s_rec.element_type_id;
2181
2182
2183 ELSIF hr_s_rec.element_information_category = 'CA_VOLUNTARY DEDUCTIONS'
2184 THEN
2185 UPDATE hr_s_element_types_f
2186 SET element_information10 = NVL(decode_elmnt_bal_information('CA',
2187 'BALANCE'
2188 , element_information10, NULL),
2189 element_information10),
2190 element_information11 = NVL(decode_elmnt_bal_information('CA',
2191 'BALANCE'
2192 , element_information11, NULL),
2193 element_information11),
2194 element_information12 = NVL(decode_elmnt_bal_information('CA',
2195 'BALANCE'
2196 , element_information12, NULL),
2197 element_information12),
2198 element_information13 = NVL(decode_elmnt_bal_information('CA',
2199 'BALANCE'
2200 , element_information13, NULL),
2201 element_information13)
2202 WHERE element_type_id = hr_s_rec.element_type_id;
2203
2204 END IF;
2205 END LOOP;
2206 ELSE /* NAME_TO_ID */
2207 FOR pay_rec IN csr_pay_element_details
2208 LOOP
2209 IF pay_rec.element_information_category = 'CA_EARNINGS'
2210 THEN
2211
2212 UPDATE pay_element_types_f
2213 SET element_information10 = NVL(decode_elmnt_bal_information('CA',
2214 'BALANCE', NULL, element_information10),
2215 element_information10),
2216 element_information12 = NVL(decode_elmnt_bal_information('CA',
2217 'BALANCE', NULL, element_information12),
2218 element_information12)
2219 WHERE element_type_id = pay_rec.element_type_id;
2220
2221 ELSIF pay_rec.element_information_category = 'CA_TAXABLE BENEFITS'
2222 THEN
2223
2224 UPDATE pay_element_types_f
2225 SET element_information10 = NVL(decode_elmnt_bal_information('CA',
2226 'BALANCE'
2227 , NULL, element_information10),
2228 element_information10),
2229 element_information12 = NVL(decode_elmnt_bal_information('CA',
2230 'BALANCE'
2231 , NULL, element_information12),
2232 element_information12)
2233 WHERE element_type_id = pay_rec.element_type_id;
2234
2235 ELSIF pay_rec.element_information_category = 'CA_INVOLUNTARY DEDUCTIONS'
2236 THEN
2237 UPDATE pay_element_types_f
2238 SET
2239 element_information10 = NVL(decode_elmnt_bal_information('CA',
2240 'BALANCE'
2241 , NULL, element_information10),
2242 element_information10),
2243 element_information11 = NVL(decode_elmnt_bal_information('CA',
2244 'BALANCE'
2245 , NULL, element_information11),
2246 element_information11),
2247 element_information12 = NVL(decode_elmnt_bal_information('CA',
2248 'BALANCE'
2249 , NULL, element_information12),
2250 element_information12),
2251 element_information13 = NVL(decode_elmnt_bal_information('CA',
2252 'BALANCE'
2253 , NULL, element_information13),
2254 element_information13),
2255 element_information15 = NVL(decode_elmnt_bal_information('CA',
2256 'BALANCE'
2257 , NULL, element_information15),
2258 element_information15),
2259 element_information16 = NVL(decode_elmnt_bal_information('CA',
2260 'BALANCE'
2261 , NULL, element_information16),
2262 element_information16),
2263 element_information17 = NVL(decode_elmnt_bal_information('CA',
2264 'BALANCE'
2265 , NULL, element_information17),
2266 element_information17)
2267 WHERE element_type_id = pay_rec.element_type_id;
2268
2269 ELSIF pay_rec.element_information_category = 'CA_NON-PAYROLL PAYMENTS'
2270 THEN
2271 UPDATE pay_element_types_f
2272 SET element_information10 = NVL(decode_elmnt_bal_information('CA',
2273 'BALANCE'
2274 , NULL, element_information10),
2275 element_information10)
2276 WHERE element_type_id = pay_rec.element_type_id;
2277
2278 ELSIF pay_rec.element_information_category = 'CA_PRE-TAX DEDUCTIONS'
2279 THEN
2280 UPDATE pay_element_types_f
2281 SET element_information10 = NVL(decode_elmnt_bal_information('CA',
2282 'BALANCE'
2283 , NULL, element_information10),
2284 element_information10),
2285 element_information11 = NVL(decode_elmnt_bal_information('CA',
2286 'BALANCE'
2287 , NULL, element_information11),
2288 element_information11),
2289 element_information12 = NVL(decode_elmnt_bal_information('CA',
2290 'BALANCE'
2291 , NULL, element_information12),
2292 element_information12),
2293 element_information13 = NVL(decode_elmnt_bal_information('CA',
2294 'BALANCE'
2295 , NULL, element_information13),
2296 element_information13)
2297 WHERE element_type_id = pay_rec.element_type_id;
2298
2299
2300 ELSIF pay_rec.element_information_category = 'CA_SUPPLEMENTAL EARNINGS'
2301 THEN
2302 UPDATE pay_element_types_f
2303 SET element_information10 = NVL(decode_elmnt_bal_information('CA',
2304 'BALANCE'
2305 , NULL, element_information10),
2306 element_information10),
2307 element_information12 = NVL(decode_elmnt_bal_information('CA',
2308 'BALANCE'
2309 , NULL, element_information12),
2310 element_information12)
2311 WHERE element_type_id = pay_rec.element_type_id;
2312
2313
2314 ELSIF pay_rec.element_information_category = 'CA_VOLUNTARY DEDUCTIONS'
2315 THEN
2316 UPDATE pay_element_types_f
2317 SET element_information10 = NVL(decode_elmnt_bal_information('CA',
2318 'BALANCE'
2319 , NULL, element_information10),
2320 element_information10),
2321 element_information11 = NVL(decode_elmnt_bal_information('CA',
2322 'BALANCE'
2323 , NULL, element_information11),
2324 element_information11),
2325 element_information12 = NVL(decode_elmnt_bal_information('CA',
2326 'BALANCE'
2327 , NULL, element_information12),
2328 element_information12),
2329 element_information13 = NVL(decode_elmnt_bal_information('CA',
2330 'BALANCE'
2331 , NULL, element_information13),
2332 element_information13)
2333 WHERE element_type_id = pay_rec.element_type_id;
2334
2335 END IF;
2336 END LOOP;
2337 END IF;
2338
2339 END translate_ca_ele_dev_df;
2340
2341 --****************************************************************************
2342 -- OVERALL INSTALLATION PROCEDURE FOR LEGISLATIVE DELIVERY
2343 --****************************************************************************
2344
2345 PROCEDURE install(p_phase number)
2346 ---------------------------------
2347 IS
2348 -- Main driving procedure to execute specific legislative routines.
2349
2350 CURSOR c_legs IS
2351 select legislation_code
2352 from hr_s_history;
2353
2354 BEGIN
2355
2356 hr_legislation.hrrunprc_trace_on;
2357 hr_utility.trace('start install_leg_loc: ' || to_char(p_phase));
2358 hr_legislation.hrrunprc_trace_off;
2359
2360 FOR r_legs IN c_legs LOOP
2361
2362 hr_legislation.hrrunprc_trace_on;
2363 hr_utility.trace('start install_leg_loc: leg_code: ' || r_legs.legislation_code ||
2364 ': ' || to_char(p_phase));
2365 hr_legislation.hrrunprc_trace_off;
2366
2367 driving_legislation := r_legs.legislation_code;
2368
2369 IF p_phase = 1 OR p_phase = 2 THEN
2370
2371
2372 hr_legislation.hrrunprc_trace_on;
2373 hr_utility.trace('start install_leg_rules: ' || to_char(p_phase));
2374 hr_legislation.hrrunprc_trace_off;
2375
2376 install_leg_rules(p_phase); --install legislation rules
2377
2378 IF driving_legislation = 'US' THEN
2379
2380
2381 hr_legislation.hrrunprc_trace_on;
2382 hr_utility.trace('start install_state_rules: ' || to_char(p_phase));
2383 hr_legislation.hrrunprc_trace_off;
2384
2385 install_state_rules(p_phase); --install pay_state_rules
2386
2387 hr_legislation.hrrunprc_trace_on;
2388 hr_utility.trace('start install_tax_rules: ' || to_char(p_phase));
2389 hr_legislation.hrrunprc_trace_off;
2390
2391 install_tax_rules(p_phase); --install pay_taxability_rules
2392
2393 hr_legislation.hrrunprc_trace_on;
2394 hr_utility.trace('start install_surcharges: ' || to_char(p_phase));
2395 hr_legislation.hrrunprc_trace_off;
2396
2397 install_surcharges(p_phase); --install Wcomp state surcharges
2398
2399 hr_legislation.hrrunprc_trace_on;
2400 hr_utility.trace('start install_us_new: ' || to_char(p_phase));
2401 hr_legislation.hrrunprc_trace_off;
2402
2403 install_us_new(p_phase); --install new US misc
2404
2405 END IF;
2406
2407 IF driving_legislation = 'CA' THEN
2408 --
2409
2410 hr_legislation.hrrunprc_trace_on;
2411 hr_utility.trace('start install_tax_rules: ' || to_char(p_phase));
2412 hr_legislation.hrrunprc_trace_off;
2413
2414 install_tax_rules(p_phase); -- install pay_taxability_rules
2415 --
2416 END IF;
2417 --
2418 IF driving_legislation = 'GB' THEN
2419
2420 hr_legislation.hrrunprc_trace_on;
2421 hr_utility.trace('start gb scl flex delete: ' || to_char(p_phase));
2422 hr_legislation.hrrunprc_trace_off;
2423
2424 --the gb scl flex only needs to appear at payroll sites
2425 delete from pay_legislation_rules
2426 where legislation_code = 'GB'
2427 and rule_type = 'S'
2428 and not exists
2429 (select null
2430 from fnd_product_installations
2431 where application_id = 801
2432 and status = 'I'
2433 );
2434
2435
2436 --The GB org tax details only needs to appear at payroll sites
2437 delete from hr_org_info_types_by_class
2438 where org_information_type = 'Tax Details References'
2439 and ORG_CLASSIFICATION = 'HR_BG'
2440 and not exists
2441 (select null
2442 from fnd_product_installations
2443 where application_id = 801
2444 and status = 'I'
2445 );
2446
2447 END IF; --end GB specific logic
2448
2449
2450 END IF; --end phase control check
2451
2452 hr_legislation.hrrunprc_trace_on;
2453 hr_utility.trace('end install_leg_loc: leg_code: ' || r_legs.legislation_code ||
2454 ': ' || to_char(p_phase));
2455 hr_legislation.hrrunprc_trace_off;
2456
2457 END LOOP; -- c_legs cursor
2458
2459 hr_legislation.hrrunprc_trace_on;
2460 hr_utility.trace('exit install_leg_loc: ' || to_char(p_phase));
2461 hr_legislation.hrrunprc_trace_off;
2462
2463 END install;
2464
2465 END hr_legislation_local;