[Home] [Help]
PACKAGE BODY: APPS.PAY_HK_PAYSLIP_ARCHIVE
Source
1 PACKAGE BODY pay_hk_payslip_archive AS
2 /* $Header: pyhkparc.pkb 120.5 2011/05/05 10:39:54 jmarupil ship $ */
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 , SUM(payment_amount) 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 group by element_reporting_name,classification_name,assessed_ri,period_start_date,period_end_date;
275
276
277 l_action_info_id NUMBER;
278 l_ovn NUMBER;
279 l_foreign_currency_amount NUMBER;
280 l_rate NUMBER;
281 l_procedure_name VARCHAR2(80);
282
283 BEGIN
284 l_procedure_name := 'archive_stat_elements'; -- Bug: 3604131
285
286 IF g_debug THEN
287 hr_utility.set_location('Entering Procedure pay_hk_payslip_archive.' || l_procedure_name,10);
288
289 hr_utility.trace('Opening Cursor csr_std_elements');
290 END IF;
291
292 FOR csr_rec IN csr_std_elements(p_assignment_action_id,p_assignment_id)
293
294 LOOP
295
296 IF g_debug THEN
297 hr_utility.set_location('Archiving Standard Element Details',20);
298 END IF;
299
300
301 if csr_rec.classification_name<>'Employer Liabilities' then
302
303 pay_action_information_api.create_action_information
304 ( p_action_information_id => l_action_info_id
305 , p_action_context_id => p_assact_id
306 , p_action_context_type => 'AAP'
307 , p_object_version_number => l_ovn
308 , p_effective_date => p_effective_date
309 , p_source_id => NULL
310 , p_source_text => NULL
311 , p_action_information_category => 'APAC ELEMENTS'
312 , p_action_information1 => csr_rec.element_reporting_name
313 , p_action_information2 => NULL
314 , p_action_information3 => NULL
315 , p_action_information4 => csr_rec.classification_name
316 , p_action_information5 => fnd_number.number_to_canonical(csr_rec.payment_amount) -- Bug: 3604131
317 , p_action_information11 => fnd_number.number_to_canonical(csr_rec.assessed_ri) -- Bug: 3604131
318 , p_action_information13 => csr_rec.period_start_date
319 , p_action_information14 => csr_rec.period_end_date);
320
321 else
322
323 pay_action_information_api.create_action_information
324 ( p_action_information_id => l_action_info_id
325 , p_action_context_id => p_assact_id
326 , p_action_context_type => 'AAP'
327 , p_object_version_number => l_ovn
328 , p_effective_date => p_effective_date
329 , p_source_id => NULL
330 , p_source_text => NULL
331 , p_action_information_category => 'APAC BALANCES 2'
332 , p_action_information1 => csr_rec.element_reporting_name
333 , p_action_information2 => NULL
334 , p_action_information3 => NULL
335 , p_action_information4 => fnd_number.number_to_canonical(csr_rec.payment_amount) -- Bug: 3604131
336 , p_action_information6 => csr_rec.period_start_date
337 , p_action_information7 => csr_rec.period_end_date
338 , p_action_information8 => fnd_number.number_to_canonical(csr_rec.assessed_ri)); -- Bug: 3604131
339
340 end if;
341
342
343 END LOOP;
344 IF g_debug THEN
345 hr_utility.trace('Closing Cursor csr_std_elements');
346 hr_utility.set_location('End of archive Standard Element',4);
347 hr_utility.set_location('Leaving Procedure pay_hk_payslip_archive.' || l_procedure_name,10);
348 END IF;
349
350 EXCEPTION
351 WHEN OTHERS THEN
352 IF g_debug THEN
353 hr_utility.set_location('Error in archiving Standard Elements ',5);
354 END IF;
355 RAISE;
356
357 END archive_stat_elements;
358
359
360
361 --------------------------------------------------------------------+
362 -- Procedure to archive the Statutory balances
363 --------------------------------------------------------------------+
364
365 PROCEDURE archive_balances( p_effective_date IN DATE
366 ,p_assact_id IN NUMBER
367 ,p_narrative IN VARCHAR2
368 ,p_ytd IN NUMBER
369 ,p_curr IN NUMBER) IS
370
371 l_action_info_id NUMBER;
372 l_ovn NUMBER;
373 l_procedure_name VARCHAR2(80);
374
375 BEGIN
376
377 l_procedure_name := 'archive_balances'; -- Bug: 3604131
378
379 IF g_debug THEN
380 hr_utility.set_location('Entering procedure ' || l_procedure_name,10);
381 hr_utility.set_location('archiving balances :',10);
382 END IF;
383
384 -- Archive Statutory balances
385
386 pay_action_information_api.create_action_information
387 ( p_action_information_id => l_action_info_id
388 , p_action_context_id => p_assact_id
389 , p_action_context_type => 'AAP'
390 , p_object_version_number => l_ovn
391 , p_effective_date => p_effective_date
392 , p_source_id => NULL
393 , p_source_text => NULL
394 , p_action_information_category => 'APAC BALANCES'
395 , p_action_information1 => p_narrative
396 , p_action_information2 => NULL
397 , p_action_information3 => NULL
398 , p_action_information4 => fnd_number.number_to_canonical(p_ytd) -- Bug: 3604131
399 , p_action_information5 => fnd_number.number_to_canonical(p_curr) -- Bug: 3604131
400 );
401
402
403 EXCEPTION
404 WHEN OTHERS THEN
405 IF g_debug THEN
406 hr_utility.set_location('Error in archiving balance :',11);
407 END IF;
408 RAISE;
409
410 END archive_balances;
411
412
413
414 --------------------------------------------------------------------+
415 -- Procedure to calculate the balances values
416 -- Calls procedure archive_balances and actually archives all the balance values
417 --------------------------------------------------------------------+
418
419
420 PROCEDURE archive_stat_balances(p_assignment_action_id IN NUMBER
421 ,p_assignment_id IN NUMBER
422 ,p_date_earned IN DATE
423 ,p_effective_date IN DATE
424 ,p_assact_id IN NUMBER) IS
425
426
427 l_Total_Earnings_This_Pay NUMBER;
428 l_Total_Earnings_YTD NUMBER;
429 l_Total_Deductions_This_pay NUMBER;
430 l_Total_Deductions_YTD NUMBER;
431 l_Net_Pay_This_pay NUMBER;
432 l_Net_Pay_YTD NUMBER;
433 l_Direct_Payments_This_Pay NUMBER;
434 l_Direct_Payments_YTD NUMBER;
435 l_Total_Payment_This_Pay NUMBER;
436 l_Total_Payment_YTD NUMBER;
437 l_tax_unit_id NUMBER;
438 l_narrative VARCHAR2(150);
439 l_procedure_name VARCHAR2(80);
440
441
442 CURSOR csr_tax_unit_id(p_assignment_action_id NUMBER)
443 IS
444 SELECT tax_unit_id
445 FROM pay_assignment_actions
446 WHERE assignment_action_id = p_assignment_action_id;
447
448 BEGIN
449
450 l_procedure_name := 'Archive_Stat_Balances'; -- Bug: 3604131
451
452 IF g_debug THEN
453 hr_utility.set_location('Entering Procedure pay_hk_payslip_archive.' || l_procedure_name,10);
454 hr_utility.set_location('Calling balance_total from pay_hk_soe_pkg',20);
455 END IF;
456
457 -- Get the totals of all the balances
458
459 OPEN csr_tax_unit_id(p_assignment_action_id);
460 FETCH csr_tax_unit_id INTO l_tax_unit_id;
461 CLOSE csr_tax_unit_id;
462
463 pay_hk_soe_pkg.balance_totals(p_assignment_action_id,
464 l_tax_unit_id,
465 l_Total_Earnings_This_Pay,
466 l_Total_Earnings_YTD,
467 l_Total_Deductions_This_pay,
468 l_Total_Deductions_YTD,
469 l_Net_Pay_This_pay,
470 l_Net_Pay_YTD,
471 l_Direct_Payments_This_Pay,
472 l_Direct_Payments_YTD,
473 l_Total_Payment_This_Pay,
474 l_Total_Payment_YTD);
475
476
477 l_narrative := 'Total Earnings';
478
479 IF g_debug THEN
480 hr_utility.set_location('Archiving value for ' || l_narrative,30);
481 END IF;
482
483 /* Archive This Pay values and YTD Values accordingly*/
484
485 archive_balances(p_effective_date =>p_effective_date
486 ,p_assact_id =>p_assact_id
487 ,p_narrative =>l_narrative
488 ,p_ytd =>l_Total_Earnings_YTD
489 ,p_curr =>l_Total_Earnings_This_Pay);
490
491
492
493 l_narrative := 'Total Deductions';
494
495 IF g_debug THEN
496 hr_utility.set_location('Archiving value for ' || l_narrative,40);
497 END IF;
498
499 archive_balances(p_effective_date => p_effective_date
500 ,p_assact_id => p_assact_id
501 ,p_narrative => l_narrative
502 ,p_ytd => l_Total_Deductions_YTD
503 ,p_curr => l_Total_Deductions_This_pay);
504
505
506
507 l_narrative := 'Net Pay';
508
509 IF g_debug THEN
510 hr_utility.set_location('Archiving value for ' || l_narrative,50);
511 END IF;
512
513 archive_balances(p_effective_date => p_effective_date
514 ,p_assact_id => p_assact_id
515 ,p_narrative => l_narrative
516 ,p_ytd => l_Net_Pay_YTD
517 ,p_curr => l_Net_Pay_This_pay);
518
519
520
521 l_narrative := 'Direct Payments';
522
523 IF g_debug THEN
524 hr_utility.set_location('Archiving value for ' || l_narrative,60);
525 END IF;
526
527 archive_balances(p_effective_date => p_effective_date
528 ,p_assact_id => p_assact_id
529 ,p_narrative => l_narrative
530 ,p_curr => l_Direct_Payments_This_Pay
531 ,p_ytd => l_Direct_Payments_YTD);
532
533
534 l_narrative := 'Total Payment';
535
536 IF g_debug THEN
537 hr_utility.set_location('Archiving value for ' || l_narrative,70);
538 END IF;
539
540 archive_balances(p_effective_date => p_effective_date
541 ,p_assact_id => p_assact_id
542 ,p_narrative => l_narrative
543 ,p_curr => l_Total_Payment_This_Pay
544 ,p_ytd => l_Total_Payment_YTD);
545
546
547
548 IF g_debug THEN
549 hr_utility.set_location('End of Archiving Stat Balances ',80);
550
551 hr_utility.set_location('Leaving Procedure pay_hk_payslip_archive.' || l_procedure_name,90);
552 END IF;
553
554
555 EXCEPTION
556 WHEN OTHERS THEN
557 IF g_debug THEN
558 hr_utility.set_location('Error in calling archive balance code :',11);
559 END IF;
560 RAISE;
561
562 END archive_stat_balances;
563
564
565
566
567
568 --------------------------------------------------------------------------------------+
569 -- This procedure calls 'pay_emp_action_arch.get_personal_information' that actually
570 -- archives the employee details,employee address details, Employer Address Details
571 -- and Net Pay Distribution information. Procedure 'get_personal_information' is
572 -- is passed tax_unit_id to make core provided 'Choose Payslip' work for us.
573 -- The action DF structures used are -
574 -- ADDRESS DETAILS
575 -- EMPLOYEE DETAILS
576 -- EMPLOYEE NET PAY DISTRIBUTION
577 -- EMPLOYEE OTHER INFORMATION
578 -- After core procedure completes the archival, the information stored for category
579 -- EMPLOYEE_NET_PAY_DISTRIBUTION is updated with currency code
580 ---------------------------------------------------------------------------------------+
581
582 PROCEDURE archive_employee_details (p_payroll_action_id IN NUMBER
583 , p_pay_assignment_action_id IN NUMBER
584 , p_assactid IN NUMBER
585 , p_assignment_id IN NUMBER
586 , p_curr_pymt_ass_act_id IN NUMBER
587 , p_date_earned IN DATE
588 , p_latest_period_end_date IN DATE
589 , p_run_effective_date IN DATE
590 , p_time_period_id IN NUMBER
591 , p_pre_effective_date IN DATE /* Bug 5736815 */) IS
592
593 -- Cursor to select the archived information for category 'EMPLOYEE NET PAY DISTRIBUTION'
594 -- by core package.
595
596 CURSOR csr_action_information_id(p_assact_id NUMBER)
597 IS
598 SELECT action_information_id
599 ,action_information1
600 ,action_information2
601 FROM pay_action_information
602 WHERE action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION'
603 AND action_context_id = p_assact_id
604 AND action_context_type = 'AAP';
605
606 -- Cursor to select the tax_unit_id of the prepayment needed for archival
607
608 CURSOR csr_payment_runs(p_assignment_action_id NUMBER)
609 IS
610 SELECT tax_unit_id,mailstop
611 FROM pay_hk_asg_payment_runs_v
612 WHERE assignment_action_id = p_assignment_action_id;
613
614
615 -- Cursor to get the bank name,percentage and currency code using the view
616 -- pay_sg_asg_net_payments_v
617
618 -- Cursor to get MPF Due Date
619
620 CURSOR csr_get_mpf_date(p_payroll_action_id NUMBER)
621 IS
622 SELECT pay_core_utils.get_parameter('MPF_DUE_DATE',legislative_parameters)
623 FROM pay_payroll_actions ppa
624 WHERE ppa.payroll_action_id = p_payroll_action_id;
625
626 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 */
627 IS
628 SELECT pea.segment2 branch_code
629 , pea.segment3 account_number
630 , pop.currency_code
631 FROM pay_external_accounts pea
632 , pay_personal_payment_methods_f ppm
633 , pay_org_payment_methods_f pop
634 , pay_pre_payments ppp
635 , hr_lookups hl
636 WHERE ppm.personal_payment_method_id(+) = ppp.personal_payment_method_id
637 AND pop.org_payment_method_id = ppp.org_payment_method_id
638 AND pea.segment3 = hl.lookup_code (+)
639 AND hl.lookup_type(+) = 'HK_ACCOUNT_TYPE'
640 AND pea.external_account_id(+) = ppm.external_account_id
641 AND ppp.assignment_action_id = p_curr_pymt_ass_act_id
642 AND ((ppp.personal_payment_method_id = l_personal_payment_method_id) or
643 (ppp.org_payment_method_id = l_org_payment_method_id and ppp.personal_payment_method_id is null)) /* Bug No : 2672510 */
644 AND p_pre_effective_date BETWEEN pop.effective_start_date
645 AND pop.effective_end_date
646 AND p_pre_effective_date BETWEEN nvl(ppm.effective_start_date, p_pre_effective_date)
647 AND nvl(ppm.effective_end_date, p_pre_effective_date); /* Bug 5736815 */
648
649 l_action_info_id NUMBER;
650 l_ovn NUMBER;
651 l_tax_unit_id NUMBER;
652 l_branch_code varchar2(100);
653 l_account varchar2(100);
654
655 l_procedure_name VARCHAR2(80);
656
657 l_bank_account_name pay_hk_asg_payment_meth_v.BANK_ACCOUNT_NAME%TYPE;
658 l_percentage NUMBER;
659 l_bank_account pay_hk_asg_payment_meth_v.BANK_ACCOUNT%TYPE;
660 l_payment_method pay_hk_asg_payment_meth_v.PAYMENT_METHOD%TYPE;
661 l_currency pay_hk_asg_payment_meth_v.CURRENCY%TYPE;
662 l_payment_amount NUMBER;
663 l_mail_stop pay_hk_asg_payment_runs_v.MAILSTOP%TYPE;
664
665 l_mpf_due_date VARCHAR2(100);
666
667
668 BEGIN
669
670 l_procedure_name := 'archive_employee_details'; -- Bug: 3604131
671
672 IF g_debug THEN
673 hr_utility.set_location('Entering Procedure pay_hk_payslip_archive.'|| l_procedure_name,10);
674
675
676 -- call generic procedure to retrieve and archive all data for
677 -- EMPLOYEE DETAILS, ADDRESS DETAILS and EMPLOYEE NET PAY DISTRIBUTION
678
679 hr_utility.trace('Opening Cursor csr_payment_runs');
680 END IF;
681
682 OPEN csr_payment_runs(p_curr_pymt_ass_act_id);
683 FETCH csr_payment_runs INTO l_tax_unit_id,l_mail_stop;
684 CLOSE csr_payment_runs;
685
686 IF g_debug THEN
687 hr_utility.trace('Closing Cursor csr_payment_runs');
688
689 hr_utility.trace('Opening Cursor csr_get mpf date');
690 END IF;
691
692 OPEN csr_get_mpf_date(p_payroll_action_id);
693 FETCH csr_get_mpf_date into l_mpf_due_date;
694 CLOSE csr_get_mpf_date;
695 IF g_debug THEN
696 hr_utility.trace('Closing Cursor csr get mpf date');
697 END IF;
698
699 l_mpf_due_date:=to_char(to_date(l_mpf_due_date,'YYYY/MM/DD'),'DD-Mon-YYYY');
700
701 IF g_debug THEN
702 hr_utility.set_location('Calling pay_emp_action_arch.get_personal_information ',20);
703 END IF;
704
705 pay_emp_action_arch.get_personal_information
706 (p_payroll_action_id => p_payroll_action_id -- archive payroll_action_id
707 , p_assactid => p_assactid -- archive assignment_action_id
708 , p_assignment_id => p_assignment_id -- current assignment_id
709 , p_curr_pymt_ass_act_id => p_curr_pymt_ass_act_id -- prepayment assignment_action_id
710 , p_curr_eff_date => p_run_effective_date -- run effective_date
711 , p_date_earned => p_date_earned -- payroll date_earned
712 , p_curr_pymt_eff_date => p_latest_period_end_date -- latest period payment date
713 , p_tax_unit_id => l_tax_unit_id -- tax_unit_id needed for Choose Payslip region.
714 , p_time_period_id => p_time_period_id -- payroll time_period_id
715 , p_ppp_source_action_id => NULL
716 , p_run_action_id => p_pay_assignment_action_id
717 );
718
719 IF g_debug THEN
720 hr_utility.set_location('Returned from pay_emp_action_arch.csr_personal_information ',30);
721
722 -- Retrieve and Archive the HK specific employee details (mailstop)
723
724 hr_utility.set_location('Archiving HK EMPLOYEE DETAILS',60);
725 END IF;
726
727 pay_action_information_api.create_action_information
728 ( p_action_information_id => l_action_info_id
729 , p_action_context_id => p_assactid
730 , p_action_context_type => 'AAP'
731 , p_object_version_number => l_ovn
732 , p_effective_date => p_latest_period_end_date
733 , p_source_id => NULL
734 , p_source_text => NULL
735 , p_action_information_category => 'HK EMPLOYEE DETAILS'
736 , p_action_information1 => NULL
737 , p_action_information2 => NULL
738 , p_action_information3 => NULL
739 , p_action_information21 => l_mpf_due_date
740 , p_action_information22 => l_mail_stop
741 );
742
743
744 IF g_debug THEN
745 hr_utility.trace('Opening Cursor csr_action_information_id');
746 END IF;
747
748 FOR net_pay_rec in csr_action_information_id(p_assactid)
749
750 LOOP
751 IF g_debug THEN
752 hr_utility.trace('Opening Cursor csr_bank_details');
753 END IF;
754 OPEN csr_bank_details(p_curr_pymt_ass_act_id,net_pay_rec.action_information2,net_pay_rec.action_information1); /* Bug No : 2672510 */
755 FETCH csr_bank_details INTO l_branch_code
756 ,l_bank_account
757 ,l_currency;
758
759
760 CLOSE csr_bank_details;
761 IF g_debug THEN
762 hr_utility.trace('Closing Cursor csr_bank_details');
763 END IF;
764
765 l_ovn := 1;
766 l_account:=null;
767
768 if (l_branch_code is not null) and (l_bank_account is not null) then
769 l_account:=l_branch_code||'-'||l_bank_account;
770 end if;
771
772 IF g_debug THEN
773 hr_utility.trace('branch code:'||l_branch_code);
774 hr_utility.trace('account number: '||l_bank_account);
775 hr_utility.trace('action information 2:'||net_pay_rec.action_information2);
776 hr_utility.trace('assignment_action_id:'||p_curr_pymt_ass_act_id);
777 hr_utility.trace('Account:'||l_account);
778 END IF;
779
780
781 pay_action_information_api.update_action_information
782 ( p_action_information_id => net_pay_rec.action_information_id
783 , p_object_version_number => l_ovn
784 , p_action_information9 => l_account
785 , p_action_information10 => l_currency
786 );
787
788 END LOOP;
789
790 IF g_debug THEN
791
792 hr_utility.trace('Closing Cursor csr_action_information_id');
793
794 hr_utility.set_location('Leaving Procedure pay_hk_payslip_archive.' || l_procedure_name,10);
795 END IF;
796
797 EXCEPTION
798 WHEN OTHERS THEN
799 IF g_debug THEN
800 hr_utility.set_location('Error in archiving Employee details ',5);
801 END IF;
802 RAISE;
803
804 END archive_employee_details;
805
806
807
808 --------------------------------------------------------------------+
809 -- Procedure to archive Accrual Details.
810
811 --------------------------------------------------------------------+
812
813 --------------------------------------------------------------------+
814 -- Procedure to archive Accruals
815 --------------------------------------------------------------------+
816
817 PROCEDURE archive_accrual_details ( p_payroll_action_id IN NUMBER
818 , p_time_period_id IN NUMBER
819 , p_assignment_id IN NUMBER
820 , p_date_earned IN DATE
821 , p_effective_date IN DATE
822 , p_assact_id IN NUMBER
823 , p_assignment_action_id IN NUMBER
824 , p_period_end_date IN DATE
825 , p_period_start_date IN DATE ) IS
826
827
828 -- Cursor to get the Leave Balance Details .
829
830 CURSOR csr_leave_balance( p_assignment_action_id NUMBER
831 , p_assignment_id NUMBER)
832 IS
833 SELECT pap.accrual_plan_name
834 , hr_general_utilities.get_lookup_meaning('US_PTO_ACCRUAL',pap.accrual_category)
835 , pap.accrual_units_of_measure
836 , ppa.payroll_id
837 , pap.business_group_id
838 , pap.accrual_plan_id
839 FROM pay_accrual_plans pap,
840 pay_element_types_f pet,
841 pay_element_links_f pel,
842 pay_element_entries_f pee,
843 pay_assignment_actions paa,
844 pay_payroll_actions ppa
845 WHERE pet.element_type_id = pap.accrual_plan_element_type_id
846 AND pel.element_type_id = pet.element_type_id
847 AND pee.element_link_id = pel.element_link_id
848 AND paa.assignment_id = pee.assignment_id
849 AND ppa.payroll_action_id = paa.payroll_action_id
850 AND pap.accrual_category = 'HKAL'
851 AND ppa.action_type IN('R','Q')
852 AND ppa.action_status = 'C'
853 AND ppa.date_earned BETWEEN pet.effective_start_date
854 AND pet.effective_end_date
855 AND ppa.date_earned BETWEEN pel.effective_start_date
856 AND pel.effective_end_date
857 AND ppa.date_earned BETWEEN pee.effective_start_date
858 AND pee.effective_end_date
859 AND paa.assignment_id = p_assignment_id
860 AND paa.assignment_action_id = p_assignment_action_id;
861
862
863 l_action_info_id NUMBER;
864 l_accrual_plan_id pay_accrual_plans.accrual_plan_id%type;
865 l_accrual_plan_name pay_accrual_plans.accrual_plan_name%type;
866 l_accrual_category pay_accrual_plans.accrual_category%type;
867 l_accrual_uom pay_accrual_plans.accrual_units_of_measure%type;
868 l_payroll_id pay_payrolls_f.payroll_id%type;
869 l_procedure_name VARCHAR2(80);
870 l_business_group_id NUMBER;
871 l_effective_date DATE;
872 l_annual_leave_balance NUMBER;
873 l_ovn NUMBER;
874 l_leave_taken NUMBER;
875
876 l_start_date DATE;
877 l_end_date DATE;
878 l_net_entitlement NUMBER;
879 l_accrual_end_date DATE;
880 l_accrual NUMBER;
881
882
883 BEGIN
884
885 l_procedure_name := 'archive_employee_details'; -- Bug: 3604131
886
887 IF g_debug THEN
888 hr_utility.set_location('Start of accrual archival code',1);
889
890 hr_utility.set_location('Entering Procedure pay_sg_payslip_archive.' || l_procedure_name,10);
891
892 hr_utility.trace('Opening Cursor csr_leave_balance');
893 END IF;
894
895 OPEN csr_leave_balance(p_assignment_action_id,p_assignment_id);
896 FETCH csr_leave_balance INTO
897 l_accrual_plan_name,
898 l_accrual_category,
899 l_accrual_uom,
900 l_payroll_id,
901 l_business_group_id,
902 l_accrual_plan_id;
903
904 CLOSE csr_leave_balance;
905
906 IF g_debug THEN
907 hr_utility.trace('Closing Cursor csr_leave_balance');
908
909 -- Call to get annual leave balance
910
911 hr_utility.set_location('Archiving Annual leave Balance information',2);
912 END IF;
913
914 per_accrual_calc_functions.get_net_accrual( p_assignment_id => p_assignment_id,
915 p_plan_id => l_accrual_plan_id,
916 p_payroll_id => l_payroll_id,
917 p_business_group_id => l_business_group_id,
918 p_calculation_date => p_effective_date,
919 p_start_date => l_start_date,
920 p_end_date => l_end_date,
921 p_accrual_end_date => l_accrual_end_date,
922 p_accrual => l_accrual,
923 p_net_entitlement => l_net_entitlement);
924
925 IF g_debug THEN
926 hr_utility.set_location('Archiving Leave Taken information',2);
927 END IF;
928
929
930 IF l_accrual_plan_name IS NOT NULL THEN
931
932 pay_action_information_api.create_action_information
933 ( p_action_information_id => l_action_info_id
934 , p_action_context_id => p_assact_id
935 , p_action_context_type => 'AAP'
936 , p_object_version_number => l_ovn
937 , p_effective_date => p_effective_date
938 , p_source_id => NULL
939 , p_source_text => NULL
940 , p_action_information_category => 'APAC ACCRUALS'
941 , p_action_information1 => l_accrual_plan_name
942 , p_action_information2 => l_accrual_category
943 , p_action_information4 => fnd_number.number_to_canonical(l_net_entitlement) -- Bug: 3604131
944 , p_action_information5 => l_accrual_uom
945 );
946
947 END IF;
948
949 IF g_debug THEN
950 hr_utility.set_location('Leaving Procedure pay_hk_payslip_archive.' || l_procedure_name,10);
951 END IF;
952
953
954 EXCEPTION
955 WHEN OTHERS THEN
956 IF g_debug THEN
957 hr_utility.set_location('Error raised in archiving Accruals and Leave Taken ',5);
958 END IF;
959 RAISE;
960
961 END archive_accrual_details;
962
963
964
965
966 --------------------------------------------------------------------+
967 -- Procedure to call the internal procedures to actually
968 -- the archive the data. The procedure called are -
969 -- archive_accrual_details
970 -- archive_employee_details
971 -- pay_apac_payslip_archive.archive_user_elements
972 -- archive_stat_balances
973 -- archive_stat_elements
974 -- pay_apac_payslip_archive.archive_user_balances
975 --------------------------------------------------------------------+
976
977 PROCEDURE archive_code (p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE,
978 p_effective_date IN DATE) IS
979
980
981 -- Cursor to select all the locked prepayment and payrolls by the archive
982 -- assignment action. The records are ordered descending as we only need
983 -- latest payroll run in the prepayment.
984 -- Bug# 3580617 Modified the SQL query of the cursor get_payslip_aa.
985
986 cursor get_payslip_aa(p_master_aa_id number)
987 is
988 select paa_arch_chd.assignment_action_id chld_arc_assignment_action_id,
989 paa_pre.assignment_action_id pre_assignment_action_id,
990 paa_run.assignment_action_id run_assignment_action_id,
991 ppa_pre.effective_date pre_effective_date,
992 paa_arch_chd.assignment_id,
993 ppa_run.payroll_action_id,
994 ppa_run.effective_date run_effective_date,
995 ppa_run.date_earned run_date_earned,
996 ptp.start_date period_start_date,
997 ptp.end_date period_end_date,
998 ptp.regular_payment_date,
999 ptp.time_period_id
1000 from pay_assignment_actions paa_arch_chd,
1001 pay_assignment_actions paa_arch_mst,
1002 pay_assignment_actions paa_pre,
1003 pay_action_interlocks pai_pre,
1004 pay_assignment_actions paa_run,
1005 pay_action_interlocks pai_run,
1006 pay_payroll_actions ppa_pre,
1007 pay_payroll_actions ppa_run,
1008 per_time_periods ptp
1009 where paa_arch_mst.assignment_action_id = p_master_aa_id
1010 and paa_arch_chd.source_action_id = paa_arch_mst.assignment_action_id
1011 and paa_arch_chd.payroll_action_id = paa_arch_mst.payroll_action_id
1012 and paa_arch_chd.assignment_id = paa_arch_mst.assignment_id
1013 and pai_pre.locking_action_id = paa_arch_mst.assignment_action_id
1014 and pai_pre.locked_action_id = paa_pre.assignment_action_id
1015 and pai_run.locking_action_id = paa_arch_chd.assignment_action_id
1016 and pai_run.locked_action_id = paa_run.assignment_action_id
1017 and ppa_pre.payroll_action_id = paa_pre.payroll_action_id
1018 and ppa_pre.action_type in ('P','U')
1019 and ppa_run.payroll_action_id = paa_run.payroll_action_id
1020 and ppa_run.action_type in ('R','Q')
1021 and ptp.payroll_id = ppa_run.payroll_id
1022 and ppa_run.date_earned between ptp.start_date
1023 and ptp.end_date
1024 -- Get the highest in sequence for this payslip
1025 and paa_run.action_sequence = (select max(paa_run2.action_sequence)
1026 from pay_assignment_actions paa_run2,
1027 pay_action_interlocks pai_run2
1028 where pai_run2.locking_action_id = paa_arch_chd.assignment_action_id
1029 and pai_run2.locked_action_id = paa_run2.assignment_action_id
1030 );
1031
1032 /* Added for the bug#5671633
1033 This cursor returns actual termination date if it falls in the pay period */
1034
1035 CURSOR csr_payment_date(p_assignment_action_id NUMBER)
1036 IS
1037 SELECT pps.actual_termination_date
1038 FROM pay_payroll_actions ppa,
1039 pay_assignment_actions paa,
1040 per_time_periods ptp,
1041 per_all_assignments_f paf,
1042 per_periods_of_service pps
1043 WHERE paa.assignment_action_id = p_assignment_action_id
1044 AND ppa.payroll_action_id = paa.payroll_action_id
1045 AND ptp.payroll_id = ppa.payroll_id
1046 AND paf.assignment_id = paa.assignment_id
1047 AND pps.period_of_service_id = paf.period_of_service_id
1048 AND ppa.date_earned between ptp.start_date AND ptp.end_date
1049 AND pps.actual_termination_date between paf.effective_start_date and paf.effective_end_date
1050 AND pps.actual_termination_date between ptp.start_date AND ptp.end_date;
1051
1052 l_pre_pay_assact_id NUMBER;
1053 l_payment_date DATE :=NULL;
1054
1055 BEGIN
1056
1057 l_pre_pay_assact_id := 0; -- Bug: 3604131
1058
1059 IF g_debug THEN
1060 hr_utility.set_location('Start of archive code',20);
1061 hr_utility.trace('Opening Cursor get_payslip_aa');
1062 END IF;
1063
1064 -- Bug# 3580617 Included the following function call pay_core_payslip_utils.generate_child_actions.
1065
1066 pay_core_payslip_utils.generate_child_actions( p_assignment_action_id,
1067 p_effective_date
1068 );
1069
1070 FOR csr_rec IN get_payslip_aa(p_assignment_action_id)
1071 LOOP
1072 /* Added for the bug#5671633 */
1073 open csr_payment_date(csr_rec.run_assignment_action_id);
1074 fetch csr_payment_date into l_payment_date;
1075 if csr_payment_date%NOTFOUND then
1076 l_payment_date := csr_rec.regular_payment_date;
1077 end if;
1078 close csr_payment_date;
1079
1080 IF g_debug THEN
1081 hr_utility.set_location('csr_rec.run_assignment_action_id = ' || csr_rec.run_assignment_action_id,20);
1082 hr_utility.set_location('csr_rec.pre_assignment_action_id = ' || csr_rec.pre_assignment_action_id,30);
1083 END IF;
1084
1085
1086 -- Loop to be executed only once for a prepayment with latest payroll run details
1087 -- in the prepayment
1088
1089 IF l_pre_pay_assact_id <> csr_rec.pre_assignment_action_id THEN
1090
1091 -- Call to procedure to archive User Configurable Balnaces
1092
1093 pay_apac_payslip_archive.archive_user_balances
1094 ( p_arch_assignment_action_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action id
1095 , p_run_assignment_action_id => csr_rec.run_assignment_action_id -- payroll assignment action id
1096 , p_pre_effective_date => csr_rec.pre_effective_date -- prepayment effecive date
1097 );
1098
1099
1100 -- Call to procedure to archive Statutory Elements
1101
1102 archive_stat_elements
1103 ( p_assignment_action_id => csr_rec.pre_assignment_action_id -- prepayment assignment action id
1104 , p_assignment_id => csr_rec.assignment_id -- assignment id
1105 , p_effective_date => csr_rec.pre_effective_date -- prepayment effective date
1106 , p_assact_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action id
1107 );
1108
1109 -- Call to procedure to archive Statutory balances
1110
1111 archive_stat_balances
1112 ( p_assignment_action_id => csr_rec.pre_assignment_action_id -- prepayment assignment action id
1113 , p_assignment_id => csr_rec.assignment_id -- assignment id
1114 , p_date_earned => csr_rec.run_date_earned -- payroll date earned
1115 , p_effective_date => csr_rec.pre_effective_date -- prepayment effective date
1116 , p_assact_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action id
1117 );
1118
1119
1120
1121 -- Call to procedure to archive User Configurable Elements
1122
1123 pay_apac_payslip_archive.archive_user_elements
1124 ( p_arch_assignment_action_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action
1125 , p_pre_assignment_action_id => csr_rec.pre_assignment_action_id -- prepayment assignment action id
1126 , p_latest_run_assact_id => csr_rec.run_assignment_action_id -- payroll assignment action id
1127 , p_pre_effective_date => csr_rec.pre_effective_date -- prepayment effective date
1128 );
1129
1130
1131 -- Call to procedure to archive Employee Details
1132
1133 archive_employee_details
1134 ( p_payroll_action_id => g_archive_pact -- archive payroll action id
1135 , p_assactid => csr_rec.chld_arc_assignment_action_id -- archive action id
1136 , p_pay_assignment_action_id => csr_rec.run_assignment_action_id -- payroll run action id
1137 , p_assignment_id => csr_rec.assignment_id -- assignment_id
1138 , p_curr_pymt_ass_act_id => csr_rec.pre_assignment_action_id -- prepayment assignment_action_id
1139 , p_date_earned => csr_rec.run_date_earned -- payroll date_earned
1140 , p_latest_period_end_date => l_payment_date -- latest payment date
1141 , p_run_effective_date => csr_rec.run_effective_date -- run effective Date
1142 , p_time_period_id => csr_rec.time_period_id -- time_period_id from per_time_periods
1143 , p_pre_effective_date => csr_rec.pre_effective_date
1144 -- prepayment effective date, bug 5736815
1145 );
1146
1147 -- Call to procedure to archive accrual and absennce details
1148
1149 archive_accrual_details
1150 ( p_payroll_action_id => csr_rec.payroll_action_id -- latest payroll action id
1151 , p_time_period_id => csr_rec.time_period_id -- latest period time period id
1152 , p_assignment_id => csr_rec.assignment_id -- assignment id
1153 , p_date_earned => csr_rec.run_date_earned -- latest payroll date earned
1154 , p_effective_date => csr_rec.pre_effective_date -- prepayment effective date
1155 , p_assact_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action id
1156 , p_assignment_action_id => csr_rec.run_assignment_action_id -- payroll run action id
1157 , p_period_end_date => csr_rec.period_end_date -- latest period end date
1158 , p_period_start_date => csr_rec.period_start_date -- latest period start date
1159 );
1160
1161
1162
1163 END IF;
1164
1165 l_pre_pay_assact_id := csr_rec.pre_assignment_action_id;
1166
1167
1168 END LOOP;
1169
1170 IF g_debug THEN
1171 hr_utility.trace('Opening Cursor csr_assignment_actions');
1172
1173 hr_utility.set_location('End of archive code',37);
1174 END IF;
1175
1176 EXCEPTION
1177 WHEN OTHERS THEN
1178 IF g_debug THEN
1179 hr_utility.set_location('Error in archive code :',11);
1180 END IF;
1181 RAISE;
1182
1183 END archive_code;
1184
1185 /*
1186 * Bug 4260143 - Added the following function to return the assessed ri value
1187 */
1188
1189
1190 FUNCTION get_assessed_ri(p_run_result_id in pay_run_results.run_result_id%TYPE)
1191 RETURN VARCHAR2 IS
1192
1193 l_assessed_ri pay_run_result_values.result_value%TYPE;
1194 CURSOR csr_get_accessed_ri
1195 IS
1196 SELECT prrv.result_value
1197 FROM pay_input_values_f pivf,
1198 pay_run_result_values prrv
1199 WHERE prrv.run_result_id = p_run_result_id
1200 AND pivf.input_value_id = prrv.input_value_id
1201 AND pivf.name = 'Assessed RI';
1202 BEGIN
1203 l_assessed_ri := null;
1204
1205 OPEN csr_get_accessed_ri;
1206 FETCH csr_get_accessed_ri into l_assessed_ri;
1207 CLOSE csr_get_accessed_ri;
1208
1209 IF l_assessed_ri IS NOT NULL THEN
1210 RETURN l_assessed_ri;
1211 END IF;
1212
1213 RETURN null;
1214 END get_assessed_ri;
1215
1216
1217
1218 END pay_hk_payslip_archive;