[Home] [Help]
PACKAGE BODY: APPS.PYNEGNET01
Source
1 PACKAGE BODY pynegnet01 AS
2 /*$Header: pyusngn1.pkb 115.4 99/07/17 06:45:06 porting ship $*/
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1996 Oracle Corporation US *
7 * *
8 * *
9 * All rights reserved. *
10 * *
11 * This material has been provided pursuant to an agreement *
12 * containing restrictions on its use. The material is also *
13 * protected by copyright law. No part of this material may *
14 * be copied or distributed, transmitted or transcribed, in *
15 * any form or by any means, electronic, mechanical, magnetic, *
16 * manual, or otherwise, or disclosed to third parties without *
17 * the express written permission of Oracle Corporation US *
18 * *
19 * *
20 * *
21 ****************************************************************** */
22 /*
23 Name : pynegnet01 (BODY)
24
25 Description : This package declares procedures required to
26 create all objects for processing negative net
27 values in payroll processing.
28
29
30 Change List
31 -----------
32
33 Version Date Author ER/CR No. Description of Change
34 -------+---------+----------+---------+--------------------------
35 110.0 03/03/98 M.Lisiecki Bug 563295.
36 Based on original pyusngnt.pkb and
37 modified to accomodate City_HT_WK,
38 Workers Compensation EE and
39 Workers Compensation2 EE.
40 Additions:
41 Bug 585429. Changed formula's CURR_ARR to be a negative value
42 and changed all arrears element's balance feeds from add to
43 substr and vice versa.
44 Update all formula results for arrears elements to the
45 negative ammount.
46 110.1 14/04/98 M.Lisiecki Removed changes introduced to fix bug 585429 as it proved not
47 to be a bug.
48 115.1 21/04/99 S.Grant Multi-radix changes.
49 115.4 16-jun-1999 achauhan replaced dbms_output with hr_utility.trace
50 ================================================================= */
51 --
52 -- ====================== declare tables ============================
53 --
54 TYPE char_tabtype IS TABLE OF VARCHAR2(50)
55 INDEX BY BINARY_INTEGER;
56 TYPE num_tabtype IS TABLE OF NUMBER
57 INDEX BY BINARY_INTEGER;
58 -- ==================================================================
59 arrear_bal_name_list char_tabtype;
60 proto_bal_name_list char_tabtype;
61 Whld_bal_feed_name_list char_tabtype;
62 --
63 Element_name_list char_tabtype;
64 Element_priority_list num_tabtype;
65 --
66 post_inp_value_list char_tabtype;
67 post_res1_out_name_list char_tabtype;
68 post_res1_inp_name_list char_tabtype;
69 post_res1_out_unit_list char_tabtype;
70 post_res4_out_name_list char_tabtype;
71 --
72 arr_inp_value_list char_tabtype;
73 arr_unit_list char_tabtype;
74 arr_bal_feed_name_list char_tabtype;
75 arr_bal_feed_rule_list char_tabtype;
76 --
77 ff_repl1 char_tabtype;
78 ff_repl2 char_tabtype;
79 ff_repl3 char_tabtype;
80 --
81 jd_list char_tabtype;
82 --
83 vertex_res_out_name_list char_tabtype;
84 --
85 -- =================================================================
86 --
87 arrear_bal_id_list num_tabtype;
88 whld_bal_feed_id_list num_tabtype;
89 --
90 arr_element_id_list num_tabtype;
91 arr_inp_id_list num_tabtype;
92 arr_bal_feed_id_list num_tabtype;
93 arr_assign_list num_tabtype;
94 --
95 post_element_id_list num_tabtype;
96 post_inp_id_list num_tabtype;
97 --
98 ff_id_list num_tabtype;
99 --
100 -- ===================== end tables ================================
101 --
102 -- ===================== variables =================================
103 --
104 v_ff_post_text VARCHAR2(3000);
105 v_effective_start_date DATE := TO_DATE('01/01/0001','DD/MM/YYYY');
106 v_effective_end_date DATE := TO_DATE('31/12/4712','DD/MM/YYYY');
107 v_sysdate DATE := SYSDATE;
108 --
109 v_legislation_code VARCHAR2(30) := 'US';
110 v_user VARCHAR2(20) := USER;
111 v_uid NUMBER := UID;
112 --
113 v_formula_type_id NUMBER;
114 v_uom VARCHAR2(10) := 'Money';
115 v_currency_code VARCHAR2(10) := 'USD';
116 --
117 -- The business group name or id is needed in some cases.
118 --
119 v_business_group_name VARCHAR2(80) ;
120 v_business_group_id NUMBER ;
121 --
122 v_arr_inp_cnt BINARY_INTEGER := 1;
123 v_post_inp_cnt BINARY_INTEGER := 1;
124 --
125 v_total_elements BINARY_INTEGER := 18;
126 v_total_balances BINARY_INTEGER := 13;
127 --
128 v_run_results_exist VARCHAR2(1) := 'N';
129 --
130 -- Bug 563295. mlisieck.
131 v_tax_arrears_id NUMBER := 0;
132
133 --
134 -- ===================== end variables =============================
135 --
136 -- ===================== initialize variables ======================
137 --
138 PROCEDURE init_ff_post_text
139 IS
140 BEGIN
141 --
142 v_ff_post_text := '
143 /* $Header: pyusngn1.pkb 115.4 99/07/17 06:45:06 porting ship $ */
144 Inputs are
145 jurisdiction(text),
146 jd_su(text),
147 jd_wk(text),
148 jd_sc(text),
149 jd_rs(text)
150
151 default for jd_sc is ''00-00000''
152
153 CUR_ARR = 0
154 CUR_NET = NET_ASG_GRE_RUN
155 CUR_TAX = {REPL1}WITHHELD_ASG{REPL2}GRE_RUN
156 CUR_ARR_TAKEN = {REPL1}ARREARS_ASG{REPL2}GRE_RUN
157
158 CUR_STEP = 0
159 ARR_RET_MSG = ''{REPL3} Arrears created''
160
161
162 IF CUR_NET >= 0 then
163
164 RETURN
165
166 ELSE
167 (
168 if CUR_TAX <> 0 and CUR_ARR_TAKEN = 0 THEN
169 (
170 CUR_STEP = CUR_TAX + CUR_NET
171
172 IF CUR_STEP <= 0 THEN
173
174 CUR_ARR = CUR_TAX
175 ELSE
176 CUR_ARR = -1 * CUR_NET
177 )
178 else
179
180 CUR_ARR = 0
181 ARR_RET_MSG = '' ''
182 )
183
184 RETURN
185 jurisdiction,
186 jd_su,
187 jd_wk,
188 jd_sc,
189 jd_rs,
190 CUR_ARR,
191 ARR_RET_MSG'
192 ;
193 --
194 select formula_type_id
195 into v_formula_type_id
196 from ff_formula_types
197 where formula_type_name = 'Oracle Payroll'
198 ;
199
200 END init_ff_post_text;
201
202 -- ===================== end initialize variables ==================
203 --
204 -- =================== initialize tables ===========================
205 --
206 --
207 -- ===================== initialize replacements for ff text =======
208 --
209 PROCEDURE init_ff_repl
210 IS
211 BEGIN
212
213 ff_repl1 (1) := 'SUI_EE_';
214 ff_repl2 (1) := '_JD_';
215 ff_repl3 (1) := 'SUI_EE ';
216
217 ff_repl1 (2) := 'CITY_';
218 ff_repl2 (2) := '_JD_';
219 ff_repl3 (2) := 'CITY_WK ';
220
221 -- Bug 563295. mlisieck.
222 ff_repl1 (3) := 'HEAD_TAX_';
223 ff_repl2 (3) := '_JD_';
224 ff_repl3 (3) := 'HEAD_TAX';
225
226 ff_repl1 (4) := 'COUNTY_';
227 ff_repl2 (4) := '_JD_';
228 ff_repl3 (4) := 'COUNTY_WK ';
229
230 ff_repl1 (5) := 'SDI_EE_';
231 ff_repl2 (5) := '_JD_';
232 ff_repl3 (5) := 'SDI_EE ';
233
234 ff_repl1 (6) := 'SIT_';
235 ff_repl2 (6) := '_JD_';
236 ff_repl3 (6) := 'SIT_WK ';
237
238 ff_repl1 (7) := 'SCHOOL_';
239 ff_repl2 (7) := '_JD_';
240 ff_repl3 (7) := 'CITY_SC_WK ';
241
242 ff_repl1 (8) := 'SCHOOL_';
243 ff_repl2 (8) := '_JD_';
244 ff_repl3 (8) := 'COUNTY_SC_WK ';
245
246 ff_repl1 (9) := 'WORKERS_COMP_';
247 ff_repl2 (9) := '_JD_';
248 ff_repl3 (9) := 'WORKERS_COMP';
249
250 ff_repl1 (10) := 'WORKERS_COMP2_';
251 ff_repl2 (10) := '_JD_';
252 ff_repl3 (10) := 'WORKERS_COMPE2';
253
254 ff_repl1 (11) := 'SCHOOL_';
255 ff_repl2 (11) := '_JD_';
256 ff_repl3 (11) := 'CITY_SC_RS ';
257
258 ff_repl1 (12) := 'SCHOOL_';
259 ff_repl2 (12) := '_JD_';
260 ff_repl3 (12) := 'COUNTY_SC_RS ';
261
262 ff_repl1 (13) := 'CITY_';
263 ff_repl2 (13) := '_JD_';
264 ff_repl3 (13) := 'CITY_RS ';
265
266 ff_repl1 (14) := 'COUNTY_';
267 ff_repl2 (14) := '_JD_';
268 ff_repl3 (14) := 'COUNTY_RS ';
269
270 ff_repl1 (15) := 'SIT_';
271 ff_repl2 (15) := '_JD_';
272 ff_repl3 (15) := 'SIT_RS ';
273
274 ff_repl1 (16) := 'FIT_';
275 ff_repl2 (16) := '_';
276 ff_repl3 (16) := 'FIT ';
277
278 ff_repl1 (17) := 'MEDICARE_EE_';
279 ff_repl2 (17) := '_';
280 ff_repl3 (17) := 'MEDICARE_EE ';
281
282 ff_repl1 (18) := 'SS_EE_';
283 ff_repl2 (18) := '_';
284 ff_repl3 (18) := 'SS_EE ';
285
286 END init_ff_repl;
287 --
288 -- ===================== init_arrear_bal_name =====================
289 --
290 PROCEDURE init_arrear_bal_name
291 IS
292 BEGIN
293
294 arrear_bal_name_list (1) := 'FIT Arrears';
295 arrear_bal_name_list (2) := 'Medicare EE Arrears';
296 arrear_bal_name_list (3) := 'Head Tax Arrears';
297 arrear_bal_name_list (4) := 'SS EE Arrears';
298 arrear_bal_name_list (5) := 'SUI EE Arrears';
299 arrear_bal_name_list (6) := 'SDI EE Arrears';
300 arrear_bal_name_list (7) := 'SIT Arrears';
301 arrear_bal_name_list (8) := 'County Arrears';
302 arrear_bal_name_list (9) := 'Workers Comp Arrears';
303 arrear_bal_name_list (10) := 'Workers Comp2 Arrears';
304 arrear_bal_name_list (11) := 'City Arrears';
305 arrear_bal_name_list (12) := 'School Arrears';
306 arrear_bal_name_list (13) := 'Tax Arrears';
307 END init_arrear_bal_name;
308 --
309 -- ===================== init_proto_bal_name =====================
310 --
311 PROCEDURE init_proto_bal_name
312 IS
313 BEGIN
314 proto_bal_name_list (1) := 'FIT Withheld';
315 proto_bal_name_list (2) := 'Medicare EE Withheld';
316 proto_bal_name_list (3) := 'Head Tax Withheld';
317 proto_bal_name_list (4) := 'SS EE Withheld';
318 proto_bal_name_list (5) := 'SUI EE Withheld';
319 proto_bal_name_list (6) := 'SDI EE Withheld';
320 proto_bal_name_list (9) := 'SIT Withheld';
321 proto_bal_name_list (8) := 'County Withheld';
322 proto_bal_name_list (9) := 'Workers Comp Withheld';
323 proto_bal_name_list (10) := 'Workers Comp2 Withheld';
324 proto_bal_name_list (11) := 'City Withheld';
325 proto_bal_name_list (12) := 'School Withheld';
326 proto_bal_name_list (13) := 'Tax Deductions';
327 END init_proto_bal_name;
328 --
329 -- ===================== init_element_name =====================
330 --
331 PROCEDURE init_element_name
332 IS
333 BEGIN
334 element_name_list (1) := 'SUI_EE_';
335 element_name_list (2) := 'CITY_WK_';
336 element_name_list (3) := 'CITY_HT_WK_';
337 element_name_list (4) := 'COUNTY_WK_';
338 element_name_list (5) := 'SDI_EE_';
339 element_name_list (6) := 'SIT_WK_';
340 element_name_list (7) := 'CITY_SC_WK_';
341 element_name_list (8) := 'COUNTY_SC_WK_';
342 element_name_list (9) := 'WORKERS_COMP_EE_';
343 element_name_list (10) := 'WORKERS_COMP2_EE_';
344 element_name_list (11) := 'CITY_SC_RS_';
345 element_name_list (12) := 'COUNTY_SC_RS_';
346 element_name_list (13) := 'CITY_RS_';
347 element_name_list (14) := 'COUNTY_RS_';
348 element_name_list (15) := 'SIT_RS_';
349 element_name_list (16) := 'FIT_';
350 element_name_list (17) := 'MEDICARE_EE_';
351 element_name_list (18) := 'SS_EE_';
352 --
353 element_priority_list (1) := 4250;
354 element_priority_list (2) := 4250;
355 element_priority_list (3) := 4250;
356 element_priority_list (4) := 4250;
357 element_priority_list (5) := 4250;
358 element_priority_list (6) := 4250;
359
360 element_priority_list (7) := 4260;
361 element_priority_list (8) := 4260;
362 element_priority_list (9) := 4260;
363 element_priority_list (10) := 4260;
364 element_priority_list (11) := 4260;
365 element_priority_list (12) := 4260;
366 element_priority_list (13) := 4260;
367 element_priority_list (14) := 4260;
368 element_priority_list (15) := 4260;
369
370 element_priority_list (16) := 4270;
371 element_priority_list (17) := 4270;
372 element_priority_list (18) := 4270;
373
374 END init_element_name;
375 --
376 -- ===================== init_arr_assign_list =====================
377 --
378 PROCEDURE init_arr_assign_list
379 IS
380 BEGIN
381 arr_assign_list (1) := 5;
382 arr_assign_list (2) := 11;
383 arr_assign_list (3) := 3;
384 arr_assign_list (4) := 8;
385 arr_assign_list (5) := 6;
386 arr_assign_list (6) := 12;
387 arr_assign_list (7) := 12;
388 arr_assign_list (8) := 12;
389 arr_assign_list (9) := 9;
390 arr_assign_list (10) := 10;
391 arr_assign_list (11) := 12;
392 arr_assign_list (12) := 12;
393 arr_assign_list (13) := 11;
394 arr_assign_list (14) := 8;
395 arr_assign_list (15) := 7;
396 arr_assign_list (16) := 1;
397 arr_assign_list (17) := 2;
398 arr_assign_list (18) := 4;
399 END init_arr_assign_list;
400 --
401 -- ===================== init_other_variables =====================
402 PROCEDURE init_other_variables
403 IS
404 el_cnt binary_integer := 1;
405 total_el binary_integer;
406 inp_cnt BINARY_INTEGER := 1;
407 bal_cnt BINARY_INTEGER := 1;
408 total_post_inp BINARY_INTEGER := 5;
409 total_arr_inp BINARY_INTEGER := 2;
410
411 cursor crs_post_element_type_id (el_cnt number) is
412 select element_type_id from pay_element_types_f
413 where element_name = element_name_list (el_cnt) || 'POST_VERTEX';
414
415 cursor crs_post_input_value_id (p_element_type_id number, p_inp_cnt number) is
416 select input_value_id from pay_input_values_f
417 where element_type_id = p_element_type_id and
418 name = post_inp_value_list(p_inp_cnt);
419
420 cursor crs_arr_element_type_id (el_count number) is
421 select element_type_id from pay_element_types_f
422 where element_name = element_name_list (el_count) || 'ARR';
423
424 cursor crs_arr_input_value_id (p_element_type_id number, p_inp_cnt number) is
425 select input_value_id from pay_input_values_f
426 where element_type_id = p_element_type_id and
427 name = arr_inp_value_list(p_inp_cnt);
428
429 cursor crs_arr_bal_id (p_bal_cnt number) is
430 select balance_type_id from pay_balance_types
431 where balance_name = arrear_bal_name_list (p_bal_cnt);
432
433
434 BEGIN
435
436 total_el := 18;
437
438 -- need to populate ids of elements that will not be created.
439 --
440 while (el_cnt <= total_el) loop
441
442 if el_cnt not in (3,9,10) then
443
444 -- post_vertex element
445 open crs_post_element_type_id(el_cnt);
446 fetch crs_post_element_type_id into post_element_id_list (el_cnt);
447
448 -- post_vertex element's inputs
449 inp_cnt := 1;
450 while inp_cnt <= total_post_inp loop
451 open crs_post_input_value_id (post_element_id_list (el_cnt), inp_cnt);
452 fetch crs_post_input_value_id into post_inp_id_list ((el_cnt-1)*5+inp_cnt);
453 close crs_post_input_value_id;
454 inp_cnt := inp_cnt + 1;
455 end loop;
456
457 close crs_post_element_type_id;
458
459 -- arrears element
460 open crs_arr_element_type_id(el_cnt);
461 fetch crs_arr_element_type_id into arr_element_id_list (el_cnt);
462
463 inp_cnt := 1;
464 -- arrears element's inputs
465 while inp_cnt <= total_arr_inp loop
466 open crs_arr_input_value_id (arr_element_id_list (el_cnt), inp_cnt);
467 fetch crs_arr_input_value_id into arr_inp_id_list((el_cnt-1)*2+inp_cnt);
468 close crs_arr_input_value_id;
469 inp_cnt := inp_cnt + 1;
470 end loop;
471
472 close crs_arr_element_type_id;
473
474 end if;
475
476 el_cnt := el_cnt + 1;
477
478 end loop;
479 --
480 while bal_cnt <= v_total_balances loop
481 open crs_arr_bal_id (bal_cnt);
482 fetch crs_arr_bal_id into arrear_bal_id_list(bal_cnt);
483 close crs_arr_bal_id;
484 bal_cnt := bal_cnt + 1;
485 end loop;
486
487 END init_other_variables;
488 --
489 -- ===================== init_jd_list =====================
490 --
491 PROCEDURE init_jd_list
492 IS
493 BEGIN
494 jd_list (1) := 'JD_WK';
495 jd_list (2) := 'JD_WK';
496 jd_list (3) := 'JD_RS';
497 jd_list (4) := 'JD_WK';
498 jd_list (5) := 'JD_WK';
499 jd_list (6) := 'JD_SC';
500 jd_list (7) := 'JD_SC';
501 jd_list (8) := 'JD_SC';
502 jd_list (9) := 'JD_WK';
503 jd_list (10) := 'JD_WK';
504 jd_list (11) := 'JD_SC';
505 jd_list (12) := 'JD_RS';
506 jd_list (13) := 'JD_RS';
507 jd_list (14) := 'JD_RS';
508 jd_list (15) := 'JD_RS';
509 jd_list (16) := 'JD_RS';
510 jd_list (17) := 'JD_RS';
511 jd_list (18) := 'JD_XX';
512 END init_jd_list;
513 --
514 -- ===================== init_post_inp_value =====================
515 --
516 PROCEDURE init_post_inp_value
517 IS
518 BEGIN
519 post_inp_value_list (1) := 'Jurisdiction';
520 post_inp_value_list (2) := 'jd_su';
521 post_inp_value_list (3) := 'jd_wk';
522 post_inp_value_list (4) := 'jd_sc';
523 post_inp_value_list (5) := 'jd_rs';
524 END init_post_inp_value;
525 --
526 -- ===================== init_post_res1_out =====================
527 --
528 PROCEDURE init_post_res1_out
529 IS
530 BEGIN
531 post_res1_out_name_list (1) := 'CUR_ARR';
532 post_res1_out_name_list (2) := 'JURISDICTION';
533 END init_post_res1_out;
534 --
535 -- ===================== init_post_res1_inp =====================
536 --
537 PROCEDURE init_post_res1_inp
538 IS
539 BEGIN
540 post_res1_inp_name_list (1) := 'Pay Value';
541 post_res1_inp_name_list (2) := 'Jurisdiction';
542 END init_post_res1_inp;
543 --
544 -- ===================== init_post_res1_unit =====================
545 --
546 PROCEDURE init_post_res1_unit
547 IS
548 BEGIN
549 post_res1_out_unit_list (1) := 'Money';
550 post_res1_out_unit_list (2) := 'Character';
551 END init_post_res1_unit;
552 --
553 -- ===================== init_post_res4_out =====================
554 --
555 PROCEDURE init_post_res4_out
556 IS
557 BEGIN
558 post_res4_out_name_list (1) := 'JD_WK';
559 post_res4_out_name_list (2) := 'JD_SU';
560 post_res4_out_name_list (3) := 'JD_WK';
561 post_res4_out_name_list (4) := 'JD_SC';
562 post_res4_out_name_list (5) := 'JD_RS';
563 END init_post_res4_out;
564 --
565 -- ===================== init_arr_inp_value =====================
566 --
567 PROCEDURE init_arr_inp_value
568 IS
569 BEGIN
570 arr_inp_value_list (1) := 'Pay Value';
571 arr_inp_value_list (2) := 'Jurisdiction';
572 END init_arr_inp_value;
573 --
574 -- ===================== init_arr_unit =====================
575 --
576 PROCEDURE init_arr_unit
577 IS
578 BEGIN
579 arr_unit_list (1) := 'Money';
580 arr_unit_list (2) := 'Character';
581 END init_arr_unit;
582 --
583 -- ===================== init_arr_bal_feed_name =====================
584 --
585 PROCEDURE init_arr_bal_feed_name
586 IS
587 BEGIN
588 arr_bal_feed_name_list (1) := 'Tax Deductions';
589 arr_bal_feed_name_list (2) := 'Net';
590 arr_bal_feed_name_list (3) := 'Payments';
591 END init_arr_bal_feed_name;
592 --
593 -- ===================== init_arr_bal_feed_id =====================
594 --
595 PROCEDURE init_arr_bal_feed_id
596 IS
597 lc_name VARCHAR2(80);
598 feed_cnt NUMBER := 1;
599 total_feed NUMBER := 3;
600 ln_id NUMBER := 0;
601 BEGIN
602 --
603 hr_utility.set_location('pynegnet01.init_arr_bal_feed_id',1);
604 --
605 WHILE feed_cnt <= total_feed
606 LOOP
607 lc_name := arr_bal_feed_name_list (feed_cnt);
608 SELECT balance_type_id
609 INTO ln_id
610 FROM pay_balance_types
611 WHERE balance_name = lc_name;
612 arr_bal_feed_id_list (feed_cnt) := ln_id;
613 feed_cnt := feed_cnt + 1;
614 END LOOP;
615 --
616 hr_utility.set_location('pynegnet01.init_arr_bal_feed_id',2);
617 --
618 END init_arr_bal_feed_id;
619 --
620 -- ===================== init_vertex_results =====================
621 --
622 PROCEDURE init_vertex_results is
623 BEGIN
624 vertex_res_out_name_list (1) := 'SUI_GEO';
625 vertex_res_out_name_list (2) := 'SUI_GEO';
626 vertex_res_out_name_list (3) := 'WCITY_GEO';
627 vertex_res_out_name_list (4) := 'SCHOOL_GEO';
628 vertex_res_out_name_list (5) := 'RCITY_GEO';
629 END init_vertex_results;
630 --
631 -- ===================== init_whld_bal_feed_name =====================
632 --
633 PROCEDURE init_whld_bal_feed_name
634 IS
635 BEGIN
636 whld_bal_feed_name_list (1) := 'SUI EE Withheld';
637 whld_bal_feed_name_list (2) := 'City Withheld';
638 whld_bal_feed_name_list (3) := 'Head Tax Withheld';
639 whld_bal_feed_name_list (4) := 'County Withheld';
640 whld_bal_feed_name_list (5) := 'SDI EE Withheld';
641 whld_bal_feed_name_list (6) := 'SIT Withheld';
642 whld_bal_feed_name_list (7) := 'School Withheld';
643 whld_bal_feed_name_list (8) := 'School Withheld';
644 whld_bal_feed_name_list (9) := 'Workers Comp Withheld';
645 whld_bal_feed_name_list (10) := 'Workers Comp2 Withheld';
646 whld_bal_feed_name_list (11) := 'School Withheld';
647 whld_bal_feed_name_list (12) := 'School Withheld';
648 whld_bal_feed_name_list (13) := 'City Withheld';
649 whld_bal_feed_name_list (14) := 'County Withheld';
650 whld_bal_feed_name_list (15) := 'SIT Withheld';
651 whld_bal_feed_name_list (16) := 'FIT Withheld';
652 whld_bal_feed_name_list (17) := 'Medicare EE Withheld';
653 whld_bal_feed_name_list (18) := 'SS EE Withheld';
654 END init_whld_bal_feed_name;
655 --
656 -- ===================== init_whld_bal_feed_id =====================
657 --
658 PROCEDURE init_whld_bal_feed_id
659 IS
660 lc_name VARCHAR2(80);
661 feed_cnt NUMBER := 1;
662 total_feed NUMBER := v_total_elements;
663 ln_id NUMBER := 0;
664 BEGIN
665 --
666 hr_utility.set_location('pynegnet01.init_whld_bal_feed_id',1);
667 --
668 WHILE feed_cnt <= total_feed
669 LOOP
670 lc_name := whld_bal_feed_name_list (feed_cnt);
671 SELECT balance_type_id
672 INTO ln_id
673 FROM pay_balance_types
674 WHERE balance_name = lc_name;
675 whld_bal_feed_id_list (feed_cnt) := ln_id;
676 feed_cnt := feed_cnt + 1;
677 END LOOP;
678 --
679 hr_utility.set_location('pynegnet01.init_whld_bal_feed_id',2);
680 --
681 END init_whld_bal_feed_id;
682 --
683 -- ===================== init_arr_bal_feed_rule =====================
684 --
685 PROCEDURE init_arr_bal_feed_rule
686 IS
687 BEGIN
688 arr_bal_feed_rule_list (1) := '+1';
689 arr_bal_feed_rule_list (2) := '-1';
690 arr_bal_feed_rule_list (3) := '-1';
691 END init_arr_bal_feed_rule;
692 --
693 -- ===================== init_all_tables =============================
694 --
695 PROCEDURE init_all_tables
696 IS
697 BEGIN
698 --
699 hr_utility.set_location('pynegnet01.init_all_tables',1);
700 --
701 init_jd_list;
702 init_arrear_bal_name;
703 init_proto_bal_name;
704 init_element_name;
705 init_arr_assign_list;
706 init_post_inp_value;
707 init_post_res1_out;
708 init_post_res1_inp;
709 init_post_res1_unit;
710 init_post_res4_out;
711 init_arr_inp_value;
712 init_arr_unit;
713 init_arr_bal_feed_name;
714 init_arr_bal_feed_id;
715 init_arr_bal_feed_rule;
716 init_whld_bal_feed_name;
717 init_whld_bal_feed_id;
718
719 init_other_variables;
720 init_vertex_results;
721 --
722 hr_utility.set_location('pynegnet01.init_all_tables',2);
723 --
724 END init_all_tables;
725 -- ====================== check_run_results =======================
726 FUNCTION check_run_results return varchar2
727 IS
728 --
729 l_results_exist varchar2(1) := 'N';
730 total_el BINARY_INTEGER := v_total_elements;
731 el_cnt BINARY_INTEGER := 1;
732 --
733 cursor csr_chk_run_results (p_element_string varchar2)is
734 select 'Y' results_exist
735 from dual
736 where
737 exists ( select 'x'
738 from pay_run_results rr,
739 pay_element_types_f ele
740 where
741 ele.element_type_id = rr.element_type_id
742 and ele.element_name in ( p_element_string||'POST_VERTEX', p_element_string||'ARR')
743 ) ;
744 BEGIN
745
746 total_el := v_total_elements;
747 WHILE ( el_cnt <= total_el and l_results_exist = 'N') LOOP
748
749 if el_cnt in (3,9,10) then
750
751 open csr_chk_run_results ( element_name_list(el_cnt));
752 fetch csr_chk_run_results into l_results_exist;
753 close csr_chk_run_results;
754 --
755 end if;
756 el_cnt := el_cnt + 1;
757 --
758 END LOOP;
759
760 return l_results_exist;
761 END check_run_results;
762 -- ====================== delete_bal_dim ===========================
763 --
764 PROCEDURE delete_bal_dim
765 IS
766 CURSOR bal_dim (p_bal_name VARCHAR2)
767 IS
768 SELECT db.defined_balance_id
769 FROM pay_balance_types bt,
770 pay_defined_balances db
771 WHERE bt.balance_name = p_bal_name AND
772 bt.balance_type_id = db.balance_type_id;
773 /* balances counter */
774 total_bal BINARY_INTEGER := v_total_balances;
775 bal_cnt BINARY_INTEGER := 1;
776 lc_name VARCHAR2(80);
777 ln_id NUMBER := 0;
778 BEGIN
779 --
780 hr_utility.set_location('pynegnet01.delete_bal_dim',1);
781 --
782 total_bal := v_total_balances;
783
784 WHILE bal_cnt <= total_bal
785 LOOP
786 if bal_cnt in (3,9,10) then
787 lc_name := arrear_bal_name_list (bal_cnt);
788 FOR dim_rec IN bal_dim(lc_name)
789 LOOP
790 ln_id :=dim_rec.defined_balance_id;
791 --
792 begin
793 DELETE FROM pay_defined_balances
794 WHERE defined_balance_id = ln_id;
795 EXCEPTION
796 WHEN NO_DATA_FOUND THEN NULL;
797 end;
798 END LOOP;
799 end if;
800 bal_cnt := bal_cnt + 1;
801 END LOOP;
802 --
803 hr_utility.set_location('pynegnet01.delete_bal_dim',2);
804 --
805 END delete_bal_dim;
806 --
807 -- ====================== delete_bal_feed ===========================
808 --
809 PROCEDURE delete_bal_feed
810 IS
811 CURSOR bal_dim (p_bal_name VARCHAR2)
812 IS
813 SELECT bf.balance_feed_id
814 FROM pay_balance_types bt,
815 pay_balance_feeds_f bf
816 WHERE
817 bt.balance_name = p_bal_name AND
818 bt.balance_type_id = bf.balance_type_id;
819 /* balances counter */
820
821 total_bal BINARY_INTEGER := v_total_balances;
822 bal_cnt BINARY_INTEGER := 1;
823 lc_name VARCHAR2(80);
824 ln_id NUMBER := 0;
825
826 BEGIN
827 --
828 hr_utility.set_location('pynegnet01.delete_bal_feed',1);
829 --
830 total_bal := v_total_balances;
831 WHILE bal_cnt <= total_bal
832 LOOP
833 if bal_cnt in (3,9,10) then
834 lc_name := arrear_bal_name_list (bal_cnt);
835 FOR dim_rec IN bal_dim(lc_name)
836 LOOP
837 ln_id :=dim_rec.balance_feed_id;
838 begin
839 DELETE FROM pay_balance_feeds_f
840 WHERE balance_feed_id = ln_id;
841 EXCEPTION
842 WHEN NO_DATA_FOUND THEN NULL;
843 end;
844 END LOOP;
845 end if;
846 bal_cnt := bal_cnt + 1;
847 END LOOP;
848 --
849 hr_utility.set_location('pynegnet01.delete_bal_feed',2);
850 --
851 END delete_bal_feed;
852 --
853 -- ====================== delete_bal ===========================
854 --
855 PROCEDURE delete_bal
856 IS
857 /* balances counter */
858 total_bal BINARY_INTEGER := v_total_balances;
859 bal_cnt BINARY_INTEGER := 1;
860 lc_name VARCHAR2(80);
861 ln_id NUMBER := 0;
862 BEGIN
863 --
864 hr_utility.set_location('pynegnet01.delete_bal',1);
865 --
866 total_bal := v_total_balances;
867 WHILE bal_cnt <= total_bal
868 LOOP
869 if bal_cnt in (3,9,10) then
870 lc_name := arrear_bal_name_list (bal_cnt);
871 begin
872 DELETE FROM pay_balance_types
873 WHERE balance_name = lc_name;
874 EXCEPTION
875 WHEN NO_DATA_FOUND THEN NULL;
876 end;
877 end if;
878 bal_cnt := bal_cnt + 1;
879 END LOOP;
880 --
881 hr_utility.set_location('pynegnet01.delete_bal',2);
882 --
883 END delete_bal;
884 --
885 -- ====================== create_balances ===========================
886 --
887 PROCEDURE create_balances
888 IS
889 CURSOR bal_dim (p_proto_bal_name VARCHAR2)
890 IS
891 SELECT bd.dimension_name
892 FROM pay_balance_types bt,
893 pay_balance_dimensions bd,
894 pay_defined_balances db
895 WHERE bt.balance_name = p_proto_bal_name AND
896 bt.balance_type_id = db.balance_type_id AND
897 db.balance_dimension_id = bd.balance_dimension_id;
898 /* balances counter */
899 total_bal BINARY_INTEGER := v_total_balances;
900 bal_cnt BINARY_INTEGER := 1;
901 lc_name VARCHAR2(80);
902 lc_proto VARCHAR2(80);
903 ln_id NUMBER := 0;
904 lc_cur_dim VARCHAR2(80);
905 BEGIN
906 --
907 hr_utility.set_location('pynegnet01.create_balances',1);
908 --
909 total_bal := v_total_balances;
910 WHILE bal_cnt <= total_bal
911 LOOP
912 if bal_cnt in (3,9,10) then
913 lc_name := arrear_bal_name_list (bal_cnt);
914
915 -- call function pay_db_pay_setup.create_balance_type
916 -- to build new arrear balance
917 --
918 ln_id := pay_db_pay_setup.create_balance_type(
919 p_balance_name => lc_name,
920 p_uom => v_uom,
921 p_currency_code => v_currency_code,
922 p_reporting_name => lc_name,
923 p_legislation_code => v_legislation_code);
924 --
925 arrear_bal_id_list (bal_cnt) := ln_id;
926 --
927 --
928 -- Use the prototype balance to determine the dimensions
929 -- and jurisdiction level.
930 --
931 lc_proto := proto_bal_name_list (bal_cnt);
932 --
933 -- update jurisdiction level for the arrears balance.
934 --
935 --
936 update pay_balance_types
937 set jurisdiction_level =
938 ( select jurisdiction_level
939 from pay_balance_types
940 where balance_name = lc_proto
941 and business_group_id is null
942 and legislation_code = 'US')
943 where balance_type_id = ln_id;
944 --
945 --
946 hr_utility.set_location('pynegnet01.create_balances',2);
947 --
948 --
949 -- build all dimensions from prototype
950 --
951 FOR dim_rec IN bal_dim(lc_proto)
952 LOOP
953 lc_cur_dim :=dim_rec.dimension_name;
954 --
955 --call procedure pay_db_pay_setup.create_defined_balance
956 pay_db_pay_setup.create_defined_balance(
957 p_balance_name => lc_name,
958 p_balance_dimension => lc_cur_dim,
959 p_legislation_code => v_legislation_code);
960 END LOOP;
961 --
962 hr_utility.set_location('pynegnet01.create_balances',3);
963 --
964 end if;
965 bal_cnt := bal_cnt + 1;
966
967 END LOOP;
968 END create_balances;
969 --
970 -- ====================== delete_ff_el ===========================
971 --
972 PROCEDURE delete_ff_el
973 IS
974 -- elements counter
975 total_el BINARY_INTEGER := v_total_elements;
976 el_cnt BINARY_INTEGER := 1;
977 ln_id NUMBER := 0;
978 lc_ff_name VARCHAR2(80);
979 ln_formula_id NUMBER :=1;
980 BEGIN
981 --
982 hr_utility.set_location('pynegnet01.delete_ff_el',1);
983 --
984 total_el := v_total_elements;
985 WHILE el_cnt <= total_el
986 LOOP
987 if el_cnt in (3,9,10) then
988 lc_ff_name := element_name_list (el_cnt) || 'POST_VERTEX';
989 begin
990 DELETE FROM ff_formulas_f
991 WHERE formula_name = lc_ff_name;
992 EXCEPTION
993 WHEN NO_DATA_FOUND THEN NULL;
994 end;
995 end if;
996 el_cnt := el_cnt + 1;
997 END LOOP;
998 --
999 hr_utility.set_location('pynegnet01.delete_ff_el',2);
1000 --
1001 END delete_ff_el;
1002 --
1003 -- ====================== create_ff_el ===========================
1004 --
1005 PROCEDURE create_ff_el
1006 IS
1007 -- elements counter
1008 total_el BINARY_INTEGER := v_total_elements;
1009 el_cnt BINARY_INTEGER := 1;
1010 ln_id NUMBER := 0;
1011 lc_text VARCHAR2(2000);
1012 lc_repl1 VARCHAR2(50);
1013 lc_repl2 VARCHAR2(50);
1014 lc_repl3 VARCHAR2(50);
1015 lc_ff_name VARCHAR2(80);
1016 lc_ff_desc VARCHAR2(80);
1017 lc_sticky_flag VARCHAR2(1) := ' ';
1018 ln_formula_id NUMBER :=1;
1019 BEGIN
1020 --
1021 hr_utility.set_location('pynegnet01.create_ff_el',1);
1022 --
1023 total_el := v_total_elements;
1024 WHILE el_cnt <= total_el
1025 LOOP
1026 if el_cnt in (3,9,10) then
1027 lc_repl1 := ff_repl1 (el_cnt);
1028 lc_repl2 := ff_repl2 (el_cnt);
1029 lc_repl3 := ff_repl3 (el_cnt);
1030 lc_text := v_ff_post_text;
1031 lc_text := REPLACE(lc_text,'{REPL1}',lc_repl1);
1032 lc_text := REPLACE(lc_text,'{REPL2}',lc_repl2);
1033 lc_text := REPLACE(lc_text,'{REPL3}',lc_repl3);
1034 lc_ff_name := element_name_list (el_cnt) || 'POST_VERTEX';
1035 lc_ff_desc := lc_ff_name || ' arrear calculation';
1036 --
1037 --
1038 SELECT ff_formulas_s.NEXTVAL
1039 INTO ln_formula_id
1040 FROM sys.dual;
1041 --
1042 INSERT INTO ff_formulas_f
1043 (FORMULA_ID,
1044 EFFECTIVE_START_DATE,
1045 EFFECTIVE_END_DATE,
1046 BUSINESS_GROUP_ID,
1047 LEGISLATION_CODE,
1048 FORMULA_TYPE_ID,
1049 FORMULA_NAME,
1050 DESCRIPTION,
1051 FORMULA_TEXT,
1052 STICKY_FLAG,
1053 LAST_UPDATE_DATE,
1054 LAST_UPDATED_BY,
1055 LAST_UPDATE_LOGIN,
1056 CREATED_BY,
1057 CREATION_DATE)
1058 VALUES
1059 (ln_formula_id,
1060 v_effective_start_date,
1061 v_effective_end_date,
1062 v_business_group_id,
1063 v_legislation_code,
1064 v_formula_type_id,
1065 lc_ff_name,
1066 lc_ff_desc,
1067 lc_text,
1068 lc_sticky_flag,
1069 v_sysdate,
1070 v_uid,
1071 v_uid,
1072 v_uid,
1073 v_sysdate);
1074 --
1075 --
1076 ff_id_list (el_cnt) := ln_formula_id;
1077 end if;
1078 el_cnt := el_cnt + 1;
1079 --
1080 END LOOP;
1081 --
1082 hr_utility.set_location('pynegnet01.create_ff_el',2);
1083 --
1084 END create_ff_el;
1085 --
1086 -- ====================== delete_arr_inp ===========================
1087 --
1088 PROCEDURE delete_arr_inp
1089 IS
1090 CURSOR arr_inp (p_arr_name VARCHAR2)
1091 IS
1092 SELECT iv.input_value_id
1093 FROM pay_input_values_f iv,
1094 pay_element_types_f et
1095 WHERE et.element_name = p_arr_name AND
1096 et.element_type_id = iv.element_type_id;
1097 /* balances counter */
1098 total_el BINARY_INTEGER := v_total_elements;
1099 el_cnt BINARY_INTEGER := 1;
1100 lc_name VARCHAR2(80);
1101 ln_id NUMBER := 0;
1102 spec_cnt BINARY_INTEGER := 1;
1103 total_spec BINARY_INTEGER := 3;
1104 ln_spec_id NUMBER := 0;
1105 BEGIN
1106 --
1107 hr_utility.set_location('pynegnet01.delete_arr_inp',1);
1108 --
1109 total_el := v_total_elements;
1110 WHILE el_cnt <= total_el
1111 LOOP
1112 if el_cnt in (3,9,10) then
1113 lc_name := element_name_list (el_cnt) || 'ARR';
1114 FOR arr_rec IN arr_inp(lc_name)
1115 LOOP
1116 ln_id := arr_rec.input_value_id;
1117 spec_cnt := 1;
1118 total_spec := 3;
1119 WHILE spec_cnt <= total_spec
1120 LOOP
1121 ln_spec_id := arr_bal_feed_id_list (spec_cnt);
1122 begin
1123 DELETE FROM pay_balance_feeds_f
1124 WHERE input_value_id = ln_id AND
1125 balance_type_id = ln_spec_id;
1126 EXCEPTION
1127 WHEN NO_DATA_FOUND THEN NULL;
1128 end;
1129 spec_cnt := spec_cnt + 1;
1130 END LOOP;
1131 begin
1132 DELETE FROM pay_input_values_f
1133 WHERE input_value_id = ln_id and
1134 business_group_id is null;
1135 EXCEPTION
1136 WHEN NO_DATA_FOUND THEN NULL;
1137 end;
1138 END LOOP;
1139 end if;
1140 el_cnt := el_cnt + 1;
1141 END LOOP;
1142 --
1143 hr_utility.set_location('pynegnet01.delete_arr_inp',2);
1144 --
1145 END delete_arr_inp;
1146 --
1147 -- ====================== delete_arr_el ===========================
1148 --
1149 PROCEDURE delete_arr_el
1150 IS
1151 /* elements counter */
1152 total_el BINARY_INTEGER := 0;
1153 el_cnt BINARY_INTEGER := 1;
1154 lc_name VARCHAR2(80);
1155 BEGIN
1156 --
1157 hr_utility.set_location('pynegnet01.delete_arr_el',1);
1158 --
1159 total_el := v_total_elements;
1160 WHILE el_cnt <= total_el
1161 LOOP
1162 if el_cnt in (3,9,10) then
1163 lc_name := element_name_list (el_cnt) || 'ARR';
1164 begin
1165 DELETE FROM pay_element_types_f
1166 WHERE element_name = lc_name and
1167 business_group_id is null;
1168 EXCEPTION
1169 WHEN NO_DATA_FOUND THEN NULL;
1170 end;
1171 end if;
1172 el_cnt := el_cnt + 1;
1173 END LOOP;
1174 el_cnt := 1;
1175 total_el := v_total_elements;
1176 WHILE el_cnt <= total_el
1177 LOOP
1178 if el_cnt in (3,9,10) then
1179
1180 lc_name := element_name_list (el_cnt) || 'ARR_%';
1181 begin
1182 DELETE FROM ff_user_entities
1183 WHERE user_entity_name LIKE lc_name;
1184 EXCEPTION
1185 WHEN NO_DATA_FOUND THEN NULL;
1186 end;
1187 end if;
1188 el_cnt := el_cnt + 1;
1189 END LOOP;
1190 --
1191 hr_utility.set_location('pynegnet01.delete_arr_el',2);
1192 --
1193 END delete_arr_el;
1194 --
1195 -- ====================== delete_arr_spec ===========================
1196 --
1197 PROCEDURE delete_arr_spec
1198 IS
1199 CURSOR arr_inp (p_arr_name VARCHAR2)
1200 IS
1201 SELECT iv.input_value_id
1202 FROM pay_input_values_f iv,
1203 pay_element_types_f et
1204 WHERE et.element_name = p_arr_name AND
1205 et.element_type_id = iv.element_type_id;
1206 /* balances counter */
1207 total_el BINARY_INTEGER := v_total_elements;
1208 el_cnt BINARY_INTEGER := 1;
1209 lc_name VARCHAR2(80);
1210 ln_id NUMBER := 0;
1211 spec_cnt BINARY_INTEGER := 1;
1212 total_spec BINARY_INTEGER := 3;
1213 ln_spec_id NUMBER := 0;
1214 ln_whld_id NUMBER := 0;
1215 BEGIN
1216 --
1217 hr_utility.set_location('pynegnet01.delete_arr_spec',1);
1218 --
1219 total_el := v_total_elements;
1220 WHILE el_cnt <= total_el
1221 LOOP
1222 if el_cnt in (3,9,10) then
1223
1224 lc_name := element_name_list (el_cnt) || 'ARR';
1225 ln_whld_id := whld_bal_feed_id_list (el_cnt);
1226 FOR arr_rec IN arr_inp(lc_name)
1227 LOOP
1228 ln_id := arr_rec.input_value_id;
1229 spec_cnt := 1;
1230 total_spec := 3;
1231 WHILE spec_cnt <= total_spec
1232 LOOP
1233 ln_spec_id := arr_bal_feed_id_list (spec_cnt);
1234 begin
1235 DELETE FROM pay_balance_feeds_f
1236 WHERE input_value_id = ln_id AND
1237 ( balance_type_id = ln_spec_id
1238 OR balance_type_id = ln_whld_id);
1239 EXCEPTION
1240 WHEN NO_DATA_FOUND THEN NULL;
1241 end;
1242 spec_cnt := spec_cnt + 1;
1243 END LOOP;
1244 END LOOP;
1245 end if;
1246 el_cnt := el_cnt + 1;
1247 END LOOP;
1248 --
1249 hr_utility.set_location('pynegnet01.delete_arr_spec',2);
1250 --
1251 END delete_arr_spec;
1252 --
1253 -- ====================== create_arr_el ===========================
1254 --
1255 PROCEDURE create_arr_el
1256 IS
1257 /* elements counter */
1258 total_el BINARY_INTEGER := 0;
1259 el_cnt BINARY_INTEGER := 1;
1260 lc_name VARCHAR2(80);
1261 lc_class VARCHAR2(20) := 'Information';
1262 ln_priority NUMBER ; -- := 4250;
1263 lc_rule VARCHAR2(20) := 'Final Close';
1264 lc_type VARCHAR2(1) := 'N';
1265 ln_id NUMBER := 0;
1266 ln_inp_id NUMBER := 0;
1267 lc_inp_name VARCHAR2(20);
1268 lc_uom VARCHAR2(20);
1269 lc_ind_flag VARCHAR2(1) := 'Y';
1270 lc_mult_entries_allowed VARCHAR2(1) := 'Y';
1271 inp_cnt BINARY_INTEGER := 1;
1272 total_inp BINARY_INTEGER := 2;
1273 --
1274 /* balance feed variables */
1275 lc_option VARCHAR2(80);
1276 ln_input_value_id NUMBER;
1277 ln_element_type_id NUMBER;
1278 ln_prim_class_id NUMBER;
1279 ln_sub_class_id NUMBER;
1280 ln_sub_class_rule_id NUMBER;
1281 ln_balance_type_id NUMBER;
1282 lc_scale VARCHAR2(80);
1283 ld_session_date DATE;
1284 lc_business_group VARCHAR2(80);
1285 lc_legislation_code VARCHAR2(80);
1286 lc_mode VARCHAR2(80);
1287 ln_find BINARY_INTEGER := 0;
1288 spec_cnt BINARY_INTEGER := 1;
1289 total_spec BINARY_INTEGER := 3;
1290 BEGIN
1291 --
1292 hr_utility.set_location('pynegnet01.create_arr_el',1);
1293 --
1294 SELECT classification_id
1295 INTO ln_prim_class_id
1296 FROM pay_element_classifications
1297 WHERE classification_name = 'Information'
1298 AND legislation_code = 'US'
1299 AND business_group_id is null
1300 ;
1301 total_el := v_total_elements;
1302 WHILE el_cnt <= total_el
1303 LOOP
1304 if el_cnt in (3,9,10) then
1305
1306 lc_name := element_name_list (el_cnt) || 'ARR';
1307 ln_priority := element_priority_list (el_cnt);
1308 /* call function pay_db_pay_setup.create_element
1309 to build new arr element */
1310 ln_id :=
1311 pay_db_pay_setup.create_element(
1312 p_element_name => lc_name,
1313 p_description => lc_name,
1314 p_reporting_name => lc_name,
1315 p_classification_name => lc_class,
1316 p_input_currency_code => v_currency_code,
1317 p_output_currency_code => v_currency_code,
1318 p_processing_type => lc_type,
1319 p_mult_entries_allowed => lc_mult_entries_allowed,
1320 p_processing_priority => ln_priority,
1321 p_post_termination_rule => lc_rule,
1322 p_indirect_only_flag => lc_ind_flag,
1323 p_effective_start_date => v_effective_start_date,
1324 p_effective_end_date => v_effective_end_date,
1325 p_legislation_code => v_legislation_code);
1326 --
1327 hr_utility.set_location('pynegnet01.create_arr_el',2);
1328 --
1329 inp_cnt := 1;
1330
1331 /* input values loop */
1332 WHILE inp_cnt <= total_inp
1333 LOOP
1334 lc_inp_name := arr_inp_value_list (inp_cnt);
1335 lc_uom := arr_unit_list (inp_cnt);
1336 ln_inp_id :=
1337 pay_db_pay_setup.create_input_value(
1338 p_element_name => lc_name,
1339 p_name => lc_inp_name,
1340 p_uom => lc_uom,
1341 p_display_sequence => inp_cnt,
1342 p_business_group_name => v_business_group_name,
1343 p_effective_start_date => v_effective_start_date,
1344 p_effective_end_date => v_effective_end_date,
1345 -- Bug 563295. mlisieck, added p_legislation_code.
1346 p_legislation_code => v_legislation_code);
1347 arr_inp_id_list ((el_cnt-1)*2+inp_cnt) := ln_inp_id; -- v_arr_inp_cnt)
1348 -- v_arr_inp_cnt := v_arr_inp_cnt + 1;
1349 inp_cnt := inp_cnt + 1;
1350 END LOOP;
1351 arr_element_id_list (el_cnt) := ln_id;
1352 end if;
1353 el_cnt := el_cnt + 1;
1354 --
1355 hr_utility.set_location('pynegnet01.create_arr_el',3);
1356 --
1357 END LOOP;
1358 --
1359 hr_utility.set_location('pynegnet01.create_arr_el',4);
1360 --
1361 END create_arr_el;
1362 --
1363 -- ====================== create_arr_feed ===========================
1364 --
1365 PROCEDURE create_arr_feed
1366 IS
1367 /* elements counter */
1368 total_el BINARY_INTEGER := 0;
1369 el_cnt BINARY_INTEGER := 1;
1370 lc_name VARCHAR2(80);
1371 lc_class VARCHAR2(20) := 'Information';
1372 ln_priority NUMBER := 4250;
1373 lc_rule VARCHAR2(20) := 'Final Close';
1374 lc_type VARCHAR2(1) := 'N';
1375 ln_id NUMBER := 0;
1376 ln_inp_id NUMBER := 0;
1377 lc_inp_name VARCHAR2(20);
1378 lc_uom VARCHAR2(20);
1379 lc_ind_flag VARCHAR2(1) := 'Y';
1380 lc_mult_entries_allowed VARCHAR2(1) := 'Y';
1381 inp_cnt BINARY_INTEGER := 1;
1382 total_inp BINARY_INTEGER := 2;
1383 --
1384 /* balance feed variables */
1385 lc_option VARCHAR2(80);
1386 ln_input_value_id NUMBER;
1387 ln_element_type_id NUMBER;
1388 ln_prim_class_id NUMBER;
1389 ln_sub_class_id NUMBER;
1390 ln_sub_class_rule_id NUMBER;
1391 ln_balance_type_id NUMBER;
1392 lc_scale VARCHAR2(80);
1393 ld_session_date DATE;
1394 lc_business_group VARCHAR2(80);
1395 lc_legislation_code VARCHAR2(80);
1396 lc_mode VARCHAR2(80);
1397 ln_find BINARY_INTEGER := 0;
1398 spec_cnt BINARY_INTEGER :=1;
1399 total_spec BINARY_INTEGER :=3;
1400 ln_inp NUMBER :=0;
1401 --
1402 --
1403 BEGIN
1404 --
1405 hr_utility.set_location('pynegnet01.create_arr_feed',1);
1406 --
1407 SELECT classification_id
1408 INTO ln_prim_class_id
1409 FROM pay_element_classifications
1410 WHERE classification_name = 'Information'
1411 and legislation_code = 'US'
1412 and business_group_id is null ;
1413
1414 total_el := v_total_elements;
1415 WHILE el_cnt <= total_el
1416 LOOP
1417 if el_cnt in (3,9,10) then
1418
1419 /* balance feeds loop */
1420 /* arrear balance feed */
1421 ln_find := arr_assign_list (el_cnt);
1422 ln_inp := (el_cnt-1)*2+1;
1423 lc_option := 'INS_MANUAL_FEED';
1424 ln_input_value_id := arr_inp_id_list (ln_inp);
1425 ln_element_type_id := arr_element_id_list (el_cnt);
1426 ln_sub_class_id := NULL;
1427 ln_sub_class_rule_id := NULL;
1428 ln_balance_type_id := arrear_bal_id_list (ln_find);
1429 lc_scale := '-1';
1430 ld_session_date := TRUNC(SYSDATE);
1431 lc_business_group := v_business_group_name;
1432 lc_legislation_code := v_legislation_code;
1433 lc_mode := 'USER';
1434
1435 hr_balances.ins_balance_feed(
1436 p_option => lc_option,
1437 p_input_value_id => ln_input_value_id,
1438 p_element_type_id => ln_element_type_id,
1439 p_primary_classification_id => ln_prim_class_id,
1440 p_sub_classification_id => ln_sub_class_id,
1441 p_sub_classification_rule_id => ln_sub_class_rule_id,
1442 p_balance_type_id => ln_balance_type_id,
1443 p_scale => lc_scale,
1444 p_session_date => ld_session_date,
1445 p_business_group => lc_business_group,
1446 p_legislation_code => lc_legislation_code,
1447 p_mode => lc_mode);
1448 --
1449 hr_utility.set_location('pynegnet01.create_arr_feed',2);
1450 --
1451 /* withheld balance feed */
1452 ln_inp := (el_cnt-1)*2+1;
1453 lc_option := 'INS_MANUAL_FEED';
1454 ln_input_value_id := arr_inp_id_list (ln_inp);
1455 ln_element_type_id := arr_element_id_list (el_cnt);
1456 ln_sub_class_id := NULL;
1457 ln_sub_class_rule_id := NULL;
1458 ln_balance_type_id := whld_bal_feed_id_list (el_cnt);
1459 lc_scale := '+1';
1460 ld_session_date := TRUNC(SYSDATE);
1461 lc_business_group := v_business_group_name;
1462 lc_legislation_code := v_legislation_code;
1463 lc_mode := 'USER';
1464
1465 hr_balances.ins_balance_feed(
1466 p_option => lc_option,
1467 p_input_value_id => ln_input_value_id,
1468 p_element_type_id => ln_element_type_id,
1469 p_primary_classification_id => ln_prim_class_id,
1470 p_sub_classification_id => ln_sub_class_id,
1471 p_sub_classification_rule_id => ln_sub_class_rule_id,
1472 p_balance_type_id => ln_balance_type_id,
1473 p_scale => lc_scale,
1474 p_session_date => ld_session_date,
1475 p_business_group => lc_business_group,
1476 p_legislation_code => lc_legislation_code,
1477 p_mode => lc_mode);
1478 --
1479 hr_utility.set_location('pynegnet01.create_arr_feed',3);
1480 --
1481 /* special balances feed Tax Deductions, Net, Payments */
1482 spec_cnt := 1;
1483 total_spec := 3;
1484 WHILE spec_cnt <= total_spec
1485 LOOP
1486 ln_inp := (el_cnt-1)*2+1;
1487 lc_option := 'INS_MANUAL_FEED';
1488 ln_input_value_id := arr_inp_id_list (ln_inp);
1489 ln_element_type_id := arr_element_id_list (el_cnt);
1490 ln_sub_class_id := NULL;
1491 ln_sub_class_rule_id := NULL;
1492 ln_balance_type_id := arr_bal_feed_id_list (spec_cnt);
1493 lc_scale := arr_bal_feed_rule_list (spec_cnt);
1494 ld_session_date := TRUNC(SYSDATE);
1495 lc_business_group := v_business_group_name;
1496 lc_legislation_code := v_legislation_code;
1497 lc_mode := 'USER';
1498 --
1499
1500
1501 hr_balances.ins_balance_feed(
1502 p_option => lc_option,
1503 p_input_value_id => ln_input_value_id,
1504 p_element_type_id => ln_element_type_id,
1505 p_primary_classification_id => ln_prim_class_id,
1506 p_sub_classification_id => ln_sub_class_id,
1507 p_sub_classification_rule_id => ln_sub_class_rule_id,
1508 p_balance_type_id => ln_balance_type_id,
1509 p_scale => lc_scale,
1510 p_session_date => ld_session_date,
1511 p_business_group => lc_business_group,
1512 p_legislation_code => lc_legislation_code,
1513 p_mode => lc_mode)
1514 ;
1515 spec_cnt := spec_cnt + 1;
1516 END LOOP;
1517
1518 /* Total Tax Arrears balance feed */
1519
1520 ln_inp := (el_cnt-1)*2+1;
1521 lc_option := 'INS_MANUAL_FEED';
1522 ln_input_value_id := arr_inp_id_list (ln_inp);
1523 ln_element_type_id := arr_element_id_list (el_cnt);
1524 ln_sub_class_id := NULL;
1525 ln_sub_class_rule_id := NULL;
1526 ln_balance_type_id := arrear_bal_id_list (13); /* arrears_bal_id_list(v_total_balances) = 'Tax Arrears' */
1527 lc_scale := '-1';
1528 ld_session_date := TRUNC(SYSDATE);
1529 lc_business_group := v_business_group_name;
1530 lc_legislation_code := v_legislation_code;
1531 lc_mode := 'USER';
1532
1533
1534 hr_balances.ins_balance_feed(
1535 p_option => lc_option,
1536 p_input_value_id => ln_input_value_id,
1537 p_element_type_id => ln_element_type_id,
1538 p_primary_classification_id => ln_prim_class_id,
1539 p_sub_classification_id => ln_sub_class_id,
1540 p_sub_classification_rule_id => ln_sub_class_rule_id,
1541 p_balance_type_id => ln_balance_type_id,
1542 p_scale => lc_scale,
1543 p_session_date => ld_session_date,
1544 p_business_group => lc_business_group,
1545 p_legislation_code => lc_legislation_code,
1546 p_mode => lc_mode);
1547
1548 end if;
1549 el_cnt := el_cnt + 1;
1550 --
1551 hr_utility.set_location('pynegnet01.create_arr_feed',4);
1552 --
1553 -- package above does not support effective_start_date
1554 --
1555 update pay_balance_feeds_f
1556 set business_group_id = null,
1557 effective_start_date = v_effective_start_date
1558 where balance_type_id = ln_balance_type_id
1559 ;
1560 --
1561 END LOOP;
1562 --
1563 hr_utility.set_location('pynegnet01.create_arr_feed',5);
1564 --
1565 --
1566
1567
1568 END create_arr_feed;
1569 --
1570 -- ====================== delete_post_inp ===========================
1571 --
1572 PROCEDURE delete_post_inp
1573 IS
1574 CURSOR post_inp (p_post_name VARCHAR2)
1575 IS
1576 SELECT iv.input_value_id
1577 FROM pay_input_values_f iv,
1578 pay_element_types_f et
1579 WHERE et.element_name = p_post_name AND
1580 et.element_type_id = iv.element_type_id;
1581 /* balances counter */
1582 total_el BINARY_INTEGER := v_total_elements;
1583 el_cnt BINARY_INTEGER := 1;
1584 lc_name VARCHAR2(80);
1585 ln_id NUMBER := 0;
1586 BEGIN
1587 --
1588 hr_utility.set_location('pynegnet01.delete_post_input',1);
1589 --
1590 total_el := v_total_elements;
1591 WHILE el_cnt <= total_el
1592 LOOP
1593 if el_cnt in (3,9,10) then
1594
1595 lc_name := element_name_list (el_cnt) || 'POST_VERTEX';
1596 FOR post_rec IN post_inp(lc_name)
1597 LOOP
1598 ln_id := post_rec.input_value_id;
1599 begin
1600 DELETE FROM pay_input_values_f
1601 WHERE input_value_id = ln_id and
1602 business_group_id is null;
1603 EXCEPTION
1604 WHEN NO_DATA_FOUND THEN NULL;
1605 end;
1606 END LOOP;
1607 end if;
1608 el_cnt := el_cnt + 1;
1609 END LOOP;
1610 --
1611 hr_utility.set_location('pynegnet01.delete_post_input',2);
1612 --
1613 END delete_post_inp;
1614 --
1615 -- ====================== delete_post_el ===========================
1616 --
1617 PROCEDURE delete_post_el
1618 IS
1619 /* elements counter */
1620 total_el BINARY_INTEGER := 0;
1621 el_cnt BINARY_INTEGER := 1;
1622 lc_name VARCHAR2(80);
1623 ln_id NUMBER := 0;
1624 l_rule_id NUMBER := 0;
1625
1626 cursor csr_all_processing_rules (p_lc_name varchar2) is
1627 SELECT psprf.status_processing_rule_id
1628 FROM pay_status_processing_rules_f psprf,
1629 pay_element_types_f petf
1630 WHERE petf.element_type_id = psprf.element_type_id and
1631 petf.element_name = p_lc_name and
1632 petf.business_group_id is null;
1633 BEGIN
1634 --
1635 hr_utility.set_location('pynegnet01.delete_post_el',1);
1636 --
1637 total_el := v_total_elements;
1638 WHILE el_cnt <= total_el
1639 LOOP
1640 if el_cnt in (3,9,10) then
1641
1642 lc_name := element_name_list (el_cnt) || 'POST_VERTEX';
1643 begin
1644 DELETE FROM pay_element_types_f
1645 WHERE element_name = lc_name and
1646 business_group_id is null;
1647 EXCEPTION
1648 WHEN NO_DATA_FOUND THEN NULL;
1649 end;
1650 end if;
1651 el_cnt := el_cnt + 1;
1652 END LOOP;
1653 --
1654 hr_utility.set_location('pynegnet01.delete_post_el',2);
1655 --
1656 el_cnt := 1;
1657 total_el := v_total_elements;
1658 WHILE el_cnt <= total_el
1659 LOOP
1660 if el_cnt in (3,9,10) then
1661
1662 lc_name := element_name_list (el_cnt) || 'POST_VERTEX_%';
1663 DELETE FROM ff_user_entities
1664 WHERE user_entity_name LIKE lc_name and
1665 business_group_id is null;
1666 end if;
1667 el_cnt := el_cnt + 1;
1668 END LOOP;
1669 --
1670 hr_utility.set_location('pynegnet01.delete_post_el',3);
1671 --
1672 el_cnt := 1;
1673 total_el := v_total_elements;
1674 WHILE el_cnt <= total_el
1675 LOOP
1676 ln_id := 0;
1677
1678 lc_name := element_name_list (el_cnt) || 'POST_VERTEX';
1679
1680 open csr_all_processing_rules (lc_name);
1681 fetch csr_all_processing_rules into l_rule_id;
1682 close csr_all_processing_rules;
1683
1684 BEGIN
1685 SELECT element_type_id
1686 INTO ln_id
1687 FROM pay_element_types_f
1688 WHERE element_name = lc_name;
1689 EXCEPTION
1690 WHEN NO_DATA_FOUND THEN NULL;
1691 END;
1692
1693 if el_cnt in (3,9,10) then
1694
1695 IF ln_id > 0 THEN
1696
1697 DELETE FROM pay_status_processing_rules_f
1698 WHERE element_type_id = ln_id;
1699 DELETE FROM pay_formula_result_rules_f
1700 WHERE status_processing_rule_id = l_rule_id;
1701 END IF;
1702 else
1703 if ln_id > 0 THEN
1704 DELETE FROM pay_formula_result_rules_f
1705 WHERE status_processing_rule_id = l_rule_id;
1706 end if;
1707 end if;
1708 el_cnt := el_cnt + 1;
1709 END LOOP;
1710 --
1711 hr_utility.set_location('pynegnet01.delete_post_el',4);
1712 --
1713 END delete_post_el;
1714 --
1715 -- ====================== create_post_el ===========================
1716 --
1717 PROCEDURE create_post_el
1718 IS
1719 /* elements counter */
1720 total_el BINARY_INTEGER := 0;
1721 el_cnt BINARY_INTEGER := 1;
1722 lc_name VARCHAR2(80);
1723 lc_class VARCHAR2(20) := 'Information';
1724 ln_priority NUMBER; -- := 4250;
1725 lc_rule VARCHAR2(20) := 'Final Close';
1726 lc_type VARCHAR2(1) := 'N';
1727 ln_id NUMBER := 0;
1728 ln_inp_id NUMBER := 0;
1729 lc_inp_name VARCHAR2(20);
1730 lc_uom VARCHAR2(20);
1731 lc_ind_flag VARCHAR2(1) := 'Y';
1732 lc_mult_entries_allowed VARCHAR2(1) := 'Y';
1733 inp_cnt BINARY_INTEGER := 1;
1734 total_inp BINARY_INTEGER := 5;
1735 /* status variables */
1736 ln_stat_id NUMBER := 0;
1737 lc_processing_rule VARCHAR2(1) := 'P';
1738 ln_formula_id NUMBER :=0;
1739 /* rules variables */
1740 ln_rule_id NUMBER := 0;
1741 ln_input_value_id NUMBER := 0;
1742 ln_element_type_id NUMBER := 0;
1743 lc_result_name VARCHAR2(30);
1744 lc_result_rule_type VARCHAR2(20);
1745 ln_find BINARY_INTEGER := 0;
1746 rule_cnt BINARY_INTEGER :=0;
1747 total_rule BINARY_INTEGER :=2;
1748 lc_severity_level VARCHAR2(1) := 'W';
1749 BEGIN
1750 --
1751 hr_utility.set_location('pynegnet01.create_post_el',1);
1752 --
1753 total_el := v_total_elements; /* element_name_list.COUNT; */
1754 el_cnt := v_total_elements;
1755 WHILE el_cnt > 0 LOOP
1756
1757 if el_cnt in (3,9,10) then
1758
1759 lc_name := element_name_list (el_cnt) || 'POST_VERTEX';
1760 ln_priority := element_priority_list (el_cnt);
1761 /* call function pay_db_pay_setup.create_element
1762 to build new post element */
1763
1764 ln_id := pay_db_pay_setup.create_element(
1765 p_element_name => lc_name,
1766 p_description => lc_name,
1767 p_reporting_name => lc_name,
1768 p_classification_name => lc_class,
1769 p_input_currency_code => v_currency_code,
1770 p_output_currency_code => v_currency_code,
1771 p_processing_type => lc_type,
1772 p_mult_entries_allowed => lc_mult_entries_allowed,
1773 p_processing_priority => ln_priority,
1774 p_post_termination_rule => lc_rule,
1775 p_indirect_only_flag => lc_ind_flag,
1776 p_effective_start_date => v_effective_start_date,
1777 p_effective_end_date => v_effective_end_date,
1778 p_legislation_code => v_legislation_code);
1779 inp_cnt := 1;
1780 --
1781 hr_utility.set_location('pynegnet01.create_post_el',2);
1782 --
1783 WHILE inp_cnt <= total_inp
1784 LOOP
1785 lc_inp_name := post_inp_value_list (inp_cnt);
1786 lc_uom := 'Character';
1787 ln_inp_id :=
1788 pay_db_pay_setup.create_input_value(
1789 p_element_name => lc_name,
1790 p_name => lc_inp_name,
1791 p_uom => lc_uom,
1792 p_display_sequence => inp_cnt,
1793 p_business_group_name => v_business_group_name,
1794 p_effective_start_date => v_effective_start_date,
1795 p_effective_end_date => v_effective_end_date,
1796 -- Bug 563295. mlisieck, added p_legislation_code.
1797 p_legislation_code => v_legislation_code);
1798 v_post_inp_cnt := (el_cnt-1)*5+inp_cnt;
1799 post_inp_id_list (v_post_inp_cnt) := ln_inp_id;
1800 inp_cnt := inp_cnt + 1;
1801 END LOOP;
1802 --
1803 hr_utility.set_location('pynegnet01.create_post_el',3);
1804 --
1805 /* insert status processing rule */
1806 ln_formula_id := ff_id_list (el_cnt);
1807 ln_stat_id := pay_formula_results.ins_stat_proc_rule(
1808 p_business_group_id => v_business_group_id,
1809 p_legislation_code => v_legislation_code,
1810 p_effective_start_date => v_effective_start_date,
1811 p_effective_end_date => v_effective_end_date,
1812 p_element_type_id => ln_id,
1813 p_formula_id => ln_formula_id,
1814 p_processing_rule => lc_processing_rule);
1815 --
1816 hr_utility.set_location('pynegnet01.create_post_el',4);
1817 --
1818 post_element_id_list (el_cnt) := ln_id;
1819 --
1820 else
1821 select status_processing_rule_id into ln_stat_id
1822 from pay_status_processing_rules_f
1823 where element_type_id = post_element_id_list (el_cnt);
1824 end if;
1825
1826 /* insert_formula result rules */
1827 /* part 1 */
1828 rule_cnt := 1;
1829 total_rule := 2;
1830 WHILE rule_cnt <= total_rule
1831 LOOP
1832 lc_result_name := post_res1_out_name_list (rule_cnt);
1833 lc_result_rule_type := 'I';
1834 ln_element_type_id := arr_element_id_list (el_cnt);
1835 ln_find := (el_cnt-1)*2+rule_cnt;
1836 ln_input_value_id := arr_inp_id_list (ln_find);
1837 ln_rule_id := pay_formula_results.ins_form_res_rule(
1838 p_business_group_id => v_business_group_id,
1839 p_legislation_code => v_legislation_code,
1840 p_effective_start_date => v_effective_start_date,
1841 p_effective_end_date => v_effective_end_date,
1842 p_status_processing_rule_id => ln_stat_id,
1843 p_input_value_id => ln_input_value_id,
1844 p_result_name => lc_result_name,
1845 p_result_rule_type => lc_result_rule_type,
1846 p_element_type_id => ln_element_type_id);
1847 rule_cnt := rule_cnt + 1;
1848 --
1849 hr_utility.set_location('pynegnet01.create_post_el',5);
1850 --
1851 END LOOP;
1852 /* part 2 */
1853 lc_result_name := 'ARR_RET_MSG';
1854 lc_result_rule_type := 'M';
1855 -- ln_element_type_id := arr_element_id_list (el_cnt);
1856 ln_rule_id := pay_formula_results.ins_form_res_rule(
1857 p_business_group_id => v_business_group_id,
1858 p_legislation_code => v_legislation_code,
1859 p_effective_start_date => v_effective_start_date,
1860 p_effective_end_date => v_effective_end_date,
1861 p_status_processing_rule_id => ln_stat_id,
1862 p_result_name => lc_result_name,
1863 p_result_rule_type => lc_result_rule_type,
1864 p_severity_level => lc_severity_level);
1865 --
1866 hr_utility.set_location('pynegnet01.create_post_el',6);
1867 --
1868 /* part 3 */
1869 IF el_cnt < v_total_elements THEN
1870 rule_cnt := 1;
1871 total_rule := 5;
1872 post_res4_out_name_list (1) := jd_list (el_cnt);
1873 WHILE rule_cnt <= total_rule
1874 LOOP
1875 lc_result_name := post_res4_out_name_list (rule_cnt);
1876 lc_result_rule_type := 'I';
1877 ln_element_type_id := post_element_id_list (el_cnt+1);
1878 ln_find := (el_cnt*5)+rule_cnt;
1879 ln_input_value_id := post_inp_id_list (ln_find);
1880 ln_rule_id := pay_formula_results.ins_form_res_rule(
1881 p_business_group_id => v_business_group_id,
1882 p_legislation_code => v_legislation_code,
1883 p_effective_start_date => v_effective_start_date,
1884 p_effective_end_date => v_effective_end_date,
1885 p_status_processing_rule_id => ln_stat_id,
1886 p_input_value_id => ln_input_value_id,
1887 p_result_name => lc_result_name,
1888 p_result_rule_type => lc_result_rule_type,
1889 p_element_type_id => ln_element_type_id);
1890 rule_cnt := rule_cnt + 1;
1891 --
1892 hr_utility.set_location('pynegnet01.create_post_el',7);
1893 --
1894 END LOOP;
1895 END IF;
1896 --
1897 el_cnt := el_cnt - 1;
1898 --
1899 END LOOP;
1900 --
1901 hr_utility.set_location('pynegnet01.create_post_el',8);
1902 --
1903 END create_post_el;
1904 --
1905 -- ====================== delete_vertex_results ===========================
1906 --
1907 PROCEDURE delete_vertex_results
1908 IS
1909 CURSOR first_inp (p_post_name VARCHAR2)
1910 IS
1911 SELECT iv.input_value_id
1912 FROM pay_input_values_f iv,
1913 pay_element_types_f et
1914 WHERE et.element_name = p_post_name AND
1915 et.element_type_id = iv.element_type_id
1916 ;
1917
1918 lc_name VARCHAR2(80) := 'VERTEX_RESULTS';
1919 ln_vertex_id NUMBER := 0;
1920 ln_id NUMBER := 0;
1921
1922
1923 BEGIN
1924 --
1925 hr_utility.set_location('pynegnet.delete_vertex_results',1);
1926 --
1927 BEGIN
1928 SELECT element_type_id
1929 INTO ln_vertex_id
1930 FROM pay_element_types_f
1931 WHERE element_name = lc_name;
1932 EXCEPTION
1933 WHEN NO_DATA_FOUND THEN NULL;
1934 END;
1935 IF ln_vertex_id > 0 THEN
1936 FOR first_rec IN first_inp('SUI_EE_POST_VERTEX')
1937 LOOP
1938 ln_id := first_rec.input_value_id;
1939 DELETE FROM pay_formula_result_rules_f
1940 WHERE
1941 input_value_id = ln_id
1942 ;
1943 -- sd1 element_type_id = ln_vertex_id
1944 -- sd1 AND input_value_id = ln_id;
1945 END LOOP;
1946 END IF;
1947 --
1948 hr_utility.set_location('pynegnet.delete_vertex_results',2);
1949 --
1950 END delete_vertex_results;
1951 --
1952 -- ====================== create_vertex_results ===========================
1953 --
1954 PROCEDURE create_vertex_results
1955 IS
1956 /* insert_formula result rules */
1957 rule_cnt BINARY_INTEGER := 1;
1958 total_rule BINARY_INTEGER := 5;
1959 ln_stat_id NUMBER := 0;
1960 ln_rule_id NUMBER := 0;
1961 ln_input_value_id NUMBER := 0;
1962 ln_element_type_id NUMBER := 0;
1963 lc_result_name VARCHAR2(30);
1964 lc_result_rule_type VARCHAR2(20);
1965 lc_severity_level VARCHAR2(1) := 'W';
1966 BEGIN
1967 --
1968 hr_utility.set_location('pynegnet.create_vertex_results',1);
1969 --
1970 BEGIN
1971 SELECT status_processing_rule_id
1972 INTO ln_stat_id
1973 FROM pay_status_processing_rules_f sp,
1974 pay_element_types_f et
1975 WHERE et.element_name = 'VERTEX_RESULTS'
1976 AND et.element_type_id = sp.element_type_id;
1977 EXCEPTION
1978 WHEN NO_DATA_FOUND THEN NULL;
1979 END;
1980 IF ln_stat_id > 0 THEN
1981 WHILE rule_cnt <= total_rule
1982 LOOP
1983 lc_result_name := vertex_res_out_name_list (rule_cnt);
1984 lc_result_rule_type := 'I';
1985 ln_element_type_id := post_element_id_list (1);
1986 ln_input_value_id := post_inp_id_list (rule_cnt);
1987 ln_rule_id := pay_formula_results.ins_form_res_rule(
1988 p_business_group_id => v_business_group_id,
1989 p_legislation_code => v_legislation_code,
1990 p_effective_start_date => v_effective_start_date,
1991 p_effective_end_date => v_effective_end_date,
1992 p_status_processing_rule_id => ln_stat_id,
1993 p_input_value_id => ln_input_value_id,
1994 p_result_name => lc_result_name,
1995 p_result_rule_type => lc_result_rule_type,
1996 p_element_type_id => ln_element_type_id);
1997 rule_cnt := rule_cnt + 1;
1998 --
1999 hr_utility.set_location('pynegnet.create_vertex_results',2);
2000 --
2001 END LOOP;
2002 END IF;
2003 --
2004 hr_utility.set_location('pynegnet.create_vertex_results',3);
2005 --
2006 END create_vertex_results;
2007 --
2008 -----------------------cleanup-------------------------------------------
2009 --
2010 procedure cleanup is
2011 cursor sel_input (p_element varchar2) is
2012 select
2013 element_type_id
2014 from pay_element_types_f
2015 where
2016 element_name = p_element
2017 and legislation_code = 'US'
2018 ;
2019
2020 total_el binary_integer := v_total_elements;
2021 el_count binary_integer := 1;
2022 lc_element_type_id number;
2023
2024 begin
2025 while (el_count <= total_el) loop
2026 --
2027 if el_count in (3,9,10) then
2028 open sel_input (element_name_list(el_count)||'ARR');
2029 fetch sel_input into lc_element_type_id;
2030 close sel_input;
2031 --
2032 update pay_input_values_f
2033 set business_group_id = null
2034 where element_type_id = lc_element_type_id
2035 and business_group_id is not null
2036 ;
2037 --
2038 update pay_balance_feeds_f
2039 set business_group_id = null,
2040 effective_start_date = v_effective_start_date
2041 where input_value_id in
2042 (select input_value_id
2043 from pay_input_values_f
2044 where element_type_id = lc_element_type_id)
2045 and (business_group_id is not null
2046 or effective_start_date <> v_effective_start_date)
2047 ;
2048 --
2049 end if;
2050 el_count := el_count + 1;
2051 end loop;
2052 --
2053 el_count := 1;
2054 --
2055 --
2056 while (el_count <= total_el) loop
2057 --
2058 if el_count in (3,9,10) then
2059 open sel_input (element_name_list(el_count)||'POST_VERTEX');
2060 fetch sel_input into lc_element_type_id;
2061 close sel_input;
2062 --
2063 --
2064 update pay_input_values_f
2065 set business_group_id = null
2066 where element_type_id = lc_element_type_id
2067 and business_group_id is not null
2068 ;
2069 --
2070 end if;
2071
2072 el_count := el_count + 1;
2073
2074 end loop;
2075 --
2076 end;
2077 --
2078 -- ====================== change_balance_feeds =========================
2079 --
2080 PROCEDURE change_balance_feeds
2081 IS
2082
2083 el_cnt BINARY_INTEGER := 1;
2084 feed_cnt BINARY_INTEGER := 1;
2085 l_scale BINARY_INTEGER;
2086 l_balance_feed_id BINARY_INTEGER;
2087 l_element_type_id BINARY_INTEGER;
2088 l_balance_name varchar2(30);
2089 l_assign_list BINARY_INTEGER;
2090
2091 cursor crs_arr_element_type_id (el_count number) is
2092 select element_type_id from pay_element_types_f
2093 where element_name = element_name_list (el_count) || 'ARR';
2094
2095 CURSOR csr_bal_feeds (p_arr_element_type_id number)
2096 IS
2097 SELECT pbf.balance_feed_id, pbt.balance_name
2098 FROM pay_balance_feeds_f pbf, pay_input_values_f pivf,
2099 pay_element_types_f petf, pay_balance_types pbt
2100 WHERE pbf.input_value_id = pivf.input_value_id and
2101 petf.element_type_id = pivf.element_type_id and
2102 petf.element_type_id = p_arr_element_type_id and
2103 pbf.balance_type_id = pbt.balance_type_id;
2104
2105 BEGIN
2106
2107 WHILE el_cnt <= v_total_elements loop
2108
2109 if el_cnt not in (3,9,10) then
2110
2111 open crs_arr_element_type_id (el_cnt);
2112 fetch crs_arr_element_type_id into l_element_type_id;
2113 --
2114 feed_cnt := 1;
2115
2116 open csr_bal_feeds ( l_element_type_id);
2117 WHILE feed_cnt <= 6 loop
2118 fetch csr_bal_feeds into l_balance_feed_id, l_balance_name;
2119 -- csr_bal_feeds%found LOOP
2120 l_assign_list := arr_assign_list(el_cnt);
2121 update pay_balance_feeds_f
2122 set scale = decode
2123 (upper(l_balance_name),
2124 upper(arrear_bal_name_list(l_assign_list)),-1,
2125 upper(arr_bal_feed_name_list (2)),-1,
2126 upper(arr_bal_feed_name_list (3)),-1,
2127 upper(arrear_bal_name_list (13)),-1, +1)
2128 where balance_feed_id = l_balance_feed_id and
2129 business_group_id is null;
2130 --
2131 feed_cnt := feed_cnt + 1;
2132 END LOOP;
2133 close csr_bal_feeds;
2134 --
2135 close crs_arr_element_type_id;
2136
2137 end if;
2138 el_cnt := el_cnt + 1;
2139 --
2140
2141 END LOOP;
2142
2143 END change_balance_feeds;
2144
2145 --
2146 -- ====================== change_post_formulas =========================
2147 --
2148 PROCEDURE change_post_formulas
2149 IS
2150
2151 total_el BINARY_INTEGER := v_total_elements;
2152 el_cnt BINARY_INTEGER := 1;
2153 lc_text VARCHAR2(2000);
2154 lc_repl1 VARCHAR2(50);
2155 lc_repl2 VARCHAR2(50);
2156 lc_repl3 VARCHAR2(50);
2157 lc_ff_name VARCHAR2(80);
2158
2159 BEGIN
2160 --
2161 WHILE el_cnt <= total_el LOOP
2162 if el_cnt not in (3,9,10) then
2163 lc_repl1 := ff_repl1 (el_cnt);
2164 lc_repl2 := ff_repl2 (el_cnt);
2165 lc_repl3 := ff_repl3 (el_cnt);
2166 lc_text := v_ff_post_text;
2167 lc_text := REPLACE(lc_text,'{REPL1}',lc_repl1);
2168 lc_text := REPLACE(lc_text,'{REPL2}',lc_repl2);
2169 lc_text := REPLACE(lc_text,'{REPL3}',lc_repl3);
2170 lc_ff_name := element_name_list (el_cnt) || 'POST_VERTEX';
2171 --
2172 update ff_formulas_f
2173 set formula_text = lc_text
2174 where formula_name = lc_ff_name and business_group_id is null;
2175 end if;
2176
2177 el_cnt := el_cnt + 1;
2178
2179 end loop;
2180
2181 end change_post_formulas;
2182 --
2183 -- ====================== change_run_results =========================
2184 --
2185 --
2186 PROCEDURE change_run_results
2187 IS
2188
2189 l_run_result_id BINARY_INTEGER;
2190 l_input_value_id BINARY_INTEGER;
2191 l_result_value varchar2(60);
2192
2193 cursor csr_run_results is
2194 select prrv.run_result_id, prrv.input_value_id, prrv.result_value
2195 from pay_run_result_values prrv,
2196 pay_input_values_f pivf,
2197 pay_element_types_f petf
2198 where prrv.input_value_id = pivf.input_value_id and
2199 upper(pivf.name) = 'PAY VALUE' and
2200 pivf.element_type_id = petf.element_type_id and
2201 petf.element_name like '%_ARR' and
2202 petf.business_group_id is null;
2203
2204 begin
2205
2206 open csr_run_results;
2207 fetch csr_run_results into l_run_result_id, l_input_value_id, l_result_value;
2208
2209 WHILE csr_run_results%found LOOP
2210
2211 if fnd_number.canonical_to_number(l_result_value) > 0 then
2212 update pay_run_result_values
2213 set result_value = fnd_number.number_to_canonical(fnd_number.canonical_to_number(result_value) * (-1))
2214 where run_result_id = l_run_result_id and
2215 input_value_id = l_input_value_id;
2216 end if;
2217 fetch csr_run_results into l_run_result_id, l_input_value_id, l_result_value;
2218 end loop;
2219
2220 close csr_run_results;
2221
2222 end change_run_results;
2223 --
2224 -- ====================== build_new_objects =========================
2225 --
2226 PROCEDURE build_new_objects
2227 IS
2228 BEGIN
2229
2230 -- initialize
2231
2232 init_ff_post_text;
2233 init_ff_repl;
2234 init_all_tables;
2235
2236 v_run_results_exist := check_run_results;
2237
2238 select name into v_business_group_name
2239 from hr_organization_units
2240 where organization_id = 0 ;
2241
2242 if v_run_results_exist = 'N' then
2243
2244 -- delete old objects
2245
2246 delete_ff_el;
2247 delete_bal_dim;
2248 delete_bal_feed;
2249 delete_arr_spec;
2250 delete_bal;
2251 delete_arr_inp;
2252 delete_arr_el;
2253
2254 delete_vertex_results;
2255 delete_post_inp;
2256 delete_post_el;
2257
2258 -- create new objects
2259
2260 create_ff_el;
2261 create_balances;
2262 create_arr_el;
2263 create_arr_feed;
2264 create_post_el;
2265 create_vertex_results;
2266 --
2267 change_balance_feeds;
2268
2269 change_post_formulas;
2270
2271 --
2272 -- this has now be removed together with changed formula result value (* -1) and balance feed sign, following
2273 -- reversing bug 585429 changes as this bug turned not to be a bug. mlisieck 14/04/98
2274 -- change_run_results;
2275
2276 cleanup;
2277
2278 else
2279 -- run results exist so be careful about what gets updated.
2280
2281 hr_utility.trace('Error. Run results exist, this script has already been applied. Contact your Oracle representative');
2282
2283 cleanup;
2284
2285 end if;
2286
2287 END build_new_objects;
2288 --
2289
2290 END pynegnet01;