[Home] [Help]
PACKAGE BODY: APPS.PAY_DB_PAY_US_GROSS
Source
1 package body pay_db_pay_us_gross as
2 /* $Header: pypusgrs.pkb 115.1 99/07/17 06:27:07 porting ship $ */
3 --
4 --
5 -- Change List
6 -- -----------
7 -- Date Name Vers Bug No Description
8 -- ---- ---- ---- ------ -----------
9 -- 02-MAR-99 J. Moyano 115.1 MLS Changes. Added
10 -- references to _TL tables.
11 --
12 --
13 /*----------------------------------------------------------------------*\
14 | PROCEDURE |
15 | create_vertex_element_names |
16 | |
17 | PURPOSE |
18 | Creates the array (PL*SQL Table) of element names and result |
19 | name prefixes required by the VERTEX PayrollTax element |
20 | of the Payroll Run. |
21 | |
22 | NOTES |
23 | |
24 \*----------------------------------------------------------------------*/
25 --
26 procedure create_vertex_element_names IS
27 --
28 begin
29 hr_utility.set_location('pay_db_pay_us_gross.create_vertex_element_names',1);
30 --
31 -- Create a table for the VERTEX element names. The name will be suffixed with
32 -- '_%elname%' to create the actual element name. e.g. 'VERTEX_WORK_bonus'.
33 -- When creating the Vertex Tax elements; The first element will be the
34 -- Recurring element and the last one (VERTEX_GROSSUP) is not created.
35 -- In the case of the grossup elements; The name will be suffixed with
36 -- '_%elname%' to create the actual element name. e.g. 'VERTEX_WORK_bonus'.
37 -- The first element (VERTEX_%elname%) will already have been created by the
38 -- form. The last element will have the Vertex gross-up formula result rule
39 -- (GROSS_UP_GROSS) and messages associated with it.
40 --
41 g_vtx_elem_tab(1) := 'VERTEX';
42 g_vtx_elem_tab(2) := 'VERTEX2';
43 g_vtx_elem_tab(3) := 'VERTEX_WORK';
44 g_vtx_elem_tab(4) := 'VERTEX_WORK2';
45 g_vtx_elem_tab(5) := 'VERTEX_SUI';
46 g_vtx_elem_tab(6) := 'VERTEX_HOME';
47 g_vtx_elem_tab(7) := 'VERTEX_RESULTS';
48 g_vtx_elem_tab(8) := '_GROSSUP';
49 --
50 -- Create a table of Input value names. All elements except %elname%_GROSSUP
51 -- require the first four input values in addition to the 'Pay Value'.
52 -- Elements number 4-7 also require the Geocode2 input value.
53 -- Note the list is also used to create the formula result rule for the
54 -- previous element that feeds the input value
55 --
56 g_vtx_input_value(0) := 'Pay Value'; g_vtx_uom(0) := 'Money';
57 g_vtx_input_value(1) := 'Jurisdiction'; g_vtx_uom(1) := 'Character';
58 g_vtx_input_value(2) := 'Percentage'; g_vtx_uom(2) := 'Money';
59 g_vtx_input_value(3) := 'Calc_Mode'; g_vtx_uom(3) := 'Character';
60 g_vtx_input_value(4) := 'Net'; g_vtx_uom(4) := 'Money';
61 g_vtx_input_value(5) := 'Geocode2'; g_vtx_uom(5) := 'Character';
62 --
63 -- Create a table of result rule names. All elements require the
64 -- GEN_FAILURE_CODE result rule (type fatal). VERTEX_RESULTS also
65 -- requires the other 18 'M'essage type result rules.
66 --
67 g_vtx_result_name(0) := 'GEN_FAILURE_CODE';
68 g_vtx_result_name(1) := 'GEN_RETURN_CODE';
69 g_vtx_result_name(2) := 'FIT_RETURN';
70 g_vtx_result_name(3) := 'FSP_RETURN';
71 g_vtx_result_name(4) := 'FICA_EE_RETURN';
72 g_vtx_result_name(5) := 'FICA_ER_RETURN';
73 g_vtx_result_name(6) := 'FUTA_RETURN';
74 g_vtx_result_name(7) := 'MEDI_EE_RETURN';
75 g_vtx_result_name(8) := 'MEDI_ER_RETURN';
76 g_vtx_result_name(9) := 'EIC_RETURN';
77 g_vtx_result_name(10) := 'WS_STT_RETURN';
78 g_vtx_result_name(11) := 'RS_STT_RETURN';
79 g_vtx_result_name(12) := 'SUI_EE_RETURN';
80 g_vtx_result_name(13) := 'SUI_ER_RETURN';
81 g_vtx_result_name(14) := 'SDI_EE_RETURN';
82 g_vtx_result_name(15) := 'SDI_ER_RETURN';
83 g_vtx_result_name(16) := 'WK_CITY_RETURN';
84 g_vtx_result_name(17) := 'WK_COUNTY_RETURN';
85 g_vtx_result_name(18) := 'RS_CITY_RETURN';
86 g_vtx_result_name(19) := 'RS_COUNTY_RETURN';
87 --
88 hr_utility.set_location('pay_db_pay_us_gross.create_vertex_element_names',2);
89 --
90 end create_vertex_element_names;
91 --
92 --
93 /*----------------------------------------------------------------------*\
94 | PROCEDURE |
95 | delete_gross_up |
96 | |
97 | PURPOSE |
98 | |
99 | NOTES |
100 | |
101 \*----------------------------------------------------------------------*/
102 --
103 procedure delete_gross_up (
104 p_business_group_id IN NUMBER,
105 p_element_name IN VARCHAR2
106 ) IS
107 -- Local Variables
108 v_element_name PAY_ELEMENT_TYPES_F.ELEMENT_NAME%TYPE;
109 --
110 CURSOR elements IS
111 SELECT pet.element_name,
112 pet.element_type_id,
113 processing_priority,
114 element_information10,
115 element_information12
116 FROM pay_element_types_f pet,
117 per_business_groups pbg
118 WHERE pbg.business_group_id + 0 = pet.business_group_id + 0
119 AND pbg.business_group_id + 0 = p_business_group_id
120 AND pet.element_name like v_element_name;
121 --
122 begin
123 --
124 hr_utility.set_location('pay_db_pay_us_gross.delete_gross_up', 1);
125 v_element_name := 'VERTEX%' || p_element_name;
126 --
127 FOR elem_rec IN elements LOOP
128 --
129 hr_utility.set_location('pay_db_pay_us_gross.delete_gross_up', 2);
130 hr_user_init_earn.do_deletions (
131 p_business_group_id=>p_business_group_id,
132 p_ele_type_id=>elem_rec.element_type_id,
133 p_ele_name=>elem_rec.element_name,
134 p_ele_priority=>elem_rec.processing_priority,
135 p_ele_info_10=>elem_rec.element_information10,
136 p_ele_info_12=>elem_rec.element_information12,
137 p_del_sess_date=>NULL,
138 p_del_val_start_date=>NULL,
139 p_del_val_end_date=>NULL
140 );
141 --
142 END LOOP;
143 --
144 hr_utility.set_location('pay_db_pay_us_gross.delete_gross_up', 3);
145 --
146 end delete_gross_up;
147 --
148 --
149 /*----------------------------------------------------------------------*\
150 | PROCEDURE |
151 | create_gross_up |
152 | |
153 | PURPOSE |
154 | |
155 | NOTES |
156 | |
157 \*----------------------------------------------------------------------*/
158 --
159 function create_gross_up (
160 p_business_group_name IN VARCHAR2 DEFAULT NULL,
161 p_element_name IN VARCHAR2,
162 p_classification IN VARCHAR2,
163 p_reporting_name IN VARCHAR2,
164 p_formula_name IN VARCHAR2,
165 p_priority IN NUMBER,
166 p_effective_start_date IN DATE DEFAULT NULL,
167 p_effective_end_date IN DATE DEFAULT NULL
168 ) RETURN NUMBER IS
169 -- Local Variables
170 v_element_type_ID NUMBER;
171 v_prev_proc_rule_ID NUMBER;
172 v_result_rule_ID NUMBER;
173 v_formula_ID NUMBER;
174 v_element_name PAY_ELEMENT_TYPES_F.ELEMENT_NAME%TYPE;
175 v_input_value_ID NUMBER;
176 v_start_date DATE;
177 v_end_date DATE;
178 v_piv_name PAY_INPUT_VALUES_F.NAME%TYPE;
179 --
180 begin
181 --
182 hr_utility.set_location('pay_db_pay_us_gross.create_gross_up', 1);
183 --
184 -- Find the Business_group_id for the parameter
185 IF p_business_group_name is NOT NULL THEN
186 SELECT business_group_id
187 INTO g_business_group_ID
188 FROM per_business_groups
189 WHERE upper(name) = upper(p_business_group_name);
190 END IF;
191 --
192 IF p_effective_start_date IS NULL THEN
193 v_start_date := g_default_start_date;
194 ELSE
195 v_start_date := p_effective_start_date;
196 END IF;
197 --
198 IF p_effective_end_date IS NULL THEN
199 v_end_date := g_max_end_date;
200 ELSE
201 v_end_date := p_effective_end_date;
202 END IF;
203 --
204 -- Get the Element_type_id and Formula_id for the existing %elname% element
205 --
206 hr_utility.set_location('pay_db_pay_us_gross.create_gross_up', 2);
207 --
208 SELECT type.element_type_id
209 INTO v_element_type_id
210 FROM pay_element_types_f_tl type_tl,
211 pay_element_types_f type
212 WHERE type_tl.element_type_id = type.element_type_id
213 and userenv('LANG') = type_tl.language
214 AND nvl(business_group_id, -1) = nvl(g_business_group_ID, -1)
215 AND type_tl.element_name = p_element_name;
216 --
217 hr_utility.set_location('pay_db_pay_us_gross.create_gross_up', 3);
218 --
219 SELECT formula_id
220 INTO v_formula_id
221 FROM ff_formulas_f ff,
222 ff_formula_types ft
223 WHERE ft.formula_type_name = 'Oracle Payroll'
224 AND ff.formula_type_id = ft.formula_type_id
225 AND ff.formula_name = p_formula_name;
226 --
227 hr_utility.set_location('pay_db_pay_us_gross.create_gross_up', 4);
228 --
229 -- Create the chain of Vertex elements ;
230 -- VERTEX--->VERTEX2--->VERTEX_WORK--->VERTEX_WORK2-->
231 -- VERTEX_HOME-->VERTEX_SUI-->VERTEX_RESULTS
232 --
233 v_prev_proc_rule_ID := create_linked_elements (
234 p_mode=>'Grossup',
235 p_element_name=>p_element_name,
236 p_element_type_id=>v_element_type_id,
237 p_formula_id=>v_formula_id,
238 p_priority=>p_priority,
239 p_business_group_name=>p_business_group_name,
240 p_start_date=>v_start_date,
241 p_end_date=>v_end_date
242 );
243 --
244 hr_utility.set_location('pay_db_pay_us_gross.create_gross_up', 5);
245 --
246 -- Create the final element '%elname%_GROSSUP'
247 --
248 v_element_name := p_element_name || g_vtx_elem_tab(g_max_elnum);
249 --
250 hr_utility.trace('** Creating Element ' || v_element_name);
251 --
252 v_element_type_ID := pay_db_pay_setup.create_element (
253 p_element_name=>v_element_name,
254 p_description=>'Gross up element for ' || p_element_name,
255 p_reporting_name=>p_reporting_name || '_final',
256 p_classification_name=>p_classification,
257 p_processing_type=>'N',
258 p_mult_entries_allowed=>'Y',
259 p_processing_priority=>p_priority + g_max_elnum,
260 p_standard_link_flag=>'N',
261 p_post_termination_rule=>'Final Close',
262 p_indirect_only_flag=>'N',
263 p_effective_start_date=>v_start_date,
264 p_effective_end_date=>v_end_date,
265 p_business_group_name=>p_business_group_name,
266 p_legislation_code=>'US'
267 );
268 --
269 hr_utility.set_location('pay_db_pay_us_gross.create_gross_up', 6);
270 --
271 -- Get the input_value_id for the 'Pay Value' input value that was created
272 -- for the '%elname%_GROSSUP' element.
273 --
274 v_piv_name := hr_input_values.get_pay_value_name ('US');
275 --
276 SELECT piv.input_value_id
277 INTO v_input_value_ID
278 FROM pay_input_values_f_tl piv_tl,
279 pay_input_values_f piv,
280 pay_element_types_f_tl pet_tl,
281 pay_element_types_f pet
282 WHERE piv_tl.input_value_id = piv.input_value_id
283 and pet_tl.element_type_id = pet.element_type_id
284 and userenv('LANG') = piv_tl.language
285 and userenv('LANG') = pet_tl.language
286 AND piv_tl.name = v_piv_name
287 AND nvl(piv.business_group_id, -1) = nvl(g_business_group_ID, -1)
288 AND pet.element_type_id = piv.element_type_id
289 AND pet_tl.element_name = v_element_name;
290 --
291 hr_utility.set_location('pay_db_pay_us_gross.create_gross_up',7);
292 --
293 -- Create the Result rule from VERTEX_RESULTS to feed the
294 -- 'Pay Value' input value.
295 --
296 v_result_rule_id := create_result_rule (
297 p_result_name=>'GROSS_UP_GROSS',
298 p_stat_proc_id=>v_prev_proc_rule_ID,
299 p_input_value_id=>v_input_value_ID,
300 p_effective_start_date=>v_start_date,
301 p_effective_end_date=>v_end_date
302 );
303 --
304 hr_utility.set_location('pay_db_pay_us_gross.create_gross_up', 8);
305 --
306 -- Get the input_value_id for the 'Pay Value' input value that was created
307 -- for the FIT_GROSSUP_ADJUSTMENT element.
308 --
309 SELECT piv.input_value_id
310 INTO v_input_value_ID
311 FROM pay_input_values_f_tl piv_tl,
312 pay_input_values_f piv,
313 pay_element_types_f pet
314 WHERE piv_tl.input_value_id = piv.input_value_id
315 and userenv('LANG') = piv_tl.language
316 and piv_tl.name = v_piv_name
317 AND piv.business_group_id is null
318 AND pet.element_type_id = piv.element_type_id
319 AND pet.element_name = 'FIT_GROSSUP_ADJUSTMENT';
320 --
321 hr_utility.set_location('pay_db_pay_us_gross.create_gross_up', 10);
322 --
323 -- Create the the result rule to feed the FIT_GROSSUP_ADJUSTMENT tax element
324 --
325 v_result_rule_id := create_result_rule (
326 p_result_name=>'FIT_GROSSUP_ADJUSTMENT',
327 p_stat_proc_id=>v_prev_proc_rule_ID,
328 p_input_value_id=>v_input_value_ID,
329 p_effective_start_date=>v_start_date,
330 p_effective_end_date=>v_end_date
331 );
332 --
333 hr_utility.set_location('pay_db_pay_us_gross.create_gross_up', 11);
334 --
335 return v_element_type_ID;
336 --
337 EXCEPTION
338 WHEN NO_DATA_FOUND THEN
339 null;
340 --
341 end create_gross_up;
342 --
343 --
344 /*----------------------------------------------------------------------*\
345 | FUNCTION |
346 | create_linked_elements |
347 | |
348 | PURPOSE |
349 | |
350 | NOTES |
351 | |
352 \*----------------------------------------------------------------------*/
353 function create_linked_elements (
354 p_mode VARCHAR2,
355 p_element_name VARCHAR2,
356 p_element_type_id NUMBER,
357 p_formula_id NUMBER,
358 p_priority NUMBER,
359 p_business_group_name VARCHAR2,
360 p_start_date DATE,
361 p_end_date DATE
362 ) RETURN NUMBER IS
363 -- Local Variables
364 v_formula_ID NUMBER;
365 v_element_type_ID NUMBER;
366 v_element_name PAY_ELEMENT_TYPES_F.ELEMENT_NAME%TYPE;
367 v_priority_incr NUMBER;
368 v_classification VARCHAR2(80);
369 v_post_termination VARCHAR2(80);
370 v_description VARCHAR2(240);
371 v_element_num BINARY_INTEGER;
372 v_start_elnum BINARY_INTEGER;
373 v_prev_proc_rule_ID NUMBER;
374 v_result_rule_ID NUMBER;
375 v_input_value_ID NUMBER;
376 v_linknum NUMBER;
377 v_msg_num NUMBER;
378 v_processing_priority NUMBER;
379 --
380 begin
381 hr_utility.set_location('pay_db_pay_us_gross.create_linked_elements', 1);
382 --
383 v_formula_ID := p_formula_id;
384 v_element_type_ID := p_element_type_id;
385 --
386 IF p_mode = 'Grossup' THEN
387 v_priority_incr := 1;
388 v_processing_priority := p_priority;
389 v_start_elnum := 1;
390 v_classification := 'Information';
391 v_post_termination := 'Final Close';
392 v_description := 'Gross Up program element';
393 --
394 -- Get the Status Processing Rule for the previous (GROSSUP) element
395 --
396 SELECT STATUS_PROCESSING_RULE_ID
397 INTO v_prev_proc_rule_id
398 FROM PAY_STATUS_PROCESSING_RULES_F
399 WHERE ELEMENT_TYPE_ID = v_element_type_ID;
400 --
401 hr_utility.set_location('pay_db_pay_us_gross.create_linked_elements', 2);
402 --
403 ELSE
404 v_priority_incr := 10;
405 v_processing_priority := p_priority - v_priority_incr;
406 v_start_elnum := 2;
407 v_classification := 'Tax Deductions';
408 v_post_termination := 'Actual Termination';
409 v_description := 'PayrollTax Calculation program VERTEX Element';
410 --
411 -- Create a Status Processing Rule for the previous (VERTEX) element
412 --
413 v_prev_proc_rule_id := create_status_proc_rule (
414 p_effective_start_date=>p_start_date,
415 p_effective_end_date=>p_end_date,
416 p_formula_id=>v_formula_ID,
417 p_element_type_id=>v_element_type_ID
418 );
419 --
420 hr_utility.set_location('pay_db_pay_us_gross.create_linked_elements', 3);
421 --
422 END IF;
423 --
424 begin
425 FOR v_element_num in v_start_elnum..g_max_elnum - 1 LOOP
426 --
427 hr_utility.set_location('pay_db_pay_us_gross.create_linked_elements', 4);
428 --
429 -- Find the formula...
430 --
431 --
432 SELECT formula_id
433 INTO v_formula_id
434 FROM ff_formulas_f ff,
435 ff_formula_types ft
436 WHERE ft.formula_type_name = 'Oracle Payroll'
437 AND ff.formula_type_id = ft.formula_type_id
438 AND ff.formula_name = g_vtx_elem_tab(v_element_num);
439 --
440 hr_utility.set_location('pay_db_pay_us_gross.create_linked_elements', 5);
441 --
442 -- Create the element...
443 --
444 IF p_mode = 'Grossup' THEN
445 v_element_name := g_vtx_elem_tab(v_element_num)
446 || '_' || p_element_name;
447 ELSE
448 v_element_name := g_vtx_elem_tab(v_element_num);
449 END IF;
450 --
451 hr_utility.trace('** Creating Element ' || v_element_name);
452 --
453 v_element_type_ID := pay_db_pay_setup.create_element (
454 p_element_name=>v_element_name,
455 p_description=>v_description,
456 p_reporting_name=>v_element_name,
457 p_classification_name=>v_classification,
458 p_processing_type=>'N',
459 p_mult_entries_allowed=>'Y',
460 p_processing_priority=>v_processing_priority +
461 (v_element_num * v_priority_incr),
462 p_standard_link_flag=>'N',
463 p_post_termination_rule=>v_post_termination,
464 p_indirect_only_flag=>'N',
465 p_effective_start_date=>p_start_date,
466 p_effective_end_date=>p_end_date,
467 p_business_group_name=>p_business_group_name,
468 p_legislation_code=>'US'
469 );
470 --
471 hr_utility.set_location('pay_db_pay_us_gross.create_linked_elements', 6);
472 --
473 IF (p_mode = 'Grossup') THEN
474 --
475 -- Create the 'Pay Value' input as this is an Information element
476 --
477 v_input_value_ID := pay_db_pay_setup.create_input_value (
478 p_element_name=>v_element_name,
479 p_uom=>g_vtx_uom(0),
480 p_name=>g_vtx_input_value(0),
481 p_display_sequence=>1,
482 p_generate_db_item_flag=>'N',
483 p_business_group_name=>p_business_group_name,
484 p_effective_start_date=>p_start_date,
485 p_effective_end_date=>p_end_date
486 );
487 ELSE
488 --
489 -- Delete the entity horizon as it gets recreated on startup data load
490 --
491 hrdyndbi.delete_element_type_dict(
492 p_element_type_id=>v_element_type_ID);
493 END IF;
494 --
495 -- Create the input values and run results
496 --
497 v_linknum := 1;
498 /*
499 * This is not really an infinite loop as the exception NO_DATA_FOUND
500 * will be raised if the end of the PL*SQL table has been reached,
501 * or the EXIT WHEN condtion is met
502 */
503 begin
504 LOOP
505 --
506 EXIT WHEN (v_linknum = 5) and (v_element_num < 5);
507 /* Do not create geocode2 result for first 4 elements */
508 --
509 create_indirect_link (
510 p_element_name=>v_element_name,
511 p_uom=>g_vtx_uom(v_linknum),
512 p_name=>g_vtx_input_value(v_linknum),
513 p_display_sequence=>v_linknum + 1,
514 p_stat_proc_id=>v_prev_proc_rule_ID,
515 p_business_group_name=>p_business_group_name,
516 p_effective_start_date=>p_start_date,
517 p_effective_end_date=>p_end_date
518 );
519 --
520 hr_utility.set_location(
521 'pay_db_pay_us_gross.create_linked_elements', 7);
522 --
523 v_linknum := v_linknum + 1;
524 --
525 END LOOP;
526 --
527 EXCEPTION
528 WHEN NO_DATA_FOUND THEN
529 null;
530 end;
531 --
532 hr_utility.set_location('pay_db_pay_us_gross.create_linked_elements', 8);
533 --
534 -- Create the (fatal) message result for the previous element
535 --
536 v_result_rule_id := create_result_rule (
537 p_result_name=>g_vtx_result_name(0),
538 p_result_type=>'M',
539 p_severity=>'F',
540 p_stat_proc_id=>v_prev_proc_rule_ID,
541 p_effective_start_date=>p_start_date,
542 p_effective_end_date=>p_end_date
543 );
544 --
545 hr_utility.set_location('pay_db_pay_us_gross.create_linked_elements', 9);
546 --
547 --
548 -- Create the Status Processing Rule for the previous element
549 --
550 v_prev_proc_rule_id := create_status_proc_rule (
551 p_effective_start_date=>p_start_date,
552 p_effective_end_date=>p_end_date,
553 p_formula_id=>v_formula_ID,
554 p_element_type_id=>v_element_type_ID
555 );
556 --
557 hr_utility.set_location('pay_db_pay_us_gross.create_linked_elements', 10);
558 --
559 END LOOP;
560 --
561 EXCEPTION
562 WHEN NO_DATA_FOUND THEN
563 null;
564 end; -- FOR v_element_num in v_start_elnum..
565 --
566 -- The VERTEX_RESULTS element has just been created with its input
567 -- values and status processing rule. It now needs :
568 -- 1) The Fatal Message Result.
569 -- 2) the Warning Message results.
570 --
571 hr_utility.set_location('pay_db_pay_us_gross.create_linked_elements', 11);
572 --
573 /* Create the Fatal message result rule */
574 --
575 v_result_rule_id := create_result_rule (
576 p_result_name=>g_vtx_result_name(0),
577 p_result_type=>'M',
578 p_severity=>'F',
579 p_stat_proc_id=>v_prev_proc_rule_ID,
580 p_effective_start_date=>p_start_date,
581 p_effective_end_date=>p_end_date
582 );
583 --
584 v_msg_num := 1;
585 /*
586 * Create the warning message result rules.
587 *
588 * This is not really an infinite loop as the exception NO_DATA_FOUND
589 * will be raised when the end of the PL*SQL table has been reached.
590 */
591 begin
592 LOOP
593 --
594 hr_utility.trace('** Cr. Rslt Rule ' || g_vtx_result_name(v_msg_num));
595 --
596 v_result_rule_ID := pay_db_pay_us_gross.create_result_rule (
597 p_result_name=>g_vtx_result_name(v_msg_num),
598 p_result_type=>'M', -- 'M'essage
599 p_severity=>'W', -- 'W'arning
600 p_stat_proc_ID=>v_prev_proc_rule_ID,
601 p_effective_start_date=>p_start_date,
602 p_effective_end_date=>p_end_date
603 );
604 --
605 v_msg_num := v_msg_num + 1;
606 --
607 hr_utility.set_location('pay_db_pay_us_gross.create_linked_elements', 12);
608 --
609 END LOOP;
610 --
611 EXCEPTION
612 WHEN NO_DATA_FOUND THEN
613 null;
614 end;
615 --
616 return v_prev_proc_rule_id;
617 --
618 end create_linked_elements;
619 --
620 --
621 /*----------------------------------------------------------------------*\
622 | PROCEDURE |
623 | create_indirect_link |
624 | |
625 | PURPOSE |
626 | Create an input value on the current element and the result |
627 | on the previous element, using its status processing rule ID |
628 | |
629 | NOTES |
630 | |
631 \*----------------------------------------------------------------------*/
632 procedure create_indirect_link (
633 p_element_name VARCHAR2,
634 p_uom VARCHAR2,
635 p_name VARCHAR2,
636 p_display_sequence NUMBER,
637 p_stat_proc_id NUMBER,
638 p_business_group_name VARCHAR2,
639 p_effective_start_date DATE,
640 p_effective_end_date DATE
641 ) IS
642 -- Local Variables
643 v_input_value_ID NUMBER;
644 v_result_rule_ID NUMBER;
645 --
646 begin
647 --
648 hr_utility.set_location('pay_db_pay_us_gross.create_indirect_link', 1);
649 --
650 hr_utility.trace('** Creating Input Value ' || p_name);
651 --
652 v_input_value_ID := pay_db_pay_setup.create_input_value (
653 p_element_name=>p_element_name,
654 p_uom=>p_uom,
655 p_name=>p_name,
656 p_display_sequence=>p_display_sequence,
657 p_generate_db_item_flag=>'N',
658 p_business_group_name=>p_business_group_name,
659 p_effective_start_date=>p_effective_start_date,
660 p_effective_end_date=>p_effective_end_date
661 );
662 --
663 hr_utility.set_location('pay_db_pay_us_gross.create_indirect_link', 2);
664 --
665 v_result_rule_id := create_result_rule (
666 p_result_name=>p_name,
667 p_stat_proc_id=>p_stat_proc_ID,
668 p_input_value_id=>v_input_value_ID,
669 p_effective_start_date=>p_effective_start_date,
670 p_effective_end_date=>p_effective_end_date
671 );
672 --
673 hr_utility.set_location('pay_db_pay_us_gross.create_indirect_link', 3);
674 --
675 end create_indirect_link;
676 --
677 --
678 /*----------------------------------------------------------------------*\
679 | FUNCTION |
680 | create_status_proc_rule |
681 | |
682 | PURPOSE |
683 | create one row in the PAY_STATUS_PROCESSING_RULES_F table |
684 | for the VERTEX formula |
685 | |
686 | NOTES |
687 | |
688 \*----------------------------------------------------------------------*/
689 --
690 function create_status_proc_rule(
691 p_effective_start_date IN DATE,
692 p_effective_end_date IN DATE,
693 p_formula_ID IN NUMBER DEFAULT NULL,
694 p_element_type_ID IN NUMBER
695 ) RETURN NUMBER IS
696 -- Local Variables
697 v_rule_ID NUMBER; -- status processing rule ID
698 --
699 begin
700 --
701 hr_utility.set_location('pay_db_pay_us_gross.create_status_proc_rule', 1);
702 -- Create the status_processing_rule_id
703 --
704 SELECT pay_status_processing_rules_s.nextval
705 INTO v_rule_ID
706 FROM sys.dual;
707 --
708 hr_utility.set_location('pay_db_pay_us_gross.create_status_proc_rule', 2);
709 --
710 INSERT INTO PAY_STATUS_PROCESSING_RULES_F
711 (
712 STATUS_PROCESSING_RULE_ID,
713 EFFECTIVE_START_DATE,
714 EFFECTIVE_END_DATE,
715 BUSINESS_GROUP_ID,
716 LEGISLATION_CODE,
717 ELEMENT_TYPE_ID,
718 ASSIGNMENT_STATUS_TYPE_ID,
719 FORMULA_ID,
720 PROCESSING_RULE,
721 COMMENT_ID,
722 LEGISLATION_SUBGROUP,
723 LAST_UPDATE_DATE,
724 LAST_UPDATED_BY,
725 LAST_UPDATE_LOGIN,
726 CREATED_BY,
727 CREATION_DATE
728 )
729 select
730 v_rule_ID,
731 p_effective_start_date,
732 p_effective_end_date,
733 g_business_group_ID,
734 'US',
735 p_element_type_ID,
736 ASSIGNMENT_STATUS_TYPE_ID, -- assignment_status_type_id
737 p_formula_ID,
738 'P', -- processing rule
739 NULL, -- comment ID
740 NULL, -- legislation subgroup
741 g_todays_date,
742 -1,
743 -1,
744 -1,
745 g_todays_date
746 from per_assignment_status_types
747 where USER_STATUS = 'Active Assignment';
748 --
749 hr_utility.set_location('pay_db_pay_us_gross.create_status_proc_rule', 3);
750 --
751 return v_rule_ID;
752 --
753 end create_status_proc_rule;
754 --
755 --
756 /*----------------------------------------------------------------------*\
757 | FUNCTION |
758 | create_result_rule |
759 | |
760 | PURPOSE |
761 | Creates a formula result rule for a combination of an element, |
762 | a result name, its type, and an input value. Required by |
763 | the VERTEX PayrollTax element of the Payroll Run. |
764 | |
765 | NOTES |
766 | |
767 \*----------------------------------------------------------------------*/
768 --
769 function create_result_rule(
770 p_legislation_code VARCHAR2 DEFAULT 'US',
771 p_result_name VARCHAR2 ,
772 p_result_type VARCHAR2 DEFAULT 'I',
773 p_severity VARCHAR2 DEFAULT NULL,
774 p_stat_proc_ID NUMBER,
775 p_input_value_ID NUMBER DEFAULT NULL,
776 p_effective_start_date DATE,
777 p_effective_end_date DATE
778 ) RETURN number IS
779 -- Local Variables
780 v_rule_ID NUMBER;
781 --
782 begin
783 hr_utility.set_location('pay_db_pay_us_gross.create_result_rule', 1);
784 --
785 -- Select the next sequence number for PAY_FORMULA_RESULT_RULES
786 SELECT pay_formula_result_rules_s.nextval
787 INTO v_rule_ID
788 FROM sys.dual;
789 --
790 hr_utility.set_location('pay_db_pay_us_gross.create_result_rule', 2);
791 --
792 hr_utility.trace('** Creating Result Rule ' || p_result_name);
793 --
794 -- Now do the INSERT into the PAY_FORMULA_RESULT_RULES_F table
795 INSERT INTO pay_formula_result_rules_f
796 (
797 FORMULA_RESULT_RULE_ID,
798 EFFECTIVE_START_DATE,
799 EFFECTIVE_END_DATE,
800 BUSINESS_GROUP_ID,
801 LEGISLATION_CODE,
802 STATUS_PROCESSING_RULE_ID,
803 RESULT_NAME,
804 RESULT_RULE_TYPE,
805 LEGISLATION_SUBGROUP,
806 SEVERITY_LEVEL,
807 INPUT_VALUE_ID,
808 LAST_UPDATE_DATE,
809 LAST_UPDATED_BY,
810 LAST_UPDATE_LOGIN,
811 CREATED_BY,
812 CREATION_DATE
813 )
814 values
815 (
816 v_rule_ID,
817 p_effective_start_date,
818 p_effective_end_date,
819 g_business_group_ID,
820 p_legislation_code, -- Legislation Code
821 p_stat_proc_ID, -- Status Processing Rule ID
822 upper(p_result_name),
823 p_result_type,
824 NULL, -- Legislation Subgroup
825 p_severity,
826 p_input_value_ID,
827 g_todays_date,
828 -1,
829 -1,
830 -1,
831 g_todays_date
832 );
833 --
834 hr_utility.set_location('pay_db_pay_us_gross.create_result_rule', 3);
835 --
836 return v_rule_ID;
837 --
838 end create_result_rule;
839 --
840 --
841 begin -- Initialization
842 hr_utility.set_location('pay_db_pay_us_gross.initialization', 1);
843 create_vertex_element_names;
844 hr_utility.set_location('pay_db_pay_us_gross.initialization', 2);
845 end pay_db_pay_us_gross;