[Home] [Help]
PACKAGE BODY: APPS.PAY_AU_PAYSLIP_ARCHIVE
Source
1 package body pay_au_payslip_archive as
2 /* $Header: pyauparc.pkb 120.11.12020000.4 2012/12/25 01:46:00 mingyhua ship $ */
3 /* +===================================================================+
4 Copyright (c) 2002 Oracle Corporation Australia Ltd
5 Melbourne, Australia
6 All Rights Reserved
7 +=====================================================================+
8 Description : This package declares functions which are used
9 by the archiver when archiving AU payslip data.
10
11 Change List
12 -----------
13
14 Name Date Version Bug Text
15 ------------- ----------- ------- ------- --------------------------
16 sclarke 18-Mar-2002 115.0 Initial Version
17 sclarke 06-May-2002 115.1 Changed parameters passed to get_personal_information
18 p_curr_eff_date => latest pay run effetive_date
19 p_curr_pymt_eff_date => latest pay run period end date
20 sclarke 07-May-2002 115.2 csr_std_elements now checks for null classification
21 archive_stat_elements now executed before archive_user_elements
22 csr_chunk_number removed
23 archiving rate into APAC_ELEMENTS
24 kaverma 26-Jun-2002 115.3 2433842 added date check to csr_prepaid_assignments cursor to pick up
25 prepayments in the date range
26 kaverma 02-Jul-2002 115.4 2438495 date format for absences displayed on payslip.
27 srrajago 01-Aug-2002 115.5 2491444 Removed the cursor csr_period_end_date from archive_employee_details procedure
28 and placed it in archive_code.Added the parameter p_period_end_date
29 to the procedure archive_employee_details.
30 In archive_employee_details, correct time_period_id and period_end_date are
31 passed to all other procedures.
32 srrajago 05-Sep-2002 115.6 2525895 The check csr_annual_leave_plan%notfound and assigning balance to zero have
33 been removed. Check for accrual_plan_name before creation of 'APAC ACCRUALS'
34 has been included.
35 Ragovind 03-Dec-2002 115.7 2689226 Added NOCOPY for the function range_code.
36 srrajago 29-May-2003 115.8 2958735 In the cursor 'csr_annual_leave_plan', included an alias name for
37 accrual_category. In the procedure 'archive_accruals', introduced a loop
38 statment for fetching the cursor 'csr_annual_leave_plan'.
39 apunekar 29-May-2003 115.9 2920725 Corrected base tables to support security model
40 vgsriniv 17-Nov-2003 115.10 3260854 Done validation for divide by zero error in
41 procedure archive_stat_elements
42 punemhta 06-Feb-2004 115.11 3245909 Added a new condition to cursor csr_prepay_assignment_actions of
43 archive_code to support Run Types.
44 punemhta 25-Feb-2004 115.12 3466097 Added a new condition for archiving Elements
45 punemhta 25-Mar-2004 115.13 3245909 called get_net_pay function multiple times to arcvhie child payment methods
46 punemhta 26-Mar-2004 115.14 3245909 ROlled back changs made in ver. 115.13
47 punemhta 26-Mar-2004 115.15 3513016 Modified for standalone patch 3513016 to archive payments process separate run type
48 punemhta 01-Apr-2004 115.16 3363519 Modified to call core packages for functionality and removed the call to get_net_pay_distribution
49 avenkatk 03-May-2004 115.17 3606558 Added new condition - NULL value check for archiving elements.
50 avenkatk 03-May-2004 115.18 3606558 Resolved GSCC Errors.
51 srrajago 05-May-2004 115.19 3604094 Converted all Number fields to Canonical format before archiving the same.
52 Action Information Category Column whose value is converted to Canonical
53 --------------------------- --------------------------------------------
54 APAC ELEMENTS action_information5, action_information9
55 APAC BALANCES action_information4
56 APAC ACCRUALS action_information4
57 APAC ABSENCES action_information6, action_information8
58 --------------------------- --------------------------------------------
59 avenkatk 18-Oct-2004 115.20 3891564 Modified call to pay_au_soe.balance_totals for earnings reporting enhancement.
60 srrajago 04-Nov-2004 115.21 3991308 Variable l_balance declaration modified from number(15,3) to number.
61 ksingla 20-Dec-2004 115.22 3935483 Modified call to balance_totals
62 ksingla 29-Dec-2004 115.23 3935483 Modified call to pay_au_soe_pkg.balance_totals to include one more parameter.
63 avenkatk 07-Dec-2004 115.24 4018490 Rounded the Hours component archived for Elements to 2 decimal places.
64 avenkatk 19-Apr-2005 115.25 4169557 Introduced call to populate Defined Balance ID's in intialization_code
65 abhargav 09-Jul-2005 115.26 4363057 Removed calls to pay_au_soe.balance_totals and replace it with pay_au_soe.final_balance_totals
66 ksingla 09-Jan-2006 120.2 4753806 Modified cursor csr_std_elements to sum up hours and payment .
67 ksingla 21-Feb-2006 120.3 5036580 Modified procedure archive_absences for performance.
68 hnainani 08-Nov-2006 120.4 5599302 Added Trunc to l_rate
69 priupadh 13-Feb-2006 120.5 5504354 Added cursors c_grade_step,c_pay_advice_date,c_get_bus_id in archive_employee_details
70 Added code to Archive additional Employee Details (Workchoice)
71 sclarke 23-Feb-2007 120.3.12000000.4
72 5713447 Added new procedure archive_offset_payment_method(),it archives payment details for offset payrolls.
73 hnainani 13-MAR-2007 120.3.1200000.5 5914696 Modified l_rate to get Rate from the view pay_au_asg_element_payments_v instead
74 priupadh 02-JUL-2007 120.3.1200000.6 6032985 Modified csr_pay_advice_date , default direct entry (ptp.default_dd_date) needs to be displayed
75 as Payment Date in Payslip
76 vamittal 29-APR-2008 120.3.12000000.7 69623336 Modified archive_offset_payment_method() to be based on Prepayment Effective Date
77 jmarupil 08-JUN-2011 120.3.12000000.8 12625481 Added cursors csr_leave_taken6, csr_leave_taken7 for archive_absences procedure for displaying
78 PPL in Leave Taken section
79 scireddy 26-JUL-2011 120.3.12000000.9 12775701 Added a join condition on 'action_type' for all the cursors in archive_absences procedure
80 to improve the performance of Pay Slip Archive process.
81 mingyhua 14-SEP-2012 120.3.12000000.10 14486386 Moved 'pay_emp_action_arch.arch_pay_action_level_data' procedure call
82 from range_code to initialization_code.
83 mingyhua 25-NOV-2012 120.3.12000000.11 15912284 Reversed the last change because under certain occasion
84 it causes duplicate record in AU payslip page when using
85 pay_employee_action_info_v
86 mingyhua 03-DEC-2012 120.3.12000000.12 15914996 Added a validation check in archive_code to check whether pay level
87 info has been archived. If not, pay level info data is archived for quickpay.
88 ====================================================================
89 */
90
91 g_arc_payroll_action_id pay_payroll_actions.payroll_action_id%type; -- Global to store last archive payroll action id
92
93 g_package constant varchar2(60) := 'pay_au_payslip_archive.'; -- Global to store package name for tracing.
94
95 --------------------------------------------------------------------
96 --
97 -- This procedure returns a sql string to select a range
98 -- of assignments eligible for archival.
99 --
100 --------------------------------------------------------------------
101
102 procedure range_code
103 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type
104 ,p_sql out NOCOPY varchar2
105 ) is
106
107 l_procedure varchar2(200) ;
108
109 begin
110 l_procedure := g_package||'range_code';
111 hr_utility.set_location('Entering '||l_procedure,1);
112
113 -- Archive the payroll action level data and EIT defintions.
114 pay_apac_payslip_archive.range_code( p_payroll_action_id => p_payroll_action_id);
115 /*Bug#3363519 */
116 pay_core_payslip_utils.range_cursor(p_payroll_action_id,
117 p_sql);
118
119 hr_utility.set_location('Leaving '||l_procedure,1000);
120
121 end range_code;
122
123 --------------------------------------------------------------------
124 --
125 -- This procedure is used to set global contexts
126 -- The globals used are PL/SQL tables i.e.(g_user_balance_table and g_element_table)
127 -- It calls the procedure pay_apac_archive.initialization_code that
128 -- actually sets the global variables and populates the global tables.
129 --
130 --------------------------------------------------------------------
131
132 procedure initialization_code
133 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type)
134 is
135 l_procedure varchar2(200) ;
136
137 begin
138 l_procedure := g_package||'initialization_code';
139 hr_utility.set_location('Entering '||l_procedure,1);
140
141 g_arc_payroll_action_id := p_payroll_action_id;
142 hr_utility.set_location('g_arc_payroll_action_id......='||g_arc_payroll_action_id,10);
143
144 -- initialization_code to to set the global tables for EIT
145 -- that will be used by each thread in multi-threading.
146 pay_apac_payslip_archive.initialization_code(p_payroll_action_id => p_payroll_action_id);
147
148 /* Bug 4169557 - Introduced calls to populate defined balance ID's */
149 pay_au_soe_pkg.populate_defined_balances;
150
151 hr_utility.set_location('Leaving '||l_procedure,1000);
152
153 exception
154 when others then
155 hr_utility.set_location('Error in '||l_procedure,999999);
156 raise;
157 end initialization_code;
158
159 --------------------------------------------------------------------
160 --
161 -- This procedure further restricts the assignment_id's
162 -- returned by range_code.
163 -- It creates the archive assignment actions and locks the prepayment
164 -- actions and the latest payroll action.
165 --
166 --------------------------------------------------------------------
167
168 procedure assignment_action_code
169 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type
170 ,p_start_person in per_all_people_f.person_id%type
171 ,p_end_person in per_all_people_f.person_id%type
172 ,p_chunk in number
173 ) is
174 --
175 l_procedure varchar2(200) ;
176
177 begin
178 l_procedure := g_package||'assignment_action_code';
179 hr_utility.set_location('Entering ' || l_procedure,1);
180 /*Bug#3363519 */
181 pay_core_payslip_utils.action_creation (
182 p_payroll_action_id,
183 p_start_person,
184 p_end_person,
185 p_chunk,
186 'AU_PAYSLIP_ARCHIVE',
187 'AU');
188
189 exception
190 when others then
191 hr_utility.set_location('Error in '||l_procedure,999999);
192 raise;
193 end assignment_action_code;
194
195 --------------------------------------------------------------------
196 --
197 -- This procedure archives the elements and corresponding payments
198 -- that are required to be shown on payslip.
199 --
200 --------------------------------------------------------------------
201
202 procedure archive_stat_elements
203 (p_pre_assignment_action_id in pay_assignment_actions.assignment_action_id%type
204 ,p_pre_effective_date in pay_payroll_actions.effective_date%type
205 ,p_assignment_id in pay_assignment_actions.assignment_id%type
206 ,p_arc_assignment_action_id in pay_assignment_actions.assignment_action_id%type
207 ) is
208
209 cursor csr_std_elements
210 (p_assignment_action_id number
211 ) is
212 select element_reporting_name /* Modified for bug 4753806 summed up hours and payment using group by */
213 , classification_name
214 , SUM(payment) payment
215 , SUM( hours ) hours
216 , rate /* 5914696 */
217 from pay_au_asg_element_payments_v
218 where assignment_action_id = p_assignment_action_id
219 and classification_name is not null
220 group by element_reporting_name,rate,classification_name;
221
222 l_action_info_id number;
223 l_ovn number;
224 l_procedure varchar2(200);
225
226 /* 5914696 */
227 -- l_rate number;
228
229 begin
230 l_procedure := g_package||'archive_stat_elements';
231 hr_utility.set_location('Entering ' || l_procedure,1);
232 --
233 -- Loop through the processed elements and archive the data
234 -- into the flexfield structure APAC_ELEMENTS.
235 --
236 FOR csr_rec IN csr_std_elements(p_pre_assignment_action_id)
237 LOOP
238 hr_utility.set_location('Archiving APAC Element Details',20);
239 /* Bug:3260854 Added the following check to avoid
240 divide by zero error. */
241
242 /* Bug 5914696 */
243
244 /* If csr_rec.hours = 0 Then
245 l_rate := 0;
246 Else
247 l_rate := trunc(csr_rec.payment / csr_rec.hours,2);
248 End if; */
249
250
251 /* Bug 3891564 - Modified the check for classification_name for Earnings Reporting enhancment.
252 */
253 /* Bug 4018490 - Rounded the Hours value archived for elements to 2 decimal places */
254 If ( csr_rec.classification_name in ('Taxable Earnings','Pre Tax Deductions','Tax Deductions','Post Tax Deductions','Direct Payments','Non Taxable Earnings','Employer Superannuation Contributions')
255 AND csr_rec.payment IS NULL) THEN
256 NULL; -- Bug:3466097,Bug:3606558 - Elements will not be archived in this case to make it similar to SOE
257 ELSE
258 pay_action_information_api.create_action_information
259 ( p_action_information_id => l_action_info_id
260 , p_action_context_id => p_arc_assignment_action_id
261 , p_action_context_type => 'AAP'
262 , p_object_version_number => l_ovn
263 , p_effective_date => p_pre_effective_date
264 , p_source_id => NULL
265 , p_source_text => NULL
266 , p_action_information_category => 'APAC ELEMENTS'
267 , p_action_information1 => csr_rec.element_reporting_name
268 , p_action_information2 => NULL
269 , p_action_information3 => NULL
270 , p_action_information4 => csr_rec.classification_name
271 , p_action_information5 => fnd_number.number_to_canonical(csr_rec.payment) -- Bug: 3604094
272 , p_action_information7 => round(csr_rec.hours,2) -- Bug: 4018490
273 , p_action_information9 => fnd_number.number_to_canonical(csr_rec.rate) -- Bug: 3604094
274 );
275 END IF;
276 END LOOP;
277 hr_utility.set_location('Leaving ' || l_procedure,1000);
278 exception
279 when others
280 then
281 hr_utility.set_location('Error in '||l_procedure,999999);
282 raise;
283 end archive_stat_elements;
284
285 --------------------------------------------------------------------
286 --
287 -- Procedure contains code which archives balance data
288 -- into the flexfield structure APAC_BALANCES.
289 --
290 --------------------------------------------------------------------
291 /* Bug 3891564 Added new parameter for archiving Current Amount value.
292 Number formatting done under enhancement 3604094 handled for the new column also.
293 */
294
295 procedure archive_balances
296 (p_arc_assignment_action_id in pay_assignment_actions.assignment_action_id%type
297 ,p_pre_effective_date in pay_payroll_actions.effective_date%type
298 ,p_narrative in varchar2
299 ,p_ytd in number
300 ,p_tp in number --Bug 3891564
301 ) is
302 l_action_info_id number;
303 l_ovn number;
304 l_procedure varchar2(200) ;
305 begin
306 l_procedure := g_package||'archive_balances';
307 hr_utility.set_location('Entering ' || l_procedure,1);
308
309 pay_action_information_api.create_action_information
310 (p_action_information_id => l_action_info_id
311 ,p_action_context_id => p_arc_assignment_action_id
312 ,p_action_context_type => 'AAP'
313 ,p_object_version_number => l_ovn
314 ,p_effective_date => p_pre_effective_date
315 ,p_source_id => NULL
316 ,p_source_text => NULL
317 ,p_action_information_category => 'APAC BALANCES'
318 ,p_action_information1 => p_narrative
319 ,p_action_information2 => NULL
320 ,p_action_information3 => NULL
321 ,p_action_information4 => fnd_number.number_to_canonical(p_ytd) -- Bug: 3604094
322 ,p_action_information5 => fnd_number.number_to_canonical(p_tp) -- Bug 3891564, Bug: 3604094
323 );
324
325 hr_utility.set_location('Leaving ' || l_procedure,1000);
326 exception
327 when others then
328 hr_utility.set_location('Error in '||l_procedure,999999);
329 raise;
330 end archive_balances;
331
332 --------------------------------------------------------------------
333 --
334 -- This procedure calculates and archives the statutory balances
335 -- that are required for display on payslip
336 --
337 --------------------------------------------------------------------
338
339 procedure archive_stat_balances
340 (p_pre_assignment_action_id in pay_assignment_actions.assignment_action_id%type /*4363057*/
341 ,p_pre_effective_date in pay_payroll_actions.effective_date%type
342 ,p_assignment_id in pay_assignment_actions.assignment_id%type
343 ,p_arc_assignment_action_id in pay_assignment_actions.assignment_action_id%type
344 ,p_calculation_date in pay_payroll_actions.effective_date%type
345 ) is
346 l_gross_this_pay number;
347 l_other_deductions_this_pay number;
348 l_tax_deductions_this_pay number;
349 l_gross_ytd number;
350 l_other_deductions_ytd number;
351 l_tax_deductions_ytd number;
352 l_non_tax_allow_this_pay number;
353 l_non_tax_allow_ytd number;
354 l_pre_tax_deductions_this_pay number;
355 l_pre_tax_deductions_ytd number;
356 l_net_payment_this_pay number;
357 l_net_payment_ytd number;
358 l_super_run number;
359 l_super_ytd number;
360 l_narrative varchar2(1000);
361 l_procedure varchar2(200) ;
362 /* Bug 3891564 - Added four new parameters to the procedure call to return taxable income
363 and direct Payments */
364 l_tax_income_this_pay number;
365 l_tax_income_ytd number;
366 l_direct_pay_this_pay number;
367 l_direct_pay_ytd number;
368 l_get_le_level_bal varchar2(1); --3935483
369 l_fetch_only_ytd_value varchar2(1); --3935483
370
371 begin
372 /* bug 3935483 2 new parameters p_get_le_level_bal passed as Y and p_fetch_only_ytd_value passed as N to balance_totals
373 to fetch the LE level balances both , run and ytd balances, from the modified pay_au soe package */
374
375
376 l_get_le_level_bal :='Y'; --3935483
377 l_fetch_only_ytd_value :='N'; --3935483
378 l_procedure := g_package||'archive_stat_balances';
379 hr_utility.set_location('Entering ' || l_procedure,1);
380 --
381 -- Get the balance values
382 --
383 /* Bug 3891564 - Added four new parameters to the procedure call to return taxable income
384 and direct Payments */
385 /*Bug 4363057 - Removed call to balance_totals and added call to final_balance_totals*/
386 pay_au_soe_pkg.final_balance_totals
387 ( p_assignment_id => p_assignment_id
388 ,p_assignment_action_id => p_pre_assignment_action_id
389 ,p_effective_date => p_calculation_date
390 ,p_gross_this_pay => l_gross_this_pay
391 ,p_other_deductions_this_pay => l_other_deductions_this_pay
392 ,p_tax_deductions_this_pay => l_tax_deductions_this_pay
393 ,p_gross_ytd => l_gross_ytd
394 ,p_other_deductions_ytd => l_other_deductions_ytd
395 ,p_tax_deductions_ytd => l_tax_deductions_ytd
396 ,p_non_tax_allow_this_pay => l_non_tax_allow_this_pay
397 ,p_non_tax_allow_ytd => l_non_tax_allow_ytd
398 ,p_pre_tax_deductions_this_pay => l_pre_tax_deductions_this_pay
399 ,p_pre_tax_deductions_ytd => l_pre_tax_deductions_ytd
400 ,p_super_this_pay => l_super_run
401 ,p_super_ytd => l_super_ytd
402 ,p_taxable_income_this_pay => l_tax_income_this_pay
403 ,p_taxable_income_ytd => l_tax_income_ytd
404 ,p_direct_payments_this_pay => l_direct_pay_this_pay
405 ,p_direct_payments_ytd => l_direct_pay_ytd
406 ,p_get_le_level_bal =>l_get_le_level_bal
407 ,p_fetch_only_ytd_value =>l_fetch_only_ytd_value
408 );
409
410 -- Calculate net_payment figure
411 l_net_payment_ytd := l_gross_ytd - l_pre_tax_deductions_ytd - l_other_deductions_ytd - l_tax_deductions_ytd + l_direct_pay_ytd;
412 l_net_payment_this_pay := l_gross_this_pay - l_pre_tax_deductions_this_pay - l_other_deductions_this_pay - l_tax_deductions_this_pay + l_direct_pay_this_pay;
413
414
415 l_narrative := 'Gross Earnings';
416 hr_utility.set_location(l_narrative||' = '||l_gross_ytd,30);
417
418 -- Archive Gross Earnings
419 archive_balances
420 (p_arc_assignment_action_id => p_arc_assignment_action_id
421 ,p_pre_effective_date => p_pre_effective_date
422 ,p_narrative => l_narrative
423 ,p_ytd => l_gross_ytd
424 ,p_tp => l_gross_this_pay
425 );
426
427 l_narrative := 'Non Taxable Earnings';
428 hr_utility.set_location(l_narrative||' = '||l_non_tax_allow_ytd,40);
429
430 -- Archive Non Taxable Earnings
431 archive_balances
432 (p_arc_assignment_action_id => p_arc_assignment_action_id
433 ,p_pre_effective_date => p_pre_effective_date
434 ,p_narrative => l_narrative
435 ,p_ytd => l_non_tax_allow_ytd
436 ,p_tp => l_non_tax_allow_this_pay
437 );
438
439 l_narrative := 'Pre Tax Deductions';
440 hr_utility.set_location(l_narrative||' = '||l_pre_tax_deductions_ytd,50);
441
442 -- Archive Pre Tax Deductions
443 archive_balances
444 (p_arc_assignment_action_id => p_arc_assignment_action_id
445 ,p_pre_effective_date => p_pre_effective_date
446 ,p_narrative => l_narrative
447 ,p_ytd => l_pre_tax_deductions_ytd
448 ,p_tp => l_pre_tax_deductions_this_pay
449 );
450
451 l_narrative := 'Taxable Gross';
452 hr_utility.set_location(l_narrative||' = '||l_tax_income_ytd,60);
453
454 -- Archive Taxable Earnings
455 archive_balances
456 (p_arc_assignment_action_id => p_arc_assignment_action_id
457 ,p_pre_effective_date => p_pre_effective_date
458 ,p_narrative => l_narrative
459 ,p_ytd => l_tax_income_ytd
460 ,p_tp => l_tax_income_this_pay
461 );
462
463 l_narrative := 'Tax Deductions';
464 hr_utility.set_location(l_narrative||' = '||l_tax_deductions_ytd,70);
465
466 -- Archive Tax Deductions
467 archive_balances
468 (p_arc_assignment_action_id => p_arc_assignment_action_id
469 ,p_pre_effective_date => p_pre_effective_date
470 ,p_narrative => l_narrative
471 ,p_ytd => l_tax_deductions_ytd
472 ,p_tp => l_tax_deductions_this_pay
473 );
474
475 l_narrative := 'Post Tax Deductions';
476 hr_utility.set_location(l_narrative||' = '||l_other_deductions_ytd,80);
477
478
479 -- Archive Post Tax Deductions
480 archive_balances
481 (p_arc_assignment_action_id => p_arc_assignment_action_id
482 ,p_pre_effective_date => p_pre_effective_date
483 ,p_narrative => l_narrative
484 ,p_ytd => l_other_deductions_ytd
485 ,p_tp => l_other_deductions_this_pay
486 );
487
488 l_narrative := 'Direct Payments';
489 hr_utility.set_location(l_narrative||' = '||l_direct_pay_ytd,90);
490
491
492 -- Archive Direct Payments
493 archive_balances
494 (p_arc_assignment_action_id => p_arc_assignment_action_id
495 ,p_pre_effective_date => p_pre_effective_date
496 ,p_narrative => l_narrative
497 ,p_ytd => l_direct_pay_ytd
498 ,p_tp => l_direct_pay_this_pay
499 );
500
501 l_narrative := 'Net Payment';
502 hr_utility.set_location(l_narrative||' = '||l_net_payment_ytd,100);
503
504 -- Archive Net Payment
505 archive_balances
506 (p_arc_assignment_action_id => p_arc_assignment_action_id
507 ,p_pre_effective_date => p_pre_effective_date
508 ,p_narrative => l_narrative
509 ,p_ytd => l_net_payment_ytd
510 ,p_tp => l_net_payment_this_pay
511 );
512
513 hr_utility.set_location('Leaving ' || l_procedure,1000);
514
515 exception
516 when others then
517 hr_utility.set_location('Error in '||l_procedure,999999);
518 raise;
519 end archive_stat_balances;
520
521 ---------------------------------------------------------------------------
522 --
523 -- This procedure archives the required information for Annual Leave
524 -- Accrual Plans into the Action Information DF structure 'APAC ACCRUALS'
525 --
526 -- Parameters:
527 -- p_assignment_id is required to calculate the plan balance
528 -- p_run_assignment_action_id is required to get accrual plan information
529 -- p_archive_assignment_action_id is used for archiving the data
530 -- p_archive_effective_date is used for archiving the data
531 -- p_calculation_date is used as the date to calculate the balance up to
532 --
533 ---------------------------------------------------------------------------
534
535 procedure archive_accruals
536 (p_assignment_id in pay_assignment_actions.assignment_id%type
537 ,p_pre_effective_date in pay_payroll_actions.effective_date%type
538 ,p_run_assignment_action_id in pay_assignment_actions.assignment_action_id%type
539 ,p_arc_assignment_action_id in pay_assignment_actions.assignment_action_id%type
540 ,p_calculation_date in date
541 ) is
542 --
543 l_assignment_id per_all_assignments_f.assignment_id%type;
544 --
545 l_balance number; -- Bug: 3991308
546 l_action_info_id pay_action_information.action_information_id%type;
547 l_ovn pay_action_information.object_version_number%type;
548 --
549 -- Cursor to retrieve the Annual Leave accrual plan
550 -- information.
551 --
552 cursor csr_annual_leave_plan
553 (p_assignment_action_id pay_assignment_actions.assignment_action_id%type)
554 is
555 select ap.accrual_plan_id
556 , ap.accrual_plan_name
557 , hr_general_utilities.get_lookup_meaning('US_PTO_ACCRUAL',ap.accrual_category) accrual_category
558 , ap.accrual_units_of_measure
559 , pa.payroll_id payroll_id
560 , ap.business_group_id business_group_id
561 from pay_accrual_plans ap
562 , pay_element_types_f et
563 , pay_element_links_f el
564 , pay_element_entries_f ee
565 , pay_assignment_actions aa
566 , pay_payroll_actions pa
567 where et.element_type_id = ap.accrual_plan_element_type_id -- select the accrual plan elements
568 and el.element_type_id = et.element_type_id -- select accrual plan element entries
569 and aa.assignment_id = ee.assignment_id -- select element entries for this assignment
570 and ee.element_link_id = el.element_link_id -- join element to element entries via element links
571 and pa.payroll_action_id = aa.payroll_action_id -- need the payroll action to check the action_type
572 and pa.action_type in ('R','Q') -- select only payroll/quikpay runs
573 and pa.action_status = 'C' -- select only successfully completed runs
574 and pa.date_earned between et.effective_start_date and et.effective_end_date
575 and pa.date_earned between el.effective_start_date and el.effective_end_date
576 and pa.date_earned between ee.effective_start_date and ee.effective_end_date
577 and ap.accrual_category = 'AUAL' -- select only annual leave accrual plans
578 and aa.assignment_action_id = p_assignment_action_id
579 ;
580 l_procedure varchar2(200) ;
581 --
582 begin
583 l_procedure := g_package||'archive_accruals';
584 hr_utility.set_location('Entering ' || l_procedure,1);
585 --
586 -- 1. Get the accrual_plan_id for this assignment action
587 --
588
589 /* Bug No : 2958735 - Introduced a loop statement for the cursor csr_annual_leave_plan */
590
591 FOR csr_rec IN csr_annual_leave_plan(p_run_assignment_action_id)
592 LOOP
593 --
594 -- Get the balance of leave for this accrual plan at the period_end_date
595 --
596 --
597 hr_utility.set_location('p_assignment_id.............= '||to_char(p_assignment_id),20);
598 hr_utility.set_location('l_payroll_id................= '||to_char(csr_rec.payroll_id),20);
599 hr_utility.set_location('l_business_group_id.........= '||to_char(csr_rec.business_group_id),20);
600 hr_utility.set_location('l_accrual_plan_id...........= '||to_char(csr_rec.accrual_plan_id),20);
601 hr_utility.set_location('p_calculation_date..........= '||to_char(p_calculation_date,'DD-MON-YYYY'),20);
602
603 l_balance := hr_au_holidays.get_net_accrual
604 (p_assignment_id => p_assignment_id
605 ,p_payroll_id => csr_rec.payroll_id
606 ,p_business_group_id => csr_rec.business_group_id
607 ,p_plan_id => csr_rec.accrual_plan_id
608 ,p_calculation_date => p_calculation_date
609 );
610
611 hr_utility.set_location('annual leave balance..........= '||to_char(l_balance),30);
612
613 IF csr_rec.accrual_plan_name IS NOT NULL THEN /* Bug No : 2525895 */
614
615 pay_action_information_api.create_action_information
616 ( p_action_information_id => l_action_info_id
617 , p_action_context_id => p_arc_assignment_action_id
618 , p_action_context_type => 'AAP'
619 , p_object_version_number => l_ovn
620 , p_effective_date => p_pre_effective_date
621 , p_source_id => NULL
622 , p_source_text => NULL
623 , p_action_information_category => 'APAC ACCRUALS'
624 , p_action_information1 => csr_rec.accrual_plan_name
625 , p_action_information2 => csr_rec.accrual_category
626 , p_action_information3 => NULL
627 , p_action_information4 => fnd_number.number_to_canonical(l_balance) -- Bug: 3604094
628 , p_action_information5 => csr_rec.accrual_units_of_measure
629 );
630
631 END IF; /* Bug No : 2525895 */
632 END LOOP;
633
634 hr_utility.set_location('Leaving '|| l_procedure,1000);
635
636 exception
637 when others then
638 hr_utility.set_location('Error in '||l_procedure,999999);
639 raise;
640 end archive_accruals;
641
642 ------------------------------------------------------------------------------
643 --
644 -- This procedure archives the information for Leave Taken into the Action
645 -- Information DF structure 'APAC ABSENCES'
646 --
647 -- Parameters:
648 -- p_assignment_id is required to retrieve leave taken information
649 -- p_time_period_id is required to retrieve leave taken information
650 -- p_arc_effective_date is used for archiving the data
651 -- p_arc_assignment_action_id is used for archiving the data
652 -- p_run_assignment_action_id is used for retrieving leave taken information
653
654 ------------------------------------------------------------------------------
655 /* Bug 5036580- Modified procedure to fetch and insert values for absence based on split views .
656 l_exists and tab_row declared to ensure duplicate rows fetched from different views are not inserted again
657 into pay_action_information */
658
659 procedure archive_absences
660 (p_assignment_id in pay_assignment_actions.assignment_id%type
661 ,p_pre_effective_date in pay_payroll_actions.effective_date%type
662 ,p_time_period_id in per_time_periods.time_period_id%type
663 ,p_arc_assignment_action_id in pay_assignment_actions.assignment_action_id%type
664 ,p_run_assignment_action_id in pay_assignment_actions.assignment_action_id%type
665 ) is
666
667
668 cursor csr_leave_taken1
669 (p_time_period_id per_time_periods.time_period_id%type
670 ,p_assignment_id pay_assignment_actions.assignment_id%type
671 ) is
672 select row_id
673 , element_reporting_name
674 , start_date
675 , end_date
676 , absence_hours
677 , payment
678 from pay_au_asg_leave_taken_v1
679 where time_period_id = p_time_period_id
680 and assignment_id = p_assignment_id
681 and action_type IN ('R','Q','V');
682
683
684 cursor csr_leave_taken2
685 (p_time_period_id per_time_periods.time_period_id%type
686 ,p_assignment_id pay_assignment_actions.assignment_id%type
687 ) is
688 select row_id,
689 element_reporting_name
690 , start_date
691 , end_date
692 , absence_hours
693 , payment
694 from pay_au_asg_leave_taken_v2
695 where time_period_id = p_time_period_id
696 and assignment_id = p_assignment_id
697 and action_type IN ('R','Q','V');
698
699
700 cursor csr_leave_taken3
701 (p_time_period_id per_time_periods.time_period_id%type
702 ,p_assignment_id pay_assignment_actions.assignment_id%type
703 ) is
704 select row_id,element_reporting_name
705 , start_date
706 , end_date
707 , absence_hours
708 , payment
709 from pay_au_asg_leave_taken_v3
710 where time_period_id = p_time_period_id
711 and assignment_id = p_assignment_id
712 and action_type IN ('R','Q','V');
713
714
715 cursor csr_leave_taken4
716 (p_time_period_id per_time_periods.time_period_id%type
717 ,p_assignment_id pay_assignment_actions.assignment_id%type
718 ) is
719 select row_id,element_reporting_name
720 , start_date
721 , end_date
722 , absence_hours
723 , payment
724 from pay_au_asg_leave_taken_v4
725 where time_period_id = p_time_period_id
726 and assignment_id = p_assignment_id;
727
728
729 cursor csr_leave_taken5
730 (p_time_period_id per_time_periods.time_period_id%type
731 ,p_assignment_id pay_assignment_actions.assignment_id%type
732 ) is
733 select row_id,element_reporting_name
734 , start_date
735 , end_date
736 , absence_hours
737 , payment
738 from pay_au_asg_leave_taken_v5
739 where time_period_id = p_time_period_id
740 and assignment_id = p_assignment_id
741 and action_type IN ('R','Q','V');
742
743 cursor csr_leave_taken6
744 (p_time_period_id per_time_periods.time_period_id%type
745 ,p_assignment_id pay_assignment_actions.assignment_id%type
746 ) is
747 select row_id,element_reporting_name
748 , start_date
749 , end_date
750 , absence_hours
751 , payment
752 from pay_au_asg_leave_taken_v6
753 where time_period_id = p_time_period_id
754 and assignment_id = p_assignment_id
755 and action_type IN ('R','Q','V');
756
757 cursor csr_leave_taken7
758 (p_time_period_id per_time_periods.time_period_id%type
759 ,p_assignment_id pay_assignment_actions.assignment_id%type
760 ) is
761 select row_id,element_reporting_name
762 , start_date
763 , end_date
764 , absence_hours
765 , payment
766 from pay_au_asg_leave_taken_v7
767 where time_period_id = p_time_period_id
768 and assignment_id = p_assignment_id
769 and action_type IN ('R','Q','V');
770
771 l_exists varchar2(10) ;
772
773 l_action_info_id pay_action_information.action_information_id%type;
774 l_ovn pay_action_information.object_version_number%type;
775
776 l_procedure varchar2(200);
777 l_start_date VARCHAR2(20);
778 l_end_date VARCHAR2(20);
779
780 type tab_row is table of pay_au_asg_leave_taken_v.row_id%type index by binary_integer;
781
782 tab_row_id tab_row;
783
784 i number ;
785
786
787
788 begin
789
790 l_exists := 'N' ;
791 i := 1 ;
792 l_procedure := g_package||'archive_leave_details';
793 hr_utility.set_location('Entering '||l_procedure,1);
794 --
795 -- Get all leave taken for this assignment for the given time period
796 --
797 FOR csr_rec IN csr_leave_taken1(p_time_period_id, p_assignment_id)
798 LOOP
799
800 if i = 1 then
801 l_exists := 'N';
802
803 else
804
805 for j in tab_row_id.first..tab_row_id.last
806 loop
807
808 if tab_row_id(j) = csr_rec.row_id then
809 l_exists := 'Y';
810 exit;
811 else
812 l_exists := 'N';
813 end if;
814
815 end loop;
816
817 end if;
818
819 if l_exists = 'N' then
820
821 tab_row_id(i) := csr_rec.row_id ;
822 i := i + 1 ;
823
824 hr_utility.trace('Entering csr_leave_taken1');
825 hr_utility.set_location('csr_rec.element_reporting_name.= '||csr_rec.element_reporting_name,50);
826 hr_utility.set_location('csr_rec.start_date.............= '||to_char(csr_rec.start_date,'DD-MON-YYYY'),50);
827 hr_utility.set_location('csr_rec.end_date...............= '||to_char(csr_rec.end_date,'DD-MON-YYYY'),50);
828 hr_utility.set_location('csr_rec.absence_hours..........= '||csr_rec.absence_hours,50);
829 hr_utility.set_location('csr_rec.payment................= '||csr_rec.payment,50);
830
831 l_start_date := fnd_date.date_to_canonical(csr_rec.start_date); /*Bug 2438495*/
832 l_end_date := fnd_date.date_to_canonical(csr_rec.end_date);
833
834 pay_action_information_api.create_action_information
835 (p_action_information_id => l_action_info_id
836 , p_action_context_id => p_arc_assignment_action_id
837 , p_action_context_type => 'AAP'
838 , p_object_version_number => l_ovn
839 , p_effective_date => p_pre_effective_date
840 , p_source_id => NULL
841 , p_source_text => NULL
842 , p_action_information_category => 'APAC ABSENCES'
843 , p_action_information1 => NULL
844 , p_action_information2 => csr_rec.element_reporting_name
845 , p_action_information3 => NULL
846 , p_action_information4 => l_start_date
847 , p_action_information5 => l_end_date
848 , p_action_information6 => fnd_number.number_to_canonical(csr_rec.absence_hours) -- Bug: 3604094
849 , p_action_information7 => NULL
850 , p_action_information8 => fnd_number.number_to_canonical(csr_rec.payment) -- Bug: 3604094
851 );
852
853 -- l_exists := 'N';
854
855 end if;
856
857 END LOOP;
858
859
860 FOR csr_rec IN csr_leave_taken2(p_time_period_id, p_assignment_id)
861 LOOP
862
863
864 if i = 1 then
865 l_exists := 'N';
866
867 else
868
869 for j in tab_row_id.first..tab_row_id.last
870 loop
871
872 if tab_row_id(j) = csr_rec.row_id then
873 l_exists := 'Y';
874 exit;
875 else
876 l_exists := 'N';
877 end if;
878
879 end loop;
880
881 end if;
882
883
884 if l_exists = 'N' then
885
886 tab_row_id(i) := csr_rec.row_id ;
887 i := i + 1 ;
888
889 hr_utility.trace('Entering csr_leave_taken2');
890 hr_utility.set_location('csr_rec.element_reporting_name.= '||csr_rec.element_reporting_name,50);
891 hr_utility.set_location('csr_rec.start_date.............= '||to_char(csr_rec.start_date,'DD-MON-YYYY'),50);
892 hr_utility.set_location('csr_rec.end_date...............= '||to_char(csr_rec.end_date,'DD-MON-YYYY'),50);
893 hr_utility.set_location('csr_rec.absence_hours..........= '||csr_rec.absence_hours,50);
894 hr_utility.set_location('csr_rec.payment................= '||csr_rec.payment,50);
895
896 l_start_date := fnd_date.date_to_canonical(csr_rec.start_date); /*Bug 2438495*/
897 l_end_date := fnd_date.date_to_canonical(csr_rec.end_date);
898
899 pay_action_information_api.create_action_information
900 (p_action_information_id => l_action_info_id
901 , p_action_context_id => p_arc_assignment_action_id
902 , p_action_context_type => 'AAP'
903 , p_object_version_number => l_ovn
904 , p_effective_date => p_pre_effective_date
905 , p_source_id => NULL
906 , p_source_text => NULL
907 , p_action_information_category => 'APAC ABSENCES'
908 , p_action_information1 => NULL
909 , p_action_information2 => csr_rec.element_reporting_name
910 , p_action_information3 => NULL
911 , p_action_information4 => l_start_date
912 , p_action_information5 => l_end_date
913 , p_action_information6 => fnd_number.number_to_canonical(csr_rec.absence_hours) -- Bug: 3604094
914 , p_action_information7 => NULL
915 , p_action_information8 => fnd_number.number_to_canonical(csr_rec.payment) -- Bug: 3604094
916 );
917
918
919 end if;
920
921 END LOOP;
922
923
924
925 FOR csr_rec IN csr_leave_taken3(p_time_period_id, p_assignment_id)
926 LOOP
927
928
929 if i = 1 then
930 l_exists := 'N';
931
932 else
933
934 for j in tab_row_id.first..tab_row_id.last
935 loop
936
937 if tab_row_id(j) = csr_rec.row_id then
938 l_exists := 'Y';
939 exit;
940 else
941 l_exists := 'N';
942 end if;
943
944 end loop;
945
946 end if;
947
948
949 if l_exists = 'N' then
950
951 tab_row_id(i) := csr_rec.row_id ;
952 i := i + 1 ;
953
954 hr_utility.trace('Entering csr_leave_taken3');
955 hr_utility.set_location('csr_rec.element_reporting_name.= '||csr_rec.element_reporting_name,50);
956 hr_utility.set_location('csr_rec.start_date.............= '||to_char(csr_rec.start_date,'DD-MON-YYYY'),50);
957 hr_utility.set_location('csr_rec.end_date...............= '||to_char(csr_rec.end_date,'DD-MON-YYYY'),50);
958 hr_utility.set_location('csr_rec.absence_hours..........= '||csr_rec.absence_hours,50);
959 hr_utility.set_location('csr_rec.payment................= '||csr_rec.payment,50);
960
961 l_start_date := fnd_date.date_to_canonical(csr_rec.start_date); /*Bug 2438495*/
962 l_end_date := fnd_date.date_to_canonical(csr_rec.end_date);
963
964 pay_action_information_api.create_action_information
965 (p_action_information_id => l_action_info_id
966 , p_action_context_id => p_arc_assignment_action_id
967 , p_action_context_type => 'AAP'
968 , p_object_version_number => l_ovn
969 , p_effective_date => p_pre_effective_date
970 , p_source_id => NULL
971 , p_source_text => NULL
972 , p_action_information_category => 'APAC ABSENCES'
973 , p_action_information1 => NULL
974 , p_action_information2 => csr_rec.element_reporting_name
975 , p_action_information3 => NULL
976 , p_action_information4 => l_start_date
977 , p_action_information5 => l_end_date
978 , p_action_information6 => fnd_number.number_to_canonical(csr_rec.absence_hours) -- Bug: 3604094
979 , p_action_information7 => NULL
980 , p_action_information8 => fnd_number.number_to_canonical(csr_rec.payment) -- Bug: 3604094
981 );
982
983
984 end if;
985
986 END LOOP;
987
988
989
990 FOR csr_rec IN csr_leave_taken4(p_time_period_id, p_assignment_id)
991 LOOP
992
993 if i = 1 then
994 l_exists := 'N';
995
996 else
997
998 for j in tab_row_id.first..tab_row_id.last
999 loop
1000
1001 if tab_row_id(j) = csr_rec.row_id then
1002 l_exists := 'Y';
1003 exit;
1004 else
1005 l_exists := 'N';
1006 end if;
1007
1008 end loop;
1009
1010 end if;
1011
1012
1013 if l_exists = 'N' then
1014
1015 tab_row_id(i) := csr_rec.row_id ;
1016 i := i + 1 ;
1017
1018 hr_utility.trace('Entering csr_leave_taken4');
1019 hr_utility.set_location('csr_rec.element_reporting_name.= '||csr_rec.element_reporting_name,50);
1020 hr_utility.set_location('csr_rec.start_date.............= '||to_char(csr_rec.start_date,'DD-MON-YYYY'),50);
1021 hr_utility.set_location('csr_rec.end_date...............= '||to_char(csr_rec.end_date,'DD-MON-YYYY'),50);
1022 hr_utility.set_location('csr_rec.absence_hours..........= '||csr_rec.absence_hours,50);
1023 hr_utility.set_location('csr_rec.payment................= '||csr_rec.payment,50);
1024
1025 l_start_date := fnd_date.date_to_canonical(csr_rec.start_date); /*Bug 2438495*/
1026 l_end_date := fnd_date.date_to_canonical(csr_rec.end_date);
1027
1028 pay_action_information_api.create_action_information
1029 (p_action_information_id => l_action_info_id
1030 , p_action_context_id => p_arc_assignment_action_id
1031 , p_action_context_type => 'AAP'
1032 , p_object_version_number => l_ovn
1033 , p_effective_date => p_pre_effective_date
1034 , p_source_id => NULL
1035 , p_source_text => NULL
1036 , p_action_information_category => 'APAC ABSENCES'
1037 , p_action_information1 => NULL
1038 , p_action_information2 => csr_rec.element_reporting_name
1039 , p_action_information3 => NULL
1040 , p_action_information4 => l_start_date
1041 , p_action_information5 => l_end_date
1042 , p_action_information6 => fnd_number.number_to_canonical(csr_rec.absence_hours) -- Bug: 3604094
1043 , p_action_information7 => NULL
1044 , p_action_information8 => fnd_number.number_to_canonical(csr_rec.payment) -- Bug: 3604094
1045 );
1046
1047
1048 end if;
1049
1050 END LOOP;
1051
1052
1053 FOR csr_rec IN csr_leave_taken5(p_time_period_id, p_assignment_id)
1054 LOOP
1055
1056 if i = 1 then
1057 l_exists := 'N';
1058
1059 else
1060
1061 for j in tab_row_id.first..tab_row_id.last
1062 loop
1063
1064 if tab_row_id(j) = csr_rec.row_id then
1065 l_exists := 'Y';
1066 exit;
1067 else
1068 l_exists := 'N';
1069 end if;
1070
1071 end loop;
1072
1073 end if;
1074
1075
1076 if l_exists = 'N' then
1077
1078 tab_row_id(i) := csr_rec.row_id ;
1079 i := i + 1 ;
1080
1081 hr_utility.trace('Entering csr_leave_taken5');
1082 hr_utility.set_location('csr_rec.element_reporting_name.= '||csr_rec.element_reporting_name,50);
1083 hr_utility.set_location('csr_rec.start_date.............= '||to_char(csr_rec.start_date,'DD-MON-YYYY'),50);
1084 hr_utility.set_location('csr_rec.end_date...............= '||to_char(csr_rec.end_date,'DD-MON-YYYY'),50);
1085 hr_utility.set_location('csr_rec.absence_hours..........= '||csr_rec.absence_hours,50);
1086 hr_utility.set_location('csr_rec.payment................= '||csr_rec.payment,50);
1087
1088 l_start_date := fnd_date.date_to_canonical(csr_rec.start_date); /*Bug 2438495*/
1089 l_end_date := fnd_date.date_to_canonical(csr_rec.end_date);
1090
1091 pay_action_information_api.create_action_information
1092 (p_action_information_id => l_action_info_id
1093 , p_action_context_id => p_arc_assignment_action_id
1094 , p_action_context_type => 'AAP'
1095 , p_object_version_number => l_ovn
1096 , p_effective_date => p_pre_effective_date
1097 , p_source_id => NULL
1098 , p_source_text => NULL
1099 , p_action_information_category => 'APAC ABSENCES'
1100 , p_action_information1 => NULL
1101 , p_action_information2 => csr_rec.element_reporting_name
1102 , p_action_information3 => NULL
1103 , p_action_information4 => l_start_date
1104 , p_action_information5 => l_end_date
1105 , p_action_information6 => fnd_number.number_to_canonical(csr_rec.absence_hours) -- Bug: 3604094
1106 , p_action_information7 => NULL
1107 , p_action_information8 => fnd_number.number_to_canonical(csr_rec.payment) -- Bug: 3604094
1108 );
1109
1110
1111 end if;
1112
1113 END LOOP;
1114
1115 FOR csr_rec IN csr_leave_taken6(p_time_period_id, p_assignment_id)
1116 LOOP
1117
1118 if i = 1 then
1119 l_exists := 'N';
1120
1121 else
1122
1123 for j in tab_row_id.first..tab_row_id.last
1124 loop
1125
1126 if tab_row_id(j) = csr_rec.row_id then
1127 l_exists := 'Y';
1128 exit;
1129 else
1130 l_exists := 'N';
1131 end if;
1132
1133 end loop;
1134
1135 end if;
1136
1137
1138 if l_exists = 'N' then
1139
1140 tab_row_id(i) := csr_rec.row_id ;
1141 i := i + 1 ;
1142
1143 hr_utility.trace('Entering csr_leave_taken6');
1144 hr_utility.set_location('csr_rec.element_reporting_name.= '||csr_rec.element_reporting_name,50);
1145 hr_utility.set_location('csr_rec.start_date.............= '||to_char(csr_rec.start_date,'DD-MON-YYYY'),50);
1146 hr_utility.set_location('csr_rec.end_date...............= '||to_char(csr_rec.end_date,'DD-MON-YYYY'),50);
1147 hr_utility.set_location('csr_rec.absence_hours..........= '||csr_rec.absence_hours,50);
1148 hr_utility.set_location('csr_rec.payment................= '||csr_rec.payment,50);
1149
1150 l_start_date := fnd_date.date_to_canonical(csr_rec.start_date); /*Bug 2438495*/
1151 l_end_date := fnd_date.date_to_canonical(csr_rec.end_date);
1152
1153 pay_action_information_api.create_action_information
1154 (p_action_information_id => l_action_info_id
1155 , p_action_context_id => p_arc_assignment_action_id
1156 , p_action_context_type => 'AAP'
1157 , p_object_version_number => l_ovn
1158 , p_effective_date => p_pre_effective_date
1159 , p_source_id => NULL
1160 , p_source_text => NULL
1161 , p_action_information_category => 'APAC ABSENCES'
1162 , p_action_information1 => NULL
1163 , p_action_information2 => csr_rec.element_reporting_name
1164 , p_action_information3 => NULL
1165 , p_action_information4 => l_start_date
1166 , p_action_information5 => l_end_date
1167 , p_action_information6 => fnd_number.number_to_canonical(csr_rec.absence_hours) -- Bug: 3604094
1168 , p_action_information7 => NULL
1169 , p_action_information8 => fnd_number.number_to_canonical(csr_rec.payment) -- Bug: 3604094
1170 );
1171
1172
1173 end if;
1174
1175 END LOOP;
1176
1177 FOR csr_rec IN csr_leave_taken7(p_time_period_id, p_assignment_id)
1178 LOOP
1179
1180 if i = 1 then
1181 l_exists := 'N';
1182
1183 else
1184
1185 for j in tab_row_id.first..tab_row_id.last
1186 loop
1187
1188 if tab_row_id(j) = csr_rec.row_id then
1189 l_exists := 'Y';
1190 exit;
1191 else
1192 l_exists := 'N';
1193 end if;
1194
1195 end loop;
1196
1197 end if;
1198
1199
1200 if l_exists = 'N' then
1201
1202 tab_row_id(i) := csr_rec.row_id ;
1203 i := i + 1 ;
1204
1205 hr_utility.trace('Entering csr_leave_taken7');
1206 hr_utility.set_location('csr_rec.element_reporting_name.= '||csr_rec.element_reporting_name,50);
1207 hr_utility.set_location('csr_rec.start_date.............= '||to_char(csr_rec.start_date,'DD-MON-YYYY'),50);
1208 hr_utility.set_location('csr_rec.end_date...............= '||to_char(csr_rec.end_date,'DD-MON-YYYY'),50);
1209 hr_utility.set_location('csr_rec.absence_hours..........= '||csr_rec.absence_hours,50);
1210 hr_utility.set_location('csr_rec.payment................= '||csr_rec.payment,50);
1211
1212 l_start_date := fnd_date.date_to_canonical(csr_rec.start_date); /*Bug 2438495*/
1213 l_end_date := fnd_date.date_to_canonical(csr_rec.end_date);
1214
1215 pay_action_information_api.create_action_information
1216 (p_action_information_id => l_action_info_id
1217 , p_action_context_id => p_arc_assignment_action_id
1218 , p_action_context_type => 'AAP'
1219 , p_object_version_number => l_ovn
1220 , p_effective_date => p_pre_effective_date
1221 , p_source_id => NULL
1222 , p_source_text => NULL
1223 , p_action_information_category => 'APAC ABSENCES'
1224 , p_action_information1 => NULL
1225 , p_action_information2 => csr_rec.element_reporting_name
1226 , p_action_information3 => NULL
1227 , p_action_information4 => l_start_date
1228 , p_action_information5 => l_end_date
1229 , p_action_information6 => fnd_number.number_to_canonical(csr_rec.absence_hours) -- Bug: 3604094
1230 , p_action_information7 => NULL
1231 , p_action_information8 => fnd_number.number_to_canonical(csr_rec.payment) -- Bug: 3604094
1232 );
1233
1234
1235 end if;
1236
1237 END LOOP;
1238
1239 hr_utility.set_location('Leaving '||l_procedure,1000);
1240
1241 exception
1242 when others
1243 then
1244 hr_utility.set_location('Error in '||l_procedure,999999);
1245 raise;
1246 END archive_absences;
1247
1248 --
1249 -- Bug#5681819
1250 -- Procedure archives payment details of the payment methods which get skipped in core procedure
1251 -- pay_emp_action_arch.get_net_pay_distribution() , this procedure archives payment methods
1252 -- for which effective start date is between period end date and payment date(+ve offset payroll)
1253 -- or payment method end date is after payment date and before period end date(-ve offset payroll).
1254 --
1255 PROCEDURE archive_offset_payment_method(
1256 p_pre_pay_action_id in number
1257 ,p_assignment_id in number
1258 ,p_curr_pymt_eff_date in date
1259 ,p_ppp_source_action_id in number
1260 ,p_action_context_id in number
1261 ,p_action_context_type in varchar2
1262 ,p_tax_unit_id in number
1263 ,p_period_end_date in date
1264 )
1265 IS
1266 --
1267 --
1268 --
1269 /* Bug 6962336 - Added a NOT EXISTS Clause to ensure the same
1270 payment is not archived twice
1271 */
1272 cursor c_net_pay(cp_pre_pay_action_id in number
1273 ,cp_assignment_id in number
1274 ,cp_curr_pymt_eff_date in date
1275 ,cp_ppp_source_action_id in number
1276 ,cp_action_context_id in number
1277 ) is
1278 select pea.segment1 seg1,
1279 pea.segment2 seg2,
1280 pea.segment3 seg3,
1281 pea.segment4 seg4,
1282 pea.segment5 seg5,
1283 pea.segment6 seg6,
1284 pea.segment7 seg7,
1285 pea.segment8 seg8,
1286 pea.segment9 seg9,
1287 pea.segment10 seg10,
1288 ppp.value amount,
1289 ppp.pre_payment_id,
1290 popm.org_payment_method_id,
1291 popm.org_payment_method_name,
1292 pppm.personal_payment_method_id
1293 from pay_assignment_actions paa,
1294 pay_pre_payments ppp,
1295 pay_org_payment_methods_f popm ,
1296 pay_personal_payment_methods_f pppm,
1297 pay_external_accounts pea
1298 where paa.assignment_action_id = cp_pre_pay_action_id
1299 and ppp.assignment_action_id = paa.assignment_action_id
1300 and paa.assignment_id = cp_assignment_id
1301 and ( ( ppp.source_action_id is null
1302 and cp_ppp_source_action_id is null)
1303 or
1304 -- is it a Normal or Process Separate specific
1305 -- Payments should be included in the Standard
1306 -- SOE. Only Separate Payments should be in
1307 -- a Separate SOE.
1308 (ppp.source_action_id is not null
1309 and cp_ppp_source_action_id is null
1310 and exists (
1311 select ''
1312 from pay_run_types_f prt,
1313 pay_assignment_actions paa_run,
1314 pay_payroll_actions ppa_run
1315 where paa_run.assignment_action_id
1316 = ppp.source_action_id
1317 and paa_run.payroll_action_id
1318 = ppa_run.payroll_action_id
1319 and paa_run.run_type_id = prt.run_type_id
1320 and prt.run_method in ('P', 'N')
1321 and ppa_run.effective_date
1322 between prt.effective_start_date
1323 and prt.effective_end_date
1324 )
1325 )
1326 or
1327 (cp_ppp_source_action_id is not null
1328 and ppp.source_action_id = cp_ppp_source_action_id)
1329 )
1330 and ppp.org_payment_method_id = popm.org_payment_method_id
1331 and popm.defined_balance_id is not null
1332 and pppm.personal_payment_method_id(+)
1333 = ppp.personal_payment_method_id
1334 and pea.external_account_id(+) = pppm.external_account_id
1335 and cp_curr_pymt_eff_date between popm.effective_start_date
1336 and popm.effective_end_date
1337 and cp_curr_pymt_eff_date between nvl(pppm.effective_start_date,
1338 cp_curr_pymt_eff_date)
1339 and nvl(pppm.effective_end_date,
1340 cp_curr_pymt_eff_date)
1341 /* Bug 6962336 - Add NOT EXISTS Clause */
1342 AND NOT EXISTS
1343 ( SELECT pai.action_information_id
1344 FROM pay_action_information pai
1345 WHERE pai.action_context_id = cp_action_context_id
1346 AND pai.action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION'
1347 AND pai.action_context_type = 'AAP'
1348 AND pai.action_information15 = ppp.pre_payment_id
1349 );
1350
1351 ln_index NUMBER;
1352 lv_segment1 VARCHAR2(300);
1353 lv_segment2 VARCHAR2(300);
1354 lv_segment3 VARCHAR2(300);
1355 lv_segment4 VARCHAR2(300);
1356 lv_segment5 VARCHAR2(300);
1357 lv_segment6 VARCHAR2(300);
1358 lv_segment7 VARCHAR2(300);
1359 lv_segment8 VARCHAR2(300);
1360 lv_segment9 VARCHAR2(300);
1361 lv_segment10 VARCHAR2(300);
1362 ln_value NUMBER(15,2);
1363 ln_pre_payment_id NUMBER;
1364 ln_org_payment_method_id NUMBER;
1365 lv_org_payment_method_name VARCHAR2(300);
1366 ln_emp_payment_method_id NUMBER;
1367 lv_procedure_name VARCHAR2(100);
1368 l_action_information_id_1 NUMBER ;
1369 l_object_version_number_1 NUMBER ;
1370
1371 BEGIN
1372 lv_procedure_name := '.archive_offset_payment_method';
1373 hr_utility.set_location('pay_au_payslip_archive' || lv_procedure_name,10);
1374 hr_utility.trace('p_pre_pay_action_id = ' || p_pre_pay_action_id);
1375 hr_utility.trace('p_curr_pymt_eff_date = ' || p_curr_pymt_eff_date);
1376 hr_utility.trace('p_ppp_source_action_id = '|| p_ppp_source_action_id);
1377
1378 open c_net_pay(p_pre_pay_action_id
1379 ,p_assignment_id
1380 ,p_curr_pymt_eff_date
1381 ,p_ppp_source_action_id
1382 ,p_action_context_id);
1383 hr_utility.trace('Opened cursor c_net_pay ');
1384
1385 loop
1386 fetch c_net_pay into lv_segment1
1387 ,lv_segment2
1388 ,lv_segment3
1389 ,lv_segment4
1390 ,lv_segment5
1391 ,lv_segment6
1392 ,lv_segment7
1393 ,lv_segment8
1394 ,lv_segment9
1395 ,lv_segment10
1396 ,ln_value
1397 ,ln_pre_payment_id
1398 ,ln_org_payment_method_id
1399 ,lv_org_payment_method_name
1400 ,ln_emp_payment_method_id;
1401 hr_utility.trace('Fetched c_net_pay ');
1402 if c_net_pay%notfound then
1403 exit;
1404 end if;
1405
1406 pay_action_information_api.create_action_information(
1407 p_action_information_id => l_action_information_id_1,
1408 p_object_version_number => l_object_version_number_1,
1409 p_action_information_category => 'EMPLOYEE NET PAY DISTRIBUTION',
1410 p_action_context_id => p_action_context_id,
1411 p_action_context_type => p_action_context_type,
1412 p_jurisdiction_code => '00-000-0000',
1413 p_assignment_id => p_assignment_id,
1414 p_tax_unit_id => p_tax_unit_id,
1415 p_effective_date => p_period_end_date,
1416 p_action_information1 => ln_org_payment_method_id,
1417 p_action_information2 => ln_emp_payment_method_id,
1418 p_action_information3 => null,
1419 p_action_information4 => null,
1420 p_action_information5 => lv_segment1,
1421 p_action_information6 => lv_segment2,
1422 p_action_information7 => lv_segment3,
1423 p_action_information8 => lv_segment4,
1424 p_action_information9 => lv_segment5,
1425 p_action_information10 => lv_segment6,
1426 p_action_information11 => lv_segment7,
1427 p_action_information12 => lv_segment8,
1428 p_action_information13 => lv_segment9,
1429 p_action_information14 => lv_segment10,
1430 p_action_information15 => ln_pre_payment_id,
1431 p_action_information16 => fnd_number.number_to_canonical(ln_value),
1432 p_action_information17 => p_pre_pay_action_id,
1433 p_action_information18 => lv_org_payment_method_name,
1434 p_action_information19 => null,
1435 p_action_information20 => null,
1436 p_action_information21 => null,
1437 p_action_information22 => null,
1438 p_action_information23 => null,
1439 p_action_information24 => null,
1440 p_action_information25 => null,
1441 p_action_information26 => null,
1442 p_action_information27 => null,
1443 p_action_information28 => null,
1444 p_action_information29 => null,
1445 p_action_information30 => null
1446 );
1447 end loop;
1448 close c_net_pay;
1449 hr_utility.set_location('Leaving pay_au_payslip_archive'|| lv_procedure_name,100);
1450 END archive_offset_payment_method;
1451 --
1452 --------------------------------------------------------------------------------------
1453 --
1454 -- archive_employee_details
1455 --
1456 -- Calls 'pay_emp_action_arch.get_personal_information' that actually
1457 -- archives the employee details,employee address details, Employer Address Details
1458 -- and Net Pay Distribution inforamation.
1459 -- tax_unit_id must be passed as a parameter to this procedure to make core provided
1460 -- 'Choose Payslip' view return appropriate rows.
1461 -- The action DF structures used are -
1462 -- ADDRESS DETAILS
1463 -- EMPLOYEE DETAILS
1464 -- EMPLOYEE NET PAY DISTRIBUTION
1465 -- EMPLOYEE OTHER INFORMATION
1466 -- Additionally required fields for Australia which have not already been archived
1467 -- are archived into the structure 'AU EMPLOYEE DETAILS'
1468 ---------------------------------------------------------------------------------------
1469
1470 procedure archive_employee_details
1471 (p_assignment_id in pay_assignment_actions.assignment_id%type
1472 ,p_arc_assignment_action_id in pay_assignment_actions.assignment_action_id%type -- assignment action for archive run
1473 ,p_run_assignment_action_id in pay_assignment_actions.assignment_action_id%type -- assignment action for payroll run
1474 ,p_pre_assignment_action_id in pay_assignment_actions.assignment_action_id%type -- assignment action for prepayment run
1475 ,p_pre_effective_date in pay_payroll_actions.effective_date%type -- effective date of prepayment run
1476 ,p_run_effective_date in pay_payroll_actions.effective_date%type -- effective date of payroll run
1477 ,p_run_date_earned in pay_payroll_actions.date_earned%type
1478 ,p_time_period_id in per_time_periods.time_period_id%type
1479 ,p_period_end_date in per_time_periods.end_date%type /* Bug No : 2491444 */
1480 ,p_regular_payment_date in per_time_periods.regular_payment_date%type /* Bug# 5681819*/
1481 ) is
1482
1483 l_action_info_id pay_action_information.action_information_id%type;
1484 l_ovn pay_action_information.object_version_number%type;
1485 l_date_earned pay_payroll_actions.date_earned%type;
1486 l_procedure varchar2(80) ;
1487 l_abn number;
1488 l_tax_unit_id pay_assignment_actions.tax_unit_id%type;
1489
1490 cursor csr_tax_unit
1491 (p_assignment_action_id pay_assignment_actions.assignment_action_id%type) is
1492 select tax_unit_id
1493 from pay_assignment_actions
1494 where assignment_action_id = p_assignment_action_id;
1495
1496 --
1497 -- Get the Employer ABN which is stored
1498 -- in the organization EIT strucutre AU_LEGAL_EMPLOYER
1499 --
1500 /*Bug2920725 Corrected base tables to support security model*/
1501 cursor csr_abn
1502 (p_assignment_id pay_assignment_actions.assignment_id%type
1503 ,p_effective_date pay_payroll_actions.effective_date%type
1504 ) is
1505 select org.org_information12 abn
1506 from per_assignments_f paaf
1507 , hr_soft_coding_keyflex flex
1508 , hr_organization_information org
1509 where paaf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
1510 and to_char(org.organization_id) = flex.segment1
1511 and org.org_information_context = 'AU_LEGAL_EMPLOYER'
1512 and paaf.assignment_id = p_assignment_id
1513 and p_effective_date between paaf.effective_start_date and paaf.effective_end_date;
1514
1515 CURSOR csr_child_action
1516 (p_prepay_action_id pay_assignment_actions.assignment_action_id%type,
1517 p_source_action_id pay_assignment_actions.assignment_action_id%type) IS
1518 SELECT paa.assignment_action_id
1519 FROM pay_assignment_actions paa,
1520 pay_action_interlocks pai,
1521 pay_run_types_f prt
1522 WHERE pai.locking_action_id = p_prepay_action_id
1523 and paa.assignment_action_id = pai.locked_action_id
1524 and paa.source_action_id = p_source_action_id
1525 and paa.run_type_id = prt.run_type_id;
1526
1527 /* Bug 5504354 - Added cursor to get pay_advice_date */
1528 /* Bug 6032985 - Added ptp.default_dd_date in place of pay_advice_date ,in case where
1529 ptp.default_dd_date is null use effective date of Pre Payment */
1530 cursor csr_pay_advice_date(p_run_assignment_action_id pay_assignment_actions.assignment_action_id%type ,
1531 p_run_date_earned pay_payroll_actions.date_earned%type,
1532 p_pre_effective_date pay_payroll_actions.effective_date%type)
1533 is
1534 select nvl(ptp.default_dd_date,p_pre_effective_date)
1535 from pay_payroll_actions ppa,
1536 per_time_periods ptp,
1537 pay_assignment_actions paa
1538 where p_run_date_earned between ptp.start_date and ptp.end_date
1539 and paa.assignment_action_id=p_run_assignment_action_id
1540 and paa.payroll_action_id=ppa.payroll_action_id
1541 and ppa.payroll_id=ptp.payroll_id;
1542
1543 /* Bug 5504354 c_get_bus_id */
1544 cursor c_get_bus_id ( p_assignment_id pay_assignment_actions.assignment_id%type
1545 ,p_effective_date date ) is
1546 select distinct business_group_id
1547 from per_all_assignments_f
1548 where assignment_id=p_assignment_id
1549 and p_effective_date between effective_start_date and effective_end_date;
1550
1551
1552 /* Bug 5504354 - Cursor c_grade_step added to get the grade step of an assignment */
1553
1554 cursor c_grade_step(p_assignment_id pay_assignment_actions.assignment_id%type
1555 ,p_effective_date pay_payroll_actions.effective_date%type,
1556 p_business_group_id per_all_assignments_f.business_group_id%type
1557 ) is
1558 select count(*)
1559 from per_spinal_point_steps_f psp,
1560 per_spinal_point_placements_f pspp,
1561 per_spinal_point_steps_f psp2
1562 where psp.sequence>= psp2.sequence
1563 and pspp.step_id=psp.step_id
1564 and pspp.assignment_id=p_assignment_id
1565 and psp.grade_spine_id=psp2.grade_spine_id
1566 and pspp.business_group_id=p_business_group_id
1567 and psp.business_group_id=p_business_group_id
1568 and psp2.business_group_id=p_business_group_id
1569 and p_effective_date between
1570 psp.effective_start_date and psp.effective_end_date
1571 and p_effective_date between
1572 psp2.effective_start_date and psp2.effective_end_date
1573 and p_effective_date between
1574 pspp.effective_start_date and pspp.effective_end_date;
1575
1576 l_business_group_id per_all_assignments_f.business_group_id%type;
1577
1578 l_step varchar2(10); /* Bug 5504354 */
1579
1580 l_pay_advice_date date ; /* Bug 5504354 */
1581
1582 l_child_action_id pay_assignment_actions.assignment_action_id%type;
1583
1584 begin
1585 l_procedure := g_package||'archive_employee_details';
1586 hr_utility.set_location('Entering '|| l_procedure,1);
1587
1588 l_step := null ; /* Bug 5504354 Initializing l_step */
1589
1590 -- Need to get the end date of the latest runs period.
1591 -- Leave is calculated up to the end of the period and the EMPLOYEE DETAILS
1592 -- must be archived with the latest period end date
1593 --
1594
1595 -- call generic procedure to retrieve and archive all data for
1596 -- EMPLOYEE DETAILS, ADDRESS DETAILS and EMPLOYEE NET PAY DISTRIBUTION
1597
1598 open csr_tax_unit(p_pre_assignment_action_id);
1599 fetch csr_tax_unit
1600 into l_tax_unit_id;
1601 close csr_tax_unit;
1602
1603 hr_utility.set_location('p_payroll_action_id........'||g_arc_payroll_action_id,10);
1604 hr_utility.set_location('p_arc_assignment_action_id '||p_arc_assignment_action_id,10);
1605 hr_utility.set_location('p_assignment_id............'||p_assignment_id,10);
1606 hr_utility.set_location('p_pre_assignment_action_id.'||p_pre_assignment_action_id,10);
1607 hr_utility.set_location('p_pre_effective_date.......'||to_char(p_pre_effective_date,'DD-MON-YYYY'),10);
1608 hr_utility.set_location('p_time_period_id...........'||p_time_period_id,10);
1609 hr_utility.set_location('p_period_end_date..........'||p_period_end_date,10); /* Bug No : 2491444 */
1610 hr_utility.set_location('Calling pay_emp_action_arch.get_personal_information',10);
1611
1612 /* Bug No : 2491444 -- Value passed for p_time_period_id changed */
1613
1614 pay_emp_action_arch.get_personal_information
1615 ( p_payroll_action_id => g_arc_payroll_action_id -- archive run payroll_action_id
1616 , p_assactid => p_arc_assignment_action_id -- archive run assignment_action_id
1617 , p_assignment_id => p_assignment_id -- current assignment_id
1618 , p_curr_pymt_ass_act_id => p_pre_assignment_action_id -- prepayment run assignment_action_id
1619 , p_curr_eff_date => p_run_effective_date -- payroll run effective_date
1620 , p_date_earned => p_run_date_earned -- payroll date_earned
1621 , p_curr_pymt_eff_date => p_period_end_date -- latest run period end date, needed for core choose payslip
1622 , p_tax_unit_id => l_tax_unit_id -- GRE contained in tax_unit_id for assignment_action
1623 , p_time_period_id => p_time_period_id -- time_period_id from per_time_periods /* Bug No : 2491444 */
1624 , p_ppp_source_action_id => NULL
1625 );
1626
1627 hr_utility.set_location('AU Finished get_personal_information',15);
1628
1629
1630 /* Bug#5681819 Call procedure only incase of offset payroll
1631 Bug#6962336 Modified Date from Regular Payment Date to Prepayment Effective Date
1632 Payments must be checked as on Prepayment Effective date, the Core Method archives all
1633 payments active on Period End Date, the following call will archive any payments missed
1634 out by Core function.
1635 */
1636 if ( p_period_end_date <> p_pre_effective_date) then
1637 /*
1638 Bug#5681819
1639 -- For positive or negative offset payroll if payment method dates do not fall in payroll period these
1640 -- payment method details get archive by this Procedure.
1641 --
1642 */
1643
1644 archive_offset_payment_method(
1645 p_pre_pay_action_id => p_pre_assignment_action_id
1646 ,p_assignment_id => p_assignment_id
1647 ,p_curr_pymt_eff_date => p_pre_effective_date /* Bug 6962336 */
1648 ,p_ppp_source_action_id => NULL
1649 ,p_action_context_id => p_arc_assignment_action_id
1650 ,p_action_context_type => 'AAP'
1651 ,p_tax_unit_id =>l_tax_unit_id
1652 ,p_period_end_date => p_period_end_date
1653 );
1654 end if;
1655
1656 -- Get the annual leave balance
1657 hr_utility.set_location('p_payroll_action_id.........'||g_arc_payroll_action_id,20);
1658 hr_utility.set_location('p_time_period_id............'||p_time_period_id,20);
1659 hr_utility.set_location('p_assignment_id.............'||p_assignment_id,20);
1660 hr_utility.set_location('p_run_date_earned...........'||to_char(p_run_date_earned,'DD-MON-YYYY'),20);
1661 hr_utility.set_location('p_arc_assignment_action_id..'||p_arc_assignment_action_id,20);
1662 hr_utility.set_location('p_run_assignment_action_id..'||p_run_assignment_action_id,20);
1663 hr_utility.set_location('Calling pay_apac_payslip_archive.archive_leave_details',20);
1664
1665 /* Bug No : 2491444 -- In all the procedure calls below the value passed for time_period_id changed */
1666
1667 archive_accruals
1668 (p_assignment_id => p_assignment_id
1669 ,p_pre_effective_date => p_pre_effective_date
1670 ,p_run_assignment_action_id => p_run_assignment_action_id
1671 ,p_arc_assignment_action_id => p_arc_assignment_action_id
1672 ,p_calculation_date => p_period_end_date -- Calculate the balance as at period end date
1673 );
1674
1675 hr_utility.set_location(l_procedure,21);
1676
1677 archive_absences
1678 (p_assignment_id => p_assignment_id
1679 ,p_pre_effective_date => p_pre_effective_date
1680 ,p_time_period_id => p_time_period_id
1681 ,p_arc_assignment_action_id => p_arc_assignment_action_id
1682 ,p_run_assignment_action_id => p_run_assignment_action_id
1683 );
1684
1685 hr_utility.set_location(l_procedure,22);
1686
1687 /* Bug# 4363057 - Modified call to archive_stat_balances passing prepayment run action_id*/
1688 archive_stat_balances
1689 (p_pre_assignment_action_id => p_pre_assignment_action_id -- prepayment run assignment action, latest
1690 ,p_pre_effective_date => p_pre_effective_date -- prepayment run effective date used to archive
1691 ,p_assignment_id => p_assignment_id
1692 ,p_arc_assignment_action_id => p_arc_assignment_action_id -- archive run assignment action
1693 ,p_calculation_date => p_period_end_date -- date to calculate the balances at
1694 );
1695
1696
1697 hr_utility.set_location(l_procedure,23);
1698
1699 pay_apac_payslip_archive.archive_user_balances
1700 (p_arch_assignment_action_id => p_arc_assignment_action_id -- archive run assignment action
1701 ,p_run_assignment_action_id => p_run_assignment_action_id -- payroll run assignment action
1702 ,p_pre_effective_date => p_pre_effective_date -- prepayment run effective_date
1703 );
1704
1705 hr_utility.set_location(l_procedure,24);
1706
1707 archive_stat_elements
1708 (p_pre_assignment_action_id => p_pre_assignment_action_id -- prepayment run assignment action
1709 ,p_pre_effective_date => p_pre_effective_date -- prepayment run effective date used to archive
1710 ,p_assignment_id => p_assignment_id -- assignment id
1711 ,p_arc_assignment_action_id => p_arc_assignment_action_id -- archive run assignment action
1712 );
1713
1714 hr_utility.set_location(l_procedure,25);
1715
1716 pay_apac_payslip_archive.archive_user_elements
1717 (p_arch_assignment_action_id => p_arc_assignment_action_id -- archive run assignment action
1718 ,p_pre_assignment_action_id => p_pre_assignment_action_id -- prepayment run assignment action
1719 ,p_latest_run_assact_id => p_run_assignment_action_id -- payroll run assignment action
1720 ,p_pre_effective_date => p_pre_effective_date -- prepayment run effective_date
1721 );
1722
1723 hr_utility.set_location(l_procedure,26);
1724
1725 /* Bug 5504354 - Archive Pay Advice Date */
1726 /* Bug 6032985 - added parameter p_pre_effective_date */
1727 open csr_pay_advice_date(p_run_assignment_action_id,p_run_date_earned,p_pre_effective_date);
1728 fetch csr_pay_advice_date into l_pay_advice_date;
1729 close csr_pay_advice_date;
1730
1731 -- Get the ABN number
1732 open csr_abn(p_assignment_id, p_pre_effective_date);
1733 fetch csr_abn into l_abn;
1734 close csr_abn;
1735
1736 hr_utility.set_location('Archiving AU EMPLOYEE DETAILS',30);
1737
1738 pay_action_information_api.create_action_information
1739 ( p_action_information_id => l_action_info_id
1740 , p_action_context_id => p_arc_assignment_action_id
1741 , p_action_context_type => 'AAP'
1742 , p_object_version_number => l_ovn
1743 , p_effective_date => p_pre_effective_date
1744 , p_source_id => NULL
1745 , p_source_text => NULL
1746 , p_action_information_category => 'AU EMPLOYEE DETAILS'
1747 , p_action_information1 => NULL
1748 , p_action_information2 => NULL
1749 , p_action_information3 => NULL
1750 , p_action_information21 => l_abn
1751 , p_action_information22 => l_pay_advice_date -- Added for pay advice date /*Bug 5504354 */
1752 );
1753
1754
1755 open c_get_bus_id(p_assignment_id,p_pre_effective_date);
1756 fetch c_get_bus_id into l_business_group_id;
1757 close c_get_bus_id;
1758
1759 /* bug 5504354 Get the Grade Step */
1760
1761 open c_grade_step(p_assignment_id, p_pre_effective_date,l_business_group_id);
1762 fetch c_grade_step into l_step;
1763 close c_grade_step;
1764
1765 /* If Step is 0 then l_step is null i.e no grade step is attached */
1766
1767 if l_step = 0 then
1768
1769 l_step :=null;
1770
1771 end if;
1772
1773 /* Bug 5504354- This grade step will be mapped to an additional person information
1774 detail item in Payslip. Grade step will be mapped to Action Information9
1775 and will be archived with action_information_category as ADDL EMPLOYEE DETAILS */
1776
1777 hr_utility.set_location('Archiving ADDL EMPLOYEE DETAILS',30);
1778
1779 hr_utility.trace('Value of l_action_info_id is '||l_action_info_id);
1780 hr_utility.trace('Value of p_arc_assignment_action_id is '||p_arc_assignment_action_id);
1781 hr_utility.trace('Value of p_pre_effective_date is '||p_pre_effective_date);
1782 hr_utility.trace('Value of l_step is '||l_step);
1783 hr_utility.trace('Value of p_assignment_id is '||p_assignment_id);
1784
1785
1786 pay_action_information_api.create_action_information (
1787 p_action_information_id => l_action_info_id
1788 ,p_action_context_id => p_arc_assignment_action_id
1789 ,p_action_context_type => 'AAP'
1790 ,p_object_version_number => l_ovn
1791 ,p_effective_date => p_pre_effective_date
1792 ,p_source_id => NULL
1793 ,p_source_text => NULL
1794 ,p_action_information_category => 'ADDL EMPLOYEE DETAILS'
1795 ,p_action_information9 => l_step
1796 );
1797
1798 hr_utility.set_location('Leaving '|| l_procedure,1000);
1799 exception
1800 when others
1801 then
1802 hr_utility.set_location('Error in '||l_procedure,999999);
1803 raise;
1804 END archive_employee_details;
1805
1806 ----------------------------
1807 --
1808 -- insert_quickpay_pa_info
1809 --
1810 -- This procedure is added for bug #15914996.
1811 -- It calls pay_emp_action_arch.arch_pay_action_level_data to archive
1812 -- employer's address details for QuickPay payslip archive.
1813 --
1814 ----------------------------
1815
1816 procedure insert_quickpay_pa_info
1817 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE
1818 ,p_effective_date in pay_action_information.effective_date%TYPE
1819 ) is
1820
1821 cursor csr_get_payroll_id
1822 (p_payroll_action_id pay_payroll_actions.payroll_id%TYPE)
1823 is
1824 select payroll_id
1825 from pay_payroll_actions paa
1826 where paa.payroll_action_id = p_payroll_action_id;
1827
1828 l_procedure varchar2(200);
1829
1830 l_payroll_id pay_payroll_actions.payroll_id%TYPE;
1831
1832 begin
1833 l_procedure := g_package||'archive_data_for_quickpay';
1834 hr_utility.set_location('Entering '||l_procedure,1);
1835
1836 hr_utility.set_location('p_payroll_action_id......= '|| p_payroll_action_id, 5);
1837 hr_utility.set_location('p_effective_date.........= '|| to_char(p_effective_date,'DD-MON-YYYY'), 7);
1838
1839 hr_utility.set_location('Getting Payroll ID', 10);
1840
1841 open csr_get_payroll_id(p_payroll_action_id);
1842 fetch csr_get_payroll_id into l_payroll_id;
1843 close csr_get_payroll_id;
1844
1845 hr_utility.set_location('p_payroll_id......= '|| l_payroll_id, 15);
1846 hr_utility.set_location('Archiving Employer''s Addr', 20);
1847
1848 pay_emp_action_arch.arch_pay_action_level_data
1849 (p_payroll_action_id => p_payroll_action_id
1850 ,p_payroll_id => l_payroll_id
1851 ,p_effective_date => p_effective_date);
1852
1853
1854 hr_utility.set_location('Leaving '|| l_procedure,1000);
1855 exception
1856 when others
1857 then
1858 hr_utility.set_location('Error in ' || l_procedure, 999999);
1859 raise;
1860 END insert_quickpay_pa_info;
1861
1862 ----------------------------
1863 --
1864 -- archive_quickpay_pa_info
1865 --
1866 -- Added for Bug #15914996, test if PA level data has been archive and if not,
1867 -- it will be archived.
1868 --
1869 ----------------------------
1870
1871 procedure archive_quickpay_pa_info
1872 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
1873 ,p_effective_date in pay_payroll_actions.effective_date%type
1874 ) is
1875
1876 cursor csr_get_payroll_action_id(
1877 p_aa_id pay_assignment_actions.assignment_action_id%TYPE)
1878 is
1879 select payroll_action_id
1880 from pay_assignment_actions paa
1881 where paa.assignment_action_id = p_aa_id;
1882
1883 cursor csr_is_employer_addr_archived(
1884 p_payroll_action_id pay_action_information.action_context_id%TYPE
1885 , p_effective_date pay_action_information.effective_date%TYPE)
1886 is
1887 select count(*)
1888 from pay_action_information pai
1889 where pai.action_context_type = 'PA'
1890 and pai.action_information_category = 'ADDRESS DETAILS'
1891 and pai.action_information14 = 'Employer Address'
1892 and pai.action_context_id = p_payroll_action_id
1893 and pai.effective_date = p_effective_date;
1894
1895 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
1896 l_is_employer_addr_archived number;
1897
1898 l_procedure varchar2(200);
1899
1900 begin
1901
1902 l_procedure := g_package||'chk_pay_level_info_archived';
1903 hr_utility.set_location('Entering '||l_procedure,1);
1904
1905 hr_utility.set_location('Fetching payroll_action_id',10);
1906
1907 open csr_get_payroll_action_id(p_assignment_action_id);
1908 fetch csr_get_payroll_action_id into l_payroll_action_id;
1909 close csr_get_payroll_action_id;
1910
1911 hr_utility.set_location('Fetching csr_is_employer_addr_archived result',20);
1912 open csr_is_employer_addr_archived(l_payroll_action_id, p_effective_date);
1913 fetch csr_is_employer_addr_archived into l_is_employer_addr_archived;
1914 close csr_is_employer_addr_archived;
1915
1916 hr_utility.set_location('Checking if employer''s derailes is archived',30);
1917 if (l_is_employer_addr_archived = 0) then
1918 hr_utility.set_location('Payroll level data is not archived',40);
1919 insert_quickpay_pa_info
1920 (p_payroll_action_id => l_payroll_action_id
1921 ,p_effective_date => p_effective_date);
1922 end if;
1923
1924 hr_utility.set_location('Leaving '||l_procedure,1000);
1925
1926 exception
1927 when others
1928 then
1929 hr_utility.set_location('Error in ' || l_procedure, 999999);
1930 raise;
1931 end archive_quickpay_pa_info;
1932
1933 ----------------------------
1934 --
1935 --
1936 --
1937 ----------------------------
1938 procedure archive_code
1939 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
1940 ,p_effective_date in pay_payroll_actions.effective_date%type
1941 ) is
1942
1943 cursor get_payslip_aa(p_master_aa_id number)
1944 is
1945 select paa_arch_chd.assignment_action_id chld_arc_assignment_action_id,
1946 paa_pre.assignment_action_id pre_assignment_action_id,
1947 paa_run.assignment_action_id run_assignment_action_id,
1948 ppa_pre.effective_date pre_effective_date,
1949 paa_arch_chd.assignment_id,
1950 ppa_run.effective_date run_effective_date,
1951 ppa_run.date_earned run_date_earned,
1952 ptp.regular_payment_date, /* 5681819 */ptp.end_date period_end_date,
1953 ptp.time_period_id
1954 from pay_assignment_actions paa_arch_chd,
1955 pay_assignment_actions paa_arch_mst,
1956 pay_assignment_actions paa_pre,
1957 pay_action_interlocks pai_pre,
1958 pay_assignment_actions paa_run,
1959 pay_action_interlocks pai_run,
1960 pay_payroll_actions ppa_pre,
1961 pay_payroll_actions ppa_run,
1962 per_time_periods ptp
1963 where paa_arch_mst.assignment_action_id = p_master_aa_id
1964 and paa_arch_chd.source_action_id = paa_arch_mst.assignment_action_id
1965 and paa_arch_chd.payroll_action_id = paa_arch_mst.payroll_action_id
1966 and paa_arch_chd.assignment_id = paa_arch_mst.assignment_id
1967 and pai_pre.locking_action_id = paa_arch_mst.assignment_action_id
1968 and pai_pre.locked_action_id = paa_pre.assignment_action_id
1969 and pai_run.locking_action_id = paa_arch_chd.assignment_action_id
1970 and pai_run.locked_action_id = paa_run.assignment_action_id
1971 and ppa_pre.payroll_action_id = paa_pre.payroll_action_id
1972 and ppa_pre.action_type in ('P','U')
1973 and ppa_run.payroll_action_id = paa_run.payroll_action_id
1974 and ppa_run.action_type in ('R','Q')
1975 and ptp.payroll_id = ppa_run.payroll_id
1976 and ppa_run.date_earned between ptp.start_date
1977 and ptp.end_date
1978 -- Get the highest in sequence for this payslip
1979 and paa_run.action_sequence = (select max(paa_run2.action_sequence)
1980 from pay_assignment_actions paa_run2,
1981 pay_action_interlocks pai_run2
1982 where pai_run2.locking_action_id =
1983 paa_arch_chd.assignment_action_id
1984 and pai_run2.locked_action_id =
1985 paa_run2.assignment_action_id
1986 );
1987
1988 l_procedure varchar2(200);
1989
1990 begin
1991 l_procedure := g_package||'archive_code';
1992 hr_utility.set_location('Entering '||l_procedure,1);
1993
1994 hr_utility.set_location('p_assignment_action_id......= '|| p_assignment_action_id,10);
1995 hr_utility.set_location('p_effective_date............= '|| to_char(p_effective_date,'DD-MON-YYYY'),15);
1996 /*Bug#3363519 */
1997 pay_core_payslip_utils.generate_child_actions(p_assignment_action_id,
1998 p_effective_date);
1999
2000 hr_utility.set_location(l_procedure,20);
2001
2002 /* Added for Bug #15914996. If normal payroll run, or has been archived
2003 * before, this will not archive anything. */
2004 archive_quickpay_pa_info(p_assignment_action_id, p_effective_date);
2005
2006 hr_utility.set_location(l_procedure,30);
2007
2008 -- For each payslip to be generated
2009 for payslip_rec in get_payslip_aa(p_assignment_action_id) loop
2010 --
2011 hr_utility.set_location(l_procedure,40);
2012
2013 archive_employee_details
2014 (p_assignment_id => payslip_rec.assignment_id
2015 ,p_arc_assignment_action_id => payslip_rec.chld_arc_assignment_action_id -- archive run assignment action
2016 ,p_run_assignment_action_id => payslip_rec.run_assignment_action_id -- payroll run assignment action
2017 ,p_pre_assignment_action_id => payslip_rec.pre_assignment_action_id -- prepayment run assignment action
2018 ,p_pre_effective_date => payslip_rec.pre_effective_date -- prepayment run effective date
2019 ,p_run_effective_date => payslip_rec.run_effective_date -- payroll run effective_date
2020 ,p_run_date_earned => payslip_rec.run_date_earned -- payroll run date_earned
2021 ,p_time_period_id => payslip_rec.time_period_id -- time_period_id from per_time_periods
2022 ,p_period_end_date => payslip_rec.period_end_date -- end date from per_time_periods
2023 ,p_regular_payment_date => payslip_rec.regular_payment_date -- Regular payment date from per_time_periods /* 5681819 */
2024 );
2025
2026 hr_utility.set_location(l_procedure,50);
2027 --
2028 end loop;
2029
2030 hr_utility.set_location('Leaving '||l_procedure,1000);
2031
2032 exception
2033 when others then
2034 hr_utility.set_location('Error in '||l_procedure,999999);
2035 raise;
2036 end archive_code;
2037
2038 end pay_au_payslip_archive;