[Home] [Help]
PACKAGE BODY: APPS.PAY_AU_PAYTAX_PKG
Source
1 PACKAGE BODY PAY_AU_PAYTAX_PKG as
2 /* $Header: pyaupyt.pkb 120.24.12020000.2 2013/02/18 02:26:34 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 ** 02-SEP-2010 pmatamsr 10069164 Removed code to insert data in FND_SESSIONS table added as part of bug 7280733 and
38 ** 18-Feb-2013 skshin 14134526 Added breakdown balances for each state and report them if used
39 */
40
41 g_debug boolean;
42 g_package varchar2(26);
43
44
45 g_arc_payroll_action_id pay_payroll_actions.payroll_action_id%type;
46 g_business_group_id hr_all_organization_units.organization_id%type;
47 g_prev_assignment_id number;
48 g_le_taxable_income number;
49 g_count number;
50 g_prev_tax_state varchar2(3);
51 g_def_bal_populted varchar2(1);
52
53
54 /* Procedure to pass all the balance results back in one call from report */
55
56 procedure get_balances
57 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
58 p_registered_employer in NUMBER,
59 p_tax_state in varchar2,
60 p_salaries_wages out NOCOPY number,
61 p_commission out NOCOPY number,
62 p_bonus_allowances out NOCOPY number,
63 p_director_fees out NOCOPY number,
64 p_termination_payments out NOCOPY number,
65 p_eligible_term_payments out NOCOPY number,
66 p_Fringe_Benefits out NOCOPY number,
67 p_Superannuation out NOCOPY number,
68 p_Contractor_Payments out NOCOPY number,
69 p_Other_Taxable_Income out NOCOPY number,
70 p_taxable_income out NOCOPY number,
71 p_salaries_wages_2 out NOCOPY number,
72 p_commission_2 out NOCOPY number,
73 p_bonus_allowances_2 out NOCOPY number,
74 p_director_fees_2 out NOCOPY number,
75 p_Fringe_Benefits_2 out NOCOPY number,
76 p_Superannuation_2 out NOCOPY number,
77 p_Contractor_Payments_2 out NOCOPY number,
78 p_Other_Taxable_Income_2 out NOCOPY number )
79 is
80
81 begin
82
83 IF g_debug THEN
84 hr_utility.trace('Entering:' || g_package || 'get_balances');
85 hr_utility.trace('Assignment action id value ===>' || p_assignment_action_id);
86 hr_utility.trace('p_registered_employer ===>' || p_registered_employer);
87 END IF;
88
89 /* Call to this function below implements Batch Balance Retrieval for better performance */
90
91 g_context_table(1).tax_unit_id := p_registered_employer;
92 pay_balance_pkg.get_value(p_assignment_action_id => p_assignment_action_id,
93 p_defined_balance_lst=>g_balance_value_tab,
94 p_context_lst =>g_context_table,
95 p_output_table=>g_result_table);
96
97
98 IF g_debug THEN
99 hr_utility.trace('Balance values for RUN dimension');
100 hr_utility.trace('-------------------------------------');
101 hr_utility.trace('Salaries_Wages ===>' || g_result_table(1).balance_value);
102 hr_utility.trace('Commission ==>' || g_result_table(2).balance_value);
103 hr_utility.trace('Bonus_Allowances ===>' || g_result_table(3).balance_value);
104 hr_utility.trace('Director_Fees ===>' || g_result_table(4).balance_value);
105 hr_utility.trace('Termination_Payments===>' || g_result_table(5).balance_value);
106 hr_utility.trace('Elgiible_Termination_Payments ===>' || g_result_table(6).balance_value);
107 hr_utility.trace('Fringe_Benefits ===>' || g_result_table(7).balance_value);
108 hr_utility.trace('Superannuation ===>' || g_result_table(8).balance_value);
109 hr_utility.trace('Contractor_Payments ===>' || g_result_table(9).balance_value);
110 hr_utility.trace('Other_Taxable_Income ===>' || g_result_table(10).balance_value);
111 hr_utility.trace('Payroll_Taxable_income ===>' || g_result_table(11).balance_value);
112 END IF;
113
114
115
116
117 p_Salaries_Wages := nvl(g_result_table(1).balance_value,0);
118 p_commission := nvl(g_result_table(2).balance_value,0);
119 p_bonus_allowances := nvl(g_result_table(3).balance_value,0);
120 p_director_fees := nvl(g_result_table(4).balance_value,0);
121 p_termination_payments := nvl(g_result_table(5).balance_value,0);
122 p_eligible_term_payments := nvl(g_result_table(6).balance_value,0);
123 p_fringe_benefits := nvl(g_result_table(7).balance_value,0);
124 p_Superannuation := nvl(g_result_table(8).balance_value,0);
125 p_Contractor_payments := nvl(g_result_table(9).balance_value,0);
126 p_other_taxable_income := nvl(g_result_table(10).balance_value,0);
127 p_taxable_income := nvl(g_result_table(11).balance_value,0);
128
129 /* 14134526 - Fetching balances for each state */
130 if p_tax_state = 'VIC' THEN
131 pay_balance_pkg.get_value(p_assignment_action_id => p_assignment_action_id,
132 p_defined_balance_lst=>g_balance_value_tab_vic,
133 p_context_lst =>g_context_table,
134 p_output_table=>g_result_table_state);
135
136 elsif p_tax_state = 'NSW' THEN
137 pay_balance_pkg.get_value(p_assignment_action_id => p_assignment_action_id,
138 p_defined_balance_lst=>g_balance_value_tab_nsw,
139 p_context_lst =>g_context_table,
140 p_output_table=>g_result_table_state);
141
142 elsif p_tax_state = 'ACT' THEN
143 pay_balance_pkg.get_value(p_assignment_action_id => p_assignment_action_id,
144 p_defined_balance_lst=>g_balance_value_tab_act,
145 p_context_lst =>g_context_table,
146 p_output_table=>g_result_table_state);
147
148 elsif p_tax_state = 'SA' THEN
149 pay_balance_pkg.get_value(p_assignment_action_id => p_assignment_action_id,
150 p_defined_balance_lst=>g_balance_value_tab_sa,
151 p_context_lst =>g_context_table,
152 p_output_table=>g_result_table_state);
153
154 elsif p_tax_state = 'QLD' THEN
155 pay_balance_pkg.get_value(p_assignment_action_id => p_assignment_action_id,
156 p_defined_balance_lst=>g_balance_value_tab_qld,
157 p_context_lst =>g_context_table,
158 p_output_table=>g_result_table_state);
159
160 elsif p_tax_state = 'TAS' THEN
161 pay_balance_pkg.get_value(p_assignment_action_id => p_assignment_action_id,
162 p_defined_balance_lst=>g_balance_value_tab_tas,
163 p_context_lst =>g_context_table,
164 p_output_table=>g_result_table_state);
165
166 elsif p_tax_state = 'WA' THEN
167 pay_balance_pkg.get_value(p_assignment_action_id => p_assignment_action_id,
168 p_defined_balance_lst=>g_balance_value_tab_wa,
169 p_context_lst =>g_context_table,
170 p_output_table=>g_result_table_state);
171
172 elsif p_tax_state = 'NT' THEN
173 pay_balance_pkg.get_value(p_assignment_action_id => p_assignment_action_id,
174 p_defined_balance_lst=>g_balance_value_tab_nt,
175 p_context_lst =>g_context_table,
176 p_output_table=>g_result_table_state);
177 end if;
178
179 IF g_debug THEN
180 hr_utility.trace('Balance values for RUN dimension for '||p_tax_state);
181 hr_utility.trace('-------------------------------------');
182 hr_utility.trace('Salaries_Wages ===>' || g_result_table_state(1).balance_value);
183 hr_utility.trace('Commission ==>' || g_result_table_state(2).balance_value);
184 hr_utility.trace('Bonus_Allowances ===>' || g_result_table_state(3).balance_value);
185 hr_utility.trace('Director_Fees ===>' || g_result_table_state(4).balance_value);
186 hr_utility.trace('Fringe_Benefits ===>' || g_result_table_state(5).balance_value);
187 hr_utility.trace('Superannuation ===>' || g_result_table_state(6).balance_value);
188 hr_utility.trace('Contractor_Payments ===>' || g_result_table_state(7).balance_value);
189 hr_utility.trace('Other_Taxable_Income ===>' || g_result_table_state(8).balance_value);
190 END IF;
191
192
193
194 p_Salaries_Wages_2 := nvl(g_result_table_state(1).balance_value,0);
195 p_commission_2 := nvl(g_result_table_state(2).balance_value,0);
196 p_bonus_allowances_2 := nvl(g_result_table_state(3).balance_value,0);
197 p_director_fees_2 := nvl(g_result_table_state(4).balance_value,0);
198 p_fringe_benefits_2 := nvl(g_result_table_state(5).balance_value,0);
199 p_Superannuation_2 := nvl(g_result_table_state(6).balance_value,0);
200 p_Contractor_payments_2 := nvl(g_result_table_state(7).balance_value,0);
201 p_other_taxable_income_2 := nvl(g_result_table_state(8).balance_value,0);
202
203 /* end 14134526 */
204
205 end get_balances;
206
207
208 FUNCTION GET_TAX(p_no_of_states number,
209 p_dge_state varchar2,
210 p_dge_group_name varchar2,
211 p_state_code varchar2,
212 p_taxable_income NUMBER,
213 p_le_taxable_income NUMBER,
214 p_message out NOCOPY varchar2,
215 p_ot_message out NOCOPY varchar2,
216 p_start_date date,
217 p_end_date date,
218 p_override_threshold NUMBER ) RETURN NUMBER IS
219
220 l_tax number;
221 l_formula_id NUMBER;
222 l_inputs ff_exec.inputs_t;
223 l_outputs ff_exec.outputs_t;
224 l_session_flag varchar2(1);
225
226 cursor c_formula is
227 SELECT formula_id
228 FROM ff_formulas_f
229 WHERE formula_name = 'AU_PAYROLL_TAX'
230 AND p_start_date between effective_start_date and effective_end_date
231 ;
232
233 BEGIN
234 --hr_utility.trace_on(null,'SKSHIN');
235
236 l_inputs(1).name := 'STATE_CODE';
237 l_inputs(1).value := p_state_code;
238 l_inputs(2).name := 'TAXABLE_INCOME';
239 l_inputs(2).value := p_taxable_income;
240 l_inputs(3).name := 'OVERRIDE_THRESHOLD';
241 l_inputs(3).value := p_override_threshold;
242 l_inputs(4).name := 'DGE_STATE';
243 l_inputs(4).value := p_dge_state;
244 l_inputs(5).name := 'LE_TAXABLE_INCOME';
245 l_inputs(5).value := p_le_taxable_income;
246 l_inputs(6).name := 'NO_OF_STATES';
247 l_inputs(6).value := p_no_of_states;
248 l_inputs(7).name := 'GROUP_NAME';
249 l_inputs(7).value := p_dge_group_name;
250 l_outputs(1).name := 'MSG';
251 l_outputs(2).name := 'PAYROLL_TAX';
252 l_outputs(3).name := 'WARN_MSG';
253
254 OPEN c_formula;
255 FETCH c_formula into l_formula_id;
256 CLOSE c_formula;
257
258 hr_utility.trace('PAYROLL_TAX input(1) p_state_code : '||nvl(l_inputs(1).value,'ZZZ'));
259 hr_utility.trace('PAYROLL_TAX input(2) p_taxable_income : '||nvl(l_inputs(2).value,0));
260 hr_utility.trace('PAYROLL_TAX input(3) p_override_threshold :'||nvl(l_inputs(3).value,0));
261 hr_utility.trace('PAYROLL_TAX input(4) p_dge_state : '||nvl(l_inputs(4).value,'ZZZ'));
262 hr_utility.trace('PAYROLL_TAX input(5) p_le_taxable_income : '||nvl(l_inputs(5).value,0));
263 hr_utility.trace('PAYROLL_TAX input(6) p_no_of_states : '||nvl(l_inputs(6).value,0));
264 hr_utility.trace('PAYROLL_TAX input(7) p_dge_group_name : '||nvl(l_inputs(7).value,'ZZZ'));
265
266 per_formula_functions.run_formula(p_formula_id => l_formula_id,
267 p_calculation_date => last_day(p_start_date),
268 p_inputs => l_inputs,
269 p_outputs => l_outputs);
270
271 l_tax := l_outputs(2).value;
272 hr_utility.trace('l_outputs(1).value :'||l_outputs(1).value);
273 hr_utility.trace('l_outputs(2).value :'||l_outputs(2).value);
274 hr_utility.trace('l_outputs(3).value :'||l_outputs(3).value);
275
276 IF l_outputs(1).value = 'ZZZZ' THEN
277 p_message:=null;
278 ELSE
279 p_message := l_outputs(1).value;
280 END IF;
281
282 IF l_outputs(3).value = 'ZZZZ' THEN
283 p_ot_message := null;
284 ELSE
285 p_ot_message := l_outputs(3).value;
286 END IF;
287
288 hr_utility.trace('p_message :'||p_message);
289 hr_utility.trace('l_tax :'||l_tax);
290 return(l_tax);
291
292 EXCEPTION when others THEN
293 RAISE_APPLICATION_ERROR(-20001,'Function get_tax ' ||sqlerrm);
294
295 END get_tax;
296
297
298
299 PROCEDURE populate_defined_balance_ids
300 ( p_registered_employer NUMBER
301 ) IS
302
303 /* 5139764 */
304 CURSOR csr_defined_balance_id
305 (c_database_item_suffix pay_balance_dimensions.database_item_suffix%type)
306 IS
307 SELECT decode(pbt.balance_name,'Payroll_Tax_Salaries_Wages',1,'Payroll_Tax_Commissions',2,
308 'Payroll_Tax_Bonuses_Allowances',3,'Payroll_Tax_Director_Fees',4,
309 'Payroll_Tax_Fringe_Benefits',7,'Payroll_Tax_Superannuation',8,
310 'Payroll_Tax_Contractor_Payments',9, 'Payroll_Tax_Other_Taxable_Payments' , '10'
311 ) sort_index,
312 pdb.defined_balance_id defined_balance_id
313 FROM pay_balance_types pbt,
314 pay_balance_dimensions pbd,
315 pay_defined_balances pdb
316 WHERE pbt.balance_name IN ( 'Payroll_Tax_Salaries_Wages', 'Payroll_Tax_Commissions' ,
317 'Payroll_Tax_Bonuses_Allowances', 'Payroll_Tax_Director_Fees',
318 'Payroll_Tax_Fringe_Benefits', 'Payroll_Tax_Superannuation', 'Payroll_Tax_Contractor_Payments',
319 'Payroll_Tax_Other_Taxable_Payments' )
320 AND pbd.database_item_suffix = c_database_item_suffix
321 AND pbt.balance_type_id = pdb.balance_type_id
322 AND pbd.balance_dimension_id = pdb.balance_dimension_id
323 AND pbt.legislation_code = 'AU'
324 ORDER BY sort_index;
325
326 l_run_dimension_name VARCHAR2(15);
327 l_ytd_dimension_name VARCHAR2(15);
328
329 /* start 14134526 */
330 CURSOR csr_defined_balance_id_vic
331 (c_database_item_suffix pay_balance_dimensions.database_item_suffix%type)
332 IS
333 SELECT decode(pbt.balance_name,'Payroll_Tax_VIC_Salaries_Wages',1,'Payroll_Tax_VIC_Commissions',2,
334 'Payroll_Tax_VIC_Bonuses_Allowances',3,'Payroll_Tax_VIC_Director_Fees',4,
335 'Payroll_Tax_VIC_Fringe_Benefits',5,'Payroll_Tax_VIC_Superannuation',6,
336 'Payroll_Tax_VIC_Contractor_Payments',7, 'Payroll_Tax_VIC_Other_Taxable_Payments',8
337 ) sort_index,
338 pdb.defined_balance_id defined_balance_id
339 FROM pay_balance_types pbt,
340 pay_balance_dimensions pbd,
341 pay_defined_balances pdb
342 WHERE pbt.balance_name IN ( 'Payroll_Tax_VIC_Salaries_Wages', 'Payroll_Tax_VIC_Commissions' ,
343 'Payroll_Tax_VIC_Bonuses_Allowances', 'Payroll_Tax_VIC_Director_Fees',
344 'Payroll_Tax_VIC_Fringe_Benefits', 'Payroll_Tax_VIC_Superannuation',
345 'Payroll_Tax_VIC_Contractor_Payments','Payroll_Tax_VIC_Other_Taxable_Payments')
346 AND pbd.database_item_suffix = c_database_item_suffix
347 AND pbt.balance_type_id = pdb.balance_type_id
348 AND pbd.balance_dimension_id = pdb.balance_dimension_id
349 AND pbt.legislation_code = 'AU'
350 ORDER BY sort_index;
351
352 CURSOR csr_defined_balance_id_nsw
353 (c_database_item_suffix pay_balance_dimensions.database_item_suffix%type)
354 IS
355 SELECT decode(pbt.balance_name,'Payroll_Tax_NSW_Salaries_Wages',1,'Payroll_Tax_NSW_Commissions',2,
356 'Payroll_Tax_NSW_Bonuses_Allowances',3,'Payroll_Tax_NSW_Director_Fees',4,
357 'Payroll_Tax_NSW_Fringe_Benefits',5,'Payroll_Tax_NSW_Superannuation',6,
358 'Payroll_Tax_NSW_Contractor_Payments',7, 'Payroll_Tax_NSW_Other_Taxable_Payments',8
359 ) sort_index,
360 pdb.defined_balance_id defined_balance_id
361 FROM pay_balance_types pbt,
362 pay_balance_dimensions pbd,
363 pay_defined_balances pdb
364 WHERE pbt.balance_name IN ( 'Payroll_Tax_NSW_Salaries_Wages', 'Payroll_Tax_NSW_Commissions' ,
365 'Payroll_Tax_NSW_Bonuses_Allowances', 'Payroll_Tax_NSW_Director_Fees',
366 'Payroll_Tax_NSW_Fringe_Benefits', 'Payroll_Tax_NSW_Superannuation',
367 'Payroll_Tax_NSW_Contractor_Payments','Payroll_Tax_NSW_Other_Taxable_Payments')
368 AND pbd.database_item_suffix = c_database_item_suffix
369 AND pbt.balance_type_id = pdb.balance_type_id
370 AND pbd.balance_dimension_id = pdb.balance_dimension_id
371 AND pbt.legislation_code = 'AU'
372 ORDER BY sort_index;
373
374 CURSOR csr_defined_balance_id_act
375 (c_database_item_suffix pay_balance_dimensions.database_item_suffix%type)
376 IS
377 SELECT decode(pbt.balance_name,'Payroll_Tax_ACT_Salaries_Wages',1,'Payroll_Tax_ACT_Commissions',2,
378 'Payroll_Tax_ACT_Bonuses_Allowances',3,'Payroll_Tax_ACT_Director_Fees',4,
379 'Payroll_Tax_ACT_Fringe_Benefits',5,'Payroll_Tax_ACT_Superannuation',6,
380 'Payroll_Tax_ACT_Contractor_Payments',7, 'Payroll_Tax_ACT_Other_Taxable_Payments',8
381 ) sort_index,
382 pdb.defined_balance_id defined_balance_id
383 FROM pay_balance_types pbt,
384 pay_balance_dimensions pbd,
385 pay_defined_balances pdb
386 WHERE pbt.balance_name IN ( 'Payroll_Tax_ACT_Salaries_Wages', 'Payroll_Tax_ACT_Commissions' ,
387 'Payroll_Tax_ACT_Bonuses_Allowances', 'Payroll_Tax_ACT_Director_Fees',
388 'Payroll_Tax_ACT_Fringe_Benefits', 'Payroll_Tax_ACT_Superannuation',
389 'Payroll_Tax_ACT_Contractor_Payments','Payroll_Tax_ACT_Other_Taxable_Payments')
390 AND pbd.database_item_suffix = c_database_item_suffix
391 AND pbt.balance_type_id = pdb.balance_type_id
392 AND pbd.balance_dimension_id = pdb.balance_dimension_id
393 AND pbt.legislation_code = 'AU'
394 ORDER BY sort_index;
395
396 CURSOR csr_defined_balance_id_sa
397 (c_database_item_suffix pay_balance_dimensions.database_item_suffix%type)
398 IS
399 SELECT decode(pbt.balance_name,'Payroll_Tax_SA_Salaries_Wages',1,'Payroll_Tax_SA_Commissions',2,
400 'Payroll_Tax_SA_Bonuses_Allowances',3,'Payroll_Tax_SA_Director_Fees',4,
401 'Payroll_Tax_SA_Fringe_Benefits',5,'Payroll_Tax_SA_Superannuation',6,
402 'Payroll_Tax_SA_Contractor_Payments',7, 'Payroll_Tax_SA_Other_Taxable_Payments',8
403 ) sort_index,
404 pdb.defined_balance_id defined_balance_id
405 FROM pay_balance_types pbt,
406 pay_balance_dimensions pbd,
407 pay_defined_balances pdb
408 WHERE pbt.balance_name IN ( 'Payroll_Tax_SA_Salaries_Wages', 'Payroll_Tax_SA_Commissions' ,
409 'Payroll_Tax_SA_Bonuses_Allowances', 'Payroll_Tax_SA_Director_Fees',
410 'Payroll_Tax_SA_Fringe_Benefits', 'Payroll_Tax_SA_Superannuation',
411 'Payroll_Tax_SA_Contractor_Payments','Payroll_Tax_SA_Other_Taxable_Payments')
412 AND pbd.database_item_suffix = c_database_item_suffix
413 AND pbt.balance_type_id = pdb.balance_type_id
414 AND pbd.balance_dimension_id = pdb.balance_dimension_id
415 AND pbt.legislation_code = 'AU'
416 ORDER BY sort_index;
417
418 CURSOR csr_defined_balance_id_qld
419 (c_database_item_suffix pay_balance_dimensions.database_item_suffix%type)
420 IS
421 SELECT decode(pbt.balance_name,'Payroll_Tax_QLD_Salaries_Wages',1,'Payroll_Tax_QLD_Commissions',2,
422 'Payroll_Tax_QLD_Bonuses_Allowances',3,'Payroll_Tax_QLD_Director_Fees',4,
423 'Payroll_Tax_QLD_Fringe_Benefits',5,'Payroll_Tax_QLD_Superannuation',6,
424 'Payroll_Tax_QLD_Contractor_Payments',7, 'Payroll_Tax_QLD_Other_Taxable_Payments',8
425 ) sort_index,
426 pdb.defined_balance_id defined_balance_id
427 FROM pay_balance_types pbt,
428 pay_balance_dimensions pbd,
429 pay_defined_balances pdb
430 WHERE pbt.balance_name IN ( 'Payroll_Tax_QLD_Salaries_Wages', 'Payroll_Tax_QLD_Commissions' ,
431 'Payroll_Tax_QLD_Bonuses_Allowances', 'Payroll_Tax_QLD_Director_Fees',
432 'Payroll_Tax_QLD_Fringe_Benefits', 'Payroll_Tax_QLD_Superannuation',
433 'Payroll_Tax_QLD_Contractor_Payments','Payroll_Tax_QLD_Other_Taxable_Payments')
434 AND pbd.database_item_suffix = c_database_item_suffix
435 AND pbt.balance_type_id = pdb.balance_type_id
436 AND pbd.balance_dimension_id = pdb.balance_dimension_id
437 AND pbt.legislation_code = 'AU'
438 ORDER BY sort_index;
439
440 CURSOR csr_defined_balance_id_tas
441 (c_database_item_suffix pay_balance_dimensions.database_item_suffix%type)
442 IS
443 SELECT decode(pbt.balance_name,'Payroll_Tax_TAS_Salaries_Wages',1,'Payroll_Tax_TAS_Commissions',2,
444 'Payroll_Tax_TAS_Bonuses_Allowances',3,'Payroll_Tax_TAS_Director_Fees',4,
445 'Payroll_Tax_TAS_Fringe_Benefits',5,'Payroll_Tax_TAS_Superannuation',6,
446 'Payroll_Tax_TAS_Contractor_Payments',7, 'Payroll_Tax_TAS_Other_Taxable_Payments',8
447 ) sort_index,
448 pdb.defined_balance_id defined_balance_id
449 FROM pay_balance_types pbt,
450 pay_balance_dimensions pbd,
451 pay_defined_balances pdb
452 WHERE pbt.balance_name IN ( 'Payroll_Tax_TAS_Salaries_Wages', 'Payroll_Tax_TAS_Commissions' ,
453 'Payroll_Tax_TAS_Bonuses_Allowances', 'Payroll_Tax_TAS_Director_Fees',
454 'Payroll_Tax_TAS_Fringe_Benefits', 'Payroll_Tax_TAS_Superannuation',
455 'Payroll_Tax_TAS_Contractor_Payments','Payroll_Tax_TAS_Other_Taxable_Payments')
456 AND pbd.database_item_suffix = c_database_item_suffix
457 AND pbt.balance_type_id = pdb.balance_type_id
458 AND pbd.balance_dimension_id = pdb.balance_dimension_id
459 AND pbt.legislation_code = 'AU'
460 ORDER BY sort_index;
461
462 CURSOR csr_defined_balance_id_wa
463 (c_database_item_suffix pay_balance_dimensions.database_item_suffix%type)
464 IS
465 SELECT decode(pbt.balance_name,'Payroll_Tax_WA_Salaries_Wages',1,'Payroll_Tax_WA_Commissions',2,
466 'Payroll_Tax_WA_Bonuses_Allowances',3,'Payroll_Tax_WA_Director_Fees',4,
467 'Payroll_Tax_WA_Fringe_Benefits',5,'Payroll_Tax_WA_Superannuation',6,
468 'Payroll_Tax_WA_Contractor_Payments',7, 'Payroll_Tax_WA_Other_Taxable_Payments',8
469 ) sort_index,
470 pdb.defined_balance_id defined_balance_id
471 FROM pay_balance_types pbt,
472 pay_balance_dimensions pbd,
473 pay_defined_balances pdb
474 WHERE pbt.balance_name IN ( 'Payroll_Tax_WA_Salaries_Wages', 'Payroll_Tax_WA_Commissions' ,
475 'Payroll_Tax_WA_Bonuses_Allowances', 'Payroll_Tax_WA_Director_Fees',
476 'Payroll_Tax_WA_Fringe_Benefits', 'Payroll_Tax_WA_Superannuation',
477 'Payroll_Tax_WA_Contractor_Payments','Payroll_Tax_WA_Other_Taxable_Payments')
478 AND pbd.database_item_suffix = c_database_item_suffix
479 AND pbt.balance_type_id = pdb.balance_type_id
480 AND pbd.balance_dimension_id = pdb.balance_dimension_id
481 AND pbt.legislation_code = 'AU'
482 ORDER BY sort_index;
483
484 CURSOR csr_defined_balance_id_nt
485 (c_database_item_suffix pay_balance_dimensions.database_item_suffix%type)
486 IS
487 SELECT decode(pbt.balance_name,'Payroll_Tax_NT_Salaries_Wages',1,'Payroll_Tax_NT_Commissions',2,
488 'Payroll_Tax_NT_Bonuses_Allowances',3,'Payroll_Tax_NT_Director_Fees',4,
489 'Payroll_Tax_NT_Fringe_Benefits',5,'Payroll_Tax_NT_Superannuation',6,
490 'Payroll_Tax_NT_Contractor_Payments',7, 'Payroll_Tax_NT_Other_Taxable_Payments',8
491 ) sort_index,
492 pdb.defined_balance_id defined_balance_id
493 FROM pay_balance_types pbt,
494 pay_balance_dimensions pbd,
495 pay_defined_balances pdb
496 WHERE pbt.balance_name IN ( 'Payroll_Tax_NT_Salaries_Wages', 'Payroll_Tax_NT_Commissions' ,
497 'Payroll_Tax_NT_Bonuses_Allowances', 'Payroll_Tax_NT_Director_Fees',
498 'Payroll_Tax_NT_Fringe_Benefits', 'Payroll_Tax_NT_Superannuation',
499 'Payroll_Tax_NT_Contractor_Payments','Payroll_Tax_NT_Other_Taxable_Payments')
500 AND pbd.database_item_suffix = c_database_item_suffix
501 AND pbt.balance_type_id = pdb.balance_type_id
502 AND pbd.balance_dimension_id = pdb.balance_dimension_id
503 AND pbt.legislation_code = 'AU'
504 ORDER BY sort_index;
505
506 /* end 14134526 */
507
508 BEGIN
509
510 hr_utility.trace('Entering:' || g_package || 'populate_defined_balance_ids');
511
512 g_balance_value_tab.delete;
513
514 l_run_dimension_name := '_ASG_LE_RUN';
515
516 /* The Balance's defined balance id are stored in the following order
517 -----------------------------------------------------
518 Storage Location of
519 Run Defined Balance Id Balance Name
520 -----------------------------------------------------
521 1 Salaries_Wages
522 2 Commmission
523 3 Bonus_Allowances
524 4 Director_Fees
525 7 Fringe_Benefits
526 8 Superannaution
527 9 Contractor_Payments
528 10 Other_Taxable_Payments
529 11 Payroll_Taxable_Income
530 -----------------------------------------------------
531 */
532
533 FOR csr_rec IN csr_defined_balance_id(l_run_dimension_name)
534 LOOP
535 g_balance_value_tab(csr_rec.sort_index).defined_balance_id := csr_rec.defined_balance_id;
536 END LOOP;
537
538 /* start 14134526 */
539 g_balance_value_tab_vic.delete;
540 FOR csr_rec_vic IN csr_defined_balance_id_vic(l_run_dimension_name) LOOP
541 g_balance_value_tab_vic(csr_rec_vic.sort_index).defined_balance_id := csr_rec_vic.defined_balance_id;
542 END LOOP;
543
544 g_balance_value_tab_nsw.delete;
545 FOR csr_rec_nsw IN csr_defined_balance_id_nsw(l_run_dimension_name) LOOP
546 g_balance_value_tab_nsw(csr_rec_nsw.sort_index).defined_balance_id := csr_rec_nsw.defined_balance_id;
547 END LOOP;
548
549 g_balance_value_tab_act.delete;
550 FOR csr_rec_act IN csr_defined_balance_id_act(l_run_dimension_name) LOOP
551 g_balance_value_tab_act(csr_rec_act.sort_index).defined_balance_id := csr_rec_act.defined_balance_id;
552 END LOOP;
553
554 g_balance_value_tab_sa.delete;
555 FOR csr_rec_sa IN csr_defined_balance_id_sa(l_run_dimension_name) LOOP
556 g_balance_value_tab_sa(csr_rec_sa.sort_index).defined_balance_id := csr_rec_sa.defined_balance_id;
557 END LOOP;
558
559 g_balance_value_tab_qld.delete;
560 FOR csr_rec_qld IN csr_defined_balance_id_qld(l_run_dimension_name) LOOP
561 g_balance_value_tab_qld(csr_rec_qld.sort_index).defined_balance_id := csr_rec_qld.defined_balance_id;
562 END LOOP;
563
564 g_balance_value_tab_tas.delete;
565 FOR csr_rec_tas IN csr_defined_balance_id_tas(l_run_dimension_name) LOOP
566 g_balance_value_tab_tas(csr_rec_tas.sort_index).defined_balance_id := csr_rec_tas.defined_balance_id;
567 END LOOP;
568
569 g_balance_value_tab_wa.delete;
570 FOR csr_rec_wa IN csr_defined_balance_id_wa(l_run_dimension_name) LOOP
571 g_balance_value_tab_wa(csr_rec_wa.sort_index).defined_balance_id := csr_rec_wa.defined_balance_id;
572 END LOOP;
573
574 g_balance_value_tab_nt.delete;
575 FOR csr_rec_nt IN csr_defined_balance_id_nt(l_run_dimension_name) LOOP
576 g_balance_value_tab_nt(csr_rec_nt.sort_index).defined_balance_id := csr_rec_nt.defined_balance_id;
577 END LOOP;
578
579 /* end 14134526 */
580
581 END;
582
583 /*
584 * Implemented the Horizontal Archive for Payroll Tax Report
585 * Procedures
586 * 1. range_code
587 * 2. assignment_action_code
588 * 3. archive_code
589 * 4. spawn_archive_reports
590 */
591
592 procedure range_code
593 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type
594 ,p_sql out NOCOPY varchar2
595 ) is
596
597 l_procedure varchar2(200) ;
598
599 begin
600
601 g_debug :=hr_utility.debug_enabled ;
602
603 l_procedure := g_package||'range_code';
604 hr_utility.set_location('Entering '||l_procedure,1);
605
606 -- Archive the payroll action level data and EIT defintions.
607 -- sql string to SELECT a range of assignments eligible for archival.
608 p_sql := ' select distinct p.person_id' ||
609 ' from per_people_f p,' ||
610 ' pay_payroll_actions pa' ||
611 ' where pa.payroll_action_id = :payroll_action_id' ||
612 ' and p.business_group_id = pa.business_group_id' ||
613 ' order by p.person_id';
614
615 hr_utility.set_location('Leaving '||l_procedure,1000);
616
617 end range_code;
618
619 procedure initialization_code
620 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type)
621 is
622 l_procedure varchar2(200) ;
623 l_defined_balance_id number;
624 l_term_defined_balance_id number;
625 l_elig_term_defined_balance_id number;
626 l_balance_Value number;
627 l_term_balance_Value number;
628 l_elig_term_balance_Value number;
629
630 --------------------------------------------------------------------+
631 -- Cursor : csr_params
632 -- Description : Fetches User Parameters from Legislative_paramters
633 -- column.
634 --------------------------------------------------------------------+
635
636 CURSOR csr_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
637 IS
638 SELECT pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
639 pay_core_utils.get_parameter('LE',legislative_parameters) legal_employer,
640 to_date(pay_core_utils.get_parameter('PERIOD',legislative_parameters),'YYYY/MM/DD') period, /*4709766 */
641 to_date(pay_core_utils.get_parameter('SDATE',legislative_parameters),'YYYY/MM/DD') start_date,
642 to_date(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD') end_date,
643 pay_core_utils.get_parameter('TAX_STATE',legislative_parameters) Tax_state,
644 pay_core_utils.get_parameter('REP_TYPE',legislative_parameters)report_type,
645 pay_core_utils.get_parameter('REP_NAME',legislative_parameters)report_name,
646 pay_core_utils.get_parameter('ACTT',legislative_parameters)act_override_threshold,
647 pay_core_utils.get_parameter('VICT',legislative_parameters)vic_override_threshold,
648 pay_core_utils.get_parameter('NSWT',legislative_parameters)nsw_override_threshold,
649 pay_core_utils.get_parameter('QLDT',legislative_parameters)qld_override_threshold,
650 pay_core_utils.get_parameter('WAT',legislative_parameters)wa_override_threshold,
651 pay_core_utils.get_parameter('NTT',legislative_parameters)nt_override_threshold,
652 pay_core_utils.get_parameter('SAT',legislative_parameters)sa_override_threshold,
653 pay_core_utils.get_parameter('TAST',legislative_parameters)tas_override_threshold
654 FROM pay_payroll_actions ppa
655 WHERE ppa.payroll_action_id = c_payroll_action_id;
656
657
658 /* 4713372 */
659 CURSOR csr_Paytax_defined_balance_id
660 (c_database_item_suffix pay_balance_dimensions.database_item_suffix%type,
661 c_state_code varchar2)
662 IS
663 SELECT pdb.defined_balance_id defined_balance_id
664 FROM pay_balance_types pbt,
665 pay_balance_dimensions pbd,
666 pay_defined_balances pdb
667 WHERE pbt.balance_name = 'Payroll_Tax_' || c_state_code
668 AND pbd.database_item_suffix = c_database_item_suffix
669 AND pbt.balance_type_id = pdb.balance_type_id
670 AND pbd.balance_dimension_id = pdb.balance_dimension_id
671 AND pbt.legislation_code = 'AU';
672
673 /* 5139764 */
674 CURSOR csr_TPaytax_defined_balance_id
675 (c_database_item_suffix pay_balance_dimensions.database_item_suffix%type,
676 c_state_code varchar2)
677 IS
678 SELECT pdb.defined_balance_id defined_balance_id
679 FROM pay_balance_types pbt,
680 pay_balance_dimensions pbd,
681 pay_defined_balances pdb
682 WHERE pbt.balance_name = 'Payroll_Tax_' || c_state_code || '_Termination_Payments'
683 AND pbd.database_item_suffix = c_database_item_suffix
684 AND pbt.balance_type_id = pdb.balance_type_id
685 AND pbd.balance_dimension_id = pdb.balance_dimension_id
686 AND pbt.legislation_code = 'AU';
687
688
689 /* 5139764 */
690
691 CURSOR csr_ETPaytax_defined_balance
692 (c_database_item_suffix pay_balance_dimensions.database_item_suffix%type,
693 c_state_code varchar2)
694 IS
695 SELECT pdb.defined_balance_id defined_balance_id
696 FROM pay_balance_types pbt,
697 pay_balance_dimensions pbd,
698 pay_defined_balances pdb
699 WHERE pbt.balance_name = 'Payroll_Tax_' || c_state_code || '_Eligible_Termination_Payments'
700 AND pbd.database_item_suffix = c_database_item_suffix
701 AND pbt.balance_type_id = pdb.balance_type_id
702 AND pbd.balance_dimension_id = pdb.balance_dimension_id
703 AND pbt.legislation_code = 'AU';
704
705 /* 4713372 */
706 cursor csr_get_ass_le_act_id (c_business_group_id hr_all_organization_units.organization_id%TYPE,
707 c_registered_employer hr_all_organization_units.organization_id%TYPE,
708 c_start_date date,
709 c_end_date date)
710 is
711 select distinct per_information2, paa.tax_unit_id,
712 assignment_action_id assignment_action_id
713 from pay_payroll_actions ppa,
714 pay_assignment_actions paa,
715 per_people_f pap,
716 per_assignments_f paf
717 where paa.payroll_action_id = ppa.payroll_action_id
718 and pap.person_id = paf.person_id
719 and paa.assignment_id = paf.assignment_id
720 and paa.tax_unit_id=nvl(c_registered_employer, paa.tax_unit_id)
721 and paf.business_group_id=c_business_group_id
722 and ppa.action_status='C'
723 and (pap.per_information3 = 'N' or pap.per_information3 is null)
724 and ppa.action_type in ('R','Q','I','B','V')
725 and ppa.effective_date between c_start_date and c_end_date
726 and ppa.effective_date between pap.effective_start_date and pap.effective_end_date /* 4729052 */
727 AND (paa.source_action_id IS NULL
728 OR (paa.source_action_id IS NOT NULL AND ppa.run_type_id IS NULL))
729 and paf.effective_end_date = (select max(effective_end_date) /* 4729052 */
730 From per_assignments_f iipaf
731 WHERE iipaf.assignment_id = paf.assignment_id
732 and iipaf.effective_end_date >= c_start_date
733 and iipaf.effective_start_date <= c_end_date)
734
735 order by per_information2;
736
737
738 Begin
739
740 g_debug :=hr_utility.debug_enabled ;
741 g_package := 'pay_au_tax_report_pkg.' ;
742 l_procedure := g_package||'initialization_code';
743 hr_utility.set_location('Entering '||l_procedure,1);
744
745
746 -- initialization_code to to set the global tables for EIT
747 -- that will be used by each thread in multi-threading.
748
749 g_arc_payroll_action_id := p_payroll_action_id;
750
751 -- Fetch the parameters by user passed into global variable.
752
753 OPEN csr_params(p_payroll_action_id);
754 FETCH csr_params into g_parameters;
755 CLOSE csr_params;
756
757
758 IF g_debug THEN
759 hr_utility.set_location('p_payroll_action_id.........= ' || p_payroll_action_id,30);
760 hr_utility.set_location('g_parameters.business_group_id.........= ' || g_parameters.business_group_id,30);
761 hr_utility.set_location('g_parameters.legal_employer.........= ' || g_parameters.legal_employer,30);
762 hr_utility.set_location('g_parameters.start_date..............= ' || g_parameters.start_date,30);
763 hr_utility.set_location('g_parameters.end_date................= ' || g_parameters.end_date,30);
764 hr_utility.set_location('g_parameters.tax_state.........= ' || g_parameters.tax_state,30);
765 hr_utility.set_location('g_parameters.report_type..........= '||g_parameters.report_type,30);
766 hr_utility.set_location('g_parameters.act_threshold..........= '||g_parameters.act_override_threshold,30);
767 hr_utility.set_location('g_parameters.vic_threshold..........= '||g_parameters.vic_override_threshold,30);
768 hr_utility.set_location('g_parameters.qld_threshold..........= '||g_parameters.qld_override_threshold,30);
769 hr_utility.set_location('g_parameters.nsw_threshold..........= '||g_parameters.nsw_override_threshold,30);
770 hr_utility.set_location('g_parameters.tas_threshold..........= '||g_parameters.tas_override_threshold,30);
771 hr_utility.set_location('g_parameters.wa_threshold..........= '||g_parameters.wa_override_threshold,30);
772 hr_utility.set_location('g_parameters.sa_threshold..........= '||g_parameters.sa_override_threshold,30);
773 hr_utility.set_location('g_parameters.nt_threshold..........= '||g_parameters.nt_override_threshold,30);
774 end if;
775
776 g_business_group_id := g_parameters.business_group_id ;
777
778
779 populate_defined_balance_ids(g_parameters.legal_employer);
780 /* 4713372 */
781
782 g_count := 0;
783 g_le_taxable_income := 0;
784 g_prev_tax_state := 'ZZZ';
785
786
787 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)
788 LOOP
789
790 open csr_paytax_defined_balance_id('_ASG_LE_RUN', csr_le_rec.per_information2); /* 4718544 */
791 fetch csr_paytax_defined_balance_id into l_defined_balance_id;
792 close csr_paytax_defined_balance_id;
793
794 /* 5139764 */
795
796 open csr_tpaytax_defined_balance_id('_ASG_LE_RUN', csr_le_rec.per_information2); /* 4718544 */
797 fetch csr_tpaytax_defined_balance_id into l_term_defined_balance_id;
798 close csr_tpaytax_defined_balance_id;
799
800 /* 5139764 */
801
802 open csr_etpaytax_defined_balance('_ASG_LE_RUN', csr_le_rec.per_information2); /* 4718544 */
803 fetch csr_etpaytax_defined_balance into l_elig_term_defined_balance_id;
804 close csr_etpaytax_defined_balance;
805
806 hr_utility.set_location('l_term_defined ' || l_term_defined_balance_id,99);
807 hr_utility.set_location('per_information ' || csr_le_rec.per_information2,99);
808
809 l_balance_value := pay_balance_pkg.get_value(l_defined_balance_id,
810 csr_le_rec.assignment_action_id, csr_le_rec.tax_unit_id, null,null,null,null);
811
812 /* 5139764 */
813
814 l_term_balance_value := pay_balance_pkg.get_value(l_term_defined_balance_id,
815 csr_le_rec.assignment_action_id, csr_le_rec.tax_unit_id, null,null,null,null);
816
817 /* 5139764 */
818
819 l_elig_term_balance_value := pay_balance_pkg.get_value(l_elig_term_defined_balance_id,
820 csr_le_rec.assignment_action_id, csr_le_rec.tax_unit_id, null,null,null,null);
821
822 hr_utility.set_location('l_balanace_value ' || l_balance_value,999);
823
824 g_le_taxable_income := nvl(g_le_taxable_income,0) + l_balance_value;
825
826 if g_prev_tax_state <> csr_le_rec.per_information2
827 then
828
829 g_count := g_count + 1;
830 g_prev_tax_state := csr_le_rec.per_information2;
831
832 end if;
833
834 END LOOP;
835 /* 4713372 */
836 if g_debug then
837 hr_utility.set_location('g_count '||g_count,1000);
838 hr_utility.set_location('g_le_taxable_income '||g_le_taxable_income,1000);
839 hr_utility.set_location('Leaving '||l_procedure,1000);
840 end if;
841
842 end initialization_code;
843
844 --------------------------------------------------------------------+
845 -- Name : assignment_Action_code
846 -- Type : Procedure
847 -- Access: Public
848 -- This procedure further restricts the assignment_id's
849 -- returned by range_code
850 -- This procedure gets the parameters given by user and restricts
851 -- the assignments to be archived.
852 -- it then calls hr_nonrun.insact to create an assignment action id
853 -- it then archives Payroll Run assignment action id details
854 -- in pay_Action_information with context 'AU_ARCHIVE_ASG_DETAILS'
855 -- for each assignment.
856 -- There are 10 different cursors for choosing the assignment ids.
857 -- Depending on the parameters passed,the appropriate cursor is used.
858 --------------------------------------------------------------------+
859
860 procedure assignment_action_code
861 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type
862 ,p_start_person in per_all_people_f.person_id%type
863 ,p_end_person in per_all_people_f.person_id%type
864 ,p_chunk in number
865 ) is
866
867
868
869 cursor csr_assignment_period
870 (p_payroll_action_id pay_payroll_actions.payroll_action_id%type
871 ,p_start_person per_all_people_f.person_id%type
872 ,p_end_person per_all_people_f.person_id%type
873 ,p_report_type varchar2
874 ,p_business_group_id hr_all_organization_units.organization_id%type
875 ,p_legal_employer hr_all_organization_units.organization_id%type
876 ,p_archive_start_date date
877 ,p_archive_end_date date
878 ,p_tax_state varchar2
879 ) is
880 select paa.assignment_action_id,
881 paa.action_sequence,
882 paaf.assignment_id,
883 paa.tax_unit_id,
884 paa.source_action_id master_action_id,
885 paa2.tax_unit_id master_tax_unit_id
886 from per_people_f pap,
887 per_assignments_f paaf,
888 pay_payroll_actions ppa,
889 pay_payroll_actions ppa1,
890 pay_assignment_actions paa,
891 pay_assignment_actions paa2,
892 per_periods_of_service pps
893 where ppa.payroll_action_id = p_payroll_action_id
894 and paa.assignment_id = paaf.assignment_id
895 and paa2.assignment_id = paaf.assignment_id
896 AND paa2.assignment_id = paa.assignment_id
897 and pap.person_id between p_start_person and p_end_person
898 and pap.person_id = paaf.person_id
899 and pap.person_id = pps.person_id
900 and pps.period_of_service_id = paaf.period_of_service_id
901 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
902 and ppa1.payroll_action_id = paa.payroll_action_id
903 and ppa1.payroll_action_id = paa2.payroll_action_id
904 AND paa2.action_status ='C'
905 AND paa.action_status ='C'
906 and (pap.per_information3 = 'N' or pap.per_information3 is null)
907 AND (pap.per_information2 = p_tax_state or p_tax_state is null)
908 AND paa2.assignment_action_id = nvl(paa.source_action_id, paa.assignment_action_id)
909 AND (paa.source_action_id IS NULL)
910 -- OR (paa.source_action_id IS NOT NULL AND ppa.run_type_id IS NULL))
911 and ppa1.business_group_id = ppa.business_group_id
912 and ppa.business_group_id = p_business_group_id
913 and ppa1.action_type in ('R','Q','I','B','V')
914 and ( paa.tax_unit_id = p_legal_employer or p_legal_employer is null)
915 and ppa1.effective_date between p_archive_start_date and p_archive_end_date
916 and paaf.effective_end_date = (select max(effective_end_date)
917 From per_assignments_f iipaf
918 WHERE iipaf.assignment_id = paaf.assignment_id
919 and iipaf.effective_end_date >= p_archive_start_date
920 and iipaf.effective_start_date <= p_archive_end_date)
921 order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id, paa.source_action_id, paa2.tax_unit_id;
922
923
924 --------------------------------------------------------------------+
925 -- Cursor : csr_params
926 -- Description : Fetches User Parameters from Legislative_paramters
927 -- column.
928 --------------------------------------------------------------------+
929
930 CURSOR csr_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
931 IS
932 SELECT pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
933 pay_core_utils.get_parameter('LE',legislative_parameters) legal_employer,
934 to_date(pay_core_utils.get_parameter('PERIOD',legislative_parameters),'YYYY/MM/DD') period,
935 to_date(pay_core_utils.get_parameter('SDATE',legislative_parameters),'YYYY/MM/DD') start_date,
936 to_date(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD') end_date,
937 pay_core_utils.get_parameter('TAX_STATE',legislative_parameters) Tax_state,
938 pay_core_utils.get_parameter('REP_TYPE',legislative_parameters)report_type,
939 pay_core_utils.get_parameter('REP_NAME',legislative_parameters)report_name,
940 pay_core_utils.get_parameter('ACTT',legislative_parameters)act_override_threshold,
941 pay_core_utils.get_parameter('VICT',legislative_parameters)vic_override_threshold,
942 pay_core_utils.get_parameter('NSWT',legislative_parameters)nsw_override_threshold,
943 pay_core_utils.get_parameter('QLDT',legislative_parameters)qld_override_threshold,
944 pay_core_utils.get_parameter('WAT',legislative_parameters)wa_override_threshold,
945 pay_core_utils.get_parameter('NTT',legislative_parameters)nt_override_threshold,
946 pay_core_utils.get_parameter('SAT',legislative_parameters)sa_override_threshold,
947 pay_core_utils.get_parameter('TAST',legislative_parameters)tas_override_threshold
948 FROM pay_payroll_actions ppa
949 WHERE ppa.payroll_action_id = c_payroll_action_id;
950
951
952
953 cursor csr_next_action_id is
954 select pay_assignment_actions_s.nextval
955 from dual;
956
957 l_next_assignment_action_id pay_assignment_actions.assignment_action_id%type;
958 l_procedure varchar2(200) ;
959 i number;
960
961 l_action_information_id number;
962 l_object_version_number number;
963
964
965 begin
966 i := 1;
967 g_package := 'pay_au_tax_rep_pkg.' ;
968 l_procedure := g_package||'assignment_action_code';
969 hr_utility.set_location('Entering ' || l_procedure,1);
970 hr_utility.set_location('Entering assignment_Action_code',302);
971
972
973 -- initialization_code to to set the global tables for EIT
974 -- that will be used by each thread in multi-threading.
975 g_arc_payroll_action_id := p_payroll_action_id;
976 hr_utility.set_location('p_payroll_Action' || p_payroll_Action_id, 777);
977
978 -- Fetch the parameters by user passed into global variable.
979
980 OPEN csr_params(p_payroll_action_id);
981 FETCH csr_params into g_parameters;
982 CLOSE csr_params;
983
984
985 IF g_debug THEN
986
987 hr_utility.set_location('p_payroll_action_id.........= ' || p_payroll_action_id,30);
988 hr_utility.set_location('g_parameters.business_group_id.........= ' || g_parameters.business_group_id,30);
989 hr_utility.set_location('g_parameters.legal_employer.........= ' || g_parameters.legal_employer,30);
990 hr_utility.set_location('g_parameters.start_date..............= ' || g_parameters.start_date,30);
991 hr_utility.set_location('g_parameters.end_date................= ' || g_parameters.end_date,30);
992 hr_utility.set_location('g_parameters.tax_state.........= ' || g_parameters.tax_state,30);
993 hr_utility.set_location('g_parameters.report_type..........= '||g_parameters.report_type,30);
994 hr_utility.set_location('g_parameters.act_threshold..........= '||g_parameters.act_override_threshold,30);
995 hr_utility.set_location('g_parameters.vic_threshold..........= '||g_parameters.vic_override_threshold,30);
996 hr_utility.set_location('g_parameters.qld_threshold..........= '||g_parameters.qld_override_threshold,30);
997 hr_utility.set_location('g_parameters.nsw_threshold..........= '||g_parameters.nsw_override_threshold,30);
998 hr_utility.set_location('g_parameters.tas_threshold..........= '||g_parameters.tas_override_threshold,30);
999 hr_utility.set_location('g_parameters.wa_threshold..........= '||g_parameters.wa_override_threshold,30);
1000 hr_utility.set_location('g_parameters.sa_threshold..........= '||g_parameters.sa_override_threshold,30);
1001 hr_utility.set_location('g_parameters.nt_threshold..........= '||g_parameters.nt_override_threshold,30);
1002
1003 end if;
1004
1005 g_business_group_id := g_parameters.business_group_id ;
1006
1007
1008
1009
1010
1011 FOR csr_rec in csr_assignment_period(p_payroll_action_id,
1012 p_start_person,
1013 p_end_person,
1014 g_parameters.report_type,
1015 g_parameters.business_group_id,
1016 g_parameters.legal_employer,
1017 g_parameters.start_date,
1018 g_parameters.end_date,
1019 g_parameters.tax_state)
1020 LOOP
1021 hr_utility.set_location('in loop' , 555);
1022 open csr_next_action_id;
1023 fetch csr_next_action_id into l_next_assignment_action_id;
1024 close csr_next_action_id;
1025
1026 -- Create the archive assignment actions
1027 hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1028
1029 insert into pay_action_information(
1030 action_information_id,
1031 action_context_id,
1032 action_context_type,
1033 effective_date,
1034 source_id,
1035 tax_unit_id,
1036 action_information_category,
1037 action_information1,
1038 action_information2,
1039 action_information3,
1040 assignment_id
1041 )
1042 values(
1043 pay_action_information_s.nextval,
1044 l_next_assignment_action_id,
1045 'AAP',
1046 null,
1047 null,
1048 csr_rec.tax_unit_id,
1049 'AU_ARCHIVE_ASG_DETAILS',
1050 csr_rec.assignment_action_id,
1051 p_payroll_action_id,
1052 csr_rec.action_sequence,
1053 csr_rec.assignment_id
1054 );
1055
1056
1057 END LOOP;
1058 if g_debug then
1059 hr_utility.set_location('Leaving............Loop7. Asg + Period...' || l_procedure,1000);
1060 end if;
1061
1062 exception
1063 when others then
1064 hr_utility.set_location('Error in '||l_procedure,999999);
1065 raise;
1066 end assignment_action_code;
1067
1068 procedure archive_code
1069 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
1070 ,p_effective_date in pay_payroll_actions.effective_date%type
1071 ) is
1072
1073 /* 5893671*/
1074 cursor c_get_employee_full_name (c_person_id number, c_end_date date, c_start_date date)
1075 is
1076 select full_name
1077 from per_people_f pap
1078 where person_id= c_person_id
1079 and pap.effective_end_date =
1080 (select max(effective_end_date)
1081 from per_people_f pap1
1082 where pap1.person_id = pap.person_id
1083 and pap1.effective_end_date >= c_start_date
1084 and pap1.effective_start_date <= c_end_date
1085 );
1086
1087 cursor c_employee_details(c_business_group_id hr_all_organization_units.organization_id%TYPE,
1088 c_assignment_id number,c_end_date date, c_start_date date,
1089 c_assignment_action_id number,
1090 c_payroll_action_id number)
1091 is /* 5893671 - commented out full name*/
1092 select /* pap.full_name, */
1093 paaf.assignment_number employee_number,
1094 ppa1.effective_date,
1095 paa2.assignment_action_id,
1096 paaf.assignment_id,
1097 paaf.organization_id,
1098 hou.NAME organization_name,
1099 hsc.segment1 tax_unit_id,
1100 hou1.NAME Legal_Employer ,
1101 pap.person_id,
1102 pap.per_information2 state_code ,
1103 hoi.org_information1 business_group_id,
1104 /* 4716254 ,4718544 */
1105 decode(pap.per_information2 , 'VIC', hoi.org_information4,
1106 'WA' , hoi.org_information11,
1107 'QLD' , hoi.org_information7,
1108 'SA' , hoi.org_information8,
1109 'NSW' , hoi.org_information5,
1110 'ACT' , hoi.org_information10,
1111 'NT' , hoi.org_information9,
1112 'TAS' , hoi.org_information6) dge_state,
1113 /* 4716254 ,4718544 */
1114
1115 hoi.org_information2 dge_legal_employer,
1116 hoi.org_information3 dge_group_name,
1117 hl.meaning state_desc
1118 from per_people_f pap,
1119 per_assignments_f paaf,
1120 pay_payroll_actions ppa,
1121 pay_payroll_actions ppa1,
1122 pay_assignment_actions paa,
1123 pay_assignment_actions paa2,
1124 hr_soft_coding_keyflex hsc,
1125 hr_organization_units hou,
1126 hr_organization_units hou1,
1127 hr_organization_information hoi,
1128 hr_lookups hl,
1129 per_periods_of_service pps
1130 where ppa.payroll_action_id = c_payroll_action_id
1131 and paa.assignment_Action_id = c_assignment_Action_id /*5235423 */
1132 and paa.assignment_id = paaf.assignment_id
1133 and pap.person_id = paaf.person_id
1134 and paa.assignment_id = c_assignment_id
1135 AND pap.per_information2 = hl.lookup_code
1136 AND hl.lookup_type = 'AU_STATE'
1137 AND hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
1138 AND hou.organization_id = paaf.organization_id
1139 and hoi.org_information_context(+) = 'AU_PAYROLL_TAX_DGE'
1140 AND hou1.organization_id = hoi.organization_id(+)
1141 AND hou1.organization_id = hsc.segment1
1142 AND paa2.assignment_id = paa.assignment_id
1143 and pap.person_id = pps.person_id
1144 and pps.period_of_service_id = paaf.period_of_service_id
1145 and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
1146 and ppa1.payroll_action_id = paa.payroll_action_id
1147 and ppa1.payroll_action_id = paa2.payroll_action_id
1148 AND paa2.action_status ='C'
1149 AND paa.action_status ='C'
1150 and (pap.per_information3 = 'N' or pap.per_information3 is null)
1151 AND paa2.assignment_action_id = nvl(paa.source_action_id, paa.assignment_action_id)
1152 AND paa.source_action_id IS NULL /* 5235423 */
1153 and ppa1.business_group_id = ppa.business_group_id
1154 and ppa.business_group_id = c_business_group_id
1155 and ppa1.action_type in ('R','Q','I','B','V')
1156 and ppa1.effective_date between c_start_date and c_end_date
1157 and paaf.effective_end_date = (select max(effective_end_date)
1158 From per_assignments_f iipaf
1159 WHERE iipaf.assignment_id = paaf.assignment_id
1160 and iipaf.effective_end_date >= c_start_date
1161 and iipaf.effective_start_date <= c_end_date)
1162 /* and c_end_date between pap.effective_start_date and pap.effective_end_date */
1163 order by paaf.assignment_id, paa2.assignment_action_id, hsc.segment1;
1164
1165
1166
1167 cursor csr_get_data (c_arc_ass_act_id number)
1168 is
1169 select pai.action_information1, pai.action_information2, pai.tax_unit_id, pai.assignment_id,pai.action_information3
1170 from pay_action_information pai
1171 where action_information_category = 'AU_ARCHIVE_ASG_DETAILS'
1172 and pai.action_context_id = c_arc_ass_act_id;
1173
1174
1175 CURSOR csr_Paytax_defined_balance_id
1176 (c_database_item_suffix pay_balance_dimensions.database_item_suffix%type,
1177 c_state_code varchar2)
1178 IS
1179 SELECT pdb.defined_balance_id defined_balance_id
1180 FROM pay_balance_types pbt,
1181 pay_balance_dimensions pbd,
1182 pay_defined_balances pdb
1183 WHERE pbt.balance_name = 'Payroll_Tax_' || c_state_code
1184 AND pbd.database_item_suffix = c_database_item_suffix
1185 AND pbt.balance_type_id = pdb.balance_type_id
1186 AND pbd.balance_dimension_id = pdb.balance_dimension_id
1187 AND pbt.legislation_code = 'AU';
1188
1189
1190 /* 5139764 */
1191
1192 CURSOR csr_TPaytax_defined_balance_id
1193 (c_database_item_suffix pay_balance_dimensions.database_item_suffix%type,
1194 c_state_code varchar2)
1195 IS
1196 SELECT pdb.defined_balance_id defined_balance_id
1197 FROM pay_balance_types pbt,
1198 pay_balance_dimensions pbd,
1199 pay_defined_balances pdb
1200 WHERE pbt.balance_name = 'Payroll_Tax_' || c_state_code || '_Termination_Payments'
1201 AND pbd.database_item_suffix = c_database_item_suffix
1202 AND pbt.balance_type_id = pdb.balance_type_id
1203 AND pbd.balance_dimension_id = pdb.balance_dimension_id
1204 AND pbt.legislation_code = 'AU';
1205
1206
1207 /* 5139764 */
1208
1209 CURSOR csr_ETPaytax_defined_balance
1210 (c_database_item_suffix pay_balance_dimensions.database_item_suffix%type,
1211 c_state_code varchar2)
1212 IS
1213 SELECT pdb.defined_balance_id defined_balance_id
1214 FROM pay_balance_types pbt,
1215 pay_balance_dimensions pbd,
1216 pay_defined_balances pdb
1217 WHERE pbt.balance_name = 'Payroll_Tax_' || c_state_code || '_Eligible_Termination_Payments'
1218 AND pbd.database_item_suffix = c_database_item_suffix
1219 AND pbt.balance_type_id = pdb.balance_type_id
1220 AND pbd.balance_dimension_id = pdb.balance_dimension_id
1221 AND pbt.legislation_code = 'AU';
1222
1223
1224 l_procedure varchar2(200);
1225 l_action_information_id number;
1226 l_object_version_number number;
1227
1228 l_SALARIES_WAGES number :=0;
1229 l_COMMISSION number:=0;
1230 l_BONUS_ALLOWANCES number:=0;
1231 l_DIRECTOR_FEES number :=0;
1232 l_TERMINATION_PAYMENTS number :=0;
1233 l_ELIGIBLE_TERM_PAYMENTS number :=0;
1234 l_FRINGE_BENEFITS number :=0;
1235 l_SUPERANNUATION number :=0;
1236 l_CONTRACTOR_PAYMENTS number :=0;
1237 l_OTHER_TAXABLE_INCOME number :=0;
1238 L_TAXABLE_INCOME number :=0;
1239 L_LE_TAXABLE_INCOME number :=0;
1240 L_NSW_TAXABLE_INCOME number :=0;
1241 L_QLD_TAXABLE_INCOME number :=0;
1242 L_ACT_TAXABLE_INCOME number :=0;
1243 L_NT_TAXABLE_INCOME number :=0;
1244 L_SA_TAXABLE_INCOME number :=0;
1245 L_WA_TAXABLE_INCOME number :=0;
1246 L_TAS_TAXABLE_INCOME number :=0;
1247 l_count number :=0;
1248 l_run_dimension_name varchar2(15);
1249 l_ass_act_id number;
1250 l_payroll_action_id number;
1251 l_tax_unit_id number;
1252 l_assignment_id number;
1253 l_full_name varchar2(100);
1254
1255 l_action_sequence number;
1256 l_max_asg_action_id number;
1257 l_max_action_sequence number;
1258 l_defined_balance_id number;
1259
1260 /* start 14134526 */
1261 l_SALARIES_WAGES_2 number :=0;
1262 l_COMMISSION_2 number:=0;
1263 l_BONUS_ALLOWANCES_2 number:=0;
1264 l_DIRECTOR_FEES_2 number :=0;
1265 l_FRINGE_BENEFITS_2 number :=0;
1266 l_SUPERANNUATION_2 number :=0;
1267 l_CONTRACTOR_PAYMENTS_2 number :=0;
1268 l_OTHER_TAXABLE_INCOME_2 number :=0;
1269 /* end 14134526 */
1270
1271 begin
1272
1273 g_debug :=hr_utility.debug_enabled ;
1274 g_package := 'pay_au_tax_rep_pkg.' ;
1275 l_procedure := g_package||'archive_code';
1276 l_run_dimension_name := '_ASG_LE_RUN';
1277
1278 OPEN csr_get_data(p_assignment_action_id);
1279 FETCH csr_get_data into l_ass_act_id, l_payroll_Action_id,l_tax_unit_id, l_assignment_id,l_action_sequence;
1280 CLOSE csr_get_data;
1281
1282
1283 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)
1284 LOOP
1285 /* 5893671 */
1286
1287
1288 OPEN c_get_employee_full_name(csr_rec.person_id, g_parameters.end_date, g_parameters.start_date);
1289 FETCH c_get_employee_full_name into l_full_name;
1290 CLOSE c_get_employee_full_name;
1291
1292 g_prev_assignment_id := csr_rec.assignment_id;
1293 g_prev_tax_state := csr_rec.state_code;
1294
1295
1296 /* 5139764 */
1297
1298 FOR csr_pt IN csr_Paytax_defined_balance_id(l_run_dimension_name, csr_rec.state_code)
1299 LOOP
1300 g_balance_value_tab(11).defined_balance_id := csr_pt.defined_balance_id;
1301 END LOOP;
1302
1303 /* 5139764 */
1304
1305 FOR csr_ptt IN csr_TPaytax_defined_balance_id(l_run_dimension_name, csr_rec.state_code)
1306 LOOP
1307 g_balance_value_tab(5).defined_balance_id := csr_ptt.defined_balance_id;
1308 END LOOP;
1309
1310
1311 FOR csr_eptt IN csr_ETPaytax_defined_balance(l_run_dimension_name, csr_rec.state_code)
1312 LOOP
1313 g_balance_value_tab(6).defined_balance_id := csr_eptt.defined_balance_id;
1314 END LOOP;
1315
1316 -- Balances Coding for BBR
1317
1318 -- Get The Action Sequence for the Assignment_Action_Id.
1319
1320 GET_BALANCES(
1321 P_ASSIGNMENT_ACTION_ID => csr_rec.assignment_action_id,
1322 P_REGISTERED_EMPLOYER => l_tax_unit_id,
1323 P_TAX_STATE => csr_rec.state_code,
1324 P_SALARIES_WAGES => l_SALARIES_WAGES,
1325 P_COMMISSION => l_COMMISSION,
1326 P_BONUS_ALLOWANCES => l_BONUS_ALLOWANCES,
1327 P_DIRECTOR_FEES => l_DIRECTOR_FEES,
1328 P_TERMINATION_PAYMENTS => l_TERMINATION_PAYMENTS,
1329 P_ELIGIBLE_TERM_PAYMENTS => l_ELIGIBLE_TERM_PAYMENTS,
1330 P_FRINGE_BENEFITS => l_FRINGE_BENEFITS,
1331 P_SUPERANNUATION => l_SUPERANNUATION,
1332 P_CONTRACTOR_PAYMENTS => l_CONTRACTOR_PAYMENTS,
1333 P_OTHER_TAXABLE_INCOME => l_OTHER_TAXABLE_INCOME,
1334 P_TAXABLE_INCOME => l_TAXABLE_INCOME,
1335 P_SALARIES_WAGES_2 => l_SALARIES_WAGES_2,
1336 P_COMMISSION_2 => l_COMMISSION_2,
1337 P_BONUS_ALLOWANCES_2 => l_BONUS_ALLOWANCES_2,
1338 P_DIRECTOR_FEES_2 => l_DIRECTOR_FEES_2,
1339 P_FRINGE_BENEFITS_2 => l_FRINGE_BENEFITS_2,
1340 P_SUPERANNUATION_2 => l_SUPERANNUATION_2,
1341 P_CONTRACTOR_PAYMENTS_2 => l_CONTRACTOR_PAYMENTS_2,
1342 P_OTHER_TAXABLE_INCOME_2 => l_OTHER_TAXABLE_INCOME_2);
1343
1344 /* start 14134526 - If new state balances are used */
1345 IF (l_SALARIES_WAGES_2 + l_COMMISSION_2 + l_BONUS_ALLOWANCES_2 + l_DIRECTOR_FEES_2 +
1346 l_FRINGE_BENEFITS_2 + l_SUPERANNUATION_2 + l_CONTRACTOR_PAYMENTS_2 + l_OTHER_TAXABLE_INCOME_2) <> 0 THEN
1347 l_SALARIES_WAGES := l_SALARIES_WAGES_2;
1348 l_COMMISSION := l_COMMISSION_2;
1349 l_BONUS_ALLOWANCES := l_BONUS_ALLOWANCES_2;
1350 l_DIRECTOR_FEES := l_DIRECTOR_FEES_2;
1351 l_FRINGE_BENEFITS := l_FRINGE_BENEFITS_2;
1352 l_SUPERANNUATION := l_SUPERANNUATION_2;
1353 l_CONTRACTOR_PAYMENTS := l_CONTRACTOR_PAYMENTS_2;
1354 l_OTHER_TAXABLE_INCOME := l_OTHER_TAXABLE_INCOME_2;
1355 END IF;
1356
1357 /* end 14134526 */
1358
1359
1360
1361 hr_utility.set_location('in BBR loop', 300);
1362 insert into pay_action_information(
1363 action_information_id,
1364 action_context_id,
1365 action_context_type,
1366 effective_date,
1367 source_id,
1368 tax_unit_id,
1369 action_information_category,
1370 assignment_id,
1371 action_information1,
1372 action_information2,
1373 action_information3,
1374 action_information4,
1375 action_information5,
1376 action_information6,
1377 action_information7,
1378 action_information8,
1379 action_information9)
1380 values(
1381 pay_action_information_s.nextval,
1382 g_arc_payroll_action_id,
1383 'PA',
1384 p_effective_date,
1385 null,
1386 l_tax_unit_id,
1387 'AU_PAYROLL_TAX_EMPLOYEE_DETAILS',
1388 l_assignment_id,
1389 csr_rec.employee_number,
1390 csr_rec.person_id,
1391 l_full_name,
1392 csr_rec.state_desc,
1393 csr_rec.legal_employer,
1394 csr_rec.state_code,
1395 csr_rec.dge_state,
1396 csr_rec.dge_legal_employer,
1397 csr_rec.dge_group_name);
1398
1399
1400
1401
1402 --
1403 -- Insert the balance data into pay_action_information table
1404 -- This Direct Insert statement is for Performance Reasons.
1405 --
1406 insert into pay_action_information (
1407 action_information_id,
1408 action_context_id,
1409 action_context_type,
1410 effective_date,
1411 source_id,
1412 tax_unit_id,
1413 assignment_id,
1414 action_information_category,
1415 action_information1,
1416 action_information2,
1417 action_information3,
1418 action_information4,
1419 action_information5,
1420 action_information6,
1421 action_information7,
1422 action_information8,
1423 action_information9,
1424 action_information10,
1425 action_information11,
1426 action_information12,
1427 action_information13,
1428 action_information14,
1429 action_information15) /* 4731692 */
1430 values (
1431 pay_action_information_s.nextval,
1432 p_assignment_action_id,
1433 'AAP',
1434 p_effective_date,
1435 null,
1436 l_tax_unit_id,
1437 l_assignment_id,
1438 'AU_PAYROLL_TAX_BALANCE_DETAILS',
1439 l_salaries_wages,
1440 l_commission,
1441 l_bonus_allowances,
1442 l_director_fees,
1443 l_termination_payments,
1444 l_eligible_term_payments,
1445 l_Fringe_Benefits,
1446 l_Superannuation,
1447 l_Contractor_payments,
1448 l_Other_taxable_Income,
1449 l_Taxable_Income,
1450 l_max_action_sequence,
1451 g_le_taxable_income, /* 4713372 */
1452 g_count,
1453 csr_rec.state_code); /* 4731692 */
1454
1455 END LOOP; /* End of assignments r employee */
1456
1457 end archive_code;
1458
1459 procedure spawn_archive_reports
1460 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type)
1461 is
1462 l_count number;
1463 ps_request_id NUMBER;
1464 l_print_style VARCHAR2(2);
1465 l_report_name VARCHAR2(30);
1466 l_short_report_name VARCHAR2(30);
1467 l_print_together VARCHAR2(80);
1468 l_print_return BOOLEAN;
1469 l_procedure varchar2(50);
1470 request_error varchar2(2000);
1471 e_submit_error exception;
1472 err_num number;
1473 err_msg varchar2(2000);
1474 l_xml_options BOOLEAN; /* Bug 6839263 */
1475 --------------------------------------------------------------------+
1476 -- Cursor : csr_params
1477 -- Description : Fetches User Parameters from Legislative_paramters
1478 -- column.
1479 --------------------------------------------------------------------+
1480
1481 CURSOR csr_report_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
1482 IS
1483 SELECT pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
1484 pay_core_utils.get_parameter('LE',legislative_parameters) legal_employer,
1485 to_date(pay_core_utils.get_parameter('PERIOD',legislative_parameters),'YYYY/MM/DD') period,
1486 to_date(pay_core_utils.get_parameter('SDATE',legislative_parameters),'YYYY/MM/DD') start_date,
1487 to_date(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD') end_date,
1488 pay_core_utils.get_parameter('TAX_STATE',legislative_parameters) Tax_state,
1489 pay_core_utils.get_parameter('REP_TYPE',legislative_parameters)report_type,
1490 pay_core_utils.get_parameter('REP_NAME',legislative_parameters)report_name,
1491 pay_core_utils.get_parameter('ACTT',legislative_parameters)act_override_threshold,
1492 pay_core_utils.get_parameter('VICT',legislative_parameters)vic_override_threshold,
1493 pay_core_utils.get_parameter('NSWT',legislative_parameters)nsw_override_threshold,
1494 pay_core_utils.get_parameter('QLDT',legislative_parameters)qld_override_threshold,
1495 pay_core_utils.get_parameter('WAT',legislative_parameters)wa_override_threshold,
1496 pay_core_utils.get_parameter('NTT',legislative_parameters)nt_override_threshold,
1497 pay_core_utils.get_parameter('SAT',legislative_parameters)sa_override_threshold,
1498 pay_core_utils.get_parameter('TAST',legislative_parameters)tas_override_threshold,
1499 pay_core_utils.get_parameter('OUTPUT_TYPE',legislative_parameters) p_output_type
1500 FROM pay_payroll_actions ppa
1501 WHERE ppa.payroll_action_id = c_payroll_action_id;
1502
1503
1504
1505 cursor csr_get_report_name(c_payroll_Action_id pay_payroll_actions.payroll_action_id%TYPE) is
1506 select pay_core_utils.get_parameter('REP_NAME',legislative_parameters)
1507 from pay_payroll_actions ppa
1508 where ppa.payroll_Action_id = c_payroll_Action_id;
1509
1510 cursor csr_get_print_options(p_payroll_action_id NUMBER) IS
1511 SELECT printer,
1512 print_style,
1513 decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output
1514 ,number_of_copies
1515 FROM pay_payroll_actions pact,
1516 fnd_concurrent_requests fcr
1517 WHERE fcr.request_id = pact.request_id
1518 AND pact.payroll_action_id = p_payroll_action_id;
1519
1520
1521 rec_print_options csr_get_print_options%ROWTYPE;
1522
1523 l_parameters csr_report_params%ROWTYPE; /* Bug 6839263 */
1524
1525 Begin
1526 l_count :=0;
1527 ps_request_id :=-1;
1528 g_debug :=hr_utility.debug_enabled ;
1529
1530
1531 if g_debug then
1532 g_package := 'pay_au_tax_rep_pkg.' ;
1533 l_procedure := g_package||' spawn_archive_reports';
1534 hr_utility.set_location('Entering '||l_procedure,999);
1535 end if;
1536
1537 -- Set User Parameters for Report.
1538 open csr_get_report_name(p_payroll_action_id);
1539 fetch csr_get_report_name into l_report_name;
1540 close csr_get_report_name;
1541
1542 open csr_report_params(p_payroll_action_id);
1543 fetch csr_report_params into l_parameters;
1544 close csr_report_params;
1545
1546 /* Start 6839263 */
1547 IF l_parameters.p_output_type = 'XML_PDF'
1548 THEN
1549 l_short_report_name := 'PYAUPYL_XML';
1550
1551 l_xml_options := fnd_request.add_layout
1552 (template_appl_name => 'PAY',
1553 template_code => 'PYAUPYL_XML',
1554 template_language => 'en',
1555 template_territory => 'US',
1556 output_format => 'PDF');
1557
1558 ELSE
1559 l_short_report_name := 'PYAUPYL';
1560 END IF;
1561 /* End 6839263 */
1562
1563 if g_debug then
1564 hr_utility.set_location('p_payroll_action_id.........= ' || p_payroll_action_id,30);
1565 hr_utility.set_location('g_parameters.business_group_id.........= ' || l_parameters.business_group_id,30);
1566 hr_utility.set_location('g_parameters.legal_employer.........= ' || l_parameters.legal_employer,30);
1567 hr_utility.set_location('g_parameters.start_date..............= ' || l_parameters.start_date,30);
1568 hr_utility.set_location('g_parameters.end_date................= ' || l_parameters.end_date,30);
1569 hr_utility.set_location('g_parameters.tax_state.........= ' || l_parameters.tax_state,30);
1570 hr_utility.set_location('g_parameters.report_type..........= '||l_parameters.report_type,30);
1571 hr_utility.set_location('g_parameters.act_threshold..........= '||l_parameters.act_override_threshold,30);
1572 hr_utility.set_location('g_parameters.vic_threshold..........= '||l_parameters.vic_override_threshold,30);
1573 hr_utility.set_location('g_parameters.qld_threshold..........= '||l_parameters.qld_override_threshold,30);
1574 hr_utility.set_location('g_parameters.nsw_threshold..........= '||l_parameters.nsw_override_threshold,30);
1575 hr_utility.set_location('g_parameters.tas_threshold..........= '||l_parameters.tas_override_threshold,30);
1576 hr_utility.set_location('g_parameters.wa_threshold..........= '||l_parameters.wa_override_threshold,30);
1577 hr_utility.set_location('g_parameters.sa_threshold..........= '||l_parameters.sa_override_threshold,30);
1578 hr_utility.set_location('g_parameters.nt_threshold..........= '||l_parameters.nt_override_threshold,30);
1579 hr_utility.set_location('Output Type = '||l_parameters.p_output_type,30);
1580 end if;
1581
1582
1583 OPEN csr_get_print_options(p_payroll_action_id);
1584 FETCH csr_get_print_options INTO rec_print_options;
1585 CLOSE csr_get_print_options;
1586 --
1587 l_print_together := nvl(fnd_profile.value('CONC_PRINT_TOGETHER'), 'N');
1588 --
1589 -- Set printer options
1590 l_print_return := fnd_request.set_print_options
1591 (printer => rec_print_options.printer,
1592 style => rec_print_options.print_style,
1593 copies => rec_print_options.number_of_copies,/* Bug 4116833*/
1594 save_output => hr_general.char_to_bool(rec_print_options.save_output),
1595 print_together => l_print_together);
1596 -- Submit report
1597 if g_debug then
1598 hr_utility.set_location('payroll_action id '|| p_payroll_action_id,25);
1599 end if;
1600
1601 ps_request_id := fnd_request.submit_request
1602 ('PAY',
1603 l_short_report_name,
1604 null,
1605 null,
1606 false,
1607 'P_PAYROLL_ACTION_ID=' || to_char(p_payroll_action_id),
1608 'P_BUSINESS_GROUP_ID='||to_char(l_parameters.business_group_id),
1609 'P_LEGAL_EMPLOYER='||to_char(l_parameters.legal_employer),
1610 'P_PERIOD='||to_char(l_parameters.period,'YYYY/MM/DD'),
1611 'P_START_DATE='||to_char(l_parameters.start_date,'YYYY/MM/DD'),
1612 'P_END_DATE='||to_char(l_parameters.end_date,'YYYY/MM/DD'),
1613 'P_TAX_STATE=' || l_parameters.tax_state,
1614 'P_ACT=' || l_parameters.act_override_threshold,
1615 'P_QLD=' || l_parameters.qld_override_threshold,
1616 'P_SA=' || l_parameters.sa_override_threshold,
1617 'P_TAS=' || l_parameters.tas_override_threshold,
1618 'P_VIC=' || l_parameters.vic_override_threshold,
1619 'P_WA=' || l_parameters.wa_override_threshold,
1620 'P_NSW=' || l_parameters.nsw_override_threshold,
1621 'P_NT=' || l_parameters.nt_override_threshold,
1622 'P_REPORT_TYPE='||l_parameters.report_type,
1623 'P_REPORT_NAME=' || l_report_name,
1624 'BLANKPAGES=NO',NULL,NULL,
1625 NULL, NULL, NULL, NULL, NULL, NULL,
1626 NULL, NULL, NULL, NULL, NULL, NULL,
1627 NULL, NULL, NULL, NULL, NULL, NULL,
1628 NULL, NULL, NULL, NULL, NULL, NULL,
1629 NULL, NULL, NULL, NULL, NULL, NULL,
1630 NULL, NULL, NULL, NULL, NULL, NULL,
1631 NULL, NULL, NULL, NULL, NULL, NULL,
1632 NULL, NULL, NULL, NULL, NULL, NULL,
1633 NULL, NULL, NULL, NULL, NULL, NULL,
1634 NULL, NULL, NULL, NULL, NULL, NULL,
1635 NULL, NULL, NULL, NULL, NULL, NULL,
1636 NULL, NULL, NULL, NULL, NULL, NULL,
1637 NULL, NULL, NULL, NULL
1638 );
1639
1640
1641 request_error := fnd_message.get;
1642
1643 if g_debug then
1644 hr_utility.set_location('ps_request ' || ps_request_id, 35);
1645
1646 hr_utility.set_location('After calling report',24);
1647
1648 end if;
1649 exception
1650 when others then
1651 err_num := SQLCODE;
1652 err_msg := substr(sqlerrm,1,100);
1653
1654 hr_utility.set_location('erro_msg ' || err_msg, 200);
1655
1656 end spawn_archive_reports;
1657
1658
1659
1660
1661 end pay_au_paytax_pkg;