1 package body pay_au_recon_summary as
2 /* $Header: pyauprec.pkb 120.18.12010000.6 2008/08/06 06:50:11 ubhat ship $*/
3
4 /*
5 *** ------------------------------------------------------------------------+
6 *** Program: pay_au_recon_summary (Package Body)
7 ***
8 *** Change History
9 ***
10 *** Date Changed By Version Description of Change
11 *** --------- ---------- ------- ----------------------------------------+
12 *** 07 APR 03 apunekar 1.1 Initial version
13 *** 05 MAY 03 apunekar 1.2 Bug2855658 - Fixed for Retro Allowances
14 *** 20 May 03 Ragovind 1.8 Bug#2819479 - ETP Pre/Post Enhancement.
15 *** Flag to check whether this function called by Termination FOrm.
16 *** 29 May 2003 apunekar 1.9 Bug2920725 Corrected base tables to support security model
17 *** 10 Jun 2003 Ragovind 1.10 Bug#2972687 - Modified the cursor Get_Allowance_Balances for Performance
18 *** 18 Jun 2003 Ragovind 1.11 Bug#3004966 - Added functions for performance improvement of Reconciliation Report.
19 *** 20 Jun 03 Ragovind 1.12 Bug#3004966 - Fix for Performance Improvement
20 *** 20 Jun 03 Ragovind 1.13 Bug#3004966 - Added check g_asg_ids_t.exists(p_assignment_id) to function check_asgid
21 *** 21 Jul 03 Apunekar 1.14 Bug#3034792 - Copied fixes from branch 115.8.11511.5 into mainline. Added all payment summary fixes.
22 Base tables used instead of secured views to sync with archive code.
23 *** 23 Jul 03 Nanuradh 1.15 Bug#2984390 - Added an extra parameter to the function call etp_prepost_ratios - ETP Pre/post Enhancement
24 *** 13 Aug 03 Nanuradh 1.16 Bug#3095923 - Modified the function Total_Lump_Sum_E_Payments to get the value of g_lump_sum_e
25 *** If single Lump sum E payment is less than 400, then the amount is subtracted from Lump sum E.
26 *** 21 Aug 03 punmehta 115.18 Bug#3095923 - Modified the Cursor c_get_pay_earned_date to fetch effective_date instead of date_earned
27 *** 22-AUG-03 punmehta 115.19 Bug#3095923 - Modified the Cursor name c_get_pay_earned_date
28 *** to c_get_pay_effective_date and variable name of date_earned to effective_date
29 *** 09-OCT-03 Ragvoind 115.20 Bug#3034189 - Removed the usage of index INDEX(rppa pay_payroll_actions_pk) from cursor c_asgids.
30 *** 20-OCT-03 punmehta 115.21 Bug#3193479 - Implemented Batch Balance Retrieval. for that created new function to populate
31 *** global plsql table and other functions gets the balance value from this function.
32 *** 21-OCT-03 punmehta 115.22 Bug#3193479 - Added check for g_debug before tracing
33 *** 21-OCT-03 punmehta 115.23 Bug#3193479 - Modified OUT with OUT NOCOPY
34 *** 23-OCT-03 punmehta 115.24 Bug#3213539 - Modified the function get_total_fbt
35 *** 27-OCT-03 avenkatk 115.25 Bug#3215789 - Modified function Total_Tax_deductions
36 *** 27-OCT-03 vgsriniv 115.26 Bug#3215982 - Removed the global declaration of g_index and also
37 *** removed initialization of g_index from the procedure get_value_bbr
38 *** 22-Nov-03 punmehta 115.27 Bug#3263659 - Modified c_asg_ids cursor to check for terminated date in last year.
39 *** 06-Feb-04 punmehta 115.28 Bug#3245909 - Modified c_get_pay_effective_date cursor to fetch Dates for only master assignment action.
40 *** 10-Feb-04 punmehta 115.29 Bug#3098367 - Added check to set a flag as 'NO' if all balances are zero.
41 *** 11-Feb-04 punmehta 115.30 Bug#3098367 - Added default value for flag.
42 *** 23-Mar-04 Ragovind 115.31 Bug#3525563 - Added IF condition to call pay_balance_pkg.get_value when l_max_asg_action_id is not null
43 *** 28-Mar-04 srrajago 115.32 Bug#3186840 - Introduced a parameter 'l_le_level' in 'populate_bal_ids' so that it can populate
44 *** defined_bal_ids of '_ASG_LE_YTD' or '_ASG_YTD' depending on the parameter value passed.
45 *** Introduced procedures 'populate_group_def_bal_ids','get_group_values_bbr',
46 *** 'get_assgt_curr_term_values_bbr' and 'get_group_assgt_values_bbr'.New procedures will be used by the
47 *** report PYAURECP-Pay Rec Payment Summary report.
48 *** 29-Mar-04 srrajago 115.33 Bug#3186840 - Removed the procedure 'get_group_assgt_values_bbr'.
49 *** 31-Mar-04 srrajago 115.34 Bug#3186840 - Procedure 'populate_group_def_bal_ids' modified to include balances 'Earnings_Total',
50 *** 'Leave Payments Marginal','CDEP' and 'Other Income'. Procedure 'get_group_assgt_values_bbr' modified
51 *** to fetch the above mentioned group level balances. References to these balances at assignment level
52 *** have been removed in 'get_assgt_curr_term_values_bbr'.
53 *** 11-Jun-04 punmehta 115.38 Bug#3686549 - Added a new parameter and logic to take more then 1 yrs term employees
54 *** 11-Jun-04 punmehta 115.39 Bug#3686549 - Modified for GSCC warnings
55 *** 21-Jun-04 punmehta 115.40 Bug#3693034 - Modified the max assignmtn_aciotn cursor in fbt procedure
56 *** 28-Jun-04 srrajago 115.41 Bug#3603495 - Performance Fix - Cursors 'c_get_pay_effective_date' and 'c_max_asg_action_id'
57 *** modified.
58 *** 01-Jul-04 punmehta 115.42 Bug#3728357 - Performance Fix
59 *** 03-Jul-04 srrajago 115.44 Reverted back the fix in 115.43. Also, modified the cursor 'Get_Allowances_Balances' to be in sink
60 *** with the one in the Payment Summary archive package.(ie)Bug#2972687 fix removed.
61 *** 07-Jul-04 punmehta 115.45 Bug#3749530 - Added archival model to archive assignment_actions for performance
62 *** 09-Jul-04 punmehta 115.46 Bug#3749530 - Added a new cursor to handle single assignment
63 *** 09-Aug-04 abhkumar 115.47 Bug#2610141 - Legal Employer enhancement changes.
64 *** 13-Aug-04 abhkumar 115.48 Bug#2610141 - Modified code to calculate correct lump sum e if there are two runs in the same period
65 *** 22-Nov-04 avenkatk 115.49 Bug#4015571 - Function get_total_fbt - Modified cursor c_max_asg_action_id.
66 *** 22-Nov-04 avenkatk 115.50 Bug#4015571 - Resolved GSCC Errors
67 *** 24-Nov-04 srrajago 115.51 Bug#3872211 - Modified cursors 'c_asgids' and 'c_asgid_only' to handle Payrolls Updation. (This is the same fix
68 *** made for Payment Summary - Refer Bug: 3815301)
69 *** 30-Dec-04 avenkatk 115.52 Bug#3899641 - Added Functional Dependancy Comment
70 *** 13-Jan-05 avenkatk 115.53 Bug#4116833 - Set the No of copies for report to be read from the Archive Request.
71 *** 18-Jan-05 hnainani 115.54 Bug#4015082 Workplace Giving Deductions
72 *** 07-Feb-05 ksingla 115.56 Bug#4161460 Modified the cursor get_allowance_balances
73 *** 09-Feb-05 ksingla 115.57 Bug#4173809 Modified the cursor c_eit_updated for Manual PS issues.
74 *** 12 Feb 05 abhargav 115.58 bug#4174037 Modified the cursor get_allowance_balances to avoid the unnecessary get_value() call.
75 *** 17 Feb 05 abhkumar 115.59 Bug#4161460 Rolled back the changes made in version 115.56.
76 *** 05 Apr 05 ksingla 115.60 Bug#4256486 Modified the etp_code for performance.
77 *** 12 Apr 05 avenkatk 115.61 Bug#4256506 Changed c_max_asg_action_id in procedure get_total_fbt for performance.
78 *** 18 Apr 05 ksingla 115.62 Bug#4278272 Changed the cursor get_allowance_balances for performance issues.
79 *** 19 Apr 05 ksingla 115.63 Bug#4278407 Changed the cursor c_get_details to improve performance.
80 *** 22 Apr 05 ksingla 115.64 Bug#4177679 Added a new paramter to the function call etp_prepost_ratios.
81 *** 25 Apr 05 ksingla 115.65 Bug#4278272 Rolled back the changes done in version 115.62.
82 *** 05 May 05 abhkumar 115.66 Bug#4377367 Added join in the cursor c_asgids to archive the end-dated employees.
83 *** 09 JUl 05 abhargav 115.67 Bug#4363057 Changes due to Retro Tax enhancement.
84 *** 2 AUG 05 hnainani 115.68 Bug#4478752 Added quotes to -999 to allow for Character values in flexfield.
85 *** 02-OCT-05 abhkumar 115.70 Bug#4688800 Modified assignment action code to pick those employees who do have payroll attached
86 at start of the financial year but not at the end of financial year.
87 *** 02-DEC-05 abhkumar 115.71 Bug#4701566 Modified the cursor get_allowance_balances to get allowance value for end-dated
88 employees and also improve the performance of the query.
89 *** 06-DEC-05 abhkumar 115.72 Bug#4863149 Modified the code to raise error message when there is no defined balance id for the allowance balance.
90 *** 09-DEC-05 ksingla 115.73 Bug#4872594 Removed round from Pre and post Jul values.
91 *** 15-DEC-05 ksingla 115.74 Bug#4872594 Put round off upto 2 decimal places.
92 *** 15-DEC-05 ksingla 115.75 Bug#4888097 Inititalise allowance variables to prevent picking value for previous employees when the current employee
93 *** being processed doesn't has a allowance.
94 *** 20-JUL-06 priupadh 115.76 Bug#5397790 In Cursor etp_code added a join of period_of_service_id
95 *** 19-Dec-06 ksingla 115.77 Bug#5708255 Added code to get value of global FBT_THRESHOLD
96 *** 27-Dec-06 ksingla 115.78 Bug#5708255 Added to_number to all occurrences of g_fbt_threshold
97 *** 8-Jan-06 ksingla 115.79 Bug#5743196 Added nvl to cursor c_allowance_balance
98 *** 13-Feb-06 priupadh 115.80 N/A Version for restoring Triple Maintanence between 11i-->R12(Branch) -->R12(MainLine)
99 *** 24-May-06 priupadh 115.81 Bug#6069614 Removed the if conditions which checks the death benefit type other then 'Dependent'
100 *** 06-Jun-06 priupadh 115.82 Bug#6112527 Added the condition removed for Bug#6069614 with check that only archive termination type death/dependent if Fin Year is 2007/2008 or greater.
101 *** 20-Mar-08 avenkatk 115.84 Bug#6839263 Added changes for support of XML migrated reports in R12.1
102 ** 21-Mar-08 avenkatk 115.85 Bug#6839263 Added Logic to set the OPP Template options for PDF output
103 *** 26-May-08 bkeshary 115.86 Bug#7030285 Modified the calculation for Assessable Income
104 *** 26-May-08 bkeshary 115.87 Bug#7030285 Added File Change History
105 *** 18-Jun-08 avenkatk 115.88 Bug#7138494 Added Changes for RANGE_PERSON_ID
106 *** 18-Jun-08 avenkatk 115.89 Bug#7138494 Modified Allowance Cursor for peformance
107 *** 01-Jul-08 avenkatk 115.90 Bug#7138494 Modified Allowance Cursor - Added ORDERED HINT
108 */
109
110
111 g_debug boolean; --Bug#3193479
112 g_pre01jul1983_value number;
113 g_post30jun1983_value number;
114 g_etp_gross number;
115 g_etp_tax number;
116 g_assessable number;
117 g_lump_sum_e number;
118 g_total_other_income number;
119 g_total_cdep number;
120 g_total_allowance number;
121 g_total_fbt number;
122 g_total_gross number;
123 g_business_group_id hr_organization_units.organization_id%type;
124 x number;
125 g_total_workplace number; /*4015082 */
126
127 g_balance_type_tab g_bal_type_tab;
128
129 g_fbt_threshold ff_globals_f.global_value%TYPE ; /* Bug 5708255 */
130
131 Function total_gross
132 return number is
133 l_total_earnings_asg_ytd number;
134 l_allowance_total number;
135 l_cdep_asg_ytd number;
136 l_other_income_asg_ytd number;
137 l_lump_sum_e_payments_asg_ytd number;
138 l_total_gross number;
139
140 begin
141
142 -- Bug: 3186840 Check against the global Dimension level value included to return the correct value.
143 IF (g_bal_dim_level = 'N') THEN
144 l_total_earnings_asg_ytd:= get_bal_value_new(g_db_id_et) + get_bal_value_new(g_db_id_lpm);
145 -- Bug# 3193479
146 END IF;
147 l_total_gross:=l_total_earnings_asg_ytd-greatest(g_total_allowance,0)-g_total_cdep-g_total_other_income
148 -g_lump_sum_e + g_total_workplace ; /*4015082 */
149 g_total_gross := l_total_gross;
150 return l_total_gross;
151
152 end total_gross;
153
154
155 Function get_total_allowances(c_year_start DATE,
156 c_year_end DATE,
157 c_assignment_id pay_assignment_actions.assignment_id%type,
158 c_assignment_action_id pay_assignment_actions.assignment_action_id%type,
159 c_registered_employer NUMBER,
160 c_message OUT NOCOPY VARCHAR2) --2610141
161 return number is
162
163
164 /*bug#4174037 Modified to avoid the unnecessary get_value() call.*/
165 /*bug 4701566 - Modified the subquery of the below cursor to get allowance value for end-dated
166 employees and also improve the performance of the query.*/
167
168 /* CURSOR Get_Allowance_Balances(c_year_start DATE,
169 c_year_end DATE,
170 c_assignment_id pay_assignment_actions.assignment_id%type)
171
172 IS
173
174 select balance_name,
175 pay_balance_pkg.get_value(def_id,
176 c_assignment_action_id,
177 c_registered_employer,
178 null,null,null,null) balance_value
179 from
180 ( SELECT DISTINCT NVL(pbt.reporting_name,pbt.balance_name) balance_name,
181 pdb.defined_balance_id def_id
182 FROM pay_element_types_f pet
183 ,per_all_assignments_f paa
184 ,pay_balance_types pbt
185 ,pay_defined_balances pdb
186 ,pay_balance_dimensions pbd
187 -- ,per_periods_of_service pps
188 ,pay_payroll_actions ppa
189 ,pay_assignment_actions pac
190 ,pay_run_results prr
191 WHERE pac.assignment_id = c_assignment_id
192 AND pac.tax_unit_id = c_registered_employer --2610141
193 AND paa.assignment_id = pac.assignment_id
194 AND pac.payroll_action_id = ppa.payroll_Action_id
195 AND ppa.effective_date
196 BETWEEN c_year_start AND c_year_end
197 and ppa.payroll_id = paa.payroll_id
198 and ppa.action_type in ('Q','R','B','I','V')
199 AND pac.assignment_action_id = prr.assignment_Action_id
200 AND prr.element_type_id = pet.element_type_id
201 AND pet.element_information_category = 'AU_EARNINGS'
202 AND pet.element_information1 = 'Y'
203 AND pet.element_information2 = pbt.balance_type_id
204 AND pbt.balance_type_id = pdb.balance_type_id
205 AND pbd.balance_dimension_id = pdb.balance_dimension_id
206 AND pbd.dimension_name = '_ASG_LE_YTD' --2610141
207 AND pbd.legislation_code = 'AU'
208 -- AND pps.PERIOD_OF_SERVICE_ID = paa.PERIOD_OF_SERVICE_ID
209 -- AND NVL(pps.actual_termination_date,c_year_end)
210 AND ppa.effective_date
211 BETWEEN paa.effective_start_date AND paa.effective_end_date
212 AND ppa.date_earned
213 BETWEEN pet.effective_start_date AND pet.effective_end_date
214 ) ORDER BY 2 DESC ;*/
215
216
217 /*Bug#4863149 - Introduced a new cursor to get all the allowance balance type id's*/
218 /* Bug 7138494 - Changed rom Date Earned to Effective date for better
219 performance - Added ORDERED HINT */
220 cursor get_allowance_balance_types
221 (c_assignment_id pay_assignment_actions.assignment_id%type
222 ,c_start_date date
223 ,c_end_date date
224 ,c_tax_unit_id pay_assignment_actions.tax_unit_id%type)
225 IS
226 SELECT /*+ ORDERED */
227 DISTINCT pet.element_information2 balance_type_id
228 FROM per_all_assignments_f paf
229 ,pay_payroll_actions ppa
230 ,pay_assignment_actions paa
231 ,pay_run_results prr
232 ,pay_element_types_f pet
233 WHERE paa.assignment_id = c_assignment_id
234 AND paf.business_group_id = ppa.business_group_id
235 AND ppa.effective_date BETWEEN c_start_date AND c_end_date
236 AND ppa.action_type in ('R','Q','B','V','I')
237 AND ppa.payroll_id = paf.payroll_id
238 AND paa.assignment_id = paf.assignment_id
239 AND paa.assignment_action_id = prr.assignment_Action_id
240 AND prr.element_type_id = pet.element_type_id
241 AND prr.status in ('P','PA')
242 AND paa.tax_unit_id = c_tax_unit_id
243 AND paa.action_status ='C'
244 AND pet.element_information_category = 'AU_EARNINGS'
245 AND pet.element_information1 = 'Y'
246 AND paa.payroll_action_id = ppa.payroll_Action_id
247 AND ppa.effective_date
248 BETWEEN paf.effective_start_date AND paf.effective_end_date
249 AND ppa.effective_date
250 BETWEEN pet.effective_start_date AND pet.effective_end_date;
251
252 /*Bug#4863149 - Introduced a new cursor to get defined balance id's for allowance balances*/
253 cursor c_allowance_balance(c_balance_type_id pay_balance_types.balance_type_id%type)
254 is
255 select nvl(pbt.reporting_name,pbt.balance_name) balance_name, /* Bug 5743196 -Added nvl */
256 pdb.defined_balance_id
257 from pay_balance_types pbt
258 ,pay_defined_balances pdb
259 ,pay_balance_dimensions pbd
260 where pbt.balance_type_id = c_balance_type_id
261 and pdb.balance_type_id = pbt.balance_type_id
262 and pdb.balance_dimension_id = pbd.balance_dimension_id
263 and pbd.dimension_name = '_ASG_LE_YTD'
264 and pbd.legislation_code ='AU';
265
266
267 -- rec_Allowance_Balances Get_Allowance_Balances%ROWTYPE;
268
269
270 t_allowance_balance pay_au_payment_summary.t_allowance_balance%TYPE;
271
272 sum_various Number :=0;
273 counter Number := 1;
274 l_total_allowance number;
275 l_counter NUMBER;
276 allow_balance_value_tab pay_balance_pkg.t_balance_value_tab; /* 4863149 */
277 allow_context_table pay_balance_pkg.t_context_tab; /* 4863149 */
278 allow_result_table pay_balance_pkg.t_detailed_bal_out_tab; /* 4863149 */
279 i number;
280
281 begin
282
283 /*Bug#4863149 - Modification starts*/
284
285 l_counter := 0;
286 g_debug := hr_utility.debug_enabled ;
287 g_balance_type_tab.delete;
288
289
290 FOR csr_bal in get_allowance_balance_types(c_assignment_id
291 ,c_year_start
292 ,c_year_end
293 ,c_registered_employer)
294 LOOP
295 l_counter := l_counter + 1 ;
296 g_balance_type_tab(l_counter) := to_number(csr_bal.balance_type_id);
297 END LOOP;
298
299
300 IF l_counter > 0 THEN
301
302 for i in g_balance_type_tab.FIRST..g_balance_type_tab.LAST
303 loop
304 if g_debug then
305 hr_utility.set_location('g_balance_type_tab(i) '||g_balance_type_tab(i),700);
306 end if;
307
308 open c_allowance_balance(g_balance_type_tab(i));
309 fetch c_allowance_balance into t_allowance_balance(i).balance_name, allow_balance_value_tab(i).defined_balance_id; /* 4863149 */
310 IF c_allowance_balance%NOTFOUND THEN
311 hr_utility.set_location('Defined balanced id not found for balance type id :'||g_balance_type_tab(i),700); /* 4863149 */
312 c_message := 'Allowance Balance Type ' || g_balance_type_tab(i) || ' not associated with dimension _ASG_LE_YTD';
313 RETURN 0;
314 END IF;
315 close c_allowance_balance;
316
317 end loop;
318
319 allow_result_table.delete;
320 allow_context_table.delete;
321
322 allow_context_table(1).tax_unit_id := c_registered_employer;
323
324 if (c_assignment_action_id is NOT NULL)
325 then
326 pay_balance_pkg.get_value(p_assignment_action_id => c_assignment_action_id,
327 p_defined_balance_lst=>allow_balance_value_tab,
328 p_context_lst =>allow_context_table,
329 p_output_table=>allow_result_table);
330
331 else
332 if g_debug then
333 hr_utility.set_location('Assingment Action ID not found for Run in Year!!',300);
334 end if;
335 end if;
336
337 for i in g_balance_type_tab.FIRST..g_balance_type_tab.LAST
338 loop
339 if g_debug then
340 hr_utility.set_location('g_balance_type_tab(i) '||g_balance_type_tab(i),700);
341 end if;
342
343 t_allowance_balance(i).balance_value := allow_result_table(i).balance_value;
344
345 end loop;
346
347
348 /*Bug#4863149 - Modification Ends here*/
349
350 i:=pay_au_payment_summary.adjust_retro_allowances(t_allowance_balance
351 ,c_year_start
352 ,c_year_end
353 ,c_assignment_id
354 ,c_registered_employer --2610141
355 );
356 g_total_allowance:=0;
357
358 For i in 1..l_counter
359 LOOP
360 If t_allowance_balance.EXISTS(i) Then
361 g_total_allowance:=g_total_allowance+nvl(t_allowance_balance(i).balance_value,0);
362 End If;
363 END LOOP;
364
365 END IF;
366
367 return nvl(g_total_allowance,0);
368 end get_total_allowances;
369
370
371
372 Function get_total_fbt(c_year_start DATE,
373 c_assignment_id pay_assignment_actions.assignment_id%type,
374 p_tax_unit_id hr_all_organization_units.organization_id%TYPE,
375 c_fbt_rate ff_globals_f.global_value%TYPE,
376 c_ml_rate ff_globals_f.global_value%TYPE,
377 p_termination VARCHAR2)
378 return number is
379
380 l_total_fbt number;
381 l_reporting_amt number;
382 l_fbt_rate number;
383 l_medicare_levy number;
384 l_fbt_ratio number;
385 l_max_asg_action_id pay_assignment_actions.assignment_action_id%type;
386
387 /* Bug: 3603495 - Performance Fix - Introduced per_assignments_f and its joins in the following cursor */
388 /* Bug: 4015571 - Modified cursor c_max_asg_action_id - Modified action_type join in sub query
389 to restrict the max action_sequence fetch to types 'Q','R','B','I'
390 Bug: 4256506 - Changed cursor c_max_asg_action_id. Merged sub query to fetch max action sequemce in main query. Done for
391 better performance.
392 */
393 cursor c_max_asg_action_id (c_assignment_id per_all_assignments_f.assignment_id%TYPE,
394 c_business_group_id hr_all_organization_units.organization_id%TYPE,
395 c_tax_unit_id hr_all_organization_units.organization_id%TYPE,
396 c_year_start date,
397 c_year_end date ) is
398 select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
399 from pay_assignment_actions paa
400 , pay_payroll_actions ppa
401 , per_assignments_f paf
402 where paa.assignment_id = paf.assignment_id
403 and paf.assignment_id = c_assignment_id
404 and ppa.payroll_action_id = paa.payroll_action_id
405 and ppa.effective_date between c_year_start and c_year_end
406 and ppa.payroll_id = paf.payroll_id
407 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
408 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
409 and paa.action_status='C'
410 AND paa.tax_unit_id = c_tax_unit_id;
411
412
413 /* Bug 5708255 */
414 -------------------------------------------
415 -- Added cursor to get value of global FBT_THRESHOLD
416 --------------------------------------------
417 CURSOR c_get_fbt_global(c_year_end DATE)
418 IS
419 SELECT global_value
420 FROM ff_globals_f
421 WHERE global_name = 'FBT_THRESHOLD'
422 AND legislation_code = 'AU'
423 AND c_year_end BETWEEN effective_start_date
424 AND effective_end_date ;
425
426
427 begin
428
429
430 --- Bug#3213539-------------------------------------------
431 IF p_termination IS NULL THEN
432 open c_max_asg_action_id ( c_assignment_id, --Bug# 3193479
433 g_business_group_id,
434 p_tax_unit_id,
435 add_months(c_year_start,-3),
436 add_months(c_year_start,9)-1); --Bug3693034 - Modified to fetch action upto 31-Mar
437 fetch c_max_asg_action_id into l_max_asg_action_id;
438 close c_max_asg_action_id;
439 ELSE
440 open c_max_asg_action_id ( c_assignment_id, --Bug# 3193479
441 g_business_group_id,
442 p_tax_unit_id,
443 add_months(c_year_start,-3),
444 (c_year_start-1));
445 fetch c_max_asg_action_id into l_max_asg_action_id;
446 close c_max_asg_action_id;
447 END IF;
448
449 /* Bug#3525563 - Added IF condition to call pay_balance_pkg.get_value when l_max_asg_action_id is not null. */
450 IF l_max_asg_action_id is not null then
451 l_total_fbt := pay_balance_pkg.get_value(g_fbt_defined_balance_id,
452 l_max_asg_action_id,p_tax_unit_id, null,null,null,null);
453 ELSE
454 l_total_fbt := 0;
455 END IF;
456 /* End of Bug#3525563 */
457
458 /* Bug 5708255 */
459 open c_get_fbt_global (add_months(add_months(c_year_start,9)-1,-3)); /* Add_months included for bug 5333143 */
460 fetch c_get_fbt_global into g_fbt_threshold;
461 close c_get_fbt_global;
462
463
464 ------------End of Bug#3213539 ---------------------
465 IF l_total_fbt <= to_number(g_fbt_threshold) THEN /* Bug 5708255 */
466 l_total_fbt := 0;
467 END IF;
468
469 l_fbt_rate := to_number(c_fbt_rate);
470 l_medicare_levy :=to_number(c_ml_rate);
471
472 l_fbt_ratio:=1-(l_fbt_rate+l_medicare_levy);
473
474 if l_fbt_ratio <> 0 then
475 l_reporting_amt := l_total_fbt/l_fbt_ratio;
476 else
477 l_reporting_amt := 0;
478 end if;
479
480 l_reporting_amt := round(l_reporting_amt,2);
481
482 g_total_fbt := nvl(l_reporting_amt,0);
483 return g_total_fbt;
484 end get_total_fbt;
485
486
487
488
489
490 function get_total_cdep
491 return number is
492 begin
493
494 -- Bug: 3186840 Check against the global Dimension level value included to return the correct value.
495
496 IF (g_bal_dim_level = 'N') THEN
497 g_total_cdep := get_bal_value_new(g_db_id_cdep);--Bug# 3193479
498 ELSIF (g_bal_dim_level = 'G') THEN
499 g_total_cdep := g_result_group_details_table(10).balance_value;
500 END IF;
501 return g_total_cdep;
502
503 end get_total_cdep;
504
505 /* 4015082 */
506 function get_total_workplace
507 return number is
508 begin
509
510 -- Bug: 3186840 Check against the global Dimension level value included to return the correct value.
511
512 IF (g_bal_dim_level = 'N') THEN
513 g_total_workplace := get_bal_value_new(g_db_id_wgd);
514 ELSIF (g_bal_dim_level = 'G') THEN
515 g_total_workplace := g_result_group_details_table(12).balance_value;
516 END IF;
517 return g_total_workplace;
518
519 end get_total_workplace;
520
521 function Total_Lump_Sum_A_Payments
522 return number is
523 l_lump_sum_a number;
524 begin
525
526 -- Bug: 3186840 Check against the global Dimension level value included to return the correct value.
527
528 IF (g_bal_dim_level = 'N') THEN
529 l_lump_sum_a :=get_bal_value_new(g_db_id_lsap);--Bug# 3193479
530 ELSIF (g_bal_dim_level = 'G') THEN
531 l_lump_sum_a := g_result_group_details_table(1).balance_value;
532 END IF;
533
534 return l_lump_sum_a;
535 end Total_Lump_Sum_A_Payments;
536
537
538 function Total_Lump_Sum_B_Payments
539 return number is
540 l_lump_sum_b number;
541 begin
542
543 -- Bug: 3186840 Check against the global Dimension level value included to return the correct value.
544
545 IF (g_bal_dim_level = 'N') THEN
546 l_lump_sum_b := get_bal_value_new(g_db_id_lsbp);--Bug# 3193479
547 ELSIF (g_bal_dim_level = 'G') THEN
548 l_lump_sum_b := g_result_group_details_table(2).balance_value;
549 END IF;
550
551 return l_lump_sum_b;
552 end Total_Lump_Sum_B_Payments;
553
554
555 function Total_Lump_Sum_D_Payments
556 return number is
557 l_lump_sum_d number;
558 begin
559
560 -- Bug: 3186840 Check against the global Dimension level value included to return the correct value.
561
562 IF (g_bal_dim_level = 'N') THEN
563 l_lump_sum_d:= get_bal_value_new(g_db_id_lsdp);--Bug# 3193479
564 ELSIF (g_bal_dim_level = 'G') THEN
565 l_lump_sum_d := g_result_group_details_table(3).balance_value;
566 END IF;
567
568 return l_lump_sum_d;
569
570 end Total_Lump_Sum_D_Payments;
571
572
573 function Total_Lump_Sum_E_Payments(c_year_end DATE,
574 c_assignment_id pay_assignment_actions.assignment_id%type,
575 c_registered_employer NUMBER) --2610141
576 return number is
577 v_lump_sum_E_ptd number;
578 v_effective_date date; /* Bug#3095923 */
579 c_year_start date;
580 l_assignment_action_id number; --2610141
581
582 CURSOR c_single_lumpsum_E_payment(v_effective_date DATE,
583 v_assignment_id pay_assignment_actions.assignment_id%type,
584 v_assignment_action_id pay_assignment_actions.assignment_id%type) --2610141
585
586 IS
587 select pay_balance_pkg.get_value(pdb.defined_balance_id, v_assignment_action_id, c_registered_employer,null,null,null,null) --2610141
588 FROM pay_balance_types pbt,
589 pay_defined_balances pdb,
590 pay_balance_dimensions pbd
591 WHERE pbt.legislation_code = 'AU'
592 AND pbt.balance_name = 'Lump Sum E Payments'
593 AND pbt.balance_type_id = pdb.balance_type_id
594 AND pbd.balance_dimension_id = pdb.balance_dimension_id
595 AND pbd.dimension_name = '_ASG_LE_PTD';
596
597 /* Bug No:3603495 - Performance Fix - In the following cursor, introduced table per_assignments_f and its joins */
598 /*Bug 4363057 - Cursor has been modified so that the Lum Sum E Payments given to previous legal employers
599 can be taken into account while calculating payment summary gross.*/
600
601
602 CURSOR c_get_pay_effective_date(v_assignment_id pay_assignment_actions.assignment_id%type
603 ,v_year_start in DATE
604 ,v_year_end in DATE)
605 IS
606 select max(paa.assignment_action_id) /* Bug#3095923 , Bug 2610141*/
607 from per_assignments_f paf,
608 pay_payroll_Actions ppa,
609 pay_assignment_Actions paa,
610 per_time_periods ptp
611 where ppa.payroll_Action_id = paa.payroll_Action_id
612 and paa.assignment_id = v_assignment_id
613 and paf.assignment_id = paa.assignment_id
614 and action_type in ('Q','R','V')
615 AND (paa.source_action_id IS NULL
616 OR (paa.source_action_id IS NOT NULL AND ppa.run_type_id IS NULL)) /*Bug 4363057*/
617 and date_earned between v_year_start and v_year_end
618 and date_earned between paf.effective_start_date and paf.effective_end_date
619 and paa.tax_unit_id = c_registered_employer
620 AND ptp.time_period_id = ppa.time_period_id
621 GROUP BY ptp.time_period_id; --2610141
622
623
624 begin
625 c_year_start := to_date('01-07-'||to_char(to_number(to_char(c_year_end,'YYYY'))-1),'DD-MM-YYYY');
626
627 -- Bug: 3186840 Check against the global Dimension level value included to return the correct value.
628
629 IF (g_bal_dim_level = 'N') THEN
630 g_lump_sum_e:= get_bal_value_new(g_db_id_lsep);--Bug# 3193479
631 ELSIF (g_bal_dim_level = 'T') THEN
632 g_lump_sum_e := g_result_term_details_table(1).balance_value;
633 END IF;
634
635 if c_year_start >= to_date('01-07-2003','DD-MM-YYYY') then
636 if g_lump_sum_e <> 0 then
637 OPEN c_get_pay_effective_date(c_assignment_id
638 ,c_year_start
639 ,c_year_end) ;
640 LOOP
641 fetch c_get_pay_effective_date into l_assignment_action_id; --2610141
642 EXIT WHEN c_get_pay_effective_date%NOTFOUND;
643 open c_single_lumpsum_E_payment(v_effective_date,
644 c_assignment_id,
645 l_assignment_action_id);
646 fetch c_single_lumpsum_E_payment into v_lump_sum_E_ptd;
647 if v_lump_sum_E_ptd < 400 then /* Bug#3095923 */
648 g_lump_sum_e := g_lump_sum_e - v_lump_sum_E_ptd;
649 end if;
650 close c_single_lumpsum_E_payment;
651 END LOOP;
652 CLOSE c_get_pay_effective_date;
653 end if;
654 end if;
655 return g_lump_sum_e;
656
657 end Total_Lump_Sum_E_Payments;
658
659
660 function Total_Union_fees
661 return number is
662 l_union_fees number;
663 begin
664
665 -- Bug: 3186840 Check against the global Dimension level value included to return the correct value.
666
667 IF (g_bal_dim_level = 'N') THEN
668 l_union_fees:=get_bal_value_new(g_db_id_uf);--Bug# 3193479
669 ELSIF (g_bal_dim_level = 'G') THEN
670 l_union_fees := g_result_group_details_table(4).balance_value;
671 END IF;
672
673 return l_union_fees;
674
675 end Total_Union_fees;
676
677 /* Bug#3004966 - Modified the Logic for the function
678 for performance improvement. */
679 function Total_Tax_deductions
680 return number is
681
682 l_total_tax_ded number := 0;
683 i number;
684 l_temp number;
685
686 BEGIN --Bug# 3193479 -- Bug #3215789
687
688 -- Bug: 3186840 Check against the global Dimension level value included to return the correct value.
689
690 IF (g_bal_dim_level = 'N') THEN
691 l_total_tax_ded := get_bal_value_new(g_db_id_lscd) * (-1) + get_bal_value_new(g_db_id_td) + get_bal_value_new(g_db_id_ttd);
692 ELSIF (g_bal_dim_level = 'G') THEN
693 l_total_tax_ded := g_result_group_details_table(5).balance_value * (-1) + g_result_group_details_table(6).balance_value +
694 g_result_group_details_table(7).balance_value;
695 END IF;
696
697 return l_total_tax_ded;
698
699 end Total_Tax_deductions;
700
701 /* End of Bug#3004966 */
702
703
704 function Total_Other_Income
705 return number is
706 begin
707
708 -- Bug: 3186840 Check against the global Dimension level value included to return the correct value.
709
710 IF (g_bal_dim_level = 'N') THEN
711 g_total_other_income:=get_bal_value_new(g_db_id_oi);--Bug# 3193479
712 ELSIF (g_bal_dim_level = 'G') THEN
713 g_total_other_income := g_result_group_details_table(11).balance_value;
714 END IF;
715
716 return g_total_other_income;
717
718 end Total_Other_Income;
719
720
721
722 function Total_Invalidity_Payments
723 return number is
724 l_total_invalidity_payments number;
725 begin
726
727 -- Bug: 3186840 Check against the global Dimension level value included to return the correct value.
728
729 IF (g_bal_dim_level = 'N') THEN
730 l_total_invalidity_payments:=get_bal_value_new(g_db_id_ip);--Bug# 3193479
731 ELSIF (g_bal_dim_level = 'T') THEN
732 l_total_invalidity_payments := g_result_term_details_table(3).balance_value;
733 END IF;
734
735 return l_total_invalidity_payments;
736
737 end Total_Invalidity_Payments;
738
739 --Bug#3749530 - Function modified to set globals parmaters
740 function populate_bal_ids(p_le_level IN varchar2 DEFAULT NULL,
741 p_business_group_id hr_organization_units.organization_id%type,
742 p_lst_yr_term VARCHAR2 DEFAULT NULL ) return number
743 is
744
745 /* Bug#3004966 - Added two Balances 'Termination Deductions', 'Total_Tax_Deductions'*/
746 CURSOR c_bal_id (c_dimension_name pay_balance_dimensions.dimension_name%TYPE) IS
747 SELECT pbt.balance_name,pbt.balance_type_id,pdb.defined_balance_id
748 from pay_balance_types pbt,
749 pay_defined_balances pdb, --Bug# 3193479
750 pay_balance_dimensions pbd
751 where pbt.legislation_code = 'AU'
752 and pbt.balance_name in
753 ('CDEP','Earnings_Total','Lump Sum A Deductions',
754 'Lump Sum A Payments','Lump Sum B Deductions','Lump Sum B Payments',
755 'Lump Sum D Payments','Lump Sum E Payments','Total_Tax_Deductions',
756 'Other Income','Union Fees','Invalidity Payments','Lump Sum C Payments',
757 'Lump Sum C Deductions','Leave Payments Marginal','Termination Deductions'
758 , 'Workplace Giving Deductions' /* 4015082 */
759 )
760 AND pdb.balance_type_id = pbt.balance_type_id
761 AND pdb.balance_dimension_id = pbd.balance_dimension_id
762 AND pbd.legislation_code = 'AU'
763 AND pdb.legislation_code = 'AU'
764 AND pbd.dimension_name = c_dimension_name;
765
766 i number;
767 l_bal_name pay_balance_types.balance_name%type;
768 l_bal_id pay_balance_types.balance_type_id%type;
769 l_def_bal_id pay_defined_balances.defined_balance_id%TYPE; --Bug# 3193479
770 c_dimension_name pay_balance_dimensions.dimension_name%TYPE; -- Bug: 3186840
771 g_debug boolean;
772
773 Cursor c_fbt_balance IS --Bug#3749530
774 select pdb.defined_balance_id
775 from pay_balance_types pbt,
776 pay_defined_balances pdb,
777 pay_balance_dimensions pbd
778 where pbt.balance_name ='Fringe Benefits'
779 and pbt.balance_type_id = pdb.balance_type_id
780 and pdb.balance_dimension_id = pbd.balance_dimension_id
781 and pbd.legislation_code ='AU'
782 and pbd.dimension_name ='_ASG_LE_FBT_YTD' --2610141
783 and pbd.legislation_code = pbt.legislation_code
784 and pbd.legislation_code = pdb.legislation_code;
785
786 BEGIN
787 g_debug := hr_utility.debug_enabled;
788
789 ---Start of Bug#3749530------------------
790 g_business_group_id := p_business_group_id;
791 g_lst_yr_term := NVL(p_lst_yr_term,'Y'); --Bug3693034
792
793 -- Added for bug 3034189
794 If g_fbt_defined_balance_id = 0 OR g_fbt_defined_balance_id IS null Then
795 Open c_fbt_balance;
796 Fetch c_fbt_balance into g_fbt_defined_balance_id;
797 Close c_fbt_balance;
798 End if;
799 -- End of Bug#3749530-----------------
800
801 /* Start of Bug: 3186840 */
802
803 IF (p_le_level = 'Y') THEN
804 c_dimension_name := '_ASG_LE_YTD';
805 ELSE
806 c_dimension_name := '_ASG_YTD';
807 END IF;
808
809 IF g_debug THEN
810 hr_utility.trace('Parameter p_le_level value => ' || p_le_level);
811 hr_utility.trace('Dimension is => ' || c_dimension_name);
812 END IF;
813
814 /* End of Bug: 3186840 */
815
816 i:=1;
817 OPEN c_bal_id(c_dimension_name); -- Bug: 3186840
818 LOOP
819 FETCH c_bal_id INTO l_bal_name,l_bal_id,l_def_bal_id; --Bug# 3193479
820 EXIT WHEN c_bal_id%NOTFOUND;
821
822 IF l_bal_name = 'CDEP' THEN
823 g_db_id_cdep := l_def_bal_id;
824 ELSIF l_bal_name = 'Earnings_Total' THEN
825 g_db_id_et := l_def_bal_id;
826 ELSIF l_bal_name = 'Lump Sum A Deductions' THEN
827 g_db_id_lsad := l_def_bal_id;
828 ELSIF l_bal_name = 'Lump Sum A Payments' THEN
829 g_db_id_lsap := l_def_bal_id;
830 ELSIF l_bal_name = 'Lump Sum B Deductions' THEN
831 g_db_id_lsbd := l_def_bal_id;
832 ELSIF l_bal_name = 'Lump Sum B Payments' THEN
833 g_db_id_lsbp := l_def_bal_id;
834 ELSIF l_bal_name = 'Lump Sum D Payments' THEN
835 g_db_id_lsdp := l_def_bal_id;
836 ELSIF l_bal_name = 'Lump Sum E Payments' THEN
837 g_db_id_lsep := l_def_bal_id;
838 ELSIF l_bal_name = 'Total_Tax_Deductions' THEN
839 g_db_id_ttd := l_def_bal_id;
840 ELSIF l_bal_name = 'Other Income' THEN
841 g_db_id_oi := l_def_bal_id;
842 ELSIF l_bal_name = 'Union Fees' THEN
843 g_db_id_uf := l_def_bal_id;
844 ELSIF l_bal_name = 'Invalidity Payments' THEN
845 g_db_id_ip := l_def_bal_id;
846 ELSIF l_bal_name = 'Lump Sum C Payments' THEN
847 g_db_id_lscp := l_def_bal_id;
848 ELSIF l_bal_name = 'Lump Sum C Deductions' THEN
849 g_db_id_lscd := l_def_bal_id;
850 ELSIF l_bal_name = 'Leave Payments Marginal' THEN
851 g_db_id_lpm := l_def_bal_id;
852 ELSIF l_bal_name = 'Termination Deductions' THEN
853 g_db_id_td := l_def_bal_id;
854 ELSIF l_bal_name = 'Workplace Giving Deductions' THEN /* 4015082 */
855 g_db_id_wgd := l_def_bal_id;
856 END IF;
857 g_input_table(i).defined_balance_id :=l_def_bal_id;--Bug# 3193479
858 g_input_table(i).balance_value := NULL;
859
860 IF g_debug THEN
861 hr_utility.trace(i || ' Defined Balance id of ' || l_bal_name || '=> ' || g_input_table(i).defined_balance_id);
862 END IF;
863
864 i:=i+1;
865 END LOOP;
866 CLOSE c_bal_id;
867 ---Except Tax-Unit_id other Context table values are not required
868 g_context_table(1).jurisdiction_code := NULL;
869 g_context_table(1).source_id := NULL;
870 g_context_table(1).source_text := NULL;
871 g_context_table(1).source_number := NULL;
872 g_context_table(1).source_text2 := NULL;
873
874 RETURN 1;
875 END populate_bal_ids;
876
877 function etp_details
878 (
879 p_assignment_id in pay_assignment_actions.ASSIGNMENT_ID%type
880 ,p_year_start in pay_payroll_Actions.effective_date%type
881 ,p_year_end in pay_payroll_Actions.effective_date%type)
882 return number is
883
884 e_prepost_error EXCEPTION;
885
886 l_etp_payment NUMBER;
887 l_pre01jul1983_days NUMBER;
888 l_post30jun1983_days NUMBER;
889 l_pre01jul1983_ratio NUMBER;
890 l_post30jun1983_ratio NUMBER;
891 l_pre01jul1983_value NUMBER;
892 l_post30jun1983_value NUMBER;
893 l_result NUMBER;
894 l_etp_service_date date; /* Bug# 2984390 */
895 l_le_etp_service_date date; /* Bug 4177679 */
896 l_current_employee_flag per_all_people_f.current_employee_flag%type;
897 l_actual_termination_date per_periods_of_service.actual_termination_date%TYPE;
898 l_date_start per_periods_of_service.date_start%TYPE;
899 l_death_benefit_type varchar2(100);
900 l_lst_yr_start date;
901
902
903 CURSOR etp_code(c_assignment_id in pay_assignment_actions.assignment_id%type,
904 c_lst_year_start in pay_payroll_actions.effective_date%type,
905 c_year_start in pay_payroll_actions.effective_date%type,
906 c_year_end in pay_payroll_actions.effective_date%type
907 )is
908 SELECT distinct nvl(current_employee_flag,'N') current_employee_flag
909 ,actual_termination_date
910 ,date_start
911 ,pps.pds_information2
912 from per_all_people_f p,
913 per_all_assignments_f a,
914 per_periods_of_service pps
915 where a.person_id = p.person_id
916 and pps.person_id = p.person_id
917 and pps.period_of_service_id=a.period_of_service_id /*Bug 5397790 */
918 and ( pps.actual_termination_date between c_lst_year_start --bug 3686549
919 and c_year_end ) --Bug 3263659
920 and a.assignment_id = c_assignment_id
921 and p.effective_start_date = (SELECT max(pp.effective_start_date)
922 from per_all_people_f pp
923 where p.person_id = pp.person_id )
924 and a.effective_start_date = (SELECT max(aa.effective_start_date)
925 from per_all_assignments_f aa
926 where aa.assignment_id = c_assignment_id); /*Bug 4256486 */
927
928 begin
929 if g_debug then
930 hr_utility.set_location('Start of archive_prepost_details',15);
931 END if;
932 -- Added for bug 3686549
933 l_current_employee_flag := 'Y';
934 IF (g_lst_yr_term = 'N') THEN
935 l_lst_yr_start := to_date('01/01/1900','DD/MM/YYYY');
936 ELSE
937 l_lst_yr_start := add_months(p_year_start,-12);
938 END IF;
939 ------------------------------------------
940
941 OPEN etp_code(p_assignment_id,
942 l_lst_yr_start, --bug 3686549
943 p_year_start,
944 p_year_end
945 );
946 FETCH etp_code into l_current_employee_flag,
947 l_actual_termination_date,
948 l_date_start,
949 l_death_benefit_type;
950
951 CLOSE etp_code;
952
953
954 /*Bug 6112527 For death benefit type 'Dependent' ETP amount is taxable eff 01-Jul-2007 */
955
956 if ((l_death_benefit_type <>'D' or to_number(to_char(p_year_start,'YYYY')) >= 2007) or l_death_benefit_type is NULL) then
957
958
959
960 -- Bug: 3186840 Check against the global Dimension level value included to return the correct value.
961
962 IF (g_bal_dim_level = 'N') THEN
963 l_etp_payment := get_bal_value_new(g_db_id_lscp);--Bug# 3193479
964 ELSIF (g_bal_dim_level = 'T') THEN
965 l_etp_payment := g_result_term_details_table(2).balance_value;
966 END IF;
967
968 --------------------------------------------------------------------------------+
969 -- this procedure gets the ratios to calculate prejul83 balance and postjun83 balance
970 --------------------------------------------------------------------------------+
971 if g_debug then
972 hr_utility.set_location('calling pay_au_terminations.etp_prepost_ratios ',15);
973 END if;
974
975 l_result :=pay_au_terminations.etp_prepost_ratios(
976 p_assignment_id -- number in
977 ,l_date_start -- date in
978 ,l_actual_termination_date -- date in
979 ,'N' -- Bug#2819479 Flag to check whether this function called by Termination Form.
980 ,l_pre01jul1983_days -- number out
981 ,l_post30jun1983_days -- number out
982 ,l_pre01jul1983_ratio -- number out
983 ,l_post30jun1983_ratio -- number out
984 ,l_etp_service_date -- date out /* Bug# 2984390 */
985 ,l_le_etp_service_date
986 ); -- date out /* Bug# 4177679 */
987
988 if l_result = 0 then
989 raise e_prepost_error;
990
991 else
992
993 g_pre01jul1983_value :=round(l_etp_payment*l_pre01jul1983_ratio,2); /* Bug 4872594 - Changed to Round upto 2 decimals*/
994 g_post30jun1983_value :=round(l_etp_payment*l_post30jun1983_ratio,2); /* Bug 4872594 - Changed to Round upto 2 decimals */
995
996
997 end if;
998 if g_debug then
999 hr_utility.set_location('End of archive_prepost_details',14);
1000 END if;
1001 g_etp_gross:=g_pre01jul1983_value+g_post30jun1983_value+Total_Invalidity_Payments;
1002 g_assessable:= round(g_post30jun1983_value,2); /* Bug 4872594 - Changed to Round upto 2 decimals */
1003 /* Bug No : 7030285 - Assessable Income modified */
1004 -- Bug: 3186840 Check against the global Dimension level value included to return the correct value.
1005
1006 IF (g_bal_dim_level = 'N') THEN
1007 g_etp_tax:=get_bal_value_new(g_db_id_lscd); --Bug# 3193479
1008 ELSIF (g_bal_dim_level = 'T') THEN
1009 g_etp_tax := g_result_term_details_table(4).balance_value;
1010 END IF;
1011
1012
1013 else
1014 g_etp_gross:=0;
1015 g_assessable:=0;
1016 g_etp_tax:=0;
1017 end if;
1018
1019 return g_pre01jul1983_value;
1020
1021
1022 exception
1023 when e_prepost_error then
1024 if g_debug then
1025 hr_utility.set_location('error from pay_au_terminations.etp_prepost_ratios',20);
1026 END if;
1027 when others then
1028 if g_debug then
1029 hr_utility.set_location('error in function_prepost_details',21);
1030 END if;
1031 raise;
1032 end etp_details;
1033
1034
1035 function post30jun1983_value return number is
1036 begin
1037 return g_post30jun1983_value;
1038 end post30jun1983_value;
1039
1040
1041 function etp_gross return number is
1042 begin
1043 return g_etp_gross;
1044 end etp_gross;
1045
1046 function assessable_income return number is
1047 begin
1048 return g_assessable;
1049 end assessable_income;
1050
1051 function etp_tax return number is
1052 begin
1053 return g_etp_tax;
1054 end etp_tax;
1055
1056 --------------------Bug# 3193479-----------------------------------------------------------------
1057 function get_bal_value_new(p_defined_balance_id pay_defined_balances.defined_balance_id%TYPE)
1058 return number is
1059 begin
1060 for i in 1..g_result_table.last
1061 loop
1062 if g_result_table.exists(i) then
1063 if g_result_table(i).defined_balance_id =p_defined_balance_id then
1064 RETURN g_result_table(i).balance_value;
1065 end if;
1066 end if;
1067 end loop;
1068 RETURN 0;
1069 end;
1070
1071
1072 PROCEDURE get_value_bbr(c_year_start DATE,
1073 c_year_end DATE,
1074 c_assignment_id pay_assignment_actions.assignment_id%type,
1075 c_fbt_rate ff_globals_f.global_value%TYPE,
1076 c_ml_rate ff_globals_f.global_value%TYPE,
1077 p_assignment_action_id pay_assignment_actions.assignment_id%type,
1078 p_tax_unit_id hr_all_organization_units.organization_id%TYPE,
1079 p_termination_date DATE, --Bug 3098367
1080 p_display_flag OUT NOCOPY VARCHAR2, --Bug 3098367
1081 p_output_tab OUT NOCOPY bal_tab,
1082 p_message OUT NOCOPY VARCHAR2
1083 ) IS
1084
1085 l_net_balance NUMBER := 0; -- 3098353
1086 begin
1087 g_pre01jul1983_value :=0;
1088 g_post30jun1983_value :=0;
1089 g_etp_gross :=0;
1090 g_etp_tax :=0;
1091 g_assessable :=0;
1092 g_lump_sum_e :=0;
1093 g_total_other_income :=0;
1094 g_total_cdep :=0;
1095 g_total_allowance :=0;
1096 g_total_fbt :=0;
1097 g_total_gross :=0;
1098 g_total_workplace :=0; /* 4015082 */
1099 g_result_table.delete;
1100 g_context_table.delete;
1101 bal_id.delete;
1102 p_display_flag := 'YES';
1103 g_context_table(1).tax_unit_id := p_tax_unit_id;
1104
1105 pay_balance_pkg.get_value(p_assignment_action_id => p_assignment_action_id,
1106 p_defined_balance_lst=>g_input_table,
1107 p_context_lst =>g_context_table,
1108 p_output_table=>g_result_table);
1109
1110 g_bal_dim_level := 'N'; -- Bug: 3186840
1111
1112 bal_id(1).balance_value := get_total_allowances(c_year_start,c_year_end,c_assignment_id,p_assignment_action_id,p_tax_unit_id,p_message); --2610141
1113 IF p_message IS NULL THEN
1114 bal_id(2).balance_value := get_total_fbt(c_year_start,c_assignment_id,p_tax_unit_id,c_fbt_rate,c_ml_rate,null); --Bug#3213539
1115 bal_id(3).balance_value := get_total_cdep;
1116 bal_id(4).balance_value := Total_Lump_Sum_A_Payments;
1117 bal_id(5).balance_value := Total_Lump_Sum_B_Payments;
1118 bal_id(6).balance_value := Total_Lump_Sum_D_Payments;
1119 bal_id(7).balance_value := Total_Lump_Sum_E_Payments(c_year_end,c_assignment_id,p_tax_unit_id); --2610141
1120 bal_id(8).balance_value := Total_Union_fees;
1121 bal_id(9).balance_value := Total_Tax_deductions;
1122 bal_id(10).balance_value := Total_Other_Income;
1123 bal_id(18).balance_value := get_total_workplace; /* 4015082 */
1124 bal_id(11).balance_value := total_gross;
1125 bal_id(12).balance_value := ETP_DETAILS(c_assignment_id,c_year_start,c_year_end);
1126 bal_id(13).balance_value := POST30JUN1983_VALUE;
1127 bal_id(14).balance_value := TOTAL_INVALIDITY_PAYMENTS;
1128 bal_id(15).balance_value := ETP_GROSS;
1129 bal_id(16).balance_value := ETP_TAX;
1130 bal_id(17).balance_value := assessable_income;
1131
1132 /*--------------Bug 3098367-------------
1133 If employee is terminated in last year then assignment details will be displayed
1134 only if sum of balance values is greater than 0 otherwise employee will not be displayed */
1135 IF p_termination_date < c_year_start THEN
1136 For i IN 1..bal_id.COUNT
1137 LOOP
1138 l_net_balance := l_net_balance + bal_id(i).balance_value;
1139 END LOOP;
1140 IF l_net_balance = 0 THEN
1141 p_display_flag := 'NO';
1142 END IF;
1143 END IF;
1144 -------End of --Bug 3098367----------------------------------------------------
1145 p_output_tab := bal_id;
1146 END IF;
1147 end;
1148 ---------------End of Bug# 3193479-----------------------------------------------------------
1149
1150 function get_exclusion_info(flag varchar2,p_assignment_id number)
1151 return varchar2 is
1152
1153 i number;
1154
1155 begin
1156
1157 if exc_tab1.count>0 then
1158
1159 for i in 0..exc_tab1.last
1160
1161 loop
1162
1163 if exc_tab1(i).assignment_id=p_assignment_id then
1164 if flag='name' then
1165 return exc_tab1(i).employee_name;
1166 end if;
1167 end if;
1168
1169 if exc_tab1(i).assignment_id=p_assignment_id then
1170 if flag='assignment' then
1171 return exc_tab1(i).assignment_number;
1172 end if;
1173 end if;
1174
1175 if exc_tab1(i).assignment_id=p_assignment_id then
1176 if flag='reason' then
1177 return exc_tab1(i).reason;
1178 end if;
1179 end if;
1180 end loop;
1181
1182
1183 end if;
1184
1185 end get_exclusion_info;
1186
1187
1188 function get_assignment_id(p_assignment_id number) return number is
1189 i number;
1190 begin
1191
1192 if exc_tab1.count>0 then
1193 for i in 0..exc_tab1.last
1194 loop
1195 if exc_tab1.exists(i) then
1196 if exc_tab1(i).assignment_id=p_assignment_id
1197 then
1198 return 1;
1199 end if;
1200 end if;
1201 end loop;
1202 end if;
1203 return 0;
1204 end get_assignment_id;
1205
1206 function populate_exclusion_table(p_assignment_id per_all_assignments_f.assignment_id%type,
1207 p_financial_year varchar2,
1208 p_financial_year_end date,
1209 p_tax_unit_id number --2610141
1210 )
1211 return number is
1212
1213 Cursor c_ps_issued(c_assignment_id per_all_assignments_f.assignment_id%type,
1214 c_financial_year varchar2)
1215 is
1216 SELECT distinct paat.assignment_id
1217 from pay_action_interlocks pail,
1218 pay_assignment_actions paat,
1219 pay_payroll_actions paas
1220 where paat.assignment_id = c_assignment_id
1221 and paas.action_type ='X'
1222 and paas.action_status ='C'
1223 and paas.report_type ='AU_PAYMENT_SUMMARY_REPORT'
1224 and pail.locking_action_id = paat.assignment_action_id
1225 and paat.payroll_action_id = paas.payroll_action_id
1226 and pay_core_utils.get_parameter('FINANCIAL_YEAR',paas.legislative_parameters) = c_financial_year
1227 and pay_core_utils.get_parameter('REGISTERED_EMPLOYER',paas.legislative_parameters) = p_tax_unit_id; --2610141
1228
1229
1230 CURSOR c_get_details(c_assignment_id per_all_assignments_f.assignment_id%type,
1231 c_financial_yr_end date)
1232 is
1233 SELECT pap.last_name,
1234 paa.assignment_number
1235 from per_all_people_f pap,per_all_assignments_f paa
1236 where pap.person_id=paa.person_id
1237 and paa.assignment_id=c_assignment_id
1238 and paa.effective_start_date = (SELECT max(paa1.effective_start_date)
1239 from per_all_assignments_f paa1
1240 where paa1.assignment_id = c_assignment_id) /* Bug 4278407*/
1241 and pap.effective_start_date = (SELECT max(ppf.effective_start_date)
1242 from per_all_people_f ppf
1243 where pap.person_id = ppf.person_id);
1244
1245 CURSOR c_eit_updated(c_assignment_id per_all_assignments_f.assignment_id%type,
1246 c_financial_year varchar2)
1247 is
1248 SELECT assignment_id
1249 from per_assignment_extra_info,
1250 hr_lookups
1251 where assignment_id = c_assignment_id
1252 and aei_information1 is not null
1253 and aei_information1 = lookup_code
1254 and nvl(aei_information2,p_tax_unit_id) = decode(aei_information2,'-999',aei_information2,p_tax_unit_id) --Bug 4173809
1255 and lookup_type ='AU_PS_FINANCIAL_YEAR'
1256 and meaning = c_financial_year;
1257
1258 /*Bug 4173809 - Cursor updated so that the assignment is reported in the exception section when Manual PS
1259 is issued against 'All' legal employers or a particular legal employer
1260 If the Manual PS is issued for 'All' the legal employers the aei_information2 would be -999*/
1261
1262 l_assignment_id per_all_assignments_f.assignment_id%type;
1263 l_assignment_number per_all_assignments_f.assignment_number%type;
1264 l_employee_name per_all_people_f.last_name%type;
1265 l_reason fnd_new_messages.message_text%type;
1266
1267
1268 begin
1269
1270
1271 OPEN c_ps_issued(p_assignment_id,p_financial_year);
1272 FETCH c_ps_issued into l_assignment_id;
1273 if c_ps_issued%found then
1274 OPEN c_get_details(l_assignment_id,p_financial_year_end);
1275 FETCH c_get_details into l_employee_name
1276 ,l_assignment_number;
1277
1278 exc_tab1(g_index).employee_name:=l_employee_name;
1279 exc_tab1(g_index).assignment_number:=l_assignment_number;
1280 exc_tab1(g_index).assignment_id:=l_assignment_id;
1281 l_reason:=fnd_message.get_string('PER','HR_AU_SELF_PRINTED_PS_ISSUED');
1282 exc_tab1(g_index).reason:=l_reason;
1283
1284 g_index:=g_index+1;
1285 CLOSE c_get_details;
1286 CLOSE c_ps_issued;
1287 return l_assignment_id;
1288 end if;
1289
1290
1291 CLOSE c_ps_issued;
1292
1293 OPEN c_eit_updated(p_assignment_id,p_financial_year);
1294 FETCH c_eit_updated into l_assignment_id;
1295 if c_eit_updated%found then
1296 OPEN c_get_details(l_assignment_id,p_financial_year_end);
1297 FETCH c_get_details into l_employee_name
1298 ,l_assignment_number;
1299
1300 exc_tab1(g_index).employee_name:=l_employee_name;
1301 exc_tab1(g_index).assignment_number:=l_assignment_number;
1302 exc_tab1(g_index).assignment_id:=l_assignment_id;
1303 l_reason:=fnd_message.get_string('PER','HR_AU_MANUAL_PS_ISSUED');
1304 exc_tab1(g_index).reason:=l_reason;
1305
1306 g_index:=g_index+1;
1307 CLOSE c_get_details;
1308 CLOSE c_eit_updated;
1309 return g_index-1;
1310 end if;
1311
1312
1313 CLOSE c_eit_updated;
1314
1315 return 0;
1316 end populate_exclusion_table;
1317
1318 /* Start of Bug : 3186840 */
1319
1320 PROCEDURE populate_group_def_bal_ids(p_dimension_name pay_balance_dimensions.dimension_name%TYPE)
1321 IS
1322
1323 CURSOR csr_group_def_bal_ids IS
1324 SELECT decode(pbt.balance_name,'Lump Sum A Payments',1,'Lump Sum B Payments',2,
1325 'Lump Sum D Payments',3,'Union Fees',4,'Lump Sum C Deductions',5,
1326 'Termination Deductions',6,'Total_Tax_Deductions',7,'Earnings_Total',8,'Leave Payments Marginal',9,
1327 'CDEP',10,'Other Income',11
1328 ,'Workplace Giving Deductions', 12) sort_index /*4015082 */
1329 , pdb.defined_balance_id
1330 FROM pay_balance_types pbt
1331 , pay_defined_balances pdb
1332 , pay_balance_dimensions pbd
1333 WHERE pbt.legislation_code = 'AU'
1334 AND pbt.balance_name in
1335 ('Lump Sum A Payments','Lump Sum B Payments','Lump Sum D Payments',
1336 'Union Fees','Lump Sum C Deductions','Termination Deductions',
1337 'Total_Tax_Deductions','Earnings_Total','Leave Payments Marginal','CDEP','Other Income',
1338 'Workplace Giving Deductions') /* 4015082 */
1339 AND pdb.balance_type_id = pbt.balance_type_id
1340 AND pdb.balance_dimension_id = pbd.balance_dimension_id
1341 AND pbd.legislation_code = 'AU'
1342 AND pdb.legislation_code = 'AU'
1343 AND pbd.dimension_name = p_dimension_name
1344 ORDER BY sort_index;
1345
1346 l_sort_index number;
1347 l_def_bal_id pay_defined_balances.defined_balance_id%TYPE;
1348
1349 BEGIN
1350
1351 g_debug := hr_utility.debug_enabled;
1352
1353 g_dimension_name := p_dimension_name;
1354
1355 IF g_debug THEN
1356 hr_utility.trace('Dimension is ' || p_dimension_name);
1357 END IF;
1358
1359 /* Group Level Defined Balance IDs get stored in the PL/SQL table with the following order.
1360
1361 ---------------------------------------------------------
1362 Location ID Group Level Balance Name
1363 '_LE_YTD' or '_PAY_LE_YTD'
1364 ---------------------------------------------------------
1365 1 Lump Sum A Payments
1366 2 Lump Sum B Payments
1367 3 Lump Sum D Payments
1368 4 Union Fees
1369 5 Lump Sum C Deductions
1370 6 Termination Deduction
1371 7 Total_Tax_Deductions
1372
1373 Bug: 3186840 - Four more Balances included for GROUP LEVEL functionality
1374
1375 8 Earnings_Total
1376 9 Leave Payments Marginal
1377 10 CDEP
1378 11 Other Income
1379 12 Workplace Giving Deductions
1380 --------------------------------------------------------- */
1381
1382 FOR csr_rec IN csr_group_def_bal_ids
1383 LOOP
1384 g_input_group_details_table(csr_rec.sort_index).defined_balance_id := csr_rec.defined_balance_id;
1385
1386 IF g_debug THEN
1387 hr_utility.trace(csr_rec.sort_index || ' ' || g_input_group_details_table(csr_rec.sort_index).defined_balance_id);
1388 END IF;
1389
1390 END LOOP;
1391
1392 --Except Tax-Unit_id other Context table values are not required
1393
1394 g_context_table(1).jurisdiction_code := NULL;
1395 g_context_table(1).source_id := NULL;
1396 g_context_table(1).source_text := NULL;
1397 g_context_table(1).source_number := NULL;
1398 g_context_table(1).source_text2 := NULL;
1399
1400 END populate_group_def_bal_ids;
1401
1402 PROCEDURE get_group_values_bbr
1403 ( p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE DEFAULT NULL
1404 , p_date_earned IN date
1405 , p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE
1406 , p_group_output_tab OUT NOCOPY bal_tab ) IS
1407
1408 BEGIN
1409 bal_id.delete;
1410 g_result_group_details_table.delete;
1411
1412 g_context_table(1).tax_unit_id := p_tax_unit_id;
1413
1414 IF (g_dimension_name = '_LE_YTD') THEN
1415 pay_balance_pkg.set_context('DATE_EARNED',fnd_date.date_to_canonical(p_date_earned));
1416 END IF;
1417
1418 pay_balance_pkg.get_value(p_assignment_action_id => p_assignment_action_id,
1419 p_defined_balance_lst => g_input_group_details_table,
1420 p_context_lst => g_context_table,
1421 p_output_table => g_result_group_details_table);
1422
1423 IF g_debug THEN
1424 FOR i IN 1..g_result_group_details_table.last
1425 LOOP
1426 IF g_result_group_details_table.exists(i) THEN
1427 hr_utility.trace('Balance Value ' || i || g_result_group_details_table(i).balance_value);
1428 END IF;
1429 END LOOP;
1430 END IF;
1431
1432
1433 g_bal_dim_level := 'G';
1434
1435 bal_id(1).balance_value := Total_Lump_Sum_A_Payments;
1436 bal_id(2).balance_value := Total_Lump_Sum_B_Payments;
1437 bal_id(3).balance_value := Total_Lump_Sum_D_Payments;
1438 bal_id(4).balance_value := Total_Union_fees;
1439 bal_id(5).balance_value := Total_Tax_deductions;
1440
1441 /* Bug: 3186840 - Included 4 more group level balances retrieval */
1442
1443 bal_id(7).balance_value := g_result_group_details_table(9).balance_value; -- Leave Payments Marginal
1444 bal_id(8).balance_value := get_total_cdep; -- CDEP
1445 bal_id(9).balance_value := Total_Other_Income; -- Other Income
1446 bal_id(10).balance_value := get_total_workplace; -- Workplace Giving Deductions /* 4015082 */
1447 bal_id(6).balance_value := g_result_group_details_table(8).balance_value; -- Earnings_Total
1448
1449 p_group_output_tab := bal_id;
1450
1451 END get_group_values_bbr;
1452
1453 PROCEDURE get_assgt_curr_term_values_bbr
1454 ( p_year_start IN date
1455 , p_year_end IN date
1456 , p_assignment_id IN pay_assignment_actions.assignment_id%type
1457 , p_fbt_rate IN ff_globals_f.global_value%TYPE
1458 , p_ml_rate IN ff_globals_f.global_value%TYPE
1459 , p_assignment_action_id IN pay_assignment_actions.assignment_action_id%type
1460 , p_tax_unit_id IN hr_all_organization_units.organization_id%TYPE
1461 , p_emp_type IN varchar2
1462 , p_term_output_tab OUT NOCOPY bal_tab
1463 , p_message OUT NOCOPY VARCHAR2) IS
1464
1465 g_debug boolean;
1466
1467 BEGIN
1468
1469 g_debug := hr_utility.debug_enabled;
1470
1471 g_pre01jul1983_value :=0;
1472 g_post30jun1983_value :=0;
1473 g_etp_gross :=0;
1474 g_etp_tax :=0;
1475 g_assessable :=0;
1476 g_total_allowance :=0; /*Bug 4888097*/
1477 g_total_fbt := 0; /*Bug 4888097*/
1478 g_lump_sum_e := 0; /*Bug 4888097*/
1479
1480 g_context_table.delete;
1481 g_context_table(1).tax_unit_id := p_tax_unit_id;
1482
1483 bal_id.delete;
1484 g_result_term_details_table.delete;
1485
1486 /* ---------------------------------------------------------
1487 Location ID Group Level Balance Name
1488 '_ASG_LE_YTD'
1489 ---------------------------------------------------------
1490 For all Employees (Current and Terminated):
1491 --------------------------------------------
1492 1 Lump Sum E Payments
1493
1494 For Terminated Employees only:
1495 -------------------------------
1496
1497 2 Lump Sum C Payments
1498 3 Invalidity Payments
1499 4 Lump Sum C Deductions
1500 --------------------------------------------------------- */
1501
1502 g_input_term_details_table(1).defined_balance_id := g_db_id_lsep;
1503
1504 IF (p_emp_type = 'T') THEN
1505
1506 g_input_term_details_table(2).defined_balance_id := g_db_id_lscp;
1507 g_input_term_details_table(3).defined_balance_id := g_db_id_ip;
1508 g_input_term_details_table(4).defined_balance_id := g_db_id_lscd;
1509
1510 END IF;
1511
1512 FOR i IN 1..g_input_term_details_table.last
1513 LOOP
1514 IF g_input_term_details_table.exists(i) THEN
1515 IF g_debug THEN
1516 hr_utility.trace(i || ' ' || g_input_term_details_table(i).defined_balance_id);
1517 END IF;
1518 END IF;
1519 END LOOP;
1520
1521 pay_balance_pkg.get_value
1522 ( p_assignment_action_id => p_assignment_action_id
1523 , p_defined_balance_lst => g_input_term_details_table
1524 , p_context_lst => g_context_table
1525 , p_output_table => g_result_term_details_table);
1526
1527 IF g_debug THEN
1528 FOR i IN 1..g_result_term_details_table.last
1529 LOOP
1530 IF g_result_term_details_table.exists(i) THEN
1531 hr_utility.trace('Balance Value ' || i || g_result_term_details_table(i).balance_value);
1532 END IF;
1533 END LOOP;
1534 END IF;
1535
1536 g_bal_dim_level := 'T';
1537
1538 bal_id(1).balance_value := get_total_allowances(p_year_start,p_year_end,p_assignment_id,p_assignment_action_id,p_tax_unit_id,p_message); --2610141
1539 IF p_message IS NULL THEN
1540 bal_id(2).balance_value := get_total_fbt(p_year_start,p_assignment_id,p_tax_unit_id,p_fbt_rate,p_ml_rate,null);
1541 bal_id(3).balance_value := Total_Lump_Sum_E_Payments(p_year_end,p_assignment_id,p_tax_unit_id); --2610141
1542
1543 IF (p_emp_type = 'T') THEN
1544
1545 bal_id(4).balance_value := ETP_DETAILS(p_assignment_id,p_year_start,p_year_end);
1546 bal_id(5).balance_value := POST30JUN1983_VALUE;
1547 bal_id(6).balance_value := TOTAL_INVALIDITY_PAYMENTS;
1548 bal_id(7).balance_value := ETP_GROSS;
1549 bal_id(8).balance_value := ETP_TAX;
1550 bal_id(9).balance_value := assessable_income;
1551
1552 END IF;
1553
1554 IF g_debug THEN
1555 FOR i IN 1..bal_id.last
1556 LOOP
1557 IF bal_id.exists(i) THEN
1558 hr_utility.trace('Output Balance Value ' || i || bal_id(i).balance_value);
1559 END IF;
1560 END LOOP;
1561 END IF;
1562
1563 p_term_output_tab := bal_id;
1564 END IF;
1565
1566 END get_assgt_curr_term_values_bbr;
1567
1568
1569 ------------ --Bug#3749530 All the procedures added below are for archival model------
1570 -- On Submitting payrec-PS reuqest first archival proceudres are called
1571
1572 --------------------------------------------------------------------
1573 -- These are PUBLIC procedures are required by the Archive process.
1574 -- There names are stored in PAY_REPORT_FORMAT_MAPPINGS_F so that
1575 -- the archive process knows what code to execute for each step of
1576 -- the archive.
1577 --------------------------------------------------------------------
1578
1579 --------------------------------------------------------------------
1580 -- This procedure returns a sql string to select a range
1581 -- of assignments eligible for archival.
1582 --------------------------------------------------------------------
1583
1584 procedure range_code
1585 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
1586 p_sql out nocopy varchar2) is
1587 begin
1588 g_debug := hr_utility.debug_enabled;
1589 IF g_debug THEN
1590 hr_utility.set_location('Start of range_code',1);
1591 END if;
1592 p_sql := ' select distinct p.person_id' ||
1593 ' from per_people_f p,' ||
1594 ' pay_payroll_actions pa' ||
1595 ' where pa.payroll_action_id = :payroll_action_id' ||
1596 ' and p.business_group_id = pa.business_group_id' ||
1597 ' order by p.person_id';
1598 IF g_debug THEN
1599 hr_utility.set_location('End of range_code',2);
1600 END if;
1601 end range_code;
1602
1603
1604 --------------------------------------------------------------------
1605 -- This procedure is used to set global contexts
1606 -- however in current case it is a dummy procedure. In case this
1607 -- procedure is not present then archiver assumes that
1608 -- no archival is required.
1609 --------------------------------------------------------------------
1610
1611 procedure initialization_code
1612 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type) is
1613 begin
1614 NULL;
1615 END;
1616
1617 /*
1618 Bug 7138494 - Added Function range_person_on
1619 --------------------------------------------------------------------
1620 Name : range_person_on
1621 Type : Function
1622 Access: Private
1623 Description: Checks if RANGE_PERSON_ID is enabled for
1624 Archive process.
1625 --------------------------------------------------------------------
1626 */
1627
1628 FUNCTION range_person_on
1629 RETURN BOOLEAN
1630 IS
1631
1632 CURSOR csr_action_parameter is
1633 select parameter_value
1634 from pay_action_parameters
1635 where parameter_name = 'RANGE_PERSON_ID';
1636
1637 CURSOR csr_range_format_param is
1638 select par.parameter_value
1639 from pay_report_format_parameters par,
1640 pay_report_format_mappings_f map
1641 where map.report_format_mapping_id = par.report_format_mapping_id
1642 and map.report_type = 'AU_REC_PS_ARCHIVE'
1643 and map.report_format = 'AU_REC_PS_ARCHIVE'
1644 and map.report_qualifier = 'AU'
1645 and par.parameter_name = 'RANGE_PERSON_ID'; -- Bug fix 5567246
1646
1647 l_return boolean;
1648 l_action_param_val varchar2(30);
1649 l_report_param_val varchar2(30);
1650
1651 BEGIN
1652
1653 g_debug := hr_utility.debug_enabled;
1654
1655 IF g_debug
1656 THEN
1657 hr_utility.set_location('range_person_on',10);
1658 END IF;
1659
1660 BEGIN
1661
1662 open csr_action_parameter;
1663 fetch csr_action_parameter into l_action_param_val;
1664 close csr_action_parameter;
1665
1666 IF g_debug
1667 THEN
1668 hr_utility.set_location('range_person_on',20);
1669 END IF;
1670
1671 open csr_range_format_param;
1672 fetch csr_range_format_param into l_report_param_val;
1673 close csr_range_format_param;
1674 IF g_debug
1675 THEN
1676 hr_utility.set_location('range_person_on',30);
1677 END IF;
1678 EXCEPTION WHEN NO_DATA_FOUND THEN
1679 l_return := FALSE;
1680 END;
1681 --
1682 IF g_debug
1683 THEN
1684 hr_utility.set_location('range_person_on',40);
1685 END IF;
1686
1687 IF l_action_param_val = 'Y' AND l_report_param_val = 'Y' THEN
1688 l_return := TRUE;
1689 hr_utility.trace('Range Person = True');
1690 ELSE
1691 l_return := FALSE;
1692 END IF;
1693 --
1694 RETURN l_return;
1695 --
1696 END range_person_on;
1697
1698 --------------------------------------------------------------------
1699 -- This procedure further restricts the assignment_id's
1700 -- returned by range_code
1701 -- It inserts the record in pay_assignment_Actions
1702 -- Which are then used in main report query to get assignment_ids
1703 --------------------------------------------------------------------
1704 procedure assignment_action_code
1705 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
1706 p_start_person_id in per_all_people_f.person_id%type,
1707 p_end_person_id in per_all_people_f.person_id%type,
1708 p_chunk in number) is
1709
1710
1711 l_asgid per_assignments_f.assignment_id%type;
1712 l_next_action_id pay_assignment_actions.assignment_action_id%type;
1713
1714
1715 l_lst_yr_start date;
1716 l_lst_fbt_yr_start date;
1717 l_assignment_id varchar2(50);
1718 l_business_group_id hr_organization_units.organization_id%type;
1719 l_legal_employer varchar2(50);
1720 l_employee_type varchar2(2);
1721 l_payroll_id varchar2(50);
1722 l_fin_year_start date;
1723 l_fin_year_end date;
1724 l_fbt_year_start date;
1725 l_fbt_year_end date;
1726 l_lst_yr_term VARCHAR2(2);
1727
1728
1729 cursor get_params(c_payroll_action_id per_all_assignments_f.assignment_id%type)
1730 is
1731 select to_date('01-07-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY')
1732 Financial_year_start
1733 ,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),6,4),'DD-MM-YYYY')
1734 Financial_year_end
1735 ,to_date('01-04-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY')
1736 FBT_year_start
1737 ,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY')
1738 FBT_year_end
1739 ,decode(pay_core_utils.get_parameter('EMPLOYEE_TYPE',legislative_parameters),'C','Y','T','N','B','%')
1740 Employee_type
1741 ,pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters) Registered_Employer
1742 ,decode(pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters),null,'%', pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters)) Assignment_id
1743 ,decode(pay_core_utils.get_parameter('PAYROLL_ID',legislative_parameters),null,'%',pay_core_utils.get_parameter('PAYROLL_ID',legislative_parameters)) payroll_id
1744 ,pay_core_utils.get_parameter('LST_YR_TERM',legislative_parameters) lst_yr_term /*Bug3661230*/
1745 ,pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters) Business_group_id
1746 from pay_payroll_actions
1747 where payroll_action_id =c_payroll_Action_id;
1748
1749 cursor next_action_id is
1750 select pay_assignment_actions_s.nextval
1751 from dual;
1752
1753 cursor c_asgids(p_assignment_id varchar2,
1754 p_business_group_id hr_organization_units.organization_id%type,
1755 p_legal_employer varchar2,
1756 p_employee_type varchar2,
1757 p_payroll_id varchar2,
1758 p_fin_year_start date,
1759 p_fin_year_end date,
1760 p_lst_fbt_yr_start date,
1761 p_fbt_year_start date,
1762 p_fbt_year_end date,
1763 p_lst_year_start date
1764 )
1765 is
1766 SELECT /*+ INDEX(pap per_people_f_pk)
1767 INDEX(rppa pay_payroll_actions_pk)
1768 INDEX(paa per_assignments_f_N12)
1769 INDEX(pps per_periods_of_service_pk)
1770 */ paa.assignment_id
1771 from per_people_f pap
1772 ,per_assignments_f paa
1773 ,pay_payroll_actions rppa
1774 ,per_periods_of_service pps
1775 where rppa.payroll_action_id = p_payroll_action_id
1776 and pap.person_id between p_start_person_id and p_end_person_id
1777 and pap.person_id = paa.person_id
1778 and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (p_fin_year_end)),1,'Y','N')) LIKE p_employee_type
1779 and pps.period_of_service_id = paa.period_of_service_id
1780 and pap.person_id = pps.person_id
1781 and rppa.business_group_id=paa.business_group_id
1782 and nvl(pps.actual_termination_date, p_lst_year_start) >= p_lst_year_start
1783 and p_fin_year_end between pap.effective_start_date and pap.effective_end_date
1784 /* Start of Bug: 3872211 */
1785 and paa.effective_end_date = (SELECT MAX(effective_end_date) /*4377367*/
1786 FROM per_assignments_f iipaf
1787 WHERE iipaf.assignment_id = paa.assignment_id
1788 AND iipaf.effective_end_date >= p_fbt_year_start
1789 AND iipaf.effective_start_date <= p_fin_year_end
1790 AND iipaf.payroll_id IS NOT NULL) /* Bug#4688800 */
1791 and paa.payroll_id like p_payroll_id
1792 /* End of Bug: 3872211 */
1793 AND EXISTS (SELECT /*+ INDEX(rpac PAY_ASSIGNMENT_ACTIONS_N51)
1794 INDEX(rpac pay_assignment_actions_n1)
1795 INDEX(rppa PAY_PAYROLL_ACTIONS_N51)
1796 INDEX(rppa PAY_PAYROLL_ACTIONS_PK) */''
1797 FROM
1798 pay_payroll_actions rppa
1799 ,pay_assignment_actions rpac
1800 ,per_assignments_f paaf -- Bug: 3872211
1801 where (rppa.effective_date between p_fin_year_start and p_fin_year_end /*Bug3048962 */
1802 or ( pps.actual_termination_date between p_lst_fbt_yr_start and p_fbt_year_end /*Bug3263659 */
1803 and rppa.effective_date between p_fbt_year_start and p_fbt_year_end
1804 and pay_balance_pkg.get_value(g_fbt_defined_balance_id, rpac.assignment_action_id
1805 + decode(rppa.payroll_id, 0, 0, 0),p_legal_employer,null,null,null,null) > to_number(g_fbt_threshold))
1806 )
1807 and rppa.action_type in ('R','Q','B','I')
1808 and rpac.tax_unit_id = p_legal_employer
1809 and rppa.payroll_action_id = rpac.payroll_action_id
1810 and rpac.action_status = 'C'
1811 /* Start of Bug: 3872211 */
1812 and rpac.assignment_id = paaf.assignment_id
1813 and rppa.payroll_id = paaf.payroll_id
1814 and paaf.assignment_id = paa.assignment_id
1815 and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date);
1816 /* End of Bug: 3872211 */
1817
1818 /*
1819 Bug 7138494 - Added Cursor for Range Person
1820 - Uses person_id in pay_population_ranges
1821 --------------------------------------------------------------------+
1822 -- Cursor : c_range_asgids
1823 -- Description : Fetches assignments For Recconciling Payment Summary
1824 -- Returns DISTINCT assignment_id
1825 -- Used when RANGE_PERSON_ID feature is enabled
1826 --------------------------------------------------------------------+
1827 */
1828
1829 CURSOR c_range_asgids
1830 (p_assignment_id varchar2,
1831 p_business_group_id hr_organization_units.organization_id%type,
1832 p_legal_employer varchar2,
1833 p_employee_type varchar2,
1834 p_payroll_id varchar2,
1835 p_fin_year_start date,
1836 p_fin_year_end date,
1837 p_lst_fbt_yr_start date,
1838 p_fbt_year_start date,
1839 p_fbt_year_end date,
1840 p_lst_year_start date
1841 )
1842 IS
1843 SELECT /*+ INDEX(pap per_people_f_pk)
1844 INDEX(rppa pay_payroll_actions_pk)
1845 INDEX(ppr PAY_POPULATION_RANGES_N4)
1846 INDEX(paa per_assignments_f_N12)
1847 INDEX(pps per_periods_of_service_PK)
1848 */ paa.assignment_id
1849 from per_people_f pap
1850 ,per_assignments_f paa
1851 ,pay_payroll_actions rppa
1852 ,per_periods_of_service pps
1853 ,pay_population_ranges ppr
1854 where rppa.payroll_action_id = p_payroll_action_id
1855 and rppa.payroll_action_id = ppr.payroll_action_id
1856 and ppr.chunk_number = p_chunk
1857 and ppr.person_id = pap.person_id
1858 and pap.person_id = paa.person_id
1859 and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (p_fin_year_end)),1,'Y','N')) LIKE p_employee_type
1860 and pps.period_of_service_id = paa.period_of_service_id
1861 and pap.person_id = pps.person_id
1862 and rppa.business_group_id=paa.business_group_id
1863 and nvl(pps.actual_termination_date, p_lst_year_start) >= p_lst_year_start
1864 and p_fin_year_end between pap.effective_start_date and pap.effective_end_date
1865 /* Start of Bug: 3872211 */
1866 and paa.effective_end_date = (SELECT MAX(effective_end_date) /*4377367*/
1867 FROM per_assignments_f iipaf
1868 WHERE iipaf.assignment_id = paa.assignment_id
1869 AND iipaf.effective_end_date >= p_fbt_year_start
1870 AND iipaf.effective_start_date <= p_fin_year_end
1871 AND iipaf.payroll_id IS NOT NULL) /* Bug#4688800 */
1872 and paa.payroll_id like p_payroll_id
1873 /* End of Bug: 3872211 */
1874 AND EXISTS (SELECT /*+ INDEX(rpac PAY_ASSIGNMENT_ACTIONS_N51)
1875 INDEX(rppa PAY_PAYROLL_ACTIONS_N51)
1876 INDEX(rppa PAY_PAYROLL_ACTIONS_PK)
1877 */''
1878 FROM
1879 pay_payroll_actions rppa
1880 ,pay_assignment_actions rpac
1881 ,per_assignments_f paaf -- Bug: 3872211
1882 where (rppa.effective_date between p_fin_year_start and p_fin_year_end /*Bug3048962 */
1883 or ( pps.actual_termination_date between p_lst_fbt_yr_start and p_fbt_year_end /*Bug3263659 */
1884 and rppa.effective_date between p_fbt_year_start and p_fbt_year_end
1885 and pay_balance_pkg.get_value(g_fbt_defined_balance_id, rpac.assignment_action_id
1886 + decode(rppa.payroll_id, 0, 0, 0),p_legal_employer,null,null,null,null) > to_number(g_fbt_threshold))
1887 )
1888 and rppa.action_type in ('R','Q','B','I')
1889 and rpac.tax_unit_id = p_legal_employer
1890 and rppa.payroll_action_id = rpac.payroll_action_id
1891 and rpac.action_status = 'C'
1892 /* Start of Bug: 3872211 */
1893 and rpac.assignment_id = paaf.assignment_id
1894 and rppa.payroll_id = paaf.payroll_id
1895 and paaf.assignment_id = paa.assignment_id
1896 and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date);
1897 /* End of Bug: 3872211 */
1898
1899
1900
1901 cursor c_asgid_only(p_assignment_id varchar2,
1902 p_business_group_id hr_organization_units.organization_id%type,
1903 p_legal_employer varchar2,
1904 p_employee_type varchar2,
1905 p_payroll_id varchar2,
1906 p_fin_year_start date,
1907 p_fin_year_end date,
1908 p_lst_fbt_yr_start date,
1909 p_fbt_year_start date,
1910 p_fbt_year_end date,
1911 p_lst_year_start date
1912 )
1913 is
1914 SELECT /*+ INDEX(pap per_people_f_pk)
1915 INDEX(paa per_assignments_f_fk1)
1916 INDEX(paa per_assignments_f_N12)
1917 INDEX(rppa pay_payroll_actions_pk)
1918 INDEX(pps per_periods_of_service_n3)
1919 */ distinct paa.assignment_id
1920 from per_people_f pap
1921 ,per_assignments_f paa
1922 ,pay_payroll_actions rppa
1923 ,per_periods_of_service pps
1924 where rppa.payroll_action_id = p_payroll_action_id
1925 and pap.person_id between p_start_person_id and p_end_person_id
1926 and pap.person_id = paa.person_id
1927 and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (p_fin_year_end)),1,'Y','N')) LIKE p_employee_type
1928 and pps.period_of_service_id = paa.period_of_service_id
1929 and paa.assignment_id = p_assignment_id
1930 and pap.person_id = pps.person_id
1931 and rppa.business_group_id=paa.business_group_id
1932 and nvl(pps.actual_termination_date, p_lst_year_start) >= p_lst_year_start
1933 and p_fin_year_end between pap.effective_start_date and pap.effective_end_date
1934 -- and least(nvl(pps.actual_termination_date,p_fin_year_end),p_fin_year_end) between paa.effective_start_date and paa.effective_end_date
1935 and paa.effective_end_date = (select max(effective_end_date) /*4377367*/
1936 From per_assignments_f iipaf
1937 WHERE iipaf.assignment_id = paa.assignment_id
1938 and iipaf.effective_end_date >= p_fbt_year_start
1939 and iipaf.effective_start_date <= p_fin_year_end
1940 AND iipaf.payroll_id IS NOT NULL) /* Bug#4688800 */
1941 and paa.payroll_id like p_payroll_id
1942 AND EXISTS (SELECT /*+ INDEX(rpac PAY_ASSIGNMENT_ACTIONS_N51)
1943 INDEX(rpac pay_assignment_actions_n1)
1944 INDEX(rppa PAY_PAYROLL_ACTIONS_N51)
1945 INDEX(rppa PAY_PAYROLL_ACTIONS_PK) */''
1946 FROM
1947 pay_payroll_actions rppa
1948 ,pay_assignment_actions rpac
1949 ,per_assignments_f paaf -- Bug: 3872211
1950 where (rppa.effective_date between p_fin_year_start and p_fin_year_end /*Bug3048962 */
1951 or ( pps.actual_termination_date between p_lst_fbt_yr_start and p_fbt_year_end /*Bug3263659 */
1952 and rppa.effective_date between p_fbt_year_start and p_fbt_year_end
1953 and pay_balance_pkg.get_value(g_fbt_defined_balance_id, rpac.assignment_action_id
1954 + decode(rppa.payroll_id, 0, 0, 0),p_legal_employer,null,null,null,null) > to_number(g_fbt_threshold) ) --2610141 /* Bug 5708255 */
1955 )
1956 and rppa.action_type in ('R','Q','B','I')
1957 and rpac.tax_unit_id = p_legal_employer
1958 and rppa.payroll_action_id = rpac.payroll_action_id
1959 and rpac.action_status = 'C'
1960 /* Start of Bug: 3872211 */
1961 and rpac.assignment_id = paaf.assignment_id
1962 and rppa.payroll_id = paaf.payroll_id
1963 and paaf.assignment_id = p_assignment_id
1964 and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date);
1965 /* End of Bug: 3872211 */
1966
1967
1968 Cursor c_fbt_balance is
1969 select pdb.defined_balance_id
1970 from pay_balance_types pbt,
1971 pay_defined_balances pdb,
1972 pay_balance_dimensions pbd
1973 where pbt.balance_name ='Fringe Benefits'
1974 and pbt.balance_type_id = pdb.balance_type_id
1975 and pdb.balance_dimension_id = pbd.balance_dimension_id
1976 and pbd.legislation_code ='AU'
1977 and pbd.dimension_name ='_ASG_LE_FBT_YTD' --2610141
1978 and pbd.legislation_code = pbt.legislation_code
1979 and pbd.legislation_code = pdb.legislation_code;
1980
1981 /* Bug 5708255 */
1982 -------------------------------------------
1983 -- Added cursor to get value of global FBT_THRESHOLD
1984 --------------------------------------------
1985 CURSOR c_get_fbt_global(c_year_end DATE)
1986 IS
1987 SELECT global_value
1988 FROM ff_globals_f
1989 WHERE global_name = 'FBT_THRESHOLD'
1990 AND legislation_code = 'AU'
1991 AND c_year_end BETWEEN effective_start_date
1992 AND effective_end_date ;
1993
1994
1995
1996 begin
1997
1998 g_debug := hr_utility.debug_enabled;
1999
2000 IF g_debug THEN
2001 hr_utility.set_location('Start of assignment_action_code',1);
2002 END IF;
2003 -------------------------------------------------------------
2004 -- get the paramters for archival process
2005 -------------------------------------------------------------
2006 open get_params(p_payroll_action_id);
2007 fetch get_params
2008 into l_fin_year_start
2009 ,l_fin_year_end
2010 ,l_fbt_year_start
2011 ,l_fbt_year_end
2012 ,l_employee_type
2013 ,l_legal_employer
2014 ,l_assignment_id
2015 ,l_payroll_id
2016 ,l_lst_yr_term
2017 ,l_business_group_id ;
2018 close get_params;
2019
2020 if g_debug then
2021 hr_utility.trace('p_assignment_id :'||l_assignment_id);
2022 hr_utility.trace('p_business_group_id :'||l_business_group_id);
2023 hr_utility.trace('p_legal_employer :' ||l_legal_employer);
2024 hr_utility.trace('p_employee_type :'||l_employee_type);
2025 hr_utility.trace('p_payroll_id :'|| l_payroll_id);
2026 hr_utility.trace('p_fin_year_start :' ||l_fin_year_start);
2027 hr_utility.trace('p_fin_year_end :' ||l_fin_year_end);
2028 hr_utility.trace('p_fbt_year_start :' ||l_fbt_year_start);
2029 hr_utility.trace('p_fbt_year_end :' ||l_fbt_year_end);
2030 END if;
2031
2032 IF (l_lst_yr_term = 'N') THEN
2033 l_lst_yr_start := to_date('01/01/1900','DD/MM/YYYY');
2034 l_lst_fbt_yr_start := to_date('01/01/1900','DD/MM/YYYY');
2035 ELSE
2036 l_lst_yr_start := add_months(l_fin_year_start,-12);
2037 l_lst_fbt_yr_start := l_fbt_year_start;
2038 END IF;
2039 ------------------------------------------
2040
2041 /* Bug 5708255 */
2042 open c_get_fbt_global (add_months(l_fin_year_end,-3)); /* Add_months included for bug 5333143 */
2043 fetch c_get_fbt_global into g_fbt_threshold;
2044 close c_get_fbt_global;
2045
2046 -- Added for bug 3034189
2047 If g_fbt_defined_balance_id = 0 OR g_fbt_defined_balance_id IS NULL Then
2048 Open c_fbt_balance;
2049 Fetch c_fbt_balance into g_fbt_defined_balance_id;
2050 Close c_fbt_balance;
2051 End if;
2052
2053
2054
2055 IF l_assignment_id = '%' THEN -- For multiple Assignments
2056
2057 /* Bug 7138494 - Added Changes for Range Person
2058 - Call Cursor using pay_population_ranges if Range Person Enabled
2059 Else call Old Cursor
2060 */
2061 IF range_person_on
2062 THEN
2063
2064 IF g_debug
2065 THEN
2066 hr_utility.set_location('Range Peron set - Use range cursor',1000);
2067 END IF;
2068 FOR csr_rec IN c_range_asgids(l_assignment_id ,
2069 l_business_group_id ,
2070 l_legal_employer ,
2071 l_employee_type ,
2072 l_payroll_id ,
2073 l_fin_year_start ,
2074 l_fin_year_end ,
2075 l_lst_fbt_yr_start,
2076 l_fbt_year_start ,
2077 l_fbt_year_end,
2078 l_lst_yr_start)
2079 LOOP
2080
2081 IF g_debug THEN
2082 hr_utility.set_location('Calling hr_nonrun_asact.insact for assignment id :'||l_asgid,2);
2083 END if;
2084
2085 OPEN next_action_id;
2086 FETCH next_action_id INTO l_next_action_id;
2087 CLOSE next_action_id;
2088
2089 hr_nonrun_asact.insact(l_next_action_id,csr_rec.assignment_id,p_payroll_action_id,p_chunk,null);
2090
2091 IF g_debug THEN
2092 hr_utility.set_location('After calling hr_nonrun_asact.insact',3);
2093 END if;
2094
2095 END LOOP;
2096
2097 ELSE /* Use Old Logic - No Range Person */
2098
2099 OPEN c_asgids(l_assignment_id ,
2100 l_business_group_id ,
2101 l_legal_employer ,
2102 l_employee_type ,
2103 l_payroll_id ,
2104 l_fin_year_start ,
2105 l_fin_year_end ,
2106 l_lst_fbt_yr_start,
2107 l_fbt_year_start ,
2108 l_fbt_year_end,
2109 l_lst_yr_start);
2110 LOOP
2111 FETCH c_asgids INTO l_asgid;
2112 IF c_asgids%NOTFOUND THEN
2113 close c_asgids;
2114 exit;
2115 ELSE
2116 IF g_debug THEN
2117 hr_utility.set_location('Calling hr_nonrun_asact.insact for assignment id :'||l_asgid,2);
2118 END if;
2119 OPEN next_action_id;
2120 FETCH next_action_id INTO l_next_action_id;
2121 CLOSE next_action_id;
2122 hr_nonrun_asact.insact(l_next_action_id,l_asgid,p_payroll_action_id,p_chunk,null);
2123
2124 IF g_debug THEN
2125 hr_utility.set_location('After calling hr_nonrun_asact.insact',3);
2126 END if;
2127 END IF;
2128 END LOOP;
2129 END IF; /* Range Person Check */
2130
2131 ELSE -- only for Single Assignment
2132 hr_utility.trace('before open');
2133 OPEN c_asgid_only(l_assignment_id ,
2134 l_business_group_id ,
2135 l_legal_employer ,
2136 l_employee_type ,
2137 l_payroll_id ,
2138 l_fin_year_start ,
2139 l_fin_year_end ,
2140 l_lst_fbt_yr_start,
2141 l_fbt_year_start ,
2142 l_fbt_year_end,
2143 l_lst_yr_start);
2144 LOOP
2145 hr_utility.trace('in loop');
2146 FETCH c_asgid_only INTO l_asgid;
2147 IF c_asgid_only%NOTFOUND THEN
2148 CLOSE c_asgid_only;
2149 EXIT;
2150 ELSE
2151 IF g_debug THEN
2152 hr_utility.set_location('Calling hr_nonrun_asact.insact for assignment id :'||l_asgid,2);
2153 END IF;
2154 OPEN next_action_id;
2155 FETCH next_action_id INTO l_next_action_id;
2156 CLOSE next_action_id;
2157 hr_nonrun_asact.insact(l_next_action_id,l_asgid,p_payroll_action_id,p_chunk,NULL);
2158
2159 IF g_debug THEN
2160 hr_utility.set_location('After calling hr_nonrun_asact.insact',3);
2161 END if;
2162 END IF;
2163 END LOOP;
2164 END IF;
2165 IF g_debug THEN
2166 hr_utility.set_location('End of assignment_action_code',4);
2167 END if;
2168
2169 EXCEPTION
2170 WHEN OTHERS THEN
2171 IF g_debug THEN
2172 hr_utility.set_location('error raised in assignment_action_code procedure ',5);
2173 hr_utility.trace(sqlerrm);
2174 END if;
2175 raise;
2176 END;
2177
2178 --------------------------------------------------------------------
2179 -- This procedure is actually used to archive data . It
2180 -- internally calls private procedures to archive balances ,
2181 -- employee details, employer details and supplier details .
2182 --------------------------------------------------------------------
2183 procedure archive_code
2184 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
2185 p_effective_date in date)is
2186 begin
2187 NULL;
2188 END;
2189
2190
2191 --------------------------------------------------------------------
2192 -- This procedure is called during de-iniitalization
2193 -- After inserting assignment_actions this procedure is called
2194 -- It submits the request for running the report
2195 -- And the report displays detials for all the archived assignments.
2196 --------------------------------------------------------------------
2197
2198 PROCEDURE spawn_ps_report
2199 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type) is
2200
2201 ps_request_id NUMBER;
2202 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
2203 l_business_group_id number;
2204 l_start_date date;
2205 l_end_date date;
2206 l_effective_date date;
2207 l_legal_employer number;
2208 l_FINANCIAL_YEAR_code varchar2(10);
2209 l_TEST_EFILE varchar2(10);
2210 l_FINANCIAL_YEAR varchar2(10);
2211 l_legislative_param varchar2(200);
2212 l_count number;
2213 l_print_style VARCHAR2(2);
2214 l_print_together VARCHAR2(80);
2215 l_print_return BOOLEAN;
2216 l_procedure varchar2(50);
2217 l_short_report_name VARCHAR2(30); /* 6839263 */
2218 l_xml_options BOOLEAN ; /* 6839263 */
2219
2220
2221 --------------------------------------------------------------------+
2222 -- Cursor : csr_params
2223 -- Description : Fetches User Parameters from Legislative_paramters
2224 -- column.
2225 --------------------------------------------------------------------+
2226
2227 CURSOR csr_report_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
2228 IS
2229 select pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters) Financial_year
2230 ,pay_core_utils.get_parameter('EMPLOYEE_TYPE',legislative_parameters) Employee_type
2231 ,pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters) legal_employer
2232 ,pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters) Assignment_id
2233 ,pay_core_utils.get_parameter('PAYROLL_ID',legislative_parameters) payroll_id
2234 ,pay_core_utils.get_parameter('LST_YR_TERM',legislative_parameters) lst_yr_term
2235 ,pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters) Business_group_id
2236 ,pay_core_utils.get_parameter('OUTPUT_TYPE',legislative_parameters)p_output_type /* Bug# 6839263 */
2237 from pay_payroll_actions
2238 where payroll_action_id =c_payroll_Action_id;
2239
2240
2241
2242 cursor csr_get_print_options(p_payroll_action_id NUMBER) IS
2243 SELECT printer,
2244 print_style,
2245 decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output
2246 ,number_of_copies /* Bug 4116833 */
2247 FROM pay_payroll_actions pact,
2248 fnd_concurrent_requests fcr
2249 WHERE fcr.request_id = pact.request_id
2250 AND pact.payroll_action_id = p_payroll_action_id;
2251
2252
2253 rec_print_options csr_get_print_options%ROWTYPE;
2254
2255 l_parameters csr_report_params%ROWTYPE;
2256
2257 BEGIN
2258 g_debug :=hr_utility.debug_enabled ;
2259
2260 IF g_debug THEN
2261 hr_utility.set_location('Start of spawn_ps_report',1);
2262 END if;
2263
2264 l_count :=0;
2265 ps_request_id :=-1;
2266
2267 -- Set User Parameters for Report.
2268
2269 OPEN csr_report_params(p_payroll_action_id);
2270 FETCH csr_report_params INTO l_parameters;
2271 CLOSE csr_report_params;
2272
2273 /* Start of 6839263 */
2274 IF l_parameters.p_output_type = 'XML_PDF' then
2275 l_short_report_name := 'PYAURECPR_XML';
2276
2277 l_xml_options := fnd_request.add_layout
2278 (template_appl_name => 'PAY',
2279 template_code => 'PYAURECPR_XML',
2280 template_language => 'en',
2281 template_territory => 'US',
2282 output_format => 'PDF');
2283
2284 ELSE
2285 l_short_report_name := 'PYAURECPR';
2286 END IF;
2287 /* End of 6839263 */
2288
2289 IF g_debug THEN
2290 hr_utility.set_location('in BG_ID '||l_parameters.Business_group_id,1);
2291 hr_utility.set_location('in payroll_parameters.id '||l_parameters.payroll_id,3);
2292 hr_utility.set_location('in asg_id '||l_parameters.assignment_id,4);
2293 hr_utility.set_location('in legal employer '||l_parameters.legal_employer,8);
2294 hr_utility.set_location('in emp_type '||l_parameters.employee_type,14);
2295 hr_utility.set_location('fin_year'||l_parameters.Financial_year,15);
2296 hr_utility.set_location('lst_yr_trm'||l_parameters.lst_yr_term,16);
2297 end if;
2298
2299 IF g_debug THEN
2300 hr_utility.set_location('Afer payroll action ' || p_payroll_action_id , 125);
2301 hr_utility.set_location('Before calling report',24);
2302 END IF;
2303
2304 OPEN csr_get_print_options(p_payroll_action_id);
2305 FETCH csr_get_print_options INTO rec_print_options;
2306 CLOSE csr_get_print_options;
2307
2308 l_print_together := nvl(fnd_profile.value('CONC_PRINT_TOGETHER'), 'N');
2309 --
2310 -- Set printer options
2311 l_print_return := fnd_request.set_print_options
2312 (printer => rec_print_options.printer,
2313 style => rec_print_options.print_style,
2314 copies => rec_print_options.number_of_copies, /* Bug 4116833 */
2315 save_output => hr_general.char_to_bool(rec_print_options.save_output),
2316 print_together => l_print_together);
2317 -- Submit report
2318 IF g_debug THEN
2319 hr_utility.set_location('payroll_action id '|| p_payroll_action_id,25);
2320 END IF;
2321
2322 ps_request_id := fnd_request.submit_request
2323 ('PAY',
2324 l_short_report_name,
2325 NULL,
2326 NULL,
2327 FALSE,
2328 'P_PAYROLL_ACTION_ID='||p_payroll_action_id,
2329 'P_ASSIGNMENT_ID='||l_parameters.assignment_id,
2330 'P_BUSINESS_GROUP_ID='||l_parameters.business_group_id,
2331 'P_EMPLOYEE_TYPE='||l_parameters.employee_type,
2332 'P_FINANCIAL_YEAR='||l_parameters.Financial_year,
2333 'P_LST_YR_TERM='||l_parameters.lst_yr_term,
2334 'P_PAYROLL_ID='||l_parameters.payroll_id,
2335 'P_REGISTERED_EMPLOYER='||l_parameters.legal_employer);
2336
2337 IF g_debug THEN
2338 hr_utility.set_location('End of spawn_ps_report',4);
2339 END IF;
2340
2341 EXCEPTION
2342 WHEN others THEN
2343 IF g_debug THEN
2344 hr_utility.set_location('error raised in spawn_ps_report procedure ',5);
2345 END if;
2346 RAISE;
2347 END;
2348
2349
2350
2351
2352 begin
2353 g_debug := hr_utility.debug_enabled;
2354 g_pre01jul1983_value :=0;
2355 g_post30jun1983_value :=0;
2356 g_etp_gross :=0;
2357 g_etp_tax :=0;
2358 g_assessable :=0;
2359 g_lump_sum_e :=0;
2360 g_total_other_income :=0;
2361 g_total_gross :=0;
2362 g_total_workplace :=0; /* 4015082 */
2363 g_total_cdep :=0;
2364 g_total_allowance :=0;
2365 g_total_fbt :=0;
2366 g_total_gross :=0;
2367 x :=0;
2368 g_bal_dim_level := 'N';
2369
2370 end pay_au_recon_summary;
[Home] [Help]