[Home] [Help]
PACKAGE BODY: APPS.PAY_AU_REC_DET_ARCHIVE
Source
1 package body pay_au_rec_det_archive as
2 /* $Header: pyaurecd.pkb 120.15 2008/03/21 13:13:44 avenkatk noship $*/
3 /*
4 *** ------------------------------------------------------------------------+
5 *** Program: pay_au_rec_det_archive (Package Body)
6 ***
7 *** Change History
8 ***
9 *** Date Changed By Version Bug No Description of Change
10 *** --------- ---------- ------- ------ --------------------------------+
11 *** 25 DEC 03 avenkatk 1.0 3064269 Initial Version
12 *** 29 DEC 03 avenkatk 1.1 3064269 Changed cursor c_element_details
13 *** 08 JAN 04 avenkatk 1.2 3064269 Modified spawn_archive_reports.
14 *** and cursors.
15 *** 09 JAN 04 avenkatk 1.3 3064269 Modified assignment_action_code
16 *** to archive action_sequence.
17 *** 25 MAR 04 avenkatk 1.4 3531704 Modified Cursor c_payment_summary_details
18 *** Added private function get_fin_year_code
19 *** 25 MAR 04 avenkatk 1.5 3531704 Removed to_char for compatibility with
20 *** 8i DB.
21 *** 16 APR 04 punmehta 1.6 3538810 Modified cursors to include rehire cases
22 *** 16 APR 04 punmehta 1.7 3538810 Modified for GSCC standards
23 *** 16 APR 04 punmehta 1.8 3538810 Modified for GSCC standards
24 *** 18 APR 04 punmehta 1.9 3538810 Removed initialization done in BEGIN of packge and added NVL
25 *** to global variables g_prev_assignment_id , g_def_bal_populted
26 *** 29 APR 04 avenkatk 1.10 3598558 Modified cursor csr_assignment_payroll_period to
27 *** pick runs when an assignment update is done.
28 *** 11 MAY 04 punmehta 1.11 3622295 Replaced Assignmetn_status_type_id check with per_system_Status check
29 *** 13 MAY 04 avenkatk 1.12 3627293 Modified Balances archival - Archive values only for Master action ID.
30 *** 07 JUN 04 abhkumar 1.13 3662449 Modfied the archive and assignment code to include assignment statuses SUSPEND and END.
31 *** 09 AUG 04 abhkumar 1.17 2610141 Legal Employer enhancement Changes.
32 *** 05 OCT 04 ksingla 1.18 3953702 Modified cursor c_employee_details for Grade display - Archived Grade Name
33 *** fetched from table per_grades_tl
34 *** 06 DEC 04 abhkumar 1.19 3953706 Mainline fix for Earnings Reporting enhancement.
35 *** 06 DEC 04 srrajago 1.20 4045910 Modified the cursor 'c_element_details' to handle the issue raised when user-defined secondary classification
36 *** is attached to an element (Distinct clause introduced).
37 *** 13 DEC 04 JLin 1.21 3953615 Added the call pay_au_reconciliation.check_report_parameters to
38 *** spawn_archive_reports to check the validation for the parameters
39 *** 29-DEC-04 abhkumar 1.22 4040688 Modified CURSOR csr_assignment_payroll_period so that
40 *** assignment IS reported FOR the latest payroll AS ON END DATE OF report.
41 *** Modified archive_code TO call YTD balances ONLY FOR the last RUN IN the period
42 *** Modified CURSOR c_employee_details TO FETCH the latest Legal Employer OF the assignment
43 *** 30-DEC-04 abhkumar 1.23 4040688 Modified the cursor csr_get_max_asg_dates and csr_get_max_asg_action to improve performance.
44 *** Modified the logic of archiving YTD balances. Two new contexts introduced
45 *** AU_BALANCE_RECON_DETAILS_RUN and AU_BALANCE_RECON_DETAILS_YTD.
46 *** 06-JAN-05 abhkumar 1.24 4099317 Modified assignment CURSOR - added CHECK OF action_status = 'C'
47 *** Modified element details cursor - added CHECK OF action_status = 'C' on
48 *** pay_assignment_actions table
49 *** 13-JAN-05 avenkatk 1.25 4116833 Set the Report Request number of copies to be read from Archive Request.
50 *** 21-JAN-05 abhkumar 1.26 4132525 Modified cursor c_employee_details to archive organization name, payroll name and Legal Employer
51 *** name. Sorting of records in reports to be done on basis of Org. names, Payroll names, Legal Employer names.
52 *** 25-JAN-05 abhkumar 1.27 4142159 Introduced Parameter P_DELETE_ACTIONS.
53 *** 08-FEB-05 ksingla 1.28 4161540 Modified cursors csr_assignment_org_period , csr_assignment_legal_period,
54 *** csr_assignment_payroll_period ,csr_assignment_period ,csr_assignment_default_period.
55 *** Removed join for per_assignment_status_types.
56 *** 09-FEB-05 ksingla 1.29 4161540 Modified cursors csr_assignment_org_period , csr_assignment_legal_period,
57 *** csr_assignment_payroll_period ,csr_assignment_period ,csr_assignment_default_period.
58 *** Modified the subquery for the check of termination in the same way as in pay_au_payment_summary .
59 *** 11-FEB-05 abhkumar 1.30 4132149 Modified initialisation_code to initialise the global variables for legislative parameters.
60 *** 13-APR-2005 abhkumar 1.31 3935471 Modified element_detail cursor to get the tax unit id of master assignment action id.
61 *** 05-MAY-2005 abhkumar 1.32 3935471 Modified file to put proper comments.
62 *** 25 May 05 abhkumar 1.33 4688872 Modified assignment action code to fix for cases where employee has a nulled payroll
63 *** at the end of year
64 *** 27 Feb 06 ksingla 1.34 5063359 Modified Cursor c_element_details for employer charges
65 *** 19 Oct 06 ksingla 1.35 5461557 Modified cursor c_element_details to get rate and hours and group by on rate.
66 *** 29-Oct-06 hnainani 1.36 5603254 Added Function get_element_payment_hours to fetch hours in c_element_details.
67 *** 16-Nov-06 abhargav 115.40 5603254 Modified cursor c_element_details to remove joins for pay_input_values_f piv2 and pay_run_result_values prrv2.
68 *** 13-Feb-06 priupadh 115.41 N/A Version for restoring Triple Maintanence between 11i-->R12(Branch) -->R12(MainLine)
69 *** 02-MAR-07 hnainani 1.42 5599310 Added Function get_element_payment_rate to fetch rate in c_element_details.
70 -- 13-MAR-07 hnainani 115.43 5599310 Added Debug messages to function get_element_payment_rate
71 ***11-Jun-07 vamittal 115.44 6109668 Modified cursor get_rate_input_value in function get_element_payment_rate
72 *** to fetch the rate input having UOM as Number from input value
73 ***29-Jun-07 vamittal 115.45 6109668 Modified cursor get_rate_input_value in function get_element_payment_rate
74 *** to fetch the rate input having UOM as Number or Money or Integer from input value
75 ***02-Aug-07 skshin 115.46 5987877 Added check in Function get_element_payment_hours for multiple Hours Input
76 ***26-Feb-08 vdabgar 115.47 6839263 Modified proc spawn_archive_reports,csr_params and csr_report_params cursors
77 *** to call the concurrent programs accordingly.
78 ***18-Mar-08 avenkatk 115.48 6839263 Backed out changes from assignment_action_code, initialization_code
79 ***21-Mar-08 avenkatk 115.49 6839263 Added Logic to set the OPP Template options for PDF output
80 *** ------------------------------------------------------------------------+
81 */
82
83 g_arc_payroll_action_id pay_payroll_actions.payroll_action_id%type;
84 g_business_group_id hr_all_organization_units.organization_id%type;
85 g_prev_assignment_id number;
86 g_def_bal_populted varchar2(1);
87
88 g_debug boolean ;
89
90 g_package constant varchar2(60) := 'pay_au_recon_det_archive.'; -- Global to store package name for tracing.
91 g_end_date date;
92 g_start_date date; --Bug#3662449
93
94 --------------------------------------------------------------------
95 -- Name : range_code
96 -- Type : Proedure
97 -- Access: Public
98 -- This procedure returns a sql string to select a range
99 -- of assignments eligible for archival.
100 --
101 --------------------------------------------------------------------
102
103 procedure range_code
104 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type
105 ,p_sql out NOCOPY varchar2
106 ) is
107
108 l_procedure varchar2(200) ;
109
110 begin
111
112 g_debug :=hr_utility.debug_enabled ;
113
114 if g_debug then
115 l_procedure := g_package||'range_code';
116 hr_utility.set_location('Entering '||l_procedure,1);
117 end if ;
118
119 -- Archive the payroll action level data and EIT defintions.
120 -- sql string to SELECT a range of assignments eligible for archival.
121 p_sql := ' select distinct p.person_id' ||
122 ' from per_people_f p,' ||
123 ' pay_payroll_actions pa' ||
124 ' where pa.payroll_action_id = :payroll_action_id' ||
125 ' and p.business_group_id = pa.business_group_id' ||
126 ' order by p.person_id';
127
128 if g_debug then
129 hr_utility.set_location('Leaving '||l_procedure,1000);
130 end if;
131
132 end range_code;
133
134 --------------------------------------------------------------------+
135 -- Name : check_termination
136 -- Type : function
137 -- Access: Public
138 -- This function is to return the assignment status
139 --------------------------------------------------------------------+
140 /*Bug#3662449 function added to check for assignment status*/
141 function check_termination
142 (p_sys_status per_assignment_status_types.per_system_status%TYPE,
143 p_emp_type varchar2)
144 return varchar2
145 is
146 l_status varchar2(10);
147 begin
148 l_status := 'FALSE';
149 if p_emp_type = 'Y' and p_sys_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN') THEN
150 l_status := 'TRUE';
151 elsif p_emp_type = 'N' and p_sys_status = 'TERM_ASSIGN' THEN
152 l_status := 'TRUE';
153 elsif p_emp_type = '%' and p_sys_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN','TERM_ASSIGN') THEN
154 l_status := 'TRUE';
155 END IF;
156 return l_status;
157
158 end check_termination;
159
160
161 --------------------------------------------------------------------+
162 -- Name : initialization_code
163 -- Type : Proedure
164 -- Access: Public
165 -- This procedure is used to set global contexts
166 --------------------------------------------------------------------+
167
168 procedure initialization_code
169 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type)
170 is
171 l_procedure varchar2(200) ;
172
173 /*Bug 4132149 - Modification begins here*/
174 --------------------------------------------------------------------+
175 -- Cursor : csr_params
176 -- Description : Fetches User Parameters from Legislative_paramters
177 -- column.
178 --------------------------------------------------------------------+
179
180 CURSOR csr_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
181 IS
182 SELECT pay_core_utils.get_parameter('PAY',legislative_parameters) payroll_id,
183 pay_core_utils.get_parameter('ORG',legislative_parameters) org_id,
184 pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
185 to_date(pay_core_utils.get_parameter('SDATE',legislative_parameters),'YYYY/MM/DD') start_date,
189 pay_core_utils.get_parameter('ASG',legislative_parameters) assignment_id,
186 to_date(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD') end_date,
187 pay_core_utils.get_parameter('PACTID',legislative_parameters) pact_id,
188 pay_core_utils.get_parameter('LE',legislative_parameters) legal_employer,
190 pay_core_utils.get_parameter('SO1',legislative_parameters) sort_order_1,
191 pay_core_utils.get_parameter('SO2',legislative_parameters) sort_order_2,
192 pay_core_utils.get_parameter('SO3',legislative_parameters) sort_order_3,
193 pay_core_utils.get_parameter('SO4',legislative_parameters) sort_order_4,
194 to_date(pay_core_utils.get_parameter('PEDATE',legislative_parameters),'YYYY/MM/DD') period_end_date,
195 pay_core_utils.get_parameter('YTD_TOT',legislative_parameters) ytd_totals,
196 pay_core_utils.get_parameter('ZERO_REC',legislative_parameters) zero_records,
197 pay_core_utils.get_parameter('NEG_REC',legislative_parameters) negative_records,
198 decode(pay_core_utils.get_parameter('EMP_TYPE',legislative_parameters),'C','Y','T','N','%') employee_type,
199 pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions /*Bug# 4142159*/
200 FROM pay_payroll_actions ppa
201 WHERE ppa.payroll_action_id = c_payroll_action_id;
202
203 --------------------------------------------------------------------+
204 -- Cursor : csr_period_date_earned
205 -- Description : Fetches Date Earned for a given payroll
206 -- run.
207 --------------------------------------------------------------------+
208 CURSOR csr_period_date_earned(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
209 IS
210 SELECT ppa.date_earned
211 FROM pay_payroll_actions ppa
212 WHERE
213 ppa.payroll_action_id = c_payroll_action_id;
214
215 /*Bug 4132149 - Modification ends here*/
216
217 begin
218
219 g_debug :=hr_utility.debug_enabled ;
220 if g_debug then
221 l_procedure := g_package||'initialization_code';
222 hr_utility.set_location('Entering '||l_procedure,1);
223 end if;
224
225
226 /*Bug 4132149 - Modification begins here*/
227
228 -- initialization_code to to set the global tables for EIT
229 -- that will be used by each thread in multi-threading.
230
231 g_arc_payroll_action_id := p_payroll_action_id;
232
233 -- Fetch the parameters by user passed into global variable.
234
235 OPEN csr_params(p_payroll_action_id);
236 FETCH csr_params into g_parameters;
237 CLOSE csr_params;
238
239
240 if g_debug then
241 hr_utility.set_location('p_payroll_action_id.........= ' || p_payroll_action_id,30);
242 hr_utility.set_location('g_parameters.business_group_id.........= ' || g_parameters.business_group_id,30);
243 hr_utility.set_location('g_parameters.payroll_id..............= ' || g_parameters.payroll_id,30);
244 hr_utility.set_location('g_parameters.org_id................= ' || g_parameters.org_id,30);
245 hr_utility.set_location('g_parameters.legal_employer.........= ' || g_parameters.legal_employer,30);
246 hr_utility.set_location('g_parameters.start_date..............= ' || g_parameters.start_date,30);
247 hr_utility.set_location('g_parameters.end_date................= ' || g_parameters.end_date,30);
248 hr_utility.set_location('g_parameters.period_end_date.........= ' || g_parameters.period_end_date,30);
249 hr_utility.set_location('g_parameters.pact_id..............= ' || g_parameters.pact_id,30);
250 hr_utility.set_location('g_parameters.employee_type..........= '||g_parameters.employee_type,30);
251 hr_utility.set_location('g_parameters.sort_order1..........= '||g_parameters.sort_order_1,30);
252 hr_utility.set_location('g_parameters.sort_order2..........= '||g_parameters.sort_order_2,30);
253 hr_utility.set_location('g_parameters.sort_order3..........= '||g_parameters.sort_order_3,30);
254 hr_utility.set_location('g_parameters.sort_order4..........= '||g_parameters.sort_order_4,30);
255 hr_utility.set_location('g_parameters.delete_actions..........= '||g_parameters.delete_actions,30);/*Bug# 4142159*/
256 end if;
257
258
259 g_business_group_id := g_parameters.business_group_id ;
260
261 -- Set end date variable .This value is used to fetch latest assignment details of
262 -- employee for archival.In case of archive start date/end date - archive end date
263 -- taken and pact_id/period_end_date , period end date is picked.
264
265 if g_parameters.end_date is not null
266 then
267 g_end_date := g_parameters.end_date;
268 g_start_date := g_parameters.start_date;
269 else
270 if g_parameters.period_end_date is not null
271 then
272 open csr_period_date_earned(g_parameters.pact_id);
273 fetch csr_period_date_earned into g_start_date;
274 close csr_period_date_earned;
275 g_end_date := g_parameters.period_end_date;
276 else
277 g_start_date := to_date('1900/01/01','YYYY/MM/DD');
278 g_end_date := to_date('4712/12/31','YYYY/MM/DD');
279 end if;
280 end if; /* End of outer if loop */
281
282 /*Bug 4132149 - Modification ends here*/
283
284 pay_au_reconciliation_pkg.populate_defined_balance_ids('Y',g_parameters.legal_employer);
285
289
286 if g_debug then
287 hr_utility.set_location('Leaving '||l_procedure,1000);
288 end if;
290 exception
291 when others then
292 hr_utility.set_location('Error in '||l_procedure,999999);
293 raise;
294 end initialization_code;
295
296 --------------------------------------------------------------------+
297 -- Name : assignment_Action_code
298 -- Type : Procedure
299 -- Access: Public
300 -- This procedure further restricts the assignment_id's
301 -- returned by range_code
302 -- This procedure gets the parameters given by user and restricts
303 -- the assignments to be archived.
304 -- it then calls hr_nonrun.insact to create an assignment action id
305 -- it then archives Payroll Run assignment action id details
306 -- in pay_Action_information with context 'AU_ARCHIVE_ASG_DETAILS'
307 -- for each assignment.
308 -- There are 10 different cursors for choosing the assignment ids.
309 -- Depending on the parameters passed,the appropriate cursor is used.
310 --------------------------------------------------------------------+
311
312 procedure assignment_action_code
313 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type
314 ,p_start_person in per_all_people_f.person_id%type
315 ,p_end_person in per_all_people_f.person_id%type
316 ,p_chunk in number
317 ) is
318
319 --------------------------------------------------------------------+
320 -- Cursor : csr_assignment_org_period
321 -- Description : Fetches assignments when Organization,Archive
322 -- Start Date and End Date is specified
323 --------------------------------------------------------------------+
324
325 cursor csr_assignment_org_period
326 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
327 ,c_start_person per_all_people_f.person_id%type
328 ,c_end_person per_all_people_f.person_id%type
329 ,c_employee_type per_all_people_f.current_employee_flag%type
330 ,c_business_group_id hr_all_organization_units.organization_id%type
331 ,c_organization_id hr_all_organization_units.organization_id%type
332 ,c_archive_start_date date
333 ,c_archive_end_date date
334 ) is
335 select paa.assignment_action_id,
336 paa.action_sequence,
337 paaf.assignment_id,
338 paa.tax_unit_id
339 from per_people_f pap,
340 per_assignments_f paaf,
341 pay_payroll_actions ppa,
342 pay_payroll_actions ppa1,
343 pay_assignment_actions paa,
344 hr_organization_units hou,
345 per_periods_of_service pps
346 -- ,per_assignment_status_types past
347 where ppa.payroll_action_id = c_payroll_action_id
348 and paa.assignment_id = paaf.assignment_id
349 and pap.person_id between c_start_person and c_end_person
350 and pap.person_id = paaf.person_id
351 and pap.person_id = pps.person_id
352 and pps.period_of_service_id = paaf.period_of_service_id
353 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
354 and ppa1.payroll_action_id = paa.payroll_action_id
355 AND paa.action_status = 'C' /*Bug 4099317*/
356 and ppa1.business_group_id = ppa.business_group_id
357 and ppa.business_group_id = c_business_group_id
358 and ppa1.action_type in ('R','Q','I','B','V')
359 and paaf.organization_id = hou.organization_id
360 and hou.business_group_id = c_business_group_id
361 and hou.organization_id = c_organization_id
362 and ppa1.effective_date between c_archive_start_date and c_archive_end_date
363 and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type --4161540
364 and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
365 From per_assignments_f iipaf
366 WHERE iipaf.assignment_id = paaf.assignment_id
367 and iipaf.effective_end_date >= c_archive_start_date
368 and iipaf.effective_start_date <= c_archive_end_date)
369 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
370
371
372 --------------------------------------------------------------------+
373 -- Cursor : csr_assignment_org_run
374 -- Description : Fetches assignments when Organization,Payroll Run
375 -- and Period End Date is specified
376 --------------------------------------------------------------------+
377
378 cursor csr_assignment_org_run
379 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
380 ,c_start_person per_all_people_f.person_id%type
381 ,c_end_person per_all_people_f.person_id%type
382 ,c_employee_type per_all_people_f.current_employee_flag%type
383 ,c_business_group_id hr_all_organization_units.organization_id%type
384 ,c_organization_id hr_all_organization_units.organization_id%type
385 ,c_period_end_date date
386 ,c_pact_id pay_payroll_actions.payroll_action_id%type
387 ) is
388 select paa.assignment_action_id,
389 paa.action_sequence,
390 paaf.assignment_id,
391 paa.tax_unit_id
395 pay_payroll_actions ppa1,
392 from per_people_f pap,
393 per_assignments_f paaf,
394 pay_payroll_actions ppa,
396 pay_assignment_actions paa,
397 hr_organization_units hou,
398 per_periods_of_service pps
399 where ppa.payroll_action_id = c_payroll_action_id
400 and paa.assignment_id = paaf.assignment_id
401 and pap.person_id between c_start_person and c_end_person
402 and pap.person_id = paaf.person_id
403 and pap.person_id = pps.person_id
404 and pps.period_of_service_id = paaf.period_of_service_id
405 and ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
406 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
407 and ppa1.payroll_action_id = paa.payroll_action_id
408 AND paa.action_status = 'C' /*Bug 4099317*/
409 and ppa1.business_group_id = ppa.business_group_id
410 and ppa.business_group_id = c_business_group_id
411 and ppa1.action_type in ('R','Q','I','B','V')
412 and paaf.organization_id = hou.organization_id
413 and hou.business_group_id = c_business_group_id
414 and NVL(pap.current_employee_flag,'N') like c_employee_type
415 and hou.organization_id = c_organization_id
416 and ppa1.payroll_action_id = c_pact_id
417 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
418
419 --------------------------------------------------------------------+
420 -- Cursor : csr_assignment_legal_period
421 -- Description : Fetches assignments when Legal Employer,Archive
422 -- Start Date and End Date is specified
423 --------------------------------------------------------------------+
424
425 cursor csr_assignment_legal_period
426 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
427 ,c_start_person per_all_people_f.person_id%type
428 ,c_end_person per_all_people_f.person_id%type
429 ,c_employee_type per_all_people_f.current_employee_flag%type
430 ,c_business_group_id hr_all_organization_units.organization_id%type
431 ,c_legal_employer hr_all_organization_units.organization_id%type
432 ,c_archive_start_date date
433 ,c_archive_end_date date
434 ) is
435 select paa.assignment_action_id,
436 paa.action_sequence,
437 paaf.assignment_id,
438 paa.tax_unit_id
439 from per_people_f pap,
440 per_assignments_f paaf,
441 pay_payroll_actions ppa,
442 pay_payroll_actions ppa1,
443 pay_assignment_actions paa,
444 per_periods_of_service pps
445 -- ,per_assignment_status_types past
446 where ppa.payroll_action_id = c_payroll_action_id
447 and paa.assignment_id = paaf.assignment_id
448 and pap.person_id between c_start_person and c_end_person
449 and pap.person_id = paaf.person_id
450 and pap.person_id = pps.person_id
451 and pps.period_of_service_id = paaf.period_of_service_id
452 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
453 and ppa1.payroll_action_id = paa.payroll_action_id
454 AND paa.action_status = 'C' /*Bug 4099317*/
455 and ppa1.business_group_id = ppa.business_group_id
456 and ppa.business_group_id = c_business_group_id
457 and ppa1.action_type in ('R','Q','I','B','V')
458 and paa.tax_unit_id = c_legal_employer
459 and ppa1.effective_date between c_archive_start_date and c_archive_end_date
460 and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type --bug 4161540
461 and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
462 From per_assignments_f iipaf
463 WHERE iipaf.assignment_id = paaf.assignment_id
464 and iipaf.effective_end_date >= c_archive_start_date
465 and iipaf.effective_start_date <= c_archive_end_date)
466 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
467
468
469 --------------------------------------------------------------------+
470 -- Cursor : csr_assignment_legal_run
471 -- Description : Fetches assignments when Legal Employer,Payroll Run
472 -- and Period End Date is specified
473 --------------------------------------------------------------------+
474
475
476 cursor csr_assignment_legal_run
477 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
478 ,c_start_person per_all_people_f.person_id%type
479 ,c_end_person per_all_people_f.person_id%type
480 ,c_employee_type per_all_people_f.current_employee_flag%type
481 ,c_business_group_id hr_all_organization_units.organization_id%type
482 ,c_legal_employer hr_all_organization_units.organization_id%type
483 ,c_period_end_date date
484 ,c_pact_id pay_payroll_actions.payroll_action_id%type
485 ) is
486 select paa.assignment_action_id,
487 paa.action_sequence,
488 paaf.assignment_id,
489 paa.tax_unit_id
490 from per_people_f pap,
491 per_assignments_f paaf,
495 per_periods_of_service pps
492 pay_payroll_actions ppa,
493 pay_payroll_actions ppa1,
494 pay_assignment_actions paa,
496 where ppa.payroll_action_id = c_payroll_action_id
497 and paa.assignment_id = paaf.assignment_id
498 and pap.person_id between c_start_person and c_end_person
499 and pap.person_id = paaf.person_id
500 and pap.person_id = pps.person_id
501 and pps.period_of_service_id = paaf.period_of_service_id
502 and ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
503 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
504 and ppa1.payroll_action_id = paa.payroll_action_id
505 AND paa.action_status = 'C' /*Bug 4099317*/
506 and ppa1.business_group_id = ppa.business_group_id
507 and ppa.business_group_id = c_business_group_id
508 and ppa1.action_type in ('R','Q','I','B','V')
509 and NVL(pap.current_employee_flag,'N') like c_employee_type
510 and paa.tax_unit_id = c_legal_employer
511 and ppa1.payroll_action_id = c_pact_id
512 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
513
514 --------------------------------------------------------------------+
515 -- Cursor : csr_assignment_payroll_period
516 -- Description : Fetches assignments when Payroll,Archive Start
517 -- Date and End Date is specified
518 --------------------------------------------------------------------+
519
520 cursor csr_assignment_payroll_period
521 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
522 ,c_start_person per_all_people_f.person_id%type
523 ,c_end_person per_all_people_f.person_id%type
524 ,c_employee_type per_all_people_f.current_employee_flag%type
525 ,c_business_group_id hr_all_organization_units.organization_id%type
526 ,c_payroll_id pay_payroll_actions.payroll_id%type
527 ,c_archive_start_date date
528 ,c_archive_end_date date
529 ) is
530 select paa.assignment_action_id,
531 paa.action_sequence,
532 paaf.assignment_id,
533 paa.tax_unit_id
534 from per_people_f pap,
535 per_assignments_f paaf,
536 pay_payroll_actions ppa,
537 pay_payroll_actions ppa1,
538 pay_assignment_actions paa,
539 per_periods_of_service pps
540 -- per_assignment_status_types past
541 where ppa.payroll_action_id = c_payroll_action_id
542 and paa.assignment_id = paaf.assignment_id
543 and pap.person_id between c_start_person and c_end_person
544 and pap.person_id = paaf.person_id
545 and pap.person_id = pps.person_id
546 and pps.period_of_service_id = paaf.period_of_service_id
547 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
548 and ppa1.payroll_action_id = paa.payroll_action_id
549 AND paa.action_status = 'C' /*Bug 4099317*/
550 and ppa1.business_group_id = ppa.business_group_id
551 and ppa.business_group_id = c_business_group_id
552 and ppa1.action_type in ('R','Q','I','B','V')
553 and ppa1.effective_date between c_archive_start_date and c_archive_end_date
554 AND paaf.payroll_id = c_payroll_id /*Bug 4040688*/
555 and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type --Bug 4161540
556 and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
557 From per_assignments_f iipaf
558 WHERE iipaf.assignment_id = paaf.assignment_id
559 and iipaf.effective_end_date >= c_archive_start_date
560 and iipaf.effective_start_date <= c_archive_end_date
561 AND iipaf.payroll_id IS NOT NULL) /*Bug 4688872*/
562 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
563
564 --------------------------------------------------------------------+
565 -- Cursor : csr_assignment_payroll_run
566 -- Description : Fetches assignments when Payroll,Payroll Run
567 -- and Period End Date is specified
568 --------------------------------------------------------------------+
569
570 cursor csr_assignment_payroll_run
571 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
572 ,c_start_person per_all_people_f.person_id%type
573 ,c_end_person per_all_people_f.person_id%type
574 ,c_employee_type per_all_people_f.current_employee_flag%type
575 ,c_business_group_id hr_all_organization_units.organization_id%type
576 ,c_payroll_id pay_payroll_actions.payroll_id%type
577 ,c_period_end_date date
578 ,c_pact_id pay_payroll_actions.payroll_action_id%type
579 ) is
580 select paa.assignment_action_id,
581 paa.action_sequence,
582 paaf.assignment_id,
583 paa.tax_unit_id
584 from per_people_f pap,
585 per_assignments_f paaf,
586 pay_payroll_actions ppa,
587 pay_payroll_actions ppa1,
588 pay_assignment_actions paa,
589 per_periods_of_service pps
593 and pap.person_id = paaf.person_id
590 where ppa.payroll_action_id = c_payroll_action_id
591 and paa.assignment_id = paaf.assignment_id
592 and pap.person_id between c_start_person and c_end_person
594 and pap.person_id = pps.person_id
595 and pps.period_of_service_id = paaf.period_of_service_id
596 and ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
597 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
598 and ppa1.payroll_action_id = paa.payroll_action_id
599 AND paa.action_status = 'C' /*Bug 4099317*/
600 and ppa1.business_group_id = ppa.business_group_id
601 and ppa.business_group_id = c_business_group_id
602 and ppa1.action_type in ('R','Q','I','B','V')
603 and NVL(pap.current_employee_flag,'N') like c_employee_type
604 and ppa1.payroll_id = c_payroll_id
605 and ppa1.payroll_action_id = c_pact_id
606 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
607
608
609 --------------------------------------------------------------------+
610 -- Cursor : csr_assignment_period
611 -- Description : Fetches assignments when Assignment,Archive Start
612 -- Date and End Date is specified
613 --------------------------------------------------------------------+
614
615 cursor csr_assignment_period
616 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
617 ,c_start_person per_all_people_f.person_id%type
618 ,c_end_person per_all_people_f.person_id%type
619 ,c_employee_type per_all_people_f.current_employee_flag%type
620 ,c_business_group_id hr_all_organization_units.organization_id%type
621 ,c_assignment_id per_all_assignments_f.assignment_id%type
622 ,c_archive_start_date date
623 ,c_archive_end_date date
624 ) is
625 select paa.assignment_action_id,
626 paa.action_sequence,
627 paaf.assignment_id,
628 paa.tax_unit_id
629 from per_people_f pap,
630 per_assignments_f paaf,
631 pay_payroll_actions ppa,
632 pay_payroll_actions ppa1,
633 pay_assignment_actions paa,
634 per_periods_of_service pps
635 -- ,per_assignment_status_types past
636 where ppa.payroll_action_id = c_payroll_action_id
637 and paa.assignment_id = paaf.assignment_id
638 and pap.person_id between c_start_person and c_end_person
639 and pap.person_id = paaf.person_id
640 and pap.person_id = pps.person_id
641 and pps.period_of_service_id = paaf.period_of_service_id
642 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
643 and ppa1.payroll_action_id = paa.payroll_action_id
644 AND paa.action_status = 'C' /*Bug 4099317*/
645 and ppa1.business_group_id = ppa.business_group_id
646 and ppa.business_group_id = c_business_group_id
647 and ppa1.action_type in ('R','Q','I','B','V')
648 and paa.assignment_id = c_assignment_id
649 and ppa1.effective_date between c_archive_start_date and c_archive_end_date
650 and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type --Bug 4161540
651 and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
652 From per_assignments_f iipaf
653 WHERE iipaf.assignment_id = paaf.assignment_id
654 and iipaf.effective_end_date >= c_archive_start_date
655 and iipaf.effective_start_date <= c_archive_end_date)
656 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
657
658 -------------------------------------------------------------------+
659 -- Cursor : csr_assignment_run
660 -- Description : Fetches assignments when Assignment,Payroll Run
661 -- and Period End Date is specified
662 --------------------------------------------------------------------+
663
664 cursor csr_assignment_run
665 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
666 ,c_start_person per_all_people_f.person_id%type
667 ,c_end_person per_all_people_f.person_id%type
668 ,c_employee_type per_all_people_f.current_employee_flag%type
669 ,c_business_group_id hr_all_organization_units.organization_id%type
670 ,c_assignment_id per_all_assignments_f.assignment_id%type
671 ,c_period_end_date date
672 ,c_pact_id pay_payroll_actions.payroll_action_id%type
673 ) is
674 select paa.assignment_action_id,
675 paa.action_sequence,
676 paaf.assignment_id,
677 paa.tax_unit_id
678 from per_people_f pap,
679 per_assignments_f paaf,
680 pay_payroll_actions ppa,
681 pay_payroll_actions ppa1,
682 pay_assignment_actions paa,
683 per_periods_of_service pps
684 where ppa.payroll_action_id = c_payroll_action_id
685 and paa.assignment_id = paaf.assignment_id
686 and pap.person_id between c_start_person and c_end_person
687 and pap.person_id = paaf.person_id
688 and pap.person_id = pps.person_id
689 and pps.period_of_service_id = paaf.period_of_service_id
693 AND paa.action_status = 'C' /*Bug 4099317*/
690 and ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
691 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
692 and ppa1.payroll_action_id = paa.payroll_action_id
694 and ppa1.business_group_id = ppa.business_group_id
695 and ppa.business_group_id = c_business_group_id
696 and ppa1.action_type in ('R','Q','I','B','V')
697 and NVL(pap.current_employee_flag,'N') like c_employee_type
698 and paa.assignment_id = c_assignment_id
699 and ppa1.payroll_action_id = c_pact_id
700 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
701
702 --------------------------------------------------------------------+
703 -- Cursor : csr_assignment_default_period
704 -- Description : Fetches assignments when Archive Start date
705 -- and End Date is specified
706 --------------------------------------------------------------------+
707
708 cursor csr_assignment_default_period
709 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
710 ,c_start_person per_all_people_f.person_id%type
711 ,c_end_person per_all_people_f.person_id%type
712 ,c_employee_type per_all_people_f.current_employee_flag%type
713 ,c_business_group_id hr_all_organization_units.organization_id%type
714 ,c_archive_start_date date
715 ,c_archive_end_date date
716 ) is
717 select paa.assignment_action_id,
718 paa.action_sequence,
719 paaf.assignment_id,
720 paa.tax_unit_id
721 from per_people_f pap,
722 per_assignments_f paaf,
723 pay_payroll_actions ppa,
724 pay_payroll_actions ppa1,
725 pay_assignment_actions paa,
726 per_periods_of_service pps
727 -- ,per_assignment_status_types past
728 where ppa.payroll_action_id = c_payroll_action_id
729 and paa.assignment_id = paaf.assignment_id
730 and pap.person_id between c_start_person and c_end_person
731 and pap.person_id = paaf.person_id
732 and pap.person_id = pps.person_id
733 and pps.period_of_service_id = paaf.period_of_service_id
734 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
735 and ppa1.payroll_action_id = paa.payroll_action_id
736 AND paa.action_status = 'C' /*Bug 4099317*/
737 and ppa1.business_group_id = ppa.business_group_id
738 and ppa.business_group_id = c_business_group_id
739 and ppa1.action_type in ('R','Q','I','B','V')
740 and ppa1.effective_date between c_archive_start_date and c_archive_end_date
741 and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type --Bug 4161540
742 and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
743 From per_assignments_f iipaf
744 WHERE iipaf.assignment_id = paaf.assignment_id
745 and iipaf.effective_end_date >= c_archive_start_date
746 and iipaf.effective_start_date <= c_archive_end_date)
747 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
748
749 --------------------------------------------------------------------+
750 -- Cursor : csr_assignment_default_run
751 -- Description : Fetches assignments when Payroll Run
752 -- and Period End Date is specified
753 --------------------------------------------------------------------+
754
755 cursor csr_assignment_default_run
756 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
757 ,c_start_person per_all_people_f.person_id%type
758 ,c_end_person per_all_people_f.person_id%type
759 ,c_employee_type per_all_people_f.current_employee_flag%type
760 ,c_business_group_id hr_all_organization_units.organization_id%type
761 ,c_period_end_date date
762 ,c_pact_id pay_payroll_actions.payroll_action_id%type
763 ) is
764 select paa.assignment_action_id,
765 paa.action_sequence,
766 paaf.assignment_id,
767 paa.tax_unit_id
768 from per_people_f pap,
769 per_assignments_f paaf,
770 pay_payroll_actions ppa,
771 pay_payroll_actions ppa1,
772 pay_assignment_actions paa,
773 per_periods_of_service pps
774 where ppa.payroll_action_id = c_payroll_action_id
775 and paa.assignment_id = paaf.assignment_id
776 and pap.person_id between c_start_person and c_end_person
777 and pap.person_id = paaf.person_id
778 and pap.person_id = pps.person_id
779 and pps.period_of_service_id = paaf.period_of_service_id
780 and ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
781 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
782 and ppa1.payroll_action_id = paa.payroll_action_id
783 AND paa.action_status = 'C' /*Bug 4099317*/
784 and ppa1.business_group_id = ppa.business_group_id
785 and ppa.business_group_id = c_business_group_id
786 and ppa1.action_type in ('R','Q','I','B','V')
787 and NVL(pap.current_employee_flag,'N') like c_employee_type
791
788 and ppa1.payroll_action_id = c_pact_id
789 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
790
792 --------------------------------------------------------------------+
793 -- Cursor : csr_params
794 -- Description : Fetches User Parameters from Legislative_paramters
795 -- column.
796 --------------------------------------------------------------------+
797
798 CURSOR csr_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
799 IS
800 SELECT pay_core_utils.get_parameter('PAY',legislative_parameters) payroll_id,
801 pay_core_utils.get_parameter('ORG',legislative_parameters) org_id,
802 pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
803 to_date(pay_core_utils.get_parameter('SDATE',legislative_parameters),'YYYY/MM/DD') start_date,
804 to_date(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD') end_date,
805 pay_core_utils.get_parameter('PACTID',legislative_parameters) pact_id,
806 pay_core_utils.get_parameter('LE',legislative_parameters) legal_employer,
807 pay_core_utils.get_parameter('ASG',legislative_parameters) assignment_id,
808 pay_core_utils.get_parameter('SO1',legislative_parameters) sort_order_1,
809 pay_core_utils.get_parameter('SO2',legislative_parameters) sort_order_2,
810 pay_core_utils.get_parameter('SO3',legislative_parameters) sort_order_3,
811 pay_core_utils.get_parameter('SO4',legislative_parameters) sort_order_4,
812 to_date(pay_core_utils.get_parameter('PEDATE',legislative_parameters),'YYYY/MM/DD') period_end_date,
813 pay_core_utils.get_parameter('YTD_TOT',legislative_parameters) ytd_totals,
814 pay_core_utils.get_parameter('ZERO_REC',legislative_parameters) zero_records,
815 pay_core_utils.get_parameter('NEG_REC',legislative_parameters) negative_records,
816 decode(pay_core_utils.get_parameter('EMP_TYPE',legislative_parameters),'C','Y','T','N','%') employee_type,
817 pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions /*Bug# 4142159*/
818 FROM pay_payroll_actions ppa
819 WHERE ppa.payroll_action_id = c_payroll_action_id;
820
821 --------------------------------------------------------------------+
822 -- Cursor : csr_period_date_earned
823 -- Description : Fetches Date Earned for a given payroll
824 -- run.
825 --------------------------------------------------------------------+
826 /*Bug#3662449 *********/
827 CURSOR csr_period_date_earned(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
828 IS
829 SELECT ppa.date_earned
830 FROM pay_payroll_actions ppa
831 WHERE
832 ppa.payroll_action_id = c_payroll_action_id;
833
834
835 cursor csr_next_action_id is
836 select pay_assignment_actions_s.nextval
837 from dual;
838
839 l_next_assignment_action_id pay_assignment_actions.assignment_action_id%type;
840 l_procedure varchar2(200) ;
841 i number;
842
843 l_action_information_id number;
844 l_object_version_number number;
845
846
847 begin
848 i := 1;
849 g_debug :=hr_utility.debug_enabled ;
850
851 if g_debug then
852 l_procedure := g_package||'assignment_action_code';
853 hr_utility.set_location('Entering ' || l_procedure,1);
854 hr_utility.set_location('Entering assignment_Action_code',302);
855 end if;
856
857 -- initialization_code to to set the global tables for EIT
858 -- that will be used by each thread in multi-threading.
859
860 g_arc_payroll_action_id := p_payroll_action_id;
861
862 -- Fetch the parameters by user passed into global variable.
863
864 OPEN csr_params(p_payroll_action_id);
865 FETCH csr_params into g_parameters;
866 CLOSE csr_params;
867
868
869 if g_debug then
870 hr_utility.set_location('p_payroll_action_id.........= ' || p_payroll_action_id,30);
871 hr_utility.set_location('p_start_person..............= ' || p_start_person,30);
872 hr_utility.set_location('p_end_person................= ' || p_end_person,30);
873 hr_utility.set_location('g_parameters.business_group_id.........= ' || g_parameters.business_group_id,30);
874 hr_utility.set_location('g_parameters.payroll_id..............= ' || g_parameters.payroll_id,30);
875 hr_utility.set_location('g_parameters.org_id................= ' || g_parameters.org_id,30);
876 hr_utility.set_location('g_parameters.legal_employer.........= ' || g_parameters.legal_employer,30);
877 hr_utility.set_location('g_parameters.start_date..............= ' || g_parameters.start_date,30);
878 hr_utility.set_location('g_parameters.end_date................= ' || g_parameters.end_date,30);
879 hr_utility.set_location('g_parameters.period_end_date.........= ' || g_parameters.period_end_date,30);
880 hr_utility.set_location('g_parameters.pact_id..............= ' || g_parameters.pact_id,30);
881 hr_utility.set_location('g_parameters.employee_type..........= '||g_parameters.employee_type,30);
882 hr_utility.set_location('g_parameters.sort_order1..........= '||g_parameters.sort_order_1,30);
886 hr_utility.set_location('g_parameters.delete_actions..........= '||g_parameters.delete_actions,30);/*Bug# 4142159*/
883 hr_utility.set_location('g_parameters.sort_order2..........= '||g_parameters.sort_order_2,30);
884 hr_utility.set_location('g_parameters.sort_order3..........= '||g_parameters.sort_order_3,30);
885 hr_utility.set_location('g_parameters.sort_order4..........= '||g_parameters.sort_order_4,30);
887 end if;
888
889
890 g_business_group_id := g_parameters.business_group_id ;
891
892 -- Set end date variable .This value is used to fetch latest assignment details of
893 -- employee for archival.In case of archive start date/end date - archive end date
894 -- taken and pact_id/period_end_date , period end date is picked.
895
896 if g_parameters.end_date is not null
897 then
898 g_end_date := g_parameters.end_date;
899 g_start_date := g_parameters.start_date; --Bug#3662449
900 else
901 if g_parameters.period_end_date is not null
902 then
903 open csr_period_date_earned(g_parameters.pact_id); --Bug#3662449
904 fetch csr_period_date_earned into g_start_date;
905 close csr_period_date_earned;
906 g_end_date := g_parameters.period_end_date;
907 else
908 g_start_date := to_date('1900/01/01','YYYY/MM/DD'); --Bug#3662449
909 g_end_date := to_date('4712/12/31','YYYY/MM/DD');
910 end if;
911 end if; /* End of outer if loop */
912
913
914 if g_parameters.org_id is not null
915 then
916 if g_parameters.start_date is not null and g_parameters.end_date is not null
917 then
918 FOR csr_rec in csr_assignment_org_period(p_payroll_action_id,
919 p_start_person,
920 p_end_person,
921 g_parameters.employee_type,
922 g_parameters.business_group_id,
923 g_parameters.org_id,
924 g_parameters.start_date,
925 g_parameters.end_date)
926 LOOP /*Loop 1 Org,Archive start date,end date */
927 open csr_next_action_id;
928 fetch csr_next_action_id into l_next_assignment_action_id;
929 close csr_next_action_id;
930
931 if g_debug then
932
933 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
934 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
935 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
936 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
937
938 end if;
939
940
941
942 -- Create the archive assignment actions
943 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
944
945
946 insert into pay_action_information(
947 action_information_id,
948 action_context_id,
949 action_context_type,
950 effective_date,
951 source_id,
952 tax_unit_id,
953 action_information_category,
954 action_information1,
955 action_information2,
956 action_information3,
957 assignment_id
958 )
959 values(
960 pay_action_information_s.nextval,
961 l_next_assignment_action_id,
962 'AAP',
963 null,
964 null,
965 csr_rec.tax_unit_id,
966 'AU_ARCHIVE_ASG_DETAILS',
967 csr_rec.assignment_action_id,
968 p_payroll_action_id,
969 csr_rec.action_sequence,
970 csr_rec.assignment_id
971 );
972
973
974 END LOOP;/* Loop 1 */
975 if g_debug then
976 hr_utility.set_location('Leaving............Loop1 Org+Period....' || l_procedure,1000);
977 end if;
978
979 else
980 FOR csr_rec in csr_assignment_org_run(p_payroll_action_id,
981 p_start_person,
982 p_end_person,
983 g_parameters.employee_type,
984 g_parameters.business_group_id,
985 g_parameters.org_id,
986 g_parameters.period_end_date,
987 g_parameters.pact_id)
988 LOOP /*Loop 2 Org,Pact_id and period end date*/
989 open csr_next_action_id;
990 fetch csr_next_action_id into l_next_assignment_action_id;
991 close csr_next_action_id;
992
993 if g_debug then
994 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
995 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
999
996 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
997 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
998 end if;
1000 -- Create the archive assignment actions
1001 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1002
1003 insert into pay_action_information(
1004 action_information_id,
1005 action_context_id,
1006 action_context_type,
1007 effective_date,
1008 source_id,
1009 tax_unit_id,
1010 action_information_category,
1011 action_information1,
1012 action_information2,
1013 action_information3,
1014 assignment_id
1015 )
1016 values(
1017 pay_action_information_s.nextval,
1018 l_next_assignment_action_id,
1019 'AAP',
1020 null,
1021 null,
1022 csr_rec.tax_unit_id,
1023 'AU_ARCHIVE_ASG_DETAILS',
1024 csr_rec.assignment_action_id,
1025 p_payroll_action_id,
1026 csr_rec.action_sequence,
1027 csr_rec.assignment_id
1028 );
1029
1030
1031 END LOOP; /* Loop 2 */
1032 if g_debug then
1033 hr_utility.set_location('Leaving............Loop2 ,Org + Run....' || l_procedure,1000);
1034 end if;
1035 end if; /* End of Inner Organization */
1036 else /* Not Org,check for others */
1037
1038 if g_parameters.legal_employer is not null
1039 then
1040 if g_parameters.start_date is not null and g_parameters.end_date is not null
1041 then
1042 FOR csr_rec in csr_assignment_legal_period(p_payroll_action_id,
1043 p_start_person,
1044 p_end_person,
1045 g_parameters.employee_type,
1046 g_parameters.business_group_id,
1047 g_parameters.legal_employer,
1048 g_parameters.start_date,
1049 g_parameters.end_date)
1050 LOOP /*Loop 3 Leg Employer,Archive Start date,archive end date*/
1051 open csr_next_action_id;
1052 fetch csr_next_action_id into l_next_assignment_action_id;
1053 close csr_next_action_id;
1054 if g_debug then
1055 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
1056 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
1057 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
1058 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
1059 end if;
1060
1061 -- Create the archive assignment actions
1062 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1063
1064 insert into pay_action_information(
1065 action_information_id,
1066 action_context_id,
1067 action_context_type,
1068 effective_date,
1069 source_id,
1070 tax_unit_id,
1071 action_information_category,
1072 action_information1,
1073 action_information2,
1074 action_information3,
1075 assignment_id
1076 )
1077 values(
1078 pay_action_information_s.nextval,
1079 l_next_assignment_action_id,
1080 'AAP',
1081 null,
1082 null,
1083 csr_rec.tax_unit_id,
1084 'AU_ARCHIVE_ASG_DETAILS',
1085 csr_rec.assignment_action_id,
1086 p_payroll_action_id,
1087 csr_rec.action_sequence,
1088 csr_rec.assignment_id
1089 );
1090
1091
1092 END LOOP;/* Loop 3 */
1093 if g_debug then
1094 hr_utility.set_location('Leaving............Loop3.Legal Emp + period...' || l_procedure,1000);
1095 end if;
1096
1097 else
1098 FOR csr_rec in csr_assignment_legal_run(p_payroll_action_id,
1099 p_start_person,
1100 p_end_person,
1101 g_parameters.employee_type,
1102 g_parameters.business_group_id,
1103 g_parameters.legal_employer,
1107 open csr_next_action_id;
1104 g_parameters.period_end_date,
1105 g_parameters.pact_id)
1106 LOOP /*Loop 4 Leg employer,pact_id + period end date */
1108 fetch csr_next_action_id into l_next_assignment_action_id;
1109 close csr_next_action_id;
1110
1111 if g_debug then
1112 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
1113 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
1114 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
1115 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
1116 end if;
1117
1118 -- Create the archive assignment actions
1119 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1120
1121 insert into pay_action_information(
1122 action_information_id,
1123 action_context_id,
1124 action_context_type,
1125 effective_date,
1126 source_id,
1127 tax_unit_id,
1128 action_information_category,
1129 action_information1,
1130 action_information2,
1131 action_information3,
1132 assignment_id
1133 )
1134 values(
1135 pay_action_information_s.nextval,
1136 l_next_assignment_action_id,
1137 'AAP',
1138 null,
1139 null,
1140 csr_rec.tax_unit_id,
1141 'AU_ARCHIVE_ASG_DETAILS',
1142 csr_rec.assignment_action_id,
1143 p_payroll_action_id,
1144 csr_rec.action_sequence,
1145 csr_rec.assignment_id
1146 );
1147
1148
1149 END LOOP; /* Loop 4 */
1150 if g_debug then
1151 hr_utility.set_location('Leaving............Loop4.Legal Emp + Run...' || l_procedure,1000);
1152 end if;
1153 end if; /* End of Inner Legal Employer */
1154 else /* Not Org,Legal Emp Check others */
1155
1156 if g_parameters.payroll_id is not null
1157 then
1158 if g_parameters.start_date is not null and g_parameters.end_date is not null
1159 then
1160 FOR csr_rec in csr_assignment_payroll_period(p_payroll_action_id,
1161 p_start_person,
1162 p_end_person,
1163 g_parameters.employee_type,
1164 g_parameters.business_group_id,
1165 g_parameters.payroll_id,
1166 g_parameters.start_date,
1167 g_parameters.end_date)
1168 LOOP /*Loop 5 Payroll, Archive start date,end date */
1169 open csr_next_action_id;
1170 fetch csr_next_action_id into l_next_assignment_action_id;
1171 close csr_next_action_id;
1172
1173 if g_debug then
1174 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
1175 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
1176 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
1177 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
1178 end if;
1179
1180 -- Create the archive assignment actions
1181 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1182
1183 insert into pay_action_information(
1184 action_information_id,
1185 action_context_id,
1186 action_context_type,
1187 effective_date,
1188 source_id,
1189 tax_unit_id,
1190 action_information_category,
1191 action_information1,
1192 action_information2,
1193 action_information3,
1194 assignment_id
1195 )
1196 values(
1197 pay_action_information_s.nextval,
1198 l_next_assignment_action_id,
1199 'AAP',
1200 null,
1201 null,
1202 csr_rec.tax_unit_id,
1203 'AU_ARCHIVE_ASG_DETAILS',
1204 csr_rec.assignment_action_id,
1205 p_payroll_action_id,
1206 csr_rec.action_sequence,
1207 csr_rec.assignment_id
1208 );
1209
1213 if g_debug then
1210
1211 END LOOP;/* Loop 5 */
1212
1214 hr_utility.set_location('Leaving............Loop5 Payroll + Period....' || l_procedure,1000);
1215 end if;
1216
1217 else
1218 FOR csr_rec in csr_assignment_payroll_run(p_payroll_action_id,
1219 p_start_person,
1220 p_end_person,
1221 g_parameters.employee_type,
1222 g_parameters.business_group_id,
1223 g_parameters.payroll_id,
1224 g_parameters.period_end_date,
1225 g_parameters.pact_id)
1226 LOOP /*Loop 6 Payroll, pact_id + period end date*/
1227 open csr_next_action_id;
1228 fetch csr_next_action_id into l_next_assignment_action_id;
1229 close csr_next_action_id;
1230
1231 if g_debug then
1232 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
1233 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
1234 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
1235 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
1236 end if;
1237
1238 -- Create the archive assignment actions
1239 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1240
1241 insert into pay_action_information(
1242 action_information_id,
1243 action_context_id,
1244 action_context_type,
1245 effective_date,
1246 source_id,
1247 tax_unit_id,
1248 action_information_category,
1249 action_information1,
1250 action_information2,
1251 action_information3,
1252 assignment_id
1253 )
1254 values(
1255 pay_action_information_s.nextval,
1256 l_next_assignment_action_id,
1257 'AAP',
1258 null,
1259 null,
1260 csr_rec.tax_unit_id,
1261 'AU_ARCHIVE_ASG_DETAILS',
1262 csr_rec.assignment_action_id,
1263 p_payroll_action_id,
1264 csr_rec.action_sequence,
1265 csr_rec.assignment_id
1266 );
1267
1268
1269 END LOOP; /* Loop 6 */
1270 if g_debug then
1271 hr_utility.set_location('Leaving............Loop6 Payroll+ Run....' || l_procedure,1000);
1272 end if;
1273 end if; /* End of Inner Payroll */
1274 else /* Not Org,Legal,Payroll check others */
1275
1276 if g_parameters.assignment_id is not null
1277 then
1278 if g_parameters.start_date is not null and g_parameters.end_date is not null
1279 then
1280 FOR csr_rec in csr_assignment_period(p_payroll_action_id,
1281 p_start_person,
1282 p_end_person,
1283 g_parameters.employee_type,
1284 g_parameters.business_group_id,
1285 g_parameters.assignment_id,
1286 g_parameters.start_date,
1287 g_parameters.end_date)
1288 LOOP /*Loop 7 Assignment ,Archive start date,end date*/
1289 open csr_next_action_id;
1290 fetch csr_next_action_id into l_next_assignment_action_id;
1291 close csr_next_action_id;
1292
1293 if g_debug then
1294 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
1295 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
1296 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
1297 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
1298 end if;
1299
1300 -- Create the archive assignment actions
1301 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1302
1303 insert into pay_action_information(
1304 action_information_id,
1305 action_context_id,
1306 action_context_type,
1307 effective_date,
1308 source_id,
1309 tax_unit_id,
1310 action_information_category,
1311 action_information1,
1312 action_information2,
1313 action_information3,
1314 assignment_id
1315 )
1316 values(
1320 null,
1317 pay_action_information_s.nextval,
1318 l_next_assignment_action_id,
1319 'AAP',
1321 null,
1322 csr_rec.tax_unit_id,
1323 'AU_ARCHIVE_ASG_DETAILS',
1324 csr_rec.assignment_action_id,
1325 p_payroll_action_id,
1326 csr_rec.action_sequence,
1327 csr_rec.assignment_id
1328 );
1329
1330
1331 END LOOP;/* Loop 7 */
1332 if g_debug then
1333 hr_utility.set_location('Leaving............Loop7. Asg + Period...' || l_procedure,1000);
1334 end if;
1335
1336 else
1337 FOR csr_rec in csr_assignment_run(p_payroll_action_id,
1338 p_start_person,
1339 p_end_person,
1340 g_parameters.employee_type,
1341 g_parameters.business_group_id,
1342 g_parameters.assignment_id,
1343 g_parameters.period_end_date,
1344 g_parameters.pact_id)
1345 LOOP /*Loop 8 Assignment Pact_id,Period end date */
1346 open csr_next_action_id;
1347 fetch csr_next_action_id into l_next_assignment_action_id;
1348 close csr_next_action_id;
1349
1350 if g_debug then
1351 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
1352 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
1353 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
1354 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
1355 end if;
1356
1357 -- Create the archive assignment actions
1358 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1359
1360 insert into pay_action_information(
1361 action_information_id,
1362 action_context_id,
1363 action_context_type,
1364 effective_date,
1365 source_id,
1366 tax_unit_id,
1367 action_information_category,
1368 action_information1,
1369 action_information2,
1370 action_information3,
1371 assignment_id
1372 )
1373 values(
1374 pay_action_information_s.nextval,
1375 l_next_assignment_action_id,
1376 'AAP',
1377 null,
1378 null,
1379 csr_rec.tax_unit_id,
1380 'AU_ARCHIVE_ASG_DETAILS',
1381 csr_rec.assignment_action_id,
1382 p_payroll_action_id,
1383 csr_rec.action_sequence,
1384 csr_rec.assignment_id
1385 );
1386
1387
1388 END LOOP; /* Loop 8 */
1389 if g_debug then
1390 hr_utility.set_location('Leaving............Loop8.Asg + Run...' || l_procedure,1000);
1391 end if;
1392 end if; /* End of Inner Assignment */
1393
1394 else
1395
1396 /* Default Begins */
1397
1398 if g_parameters.start_date is not null and g_parameters.end_date is not null
1399 then
1400 FOR csr_rec in csr_assignment_default_period(p_payroll_action_id,
1401 p_start_person,
1402 p_end_person,
1403 g_parameters.employee_type,
1404 g_parameters.business_group_id,
1405 g_parameters.start_date,
1406 g_parameters.end_date)
1407 LOOP /*Loop 9*/
1408 open csr_next_action_id;
1409 fetch csr_next_action_id into l_next_assignment_action_id;
1410 close csr_next_action_id;
1411
1412 if g_debug then
1413 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
1414 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
1415 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
1416 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
1417 end if;
1418
1419 -- Create the archive assignment actions
1420 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1421
1422 insert into pay_action_information(
1423 action_information_id,
1424 action_context_id,
1428 tax_unit_id,
1425 action_context_type,
1426 effective_date,
1427 source_id,
1429 action_information_category,
1430 action_information1,
1431 action_information2,
1432 action_information3,
1433 assignment_id
1434 )
1435 values(
1436 pay_action_information_s.nextval,
1437 l_next_assignment_action_id,
1438 'AAP',
1439 null,
1440 null,
1441 csr_rec.tax_unit_id,
1442 'AU_ARCHIVE_ASG_DETAILS',
1443 csr_rec.assignment_action_id,
1444 p_payroll_action_id,
1445 csr_rec.action_sequence,
1446 csr_rec.assignment_id
1447 );
1448
1449
1450 END LOOP;/* Loop 9 */
1451 if g_debug then
1452 hr_utility.set_location('Leaving............Loop9..Default + Period..' || l_procedure,1000);
1453 end if;
1454
1455 else
1456 FOR csr_rec in csr_assignment_default_run(p_payroll_action_id,
1457 p_start_person,
1458 p_end_person,
1459 g_parameters.employee_type,
1460 g_parameters.business_group_id,
1461 g_parameters.period_end_date,
1462 g_parameters.pact_id)
1463 LOOP /*Loop 10 */
1464 open csr_next_action_id;
1465 fetch csr_next_action_id into l_next_assignment_action_id;
1466 close csr_next_action_id;
1467
1468 if g_debug then
1469 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
1470 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
1471 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
1472 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
1473 end if;
1474
1475 -- Create the archive assignment actions
1476 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1477
1478 insert into pay_action_information(
1479 action_information_id,
1480 action_context_id,
1481 action_context_type,
1482 effective_date,
1483 source_id,
1484 tax_unit_id,
1485 action_information_category,
1486 action_information1,
1487 action_information2,
1488 action_information3,
1489 assignment_id
1490 )
1491 values(
1492 pay_action_information_s.nextval,
1493 l_next_assignment_action_id,
1494 'AAP',
1495 null,
1496 null,
1497 csr_rec.tax_unit_id,
1498 'AU_ARCHIVE_ASG_DETAILS',
1499 csr_rec.assignment_action_id,
1500 p_payroll_action_id,
1501 csr_rec.action_sequence,
1502 csr_rec.assignment_id
1503 );
1504
1505
1506 END LOOP; /* Loop 10 */
1507 if g_debug then
1508 hr_utility.set_location('Leaving............Loop10 Default + Run....' || l_procedure,1000);
1509 end if;
1510 end if; /* End of Inner Default */
1511
1512
1513 end if ;/*End Assignment id */
1514 end if ; /* End Payroll */
1515 end if; /* End Legal */
1516 end if; /* End Organization */
1517
1518 exception
1519 when others then
1520 hr_utility.set_location('Error in '||l_procedure,999999);
1521 raise;
1522 end assignment_action_code;
1523
1524 --------------------------------------------------------------------+
1525 -- Name : archive_code
1526 -- Type : Procedure
1527 -- Access: Public
1528 -- This procedure archives details for assignment.
1529 -- Employee details
1530 -- Checks pay_Action_information context ='AU_ARCHIVE_ASG_DETAILS'
1531 -- If employee details not previously archived,proc archives
1532 -- employee details in pay_Action_information with context
1533 -- 'AU_EMPLOYEE_RECON_DETAILS'.
1534 -- Element details.
1535 -- For each assignment run,proc archives element processed in
1536 -- pay_Action_information with context 'AU_ELEMENT_RECON_DETAILS'
1537 -- Balance Details.
1538 -- For each assignment run,proc archives balance details in
1539 -- pay_Action_information with context 'AU_BALANCE_RECON_DETAILS'
1543
1540 -- Uses package pay_au_reconciliation_pkg to fetch balances.
1541
1542 --------------------------------------------------------------------+
1544 procedure archive_code
1545 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
1546 ,p_effective_date in pay_payroll_actions.effective_date%type
1547 ) is
1548
1549
1550
1551 cursor c_employee_details(c_business_group_id hr_all_organization_units.organization_id%TYPE,
1552 c_assignment_id number,c_end_date date, c_start_date date) /*Bug#3662449 c_start_date parameter added*/
1553 is
1554 select pap.full_name,
1555 paa.assignment_number,
1556 paa.assignment_id,
1557 to_number(pro.proposed_salary_n) actual_salary,
1558 paa.normal_hours,
1559 pps.actual_termination_date,
1560 pgr.name grade, /* Bug 3953702*/
1561 paa.organization_id,
1562 hou.NAME organization_name, /*Bug 3953706 */
1563 -- paa.payroll_id, /*Bug 4688872*/
1564 -- papf.payroll_name, /*Bug 4132525, Bug 4688872*/
1565 hsc.segment1 tax_unit_id, /*Bug 4040688*/
1566 hou1.NAME Legal_Employer /*Bug 4132525*/
1567 from per_people_f pap,
1568 per_assignments_f paa,
1569 per_grades_tl pgr, /* Bug 3953702*/
1570 per_periods_of_service pps,
1571 per_pay_bases ppb,
1572 per_pay_proposals pro,
1573 per_assignment_status_types past,
1574 hr_soft_coding_keyflex hsc, /*Bug 4040688*/
1575 hr_organization_units hou, /*Bug 3953706 */
1576 hr_organization_units hou1 /*Bug 4132525*/
1577 -- pay_payrolls_f papf /*Bug 4132525, Bug 4688872*/
1578 where pap.person_id = paa.person_id
1579 and paa.assignment_id = c_assignment_id
1580 and paa.business_group_id = c_business_group_id
1581 and paa.grade_id = pgr.grade_id(+) /* Bug 3953702*/
1582 and pgr.language(+) = userenv('LANG')
1583 and paa.pay_basis_id = ppb.pay_basis_id(+)
1584 and paa.assignment_id = pro.assignment_id(+)
1585 AND hou.organization_id = paa.organization_id /*Bug 3953706 */
1586 AND hou1.organization_id = hsc.segment1 /*Bug 4132525*/
1587 -- AND papf.payroll_id = paa.payroll_id /*Bug 4132525, Bug 4688872*/
1588 -- AND c_end_date BETWEEN papf.effective_start_date AND papf.effective_end_date /*Bug 4132525, Bug 4688872*/
1589 and pps.period_of_service_id = paa.period_of_service_id
1590 AND paa.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id /*Bug 4040688*/
1591 and paa.assignment_status_type_id = past.assignment_status_type_id
1592 and paa.effective_end_date = ( select max(effective_end_date) /*Bug#3662449 sub query added*/
1593 from per_assignments_f
1594 WHERE assignment_id = c_assignment_id
1595 and effective_end_date >= c_start_date
1596 and effective_start_date <= c_end_date)
1597 and c_end_date between pap.effective_start_date and pap.effective_end_date
1598 and pps.person_id = pap.person_id
1599 and pro.change_date(+) <= c_end_date
1600 and nvl(pro.approved,'Y') = 'Y'
1601 and nvl(pro.change_date,to_date('4712/12/31','YYYY/MM/DD')) = (select nvl(max(pro1.change_date),to_date('4712/12/31','YYYY/MM/DD'))
1602 from per_pay_proposals pro1
1603 where pro1.assignment_id(+) = paa.assignment_id
1604 and pro1.change_date(+) <= c_end_date
1605 and nvl(pro1.approved,'Y')='Y');
1606
1607
1608 /*Bug# 4688872 - Introduced a new cursor to get the payroll name for the employee. This has been done to take care of cases
1609 where assignment has payroll attached to it for few months but is not attached at the end of year*/
1610 CURSOR c_get_payroll_name(c_assignment_id number,
1611 c_start_date date,
1612 c_end_date date)
1613 IS
1614 SELECT paaf.payroll_id, pay.payroll_name
1615 FROM per_all_assignments_f paaf,
1616 pay_payrolls_f pay
1617 WHERE paaf.assignment_id = c_assignment_id
1618 and paaf.effective_end_date = (select max(effective_end_date)
1619 From per_assignments_f iipaf
1620 WHERE iipaf.assignment_id = c_assignment_id
1621 and iipaf.effective_end_date >= c_start_date
1622 and iipaf.effective_start_date <= c_end_date
1623 AND iipaf.payroll_id IS NOT NULL)
1624 AND pay.payroll_id = paaf.payroll_id
1625 AND paaf.effective_end_date BETWEEN pay.effective_start_date AND pay.effective_end_date;
1626
1627 cursor c_payment_summary_details(c_assignment_id number,
1628 c_fin_date per_assignment_extra_info.aei_information1%TYPE)
1629 is
1630 select hr.meaning fin_year
1631 from per_assignment_extra_info pae,
1632 hr_lookups hr
1633 where pae.aei_information_category = 'HR_PS_ISSUE_DATE_AU'
1634 and pae.information_type = 'HR_PS_ISSUE_DATE_AU'
1635 and pae.assignment_id = c_assignment_id
1636 and pae.aei_information1 = c_fin_date
1637 and pae.aei_information1 = hr.lookup_code
1638 and hr.lookup_type = 'AU_PS_FINANCIAL_YEAR';
1639
1640 /* Bug 3627293 Modified cursor to return source_action_id */
1641 /* Bug 3953706 Modified cursor to group elements into 7 groups (Taxable Earnings,Non Taxable Earnings,Pre Tax Deductions,Employer Superannuation Contributions,
1642 Post Tax Deductions,Direct Payments,Tax Deductions)*/
1643 /*Bug 3935471 modified cursor to return the tax unit of master assignment action id*/
1647 Used a group by based on rate */
1644 /* Bug 5063359 - Modified decode for Employer Charges to return classification as Employer Charges rather than Employer Superannuation Contribution */
1645
1646 /* Bug 5461557 - Added tables piv2 and prrv2 to get input value for hours and rate and their relative joins,
1648
1649 /*Bug 5603254 - Removed tables piv2 and prrv2 and their joins from cursor , added a call to function pay_au_rec_det_archive.get_element_payment_hours to get the value for hours and rate */
1650
1651
1652 cursor c_element_details(c_business_group_id hr_all_organization_units.organization_id%TYPE,c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
1653 is
1654 select element_name,label classification_name,sum(amount) payment,sum(hours) hours,source_action_id master_action_id, tax_unit_id master_tax_unit_id,rate /*Bug 3935471 ,5461557 */
1655 from
1656 (select distinct -- Bug No: 4045910
1657 nvl(pet.reporting_name, pet.element_name) element_name,
1658 decode(instr(pec.classification_name, 'Earnings'), 0, null,
1659 decode(pec2.classification_name, 'Non Taxable', 'Non Taxable Earnings', 'Taxable Earnings')) ||
1660 decode(instr(pec.classification_name, 'Payments'), 0, null,
1661 decode(instr(pec.classification_name, 'Direct'), 0, 'Taxable Earnings', 'Direct Payments')) ||
1662 decode(instr(pec.classification_name, 'Deductions'), 0, null,
1663 decode(pec.classification_name , 'Termination Deductions' , 'Tax Deductions'
1664 , 'Involuntary Deductions' , 'Post Tax Deductions'
1665 , 'Voluntary Deductions' , 'Post Tax Deductions'
1666 , pec.classification_name )) ||
1667 decode(instr(pec.classification_name, 'Employer Charges'), 0,null,'Employer Charges' ) label,
1668 decode(substr(piv.uom,1,1), 'M', prrv.result_value, null) amount,
1669 pay_au_rec_det_archive.get_element_payment_hours(prr.assignment_action_id,pet.element_type_id,prr.run_result_id,ppa.effective_date) hours, /*Bug 5603254 */
1670 decode(pay_au_rec_det_archive.get_element_payment_rate(prr.assignment_action_id,pet.element_type_id,prr.run_result_id,ppa.effective_date), null,
1671 (prrv.result_value/pay_au_rec_det_archive.get_element_payment_hours(prr.assignment_action_id,pet.element_type_id,prr.run_result_id, ppa.effective_date)),
1672 pay_au_rec_det_archive.get_element_payment_rate(prr.assignment_action_id,pet.element_type_id,prr.run_result_id,ppa.effective_date)) rate, /* 5599310 */
1673 prr.run_result_id,
1674 paa.source_action_id,
1675 paa2.tax_unit_id /*Bug 3935471*/
1676 from pay_element_types_f pet
1677 ,pay_input_values_f piv
1678 ,pay_element_classifications pec
1679 ,pay_assignment_actions paa
1680 ,pay_assignment_actions paa2 /*Bug 3935471*/
1681 ,pay_payroll_actions ppa
1682 ,per_assignments_f paaf
1683 ,pay_run_results prr
1684 ,pay_run_result_values prrv
1685 ,pay_element_classifications pec2
1686 ,pay_sub_classification_rules_f pscr
1687 where pet.element_type_id = piv.element_type_id
1688 and pet.element_type_id = prr.element_type_id
1689 and prr.assignment_action_id = paa.assignment_action_id
1690 and paaf.assignment_id = paa.assignment_id
1691 AND paa2.assignment_action_id = nvl(paa.source_action_id, paa.assignment_action_id) /*Bug 3935471*/
1692 and paaf.business_group_id = c_business_group_id/*Bug 5370001 */
1693 and prr.run_result_id = prrv.run_result_id
1694 and prrv.input_value_id = piv.input_value_id
1695 and pet.classification_id = pec.classification_id
1696 and pec.legislation_code = 'AU'
1697 and paa.assignment_action_id = c_assignment_action_id/*Bug 5370001 */
1698 and paa.payroll_action_id = ppa.payroll_action_id
1699 and paa.action_status = 'C' /*Bug 4099317*/
1700 and piv.name = 'Pay Value'
1701 and (instr(pec.classification_name, 'Earnings') > 0
1702 or instr(pec.classification_name, 'Payments') > 0
1703 or instr(pec.classification_name, 'Deductions') > 0
1704 or instr(pec.classification_name, 'Employer Charges' ) > 0 )
1705 and pet.element_type_id = pscr.element_type_id (+)
1706 and ppa.effective_date between nvl(pscr.effective_start_date, ppa.effective_date)
1707 and nvl(pscr.effective_end_date, ppa.effective_date)
1708 and pscr.classification_id = pec2.classification_id(+)
1709 and ppa.date_earned between pet.effective_start_date and pet.effective_end_date
1710 and ppa.date_earned between paaf.effective_start_date and paaf.effective_end_date
1711 and prr.status in ('P','PA')
1712 )
1713 group by element_name,label,source_action_id, tax_unit_id, rate ; /*Bug 3935471*/
1714
1715 cursor csr_get_data (c_arc_ass_act_id number)
1716 is
1717 select pai.action_information1, pai.tax_unit_id, pai.assignment_id,pai.action_information3
1718 from pay_action_information pai
1719 where action_information_category = 'AU_ARCHIVE_ASG_DETAILS'
1720 and pai.action_context_id = c_arc_ass_act_id;
1721
1722 /*Bug 4040688 - Two new cursors added to get the maximum assignment action id*/
1723 cursor csr_get_max_asg_dates (c_assignment_id number,
1724 c_start_date DATE,
1725 c_end_date DATE,
1726 c_tax_unit_id number)
1727 is
1728 select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
1729 ,max(paa.action_sequence)
1730 from pay_assignment_actions paa
1731 , pay_payroll_actions ppa
1732 , per_assignments_f paf
1733 where paa.assignment_id = paf.assignment_id
1734 and paf.assignment_id = c_assignment_id
1738 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
1735 and ppa.payroll_action_id = paa.payroll_action_id
1736 and ppa.effective_date between c_start_date and c_end_date
1737 and ppa.payroll_id = paf.payroll_id
1739 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
1740 and paa.action_status='C'
1741 AND paa.tax_unit_id = nvl(c_tax_unit_id, paa.tax_unit_id);
1742
1743
1744 cursor csr_get_max_asg_action (c_assignment_id number,
1745 c_payroll_action_id number,
1746 c_tax_unit_id number)
1747 is
1748 select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
1749 ,max(paa.action_sequence)
1750 from pay_assignment_actions paa
1751 , pay_payroll_actions ppa
1752 , per_assignments_f paf
1753 where paa.assignment_id = paf.assignment_id
1754 and paf.assignment_id = c_assignment_id
1755 and ppa.payroll_action_id = paa.payroll_action_id
1756 and ppa.payroll_action_id = c_payroll_action_id
1757 and ppa.payroll_id = paf.payroll_id
1758 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
1759 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
1760 and paa.action_status='C'
1761 AND paa.tax_unit_id = nvl(c_tax_unit_id, paa.tax_unit_id);
1762
1763 /*Bug 4040688 - end of modification*/
1764
1765 l_procedure varchar2(200);
1766 l_action_information_id number;
1767 l_object_version_number number;
1768
1769 --
1770 -- Table Declarations for the BBR
1771 --
1772 l_context_lst pay_balance_pkg.t_context_tab;
1773 l_output_tab pay_balance_pkg.t_detailed_bal_out_tab;
1774
1775 l_TAXABLE_EARNINGS number;
1776 l_GROSS_EARNINGS number;
1777 l_PRE_TAX_DEDUCTIONS number;
1778 l_DIRECT_PAYMENTS number;
1779 l_NON_TAXABLE_EARNINGS number;
1780 l_DEDUCTIONS number;
1781 l_TAX number;
1782 l_NET_PAYMENT number;
1783 l_EMPLOYER_CHARGES number;
1784
1785 l_YTD_TAXABLE_EARNINGS number;
1786 l_YTD_NON_TAXABLE_EARNINGS number;
1787 l_YTD_GROSS_EARNINGS number;
1788 l_YTD_PRE_TAX_DEDUCTIONS number;
1789 l_YTD_DIRECT_PAYMENTS number;
1790 l_YTD_DEDUCTIONS number;
1791 l_YTD_TAX number;
1792 l_YTD_NET_PAYMENT number;
1793 l_YTD_EMPLOYER_CHARGES number;
1794
1795 l_ass_act_id number;
1796 l_tax_unit_id number;
1797 l_assignment_id number;
1798
1799 l_fin_year varchar2(80);
1800 l_action_sequence number;
1801 l_fin_year_code per_assignment_extra_info.aei_information1%TYPE;
1802 l_balance_flag varchar2(1);
1803
1804 l_max_asg_action_id number; /*Bug 4040688*/
1805 l_max_action_sequence number; /*Bug 4040688*/
1806 l_payroll_id number; /*Bug 4688872*/
1807 l_payroll_name pay_payrolls_f.payroll_name%type; /*Bug 4688872*/
1808
1809 /* Bug 3531704 */
1810 --------------------------------------------------------------------+
1811 -- Name : get_fin_year_code
1812 -- Type : Function
1813 -- Access : Private
1814 -- This function returns the AU financial year code
1815 -- based on the Date parameter.
1816 --------------------------------------------------------------------+
1817
1818 FUNCTION get_fin_year_code(p_end_date in date)
1819 RETURN VARCHAR2
1820 IS
1821 l_check_date date;
1822 BEGIN
1823 /* Bug 3531704 Removed to_char */
1824 l_check_date := to_date('01/07/'||to_char(p_end_date,'YYYY'),'DD/MM/YYYY');
1825
1826 if (months_between(p_end_date,l_check_date) >= 0 )
1827 then
1828 return to_char(p_end_date,'YY');
1829 else
1830 return to_char(add_months(p_end_date,-6),'YY');
1831 end if;
1832 END get_fin_year_code;
1833
1834 begin
1835
1836 g_debug :=hr_utility.debug_enabled ;
1837 l_balance_flag := 'Y' ; /* Bug 3627293 - Balances have to be stored */
1838 l_YTD_GROSS_EARNINGS :=0 ;
1839 l_YTD_NON_TAXABLE_EARNINGS :=0 ;
1840 l_YTD_PRE_TAX_DEDUCTIONS :=0 ;
1841 l_YTD_TAXABLE_EARNINGS :=0 ;
1842 l_YTD_TAX :=0 ;
1843 l_YTD_DEDUCTIONS :=0 ;
1844 l_YTD_DIRECT_PAYMENTS :=0 ;
1845 l_YTD_NET_PAYMENT :=0 ;
1846 l_YTD_EMPLOYER_CHARGES :=0 ;
1847
1848 if g_debug then
1849 l_procedure := g_package||'archive_code';
1850 hr_utility.set_location('Entering '||l_procedure,1);
1851 hr_utility.set_location('p_assignment_action_id......= '|| p_assignment_action_id,10);
1852 hr_utility.set_location('p_effective_date............= '|| to_char(p_effective_date,'DD-MON-YYYY'),10);
1853 end if;
1854
1855 OPEN csr_get_data(p_assignment_action_id);
1856 FETCH csr_get_data into l_ass_act_id, l_tax_unit_id, l_assignment_id,l_action_sequence;
1857 CLOSE csr_get_data;
1858
1859 if g_debug then
1860 hr_utility.set_location('l_ass_act_id......= '|| l_ass_act_id,10);
1861 hr_utility.set_location('l_tax_unit_id............= '|| l_tax_unit_id,10);
1862 hr_utility.set_location('l_assignment_id......= '|| l_assignment_id,10);
1866 LOOP
1863 end if;
1864
1865 FOR csr_rec in c_employee_details(g_business_group_id, l_assignment_id,g_end_date,g_start_date) --Bug#3662449
1867
1868 if g_debug then
1869 hr_utility.set_location('csr_rec.full_name............= '|| csr_rec.full_name,10);
1870 end if;
1871
1872 IF (NVL(g_prev_assignment_id,0) <> csr_rec.assignment_id) THEN
1873
1874 g_prev_assignment_id := csr_rec.assignment_id;
1875
1876 -- Fetch Manual PS Details for assignment
1877
1878 l_fin_year_code := get_fin_year_code(g_end_date);
1879
1880 OPEN c_payment_summary_details(csr_rec.assignment_id,l_fin_year_code);
1881 FETCH c_payment_summary_details into l_fin_year;
1882 CLOSE c_payment_summary_details;
1883
1884 /*Bug 4040688 - Call to the procedure to get maximum assignment action id*/
1885 IF g_parameters.pact_id IS NULL THEN
1886 OPEN csr_get_max_asg_dates(csr_rec.assignment_id, g_start_date, g_end_date, g_parameters.legal_employer);
1887 FETCH csr_get_max_asg_dates INTO l_max_asg_action_id, l_max_action_sequence;
1888 CLOSE csr_get_max_asg_dates;
1889 ELSE
1890 OPEN csr_get_max_asg_action(csr_rec.assignment_id, g_parameters.pact_id, g_parameters.legal_employer);
1891 FETCH csr_get_max_asg_action INTO l_max_asg_action_id, l_max_action_sequence;
1892 CLOSE csr_get_max_asg_action;
1893 END IF ;
1894 -- Archive YTD balance details
1895 /*Bug 3953706 - Modfied the call to procedure introduce new parameters*/
1896 /*Bug 4040688 - YTD Balances will be called for the maximum assignment action id of the assignment*/
1897 IF l_max_asg_action_id IS NOT NULL THEN
1898 pay_au_reconciliation_pkg.GET_YTD_AU_REC_BALANCES(
1899 P_ASSIGNMENT_ACTION_ID => l_max_asg_action_id,
1900 P_REGISTERED_EMPLOYER => g_parameters.legal_employer, --2610141
1901 P_YTD_GROSS_EARNINGS => l_YTD_GROSS_EARNINGS,
1902 P_YTD_NON_TAXABLE_EARNINGS => l_YTD_NON_TAXABLE_EARNINGS,
1903 P_YTD_PRE_TAX_DEDUCTIONS => l_YTD_PRE_TAX_DEDUCTIONS,
1904 P_YTD_TAXABLE_EARNINGS => l_YTD_TAXABLE_EARNINGS,
1905 P_YTD_TAX => l_YTD_TAX ,
1906 P_YTD_DEDUCTIONS => l_YTD_DEDUCTIONS ,
1907 P_YTD_DIRECT_PAYMENTS => l_YTD_DIRECT_PAYMENTS,
1908 P_YTD_NET_PAYMENT => l_YTD_NET_PAYMENT ,
1909 P_YTD_EMPLOYER_CHARGES => l_YTD_EMPLOYER_CHARGES);
1910 END IF ;
1911
1912 insert into pay_action_information (
1913 action_information_id,
1914 action_context_id,
1915 action_context_type,
1916 effective_date,
1917 source_id,
1918 tax_unit_id,
1919 assignment_id,
1920 action_information_category,
1921 action_information1,
1922 action_information2,
1923 action_information3,
1924 action_information4,
1925 action_information5,
1926 action_information6,
1927 action_information7,
1928 action_information8,
1929 action_information9,
1930 action_information10)
1931 values (
1932 pay_action_information_s.nextval,
1933 p_assignment_action_id,
1934 'AAP',
1935 p_effective_date,
1936 null,
1937 null,
1938 l_assignment_id,
1939 'AU_BALANCE_RECON_DETAILS_YTD',
1940 l_YTD_TAXABLE_EARNINGS,
1941 l_YTD_NON_TAXABLE_EARNINGS,
1942 l_YTD_DEDUCTIONS,
1943 l_YTD_TAX,
1944 l_YTD_NET_PAYMENT,
1945 l_YTD_EMPLOYER_CHARGES,
1946 l_YTD_GROSS_EARNINGS,
1947 l_YTD_PRE_TAX_DEDUCTIONS,
1948 l_YTD_DIRECT_PAYMENTS,
1949 l_max_action_sequence);
1950
1951 /*Bug 4040688 - end of modification*/
1952
1953 if g_debug then
1954 hr_utility.set_location('g_prev_assignment_id......= '|| g_prev_assignment_id,10);
1955 hr_utility.set_location('g_arc_payroll_action_id......= '|| g_arc_payroll_action_id,20);
1956 hr_utility.set_location('l_max_asg_action_id......= '|| l_max_asg_action_id,20);
1957 end if;
1958
1959 -- Archive employee details
1960 /* Bug 3953702 - Archived the Grade Name details into pay_action_information */
1961
1962 /*Bug 4688872*/
1963 OPEN c_get_payroll_name(l_assignment_id,g_start_date,g_end_date);
1964 FETCH c_get_payroll_name INTO l_payroll_id, l_payroll_name;
1965 CLOSE c_get_payroll_name;
1966
1967
1968 insert into pay_action_information(
1969 action_information_id,
1970 action_context_id,
1971 action_context_type,
1972 effective_date,
1973 source_id,
1974 tax_unit_id,
1975 action_information_category,
1976 action_information1,
1977 action_information2,
1978 action_information3,
1979 action_information4,
1983 action_information8,
1980 action_information5,
1981 action_information6,
1982 action_information7,
1984 action_information9,
1985 action_information10,
1986 action_information11,
1987 assignment_id)
1988 values(
1989 pay_action_information_s.nextval,
1990 g_arc_payroll_action_id,
1991 'PA',
1992 p_effective_date,
1993 null,
1994 l_tax_unit_id,
1995 'AU_EMPLOYEE_RECON_DETAILS',
1996 csr_rec.full_name,
1997 csr_rec.assignment_number,
1998 csr_rec.actual_salary,
1999 csr_rec.grade, /* Bug 3953702*/
2000 csr_rec.normal_hours,
2001 csr_rec.actual_termination_date,
2002 l_fin_year,
2003 csr_rec.organization_name,/*Bug 4132525*/
2004 csr_rec.Legal_Employer, /*Bug 4040688, Bug 4132525*/
2005 l_payroll_name, /*Bug 4132525, Bug 4688872*/
2006 csr_rec.organization_name, /*Bug 3953706*/
2007 l_assignment_id);
2008
2009
2010 END IF;
2011
2012
2013 FOR csr_ele_det in c_element_details(g_business_group_id,l_ass_act_id)
2014 LOOP
2015
2016 -- Delete all the data that was populated due to previous action id
2017 --
2018 l_context_lst.delete;
2019 l_output_tab.delete;
2020
2021 /*Bug 3627293 - Support for Run Types */
2022 /*Bug 3935471 - Archive run balances only for master actions and for those child actions which
2023 have tax unit id different as compared to the master actions.*/
2024
2025 if csr_ele_det.master_action_id IS NOT NULL THEN
2026 /* Assignment Action is a child action,balances need not be stored */
2027 IF l_tax_unit_id = csr_ele_det.master_tax_unit_id OR g_parameters.legal_employer IS NULL THEN
2028 l_balance_flag :='N';
2029 END IF;
2030 end if;
2031
2032 --
2033 -- Insert the element data into pay_action_information table
2034 -- This Direct Insert statement is for Performance Reasons.
2035 --
2036
2037 insert into pay_action_information (
2038 action_information_id,
2039 action_context_id,
2040 action_context_type,
2041 effective_date,
2042 source_id,
2043 tax_unit_id,
2044 action_information_category,
2045 action_information1,
2046 action_information2,
2047 action_information3,
2048 action_information4,
2049 action_information5,
2050 action_information6,
2051 assignment_id)
2052 values (
2053 pay_action_information_s.nextval,
2054 p_assignment_action_id,
2055 'AAP',
2056 p_effective_date,
2057 null,
2058 l_tax_unit_id,
2059 'AU_ELEMENT_RECON_DETAILS',
2060 csr_ele_det.element_name,
2061 csr_ele_det.classification_name,
2062 null,
2063 csr_ele_det.hours,
2064 csr_ele_det.rate, /* 5599310 */
2065 csr_ele_det.payment,
2066 l_assignment_id);
2067
2068 ---
2069 END LOOP; /* Completed the Element Details Archive */
2070
2071 -- Balances Coding for BBR
2072 --
2073 -- Populate the Defined Balance IDs for the RUN and YTD dimensions
2074 -- for the required balances.
2075
2076 -- Get The Action Sequence for the Assignment_Action_Id.
2077
2078
2079
2080 /* Bug 3627293 - Archive Balances only for Master Assignment Action */
2081 if ( l_balance_flag = 'Y' )
2082 then
2083 /*Bug 3953706 - Modfied the call to procedure introduce new parameters*/
2084 pay_au_reconciliation_pkg.GET_AU_REC_BALANCES(
2085 P_ASSIGNMENT_ACTION_ID => l_ass_act_id,
2086 P_REGISTERED_EMPLOYER => g_parameters.legal_employer, --2610141
2087 P_GROSS_EARNINGS => l_GROSS_EARNINGS,
2088 P_NON_TAXABLE_EARNINGS => l_NON_TAXABLE_EARNINGS,
2089 P_PRE_TAX_DEDUCTIONS => l_PRE_TAX_DEDUCTIONS,
2090 P_TAXABLE_EARNINGS => l_TAXABLE_EARNINGS ,
2091 P_TAX => l_TAX ,
2092 P_DEDUCTIONS => l_DEDUCTIONS ,
2093 P_DIRECT_PAYMENTS => l_DIRECT_PAYMENTS,
2094 P_NET_PAYMENT => l_NET_PAYMENT ,
2095 P_EMPLOYER_CHARGES => l_EMPLOYER_CHARGES);
2096
2097
2098 --
2099 -- Insert the balance data into pay_action_information table
2100 -- This Direct Insert statement is for Performance Reasons.
2101 --
2102 /*Bug 4040688 - Modified insert statement to store run balance details.*/
2103 insert into pay_action_information (
2104 action_information_id,
2105 action_context_id,
2106 action_context_type,
2107 effective_date,
2108 source_id,
2109 tax_unit_id,
2110 assignment_id,
2111 action_information_category,
2115 action_information4,
2112 action_information1,
2113 action_information2,
2114 action_information3,
2116 action_information5,
2117 action_information6,
2118 action_information7,
2119 action_information8,
2120 action_information9,
2121 action_information10)
2122 values (
2123 pay_action_information_s.nextval,
2124 p_assignment_action_id,
2125 'AAP',
2126 p_effective_date,
2127 null,
2128 l_tax_unit_id,
2129 l_assignment_id,
2130 'AU_BALANCE_RECON_DETAILS_RUN',
2131 l_taxable_earnings,
2132 l_NON_TAXABLE_EARNINGS,
2133 l_DEDUCTIONS,
2134 l_TAX,
2135 l_NET_PAYMENT,
2136 l_EMPLOYER_CHARGES,
2137 l_GROSS_EARNINGS,
2138 l_PRE_TAX_DEDUCTIONS,
2139 l_DIRECT_PAYMENTS,
2140 l_action_sequence);
2141 end if; /*End Check for Balance Flag */
2142 END LOOP; /* End of assignments for employee */
2143
2144
2145 end archive_code;
2146
2147 --------------------------------------------------------------------+
2148 -- Name : spawn_archive_reports
2149 -- Type : Procedure
2150 -- Access: Public
2151 -- This procedure calls the Detail report
2152 -- Using the parameters passed, this proc calls the Reconciliation
2153 -- Detail report.
2154 -- This proc is called as deinitialization code of archive process.
2155
2156 --------------------------------------------------------------------+
2157
2158
2159
2160 procedure spawn_archive_reports
2161 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type)
2162 is
2163 l_count number;
2164 ps_request_id NUMBER;
2165 l_print_style VARCHAR2(2);
2166 l_print_together VARCHAR2(80);
2167 l_print_return BOOLEAN;
2168 l_procedure VARCHAR2(50);
2169 l_short_report_name VARCHAR2(30); /* 6839263 */
2170 l_xml_options BOOLEAN ; /* 6839263 */
2171
2172 --------------------------------------------------------------------+
2173 -- Cursor : csr_params
2174 -- Description : Fetches User Parameters from Legislative_paramters
2175 -- column.
2176 --------------------------------------------------------------------+
2177
2178 CURSOR csr_report_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
2179 IS
2180 SELECT pay_core_utils.get_parameter('PAY',legislative_parameters) payroll_id,
2181 pay_core_utils.get_parameter('ORG',legislative_parameters) org_id,
2182 pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
2183 to_date(pay_core_utils.get_parameter('SDATE',legislative_parameters),'YYYY/MM/DD') start_date,
2184 to_date(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD') end_date,
2185 pay_core_utils.get_parameter('PACTID',legislative_parameters) pact_id,
2186 pay_core_utils.get_parameter('LE',legislative_parameters) legal_employer,
2187 pay_core_utils.get_parameter('ASG',legislative_parameters) assignment_id,
2188 pay_core_utils.get_parameter('SO1',legislative_parameters) sort_order_1,
2189 pay_core_utils.get_parameter('SO2',legislative_parameters) sort_order_2,
2190 pay_core_utils.get_parameter('SO3',legislative_parameters) sort_order_3,
2191 pay_core_utils.get_parameter('SO4',legislative_parameters) sort_order_4,
2192 to_date(pay_core_utils.get_parameter('PEDATE',legislative_parameters),'YYYY/MM/DD') period_end_date,
2193 pay_core_utils.get_parameter('YTD_TOT',legislative_parameters) ytd_totals,
2194 pay_core_utils.get_parameter('ZERO_REC',legislative_parameters) zero_records,
2195 pay_core_utils.get_parameter('NEG_REC',legislative_parameters) negative_records,
2196 pay_core_utils.get_parameter('EMP_TYPE',legislative_parameters) employee_type,
2197 pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions, /*Bug# 4142159*/
2198 pay_core_utils.get_parameter('OUTPUT_TYPE',legislative_parameters)p_output_type /* Bug# 6839263 */
2199 FROM pay_payroll_actions ppa
2200 WHERE ppa.payroll_action_id = c_payroll_action_id;
2201
2202
2203 cursor csr_get_print_options(p_payroll_action_id NUMBER) IS
2204 SELECT printer,
2205 print_style,
2206 decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output
2207 ,number_of_copies /* Bug 4116833 */
2208 FROM pay_payroll_actions pact,
2209 fnd_concurrent_requests fcr
2210 WHERE fcr.request_id = pact.request_id
2211 AND pact.payroll_action_id = p_payroll_action_id;
2212
2213 rec_print_options csr_get_print_options%ROWTYPE;
2214
2215 l_parameters csr_report_params%ROWTYPE;
2216
2217 Begin
2218 l_count :=0;
2219 ps_request_id :=-1;
2223 l_procedure := g_package||' spawn_archive_reports';
2220 g_debug :=hr_utility.debug_enabled ;
2221
2222 if g_debug then
2224 hr_utility.set_location('Entering '||l_procedure,999);
2225 end if;
2226
2227 -- Set User Parameters for Report.
2228
2229 open csr_report_params(p_payroll_action_id);
2230 fetch csr_report_params into l_parameters;
2231 close csr_report_params;
2232
2233 /* Start Bug 6839263 */
2234 IF l_parameters.p_output_type = 'XML_PDF'
2235 THEN
2236 l_short_report_name := 'PYAURECD_XML';
2237
2238 l_xml_options := fnd_request.add_layout
2239 (template_appl_name => 'PAY',
2240 template_code => 'PYAURECD_XML',
2241 template_language => 'en',
2242 template_territory => 'US',
2243 output_format => 'PDF');
2244
2245 ELSE
2246 l_short_report_name := 'PYAURECD';
2247 END IF;
2248 /* End Bug 6839263 */
2249
2250
2251 /*Bug 3953615 -- Added the call to check parameters validations*/
2252 pay_au_reconciliation_pkg.check_report_parameters(l_parameters.start_date
2253 ,l_parameters.end_date
2254 ,l_parameters.period_end_date);
2255
2256
2257
2258 if g_debug then
2259 hr_utility.set_location('payroll_parameters.action '||p_payroll_action_id,0);
2260 hr_utility.set_location('in BG_ID '||l_parameters.business_group_id,1);
2261 hr_utility.set_location('in org_id '||l_parameters.org_id,2);
2262 hr_utility.set_location('in payroll_parameters.id '||l_parameters.payroll_id,3);
2263 hr_utility.set_location('in asg_id '||l_parameters.assignment_id,4);
2264 hr_utility.set_location('in archive start date '||to_char(l_parameters.start_date,'YYYY/MM/DD'),5);
2265 hr_utility.set_location('in archive end date '||to_char(l_parameters.end_date,'YYYY/MM/DD'),6);
2266 hr_utility.set_location('in pact_id '||l_parameters.pact_id,7);
2267 hr_utility.set_location('in legal employer '||l_parameters.legal_employer,8);
2268 hr_utility.set_location('in PERIOD END DATE '||to_char(l_parameters.period_end_date,'YYYY/MM/DD'),9);
2269 hr_utility.set_location('in YTD totals '||l_parameters.ytd_totals,10);
2270 hr_utility.set_location('in zero records'||l_parameters.zero_records,11);
2271 hr_utility.set_location('in Negative records'||l_parameters.negative_records,12);
2272 hr_utility.set_location('in emp_type '||l_parameters.employee_type,14);
2273 hr_utility.set_location('in sort order 1'||l_parameters.sort_order_1,15);
2274 hr_utility.set_location('in sort order 2'||l_parameters.sort_order_2,16);
2275 hr_utility.set_location('in sort order 3'||l_parameters.sort_order_3,17);
2276 hr_utility.set_location('in sort order 4'||l_parameters.sort_order_4,18);
2277 hr_utility.set_location('in delete action'||l_parameters.delete_actions,19); /*Bug# 4142159*/
2278 hr_utility.set_location('in Output Type '||l_parameters.p_output_type,20); /*Bug# 6939263 */
2279 end if;
2280
2281 if g_debug then
2282 hr_utility.set_location('Afer payroll action ' || p_payroll_action_id , 125);
2283
2284 hr_utility.set_location('Before calling report',24);
2285 end if;
2286
2287 OPEN csr_get_print_options(p_payroll_action_id);
2288 FETCH csr_get_print_options INTO rec_print_options;
2289 CLOSE csr_get_print_options;
2290 --
2291 l_print_together := nvl(fnd_profile.value('CONC_PRINT_TOGETHER'), 'N');
2292 --
2293 -- Set printer options
2294 l_print_return := fnd_request.set_print_options
2295 (printer => rec_print_options.printer,
2296 style => rec_print_options.print_style,
2297 copies => rec_print_options.number_of_copies, /* Bug 4116833*/
2298 save_output => hr_general.char_to_bool(rec_print_options.save_output),
2299 print_together => l_print_together);
2300 -- Submit report
2301 if g_debug then
2302 hr_utility.set_location('payroll_action id '|| p_payroll_action_id,25);
2303 end if;
2304
2305 ps_request_id := fnd_request.submit_request
2306 ('PAY',
2307 l_short_report_name,
2308 null,
2309 null,
2310 false,
2311 'P_PAYROLL_ACTION_ID='||to_char(p_payroll_action_id),
2312 'P_BUSINESS_GROUP_ID='||to_char(l_parameters.business_group_id),
2313 'P_ORGANIZATION_ID='||to_char(l_parameters.org_id),
2314 'P_PAYROLL_ID='||to_char(l_parameters.payroll_id),
2315 'P_REGISTERED_EMPLOYER='||to_char(l_parameters.legal_employer),
2316 'P_ASSIGNMENT_ID='||to_char(l_parameters.assignment_id),
2317 'P_START_DATE='||to_char(l_parameters.start_date,'YYYY/MM/DD'),
2318 'P_END_DATE='||to_char(l_parameters.end_date,'YYYY/MM/DD'),
2319 'P_PAYROLL_RUN_ID='||to_char(l_parameters.pact_id),
2320 'P_PERIOD_END_DATE='||to_char(l_parameters.period_end_date,'YYYY/MM/DD'),
2324 'P_NEGATIVE_RECORDS='||l_parameters.negative_records,
2321 'P_EMPLOYEE_TYPE='||l_parameters.employee_type,
2322 'P_YTD_TOTALS='||l_parameters.ytd_totals,
2323 'P_ZERO_RECORDS='||l_parameters.zero_records,
2325 'P_SORT_ORDER_1='||l_parameters.sort_order_1,
2326 'P_SORT_ORDER_2='||l_parameters.sort_order_2,
2327 'P_SORT_ORDER_3='||l_parameters.sort_order_3,
2328 'P_SORT_ORDER_4='||l_parameters.sort_order_4,
2329 'P_DELETE_ACTIONS='||l_parameters.delete_actions, /*Bug# 4142159*/
2330 'BLANKPAGES=NO',
2331 NULL, NULL, NULL, NULL, NULL, NULL,
2332 NULL, NULL, NULL, NULL, NULL, NULL,
2333 NULL, NULL, NULL, NULL, NULL, NULL,
2334 NULL, NULL, NULL, NULL, NULL, NULL,
2335 NULL, NULL, NULL, NULL, NULL, NULL,
2336 NULL, NULL, NULL, NULL, NULL, NULL,
2337 NULL, NULL, NULL, NULL, NULL, NULL,
2338 NULL, NULL, NULL, NULL, NULL, NULL,
2339 NULL, NULL, NULL, NULL, NULL, NULL,
2340 NULL, NULL, NULL, NULL, NULL, NULL,
2341 NULL, NULL, NULL, NULL, NULL, NULL,
2342 NULL, NULL, NULL, NULL, NULL, NULL,
2343 NULL, NULL, NULL, NULL
2344 );
2345 if g_debug then
2346 hr_utility.set_location('After calling report',24);
2347 end if;
2348
2349
2350
2351
2352
2353
2354 end spawn_archive_reports;
2355
2356 -------------------------------------------------------------------------------------------
2357 /* Bug 5603254 - Function is used to compute Hours for Elements.
2358 Function : get_element_payment_hours
2359 Description : This function is to be used for getting the Hours component paid in run.
2360
2361 Inputs : p_assignment_action_id - Assignment Action ID
2362 p_element_type_id - Element Type ID
2363 p_run_result_id - Run Result ID
2364 p_effective_date - Effective Date of Run
2365 */
2366 -------------------------------------------------------------------------------------------
2367 FUNCTION get_element_payment_hours
2368 (
2369 p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE,
2370 p_element_type_id IN pay_element_entries_f.element_entry_id%TYPE,
2371 p_run_result_id IN pay_run_results.run_result_ID%TYPE,
2372 p_effective_date IN pay_payroll_actions.effective_date%TYPE
2373 )
2374 RETURN NUMBER
2375 IS
2376
2377 l_element_type_id pay_element_types_f.element_type_id%TYPE;
2378 l_input_value_id pay_input_values_f.input_value_id%TYPE;
2379
2380 l_result number := NULL;
2381 l_temp NUMBER := NULL;
2382
2383 /* Bug 5987877 - Added Check for Input with Name - Hours */
2384 CURSOR get_hours_input_value
2385 (c_element_type_id pay_element_types_f.element_type_id%TYPE
2386 ,c_effective_date date)
2387 IS
2388 SELECT pivf.input_value_id
2389 ,pivf.name
2390 ,decode(pivf.name,'Hours',1,2) sort_index
2391 FROM pay_input_values_f pivf
2392 WHERE pivf.element_type_id = c_element_type_id
2393 AND substr(pivf.uom,1,1) = 'H'
2394 AND c_effective_date between pivf.effective_start_date and pivf.effective_end_date
2395 ORDER BY sort_index;
2396
2397 CURSOR get_hours_result_value
2398 (c_run_result_id pay_run_result_values.run_result_id%TYPE
2399 ,c_input_value_id pay_run_result_values.input_value_id%TYPE)
2400 IS
2401 SELECT prrv.result_value
2402 FROM pay_run_result_values prrv
2403 WHERE prrv.run_result_id = c_run_result_id
2404 AND prrv.input_value_id = c_input_value_id;
2405
2406 BEGIN
2407
2408 g_debug := hr_utility.debug_enabled;
2409
2410 /* Bug 5987877 - Added Check for Multiple Hours Input
2411 If Input Name = "Hours", return run result for it
2412 else sum the run results for all "H_" UOM type.
2413 */
2414 FOR csr_rec IN get_hours_input_value(p_element_type_id,p_effective_date)
2415 LOOP
2416 OPEN get_hours_result_value(p_run_result_id,csr_rec.input_value_id);
2417 FETCH get_hours_result_value INTO l_temp;
2418 CLOSE get_hours_result_value;
2419 IF csr_rec.sort_index = 1
2420 THEN
2421 l_result := l_temp;
2422 EXIT;
2423 ELSE
2424 l_result := NVL(l_result,0) + NVL(l_temp,0);
2425 END IF;
2426 END LOOP;
2427
2428
2429 /* Avoid Divide by Zero Errors when used for computing Rate, Report Hours and Rate as Null */
2430 IF l_result = 0
2431 THEN
2432 l_result := NULL;
2433 END IF;
2434
2435 RETURN l_result;
2436 END get_element_payment_hours;
2437
2438 -------------------------------------------------------------------------------------------
2439 /* Bug 5599310 - Function is used to compute Rate for Elements.
2440 Function : get_element_payment_rate
2441 Description : This function is to be used for getting the Rate component if cusomters have entered it
2442
2443 Inputs : p_assignment_action_id - Assignment Action ID
2444 p_element_type_id - Element Type ID
2445 p_run_result_id - Run Result ID
2446 p_effective_date - Effective Date of Run
2447 */
2448 -------------------------------------------------------------------------------------------
2449 FUNCTION get_element_payment_rate
2450 (
2451 p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE,
2452 p_element_type_id IN pay_element_entries_f.element_entry_id%TYPE,
2456 RETURN NUMBER
2453 p_run_result_id IN pay_run_results.run_result_ID%TYPE,
2454 p_effective_date IN pay_payroll_actions.effective_date%TYPE
2455 )
2457 IS
2458
2459 l_element_type_id pay_element_types_f.element_type_id%TYPE;
2460 l_input_value_id pay_input_values_f.input_value_id%TYPE;
2461
2462 l_result number := NULL;
2463
2464 CURSOR get_rate_input_value
2465 (c_element_type_id pay_element_types_f.element_type_id%TYPE
2466 ,c_effective_date date)
2467 IS
2468 SELECT pivf.input_value_id
2469 FROM pay_input_values_f pivf
2470 WHERE pivf.element_type_id = c_element_type_id
2471 AND upper(pivf.name) like 'RATE%'
2472 AND pivf.uom in ('N','M','I') /*bug 6109668 */
2473 AND c_effective_date between pivf.effective_start_date and pivf.effective_end_date;
2474
2475 CURSOR get_rate_result_value
2476 (c_run_result_id pay_run_result_values.run_result_id%TYPE
2477 ,c_input_value_id pay_run_result_values.input_value_id%TYPE)
2478 IS
2479 SELECT prrv.result_value
2480 FROM pay_run_result_values prrv
2481 WHERE prrv.run_result_id = c_run_result_id
2482 AND prrv.input_value_id = c_input_value_id;
2483
2484 BEGIN
2485
2486 g_debug := hr_utility.debug_enabled;
2487
2488 if g_debug then
2489 hr_utility.trace('Entering get_element_payment_rate');
2490 end if;
2491
2492 OPEN get_rate_input_value(p_element_type_id,p_effective_date);
2493 FETCH get_rate_input_value INTO l_input_value_id;
2494 CLOSE get_rate_input_value;
2495
2496 IF l_input_value_id IS NOT NULL
2497 THEN
2498 OPEN get_rate_result_value(p_run_result_id,l_input_value_id);
2499 FETCH get_rate_result_value INTO l_result;
2500 CLOSE get_rate_result_value;
2501 END IF;
2502
2503 /* Avoid Divide by Zero Errors when used for computing Rate, Report Hours and Rate as Null */
2504
2505 if g_debug then
2506 hr_utility.trace('l_result is ' || l_result);
2507 end if;
2508
2509 IF l_result = 0
2510 THEN
2511 l_result := NULL;
2512 END IF;
2513
2514
2515 if g_debug then
2516 hr_utility.trace('Leaving get_element_payment_rate');
2517 end if;
2518
2519 RETURN l_result;
2520 END get_element_payment_rate;
2521
2522
2523 end pay_au_rec_det_archive;