[Home] [Help]
PACKAGE BODY: APPS.PAY_US_RULES
Source
1 PACKAGE BODY PAY_US_RULES AS
2 /* $Header: pyusrule.pkb 120.24.12010000.2 2008/08/29 08:57:55 sudedas ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Change History
21 ---------------------
22
23 Date Name Vers Bug No Description
24 ----------- ---------- ------ ------- ------------------------------------
25 28-Aug-2008 sudedas 115.42 7269477 Modified cursor get_depoadvice_deatils
26 and get_check_depoad_details.
27 Added effective_date Join Condition.
28 5-Sep-2007 sausingh 115.41 6392875 Modified the cursor get_check_num_for_depad
29 31-JUL-2007 sausingh 115.40 5635335 Added cursor to capture check details if
30 deposit advice is run after check-writer for an
31 assignment.
32 05-JUL-2007 sausingh 115.39 5635335 Added cursors
33 get_check_depoad_details
34 get_preassact_id
35 get_depoadvice_deatils
36 To get Employer's account detail and
37 deposit advice number in the XML
38 26-JUN-2007 sausingh 115.38 5635335 Added <Chech_Amount> Tag to get the Check
39 Amount in check writer.
40 26-Jun-2007 sudedas 115.36 5635335 Modified add_custom_xml to print
41 Check Number and Amount. Added
42 procedure get_token_names.
43 24-May-2007 sausingh 115.33 5635335 Added procedure add_custom_xml
44 and some functions to display
45 Net Pay Amount in Words in Archive
46 Check Writer/Deposit Advice.
47 13-MAR-2007 kvsankar 115.33 FLSA For some scenarios, the function
48 get_time_def_for_entry and
49 get_time_def_for_entry_func was
50 associating custom Time Definitions
51 with seeded 'Regular Salary' and
52 'Regular Wages'.
53 Also the caching logic was
54 modified so that it does not return
55 seeded Time Definition(if FLSA Time
56 Definitionis is specified) for other
57 elements if 'Regular Salary' or
58 'Regular Wages' happens to be the
59 first element to be processed.
60 18-FEB-2007 kvsankar 115.32 5876883 Modified element_template_pre_process
61 5696187 and element_template_post_process
62 for the new template names
63 'US FLSA <Classification Name>'
64 and
65 'US <Classification Name>'
66 20-OCT-2006 asasthan 115.31 5610376 Regular Salary and Regular
67 Wages should not be associated
68 with FLSA Time Definitions as
69 these elements should not be
70 allocated.Code has been modified
71 to ensure that these seeded
72 elements do not inherit the time def
73 set at Payroll. Modified caching
74 so that seeded elements do not
75 blindly inherit time def set
76 by reduce regular element.
77
78 18-APR-2006 saikrish 115.30 5161974 Creating Commission balance feeds.
79 13-APR-2006 ahanda 115.29 Added a formula result rule to the
80 seeded Hours by Rate element
81 TEMPLATE_EARNING -> Pay Value
82 20-SEP-2005 rdhingra 115.28 FLSA2 Priority for Reduce Regular has to
83 be set to 1526. Updating
84 element_template_upd_user_stru
85 15-SEP-2005 rdhingra 115.27 FLSA2 Changed reporting name of FLSA Adjust
86 FROM: Retro <element name>
87 TO: <element name> Adjustment
88 15-SEP-2005 rdhingra 115.26 FLSA2 Added an AND clause in
89 CURSOR: get_payroll_time_definition_id
90 FUNCTION: get_time_def_for_entry_func
91 to take time_definition id as of
92 payroll_period end date
93 02-SEP-2005 asasthan 115.25 FLSA2 Attached Proration Event to
94 FLSA Period Adjustment Element
95 31-AUG-2005 rdhingra 115.24 FLSA2 Changes for FLSA Phase 2
96 Premium Adjutment
97 11-AUG-2005 kvsankar 115.23 FLSA2 Created a new function
98 get_time_def_for_entry_func
99 which is called by the procedure
100 get_time_def_for_entry
101 10-AUG-2005 rdhingra 115.22 FLSA2 Exclusion rule added for Overtime
102 and Premium categories.
103 4542621 element_information_category updated
104 from US_IMPUTED_EARNINGS
105 to US_IMPUTED EARNINGS
106 08-AUG-2005 rdhingra 115.21 FLSA2 Added default retro component
107 for "Entry Changes"
108 02-AUG-2005 rdhingra 115.20 FLSA2 Added retro group "Entry Changes"
109 to all FLSA Calc elements in Post
110 Process
111 27-JUL-2005 rdhingra 115.19 FLSA2 Modified element_template_pre_process
112 to remove the exclusion rules for
113 HXR when calculation_rule = 'US Earnings'
114 Added details pertaining to Augments
115 Added procedures delete_pre_process
116 and delete_post_process
117 09-JUN-2005 kvsankar 115.18 4420211 Modified the
118 element_template_post_process
119 to set the Mandatory Flag for
120 'Deduction Processing' to 'N'
121 for 'Non-payroll Payments'
122 24-MAY-2005 kvsankar 115.17 FLSA Modified the
123 element_template_upd_user_stru
124 to set the Processing priority
125 depending on whether Reduce
126 Regular checkbox is checked
127 or not.
128 23-MAY-2005 asasthn 115.15 FLSA Modified defaulting of JOB CODE
129 23-MAY-2005 rdhingra 115.14 FLSA Modified get_time_def_for_entry
130 Problem in cursor call.
131 21-MAY-2005 rdhingra 115.13 FLSA Added code to get the default
132 time_definition_id in procedure
133 get_time_def_for_entry
134 05-MAY-2005 kvsankar 115.12 FLSA Modified the
135 element_template_post_process to set
136 the Time Definition Type of
137 Base element to 'G' if the element
138 has FLSA Earnings checked
139 05-MAY-2005 kvsankar 115.11 FLSA is created using US FLSA template
140 04-MAY-2005 ahanda 115.10 FLSA Modified get_time_def_for_entry
141 29-APR-2005 kvsankar 115.9 FLSA Modified the
142 element_template_post_process to set
143 the Time Definition Type of only the
144 Base element to 'G' if the element
145 is created using US FLSA template
146 29-APR-2005 rdhingra 115.8 FLSA Added Procedure call for
147 get_time_def_for_entry
148 28-APR-2005 sodhingr 115.7 Added the function work_schedule
149 _total_hours used by new work
150 schedule functionality
151 27-APR-2005 kvsankar 115.6 FLSA Modified the Element Template PRE
152 Process to not to create Special
153 Inputs element, if 'FLSA Hours' or
154 'Overtime Base' checkboxes are
155 checked. This check is only for
156 'US FLSA' template.
157 27-APR-2005 kvsankar 115.5 FLSA Modified the Element Template PRE,
158 UPDATE and the POST Process for
159 incluing the new template created
160 for FLSA
161 17-APR-2005 rdhingra 115.3 Changed for Global Element
162 Template Migration. Added defi-
163 nitions for user exit calls
164 Pre-Process, upd_user_stru and
165 Post-Process made from Global
166 Element Template. Also added
167 definition of get_obj_id
168 function.
169 23-AUG-2004 kvsankar 115.2 3840248 Modified the IF condition to
170 correctly set END IF
171 12-MAY-2004 sdahiya 115.1 Modified phase to plb
172 25-APR-2004 sdahiya 115.0 3622290 Created.
173
174 ****************************************************************************/
175
176
177
178 /****************************************************************************
179 Name : GET_DEFAULT_JUSRIDICTION
180 Description : This function returns the default jurisdiction code which is
181 used for involuntary deduction elements if the end user does
182 not specify jurisdiction input value.
183 *****************************************************************************/
184
185 PROCEDURE get_default_jurisdiction(p_asg_act_id number,
186 p_ee_id number,
187 p_jurisdiction in out nocopy varchar2) IS
188
189 -- Cursor to get classification of elements.
190 cursor csr_ele_classification is
191 select classification_name
192 from pay_element_classifications pec,
193 pay_element_types_f pet,
194 pay_element_entries_f pee
195 where pec.classification_id = pet.classification_id
196 and pet.element_type_id = pee.element_type_id
197 and pee.element_entry_id = p_ee_id;
198
199 -- Cursor to get 'Work At Home' flag of current assignment.
200 cursor csr_wrk_at_home is
201 select assign.work_at_home
202 from per_all_assignments_f assign,
203 pay_assignment_actions paa
204 where paa.assignment_id = assign.assignment_id
205 and paa.assignment_action_id = p_asg_act_id
206 and assign.effective_start_date = (select max(paf.effective_start_date)
207 from per_all_assignments_f paf
208 where paf.assignment_id = assign.assignment_id);
209
210 -- Cursor to get address information for the case when
211 -- person is working at home.
212 cursor csr_per_regions is
213 select nvl(addr.add_information17,addr.region_2),
214 nvl(addr.add_information19,addr.region_1),
215 nvl(addr.add_information18,addr.town_or_city),
216 nvl(addr.add_information20,addr.postal_code)
217 from per_addresses addr,
218 per_all_assignments_f assign,
219 pay_assignment_actions paa
220 where paa.assignment_id = assign.assignment_id
221 and paa.assignment_action_id = p_asg_act_id
222 and addr.person_id = assign.person_id
223 and addr.primary_flag = 'Y'
224 and assign.effective_start_date
225 between nvl(addr.date_from, assign.effective_start_date)
226 and nvl(addr.date_to,assign.effective_start_date)
227 and assign.effective_start_date = (select max(paf.effective_start_date)
228 from per_all_assignments_f paf
229 where paf.assignment_id = assign.assignment_id);
230
231
232 -- Cursor to get address information for the case when
233 -- person is NOT working at home.
234 cursor csr_loc_regions is
235 select nvl(hrloc.loc_information17,hrloc.region_2),
236 nvl(hrloc.loc_information19,hrloc.region_1),
237 nvl(hrloc.loc_information18,hrloc.town_or_city),
238 nvl(hrloc.loc_information20,hrloc.postal_code)
239 from hr_locations hrloc,
240 hr_soft_coding_keyflex hrsckf,
241 per_all_assignments_f assign,
242 pay_assignment_actions paa
243 where paa.assignment_id = assign.assignment_id
244 and paa.assignment_action_id = p_asg_act_id
245 and assign.soft_coding_keyflex_id = hrsckf.soft_coding_keyflex_id
246 and nvl(hrsckf.segment18,assign.location_id) = hrloc.location_id
247 and assign.effective_start_date = (select max(paf.effective_start_date)
248 from per_all_assignments_f paf
249 where paf.assignment_id = assign.assignment_id);
250
251 l_asg_wrk_at_home varchar2(1);
252 l_ele_classification pay_element_classifications.classification_name%type;
253 l_proc_name varchar2(50);
254
255 l_override_adr_region_2 per_addresses.add_information17%type;
256 l_override_adr_region_1 per_addresses.add_information17%type;
257 l_override_adr_city per_addresses.add_information17%type;
258 l_override_adr_postal_code per_addresses.add_information17%type;
259
260 BEGIN
261 l_pkg_name := 'pay_us_rules.';
262 l_proc_name := l_pkg_name||'default_jurisdiction';
263 hr_utility.trace('Entering '||l_proc_name);
264
265 open csr_ele_classification;
266 fetch csr_ele_classification into l_ele_classification;
267 close csr_ele_classification;
268
269 hr_utility.trace('Classification of element entry id '|| p_ee_id ||' is '||l_ele_classification);
270
271 if l_ele_classification = 'Involuntary Deductions' then
272 open csr_wrk_at_home;
273 fetch csr_wrk_at_home into l_asg_wrk_at_home;
274 close csr_wrk_at_home;
275
276 if l_asg_wrk_at_home = 'Y'then
277 open csr_per_regions;
278 fetch csr_per_regions into l_override_adr_region_2,l_override_adr_region_1,
279 l_override_adr_city, l_override_adr_postal_code;
280 close csr_per_regions;
281 else
282 open csr_loc_regions;
283 fetch csr_loc_regions into l_override_adr_region_2, l_override_adr_region_1,
284 l_override_adr_city, l_override_adr_postal_code;
285 close csr_loc_regions;
286 end if;
287
288 p_jurisdiction := hr_us_ff_udfs.addr_val (l_override_adr_region_2,
289 l_override_adr_region_1,
290 l_override_adr_city,
291 l_override_adr_postal_code);
292 hr_utility.trace('Default jurisdiction code is '||p_jurisdiction);
293 end if;
294 hr_utility.trace('Leaving '||l_proc_name);
295 END get_default_jurisdiction;
296
297
298 /****************************************************************************/
299 /* FUNCTION element_template_pre_process */
300 /****************************************************************************/
301
302 FUNCTION element_template_pre_process (p_rec IN pay_ele_tmplt_obj)
303 RETURN pay_ele_tmplt_obj IS
304 BEGIN
305 hr_utility.TRACE ('Entering pay_us_rules.element_template_pre_process');
306 hr_utility.TRACE ('Legislation Code ' || lrec.legislation_code);
307 -- INITIALIZING THE GLOBAL VARIABLE
308 lrec := NULL;
309 -- DEFAULTING TO input VARIABLE
310 lrec := p_rec;
311
312
313
314
315 IF (lrec.calculation_rule = 'US ' || lrec.element_classification) THEN
316
317 -------------------------
318 -- Determine the priority
319 -------------------------
320 -- variable lrec.configuration_information22 controls the exclusion rules
321 IF (lrec.element_classification = 'Earnings') THEN
322 lrec.preference_information11 := -249; --l_si_rel_priority
323 lrec.preference_information9 := 250; --l_sf_rel_priority
324 lrec.preference_information7 := 'US_EARNINGS';
325 lrec.preference_information12 := NULL; --l_skip_formula
326 ELSIF (lrec.element_classification = 'Supplemental Earnings') THEN
327 lrec.preference_information11 := -499; --l_si_rel_priority
328 lrec.preference_information9 := 500; --l_sf_rel_priority
329 lrec.preference_information7 := 'US_SUPPLEMENTAL EARNINGS';
330 lrec.configuration_information22 := 'N'; --l_ele_type_usages
331 lrec.preference_information12 := NULL; --l_skip_formula
332 ELSIF (lrec.element_classification = 'Imputed Earnings') THEN
333 lrec.preference_information11 := -249; --l_si_rel_priority
334 lrec.preference_information9 := 250; --l_sf_rel_priority
335 lrec.preference_information7 := 'US_IMPUTED EARNINGS';
336 lrec.preference_information12 := NULL; --l_skip_formula
337 ELSIF (lrec.element_classification = 'Non-payroll Payments') THEN
338 lrec.preference_information11 := -249; --l_si_rel_priority
339 lrec.preference_information9 := 250; --l_sf_rel_priority
340 lrec.preference_information7 := 'US_NON-PAYROLL PAYMENTS';
341 lrec.preference_information12 := NULL; --l_skip_formula
342 lrec.configuration_information22 := 'N'; --l_ele_type_usages
343 END IF;
344
345 --------------------------------------------
346 -- set the appropriate exclusion rules
347 --------------------------------------------
348 -- The Configuration Flex segments for the Exclusion Rules are as follows:
349 -- {Do not match the config numbers with variables used below}
350 -- CONFIGURATION_INFORMATION1 - Xclude SI and SF elements
351 -- IF ele_processing_type='N'
352 -- CONFIGURATION_INFORMATION2 - Flat Amount/Percentage
353 -- CONFIGURATION_INFORMATION7 - Xclude objects IF overtime base is
354 -- not checked
355 -- CONFIGURATION_INFORMATION8 - Excl rule for special features elements
356 -- CONFIGURATION_INFORMATION9 - Excl rule for input value location in
357 -- case of special features elements.
358 -- CONFIGURATION_INFORMATION10 - Excl rule for student earnings
359 -- CONFIGURATION_INFORMATION11 - Excl rule for Regular Earning to decide
360 -- 401k feed.
361 -- CONFIGURATION_INFORMATION12 - Excl rule for Supplemental Earning
362 -- Config14 - Excl rule for Overtime and Premium Categories
363 -- Config15 - Excl rule for formula for flat amount non recurring
364 -- Config16 - Excl rule for formula for flat amount recurring
365 -- Config17 - Excl rule for formula for percentage non-recurring
366 -- Config18 - Excl rule for formula for percentage recurring
367 -- Config19 -
368 -- Config20 -
369 -- Config21 - Excl rule for Stop Reach Rule
370 -- Config22 - Element type usages exlusion rule.
371 --dont enter anyting for supplemental earning element
372 -- Config23 - Processing type, recurring/non recur
373 -- Config24 - Extra Input Values for Augments
374
375 --Added for bug 5161974
376 lrec.configuration_information19 := 'N';
377
378
379 IF (lrec.configuration_info_category = 'REG') THEN
380 lrec.configuration_information11 := 'Y'; -- l_reg_earning_flag
381 END IF;
382
383 lrec.configuration_information14 := 'N'; -- Overtime or Premium Category
384 -- For Overtime or Premium Category
385 IF ((lrec.element_classification = 'Earnings') AND
386 ((lrec.configuration_info_category = 'OT') OR
387 (lrec.configuration_info_category = 'P')
388 )
389 ) THEN
390 lrec.configuration_information14 := 'Y';
391 END IF;
392
393 IF (lrec.element_classification = 'Supplemental Earnings') THEN
394 lrec.configuration_information12 := 'Y'; --l_supp_earn_flag
395 lrec.configuration_information22 := 'N'; --l_ele_type_usages
396 ----Added for bug 5161974
397 IF (lrec.configuration_info_category = 'CM') THEN --Tax category Commissions
398 lrec.configuration_information19 := 'Y';
399 ELSE
400 lrec.configuration_information19 := 'N';
401 END IF;
402 --End changes for bug 5161974
403 END IF;
404
405 IF (SUBSTR (lrec.preference_information1, 1, 11) = 'FLAT_AMOUNT') THEN
406 --p_ele_calc_ff_name
407 lrec.configuration_information2 := 'FLAT'; --l_config2_amt
408 -- This is not getting used anywhere so I have commented it
409 -- l_calc_type := 'FLAT_AMOUNT';
410 ELSIF (SUBSTR (lrec.preference_information1, 1, 26) =
411 'PERCENTAGE_OF_REG_EARNINGS'
412 ) THEN --p_ele_calc_ff_name
413 lrec.configuration_information2 := 'PCT'; --l_config3_perc
414 -- l_calc_type := 'PERCENTAGE';
415 END IF;
416
417 lrec.configuration_information1 := 'Y'; --l_si_flag
418
419 IF ( (lrec.processing_type = 'N'
420 -- AND p_termination_rule <> 'L'
421 )
422 OR lrec.preference_information14 = 'N' --p_special_input_flag
423 ) THEN
424 lrec.configuration_information1 := 'N'; --l_si_flag
425 END IF;
426
427 IF (lrec.preference_information3 = 'Y') THEN --p_special_feature_flag
428 lrec.configuration_information8 := 'Y'; --l_sf_flag
429 lrec.configuration_information9 := 'N'; --l_sf_iv_flag
430 ELSE
431 lrec.configuration_information8 := 'N'; --l_sf_flag
432 lrec.configuration_information9 := 'Y'; --l_sf_iv_flag
433 END IF;
434
435 lrec.configuration_information10 := 'N'; --l_se_iv_flag
436 lrec.configuration_information21 := 'N'; --l_stop_reach_flag
437
438 IF (lrec.preference_information2 = 'Y') THEN --p_student_earning
439 lrec.configuration_information8 := 'Y'; --l_sf_flag
440 lrec.configuration_information10 := 'Y'; --l_se_iv_flag
441 lrec.configuration_information9 := 'N'; --l_sf_iv_flag
442 lrec.configuration_information21 := 'Y'; --l_stop_reach_flag
443 -- l_multiple_entries :='N';
444 END IF;
445
446 IF (lrec.preference_information15 = 'Y') THEN --p_stop_reach_rule
447 lrec.configuration_information21 := 'Y'; --l_stop_reach_flag
448 END IF;
449
450 lrec.configuration_information15 := 'N'; --l_config2_NR_amt
451 lrec.configuration_information16 := 'N'; --l_config2_RSI_amt
452 lrec.configuration_information17 := 'N'; --l_config3_NR_perc
453 lrec.configuration_information18 := 'N'; --l_config3_RSI_perc
454
455 IF (lrec.configuration_information1 = 'Y') THEN --l_si_flag
456 IF (lrec.configuration_information2 = 'FLAT') THEN --l_config2_amt
457 lrec.configuration_information16 := 'Y'; --l_config2_RSI_amt
458 ELSIF (lrec.configuration_information2 = 'PCT') THEN --l_config3_perc
459 lrec.configuration_information18 := 'Y'; --l_config3_RSI_perc
460 END IF;
461 ELSE
462 IF (lrec.configuration_information2 = 'FLAT') THEN --l_config2_amt
463 lrec.configuration_information15 := 'Y'; --l_config2_NR_amt
464 ELSIF (lrec.configuration_information2 = 'PCT') THEN --l_config3_perc
465 lrec.configuration_information17 := 'Y'; --l_config3_NR_perc
466 END IF;
467 END IF;
468
469 lrec.configuration_information24 := 'N';
470 IF (SUBSTR (lrec.preference_information1, 1, 11) = 'FLAT_AMOUNT' AND
471 lrec.element_classification = 'Supplemental Earnings' AND
472 lrec.processing_type = 'N' AND
473 lrec.configuration_information7 = 'Y'
474 ) THEN
475 lrec.configuration_information24 := 'Y';
476 /*
477 For Augments configuration_information7 is made 'N' so that the
478 base element does not feed FLSA Earnings and FLSA Allocated Earnings
479 balances. Will change it back to Y in element_template_upd_user_stru
480 such that it reflects on the earnings form.
481 */
482 lrec.configuration_information7 := 'N';
483 END IF;
484
485
486 ELSE
487
488 -- FLSA Earning Elements
489 -- Exclusion Rules are
490 -- CONFIGURATION_INFORMATION1 => Special Feature Element
491 -- CONFIGURATION_INFORMATION2 => Student Earnings
492 -- CONFIGURATION_INFORMATION3 => Regular Category Check
493 -- CONFIGURATION_INFORMATION4 => STOP Reach rule
494 -- CONFIGURATION_INFORMATION5 => Reduce Regular Checkbox
495 -- CONFIGURATION_INFORMATION6 => FLSA Hours
496 -- CONFIGURATION_INFORMATION7 => Overtime Base
497 -- CONFIGURATION_INFORMATION8 => Processing Type
498 -- CONFIGURATION_INFORMATION9 => Supplemental Element Check
499 -- CONFIGURATION_INFORMATION10 => Special Input Element
500 -- CONFIGURATION_INFORMATION11 => Supplemental Element Check For SI
501 -- CONFIGURATION_INFORMATION12 => Hours * Rate Formula
502 -- CONFIGURATION_INFORMATION13 => Premium Formula
503 -- CONFIGURATION_INFORMATION14 => Overtime and Premium Categories
504 -- CONFIGURATION_INFORMATION15 => Regular Element
505
506 -- Initialize various Exclusion variables
507 -- CONFIGURATION_INFORMATION5 (Reduce Regular) and
508 -- CONFIGURATION_INFORMATION6 (FLSA Hours) and
509 -- CONFIGURATION_INFORMATION7 (Overtime Base)
510 -- CONFIGURATION_INFORMATION8 (Processing Tyep) are not initialized
511 -- as they are properly intialized in the call
512 lrec.configuration_information1 := 'N'; -- Special Feature Element
513 lrec.configuration_information2 := 'N'; -- Student Earnings
514 lrec.configuration_information3 := 'N'; -- Regular Category Check
515 lrec.configuration_information4 := 'N'; -- STOP Reach rule
516 lrec.configuration_information9 := 'NONSUPP'; -- Create Ele Type Usages
517 lrec.configuration_information10 := 'N'; -- Special Input Element
518 lrec.configuration_information11 := 'N'; -- Create Ele Type Usage for SI
519 lrec.configuration_information12 := 'N'; -- Hours * Rate Formula
520 lrec.configuration_information13 := 'N'; -- Premium Formula
521 lrec.configuration_information14 := 'N'; -- Overtime and Premium Category
522
523 -- If the element is Reduce Regular, then set configuration_information15
524 -- to 'N' so that Reduce Regular input values are not creted
525 -- Else set to 'Y' so that they are created for Regular Elements
526 if lrec.configuration_information5 = 'Y' then
527 lrec.configuration_information15 := 'N';
528 else
529 lrec.configuration_information15 := 'Y';
530 end if; -- if lrec.configuration_information5
531
532 -- Setting the Preference Information values
533 IF (lrec.element_classification = 'Earnings') THEN
534 lrec.preference_information11 := -249; -- SI Priority
535 lrec.preference_information9 := 250; -- SF Priority
536 lrec.preference_information7 := 'US_EARNINGS'; -- Ele Info Cat
537 lrec.preference_information12 := NULL; -- Skip Formula
538 lrec.configuration_information9 := 'NONSUPP'; -- Ele Type Usage
539 lrec.configuration_information11 := 'Y'; -- Ele Type For SI
540 ELSIF (lrec.element_classification = 'Supplemental Earnings') THEN
541 lrec.preference_information11 := -499; -- SI Priority
542 lrec.preference_information9 := 500; -- SF Priority
543 lrec.preference_information7 := 'US_SUPPLEMENTAL EARNINGS';
544 -- Ele Info Cat
545 lrec.preference_information12 := NULL; -- Skip Formula
546 lrec.configuration_information9 := 'SUPP'; -- No Ele Type Usage
547 lrec.configuration_information11 := 'N'; -- No Ele Type For SI
548 ELSIF (lrec.element_classification = 'Imputed Earnings') THEN
549 lrec.preference_information11 := -249; -- SI Priority
550 lrec.preference_information9 := 250; -- SF Priority
551 lrec.preference_information7 := 'US_IMPUTED EARNINGS';
552 -- Ele Info Cat
553 lrec.preference_information12 := NULL; -- Skip Formula
554 lrec.configuration_information9 := 'NONSUPP'; -- Ele Type Usage
555 lrec.configuration_information11 := 'N'; -- No Ele Type For SI
556 ELSIF (lrec.element_classification = 'Non-payroll Payments') THEN
557 lrec.preference_information11 := -249; -- SI Priority
558 lrec.preference_information9 := 250; -- SF Priority
559 lrec.preference_information7 := 'US_NON-PAYROLL PAYMENTS';
560 -- Ele Info Cat
561 lrec.preference_information12 := NULL; -- Skip Formula
562 lrec.configuration_information9 := 'SUPP'; -- Ele Type Usage
563 lrec.configuration_information11 := 'Y'; -- Ele Type For SI
564 END IF;
565
566 -- CONFIGURATION_INFORMATION1 is used for Special Features
567 IF (lrec.preference_information3 = 'Y') THEN
568 lrec.configuration_information1 := 'Y';
569 ELSE
570 lrec.configuration_information1 := 'N';
571 END IF; /* IF (lrec.preference_information3 = 'Y') */
572
573 -- CONFIGURATION_INFORMATION10 ==> Special Feature Element
574 -- CONFIGURATION_INFORMATION11 ==> Ele Type Usage for Special
575 -- Feature Element
576 IF (lrec.processing_type = 'N'
577 OR lrec.preference_information14 = 'N') THEN
578 lrec.configuration_information10 := 'N'; -- No SI Element
579 lrec.configuration_information11 := 'N'; -- No Ele Type For SI
580 ELSE
581 lrec.configuration_information10 := 'Y'; -- SI Element
582 END IF;
583
584 IF (lrec.preference_information2 = 'Y') THEN
585 -- Student Earnings
586 lrec.configuration_information1 := 'Y'; -- Special Fetures
587 lrec.configuration_information2 := 'Y'; -- Student Earnings
588 lrec.configuration_information4 := 'Y'; -- STOP Reach rule
589 lrec.configuration_information15:= 'N'; -- No Red Reg Input Values
590 lrec.configuration_information5 := 'N'; -- Exclude Red Reg Feeds
591 END IF; /* (lrec.preference_information2 = 'Y') */
592
593 IF (lrec.configuration_info_category = 'REG') THEN
594 -- Regular Earnings check to feed 401K balance
595 lrec.configuration_information3 := 'Y';
596 END IF; /* IF (lrec.configuration_info_category = 'REG') */
597
598 IF (lrec.preference_information15 = 'Y') THEN
599 -- Total Stop Reach Rule Checkbox
600 lrec.configuration_information4 := 'Y'; -- STOP Reach rule
601 END IF; /* IF (lrec.preference_information15 = 'Y') */
602
603 IF (SUBSTR (lrec.preference_information1, 1, 12) = 'HOURS_X_RATE') THEN
604 IF (lrec.configuration_information6 = 'Y' OR
605 lrec.configuration_information7 = 'Y' OR
606 lrec.configuration_information10 = 'N') THEN
607 lrec.configuration_information12 := 'Y'; -- Hour * Rate formula
608 -- No SI if any of the FLSA Hours or Overtime Base is checked
609 lrec.configuration_information10 := 'N';
610 END IF;
611 ELSIF (SUBSTR (lrec.preference_information1, 1, 7) = 'PREMIUM') THEN
612 lrec.configuration_information13 := 'Y'; -- Premium Formula
613 lrec.configuration_information10 := 'N'; -- No SI Element
614 lrec.configuration_information11 := 'N'; -- No Ele Type For SI
615 END IF; /* IF (SUBSTR (lrec.preference_information1... */
616
617 -- No Special Input Element
618 -- If Special Input element is created, then we should use
619 -- Hours * Rate formula with SI and not the one set above
620 IF (lrec.configuration_information10 = 'N') THEN
621 lrec.configuration_information11 := 'N'; -- No Ele Type Usage For SI
622 ELSE
623 lrec.configuration_information12 := 'N'; -- Hour * Rate formula
624 lrec.configuration_information13 := 'N'; -- Premium Formula
625 END IF;
626
627 -- For Overtime or Premium Category
628 IF ((lrec.element_classification = 'Earnings') AND
629 ((lrec.configuration_info_category = 'OT') OR
630 (lrec.configuration_info_category = 'P')
631 )
632 ) THEN
633 lrec.configuration_information14 := 'Y';
634 END IF;
635
636 hr_utility.trace('CONFIG1 = ' || lrec.configuration_information1);
637 hr_utility.trace('CONFIG2 = ' || lrec.configuration_information2);
638 hr_utility.trace('CONFIG3 = ' || lrec.configuration_information3);
639 hr_utility.trace('CONFIG4 = ' || lrec.configuration_information4);
640 hr_utility.trace('CONFIG5 = ' || lrec.configuration_information5);
641 hr_utility.trace('CONFIG6 = ' || lrec.configuration_information6);
642 hr_utility.trace('CONFIG7 = ' || lrec.configuration_information7);
643 hr_utility.trace('CONFIG8 = ' || lrec.configuration_information8);
644 hr_utility.trace('CONFIG9 = ' || lrec.configuration_information9);
645 hr_utility.trace('CONFIG10 = ' || lrec.configuration_information10);
646 hr_utility.trace('CONFIG11 = ' || lrec.configuration_information11);
647 hr_utility.trace('CONFIG12 = ' || lrec.configuration_information12);
648 hr_utility.trace('CONFIG13 = ' || lrec.configuration_information13);
649 hr_utility.trace('CONFIG14 = ' || lrec.configuration_information14);
650 hr_utility.trace('CONFIG15 = ' || lrec.configuration_information15);
651 hr_utility.trace('Priority = ' || lrec.processing_priority);
652
653 -- hr_utility.trace_off();
654
655 END IF; /* IF (lrec.calculation_rule = 'US Earnings') */
656
657 -----------------------------------------------------------------
658 -- Used in Update Base shadow Element with user-specified details
659 -----------------------------------------------------------------
660 IF lrec.processing_type = 'N' THEN
661 lrec.multiple_entries_allowed := 'Y';
662 END IF;
663 -----------------------------------------------------------------
664 -- Change the process mode to S for earnings element
665 -----------------------------------------------------------------
666
667 IF lrec.process_mode = 'N' THEN-- value sent as N for earnings
668 lrec.process_mode := 'S';
669 END IF;
670
671 hr_utility.TRACE ('Leaving pay_us_rules.element_template_pre_process');
672 RETURN lrec;
673 END element_template_pre_process;
674
675 /****************************************************************************/
676 /* PROCEDURE element_template_upd_user_stru */
677 /****************************************************************************/
678
679 PROCEDURE element_template_upd_user_stru (p_element_template_id IN NUMBER) IS
680 l_template_name VARCHAR2 (240);
681 l_element_type_id NUMBER;
682 l_ovn NUMBER;
683
684 --
685 -- cursor to fetch the new element type id
686 --
687 CURSOR c_element (p_ele_name VARCHAR2, p_template_id NUMBER) IS
688 SELECT element_type_id, object_version_number
689 FROM pay_shadow_element_types
690 WHERE element_name = p_ele_name AND template_id = p_template_id;
691
692 --
693 -- cursor to get the template id
694 --
695 CURSOR c_template (p_template_name VARCHAR2, p_legislation_code VARCHAR2) IS
696 SELECT template_id
697 FROM pay_element_templates
698 WHERE template_name = p_template_name
699 AND legislation_code = p_legislation_code;
700
701 lv_reduce_regular VARCHAR2(10);
702 lv_special_feat VARCHAR2(10);
703 lv_special_inp VARCHAR2(10);
704 lv_flsa_calc_name VARCHAR2(100);
705 lv_prem_adjust_name VARCHAR2(100);
706 ln_base_process_priority NUMBER(9);
707 ln_si_process_priority NUMBER(9);
708 ln_sf_process_priority NUMBER(9);
709 ln_fc_process_priority NUMBER(9);
710 ln_fpa_process_priority NUMBER(9);
711 BEGIN
712 hr_utility.TRACE ('Entering pay_us_rules.element_template_upd_user_stru');
713 hr_utility.TRACE ('p_element_template_id ' || p_element_template_id);
714
715 -----------------------------------------------------------
716 -- Update Base shadow Element with user-specified details
717 -----------------------------------------------------------
718 FOR c_rec IN c_element (lrec.element_name, p_element_template_id)
719 LOOP
720 l_element_type_id := c_rec.element_type_id;
721 l_ovn := c_rec.object_version_number;
722 END LOOP;
723
724 -- FLSA Changes
725 IF (lrec.calculation_rule = 'US ' || lrec.element_classification) THEN
726 lv_reduce_regular := lrec.configuration_information13; -- Reduce Regular
727 lv_special_feat := lrec.configuration_information8; -- Special Features
728 lv_special_inp := lrec.configuration_information1; -- Special Input
729 ELSE
730 lv_special_feat := lrec.configuration_information1; -- Special Features
731 lv_reduce_regular := lrec.configuration_information5; -- Reduce Regular
732 lv_special_inp := lrec.configuration_information10; -- Special Input
733 END IF;
734
735 /*For Augments configuration_information7 was made 'N' so that the
736 base element does not feed FLSA Earnings and FLSA Allocated Earnings
737 balances. Changin it back to Y such that it reflects on the earnings
738 form.
739 */
740 IF ((lrec.calculation_rule = 'US ' || lrec.element_classification) AND lrec.configuration_information24 = 'Y') THEN
741 lrec.configuration_information7 := 'Y';
742 END IF;
743
744 ----------------------------------------------------------------------------
745 -- Modify the Base elements priorty to 1526 if Reduce Regular is checked.
746 -- The template has the Base elements priority set to 1750. We need to
747 -- offset that by relative priority of -224 for Reduce Regular elements
748 ----------------------------------------------------------------------------
749 IF lv_reduce_regular = 'Y' THEN
750 ln_base_process_priority := 0;
751 ln_sf_process_priority := ln_base_process_priority - 249;
752 ln_si_process_priority := ln_base_process_priority + 250;
753 ln_fc_process_priority := 0;
754 ln_fpa_process_priority := ln_base_process_priority + 10;
755 ELSE
756 ln_base_process_priority := 0;
757 ln_sf_process_priority := lrec.preference_information9;
758 ln_si_process_priority := lrec.preference_information11;
759 ln_fc_process_priority := 0;
760 ln_fpa_process_priority := ln_base_process_priority + 10;
761 END IF;
762 -- FLSA Changes
763 pay_shadow_element_api.update_shadow_element
764 (p_validate => FALSE
765 ,p_effective_date => lrec.effective_date
766 ,p_element_type_id => l_element_type_id
767 ,p_element_name => lrec.element_name
768 ,p_skip_formula => lrec.preference_information12
769 ,p_element_information_category => lrec.preference_information7
770 -- p_ele_category
771 ,p_element_information1 => NVL(lrec.configuration_info_category,
772 hr_api.g_varchar2)
773 --p_ele_ot_base
774 ,p_element_information8 => NVL(lrec.configuration_information7,
775 hr_api.g_varchar2)
776 --p_flsa_hours
777 ,p_element_information11 => NVL(lrec.configuration_information6,
778 hr_api.g_varchar2)
779 --p_reduce_regular
780 ,p_element_information13 => NVL(lv_reduce_regular,
781 hr_api.g_varchar2)
782 --p_special_input_flag
783 ,p_element_information14 => NVL(lrec.preference_information14,
784 hr_api.g_varchar2)
785 --p_stop_reach_rule
786 ,p_element_information15 => NVL(lrec.preference_information15,
787 hr_api.g_varchar2)
788 ,p_relative_processing_priority => ln_base_process_priority
789 ,p_object_version_number => l_ovn
790 );
791
792 -------------------------------------------------------------------
793 -- Update user-specified details on Special Features Element.
794 -------------------------------------------------------------------
795 -- FLSA Changes
796 IF (lv_special_feat = 'Y') THEN --l_sf_flag
797 FOR c1_rec IN c_element (lrec.element_name || ' Special Features',
798 p_element_template_id
799 )
800 LOOP
801 l_element_type_id := c1_rec.element_type_id;
802 l_ovn := c1_rec.object_version_number;
803 pay_shadow_element_api.update_shadow_element
804 (p_validate => FALSE
805 ,p_reporting_name => lrec.reporting_name || ' SF'
806 ,p_classification_name => lrec.element_classification
807 ,p_effective_date => lrec.effective_date
808 ,p_element_type_id => l_element_type_id
809 ,p_description => 'Special Features element for '
810 || lrec.element_name
811 --l_sf_rel_priority
812 ,p_relative_processing_priority => ln_sf_process_priority
813 ,p_element_information_category => lrec.preference_information7
814 --p_ele_category
815 ,p_element_information1 => NVL(lrec.configuration_info_category,
816 hr_api.g_varchar2)
817 --p_ele_ot_base
818 ,p_element_information8 => NVL(lrec.configuration_information7,
819 hr_api.g_varchar2)
820 ,p_object_version_number => l_ovn
821 );
822 END LOOP;
823 END IF;
824
825 --------------------------------------------------------------------
826 -- Update user-specified Classification Special Inputs IF it exists.
827 --------------------------------------------------------------------
828 -- FLSA Changes
829 IF (lv_special_inp = 'Y') THEN --l_si_flag
830 FOR c1_rec IN c_element (lrec.element_name || ' Special Inputs',
831 p_element_template_id
832 )
833 LOOP
834 l_element_type_id := c1_rec.element_type_id;
835 l_ovn := c1_rec.object_version_number;
836 END LOOP;
837
838 pay_shadow_element_api.update_shadow_element
839 (p_validate => FALSE
840 ,p_reporting_name => lrec.reporting_name || ' SI'
841 ,p_classification_name => lrec.element_classification
842 ,p_effective_date => lrec.effective_date
843 ,p_element_type_id => l_element_type_id
844 ,p_description => 'Special Inputs element for '
845 || lrec.element_name
846 --l_si_rel_priority
847 ,p_relative_processing_priority => ln_si_process_priority
848 ,p_element_information_category => lrec.preference_information7
849 --p_ele_category
850 ,p_element_information1 => NVL(lrec.configuration_info_category,
851 hr_api.g_varchar2)
852 --p_ele_ot_base
853 ,p_element_information8 => NVL(lrec.configuration_information7,
854 hr_api.g_varchar2)
855 ,p_object_version_number => l_ovn
856 );
857 END IF;
858
859 -----------------------------------------------------------
860 -- Update user-specified details on FC element
861 -----------------------------------------------------------
862 IF ((lrec.calculation_rule = 'US ' || lrec.element_classification) AND lrec.configuration_information24 = 'Y') THEN
863 lv_flsa_calc_name := lrec.element_name || ' for FLSA Calc';
864 FOR c_rec IN c_element (lv_flsa_calc_name, p_element_template_id)
865 LOOP
866 l_element_type_id := c_rec.element_type_id;
867 l_ovn := c_rec.object_version_number;
868 END LOOP;
869 pay_shadow_element_api.update_shadow_element
870 (p_validate => FALSE
871 ,p_effective_date => lrec.effective_date
872 ,p_element_type_id => l_element_type_id
873 ,p_element_name => lv_flsa_calc_name
874 ,p_reporting_name => lrec.reporting_name || ' FC'
875 ,p_classification_name => 'Information'
876 ,p_description => 'FLSA Calc element for '
877 || lrec.element_name
878 ,p_skip_formula => lrec.preference_information12
879 ,p_element_information_category => 'US_INFORMATION'
880 -- p_ele_category
881 ,p_element_information1 => NULL
882 --p_ele_ot_base
883 ,p_element_information8 => NVL(lrec.configuration_information7,
884 hr_api.g_varchar2)
885 ,p_relative_processing_priority => ln_fc_process_priority
886 ,p_object_version_number => l_ovn
887 );
888 END IF;
889
890 -------------------------------------------------------------------------
891 -- Update user-specified details on ' for FLSA Period Adjustment' element
892 -------------------------------------------------------------------------
893 IF ((lrec.calculation_rule = 'US FLSA ' || lrec.element_classification) AND lrec.configuration_information13 = 'Y') THEN
894 lv_prem_adjust_name := lrec.element_name || ' for FLSA Period Adjustment';
895 FOR c_rec IN c_element (lv_prem_adjust_name, p_element_template_id)
896 LOOP
897 l_element_type_id := c_rec.element_type_id;
898 l_ovn := c_rec.object_version_number;
899 END LOOP;
900 pay_shadow_element_api.update_shadow_element
901 (p_validate => FALSE
902 ,p_effective_date => lrec.effective_date
903 ,p_element_type_id => l_element_type_id
904 ,p_element_name => lv_prem_adjust_name
905 ,p_reporting_name => lrec.reporting_name || ' Adjustment'
906 ,p_classification_name => lrec.element_classification
907 ,p_description => 'FLSA Period Adjust element for '
908 || lrec.element_name
909 ,p_skip_formula => lrec.preference_information12
910 ,p_element_information_category => lrec.preference_information7
911 -- p_ele_category
912 ,p_element_information1 => NVL(lrec.configuration_info_category,
913 hr_api.g_varchar2)
914 --p_ele_ot_base
915 ,p_element_information8 => NVL(lrec.configuration_information7,
916 hr_api.g_varchar2)
917 ,p_post_termination_rule => NVL(lrec.termination_rule,
918 hr_api.g_varchar2)
919 ,p_relative_processing_priority => ln_fpa_process_priority
920 ,p_object_version_number => l_ovn
921 );
922 END IF;
923
924 hr_utility.TRACE ('Leaving pay_us_rules.element_template_upd_user_stru');
925 END element_template_upd_user_stru;
926
927 /****************************************************************************/
928 /* PROCEDURE element_template_post_process */
929 /****************************************************************************/
930
931 PROCEDURE element_template_post_process (p_element_template_id IN NUMBER) IS
932 TYPE typeidnumber IS TABLE OF NUMBER
933 INDEX BY BINARY_INTEGER;
934
935 TYPE typeidchar IS TABLE OF VARCHAR2 (10)
936 INDEX BY BINARY_INTEGER;
937
938 TYPE tinputvalrec IS RECORD
939 (
940 vname pay_input_values_f.name%TYPE,
941 vresultname pay_formula_result_rules_f.result_name%TYPE,
942 vresultruletype pay_formula_result_rules_f.result_rule_type%TYPE,
943 vinputvalid pay_input_values_f.input_value_id%TYPE
944 );
945 TYPE tinputvalrectab IS TABLE OF tinputvalrec
946 INDEX BY BINARY_INTEGER;
947 tinputdetails tinputvalrectab;
948
949
950 i NUMBER;
951 t_form_id typeidnumber;
952 t_ipv_id typeidnumber;
953 t_def_val typeidchar;
954 t_we_flag typeidchar;
955 l_asg_gre_run_dim_id pay_balance_dimensions.balance_dimension_id%TYPE;
956 ln_business_group_id NUMBER;
957 lv_legislation_code VARCHAR2 (240);
958 lv_currency_code VARCHAR2 (240);
959 l_pri_bal_id NUMBER;
960 l_addl_bal_id NUMBER;
961 l_repl_bal_id NUMBER;
962 l_hours_bal_id NUMBER;
963 l_si_ele_type_id NUMBER;
964 l_sf_ele_type_id NUMBER;
965 l_hr_ele_id NUMBER;
966 l_hr_iv_id NUMBER;
967 l_stat_proc_rule_id NUMBER;
968 l_rr_id NUMBER;
969 lv_hoursXrate VARCHAR2(10);
970 lv_hoursXratemul VARCHAR2(10);
971 l_fc_bal_id NUMBER;
972 l_fc_ele_type_id NUMBER;
973 l_fc_stat_proc_rule_id NUMBER;
974 l_fc_eff_start_date DATE;
975 l_fc_eff_end_date DATE;
976 l_fc_obj_ver_num NUMBER;
977 l_for_mismatch_warn BOOLEAN;
978 l_fc_formula_name ff_formulas_f.formula_name%TYPE;
979 l_fc_formula_id ff_formulas_f.formula_id%TYPE;
980 l_fc_totamnt_iv_id NUMBER;
981 l_fc_formula_res_rul_id NUMBER;
982 ln_proration_group_name pay_event_groups.event_group_name%TYPE;
983 ln_proration_group_id pay_event_groups.event_group_id%TYPE;
984 ln_retro_comp_usge_id NUMBER;
985 ln_retro_comp_ovn NUMBER;
986 ln_retro_comp_id NUMBER;
987 ln_comp_name pay_retro_components.component_name%TYPE;
988 ln_retro_type pay_retro_components.retro_type%TYPE;
989 l_fpa_formula_name ff_formulas_f.formula_name%TYPE;
990 l_fpa_formula_id ff_formulas_f.formula_id%TYPE;
991 l_fpa_ele_type_id NUMBER;
992 l_fpa_stat_proc_rule_id NUMBER;
993 l_fpa_eff_start_date DATE;
994 l_fpa_eff_end_date DATE;
995 l_fpa_obj_ver_num NUMBER;
996 l_fpa_payval_iv_id NUMBER;
997 l_fpa_formula_res_rul_id NUMBER;
998 l_fpa_bal_id NUMBER;
999 l_fpa_hrs_bal_id NUMBER;
1000 l_fpa_req_id NUMBER;
1001
1002
1003
1004
1005
1006 CURSOR get_busgrp_info (cp_business_group_name VARCHAR2) IS
1007 SELECT business_group_id, legislation_code
1008 FROM per_business_groups
1009 WHERE NAME = cp_business_group_name;
1010
1011 CURSOR get_asg_gre_run_dim_id IS
1012 SELECT balance_dimension_id
1013 FROM pay_balance_dimensions
1014 WHERE dimension_name =
1015 'Assignment within Government Reporting Entity Run'
1016 AND legislation_code = 'US';
1017
1018 CURSOR c_ele (p_element_name IN VARCHAR2) IS
1019 SELECT element_type_id
1020 FROM pay_element_types_f
1021 WHERE UPPER (element_name) = UPPER (p_element_name)
1022 AND legislation_code = 'US';
1023
1024 CURSOR c_inp_val(p_input_val_name IN VARCHAR2, p_element_type_id IN NUMBER)
1025 IS
1026 SELECT input_value_id
1027 FROM pay_input_values_f
1028 WHERE element_type_id = p_element_type_id
1029 AND UPPER (NAME) = UPPER (p_input_val_name);
1030
1031 CURSOR c_pspr (
1032 p_element_type_id IN NUMBER,
1033 p_bg_id IN NUMBER,
1034 p_assgn_status_id IN NUMBER
1035 ) IS
1036 SELECT status_processing_rule_id
1037 FROM pay_status_processing_rules_f
1038 WHERE element_type_id = p_element_type_id
1039 AND business_group_id = p_bg_id;
1040
1041 CURSOR c_formula_id (
1042 p_formula_name IN VARCHAR2,
1043 p_legislation_code IN VARCHAR2
1044 ) IS
1045 SELECT formula_id
1046 FROM ff_formulas_f
1047 WHERE formula_name = p_formula_name
1048 AND legislation_code = p_legislation_code;
1049
1050 -- Get Formula Name
1051 CURSOR get_formula_name( l_element_type_id NUMBER,
1052 l_processing_rule VARCHAR2,
1053 l_business_group_id NUMBER
1054 ) IS
1055 SELECT FF.formula_name
1056 FROM pay_status_processing_rules_f PSP,
1057 ff_formulas_f FF
1058 WHERE PSP.element_type_id = l_element_type_id
1059 AND PSP.processing_rule = l_processing_rule
1060 AND FF.formula_id = PSP.formula_id
1061 AND PSP.business_group_id = l_business_group_id
1062 AND FF.business_group_id = l_business_group_id;
1063
1064
1065 -- Get Proration Group ID
1066 CURSOR get_proration_group_id( l_proration_group_name VARCHAR2
1067 ,l_bg_id NUMBER
1068 ,l_legislation_code VARCHAR2) IS
1069 SELECT event_group_id
1070 FROM pay_event_groups
1071 WHERE event_group_name = l_proration_group_name
1072 AND ((business_group_id IS NULL and legislation_code IS NULL) OR
1073 (business_group_id IS NULL and legislation_code = l_legislation_code) OR
1074 (business_group_id = l_bg_id and legislation_code IS NULL)
1075 );
1076
1077 -- Get Retro Component Id
1078 CURSOR get_retro_comp_id( l_comp_name VARCHAR2
1079 ,l_retro_type VARCHAR2
1080 ,l_legislation_code VARCHAR2
1081 ) IS
1082 SELECT retro_component_id
1083 FROM pay_retro_components
1084 WHERE component_name = l_comp_name
1085 AND retro_type = l_retro_type
1086 AND legislation_code = l_legislation_code;/*For seeded retro component, US
1087 legislation_code will be present
1088 */
1089
1090 BEGIN
1091 hr_utility.TRACE ('Entering pay_us_rules.element_template_post_process');
1092 hr_utility.TRACE ('p_element_template_id ' || p_element_template_id);
1093
1094 -- FLSA Changes
1095 IF (lrec.calculation_rule = 'US ' || lrec.element_classification) THEN
1096 lv_hoursXrate := 'N'; --lrec.configuration_information4;
1097 lv_hoursXratemul := 'N'; --lrec.configuration_information5;
1098 ELSE
1099 lv_hoursXrate := 'Y';
1100 lv_hoursXratemul := 'Y';
1101 END IF;
1102
1103 OPEN get_busgrp_info (lrec.business_group_name);
1104
1105 FETCH get_busgrp_info
1106 INTO ln_business_group_id, lv_legislation_code;
1107
1108 CLOSE get_busgrp_info;
1109
1110 -------------------------------------------------------------------
1111 -- Get Element and Balance Id's to update the Further Information
1112 -------------------------------------------------------------------
1113 l_pri_bal_id :=
1114 get_obj_id (ln_business_group_id,
1115 lv_legislation_code,
1116 'BAL',
1117 lrec.element_name
1118 );
1119 l_addl_bal_id :=
1120 get_obj_id (ln_business_group_id,
1121 lv_legislation_code,
1122 'BAL',
1123 lrec.element_name || ' Additional'
1124 );
1125 l_repl_bal_id :=
1126 get_obj_id (ln_business_group_id,
1127 lv_legislation_code,
1128 'BAL',
1129 lrec.element_name || ' Replacement'
1130 );
1131 l_hours_bal_id :=
1132 get_obj_id (ln_business_group_id,
1133 lv_legislation_code,
1134 'BAL',
1135 lrec.element_name || ' Hours'
1136 );
1137 pay_us_earn_templ_wrapper.g_ele_type_id :=
1138 get_obj_id (ln_business_group_id,
1139 lv_legislation_code,
1140 'ELE',
1141 lrec.element_name
1142 );
1143 l_si_ele_type_id :=
1144 get_obj_id (ln_business_group_id,
1145 lv_legislation_code,
1146 'ELE',
1147 lrec.element_name || ' Special Inputs'
1148 );
1149 l_sf_ele_type_id :=
1150 get_obj_id (ln_business_group_id,
1151 lv_legislation_code,
1152 'ELE',
1153 lrec.element_name || ' Special Features'
1154 );
1155
1156 UPDATE pay_element_types_f
1157 SET element_name = lrec.element_name,
1158 element_information10 = l_pri_bal_id,
1159 element_information12 = l_hours_bal_id
1160 WHERE element_type_id = pay_us_earn_templ_wrapper.g_ele_type_id
1161 AND business_group_id = ln_business_group_id;
1162
1163 -------------------------------------------------------------------
1164 -- Get Element and Balance Id to update the FLSA Calc Element
1165 -------------------------------------------------------------------
1166 IF ((lrec.calculation_rule = 'US ' || lrec.element_classification) AND lrec.configuration_information24 = 'Y') THEN
1167 /*
1168 l_fc_bal_id :=
1169 get_obj_id (ln_business_group_id,
1170 lv_legislation_code,
1171 'BAL',
1172 lrec.element_name || ' for FLSA Calc'
1173 );
1174 */
1175 l_fc_ele_type_id :=
1176 get_obj_id (ln_business_group_id,
1177 lv_legislation_code,
1178 'ELE',
1179 lrec.element_name || ' for FLSA Calc'
1180 );
1181 /*
1182 UPDATE pay_element_types_f
1183 SET element_information10 = l_fc_bal_id
1184 WHERE element_type_id = l_fc_ele_type_id
1185 AND business_group_id = ln_business_group_id;
1186 */
1187 END IF;
1188
1189 -------------------------------------------------------------------
1190 -- Get Element Type Id of ' for Premium Re Calc'
1191 -------------------------------------------------------------------
1192 IF ((lrec.calculation_rule = 'US FLSA ' || lrec.element_classification) AND lrec.configuration_information13 = 'Y') THEN
1193
1194 l_fpa_ele_type_id :=
1195 get_obj_id (ln_business_group_id,
1196 lv_legislation_code,
1197 'ELE',
1198 lrec.element_name || ' for FLSA Period Adjustment'
1199 );
1200
1201 l_fpa_bal_id :=
1202 get_obj_id (ln_business_group_id,
1203 lv_legislation_code,
1204 'BAL',
1205 lrec.element_name || ' for FLSA Period Adjustment'
1206 );
1207
1208 l_fpa_hrs_bal_id :=
1209 get_obj_id (ln_business_group_id,
1210 lv_legislation_code,
1211 'BAL',
1212 lrec.element_name || ' for FLSA Period Adjustment Hours'
1213 );
1214 /* Attach a proration group and event
1215 with FLSA Period Adjustment Element */
1216
1217 ln_proration_group_name := 'Entry Changes for Proration';
1218
1219 OPEN get_proration_group_id(ln_proration_group_name,NULL,NULL);
1220 FETCH get_proration_group_id INTO ln_proration_group_id;
1221 CLOSE get_proration_group_id;
1222
1223 UPDATE pay_element_types_f
1224 SET element_information10 = l_fpa_bal_id,
1225 element_information12 = l_fpa_hrs_bal_id,
1226 proration_group_id = ln_proration_group_id
1227 WHERE element_type_id = l_fpa_ele_type_id
1228 AND business_group_id = ln_business_group_id;
1229
1230 END IF;
1231
1232 -------------------------------------------------------------------
1233 -- Update Input values with default values, validation formula etc.
1234 -------------------------------------------------------------------
1235 t_ipv_id (1) :=
1236 get_obj_id (ln_business_group_id,
1237 lv_legislation_code,
1238 'IPV',
1239 'Deduction Processing',
1240 pay_us_earn_templ_wrapper.g_ele_type_id
1241 );
1242 t_form_id (1) := NULL;
1243 t_we_flag (1) := NULL;
1244 t_def_val (1) := lrec.preference_information6;
1245 t_ipv_id (2) :=
1246 get_obj_id (ln_business_group_id,
1247 lv_legislation_code,
1248 'IPV',
1249 'Separate Check',
1250 pay_us_earn_templ_wrapper.g_ele_type_id
1251 );
1252 t_form_id (2) := NULL;
1253 t_we_flag (2) := NULL;
1254 t_def_val (2) := lrec.preference_information8;
1255
1256 --
1257 FOR i IN 1 .. 2
1258 LOOP
1259 UPDATE pay_input_values_f
1260 SET formula_id = t_form_id (i),
1261 warning_or_error = t_we_flag (i),
1262 DEFAULT_VALUE = t_def_val (i)
1263 WHERE input_value_id = t_ipv_id (i);
1264
1265 -- Bug 4420211
1266 -- Set the Mandatory Flag to 'N' for input value 'Deduction Processing'
1267 -- if the classification is 'Non-payroll Payments'
1268 IF (lrec.element_classification = 'Non-payroll Payments' and i = 1) THEN
1269 UPDATE pay_input_values_f
1270 SET mandatory_flag = 'N'
1271 WHERE input_value_id = t_ipv_id (i);
1272 END IF;
1273 END LOOP;
1274
1275 ------------------------------------
1276 -- Get the _ASG_GRE_RUN dimension id
1277 ------------------------------------
1278 FOR crec IN get_asg_gre_run_dim_id
1279 LOOP
1280 l_asg_gre_run_dim_id := crec.balance_dimension_id;
1281 END LOOP;
1282
1283 --
1284 FOR c_rec IN c_ele ('Hours by Rate')
1285 LOOP
1286 l_hr_ele_id := c_rec.element_type_id;
1287 END LOOP;
1288
1289 FOR c_rec IN c_inp_val ('Element Type Id', l_hr_ele_id)
1290 LOOP
1291 l_hr_iv_id := c_rec.input_value_id;
1292 END LOOP;
1293
1294 -- FLSA Changes
1295 IF (lv_hoursXrate = 'Y') THEN --l_config4_hr
1296 FOR c_rec IN
1297 c_pspr
1298 (p_element_type_id => pay_us_earn_templ_wrapper.g_ele_type_id,
1299 p_bg_id => ln_business_group_id,
1300 p_assgn_status_id => NULL
1301 )
1302 LOOP
1303 l_stat_proc_rule_id := c_rec.status_processing_rule_id;
1304 END LOOP;
1305
1306 l_rr_id :=
1307 pay_formula_results.ins_form_res_rule
1308 (p_business_group_id => ln_business_group_id,
1309 p_legislation_code => NULL,
1310 p_effective_start_date => lrec.effective_date,
1311 p_effective_end_date => NULL,
1312 p_status_processing_rule_id => l_stat_proc_rule_id,
1313 p_element_type_id => l_hr_ele_id,
1314 p_input_value_id => l_hr_iv_id,
1315 p_result_name => 'ELEMENT_TYPE_ID_PASSED',
1316 p_result_rule_type => 'I',
1317 p_severity_level => NULL
1318 );
1319
1320 FOR c_rec IN c_inp_val ('Hours', l_hr_ele_id)
1321 LOOP
1322 l_hr_iv_id := c_rec.input_value_id;
1323 END LOOP;
1324
1325 l_rr_id :=
1326 pay_formula_results.ins_form_res_rule
1327 (p_business_group_id => ln_business_group_id,
1328 p_legislation_code => NULL,
1329 p_effective_start_date => lrec.effective_date,
1330 p_effective_end_date => NULL,
1331 p_status_processing_rule_id => l_stat_proc_rule_id,
1332 p_element_type_id => l_hr_ele_id,
1333 p_input_value_id => l_hr_iv_id,
1334 p_result_name => 'HOURS_PASSED',
1335 p_result_rule_type => 'I',
1336 p_severity_level => NULL
1337 );
1338
1339 -- FLSA Changes
1340 IF (lv_hoursXratemul = 'Y') THEN --l_config5_hrm
1341 FOR c_rec IN c_inp_val ('Multiple', l_hr_ele_id)
1342 LOOP
1343 l_hr_iv_id := c_rec.input_value_id;
1344 END LOOP;
1345
1346 l_rr_id :=
1347 pay_formula_results.ins_form_res_rule
1348 (p_business_group_id => ln_business_group_id,
1349 p_legislation_code => NULL,
1350 p_effective_start_date => lrec.effective_date,
1351 p_effective_end_date => NULL,
1352 p_status_processing_rule_id => l_stat_proc_rule_id,
1353 p_element_type_id => l_hr_ele_id,
1354 p_input_value_id => l_hr_iv_id,
1355 p_result_name => 'MULTIPLE_PASSED',
1356 p_result_rule_type => 'I',
1357 p_severity_level => NULL
1358 );
1359 END IF;
1360
1361 FOR c_rec IN c_inp_val ('Rate', l_hr_ele_id)
1362 LOOP
1363 l_hr_iv_id := c_rec.input_value_id;
1364 END LOOP;
1365
1366 l_rr_id :=
1367 pay_formula_results.ins_form_res_rule
1368 (p_business_group_id => ln_business_group_id,
1369 p_legislation_code => NULL,
1370 p_effective_start_date => lrec.effective_date,
1371 p_effective_end_date => NULL,
1372 p_status_processing_rule_id => l_stat_proc_rule_id,
1373 p_element_type_id => l_hr_ele_id,
1374 p_input_value_id => l_hr_iv_id,
1375 p_result_name => 'RATE_PASSED',
1376 p_result_rule_type => 'I',
1377 p_severity_level => NULL
1378 );
1379
1380 FOR c_rec IN c_inp_val ('Pay Value', l_hr_ele_id)
1381 LOOP
1382 l_hr_iv_id := c_rec.input_value_id;
1383 END LOOP;
1384
1385 l_rr_id :=
1386 pay_formula_results.ins_form_res_rule
1387 (p_business_group_id => ln_business_group_id,
1388 p_legislation_code => NULL,
1389 p_effective_start_date => lrec.effective_date,
1390 p_effective_end_date => NULL,
1391 p_status_processing_rule_id => l_stat_proc_rule_id,
1392 p_element_type_id => l_hr_ele_id,
1393 p_input_value_id => l_hr_iv_id,
1394 p_result_name => 'TEMPLATE_EARNING',
1395 p_result_rule_type => 'I',
1396 p_severity_level => NULL
1397 );
1398
1399 END IF;
1400 -------------------------------------------------------------------
1401 -- Update status_processing_rules for FLSA Calc Element
1402 -- Add formula result rule for FLSA Calc Element
1403 -- Add Entry Changes for Proration event group to FLSA Calc Element
1404 -------------------------------------------------------------------
1405 IF ((lrec.calculation_rule = 'US ' || lrec.element_classification) AND lrec.configuration_information24 = 'Y') THEN
1406
1407 /*Updating status_processing_rules*/
1408 l_fc_formula_name := 'FLSA_PREMIUM_AMOUNT_CALCULATION';
1409
1410 OPEN c_formula_id (l_fc_formula_name,'US');
1411 FETCH c_formula_id INTO l_fc_formula_id;
1412
1413 IF c_formula_id%FOUND THEN
1414 pay_status_processing_rule_api.create_status_process_rule
1415 (
1416 p_validate => FALSE
1417 ,p_effective_date => lrec.effective_date
1418 ,p_element_type_id => l_fc_ele_type_id
1419 ,p_business_group_id => ln_business_group_id
1420 ,p_legislation_code => NULL
1421 ,p_formula_id => l_fc_formula_id
1422 ,p_status_processing_rule_id => l_fc_stat_proc_rule_id
1423 ,p_effective_start_date => l_fc_eff_start_date
1424 ,p_effective_end_date => l_fc_eff_end_date
1425 ,p_object_version_number => l_fc_obj_ver_num
1426 ,p_formula_mismatch_warning => l_for_mismatch_warn
1427 );
1428
1429 FOR c_rec IN c_inp_val ('Total Amount', l_fc_ele_type_id)
1430 LOOP
1431 l_fc_totamnt_iv_id := c_rec.input_value_id;
1432 END LOOP;
1433
1434 pay_formula_result_rule_api.create_formula_result_rule
1435 (
1436 p_validate => FALSE
1437 ,p_effective_date => lrec.effective_date
1438 ,p_status_processing_rule_id => l_fc_stat_proc_rule_id
1439 ,p_result_name => 'TEMPLATE_EARNING'
1440 ,p_result_rule_type => 'D'
1441 ,p_business_group_id => ln_business_group_id
1442 ,p_legislation_code => NULL
1443 ,p_element_type_id => l_fc_ele_type_id
1444 ,p_severity_level => NULL
1445 ,p_input_value_id => l_fc_totamnt_iv_id
1446 ,p_formula_result_rule_id => l_fc_formula_res_rul_id
1447 ,p_effective_start_date => l_fc_eff_start_date
1448 ,p_effective_end_date => l_fc_eff_end_date
1449 ,p_object_version_number => l_fc_obj_ver_num
1450 );
1451 ELSE
1452 hr_utility.TRACE ('Error in pay_us_rules.element_template_post_process');
1453 hr_utility.TRACE ('Error in fetching formula id for FLSA_PREMIUM_AMOUNT_CALCULATION');
1454 END IF;/*End of c_formula_id%FOUND*/
1455
1456 CLOSE c_formula_id;
1457
1458 ln_proration_group_name := 'Entry Changes for Proration';
1459 OPEN get_proration_group_id(ln_proration_group_name,NULL,NULL);
1460 FETCH get_proration_group_id INTO ln_proration_group_id;
1461 IF get_proration_group_id%FOUND THEN
1462 /*Updating for FLSA Calc Element with proration group id*/
1463 UPDATE pay_element_types_f
1464 SET proration_group_id = ln_proration_group_id
1465 WHERE business_group_id = ln_business_group_id
1466 AND element_type_id = l_fc_ele_type_id;
1467 ELSE
1468 hr_utility.TRACE ('Error in pay_us_rules.element_template_post_process');
1469 hr_utility.TRACE ('Error in fetching proration group id for Entry Changes for Proration');
1470 END IF;
1471 CLOSE get_proration_group_id;
1472
1473
1474 ln_proration_group_name := 'Entry Changes';
1475 OPEN get_proration_group_id(ln_proration_group_name,NULL,NULL);
1476 FETCH get_proration_group_id INTO ln_proration_group_id;
1477 IF get_proration_group_id%FOUND THEN
1478 /*Updating for FLSA Calc Element with retro group id*/
1479 UPDATE pay_element_types_f
1480 SET recalc_event_group_id = ln_proration_group_id
1481 WHERE business_group_id = ln_business_group_id
1482 AND element_type_id = l_fc_ele_type_id;
1483
1484 /*Adding default retro component*/
1485 ln_comp_name := 'Retropay';
1486 ln_retro_type := 'F';
1487 OPEN get_retro_comp_id(ln_comp_name,ln_retro_type,'US');
1488 FETCH get_retro_comp_id INTO ln_retro_comp_id;
1489 IF get_retro_comp_id%FOUND THEN
1490 pay_rcu_ins.ins
1491 (p_effective_date => lrec.effective_date
1492 ,p_retro_component_id => ln_retro_comp_id
1493 ,p_creator_id => l_fc_ele_type_id
1494 ,p_creator_type => 'ET'
1495 ,p_default_component => 'Y'
1496 ,p_reprocess_type => 'R'
1497 ,p_business_group_id => ln_business_group_id
1498 ,p_legislation_code => NULL
1499 ,p_retro_component_usage_id => ln_retro_comp_usge_id
1500 ,p_object_version_number => ln_retro_comp_ovn
1501 ,p_replace_run_flag => 'N'
1502 ,p_use_override_dates => 'N'
1503 );
1504 ELSE
1505 hr_utility.TRACE ('Error in pay_us_rules.element_template_post_process');
1506 hr_utility.TRACE ('Error in fetching retro component id');
1507 END IF;
1508 CLOSE get_retro_comp_id;
1509 ELSE
1510 hr_utility.TRACE ('Error in pay_us_rules.element_template_post_process');
1511 hr_utility.TRACE ('Error in fetching retro group id for Entry Changes');
1512 END IF;
1513 CLOSE get_proration_group_id;
1514
1515
1516
1517 END IF;/* End of lrec.calculation_rule = 'US Earnings' AND lrec.configuration_information24 = 'Y'*/
1518
1519 -------------------------------------------------------------------
1520 -- Update status_processing_rules for 'for Premium Re Calc' Element
1521 -- Add formula result rule for 'for Premium Re Calc' Element
1522 -------------------------------------------------------------------
1523 IF ((lrec.calculation_rule = 'US FLSA ' || lrec.element_classification) AND lrec.configuration_information13 = 'Y') THEN
1524
1525
1526 FOR c_rec IN
1527 c_pspr
1528 (p_element_type_id => l_fpa_ele_type_id,
1529 p_bg_id => ln_business_group_id,
1530 p_assgn_status_id => NULL
1531 )
1532 LOOP
1533 l_fpa_stat_proc_rule_id := c_rec.status_processing_rule_id;
1534 END LOOP;
1535
1536 FOR c_rec IN c_ele ('Hours by Rate')
1537 LOOP
1538 l_hr_ele_id := c_rec.element_type_id;
1539 END LOOP;
1540
1541
1542 i := 1;
1543 tinputdetails(i).vname := 'Element Type Id';
1544 tinputdetails(i).vresultname := 'ELEMENT_TYPE_ID_PASSED';
1545 tinputdetails(i).vresultruletype := 'I';
1546
1547
1548 i := i + 1;
1549 tinputdetails(i).vname := 'Hours';
1550 tinputdetails(i).vresultname := 'HOURS_PASSED';
1551 tinputdetails(i).vresultruletype := 'I';
1552
1553 i := i + 1;
1554 tinputdetails(i).vname := 'Rate';
1555 tinputdetails(i).vresultname := 'RATE_PASSED';
1556 tinputdetails(i).vresultruletype := 'I';
1557
1558 i := i + 1;
1559 tinputdetails(i).vname := 'Multiple';
1560 tinputdetails(i).vresultname := 'MULTIPLE_PASSED';
1561 tinputdetails(i).vresultruletype := 'I';
1562
1563 i := i + 1;
1564 tinputdetails(i).vname := 'Pay Value';
1565 tinputdetails(i).vresultname := 'TEMPLATE_EARNINGS';
1566 tinputdetails(i).vresultruletype := 'I';
1567
1568 --
1569 FOR x IN tinputdetails.FIRST .. tinputdetails.LAST
1570 LOOP
1571 FOR c_rec IN c_inp_val (tinputdetails(x).vname, l_hr_ele_id)
1572 LOOP
1573 tinputdetails(x).vinputvalid := c_rec.input_value_id;
1574 END LOOP;
1575
1576 l_rr_id :=
1577 pay_formula_results.ins_form_res_rule
1578 (p_business_group_id => ln_business_group_id,
1579 p_legislation_code => NULL,
1580 p_effective_start_date => lrec.effective_date,
1581 p_effective_end_date => NULL,
1582 p_status_processing_rule_id => l_fpa_stat_proc_rule_id,
1583 p_element_type_id => l_hr_ele_id,
1584 p_input_value_id => tinputdetails(x).vinputvalid,
1585 p_result_name => tinputdetails(x).vresultname,
1586 p_result_rule_type => tinputdetails(x).vresultruletype,
1587 p_severity_level => NULL
1588 );
1589
1590 END LOOP;
1591
1592 END IF;/* End of lrec.calculation_rule = 'US FLSA' AND lrec.configuration_information13 = 'Y'*/
1593
1594 -- FLSA Changes
1595 -- Modifying the TIME_DEFINTION_TYPE for FLSA elements to 'G'
1596 -- if the elements are created using FLSA template or the
1597 -- element has FLSA Earnings checked
1598 hr_utility.trace('Calc Rule = ' || lrec.calculation_rule);
1599 hr_utility.trace('Ele Class = ' || lrec.element_classification);
1600 hr_utility.trace('CONFIG10 = ' || lrec.configuration_information10);
1601 IF (lrec.calculation_rule = 'US FLSA ' || lrec.element_classification) THEN
1602 IF (lrec.configuration_information10 = 'N') then
1603 hr_utility.trace('1. Updating Time Definition Type to G');
1604 UPDATE pay_element_types_f
1605 SET time_definition_type = 'G'
1606 WHERE business_group_id = ln_business_group_id
1607 AND element_type_id in ( pay_us_earn_templ_wrapper.g_ele_type_id
1608 ,l_fpa_ele_type_id);
1609 END IF;
1610 ELSIF lrec.configuration_information7 = 'Y' THEN
1611 hr_utility.trace('2. Updating Time Definition Type to G');
1612 UPDATE pay_element_types_f
1613 SET time_definition_type = 'G'
1614 WHERE business_group_id = ln_business_group_id
1615 AND element_type_id = pay_us_earn_templ_wrapper.g_ele_type_id;
1616 END IF;
1617
1618 -- Modifying the TIME_DEFINTION_TYPE for Augment elements to 'G'
1619 IF ((lrec.calculation_rule = 'US ' || lrec.element_classification) AND lrec.configuration_information24 = 'Y') THEN
1620 hr_utility.trace('2. Updating Time Definition Type to G');
1621 UPDATE pay_element_types_f
1622 SET time_definition_type = 'G'
1623 WHERE business_group_id = ln_business_group_id
1624 AND element_type_id = l_fc_ele_type_id;
1625 END IF;
1626
1627
1628 hr_utility.TRACE ('Leaving pay_us_rules.element_template_post_process');
1629 END element_template_post_process;
1630
1631 /****************************************************************************/
1632 /* PROCEDURE delete_pre_process */
1633 /****************************************************************************/
1634
1635 PROCEDURE delete_pre_process(p_element_template_id IN NUMBER) IS
1636
1637 i NUMBER;
1638 lv_ele_name pay_element_types_f.element_name%TYPE;
1639 lv_business_group_id NUMBER;
1640 lv_legislation_code VARCHAR2 (240);
1641 l_shadow_ele_type_id NUMBER;
1642 l_spr_id NUMBER;
1643 l_spr_obj_ver_num NUMBER;
1644 l_spr_eff_start_date DATE;
1645 l_frr_id NUMBER;
1646 l_frr_obj_ver_num NUMBER;
1647 l_frr_eff_start_date DATE;
1648 l_eff_start_date DATE;
1649 l_eff_end_date DATE;
1650
1651 TYPE ushadowrec IS RECORD
1652 (
1653 v_shadow_ele_name pay_element_types_f.element_name%TYPE
1654 );
1655
1656 TYPE ushadowrectab IS TABLE OF ushadowrec
1657 INDEX BY BINARY_INTEGER;
1658 tshadoweledetails ushadowrectab;
1659
1660
1661 CURSOR c_ele_name (p_element_type_id IN NUMBER) IS
1662 SELECT element_name
1663 , business_group_id
1664 , legislation_code
1665 FROM pay_element_types_f
1666 WHERE element_type_id = p_element_type_id;
1667
1668 CURSOR c_stat_proc_rule ( p_element_type_id IN NUMBER
1669 ,p_business_group_id IN NUMBER
1670 ,p_legislation_code IN NUMBER
1671 ) IS
1672 SELECT status_processing_rule_id
1673 , effective_start_date
1674 , object_version_number
1675 FROM pay_status_processing_rules_f
1676 WHERE element_type_id = p_element_type_id
1677 AND ((business_group_id = p_business_group_id AND
1678 legislation_code IS NULL) OR
1679 (business_group_id IS NULL AND
1680 legislation_code = p_legislation_code)
1681 );
1682
1683 CURSOR c_for_res_rule ( p_status_processing_rule_id IN NUMBER
1684 ,p_business_group_id IN NUMBER
1685 ,p_legislation_code IN NUMBER
1686 ) IS
1687 SELECT formula_result_rule_id
1688 , effective_start_date
1689 , object_version_number
1690 FROM pay_formula_result_rules_f
1691 WHERE status_processing_rule_id = p_status_processing_rule_id
1692 AND ((business_group_id = p_business_group_id AND
1693 legislation_code IS NULL) OR
1694 (business_group_id IS NULL AND
1695 legislation_code = p_legislation_code)
1696 );
1697
1698
1699 BEGIN
1700
1701 hr_utility.TRACE ('Entering pay_us_rules.delete_pre_process');
1702
1703 OPEN c_ele_name(pay_us_earn_templ_wrapper.g_ele_type_id);
1704 FETCH c_ele_name INTO lv_ele_name
1705 , lv_business_group_id
1706 , lv_legislation_code;
1707
1708 IF c_ele_name%FOUND THEN
1709 i := 1;
1710 tshadoweledetails (i).v_shadow_ele_name := lv_ele_name || ' for FLSA Calc';
1711
1712 i := i + 1;
1713 tshadoweledetails (i).v_shadow_ele_name := lv_ele_name || ' for FLSA Period Adjustment';
1714
1715 FOR x IN tshadoweledetails.FIRST .. tshadoweledetails.LAST
1716 --
1717 LOOP
1718 l_shadow_ele_type_id :=
1719 get_obj_id (lv_business_group_id,
1720 lv_legislation_code,
1721 'ELE',
1722 tshadoweledetails (x).v_shadow_ele_name
1723 );
1724 IF l_shadow_ele_type_id IS NOT NULL THEN
1725
1726 OPEN c_stat_proc_rule(l_shadow_ele_type_id
1727 ,lv_business_group_id
1728 ,lv_legislation_code
1729 );
1730 FETCH c_stat_proc_rule INTO l_spr_id
1731 ,l_spr_eff_start_date
1732 ,l_spr_obj_ver_num;
1733 IF c_stat_proc_rule%FOUND THEN
1734
1735 OPEN c_for_res_rule(l_spr_id
1736 ,lv_business_group_id
1737 ,lv_legislation_code
1738 );
1739 FETCH c_for_res_rule INTO l_frr_id
1740 ,l_frr_eff_start_date
1741 ,l_frr_obj_ver_num;
1742 IF c_for_res_rule%FOUND THEN
1743 NULL;
1744 pay_formula_result_rule_api.DELETE_FORMULA_RESULT_RULE
1745 (p_validate => FALSE
1746 ,p_effective_date => l_frr_eff_start_date
1747 ,p_datetrack_delete_mode => 'ZAP'
1748 ,p_formula_result_rule_id => l_frr_id
1749 ,p_object_version_number => l_frr_obj_ver_num
1750 ,p_effective_start_date => l_eff_start_date
1751 ,p_effective_end_date => l_eff_end_date
1752 );
1753 ELSE
1754 hr_utility.TRACE ('pay_formula_result_rules_f does not return any row');
1755 END IF;/*c_for_res_rule%FOUND*/
1756 CLOSE c_for_res_rule;
1757
1758 pay_status_processing_rule_api.delete_status_process_rule
1759 (p_validate => FALSE
1760 ,p_effective_date => l_spr_eff_start_date
1761 ,p_datetrack_mode => 'ZAP'
1762 ,p_status_processing_rule_id => l_spr_id
1763 ,p_object_version_number => l_spr_obj_ver_num
1764 ,p_effective_start_date => l_eff_start_date
1765 ,p_effective_end_date => l_eff_end_date
1766 );
1767 ELSE
1768 hr_utility.TRACE ('pay_status_processing_rules_f does not return any row');
1769 END IF;/*c_stat_proc_rule%FOUND*/
1770 CLOSE c_stat_proc_rule;
1771
1772 END IF;/*l_shadow_ele_type_id IS NOT NULL*/
1773 END LOOP;/*tshadoweledetails.FIRST .. tshadoweledetails.LAST*/
1774
1775 ELSE
1776 hr_utility.TRACE ('Error in pay_us_rules.delete_pre_process');
1777 hr_utility.TRACE ('Element Type Id passed does not have a row in pay_element_types_f');
1778 END IF;/*c_ele_name%FOUND*/
1779 CLOSE c_ele_name;
1780
1781 hr_utility.TRACE ('Leaving pay_us_rules.delete_pre_process');
1782 END delete_pre_process;
1783
1784 /****************************************************************************/
1785 /* PROCEDURE delete_post_process */
1786 /****************************************************************************/
1787
1788 PROCEDURE delete_post_process(p_element_template_id IN NUMBER) IS
1789 BEGIN
1790 hr_utility.TRACE ('Entering pay_us_rules.delete_post_process');
1791 NULL;
1792 hr_utility.TRACE ('Leaving pay_us_rules.delete_post_process');
1793 END delete_post_process;
1794
1795
1796 --=======================================================================
1797 -- FUNCTION GET_OBJ_ID
1798 --=======================================================================
1799
1800 FUNCTION get_obj_id (
1801 p_business_group_id IN NUMBER,
1802 p_legislation_code IN VARCHAR2,
1803 p_object_type IN VARCHAR2,
1804 p_object_name IN VARCHAR2,
1805 p_object_id IN NUMBER DEFAULT NULL
1806 )
1807 RETURN NUMBER IS
1808 --
1809 l_object_id NUMBER := NULL;
1810 l_proc VARCHAR2 (60);
1811
1812 --
1813 CURSOR c_element IS -- Gets the element type id
1814 SELECT element_type_id
1815 FROM pay_element_types_f
1816 WHERE element_name = p_object_name
1817 AND business_group_id = p_business_group_id;
1818
1819 --
1820 CURSOR c_get_ipv_id IS -- Gets the input value id
1821 SELECT piv.input_value_id
1822 FROM pay_input_values_f piv
1823 WHERE piv.NAME = p_object_name
1824 AND piv.element_type_id = p_object_id
1825 AND piv.business_group_id = p_business_group_id;
1826
1827 --
1828 CURSOR c_get_bal_id IS -- Gets the Balance type id
1829 SELECT balance_type_id
1830 FROM pay_balance_types pbt
1831 WHERE pbt.balance_name = p_object_name
1832 AND NVL (pbt.business_group_id, p_business_group_id) =
1833 p_business_group_id
1834 AND NVL (pbt.legislation_code, p_legislation_code) = p_legislation_code;
1835 --
1836 BEGIN
1837 hr_utility.set_location ('Entering: ' || l_proc, 10);
1838 l_proc := 'pay_us_earnings_template.get_obj_id';
1839 --
1840 IF p_object_type = 'ELE' THEN
1841 FOR c_rec IN c_element
1842 LOOP
1843 l_object_id := c_rec.element_type_id; -- element id
1844 END LOOP;
1845 ELSIF p_object_type = 'BAL' THEN
1846 FOR c_rec IN c_get_bal_id
1847 LOOP
1848 l_object_id := c_rec.balance_type_id; -- balance id
1849 END LOOP;
1850 ELSIF p_object_type = 'IPV' THEN
1851 FOR c_rec IN c_get_ipv_id
1852 LOOP
1853 l_object_id := c_rec.input_value_id; -- input value id
1854 END LOOP;
1855 END IF;
1856
1857 hr_utility.set_location ('Leaving: ' || l_proc, 50);
1858 --
1859 RETURN l_object_id;
1860 END get_obj_id;
1861 --
1862 --
1863 FUNCTION work_schedule_total_hours(
1864 assignment_action_id IN number --Context
1865 ,assignment_id IN number --Context
1866 ,p_bg_id IN NUMBER -- Context
1867 ,element_entry_id IN number --Context
1868 ,date_earned IN DATE --Context
1869 ,p_range_start IN DATE
1870 ,p_range_end IN DATE)
1871 RETURN NUMBER IS
1872
1873 -- local constants
1874 c_ws_tab_name VARCHAR2(80);
1875
1876 -- local variables
1877 v_total_hours NUMBER(15,7);
1878 v_range_start DATE;
1879 v_range_end DATE;
1880 v_curr_date DATE;
1881 v_curr_day VARCHAR2(3); -- 3 char abbrev for day of wk.
1882 v_ws_name VARCHAR2(80); -- Work Schedule Name.
1883 v_gtv_hours VARCHAR2(80); -- get_table_value returns varchar2
1884 v_fnd_sess_row VARCHAR2(1);
1885 l_exists VARCHAR2(1);
1886 v_day_no NUMBER;
1887 p_ws_name VARCHAR2(80); -- Work Schedule Name from SCL
1888 l_id_flex_num NUMBER;
1889
1890 CURSOR get_id_flex_num IS
1891 SELECT rule_mode
1892 FROM pay_legislation_rules
1893 WHERE legislation_code = 'US'
1894 and rule_type = 'S';
1895
1896 Cursor get_ws_name (p_id_flex_num number,
1897 p_date_earned date,
1898 p_assignment_id number) IS
1899 SELECT target.SEGMENT4
1900 FROM /* route for SCL keyflex - assignment level */
1901 hr_soft_coding_keyflex target,
1902 per_all_assignments_f ASSIGN
1903 WHERE p_date_earned BETWEEN ASSIGN.effective_start_date
1904 AND ASSIGN.effective_end_date
1905 AND ASSIGN.assignment_id = p_assignment_id
1906 AND target.soft_coding_keyflex_id = ASSIGN.soft_coding_keyflex_id
1907 AND target.enabled_flag = 'Y'
1908 AND target.id_flex_num = p_id_flex_num;
1909
1910
1911 BEGIN -- work_schedule_total_hours
1912 /* Init */
1913 v_total_hours := 0;
1914 c_ws_tab_name := 'COMPANY WORK SCHEDULES';
1915
1916 /* get ID FLEX NUM */
1917 --IF pay_us_rules.g_id_flex_num IS NULL THEN
1918 hr_utility.trace('Getting ID_FLEX_NUM for US legislation ');
1919 OPEN get_id_flex_num;
1920 FETCH get_id_flex_num INTO l_id_flex_num;
1921 -- pay_us_rules.g_id_flex_num := l_id_flex_num;
1922 CLOSE get_id_flex_num;
1923 --END IF;
1924
1925 -- hr_utility.trace('pay_us_rules.g_id_flex_num '||pay_us_rules.g_id_flex_num);
1926 hr_utility.trace('l_id_flex_num '||l_id_flex_num);
1927 hr_utility.trace('assignment_action_id=' || assignment_action_id);
1928 hr_utility.trace('assignment_id=' || assignment_id);
1929 hr_utility.trace('business_group_id=' || p_bg_id);
1930 hr_utility.trace('p_range_start=' || p_range_start);
1931 hr_utility.trace('p_range_end=' || p_range_end);
1932 hr_utility.trace('element_entry_id=' || element_entry_id);
1933 hr_utility.trace('date_earned ' || date_earned);
1934
1935 /* get work schedule_name */
1936 --IF pay_us_rules.g_id_flex_num IS NOT NULL THEN
1937 IF l_id_flex_num IS NOT NULL THEN
1938 hr_utility.trace('getting work schedule name ');
1939 OPEN get_ws_name (l_id_flex_num,--pay_us_rules.g_id_flex_num,
1940 date_earned,
1941 assignment_id);
1942 FETCH get_ws_name INTO p_ws_name;
1943 CLOSE get_ws_name;
1944 END IF;
1945
1946 IF p_ws_name IS NULL THEN
1947 hr_utility.trace('Work Schedule not found ');
1948 return 0;
1949 END IF;
1950
1951 hr_utility.trace('Work Schedule '||p_ws_name);
1952
1953 --changed to select the work schedule defined
1954 --at the business group level instead of
1955 --hardcoding the default work schedule
1956 --(COMPANY WORK SCHEDULES ) to the
1957 --variable c_ws_tab_name
1958
1959 begin
1960 select put.user_table_name
1961 into c_ws_tab_name
1962 from hr_organization_information hoi
1963 ,pay_user_tables put
1964 where hoi.organization_id = p_bg_id
1965 and hoi.org_information_context ='Work Schedule'
1966 and hoi.org_information1 = put.user_table_id ;
1967
1968 EXCEPTION WHEN NO_DATA_FOUND THEN
1969 null;
1970 end;
1971
1972 -- Set range to a single week if no dates are entered:
1973 -- IF (p_range_start IS NULL) AND (p_range_end IS NULL) THEN
1974 --
1975 v_range_start := NVL(p_range_start, sysdate);
1976 v_range_end := NVL(p_range_end, sysdate + 6);
1977 --
1978 -- END IF;
1979
1980 -- Check for valid range
1981 IF v_range_start > v_range_end THEN
1982 --
1983 RETURN v_total_hours;
1984 -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
1985 -- hr_utility.raise_error;
1986 --
1987 END IF;
1988
1989 -- Get_Table_Value requires row in FND_SESSIONS. We must insert this
1990 -- record if one doe not already exist.
1991 SELECT DECODE(COUNT(session_id), 0, 'N', 'Y')
1992 INTO v_fnd_sess_row
1993 FROM fnd_sessions
1994 WHERE session_id = userenv('sessionid');
1995
1996 IF v_fnd_sess_row = 'N' THEN
1997 dt_fndate.set_effective_date(trunc(sysdate));
1998 END IF;
1999
2000 --
2001 -- Track range dates:
2002 --
2003 -- Check if the work schedule is an id or a name. If the work
2004 -- schedule does not exist, then return 0.
2005 --
2006 BEGIN
2007 select 'Y'
2008 into l_exists
2009 from pay_user_tables PUT,
2010 pay_user_columns PUC
2011 where PUC.USER_COLUMN_NAME = p_ws_name
2012 and NVL(PUC.business_group_id, p_bg_id) = p_bg_id
2013 and NVL(PUC.legislation_code,'US') = 'US'
2014 and PUC.user_table_id = PUT.user_table_id
2015 and PUT.user_table_name = c_ws_tab_name;
2016
2017
2018 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
2019 END;
2020
2021 if l_exists = 'Y' then
2022 v_ws_name := p_ws_name;
2023 else
2024 BEGIN
2025 select PUC.USER_COLUMN_NAME
2026 into v_ws_name
2027 from pay_user_tables PUT,
2028 pay_user_columns PUC
2029 where PUC.USER_COLUMN_ID = p_ws_name
2030 and NVL(PUC.business_group_id, p_bg_id) = p_bg_id
2031 and NVL(PUC.legislation_code,'US') = 'US'
2032 and PUC.user_table_id = PUT.user_table_id
2033 and PUT.user_table_name = c_ws_tab_name;
2034
2035 EXCEPTION WHEN NO_DATA_FOUND THEN
2036 RETURN v_total_hours;
2037 END;
2038 end if;
2039
2040 v_curr_date := v_range_start;
2041
2042 LOOP
2043
2044 v_day_no := TO_CHAR(v_curr_date, 'D');
2045
2046
2047 SELECT decode(v_day_no,1,'SUN',2,'MON',3,'TUE',
2048 4,'WED',5,'THU',6,'FRI',7,'SAT')
2049 INTO v_curr_day
2050 FROM DUAL;
2051
2052 v_total_hours := v_total_hours +
2053 FND_NUMBER.CANONICAL_TO_NUMBER(
2054 hruserdt.get_table_value(p_bg_id,
2055 c_ws_tab_name,
2056 v_ws_name,
2057 v_curr_day));
2058 v_curr_date := v_curr_date + 1;
2059
2060
2061 EXIT WHEN v_curr_date > v_range_end;
2062
2063 END LOOP;
2064
2065 RETURN v_total_hours;
2066
2067 END work_schedule_total_hours;
2068
2069 /****************************************************************************/
2070 /* PROCEDURE get_time_def_for_entry */
2071 /****************************************************************************/
2072 PROCEDURE get_time_def_for_entry (
2073 p_element_entry_id NUMBER,
2074 p_assignment_id NUMBER,
2075 p_assignment_action_id NUMBER,
2076 p_business_group_id NUMBER,
2077 p_time_definition_id IN OUT NOCOPY VARCHAR2
2078 ) IS
2079
2080 /* Get Date_Earned of the payroll action */
2081 CURSOR get_date_earned(cp_assignment_action_id NUMBER) IS
2082 SELECT NVL(ppa.date_earned,ppa.effective_date)
2083 FROM pay_assignment_actions paa
2084 , pay_payroll_actions ppa
2085 WHERE paa.assignment_action_id = cp_assignment_action_id
2086 AND paa.payroll_action_id = ppa.payroll_action_id;
2087
2088
2089 CURSOR chk_regsal_regwag (cp_element_entry NUMBER
2090 ,cp_bus_grp NUMBER
2091 ,cp_date DATE
2092 ) IS
2093 SELECT element_name
2094 FROM pay_element_types_f pet,
2095 pay_element_entries_f pee
2096 WHERE pee.element_entry_id = cp_element_entry
2097 AND pee.element_type_id = pet.element_type_id
2098 AND ((pet.legislation_code = 'US' and pet.business_group_id is null)
2099 or (pet.legislation_code is null and pet.business_group_id = cp_bus_grp))
2100 AND cp_date BETWEEN pee.effective_start_date
2101 AND pee.effective_end_date
2102 AND cp_date BETWEEN pet.effective_start_date
2103 AND pet.effective_end_date;
2104
2105
2106 CURSOR get_default_time_definition_id(
2107 p_time_def_name VARCHAR2,
2108 p_legislation_code VARCHAR2
2109 )
2110 IS
2111 SELECT time_definition_id
2112 FROM pay_time_definitions
2113 WHERE definition_name = p_time_def_name
2114 AND legislation_code = p_legislation_code
2115 AND business_group_id IS NULL;
2116
2117 l_date_earned date;
2118 l_time_definition_id number;
2119 lv_element_name varchar2(80);
2120 ln_def_time_def number;
2121
2122 BEGIN
2123
2124 --hr_utility.trace_on(null, 'TIME');
2125 hr_utility.trace('Entering PAY_US_RULES.get_time_def_for_entry');
2126 hr_utility.trace('p_assignment_id=' || p_assignment_id);
2127 hr_utility.trace('p_assignment_action_id='|| p_assignment_action_id);
2128 hr_utility.trace('p_business_group_id=' || p_business_group_id);
2129 hr_utility.trace('p_element_entry_id=' || p_element_entry_id);
2130
2131 -- Check if it is the same assignment id, return the value already stored
2132
2133 if g_current_asg_id = p_assignment_id then
2134
2135 -- Check whether assignment has either Regular Salary or Regular Wages
2136 -- entry. In this case we will not use the cached time definition (set by
2137 -- reduce regular). The seeded Non Allocated Time Definition will
2138 -- be assigned for Regular Salary or Regular Element
2139
2140 open get_date_earned (p_assignment_action_id);
2141 fetch get_date_earned into l_date_earned;
2142 close get_date_earned;
2143
2144 open chk_regsal_regwag (p_element_entry_id
2145 ,p_business_group_id
2146 ,l_date_earned);
2147 fetch chk_regsal_regwag into lv_element_name;
2148 close chk_regsal_regwag;
2149
2150 if lv_element_name = 'Regular Salary' or
2151 lv_element_name = 'Regular Wages' then
2152
2153 -- Get value for Non Allocated Time Definition Id
2154 open get_default_time_definition_id('Non Allocated Time Definition'
2155 ,'US');
2156 fetch get_default_time_definition_id into l_time_definition_id;
2157 close get_default_time_definition_id;
2158 p_time_definition_id := l_time_definition_id;
2159 elsif g_get_time_def_flag then
2160 l_time_definition_id :=
2161 get_time_def_for_entry_func(p_element_entry_id
2162 ,p_assignment_id
2163 ,p_assignment_action_id
2164 ,p_business_group_id
2165 ,l_date_earned);
2166 g_current_time_def_id := l_time_definition_id;
2167 p_time_definition_id := g_current_time_def_id;
2168 g_get_time_def_flag := FALSE;
2169 else
2170 p_time_definition_id := g_current_time_def_id;
2171 end if;
2172 else
2173 hr_utility.trace('Finding Time Definition ID');
2174 -- find the Date Earned of the payroll period
2175 open get_date_earned (p_assignment_action_id);
2176 fetch get_date_earned into l_date_earned;
2177 close get_date_earned;
2178
2179 open chk_regsal_regwag (p_element_entry_id
2180 ,p_business_group_id
2181 ,l_date_earned);
2182 fetch chk_regsal_regwag into lv_element_name;
2183 close chk_regsal_regwag;
2184
2185 if lv_element_name = 'Regular Salary' or
2186 lv_element_name = 'Regular Wages' then
2187 open get_default_time_definition_id('Non Allocated Time Definition'
2188 ,'US');
2189 fetch get_default_time_definition_id into l_time_definition_id;
2190 close get_default_time_definition_id;
2191 g_get_time_def_flag := TRUE;
2192 else
2193 l_time_definition_id :=
2194 get_time_def_for_entry_func(p_element_entry_id
2195 ,p_assignment_id
2196 ,p_assignment_action_id
2197 ,p_business_group_id
2198 ,l_date_earned);
2199 g_get_time_def_flag := FALSE;
2200 end if;
2201
2202 g_current_time_def_id := l_time_definition_id;
2203 p_time_definition_id := l_time_definition_id;
2204 g_current_asg_id := p_assignment_id;
2205 end if;
2206
2207 hr_utility.trace('p_time_definition_id = ' || p_time_definition_id);
2208 hr_utility.trace('Leaving PAY_US_RULES.get_time_def_for_entry');
2209 return;
2210 END;
2211
2212
2213 /*****************************************************************************
2214 * PROCEDURE get_time_def_for_entry_func *
2215 * This procedure has to maintain purity and not write into global variables *
2216 * or insert data into any database tables. No calls to hr_utility.trace *
2217 * should be made. *
2218 /****************************************************************************/
2219 FUNCTION get_time_def_for_entry_func(
2220 p_element_entry_id NUMBER,
2221 p_assignment_id NUMBER,
2222 p_assignment_action_id NUMBER,
2223 p_business_group_id NUMBER,
2224 p_time_def_date DATE
2225 ) RETURN NUMBER IS
2226
2227 /* Check if employee is flsa eligible */
2228 CURSOR get_jobs_us_flsa_code(cp_date_earned DATE
2229 ,cp_assignment_id NUMBER) IS
2230 SELECT nvl(perj.JOB_INFORMATION3, 'EX')
2231 FROM per_jobs perj,
2232 per_jobs_tl perjtl,
2233 per_all_assignments_f paa
2234 WHERE cp_date_earned BETWEEN paa.effective_start_date AND paa.effective_end_date
2235 AND paa.assignment_id = cp_assignment_id
2236 AND paa.job_id = perj.job_id
2237 AND paa.job_id = perjtl.job_id
2238 AND userenv('LANG') = perjtl.language;
2239
2240 /* Get time_definition_id from information element */
2241 CURSOR get_info_time_def_id (cp_ele_name VARCHAR2
2242 ,cp_inp_val_name VARCHAR2
2243 ,cp_assignment_id NUMBER
2244 ,cp_date DATE
2245 ) IS
2246 SELECT peev.screen_entry_value
2247 FROM pay_element_types_f pet,
2248 pay_input_values_f piv,
2249 pay_element_entries_f peef,
2250 pay_element_entry_values_f peev
2251 WHERE pet.element_name = cp_ele_name
2252 AND pet.business_group_id is NULL
2253 AND peef.element_type_id = pet.element_type_id
2254 AND pet.element_type_id = piv.element_type_id
2255 AND pet.legislation_code = 'US'
2256 AND piv.business_group_id is NULL
2257 AND piv.legislation_code = 'US'
2258 AND piv.NAME = cp_inp_val_name
2259 AND peev.element_entry_id = peef.element_entry_id
2260 AND peev.input_value_id = piv.input_value_id
2261 AND peef.assignment_id = cp_assignment_id
2262 AND cp_date BETWEEN piv.effective_start_date
2263 AND piv.effective_end_date
2264 AND cp_date BETWEEN peef.effective_start_date
2265 AND peef.effective_end_date
2266 AND cp_date BETWEEN pet.effective_start_date
2267 AND pet.effective_end_date
2268 AND cp_date BETWEEN peev.effective_start_date
2269 AND peev.effective_end_date;
2270
2271 /* Get person id and payroll id for the assignment/business
2272 group id provided */
2273 CURSOR get_assignment_info(cp_assignment_id NUMBER
2274 ,cp_date DATE) IS
2275 SELECT person_id, payroll_id
2276 FROM per_all_assignments_f
2277 WHERE assignment_id = cp_assignment_id
2278 AND cp_date BETWEEN effective_start_date AND effective_end_date;
2279
2280 -- Get time_definition_id corresponding to Overtime Week Id
2281 CURSOR get_time_from_week (cp_otl_recurring_period VARCHAR2) IS
2282 SELECT time_definition_id
2283 FROM pay_time_definitions
2284 WHERE creator_id = cp_otl_recurring_period AND creator_type = 'OTL_W';
2285
2286 -- Get time definition id defined at payroll level
2287 CURSOR get_payroll_time_definition_id (cp_payroll_id NUMBER
2288 ,cp_date DATE
2289 ) IS
2290 SELECT pap.prl_information10
2291 FROM pay_all_payrolls_f pap
2292 WHERE pap.payroll_id = cp_payroll_id
2293 AND cp_date BETWEEN pap.effective_start_date
2294 AND pap.effective_end_date;
2295
2296 -- Get default time definition
2297 CURSOR get_default_time_definition_id(
2298 p_time_def_name VARCHAR2,
2299 p_legislation_code VARCHAR2
2300 )
2301 IS
2302 SELECT time_definition_id
2303 FROM pay_time_definitions
2304 WHERE definition_name = p_time_def_name
2305 AND legislation_code = p_legislation_code
2306 AND business_group_id IS NULL;
2307
2308 CURSOR chk_seeded_elements(cp_element_entry NUMBER
2309 ,cp_bus_grp NUMBER
2310 ,cp_date DATE
2311 ) IS
2312 SELECT element_name
2313 FROM pay_element_types_f pet,
2314 pay_element_entries_f pee
2315 WHERE pee.element_entry_id = cp_element_entry
2316 AND pee.element_type_id = pet.element_type_id
2317 AND ((pet.legislation_code = 'US' and pet.business_group_id is null)
2318 or (pet.legislation_code is null and pet.business_group_id = cp_bus_grp))
2319 AND cp_date BETWEEN pee.effective_start_date
2320 AND pee.effective_end_date
2321 AND cp_date BETWEEN pet.effective_start_date
2322 AND pet.effective_end_date;
2323
2324
2325 l_time_def_id NUMBER;
2326 l_otlr_pref VARCHAR2 (30);
2327 l_ot_period_segment NUMBER;
2328 l_otl_recurring_period VARCHAR2 (120);
2329 l_person_id NUMBER;
2330 l_payroll_id NUMBER;
2331 l_jobs_us_flsa_code VARCHAR2(150);
2332 no_otl_package_function EXCEPTION;
2333 l_otl_text VARCHAR2(10000);
2334 l_default_time_def_name pay_time_definitions.definition_name%TYPE;
2335 l_legislation_code pay_time_definitions.legislation_code%TYPE;
2336 l_time_definition_id NUMBER;
2337 l_time_def_date DATE;
2338 l_element_name VARCHAR2(80);
2339
2340 PRAGMA EXCEPTION_INIT (no_otl_package_function, -6550);
2341
2342 BEGIN
2343
2344 -- OTL constants
2345 l_otlr_pref := 'TC_W_RULES_EVALUATION';
2346 l_ot_period_segment := 3;
2347
2348 -- Default time definition name
2349 l_default_time_def_name := 'Non Allocated Time Definition';
2350 l_legislation_code := 'US';
2351
2352 l_time_def_id := NULL;
2353 l_time_def_date := p_time_def_date;
2354
2355 /* Check if employee is flsa eligible */
2356 open get_jobs_us_flsa_code (l_time_def_date, p_assignment_id);
2357 fetch get_jobs_us_flsa_code into l_jobs_us_flsa_code;
2358 if get_jobs_us_flsa_code%NOTFOUND then
2359 l_jobs_us_flsa_code := 'EX';
2360 end if;
2361 close get_jobs_us_flsa_code;
2362
2363 if l_jobs_us_flsa_code <> 'EX' then
2364 /* Cursor which checks if time_definition_id can be gathered
2365 from FLSA Time Definition element */
2366 open get_info_time_def_id ('FLSA Time Definition'
2367 ,'Time Definition'
2368 ,p_assignment_id
2369 ,l_time_def_date );
2370 fetch get_info_time_def_id into l_time_def_id;
2371 if get_info_time_def_id%notfound or l_time_def_id is null then
2372 open get_assignment_info (p_assignment_id, l_time_def_date);
2373 fetch get_assignment_info into l_person_id, l_payroll_id;
2374 close get_assignment_info;
2375
2376 -- Getting Overtime week id from OTL
2377 /* BEGIN
2378 v := 'BEGIN
2379 :l_otl_recurring_period := hxc_preference_evaluation.resource_preferences ('
2380 || 'p_resource_id => :l_person_id ,'
2381 || 'p_pref_code => :l_otlr_pref ,'
2382 || 'p_attribute_n => :l_ot_period_segment ,'
2383 || 'p_evaluation_date => :l_time_def_date '
2384 || '); end;';
2385
2386 EXECUTE IMMEDIATE v
2387 USING OUT l_otl_recurring_period,
2388 IN l_person_id,
2389 IN l_otlr_pref,
2390 IN l_ot_period_segment,
2391 IN l_time_def_date;
2392 EXCEPTION
2393 WHEN no_otl_package_function THEN
2394 l_otl_recurring_period := NULL ;
2395 END;
2396 */
2397 -- Get time_definition_id corresponding to Overtime Week Id
2398 l_otl_recurring_period := NULL ;
2399
2400 if l_otl_recurring_period IS NOT NULL then
2401 open get_time_from_week (l_otl_recurring_period);
2402 fetch get_time_from_week into l_time_def_id;
2403 close get_time_from_week;
2404 end if;
2405
2406 if l_time_def_id is null then
2407 -- Get time_definition_id corresponding to Overtime Week Id
2408 open get_payroll_time_definition_id (l_payroll_id,l_time_def_date);
2409 fetch get_payroll_time_definition_id into l_time_def_id;
2410 close get_payroll_time_definition_id;
2411 end if;
2412 end if;
2413 close get_info_time_def_id;
2414
2415 open chk_seeded_elements(p_element_entry_id
2416 ,p_business_group_id
2417 ,p_time_def_date);
2418 fetch chk_seeded_elements into l_element_name;
2419
2420 close chk_seeded_elements;
2421
2422 end if;
2423
2424 /*If time_definition_id is still null till this point we assign
2425 it the default time_definition
2426 */
2427 IF (l_time_def_id IS NULL) or
2428 (l_time_def_id is not null and l_element_name = 'Regular Salary') or
2429 (l_time_def_id is not null and l_element_name = 'Regular Wages') THEN
2430 open get_default_time_definition_id(l_default_time_def_name
2431 ,l_legislation_code);
2432 fetch get_default_time_definition_id into l_time_def_id;
2433 close get_default_time_definition_id;
2434 END IF;
2435
2436 l_time_definition_id := l_time_def_id;
2437
2438 return l_time_definition_id;
2439 END get_time_def_for_entry_func;
2440
2441 -- Procedures / Functions Added for (Archived) Check Writer Process
2442
2443 PROCEDURE add_custom_xml(P_ASSIGNMENT_ACTION_ID IN NUMBER ,
2444 P_ACTION_INFORMATION_CATEGORY IN VARCHAR2,
2445 P_DOCUMENT_TYPE IN VARCHAR2) IS
2446
2447 CURSOR get_net_pay(CP_ASSIGNMENT_ACTION_ID IN NUMBER) IS
2448 SELECT net_pay
2449 FROM PAY_AC_EMP_SUM_ACTION_INFO_V
2450 WHERE action_context_id = cp_assignment_action_id
2451 AND action_information_category = 'AC SUMMARY CURRENT';
2452
2453 CURSOR get_net_pay_ytd(CP_ASSIGNMENT_ACTION_ID IN NUMBER) is
2454 SELECT net_pay
2455 FROM PAY_AC_EMP_SUM_ACTION_INFO_V
2456 WHERE action_context_id = cp_assignment_action_id
2457 AND ACTION_INFORMATION_CATEGORY = 'AC SUMMARY YTD';
2458
2459 CURSOR get_check_depoad_details ( arch_assact_id in number ,
2460 chk_assact_id in number) IS
2461
2462 SELECT pai.action_information16, ppt.CATEGORY, pai.action_information5,
2463 pai.action_information6, pai.action_information7,
2464 pai.action_information8, pai.action_information9,
2465 pai.action_information10, paa.serial_number
2466 FROM pay_action_information pai,
2467 pay_org_payment_methods_f popmf,
2468 pay_payment_types ppt,
2469 pay_assignment_actions paa
2470 WHERE pai.action_context_id = arch_assact_id
2471 AND pai.action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION'
2472 AND paa.assignment_action_id = chk_assact_id
2473 AND popmf.org_payment_method_id = pai.action_information1
2474 AND popmf.payment_type_id = ppt.payment_type_id
2475 AND paa.pre_payment_id = pai.action_information15
2476 AND pai.effective_date BETWEEN popmf.effective_start_date AND popmf.effective_end_date;
2477
2478 CURSOR get_preassact_id ( arch_assact_id in number) IS
2479 SELECT locked_action_id
2480 FROM pay_action_interlocks
2481 WHERE locking_action_id = arch_assact_id;
2482
2483 CURSOR get_depoadvice_deatils ( arch_assact_id in number) IS
2484 SELECT pai.action_information5,
2485 DECODE (pai.action_information6,
2486 'C', 'Checking Account',
2487 'Savings Account'
2488 ),
2489 pai.action_information7, pai.action_information8,
2490 pai.action_information9, pai.action_information10,
2491 pai.action_information17, pai.action_information16
2492 FROM pay_action_information pai,
2493 pay_org_payment_methods_f popmf,
2494 pay_payment_types ppt
2495 WHERE pai.action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION'
2496 AND pai.action_context_id = arch_assact_id
2497 AND pai.action_information1 = popmf.org_payment_method_id
2498 AND popmf.payment_type_id = ppt.payment_type_id
2499 AND ppt.CATEGORY = 'MT'
2500 AND pai.effective_date BETWEEN popmf.effective_start_date AND popmf.effective_end_date ;
2501
2502 CURSOR get_check_num_for_depad ( cp_assignment_action_id in number ) IS
2503 SELECT paa.serial_number, pain.action_information16 ,
2504 pain.action_information9 ,
2505 DECODE (pain.action_information6,
2506 'C', 'Checking Account',
2507 'Savings Account'
2508 ),
2509 pain.action_information7
2510 FROM pay_action_interlocks pai,
2511 pay_assignment_actions paa,
2512 pay_payroll_actions ppa,
2513 pay_action_interlocks pai1,
2514 pay_action_information pain
2515 WHERE pai.locking_action_id = cp_assignment_action_id
2516 AND pai.locked_action_id = pai1.locked_action_id
2517 AND pai.locking_action_id <> pai1.locking_action_id
2518 AND pai1.locking_action_id = paa.assignment_action_id
2519 AND paa.payroll_action_id = ppa.payroll_action_id
2520 AND ppa.action_type = 'H'
2521 AND pain.action_information15 = paa.pre_payment_id
2522 AND pain.action_context_id = pai.locking_action_id
2523 AND pain.action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION' ;
2524
2525
2526 ln_amount number;
2527 lv_amount_in_word varchar2(200);
2528 ln_net_pay_ytd number;
2529 ln_deposit_advice_number number ;
2530 lv_check_number varchar2(200);
2531 ln_check_value number ;
2532 lv_account_name varchar2(200);
2533 lv_account_type varchar2(200);
2534 ln_account_number varchar2(200);
2535 lv_transit_code varchar2(200);
2536 lv_bank_name varchar2(200);
2537 lv_bank_branch varchar2(200);
2538 ln_depoad_num number ;
2539 lv_category varchar2(200) := 'DA';
2540 ln_account_number1 number ;
2541
2542 BEGIN
2543 --hr_utility.trace ('Entering '|| 'pay_us_rules. _xml');
2544
2545 IF p_action_information_category = 'EMPLOYEE DETAILS' THEN
2546
2547
2548 OPEN get_check_depoad_details(P_ASSIGNMENT_ACTION_ID,
2549 pay_archive_chequewriter.g_chq_asg_action_id);
2550 FETCH get_check_depoad_details INTO ln_check_value,
2551 lv_category,
2552 lv_account_name,
2553 lv_account_type,
2554 ln_account_number,
2555 lv_transit_code,
2556 lv_bank_name ,
2557 lv_bank_branch,
2558 lv_check_number ;
2559 CLOSE get_check_depoad_details;
2560 IF lv_category = 'CH' THEN
2561 pay_payroll_xml_extract_pkg.load_xml_data('D','CHECK_NUMBER',lv_check_number);
2562 lv_amount_in_word := CF_word_amountFormula(ln_check_value);
2563 pay_payroll_xml_extract_pkg.load_xml_data('D','AMOUNT_IN_WORDS',lv_amount_in_word);
2564 pay_payroll_xml_extract_pkg.load_xml_data('D','CHECK_AMOUNT',ln_check_value);
2565 pay_payroll_xml_extract_pkg.load_xml_data('D','ACCOUNT_NAME',lv_account_name);
2566 pay_payroll_xml_extract_pkg.load_xml_data('D','ACCOUNT_TYPE',lv_account_type);
2567 pay_payroll_xml_extract_pkg.load_xml_data('D','ACCOUNT_NUMBER',ln_account_number);
2568 pay_payroll_xml_extract_pkg.load_xml_data('D','TRANSIT_CODE',lv_transit_code);
2569 pay_payroll_xml_extract_pkg.load_xml_data('D','BANK_NAME',lv_bank_name);
2570 pay_payroll_xml_extract_pkg.load_xml_data('D','BANK_BRANCH',lv_bank_branch);
2571
2572 ELSE
2573 OPEN get_preassact_id (P_ASSIGNMENT_ACTION_ID);
2574 FETCH get_preassact_id INTO ln_deposit_advice_number ;
2575 CLOSE get_preassact_id ;
2576 pay_payroll_xml_extract_pkg.load_xml_data('D','DEPOSIT_FINAL_ADNUM',ln_deposit_advice_number);
2577 OPEN get_depoadvice_deatils(P_ASSIGNMENT_ACTION_ID);
2578 LOOP
2579 FETCH get_depoadvice_deatils INTO lv_account_name,
2580 lv_account_type,
2581 ln_account_number,
2582 lv_transit_code,
2583 lv_bank_name ,
2584 lv_bank_branch,
2585 ln_depoad_num ,
2586 ln_check_value ;
2587 IF get_depoadvice_deatils%NOTFOUND THEN
2588 close get_depoadvice_deatils ;
2589 exit;
2590 ELSE
2591 pay_payroll_xml_extract_pkg.load_xml_data('CS','CHECK_DETAILS',null);
2592 pay_payroll_xml_extract_pkg.load_xml_data('D','ACCOUNT_NAME',lv_account_name);
2593 pay_payroll_xml_extract_pkg.load_xml_data('D','ACCOUNT_TYPE',lv_account_type);
2594 pay_payroll_xml_extract_pkg.load_xml_data('D','ACCOUNT_NUMBER',ln_account_number);
2595 pay_payroll_xml_extract_pkg.load_xml_data('D','TRANSIT_CODE',lv_transit_code);
2596 pay_payroll_xml_extract_pkg.load_xml_data('D','BANK_NAME',lv_bank_name);
2597 pay_payroll_xml_extract_pkg.load_xml_data('D','BANK_BRANCH',lv_bank_branch);
2598 pay_payroll_xml_extract_pkg.load_xml_data('D','DEPOSIT_ADVICE_NUMBER',ln_depoad_num);
2599 pay_payroll_xml_extract_pkg.load_xml_data('D','DEPOSIT_ADVICE_VALUE',ln_check_value);
2600 pay_payroll_xml_extract_pkg.load_xml_data('CE','CHECK_DETAILS',null);
2601 END IF;
2602 END LOOP;
2603 END IF;
2604
2605 IF lv_category <> 'CH' THEN
2606 OPEN get_check_num_for_depad (P_ASSIGNMENT_ACTION_ID);
2607 LOOP
2608 FETCH get_check_num_for_depad INTO ln_account_number,
2609 ln_check_value,
2610 lv_bank_name,
2611 lv_account_type,
2612 ln_account_number1;
2613 IF get_check_num_for_depad%NOTFOUND THEN
2614 close get_check_num_for_depad;
2615 EXIT;
2616 ELSE
2617 pay_payroll_xml_extract_pkg.load_xml_data('CS','CHECK_DETAILS',null);
2618 pay_payroll_xml_extract_pkg.load_xml_data('D','DEPOSIT_ADVICE_NUMBER',ln_account_number);
2619 pay_payroll_xml_extract_pkg.load_xml_data('D','DEPOSIT_ADVICE_VALUE',ln_check_value);
2620 pay_payroll_xml_extract_pkg.load_xml_data('D','ACCOUNT_TYPE',lv_account_type);
2621 pay_payroll_xml_extract_pkg.load_xml_data('D','BANK_NAME',lv_bank_name);
2622 pay_payroll_xml_extract_pkg.load_xml_data('D','ACCOUNT_NUMBER',ln_account_number1);
2623 pay_payroll_xml_extract_pkg.load_xml_data('CE','CHECK_DETAILS',null);
2624
2625
2626 END IF;
2627 END LOOP;
2628 END IF;
2629 END IF;
2630
2631 IF p_action_information_category = 'AC SUMMARY YTD' THEN
2632
2633 OPEN get_net_pay_ytd(p_assignment_action_id);
2634 FETCH get_net_pay_ytd INTO ln_net_pay_ytd;
2635 pay_payroll_xml_extract_pkg.load_xml_data('D','NET_PAY_YTD',ln_net_pay_ytd );
2636 CLOSE get_net_pay_ytd;
2637
2638 END IF;
2639
2640 IF p_action_information_category = 'AC SUMMARY CURRENT' THEN
2641
2642 OPEN get_net_pay(p_assignment_action_id);
2643 FETCH get_net_pay into ln_amount;
2644 CLOSE get_net_pay;
2645
2646 pay_payroll_xml_extract_pkg.load_xml_data('D','NET_PAY',ln_amount);
2647
2648 END IF;
2649 hr_utility.trace('exiting PAY_US_RULES.add_custom_xml');
2650 END add_custom_xml;
2651
2652 FUNCTION CF_word_amountFormula(CP_LN_AMOUNT IN NUMBER) RETURN VARCHAR2 IS
2653
2654 l_word_text varchar2(240);
2655 l_width number := 73; -- Width of word amount field
2656 BEGIN
2657 l_word_text := get_word_value(cp_ln_amount);
2658
2659 -- Format the output to have asterisks on right-hand side
2660 IF NVL(LENGTH(l_word_text), 0) <= l_width THEN
2661 l_word_text := rpad(l_word_text,l_width,'*');
2662
2663 ELSIF NVL(LENGTH(l_word_text), 0) <= l_width*2 THEN
2664 -- Allow for word wrapping
2665 l_word_text := rpad(l_word_text,l_width*2 -
2666 (l_width-instr(substr(l_word_text,1,l_width+1),' ',-1)),'*');
2667 ELSIF NVL(LENGTH(l_word_text), 0) <= l_width*3 THEN
2668
2669 l_word_text := rpad(l_word_text,l_width*3,'*');
2670 END IF;
2671 RETURN(l_word_text);
2672 END CF_word_amountFormula ;
2673
2674 FUNCTION get_word_value (P_AMOUNT NUMBER) RETURN VARCHAR2 IS
2675
2676 l_word_amount varchar2(240) := convert_number(trunc(p_amount));
2677 l_currency_word varchar2(240);
2678 l_log integer;
2679 l_unit_ratio number := 100; --ie. the number of subunits(cents) in a unit(dollar)
2680 l_unit_singular varchar2(6) := 'Dollar';
2681 l_unit_plural varchar2(7) := 'Dollars';
2682 l_sub_unit_singular varchar2(4) := 'Cent';
2683 l_sub_unit_plural varchar2(5) := 'Cents';
2684
2685 /* This is a workaround until bug #165793 is fixed */
2686 FUNCTION my_log (a integer, b integer) RETURN NUMBER IS
2687 BEGIN
2688 IF a <> 10 THEN RETURN(NULL);
2689 ELSIF b > 0 AND b <= 10 THEN RETURN(1);
2690 ELSIF b > 10 AND b <= 100 THEN RETURN(2);
2691 ELSIF b > 100 AND b <= 1000 THEN RETURN(3);
2692 ELSE RETURN(NULL);
2693 END IF;
2694 RETURN NULL;
2695 END my_log;
2696
2697 BEGIN
2698 l_log := my_log(10,l_unit_ratio);
2699
2700 select initcap(lower(
2701 l_word_amount||' '||
2702 decode(trunc(p_amount),
2703 1,l_unit_singular,
2704 l_unit_plural)||' And '||
2705 lpad(to_char(trunc((p_amount-trunc(p_amount))*l_unit_ratio)),
2706 ceil(l_log),'0')||' '||
2707 decode(trunc((p_amount-trunc(p_amount))*l_unit_ratio),
2708 1,l_sub_unit_singular,
2709 l_sub_unit_plural)
2710 ))
2711 into l_currency_word
2712 from dual;
2713
2714 RETURN(l_currency_word);
2715 END get_word_value;
2716
2717 FUNCTION convert_number(IN_NUMERAL INTEGER := 0) RETURN VARCHAR2 IS
2718
2719 number_too_large exception;
2720 numeral integer := abs(in_numeral);
2721 max_digit integer := 9; -- for numbers less than a (US) billion
2722 number_text varchar2(240) := '';
2723 current_segment varchar2(80);
2724 b_zero varchar2(25) := 'Zero';
2725 b_thousand varchar2(25) := ' Thousand ';
2726 thousand number := power(10,3);
2727 b_million varchar2(25) := ' Million ';
2728 million number := power(10,6);
2729
2730 FUNCTION convert_number (segment number) RETURN VARCHAR2 IS
2731 value_text varchar2(80);
2732 BEGIN
2733 value_text := to_char( to_date(segment,'YYYY'),'Yyyysp');
2734 RETURN(value_text);
2735 END;
2736
2737 BEGIN
2738
2739 IF numeral >= power(10,max_digit) THEN
2740 RAISE number_too_large;
2741 END IF;
2742
2743 IF numeral = 0 THEN
2744 RETURN(b_zero);
2745 END IF;
2746
2747 current_segment := trunc(numeral/million);
2748 numeral := numeral - (current_segment * million);
2749 IF current_segment <> 0 THEN
2750 number_text := number_text||convert_number(current_segment)||b_million;
2751 END IF;
2752
2753 current_segment := trunc(numeral/thousand);
2754 numeral := numeral - (current_segment * thousand);
2755 IF current_segment <> 0 THEN
2756 number_text := number_text||convert_number(current_segment)||b_thousand;
2757 END IF;
2758
2759 IF numeral <> 0 THEN
2760 number_text := number_text||convert_number(numeral);
2761 END IF;
2762
2763 number_text := substr(number_text,1,1) ||
2764 rtrim(lower(substr(number_text,2,NVL(length(number_text), 0))));
2765
2766 RETURN(number_text);
2767
2768 EXCEPTION
2769 WHEN number_too_large THEN
2770 RETURN(null);
2771 WHEN OTHERS THEN
2772 RETURN(null);
2773 END convert_number ;
2774 --
2775 --
2776 -- Added this procedure to be used by Global Payslip Printing Solution for US
2777 PROCEDURE get_token_names(p_pa_token OUT NOCOPY varchar2
2778 ,p_cs_token OUT NOCOPY varchar2) IS
2779 BEGIN
2780
2781 p_pa_token := 'TRANSFER_PAYROLL_ID';
2782 p_cs_token := 'TRANSFER_CONSOLIDATION_SET_ID';
2783
2784 END get_token_names;
2785 --
2786 --
2787 END PAY_US_RULES;
2788