[Home] [Help]
PACKAGE BODY: APPS.HR_US_GARN_GEN
Source
1 package body hr_us_garn_gen as
2 /*$Header: pywatgen.pkb 120.1.12000000.1 2007/01/18 03:19:40 appldev noship $*/
3
4 /*
5 +======================================================================+
6 | Copyright (c) 1993 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +======================================================================+
10
11 Name : Wage Attachment Generator
12 Filename : pywatgen.pkb
13 Change List
14 -----------
15 Date Name Vers Bug No Description
16 ---- ---- ---- ------ -----------
17 13-NOV-95 hparicha 40.0 Created.
18 19-DEC-95 jthuring 40.3 Fixed RCS header
19 09-JAN-96 hparicha 40.4 333133 Added defined balances
20 required for summary reporting.
21 20-FEB-96 hparicha 40.5 338498 Garn fees must be reported
22 separately from
23 garn deduction...add associated bal
24 and ele, along with appropriate bal
25 feeds and formula results.
26 11-MAR-96 hparicha Don't forget to associate the fee bal
27 with fee ele as the
28 primary balance; and to make
29 classification feeds for
30 the input values fed with Fee results
31 - ie. feed all balances to be fed by
32 involuntary deductions.
33 28-MAR-96 hparicha 40.6 353325: Adding params to allow user
34 entered reporting name and description
35 of wage attachment elements.
36 353321: Freq rules can now be entered
37 for invol dedns.
38 15th April 1996 Hparicha 40.7 350540 : Not Taken and Adj Not Taken
39 input values added
40 to Special Features. Results from
41 base and verif ffs, feeds to
42 associated balance Not Taken.
43 16th April 1996 Hparicha 344518 : Arrears Bal Amount and
44 Support Other Family ivs
45 added to base and calc. Results from
46 base to calc for supp other fam
47 (reusing arrears bal amount from base
48 to calc).
49 Also, modify result rules from calc
50 and verif to feed Pay Values of base
51 and fee elements for costing purposes.
52 Also, adding feeds to Accrued assoc bal
53 for proper total owed processing.
54 14th July 1996 hparicha 40.8 373235 - friendlier iv names.
55 374743 - spousal support and
56 alimony are treated same as
57 child support - ie. they are
58 all "support" orders.
59 Adding checks for existence before
60 creation of elements, etc...this
61 makes wage attachments upgradeable.
62 Also making iv name changes as per bug
63 text.
64 365745 - New high level category bal
65 for tax levies must be fed when
66 category is tax levy.
67 378699 - New high level adjusted
68 support bal must be fed from verifier
69 result via SF input val.
70 25th July 1996 hparicha 40.9 Various changes during SWAMP QA.
71 40.10 Removed various out-dated inputs.
72 40.11 Changed withheld fee result so that it
73 hits an input value on Fee element so
74 a run result is created...so the Fee
75 shows up on SOE et al.
76 30th July 1996 hparicha 40.12 Added new result rule to Order Indirect
77 subpriority of Calculator elements.
78 31st July 1996 hparicha 40.13 Removed underscores from iv names
79 (again).
80 7th Aug 1996 hparicha 40.14 388710. Added formula resrule
81 to pass Date In Arrears input
82 value from base to calculator ele.
83 14th Aug 1996 hparicha 40.15 New variable for l_upgrade_mode.
84 This var defaults to No. It is
85 only used when running the package
86 in upgrade mode. Upgrade mode is
87 inferred when base element already
88 exists. When upgrade mode is Yes, the
89 new API for adding an input value
90 over the lifetime of an element type
91 will be called. This new api will
92 then create all link_input_value,
93 element_entry_value, and
94 run_result_value records as needed.
95 17th Sep 1996 hparicha 40.16 374391. Adding input values for
96 monthly cap processing on support
97 orders.
98 20th Sep 1996 hparicha 40.17 Upgrade mode now set based on
99 UPPER of element name comparison.
100 3rd Oct 1996 hparicha 40.18 398791. Added param to existence
101 functions (pytmplex.pkb) for an
102 effective date to be used in
103 existence comparisons.
104 5th Nov 1996 hparicha 40.19 413211. Updated deletion procedure
105 with respect to latest configuration.
106 7th Nov 1996 hparicha 40.20 413211 - again...deletion procedure needs to
107 cleanup OLD formulae creating during upgrades.
108
109 10th July 1997 mmukherj 40.21 502307 Updated do_defined_balances
110 procedure.Included business_group_id
111 in where condition while checking
112 pay_defined_balnce has already exist
113 for that balance_name for that busines_
114 group_id or not
115 21st July 1997 mmukherj 40.22 Put Version No in the comment above (
116 10th July 1997) and added comment to
117 do_defined_balance procedure.
118 Changed the select statement of the
119 same proceduerto avoid using index on
120 business_group_id.
121
122 7th Nov 1997 rpanchap 40.23 459552 Updated the function create_garnishment
123 to Populate the text table, garn_base_iv_names,
124 based on 2 categories (Support and
125 the rest of the categories grouped as one)
126
127 9th Jan 1998 mmukherj 40.24 566328 delete_dedn procedure is modified to include
128 business_group_id in one a select(without cursor)
129 statement, which was selecting more than one row
130 for a given element_name.
131
132 14th Jul 1998 ssarma 40.25 625442 Fee indirect result from calculator
133 formula now feeds the fee element
134 instead of the verifier element
135 incase of non-support invlountary
136 deductions.
137
138 03rd Nov 1998 ssarma 40.26 658479 Added run result Calc_Subprio to all associated
139 elements so that subpriority can be processed.
140 Changed the processing priority of all Garnishment
141 elements so that CS/AY/SS and TL process at the
142 highest priority based on date served. followed by
143 BO,CD/G,EL and ER.
144
145 01 Dec 1998 ssarma 40.27 658479 Changed priority for Tax levy
146 calculation element to be equal to that of support
147 calculator elements. Added formula result rules
148 of Garn_fee in calculator. Added Diff_dedn_amt
149 and Diff_fee_amt formula result rules to verifier
150 element. Added delete stmt to delete formula
151 result rules from pay_formula_result_rules_f
152 so that no unnecessary result rules are hanging !
153
154 04 Dec 1998 ssarma 40.28 774717 Added back dedn at time of writ
155 to the tax levy element.
156
157 11 Dec 1998 ssarma 40.29 771631 Changed the processing priority of the tax
158 calculator element to be equal with the support
159 verifier element.
160
161 08-FEB-1999 ssarma 40.36 Max Per Period input value added in the base element
162 and corresponging input values/FRR to indirect elmt.
163
164 05-MAR-1999 ahanda 40.37 Added dimension for assignment Inception to Date.
165 Also changed the priority for Special Inputs for
166 garnishment and edu. loan to process before
167 base element.
168
169 23-SEP-1999 ssarma 40.39 Made change from = to like for old formula
170 select statement.
171 21-APR-2000 fusman 40.43 New PTD Fee Balance,Month Fee Balance and
172 Accrued Fee Balance are created.
173 For Garnishment: Acc_Fee=>Garn.Cal=>
174 Spl.Fee. PTD,Month=>Calculator
175 For Child.Supp
176 Acc_Fee=>Chi.Sup.Cal=>Prio=>Verif=>
177 Spl.Features PTD,Month=>Chi.Sup.Cal
178 =>Prio=>Verif.
179 21-APR-2000 fusman 40.44 Changed the suffixes from database_item
180 _suffixes to dimension names as names
181 have index which will speed up
182 the process when creating the balances.
183 24-APR-2000 fusman 40.45 Corrected the Dimension name
184 Assignment-Level Current Run.
185 24-APR-2000 fusman 40.46 Removed the balances PTD and Month since
186 only Accrued balance is used in two
187 dimensions
188 03-MAY-2000 fusman 115.17 Changed the dates to fnd.canonical format.
189 24-Oct-2000 fusman 115.18 New dimensions PTD,ITD and Month has been
190 included.
191 27-Dec-2000 fusman 115.19 1348004 Changed the priority of EL same as G,CD.
192 1498260 Added date check in the select stmt
193 of input_value_id
194 03-Jan-2001 fusman 115.20 Added Compile_flag 'N' for Old formulas.
195 22-JAN-2002 ahanda 115.21 Added creation of _ASG_PAYMENTS defined
196 balance ID.
197 19-Jul-2002 ekim 115.23 Added balance feed to 'Child Supp Total
198 Amount' for bug 2374248.
199 21-Aug-2002 ekim 115.24 Chaged the formula result rule of the
200 Calculator element. The result of
201 to_addl, and to_repl should feed Special
202 Features element not Special Inputs
203 element.
204 22-Aug-2002 ekim 115.25 2527761 Commented update of element_information12
205 and element_information13 to keep bug
206 980683 fix and remove setting not_taken
207 to 0 in the calculator formula
208 pyusgarnfedlycal.hdt for other wage attach
209 and verifier formula pyusgarnchsupver
210 for support.
211
212 Added following balances for Special
213 Features element with IV of Not Taken
214 will feed to this balance.
215 Support Not Taken Amount
216 Other Garn Not Taken Amount
217 12-sep-2002 ekim 115.27 Removed elisa from trace message.
218 08-Oct-2002 ekim 115.28 2603525 Changed UOM to pass lookup code
219 instead of meaning. (ex.M not Money)
220 Changed to use p_uom_code to call
221 pay_db_pay_setup.create_input_value
222 and create_balance_type instead
223 of p_uom.
224 10-Oct-2002 ekim 115.29 Removed default value from delete_dedn
225 for GSCC warning.
226 07-Jan-2003 ekim 115.30 Made performance change for the query
227 which gets l_count_already.
228 Bug 2721714.
229 01-APR-2003 ahanda 115.31 Fixed the issue with the select stmt
230 changed for bug 2721714.
231 01-APR-2003 ahanda 115.32 Changed the defined balance creation call
232 to store _ASG_GRE_RUN as run balance.
233 26-JUN-2003 ahanda 115.33 Changed call to create_balance_type procedure
234 to pass the balance name
235 Added code to populate 'After-Tax Deductions'
236 category for balances
237 18-MAR-2004 kvsankar 115.34 3311781 Changed call to create_balance_type procedure
238 to pass the balance category as
239 'Involuntary Deductions' instead of
240 'After-Tax Deductions'.
241 09-JUN-2004 kvsankar 115.35 3622290 Changed the delete_dedn procedure to
242 delete the elements based on the new
243 architecture for Involuntary deductions
244 element. The procedure does delete
245 elements created using the old
246 architecture.
247 17-JUN-2004 kvsankar 115.36 3682501 Added code for deleting the template
248 created while creating an Involuntary
249 Deduction element in the new
250 architecture. Also modified the
251 delete_dedn procedure to take into
252 account 'Arrears' and 'Not Taken'
253 balances for deletion.
254 18-AUG-2004 sdhole 115.37 3651755 Removed the balance category for the
255 following balances Additional, Replacement
256 Accrued Fees, Vol Dedns.
257 30-JAN-2006 kvsankar 115.38 4680388 Modified the delete_dedn procedure
258 to use delete_user_structure for
259 deleting elements created using
260 template engine
261 ***************************************************************************/
262
263 FUNCTION create_garnishment (
264 p_garn_name IN VARCHAR2,
265 p_garn_reporting_name IN VARCHAR2,
266 p_garn_description IN VARCHAR2,
267 p_category IN VARCHAR2,
268 p_bg_id IN NUMBER,
269 p_ele_eff_start_date IN DATE) RETURN NUMBER IS
270
271 c_end_of_time CONSTANT DATE := fnd_date.canonical_to_date('4712/12/31 00:00:00');
272
273 TYPE text_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
274 TYPE num_table IS TABLE OF NUMBER(9) INDEX BY BINARY_INTEGER;
275
276 garn_ele_names text_table;
277 garn_ele_repnames text_table;
278 garn_ele_proc_type text_table;
279 garn_ele_desc text_table;
280 garn_ele_priority num_table;
281 garn_indirect_only text_table;
282 garn_mix_category text_table;
283 garn_pay_formula text_table;
284 garn_ele_ids num_table;
285 garn_statproc_rule_id num_table;
286 garn_third_party_pay text_table;
287 garn_payval_id num_table;
288
289 garn_base_frr_name text_table;
290 garn_base_frr_type text_table;
291 garn_base_frr_ele_id num_table;
292 garn_base_frr_iv_id num_table;
293 garn_base_frr_severity text_table;
294
295 garn_calc_frr_name text_table;
296 garn_calc_frr_type text_table;
297 garn_calc_frr_ele_id num_table;
298 garn_calc_frr_iv_id num_table;
299 garn_calc_frr_severity text_table;
300
301 garn_verif_frr_name text_table;
302 garn_verif_frr_type text_table;
303 garn_verif_frr_ele_id num_table;
304 garn_verif_frr_iv_id num_table;
305 garn_verif_frr_severity text_table;
306
307 garn_si_frr_name text_table;
308 garn_si_frr_type text_table;
309 garn_si_frr_ele_id num_table;
310 garn_si_frr_iv_id num_table;
311 garn_si_frr_severity text_table;
312
313 garn_sf_frr_name text_table;
314 garn_sf_frr_type text_table;
315 garn_sf_frr_ele_id num_table;
316 garn_sf_frr_iv_id num_table;
317 garn_sf_frr_severity text_table;
318
319 garn_vp_frr_name text_table;
320 garn_vp_frr_type text_table;
321 garn_vp_frr_ele_id num_table;
322 garn_vp_frr_iv_id num_table;
323 garn_vp_frr_severity text_table;
324
325 garn_base_feed_iv_id num_table;
326 garn_base_feed_bal_id num_table;
327
328 garn_calc_feed_iv_id num_table;
329 garn_calc_feed_bal_id num_table;
330
331 garn_verif_feed_iv_id num_table;
332 garn_verif_feed_bal_id num_table;
333
334 garn_si_feed_iv_id num_table;
335 garn_si_feed_bal_id num_table;
336
337 garn_sf_feed_iv_id num_table;
338 garn_sf_feed_bal_id num_table;
339
340 garn_fee_feed_iv_id num_table;
341 garn_fee_feed_bal_id num_table;
342
343 garn_vp_feed_iv_id num_table;
344 garn_vp_feed_bal_id num_table;
345
346 l_num_base_resrules number;
347 l_num_calc_resrules number;
348 l_num_verif_resrules number;
349 l_num_si_resrules number;
350 l_num_sf_resrules number;
351 l_num_vp_resrules number;
352
353 l_num_base_feeds number;
354 l_num_calc_feeds number;
355 l_num_verif_feeds number;
356 l_num_si_feeds number;
357 l_num_sf_feeds number;
358 l_num_fee_feeds number;
359 l_num_vp_feeds number;
360
361 garn_assoc_bal_names text_table;
362 garn_assoc_bal_ids num_table;
363
364 garn_base_iv_names text_table;
365 garn_base_iv_seq num_table;
366 garn_base_iv_mand text_table;
367 garn_base_iv_uom text_table;
368 garn_base_iv_dbi text_table;
369 garn_base_iv_lkp text_table;
370 garn_base_iv_dflt text_table;
371 garn_base_iv_ids num_table;
372
373 garn_calc_iv_names text_table;
374 garn_calc_iv_seq num_table;
375 garn_calc_iv_mand text_table;
376 garn_calc_iv_uom text_table;
377 garn_calc_iv_dbi text_table;
378 garn_calc_iv_lkp text_table;
379 garn_calc_iv_dflt text_table;
380 garn_calc_iv_ids num_table;
381
382 garn_calc_payval_id number(9);
383
384 garn_verif_iv_names text_table;
385 garn_verif_iv_seq num_table;
386 garn_verif_iv_mand text_table;
387 garn_verif_iv_uom text_table;
388 garn_verif_iv_dbi text_table;
389 garn_verif_iv_lkp text_table;
390 garn_verif_iv_dflt text_table;
391 garn_verif_iv_ids num_table;
392
393 garn_si_iv_names text_table;
394 garn_si_iv_seq num_table;
395 garn_si_iv_mand text_table;
396 garn_si_iv_uom text_table;
397 garn_si_iv_dbi text_table;
398 garn_si_iv_lkp text_table;
399 garn_si_iv_dflt text_table;
400 garn_si_iv_ids num_table;
401
402 garn_sf_iv_names text_table;
403 garn_sf_iv_seq num_table;
404 garn_sf_iv_mand text_table;
405 garn_sf_iv_uom text_table;
406 garn_sf_iv_dbi text_table;
407 garn_sf_iv_lkp text_table;
408 garn_sf_iv_dflt text_table;
409 garn_sf_iv_ids num_table;
410
411 garn_fee_iv_names text_table;
412 garn_fee_iv_seq num_table;
413 garn_fee_iv_mand text_table;
414 garn_fee_iv_uom text_table;
415 garn_fee_iv_dbi text_table;
416 garn_fee_iv_lkp text_table;
417 garn_fee_iv_dflt text_table;
418 garn_fee_iv_ids num_table;
419
420 garn_vp_iv_names text_table;
421 garn_vp_iv_seq num_table;
422 garn_vp_iv_mand text_table;
423 garn_vp_iv_uom text_table;
424 garn_vp_iv_dbi text_table;
425 garn_vp_iv_lkp text_table;
426 garn_vp_iv_dflt text_table;
427 garn_vp_iv_ids num_table;
428
429 l_num_eles number;
430 l_num_assoc_bals number;
431
432 l_num_base_ivs number;
433 l_num_calc_ivs number;
434 l_num_verif_ivs number;
435 l_num_si_ivs number;
436 l_num_sf_ivs number;
437 l_num_fee_ivs number;
438 l_num_vp_ivs number;
439
440 lfee number;
441 nfee number;
442 i number;
443 k number;
444 n number;
445 o number;
446 p number;
447 q number;
448 r number;
449 s number;
450 t number;
451 c number;
452 vf number;
453 sif number;
454 scf number;
455 sf number;
456 siv number;
457 sfv number;
458
459 already_exists number;
460
461 -- Other various local vars
462
463 l_base_ele_id NUMBER(9);
464 v_bg_name VARCHAR2(80);
465 v_bal_type_id NUMBER(9);
466 v_ele_type_id NUMBER(9);
467 v_inpval_id NUMBER(9);
468 v_pay_value_name VARCHAR2(80);
469 v_payval_id NUMBER(9);
470 v_calc_formula_id NUMBER(9);
471 v_stat_proc_rule_id NUMBER(9);
472 v_spr_formula_id NUMBER(9);
473 v_fres_rule_id NUMBER(9);
474 v_frr_ele_id NUMBER(9);
475 v_frr_iv_id NUMBER(9);
476
477 v_ff_text VARCHAR2(32000);
478 v_balstp_ff_name VARCHAR2(80);
479
480 g_childsupp_count_balance_id NUMBER(9);
481 g_total_dedns_balance_id NUMBER(9);
482 g_total_amount_balance_id NUMBER(9);
483 g_total_fees_balance_id NUMBER(9);
484 g_supp_not_taken_bal_id NUMBER(9);
485 g_other_not_taken_bal_id NUMBER(9);
486
487 g_voldedns_balance_id NUMBER(9);
488
489 g_net_balance_id number(9);
490 g_payments_balance_id number(9);
491
492 /* 365745, 378699 */
493 g_wh_support_balance_id number(9);
494 g_wh_fee_balance_id number(9);
495 g_tax_levies_balance_id number(9);
496 g_wh_supp_over_flow_balance_id number(9);
497
498 g_ele_classification VARCHAR2(30) := 'INVOLUNTARY DEDUCTIONS';
499 g_ele_info_cat VARCHAR2(30) := 'US_INVOLUNTARY DEDUCTIONS';
500 g_ele_standard_link VARCHAR2(1) := 'N';
501 g_ele_priority NUMBER(9);
502 g_ele_class_id NUMBER(9);
503 g_skip_formula_id NUMBER(9);
504 g_partial_dedn VARCHAR2(30) := 'Y';
505 g_ele_runtype VARCHAR2(30) := 'REG';
506 -- was 'ALL', but now freq rules are allowed...
507 g_mix_category VARCHAR2(30) := 'D';
508
509 g_asst_status_type_id NUMBER(9) := NULL;
510 g_proc_rule VARCHAR2(1) := 'P';
511
512 g_eff_start_date DATE;
513 g_eff_end_date DATE := c_end_of_time;
514
515 l_involbal_id number(9);
516 l_invol_scale number(5);
517
518 l_upgrade_mode varchar2(1) := 'N';
519
520 cursor get_invol_bals is
521 select bt.balance_type_id,
522 bc.scale
523 from pay_balance_types bt,
524 pay_balance_classifications bc,
525 pay_element_classifications ec
526 where bt.balance_type_id = bc.balance_type_id
527 and bc.classification_id = ec.classification_id
528 and nvl(bc.business_group_id, p_bg_id) + 0 = p_bg_id
529 and ec.classification_name = 'Involuntary Deductions'
530 and ec.legislation_code = 'US'
531 order by bt.balance_name;
532
533 --
534 ---------------------------- ins_garn_ele_type -------------------------------
535 --
536 FUNCTION ins_garn_ele_type ( p_ele_name in varchar2,
537 p_ele_reporting_name in varchar2,
538 p_ele_description in varchar2,
539 p_ele_class in varchar2,
540 p_ele_category in varchar2,
541 p_ele_processing_type in varchar2,
542 p_ele_priority in number,
543 p_ele_standard_link in varchar2,
544 p_skip_formula_id in number default NULL,
545 p_ind_only_flag in varchar2,
546 p_third_party_pay in varchar2,
547 p_ele_eff_start_date in date,
548 p_ele_eff_end_date in date,
549 p_bg_name in varchar2,
550 p_bg_id in number)
551 RETURN number IS
552 -- local vars
553 ret NUMBER;
554 v_mult_entries_allowed VARCHAR2(1);
555 l_ele_priority NUMBER;
556
557 g_third_ppm VARCHAR2(30) := 'N';
558
559 already_exists number;
560
561 BEGIN
562
563 --
564
565 IF p_ele_processing_type = 'N' THEN
566
567 v_mult_entries_allowed := 'Y';
568
569 ELSE
570
571 v_mult_entries_allowed := 'N';
572
573 END IF;
574
575 already_exists := hr_template_existence.ele_exists(
576 p_ele_name => p_ele_name,
577 p_bg_id => p_bg_id,
578 p_eff_date => p_ele_eff_start_date);
579
580 if already_exists = 0 then
581
582 ret := pay_db_pay_setup.create_element(
583 p_element_name => p_ele_name,
584 p_description => p_ele_description,
585 p_classification_name => p_ele_class,
586 p_post_termination_rule => 'Final Close',
587 p_reporting_name => p_ele_reporting_name,
588 p_processing_type => p_ele_processing_type,
589 p_mult_entries_allowed => v_mult_entries_allowed,
590 p_indirect_only_flag => p_ind_only_flag,
591 p_formula_id => p_skip_formula_id,
592 p_processing_priority => p_ele_priority,
593 p_standard_link_flag => p_ele_standard_link,
594 p_business_group_name => p_bg_name,
595 p_effective_start_date => p_ele_eff_start_date,
596 p_effective_end_date => p_ele_eff_end_date,
597 p_legislation_code => NULL,
598 p_third_party_pay_only => p_third_party_pay);
599
600 else
601
602 if upper(p_ele_name) = upper(garn_ele_names(1)) then
603
604 -- Base element already exists, this MUST be called via upgrade mechanism.
605 -- Set upgrade mode flag for addition of input values, link input values, entry values,
606 -- and run result values.
607
608 l_upgrade_mode := 'Y';
609 hr_utility.trace('UPGRADE MODE = YES');
610
611 end if;
612
613 ret := already_exists;
614
615 /* Updating the processing priority of the elements */
616
617 update pay_element_types_f
618 set processing_priority = p_ele_priority
619 where element_type_id = ret
620 and business_group_id + 0 = p_bg_id;
621
622 end if;
623
624 RETURN ret;
625 --
626 END ins_garn_ele_type;
627 --
628
629 PROCEDURE do_defined_balances ( p_bal_id IN NUMBER,
630 p_bal_name IN VARCHAR2,
631 p_bg_name IN VARCHAR2,
632 p_no_payments IN BOOLEAN default FALSE,
633 p_save_run_value IN VARCHAR2 default 'N') IS
634
635 -- local vars
636
637 TYPE text_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
638
639 suffixes text_table;
640 x_dimension_name text_table;
641 dim_id number(9);
642 dim_name varchar2(80);
643 num_suffixes number;
644
645 already_exists number;
646 v_business_group_id number;
647
648 BEGIN
649
650 suffixes(1) := '_ASG_RUN';
651 x_dimension_name(1) := 'Assignment-Level Current Run';
652
653 suffixes(2) := '_ASG_PTD';
654 x_dimension_name(2) := 'Assignment Period to Date';
655
656 suffixes(3) := '_ASG_MONTH';
657 x_dimension_name(3) := 'Assignment Month';
658
659 suffixes(4) := '_ASG_QTD';
660 x_dimension_name(4) := 'Assignment Quarter to Date';
661
662 suffixes(5) := '_ASG_YTD';
663 x_dimension_name(5) := 'Assignment Year to Date';
664
665 suffixes(6) := '_ASG_GRE_RUN';
666 x_dimension_name(6) := 'Assignment within Government Reporting Entity Run';
667
668 suffixes(7) := '_ASG_GRE_PTD';
669 x_dimension_name(7) := 'Assignment within Government Reporting Entity Period to Date';
670
671 suffixes(8) := '_ASG_GRE_MONTH';
672 x_dimension_name(8) := 'Assignment within Government Reporting Entity Month';
673
674 suffixes(9) := '_ASG_GRE_QTD';
675 x_dimension_name(9) := 'Assignment within Government Reporting Entity Quarter to Date';
676
677 suffixes(10) := '_ASG_GRE_YTD';
678 x_dimension_name(10) := 'Assignment within Government Reporting Entity Year to Date';
679
680 suffixes(11) := '_PER_RUN';
681 x_dimension_name(11) := 'Person Run';
682
683 suffixes(12) := '_PER_MONTH';
684 x_dimension_name(12) := 'Person Month';
685
686 suffixes(13) := '_PER_QTD';
687 x_dimension_name(13) := 'Person Quarter to Date';
688
689 suffixes(14) := '_PER_YTD';
690 x_dimension_name(14) := 'Person Year to Date';
691
692 suffixes(15) := '_PER_GRE_RUN';
693 x_dimension_name(15) := 'Person within Government Reporting Entity Run';
694
695 suffixes(16) := '_PER_GRE_MONTH';
696 x_dimension_name(16) := 'Person within Government Reporting Entity Month';
697
698 suffixes(17) := '_PER_GRE_QTD';
699 x_dimension_name(17) := 'Person within Government Reporting Entity Quarter to Date';
700
701 suffixes(18) := '_PER_GRE_YTD';
702 x_dimension_name(18) := 'Person within Government Reporting Entity Year to Date';
703
704 suffixes(19) := '_PAYMENTS';
705 x_dimension_name(19) := 'Payments';
706
707 suffixes(20) := '_ASG_GRE_LTD';
708 x_dimension_name(20) := 'Assignment within Government Reporting Entity Lifetime to Date';
709
710 suffixes(21) := '_ASG_LTD';
711 x_dimension_name(21) := 'Assignment Lifetime to Date';
712
713 suffixes(22) := '_PER_GRE_LTD';
714 x_dimension_name(22) := 'Person within Government Reporting Entity Lifetime to Date';
715
716 suffixes(23) := '_PER_LTD';
717 x_dimension_name(23) := 'Person Lifetime to Date';
718
719 /* WWBug 133133 start */
720 /* Add defbals required for company level, summary reporting. */
721 suffixes(24) := '_GRE_RUN';
722 x_dimension_name(24) := 'Government Reporting Entity Run';
723
724 suffixes(25) := '_GRE_YTD';
725 x_dimension_name(25) := 'Government Reporting Entity Year to Date';
726
727 suffixes(26) := '_GRE_ITD';
728 x_dimension_name(26) := 'Government Reporting Entity Inception To Date';
729
730
731 /* WWBug 350540 start */
732 /* Need defbals on arrears bal for ASG_GRE_ITD and GRE_ITD. */
733 suffixes(27) := '_ASG_GRE_ITD';
734 x_dimension_name(27) := 'Assignment within Government Reporting Entity Inception To Date';
735
736 /* Changed GRE_ITD to ASG_ITD as GRE_ITD is already there in
737 suffixes(26) and ASG_ITD is missing. Bug 820068 */
738
739 suffixes(28) := '_ASG_ITD';
740 x_dimension_name(28) := 'Assignment Inception To Date';
741
742 suffixes(29) := '_ENTRY_ITD';
743 x_dimension_name(29) := 'US Element Entry Inception to Date';
744
745 suffixes(30) := '_ENTRY_MONTH';
746 x_dimension_name(30) := 'US Element Entry Month';
747
748 suffixes(31) := '_ENTRY_PTD';
749 x_dimension_name(31) := 'US Element Entry Period to Date';
750
751 suffixes(32) := '_ASG_PAYMENTS';
752 x_dimension_name(32) := 'Assignment Payments';
753
754 num_suffixes := 32;
755
756 /* WWBug 133133, 350540 finish */
757
758 hr_utility.trace('In do_defined_balances');
759 for i in 1..num_suffixes loop
760
761 select dimension_name, balance_dimension_id
762 into dim_name, dim_id
763 from pay_balance_dimensions
764 where dimension_name = x_dimension_name(i)
765 and legislation_code = g_template_leg_code;
766 -- and business_group_id IS NULL;
767
768 /* added line to include business_group_id in the where clause of the select
769 statement below. So that it checkes the existence of data for a the given
770 business_group_id Bug No: 502307.
771 */
772 hr_utility.trace('Defined Balance for '||p_bal_name||suffixes(i));
773 SELECT count(0)
774 INTO already_exists
775 FROM pay_defined_balances db
776 WHERE db.balance_type_id = p_bal_id
777 AND db.balance_dimension_id = dim_id
778 AND db.business_group_id + 0 = p_bg_id;
779
780 if (already_exists = 0) then
781
782 IF p_no_payments = TRUE and suffixes(i) = '_PAYMENTS' THEN
783 NULL;
784 ELSE
785 IF p_save_run_value = 'Y' THEN
786 IF suffixes(i) = '_ASG_GRE_RUN' THEN
787 pay_db_pay_setup.create_defined_balance(
788 p_balance_name => p_bal_name,
789 p_balance_dimension => dim_name,
790 p_business_group_name => p_bg_name,
791 p_legislation_code => NULL,
792 p_save_run_bal => p_save_run_value);
793 ELSE
794 pay_db_pay_setup.create_defined_balance(
795 p_balance_name => p_bal_name,
796 p_balance_dimension => dim_name,
797 p_business_group_name => p_bg_name,
798 p_legislation_code => NULL);
799 END IF;
800
801 ELSE
802 pay_db_pay_setup.create_defined_balance(
803 p_balance_name => p_bal_name,
804 p_balance_dimension => dim_name,
805 p_business_group_name => p_bg_name,
806 p_legislation_code => NULL);
807
808 END IF;
809 END IF;
810
811 end if;
812
813 end loop;
814 hr_utility.trace('Out of do_defined_balances');
815 END do_defined_balances;
816 --
817 ------------------------- ins_formula -----------------------
818 --
819 FUNCTION ins_formula ( p_ff_ele_name in varchar2,
820 p_ff_bg_id in number)
821 RETURN varchar2 IS
822 -- local vars
823 v_formula_id number;
824 --
825 v_skeleton_formula_text VARCHAR2(32000);
826 v_skeleton_formula_type_id NUMBER(9);
827 v_ele_formula_text VARCHAR2(32000);
828 v_ele_formula_name VARCHAR2(80); -- Return variable
829 v_ele_formula_id NUMBER(9);
830 v_ele_name VARCHAR2(80);
831
832 v_ff_desc varchar2(80);
833
834 v_orig_ele_formula_id NUMBER(9);
835 v_orig_ele_formula_name VARCHAR2(80);
836 v_orig_ele_formula_text varchar2(32000);
837 v_new_ele_formula_id NUMBER(9);
838 v_new_ele_formula_name VARCHAR2(80);
839 v_new_ele_formula_text VARCHAR2(32000);
840
841 already_exists number;
842 l_count_already number;
843
844 BEGIN
845
846 -- Enhance this function to preserve original formula text...
847
848 SELECT FF.formula_text,
849 FF.formula_type_id
850 INTO v_skeleton_formula_text,
851 v_skeleton_formula_type_id
852 FROM ff_formulas_f FF
853 WHERE FF.formula_name = 'BALANCE_SETUP_FORMULA'
854 AND FF.business_group_id IS NULL
855 AND FF.legislation_code = 'US'
856 AND g_eff_start_date >= FF.effective_start_date
857 AND g_eff_start_date <= FF.effective_end_date;
858
859 -- Replace element name placeholders with current element name:
860
861 v_ele_name := REPLACE(LTRIM(RTRIM(UPPER(p_ff_ele_name))),' ','_');
862
863 v_new_ele_formula_text := REPLACE( v_skeleton_formula_text,
864 '<ELE_NAME>',
865 v_ele_name);
866
867
868 v_new_ele_formula_name := UPPER(v_ele_name || '_BALANCE_SETUP_FORMULA');
869 v_new_ele_formula_name := SUBSTR(v_new_ele_formula_name, 1, 80);
870
871 v_ff_desc := 'Formula used to access element specific balances.';
872
873 -- Call function to check existence of formula to get id.
874 -- Get original formula id, name, and text for this element currently,
875 -- ie. before putting in new ff text.
876
877 hr_utility.set_location('hr_us_garn_gen.ins_formula',20);
878
879 already_exists := hr_template_existence.ele_ff_exists(
880 p_ele_name => p_ff_ele_name,
881 p_bg_id => p_ff_bg_id,
882 p_ff_name => v_orig_ele_formula_name,
883 p_ff_text => v_orig_ele_formula_text,
884 p_eff_date => g_eff_start_date);
885
886 if already_exists = 0 then
887
888 -- Insert the new formula text into current business group since
889 -- there is no formula for this element currently.
890 --
891 -- Get new id for formula
892
893 SELECT ff_formulas_s.nextval
894 INTO v_new_ele_formula_id
895 FROM sys.dual;
896
897 --
898 -- Insert the new formula into current business group:
899 --
900 hr_utility.set_location('hr_us_garn_gen.ins_formula',40);
901 INSERT INTO ff_formulas_f (
902 FORMULA_ID,
903 EFFECTIVE_START_DATE,
904 EFFECTIVE_END_DATE,
905 BUSINESS_GROUP_ID,
906 LEGISLATION_CODE,
907 FORMULA_TYPE_ID,
908 FORMULA_NAME,
909 DESCRIPTION,
910 FORMULA_TEXT,
911 STICKY_FLAG,
912 LAST_UPDATE_DATE,
913 LAST_UPDATED_BY,
914 LAST_UPDATE_LOGIN,
915 CREATED_BY,
916 CREATION_DATE)
917 values (
918 v_new_ele_formula_id,
919 g_eff_start_date,
920 g_eff_end_date,
921 p_ff_bg_id,
922 NULL,
923 v_skeleton_formula_type_id,
924 v_new_ele_formula_name,
925 v_ff_desc,
926 v_new_ele_formula_text,
927 'N',
928 NULL,
929 NULL,
930 NULL,
931 -1,
932 g_eff_start_date);
933
934 else
935
936 -- Element already has formula attached via stat proc rule...
937 -- original formula name and text have been populated as outputs
938 -- from check for existence.
939
940 hr_utility.set_location('hr_us_garn_gen.ins_formula',50);
941
942 v_new_ele_formula_id := already_exists;
943 v_new_ele_formula_name := v_orig_ele_formula_name;
944
945 -- Update existing formula with new ff name and text.
946
947 -- dbms_output.put_line('existing FF '||v_new_ele_formula_id||' being updated');
948 -- dbms_output.put_line(v_new_ele_formula_name);
949
950 hr_utility.set_location('hr_us_garn_gen.ins_formula',70);
951
952 UPDATE ff_formulas_f
953 SET formula_text = v_new_ele_formula_text
954 WHERE formula_id = v_new_ele_formula_id
955 AND business_group_id+0 = p_ff_bg_id
956 AND g_eff_start_date BETWEEN effective_start_date
957 AND effective_end_date;
958
959 --
960 -- Insert the original formula into current business group to preserve customer mods.
961 --
962 hr_utility.trace('FF '||v_orig_ele_formula_name||' already exists for ele '||p_ff_ele_name);
963
964 select count(0)
965 into l_count_already
966 from ff_formulas_f fff, ff_formula_types ffft
967 where (upper(formula_name) like upper('OLD%_'||v_orig_ele_formula_name) or
968 upper(formula_name) like upper(v_orig_ele_formula_name) or
969 upper(formula_name) like upper('%'||v_orig_ele_formula_name)||'_EXP' or
970 upper(formula_name) like upper('%'||v_orig_ele_formula_name)||'_OLD%' )
971 and business_group_id+0 = p_ff_bg_id
972 and fff.legislation_code = 'US'
973 and ffft.formula_type_name = 'Oracle Payroll'
974 and ffft.formula_type_id = fff.formula_type_id;
975
976 hr_utility.set_location('hr_us_garn_gen.ins_formula',35);
977
978 hr_utility.trace('Preserving text for formula '||v_orig_ele_formula_name ||
979 ' for BG ' || p_ff_bg_id);
980
981 v_orig_ele_formula_name := 'OLD'||l_count_already||'_'||v_orig_ele_formula_name;
982 v_orig_ele_formula_name := substr(v_orig_ele_formula_name,1,80);
983
984 hr_utility.trace('Text saved in formula named '||v_orig_ele_formula_name);
985
986 hr_utility.set_location('hr_us_garn_gen.ins_formula',30);
987
988 SELECT ff_formulas_s.nextval
989 INTO v_orig_ele_formula_id
990 FROM sys.dual;
991
992 hr_utility.set_location('hr_us_garn_gen.ins_formula',40);
993
994 INSERT INTO ff_formulas_f ( FORMULA_ID,
995 EFFECTIVE_START_DATE,
996 EFFECTIVE_END_DATE,
997 BUSINESS_GROUP_ID,
998 LEGISLATION_CODE,
999 FORMULA_TYPE_ID,
1000 FORMULA_NAME,
1001 DESCRIPTION,
1002 FORMULA_TEXT,
1003 STICKY_FLAG,
1004 LAST_UPDATE_DATE,
1005 LAST_UPDATED_BY,
1006 LAST_UPDATE_LOGIN,
1007 CREATED_BY,
1008 CREATION_DATE,
1009 COMPILE_FLAG)
1010 values (
1011 v_orig_ele_formula_id,
1012 g_eff_start_date,
1013 g_eff_end_date,
1014 p_ff_bg_id,
1015 NULL,
1016 v_skeleton_formula_type_id,
1017 v_orig_ele_formula_name,
1018 v_ff_desc,
1019 v_orig_ele_formula_text,
1020 'N',
1021 NULL,
1022 NULL,
1023 NULL,
1024 -1,
1025 g_eff_start_date,
1026 'N');
1027
1028 end if;
1029
1030 RETURN v_new_ele_formula_name;
1031
1032 END ins_formula;
1033
1034 --
1035 -- Create Garnishment MAIN
1036 --
1037
1038 BEGIN
1039 -- hr_utility.trace_on(null,'ORACLE');
1040 begin
1041
1042 select name
1043 into v_bg_name
1044 from per_business_groups
1045 where business_group_id + 0 = p_bg_id;
1046 exception WHEN NO_DATA_FOUND THEN
1047 v_bg_name := NULL;
1048 hr_utility.set_location('Could not find ''BG NAME'' ', 999);
1049 end;
1050
1051 g_eff_start_date := nvl(p_ele_eff_start_date, sysdate);
1052
1053 begin
1054 select classification_id,
1055 default_priority
1056 into g_ele_class_id,
1057 g_ele_priority
1058 from pay_element_classifications
1059 where UPPER(classification_name) = g_ele_classification
1060 and legislation_code = 'US';
1061 exception WHEN NO_DATA_FOUND THEN
1062 hr_utility.set_location('Could not find ''CLASSIFICATION'' ', 999);
1063 end;
1064
1065 -- Need skip rule formula
1066 begin
1067 SELECT FF.formula_id
1068 INTO g_skip_formula_id
1069 FROM ff_formulas_f FF
1070 WHERE FF.formula_name = 'WAT_SKIP'
1071 AND FF.business_group_id IS NULL
1072 AND legislation_code = 'US'
1073 AND p_ele_eff_start_date >= FF.effective_start_date
1074 AND p_ele_eff_start_date <= FF.effective_end_date;
1075 exception WHEN NO_DATA_FOUND THEN
1076 hr_utility.set_location('Could not find ''SKIP RULE'' ', 999);
1077 end;
1078 -- Get voluntary deductions high level balance for future ref.
1079
1080 begin
1081
1082 SELECT balance_type_id
1083 INTO g_voldedns_balance_id
1084 FROM pay_balance_types
1085 WHERE balance_name = 'Voluntary Deductions'
1086 AND business_group_id IS NULL
1087 AND legislation_code = 'US';
1088
1089 exception WHEN NO_DATA_FOUND THEN
1090
1091 g_voldedns_balance_id := NULL;
1092
1093 hr_utility.set_location('Could not find ''Voluntary Deductions'' balance', 999);
1094
1095 end;
1096
1097 -- 374743 : Spousal support and alimony are treated same as child support.
1098 IF p_category NOT IN ('CS', 'SS', 'AY') THEN
1099
1100 -- Get wage attach "total dedns" balance for future ref.
1101
1102 begin
1103
1104 SELECT balance_type_id
1105 INTO g_total_dedns_balance_id
1106 FROM pay_balance_types
1107 WHERE balance_name = 'Garn Total Dedns'
1108 AND business_group_id IS NULL
1109 AND legislation_code = 'US';
1110
1111 exception WHEN NO_DATA_FOUND THEN
1112
1113 g_total_dedns_balance_id := NULL;
1114
1115 hr_utility.set_location('Could not find ''Total Dedns'' balance', 999);
1116
1117 end;
1118
1119 -- Get garnishment "total fees" balance for future ref.
1120
1121 begin
1122
1123 SELECT balance_type_id
1124 INTO g_total_fees_balance_id
1125 FROM pay_balance_types
1126 WHERE balance_name = 'Garn Total Fees'
1127 AND business_group_id IS NULL
1128 AND legislation_code = 'US';
1129
1130 exception WHEN NO_DATA_FOUND THEN
1131
1132 g_total_fees_balance_id := NULL;
1133
1134 hr_utility.set_location('Could not find ''Total Fees'' balance', 999);
1135
1136 end;
1137
1138 -- Get Other Garn Not Taken Amount balance for future ref.
1139
1140 begin
1141
1142 SELECT balance_type_id
1143 INTO g_other_not_taken_bal_id
1144 FROM pay_balance_types
1145 WHERE balance_name = 'Other Garn Not Taken Amount'
1146 AND business_group_id IS NULL
1147 AND legislation_code = 'US';
1148
1149 hr_utility.trace('g_other_not_taken_bal_id = '||to_char(g_other_not_taken_bal_id));
1150
1151 exception WHEN NO_DATA_FOUND THEN
1152
1153 g_other_not_taken_bal_id := NULL;
1154
1155 hr_utility.set_location('Could not find ''Other Wage Attach Not Taken Amount'' balance'
1156 , 999);
1157
1158 end;
1159
1160
1161 /* 365745 fixes begin */
1162 if p_category = 'TL' then
1163
1164 -- Get wage attach "tax levies" balance for future ref.
1165
1166 begin
1167
1168 SELECT balance_type_id
1169 INTO g_tax_levies_balance_id
1170 FROM pay_balance_types
1171 WHERE balance_name = 'Tax Levies'
1172 AND business_group_id IS NULL
1173 AND legislation_code = 'US';
1174
1175 exception WHEN NO_DATA_FOUND THEN
1176
1177 g_tax_levies_balance_id := NULL;
1178
1179 hr_utility.set_location('Could not find ''Tax Levies'' balance', 999);
1180
1181 end;
1182
1183 end if;
1184 /* 365745 fixes end */
1185
1186 ELSE
1187
1188 -- Get child support "count" balance for future ref.
1189 -- Child Support, Spousal Support and Alimony are all
1190 -- treated as "support" (374743).
1191
1192 begin
1193
1194 SELECT balance_type_id
1195 INTO g_childsupp_count_balance_id
1196 FROM pay_balance_types
1197 WHERE balance_name = 'Child Supp Count'
1198 AND business_group_id IS NULL
1199 AND legislation_code = 'US';
1200
1201 exception WHEN NO_DATA_FOUND THEN
1202
1203 g_childsupp_count_balance_id := NULL;
1204
1205 hr_utility.set_location('Could not find ''Count'' balance', 999);
1206
1207 end;
1208
1209 -- Get child support "total dedns" balance for future ref.
1210
1211 begin
1212
1213 SELECT balance_type_id
1214 INTO g_total_dedns_balance_id
1215 FROM pay_balance_types
1216 WHERE balance_name = 'Child Supp Total Dedns'
1217 AND business_group_id IS NULL
1218 AND legislation_code = 'US';
1219
1220 exception WHEN NO_DATA_FOUND THEN
1221
1222 g_total_dedns_balance_id := NULL;
1223
1224 hr_utility.set_location('Could not find ''Total Dedns'' balance', 999);
1225
1226 end;
1227
1228 -- Get child support "total amount" balance for future ref.
1229
1230 begin
1231
1232 SELECT balance_type_id
1233 INTO g_total_amount_balance_id
1234 FROM pay_balance_types
1235 WHERE balance_name = 'Child Supp Total Amount'
1236 AND business_group_id IS NULL
1237 AND legislation_code = 'US';
1238
1239 exception WHEN NO_DATA_FOUND THEN
1240
1241 g_total_amount_balance_id := NULL;
1242
1243 hr_utility.set_location('Could not find ''Total Amount'' balance', 999);
1244
1245 end;
1246
1247 -- Get support not taken amount balance for future ref.
1248
1249 begin
1250
1251 SELECT balance_type_id
1252 INTO g_supp_not_taken_bal_id
1253 FROM pay_balance_types
1254 WHERE balance_name = 'Support Not Taken Amount'
1255 AND business_group_id IS NULL
1256 AND legislation_code = 'US';
1257
1258 exception WHEN NO_DATA_FOUND THEN
1259
1260 g_supp_not_taken_bal_id := NULL;
1261
1262 hr_utility.set_location('Could not find ''Support Not Taken Amount'' balance', 999);
1263
1264 end;
1265
1266
1267 -- Get child support "total fees" balance for future ref.
1268
1269 begin
1270
1271 SELECT balance_type_id
1272 INTO g_total_fees_balance_id
1273 FROM pay_balance_types
1274 WHERE balance_name = 'Child Supp Total Fees'
1275 AND business_group_id IS NULL
1276 AND legislation_code = 'US';
1277
1278 exception WHEN NO_DATA_FOUND THEN
1279
1280 g_total_fees_balance_id := NULL;
1281
1282 hr_utility.set_location('Could not find ''Total Fees'' balance', 999);
1283
1284 end;
1285
1286 /* 378699 fixes begin */
1287 -- Get wage attach "total adjusted support" balance for future ref.
1288
1289 begin
1290
1291 SELECT balance_type_id
1292 INTO g_wh_support_balance_id
1293 FROM pay_balance_types
1294 WHERE balance_name = 'Total Withheld Support'
1295 AND business_group_id IS NULL
1296 AND legislation_code = 'US';
1297
1298 exception WHEN NO_DATA_FOUND THEN
1299
1300 g_wh_support_balance_id := NULL;
1301
1302 hr_utility.set_location('Could not find ''Total Withheld Support'' balance', 999);
1303
1304 end;
1305
1306 -- Get wage attach "total adjusted support" balance for future ref.
1307
1308 begin
1309
1310 SELECT balance_type_id
1311 INTO g_wh_fee_balance_id
1312 FROM pay_balance_types
1313 WHERE balance_name = 'Total Withheld Fee'
1314 AND business_group_id IS NULL
1315 AND legislation_code = 'US';
1316
1317 exception WHEN NO_DATA_FOUND THEN
1318
1319 g_wh_fee_balance_id := NULL;
1320
1321 hr_utility.set_location('Could not find ''Total Withheld Fee'' balance', 999);
1322
1323 end;
1324 /* 378699 fixes end */
1325
1326 END IF;
1327
1328 begin
1329
1330 SELECT balance_type_id
1331 INTO g_net_balance_id
1332 FROM pay_balance_types
1333 WHERE balance_name = 'Net'
1334 AND business_group_id IS NULL
1335 AND legislation_code = 'US';
1336
1337 SELECT balance_type_id
1338 INTO g_payments_balance_id
1339 FROM pay_balance_types
1340 WHERE balance_name = 'Payments'
1341 AND business_group_id IS NULL
1342 AND legislation_code = 'US';
1343
1344 exception WHEN NO_DATA_FOUND THEN
1345
1346 g_net_balance_id := NULL;
1347 g_payments_balance_id := null;
1348
1349 hr_utility.set_location('MAJOR ERROR: Net or Payments STU balance not found', 999);
1350
1351 end;
1352
1353 -- Setup all configuration param settings based on category:
1354
1355
1356 -- Element and payroll formula parameter settings:
1357 garn_ele_names(1) := p_garn_name;
1358 garn_ele_repnames(1) := p_garn_reporting_name;
1359 garn_ele_proc_type(1) := 'R';
1360 garn_ele_desc(1) := p_garn_description;
1361
1362 IF p_category IN ('CS','AY','SS','TL') THEN
1363 garn_ele_priority(1) := g_ele_priority;
1364 ELSIF p_category = 'BO' THEN
1365 garn_ele_priority(1) := g_ele_priority + 7;
1366 ELSIF p_category IN ('CD','G') THEN
1367 garn_ele_priority(1) := g_ele_priority + 11;
1368 ELSIF p_category = 'EL' THEN
1369 garn_ele_priority(1) := g_ele_priority + 11; /*bug:1348004*/
1370 ELSE
1371 garn_ele_priority(1) := g_ele_priority + 22;
1372 END IF;
1373 garn_indirect_only(1) := 'N';
1374 garn_mix_category(1) := 'D';
1375 garn_pay_formula(1) := ins_formula( p_ff_ele_name => garn_ele_names(1),
1376 p_ff_bg_id => p_bg_id);
1377 garn_third_party_pay(1) := 'Y';
1378
1379 garn_ele_names(2) := p_garn_name||' Calculator';
1380 garn_ele_repnames(2) := p_garn_reporting_name||' Calc';
1381 garn_ele_proc_type(2) := 'N';
1382 garn_ele_desc(2) := 'Generated calculation element for '||p_garn_name;
1383 IF p_category IN ('CS', 'SS', 'AY') THEN
1384 garn_ele_priority(2) := g_ele_priority + 1;
1385 ELSIF p_category = 'TL' THEN
1386 garn_ele_priority(2) := g_ele_priority + 3;
1387 ELSIF p_category = 'BO' THEN
1388 garn_ele_priority(2) := g_ele_priority + 8;
1389 ELSIF p_category IN ('CD','G') THEN
1390 garn_ele_priority(2) := g_ele_priority + 13;
1391 ELSIF p_category = 'EL' THEN
1392 garn_ele_priority(2) := g_ele_priority + 13;
1393 ELSE
1394 garn_ele_priority(2) := g_ele_priority + 23;
1395 END IF;
1396 garn_indirect_only(2) := 'Y';
1397 garn_mix_category(2) := NULL;
1398 garn_third_party_pay(2) := 'N';
1399
1400 -- 374743 : Treat spousal support and alimony same as child support.
1401 IF p_category IN ('CS', 'SS', 'AY') THEN
1402
1403 garn_pay_formula(2) := 'CHILD_SUPP_CALCULATION_FORMULA';
1404
1405 ELSIF p_category = 'TL' THEN
1406
1407 garn_pay_formula(2) := 'FED_LEVY_CALCULATION_FORMULA';
1408
1409 ELSE
1410
1411 garn_pay_formula(2) := 'GARN_CALCULATION_FORMULA';
1412
1413 END IF;
1414
1415 garn_ele_names(3) := p_garn_name||' Verifier';
1416 garn_ele_repnames(3) := p_garn_reporting_name||' Verify';
1417 garn_ele_proc_type(3) := 'N';
1418 garn_ele_desc(3) := 'Generated verification element for '||p_garn_name;
1419 IF p_category IN ('CS', 'SS', 'AY') THEN
1420 garn_ele_priority(3) := g_ele_priority + 3;
1421 ELSIF p_category = 'TL' THEN
1422 garn_ele_priority(3) := g_ele_priority + 5;
1423 ELSIF p_category = 'BO' THEN
1424 garn_ele_priority(3) := g_ele_priority + 10;
1425 ELSIF p_category IN ('CD','G') THEN
1426 garn_ele_priority(3) := g_ele_priority + 15;
1427 ELSIF p_category = 'EL' THEN
1428 garn_ele_priority(3) := g_ele_priority + 15;
1429 ELSE
1430 garn_ele_priority(3) := g_ele_priority + 25;
1431 END IF;
1432 garn_indirect_only(3) := 'Y';
1433 garn_mix_category(3) := NULL;
1434 garn_third_party_pay(3) := 'N';
1435
1436 -- 374743 : Treat spousal support and alimony same as child support.
1437 IF p_category IN ('CS', 'SS', 'AY') THEN
1438
1439 garn_pay_formula(3) := 'CHILD_SUPP_VERIFICATION_FORMULA';
1440
1441 ELSE
1442
1443 garn_pay_formula(3) := NULL;
1444
1445 END IF;
1446
1447 garn_ele_names(4) := p_garn_name||' Special Inputs';
1448 garn_ele_repnames(4) := p_garn_reporting_name||' SI';
1449 garn_ele_proc_type(4) := 'N';
1450 garn_ele_desc(4) := 'Generated adjustments element for '||p_garn_name;
1451 IF p_category IN ('AY','CS','SS','TL') THEN
1452 garn_ele_priority(4) := g_ele_priority - 1;
1453 ELSIF p_category = 'BO' THEN
1454 garn_ele_priority(4) := g_ele_priority + 6;
1455 ELSIF p_category IN ('CD','G') THEN
1456 garn_ele_priority(4) := g_ele_priority + 9;
1457 ELSIF p_category = 'EL' THEN
1458 garn_ele_priority(4) := g_ele_priority + 9;
1459 ELSE
1460 garn_ele_priority(4) := g_ele_priority + 21;
1461 END IF;
1462 garn_indirect_only(4) := 'N';
1463 garn_mix_category(4) := NULL;
1464 garn_pay_formula(4) := NULL;
1465 garn_third_party_pay(4) := 'N';
1466
1467 garn_ele_names(5) := p_garn_name||' Special Features';
1468 garn_ele_repnames(5) := p_garn_reporting_name||' SF';
1469 garn_ele_proc_type(5) := 'N';
1470 garn_ele_desc(5) := 'Generated results element for '||p_garn_name;
1471 IF p_category IN ('AY','CS','SS','TL') THEN
1472 garn_ele_priority(5) := g_ele_priority;
1473 ELSIF p_category = 'BO' THEN
1474 garn_ele_priority(5) := g_ele_priority + 7;
1475 ELSIF p_category IN ('CD','G') THEN
1476 garn_ele_priority(5) := g_ele_priority + 11;
1477 ELSIF p_category = 'EL' THEN
1478 garn_ele_priority(5) := g_ele_priority + 11;
1479 ELSE
1480 garn_ele_priority(5) := g_ele_priority + 22;
1481 END IF;
1482 garn_indirect_only(5) := 'Y';
1483 garn_mix_category(5) := NULL;
1484 garn_pay_formula(5) := NULL;
1485 garn_third_party_pay(5) := 'N';
1486
1487 garn_ele_names(6) := p_garn_name||' Fees';
1488 garn_ele_repnames(6) := p_garn_reporting_name||' Fees';
1489 garn_ele_proc_type(6) := 'N';
1490 garn_ele_desc(6) := 'Generated Fee results element for '||p_garn_name;
1491 IF p_category IN ('CS', 'SS', 'AY') THEN
1492 garn_ele_priority(6) := g_ele_priority + 2;
1493 ELSIF p_category = 'TL' THEN
1494 garn_ele_priority(6) := g_ele_priority + 4;
1495 ELSIF p_category = 'BO' THEN
1496 garn_ele_priority(6) := g_ele_priority + 9;
1497 ELSIF p_category IN ('CD','G') THEN
1498 garn_ele_priority(6) := g_ele_priority + 14;
1499 ELSIF p_category = 'EL' THEN
1500 garn_ele_priority(6) := g_ele_priority + 14;
1501 ELSE
1502 garn_ele_priority(6) := g_ele_priority + 24;
1503 END IF;
1504 garn_indirect_only(6) := 'Y';
1505 garn_mix_category(6) := NULL;
1506 garn_pay_formula(6) := NULL;
1507 garn_third_party_pay(6) := 'N';
1508
1509 garn_ele_names(7) := p_garn_name||' Priority';
1510 garn_ele_repnames(7) := p_garn_reporting_name||' VP';
1511 garn_ele_proc_type(7) := 'N';
1512 garn_ele_desc(7) := 'Generated verifier priority element for '||p_garn_name;
1513 IF p_category IN ('CS', 'SS', 'AY') THEN
1514 garn_ele_priority(7) := g_ele_priority + 1;
1515 ELSIF p_category = 'TL' THEN
1516 garn_ele_priority(7) := g_ele_priority + 3;
1517 ELSIF p_category = 'BO' THEN
1518 garn_ele_priority(7) := g_ele_priority + 8;
1519 ELSIF p_category IN ('CD','G') THEN
1520 garn_ele_priority(7) := g_ele_priority + 13;
1521 ELSIF p_category = 'EL' THEN
1522 garn_ele_priority(7) := g_ele_priority + 13;
1523 ELSE
1524 garn_ele_priority(7) := g_ele_priority + 23;
1525 END IF;
1526 garn_indirect_only(7) := 'Y';
1527 garn_mix_category(7) := NULL;
1528 garn_third_party_pay(7) := 'N';
1529 IF p_category IN ('CS', 'SS', 'AY') THEN
1530 garn_pay_formula(7) := 'WAGE_ATTACH_PRIORITY_FORMULA';
1531 ELSE
1532 garn_pay_formula(7) := NULL;
1533 END IF;
1534
1535 l_num_eles := 7;
1536
1537 -- Garn input value param settings:
1538
1539 -- Based on Category (Currently classifying it as Support Vs rest of them)
1540 -- The following 5 iv_names are common to all categories :
1541
1542 garn_base_iv_names(1) := 'Amount';
1543 garn_base_iv_seq(1) := 1;
1544 garn_base_iv_uom(1) := 'M';
1545 garn_base_iv_mand(1) := 'N';
1546 garn_base_iv_dbi(1) := 'N';
1547 garn_base_iv_lkp(1) := NULL;
1548 garn_base_iv_dflt(1) := NULL;
1549
1550 garn_base_iv_names(2) := 'Percentage';
1551 garn_base_iv_seq(2) := 2;
1552 garn_base_iv_uom(2) := 'N';
1553 garn_base_iv_mand(2) := 'N';
1554 garn_base_iv_dbi(2) := 'N';
1555 garn_base_iv_lkp(2) := NULL;
1556 garn_base_iv_dflt(2) := NULL;
1557
1558 garn_base_iv_names(3) := 'Jurisdiction';
1559 garn_base_iv_seq(3) := 3;
1560 garn_base_iv_uom(3) := 'C';
1561 garn_base_iv_mand(3) := 'N';
1562 garn_base_iv_dbi(3) := 'N';
1563 garn_base_iv_lkp(3) := NULL;
1564 garn_base_iv_dflt(3) := NULL;
1565
1566 garn_base_iv_names(4) := 'Attachment Number';
1567 garn_base_iv_seq(4) := 4;
1568 garn_base_iv_uom(4) := 'C';
1569 garn_base_iv_mand(4) := 'N';
1570 garn_base_iv_dbi(4) := 'N';
1571 garn_base_iv_lkp(4) := NULL;
1572 garn_base_iv_dflt(4) := NULL;
1573
1574 garn_base_iv_names(5) := 'Total Owed';
1575 garn_base_iv_seq(5) := 5;
1576 garn_base_iv_uom(5) := 'M';
1577 garn_base_iv_mand(5) := 'N';
1578 garn_base_iv_dbi(5) := 'N';
1579 garn_base_iv_lkp(5) := NULL;
1580 garn_base_iv_dflt(5) := NULL;
1581
1582 garn_base_iv_names(6) := 'Date Served';
1583 garn_base_iv_seq(6) := 6;
1584 garn_base_iv_uom(6) := 'D';
1585 garn_base_iv_mand(6) := 'N';
1586 garn_base_iv_dbi(6) := 'N';
1587 garn_base_iv_lkp(6) := NULL;
1588 garn_base_iv_dflt(6) := NULL;
1589
1590 garn_base_iv_names(7) := 'Max Per Period';
1591 garn_base_iv_seq(7) := 7;
1592 garn_base_iv_uom(7) := 'M';
1593 garn_base_iv_mand(7) := 'N';
1594 garn_base_iv_dbi(7) := 'N';
1595 garn_base_iv_lkp(7) := NULL;
1596 garn_base_iv_dflt(7) := NULL;
1597
1598 garn_base_iv_names(8) := 'Monthly Cap';
1599 garn_base_iv_seq(8) := 8;
1600 garn_base_iv_uom(8) := 'M';
1601 garn_base_iv_mand(8) := 'N';
1602 garn_base_iv_dbi(8) := 'N';
1603 garn_base_iv_lkp(8) := NULL;
1604 garn_base_iv_dflt(8) := NULL;
1605
1606 IF p_category IN ('CS', 'SS', 'AY') THEN -- All Support Categories
1607
1608 garn_base_iv_names(9) := 'Arrears Dedn Amount';
1609 garn_base_iv_seq(9) := 9;
1610 garn_base_iv_uom(9) := 'M';
1611 garn_base_iv_mand(9) := 'N';
1612 garn_base_iv_dbi(9) := 'N';
1613 garn_base_iv_lkp(9) := NULL;
1614 garn_base_iv_dflt(9) := NULL;
1615
1616 garn_base_iv_names(10) := 'Date In Arrears';
1617 garn_base_iv_seq(10) := 10;
1618 garn_base_iv_uom(10) := 'D';
1619 garn_base_iv_mand(10) := 'N';
1620 garn_base_iv_dbi(10) := 'N';
1621 garn_base_iv_lkp(10) := NULL;
1622 garn_base_iv_dflt(10) := NULL;
1623
1624 garn_base_iv_names(11) := 'Num Dependents';
1625 garn_base_iv_seq(11) := 11;
1626 garn_base_iv_uom(11) := 'I';
1627 garn_base_iv_mand(11) := 'N';
1628 garn_base_iv_dbi(11) := 'N';
1629 garn_base_iv_lkp(11) := NULL;
1630 garn_base_iv_dflt(11) := NULL;
1631
1632 garn_base_iv_names(12) := 'Arrears Bal Amount';
1633 garn_base_iv_seq(12) := 12;
1634 garn_base_iv_uom(12) := 'M';
1635 garn_base_iv_mand(12) := 'N';
1636 garn_base_iv_dbi(12) := 'N';
1637 garn_base_iv_lkp(12) := NULL;
1638 garn_base_iv_dflt(12) := NULL;
1639
1640 garn_base_iv_names(13) := 'Support Other Family';
1641 garn_base_iv_seq(13) := 13;
1642 garn_base_iv_uom(13) := 'C';
1643 garn_base_iv_mand(13) := 'N';
1644 garn_base_iv_dbi(13) := 'N';
1645 garn_base_iv_lkp(13) := 'YES_NO';
1646 garn_base_iv_dflt(13) := NULL;
1647
1648 garn_base_iv_names(14) := 'Allowances';
1649 garn_base_iv_seq(14) := 14;
1650 garn_base_iv_uom(14) := 'I';
1651 garn_base_iv_mand(14) := 'N';
1652 garn_base_iv_dbi(14) := 'N';
1653 garn_base_iv_lkp(14) := NULL;
1654 garn_base_iv_dflt(14) := NULL;
1655
1656 garn_base_iv_names(15) := 'Dedns at Time of Writ';
1657 garn_base_iv_seq(15) := 15;
1658 garn_base_iv_uom(15) := 'M';
1659 garn_base_iv_mand(15) := 'N';
1660 garn_base_iv_dbi(15) := 'N';
1661 garn_base_iv_lkp(15) := NULL;
1662 garn_base_iv_dflt(15) := NULL;
1663
1664
1665 garn_base_iv_names(16) := 'Clear Arrears';
1666 garn_base_iv_seq(16) := 16;
1667 garn_base_iv_uom(16) := 'C';
1668 garn_base_iv_mand(16) := 'N';
1669 garn_base_iv_dbi(16) := 'N';
1670 garn_base_iv_lkp(16) := 'YES_NO';
1671 garn_base_iv_dflt(16) := 'N';
1672
1673 l_num_base_ivs := 16;
1674
1675 Else
1676
1677 garn_base_iv_names(9) := 'Dedns at Time of Writ';
1678 garn_base_iv_seq(9) := 9;
1679 garn_base_iv_uom(9) := 'M';
1680 garn_base_iv_mand(9) := 'N';
1681 garn_base_iv_dbi(9) := 'N';
1682 garn_base_iv_lkp(9) := NULL;
1683 garn_base_iv_dflt(9) := NULL;
1684
1685 garn_base_iv_names(10) := 'Filing Status';
1686 garn_base_iv_seq(10) := 10;
1687 garn_base_iv_uom(10) := 'C';
1688 garn_base_iv_mand(10) := 'N';
1689 garn_base_iv_dbi(10) := 'N';
1690 garn_base_iv_lkp(10) := 'US_FEDLEVY_FILING_STATUS';
1691 garn_base_iv_dflt(10) := NULL;
1692
1693 garn_base_iv_names(11) := 'Allowances';
1694 garn_base_iv_seq(11) := 11;
1695 garn_base_iv_uom(11) := 'I';
1696 garn_base_iv_mand(11) := 'N';
1697 garn_base_iv_dbi(11) := 'N';
1698 garn_base_iv_lkp(11) := NULL;
1699 garn_base_iv_dflt(11) := NULL;
1700
1701 garn_base_iv_names(12) := 'Num Dependents';
1702 garn_base_iv_seq(12) := 12;
1703 garn_base_iv_uom(12) := 'I';
1704 garn_base_iv_mand(12) := 'N';
1705 garn_base_iv_dbi(12) := 'N';
1706 garn_base_iv_lkp(12) := NULL;
1707 garn_base_iv_dflt(12) := NULL;
1708
1709 garn_base_iv_names(13) := 'Clear Arrears';
1710 garn_base_iv_seq(13) := 13;
1711 garn_base_iv_uom(13) := 'C';
1712 garn_base_iv_mand(13) := 'N';
1713 garn_base_iv_dbi(13) := 'N';
1714 garn_base_iv_lkp(13) := 'YES_NO';
1715 garn_base_iv_dflt(13) := 'N';
1716
1717 l_num_base_ivs := 13;
1718
1719 if p_category IN ('BO') THEN
1720
1721 garn_base_iv_names(14) := 'Exempt Amount';
1722 garn_base_iv_seq(14) := 14;
1723 garn_base_iv_uom(14) := 'N';
1724 garn_base_iv_mand(14) := 'N';
1725 garn_base_iv_dbi(14) := 'N';
1726 garn_base_iv_lkp(14) := NULL;
1727 garn_base_iv_dflt(14) := NULL;
1728
1729 l_num_base_ivs := 14;
1730
1731 end if;
1732
1733 -- Note : l_num_base_ivs to be later used to determine the No. or input values to be created
1734 -- for the specific element.
1735
1736 End if;
1737
1738
1739 garn_calc_iv_names(1) := 'Amount';
1740 garn_calc_iv_seq(1) := 1;
1741 garn_calc_iv_uom(1) := 'M';
1742 garn_calc_iv_mand(1) := 'N';
1743 garn_calc_iv_dbi(1) := 'N';
1744 garn_calc_iv_lkp(1) := NULL;
1745 garn_calc_iv_dflt(1) := NULL;
1746
1747 garn_calc_iv_names(2) := 'Jurisdiction';
1748 garn_calc_iv_seq(2) := 2;
1749 garn_calc_iv_uom(2) := 'C';
1750 garn_calc_iv_mand(2) := 'N';
1751 garn_calc_iv_dbi(2) := 'N';
1752 garn_calc_iv_lkp(2) := NULL;
1753 garn_calc_iv_dflt(2) := NULL;
1754
1755 garn_calc_iv_names(3) := 'Total Owed';
1756 garn_calc_iv_seq(3) := 3;
1757 garn_calc_iv_uom(3) := 'M';
1758 garn_calc_iv_mand(3) := 'N';
1759 garn_calc_iv_dbi(3) := 'N';
1760 garn_calc_iv_lkp(3) := NULL;
1761 garn_calc_iv_dflt(3) := NULL;
1762
1763 garn_calc_iv_names(4) := 'Date Served';
1764 garn_calc_iv_seq(4) := 4;
1765 garn_calc_iv_uom(4) := 'D';
1766 garn_calc_iv_mand(4) := 'N';
1767 garn_calc_iv_dbi(4) := 'N';
1768 garn_calc_iv_lkp(4) := NULL;
1769 garn_calc_iv_dflt(4) := NULL;
1770
1771 garn_calc_iv_names(5) := 'Arrears Dedn Amount';
1772 garn_calc_iv_seq(5) := 5;
1773 garn_calc_iv_uom(5) := 'M';
1774 garn_calc_iv_mand(5) := 'N';
1775 garn_calc_iv_dbi(5) := 'N';
1776 garn_calc_iv_lkp(5) := NULL;
1777 garn_calc_iv_dflt(5) := NULL;
1778
1779 garn_calc_iv_names(6) := 'Date In Arrears';
1780 garn_calc_iv_seq(6) := 6;
1781 garn_calc_iv_uom(6) := 'D';
1782 garn_calc_iv_mand(6) := 'N';
1783 garn_calc_iv_dbi(6) := 'N';
1784 garn_calc_iv_lkp(6) := NULL;
1785 garn_calc_iv_dflt(6) := NULL;
1786
1787 garn_calc_iv_names(7) := 'Num Dependents';
1788 garn_calc_iv_seq(7) := 7;
1789 garn_calc_iv_uom(7) := 'I';
1790 garn_calc_iv_mand(7) := 'N';
1791 garn_calc_iv_dbi(7) := 'N';
1792 garn_calc_iv_lkp(7) := NULL;
1793 garn_calc_iv_dflt(7) := NULL;
1794
1795 garn_calc_iv_names(8) := 'Filing Status';
1796 garn_calc_iv_seq(8) := 8;
1797 garn_calc_iv_uom(8) := 'C';
1798 garn_calc_iv_mand(8) := 'N';
1799 garn_calc_iv_dbi(8) := 'N';
1800 garn_calc_iv_lkp(8) := 'US_FEDLEVY_FILING_STATUS';
1801 garn_calc_iv_dflt(8) := NULL;
1802
1803 garn_calc_iv_names(9) := 'Allowances';
1804 garn_calc_iv_seq(9) := 9;
1805 garn_calc_iv_uom(9) := 'I';
1806 garn_calc_iv_mand(9) := 'N';
1807 garn_calc_iv_dbi(9) := 'N';
1808 garn_calc_iv_lkp(9) := NULL;
1809 garn_calc_iv_dflt(9) := NULL;
1810
1811 garn_calc_iv_names(10) := 'Dedns at Time of Writ';
1812 garn_calc_iv_seq(10) := 10;
1813 garn_calc_iv_uom(10) := 'M';
1814 garn_calc_iv_mand(10) := 'N';
1815 garn_calc_iv_dbi(10) := 'N';
1816 garn_calc_iv_lkp(10) := NULL;
1817 garn_calc_iv_dflt(10) := NULL;
1818
1819 garn_calc_iv_names(11) := 'Additional Amount Balance';
1820 garn_calc_iv_seq(11) := 11;
1821 garn_calc_iv_uom(11) := 'M';
1822 garn_calc_iv_mand(11) := 'N';
1823 garn_calc_iv_dbi(11) := 'N';
1824 garn_calc_iv_lkp(11) := NULL;
1825 garn_calc_iv_dflt(11) := NULL;
1826
1827 garn_calc_iv_names(12) := 'Replacement Amount Balance';
1828 garn_calc_iv_seq(12) := 12;
1829 garn_calc_iv_uom(12) := 'M';
1830 garn_calc_iv_mand(12) := 'N';
1831 garn_calc_iv_dbi(12) := 'N';
1832 garn_calc_iv_lkp(12) := NULL;
1833 garn_calc_iv_dflt(12) := NULL;
1834
1835 garn_calc_iv_names(13) := 'Arrears Amount Balance';
1836 garn_calc_iv_seq(13) := 13;
1837 garn_calc_iv_uom(13) := 'M';
1838 garn_calc_iv_mand(13) := 'N';
1839 garn_calc_iv_dbi(13) := 'N';
1840 garn_calc_iv_lkp(13) := NULL;
1841 garn_calc_iv_dflt(13) := NULL;
1842
1843 garn_calc_iv_names(14) := 'Primary Amount Balance';
1844 garn_calc_iv_seq(14) := 14;
1845 garn_calc_iv_uom(14) := 'M';
1846 garn_calc_iv_mand(14) := 'N';
1847 garn_calc_iv_dbi(14) := 'N';
1848 garn_calc_iv_lkp(14) := NULL;
1849 garn_calc_iv_dflt(14) := NULL;
1850
1851 garn_calc_iv_names(15) := 'Percentage';
1852 garn_calc_iv_seq(15) := 15;
1853 garn_calc_iv_uom(15) := 'N';
1854 garn_calc_iv_mand(15) := 'N';
1855 garn_calc_iv_dbi(15) := 'N';
1856 garn_calc_iv_lkp(15) := NULL;
1857 garn_calc_iv_dflt(15) := NULL;
1858
1859 garn_calc_iv_names(16) := 'Support Other Family';
1860 garn_calc_iv_seq(16) := 16;
1861 garn_calc_iv_uom(16) := 'C';
1862 garn_calc_iv_mand(16) := 'N';
1863 garn_calc_iv_dbi(16) := 'N';
1864 garn_calc_iv_lkp(16) := 'YES_NO';
1865 garn_calc_iv_dflt(16) := NULL;
1866
1867 garn_calc_iv_names(17) := 'Monthly Cap Amount';
1868 garn_calc_iv_seq(17) := 17;
1869 garn_calc_iv_uom(17) := 'M';
1870 garn_calc_iv_mand(17) := 'N';
1871 garn_calc_iv_dbi(17) := 'N';
1872 garn_calc_iv_lkp(17) := NULL;
1873 garn_calc_iv_dflt(17) := NULL;
1874
1875 garn_calc_iv_names(18) := 'Month To Date Balance';
1876 garn_calc_iv_seq(18) := 18;
1877 garn_calc_iv_uom(18) := 'M';
1878 garn_calc_iv_mand(18) := 'N';
1879 garn_calc_iv_dbi(18) := 'N';
1880 garn_calc_iv_lkp(18) := NULL;
1881 garn_calc_iv_dflt(18) := NULL;
1882
1883 garn_calc_iv_names(19) := 'Exempt Amt BO';
1884 garn_calc_iv_seq(19) := 19;
1885 garn_calc_iv_uom(19) := 'M';
1886 garn_calc_iv_mand(19) := 'N';
1887 garn_calc_iv_dbi(19) := 'N';
1888 garn_calc_iv_lkp(19) := NULL;
1889 garn_calc_iv_dflt(19) := NULL;
1890
1891 garn_calc_iv_names(20) := 'Period Cap Amount';
1892 garn_calc_iv_seq(20) := 20;
1893 garn_calc_iv_uom(20) := 'M';
1894 garn_calc_iv_mand(20) := 'N';
1895 garn_calc_iv_dbi(20) := 'N';
1896 garn_calc_iv_lkp(20) := NULL;
1897 garn_calc_iv_dflt(20) := NULL;
1898
1899 garn_calc_iv_names(21) := 'Period To Date Balance';
1900 garn_calc_iv_seq(21) := 21;
1901 garn_calc_iv_uom(21) := 'M';
1902 garn_calc_iv_mand(21) := 'N';
1903 garn_calc_iv_dbi(21) := 'N';
1904 garn_calc_iv_lkp(21) := NULL;
1905 garn_calc_iv_dflt(21) := NULL;
1906
1907
1908 garn_calc_iv_names(22) := 'Accrued Fees';
1909 garn_calc_iv_seq(22) := 22;
1910 garn_calc_iv_uom(22) := 'M';
1911 garn_calc_iv_mand(22) := 'N';
1912 garn_calc_iv_dbi(22) := 'N';
1913 garn_calc_iv_lkp(22) := NULL;
1914 garn_calc_iv_dflt(22) := NULL;
1915
1916 garn_calc_iv_names(23) := 'PTD Fee Balance';
1917 garn_calc_iv_seq(23) := 23;
1918 garn_calc_iv_uom(23) := 'M';
1919 garn_calc_iv_mand(23) := 'N';
1920 garn_calc_iv_dbi(23) := 'N';
1921 garn_calc_iv_lkp(23) := NULL;
1922 garn_calc_iv_dflt(23) := NULL;
1923
1924 garn_calc_iv_names(24) := 'Month Fee Balance';
1925 garn_calc_iv_seq(24) := 24;
1926 garn_calc_iv_uom(24) := 'M';
1927 garn_calc_iv_mand(24) := 'N';
1928 garn_calc_iv_dbi(24) := 'N';
1929 garn_calc_iv_lkp(24) := NULL;
1930 garn_calc_iv_dflt(24) := NULL;
1931
1932 garn_calc_iv_names(25) := 'Accrued Fee Correction';
1933 garn_calc_iv_seq(25) := 25;
1934 garn_calc_iv_uom(25) := 'M';
1935 garn_calc_iv_mand(25) := 'N';
1936 garn_calc_iv_dbi(25) := 'N';
1937 garn_calc_iv_lkp(25) := NULL;
1938 garn_calc_iv_dflt(25) := NULL;
1939
1940
1941 l_num_calc_ivs := 25;
1942
1943 garn_verif_iv_names(1) := 'Deduction Amount';
1944 garn_verif_iv_seq(1) := 1;
1945 garn_verif_iv_uom(1) := 'M';
1946 garn_verif_iv_mand(1) := 'N';
1947 garn_verif_iv_dbi(1) := 'N';
1948 garn_verif_iv_lkp(1) := NULL;
1949 garn_verif_iv_dflt(1) := NULL;
1950
1951 garn_verif_iv_names(2) := 'Arrears Amount';
1952 garn_verif_iv_seq(2) := 2;
1953 garn_verif_iv_uom(2) := 'M';
1954 garn_verif_iv_mand(2) := 'N';
1955 garn_verif_iv_dbi(2) := 'N';
1956 garn_verif_iv_lkp(2) := NULL;
1957 garn_verif_iv_dflt(2) := NULL;
1958
1959 garn_verif_iv_names(3) := 'Fee Amount';
1960 garn_verif_iv_seq(3) := 3;
1961 garn_verif_iv_uom(3) := 'M';
1962 garn_verif_iv_mand(3) := 'N';
1963 garn_verif_iv_dbi(3) := 'N';
1964 garn_verif_iv_lkp(3) := NULL;
1965 garn_verif_iv_dflt(3) := NULL;
1966
1967 garn_verif_iv_names(4) := 'DI Subject';
1968 garn_verif_iv_seq(4) := 4;
1969 garn_verif_iv_uom(4) := 'M';
1970 garn_verif_iv_mand(4) := 'N';
1971 garn_verif_iv_dbi(4) := 'N';
1972 garn_verif_iv_lkp(4) := NULL;
1973 garn_verif_iv_dflt(4) := NULL;
1974
1975 garn_verif_iv_names(5) := 'Jurisdiction';
1976 garn_verif_iv_seq(5) := 5;
1977 garn_verif_iv_uom(5) := 'C';
1978 garn_verif_iv_mand(5) := 'N';
1979 garn_verif_iv_dbi(5) := 'N';
1980 garn_verif_iv_lkp(5) := NULL;
1981 garn_verif_iv_dflt(5) := NULL;
1982
1983 garn_verif_iv_names(6) := 'Primary Amount Balance';
1984 garn_verif_iv_seq(6) := 6;
1985 garn_verif_iv_uom(6) := 'M';
1986 garn_verif_iv_mand(6) := 'N';
1987 garn_verif_iv_dbi(6) := 'N';
1988 garn_verif_iv_lkp(6) := NULL;
1989 garn_verif_iv_dflt(6) := NULL;
1990
1991 garn_verif_iv_names(7) := 'Total Owed';
1992 garn_verif_iv_seq(7) := 7;
1993 garn_verif_iv_uom(7) := 'M';
1994 garn_verif_iv_mand(7) := 'N';
1995 garn_verif_iv_dbi(7) := 'N';
1996 garn_verif_iv_lkp(7) := NULL;
1997 garn_verif_iv_dflt(7) := NULL;
1998
1999 garn_verif_iv_names(8) := 'Date Served';
2000 garn_verif_iv_seq(8) := 8;
2001 garn_verif_iv_uom(8) := 'D';
2002 garn_verif_iv_mand(8) := 'N';
2003 garn_verif_iv_dbi(8) := 'N';
2004 garn_verif_iv_lkp(8) := NULL;
2005 garn_verif_iv_dflt(8) := NULL;
2006
2007 garn_verif_iv_names(9) := 'DI Subject 45';
2008 garn_verif_iv_seq(9) := 9;
2009 garn_verif_iv_uom(9) := 'M';
2010 garn_verif_iv_mand(9) := 'N';
2011 garn_verif_iv_dbi(9) := 'N';
2012 garn_verif_iv_lkp(9) := NULL;
2013 garn_verif_iv_dflt(9) := NULL;
2014
2015 garn_verif_iv_names(10) := 'DI Subject 50';
2016 garn_verif_iv_seq(10) := 10;
2017 garn_verif_iv_uom(10) := 'M';
2018 garn_verif_iv_mand(10) := 'N';
2019 garn_verif_iv_dbi(10) := 'N';
2020 garn_verif_iv_lkp(10) := NULL;
2021 garn_verif_iv_dflt(10) := NULL;
2022
2023 garn_verif_iv_names(11) := 'Support Other Family';
2024 garn_verif_iv_seq(11) := 11;
2025 garn_verif_iv_uom(11) := 'C';
2026 garn_verif_iv_mand(11) := 'N';
2027 garn_verif_iv_dbi(11) := 'N';
2028 garn_verif_iv_lkp(11) := 'YES_NO';
2029 garn_verif_iv_dflt(11) := NULL;
2030
2031 garn_verif_iv_names(12) := 'Accrued Fees';
2032 garn_verif_iv_seq(12) := 12;
2033 garn_verif_iv_uom(12) := 'M';
2034 garn_verif_iv_mand(12) := 'N';
2035 garn_verif_iv_dbi(12) := 'N';
2036 garn_verif_iv_lkp(12) := NULL;
2037 garn_verif_iv_dflt(12) := NULL;
2038
2039 garn_verif_iv_names(13) := 'PTD Fee Balance';
2040 garn_verif_iv_seq(13) := 13;
2041 garn_verif_iv_uom(13) := 'M';
2042 garn_verif_iv_mand(13) := 'N';
2043 garn_verif_iv_dbi(13) := 'N';
2044 garn_verif_iv_lkp(13) := NULL;
2045 garn_verif_iv_dflt(13) := NULL;
2046
2047 garn_verif_iv_names(14) := 'Month Fee Balance';
2048 garn_verif_iv_seq(14) := 14;
2049 garn_verif_iv_uom(14) := 'M';
2050 garn_verif_iv_mand(14) := 'N';
2051 garn_verif_iv_dbi(14) := 'N';
2052 garn_verif_iv_lkp(14) := NULL;
2053 garn_verif_iv_dflt(14) := NULL;
2054
2055 l_num_verif_ivs := 14;
2056
2057
2058 garn_si_iv_names(1) := 'Replace Amt';
2059 garn_si_iv_seq(1) := 1;
2060 garn_si_iv_uom(1) := 'M';
2061 garn_si_iv_mand(1) := 'N';
2062 garn_si_iv_dbi(1) := 'N';
2063 garn_si_iv_lkp(1) := NULL;
2064 garn_si_iv_dflt(1) := NULL;
2065
2066 garn_si_iv_names(2) := 'Addl Amt';
2067 garn_si_iv_seq(2) := 2;
2068 garn_si_iv_uom(2) := 'M';
2069 garn_si_iv_mand(2) := 'N';
2070 garn_si_iv_dbi(2) := 'N';
2071 garn_si_iv_lkp(2) := NULL;
2072 garn_si_iv_dflt(2) := NULL;
2073
2074 garn_si_iv_names(3) := 'Adjust Arrears';
2075 garn_si_iv_seq(3) := 3;
2076 garn_si_iv_uom(3) := 'M';
2077 garn_si_iv_mand(3) := 'N';
2078 garn_si_iv_dbi(3) := 'N';
2079 garn_si_iv_lkp(3) := NULL;
2080 garn_si_iv_dflt(3) := NULL;
2081
2082 l_num_si_ivs := 3;
2083
2084 garn_sf_iv_names(1) := 'Not Taken';
2085 garn_sf_iv_seq(1) := 1;
2086 garn_sf_iv_uom(1) := 'M';
2087 garn_sf_iv_mand(1) := 'N';
2088 garn_sf_iv_dbi(1) := 'N';
2089 garn_sf_iv_lkp(1) := NULL;
2090 garn_sf_iv_dflt(1) := NULL;
2091
2092 garn_sf_iv_names(2) := 'Arrears Contr';
2093 garn_sf_iv_seq(2) := 2;
2094 garn_sf_iv_uom(2) := 'M';
2095 garn_sf_iv_mand(2) := 'N';
2096 garn_sf_iv_dbi(2) := 'N';
2097 garn_sf_iv_lkp(2) := NULL;
2098 garn_sf_iv_dflt(2) := NULL;
2099
2100 garn_sf_iv_names(3) := 'Calculated Fee Amount';
2101 garn_sf_iv_seq(3) := 3;
2102 garn_sf_iv_uom(3) := 'M';
2103 garn_sf_iv_mand(3) := 'N';
2104 garn_sf_iv_dbi(3) := 'N';
2105 garn_sf_iv_lkp(3) := NULL;
2106 garn_sf_iv_dflt(3) := NULL;
2107
2108 -- Feeds child supp total dedns balance...378699
2109 garn_sf_iv_names(4) := 'Calculated Amount';
2110 garn_sf_iv_seq(4) := 4;
2111 garn_sf_iv_uom(4) := 'M';
2112 garn_sf_iv_mand(4) := 'N';
2113 garn_sf_iv_dbi(4) := 'N';
2114 garn_sf_iv_lkp(4) := NULL;
2115 garn_sf_iv_dflt(4) := NULL;
2116
2117 garn_sf_iv_names(5) := 'Replacement Amt';
2118 garn_sf_iv_seq(5) := 5;
2119 garn_sf_iv_uom(5) := 'M';
2120 garn_sf_iv_mand(5) := 'N';
2121 garn_sf_iv_dbi(5) := 'N';
2122 garn_sf_iv_lkp(5) := NULL;
2123 garn_sf_iv_dflt(5) := NULL;
2124
2125 garn_sf_iv_names(6) := 'Additional Amt';
2126 garn_sf_iv_seq(6) := 6;
2127 garn_sf_iv_uom(6) := 'M';
2128 garn_sf_iv_mand(6) := 'N';
2129 garn_sf_iv_dbi(6) := 'N';
2130 garn_sf_iv_lkp(6) := NULL;
2131 garn_sf_iv_dflt(6) := NULL;
2132
2133 garn_sf_iv_names(7) := 'Counter';
2134 garn_sf_iv_seq(7) := 7;
2135 garn_sf_iv_uom(7) := 'N';
2136 garn_sf_iv_mand(7) := 'N';
2137 garn_sf_iv_dbi(7) := 'N';
2138 garn_sf_iv_lkp(7) := NULL;
2139 garn_sf_iv_dflt(7) := NULL;
2140
2141 garn_sf_iv_names(8) := 'Voldedns at Writ';
2142 garn_sf_iv_seq(8) := 8;
2143 garn_sf_iv_uom(8) := 'M';
2144 garn_sf_iv_mand(8) := 'N';
2145 garn_sf_iv_dbi(8) := 'N';
2146 garn_sf_iv_lkp(8) := NULL;
2147 garn_sf_iv_dflt(8) := NULL;
2148
2149 garn_sf_iv_names(9) := 'To Total Owed';
2150 garn_sf_iv_seq(9) := 9;
2151 garn_sf_iv_uom(9) := 'M';
2152 garn_sf_iv_mand(9) := 'N';
2153 garn_sf_iv_dbi(9) := 'N';
2154 garn_sf_iv_lkp(9) := NULL;
2155 garn_sf_iv_dflt(9) := NULL;
2156
2157 garn_sf_iv_names(10) := 'Accrued Fees';
2158 garn_sf_iv_seq(10) := 10;
2159 garn_sf_iv_uom(10) := 'M';
2160 garn_sf_iv_mand(10) := 'N';
2161 garn_sf_iv_dbi(10) := 'N';
2162 garn_sf_iv_lkp(10) := NULL;
2163 garn_sf_iv_dflt(10) := NULL;
2164
2165 l_num_sf_ivs := 10;
2166
2167 /* 338498 : 20th Feb 1996 - begin */
2168
2169 garn_fee_iv_names(1) := 'Withheld Fee Amount';
2170 garn_fee_iv_seq(1) := 1;
2171 garn_fee_iv_uom(1) := 'M';
2172 garn_fee_iv_mand(1) := 'N';
2173 garn_fee_iv_dbi(1) := 'N';
2174 garn_fee_iv_lkp(1) := NULL;
2175 garn_fee_iv_dflt(1) := NULL;
2176
2177 l_num_fee_ivs := 1;
2178
2179 garn_vp_iv_names(1) := 'Amount';
2180 garn_vp_iv_seq(1) := 1;
2181 garn_vp_iv_uom(1) := 'M';
2182 garn_vp_iv_mand(1) := 'N';
2183 garn_vp_iv_dbi(1) := 'N';
2184 garn_vp_iv_lkp(1) := NULL;
2185 garn_vp_iv_dflt(1) := NULL;
2186
2187 garn_vp_iv_names(2) := 'Calc Priority';
2188 garn_vp_iv_seq(2) := 2;
2189 garn_vp_iv_uom(2) := 'N';
2190 garn_vp_iv_mand(2) := 'N';
2191 garn_vp_iv_dbi(2) := 'N';
2192 garn_vp_iv_lkp(2) := NULL;
2193 garn_vp_iv_dflt(2) := NULL;
2194
2195 garn_vp_iv_names(3) := 'DI Subject';
2196 garn_vp_iv_seq(3) := 3;
2197 garn_vp_iv_uom(3) := 'M';
2198 garn_vp_iv_mand(3) := 'N';
2199 garn_vp_iv_dbi(3) := 'N';
2200 garn_vp_iv_lkp(3) := NULL;
2201 garn_vp_iv_dflt(3) := NULL;
2202
2203 garn_vp_iv_names(4) := 'Jurisdiction';
2204 garn_vp_iv_seq(4) := 4;
2205 garn_vp_iv_uom(4) := 'C';
2206 garn_vp_iv_mand(4) := 'N';
2207 garn_vp_iv_dbi(4) := 'N';
2208 garn_vp_iv_lkp(4) := NULL;
2209 garn_vp_iv_dflt(4) := NULL;
2210
2211 garn_vp_iv_names(5) := 'Arrears Amount';
2212 garn_vp_iv_seq(5) := 5;
2213 garn_vp_iv_uom(5) := 'M';
2214 garn_vp_iv_mand(5) := 'N';
2215 garn_vp_iv_dbi(5) := 'N';
2216 garn_vp_iv_lkp(5) := NULL;
2217 garn_vp_iv_dflt(5) := NULL;
2218
2219 garn_vp_iv_names(6) := 'Fee Amount';
2220 garn_vp_iv_seq(6) := 6;
2221 garn_vp_iv_uom(6) := 'M';
2222 garn_vp_iv_mand(6) := 'N';
2223 garn_vp_iv_dbi(6) := 'N';
2224 garn_vp_iv_lkp(6) := NULL;
2225 garn_vp_iv_dflt(6) := NULL;
2226
2227 garn_vp_iv_names(7) := 'Primary Amount Balance';
2228 garn_vp_iv_seq(7) := 7;
2229 garn_vp_iv_uom(7) := 'M';
2230 garn_vp_iv_mand(7) := 'N';
2231 garn_vp_iv_dbi(7) := 'N';
2232 garn_vp_iv_lkp(7) := NULL;
2233 garn_vp_iv_dflt(7) := NULL;
2234
2235 garn_vp_iv_names(8) := 'Total Owed';
2236 garn_vp_iv_seq(8) := 8;
2237 garn_vp_iv_uom(8) := 'M';
2238 garn_vp_iv_mand(8) := 'N';
2239 garn_vp_iv_dbi(8) := 'N';
2240 garn_vp_iv_lkp(8) := NULL;
2241 garn_vp_iv_dflt(8) := NULL;
2242
2243 garn_vp_iv_names(9) := 'Date Served';
2244 garn_vp_iv_seq(9) := 9;
2245 garn_vp_iv_uom(9) := 'D';
2246 garn_vp_iv_mand(9) := 'N';
2247 garn_vp_iv_dbi(9) := 'N';
2248 garn_vp_iv_lkp(9) := NULL;
2249 garn_vp_iv_dflt(9) := NULL;
2250
2251 garn_vp_iv_names(10) := 'DI Subject 45';
2252 garn_vp_iv_seq(10) := 10;
2253 garn_vp_iv_uom(10) := 'M';
2254 garn_vp_iv_mand(10) := 'N';
2255 garn_vp_iv_dbi(10) := 'N';
2256 garn_vp_iv_lkp(10) := NULL;
2257 garn_vp_iv_dflt(10) := NULL;
2258
2259 garn_vp_iv_names(11) := 'DI Subject 50';
2260 garn_vp_iv_seq(11) := 11;
2261 garn_vp_iv_uom(11) := 'M';
2262 garn_vp_iv_mand(11) := 'N';
2263 garn_vp_iv_dbi(11) := 'N';
2264 garn_vp_iv_lkp(11) := NULL;
2265 garn_vp_iv_dflt(11) := NULL;
2266
2267 garn_vp_iv_names(12) := 'Support Other Family';
2268 garn_vp_iv_seq(12) := 12;
2269 garn_vp_iv_uom(12) := 'C';
2270 garn_vp_iv_mand(12) := 'N';
2271 garn_vp_iv_dbi(12) := 'N';
2272 garn_vp_iv_lkp(12) := 'YES_NO';
2273 garn_vp_iv_dflt(12) := NULL;
2274
2275 garn_vp_iv_names(13) := 'Accrued Fees';
2276 garn_vp_iv_seq(13) := 13;
2277 garn_vp_iv_uom(13) := 'M';
2278 garn_vp_iv_mand(13) := 'N';
2279 garn_vp_iv_dbi(13) := 'N';
2280 garn_vp_iv_lkp(13) := NULL;
2281 garn_vp_iv_dflt(13) := NULL;
2282
2283 garn_vp_iv_names(14) := 'PTD Fee Balance';
2284 garn_vp_iv_seq(14) := 14;
2285 garn_vp_iv_uom(14) := 'M';
2286 garn_vp_iv_mand(14) := 'N';
2287 garn_vp_iv_dbi(14) := 'N';
2288 garn_vp_iv_lkp(14) := NULL;
2289 garn_vp_iv_dflt(14) := NULL;
2290
2291 garn_vp_iv_names(15) := 'Month Fee Balance';
2292 garn_vp_iv_seq(15) := 15;
2293 garn_vp_iv_uom(15) := 'M';
2294 garn_vp_iv_mand(15) := 'N';
2295 garn_vp_iv_dbi(15) := 'N';
2296 garn_vp_iv_lkp(15) := NULL;
2297 garn_vp_iv_dflt(15) := NULL;
2298
2299 l_num_vp_ivs := 15;
2300
2301 /* 338498 : 20th Feb 1996 - end */
2302
2303 garn_assoc_bal_names(1) := p_garn_name;
2304 garn_assoc_bal_names(2) := p_garn_name||' Additional';
2305 garn_assoc_bal_names(3) := p_garn_name||' Replacement';
2306 garn_assoc_bal_names(4) := p_garn_name||' Accrued';
2307 garn_assoc_bal_names(5) := p_garn_name||' Arrears';
2308 garn_assoc_bal_names(6) := p_garn_name||' Not Taken';
2309 garn_assoc_bal_names(7) := p_garn_name||' Fees';
2310 garn_assoc_bal_names(8) := p_garn_name||' Vol Dedns';
2311 garn_assoc_bal_names(9) := p_garn_name||' Accrued Fees';
2312
2313
2314 l_num_assoc_bals := 9;
2315
2316 --
2317 -- Create Associated Balance Types and Defined Balances
2318 -- NOTE : Done in this order because of locking ladder.
2319 --
2320 FOR i in 1..l_num_assoc_bals LOOP
2321
2322 -- Check for existence before creating baltype.
2323 -- If already exists, set dedn_assoc_bal_id(i) appropriately for
2324 -- future reference.
2325
2326 already_exists := hr_template_existence.bal_exists(
2327 p_bg_id => p_bg_id,
2328 p_bal_name => garn_assoc_bal_names(i),
2329 p_eff_date => g_eff_start_date);
2330
2331 if already_exists = 0 then
2332
2333 -- Check element name, ie. primary balance name, is unique to
2334 -- balances within this BG.
2335 pay_balance_types_pkg.chk_balance_type(
2336 p_row_id => NULL,
2337 p_business_group_id => p_bg_id,
2338 p_legislation_code => NULL,
2339 p_balance_name => garn_assoc_bal_names(i),
2340 p_reporting_name => garn_assoc_bal_names(i),
2341 p_assignment_remuneration_flag => 'N');
2342
2343 -- Check element name, ie. primary balance name, is unique to
2344 -- balances provided as startup data.
2345 pay_balance_types_pkg.chk_balance_type(
2346 p_row_id => NULL,
2347 p_business_group_id => NULL,
2348 p_legislation_code => 'US',
2349 p_balance_name => garn_assoc_bal_names(i),
2350 p_reporting_name => garn_assoc_bal_names(i),
2351 p_assignment_remuneration_flag => 'N');
2352
2353 -- For Bug 3651755 added if condition
2354
2355 if garn_assoc_bal_names(i) in (p_garn_name||' Additional',
2356 p_garn_name||' Replacement',
2357 p_garn_name||' Vol Dedns',
2358 p_garn_name||' Accrued Fees') then
2359
2360 v_bal_type_id := pay_db_pay_setup.create_balance_type(
2361 p_balance_name => garn_assoc_bal_names(i),
2362 p_uom => null,
2363 p_uom_code => 'M',
2364 p_reporting_name => garn_assoc_bal_names(i),
2365 p_business_group_name => v_bg_name,
2366 p_legislation_code => NULL,
2367 p_legislation_subgroup => NULL,
2368 p_bc_leg_code => 'US',
2369 p_effective_date => g_eff_start_date);
2370
2371 else
2372
2373 v_bal_type_id := pay_db_pay_setup.create_balance_type(
2374 p_balance_name => garn_assoc_bal_names(i),
2375 p_uom => null,
2376 p_uom_code => 'M',
2377 p_reporting_name => garn_assoc_bal_names(i),
2378 p_business_group_name => v_bg_name,
2379 p_legislation_code => NULL,
2380 p_legislation_subgroup => NULL,
2381 p_balance_category => 'Involuntary Deductions', -- Bug 3311781
2382 p_bc_leg_code => 'US',
2383 p_effective_date => g_eff_start_date);
2384 end if;
2385
2386 garn_assoc_bal_ids(i) := v_bal_type_id;
2387
2388 else
2389
2390 garn_assoc_bal_ids(i) := already_exists;
2391
2392 end if;
2393
2394 hr_utility.trace('Before do_defined_balances');
2395 if garn_assoc_bal_names(i) in (p_garn_name||' Additional',
2396 p_garn_name||' Replacement',
2397 p_garn_name||' Vol Dedns',
2398 p_garn_name||' Accrued Fees') then
2399
2400 do_defined_balances(p_bal_id => garn_assoc_bal_ids(i),
2401 p_bal_name => garn_assoc_bal_names(i),
2402 p_bg_name => v_bg_name,
2403 p_save_run_value => 'N');
2404 else
2405
2406 do_defined_balances(p_bal_id => garn_assoc_bal_ids(i),
2407 p_bal_name => garn_assoc_bal_names(i),
2408 p_bg_name => v_bg_name,
2409 p_save_run_value => 'Y');
2410
2411 end if;
2412
2413 hr_utility.trace('After do_defined_balances');
2414
2415 END LOOP;
2416
2417 --
2418 -- Create Element Types
2419 --
2420 FOR i in 1..l_num_eles LOOP
2421 v_ele_type_id := ins_garn_ele_type (
2422 p_ele_name => garn_ele_names(i),
2423 p_ele_reporting_name => garn_ele_repnames(i),
2424 p_ele_description => garn_ele_desc(i),
2425 p_ele_class => g_ele_classification,
2426 p_ele_category => p_category,
2427 p_ele_processing_type => garn_ele_proc_type(i),
2428 p_ele_priority => garn_ele_priority(i),
2429 p_ele_standard_link => g_ele_standard_link,
2430 p_skip_formula_id => g_skip_formula_id,
2431 p_ind_only_flag => garn_indirect_only(i),
2432 p_third_party_pay => garn_third_party_pay(i),
2433 p_ele_eff_start_date => g_eff_start_date,
2434 p_ele_eff_end_date => g_eff_end_date,
2435 p_bg_name => v_bg_name,
2436 p_bg_id => p_bg_id);
2437
2438 garn_ele_ids(i) := v_ele_type_id;
2439
2440 v_pay_value_name := hr_input_values.get_pay_value_name(g_template_leg_code);
2441
2442 begin
2443 UPDATE pay_input_values_f
2444 SET mandatory_flag = 'X'
2445 WHERE element_type_id = v_ele_type_id
2446 AND name = v_pay_value_name;
2447 if sql%notfound then
2448 hr_utility.trace('Error here...');
2449 end if;
2450 end;
2451
2452 begin
2453 SELECT input_value_id
2454 INTO v_payval_id
2455 FROM pay_input_values_f
2456 WHERE element_type_id = v_ele_type_id
2457 AND name = v_pay_value_name
2458 AND g_eff_end_date between effective_start_date
2459 and effective_end_date; /*1498260*/
2460
2461 exception
2462 when no_data_found then
2463 hr_utility.trace('Error here...'||to_char(v_ele_type_id));
2464 end;
2465
2466 garn_payval_id(i) := v_payval_id;
2467
2468 -- Update Element Type DDF as appropriate.
2469
2470 UPDATE pay_element_types_f
2471 SET element_information_category = g_ele_info_cat,
2472 element_information1 = p_category,
2473 element_information2 = g_partial_dedn,
2474 element_information3 = g_ele_runtype,
2475 element_information9 = garn_mix_category(i)
2476 WHERE element_type_id = v_ele_type_id
2477 AND business_group_id + 0 = p_bg_id;
2478
2479 --
2480 -- Create status processing rule.
2481 --
2482 IF garn_pay_formula(i) IS NOT NULL THEN
2483
2484 begin
2485
2486 SELECT formula_id
2487 INTO v_calc_formula_id
2488 FROM ff_formulas_f
2489 WHERE formula_name = garn_pay_formula(i)
2490 AND g_eff_start_date between effective_start_date
2491 and effective_end_date
2492 AND nvl(business_group_id, p_bg_id) + 0 = p_bg_id
2493 AND nvl(legislation_code, g_template_leg_code) = g_template_leg_code
2494 AND rownum < 2;
2495
2496 exception WHEN NO_DATA_FOUND THEN
2497
2498 v_calc_formula_id := NULL;
2499
2500 hr_utility.set_location('Error : DID NOT FIND CALC FORMULA', 9);
2501
2502 end;
2503
2504 hr_utility.set_location('ele '||v_ele_type_id||'ff '||v_calc_formula_id||'bg '||p_bg_id, 9);
2505
2506 -- Looks to require a change here since Alimony and Spousal support verifiers
2507 -- will already have a stat proc rule but not with the new verif formula now put
2508 -- on spousal and alimony...change the spr_exists fn to not use ff id...return ff of
2509 -- existing spr...then compare with v_calc_formula_id, if not the same - then update
2510 -- existing spr, if same, do nothing...if no spr exists, proceed as normal and create spr...
2511
2512 already_exists := hr_template_existence.spr_exists (
2513 p_ele_id => v_ele_type_id,
2514 p_ff_id => v_spr_formula_id,
2515 p_bg_id => p_bg_id,
2516 p_val_date => g_eff_start_date);
2517
2518 if already_exists = 0 then
2519
2520 v_stat_proc_rule_id :=
2521 pay_formula_results.ins_stat_proc_rule (
2522 p_business_group_id => p_bg_id,
2523 p_legislation_code => NULL,
2524 p_legislation_subgroup => g_template_leg_subgroup,
2525 p_effective_start_date => g_eff_start_date,
2526 p_effective_end_date => g_eff_end_date,
2527 p_element_type_id => v_ele_type_id,
2528 p_assignment_status_type_id => g_asst_status_type_id,
2529 p_formula_id => v_calc_formula_id,
2530 p_processing_rule => g_proc_rule);
2531
2532 garn_statproc_rule_id(i) := v_stat_proc_rule_id;
2533
2534 else
2535
2536 garn_statproc_rule_id(i) := already_exists;
2537
2538 IF v_calc_formula_id = v_spr_formula_id THEN
2539
2540 NULL; -- spr already has this formula attached.
2541
2542 ELSE
2543
2544 -- Make sure to update the "standard" processing rule, ie. which
2545 -- is the one that was checked for in spr_exists.
2546 UPDATE pay_status_processing_rules_f
2547 SET formula_id = v_calc_formula_id
2548 WHERE status_processing_rule_id = already_exists
2549 AND g_eff_start_date between effective_start_date
2550 and effective_end_date;
2551
2552 END IF;
2553
2554 end if;
2555
2556 END IF;
2557
2558 END LOOP;
2559
2560 --
2561 -- Now create all base element input values.
2562 --
2563 hr_utility.trace('Base ivs = '||to_char(l_num_base_ivs));
2564 FOR k in 1..l_num_base_ivs LOOP
2565 hr_utility.trace('Base iv # = '||to_char(k));
2566 already_exists := hr_template_existence.iv_name_exists(
2567 p_ele_id => garn_ele_ids(1),
2568 p_bg_id => p_bg_id,
2569 p_iv_name => garn_base_iv_names(k),
2570 p_eff_date => g_eff_start_date);
2571
2572 if already_exists = 0 then
2573
2574 /*
2575 40.15 : Call new API to add input value over life of element if
2576 upgrade mode = Yes
2577 */
2578
2579 IF l_upgrade_mode = 'N' THEN
2580
2581 v_inpval_id := pay_db_pay_setup.create_input_value (
2582 p_element_name => garn_ele_names(1),
2583 p_name => garn_base_iv_names(k),
2584 p_uom_code => garn_base_iv_uom(k),
2585 p_mandatory_flag => garn_base_iv_mand(k),
2586 p_generate_db_item_flag => garn_base_iv_dbi(k),
2587 p_default_value => garn_base_iv_dflt(k),
2588 p_min_value => NULL,
2589 p_max_value => NULL,
2590 p_warning_or_error => NULL,
2591 p_lookup_type => garn_base_iv_lkp(k),
2592 p_formula_id => NULL,
2593 p_hot_default_flag => 'N',
2594 p_display_sequence => garn_base_iv_seq(k),
2595 p_business_group_name => v_bg_name,
2596 p_effective_start_date => g_eff_start_date,
2597 p_effective_end_date => g_eff_end_date);
2598
2599 garn_base_iv_ids(k) := v_inpval_id;
2600
2601 hr_input_values.chk_input_value(
2602 p_element_type_id => garn_ele_ids(1),
2603 p_legislation_code => g_template_leg_code,
2604 p_val_start_date => g_eff_start_date,
2605 p_val_end_date => g_eff_end_date,
2606 p_insert_update_flag => 'UPDATE',
2607 p_input_value_id => garn_base_iv_ids(k),
2608 p_rowid => NULL,
2609 p_recurring_flag => 'N',
2610 p_mandatory_flag => garn_base_iv_mand(k),
2611 p_hot_default_flag => 'N',
2612 p_standard_link_flag => 'N',
2613 p_classification_type => 'N',
2614 p_name => garn_base_iv_names(k),
2615 p_uom => garn_base_iv_uom(k),
2616 p_min_value => NULL,
2617 p_max_value => NULL,
2618 p_default_value => garn_base_iv_dflt(k),
2619 p_lookup_type => garn_base_iv_lkp(k),
2620 p_formula_id => NULL,
2621 p_generate_db_items_flag => garn_base_iv_dbi(k),
2622 p_warning_or_error => NULL);
2623
2624 hr_input_values.ins_3p_input_values(
2625 p_val_start_date => g_eff_start_date,
2626 p_val_end_date => g_eff_end_date,
2627 p_element_type_id => garn_ele_ids(1),
2628 p_primary_classification_id => g_ele_class_id,
2629 p_input_value_id => garn_base_iv_ids(k),
2630 p_default_value => garn_base_iv_dflt(k),
2631 p_max_value => NULL,
2632 p_min_value => NULL,
2633 p_warning_or_error_flag => NULL,
2634 p_input_value_name => garn_base_iv_names(k),
2635 p_db_items_flag => garn_base_iv_dbi(k),
2636 p_costable_type => NULL,
2637 p_hot_default_flag => 'N',
2638 p_business_group_id => p_bg_id,
2639 p_legislation_code => NULL,
2640 p_startup_mode => NULL);
2641
2642 ELSE
2643
2644 v_inpval_id := pay_db_pay_setup.create_input_value (
2645 p_element_name => garn_ele_names(1),
2646 p_name => garn_base_iv_names(k),
2647 p_uom_code => garn_base_iv_uom(k),
2648 p_mandatory_flag => garn_base_iv_mand(k),
2649 p_generate_db_item_flag => garn_base_iv_dbi(k),
2650 p_default_value => garn_base_iv_dflt(k),
2651 p_min_value => NULL,
2652 p_max_value => NULL,
2653 p_warning_or_error => NULL,
2654 p_lookup_type => garn_base_iv_lkp(k),
2655 p_formula_id => NULL,
2656 p_hot_default_flag => 'N',
2657 p_display_sequence => garn_base_iv_seq(k),
2658 p_business_group_name => v_bg_name,
2659 p_effective_start_date => g_eff_start_date,
2660 p_effective_end_date => g_eff_end_date);
2661
2662 garn_base_iv_ids(k) := v_inpval_id;
2663
2664 -- Existing elements being upgraded. Call modified iv procedures
2665 -- that do not validate for existing entries or run results.
2666
2667 pay_template_ivs.chk_input_value(
2668 p_element_type_id => garn_ele_ids(1),
2669 p_legislation_code => g_template_leg_code,
2670 p_val_start_date => g_eff_start_date,
2671 p_val_end_date => g_eff_end_date,
2672 p_insert_update_flag => 'UPDATE',
2673 p_input_value_id => garn_base_iv_ids(k),
2674 p_rowid => NULL,
2675 p_recurring_flag => 'N',
2676 p_mandatory_flag => garn_base_iv_mand(k),
2677 p_hot_default_flag => 'N',
2678 p_standard_link_flag => 'N',
2679 p_classification_type => 'N',
2680 p_name => garn_base_iv_names(k),
2681 p_uom => garn_base_iv_uom(k),
2682 p_min_value => NULL,
2683 p_max_value => NULL,
2684 p_default_value => garn_base_iv_dflt(k),
2685 p_lookup_type => garn_base_iv_lkp(k),
2686 p_formula_id => NULL,
2687 p_generate_db_items_flag => garn_base_iv_dbi(k),
2688 p_warning_or_error => NULL);
2689
2690 pay_template_ivs.ins_3p_input_values(
2691 p_val_start_date => g_eff_start_date,
2692 p_val_end_date => g_eff_end_date,
2693 p_element_type_id => garn_ele_ids(1),
2694 p_primary_classification_id => g_ele_class_id,
2695 p_input_value_id => garn_base_iv_ids(k),
2696 p_default_value => garn_base_iv_dflt(k),
2697 p_max_value => NULL,
2698 p_min_value => NULL,
2699 p_warning_or_error_flag => NULL,
2700 p_input_value_name => garn_base_iv_names(k),
2701 p_db_items_flag => garn_base_iv_dbi(k),
2702 p_costable_type => NULL,
2703 p_hot_default_flag => 'N',
2704 p_business_group_id => p_bg_id,
2705 p_legislation_code => NULL,
2706 p_startup_mode => NULL);
2707
2708 -- Need to add link input value, element entry values, and
2709 -- run result values for new input value on existing element.
2710
2711 pay_template_ivs.new_input_value (
2712 p_element_type_id => garn_ele_ids(1),
2713 p_input_value_id => garn_base_iv_ids(k),
2714 p_costed_flag => 'N',
2715 p_default_value => garn_base_iv_dflt(k),
2716 p_max_value => NULL,
2717 p_min_value => NULL,
2718 p_warning_or_error => NULL);
2719
2720 END IF;
2721
2722 else
2723
2724 garn_base_iv_ids(k) := already_exists;
2725
2726 end if;
2727
2728 END LOOP;
2729
2730 --
2731 -- Now create all calc element input values.
2732 --
2733 hr_utility.trace('Calc ivs = '||to_char(l_num_calc_ivs));
2734 FOR c in 1..l_num_calc_ivs LOOP
2735 hr_utility.trace('Calc iv # = '||to_char(c));
2736 already_exists := hr_template_existence.iv_name_exists(
2737 p_ele_id => garn_ele_ids(2),
2738 p_bg_id => p_bg_id,
2739 p_iv_name => garn_calc_iv_names(c),
2740 p_eff_date => g_eff_start_date);
2741
2742 if already_exists = 0 then
2743
2744 /* 40.15 : Call new API to add input value over life of element if
2745 upgrade mode = Yes
2746 */
2747
2748 IF l_upgrade_mode = 'N' THEN
2749
2750 v_inpval_id := pay_db_pay_setup.create_input_value (
2751 p_element_name => garn_ele_names(2),
2752 p_name => garn_calc_iv_names(c),
2753 p_uom_code => garn_calc_iv_uom(c),
2754 p_mandatory_flag => garn_calc_iv_mand(c),
2755 p_generate_db_item_flag => garn_calc_iv_dbi(c),
2756 p_default_value => garn_calc_iv_dflt(c),
2757 p_min_value => NULL,
2758 p_max_value => NULL,
2759 p_warning_or_error => NULL,
2760 p_lookup_type => garn_calc_iv_lkp(c),
2761 p_formula_id => NULL,
2762 p_hot_default_flag => 'N',
2763 p_display_sequence => garn_calc_iv_seq(c),
2764 p_business_group_name => v_bg_name,
2765 p_effective_start_date => g_eff_start_date,
2766 p_effective_end_date => g_eff_end_date);
2767
2768 garn_calc_iv_ids(c) := v_inpval_id;
2769
2770 hr_input_values.chk_input_value(
2771 p_element_type_id => garn_ele_ids(2),
2772 p_legislation_code => g_template_leg_code,
2773 p_val_start_date => g_eff_start_date,
2774 p_val_end_date => g_eff_end_date,
2775 p_insert_update_flag => 'UPDATE',
2776 p_input_value_id => garn_calc_iv_ids(c),
2777 p_rowid => NULL,
2778 p_recurring_flag => 'N',
2779 p_mandatory_flag => garn_calc_iv_mand(c),
2780 p_hot_default_flag => 'N',
2781 p_standard_link_flag => 'N',
2782 p_classification_type => 'N',
2783 p_name => garn_calc_iv_names(c),
2784 p_uom => garn_calc_iv_uom(c),
2785 p_min_value => NULL,
2786 p_max_value => NULL,
2787 p_default_value => garn_calc_iv_dflt(c),
2788 p_lookup_type => garn_calc_iv_lkp(c),
2789 p_formula_id => NULL,
2790 p_generate_db_items_flag => garn_calc_iv_dbi(c),
2791 p_warning_or_error => NULL);
2792
2793 hr_input_values.ins_3p_input_values(
2794 p_val_start_date => g_eff_start_date,
2795 p_val_end_date => g_eff_end_date,
2796 p_element_type_id => garn_ele_ids(2),
2797 p_primary_classification_id => g_ele_class_id,
2798 p_input_value_id => garn_calc_iv_ids(c),
2799 p_default_value => garn_calc_iv_dflt(c),
2800 p_max_value => NULL,
2801 p_min_value => NULL,
2802 p_warning_or_error_flag => NULL,
2803 p_input_value_name => garn_calc_iv_names(c),
2804 p_db_items_flag => garn_calc_iv_dbi(c),
2805 p_costable_type => NULL,
2806 p_hot_default_flag => 'N',
2807 p_business_group_id => p_bg_id,
2808 p_legislation_code => NULL,
2809 p_startup_mode => NULL);
2810
2811 ELSE
2812
2813 v_inpval_id := pay_db_pay_setup.create_input_value (
2814 p_element_name => garn_ele_names(2),
2815 p_name => garn_calc_iv_names(c),
2816 p_uom_code => garn_calc_iv_uom(c),
2817 p_mandatory_flag => garn_calc_iv_mand(c),
2818 p_generate_db_item_flag => garn_calc_iv_dbi(c),
2819 p_default_value => garn_calc_iv_dflt(c),
2820 p_min_value => NULL,
2821 p_max_value => NULL,
2822 p_warning_or_error => NULL,
2823 p_lookup_type => garn_calc_iv_lkp(c),
2824 p_formula_id => NULL,
2825 p_hot_default_flag => 'N',
2826 p_display_sequence => garn_calc_iv_seq(c),
2827 p_business_group_name => v_bg_name,
2828 p_effective_start_date => g_eff_start_date,
2829 p_effective_end_date => g_eff_end_date);
2830
2831 garn_calc_iv_ids(c) := v_inpval_id;
2832
2833 pay_template_ivs.chk_input_value(
2834 p_element_type_id => garn_ele_ids(2),
2835 p_legislation_code => g_template_leg_code,
2836 p_val_start_date => g_eff_start_date,
2837 p_val_end_date => g_eff_end_date,
2838 p_insert_update_flag => 'UPDATE',
2839 p_input_value_id => garn_calc_iv_ids(c),
2840 p_rowid => NULL,
2841 p_recurring_flag => 'N',
2842 p_mandatory_flag => garn_calc_iv_mand(c),
2843 p_hot_default_flag => 'N',
2844 p_standard_link_flag => 'N',
2845 p_classification_type => 'N',
2846 p_name => garn_calc_iv_names(c),
2847 p_uom => garn_calc_iv_uom(c),
2848 p_min_value => NULL,
2849 p_max_value => NULL,
2850 p_default_value => garn_calc_iv_dflt(c),
2851 p_lookup_type => garn_calc_iv_lkp(c),
2852 p_formula_id => NULL,
2853 p_generate_db_items_flag => garn_calc_iv_dbi(c),
2854 p_warning_or_error => NULL);
2855
2856 pay_template_ivs.ins_3p_input_values(
2857 p_val_start_date => g_eff_start_date,
2858 p_val_end_date => g_eff_end_date,
2859 p_element_type_id => garn_ele_ids(2),
2860 p_primary_classification_id => g_ele_class_id,
2861 p_input_value_id => garn_calc_iv_ids(c),
2862 p_default_value => garn_calc_iv_dflt(c),
2863 p_max_value => NULL,
2864 p_min_value => NULL,
2865 p_warning_or_error_flag => NULL,
2866 p_input_value_name => garn_calc_iv_names(c),
2867 p_db_items_flag => garn_calc_iv_dbi(c),
2868 p_costable_type => NULL,
2869 p_hot_default_flag => 'N',
2870 p_business_group_id => p_bg_id,
2871 p_legislation_code => NULL,
2872 p_startup_mode => NULL);
2873
2874 pay_template_ivs.new_input_value (
2875 p_element_type_id => garn_ele_ids(2),
2876 p_input_value_id => garn_calc_iv_ids(c),
2877 p_costed_flag => 'N',
2878 p_default_value => garn_calc_iv_dflt(c),
2879 p_max_value => NULL,
2880 p_min_value => NULL,
2881 p_warning_or_error => NULL);
2882
2883 END IF;
2884
2885 else
2886
2887 garn_calc_iv_ids(c) := already_exists;
2888
2889 end if;
2890
2891 END LOOP;
2892
2893 --
2894 -- Now create all verification element input values.
2895 --
2896 hr_utility.trace('Verif ivs = '||to_char(l_num_verif_ivs));
2897 FOR v in 1..l_num_verif_ivs LOOP
2898 hr_utility.trace('Verif iv # = '||to_char(v));
2899
2900 already_exists := hr_template_existence.iv_name_exists(
2901 p_ele_id => garn_ele_ids(3),
2902 p_bg_id => p_bg_id,
2903 p_iv_name => garn_verif_iv_names(v),
2904 p_eff_date => g_eff_start_date);
2905
2906 if already_exists = 0 then
2907
2908 /* 40.15 : Call new API to add input value over life of element if
2909 upgrade mode = Yes
2910 */
2911
2912 IF l_upgrade_mode = 'N' THEN
2913
2914 v_inpval_id := pay_db_pay_setup.create_input_value (
2915 p_element_name => garn_ele_names(3),
2916 p_name => garn_verif_iv_names(v),
2917 p_uom_code => garn_verif_iv_uom(v),
2918 p_mandatory_flag => garn_verif_iv_mand(v),
2919 p_generate_db_item_flag => garn_verif_iv_dbi(v),
2920 p_default_value => garn_verif_iv_dflt(v),
2921 p_min_value => NULL,
2922 p_max_value => NULL,
2923 p_warning_or_error => NULL,
2924 p_lookup_type => garn_verif_iv_lkp(v),
2925 p_formula_id => NULL,
2926 p_hot_default_flag => 'N',
2927 p_display_sequence => garn_verif_iv_seq(v),
2928 p_business_group_name => v_bg_name,
2929 p_effective_start_date => g_eff_start_date,
2930 p_effective_end_date => g_eff_end_date);
2931
2932 garn_verif_iv_ids(v) := v_inpval_id;
2933
2934 hr_input_values.chk_input_value(
2935 p_element_type_id => garn_ele_ids(3),
2936 p_legislation_code => g_template_leg_code,
2937 p_val_start_date => g_eff_start_date,
2938 p_val_end_date => g_eff_end_date,
2939 p_insert_update_flag => 'UPDATE',
2940 p_input_value_id => garn_verif_iv_ids(v),
2941 p_rowid => NULL,
2942 p_recurring_flag => 'N',
2943 p_mandatory_flag => garn_verif_iv_mand(v),
2944 p_hot_default_flag => 'N',
2945 p_standard_link_flag => 'N',
2946 p_classification_type => 'N',
2947 p_name => garn_verif_iv_names(v),
2948 p_uom => garn_verif_iv_uom(v),
2949 p_min_value => NULL,
2950 p_max_value => NULL,
2951 p_default_value => NULL,
2952 p_lookup_type => garn_verif_iv_dflt(v),
2953 p_formula_id => NULL,
2954 p_generate_db_items_flag => garn_verif_iv_dbi(v),
2955 p_warning_or_error => NULL);
2956
2957 hr_input_values.ins_3p_input_values(
2958 p_val_start_date => g_eff_start_date,
2959 p_val_end_date => g_eff_end_date,
2960 p_element_type_id => garn_ele_ids(3),
2961 p_primary_classification_id => g_ele_class_id,
2962 p_input_value_id => garn_verif_iv_ids(v),
2963 p_default_value => garn_verif_iv_dflt(v),
2964 p_max_value => NULL,
2965 p_min_value => NULL,
2966 p_warning_or_error_flag => NULL,
2967 p_input_value_name => garn_verif_iv_names(v),
2968 p_db_items_flag => garn_verif_iv_dbi(v),
2969 p_costable_type => NULL,
2970 p_hot_default_flag => 'N',
2971 p_business_group_id => p_bg_id,
2972 p_legislation_code => NULL,
2973 p_startup_mode => NULL);
2974
2975 ELSE
2976
2977 v_inpval_id := pay_db_pay_setup.create_input_value (
2978 p_element_name => garn_ele_names(3),
2979 p_name => garn_verif_iv_names(v),
2980 p_uom_code => garn_verif_iv_uom(v),
2981 p_mandatory_flag => garn_verif_iv_mand(v),
2982 p_generate_db_item_flag => garn_verif_iv_dbi(v),
2983 p_default_value => garn_verif_iv_dflt(v),
2984 p_min_value => NULL,
2985 p_max_value => NULL,
2986 p_warning_or_error => NULL,
2987 p_lookup_type => garn_verif_iv_lkp(v),
2988 p_formula_id => NULL,
2989 p_hot_default_flag => 'N',
2990 p_display_sequence => garn_verif_iv_seq(v),
2991 p_business_group_name => v_bg_name,
2992 p_effective_start_date => g_eff_start_date,
2993 p_effective_end_date => g_eff_end_date);
2994
2995 garn_verif_iv_ids(v) := v_inpval_id;
2996
2997 pay_template_ivs.chk_input_value(
2998 p_element_type_id => garn_ele_ids(3),
2999 p_legislation_code => g_template_leg_code,
3000 p_val_start_date => g_eff_start_date,
3001 p_val_end_date => g_eff_end_date,
3002 p_insert_update_flag => 'UPDATE',
3003 p_input_value_id => garn_verif_iv_ids(v),
3004 p_rowid => NULL,
3005 p_recurring_flag => 'N',
3006 p_mandatory_flag => garn_verif_iv_mand(v),
3007 p_hot_default_flag => 'N',
3008 p_standard_link_flag => 'N',
3009 p_classification_type => 'N',
3010 p_name => garn_verif_iv_names(v),
3011 p_uom => garn_verif_iv_uom(v),
3012 p_min_value => NULL,
3013 p_max_value => NULL,
3014 p_default_value => NULL,
3015 p_lookup_type => garn_verif_iv_dflt(v),
3016 p_formula_id => NULL,
3017 p_generate_db_items_flag => garn_verif_iv_dbi(v),
3018 p_warning_or_error => NULL);
3019
3020 pay_template_ivs.ins_3p_input_values(
3021 p_val_start_date => g_eff_start_date,
3022 p_val_end_date => g_eff_end_date,
3023 p_element_type_id => garn_ele_ids(3),
3024 p_primary_classification_id => g_ele_class_id,
3025 p_input_value_id => garn_verif_iv_ids(v),
3026 p_default_value => garn_verif_iv_dflt(v),
3027 p_max_value => NULL,
3028 p_min_value => NULL,
3029 p_warning_or_error_flag => NULL,
3030 p_input_value_name => garn_verif_iv_names(v),
3031 p_db_items_flag => garn_verif_iv_dbi(v),
3032 p_costable_type => NULL,
3033 p_hot_default_flag => 'N',
3034 p_business_group_id => p_bg_id,
3035 p_legislation_code => NULL,
3036 p_startup_mode => NULL);
3037
3038 pay_template_ivs.new_input_value (
3039 p_element_type_id => garn_ele_ids(3),
3040 p_input_value_id => garn_verif_iv_ids(v),
3041 p_costed_flag => 'N',
3042 p_default_value => garn_verif_iv_dflt(v),
3043 p_max_value => NULL,
3044 p_min_value => NULL,
3045 p_warning_or_error => NULL);
3046
3047 END IF;
3048
3049 else
3050
3051 garn_verif_iv_ids(v) := already_exists;
3052
3053 end if;
3054
3055 END LOOP;
3056
3057 --
3058 -- Now create all special inputs element input values.
3059 --
3060 hr_utility.trace('SIV ivs = '||to_char(l_num_si_ivs));
3061 FOR siv in 1..l_num_si_ivs LOOP
3062 hr_utility.trace('SIV iv # = '||to_char(siv));
3063 already_exists := hr_template_existence.iv_name_exists(
3064 p_ele_id => garn_ele_ids(4),
3065 p_bg_id => p_bg_id,
3066 p_iv_name => garn_si_iv_names(siv),
3067 p_eff_date => g_eff_start_date);
3068
3069 if already_exists = 0 then
3070
3071 /* 40.15 : Call new API to add input value over life of element if
3072 upgrade mode = Yes
3073 */
3074
3075 IF l_upgrade_mode = 'N' THEN
3076
3077 v_inpval_id := pay_db_pay_setup.create_input_value (
3078 p_element_name => garn_ele_names(4),
3079 p_name => garn_si_iv_names(siv),
3080 p_uom_code => garn_si_iv_uom(siv),
3081 p_mandatory_flag => garn_si_iv_mand(siv),
3082 p_generate_db_item_flag => garn_si_iv_dbi(siv),
3083 p_default_value => garn_si_iv_dflt(siv),
3084 p_min_value => NULL,
3085 p_max_value => NULL,
3086 p_warning_or_error => NULL,
3087 p_lookup_type => garn_si_iv_lkp(siv),
3088 p_formula_id => NULL,
3089 p_hot_default_flag => 'N',
3090 p_display_sequence => garn_si_iv_seq(siv),
3091 p_business_group_name => v_bg_name,
3092 p_effective_start_date => g_eff_start_date,
3093 p_effective_end_date => g_eff_end_date);
3094
3095 garn_si_iv_ids(siv) := v_inpval_id;
3096
3097 hr_input_values.chk_input_value(
3098 p_element_type_id => garn_ele_ids(4),
3099 p_legislation_code => g_template_leg_code,
3100 p_val_start_date => g_eff_start_date,
3101 p_val_end_date => g_eff_end_date,
3102 p_insert_update_flag => 'UPDATE',
3103 p_input_value_id => garn_si_iv_ids(siv),
3104 p_rowid => NULL,
3105 p_recurring_flag => 'N',
3106 p_mandatory_flag => garn_si_iv_mand(siv),
3107 p_hot_default_flag => 'N',
3108 p_standard_link_flag => 'N',
3109 p_classification_type => 'N',
3110 p_name => garn_si_iv_names(siv),
3111 p_uom => garn_si_iv_uom(siv),
3112 p_min_value => NULL,
3113 p_max_value => NULL,
3114 p_default_value => NULL,
3115 p_lookup_type => garn_si_iv_dflt(siv),
3116 p_formula_id => NULL,
3117 p_generate_db_items_flag => garn_si_iv_dbi(siv),
3118 p_warning_or_error => NULL);
3119
3120 hr_input_values.ins_3p_input_values(
3121 p_val_start_date => g_eff_start_date,
3122 p_val_end_date => g_eff_end_date,
3123 p_element_type_id => garn_ele_ids(4),
3124 p_primary_classification_id => g_ele_class_id,
3125 p_input_value_id => garn_si_iv_ids(siv),
3126 p_default_value => garn_si_iv_dflt(siv),
3127 p_max_value => NULL,
3128 p_min_value => NULL,
3129 p_warning_or_error_flag => NULL,
3130 p_input_value_name => garn_si_iv_names(siv),
3131 p_db_items_flag => garn_si_iv_dbi(siv),
3132 p_costable_type => NULL,
3133 p_hot_default_flag => 'N',
3134 p_business_group_id => p_bg_id,
3135 p_legislation_code => NULL,
3136 p_startup_mode => NULL);
3137
3138 ELSE
3139
3140 v_inpval_id := pay_db_pay_setup.create_input_value (
3141 p_element_name => garn_ele_names(4),
3142 p_name => garn_si_iv_names(siv),
3143 p_uom_code => garn_si_iv_uom(siv),
3144 p_mandatory_flag => garn_si_iv_mand(siv),
3145 p_generate_db_item_flag => garn_si_iv_dbi(siv),
3146 p_default_value => garn_si_iv_dflt(siv),
3147 p_min_value => NULL,
3148 p_max_value => NULL,
3149 p_warning_or_error => NULL,
3150 p_lookup_type => garn_si_iv_lkp(siv),
3151 p_formula_id => NULL,
3152 p_hot_default_flag => 'N',
3153 p_display_sequence => garn_si_iv_seq(siv),
3154 p_business_group_name => v_bg_name,
3155 p_effective_start_date => g_eff_start_date,
3156 p_effective_end_date => g_eff_end_date);
3157
3158 garn_si_iv_ids(siv) := v_inpval_id;
3159
3160 pay_template_ivs.chk_input_value(
3161 p_element_type_id => garn_ele_ids(4),
3162 p_legislation_code => g_template_leg_code,
3163 p_val_start_date => g_eff_start_date,
3164 p_val_end_date => g_eff_end_date,
3165 p_insert_update_flag => 'UPDATE',
3166 p_input_value_id => garn_si_iv_ids(siv),
3167 p_rowid => NULL,
3168 p_recurring_flag => 'N',
3169 p_mandatory_flag => garn_si_iv_mand(siv),
3170 p_hot_default_flag => 'N',
3171 p_standard_link_flag => 'N',
3172 p_classification_type => 'N',
3173 p_name => garn_si_iv_names(siv),
3174 p_uom => garn_si_iv_uom(siv),
3175 p_min_value => NULL,
3176 p_max_value => NULL,
3177 p_default_value => NULL,
3178 p_lookup_type => garn_si_iv_dflt(siv),
3179 p_formula_id => NULL,
3180 p_generate_db_items_flag => garn_si_iv_dbi(siv),
3181 p_warning_or_error => NULL);
3182
3183 pay_template_ivs.ins_3p_input_values(
3184 p_val_start_date => g_eff_start_date,
3185 p_val_end_date => g_eff_end_date,
3186 p_element_type_id => garn_ele_ids(4),
3187 p_primary_classification_id => g_ele_class_id,
3188 p_input_value_id => garn_si_iv_ids(siv),
3189 p_default_value => garn_si_iv_dflt(siv),
3190 p_max_value => NULL,
3191 p_min_value => NULL,
3192 p_warning_or_error_flag => NULL,
3193 p_input_value_name => garn_si_iv_names(siv),
3194 p_db_items_flag => garn_si_iv_dbi(siv),
3195 p_costable_type => NULL,
3196 p_hot_default_flag => 'N',
3197 p_business_group_id => p_bg_id,
3198 p_legislation_code => NULL,
3199 p_startup_mode => NULL);
3200
3201 pay_template_ivs.new_input_value (
3202 p_element_type_id => garn_ele_ids(4),
3203 p_input_value_id => garn_si_iv_ids(siv),
3204 p_costed_flag => 'N',
3205 p_default_value => garn_si_iv_dflt(siv),
3206 p_max_value => NULL,
3207 p_min_value => NULL,
3208 p_warning_or_error => NULL);
3209
3210 END IF;
3211
3212 else
3213
3214 garn_si_iv_ids(siv) := already_exists;
3215
3216 end if;
3217
3218 END LOOP;
3219
3220
3221 --
3222 -- Now create all special features element input values.
3223 --
3224 hr_utility.trace('SFV ivs = '||to_char(l_num_sf_ivs));
3225 FOR sfv in 1..l_num_sf_ivs LOOP
3226 hr_utility.trace('SFV iv # = '||to_char(sfv));
3227
3228 already_exists := hr_template_existence.iv_name_exists(
3229 p_ele_id => garn_ele_ids(5),
3230 p_bg_id => p_bg_id,
3231 p_iv_name => garn_sf_iv_names(sfv),
3232 p_eff_date => g_eff_start_date);
3233
3234 if already_exists = 0 then
3235
3236 /* 40.15 : Call new API to add input value over life of element if
3237 upgrade mode = Yes
3238 */
3239
3240 IF l_upgrade_mode = 'N' THEN
3241
3242 v_inpval_id := pay_db_pay_setup.create_input_value (
3243 p_element_name => garn_ele_names(5),
3244 p_name => garn_sf_iv_names(sfv),
3245 p_uom_code => garn_sf_iv_uom(sfv),
3246 p_mandatory_flag => garn_sf_iv_mand(sfv),
3247 p_generate_db_item_flag => garn_sf_iv_dbi(sfv),
3248 p_default_value => garn_sf_iv_dflt(sfv),
3249 p_min_value => NULL,
3250 p_max_value => NULL,
3251 p_warning_or_error => NULL,
3252 p_lookup_type => garn_sf_iv_lkp(sfv),
3253 p_formula_id => NULL,
3254 p_hot_default_flag => 'N',
3255 p_display_sequence => garn_sf_iv_seq(sfv),
3256 p_business_group_name => v_bg_name,
3257 p_effective_start_date => g_eff_start_date,
3258 p_effective_end_date => g_eff_end_date);
3259
3260 garn_sf_iv_ids(sfv) := v_inpval_id;
3261
3262 hr_input_values.chk_input_value(
3263 p_element_type_id => garn_ele_ids(5),
3264 p_legislation_code => g_template_leg_code,
3265 p_val_start_date => g_eff_start_date,
3266 p_val_end_date => g_eff_end_date,
3267 p_insert_update_flag => 'UPDATE',
3268 p_input_value_id => garn_sf_iv_ids(sfv),
3269 p_rowid => NULL,
3270 p_recurring_flag => 'N',
3271 p_mandatory_flag => garn_sf_iv_mand(sfv),
3272 p_hot_default_flag => 'N',
3273 p_standard_link_flag => 'N',
3274 p_classification_type => 'N',
3275 p_name => garn_sf_iv_names(sfv),
3276 p_uom => garn_sf_iv_uom(sfv),
3277 p_min_value => NULL,
3278 p_max_value => NULL,
3279 p_default_value => NULL,
3280 p_lookup_type => garn_sf_iv_dflt(sfv),
3281 p_formula_id => NULL,
3282 p_generate_db_items_flag => garn_sf_iv_dbi(sfv),
3283 p_warning_or_error => NULL);
3284
3285 hr_input_values.ins_3p_input_values(
3286 p_val_start_date => g_eff_start_date,
3287 p_val_end_date => g_eff_end_date,
3288 p_element_type_id => garn_ele_ids(5),
3289 p_primary_classification_id => g_ele_class_id,
3290 p_input_value_id => garn_sf_iv_ids(sfv),
3291 p_default_value => garn_sf_iv_dflt(sfv),
3292 p_max_value => NULL,
3293 p_min_value => NULL,
3294 p_warning_or_error_flag => NULL,
3295 p_input_value_name => garn_sf_iv_names(sfv),
3296 p_db_items_flag => garn_sf_iv_dbi(sfv),
3297 p_costable_type => NULL,
3298 p_hot_default_flag => 'N',
3299 p_business_group_id => p_bg_id,
3300 p_legislation_code => NULL,
3301 p_startup_mode => NULL);
3302
3303 ELSE
3304
3305 v_inpval_id := pay_db_pay_setup.create_input_value (
3306 p_element_name => garn_ele_names(5),
3307 p_name => garn_sf_iv_names(sfv),
3308 p_uom_code => garn_sf_iv_uom(sfv),
3309 p_mandatory_flag => garn_sf_iv_mand(sfv),
3310 p_generate_db_item_flag => garn_sf_iv_dbi(sfv),
3311 p_default_value => garn_sf_iv_dflt(sfv),
3312 p_min_value => NULL,
3313 p_max_value => NULL,
3314 p_warning_or_error => NULL,
3315 p_lookup_type => garn_sf_iv_lkp(sfv),
3316 p_formula_id => NULL,
3317 p_hot_default_flag => 'N',
3318 p_display_sequence => garn_sf_iv_seq(sfv),
3319 p_business_group_name => v_bg_name,
3320 p_effective_start_date => g_eff_start_date,
3321 p_effective_end_date => g_eff_end_date);
3322
3323 garn_sf_iv_ids(sfv) := v_inpval_id;
3324
3325 pay_template_ivs.chk_input_value(
3326 p_element_type_id => garn_ele_ids(5),
3327 p_legislation_code => g_template_leg_code,
3328 p_val_start_date => g_eff_start_date,
3329 p_val_end_date => g_eff_end_date,
3330 p_insert_update_flag => 'UPDATE',
3331 p_input_value_id => garn_sf_iv_ids(sfv),
3332 p_rowid => NULL,
3333 p_recurring_flag => 'N',
3334 p_mandatory_flag => garn_sf_iv_mand(sfv),
3335 p_hot_default_flag => 'N',
3336 p_standard_link_flag => 'N',
3337 p_classification_type => 'N',
3338 p_name => garn_sf_iv_names(sfv),
3339 p_uom => garn_sf_iv_uom(sfv),
3340 p_min_value => NULL,
3341 p_max_value => NULL,
3342 p_default_value => NULL,
3343 p_lookup_type => garn_sf_iv_dflt(sfv),
3344 p_formula_id => NULL,
3345 p_generate_db_items_flag => garn_sf_iv_dbi(sfv),
3346 p_warning_or_error => NULL);
3347
3348 pay_template_ivs.ins_3p_input_values(
3349 p_val_start_date => g_eff_start_date,
3350 p_val_end_date => g_eff_end_date,
3351 p_element_type_id => garn_ele_ids(5),
3352 p_primary_classification_id => g_ele_class_id,
3353 p_input_value_id => garn_sf_iv_ids(sfv),
3354 p_default_value => garn_sf_iv_dflt(sfv),
3355 p_max_value => NULL,
3356 p_min_value => NULL,
3357 p_warning_or_error_flag => NULL,
3358 p_input_value_name => garn_sf_iv_names(sfv),
3359 p_db_items_flag => garn_sf_iv_dbi(sfv),
3360 p_costable_type => NULL,
3361 p_hot_default_flag => 'N',
3362 p_business_group_id => p_bg_id,
3363 p_legislation_code => NULL,
3364 p_startup_mode => NULL);
3365
3366 pay_template_ivs.new_input_value (
3367 p_element_type_id => garn_ele_ids(5),
3368 p_input_value_id => garn_sf_iv_ids(sfv),
3369 p_costed_flag => 'N',
3370 p_default_value => garn_sf_iv_dflt(sfv),
3371 p_max_value => NULL,
3372 p_min_value => NULL,
3373 p_warning_or_error => NULL);
3374
3375 END IF;
3376
3377 else
3378
3379 garn_sf_iv_ids(sfv) := already_exists;
3380
3381 end if;
3382
3383 END LOOP;
3384
3385 --
3386 -- Now create all fee element input values.
3387 --
3388 hr_utility.trace('Fee ivs = '||to_char(l_num_fee_ivs));
3389 FOR lfee in 1..l_num_fee_ivs LOOP
3390 hr_utility.trace('Fees iv # = '||to_char(lfee));
3391 already_exists := hr_template_existence.iv_name_exists(
3392 p_ele_id => garn_ele_ids(6),
3393 p_bg_id => p_bg_id,
3394 p_iv_name => garn_fee_iv_names(lfee),
3395 p_eff_date => g_eff_start_date);
3396
3397 if already_exists = 0 then
3398
3399 /* 40.15 : Call new API to add input value over life of element if
3400 upgrade mode = Yes
3401 */
3402
3403 IF l_upgrade_mode = 'N' THEN
3404
3405 v_inpval_id := pay_db_pay_setup.create_input_value (
3406 p_element_name => garn_ele_names(6),
3407 p_name => garn_fee_iv_names(lfee),
3408 p_uom_code => garn_fee_iv_uom(lfee),
3409 p_mandatory_flag => garn_fee_iv_mand(lfee),
3410 p_generate_db_item_flag => garn_fee_iv_dbi(lfee),
3411 p_default_value => garn_fee_iv_dflt(lfee),
3412 p_min_value => NULL,
3413 p_max_value => NULL,
3414 p_warning_or_error => NULL,
3415 p_lookup_type => garn_fee_iv_lkp(lfee),
3416 p_formula_id => NULL,
3417 p_hot_default_flag => 'N',
3418 p_display_sequence => garn_fee_iv_seq(lfee),
3419 p_business_group_name => v_bg_name,
3420 p_effective_start_date => g_eff_start_date,
3421 p_effective_end_date => g_eff_end_date);
3422
3423 garn_fee_iv_ids(lfee) := v_inpval_id;
3424
3425 hr_input_values.chk_input_value(
3426 p_element_type_id => garn_ele_ids(6),
3427 p_legislation_code => g_template_leg_code,
3428 p_val_start_date => g_eff_start_date,
3429 p_val_end_date => g_eff_end_date,
3430 p_insert_update_flag => 'UPDATE',
3431 p_input_value_id => garn_fee_iv_ids(lfee),
3432 p_rowid => NULL,
3433 p_recurring_flag => 'N',
3434 p_mandatory_flag => garn_fee_iv_mand(lfee),
3435 p_hot_default_flag => 'N',
3436 p_standard_link_flag => 'N',
3437 p_classification_type => 'N',
3438 p_name => garn_fee_iv_names(lfee),
3439 p_uom => garn_fee_iv_uom(lfee),
3440 p_min_value => NULL,
3441 p_max_value => NULL,
3442 p_default_value => NULL,
3443 p_lookup_type => garn_fee_iv_dflt(lfee),
3444 p_formula_id => NULL,
3445 p_generate_db_items_flag => garn_fee_iv_dbi(lfee),
3446 p_warning_or_error => NULL);
3447
3448 hr_input_values.ins_3p_input_values(
3449 p_val_start_date => g_eff_start_date,
3450 p_val_end_date => g_eff_end_date,
3451 p_element_type_id => garn_ele_ids(6),
3452 p_primary_classification_id => g_ele_class_id,
3453 p_input_value_id => garn_fee_iv_ids(lfee),
3454 p_default_value => garn_fee_iv_dflt(lfee),
3455 p_max_value => NULL,
3456 p_min_value => NULL,
3457 p_warning_or_error_flag => NULL,
3458 p_input_value_name => garn_fee_iv_names(lfee),
3459 p_db_items_flag => garn_fee_iv_dbi(lfee),
3460 p_costable_type => NULL,
3461 p_hot_default_flag => 'N',
3462 p_business_group_id => p_bg_id,
3463 p_legislation_code => NULL,
3464 p_startup_mode => NULL);
3465
3466 ELSE
3467
3468 v_inpval_id := pay_db_pay_setup.create_input_value (
3469 p_element_name => garn_ele_names(6),
3470 p_name => garn_fee_iv_names(lfee),
3471 p_uom_code => garn_fee_iv_uom(lfee),
3472 p_mandatory_flag => garn_fee_iv_mand(lfee),
3473 p_generate_db_item_flag => garn_fee_iv_dbi(lfee),
3474 p_default_value => garn_fee_iv_dflt(lfee),
3475 p_min_value => NULL,
3476 p_max_value => NULL,
3477 p_warning_or_error => NULL,
3478 p_lookup_type => garn_fee_iv_lkp(lfee),
3479 p_formula_id => NULL,
3480 p_hot_default_flag => 'N',
3481 p_display_sequence => garn_fee_iv_seq(lfee),
3482 p_business_group_name => v_bg_name,
3483 p_effective_start_date => g_eff_start_date,
3484 p_effective_end_date => g_eff_end_date);
3485
3486 garn_fee_iv_ids(lfee) := v_inpval_id;
3487
3488 pay_template_ivs.chk_input_value(
3489 p_element_type_id => garn_ele_ids(6),
3490 p_legislation_code => g_template_leg_code,
3491 p_val_start_date => g_eff_start_date,
3492 p_val_end_date => g_eff_end_date,
3493 p_insert_update_flag => 'UPDATE',
3494 p_input_value_id => garn_fee_iv_ids(lfee),
3495 p_rowid => NULL,
3496 p_recurring_flag => 'N',
3497 p_mandatory_flag => garn_fee_iv_mand(lfee),
3498 p_hot_default_flag => 'N',
3499 p_standard_link_flag => 'N',
3500 p_classification_type => 'N',
3501 p_name => garn_fee_iv_names(lfee),
3502 p_uom => garn_fee_iv_uom(lfee),
3503 p_min_value => NULL,
3504 p_max_value => NULL,
3505 p_default_value => NULL,
3506 p_lookup_type => garn_fee_iv_dflt(lfee),
3507 p_formula_id => NULL,
3508 p_generate_db_items_flag => garn_fee_iv_dbi(lfee),
3509 p_warning_or_error => NULL);
3510
3511 pay_template_ivs.ins_3p_input_values(
3512 p_val_start_date => g_eff_start_date,
3513 p_val_end_date => g_eff_end_date,
3514 p_element_type_id => garn_ele_ids(6),
3515 p_primary_classification_id => g_ele_class_id,
3516 p_input_value_id => garn_fee_iv_ids(lfee),
3517 p_default_value => garn_fee_iv_dflt(lfee),
3518 p_max_value => NULL,
3519 p_min_value => NULL,
3520 p_warning_or_error_flag => NULL,
3521 p_input_value_name => garn_fee_iv_names(lfee),
3522 p_db_items_flag => garn_fee_iv_dbi(lfee),
3523 p_costable_type => NULL,
3524 p_hot_default_flag => 'N',
3525 p_business_group_id => p_bg_id,
3526 p_legislation_code => NULL,
3527 p_startup_mode => NULL);
3528
3529 pay_template_ivs.new_input_value (
3530 p_element_type_id => garn_ele_ids(6),
3531 p_input_value_id => garn_fee_iv_ids(lfee),
3532 p_costed_flag => 'N',
3533 p_default_value => garn_fee_iv_dflt(lfee),
3534 p_max_value => NULL,
3535 p_min_value => NULL,
3536 p_warning_or_error => NULL);
3537
3538 END IF;
3539
3540 else
3541
3542 garn_fee_iv_ids(lfee) := already_exists;
3543
3544 end if;
3545
3546 END LOOP;
3547
3548 -- Now create all verifier priority element input values.
3549 --
3550 hr_utility.trace('VP ivs = '||to_char(l_num_vp_ivs));
3551 FOR lvp in 1..l_num_vp_ivs LOOP
3552 hr_utility.trace('VP iv # = '||to_char(lvp));
3553 already_exists := hr_template_existence.iv_name_exists(
3554 p_ele_id => garn_ele_ids(7),
3555 p_bg_id => p_bg_id,
3556 p_iv_name => garn_vp_iv_names(lvp),
3557 p_eff_date => g_eff_start_date);
3558 if already_exists = 0 then
3559
3560 /* 40.15 : Call new API to add input value over life of element if
3561 upgrade mode = Yes
3562 */
3563
3564 IF l_upgrade_mode = 'N' THEN
3565
3566 v_inpval_id := pay_db_pay_setup.create_input_value (
3567 p_element_name => garn_ele_names(7),
3568 p_name => garn_vp_iv_names(lvp),
3569 p_uom_code => garn_vp_iv_uom(lvp),
3570 p_mandatory_flag => garn_vp_iv_mand(lvp),
3571 p_generate_db_item_flag => garn_vp_iv_dbi(lvp),
3572 p_default_value => garn_vp_iv_dflt(lvp),
3573 p_min_value => NULL,
3574 p_max_value => NULL,
3575 p_warning_or_error => NULL,
3576 p_lookup_type => garn_vp_iv_lkp(lvp),
3577 p_formula_id => NULL,
3578 p_hot_default_flag => 'N',
3579 p_display_sequence => garn_vp_iv_seq(lvp),
3580 p_business_group_name => v_bg_name,
3581 p_effective_start_date => g_eff_start_date,
3582 p_effective_end_date => g_eff_end_date);
3583
3584 garn_vp_iv_ids(lvp) := v_inpval_id;
3585
3586 hr_input_values.chk_input_value(
3587 p_element_type_id => garn_ele_ids(7),
3588 p_legislation_code => g_template_leg_code,
3589 p_val_start_date => g_eff_start_date,
3590 p_val_end_date => g_eff_end_date,
3591 p_insert_update_flag => 'UPDATE',
3592 p_input_value_id => garn_vp_iv_ids(lvp),
3593 p_rowid => NULL,
3594 p_recurring_flag => 'N',
3595 p_mandatory_flag => garn_vp_iv_mand(lvp),
3596 p_hot_default_flag => 'N',
3597 p_standard_link_flag => 'N',
3598 p_classification_type => 'N',
3599 p_name => garn_vp_iv_names(lvp),
3600 p_uom => garn_vp_iv_uom(lvp),
3601 p_min_value => NULL,
3602 p_max_value => NULL,
3603 p_default_value => NULL,
3604 p_lookup_type => garn_vp_iv_dflt(lvp),
3605 p_formula_id => NULL,
3606 p_generate_db_items_flag => garn_vp_iv_dbi(lvp),
3607 p_warning_or_error => NULL);
3608
3609 hr_input_values.ins_3p_input_values(
3610 p_val_start_date => g_eff_start_date,
3611 p_val_end_date => g_eff_end_date,
3612 p_element_type_id => garn_ele_ids(7),
3613 p_primary_classification_id => g_ele_class_id,
3614 p_input_value_id => garn_vp_iv_ids(lvp),
3615 p_default_value => garn_vp_iv_dflt(lvp),
3616 p_max_value => NULL,
3617 p_min_value => NULL,
3618 p_warning_or_error_flag => NULL,
3619 p_input_value_name => garn_vp_iv_names(lvp),
3620 p_db_items_flag => garn_vp_iv_dbi(lvp),
3621 p_costable_type => NULL,
3622 p_hot_default_flag => 'N',
3623 p_business_group_id => p_bg_id,
3624 p_legislation_code => NULL,
3625 p_startup_mode => NULL);
3626
3627 ELSE
3628
3629 v_inpval_id := pay_db_pay_setup.create_input_value (
3630 p_element_name => garn_ele_names(7),
3631 p_name => garn_vp_iv_names(lvp),
3632 p_uom_code => garn_vp_iv_uom(lvp),
3633 p_mandatory_flag => garn_vp_iv_mand(lvp),
3634 p_generate_db_item_flag => garn_vp_iv_dbi(lvp),
3635 p_default_value => garn_vp_iv_dflt(lvp),
3636 p_min_value => NULL,
3637 p_max_value => NULL,
3638 p_warning_or_error => NULL,
3639 p_lookup_type => garn_vp_iv_lkp(lvp),
3640 p_formula_id => NULL,
3641 p_hot_default_flag => 'N',
3642 p_display_sequence => garn_vp_iv_seq(lvp),
3643 p_business_group_name => v_bg_name,
3644 p_effective_start_date => g_eff_start_date,
3645 p_effective_end_date => g_eff_end_date);
3646
3647 garn_vp_iv_ids(lvp) := v_inpval_id;
3648
3649 pay_template_ivs.chk_input_value(
3650 p_element_type_id => garn_ele_ids(7),
3651 p_legislation_code => g_template_leg_code,
3652 p_val_start_date => g_eff_start_date,
3653 p_val_end_date => g_eff_end_date,
3654 p_insert_update_flag => 'UPDATE',
3655 p_input_value_id => garn_vp_iv_ids(lvp),
3656 p_rowid => NULL,
3657 p_recurring_flag => 'N',
3658 p_mandatory_flag => garn_vp_iv_mand(lvp),
3659 p_hot_default_flag => 'N',
3660 p_standard_link_flag => 'N',
3661 p_classification_type => 'N',
3662 p_name => garn_vp_iv_names(lvp),
3663 p_uom => garn_vp_iv_uom(lvp),
3664 p_min_value => NULL,
3665 p_max_value => NULL,
3666 p_default_value => NULL,
3667 p_lookup_type => garn_vp_iv_dflt(lvp),
3668 p_formula_id => NULL,
3669 p_generate_db_items_flag => garn_vp_iv_dbi(lvp),
3670 p_warning_or_error => NULL);
3671
3672 pay_template_ivs.ins_3p_input_values(
3673 p_val_start_date => g_eff_start_date,
3674 p_val_end_date => g_eff_end_date,
3675 p_element_type_id => garn_ele_ids(7),
3676 p_primary_classification_id => g_ele_class_id,
3677 p_input_value_id => garn_vp_iv_ids(lvp),
3678 p_default_value => garn_vp_iv_dflt(lvp),
3679 p_max_value => NULL,
3680 p_min_value => NULL,
3681 p_warning_or_error_flag => NULL,
3682 p_input_value_name => garn_vp_iv_names(lvp),
3683 p_db_items_flag => garn_vp_iv_dbi(lvp),
3684 p_costable_type => NULL,
3685 p_hot_default_flag => 'N',
3686 p_business_group_id => p_bg_id,
3687 p_legislation_code => NULL,
3688 p_startup_mode => NULL);
3689
3690 pay_template_ivs.new_input_value (
3691 p_element_type_id => garn_ele_ids(7),
3692 p_input_value_id => garn_vp_iv_ids(lvp),
3693 p_costed_flag => 'N',
3694 p_default_value => garn_vp_iv_dflt(lvp),
3695 p_max_value => NULL,
3696 p_min_value => NULL,
3697 p_warning_or_error => NULL);
3698
3699 END IF;
3700
3701 else
3702
3703 garn_vp_iv_ids(lvp) := already_exists;
3704
3705 end if;
3706
3707 END LOOP;
3708 --
3709 -- Now insert appropriate formula_result_rules for this element, now that
3710 -- all elements and input values are created.
3711 --
3712
3713 -- Formula result rule param settings:
3714 garn_base_frr_name(1) := 'AMT';
3715 garn_base_frr_type(1) := 'I';
3716 garn_base_frr_ele_id(1) := garn_ele_ids(2);
3717 garn_base_frr_iv_id(1) := garn_calc_iv_ids(1);
3718 garn_base_frr_severity(1) := NULL;
3719
3720 garn_base_frr_name(2) := 'OWED';
3721 garn_base_frr_type(2) := 'I';
3722 garn_base_frr_ele_id(2) := garn_ele_ids(2);
3723 garn_base_frr_iv_id(2) := garn_calc_iv_ids(3);
3724 garn_base_frr_severity(2) := NULL;
3725
3726 garn_base_frr_name(3) := 'SERVED';
3727 garn_base_frr_type(3) := 'I';
3728 garn_base_frr_ele_id(3) := garn_ele_ids(2);
3729 garn_base_frr_iv_id(3) := garn_calc_iv_ids(4);
3730 garn_base_frr_severity(3) := NULL;
3731
3732 garn_base_frr_name(4) := 'ARREARS_OVERRIDE';
3733 garn_base_frr_type(4) := 'I';
3734 garn_base_frr_ele_id(4) := garn_ele_ids(2);
3735 garn_base_frr_iv_id(4) := garn_calc_iv_ids(5);
3736 garn_base_frr_severity(4) := NULL;
3737
3738 garn_base_frr_name(5) := 'NUM_DEPS';
3739 garn_base_frr_type(5) := 'I';
3740 garn_base_frr_ele_id(5) := garn_ele_ids(2);
3741 garn_base_frr_iv_id(5) := garn_calc_iv_ids(7);
3742 garn_base_frr_severity(5) := NULL;
3743
3744 garn_base_frr_name(6) := 'FIL_STAT';
3745 garn_base_frr_type(6) := 'I';
3746 garn_base_frr_ele_id(6) := garn_ele_ids(2);
3747 garn_base_frr_iv_id(6) := garn_calc_iv_ids(8);
3748 garn_base_frr_severity(6) := NULL;
3749
3750 garn_base_frr_name(7) := 'ALLOWS';
3751 garn_base_frr_type(7) := 'I';
3752 garn_base_frr_ele_id(7) := garn_ele_ids(2);
3753 garn_base_frr_iv_id(7) := garn_calc_iv_ids(9);
3754 garn_base_frr_severity(7) := NULL;
3755
3756 garn_base_frr_name(8) := 'DEDN_OVERRIDE';
3757 garn_base_frr_type(8) := 'I';
3758 garn_base_frr_ele_id(8) := garn_ele_ids(2);
3759 garn_base_frr_iv_id(8) := garn_calc_iv_ids(10);
3760 garn_base_frr_severity(8) := NULL;
3761
3762 garn_base_frr_name(9) := 'ADDITIONAL_AMOUNT_BALANCE';
3763 garn_base_frr_type(9) := 'I';
3764 garn_base_frr_ele_id(9) := garn_ele_ids(2);
3765 garn_base_frr_iv_id(9) := garn_calc_iv_ids(11);
3766 garn_base_frr_severity(9) := NULL;
3767
3768 garn_base_frr_name(10) := 'REPLACEMENT_AMOUNT_BALANCE';
3769 garn_base_frr_type(10) := 'I';
3770 garn_base_frr_ele_id(10) := garn_ele_ids(2);
3771 garn_base_frr_iv_id(10) := garn_calc_iv_ids(12);
3772 garn_base_frr_severity(10) := NULL;
3773
3774 garn_base_frr_name(11) := 'ARREARS_AMOUNT_BALANCE';
3775 garn_base_frr_type(11) := 'I';
3776 garn_base_frr_ele_id(11) := garn_ele_ids(2);
3777 garn_base_frr_iv_id(11) := garn_calc_iv_ids(13);
3778 garn_base_frr_severity(11) := NULL;
3779
3780 garn_base_frr_name(12) := 'PRIMARY_AMOUNT_BALANCE';
3781 garn_base_frr_type(12) := 'I';
3782 garn_base_frr_ele_id(12) := garn_ele_ids(2);
3783 garn_base_frr_iv_id(12) := garn_calc_iv_ids(14);
3784 garn_base_frr_severity(12) := NULL;
3785
3786 garn_base_frr_name(13) := 'JURIS_CODE';
3787 garn_base_frr_type(13) := 'I';
3788 garn_base_frr_ele_id(13) := garn_ele_ids(2);
3789 garn_base_frr_iv_id(13) := garn_calc_iv_ids(2);
3790 garn_base_frr_severity(13) := NULL;
3791
3792 garn_base_frr_name(14) := 'PCT';
3793 garn_base_frr_type(14) := 'I';
3794 garn_base_frr_ele_id(14) := garn_ele_ids(2);
3795 garn_base_frr_iv_id(14) := garn_calc_iv_ids(15);
3796 garn_base_frr_severity(14) := NULL;
3797
3798 garn_base_frr_name(15) := 'VOLDEDNS_AT_WRIT';
3799 garn_base_frr_type(15) := 'I';
3800 garn_base_frr_ele_id(15) := garn_ele_ids(5);
3801 garn_base_frr_iv_id(15) := garn_SF_IV_IDS(8);
3802 garn_base_frr_severity(15) := NULL;
3803
3804 garn_base_frr_name(16) := 'SUPP_OTHER_FAM';
3805 garn_base_frr_type(16) := 'I';
3806 garn_base_frr_ele_id(16) := garn_ele_ids(2);
3807 garn_base_frr_iv_id(16) := garn_calc_iv_ids(16);
3808 garn_base_frr_severity(16) := NULL;
3809
3810 garn_base_frr_name(17) := 'CALC_SUBPRIO';
3811 garn_base_frr_type(17) := 'O';
3812 garn_base_frr_ele_id(17) := garn_ele_ids(2);
3813 garn_base_frr_iv_id(17) := NULL;
3814 garn_base_frr_severity(17) := NULL;
3815
3816 garn_base_frr_name(18) := 'ARREARS_DATE';
3817 garn_base_frr_type(18) := 'I';
3818 garn_base_frr_ele_id(18) := garn_ele_ids(2);
3819 garn_base_frr_iv_id(18) := garn_calc_iv_ids(6);
3820 garn_base_frr_severity(18) := NULL;
3821
3822 garn_base_frr_name(19) := 'MONTH_CAP_AMT';
3823 garn_base_frr_type(19) := 'I';
3824 garn_base_frr_ele_id(19) := garn_ele_ids(2);
3825 garn_base_frr_iv_id(19) := garn_calc_iv_ids(17);
3826 garn_base_frr_severity(19) := NULL;
3827
3828 garn_base_frr_name(20) := 'MTD_BAL';
3829 garn_base_frr_type(20) := 'I';
3830 garn_base_frr_ele_id(20) := garn_ele_ids(2);
3831 garn_base_frr_iv_id(20) := garn_calc_iv_ids(18);
3832 garn_base_frr_severity(20) := NULL;
3833
3834 garn_base_frr_name(21) := 'EXEMPT_AMT';
3835 garn_base_frr_type(21) := 'I';
3836 garn_base_frr_ele_id(21) := garn_ele_ids(2);
3837 garn_base_frr_iv_id(21) := garn_calc_iv_ids(19);
3838 garn_base_frr_severity(21) := NULL;
3839
3840 garn_base_frr_name(22) := 'PTD_CAP_AMT';
3841 garn_base_frr_type(22) := 'I';
3842 garn_base_frr_ele_id(22) := garn_ele_ids(2);
3843 garn_base_frr_iv_id(22) := garn_calc_iv_ids(20);
3844 garn_base_frr_severity(22) := NULL;
3845
3846 garn_base_frr_name(23) := 'PTD_BAL';
3847 garn_base_frr_type(23) := 'I';
3848 garn_base_frr_ele_id(23) := garn_ele_ids(2);
3849 garn_base_frr_iv_id(23) := garn_calc_iv_ids(21);
3850 garn_base_frr_severity(23) := NULL;
3851
3852 garn_base_frr_name(24) := 'CA_ACCRUED_FEES';
3853 garn_base_frr_type(24) := 'I';
3854 garn_base_frr_ele_id(24) := garn_ele_ids(2);
3855 garn_base_frr_iv_id(24) := garn_calc_iv_ids(22);
3856 garn_base_frr_severity(24) := NULL;
3857
3858 garn_base_frr_name(25) := 'CA_PTD_FEE_BAL';
3859 garn_base_frr_type(25) := 'I';
3860 garn_base_frr_ele_id(25) := garn_ele_ids(2);
3861 garn_base_frr_iv_id(25) := garn_calc_iv_ids(23);
3862 garn_base_frr_severity(25) := NULL;
3863
3864 garn_base_frr_name(26) := 'CA_MONTH_FEE_BAL';
3865 garn_base_frr_type(26) := 'I';
3866 garn_base_frr_ele_id(26) := garn_ele_ids(2);
3867 garn_base_frr_iv_id(26) := garn_calc_iv_ids(24);
3868 garn_base_frr_severity(26) := NULL;
3869
3870 garn_base_frr_name(27) := 'TO_ACCRUED_FEES';
3871 garn_base_frr_type(27) := 'I';
3872 garn_base_frr_ele_id(27) := garn_ele_ids(2);
3873 garn_base_frr_iv_id(27) := garn_calc_iv_ids(25);
3874 garn_base_frr_severity(27) := NULL;
3875
3876 l_num_base_resrules := 27;
3877
3878 garn_calc_frr_name(1) := 'TO_COUNT';
3879 garn_calc_frr_type(1) := 'I';
3880 garn_calc_frr_iv_id(1) := garn_SF_IV_IDS(7);
3881 garn_calc_frr_ele_id(1) := garn_ele_ids(5);
3882 garn_calc_frr_severity(1) := NULL;
3883
3884 garn_calc_frr_name(2) := 'MESG';
3885 garn_calc_frr_type(2) := 'M';
3886 garn_calc_frr_iv_id(2) := NULL;
3887 garn_calc_frr_ele_id(2) := NULL;
3888 garn_calc_frr_severity(2) := 'W';
3889
3890 garn_calc_frr_name(3) := 'VERIFY_DEDN_AMT';
3891 garn_calc_frr_type(3) := 'I';
3892 garn_calc_frr_iv_id(3) := garn_vp_iv_ids(1);
3893 garn_calc_frr_ele_id(3) := garn_ele_ids(7);
3894 garn_calc_frr_severity(3) := NULL;
3895
3896 garn_calc_frr_name(4) := 'VERIFY_ARREARS_AMT';
3897 garn_calc_frr_type(4) := 'I';
3898 garn_calc_frr_iv_id(4) := garn_vp_iv_ids(5);
3899 garn_calc_frr_ele_id(4) := garn_ele_ids(7);
3900 garn_calc_frr_severity(4) := NULL;
3901
3902 garn_calc_frr_name(5) := 'VERIFY_FEE_AMT';
3903 garn_calc_frr_type(5) := 'I';
3904 garn_calc_frr_iv_id(5) := garn_vp_iv_ids(6);
3905 garn_calc_frr_ele_id(5) := garn_ele_ids(7);
3906 garn_calc_frr_severity(5) := NULL;
3907
3908 garn_calc_frr_name(6) := 'DI_SUBJ';
3909 garn_calc_frr_type(6) := 'I';
3910 garn_calc_frr_iv_id(6) := garn_vp_iv_ids(3);
3911 garn_calc_frr_ele_id(6) := garn_ele_ids(7);
3912 garn_calc_frr_severity(6) := NULL;
3913
3914 -- 378699 : If support order, this result now goes to special features iv
3915 -- feeding total dedns bal...otherwise, goes to payval of base ele.
3916 IF p_category in ('CS', 'SS', 'AY') THEN
3917
3918 garn_calc_frr_name(7) := 'CALCD_DEDN_AMT';
3919 garn_calc_frr_type(7) := 'I';
3920 garn_calc_frr_iv_id(7) := garn_sf_iv_ids(4);
3921 garn_calc_frr_ele_id(7) := garn_ele_ids(5);
3922 garn_calc_frr_severity(7) := NULL;
3923
3924 ELSE
3925
3926 garn_calc_frr_name(7) := 'CALCD_DEDN_AMT';
3927 garn_calc_frr_type(7) := 'I';
3928 garn_calc_frr_iv_id(7) := garn_payval_id(1);
3929 garn_calc_frr_ele_id(7) := garn_ele_ids(1);
3930 garn_calc_frr_severity(7) := NULL;
3931
3932 END IF;
3933
3934 garn_calc_frr_name(8) := 'VERIFY_JD_CODE';
3935 garn_calc_frr_type(8) := 'I';
3936 garn_calc_frr_iv_id(8) := garn_vp_iv_ids(4);
3937 garn_calc_frr_ele_id(8) := garn_ele_ids(7);
3938 garn_calc_frr_severity(8) := NULL;
3939
3940 -- 378699 : Result now goes to special features iv feeding total fees bal.
3941 garn_calc_frr_name(9) := 'CALCD_FEE';
3942 garn_calc_frr_type(9) := 'I';
3943 garn_calc_frr_iv_id(9) := garn_sf_iv_ids(3);
3944 garn_calc_frr_ele_id(9) := garn_ele_ids(5);
3945 garn_calc_frr_severity(9) := NULL;
3946
3947 garn_calc_frr_name(10) := 'TO_TOTAL_OWED';
3948 garn_calc_frr_type(10) := 'I';
3949 garn_calc_frr_iv_id(10) := garn_SF_IV_IDS(9);
3950 garn_calc_frr_ele_id(10) := garn_ele_ids(5);
3951 garn_calc_frr_severity(10) := NULL;
3952
3953 garn_calc_frr_name(11) := 'STOP_ENTRY';
3954 garn_calc_frr_type(11) := 'S';
3955 garn_calc_frr_iv_id(11) := garn_payval_id(1);
3956 garn_calc_frr_ele_id(11) := garn_ele_ids(1);
3957 garn_calc_frr_severity(11) := NULL;
3958
3959 garn_calc_frr_name(12) := 'CALCD_ARREARS';
3960 garn_calc_frr_type(12) := 'I';
3961 garn_calc_frr_iv_id(12) := garn_sf_iv_ids(2);
3962 garn_calc_frr_ele_id(12) := garn_ele_ids(5);
3963 garn_calc_frr_severity(12) := NULL;
3964
3965 garn_calc_frr_name(13) := 'NOT_TAKEN';
3966 garn_calc_frr_type(13) := 'I';
3967 garn_calc_frr_iv_id(13) := garn_sf_iv_ids(1);
3968 garn_calc_frr_ele_id(13) := garn_ele_ids(5);
3969 garn_calc_frr_severity(13) := NULL;
3970
3971 garn_calc_frr_name(14) := 'PRIM_BAL';
3972 garn_calc_frr_type(14) := 'I';
3973 garn_calc_frr_iv_id(14) := garn_vp_iv_ids(7);
3974 garn_calc_frr_ele_id(14) := garn_ele_ids(7);
3975 garn_calc_frr_severity(14) := NULL;
3976
3977 garn_calc_frr_name(15) := 'TOTAL_OWED_AMT';
3978 garn_calc_frr_type(15) := 'I';
3979 garn_calc_frr_ele_id(15) := garn_ele_ids(7);
3980 garn_calc_frr_iv_id(15) := garn_vp_iv_ids(8);
3981 garn_calc_frr_severity(15) := NULL;
3982
3983 garn_calc_frr_name(16) := 'VERIF_DATE_SERVED';
3984 garn_calc_frr_type(16) := 'I';
3985 garn_calc_frr_ele_id(16) := garn_ele_ids(7);
3986 garn_calc_frr_iv_id(16) := garn_vp_iv_ids(9);
3987 garn_calc_frr_severity(16) := NULL;
3988
3989
3990 /* Fatal Error Message */
3991 garn_calc_frr_name(17) := 'FATAL_MESG';
3992 garn_calc_frr_type(17) := 'M';
3993 garn_calc_frr_iv_id(17) := NULL;
3994 garn_calc_frr_ele_id(17) := NULL;
3995 garn_calc_frr_severity(17) := 'F';
3996
3997
3998 /* Added 05 Mar 1999 for Bug 413086 */
3999 garn_calc_frr_name(18) := 'TO_REPL';
4000 garn_calc_frr_type(18) := 'I';
4001 garn_calc_frr_iv_id(18) := garn_sf_iv_ids(5);
4002 garn_calc_frr_ele_id(18) := garn_ele_ids(5);
4003 garn_calc_frr_severity(18) := NULL;
4004
4005
4006 garn_calc_frr_name(19) := 'TO_ADDL';
4007 garn_calc_frr_type(19) := 'I';
4008 garn_calc_frr_iv_id(19) := garn_sf_iv_ids(6);
4009 garn_calc_frr_ele_id(19) := garn_ele_ids(5);
4010 garn_calc_frr_severity(19) := NULL;
4011
4012 -- 625442 : Calculated fee for non-support garnishments to feed the fee element.
4013 IF p_category not in ('CS','SS','AY') THEN
4014 garn_calc_frr_name(20) := 'GARN_FEE';
4015 garn_calc_frr_type(20) := 'I';
4016 garn_calc_frr_iv_id(20) := garn_fee_iv_ids(1);
4017 garn_calc_frr_ele_id(20) := garn_ele_ids(6);
4018 garn_calc_frr_severity(20) := NULL;
4019
4020 garn_calc_frr_name(21) := 'SF_ACCRUED_FEES';
4021 garn_calc_frr_type(21) := 'I';
4022 garn_calc_frr_iv_id(21) := garn_SF_IV_IDS(10);
4023 garn_calc_frr_ele_id(21) := garn_ele_ids(5);
4024 garn_calc_frr_severity(21) := NULL;
4025
4026 l_num_calc_resrules := 21;
4027 ELSE
4028 garn_calc_frr_name(20) := 'DI_SUBJ_NC45';
4029 garn_calc_frr_type(20) := 'I';
4030 garn_calc_frr_ele_id(20) := garn_ele_ids(7);
4031 garn_calc_frr_iv_id(20) := garn_vp_iv_ids(10);
4032 garn_calc_frr_severity(20) := NULL;
4033
4034 garn_calc_frr_name(21) := 'CALC_SUBPRIO';
4035 garn_calc_frr_type(21) := 'I';
4036 garn_calc_frr_ele_id(21) := garn_ele_ids(7);
4037 garn_calc_frr_iv_id(21) := garn_vp_iv_ids(2);
4038 garn_calc_frr_severity(21) := NULL;
4039
4040 garn_calc_frr_name(22) := 'DI_SUBJ_NC50';
4041 garn_calc_frr_type(22) := 'I';
4042 garn_calc_frr_ele_id(22) := garn_ele_ids(7);
4043 garn_calc_frr_iv_id(22) := garn_vp_iv_ids(11);
4044 garn_calc_frr_severity(22) := NULL;
4045
4046 garn_calc_frr_name(23) := 'SUPP_OTHER_FAMILY';
4047 garn_calc_frr_type(23) := 'I';
4048 garn_calc_frr_ele_id(23) := garn_ele_ids(7);
4049 garn_calc_frr_iv_id(23) := garn_vp_iv_ids(12);
4050 garn_calc_frr_severity(23) := NULL;
4051
4052 garn_calc_frr_name(24) := 'PR_ACCRUED_FEES';
4053 garn_calc_frr_type(24) := 'I';
4054 garn_calc_frr_iv_id(24) := garn_vp_iv_ids(13);
4055 garn_calc_frr_ele_id(24) := garn_ele_ids(7);
4056 garn_calc_frr_severity(24) := NULL;
4057
4058 garn_calc_frr_name(25) := 'PR_PTD_FEE_BAL';
4059 garn_calc_frr_type(25) := 'I';
4060 garn_calc_frr_iv_id(25) := garn_vp_iv_ids(14);
4061 garn_calc_frr_ele_id(25) := garn_ele_ids(7);
4062 garn_calc_frr_severity(25) := NULL;
4063
4064 garn_calc_frr_name(26) := 'PR_MONTH_FEE_BAL';
4065 garn_calc_frr_type(26) := 'I';
4066 garn_calc_frr_iv_id(26) := garn_vp_iv_ids(15);
4067 garn_calc_frr_ele_id(26) := garn_ele_ids(7);
4068 garn_calc_frr_severity(26) := NULL;
4069
4070 garn_calc_frr_name(27) := 'SF_ACCRUED_FEES';
4071 garn_calc_frr_type(27) := 'I';
4072 garn_calc_frr_iv_id(27) := garn_SF_iv_ids(10);
4073 garn_calc_frr_ele_id(27) := garn_ele_ids(5);
4074 garn_calc_frr_severity(27) := NULL;
4075
4076 l_num_calc_resrules := 27;
4077
4078 END IF;
4079
4080 hr_utility.trace('After inserting Calculated fee for non supported elements');
4081 garn_verif_frr_name(1) := 'WH_DEDN_AMT';
4082 garn_verif_frr_type(1) := 'I';
4083 garn_verif_frr_iv_id(1) := garn_payval_id(1);
4084 garn_verif_frr_ele_id(1) := garn_ele_ids(1);
4085 garn_verif_frr_severity(1) := NULL;
4086
4087 garn_verif_frr_name(2) := 'ARREARS_AMT';
4088 garn_verif_frr_type(2) := 'I';
4089 garn_verif_frr_iv_id(2) := garn_sf_iv_ids(2);
4090 garn_verif_frr_ele_id(2) := garn_ele_ids(5);
4091 garn_verif_frr_severity(2) := NULL;
4092
4093 garn_verif_frr_name(3) := 'WH_FEE_AMT';
4094 garn_verif_frr_type(3) := 'I';
4095 garn_verif_frr_iv_id(3) := garn_fee_iv_ids(1);
4096 garn_verif_frr_ele_id(3) := garn_ele_ids(6);
4097 garn_verif_frr_severity(3) := NULL;
4098
4099 garn_verif_frr_name(4) := 'NOT_TAKEN';
4100 garn_verif_frr_type(4) := 'I';
4101 garn_verif_frr_iv_id(4) := garn_sf_iv_ids(1);
4102 garn_verif_frr_ele_id(4) := garn_ele_ids(5);
4103 garn_verif_frr_severity(4) := NULL;
4104
4105 garn_verif_frr_name(5) := 'TO_TOTAL_OWED';
4106 garn_verif_frr_type(5) := 'I';
4107 garn_verif_frr_iv_id(5) := garn_SF_IV_IDS(9);
4108 garn_verif_frr_ele_id(5) := garn_ele_ids(5);
4109 garn_verif_frr_severity(5) := NULL;
4110
4111 garn_verif_frr_name(6) := 'STOP_ENTRY';
4112 garn_verif_frr_type(6) := 'S';
4113 garn_verif_frr_iv_id(6) := garn_payval_id(1);
4114 garn_verif_frr_ele_id(6) := garn_ele_ids(1);
4115 garn_verif_frr_severity(6) := NULL;
4116
4117 garn_verif_frr_name(7) := 'CALC_SUBPRIO';
4118 garn_verif_frr_type(7) := 'O';
4119 garn_verif_frr_ele_id(7) := garn_ele_ids(6);
4120 garn_verif_frr_iv_id(7) := NULL;
4121 garn_verif_frr_severity(7) := NULL;
4122
4123 garn_verif_frr_name(8) := 'DIFF_DEDN_AMT';
4124 garn_verif_frr_type(8) := 'I';
4125 garn_verif_frr_iv_id(8) := garn_sf_iv_ids(4);
4126 garn_verif_frr_ele_id(8) := garn_ele_ids(5);
4127 garn_verif_frr_severity(8) := NULL;
4128
4129 garn_verif_frr_name(9) := 'DIFF_FEE_AMT';
4130 garn_verif_frr_type(9) := 'I';
4131 garn_verif_frr_iv_id(9) := garn_sf_iv_ids(3);
4132 garn_verif_frr_ele_id(9) := garn_ele_ids(5);
4133 garn_verif_frr_severity(9) := NULL;
4134
4135 garn_verif_frr_name(10) := 'MESG';
4136 garn_verif_frr_type(10) := 'M';
4137 garn_verif_frr_iv_id(10) := NULL;
4138 garn_verif_frr_ele_id(10) := NULL;
4139 garn_verif_frr_severity(10) := 'W';
4140
4141 garn_verif_frr_name(11) := 'FATAL_MESG';
4142 garn_verif_frr_type(11) := 'M';
4143 garn_verif_frr_iv_id(11) := NULL;
4144 garn_verif_frr_ele_id(11) := NULL;
4145 garn_verif_frr_severity(11) := 'F';
4146
4147 garn_verif_frr_name(12) := 'TO_COUNT';
4148 garn_verif_frr_type(12) := 'I';
4149 garn_verif_frr_iv_id(12) := garn_SF_IV_IDS(7);
4150 garn_verif_frr_ele_id(12) := garn_ele_ids(5);
4151 garn_verif_frr_severity(12) := NULL;
4152
4153 garn_verif_frr_name(13) := 'SF_ACCRUED_FEES';
4154 garn_verif_frr_type(13) := 'I';
4155 garn_verif_frr_iv_id(13) := garn_SF_IV_IDS(10);
4156 garn_verif_frr_ele_id(13) := garn_ele_ids(5);
4157 garn_verif_frr_severity(13) := NULL;
4158
4159 l_num_verif_resrules := 13;
4160
4161 garn_vp_frr_name(1) := 'VF_CALC_SUBPRIO';
4162 garn_vp_frr_type(1) := 'O';
4163 garn_vp_frr_iv_id(1) := NULL;
4164 garn_vp_frr_ele_id(1) := garn_ele_ids(3);
4165 garn_vp_frr_severity(1) := NULL;
4166
4167 garn_vp_frr_name(2) := 'VF_JD_CODE';
4168 garn_vp_frr_type(2) := 'I';
4169 garn_vp_frr_iv_id(2) := garn_verif_iv_ids(5);
4170 garn_vp_frr_ele_id(2) := garn_ele_ids(3);
4171 garn_vp_frr_severity(2) := NULL;
4172
4173 garn_vp_frr_name(3) := 'VF_DEDN_AMT';
4174 garn_vp_frr_type(3) := 'I';
4175 garn_vp_frr_iv_id(3) := garn_verif_iv_ids(1);
4176 garn_vp_frr_ele_id(3) := garn_ele_ids(3);
4177 garn_vp_frr_severity(3) := NULL;
4178
4179 garn_vp_frr_name(4) := 'VF_ARREARS_AMT';
4180 garn_vp_frr_type(4) := 'I';
4181 garn_vp_frr_iv_id(4) := garn_verif_iv_ids(2);
4182 garn_vp_frr_ele_id(4) := garn_ele_ids(3);
4183 garn_vp_frr_severity(4) := NULL;
4184
4185 garn_vp_frr_name(5) := 'VF_FEE_AMT';
4186 garn_vp_frr_type(5) := 'I';
4187 garn_vp_frr_iv_id(5) := garn_verif_iv_ids(3);
4188 garn_vp_frr_ele_id(5) := garn_ele_ids(3);
4189 garn_vp_frr_severity(5) := NULL;
4190
4191 garn_vp_frr_name(6) := 'VF_DI_SUBJ';
4192 garn_vp_frr_type(6) := 'I';
4193 garn_vp_frr_iv_id(6) := garn_verif_iv_ids(4);
4194 garn_vp_frr_ele_id(6) := garn_ele_ids(3);
4195 garn_vp_frr_severity(6) := NULL;
4196
4197 garn_vp_frr_name(7) := 'VF_DI_SUBJ_NC45';
4198 garn_vp_frr_type(7) := 'I';
4199 garn_vp_frr_iv_id(7) := garn_verif_iv_ids(9);
4200 garn_vp_frr_ele_id(7) := garn_ele_ids(3);
4201 garn_vp_frr_severity(7) := NULL;
4202
4203 garn_vp_frr_name(8) := 'VF_PRIM_BAL';
4204 garn_vp_frr_type(8) := 'I';
4205 garn_vp_frr_iv_id(8) := garn_verif_iv_ids(6);
4206 garn_vp_frr_ele_id(8) := garn_ele_ids(3);
4207 garn_vp_frr_severity(8) := NULL;
4208
4209 garn_vp_frr_name(9) := 'VF_TOTAL_OWED_AMT';
4210 garn_vp_frr_type(9) := 'I';
4211 garn_vp_frr_iv_id(9) := garn_verif_iv_ids(7);
4212 garn_vp_frr_ele_id(9) := garn_ele_ids(3);
4213 garn_vp_frr_severity(9) := NULL;
4214
4215 garn_vp_frr_name(10) := 'VF_DATE_SERVED';
4216 garn_vp_frr_type(10) := 'I';
4217 garn_vp_frr_iv_id(10) := garn_verif_iv_ids(8);
4218 garn_vp_frr_ele_id(10) := garn_ele_ids(3);
4219 garn_vp_frr_severity(10) := NULL;
4220
4221 garn_vp_frr_name(11) := 'VF_DI_SUBJ_NC50';
4222 garn_vp_frr_type(11) := 'I';
4223 garn_vp_frr_iv_id(11) := garn_verif_iv_ids(10);
4224 garn_vp_frr_ele_id(11) := garn_ele_ids(3);
4225 garn_vp_frr_severity(11) := NULL;
4226
4227 garn_vp_frr_name(12) := 'VF_SUPP_OTHER_FAMILY';
4228 garn_vp_frr_type(12) := 'I';
4229 garn_vp_frr_iv_id(12) := garn_verif_iv_ids(11);
4230 garn_vp_frr_ele_id(12) := garn_ele_ids(3);
4231 garn_vp_frr_severity(12) := NULL;
4232
4233 garn_vp_frr_name(13) := 'VF_ACCRUED_FEES';
4234 garn_vp_frr_type(13) := 'I';
4235 garn_vp_frr_iv_id(13) := garn_verif_iv_ids(12);
4236 garn_vp_frr_ele_id(13) := garn_ele_ids(3);
4237 garn_vp_frr_severity(13) := NULL;
4238
4239 garn_vp_frr_name(14) := 'VF_PTD_FEE_BAL';
4240 garn_vp_frr_type(14) := 'I';
4241 garn_vp_frr_iv_id(14) := garn_verif_iv_ids(13);
4242 garn_vp_frr_ele_id(14) := garn_ele_ids(3);
4243 garn_vp_frr_severity(14) := NULL;
4244
4245 garn_vp_frr_name(15) := 'VF_MONTH_FEE_BAL';
4246 garn_vp_frr_type(15) := 'I';
4247 garn_vp_frr_iv_id(15) := garn_verif_iv_ids(14);
4248 garn_vp_frr_ele_id(15) := garn_ele_ids(3);
4249 garn_vp_frr_severity(15) := NULL;
4250
4251 l_num_vp_resrules := 15;
4252
4253
4254 -- Formula Result Rules for BASE ELEMENT
4255 -- ie. garn_statproc_rule_id(1)
4256 hr_utility.set_location('Here', 1);
4257 delete from pay_formula_result_rules_f
4258 where STATUS_PROCESSING_RULE_ID = garn_statproc_rule_id(1);
4259
4260 hr_utility.set_location('Here', 2);
4261 FOR n in 1..l_num_base_resrules LOOP
4262
4263
4264 already_exists := hr_template_existence.result_rule_exists(
4265 p_spr_id => garn_statproc_rule_id(1),
4266 p_frr_name => garn_base_frr_name(n),
4267 p_iv_id => garn_base_frr_iv_id(n),
4268 p_bg_id => p_bg_id,
4269 p_ele_id => garn_base_frr_ele_id(n),
4270 p_eff_date => g_eff_start_date);
4271
4272 hr_utility.set_location('Here', 3);
4273 if already_exists = 0 then
4274
4275 hr_utility.set_location('Here', 4);
4276 v_fres_rule_id := pay_formula_results.ins_form_res_rule (
4277 p_business_group_id => p_bg_id,
4278 p_legislation_code => NULL,
4279 p_legislation_subgroup => g_template_leg_subgroup,
4280 p_effective_start_date => g_eff_start_date,
4281 p_effective_end_date => g_eff_end_date,
4282 p_status_processing_rule_id => garn_statproc_rule_id(1),
4283 p_input_value_id => garn_base_frr_iv_id(n),
4284 p_result_name => garn_base_frr_name(n),
4285 p_result_rule_type => garn_base_frr_type(n),
4286 p_severity_level => garn_base_frr_severity(n),
4287 p_element_type_id => garn_base_frr_ele_id(n));
4288 hr_utility.set_location('Here', 5);
4289
4290 else
4291
4292 hr_utility.set_location('Here', 6);
4293 v_fres_rule_id := already_exists;
4294
4295 end if;
4296
4297 END LOOP;
4298
4299 -- Formula Result Rules for CALCULATION ELEMENT
4300 -- ie. garn_statproc_rule_id(2)
4301 hr_utility.set_location('Here', 7);
4302 delete from pay_formula_result_rules_f
4303 where STATUS_PROCESSING_RULE_ID = garn_statproc_rule_id(2);
4304
4305 hr_utility.set_location('Here', 8);
4306 FOR o in 1..l_num_calc_resrules LOOP
4307
4308 hr_utility.set_location('Here', 9);
4309 already_exists := hr_template_existence.result_rule_exists(
4310 p_spr_id => garn_statproc_rule_id(2),
4311 p_frr_name => garn_calc_frr_name(o),
4312 p_iv_id => garn_calc_frr_iv_id(o),
4313 p_bg_id => p_bg_id,
4314 p_ele_id => garn_calc_frr_ele_id(o),
4315 p_eff_date => g_eff_start_date);
4316
4317 if already_exists = 0 then
4318
4319 hr_utility.set_location('Here', 10);
4320
4321 v_fres_rule_id := pay_formula_results.ins_form_res_rule (
4322 p_business_group_id => p_bg_id,
4323 p_legislation_code => NULL,
4324 p_legislation_subgroup => g_template_leg_subgroup,
4325 p_effective_start_date => g_eff_start_date,
4326 p_effective_end_date => g_eff_end_date,
4327 p_status_processing_rule_id => garn_statproc_rule_id(2),
4328 p_input_value_id => garn_calc_frr_iv_id(o),
4329 p_result_name => garn_calc_frr_name(o),
4330 p_result_rule_type => garn_calc_frr_type(o),
4331 p_severity_level => garn_calc_frr_severity(o),
4332 p_element_type_id => garn_calc_frr_ele_id(o));
4333
4334 hr_utility.set_location('Here', 11);
4335 else
4336
4337 v_fres_rule_id := already_exists;
4338
4339 hr_utility.set_location('Here', 12);
4340 end if;
4341
4342 END LOOP;
4343 hr_utility.set_location('Here', 13);
4344
4345 -- Formula Result Rules for VERIFIER PRIORITY ELEMENT
4346 -- ie. garn_statproc_rule_id(7)
4347 IF p_category in ('CS', 'SS', 'AY') THEN
4348 hr_utility.set_location('Here', 14);
4349 delete from pay_formula_result_rules_f
4350 where STATUS_PROCESSING_RULE_ID = garn_statproc_rule_id(7);
4351 hr_utility.set_location('Here', 15);
4352
4353 FOR o in 1..l_num_vp_resrules LOOP
4354
4355 hr_utility.set_location('Here', 16);
4356 already_exists := hr_template_existence.result_rule_exists(
4357 p_spr_id => garn_statproc_rule_id(7),
4358 p_frr_name => garn_vp_frr_name(o),
4359 p_iv_id => garn_vp_frr_iv_id(o),
4360 p_bg_id => p_bg_id,
4361 p_ele_id => garn_vp_frr_ele_id(o),
4362 p_eff_date => g_eff_start_date);
4363
4364 if already_exists = 0 then
4365
4366 hr_utility.set_location('Here', 17);
4367 v_fres_rule_id := pay_formula_results.ins_form_res_rule (
4368 p_business_group_id => p_bg_id,
4369 p_legislation_code => NULL,
4370 p_legislation_subgroup => g_template_leg_subgroup,
4371 p_effective_start_date => g_eff_start_date,
4372 p_effective_end_date => g_eff_end_date,
4373 p_status_processing_rule_id => garn_statproc_rule_id(7),
4374 p_input_value_id => garn_vp_frr_iv_id(o),
4375 p_result_name => garn_vp_frr_name(o),
4376 p_result_rule_type => garn_vp_frr_type(o),
4377 p_severity_level => garn_vp_frr_severity(o),
4378 p_element_type_id => garn_vp_frr_ele_id(o));
4379
4380 hr_utility.set_location('Here', 18);
4381 else
4382
4383 v_fres_rule_id := already_exists;
4384 hr_utility.set_location('Here', 19);
4385
4386 end if;
4387
4388 END LOOP;
4389 end if;
4390
4391 hr_utility.set_location('Here', 20);
4392 -- Formula Result Rules for VERIFIER ELEMENT
4393 -- ie. garn_statproc_rule_id(3) , only required for support orders...
4394 IF p_category in ('CS', 'SS', 'AY') THEN
4395 hr_utility.set_location('Here', 21);
4396 delete from pay_formula_result_rules_f
4397 where STATUS_PROCESSING_RULE_ID = garn_statproc_rule_id(3);
4398
4399 FOR p in 1..l_num_verif_resrules LOOP
4400
4401 hr_utility.set_location('Here', 22);
4402 already_exists := hr_template_existence.result_rule_exists(
4403 p_spr_id => garn_statproc_rule_id(3),
4404 p_frr_name => garn_verif_frr_name(p),
4405 p_iv_id => garn_verif_frr_iv_id(p),
4406 p_bg_id => p_bg_id,
4407 p_ele_id => garn_verif_frr_ele_id(p),
4408 p_eff_date => g_eff_start_date);
4409
4410 hr_utility.set_location('Here', 23);
4411 if already_exists = 0 then
4412
4413 hr_utility.set_location('Here', 24);
4414 v_fres_rule_id := pay_formula_results.ins_form_res_rule (
4415 p_business_group_id => p_bg_id,
4416 p_legislation_code => NULL,
4417 p_legislation_subgroup => g_template_leg_subgroup,
4418 p_effective_start_date => g_eff_start_date,
4419 p_effective_end_date => g_eff_end_date,
4420 p_status_processing_rule_id => garn_statproc_rule_id(3),
4421 p_input_value_id => garn_verif_frr_iv_id(p),
4422 p_result_name => garn_verif_frr_name(p),
4423 p_result_rule_type => garn_verif_frr_type(p),
4424 p_severity_level => garn_verif_frr_severity(p),
4425 p_element_type_id => garn_verif_frr_ele_id(p));
4426
4427 else
4428
4429 v_fres_rule_id := already_exists;
4430 hr_utility.set_location('Here', 25);
4431
4432 end if;
4433
4434 END LOOP;
4435
4436 END IF;
4437
4438 --
4439 -- Now insert appropriate balance feeds.
4440 --
4441
4442 garn_si_feed_iv_id(1) := garn_si_iv_ids(1);
4443 garn_si_feed_bal_id(1) := garn_assoc_bal_ids(3);
4444
4445 garn_si_feed_iv_id(2) := garn_si_iv_ids(2);
4446 garn_si_feed_bal_id(2) := garn_assoc_bal_ids(2);
4447
4448 garn_si_feed_iv_id(3) := garn_si_iv_ids(3);
4449 garn_si_feed_bal_id(3) := garn_assoc_bal_ids(5);
4450
4451 l_num_si_feeds := 3;
4452
4453
4454 garn_sf_feed_iv_id(1) := garn_sf_iv_ids(2);
4455 garn_sf_feed_bal_id(1) := garn_assoc_bal_ids(5);
4456
4457 garn_sf_feed_iv_id(2) := garn_SF_IV_IDS(5);
4458 garn_sf_feed_bal_id(2) := garn_assoc_bal_ids(3);
4459
4460 garn_sf_feed_iv_id(3) := garn_SF_IV_IDS(6);
4461 garn_sf_feed_bal_id(3) := garn_assoc_bal_ids(2);
4462
4463 garn_sf_feed_iv_id(4) := garn_SF_IV_IDS(8);
4464 garn_sf_feed_bal_id(4) := garn_assoc_bal_ids(8);
4465
4466 garn_sf_feed_iv_id(5) := garn_sf_iv_ids(1);
4467 garn_sf_feed_bal_id(5) := garn_assoc_bal_ids(6);
4468
4469 garn_sf_feed_iv_id(6) := garn_SF_IV_IDS(9);
4470 garn_sf_feed_bal_id(6) := garn_assoc_bal_ids(4);
4471
4472 -- 378699 begin
4473 garn_sf_feed_iv_id(7) := garn_sf_iv_ids(3);
4474 garn_sf_feed_bal_id(7) := g_total_fees_balance_id;
4475
4476 garn_sf_feed_iv_id(8) := garn_sf_iv_ids(4);
4477 garn_sf_feed_bal_id(8) := g_total_dedns_balance_id;
4478
4479 -- 378699 end
4480
4481 garn_sf_feed_iv_id(9) := garn_SF_IV_IDS(10);
4482 garn_sf_feed_bal_id(9) := garn_assoc_bal_ids(9);
4483
4484 -- 374743 : Treat spousal support and alimony same as child support.
4485 IF p_category IN ('CS', 'SS', 'AY') THEN
4486
4487 garn_sf_feed_iv_id(10) := garn_SF_IV_IDS(7);
4488 garn_sf_feed_bal_id(10) := g_childsupp_count_balance_id;
4489
4490 /* pre-tax arrearage balance for Support */
4491
4492 garn_sf_feed_iv_id(11) := garn_sf_iv_ids(1);
4493 garn_sf_feed_bal_id(11) := g_supp_not_taken_bal_id;
4494
4495 l_num_sf_feeds := 11;
4496
4497 ELSE
4498
4499 /* pre-tax arrearage balance for Other Wage Attachment*/
4500
4501 garn_sf_feed_iv_id(10) := garn_sf_iv_ids(1);
4502 garn_sf_feed_bal_id(10) := g_other_not_taken_bal_id;
4503
4504
4505 l_num_sf_feeds := 10;
4506
4507 END IF;
4508
4509 garn_base_feed_iv_id(1) := garn_payval_id(1);
4510 garn_base_feed_bal_id(1) := garn_assoc_bal_ids(1);
4511
4512 l_num_base_feeds := 1;
4513
4514 /* 378699 : payval is now fed by verifier, feed "total withheld support"
4515 balance for proration of support in order of receipt.
4516 */
4517 IF p_category in ('CS', 'SS', 'AY') THEN
4518
4519 garn_base_feed_iv_id(2) := garn_payval_id(1);
4520 garn_base_feed_bal_id(2) := g_wh_support_balance_id;
4521
4522 /* 2374248 : Created new balance to get desired Amount which is
4523 input value of Amount rather than using calculated dedn amount */
4524
4525 garn_base_feed_iv_id(3) := garn_base_iv_ids(1);
4526 garn_base_feed_bal_id(3) := g_total_amount_balance_id;
4527
4528 l_num_base_feeds := 3;
4529
4530 ELSE
4531
4532 /* 365745 : Feed tax levies high level category balance when wage attachment
4533 is a tax levy...
4534 */
4535
4536 IF p_category = 'TL' THEN
4537
4538 garn_base_feed_iv_id(2) := garn_payval_id(1);
4539 garn_base_feed_bal_id(2) := g_tax_levies_balance_id;
4540
4541 l_num_base_feeds := 2;
4542
4543
4544 ELSE
4545
4546 garn_base_feed_iv_id(2) := garn_payval_id(1);
4547 garn_base_feed_bal_id(2) := g_total_dedns_balance_id;
4548
4549 l_num_base_feeds := 2;
4550
4551 END IF;
4552
4553 END IF;
4554
4555 /* 365745 : Feed tax levies high level category balance when wage attachment
4556 is a tax levy...
4557 */
4558
4559 IF p_category = 'TL' THEN
4560
4561 garn_base_feed_iv_id(3) := garn_payval_id(1);
4562 garn_base_feed_bal_id(3) := g_tax_levies_balance_id;
4563
4564 l_num_base_feeds := 3;
4565
4566 END IF;
4567
4568
4569 garn_calc_feed_iv_id(1) := garn_payval_id(2);
4570 garn_calc_feed_bal_id(1) := garn_assoc_bal_ids(1);
4571
4572 garn_calc_feed_iv_id(2) := garn_payval_id(2);
4573 garn_calc_feed_bal_id(2) := g_total_dedns_balance_id;
4574
4575 l_num_calc_feeds := 2;
4576
4577
4578
4579 garn_fee_feed_iv_id(1) := garn_fee_iv_ids(1);
4580 garn_fee_feed_bal_id(1) := garn_assoc_bal_ids(7);
4581
4582 IF p_category in ('CS', 'SS', 'AY') THEN
4583
4584 garn_fee_feed_iv_id(2) := garn_fee_iv_ids(1);
4585 garn_fee_feed_bal_id(2) := g_wh_fee_balance_id;
4586
4587 l_num_fee_feeds := 2;
4588
4589 ELSE
4590
4591 l_num_fee_feeds := 1;
4592
4593 END IF;
4594
4595
4596 hr_utility.trace('Base Feeds : '||to_char(l_num_base_feeds));
4597
4598 FOR i in 1..l_num_base_feeds LOOP
4599
4600 IF garn_base_feed_iv_id(i) IS NOT NULL THEN
4601
4602 hr_utility.trace('Bal Id : '||to_char(garn_base_feed_bal_id(i)));
4603 hr_utility.trace('BG Id : '||to_char(p_bg_id));
4604 hr_utility.trace('IV Id : '||to_char(garn_base_feed_iv_id(i)));
4605 hr_utility.trace('Date : '||g_eff_start_date);
4606
4607 already_exists := hr_template_existence.bal_feed_exists (
4608 p_bal_id => garn_base_feed_bal_id(i),
4609 p_bg_id => p_bg_id,
4610 p_iv_id => garn_base_feed_iv_id(i),
4611 p_eff_date => g_eff_start_date);
4612
4613 if ALREADY_EXISTS = 0 then
4614
4615 hr_balances.ins_balance_feed(
4616 p_option => 'INS_MANUAL_FEED',
4617 p_input_value_id => garn_base_feed_iv_id(i),
4618 p_element_type_id => NULL,
4619 p_primary_classification_id => NULL,
4620 p_sub_classification_id => NULL,
4621 p_sub_classification_rule_id => NULL,
4622 p_balance_type_id => garn_base_feed_bal_id(i),
4623 p_scale => '1',
4624 p_session_date => g_eff_start_date,
4625 p_business_group => p_bg_id,
4626 p_legislation_code => NULL,
4627 p_mode => 'USER');
4628
4629 end if;
4630
4631 END IF;
4632
4633 END LOOP;
4634
4635 hr_utility.trace('SI Feeds : '||to_char(l_num_si_feeds));
4636
4637 FOR sif in 1..l_num_si_feeds LOOP
4638
4639 hr_utility.trace('Bal Id : '||to_char(garn_si_feed_bal_id(sif)));
4640 hr_utility.trace('BG Id : '||to_char(p_bg_id));
4641 hr_utility.trace('IV Id : '||to_char(garn_si_feed_iv_id(sif)));
4642 hr_utility.trace('Date : '||g_eff_start_date);
4643
4644 already_exists := hr_template_existence.bal_feed_exists (
4645 p_bal_id => garn_si_feed_bal_id(sif),
4646 p_bg_id => p_bg_id,
4647 p_iv_id => garn_si_feed_iv_id(sif),
4648 p_eff_date => g_eff_start_date);
4649
4650 if ALREADY_EXISTS = 0 then
4651
4652 hr_balances.ins_balance_feed(
4653 p_option => 'INS_MANUAL_FEED',
4654 p_input_value_id => garn_si_feed_iv_id(sif),
4655 p_element_type_id => NULL,
4656 p_primary_classification_id => NULL,
4657 p_sub_classification_id => NULL,
4658 p_sub_classification_rule_id => NULL,
4659 p_balance_type_id => garn_si_feed_bal_id(sif),
4660 p_scale => '1',
4661 p_session_date => g_eff_start_date,
4662 p_business_group => p_bg_id,
4663 p_legislation_code => NULL,
4664 p_mode => 'USER');
4665
4666 end if;
4667
4668 END LOOP;
4669
4670 hr_utility.trace('SF Feeds : '||to_char(l_num_sf_feeds));
4671
4672 FOR sf in 1..l_num_sf_feeds LOOP
4673
4674 hr_utility.trace('l_num_sf_feeds = '||to_char(sf));
4675 hr_utility.trace('g_other_not_taken_bal_id = '||to_char(g_other_not_taken_bal_id));
4676 hr_utility.trace('Bal Id : '||to_char(garn_sf_feed_bal_id(sf)));
4677 hr_utility.trace('BG Id : '||to_char(p_bg_id));
4678 hr_utility.trace('IV Id : '||to_char(garn_sf_feed_iv_id(sf)));
4679 hr_utility.trace('Date : '||g_eff_start_date);
4680
4681 already_exists := hr_template_existence.bal_feed_exists (
4682 p_bal_id => garn_sf_feed_bal_id(sf),
4683 p_bg_id => p_bg_id,
4684 p_iv_id => garn_sf_feed_iv_id(sf),
4685 p_eff_date => g_eff_start_date);
4686
4687 if ALREADY_EXISTS = 0 then
4688
4689 hr_balances.ins_balance_feed(
4690 p_option => 'INS_MANUAL_FEED',
4691 p_input_value_id => garn_sf_feed_iv_id(sf),
4692 p_element_type_id => NULL,
4693 p_primary_classification_id => NULL,
4694 p_sub_classification_id => NULL,
4695 p_sub_classification_rule_id => NULL,
4696 p_balance_type_id => garn_sf_feed_bal_id(sf),
4697 p_scale => '1',
4698 p_session_date => g_eff_start_date,
4699 p_business_group => p_bg_id,
4700 p_legislation_code => NULL,
4701 p_mode => 'USER');
4702
4703 end if;
4704
4705 END LOOP;
4706
4707 /*
4708 hr_utility.trace('Calc Feeds : '||to_char(l_num_calc_feeds));
4709
4710 FOR scf in 1..l_num_calc_feeds LOOP
4711
4712 hr_utility.trace('Bal Id : '||to_char(garn_calc_feed_bal_id(scf)));
4713 hr_utility.trace('BG Id : '||to_char(p_bg_id));
4714 hr_utility.trace('IV Id : '||to_char(garn_calc_feed_iv_id(scf)));
4715 hr_utility.trace('Date : '||g_eff_start_date);
4716
4717 already_exists := hr_template_existence.bal_feed_exists(
4718 p_bal_id => garn_calc_feed_bal_id(scf),
4719 p_bg_id => p_bg_id,
4720 p_iv_id => garn_calc_feed_iv_id(scf),
4721 p_eff_date => g_eff_start_date);
4722
4723 if ALREADY_EXISTS = 0 then
4724
4725 hr_balances.ins_balance_feed(
4726 p_option => 'INS_MANUAL_FEED',
4727 p_input_value_id => garn_calc_feed_iv_id(scf),
4728 p_element_type_id => NULL,
4729 p_primary_classification_id => NULL,
4730 p_sub_classification_id => NULL,
4731 p_sub_classification_rule_id => NULL,
4732 p_balance_type_id => garn_calc_feed_bal_id(scf),
4733 p_scale => '1',
4734 p_session_date => g_eff_start_date,
4735 p_business_group => p_bg_id,
4736 p_legislation_code => NULL,
4737 p_mode => 'USER');
4738
4739 end if;
4740
4741 END LOOP;
4742
4743 hr_utility.trace('Verif Feeds : '||to_char(l_num_verif_feeds));
4744
4745 FOR i in 1..l_num_verif_feeds LOOP
4746
4747 hr_utility.trace('Bal Id : '||to_char(garn_verif_feed_bal_id(sif)));
4748 hr_utility.trace('BG Id : '||to_char(p_bg_id));
4749 hr_utility.trace('IV Id : '||to_char(garn_verif_feed_iv_id(sif)));
4750 hr_utility.trace('Date : '||g_eff_start_date);
4751
4752 already_exists := hr_template_existence.bal_feed_exists (
4753 p_bal_id => garn_verif_feed_bal_id(i),
4754 p_bg_id => p_bg_id,
4755 p_iv_id => garn_verif_feed_iv_id(i),
4756 p_eff_date => g_eff_start_date);
4757
4758 if ALREADY_EXISTS = 0 then
4759
4760 hr_balances.ins_balance_feed(
4761 p_option => 'INS_MANUAL_FEED',
4762 p_input_value_id => garn_verif_feed_iv_id(i),
4763 p_element_type_id => NULL,
4764 p_primary_classification_id => NULL,
4765 p_sub_classification_id => NULL,
4766 p_sub_classification_rule_id => NULL,
4767 p_balance_type_id => garn_verif_feed_bal_id(i),
4768 p_scale => '1',
4769 p_session_date => g_eff_start_date,
4770 p_business_group => p_bg_id,
4771 p_legislation_code => NULL,
4772 p_mode => 'USER');
4773
4774 end if;
4775
4776 END LOOP;
4777 */
4778 hr_utility.trace('Fee Feeds : '||to_char(l_num_fee_feeds));
4779
4780 FOR nfee in 1..l_num_fee_feeds LOOP
4781
4782 hr_utility.trace('Bal Id : '||to_char(garn_fee_feed_bal_id(nfee)));
4783 hr_utility.trace('BG Id : '||to_char(p_bg_id));
4784 hr_utility.trace('IV Id : '||to_char(garn_fee_feed_iv_id(nfee)));
4785 hr_utility.trace('Date : '||g_eff_start_date);
4786
4787 already_exists := hr_template_existence.bal_feed_exists (
4788 p_bal_id => garn_fee_feed_bal_id(nfee),
4789 p_bg_id => p_bg_id,
4790 p_iv_id => garn_fee_feed_iv_id(nfee),
4791 p_eff_date => g_eff_start_date);
4792
4793 if ALREADY_EXISTS = 0 then
4794
4795 hr_balances.ins_balance_feed(
4796 p_option => 'INS_MANUAL_FEED',
4797 p_input_value_id => garn_fee_feed_iv_id(nfee),
4798 p_element_type_id => NULL,
4799 p_primary_classification_id => NULL,
4800 p_sub_classification_id => NULL,
4801 p_sub_classification_rule_id => NULL,
4802 p_balance_type_id => garn_fee_feed_bal_id(nfee),
4803 p_scale => '1',
4804 p_session_date => g_eff_start_date,
4805 p_business_group => p_bg_id,
4806 p_legislation_code => NULL,
4807 p_mode => 'USER');
4808
4809 end if;
4810
4811 END LOOP;
4812
4813
4814 -- Insert feeds for fee amount input val to balances fed by invol dedns (ie. not automatically
4815 -- created b/c these are not the Fee element Pay Value!).
4816
4817 OPEN get_invol_bals;
4818 hr_utility.trace('Insert feeds for fee amount input val to balances fed by invol dedns');
4819 LOOP
4820
4821 FETCH get_invol_bals INTO l_involbal_id, l_invol_scale;
4822 EXIT WHEN get_invol_bals%NOTFOUND;
4823
4824 hr_utility.trace('Bal Id : '||to_char(l_involbal_id));
4825 hr_utility.trace('BG Id : '||to_char(p_bg_id));
4826 hr_utility.trace('IV Id : '||to_char(garn_fee_iv_ids(1)));
4827 hr_utility.trace('Date : '||g_eff_start_date);
4828
4829 already_exists := hr_template_existence.bal_feed_exists (
4830 p_bal_id => l_involbal_id,
4831 p_bg_id => p_bg_id,
4832 p_iv_id => garn_fee_iv_ids(1),
4833 p_eff_date => g_eff_start_date);
4834
4835 if ALREADY_EXISTS = 0 then
4836
4837 hr_balances.ins_balance_feed(
4838 p_option => 'INS_MANUAL_FEED',
4839 p_input_value_id => garn_fee_iv_ids(1),
4840 p_element_type_id => NULL,
4841 p_primary_classification_id => NULL,
4842 p_sub_classification_id => NULL,
4843 p_sub_classification_rule_id => NULL,
4844 p_balance_type_id => l_involbal_id,
4845 p_scale => l_invol_scale,
4846 p_session_date => g_eff_start_date,
4847 p_business_group => p_bg_id,
4848 p_legislation_code => NULL,
4849 p_mode => 'USER');
4850
4851 end if;
4852
4853 -- Also need to do this for pay value of base element, just in case...
4854 hr_utility.trace('Also need to do this for pay value of base element');
4855
4856 hr_utility.trace('Bal Id : '||to_char(l_involbal_id));
4857 hr_utility.trace('BG Id : '||to_char(p_bg_id));
4858 hr_utility.trace('IV Id : '||to_char(garn_payval_id(1)));
4859 hr_utility.trace('Date : '||g_eff_start_date);
4860
4861 already_exists := hr_template_existence.bal_feed_exists (
4862 p_bal_id => l_involbal_id,
4863 p_bg_id => p_bg_id,
4864 p_iv_id => garn_payval_id(1),
4865 p_eff_date => g_eff_start_date);
4866
4867 if ALREADY_EXISTS = 0 then
4868
4869 hr_balances.ins_balance_feed(
4870 p_option => 'INS_MANUAL_FEED',
4871 p_input_value_id => garn_payval_id(1),
4872 p_element_type_id => NULL,
4873 p_primary_classification_id => NULL,
4874 p_sub_classification_id => NULL,
4875 p_sub_classification_rule_id => NULL,
4876 p_balance_type_id => l_involbal_id,
4877 p_scale => l_invol_scale,
4878 p_session_date => g_eff_start_date,
4879 p_business_group => p_bg_id,
4880 p_legislation_code => NULL,
4881 p_mode => 'USER');
4882
4883 end if;
4884
4885 END LOOP;
4886 CLOSE get_invol_bals;
4887
4888 --
4889 -- Now make sure all DDF segs for assoc bals and assoc eles are set for
4890 -- child support.
4891 --
4892 UPDATE pay_element_types_f
4893 SET element_information5 = garn_ele_ids(2),
4894 element_information8 = garn_assoc_bal_ids(8),
4895 element_information10 = garn_assoc_bal_ids(1),
4896 element_information11 = garn_assoc_bal_ids(4),
4897 /* Not setting Arrears and Not Taken Balance for bug 980683
4898 NotTaken and Arrears are set to 0 in the verifier formula
4899 for bug 980683. However, these balances are needed for
4900 bug 2527761. So, to make both work, the arrears and not taken
4901 balance will be created but not set in the Further Element
4902 Information Flexfield.
4903
4904 element_information12 = garn_assoc_bal_ids(5),
4905 element_information13 = garn_assoc_bal_ids(6),
4906 */
4907 element_information16 = garn_assoc_bal_ids(2),
4908 element_information17 = garn_assoc_bal_ids(3),
4909 element_information15 = garn_assoc_bal_ids(7) ,
4910 element_information18 = garn_ele_ids(4),
4911 element_information19 = garn_ele_ids(5),
4912 element_information20 = garn_ele_ids(3)
4913 WHERE element_type_id = garn_ele_ids(1);
4914
4915 hr_utility.trace('Before final update Line:4513 garn_assoc_bal_id '|| to_char(garn_assoc_bal_ids(7)));
4916 UPDATE pay_element_types_f
4917 SET element_information10 = garn_assoc_bal_ids(7),
4918 element_information11 = garn_assoc_bal_ids(9)
4919 WHERE element_type_id = garn_ele_ids(6);
4920
4921 l_base_ele_id := garn_ele_ids(1);
4922
4923 RETURN l_base_ele_id;
4924 END create_garnishment;
4925
4926 --
4927 ------------------------- Deletion procedures -----------------------------
4928 --
4929 -- The following procedure is used to delete all payroll objects related to
4930 -- deduction configurations (esp. involuntary deductions).
4931 -- The element type DDF seggies are utilized as follows:
4932 -- ELEMENT_INFORMATION10 = Associated Primary Balance
4933 -- ELEMENT_INFORMATION11 = Associated Accrued Balance
4934 -- ELEMENT_INFORMATION12 = Associated Arrears Balance
4935 -- ELEMENT_INFORMATION13 = Associated Not Taken Balance
4936 -- ELEMENT_INFORMATION14 = Associated To Bond Purchase Balance
4937 -- ELEMENT_INFORMATION15 = Associated Fee Balance
4938 -- ELEMENT_INFORMATION16 = Associated Additional Amount Balance
4939 -- ELEMENT_INFORMATION17 = Associated Replacement Amount Balance
4940 -- ELEMENT_INFORMATION18 = Associated Special Inputs Element
4941 -- ELEMENT_INFORMATION19 = Associated Special Features Element
4942 -- ELEMENT_INFORMATION20 = Associated Verification Element
4943 -- ELEMENT_INFORMATION5 = Associated Calculation Element
4944 -- NOTE: Further Element Type DDF is full, so associated Fee Element
4945 -- must be found by explicit match on element name.
4946 --
4947 -- Configuration deletion follows this algorithm:
4948 -- 0. Delete frequency rules for wage attachment.
4949 -- 1. Delete all associated balances.
4950 -- 2. For all associated element types of configured wage attachment...
4951 -- 3. Delete all formula result rules.
4952 -- 4. Delete all status processing rules.
4953 -- 5. Delete all formulae...including OLD formulae preserved during upgrades.
4954 -- 6. Delete all input values.
4955 -- 7. Delete element types.
4956 --
4957
4958 PROCEDURE delete_dedn (p_business_group_id in number,
4959 p_ele_type_id in number,
4960 p_ele_name in varchar2,
4961 p_ele_priority in number,
4962 p_ele_info_10 in varchar2,
4963 p_ele_info_11 in varchar2,
4964 p_ele_info_12 in varchar2,
4965 p_ele_info_13 in varchar2,
4966 p_ele_info_14 in varchar2,
4967 p_ele_info_15 in varchar2,
4968 p_ele_info_16 in varchar2,
4969 p_ele_info_17 in varchar2,
4970 p_ele_info_18 in varchar2,
4971 p_ele_info_19 in varchar2,
4972 p_ele_info_20 in varchar2,
4973 p_ele_info_5 in varchar2,
4974 p_ele_info_8 in varchar2,
4975 p_del_sess_date in date,
4976 p_del_val_start_date in date,
4977 p_del_val_end_date in date) IS
4978 -- local constants
4979 c_end_of_time CONSTANT DATE := TO_DATE('31/12/4712','DD/MM/YYYY');
4980
4981 TYPE text_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4982 TYPE num_table IS TABLE OF NUMBER(9) INDEX BY BINARY_INTEGER;
4983
4984 assoc_eles num_table;
4985 assoc_bals num_table;
4986
4987 i number;
4988 j number;
4989 l_num_assoc_bals number;
4990 l_num_assoc_eles number;
4991
4992 -- local vars
4993 v_del_mode VARCHAR2(80) := 'ZAP'; -- Completely remove template.
4994 v_startup_mode VARCHAR2(80) := 'USER';
4995 v_del_sess_date DATE := NULL;
4996 v_del_val_start DATE := NULL;
4997 v_del_val_end DATE := NULL;
4998 v_bal_type_id NUMBER(9);
4999 v_eletype_id NUMBER(9);
5000 v_assoc_eletype_id NUMBER(9);
5001 v_assoc_ele_priority NUMBER(9);
5002 v_ff_id NUMBER(9);
5003 v_ff_count NUMBER(3);
5004 v_fname_suffix VARCHAR2(20);
5005 v_eletype_count NUMBER(3);
5006 v_baltype_count NUMBER(3);
5007 v_assoc_bal_type_id NUMBER(9);
5008 v_assoc_bal_name VARCHAR2(80);
5009 v_freqrule_id NUMBER(9);
5010 v_spr_id NUMBER(9);
5011
5012 v_voldedn_baltype_id NUMBER(9);
5013 v_template_id number(9);
5014
5015 l_fee_primary_bal varchar2(150);
5016 l_fee_primbal_id number(9);
5017
5018 l_fee_ele_id number(9);
5019 l_pri_ele_id number(9);
5020 l_fee_accrued_bal_id number(9);
5021
5022
5023 CURSOR get_formulae(l_ele_id in number) IS
5024 SELECT distinct ff.formula_id
5025 FROM pay_status_processing_rules_f spr, ff_formulas_f ff
5026 WHERE spr.element_type_id = l_ele_id
5027 AND ff.formula_id = spr.formula_id
5028 AND ff.business_group_id + 0 = p_business_group_id
5029 AND ff.legislation_code IS NULL;
5030
5031 CURSOR get_old_formulae(l_ele_name in varchar2) IS
5032 SELECT distinct ff.formula_id
5033 FROM ff_formulas_f ff
5034 WHERE ff.formula_name like upper('OLD%'||p_ele_name||'_BALANCE_SETUP_FORMULA%')
5035 AND ff.business_group_id + 0 = p_business_group_id;
5036
5037 CURSOR get_spr(l_ele_id in number) IS
5038 SELECT distinct status_processing_rule_id
5039 FROM pay_status_processing_rules_f
5040 WHERE element_type_id = l_ele_id;
5041
5042 CURSOR get_freqrule IS
5043 SELECT ele_payroll_freq_rule_id
5044 FROM pay_ele_payroll_freq_rules
5045 WHERE element_type_id = p_ele_type_id;
5046
5047 -- Bug 3682501
5048 -- Cursor to obtain the template id needed to be deleted from the shadow tables
5049 -- for the current element getting deleted
5050 CURSOR get_template_id IS
5051 SELECT template_id
5052 FROM pay_shadow_element_types
5053 WHERE element_name = p_ele_name;
5054
5055 -- Garnishmment Rewrite
5056 -- Changing the select statement to a cursor because in the
5057 -- new architecture we dont have Priority element.
5058 -- Cursor will return NULL for elements created using New architecture
5059 -- Elements created prior to Garnishment Rewrite will return the
5060 -- element_type_id which is then used to delete the Priority element created.
5061 CURSOR c_get_priority IS
5062 SELECT element_type_id
5063 FROM pay_element_types_f
5064 WHERE element_name = p_ele_name||' Priority'
5065 AND p_del_sess_date between effective_start_date
5066 and effective_end_date
5067 AND business_group_id + 0 = p_business_group_id;
5068
5069 -- Bug 3682501
5070 -- Cursor added to get the balance type ids of 'Arrears' and 'Not Taken' balance
5071 -- as the Further Element Information descriptive flexfield are not set for
5072 -- the mentioned balances. Refer to Bug 2527761 for more information.
5073 CURSOR c_get_bal_type(l_bal_name varchar2) IS
5074 SELECT balance_type_id
5075 FROM pay_balance_types
5076 WHERE balance_name = p_ele_name || l_bal_name
5077 AND business_group_id + 0 = p_business_group_id;
5078
5079 BEGIN
5080
5081 /** Bug 566328: The select statement below is modified to put business group id
5082 in the where clause. Because now it is allowing to enter deduction with
5083 same name in different business groups( ref. Bug 502307), selection only by
5084 element name will fetch more than one row and raise error. **/
5085
5086 select element_type_id
5087 , element_information11
5088 into l_fee_ele_id
5089 , l_fee_accrued_bal_id
5090 from pay_element_types_f
5091 where element_name = p_ele_name||' Fees'
5092 and p_del_sess_date between effective_start_date
5093 and effective_end_date
5094 and business_group_id + 0 = p_business_group_id ;
5095
5096 -- Garnishment Rewrite
5097 -- Element id for priority element
5098 l_pri_ele_id := NULL;
5099 Open c_get_priority;
5100 Fetch c_get_priority into l_pri_ele_id;
5101 Close c_get_priority;
5102
5103 assoc_eles(1) := fnd_number.canonical_to_number(p_ele_info_18); -- Special Inputs ele
5104 assoc_eles(2) := fnd_number.canonical_to_number(p_ele_info_19); -- Special Features ele
5105 assoc_eles(3) := l_fee_ele_id; -- Fee ele
5106 assoc_eles(4) := fnd_number.canonical_to_number(p_ele_info_20); -- Verifier ele
5107 assoc_eles(5) := fnd_number.canonical_to_number(p_ele_info_5); -- Calculator ele
5108 assoc_eles(6) := p_ele_type_id; -- Base ele
5109 assoc_eles(7) := l_pri_ele_id; -- Priority ele
5110
5111 l_num_assoc_eles := 7;
5112
5113 assoc_bals(1) := fnd_number.canonical_to_number(p_ele_info_10); -- Primary bal
5114 assoc_bals(2) := fnd_number.canonical_to_number(p_ele_info_11); -- Accrued bal
5115 assoc_bals(3) := fnd_number.canonical_to_number(p_ele_info_12); -- Arrears bal
5116 assoc_bals(4) := fnd_number.canonical_to_number(p_ele_info_13); -- Not Taken bal
5117 assoc_bals(5) := fnd_number.canonical_to_number(p_ele_info_14); -- To Bond bal
5118 assoc_bals(6) := fnd_number.canonical_to_number(p_ele_info_15); -- Fee bal
5119 assoc_bals(7) := fnd_number.canonical_to_number(p_ele_info_16); -- Additional Amt bal
5120 assoc_bals(8) := fnd_number.canonical_to_number(p_ele_info_17); -- Replacement Amt bal
5121 assoc_bals(9) := fnd_number.canonical_to_number(p_ele_info_8); -- Vol Dedns bal
5122
5123 assoc_bals(10) := l_fee_accrued_bal_id; -- Fee Accrued bal
5124
5125 -- Bug 3682501
5126 -- Populating the balance_type_id for Arrears balance
5127 v_bal_type_id := null;
5128 open c_get_bal_type(' Arrears');
5129 fetch c_get_bal_type into v_bal_type_id;
5130 if v_bal_type_id is not null then
5131 assoc_bals(3) := fnd_number.canonical_to_number(v_bal_type_id);
5132 end if;
5133 close c_get_bal_type;
5134
5135 -- Bug 3682501
5136 -- Populating the balance_type_id for Not Taken balance
5137 v_bal_type_id := null;
5138 open c_get_bal_type(' Not Taken');
5139 fetch c_get_bal_type into v_bal_type_id;
5140 if v_bal_type_id is not null then
5141 assoc_bals(4) := fnd_number.canonical_to_number(v_bal_type_id);
5142 end if;
5143 close c_get_bal_type;
5144
5145 l_num_assoc_bals := 10;
5146
5147 -- Populate vars.
5148 v_del_val_end := nvl(p_del_val_end_date, c_end_of_time);
5149 v_del_val_start := nvl(p_del_val_start_date, sysdate);
5150 v_del_sess_date := nvl(p_del_sess_date, sysdate);
5151 --
5152 -- Do not allow direct deletion of any associated element, these will be
5153 -- deleted by deleting the "base" ele.
5154 --
5155 IF p_ele_name like '% Special Features' THEN
5156 hr_utility.set_location('hr_us_garn_gen.delete_dedn',40);
5157 hr_utility.set_message(801,'PAY_xxxx_CANNOT_DEL_ELE');
5158 hr_utility.raise_error;
5159 END IF;
5160 --
5161 -- Do not allow deletion of Special Inputs ele, delete by deleting base ele:
5162 --
5163 IF p_ele_name like '% Special Inputs' THEN
5164 hr_utility.set_location('hr_us_garn_gen.delete_dedn',40);
5165 hr_utility.set_message(801,'PAY_xxxx_CANNOT_DEL_ELE');
5166 hr_utility.raise_error;
5167 END IF;
5168 --
5169 -- Do not allow deletion of Calculator element, delete by deleting base ele:
5170 --
5171 IF p_ele_name like '% Calculator' THEN
5172 hr_utility.set_location('hr_us_garn_gen.delete_dedn',40);
5173 hr_utility.set_message(801,'PAY_xxxx_CANNOT_DEL_ELE');
5174 hr_utility.raise_error;
5175 END IF;
5176 --
5177 -- Do not allow deletion of Verifier element, delete by deleting base ele:
5178 --
5179 IF p_ele_name like '% Verifier' THEN
5180 hr_utility.set_location('hr_us_garn_gen.delete_dedn',40);
5181 hr_utility.set_message(801,'PAY_xxxx_CANNOT_DEL_ELE');
5182 hr_utility.raise_error;
5183 END IF;
5184 --
5185 -- Do not allow deletion of Fees element, delete by deleting base ele:
5186 --
5187 IF p_ele_name like '% Fees' THEN
5188 hr_utility.set_location('hr_us_garn_gen.delete_dedn',40);
5189 hr_utility.set_message(801,'PAY_xxxx_CANNOT_DEL_ELE');
5190 hr_utility.raise_error;
5191 END IF;
5192
5193 IF p_ele_name like '% Priority' THEN
5194 hr_utility.set_location('hr_us_garn_gen.delete_dedn',40);
5195 hr_utility.set_message(801,'PAY_xxxx_CANNOT_DEL_ELE');
5196 hr_utility.raise_error;
5197 END IF;
5198 --
5199 -- Delete base ele frequency rule info:
5200 --
5201 -- Deletion of any Deduction Frequency Rules should be handled
5202 -- by cascade delete according to db constraint.
5203 -- 14 June 1995: Bug# 271622 notes that ele freq rules are NOT deleted
5204 -- when a deduction is deleted. Constraint not working...
5205 --
5206
5207 OPEN get_freqrule;
5208 LOOP
5209 FETCH get_freqrule INTO v_freqrule_id;
5210 EXIT WHEN get_freqrule%NOTFOUND;
5211
5212 begin
5213
5214 DELETE FROM pay_freq_rule_periods
5215 WHERE ele_payroll_freq_rule_id = v_freqrule_id;
5216
5217 exception when NO_DATA_FOUND then
5218 null; -- No freq rule periods exist.
5219 end;
5220
5221 begin
5222
5223 DELETE FROM pay_ele_payroll_freq_rules
5224 WHERE ele_payroll_freq_rule_id = v_freqrule_id;
5225
5226 exception when NO_DATA_FOUND then
5227 null; -- No freq rule exists.
5228 end;
5229
5230 END LOOP;
5231 CLOSE get_freqrule;
5232
5233 --
5234 -- Loop to delete formula result rules, status proc rules, and formulae.
5235 --
5236 -- Note: deletion of formula result rules is handled by
5237 -- del_status_processing_rules.
5238 --
5239
5240 FOR i in 1..l_num_assoc_eles LOOP
5241
5242 IF assoc_eles(i) IS NOT NULL THEN
5243
5244 -- Get formula_ids to delete from various tables:
5245 -- FF_FORMULAS_F
5246 -- FF_FDI_USAGES_F
5247 -- FF_COMPILED_INFO_F
5248 --
5249 OPEN get_formulae(assoc_eles(i));
5250 LOOP
5251
5252 FETCH get_formulae INTO v_ff_id;
5253 EXIT WHEN get_formulae%NOTFOUND;
5254
5255 begin
5256
5257 DELETE FROM ff_formulas_f
5258 WHERE formula_id = v_ff_id;
5259
5260 exception when NO_DATA_FOUND then
5261 null;
5262 end;
5263
5264 begin
5265
5266 DELETE FROM ff_fdi_usages_f
5267 WHERE formula_id = v_ff_id;
5268
5269 exception when NO_DATA_FOUND then
5270 null;
5271 end;
5272
5273 begin
5274
5275 DELETE FROM ff_compiled_info_f
5276 WHERE formula_id = v_ff_id;
5277
5278 exception when NO_DATA_FOUND then
5279 null;
5280 end;
5281
5282 END LOOP;
5283 CLOSE get_formulae;
5284
5285 OPEN get_spr(assoc_eles(i));
5286 LOOP
5287
5288 FETCH get_spr INTO v_spr_id;
5289 EXIT WHEN get_spr%NOTFOUND;
5290
5291 hr_utility.set_location('hr_us_garn_gen.delete_dedn', 10);
5292 hr_elements.del_status_processing_rules(
5293 p_element_type_id => assoc_eles(i),
5294 p_delete_mode => v_del_mode,
5295 p_val_session_date => v_del_sess_date,
5296 p_val_start_date => v_del_val_start,
5297 p_val_end_date => v_del_val_end,
5298 p_startup_mode => v_startup_mode);
5299
5300 END LOOP;
5301 CLOSE get_spr;
5302
5303 END IF;
5304
5305 END LOOP;
5306
5307 --
5308 -- Delete all OLD formulae created during upgrades.
5309 --
5310 OPEN get_old_formulae(p_ele_name);
5311 LOOP
5312 FETCH get_old_formulae
5313 INTO v_ff_id;
5314 EXIT WHEN get_old_formulae%NOTFOUND;
5315
5316 begin
5317
5318 DELETE FROM ff_formulas_f
5319 WHERE formula_id = v_ff_id;
5320
5321 exception when NO_DATA_FOUND then
5322 null;
5323 end;
5324
5325 begin
5326
5327 DELETE FROM ff_fdi_usages_f
5328 WHERE formula_id = v_ff_id;
5329
5330 exception when NO_DATA_FOUND then
5331 null;
5332 end;
5333
5334 begin
5335
5336 DELETE FROM ff_compiled_info_f
5337 WHERE formula_id = v_ff_id;
5338
5339 exception when NO_DATA_FOUND then
5340 null;
5341 end;
5342
5343 END LOOP;
5344 CLOSE get_old_formulae;
5345
5346 --
5347 -- Delete all associated balances.
5348 --
5349 -- Balance type ids of associated balances for this element are passed in
5350 -- via the p_ele_info_xx params.
5351 -- Note, all balance feeds for each balance type are deleted
5352 -- by del_balance_type_cascade.
5353 --
5354 FOR i in 1..l_num_assoc_bals LOOP
5355
5356 IF assoc_bals(i) IS NOT NULL THEN
5357
5358 hr_utility.set_location('hr_us_garn_gen.delete_dedn', 50);
5359 hr_balances.del_balance_type_cascade (
5360 p_balance_type_id => assoc_bals(i),
5361 p_legislation_code => g_template_leg_code,
5362 p_mode => v_del_mode);
5363
5364 hr_utility.set_location('hr_us_garn_gen.delete_dedn', 60);
5365 DELETE FROM pay_balance_types
5366 WHERE balance_type_id = assoc_bals(i);
5367
5368 END IF;
5369
5370 END LOOP;
5371
5372 --
5373 -- Now delete associated eles:
5374 --
5375 -- Note: Input value deletion is handled by del_3p_element_type
5376 --
5377
5378 FOR j in 1..l_num_assoc_eles LOOP
5379
5380 IF assoc_eles(j) IS NOT NULL THEN
5381
5382 select processing_priority
5383 into v_assoc_ele_priority
5384 from pay_element_types_f
5385 where element_type_id = assoc_eles(j)
5386 and v_del_sess_date between effective_start_date
5387 and effective_end_date;
5388
5389 hr_utility.set_location('hr_us_garn_gen.delete_dedn', 20);
5390 hr_elements.chk_del_element_type (
5391 p_mode => v_del_mode,
5392 p_element_type_id => assoc_eles(j),
5393 p_processing_priority => v_assoc_ele_priority,
5394 p_session_date => v_del_sess_date,
5395 p_val_start_date => v_del_val_start,
5396 p_val_end_date => v_del_val_end);
5397
5398 hr_utility.set_location('hr_us_garn_gen.delete_dedn', 30);
5399 hr_elements.del_3p_element_type (
5400 p_element_type_id => assoc_eles(j),
5401 p_delete_mode => v_del_mode,
5402 p_val_session_date => v_del_sess_date,
5403 p_val_start_date => v_del_val_start,
5404 p_val_end_date => v_del_val_end,
5405 p_startup_mode => v_startup_mode);
5406 --
5407 -- Delete element type record:
5408 -- Remember, we're 'ZAP'ing, no need to worry about date-effective delete.
5409 --
5410 hr_utility.set_location('hr_us_garn_gen.delete_dedn', 35);
5411 delete from PAY_ELEMENT_TYPES_F
5412 where element_type_id = assoc_eles(j);
5413
5414 END IF;
5415
5416 END LOOP;
5417
5418 -- ********
5419 -- END LOOP TO DELETE ELES
5420 -- ********
5421
5422 -- Bug 3682501
5423 -- Code added for deleting the template
5424 open get_template_id;
5425 fetch get_template_id into v_template_id;
5426 close get_template_id;
5427
5428 -- Check if the element to be deleted was created using CORE template. If yes
5429 -- delete the template created while creating the element.
5430 if v_template_id is not null then
5431 hr_utility.set_location('hr_us_garn_gen.delete_dedn', 45);
5432 -- Bug 4680388
5433 -- Modified the code to use delete_user_structure
5434 pay_element_template_api.delete_user_structure(
5435 p_drop_formula_packages => TRUE
5436 ,p_template_id => v_template_id);
5437 end if;
5438
5439
5440 END delete_dedn;
5441
5442 END hr_us_garn_gen;