[Home] [Help]
PACKAGE BODY: APPS.PAY_HK_PAYSLIP_ARCHIVE
Source
1 PACKAGE BODY pay_hk_payslip_archive AS
2 /* $Header: pyhkparc.pkb 120.4 2006/12/28 05:53:48 jalin noship $ */
3
4 ---------------------------------------------------------------------+
5 -- This is a global variable used to store Archive assignment action id
6 --------------------------------------------------------------------+
7
8
9 g_archive_pact NUMBER;
10 g_debug BOOLEAN;
11
12 --------------------------------------------------------------------+
13 -- This procedure returns a sql string to SELECT a range
14 -- of assignments eligible for archival.
15 -- It calls pay_apac_payslip_archive.range_code that archives the EIT
16 -- definition and payroll level data (Messages, employer address details etc)
17 --------------------------------------------------------------------+
18
19 PROCEDURE range_code(p_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE,
20 p_sql OUT nocopy VARCHAR2) IS
21
22
23 BEGIN
24
25 g_debug := hr_utility.debug_enabled;
26
27 IF g_debug THEN
28 hr_utility.set_location('Start of range_code',1);
29 END IF;
30
31
32 --------------------------------------------------------------------------------+
33 -- Call to range_code from common apac package 'pay_apac_payslip_archive'
34 -- to archive the payroll action level data and EIT defintions.
35 --------------------------------------------------------------------------------+
36
37 pay_apac_payslip_archive.range_code(p_payroll_action_id => p_payroll_action_id);
38
39 -- Bug#3580617 Replaced Dynamic SQL with a function sql.
40 pay_core_payslip_utils.range_cursor( p_payroll_action_id,
41 p_sql
42 );
43 IF g_debug THEN
44 hr_utility.set_location('End of range_code',2);
45 END IF;
46
47 EXCEPTION
48 WHEN OTHERS THEN
49 IF g_debug THEN
50 hr_utility.set_location('Error in initialization_code',2);
51 END IF;
52 RAISE;
53
54 END range_code;
55
56
57
58
59 --------------------------------------------------------------------+
60 -- This procedure is used to set global contexts .
61 -- The globals used are PL/SQL tables i.e.(g_user_balance_table and g_element_table)
62 -- It calls the procedure pay_apac_archive.initialization_code that
63 -- actually sets the global variables and populates the global tables.
64 --------------------------------------------------------------------+
65
66 PROCEDURE initialization_code (p_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE) IS
67
68
69 BEGIN
70 IF g_debug THEN
71 hr_utility.set_location('Start of initialization_code',1);
72 END IF;
73
74 g_archive_pact := p_payroll_action_id;
75
76 ------------------------------------------------------------------+
77 -- Call to common package procedure pay_apac_payslip_archive.
78 -- initialization_code to to set the global tables for EIT
79 -- that will be used by each thread in multi-threading.
80 ------------------------------------------------------------------+
81
82 pay_apac_payslip_archive.initialization_code(p_payroll_action_id => p_payroll_action_id);
83
84 IF g_debug THEN
85 hr_utility.set_location('End of initialization_code',2);
86 END IF;
87
88 EXCEPTION
89 WHEN OTHERS THEN
90 IF g_debug THEN
91 hr_utility.set_location('Error in initialization_code',2);
92 END IF;
93 RAISE;
94
95 END initialization_code;
96
97
98 --------------------------------------------------------------------+
99 -- This procedure further restricts the assignment_id's
100 -- returned by range_code
101 -- It filters the assignments selected by range_code procedure
102
103 -- Since the Payslip is given for each prepayment, the data should
104 -- be archived for each prepayment.
105 -- So, the successfully completed prepayments are selected and locked
106 -- by the archival action.
107 -- All the successfully completed runs under the prepayments are also
108 -- selected and locked by archival to make the core 'Choose Payslip'
109 -- work.
110 -- The archive will not pickup already archived prepayments.
111 --------------------------------------------------------------------+
112
113 PROCEDURE assignment_action_code (p_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE,
114 p_start_person IN per_all_people_f.person_id%TYPE,
115 p_end_person IN per_all_people_f.person_id%TYPE,
116 p_chunk IN NUMBER) IS
117
118 BEGIN
119 IF g_debug THEN
120 hr_utility.trace('Start of assignment action code');
121 END IF;
122
123 -- Bug#3580617 Logic of generating Assignment actions has been replaced with the following
124 -- Function Call.
125
126 pay_core_payslip_utils.action_creation ( p_payroll_action_id,
127 p_start_person,
128 p_end_person,
129 p_chunk,
130 'HK_PAYSLIP_ARCHIVE',
131 'HK');
132 IF g_debug THEN
133 hr_utility.trace('End of Assignment action code');
134 END IF;
135
136 EXCEPTION
137 WHEN OTHERS THEN
138 IF g_debug THEN
139 hr_utility.trace('Error occured in Assignment action code');
140 END IF;
141 RAISE;
142
143 END assignment_action_code;
144
145 /*
146 * Bug 3134158 - Added the following function to return the scheme name
147 *
148 */
149 FUNCTION get_scheme_name(p_run_result_id in pay_run_results.run_result_id%TYPE,
150 p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE,
151 p_business_group_id in hr_organization_units.business_group_id%TYPE)
152 RETURN VARCHAR2
153 IS
154
155 -- Cursor to get all the valid scheme names for this
156 -- business_group_id
157
158 CURSOR csr_scheme_names(c_business_group_id IN NUMBER)
159 IS
160 SELECT fnd_number.canonical_to_number(hoi.org_information20) scheme_id,
161 hoi.org_information2 scheme_name
162 from hr_organization_units hou,
163 hr_organization_information hoi
164 where hoi.org_information_context = 'HK_MPF_SCHEMES'
165 and hou.business_group_id = c_business_group_id
166 and hou.organization_id = hoi.organization_id;
167
168 -- Cursor to get the result_value for this run_result_id
169 --
170
171 CURSOR csr_result_value(c_run_result_id in NUMBER,
172 c_assignment_action_id IN NUMBER)
173 IS
174 SELECT fnd_number.canonical_to_number(prrv.result_value)
175 FROM pay_assignment_actions paa,
176 pay_run_results prr,
177 pay_run_result_values prrv,
178 pay_input_values_f pivf
179 WHERE prr.run_result_id = c_run_result_id
180 AND paa.assignment_action_id = c_assignment_action_id
181 AND prr.run_result_id = prrv.run_result_id
182 AND prrv.input_value_id = pivf.input_value_id
183 AND pivf.legislation_code = 'HK'
184 AND pivf.name = 'Source'
185 AND prr.assignment_action_id = paa.assignment_action_id;
186 --
187 l_result_value pay_run_result_values.result_value%TYPE;
188 BEGIN
189 l_result_value := null; -- Bug: 3604131
190 --
191 IF g_debug THEN
192 hr_utility.trace('Run Result ID: ' || p_run_result_id);
193 hr_utility.trace('Assignment action ID: '||p_assignment_action_id);
194 hr_utility.trace('Business_group_id: '||p_business_group_id);
195 END IF;
196 --
197 -- If the pl/sql table is not populated previously. Populate
198 -- the table with all the scheme names available in this business
199 -- group.
200 ---------------------------------------------------------------------
201 IF g_sn_populated = FALSE THEN
202 FOR csr_sn_rec IN csr_scheme_names(p_business_group_id) LOOP
203 g_scheme_name_table(csr_sn_rec.scheme_id).scheme_name := csr_sn_rec.scheme_name;
204 IF g_debug THEN
205 hr_utility.trace('Scheme Name: ' || csr_sn_rec.scheme_name);
206 hr_utility.trace('Scheme ID: ' ||csr_sn_rec.scheme_id);
207 END IF;
208 END LOOP;
209 g_sn_populated := TRUE;
210 END IF;
211 --
212 -- Check if the pl/sql table has any data
213 -- If data doesn't exists --> then return null
214 -- else --> 1. Get the result value
215 -- 2. If it is not null, get the scheme name and
216 -- 3. Return the scheme name or null
217 ---------------------------------------------------------------------
218 IF g_scheme_name_table.count > 0 THEN
219
220 -- 1. Get the result value
221 --------------------------
222 OPEN csr_result_value(p_run_result_id, p_assignment_action_id);
223 FETCH csr_result_value INTO l_result_value;
224 CLOSE csr_result_value;
225
226 IF g_debug THEN
227 hr_utility.trace('Result Value: '||nvl(l_result_value, '<null>'));
228 END IF;
229
230 -- 2. If result value is not null
231 -- Check if the scheme name exists.
232 -------------------------------------------------
233 IF l_result_value is not null AND g_scheme_name_table.exists(l_result_value) THEN
234 IF g_debug THEN
235 hr_utility.trace('Success : '||g_scheme_name_table(l_result_value).scheme_name);
236 END IF;
237 -- 3. Return the scheme name
238 ----------------------------
239 return g_scheme_name_table(l_result_value).scheme_name;
240 END IF;
241
242 END IF;
243 --
244 IF g_debug THEN
245 hr_utility.trace('Scheme name does not exists');
246 END IF;
247
248 -- Scheme Name doesn't exists
249 -----------------------------
250 RETURN null;
251 --
252 END get_scheme_name;
253
254 PROCEDURE archive_stat_elements(p_assignment_action_id IN NUMBER,
255 p_assignment_id IN NUMBER,
256 p_effective_date IN DATE,
257 p_assact_id IN NUMBER) IS
258
259 -- Cursor to get all the elements processed for the assignment in the
260 -- prepayment.
261
262 CURSOR csr_std_elements(p_assignment_action_id NUMBER,
263 p_assignment_id NUMBER)
264 IS
265 SELECT element_reporting_name
266 , classification_name
267 , payment_amount
268 , assessed_ri
269 , fnd_date.date_to_canonical(to_date(period_start_date,'YYYY/MM/DD HH24:MI:SS')) period_start_date
270 , fnd_date.date_to_canonical(to_date(period_end_date,'YYYY/MM/DD HH24:MI:SS')) period_end_date
271 FROM PAY_HK_ASG_ELEMENT_PAYMENTS_V
272 WHERE assignment_action_id = p_assignment_action_id
273 AND classification_name IS NOT NULL;
274
275
276 l_action_info_id NUMBER;
277 l_ovn NUMBER;
278 l_foreign_currency_amount NUMBER;
279 l_rate NUMBER;
280 l_procedure_name VARCHAR2(80);
281
282 BEGIN
283 l_procedure_name := 'archive_stat_elements'; -- Bug: 3604131
284
285 IF g_debug THEN
286 hr_utility.set_location('Entering Procedure pay_hk_payslip_archive.' || l_procedure_name,10);
287
288 hr_utility.trace('Opening Cursor csr_std_elements');
289 END IF;
290
291 FOR csr_rec IN csr_std_elements(p_assignment_action_id,p_assignment_id)
292
293 LOOP
294
295 IF g_debug THEN
296 hr_utility.set_location('Archiving Standard Element Details',20);
297 END IF;
298
299
300 if csr_rec.classification_name<>'Employer Liabilities' then
301
302 pay_action_information_api.create_action_information
303 ( p_action_information_id => l_action_info_id
304 , p_action_context_id => p_assact_id
305 , p_action_context_type => 'AAP'
306 , p_object_version_number => l_ovn
307 , p_effective_date => p_effective_date
308 , p_source_id => NULL
309 , p_source_text => NULL
310 , p_action_information_category => 'APAC ELEMENTS'
311 , p_action_information1 => csr_rec.element_reporting_name
312 , p_action_information2 => NULL
313 , p_action_information3 => NULL
314 , p_action_information4 => csr_rec.classification_name
315 , p_action_information5 => fnd_number.number_to_canonical(csr_rec.payment_amount) -- Bug: 3604131
316 , p_action_information11 => fnd_number.number_to_canonical(csr_rec.assessed_ri) -- Bug: 3604131
317 , p_action_information13 => csr_rec.period_start_date
318 , p_action_information14 => csr_rec.period_end_date);
319
320 else
321
322 pay_action_information_api.create_action_information
323 ( p_action_information_id => l_action_info_id
324 , p_action_context_id => p_assact_id
325 , p_action_context_type => 'AAP'
326 , p_object_version_number => l_ovn
327 , p_effective_date => p_effective_date
328 , p_source_id => NULL
329 , p_source_text => NULL
330 , p_action_information_category => 'APAC BALANCES 2'
331 , p_action_information1 => csr_rec.element_reporting_name
332 , p_action_information2 => NULL
333 , p_action_information3 => NULL
334 , p_action_information4 => fnd_number.number_to_canonical(csr_rec.payment_amount) -- Bug: 3604131
335 , p_action_information6 => csr_rec.period_start_date
336 , p_action_information7 => csr_rec.period_end_date
337 , p_action_information8 => fnd_number.number_to_canonical(csr_rec.assessed_ri)); -- Bug: 3604131
338
339 end if;
340
341
342 END LOOP;
343 IF g_debug THEN
344 hr_utility.trace('Closing Cursor csr_std_elements');
345 hr_utility.set_location('End of archive Standard Element',4);
346 hr_utility.set_location('Leaving Procedure pay_hk_payslip_archive.' || l_procedure_name,10);
347 END IF;
348
349 EXCEPTION
350 WHEN OTHERS THEN
351 IF g_debug THEN
352 hr_utility.set_location('Error in archiving Standard Elements ',5);
353 END IF;
354 RAISE;
355
356 END archive_stat_elements;
357
358
359
360 --------------------------------------------------------------------+
361 -- Procedure to archive the Statutory balances
362 --------------------------------------------------------------------+
363
364 PROCEDURE archive_balances( p_effective_date IN DATE
365 ,p_assact_id IN NUMBER
366 ,p_narrative IN VARCHAR2
367 ,p_ytd IN NUMBER
368 ,p_curr IN NUMBER) IS
369
370 l_action_info_id NUMBER;
371 l_ovn NUMBER;
372 l_procedure_name VARCHAR2(80);
373
374 BEGIN
375
376 l_procedure_name := 'archive_balances'; -- Bug: 3604131
377
378 IF g_debug THEN
379 hr_utility.set_location('Entering procedure ' || l_procedure_name,10);
380 hr_utility.set_location('archiving balances :',10);
381 END IF;
382
383 -- Archive Statutory balances
384
385 pay_action_information_api.create_action_information
386 ( p_action_information_id => l_action_info_id
387 , p_action_context_id => p_assact_id
388 , p_action_context_type => 'AAP'
389 , p_object_version_number => l_ovn
390 , p_effective_date => p_effective_date
391 , p_source_id => NULL
392 , p_source_text => NULL
393 , p_action_information_category => 'APAC BALANCES'
394 , p_action_information1 => p_narrative
395 , p_action_information2 => NULL
396 , p_action_information3 => NULL
397 , p_action_information4 => fnd_number.number_to_canonical(p_ytd) -- Bug: 3604131
398 , p_action_information5 => fnd_number.number_to_canonical(p_curr) -- Bug: 3604131
399 );
400
401
402 EXCEPTION
403 WHEN OTHERS THEN
404 IF g_debug THEN
405 hr_utility.set_location('Error in archiving balance :',11);
406 END IF;
407 RAISE;
408
409 END archive_balances;
410
411
412
413 --------------------------------------------------------------------+
414 -- Procedure to calculate the balances values
415 -- Calls procedure archive_balances and actually archives all the balance values
416 --------------------------------------------------------------------+
417
418
419 PROCEDURE archive_stat_balances(p_assignment_action_id IN NUMBER
420 ,p_assignment_id IN NUMBER
421 ,p_date_earned IN DATE
422 ,p_effective_date IN DATE
423 ,p_assact_id IN NUMBER) IS
424
425
426 l_Total_Earnings_This_Pay NUMBER;
427 l_Total_Earnings_YTD NUMBER;
428 l_Total_Deductions_This_pay NUMBER;
429 l_Total_Deductions_YTD NUMBER;
430 l_Net_Pay_This_pay NUMBER;
431 l_Net_Pay_YTD NUMBER;
432 l_Direct_Payments_This_Pay NUMBER;
433 l_Direct_Payments_YTD NUMBER;
434 l_Total_Payment_This_Pay NUMBER;
435 l_Total_Payment_YTD NUMBER;
436 l_tax_unit_id NUMBER;
437 l_narrative VARCHAR2(150);
438 l_procedure_name VARCHAR2(80);
439
440
441 CURSOR csr_tax_unit_id(p_assignment_action_id NUMBER)
442 IS
443 SELECT tax_unit_id
444 FROM pay_assignment_actions
445 WHERE assignment_action_id = p_assignment_action_id;
446
447 BEGIN
448
449 l_procedure_name := 'Archive_Stat_Balances'; -- Bug: 3604131
450
451 IF g_debug THEN
452 hr_utility.set_location('Entering Procedure pay_hk_payslip_archive.' || l_procedure_name,10);
453 hr_utility.set_location('Calling balance_total from pay_hk_soe_pkg',20);
454 END IF;
455
456 -- Get the totals of all the balances
457
458 OPEN csr_tax_unit_id(p_assignment_action_id);
459 FETCH csr_tax_unit_id INTO l_tax_unit_id;
460 CLOSE csr_tax_unit_id;
461
462 pay_hk_soe_pkg.balance_totals(p_assignment_action_id,
463 l_tax_unit_id,
464 l_Total_Earnings_This_Pay,
465 l_Total_Earnings_YTD,
466 l_Total_Deductions_This_pay,
467 l_Total_Deductions_YTD,
468 l_Net_Pay_This_pay,
469 l_Net_Pay_YTD,
470 l_Direct_Payments_This_Pay,
471 l_Direct_Payments_YTD,
472 l_Total_Payment_This_Pay,
473 l_Total_Payment_YTD);
474
475
476 l_narrative := 'Total Earnings';
477
478 IF g_debug THEN
479 hr_utility.set_location('Archiving value for ' || l_narrative,30);
480 END IF;
481
482 /* Archive This Pay values and YTD Values accordingly*/
483
484 archive_balances(p_effective_date =>p_effective_date
485 ,p_assact_id =>p_assact_id
486 ,p_narrative =>l_narrative
487 ,p_ytd =>l_Total_Earnings_YTD
488 ,p_curr =>l_Total_Earnings_This_Pay);
489
490
491
492 l_narrative := 'Total Deductions';
493
494 IF g_debug THEN
495 hr_utility.set_location('Archiving value for ' || l_narrative,40);
496 END IF;
497
498 archive_balances(p_effective_date => p_effective_date
499 ,p_assact_id => p_assact_id
500 ,p_narrative => l_narrative
501 ,p_ytd => l_Total_Deductions_YTD
502 ,p_curr => l_Total_Deductions_This_pay);
503
504
505
506 l_narrative := 'Net Pay';
507
508 IF g_debug THEN
509 hr_utility.set_location('Archiving value for ' || l_narrative,50);
510 END IF;
511
512 archive_balances(p_effective_date => p_effective_date
513 ,p_assact_id => p_assact_id
514 ,p_narrative => l_narrative
515 ,p_ytd => l_Net_Pay_YTD
516 ,p_curr => l_Net_Pay_This_pay);
517
518
519
520 l_narrative := 'Direct Payments';
521
522 IF g_debug THEN
523 hr_utility.set_location('Archiving value for ' || l_narrative,60);
524 END IF;
525
526 archive_balances(p_effective_date => p_effective_date
527 ,p_assact_id => p_assact_id
528 ,p_narrative => l_narrative
529 ,p_curr => l_Direct_Payments_This_Pay
530 ,p_ytd => l_Direct_Payments_YTD);
531
532
533 l_narrative := 'Total Payment';
534
535 IF g_debug THEN
536 hr_utility.set_location('Archiving value for ' || l_narrative,70);
537 END IF;
538
539 archive_balances(p_effective_date => p_effective_date
540 ,p_assact_id => p_assact_id
541 ,p_narrative => l_narrative
542 ,p_curr => l_Total_Payment_This_Pay
543 ,p_ytd => l_Total_Payment_YTD);
544
545
546
547 IF g_debug THEN
548 hr_utility.set_location('End of Archiving Stat Balances ',80);
549
550 hr_utility.set_location('Leaving Procedure pay_hk_payslip_archive.' || l_procedure_name,90);
551 END IF;
552
553
554 EXCEPTION
555 WHEN OTHERS THEN
556 IF g_debug THEN
557 hr_utility.set_location('Error in calling archive balance code :',11);
558 END IF;
559 RAISE;
560
561 END archive_stat_balances;
562
563
564
565
566
567 --------------------------------------------------------------------------------------+
568 -- This procedure calls 'pay_emp_action_arch.get_personal_information' that actually
569 -- archives the employee details,employee address details, Employer Address Details
570 -- and Net Pay Distribution information. Procedure 'get_personal_information' is
571 -- is passed tax_unit_id to make core provided 'Choose Payslip' work for us.
572 -- The action DF structures used are -
573 -- ADDRESS DETAILS
574 -- EMPLOYEE DETAILS
575 -- EMPLOYEE NET PAY DISTRIBUTION
576 -- EMPLOYEE OTHER INFORMATION
577 -- After core procedure completes the archival, the information stored for category
578 -- EMPLOYEE_NET_PAY_DISTRIBUTION is updated with currency code
579 ---------------------------------------------------------------------------------------+
580
581 PROCEDURE archive_employee_details (p_payroll_action_id IN NUMBER
582 , p_pay_assignment_action_id IN NUMBER
583 , p_assactid IN NUMBER
584 , p_assignment_id IN NUMBER
585 , p_curr_pymt_ass_act_id IN NUMBER
586 , p_date_earned IN DATE
587 , p_latest_period_end_date IN DATE
588 , p_run_effective_date IN DATE
589 , p_time_period_id IN NUMBER
590 , p_pre_effective_date IN DATE /* Bug 5736815 */) IS
591
592 -- Cursor to select the archived information for category 'EMPLOYEE NET PAY DISTRIBUTION'
593 -- by core package.
594
595 CURSOR csr_action_information_id(p_assact_id NUMBER)
596 IS
597 SELECT action_information_id
598 ,action_information1
599 ,action_information2
600 FROM pay_action_information
601 WHERE action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION'
602 AND action_context_id = p_assact_id
603 AND action_context_type = 'AAP';
604
605 -- Cursor to select the tax_unit_id of the prepayment needed for archival
606
607 CURSOR csr_payment_runs(p_assignment_action_id NUMBER)
608 IS
609 SELECT tax_unit_id,mailstop
610 FROM pay_hk_asg_payment_runs_v
611 WHERE assignment_action_id = p_assignment_action_id;
612
613
614 -- Cursor to get the bank name,percentage and currency code using the view
615 -- pay_sg_asg_net_payments_v
616
617 -- Cursor to get MPF Due Date
618
619 CURSOR csr_get_mpf_date(p_payroll_action_id NUMBER)
620 IS
621 SELECT pay_core_utils.get_parameter('MPF_DUE_DATE',legislative_parameters)
622 FROM pay_payroll_actions ppa
623 WHERE ppa.payroll_action_id = p_payroll_action_id;
624
625 CURSOR csr_bank_details(p_curr_pymt_ass_act_id NUMBER, l_personal_payment_method_id NUMBER, l_org_payment_method_id NUMBER) /* Bug No : 2672510 */
626 IS
627 SELECT pea.segment2 branch_code
628 , pea.segment3 account_number
629 , pop.currency_code
630 FROM pay_external_accounts pea
631 , pay_personal_payment_methods_f ppm
632 , pay_org_payment_methods_f pop
633 , pay_pre_payments ppp
634 , hr_lookups hl
635 WHERE ppm.personal_payment_method_id(+) = ppp.personal_payment_method_id
636 AND pop.org_payment_method_id = ppp.org_payment_method_id
637 AND pea.segment3 = hl.lookup_code (+)
638 AND hl.lookup_type(+) = 'HK_ACCOUNT_TYPE'
639 AND pea.external_account_id(+) = ppm.external_account_id
640 AND ppp.assignment_action_id = p_curr_pymt_ass_act_id
641 AND ((ppp.personal_payment_method_id = l_personal_payment_method_id) or
642 (ppp.org_payment_method_id = l_org_payment_method_id and ppp.personal_payment_method_id is null)) /* Bug No : 2672510 */
643 AND p_pre_effective_date BETWEEN pop.effective_start_date
644 AND pop.effective_end_date
645 AND p_pre_effective_date BETWEEN nvl(ppm.effective_start_date, p_pre_effective_date)
646 AND nvl(ppm.effective_end_date, p_pre_effective_date); /* Bug 5736815 */
647
648 l_action_info_id NUMBER;
649 l_ovn NUMBER;
650 l_tax_unit_id NUMBER;
651 l_branch_code varchar2(100);
652 l_account varchar2(100);
653
654 l_procedure_name VARCHAR2(80);
655
656 l_bank_account_name pay_hk_asg_payment_meth_v.BANK_ACCOUNT_NAME%TYPE;
657 l_percentage NUMBER;
658 l_bank_account pay_hk_asg_payment_meth_v.BANK_ACCOUNT%TYPE;
659 l_payment_method pay_hk_asg_payment_meth_v.PAYMENT_METHOD%TYPE;
660 l_currency pay_hk_asg_payment_meth_v.CURRENCY%TYPE;
661 l_payment_amount NUMBER;
662 l_mail_stop pay_hk_asg_payment_runs_v.MAILSTOP%TYPE;
663
664 l_mpf_due_date VARCHAR2(100);
665
666
667 BEGIN
668
669 l_procedure_name := 'archive_employee_details'; -- Bug: 3604131
670
671 IF g_debug THEN
672 hr_utility.set_location('Entering Procedure pay_hk_payslip_archive.'|| l_procedure_name,10);
673
674
675 -- call generic procedure to retrieve and archive all data for
676 -- EMPLOYEE DETAILS, ADDRESS DETAILS and EMPLOYEE NET PAY DISTRIBUTION
677
678 hr_utility.trace('Opening Cursor csr_payment_runs');
679 END IF;
680
681 OPEN csr_payment_runs(p_curr_pymt_ass_act_id);
682 FETCH csr_payment_runs INTO l_tax_unit_id,l_mail_stop;
683 CLOSE csr_payment_runs;
684
685 IF g_debug THEN
686 hr_utility.trace('Closing Cursor csr_payment_runs');
687
688 hr_utility.trace('Opening Cursor csr_get mpf date');
689 END IF;
690
691 OPEN csr_get_mpf_date(p_payroll_action_id);
692 FETCH csr_get_mpf_date into l_mpf_due_date;
693 CLOSE csr_get_mpf_date;
694 IF g_debug THEN
695 hr_utility.trace('Closing Cursor csr get mpf date');
696 END IF;
697
698 l_mpf_due_date:=to_char(to_date(l_mpf_due_date,'YYYY/MM/DD'),'DD-Mon-YYYY');
699
700 IF g_debug THEN
701 hr_utility.set_location('Calling pay_emp_action_arch.get_personal_information ',20);
702 END IF;
703
704 pay_emp_action_arch.get_personal_information
705 (p_payroll_action_id => p_payroll_action_id -- archive payroll_action_id
706 , p_assactid => p_assactid -- archive assignment_action_id
707 , p_assignment_id => p_assignment_id -- current assignment_id
708 , p_curr_pymt_ass_act_id => p_curr_pymt_ass_act_id -- prepayment assignment_action_id
709 , p_curr_eff_date => p_run_effective_date -- run effective_date
710 , p_date_earned => p_date_earned -- payroll date_earned
711 , p_curr_pymt_eff_date => p_latest_period_end_date -- latest period payment date
712 , p_tax_unit_id => l_tax_unit_id -- tax_unit_id needed for Choose Payslip region.
713 , p_time_period_id => p_time_period_id -- payroll time_period_id
714 , p_ppp_source_action_id => NULL
715 , p_run_action_id => p_pay_assignment_action_id
716 );
717
718 IF g_debug THEN
719 hr_utility.set_location('Returned from pay_emp_action_arch.csr_personal_information ',30);
720
721 -- Retrieve and Archive the HK specific employee details (mailstop)
722
723 hr_utility.set_location('Archiving HK EMPLOYEE DETAILS',60);
724 END IF;
725
726 pay_action_information_api.create_action_information
727 ( p_action_information_id => l_action_info_id
728 , p_action_context_id => p_assactid
729 , p_action_context_type => 'AAP'
730 , p_object_version_number => l_ovn
731 , p_effective_date => p_latest_period_end_date
732 , p_source_id => NULL
733 , p_source_text => NULL
734 , p_action_information_category => 'HK EMPLOYEE DETAILS'
735 , p_action_information1 => NULL
736 , p_action_information2 => NULL
737 , p_action_information3 => NULL
738 , p_action_information21 => l_mpf_due_date
739 , p_action_information22 => l_mail_stop
740 );
741
742
743 IF g_debug THEN
744 hr_utility.trace('Opening Cursor csr_action_information_id');
745 END IF;
746
747 FOR net_pay_rec in csr_action_information_id(p_assactid)
748
749 LOOP
750 IF g_debug THEN
751 hr_utility.trace('Opening Cursor csr_bank_details');
752 END IF;
753 OPEN csr_bank_details(p_curr_pymt_ass_act_id,net_pay_rec.action_information2,net_pay_rec.action_information1); /* Bug No : 2672510 */
754 FETCH csr_bank_details INTO l_branch_code
755 ,l_bank_account
756 ,l_currency;
757
758
759 CLOSE csr_bank_details;
760 IF g_debug THEN
761 hr_utility.trace('Closing Cursor csr_bank_details');
762 END IF;
763
764 l_ovn := 1;
765 l_account:=null;
766
767 if (l_branch_code is not null) and (l_bank_account is not null) then
768 l_account:=l_branch_code||'-'||l_bank_account;
769 end if;
770
771 IF g_debug THEN
772 hr_utility.trace('branch code:'||l_branch_code);
773 hr_utility.trace('account number: '||l_bank_account);
774 hr_utility.trace('action information 2:'||net_pay_rec.action_information2);
775 hr_utility.trace('assignment_action_id:'||p_curr_pymt_ass_act_id);
776 hr_utility.trace('Account:'||l_account);
777 END IF;
778
779
780 pay_action_information_api.update_action_information
781 ( p_action_information_id => net_pay_rec.action_information_id
782 , p_object_version_number => l_ovn
783 , p_action_information9 => l_account
784 , p_action_information10 => l_currency
785 );
786
787 END LOOP;
788
789 IF g_debug THEN
790
791 hr_utility.trace('Closing Cursor csr_action_information_id');
792
793 hr_utility.set_location('Leaving Procedure pay_hk_payslip_archive.' || l_procedure_name,10);
794 END IF;
795
796 EXCEPTION
797 WHEN OTHERS THEN
798 IF g_debug THEN
799 hr_utility.set_location('Error in archiving Employee details ',5);
800 END IF;
801 RAISE;
802
803 END archive_employee_details;
804
805
806
807 --------------------------------------------------------------------+
808 -- Procedure to archive Accrual Details.
809
810 --------------------------------------------------------------------+
811
812 --------------------------------------------------------------------+
813 -- Procedure to archive Accruals
814 --------------------------------------------------------------------+
815
816 PROCEDURE archive_accrual_details ( p_payroll_action_id IN NUMBER
817 , p_time_period_id IN NUMBER
818 , p_assignment_id IN NUMBER
819 , p_date_earned IN DATE
820 , p_effective_date IN DATE
821 , p_assact_id IN NUMBER
822 , p_assignment_action_id IN NUMBER
823 , p_period_end_date IN DATE
824 , p_period_start_date IN DATE ) IS
825
826
827 -- Cursor to get the Leave Balance Details .
828
829 CURSOR csr_leave_balance( p_assignment_action_id NUMBER
830 , p_assignment_id NUMBER)
831 IS
832 SELECT pap.accrual_plan_name
833 , hr_general_utilities.get_lookup_meaning('US_PTO_ACCRUAL',pap.accrual_category)
834 , pap.accrual_units_of_measure
835 , ppa.payroll_id
836 , pap.business_group_id
837 , pap.accrual_plan_id
838 FROM pay_accrual_plans pap,
839 pay_element_types_f pet,
840 pay_element_links_f pel,
841 pay_element_entries_f pee,
842 pay_assignment_actions paa,
843 pay_payroll_actions ppa
844 WHERE pet.element_type_id = pap.accrual_plan_element_type_id
845 AND pel.element_type_id = pet.element_type_id
846 AND pee.element_link_id = pel.element_link_id
847 AND paa.assignment_id = pee.assignment_id
848 AND ppa.payroll_action_id = paa.payroll_action_id
849 AND pap.accrual_category = 'HKAL'
850 AND ppa.action_type IN('R','Q')
851 AND ppa.action_status = 'C'
852 AND ppa.date_earned BETWEEN pet.effective_start_date
853 AND pet.effective_end_date
854 AND ppa.date_earned BETWEEN pel.effective_start_date
855 AND pel.effective_end_date
856 AND ppa.date_earned BETWEEN pee.effective_start_date
857 AND pee.effective_end_date
858 AND paa.assignment_id = p_assignment_id
859 AND paa.assignment_action_id = p_assignment_action_id;
860
861
862 l_action_info_id NUMBER;
863 l_accrual_plan_id pay_accrual_plans.accrual_plan_id%type;
864 l_accrual_plan_name pay_accrual_plans.accrual_plan_name%type;
865 l_accrual_category pay_accrual_plans.accrual_category%type;
866 l_accrual_uom pay_accrual_plans.accrual_units_of_measure%type;
867 l_payroll_id pay_payrolls_f.payroll_id%type;
868 l_procedure_name VARCHAR2(80);
869 l_business_group_id NUMBER;
870 l_effective_date DATE;
871 l_annual_leave_balance NUMBER;
872 l_ovn NUMBER;
873 l_leave_taken NUMBER;
874
875 l_start_date DATE;
876 l_end_date DATE;
877 l_net_entitlement NUMBER;
878 l_accrual_end_date DATE;
879 l_accrual NUMBER;
880
881
882 BEGIN
883
884 l_procedure_name := 'archive_employee_details'; -- Bug: 3604131
885
886 IF g_debug THEN
887 hr_utility.set_location('Start of accrual archival code',1);
888
889 hr_utility.set_location('Entering Procedure pay_sg_payslip_archive.' || l_procedure_name,10);
890
891 hr_utility.trace('Opening Cursor csr_leave_balance');
892 END IF;
893
894 OPEN csr_leave_balance(p_assignment_action_id,p_assignment_id);
895 FETCH csr_leave_balance INTO
896 l_accrual_plan_name,
897 l_accrual_category,
898 l_accrual_uom,
899 l_payroll_id,
900 l_business_group_id,
901 l_accrual_plan_id;
902
903 CLOSE csr_leave_balance;
904
905 IF g_debug THEN
906 hr_utility.trace('Closing Cursor csr_leave_balance');
907
908 -- Call to get annual leave balance
909
910 hr_utility.set_location('Archiving Annual leave Balance information',2);
911 END IF;
912
913 per_accrual_calc_functions.get_net_accrual( p_assignment_id => p_assignment_id,
914 p_plan_id => l_accrual_plan_id,
915 p_payroll_id => l_payroll_id,
916 p_business_group_id => l_business_group_id,
917 p_calculation_date => p_effective_date,
918 p_start_date => l_start_date,
919 p_end_date => l_end_date,
920 p_accrual_end_date => l_accrual_end_date,
921 p_accrual => l_accrual,
922 p_net_entitlement => l_net_entitlement);
923
924 IF g_debug THEN
925 hr_utility.set_location('Archiving Leave Taken information',2);
926 END IF;
927
928
929 IF l_accrual_plan_name IS NOT NULL THEN
930
931 pay_action_information_api.create_action_information
932 ( p_action_information_id => l_action_info_id
933 , p_action_context_id => p_assact_id
934 , p_action_context_type => 'AAP'
935 , p_object_version_number => l_ovn
936 , p_effective_date => p_effective_date
937 , p_source_id => NULL
938 , p_source_text => NULL
939 , p_action_information_category => 'APAC ACCRUALS'
940 , p_action_information1 => l_accrual_plan_name
941 , p_action_information2 => l_accrual_category
942 , p_action_information4 => fnd_number.number_to_canonical(l_net_entitlement) -- Bug: 3604131
943 , p_action_information5 => l_accrual_uom
944 );
945
946 END IF;
947
948 IF g_debug THEN
949 hr_utility.set_location('Leaving Procedure pay_hk_payslip_archive.' || l_procedure_name,10);
950 END IF;
951
952
953 EXCEPTION
954 WHEN OTHERS THEN
955 IF g_debug THEN
956 hr_utility.set_location('Error raised in archiving Accruals and Leave Taken ',5);
957 END IF;
958 RAISE;
959
960 END archive_accrual_details;
961
962
963
964
965 --------------------------------------------------------------------+
966 -- Procedure to call the internal procedures to actually
967 -- the archive the data. The procedure called are -
968 -- archive_accrual_details
969 -- archive_employee_details
970 -- pay_apac_payslip_archive.archive_user_elements
971 -- archive_stat_balances
972 -- archive_stat_elements
973 -- pay_apac_payslip_archive.archive_user_balances
974 --------------------------------------------------------------------+
975
976 PROCEDURE archive_code (p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE,
977 p_effective_date IN DATE) IS
978
979
980 -- Cursor to select all the locked prepayment and payrolls by the archive
981 -- assignment action. The records are ordered descending as we only need
982 -- latest payroll run in the prepayment.
983 -- Bug# 3580617 Modified the SQL query of the cursor get_payslip_aa.
984
985 cursor get_payslip_aa(p_master_aa_id number)
986 is
987 select paa_arch_chd.assignment_action_id chld_arc_assignment_action_id,
988 paa_pre.assignment_action_id pre_assignment_action_id,
989 paa_run.assignment_action_id run_assignment_action_id,
990 ppa_pre.effective_date pre_effective_date,
991 paa_arch_chd.assignment_id,
992 ppa_run.payroll_action_id,
993 ppa_run.effective_date run_effective_date,
994 ppa_run.date_earned run_date_earned,
995 ptp.start_date period_start_date,
996 ptp.end_date period_end_date,
997 ptp.regular_payment_date,
998 ptp.time_period_id
999 from pay_assignment_actions paa_arch_chd,
1000 pay_assignment_actions paa_arch_mst,
1001 pay_assignment_actions paa_pre,
1002 pay_action_interlocks pai_pre,
1003 pay_assignment_actions paa_run,
1004 pay_action_interlocks pai_run,
1005 pay_payroll_actions ppa_pre,
1006 pay_payroll_actions ppa_run,
1007 per_time_periods ptp
1008 where paa_arch_mst.assignment_action_id = p_master_aa_id
1009 and paa_arch_chd.source_action_id = paa_arch_mst.assignment_action_id
1010 and paa_arch_chd.payroll_action_id = paa_arch_mst.payroll_action_id
1011 and paa_arch_chd.assignment_id = paa_arch_mst.assignment_id
1012 and pai_pre.locking_action_id = paa_arch_mst.assignment_action_id
1013 and pai_pre.locked_action_id = paa_pre.assignment_action_id
1014 and pai_run.locking_action_id = paa_arch_chd.assignment_action_id
1015 and pai_run.locked_action_id = paa_run.assignment_action_id
1016 and ppa_pre.payroll_action_id = paa_pre.payroll_action_id
1017 and ppa_pre.action_type in ('P','U')
1018 and ppa_run.payroll_action_id = paa_run.payroll_action_id
1019 and ppa_run.action_type in ('R','Q')
1020 and ptp.payroll_id = ppa_run.payroll_id
1021 and ppa_run.date_earned between ptp.start_date
1022 and ptp.end_date
1023 -- Get the highest in sequence for this payslip
1024 and paa_run.action_sequence = (select max(paa_run2.action_sequence)
1025 from pay_assignment_actions paa_run2,
1026 pay_action_interlocks pai_run2
1027 where pai_run2.locking_action_id = paa_arch_chd.assignment_action_id
1028 and pai_run2.locked_action_id = paa_run2.assignment_action_id
1029 );
1030
1031 /* Added for the bug#5671633
1032 This cursor returns actual termination date if it falls in the pay period */
1033
1034 CURSOR csr_payment_date(p_assignment_action_id NUMBER)
1035 IS
1036 SELECT pps.actual_termination_date
1037 FROM pay_payroll_actions ppa,
1038 pay_assignment_actions paa,
1039 per_time_periods ptp,
1040 per_all_assignments_f paf,
1041 per_periods_of_service pps
1042 WHERE paa.assignment_action_id = p_assignment_action_id
1043 AND ppa.payroll_action_id = paa.payroll_action_id
1044 AND ptp.payroll_id = ppa.payroll_id
1045 AND paf.assignment_id = paa.assignment_id
1046 AND pps.period_of_service_id = paf.period_of_service_id
1047 AND ppa.date_earned between ptp.start_date AND ptp.end_date
1048 AND pps.actual_termination_date between paf.effective_start_date and paf.effective_end_date
1049 AND pps.actual_termination_date between ptp.start_date AND ptp.end_date;
1050
1051 l_pre_pay_assact_id NUMBER;
1052 l_payment_date DATE :=NULL;
1053
1054 BEGIN
1055
1056 l_pre_pay_assact_id := 0; -- Bug: 3604131
1057
1058 IF g_debug THEN
1059 hr_utility.set_location('Start of archive code',20);
1060 hr_utility.trace('Opening Cursor get_payslip_aa');
1061 END IF;
1062
1063 -- Bug# 3580617 Included the following function call pay_core_payslip_utils.generate_child_actions.
1064
1065 pay_core_payslip_utils.generate_child_actions( p_assignment_action_id,
1066 p_effective_date
1067 );
1068
1069 FOR csr_rec IN get_payslip_aa(p_assignment_action_id)
1070 LOOP
1071 /* Added for the bug#5671633 */
1072 open csr_payment_date(csr_rec.run_assignment_action_id);
1073 fetch csr_payment_date into l_payment_date;
1074 if csr_payment_date%NOTFOUND then
1075 l_payment_date := csr_rec.regular_payment_date;
1076 end if;
1077 close csr_payment_date;
1078
1079 IF g_debug THEN
1080 hr_utility.set_location('csr_rec.run_assignment_action_id = ' || csr_rec.run_assignment_action_id,20);
1081 hr_utility.set_location('csr_rec.pre_assignment_action_id = ' || csr_rec.pre_assignment_action_id,30);
1082 END IF;
1083
1084
1085 -- Loop to be executed only once for a prepayment with latest payroll run details
1086 -- in the prepayment
1087
1088 IF l_pre_pay_assact_id <> csr_rec.pre_assignment_action_id THEN
1089
1090 -- Call to procedure to archive User Configurable Balnaces
1091
1092 pay_apac_payslip_archive.archive_user_balances
1093 ( p_arch_assignment_action_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action id
1094 , p_run_assignment_action_id => csr_rec.run_assignment_action_id -- payroll assignment action id
1095 , p_pre_effective_date => csr_rec.pre_effective_date -- prepayment effecive date
1096 );
1097
1098
1099 -- Call to procedure to archive Statutory Elements
1100
1101 archive_stat_elements
1102 ( p_assignment_action_id => csr_rec.pre_assignment_action_id -- prepayment assignment action id
1103 , p_assignment_id => csr_rec.assignment_id -- assignment id
1104 , p_effective_date => csr_rec.pre_effective_date -- prepayment effective date
1105 , p_assact_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action id
1106 );
1107
1108 -- Call to procedure to archive Statutory balances
1109
1110 archive_stat_balances
1111 ( p_assignment_action_id => csr_rec.pre_assignment_action_id -- prepayment assignment action id
1112 , p_assignment_id => csr_rec.assignment_id -- assignment id
1113 , p_date_earned => csr_rec.run_date_earned -- payroll date earned
1114 , p_effective_date => csr_rec.pre_effective_date -- prepayment effective date
1115 , p_assact_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action id
1116 );
1117
1118
1119
1120 -- Call to procedure to archive User Configurable Elements
1121
1122 pay_apac_payslip_archive.archive_user_elements
1123 ( p_arch_assignment_action_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action
1124 , p_pre_assignment_action_id => csr_rec.pre_assignment_action_id -- prepayment assignment action id
1125 , p_latest_run_assact_id => csr_rec.run_assignment_action_id -- payroll assignment action id
1126 , p_pre_effective_date => csr_rec.pre_effective_date -- prepayment effective date
1127 );
1128
1129
1130 -- Call to procedure to archive Employee Details
1131
1132 archive_employee_details
1133 ( p_payroll_action_id => g_archive_pact -- archive payroll action id
1134 , p_assactid => csr_rec.chld_arc_assignment_action_id -- archive action id
1135 , p_pay_assignment_action_id => csr_rec.run_assignment_action_id -- payroll run action id
1136 , p_assignment_id => csr_rec.assignment_id -- assignment_id
1137 , p_curr_pymt_ass_act_id => csr_rec.pre_assignment_action_id -- prepayment assignment_action_id
1138 , p_date_earned => csr_rec.run_date_earned -- payroll date_earned
1139 , p_latest_period_end_date => l_payment_date -- latest payment date
1140 , p_run_effective_date => csr_rec.run_effective_date -- run effective Date
1141 , p_time_period_id => csr_rec.time_period_id -- time_period_id from per_time_periods
1142 , p_pre_effective_date => csr_rec.pre_effective_date
1143 -- prepayment effective date, bug 5736815
1144 );
1145
1146 -- Call to procedure to archive accrual and absennce details
1147
1148 archive_accrual_details
1149 ( p_payroll_action_id => csr_rec.payroll_action_id -- latest payroll action id
1150 , p_time_period_id => csr_rec.time_period_id -- latest period time period id
1151 , p_assignment_id => csr_rec.assignment_id -- assignment id
1152 , p_date_earned => csr_rec.run_date_earned -- latest payroll date earned
1153 , p_effective_date => csr_rec.pre_effective_date -- prepayment effective date
1154 , p_assact_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action id
1155 , p_assignment_action_id => csr_rec.run_assignment_action_id -- payroll run action id
1156 , p_period_end_date => csr_rec.period_end_date -- latest period end date
1157 , p_period_start_date => csr_rec.period_start_date -- latest period start date
1158 );
1159
1160
1161
1162 END IF;
1163
1164 l_pre_pay_assact_id := csr_rec.pre_assignment_action_id;
1165
1166
1167 END LOOP;
1168
1169 IF g_debug THEN
1170 hr_utility.trace('Opening Cursor csr_assignment_actions');
1171
1172 hr_utility.set_location('End of archive code',37);
1173 END IF;
1174
1175 EXCEPTION
1176 WHEN OTHERS THEN
1177 IF g_debug THEN
1178 hr_utility.set_location('Error in archive code :',11);
1179 END IF;
1180 RAISE;
1181
1182 END archive_code;
1183
1184 /*
1185 * Bug 4260143 - Added the following function to return the assessed ri value
1186 */
1187
1188
1189 FUNCTION get_assessed_ri(p_run_result_id in pay_run_results.run_result_id%TYPE)
1190 RETURN VARCHAR2 IS
1191
1192 l_assessed_ri pay_run_result_values.result_value%TYPE;
1193 CURSOR csr_get_accessed_ri
1194 IS
1195 SELECT prrv.result_value
1196 FROM pay_input_values_f pivf,
1197 pay_run_result_values prrv
1198 WHERE prrv.run_result_id = p_run_result_id
1199 AND pivf.input_value_id = prrv.input_value_id
1200 AND pivf.name = 'Assessed RI';
1201 BEGIN
1202 l_assessed_ri := null;
1203
1204 OPEN csr_get_accessed_ri;
1205 FETCH csr_get_accessed_ri into l_assessed_ri;
1206 CLOSE csr_get_accessed_ri;
1207
1208 IF l_assessed_ri IS NOT NULL THEN
1209 RETURN l_assessed_ri;
1210 END IF;
1211
1212 RETURN null;
1213 END get_assessed_ri;
1214
1215
1216
1217 END pay_hk_payslip_archive;