[Home] [Help]
PACKAGE BODY: APPS.PAY_AU_RECONCILIATION_PKG
Source
1 package body pay_au_reconciliation_pkg as
2 /* $Header: pyaurecs.pkb 120.7 2008/03/20 07:57:28 avenkatk noship $ */
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 /* Procedure to pass all the balance results back in one call from report */
19
20 procedure get_au_rec_balances
21 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
22 p_registered_employer in NUMBER, --2610141
23 p_gross_earnings out NOCOPY number, /*Bug 3953706*/
24 p_non_taxable_earnings out NOCOPY number,
25 p_pre_tax_deductions out NOCOPY number, /*Bug 3953706*/
26 p_taxable_earnings out NOCOPY number,
27 p_tax out NOCOPY number,
28 p_deductions out NOCOPY number,
29 p_direct_payments out NOCOPY number, /*Bug 3953706*/
30 p_net_payment out NOCOPY number,
31 p_employer_charges out NOCOPY number)
32 is
33
34 begin
35
36 IF g_debug THEN
37 hr_utility.trace('Entering:' || g_package || 'get_au_rec_balances');
38 hr_utility.trace('Assignment action id value ===>' || p_assignment_action_id);
39 END IF;
40
41 /* Call to this function below implements Batch Balance Retrieval for better performance */
42
43 /*Changes made for bug 2610141 Start here*/
44 g_context_table(1).tax_unit_id := p_registered_employer;
45
46 pay_balance_pkg.get_value(p_assignment_action_id => p_assignment_action_id,
47 p_defined_balance_lst=>g_balance_value_tab,
48 p_context_lst =>g_context_table,
49 p_output_table=>g_result_table);
50
51 /*Changes made for bug 2610141 Ends here*/
52
53 /* pay_balance_pkg.get_value ( p_assignment_action_id => p_assignment_action_id
54 , p_defined_balance_lst => g_balance_value_tab );*/
55
56 IF g_debug THEN
57 hr_utility.trace('Balance values for RUN dimension');
58 hr_utility.trace('-------------------------------------');
59 hr_utility.trace('Earnings_Total ===>' || g_result_table(1).balance_value);
60 hr_utility.trace('Direct Payments ===>' || g_result_table(2).balance_value);
61 hr_utility.trace('Termination_Payments ===>' || g_result_table(3).balance_value);
62 hr_utility.trace('Involuntary Deductions===>' || g_result_table(4).balance_value);
63 hr_utility.trace('Pre Tax Deductions ===>' || g_result_table(5).balance_value);
64 hr_utility.trace('Termination Deductions===>' || g_result_table(6).balance_value);
65 hr_utility.trace('Voluntary Deductions ===>' || g_result_table(7).balance_value);
66 hr_utility.trace('Total_Tax_Deduction ===>' || g_result_table(8).balance_value);
67 hr_utility.trace('Earnings_Non_Taxable ===>' || g_result_table(9).balance_value);
68 hr_utility.trace('Employer_Charges ===>' || g_result_table(10).balance_value);
69 END IF;
70
71 /* Bug 3953706 - Modified calculation of Earnings and deductions as given below:
72 Calculations :-
73 ===============
74 Gross Earnings = Earnings Total + Termination Payments + Pre Tax Deductions
75 Taxable_earnings = Gross Earnings - Pre_tax_deductions - Earnings_non_taxable
76 Non_taxable_earnings = Earnings_non_taxable
77 Post Tax Deductions = Involuntary_deductions + Voluntary_deductions
78 Tax Deductions = Tax_deductions + Termination_deductions
79 Net_payment = Taxable_earnings + Non_taxable_earnings - Tax - Deductions + Direct_Payments
80 Direct Payments = Direct_Payments
81 Employer_charges = Employer_charges
82 Pre Tax Deductions = Pre_tax_deductions */
83
84
85
86 p_gross_earnings := g_result_table(1).balance_value + g_result_table(3).balance_value + g_result_table(5).balance_value;
87 p_non_taxable_earnings := g_result_table(9).balance_value;
88 p_pre_tax_deductions := g_result_table(5).balance_value;
89 p_taxable_earnings := p_gross_earnings - p_pre_tax_deductions - p_non_taxable_earnings;
90 p_tax := g_result_table(8).balance_value + g_result_table(6).balance_value;
91 p_deductions := g_result_table(4).balance_value + g_result_table(7).balance_value;
92 p_direct_payments := g_result_table(2).balance_value;
93 p_net_payment := p_taxable_earnings + p_non_taxable_earnings - p_tax - p_deductions + p_direct_payments;
94 p_employer_charges := g_result_table(10).balance_value;
95
96 IF g_debug THEN
97 hr_utility.trace('p_taxable_earnings ===>' || p_taxable_earnings);
98 hr_utility.trace('p_non_taxable_earnings ===>' || p_non_taxable_earnings);
99 hr_utility.trace('p_deductions ===>' || p_deductions);
100 hr_utility.trace('p_tax ===>' || p_tax);
101 hr_utility.trace('p_net_payment ===>' || p_net_payment);
102 hr_utility.trace('p_employer_charges ===>' || p_employer_charges);
103 hr_utility.trace('p_gross_earnings ===>' || p_gross_earnings);
104 hr_utility.trace('p_pre_tax_deduction ===>' || p_pre_tax_deductions);
105 hr_utility.trace('p_direct_payments ===>' || p_direct_payments);
106 END IF;
107
108
109 end get_au_rec_balances;
110
111
112 /* Procedure to pass all the YTD balance results back in one call from report */
113
114 procedure get_ytd_au_rec_balances
115 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
116 p_registered_employer in NUMBER, --2610141
117 p_ytd_gross_earnings out NOCOPY number, /*Bug 3953706*/
118 p_ytd_non_taxable_earnings out NOCOPY number,
119 p_ytd_pre_tax_deductions out NOCOPY number, /*Bug 3953706*/
120 p_ytd_taxable_earnings out NOCOPY number,
121 p_ytd_tax out NOCOPY number,
122 p_ytd_deductions out NOCOPY number,
123 p_ytd_direct_payments out NOCOPY number, /*Bug 3953706*/
124 p_ytd_net_payment out NOCOPY number,
125 p_ytd_employer_charges out NOCOPY number)
126 is
127
128 begin
129
130 IF g_debug THEN
131 hr_utility.trace('Entering:' || g_package || 'get_ytd_au_rec_balances');
132 hr_utility.trace('Assignment action id value ===>' || p_assignment_action_id);
133 END IF;
134
135 /* Call to this function below implements Batch Balance Retrieval for better performance */
136
137 /*Changes made for bug 2610141 Start here*/
138 g_context_table(1).tax_unit_id := p_registered_employer;
139
140 pay_balance_pkg.get_value(p_assignment_action_id => p_assignment_action_id,
141 p_defined_balance_lst=>g_ytd_balance_value_tab, /*Bug 4040688*/
142 p_context_lst =>g_context_table,
143 p_output_table=>g_result_table);
144
145 /*Changes made for bug 2610141 Ends here*/
146
147 /* pay_balance_pkg.get_value ( p_assignment_action_id => p_assignment_action_id
148 , p_defined_balance_lst => g_balance_value_tab );*/
149
150 IF g_debug THEN
151 hr_utility.trace('Balance values for YTD dimension');
152 hr_utility.trace('-------------------------------------');
153 hr_utility.trace('Earnings_Total ===>' || g_result_table(1).balance_value);
154 hr_utility.trace('Direct Payments ===>' || g_result_table(2).balance_value);
155 hr_utility.trace('Termination_Payments ===>' || g_result_table(3).balance_value);
156 hr_utility.trace('Involuntary Deductions===>' || g_result_table(4).balance_value);
157 hr_utility.trace('Pre Tax Deductions ===>' || g_result_table(5).balance_value);
158 hr_utility.trace('Termination Deductions===>' || g_result_table(6).balance_value);
159 hr_utility.trace('Voluntary Deductions ===>' || g_result_table(7).balance_value);
160 hr_utility.trace('Total_Tax_Deduction ===>' || g_result_table(8).balance_value);
161 hr_utility.trace('Earnings_Non_Taxable ===>' || g_result_table(9).balance_value);
162 hr_utility.trace('Employer_Charges ===>' || g_result_table(10).balance_value);
163 END IF;
164
165 /* Bug 3953706 - Modified calculation of Earnings and deductions as given below:
166 Calculations :-
167 ===============
168 Gross Earnings = Earnings Total + Termination Payments + Pre Tax Deductions
169 Taxable_earnings = Gross Earnings - Pre_tax_deductions - Earnings_non_taxable
170 Non_taxable_earnings = Earnings_non_taxable
171 Post Tax Deductions = Involuntary_deductions + Voluntary_deductions
172 Tax Deductions = Tax_deductions + Termination_deductions
173 Net_payment = Taxable_earnings + Non_taxable_earnings - Tax - Deductions + Direct_Payments
174 Direct Payments = Direct_Payments
175 Employer_charges = Employer_charges
176 Pre Tax Deductions = Pre_tax_deductions */
177
178 p_ytd_gross_earnings := g_result_table(1).balance_value + g_result_table(3).balance_value + g_result_table(5).balance_value;
179 p_ytd_non_taxable_earnings := g_result_table(9).balance_value;
180 p_ytd_pre_tax_deductions := g_result_table(5).balance_value;
181 p_ytd_taxable_earnings := p_ytd_gross_earnings - p_ytd_pre_tax_deductions - p_ytd_non_taxable_earnings;
182 p_ytd_tax := g_result_table(8).balance_value + g_result_table(6).balance_value;
183 p_ytd_deductions := g_result_table(4).balance_value + g_result_table(7).balance_value;
184 p_ytd_direct_payments := g_result_table(2).balance_value;
185 p_ytd_net_payment := p_ytd_taxable_earnings + p_ytd_non_taxable_earnings - p_ytd_tax - p_ytd_deductions + p_ytd_direct_payments;
186 p_ytd_employer_charges := g_result_table(10).balance_value;
187
188
189 IF g_debug THEN
190 hr_utility.trace('p_ytd_taxable_earnings ===>' || p_ytd_taxable_earnings);
191 hr_utility.trace('p_ytd_non_taxable_earnings ===>' || p_ytd_non_taxable_earnings);
192 hr_utility.trace('p_ytd_deductions ===>' || p_ytd_deductions);
193 hr_utility.trace('p_ytd_tax ===>' || p_ytd_tax);
194 hr_utility.trace('p_ytd_net_payment ===>' || p_ytd_net_payment);
195 hr_utility.trace('p_ytd_employer_charges ===>' || p_ytd_employer_charges);
196 hr_utility.trace('p_ytd_gross_earnings ===>' || p_ytd_gross_earnings);
197 hr_utility.trace('p_ytd_pre_tax_deduction ===>' || p_ytd_pre_tax_deductions);
198 hr_utility.trace('p_ytd_direct_payments ===>' || p_ytd_direct_payments);
199 END IF;
200
201 end get_ytd_au_rec_balances;
202
203
204 PROCEDURE populate_defined_balance_ids
205 (p_ytd_totals varchar2,
206 p_registered_employer NUMBER) IS --2610141
207
208 CURSOR csr_defined_balance_id
209 (c_database_item_suffix pay_balance_dimensions.database_item_suffix%type)
210 IS
211 SELECT decode(pbt.balance_name,'Earnings_Total',1,'Direct Payments',2,'Termination_Payments',3,
212 'Involuntary Deductions',4,'Pre Tax Deductions',5,'Termination Deductions',6,
213 'Voluntary Deductions',7,'Total_Tax_Deductions',8,'Earnings_Non_Taxable',9,
214 'Employer_Charges',10) sort_index,
215 pdb.defined_balance_id defined_balance_id
216 FROM pay_balance_types pbt,
217 pay_balance_dimensions pbd,
218 pay_defined_balances pdb
219 WHERE pbt.balance_name IN ('Earnings_Total','Direct Payments','Termination_Payments','Involuntary Deductions',
220 'Pre Tax Deductions','Termination Deductions','Voluntary Deductions','Total_Tax_Deductions',
221 'Earnings_Non_Taxable','Employer_Charges')
222 AND pbd.database_item_suffix = c_database_item_suffix
223 AND pbt.balance_type_id = pdb.balance_type_id
224 AND pbd.balance_dimension_id = pdb.balance_dimension_id
225 AND pbt.legislation_code = 'AU'
226 ORDER BY sort_index;
227
228 i NUMBER := 0;
229 l_run_dimension_name VARCHAR2(15);--2610141
230 l_ytd_dimension_name VARCHAR2(15);--2610141
231
232 BEGIN
233
234 IF g_debug THEN
235 hr_utility.trace('Entering:' || g_package || 'populate_defined_balance_ids');
236 hr_utility.trace('Parameter p_ytd_totals value ===>' || p_ytd_totals);
237 END IF;
238
239 g_balance_value_tab.delete;
240
241 /*Bug 2610141 - Code added to pick up the right dimension on the basis of input parameters for the report*/
242 IF p_registered_employer IS NULL THEN
243 l_run_dimension_name := '_ASG_RUN';
244 l_ytd_dimension_name := '_ASG_YTD';
245 ELSE
246 l_run_dimension_name := '_ASG_LE_RUN';
247 l_ytd_dimension_name := '_ASG_LE_YTD';
248 END IF;
249
250 /* The Balance's defined balance id are stored in the following order
251 -----------------------------------------------------
252 Storage Location of
253 Run Defined Balance Id Balance Name
254 -----------------------------------------------------
255 1 Earnings_Total
256 2 Direct Payments
257 3 Termination_Payments
258 4 Involuntary Deductions
259 5 Pre Tax Deductions
260 6 Termination Deductions
261 7 Voluntary Deductions
262 8 Total_Tax_Deduction
263 9 Earnings_Non_Taxable
264 10 Employer_Charges
265 -----------------------------------------------------
266 If required, YTD defined balance ids are stored for all the balances in the same order as mentioned above
267 from location 10 to 19 */
268
269 FOR csr_rec IN csr_defined_balance_id(l_run_dimension_name)
270 LOOP
271 g_balance_value_tab(csr_rec.sort_index).defined_balance_id := csr_rec.defined_balance_id;
272 END LOOP;
273
274 IF g_debug THEN
275 hr_utility.trace('Defined Balance ids for RUN dimension');
276 hr_utility.trace('-------------------------------------');
277 hr_utility.trace('Earnings_Total ===>' || g_balance_value_tab(1).defined_balance_id);
278 hr_utility.trace('Direct Payments ===>' || g_balance_value_tab(2).defined_balance_id);
279 hr_utility.trace('Termination_Payments ===>' || g_balance_value_tab(3).defined_balance_id);
280 hr_utility.trace('Involuntary Deductions===>' || g_balance_value_tab(4).defined_balance_id);
281 hr_utility.trace('Pre Tax Deductions ===>' || g_balance_value_tab(5).defined_balance_id);
282 hr_utility.trace('Termination Deductions===>' || g_balance_value_tab(6).defined_balance_id);
283 hr_utility.trace('Voluntary Deductions ===>' || g_balance_value_tab(7).defined_balance_id);
284 hr_utility.trace('Total_Tax_Deduction ===>' || g_balance_value_tab(8).defined_balance_id);
285 hr_utility.trace('Earnings_Non_Taxable ===>' || g_balance_value_tab(9).defined_balance_id);
286 hr_utility.trace('Employer_Charges ===>' || g_balance_value_tab(10).defined_balance_id);
287 END IF;
288
289 IF (p_ytd_totals = 'Y') THEN
290
291 FOR csr_rec IN csr_defined_balance_id(l_ytd_dimension_name)
292 LOOP
293 g_ytd_balance_value_tab(csr_rec.sort_index).defined_balance_id := csr_rec.defined_balance_id; /*Bug 4040688*/
294 END LOOP;
295
296 IF g_debug THEN
297 hr_utility.trace('Defined Balance ids for YTD dimension');
298 hr_utility.trace('-------------------------------------');
299 hr_utility.trace('Earnings_Total ===>' || g_ytd_balance_value_tab(1).defined_balance_id);
300 hr_utility.trace('Direct Payments ===>' || g_ytd_balance_value_tab(2).defined_balance_id);
301 hr_utility.trace('Termination_Payments ===>' || g_ytd_balance_value_tab(3).defined_balance_id);
302 hr_utility.trace('Involuntary Deductions===>' || g_ytd_balance_value_tab(4).defined_balance_id);
303 hr_utility.trace('Pre Tax Deductions ===>' || g_ytd_balance_value_tab(5).defined_balance_id);
307 hr_utility.trace('Earnings_Non_Taxable ===>' || g_ytd_balance_value_tab(9).defined_balance_id);
304 hr_utility.trace('Termination Deductions===>' || g_ytd_balance_value_tab(6).defined_balance_id);
305 hr_utility.trace('Voluntary Deductionsn ===>' || g_ytd_balance_value_tab(7).defined_balance_id);
306 hr_utility.trace('Total_Tax_Deduction ===>' || g_ytd_balance_value_tab(8).defined_balance_id);
308 hr_utility.trace('Employer_Charges ===>' || g_ytd_balance_value_tab(10).defined_balance_id);
309 END IF;
310
311 END IF;
312
313 END;
314
315 /* Bug 4036052
316 * Implemented the Horizontal Archive for Payroll Rec- Summary Report
317 * Procedures
318 * 1. range_code
319 * 2. assignment_action_code
320 * 3. archive_code
321 * 4. spawn_archive_reports
322 */
323
324 procedure range_code
325 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type
326 ,p_sql out NOCOPY varchar2
327 ) is
328
329 l_procedure varchar2(200) ;
330
331 begin
332
333 g_debug :=hr_utility.debug_enabled ;
334
335 if g_debug then
336 l_procedure := g_package||'range_code';
337 hr_utility.set_location('Entering '||l_procedure,1);
338 end if ;
339
340 -- Archive the payroll action level data and EIT defintions.
341 -- sql string to SELECT a range of assignments eligible for archival.
342 p_sql := ' select distinct p.person_id' ||
343 ' from per_people_f p,' ||
344 ' pay_payroll_actions pa' ||
345 ' where pa.payroll_action_id = :payroll_action_id' ||
346 ' and p.business_group_id = pa.business_group_id' ||
347 ' order by p.person_id';
348
349 if g_debug then
350 hr_utility.set_location('Leaving '||l_procedure,1000);
351 end if;
352
353 end range_code;
354
355 procedure initialization_code
356 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type)
357 is
358 l_procedure varchar2(200) ;
359
360 /*Bug 4132149 - Modification begins here */
361 --------------------------------------------------------------------+
362 -- Cursor : csr_params
363 -- Description : Fetches User Parameters from Legislative_paramters
364 -- column.
365 --------------------------------------------------------------------+
366
367 CURSOR csr_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
368 IS
369 SELECT pay_core_utils.get_parameter('PAY',legislative_parameters) payroll_id,
370 pay_core_utils.get_parameter('ORG',legislative_parameters) org_id,
371 pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
372 to_date(pay_core_utils.get_parameter('SDATE',legislative_parameters),'YYYY/MM/DD') start_date,
373 to_date(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD') end_date,
374 pay_core_utils.get_parameter('PACTID',legislative_parameters) pact_id,
375 pay_core_utils.get_parameter('LE',legislative_parameters) legal_employer,
376 pay_core_utils.get_parameter('ASG',legislative_parameters) assignment_id,
377 pay_core_utils.get_parameter('SO1',legislative_parameters) sort_order_1,
378 pay_core_utils.get_parameter('SO2',legislative_parameters) sort_order_2,
379 pay_core_utils.get_parameter('SO3',legislative_parameters) sort_order_3,
380 pay_core_utils.get_parameter('SO4',legislative_parameters) sort_order_4,
381 to_date(pay_core_utils.get_parameter('PEDATE',legislative_parameters),'YYYY/MM/DD') period_end_date,
382 pay_core_utils.get_parameter('YTD_TOT',legislative_parameters) ytd_totals,
383 pay_core_utils.get_parameter('ZERO_REC',legislative_parameters) zero_records,
384 pay_core_utils.get_parameter('NEG_REC',legislative_parameters) negative_records,
385 decode(pay_core_utils.get_parameter('EMP_TYPE',legislative_parameters),'C','Y','T','N','%') employee_type,
386 pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions /*Bug# 4142159*/
387 FROM pay_payroll_actions ppa
388 WHERE ppa.payroll_action_id = c_payroll_action_id;
389
390 --------------------------------------------------------------------+
391 -- Cursor : csr_period_date_earned
392 -- Description : Fetches Date Earned for a given payroll
393 -- run.
394 --------------------------------------------------------------------+
395 CURSOR csr_period_date_earned(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
396 IS
397 SELECT ppa.date_earned
398 FROM pay_payroll_actions ppa
399 WHERE
400 ppa.payroll_action_id = c_payroll_action_id;
401
402 /*Bug 4132149 - Modification ends here */
403
404 begin
405
406 g_debug :=hr_utility.debug_enabled ;
407 if g_debug then
408 g_package := 'pay_au_reconciliation_pkg.' ;
409 l_procedure := g_package||'initialization_code';
410 hr_utility.set_location('Entering '||l_procedure,1);
411 end if;
412
416 -- that will be used by each thread in multi-threading.
413 /*Bug 4132149 - Modification begins here */
414
415 -- initialization_code to to set the global tables for EIT
417
418 g_arc_payroll_action_id := p_payroll_action_id;
419
420 -- Fetch the parameters by user passed into global variable.
421
422 OPEN csr_params(p_payroll_action_id);
423 FETCH csr_params into g_parameters;
424 CLOSE csr_params;
425
426
427 if g_debug then
428 hr_utility.set_location('p_payroll_action_id.........= ' || p_payroll_action_id,30);
429 hr_utility.set_location('g_parameters.business_group_id.........= ' || g_parameters.business_group_id,30);
430 hr_utility.set_location('g_parameters.payroll_id..............= ' || g_parameters.payroll_id,30);
431 hr_utility.set_location('g_parameters.org_id................= ' || g_parameters.org_id,30);
432 hr_utility.set_location('g_parameters.legal_employer.........= ' || g_parameters.legal_employer,30);
433 hr_utility.set_location('g_parameters.start_date..............= ' || g_parameters.start_date,30);
434 hr_utility.set_location('g_parameters.end_date................= ' || g_parameters.end_date,30);
435 hr_utility.set_location('g_parameters.period_end_date.........= ' || g_parameters.period_end_date,30);
436 hr_utility.set_location('g_parameters.pact_id..............= ' || g_parameters.pact_id,30);
437 hr_utility.set_location('g_parameters.employee_type..........= '||g_parameters.employee_type,30);
438 hr_utility.set_location('g_parameters.sort_order1..........= '||g_parameters.sort_order_1,30);
439 hr_utility.set_location('g_parameters.sort_order2..........= '||g_parameters.sort_order_2,30);
440 hr_utility.set_location('g_parameters.sort_order3..........= '||g_parameters.sort_order_3,30);
441 hr_utility.set_location('g_parameters.sort_order4..........= '||g_parameters.sort_order_4,30);
442 hr_utility.set_location('g_parameters.delete_actions..........= '||g_parameters.delete_actions,30);/*Bug# 4142159*/
443 end if;
444
445
446 g_business_group_id := g_parameters.business_group_id ;
447
448 -- Set end date variable .This value is used to fetch latest assignment details of
449 -- employee for archival.In case of archive start date/end date - archive end date
450 -- taken and pact_id/period_end_date , period end date is picked.
451
452 if g_parameters.end_date is not null
453 then
454 g_end_date := g_parameters.end_date;
455 g_start_date := g_parameters.start_date; --Bug#3662449
456 else
457 if g_parameters.period_end_date is not null
458 then
459 open csr_period_date_earned(g_parameters.pact_id); --Bug#3662449
460 fetch csr_period_date_earned into g_start_date;
461 close csr_period_date_earned;
462 g_end_date := g_parameters.period_end_date;
463 else
464 g_start_date := to_date('1900/01/01','YYYY/MM/DD'); --Bug#3662449
465 g_end_date := to_date('4712/12/31','YYYY/MM/DD');
466 end if;
467 end if; /* End of outer if loop */
468
469 /*Bug 4132149 - Modification ends here */
470
471 pay_au_reconciliation_pkg.populate_defined_balance_ids('Y',g_parameters.legal_employer);
472 if g_debug then
473 hr_utility.set_location('Leaving '||l_procedure,1000);
474 end if;
475
476 exception
477 when others then
478 hr_utility.set_location('Error in '||l_procedure,999999);
479 raise;
480 end initialization_code;
481
482 --------------------------------------------------------------------+
483 -- Name : assignment_Action_code
484 -- Type : Procedure
485 -- Access: Public
486 -- This procedure further restricts the assignment_id's
487 -- returned by range_code
488 -- This procedure gets the parameters given by user and restricts
489 -- the assignments to be archived.
490 -- it then calls hr_nonrun.insact to create an assignment action id
491 -- it then archives Payroll Run assignment action id details
492 -- in pay_Action_information with context 'AU_ARCHIVE_ASG_DETAILS'
493 -- for each assignment.
494 -- There are 10 different cursors for choosing the assignment ids.
495 -- Depending on the parameters passed,the appropriate cursor is used.
496 --------------------------------------------------------------------+
497
498 procedure assignment_action_code
499 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type
500 ,p_start_person in per_all_people_f.person_id%type
501 ,p_end_person in per_all_people_f.person_id%type
502 ,p_chunk in number
503 ) is
504
505 --------------------------------------------------------------------+
506 -- Cursor : csr_assignment_org_period
507 -- Description : Fetches assignments when Organization,Archive
508 -- Start Date and End Date is specified
509 --------------------------------------------------------------------+
510
511 cursor csr_assignment_org_period
512 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
513 ,c_start_person per_all_people_f.person_id%type
514 ,c_end_person per_all_people_f.person_id%type
515 ,c_employee_type per_all_people_f.current_employee_flag%type
516 ,c_business_group_id hr_all_organization_units.organization_id%type
517 ,c_organization_id hr_all_organization_units.organization_id%type
518 ,c_archive_start_date date
519 ,c_archive_end_date date
523 paaf.assignment_id,
520 ) is
521 select paa.assignment_action_id,
522 paa.action_sequence,
524 paa.tax_unit_id
525 from per_people_f pap,
526 per_assignments_f paaf,
527 pay_payroll_actions ppa,
528 pay_payroll_actions ppa1,
529 pay_assignment_actions paa,
530 hr_organization_units hou,
531 per_periods_of_service pps
532 where ppa.payroll_action_id = c_payroll_action_id
533 and paa.assignment_id = paaf.assignment_id
534 and pap.person_id between c_start_person and c_end_person
535 and pap.person_id = paaf.person_id
536 and pap.person_id = pps.person_id
537 and pps.period_of_service_id = paaf.period_of_service_id
538 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
539 and ppa1.payroll_action_id = paa.payroll_action_id
540 AND paa.action_status ='C' /*Bug 4099317*/
541 and ppa1.business_group_id = ppa.business_group_id
542 and ppa.business_group_id = c_business_group_id
543 and ppa1.action_type in ('R','Q','I','B','V')
544 and paa.source_action_id is null
545 and paaf.organization_id = hou.organization_id
546 and hou.business_group_id = c_business_group_id
547 and hou.organization_id = c_organization_id
548 and ppa1.effective_date between c_archive_start_date and c_archive_end_date
549 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
550 and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
551 From per_assignments_f iipaf
552 WHERE iipaf.assignment_id = paaf.assignment_id
553 and iipaf.effective_end_date >= c_archive_start_date
554 and iipaf.effective_start_date <= c_archive_end_date)
555 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
556
557
558 --------------------------------------------------------------------+
559 -- Cursor : csr_assignment_org_run
560 -- Description : Fetches assignments when Organization,Payroll Run
561 -- and Period End Date is specified
562 --------------------------------------------------------------------+
563
564 cursor csr_assignment_org_run
565 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
566 ,c_start_person per_all_people_f.person_id%type
567 ,c_end_person per_all_people_f.person_id%type
568 ,c_employee_type per_all_people_f.current_employee_flag%type
569 ,c_business_group_id hr_all_organization_units.organization_id%type
570 ,c_organization_id hr_all_organization_units.organization_id%type
571 ,c_period_end_date date
572 ,c_pact_id pay_payroll_actions.payroll_action_id%type
573 ) is
574 select paa.assignment_action_id,
575 paa.action_sequence,
576 paaf.assignment_id,
577 paa.tax_unit_id
578 from per_people_f pap,
579 per_assignments_f paaf,
580 pay_payroll_actions ppa,
581 pay_payroll_actions ppa1,
582 pay_assignment_actions paa,
583 hr_organization_units hou,
584 per_periods_of_service pps
585 where ppa.payroll_action_id = c_payroll_action_id
586 and paa.assignment_id = paaf.assignment_id
587 and pap.person_id between c_start_person and c_end_person
588 and pap.person_id = paaf.person_id
589 and pap.person_id = pps.person_id
590 and pps.period_of_service_id = paaf.period_of_service_id
591 and ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
592 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
593 and ppa1.payroll_action_id = paa.payroll_action_id
594 AND paa.action_status ='C' /*Bug 4099317*/
595 and ppa1.business_group_id = ppa.business_group_id
596 and ppa.business_group_id = c_business_group_id
597 and ppa1.action_type in ('R','Q','I','B','V')
598 and paa.source_action_id is null
599 and paaf.organization_id = hou.organization_id
600 and hou.business_group_id = c_business_group_id
601 and NVL(pap.current_employee_flag,'N') like c_employee_type
602 and hou.organization_id = c_organization_id
603 and ppa1.payroll_action_id = c_pact_id
604 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
605
606 --------------------------------------------------------------------+
607 -- Cursor : csr_assignment_legal_period
608 -- Description : Fetches assignments when Legal Employer,Archive
609 -- Start Date and End Date is specified
610 --------------------------------------------------------------------+
611 /*Bug 3935471 - modified cursor to return the assignment action id and
612 tax unit of the master wherever there is a master-child
613 relationship*/
614
615 cursor csr_assignment_legal_period
616 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
617 ,c_start_person per_all_people_f.person_id%type
618 ,c_end_person per_all_people_f.person_id%type
622 ,c_archive_start_date date
619 ,c_employee_type per_all_people_f.current_employee_flag%type
620 ,c_business_group_id hr_all_organization_units.organization_id%type
621 ,c_legal_employer hr_all_organization_units.organization_id%type
623 ,c_archive_end_date date
624 ) is
625 select paa.assignment_action_id,
626 paa.action_sequence,
627 paaf.assignment_id,
628 paa.tax_unit_id,
629 paa.source_action_id master_action_id, /*Bug# 3935471*/
630 paa2.tax_unit_id master_tax_unit_id /*Bug# 3935471*/
631 from per_people_f pap,
632 per_assignments_f paaf,
633 pay_payroll_actions ppa,
634 pay_payroll_actions ppa1,
635 pay_assignment_actions paa,
636 pay_assignment_actions paa2, /*Bug# 3935471*/
637 per_periods_of_service pps
638 where ppa.payroll_action_id = c_payroll_action_id
639 and paa.assignment_id = paaf.assignment_id /*Bug# 3935471*/
640 AND paa2.assignment_id = paa.assignment_id
641 and pap.person_id between c_start_person and c_end_person
642 and pap.person_id = paaf.person_id
643 and pap.person_id = pps.person_id
644 and pps.period_of_service_id = paaf.period_of_service_id
645 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
646 and ppa1.payroll_action_id = paa.payroll_action_id
647 and ppa1.payroll_action_id = paa2.payroll_action_id /*Bug# 3935471*/
648 AND paa2.action_status ='C' /*Bug# 3935471*/
649 AND paa.action_status ='C' /*Bug 4099317*/
650 AND paa2.assignment_action_id = nvl(paa.source_action_id, paa.assignment_action_id) /*Bug# 3935471*/
651 and ppa1.business_group_id = ppa.business_group_id
652 and ppa.business_group_id = c_business_group_id
653 and ppa1.action_type in ('R','Q','I','B','V')
654 and paa.tax_unit_id = c_legal_employer
655 and ppa1.effective_date between c_archive_start_date and c_archive_end_date
656 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
657 and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
658 From per_assignments_f iipaf
659 WHERE iipaf.assignment_id = paaf.assignment_id
660 and iipaf.effective_end_date >= c_archive_start_date
661 and iipaf.effective_start_date <= c_archive_end_date)
662 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id, paa.source_action_id, paa2.tax_unit_id; /*Bug# 3935471*/
663
664
665 --------------------------------------------------------------------+
666 -- Cursor : csr_assignment_legal_run
667 -- Description : Fetches assignments when Legal Employer,Payroll Run
668 -- and Period End Date is specified
669 --------------------------------------------------------------------+
670 /*Bug 3935471 - modified cursor to return the assignment action id and
671 tax unit of the master wherever there is a master-child
672 relationship*/
673
674 cursor csr_assignment_legal_run
675 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
676 ,c_start_person per_all_people_f.person_id%type
677 ,c_end_person per_all_people_f.person_id%type
678 ,c_employee_type per_all_people_f.current_employee_flag%type
679 ,c_business_group_id hr_all_organization_units.organization_id%type
680 ,c_legal_employer hr_all_organization_units.organization_id%type
681 ,c_period_end_date date
682 ,c_pact_id pay_payroll_actions.payroll_action_id%type
683 ) is
684 select paa.assignment_action_id,
685 paa.action_sequence,
686 paaf.assignment_id,
687 paa.tax_unit_id,
688 paa.source_action_id master_action_id, /*Bug# 3935471*/
689 paa2.tax_unit_id master_tax_unit_id /*Bug# 3935471*/
690 from per_people_f pap,
691 per_assignments_f paaf,
692 pay_payroll_actions ppa,
693 pay_payroll_actions ppa1,
694 pay_assignment_actions paa,
695 pay_assignment_actions paa2, /*Bug# 3935471*/
696 per_periods_of_service pps
697 where ppa.payroll_action_id = c_payroll_action_id
698 and paa.assignment_id = paaf.assignment_id
699 AND paa2.assignment_id = paa.assignment_id /*Bug# 3935471*/
700 and pap.person_id between c_start_person and c_end_person
701 and pap.person_id = paaf.person_id
702 and pap.person_id = pps.person_id
703 and pps.period_of_service_id = paaf.period_of_service_id
704 and ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
705 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
706 and ppa1.payroll_action_id = paa.payroll_action_id
707 AND ppa1.payroll_action_id = paa2.payroll_action_id /*Bug# 3935471*/
708 AND paa2.assignment_action_id = nvl(paa.source_action_id, paa.assignment_action_id) /*Bug# 3935471*/
709 AND paa.action_status ='C' /*Bug 4099317*/
710 AND paa2.action_status = 'C' /*Bug# 3935471*/
711 and ppa1.business_group_id = ppa.business_group_id
715 and paa.tax_unit_id = c_legal_employer
712 and ppa.business_group_id = c_business_group_id
713 and ppa1.action_type in ('R','Q','I','B','V')
714 and NVL(pap.current_employee_flag,'N') like c_employee_type
716 and ppa1.payroll_action_id = c_pact_id
717 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id, paa.source_action_id, paa2.tax_unit_id; /*Bug# 3935471*/
718
719 --------------------------------------------------------------------+
720 -- Cursor : csr_assignment_payroll_period
721 -- Description : Fetches assignments when Payroll,Archive Start
722 -- Date and End Date is specified
723 --------------------------------------------------------------------+
724
725 cursor csr_assignment_payroll_period
726 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
727 ,c_start_person per_all_people_f.person_id%type
728 ,c_end_person per_all_people_f.person_id%type
729 ,c_employee_type per_all_people_f.current_employee_flag%type
730 ,c_business_group_id hr_all_organization_units.organization_id%type
731 ,c_payroll_id pay_payroll_actions.payroll_id%type
732 ,c_archive_start_date date
733 ,c_archive_end_date date
734 ) is
735 select paa.assignment_action_id,
736 paa.action_sequence,
737 paaf.assignment_id,
738 paa.tax_unit_id
739 from per_people_f pap,
740 per_assignments_f paaf,
741 pay_payroll_actions ppa,
742 pay_payroll_actions ppa1,
743 pay_assignment_actions paa,
744 per_periods_of_service pps
745 where ppa.payroll_action_id = c_payroll_action_id
746 and paa.assignment_id = paaf.assignment_id
747 and pap.person_id between c_start_person and c_end_person
748 and pap.person_id = paaf.person_id
749 and pap.person_id = pps.person_id
750 and pps.period_of_service_id = paaf.period_of_service_id
751 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
752 and ppa1.payroll_action_id = paa.payroll_action_id
753 AND paa.action_status ='C' /*Bug 4099317*/
754 and ppa1.business_group_id = ppa.business_group_id
755 and ppa.business_group_id = c_business_group_id
756 and ppa1.action_type in ('R','Q','I','B','V')
757 and paa.source_action_id is null
758 and ppa1.effective_date between c_archive_start_date and c_archive_end_date
759 AND paaf.payroll_id = c_payroll_id /*Bug 4040688*/
760 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
761 and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
762 From per_assignments_f iipaf
763 WHERE iipaf.assignment_id = paaf.assignment_id
764 and iipaf.effective_end_date >= c_archive_start_date
765 and iipaf.effective_start_date <= c_archive_end_date
766 AND iipaf.payroll_id IS NOT NULL) /*Bug#4688800*/
767 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
768
769 --------------------------------------------------------------------+
770 -- Cursor : csr_assignment_payroll_run
771 -- Description : Fetches assignments when Payroll,Payroll Run
772 -- and Period End Date is specified
773 --------------------------------------------------------------------+
774
775 cursor csr_assignment_payroll_run
776 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
777 ,c_start_person per_all_people_f.person_id%type
778 ,c_end_person per_all_people_f.person_id%type
779 ,c_employee_type per_all_people_f.current_employee_flag%type
780 ,c_business_group_id hr_all_organization_units.organization_id%type
781 ,c_payroll_id pay_payroll_actions.payroll_id%type
782 ,c_period_end_date date
783 ,c_pact_id pay_payroll_actions.payroll_action_id%type
784 ) is
785 select paa.assignment_action_id,
786 paa.action_sequence,
787 paaf.assignment_id,
788 paa.tax_unit_id
789 from per_people_f pap,
790 per_assignments_f paaf,
791 pay_payroll_actions ppa,
792 pay_payroll_actions ppa1,
793 pay_assignment_actions paa,
794 per_periods_of_service pps
795 where ppa.payroll_action_id = c_payroll_action_id
796 and paa.assignment_id = paaf.assignment_id
797 and pap.person_id between c_start_person and c_end_person
798 and pap.person_id = paaf.person_id
799 and pap.person_id = pps.person_id
800 and pps.period_of_service_id = paaf.period_of_service_id
801 and ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
802 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
803 and ppa1.payroll_action_id = paa.payroll_action_id
804 AND paa.action_status ='C' /*Bug 4099317*/
805 and ppa1.business_group_id = ppa.business_group_id
806 and ppa.business_group_id = c_business_group_id
807 and ppa1.action_type in ('R','Q','I','B','V')
808 and paa.source_action_id is null
812 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
809 and NVL(pap.current_employee_flag,'N') like c_employee_type
810 and ppa1.payroll_id = c_payroll_id
811 and ppa1.payroll_action_id = c_pact_id
813
814
815 --------------------------------------------------------------------+
816 -- Cursor : csr_assignment_period
817 -- Description : Fetches assignments when Assignment,Archive Start
818 -- Date and End Date is specified
819 --------------------------------------------------------------------+
820
821 cursor csr_assignment_period
822 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
823 ,c_start_person per_all_people_f.person_id%type
824 ,c_end_person per_all_people_f.person_id%type
825 ,c_employee_type per_all_people_f.current_employee_flag%type
826 ,c_business_group_id hr_all_organization_units.organization_id%type
827 ,c_assignment_id per_all_assignments_f.assignment_id%type
828 ,c_archive_start_date date
829 ,c_archive_end_date date
830 ) is
831 select paa.assignment_action_id,
832 paa.action_sequence,
833 paaf.assignment_id,
834 paa.tax_unit_id
835 from per_people_f pap,
836 per_assignments_f paaf,
837 pay_payroll_actions ppa,
838 pay_payroll_actions ppa1,
839 pay_assignment_actions paa,
840 per_periods_of_service pps
841 where ppa.payroll_action_id = c_payroll_action_id
842 and paa.assignment_id = paaf.assignment_id
843 and pap.person_id between c_start_person and c_end_person
844 and pap.person_id = paaf.person_id
845 and pap.person_id = pps.person_id
846 and pps.period_of_service_id = paaf.period_of_service_id
847 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
848 and ppa1.payroll_action_id = paa.payroll_action_id
849 AND paa.action_status ='C' /*Bug 4099317*/
850 and ppa1.business_group_id = ppa.business_group_id
851 and ppa.business_group_id = c_business_group_id
852 and ppa1.action_type in ('R','Q','I','B','V')
853 and paa.source_action_id is null
854 and paa.assignment_id = c_assignment_id
855 and ppa1.effective_date between c_archive_start_date and c_archive_end_date
856 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
857 and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
858 From per_assignments_f iipaf
859 WHERE iipaf.assignment_id = paaf.assignment_id
860 and iipaf.effective_end_date >= c_archive_start_date
861 and iipaf.effective_start_date <= c_archive_end_date)
862 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
863
864 -------------------------------------------------------------------+
865 -- Cursor : csr_assignment_run
866 -- Description : Fetches assignments when Assignment,Payroll Run
867 -- and Period End Date is specified
868 --------------------------------------------------------------------+
869
870 cursor csr_assignment_run
871 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
872 ,c_start_person per_all_people_f.person_id%type
873 ,c_end_person per_all_people_f.person_id%type
874 ,c_employee_type per_all_people_f.current_employee_flag%type
875 ,c_business_group_id hr_all_organization_units.organization_id%type
876 ,c_assignment_id per_all_assignments_f.assignment_id%type
877 ,c_period_end_date date
878 ,c_pact_id pay_payroll_actions.payroll_action_id%type
879 ) is
880 select paa.assignment_action_id,
881 paa.action_sequence,
882 paaf.assignment_id,
883 paa.tax_unit_id
884 from per_people_f pap,
885 per_assignments_f paaf,
886 pay_payroll_actions ppa,
887 pay_payroll_actions ppa1,
888 pay_assignment_actions paa,
889 per_periods_of_service pps
890 where ppa.payroll_action_id = c_payroll_action_id
891 and paa.assignment_id = paaf.assignment_id
892 and pap.person_id between c_start_person and c_end_person
893 and pap.person_id = paaf.person_id
894 and pap.person_id = pps.person_id
895 and pps.period_of_service_id = paaf.period_of_service_id
896 and ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
897 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
898 and ppa1.payroll_action_id = paa.payroll_action_id
899 AND paa.action_status ='C' /*Bug 4099317*/
900 and ppa1.business_group_id = ppa.business_group_id
901 and ppa.business_group_id = c_business_group_id
902 and ppa1.action_type in ('R','Q','I','B','V')
903 and paa.source_action_id is null
904 and NVL(pap.current_employee_flag,'N') like c_employee_type
905 and paa.assignment_id = c_assignment_id
906 and ppa1.payroll_action_id = c_pact_id
907 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
908
909 --------------------------------------------------------------------+
913 --------------------------------------------------------------------+
910 -- Cursor : csr_assignment_default_period
911 -- Description : Fetches assignments when Archive Start date
912 -- and End Date is specified
914
915 cursor csr_assignment_default_period
916 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
917 ,c_start_person per_all_people_f.person_id%type
918 ,c_end_person per_all_people_f.person_id%type
919 ,c_employee_type per_all_people_f.current_employee_flag%type
920 ,c_business_group_id hr_all_organization_units.organization_id%type
921 ,c_archive_start_date date
922 ,c_archive_end_date date
923 ) is
924 select paa.assignment_action_id,
925 paa.action_sequence,
926 paaf.assignment_id,
927 paa.tax_unit_id
928 from per_people_f pap,
929 per_assignments_f paaf,
930 pay_payroll_actions ppa,
931 pay_payroll_actions ppa1,
932 pay_assignment_actions paa,
933 per_periods_of_service pps
934 where ppa.payroll_action_id = c_payroll_action_id
935 and paa.assignment_id = paaf.assignment_id
936 and pap.person_id between c_start_person and c_end_person
937 and pap.person_id = paaf.person_id
938 and pap.person_id = pps.person_id
939 and pps.period_of_service_id = paaf.period_of_service_id
940 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
941 and ppa1.payroll_action_id = paa.payroll_action_id
942 AND paa.action_status ='C' /*Bug 4099317*/
943 and ppa1.business_group_id = ppa.business_group_id
944 and ppa.business_group_id = c_business_group_id
945 and ppa1.action_type in ('R','Q','I','B','V')
946 and paa.source_action_id is null
947 and ppa1.effective_date between c_archive_start_date and c_archive_end_date
948 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
949 and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
950 From per_assignments_f iipaf
951 WHERE iipaf.assignment_id = paaf.assignment_id
952 and iipaf.effective_end_date >= c_archive_start_date
953 and iipaf.effective_start_date <= c_archive_end_date)
954 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
955
956 --------------------------------------------------------------------+
957 -- Cursor : csr_assignment_default_run
958 -- Description : Fetches assignments when Payroll Run
959 -- and Period End Date is specified
960 --------------------------------------------------------------------+
961
962 cursor csr_assignment_default_run
963 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
964 ,c_start_person per_all_people_f.person_id%type
965 ,c_end_person per_all_people_f.person_id%type
966 ,c_employee_type per_all_people_f.current_employee_flag%type
967 ,c_business_group_id hr_all_organization_units.organization_id%type
968 ,c_period_end_date date
969 ,c_pact_id pay_payroll_actions.payroll_action_id%type
970 ) is
971 select paa.assignment_action_id,
972 paa.action_sequence,
973 paaf.assignment_id,
974 paa.tax_unit_id
975 from per_people_f pap,
976 per_assignments_f paaf,
977 pay_payroll_actions ppa,
978 pay_payroll_actions ppa1,
979 pay_assignment_actions paa,
980 per_periods_of_service pps
981 where ppa.payroll_action_id = c_payroll_action_id
982 and paa.assignment_id = paaf.assignment_id
983 and pap.person_id between c_start_person and c_end_person
984 and pap.person_id = paaf.person_id
985 and pap.person_id = pps.person_id
986 and pps.period_of_service_id = paaf.period_of_service_id
987 and ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
988 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
989 and ppa1.payroll_action_id = paa.payroll_action_id
990 AND paa.action_status ='C' /*Bug 4099317*/
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 paa.source_action_id is null
995 and NVL(pap.current_employee_flag,'N') like c_employee_type
996 and ppa1.payroll_action_id = c_pact_id
997 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
998
999
1000 --------------------------------------------------------------------+
1001 -- Cursor : csr_params
1002 -- Description : Fetches User Parameters from Legislative_paramters
1003 -- column.
1004 --------------------------------------------------------------------+
1005
1006 CURSOR csr_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
1007 IS
1008 SELECT pay_core_utils.get_parameter('PAY',legislative_parameters) payroll_id,
1009 pay_core_utils.get_parameter('ORG',legislative_parameters) org_id,
1010 pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
1014 pay_core_utils.get_parameter('LE',legislative_parameters) legal_employer,
1011 to_date(pay_core_utils.get_parameter('SDATE',legislative_parameters),'YYYY/MM/DD') start_date,
1012 to_date(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD') end_date,
1013 pay_core_utils.get_parameter('PACTID',legislative_parameters) pact_id,
1015 pay_core_utils.get_parameter('ASG',legislative_parameters) assignment_id,
1016 pay_core_utils.get_parameter('SO1',legislative_parameters) sort_order_1,
1017 pay_core_utils.get_parameter('SO2',legislative_parameters) sort_order_2,
1018 pay_core_utils.get_parameter('SO3',legislative_parameters) sort_order_3,
1019 pay_core_utils.get_parameter('SO4',legislative_parameters) sort_order_4,
1020 to_date(pay_core_utils.get_parameter('PEDATE',legislative_parameters),'YYYY/MM/DD') period_end_date,
1021 pay_core_utils.get_parameter('YTD_TOT',legislative_parameters) ytd_totals,
1022 pay_core_utils.get_parameter('ZERO_REC',legislative_parameters) zero_records,
1023 pay_core_utils.get_parameter('NEG_REC',legislative_parameters) negative_records,
1024 decode(pay_core_utils.get_parameter('EMP_TYPE',legislative_parameters),'C','Y','T','N','%') employee_type,
1025 pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions /*Bug# 4142159*/
1026 FROM pay_payroll_actions ppa
1027 WHERE ppa.payroll_action_id = c_payroll_action_id;
1028
1029 --------------------------------------------------------------------+
1030 -- Cursor : csr_period_date_earned
1031 -- Description : Fetches Date Earned for a given payroll
1032 -- run.
1033 --------------------------------------------------------------------+
1034 /*Bug#3662449 *********/
1035 CURSOR csr_period_date_earned(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
1036 IS
1037 SELECT ppa.date_earned
1038 FROM pay_payroll_actions ppa
1039 WHERE
1040 ppa.payroll_action_id = c_payroll_action_id;
1041
1042
1043 cursor csr_next_action_id is
1044 select pay_assignment_actions_s.nextval
1045 from dual;
1046
1047 l_next_assignment_action_id pay_assignment_actions.assignment_action_id%type;
1048 l_procedure varchar2(200) ;
1049 i number;
1050
1051 l_action_information_id number;
1052 l_object_version_number number;
1053
1054
1055 begin
1056 i := 1;
1057 g_debug :=hr_utility.debug_enabled ;
1058 if g_debug then
1059 g_package := 'pay_au_reconciliation_pkg.' ;
1060 l_procedure := g_package||'assignment_action_code';
1061 hr_utility.set_location('Entering ' || l_procedure,1);
1062 hr_utility.set_location('Entering assignment_Action_code',302);
1063 end if;
1064
1065 -- initialization_code to to set the global tables for EIT
1066 -- that will be used by each thread in multi-threading.
1067
1068 g_arc_payroll_action_id := p_payroll_action_id;
1069
1070 -- Fetch the parameters by user passed into global variable.
1071
1072 OPEN csr_params(p_payroll_action_id);
1073 FETCH csr_params into g_parameters;
1074 CLOSE csr_params;
1075
1076
1077 if g_debug then
1078 hr_utility.set_location('p_payroll_action_id.........= ' || p_payroll_action_id,30);
1079 hr_utility.set_location('p_start_person..............= ' || p_start_person,30);
1080 hr_utility.set_location('p_end_person................= ' || p_end_person,30);
1081 hr_utility.set_location('g_parameters.business_group_id.........= ' || g_parameters.business_group_id,30);
1082 hr_utility.set_location('g_parameters.payroll_id..............= ' || g_parameters.payroll_id,30);
1083 hr_utility.set_location('g_parameters.org_id................= ' || g_parameters.org_id,30);
1084 hr_utility.set_location('g_parameters.legal_employer.........= ' || g_parameters.legal_employer,30);
1085 hr_utility.set_location('g_parameters.start_date..............= ' || g_parameters.start_date,30);
1086 hr_utility.set_location('g_parameters.end_date................= ' || g_parameters.end_date,30);
1087 hr_utility.set_location('g_parameters.period_end_date.........= ' || g_parameters.period_end_date,30);
1088 hr_utility.set_location('g_parameters.pact_id..............= ' || g_parameters.pact_id,30);
1089 hr_utility.set_location('g_parameters.employee_type..........= '||g_parameters.employee_type,30);
1090 hr_utility.set_location('g_parameters.sort_order1..........= '||g_parameters.sort_order_1,30);
1091 hr_utility.set_location('g_parameters.sort_order2..........= '||g_parameters.sort_order_2,30);
1092 hr_utility.set_location('g_parameters.sort_order3..........= '||g_parameters.sort_order_3,30);
1093 hr_utility.set_location('g_parameters.sort_order4..........= '||g_parameters.sort_order_4,30);
1094 hr_utility.set_location('g_parameters.delete_actions..........= '||g_parameters.delete_actions,30);/*Bug# 4142159*/
1095 end if;
1096
1097
1098 g_business_group_id := g_parameters.business_group_id ;
1099
1100 -- Set end date variable .This value is used to fetch latest assignment details of
1101 -- employee for archival.In case of archive start date/end date - archive end date
1102 -- taken and pact_id/period_end_date , period end date is picked.
1103
1104 if g_parameters.end_date is not null
1105 then
1109 if g_parameters.period_end_date is not null
1106 g_end_date := g_parameters.end_date;
1107 g_start_date := g_parameters.start_date; --Bug#3662449
1108 else
1110 then
1111 open csr_period_date_earned(g_parameters.pact_id); --Bug#3662449
1112 fetch csr_period_date_earned into g_start_date;
1113 close csr_period_date_earned;
1114 g_end_date := g_parameters.period_end_date;
1115 else
1116 g_start_date := to_date('1900/01/01','YYYY/MM/DD'); --Bug#3662449
1117 g_end_date := to_date('4712/12/31','YYYY/MM/DD');
1118 end if;
1119 end if; /* End of outer if loop */
1120
1121
1122 if g_parameters.org_id is not null
1123 then
1124 if g_parameters.start_date is not null and g_parameters.end_date is not null
1125 then
1126 FOR csr_rec in csr_assignment_org_period(p_payroll_action_id,
1127 p_start_person,
1128 p_end_person,
1129 g_parameters.employee_type,
1130 g_parameters.business_group_id,
1131 g_parameters.org_id,
1132 g_parameters.start_date,
1133 g_parameters.end_date)
1134 LOOP /*Loop 1 Org,Archive start date,end date */
1135 open csr_next_action_id;
1136 fetch csr_next_action_id into l_next_assignment_action_id;
1137 close csr_next_action_id;
1138
1139 if g_debug then
1140
1141 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
1142 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
1143 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
1144 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
1145
1146 end if;
1147
1148
1149
1150 -- Create the archive assignment actions
1151 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1152
1153
1154 insert into pay_action_information(
1155 action_information_id,
1156 action_context_id,
1157 action_context_type,
1158 effective_date,
1159 source_id,
1160 tax_unit_id,
1161 action_information_category,
1162 action_information1,
1163 action_information2,
1164 action_information3,
1165 assignment_id
1166 )
1167 values(
1168 pay_action_information_s.nextval,
1169 l_next_assignment_action_id,
1170 'AAP',
1171 null,
1172 null,
1173 csr_rec.tax_unit_id,
1174 'AU_ARCHIVE_ASG_DETAILS',
1175 csr_rec.assignment_action_id,
1176 p_payroll_action_id,
1177 csr_rec.action_sequence,
1178 csr_rec.assignment_id
1179 );
1180
1181
1182 END LOOP;/* Loop 1 */
1183 if g_debug then
1184 hr_utility.set_location('Leaving............Loop1 Org+Period....' || l_procedure,1000);
1185 end if;
1186
1187 else
1188 FOR csr_rec in csr_assignment_org_run(p_payroll_action_id,
1189 p_start_person,
1190 p_end_person,
1191 g_parameters.employee_type,
1192 g_parameters.business_group_id,
1193 g_parameters.org_id,
1194 g_parameters.period_end_date,
1195 g_parameters.pact_id)
1196 LOOP /*Loop 2 Org,Pact_id and period end date*/
1197 open csr_next_action_id;
1198 fetch csr_next_action_id into l_next_assignment_action_id;
1199 close csr_next_action_id;
1200
1201 if g_debug then
1202 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
1203 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
1204 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
1205 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
1206 end if;
1207
1208 -- Create the archive assignment actions
1209 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1210
1211 insert into pay_action_information(
1212 action_information_id,
1213 action_context_id,
1214 action_context_type,
1215 effective_date,
1216 source_id,
1217 tax_unit_id,
1218 action_information_category,
1219 action_information1,
1223 )
1220 action_information2,
1221 action_information3,
1222 assignment_id
1224 values(
1225 pay_action_information_s.nextval,
1226 l_next_assignment_action_id,
1227 'AAP',
1228 null,
1229 null,
1230 csr_rec.tax_unit_id,
1231 'AU_ARCHIVE_ASG_DETAILS',
1232 csr_rec.assignment_action_id,
1233 p_payroll_action_id,
1234 csr_rec.action_sequence,
1235 csr_rec.assignment_id
1236 );
1237
1238
1239 END LOOP; /* Loop 2 */
1240 if g_debug then
1241 hr_utility.set_location('Leaving............Loop2 ,Org + Run....' || l_procedure,1000);
1242 end if;
1243 end if; /* End of Inner Organization */
1244 else /* Not Org,check for others */
1245
1246 if g_parameters.legal_employer is not null
1247 then
1248 if g_parameters.start_date is not null and g_parameters.end_date is not null
1249 then
1250 FOR csr_rec in csr_assignment_legal_period(p_payroll_action_id,
1251 p_start_person,
1252 p_end_person,
1253 g_parameters.employee_type,
1254 g_parameters.business_group_id,
1255 g_parameters.legal_employer,
1256 g_parameters.start_date,
1257 g_parameters.end_date)
1258 LOOP /*Loop 3 Leg Employer,Archive Start date,archive end date*/
1259 /*Bug 3935471 - IF Condition used to archive all master actions and only those child actions which have tax unit id not same as master*/
1260
1261 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
1262 open csr_next_action_id;
1263 fetch csr_next_action_id into l_next_assignment_action_id;
1264 close csr_next_action_id;
1265 if g_debug then
1266 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
1267 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
1268 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
1269 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
1270 end if;
1271
1272 -- Create the archive assignment actions
1273 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1274
1275 insert into pay_action_information(
1276 action_information_id,
1277 action_context_id,
1278 action_context_type,
1279 effective_date,
1280 source_id,
1281 tax_unit_id,
1282 action_information_category,
1283 action_information1,
1284 action_information2,
1285 action_information3,
1286 assignment_id
1287 )
1288 values(
1289 pay_action_information_s.nextval,
1290 l_next_assignment_action_id,
1291 'AAP',
1292 null,
1293 null,
1294 csr_rec.tax_unit_id,
1295 'AU_ARCHIVE_ASG_DETAILS',
1296 csr_rec.assignment_action_id,
1297 p_payroll_action_id,
1298 csr_rec.action_sequence,
1299 csr_rec.assignment_id
1300 );
1301
1302 END IF;
1303 END LOOP;/* Loop 3 */
1304 if g_debug then
1305 hr_utility.set_location('Leaving............Loop3.Legal Emp + period...' || l_procedure,1000);
1306 end if;
1307
1308 else
1309 FOR csr_rec in csr_assignment_legal_run(p_payroll_action_id,
1310 p_start_person,
1311 p_end_person,
1312 g_parameters.employee_type,
1313 g_parameters.business_group_id,
1314 g_parameters.legal_employer,
1315 g_parameters.period_end_date,
1316 g_parameters.pact_id)
1317 LOOP /*Loop 4 Leg employer,pact_id + period end date */
1318 /*Bug 3935471 - IF Condition used to archive all master actions and only those child actions which have tax unit id not same as master*/
1319
1320 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
1321 open csr_next_action_id;
1322 fetch csr_next_action_id into l_next_assignment_action_id;
1326 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
1323 close csr_next_action_id;
1324
1325 if g_debug then
1327 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
1328 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
1329 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
1330 end if;
1331
1332 -- Create the archive assignment actions
1333 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1334
1335 insert into pay_action_information(
1336 action_information_id,
1337 action_context_id,
1338 action_context_type,
1339 effective_date,
1340 source_id,
1341 tax_unit_id,
1342 action_information_category,
1343 action_information1,
1344 action_information2,
1345 action_information3,
1346 assignment_id
1347 )
1348 values(
1349 pay_action_information_s.nextval,
1350 l_next_assignment_action_id,
1351 'AAP',
1352 null,
1353 null,
1354 csr_rec.tax_unit_id,
1355 'AU_ARCHIVE_ASG_DETAILS',
1356 csr_rec.assignment_action_id,
1357 p_payroll_action_id,
1358 csr_rec.action_sequence,
1359 csr_rec.assignment_id
1360 );
1361
1362 END IF;
1363 END LOOP; /* Loop 4 */
1364 if g_debug then
1365 hr_utility.set_location('Leaving............Loop4.Legal Emp + Run...' || l_procedure,1000);
1366 end if;
1367 end if; /* End of Inner Legal Employer */
1368 else /* Not Org,Legal Emp Check others */
1369
1370 if g_parameters.payroll_id is not null
1371 then
1372 if g_parameters.start_date is not null and g_parameters.end_date is not null
1373 then
1374 FOR csr_rec in csr_assignment_payroll_period(p_payroll_action_id,
1375 p_start_person,
1376 p_end_person,
1377 g_parameters.employee_type,
1378 g_parameters.business_group_id,
1379 g_parameters.payroll_id,
1380 g_parameters.start_date,
1381 g_parameters.end_date)
1382 LOOP /*Loop 5 Payroll, Archive start date,end date */
1383 open csr_next_action_id;
1384 fetch csr_next_action_id into l_next_assignment_action_id;
1385 close csr_next_action_id;
1386
1387 if g_debug then
1388 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
1389 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
1390 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
1391 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
1392 end if;
1393
1394 -- Create the archive assignment actions
1395 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1396
1397 insert into pay_action_information(
1398 action_information_id,
1399 action_context_id,
1400 action_context_type,
1401 effective_date,
1402 source_id,
1403 tax_unit_id,
1404 action_information_category,
1405 action_information1,
1406 action_information2,
1407 action_information3,
1408 assignment_id
1409 )
1410 values(
1411 pay_action_information_s.nextval,
1412 l_next_assignment_action_id,
1413 'AAP',
1414 null,
1415 null,
1416 csr_rec.tax_unit_id,
1417 'AU_ARCHIVE_ASG_DETAILS',
1418 csr_rec.assignment_action_id,
1419 p_payroll_action_id,
1420 csr_rec.action_sequence,
1421 csr_rec.assignment_id
1422 );
1423
1424
1425 END LOOP;/* Loop 5 */
1426
1427 if g_debug then
1428 hr_utility.set_location('Leaving............Loop5 Payroll + Period....' || l_procedure,1000);
1432 FOR csr_rec in csr_assignment_payroll_run(p_payroll_action_id,
1429 end if;
1430
1431 else
1433 p_start_person,
1434 p_end_person,
1435 g_parameters.employee_type,
1436 g_parameters.business_group_id,
1437 g_parameters.payroll_id,
1438 g_parameters.period_end_date,
1439 g_parameters.pact_id)
1440 LOOP /*Loop 6 Payroll, pact_id + period end date*/
1441 open csr_next_action_id;
1442 fetch csr_next_action_id into l_next_assignment_action_id;
1443 close csr_next_action_id;
1444
1445 if g_debug then
1446 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
1447 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
1448 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
1449 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
1450 end if;
1451
1452 -- Create the archive assignment actions
1453 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1454
1455 insert into pay_action_information(
1456 action_information_id,
1457 action_context_id,
1458 action_context_type,
1459 effective_date,
1460 source_id,
1461 tax_unit_id,
1462 action_information_category,
1463 action_information1,
1464 action_information2,
1465 action_information3,
1466 assignment_id
1467 )
1468 values(
1469 pay_action_information_s.nextval,
1470 l_next_assignment_action_id,
1471 'AAP',
1472 null,
1473 null,
1474 csr_rec.tax_unit_id,
1475 'AU_ARCHIVE_ASG_DETAILS',
1476 csr_rec.assignment_action_id,
1477 p_payroll_action_id,
1478 csr_rec.action_sequence,
1479 csr_rec.assignment_id
1480 );
1481
1482
1483 END LOOP; /* Loop 6 */
1484 if g_debug then
1485 hr_utility.set_location('Leaving............Loop6 Payroll+ Run....' || l_procedure,1000);
1486 end if;
1487 end if; /* End of Inner Payroll */
1488 else /* Not Org,Legal,Payroll check others */
1489
1490 if g_parameters.assignment_id is not null
1491 then
1492 if g_parameters.start_date is not null and g_parameters.end_date is not null
1493 then
1494 FOR csr_rec in csr_assignment_period(p_payroll_action_id,
1495 p_start_person,
1496 p_end_person,
1497 g_parameters.employee_type,
1498 g_parameters.business_group_id,
1499 g_parameters.assignment_id,
1500 g_parameters.start_date,
1501 g_parameters.end_date)
1502 LOOP /*Loop 7 Assignment ,Archive start date,end date*/
1503 open csr_next_action_id;
1504 fetch csr_next_action_id into l_next_assignment_action_id;
1505 close csr_next_action_id;
1506
1507 if g_debug then
1508 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
1509 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
1510 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
1511 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
1512 end if;
1513
1514 -- Create the archive assignment actions
1515 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1516
1517 insert into pay_action_information(
1518 action_information_id,
1519 action_context_id,
1520 action_context_type,
1521 effective_date,
1522 source_id,
1523 tax_unit_id,
1524 action_information_category,
1525 action_information1,
1526 action_information2,
1527 action_information3,
1528 assignment_id
1529 )
1530 values(
1531 pay_action_information_s.nextval,
1532 l_next_assignment_action_id,
1533 'AAP',
1534 null,
1535 null,
1539 p_payroll_action_id,
1536 csr_rec.tax_unit_id,
1537 'AU_ARCHIVE_ASG_DETAILS',
1538 csr_rec.assignment_action_id,
1540 csr_rec.action_sequence,
1541 csr_rec.assignment_id
1542 );
1543
1544
1545 END LOOP;/* Loop 7 */
1546 if g_debug then
1547 hr_utility.set_location('Leaving............Loop7. Asg + Period...' || l_procedure,1000);
1548 end if;
1549
1550 else
1551 FOR csr_rec in csr_assignment_run(p_payroll_action_id,
1552 p_start_person,
1553 p_end_person,
1554 g_parameters.employee_type,
1555 g_parameters.business_group_id,
1556 g_parameters.assignment_id,
1557 g_parameters.period_end_date,
1558 g_parameters.pact_id)
1559 LOOP /*Loop 8 Assignment Pact_id,Period end date */
1560 open csr_next_action_id;
1561 fetch csr_next_action_id into l_next_assignment_action_id;
1562 close csr_next_action_id;
1563
1564 if g_debug then
1565 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
1566 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
1567 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
1568 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
1569 end if;
1570
1571 -- Create the archive assignment actions
1572 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1573
1574 insert into pay_action_information(
1575 action_information_id,
1576 action_context_id,
1577 action_context_type,
1578 effective_date,
1579 source_id,
1580 tax_unit_id,
1581 action_information_category,
1582 action_information1,
1583 action_information2,
1584 action_information3,
1585 assignment_id
1586 )
1587 values(
1588 pay_action_information_s.nextval,
1589 l_next_assignment_action_id,
1590 'AAP',
1591 null,
1592 null,
1593 csr_rec.tax_unit_id,
1594 'AU_ARCHIVE_ASG_DETAILS',
1595 csr_rec.assignment_action_id,
1596 p_payroll_action_id,
1597 csr_rec.action_sequence,
1598 csr_rec.assignment_id
1599 );
1600
1601
1602 END LOOP; /* Loop 8 */
1603 if g_debug then
1604 hr_utility.set_location('Leaving............Loop8.Asg + Run...' || l_procedure,1000);
1605 end if;
1606 end if; /* End of Inner Assignment */
1607
1608 else
1609
1610 /* Default Begins */
1611
1612 if g_parameters.start_date is not null and g_parameters.end_date is not null
1613 then
1614 FOR csr_rec in csr_assignment_default_period(p_payroll_action_id,
1615 p_start_person,
1616 p_end_person,
1617 g_parameters.employee_type,
1618 g_parameters.business_group_id,
1619 g_parameters.start_date,
1620 g_parameters.end_date)
1621 LOOP /*Loop 9*/
1622 open csr_next_action_id;
1623 fetch csr_next_action_id into l_next_assignment_action_id;
1624 close csr_next_action_id;
1625
1626 if g_debug then
1627 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
1628 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
1629 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
1630 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
1631 end if;
1632
1633 -- Create the archive assignment actions
1634 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1635
1636 insert into pay_action_information(
1637 action_information_id,
1638 action_context_id,
1639 action_context_type,
1640 effective_date,
1641 source_id,
1642 tax_unit_id,
1643 action_information_category,
1647 assignment_id
1644 action_information1,
1645 action_information2,
1646 action_information3,
1648 )
1649 values(
1650 pay_action_information_s.nextval,
1651 l_next_assignment_action_id,
1652 'AAP',
1653 null,
1654 null,
1655 csr_rec.tax_unit_id,
1656 'AU_ARCHIVE_ASG_DETAILS',
1657 csr_rec.assignment_action_id,
1658 p_payroll_action_id,
1659 csr_rec.action_sequence,
1660 csr_rec.assignment_id
1661 );
1662
1663
1664 END LOOP;/* Loop 9 */
1665 if g_debug then
1666 hr_utility.set_location('Leaving............Loop9..Default + Period..' || l_procedure,1000);
1667 end if;
1668
1669 else
1670 FOR csr_rec in csr_assignment_default_run(p_payroll_action_id,
1671 p_start_person,
1672 p_end_person,
1673 g_parameters.employee_type,
1674 g_parameters.business_group_id,
1675 g_parameters.period_end_date,
1676 g_parameters.pact_id)
1677 LOOP /*Loop 10 */
1678 open csr_next_action_id;
1679 fetch csr_next_action_id into l_next_assignment_action_id;
1680 close csr_next_action_id;
1681
1682 if g_debug then
1683 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
1684 hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
1685 hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
1686 hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
1687 end if;
1688
1689 -- Create the archive assignment actions
1690 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1691
1692 insert into pay_action_information(
1693 action_information_id,
1694 action_context_id,
1695 action_context_type,
1696 effective_date,
1697 source_id,
1698 tax_unit_id,
1699 action_information_category,
1700 action_information1,
1701 action_information2,
1702 action_information3,
1703 assignment_id
1704 )
1705 values(
1706 pay_action_information_s.nextval,
1707 l_next_assignment_action_id,
1708 'AAP',
1709 null,
1710 null,
1711 csr_rec.tax_unit_id,
1712 'AU_ARCHIVE_ASG_DETAILS',
1713 csr_rec.assignment_action_id,
1714 p_payroll_action_id,
1715 csr_rec.action_sequence,
1716 csr_rec.assignment_id
1717 );
1718
1719
1720 END LOOP; /* Loop 10 */
1721 if g_debug then
1722 hr_utility.set_location('Leaving............Loop10 Default + Run....' || l_procedure,1000);
1723 end if;
1724 end if; /* End of Inner Default */
1725
1726
1727 end if ;/*End Assignment id */
1728 end if ; /* End Payroll */
1729 end if; /* End Legal */
1730 end if; /* End Organization */
1731
1732 exception
1733 when others then
1734 hr_utility.set_location('Error in '||l_procedure,999999);
1735 raise;
1736 end assignment_action_code;
1737
1738 procedure archive_code
1739 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
1740 ,p_effective_date in pay_payroll_actions.effective_date%type
1741 ) is
1742
1743 /* Bug 3873942
1744 Cursor c_employee_details - Added join with table per_grades_tl to fetch and archive the grade name.
1745 Outer Join introduced between per_grades_tl and per_assignments_f based on grade_id.
1746 */
1747
1748 cursor c_employee_details(c_business_group_id hr_all_organization_units.organization_id%TYPE,
1749 c_assignment_id number,c_end_date date, c_start_date date) /*Bug#3662449 c_start_date parameter added*/
1750 is
1751 select pap.full_name,
1752 paa.assignment_number,
1753 paa.assignment_id,
1754 paa.organization_id,
1755 hou.NAME organization_name, /*Bug 4132525*/
1756 -- paa.payroll_id, /*Bug 4688800*/
1757 -- papf.payroll_name, /*Bug 4132525, Bug 4688800*/
1758 hsc.segment1 tax_unit_id, /*Bug 4040688*/
1759 hou1.NAME Legal_Employer /*Bug 4132525*/
1763 hr_organization_units hou, /*Bug 4132525*/
1760 from per_people_f pap,
1761 per_assignments_f paa,
1762 hr_soft_coding_keyflex hsc, /*Bug 4040688*/
1764 hr_organization_units hou1 /*Bug 4132525*/
1765 -- pay_payrolls_f papf /*Bug 4132525, Bug 4688800*/
1766 where pap.person_id = paa.person_id
1767 and paa.assignment_id = c_assignment_id
1768 and paa.business_group_id = c_business_group_id
1769 AND hsc.soft_coding_keyflex_id = paa.soft_coding_keyflex_id /*Bug 4040688*/
1770 AND hou.organization_id = paa.organization_id /*Bug 4132525*/
1771 AND hou1.organization_id = hsc.segment1 /*Bug 4132525*/
1772 -- AND papf.payroll_id = paa.payroll_id /*Bug 4132525, Bug 4688800*/
1773 -- AND c_end_date BETWEEN papf.effective_start_date AND papf.effective_end_date /*Bug 4132525, Bug 4688800*/
1774 and paa.effective_end_date = ( select max(effective_end_date) /*Bug#3662449 sub query added*/
1775 from per_assignments_f
1776 WHERE assignment_id = c_assignment_id
1777 and effective_end_date >= c_start_date
1778 and effective_start_date <= c_end_date)
1779 and c_end_date between pap.effective_start_date and pap.effective_end_date;
1780
1781
1782 /*Bug# 4688800 - Introduced a new cursor to get the payroll name for the employee. This has been done to take care of cases
1783 where assignment has payroll attached to it for few months but is not attached at the end of year*/
1784 CURSOR c_get_payroll_name(c_assignment_id number,c_end_date date, c_start_date date)
1785 IS
1786 SELECT paaf.payroll_id, pay.payroll_name
1787 FROM per_all_assignments_f paaf,
1788 pay_payrolls_f pay
1789 WHERE paaf.assignment_id = c_assignment_id
1790 and paaf.effective_end_date = (select max(effective_end_date)
1791 From per_assignments_f iipaf
1792 WHERE iipaf.assignment_id = c_assignment_id
1793 and iipaf.effective_end_date >= c_start_date
1794 and iipaf.effective_start_date <= c_end_date
1795 AND iipaf.payroll_id IS NOT NULL)
1796 AND pay.payroll_id = paaf.payroll_id
1797 AND paaf.effective_end_date BETWEEN pay.effective_start_date AND pay.effective_end_date;
1798
1799 cursor csr_get_data (c_arc_ass_act_id number)
1800 is
1801 select pai.action_information1, pai.tax_unit_id, pai.assignment_id,pai.action_information3
1802 from pay_action_information pai
1803 where action_information_category = 'AU_ARCHIVE_ASG_DETAILS'
1804 and pai.action_context_id = c_arc_ass_act_id;
1805
1806 /*Bug 4040688 - Two cursors introduced to get the maximum assignment action id for the assignment*/
1807 cursor csr_get_max_asg_dates (c_assignment_id number,
1808 c_start_date DATE,
1809 c_end_date DATE,
1810 c_tax_unit_id number)
1811 is
1812 select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
1813 ,max(paa.action_sequence)
1814 from pay_assignment_actions paa
1815 , pay_payroll_actions ppa
1816 , per_assignments_f paf
1817 where paa.assignment_id = paf.assignment_id
1818 and paf.assignment_id = c_assignment_id
1819 and ppa.payroll_action_id = paa.payroll_action_id
1820 and ppa.effective_date between c_start_date and c_end_date
1821 and ppa.payroll_id = paf.payroll_id
1822 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
1823 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
1824 and paa.action_status='C'
1825 AND paa.tax_unit_id = nvl(c_tax_unit_id, paa.tax_unit_id);
1826
1827
1828 cursor csr_get_max_asg_action (c_assignment_id number,
1829 c_payroll_action_id number,
1830 c_tax_unit_id number)
1831 is
1832 select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
1833 ,max(paa.action_sequence)
1834 from pay_assignment_actions paa
1835 , pay_payroll_actions ppa
1836 , per_assignments_f paf
1837 where paa.assignment_id = paf.assignment_id
1838 and paf.assignment_id = c_assignment_id
1839 and ppa.payroll_action_id = paa.payroll_action_id
1840 and ppa.payroll_action_id = c_payroll_action_id
1841 and ppa.payroll_id = paf.payroll_id
1842 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
1843 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
1844 and paa.action_status='C'
1845 AND paa.tax_unit_id = nvl(c_tax_unit_id, paa.tax_unit_id);
1846
1847 /*Bug 4040688 - end of modification*/
1848
1849 l_procedure varchar2(200);
1850 l_action_information_id number;
1851 l_object_version_number number;
1852
1853 l_TAXABLE_EARNINGS number;
1854 l_GROSS_EARNINGS number;
1855 l_PRE_TAX_DEDUCTIONS number;
1856 l_DIRECT_PAYMENTS number;
1857 l_NON_TAXABLE_EARNINGS number;
1858 l_DEDUCTIONS number;
1859 l_TAX number;
1860 l_NET_PAYMENT number;
1861 l_EMPLOYER_CHARGES number;
1862
1863 l_YTD_TAXABLE_EARNINGS number;
1867 l_YTD_DIRECT_PAYMENTS number;
1864 l_YTD_NON_TAXABLE_EARNINGS number;
1865 l_YTD_GROSS_EARNINGS number;
1866 l_YTD_PRE_TAX_DEDUCTIONS number;
1868 l_YTD_DEDUCTIONS number;
1869 l_YTD_TAX number;
1870 l_YTD_NET_PAYMENT number;
1871 l_YTD_EMPLOYER_CHARGES number;
1872
1873 l_ass_act_id number;
1874 l_tax_unit_id number;
1875 l_assignment_id number;
1876
1877 l_action_sequence number;
1878 l_max_asg_action_id number; /*Bug 4040688*/
1879 l_max_action_sequence number; /*Bug 4040688*/
1880
1881 l_payroll_id number; /*Bug 4688800*/
1882 l_payroll_name pay_payrolls_f.payroll_name%type; /*Bug 4688800*/
1883
1884 begin
1885
1886 g_debug :=hr_utility.debug_enabled ;
1887
1888 l_YTD_GROSS_EARNINGS := 0;
1889 l_YTD_NON_TAXABLE_EARNINGS := 0;
1890 l_YTD_PRE_TAX_DEDUCTIONS := 0;
1891 l_YTD_TAXABLE_EARNINGS := 0;
1892 l_YTD_TAX := 0;
1893 l_YTD_DEDUCTIONS := 0;
1894 l_YTD_DIRECT_PAYMENTS := 0;
1895 l_YTD_NET_PAYMENT := 0;
1896 l_YTD_EMPLOYER_CHARGES := 0;
1897
1898 if g_debug then
1899 g_package := 'pay_au_reconciliation_pkg.' ;
1900 l_procedure := g_package||'archive_code';
1901 hr_utility.set_location('Entering '||l_procedure,1);
1902 hr_utility.set_location('p_assignment_action_id......= '|| p_assignment_action_id,10);
1903 hr_utility.set_location('p_effective_date............= '|| to_char(p_effective_date,'DD-MON-YYYY'),10);
1904 end if;
1905
1906 OPEN csr_get_data(p_assignment_action_id);
1907 FETCH csr_get_data into l_ass_act_id, l_tax_unit_id, l_assignment_id,l_action_sequence;
1908 CLOSE csr_get_data;
1909
1910 if g_debug then
1911 hr_utility.set_location('l_ass_act_id......= '|| l_ass_act_id,10);
1912 hr_utility.set_location('l_tax_unit_id............= '|| l_tax_unit_id,10);
1913 hr_utility.set_location('l_assignment_id......= '|| l_assignment_id,10);
1914 end if;
1915
1916 FOR csr_rec in c_employee_details(g_business_group_id,l_assignment_id,g_end_date,g_start_date) --Bug#3662449
1917 LOOP
1918
1919 if g_debug then
1920 hr_utility.set_location('csr_rec.full_name............= '|| csr_rec.full_name,10);
1921 end if;
1922
1923
1924 IF (NVL(g_prev_assignment_id,0) <> csr_rec.assignment_id) THEN
1925 g_prev_assignment_id := csr_rec.assignment_id;
1926
1927 /*Bug 4040688 - Calling the cursor to get maximum assignment action id*/
1928 IF g_parameters.pact_id IS NULL THEN
1929 OPEN csr_get_max_asg_dates(csr_rec.assignment_id, g_start_date, g_end_date, g_parameters.legal_employer);
1930 FETCH csr_get_max_asg_dates INTO l_max_asg_action_id, l_max_action_sequence;
1931 CLOSE csr_get_max_asg_dates;
1932 ELSE
1933 OPEN csr_get_max_asg_action(csr_rec.assignment_id, g_parameters.pact_id, g_parameters.legal_employer);
1934 FETCH csr_get_max_asg_action INTO l_max_asg_action_id, l_max_action_sequence;
1935 CLOSE csr_get_max_asg_action;
1936 END IF ;
1937
1938 -- Archive YTD balance details
1939 /*Bug 3953706 - Modfied the call to procedure introduce new parameters*/
1940 /*Bug 4040688 - YTD Balances will be called for the maximum assignment action id of the assignment*/
1941
1942 IF l_max_asg_action_id IS NOT NULL THEN
1943 pay_au_reconciliation_pkg.GET_YTD_AU_REC_BALANCES(
1944 P_ASSIGNMENT_ACTION_ID => l_max_asg_action_id,
1945 P_REGISTERED_EMPLOYER => g_parameters.legal_employer, --2610141
1946 P_YTD_GROSS_EARNINGS => l_YTD_GROSS_EARNINGS,
1947 P_YTD_NON_TAXABLE_EARNINGS => l_YTD_NON_TAXABLE_EARNINGS,
1948 P_YTD_PRE_TAX_DEDUCTIONS => l_YTD_PRE_TAX_DEDUCTIONS,
1949 P_YTD_TAXABLE_EARNINGS => l_YTD_TAXABLE_EARNINGS,
1950 P_YTD_TAX => l_YTD_TAX ,
1951 P_YTD_DEDUCTIONS => l_YTD_DEDUCTIONS ,
1952 P_YTD_DIRECT_PAYMENTS => l_YTD_DIRECT_PAYMENTS,
1953 P_YTD_NET_PAYMENT => l_YTD_NET_PAYMENT ,
1954 P_YTD_EMPLOYER_CHARGES => l_YTD_EMPLOYER_CHARGES);
1955 END IF ;
1956
1957 insert into pay_action_information (
1958 action_information_id,
1959 action_context_id,
1960 action_context_type,
1961 effective_date,
1962 source_id,
1963 tax_unit_id,
1964 assignment_id,
1965 action_information_category,
1966 action_information1,
1967 action_information2,
1968 action_information3,
1969 action_information4,
1970 action_information5,
1971 action_information6,
1972 action_information7,
1973 action_information8,
1974 action_information9,
1975 action_information10)
1976 values (
1977 pay_action_information_s.nextval,
1978 p_assignment_action_id,
1979 'AAP',
1980 p_effective_date,
1981 null,
1985 l_YTD_TAXABLE_EARNINGS,
1982 null,
1983 l_assignment_id,
1984 'AU_BALANCE_RECON_DETAILS_YTD',
1986 l_YTD_NON_TAXABLE_EARNINGS,
1987 l_YTD_DEDUCTIONS,
1988 l_YTD_TAX,
1989 l_YTD_NET_PAYMENT,
1990 l_YTD_EMPLOYER_CHARGES,
1991 l_YTD_GROSS_EARNINGS,
1992 l_YTD_PRE_TAX_DEDUCTIONS,
1993 l_YTD_DIRECT_PAYMENTS,
1994 l_max_action_sequence);
1995
1996 /*Bug 4040688 - end of modification*/
1997
1998
1999 /*Bug 4688800*/
2000 OPEN c_get_payroll_name(l_assignment_id,g_end_date,g_start_date);
2001 FETCH c_get_payroll_name INTO l_payroll_id,l_payroll_name;
2002 CLOSE c_get_payroll_name;
2003
2004 insert into pay_action_information(
2005 action_information_id,
2006 action_context_id,
2007 action_context_type,
2008 effective_date,
2009 source_id,
2010 tax_unit_id,
2011 action_information_category,
2012 action_information1,
2013 action_information2,
2014 action_information8,
2015 action_information9,
2016 action_information10,
2017 assignment_id)
2018 values(
2019 pay_action_information_s.nextval,
2020 g_arc_payroll_action_id,
2021 'PA',
2022 p_effective_date,
2023 null,
2024 l_tax_unit_id,
2025 'AU_EMPLOYEE_RECON_DETAILS',
2026 csr_rec.full_name,
2027 csr_rec.assignment_number,
2028 csr_rec.organization_name, /*Bug 4132525*/
2029 csr_rec.Legal_Employer, /*Bug 4040688, Bug 4132525*/
2030 l_payroll_name, /*Bug 4132525, Bug 4688800*/
2031 l_assignment_id);
2032 END IF;
2033 -- Balances Coding for BBR
2034
2035 -- Get The Action Sequence for the Assignment_Action_Id.
2036
2037 /*Bug 3891564 - Modfied the call to procedure introduce new parameters*/
2038 pay_au_reconciliation_pkg.GET_AU_REC_BALANCES(
2039 P_ASSIGNMENT_ACTION_ID => l_ass_act_id,
2040 P_REGISTERED_EMPLOYER => g_parameters.legal_employer,
2041 P_GROSS_EARNINGS => l_GROSS_EARNINGS,
2042 P_NON_TAXABLE_EARNINGS => l_NON_TAXABLE_EARNINGS,
2043 P_PRE_TAX_DEDUCTIONS => l_PRE_TAX_DEDUCTIONS,
2044 P_TAXABLE_EARNINGS => l_TAXABLE_EARNINGS ,
2045 P_TAX => l_TAX ,
2046 P_DEDUCTIONS => l_DEDUCTIONS ,
2047 P_DIRECT_PAYMENTS => l_DIRECT_PAYMENTS,
2048 P_NET_PAYMENT => l_NET_PAYMENT ,
2049 P_EMPLOYER_CHARGES => l_EMPLOYER_CHARGES);
2050
2051 --
2052 -- Insert the balance data into pay_action_information table
2053 -- This Direct Insert statement is for Performance Reasons.
2054 --
2055 /*Bug 4040688 - Modified contexts which will store only the run balance values.*/
2056 insert into pay_action_information (
2057 action_information_id,
2058 action_context_id,
2059 action_context_type,
2060 effective_date,
2061 source_id,
2062 tax_unit_id,
2063 assignment_id,
2064 action_information_category,
2065 action_information1,
2066 action_information2,
2067 action_information3,
2068 action_information4,
2069 action_information5,
2070 action_information6,
2071 action_information7,
2072 action_information8,
2073 action_information9,
2074 action_information10)
2075 values (
2076 pay_action_information_s.nextval,
2077 p_assignment_action_id,
2078 'AAP',
2079 p_effective_date,
2080 null,
2081 l_tax_unit_id,
2082 l_assignment_id,
2083 'AU_BALANCE_RECON_DETAILS_RUN',
2084 l_taxable_earnings,
2085 l_NON_TAXABLE_EARNINGS,
2086 l_DEDUCTIONS,
2087 l_TAX,
2088 l_NET_PAYMENT,
2089 l_EMPLOYER_CHARGES,
2090 l_GROSS_EARNINGS,
2091 l_PRE_TAX_DEDUCTIONS,
2092 l_DIRECT_PAYMENTS,
2093 l_action_sequence);
2094
2095 END LOOP; /* End of assignments for employee */
2096
2097 end archive_code;
2098
2099 procedure spawn_archive_reports
2100 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type)
2101 is
2102 l_count number;
2103 ps_request_id NUMBER;
2104 l_print_style VARCHAR2(2);
2105 l_print_together VARCHAR2(80);
2106 l_print_return BOOLEAN;
2110 --------------------------------------------------------------------+
2107 l_procedure varchar2(50);
2108 l_program_name varchar2(50); /* Bug 5174524 */
2109
2111 -- Cursor : csr_params
2112 -- Description : Fetches User Parameters from Legislative_paramters
2113 -- column.
2114 --------------------------------------------------------------------+
2115
2116 CURSOR csr_report_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
2117 IS
2118 SELECT pay_core_utils.get_parameter('PAY',legislative_parameters) payroll_id,
2119 pay_core_utils.get_parameter('ORG',legislative_parameters) org_id,
2120 pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
2121 to_date(pay_core_utils.get_parameter('SDATE',legislative_parameters),'YYYY/MM/DD') start_date,
2122 to_date(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD') end_date,
2123 pay_core_utils.get_parameter('PACTID',legislative_parameters) pact_id,
2124 pay_core_utils.get_parameter('LE',legislative_parameters) legal_employer,
2125 pay_core_utils.get_parameter('ASG',legislative_parameters) assignment_id,
2126 pay_core_utils.get_parameter('SO1',legislative_parameters) sort_order_1,
2127 pay_core_utils.get_parameter('SO2',legislative_parameters) sort_order_2,
2128 pay_core_utils.get_parameter('SO3',legislative_parameters) sort_order_3,
2129 pay_core_utils.get_parameter('SO4',legislative_parameters) sort_order_4,
2130 to_date(pay_core_utils.get_parameter('PEDATE',legislative_parameters),'YYYY/MM/DD') period_end_date,
2131 pay_core_utils.get_parameter('YTD_TOT',legislative_parameters) ytd_totals,
2132 pay_core_utils.get_parameter('ZERO_REC',legislative_parameters) zero_records,
2133 pay_core_utils.get_parameter('NEG_REC',legislative_parameters) negative_records,
2134 pay_core_utils.get_parameter('EMP_TYPE',legislative_parameters) employee_type,
2135 pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions, /*Bug# 4142159*/
2136 pay_core_utils.get_parameter('OUTPUT_TYPE',legislative_parameters) output_type /*Bug# 5174524*/
2137 FROM pay_payroll_actions ppa
2138 WHERE ppa.payroll_action_id = c_payroll_action_id;
2139
2140
2141 cursor csr_get_print_options(p_payroll_action_id NUMBER) IS
2142 SELECT printer,
2143 print_style,
2144 decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output
2145 ,number_of_copies /* Bug 4116833*/
2146 FROM pay_payroll_actions pact,
2147 fnd_concurrent_requests fcr
2148 WHERE fcr.request_id = pact.request_id
2149 AND pact.payroll_action_id = p_payroll_action_id;
2150
2151
2152 rec_print_options csr_get_print_options%ROWTYPE;
2153
2154 l_parameters csr_report_params%ROWTYPE; /* Bug 3891577*/
2155
2156 Begin
2157 l_count :=0;
2158 ps_request_id :=-1;
2159 g_debug :=hr_utility.debug_enabled ;
2160
2161
2162 if g_debug then
2163 g_package := 'pay_au_reconciliation_pkg.' ;
2164 l_procedure := g_package||' spawn_archive_reports';
2165 hr_utility.set_location('Entering '||l_procedure,999);
2166 end if;
2167
2168 -- Set User Parameters for Report.
2169
2170 open csr_report_params(p_payroll_action_id);
2171 fetch csr_report_params into l_parameters;
2172 close csr_report_params;
2173
2174 /*Bug 4040688 -- Moved the call to check parameters validations from report to archive code.*/
2175 check_report_parameters(l_parameters.start_date
2176 ,l_parameters.end_date
2177 ,l_parameters.period_end_date);
2178
2179 if g_debug then
2180 hr_utility.set_location('payroll_parameters.action '||p_payroll_action_id,0);
2181 hr_utility.set_location('in BG_ID '||l_parameters.business_group_id,1);
2182 hr_utility.set_location('in org_id '||l_parameters.org_id,2);
2183 hr_utility.set_location('in payroll_parameters.id '||l_parameters.payroll_id,3);
2184 hr_utility.set_location('in asg_id '||l_parameters.assignment_id,4);
2185 hr_utility.set_location('in archive start date '||to_char(l_parameters.start_date,'YYYY/MM/DD'),5);
2186 hr_utility.set_location('in archive end date '||to_char(l_parameters.end_date,'YYYY/MM/DD'),6);
2187 hr_utility.set_location('in pact_id '||l_parameters.pact_id,7);
2188 hr_utility.set_location('in legal employer '||l_parameters.legal_employer,8);
2189 hr_utility.set_location('in PERIOD END DATE '||to_char(l_parameters.period_end_date,'YYYY/MM/DD'),9);
2190 hr_utility.set_location('in YTD totals '||l_parameters.ytd_totals,10);
2191 hr_utility.set_location('in zero records'||l_parameters.zero_records,11);
2192 hr_utility.set_location('in Negative records'||l_parameters.negative_records,12);
2193 hr_utility.set_location('in emp_type '||l_parameters.employee_type,14);
2194 hr_utility.set_location('in sort order 1'||l_parameters.sort_order_1,15);
2195 hr_utility.set_location('in sort order 2'||l_parameters.sort_order_2,16);
2196 hr_utility.set_location('in sort order 3'||l_parameters.sort_order_3,17);
2197 hr_utility.set_location('in sort order 4'||l_parameters.sort_order_4,18);
2201
2198 hr_utility.set_location('in delete action'||l_parameters.delete_actions,19); /*Bug# 4142159*/
2199 hr_utility.set_location('in Output Type '||l_parameters.output_type,20); /*Bug# 5174524*/
2200 end if;
2202 if g_debug then
2203 hr_utility.set_location('Afer payroll action ' || p_payroll_action_id , 125);
2204
2205 hr_utility.set_location('Before calling report',24);
2206 end if;
2207
2208 OPEN csr_get_print_options(p_payroll_action_id);
2209 FETCH csr_get_print_options INTO rec_print_options;
2210 CLOSE csr_get_print_options;
2211 --
2212 l_print_together := nvl(fnd_profile.value('CONC_PRINT_TOGETHER'), 'N');
2213 --
2214 -- Set printer options
2215 l_print_return := fnd_request.set_print_options
2216 (printer => rec_print_options.printer,
2217 style => rec_print_options.print_style,
2218 copies => rec_print_options.number_of_copies,/* Bug 4116833*/
2219 save_output => hr_general.char_to_bool(rec_print_options.save_output),
2220 print_together => l_print_together);
2221 -- Submit report
2222 if g_debug then
2223 hr_utility.set_location('payroll_action id '|| p_payroll_action_id,25);
2224 end if;
2225
2226 /* Bug 5174524 - Check for Report Output Type and choose appropriate
2227 concurrent program to submit
2228 */
2229 if l_parameters.output_type = 'TEXT'
2230 then
2231 l_program_name := 'PYAURECSR_TXT';
2232 else
2233 l_program_name := 'PYAURECSR';
2234 end if;
2235
2236 /* Bug 3891577 - Added the Template Name parameter to Report.
2237 This is done to enable the PDF Output for report.
2238 Backed out this parameter as template name is not specified here in 2-setp process.*/
2239 ps_request_id := fnd_request.submit_request
2240 ('PAY',
2241 l_program_name, /* Bug 5174524 */
2242 null,
2243 null,
2244 false,
2245 'P_PAYROLL_ACTION_ID='||to_char(p_payroll_action_id),
2246 'P_BUSINESS_GROUP_ID='||l_parameters.business_group_id,
2247 'P_ORGANIZATION_ID='||l_parameters.org_id,
2248 'P_PAYROLL_ID='||l_parameters.payroll_id,
2249 'P_REGISTERED_EMPLOYER='||l_parameters.legal_employer,
2250 'P_ASSIGNMENT_ID='||l_parameters.assignment_id,
2251 'P_START_DATE='||to_char(l_parameters.start_date,'YYYY/MM/DD'),
2252 'P_END_DATE='||to_char(l_parameters.end_date,'YYYY/MM/DD'),
2253 'P_PAYROLL_RUN_ID='||l_parameters.pact_id,
2254 'P_PERIOD_END_DATE='||to_char(l_parameters.period_end_date,'YYYY/MM/DD'),
2255 'P_EMPLOYEE_TYPE='||l_parameters.employee_type,
2256 'P_YTD_TOTALS='||l_parameters.ytd_totals,
2257 'P_ZERO_RECORDS='||l_parameters.zero_records,
2258 'P_NEGATIVE_RECORDS='||l_parameters.negative_records,
2259 'P_SORT_ORDER_1='||l_parameters.sort_order_1,
2260 'P_SORT_ORDER_2='||l_parameters.sort_order_2,
2261 'P_SORT_ORDER_3='||l_parameters.sort_order_3,
2262 'P_SORT_ORDER_4='||l_parameters.sort_order_4,
2263 'P_DELETE_ACTIONS='||l_parameters.delete_actions, /*Bug# 4142159*/
2264 'BLANKPAGES=NO',
2265 NULL, NULL, NULL, NULL, NULL, NULL,
2266 NULL, NULL, NULL, NULL, NULL, NULL,
2267 NULL, NULL, NULL, NULL, NULL, NULL,
2268 NULL, NULL, NULL, NULL, NULL, NULL,
2269 NULL, NULL, NULL, NULL, NULL, NULL,
2270 NULL, NULL, NULL, NULL, NULL, NULL,
2271 NULL, NULL, NULL, NULL, NULL, NULL,
2272 NULL, NULL, NULL, NULL, NULL, NULL,
2273 NULL, NULL, NULL, NULL, NULL, NULL,
2274 NULL, NULL, NULL, NULL, NULL, NULL,
2275 NULL, NULL, NULL, NULL, NULL, NULL,
2276 NULL, NULL, NULL, NULL, NULL, NULL,
2277 NULL, NULL, NULL, NULL
2278 );
2279 if g_debug then
2280 hr_utility.set_location('After calling report',24);
2281 end if;
2282
2283 end spawn_archive_reports;
2284
2285 procedure check_report_parameters
2286 (p_start_date IN date,
2287 p_end_date IN date,
2288 p_period_end_date IN date) is
2289
2290 e_bad_end_date exception ;
2291 e_bad_combination_date exception ;
2292
2293 begin
2294
2295 IF g_debug THEN
2296 hr_utility.trace('Entering:' || g_package || 'check_report_parameters');
2297 END IF;
2298
2299 if p_start_date is not null and p_end_date is null then
2300 raise e_bad_end_date;
2301 end if;
2302
2303 if p_start_date is null and p_period_end_date is null then
2304 raise e_bad_combination_date;
2305 end if;
2306
2307
2308 exception
2309 when e_bad_end_date
2310 then
2311 hr_utility.set_message(801, 'HR_AU_REC_MISSING_END_DATE');
2312 hr_utility.raise_error;
2313 when e_bad_combination_date
2314 then
2315 hr_utility.set_message(801, 'HR_AU_REC_COMBINATION_DATES');
2316 hr_utility.raise_error;
2317
2318 end check_report_parameters;
2319
2320 BEGIN
2321 /*Bug 2610141 - Code added to remove the gscc warnings */
2322 g_debug := hr_utility.debug_enabled;
2323 g_package := 'pay_au_reconciliation_pkg.';
2324
2325 end pay_au_reconciliation_pkg;