[Home] [Help]
PACKAGE BODY: APPS.PAY_AU_PAYTAX_PKG
Source
1 PACKAGE BODY PAY_AU_PAYTAX_PKG as
2 /* $Header: pyaupyt.pkb 120.21.12010000.3 2008/09/26 01:11:30 skshin ship $ */
3
4 /*
5 **
6 ** Copyright (C) 1999 Oracle Corporation
7 ** All Rights Reserved
8 **
9 ** AU HRMS Payroll Tax package
10 **
11 ** Change List
12 ** ===========
13 **
14 ** Date Author Reference Description
15 ** -----------+--------+---------+-------------
16 ** 29 JAN 2001 SHOSKATT N/A Creation
17 ** 20 Jun 2002 Ragovind 2272424 Modified the Get_Tax Function to handle the user given override threshold.
18 ** 03 Dec 2002 Ragovind 2689226 Added NOCOPY for the function get_tax.
19 ** 09 AUG 2004 abhkumar 2610141 Added tax_unit_id in function GET_BALANCE for Legal Employer enhancement
20
21 ** 25 AUG 2005 hnainani 3541814 Added / Modified functions for Payroll Tax Grouping
22 ** 03 NOV 2005 hnainani 4709766 Added Period to get_parameters function
23 ** 06 Nov 2005 hnainani 4713372 Added an Extra Parameter and cursor to archive le_balances
24 ** 06 Nov 2005 hnainani 4716254 Corrected Flexfields being archived for Org Developer DF
25 ** 07 Nov 2005 hnainani 4718544 Changed Dimension from _LE_RUN to _ASG_LE_RUN
26 ** 10 Nov 2005 Hnainani 4729052 Added Date checks to csr_get_ass_le_act_id to get Total Taxable Income
27 ** 10 Nov 2005 Hnainani 4731692 Added new flexfield to archive State Code in Balance Details
28 ** 05 May 2006 Hnainani 5139764 Added new Termination State Breakdown Balances
29 ** 29-May-2006 Hnainani 5235423 Added new joins to Employee Details Cursor
30 ** 21-FEB-2007 hnainani 5893671 Removed the full name information from c_employee_details cursor
31 ** instead added a new cursor c_get_employee_full_name
32 ** 26-FEB-2008 vdabgar 6839263 Modified cursors,csr_params and csr_report_params to pick p_output_type
33 ** accordingly.
34 ** 18-Mar-2008 avenkatk 6839263 Backed out changes in assignment_Action_code and initialization_code
35 ** 21-Mar-2008 avenkatk 6839263 Added Logic to set the OPP Template options for PDF output
36 ** 19-SEP-2008 skshin 7280733 Added c_session cursor to be able to use Global values in run_formula in get_tax function
37 */
38
39
40 g_debug boolean;
41 g_package varchar2(26);
42
43
44 g_arc_payroll_action_id pay_payroll_actions.payroll_action_id%type;
45 g_business_group_id hr_all_organization_units.organization_id%type;
46 g_prev_assignment_id number;
47 g_le_taxable_income number;
48 g_count number;
49 g_prev_tax_state varchar2(3);
50 g_def_bal_populted varchar2(1);
51
52
53 /* Procedure to pass all the balance results back in one call from report */
54
55 procedure get_balances
56 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
57 p_registered_employer in NUMBER,
58 p_tax_state in varchar2,
59 p_salaries_wages out NOCOPY number,
60 p_commission out NOCOPY number,
61 p_bonus_allowances out NOCOPY number,
62 p_director_fees out NOCOPY number,
63 p_termination_payments out NOCOPY number,
64 p_eligible_term_payments out NOCOPY number,
65 p_Fringe_Benefits out NOCOPY number,
66 p_Superannuation out NOCOPY number,
67 p_Contractor_Payments out NOCOPY number,
68 p_Other_Taxable_Income out NOCOPY number,
69 p_taxable_income out NOCOPY number)
70 is
71
72 begin
73
74 IF g_debug THEN
75 hr_utility.trace('Entering:' || g_package || 'get_balances');
76 hr_utility.trace('Assignment action id value ===>' || p_assignment_action_id);
77 hr_utility.trace('p_registered_employer ===>' || p_registered_employer);
78 END IF;
79
80 /* Call to this function below implements Batch Balance Retrieval for better performance */
81
82 g_context_table(1).tax_unit_id := p_registered_employer;
83 pay_balance_pkg.get_value(p_assignment_action_id => p_assignment_action_id,
84 p_defined_balance_lst=>g_balance_value_tab,
85 p_context_lst =>g_context_table,
86 p_output_table=>g_result_table);
87
88
89
90 hr_utility.trace('Balance values for RUN dimension');
91 hr_utility.trace('-------------------------------------');
92 hr_utility.trace('Salaries_Wages ===>' || g_result_table(1).balance_value);
93 hr_utility.trace('Commission ==>' || g_result_table(2).balance_value);
94 hr_utility.trace('Bonus_Allowances ===>' || g_result_table(3).balance_value);
95 hr_utility.trace('Director_Fees ===>' || g_result_table(4).balance_value);
96 /* hr_utility.trace('Termination_Payments===>' || g_result_table(5).balance_value);
97 hr_utility.trace('Elgiible_Termination_Payments ===>' || g_result_table(6).balance_value); */
98 hr_utility.trace('Fringe_Benefits ===>' || g_result_table(6).balance_value);
99 hr_utility.trace('Superannuation ===>' || g_result_table(7).balance_value);
100 hr_utility.trace('Contractor_Payments ===>' || g_result_table(8).balance_value);
101 hr_utility.trace('Other_Taxable_Income ===>' || g_result_table(9).balance_value);
102 hr_utility.trace('Payroll_Taxable_income ===>' || g_result_table(10).balance_value);
103
104
105
106
107 p_Salaries_Wages := nvl(g_result_table(1).balance_value,0);
108 p_commission := nvl(g_result_table(2).balance_value,0);
109 p_bonus_allowances := nvl(g_result_table(3).balance_value,0);
110 p_director_fees := nvl(g_result_table(4).balance_value,0);
111 p_termination_payments := nvl(g_result_table(5).balance_value,0);
112 p_eligible_term_payments := nvl(g_result_table(6).balance_value,0);
113 p_fringe_benefits := nvl(g_result_table(7).balance_value,0);
114 p_Superannuation := nvl(g_result_table(8).balance_value,0);
115 p_Contractor_payments := nvl(g_result_table(9).balance_value,0);
116 p_other_taxable_income := nvl(g_result_table(10).balance_value,0);
117 p_taxable_income := nvl(g_result_table(11).balance_value,0);
118
119
120 end get_balances;
121
122
123 FUNCTION GET_TAX(p_no_of_states number,
124 p_dge_state varchar2,
125 p_dge_group_name varchar2,
126 p_state_code varchar2,
127 p_taxable_income NUMBER,
128 p_le_taxable_income NUMBER,
129 p_message out NOCOPY varchar2,
130 p_ot_message out NOCOPY varchar2,
131 p_start_date date,
132 p_end_date date,
133 p_override_threshold NUMBER ) RETURN NUMBER IS
134
135 l_tax number;
136 l_formula_id NUMBER;
137 l_inputs ff_exec.inputs_t;
138 l_outputs ff_exec.outputs_t;
139 l_session_flag varchar2(1);
140
141 cursor c_formula is
142 SELECT formula_id
143 FROM ff_formulas_f
144 WHERE formula_name = 'AU_PAYROLL_TAX'
145 AND p_start_date between effective_start_date and effective_end_date
146 ;
147 /*bug7280733*/
148 cursor c_session is
149 SELECT 'X' INTO l_session_flag
150 FROM fnd_sessions
151 WHERE session_id = USERENV('SESSIONID');
152
153 BEGIN
154
155
156 l_inputs(1).name := 'STATE_CODE';
157 l_inputs(1).value := p_state_code;
158 l_inputs(2).name := 'TAXABLE_INCOME';
159 l_inputs(2).value := p_taxable_income;
160 l_inputs(3).name := 'OVERRIDE_THRESHOLD';
161 l_inputs(3).value := p_override_threshold;
162 l_inputs(4).name := 'DGE_STATE';
163 l_inputs(4).value := p_dge_state;
164 l_inputs(5).name := 'LE_TAXABLE_INCOME';
165 l_inputs(5).value := p_le_taxable_income;
166 l_inputs(6).name := 'NO_OF_STATES';
167 l_inputs(6).value := p_no_of_states;
168 l_inputs(7).name := 'GROUP_NAME';
169 l_inputs(7).value := p_dge_group_name;
170 l_outputs(1).name := 'MSG';
171 l_outputs(2).name := 'PAYROLL_TAX';
172 l_outputs(3).name := 'WARN_MSG';
173
174 OPEN c_formula;
175 FETCH c_formula into l_formula_id;
176 /* bug7280733 start */
177 IF c_formula%FOUND THEN
178 OPEN c_session;
179 FETCH c_session into l_session_flag;
180 IF c_session%NOTFOUND THEN
181 insert into fnd_sessions (SESSION_ID, EFFECTIVE_DATE) values (userenv('sessionid'),trunc(p_start_date));
182 END IF;
183 CLOSE c_session;
184 END IF;
185 /* bug7280733 end*/
186 CLOSE c_formula;
187
188 per_formula_functions.run_formula(p_formula_id => l_formula_id,
189 p_calculation_date => last_day(p_start_date),
190 p_inputs => l_inputs,
191 p_outputs => l_outputs);
192
193 l_tax := l_outputs(2).value;
194 hr_utility.trace('l_outputs(1).value :'||l_outputs(1).value);
195 hr_utility.trace('l_outputs(2).value :'||l_outputs(2).value);
196 hr_utility.trace('l_outputs(3).value :'||l_outputs(3).value);
197
198 IF l_outputs(1).value = 'ZZZZ' THEN
199 p_message:=null;
200 ELSE
201 p_message := l_outputs(1).value;
202 END IF;
203
204 IF l_outputs(3).value = 'ZZZZ' THEN
205 p_ot_message := null;
206 ELSE
207 p_ot_message := l_outputs(3).value;
208 END IF;
209
210 hr_utility.trace('p_message :'||p_message);
211 hr_utility.trace('l_tax :'||l_tax);
212 return(l_tax);
213
214 EXCEPTION when others THEN
215 RAISE_APPLICATION_ERROR(-20001,'Function get_tax ' ||sqlerrm);
216
217 END get_tax;
218
219
220
221 PROCEDURE populate_defined_balance_ids
222 ( p_registered_employer NUMBER
223 ) IS
224
225 /* 5139764 */
226 CURSOR csr_defined_balance_id
227 (c_database_item_suffix pay_balance_dimensions.database_item_suffix%type)
228 IS
229 SELECT decode(pbt.balance_name,'Payroll_Tax_Salaries_Wages',1,'Payroll_Tax_Commissions',2,
230 'Payroll_Tax_Bonuses_Allowances',3,'Payroll_Tax_Director_Fees',4,
231 'Payroll_Tax_Fringe_Benefits',7,'Payroll_Tax_Superannuation',8,
232 'Payroll_Tax_Contractor_Payments',9, 'Payroll_Tax_Other_Taxable_Payments' , '10'
233 ) sort_index,
234 pdb.defined_balance_id defined_balance_id
235 FROM pay_balance_types pbt,
236 pay_balance_dimensions pbd,
237 pay_defined_balances pdb
238 WHERE pbt.balance_name IN ( 'Payroll_Tax_Salaries_Wages', 'Payroll_Tax_Commissions' ,
239 'Payroll_Tax_Bonuses_Allowances', 'Payroll_Tax_Director_Fees',
240 'Payroll_Tax_Fringe_Benefits', 'Payroll_Tax_Superannuation', 'Payroll_Tax_Contractor_Payments',
241 'Payroll_Tax_Other_Taxable_Payments' )
242 AND pbd.database_item_suffix = c_database_item_suffix
243 AND pbt.balance_type_id = pdb.balance_type_id
244 AND pbd.balance_dimension_id = pdb.balance_dimension_id
245 AND pbt.legislation_code = 'AU'
246 ORDER BY sort_index;
247
248 l_run_dimension_name VARCHAR2(15);
249 l_ytd_dimension_name VARCHAR2(15);
250
251 BEGIN
252
253 hr_utility.trace('Entering:' || g_package || 'populate_defined_balance_ids');
254
255 g_balance_value_tab.delete;
256
257 l_run_dimension_name := '_ASG_LE_RUN';
258
259 /* The Balance's defined balance id are stored in the following order
260 -----------------------------------------------------
261 Storage Location of
262 Run Defined Balance Id Balance Name
263 -----------------------------------------------------
264 1 Salaries_Wages
265 2 Commmission
266 3 Bonus_Allowances
267 4 Director_Fees
268 7 Fringe_Benefits
269 8 Superannaution
270 9 Contractor_Payments
271 10 Other_Taxable_Payments
272 11 Payroll_Taxable_Income
273 -----------------------------------------------------
274 */
275
276 FOR csr_rec IN csr_defined_balance_id(l_run_dimension_name)
277 LOOP
278 g_balance_value_tab(csr_rec.sort_index).defined_balance_id := csr_rec.defined_balance_id;
279 END LOOP;
280
281
282
283 END;
284
285 /*
286 * Implemented the Horizontal Archive for Payroll Tax Report
287 * Procedures
288 * 1. range_code
289 * 2. assignment_action_code
290 * 3. archive_code
291 * 4. spawn_archive_reports
292 */
293
294 procedure range_code
295 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type
296 ,p_sql out NOCOPY varchar2
297 ) is
298
299 l_procedure varchar2(200) ;
300
301 begin
302
303 g_debug :=hr_utility.debug_enabled ;
304
305 l_procedure := g_package||'range_code';
306 hr_utility.set_location('Entering '||l_procedure,1);
307
308 -- Archive the payroll action level data and EIT defintions.
309 -- sql string to SELECT a range of assignments eligible for archival.
310 p_sql := ' select distinct p.person_id' ||
311 ' from per_people_f p,' ||
312 ' pay_payroll_actions pa' ||
313 ' where pa.payroll_action_id = :payroll_action_id' ||
314 ' and p.business_group_id = pa.business_group_id' ||
315 ' order by p.person_id';
316
317 hr_utility.set_location('Leaving '||l_procedure,1000);
318
319 end range_code;
320
321 procedure initialization_code
322 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type)
323 is
324 l_procedure varchar2(200) ;
325 l_defined_balance_id number;
326 l_term_defined_balance_id number;
327 l_elig_term_defined_balance_id number;
328 l_balance_Value number;
329 l_term_balance_Value number;
330 l_elig_term_balance_Value number;
331
332 --------------------------------------------------------------------+
333 -- Cursor : csr_params
334 -- Description : Fetches User Parameters from Legislative_paramters
335 -- column.
336 --------------------------------------------------------------------+
337
338 CURSOR csr_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
339 IS
340 SELECT pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
341 pay_core_utils.get_parameter('LE',legislative_parameters) legal_employer,
342 to_date(pay_core_utils.get_parameter('PERIOD',legislative_parameters),'YYYY/MM/DD') period, /*4709766 */
343 to_date(pay_core_utils.get_parameter('SDATE',legislative_parameters),'YYYY/MM/DD') start_date,
344 to_date(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD') end_date,
345 pay_core_utils.get_parameter('TAX_STATE',legislative_parameters) Tax_state,
346 pay_core_utils.get_parameter('REP_TYPE',legislative_parameters)report_type,
350 pay_core_utils.get_parameter('NSWT',legislative_parameters)nsw_override_threshold,
347 pay_core_utils.get_parameter('REP_NAME',legislative_parameters)report_name,
348 pay_core_utils.get_parameter('ACTT',legislative_parameters)act_override_threshold,
349 pay_core_utils.get_parameter('VICT',legislative_parameters)vic_override_threshold,
351 pay_core_utils.get_parameter('QLDT',legislative_parameters)qld_override_threshold,
352 pay_core_utils.get_parameter('WAT',legislative_parameters)wa_override_threshold,
353 pay_core_utils.get_parameter('NTT',legislative_parameters)nt_override_threshold,
354 pay_core_utils.get_parameter('SAT',legislative_parameters)sa_override_threshold,
355 pay_core_utils.get_parameter('TAST',legislative_parameters)tas_override_threshold
356 FROM pay_payroll_actions ppa
357 WHERE ppa.payroll_action_id = c_payroll_action_id;
358
359
360 /* 4713372 */
361 CURSOR csr_Paytax_defined_balance_id
362 (c_database_item_suffix pay_balance_dimensions.database_item_suffix%type,
363 c_state_code varchar2)
364 IS
365 SELECT pdb.defined_balance_id defined_balance_id
366 FROM pay_balance_types pbt,
367 pay_balance_dimensions pbd,
368 pay_defined_balances pdb
369 WHERE pbt.balance_name = 'Payroll_Tax_' || c_state_code
370 AND pbd.database_item_suffix = c_database_item_suffix
371 AND pbt.balance_type_id = pdb.balance_type_id
372 AND pbd.balance_dimension_id = pdb.balance_dimension_id
373 AND pbt.legislation_code = 'AU';
374
375 /* 5139764 */
376 CURSOR csr_TPaytax_defined_balance_id
377 (c_database_item_suffix pay_balance_dimensions.database_item_suffix%type,
378 c_state_code varchar2)
379 IS
380 SELECT pdb.defined_balance_id defined_balance_id
381 FROM pay_balance_types pbt,
382 pay_balance_dimensions pbd,
383 pay_defined_balances pdb
384 WHERE pbt.balance_name = 'Payroll_Tax_' || c_state_code || '_Termination_Payments'
385 AND pbd.database_item_suffix = c_database_item_suffix
386 AND pbt.balance_type_id = pdb.balance_type_id
387 AND pbd.balance_dimension_id = pdb.balance_dimension_id
388 AND pbt.legislation_code = 'AU';
389
390
391 /* 5139764 */
392
393 CURSOR csr_ETPaytax_defined_balance
394 (c_database_item_suffix pay_balance_dimensions.database_item_suffix%type,
395 c_state_code varchar2)
396 IS
397 SELECT pdb.defined_balance_id defined_balance_id
398 FROM pay_balance_types pbt,
399 pay_balance_dimensions pbd,
400 pay_defined_balances pdb
401 WHERE pbt.balance_name = 'Payroll_Tax_' || c_state_code || '_Eligible_Termination_Payments'
402 AND pbd.database_item_suffix = c_database_item_suffix
403 AND pbt.balance_type_id = pdb.balance_type_id
404 AND pbd.balance_dimension_id = pdb.balance_dimension_id
405 AND pbt.legislation_code = 'AU';
406
407 /* 4713372 */
408 cursor csr_get_ass_le_act_id (c_business_group_id hr_all_organization_units.organization_id%TYPE,
409 c_registered_employer hr_all_organization_units.organization_id%TYPE,
410 c_start_date date,
411 c_end_date date)
412 is
413 select distinct per_information2, paa.tax_unit_id,
414 assignment_action_id assignment_action_id
415 from pay_payroll_actions ppa,
416 pay_assignment_actions paa,
417 per_people_f pap,
418 per_assignments_f paf
419 where paa.payroll_action_id = ppa.payroll_action_id
420 and pap.person_id = paf.person_id
421 and paa.assignment_id = paf.assignment_id
422 and paa.tax_unit_id=nvl(c_registered_employer, paa.tax_unit_id)
423 and paf.business_group_id=c_business_group_id
424 and ppa.action_status='C'
425 and (pap.per_information3 = 'N' or pap.per_information3 is null)
426 and ppa.action_type in ('R','Q','I','B','V')
427 and ppa.effective_date between c_start_date and c_end_date
428 and ppa.effective_date between pap.effective_start_date and pap.effective_end_date /* 4729052 */
429 AND (paa.source_action_id IS NULL
430 OR (paa.source_action_id IS NOT NULL AND ppa.run_type_id IS NULL))
431 and paf.effective_end_date = (select max(effective_end_date) /* 4729052 */
432 From per_assignments_f iipaf
433 WHERE iipaf.assignment_id = paf.assignment_id
434 and iipaf.effective_end_date >= c_start_date
435 and iipaf.effective_start_date <= c_end_date)
436
437 order by per_information2;
438
439
440 Begin
441 g_debug :=hr_utility.debug_enabled ;
442 g_package := 'pay_au_tax_report_pkg.' ;
443 l_procedure := g_package||'initialization_code';
444 hr_utility.set_location('Entering '||l_procedure,1);
445
446
447 -- initialization_code to to set the global tables for EIT
448 -- that will be used by each thread in multi-threading.
449
450 g_arc_payroll_action_id := p_payroll_action_id;
451
452 -- Fetch the parameters by user passed into global variable.
453
454 OPEN csr_params(p_payroll_action_id);
455 FETCH csr_params into g_parameters;
456 CLOSE csr_params;
457
458
462 hr_utility.set_location('g_parameters.legal_employer.........= ' || g_parameters.legal_employer,30);
459 IF g_debug THEN
460 hr_utility.set_location('p_payroll_action_id.........= ' || p_payroll_action_id,30);
461 hr_utility.set_location('g_parameters.business_group_id.........= ' || g_parameters.business_group_id,30);
463 hr_utility.set_location('g_parameters.start_date..............= ' || g_parameters.start_date,30);
464 hr_utility.set_location('g_parameters.end_date................= ' || g_parameters.end_date,30);
465 hr_utility.set_location('g_parameters.tax_state.........= ' || g_parameters.tax_state,30);
466 hr_utility.set_location('g_parameters.report_type..........= '||g_parameters.report_type,30);
467 hr_utility.set_location('g_parameters.act_threshold..........= '||g_parameters.act_override_threshold,30);
468 hr_utility.set_location('g_parameters.vic_threshold..........= '||g_parameters.vic_override_threshold,30);
469 hr_utility.set_location('g_parameters.qld_threshold..........= '||g_parameters.qld_override_threshold,30);
470 hr_utility.set_location('g_parameters.nsw_threshold..........= '||g_parameters.nsw_override_threshold,30);
471 hr_utility.set_location('g_parameters.tas_threshold..........= '||g_parameters.tas_override_threshold,30);
472 hr_utility.set_location('g_parameters.wa_threshold..........= '||g_parameters.wa_override_threshold,30);
473 hr_utility.set_location('g_parameters.sa_threshold..........= '||g_parameters.sa_override_threshold,30);
474 hr_utility.set_location('g_parameters.nt_threshold..........= '||g_parameters.nt_override_threshold,30);
475 end if;
476
477 g_business_group_id := g_parameters.business_group_id ;
478
479
480 populate_defined_balance_ids(g_parameters.legal_employer);
481 /* 4713372 */
482
483 g_count := 0;
484 g_le_taxable_income := 0;
485 g_prev_tax_state := 'ZZZ';
486
487
488 for csr_le_rec in csr_get_ass_le_act_id(g_business_group_id, g_parameters.legal_employer , g_parameters.start_date, g_parameters.end_date)
489 LOOP
490
491 open csr_paytax_defined_balance_id('_ASG_LE_RUN', csr_le_rec.per_information2); /* 4718544 */
492 fetch csr_paytax_defined_balance_id into l_defined_balance_id;
493 close csr_paytax_defined_balance_id;
494
495 /* 5139764 */
496
497 open csr_tpaytax_defined_balance_id('_ASG_LE_RUN', csr_le_rec.per_information2); /* 4718544 */
498 fetch csr_tpaytax_defined_balance_id into l_term_defined_balance_id;
499 close csr_tpaytax_defined_balance_id;
500
501 /* 5139764 */
502
503 open csr_etpaytax_defined_balance('_ASG_LE_RUN', csr_le_rec.per_information2); /* 4718544 */
504 fetch csr_etpaytax_defined_balance into l_elig_term_defined_balance_id;
505 close csr_etpaytax_defined_balance;
506
507 hr_utility.set_location('l_term_defined ' || l_term_defined_balance_id,99);
508 hr_utility.set_location('per_information ' || csr_le_rec.per_information2,99);
509
510 l_balance_value := pay_balance_pkg.get_value(l_defined_balance_id,
511 csr_le_rec.assignment_action_id, csr_le_rec.tax_unit_id, null,null,null,null);
512
513 /* 5139764 */
514
515 l_term_balance_value := pay_balance_pkg.get_value(l_term_defined_balance_id,
516 csr_le_rec.assignment_action_id, csr_le_rec.tax_unit_id, null,null,null,null);
517
518 /* 5139764 */
519
520 l_elig_term_balance_value := pay_balance_pkg.get_value(l_elig_term_defined_balance_id,
521 csr_le_rec.assignment_action_id, csr_le_rec.tax_unit_id, null,null,null,null);
522
523 hr_utility.set_location('l_balanace_value ' || l_balance_value,999);
524
525 g_le_taxable_income := nvl(g_le_taxable_income,0) + l_balance_value;
526
527 if g_prev_tax_state <> csr_le_rec.per_information2
528 then
529
530 g_count := g_count + 1;
531 g_prev_tax_state := csr_le_rec.per_information2;
532
533 end if;
534
535 END LOOP;
536 /* 4713372 */
537 if g_debug then
538 hr_utility.set_location('Leaving '||l_procedure,1000);
539 end if;
540
541 end initialization_code;
542
543 --------------------------------------------------------------------+
544 -- Name : assignment_Action_code
545 -- Type : Procedure
546 -- Access: Public
547 -- This procedure further restricts the assignment_id's
548 -- returned by range_code
549 -- This procedure gets the parameters given by user and restricts
550 -- the assignments to be archived.
551 -- it then calls hr_nonrun.insact to create an assignment action id
552 -- it then archives Payroll Run assignment action id details
553 -- in pay_Action_information with context 'AU_ARCHIVE_ASG_DETAILS'
554 -- for each assignment.
555 -- There are 10 different cursors for choosing the assignment ids.
556 -- Depending on the parameters passed,the appropriate cursor is used.
557 --------------------------------------------------------------------+
558
559 procedure assignment_action_code
560 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type
561 ,p_start_person in per_all_people_f.person_id%type
562 ,p_end_person in per_all_people_f.person_id%type
563 ,p_chunk in number
564 ) is
565
566
567
568 cursor csr_assignment_period
569 (p_payroll_action_id pay_payroll_actions.payroll_action_id%type
570 ,p_start_person per_all_people_f.person_id%type
571 ,p_end_person per_all_people_f.person_id%type
575 ,p_archive_start_date date
572 ,p_report_type varchar2
573 ,p_business_group_id hr_all_organization_units.organization_id%type
574 ,p_legal_employer hr_all_organization_units.organization_id%type
576 ,p_archive_end_date date
577 ,p_tax_state varchar2
578 ) is
579 select paa.assignment_action_id,
580 paa.action_sequence,
581 paaf.assignment_id,
582 paa.tax_unit_id,
583 paa.source_action_id master_action_id,
584 paa2.tax_unit_id master_tax_unit_id
585 from per_people_f pap,
586 per_assignments_f paaf,
587 pay_payroll_actions ppa,
588 pay_payroll_actions ppa1,
589 pay_assignment_actions paa,
590 pay_assignment_actions paa2,
591 per_periods_of_service pps
592 where ppa.payroll_action_id = p_payroll_action_id
593 and paa.assignment_id = paaf.assignment_id
594 and paa2.assignment_id = paaf.assignment_id
595 AND paa2.assignment_id = paa.assignment_id
596 and pap.person_id between p_start_person and p_end_person
597 and pap.person_id = paaf.person_id
598 and pap.person_id = pps.person_id
599 and pps.period_of_service_id = paaf.period_of_service_id
600 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
601 and ppa1.payroll_action_id = paa.payroll_action_id
602 and ppa1.payroll_action_id = paa2.payroll_action_id
603 AND paa2.action_status ='C'
604 AND paa.action_status ='C'
605 and (pap.per_information3 = 'N' or pap.per_information3 is null)
606 AND (pap.per_information2 = p_tax_state or p_tax_state is null)
607 AND paa2.assignment_action_id = nvl(paa.source_action_id, paa.assignment_action_id)
608 AND (paa.source_action_id IS NULL)
609 -- OR (paa.source_action_id IS NOT NULL AND ppa.run_type_id IS NULL))
610 and ppa1.business_group_id = ppa.business_group_id
611 and ppa.business_group_id = p_business_group_id
612 and ppa1.action_type in ('R','Q','I','B','V')
613 and ( paa.tax_unit_id = p_legal_employer or p_legal_employer is null)
614 and ppa1.effective_date between p_archive_start_date and p_archive_end_date
615 and paaf.effective_end_date = (select max(effective_end_date)
616 From per_assignments_f iipaf
617 WHERE iipaf.assignment_id = paaf.assignment_id
618 and iipaf.effective_end_date >= p_archive_start_date
619 and iipaf.effective_start_date <= p_archive_end_date)
620 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id, paa.source_action_id, paa2.tax_unit_id;
621
622
623 --------------------------------------------------------------------+
624 -- Cursor : csr_params
625 -- Description : Fetches User Parameters from Legislative_paramters
626 -- column.
627 --------------------------------------------------------------------+
628
629 CURSOR csr_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
630 IS
631 SELECT pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
632 pay_core_utils.get_parameter('LE',legislative_parameters) legal_employer,
633 to_date(pay_core_utils.get_parameter('PERIOD',legislative_parameters),'YYYY/MM/DD') period,
634 to_date(pay_core_utils.get_parameter('SDATE',legislative_parameters),'YYYY/MM/DD') start_date,
635 to_date(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD') end_date,
636 pay_core_utils.get_parameter('TAX_STATE',legislative_parameters) Tax_state,
637 pay_core_utils.get_parameter('REP_TYPE',legislative_parameters)report_type,
638 pay_core_utils.get_parameter('REP_NAME',legislative_parameters)report_name,
639 pay_core_utils.get_parameter('ACTT',legislative_parameters)act_override_threshold,
640 pay_core_utils.get_parameter('VICT',legislative_parameters)vic_override_threshold,
641 pay_core_utils.get_parameter('NSWT',legislative_parameters)nsw_override_threshold,
642 pay_core_utils.get_parameter('QLDT',legislative_parameters)qld_override_threshold,
643 pay_core_utils.get_parameter('WAT',legislative_parameters)wa_override_threshold,
644 pay_core_utils.get_parameter('NTT',legislative_parameters)nt_override_threshold,
645 pay_core_utils.get_parameter('SAT',legislative_parameters)sa_override_threshold,
646 pay_core_utils.get_parameter('TAST',legislative_parameters)tas_override_threshold
647 FROM pay_payroll_actions ppa
648 WHERE ppa.payroll_action_id = c_payroll_action_id;
649
650
651
652 cursor csr_next_action_id is
653 select pay_assignment_actions_s.nextval
654 from dual;
655
656 l_next_assignment_action_id pay_assignment_actions.assignment_action_id%type;
657 l_procedure varchar2(200) ;
658 i number;
659
660 l_action_information_id number;
661 l_object_version_number number;
662
663
664 begin
665 i := 1;
669 hr_utility.set_location('Entering assignment_Action_code',302);
666 g_package := 'pay_au_tax_rep_pkg.' ;
667 l_procedure := g_package||'assignment_action_code';
668 hr_utility.set_location('Entering ' || l_procedure,1);
670
671
672 -- initialization_code to to set the global tables for EIT
673 -- that will be used by each thread in multi-threading.
674 g_arc_payroll_action_id := p_payroll_action_id;
675 hr_utility.set_location('p_payroll_Action' || p_payroll_Action_id, 777);
676
677 -- Fetch the parameters by user passed into global variable.
678
679 OPEN csr_params(p_payroll_action_id);
680 FETCH csr_params into g_parameters;
681 CLOSE csr_params;
682
683
684 IF g_debug THEN
685
686 hr_utility.set_location('p_payroll_action_id.........= ' || p_payroll_action_id,30);
687 hr_utility.set_location('g_parameters.business_group_id.........= ' || g_parameters.business_group_id,30);
688 hr_utility.set_location('g_parameters.legal_employer.........= ' || g_parameters.legal_employer,30);
689 hr_utility.set_location('g_parameters.start_date..............= ' || g_parameters.start_date,30);
690 hr_utility.set_location('g_parameters.end_date................= ' || g_parameters.end_date,30);
691 hr_utility.set_location('g_parameters.tax_state.........= ' || g_parameters.tax_state,30);
692 hr_utility.set_location('g_parameters.report_type..........= '||g_parameters.report_type,30);
693 hr_utility.set_location('g_parameters.act_threshold..........= '||g_parameters.act_override_threshold,30);
694 hr_utility.set_location('g_parameters.vic_threshold..........= '||g_parameters.vic_override_threshold,30);
695 hr_utility.set_location('g_parameters.qld_threshold..........= '||g_parameters.qld_override_threshold,30);
696 hr_utility.set_location('g_parameters.nsw_threshold..........= '||g_parameters.nsw_override_threshold,30);
697 hr_utility.set_location('g_parameters.tas_threshold..........= '||g_parameters.tas_override_threshold,30);
698 hr_utility.set_location('g_parameters.wa_threshold..........= '||g_parameters.wa_override_threshold,30);
699 hr_utility.set_location('g_parameters.sa_threshold..........= '||g_parameters.sa_override_threshold,30);
700 hr_utility.set_location('g_parameters.nt_threshold..........= '||g_parameters.nt_override_threshold,30);
701
702 end if;
703
704 g_business_group_id := g_parameters.business_group_id ;
705
706
707
708
709
710 FOR csr_rec in csr_assignment_period(p_payroll_action_id,
711 p_start_person,
712 p_end_person,
713 g_parameters.report_type,
714 g_parameters.business_group_id,
715 g_parameters.legal_employer,
716 g_parameters.start_date,
717 g_parameters.end_date,
718 g_parameters.tax_state)
719 LOOP
720 hr_utility.set_location('in loop' , 555);
721 open csr_next_action_id;
722 fetch csr_next_action_id into l_next_assignment_action_id;
723 close csr_next_action_id;
724
725 -- Create the archive assignment actions
726 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
727
728 insert into pay_action_information(
729 action_information_id,
730 action_context_id,
731 action_context_type,
732 effective_date,
733 source_id,
734 tax_unit_id,
735 action_information_category,
736 action_information1,
737 action_information2,
738 action_information3,
739 assignment_id
740 )
741 values(
742 pay_action_information_s.nextval,
743 l_next_assignment_action_id,
744 'AAP',
745 null,
746 null,
747 csr_rec.tax_unit_id,
748 'AU_ARCHIVE_ASG_DETAILS',
749 csr_rec.assignment_action_id,
750 p_payroll_action_id,
751 csr_rec.action_sequence,
752 csr_rec.assignment_id
753 );
754
755
756 END LOOP;
757 if g_debug then
758 hr_utility.set_location('Leaving............Loop7. Asg + Period...' || l_procedure,1000);
759 end if;
760
761 exception
762 when others then
763 hr_utility.set_location('Error in '||l_procedure,999999);
764 raise;
765 end assignment_action_code;
766
767 procedure archive_code
768 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
769 ,p_effective_date in pay_payroll_actions.effective_date%type
770 ) is
771
772 /* 5893671*/
776 from per_people_f pap
773 cursor c_get_employee_full_name (c_person_id number, c_end_date date, c_start_date date)
774 is
775 select full_name
777 where person_id= c_person_id
778 and pap.effective_end_date =
779 (select max(effective_end_date)
780 from per_people_f pap1
781 where pap1.person_id = pap.person_id
782 and pap1.effective_end_date >= c_start_date
783 and pap1.effective_start_date <= c_end_date
784 );
785
786 cursor c_employee_details(c_business_group_id hr_all_organization_units.organization_id%TYPE,
787 c_assignment_id number,c_end_date date, c_start_date date,
788 c_assignment_action_id number,
789 c_payroll_action_id number)
790 is /* 5893671 - commented out full name*/
791 select /* pap.full_name, */
792 paaf.assignment_number employee_number,
793 ppa1.effective_date,
794 paa2.assignment_action_id,
795 paaf.assignment_id,
796 paaf.organization_id,
797 hou.NAME organization_name,
798 hsc.segment1 tax_unit_id,
799 hou1.NAME Legal_Employer ,
800 pap.person_id,
801 pap.per_information2 state_code ,
802 hoi.org_information1 business_group_id,
803 /* 4716254 ,4718544 */
804 decode(pap.per_information2 , 'VIC', hoi.org_information4,
805 'WA' , hoi.org_information11,
806 'QLD' , hoi.org_information7,
807 'SA' , hoi.org_information8,
808 'NSW' , hoi.org_information5,
809 'ACT' , hoi.org_information10,
810 'NT' , hoi.org_information9,
811 'TAS' , hoi.org_information6) dge_state,
812 /* 4716254 ,4718544 */
813
814 hoi.org_information2 dge_legal_employer,
815 hoi.org_information3 dge_group_name,
816 hl.meaning state_desc
817 from per_people_f pap,
818 per_assignments_f paaf,
819 pay_payroll_actions ppa,
820 pay_payroll_actions ppa1,
821 pay_assignment_actions paa,
822 pay_assignment_actions paa2,
823 hr_soft_coding_keyflex hsc,
824 hr_organization_units hou,
825 hr_organization_units hou1,
826 hr_organization_information hoi,
827 hr_lookups hl,
828 per_periods_of_service pps
829 where ppa.payroll_action_id = c_payroll_action_id
830 and paa.assignment_Action_id = c_assignment_Action_id /*5235423 */
831 and paa.assignment_id = paaf.assignment_id
832 and pap.person_id = paaf.person_id
833 and paa.assignment_id = c_assignment_id
834 AND pap.per_information2 = hl.lookup_code
835 AND hl.lookup_type = 'AU_STATE'
836 AND hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
837 AND hou.organization_id = paaf.organization_id
838 and hoi.org_information_context(+) = 'AU_PAYROLL_TAX_DGE'
839 AND hou1.organization_id = hoi.organization_id(+)
840 AND hou1.organization_id = hsc.segment1
841 AND paa2.assignment_id = paa.assignment_id
842 and pap.person_id = pps.person_id
843 and pps.period_of_service_id = paaf.period_of_service_id
844 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
845 and ppa1.payroll_action_id = paa.payroll_action_id
846 and ppa1.payroll_action_id = paa2.payroll_action_id
847 AND paa2.action_status ='C'
848 AND paa.action_status ='C'
849 and (pap.per_information3 = 'N' or pap.per_information3 is null)
850 AND paa2.assignment_action_id = nvl(paa.source_action_id, paa.assignment_action_id)
851 AND paa.source_action_id IS NULL /* 5235423 */
852 and ppa1.business_group_id = ppa.business_group_id
853 and ppa.business_group_id = c_business_group_id
854 and ppa1.action_type in ('R','Q','I','B','V')
855 and ppa1.effective_date between c_start_date and c_end_date
856 and paaf.effective_end_date = (select max(effective_end_date)
857 From per_assignments_f iipaf
858 WHERE iipaf.assignment_id = paaf.assignment_id
859 and iipaf.effective_end_date >= c_start_date
860 and iipaf.effective_start_date <= c_end_date)
861 /* and c_end_date between pap.effective_start_date and pap.effective_end_date */
862 order by paaf.assignment_id, paa2.assignment_action_id, hsc.segment1;
863
864
865
866 cursor csr_get_data (c_arc_ass_act_id number)
867 is
868 select pai.action_information1, pai.action_information2, pai.tax_unit_id, pai.assignment_id,pai.action_information3
869 from pay_action_information pai
870 where action_information_category = 'AU_ARCHIVE_ASG_DETAILS'
871 and pai.action_context_id = c_arc_ass_act_id;
872
873
874 CURSOR csr_Paytax_defined_balance_id
875 (c_database_item_suffix pay_balance_dimensions.database_item_suffix%type,
876 c_state_code varchar2)
877 IS
878 SELECT pdb.defined_balance_id defined_balance_id
879 FROM pay_balance_types pbt,
880 pay_balance_dimensions pbd,
884 AND pbt.balance_type_id = pdb.balance_type_id
881 pay_defined_balances pdb
882 WHERE pbt.balance_name = 'Payroll_Tax_' || c_state_code
883 AND pbd.database_item_suffix = c_database_item_suffix
885 AND pbd.balance_dimension_id = pdb.balance_dimension_id
886 AND pbt.legislation_code = 'AU';
887
888
889 /* 5139764 */
890
891 CURSOR csr_TPaytax_defined_balance_id
892 (c_database_item_suffix pay_balance_dimensions.database_item_suffix%type,
893 c_state_code varchar2)
894 IS
895 SELECT pdb.defined_balance_id defined_balance_id
896 FROM pay_balance_types pbt,
897 pay_balance_dimensions pbd,
898 pay_defined_balances pdb
899 WHERE pbt.balance_name = 'Payroll_Tax_' || c_state_code || '_Termination_Payments'
900 AND pbd.database_item_suffix = c_database_item_suffix
901 AND pbt.balance_type_id = pdb.balance_type_id
902 AND pbd.balance_dimension_id = pdb.balance_dimension_id
903 AND pbt.legislation_code = 'AU';
904
905
906 /* 5139764 */
907
908 CURSOR csr_ETPaytax_defined_balance
909 (c_database_item_suffix pay_balance_dimensions.database_item_suffix%type,
910 c_state_code varchar2)
911 IS
912 SELECT pdb.defined_balance_id defined_balance_id
913 FROM pay_balance_types pbt,
914 pay_balance_dimensions pbd,
915 pay_defined_balances pdb
916 WHERE pbt.balance_name = 'Payroll_Tax_' || c_state_code || '_Eligible_Termination_Payments'
917 AND pbd.database_item_suffix = c_database_item_suffix
918 AND pbt.balance_type_id = pdb.balance_type_id
919 AND pbd.balance_dimension_id = pdb.balance_dimension_id
920 AND pbt.legislation_code = 'AU';
921
922
923 l_procedure varchar2(200);
924 l_action_information_id number;
925 l_object_version_number number;
926
927 l_SALARIES_WAGES number :=0;
928 l_COMMISSION number:=0;
929 l_BONUS_ALLOWANCES number:=0;
930 l_DIRECTOR_FEES number :=0;
931 l_TERMINATION_PAYMENTS number :=0;
932 l_ELIGIBLE_TERM_PAYMENTS number :=0;
933 l_FRINGE_BENEFITS number :=0;
934 l_SUPERANNUATION number :=0;
935 l_CONTRACTOR_PAYMENTS number :=0;
936 l_OTHER_TAXABLE_INCOME number :=0;
937 L_TAXABLE_INCOME number :=0;
938 L_LE_TAXABLE_INCOME number :=0;
939 L_NSW_TAXABLE_INCOME number :=0;
940 L_QLD_TAXABLE_INCOME number :=0;
941 L_ACT_TAXABLE_INCOME number :=0;
942 L_NT_TAXABLE_INCOME number :=0;
943 L_SA_TAXABLE_INCOME number :=0;
944 L_WA_TAXABLE_INCOME number :=0;
945 L_TAS_TAXABLE_INCOME number :=0;
946 l_count number :=0;
947 l_run_dimension_name varchar2(15);
948 l_ass_act_id number;
949 l_payroll_action_id number;
950 l_tax_unit_id number;
951 l_assignment_id number;
952 l_full_name varchar2(100);
953
954 l_action_sequence number;
955 l_max_asg_action_id number;
956 l_max_action_sequence number;
957 l_defined_balance_id number;
958
959 begin
960
961 g_debug :=hr_utility.debug_enabled ;
962 g_package := 'pay_au_tax_rep_pkg.' ;
963 l_procedure := g_package||'archive_code';
964 l_run_dimension_name := '_ASG_LE_RUN';
965
966
967
968 OPEN csr_get_data(p_assignment_action_id);
969 FETCH csr_get_data into l_ass_act_id, l_payroll_Action_id,l_tax_unit_id, l_assignment_id,l_action_sequence;
970 CLOSE csr_get_data;
971
972
973 FOR csr_rec in c_employee_details(g_business_group_id,l_assignment_id,g_parameters.end_date,g_parameters.start_date,l_ass_act_id,l_payroll_action_id)
974 LOOP
975 /* 5893671 */
976
977
978 OPEN c_get_employee_full_name(csr_rec.person_id, g_parameters.end_date, g_parameters.start_date);
979 FETCH c_get_employee_full_name into l_full_name;
980 CLOSE c_get_employee_full_name;
981
982 g_prev_assignment_id := csr_rec.assignment_id;
983 g_prev_tax_state := csr_rec.state_code;
984
985
986 /* 5139764 */
987
988 FOR csr_pt IN csr_Paytax_defined_balance_id(l_run_dimension_name, csr_rec.state_code)
989 LOOP
990 g_balance_value_tab(11).defined_balance_id := csr_pt.defined_balance_id;
991 END LOOP;
992
993 /* 5139764 */
994
995 FOR csr_ptt IN csr_TPaytax_defined_balance_id(l_run_dimension_name, csr_rec.state_code)
996 LOOP
997 g_balance_value_tab(5).defined_balance_id := csr_ptt.defined_balance_id;
998 END LOOP;
999
1000
1001 FOR csr_eptt IN csr_ETPaytax_defined_balance(l_run_dimension_name, csr_rec.state_code)
1002 LOOP
1003 g_balance_value_tab(6).defined_balance_id := csr_eptt.defined_balance_id;
1004 END LOOP;
1005
1006 -- Balances Coding for BBR
1007
1008 -- Get The Action Sequence for the Assignment_Action_Id.
1009
1010 GET_BALANCES(
1011 P_ASSIGNMENT_ACTION_ID => csr_rec.assignment_action_id,
1012 P_REGISTERED_EMPLOYER => l_tax_unit_id,
1013 P_TAX_STATE => csr_rec.state_code,
1017 P_DIRECTOR_FEES => l_DIRECTOR_FEES,
1014 P_SALARIES_WAGES => l_SALARIES_WAGES,
1015 P_COMMISSION => l_COMMISSION,
1016 P_BONUS_ALLOWANCES => l_BONUS_ALLOWANCES ,
1018 P_TERMINATION_PAYMENTS => l_TERMINATION_PAYMENTS,
1019 P_ELIGIBLE_TERM_PAYMENTS => l_ELIGIBLE_TERM_PAYMENTS,
1020 P_FRINGE_BENEFITS => l_FRINGE_BENEFITS,
1021 P_SUPERANNUATION => l_SUPERANNUATION,
1022 P_CONTRACTOR_PAYMENTS => l_CONTRACTOR_PAYMENTS,
1023 P_OTHER_TAXABLE_INCOME => l_OTHER_TAXABLE_INCOME,
1024 P_TAXABLE_INCOME => l_TAXABLE_INCOME);
1025
1026 hr_utility.set_location('in BBR loop', 300);
1027 insert into pay_action_information(
1028 action_information_id,
1029 action_context_id,
1030 action_context_type,
1031 effective_date,
1032 source_id,
1033 tax_unit_id,
1034 action_information_category,
1035 assignment_id,
1036 action_information1,
1037 action_information2,
1038 action_information3,
1039 action_information4,
1040 action_information5,
1041 action_information6,
1042 action_information7,
1043 action_information8,
1044 action_information9)
1045 values(
1046 pay_action_information_s.nextval,
1047 g_arc_payroll_action_id,
1048 'PA',
1049 p_effective_date,
1050 null,
1051 l_tax_unit_id,
1052 'AU_PAYROLL_TAX_EMPLOYEE_DETAILS',
1053 l_assignment_id,
1054 csr_rec.employee_number,
1055 csr_rec.person_id,
1056 l_full_name,
1057 csr_rec.state_desc,
1058 csr_rec.legal_employer,
1059 csr_rec.state_code,
1060 csr_rec.dge_state,
1061 csr_rec.dge_legal_employer,
1062 csr_rec.dge_group_name);
1063
1064
1065
1066
1067 --
1068 -- Insert the balance data into pay_action_information table
1069 -- This Direct Insert statement is for Performance Reasons.
1070 --
1071 insert into pay_action_information (
1072 action_information_id,
1073 action_context_id,
1074 action_context_type,
1075 effective_date,
1076 source_id,
1077 tax_unit_id,
1078 assignment_id,
1079 action_information_category,
1080 action_information1,
1081 action_information2,
1082 action_information3,
1083 action_information4,
1084 action_information5,
1085 action_information6,
1086 action_information7,
1087 action_information8,
1088 action_information9,
1089 action_information10,
1090 action_information11,
1091 action_information12,
1092 action_information13,
1093 action_information14,
1094 action_information15) /* 4731692 */
1095 values (
1096 pay_action_information_s.nextval,
1097 p_assignment_action_id,
1098 'AAP',
1099 p_effective_date,
1100 null,
1101 l_tax_unit_id,
1102 l_assignment_id,
1103 'AU_PAYROLL_TAX_BALANCE_DETAILS',
1104 l_salaries_wages,
1105 l_commission,
1106 l_bonus_allowances,
1107 l_director_fees,
1108 l_termination_payments,
1109 l_eligible_term_payments,
1110 l_Fringe_Benefits,
1111 l_Superannuation,
1112 l_Contractor_payments,
1113 l_Other_taxable_Income,
1114 l_Taxable_Income,
1115 l_max_action_sequence,
1116 g_le_taxable_income, /* 4713372 */
1117 g_count,
1118 csr_rec.state_code); /* 4731692 */
1119
1123
1120 END LOOP; /* End of assignments r employee */
1121
1122 end archive_code;
1124 procedure spawn_archive_reports
1125 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type)
1126 is
1127 l_count number;
1128 ps_request_id NUMBER;
1129 l_print_style VARCHAR2(2);
1130 l_report_name VARCHAR2(30);
1131 l_short_report_name VARCHAR2(30);
1132 l_print_together VARCHAR2(80);
1133 l_print_return BOOLEAN;
1134 l_procedure varchar2(50);
1135 request_error varchar2(2000);
1136 e_submit_error exception;
1137 err_num number;
1138 err_msg varchar2(2000);
1139 l_xml_options BOOLEAN; /* Bug 6839263 */
1140 --------------------------------------------------------------------+
1141 -- Cursor : csr_params
1142 -- Description : Fetches User Parameters from Legislative_paramters
1143 -- column.
1144 --------------------------------------------------------------------+
1145
1146 CURSOR csr_report_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
1147 IS
1148 SELECT pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
1149 pay_core_utils.get_parameter('LE',legislative_parameters) legal_employer,
1150 to_date(pay_core_utils.get_parameter('PERIOD',legislative_parameters),'YYYY/MM/DD') period,
1151 to_date(pay_core_utils.get_parameter('SDATE',legislative_parameters),'YYYY/MM/DD') start_date,
1152 to_date(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD') end_date,
1153 pay_core_utils.get_parameter('TAX_STATE',legislative_parameters) Tax_state,
1154 pay_core_utils.get_parameter('REP_TYPE',legislative_parameters)report_type,
1155 pay_core_utils.get_parameter('REP_NAME',legislative_parameters)report_name,
1156 pay_core_utils.get_parameter('ACTT',legislative_parameters)act_override_threshold,
1157 pay_core_utils.get_parameter('VICT',legislative_parameters)vic_override_threshold,
1158 pay_core_utils.get_parameter('NSWT',legislative_parameters)nsw_override_threshold,
1159 pay_core_utils.get_parameter('QLDT',legislative_parameters)qld_override_threshold,
1160 pay_core_utils.get_parameter('WAT',legislative_parameters)wa_override_threshold,
1161 pay_core_utils.get_parameter('NTT',legislative_parameters)nt_override_threshold,
1162 pay_core_utils.get_parameter('SAT',legislative_parameters)sa_override_threshold,
1163 pay_core_utils.get_parameter('TAST',legislative_parameters)tas_override_threshold,
1164 pay_core_utils.get_parameter('OUTPUT_TYPE',legislative_parameters) p_output_type
1165 FROM pay_payroll_actions ppa
1166 WHERE ppa.payroll_action_id = c_payroll_action_id;
1167
1168
1169
1170 cursor csr_get_report_name(c_payroll_Action_id pay_payroll_actions.payroll_action_id%TYPE) is
1171 select pay_core_utils.get_parameter('REP_NAME',legislative_parameters)
1172 from pay_payroll_actions ppa
1173 where ppa.payroll_Action_id = c_payroll_Action_id;
1174
1175 cursor csr_get_print_options(p_payroll_action_id NUMBER) IS
1176 SELECT printer,
1177 print_style,
1178 decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output
1179 ,number_of_copies
1180 FROM pay_payroll_actions pact,
1181 fnd_concurrent_requests fcr
1182 WHERE fcr.request_id = pact.request_id
1183 AND pact.payroll_action_id = p_payroll_action_id;
1184
1185
1186 rec_print_options csr_get_print_options%ROWTYPE;
1187
1188 l_parameters csr_report_params%ROWTYPE; /* Bug 6839263 */
1189
1190 Begin
1191 l_count :=0;
1192 ps_request_id :=-1;
1193 g_debug :=hr_utility.debug_enabled ;
1194
1195
1196 if g_debug then
1197 g_package := 'pay_au_tax_rep_pkg.' ;
1198 l_procedure := g_package||' spawn_archive_reports';
1199 hr_utility.set_location('Entering '||l_procedure,999);
1200 end if;
1201
1202 -- Set User Parameters for Report.
1203 open csr_get_report_name(p_payroll_action_id);
1204 fetch csr_get_report_name into l_report_name;
1205 close csr_get_report_name;
1206
1207 open csr_report_params(p_payroll_action_id);
1208 fetch csr_report_params into l_parameters;
1209 close csr_report_params;
1210
1211 /* Start 6839263 */
1212 IF l_parameters.p_output_type = 'XML_PDF'
1213 THEN
1214 l_short_report_name := 'PYAUPYL_XML';
1215
1216 l_xml_options := fnd_request.add_layout
1217 (template_appl_name => 'PAY',
1218 template_code => 'PYAUPYL_XML',
1219 template_language => 'en',
1220 template_territory => 'US',
1221 output_format => 'PDF');
1222
1223 ELSE
1224 l_short_report_name := 'PYAUPYL';
1225 END IF;
1226 /* End 6839263 */
1227
1228 if g_debug then
1229 hr_utility.set_location('p_payroll_action_id.........= ' || p_payroll_action_id,30);
1230 hr_utility.set_location('g_parameters.business_group_id.........= ' || l_parameters.business_group_id,30);
1231 hr_utility.set_location('g_parameters.legal_employer.........= ' || l_parameters.legal_employer,30);
1232 hr_utility.set_location('g_parameters.start_date..............= ' || l_parameters.start_date,30);
1233 hr_utility.set_location('g_parameters.end_date................= ' || l_parameters.end_date,30);
1234 hr_utility.set_location('g_parameters.tax_state.........= ' || l_parameters.tax_state,30);
1235 hr_utility.set_location('g_parameters.report_type..........= '||l_parameters.report_type,30);
1236 hr_utility.set_location('g_parameters.act_threshold..........= '||l_parameters.act_override_threshold,30);
1237 hr_utility.set_location('g_parameters.vic_threshold..........= '||l_parameters.vic_override_threshold,30);
1238 hr_utility.set_location('g_parameters.qld_threshold..........= '||l_parameters.qld_override_threshold,30);
1239 hr_utility.set_location('g_parameters.nsw_threshold..........= '||l_parameters.nsw_override_threshold,30);
1240 hr_utility.set_location('g_parameters.tas_threshold..........= '||l_parameters.tas_override_threshold,30);
1241 hr_utility.set_location('g_parameters.wa_threshold..........= '||l_parameters.wa_override_threshold,30);
1242 hr_utility.set_location('g_parameters.sa_threshold..........= '||l_parameters.sa_override_threshold,30);
1243 hr_utility.set_location('g_parameters.nt_threshold..........= '||l_parameters.nt_override_threshold,30);
1244 hr_utility.set_location('Output Type = '||l_parameters.p_output_type,30);
1245 end if;
1246
1247
1248 OPEN csr_get_print_options(p_payroll_action_id);
1249 FETCH csr_get_print_options INTO rec_print_options;
1250 CLOSE csr_get_print_options;
1251 --
1252 l_print_together := nvl(fnd_profile.value('CONC_PRINT_TOGETHER'), 'N');
1253 --
1254 -- Set printer options
1255 l_print_return := fnd_request.set_print_options
1256 (printer => rec_print_options.printer,
1257 style => rec_print_options.print_style,
1258 copies => rec_print_options.number_of_copies,/* Bug 4116833*/
1259 save_output => hr_general.char_to_bool(rec_print_options.save_output),
1260 print_together => l_print_together);
1261 -- Submit report
1262 if g_debug then
1263 hr_utility.set_location('payroll_action id '|| p_payroll_action_id,25);
1264 end if;
1265
1266 ps_request_id := fnd_request.submit_request
1267 ('PAY',
1268 l_short_report_name,
1269 null,
1270 null,
1271 false,
1272 'P_PAYROLL_ACTION_ID=' || to_char(p_payroll_action_id),
1273 'P_BUSINESS_GROUP_ID='||to_char(l_parameters.business_group_id),
1274 'P_LEGAL_EMPLOYER='||to_char(l_parameters.legal_employer),
1275 'P_PERIOD='||to_char(l_parameters.period,'YYYY/MM/DD'),
1276 'P_START_DATE='||to_char(l_parameters.start_date,'YYYY/MM/DD'),
1277 'P_END_DATE='||to_char(l_parameters.end_date,'YYYY/MM/DD'),
1278 'P_TAX_STATE=' || l_parameters.tax_state,
1279 'P_ACT=' || l_parameters.act_override_threshold,
1280 'P_QLD=' || l_parameters.qld_override_threshold,
1281 'P_SA=' || l_parameters.sa_override_threshold,
1282 'P_TAS=' || l_parameters.tas_override_threshold,
1283 'P_VIC=' || l_parameters.vic_override_threshold,
1284 'P_WA=' || l_parameters.wa_override_threshold,
1285 'P_NSW=' || l_parameters.nsw_override_threshold,
1286 'P_NT=' || l_parameters.nt_override_threshold,
1287 'P_REPORT_TYPE='||l_parameters.report_type,
1288 'P_REPORT_NAME=' || l_report_name,
1289 'BLANKPAGES=NO',NULL,NULL,
1290 NULL, NULL, NULL, NULL, NULL, NULL,
1291 NULL, NULL, NULL, NULL, NULL, NULL,
1292 NULL, NULL, NULL, NULL, NULL, NULL,
1293 NULL, NULL, NULL, NULL, NULL, NULL,
1294 NULL, NULL, NULL, NULL, NULL, NULL,
1295 NULL, NULL, NULL, NULL, NULL, NULL,
1296 NULL, NULL, NULL, NULL, NULL, NULL,
1297 NULL, NULL, NULL, NULL, NULL, NULL,
1298 NULL, NULL, NULL, NULL, NULL, NULL,
1299 NULL, NULL, NULL, NULL, NULL, NULL,
1300 NULL, NULL, NULL, NULL, NULL, NULL,
1301 NULL, NULL, NULL, NULL, NULL, NULL,
1302 NULL, NULL, NULL, NULL
1303 );
1304
1305
1306 request_error := fnd_message.get;
1307
1308 if g_debug then
1309 hr_utility.set_location('ps_request ' || ps_request_id, 35);
1310
1311 hr_utility.set_location('After calling report',24);
1312
1313 end if;
1314 exception
1315 when others then
1316 err_num := SQLCODE;
1317 err_msg := substr(sqlerrm,1,100);
1318
1319 hr_utility.set_location('erro_msg ' || err_msg, 200);
1320
1321 end spawn_archive_reports;
1322
1323
1324
1325
1326 end pay_au_paytax_pkg;