1 PACKAGE BODY pay_us_simulation AS
2 /* $Header: pyussmwr.pkb 120.0.12020000.4 2013/03/25 09:42:16 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 18-MAR-2013 emunisek 120.2 16482011 Made changes so that the preference
14 selected at Business Group level
15 reflects in Simulation output
16 instantaneously instead of waiting
17 for the cache to be cleared which is
18 not happening over a long period of
19 time or services are not bounced.
20 18-MAR-2013 emunisek 120.3 16408289 Added new get_context_refresh to
21 control the refresh of regions based
22 on changes to Flexfield segments.
23 */
24
25 gv_package VARCHAR2(100) := 'pay_us_simulation';
26
27 /* Procedure : add_custom_xml
28 Purpose : This procedure is to enable the Localization specific
29 XML Data to be added in addition to the Generic XML that
30 gets generated based on the Data archived in the table
31 PAY_SIMULATION_INFORMATION. The Descriptive Flexfield
32 Action Information DF Contexts which get stored to column
33 ACTION_INFORMATION_CATEGORY of PAY_SIMULATION_INFORMATION
34 form basis for the generation of XML Tags. In addition to
35 these XML tags based on Flexfield Segments, Localization
36 specific tags can be appended through this procedure.
37 Important : This should not be confused with the provison supplied to
38 customers to add Custom XML. This procedure is for
39 Legislative requirements only.
40 */
41
42 PROCEDURE add_custom_xml(p_assignment_action_id NUMBER ,
43 p_action_information_category VARCHAR2,
44 p_document_type VARCHAR2)
45
46 IS
47
48 CURSOR get_organization_id(cp_assignment_id NUMBER,
49 cp_effective_date DATE)
50 IS
51 SELECT paf.organization_id
52 FROM per_all_assignments_f paf
53 WHERE paf.assignment_id = cp_assignment_id
54 AND cp_effective_date BETWEEN paf.effective_start_date
55 AND paf.effective_end_date;
56
57 CURSOR get_employer_name_phone ( cp_organization_id NUMBER)
58 IS
59 SELECT org.name,loc.telephone_number_1
60 FROM hr_all_organization_units org,
61 hr_locations_all loc
62 WHERE org.location_id=loc.location_id
63 AND org.organization_id = cp_organization_id;
64
65 lv_procedure_name VARCHAR2(100) := '.add_custom_xml';
66
67 ln_business_group_id per_all_assignments_f.business_group_id%TYPE;
68 ln_tax_unit_id hr_all_organization_units.organization_id%TYPE;
69 ld_effective_date DATE;
70 ln_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
71 ln_assignment_id per_all_assignments_f.assignment_id%TYPE;
72 ln_organization_id hr_all_organization_units.organization_id%TYPE;
73
74 lv_employer_address1 pay_action_information.action_information1%TYPE;
75 lv_employer_address2 pay_action_information.action_information1%TYPE;
76 lv_employer_address3 pay_action_information.action_information1%TYPE;
77 lv_employer_city pay_action_information.action_information1%TYPE;
78 lv_employer_state pay_action_information.action_information1%TYPE;
79 lv_employer_zip_code pay_action_information.action_information1%TYPE;
80 lv_employer_country pay_action_information.action_information1%TYPE;
81
82 lv_employer_name hr_all_organization_units.name%TYPE;
83 lv_phone_number hr_locations_all.telephone_number_1%TYPE;
84
85 ln_net_pay_ytd NUMBER;
86 ln_amount NUMBER;
87
88 BEGIN
89
90 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
91
92 hr_utility.set_location(gv_package || lv_procedure_name, 10);
93
94 IF p_document_type = 'PAYSLIP' AND p_action_information_category IS NULL THEN
95
96 hr_utility.set_location(gv_package || lv_procedure_name, 20);
97
98 OPEN pay_us_payslip_simulation_main.get_action_details(p_assignment_action_id);
99 FETCH pay_us_payslip_simulation_main.get_action_details
100 INTO ln_business_group_id,
101 ln_tax_unit_id,
102 ld_effective_date,
103 ln_payroll_action_id,
104 ln_assignment_id;
105 CLOSE pay_us_payslip_simulation_main.get_action_details;
106
107 OPEN get_organization_id(ln_assignment_id,ld_effective_date);
108 FETCH get_organization_id INTO ln_organization_id;
109 CLOSE get_organization_id;
110
111 hr_utility.set_location(gv_package || lv_procedure_name, 30);
112
113 pay_payslip_util.g_business_group_id := NULL;
114
115 ln_organization_id := pay_payslip_util.get_id_for_employer_address
116 (ln_business_group_id
117 ,ln_tax_unit_id
118 ,ln_organization_id
119 ,ld_effective_date);
120
121 hr_utility.set_location(gv_package || lv_procedure_name, 40);
122
123 OPEN pay_us_payslip_simulation_main.get_us_employer_addr(ln_organization_id,
124 ln_payroll_action_id);
125 FETCH pay_us_payslip_simulation_main.get_us_employer_addr
126 INTO lv_employer_address1,
127 lv_employer_address2,
128 lv_employer_address3,
129 lv_employer_city,
130 lv_employer_state,
131 lv_employer_zip_code,
132 lv_employer_country ;
133 CLOSE pay_us_payslip_simulation_main.get_us_employer_addr;
134
135 pay_payroll_xml_extract_pkg.load_xml_data('CS','US_EMPLOYER_ADDRESS',null);
136 pay_payroll_xml_extract_pkg.load_xml_data('D','ORGANIZATION_ID',ln_organization_id);
137 pay_payroll_xml_extract_pkg.load_xml_data('D','ADDRESS_TYPE','US Employer Address');
138 pay_payroll_xml_extract_pkg.load_xml_data('D','ADDRESS1',lv_employer_address1);
139 pay_payroll_xml_extract_pkg.load_xml_data('D','ADDRESS2',lv_employer_address2);
140 pay_payroll_xml_extract_pkg.load_xml_data('D','ADDRESS3',lv_employer_address3);
141 pay_payroll_xml_extract_pkg.load_xml_data('D','CITY',lv_employer_city);
142 pay_payroll_xml_extract_pkg.load_xml_data('D','STATE',lv_employer_state);
143 pay_payroll_xml_extract_pkg.load_xml_data('D','ZIP_CODE',lv_employer_zip_code);
144 pay_payroll_xml_extract_pkg.load_xml_data('D','COUNTRY',lv_employer_country);
145 pay_payroll_xml_extract_pkg.load_xml_data('CE','US_EMPLOYER_ADDRESS',null);
146
147 hr_utility.set_location(gv_package || lv_procedure_name, 50);
148
149 OPEN get_employer_name_phone(ln_organization_id);
150 FETCH get_employer_name_phone INTO lv_employer_name,lv_phone_number;
151 CLOSE get_employer_name_phone;
152
153 pay_payroll_xml_extract_pkg.load_xml_data('CS','US_EMPLOYER_DETAILS',null);
154 pay_payroll_xml_extract_pkg.load_xml_data('D','US_EMPLOYER_NAME',lv_employer_name);
155 pay_payroll_xml_extract_pkg.load_xml_data('D','US_EMPLOYER_PHONE',lv_phone_number);
156 pay_payroll_xml_extract_pkg.load_xml_data('CE','US_EMPLOYER_DETAILS',null);
157
158 hr_utility.set_location(gv_package || lv_procedure_name, 60);
159
160 END IF;
161
162 hr_utility.set_location(gv_package || lv_procedure_name, 70);
163
164 IF p_action_information_category = 'AC SUMMARY YTD' THEN
165
166 hr_utility.set_location(gv_package || lv_procedure_name, 80);
167
168 OPEN pay_us_payslip_simulation_main.get_net_pay(p_assignment_action_id,'YTD');
169 FETCH pay_us_payslip_simulation_main.get_net_pay INTO ln_net_pay_ytd;
170 CLOSE pay_us_payslip_simulation_main.get_net_pay;
171
172 pay_payroll_xml_extract_pkg.load_xml_data('D','NET_PAY_YTD',ln_net_pay_ytd );
173
174 hr_utility.set_location(gv_package || lv_procedure_name, 90);
175
176 END IF;
177
178 IF p_action_information_category = 'AC SUMMARY CURRENT' THEN
179
180 hr_utility.set_location(gv_package || lv_procedure_name, 100);
181
182 OPEN pay_us_payslip_simulation_main.get_net_pay(p_assignment_action_id,'CURRENT');
183 FETCH pay_us_payslip_simulation_main.get_net_pay into ln_amount;
184 CLOSE pay_us_payslip_simulation_main.get_net_pay;
185
186 pay_payroll_xml_extract_pkg.load_xml_data('D','NET_PAY',ln_amount);
187
188 hr_utility.set_location(gv_package || lv_procedure_name, 110);
189
190 END IF;
191
192 hr_utility.set_location(gv_package || lv_procedure_name, 120);
193
194 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
195
196 END add_custom_xml;
197
198 /* Procedure : pre_processing
199 Purpose : This procedure is to execute any Pre-Processing tasks that
200 need to be carried before Payroll Simulation page is made
201 available to the user. For US Localization, we are using
202 this procedure to determine all those "Element Name -
203 Input Value" combinations applicable to the current
204 Assignment. These combinations are stored to table
205 PAY_SIMULATION_INFORMATION with ACTION_CONTEXT_TYPE as
206 "INPUTSLOV". This data is used by the LOV Queries related
207 to Earnings and Deductions regions of Payroll Simulator.
208 Important : This procedure is initiated from Payroll Simulator Page
209 and the data gathered by this procedure will not be
210 committed to the database. As of now, we are using it to
211 determine "Element Name-Input Value" combinations for
212 Value Set Queries as this can improve the performance of
213 LOVs. In future, if required, additional tasks can be
214 added as required.
215 */
216
217 PROCEDURE pre_processing(p_assignment_id NUMBER,
218 p_business_group_id NUMBER DEFAULT NULL,
219 p_legislation_code VARCHAR2 DEFAULT NULL,
220 p_effective_date DATE DEFAULT NULL)
221
222 IS
223
224 lv_procedure_name VARCHAR2(100) := '.pre_processing';
225
226 ld_effective_date DATE;
227
228 BEGIN
229
230 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
231
232 IF p_effective_date IS NULL THEN
233
234 ld_effective_date := SYSDATE;
235
236 ELSE
237
238 ld_effective_date := p_effective_date;
239
240 END IF;
241
242 pay_us_payslip_simulation_main.pre_processing(p_assignment_id => p_assignment_id
243 ,p_business_group_id => p_business_group_id
244 ,p_legislation_code => p_legislation_code
245 ,p_effective_date => p_effective_date);
246
247 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
248
249 END pre_processing;
250
251 /* Procedure : update_asg_data
252 Purpose : This procedure is to propagate the changes made on the
253 Payroll Simulator to the Assignment specific Data. This
254 does not include the changes to Element entries as those
255 changes are handled by Core Payroll Team. Only Assignment
256 specific changes like Address, Work Location, Tax Data etc
257 are propagated by this procedure.
258 Important : This procedure is initiated by Core Payroll Code after the
259 creation of Database Level Savepoint. Once the Payroll
260 Simulator Run is complete, the entire session will be
261 rolled back to the initial Savepoint. Hence the changes
262 made on the Payroll Simulator page will be propagated to
263 the actual live data through this procedure.
264 */
265
266 PROCEDURE update_asg_data(p_source_action_id NUMBER,
267 p_effective_date DATE DEFAULT NULL)
268
269 IS
270
271 lv_procedure_name VARCHAR2(100) := '.update_asg_data';
272
273 BEGIN
274
275 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
276
277 pay_us_payslip_simulation_main.update_asg_data(
278 p_source_action_id => p_source_action_id,
279 p_effective_date => p_effective_date);
280
281 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
282
283 END update_asg_data;
284
285 /* Procedure : archive_data
286 Purpose : This procedure captures the results of Payroll Simulator
287 Run to the table PAY_SIMULATION_INFORMATION. This
288 procedure is similar to the Payroll Archiver procedure
289 PAY_US_ACTION_ARCH.ACTION_ARCHIVE_DATA. The regular
290 Payroll Archiver procedure is based on Prepayments where
291 as the current procedure is based on actual Payroll Run
292 itself.
293 Important : The Data archival process is similar to the regular
294 Payroll Archiver except the Payment related data will be
295 skipped here as there are no Prepayments executed as part
296 of Payroll Simulation. All the data gathered during this
297 procedure is inserted into PAY_SIMULATION_INFORMATION
298 table autonomously so that the data can be used to
299 generate the Output, post Database level rollback. Any
300 information required for generating Output, that will not
301 be available due to rollback should be captured through
302 archive_data procedure
303 */
304
305 PROCEDURE archive_data(p_source_action_id NUMBER,
306 p_effective_date DATE DEFAULT NULL)
307
308 IS
309
310 lv_procedure_name VARCHAR2(100) := '.archive_data';
311
312 BEGIN
313
314 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
315
316 pay_us_payslip_simulation_main.archive_data(p_source_action_id => p_source_action_id
317 ,p_effective_date => p_effective_date);
318
319 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
320
321 END archive_data;
322
323 /* Procedure : generate_xml
324 Purpose : This procedure is used to generate the XML Data necessary
325 to generate the Payroll Simulation Output. This procedure
326 is similar to the current XML generation procedure
327 PAY_PAYROLL_XML_EXTRACT_PKG.GENERATE.
328 Important : This procedure will be executed by Core Payroll post the
329 Database level rollback of changes made during Payroll
330 Simulation run. Hence any information required for
331 generating Output, that will not be available due to
332 rollback should be captured through archive_data procedure
333 and saved autonomously to PAY_SIMULATION_INFORMATION table
334 Parameter p_xml_code is to allow the Customers the ability
335 to use Custom XML Code. This is hidden from Customers as
336 of now and will be enabled if required in future. The
337 entire code to support Custom XML Code is already in
338 place. We need to add the Segment to input Custom XML Code
339 details at Business Group and Organization Level in "Self
340 Service Preference". The Segment name should be "Payroll
341 Simulator XML Code".
342 */
343
344 PROCEDURE generate_xml(p_assignment_id IN NUMBER,
345 p_xml_code IN VARCHAR2 DEFAULT NULL,
346 p_xml OUT NOCOPY BLOB)
347
348 IS
349
350 lv_procedure_name VARCHAR2(100) := '.generate_xml';
351
352 BEGIN
353
354 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
355
356 pay_us_payslip_simulation_main.generate_xml(
357 p_assignment_id => p_assignment_id,
358 p_xml_code => p_xml_code,
359 p_xml => p_xml);
360
361 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
362
363 END generate_xml;
364
365 /* Procedure : get_profile
366 Purpose : This procedure is used to indicate the Profile Options to
367 be populated corresponding to the Flexfield Context
368 segments. Core Payroll uses this procedure to get the
369 PL/SQL table loaded with Profile Options and corresponding
370 Flexfield segments for each Flexfield Context.
371 Important : It is necessary to update this procedure when ever we need
372 value selected in Flexfield Segment to be set as Profile
373 Option. Using the Profile Option, we can access the value
374 chosen in one Flexfield context in other context.
375 */
376
377 PROCEDURE get_profile(p_flex_context_code IN VARCHAR2,
378 p_profile_tab IN OUT NOCOPY PAY_SIMULATION_PROFILE_TAB)
379
380 IS
381
382 lv_procedure_name VARCHAR2(100) := '.get_profile';
383
384 l_tab PAY_SIMULATION_PROFILE_TAB;
385
386 BEGIN
387
388 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
389
390 l_tab := PAY_SIMULATION_PROFILE_TAB();
391 l_tab.extend(2);
392
393 hr_utility.set_location(gv_package || lv_procedure_name, 10);
394
395 IF p_flex_context_code = 'US_SIMULATION_REGION1' THEN
396
397 l_tab(1) := PAY_SIMULATION_PROFILE_REC('PER_US_RESIDENT_STATE','AeiInformation1');
398 l_tab(2) := PAY_SIMULATION_PROFILE_REC('PER_LOCATION_ID','AeiInformation4');
399
400 END IF;
401
402 p_profile_tab := l_tab;
403
404 hr_utility.set_location(gv_package || lv_procedure_name, 20);
405
406 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
407
408 END get_profile;
409
410 /* Procedure : is_entries_affected
411 Purpose : This procedure is used to indicate if the change in a
412 particular Flexfield segment can affect the Element
413 entries. Core Payroll calls this procedure when ever
414 change happens to the Flexfield Segments on Payroll
415 Simulator Page. If this Function returns 'true', then
416 Core Payroll will refresh the Earnings and Deductions
417 section so that the Element entries can be populated as
418 per the new Eligibility criteria.
419 Important : As of now, for US Localization we have "Work Location"
420 Segment which can affect Element entries and which
421 requires Element entries refresh if altered. If in future
422 any more such Segments are added, this function needs to
423 be updated accordingly.
424 */
425
426 FUNCTION is_entries_affected(p_flex_context_code VARCHAR2,
427 p_segment_name VARCHAR2)
428 RETURN VARCHAR2
429
430 IS
431
432 lv_procedure_name VARCHAR2(100) := '.is_entries_affected';
433
434 lv_return VARCHAR2(5) := 'false';
435
436 BEGIN
437
438 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
439
440 IF p_flex_context_code = 'US_SIMULATION_REGION1'
441 AND p_segment_name = 'AeiInformation4' THEN
442
443 lv_return := 'true';
444
445 END IF;
446
447 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
448 hr_utility.trace('Value returned is : '||lv_return);
449
450 RETURN lv_return;
451
452 END is_entries_affected;
453
454 /* Procedure : get_run_type_id
455 Purpose : This function is used to determine the Run Type ID
456 corresponding to the type of run that Core Payroll should
457 use to initiate the Simulation Run.
458 Important :
459 */
460
461 FUNCTION get_run_type_id
462 RETURN NUMBER
463
464 IS
465
466 CURSOR get_runtype_id
467 IS
468 SELECT run_type_id
469 FROM pay_run_types_f prt
470 WHERE prt.legislation_code = 'US'
471 AND RUN_METHOD = 'C'
472 AND RUN_TYPE_NAME = 'Regular';
473
474 lv_procedure_name VARCHAR2(100) := '.get_run_type_id';
475
476 ln_run_type_id pay_run_types_f.run_type_id%TYPE;
477
478 BEGIN
479
480 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
481
482 OPEN get_runtype_id;
483 FETCH get_runtype_id INTO ln_run_type_id;
484 CLOSE get_runtype_id;
485
486 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
487 hr_utility.trace('Value returned is : '||ln_run_type_id);
488
489 RETURN ln_run_type_id;
490
491 END get_run_type_id;
492
493 /* Procedure : get_context_refresh
494 Purpose : This procedure is used to indicate if the change in a
495 particular Flexfield segment can affect the other
496 regions or requires page reloaded. Core Payroll calls
497 this procedure when ever change happens to the Flexfield
498 Segments on Payroll Simulator Page. If this Procedure
499 returns 'true' for any context, then Core Payroll will
500 refresh the page and reloads the particular context
501 clearing all the details in that context
502 Important : As of now, for US Localization we have "Work Location"
503 Segment which can affect Element entries and which
504 requires Element entries refresh if altered. For "Resident
505 State" Change, the "Tax Withholding Details" needs to be
506 refreshed and the page needs to be reloaded.
507 */
508
509 PROCEDURE get_context_refresh(p_flex_context_code VARCHAR2,
510 p_segment_name VARCHAR2,
511 p_flex1_context IN OUT NOCOPY VARCHAR2 ,
512 p_flex2_context IN OUT NOCOPY VARCHAR2 ,
513 p_flex3_context IN OUT NOCOPY VARCHAR2 ,
514 p_flex4_context IN OUT NOCOPY VARCHAR2)
515
516 IS
517
518 lv_procedure_name VARCHAR2(100) := '.get_context_refresh';
519
520 BEGIN
521
522 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
523
524 p_flex1_context := 'false';
525 p_flex2_context := 'false';
526 p_flex3_context := 'false';
527 p_flex4_context := 'false';
528
529 IF p_flex_context_code = 'US_SIMULATION_REGION1'
530 THEN
531
532 IF p_segment_name = 'AeiInformation1'
533 THEN
534
535 p_flex2_context := 'true';
536
537 ELSIF p_segment_name = 'AeiInformation2'
538 THEN
539
540 p_flex2_context := 'true';
541
542 ELSIF p_segment_name = 'AeiInformation4'
543 THEN
544
545 p_flex2_context := 'true';
546 p_flex3_context := 'true';
547 p_flex4_context := 'true';
548
549 END IF;
550
551 END IF;
552
553 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
554 hr_utility.trace('Values returned are : ');
555 hr_utility.trace('p_flex1_context : '||p_flex1_context);
556 hr_utility.trace('p_flex2_context : '||p_flex2_context);
557 hr_utility.trace('p_flex3_context : '||p_flex3_context);
558 hr_utility.trace('p_flex4_context : '||p_flex4_context);
559
560 END get_context_refresh;
561
562 END pay_us_simulation;
[Home] [Help]