[Home] [Help]
PACKAGE BODY: APPS.PAY_US_PAYSLIP_SIMULATION_MAIN
Source
1 PACKAGE BODY pay_us_payslip_simulation_main AS
2 /* $Header: pyuspssm.pkb 120.0.12020000.13 2013/04/09 03:20:42 emunisek noship $ */
3 /*
4
5 Change List
6 -----------
7 Date Name Vers Bug No Description
8 ----------- ---------- ----- ------- -------------------------------
9 04-FEB-2013 emunisek 120.0 Created. Bug#16082307
10 04-FEB-2013 emunisek 120.1 After enabling Dual Checkin,
11 arcsing in file to generate
12 Checkfile Equivalence
13 05-FEB-2013 emunisek 120.2 Replaced sequence related to
14 PAY_ACTION_INFORMATION_S with
15 PAY_SIMULATION_INFORMATION_S
16 06-FEB-2013 emunisek 120.3 Resolved issues in update_asg_data
17 Procedure
18 07-FEB-2013 emunisek 120.4 Resolved issues with display of Tax
19 Deductions and Tax Withholding Details.
20 13-Feb-2013 emunisek 120.5 Made changes to allow user to enter
21 NULL values to Tax Amount and Override
22 Fields. Made changes to show Simulation
23 Name on Output also.
24 15-Feb-2013 emunisek 120.6 Made change to make sure Elements
25 associated to other Salary Basis than
26 Assignment's Salary Basis does not appear
27 in Earnings/Deductions LOV as their Element
28 Entry creation ends in error.
29 Made changes to make sure number related
30 fields are handled correctly with the
31 canonical conversion as required.
32 18-Feb-2013 emunisek 120.7 Made changes so that the City Tax gets
33 displayed in Simulation Output without
34 the <BLANK> Tag.
35 20-Feb-2013 emunisek 120.8 Made changes so that the Simulation Output
36 shows Employee and Employer details in the
37 event of Zero wages in the processing.
38 Also made changes to pickup the Exclusion
39 element set details from Record with Document
40 Type as Payroll Simulator.
41 22-Feb-2013 emunisek 120.9 Corrected the Query which gathers Element-Input
42 details to make sure that the total length is
43 restricted to 60 Characters.
44 07-Mar-2013 emunisek 120.10 16281614 Created procedure simulation_generate. This
45 was delivered earlier through package
46 pay_payroll_xml_extract_pkg. Added additional
47 procedures build_sql,print_blob and flex_seg_enabled
48 which are needed for simulation_generate
49 14-Mar-2013 emunisek 120.11 16482011 Made changes so that Employee Name and Employer
50 Address details get displayed in the same way as
51 in Payslip
52 08-Apr-2013 emunisek 120.12 16605117 Made changes so that Element Name under Rate Details
53 section displays Simulation Name if present.
54 */
55
56 gv_package VARCHAR2(100) := 'pay_us_payslip_simulation_main';
57 gn_gross_earn_def_bal_id number := 0;
58
59 gv_dim_asg_tg_ytd VARCHAR2(100) := '_ASG_TG_YTD';
60 gv_dim_asg_gre_ytd VARCHAR2(100) := '_ASG_GRE_YTD';
61 gv_dim_asg_jd_gre_ytd VARCHAR2(100) := '_ASG_JD_GRE_YTD';
62
63 gv_dim_asg_tg_ptd VARCHAR2(100) := '_ASG_TG_PTD';
64 gv_dim_asg_gre_ptd VARCHAR2(100) := '_ASG_GRE_PTD';
65 gv_dim_asg_jd_gre_ptd VARCHAR2(100) := '_ASG_JD_GRE_PTD';
66
67 /* Procedure : initialization_process
68 Purpose : This procedure is to initialize the PL/SQL tables required
69 to hold the Data gathered during archive_data procedure.
70 Important : This is of more significance for Canada Localization as
71 it populates the Labels for Summary Section for required
72 Language along with determing the YTD Balance Dimension to
73 be used based on the Organization Level setting
74 */
75
76 PROCEDURE initialization_process(p_legislation_code VARCHAR2,
77 p_payroll_action_id NUMBER)
78 IS
79
80 lv_procedure_name VARCHAR2(100) := '.initialization_process';
81
82 CURSOR c_arch_labels
83 IS
84 SELECT language,
85 lookup_code,
86 meaning
87 FROM fnd_lookup_values
88 WHERE lookup_type = 'CA_CHEQUE_LABELS'
89 AND lookup_code IN ('CURRENT', 'YTD');
90
91 CURSOR cur_reporting_level(cp_payroll_action_id NUMBER)
92 IS
93 SELECT org_information1
94 FROM hr_organization_information hoi,
95 pay_payroll_actions ppa
96 where ppa.payroll_action_id = cp_payroll_action_id
97 and hoi.organization_id = ppa.business_group_id
98 and hoi.org_information_context = 'Payroll Archiver Level';
99
100 i NUMBER;
101 lv_reporting_level hr_organization_information.org_information1%TYPE;
102
103 BEGIN
104
105 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
106
107 pay_us_payslip_simulation_main.lrr_act_tab.delete;
108 pay_us_payslip_simulation_main.emp_elements_tab.delete;
109
110 hr_utility.set_location(gv_package || lv_procedure_name, 10);
111
112 IF p_legislation_code = 'CA' THEN
113
114 hr_utility.set_location(gv_package || lv_procedure_name, 20);
115
116 OPEN cur_reporting_level(p_payroll_action_id);
117 FETCH cur_reporting_level INTO lv_reporting_level;
118
119 IF cur_reporting_level%NOTFOUND THEN
120 lv_reporting_level := 'GRE';
121 END IF;
122
123 CLOSE cur_reporting_level;
124
125 gv_reporting_level := lv_reporting_level;
126
127 IF gv_reporting_level = 'TAXGRP' THEN
128 gv_ytd_balance_dimension := gv_dim_asg_tg_ytd;
129 ELSE
130 gv_ytd_balance_dimension := gv_dim_asg_gre_ytd;
131 END IF;
132
133 IF pay_us_payslip_simulation_main.ltr_summary_labels.count = 0 THEN
134
135 hr_utility.set_location(gv_package || lv_procedure_name, 30);
136
137 i := 0;
138
139 FOR lbl in c_arch_labels LOOP
140
141 pay_us_payslip_simulation_main.ltr_summary_labels(i).language := lbl.language;
142 pay_us_payslip_simulation_main.ltr_summary_labels(i).lookup_code := lbl.lookup_code;
143 pay_us_payslip_simulation_main.ltr_summary_labels(i).meaning := lbl.meaning;
144
145 i := i + 1;
146
147 END LOOP;
148
149 END IF;
150
151 END IF; /*p_legislation_code = 'CA' IF*/
152
153 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
154
155 END initialization_process;
156
157 /* Procedure : update_asg_data
158 Purpose : This procedure is to update the Assignment related data as
159 per the modifications specified on Payroll Simulator page
160 so that Payroll calculations happen accordingly. The
161 details specified on Payroll Simulator page are stored to
162 table PER_ASSIGNMENT_EXTRA_INFO by Core Payroll. This
163 procedure determines the necessary updates to Assignment
164 data and carries them as required.
165 Important : The changes made in this procedure are on the actual data.
166 But since the entire Payroll simulation process is rolled
167 back at Database level, none of these changes will get
168 saved to the database.
169 */
170
171 PROCEDURE update_asg_data(p_source_action_id NUMBER,
172 p_effective_date DATE DEFAULT NULL)
173
174 IS
175
176 CURSOR get_details (cp_source_action_id NUMBER)
177 IS
178 SELECT paa.assignment_id,
179 ppa.business_group_id,
180 ppa.effective_date,
181 ppa.date_earned
182 FROM pay_assignment_actions paa,
183 pay_payroll_actions ppa
184 WHERE paa.assignment_action_id = cp_source_action_id
185 AND ppa.payroll_action_id = paa.payroll_action_id;
186
187 CURSOR get_legislation_code(cp_business_group_id NUMBER,
188 cp_effective_date DATE)
189 IS
190 SELECT pbg.legislation_code
191 FROM per_business_groups pbg
192 WHERE pbg.business_group_id = cp_business_group_id
193 AND cp_effective_date BETWEEN pbg.date_from
194 AND NVL(pbg.date_to,TO_DATE('31/12/4712','DD/MM/YYYY'));
195
196 CURSOR get_person_details(cp_assignment_id NUMBER,
197 cp_effective_date DATE)
198 IS
199 SELECT paf.person_id,
200 paf.object_version_number
201 FROM per_all_assignments_f paf
202 WHERE paf.assignment_id = cp_assignment_id
203 AND cp_effective_date BETWEEN effective_start_date
204 AND effective_end_date;
205
206 CURSOR get_address_details(cp_person_id NUMBER,
207 cp_effective_date DATE,
208 cp_legislation_code VARCHAR2)
209 IS
210 SELECT address_id,
211 date_from,
212 date_to,
213 object_version_number
214 FROM per_addresses
215 WHERE person_id = cp_person_id
216 AND cp_effective_date BETWEEN date_from
217 AND NVL(date_to,TO_DATE('31-12-4712','DD-MM-YYYY'))
218 AND primary_flag = 'Y'
219 AND style = cp_legislation_code;
220
221 CURSOR get_location_state(cp_location_id NUMBER)
222 IS
223 SELECT NVL(hl.loc_information17,hl.region_2)
224 FROM hr_locations_all hl
225 WHERE hl.location_id = cp_location_id;
226
227 CURSOR get_resident_zip_code(cp_jurisdiction_code VARCHAR2)
228 IS
229 SELECT MIN(puzc.zip_start)
230 FROM pay_us_zip_codes puzc
231 WHERE puzc.state_code = SUBSTR(cp_jurisdiction_code,1,2)
232 AND puzc.county_code = SUBSTR(cp_jurisdiction_code,4,3)
233 AND puzc.city_code = SUBSTR(cp_jurisdiction_code,8,4);
234
235 CURSOR get_assignment_information(cp_assignment_id NUMBER,
236 cp_legislation_code VARCHAR2)
237 IS
238 SELECT paei.aei_information_category,
239 paei.aei_information1,
240 paei.aei_information2,
241 paei.aei_information3,
242 paei.aei_information4,
243 paei.aei_information5,
244 paei.aei_information6,
245 paei.aei_information7,
246 paei.aei_information8,
247 paei.aei_information9,
248 paei.aei_information10,
249 paei.aei_information11,
250 paei.aei_information12,
251 paei.aei_information13,
252 paei.aei_information14,
253 paei.aei_information15,
254 paei.aei_information16,
255 paei.aei_information17,
256 paei.aei_information18,
257 paei.aei_information19,
258 paei.aei_information20,
259 paei.aei_information21,
260 paei.aei_information22,
261 paei.aei_information23,
262 paei.aei_information24,
263 paei.aei_information25,
264 paei.aei_information26,
265 paei.aei_information27,
266 paei.aei_information28,
267 paei.aei_information29,
268 paei.aei_information30
269 FROM per_assignment_extra_info paei
270 WHERE paei.assignment_id = cp_assignment_id
271 AND (paei.aei_information_category
272 LIKE '%'||cp_legislation_code||'_SIMULATION_REGION1'
273 OR
274 paei.aei_information_category
275 LIKE '%'||cp_legislation_code||'_SIMULATION_REGION2'
276 );
277
278 CURSOR get_federal_tax_details(cp_assignment_id NUMBER,
279 cp_effective_date DATE)
280 IS
281 SELECT pft.emp_fed_tax_rule_id,
282 pft.object_version_number
283 FROM pay_us_emp_fed_tax_rules_f pft
284 WHERE pft.assignment_id = cp_assignment_id
285 AND cp_effective_date BETWEEN pft.effective_start_date
286 AND pft.effective_end_date;
287
288 CURSOR get_state_tax_details(cp_assignment_id NUMBER,
289 cp_effective_date DATE,
290 cp_state_abbrev VARCHAR2)
291 IS
292 SELECT pst.emp_state_tax_rule_id,
293 pst.object_version_number
294 FROM pay_us_emp_state_tax_rules_f pst,
295 pay_us_states pus
296 WHERE pst.assignment_id = cp_assignment_id
297 AND cp_effective_date BETWEEN pst.effective_start_date
298 AND pst.effective_end_date
299 AND pst.state_code = pus.state_code
300 AND pus.state_abbrev = cp_state_abbrev;
301
302 CURSOR get_override_location(cp_assignment_id NUMBER,
303 cp_effective_date DATE)
304 IS
305 SELECT hsck.segment18
306 FROM hr_soft_coding_keyflex hsck,
307 per_all_assignments_f paf
308 WHERE hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
309 AND paf.assignment_id = cp_assignment_id
310 AND cp_effective_date BETWEEN paf.effective_start_date
311 AND paf.effective_end_date;
312
313
314 lv_procedure_name VARCHAR2(100) := '.update_asg_data';
315
316 ln_assignment_id per_all_assignments_f.assignment_id%TYPE;
317 ln_person_id per_all_assignments_f.person_id%TYPE;
318 ln_business_group_id per_all_assignments_f.business_group_id%TYPE;
319 ld_effective_date DATE;
320 ld_date_earned DATE;
321 lv_legislation_code VARCHAR2(2);
322
323 lct_asg_information get_assignment_information%ROWTYPE;
324 lct_region1 get_assignment_information%ROWTYPE;
325 lct_default_region1 get_assignment_information%ROWTYPE;
326 lct_region2 get_assignment_information%ROWTYPE;
327 lct_default_region2 get_assignment_information%ROWTYPE;
328
329 lv_resident_addr_changed VARCHAR2(2) := 'N';
330 lv_work_location_changed VARCHAR2(2) := 'N';
331 lv_work_at_home_changed VARCHAR2(2) := 'N';
332 lv_override_location_id hr_locations_all.location_id%TYPE := NULL;
333
334 lv_federal_tax_changed VARCHAR2(2) := 'N';
335 lv_rs_state_tax_changed VARCHAR2(2) := 'N';
336 lv_wk_state_tax_changed VARCHAR2(2) := 'N';
337
338 lv_rs_state_changed VARCHAR2(2) := 'N';
339 lv_wk_state_changed VARCHAR2(2) := 'N';
340 lv_same_rs_wk_state VARCHAR2(2) := 'N';
341 lv_work_state VARCHAR2(2);
342 lv_default_work_state VARCHAR2(2);
343 lv_resident_jd_code VARCHAR2(11);
344
345 lct_address_details get_address_details%ROWTYPE;
346
347 lv_resident_state pay_us_states.state_abbrev%TYPE;
348 lv_resident_county pay_us_counties.county_name%TYPE;
349 lv_resident_city pay_us_city_names.city_name%TYPE;
350 lv_resident_zip_code pay_us_zip_codes.zip_start%TYPE;
351
352 lv_datetrack_update_mode VARCHAR2(50) := 'CORRECTION';
353 ln_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
354 ln_cagr_grade_def_id per_all_assignments_f.cagr_grade_def_id%TYPE;
355 lv_cagr_concatenated_segments VARCHAR2(2000);
356 ln_comment_id per_all_assignments_f.comment_id%TYPE;
357 ln_soft_coding_keyflex_id hr_soft_coding_keyflex.soft_coding_keyflex_id%TYPE;
358 ld_effective_start_date DATE;
359 ld_effective_end_date DATE;
360 lv_concatenated_segments hr_soft_coding_keyflex.concatenated_segments%TYPE;
361 lb_no_managers_warning BOOLEAN;
362 lb_other_manager_warning BOOLEAN;
363 lb_hourly_salaried_warning BOOLEAN;
364 lb_gsp_post_process_warning VARCHAR2(2000);
365
366 ln_special_ceiling_step_id per_all_assignments_f.special_ceiling_step_id%TYPE;
367 ln_people_group_id per_all_assignments_f.people_group_id%TYPE;
368 lv_group_name pay_people_groups.group_name%TYPE;
369 lb_spp_delete_warning BOOLEAN;
370 lv_entries_changed_warning VARCHAR2(10);
371 lb_tax_dist_changed_warning BOOLEAN;
372
373 ln_emp_fed_tax_rule_id pay_us_emp_fed_tax_rules_f.emp_fed_tax_rule_id%TYPE;
374 ln_ft_object_version_number pay_us_emp_fed_tax_rules_f.object_version_number%TYPE;
375 ln_emp_state_tax_rule_id pay_us_emp_state_tax_rules_f.emp_state_tax_rule_id%TYPE;
376 ln_st_object_version_number pay_us_emp_state_tax_rules_f.object_version_number%TYPE;
377
378 FUNCTION value_changed(p_value VARCHAR2,
379 p_default_value VARCHAR2)
380
381 RETURN BOOLEAN
382
383 IS
384
385 lv_procedure_name VARCHAR2(100) := '.update_asg_data.value_changed';
386 lb_return BOOLEAN := FALSE;
387
388 BEGIN
389
390 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
391
392 IF (p_value IS NULL AND p_default_value IS NOT NULL)
393 OR
394 (p_value IS NOT NULL AND p_default_value IS NULL)
395 OR
396 (p_value IS NOT NULL AND p_default_value IS NOT NULL
397 AND p_value <> p_default_value)
398 THEN
399
400 lb_return := TRUE;
401
402 END IF;
403
404 RETURN lb_return;
405
406 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
407
408 END value_changed;
409
410 BEGIN
411
412 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
413
414 OPEN get_details(p_source_action_id);
415 FETCH get_details INTO ln_assignment_id,
416 ln_business_group_id,
417 ld_effective_date,
418 ld_date_earned;
419 CLOSE get_details;
420
421 OPEN get_person_details(ln_assignment_id,ld_effective_date);
422 FETCH get_person_details INTO ln_person_id,ln_asg_object_version_number;
423 CLOSE get_person_details;
424
425 hr_utility.set_location(gv_package || lv_procedure_name, 10);
426
427 IF p_effective_date IS NOT NULL THEN
428
429 hr_utility.set_location(gv_package || lv_procedure_name, 20);
430
431 ld_effective_date := p_effective_date;
432
433 END IF;
434
435 OPEN get_legislation_code(ln_business_group_id,ld_effective_date);
436 FETCH get_legislation_code INTO lv_legislation_code;
437 CLOSE get_legislation_code;
438
439 hr_utility.set_location(gv_package || lv_procedure_name, 30);
440
441 OPEN get_assignment_information(ln_assignment_id,
442 lv_legislation_code);
443
444 LOOP
445
446 FETCH get_assignment_information INTO lct_asg_information;
447
448 IF get_assignment_information%NOTFOUND THEN
449
450 EXIT;
451
452 END IF;
453
454 IF lct_asg_information.aei_information_category
455 = lv_legislation_code ||'_SIMULATION_REGION1' THEN
456
457 lct_region1 := lct_asg_information;
458
459 ELSIF lct_asg_information.aei_information_category
460 = lv_legislation_code ||'_SIMULATION_REGION2' THEN
461
462 lct_region2 := lct_asg_information;
463
464 ELSIF lct_asg_information.aei_information_category
465 = 'DEFAULT_'||lv_legislation_code ||'_SIMULATION_REGION1' THEN
466
467 lct_default_region1 := lct_asg_information;
468
469 ELSIF lct_asg_information.aei_information_category
470 = 'DEFAULT_'||lv_legislation_code ||'_SIMULATION_REGION2' THEN
471
472 lct_default_region2 := lct_asg_information;
473
474 END IF;
475
476 hr_utility.set_location(gv_package || lv_procedure_name, 40);
477
478 END LOOP;
479
480 CLOSE get_assignment_information;
481
482 hr_utility.set_location(gv_package || lv_procedure_name, 50);
483
484 IF lv_legislation_code = 'US' THEN
485
486 IF lct_region1.aei_information_category = 'US_SIMULATION_REGION1' AND
487 lct_default_region1.aei_information_category = 'DEFAULT_US_SIMULATION_REGION1'
488 THEN
489
490 /* AEI_INFORMATION1 maps to Resident State. This column captures State Code
491 corresponding to Resident State */
492
493 IF value_changed(lct_region1.aei_information1,
494 lct_default_region1.aei_information1) THEN
495
496 lv_rs_state_changed := 'Y';
497 lv_resident_addr_changed := 'Y'; /* Since Resident state changed, the
498 Resident Address also got changed */
499
500 END IF;
501
502 /* If Resident State changed, it means the Resident Address also got changed.
503 So we need to explicity check if the Resident Address got changed only if
504 the Resident State remains same */
505
506 IF lv_rs_state_changed = 'N' THEN
507
508 /* AEI_INFORMATION3 maps to Resident City. This Column captures the data in
509 JURISDICTION_CODE||'-||CITY_NAME format. If the City Name changes but the
510 Jurisdiction Code remains same, then we can consider that the City did not
511 change as the Taxation is based on Jurisdiction Code */
512
513 IF value_changed(SUBSTR(lct_region1.aei_information3,1,11),
514 SUBSTR(lct_default_region1.aei_information3,1,11)) THEN
515
516 lv_resident_addr_changed := 'Y';
517
518 END IF;
519
520 END IF; /* lv_rs_state_changed := 'N' IF */
521
522 /* AEI_INFORMATION4 maps to Work Location. This Column captures the
523 LOCATION_ID corresponding to the Work Location. */
524
525 IF value_changed(lct_region1.aei_information4,
526 lct_default_region1.aei_information4) THEN
527
528 lv_work_location_changed := 'Y';
529
530 END IF;
531
532 /* Only if Work Location changed, we need to see if the Work State has changed
533 If Work Location remains same, we can assume that Work State is same */
534
535 IF lv_work_location_changed = 'Y' THEN
536
537 OPEN get_location_state(lct_region1.aei_information4);
538 FETCH get_location_state INTO lv_work_state;
539 CLOSE get_location_state;
540
541 OPEN get_location_state(lct_default_region1.aei_information4);
542 FETCH get_location_state INTO lv_default_work_state;
543 CLOSE get_location_state;
544
545 IF lv_work_state <> lv_default_work_state THEN
546
547 lv_wk_state_changed := 'Y';
548
549 END IF;
550
551 END IF;
552
553 /* AEI_INFORMATION5 maps to Work at Home Preference */
554
555 IF value_changed(lct_region1.aei_information5,
556 lct_default_region1.aei_information5) THEN
557
558 lv_work_at_home_changed := 'Y';
559
560 END IF;
561
562 IF lv_resident_addr_changed = 'Y' THEN
563
564 /* Resident Address changed for Employee. Employee Address needs to be
565 updated with the latest details. This update will be done along with removal
566 of Taxation Override section of Address if any so that the latest address
567 will be considered. This is done to make sure that the latest address does not
568 get ignored in the event of Taxation Override address already present*/
569
570 /* Retrieve the State Abbreviation, County Name, City Name and Zip Code
571 to update the address */
572
573 SELECT pus.state_abbrev
574 INTO lv_resident_state
575 FROM pay_us_states pus
576 WHERE pus.state_code = lct_region1.aei_information1;
577
578 SELECT puc.county_name
579 INTO lv_resident_county
580 FROM pay_us_counties puc
581 WHERE puc.state_code = lct_region1.aei_information1
582 AND puc.county_code = SUBSTR(lct_region1.aei_information2,4,3);
583
584 lv_resident_city := SUBSTR(lct_region1.aei_information3,13);
585 lv_resident_jd_code := SUBSTR(lct_region1.aei_information3,1,11);
586
587 OPEN get_resident_zip_code(lv_resident_jd_code);
588 FETCH get_resident_zip_code INTO lv_resident_zip_code;
589 CLOSE get_resident_zip_code;
590
591 /* For Indiana State, the Resident address on Date paid ( ld_effective_date here)
592 will be considered for Payroll processing. For other states, the Resident address
593 on Date Earned is considered. So we will update the Address Record with the latest
594 address accordingly */
595
596 IF lv_resident_state = 'IN' THEN
597
598 OPEN get_address_details(ln_person_id,ld_effective_date,lv_legislation_code);
599
600 ELSE
601
602 OPEN get_address_details(ln_person_id,ld_date_earned,lv_legislation_code);
603
604 END IF;
605
606 FETCH get_address_details INTO lct_address_details;
607 CLOSE get_address_details;
608
609 hr_person_address_api.update_us_person_address
610 ( p_effective_date => ld_effective_date
611 ,p_address_id => lct_address_details.address_id
612 ,p_object_version_number => lct_address_details.object_version_number
613 ,p_date_from => lct_address_details.date_from
614 ,p_date_to => lct_address_details.date_to
615 ,p_city => lv_resident_city
616 ,p_county => lv_resident_county
617 ,p_state => lv_resident_state
618 ,p_zip_code => lv_resident_zip_code
619 ,p_add_information18 => NULL
620 ,p_add_information19 => NULL
621 ,p_add_information17 => NULL
622 ,p_add_information20 => NULL
623 );
624
625 END IF; /* lv_resident_addr_changed = 'Y' IF */
626
627 /* In the event of Work Location change, it is necessary to remove the Override
628 Location, if any. First determine if there is a Override Location*/
629
630 IF lv_work_location_changed = 'Y' THEN
631
632 OPEN get_override_location(ln_assignment_id,ld_effective_date);
633 FETCH get_override_location INTO lv_override_location_id;
634 CLOSE get_override_location;
635
636 END IF;
637
638 IF lv_override_location_id IS NOT NULL THEN
639
640 /* If there is Override Work Location set in Soft Coded Flexfield
641 Level, then that needs to be removed so that the new location
642 can take effect. Override Work Location is stored in Segment18 */
643
644 /* This API call updates the Work At Home Preference also. If there is
645 change to this value, then it will also happen as we are passing the
646 latest value stored in lct_region1.aei_information5*/
647
648 hr_assignment_api.update_emp_asg
649 ( p_effective_date => ld_effective_date
650 ,p_datetrack_update_mode => lv_datetrack_update_mode
651 ,p_assignment_id => ln_assignment_id
652 ,p_object_version_number => ln_asg_object_version_number
653 ,p_work_at_home => lct_region1.aei_information5
654 ,p_segment18 => NULL
655 ,p_cagr_grade_def_id => ln_cagr_grade_def_id
656 ,p_cagr_concatenated_segments => lv_cagr_concatenated_segments
657 ,p_comment_id => ln_comment_id
658 ,p_soft_coding_keyflex_id => ln_soft_coding_keyflex_id
659 ,p_effective_start_date => ld_effective_start_date
660 ,p_effective_end_date => ld_effective_end_date
661 ,p_concatenated_segments => lv_concatenated_segments
662 ,p_no_managers_warning => lb_no_managers_warning
663 ,p_other_manager_warning => lb_other_manager_warning
664 ,p_hourly_salaried_warning => lb_hourly_salaried_warning
665 ,p_gsp_post_process_warning => lb_gsp_post_process_warning
666 );
667
668 ELSIF lv_work_at_home_changed = 'Y' THEN
669
670 /* If above API call does not get executed because the Override Location is
671 not present, then we need to call below API to update Work At Home value
672 and this needs to be done only if there is a change to Work At Home*/
673
674 hr_assignment_api.update_emp_asg
675 ( p_effective_date => ld_effective_date
676 ,p_datetrack_update_mode => lv_datetrack_update_mode
677 ,p_assignment_id => ln_assignment_id
678 ,p_object_version_number => ln_asg_object_version_number
679 ,p_work_at_home => lct_region1.aei_information5
680 ,p_cagr_grade_def_id => ln_cagr_grade_def_id
681 ,p_cagr_concatenated_segments => lv_cagr_concatenated_segments
682 ,p_comment_id => ln_comment_id
683 ,p_soft_coding_keyflex_id => ln_soft_coding_keyflex_id
684 ,p_effective_start_date => ld_effective_start_date
685 ,p_effective_end_date => ld_effective_end_date
686 ,p_concatenated_segments => lv_concatenated_segments
687 ,p_no_managers_warning => lb_no_managers_warning
688 ,p_other_manager_warning => lb_other_manager_warning
689 ,p_hourly_salaried_warning => lb_hourly_salaried_warning
690 ,p_gsp_post_process_warning => lb_gsp_post_process_warning
691 );
692
693 END IF;
694
695 IF lv_work_location_changed = 'Y' THEN
696
697 hr_assignment_api.update_emp_asg_criteria
698 ( p_effective_date => ld_effective_date
699 ,p_datetrack_update_mode => lv_datetrack_update_mode
700 ,p_assignment_id => ln_assignment_id
701 ,p_location_id => lct_region1.aei_information4
702 ,p_object_version_number => ln_asg_object_version_number
703 ,p_special_ceiling_step_id => ln_special_ceiling_step_id
704 ,p_people_group_id => ln_people_group_id
705 ,p_soft_coding_keyflex_id => ln_soft_coding_keyflex_id
706 ,p_group_name => lv_group_name
707 ,p_effective_start_date => ld_effective_start_date
708 ,p_effective_end_date => ld_effective_end_date
709 ,p_org_now_no_manager_warning => lb_no_managers_warning
710 ,p_other_manager_warning => lb_other_manager_warning
711 ,p_spp_delete_warning => lb_spp_delete_warning
712 ,p_entries_changed_warning => lv_entries_changed_warning
713 ,p_tax_district_changed_warning => lb_tax_dist_changed_warning
714 ,p_concatenated_segments => lv_concatenated_segments
715 ,p_gsp_post_process_warning => lb_gsp_post_process_warning
716 );
717
718 END IF; /* lv_work_location_changed = 'Y' */
719
720 END IF; /* US_SIMULATION_REGION1 IF */
721
722 IF lct_region2.aei_information_category = 'US_SIMULATION_REGION2' AND
723 lct_default_region2.aei_information_category = 'DEFAULT_US_SIMULATION_REGION2'
724 THEN
725
726 /*Determine if there are any changes to Federal Tax Record Details */
727
728 /*Details of Federal Tax Information in US_SIMULATION_REGION2
729
730 AEI_INFORMATION1 : Federal Filing Status
731 AEI_INFORMATION2 : Federal Tax Exempt ?
732 AEI_INFORMATION3 : Federal Withholding Allowances
733 AEI_INFORMATION4 : Additional Federal Withholding Amount
734 AEI_INFORMATION5 : Federal Withholding Override Rate
735 AEI_INFORMATION6 : Federal Withholding Override Amount
736 AEI_INFORMATION7 : Federal Withholding Override Supplemental Rate
737
738 */
739
740 IF value_changed(lct_region2.aei_information1,
741 lct_default_region2.aei_information1) OR
742 value_changed(lct_region2.aei_information2,
743 lct_default_region2.aei_information2) OR
744 value_changed(lct_region2.aei_information3,
745 lct_default_region2.aei_information3) OR
746 value_changed(lct_region2.aei_information4,
747 lct_default_region2.aei_information4) OR
748 value_changed(lct_region2.aei_information5,
749 lct_default_region2.aei_information5) OR
750 value_changed(lct_region2.aei_information6,
751 lct_default_region2.aei_information6) OR
752 value_changed(lct_region2.aei_information7,
753 lct_default_region2.aei_information7) THEN
754
755 lv_federal_tax_changed := 'Y';
756
757 END IF;
758
759 IF lv_rs_state_changed = 'Y' THEN
760
761 /* If the Resident State has changed, the tax record for the new state
762 will be created by Update Assignment Code. The details selected on
763 Simulation Page are to be saved to the corresponding tax record */
764
765 lv_rs_state_tax_changed := 'Y';
766
767 ELSE
768
769 /*Determine if there are any changes to Resident State Tax Record Details */
770
771 /*Details of Resident State Tax Information in US_SIMULATION_REGION2
772
773 AEI_INFORMATION8 : Resident State Filing Status
774 AEI_INFORMATION9 : Resident State Tax Exempt ?
775 AEI_INFORMATION10 : Resident State Withholding Allowances
776 AEI_INFORMATION11 : Additional Resident State Withholding Amount
777 AEI_INFORMATION12 : Resident State Withholding Override Rate
778 AEI_INFORMATION13 : Resident State Withholding Override Amount
779 AEI_INFORMATION14 : Resident State Withholding Override Supplemental Rate
780
781 */
782
783 IF value_changed(lct_region2.aei_information8,
784 lct_default_region2.aei_information8) OR
785 value_changed(lct_region2.aei_information9,
786 lct_default_region2.aei_information9) OR
787 value_changed(lct_region2.aei_information10,
788 lct_default_region2.aei_information10) OR
789 value_changed(lct_region2.aei_information11,
790 lct_default_region2.aei_information11) OR
791 value_changed(lct_region2.aei_information12,
792 lct_default_region2.aei_information12) OR
793 value_changed(lct_region2.aei_information13,
794 lct_default_region2.aei_information13) OR
795 value_changed(lct_region2.aei_information14,
796 lct_default_region2.aei_information14) THEN
797
798 lv_rs_state_tax_changed := 'Y';
799
800 END IF;
801
802 END IF; /* lv_rs_state_changed = 'Y' IF */
803
804 SELECT pus.state_abbrev
805 INTO lv_resident_state
806 FROM pay_us_states pus
807 WHERE pus.state_code = lct_region1.aei_information1;
808
809 OPEN get_location_state(lct_region1.aei_information4);
810 FETCH get_location_state INTO lv_work_state;
811 CLOSE get_location_state;
812
813 IF lv_resident_state = lv_work_state THEN
814
815 lv_same_rs_wk_state := 'Y';
816
817 END IF;
818
819 /* If Resident and Work State are the same, then Resident Tax Record
820 update will be sufficient. Work Tax Record update can be skipped as
821 we have same tax record for both Resident and Work States*/
822
823 IF lv_same_rs_wk_state = 'N' THEN
824
825 IF lv_wk_state_changed = 'Y' THEN
826
827 lv_wk_state_tax_changed := 'Y';
828
829 ELSE
830
831 /*Determine if there are any changes to Work State Tax Record Details */
832
833 /*Details of Work State Tax Information in US_SIMULATION_REGION2
834
835 AEI_INFORMATION15 : Work State Filing Status
836 AEI_INFORMATION16 : Work State Tax Exempt ?
837 AEI_INFORMATION17 : Work State Withholding Allowances
838 AEI_INFORMATION18 : Additional Work State Withholding Amount
839 AEI_INFORMATION19 : Work State Withholding Override Rate
840 AEI_INFORMATION20 : Work State Withholding Override Amount
841 AEI_INFORMATION21 : Work State Withholding Override Supplemental Rate
842
843 */
844
845 IF value_changed(lct_region2.aei_information15,
846 lct_default_region2.aei_information15) OR
847 value_changed(lct_region2.aei_information16,
848 lct_default_region2.aei_information16) OR
849 value_changed(lct_region2.aei_information17,
850 lct_default_region2.aei_information17) OR
851 value_changed(lct_region2.aei_information18,
852 lct_default_region2.aei_information18) OR
853 value_changed(lct_region2.aei_information19,
854 lct_default_region2.aei_information19) OR
855 value_changed(lct_region2.aei_information20,
856 lct_default_region2.aei_information20) OR
857 value_changed(lct_region2.aei_information21,
858 lct_default_region2.aei_information21) THEN
859
860 lv_wk_state_tax_changed := 'Y';
861
862 END IF;
863
864 END IF; /* lv_wk_state_changed = 'Y' */
865
866 END IF; /* lv_same_rs_wk_state = 'N' */
867
868 IF lv_federal_tax_changed = 'Y' THEN
869
870 OPEN get_federal_tax_details(ln_assignment_id,ld_effective_date);
871 FETCH get_federal_tax_details INTO ln_emp_fed_tax_rule_id,
872 ln_ft_object_version_number;
873 CLOSE get_federal_tax_details;
874
875 pay_federal_tax_rule_api.update_fed_tax_rule
876 (p_effective_date => ld_effective_date
877 ,p_datetrack_update_mode => lv_datetrack_update_mode
878 ,p_emp_fed_tax_rule_id => ln_emp_fed_tax_rule_id
879 ,p_object_version_number => ln_ft_object_version_number
880 ,p_filing_status_code => lct_region2.aei_information1
881 ,p_fit_override_amount => NVL(fnd_number.canonical_to_number(lct_region2.aei_information6),0)
882 ,p_fit_override_rate => NVL(fnd_number.canonical_to_number(lct_region2.aei_information5),0)
883 ,p_withholding_allowances => NVL(fnd_number.canonical_to_number(lct_region2.aei_information3),0)
884 ,p_fit_additional_tax => NVL(fnd_number.canonical_to_number(lct_region2.aei_information4),0)
885 ,p_fit_exempt => lct_region2.aei_information2
886 ,p_supp_tax_override_rate => NVL(fnd_number.canonical_to_number(lct_region2.aei_information7),0)
887 ,p_effective_start_date => ld_effective_start_date
888 ,p_effective_end_date => ld_effective_end_date
889 );
890
891 END IF; /* lv_federal_tax_changed = 'Y' IF */
892
893 IF lv_rs_state_tax_changed = 'Y' THEN
894
895 OPEN get_state_tax_details(ln_assignment_id,ld_effective_date,lv_resident_state);
896 FETCH get_state_tax_details INTO ln_emp_state_tax_rule_id,
897 ln_st_object_version_number;
898 CLOSE get_state_tax_details;
899
900 pay_state_tax_rule_api.update_state_tax_rule
901 (p_effective_date => ld_effective_date
902 ,p_datetrack_update_mode => lv_datetrack_update_mode
903 ,p_emp_state_tax_rule_id => ln_emp_state_tax_rule_id
904 ,p_object_version_number => ln_st_object_version_number
905 ,p_filing_status_code => LPAD(lct_region2.aei_information8,2,'0')
906 ,p_sit_additional_tax => NVL(fnd_number.canonical_to_number(lct_region2.aei_information11),0)
907 ,p_sit_override_amount => NVL(fnd_number.canonical_to_number(lct_region2.aei_information13),0)
908 ,p_sit_override_rate => NVL(fnd_number.canonical_to_number(lct_region2.aei_information12),0)
909 ,p_withholding_allowances => NVL(fnd_number.canonical_to_number(lct_region2.aei_information10),0)
910 ,p_sit_exempt => lct_region2.aei_information9
911 ,p_supp_tax_override_rate => NVL(fnd_number.canonical_to_number(lct_region2.aei_information14),0)
912 ,p_effective_start_date => ld_effective_start_date
913 ,p_effective_end_date => ld_effective_end_date
914 );
915
916 END IF; /* lv_rs_state_tax_changed = 'Y' IF */
917
918 IF lv_wk_state_tax_changed = 'Y' THEN
919
920 OPEN get_state_tax_details(ln_assignment_id,ld_effective_date,lv_work_state);
921 FETCH get_state_tax_details INTO ln_emp_state_tax_rule_id,
922 ln_st_object_version_number;
923 CLOSE get_state_tax_details;
924
925 pay_state_tax_rule_api.update_state_tax_rule
926 (p_effective_date => ld_effective_date
927 ,p_datetrack_update_mode => lv_datetrack_update_mode
928 ,p_emp_state_tax_rule_id => ln_emp_state_tax_rule_id
929 ,p_object_version_number => ln_st_object_version_number
930 ,p_filing_status_code => LPAD(lct_region2.aei_information15,2,'0')
931 ,p_sit_additional_tax => NVL(fnd_number.canonical_to_number(lct_region2.aei_information18),0)
932 ,p_sit_override_amount => NVL(fnd_number.canonical_to_number(lct_region2.aei_information20),0)
933 ,p_sit_override_rate => NVL(fnd_number.canonical_to_number(lct_region2.aei_information19),0)
934 ,p_withholding_allowances => NVL(fnd_number.canonical_to_number(lct_region2.aei_information17),0)
935 ,p_sit_exempt => lct_region2.aei_information16
936 ,p_supp_tax_override_rate => NVL(fnd_number.canonical_to_number(lct_region2.aei_information21),0)
937 ,p_effective_start_date => ld_effective_start_date
938 ,p_effective_end_date => ld_effective_end_date
939 );
940
941 END IF; /* lv_wk_state_tax_changed = 'Y' IF */
942
943 END IF; /* US_SIMULATION_REGION2 IF */
944
945 ELSE
946
947 /* Place holder for other Localizations */
948 NULL;
949
950 END IF; /* lv_legislation_code IF */
951
952 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
953
954 END;
955
956 /* Procedure : get_defined_balance_id
957 Purpose : This procedure is to retrieve the Defined Balance ID
958 corresponding to a Balance and Dimension Combination.
959 This is a Generic function that can be used by any
960 Localization and Defined Balance ID can be retrieved
961 using Balance Type ID or Balance Name. Also the Balance
962 Dimension Parameter used here corresponds to the
963 DATABASE_ITEM_SUFFIX column of PAY_BALANCE_DIMENSIONS
964 table.
965 Important :
966 */
967
968 FUNCTION get_defined_balance_id(
969 p_legislation_code VARCHAR2,
970 p_business_group_id NUMBER,
971 p_balance_name VARCHAR2,
972 p_balance_type_id NUMBER,
973 p_balance_dimension VARCHAR2)
974 RETURN NUMBER
975
976 IS
977
978 CURSOR c_get_defined_balance_id (
979 cp_legislation_code VARCHAR2,
980 cp_business_group_id NUMBER,
981 cp_balance_name VARCHAR2,
982 cp_balance_type_id VARCHAR2,
983 cp_balance_dimension VARCHAR2 )
984 IS
985 SELECT pdb.defined_balance_id
986 FROM pay_defined_balances pdb,
987 pay_balance_dimensions pbd,
988 pay_balance_types pbt
989 WHERE pbt.balance_name = NVL(cp_balance_name,pbt.balance_name)
990 AND pbt.balance_type_id = NVL(cp_balance_type_id,pbt.balance_type_id)
991 AND pbd.database_item_suffix= cp_balance_dimension
992 AND pbt.balance_type_id = pdb.balance_type_id
993 AND pbd.balance_dimension_id = pdb.balance_dimension_id
994 AND ((pbt.legislation_code = cp_legislation_code and
995 pbt.business_group_id IS NULL)
996 OR (pbt.legislation_code IS NULL AND
997 pbt.business_group_id = cp_business_group_id))
998 AND ((pdb.legislation_code = cp_legislation_code AND
999 pdb.business_group_id IS NULL)
1000 OR (pdb.legislation_code IS NULL AND
1001 pdb.business_group_id = cp_business_group_id));
1002
1003 lv_procedure_name VARCHAR2(100) := '.get_defined_balance_id';
1004
1005 ln_defined_balance_id NUMBER := NULL;
1006
1007 BEGIN
1008
1009 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
1010
1011 OPEN c_get_defined_balance_id(p_legislation_code,
1012 p_business_group_id,
1013 p_balance_name,
1014 p_balance_type_id,
1015 p_balance_dimension);
1016
1017 FETCH c_get_defined_balance_id INTO ln_defined_balance_id;
1018
1019 IF c_get_defined_balance_id%NOTFOUND THEN
1020
1021 hr_utility.trace('Defined balance Id not found');
1022
1023 END IF;
1024
1025 CLOSE c_get_defined_balance_id;
1026
1027 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
1028 hr_utility.trace('Return Value : '||ln_defined_balance_id);
1029
1030 RETURN (ln_defined_balance_id);
1031
1032 END get_defined_balance_id;
1033
1034 /* Procedure : get_defined_balance_id
1035 Purpose : Overloaded version of get_defined_balance_id Procedure to
1036 facilitate procedure calling with Balance Name.
1037 Important :
1038 */
1039
1040 FUNCTION get_defined_balance_id(
1041 p_legislation_code VARCHAR2,
1042 p_business_group_id NUMBER,
1043 p_balance_name VARCHAR2,
1044 p_balance_dimension VARCHAR2)
1045 RETURN NUMBER
1046
1047 IS
1048
1049 BEGIN
1050
1051 RETURN get_defined_balance_id(p_legislation_code,
1052 p_business_group_id,
1053 p_balance_name,
1054 NULL,
1055 p_balance_dimension);
1056
1057 END get_defined_balance_id;
1058
1059 /* Procedure : get_defined_balance_id
1060 Purpose : Overloaded version of get_defined_balance_id Procedure to
1061 facilitate procedure calling with Balance Type ID.
1062 Important :
1063 */
1064
1065 FUNCTION get_defined_balance_id(
1066 p_legislation_code VARCHAR2,
1067 p_business_group_id NUMBER,
1068 p_balance_type_id NUMBER,
1069 p_balance_dimension VARCHAR2)
1070 RETURN NUMBER
1071
1072 IS
1073
1074 BEGIN
1075
1076 RETURN get_defined_balance_id(p_legislation_code,
1077 p_business_group_id,
1078 NULL,
1079 p_balance_type_id,
1080 p_balance_dimension);
1081
1082 END get_defined_balance_id;
1083
1084 /* Procedure : gross_earnings
1085 Purpose : This procedure is used to retrieve the Gross Earnings
1086 Balance corresponding to a particular Assignment Action.
1087 For US, CA and MX Localizations, we have 'Gross Earnings'
1088 Balance. The main objective of this procedure is to see if
1089 non-zero Gross Earnings are present before we process the
1090 data for archival. Other Localizations which do not have
1091 "Gross Earnings" can use their preferred balance which
1092 they can use to decide if archival needs to be done or can
1093 be skipped.
1094 Important :
1095 */
1096
1097 FUNCTION gross_earnings (p_assignment_action_id NUMBER,
1098 p_legislation_code VARCHAR2,
1099 p_gross_balance VARCHAR2,
1100 p_run_dimension_suffix VARCHAR2)
1101 RETURN NUMBER
1102
1103 IS
1104
1105 lv_procedure_name VARCHAR2(100) := '.gross_earnings';
1106
1107 ln_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
1108 ln_gross_earnings NUMBER := 0;
1109 lv_balance_name pay_balance_types.balance_name%TYPE := 'Gross Earnings';
1110 lv_dim_dbi_suffix pay_balance_dimensions.database_item_suffix%TYPE;
1111
1112 BEGIN
1113
1114 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
1115
1116 /*For US,MX and CA Localizations we have the "Gross Earnings" Balance
1117 and "_ASG_RUN" Dimension*/
1118
1119 lv_balance_name := p_gross_balance;
1120 lv_dim_dbi_suffix := p_run_dimension_suffix;
1121
1122 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1123
1124 ln_defined_balance_id :=
1125 get_defined_balance_id(p_legislation_code,
1126 NULL,
1127 lv_balance_name,
1128 lv_dim_dbi_suffix);
1129
1130 hr_utility.set_location(gv_package || lv_procedure_name, 20);
1131
1132 IF ln_defined_balance_id IS NOT NULL THEN
1133
1134 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1135
1136 ln_gross_earnings := pay_balance_pkg.get_value(ln_defined_balance_id,
1137 p_assignment_action_id);
1138
1139 END IF;
1140
1141 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
1142 hr_utility.trace('Return Value : '||ln_gross_earnings);
1143
1144 RETURN ln_gross_earnings;
1145
1146 END gross_earnings;
1147
1148 /* Procedure : get_emp_resident_jd
1149 Purpose : This procedure is used to determine the Jurisdiction Code
1150 corresponding to Employee Residence. This is used to
1151 retrieve the Tax Withholding Information of Employee
1152 Important :
1153 */
1154
1155 PROCEDURE get_emp_resident_jd(p_assignment_id IN NUMBER,
1156 p_effective_date IN DATE,
1157 p_jurisdiction_code OUT NOCOPY VARCHAR2)
1158
1159 IS
1160
1161 lv_procedure_name VARCHAR2(100) := '.get_emp_resident_jd';
1162
1163 /* Below cursor looks for Taxation Override Address first. If no
1164 override Adress is present, then the Address will be used. */
1165
1166 CURSOR c_get_emp_res_dtls(cp_assignment_id NUMBER,
1167 cp_effective_date DATE)
1168 IS
1169 SELECT NVL(addr.add_information17,addr.region_2) state_abbrev,
1170 NVL(addr.add_information19,addr.region_1) county_name,
1171 NVL(addr.add_information18,addr.town_or_city) city_name
1172 from per_addresses addr,
1173 per_all_assignments_f asg
1174 WHERE cp_effective_date BETWEEN asg.effective_start_date
1175 AND asg.effective_end_date
1176 AND asg.assignment_id = cp_assignment_id
1177 AND addr.person_id = asg.person_id
1178 AND addr.primary_flag = 'Y'
1179 AND cp_effective_date BETWEEN addr.date_from
1180 AND NVL(addr.date_to,TO_DATE('31/12/4712', 'DD/MM/YYYY'));
1181
1182 /* Below cursor determines the Jurisdiction Code in the format
1183 SS-COU-CITY based on the Address Details where SS Stands for State
1184 Code, COU Stands for County Code and CITY Stands for City Code. */
1185
1186 CURSOR c_get_emp_res_jd(cp_state_abbrev VARCHAR2,
1187 cp_county_name VARCHAR2,
1188 cp_city_name VARCHAR2) IS
1189 SELECT pcn.state_code||'-'||pcn.county_code||'-'||pcn.city_code
1190 FROM pay_us_states pus,
1191 pay_us_counties puc,
1192 pay_us_city_names pcn
1193 WHERE pus.state_abbrev = cp_state_abbrev
1194 AND puc.state_code = pus.state_code
1195 AND puc.county_name = cp_county_name
1196 AND pcn.state_code = puc.state_code
1197 AND pcn.county_code = puc.county_code
1198 AND pcn.city_name = cp_city_name;
1199
1200 l_emp_res_dtls_rec c_get_emp_res_dtls%ROWTYPE;
1201
1202 BEGIN
1203
1204 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
1205
1206 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1207
1208 OPEN c_get_emp_res_dtls(p_assignment_id,p_effective_date);
1209 FETCH c_get_emp_res_dtls INTO l_emp_res_dtls_rec;
1210 CLOSE c_get_emp_res_dtls;
1211
1212 hr_utility.set_location(gv_package || lv_procedure_name, 20);
1213
1214 OPEN c_get_emp_res_jd(l_emp_res_dtls_rec.state_abbrev,
1215 l_emp_res_dtls_rec.county_name,
1216 l_emp_res_dtls_rec.city_name);
1217 FETCH c_get_emp_res_jd INTO p_jurisdiction_code;
1218 CLOSE c_get_emp_res_jd;
1219
1220 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
1221 hr_utility.trace('Return Value : '||p_jurisdiction_code);
1222
1223 END get_emp_resident_jd;
1224
1225 /* Procedure : get_personal_information
1226 Purpose : This procedure is used to retrieve the Employee
1227 Information. Please note that most of this information
1228 remains static during Payroll Simulator Run. This is
1229 provided only for informative purpose and also to make
1230 sure that we have same user experience with Simulation
1231 Payslip as we have with Regular Payslip.
1232 Important : This procedure borrows most of the Code from the Parent
1233 Payroll Archive Package PAY_EMP_ACTION_ARCH. If anything
1234 needs to be archived in addition to or different from the
1235 current data, then it is highly necessary to create own
1236 procedures. It is mostly desired not to alter the Regular
1237 Archiver Code for Payroll Simulation purpose.
1238 */
1239
1240 PROCEDURE get_personal_information(
1241 p_assignment_action_id NUMBER
1242 ,p_assignment_id NUMBER
1243 ,p_effective_date DATE
1244 ,p_date_earned DATE
1245 ,p_tax_unit_id NUMBER
1246 ,p_time_period_id NUMBER
1247 ,p_ytd_balcall_aaid NUMBER DEFAULT NULL
1248 )
1249 IS
1250
1251 CURSOR c_employee_details(cp_assignment_id NUMBER
1252 ,cp_effective_date DATE
1253 ,cp_date_earned DATE
1254 )
1255 IS
1256 SELECT ppf.full_name,
1257 ppf.national_identifier,
1258 ppf.person_id,
1259 pps.date_start,
1260 ppf.employee_number,
1261 ppf.original_date_of_hire,
1262 pps.adjusted_svc_date,
1263 paf.assignment_number,
1264 paf.location_id,
1265 paf.organization_id,
1266 paf.job_id,
1267 paf.position_id,
1268 paf.pay_basis_id,
1269 paf.frequency,
1270 paf.grade_id,
1271 paf.bargaining_unit_code,
1272 paf.collective_agreement_id,
1273 paf.contract_id,
1274 paf.special_ceiling_step_id,
1275 paf.people_group_id,
1276 paf.normal_hours,
1277 paf.time_normal_start,
1278 paf.time_normal_finish,
1279 paf.business_group_id,
1280 paf.soft_coding_keyflex_id,
1281 paf.hourly_salaried_code
1282 FROM per_assignments_f paf,
1283 per_all_people_f ppf,
1284 per_all_people_f ppf1,
1285 per_periods_of_service pps
1286 WHERE paf.person_id = ppf.person_id
1287 AND paf.assignment_id = cp_assignment_id
1288 AND ppf1.person_id = ppf.person_id
1289 AND ((ppf1.current_employee_flag = 'Y'
1290 AND cp_date_earned BETWEEN paf.effective_start_date
1291 AND paf.effective_end_date)
1292 OR
1293 (
1294 ppf1.current_employee_flag <> 'Y'
1295 AND cp_effective_date BETWEEN paf.effective_start_date
1296 AND paf.effective_end_date))
1297 AND cp_date_earned BETWEEN ppf.effective_start_date
1298 AND ppf.effective_end_date
1299 AND pps.person_id = ppf.person_id
1300 AND pps.date_start = (SELECT MAX(pps1.date_start)
1301 FROM per_periods_of_service pps1
1302 WHERE pps1.person_id = paf.person_id
1303 AND pps1.date_start <= cp_date_earned);
1304
1305 CURSOR c_period_details (cp_time_period_id NUMBER)
1306 IS
1307 SELECT payroll_id, period_type, start_date, cut_off_date
1308 FROM per_time_periods
1309 WHERE time_period_id = cp_time_period_id;
1310
1311 cursor c_step (cp_sp_ceil_step_id in number,
1312 cp_effective_date in date) is
1313 select count(*)
1314 from per_spinal_points psp,
1315 per_spinal_points psp2,
1316 per_spinal_point_steps_f psps,
1317 per_spinal_point_steps_f psps2
1318 where psps.step_id = cp_sp_ceil_step_id
1319 and psp.spinal_point_id = psps.spinal_point_id
1320 and psps.grade_spine_id = psps2.grade_spine_id
1321 and psp2.spinal_point_id = psps2.spinal_point_id
1322 and psp.sequence >= psp2.sequence
1323 and cp_effective_date between psps.effective_start_date
1324 and psps.effective_end_date
1325 and cp_effective_date between psps2.effective_start_date
1326 and psps2.effective_end_date
1327 group by psp.spinal_point,
1328 psps.step_id,
1329 psps.sequence,
1330 psps.effective_start_date,
1331 psps.effective_end_date;
1332
1333 CURSOR er_phone_number(cp_organization_id in number) IS
1334 select telephone_number_1
1335 from hr_locations hl,
1336 hr_organization_units hou
1337 where hou.organization_id = cp_organization_id
1338 and hou.location_id = hl.location_id;
1339
1340 CURSOR get_legislation_code(cp_business_group_id NUMBER,
1341 cp_effective_date DATE)
1342 IS
1343 SELECT pbg.legislation_code
1344 FROM per_business_groups pbg
1345 WHERE pbg.business_group_id = cp_business_group_id
1346 AND cp_effective_date BETWEEN pbg.date_from
1347 AND NVL(pbg.date_to,TO_DATE('31/12/4712','DD/MM/YYYY'));
1348
1349 CURSOR c_get_employee_name(cp_person_id NUMBER,
1350 cp_effective_date DATE)
1351 IS
1352 SELECT LTRIM(RTRIM(
1353 first_name || ' ' ||
1354 DECODE(NVL(LENGTH(LTRIM(RTRIM(middle_names))),0), 0, NULL,
1355 UPPER(SUBSTR(middle_names,1,1)) || '. ' ) ||
1356 pre_name_adjunct || last_name || ' '|| suffix))
1357 FROM per_all_people_f ppf
1358 WHERE ppf.person_id = cp_person_id
1359 AND cp_effective_date between ppf.effective_start_date
1360 and ppf.effective_end_date;
1361
1362 lv_procedure_name VARCHAR2(100) := '.get_personal_information';
1363
1364 lv_full_name VARCHAR2(300);
1365 lv_employee_name VARCHAR2(300);
1366 lv_national_identifier VARCHAR2(100);
1367 ln_person_id NUMBER;
1368 ln_index NUMBER;
1369 ld_date_start DATE;
1370 lv_employee_number VARCHAR2(50);
1371 ld_original_date_of_hire DATE;
1372 ld_adjusted_svc_date DATE;
1373 lv_assignment_number VARCHAR2(50);
1374 ln_location_id NUMBER;
1375 lv_location_code VARCHAR2(240);
1376 ln_organization_id NUMBER;
1377 ln_job_id NUMBER;
1378 ln_pay_basis_id NUMBER;
1379 lv_frequency VARCHAR2(30);
1380 ln_grade_id NUMBER;
1381 lv_bargaining_unit_code VARCHAR2(80);
1382 ln_collective_agreement_id NUMBER(9);
1383 ln_contract_id NUMBER;
1384 ln_special_ceiling_step_id NUMBER;
1385 ln_people_group_id NUMBER;
1386 ln_normal_hours NUMBER(22,3);
1387 lv_time_normal_start VARCHAR2(5) :=null;
1388 lv_time_normal_finish VARCHAR2(5) :=null;
1389 ln_position_id NUMBER;
1390 lv_position_name VARCHAR2(240) :=null;
1391 ln_soft_coding_keyflex_id NUMBER;
1392 lv_gre_name VARCHAR2(240) :=null;
1393 lv_er_phone_number VARCHAR2(240) :=null;
1394 ln_business_group_id NUMBER;
1395 lv_organization_name VARCHAR2(240) :=null;
1396 lv_job_name VARCHAR2(240) :=null;
1397 lv_pay_basis VARCHAR2(240) :=null;
1398 lv_frequency_desc VARCHAR2(240) :=null;
1399 lv_grade VARCHAR2(240);
1400 lv_bargaining_unit VARCHAR2(240);
1401 lv_collective_agreement VARCHAR2(240);
1402 lv_contract VARCHAR2(240);
1403 lv_progression_point VARCHAR2(240);
1404 lv_step VARCHAR2(240);
1405 lv_pay_calc_method VARCHAR2(240);
1406 lv_shift_desc VARCHAR2(240);
1407 lv_hourly_salaried_code VARCHAR2(240);
1408 lv_hourly_salaried_desc VARCHAR2(240);
1409
1410 ln_payroll_id NUMBER;
1411 lv_period_type VARCHAR2(240);
1412 ld_period_start_date DATE;
1413 ld_period_end_date DATE;
1414
1415 ln_proposed_salary NUMBER(20,5);
1416 ln_pay_annualization_factor NUMBER(20,5);
1417
1418 lv_legislation_code per_business_groups.legislation_code%TYPE;
1419
1420 lv_exists VARCHAR2(1);
1421 ln_index1 number;
1422
1423
1424 BEGIN
1425
1426 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
1427
1428 lv_exists := 'N';
1429
1430 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1431
1432 pay_emp_action_arch.initialization_process;
1433
1434 hr_utility.set_location(gv_package || lv_procedure_name, 20);
1435
1436 OPEN c_employee_details(p_assignment_id,p_effective_date,p_date_earned);
1437
1438 FETCH c_employee_details INTO lv_full_name,
1439 lv_national_identifier,
1440 ln_person_id,
1441 ld_date_start,
1442 lv_employee_number,
1443 ld_original_date_of_hire,
1444 ld_adjusted_svc_date,
1445 lv_assignment_number,
1446 ln_location_id,
1447 ln_organization_id,
1448 ln_job_id,
1449 ln_position_id,
1450 ln_pay_basis_id,
1451 lv_frequency,
1452 ln_grade_id,
1453 lv_bargaining_unit_code,
1454 ln_collective_agreement_id,
1455 ln_contract_id,
1456 ln_special_ceiling_step_id,
1457 ln_people_group_id,
1458 ln_normal_hours,
1459 lv_time_normal_start,
1460 lv_time_normal_finish,
1461 ln_business_group_id,
1462 ln_soft_coding_keyflex_id,
1463 lv_hourly_salaried_code;
1464
1465 CLOSE c_employee_details;
1466
1467 OPEN c_period_details(p_time_period_id);
1468 FETCH c_period_details INTO ln_payroll_id,
1469 lv_period_type,
1470 ld_period_start_date,
1471 ld_period_end_date;
1472 CLOSE c_period_details;
1473
1474 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1475
1476 lv_gre_name := pay_emp_action_arch.get_organization_name(p_tax_unit_id);
1477 lv_organization_name := pay_emp_action_arch.get_organization_name(ln_organization_id);
1478
1479 hr_utility.set_location(gv_package || lv_procedure_name, 40);
1480
1481 IF ln_job_id IS NOT NULL THEN
1482 lv_job_name := pay_emp_action_arch.get_job_name(ln_job_id
1483 ,p_effective_date);
1484 END IF ;
1485
1486 hr_utility.set_location(gv_package || lv_procedure_name, 50);
1487
1488 IF ln_position_id IS NOT NULL THEN
1489 lv_position_name := pay_emp_action_arch.get_position(ln_position_id
1490 ,p_effective_date);
1491 END IF;
1492
1493 hr_utility.set_location(gv_package || lv_procedure_name, 60);
1494
1495 IF ln_pay_basis_id IS NOT NULL THEN
1496 lv_pay_basis := pay_emp_action_arch.get_pay_basis(ln_pay_basis_id
1497 ,p_effective_date);
1498 END IF;
1499
1500 hr_utility.set_location(gv_package || lv_procedure_name, 70);
1501
1502 IF ln_location_id IS NOT NULL THEN
1503 lv_location_code := pay_emp_action_arch.get_location(ln_location_id);
1504 END IF;
1505
1506 hr_utility.set_location(gv_package || lv_procedure_name, 80);
1507
1508 ln_proposed_salary := pay_emp_action_arch.get_proposed_emp_salary(p_assignment_id
1509 ,ln_pay_basis_id
1510 ,lv_pay_basis
1511 ,p_date_earned);
1512
1513 hr_utility.set_location(gv_package || lv_procedure_name, 90);
1514
1515 ln_pay_annualization_factor := pay_emp_action_arch.get_emp_annualization_factor(
1516 ln_pay_basis_id
1517 ,lv_period_type
1518 ,lv_pay_basis
1519 ,p_assignment_id
1520 ,p_date_earned);
1521
1522 hr_utility.set_location(gv_package || lv_procedure_name, 100);
1523
1524 IF lv_frequency IS NOT NULL THEN
1525 lv_frequency_desc := pay_emp_action_arch.get_frequency(lv_frequency
1526 ,p_effective_date);
1527 END IF;
1528
1529 hr_utility.set_location(gv_package || lv_procedure_name, 110);
1530
1531 IF ln_grade_id IS NOT NULL THEN
1532 lv_grade := pay_emp_action_arch.get_grade(ln_grade_id
1533 ,p_effective_date);
1534 END IF;
1535
1536 hr_utility.set_location(gv_package || lv_procedure_name, 120);
1537
1538 IF lv_bargaining_unit_code IS NOT NULL THEN
1539 lv_bargaining_unit := pay_emp_action_arch.get_bargaining_unit(lv_bargaining_unit_code
1540 ,p_effective_date);
1541 END IF;
1542
1543 hr_utility.set_location(gv_package || lv_procedure_name, 130);
1544
1545 IF ln_collective_agreement_id IS NOT NULL THEN
1546 lv_collective_agreement := pay_emp_action_arch.get_collective_agreement(
1547 ln_collective_agreement_id
1548 ,p_effective_date
1549 );
1550 END IF;
1551
1552 hr_utility.set_location(gv_package || lv_procedure_name, 140);
1553
1554 IF ln_contract_id IS NOT NULL THEN
1555 lv_contract := pay_emp_action_arch.get_contract(ln_contract_id
1556 ,p_effective_date) ;
1557 END IF;
1558
1559 hr_utility.set_location(gv_package || lv_procedure_name, 150);
1560
1561 IF lv_hourly_salaried_code IS NOT NULL THEN
1562 lv_hourly_salaried_desc := pay_emp_action_arch.get_hourly_salaried_code(
1563 lv_hourly_salaried_code
1564 ,p_effective_date) ;
1565 END IF;
1566
1567 hr_utility.set_location(gv_package || lv_procedure_name, 160);
1568
1569 IF ln_soft_coding_keyflex_id IS NOT NULL THEN
1570 lv_shift_desc := pay_emp_action_arch.get_shift( ln_soft_coding_keyflex_id
1571 ,p_effective_date,ln_business_group_id) ;
1572 END IF;
1573
1574 hr_utility.set_location(gv_package || lv_procedure_name, 170);
1575
1576 OPEN er_phone_number(ln_organization_id);
1577 FETCH er_phone_number INTO lv_er_phone_number;
1578 CLOSE er_phone_number;
1579
1580 IF ln_special_ceiling_step_id IS NOT NULL THEN
1581 OPEN c_step(ln_special_ceiling_step_id, p_effective_date);
1582 FETCH c_step INTO lv_step;
1583 CLOSE c_step;
1584 END IF;
1585
1586 hr_utility.set_location(gv_package || lv_procedure_name, 180);
1587
1588 ln_index := pay_emp_action_arch.lrr_act_tab.count;
1589
1590 pay_emp_action_arch.lrr_act_tab(ln_index).action_info_category
1591 := 'EMPLOYEE DETAILS';
1592 pay_emp_action_arch.lrr_act_tab(ln_index).jurisdiction_code
1593 := '00-000-0000';
1594 pay_emp_action_arch.lrr_act_tab(ln_index).act_info1
1595 := lv_full_name;
1596
1597 OPEN get_legislation_code(ln_business_group_id,p_effective_date);
1598 FETCH get_legislation_code INTO lv_legislation_code;
1599 CLOSE get_legislation_code;
1600
1601 IF lv_legislation_code = 'US' THEN
1602
1603 OPEN c_get_employee_name(ln_person_id,p_effective_date);
1604 FETCH c_get_employee_name INTO lv_employee_name;
1605 CLOSE c_get_employee_name;
1606
1607 pay_emp_action_arch.lrr_act_tab(ln_index).act_info1
1608 := lv_employee_name;
1609
1610 END IF;
1611
1612 hr_utility.trace('lv_full_name is'||lv_full_name);
1613
1614 pay_emp_action_arch.lrr_act_tab(ln_index).act_info2
1615 := ln_organization_id;
1616 hr_utility.trace('ln_organization_id is'||ln_organization_id);
1617
1618 pay_emp_action_arch.lrr_act_tab(ln_index).act_info4
1619 := lv_national_identifier ;
1620
1621 hr_utility.trace('lv_national_identifier is'||lv_national_identifier);
1622
1623 pay_emp_action_arch.lrr_act_tab(ln_index).act_info5 := lv_pay_basis;
1624
1625 hr_utility.trace('lv_pay_basis is'||lv_pay_basis);
1626
1627 pay_emp_action_arch.lrr_act_tab(ln_index).act_info6
1628 := lv_frequency_desc;
1629
1630 hr_utility.trace('lv_frequency_desc is'||lv_frequency_desc);
1631
1632 pay_emp_action_arch.lrr_act_tab(ln_index).act_info7
1633 := lv_grade;
1634
1635 hr_utility.trace('lv_grade is'||lv_grade);
1636
1637 pay_emp_action_arch.lrr_act_tab(ln_index).act_info8
1638 := lv_bargaining_unit;
1639
1640 hr_utility.trace('lv_bargaining_unit is'||lv_bargaining_unit);
1641 pay_emp_action_arch.lrr_act_tab(ln_index).act_info9
1642 := lv_collective_agreement;
1643
1644 hr_utility.trace('lv_collective_agreement is'||lv_collective_agreement);
1645 pay_emp_action_arch.lrr_act_tab(ln_index).act_info10
1646 := lv_employee_number ;
1647
1648 pay_emp_action_arch.lrr_act_tab(ln_index).act_info11
1649 := fnd_date.date_to_canonical(ld_date_start);
1650
1651 pay_emp_action_arch.lrr_act_tab(ln_index).act_info12
1652 := fnd_date.date_to_canonical(ld_original_date_of_hire);
1653
1654 pay_emp_action_arch.lrr_act_tab(ln_index).act_info13
1655 := fnd_date.date_to_canonical(ld_adjusted_svc_date);
1656
1657 pay_emp_action_arch.lrr_act_tab(ln_index).act_info14
1658 := lv_assignment_number;
1659
1660 pay_emp_action_arch.lrr_act_tab(ln_index).act_info15
1661 := lv_organization_name;
1662 pay_emp_action_arch.lrr_act_tab(ln_index).act_info16
1663 := p_time_period_id;
1664 pay_emp_action_arch.lrr_act_tab(ln_index).act_info17
1665 := lv_job_name ;
1666 pay_emp_action_arch.lrr_act_tab(ln_index).act_info18
1667 := lv_gre_name;
1668 pay_emp_action_arch.lrr_act_tab(ln_index).act_info19
1669 := lv_position_name;
1670 pay_emp_action_arch.lrr_act_tab(ln_index).act_info20
1671 := lv_contract;
1672 pay_emp_action_arch.lrr_act_tab(ln_index).act_info21
1673 := lv_time_normal_start ;
1674 pay_emp_action_arch.lrr_act_tab(ln_index).act_info22
1675 := lv_time_normal_finish;
1676 pay_emp_action_arch.lrr_act_tab(ln_index).act_info23
1677 := lv_pay_calc_method;
1678 pay_emp_action_arch.lrr_act_tab(ln_index).act_info24
1679 := lv_shift_desc;
1680 pay_emp_action_arch.lrr_act_tab(ln_index).act_info25
1681 := lv_er_phone_number;
1682 pay_emp_action_arch.lrr_act_tab(ln_index).act_info26
1683 := lv_hourly_salaried_desc;
1684 pay_emp_action_arch.lrr_act_tab(ln_index).act_info27
1685 := lv_step ;
1686 pay_emp_action_arch.lrr_act_tab(ln_index).act_info28
1687 := fnd_number.number_to_canonical(ln_proposed_salary) ;
1688 pay_emp_action_arch.lrr_act_tab(ln_index).act_info29
1689 := fnd_number.number_to_canonical(ln_pay_annualization_factor) ;
1690 pay_emp_action_arch.lrr_act_tab(ln_index).act_info30
1691 := lv_location_code ;
1692
1693 hr_utility.set_location(gv_package || lv_procedure_name, 190);
1694
1695 /* Below Procedure is used to retrieve all the Organization (Or Business Group)
1696 Level Balances and Elements selected in "Payslip Information" Category and
1697 determine the corresponding values. Please note that as of now we are Archiving
1698 this data and XML also gets generated including this information. But the Payroll
1699 Simulator Output will not have these details. Any customer who needs this extra
1700 information can add the required sections in their template.*/
1701
1702 pay_emp_action_arch.get_employee_other_info(p_run_action_id => p_assignment_action_id
1703 ,p_assignment_id => p_assignment_id
1704 ,p_organization_id => ln_organization_id
1705 ,p_business_group_id => ln_business_group_id
1706 ,p_curr_pymt_eff_date => p_effective_date
1707 ,p_tax_unit_id => p_tax_unit_id
1708 ,p_ppp_source_action_id => p_assignment_action_id
1709 ,p_ytd_balcall_aaid => p_assignment_action_id
1710 ) ;
1711
1712 hr_utility.set_location(gv_package || lv_procedure_name, 200);
1713
1714 pay_emp_action_arch.get_employee_addr (ln_person_id
1715 ,p_effective_date);
1716
1717 hr_utility.set_location(gv_package || lv_procedure_name, 210);
1718
1719 IF pay_emp_action_arch.lrr_act_tab.count > 0 THEN
1720
1721 hr_utility.set_location(gv_package || lv_procedure_name, 220);
1722
1723 pay_simulator_pkg.insert_simulation_rows(
1724 p_action_context_id => p_assignment_action_id
1725 ,p_action_context_type => 'AAP'
1726 ,p_assignment_id => p_assignment_id
1727 ,p_tax_unit_id => p_tax_unit_id
1728 ,p_effective_date => p_effective_date
1729 ,p_tab_rec_data => pay_emp_action_arch.lrr_act_tab
1730 );
1731 END IF;
1732
1733 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
1734
1735 END get_personal_information;
1736
1737 /* Procedure : populate_elements
1738 Purpose : This procedure is used to determine the balance values
1739 related to each of the Elements retrieved by procedure
1740 get_current_elements. For each of the Element, the Primary
1741 Balance related information will be retrieved for the
1742 Current Run and YTD. If the Element has Hours Balance
1743 associated, corresponding hours information is also
1744 retrieved.
1745 Important :
1746 */
1747
1748 PROCEDURE populate_elements(p_source_action_id NUMBER
1749 ,p_effective_date DATE
1750 ,p_element_type_id NUMBER
1751 ,p_primary_balance_id NUMBER
1752 ,p_hours_balance_id NUMBER
1753 ,p_processing_priority NUMBER
1754 ,p_element_classification_name VARCHAR2
1755 ,p_reporting_name VARCHAR2
1756 ,p_tax_unit_id NUMBER
1757 ,p_business_group_id NUMBER
1758 ,p_ytd_balcall_aaid NUMBER
1759 ,p_jurisdiction_code VARCHAR2 DEFAULT NULL
1760 ,p_legislation_code VARCHAR2
1761 ,p_el_jurisdiction_code VARCHAR2 DEFAULT NULL
1762 )
1763
1764 IS
1765
1766 CURSOR c_hoursbyrate(cp_run_action_id NUMBER,
1767 cp_element_type_id NUMBER,
1768 cp_legislation_code VARCHAR2)
1769 IS
1770 SELECT NVL(mul.multiple,1),mul.rate
1771 FROM pay_hours_by_rate_v mul
1772 WHERE mul.assignment_action_id = cp_run_action_id
1773 AND legislation_code = cp_legislation_code
1774 AND mul.element_type_id >= 0
1775 AND mul.element_type_id = p_element_type_id
1776 ORDER BY mul.processing_priority,mul.element_type_id;
1777
1778 CURSOR get_display_name(cp_reporting_name VARCHAR2,
1779 cp_jurisdiction_code VARCHAR2)
1780 IS
1781 SELECT DECODE(LENGTH(cp_jurisdiction_code),
1782 11,DECODE(cp_jurisdiction_code,
1783 '00-000-0000', NULL,
1784 DECODE(cp_reporting_name,
1785 'Head Tax Withheld', NULL,
1786 pay_us_employee_payslip_web.get_jurisdiction_name(cp_jurisdiction_code) || ' ')),
1787 8, pay_us_employee_payslip_web.get_jurisdiction_name(
1788 SUBSTR(cp_jurisdiction_code,1,2)||'-000-0000') || ' ')||
1789 DECODE(fl.description,
1790 '', NULL,
1791 NVL(fl.description, cp_reporting_name)) || ' ' ||
1792 DECODE(LENGTH(cp_jurisdiction_code),
1793 8, DECODE(SUBSTR(cp_jurisdiction_code,1,2),
1794 '36', SUBSTR(cp_jurisdiction_code, 4),
1795 pay_us_employee_payslip_web.get_jurisdiction_name(cp_jurisdiction_code)),
1796 11, DECODE(cp_reporting_name,
1797 'Head Tax Withheld', pay_us_employee_payslip_web.get_jurisdiction_name(
1798 cp_jurisdiction_code))) display_name
1799 FROM fnd_common_lookups fl
1800 WHERE fl.lookup_type(+) = 'PAY_US_LABELS'
1801 AND upper(cp_reporting_name) = fl.lookup_code(+);
1802
1803 CURSOR get_simulation_name(cp_element_type_id NUMBER)
1804 IS
1805 SELECT SUBSTR(petei.eei_information18,1,80)
1806 FROM pay_element_type_extra_info petei
1807 WHERE petei.element_type_id = cp_element_type_id
1808 AND petei.eei_information18 IS NOT NULL;
1809
1810 lv_procedure_name VARCHAR2(100) := '.populate_elements';
1811
1812 ln_ytd_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
1813 ln_run_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
1814 ln_ptd_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
1815 ln_ytd_hours_balance_id pay_defined_balances.defined_balance_id%TYPE;
1816 ln_run_hours_balance_id pay_defined_balances.defined_balance_id%TYPE;
1817 ln_ptd_hours_balance_id pay_defined_balances.defined_balance_id%TYPE;
1818
1819 lv_rate_exists VARCHAR2(1) := 'N';
1820
1821 ln_rate NUMBER(15,5);
1822 ln_multiple NUMBER(15,5);
1823
1824 ln_ytd_amount NUMBER(20,2);
1825 ln_current_amount NUMBER(20,2);
1826 ln_ytd_hours NUMBER(20,2);
1827 ln_current_hours NUMBER(20,2);
1828
1829 ln_index NUMBER ;
1830 lv_action_category VARCHAR2(50) := 'AC DEDUCTIONS';
1831
1832 lv_display_name VARCHAR2(200);
1833 lv_simulation_name VARCHAR2(200);
1834
1835 BEGIN
1836
1837 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
1838
1839 IF pay_emp_action_arch.gv_multi_leg_rule IS NULL THEN
1840
1841 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1842
1843 pay_emp_action_arch.gv_multi_leg_rule
1844 := pay_emp_action_arch.get_multi_legislative_rule(
1845 p_legislation_code);
1846
1847 END IF;
1848
1849 hr_utility.set_location(gv_package || lv_procedure_name, 20);
1850
1851 pay_balance_pkg.set_context('TAX_UNIT_ID',p_tax_unit_id);
1852
1853 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1854
1855 IF p_jurisdiction_code <> '00-000-0000' THEN
1856
1857 hr_utility.set_location(gv_package || lv_procedure_name, 40);
1858
1859 pay_balance_pkg.set_context('JURISDICTION_CODE', p_jurisdiction_code);
1860 gv_ytd_balance_dimension := gv_dim_asg_jd_gre_ytd;
1861 gv_ptd_balance_dimension := gv_dim_asg_jd_gre_ptd;
1862
1863 ELSE
1864
1865 hr_utility.set_location(gv_package || lv_procedure_name, 50);
1866
1867 pay_balance_pkg.set_context('JURISDICTION_CODE', p_jurisdiction_code);
1868
1869 IF gv_reporting_level = 'TAXGRP' THEN
1870
1871 hr_utility.set_location(gv_package || lv_procedure_name, 60);
1872
1873 gv_ytd_balance_dimension := gv_dim_asg_tg_ytd;
1874 /* Review Defined Balances are not present as of now for TG as needed*/
1875 gv_ptd_balance_dimension := gv_dim_asg_tg_ptd;
1876
1877 ELSE
1878
1879 hr_utility.set_location(gv_package || lv_procedure_name, 70);
1880
1881 gv_ytd_balance_dimension := gv_dim_asg_gre_ytd;
1882 gv_ptd_balance_dimension := gv_dim_asg_gre_ptd;
1883
1884 END IF;
1885
1886 END IF;
1887
1888 hr_utility.set_location(gv_package || lv_procedure_name, 80);
1889
1890 /*Get YTD Defined Balance ID*/
1891
1892 ln_ytd_defined_balance_id
1893 := get_defined_balance_id(p_legislation_code,
1894 p_business_group_id,
1895 p_primary_balance_id,
1896 gv_ytd_balance_dimension);
1897
1898 hr_utility.set_location(gv_package || lv_procedure_name, 90);
1899
1900 ln_ptd_defined_balance_id
1901 := get_defined_balance_id(p_legislation_code,
1902 p_business_group_id,
1903 p_primary_balance_id,
1904 gv_ptd_balance_dimension);
1905
1906 IF p_hours_balance_id IS NOT NULL THEN
1907
1908 hr_utility.set_location(gv_package || lv_procedure_name, 100);
1909
1910 ln_ytd_hours_balance_id
1911 := get_defined_balance_id(p_legislation_code,
1912 p_business_group_id,
1913 p_hours_balance_id,
1914 gv_ytd_balance_dimension);
1915
1916 hr_utility.set_location(gv_package || lv_procedure_name, 110);
1917
1918 ln_ptd_hours_balance_id
1919 := get_defined_balance_id(p_legislation_code,
1920 p_business_group_id,
1921 p_hours_balance_id,
1922 gv_ptd_balance_dimension);
1923
1924 END IF;
1925
1926 hr_utility.set_location(gv_package || lv_procedure_name, 120);
1927
1928 /*Review
1929
1930 if p_legislation_code <> 'US' THEN
1931 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1932 ln_step := 6;
1933 check_hours_by_rate(
1934 p_xfr_action_id => p_xfr_action_id
1935 ,p_puv_assignment_action_id => p_pymt_assignment_action_id
1936 ,p_element_classification_name => p_element_classification_name
1937 ,p_reporting_name => p_reporting_name
1938 ,p_element_type_id => p_element_type_id
1939 ,p_primary_balance_id => p_primary_balance_id
1940 ,p_processing_priority => p_processing_priority
1941 ,p_tax_unit_id => p_tax_unit_id
1942 ,p_pymt_eff_date => p_pymt_eff_date
1943 ,p_ytd_balcall_aaid => p_ytd_balcall_aaid
1944 ,p_ytd_defined_balance_id => ln_ytd_defined_balance_id
1945 ,p_ytd_hours_balance_id => ln_ytd_hours_balance_id
1946 ,p_rate_exists => lv_rate_exists
1947 );
1948 end if;
1949
1950 */
1951
1952 IF lv_rate_exists = 'N' THEN
1953
1954 hr_utility.set_location(gv_package || lv_procedure_name, 130);
1955
1956 IF ln_ytd_defined_balance_id IS NOT NULL THEN
1957
1958 hr_utility.set_location(gv_package || lv_procedure_name, 140);
1959
1960 ln_ytd_amount := NVL(pay_balance_pkg.get_value(
1961 ln_ytd_defined_balance_id,
1962 p_ytd_balcall_aaid),0);
1963
1964 END IF;
1965
1966 hr_utility.set_location(gv_package || lv_procedure_name, 150);
1967
1968 IF ln_ptd_defined_balance_id IS NOT NULL THEN
1969
1970 hr_utility.set_location(gv_package || lv_procedure_name, 160);
1971
1972 ln_current_amount := NVL(pay_balance_pkg.get_value(
1973 ln_ptd_defined_balance_id,
1974 p_ytd_balcall_aaid),0);
1975
1976 ELSE
1977
1978 /*For Canada, the _ASG_TG_PTD dimension is not available. It is necessary
1979 to determine the necessary _ASG_TG_PTD value by summing up the value of
1980 _ASG_TG_RUN for all the Assignment actions associated to the current
1981 source Assignment action */
1982
1983 IF p_legislation_code = 'CA'
1984 AND gv_ptd_balance_dimension = gv_dim_asg_tg_ptd THEN
1985
1986 /* The below statement needs to be replaced with Canada specific function
1987 to calculate the current amount which can be called with p_source_action_id */
1988
1989 ln_current_amount := 0;
1990
1991 END IF;
1992
1993 END IF;
1994
1995 hr_utility.set_location(gv_package || lv_procedure_name, 170);
1996
1997 IF p_hours_balance_id IS NOT NULL THEN
1998
1999 hr_utility.set_location(gv_package || lv_procedure_name, 180);
2000
2001 IF ln_ytd_hours_balance_id IS NOT NULL THEN
2002
2003 hr_utility.set_location(gv_package || lv_procedure_name, 190);
2004
2005 ln_ytd_hours := NVL(pay_balance_pkg.get_value(
2006 ln_ytd_hours_balance_id,
2007 p_ytd_balcall_aaid),0);
2008
2009 END IF;
2010
2011 hr_utility.set_location(gv_package || lv_procedure_name, 200);
2012
2013 IF ln_ptd_hours_balance_id IS NOT NULL THEN
2014
2015 hr_utility.set_location(gv_package || lv_procedure_name, 210);
2016
2017 ln_current_hours := NVL(pay_balance_pkg.get_value(
2018 ln_ptd_hours_balance_id,
2019 p_ytd_balcall_aaid),0);
2020
2021 END IF;
2022
2023 END IF; /* p_hours_balance_id IS NOT NULL IF*/
2024
2025 hr_utility.set_location(gv_package || lv_procedure_name, 220);
2026
2027 IF NVL(ln_ytd_amount,0) <> 0 OR NVL(ln_ytd_hours,0) <> 0 OR
2028 NVL(ln_current_amount,0) <> 0 OR NVL(ln_current_hours,0) <> 0 THEN
2029
2030 hr_utility.set_location(gv_package || lv_procedure_name, 230);
2031
2032 ln_index := pay_us_payslip_simulation_main.lrr_act_tab.count;
2033
2034 IF p_element_classification_name in ('Earnings',
2035 'Supplemental Earnings',
2036 'Taxable Benefits',
2037 'Imputed Earnings',
2038 'Non-payroll Payments',
2039 'Alien/Expat Earnings') THEN
2040
2041 lv_action_category := 'AC EARNINGS';
2042
2043 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info11
2044 := fnd_number.number_to_canonical(ln_current_hours);
2045
2046 IF ln_current_hours <> 0 AND ln_current_amount <> 0 THEN
2047
2048 hr_utility.set_location(gv_package || lv_procedure_name, 240);
2049
2050 OPEN c_hoursbyrate(p_ytd_balcall_aaid,
2051 p_element_type_id,
2052 p_legislation_code);
2053 LOOP
2054
2055 FETCH c_hoursbyrate INTO ln_multiple,ln_rate;
2056
2057 IF c_hoursbyrate%NOTFOUND THEN
2058 EXIT;
2059 END IF;
2060
2061 END LOOP;
2062 CLOSE c_hoursbyrate;
2063
2064 IF ROUND(ln_rate,5) <> round((ln_current_amount/ln_current_hours),5) OR ln_rate IS NULL
2065 THEN
2066
2067 hr_utility.set_location(gv_package || lv_procedure_name, 250);
2068
2069 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info22:=
2070 ROUND((ln_current_amount/ln_current_hours),5);
2071
2072 ELSE
2073
2074 hr_utility.set_location(gv_package || lv_procedure_name, 260);
2075
2076 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info22:=ln_rate;
2077
2078 END IF;
2079
2080 ELSE
2081
2082 hr_utility.set_location(gv_package || lv_procedure_name, 270);
2083
2084 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info22 := null;
2085
2086 END IF;
2087
2088 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info12
2089 := fnd_number.number_to_canonical(ln_ytd_hours);
2090
2091 END IF; /*p_element_classification_name IF*/
2092
2093 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).action_info_category
2094 := lv_action_category;
2095 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).jurisdiction_code
2096 := NVL(p_jurisdiction_code, '00-000-0000');
2097 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).action_context_id
2098 := p_source_action_id;
2099 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info1
2100 := p_element_classification_name;
2101 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info2
2102 := p_element_type_id;
2103 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info6
2104 := p_primary_balance_id;
2105 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info7
2106 := p_processing_priority;
2107
2108 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info8
2109 := fnd_number.number_to_canonical(NVL(ln_current_amount,0));
2110 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info9
2111 := fnd_number.number_to_canonical(NVL(ln_ytd_amount,0));
2112
2113 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info10
2114 := p_reporting_name;
2115
2116 IF p_element_classification_name <> 'Tax Deductions' THEN
2117
2118 OPEN get_simulation_name(p_element_type_id);
2119 FETCH get_simulation_name INTO lv_simulation_name;
2120
2121 IF get_simulation_name%FOUND THEN
2122
2123 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info10
2124 := lv_simulation_name;
2125
2126 END IF;
2127
2128 CLOSE get_simulation_name;
2129
2130 END IF;
2131
2132 IF lv_action_category = 'AC DEDUCTIONS' THEN
2133 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info24
2134 := p_reporting_name;
2135
2136 IF pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info1
2137 = 'Tax Deductions' AND p_legislation_code = 'US' THEN
2138
2139 OPEN get_display_name(p_reporting_name,
2140 NVL(p_jurisdiction_code, '00-000-0000'));
2141 FETCH get_display_name INTO lv_display_name;
2142
2143 IF get_display_name%FOUND THEN
2144 IF substr(lv_display_name , -7,5) = 'BLANK' THEN
2145 lv_display_name := substr(lv_display_name , 1, length(lv_display_name)-8);
2146 END IF;
2147 ELSE
2148 lv_display_name := ' ';
2149 END IF;
2150
2151 CLOSE get_display_name;
2152
2153 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info24
2154 := lv_display_name;
2155
2156 END IF;
2157
2158 END IF;
2159
2160 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info21
2161 := p_el_jurisdiction_code;
2162
2163 END IF; /* NVL(ln_ytd_amount, 0) <> 0 OR NVL(ln_ytd_hours,0) <> 0 IF */
2164
2165 END IF; /*lv_rate_exists = 'N' IF*/
2166
2167 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
2168
2169 END populate_elements;
2170
2171 /* Procedure : get_current_elements
2172 Purpose : This procedure is used to determine all the elements
2173 associated with the Simulation Run. The Elements can be
2174 fetched in two approaches. If the Run Balances are in
2175 Valid State, then values are fetched through Run Balances
2176 route. If Run Balances are invalid, then values are
2177 fetched through Run Results route. Since Simulation
2178 produces only a Single Output for all the elements
2179 processed in the run, though there are Elements with
2180 Separate Check or Tax Separately selected, we will
2181 consider all the elements in a single set unlike the
2182 Regular archiver where we process these type of elements
2183 separately.
2184 Important : As the current procedure is only for Simulation, it does
2185 not look for YTD Elements and Retro Elements which are
2186 usually fetched in normal Archiver code with extra
2187 processing. The Elements that got processed and generated
2188 [i.e Indirect Elements that got saved like Tax Related
2189 Elements] in current simulation run are only considered by
2190 this procedure.
2191 */
2192
2193 PROCEDURE get_current_elements(p_source_action_id NUMBER
2194 ,p_effective_date DATE
2195 ,p_assignment_id NUMBER
2196 ,p_tax_unit_id NUMBER
2197 ,p_ytd_balcall_aaid NUMBER
2198 ,p_legislation_code VARCHAR2
2199 ,p_business_group_id NUMBER
2200 )
2201 IS
2202
2203 CURSOR get_element_entry_id( cp_run_action_id NUMBER ,
2204 cp_assignment_id NUMBER ,
2205 cp_element_type_id NUMBER ) IS
2206 SELECT DISTINCT peef.element_entry_id
2207 FROM pay_element_entries_f peef,
2208 pay_assignment_actions paa,
2209 pay_payroll_actions ppa,
2210 per_time_periods ptp
2211 WHERE paa.assignment_action_id = cp_run_action_id
2212 AND ppa.payroll_action_id = paa.payroll_action_id
2213 AND ptp.payroll_id = ppa.payroll_id
2214 AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date
2215 AND peef.assignment_id = cp_assignment_id
2216 AND peef.element_type_id = cp_element_type_id;
2217
2218 CURSOR c_get_wrkathome(cp_assignment_id NUMBER)
2219 IS
2220 SELECT NVL(paf.work_at_home, 'N')
2221 ,ppf.person_id
2222 ,ppf.business_group_id
2223 FROM per_assignments_f paf
2224 ,per_all_people_f ppf
2225 WHERE paf.assignment_id = cp_assignment_id
2226 AND paf.person_id = ppf.person_id;
2227
2228 CURSOR c_cur_home_state_jd(cp_person_id NUMBER
2229 ,cp_business_group_id NUMBER
2230 ,cp_effective_date DATE
2231 ,cp_legislation_code VARCHAR2)
2232 IS
2233 SELECT pus.state_code || '-000-0000'
2234 FROM per_addresses pa
2235 ,pay_us_states pus
2236 WHERE pa.person_id = cp_person_id
2237 AND pa.primary_flag = 'Y'
2238 AND cp_effective_date BETWEEN pa.date_from
2239 AND NVL(pa.date_to, hr_general.END_OF_TIME)
2240 AND pa.business_group_id = cp_business_group_id
2241 AND pa.region_2 = pus.state_abbrev
2242 AND pa.style = cp_legislation_code;
2243
2244 CURSOR c_ele_entry_jd(cp_element_type_id NUMBER,
2245 cp_element_entry_id NUMBER)
2246 IS
2247 SELECT peevf.screen_entry_value
2248 FROM pay_input_values_f pivf,
2249 pay_element_entry_values_f peevf
2250 WHERE pivf.element_type_id = cp_element_type_id
2251 AND pivf.name = 'Jurisdiction'
2252 AND peevf.element_entry_id = cp_element_entry_id
2253 AND pivf.input_value_id = peevf.input_value_id;
2254
2255 CURSOR c_work_loc_jd(cp_assignment_id NUMBER,
2256 cp_effective_date DATE)
2257 IS
2258 SELECT DISTINCT pus.state_code
2259 || '-'
2260 || puc.county_code
2261 || '-'
2262 || punc.city_code
2263 FROM per_all_assignments_f peaf,
2264 hr_locations_all hla,
2265 pay_us_states pus,
2266 pay_us_counties puc,
2267 pay_us_city_names punc
2268 WHERE peaf.assignment_id = p_assignment_id
2269 AND peaf.location_id = hla.location_id
2270 AND hla.region_2 = pus.state_abbrev
2271 AND pus.state_code = puc.state_code
2272 AND hla.region_1 = puc.county_name
2273 AND hla.town_or_city = punc.city_name
2274 AND pus.state_code = punc.state_code
2275 AND puc.county_code = punc.county_code
2276 AND cp_effective_date BETWEEN peaf.effective_start_date
2277 AND peaf.effective_end_date;
2278
2279 /* Below cursor is to retrieve all the Elements that got saved to Run
2280 Results for the Simulation Run. We consider only those elements that
2281 have the Primary Balance defined. If there are elements created with
2282 Separate Check or Tax Separately Inputs as Yes, then we will have
2283 the corresponding Run Results in different assignment actions than
2284 the Regular action. In Simulation, we will display all the elements
2285 in a Single output. Hence we are going to retrieve all the elements
2286 based on the Master Action of the Simulation Run. */
2287
2288 CURSOR c_cur_action_elements_rr(cp_source_action_id NUMBER
2289 ,cp_assignment_id NUMBER
2290 ,cp_legislation_code VARCHAR2
2291 )
2292 IS
2293 SELECT DISTINCT
2294 DECODE(pec.classification_name,
2295 'Tax Deductions', NULL,
2296 prr.element_type_id) element_type_id,
2297 pec.classification_name,
2298 NVL(DECODE(pec.classification_name,
2299 'Tax Deductions', petl.reporting_name || ' Withheld',
2300 petl.reporting_name), petl.element_name) reporting_name,
2301 NVL(DECODE(pec.classification_name,
2302 'Tax Deductions',
2303 DECODE(pec.legislation_code,
2304 'CA', SUBSTR(jurisdiction_code,1,2),
2305 DECODE(TO_CHAR(LENGTH(REPLACE(jurisdiction_code, '-'))),
2306 '7', jurisdiction_code,
2307 RPAD(NVL(SUBSTR(RTRIM(LTRIM(jurisdiction_code)),1,2),'0')
2308 ,2,'0') || '-'||
2309 RPAD(NVL(SUBSTR(RTRIM(LTRIM(jurisdiction_code)),4,3),'0')
2310 ,3,'0') ||'-' ||
2311 RPAD(NVL(SUBSTR(RTRIM(LTRIM(jurisdiction_code)),8,4),'0')
2312 ,4,'0')))), '00-000-0000') jurisdiction_code,
2313 pet.element_information10,
2314 pet.element_information12,
2315 pet.processing_priority
2316 FROM pay_assignment_actions paa,
2317 pay_payroll_actions ppa,
2318 pay_all_payrolls_f ppf,
2319 pay_run_results prr,
2320 pay_element_types_f pet,
2321 pay_element_classifications pec,
2322 pay_element_types_f_tl petl
2323 WHERE paa.assignment_id = cp_assignment_id
2324 AND prr.assignment_action_id = paa.assignment_action_id
2325 AND paa.source_action_id = cp_source_action_id
2326 AND ppa.payroll_action_id = paa.payroll_action_id
2327 AND pet.element_type_id = prr.element_type_id
2328 AND pet.element_information10 IS NOT NULL
2329 AND ppa.effective_date between pet.effective_start_date
2330 and pet.effective_end_date
2331 AND ppa.payroll_id = ppf.payroll_id
2332 AND ppf.payroll_id >= 0
2333 AND ppa.effective_date BETWEEN ppf.effective_start_date
2334 AND ppf.effective_end_date
2335 AND petl.element_type_id = pet.element_type_id
2336 AND petl.language = gv_person_lang
2337 AND pec.classification_id = pet.classification_id
2338 AND pec.legislation_code = cp_legislation_code
2339 AND pec.classification_name IN ('Earnings',
2340 'Alien/Expat Earnings',
2341 'Supplemental Earnings',
2342 'Imputed Earnings',
2343 'Taxable Benefits',
2344 'Pre-Tax Deductions',
2345 'Involuntary Deductions',
2346 'Voluntary Deductions',
2347 'Non-payroll Payments',
2348 'Tax Deductions'
2349 )
2350 AND pet.element_name not like '%Calculator'
2351 AND pet.element_name not like '%Special Inputs'
2352 AND pet.element_name not like '%Special Features'
2353 AND pet.element_name not like '%Special Features 2'
2354 AND pet.element_name not like '%Verifier'
2355 AND pet.element_name not like '%Priority'
2356 ORDER by pec.classification_name;
2357
2358 /* Below cursor is to retrieve all the Elements that got saved to Run
2359 Balances for the Simulation Run. We consider only those elements
2360 that have the Primary Balance defined. If there are elements created
2361 with Separate Check or Tax Separately Inputs as Yes, then we will
2362 have the corresponding Run Results in different assignment actions
2363 than the Regular action. In Simulation, we will display all the
2364 elements in a Single output. Hence we are going to retrieve all the
2365 elements based on the Master Action of the Simulation Run. */
2366
2367 CURSOR c_cur_action_elements_rb(cp_source_action_id NUMBER
2368 ,cp_assignment_id NUMBER
2369 ,cp_business_group_id NUMBER
2370 ,cp_legislation_code VARCHAR2
2371 )
2372 IS
2373 SELECT /*+ ORDERED */ DISTINCT
2374 DECODE(pec.classification_name, 'Tax Deductions', null,
2375 pet.element_type_id) element_type_id,
2376 pec.classification_name,
2377 NVL(DECODE(pec.classification_name,
2378 'Tax Deductions', petl.reporting_name || ' Withheld',
2379 petl.reporting_name), petl.element_name) reporting_name,
2380 NVL(DECODE(pec.classification_name,
2381 'Tax Deductions',
2382 DECODE(pec.legislation_code,
2383 'CA', substr(jurisdiction_code,1,2),
2384 decode(to_char(length(replace(jurisdiction_code, '-'))),
2385 '7', jurisdiction_code,
2386 rpad(nvl(substr(rtrim(ltrim(jurisdiction_code)),1,2),'0')
2387 ,2,'0') || '-'||
2388 rpad(nvl(substr(rtrim(ltrim(jurisdiction_code)),4,3),'0')
2389 ,3,'0') ||'-' ||
2390 rpad(nvl(substr(rtrim(ltrim(jurisdiction_code)),8,4),'0')
2391 ,4,'0')))), '00-000-0000') jurisdiction_code,
2392 pet.element_information10,
2393 pet.element_information12,
2394 pet.processing_priority
2395 FROM pay_assignment_actions paa
2396 ,pay_element_classifications pec
2397 ,pay_element_types_f pet
2398 ,pay_balance_types pbt
2399 ,pay_defined_balances pdb
2400 ,pay_run_balances prb
2401 ,pay_element_types_f_tl petl
2402 WHERE paa.assignment_id = cp_assignment_id
2403 AND paa.source_action_id = cp_source_action_id
2404 AND prb.assignment_id = paa.assignment_id
2405 AND prb.assignment_action_id = paa.assignment_action_id
2406 AND pet.element_information10 IS NOT NULL
2407 AND NVL(pet.legislation_code,cp_legislation_code) = cp_legislation_code
2408 AND NVL(pet.business_group_id,cp_business_group_id) = cp_business_group_id
2409 AND pet.element_information10 = pbt.balance_type_id
2410 AND pbt.balance_type_id = pdb.balance_type_id
2411 AND pdb.save_run_balance = 'Y'
2412 AND pdb.defined_balance_id = prb.defined_balance_id
2413 AND prb.effective_date BETWEEN pet.effective_start_date
2414 AND pet.effective_end_date
2415 AND petl.element_type_id = pet.element_type_id
2416 AND petl.language = gv_person_lang
2417 AND pec.classification_id = pet.classification_id
2418 AND pec.legislation_code = cp_legislation_code
2419 AND pec.classification_name IN ('Earnings',
2420 'Alien/Expat Earnings',
2421 'Supplemental Earnings',
2422 'Imputed Earnings',
2423 'Taxable Benefits',
2424 'Pre-Tax Deductions',
2425 'Involuntary Deductions',
2426 'Voluntary Deductions',
2427 'Non-payroll Payments',
2428 'Tax Deductions')
2429 AND pet.element_name NOT LIKE '%Calculator'
2430 AND pet.element_name NOT LIKE '%Special Inputs'
2431 AND pet.element_name NOT LIKE '%Special Features'
2432 AND pet.element_name NOT LIKE '%Special Features 2'
2433 AND pet.element_name NOT LIKE '%Verifier'
2434 AND pet.element_name NOT LIKE '%Priority'
2435 ORDER BY pec.classification_name;
2436
2437 lv_procedure_name VARCHAR2(100) := '.get_current_elements';
2438
2439 lv_wrk_at_home VARCHAR2(2);
2440 ln_person_id per_people_f.person_id%TYPE;
2441 ln_business_group_id per_people_f.business_group_id%TYPE;
2442
2443 lv_jurisdiction_flag VARCHAR2(11);
2444 l_work_loc_jd VARCHAR2(11);
2445
2446 ln_element_index NUMBER;
2447
2448 ln_element_type_id NUMBER;
2449 lv_element_classification_name VARCHAR2(80);
2450 lv_reporting_name VARCHAR2(80);
2451 lv_jurisdiction_code VARCHAR2(80);
2452 ln_primary_balance_id NUMBER;
2453 ln_hours_balance_id NUMBER;
2454 ln_processing_priority NUMBER;
2455
2456 ln_element_entry_id NUMBER;
2457 l_ele_entry_jd VARCHAR2(11);
2458
2459 lv_run_bal_status VARCHAR2(1) := 'N';
2460
2461 BEGIN
2462
2463 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
2464
2465 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2466
2467 /*Review
2468
2469 IF p_legislation_code <> 'US' THEN
2470
2471 populate_hours_x_rate(p_xfr_action_id => p_xfr_action_id
2472 ,p_curr_pymt_action_id => p_curr_pymt_action_id
2473 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
2474 ,p_assignment_id => p_assignment_id
2475 ,p_tax_unit_id => p_tax_unit_id
2476 ,p_sepchk_run_type_id => p_sepchk_run_type_id
2477 ,p_sepchk_flag => p_sepchk_flag
2478 ,p_pymt_balcall_aaid => p_pymt_balcall_aaid
2479 ,p_ytd_balcall_aaid => p_ytd_balcall_aaid
2480 ,p_legislation_code => p_legislation_code);
2481
2482 END IF;
2483
2484 */
2485
2486 hr_utility.set_location(gv_package || lv_procedure_name, 20);
2487
2488 lv_run_bal_status := NULL;
2489
2490 lv_run_bal_status := pay_ac_action_arch.check_run_balance_status(
2491 p_assignment_id => p_assignment_id
2492 ,p_curr_pymt_eff_date => p_effective_date
2493 ,p_legislation_code => p_legislation_code);
2494
2495 IF lv_run_bal_status = 'Y' THEN
2496
2497 hr_utility.set_location(gv_package || lv_procedure_name, 30);
2498
2499 OPEN c_cur_action_elements_rb(p_source_action_id,
2500 p_assignment_id,
2501 p_business_group_id,
2502 p_legislation_code);
2503
2504 ELSE
2505
2506 hr_utility.set_location(gv_package || lv_procedure_name, 40);
2507
2508 OPEN c_cur_action_elements_rr(p_source_action_id,
2509 p_assignment_id,
2510 p_legislation_code);
2511
2512 END IF;
2513
2514 LOOP /* Start of c_cur_action_elements_rb OR c_cur_action_elements_rr LOOP */
2515
2516 IF lv_run_bal_status = 'Y' THEN
2517
2518 hr_utility.set_location(gv_package || lv_procedure_name, 50);
2519
2520 FETCH c_cur_action_elements_rb INTO
2521 ln_element_type_id,
2522 lv_element_classification_name,
2523 lv_reporting_name,
2524 lv_jurisdiction_code,
2525 ln_primary_balance_id,
2526 ln_hours_balance_id,
2527 ln_processing_priority;
2528
2529 IF c_cur_action_elements_rb%NOTFOUND THEN
2530 hr_utility.set_location(gv_package || lv_procedure_name, 55);
2531 EXIT;
2532 END IF;
2533
2534 ELSE
2535
2536 hr_utility.set_location(gv_package || lv_procedure_name, 60);
2537
2538 FETCH c_cur_action_elements_rr INTO
2539 ln_element_type_id,
2540 lv_element_classification_name,
2541 lv_reporting_name,
2542 lv_jurisdiction_code,
2543 ln_primary_balance_id,
2544 ln_hours_balance_id,
2545 ln_processing_priority;
2546
2547 IF c_cur_action_elements_rr%NOTFOUND THEN
2548 hr_utility.set_location(gv_package || lv_procedure_name, 65);
2549 EXIT;
2550 END IF;
2551
2552 END IF;
2553
2554 hr_utility.trace('Element Details fetched are : ');
2555 hr_utility.trace('Element Type ID : '||ln_element_type_id);
2556
2557 /* Review
2558
2559 if p_legislation_code <> 'US' THEN
2560 lv_retro_flag := 'N' ;
2561 lv_retro_parent_flag := 'N';
2562 gv_ytd_amount := 0;
2563 gv_ytd_hour := 0;
2564 lv_original_date_earned := NULL;
2565 lv_effective_start_date := NULL;
2566 lv_effective_end_date := NULL;
2567 lv_category := NULL;
2568 lv_jurisdiction_flag := NULL;
2569 lv_original_date_paid:= NULL;
2570 end if;
2571
2572 */
2573
2574 IF lv_element_classification_name <> 'Tax Deductions' THEN
2575
2576 hr_utility.set_location(gv_package || lv_procedure_name, 70);
2577
2578 OPEN c_get_wrkathome(p_assignment_id);
2579 FETCH c_get_wrkathome INTO lv_wrk_at_home
2580 ,ln_person_id
2581 ,ln_business_group_id;
2582 CLOSE c_get_wrkathome;
2583
2584 IF lv_wrk_at_home = 'Y' THEN
2585
2586 hr_utility.set_location(gv_package || lv_procedure_name, 80);
2587
2588 OPEN c_cur_home_state_jd(ln_person_id,
2589 ln_business_group_id,
2590 p_effective_date,
2591 p_legislation_code);
2592 FETCH c_cur_home_state_jd INTO lv_jurisdiction_flag;
2593 CLOSE c_cur_home_state_jd;
2594
2595 ELSE
2596
2597 hr_utility.set_location(gv_package || lv_procedure_name, 90);
2598
2599 OPEN get_element_entry_id(p_source_action_id,
2600 p_assignment_id,
2601 ln_element_type_id);
2602 FETCH get_element_entry_id INTO ln_element_entry_id;
2603 CLOSE get_element_entry_id;
2604
2605 OPEN c_ele_entry_jd(ln_element_type_id,ln_element_entry_id);
2606 FETCH c_ele_entry_jd INTO l_ele_entry_jd;
2607 CLOSE c_ele_entry_jd;
2608
2609 IF l_ele_entry_jd IS NULL THEN
2610
2611 hr_utility.set_location(gv_package || lv_procedure_name, 100);
2612
2613 OPEN c_work_loc_jd(p_assignment_id,p_effective_date);
2614 FETCH c_work_loc_jd INTO l_work_loc_jd;
2615 CLOSE c_work_loc_jd;
2616
2617 lv_jurisdiction_flag := l_work_loc_jd;
2618
2619 ELSE
2620
2621 hr_utility.set_location(gv_package || lv_procedure_name, 110);
2622
2623 lv_jurisdiction_flag := l_ele_entry_jd;
2624
2625 END IF;
2626
2627 END IF; /* lv_wrk_at_home IF */
2628
2629 END IF; /* lv_element_classification_name <> 'Tax Deductions' IF */
2630
2631 IF lv_element_classification_name LIKE '% Deductions' THEN
2632
2633 ln_hours_balance_id := null;
2634
2635 END IF;
2636
2637 ln_element_index := pay_us_payslip_simulation_main.emp_elements_tab.count;
2638
2639 pay_us_payslip_simulation_main.emp_elements_tab(ln_element_index).element_type_id
2640 := ln_element_type_id;
2641 pay_us_payslip_simulation_main.emp_elements_tab(ln_element_index).element_classfn
2642 := lv_element_classification_name;
2643 pay_us_payslip_simulation_main.emp_elements_tab(ln_element_index).element_primary_balance_id
2644 := ln_primary_balance_id;
2645 pay_us_payslip_simulation_main.emp_elements_tab(ln_element_index).element_processing_priority
2646 := ln_processing_priority;
2647 pay_us_payslip_simulation_main.emp_elements_tab(ln_element_index).element_reporting_name
2648 := lv_reporting_name;
2649 pay_us_payslip_simulation_main.emp_elements_tab(ln_element_index).element_hours_balance_id
2650 := ln_hours_balance_id;
2651 pay_us_payslip_simulation_main.emp_elements_tab(ln_element_index).jurisdiction_code
2652 := lv_jurisdiction_code;
2653
2654 hr_utility.set_location(gv_package || lv_procedure_name, 120);
2655
2656 populate_elements(p_source_action_id => p_source_action_id
2657 ,p_effective_date => p_effective_date
2658 ,p_element_type_id => ln_element_type_id
2659 ,p_primary_balance_id => ln_primary_balance_id
2660 ,p_hours_balance_id => ln_hours_balance_id
2661 ,p_processing_priority => ln_processing_priority
2662 ,p_element_classification_name
2663 => lv_element_classification_name
2664 ,p_reporting_name => lv_reporting_name
2665 ,p_tax_unit_id => p_tax_unit_id
2666 ,p_business_group_id => p_business_group_id
2667 ,p_ytd_balcall_aaid => p_ytd_balcall_aaid
2668 ,p_jurisdiction_code => lv_jurisdiction_code
2669 ,p_legislation_code => p_legislation_code
2670 ,p_el_jurisdiction_code => lv_jurisdiction_flag
2671 );
2672
2673 hr_utility.set_location(gv_package || lv_procedure_name, 130);
2674
2675 /*Clearing variables for Next Element */
2676
2677 lv_element_classification_name := NULL;
2678 ln_element_type_id := NULL;
2679 lv_jurisdiction_code := NULL;
2680 ln_primary_balance_id := NULL;
2681 ln_processing_priority := NULL;
2682 lv_reporting_name := NULL;
2683 ln_hours_balance_id := NULL;
2684 l_ele_entry_jd := NULL;
2685 lv_jurisdiction_flag := NULL;
2686
2687 END LOOP; /* End of c_cur_action_elements_rb OR c_cur_action_elements_rr LOOP */
2688
2689 IF lv_run_bal_status = 'Y' THEN
2690
2691 CLOSE c_cur_action_elements_rb;
2692
2693 ELSE
2694
2695 CLOSE c_cur_action_elements_rr;
2696
2697 END IF;
2698
2699 IF pay_us_payslip_simulation_main.lrr_act_tab.COUNT > 0 THEN
2700
2701 FOR i IN pay_us_payslip_simulation_main.lrr_act_tab.FIRST ..
2702 pay_us_payslip_simulation_main.lrr_act_tab.LAST LOOP
2703
2704 hr_utility.trace('after populate_elements ftp' ||
2705 ' action_context_id is ' ||
2706 to_char(pay_us_payslip_simulation_main.lrr_act_tab(i).action_context_id));
2707 hr_utility.trace('action_info_category ' ||
2708 pay_us_payslip_simulation_main.lrr_act_tab(i).action_info_category);
2709 hr_utility.trace('act_info1 is ' ||
2710 pay_us_payslip_simulation_main.lrr_act_tab(i).act_info1);
2711 hr_utility.trace('act_info10 ' ||
2712 pay_us_payslip_simulation_main.lrr_act_tab(i).act_info10);
2713 hr_utility.trace('act_info3 ' ||
2714 pay_us_payslip_simulation_main.lrr_act_tab(i).act_info3);
2715 hr_utility.trace('act_info4 ' ||
2716 pay_us_payslip_simulation_main.lrr_act_tab(i).act_info4);
2717 hr_utility.trace('act_info5 ' ||
2718 pay_us_payslip_simulation_main.lrr_act_tab(i).act_info5);
2719 hr_utility.trace('act_info6 ' ||
2720 pay_us_payslip_simulation_main.lrr_act_tab(i).act_info6);
2721 hr_utility.trace('act_info7 ' ||
2722 pay_us_payslip_simulation_main.lrr_act_tab(i).act_info7);
2723 hr_utility.trace('act_info8 ' ||
2724 pay_us_payslip_simulation_main.lrr_act_tab(i).act_info8);
2725
2726 END LOOP;
2727
2728 END IF;
2729
2730 hr_utility.set_location(gv_package || lv_procedure_name, 140);
2731
2732 IF pay_us_payslip_simulation_main.emp_elements_tab.count > 0 THEN
2733
2734 hr_utility.set_location(gv_package || lv_procedure_name, 150);
2735
2736 FOR j in pay_us_payslip_simulation_main.emp_elements_tab.FIRST ..
2737 pay_us_payslip_simulation_main.emp_elements_tab.LAST LOOP
2738
2739 hr_utility.trace('emp_elements_tab.element_type ' ||
2740 to_char(pay_us_payslip_simulation_main.emp_elements_tab(j).element_type_id));
2741
2742 END LOOP;
2743
2744 END IF;
2745
2746 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
2747
2748 END get_current_elements;
2749
2750 PROCEDURE populate_emp_hours_by_rate(p_assignment_action_id NUMBER
2751 ,p_assignment_id NUMBER
2752 ,p_ytd_balcall_aaid NUMBER)
2753 IS
2754
2755 CURSOR c_hoursbyrate(cp_ytd_action_id NUMBER)
2756 IS
2757 SELECT hours.element_type_id,
2758 hours.element_name,
2759 hours.processing_priority,
2760 hours.rate,
2761 nvl(hours.multiple,1),
2762 hours.hours,
2763 hours.amount
2764 FROM pay_hours_by_rate_v hours
2765 WHERE hours.assignment_action_id = cp_ytd_action_id
2766 AND legislation_code = 'US'
2767 AND hours.element_type_id >= 0
2768 ORDER BY hours.processing_priority,hours.element_type_id;
2769
2770 CURSOR get_simulation_name(cp_element_type_id NUMBER)
2771 IS
2772 SELECT petei.eei_information18
2773 FROM pay_element_type_extra_info petei
2774 WHERE petei.element_type_id = cp_element_type_id
2775 AND petei.eei_information18 IS NOT NULL;
2776
2777 lv_procedure_name VARCHAR2(100) := '.populate_emp_hours_by_rate';
2778
2779 ln_element_type_id NUMBER;
2780 lv_simulation_name VARCHAR2(100);
2781 lv_element_name VARCHAR2(100);
2782 lv_processing_priority VARCHAR2(10);
2783 ln_rate NUMBER(15,5);
2784 ln_multiple NUMBER(15,5);
2785 ln_hours NUMBER(15,5);
2786 ln_amount NUMBER(15,5);
2787 ln_index NUMBER;
2788
2789 BEGIN
2790
2791 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
2792
2793 ln_rate := 0;
2794 ln_hours := 0;
2795 ln_multiple := 1;
2796
2797 OPEN c_hoursbyrate(p_ytd_balcall_aaid);
2798 LOOP
2799
2800 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2801
2802 FETCH c_hoursbyrate INTO ln_element_type_id
2803 ,lv_element_name
2804 ,lv_processing_priority
2805 ,ln_rate
2806 ,ln_multiple
2807 ,ln_hours
2808 ,ln_amount;
2809
2810 IF c_hoursbyrate%NOTFOUND THEN
2811
2812 hr_utility.set_location(gv_package || lv_procedure_name, 15);
2813 EXIT;
2814
2815 END IF;
2816
2817 OPEN get_simulation_name(ln_element_type_id);
2818 FETCH get_simulation_name INTO lv_simulation_name;
2819
2820 IF get_simulation_name%FOUND THEN
2821
2822 lv_element_name := lv_simulation_name;
2823
2824 END IF;
2825
2826 CLOSE get_simulation_name;
2827
2828 hr_utility.set_location(gv_package || lv_procedure_name, 20);
2829
2830 ln_index := pay_ac_action_arch.lrr_act_tab.count;
2831
2832 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
2833 := 'EMPLOYEE HOURS BY RATE';
2834 pay_ac_action_arch.lrr_act_tab(ln_index).jurisdiction_code
2835 := '00-000-0000';
2836 pay_ac_action_arch.lrr_act_tab(ln_index).action_context_id
2837 := p_assignment_action_id;
2838 pay_ac_action_arch.lrr_act_tab(ln_index).assignment_id
2839 := p_assignment_id;
2840 pay_ac_action_arch.lrr_act_tab(ln_index).act_info1
2841 := ln_element_type_id;
2842 pay_ac_action_arch.lrr_act_tab(ln_index).act_info4
2843 := lv_element_name;
2844 pay_ac_action_arch.lrr_act_tab(ln_index).act_info5
2845 := fnd_number.number_to_canonical(ln_rate);
2846 pay_ac_action_arch.lrr_act_tab(ln_index).act_info6
2847 := fnd_number.number_to_canonical(ln_hours);
2848 pay_ac_action_arch.lrr_act_tab(ln_index).act_info7
2849 := lv_processing_priority;
2850 pay_ac_action_arch.lrr_act_tab(ln_index).act_info8
2851 := fnd_number.number_to_canonical(ln_multiple);
2852 pay_ac_action_arch.lrr_act_tab(ln_index).act_info9
2853 := fnd_number.number_to_canonical(ln_amount);
2854
2855 hr_utility.set_location(gv_package || lv_procedure_name, 30);
2856
2857 END LOOP;
2858
2859 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
2860
2861 END populate_emp_hours_by_rate;
2862
2863 PROCEDURE populate_summary(p_assignment_action_id NUMBER,
2864 p_assignment_id NUMBER,
2865 p_effective_date DATE)
2866
2867 IS
2868
2869 CURSOR get_person_id(cp_assignment_id NUMBER,
2870 cp_effective_date DATE)
2871 IS
2872 SELECT paf.person_id
2873 FROM per_all_assignments_f paf
2874 WHERE paf.assignment_id = cp_assignment_id
2875 AND cp_effective_date BETWEEN paf.effective_start_date
2876 AND paf.effective_end_date;
2877
2878 CURSOR get_prev_values(cp_assignment_id NUMBER,
2879 cp_person_id NUMBER,
2880 cp_effective_date DATE)
2881 IS
2882 SELECT pai.action_information4 earnings,
2883 pai.action_information5 supplemental_earnings,
2884 pai.action_information6 imputed_earnings,
2885 pai.action_information7 pre_tax_deductions,
2886 pai.action_information8 involuntary_deductions,
2887 pai.action_information9 voluntary_deductions,
2888 pai.action_information10 tax_deductions,
2889 pai.action_information11 taxable_benefits,
2890 pai.action_information12 alien_expat_earnings,
2891 pai.action_information13 non_payroll_payments
2892 FROM pay_emp_payslip_action_info_v payslipv,
2893 pay_assignment_actions paa,
2894 pay_action_information pai,
2895 fnd_sessions fs
2896 WHERE paa.assignment_id = cp_assignment_id
2897 AND payslipv.person_id = cp_person_id
2898 AND payslipv.action_context_id = paa.assignment_action_id
2899 AND payslipv.effective_date < cp_effective_date
2900 AND pai.assignment_id = paa.assignment_id
2901 AND pai.action_context_id = payslipv.action_context_id
2902 AND pai.action_context_type = 'AAP'
2903 AND pai.action_information_category = 'AC SUMMARY CURRENT'
2904 AND fs.session_id = USERENV('SESSIONID')
2905 AND payslipv.effective_date < fs.effective_date
2906 ORDER BY payslipv.effective_date DESC,
2907 payslipv.action_context_id DESC;
2908
2909 lv_procedure_name VARCHAR2(100) := '.populate_summary';
2910
2911 lv_earnings VARCHAR2(80):= 0;
2912 lv_supplemental_earnings VARCHAR2(80):= 0;
2913 lv_imputed_Earnings VARCHAR2(80):= 0;
2914 lv_non_payroll_payments VARCHAR2(80):= 0;
2915 lv_pre_tax_deductions VARCHAR2(80):= 0;
2916 lv_involuntary_deductions VARCHAR2(80):= 0;
2917 lv_voluntary_deductions VARCHAR2(80):= 0;
2918 lv_tax_deductions VARCHAR2(80):= 0;
2919 lv_taxable_benefits VARCHAR2(80):= 0;
2920 lv_alien_expat_earnings VARCHAR2(80):= 0;
2921
2922 lv_estimated_net VARCHAR2(80):= 0;
2923
2924 lv_prev_earnings VARCHAR2(80):= 0;
2925 lv_prev_supplemental_earnings VARCHAR2(80):= 0;
2926 lv_prev_imputed_Earnings VARCHAR2(80):= 0;
2927 lv_prev_non_payroll_payments VARCHAR2(80):= 0;
2928 lv_prev_pre_tax_deductions VARCHAR2(80):= 0;
2929 lv_prev_involuntary_deductions VARCHAR2(80):= 0;
2930 lv_prev_voluntary_deductions VARCHAR2(80):= 0;
2931 lv_prev_tax_deductions VARCHAR2(80):= 0;
2932 lv_prev_taxable_benefits VARCHAR2(80):= 0;
2933 lv_prev_alien_expat_earnings VARCHAR2(80):= 0;
2934
2935 lv_ytd_earnings VARCHAR2(80):= 0;
2936 lv_ytd_supplemental_earnings VARCHAR2(80):= 0;
2937 lv_ytd_imputed_Earnings VARCHAR2(80):= 0;
2938 lv_ytd_non_payroll_payments VARCHAR2(80):= 0;
2939 lv_ytd_pre_tax_deductions VARCHAR2(80):= 0;
2940 lv_ytd_involuntary_deductions VARCHAR2(80):= 0;
2941 lv_ytd_voluntary_deductions VARCHAR2(80):= 0;
2942 lv_ytd_tax_deductions VARCHAR2(80):= 0;
2943 lv_ytd_taxable_benefits VARCHAR2(80):= 0;
2944 lv_ytd_alien_expat_earnings VARCHAR2(80):= 0;
2945
2946 ln_index NUMBER;
2947 ln_person_id per_all_assignments_f.person_id%TYPE;
2948
2949 lv_error_message VARCHAR2(200);
2950 ln_step NUMBER;
2951
2952 lv_current_label VARCHAR2(100);
2953 lv_ytd_label VARCHAR2(100);
2954 j NUMBER := 0;
2955
2956 BEGIN
2957
2958 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
2959
2960 IF pay_us_payslip_simulation_main.lrr_act_tab.count > 0 THEN
2961
2962 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2963
2964 FOR i in pay_us_payslip_simulation_main.lrr_act_tab.FIRST ..
2965 pay_us_payslip_simulation_main.lrr_act_tab.LAST LOOP
2966
2967 IF pay_us_payslip_simulation_main.lrr_act_tab(i).action_info_category
2968 = 'AC EARNINGS' THEN
2969
2970 IF pay_us_payslip_simulation_main.lrr_act_tab(i).act_info1
2971 = 'Earnings' THEN
2972
2973 lv_earnings
2974 := lv_earnings +
2975 NVL(fnd_number.canonical_to_number(pay_us_payslip_simulation_main.lrr_act_tab(i).act_info8),0);
2976 lv_ytd_earnings
2977 := lv_ytd_earnings +
2978 NVL(fnd_number.canonical_to_number(pay_us_payslip_simulation_main.lrr_act_tab(i).act_info9),0);
2979
2980 ELSIF pay_us_payslip_simulation_main.lrr_act_tab(i).act_info1
2981 = 'Supplemental Earnings' THEN
2982
2983 lv_supplemental_earnings
2984 := lv_supplemental_earnings +
2985 NVL(fnd_number.canonical_to_number(pay_us_payslip_simulation_main.lrr_act_tab(i).act_info8),0);
2986 lv_ytd_supplemental_earnings
2987 := lv_ytd_supplemental_earnings +
2988 NVL(fnd_number.canonical_to_number(pay_us_payslip_simulation_main.lrr_act_tab(i).act_info9),0);
2989
2990 ELSIF pay_us_payslip_simulation_main.lrr_act_tab(i).act_info1
2991 = 'Imputed Earnings' THEN
2992
2993 lv_imputed_earnings
2994 := lv_imputed_earnings +
2995 NVL(fnd_number.canonical_to_number(pay_us_payslip_simulation_main.lrr_act_tab(i).act_info8),0);
2996 lv_ytd_imputed_earnings
2997 := lv_ytd_imputed_earnings +
2998 NVL(fnd_number.canonical_to_number(pay_us_payslip_simulation_main.lrr_act_tab(i).act_info9),0);
2999
3000 ELSIF pay_us_payslip_simulation_main.lrr_act_tab(i).act_info1
3001 = 'Non-payroll Payments' THEN
3002
3003 lv_non_payroll_payments
3004 := lv_non_payroll_payments +
3005 NVL(fnd_number.canonical_to_number(pay_us_payslip_simulation_main.lrr_act_tab(i).act_info8),0);
3006 lv_ytd_non_payroll_payments
3007 := lv_ytd_non_payroll_payments +
3008 NVL(fnd_number.canonical_to_number(pay_us_payslip_simulation_main.lrr_act_tab(i).act_info9),0);
3009
3010 ELSIF pay_us_payslip_simulation_main.lrr_act_tab(i).act_info1
3011 = 'Taxable Benefits' THEN
3012
3013 lv_taxable_benefits
3014 := lv_taxable_benefits +
3015 NVL(fnd_number.canonical_to_number(pay_us_payslip_simulation_main.lrr_act_tab(i).act_info8),0);
3016 lv_ytd_taxable_benefits
3017 := lv_ytd_taxable_benefits +
3018 NVL(fnd_number.canonical_to_number(pay_us_payslip_simulation_main.lrr_act_tab(i).act_info9),0);
3019
3020 ELSIF pay_us_payslip_simulation_main.lrr_act_tab(i).act_info1
3021 = 'Alien/Expat Earnings' THEN
3022
3023 lv_alien_expat_earnings
3024 := lv_alien_expat_earnings +
3025 NVL(fnd_number.canonical_to_number(pay_us_payslip_simulation_main.lrr_act_tab(i).act_info8),0);
3026 lv_ytd_alien_expat_earnings
3027 := lv_ytd_alien_expat_earnings +
3028 NVL(fnd_number.canonical_to_number(pay_us_payslip_simulation_main.lrr_act_tab(i).act_info9),0);
3029
3030 END IF; /* End of AC EARNINGS Section */
3031
3032 ELSIF pay_us_payslip_simulation_main.lrr_act_tab(i).action_info_category
3033 = 'AC DEDUCTIONS' THEN
3034
3035 IF pay_us_payslip_simulation_main.lrr_act_tab(i).act_info1
3036 = 'Pre-Tax Deductions' THEN
3037
3038 lv_pre_tax_deductions
3039 := lv_pre_tax_deductions +
3040 NVL(fnd_number.canonical_to_number(pay_us_payslip_simulation_main.lrr_act_tab(i).act_info8),0);
3041 lv_ytd_pre_tax_deductions
3042 := lv_ytd_pre_tax_deductions +
3043 NVL(fnd_number.canonical_to_number(pay_us_payslip_simulation_main.lrr_act_tab(i).act_info9),0);
3044
3045 ELSIF pay_us_payslip_simulation_main.lrr_act_tab(i).act_info1
3046 = 'Involuntary Deductions' THEN
3047
3048 lv_involuntary_deductions
3049 := lv_involuntary_deductions +
3050 NVL(fnd_number.canonical_to_number(pay_us_payslip_simulation_main.lrr_act_tab(i).act_info8),0);
3051 lv_ytd_involuntary_deductions
3052 := lv_ytd_involuntary_deductions +
3053 NVL(fnd_number.canonical_to_number(pay_us_payslip_simulation_main.lrr_act_tab(i).act_info9),0);
3054
3055 ELSIF pay_us_payslip_simulation_main.lrr_act_tab(i).act_info1
3056 = 'Voluntary Deductions' THEN
3057
3058 lv_voluntary_deductions
3059 := lv_voluntary_deductions +
3060 NVL(fnd_number.canonical_to_number(pay_us_payslip_simulation_main.lrr_act_tab(i).act_info8),0);
3061 lv_ytd_voluntary_deductions
3062 := lv_ytd_voluntary_deductions +
3063 NVL(fnd_number.canonical_to_number(pay_us_payslip_simulation_main.lrr_act_tab(i).act_info9),0);
3064
3065 ELSIF pay_us_payslip_simulation_main.lrr_act_tab(i).act_info1
3066 = 'Tax Deductions' THEN
3067
3068 lv_tax_deductions
3069 := lv_tax_deductions +
3070 NVL(fnd_number.canonical_to_number(pay_us_payslip_simulation_main.lrr_act_tab(i).act_info8),0);
3071 lv_ytd_tax_deductions
3072 := lv_ytd_tax_deductions +
3073 NVL(fnd_number.canonical_to_number(pay_us_payslip_simulation_main.lrr_act_tab(i).act_info9),0);
3074
3075 END IF; /* End of AC DEDUCTIONS Section */
3076
3077 END IF; /* action_info_category IF */
3078
3079 END LOOP;
3080
3081 END IF; /* pay_us_payslip_simulation_main.lrr_act_tab.count > 0 IF */
3082
3083 hr_utility.set_location(gv_package || lv_procedure_name, 20);
3084
3085 j := 0;
3086
3087 IF pay_us_payslip_simulation_main.ltr_summary_labels.count > 0 THEN
3088
3089 for j in pay_us_payslip_simulation_main.ltr_summary_labels.first..
3090 pay_us_payslip_simulation_main.ltr_summary_labels.last loop
3091 if pay_us_payslip_simulation_main.ltr_summary_labels(j).language
3092 = pay_ac_action_arch.gv_person_lang and
3093 pay_us_payslip_simulation_main.ltr_summary_labels(j).lookup_code = 'CURRENT' THEN
3094 lv_current_label := pay_us_payslip_simulation_main.ltr_summary_labels(j).meaning;
3095 end if;
3096
3097 if pay_us_payslip_simulation_main.ltr_summary_labels(j).language
3098 = pay_ac_action_arch.gv_person_lang and
3099 pay_us_payslip_simulation_main.ltr_summary_labels(j).lookup_code = 'YTD' THEN
3100 lv_ytd_label := pay_us_payslip_simulation_main.ltr_summary_labels(j).meaning;
3101 end if;
3102 end loop;
3103
3104 END IF;
3105
3106 /* Insert one row for CURRENT and one for YTD */
3107
3108 IF pay_us_payslip_simulation_main.lrr_act_tab.count > 0 THEN
3109
3110 /* CURRENT Values */
3111
3112 ln_index := pay_us_payslip_simulation_main.lrr_act_tab.count;
3113 hr_utility.trace('ln_index = ' || ln_index);
3114 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).action_info_category
3115 := 'AC SUMMARY CURRENT';
3116 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).jurisdiction_code
3117 := '00-000-0000';
3118 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info4
3119 := fnd_number.number_to_canonical(lv_earnings); /*Bug 3311866*/
3120 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info5
3121 := fnd_number.number_to_canonical(lv_supplemental_earnings) ;
3122 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info6
3123 := fnd_number.number_to_canonical(lv_imputed_earnings);
3124 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info7
3125 := fnd_number.number_to_canonical(lv_pre_tax_deductions) ;
3126 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info8
3127 := fnd_number.number_to_canonical(lv_involuntary_deductions);
3128 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info9
3129 := fnd_number.number_to_canonical(lv_voluntary_deductions) ;
3130 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info10
3131 := fnd_number.number_to_canonical(lv_tax_deductions) ;
3132 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info11
3133 := fnd_number.number_to_canonical(lv_taxable_benefits);
3134 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info12
3135 := fnd_number.number_to_canonical(lv_alien_expat_earnings);
3136 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info13
3137 := fnd_number.number_to_canonical(lv_non_payroll_payments);
3138 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info14
3139 := lv_current_label;
3140 /* Determining Net Current Value below. In Regular Archiver Net value is
3141 determined based on PAYMENTS Dimension. But in Payroll Simulation, since
3142 Prepayments will not be run, the Net value will be determined based on
3143 the Earnings and Deductions */
3144 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info15
3145 := fnd_number.number_to_canonical(lv_earnings
3146 + lv_supplemental_earnings
3147 - lv_pre_tax_deductions
3148 - lv_involuntary_deductions
3149 - lv_voluntary_deductions
3150 - lv_tax_deductions
3151 + lv_alien_expat_earnings
3152 + lv_non_payroll_payments);
3153
3154 lv_estimated_net := fnd_number.canonical_to_number(
3155 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info15);
3156
3157 /* YTD Values */
3158
3159 ln_index := pay_us_payslip_simulation_main.lrr_act_tab.count;
3160
3161 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).action_info_category
3162 := 'AC SUMMARY YTD';
3163 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).jurisdiction_code
3164 := '00-000-0000';
3165 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info4
3166 := fnd_number.number_to_canonical(lv_ytd_earnings); /*Bug 3311866*/
3167 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info5
3168 := fnd_number.number_to_canonical(lv_ytd_supplemental_earnings) ;
3169 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info6
3170 := fnd_number.number_to_canonical(lv_ytd_imputed_earnings);
3171 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info7
3172 := fnd_number.number_to_canonical(lv_ytd_pre_tax_deductions) ;
3173 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info8
3174 := fnd_number.number_to_canonical(lv_ytd_involuntary_deductions);
3175 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info9
3176 := fnd_number.number_to_canonical(lv_ytd_voluntary_deductions) ;
3177 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info10
3178 := fnd_number.number_to_canonical(lv_ytd_tax_deductions) ;
3179 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info11
3180 := fnd_number.number_to_canonical(lv_ytd_taxable_benefits);
3181 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info12
3182 := fnd_number.number_to_canonical(lv_ytd_alien_expat_earnings);
3183 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info13
3184 := fnd_number.number_to_canonical(lv_ytd_non_payroll_payments);
3185 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info14
3186 := lv_ytd_label;
3187 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info15
3188 := fnd_number.number_to_canonical(lv_ytd_earnings
3189 + lv_ytd_supplemental_earnings
3190 - lv_ytd_pre_tax_deductions
3191 - lv_ytd_involuntary_deductions
3192 - lv_ytd_voluntary_deductions
3193 - lv_ytd_tax_deductions
3194 + lv_ytd_alien_expat_earnings
3195 + lv_ytd_non_payroll_payments);
3196
3197 END IF;
3198
3199 /* Archive Estimated Values for Summary */
3200
3201 ln_index := pay_us_payslip_simulation_main.lrr_act_tab.count;
3202 hr_utility.trace('ln_index = ' || ln_index);
3203 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).action_info_category
3204 := 'AC SIM GRAPH ESTIMATED';
3205 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).jurisdiction_code
3206 := '00-000-0000';
3207 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info1
3208 := fnd_number.number_to_canonical(lv_pre_tax_deductions);
3209 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info2
3210 := fnd_number.number_to_canonical(lv_involuntary_deductions +
3211 lv_voluntary_deductions);
3212 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info3
3213 := fnd_number.number_to_canonical(lv_tax_deductions);
3214 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info4
3215 := fnd_number.number_to_canonical(lv_estimated_net);
3216
3217 /* Archive Current Values for Summary */
3218 /* Archived values are stored in Canonical Form. So we are directly
3219 assigning them without conversion. Only when calculation is to be
3220 done, we are converting values to number and back to canonical*/
3221
3222 OPEN get_person_id(p_assignment_id,p_effective_date);
3223 FETCH get_person_id INTO ln_person_id;
3224 CLOSE get_person_id;
3225
3226 OPEN get_prev_values(p_assignment_id
3227 ,ln_person_id
3228 ,p_effective_date);
3229 FETCH get_prev_values INTO lv_prev_earnings
3230 ,lv_prev_supplemental_earnings
3231 ,lv_prev_imputed_Earnings
3232 ,lv_prev_pre_tax_deductions
3233 ,lv_prev_involuntary_deductions
3234 ,lv_prev_voluntary_deductions
3235 ,lv_prev_tax_deductions
3236 ,lv_prev_taxable_benefits
3237 ,lv_prev_alien_expat_earnings
3238 ,lv_prev_non_payroll_payments;
3239 CLOSE get_prev_values;
3240
3241 ln_index := pay_us_payslip_simulation_main.lrr_act_tab.count;
3242
3243 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).action_info_category
3244 := 'AC SIM GRAPH ACTUAL';
3245 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).jurisdiction_code
3246 := '00-000-0000';
3247 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info1
3248 := lv_prev_pre_tax_deductions;
3249 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info2
3250 := fnd_number.number_to_canonical(
3251 fnd_number.canonical_to_number(lv_prev_involuntary_deductions) +
3252 fnd_number.canonical_to_number(lv_prev_voluntary_deductions));
3253 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info3
3254 := lv_prev_tax_deductions;
3255 pay_us_payslip_simulation_main.lrr_act_tab(ln_index).act_info4
3256 := fnd_number.number_to_canonical(
3257 fnd_number.canonical_to_number(lv_prev_earnings)
3258 + fnd_number.canonical_to_number(lv_prev_supplemental_earnings)
3259 - fnd_number.canonical_to_number(lv_prev_pre_tax_deductions)
3260 - fnd_number.canonical_to_number(lv_prev_involuntary_deductions)
3261 - fnd_number.canonical_to_number(lv_prev_voluntary_deductions)
3262 - fnd_number.canonical_to_number(lv_prev_tax_deductions)
3263 + fnd_number.canonical_to_number(lv_prev_alien_expat_earnings)
3264 + fnd_number.canonical_to_number(lv_prev_non_payroll_payments));
3265
3266 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
3267
3268 END populate_summary;
3269
3270 PROCEDURE process_actions(p_payroll_action_id NUMBER
3271 ,p_source_action_id NUMBER
3272 ,p_legislation_code VARCHAR2
3273 ,p_business_group_id NUMBER
3274 ,p_assignment_id NUMBER
3275 ,p_payroll_id NUMBER
3276 ,p_consolidation_set_id NUMBER
3277 ,p_tax_unit_id NUMBER
3278 ,p_effective_date DATE
3279 ,p_archive_balance_info VARCHAR2 DEFAULT 'Y'
3280 )
3281
3282 IS
3283
3284 CURSOR c_get_ytd_aaid(cp_source_action_id NUMBER,
3285 cp_payroll_action_id NUMBER,
3286 cp_assignment_id NUMBER)
3287 IS
3288 SELECT paa.assignment_action_id
3289 FROM pay_assignment_actions paa
3290 WHERE paa.source_action_id = cp_source_action_id
3291 AND paa.assignment_id = cp_assignment_id
3292 AND paa.payroll_action_id = cp_payroll_action_id
3293 ORDER BY paa.assignment_action_id DESC;
3294
3295 CURSOR c_time_period(cp_run_assignment_action NUMBER)
3296 IS
3297 SELECT ptp.time_period_id,
3298 NVL(ppa.date_earned,ppa.effective_date)
3299 FROM pay_assignment_actions paa,
3300 pay_payroll_actions ppa,
3301 per_time_periods ptp
3302 WHERE paa.assignment_action_id = cp_run_assignment_action
3303 AND ppa.payroll_action_id = paa.payroll_action_id
3304 AND ptp.payroll_id = ppa.payroll_id
3305 AND NVL(ppa.date_earned,ppa.effective_date)
3306 BETWEEN ptp.start_date AND ptp.end_date;
3307
3308 lv_procedure_name VARCHAR2(100) := '.process_actions';
3309
3310 ln_ytd_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;
3311
3312 lv_res_jurisdiction_code VARCHAR2(30);
3313
3314 ln_time_period_id per_time_periods.time_period_id%TYPE;
3315 ld_run_date_earned DATE;
3316
3317 BEGIN
3318
3319 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
3320 hr_utility.set_location(gv_package || lv_procedure_name, 10);
3321
3322 pay_balance_pkg.set_context('TAX_UNIT_ID', p_tax_unit_id);
3323
3324 OPEN c_get_ytd_aaid(p_source_action_id,
3325 p_payroll_action_id,
3326 p_assignment_id);
3327 FETCH c_get_ytd_aaid INTO ln_ytd_asg_action_id;
3328 CLOSE c_get_ytd_aaid;
3329
3330 OPEN c_time_period(p_source_action_id);
3331 FETCH c_time_period INTO ln_time_period_id,
3332 ld_run_date_earned;
3333 CLOSE c_time_period;
3334
3335 hr_utility.set_location(gv_package || lv_procedure_name, 20);
3336
3337 initialization_process(p_legislation_code,p_payroll_action_id);
3338
3339 hr_utility.set_location(gv_package || lv_procedure_name, 30);
3340
3341 hr_utility.set_location(gv_package || lv_procedure_name, 100);
3342
3343 IF p_archive_balance_info = 'Y' THEN
3344
3345 get_current_elements(
3346 p_source_action_id => p_source_action_id
3347 ,p_effective_date => p_effective_date
3348 ,p_assignment_id => p_assignment_id
3349 ,p_tax_unit_id => p_tax_unit_id
3350 ,p_ytd_balcall_aaid => ln_ytd_asg_action_id
3351 ,p_legislation_code => p_legislation_code
3352 ,p_business_group_id => p_business_group_id
3353 );
3354
3355 END IF;
3356
3357 hr_utility.set_location(gv_package || lv_procedure_name, 120);
3358
3359 /*Determine the Employee Resident Jurisdiction Code*/
3360 /*This is necessary to determine Employee Withholding Information*/
3361
3362 get_emp_resident_jd(p_assignment_id,
3363 p_effective_date,
3364 lv_res_jurisdiction_code);
3365
3366 get_personal_information(
3367 p_assignment_action_id => p_source_action_id
3368 ,p_assignment_id => p_assignment_id
3369 ,p_effective_date => p_effective_date
3370 ,p_date_earned => ld_run_date_earned
3371 ,p_tax_unit_id => p_tax_unit_id
3372 ,p_time_period_id => ln_time_period_id
3373 ,p_ytd_balcall_aaid => ln_ytd_asg_action_id
3374 );
3375
3376 pay_ac_action_arch.initialization_process;
3377
3378 pay_us_action_arch.get_employee_withholding_info(
3379 p_assignment_id
3380 ,p_effective_date
3381 ,lv_res_jurisdiction_code);
3382
3383 populate_emp_hours_by_rate(
3384 p_assignment_action_id => p_source_action_id
3385 ,p_assignment_id => p_assignment_id
3386 ,p_ytd_balcall_aaid => ln_ytd_asg_action_id);
3387
3388 populate_summary(p_source_action_id,p_assignment_id,p_effective_date);
3389
3390 pay_simulator_pkg.insert_simulation_rows(p_action_context_id => p_source_action_id
3391 ,p_action_context_type => 'AAP'
3392 ,p_assignment_id => p_assignment_id
3393 ,p_tax_unit_id => p_tax_unit_id
3394 ,p_effective_date => p_effective_date
3395 ,p_tab_rec_data => pay_us_payslip_simulation_main.lrr_act_tab
3396 );
3397
3398 pay_simulator_pkg.insert_simulation_rows(p_action_context_id => p_source_action_id
3399 ,p_action_context_type => 'AAP'
3400 ,p_assignment_id => p_assignment_id
3401 ,p_tax_unit_id => p_tax_unit_id
3402 ,p_effective_date => p_effective_date
3403 ,p_tab_rec_data => pay_ac_action_arch.lrr_act_tab
3404 );
3405
3406 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
3407
3408 END process_actions;
3409
3410 PROCEDURE archive_action_information(p_payroll_action_id NUMBER,
3411 p_source_action_id NUMBER,
3412 p_assignment_id NUMBER,
3413 p_effective_Date DATE,
3414 p_payroll_id NUMBER,
3415 p_time_period_id NUMBER,
3416 p_legislation_code VARCHAR2,
3417 p_business_group_id NUMBER,
3418 p_tax_unit_id NUMBER)
3419
3420 IS
3421
3422 lv_procedure_name VARCHAR2(100) := '.archive_action_information';
3423
3424 ltr_info_arch pay_emp_action_arch.action_info_table;
3425
3426 ln_index NUMBER;
3427
3428 BEGIN
3429
3430 ltr_info_arch.DELETE;
3431
3432 ln_index := ltr_info_arch.COUNT;
3433
3434 ltr_info_arch(ln_index).action_info_category
3435 := 'SIMULATION_ACTION_INFORMATION';
3436 ltr_info_arch(ln_index).act_info1 := p_payroll_action_id;
3437 ltr_info_arch(ln_index).act_info2 := p_source_action_id;
3438 ltr_info_arch(ln_index).act_info3 := p_business_group_id;
3439 ltr_info_arch(ln_index).act_info4 := p_legislation_code;
3440 ltr_info_arch(ln_index).act_info5 := p_payroll_id;
3441 ltr_info_arch(ln_index).act_info6 := p_time_period_id;
3442
3443 pay_simulator_pkg.insert_simulation_rows(p_action_context_id => p_payroll_action_id
3444 ,p_action_context_type => 'ACTION INFO'
3445 ,p_assignment_id => p_assignment_id
3446 ,p_tax_unit_id => p_tax_unit_id
3447 ,p_effective_date => p_effective_date
3448 ,p_tab_rec_data => ltr_info_arch
3449 );
3450
3451 END archive_action_information;
3452
3453 PROCEDURE archive_payroll_level_data(p_payroll_action_id NUMBER,
3454 p_payroll_id NUMBER,
3455 p_assignment_id NUMBER,
3456 p_effective_date DATE)
3457 IS
3458
3459 CURSOR c_get_organization(cp_payroll_id NUMBER,
3460 cp_assignment_id NUMBER,
3461 cp_effective_date DATE)
3462 IS
3463 SELECT DISTINCT paf.organization_id,
3464 paf.business_group_id
3465 FROM per_all_assignments_f paf
3466 WHERE paf.payroll_id = cp_payroll_id
3467 AND paf.assignment_id = cp_assignment_id
3468 AND cp_effective_date BETWEEN paf.effective_start_date
3469 AND paf.effective_end_date;
3470
3471 CURSOR c_get_tax_unit_id(cp_assignment_id NUMBER)
3472 IS
3473 SELECT NVL(psi.tax_unit_id,-1)
3474 FROM pay_simulation_information psi
3475 WHERE psi.action_information_category = 'EMPLOYEE DETAILS'
3476 AND psi.action_context_type = 'AAP'
3477 AND psi.assignment_id = cp_assignment_id
3478 ORDER BY psi.action_context_id DESC;
3479
3480 lv_procedure_name VARCHAR2(100) := '.archive_payroll_level_data';
3481
3482 ln_organization_id per_all_assignments_f.organization_id%TYPE;
3483 ln_business_group_id per_all_assignments_f.business_group_id%TYPE;
3484 ln_tax_unit_id per_all_assignments_f.organization_id%TYPE;
3485
3486 BEGIN
3487
3488 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
3489
3490 OPEN c_get_organization(p_payroll_id,
3491 p_assignment_id,
3492 p_effective_date);
3493
3494 LOOP
3495
3496 FETCH c_get_organization INTO ln_organization_id,
3497 ln_business_group_id;
3498
3499 IF c_get_organization%NOTFOUND THEN
3500 EXIT;
3501 END IF;
3502
3503 pay_emp_action_arch.get_org_other_info(ln_organization_id,
3504 ln_business_group_id);
3505 pay_emp_action_arch.get_org_address(ln_organization_id);
3506
3507 IF ln_business_group_id <> ln_organization_id
3508 THEN
3509
3510 pay_emp_action_arch.get_org_address(ln_business_group_id);
3511
3512 END IF;
3513
3514 ln_tax_unit_id := -1;
3515
3516 OPEN c_get_tax_unit_id(p_assignment_id);
3517 FETCH c_get_tax_unit_id INTO ln_tax_unit_id;
3518 CLOSE c_get_tax_unit_id;
3519
3520 IF ln_tax_unit_id <> -1
3521 AND ln_tax_unit_id <> ln_business_group_id
3522 AND ln_tax_unit_id <> ln_organization_id
3523 THEN
3524
3525 pay_emp_action_arch.get_org_address(ln_tax_unit_id);
3526
3527 END IF;
3528
3529 END LOOP;
3530
3531 CLOSE c_get_organization;
3532
3533 IF pay_emp_action_arch.ltr_ppa_arch.count > 0 THEN
3534
3535 pay_simulator_pkg.insert_simulation_rows(p_action_context_id => p_payroll_action_id
3536 ,p_action_context_type => 'PA'
3537 ,p_assignment_id => p_assignment_id
3538 ,p_tax_unit_id => NULL
3539 ,p_effective_date => p_effective_date
3540 ,p_tab_rec_data => pay_emp_action_arch.ltr_ppa_arch
3541 );
3542
3543 END IF;
3544
3545 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
3546
3547 END archive_payroll_level_data;
3548
3549 /* Procedure : archive_data
3550 Purpose : This procedure captures the results of Payroll Simulator
3551 Run to the table PAY_SIMULATION_INFORMATION. This
3552 procedure is similar to the Payroll Archiver procedure
3553 PAY_US_ACTION_ARCH.ACTION_ARCHIVE_DATA. The regular
3554 Payroll Archiver procedure is based on Prepayments where
3555 as the current procedure is based on actual Payroll Run
3556 itself.
3557 Important : The Data archival process is similar to the regular
3558 Payroll Archiver except the Payment related data will be
3559 skipped here as there are no Prepayments executed as part
3560 of Payroll Simulation. All the data gathered during this
3561 procedure is inserted into PAY_SIMULATION_INFORMATION
3562 table autonomously so that the data can be used to
3563 generate the Output, post Database level rollback. Any
3564 information required for generating Output, that will not
3565 be available due to rollback should be captured through
3566 archive_data procedure
3567 */
3568
3569 PROCEDURE archive_data(p_source_action_id NUMBER,
3570 p_effective_date DATE DEFAULT NULL)
3571
3572 IS
3573
3574 CURSOR c_get_action_info(cp_assignment_action_id NUMBER)
3575 IS
3576 SELECT assignment_id,
3577 payroll_action_id
3578 FROM pay_assignment_actions
3579 WHERE assignment_action_id = cp_assignment_action_id;
3580
3581 CURSOR c_get_payroll_action_info(cp_payroll_action_id NUMBER)
3582 IS
3583 SELECT ppa.business_group_id,
3584 ppa.consolidation_set_id,
3585 ppa.payroll_id,
3586 ppa.time_period_id,
3587 ppa.effective_date
3588 FROM pay_payroll_actions ppa
3589 WHERE payroll_action_id = cp_payroll_action_id;
3590
3591 CURSOR c_get_legislation_code (cp_business_group NUMBER)
3592 IS
3593 SELECT org_information9
3594 FROM hr_organization_information
3595 WHERE org_information_context = 'Business Group Information'
3596 AND organization_id = cp_business_group;
3597
3598 /* Below cursor fetches the Assignment Actions corresponding to the
3599 Master Assignment Action ID. Only the Regular/Supplemental Run and
3600 Separate Check Runs will be fetched by this cursor as data needs
3601 to be archived only for those runs. The Tax Separate Run gets
3602 archived along with the corresponding Regular/Supplemental Run. Only
3603 for Separate Check Runs we need to archive data independently*/
3604
3605 CURSOR c_get_assignment_actions
3606 (cp_master_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
3607 IS
3608 SELECT paa.assignment_action_id,
3609 paa.assignment_id,
3610 paa.tax_unit_id,
3611 paa.payroll_action_id
3612 FROM pay_assignment_actions paa,
3613 pay_run_types_f prt
3614 WHERE paa.source_action_id = cp_master_assignment_action_id
3615 AND paa.run_type_id = prt.run_type_id
3616 AND paa.action_status = 'C'
3617 AND prt.run_method <> 'C'
3618 AND prt.run_method IN ('N','S')
3619 ORDER BY DECODE(prt.run_method,'N',1,2),
3620 paa.assignment_action_id;
3621
3622 lv_procedure_name VARCHAR2(100) := '.archive_data';
3623
3624 ln_business_group_id pay_payroll_actions.business_group_id%TYPE;
3625 ln_consolidation_set_id pay_payroll_actions.consolidation_set_id%TYPE;
3626 ln_payroll_id pay_payroll_actions.payroll_id%TYPE;
3627 ln_time_period_id pay_payroll_actions.time_period_id%TYPE;
3628 ld_effective_date DATE;
3629
3630 lv_payroll_multiple_asgs VARCHAR2(2);
3631
3632 lv_legislation_code VARCHAR2(10);
3633
3634 ln_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
3635 ln_assignment_id pay_assignment_actions.assignment_id%TYPE;
3636 ln_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
3637 ln_payroll_action_id pay_assignment_actions.payroll_action_id%TYPE;
3638
3639 lv_gross_balance pay_balance_types.balance_name%TYPE;
3640 lv_run_dimension_suffix pay_balance_dimensions.database_item_suffix%TYPE;
3641
3642 ln_gross_earnings NUMBER;
3643
3644 lv_archive_balance_info VARCHAR2(2);
3645
3646 BEGIN
3647
3648 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
3649
3650 hr_utility.set_location(gv_package || lv_procedure_name, 10);
3651
3652 /*Get Action related Information*/
3653
3654 OPEN c_get_action_info(p_source_action_id);
3655 FETCH c_get_action_info INTO ln_assignment_id,
3656 ln_payroll_action_id;
3657 CLOSE c_get_action_info;
3658
3659 /*Clean up any old data in the archive table*/
3660
3661 pay_simulator_pkg.clear_previous_sim_data(ln_assignment_id);
3662
3663 /*Fetch Payroll Action Information */
3664
3665 OPEN c_get_payroll_action_info(ln_payroll_action_id);
3666 FETCH c_get_payroll_action_info INTO ln_business_group_id,
3667 ln_consolidation_set_id,
3668 ln_payroll_id,
3669 ln_time_period_id,
3670 ld_effective_date;
3671 CLOSE c_get_payroll_action_info;
3672
3673 IF p_effective_date IS NOT NULL THEN
3674 ld_effective_date := p_effective_date;
3675 END IF;
3676
3677 /*Capture user selected details into archive table*/
3678
3679 pay_simulator_pkg.capture_user_inputs(ln_assignment_id,
3680 p_source_action_id,
3681 ld_effective_date);
3682
3683 OPEN c_get_legislation_code(ln_business_group_id);
3684 FETCH c_get_legislation_code INTO lv_legislation_code;
3685 CLOSE c_get_legislation_code;
3686
3687 IF lv_legislation_code IN ('US','CA','MX') THEN
3688
3689 lv_gross_balance := 'Gross Earnings';
3690 lv_run_dimension_suffix := '_ASG_RUN';
3691
3692 ELSE /* Placeholder for other Localizations */
3693
3694 NULL;
3695
3696 END IF;
3697
3698 hr_utility.set_location(gv_package || lv_procedure_name, 20);
3699
3700 /*Retrieve the Assignment related information*/
3701
3702 OPEN c_get_assignment_actions(p_source_action_id);
3703
3704 FETCH c_get_assignment_actions INTO ln_assignment_action_id,
3705 ln_assignment_id,
3706 ln_tax_unit_id,
3707 ln_payroll_action_id;
3708
3709 WHILE(c_get_assignment_actions%FOUND)
3710
3711 LOOP
3712
3713 ln_gross_earnings := gross_earnings(ln_assignment_action_id,
3714 lv_legislation_code,
3715 lv_gross_balance,
3716 lv_run_dimension_suffix);
3717
3718 IF ln_gross_earnings <> 0 THEN
3719 EXIT;
3720 END IF;
3721
3722 FETCH c_get_assignment_actions INTO ln_assignment_action_id,
3723 ln_assignment_id,
3724 ln_tax_unit_id,
3725 ln_payroll_action_id;
3726
3727 END LOOP;
3728
3729 CLOSE c_get_assignment_actions;
3730
3731 IF ln_gross_earnings = 0
3732 THEN
3733
3734 lv_archive_balance_info := 'N';
3735
3736 ELSE
3737
3738 lv_archive_balance_info := 'Y';
3739
3740 END IF;
3741
3742 /* Removed the Earnings check so that Employee details get archived in
3743 event of no earnings present in run. Otherwise a Blank PDF will get
3744 generated not with Employee details also */
3745
3746 /*IF ln_gross_earnings <> 0
3747 THEN */
3748
3749 IF lv_legislation_code IN ('US','CA') THEN
3750
3751 process_actions(p_payroll_action_id => ln_payroll_action_id
3752 ,p_source_action_id => p_source_action_id
3753 ,p_legislation_code => lv_legislation_code
3754 ,p_business_group_id => ln_business_group_id
3755 ,p_assignment_id => ln_assignment_id
3756 ,p_payroll_id => ln_payroll_id
3757 ,p_consolidation_set_id => ln_consolidation_set_id
3758 ,p_tax_unit_id => ln_tax_unit_id
3759 ,p_effective_date => ld_effective_date
3760 ,p_archive_balance_info => lv_archive_balance_info
3761 );
3762
3763 ELSE /* Placeholder for other localizations */
3764
3765 NULL;
3766
3767 END IF;
3768
3769 /*END IF;*/
3770
3771 /*Archive Payroll Action Level Data */
3772
3773 IF lv_legislation_code IN ('US') THEN
3774
3775 archive_payroll_level_data(p_payroll_action_id => ln_payroll_action_id
3776 ,p_payroll_id => ln_payroll_id
3777 ,p_assignment_id => ln_assignment_id
3778 ,p_effective_date => ld_effective_date
3779 );
3780
3781 /*Since the Assignment Action will be rolled back, it is necessary to
3782 capture the extra information which we usually retrieve based on Assignment Action
3783 like Time Period ID, Tax Unit ID, etc.*/
3784
3785 archive_action_information(p_payroll_action_id => ln_payroll_action_id
3786 ,p_source_action_id => p_source_action_id
3787 ,p_assignment_id => ln_assignment_id
3788 ,p_effective_date => ld_effective_date
3789 ,p_payroll_id => ln_payroll_id
3790 ,p_time_period_id => ln_time_period_id
3791 ,p_legislation_code => lv_legislation_code
3792 ,p_business_group_id => ln_business_group_id
3793 ,p_tax_unit_id => ln_tax_unit_id);
3794
3795 ELSE /* Placeholder for other localizations */
3796
3797 NULL;
3798
3799 END IF;
3800
3801 /*End of Payroll Action Level Data archiving */
3802
3803 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
3804
3805 END archive_data;
3806
3807 PROCEDURE build_sql(
3808 p_sqlstr_tab IN OUT NOCOPY DBMS_SQL.VARCHAR2S,
3809 p_cntr IN OUT NOCOPY NUMBER,
3810 p_string VARCHAR2)
3811
3812 IS
3813
3814 lv_procedure_name varchar2(100) := '.build_sql';
3815
3816 BEGIN
3817
3818 hr_utility.trace ('Entering '||gv_package||lv_procedure_name);
3819
3820 p_sqlstr_tab(p_cntr) := p_string;
3821 p_cntr := p_cntr + 1;
3822
3823 hr_utility.trace ('Leaving '||gv_package||lv_procedure_name);
3824
3825 END build_sql;
3826
3827 PROCEDURE print_blob(p_blob BLOB)
3828
3829 IS
3830
3831 BEGIN
3832
3833 IF g_debug THEN
3834 pay_ac_utility.print_lob(p_blob);
3835 END IF;
3836
3837 END print_blob;
3838
3839 /* Procedure : flex_seg_enabled
3840 Purpose : This function returns TRUE if an application column is
3841 registered with given context of Action Information DF.
3842 Otherwise, it returns false.
3843 Important :
3844 */
3845
3846 FUNCTION flex_seg_enabled(
3847 p_context_code VARCHAR2,
3848 p_application_column_name VARCHAR2)
3849 RETURN BOOLEAN
3850
3851 IS
3852
3853 CURSOR csr_seg_enabled IS
3854 SELECT 'Y'
3855 FROM fnd_descr_flex_col_usage_vl
3856 WHERE descriptive_flexfield_name like 'Action Information DF'
3857 AND descriptive_flex_context_code = p_context_code
3858 AND application_column_name like p_application_column_name
3859 AND application_id = 801 /* Bug 6522667 */
3860 AND enabled_flag = 'Y';
3861
3862 lv_procedure_name VARCHAR2(100);
3863 l_exists VARCHAR2(1);
3864
3865 BEGIN
3866
3867 lv_procedure_name := '.flex_seg_enabled';
3868
3869 hr_utility.trace ('Entering '||gv_package||lv_procedure_name);
3870
3871 OPEN csr_seg_enabled;
3872 FETCH csr_seg_enabled INTO l_exists;
3873 CLOSE csr_seg_enabled;
3874
3875 hr_utility.trace ('Leaving '||gv_package||lv_procedure_name);
3876
3877 IF l_exists = 'Y' THEN
3878 RETURN (TRUE);
3879 ELSE
3880 RETURN (FALSE);
3881 END IF;
3882
3883 END flex_seg_enabled;
3884
3885 /* Procedure : simulation_generate
3886 Purpose : This procedure interprets archived information, converts it to
3887 XML and prints it to a BLOB. This is a private procedure. This
3888 is created based on the procedure pay_payroll_xml_extract_pkg.
3889 generate_internal which is used by Regular Payslip
3890 Important :
3891 */
3892
3893 PROCEDURE simulation_generate (
3894 p_action_information_id NUMBER DEFAULT NULL,
3895 p_action_context_id NUMBER,
3896 p_custom_action_info_cat VARCHAR2 DEFAULT NULL,
3897 p_custom_xml_procedure VARCHAR2,
3898 p_generate_header_flag VARCHAR2, -- {Y/N}
3899 p_root_tag VARCHAR2,
3900 p_document_type VARCHAR2,
3901 p_xml OUT NOCOPY BLOB)
3902
3903 IS
3904
3905 lt_per_bgId_value NUMBER;
3906
3907 lv_procedure_name VARCHAR2(100);
3908 sqlstr DBMS_SQL.VARCHAR2S;
3909 l_cntr_sql NUMBER;
3910 l_xml BLOB;
3911 csr NUMBER;
3912 ret NUMBER;
3913 cntr_flex_col NUMBER;
3914 l_flex_col_num NUMBER;
3915 /* Max. number of flex segments in Action Informtion DF */
3916 l_kff_seg_start NUMBER;
3917 /* Segment number where bank KFF segments start. Currently, it is
3918 ACTION_INFORMATION5, so, l_kff_seg_start = 5 */
3919 l_kff_seg_end NUMBER;
3920 /* Segment number where bank KFF segments end. Currently, it is
3921 ACTION_INFORMATION14, so, l_kff_seg_end = 14 */
3922
3923 l_action_information_id VARCHAR2(100);
3924 l_action_context_id VARCHAR2(100);
3925 lr_xml RAW (32767);
3926 ln_amt NUMBER;
3927
3928 BEGIN
3929
3930 lv_procedure_name := '.simulation_generate';
3931
3932 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
3933
3934 hr_utility.trace('Parameters ....');
3935 hr_utility.trace('P_ACTION_INFORMATION_ID ='||P_ACTION_INFORMATION_ID);
3936 hr_utility.trace('P_ACTION_CONTEXT_ID ='||P_ACTION_CONTEXT_ID);
3937
3938 IF p_generate_header_flag = 'Y' THEN
3939 hr_utility.trace ('P_GENERATE_HEADER_FLAG = TRUE');
3940 ELSE
3941 hr_utility.trace ('P_GENERATE_HEADER_FLAG = FALSE');
3942 END IF;
3943
3944 hr_utility.trace ('P_CUSTOM_ACTION_INFO_CAT ='||P_CUSTOM_ACTION_INFO_CAT);
3945
3946 pay_payroll_xml_extract_pkg.g_xml_table.delete();
3947 l_flex_col_num := 30;
3948 l_kff_seg_start := 5;
3949 l_kff_seg_end := 14;
3950 l_cntr_sql := 1;
3951 g_action_ctx_id := p_action_context_id;
3952 g_custom_context := TRANSLATE(p_custom_action_info_cat, ' /', '__');
3953
3954 SELECT DECODE (p_action_information_id,
3955 NULL, 'NULL',
3956 to_char(p_action_information_id)),
3957 DECODE (p_action_context_id,
3958 NULL, 'NULL',
3959 to_char(p_action_context_id))
3960 INTO l_action_information_id,
3961 l_action_context_id
3962 FROM DUAL;
3963
3964 build_sql(sqlstr, l_cntr_sql, 'declare l_org_id varchar2(100);');
3965
3966 build_sql(sqlstr, l_cntr_sql, 'lt_pay_date DATE := TO_DATE(''0001/01/01'',''YYYY/MM/DD'');');
3967 build_sql(sqlstr, l_cntr_sql, 'lt_state_cd VARCHAR2(2);');
3968 build_sql(sqlstr, l_cntr_sql, 'lt_element_type VARCHAR2(240);');
3969 build_sql(sqlstr, l_cntr_sql, 'lt_ret_val VARCHAR2(2);');
3970 build_sql(sqlstr, l_cntr_sql, 'lt_rate_flg VARCHAR2(2);');
3971
3972 build_sql(sqlstr, l_cntr_sql, 'begin ');
3973
3974 build_sql(sqlstr, l_cntr_sql, 'lt_state_cd := NULL;');
3975 build_sql(sqlstr, l_cntr_sql, 'lt_element_type := NULL;');
3976 build_sql(sqlstr, l_cntr_sql, 'lt_ret_val := ''N'';');
3977 build_sql(sqlstr, l_cntr_sql, 'lt_rate_flg := ''N'';');
3978
3979 lt_per_bgId_value := 0;
3980
3981 /* Fetch Businesss Grp Id */
3982
3983 OPEN pay_us_payslip_simulation_main.c_bgid(g_action_ctx_id);
3984 FETCH pay_us_payslip_simulation_main.c_bgid INTO lt_per_bgId_value;
3985 CLOSE pay_us_payslip_simulation_main.c_bgid;
3986
3987 IF p_generate_header_flag = 'Y' THEN
3988 build_sql(sqlstr,
3989 l_cntr_sql,
3990 'pay_payroll_xml_extract_pkg.load_xml(''CS'', NULL, ''?xml version="1.0" encoding="'||
3991 hr_mx_utility.get_IANA_charset||'"?'', NULL);');
3992 END IF;
3993
3994 IF p_root_tag IS NOT NULL THEN
3995 build_sql(sqlstr,
3996 l_cntr_sql,
3997 'pay_payroll_xml_extract_pkg.load_xml(''CS'', NULL, '''||
3998 p_root_tag||''', NULL);');
3999 END IF;
4000
4001 /* Fetch legislation_code. */
4002 OPEN pay_us_payslip_simulation_main.get_leg_code(p_action_context_id);
4003 FETCH pay_us_payslip_simulation_main.get_leg_code INTO pay_payroll_xml_extract_pkg.g_leg_code;
4004 CLOSE pay_us_payslip_simulation_main.get_leg_code;
4005
4006 /* Checking for International Payroll Legislation,
4007 The flag g_install_leg_check returns 'N' for International Payroll localization and 'Y' for supported legislations.
4008 */
4009
4010 g_install_leg_check := pay_ip_utility.get_ip_installation(pay_payroll_xml_extract_pkg.g_leg_code);
4011
4012 FOR csr_get_archived_regions_rec IN pay_us_payslip_simulation_main.csr_get_archived_regions(p_action_context_id,
4013 p_action_information_id)
4014 LOOP
4015
4016 IF csr_get_archived_regions_rec.action_information_category IN
4017 ('ADDRESS DETAILS', pay_payroll_xml_extract_pkg.g_leg_code ||
4018 ' EMPLOYER DETAILS') THEN
4019 build_sql(sqlstr,
4020 l_cntr_sql,
4021 'FOR csr_get_sim_arch_info_rec IN pay_us_payslip_simulation_main.csr_get_sim_archived_info (:l_action_context_id,'''||
4022 csr_get_archived_regions_rec.action_information_category
4023 ||''', l_org_id, :l_action_information_id) LOOP ');
4024 ELSE
4025 build_sql(sqlstr,
4026 l_cntr_sql,
4027 'FOR csr_get_sim_arch_info_rec IN pay_us_payslip_simulation_main.csr_get_sim_archived_info (:l_action_context_id,'''||
4028 csr_get_archived_regions_rec.action_information_category
4029 ||''', NULL, :l_action_information_id) LOOP ');
4030 END IF;
4031 build_sql(sqlstr,
4032 l_cntr_sql,
4033 'pay_payroll_xml_extract_pkg.load_xml(''CS'', NULL, ''' ||
4034 csr_get_archived_regions_rec.action_information_category ||
4035 ''', NULL);');
4036
4037 IF csr_get_archived_regions_rec.action_information_category = 'EMPLOYEE DETAILS'
4038 AND pay_payroll_xml_extract_pkg.g_leg_code = 'US' THEN
4039
4040 build_sql(sqlstr, l_cntr_sql, 'lt_pay_date := csr_get_sim_arch_info_rec.effective_date;');
4041
4042 build_sql(sqlstr,l_cntr_sql,
4043 ' SELECT NVL((select lei_information13 from hr_location_extra_info hle ,HR_LOCATIONS_ALL HLA '||
4044 'WHERE hle.information_type = ''HR_SELF_SERVICE_LOC_PREFERENCE''');
4045
4046 build_sql(sqlstr,l_cntr_sql,
4047 'AND hle.lei_information1 =''PAYSLIP'' AND hla.LOCATION_ID = hle.LOCATION_ID AND '||
4048 'hla.LOCATION_CODE=csr_get_sim_arch_info_rec.action_information30),''K'') INTO lt_rate_flg FROM DUAL;');
4049
4050 build_sql(sqlstr,l_cntr_sql,'IF lt_rate_flg = ''K'' THEN');
4051
4052 build_sql(sqlstr,l_cntr_sql,
4053 ' SELECT NVL((select org_information13 FROM hr_organization_information '||
4054 'WHERE org_information_context = ''HR_SELF_SERVICE_ORG_PREFERENCE'' and ORG_INFORMATION1=''PAYSLIP''');
4055 build_sql(sqlstr,l_cntr_sql,
4056 'AND organization_id=csr_get_sim_arch_info_rec.action_information2),''K'') INTO lt_rate_flg FROM DUAL ;');
4057 build_sql(sqlstr,l_cntr_sql,'IF lt_rate_flg = ''K'' THEN ');
4058 build_sql(sqlstr,l_cntr_sql,
4059 ' SELECT NVL((select org_information13 FROM hr_organization_information '||
4060 'WHERE org_information_context = ''HR_SELF_SERVICE_BG_PREFERENCE'' and ORG_INFORMATION1=''PAYSLIP''');
4061 build_sql(sqlstr,l_cntr_sql,
4062 'AND organization_id=csr_get_sim_arch_info_rec.action_information2),''K'') INTO lt_rate_flg FROM DUAL;');
4063 build_sql(sqlstr,l_cntr_sql,'IF lt_rate_flg = ''K'' THEN ');
4064 build_sql(sqlstr,l_cntr_sql,' lt_rate_flg := ''N'';');
4065 build_sql(sqlstr,l_cntr_sql,'END IF;'||'END IF;'||'END IF;');
4066 build_sql(sqlstr,l_cntr_sql,'pay_payroll_xml_extract_pkg.load_xml(''D'', ''RATE_DISP_FLG'', lt_rate_flg);');
4067
4068 END IF;
4069
4070 IF csr_get_archived_regions_rec.action_information_category = 'AC EARNINGS'
4071 AND pay_payroll_xml_extract_pkg.g_leg_code = 'US' THEN
4072
4073 build_sql(sqlstr, l_cntr_sql, 'lt_state_cd := substr(csr_get_sim_arch_info_rec.action_information21,1,2);');
4074 build_sql(sqlstr, l_cntr_sql, 'lt_element_type := csr_get_sim_arch_info_rec.action_information20;');
4075 build_sql(sqlstr, l_cntr_sql,
4076 'SELECT NVL(hruserdt.get_table_value('||lt_per_bgId_value||',''ONLINE_PAYSLIP_DATE_CONTROL_TBL'',lt_state_cd'||
4077 ',lt_element_type,lt_pay_date),''N'') INTO lt_ret_val FROM DUAL ;');
4078 build_sql(sqlstr,l_cntr_sql,
4079 ' pay_payroll_xml_extract_pkg.load_xml(''D'', ''DATE_DISP_FLG'', lt_ret_val);');
4080
4081 END IF;
4082
4083 IF csr_get_archived_regions_rec.action_information_category =
4084 'EMPLOYEE NET PAY DISTRIBUTION'
4085 AND g_install_leg_check = 'N' THEN
4086
4087 hr_utility.trace('Entering employee net pay '||g_install_leg_check);
4088
4089 build_sql(sqlstr,l_cntr_sql,
4090 'csr_get_sim_arch_info_rec.action_information4 :=
4091 pay_ip_utility.get_check_number(csr_get_sim_arch_info_rec.action_information17
4092 ,csr_get_sim_arch_info_rec.action_information15);');
4093
4094 END IF;
4095
4096 cntr_flex_col := 1;
4097
4098 LOOP
4099
4100 EXIT WHEN cntr_flex_col > l_flex_col_num;
4101
4102 IF flex_seg_enabled (
4103 csr_get_archived_regions_rec.action_information_category,
4104 'ACTION_INFORMATION'||cntr_flex_col) THEN
4105
4106 IF csr_get_archived_regions_rec.action_information_category =
4107 'EMPLOYEE NET PAY DISTRIBUTION'
4108 AND cntr_flex_col BETWEEN l_kff_seg_start
4109 AND l_kff_seg_end THEN
4110 build_sql(sqlstr,
4111 l_cntr_sql,
4112 'pay_payroll_xml_extract_pkg.load_xml(''D'', ''Segment'
4113 || (cntr_flex_col - l_kff_seg_start + 1) ||
4114 ''', LTRIM(RTRIM(csr_get_sim_arch_info_rec.action_information'
4115 || cntr_flex_col ||')));');
4116 ELSE
4117
4118 IF csr_get_archived_regions_rec.action_information_category =
4119 'EMPLOYEE THIRD PARTY PAYMENTS'
4120 AND pay_payroll_xml_extract_pkg.g_leg_code = 'US' THEN
4121
4122 build_sql(sqlstr,l_cntr_sql,
4123 'csr_get_sim_arch_info_rec.action_information4 :=
4124 pay_us_employee_payslip_web.get_check_number(csr_get_sim_arch_info_rec.action_information17
4125 ,csr_get_sim_arch_info_rec.action_information15);');
4126
4127 build_sql(sqlstr,l_cntr_sql,
4128 'csr_get_sim_arch_info_rec.action_information7 :=
4129 HR_GENERAL2.mask_characters(csr_get_sim_arch_info_rec.action_information7);');
4130
4131 END IF;
4132
4133 build_sql(sqlstr,l_cntr_sql,
4134 'pay_payroll_xml_extract_pkg.load_xml(''D'', '''
4135 || csr_get_archived_regions_rec.action_information_category ||
4136 ''', ''ACTION_INFORMATION'|| cntr_flex_col ||
4137 ''', LTRIM(RTRIM(csr_get_sim_arch_info_rec.action_information'
4138 || cntr_flex_col ||')));');
4139
4140 END IF;
4141
4142 END IF;
4143
4144 cntr_flex_col := cntr_flex_col + 1;
4145
4146 END LOOP;
4147
4148 /*Generate payroll details from time period id (action_information16)*/
4149 IF csr_get_archived_regions_rec.action_information_category =
4150 'EMPLOYEE DETAILS' THEN
4151 build_sql(sqlstr,
4152 l_cntr_sql,
4153 'FOR csr_payroll_details_rec IN pay_payroll_xml_extract_pkg.csr_payroll_details(csr_get_sim_arch_info_rec.action_information16) LOOP ');
4154 build_sql(sqlstr,
4155 l_cntr_sql,
4156 'pay_payroll_xml_extract_pkg.load_xml(''D'', NULL, ''PAYROLL_NAME'', csr_payroll_details_rec.payroll_name);');
4157 build_sql(sqlstr,
4158 l_cntr_sql,
4159 'pay_payroll_xml_extract_pkg.load_xml(''D'', NULL, ''PERIOD_TYPE'', csr_payroll_details_rec.period_type);');
4160 build_sql(sqlstr,
4161 l_cntr_sql,
4162 'pay_payroll_xml_extract_pkg.load_xml(''D'', NULL, ''START_DATE'', csr_payroll_details_rec.start_date);');
4163 build_sql(sqlstr,
4164 l_cntr_sql,
4165 'pay_payroll_xml_extract_pkg.load_xml(''D'', NULL, ''END_DATE'', csr_payroll_details_rec.end_date);');
4166 build_sql(sqlstr,
4167 l_cntr_sql,
4168 'pay_payroll_xml_extract_pkg.load_xml(''D'', NULL, ''PAYMENT_DATE'', substr(fnd_date.date_to_canonical(csr_get_sim_arch_info_rec.effective_date),1,10));');
4169 build_sql(sqlstr,
4170 l_cntr_sql,
4171 'END LOOP;');
4172 build_sql(sqlstr,
4173 l_cntr_sql,
4174 'l_org_id := csr_get_sim_arch_info_rec.action_information2;');
4175
4176 END IF;
4177
4178 -- Localization procedure call
4179
4180 build_sql(sqlstr,
4181 l_cntr_sql,
4182 'BEGIN ');
4183
4184 IF g_install_leg_check = 'N' THEN
4185
4186 build_sql(sqlstr,
4187 l_cntr_sql,
4188 'EXECUTE IMMEDIATE (''BEGIN pay_'||
4189 'ip'||
4190 '_simulation.add_custom_xml('||l_action_context_id||
4191 ', '''''|| csr_get_archived_regions_rec.action_information_category ||
4192 ''''', '''''|| p_document_type ||
4193 '''''); END;'');');
4194
4195 ELSE
4196
4197 build_sql(sqlstr,
4198 l_cntr_sql,
4199 'EXECUTE IMMEDIATE (''BEGIN pay_'||
4200 pay_payroll_xml_extract_pkg.g_leg_code||
4201 '_simulation.add_custom_xml('||l_action_context_id||
4202 ', '''''|| csr_get_archived_regions_rec.action_information_category ||
4203 ''''', '''''|| p_document_type ||
4204 '''''); END;'');');
4205
4206 END IF;
4207
4208 build_sql(sqlstr,
4209 l_cntr_sql,
4210 'IF pay_payroll_xml_extract_pkg.g_custom_xml.count() > 0 THEN ');
4211 build_sql(sqlstr,
4212 l_cntr_sql,
4213 'pay_payroll_xml_extract_pkg.load_xml(''<!-- Following segment(s) were added by PAY_'||
4214 pay_payroll_xml_extract_pkg.g_leg_code||
4215 '_SIMULATION.ADD_CUSTOM_XML -->'');');
4216 build_sql(sqlstr,
4217 l_cntr_sql,
4218 'FOR cntr IN pay_payroll_xml_extract_pkg.g_custom_xml.first()..pay_payroll_xml_extract_pkg.g_custom_xml.last() LOOP ');
4219 build_sql(sqlstr,
4220 l_cntr_sql,
4221 'pay_payroll_xml_extract_pkg.load_xml(pay_payroll_xml_extract_pkg.g_custom_xml(cntr));');
4222 build_sql(sqlstr,
4223 l_cntr_sql,
4224 'END LOOP;');
4225 build_sql(sqlstr,
4226 l_cntr_sql,
4227 'pay_payroll_xml_extract_pkg.g_custom_xml.delete();');
4228 build_sql(sqlstr,
4229 l_cntr_sql,
4230 'END IF;');
4231 build_sql(sqlstr,
4232 l_cntr_sql,
4233 'EXCEPTION ');
4234 build_sql(sqlstr,
4235 l_cntr_sql,
4236 'WHEN OTHERS THEN NULL;');
4237 build_sql(sqlstr,
4238 l_cntr_sql,
4239 'END;');
4240
4241 -- Custom procedure call
4242 build_sql(sqlstr,
4243 l_cntr_sql,
4244 'BEGIN ');
4245 build_sql(sqlstr,
4246 l_cntr_sql,
4247 'EXECUTE IMMEDIATE (''BEGIN '||p_custom_xml_procedure||
4248 '('||l_action_context_id||
4249 ', '''''||
4250 csr_get_archived_regions_rec.action_information_category ||
4251 ''''', '''''|| p_document_type ||
4252 '''''); END;'');');
4253 build_sql(sqlstr,
4254 l_cntr_sql,
4255 'IF pay_payroll_xml_extract_pkg.g_custom_xml.count() > 0 THEN ');
4256 build_sql(sqlstr,
4257 l_cntr_sql,
4258 'pay_payroll_xml_extract_pkg.load_xml(''<!-- Following segment(s) were added by '||
4259 UPPER(p_custom_xml_procedure)||' -->'');');
4260 build_sql(sqlstr,
4261 l_cntr_sql,
4262 'FOR cntr IN pay_payroll_xml_extract_pkg.g_custom_xml.first()..pay_payroll_xml_extract_pkg.g_custom_xml.last() LOOP ');
4263 build_sql(sqlstr,
4264 l_cntr_sql,
4265 'pay_payroll_xml_extract_pkg.load_xml(pay_payroll_xml_extract_pkg.g_custom_xml(cntr));');
4266 build_sql(sqlstr,
4267 l_cntr_sql,
4268 'END LOOP;');
4269 build_sql(sqlstr,
4270 l_cntr_sql,
4271 'pay_payroll_xml_extract_pkg.g_custom_xml.delete();');
4272 build_sql(sqlstr,
4273 l_cntr_sql,
4274 'END IF;');
4275 build_sql(sqlstr,
4276 l_cntr_sql,
4277 'EXCEPTION ');
4278 build_sql(sqlstr,
4279 l_cntr_sql,
4280 'WHEN OTHERS THEN NULL;');
4281 build_sql(sqlstr,
4282 l_cntr_sql,
4283 'END;');
4284
4285 build_sql(sqlstr,
4286 l_cntr_sql,
4287 'pay_payroll_xml_extract_pkg.load_xml(''CE'', NULL, ''' ||
4288 csr_get_archived_regions_rec.action_information_category ||
4289 ''', NULL);');
4290 build_sql(sqlstr,
4291 l_cntr_sql,
4292 'END LOOP;');
4293 END LOOP;
4294
4295 -- Localization procedure call
4296 build_sql(sqlstr,
4297 l_cntr_sql,
4298 'BEGIN ');
4299
4300 IF g_install_leg_check = 'N' THEN
4301
4302 build_sql(sqlstr,
4303 l_cntr_sql,
4304 'EXECUTE IMMEDIATE (''BEGIN pay_'||
4305 'ip'||
4306 '_simulation.add_custom_xml('||l_action_context_id||', NULL'||
4307 ', '''''|| p_document_type ||
4308 '''''); END;'');');
4309 ELSE
4310
4311 build_sql(sqlstr,
4312 l_cntr_sql,
4313 'EXECUTE IMMEDIATE (''BEGIN pay_'||
4314 pay_payroll_xml_extract_pkg.g_leg_code||
4315 '_simulation.add_custom_xml('||l_action_context_id||', NULL'||
4316 ', '''''|| p_document_type ||
4317 '''''); END;'');');
4318
4319 END IF;
4320
4321 build_sql(sqlstr,
4322 l_cntr_sql,
4323 'IF pay_payroll_xml_extract_pkg.g_custom_xml.count() > 0 THEN ');
4324 build_sql(sqlstr,
4325 l_cntr_sql,
4326 'pay_payroll_xml_extract_pkg.load_xml(''<!-- Following context(s) were added by PAY_'||
4327 pay_payroll_xml_extract_pkg.g_leg_code||
4328 '_SIMULATION.ADD_CUSTOM_XML -->'');');
4329 build_sql(sqlstr,
4330 l_cntr_sql,
4331 'FOR cntr IN pay_payroll_xml_extract_pkg.g_custom_xml.first()..pay_payroll_xml_extract_pkg.g_custom_xml.last() LOOP ');
4332 build_sql(sqlstr,
4333 l_cntr_sql,
4334 'pay_payroll_xml_extract_pkg.load_xml(pay_payroll_xml_extract_pkg.g_custom_xml(cntr));');
4335 build_sql(sqlstr,
4336 l_cntr_sql,
4337 'END LOOP;');
4338 build_sql(sqlstr,
4339 l_cntr_sql,
4340 'pay_payroll_xml_extract_pkg.g_custom_xml.delete();');
4341 build_sql(sqlstr,
4342 l_cntr_sql,
4343 'END IF;');
4344 build_sql(sqlstr,
4345 l_cntr_sql,
4346 'EXCEPTION ');
4347 build_sql(sqlstr,
4348 l_cntr_sql,
4349 'WHEN OTHERS THEN NULL;');
4350 build_sql(sqlstr,
4351 l_cntr_sql,
4352 'END;');
4353
4354 -- Custom procedure call
4355 build_sql(sqlstr,
4356 l_cntr_sql,
4357 'BEGIN ');
4358 build_sql(sqlstr,
4359 l_cntr_sql,
4360 'EXECUTE IMMEDIATE (''BEGIN '||p_custom_xml_procedure||
4361 '('||l_action_context_id||', NULL'||
4362 ', '''''|| p_document_type ||
4363 '''''); END;'');');
4364 build_sql(sqlstr,
4365 l_cntr_sql,
4366 'IF pay_payroll_xml_extract_pkg.g_custom_xml.count() > 0 THEN ');
4367 build_sql(sqlstr,
4368 l_cntr_sql,
4369 'pay_payroll_xml_extract_pkg.load_xml(''<!-- Following segment(s) were added by '||
4370 UPPER(p_custom_xml_procedure)||' -->'');');
4371 build_sql(sqlstr,
4372 l_cntr_sql,
4373 'FOR cntr IN pay_payroll_xml_extract_pkg.g_custom_xml.first()..pay_payroll_xml_extract_pkg.g_custom_xml.last() LOOP ');
4374 build_sql(sqlstr,
4375 l_cntr_sql,
4376 'pay_payroll_xml_extract_pkg.load_xml(pay_payroll_xml_extract_pkg.g_custom_xml(cntr));');
4377 build_sql(sqlstr,
4378 l_cntr_sql,
4379 'END LOOP;');
4380 build_sql(sqlstr,
4381 l_cntr_sql,
4382 'pay_payroll_xml_extract_pkg.g_custom_xml.delete();');
4383 build_sql(sqlstr,
4384 l_cntr_sql,
4385 'END IF;');
4386 build_sql(sqlstr,
4387 l_cntr_sql,
4388 'EXCEPTION ');
4389 build_sql(sqlstr,
4390 l_cntr_sql,
4391 'WHEN OTHERS THEN NULL;');
4392 build_sql(sqlstr,
4393 l_cntr_sql,
4394 'END;');
4395
4396 IF p_root_tag IS NOT NULL THEN
4397 build_sql(sqlstr,
4398 l_cntr_sql,
4399 'pay_payroll_xml_extract_pkg.load_xml(''CE'', NULL, '''||
4400 p_root_tag||''', NULL);');
4401 END IF;
4402
4403 build_sql(sqlstr,
4404 l_cntr_sql,
4405 'null;');
4406 build_sql(sqlstr,
4407 l_cntr_sql,
4408 'end;');
4409
4410 FOR cntr IN sqlstr.first()..sqlstr.last() LOOP
4411 hr_utility.trace(sqlstr(cntr));
4412 END LOOP;
4413
4414 csr := dbms_sql.open_cursor;
4415 dbms_sql.parse (csr,
4416 sqlstr,
4417 sqlstr.first(),
4418 sqlstr.last(),
4419 false,
4420 dbms_sql.v7);
4421 dbms_sql.bind_variable (csr,
4422 ':l_action_context_id',
4423 p_action_context_id);
4424 dbms_sql.bind_variable (csr,
4425 ':l_action_information_id',
4426 p_action_information_id);
4427 ret := dbms_sql.execute(csr);
4428 dbms_sql.close_cursor(csr);
4429
4430 IF pay_payroll_xml_extract_pkg.g_xml_table.count() <> 0 THEN
4431 dbms_lob.createTemporary(l_xml, true, dbms_lob.session);
4432 FOR cntr IN pay_payroll_xml_extract_pkg.g_xml_table.first()..pay_payroll_xml_extract_pkg.g_xml_table.last() LOOP
4433 lr_xml := utl_raw.cast_to_raw(pay_payroll_xml_extract_pkg.g_xml_table(cntr));
4434 ln_amt := utl_raw.length(lr_xml);
4435
4436 dbms_lob.writeAppend(l_xml,
4437 ln_amt,
4438 lr_xml);
4439
4440 hr_utility.trace (pay_payroll_xml_extract_pkg.g_xml_table(cntr));
4441 END LOOP;
4442 p_xml := l_xml;
4443 dbms_lob.freeTemporary(l_xml);
4444 END IF;
4445
4446 print_blob (p_xml);
4447
4448 -- Unset globals before exit;
4449 pay_payroll_xml_extract_pkg.g_xml_table.delete();
4450 g_custom_context := NULL;
4451
4452 hr_utility.trace ('Leaving '||gv_package||lv_procedure_name);
4453
4454 END simulation_generate;
4455
4456
4457 /* Procedure : generate_xml
4458 Purpose : This procedure is used to generate the XML Data necessary
4459 to generate the Payroll Simulation Output. This procedure
4460 is similar to the current XML generation procedure
4461 PAY_PAYROLL_XML_EXTRACT_PKG.GENERATE.
4462 Important : This procedure will be executed by Core Payroll post the
4463 Database level rollback of changes made during Payroll
4464 Simulation run. Hence any information required for
4465 generating Output, that will not be available due to
4466 rollback should be captured through archive_data procedure
4467 and saved autonomously to PAY_SIMULATION_INFORMATION table
4468 Parameter p_xml_code is to allow the Customers the ability
4469 to use Custom XML Code. This is hidden from Customers as
4470 of now and will be enabled if required in future. The
4471 entire code to support Custom XML Code is already in
4472 place. We need to add the Segment to input Custom XML Code
4473 details at Business Group and Organization Level in "Self
4474 Service Preference". The Segment name should be "Payroll
4475 Simulator XML Code".
4476 */
4477
4478 PROCEDURE generate_xml(p_assignment_id IN NUMBER,
4479 p_xml_code IN VARCHAR2 DEFAULT NULL,
4480 p_xml OUT NOCOPY BLOB)
4481
4482 IS
4483
4484 CURSOR get_asg_action(cp_assignment_id NUMBER)
4485 IS
4486 SELECT psi.action_information2
4487 FROM pay_simulation_information psi
4488 WHERE psi.assignment_id = cp_assignment_id
4489 AND psi.action_information_category = 'SIMULATION_ACTION_INFORMATION'
4490 AND psi.action_context_type = 'ACTION INFO'
4491 ORDER BY psi.action_information1 DESC,psi.action_information2 DESC;
4492
4493 lv_procedure_name VARCHAR2(100) := '.generate_xml';
4494
4495 ln_assignment_action_id NUMBER;
4496
4497 BEGIN
4498
4499 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
4500
4501 OPEN get_asg_action(p_assignment_id);
4502 FETCH get_asg_action INTO ln_assignment_action_id;
4503 CLOSE get_asg_action;
4504
4505 simulation_generate(p_action_context_id => ln_assignment_action_id
4506 ,p_custom_xml_procedure => p_xml_code
4507 ,p_generate_header_flag => 'Y'
4508 ,p_root_tag => 'PAYSLIP'
4509 ,p_document_type => 'PAYSLIP'
4510 ,p_xml => p_xml);
4511
4512 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
4513
4514 END generate_xml;
4515
4516 /* Procedure : pre_processing
4517 Purpose : This procedure is to execute any Pre-Processing tasks that
4518 need to be carried before Payroll Simulation page is made
4519 available to the user. For US Localization, we are using
4520 this procedure to determine all those "Element Name -
4521 Input Value" combinations applicable to the current
4522 Assignment. These combinations are stored to table
4523 PAY_SIMULATION_INFORMATION with ACTION_CONTEXT_TYPE as
4524 "INPUTSLOV". This data is used by the LOV Queries related
4525 to Earnings and Deductions regions of Payroll Simulator.
4526 Important : This procedure is initiated from Payroll Simulator Page
4527 and the data gathered by this procedure will not be
4528 committed to the database. As of now, we are using it to
4529 determine "Element Name-Input Value" combinations for
4530 Value Set Queries as this can improve the performance of
4531 LOVs. In future, if required, additional tasks can be
4532 added as required.
4533 */
4534
4535 PROCEDURE pre_processing(p_assignment_id NUMBER,
4536 p_business_group_id NUMBER DEFAULT NULL,
4537 p_legislation_code VARCHAR2 DEFAULT NULL,
4538 p_effective_date DATE DEFAULT NULL)
4539
4540 IS
4541
4542 CURSOR get_effective_date
4543 IS
4544 SELECT fs.effective_date
4545 FROM fnd_sessions fs
4546 WHERE fs.session_id = USERENV('SESSIONID');
4547
4548 CURSOR get_details(cp_assignment_id NUMBER,
4549 cp_effective_date DATE)
4550 IS
4551 SELECT paaf.person_id,
4552 paaf.business_group_id,
4553 paaf.payroll_id,
4554 paaf.pay_basis_id
4555 FROM per_all_assignments_f paaf,
4556 fnd_sessions fs
4557 WHERE paaf.assignment_id = cp_assignment_id
4558 AND cp_effective_date BETWEEN paaf.effective_start_date
4559 AND paaf.effective_end_date;
4560
4561 CURSOR get_period_type(cp_payroll_id NUMBER,
4562 cp_effective_date DATE)
4563 IS
4564 SELECT ppf.period_type
4565 FROM pay_payrolls_f ppf
4566 WHERE ppf.payroll_id = cp_payroll_id
4567 AND cp_effective_date BETWEEN ppf.effective_start_date
4568 AND ppf.effective_end_date;
4569
4570 CURSOR get_legislation_code(cp_business_group_id NUMBER)
4571 IS
4572 SELECT pbg.legislation_code
4573 FROM per_business_groups pbg,
4574 fnd_sessions fs
4575 WHERE pbg.business_group_id = cp_business_group_id
4576 AND fs.session_id = USERENV('SESSIONID')
4577 AND fs.effective_date BETWEEN pbg.date_from
4578 AND NVL(pbg.date_to,TO_DATE('31/12/4712','DD/MM/YYYY'));
4579
4580 CURSOR get_exclusion_set_id(cp_business_group_id NUMBER)
4581 IS
4582 SELECT org_information7
4583 FROM hr_organization_information hoi
4584 WHERE hoi.organization_id = cp_business_group_id
4585 AND hoi.org_information_context = 'HR_SELF_SERVICE_BG_PREFERENCE'
4586 AND org_information1 = 'SIMPAYSLIP';
4587
4588 CURSOR get_pay_basis(cp_pay_basis_id NUMBER
4589 ,cp_effective_date DATE)
4590 IS
4591 SELECT ppb.name
4592 FROM per_pay_bases ppb,
4593 pay_input_values_f piv
4594 WHERE ppb.pay_basis_id = cp_pay_basis_id
4595 AND piv.input_value_id = ppb.input_value_id
4596 AND cp_effective_date BETWEEN piv.effective_start_date
4597 AND piv.effective_end_date;
4598
4599 CURSOR get_last_payslip_details(cp_assignment_id NUMBER,
4600 cp_person_id NUMBER,
4601 cp_effective_date DATE)
4602 IS
4603 SELECT payslipv.effective_date,
4604 payslipv.action_context_id
4605 FROM pay_emp_payslip_action_info_v payslipv,
4606 pay_assignment_actions paa
4607 WHERE paa.assignment_id = cp_assignment_id
4608 AND payslipv.person_id = cp_person_id
4609 AND payslipv.action_context_id = paa.assignment_action_id
4610 AND payslipv.effective_date < cp_effective_date
4611 ORDER BY payslipv.effective_date DESC;
4612
4613 CURSOR get_gross_pay_ytd(cp_action_context_id NUMBER)
4614 IS
4615 SELECT gross_pay
4616 FROM pay_ac_emp_sum_action_info_v
4617 WHERE action_context_id = cp_action_context_id
4618 AND action_information_category = 'AC SUMMARY YTD';
4619
4620 lv_procedure_name VARCHAR2(100) := '.pre_processing';
4621
4622 ln_business_group_id NUMBER;
4623 lv_legislation_code VARCHAR2(2);
4624 ld_effective_date DATE;
4625
4626 ld_last_payslip_date DATE;
4627 ln_archive_action_id pay_assignment_actions.assignment_action_id%TYPE;
4628
4629 ln_person_id per_all_assignments_f.person_id%TYPE;
4630 ln_payroll_id per_all_assignments_f.payroll_id%TYPE;
4631 ln_pay_basis_id per_all_assignments_f.pay_basis_id%TYPE;
4632
4633 lv_period_type pay_payrolls_f.period_type%TYPE;
4634 lv_pay_basis per_pay_bases.name%TYPE;
4635
4636 ln_annual_salary NUMBER;
4637 ln_proposed_salary NUMBER;
4638 ln_pay_annualization_factor NUMBER;
4639 ln_gross_salary_ytd NUMBER;
4640
4641 ln_exclusion_set_id pay_element_sets.element_set_id%TYPE := NULL;
4642
4643 BEGIN
4644
4645 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
4646
4647 IF p_effective_date IS NULL THEN
4648
4649 OPEN get_effective_date;
4650 FETCH get_effective_date INTO ld_effective_date;
4651 CLOSE get_effective_date;
4652
4653 ELSE
4654
4655 ld_effective_date := p_effective_date;
4656
4657 END IF;
4658
4659 OPEN get_details(p_assignment_id,ld_effective_date);
4660 FETCH get_details INTO ln_person_id,
4661 ln_business_group_id,
4662 ln_payroll_id,
4663 ln_pay_basis_id;
4664 CLOSE get_details;
4665
4666 IF p_business_group_id IS NOT NULL THEN
4667
4668 ln_business_group_id := p_business_group_id;
4669
4670 END IF;
4671
4672 IF p_legislation_code IS NULL THEN
4673
4674 OPEN get_legislation_code(ln_business_group_id);
4675 FETCH get_legislation_code INTO lv_legislation_code;
4676 CLOSE get_legislation_code;
4677
4678 ELSE
4679
4680 lv_legislation_code := p_legislation_code;
4681
4682 END IF;
4683
4684 OPEN get_pay_basis(ln_pay_basis_id,ld_effective_date);
4685 FETCH get_pay_basis INTO lv_pay_basis;
4686 CLOSE get_pay_basis;
4687
4688 ln_proposed_salary :=
4689 pay_emp_action_arch.get_proposed_emp_salary(p_assignment_id
4690 ,ln_pay_basis_id
4691 ,lv_pay_basis
4692 ,ld_effective_date);
4693
4694 OPEN get_period_type(ln_payroll_id,ld_effective_date);
4695 FETCH get_period_type INTO lv_period_type;
4696 CLOSE get_period_type;
4697
4698 ln_pay_annualization_factor :=
4699 pay_emp_action_arch.get_emp_annualization_factor(
4700 ln_pay_basis_id
4701 ,lv_period_type
4702 ,lv_pay_basis
4703 ,p_assignment_id
4704 ,ld_effective_date);
4705
4706 ln_annual_salary := ln_pay_annualization_factor * ln_proposed_salary;
4707
4708 OPEN get_last_payslip_details(p_assignment_id,ln_person_id,ld_effective_date);
4709 FETCH get_last_payslip_details INTO ld_last_payslip_date,ln_archive_action_id;
4710 CLOSE get_last_payslip_details;
4711
4712 OPEN get_gross_pay_ytd(ln_archive_action_id);
4713 FETCH get_gross_pay_ytd INTO ln_gross_salary_ytd;
4714 CLOSE get_gross_pay_ytd;
4715
4716 DELETE FROM pay_simulation_information
4717 WHERE assignment_id = p_assignment_id
4718 AND action_context_type IN ('INPUTSLOV','SIMDISPLAY')
4719 AND action_context_id = p_assignment_id;
4720
4721 INSERT INTO pay_simulation_information
4722 (action_information_id,
4723 action_context_id,
4724 action_context_type,
4725 action_information_category,
4726 action_information1,
4727 effective_date,
4728 assignment_id
4729 )
4730 VALUES
4731 (pay_simulation_information_s.NEXTVAL,
4732 p_assignment_id,
4733 'INPUTSLOV',
4734 'LAST_PAYSLIP_DATE',
4735 TO_CHAR(ld_last_payslip_date,'DD/MM/YYYY'),
4736 ld_effective_date,
4737 p_assignment_id
4738 );
4739
4740 INSERT INTO pay_simulation_information
4741 (action_information_id,
4742 action_context_id,
4743 action_context_type,
4744 action_information_category,
4745 action_information1,
4746 action_information2,
4747 action_information3,
4748 action_information4,
4749 effective_date,
4750 assignment_id
4751 )
4752 VALUES
4753 (pay_simulation_information_s.NEXTVAL,
4754 p_assignment_id,
4755 'SIMDISPLAY',
4756 'US_SIMULATION_DISPLAY',
4757 lv_pay_basis,
4758 ln_proposed_salary,
4759 ln_annual_salary,
4760 ln_gross_salary_ytd,
4761 ld_effective_date,
4762 p_assignment_id
4763 );
4764
4765 OPEN get_exclusion_set_id(ln_business_group_id);
4766 FETCH get_exclusion_set_id INTO ln_exclusion_set_id;
4767 CLOSE get_exclusion_set_id;
4768
4769 INSERT INTO pay_simulation_information
4770 (action_information_id,
4771 action_context_id,
4772 action_context_type,
4773 action_information_category,
4774 action_information1,
4775 action_information2,
4776 action_information3,
4777 action_information4,
4778 action_information5,
4779 action_information6,
4780 action_information7,
4781 effective_date,
4782 assignment_id
4783 )
4784 SELECT pay_simulation_information_s.NEXTVAL,
4785 p_assignment_id,
4786 'INPUTSLOV',
4787 DECODE(pec.classification_name,
4788 'Earnings','EARNINGS INPUT VALUES',
4789 'Supplemental Earnings','EARNINGS INPUT VALUES',
4790 'Imputed Earnings','EARNINGS INPUT VALUES',
4791 'DEDUCTIONS INPUT VALUES'),
4792 SUBSTR(NVL(NVL(eit.eei_information18,petl.reporting_name),petl.element_name),1,37)||
4793 ' - '||SUBSTR(pivtl.name,1,20) element_input_name,
4794 pet.element_type_id,
4795 piv.input_value_id,
4796 pec.classification_name,
4797 pec.classification_id,
4798 pel.element_link_id,
4799 pel.location_id,
4800 ld_effective_date,
4801 p_assignment_id
4802 FROM pay_element_type_extra_info eit,
4803 pay_element_types_f pet,
4804 pay_element_types_f_tl petl,
4805 pay_element_classifications pec,
4806 pay_input_values_f piv,
4807 pay_input_values_f_tl pivtl,
4808 pay_element_links_f pel
4809 WHERE eit.information_type = 'SIMULATION_ELEMENTS'
4810 AND eit.eei_information1 = 'Y'
4811 AND eit.element_type_id = pet.element_type_id
4812 AND (NVL(eit.eei_information2,'-1') = TO_CHAR(piv.input_value_id)
4813 OR NVL(eit.eei_information3,'-1') = TO_CHAR(piv.input_value_id)
4814 OR NVL(eit.eei_information4,'-1') = TO_CHAR(piv.input_value_id)
4815 OR NVL(eit.eei_information5,'-1') = TO_CHAR(piv.input_value_id)
4816 OR NVL(eit.eei_information6,'-1') = TO_CHAR(piv.input_value_id)
4817 OR NVL(eit.eei_information7,'-1') = TO_CHAR(piv.input_value_id)
4818 OR NVL(eit.eei_information8,'-1') = TO_CHAR(piv.input_value_id)
4819 OR NVL(eit.eei_information9,'-1') = TO_CHAR(piv.input_value_id)
4820 OR NVL(eit.eei_information10,'-1') = TO_CHAR(piv.input_value_id)
4821 OR NVL(eit.eei_information11,'-1') = TO_CHAR(piv.input_value_id)
4822 OR NVL(eit.eei_information12,'-1') = TO_CHAR(piv.input_value_id)
4823 OR NVL(eit.eei_information13,'-1') = TO_CHAR(piv.input_value_id)
4824 OR NVL(eit.eei_information14,'-1') = TO_CHAR(piv.input_value_id)
4825 OR NVL(eit.eei_information15,'-1') = TO_CHAR(piv.input_value_id)
4826 OR NVL(eit.eei_information16,'-1') = TO_CHAR(piv.input_value_id))
4827 AND pet.element_type_id = piv.element_type_id
4828 AND ld_effective_date BETWEEN pet.effective_start_date
4829 AND pet.effective_end_date
4830 AND pet.element_type_id = petl.element_type_id
4831 AND petl.language = USERENV('LANG')
4832 AND ld_effective_date BETWEEN piv.effective_start_date
4833 AND piv.effective_end_date
4834 AND ld_effective_date BETWEEN pel.effective_start_date
4835 AND pel.effective_end_date
4836 AND piv.input_value_id = pivtl.input_value_id
4837 AND pivtl.language = USERENV('LANG')
4838 AND pec.classification_id = pet.classification_id
4839 AND pec.legislation_code = lv_legislation_code
4840 AND pec.classification_name IN
4841 ('Earnings',
4842 'Supplemental Earnings',
4843 'Imputed Earnings',
4844 'Involuntary Deductions',
4845 'Pre-Tax Deductions',
4846 'Voluntary Deductions')
4847 AND pel.element_type_id = pet.element_type_id
4848 AND NOT EXISTS
4849 (SELECT NULL
4850 FROM pay_element_set_members pesm
4851 WHERE pesm.element_set_id = ln_exclusion_set_id
4852 AND pesm.element_type_id = pet.element_type_id
4853 )
4854 AND EXISTS
4855 (SELECT NULL
4856 FROM per_all_assignments_f asgt
4857 WHERE asgt.assignment_id = p_assignment_id
4858 AND ld_effective_date BETWEEN asgt.effective_start_date
4859 AND asgt.effective_end_date
4860 AND asgt.business_group_id = pel.business_group_id
4861 AND ((
4862 pel.payroll_id IS NOT NULL
4863 AND pel.payroll_id = asgt.payroll_id
4864 )
4865 OR(
4866 pel.link_to_all_payrolls_flag = 'Y'
4867 AND asgt.payroll_id IS NOT NULL
4868 )
4869 OR(
4870 pel.payroll_id IS NULL
4871 AND pel.link_to_all_payrolls_flag = 'N'
4872 )
4873 )
4874 AND ( pel.organization_id = asgt.organization_id
4875 OR pel.organization_id IS NULL
4876 )
4877 AND ( pel.position_id = asgt.position_id
4878 OR pel.position_id IS NULL
4879 )
4880 AND ( pel.job_id = asgt.job_id
4881 OR pel.job_id IS NULL
4882 )
4883 AND ( pel.grade_id = asgt.grade_id
4884 OR pel.grade_id IS NULL
4885 )
4886 AND ( pel.pay_basis_id = asgt.pay_basis_id
4887 OR
4888 ( pel.pay_basis_id IS NULL
4889 AND NOT EXISTS
4890 (SELECT ppb.pay_basis_id
4891 FROM per_pay_bases ppb,
4892 pay_input_values_f piv1
4893 WHERE piv1.element_type_id = pet.element_type_id
4894 AND ld_effective_date
4895 BETWEEN piv1.effective_start_date
4896 AND piv1.effective_end_date
4897 AND ppb.input_value_id = piv1.input_value_id
4898 AND ppb.business_group_id = asgt.business_group_id
4899 AND ppb.pay_basis_id <> asgt.pay_basis_id
4900 )
4901 )
4902 )
4903 AND ( pel.employment_category = asgt.employment_category
4904 OR pel.employment_category IS NULL
4905 )
4906 AND ( pel.people_group_id = asgt.people_group_id
4907 OR pel.people_group_id IS NULL
4908 )
4909 );
4910
4911 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
4912
4913 END pre_processing;
4914
4915 END pay_us_payslip_simulation_main;