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