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