[Home] [Help]
PACKAGE BODY: APPS.PAY_AU_RECON_SUMMARY
Source
1 package body pay_au_recon_summary as
2 /* $Header: pyauprec.pkb 120.43.12020000.6 2012/12/07 03:22:36 skshin 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 *** 02-Dec-08 skshin 115.91 Bug#7571001 Enabled Group Level Dimension for Allowances
109 *** 20-JAN-09 skshin 115.92 Bug#7571001 Modified cursors as suggested by comments in bug 7571001 and added comments
110 *** 28-Apr-09 pmatamsr 115.93 Bug#8441044 Cursor c_get_pay_effective_date is modified to consider Lump Sum E payments for payment summary gross calculation
111 *** for action types 'B' and 'I'.
112 *** 23-Jun-09 pmatamsr 115.94 Bug#8587013 Added changes to support archival of balances 'Reportable Employer Superannuation Contributions' and
113 *** 'Exempt Foreign Employment Income' introduced as part of PS changes 2009 and removed the reporting of Other Income balance.
114 *** 07-Sep-09 pmatamsr 115.95 Bug#8769345 Modified functions populate_bal_ids ,etp_details and procedure get_assgt_curr_term_values_bbr to support ETP Taxable and Tax Free
115 *** balances introduced as part of statutory changes to super rollover.
116 *** 19-Nov-09 skshin 115.98 Bug#8711855 Modified Total_Lump_Sum_E_Payments procedure to call get_lumpsumE_value function and changed g_input_term_details_table index ids
117 *** 15-Dec-09 pmatamsr 115.99 Bug#9190980 Added a new argument v_adj_lump_sum_pre_tax in call to get_lumpsumE_value function.
118 *** 13-Jan-09 pmatamsr 115.100 Bug#9226023 Added logic to support the calculation of ETP taxable and Tax Free components for terminated employees processed
119 *** before applying the patch 8769345.
120 *** 28-SEP-10 dduvvuri 115.101 Bug#9147438 Changes done for Foreign Worker EOY reporting enhancement
121 *** 29-SEP-10 dduvvuri 115.102 Bug#9147438 Fixed certain FBT threshold related issues and no data found errors
122 *** 20-OCT-10 dduvvuri 115.104 Bug#10209338 Initialiased FW FBT and Reporting amounts to 0 in procedure get_value_bbr to ensure correct values are
123 *** returned when multiple assignments are present.
124 *** 22-Nov-10 skshin 115.105 Bug#10143762 Adjusted Exempt Foreign Income from Gross_Earnings for both INB and FW type.
125 *** Bug#10216064 LT12_Curr retro and LT12_Curr retro Tax are to be reported on each type of payment summary based on
126 *** assignment type of original period. The other retros and associated retro Taxes are to be reported on INB payment summary.
127 *** 01-Dec-10 avenkatk 115.106 Bug#10331262 Made changes for FW Leave and Termination payment reporting
128 *** Bug#10209338 Also corrected RESC,RFB and Lump Sum D Reporting issues.
129 *** get_group_values_bbr - Removed all FW retreival
130 *** Function etp_details - corrected index for ETP tax free, taxable reporting
131 *** 07-Jun-11 prasrang 115.107 Bug#12400821 Performance Improvement done for westpac customer
132 *** 07-Jul-11 dduvvuri 115.109 Bug#12725161 This version is a rollback of 115.108 version with the bug 12698821 fixed in a different way to avoid eoy related issues
133 *** 26-sep-11 dduvvuri 115.110 Bug#12400821 Performance improvements in EXISTS clause in all 3 assignment_action_code cursors done for westpac customer
134 *** 02-Feb-12 skshin 115.111 Bug#13362286 Add Retro Earnings Additional GT12 balance for Lump Sum E and foreign worker
135 *** 26-Apr-12 prasrang 115.112 Bug#13989281 Modified the indexes of internal tables g_input_term_details_table and g_result_term_details_table.
136 *** 22-Jun-12 jmarupil 115.113 Bug#14060570 Modified the condition for allowances retro balances
137 *** 7-Dec-12 skshin 115.115 Bug#14703826 Modified to retrieve new ETP balances for Excluded and Non Excluded
138 */
139
140 g_debug boolean; --Bug#3193479
141 g_pre01jul1983_value number;
142 g_post30jun1983_value number;
143 g_etp_gross number;
144 g_etp_tax number;
145 g_assessable number;
146 g_lump_sum_e number;
147 g_total_cdep number;
148 g_total_allowance number;
149 g_total_fbt number;
150 g_total_gross number;
151 /* Begin 8587013 - Package variables declared to hold the totals of RESC and Exempt Foreign Employment Income balances*/
152 g_total_resc number;
153 g_total_foreign_income number;
154 /* End 8587013 */
155 g_business_group_id hr_organization_units.organization_id%type;
156 x number;
157 g_total_workplace number; /*4015082 */
158 g_balance_type_tab g_bal_type_tab;
159 g_fbt_threshold ff_globals_f.global_value%TYPE ; /* Bug 5708255 */
160
161 /* Changes for 9147438 start */
162 type fw_gross_type_table is table of varchar2(1) index by binary_integer;
163 t_fw_gross_type fw_gross_type_table;
164
165 f_fw_date_tab_g pay_au_foreign_workers.tab_fw_dates;
166 j_fw_date_tab_g pay_au_foreign_workers.tab_fw_dates;
167 g_fw_date_tab pay_au_foreign_workers.tab_fw_dates;
168 g_fw_total_resc number;
169 g_fw_total_cdep number;
170 g_fw_total_workplace number;
171 g_balance_type_id pay_balance_types.balance_type_id%type;
172 p_fw_fbt_bal_type_tab pay_au_foreign_workers.tab_bal_type;
173 g_fw_fbt_balance number;
174 g_fw_reporting_amt number;
175 /* Changes for 9147438 end */
176
177 /*Bug 8587013 - Removed Other Income balance from Gross Calculation*/
178 Function total_gross
179 return number is
180 l_total_earnings_asg_ytd number;
181 l_allowance_total number;
182 l_cdep_asg_ytd number;
183 l_other_income_asg_ytd number;
184 l_lump_sum_e_payments_asg_ytd number;
185 l_total_gross number;
186
187 begin
188
189 -- Bug: 3186840 Check against the global Dimension level value included to return the correct value.
190 IF (g_bal_dim_level = 'N') THEN
191 l_total_earnings_asg_ytd:= get_bal_value_new(g_db_id_et) + get_bal_value_new(g_db_id_lpm);
192 -- Bug# 3193479
193 END IF;
194 l_total_gross:=l_total_earnings_asg_ytd-greatest(g_total_allowance,0)-g_total_cdep
195 -g_lump_sum_e + g_total_workplace - g_total_foreign_income; /*4015082, Exempt Foreign Employment Income 10143762*/
196 g_total_gross := l_total_gross;
197 return l_total_gross;
198
199 end total_gross;
200
201
202 /**************
203 bug 7571001 : new function - adjust_retro_group_allowances
204 This is called from get_total_allowances function for group level calculation to adjust allowance
205 Very similar to pay_au_payment_summary.adjust_retro_allowances excpet for an individual assignment
206 **************/
207
208 function adjust_retro_group_allowances(t_allowance_balance IN OUT NOCOPY pay_au_payment_summary.t_allowance_balance%type
209 ,p_year_start in DATE
210 ,p_year_end in DATE
211 ,p_registered_employer in NUMBER --2610141
212 )
213 return number is
214
215 /* This cursor is similar to Get_retro_Entry_ids cursor in pay_au_payment_summary.adjust_retro_allowances
216 except paa.assignment_id = c_assignment_id
217 */
218 /* Bug: 12400821 - Performance Improvement done */
219 CURSOR Get_retro_Entry_ids(c_year_start DATE,
220 c_year_end DATE)
221 IS
222 SELECT /*+ ORDERED */
223 PEE.ELEMENT_ENTRY_ID ELEMENT_ENTRY_ID,
224 PPA.DATE_EARNED DATE_EARNED,
225 PEE.ASSIGNMENT_ID ASSIGNMENT_ID,
226 PAC.TAX_UNIT_ID,
227 PDB.BALANCE_TYPE_ID
228 FROM
229 PAY_BAL_ATTRIBUTE_DEFINITIONS PBAD ,
230 PAY_BALANCE_ATTRIBUTES PBA ,
231 PAY_DEFINED_BALANCES PDB ,
232 PAY_BALANCE_DIMENSIONS PBD ,
233 PAY_BALANCE_FEEDS_F PBF ,
234 PAY_INPUT_VALUES_F PIV,
235 PAY_ELEMENT_ENTRIES_F PEE ,
236 PAY_ELEMENT_TYPES_F PET ,
237 PER_ALL_ASSIGNMENTS_F PAA ,
238 PER_PERIODS_OF_SERVICE PPS ,
239 PAY_RUN_RESULTS PRR ,
240 PAY_ASSIGNMENT_ACTIONS PAC ,
241 PAY_PAYROLL_ACTIONS PPA
242 WHERE pbad.attribute_name = 'AU_EOY_ALLOWANCE'
243 AND pbad.legislation_code = 'AU'
244 AND pbad.attribute_id = pba.attribute_id
245 AND pba.defined_balance_id = pdb.defined_balance_id
246 AND pbd.balance_dimension_id = pdb.balance_dimension_id
247 AND pbd.dimension_name = '_ASG_LE_YTD'
248 and pbd.legislation_code = 'AU'
249 AND pdb.balance_type_id = pbf.balance_type_id
250 AND pbf.input_value_id = piv.input_value_id
251 AND piv.element_type_id = pet.element_type_id
252 AND pee.element_type_id = piv.element_type_id
253 AND pps.PERIOD_OF_SERVICE_ID = paa.PERIOD_OF_SERVICE_ID
254 AND NVL(pps.actual_termination_date,c_year_end)
255 BETWEEN paa.effective_start_date AND paa.effective_end_date
256 AND pac.payroll_action_id = ppa.payroll_Action_id
257 AND pac.assignment_id = paa.assignment_id
258 AND pac.tax_unit_id = p_registered_employer
259 AND ppa.effective_date BETWEEN c_year_start AND c_year_end
260 AND pac.assignment_Action_id = prr.assignment_Action_id
261 AND prr.element_type_id=pet.element_type_id
262 AND prr.element_type_id = pee.element_type_id
263 AND pee.element_entry_id=prr.source_id
264 AND pee.creator_type in ('EE','RR')
265 AND pee.assignment_id = paa.assignment_id
266 AND paa.business_group_id = ppa.business_group_id
267 AND paa.business_group_id = pet.business_group_id
268 AND ppa.action_status='C'
269 AND pac.action_status='C'
270 AND ppa.date_earned between pee.effective_start_date and pee.effective_end_date
271 AND ppa.date_earned BETWEEN pet.effective_start_date AND pet.effective_end_date
272 AND ppa.date_earned between pbf.effective_start_date and pbf.effective_end_date
273 AND ppa.date_earned between piv.effective_start_date and piv.effective_end_date
274 ;
275
276
277 Cursor Get_Retro_allowances(c_element_entry_id pay_element_entries_f.element_entry_id%type,
278 c_balance_type_id pay_defined_balances.defined_balance_id%type)
279 IS
280 select NVL(pbt.reporting_name,pbt.balance_name) balance_name /* Bug 5743196 Added nvl */
281 ,prv.result_value balance_value
282 from
283 pay_element_entries_f pee,
284 pay_run_results prr,
285 pay_run_result_values prv,
286 pay_element_types_f pet,
287 pay_balance_types pbt
288 ,PAY_BALANCE_FEEDS_F pbf
289 ,pay_input_values_f piv
290 where
291 pee.element_entry_id=c_element_entry_id
292 and prv.run_result_id=prr.run_result_id
293 AND pee.element_entry_id=prr.source_id
294 AND prr.element_type_id=pet.element_type_id
295 AND pbt.balance_type_id = c_balance_type_id
296 AND pbt.balance_type_id = pbf.balance_type_id
297 AND pbf.input_value_id = piv.input_value_id
298 AND piv.element_type_id = pet.element_type_id
299 AND pee.effective_start_date between pet.effective_start_date and pet.effective_end_date
300 AND pee.effective_start_date between pbf.effective_start_date and pbf.effective_end_date
301 AND pee.effective_start_date between piv.effective_start_date and piv.effective_end_date
302 ;
303
304 CURSOR get_legislation_rule
305 IS
306 SELECT plr.rule_mode
307 FROM pay_legislation_rules plr
308 WHERE plr.legislation_code = 'AU'
309 AND plr.rule_type ='ADVANCED_RETRO';
310
311 rec_retro_Allowances Get_retro_Allowances%ROWTYPE;
312 TYPE r_ret_allowances IS RECORD(balance_name pay_balance_types.balance_name%TYPE,
313 balance_value Number);
314 TYPE tab_ret_allowances IS TABLE OF r_ret_allowances INDEX BY BINARY_INTEGER;
315 t_ret_allowances tab_ret_allowances;
316
317 rec_ret_entry_ids Get_retro_Entry_ids%ROWTYPE;
318
319
320 ret_counter Number;
321 retro_start date;
322 retro_end date;
323 x number;
324 orig_eff_date date;
325 retro_eff_date date;
326 time_span varchar2(10);
327 retro_type varchar2(50);
328 l_adv_retro_flag pay_legislation_rules.rule_mode%TYPE;
329
330 Begin
331 g_debug := hr_utility.debug_enabled;
332 ret_counter := 1;
333
334 OPEN get_legislation_rule;
335 FETCH get_legislation_rule INTO l_adv_retro_flag;
336 IF get_legislation_rule%NOTFOUND THEN
337 l_adv_retro_flag := 'N';
338 END IF;
339 CLOSE get_legislation_rule;
340
341 /* Retropay by element - logic for Retropay By Element is used */
342
343 IF l_adv_retro_flag <> 'Y'
344 THEN
345
346 OPEN Get_retro_Entry_ids(p_year_start,p_year_end);
347 LOOP
348 FETCH Get_retro_Entry_ids INTO rec_ret_entry_ids;
349 IF Get_retro_Entry_ids%NOTFOUND Then
350 IF g_debug THEN
351 hr_utility.set_location('Get_retro_Entry_Id: not found',1);
352 END if;
353 Exit;
354 End If;
355 IF g_debug THEN
356 hr_utility.set_location('Calling Get Retro Periods',2);
357 END if;
358
359 x:=pay_au_paye_ff.get_retro_period(rec_ret_entry_ids.element_entry_id,
360 rec_ret_entry_ids.date_earned,
361 p_registered_employer, /*Bug 4418107*/
362 retro_start,
363 retro_end);
364
365 IF g_debug THEN
366 hr_utility.set_location('Back from call to Get Retro Periods',3);
367 END if;
368
369 IF months_between(rec_ret_entry_ids.date_earned,retro_end) > 12 then
370 IF g_debug THEN
371 hr_utility.set_location('Getting Retro Allowance Greater than 12 months',4);
372 END if;
373
374 OPEN Get_retro_Allowances(rec_ret_entry_ids.element_entry_id, rec_ret_entry_ids.balance_type_id);
375 FETCH Get_retro_Allowances INTO rec_retro_Allowances;
376 CLOSE Get_retro_Allowances;
377
378
379 If NVL(rec_retro_Allowances.balance_value,0) <> 0 Then
380
381 t_ret_allowances(ret_counter).balance_name := rec_retro_Allowances.balance_name;
382 t_ret_allowances(ret_counter).balance_value := rec_retro_Allowances.balance_value;
383 ret_counter := ret_counter+1;
384 End If;
385
386 END IF;
387 END LOOP;
388
389 CLOSE Get_retro_Entry_ids;
390
391
392 if t_ret_allowances.count > 0 then
393 For i in 1..t_ret_allowances.last
394 LOOP
395 For j in 1..t_allowance_balance.last
396 LOOP
397 if t_ret_allowances(i).balance_name = t_allowance_balance(j).balance_name then
398 t_allowance_balance(j).balance_value := t_allowance_balance(j).balance_value - t_ret_allowances(i).balance_value;
399 exit;
400 end if;
401 END LOOP;
402 END LOOP;
403 end if;
404
405 t_ret_allowances.delete;
406
407 /*Enh Retro If Retrospective Payment Greater than 12 months then it is deducted from total allowance*/
408
409 ELSE
410 OPEN Get_retro_Entry_ids(p_year_start,p_year_end);
411 LOOP
412 FETCH Get_retro_Entry_ids INTO rec_ret_entry_ids;
413 IF Get_retro_Entry_ids%NOTFOUND Then
414 IF g_debug THEN
415 hr_utility.set_location('Get_retro_Entry_Id: not found',1);
416 END if;
417 Exit;
418 End If;
419 IF g_debug THEN
420 hr_utility.set_location('Calling Get Retro Time Span',2);
421 END if;
422
423 x:= pay_au_paye_ff.get_retro_time_span(rec_ret_entry_ids.element_entry_id,
424 rec_ret_entry_ids.date_earned,
425 rec_ret_entry_ids.tax_unit_id,
426 retro_start,
427 retro_end,
428 orig_eff_date,
429 retro_eff_date,
430 time_span,
431 retro_type);
432 IF g_debug THEN
433 hr_utility.set_location('Back from call to Get Retro Time Span',3);
434 END if;
435 IF time_span ='GT12' then
436 IF g_debug THEN
437 hr_utility.set_location('Getting Retro Allowance Greater than 12 months',4);
438 END if;
439 OPEN Get_retro_Allowances(rec_ret_entry_ids.element_entry_id, rec_ret_entry_ids.balance_type_id);
440 FETCH Get_retro_Allowances INTO rec_retro_Allowances;
441 CLOSE Get_retro_Allowances;
442
443 If NVL(rec_retro_Allowances.balance_value,0) <> 0 Then
444 t_ret_allowances(ret_counter).balance_name := rec_retro_Allowances.balance_name;
445 t_ret_allowances(ret_counter).balance_value := rec_retro_Allowances.balance_value;
446 ret_counter := ret_counter+1;
447
448 End If;
449 END IF;
450 END LOOP;
451
452 CLOSE Get_retro_Entry_ids;
453
454 if t_ret_allowances.count > 0 then
455 For i in 1..t_ret_allowances.last
456 LOOP
457 For j in 1..t_allowance_balance.last
458 LOOP
459
460 if t_ret_allowances(i).balance_name = t_allowance_balance(j).balance_name then
461 t_allowance_balance(j).balance_value := t_allowance_balance(j).balance_value - t_ret_allowances(i).balance_value;
462
463 exit;
464 end if;
465 END LOOP;
466 END LOOP;
467 end if;
468
469 t_ret_allowances.delete;
470
471 END IF;
472 return 1;
473
474 End adjust_retro_group_allowances;
475
476 /***************
477 bug 7571001 - get_total_allowances function is entirely changed to enable group level reporting
478 pay_balance_pkg.get_value is called with different input values based on g_bal_dim_level
479 then pay_au_payment_summary.adjust_retro_allowances is called for 'N' dimension level or
480 adjust_retro_group_allowances is called for 'G'.
481 ***************/
482 Function get_total_allowances ( p_year_start DATE,
483 p_year_end DATE,
484 p_assignment_id pay_assignment_actions.assignment_id%type,
485 p_assignment_action_id pay_assignment_actions.assignment_id%type,
486 p_tax_unit_id hr_all_organization_units.organization_id%type)
487 return number is
488
489 CURSOR get_alw_balance_name (p_def_bal_id pay_defined_balances.defined_balance_id%type) IS
490 select NVL(pbt.reporting_name,pbt.balance_name)
491 from pay_balance_types pbt, pay_defined_balances pdb
492 where pdb.defined_balance_id = p_def_bal_id
493 and pdb.balance_type_id = pbt.balance_type_id
494 ;
495
496 t_allowance_balance pay_au_payment_summary.t_allowance_balance%type;
497 l_balance_name pay_balance_types.balance_name%type;
498 cnt number := 1;
499 counter number := 1;
500 i number;
501
502 begin
503
504 g_debug :=hr_utility.debug_enabled ;
505
506 if g_debug then
507 hr_utility.set_location('Entering get_total_allowances for assingment_id '||p_assignment_id, 0);
508 end if;
509
510 IF (g_bal_dim_level = 'N') THEN
511
512 IF g_input_alw_table.count > 0 THEN
513
514 g_result_alw_table.delete;
515 t_allowance_balance.delete;
516
517 pay_balance_pkg.get_value(p_assignment_action_id => p_assignment_action_id,
518 p_defined_balance_lst=>g_input_alw_table,
519 p_context_lst =>g_context_table,
520 p_output_table=>g_result_alw_table);
521
522 FOR i in g_result_alw_table.first .. g_result_alw_table.last LOOP
523
524 IF g_result_alw_table.exists(i) THEN
525 if nvl(g_result_alw_table(i).balance_value,0) >0 then
526
527 open get_alw_balance_name(g_result_alw_table(i).defined_balance_id);
528 fetch get_alw_balance_name into l_balance_name;
529 close get_alw_balance_name;
530
531 t_allowance_balance(cnt).balance_name := l_balance_name;
532 t_allowance_balance(cnt).balance_value := g_result_alw_table(i).balance_value;
533
534 if g_debug then
535 hr_utility.trace('N t_allowance_ balance name ('||cnt||') = '|| t_allowance_balance(cnt).balance_name);
536 hr_utility.trace('N t_allowance_balance value ('||cnt||') = '||t_allowance_balance(cnt).balance_value);
537 end if;
538
539 cnt := cnt + 1;
540
541 end if;
542 END IF;
543
544 END LOOP;
545
546 IF t_allowance_balance.count >0 THEN
547
548 i := pay_au_payment_summary.adjust_retro_allowances
549 (t_allowance_balance
550 ,p_year_start
551 ,p_year_end
552 ,p_assignment_id
553 ,p_tax_unit_id --2610141
554 );
555
556
557 For i in t_allowance_balance.first .. t_allowance_balance.last LOOP
558 If t_allowance_balance.EXISTS(i) Then
559 g_total_allowance:=g_total_allowance+nvl(t_allowance_balance(i).balance_value,0);
560 if g_debug then
561 hr_utility.trace('N1 t_allowance_ balance name ('||cnt||') = '|| t_allowance_balance(i).balance_name);
562 hr_utility.trace('N1 t_allowance_balance value ('||cnt||') = '||t_allowance_balance(i).balance_value);
563 end if;
564 End If;
565 END LOOP;
566 g_fw_allowance_tab:=t_allowance_balance;
567 ELSE
568 g_total_allowance := 0;
569 END IF;
570 END IF;
571
572
573
574 ELSIF (g_bal_dim_level = 'G') THEN
575
576
577 IF g_input_group_alw_table.count > 0 THEN
578
579 g_result_group_alw_table.delete;
580 t_allowance_balance.delete;
581
582 pay_balance_pkg.get_value(p_assignment_action_id => p_assignment_action_id,
583 p_defined_balance_lst => g_input_group_alw_table,
584 p_context_lst => g_context_table,
585 p_output_table => g_result_group_alw_table);
586
587 FOR i in g_result_group_alw_table.first .. g_result_group_alw_table.last LOOP
588
589 IF g_result_group_alw_table.exists(i) THEN
590 if nvl(g_result_group_alw_table(i).balance_value,0) >0 then
591
592 open get_alw_balance_name(g_result_group_alw_table(i).defined_balance_id);
593 fetch get_alw_balance_name into l_balance_name;
594 close get_alw_balance_name;
595
596 t_allowance_balance(counter).balance_name := l_balance_name;
597 t_allowance_balance(counter).balance_value := g_result_group_alw_table(i).balance_value;
598
599 if g_debug then
600 hr_utility.trace('G t_allowance_ balance name ('||counter||') = '|| t_allowance_balance(counter).balance_name);
601 hr_utility.trace('G t_allowance_balance value ('||counter||') = '||t_allowance_balance(counter).balance_value);
602 end if;
603
604 counter := counter + 1;
605
606 end if;
607 END IF;
608
609 END LOOP;
610
611 IF t_allowance_balance.count >0 THEN
612 i := adjust_retro_group_allowances
613 (t_allowance_balance
614 ,p_year_start
615 ,p_year_end
616 ,p_tax_unit_id --2610141
617 );
618
619
620 For i in t_allowance_balance.first .. t_allowance_balance.last LOOP
621 If t_allowance_balance.EXISTS(i) Then
622 g_total_allowance:=g_total_allowance+nvl(t_allowance_balance(i).balance_value,0);
623 if g_debug then
624 hr_utility.trace('G1 t_allowance_ balance name ('||cnt||') = '|| t_allowance_balance(i).balance_name);
625 hr_utility.trace('G1 t_allowance_balance value ('||cnt||') = '||t_allowance_balance(i).balance_value);
626 end if;
627 End If;
628 END LOOP;
629 ELSE
630 g_total_allowance := 0;
631 END IF;
632
633 END IF;
634
635 END IF;
636
637 return nvl(g_total_allowance,0);
638
639 if g_debug then
640 hr_utility.set_location('Returned g_total_allowace : '||nvl(g_total_allowance,0), 888);
641 hr_utility.set_location('Leaving get_total_allowances for assingment_id '||p_assignment_id, 999);
642 end if;
643
644 end get_total_allowances;
645
646
647
648 Function get_total_fbt(c_year_start DATE,
649 c_assignment_id pay_assignment_actions.assignment_id%type,
650 p_tax_unit_id hr_all_organization_units.organization_id%TYPE,
651 c_fbt_rate ff_globals_f.global_value%TYPE,
652 c_ml_rate ff_globals_f.global_value%TYPE,
653 p_termination VARCHAR2)
654 return number is
655
656 l_total_fbt number;
657 l_reporting_amt number;
658 l_fbt_rate number;
659 l_medicare_levy number;
660 l_fbt_ratio number;
661 l_max_asg_action_id pay_assignment_actions.assignment_action_id%type;
662 l_fw_reporting_amt number;
663 /* Changes for 9147438 start */
664 l_fw_f_type varchar2(1);
665 l_fw_j_type varchar2(1);
666 f_fw_date_tab pay_au_foreign_workers.tab_fw_dates;
667 j_fw_date_tab pay_au_foreign_workers.tab_fw_dates;
668 l_fw_fbt_output_tab pay_balance_pkg.t_detailed_bal_out_tab;
669 /* Changes for 9147438 end */
670
671 /* Bug: 3603495 - Performance Fix - Introduced per_assignments_f and its joins in the following cursor */
672 /* Bug: 4015571 - Modified cursor c_max_asg_action_id - Modified action_type join in sub query
673 to restrict the max action_sequence fetch to types 'Q','R','B','I'
674 Bug: 4256506 - Changed cursor c_max_asg_action_id. Merged sub query to fetch max action sequemce in main query. Done for
675 better performance.
676 */
677 /* Bug: 12400821 - Performance Improvement done */
678
679 cursor c_max_asg_action_id (c_assignment_id per_all_assignments_f.assignment_id%TYPE,
680 c_business_group_id hr_all_organization_units.organization_id%TYPE,
681 c_tax_unit_id hr_all_organization_units.organization_id%TYPE,
682 c_year_start date,
683 c_year_end date ) is
684 select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
685 from pay_assignment_actions paa
686 , pay_payroll_actions ppa
687 , per_assignments_f paf
688 where paa.assignment_id = paf.assignment_id
689 and paf.assignment_id = c_assignment_id
690 and paa.assignment_id = c_assignment_id
691 and ppa.payroll_action_id = paa.payroll_action_id
692 and ppa.effective_date between c_year_start and c_year_end
693 and ppa.payroll_id = paf.payroll_id
694 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
695 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
696 and paa.action_status='C'
697 AND paa.tax_unit_id = c_tax_unit_id;
698
699
700 /* Bug 5708255 */
701 -------------------------------------------
702 -- Added cursor to get value of global FBT_THRESHOLD
703 --------------------------------------------
704 CURSOR c_get_fbt_global(c_year_end DATE)
705 IS
706 SELECT global_value
707 FROM ff_globals_f
708 WHERE global_name = 'FBT_THRESHOLD'
709 AND legislation_code = 'AU'
710 AND c_year_end BETWEEN effective_start_date
711 AND effective_end_date ;
712
713
714 begin
715 /* Changes for 9147438 start */
716 l_fw_f_type:='N';
717 l_fw_j_type:='N';
718 g_fw_reporting_amt :=0;
719 /* Changes for 9147438 end */
720
721 --- Bug#3213539-------------------------------------------
722 IF p_termination IS NULL THEN
723 open c_max_asg_action_id ( c_assignment_id, --Bug# 3193479
724 g_business_group_id,
725 p_tax_unit_id,
726 add_months(c_year_start,-3),
727 add_months(c_year_start,9)-1); --Bug3693034 - Modified to fetch action upto 31-Mar
728 fetch c_max_asg_action_id into l_max_asg_action_id;
729 close c_max_asg_action_id;
730
731 ELSE
732 open c_max_asg_action_id ( c_assignment_id, --Bug# 3193479
733 g_business_group_id,
734 p_tax_unit_id,
735 add_months(c_year_start,-3),
736 (c_year_start-1));
737 fetch c_max_asg_action_id into l_max_asg_action_id;
738 close c_max_asg_action_id;
739 END IF;
740
741 /* Bug#3525563 - Added IF condition to call pay_balance_pkg.get_value when l_max_asg_action_id is not null. */
742 IF l_max_asg_action_id is not null then
743 l_total_fbt := pay_balance_pkg.get_value(g_fbt_defined_balance_id,
744 l_max_asg_action_id,p_tax_unit_id, null,null,null,null);
745 ELSE
746 l_total_fbt := 0;
747 END IF;
748 /* End of Bug#3525563 */
749
750 /* Bug 5708255 */
751 open c_get_fbt_global (add_months(add_months(c_year_start,9)-1,-3)); /* Add_months included for bug 5333143 */
752 fetch c_get_fbt_global into g_fbt_threshold;
753 close c_get_fbt_global;
754
755
756 ------------End of Bug#3213539 ---------------------
757 IF l_total_fbt <= to_number(g_fbt_threshold) THEN /* Bug 5708255 */
758 l_total_fbt := 0;
759 END IF;
760
761 l_fbt_rate := to_number(c_fbt_rate);
762 l_medicare_levy :=to_number(c_ml_rate);
763
764 l_fbt_ratio:=1-(l_fbt_rate+l_medicare_levy);
765
766 if l_fbt_ratio <> 0 then
767 l_reporting_amt := l_total_fbt/l_fbt_ratio;
768 else
769 l_reporting_amt := 0;
770 end if;
771
772 l_reporting_amt := round(l_reporting_amt,2);
773
774 l_reporting_amt := nvl(l_reporting_amt,0);
775
776 /* Changes for 9147438 start */
777 g_total_fbt := l_total_fbt;
778 if (to_number(to_char(c_year_start,'YYYY')) >= 2010) then
779
780 l_fw_fbt_output_tab.delete;
781 t_fw_gross_type.delete;
782 f_fw_date_tab.delete;
783 j_fw_date_tab.delete;
784
785 IF p_termination IS NOT NULL THEN
786
787 l_fw_f_type := pay_au_foreign_workers.check_foreign_worker(c_assignment_id, p_tax_unit_id, add_months(c_year_start,-3), (c_year_start - 1),
788 pay_au_foreign_workers.g_fw_f_type);
789 l_fw_j_type := pay_au_foreign_workers.check_foreign_worker(c_assignment_id, p_tax_unit_id, add_months(c_year_start,-3), (c_year_start - 1),
790 pay_au_foreign_workers.g_fw_j_type);
791
792
793 if l_fw_f_type <> 'N' or l_fw_j_type <> 'N' then
794
795 if l_fw_f_type = 'Y' then
796 pay_au_foreign_workers.get_foreign_worker_dates(c_assignment_id, p_tax_unit_id, add_months(c_year_start,-3), (c_year_start - 1),
797 pay_au_foreign_workers.g_fw_f_type, f_fw_date_tab);
798 end if;
799
800 if l_fw_j_type = 'Y' then
801 pay_au_foreign_workers.get_foreign_worker_dates(c_assignment_id, p_tax_unit_id, add_months(c_year_start,-3), (c_year_start - 1),
802 pay_au_foreign_workers.g_fw_j_type, j_fw_date_tab);
803 end if;
804
805 if f_fw_date_tab.count > 0 then
806 t_fw_gross_type(1) := 'F';
807 if j_fw_date_tab.count >0 then
808 t_fw_gross_type(2) := 'J';
809 end if;
810 elsif j_fw_date_tab.count > 0 then
811 t_fw_gross_type(1) := 'J';
812 end if;
813
814 for i_idx in t_fw_gross_type.first .. t_fw_gross_type.last loop
815
816 IF t_fw_gross_type(i_idx) = 'F' THEN
817
818 pay_au_foreign_workers.get_foreign_payment_amounts(c_assignment_id,
819 p_tax_unit_id,
820 f_fw_date_tab,
821 add_months(c_year_start,-3),
822 (c_year_start - 1),
823 pay_au_foreign_workers.g_fw_f_type,
824 p_fw_fbt_bal_type_tab,
825 l_fw_fbt_output_tab) ;
826
827 ELSIF t_fw_gross_type(i_idx) = 'J' THEN
828 pay_au_foreign_workers.get_foreign_payment_amounts(c_assignment_id,
829 p_tax_unit_id,
830 j_fw_date_tab,
831 add_months(c_year_start,-3),
832 (c_year_start - 1),
833 pay_au_foreign_workers.g_fw_j_type,
834 p_fw_fbt_bal_type_tab,
835 l_fw_fbt_output_tab) ;
836 END IF;
837
838 if l_fw_fbt_output_tab(1).balance_value >0 then
839 g_fw_fbt_balance := g_fw_fbt_balance + l_fw_fbt_output_tab(1).balance_value;
840 end if;
841 end loop;
842 g_fw_reporting_amt := g_fw_fbt_balance/(1-(l_fbt_rate+l_medicare_levy));
843 g_fw_reporting_amt := round(g_fw_reporting_amt,2);
844 IF l_total_fbt = 0 THEN
845 g_fw_reporting_amt := 0;
846 END IF;
847 end if;
848 else
849
850 l_fw_f_type := pay_au_foreign_workers.check_foreign_worker(c_assignment_id, p_tax_unit_id, add_months(c_year_start,-3), add_months(c_year_start,9)-1,
851 pay_au_foreign_workers.g_fw_f_type);
852 l_fw_j_type := pay_au_foreign_workers.check_foreign_worker(c_assignment_id, p_tax_unit_id, add_months(c_year_start,-3), add_months(c_year_start,9)-1,
853 pay_au_foreign_workers.g_fw_j_type);
854
855 if l_fw_f_type <> 'N' or l_fw_j_type <> 'N' then
856
857 if l_fw_f_type = 'Y' then
858 pay_au_foreign_workers.get_foreign_worker_dates(c_assignment_id, p_tax_unit_id, add_months(c_year_start,-3), add_months(c_year_start,9)-1,
859 pay_au_foreign_workers.g_fw_f_type, f_fw_date_tab);
860 end if;
861
862 if l_fw_j_type = 'Y' then
863 pay_au_foreign_workers.get_foreign_worker_dates(c_assignment_id, p_tax_unit_id, add_months(c_year_start,-3), add_months(c_year_start,9)-1,
864 pay_au_foreign_workers.g_fw_j_type, j_fw_date_tab);
865 end if;
866
867 if f_fw_date_tab.count > 0 then
868 t_fw_gross_type(1) := 'F';
869 if j_fw_date_tab.count >0 then
870 t_fw_gross_type(2) := 'J';
871 end if;
872 elsif j_fw_date_tab.count > 0 then
873 t_fw_gross_type(1) := 'J';
874 end if;
875
876 for i_idx in t_fw_gross_type.first .. t_fw_gross_type.last loop
877
878 IF t_fw_gross_type(i_idx) = 'F' THEN
879 pay_au_foreign_workers.get_foreign_payment_amounts(c_assignment_id,
880 p_tax_unit_id,
881 f_fw_date_tab,
882 add_months(c_year_start,-3),
883 add_months(c_year_start,9)-1,
884 pay_au_foreign_workers.g_fw_f_type,
885 p_fw_fbt_bal_type_tab,
886 l_fw_fbt_output_tab) ;
887
888 ELSIF t_fw_gross_type(i_idx) = 'J' THEN
889 pay_au_foreign_workers.get_foreign_payment_amounts(c_assignment_id,
890 p_tax_unit_id,
891 j_fw_date_tab,
892 add_months(c_year_start,-3),
893 add_months(c_year_start,9)-1,
894 pay_au_foreign_workers.g_fw_j_type,
895 p_fw_fbt_bal_type_tab,
896 l_fw_fbt_output_tab) ;
897 END IF;
898
899 if l_fw_fbt_output_tab(1).balance_value >0 then
900 g_fw_fbt_balance := g_fw_fbt_balance + l_fw_fbt_output_tab(1).balance_value;
901 end if;
902 end loop;
903 g_fw_reporting_amt := g_fw_fbt_balance/(1-(l_fbt_rate+l_medicare_levy));
904 g_fw_reporting_amt := round(g_fw_reporting_amt,2);
905 IF l_total_fbt = 0 THEN
906 g_fw_reporting_amt := 0;
907 END IF;
908 end if;
909 end if ;
910 end if;
911
912 g_fw_reporting_amt := nvl(g_fw_reporting_amt,0);
913 g_fw_fbt_balance := nvl(g_fw_fbt_balance,0);
914 /* Changes for 9147438 end */
915
916 return l_reporting_amt;
917 end get_total_fbt;
918
919
920
921
922
923 function get_total_cdep
924 return number is
925 begin
926
927 -- Bug: 3186840 Check against the global Dimension level value included to return the correct value.
928
929 IF (g_bal_dim_level = 'N') THEN
930 g_total_cdep := get_bal_value_new(g_db_id_cdep);--Bug# 3193479
931 ELSIF (g_bal_dim_level = 'G') THEN
932 g_total_cdep := g_result_group_details_table(10).balance_value;
933 END IF;
934 return g_total_cdep;
935
936 end get_total_cdep;
937
938 /* 4015082 */
939 function get_total_workplace
940 return number is
941 begin
942
943 -- Bug: 3186840 Check against the global Dimension level value included to return the correct value.
944
945 IF (g_bal_dim_level = 'N') THEN
946 g_total_workplace := get_bal_value_new(g_db_id_wgd);
947 ELSIF (g_bal_dim_level = 'G') THEN
948 g_total_workplace := g_result_group_details_table(12).balance_value;
949 END IF;
950 return g_total_workplace;
951
952 end get_total_workplace;
953
954 function Total_Lump_Sum_A_Payments
955 return number is
956 l_lump_sum_a number;
957 begin
958
959 -- Bug: 3186840 Check against the global Dimension level value included to return the correct value.
960
961 IF (g_bal_dim_level = 'N') THEN
962 l_lump_sum_a :=get_bal_value_new(g_db_id_lsap);--Bug# 3193479
963 ELSIF (g_bal_dim_level = 'G') THEN
964 l_lump_sum_a := g_result_group_details_table(1).balance_value;
965 END IF;
966 return l_lump_sum_a;
967 end Total_Lump_Sum_A_Payments;
968
969 function Total_Lump_Sum_B_Payments
970 return number is
971 l_lump_sum_b number;
972 begin
973
974 -- Bug: 3186840 Check against the global Dimension level value included to return the correct value.
975
976 IF (g_bal_dim_level = 'N') THEN
977 l_lump_sum_b := get_bal_value_new(g_db_id_lsbp);--Bug# 3193479
978 ELSIF (g_bal_dim_level = 'G') THEN
979 l_lump_sum_b := g_result_group_details_table(2).balance_value;
980 END IF;
981
982 return l_lump_sum_b;
983 end Total_Lump_Sum_B_Payments;
984
985
986 function Total_Lump_Sum_D_Payments
987 return number is
988 l_lump_sum_d number;
989 begin
990
991 -- Bug: 3186840 Check against the global Dimension level value included to return the correct value.
992
993 IF (g_bal_dim_level = 'N') THEN
994 l_lump_sum_d:= get_bal_value_new(g_db_id_lsdp);--Bug# 3193479
995 ELSIF (g_bal_dim_level = 'G') THEN
996 l_lump_sum_d := g_result_group_details_table(3).balance_value;
997 END IF;
998
999 return l_lump_sum_d;
1000
1001 end Total_Lump_Sum_D_Payments;
1002
1003 /*bug8711855 - p_assignment_action_id parameter is added to call pay_au_payment_summary.get_retro_lumpsumE_value function */
1004 function Total_Lump_Sum_E_Payments(c_year_end DATE,
1005 c_assignment_id pay_assignment_actions.assignment_id%type,
1006 c_registered_employer NUMBER) --2610141
1007 return number is
1008
1009 /*bug8711855 - Fetching Defined_Balance_Ids of Lump Sum E balances_PTD*/
1010 CURSOR c_single_lumpsum_E_payment IS
1011 SELECT decode(pbt.balance_name,
1012 'Lump Sum E Payments', 1
1013 ,'Retro Earnings Leave Loading GT 12 Mths Amount', 2
1014 ,'Retro Earnings Spread GT 12 Mths Amount', 3
1015 ,'Retro Pre Tax GT 12 Mths Amount', 4
1016 ,'Retro Earnings Additional GT 12 Mths Amount', 5) sort_index
1017 , pdb.defined_balance_id defined_balance_id
1018 FROM pay_balance_types pbt,
1019 pay_defined_balances pdb,
1020 pay_balance_dimensions pbd
1021 WHERE pbt.legislation_code = 'AU'
1022 AND pbt.balance_name in ( 'Lump Sum E Payments'
1023 ,'Retro Earnings Leave Loading GT 12 Mths Amount'
1024 ,'Retro Earnings Spread GT 12 Mths Amount'
1025 ,'Retro Pre Tax GT 12 Mths Amount'
1026 ,'Retro Earnings Additional GT 12 Mths Amount') -- bug 13362286
1027 AND pbt.balance_type_id = pdb.balance_type_id
1028 AND pbd.balance_dimension_id = pdb.balance_dimension_id
1029 AND pbd.dimension_name = '_ASG_LE_PTD'
1030 order by sort_index;
1031
1032 v_lump_sum_E_ptd number;
1033 v_effective_date date; /* Bug#3095923 */
1034 c_year_start date;
1035 l_assignment_action_id number; --2610141
1036 v_adj_lump_sum_E_ptd number; --bug8711855
1037 p_lump_sum_E_ptd_tab pay_balance_pkg.t_balance_value_tab; --bug8711855
1038 v_adj_lump_sum_pre_tax number; /* Bug 9190980 */
1039
1040 begin
1041 c_year_start := to_date('01-07-'||to_char(to_number(to_char(c_year_end,'YYYY'))-1),'DD-MM-YYYY');
1042
1043 IF (g_bal_dim_level = 'N') THEN
1044 g_lump_sum_e := get_bal_value_new(g_db_id_lsep) + get_bal_value_new(g_db_id_rll)
1045 + get_bal_value_new(g_db_id_res) - get_bal_value_new(g_db_id_rpt) --bug8711855
1046 + get_bal_value_new(g_db_id_rea) ; -- bug 13362286
1047 ELSIF (g_bal_dim_level = 'T') THEN
1048 g_lump_sum_e := g_result_term_details_table(1).balance_value + g_result_term_details_table(2).balance_value
1049 + g_result_term_details_table(3).balance_value - g_result_term_details_table(4).balance_value --bug8711855
1050 + g_result_term_details_table(5).balance_value; -- bug 13362286
1051 END IF;
1052
1053 p_lump_sum_E_ptd_tab.delete;
1054 for csr_rec in c_single_lumpsum_E_payment loop
1055 p_lump_sum_E_ptd_tab(csr_rec.sort_index).defined_balance_id := csr_rec.defined_balance_id;
1056 end loop;
1057
1058 /* bug8711855 - To adjust Lump Sum E with single lump sum e payments less than 400*/
1059 /* Bug 9190980 - Added argument in call to get_lumpsumE_value function */
1060 if g_lump_sum_e <> 0 then
1061
1062 g_lump_sum_e := pay_au_payment_summary.get_lumpsumE_value(c_registered_employer, c_assignment_id, c_year_start,
1063 c_year_end, p_lump_sum_E_ptd_tab, g_lump_sum_e, v_adj_lump_sum_E_ptd,v_adj_lump_sum_pre_tax); -- Bug 9190980
1064
1065 end if;
1066
1067 return g_lump_sum_e;
1068
1069 end Total_Lump_Sum_E_Payments;
1070
1071
1072 function Total_Union_fees
1073 return number is
1074 l_union_fees number;
1075 begin
1076
1077 -- Bug: 3186840 Check against the global Dimension level value included to return the correct value.
1078
1079 IF (g_bal_dim_level = 'N') THEN
1080 l_union_fees:=get_bal_value_new(g_db_id_uf);--Bug# 3193479
1081 ELSIF (g_bal_dim_level = 'G') THEN
1082 l_union_fees := g_result_group_details_table(4).balance_value;
1083 END IF;
1084
1085 return l_union_fees;
1086
1087 end Total_Union_fees;
1088
1089 /* Bug#3004966 - Modified the Logic for the function
1090 for performance improvement. */
1091 function Total_Tax_deductions
1092 return number is
1093
1094 l_total_tax_ded number := 0;
1095 i number;
1096 l_temp number;
1097
1098 BEGIN --Bug# 3193479 -- Bug #3215789
1099
1100 -- Bug: 3186840 Check against the global Dimension level value included to return the correct value.
1101
1102 IF (g_bal_dim_level = 'N') THEN
1103 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);
1104 ELSIF (g_bal_dim_level = 'G') THEN
1105 l_total_tax_ded := g_result_group_details_table(5).balance_value * (-1) + g_result_group_details_table(6).balance_value +
1106 g_result_group_details_table(7).balance_value;
1107 END IF;
1108
1109 return l_total_tax_ded;
1110
1111 end Total_Tax_deductions;
1112
1113 /* End of Bug#3004966 */
1114
1115 /* Begin 8587013 - Added functions Total_RESC and Total_Foreign_Income for total values calculation of
1116 RESC and Exempt Foreign Employment Income balances */
1117 function Total_RESC
1118 return number is
1119 begin
1120
1121 IF (g_bal_dim_level = 'N') THEN
1122 g_total_resc:=get_bal_value_new(g_db_id_resc);--Bug# 3193479
1123 ELSIF (g_bal_dim_level = 'G') THEN
1124 g_total_resc := g_result_group_details_table(11).balance_value;
1125 END IF;
1126
1127 return g_total_resc;
1128
1129 end Total_RESC;
1130
1131 function Total_Foreign_Income
1132 return number is
1133 begin
1134
1135 IF (g_bal_dim_level = 'N') THEN
1136 g_total_foreign_income:=get_bal_value_new(g_db_id_efei);--Bug# 3193479
1137 ELSIF (g_bal_dim_level = 'G') THEN
1138 g_total_foreign_income := g_result_group_details_table(13).balance_value;
1139 END IF;
1140
1141 return g_total_foreign_income;
1142
1143 end Total_Foreign_Income;
1144
1145 /* End 8587013 */
1146
1147 function Total_Invalidity_Payments
1148 return number is
1149 l_total_invalidity_payments number;
1150 begin
1151
1152 -- Bug: 3186840 Check against the global Dimension level value included to return the correct value.
1153
1154 IF (g_bal_dim_level = 'N') THEN
1155 l_total_invalidity_payments:=get_bal_value_new(g_db_id_ip);--Bug# 3193479
1156 ELSIF (g_bal_dim_level = 'T') THEN
1157 l_total_invalidity_payments := g_result_term_details_table(7).balance_value;
1158 END IF;
1159
1160 return l_total_invalidity_payments;
1161
1162 end Total_Invalidity_Payments;
1163
1164 --Bug#3749530 - Function modified to set globals parmaters
1165 function populate_bal_ids(p_le_level IN varchar2 DEFAULT NULL,
1166 p_business_group_id hr_organization_units.organization_id%type,
1167 p_lst_yr_term VARCHAR2 DEFAULT NULL ) return number
1168 is
1169
1170 /* Bug#3004966 - Added two Balances 'Termination Deductions', 'Total_Tax_Deductions'*/
1171 /* Bug 8587013 - Added two balances 'Reportable Employer Superannuation Contributions' and 'Exempt Foreign Employment Income'
1172 and removed 'Other Income' balance */
1173 /* Bug 8769345 - Added ETP Taxable and Tax Free balances to the cursor */
1174 /* Bug 10331262 - Added Foreign Leave and Termination Payment balances */
1175 CURSOR c_bal_id (c_dimension_name pay_balance_dimensions.dimension_name%TYPE) IS
1176 SELECT pbt.balance_name,pbt.balance_type_id,pdb.defined_balance_id
1177 FROM pay_balance_types pbt,
1178 pay_defined_balances pdb, --Bug# 3193479
1179 pay_balance_dimensions pbd
1180 where pbt.legislation_code = 'AU'
1181 and pbt.balance_name in
1182 ('CDEP','Earnings_Total','Lump Sum A Deductions',
1183 'Lump Sum A Payments','Lump Sum B Deductions','Lump Sum B Payments',
1184 'Lump Sum D Payments','Lump Sum E Payments','Total_Tax_Deductions',
1185 'Union Fees','Invalidity Payments','Lump Sum C Payments',
1186 'Lump Sum C Deductions','Leave Payments Marginal','Termination Deductions'
1187 , 'Workplace Giving Deductions' /* 4015082 */
1188 , 'Reportable Employer Superannuation Contributions' /* 8587013 */
1189 , 'Exempt Foreign Employment Income' /* 8587013 */
1190 , 'ETP Tax Free Payments Excluded' /*start bug 14703826*/
1191 , 'ETP Taxable Payments Excluded'
1192 , 'ETP Tax Free Payments Non Excluded'
1193 , 'ETP Taxable Payments Non Excluded' /*end bug 14703826*/
1194 , 'Retro Earnings Leave Loading GT 12 Mths Amount' --bug8711855
1195 , 'Retro Earnings Spread GT 12 Mths Amount'
1196 , 'Retro Pre Tax GT 12 Mths Amount'
1197 , 'Foreign Leave Payments', 'Foreign Leave Payments Marginal'
1198 , 'Foreign Lump Sum A Payments', 'Foreign Leave Component Deduction'
1199 , 'Foreign Lump Sum A Deduction' /* 10331262 */
1200 , 'Retro Earnings Additional GT 12 Mths Amount' -- bug 13362286
1201 )
1202 AND pdb.balance_type_id = pbt.balance_type_id
1203 AND pdb.balance_dimension_id = pbd.balance_dimension_id
1204 AND pbd.legislation_code = 'AU'
1205 AND pdb.legislation_code = 'AU'
1206 AND pbd.dimension_name = c_dimension_name;
1207
1208 /* Changes for 9147438 starts*/
1209 CURSOR c_fw_bal_id (c_dimension_name pay_balance_dimensions.dimension_name%TYPE) IS
1210 SELECT decode(pbt.balance_name,'Earnings_Total',1
1211 , 'Leave Payments Marginal',2
1212 , 'Workplace Giving Deductions',3
1213 , 'Lump Sum E Payments',4
1214 , 'Retro Earnings Leave Loading GT 12 Mths Amount',5
1215 , 'Retro Earnings Spread GT 12 Mths Amount',6
1216 , 'Retro Pre Tax GT 12 Mths Amount',7
1217 , 'Total_Tax_Deductions',8
1218 , 'Termination Deductions',9
1219 , 'Lump Sum C Deductions',10
1220 , 'Foreign Tax Deductions',11
1221 , 'Lump Sum A Payments',12
1222 , 'Lump Sum D Payments',13
1223 , 'Reportable Employer Superannuation Contributions',14
1224 , 'Union Fees',15
1225 , 'CDEP',16
1226 , 'Exempt Foreign Employment Income',17
1227 , 'Retro LT 12 Mths Prev Yr Amount', 18
1228 , 'Retro Earnings Leave Loading LT 12 Mths Prev Yr Amount', 19
1229 , 'Retro Earnings Spread LT 12 Mths Prev Yr Amount', 20
1230 , 'Retro Pre Tax LT 12 Mths Prev Yr Amount', 21
1231 , 'Retro LT 12 Mths Curr Yr Amount', 22
1232 , 'Retro Earnings Leave Loading LT 12 Mths Curr Yr Amount', 23
1233 , 'Retro Earnings Spread LT 12 Mths Curr Amount', 24
1234 , 'Retro Tax GT12 Amount', 25
1235 , 'Retro Tax LT12 Prev Amount', 26
1236 , 'Retro Tax LT12 Curr Amount', 27
1237 , 'Foreign Leave Payments', 28
1238 , 'Retro Earnings Additional GT 12 Mths Amount', 29
1239 , 'Retro Earnings Additional LT12 Prev Mths Amount', 30
1240 , 'Retro Earnings Additional LT12 Curr Mths Amount', 31
1241 ) sort_index,
1242 pbt.balance_type_id balance_type_id
1243 FROM pay_balance_types pbt
1244 WHERE pbt.legislation_code = 'AU'
1245 and pbt.balance_name in ('Earnings_Total'
1246 , 'Leave Payments Marginal'
1247 , 'Workplace Giving Deductions'
1248 , 'Lump Sum E Payments'
1249 , 'Retro Earnings Leave Loading GT 12 Mths Amount'
1250 , 'Retro Earnings Spread GT 12 Mths Amount'
1251 , 'Retro Pre Tax GT 12 Mths Amount'
1252 , 'Total_Tax_Deductions'
1253 , 'Termination Deductions'
1254 , 'Lump Sum C Deductions'
1255 , 'Foreign Tax Deductions'
1256 , 'Lump Sum A Payments'
1257 , 'Lump Sum D Payments'
1258 , 'Reportable Employer Superannuation Contributions'
1259 , 'Union Fees'
1260 , 'CDEP'
1261 , 'Exempt Foreign Employment Income' -- bug 10143762
1262 /* start bug 9950136 */
1263 , 'Retro LT 12 Mths Prev Yr Amount'
1264 , 'Retro Earnings Leave Loading LT 12 Mths Prev Yr Amount'
1265 , 'Retro Earnings Spread LT 12 Mths Prev Yr Amount'
1266 , 'Retro Pre Tax LT 12 Mths Prev Yr Amount'
1267 , 'Retro LT 12 Mths Curr Yr Amount'
1268 , 'Retro Earnings Leave Loading LT 12 Mths Curr Yr Amount'
1269 , 'Retro Earnings Spread LT 12 Mths Curr Amount'
1270 , 'Retro Tax GT12 Amount'
1271 , 'Retro Tax LT12 Prev Amount'
1272 , 'Retro Tax LT12 Curr Amount'
1273 /* end bug 9950136 */
1274 , 'Foreign Leave Payments'
1275 , 'Retro Earnings Additional GT 12 Mths Amount' -- bug 13362286
1276 , 'Retro Earnings Additional LT12 Prev Mths Amount'
1277 , 'Retro Earnings Additional LT12 Curr Mths Amount'
1278 )
1279 order by sort_index;
1280
1281 CURSOR c_fw_alw_bal_id IS
1282 select pbt.balance_type_id,
1283 pbt.balance_name
1284 from PAY_BAL_ATTRIBUTE_DEFINITIONS pbad
1285 ,pay_balance_attributes pba
1286 ,pay_defined_balances pdb
1287 ,pay_balance_types pbt
1288 ,pay_balance_dimensions pbd
1289 where pbad.attribute_name = 'AU_EOY_ALLOWANCE'
1290 and pba.attribute_id = pbad.attribute_id
1291 and pba.defined_balance_id = pdb.defined_balance_id
1292 and pdb.balance_type_id = pbt.balance_type_id
1293 and pdb.business_group_id = p_business_group_id
1294 and pbd.balance_dimension_id = pdb.balance_dimension_id
1295 and pbd.dimension_name = '_ASG_LE_YTD'
1296 and pbd.legislation_code = 'AU';
1297
1298 cursor c_get_fw_fbt_bal_type_id is
1299 select balance_type_id
1300 from pay_balance_types
1301 where balance_name = 'Fringe Benefits'
1302 and legislation_code = 'AU';
1303
1304 /* Changes for 9147438 end */
1305
1306 /* start bug 7571001 - new cursor c_alw_bal_id is added */
1307 CURSOR c_alw_bal_id IS
1308 select pbt.balance_name
1309 , pdb.defined_balance_id
1310 from PAY_BAL_ATTRIBUTE_DEFINITIONS pbad
1311 ,pay_balance_attributes pba
1312 ,pay_defined_balances pdb
1313 ,pay_balance_types pbt
1314 ,pay_balance_dimensions pbd
1315 where pbad.attribute_name = 'AU_EOY_ALLOWANCE'
1316 and pba.attribute_id = pbad.attribute_id
1317 and pba.defined_balance_id = pdb.defined_balance_id
1318 and pdb.balance_type_id = pbt.balance_type_id
1319 and pdb.business_group_id = p_business_group_id
1320 and pbd.balance_dimension_id = pdb.balance_dimension_id
1321 and pbd.dimension_name = '_ASG_LE_YTD'
1322 and pbd.legislation_code = 'AU'
1323 ;
1324
1325 l_alw_bal_name pay_balance_types.balance_name%type;
1326 l_alw_def_bal_id pay_defined_balances.defined_balance_id%TYPE;
1327 cnt number := 1;
1328 idx number := 1;
1329 /* end bug 7571001 - new cursor c_alw_bal_id is added */
1330
1331
1332
1333
1334 i number;
1335 j number;
1336 l_bal_name pay_balance_types.balance_name%type;
1337 l_bal_id pay_balance_types.balance_type_id%type;
1338 l_def_bal_id pay_defined_balances.defined_balance_id%TYPE; --Bug# 3193479
1339 c_dimension_name pay_balance_dimensions.dimension_name%TYPE; -- Bug: 3186840
1340 g_debug boolean;
1341 l_sort_index number ;
1342
1343 Cursor c_fbt_balance IS --Bug#3749530
1344 select pdb.defined_balance_id
1345 from pay_balance_types pbt,
1346 pay_defined_balances pdb,
1347 pay_balance_dimensions pbd
1348 where pbt.balance_name ='Fringe Benefits'
1349 and pbt.balance_type_id = pdb.balance_type_id
1350 and pdb.balance_dimension_id = pbd.balance_dimension_id
1351 and pbd.legislation_code ='AU'
1352 and pbd.dimension_name ='_ASG_LE_FBT_YTD' --2610141
1353 and pbd.legislation_code = pbt.legislation_code
1354 and pbd.legislation_code = pdb.legislation_code;
1355
1356
1357 BEGIN
1358 g_debug := hr_utility.debug_enabled;
1359
1360 ---Start of Bug#3749530------------------
1361 g_business_group_id := p_business_group_id;
1362 g_lst_yr_term := NVL(p_lst_yr_term,'Y'); --Bug3693034
1363
1364 -- Added for bug 3034189
1365 If g_fbt_defined_balance_id = 0 OR g_fbt_defined_balance_id IS null Then
1366 Open c_fbt_balance;
1367 Fetch c_fbt_balance into g_fbt_defined_balance_id;
1368 Close c_fbt_balance;
1369 End if;
1370 -- End of Bug#3749530-----------------
1371
1372 /* Start of Bug: 3186840 */
1373
1374 IF (p_le_level = 'Y') THEN
1375 c_dimension_name := '_ASG_LE_YTD';
1376 ELSE -- never comes into this condition
1377 c_dimension_name := '_ASG_YTD';
1378 END IF;
1379
1380 IF g_debug THEN
1381 hr_utility.trace('Parameter p_le_level value => ' || p_le_level);
1382 hr_utility.trace('Dimension is => ' || c_dimension_name);
1383 END IF;
1384
1385 /* End of Bug: 3186840 */
1386
1387 i:=1;
1388 OPEN c_bal_id(c_dimension_name); -- Bug: 3186840
1389 LOOP
1390 FETCH c_bal_id INTO l_bal_name,l_bal_id,l_def_bal_id; --Bug# 3193479
1391 EXIT WHEN c_bal_id%NOTFOUND;
1392
1393 IF l_bal_name = 'CDEP' THEN
1394 g_db_id_cdep := l_def_bal_id;
1395 ELSIF l_bal_name = 'Earnings_Total' THEN
1396 g_db_id_et := l_def_bal_id;
1397 ELSIF l_bal_name = 'Lump Sum A Deductions' THEN
1398 g_db_id_lsad := l_def_bal_id;
1399 ELSIF l_bal_name = 'Lump Sum A Payments' THEN
1400 g_db_id_lsap := l_def_bal_id;
1401 ELSIF l_bal_name = 'Lump Sum B Deductions' THEN
1402 g_db_id_lsbd := l_def_bal_id;
1403 ELSIF l_bal_name = 'Lump Sum B Payments' THEN
1404 g_db_id_lsbp := l_def_bal_id;
1405 ELSIF l_bal_name = 'Lump Sum D Payments' THEN
1406 g_db_id_lsdp := l_def_bal_id;
1407 ELSIF l_bal_name = 'Lump Sum E Payments' THEN
1408 g_db_id_lsep := l_def_bal_id;
1409 ELSIF l_bal_name = 'Total_Tax_Deductions' THEN
1410 g_db_id_ttd := l_def_bal_id;
1411 ELSIF l_bal_name = 'Union Fees' THEN
1412 g_db_id_uf := l_def_bal_id;
1413 ELSIF l_bal_name = 'Invalidity Payments' THEN
1414 g_db_id_ip := l_def_bal_id;
1415 ELSIF l_bal_name = 'Lump Sum C Payments' THEN
1416 g_db_id_lscp := l_def_bal_id;
1417 ELSIF l_bal_name = 'Lump Sum C Deductions' THEN
1418 g_db_id_lscd := l_def_bal_id;
1419 ELSIF l_bal_name = 'Leave Payments Marginal' THEN
1420 g_db_id_lpm := l_def_bal_id;
1421 ELSIF l_bal_name = 'Termination Deductions' THEN
1422 g_db_id_td := l_def_bal_id;
1423 ELSIF l_bal_name = 'Workplace Giving Deductions' THEN /* 4015082 */
1424 g_db_id_wgd := l_def_bal_id;
1425 /* Begin 8587013 - Logic added for getting the defined_balance_id of two new balances */
1426 ELSIF l_bal_name = 'Reportable Employer Superannuation Contributions' THEN
1427 g_db_id_resc := l_def_bal_id;
1428 ELSIF l_bal_name = 'Exempt Foreign Employment Income' THEN
1429 g_db_id_efei := l_def_bal_id;
1430 /* End 8587013 */
1431 /* start bug 14703826 */
1432 ELSIF l_bal_name = 'ETP Tax Free Payments Excluded' THEN
1433 g_db_id_tftn := l_def_bal_id;
1434 ELSIF l_bal_name = 'ETP Taxable Payments Excluded' THEN
1435 g_db_id_ttn := l_def_bal_id;
1436 ELSIF l_bal_name = 'ETP Tax Free Payments Non Excluded' THEN
1437 g_db_id_tftp := l_def_bal_id;
1438 ELSIF l_bal_name = 'ETP Taxable Payments Non Excluded' THEN
1439 g_db_id_ttp := l_def_bal_id;
1440 /*end bug 14703826*/
1441 /* Start 8711855 - Adeed code for holiding the defined balance ids of Retro GT12 balances (Lump Sum E)*/
1442 ELSIF l_bal_name = 'Retro Earnings Leave Loading GT 12 Mths Amount' THEN
1443 g_db_id_rll := l_def_bal_id;
1444 ELSIF l_bal_name = 'Retro Earnings Spread GT 12 Mths Amount' THEN
1445 g_db_id_res := l_def_bal_id;
1446 ELSIF l_bal_name = 'Retro Pre Tax GT 12 Mths Amount' THEN
1447 g_db_id_rpt := l_def_bal_id;
1448 /* End 8711855 , Start 10331262 */
1449 ELSIF l_bal_name = 'Retro Earnings Additional GT 12 Mths Amount' THEN -- bug 13362286
1450 g_db_id_rea := l_def_bal_id;
1451 ELSIF l_bal_name = 'Foreign Leave Payments' THEN
1452 g_db_id_flp := l_def_bal_id;
1453 ELSIF l_bal_name = 'Foreign Leave Payments Marginal' THEN
1454 g_db_id_flpm := l_def_bal_id;
1455 ELSIF l_bal_name = 'Foreign Lump Sum A Payments' THEN
1456 g_db_id_flsap := l_def_bal_id;
1457 ELSIF l_bal_name = 'Foreign Leave Component Deduction' THEN
1458 g_db_id_flcd := l_def_bal_id;
1459 ELSIF l_bal_name = 'Foreign Lump Sum A Deduction' THEN
1460 g_db_id_flsad := l_def_bal_id;
1461 /* End 10331262 */
1462 END IF;
1463
1464 g_input_table(i).defined_balance_id :=l_def_bal_id;--Bug# 3193479
1465 g_input_table(i).balance_value := NULL;
1466
1467 IF g_debug THEN
1468 hr_utility.trace(i || ' Defined Balance id of ' || l_bal_name || '=> ' || g_input_table(i).defined_balance_id);
1469 END IF;
1470
1471 i:=i+1;
1472 END LOOP;
1473 CLOSE c_bal_id;
1474
1475 /* Changes for 9147438 starts*/
1476
1477 g_fw_input_table.delete;
1478 FOR csr_rec IN c_fw_bal_id(c_dimension_name)
1479 LOOP
1480
1481 g_fw_input_table(csr_rec.sort_index).balance_type_id := csr_rec.balance_type_id;
1482 IF g_debug THEN
1483 hr_utility.trace(csr_rec.sort_index || ' ' || g_fw_input_table(csr_rec.sort_index).balance_type_id);
1484 END IF;
1485
1486 END LOOP;
1487
1488 g_fw_input_alw_table.delete;
1489 idx:=1;
1490 for csr_rec in c_fw_alw_bal_id loop
1491 g_fw_input_alw_table(idx).balance_type_id := csr_rec.balance_type_id;
1492 idx := idx + 1;
1493 end loop;
1494
1495 p_fw_fbt_bal_type_tab.delete;
1496 open c_get_fw_fbt_bal_type_id;
1497 fetch c_get_fw_fbt_bal_type_id into p_fw_fbt_bal_type_tab(1).balance_type_id;
1498 close c_get_fw_fbt_bal_type_id;
1499
1500 /* Changes for 9147438 end */
1501
1502 /* bug 7571001 - populating defined_balance_id for allowances */
1503 g_input_alw_table.delete;
1504 OPEN c_alw_bal_id;
1505 LOOP
1506 FETCH c_alw_bal_id into l_alw_bal_name, l_alw_def_bal_id;
1507 EXIT WHEN c_alw_bal_id%NOTFOUND;
1508
1509 g_input_alw_table(cnt).defined_balance_id := l_alw_def_bal_id;
1510 g_input_alw_table(cnt).balance_value := NULL;
1511
1512 IF g_debug THEN
1513 hr_utility.trace( ' Defined Balance id of ' || l_alw_bal_name || ' => ' || g_input_alw_table(cnt).defined_balance_id);
1514 END IF;
1515 cnt := cnt + 1;
1516 END LOOP;
1517
1518
1519 ---Except Tax-Unit_id other Context table values are not required
1520 g_context_table(1).jurisdiction_code := NULL;
1521 g_context_table(1).source_id := NULL;
1522 g_context_table(1).source_text := NULL;
1523 g_context_table(1).source_number := NULL;
1524 g_context_table(1).source_text2 := NULL;
1525
1526 RETURN 1;
1527 END populate_bal_ids;
1528
1529 function etp_details
1530 (
1531 p_assignment_id in pay_assignment_actions.ASSIGNMENT_ID%type
1532 ,p_year_start in pay_payroll_Actions.effective_date%type
1533 ,p_year_end in pay_payroll_Actions.effective_date%type)
1534 return number is
1535
1536 e_prepost_error EXCEPTION;
1537
1538 l_etp_payment NUMBER;
1539 l_pre01jul1983_days NUMBER;
1540 l_post30jun1983_days NUMBER;
1541 l_pre01jul1983_ratio NUMBER;
1542 l_post30jun1983_ratio NUMBER;
1543 l_pre01jul1983_value NUMBER;
1544 l_post30jun1983_value NUMBER;
1545 l_result NUMBER;
1546 l_etp_service_date date; /* Bug# 2984390 */
1547 l_le_etp_service_date date; /* Bug 4177679 */
1548 l_current_employee_flag per_all_people_f.current_employee_flag%type;
1549 l_actual_termination_date per_periods_of_service.actual_termination_date%TYPE;
1550 l_date_start per_periods_of_service.date_start%TYPE;
1551 l_death_benefit_type varchar2(100);
1552 l_lst_yr_start date;
1553 l_etp_new_bal_total NUMBER ; /* Bug 9226023 - Variable declared to store the sum of Taxable and Tax Free portions of ETP balances
1554 introduced as part of patch 8769345*/
1555
1556
1557 CURSOR etp_code(c_assignment_id in pay_assignment_actions.assignment_id%type,
1558 c_lst_year_start in pay_payroll_actions.effective_date%type,
1559 c_year_start in pay_payroll_actions.effective_date%type,
1560 c_year_end in pay_payroll_actions.effective_date%type
1561 )is
1562 SELECT distinct nvl(current_employee_flag,'N') current_employee_flag
1563 ,actual_termination_date
1564 ,date_start
1565 ,pps.pds_information2
1566 from per_all_people_f p,
1567 per_all_assignments_f a,
1568 per_periods_of_service pps
1569 where a.person_id = p.person_id
1570 and pps.person_id = p.person_id
1571 and pps.period_of_service_id=a.period_of_service_id /*Bug 5397790 */
1572 and ( pps.actual_termination_date between c_lst_year_start --bug 3686549
1573 and c_year_end ) --Bug 3263659
1574 and a.assignment_id = c_assignment_id
1575 and p.effective_start_date = (SELECT max(pp.effective_start_date)
1576 from per_all_people_f pp
1577 where p.person_id = pp.person_id )
1578 and a.effective_start_date = (SELECT max(aa.effective_start_date)
1579 from per_all_assignments_f aa
1580 where aa.assignment_id = c_assignment_id); /*Bug 4256486 */
1581
1582 begin
1583 if g_debug then
1584 hr_utility.set_location('Start of archive_prepost_details',15);
1585 END if;
1586 -- Added for bug 3686549
1587 l_current_employee_flag := 'Y';
1588 IF (g_lst_yr_term = 'N') THEN
1589 l_lst_yr_start := to_date('01/01/1900','DD/MM/YYYY');
1590 ELSE
1591 l_lst_yr_start := add_months(p_year_start,-12);
1592 END IF;
1593 ------------------------------------------
1594
1595 OPEN etp_code(p_assignment_id,
1596 l_lst_yr_start, --bug 3686549
1597 p_year_start,
1598 p_year_end
1599 );
1600 FETCH etp_code into l_current_employee_flag,
1601 l_actual_termination_date,
1602 l_date_start,
1603 l_death_benefit_type;
1604
1605 CLOSE etp_code;
1606
1607
1608 /*Bug 6112527 For death benefit type 'Dependent' ETP amount is taxable eff 01-Jul-2007 */
1609
1610 if ((l_death_benefit_type <>'D' or to_number(to_char(p_year_start,'YYYY')) >= 2007) or l_death_benefit_type is NULL) then
1611
1612
1613
1614 -- Bug: 3186840 Check against the global Dimension level value included to return the correct value.
1615 /* Start 8679345 - The pre 83 and post 83 components of ETP are computed by using the
1616 ETP Taxable and Tax Free balances */
1617 --------------------------------------------------------------------------------+
1618 -- this procedure gets the ratios to calculate prejul83 balance and postjun83 balance
1619 --------------------------------------------------------------------------------+
1620 if g_debug then
1621 hr_utility.set_location('calling pay_au_terminations.etp_prepost_ratios ',15);
1622 END if;
1623
1624 l_result :=pay_au_terminations.etp_prepost_ratios(
1625 p_assignment_id -- number in
1626 ,l_date_start -- date in
1627 ,l_actual_termination_date -- date in
1628 ,'N' -- Bug#2819479 Flag to check whether this function called by Termination Form.
1629 ,l_pre01jul1983_days -- number out
1630 ,l_post30jun1983_days -- number out
1631 ,l_pre01jul1983_ratio -- number out
1632 ,l_post30jun1983_ratio -- number out
1633 ,l_etp_service_date -- date out /* Bug# 2984390 */
1634 ,l_le_etp_service_date
1635 ); -- date out /* Bug# 4177679 */
1636
1637 /* Start 9226023 - Logic modified to support the calculation of Taxable and Tax Free portions of ETP for the
1638 terminated employees processed before applying the patch 8768345
1639 start 14703826 - Removed 9226023 logic */
1640 IF (g_bal_dim_level = 'N') THEN
1641 l_etp_payment := get_bal_value_new(g_db_id_lscp); --Bug# 3193479
1642
1643 l_pre01jul1983_value := get_bal_value_new(g_db_id_tftn) + get_bal_value_new(g_db_id_tftp) ;
1644
1645 l_post30jun1983_value := get_bal_value_new(g_db_id_ttn) + get_bal_value_new(g_db_id_ttp) ;
1646
1647 ELSIF (g_bal_dim_level = 'T') THEN
1648 l_etp_payment := g_result_term_details_table(6).balance_value;
1649
1650 l_pre01jul1983_value := g_result_term_details_table(9).balance_value +
1651 g_result_term_details_table(11).balance_value ;
1652
1653 l_post30jun1983_value := g_result_term_details_table(10).balance_value +
1654 g_result_term_details_table(12).balance_value ;
1655 END IF;
1656 /* End 9226023, 14703826*/
1657 /* End 8679345 */
1658 if l_result = 0 then
1659 raise e_prepost_error;
1660
1661 else
1662
1663 /* Start 8769345 - The pre 83 and post 83 components of ETP are computed by making of ETP Tax Free and Taxable balances */
1664
1665 g_pre01jul1983_value :=round(l_pre01jul1983_value,2); /* Bug 4872594 - Changed to Round upto 2 decimals*/
1666 g_post30jun1983_value :=round(l_post30jun1983_value,2); /* Bug 4872594 - Changed to Round upto 2 decimals*/
1667 /* End 8769345 */
1668
1669 end if;
1670 if g_debug then
1671 hr_utility.set_location('End of archive_prepost_details',14);
1672 END if;
1673 g_etp_gross:=g_pre01jul1983_value+g_post30jun1983_value+Total_Invalidity_Payments;
1674 g_assessable:= round(g_post30jun1983_value,2); /* Bug 4872594 - Changed to Round upto 2 decimals */
1675 /* Bug No : 7030285 - Assessable Income modified */
1676 -- Bug: 3186840 Check against the global Dimension level value included to return the correct value.
1677
1678 IF (g_bal_dim_level = 'N') THEN
1679 g_etp_tax:=get_bal_value_new(g_db_id_lscd); --Bug# 3193479
1680 ELSIF (g_bal_dim_level = 'T') THEN
1681 g_etp_tax := g_result_term_details_table(8).balance_value;
1682 END IF;
1683
1684
1685 else
1686 g_etp_gross:=0;
1687 g_assessable:=0;
1688 g_etp_tax:=0;
1689 end if;
1690
1691 return g_pre01jul1983_value;
1692
1693
1694 exception
1695 when e_prepost_error then
1696 if g_debug then
1697 hr_utility.set_location('error from pay_au_terminations.etp_prepost_ratios',20);
1698 END if;
1699 when others then
1700 if g_debug then
1701 hr_utility.set_location('error in function_prepost_details',21);
1702 END if;
1703 raise;
1704 end etp_details;
1705
1706
1707 function post30jun1983_value return number is
1708 begin
1709 return g_post30jun1983_value;
1710 end post30jun1983_value;
1711
1712
1713 function etp_gross return number is
1714 begin
1715 return g_etp_gross;
1716 end etp_gross;
1717
1718 function assessable_income return number is
1719 begin
1720 return g_assessable;
1721 end assessable_income;
1722
1723 function etp_tax return number is
1724 begin
1725 return g_etp_tax;
1726 end etp_tax;
1727
1728 --------------------Bug# 3193479-----------------------------------------------------------------
1729 function get_bal_value_new(p_defined_balance_id pay_defined_balances.defined_balance_id%TYPE)
1730 return number is
1731 begin
1732 for i in 1..g_result_table.last
1733 loop
1734 if g_result_table.exists(i) then
1735 if g_result_table(i).defined_balance_id =p_defined_balance_id then
1736 RETURN g_result_table(i).balance_value;
1737 end if;
1738 end if;
1739 end loop;
1740 RETURN 0;
1741 end;
1742
1743
1744 PROCEDURE get_value_bbr(c_year_start DATE,
1745 c_year_end DATE,
1746 c_assignment_id pay_assignment_actions.assignment_id%type,
1747 c_fbt_rate ff_globals_f.global_value%TYPE,
1748 c_ml_rate ff_globals_f.global_value%TYPE,
1749 p_assignment_action_id pay_assignment_actions.assignment_id%type,
1750 p_tax_unit_id hr_all_organization_units.organization_id%TYPE,
1751 p_termination_date DATE, --Bug 3098367
1752 p_display_flag OUT NOCOPY VARCHAR2, --Bug 3098367
1753 p_output_tab OUT NOCOPY bal_tab
1754 ) IS
1755
1756 l_net_balance NUMBER := 0; -- 3098353
1757
1758 /* Changes for 9147438 start */
1759
1760 cursor get_balance_name (c_defined_balance_id pay_defined_balances.defined_balance_id%type) is
1761 select NVL(pbt.reporting_name,pbt.balance_name) balance_name
1762 from pay_defined_balances pdb,
1763 pay_balance_types pbt
1764 where pdb.defined_balance_id = c_defined_balance_id
1765 and pdb.balance_type_id = pbt.balance_type_id
1766 and pdb.business_group_id = g_business_group_id;
1767
1768
1769 l_fw_f_type varchar2(1);
1770 l_fw_j_type varchar2(1);
1771 i_idx number ;
1772 l_fw_earnings_asg_le_ytd number;
1773 l_fw_lump_sumE_asg_le_ytd number;
1774 l_fw_workplace_asg_le_ytd number;
1775 l_fw_Total_Tax_asg_le_ytd number;
1776 l_fw_Foreign_Tax_asg_le_ytd number;
1777 l_fw_lump_sumA_asg_le_ytd number;
1778 l_fw_lump_sumD_asg_le_ytd number;
1779 l_fw_resc_asg_le_ytd number;
1780 l_fw_union_asg_le_ytd number;
1781 l_alw_total number;
1782 g_fw_check varchar2(1);
1783 l_fbt_total number;
1784 p_fw_allowance_tab pay_au_payment_summary.t_allowance_balance%type;
1785 l_fw_union_output_tab pay_balance_pkg.t_detailed_bal_out_tab;
1786 counter number;
1787 l_fw_cdep_asg_le_ytd number;
1788 cnt number;
1789 x number;
1790 l_fw_tot_net_balance number;
1791 l_fw_tot_sub_balance number;
1792 g_net_balance number;
1793 l_payg_fw_type char(1);
1794 /* Changes for 9147438 end */
1795 /* start bug 9950136 */
1796 l_fw_lt12_prev_earnings number;
1797 l_fw_lt12_curr_earnings number;
1798 l_retro_fw_gross_total number;
1799 l_retro_fw_tax_total number;
1800 l_retro_fw_gross number;
1801 l_retro_fw_tax number;
1802 n number;
1803 /* end bug 9950136 */
1804
1805 /* Start bug 10331262 */
1806 l_lve_earnings NUMBER;
1807 l_lve_tax NUMBER;
1808 l_term_earnings NUMBER;
1809 l_term_tax NUMBER;
1810 l_lumpsuma NUMBER;
1811 /* End bug 10331262 */
1812
1813 begin
1814
1815 g_pre01jul1983_value :=0;
1816 g_post30jun1983_value :=0;
1817 g_etp_gross :=0;
1818 g_etp_tax :=0;
1819 g_assessable :=0;
1820 g_lump_sum_e :=0;
1821 g_total_cdep :=0;
1822 g_total_allowance :=0;
1823 g_total_fbt :=0;
1824 g_total_gross :=0;
1825 g_total_workplace :=0; /* 4015082 */
1826 /* Begin 8587013 - Initialize package variables of RESC and Exempt Foreign Employment Income balances */
1827 g_total_resc :=0;
1828 g_total_foreign_income := 0;
1829 /* End 8587013 */
1830
1831 /* Changes for 9147438 start */
1832 l_fw_earnings_asg_le_ytd :=0;
1833 l_fw_lump_sumE_asg_le_ytd :=0 ;
1834 l_fw_workplace_asg_le_ytd :=0 ;
1835 l_fw_Total_Tax_asg_le_ytd :=0 ;
1836 l_fw_Foreign_Tax_asg_le_ytd :=0;
1837 l_fw_lump_sumA_asg_le_ytd :=0;
1838 l_fw_lump_sumD_asg_le_ytd :=0;
1839 l_fw_resc_asg_le_ytd :=0;
1840 l_fw_union_asg_le_ytd :=0;
1841 l_alw_total :=0;
1842 l_fbt_total :=0;
1843 l_fw_cdep_asg_le_ytd := 0;
1844
1845 g_net_balance :=0;
1846 l_fw_tot_net_balance :=0;
1847 l_fw_tot_sub_balance :=0;
1848 /* Changes for 10209338 starts */
1849 g_fw_fbt_balance :=0;
1850 g_fw_reporting_amt :=0;
1851 /* Changes for 10209338 end */
1852 l_payg_fw_type :='P';
1853
1854 cnt :=1;
1855 /* Changes for 9147438 end */
1856 /* start bug 9950136 */
1857 l_fw_lt12_prev_earnings :=0;
1858 l_fw_lt12_curr_earnings :=0;
1859 l_retro_fw_gross_total :=0;
1860 l_retro_fw_tax_total :=0;
1861 /* end bug 9950136 */
1862
1863 g_debug := hr_utility.debug_enabled;
1864
1865 g_result_table.delete;
1866 g_context_table.delete;
1867 bal_id.delete;
1868
1869 /* Changes for 9147438 start */
1870 g_fw_result_table.delete;
1871 f_fw_date_tab_g.delete;
1872 j_fw_date_tab_g.delete;
1873 t_fw_gross_type.delete;
1874 /* Changes for 9147438 end*/
1875
1876 p_display_flag := 'YES';
1877 g_context_table(1).tax_unit_id := p_tax_unit_id;
1878
1879 pay_balance_pkg.get_value(p_assignment_action_id => p_assignment_action_id,
1880 p_defined_balance_lst=>g_input_table,
1881 p_context_lst =>g_context_table,
1882 p_output_table=>g_result_table);
1883
1884 g_bal_dim_level := 'N'; -- Bug: 3186840
1885 /* Bug 8587013 - Addded code for RESC and Exempt Foreign Employment Income balances.
1886 The Other Income balance is removed and the value is set to zero */
1887 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); -- bug 7571001
1888
1889 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
1890 bal_id(3).balance_value := get_total_cdep;
1891 bal_id(4).balance_value := Total_Lump_Sum_A_Payments;
1892 bal_id(5).balance_value := Total_Lump_Sum_B_Payments;
1893 bal_id(6).balance_value := Total_Lump_Sum_D_Payments;
1894 bal_id(7).balance_value := Total_Lump_Sum_E_Payments(c_year_end,c_assignment_id,p_tax_unit_id) ; --2610141
1895 bal_id(8).balance_value := Total_Union_fees;
1896 bal_id(9).balance_value := Total_Tax_deductions;
1897 bal_id(10).balance_value := 0; /* 8587013 */
1898 bal_id(18).balance_value := get_total_workplace; /* 4015082 */
1899 bal_id(20).balance_value := Total_Foreign_Income; -- bug#10143762
1900 bal_id(11).balance_value := total_gross;
1901 bal_id(12).balance_value := ETP_DETAILS(c_assignment_id,c_year_start,c_year_end);
1902 bal_id(13).balance_value := POST30JUN1983_VALUE;
1903 bal_id(14).balance_value := TOTAL_INVALIDITY_PAYMENTS;
1904 bal_id(15).balance_value := ETP_GROSS;
1905 bal_id(16).balance_value := ETP_TAX;
1906 bal_id(17).balance_value := assessable_income;
1907 bal_id(19).balance_value := Total_RESC;
1908 --bal_id(20).balance_value := Total_Foreign_Income;
1909
1910 /* g_total_fbt + Gross + Lump Sum E + Tax + CDEP + Lump Sum A + B + D + TFI + RESC */
1911
1912 g_net_balance := g_total_fbt + bal_id(11).balance_value + bal_id(7).balance_value
1913 + bal_id(9).balance_value + bal_id(3).balance_value + bal_id(4).balance_value
1914 + bal_id(5).balance_value + bal_id(6).balance_value + bal_id(20).balance_value
1915 + bal_id(19).balance_value ;
1916
1917 /* Changes for 9147438 Starts */
1918
1919 if (to_number(to_char(c_year_start,'YYYY')) >= 2010) then
1920
1921 g_fw_check := pay_au_foreign_workers.check_foreign_worker(c_assignment_id, p_tax_unit_id, c_year_start, c_year_end);
1922
1923 if g_debug then
1924 hr_utility.set_location('FW EMP check .. g_fw_check for assignment_id '||c_assignment_id||' is '||g_fw_check, 2);
1925
1926 end if;
1927
1928 end if;
1929
1930
1931 if g_fw_check = 'Y' then
1932
1933 l_fw_f_type := pay_au_foreign_workers.check_foreign_worker(c_assignment_id, p_tax_unit_id, c_year_start, c_year_end, pay_au_foreign_workers.g_fw_f_type);
1934 l_fw_j_type := pay_au_foreign_workers.check_foreign_worker(c_assignment_id, p_tax_unit_id, c_year_start, c_year_end, pay_au_foreign_workers.g_fw_j_type);
1935
1936 if l_fw_f_type = 'Y' then
1937 pay_au_foreign_workers.get_foreign_worker_dates(c_assignment_id, p_tax_unit_id, c_year_start, c_year_end, pay_au_foreign_workers.g_fw_f_type, f_fw_date_tab_g);
1938 end if;
1939
1940 if l_fw_j_type = 'Y' then
1941 pay_au_foreign_workers.get_foreign_worker_dates(c_assignment_id, p_tax_unit_id, c_year_start, c_year_end, pay_au_foreign_workers.g_fw_j_type, j_fw_date_tab_g);
1942 end if;
1943
1944 if f_fw_date_tab_g.count > 0 then
1945 t_fw_gross_type(1) := 'F';
1946 if j_fw_date_tab_g.count >0 then
1947 t_fw_gross_type(2) := 'J';
1948 end if;
1949 elsif j_fw_date_tab_g.count > 0 then
1950 t_fw_gross_type(1) := 'J';
1951 end if;
1952
1953 For i_idx in t_fw_gross_type.first .. t_fw_gross_type.last loop
1954
1955 g_fw_result_table.delete;
1956
1957 IF t_fw_gross_type(i_idx) = 'F' THEN
1958 pay_au_foreign_workers.get_foreign_payment_amounts(c_assignment_id,
1959 p_tax_unit_id,
1960 f_fw_date_tab_g,
1961 c_year_start,
1962 c_year_end,
1963 pay_au_foreign_workers.g_fw_f_type,
1964 g_fw_input_table,
1965 g_fw_result_table) ;
1966 ELSIF t_fw_gross_type(i_idx) = 'J' THEN
1967 pay_au_foreign_workers.get_foreign_payment_amounts(c_assignment_id,
1968 p_tax_unit_id,
1969 j_fw_date_tab_g,
1970 c_year_start,
1971 c_year_end,
1972 pay_au_foreign_workers.g_fw_j_type,
1973 g_fw_input_table,
1974 g_fw_result_table) ;
1975 END IF;
1976
1977 /* Bug 10331262 - Reporting of Termination Payments and Foreign Leave Payments
1978 Leave Payments:
1979 l_lve_earnings = Foreign Leave Payment (for non FW-periods)
1980 l_lve_tax = Tax on Foreign Leave Payment (for non FW-periods)
1981 l_term_earnings = Foreign Leave Payments Marginal
1982 l_lump_sum_a = Foreign Lump Sum A Payments
1983 l_term_tax = Foreign Leave Components Deduction + Foreign Lump Sum A Deduction
1984 */
1985 IF (i_idx = 1)
1986 THEN
1987 IF g_debug
1988 THEN
1989 hr_utility.set_location('Entering to calculate the Term and Leave Pay amounts ',1000);
1990 END IF;
1991 l_term_earnings := get_bal_value_new(g_db_id_flpm);
1992 l_lumpsuma := get_bal_value_new(g_db_id_flsap);
1993 l_term_tax := get_bal_value_new(g_db_id_flcd) + get_bal_value_new(g_db_id_flsad);
1994
1995 IF (get_bal_value_new(g_db_id_flp) - g_fw_result_table(28).balance_value) <> 0
1996 THEN
1997
1998 pay_au_payment_summary.get_foreign_leave_payments
1999 (c_assignment_id
2000 ,p_tax_unit_id
2001 ,c_year_start
2002 ,c_year_end
2003 ,l_lve_earnings
2004 ,l_lve_tax);
2005 ELSE
2006 l_lve_earnings := 0;
2007 l_lve_tax := 0;
2008 END IF;
2009
2010 ELSE
2011 l_lve_earnings := 0;
2012 l_lve_tax := 0;
2013 l_term_earnings := 0;
2014 l_term_tax := 0;
2015 l_lumpsuma := 0;
2016
2017 END IF;
2018 /* End Bug 10331262 */
2019
2020 /* Bug 10331262 - Modified termination payments added to earnings */
2021
2022 l_fw_earnings_asg_le_ytd := l_fw_earnings_asg_le_ytd
2023 + g_fw_result_table(1).balance_value
2024 + l_lve_earnings + l_term_earnings
2025 + g_fw_result_table(3).balance_value
2026 - g_fw_result_table(17).balance_value; -- bug 10143762
2027
2028 l_fw_lump_sumE_asg_le_ytd := l_fw_lump_sumE_asg_le_ytd
2029 + g_fw_result_table(4).balance_value + g_fw_result_table(5).balance_value
2030 + g_fw_result_table(6).balance_value - g_fw_result_table(7).balance_value
2031 + g_fw_result_table(29).balance_value ; -- bug 13362286
2032
2033 /* start bug 9950136 */
2034 l_retro_fw_gross := 0;
2035 l_retro_fw_tax := 0;
2036 IF t_fw_gross_type(i_idx) = 'F' THEN
2037 n := pay_au_payment_summary.adjust_retro_fw(c_assignment_id
2038 ,p_tax_unit_id
2039 ,c_year_start
2040 ,c_year_end
2041 ,pay_au_foreign_workers.g_fw_f_type
2042 ,l_retro_fw_gross
2043 ,l_retro_fw_tax);
2044 ELSIF t_fw_gross_type(i_idx) = 'J' THEN
2045 n := pay_au_payment_summary.adjust_retro_fw(c_assignment_id
2046 ,p_tax_unit_id
2047 ,c_year_start
2048 ,c_year_end
2049 ,pay_au_foreign_workers.g_fw_j_type
2050 ,l_retro_fw_gross
2051 ,l_retro_fw_tax);
2052 END IF;
2053
2054 l_fw_lt12_prev_earnings := l_fw_lt12_prev_earnings
2055 + g_fw_result_table(18).balance_value + g_fw_result_table(19).balance_value
2056 + g_fw_result_table(20).balance_value - g_fw_result_table(21).balance_value
2057 + g_fw_result_table(30).balance_value ; -- bug 13362286
2058
2059 l_fw_lt12_curr_earnings := l_fw_lt12_curr_earnings
2060 + g_fw_result_table(22).balance_value + g_fw_result_table(23).balance_value
2061 + g_fw_result_table(24).balance_value + g_fw_result_table(31).balance_value; -- bug 13362286
2062
2063 l_retro_fw_gross_total := l_retro_fw_gross_total + l_retro_fw_gross;
2064
2065 l_retro_fw_tax_total := l_retro_fw_tax_total + l_retro_fw_tax;
2066
2067 /* Bug 10331262 - Replaced Termination Deductions, Lump Sum C with
2068 Foreign Termination Deductions */
2069
2070 l_fw_Total_Tax_asg_le_ytd := l_fw_Total_Tax_asg_le_ytd
2071 + g_fw_result_table(8).balance_value
2072 + l_term_tax + l_lve_tax
2073 - g_fw_result_table(25).balance_value
2074 - g_fw_result_table(26).balance_value
2075 - g_fw_result_table(27).balance_value;
2076 /* start bug 9950136 */
2077
2078 /* Bug 10331262 - Replace Lump Sum A with Foreign Lump Sum A Pay Values */
2079 l_fw_workplace_asg_le_ytd := l_fw_workplace_asg_le_ytd + g_fw_result_table(3).balance_value;
2080 l_fw_Foreign_Tax_asg_le_ytd := l_fw_Foreign_Tax_asg_le_ytd + g_fw_result_table(11).balance_value;
2081 l_fw_lump_sumA_asg_le_ytd := l_fw_lump_sumA_asg_le_ytd + l_lumpsuma;
2082 l_fw_lump_sumD_asg_le_ytd := l_fw_lump_sumD_asg_le_ytd + g_fw_result_table(13).balance_value;
2083 l_fw_resc_asg_le_ytd := l_fw_resc_asg_le_ytd + g_fw_result_table(14).balance_value;
2084 l_fw_union_asg_le_ytd := l_fw_union_asg_le_ytd + g_fw_result_table(15).balance_value;
2085 l_fw_cdep_asg_le_ytd := l_fw_cdep_asg_le_ytd + g_fw_result_table(16).balance_value;
2086
2087 if nvl(g_total_allowance,0) > 0 then
2088
2089 g_fw_result_alw_table.delete;
2090
2091 IF t_fw_gross_type(i_idx) = 'F' THEN
2092 pay_au_foreign_workers.get_foreign_payment_amounts(c_assignment_id,
2093 p_tax_unit_id,
2094 f_fw_date_tab_g,
2095 c_year_start,
2096 c_year_end,
2097 pay_au_foreign_workers.g_fw_f_type,
2098 g_fw_input_alw_table,
2099 g_fw_result_alw_table) ;
2100 ELSIF t_fw_gross_type(i_idx) = 'J' THEN
2101 pay_au_foreign_workers.get_foreign_payment_amounts(c_assignment_id,
2102 p_tax_unit_id,
2103 j_fw_date_tab_g,
2104 c_year_start,
2105 c_year_end,
2106 pay_au_foreign_workers.g_fw_j_type,
2107 g_fw_input_alw_table,
2108 g_fw_result_alw_table) ;
2109 END IF;
2110
2111 if g_fw_result_alw_table.count > 0 then
2112
2113 for i in g_fw_result_alw_table.first .. g_fw_result_alw_table.last loop
2114 if g_fw_result_alw_table(i).balance_value > 0 then
2115 for alw_rec in get_balance_name(g_fw_result_alw_table(i).defined_balance_id) loop
2116 p_fw_allowance_tab(cnt).balance_name := alw_rec.balance_name;
2117 p_fw_allowance_tab(cnt).balance_value := g_fw_result_alw_table(i).balance_value;
2118 cnt := cnt + 1;
2119 end loop;
2120 end if;
2121 end loop;
2122 end if;
2123
2124 if p_fw_allowance_tab.count >0 then
2125
2126 IF t_fw_gross_type(i_idx) = 'F' THEN
2127 for i in f_fw_date_tab_g.FIRST .. f_fw_date_tab_g.LAST loop
2128 x := pay_au_payment_summary.adjust_retro_allowances
2129 (p_fw_allowance_tab
2130 ,f_fw_date_tab_g(i).start_date
2131 ,f_fw_date_tab_g(i).end_date
2132 ,c_assignment_id
2133 ,p_tax_unit_id
2134 );
2135 end loop;
2136 ELSIF t_fw_gross_type(i_idx) = 'J' THEN
2137 for i in j_fw_date_tab_g.FIRST ..j_fw_date_tab_g.LAST loop
2138 x := pay_au_payment_summary.adjust_retro_allowances
2139 (p_fw_allowance_tab
2140 ,j_fw_date_tab_g(i).start_date
2141 ,j_fw_date_tab_g(i).end_date
2142 ,c_assignment_id
2143 ,p_tax_unit_id
2144 );
2145 end loop;
2146 END IF;
2147 end if;
2148 end if; /* allowance code ends */
2149 end loop;
2150
2151 if p_fw_allowance_tab.count >0 then
2152 for i in p_fw_allowance_tab.first .. p_fw_allowance_tab.last loop
2153 l_alw_total := l_alw_total + p_fw_allowance_tab(i).balance_value;
2154 end loop;
2155 end if;
2156
2157 /* start bug 9950136 */
2158 l_fw_earnings_asg_le_ytd := l_fw_earnings_asg_le_ytd - l_alw_total - l_fw_lump_sumE_asg_le_ytd - l_fw_cdep_asg_le_ytd
2159 - l_fw_lt12_prev_earnings
2160 - l_fw_lt12_curr_earnings
2161 + l_retro_fw_gross_total;
2162
2163 l_fw_lump_sumE_asg_le_ytd := 0;
2164
2165 l_fw_Total_Tax_asg_le_ytd := l_fw_Total_Tax_asg_le_ytd + l_retro_fw_tax_total;
2166 /* end bug 9950136 */
2167
2168
2169 /* g_total_fbt + Gross + Lump Sum E + Tax + CDEP + Lump Sum A + B + D + TFI + RESC */
2170
2171 /* Bug 10331262 - Add the total values for RESC,Lump Sum D and RFB and not FW ones. These could be
2172 prorated */
2173 l_fw_tot_net_balance := l_fw_earnings_asg_le_ytd + l_fw_lump_sumE_asg_le_ytd + l_fw_Total_Tax_asg_le_ytd + l_fw_cdep_asg_le_ytd
2174 + l_fw_lump_sumA_asg_le_ytd
2175 + bal_id(19).balance_value + bal_id(6).balance_value + g_total_fbt;
2176 l_fw_tot_sub_balance := bal_id(19).balance_value + bal_id(6).balance_value + g_total_fbt;
2177
2178
2179 /* Bug 10331262 - Return 0 for FW Lump Sum D, RESC and RFB */
2180 bal_id(21).balance_value := l_fw_earnings_asg_le_ytd ;
2181 bal_id(22).balance_value := l_fw_lump_sumE_asg_le_ytd;
2182 bal_id(23).balance_value := l_fw_workplace_asg_le_ytd;
2183 bal_id(24).balance_value := l_fw_Total_Tax_asg_le_ytd;
2184 bal_id(25).balance_value := l_fw_Foreign_Tax_asg_le_ytd;
2185 bal_id(26).balance_value := l_fw_lump_sumA_asg_le_ytd;
2186 bal_id(27).balance_value := 0;
2187 bal_id(28).balance_value := 0;
2188 bal_id(29).balance_value := l_fw_union_asg_le_ytd;
2189 bal_id(30).balance_value := 0;
2190 bal_id(31).balance_value := l_fw_tot_net_balance;
2191 bal_id(32).balance_value := l_fw_tot_sub_balance;
2192 bal_id(33).balance_value := g_net_balance;
2193 bal_id(34).balance_value := l_fw_cdep_asg_le_ytd;
2194 bal_id(35).balance_value := l_alw_total ;
2195
2196 else /* Not a Foreign Worker employee */
2197 bal_id(21).balance_value := 0 ;
2198 bal_id(22).balance_value := 0;
2199 bal_id(23).balance_value := 0;
2200 bal_id(24).balance_value := 0;
2201 bal_id(25).balance_value := 0;
2202 bal_id(26).balance_value := 0;
2203 bal_id(27).balance_value := 0;
2204 bal_id(28).balance_value := 0;
2205 bal_id(29).balance_value := 0;
2206 bal_id(30).balance_value := 0;
2207 bal_id(31).balance_value := 0;
2208 bal_id(32).balance_value := 0;
2209 bal_id(33).balance_value := g_net_balance;
2210 bal_id(34).balance_value := 0;
2211 bal_id(35).balance_value := 0;
2212 end if;
2213 /* Changes for 9147438 end */
2214
2215
2216 /*--------------Bug 3098367-------------
2217 If employee is terminated in last year then assignment details will be displayed
2218 only if sum of balance values is greater than 0 otherwise employee will not be displayed */
2219 IF p_termination_date < c_year_start THEN
2220 For i IN 1..bal_id.COUNT
2221 LOOP
2222 l_net_balance := l_net_balance + bal_id(i).balance_value;
2223 END LOOP;
2224 IF l_net_balance = 0 THEN
2225 p_display_flag := 'NO';
2226 END IF;
2227 END IF;
2228 -------End of --Bug 3098367----------------------------------------------------
2229 p_output_tab := bal_id;
2230
2231 IF g_debug
2232 THEN
2233 hr_utility.set_location('get_value_bbr Print Values ',1000);
2234 FOR i IN p_output_tab.FIRST..p_output_tab.LAST
2235 LOOP
2236 hr_utility.set_location('Index '||i||' Value ='||p_output_tab(i).balance_value,1000);
2237 END LOOP;
2238 END IF;
2239 END;
2240 ---------------End of Bug# 3193479-----------------------------------------------------------
2241
2242 /* Bug: 12400821 - Performance Improvement done */
2243 function get_exclusion_info(flag varchar2,p_assignment_id number)
2244 return varchar2 is
2245 begin
2246
2247 if exc_tab1.count>0 then
2248 if exc_tab1.exists(p_assignment_id) then
2249 if flag='name' then
2250 return exc_tab1(p_assignment_id).employee_name || '|' || exc_tab1(p_assignment_id).assignment_number || '|' || exc_tab1(p_assignment_id).reason;
2251 end if;
2252 end if;
2253 end if;
2254
2255 end get_exclusion_info;
2256
2257 /* Bug: 12400821 - Performance Improvement done */
2258 function get_assignment_id(p_assignment_id number) return number is
2259 begin
2260
2261 if exc_tab1.count>0 then
2262 if exc_tab1.exists(p_assignment_id) then
2263 return 1;
2264 end if;
2265
2266 end if;
2267 return 0;
2268 end get_assignment_id;
2269
2270 /* Bug: 12400821 - Performance Improvement done */
2271 function populate_exclusion_table(p_assignment_id per_all_assignments_f.assignment_id%type,
2272 p_financial_year varchar2,
2273 p_financial_year_end date,
2274 p_tax_unit_id number --2610141
2275 )
2276 return number is
2277
2278 Cursor c_ps_issued(c_assignment_id per_all_assignments_f.assignment_id%type,
2279 c_financial_year varchar2)
2280 is
2281 SELECT distinct paat.assignment_id
2282 from pay_action_interlocks pail,
2283 pay_assignment_actions paat,
2284 pay_payroll_actions paas
2285 where paat.assignment_id = c_assignment_id
2286 and paas.action_type ='X'
2287 and paas.action_status ='C'
2288 and paas.report_type ='AU_PAYMENT_SUMMARY_REPORT'
2289 and pail.locking_action_id = paat.assignment_action_id
2290 and paat.payroll_action_id = paas.payroll_action_id
2291 and pay_core_utils.get_parameter('FINANCIAL_YEAR',paas.legislative_parameters) = c_financial_year
2292 and pay_core_utils.get_parameter('REGISTERED_EMPLOYER',paas.legislative_parameters) = p_tax_unit_id; --2610141
2293
2294
2295 CURSOR c_get_details(c_assignment_id per_all_assignments_f.assignment_id%type,
2296 c_financial_yr_end date)
2297 is
2298 SELECT pap.last_name,
2299 paa.assignment_number
2300 from per_all_people_f pap,per_all_assignments_f paa
2301 where pap.person_id=paa.person_id
2302 and paa.assignment_id=c_assignment_id
2303 and paa.effective_start_date = (SELECT max(paa1.effective_start_date)
2304 from per_all_assignments_f paa1
2305 where paa1.assignment_id = c_assignment_id) /* Bug 4278407*/
2306 and pap.effective_start_date = (SELECT max(ppf.effective_start_date)
2307 from per_all_people_f ppf
2308 where pap.person_id = ppf.person_id);
2309
2310 CURSOR c_eit_updated(c_assignment_id per_all_assignments_f.assignment_id%type,
2311 c_financial_year varchar2)
2312 is
2313 SELECT assignment_id
2314 from per_assignment_extra_info,
2315 hr_lookups
2316 where assignment_id = c_assignment_id
2317 and aei_information1 is not null
2318 and aei_information1 = lookup_code
2319 and nvl(aei_information2,p_tax_unit_id) = decode(aei_information2,'-999',aei_information2,p_tax_unit_id) --Bug 4173809
2320 and lookup_type ='AU_PS_FINANCIAL_YEAR'
2321 and meaning = c_financial_year;
2322
2323 /*Bug 4173809 - Cursor updated so that the assignment is reported in the exception section when Manual PS
2324 is issued against 'All' legal employers or a particular legal employer
2325 If the Manual PS is issued for 'All' the legal employers the aei_information2 would be -999*/
2326
2327 l_assignment_id per_all_assignments_f.assignment_id%type;
2328 l_assignment_number per_all_assignments_f.assignment_number%type;
2329 l_employee_name per_all_people_f.last_name%type;
2330 l_reason fnd_new_messages.message_text%type;
2331
2332
2333 begin
2334
2335
2336 OPEN c_ps_issued(p_assignment_id,p_financial_year);
2337 FETCH c_ps_issued into l_assignment_id;
2338 if c_ps_issued%found then
2339 OPEN c_get_details(l_assignment_id,p_financial_year_end);
2340 FETCH c_get_details into l_employee_name
2341 ,l_assignment_number;
2342
2343 if not exc_tab1.exists(l_assignment_id) then
2344 exc_tab1(l_assignment_id).employee_name:=l_employee_name;
2345 exc_tab1(l_assignment_id).assignment_number:=l_assignment_number;
2346 exc_tab1(l_assignment_id).assignment_id:=l_assignment_id;
2347 l_reason:=fnd_message.get_string('PER','HR_AU_SELF_PRINTED_PS_ISSUED');
2348 exc_tab1(l_assignment_id).reason:=l_reason;
2349 end if;
2350 CLOSE c_get_details;
2351 CLOSE c_ps_issued;
2352 return l_assignment_id;
2353 end if;
2354
2355
2356 CLOSE c_ps_issued;
2357
2358 OPEN c_eit_updated(p_assignment_id,p_financial_year);
2359 FETCH c_eit_updated into l_assignment_id;
2360 if c_eit_updated%found then
2361 OPEN c_get_details(l_assignment_id,p_financial_year_end);
2362 FETCH c_get_details into l_employee_name
2363 ,l_assignment_number;
2364
2365 if not exc_tab1.exists(l_assignment_id) then
2366 exc_tab1(l_assignment_id).employee_name:=l_employee_name;
2367 exc_tab1(l_assignment_id).assignment_number:=l_assignment_number;
2368 exc_tab1(l_assignment_id).assignment_id:=l_assignment_id;
2369 l_reason:=fnd_message.get_string('PER','HR_AU_MANUAL_PS_ISSUED');
2370 exc_tab1(l_assignment_id).reason:=l_reason;
2371 end if;
2372 CLOSE c_get_details;
2373 CLOSE c_eit_updated;
2374 return exc_tab1.count;
2375 end if;
2376
2377
2378 CLOSE c_eit_updated;
2379
2380 return 0;
2381 end populate_exclusion_table;
2382
2383 /* Start of Bug : 3186840 */
2384 /* Bug 8587013 - Added balances 'Reportable Employer Superannuation Contributions' and 'Exempt Foreign Employment Income'
2385 and removed Other Income balance */
2386 PROCEDURE populate_group_def_bal_ids(p_dimension_name pay_balance_dimensions.dimension_name%TYPE
2387 ,p_business_group_id per_business_groups.business_group_id%TYPE)
2388 IS
2389
2390 CURSOR csr_group_def_bal_ids IS
2391 SELECT decode(pbt.balance_name,'Lump Sum A Payments',1,'Lump Sum B Payments',2,
2392 'Lump Sum D Payments',3,'Union Fees',4,'Lump Sum C Deductions',5,
2393 'Termination Deductions',6,'Total_Tax_Deductions',7,'Earnings_Total',8,'Leave Payments Marginal',9,
2394 'CDEP',10,'Reportable Employer Superannuation Contributions', 11 ,'Workplace Giving Deductions', 12 ,
2395 'Exempt Foreign Employment Income' ,13) sort_index /*4015082 , 8587013*/
2396 , pdb.defined_balance_id
2397 FROM pay_balance_types pbt
2398 , pay_defined_balances pdb
2399 , pay_balance_dimensions pbd
2400 WHERE pbt.legislation_code = 'AU'
2401 AND pbt.balance_name in
2402 ('Lump Sum A Payments','Lump Sum B Payments','Lump Sum D Payments',
2403 'Union Fees','Lump Sum C Deductions','Termination Deductions',
2404 'Total_Tax_Deductions','Earnings_Total','Leave Payments Marginal','CDEP',
2405 'Workplace Giving Deductions','Reportable Employer Superannuation Contributions','Exempt Foreign Employment Income') /* 4015082 , 8587013*/
2406 AND pdb.balance_type_id = pbt.balance_type_id
2407 AND pdb.balance_dimension_id = pbd.balance_dimension_id
2408 AND pbd.legislation_code = 'AU'
2409 AND pdb.legislation_code = 'AU'
2410 AND pbd.dimension_name = p_dimension_name
2411 ORDER BY sort_index;
2412
2413 l_sort_index number;
2414 l_def_bal_id pay_defined_balances.defined_balance_id%TYPE;
2415
2416 /* bug 7571001 - added csr_group_alw_def_bal_ids cursor for allowance balances */
2417 CURSOR csr_group_alw_def_bal_ids IS
2418 select pbt.balance_name
2419 ,pdb.defined_balance_id
2420 from pay_balance_types pbt
2421 ,pay_defined_balances pdb
2422 ,pay_balance_dimensions pbd
2423 where pdb.balance_type_id = pbt.balance_type_id
2424 AND pdb.balance_dimension_id = pbd.balance_dimension_id
2425 AND pbd.dimension_name = p_dimension_name
2426 AND pdb.business_group_id = p_business_group_id
2427 AND pbd.legislation_code = 'AU'
2428 AND exists (
2429 select null
2430 from PAY_BAL_ATTRIBUTE_DEFINITIONS pbad
2431 ,pay_balance_attributes pba
2432 ,pay_defined_balances pdb2
2433 ,pay_balance_dimensions pbd2
2434 where pbad.attribute_name = 'AU_EOY_ALLOWANCE'
2435 and pba.attribute_id = pbad.attribute_id
2436 and pba.defined_balance_id = pdb2.defined_balance_id
2437 and pdb2.business_group_id = p_business_group_id
2438 and pbt.balance_type_id = pdb2.balance_type_id
2439 and pbd2.balance_dimension_id = pdb2.balance_dimension_id
2440 and pbd2.dimension_name = '_ASG_LE_YTD'
2441 and pbd2.legislation_code = 'AU'
2442 ) ;
2443 /*Changes for 9147438 start */
2444 CURSOR csr_fw_grp_def_bal_ids IS
2445 SELECT decode(pbt.balance_name,
2446 'Earnings_Total',1
2447 , 'Leave Payments Marginal',2
2448 , 'Workplace Giving Deductions',3
2449 , 'Lump Sum E Payments',4
2450 , 'Retro Earnings Leave Loading GT 12 Mths Amount',5
2451 , 'Retro Earnings Spread GT 12 Mths Amount',6
2452 , 'Retro Pre Tax GT 12 Mths Amount',7
2453 , 'Total_Tax_Deductions',8
2454 , 'Termination Deductions',9
2455 , 'Lump Sum C Deductions',10
2456 , 'Foreign Tax Deductions',11
2457 , 'Lump Sum A Payments',12
2458 , 'Lump Sum D Payments',13
2459 , 'Reportable Employer Superannuation Contributions',14
2460 , 'Union Fees',15
2461 , 'CDEP',16
2462 , 'Exempt Foreign Employment Income',17
2463 , 'Retro LT 12 Mths Prev Yr Amount', 18
2464 , 'Retro Earnings Leave Loading LT 12 Mths Prev Yr Amount', 19
2465 , 'Retro Earnings Spread LT 12 Mths Prev Yr Amount', 20
2466 , 'Retro Pre Tax LT 12 Mths Prev Yr Amount', 21
2467 , 'Retro LT 12 Mths Curr Yr Amount', 22
2468 , 'Retro Earnings Leave Loading LT 12 Mths Curr Yr Amount', 23
2469 , 'Retro Earnings Spread LT 12 Mths Curr Amount', 24
2470 , 'Retro Tax GT12 Amount', 25
2471 , 'Retro Tax LT12 Prev Amount', 26
2472 , 'Retro Tax LT12 Curr Amount', 27
2473 , 'Foreign Leave Payments', 28
2474 , 'Retro Earnings Additional GT 12 Mths Amount', 29 -- bug 13362286
2475 ) sort_index,
2476 pbt.balance_type_id balance_type_id
2477 FROM pay_balance_types pbt
2478 WHERE pbt.legislation_code = 'AU'
2479 and pbt.balance_name in (
2480 'Earnings_Total'
2481 , 'Leave Payments Marginal'
2482 , 'Workplace Giving Deductions'
2483 , 'Lump Sum E Payments'
2484 , 'Retro Earnings Leave Loading GT 12 Mths Amount'
2485 , 'Retro Earnings Spread GT 12 Mths Amount'
2486 , 'Retro Pre Tax GT 12 Mths Amount'
2487 , 'Total_Tax_Deductions'
2488 , 'Termination Deductions'
2489 , 'Lump Sum C Deductions'
2490 , 'Foreign Tax Deductions'
2491 , 'Lump Sum A Payments'
2492 , 'Lump Sum D Payments'
2493 , 'Reportable Employer Superannuation Contributions'
2494 , 'Union Fees'
2495 , 'CDEP'
2496 , 'Exempt Foreign Employment Income' --bug
2497 /* start bug 9950136 */
2498 , 'Retro LT 12 Mths Prev Yr Amount'
2499 , 'Retro Earnings Leave Loading LT 12 Mths Prev Yr Amount'
2500 , 'Retro Earnings Spread LT 12 Mths Prev Yr Amount'
2501 , 'Retro Pre Tax LT 12 Mths Prev Yr Amount'
2502 , 'Retro LT 12 Mths Curr Yr Amount'
2503 , 'Retro Earnings Leave Loading LT 12 Mths Curr Yr Amount'
2504 , 'Retro Earnings Spread LT 12 Mths Curr Amount'
2505 , 'Retro Tax GT12 Amount'
2506 , 'Retro Tax LT12 Prev Amount'
2507 , 'Retro Tax LT12 Curr Amount'
2508 /* end bug 9950136 */
2509 , 'Foreign Leave Payments'
2510 , 'Retro Earnings Additional GT 12 Mths Amount' -- bug 13362286
2511 )
2512 ORDER BY sort_index;
2513 /*Changes for 9147438 end */
2514
2515 cnt number := 1;
2516
2517 BEGIN
2518
2519 g_debug := hr_utility.debug_enabled;
2520
2521 g_dimension_name := p_dimension_name;
2522
2523 IF g_debug THEN
2524 hr_utility.trace('Dimension is ' || p_dimension_name);
2525 END IF;
2526
2527 /* Group Level Defined Balance IDs get stored in the PL/SQL table with the following order.
2528
2529 ---------------------------------------------------------
2530 Location ID Group Level Balance Name
2531 '_LE_YTD' or '_PAY_LE_YTD'
2532 ---------------------------------------------------------
2533 1 Lump Sum A Payments
2534 2 Lump Sum B Payments
2535 3 Lump Sum D Payments
2536 4 Union Fees
2537 5 Lump Sum C Deductions
2538 6 Termination Deduction
2539 7 Total_Tax_Deductions
2540
2541 Bug: 3186840 - Four more Balances included for GROUP LEVEL functionality
2542
2543 8 Earnings_Total
2544 9 Leave Payments Marginal
2545 10 CDEP
2546 11 Reportable Employer Superannuation Contributions
2547 12 Workplace Giving Deductions
2548 13 Exempt Foreign Employment Income
2549 --------------------------------------------------------- */
2550
2551 FOR csr_rec IN csr_group_def_bal_ids
2552 LOOP
2553 g_input_group_details_table(csr_rec.sort_index).defined_balance_id := csr_rec.defined_balance_id;
2554
2555 IF g_debug THEN
2556 hr_utility.trace(csr_rec.sort_index || ' ' || g_input_group_details_table(csr_rec.sort_index).defined_balance_id);
2557 END IF;
2558
2559 END LOOP;
2560
2561
2562 /* Changes for 9147438 start */
2563
2564 FOR csr_rec IN csr_fw_grp_def_bal_ids
2565 LOOP
2566 g_input_fw_grp_dtls_table(csr_rec.sort_index).balance_type_id := csr_rec.balance_type_id;
2567
2568 IF g_debug THEN
2569 hr_utility.trace(csr_rec.sort_index || ' ' || g_input_fw_grp_dtls_table(csr_rec.sort_index).balance_type_id);
2570 END IF;
2571
2572 END LOOP;
2573 /* Changes for 9147438 end */
2574
2575 /* bug 7571001 - populating defined_defined_balance_id of _LE_YTD for allowance balances */
2576 g_input_group_alw_table.delete;
2577 FOR csr_rec IN csr_group_alw_def_bal_ids LOOP
2578 g_input_group_alw_table(cnt).defined_balance_id := csr_rec.defined_balance_id;
2579
2580 IF g_debug THEN
2581 hr_utility.trace( ' Defined Balance id of ' || csr_rec.balance_name || ' => ' || g_input_group_alw_table(cnt).defined_balance_id);
2582 END IF;
2583
2584 cnt := cnt + 1;
2585 END LOOP;
2586
2587
2588 --Except Tax-Unit_id other Context table values are not required
2589
2590 g_context_table(1).jurisdiction_code := NULL;
2591 g_context_table(1).source_id := NULL;
2592 g_context_table(1).source_text := NULL;
2593 g_context_table(1).source_number := NULL;
2594 g_context_table(1).source_text2 := NULL;
2595
2596 END populate_group_def_bal_ids;
2597
2598 PROCEDURE get_group_values_bbr
2599 (p_year_start DATE
2600 ,p_year_end DATE
2601 ,p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE DEFAULT NULL
2602 ,p_date_earned IN date
2603 ,p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE
2604 ,p_group_output_tab OUT NOCOPY bal_tab ) IS
2605
2606 BEGIN
2607
2608 g_total_allowance :=0;
2609 bal_id.delete;
2610 g_result_group_details_table.delete;
2611
2612 g_context_table(1).tax_unit_id := p_tax_unit_id;
2613
2614 IF (g_dimension_name = '_LE_YTD') THEN
2615 pay_balance_pkg.set_context('DATE_EARNED',fnd_date.date_to_canonical(p_date_earned));
2616 END IF;
2617
2618 pay_balance_pkg.get_value(p_assignment_action_id => p_assignment_action_id,
2619 p_defined_balance_lst => g_input_group_details_table,
2620 p_context_lst => g_context_table,
2621 p_output_table => g_result_group_details_table);
2622
2623 IF g_debug THEN
2624 FOR i IN 1..g_result_group_details_table.last
2625 LOOP
2626 IF g_result_group_details_table.exists(i) THEN
2627 hr_utility.trace('Balance Value ' || i || g_result_group_details_table(i).balance_value);
2628 END IF;
2629 END LOOP;
2630 END IF;
2631
2632 /* Changes for 9147438 start */
2633 g_bal_dim_level := 'G';
2634
2635 /* Bug 8587013 - Added code for RESC and Exempt Foreign Employment Income balances.
2636 The Other Income balance is removed and the value is set to zero */
2637 bal_id(1).balance_value := Total_Lump_Sum_A_Payments;
2638 bal_id(2).balance_value := Total_Lump_Sum_B_Payments;
2639 bal_id(3).balance_value := Total_Lump_Sum_D_Payments;
2640 bal_id(4).balance_value := Total_Union_fees;
2641 bal_id(5).balance_value := Total_Tax_deductions;
2642
2643 /* Bug: 3186840 - Included 4 more group level balances retrieval */
2644 bal_id(7).balance_value := g_result_group_details_table(9).balance_value; -- Leave Payments Marginal
2645 bal_id(8).balance_value := get_total_cdep; -- CDEP
2646 bal_id(9).balance_value := 0; -- Other Income 8587013
2647 bal_id(10).balance_value := get_total_workplace; -- Workplace Giving Deductions /* 4015082 */
2648 bal_id(13).balance_value := Total_Foreign_Income; -- bug#10143762
2649 bal_id(6).balance_value := g_result_group_details_table(8).balance_value ; -- Earnings_Total
2650
2651 bal_id(11).balance_value :=get_total_allowances(p_year_start, p_year_end, null, null, p_tax_unit_id); -- bug 7571001
2652 /* Begin 8587013 */
2653 bal_id(12).balance_value := Total_RESC; -- Reportable Employer Superannuation Contributions
2654 /* Changes for 9147438 end */
2655 p_group_output_tab := bal_id;
2656
2657 END get_group_values_bbr;
2658
2659 PROCEDURE get_assgt_curr_term_values_bbr
2660 ( p_year_start IN date
2661 , p_year_end IN date
2662 , p_assignment_id IN pay_assignment_actions.assignment_id%type
2663 , p_fbt_rate IN ff_globals_f.global_value%TYPE
2664 , p_ml_rate IN ff_globals_f.global_value%TYPE
2665 , p_assignment_action_id IN pay_assignment_actions.assignment_action_id%type
2666 , p_tax_unit_id IN hr_all_organization_units.organization_id%TYPE
2667 , p_emp_type IN varchar2
2668 , p_term_output_tab OUT NOCOPY bal_tab) IS
2669
2670 g_debug boolean;
2671
2672 BEGIN
2673
2674 g_debug := hr_utility.debug_enabled;
2675
2676 g_pre01jul1983_value :=0;
2677 g_post30jun1983_value :=0;
2678 g_etp_gross :=0;
2679 g_etp_tax :=0;
2680 g_assessable :=0;
2681 g_total_allowance :=0; /*Bug 4888097*/
2682 g_total_fbt := 0; /*Bug 4888097*/
2683 g_lump_sum_e := 0; /*Bug 4888097*/
2684
2685 g_context_table.delete;
2686 g_context_table(1).tax_unit_id := p_tax_unit_id;
2687
2688 bal_id.delete;
2689 g_result_term_details_table.delete;
2690
2691 /* ---------------------------------------------------------
2692 Location ID Group Level Balance Name
2693 '_ASG_LE_YTD'
2694 ---------------------------------------------------------
2695 For all Employees (Current and Terminated):
2696 --------------------------------------------
2697 1 Lump Sum E Payments
2698
2699 For Terminated Employees only:
2700 -------------------------------
2701
2702 2 Lump Sum C Payments
2703 3 Invalidity Payments
2704 4 Lump Sum C Deductions
2705 --------------------------------------------------------- */
2706
2707 g_input_term_details_table(1).defined_balance_id := g_db_id_lsep;
2708 /* bug8711855 - Added new defined balance ids for Lump Sum E */
2709 g_input_term_details_table(2).defined_balance_id := g_db_id_rll;
2710 g_input_term_details_table(3).defined_balance_id := g_db_id_res;
2711 g_input_term_details_table(4).defined_balance_id := g_db_id_rpt;
2712 /* Bug 13989281 - Change in index starts */
2713 g_input_term_details_table(5).defined_balance_id := g_db_id_rea;
2714
2715 /* Bug 8769345 - Added code to hold the defined balance ids of ETP Taxable and Tax Free balances in pl/sql table */
2716 /* bug8711855 - Modified g_input_term_details_table index ids */
2717 /* bug 14703826 - Removed 13 - 16 indexes */
2718 IF (p_emp_type = 'T') THEN
2719
2720 g_input_term_details_table(6).defined_balance_id := g_db_id_lscp;
2721 g_input_term_details_table(7).defined_balance_id := g_db_id_ip;
2722 g_input_term_details_table(8).defined_balance_id := g_db_id_lscd;
2723 g_input_term_details_table(9).defined_balance_id := g_db_id_tftn;
2724 g_input_term_details_table(10).defined_balance_id := g_db_id_ttn;
2725 g_input_term_details_table(11).defined_balance_id := g_db_id_tftp;
2726 g_input_term_details_table(12).defined_balance_id := g_db_id_ttp;
2727 /* Bug 13989281 - Change in index ends */
2728 END IF;
2729
2730 FOR i IN 1..g_input_term_details_table.last
2731 LOOP
2732 IF g_input_term_details_table.exists(i) THEN
2733 IF g_debug THEN
2734 hr_utility.trace(i || ' ' || g_input_term_details_table(i).defined_balance_id);
2735 END IF;
2736 END IF;
2737 END LOOP;
2738
2739 pay_balance_pkg.get_value
2740 ( p_assignment_action_id => p_assignment_action_id
2741 , p_defined_balance_lst => g_input_term_details_table
2742 , p_context_lst => g_context_table
2743 , p_output_table => g_result_term_details_table);
2744
2745 IF g_debug THEN
2746 FOR i IN 1..g_result_term_details_table.last
2747 LOOP
2748 IF g_result_term_details_table.exists(i) THEN
2749 hr_utility.trace('Balance Value ' || i || g_result_term_details_table(i).balance_value);
2750 END IF;
2751 END LOOP;
2752 END IF;
2753
2754 g_bal_dim_level := 'T';
2755
2756 /* bug 7571001 - Removed the code to get allowance as it moved to get_group_values_bbr for group level reporting */
2757 bal_id(1).balance_value := get_total_fbt(p_year_start,p_assignment_id,p_tax_unit_id,p_fbt_rate,p_ml_rate,null);
2758 bal_id(2).balance_value := Total_Lump_Sum_E_Payments(p_year_end,p_assignment_id,p_tax_unit_id) ; --2610141
2759
2760 IF (p_emp_type = 'T') THEN
2761
2762 bal_id(3).balance_value := ETP_DETAILS(p_assignment_id,p_year_start,p_year_end);
2763 bal_id(4).balance_value := POST30JUN1983_VALUE; -- g_post30jun1983_value
2764 bal_id(5).balance_value := TOTAL_INVALIDITY_PAYMENTS; -- l_total_invalidity_payments
2765 bal_id(6).balance_value := ETP_GROSS; -- g_etp_gross
2766 bal_id(7).balance_value := ETP_TAX; -- g_etp_tax
2767 bal_id(8).balance_value := assessable_income; -- g_assessable
2768
2769 END IF;
2770
2771 IF g_debug THEN
2772 FOR i IN 1..bal_id.last
2773 LOOP
2774 IF bal_id.exists(i) THEN
2775 hr_utility.trace('Output Balance Value ' || i || bal_id(i).balance_value);
2776 END IF;
2777 END LOOP;
2778 END IF;
2779
2780 p_term_output_tab := bal_id;
2781
2782 END get_assgt_curr_term_values_bbr;
2783
2784
2785 ------------ --Bug#3749530 All the procedures added below are for archival model------
2786 -- On Submitting payrec-PS reuqest first archival proceudres are called
2787
2788 --------------------------------------------------------------------
2789 -- These are PUBLIC procedures are required by the Archive process.
2790 -- There names are stored in PAY_REPORT_FORMAT_MAPPINGS_F so that
2791 -- the archive process knows what code to execute for each step of
2792 -- the archive.
2793 --------------------------------------------------------------------
2794
2795 --------------------------------------------------------------------
2796 -- This procedure returns a sql string to select a range
2797 -- of assignments eligible for archival.
2798 --------------------------------------------------------------------
2799
2800 procedure range_code
2801 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
2802 p_sql out nocopy varchar2) is
2803 begin
2804 g_debug := hr_utility.debug_enabled;
2805 IF g_debug THEN
2806 hr_utility.set_location('Start of range_code',1);
2807 END if;
2808 p_sql := ' select distinct p.person_id' ||
2809 ' from per_people_f p,' ||
2810 ' pay_payroll_actions pa' ||
2811 ' where pa.payroll_action_id = :payroll_action_id' ||
2812 ' and p.business_group_id = pa.business_group_id' ||
2813 ' order by p.person_id';
2814 IF g_debug THEN
2815 hr_utility.set_location('End of range_code',2);
2816 END if;
2817 end range_code;
2818
2819
2820 --------------------------------------------------------------------
2821 -- This procedure is used to set global contexts
2822 -- however in current case it is a dummy procedure. In case this
2823 -- procedure is not present then archiver assumes that
2824 -- no archival is required.
2825 --------------------------------------------------------------------
2826
2827 procedure initialization_code
2828 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type) is
2829 begin
2830 NULL;
2831 END;
2832
2833 /*
2834 Bug 7138494 - Added Function range_person_on
2835 --------------------------------------------------------------------
2836 Name : range_person_on
2837 Type : Function
2838 Access: Private
2839 Description: Checks if RANGE_PERSON_ID is enabled for
2840 Archive process.
2841 --------------------------------------------------------------------
2842 */
2843
2844 FUNCTION range_person_on
2845 RETURN BOOLEAN
2846 IS
2847
2848 CURSOR csr_action_parameter is
2849 select parameter_value
2850 from pay_action_parameters
2851 where parameter_name = 'RANGE_PERSON_ID';
2852
2853 CURSOR csr_range_format_param is
2854 select par.parameter_value
2855 from pay_report_format_parameters par,
2856 pay_report_format_mappings_f map
2857 where map.report_format_mapping_id = par.report_format_mapping_id
2858 and map.report_type = 'AU_REC_PS_ARCHIVE'
2859 and map.report_format = 'AU_REC_PS_ARCHIVE'
2860 and map.report_qualifier = 'AU'
2861 and par.parameter_name = 'RANGE_PERSON_ID'; -- Bug fix 5567246
2862
2863 l_return boolean;
2864 l_action_param_val varchar2(30);
2865 l_report_param_val varchar2(30);
2866
2867 BEGIN
2868
2869 g_debug := hr_utility.debug_enabled;
2870
2871 IF g_debug
2872 THEN
2873 hr_utility.set_location('range_person_on',10);
2874 END IF;
2875
2876 BEGIN
2877
2878 open csr_action_parameter;
2879 fetch csr_action_parameter into l_action_param_val;
2880 close csr_action_parameter;
2881
2882 IF g_debug
2883 THEN
2884 hr_utility.set_location('range_person_on',20);
2885 END IF;
2886
2887 open csr_range_format_param;
2888 fetch csr_range_format_param into l_report_param_val;
2889 close csr_range_format_param;
2890 IF g_debug
2891 THEN
2892 hr_utility.set_location('range_person_on',30);
2893 END IF;
2894 EXCEPTION WHEN NO_DATA_FOUND THEN
2895 l_return := FALSE;
2896 END;
2897 --
2898 IF g_debug
2899 THEN
2900 hr_utility.set_location('range_person_on',40);
2901 END IF;
2902
2903 IF l_action_param_val = 'Y' AND l_report_param_val = 'Y' THEN
2904 l_return := TRUE;
2905 hr_utility.trace('Range Person = True');
2906 ELSE
2907 l_return := FALSE;
2908 END IF;
2909 --
2910 RETURN l_return;
2911 --
2912 END range_person_on;
2913
2914 --------------------------------------------------------------------
2915 -- This procedure further restricts the assignment_id's
2916 -- returned by range_code
2917 -- It inserts the record in pay_assignment_Actions
2918 -- Which are then used in main report query to get assignment_ids
2919 --------------------------------------------------------------------
2920 procedure assignment_action_code
2921 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
2922 p_start_person_id in per_all_people_f.person_id%type,
2923 p_end_person_id in per_all_people_f.person_id%type,
2924 p_chunk in number) is
2925
2926
2927 l_asgid per_assignments_f.assignment_id%type;
2928 l_next_action_id pay_assignment_actions.assignment_action_id%type;
2929
2930
2931 l_lst_yr_start date;
2932 l_lst_fbt_yr_start date;
2933 l_assignment_id varchar2(50);
2934 l_business_group_id hr_organization_units.organization_id%type;
2935 l_legal_employer varchar2(50);
2936 l_employee_type varchar2(2);
2937 l_payroll_id varchar2(50);
2938 l_fin_year_start date;
2939 l_fin_year_end date;
2940 l_fbt_year_start date;
2941 l_fbt_year_end date;
2942 l_lst_yr_term VARCHAR2(2);
2943
2944
2945 cursor get_params(c_payroll_action_id per_all_assignments_f.assignment_id%type)
2946 is
2947 select to_date('01-07-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY')
2948 Financial_year_start
2949 ,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),6,4),'DD-MM-YYYY')
2950 Financial_year_end
2951 ,to_date('01-04-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY')
2952 FBT_year_start
2953 ,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY')
2954 FBT_year_end
2955 ,decode(pay_core_utils.get_parameter('EMPLOYEE_TYPE',legislative_parameters),'C','Y','T','N','B','%')
2956 Employee_type
2957 ,pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters) Registered_Employer
2958 ,decode(pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters),null,'%', pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters)) Assignment_id
2959 ,decode(pay_core_utils.get_parameter('PAYROLL_ID',legislative_parameters),null,'%',pay_core_utils.get_parameter('PAYROLL_ID',legislative_parameters)) payroll_id
2960 ,pay_core_utils.get_parameter('LST_YR_TERM',legislative_parameters) lst_yr_term /*Bug3661230*/
2961 ,pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters) Business_group_id
2962 from pay_payroll_actions
2963 where payroll_action_id =c_payroll_Action_id;
2964
2965 cursor next_action_id is
2966 select pay_assignment_actions_s.nextval
2967 from dual;
2968
2969 cursor c_asgids(p_assignment_id varchar2,
2970 p_business_group_id hr_organization_units.organization_id%type,
2971 p_legal_employer varchar2,
2972 p_employee_type varchar2,
2973 p_payroll_id varchar2,
2974 p_fin_year_start date,
2975 p_fin_year_end date,
2976 p_lst_fbt_yr_start date,
2977 p_fbt_year_start date,
2978 p_fbt_year_end date,
2979 p_lst_year_start date
2980 )
2981 is
2982 SELECT /*+ INDEX(pap per_people_f_pk)
2983 INDEX(rppa pay_payroll_actions_pk)
2984 INDEX(paa per_assignments_f_N12)
2985 INDEX(pps per_periods_of_service_pk)
2986 */ paa.assignment_id
2987 from per_people_f pap
2988 ,per_assignments_f paa
2989 ,pay_payroll_actions rppa
2990 ,per_periods_of_service pps
2991 where rppa.payroll_action_id = p_payroll_action_id
2992 and pap.person_id between p_start_person_id and p_end_person_id
2993 and pap.person_id = paa.person_id
2994 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
2995 and pps.period_of_service_id = paa.period_of_service_id
2996 and pap.person_id = pps.person_id
2997 and rppa.business_group_id=paa.business_group_id
2998 and nvl(pps.actual_termination_date, p_lst_year_start) >= p_lst_year_start
2999 and p_fin_year_end between pap.effective_start_date and pap.effective_end_date
3000 /* Start of Bug: 3872211 */
3001 and paa.effective_end_date = (SELECT MAX(effective_end_date) /*4377367*/
3002 FROM per_assignments_f iipaf
3003 WHERE iipaf.assignment_id = paa.assignment_id
3004 AND iipaf.effective_end_date >= p_fbt_year_start
3005 AND iipaf.effective_start_date <= p_fin_year_end
3006 AND iipaf.payroll_id IS NOT NULL) /* Bug#4688800 */
3007 and paa.payroll_id like p_payroll_id
3008 /* End of Bug: 3872211 */
3009 AND EXISTS (SELECT /*+ ORDERED */''
3010 FROM
3011 per_assignments_f paaf
3012 ,pay_assignment_actions rpac
3013 ,pay_payroll_actions rppa
3014 where rppa.effective_date between p_fin_year_start and p_fin_year_end /*Bug3048962 */
3015 and rppa.action_type in ('R','Q','B','I')
3016 and rpac.tax_unit_id = p_legal_employer
3017 and rppa.payroll_action_id = rpac.payroll_action_id
3018 and rpac.action_status = 'C'
3019 and rppa.payroll_id = paaf.payroll_id
3020 and paaf.assignment_id = paa.assignment_id
3021 and rpac.assignment_id = paa.assignment_id
3022 and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date
3023 UNION
3024 SELECT /*+ ORDERED */''
3025 FROM
3026 per_assignments_f paaf
3027 ,pay_assignment_actions rpac
3028 ,pay_payroll_actions rppa
3029 where pps.actual_termination_date between p_lst_fbt_yr_start and p_fbt_year_end /*Bug3263659 */
3030 and rppa.effective_date between p_fbt_year_start and p_fbt_year_end
3031 and pay_balance_pkg.get_value(g_fbt_defined_balance_id, rpac.assignment_action_id
3032 + decode(rppa.payroll_id, 0, 0, 0),p_legal_employer,null,null,null,null) > to_number(g_fbt_threshold)
3033 and rppa.action_type in ('R','Q','B','I')
3034 and rpac.tax_unit_id = p_legal_employer
3035 and rppa.payroll_action_id = rpac.payroll_action_id
3036 and rpac.action_status = 'C'
3037 and rppa.payroll_id = paaf.payroll_id
3038 and paaf.assignment_id = paa.assignment_id
3039 and rpac.assignment_id = paa.assignment_id
3040 and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date
3041 );
3042
3043 /*
3044 Bug 7138494 - Added Cursor for Range Person
3045 - Uses person_id in pay_population_ranges
3046 --------------------------------------------------------------------+
3047 -- Cursor : c_range_asgids
3048 -- Description : Fetches assignments For Recconciling Payment Summary
3049 -- Returns DISTINCT assignment_id
3050 -- Used when RANGE_PERSON_ID feature is enabled
3051 --------------------------------------------------------------------+
3052 */
3053
3054 /* Bug: 12400821 - Performance Improvement done */
3055 CURSOR c_range_asgids
3056 (p_assignment_id varchar2,
3057 p_business_group_id hr_organization_units.organization_id%type,
3058 p_legal_employer varchar2,
3059 p_employee_type varchar2,
3060 p_payroll_id varchar2,
3061 p_fin_year_start date,
3062 p_fin_year_end date,
3063 p_lst_fbt_yr_start date,
3064 p_fbt_year_start date,
3065 p_fbt_year_end date,
3066 p_lst_year_start date
3067 )
3068 IS
3069 SELECT paa.assignment_id
3070 from per_people_f pap
3071 ,per_assignments_f paa
3072 ,pay_payroll_actions rppa
3073 ,per_periods_of_service pps
3074 ,pay_population_ranges ppr
3075 where rppa.payroll_action_id = p_payroll_action_id
3076 and rppa.payroll_action_id = ppr.payroll_action_id
3077 AND ppr.payroll_action_id = p_payroll_action_id
3078 and ppr.chunk_number = p_chunk
3079 and ppr.person_id = pap.person_id
3080 and pap.person_id = paa.person_id
3081 AND PAA.PERSON_ID = PPR.PERSON_ID
3082 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
3083 and pps.period_of_service_id = paa.period_of_service_id
3084 and pap.person_id = pps.person_id
3085 and rppa.business_group_id=paa.business_group_id
3086 and nvl(pps.actual_termination_date, p_lst_year_start) >= p_lst_year_start
3087 and p_fin_year_end between pap.effective_start_date and pap.effective_end_date
3088 /* Start of Bug: 3872211 */
3089 and paa.effective_end_date = (SELECT MAX(effective_end_date) /*4377367*/
3090 FROM per_assignments_f iipaf
3091 WHERE iipaf.assignment_id = paa.assignment_id
3092 AND IIPAF.PERSON_ID = PAA.PERSON_ID
3093 AND iipaf.effective_end_date >= p_fbt_year_start
3094 AND iipaf.effective_start_date <= p_fin_year_end
3095 AND iipaf.payroll_id IS NOT NULL) /* Bug#4688800 */
3096 and paa.payroll_id like p_payroll_id
3097 /* End of Bug: 3872211 */
3098 AND EXISTS (SELECT /*+ ORDERED */''
3099 FROM
3100 per_assignments_f paaf
3101 ,pay_assignment_actions rpac
3102 ,pay_payroll_actions rppa
3103 where rppa.effective_date between p_fin_year_start and p_fin_year_end /*Bug3048962 */
3104 and rppa.action_type in ('R','Q','B','I')
3105 and rpac.tax_unit_id = p_legal_employer
3106 and rppa.payroll_action_id = rpac.payroll_action_id
3107 and rpac.action_status = 'C'
3108 and rppa.payroll_id = paaf.payroll_id
3109 and paaf.assignment_id = paa.assignment_id
3110 and rpac.assignment_id = paa.assignment_id
3111 and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date
3112 UNION
3113 SELECT /*+ ORDERED */''
3114 FROM
3115 per_assignments_f paaf
3116 ,pay_assignment_actions rpac
3117 ,pay_payroll_actions rppa
3118 where pps.actual_termination_date between p_lst_fbt_yr_start and p_fbt_year_end /*Bug3263659 */
3119 and rppa.effective_date between p_fbt_year_start and p_fbt_year_end
3120 and pay_balance_pkg.get_value(g_fbt_defined_balance_id, rpac.assignment_action_id
3121 + decode(rppa.payroll_id, 0, 0, 0),p_legal_employer,null,null,null,null) > to_number(g_fbt_threshold)
3122 and rppa.action_type in ('R','Q','B','I')
3123 and rpac.tax_unit_id = p_legal_employer
3124 and rppa.payroll_action_id = rpac.payroll_action_id
3125 and rpac.action_status = 'C'
3126 and rppa.payroll_id = paaf.payroll_id
3127 and paaf.assignment_id = paa.assignment_id
3128 and rpac.assignment_id = paa.assignment_id
3129 and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date
3130 );
3131
3132
3133 cursor c_asgid_only(p_assignment_id varchar2,
3134 p_business_group_id hr_organization_units.organization_id%type,
3135 p_legal_employer varchar2,
3136 p_employee_type varchar2,
3137 p_payroll_id varchar2,
3138 p_fin_year_start date,
3139 p_fin_year_end date,
3140 p_lst_fbt_yr_start date,
3141 p_fbt_year_start date,
3142 p_fbt_year_end date,
3143 p_lst_year_start date
3144 )
3145 is
3146 SELECT /*+ INDEX(pap per_people_f_pk)
3147 INDEX(paa per_assignments_f_fk1)
3148 INDEX(paa per_assignments_f_N12)
3149 INDEX(rppa pay_payroll_actions_pk)
3150 INDEX(pps per_periods_of_service_n3)
3151 */ distinct paa.assignment_id
3152 from per_people_f pap
3153 ,per_assignments_f paa
3154 ,pay_payroll_actions rppa
3155 ,per_periods_of_service pps
3156 where rppa.payroll_action_id = p_payroll_action_id
3157 and pap.person_id between p_start_person_id and p_end_person_id
3158 and pap.person_id = paa.person_id
3159 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
3160 and pps.period_of_service_id = paa.period_of_service_id
3161 and paa.assignment_id = p_assignment_id
3162 and pap.person_id = pps.person_id
3163 and rppa.business_group_id=paa.business_group_id
3164 and nvl(pps.actual_termination_date, p_lst_year_start) >= p_lst_year_start
3165 and p_fin_year_end between pap.effective_start_date and pap.effective_end_date
3166 -- 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
3167 and paa.effective_end_date = (select max(effective_end_date) /*4377367*/
3168 From per_assignments_f iipaf
3169 WHERE iipaf.assignment_id = paa.assignment_id
3170 and iipaf.effective_end_date >= p_fbt_year_start
3171 and iipaf.effective_start_date <= p_fin_year_end
3172 AND iipaf.payroll_id IS NOT NULL) /* Bug#4688800 */
3173 and paa.payroll_id like p_payroll_id
3174 AND EXISTS (SELECT /*+ ORDERED */''
3175 FROM
3176 per_assignments_f paaf
3177 ,pay_assignment_actions rpac
3178 ,pay_payroll_actions rppa
3179 where rppa.effective_date between p_fin_year_start and p_fin_year_end /*Bug3048962 */
3180 and rppa.action_type in ('R','Q','B','I')
3181 and rpac.tax_unit_id = p_legal_employer
3182 and rppa.payroll_action_id = rpac.payroll_action_id
3183 and rpac.action_status = 'C'
3184 and rppa.payroll_id = paaf.payroll_id
3185 and paaf.assignment_id = p_assignment_id
3186 and rpac.assignment_id = p_assignment_id
3187 and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date
3188 UNION
3189 SELECT /*+ ORDERED */''
3190 FROM
3191 per_assignments_f paaf
3192 ,pay_assignment_actions rpac
3193 ,pay_payroll_actions rppa
3194 where pps.actual_termination_date between p_lst_fbt_yr_start and p_fbt_year_end /*Bug3263659 */
3195 and rppa.effective_date between p_fbt_year_start and p_fbt_year_end
3196 and pay_balance_pkg.get_value(g_fbt_defined_balance_id, rpac.assignment_action_id
3197 + decode(rppa.payroll_id, 0, 0, 0),p_legal_employer,null,null,null,null) > to_number(g_fbt_threshold)
3198 and rppa.action_type in ('R','Q','B','I')
3199 and rpac.tax_unit_id = p_legal_employer
3200 and rppa.payroll_action_id = rpac.payroll_action_id
3201 and rpac.action_status = 'C'
3202 and rppa.payroll_id = paaf.payroll_id
3203 and paaf.assignment_id = p_assignment_id
3204 and rpac.assignment_id = p_assignment_id
3205 and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date
3206 );
3207
3208
3209 Cursor c_fbt_balance is
3210 select pdb.defined_balance_id
3211 from pay_balance_types pbt,
3212 pay_defined_balances pdb,
3213 pay_balance_dimensions pbd
3214 where pbt.balance_name ='Fringe Benefits'
3215 and pbt.balance_type_id = pdb.balance_type_id
3216 and pdb.balance_dimension_id = pbd.balance_dimension_id
3217 and pbd.legislation_code ='AU'
3218 and pbd.dimension_name ='_ASG_LE_FBT_YTD' --2610141
3219 and pbd.legislation_code = pbt.legislation_code
3220 and pbd.legislation_code = pdb.legislation_code;
3221
3222 /* Bug 5708255 */
3223 -------------------------------------------
3224 -- Added cursor to get value of global FBT_THRESHOLD
3225 --------------------------------------------
3226 CURSOR c_get_fbt_global(c_year_end DATE)
3227 IS
3228 SELECT global_value
3229 FROM ff_globals_f
3230 WHERE global_name = 'FBT_THRESHOLD'
3231 AND legislation_code = 'AU'
3232 AND c_year_end BETWEEN effective_start_date
3233 AND effective_end_date ;
3234
3235
3236
3237 begin
3238
3239 g_debug := hr_utility.debug_enabled;
3240
3241 IF g_debug THEN
3242 hr_utility.set_location('Start of assignment_action_code',1);
3243 END IF;
3244 -------------------------------------------------------------
3245 -- get the paramters for archival process
3246 -------------------------------------------------------------
3247 open get_params(p_payroll_action_id);
3248 fetch get_params
3249 into l_fin_year_start
3250 ,l_fin_year_end
3251 ,l_fbt_year_start
3252 ,l_fbt_year_end
3253 ,l_employee_type
3254 ,l_legal_employer
3255 ,l_assignment_id
3256 ,l_payroll_id
3257 ,l_lst_yr_term
3258 ,l_business_group_id ;
3259 close get_params;
3260
3261 if g_debug then
3262 hr_utility.trace('p_assignment_id :'||l_assignment_id);
3263 hr_utility.trace('p_business_group_id :'||l_business_group_id);
3264 hr_utility.trace('p_legal_employer :' ||l_legal_employer);
3265 hr_utility.trace('p_employee_type :'||l_employee_type);
3266 hr_utility.trace('p_payroll_id :'|| l_payroll_id);
3267 hr_utility.trace('p_fin_year_start :' ||l_fin_year_start);
3268 hr_utility.trace('p_fin_year_end :' ||l_fin_year_end);
3269 hr_utility.trace('p_fbt_year_start :' ||l_fbt_year_start);
3270 hr_utility.trace('p_fbt_year_end :' ||l_fbt_year_end);
3271 END if;
3272
3273 IF (l_lst_yr_term = 'N') THEN
3274 l_lst_yr_start := to_date('01/01/1900','DD/MM/YYYY');
3275 l_lst_fbt_yr_start := to_date('01/01/1900','DD/MM/YYYY');
3276 ELSE
3277 l_lst_yr_start := add_months(l_fin_year_start,-12);
3278 l_lst_fbt_yr_start := l_fbt_year_start;
3279 END IF;
3280 ------------------------------------------
3281
3282 /* Bug 5708255 */
3283 open c_get_fbt_global (add_months(l_fin_year_end,-3)); /* Add_months included for bug 5333143 */
3284 fetch c_get_fbt_global into g_fbt_threshold;
3285 close c_get_fbt_global;
3286
3287 -- Added for bug 3034189
3288 If g_fbt_defined_balance_id = 0 OR g_fbt_defined_balance_id IS NULL Then
3289 Open c_fbt_balance;
3290 Fetch c_fbt_balance into g_fbt_defined_balance_id;
3291 Close c_fbt_balance;
3292 End if;
3293
3294
3295
3296 IF l_assignment_id = '%' THEN -- For multiple Assignments
3297
3298 /* Bug 7138494 - Added Changes for Range Person
3299 - Call Cursor using pay_population_ranges if Range Person Enabled
3300 Else call Old Cursor
3301 */
3302 IF range_person_on
3303 THEN
3304
3305 IF g_debug
3306 THEN
3307 hr_utility.set_location('Range Peron set - Use range cursor',1000);
3308 END IF;
3309 FOR csr_rec IN c_range_asgids(l_assignment_id ,
3310 l_business_group_id ,
3311 l_legal_employer ,
3312 l_employee_type ,
3313 l_payroll_id ,
3314 l_fin_year_start ,
3315 l_fin_year_end ,
3316 l_lst_fbt_yr_start,
3317 l_fbt_year_start ,
3318 l_fbt_year_end,
3319 l_lst_yr_start)
3320 LOOP
3321
3322 IF g_debug THEN
3323 hr_utility.set_location('Calling hr_nonrun_asact.insact for assignment id :'||l_asgid,2);
3324 END if;
3325
3326 OPEN next_action_id;
3327 FETCH next_action_id INTO l_next_action_id;
3328 CLOSE next_action_id;
3329
3330 hr_nonrun_asact.insact(l_next_action_id,csr_rec.assignment_id,p_payroll_action_id,p_chunk,null);
3331
3332 IF g_debug THEN
3333 hr_utility.set_location('After calling hr_nonrun_asact.insact',3);
3334 END if;
3335
3336 END LOOP;
3337
3338 ELSE /* Use Old Logic - No Range Person */
3339
3340 OPEN c_asgids(l_assignment_id ,
3341 l_business_group_id ,
3342 l_legal_employer ,
3343 l_employee_type ,
3344 l_payroll_id ,
3345 l_fin_year_start ,
3346 l_fin_year_end ,
3347 l_lst_fbt_yr_start,
3348 l_fbt_year_start ,
3349 l_fbt_year_end,
3350 l_lst_yr_start);
3351 LOOP
3352 FETCH c_asgids INTO l_asgid;
3353 IF c_asgids%NOTFOUND THEN
3354 close c_asgids;
3355 exit;
3356 ELSE
3357 IF g_debug THEN
3358 hr_utility.set_location('Calling hr_nonrun_asact.insact for assignment id :'||l_asgid,2);
3359 END if;
3360 OPEN next_action_id;
3361 FETCH next_action_id INTO l_next_action_id;
3362 CLOSE next_action_id;
3363 hr_nonrun_asact.insact(l_next_action_id,l_asgid,p_payroll_action_id,p_chunk,null);
3364
3365 IF g_debug THEN
3366 hr_utility.set_location('After calling hr_nonrun_asact.insact',3);
3367 END if;
3368 END IF;
3369 END LOOP;
3370 END IF; /* Range Person Check */
3371
3372 ELSE -- only for Single Assignment
3373 hr_utility.trace('before open');
3374 OPEN c_asgid_only(l_assignment_id ,
3375 l_business_group_id ,
3376 l_legal_employer ,
3377 l_employee_type ,
3378 l_payroll_id ,
3379 l_fin_year_start ,
3380 l_fin_year_end ,
3381 l_lst_fbt_yr_start,
3382 l_fbt_year_start ,
3383 l_fbt_year_end,
3384 l_lst_yr_start);
3385 LOOP
3386 hr_utility.trace('in loop');
3387 FETCH c_asgid_only INTO l_asgid;
3388 IF c_asgid_only%NOTFOUND THEN
3389 CLOSE c_asgid_only;
3390 EXIT;
3391 ELSE
3392 IF g_debug THEN
3393 hr_utility.set_location('Calling hr_nonrun_asact.insact for assignment id :'||l_asgid,2);
3394 END IF;
3395 OPEN next_action_id;
3396 FETCH next_action_id INTO l_next_action_id;
3397 CLOSE next_action_id;
3398 hr_nonrun_asact.insact(l_next_action_id,l_asgid,p_payroll_action_id,p_chunk,NULL);
3399
3400 IF g_debug THEN
3401 hr_utility.set_location('After calling hr_nonrun_asact.insact',3);
3402 END if;
3403 END IF;
3404 END LOOP;
3405 END IF;
3406 IF g_debug THEN
3407 hr_utility.set_location('End of assignment_action_code',4);
3408 END if;
3409
3410 EXCEPTION
3411 WHEN OTHERS THEN
3412 IF g_debug THEN
3413 hr_utility.set_location('error raised in assignment_action_code procedure ',5);
3414 hr_utility.trace(sqlerrm);
3415 END if;
3416 raise;
3417 END;
3418
3419 --------------------------------------------------------------------
3420 -- This procedure is actually used to archive data . It
3421 -- internally calls private procedures to archive balances ,
3422 -- employee details, employer details and supplier details .
3423 --------------------------------------------------------------------
3424 procedure archive_code
3425 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3426 p_effective_date in date)is
3427 begin
3428 NULL;
3429 END;
3430
3431
3432 --------------------------------------------------------------------
3433 -- This procedure is called during de-iniitalization
3434 -- After inserting assignment_actions this procedure is called
3435 -- It submits the request for running the report
3436 -- And the report displays detials for all the archived assignments.
3437 --------------------------------------------------------------------
3438
3439 PROCEDURE spawn_ps_report
3440 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type) is
3441
3442 ps_request_id NUMBER;
3443 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
3444 l_business_group_id number;
3445 l_start_date date;
3446 l_end_date date;
3447 l_effective_date date;
3448 l_legal_employer number;
3449 l_FINANCIAL_YEAR_code varchar2(10);
3450 l_TEST_EFILE varchar2(10);
3451 l_FINANCIAL_YEAR varchar2(10);
3452 l_legislative_param varchar2(200);
3453 l_count number;
3454 l_print_style VARCHAR2(2);
3455 l_print_together VARCHAR2(80);
3456 l_print_return BOOLEAN;
3457 l_procedure varchar2(50);
3458 l_short_report_name VARCHAR2(30); /* 6839263 */
3459 l_xml_options BOOLEAN ; /* 6839263 */
3460
3461
3462 --------------------------------------------------------------------+
3463 -- Cursor : csr_params
3464 -- Description : Fetches User Parameters from Legislative_paramters
3465 -- column.
3466 --------------------------------------------------------------------+
3467
3468 CURSOR csr_report_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
3469 IS
3470 select pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters) Financial_year
3471 ,pay_core_utils.get_parameter('EMPLOYEE_TYPE',legislative_parameters) Employee_type
3472 ,pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters) legal_employer
3473 ,pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters) Assignment_id
3474 ,pay_core_utils.get_parameter('PAYROLL_ID',legislative_parameters) payroll_id
3475 ,pay_core_utils.get_parameter('LST_YR_TERM',legislative_parameters) lst_yr_term
3476 ,pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters) Business_group_id
3477 ,pay_core_utils.get_parameter('OUTPUT_TYPE',legislative_parameters)p_output_type /* Bug# 6839263 */
3478 from pay_payroll_actions
3479 where payroll_action_id =c_payroll_Action_id;
3480
3481
3482
3483 cursor csr_get_print_options(p_payroll_action_id NUMBER) IS
3484 SELECT printer,
3485 print_style,
3486 decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output
3487 ,number_of_copies /* Bug 4116833 */
3488 FROM pay_payroll_actions pact,
3489 fnd_concurrent_requests fcr
3490 WHERE fcr.request_id = pact.request_id
3491 AND pact.payroll_action_id = p_payroll_action_id;
3492
3493
3494 rec_print_options csr_get_print_options%ROWTYPE;
3495
3496 l_parameters csr_report_params%ROWTYPE;
3497
3498 BEGIN
3499 g_debug :=hr_utility.debug_enabled ;
3500
3501 IF g_debug THEN
3502 hr_utility.set_location('Start of spawn_ps_report',1);
3503 END if;
3504
3505 l_count :=0;
3506 ps_request_id :=-1;
3507
3508 -- Set User Parameters for Report.
3509
3510 OPEN csr_report_params(p_payroll_action_id);
3511 FETCH csr_report_params INTO l_parameters;
3512 CLOSE csr_report_params;
3513
3514 /* Start of 6839263 */
3515 IF l_parameters.p_output_type = 'XML_PDF' then
3516 l_short_report_name := 'PYAURECPR_XML';
3517
3518 l_xml_options := fnd_request.add_layout
3519 (template_appl_name => 'PAY',
3520 template_code => 'PYAURECPR_XML',
3521 template_language => 'en',
3522 template_territory => 'US',
3523 output_format => 'PDF');
3524
3525 ELSE
3526 l_short_report_name := 'PYAURECPR';
3527 END IF;
3528 /* End of 6839263 */
3529
3530 IF g_debug THEN
3531 hr_utility.set_location('in BG_ID '||l_parameters.Business_group_id,1);
3532 hr_utility.set_location('in payroll_parameters.id '||l_parameters.payroll_id,3);
3533 hr_utility.set_location('in asg_id '||l_parameters.assignment_id,4);
3534 hr_utility.set_location('in legal employer '||l_parameters.legal_employer,8);
3535 hr_utility.set_location('in emp_type '||l_parameters.employee_type,14);
3536 hr_utility.set_location('fin_year'||l_parameters.Financial_year,15);
3537 hr_utility.set_location('lst_yr_trm'||l_parameters.lst_yr_term,16);
3538 end if;
3539
3540 IF g_debug THEN
3541 hr_utility.set_location('Afer payroll action ' || p_payroll_action_id , 125);
3542 hr_utility.set_location('Before calling report',24);
3543 END IF;
3544
3545 OPEN csr_get_print_options(p_payroll_action_id);
3546 FETCH csr_get_print_options INTO rec_print_options;
3547 CLOSE csr_get_print_options;
3548
3549 l_print_together := nvl(fnd_profile.value('CONC_PRINT_TOGETHER'), 'N');
3550 --
3551 -- Set printer options
3552 l_print_return := fnd_request.set_print_options
3553 (printer => rec_print_options.printer,
3554 style => rec_print_options.print_style,
3555 copies => rec_print_options.number_of_copies, /* Bug 4116833 */
3556 save_output => hr_general.char_to_bool(rec_print_options.save_output),
3557 print_together => l_print_together);
3558 -- Submit report
3559 IF g_debug THEN
3560 hr_utility.set_location('payroll_action id '|| p_payroll_action_id,25);
3561 END IF;
3562
3563 ps_request_id := fnd_request.submit_request
3564 ('PAY',
3565 l_short_report_name,
3566 NULL,
3567 NULL,
3568 FALSE,
3569 'P_PAYROLL_ACTION_ID='||p_payroll_action_id,
3570 'P_ASSIGNMENT_ID='||l_parameters.assignment_id,
3571 'P_BUSINESS_GROUP_ID='||l_parameters.business_group_id,
3572 'P_EMPLOYEE_TYPE='||l_parameters.employee_type,
3573 'P_FINANCIAL_YEAR='||l_parameters.Financial_year,
3574 'P_LST_YR_TERM='||l_parameters.lst_yr_term,
3575 'P_PAYROLL_ID='||l_parameters.payroll_id,
3576 'P_REGISTERED_EMPLOYER='||l_parameters.legal_employer);
3577
3578 IF g_debug THEN
3579 hr_utility.set_location('End of spawn_ps_report',4);
3580 END IF;
3581
3582 EXCEPTION
3583 WHEN others THEN
3584 IF g_debug THEN
3585 hr_utility.set_location('error raised in spawn_ps_report procedure ',5);
3586 END if;
3587 RAISE;
3588 END;
3589
3590
3591
3592
3593 begin
3594 g_debug := hr_utility.debug_enabled;
3595 g_pre01jul1983_value :=0;
3596 g_post30jun1983_value :=0;
3597 g_etp_gross :=0;
3598 g_etp_tax :=0;
3599 g_assessable :=0;
3600 g_lump_sum_e :=0;
3601 g_total_gross :=0;
3602 g_total_workplace :=0; /* 4015082 */
3603 g_total_cdep :=0;
3604 g_total_allowance :=0;
3605 g_total_fbt :=0;
3606 g_total_gross :=0;
3607 /*Begin 8587013 - Set the values of variables to zero*/
3608 g_total_resc :=0;
3609 g_total_foreign_income :=0;
3610 /*End 8587013*/
3611 /* Added for 9147438 */
3612 g_fw_fbt_balance :=0;
3613 x :=0;
3614 g_bal_dim_level := 'N';
3615
3616 end pay_au_recon_summary;