[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.20 2010/08/04 06:58:36 pmatamsr ship $*/
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 ***13-Feb-09 mdubasi 115.50 7590936 Replaced secure view hr_organization_units with hr_all_organization_units
81 *** in the cursor c_employee_details
82 ***11-Dec-09 dduvvuri 115.51 9113084 Added RANGE_PERSON_ID for Payroll Reconciliation Detail Report
83 ***22-Dec-09 dduvvuri 115.52 9113084 Restructured the logic in assignment_action_code to use all Range
84 *** Cursors at one place and Old cursors at one place for code clarity
85 ***04-May-10 avenkatk 115.53 9147421 Foreign Workers Reporting. Changed c_element_details cursor for new element classification.
86 ***04-Aug-10 pmatamsr 115.54 9871560 Added LEADING and INDEX hints to rg_assignment_payroll_period cursor.
87 *** ------------------------------------------------------------------------+
88 */
89
90 g_arc_payroll_action_id pay_payroll_actions.payroll_action_id%type;
91 g_business_group_id hr_all_organization_units.organization_id%type;
92 g_prev_assignment_id number;
93 g_def_bal_populted varchar2(1);
94
95 g_debug boolean ;
96
97 g_package constant varchar2(60) := 'pay_au_recon_det_archive.'; -- Global to store package name for tracing.
98 g_end_date date;
99 g_start_date date; --Bug#3662449
100
101 --------------------------------------------------------------------
102 -- Name : range_code
103 -- Type : Proedure
104 -- Access: Public
105 -- This procedure returns a sql string to select a range
106 -- of assignments eligible for archival.
107 --
108 --------------------------------------------------------------------
109
110 procedure range_code
111 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type
112 ,p_sql out NOCOPY varchar2
113 ) is
114
115 l_procedure varchar2(200) ;
116
117 begin
118
119 g_debug :=hr_utility.debug_enabled ;
120
121 if g_debug then
122 l_procedure := g_package||'range_code';
123 hr_utility.set_location('Entering '||l_procedure,1);
124 end if ;
125
126 -- Archive the payroll action level data and EIT defintions.
127 -- sql string to SELECT a range of assignments eligible for archival.
128 p_sql := ' select distinct p.person_id' ||
129 ' from per_people_f p,' ||
130 ' pay_payroll_actions pa' ||
131 ' where pa.payroll_action_id = :payroll_action_id' ||
132 ' and p.business_group_id = pa.business_group_id' ||
133 ' order by p.person_id';
134
135 if g_debug then
136 hr_utility.set_location('Leaving '||l_procedure,1000);
137 end if;
138
139 end range_code;
140
141 /*
142 Bug 9113084 - Added Function range_person_on
143 --------------------------------------------------------------------
144 Name : range_person_on
145 Type : Function
146 Access: Private
147 Description: Checks if RANGE_PERSON_ID is enabled for
148 Archive process.
149 --------------------------------------------------------------------
150 */
151
152 FUNCTION range_person_on
153 RETURN BOOLEAN
154 IS
155
156 CURSOR csr_action_parameter is
157 select parameter_value
158 from pay_action_parameters
159 where parameter_name = 'RANGE_PERSON_ID';
160
161 CURSOR csr_range_format_param is
162 select par.parameter_value
163 from pay_report_format_parameters par,
164 pay_report_format_mappings_f map
165 where map.report_format_mapping_id = par.report_format_mapping_id
166 and map.report_type = 'AU_REC_DET_ARCHIVE'
167 and map.report_format = 'AU_REC_DET_ARCHIVE'
168 and map.report_qualifier = 'AU'
169 and par.parameter_name = 'RANGE_PERSON_ID';
170
171 l_return boolean;
172 l_action_param_val varchar2(30);
173 l_report_param_val varchar2(30);
174
175 BEGIN
176
177 g_debug := hr_utility.debug_enabled;
178
179 BEGIN
180
181 open csr_action_parameter;
182 fetch csr_action_parameter into l_action_param_val;
183 close csr_action_parameter;
184
185 open csr_range_format_param;
186 fetch csr_range_format_param into l_report_param_val;
187 close csr_range_format_param;
188
189 EXCEPTION WHEN NO_DATA_FOUND THEN
190 l_return := FALSE;
191 END;
192 --
193 IF l_action_param_val = 'Y' AND l_report_param_val = 'Y' THEN
194 l_return := TRUE;
195 IF g_debug THEN
196 hr_utility.set_location('Range Person = True',1);
197 END IF;
198 ELSE
199 l_return := FALSE;
200 END IF;
201 --
202 RETURN l_return;
203 --
204 END range_person_on;
205
206 --------------------------------------------------------------------+
207 -- Name : check_termination
208 -- Type : function
209 -- Access: Public
210 -- This function is to return the assignment status
211 --------------------------------------------------------------------+
212 /*Bug#3662449 function added to check for assignment status*/
213 function check_termination
214 (p_sys_status per_assignment_status_types.per_system_status%TYPE,
215 p_emp_type varchar2)
216 return varchar2
217 is
218 l_status varchar2(10);
219 begin
220 l_status := 'FALSE';
221 if p_emp_type = 'Y' and p_sys_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN') THEN
222 l_status := 'TRUE';
223 elsif p_emp_type = 'N' and p_sys_status = 'TERM_ASSIGN' THEN
224 l_status := 'TRUE';
225 elsif p_emp_type = '%' and p_sys_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN','TERM_ASSIGN') THEN
226 l_status := 'TRUE';
227 END IF;
228 return l_status;
229
230 end check_termination;
231
232
233 --------------------------------------------------------------------+
234 -- Name : initialization_code
235 -- Type : Proedure
236 -- Access: Public
237 -- This procedure is used to set global contexts
238 --------------------------------------------------------------------+
239
240 procedure initialization_code
241 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type)
242 is
243 l_procedure varchar2(200) ;
244
245 /*Bug 4132149 - Modification begins here*/
246 --------------------------------------------------------------------+
247 -- Cursor : csr_params
248 -- Description : Fetches User Parameters from Legislative_paramters
249 -- column.
250 --------------------------------------------------------------------+
251
252 CURSOR csr_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
253 IS
254 SELECT pay_core_utils.get_parameter('PAY',legislative_parameters) payroll_id,
255 pay_core_utils.get_parameter('ORG',legislative_parameters) org_id,
256 pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
257 to_date(pay_core_utils.get_parameter('SDATE',legislative_parameters),'YYYY/MM/DD') start_date,
258 to_date(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD') end_date,
259 pay_core_utils.get_parameter('PACTID',legislative_parameters) pact_id,
260 pay_core_utils.get_parameter('LE',legislative_parameters) legal_employer,
261 pay_core_utils.get_parameter('ASG',legislative_parameters) assignment_id,
262 pay_core_utils.get_parameter('SO1',legislative_parameters) sort_order_1,
263 pay_core_utils.get_parameter('SO2',legislative_parameters) sort_order_2,
264 pay_core_utils.get_parameter('SO3',legislative_parameters) sort_order_3,
265 pay_core_utils.get_parameter('SO4',legislative_parameters) sort_order_4,
266 to_date(pay_core_utils.get_parameter('PEDATE',legislative_parameters),'YYYY/MM/DD') period_end_date,
267 pay_core_utils.get_parameter('YTD_TOT',legislative_parameters) ytd_totals,
268 pay_core_utils.get_parameter('ZERO_REC',legislative_parameters) zero_records,
269 pay_core_utils.get_parameter('NEG_REC',legislative_parameters) negative_records,
270 decode(pay_core_utils.get_parameter('EMP_TYPE',legislative_parameters),'C','Y','T','N','%') employee_type,
271 pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions /*Bug# 4142159*/
272 FROM pay_payroll_actions ppa
273 WHERE ppa.payroll_action_id = c_payroll_action_id;
274
275 --------------------------------------------------------------------+
276 -- Cursor : csr_period_date_earned
277 -- Description : Fetches Date Earned for a given payroll
278 -- run.
279 --------------------------------------------------------------------+
280 CURSOR csr_period_date_earned(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
281 IS
282 SELECT ppa.date_earned
283 FROM pay_payroll_actions ppa
284 WHERE
285 ppa.payroll_action_id = c_payroll_action_id;
286
287 /*Bug 4132149 - Modification ends here*/
288
289 begin
290
291 g_debug :=hr_utility.debug_enabled ;
292 if g_debug then
293 l_procedure := g_package||'initialization_code';
294 hr_utility.set_location('Entering '||l_procedure,1);
295 end if;
296
297
298 /*Bug 4132149 - Modification begins here*/
299
300 -- initialization_code to to set the global tables for EIT
301 -- that will be used by each thread in multi-threading.
302
303 g_arc_payroll_action_id := p_payroll_action_id;
304
305 -- Fetch the parameters by user passed into global variable.
306
307 OPEN csr_params(p_payroll_action_id);
308 FETCH csr_params into g_parameters;
309 CLOSE csr_params;
310
311
312 if g_debug then
313 hr_utility.set_location('p_payroll_action_id.........= ' || p_payroll_action_id,30);
314 hr_utility.set_location('g_parameters.business_group_id.........= ' || g_parameters.business_group_id,30);
315 hr_utility.set_location('g_parameters.payroll_id..............= ' || g_parameters.payroll_id,30);
316 hr_utility.set_location('g_parameters.org_id................= ' || g_parameters.org_id,30);
317 hr_utility.set_location('g_parameters.legal_employer.........= ' || g_parameters.legal_employer,30);
318 hr_utility.set_location('g_parameters.start_date..............= ' || g_parameters.start_date,30);
319 hr_utility.set_location('g_parameters.end_date................= ' || g_parameters.end_date,30);
320 hr_utility.set_location('g_parameters.period_end_date.........= ' || g_parameters.period_end_date,30);
321 hr_utility.set_location('g_parameters.pact_id..............= ' || g_parameters.pact_id,30);
322 hr_utility.set_location('g_parameters.employee_type..........= '||g_parameters.employee_type,30);
323 hr_utility.set_location('g_parameters.sort_order1..........= '||g_parameters.sort_order_1,30);
324 hr_utility.set_location('g_parameters.sort_order2..........= '||g_parameters.sort_order_2,30);
325 hr_utility.set_location('g_parameters.sort_order3..........= '||g_parameters.sort_order_3,30);
326 hr_utility.set_location('g_parameters.sort_order4..........= '||g_parameters.sort_order_4,30);
327 hr_utility.set_location('g_parameters.delete_actions..........= '||g_parameters.delete_actions,30);/*Bug# 4142159*/
328 end if;
329
330
331 g_business_group_id := g_parameters.business_group_id ;
332
333 -- Set end date variable .This value is used to fetch latest assignment details of
334 -- employee for archival.In case of archive start date/end date - archive end date
335 -- taken and pact_id/period_end_date , period end date is picked.
336
337 if g_parameters.end_date is not null
338 then
339 g_end_date := g_parameters.end_date;
340 g_start_date := g_parameters.start_date;
341 else
342 if g_parameters.period_end_date is not null
343 then
344 open csr_period_date_earned(g_parameters.pact_id);
345 fetch csr_period_date_earned into g_start_date;
346 close csr_period_date_earned;
347 g_end_date := g_parameters.period_end_date;
348 else
349 g_start_date := to_date('1900/01/01','YYYY/MM/DD');
350 g_end_date := to_date('4712/12/31','YYYY/MM/DD');
351 end if;
352 end if; /* End of outer if loop */
353
354 /*Bug 4132149 - Modification ends here*/
355
356 pay_au_reconciliation_pkg.populate_defined_balance_ids('Y',g_parameters.legal_employer);
357
358 if g_debug then
359 hr_utility.set_location('Leaving '||l_procedure,1000);
360 end if;
361
362 exception
363 when others then
364 hr_utility.set_location('Error in '||l_procedure,999999);
365 raise;
366 end initialization_code;
367
368 --------------------------------------------------------------------+
369 -- Name : assignment_Action_code
370 -- Type : Procedure
371 -- Access: Public
372 -- This procedure further restricts the assignment_id's
373 -- returned by range_code
374 -- This procedure gets the parameters given by user and restricts
375 -- the assignments to be archived.
376 -- it then calls hr_nonrun.insact to create an assignment action id
377 -- it then archives Payroll Run assignment action id details
378 -- in pay_Action_information with context 'AU_ARCHIVE_ASG_DETAILS'
379 -- for each assignment.
380 -- There are 10 different cursors for choosing the assignment ids.
381 -- Depending on the parameters passed,the appropriate cursor is used.
382 --------------------------------------------------------------------+
383
384 procedure assignment_action_code
385 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type
386 ,p_start_person in per_all_people_f.person_id%type
387 ,p_end_person in per_all_people_f.person_id%type
388 ,p_chunk in number
389 ) is
390
391 --------------------------------------------------------------------+
392 -- Cursor : csr_assignment_org_period
393 -- Description : Fetches assignments when Organization,Archive
394 -- Start Date and End Date is specified
395 --------------------------------------------------------------------+
396
397 cursor csr_assignment_org_period
398 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
399 ,c_start_person per_all_people_f.person_id%type
400 ,c_end_person per_all_people_f.person_id%type
401 ,c_employee_type per_all_people_f.current_employee_flag%type
402 ,c_business_group_id hr_all_organization_units.organization_id%type
403 ,c_organization_id hr_all_organization_units.organization_id%type
404 ,c_archive_start_date date
405 ,c_archive_end_date date
406 ) is
407 select paa.assignment_action_id,
408 paa.action_sequence,
409 paaf.assignment_id,
410 paa.tax_unit_id
411 from per_people_f pap,
412 per_assignments_f paaf,
413 pay_payroll_actions ppa,
414 pay_payroll_actions ppa1,
415 pay_assignment_actions paa,
416 hr_organization_units hou,
417 per_periods_of_service pps
418 -- ,per_assignment_status_types past
419 where ppa.payroll_action_id = c_payroll_action_id
420 and paa.assignment_id = paaf.assignment_id
421 and pap.person_id between c_start_person and c_end_person
422 and pap.person_id = paaf.person_id
423 and pap.person_id = pps.person_id
424 and pps.period_of_service_id = paaf.period_of_service_id
425 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
426 and ppa1.payroll_action_id = paa.payroll_action_id
427 AND paa.action_status = 'C' /*Bug 4099317*/
428 and ppa1.business_group_id = ppa.business_group_id
429 and ppa.business_group_id = c_business_group_id
430 and ppa1.action_type in ('R','Q','I','B','V')
431 and paaf.organization_id = hou.organization_id
432 and hou.business_group_id = c_business_group_id
433 and hou.organization_id = c_organization_id
434 and ppa1.effective_date between c_archive_start_date and c_archive_end_date
435 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
436 and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
437 From per_assignments_f iipaf
438 WHERE iipaf.assignment_id = paaf.assignment_id
439 and iipaf.effective_end_date >= c_archive_start_date
440 and iipaf.effective_start_date <= c_archive_end_date)
441 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
442
443 --------------------------------------------------------------------+
444 -- Bug : 9113084
445 -- Cursor : rg_csr_assignment_org_period
446 -- Description : Fetches assignments when Organization,Archive
447 -- Start Date and End Date is specified
448 -- Usage : When Range Person is enabled
449 --------------------------------------------------------------------+
450 cursor rg_csr_assignment_org_period
451 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
452 ,c_chunk number
453 ,c_employee_type per_all_people_f.current_employee_flag%type
454 ,c_business_group_id hr_all_organization_units.organization_id%type
455 ,c_organization_id hr_all_organization_units.organization_id%type
456 ,c_archive_start_date date
457 ,c_archive_end_date date
458 ) is
459 select paa.assignment_action_id,
460 paa.action_sequence,
461 paaf.assignment_id,
462 paa.tax_unit_id
463 from per_people_f pap,
464 per_assignments_f paaf,
465 pay_payroll_actions ppa,
466 pay_payroll_actions ppa1,
467 pay_assignment_actions paa,
468 hr_organization_units hou,
469 per_periods_of_service pps,
470 pay_population_ranges ppr
471 where ppa.payroll_action_id = c_payroll_action_id
472 and ppr.payroll_action_id = ppa.payroll_action_id
473 and ppr.chunk_number = c_chunk
474 and paa.assignment_id = paaf.assignment_id
475 and pap.person_id = ppr.person_id
476 and pap.person_id = paaf.person_id
477 and pap.person_id = pps.person_id
478 and pps.period_of_service_id = paaf.period_of_service_id
479 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
480 and ppa1.payroll_action_id = paa.payroll_action_id
481 AND paa.action_status = 'C'
482 and ppa1.business_group_id = ppa.business_group_id
483 and ppa.business_group_id = c_business_group_id
484 and ppa1.action_type in ('R','Q','I','B','V')
485 and paaf.organization_id = hou.organization_id
486 and hou.business_group_id = c_business_group_id
487 and hou.organization_id = c_organization_id
488 and ppa1.effective_date between c_archive_start_date and c_archive_end_date
489 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
490 and paaf.effective_end_date = (select max(effective_end_date)
491 From per_assignments_f iipaf
492 WHERE iipaf.assignment_id = paaf.assignment_id
493 and iipaf.effective_end_date >= c_archive_start_date
494 and iipaf.effective_start_date <= c_archive_end_date)
495 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
496
497
498 --------------------------------------------------------------------+
499 -- Cursor : csr_assignment_org_run
500 -- Description : Fetches assignments when Organization,Payroll Run
501 -- and Period End Date is specified
502 --------------------------------------------------------------------+
503
504 cursor csr_assignment_org_run
505 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
506 ,c_start_person per_all_people_f.person_id%type
507 ,c_end_person per_all_people_f.person_id%type
508 ,c_employee_type per_all_people_f.current_employee_flag%type
509 ,c_business_group_id hr_all_organization_units.organization_id%type
510 ,c_organization_id hr_all_organization_units.organization_id%type
511 ,c_period_end_date date
512 ,c_pact_id pay_payroll_actions.payroll_action_id%type
513 ) is
514 select paa.assignment_action_id,
515 paa.action_sequence,
516 paaf.assignment_id,
517 paa.tax_unit_id
518 from per_people_f pap,
519 per_assignments_f paaf,
520 pay_payroll_actions ppa,
521 pay_payroll_actions ppa1,
522 pay_assignment_actions paa,
523 hr_organization_units hou,
524 per_periods_of_service pps
525 where ppa.payroll_action_id = c_payroll_action_id
526 and paa.assignment_id = paaf.assignment_id
527 and pap.person_id between c_start_person and c_end_person
528 and pap.person_id = paaf.person_id
529 and pap.person_id = pps.person_id
530 and pps.period_of_service_id = paaf.period_of_service_id
531 and ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
532 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
533 and ppa1.payroll_action_id = paa.payroll_action_id
534 AND paa.action_status = 'C' /*Bug 4099317*/
535 and ppa1.business_group_id = ppa.business_group_id
536 and ppa.business_group_id = c_business_group_id
537 and ppa1.action_type in ('R','Q','I','B','V')
538 and paaf.organization_id = hou.organization_id
539 and hou.business_group_id = c_business_group_id
540 and NVL(pap.current_employee_flag,'N') like c_employee_type
541 and hou.organization_id = c_organization_id
542 and ppa1.payroll_action_id = c_pact_id
543 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
544
545 --------------------------------------------------------------------+
546 -- Bug : 9113084
547 -- Cursor : rg_csr_assignment_org_run
548 -- Description : Fetches assignments when Organization,Payroll Run
549 -- and Period End Date is specified
550 -- Usage : When Range Person is enabled
551 --------------------------------------------------------------------+
552 cursor rg_csr_assignment_org_run
553 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
554 ,c_chunk number
555 ,c_employee_type per_all_people_f.current_employee_flag%type
556 ,c_business_group_id hr_all_organization_units.organization_id%type
557 ,c_organization_id hr_all_organization_units.organization_id%type
558 ,c_period_end_date date
559 ,c_pact_id pay_payroll_actions.payroll_action_id%type
560 ) is
561 select paa.assignment_action_id,
562 paa.action_sequence,
563 paaf.assignment_id,
564 paa.tax_unit_id
565 from per_people_f pap,
566 per_assignments_f paaf,
567 pay_payroll_actions ppa,
568 pay_payroll_actions ppa1,
569 pay_assignment_actions paa,
570 hr_organization_units hou,
571 per_periods_of_service pps,
572 pay_population_ranges ppr
573 where ppa.payroll_action_id = c_payroll_action_id
574 and ppr.payroll_action_id = ppa.payroll_action_id
575 and ppr.chunk_number = c_chunk
576 and paa.assignment_id = paaf.assignment_id
577 and pap.person_id = ppr.person_id
578 and pap.person_id = paaf.person_id
579 and pap.person_id = pps.person_id
580 and pps.period_of_service_id = paaf.period_of_service_id
581 and ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
582 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
583 and ppa1.payroll_action_id = paa.payroll_action_id
584 AND paa.action_status = 'C'
585 and ppa1.business_group_id = ppa.business_group_id
586 and ppa.business_group_id = c_business_group_id
587 and ppa1.action_type in ('R','Q','I','B','V')
588 and paaf.organization_id = hou.organization_id
589 and hou.business_group_id = c_business_group_id
590 and NVL(pap.current_employee_flag,'N') like c_employee_type
591 and hou.organization_id = c_organization_id
592 and ppa1.payroll_action_id = c_pact_id
593 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
594
595 --------------------------------------------------------------------+
596 -- Cursor : csr_assignment_legal_period
597 -- Description : Fetches assignments when Legal Employer,Archive
598 -- Start Date and End Date is specified
599 --------------------------------------------------------------------+
600
601 cursor csr_assignment_legal_period
602 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
603 ,c_start_person per_all_people_f.person_id%type
604 ,c_end_person per_all_people_f.person_id%type
605 ,c_employee_type per_all_people_f.current_employee_flag%type
606 ,c_business_group_id hr_all_organization_units.organization_id%type
607 ,c_legal_employer hr_all_organization_units.organization_id%type
608 ,c_archive_start_date date
609 ,c_archive_end_date date
610 ) is
611 select paa.assignment_action_id,
612 paa.action_sequence,
613 paaf.assignment_id,
614 paa.tax_unit_id
615 from per_people_f pap,
616 per_assignments_f paaf,
617 pay_payroll_actions ppa,
618 pay_payroll_actions ppa1,
619 pay_assignment_actions paa,
620 per_periods_of_service pps
621 -- ,per_assignment_status_types past
622 where ppa.payroll_action_id = c_payroll_action_id
623 and paa.assignment_id = paaf.assignment_id
624 and pap.person_id between c_start_person and c_end_person
625 and pap.person_id = paaf.person_id
626 and pap.person_id = pps.person_id
627 and pps.period_of_service_id = paaf.period_of_service_id
628 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
629 and ppa1.payroll_action_id = paa.payroll_action_id
630 AND paa.action_status = 'C' /*Bug 4099317*/
631 and ppa1.business_group_id = ppa.business_group_id
632 and ppa.business_group_id = c_business_group_id
633 and ppa1.action_type in ('R','Q','I','B','V')
634 and paa.tax_unit_id = c_legal_employer
635 and ppa1.effective_date between c_archive_start_date and c_archive_end_date
636 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
637 and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
638 From per_assignments_f iipaf
639 WHERE iipaf.assignment_id = paaf.assignment_id
640 and iipaf.effective_end_date >= c_archive_start_date
641 and iipaf.effective_start_date <= c_archive_end_date)
642 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
643
644
645 --------------------------------------------------------------------+
646 -- Bug : 9113084
647 -- Cursor : rg_csr_assignment_legal_period
648 -- Description : Fetches assignments when Legal Employer,Archive
649 -- Start Date and End Date is specified
650 -- Usage : When Range Person is enabled
651 --------------------------------------------------------------------+
652 cursor rg_csr_assignment_legal_period
653 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
654 ,c_chunk number
655 ,c_employee_type per_all_people_f.current_employee_flag%type
656 ,c_business_group_id hr_all_organization_units.organization_id%type
657 ,c_legal_employer hr_all_organization_units.organization_id%type
658 ,c_archive_start_date date
659 ,c_archive_end_date date
660 ) is
661 select paa.assignment_action_id,
662 paa.action_sequence,
663 paaf.assignment_id,
664 paa.tax_unit_id
665 from per_people_f pap,
666 per_assignments_f paaf,
667 pay_payroll_actions ppa,
668 pay_payroll_actions ppa1,
669 pay_assignment_actions paa,
670 per_periods_of_service pps,
671 pay_population_ranges ppr
672 where ppa.payroll_action_id = c_payroll_action_id
673 and ppr.payroll_action_id = ppa.payroll_action_id
674 and ppr.chunk_number = c_chunk
675 and paa.assignment_id = paaf.assignment_id
676 and pap.person_id = ppr.person_id
677 and pap.person_id = paaf.person_id
678 and pap.person_id = pps.person_id
679 and pps.period_of_service_id = paaf.period_of_service_id
680 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
681 and ppa1.payroll_action_id = paa.payroll_action_id
682 AND paa.action_status = 'C'
683 and ppa1.business_group_id = ppa.business_group_id
684 and ppa.business_group_id = c_business_group_id
685 and ppa1.action_type in ('R','Q','I','B','V')
686 and paa.tax_unit_id = c_legal_employer
687 and ppa1.effective_date between c_archive_start_date and c_archive_end_date
688 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
689 and paaf.effective_end_date = (select max(effective_end_date)
690 From per_assignments_f iipaf
691 WHERE iipaf.assignment_id = paaf.assignment_id
692 and iipaf.effective_end_date >= c_archive_start_date
693 and iipaf.effective_start_date <= c_archive_end_date)
694 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
695
696 --------------------------------------------------------------------+
697 -- Cursor : csr_assignment_legal_run
698 -- Description : Fetches assignments when Legal Employer,Payroll Run
699 -- and Period End Date is specified
700 --------------------------------------------------------------------+
701
702
703 cursor csr_assignment_legal_run
704 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
705 ,c_start_person per_all_people_f.person_id%type
706 ,c_end_person per_all_people_f.person_id%type
707 ,c_employee_type per_all_people_f.current_employee_flag%type
708 ,c_business_group_id hr_all_organization_units.organization_id%type
709 ,c_legal_employer hr_all_organization_units.organization_id%type
710 ,c_period_end_date date
711 ,c_pact_id pay_payroll_actions.payroll_action_id%type
712 ) is
713 select paa.assignment_action_id,
714 paa.action_sequence,
715 paaf.assignment_id,
716 paa.tax_unit_id
717 from per_people_f pap,
718 per_assignments_f paaf,
719 pay_payroll_actions ppa,
720 pay_payroll_actions ppa1,
721 pay_assignment_actions paa,
722 per_periods_of_service pps
723 where ppa.payroll_action_id = c_payroll_action_id
724 and paa.assignment_id = paaf.assignment_id
725 and pap.person_id between c_start_person and c_end_person
726 and pap.person_id = paaf.person_id
727 and pap.person_id = pps.person_id
728 and pps.period_of_service_id = paaf.period_of_service_id
729 and ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
730 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
731 and ppa1.payroll_action_id = paa.payroll_action_id
732 AND paa.action_status = 'C' /*Bug 4099317*/
733 and ppa1.business_group_id = ppa.business_group_id
734 and ppa.business_group_id = c_business_group_id
735 and ppa1.action_type in ('R','Q','I','B','V')
736 and NVL(pap.current_employee_flag,'N') like c_employee_type
737 and paa.tax_unit_id = c_legal_employer
738 and ppa1.payroll_action_id = c_pact_id
739 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
740
741 --------------------------------------------------------------------+
742 -- Bug : 9113084
743 -- Cursor : rg_csr_assignment_legal_run
744 -- Description : Fetches assignments when Legal Employer,Payroll Run
745 -- and Period End Date is specified
746 -- Usage : When Range Person is enabled
747 --------------------------------------------------------------------+
748
749 cursor rg_csr_assignment_legal_run
750 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
751 , c_chunk number
752 ,c_employee_type per_all_people_f.current_employee_flag%type
753 ,c_business_group_id hr_all_organization_units.organization_id%type
754 ,c_legal_employer hr_all_organization_units.organization_id%type
755 ,c_period_end_date date
756 ,c_pact_id pay_payroll_actions.payroll_action_id%type
757 ) is
758 select paa.assignment_action_id,
759 paa.action_sequence,
760 paaf.assignment_id,
761 paa.tax_unit_id
762 from per_people_f pap,
763 per_assignments_f paaf,
764 pay_payroll_actions ppa,
765 pay_payroll_actions ppa1,
766 pay_assignment_actions paa,
767 per_periods_of_service pps,
768 pay_population_ranges ppr
769 where ppa.payroll_action_id = c_payroll_action_id
770 and ppr.payroll_action_id = ppa.payroll_action_id
771 and ppr.chunk_number = c_chunk
772 and paa.assignment_id = paaf.assignment_id
773 and pap.person_id = ppr.person_id
774 and pap.person_id = paaf.person_id
775 and pap.person_id = pps.person_id
776 and pps.period_of_service_id = paaf.period_of_service_id
777 and ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
778 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
779 and ppa1.payroll_action_id = paa.payroll_action_id
780 AND paa.action_status = 'C'
781 and ppa1.business_group_id = ppa.business_group_id
782 and ppa.business_group_id = c_business_group_id
783 and ppa1.action_type in ('R','Q','I','B','V')
784 and NVL(pap.current_employee_flag,'N') like c_employee_type
785 and paa.tax_unit_id = c_legal_employer
786 and ppa1.payroll_action_id = c_pact_id
787 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
788
789 --------------------------------------------------------------------+
790 -- Cursor : csr_assignment_payroll_period
791 -- Description : Fetches assignments when Payroll,Archive Start
792 -- Date and End Date is specified
793 --------------------------------------------------------------------+
794
795 cursor csr_assignment_payroll_period
796 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
797 ,c_start_person per_all_people_f.person_id%type
798 ,c_end_person per_all_people_f.person_id%type
799 ,c_employee_type per_all_people_f.current_employee_flag%type
800 ,c_business_group_id hr_all_organization_units.organization_id%type
801 ,c_payroll_id pay_payroll_actions.payroll_id%type
802 ,c_archive_start_date date
803 ,c_archive_end_date date
804 ) is
805 select paa.assignment_action_id,
806 paa.action_sequence,
807 paaf.assignment_id,
808 paa.tax_unit_id
809 from per_people_f pap,
810 per_assignments_f paaf,
811 pay_payroll_actions ppa,
812 pay_payroll_actions ppa1,
813 pay_assignment_actions paa,
814 per_periods_of_service pps
815 -- per_assignment_status_types past
816 where ppa.payroll_action_id = c_payroll_action_id
817 and paa.assignment_id = paaf.assignment_id
818 and pap.person_id between c_start_person and c_end_person
819 and pap.person_id = paaf.person_id
820 and pap.person_id = pps.person_id
821 and pps.period_of_service_id = paaf.period_of_service_id
822 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
823 and ppa1.payroll_action_id = paa.payroll_action_id
824 AND paa.action_status = 'C' /*Bug 4099317*/
825 and ppa1.business_group_id = ppa.business_group_id
826 and ppa.business_group_id = c_business_group_id
827 and ppa1.action_type in ('R','Q','I','B','V')
828 and ppa1.effective_date between c_archive_start_date and c_archive_end_date
829 AND paaf.payroll_id = c_payroll_id /*Bug 4040688*/
830 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
831 and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
832 From per_assignments_f iipaf
833 WHERE iipaf.assignment_id = paaf.assignment_id
834 and iipaf.effective_end_date >= c_archive_start_date
835 and iipaf.effective_start_date <= c_archive_end_date
836 AND iipaf.payroll_id IS NOT NULL) /*Bug 4688872*/
837 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
838
839 --------------------------------------------------------------------+
840 -- Bug : 9113084
841 -- Cursor : rg_assignment_payroll_period
842 -- Description : Fetches assignments when Payroll,Archive Start
843 -- Date and End Date is specified
844 -- Usage : When Range Person is enabled
845 --------------------------------------------------------------------+
846 /* Bug 9871560 - Added LEADING and INDEX hints to improve the performance of the cursor query. */
847
848 cursor rg_assignment_payroll_period
849 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
850 ,c_chunk NUMBER
851 ,c_employee_type per_all_people_f.current_employee_flag%type
852 ,c_business_group_id hr_all_organization_units.organization_id%type
853 ,c_payroll_id pay_payroll_actions.payroll_id%type
854 ,c_archive_start_date date
855 ,c_archive_end_date date
856 ) is
857 select /*+ LEADING (ppa ppr pap paaf pps) INDEX(paaf PER_ASSIGNMENTS_F_N12)*/
858 paa.assignment_action_id,
859 paa.action_sequence,
860 paaf.assignment_id,
861 paa.tax_unit_id
862 from pay_payroll_actions ppa,
863 pay_population_ranges ppr,
864 per_people_f pap,
865 per_assignments_f paaf,
866 per_periods_of_service pps,
867 pay_assignment_actions paa,
868 pay_payroll_actions ppa1
869 where ppa.payroll_action_id = c_payroll_action_id
870 and ppr.payroll_action_id = ppa.payroll_action_id
871 and ppr.chunk_number = c_chunk
872 and paa.assignment_id = paaf.assignment_id
873 and pap.person_id = ppr.person_id
874 and pap.person_id = paaf.person_id
875 and pap.person_id = pps.person_id
876 and pps.period_of_service_id = paaf.period_of_service_id
877 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
878 and ppa1.payroll_action_id = paa.payroll_action_id
879 AND paa.action_status = 'C'
880 and ppa1.business_group_id = ppa.business_group_id
881 and ppa.business_group_id = c_business_group_id
882 and ppa1.action_type in ('R','Q','I','B','V')
883 and ppa1.effective_date between c_archive_start_date and c_archive_end_date
884 AND paaf.payroll_id = c_payroll_id
885 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
886 and paaf.effective_end_date = (select max(effective_end_date)
887 From per_assignments_f iipaf
888 WHERE iipaf.assignment_id = paaf.assignment_id
889 and iipaf.effective_end_date >= c_archive_start_date
890 and iipaf.effective_start_date <= c_archive_end_date
891 AND iipaf.payroll_id IS NOT NULL)
892 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
893
894 --------------------------------------------------------------------+
895 -- Cursor : csr_assignment_payroll_run
896 -- Description : Fetches assignments when Payroll,Payroll Run
897 -- and Period End Date is specified
898 --------------------------------------------------------------------+
899
900 cursor csr_assignment_payroll_run
901 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
902 ,c_start_person per_all_people_f.person_id%type
903 ,c_end_person per_all_people_f.person_id%type
904 ,c_employee_type per_all_people_f.current_employee_flag%type
905 ,c_business_group_id hr_all_organization_units.organization_id%type
906 ,c_payroll_id pay_payroll_actions.payroll_id%type
907 ,c_period_end_date date
908 ,c_pact_id pay_payroll_actions.payroll_action_id%type
909 ) is
910 select paa.assignment_action_id,
911 paa.action_sequence,
912 paaf.assignment_id,
913 paa.tax_unit_id
914 from per_people_f pap,
915 per_assignments_f paaf,
916 pay_payroll_actions ppa,
917 pay_payroll_actions ppa1,
918 pay_assignment_actions paa,
919 per_periods_of_service pps
920 where ppa.payroll_action_id = c_payroll_action_id
921 and paa.assignment_id = paaf.assignment_id
922 and pap.person_id between c_start_person and c_end_person
923 and pap.person_id = paaf.person_id
924 and pap.person_id = pps.person_id
925 and pps.period_of_service_id = paaf.period_of_service_id
926 and ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
927 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
928 and ppa1.payroll_action_id = paa.payroll_action_id
929 AND paa.action_status = 'C' /*Bug 4099317*/
930 and ppa1.business_group_id = ppa.business_group_id
931 and ppa.business_group_id = c_business_group_id
932 and ppa1.action_type in ('R','Q','I','B','V')
933 and NVL(pap.current_employee_flag,'N') like c_employee_type
934 and ppa1.payroll_id = c_payroll_id
935 and ppa1.payroll_action_id = c_pact_id
936 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
937
938
939 --------------------------------------------------------------------+
940 -- Bug : 9113084
941 -- Cursor : rg_csr_assignment_payroll_run
942 -- Description : Fetches assignments when Payroll,Payroll Run
943 -- and Period End Date is specified
944 -- Usage : When Range Person is enabled
945 --------------------------------------------------------------------+
946
947 cursor rg_csr_assignment_payroll_run
948 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
949 , c_chunk NUMBER
950 ,c_employee_type per_all_people_f.current_employee_flag%type
951 ,c_business_group_id hr_all_organization_units.organization_id%type
952 ,c_payroll_id pay_payroll_actions.payroll_id%type
953 ,c_period_end_date date
954 ,c_pact_id pay_payroll_actions.payroll_action_id%type
955 ) is
956 select paa.assignment_action_id,
957 paa.action_sequence,
958 paaf.assignment_id,
959 paa.tax_unit_id
960 from per_people_f pap,
961 per_assignments_f paaf,
962 pay_payroll_actions ppa,
963 pay_payroll_actions ppa1,
964 pay_assignment_actions paa,
965 per_periods_of_service pps,
966 pay_population_ranges ppr
967 where ppa.payroll_action_id = c_payroll_action_id
968 and ppr.payroll_action_id = ppa.payroll_action_id
969 and ppr.chunk_number = c_chunk
970 and paa.assignment_id = paaf.assignment_id
971 and pap.person_id = ppr.person_id
972 and pap.person_id = paaf.person_id
973 and pap.person_id = pps.person_id
974 and pps.period_of_service_id = paaf.period_of_service_id
975 and ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
976 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
977 and ppa1.payroll_action_id = paa.payroll_action_id
978 AND paa.action_status = 'C'
979 and ppa1.business_group_id = ppa.business_group_id
980 and ppa.business_group_id = c_business_group_id
981 and ppa1.action_type in ('R','Q','I','B','V')
982 and NVL(pap.current_employee_flag,'N') like c_employee_type
983 and ppa1.payroll_id = c_payroll_id
984 and ppa1.payroll_action_id = c_pact_id
985 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
986
987
988 --------------------------------------------------------------------+
989 -- Cursor : csr_assignment_period
990 -- Description : Fetches assignments when Assignment,Archive Start
991 -- Date and End Date is specified
992 --------------------------------------------------------------------+
993
994 cursor csr_assignment_period
995 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
996 ,c_start_person per_all_people_f.person_id%type
997 ,c_end_person per_all_people_f.person_id%type
998 ,c_employee_type per_all_people_f.current_employee_flag%type
999 ,c_business_group_id hr_all_organization_units.organization_id%type
1000 ,c_assignment_id per_all_assignments_f.assignment_id%type
1001 ,c_archive_start_date date
1002 ,c_archive_end_date date
1003 ) is
1004 select paa.assignment_action_id,
1005 paa.action_sequence,
1006 paaf.assignment_id,
1007 paa.tax_unit_id
1008 from per_people_f pap,
1009 per_assignments_f paaf,
1010 pay_payroll_actions ppa,
1011 pay_payroll_actions ppa1,
1012 pay_assignment_actions paa,
1013 per_periods_of_service pps
1014 -- ,per_assignment_status_types past
1015 where ppa.payroll_action_id = c_payroll_action_id
1016 and paa.assignment_id = paaf.assignment_id
1017 and pap.person_id between c_start_person and c_end_person
1018 and pap.person_id = paaf.person_id
1019 and pap.person_id = pps.person_id
1020 and pps.period_of_service_id = paaf.period_of_service_id
1021 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
1022 and ppa1.payroll_action_id = paa.payroll_action_id
1023 AND paa.action_status = 'C' /*Bug 4099317*/
1024 and ppa1.business_group_id = ppa.business_group_id
1025 and ppa.business_group_id = c_business_group_id
1026 and ppa1.action_type in ('R','Q','I','B','V')
1027 and paa.assignment_id = c_assignment_id
1028 and ppa1.effective_date between c_archive_start_date and c_archive_end_date
1029 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
1030 and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
1031 From per_assignments_f iipaf
1032 WHERE iipaf.assignment_id = paaf.assignment_id
1033 and iipaf.effective_end_date >= c_archive_start_date
1034 and iipaf.effective_start_date <= c_archive_end_date)
1035 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
1036
1037 --------------------------------------------------------------------+
1038 -- Bug : 9113084
1039 -- Cursor : rg_csr_assignment_period
1040 -- Description : Fetches assignments when Assignment,Archive Start
1041 -- Date and End Date is specified
1042 -- Usage : When Range Person is enabled
1043 --------------------------------------------------------------------+
1044 cursor rg_csr_assignment_period
1045 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
1046 ,c_chunk NUMBER
1047 ,c_employee_type per_all_people_f.current_employee_flag%type
1048 ,c_business_group_id hr_all_organization_units.organization_id%type
1049 ,c_assignment_id per_all_assignments_f.assignment_id%type
1050 ,c_archive_start_date date
1051 ,c_archive_end_date date
1052 ) is
1053 select paa.assignment_action_id,
1054 paa.action_sequence,
1055 paaf.assignment_id,
1056 paa.tax_unit_id
1057 from per_people_f pap,
1058 per_assignments_f paaf,
1059 pay_payroll_actions ppa,
1060 pay_payroll_actions ppa1,
1061 pay_assignment_actions paa,
1062 per_periods_of_service pps,
1063 pay_population_ranges ppr
1064 where ppa.payroll_action_id = c_payroll_action_id
1065 and ppr.payroll_action_id = ppa.payroll_action_id
1066 and ppr.chunk_number = c_chunk
1067 and paa.assignment_id = paaf.assignment_id
1068 and pap.person_id = ppr.person_id
1069 and pap.person_id = paaf.person_id
1070 and pap.person_id = pps.person_id
1071 and pps.period_of_service_id = paaf.period_of_service_id
1072 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
1073 and ppa1.payroll_action_id = paa.payroll_action_id
1074 AND paa.action_status = 'C'
1075 and ppa1.business_group_id = ppa.business_group_id
1076 and ppa.business_group_id = c_business_group_id
1077 and ppa1.action_type in ('R','Q','I','B','V')
1078 and paa.assignment_id = c_assignment_id
1079 and ppa1.effective_date between c_archive_start_date and c_archive_end_date
1080 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
1081 and paaf.effective_end_date = (select max(effective_end_date)
1082 From per_assignments_f iipaf
1083 WHERE iipaf.assignment_id = paaf.assignment_id
1084 and iipaf.effective_end_date >= c_archive_start_date
1085 and iipaf.effective_start_date <= c_archive_end_date)
1086 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
1087
1088 -------------------------------------------------------------------+
1089 -- Cursor : csr_assignment_run
1090 -- Description : Fetches assignments when Assignment,Payroll Run
1091 -- and Period End Date is specified
1092 --------------------------------------------------------------------+
1093
1094 cursor csr_assignment_run
1095 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
1096 ,c_start_person per_all_people_f.person_id%type
1097 ,c_end_person per_all_people_f.person_id%type
1098 ,c_employee_type per_all_people_f.current_employee_flag%type
1099 ,c_business_group_id hr_all_organization_units.organization_id%type
1100 ,c_assignment_id per_all_assignments_f.assignment_id%type
1101 ,c_period_end_date date
1102 ,c_pact_id pay_payroll_actions.payroll_action_id%type
1103 ) is
1104 select paa.assignment_action_id,
1105 paa.action_sequence,
1106 paaf.assignment_id,
1107 paa.tax_unit_id
1108 from per_people_f pap,
1109 per_assignments_f paaf,
1110 pay_payroll_actions ppa,
1111 pay_payroll_actions ppa1,
1112 pay_assignment_actions paa,
1113 per_periods_of_service pps
1114 where ppa.payroll_action_id = c_payroll_action_id
1115 and paa.assignment_id = paaf.assignment_id
1116 and pap.person_id between c_start_person and c_end_person
1117 and pap.person_id = paaf.person_id
1118 and pap.person_id = pps.person_id
1119 and pps.period_of_service_id = paaf.period_of_service_id
1120 and ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
1121 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
1122 and ppa1.payroll_action_id = paa.payroll_action_id
1123 AND paa.action_status = 'C' /*Bug 4099317*/
1124 and ppa1.business_group_id = ppa.business_group_id
1125 and ppa.business_group_id = c_business_group_id
1126 and ppa1.action_type in ('R','Q','I','B','V')
1127 and NVL(pap.current_employee_flag,'N') like c_employee_type
1128 and paa.assignment_id = c_assignment_id
1129 and ppa1.payroll_action_id = c_pact_id
1130 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
1131
1132 -------------------------------------------------------------------+
1133 -- Bug : 9113084
1134 -- Cursor : rg_csr_assignment_run
1135 -- Description : Fetches assignments when Assignment,Payroll Run
1136 -- and Period End Date is specified
1137 -- Usage : When Range Person is enabled
1138 --------------------------------------------------------------------+
1139 cursor rg_csr_assignment_run
1140 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
1141 , c_chunk NUMBER
1142 ,c_employee_type per_all_people_f.current_employee_flag%type
1143 ,c_business_group_id hr_all_organization_units.organization_id%type
1144 ,c_assignment_id per_all_assignments_f.assignment_id%type
1145 ,c_period_end_date date
1146 ,c_pact_id pay_payroll_actions.payroll_action_id%type
1147 ) is
1148 select paa.assignment_action_id,
1149 paa.action_sequence,
1150 paaf.assignment_id,
1151 paa.tax_unit_id
1152 from per_people_f pap,
1153 per_assignments_f paaf,
1154 pay_payroll_actions ppa,
1155 pay_payroll_actions ppa1,
1156 pay_assignment_actions paa,
1157 per_periods_of_service pps,
1158 pay_population_ranges ppr
1159 where ppa.payroll_action_id = c_payroll_action_id
1160 and ppr.payroll_action_id = ppa.payroll_action_id
1161 and ppr.chunk_number = c_chunk
1162 and paa.assignment_id = paaf.assignment_id
1163 and pap.person_id = ppr.person_id
1164 and pap.person_id = paaf.person_id
1165 and pap.person_id = pps.person_id
1166 and pps.period_of_service_id = paaf.period_of_service_id
1167 and ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
1168 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
1169 and ppa1.payroll_action_id = paa.payroll_action_id
1170 AND paa.action_status = 'C'
1171 and ppa1.business_group_id = ppa.business_group_id
1172 and ppa.business_group_id = c_business_group_id
1173 and ppa1.action_type in ('R','Q','I','B','V')
1174 and NVL(pap.current_employee_flag,'N') like c_employee_type
1175 and paa.assignment_id = c_assignment_id
1176 and ppa1.payroll_action_id = c_pact_id
1177 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
1178
1179 --------------------------------------------------------------------+
1180 -- Cursor : csr_assignment_default_period
1181 -- Description : Fetches assignments when Archive Start date
1182 -- and End Date is specified
1183 --------------------------------------------------------------------+
1184
1185 cursor csr_assignment_default_period
1186 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
1187 ,c_start_person per_all_people_f.person_id%type
1188 ,c_end_person per_all_people_f.person_id%type
1189 ,c_employee_type per_all_people_f.current_employee_flag%type
1190 ,c_business_group_id hr_all_organization_units.organization_id%type
1191 ,c_archive_start_date date
1192 ,c_archive_end_date date
1193 ) is
1194 select paa.assignment_action_id,
1195 paa.action_sequence,
1196 paaf.assignment_id,
1197 paa.tax_unit_id
1198 from per_people_f pap,
1199 per_assignments_f paaf,
1200 pay_payroll_actions ppa,
1201 pay_payroll_actions ppa1,
1202 pay_assignment_actions paa,
1203 per_periods_of_service pps
1204 -- ,per_assignment_status_types past
1205 where ppa.payroll_action_id = c_payroll_action_id
1206 and paa.assignment_id = paaf.assignment_id
1207 and pap.person_id between c_start_person and c_end_person
1208 and pap.person_id = paaf.person_id
1209 and pap.person_id = pps.person_id
1210 and pps.period_of_service_id = paaf.period_of_service_id
1211 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
1212 and ppa1.payroll_action_id = paa.payroll_action_id
1213 AND paa.action_status = 'C' /*Bug 4099317*/
1214 and ppa1.business_group_id = ppa.business_group_id
1215 and ppa.business_group_id = c_business_group_id
1216 and ppa1.action_type in ('R','Q','I','B','V')
1217 and ppa1.effective_date between c_archive_start_date and c_archive_end_date
1218 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
1219 and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
1220 From per_assignments_f iipaf
1221 WHERE iipaf.assignment_id = paaf.assignment_id
1222 and iipaf.effective_end_date >= c_archive_start_date
1223 and iipaf.effective_start_date <= c_archive_end_date)
1224 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
1225
1226 --------------------------------------------------------------------+
1227 -- Bug : 9113084
1228 -- Cursor : rg_assignment_default_period
1229 -- Description : Fetches assignments when Archive Start date
1230 -- and End Date is specified
1231 -- Usage : When Range Person is enabled
1232 --------------------------------------------------------------------+
1233 cursor rg_assignment_default_period
1234 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
1235 ,c_chunk NUMBER
1236 ,c_employee_type per_all_people_f.current_employee_flag%type
1237 ,c_business_group_id hr_all_organization_units.organization_id%type
1238 ,c_archive_start_date date
1239 ,c_archive_end_date date
1240 ) is
1241 select paa.assignment_action_id,
1242 paa.action_sequence,
1243 paaf.assignment_id,
1244 paa.tax_unit_id
1245 from per_people_f pap,
1246 per_assignments_f paaf,
1247 pay_payroll_actions ppa,
1248 pay_payroll_actions ppa1,
1249 pay_assignment_actions paa,
1250 per_periods_of_service pps,
1251 pay_population_ranges ppr
1252 where ppa.payroll_action_id = c_payroll_action_id
1253 and ppr.payroll_action_id = ppa.payroll_action_id
1254 and ppr.chunk_number = c_chunk
1255 and paa.assignment_id = paaf.assignment_id
1256 and pap.person_id = ppr.person_id
1257 and pap.person_id = paaf.person_id
1258 and pap.person_id = pps.person_id
1259 and pps.period_of_service_id = paaf.period_of_service_id
1260 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
1261 and ppa1.payroll_action_id = paa.payroll_action_id
1262 AND paa.action_status = 'C'
1263 and ppa1.business_group_id = ppa.business_group_id
1264 and ppa.business_group_id = c_business_group_id
1265 and ppa1.action_type in ('R','Q','I','B','V')
1266 and ppa1.effective_date between c_archive_start_date and c_archive_end_date
1267 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
1268 and paaf.effective_end_date = (select max(effective_end_date)
1269 From per_assignments_f iipaf
1270 WHERE iipaf.assignment_id = paaf.assignment_id
1271 and iipaf.effective_end_date >= c_archive_start_date
1272 and iipaf.effective_start_date <= c_archive_end_date)
1273 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
1274
1275 --------------------------------------------------------------------+
1276 -- Cursor : csr_assignment_default_run
1277 -- Description : Fetches assignments when Payroll Run
1278 -- and Period End Date is specified
1279 --------------------------------------------------------------------+
1280
1281 cursor csr_assignment_default_run
1282 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
1283 ,c_start_person per_all_people_f.person_id%type
1284 ,c_end_person per_all_people_f.person_id%type
1285 ,c_employee_type per_all_people_f.current_employee_flag%type
1286 ,c_business_group_id hr_all_organization_units.organization_id%type
1287 ,c_period_end_date date
1288 ,c_pact_id pay_payroll_actions.payroll_action_id%type
1289 ) is
1290 select paa.assignment_action_id,
1291 paa.action_sequence,
1292 paaf.assignment_id,
1293 paa.tax_unit_id
1294 from per_people_f pap,
1295 per_assignments_f paaf,
1296 pay_payroll_actions ppa,
1297 pay_payroll_actions ppa1,
1298 pay_assignment_actions paa,
1299 per_periods_of_service pps
1300 where ppa.payroll_action_id = c_payroll_action_id
1301 and paa.assignment_id = paaf.assignment_id
1302 and pap.person_id between c_start_person and c_end_person
1303 and pap.person_id = paaf.person_id
1304 and pap.person_id = pps.person_id
1305 and pps.period_of_service_id = paaf.period_of_service_id
1306 and ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
1307 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
1308 and ppa1.payroll_action_id = paa.payroll_action_id
1309 AND paa.action_status = 'C' /*Bug 4099317*/
1310 and ppa1.business_group_id = ppa.business_group_id
1311 and ppa.business_group_id = c_business_group_id
1312 and ppa1.action_type in ('R','Q','I','B','V')
1313 and NVL(pap.current_employee_flag,'N') like c_employee_type
1314 and ppa1.payroll_action_id = c_pact_id
1315 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
1316
1317
1318 --------------------------------------------------------------------+
1319 -- Bug : 9113084
1320 -- Cursor : rg_csr_assignment_default_run
1321 -- Description : Fetches assignments when Payroll Run
1322 -- and Period End Date is specified
1323 -- Usage : When Range Person is enabled
1324 --------------------------------------------------------------------+
1325 cursor rg_csr_assignment_default_run
1326 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
1327 , c_chunk NUMBER
1328 ,c_employee_type per_all_people_f.current_employee_flag%type
1329 ,c_business_group_id hr_all_organization_units.organization_id%type
1330 ,c_period_end_date date
1331 ,c_pact_id pay_payroll_actions.payroll_action_id%type
1332 ) is
1333 select paa.assignment_action_id,
1334 paa.action_sequence,
1335 paaf.assignment_id,
1336 paa.tax_unit_id
1337 from per_people_f pap,
1338 per_assignments_f paaf,
1339 pay_payroll_actions ppa,
1340 pay_payroll_actions ppa1,
1341 pay_assignment_actions paa,
1342 per_periods_of_service pps,
1343 pay_population_ranges ppr
1344 where ppa.payroll_action_id = c_payroll_action_id
1345 and ppr.payroll_action_id = ppa.payroll_action_id
1346 and ppr.chunk_number = c_chunk
1347 and paa.assignment_id = paaf.assignment_id
1348 and pap.person_id = ppr.person_id
1349 and pap.person_id = paaf.person_id
1350 and pap.person_id = pps.person_id
1351 and pps.period_of_service_id = paaf.period_of_service_id
1352 and ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
1353 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
1354 and ppa1.payroll_action_id = paa.payroll_action_id
1355 AND paa.action_status = 'C'
1356 and ppa1.business_group_id = ppa.business_group_id
1357 and ppa.business_group_id = c_business_group_id
1358 and ppa1.action_type in ('R','Q','I','B','V')
1359 and NVL(pap.current_employee_flag,'N') like c_employee_type
1360 and ppa1.payroll_action_id = c_pact_id
1361 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
1362
1363 --------------------------------------------------------------------+
1364 -- Cursor : csr_params
1365 -- Description : Fetches User Parameters from Legislative_paramters
1366 -- column.
1367 --------------------------------------------------------------------+
1368
1369 CURSOR csr_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
1370 IS
1371 SELECT pay_core_utils.get_parameter('PAY',legislative_parameters) payroll_id,
1372 pay_core_utils.get_parameter('ORG',legislative_parameters) org_id,
1373 pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
1374 to_date(pay_core_utils.get_parameter('SDATE',legislative_parameters),'YYYY/MM/DD') start_date,
1375 to_date(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD') end_date,
1376 pay_core_utils.get_parameter('PACTID',legislative_parameters) pact_id,
1377 pay_core_utils.get_parameter('LE',legislative_parameters) legal_employer,
1378 pay_core_utils.get_parameter('ASG',legislative_parameters) assignment_id,
1379 pay_core_utils.get_parameter('SO1',legislative_parameters) sort_order_1,
1380 pay_core_utils.get_parameter('SO2',legislative_parameters) sort_order_2,
1381 pay_core_utils.get_parameter('SO3',legislative_parameters) sort_order_3,
1382 pay_core_utils.get_parameter('SO4',legislative_parameters) sort_order_4,
1383 to_date(pay_core_utils.get_parameter('PEDATE',legislative_parameters),'YYYY/MM/DD') period_end_date,
1384 pay_core_utils.get_parameter('YTD_TOT',legislative_parameters) ytd_totals,
1385 pay_core_utils.get_parameter('ZERO_REC',legislative_parameters) zero_records,
1386 pay_core_utils.get_parameter('NEG_REC',legislative_parameters) negative_records,
1387 decode(pay_core_utils.get_parameter('EMP_TYPE',legislative_parameters),'C','Y','T','N','%') employee_type,
1388 pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions /*Bug# 4142159*/
1389 FROM pay_payroll_actions ppa
1390 WHERE ppa.payroll_action_id = c_payroll_action_id;
1391
1392 --------------------------------------------------------------------+
1393 -- Cursor : csr_period_date_earned
1394 -- Description : Fetches Date Earned for a given payroll
1395 -- run.
1396 --------------------------------------------------------------------+
1397 /*Bug#3662449 *********/
1398 CURSOR csr_period_date_earned(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
1399 IS
1400 SELECT ppa.date_earned
1401 FROM pay_payroll_actions ppa
1402 WHERE
1403 ppa.payroll_action_id = c_payroll_action_id;
1404
1405
1406 cursor csr_next_action_id is
1407 select pay_assignment_actions_s.nextval
1408 from dual;
1409
1410 l_next_assignment_action_id pay_assignment_actions.assignment_action_id%type;
1411 l_procedure varchar2(200) ;
1412 i number;
1413
1414 l_action_information_id number;
1415 l_object_version_number number;
1416
1417
1418 begin
1419 i := 1;
1420 g_debug :=hr_utility.debug_enabled ;
1421
1422 if g_debug then
1423 l_procedure := g_package||'assignment_action_code';
1424 hr_utility.set_location('Entering ' || l_procedure,1);
1425 hr_utility.set_location('Entering assignment_Action_code',302);
1426 end if;
1427
1428 -- initialization_code to to set the global tables for EIT
1429 -- that will be used by each thread in multi-threading.
1430
1431 g_arc_payroll_action_id := p_payroll_action_id;
1432
1433 -- Fetch the parameters by user passed into global variable.
1434
1435 OPEN csr_params(p_payroll_action_id);
1436 FETCH csr_params into g_parameters;
1437 CLOSE csr_params;
1438
1439
1440 if g_debug then
1441 hr_utility.set_location('p_payroll_action_id.........= ' || p_payroll_action_id,30);
1442 hr_utility.set_location('p_start_person..............= ' || p_start_person,30);
1443 hr_utility.set_location('p_end_person................= ' || p_end_person,30);
1444 hr_utility.set_location('g_parameters.business_group_id.........= ' || g_parameters.business_group_id,30);
1445 hr_utility.set_location('g_parameters.payroll_id..............= ' || g_parameters.payroll_id,30);
1446 hr_utility.set_location('g_parameters.org_id................= ' || g_parameters.org_id,30);
1447 hr_utility.set_location('g_parameters.legal_employer.........= ' || g_parameters.legal_employer,30);
1448 hr_utility.set_location('g_parameters.start_date..............= ' || g_parameters.start_date,30);
1449 hr_utility.set_location('g_parameters.end_date................= ' || g_parameters.end_date,30);
1450 hr_utility.set_location('g_parameters.period_end_date.........= ' || g_parameters.period_end_date,30);
1451 hr_utility.set_location('g_parameters.pact_id..............= ' || g_parameters.pact_id,30);
1452 hr_utility.set_location('g_parameters.employee_type..........= '||g_parameters.employee_type,30);
1453 hr_utility.set_location('g_parameters.sort_order1..........= '||g_parameters.sort_order_1,30);
1454 hr_utility.set_location('g_parameters.sort_order2..........= '||g_parameters.sort_order_2,30);
1455 hr_utility.set_location('g_parameters.sort_order3..........= '||g_parameters.sort_order_3,30);
1456 hr_utility.set_location('g_parameters.sort_order4..........= '||g_parameters.sort_order_4,30);
1457 hr_utility.set_location('g_parameters.delete_actions..........= '||g_parameters.delete_actions,30);/*Bug# 4142159*/
1458 end if;
1459
1460
1461 g_business_group_id := g_parameters.business_group_id ;
1462
1463 -- Set end date variable .This value is used to fetch latest assignment details of
1464 -- employee for archival.In case of archive start date/end date - archive end date
1465 -- taken and pact_id/period_end_date , period end date is picked.
1466
1467 if g_parameters.end_date is not null
1468 then
1469 g_end_date := g_parameters.end_date;
1470 g_start_date := g_parameters.start_date; --Bug#3662449
1471 else
1472 if g_parameters.period_end_date is not null
1473 then
1474 open csr_period_date_earned(g_parameters.pact_id); --Bug#3662449
1475 fetch csr_period_date_earned into g_start_date;
1476 close csr_period_date_earned;
1477 g_end_date := g_parameters.period_end_date;
1478 else
1479 g_start_date := to_date('1900/01/01','YYYY/MM/DD'); --Bug#3662449
1480 g_end_date := to_date('4712/12/31','YYYY/MM/DD');
1481 end if;
1482 end if; /* End of outer if loop */
1483
1484
1485 IF range_person_on THEN /* 9113084 - Use Range Person Cursors when RANGE_PERSON_ID is enabled */
1486
1487 if g_parameters.org_id is not null
1488 then
1489 if g_parameters.start_date is not null and g_parameters.end_date is not null
1490 then
1491 IF g_debug THEN
1492 hr_utility.set_location('Using Range Person Cursor for fetching assignments',30);
1493 END IF;
1494 FOR csr_rec in rg_csr_assignment_org_period(p_payroll_action_id,
1495 p_chunk,
1496 g_parameters.employee_type,
1497 g_parameters.business_group_id,
1498 g_parameters.org_id,
1499 g_parameters.start_date,
1500 g_parameters.end_date)
1501 LOOP /*Loop 1 Org,Archive start date,end date */
1502 open csr_next_action_id;
1503 fetch csr_next_action_id into l_next_assignment_action_id;
1504 close csr_next_action_id;
1505
1506 if g_debug then
1507
1508 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
1509 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
1510 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
1511 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
1512
1513 end if;
1514
1515 -- Create the archive assignment actions
1516 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1517
1518
1519 insert into pay_action_information(
1520 action_information_id,
1521 action_context_id,
1522 action_context_type,
1523 effective_date,
1524 source_id,
1525 tax_unit_id,
1526 action_information_category,
1527 action_information1,
1528 action_information2,
1529 action_information3,
1530 assignment_id
1531 )
1532 values(
1533 pay_action_information_s.nextval,
1534 l_next_assignment_action_id,
1535 'AAP',
1536 null,
1537 null,
1538 csr_rec.tax_unit_id,
1539 'AU_ARCHIVE_ASG_DETAILS',
1540 csr_rec.assignment_action_id,
1541 p_payroll_action_id,
1542 csr_rec.action_sequence,
1543 csr_rec.assignment_id
1544 );
1545
1546
1547 END LOOP;/* Loop 1 */
1548 if g_debug then
1549 hr_utility.set_location('Leaving............Loop1 Org+Period....' || l_procedure,1000);
1550 end if;
1551
1552 else
1553 IF g_debug THEN
1554 hr_utility.set_location('Using Range Person Cursor for fetching assignments',30);
1555 END IF;
1556 FOR csr_rec in rg_csr_assignment_org_run(p_payroll_action_id,
1557 p_chunk,
1558 g_parameters.employee_type,
1559 g_parameters.business_group_id,
1560 g_parameters.org_id,
1561 g_parameters.period_end_date,
1562 g_parameters.pact_id)
1563 LOOP /*Loop 2 Org,Pact_id and period end date*/
1564 open csr_next_action_id;
1565 fetch csr_next_action_id into l_next_assignment_action_id;
1566 close csr_next_action_id;
1567
1568 if g_debug then
1569 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
1570 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
1571 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
1572 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
1573 end if;
1574
1575 -- Create the archive assignment actions
1576 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1577
1578 insert into pay_action_information(
1579 action_information_id,
1580 action_context_id,
1581 action_context_type,
1582 effective_date,
1583 source_id,
1584 tax_unit_id,
1585 action_information_category,
1586 action_information1,
1587 action_information2,
1588 action_information3,
1589 assignment_id
1590 )
1591 values(
1592 pay_action_information_s.nextval,
1593 l_next_assignment_action_id,
1594 'AAP',
1595 null,
1596 null,
1597 csr_rec.tax_unit_id,
1598 'AU_ARCHIVE_ASG_DETAILS',
1599 csr_rec.assignment_action_id,
1600 p_payroll_action_id,
1601 csr_rec.action_sequence,
1602 csr_rec.assignment_id
1603 );
1604
1605
1606 END LOOP; /* Loop 2 */
1607 if g_debug then
1608 hr_utility.set_location('Leaving............Loop2 ,Org + Run....' || l_procedure,1000);
1609 end if;
1610 end if; /* End of Inner Organization */
1611 else /* Not Org,check for others */
1612
1613 if g_parameters.legal_employer is not null
1614 then
1615 if g_parameters.start_date is not null and g_parameters.end_date is not null
1616 then
1617 IF g_debug THEN
1618 hr_utility.set_location('Using Range Person Cursor for fetching assignments',30);
1619 END IF;
1620 FOR csr_rec in rg_csr_assignment_legal_period(p_payroll_action_id,
1621 p_chunk,
1622 g_parameters.employee_type,
1623 g_parameters.business_group_id,
1624 g_parameters.legal_employer,
1625 g_parameters.start_date,
1626 g_parameters.end_date)
1627 LOOP /*Loop 3 Leg Employer,Archive Start date,archive end date*/
1628 open csr_next_action_id;
1629 fetch csr_next_action_id into l_next_assignment_action_id;
1630 close csr_next_action_id;
1631 if g_debug then
1632 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
1633 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
1634 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
1635 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
1636 end if;
1637
1638 -- Create the archive assignment actions
1639 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1640
1641 insert into pay_action_information(
1642 action_information_id,
1643 action_context_id,
1644 action_context_type,
1645 effective_date,
1646 source_id,
1647 tax_unit_id,
1648 action_information_category,
1649 action_information1,
1650 action_information2,
1651 action_information3,
1652 assignment_id
1653 )
1654 values(
1655 pay_action_information_s.nextval,
1656 l_next_assignment_action_id,
1657 'AAP',
1658 null,
1659 null,
1660 csr_rec.tax_unit_id,
1661 'AU_ARCHIVE_ASG_DETAILS',
1662 csr_rec.assignment_action_id,
1663 p_payroll_action_id,
1664 csr_rec.action_sequence,
1665 csr_rec.assignment_id
1666 );
1667
1668
1669 END LOOP;/* Loop 3 */
1670 if g_debug then
1671 hr_utility.set_location('Leaving............Loop3.Legal Emp + period...' || l_procedure,1000);
1672 end if;
1673
1674 else
1675 IF g_debug THEN
1676 hr_utility.set_location('Using Range Person Cursor for fetching assignments',30);
1677 END IF;
1678 FOR csr_rec in rg_csr_assignment_legal_run(p_payroll_action_id,
1679 p_chunk,
1680 g_parameters.employee_type,
1681 g_parameters.business_group_id,
1682 g_parameters.legal_employer,
1683 g_parameters.period_end_date,
1684 g_parameters.pact_id)
1685 LOOP /*Loop 4 Leg employer,pact_id + period end date */
1686 open csr_next_action_id;
1687 fetch csr_next_action_id into l_next_assignment_action_id;
1688 close csr_next_action_id;
1689
1690 if g_debug then
1691 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
1692 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
1693 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
1694 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
1695 end if;
1696
1697 -- Create the archive assignment actions
1698 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1699
1700 insert into pay_action_information(
1701 action_information_id,
1702 action_context_id,
1703 action_context_type,
1704 effective_date,
1705 source_id,
1706 tax_unit_id,
1707 action_information_category,
1708 action_information1,
1709 action_information2,
1710 action_information3,
1711 assignment_id
1712 )
1713 values(
1714 pay_action_information_s.nextval,
1715 l_next_assignment_action_id,
1716 'AAP',
1717 null,
1718 null,
1719 csr_rec.tax_unit_id,
1720 'AU_ARCHIVE_ASG_DETAILS',
1721 csr_rec.assignment_action_id,
1722 p_payroll_action_id,
1723 csr_rec.action_sequence,
1724 csr_rec.assignment_id
1725 );
1726
1727
1728 END LOOP; /* Loop 4 */
1729 if g_debug then
1730 hr_utility.set_location('Leaving............Loop4.Legal Emp + Run...' || l_procedure,1000);
1731 end if;
1732 end if; /* End of Inner Legal Employer */
1733 else /* Not Org,Legal Emp Check others */
1734
1735 if g_parameters.payroll_id is not null
1736 then
1737 if g_parameters.start_date is not null and g_parameters.end_date is not null
1738 then
1739 IF g_debug THEN
1740 hr_utility.set_location('Using Range Person Cursor for fetching assignments',30);
1741 END IF;
1742 FOR csr_rec in rg_assignment_payroll_period(p_payroll_action_id,
1743 p_chunk,
1744 g_parameters.employee_type,
1745 g_parameters.business_group_id,
1746 g_parameters.payroll_id,
1747 g_parameters.start_date,
1748 g_parameters.end_date)
1749 LOOP /*Loop 5 Payroll, Archive start date,end date */
1750 open csr_next_action_id;
1751 fetch csr_next_action_id into l_next_assignment_action_id;
1752 close csr_next_action_id;
1753
1754 if g_debug then
1755 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
1756 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
1757 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
1758 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
1759 end if;
1760
1761 -- Create the archive assignment actions
1762 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1763
1764 insert into pay_action_information(
1765 action_information_id,
1766 action_context_id,
1767 action_context_type,
1768 effective_date,
1769 source_id,
1770 tax_unit_id,
1771 action_information_category,
1772 action_information1,
1773 action_information2,
1774 action_information3,
1775 assignment_id
1776 )
1777 values(
1778 pay_action_information_s.nextval,
1779 l_next_assignment_action_id,
1780 'AAP',
1781 null,
1782 null,
1783 csr_rec.tax_unit_id,
1784 'AU_ARCHIVE_ASG_DETAILS',
1785 csr_rec.assignment_action_id,
1786 p_payroll_action_id,
1787 csr_rec.action_sequence,
1788 csr_rec.assignment_id
1789 );
1790
1791
1792 END LOOP;/* Loop 5 */
1793
1794 if g_debug then
1795 hr_utility.set_location('Leaving............Loop5 Payroll + Period....' || l_procedure,1000);
1796 end if;
1797
1798 else
1799 IF g_debug THEN
1800 hr_utility.set_location('Using Range Person Cursor for fetching assignments',30);
1801 END IF;
1802 FOR csr_rec in rg_csr_assignment_payroll_run(p_payroll_action_id,
1803 p_chunk,
1804 g_parameters.employee_type,
1805 g_parameters.business_group_id,
1806 g_parameters.payroll_id,
1807 g_parameters.period_end_date,
1808 g_parameters.pact_id)
1809 LOOP /*Loop 6 Payroll, pact_id + period end date*/
1810 open csr_next_action_id;
1811 fetch csr_next_action_id into l_next_assignment_action_id;
1812 close csr_next_action_id;
1813
1814 if g_debug then
1815 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
1816 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
1817 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
1818 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
1819 end if;
1820
1821 -- Create the archive assignment actions
1822 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1823
1824 insert into pay_action_information(
1825 action_information_id,
1826 action_context_id,
1827 action_context_type,
1828 effective_date,
1829 source_id,
1830 tax_unit_id,
1831 action_information_category,
1832 action_information1,
1833 action_information2,
1834 action_information3,
1835 assignment_id
1836 )
1837 values(
1838 pay_action_information_s.nextval,
1839 l_next_assignment_action_id,
1840 'AAP',
1841 null,
1842 null,
1843 csr_rec.tax_unit_id,
1844 'AU_ARCHIVE_ASG_DETAILS',
1845 csr_rec.assignment_action_id,
1846 p_payroll_action_id,
1847 csr_rec.action_sequence,
1848 csr_rec.assignment_id
1849 );
1850
1851
1852 END LOOP; /* Loop 6 */
1853 if g_debug then
1854 hr_utility.set_location('Leaving............Loop6 Payroll+ Run....' || l_procedure,1000);
1855 end if;
1856 end if; /* End of Inner Payroll */
1857 else /* Not Org,Legal,Payroll check others */
1858
1859 if g_parameters.assignment_id is not null
1860 then
1861 if g_parameters.start_date is not null and g_parameters.end_date is not null
1862 then
1863 IF g_debug THEN
1864 hr_utility.set_location('Using Range Person Cursor for fetching assignments',30);
1865 END IF;
1866 FOR csr_rec in rg_csr_assignment_period(p_payroll_action_id,
1867 p_chunk,
1868 g_parameters.employee_type,
1869 g_parameters.business_group_id,
1870 g_parameters.assignment_id,
1871 g_parameters.start_date,
1872 g_parameters.end_date)
1873 LOOP /*Loop 7 Assignment ,Archive start date,end date*/
1874 open csr_next_action_id;
1875 fetch csr_next_action_id into l_next_assignment_action_id;
1876 close csr_next_action_id;
1877
1878 if g_debug then
1879 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
1880 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
1881 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
1882 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
1883 end if;
1884
1885 -- Create the archive assignment actions
1886 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1887
1888 insert into pay_action_information(
1889 action_information_id,
1890 action_context_id,
1891 action_context_type,
1892 effective_date,
1893 source_id,
1894 tax_unit_id,
1895 action_information_category,
1896 action_information1,
1897 action_information2,
1898 action_information3,
1899 assignment_id
1900 )
1901 values(
1902 pay_action_information_s.nextval,
1903 l_next_assignment_action_id,
1904 'AAP',
1905 null,
1906 null,
1907 csr_rec.tax_unit_id,
1908 'AU_ARCHIVE_ASG_DETAILS',
1909 csr_rec.assignment_action_id,
1910 p_payroll_action_id,
1911 csr_rec.action_sequence,
1912 csr_rec.assignment_id
1913 );
1914
1915
1916 END LOOP;/* Loop 7 */
1917 if g_debug then
1918 hr_utility.set_location('Leaving............Loop7. Asg + Period...' || l_procedure,1000);
1919 end if;
1920
1921 else
1922 IF g_debug THEN
1923 hr_utility.set_location('Using Range Person Cursor for fetching assignments',30);
1924 END IF;
1925 FOR csr_rec in rg_csr_assignment_run(p_payroll_action_id,
1926 p_chunk,
1927 g_parameters.employee_type,
1928 g_parameters.business_group_id,
1929 g_parameters.assignment_id,
1930 g_parameters.period_end_date,
1931 g_parameters.pact_id)
1932 LOOP /*Loop 8 Assignment Pact_id,Period end date */
1933 open csr_next_action_id;
1934 fetch csr_next_action_id into l_next_assignment_action_id;
1935 close csr_next_action_id;
1936
1937 if g_debug then
1938 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
1939 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
1940 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
1941 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
1942 end if;
1943
1944 -- Create the archive assignment actions
1945 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1946
1947 insert into pay_action_information(
1948 action_information_id,
1949 action_context_id,
1950 action_context_type,
1951 effective_date,
1952 source_id,
1953 tax_unit_id,
1954 action_information_category,
1955 action_information1,
1956 action_information2,
1957 action_information3,
1958 assignment_id
1959 )
1960 values(
1961 pay_action_information_s.nextval,
1962 l_next_assignment_action_id,
1963 'AAP',
1964 null,
1965 null,
1966 csr_rec.tax_unit_id,
1967 'AU_ARCHIVE_ASG_DETAILS',
1968 csr_rec.assignment_action_id,
1969 p_payroll_action_id,
1970 csr_rec.action_sequence,
1971 csr_rec.assignment_id
1972 );
1973
1974
1975 END LOOP; /* Loop 8 */
1976 if g_debug then
1977 hr_utility.set_location('Leaving............Loop8.Asg + Run...' || l_procedure,1000);
1978 end if;
1979 end if; /* End of Inner Assignment */
1980
1981 else
1982
1983 /* Default Begins */
1984
1985 if g_parameters.start_date is not null and g_parameters.end_date is not null
1986 then
1987 IF g_debug THEN
1988 hr_utility.set_location('Using Range Person Cursor for fetching assignments',30);
1989 END IF;
1990 FOR csr_rec in rg_assignment_default_period(p_payroll_action_id,
1991 p_chunk,
1992 g_parameters.employee_type,
1993 g_parameters.business_group_id,
1994 g_parameters.start_date,
1995 g_parameters.end_date)
1996 LOOP /*Loop 9*/
1997 open csr_next_action_id;
1998 fetch csr_next_action_id into l_next_assignment_action_id;
1999 close csr_next_action_id;
2000
2001 if g_debug then
2002 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
2003 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
2004 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
2005 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
2006 end if;
2007
2008 -- Create the archive assignment actions
2009 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
2010
2011 insert into pay_action_information(
2012 action_information_id,
2013 action_context_id,
2014 action_context_type,
2015 effective_date,
2016 source_id,
2017 tax_unit_id,
2018 action_information_category,
2019 action_information1,
2020 action_information2,
2021 action_information3,
2022 assignment_id
2023 )
2024 values(
2025 pay_action_information_s.nextval,
2026 l_next_assignment_action_id,
2027 'AAP',
2028 null,
2029 null,
2030 csr_rec.tax_unit_id,
2031 'AU_ARCHIVE_ASG_DETAILS',
2032 csr_rec.assignment_action_id,
2033 p_payroll_action_id,
2034 csr_rec.action_sequence,
2035 csr_rec.assignment_id
2036 );
2037
2038
2039 END LOOP;/* Loop 9 */
2040 if g_debug then
2041 hr_utility.set_location('Leaving............Loop9..Default + Period..' || l_procedure,1000);
2042 end if;
2043
2044 else
2045 IF g_debug THEN
2046 hr_utility.set_location('Using Range Person Cursor for fetching assignments',30);
2047 END IF;
2048 FOR csr_rec in rg_csr_assignment_default_run(p_payroll_action_id,
2049 p_chunk,
2050 g_parameters.employee_type,
2051 g_parameters.business_group_id,
2052 g_parameters.period_end_date,
2053 g_parameters.pact_id)
2054 LOOP /*Loop 10 */
2055 open csr_next_action_id;
2056 fetch csr_next_action_id into l_next_assignment_action_id;
2057 close csr_next_action_id;
2058
2059 if g_debug then
2060 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
2061 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
2062 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
2063 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
2064 end if;
2065
2066 -- Create the archive assignment actions
2067 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
2068
2069 insert into pay_action_information(
2070 action_information_id,
2071 action_context_id,
2072 action_context_type,
2073 effective_date,
2074 source_id,
2075 tax_unit_id,
2076 action_information_category,
2077 action_information1,
2078 action_information2,
2079 action_information3,
2080 assignment_id
2081 )
2082 values(
2083 pay_action_information_s.nextval,
2084 l_next_assignment_action_id,
2085 'AAP',
2086 null,
2087 null,
2088 csr_rec.tax_unit_id,
2089 'AU_ARCHIVE_ASG_DETAILS',
2090 csr_rec.assignment_action_id,
2091 p_payroll_action_id,
2092 csr_rec.action_sequence,
2093 csr_rec.assignment_id
2094 );
2095
2096
2097 END LOOP; /* Loop 10 */
2098 if g_debug then
2099 hr_utility.set_location('Leaving............Loop10 Default + Run....' || l_procedure,1000);
2100 end if;
2101 end if; /* End of Inner Default */
2102
2103
2104 end if ;/*End Assignment id */
2105 end if ; /* End Payroll */
2106 end if; /* End Legal */
2107 end if; /* End Organization */
2108
2109 ELSE /* 9113084 - Use Old logic when RANGE_PERSON_ID is disabled */
2110
2111 if g_parameters.org_id is not null
2112 then
2113 if g_parameters.start_date is not null and g_parameters.end_date is not null
2114 then
2115 FOR csr_rec in csr_assignment_org_period(p_payroll_action_id,
2116 p_start_person,
2117 p_end_person,
2118 g_parameters.employee_type,
2119 g_parameters.business_group_id,
2120 g_parameters.org_id,
2121 g_parameters.start_date,
2122 g_parameters.end_date)
2123 LOOP /*Loop 1 Org,Archive start date,end date */
2124 open csr_next_action_id;
2125 fetch csr_next_action_id into l_next_assignment_action_id;
2126 close csr_next_action_id;
2127
2128 if g_debug then
2129
2130 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
2131 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
2132 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
2133 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
2134
2135 end if;
2136
2137
2138
2139 -- Create the archive assignment actions
2140 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
2141
2142
2143 insert into pay_action_information(
2144 action_information_id,
2145 action_context_id,
2146 action_context_type,
2147 effective_date,
2148 source_id,
2149 tax_unit_id,
2150 action_information_category,
2151 action_information1,
2152 action_information2,
2153 action_information3,
2154 assignment_id
2155 )
2156 values(
2157 pay_action_information_s.nextval,
2158 l_next_assignment_action_id,
2159 'AAP',
2160 null,
2161 null,
2162 csr_rec.tax_unit_id,
2163 'AU_ARCHIVE_ASG_DETAILS',
2164 csr_rec.assignment_action_id,
2165 p_payroll_action_id,
2166 csr_rec.action_sequence,
2167 csr_rec.assignment_id
2168 );
2169
2170
2171 END LOOP;/* Loop 1 */
2172 if g_debug then
2173 hr_utility.set_location('Leaving............Loop1 Org+Period....' || l_procedure,1000);
2174 end if;
2175
2176 else
2177 FOR csr_rec in csr_assignment_org_run(p_payroll_action_id,
2178 p_start_person,
2179 p_end_person,
2180 g_parameters.employee_type,
2181 g_parameters.business_group_id,
2182 g_parameters.org_id,
2183 g_parameters.period_end_date,
2184 g_parameters.pact_id)
2185 LOOP /*Loop 2 Org,Pact_id and period end date*/
2186 open csr_next_action_id;
2187 fetch csr_next_action_id into l_next_assignment_action_id;
2188 close csr_next_action_id;
2189
2190 if g_debug then
2191 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
2192 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
2193 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
2194 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
2195 end if;
2196
2197 -- Create the archive assignment actions
2198 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
2199
2200 insert into pay_action_information(
2201 action_information_id,
2202 action_context_id,
2203 action_context_type,
2204 effective_date,
2205 source_id,
2206 tax_unit_id,
2207 action_information_category,
2208 action_information1,
2209 action_information2,
2210 action_information3,
2211 assignment_id
2212 )
2213 values(
2214 pay_action_information_s.nextval,
2215 l_next_assignment_action_id,
2216 'AAP',
2217 null,
2218 null,
2219 csr_rec.tax_unit_id,
2220 'AU_ARCHIVE_ASG_DETAILS',
2221 csr_rec.assignment_action_id,
2222 p_payroll_action_id,
2223 csr_rec.action_sequence,
2224 csr_rec.assignment_id
2225 );
2226
2227
2228 END LOOP; /* Loop 2 */
2229 if g_debug then
2230 hr_utility.set_location('Leaving............Loop2 ,Org + Run....' || l_procedure,1000);
2231 end if;
2232 end if; /* End of Inner Organization */
2233 else /* Not Org,check for others */
2234
2235 if g_parameters.legal_employer is not null
2236 then
2237 if g_parameters.start_date is not null and g_parameters.end_date is not null
2238 then
2239 FOR csr_rec in csr_assignment_legal_period(p_payroll_action_id,
2240 p_start_person,
2241 p_end_person,
2242 g_parameters.employee_type,
2243 g_parameters.business_group_id,
2244 g_parameters.legal_employer,
2245 g_parameters.start_date,
2246 g_parameters.end_date)
2247 LOOP /*Loop 3 Leg Employer,Archive Start date,archive end date*/
2248 open csr_next_action_id;
2249 fetch csr_next_action_id into l_next_assignment_action_id;
2250 close csr_next_action_id;
2251 if g_debug then
2252 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
2253 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
2254 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
2255 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
2256 end if;
2257
2258 -- Create the archive assignment actions
2259 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
2260
2261 insert into pay_action_information(
2262 action_information_id,
2263 action_context_id,
2264 action_context_type,
2265 effective_date,
2266 source_id,
2267 tax_unit_id,
2268 action_information_category,
2269 action_information1,
2270 action_information2,
2271 action_information3,
2272 assignment_id
2273 )
2274 values(
2275 pay_action_information_s.nextval,
2276 l_next_assignment_action_id,
2277 'AAP',
2278 null,
2279 null,
2280 csr_rec.tax_unit_id,
2281 'AU_ARCHIVE_ASG_DETAILS',
2282 csr_rec.assignment_action_id,
2283 p_payroll_action_id,
2284 csr_rec.action_sequence,
2285 csr_rec.assignment_id
2286 );
2287
2288
2289 END LOOP;/* Loop 3 */
2290 if g_debug then
2291 hr_utility.set_location('Leaving............Loop3.Legal Emp + period...' || l_procedure,1000);
2292 end if;
2293
2294 else
2295 FOR csr_rec in csr_assignment_legal_run(p_payroll_action_id,
2296 p_start_person,
2297 p_end_person,
2298 g_parameters.employee_type,
2299 g_parameters.business_group_id,
2300 g_parameters.legal_employer,
2301 g_parameters.period_end_date,
2302 g_parameters.pact_id)
2303 LOOP /*Loop 4 Leg employer,pact_id + period end date */
2304 open csr_next_action_id;
2305 fetch csr_next_action_id into l_next_assignment_action_id;
2306 close csr_next_action_id;
2307
2308 if g_debug then
2309 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
2310 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
2311 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
2312 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
2313 end if;
2314
2315 -- Create the archive assignment actions
2316 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
2317
2318 insert into pay_action_information(
2319 action_information_id,
2320 action_context_id,
2321 action_context_type,
2322 effective_date,
2323 source_id,
2324 tax_unit_id,
2325 action_information_category,
2326 action_information1,
2327 action_information2,
2328 action_information3,
2329 assignment_id
2330 )
2331 values(
2332 pay_action_information_s.nextval,
2333 l_next_assignment_action_id,
2334 'AAP',
2335 null,
2336 null,
2337 csr_rec.tax_unit_id,
2338 'AU_ARCHIVE_ASG_DETAILS',
2339 csr_rec.assignment_action_id,
2340 p_payroll_action_id,
2341 csr_rec.action_sequence,
2342 csr_rec.assignment_id
2343 );
2344
2345
2346 END LOOP; /* Loop 4 */
2347 if g_debug then
2348 hr_utility.set_location('Leaving............Loop4.Legal Emp + Run...' || l_procedure,1000);
2349 end if;
2350 end if; /* End of Inner Legal Employer */
2351 else /* Not Org,Legal Emp Check others */
2352
2353 if g_parameters.payroll_id is not null
2354 then
2355 if g_parameters.start_date is not null and g_parameters.end_date is not null
2356 then
2357 FOR csr_rec in csr_assignment_payroll_period(p_payroll_action_id,
2358 p_start_person,
2359 p_end_person,
2360 g_parameters.employee_type,
2361 g_parameters.business_group_id,
2362 g_parameters.payroll_id,
2363 g_parameters.start_date,
2364 g_parameters.end_date)
2365 LOOP /*Loop 5 Payroll, Archive start date,end date */
2366 open csr_next_action_id;
2367 fetch csr_next_action_id into l_next_assignment_action_id;
2368 close csr_next_action_id;
2369
2370 if g_debug then
2371 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
2372 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
2373 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
2374 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
2375 end if;
2376
2377 -- Create the archive assignment actions
2378 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
2379
2380 insert into pay_action_information(
2381 action_information_id,
2382 action_context_id,
2383 action_context_type,
2384 effective_date,
2385 source_id,
2386 tax_unit_id,
2387 action_information_category,
2388 action_information1,
2389 action_information2,
2390 action_information3,
2391 assignment_id
2392 )
2393 values(
2394 pay_action_information_s.nextval,
2395 l_next_assignment_action_id,
2396 'AAP',
2397 null,
2398 null,
2399 csr_rec.tax_unit_id,
2400 'AU_ARCHIVE_ASG_DETAILS',
2401 csr_rec.assignment_action_id,
2402 p_payroll_action_id,
2403 csr_rec.action_sequence,
2404 csr_rec.assignment_id
2405 );
2406
2407
2408 END LOOP;/* Loop 5 */
2409
2410 if g_debug then
2411 hr_utility.set_location('Leaving............Loop5 Payroll + Period....' || l_procedure,1000);
2412 end if;
2413
2414 else
2415 FOR csr_rec in csr_assignment_payroll_run(p_payroll_action_id,
2416 p_start_person,
2417 p_end_person,
2418 g_parameters.employee_type,
2419 g_parameters.business_group_id,
2420 g_parameters.payroll_id,
2421 g_parameters.period_end_date,
2422 g_parameters.pact_id)
2423 LOOP /*Loop 6 Payroll, pact_id + period end date*/
2424 open csr_next_action_id;
2425 fetch csr_next_action_id into l_next_assignment_action_id;
2426 close csr_next_action_id;
2427
2428 if g_debug then
2429 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
2430 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
2431 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
2432 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
2433 end if;
2434
2435 -- Create the archive assignment actions
2436 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
2437
2438 insert into pay_action_information(
2439 action_information_id,
2440 action_context_id,
2441 action_context_type,
2442 effective_date,
2443 source_id,
2444 tax_unit_id,
2445 action_information_category,
2446 action_information1,
2447 action_information2,
2448 action_information3,
2449 assignment_id
2450 )
2451 values(
2452 pay_action_information_s.nextval,
2453 l_next_assignment_action_id,
2454 'AAP',
2455 null,
2456 null,
2457 csr_rec.tax_unit_id,
2458 'AU_ARCHIVE_ASG_DETAILS',
2459 csr_rec.assignment_action_id,
2460 p_payroll_action_id,
2461 csr_rec.action_sequence,
2462 csr_rec.assignment_id
2463 );
2464
2465
2466 END LOOP; /* Loop 6 */
2467 if g_debug then
2468 hr_utility.set_location('Leaving............Loop6 Payroll+ Run....' || l_procedure,1000);
2469 end if;
2470 end if; /* End of Inner Payroll */
2471 else /* Not Org,Legal,Payroll check others */
2472
2473 if g_parameters.assignment_id is not null
2474 then
2475 if g_parameters.start_date is not null and g_parameters.end_date is not null
2476 then
2477 FOR csr_rec in csr_assignment_period(p_payroll_action_id,
2478 p_start_person,
2479 p_end_person,
2480 g_parameters.employee_type,
2481 g_parameters.business_group_id,
2482 g_parameters.assignment_id,
2483 g_parameters.start_date,
2484 g_parameters.end_date)
2485 LOOP /*Loop 7 Assignment ,Archive start date,end date*/
2486 open csr_next_action_id;
2487 fetch csr_next_action_id into l_next_assignment_action_id;
2488 close csr_next_action_id;
2489
2490 if g_debug then
2491 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
2492 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
2493 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
2494 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
2495 end if;
2496
2497 -- Create the archive assignment actions
2498 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
2499
2500 insert into pay_action_information(
2501 action_information_id,
2502 action_context_id,
2503 action_context_type,
2504 effective_date,
2505 source_id,
2506 tax_unit_id,
2507 action_information_category,
2508 action_information1,
2509 action_information2,
2510 action_information3,
2511 assignment_id
2512 )
2513 values(
2514 pay_action_information_s.nextval,
2515 l_next_assignment_action_id,
2516 'AAP',
2517 null,
2518 null,
2519 csr_rec.tax_unit_id,
2520 'AU_ARCHIVE_ASG_DETAILS',
2521 csr_rec.assignment_action_id,
2522 p_payroll_action_id,
2523 csr_rec.action_sequence,
2524 csr_rec.assignment_id
2525 );
2526
2527
2528 END LOOP;/* Loop 7 */
2529 if g_debug then
2530 hr_utility.set_location('Leaving............Loop7. Asg + Period...' || l_procedure,1000);
2531 end if;
2532
2533 else
2534 FOR csr_rec in csr_assignment_run(p_payroll_action_id,
2535 p_start_person,
2536 p_end_person,
2537 g_parameters.employee_type,
2538 g_parameters.business_group_id,
2539 g_parameters.assignment_id,
2540 g_parameters.period_end_date,
2541 g_parameters.pact_id)
2542 LOOP /*Loop 8 Assignment Pact_id,Period end date */
2543 open csr_next_action_id;
2544 fetch csr_next_action_id into l_next_assignment_action_id;
2545 close csr_next_action_id;
2546
2547 if g_debug then
2548 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
2549 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
2550 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
2551 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
2552 end if;
2553
2554 -- Create the archive assignment actions
2555 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
2556
2557 insert into pay_action_information(
2558 action_information_id,
2559 action_context_id,
2560 action_context_type,
2561 effective_date,
2562 source_id,
2563 tax_unit_id,
2564 action_information_category,
2565 action_information1,
2566 action_information2,
2567 action_information3,
2568 assignment_id
2569 )
2570 values(
2571 pay_action_information_s.nextval,
2572 l_next_assignment_action_id,
2573 'AAP',
2574 null,
2575 null,
2576 csr_rec.tax_unit_id,
2577 'AU_ARCHIVE_ASG_DETAILS',
2578 csr_rec.assignment_action_id,
2579 p_payroll_action_id,
2580 csr_rec.action_sequence,
2581 csr_rec.assignment_id
2582 );
2583
2584
2585 END LOOP; /* Loop 8 */
2586 if g_debug then
2587 hr_utility.set_location('Leaving............Loop8.Asg + Run...' || l_procedure,1000);
2588 end if;
2589 end if; /* End of Inner Assignment */
2590
2591 else
2592
2593 /* Default Begins */
2594
2595 if g_parameters.start_date is not null and g_parameters.end_date is not null
2596 then
2597 FOR csr_rec in csr_assignment_default_period(p_payroll_action_id,
2598 p_start_person,
2599 p_end_person,
2600 g_parameters.employee_type,
2601 g_parameters.business_group_id,
2602 g_parameters.start_date,
2603 g_parameters.end_date)
2604 LOOP /*Loop 9*/
2605 open csr_next_action_id;
2606 fetch csr_next_action_id into l_next_assignment_action_id;
2607 close csr_next_action_id;
2608
2609 if g_debug then
2610 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
2611 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
2612 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
2613 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
2614 end if;
2615
2616 -- Create the archive assignment actions
2617 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
2618
2619 insert into pay_action_information(
2620 action_information_id,
2621 action_context_id,
2622 action_context_type,
2623 effective_date,
2624 source_id,
2625 tax_unit_id,
2626 action_information_category,
2627 action_information1,
2628 action_information2,
2629 action_information3,
2630 assignment_id
2631 )
2632 values(
2633 pay_action_information_s.nextval,
2634 l_next_assignment_action_id,
2635 'AAP',
2636 null,
2637 null,
2638 csr_rec.tax_unit_id,
2639 'AU_ARCHIVE_ASG_DETAILS',
2640 csr_rec.assignment_action_id,
2641 p_payroll_action_id,
2642 csr_rec.action_sequence,
2643 csr_rec.assignment_id
2644 );
2645
2646
2647 END LOOP;/* Loop 9 */
2648 if g_debug then
2649 hr_utility.set_location('Leaving............Loop9..Default + Period..' || l_procedure,1000);
2650 end if;
2651
2652 else
2653 FOR csr_rec in csr_assignment_default_run(p_payroll_action_id,
2654 p_start_person,
2655 p_end_person,
2656 g_parameters.employee_type,
2657 g_parameters.business_group_id,
2658 g_parameters.period_end_date,
2659 g_parameters.pact_id)
2660 LOOP /*Loop 10 */
2661 open csr_next_action_id;
2662 fetch csr_next_action_id into l_next_assignment_action_id;
2663 close csr_next_action_id;
2664
2665 if g_debug then
2666 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
2667 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
2668 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
2669 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
2670 end if;
2671
2672 -- Create the archive assignment actions
2673 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
2674
2675 insert into pay_action_information(
2676 action_information_id,
2677 action_context_id,
2678 action_context_type,
2679 effective_date,
2680 source_id,
2681 tax_unit_id,
2682 action_information_category,
2683 action_information1,
2684 action_information2,
2685 action_information3,
2686 assignment_id
2687 )
2688 values(
2689 pay_action_information_s.nextval,
2690 l_next_assignment_action_id,
2691 'AAP',
2692 null,
2693 null,
2694 csr_rec.tax_unit_id,
2695 'AU_ARCHIVE_ASG_DETAILS',
2696 csr_rec.assignment_action_id,
2697 p_payroll_action_id,
2698 csr_rec.action_sequence,
2699 csr_rec.assignment_id
2700 );
2701
2702
2703 END LOOP; /* Loop 10 */
2704 if g_debug then
2705 hr_utility.set_location('Leaving............Loop10 Default + Run....' || l_procedure,1000);
2706 end if;
2707 end if; /* End of Inner Default */
2708
2709
2710 end if ;/*End Assignment id */
2711 end if ; /* End Payroll */
2712 end if; /* End Legal */
2713 end if; /* End Organization */
2714
2715 END IF;
2716
2717 exception
2718 when others then
2719 hr_utility.set_location('Error in '||l_procedure,999999);
2720 raise;
2721 end assignment_action_code;
2722
2723 --------------------------------------------------------------------+
2724 -- Name : archive_code
2725 -- Type : Procedure
2726 -- Access: Public
2727 -- This procedure archives details for assignment.
2728 -- Employee details
2729 -- Checks pay_Action_information context ='AU_ARCHIVE_ASG_DETAILS'
2730 -- If employee details not previously archived,proc archives
2731 -- employee details in pay_Action_information with context
2732 -- 'AU_EMPLOYEE_RECON_DETAILS'.
2733 -- Element details.
2734 -- For each assignment run,proc archives element processed in
2735 -- pay_Action_information with context 'AU_ELEMENT_RECON_DETAILS'
2736 -- Balance Details.
2737 -- For each assignment run,proc archives balance details in
2738 -- pay_Action_information with context 'AU_BALANCE_RECON_DETAILS'
2739 -- Uses package pay_au_reconciliation_pkg to fetch balances.
2740
2741 --------------------------------------------------------------------+
2742
2743 procedure archive_code
2744 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
2745 ,p_effective_date in pay_payroll_actions.effective_date%type
2746 ) is
2747
2748
2749
2750 cursor c_employee_details(c_business_group_id hr_all_organization_units.organization_id%TYPE,
2751 c_assignment_id number,c_end_date date, c_start_date date) /*Bug#3662449 c_start_date parameter added*/
2752 is
2753 select pap.full_name,
2754 paa.assignment_number,
2755 paa.assignment_id,
2756 to_number(pro.proposed_salary_n) actual_salary,
2757 paa.normal_hours,
2758 pps.actual_termination_date,
2759 pgr.name grade, /* Bug 3953702*/
2760 paa.organization_id,
2761 hou.NAME organization_name, /*Bug 3953706 */
2762 -- paa.payroll_id, /*Bug 4688872*/
2763 -- papf.payroll_name, /*Bug 4132525, Bug 4688872*/
2764 hsc.segment1 tax_unit_id, /*Bug 4040688*/
2765 hou1.NAME Legal_Employer /*Bug 4132525*/
2766 from per_people_f pap,
2767 per_assignments_f paa,
2768 per_grades_tl pgr, /* Bug 3953702*/
2769 per_periods_of_service pps,
2770 per_pay_bases ppb,
2771 per_pay_proposals pro,
2772 per_assignment_status_types past,
2773 hr_soft_coding_keyflex hsc, /*Bug 4040688*/
2774 hr_all_organization_units hou, /*Bug 3953706 */
2775 hr_all_organization_units hou1 /*Bug 4132525*/
2776 -- pay_payrolls_f papf /*Bug 4132525, Bug 4688872*/
2777 where pap.person_id = paa.person_id
2778 and paa.assignment_id = c_assignment_id
2779 and paa.business_group_id = c_business_group_id
2780 and paa.grade_id = pgr.grade_id(+) /* Bug 3953702*/
2781 and pgr.language(+) = userenv('LANG')
2782 and paa.pay_basis_id = ppb.pay_basis_id(+)
2783 and paa.assignment_id = pro.assignment_id(+)
2784 AND hou.organization_id = paa.organization_id /*Bug 3953706 */
2785 AND hou1.organization_id = hsc.segment1 /*Bug 4132525*/
2786 -- AND papf.payroll_id = paa.payroll_id /*Bug 4132525, Bug 4688872*/
2787 -- AND c_end_date BETWEEN papf.effective_start_date AND papf.effective_end_date /*Bug 4132525, Bug 4688872*/
2788 and pps.period_of_service_id = paa.period_of_service_id
2789 AND paa.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id /*Bug 4040688*/
2790 and paa.assignment_status_type_id = past.assignment_status_type_id
2791 and paa.effective_end_date = ( select max(effective_end_date) /*Bug#3662449 sub query added*/
2792 from per_assignments_f
2793 WHERE assignment_id = c_assignment_id
2794 and effective_end_date >= c_start_date
2795 and effective_start_date <= c_end_date)
2796 and c_end_date between pap.effective_start_date and pap.effective_end_date
2797 and pps.person_id = pap.person_id
2798 and pro.change_date(+) <= c_end_date
2799 and nvl(pro.approved,'Y') = 'Y'
2800 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'))
2801 from per_pay_proposals pro1
2802 where pro1.assignment_id(+) = paa.assignment_id
2803 and pro1.change_date(+) <= c_end_date
2804 and nvl(pro1.approved,'Y')='Y');
2805
2806
2807 /*Bug# 4688872 - Introduced a new cursor to get the payroll name for the employee. This has been done to take care of cases
2808 where assignment has payroll attached to it for few months but is not attached at the end of year*/
2809 CURSOR c_get_payroll_name(c_assignment_id number,
2810 c_start_date date,
2811 c_end_date date)
2812 IS
2813 SELECT paaf.payroll_id, pay.payroll_name
2814 FROM per_all_assignments_f paaf,
2815 pay_payrolls_f pay
2816 WHERE paaf.assignment_id = c_assignment_id
2817 and paaf.effective_end_date = (select max(effective_end_date)
2818 From per_assignments_f iipaf
2819 WHERE iipaf.assignment_id = c_assignment_id
2820 and iipaf.effective_end_date >= c_start_date
2821 and iipaf.effective_start_date <= c_end_date
2822 AND iipaf.payroll_id IS NOT NULL)
2823 AND pay.payroll_id = paaf.payroll_id
2824 AND paaf.effective_end_date BETWEEN pay.effective_start_date AND pay.effective_end_date;
2825
2826 cursor c_payment_summary_details(c_assignment_id number,
2827 c_fin_date per_assignment_extra_info.aei_information1%TYPE)
2828 is
2829 select hr.meaning fin_year
2830 from per_assignment_extra_info pae,
2831 hr_lookups hr
2832 where pae.aei_information_category = 'HR_PS_ISSUE_DATE_AU'
2833 and pae.information_type = 'HR_PS_ISSUE_DATE_AU'
2834 and pae.assignment_id = c_assignment_id
2835 and pae.aei_information1 = c_fin_date
2836 and pae.aei_information1 = hr.lookup_code
2837 and hr.lookup_type = 'AU_PS_FINANCIAL_YEAR';
2838
2839 /* Bug 3627293 Modified cursor to return source_action_id */
2840 /* Bug 3953706 Modified cursor to group elements into 7 groups (Taxable Earnings,Non Taxable Earnings,Pre Tax Deductions,Employer Superannuation Contributions,
2841 Post Tax Deductions,Direct Payments,Tax Deductions)*/
2842 /*Bug 3935471 modified cursor to return the tax unit of master assignment action id*/
2843 /* Bug 5063359 - Modified decode for Employer Charges to return classification as Employer Charges rather than Employer Superannuation Contribution */
2844
2845 /* Bug 5461557 - Added tables piv2 and prrv2 to get input value for hours and rate and their relative joins,
2846 Used a group by based on rate */
2847
2848 /*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 */
2849 /*Bug 9147421 - Added check for element classification 'Foreign Tax Deductions'
2850 - Negate the amount for foreign tax deductions and classify as Tax Deductions
2851 */
2852
2853
2854
2855 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)
2856 is
2857 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 */
2858 from
2859 (select distinct -- Bug No: 4045910
2860 nvl(pet.reporting_name, pet.element_name) element_name,
2861 decode(instr(pec.classification_name, 'Earnings'), 0, null,
2862 decode(pec2.classification_name, 'Non Taxable', 'Non Taxable Earnings', 'Taxable Earnings')) ||
2863 decode(instr(pec.classification_name, 'Payments'), 0, null,
2864 decode(instr(pec.classification_name, 'Direct'), 0, 'Taxable Earnings', 'Direct Payments')) ||
2865 decode(instr(pec.classification_name, 'Deductions'), 0, null,
2866 decode(pec.classification_name , 'Termination Deductions' , 'Tax Deductions'
2867 , 'Involuntary Deductions' , 'Post Tax Deductions'
2868 , 'Voluntary Deductions' , 'Post Tax Deductions'
2869 , 'Foreign Tax Deductions' , 'Tax Deductions'
2870 , pec.classification_name )) ||
2871 decode(instr(pec.classification_name, 'Employer Charges'), 0,null,'Employer Charges' ) label,
2872 decode(pec.classification_name,'Foreign Tax Deductions',(-1 * NVL(prrv.result_value,0)), prrv.result_value) amount,
2873 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 */
2874 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,
2875 (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)),
2876 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 */
2877 prr.run_result_id,
2878 paa.source_action_id,
2879 paa2.tax_unit_id /*Bug 3935471*/
2880 from pay_element_types_f pet
2881 ,pay_input_values_f piv
2882 ,pay_element_classifications pec
2883 ,pay_assignment_actions paa
2884 ,pay_assignment_actions paa2 /*Bug 3935471*/
2885 ,pay_payroll_actions ppa
2886 ,per_assignments_f paaf
2887 ,pay_run_results prr
2888 ,pay_run_result_values prrv
2889 ,pay_element_classifications pec2
2890 ,pay_sub_classification_rules_f pscr
2891 where pet.element_type_id = piv.element_type_id
2892 and pet.element_type_id = prr.element_type_id
2893 and prr.assignment_action_id = paa.assignment_action_id
2894 and paaf.assignment_id = paa.assignment_id
2895 AND paa2.assignment_action_id = nvl(paa.source_action_id, paa.assignment_action_id) /*Bug 3935471*/
2896 and paaf.business_group_id = c_business_group_id/*Bug 5370001 */
2897 and prr.run_result_id = prrv.run_result_id
2898 and prrv.input_value_id = piv.input_value_id
2899 and pet.classification_id = pec.classification_id
2900 and pec.legislation_code = 'AU'
2901 and paa.assignment_action_id = c_assignment_action_id/*Bug 5370001 */
2902 and paa.payroll_action_id = ppa.payroll_action_id
2903 and paa.action_status = 'C' /*Bug 4099317*/
2904 and piv.name = 'Pay Value'
2905 and (instr(pec.classification_name, 'Earnings') > 0
2906 or instr(pec.classification_name, 'Payments') > 0
2907 or instr(pec.classification_name, 'Deductions') > 0
2908 or instr(pec.classification_name, 'Employer Charges' ) > 0 )
2909 and pet.element_type_id = pscr.element_type_id (+)
2910 and ppa.effective_date between nvl(pscr.effective_start_date, ppa.effective_date)
2911 and nvl(pscr.effective_end_date, ppa.effective_date)
2912 and pscr.classification_id = pec2.classification_id(+)
2913 and ppa.date_earned between pet.effective_start_date and pet.effective_end_date
2914 and ppa.date_earned between paaf.effective_start_date and paaf.effective_end_date
2915 and prr.status in ('P','PA')
2916 )
2917 group by element_name,label,source_action_id, tax_unit_id, rate ; /*Bug 3935471*/
2918
2919 cursor csr_get_data (c_arc_ass_act_id number)
2920 is
2921 select pai.action_information1, pai.tax_unit_id, pai.assignment_id,pai.action_information3
2922 from pay_action_information pai
2923 where action_information_category = 'AU_ARCHIVE_ASG_DETAILS'
2924 and pai.action_context_id = c_arc_ass_act_id;
2925
2926 /*Bug 4040688 - Two new cursors added to get the maximum assignment action id*/
2927 cursor csr_get_max_asg_dates (c_assignment_id number,
2928 c_start_date DATE,
2929 c_end_date DATE,
2930 c_tax_unit_id number)
2931 is
2932 select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
2933 ,max(paa.action_sequence)
2934 from pay_assignment_actions paa
2935 , pay_payroll_actions ppa
2936 , per_assignments_f paf
2937 where paa.assignment_id = paf.assignment_id
2938 and paf.assignment_id = c_assignment_id
2939 and ppa.payroll_action_id = paa.payroll_action_id
2940 and ppa.effective_date between c_start_date and c_end_date
2941 and ppa.payroll_id = paf.payroll_id
2942 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
2943 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
2944 and paa.action_status='C'
2945 AND paa.tax_unit_id = nvl(c_tax_unit_id, paa.tax_unit_id);
2946
2947
2948 cursor csr_get_max_asg_action (c_assignment_id number,
2949 c_payroll_action_id number,
2950 c_tax_unit_id number)
2951 is
2952 select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
2953 ,max(paa.action_sequence)
2954 from pay_assignment_actions paa
2955 , pay_payroll_actions ppa
2956 , per_assignments_f paf
2957 where paa.assignment_id = paf.assignment_id
2958 and paf.assignment_id = c_assignment_id
2959 and ppa.payroll_action_id = paa.payroll_action_id
2960 and ppa.payroll_action_id = c_payroll_action_id
2961 and ppa.payroll_id = paf.payroll_id
2962 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
2963 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
2964 and paa.action_status='C'
2965 AND paa.tax_unit_id = nvl(c_tax_unit_id, paa.tax_unit_id);
2966
2967 /*Bug 4040688 - end of modification*/
2968
2969 l_procedure varchar2(200);
2970 l_action_information_id number;
2971 l_object_version_number number;
2972
2973 --
2974 -- Table Declarations for the BBR
2975 --
2976 l_context_lst pay_balance_pkg.t_context_tab;
2977 l_output_tab pay_balance_pkg.t_detailed_bal_out_tab;
2978
2979 l_TAXABLE_EARNINGS number;
2980 l_GROSS_EARNINGS number;
2981 l_PRE_TAX_DEDUCTIONS number;
2982 l_DIRECT_PAYMENTS number;
2983 l_NON_TAXABLE_EARNINGS number;
2984 l_DEDUCTIONS number;
2985 l_TAX number;
2986 l_NET_PAYMENT number;
2987 l_EMPLOYER_CHARGES number;
2988
2989 l_YTD_TAXABLE_EARNINGS number;
2990 l_YTD_NON_TAXABLE_EARNINGS number;
2991 l_YTD_GROSS_EARNINGS number;
2992 l_YTD_PRE_TAX_DEDUCTIONS number;
2993 l_YTD_DIRECT_PAYMENTS number;
2994 l_YTD_DEDUCTIONS number;
2995 l_YTD_TAX number;
2996 l_YTD_NET_PAYMENT number;
2997 l_YTD_EMPLOYER_CHARGES number;
2998
2999 l_ass_act_id number;
3000 l_tax_unit_id number;
3001 l_assignment_id number;
3002
3003 l_fin_year varchar2(80);
3004 l_action_sequence number;
3005 l_fin_year_code per_assignment_extra_info.aei_information1%TYPE;
3006 l_balance_flag varchar2(1);
3007
3008 l_max_asg_action_id number; /*Bug 4040688*/
3009 l_max_action_sequence number; /*Bug 4040688*/
3010 l_payroll_id number; /*Bug 4688872*/
3011 l_payroll_name pay_payrolls_f.payroll_name%type; /*Bug 4688872*/
3012
3013 /* Bug 3531704 */
3014 --------------------------------------------------------------------+
3015 -- Name : get_fin_year_code
3016 -- Type : Function
3017 -- Access : Private
3018 -- This function returns the AU financial year code
3019 -- based on the Date parameter.
3020 --------------------------------------------------------------------+
3021
3022 FUNCTION get_fin_year_code(p_end_date in date)
3023 RETURN VARCHAR2
3024 IS
3025 l_check_date date;
3026 BEGIN
3027 /* Bug 3531704 Removed to_char */
3028 l_check_date := to_date('01/07/'||to_char(p_end_date,'YYYY'),'DD/MM/YYYY');
3029
3030 if (months_between(p_end_date,l_check_date) >= 0 )
3031 then
3032 return to_char(p_end_date,'YY');
3033 else
3034 return to_char(add_months(p_end_date,-6),'YY');
3035 end if;
3036 END get_fin_year_code;
3037
3038 begin
3039
3040 g_debug :=hr_utility.debug_enabled ;
3041 l_balance_flag := 'Y' ; /* Bug 3627293 - Balances have to be stored */
3042 l_YTD_GROSS_EARNINGS :=0 ;
3043 l_YTD_NON_TAXABLE_EARNINGS :=0 ;
3044 l_YTD_PRE_TAX_DEDUCTIONS :=0 ;
3045 l_YTD_TAXABLE_EARNINGS :=0 ;
3046 l_YTD_TAX :=0 ;
3047 l_YTD_DEDUCTIONS :=0 ;
3048 l_YTD_DIRECT_PAYMENTS :=0 ;
3049 l_YTD_NET_PAYMENT :=0 ;
3050 l_YTD_EMPLOYER_CHARGES :=0 ;
3051
3052 if g_debug then
3053 l_procedure := g_package||'archive_code';
3054 hr_utility.set_location('Entering '||l_procedure,1);
3055 hr_utility.set_location('p_assignment_action_id......= '|| p_assignment_action_id,10);
3056 hr_utility.set_location('p_effective_date............= '|| to_char(p_effective_date,'DD-MON-YYYY'),10);
3057 end if;
3058
3059 OPEN csr_get_data(p_assignment_action_id);
3060 FETCH csr_get_data into l_ass_act_id, l_tax_unit_id, l_assignment_id,l_action_sequence;
3061 CLOSE csr_get_data;
3062
3063 if g_debug then
3064 hr_utility.set_location('l_ass_act_id......= '|| l_ass_act_id,10);
3065 hr_utility.set_location('l_tax_unit_id............= '|| l_tax_unit_id,10);
3066 hr_utility.set_location('l_assignment_id......= '|| l_assignment_id,10);
3067 end if;
3068
3069 FOR csr_rec in c_employee_details(g_business_group_id, l_assignment_id,g_end_date,g_start_date) --Bug#3662449
3070 LOOP
3071
3072 if g_debug then
3073 hr_utility.set_location('csr_rec.full_name............= '|| csr_rec.full_name,10);
3074 end if;
3075
3076 IF (NVL(g_prev_assignment_id,0) <> csr_rec.assignment_id) THEN
3077
3078 g_prev_assignment_id := csr_rec.assignment_id;
3079
3080 -- Fetch Manual PS Details for assignment
3081
3082 l_fin_year_code := get_fin_year_code(g_end_date);
3083
3084 OPEN c_payment_summary_details(csr_rec.assignment_id,l_fin_year_code);
3085 FETCH c_payment_summary_details into l_fin_year;
3086 CLOSE c_payment_summary_details;
3087
3088 /*Bug 4040688 - Call to the procedure to get maximum assignment action id*/
3089 IF g_parameters.pact_id IS NULL THEN
3090 OPEN csr_get_max_asg_dates(csr_rec.assignment_id, g_start_date, g_end_date, g_parameters.legal_employer);
3091 FETCH csr_get_max_asg_dates INTO l_max_asg_action_id, l_max_action_sequence;
3092 CLOSE csr_get_max_asg_dates;
3093 ELSE
3094 OPEN csr_get_max_asg_action(csr_rec.assignment_id, g_parameters.pact_id, g_parameters.legal_employer);
3095 FETCH csr_get_max_asg_action INTO l_max_asg_action_id, l_max_action_sequence;
3096 CLOSE csr_get_max_asg_action;
3097 END IF ;
3098 -- Archive YTD balance details
3099 /*Bug 3953706 - Modfied the call to procedure introduce new parameters*/
3100 /*Bug 4040688 - YTD Balances will be called for the maximum assignment action id of the assignment*/
3101 IF l_max_asg_action_id IS NOT NULL THEN
3102 pay_au_reconciliation_pkg.GET_YTD_AU_REC_BALANCES(
3103 P_ASSIGNMENT_ACTION_ID => l_max_asg_action_id,
3104 P_REGISTERED_EMPLOYER => g_parameters.legal_employer, --2610141
3105 P_YTD_GROSS_EARNINGS => l_YTD_GROSS_EARNINGS,
3106 P_YTD_NON_TAXABLE_EARNINGS => l_YTD_NON_TAXABLE_EARNINGS,
3107 P_YTD_PRE_TAX_DEDUCTIONS => l_YTD_PRE_TAX_DEDUCTIONS,
3108 P_YTD_TAXABLE_EARNINGS => l_YTD_TAXABLE_EARNINGS,
3109 P_YTD_TAX => l_YTD_TAX ,
3110 P_YTD_DEDUCTIONS => l_YTD_DEDUCTIONS ,
3111 P_YTD_DIRECT_PAYMENTS => l_YTD_DIRECT_PAYMENTS,
3112 P_YTD_NET_PAYMENT => l_YTD_NET_PAYMENT ,
3113 P_YTD_EMPLOYER_CHARGES => l_YTD_EMPLOYER_CHARGES);
3114 END IF ;
3115
3116 insert into pay_action_information (
3117 action_information_id,
3118 action_context_id,
3119 action_context_type,
3120 effective_date,
3121 source_id,
3122 tax_unit_id,
3123 assignment_id,
3124 action_information_category,
3125 action_information1,
3126 action_information2,
3127 action_information3,
3128 action_information4,
3129 action_information5,
3130 action_information6,
3131 action_information7,
3132 action_information8,
3133 action_information9,
3134 action_information10)
3135 values (
3136 pay_action_information_s.nextval,
3137 p_assignment_action_id,
3138 'AAP',
3139 p_effective_date,
3140 null,
3141 null,
3142 l_assignment_id,
3143 'AU_BALANCE_RECON_DETAILS_YTD',
3144 l_YTD_TAXABLE_EARNINGS,
3145 l_YTD_NON_TAXABLE_EARNINGS,
3146 l_YTD_DEDUCTIONS,
3147 l_YTD_TAX,
3148 l_YTD_NET_PAYMENT,
3149 l_YTD_EMPLOYER_CHARGES,
3150 l_YTD_GROSS_EARNINGS,
3151 l_YTD_PRE_TAX_DEDUCTIONS,
3152 l_YTD_DIRECT_PAYMENTS,
3153 l_max_action_sequence);
3154
3155 /*Bug 4040688 - end of modification*/
3156
3157 if g_debug then
3158 hr_utility.set_location('g_prev_assignment_id......= '|| g_prev_assignment_id,10);
3159 hr_utility.set_location('g_arc_payroll_action_id......= '|| g_arc_payroll_action_id,20);
3160 hr_utility.set_location('l_max_asg_action_id......= '|| l_max_asg_action_id,20);
3161 end if;
3162
3163 -- Archive employee details
3164 /* Bug 3953702 - Archived the Grade Name details into pay_action_information */
3165
3166 /*Bug 4688872*/
3167 OPEN c_get_payroll_name(l_assignment_id,g_start_date,g_end_date);
3168 FETCH c_get_payroll_name INTO l_payroll_id, l_payroll_name;
3169 CLOSE c_get_payroll_name;
3170
3171
3172 insert into pay_action_information(
3173 action_information_id,
3174 action_context_id,
3175 action_context_type,
3176 effective_date,
3177 source_id,
3178 tax_unit_id,
3179 action_information_category,
3180 action_information1,
3181 action_information2,
3182 action_information3,
3183 action_information4,
3184 action_information5,
3185 action_information6,
3186 action_information7,
3187 action_information8,
3188 action_information9,
3189 action_information10,
3190 action_information11,
3191 assignment_id)
3192 values(
3193 pay_action_information_s.nextval,
3194 g_arc_payroll_action_id,
3195 'PA',
3196 p_effective_date,
3197 null,
3198 l_tax_unit_id,
3199 'AU_EMPLOYEE_RECON_DETAILS',
3200 csr_rec.full_name,
3201 csr_rec.assignment_number,
3202 csr_rec.actual_salary,
3203 csr_rec.grade, /* Bug 3953702*/
3204 csr_rec.normal_hours,
3205 csr_rec.actual_termination_date,
3206 l_fin_year,
3207 csr_rec.organization_name,/*Bug 4132525*/
3208 csr_rec.Legal_Employer, /*Bug 4040688, Bug 4132525*/
3209 l_payroll_name, /*Bug 4132525, Bug 4688872*/
3210 csr_rec.organization_name, /*Bug 3953706*/
3211 l_assignment_id);
3212
3213
3214 END IF;
3215
3216
3217 FOR csr_ele_det in c_element_details(g_business_group_id,l_ass_act_id)
3218 LOOP
3219
3220 -- Delete all the data that was populated due to previous action id
3221 --
3222 l_context_lst.delete;
3223 l_output_tab.delete;
3224
3225 /*Bug 3627293 - Support for Run Types */
3226 /*Bug 3935471 - Archive run balances only for master actions and for those child actions which
3227 have tax unit id different as compared to the master actions.*/
3228
3229 if csr_ele_det.master_action_id IS NOT NULL THEN
3230 /* Assignment Action is a child action,balances need not be stored */
3231 IF l_tax_unit_id = csr_ele_det.master_tax_unit_id OR g_parameters.legal_employer IS NULL THEN
3232 l_balance_flag :='N';
3233 END IF;
3234 end if;
3235
3236 --
3237 -- Insert the element data into pay_action_information table
3238 -- This Direct Insert statement is for Performance Reasons.
3239 --
3240
3241 insert into pay_action_information (
3242 action_information_id,
3243 action_context_id,
3244 action_context_type,
3245 effective_date,
3246 source_id,
3247 tax_unit_id,
3248 action_information_category,
3249 action_information1,
3250 action_information2,
3251 action_information3,
3252 action_information4,
3253 action_information5,
3254 action_information6,
3255 assignment_id)
3256 values (
3257 pay_action_information_s.nextval,
3258 p_assignment_action_id,
3259 'AAP',
3260 p_effective_date,
3261 null,
3262 l_tax_unit_id,
3263 'AU_ELEMENT_RECON_DETAILS',
3264 csr_ele_det.element_name,
3265 csr_ele_det.classification_name,
3266 null,
3267 csr_ele_det.hours,
3268 csr_ele_det.rate, /* 5599310 */
3269 csr_ele_det.payment,
3270 l_assignment_id);
3271
3272 ---
3273 END LOOP; /* Completed the Element Details Archive */
3274
3275 -- Balances Coding for BBR
3276 --
3277 -- Populate the Defined Balance IDs for the RUN and YTD dimensions
3278 -- for the required balances.
3279
3280 -- Get The Action Sequence for the Assignment_Action_Id.
3281
3282
3283
3284 /* Bug 3627293 - Archive Balances only for Master Assignment Action */
3285 if ( l_balance_flag = 'Y' )
3286 then
3287 /*Bug 3953706 - Modfied the call to procedure introduce new parameters*/
3288 pay_au_reconciliation_pkg.GET_AU_REC_BALANCES(
3289 P_ASSIGNMENT_ACTION_ID => l_ass_act_id,
3290 P_REGISTERED_EMPLOYER => g_parameters.legal_employer, --2610141
3291 P_GROSS_EARNINGS => l_GROSS_EARNINGS,
3292 P_NON_TAXABLE_EARNINGS => l_NON_TAXABLE_EARNINGS,
3293 P_PRE_TAX_DEDUCTIONS => l_PRE_TAX_DEDUCTIONS,
3294 P_TAXABLE_EARNINGS => l_TAXABLE_EARNINGS ,
3295 P_TAX => l_TAX ,
3296 P_DEDUCTIONS => l_DEDUCTIONS ,
3297 P_DIRECT_PAYMENTS => l_DIRECT_PAYMENTS,
3298 P_NET_PAYMENT => l_NET_PAYMENT ,
3299 P_EMPLOYER_CHARGES => l_EMPLOYER_CHARGES);
3300
3301
3302 --
3303 -- Insert the balance data into pay_action_information table
3304 -- This Direct Insert statement is for Performance Reasons.
3305 --
3306 /*Bug 4040688 - Modified insert statement to store run balance details.*/
3307 insert into pay_action_information (
3308 action_information_id,
3309 action_context_id,
3310 action_context_type,
3311 effective_date,
3312 source_id,
3313 tax_unit_id,
3314 assignment_id,
3315 action_information_category,
3316 action_information1,
3317 action_information2,
3318 action_information3,
3319 action_information4,
3320 action_information5,
3321 action_information6,
3322 action_information7,
3323 action_information8,
3324 action_information9,
3325 action_information10)
3326 values (
3327 pay_action_information_s.nextval,
3328 p_assignment_action_id,
3329 'AAP',
3330 p_effective_date,
3331 null,
3332 l_tax_unit_id,
3333 l_assignment_id,
3334 'AU_BALANCE_RECON_DETAILS_RUN',
3335 l_taxable_earnings,
3336 l_NON_TAXABLE_EARNINGS,
3337 l_DEDUCTIONS,
3338 l_TAX,
3339 l_NET_PAYMENT,
3340 l_EMPLOYER_CHARGES,
3341 l_GROSS_EARNINGS,
3342 l_PRE_TAX_DEDUCTIONS,
3343 l_DIRECT_PAYMENTS,
3344 l_action_sequence);
3345 end if; /*End Check for Balance Flag */
3346 END LOOP; /* End of assignments for employee */
3347
3348
3349 end archive_code;
3350
3351 --------------------------------------------------------------------+
3352 -- Name : spawn_archive_reports
3353 -- Type : Procedure
3354 -- Access: Public
3355 -- This procedure calls the Detail report
3356 -- Using the parameters passed, this proc calls the Reconciliation
3357 -- Detail report.
3358 -- This proc is called as deinitialization code of archive process.
3359
3360 --------------------------------------------------------------------+
3361
3362
3363
3364 procedure spawn_archive_reports
3365 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type)
3366 is
3367 l_count number;
3368 ps_request_id NUMBER;
3369 l_print_style VARCHAR2(2);
3370 l_print_together VARCHAR2(80);
3371 l_print_return BOOLEAN;
3372 l_procedure VARCHAR2(50);
3373 l_short_report_name VARCHAR2(30); /* 6839263 */
3374 l_xml_options BOOLEAN ; /* 6839263 */
3375
3376 --------------------------------------------------------------------+
3377 -- Cursor : csr_params
3378 -- Description : Fetches User Parameters from Legislative_paramters
3379 -- column.
3380 --------------------------------------------------------------------+
3381
3382 CURSOR csr_report_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
3383 IS
3384 SELECT pay_core_utils.get_parameter('PAY',legislative_parameters) payroll_id,
3385 pay_core_utils.get_parameter('ORG',legislative_parameters) org_id,
3386 pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
3387 to_date(pay_core_utils.get_parameter('SDATE',legislative_parameters),'YYYY/MM/DD') start_date,
3388 to_date(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD') end_date,
3389 pay_core_utils.get_parameter('PACTID',legislative_parameters) pact_id,
3390 pay_core_utils.get_parameter('LE',legislative_parameters) legal_employer,
3391 pay_core_utils.get_parameter('ASG',legislative_parameters) assignment_id,
3392 pay_core_utils.get_parameter('SO1',legislative_parameters) sort_order_1,
3393 pay_core_utils.get_parameter('SO2',legislative_parameters) sort_order_2,
3394 pay_core_utils.get_parameter('SO3',legislative_parameters) sort_order_3,
3395 pay_core_utils.get_parameter('SO4',legislative_parameters) sort_order_4,
3396 to_date(pay_core_utils.get_parameter('PEDATE',legislative_parameters),'YYYY/MM/DD') period_end_date,
3397 pay_core_utils.get_parameter('YTD_TOT',legislative_parameters) ytd_totals,
3398 pay_core_utils.get_parameter('ZERO_REC',legislative_parameters) zero_records,
3399 pay_core_utils.get_parameter('NEG_REC',legislative_parameters) negative_records,
3400 pay_core_utils.get_parameter('EMP_TYPE',legislative_parameters) employee_type,
3401 pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions, /*Bug# 4142159*/
3402 pay_core_utils.get_parameter('OUTPUT_TYPE',legislative_parameters)p_output_type /* Bug# 6839263 */
3403 FROM pay_payroll_actions ppa
3404 WHERE ppa.payroll_action_id = c_payroll_action_id;
3405
3406
3407 cursor csr_get_print_options(p_payroll_action_id NUMBER) IS
3408 SELECT printer,
3409 print_style,
3410 decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output
3411 ,number_of_copies /* Bug 4116833 */
3412 FROM pay_payroll_actions pact,
3413 fnd_concurrent_requests fcr
3414 WHERE fcr.request_id = pact.request_id
3415 AND pact.payroll_action_id = p_payroll_action_id;
3416
3417 rec_print_options csr_get_print_options%ROWTYPE;
3418
3419 l_parameters csr_report_params%ROWTYPE;
3420
3421 Begin
3422 l_count :=0;
3423 ps_request_id :=-1;
3424 g_debug :=hr_utility.debug_enabled ;
3425
3426 if g_debug then
3427 l_procedure := g_package||' spawn_archive_reports';
3428 hr_utility.set_location('Entering '||l_procedure,999);
3429 end if;
3430
3431 -- Set User Parameters for Report.
3432
3433 open csr_report_params(p_payroll_action_id);
3434 fetch csr_report_params into l_parameters;
3435 close csr_report_params;
3436
3437 /* Start Bug 6839263 */
3438 IF l_parameters.p_output_type = 'XML_PDF'
3439 THEN
3440 l_short_report_name := 'PYAURECD_XML';
3441
3442 l_xml_options := fnd_request.add_layout
3443 (template_appl_name => 'PAY',
3444 template_code => 'PYAURECD_XML',
3445 template_language => 'en',
3446 template_territory => 'US',
3447 output_format => 'PDF');
3448
3449 ELSE
3450 l_short_report_name := 'PYAURECD';
3451 END IF;
3452 /* End Bug 6839263 */
3453
3454
3455 /*Bug 3953615 -- Added the call to check parameters validations*/
3456 pay_au_reconciliation_pkg.check_report_parameters(l_parameters.start_date
3457 ,l_parameters.end_date
3458 ,l_parameters.period_end_date);
3459
3460
3461
3462 if g_debug then
3463 hr_utility.set_location('payroll_parameters.action '||p_payroll_action_id,0);
3464 hr_utility.set_location('in BG_ID '||l_parameters.business_group_id,1);
3465 hr_utility.set_location('in org_id '||l_parameters.org_id,2);
3466 hr_utility.set_location('in payroll_parameters.id '||l_parameters.payroll_id,3);
3467 hr_utility.set_location('in asg_id '||l_parameters.assignment_id,4);
3468 hr_utility.set_location('in archive start date '||to_char(l_parameters.start_date,'YYYY/MM/DD'),5);
3469 hr_utility.set_location('in archive end date '||to_char(l_parameters.end_date,'YYYY/MM/DD'),6);
3470 hr_utility.set_location('in pact_id '||l_parameters.pact_id,7);
3471 hr_utility.set_location('in legal employer '||l_parameters.legal_employer,8);
3472 hr_utility.set_location('in PERIOD END DATE '||to_char(l_parameters.period_end_date,'YYYY/MM/DD'),9);
3473 hr_utility.set_location('in YTD totals '||l_parameters.ytd_totals,10);
3474 hr_utility.set_location('in zero records'||l_parameters.zero_records,11);
3475 hr_utility.set_location('in Negative records'||l_parameters.negative_records,12);
3476 hr_utility.set_location('in emp_type '||l_parameters.employee_type,14);
3477 hr_utility.set_location('in sort order 1'||l_parameters.sort_order_1,15);
3478 hr_utility.set_location('in sort order 2'||l_parameters.sort_order_2,16);
3479 hr_utility.set_location('in sort order 3'||l_parameters.sort_order_3,17);
3480 hr_utility.set_location('in sort order 4'||l_parameters.sort_order_4,18);
3481 hr_utility.set_location('in delete action'||l_parameters.delete_actions,19); /*Bug# 4142159*/
3482 hr_utility.set_location('in Output Type '||l_parameters.p_output_type,20); /*Bug# 6939263 */
3483 end if;
3484
3485 if g_debug then
3486 hr_utility.set_location('Afer payroll action ' || p_payroll_action_id , 125);
3487
3488 hr_utility.set_location('Before calling report',24);
3489 end if;
3490
3491 OPEN csr_get_print_options(p_payroll_action_id);
3492 FETCH csr_get_print_options INTO rec_print_options;
3493 CLOSE csr_get_print_options;
3494 --
3495 l_print_together := nvl(fnd_profile.value('CONC_PRINT_TOGETHER'), 'N');
3496 --
3497 -- Set printer options
3498 l_print_return := fnd_request.set_print_options
3499 (printer => rec_print_options.printer,
3500 style => rec_print_options.print_style,
3501 copies => rec_print_options.number_of_copies, /* Bug 4116833*/
3502 save_output => hr_general.char_to_bool(rec_print_options.save_output),
3503 print_together => l_print_together);
3504 -- Submit report
3505 if g_debug then
3506 hr_utility.set_location('payroll_action id '|| p_payroll_action_id,25);
3507 end if;
3508
3509 ps_request_id := fnd_request.submit_request
3510 ('PAY',
3511 l_short_report_name,
3512 null,
3513 null,
3514 false,
3515 'P_PAYROLL_ACTION_ID='||to_char(p_payroll_action_id),
3516 'P_BUSINESS_GROUP_ID='||to_char(l_parameters.business_group_id),
3517 'P_ORGANIZATION_ID='||to_char(l_parameters.org_id),
3518 'P_PAYROLL_ID='||to_char(l_parameters.payroll_id),
3519 'P_REGISTERED_EMPLOYER='||to_char(l_parameters.legal_employer),
3520 'P_ASSIGNMENT_ID='||to_char(l_parameters.assignment_id),
3521 'P_START_DATE='||to_char(l_parameters.start_date,'YYYY/MM/DD'),
3522 'P_END_DATE='||to_char(l_parameters.end_date,'YYYY/MM/DD'),
3523 'P_PAYROLL_RUN_ID='||to_char(l_parameters.pact_id),
3524 'P_PERIOD_END_DATE='||to_char(l_parameters.period_end_date,'YYYY/MM/DD'),
3525 'P_EMPLOYEE_TYPE='||l_parameters.employee_type,
3526 'P_YTD_TOTALS='||l_parameters.ytd_totals,
3527 'P_ZERO_RECORDS='||l_parameters.zero_records,
3528 'P_NEGATIVE_RECORDS='||l_parameters.negative_records,
3529 'P_SORT_ORDER_1='||l_parameters.sort_order_1,
3530 'P_SORT_ORDER_2='||l_parameters.sort_order_2,
3531 'P_SORT_ORDER_3='||l_parameters.sort_order_3,
3532 'P_SORT_ORDER_4='||l_parameters.sort_order_4,
3533 'P_DELETE_ACTIONS='||l_parameters.delete_actions, /*Bug# 4142159*/
3534 'BLANKPAGES=NO',
3535 NULL, NULL, NULL, NULL, NULL, NULL,
3536 NULL, NULL, NULL, NULL, NULL, NULL,
3537 NULL, NULL, NULL, NULL, NULL, NULL,
3538 NULL, NULL, NULL, NULL, NULL, NULL,
3539 NULL, NULL, NULL, NULL, NULL, NULL,
3540 NULL, NULL, NULL, NULL, NULL, NULL,
3541 NULL, NULL, NULL, NULL, NULL, NULL,
3542 NULL, NULL, NULL, NULL, NULL, NULL,
3543 NULL, NULL, NULL, NULL, NULL, NULL,
3544 NULL, NULL, NULL, NULL, NULL, NULL,
3545 NULL, NULL, NULL, NULL, NULL, NULL,
3546 NULL, NULL, NULL, NULL, NULL, NULL,
3547 NULL, NULL, NULL, NULL
3548 );
3549 if g_debug then
3550 hr_utility.set_location('After calling report',24);
3551 end if;
3552
3553
3554
3555
3556
3557
3558 end spawn_archive_reports;
3559
3560 -------------------------------------------------------------------------------------------
3561 /* Bug 5603254 - Function is used to compute Hours for Elements.
3562 Function : get_element_payment_hours
3563 Description : This function is to be used for getting the Hours component paid in run.
3564
3565 Inputs : p_assignment_action_id - Assignment Action ID
3566 p_element_type_id - Element Type ID
3567 p_run_result_id - Run Result ID
3568 p_effective_date - Effective Date of Run
3569 */
3570 -------------------------------------------------------------------------------------------
3571 FUNCTION get_element_payment_hours
3572 (
3573 p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE,
3574 p_element_type_id IN pay_element_entries_f.element_entry_id%TYPE,
3575 p_run_result_id IN pay_run_results.run_result_ID%TYPE,
3576 p_effective_date IN pay_payroll_actions.effective_date%TYPE
3577 )
3578 RETURN NUMBER
3579 IS
3580
3581 l_element_type_id pay_element_types_f.element_type_id%TYPE;
3582 l_input_value_id pay_input_values_f.input_value_id%TYPE;
3583
3584 l_result number := NULL;
3585 l_temp NUMBER := NULL;
3586
3587 /* Bug 5987877 - Added Check for Input with Name - Hours */
3588 CURSOR get_hours_input_value
3589 (c_element_type_id pay_element_types_f.element_type_id%TYPE
3590 ,c_effective_date date)
3591 IS
3592 SELECT pivf.input_value_id
3593 ,pivf.name
3594 ,decode(pivf.name,'Hours',1,2) sort_index
3595 FROM pay_input_values_f pivf
3596 WHERE pivf.element_type_id = c_element_type_id
3597 AND substr(pivf.uom,1,1) = 'H'
3598 AND c_effective_date between pivf.effective_start_date and pivf.effective_end_date
3599 ORDER BY sort_index;
3600
3601 CURSOR get_hours_result_value
3602 (c_run_result_id pay_run_result_values.run_result_id%TYPE
3603 ,c_input_value_id pay_run_result_values.input_value_id%TYPE)
3604 IS
3605 SELECT prrv.result_value
3606 FROM pay_run_result_values prrv
3607 WHERE prrv.run_result_id = c_run_result_id
3608 AND prrv.input_value_id = c_input_value_id;
3609
3610 BEGIN
3611
3612 g_debug := hr_utility.debug_enabled;
3613
3614 /* Bug 5987877 - Added Check for Multiple Hours Input
3615 If Input Name = "Hours", return run result for it
3616 else sum the run results for all "H_" UOM type.
3617 */
3618 FOR csr_rec IN get_hours_input_value(p_element_type_id,p_effective_date)
3619 LOOP
3620 OPEN get_hours_result_value(p_run_result_id,csr_rec.input_value_id);
3621 FETCH get_hours_result_value INTO l_temp;
3622 CLOSE get_hours_result_value;
3623 IF csr_rec.sort_index = 1
3624 THEN
3625 l_result := l_temp;
3626 EXIT;
3627 ELSE
3628 l_result := NVL(l_result,0) + NVL(l_temp,0);
3629 END IF;
3630 END LOOP;
3631
3632
3633 /* Avoid Divide by Zero Errors when used for computing Rate, Report Hours and Rate as Null */
3634 IF l_result = 0
3635 THEN
3636 l_result := NULL;
3637 END IF;
3638
3639 RETURN l_result;
3640 END get_element_payment_hours;
3641
3642 -------------------------------------------------------------------------------------------
3643 /* Bug 5599310 - Function is used to compute Rate for Elements.
3644 Function : get_element_payment_rate
3645 Description : This function is to be used for getting the Rate component if cusomters have entered it
3646
3647 Inputs : p_assignment_action_id - Assignment Action ID
3648 p_element_type_id - Element Type ID
3649 p_run_result_id - Run Result ID
3650 p_effective_date - Effective Date of Run
3651 */
3652 -------------------------------------------------------------------------------------------
3653 FUNCTION get_element_payment_rate
3654 (
3655 p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE,
3656 p_element_type_id IN pay_element_entries_f.element_entry_id%TYPE,
3657 p_run_result_id IN pay_run_results.run_result_ID%TYPE,
3658 p_effective_date IN pay_payroll_actions.effective_date%TYPE
3659 )
3660 RETURN NUMBER
3661 IS
3662
3663 l_element_type_id pay_element_types_f.element_type_id%TYPE;
3664 l_input_value_id pay_input_values_f.input_value_id%TYPE;
3665
3666 l_result number := NULL;
3667
3668 CURSOR get_rate_input_value
3669 (c_element_type_id pay_element_types_f.element_type_id%TYPE
3670 ,c_effective_date date)
3671 IS
3672 SELECT pivf.input_value_id
3673 FROM pay_input_values_f pivf
3674 WHERE pivf.element_type_id = c_element_type_id
3675 AND upper(pivf.name) like 'RATE%'
3676 AND pivf.uom in ('N','M','I') /*bug 6109668 */
3677 AND c_effective_date between pivf.effective_start_date and pivf.effective_end_date;
3678
3679 CURSOR get_rate_result_value
3680 (c_run_result_id pay_run_result_values.run_result_id%TYPE
3681 ,c_input_value_id pay_run_result_values.input_value_id%TYPE)
3682 IS
3683 SELECT prrv.result_value
3684 FROM pay_run_result_values prrv
3685 WHERE prrv.run_result_id = c_run_result_id
3686 AND prrv.input_value_id = c_input_value_id;
3687
3688 BEGIN
3689
3690 g_debug := hr_utility.debug_enabled;
3691
3692 if g_debug then
3693 hr_utility.trace('Entering get_element_payment_rate');
3694 end if;
3695
3696 OPEN get_rate_input_value(p_element_type_id,p_effective_date);
3697 FETCH get_rate_input_value INTO l_input_value_id;
3698 CLOSE get_rate_input_value;
3699
3700 IF l_input_value_id IS NOT NULL
3701 THEN
3702 OPEN get_rate_result_value(p_run_result_id,l_input_value_id);
3703 FETCH get_rate_result_value INTO l_result;
3704 CLOSE get_rate_result_value;
3705 END IF;
3706
3707 /* Avoid Divide by Zero Errors when used for computing Rate, Report Hours and Rate as Null */
3708
3709 if g_debug then
3710 hr_utility.trace('l_result is ' || l_result);
3711 end if;
3712
3713 IF l_result = 0
3714 THEN
3715 l_result := NULL;
3716 END IF;
3717
3718
3719 if g_debug then
3720 hr_utility.trace('Leaving get_element_payment_rate');
3721 end if;
3722
3723 RETURN l_result;
3724 END get_element_payment_rate;
3725
3726
3727 end pay_au_rec_det_archive;