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