[Home] [Help]
PACKAGE BODY: APPS.PAY_AU_REC_DET_PAYSUM_MODE
Source
1 package body pay_au_rec_det_paysum_mode as
2 /* $Header: pyaureps.pkb 120.61.12020000.5 2012/12/07 03:17:55 skshin ship $*/
3 /* ------------------------------------------------------------------------+
4 *** Program: pay_au_rec_det_paysum_mode (Package Body)
5 ***
6 *** Change History
7 ***
8 *** Date Changed By Version Bug No Description of Change
9 *** --------- ---------- ------- ------ --------------------------------+
10 *** 22 DEC 04 avenkatk 1.0 3899641 Initial Version
11 *** 30 DEC 04 avenkatk 1.1 3899641 Changed Package Name
12 *** 31 DEC 04 avenkatk 1.2 3899641 Changed Flexfield context for Standard
13 *** Balances
14 *** 6 Jan 05 hnainani 1.3 4085496 Added Workplace Giving Deductions changes
15 *** 13 Jan 05 avenkatk 1.5 4116833 Set the Report Request number of copies to be read from Archive Request.
16 *** 20 Jan 05 avenkatk 1.6 4133326 Procedure get_fbt_balance - Added FBT Balance > 1000 check.
17 *** 07 Feb 05 abhkumar 1.7 4142159 Conditional deletion of generated actions.
18 *** 07 Feb 05 ksingla 1.8 4161460 Modified the cursor get_allowance_balances.
19 *** 11 Feb 05 avenkatk 1.9 4179109 Modified c_ps_element_details to check for Non Taxable Allowances
20 *** 11-FEB-05 abhkumar 1.10 4132149 Modified initialisation_code to initialise the global variables for legislative parameters.
21 *** 17 Feb 05 abhkumar 1.11 4161460 Rolled back changes made in version 115.8.
22 *** 24 Feb 05 avenkatk 1.12 4201894 Added check to Archive Retro Payments < $400 as Taxable Earnings.
23 *** 22 Apr 05 ksingla 1.13 4177679 Added a new paramter to the function call etp_prepost_ratios.
24 *** 05 May 05 ksingla 1.14 4353285 Removed to_char from fnd_request to payroll_id and assignment_id.
25 *** 05 May 05 abhkumar 1.15 4377367 Added join in the cursor csr_assignment_paysum to archive the end-dated employees.
26 *** 22 Jun 05 abhargav 1.16 4363057 Changes due to Retro Tax enhancement.
27 *** 04 Jul 05 avenkatk 1.17 3891577 Introduced Logic and procedures for Summary Report - Payment Summary Mode
28 *** (A) Parameter REP_MODE - to decide whether Report is Summary or Detail
29 *** 2 AUG 05 hnainani 1.18 Bug#4478752 Added quotes to -999 to allow for Character values in flexfield
30 *** 12-Sep-05 avenkatk 1.19 3891577 (A) Backed out template Name parameter from procedure spawn_summary_reports
31 *** Template Details not specified in this step of 2-step process.
32 *** 02-OCT-05 abhkumar 1.21 4688800 Modified assignment action code to pick those employees who do have payroll attached
33 at start of the financial year but not at the end of financial year.
34 *** 05-DEC-05 abhkumar 1.22 4177630 Modified the get_allowance_balances procedure to get balance values in BBR mode.
35 *** 06-DEC-05 abhkumar 1.23 4177630 Modified the code to raise error message when there is no defined balance id for the allowance balance.
36 *** 15-DEC-05 ksingla 1.24 4872594 Modified code and put round off for total assessable income.
37 *** 28-DEC-05 avenkatk 1.25 4726352 Modified code to ensure Manual PS Details are archive for Summary Report.
38 *** 27-FEB-06 ksingla 1.26 5063359 Modified cursor c_ps_element_details for Employer Charges.
39 *** 27-MAR-06 avenkatk 1.27 5119734 Modified cursor c_ps_element_details for reporting Allowances.
40 *** 20-Jun-06 ksingla 1.28 5333143 Add_months included to fetch FBT_RATE and MEDICARE_LEVY
41 *** 29-Oct-06 hnainani 1.29 5603254 Added Function get_element_payment_hours to fetch hours in c_element_details.
42 ***29 OCT 06 hnainani 1.30 5603524 Removed function get_element_payment_hours - used function defined in pay_au_Rec_det_archive instead
43 *** 16-Nov-06 abhargav 115.31 5603254 Modified cursor c_ps_element_details to remove join for table pay_input_values_f piv2 and pay_run_result_values prrv2.
44 *** 19-Dec-06 ksingla 115.32 5708255 Added code to get value of global FBT_THRESHOLD
45 *** 22-Dec-06 ksingla 115.33 5708255 Changed 1000 to g_fbt_threshold in cursor csr_assignment_only_paysum
46 *** 27-Dec-06 ksingla 115.34 5708255 Added to_number to all occurrences of g_fbt_threshold
47 *** 8-Jan-06 ksingla 115.35 Bug#5743196 Added nvl to cursor c_allowance_balance
48 *** 7-Feb-06 priupadh 115.36 5846278 In Cursor C_PS_ELEMENT_DETAILS added Lump Sum E Payments in not exists clause
49 *** 13-Feb-06 priupadh 115.37 N/A Version for restoring Triple Maintanence between 11i-->R12(Branch) -->R12(MainLine)
50 *** 3-MAR-07 hnainani 115.38 5599310 Added Function get_element_payment_rate to fetch rate in c_ps_element_details.
51 *** 26-FEB-08 vdabgar 115.39 6839263 Modified proc spawn_archive_reports,csr_params and csr_report_params cursors
52 *** to call the concurrent programs accordingly.
53 *** 13-MAR-08 avenkatk 115.40 6839263 Backed out changes in initialization_code and assignment_section_code
54 *** 21-Mar-08 avenkatk 115.41 6839263 Added Logic to set the OPP Template options for PDF output
55 *** 26-May-08 bkeshary 115.42 7030285 Modified the calculation for Assessable Income
56 *** 26-May-08 bkeshary 115.43 7030285 Added File Change History
57 *** 26-May-08 bkeshary 115.44 7030285 Modified the Comment lines
58 *** 18-Jun-08 avenkatk 115.45 7138494 Added Changes for RANGE_PERSON_ID
59 *** 18-Jun-08 avenkatk 115.46 7138494 Modified Allowance Cursor for peformance
60 *** 18-Dec-08 skshin 115.47 7571001 Modified archive_element_details, summary_rep_populate_allowance and get_allowance_balances
61 *** 27-Jan-09 skshin 115.48 7571001 Modified Cursor c_ps_element_details to have separate cursor c_ps_alw_details for allowances and removed summary_rep_populate_allowance procedure and p_allowance_exist in archive_element_details
62 *** 13-Feb-09 mdubasi 115.49 7590936 Replaced secure view hr_organization_units with hr_all_organization_units
63 *** in the cursor c_employee_details
64 *** 23-Feb-09 mdubasi 115.50 7590936 Replaced second secure view hr_organization_units with hr_all_organization_units
65 *** in the cursor c_employee_details
66 *** 28-Apr-09 pmatamsr 115.51 8441044 Cursor c_get_pay_effective_date is modified to consider Lump Sum E payments for payment summary gross calculation
67 *** for action types 'B' and 'I'.
68 *** 23-Jun-09 pmatamsr 115.52 8587013 Added changes to support the archival of new balances 'Reportable Employer Superannuation Contributions'
69 *** and 'Exempt Foreign Employment Income' introduced as part of PS Changes effective from 01-Jul-2009.
70 *** 25-Jun-09 pmatamsr 115.53 8587013 Resolved GSCC errors.
71 *** 08-Aug-09 pmatamsr 115.54 8760756 Added a join condition and removed hours and rate columns from c_ps_alw_details cursor query
72 *** for reporting of allowances in EOY Report.
73 *** 07-Sep-09 pmatamsr 115.55 8769345 Modified initialization_code,archive_code,archive_balance_details and archive_element_details procedures to support
74 *** the archival of new ETP Taxable and Tax Free balances introduced as part of statutory changes to Super Rollover.
75 *** 26-Nov-09 avenkatk 115.57 9146069 Modified the terms display in the ETP Payments section to be in sync with Validation Report
76 *** 26-Nov-09 avenkatk 115.58 9146069 Modified condition to check for Invalidity Balance
77 *** 19-Nov-09 skshin 115.60 8711855 Modifed Adjust_lumpsum_E_payments procedure to call pay_au_payment_summary.get_retro_lumpsumE_value
78 *** 19-Nov-09 skshin 115.61 9190980 Modified c_ps_element_details cursor to exclude retro gt12 balances
79 *** 15-Dec-09 pmatamsr 115.62 9190980 Added code to report Retro GT12 Pre Tax Deductions seperately under 'Lump Sum E Pre Tax' and 'Retro Pre Tax < $400'
80 *** 15-Dec-09 pmatamsr 115.63 9190980 Commented Retro Pre Tax GT 12 Mths Amount element in c_ps_element_details cursor so that the element is repoted
81 *** in Pre Tax Deductions section.
82 *** 15-Dec-09 pmatamsr 115.64 9190980 Uncommented Retro Pre Tax GT 12 Mths Amount element in c_ps_element_details cursor.
83 *** 13-Jan-09 pmatamsr 115.65 9226023 Added code to support the calculation of Taxable and Tax free portions of ETP for terminated employees processed
84 *** before applying 8769345 patch.
85 *** 27-Apr-10 pmatamsr 115.66 9638323 Added code to initialize g_tax_free_etp and g_taxable_etp global variables.
86 *** 04-May-10 avenkatk 115.67 9147421 Foreign Workers Reporting. Changed c_ps_element_details cursor for new element classification.
87 *** 13-May-10 pmatamsr 115.68 9700346 Modified the initialization position of g_adjusted_lump_sum_e_pay,g_adj_lump_sum_pre_tax,g_tax_free_etp and g_taxable_etp global variables.
88 *** 28-Sep-10 dduvvuri 115.69 9147438 Changes done for Foreign Worker EOY reporting processes
89 *** 30-Sep-10 dduvvuri 115.70 9147438 Fixed some FBT threshold related issues found during dev testing.
90 *** 22-Nov-10 skshin 115.73 10143762 Adjusted Exempt Foreign Income from Gross_Earnings for both INB and FW type.
91 *** 10216064 LT12_Curr retro and LT12_Curr retro Tax are to be reported on each type of payment summary based on
92 *** assignment type of original period. The other retros and associated retro Taxes are to be reported on INB payment summary.
93 *** 01-Dec-10 avenkatk 115.74 10331262 Made changes for FW Leave and Termination payment reporting
94 *** 08-Jun-11 skshin 115.75 12615137 Added more filter condition and ORDERED hint in c_ps_alw_details cursor
95 *** 30-Jun-11 skshin 115.76 12603219 Modified csr_range_assignment_paysum cursor and c_ps_element_details cursor for performance
96 *** 07-Jul-11 dduvvuri 115.79 12725161 This version is a rollback of 115.78 version with the bug 12698821 fixed in a different way to avoid eoy related issues
97 *** 26-Jul-11 dduvvuri 115.80 12400821 Performance improvements in EXISTS clause in all 3 assignment_action_code cursors done for westpac customer.
98 *** 18-Jan-11 jmarupil 115.81 13051557 Modified g_fw_gross_type intialization value from 'P' to NULL
99 *** 02-Feb-12 skshin 115.82 13362286 Add Retro Earnings Additional GT12 balance for Lump Sum E and foreign worker
100 *** 26-Apr-12 jmarupil 115.83 14005702 Modified g_fw_gross_type intialization value to ''
101 *** 07-Dec-12 skshin 115.84 14703826 Modified to retrieve new ETP balances of Excluded and Non Excluded
102 *** ------------------------------------------------------------------------+
103 */
104
105 /* Package - Functional comments.
106 This package is used to archive data in pay_action_information for TWO reports,
107 i. End of Year Reconciliation Detail Report (Bug 3899641)
108 ii. End of Year Reconciliation Summary Report (Bug 3891577)
109
110 Parameter REP_MODE in legislative parameters is used to distinguish the type of Report.
111 For Detail report - REP_MODE returns NULL
112 For Summary report - REP_MODE returns 'SUMM'
113 Based on the type of report,appropriate data is archived.
114
115 Any changes made to this package must be Functionally/Technically tested against both reports.
116 */
117
118 g_arc_payroll_action_id pay_payroll_actions.payroll_action_id%type;
119 g_business_group_id hr_all_organization_units.organization_id%type;
120 g_debug boolean ;
121 g_package constant varchar2(60) := 'pay_au_rec_det_paysum_mode.'; -- Global to store package name for tracing.
122 g_end_date date;
123 g_start_date date;
124 g_tax_unit_id pay_assignment_actions.tax_unit_id%type;
125 g_attribute_id pay_balance_attributes.attribute_id%type; -- bug 7571101
126 g_taxable_etp number; /* Start 9226023 - Global varaibles to store Taxable and Tax Free portions of ETP */
127 g_tax_free_etp number; /* End 9226023 */
128
129 g_fbt_threshold ff_globals_f.global_value%TYPE ; /* Bug 5708255 */
130 p_lump_sum_E_ptd_tab pay_balance_pkg.t_balance_value_tab; -- bug8711855
131
132 /* Changes for 9147438 start */
133
134 -- Foreign Worker status check global variables
135 g_fw_check varchar2(1);
136 g_fw_fbt_check varchar2(2);
137 g_fw_fbt_term_check varchar2(2);
138 g_payg_fw_type varchar2(10) :='P';
139 g_fw_gross_type varchar2(10); /* Bug 13051557 */
140 g_fw_earnings number;
141 g_fw_lump_sumE number;
142 g_fw_workplace number;
143 g_fw_Total_Tax number;
144 g_fw_Foreign_Tax number;
145 g_fw_lump_sumA number;
146 g_fw_lump_sumD number;
147 g_fw_resc number;
148 g_fw_union number;
149 g_fw_cdep number;
150 g_fw_alw_total number;
151 g_fw_fbt_balance number;
152 g_fw_reporting_fbt number;
153
154 /* start bug 9950136*/
155 g_fw_lt12_prev_earnings number;
156 g_fw_lt12_curr_earnings number;
157 g_retro_fw_gross number;
158 g_retro_fw_tax number;
159 /* start bug 9950136*/
160
161 -- Defined_balance_IDs for Foregin Worker balances
162 p_fw_fbt_bal_type_tab pay_au_foreign_workers.tab_bal_type;
163 p_fw_balance_type_tab pay_au_foreign_workers.tab_bal_type;
164 p_fw_alw_balance_type_tab pay_au_foreign_workers.tab_bal_type;
165 g_fw_result_table pay_balance_pkg.t_detailed_bal_out_tab;
166 g_fw_input_alw_table pay_au_foreign_workers.tab_bal_type;
167 g_fw_result_alw_table pay_balance_pkg.t_detailed_bal_out_tab;
168
169 -- Global variables for comparison
170 g_net_balance number ;
171 g_fw_union_value number;
172 type r_allowance_bal is record (balance_name pay_balance_types.balance_name%type, balance_value number);
173 type tab_allownace_balance is table of r_allowance_bal index by binary_integer;
174
175 p_fw_union_tab_sorted tab_allownace_balance;
176 p_fw_alw_tab_sorted tab_allownace_balance;
177
178 type fw_gross_type_table is table of varchar2(1) index by binary_integer;
179 t_fw_gross_type fw_gross_type_table;
180 f_fw_date_tab_g pay_au_foreign_workers.tab_fw_dates;
181 j_fw_date_tab_g pay_au_foreign_workers.tab_fw_dates;
182
183 p_fw_allowance_tab pay_au_payment_summary.t_allowance_balance%type;
184 g_balance_type_id pay_balance_types.balance_type_id%type;
185 p_fw_union_balance_type_tab pay_au_foreign_workers.tab_bal_type;
186 l_fw_union_output_tab pay_balance_pkg.t_detailed_bal_out_tab;
187
188
189 /* Changes for 9147438 end */
190 --------------------------------------------------------------------
191 -- Name : range_code
192 -- Type : Proedure
193 -- Access: Public
194 -- This procedure returns a sql string to select a range
195 -- of assignments eligible for archival.
196 --
197 --------------------------------------------------------------------
198
199 procedure range_code
200 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type
201 ,p_sql out NOCOPY varchar2
202 ) is
203
204 l_procedure varchar2(200) ;
205
206 begin
207
208 g_debug :=hr_utility.debug_enabled ;
209
210 if g_debug then
211 l_procedure := g_package||'range_code';
212 hr_utility.set_location('Entering '||l_procedure,1);
213 end if ;
214
215 -- Archive the payroll action level data and EIT defintions.
216 -- sql string to SELECT a range of assignments eligible for archival.
217 p_sql := ' select distinct p.person_id' ||
218 ' from per_people_f p,' ||
219 ' pay_payroll_actions pa' ||
220 ' where pa.payroll_action_id = :payroll_action_id' ||
221 ' and p.business_group_id = pa.business_group_id' ||
222 ' order by p.person_id';
223
224 if g_debug then
225 hr_utility.set_location('Leaving '||l_procedure,1000);
226 end if;
227
228 end range_code;
229
230 --------------------------------------------------------------------
231 -- Name : initialization_code
232 -- Type : Proedure
233 -- Access: Public
234 -- This procedure builds a PL/SQL table with Defined Balance ID's
235 -- of Balances which need to be fetched using
236 --
237 --------------------------------------------------------------------
238
239 /* Bug 8587013 - Added balances 'Reportable Employer Superannuation Contributions', 'Exempt Foreign Employment Income'
240 and removed 'Other Income' balance. */
241 /* Bug 8769345 - Added ETP Taxable and Tax Free balances to the cursor */
242
243 procedure initialization_code
244 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type)
245 is
246
247 CURSOR csr_defined_balance_id
248 IS
249 SELECT decode(pbt.balance_name,'Earnings_Total',1,
250 'Direct Payments',2,
251 'Termination_Payments',3,
252 'Involuntary Deductions',4,
253 'Pre Tax Deductions',5,
254 'Termination Deductions',6,
255 'Voluntary Deductions',7,
256 'Total_Tax_Deductions',8,
257 'Earnings_Non_Taxable',9,
258 'Employer_Charges',10,
259 'Lump Sum A Payments',11,
260 'Lump Sum B Payments',12,
261 'Lump Sum C Payments',13,
262 'Lump Sum D Payments',14,
263 'Lump Sum E Payments',15,
264 'Invalidity Payments',16,
265 'CDEP',17,
266 'Leave Payments Marginal',18,
267 'Reportable Employer Superannuation Contributions',19,
268 'Union Fees',20,
269 'Workplace Giving Deductions' ,21,
270 'Exempt Foreign Employment Income',22,
271 'ETP Tax Free Payments Excluded',23, /*start bug 14703826*/
272 'ETP Tax Free Payments Non Excluded',24,
273 'ETP Taxable Payments Excluded',25,
274 'ETP Taxable Payments Non Excluded',26,
275 'ETP Deductions Excluded',27, /* 27- 30 Added for empty slots */
276 'ETP Deductions Excluded Part of Prev',28,
277 'ETP Deductions Non Excluded',29,
278 'ETP Deductions Non Excluded Part of Prev',30, /*end bug 14703826*/
279 'Retro Earnings Leave Loading GT 12 Mths Amount',31,
280 'Retro Earnings Spread GT 12 Mths Amount',32,
281 'Retro Pre Tax GT 12 Mths Amount',33,
282 'Lump Sum C Deductions',34, /* bug8711855 */
283 'Foreign Leave Payments',35, /* Start Bug 10331262 */
284 'Foreign Leave Payments Marginal',36,
285 'Foreign Lump Sum A Payments',37,
286 'Foreign Leave Component Deduction',38,
287 'Foreign Lump Sum A Deduction',39 /* End Bug 10331262 */
288 ,'Retro Earnings Additional GT 12 Mths Amount',40-- bug 13362286
289 ) sort_index,
290 pdb.defined_balance_id defined_balance_id,
291 pbt.balance_name
292 FROM pay_balance_types pbt,
293 pay_balance_dimensions pbd,
294 pay_defined_balances pdb
295 WHERE pbt.balance_name IN ('Earnings_Total','Direct Payments','Termination_Payments','Involuntary Deductions',
296 'Pre Tax Deductions','Termination Deductions','Voluntary Deductions','Total_Tax_Deductions',
297 'Earnings_Non_Taxable','Employer_Charges','Lump Sum A Payments','Lump Sum B Payments','Lump Sum C Payments',
298 'Lump Sum D Payments','Lump Sum E Payments','Invalidity Payments','CDEP','Leave Payments Marginal',
299 'Reportable Employer Superannuation Contributions','Union Fees', 'Workplace Giving Deductions','Exempt Foreign Employment Income',
300 'ETP Tax Free Payments Excluded','ETP Tax Free Payments Non Excluded','ETP Taxable Payments Excluded','ETP Taxable Payments Non Excluded',
301 'ETP Deductions Excluded','ETP Deductions Excluded Part of Prev','ETP Deductions Non Excluded','ETP Deductions Non Excluded Part of Prev',
302 'Retro Earnings Leave Loading GT 12 Mths Amount', 'Retro Earnings Spread GT 12 Mths Amount', 'Retro Pre Tax GT 12 Mths Amount','Lump Sum C Deductions'
303 ,'Retro Pre Tax GT 12 Mths Amount', 'Foreign Leave Payments', 'Foreign Leave Payments Marginal'
304 ,'Foreign Lump Sum A Payments', 'Foreign Leave Component Deduction', 'Foreign Lump Sum A Deduction' /* 10331262, 14703826 */
305 ,'Retro Earnings Additional GT 12 Mths Amount')
306 AND pbd.database_item_suffix = '_ASG_LE_YTD'
307 AND pbt.balance_type_id = pdb.balance_type_id
308 AND pbd.balance_dimension_id = pdb.balance_dimension_id
309 AND pbt.legislation_code = 'AU'
310 ORDER BY sort_index;
311
312 /* bug8711855 - Fetching defined_balance_ids of Lump Sum E balances_PTD */
313 CURSOR c_single_lumpsum_E_payment IS
314 SELECT decode(pbt.balance_name,
315 'Lump Sum E Payments', 1
316 ,'Retro Earnings Leave Loading GT 12 Mths Amount', 2
317 ,'Retro Earnings Spread GT 12 Mths Amount', 3
318 ,'Retro Pre Tax GT 12 Mths Amount', 4
319 ,'Retro Earnings Additional GT 12 Mths Amount', 5 -- bug 13362286
320 ) sort_index
321 , pdb.defined_balance_id defined_balance_id
322 FROM pay_balance_types pbt,
323 pay_defined_balances pdb,
324 pay_balance_dimensions pbd
325 WHERE pbt.legislation_code = 'AU'
326 AND pbt.balance_name in ( 'Lump Sum E Payments'
327 ,'Retro Earnings Leave Loading GT 12 Mths Amount'
328 ,'Retro Earnings Spread GT 12 Mths Amount'
329 ,'Retro Pre Tax GT 12 Mths Amount'
330 ,'Retro Earnings Additional GT 12 Mths Amount')
331 AND pbt.balance_type_id = pdb.balance_type_id
332 AND pbd.balance_dimension_id = pdb.balance_dimension_id
333 AND pbd.dimension_name = '_ASG_LE_PTD'
334 order by sort_index;
335
336 /*Bug 4132149 - Modification begins here */
337 cursor get_ps_params(c_payroll_action_id per_all_assignments_f.assignment_id%type)
338 is
339 select pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters) business_group_id
340 ,pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters) legal_employer
341 ,decode(pay_core_utils.get_parameter('PAYROLL',legislative_parameters),null,'%',pay_core_utils.get_parameter('PAYROLL',legislative_parameters)) payroll_id
342 ,decode(pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters),null,'%',pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters)) assignment_id
343 ,decode(pay_core_utils.get_parameter('EMPLOYEE_TYPE',legislative_parameters),'C','Y','T','N','B','%') employee_type
344 ,to_date('01-07-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fin_year_state_date
345 ,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),6,4),'DD-MM-YYYY') fin_year_end_date
346 ,to_date('01-04-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_start_date
347 ,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_end_date
348 ,pay_core_utils.get_parameter('LST_YR_TERM',legislative_parameters) lst_year_term
349 ,pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions /*Bug 4142159*/
350 ,decode(pay_core_utils.get_parameter('REP_MODE',legislative_parameters),'SUMM','S','D') report_mode /*Bug 3891577*/
351 from pay_payroll_actions
352 where payroll_action_id = c_payroll_action_id;
353
354 Cursor c_fbt_balance is
355 select pdb.defined_balance_id
356 from pay_balance_types pbt,
357 pay_defined_balances pdb,
358 pay_balance_dimensions pbd
359 where pbt.balance_name ='Fringe Benefits'
360 and pbt.balance_type_id = pdb.balance_type_id
361 and pdb.balance_dimension_id = pbd.balance_dimension_id /* Bug 2501105 */
362 and pbd.legislation_code ='AU'
363 and pbd.dimension_name ='_ASG_LE_FBT_YTD' --2610141
364 and pbd.legislation_code = pbt.legislation_code
365 and pbd.legislation_code = pdb.legislation_code;
366
367 /*Bug 4132149 - Modification ends here */
368
369 /* bug 7571001 - added get_balance_attribute cursor */
370 CURSOR get_balance_attribute (c_attribute_name PAY_BAL_ATTRIBUTE_DEFINITIONS.attribute_name%type) IS
371 select attribute_id
372 from PAY_BAL_ATTRIBUTE_DEFINITIONS
373 where attribute_name = c_attribute_name
374 ;
375
376 /* Changes for 9147438 starts*/
377
378 cursor c_get_fw_fbt_bal_type_id is
379 select balance_type_id
380 from pay_balance_types
381 where balance_name = 'Fringe Benefits'
382 and legislation_code = 'AU';
383
384 CURSOR c_get_fw_bal_type_id IS
385 select decode(pbt.balance_name,
386 'Earnings_Total',1
387 , 'Leave Payments Marginal',2
388 , 'Workplace Giving Deductions',3
389 , 'Lump Sum E Payments',4
390 , 'Retro Earnings Leave Loading GT 12 Mths Amount',5
391 , 'Retro Earnings Spread GT 12 Mths Amount',6
392 , 'Retro Pre Tax GT 12 Mths Amount',7
393 , 'Total_Tax_Deductions',8
394 , 'Termination Deductions',9
395 , 'Lump Sum C Deductions',10
396 , 'Foreign Tax Deductions',11
397 , 'Lump Sum A Payments',12
398 , 'Lump Sum D Payments',13
399 , 'Reportable Employer Superannuation Contributions',14
400 , 'Union Fees',15
401 , 'CDEP',16
402 , 'Exempt Foreign Employment Income',17
403 , 'Retro LT 12 Mths Prev Yr Amount', 18
404 , 'Retro Earnings Leave Loading LT 12 Mths Prev Yr Amount', 19
405 , 'Retro Earnings Spread LT 12 Mths Prev Yr Amount', 20
406 , 'Retro Pre Tax LT 12 Mths Prev Yr Amount', 21
407 , 'Retro LT 12 Mths Curr Yr Amount', 22
408 , 'Retro Earnings Leave Loading LT 12 Mths Curr Yr Amount', 23
409 , 'Retro Earnings Spread LT 12 Mths Curr Amount', 24
410 , 'Retro Tax GT12 Amount', 25
411 , 'Retro Tax LT12 Prev Amount', 26
412 , 'Retro Tax LT12 Curr Amount', 27
413 , 'Foreign Leave Payments',28
414 , 'Retro Earnings Additional GT 12 Mths Amount', 29 -- bug 13362286
415 , 'Retro Earnings Additional LT12 Prev Mths Amount', 30
416 , 'Retro Earnings Additional LT12 Curr Mths Amount', 31
417 ) sort_index,
418 pbt.balance_type_id balance_type_id
419 from pay_balance_types pbt
420 where pbt.balance_name in (
421 'Earnings_Total'
422 , 'Leave Payments Marginal'
423 , 'Workplace Giving Deductions'
424 , 'Lump Sum E Payments'
425 , 'Retro Earnings Leave Loading GT 12 Mths Amount'
426 , 'Retro Earnings Spread GT 12 Mths Amount'
427 , 'Retro Pre Tax GT 12 Mths Amount'
428 , 'Total_Tax_Deductions'
429 , 'Termination Deductions'
430 , 'Lump Sum C Deductions'
431 , 'Foreign Tax Deductions'
432 , 'Lump Sum A Payments'
433 , 'Lump Sum D Payments'
434 , 'Reportable Employer Superannuation Contributions'
435 , 'Union Fees'
436 , 'CDEP'
437 , 'Exempt Foreign Employment Income' -- bug 10143762
438 /* start bug 9950136*/
439 , 'Retro LT 12 Mths Prev Yr Amount'
440 , 'Retro Earnings Leave Loading LT 12 Mths Prev Yr Amount'
441 , 'Retro Earnings Spread LT 12 Mths Prev Yr Amount'
442 , 'Retro Pre Tax LT 12 Mths Prev Yr Amount'
443 , 'Retro LT 12 Mths Curr Yr Amount'
444 , 'Retro Earnings Leave Loading LT 12 Mths Curr Yr Amount'
445 , 'Retro Earnings Spread LT 12 Mths Curr Amount'
446 , 'Retro Tax GT12 Amount'
447 , 'Retro Tax LT12 Prev Amount'
448 , 'Retro Tax LT12 Curr Amount'
449 /* end bug 9950136*/
450 , 'Foreign Leave Payments'
451 , 'Retro Earnings Additional GT 12 Mths Amount'
452 , 'Retro Earnings Additional LT12 Prev Mths Amount'
453 , 'Retro Earnings Additional LT12 Curr Mths Amount'
454 )
455 and pbt.legislation_code = 'AU'
456 order by sort_index;
457
458 /* Changes for 9147438 ends */
459 l_procedure varchar2(200) ;
460 idx number;
461
462 begin
463
464 idx :=1;
465
466 g_debug :=hr_utility.debug_enabled ;
467 if g_debug then
468 l_procedure := g_package||'initialization_code';
469 hr_utility.set_location('Entering '||l_procedure,1);
470 end if;
471
472 g_balance_value_tab.delete;
473
474 /*Bug 4132149 - Modification begins here */
475
476 g_arc_payroll_action_id := p_payroll_action_id;
477
478 /* Fetch Params */
479 open get_ps_params(p_payroll_action_id);
480 fetch get_ps_params into g_parameters;
481 close get_ps_params;
482
483 g_business_group_id := g_parameters.business_group_id;
484 g_start_date := g_parameters.fin_year_start_date;
485 g_end_date := g_parameters.fin_year_end_date;
486 g_tax_unit_id := g_parameters.legal_employer;
487
488 if g_debug
489 then
490 hr_utility.set_location('p_payroll_action_id.........= ' || p_payroll_action_id,30);
491 hr_utility.set_location('g_parameters.business_group_id.........= ' || g_parameters.business_group_id,30);
492 hr_utility.set_location('g_parameters.payroll_id..............= ' || g_parameters.payroll_id,30);
493 hr_utility.set_location('g_parameters.legal_employer.........= ' || g_parameters.legal_employer,30);
494 hr_utility.set_location('g_parameters.assignment_id.........= ' || g_parameters.assignment_id,30);
495 hr_utility.set_location('g_parameters.fin_year_start_date..............= ' || g_parameters.fin_year_start_date,30);
496 hr_utility.set_location('g_parameters.fin_year_end_date................= ' || g_parameters.fin_year_end_date,30);
497 hr_utility.set_location('g_parameters.fbt_year_start_date..............= ' || g_parameters.fbt_year_start_date,30);
498 hr_utility.set_location('g_parameters.fbt_year_end_date................= ' || g_parameters.fbt_year_end_date,30);
499 hr_utility.set_location('g_parameters.employee_type..........= '||g_parameters.employee_type,30);
500 hr_utility.set_location('g_parameters.delete_actions..........= '||g_parameters.delete_actions,30); /*Bug 4142159*/
501 hr_utility.set_location('g_parameters.report_mode..........= '||g_parameters.report_mode,30); /*Bug 3891577*/
502 end if;
503
504
505 If g_fbt_defined_balance_id is null OR g_fbt_defined_balance_id =0 Then
506 Open c_fbt_balance;
507 Fetch c_fbt_balance into g_fbt_defined_balance_id;
508 Close c_fbt_balance;
509 End if;
510
511
512 /*Bug 4132149 - Modification ends here */
513
514 FOR csr_rec IN csr_defined_balance_id
515 LOOP
516 g_balance_value_tab(csr_rec.sort_index).defined_balance_id := csr_rec.defined_balance_id;
517 END LOOP;
518
519 IF g_debug THEN
520 hr_utility.trace('Defined Balance ids for YTD dimension');
521 hr_utility.trace('-------------------------------------');
522 hr_utility.trace('Earnings_Total ===>' || g_balance_value_tab(1).defined_balance_id);
523 hr_utility.trace('Workplace Giving Deductions ===>' || g_balance_value_tab(21).defined_balance_id);
524 hr_utility.trace('Direct Payments ===>' || g_balance_value_tab(2).defined_balance_id);
525 hr_utility.trace('Termination_Payments ===>' || g_balance_value_tab(3).defined_balance_id);
526 hr_utility.trace('Involuntary Deductions ===>' || g_balance_value_tab(4).defined_balance_id);
527 hr_utility.trace('Pre Tax Deductions ===>' || g_balance_value_tab(5).defined_balance_id);
528 hr_utility.trace('Termination Deductions ===>' || g_balance_value_tab(6).defined_balance_id);
529 hr_utility.trace('Voluntary Deductionsn ===>' || g_balance_value_tab(7).defined_balance_id);
530 hr_utility.trace('Total_Tax_Deduction ===>' || g_balance_value_tab(8).defined_balance_id);
531 hr_utility.trace('Earnings_Non_Taxable ===>' || g_balance_value_tab(9).defined_balance_id);
532 hr_utility.trace('Employer_Charges ===>' || g_balance_value_tab(10).defined_balance_id);
533 hr_utility.trace('Lump Sum A Payments ===>' || g_balance_value_tab(11).defined_balance_id);
534 hr_utility.trace('Lump Sum B Payments ===>' || g_balance_value_tab(12).defined_balance_id);
535 hr_utility.trace('Lump Sum C Payments ===>' || g_balance_value_tab(13).defined_balance_id);
536 hr_utility.trace('Lump Sum D Payments ===>' || g_balance_value_tab(14).defined_balance_id);
537 hr_utility.trace('Lump Sum E Payments ===>' || g_balance_value_tab(15).defined_balance_id);
538 hr_utility.trace('Invalidity Payments ===>' || g_balance_value_tab(16).defined_balance_id);
539 hr_utility.trace('CDEP ===>' || g_balance_value_tab(17).defined_balance_id);
540 hr_utility.trace('Leave Payments Marginal ===>' || g_balance_value_tab(18).defined_balance_id);
541 hr_utility.trace('Reportable Employer Super Contri ===>' || g_balance_value_tab(19).defined_balance_id); /* Bug 8587013 */
542 hr_utility.trace('Union Fees ===>' || g_balance_value_tab(20).defined_balance_id);
543 hr_utility.trace('Exempt Foreign Employment Income ===>' || g_balance_value_tab(22).defined_balance_id); /* Bug 8587013 */
544 hr_utility.trace('ETP Tax Free Payments Excluded ===>' || g_balance_value_tab(23).defined_balance_id); /*start bug 14703826 */
545 hr_utility.trace('ETP Tax Free Payments Non Excluded ===>' || g_balance_value_tab(24).defined_balance_id);
546 hr_utility.trace('ETP Taxable Payments Excluded ===>' || g_balance_value_tab(25).defined_balance_id);
547 hr_utility.trace('ETP Taxable Payments Non Excluded ===>' || g_balance_value_tab(26).defined_balance_id); /*end bug 14703826*/
548 hr_utility.trace('Retro Earnings Leave Loading GT 12 Mths Amount ===>' || g_balance_value_tab(31).defined_balance_id); -- start 8711855
549 hr_utility.trace('Retro Earnings Spread GT 12 Mths Amount ===>' || g_balance_value_tab(32).defined_balance_id);
550 hr_utility.trace('Retro Pre Tax GT 12 Mths Amount ===>' || g_balance_value_tab(33).defined_balance_id); -- end 8711855
551 hr_utility.trace('Lump Sum C Deductions ===>' || g_balance_value_tab(34).defined_balance_id);
552 hr_utility.trace('Foreign Leave Payments ===>' || g_balance_value_tab(35).defined_balance_id);
553 hr_utility.trace('Foreign Leave Payments Marginal ===>' || g_balance_value_tab(36).defined_balance_id);
554 hr_utility.trace('Foreign Lump Sum A Payments ===>' || g_balance_value_tab(37).defined_balance_id);
555 hr_utility.trace('Foreign Leave Component Deduction ===>' || g_balance_value_tab(38).defined_balance_id);
556 hr_utility.trace('Foreign Lump Sum A Deduction ===>' || g_balance_value_tab(39).defined_balance_id);
557 hr_utility.trace('Retro Earnings Additional GT 12 Mths Amount ===>' || g_balance_value_tab(40).defined_balance_id);
558 end if;
559
560
561 p_lump_sum_E_ptd_tab.delete;
562 for csr_rec in c_single_lumpsum_E_payment loop
563 p_lump_sum_E_ptd_tab(csr_rec.sort_index).defined_balance_id := csr_rec.defined_balance_id;
564 end loop;
565
566 if g_debug then
567 hr_utility.set_location('Defined Balance Ids for Lump Sume E PTD are:' , 15);
568 hr_utility.trace('--------------------------------------------');
569 hr_utility.trace('Lump Sum E Payments ===>' || p_lump_sum_E_ptd_tab(1).defined_balance_id);
570 hr_utility.trace('Retro Earnings Leave Loading GT 12 Mths Amount ===>' || p_lump_sum_E_ptd_tab(2).defined_balance_id);
571 hr_utility.trace('Retro Earnings Spread GT 12 Mths Amount ===>' || p_lump_sum_E_ptd_tab(3).defined_balance_id);
572 hr_utility.trace('Retro Pre Tax GT 12 Mths Amount ===>' || p_lump_sum_E_ptd_tab(4).defined_balance_id);
573 hr_utility.trace('Retro Earnings Additional GT 12 Mths Amount ===>' || p_lump_sum_E_ptd_tab(5).defined_balance_id);
574 end if;
575
576 /* Changes for 9147438 starts */
577
578 p_fw_fbt_bal_type_tab.delete;
579 open c_get_fw_fbt_bal_type_id;
580 fetch c_get_fw_fbt_bal_type_id into p_fw_fbt_bal_type_tab(1).balance_type_id;
581 close c_get_fw_fbt_bal_type_id;
582
583 p_fw_balance_type_tab.delete;
584 for csr_rec in c_get_fw_bal_type_id
585 loop
586 p_fw_balance_type_tab(csr_rec.sort_index).balance_type_id := csr_rec.balance_type_id;
587 end loop;
588
589 IF g_debug THEN
590 hr_utility.trace('Balance Type IDs for FW Balances ');
591 hr_utility.trace('-------------------------------------');
592 hr_utility.trace('FW Frienge Benefits ===>' || p_fw_fbt_bal_type_tab(1).balance_type_id);
593 hr_utility.trace('Earnings_Total ===>' || p_fw_balance_type_tab(1).balance_type_id);
594 hr_utility.trace('Leave Payments Marginal ===>' || p_fw_balance_type_tab(2).balance_type_id);
595 hr_utility.trace('Workplace Giving Deductions ===>' || p_fw_balance_type_tab(3).balance_type_id);
596 hr_utility.trace('Lump Sum E Payments ===>' || p_fw_balance_type_tab(4).balance_type_id);
597 hr_utility.trace('Retro Earnings Leave Loading GT 12 Mths Amount ===>' || p_fw_balance_type_tab(5).balance_type_id);
598 hr_utility.trace('Retro Earnings Spread GT 12 Mths Amount ===>' || p_fw_balance_type_tab(6).balance_type_id);
599 hr_utility.trace('Retro Pre Tax GT 12 Mths Amount ===>' || p_fw_balance_type_tab(7).balance_type_id);
600 hr_utility.trace('Total_Tax_Deductions ===>' || p_fw_balance_type_tab(8).balance_type_id);
601 hr_utility.trace('Termination Deductions ===>' || p_fw_balance_type_tab(9).balance_type_id);
602 hr_utility.trace('Lump Sum C Deductions ===>' || p_fw_balance_type_tab(10).balance_type_id);
603 hr_utility.trace('Foreign Tax Deductions ===>' || p_fw_balance_type_tab(11).balance_type_id);
604 hr_utility.trace('Lump Sum A Payments ===>' || p_fw_balance_type_tab(12).balance_type_id);
605 hr_utility.trace('Lump Sum D Payments ===>' || p_fw_balance_type_tab(13).balance_type_id);
606 hr_utility.trace('Reportable Employer Superannuation Contributions ===>' || p_fw_balance_type_tab(14).balance_type_id);
607 hr_utility.trace('Union Fees ===>' || p_fw_balance_type_tab(15).balance_type_id);
608 hr_utility.trace('CDEP ===>' || p_fw_balance_type_tab(16).balance_type_id);
609 hr_utility.trace('Retro LT 12 Mths Prev Yr Amount ===>' || p_fw_balance_type_tab(18).balance_type_id);
610 hr_utility.trace('Retro Earnings Leave Loading LT 12 Mths Prev Yr Amoun===>' || p_fw_balance_type_tab(19).balance_type_id);
611 hr_utility.trace('Retro Earnings Spread LT 12 Mths Prev Yr Amount ===>' || p_fw_balance_type_tab(20).balance_type_id);
612 hr_utility.trace('Retro Pre Tax LT 12 Mths Prev Yr Amount ===>' || p_fw_balance_type_tab(21).balance_type_id);
613 hr_utility.trace('Retro LT 12 Mths Curr Yr Amount ===>' || p_fw_balance_type_tab(22).balance_type_id);
614 hr_utility.trace('Retro Earnings Leave Loading LT 12 Mths Curr Yr Amoun===>' || p_fw_balance_type_tab(23).balance_type_id);
615 hr_utility.trace('Retro Earnings Spread LT 12 Mths Curr Amount ===>' || p_fw_balance_type_tab(24).balance_type_id);
616 hr_utility.trace('Retro Tax GT12 Amount ===>' || p_fw_balance_type_tab(25).balance_type_id);
617 hr_utility.trace('Retro Tax LT12 Prev Amount ===>' || p_fw_balance_type_tab(26).balance_type_id);
618 hr_utility.trace('Retro Tax LT12 Curr Amount ===>' || p_fw_balance_type_tab(27).balance_type_id);
619 hr_utility.trace('Foreign Leave Payments ===>' || p_fw_balance_type_tab(28).balance_type_id);
620 hr_utility.trace('Retro Earnings Additional GT 12 Mths Amount ===>' || p_fw_balance_type_tab(29).balance_type_id);
621 hr_utility.trace('Retro Earnings Additional LT12 Prev Mths Amount ===>' || p_fw_balance_type_tab(30).balance_type_id);
622 hr_utility.trace('Retro Earnings Additional LT12 Curr Mths Amount ===>' || p_fw_balance_type_tab(31).balance_type_id);
623 end if;
624
625 /* Changes for 9147438 end */
626
627 /* bug 7571001 - initialize g_attribute_id */
628 open get_balance_attribute('AU_EOY_ALLOWANCE');
629 fetch get_balance_attribute into g_attribute_id;
630 close get_balance_attribute;
631
632 if g_debug then
633 hr_utility.set_location('Leaving '||l_procedure,1000);
634 end if;
635
636 exception
637 when others then
638 hr_utility.set_location('Error in '||l_procedure,999999);
639 raise;
640 end initialization_code;
641
642 /*
643 Bug 7138494 - Added Function range_person_on
644 --------------------------------------------------------------------
645 Name : range_person_on
646 Type : Function
647 Access: Private
648 Description: Checks if RANGE_PERSON_ID is enabled for
649 Archive process.
650 --------------------------------------------------------------------
651 */
652
653 FUNCTION range_person_on
654 (p_report_mode IN VARCHAR2)
655 RETURN BOOLEAN
656 IS
657
658 CURSOR csr_action_parameter is
659 select parameter_value
660 from pay_action_parameters
661 where parameter_name = 'RANGE_PERSON_ID';
662
663 CURSOR csr_range_format_param(c_report_type VARCHAR2)
664 IS
665 SELECT par.parameter_value
666 FROM pay_report_format_parameters par,
667 pay_report_format_mappings_f map
668 WHERE map.report_format_mapping_id = par.report_format_mapping_id
669 AND map.report_type = c_report_type
670 AND map.report_format = c_report_type
671 AND map.report_qualifier = 'AU'
672 AND par.parameter_name = 'RANGE_PERSON_ID'; -- Bug fix 5567246
673
674 l_return boolean;
675 l_action_param_val varchar2(30);
676 l_report_param_val varchar2(30);
677 l_report_type VARCHAR2(50);
678
679 BEGIN
680
681 g_debug := hr_utility.debug_enabled;
682
683 IF g_debug
684 THEN
685 hr_utility.set_location('range_person_on',10);
686 END IF;
687
688 BEGIN
689
690 open csr_action_parameter;
691 fetch csr_action_parameter into l_action_param_val;
692 close csr_action_parameter;
693
694 IF g_debug
695 THEN
696 hr_utility.set_location('range_person_on',20);
697 END IF;
698
699 IF p_report_mode = 'S'
700 THEN
701 l_report_type := 'AU_REC_PS_SUMM_ARCHIVE';
702 ELSE
703 l_report_type := 'AU_REC_PS_DET_ARCHIVE';
704 END IF;
705
706
707 open csr_range_format_param(l_report_type);
708 fetch csr_range_format_param into l_report_param_val;
709 close csr_range_format_param;
710 IF g_debug
711 THEN
712 hr_utility.set_location('range_person_on',30);
713 END IF;
714 EXCEPTION WHEN NO_DATA_FOUND THEN
715 l_return := FALSE;
716 END;
717 --
718 IF g_debug
719 THEN
720 hr_utility.set_location('range_person_on',40);
721 END IF;
722
723 IF l_action_param_val = 'Y' AND l_report_param_val = 'Y' THEN
724 l_return := TRUE;
725
726 IF g_debug
727 THEN
728 hr_utility.trace('Range Person = True');
729 END IF;
730 ELSE
731 l_return := FALSE;
732 END IF;
733 --
734 RETURN l_return;
735 --
736 END range_person_on;
737
738 --------------------------------------------------------------------+
739 -- Name : assignment_Action_code
740 -- Type : Procedure
741 -- Access: Public
742 -- This procedure further restricts the assignment_id's
743 -- returned by range_code
744 -- This procedure gets the parameters given by user and restricts
745 -- the assignments to be archived.
746 -- it then calls hr_nonrun.insact to create an assignment action id
747 -- it then archives Payroll Run assignment action id details
748 --------------------------------------------------------------------+
749
750 procedure assignment_action_code
751 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type
752 ,p_start_person in per_all_people_f.person_id%type
753 ,p_end_person in per_all_people_f.person_id%type
754 ,p_chunk in number
755 ) is
756 cursor get_ps_params(c_payroll_action_id per_all_assignments_f.assignment_id%type)
757 is
758 select pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters) business_group_id
759 ,pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters) legal_employer
760 ,decode(pay_core_utils.get_parameter('PAYROLL',legislative_parameters),null,'%',pay_core_utils.get_parameter('PAYROLL',legislative_parameters)) payroll_id
761 ,decode(pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters),null,'%',pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters)) assignment_id
762 ,decode(pay_core_utils.get_parameter('EMPLOYEE_TYPE',legislative_parameters),'C','Y','T','N','B','%') employee_type
763 ,to_date('01-07-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fin_year_state_date
764 ,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),6,4),'DD-MM-YYYY') fin_year_end_date
765 ,to_date('01-04-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_start_date
766 ,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_end_date
767 ,pay_core_utils.get_parameter('LST_YR_TERM',legislative_parameters) lst_year_term
768 ,pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions /*Bug 4142159*/
769 ,decode(pay_core_utils.get_parameter('REP_MODE',legislative_parameters),'SUMM','S','D') report_mode /*Bug 3891577*/
770 from pay_payroll_actions
771 where payroll_action_id = c_payroll_action_id;
772
773 --------------------------------------------------------------------+
774 -- Cursor : csr_assignment_paysum
775 -- Description : Fetches assignments For Recconciling Payment Summary
776 -- Returns DISTINCT assignment_id
777 --
778 --------------------------------------------------------------------+
779 cursor csr_assignment_paysum
780 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
781 ,c_start_person per_all_people_f.person_id%type
782 ,c_end_person per_all_people_f.person_id%type
783 ,c_employee_type per_all_people_f.current_employee_flag%type
784 ,c_business_group_id hr_all_organization_units.organization_id%type
785 ,c_legal_employer pay_assignment_actions.tax_unit_id%type
786 ,c_payroll_id varchar2
787 ,c_fin_year_start date
788 ,c_fin_year_end date
789 ,c_lst_fbt_yr_start date
790 ,c_fbt_year_start date
791 ,c_fbt_year_end date
792 ,c_lst_year_start date
793 ,c_fbt_defined_balance_id pay_defined_balances.defined_balance_id%type
794 ,c_assignment_id varchar2
795 ) is
796 SELECT /*+ INDEX(pap per_people_f_pk)
797 INDEX(paa per_assignments_f_fk1)
798 INDEX(paa per_assignments_f_N12)
799 INDEX(rppa pay_payroll_actions_pk)
800 INDEX(pps per_periods_of_service_n3)
801 */ distinct paa.assignment_id
802 from per_people_f pap
803 ,per_assignments_f paa
804 ,pay_payroll_actions rppa
805 ,per_periods_of_service pps
806 where rppa.payroll_action_id = c_payroll_action_id
807 and pap.person_id between c_start_person and c_end_person
808 and pap.person_id = paa.person_id
809 and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_fin_year_end)),1,'Y','N')) LIKE c_employee_type
810 and pps.period_of_service_id = paa.period_of_service_id
811 and pap.person_id = pps.person_id
812 and rppa.business_group_id=paa.business_group_id
813 and nvl(pps.actual_termination_date, c_lst_year_start) >= c_lst_year_start
814 and c_fin_year_end between pap.effective_start_date and pap.effective_end_date
815 -- and least(nvl(pps.actual_termination_date,v_fin_year_end),v_fin_year_end) between a.effective_start_date and a.effective_end_date
816 and paa.effective_end_date = (select max(effective_end_date) /*4377367*/
817 From per_assignments_f iipaf
818 WHERE iipaf.assignment_id = paa.assignment_id
819 and iipaf.effective_end_date >= c_fbt_year_start
820 and iipaf.effective_start_date <= c_fin_year_end
821 AND iipaf.payroll_id IS NOT NULL) /*Bug 4688800*/
822 and paa.payroll_id like c_payroll_id
823 and paa.assignment_id like c_assignment_id
824 AND EXISTS (SELECT /*+ ORDERED */''
825 FROM per_assignments_f paaf
826 ,pay_assignment_actions rpac
827 ,pay_payroll_actions rppa
828 WHERE rppa.effective_date between c_fin_year_start AND c_fin_year_end /*Bug3048962 */
829 AND rppa.action_type in ('R','Q','B','I')
830 AND rpac.tax_unit_id = c_legal_employer
831 AND rppa.payroll_action_id = rpac.payroll_action_id
832 AND rpac.action_status = 'C'
833 AND rppa.payroll_id = paaf.payroll_id
834 AND rpac.assignment_id = paa.assignment_id
835 AND paaf.assignment_id = paa.assignment_id
836 AND rppa.effective_date between paaf.effective_start_date AND paaf.effective_end_date
837 UNION
838 SELECT /*+ ORDERED */''
839 FROM per_assignments_f paaf
840 ,pay_assignment_actions rpac
841 ,pay_payroll_actions rppa
842 WHERE pps.actual_termination_date between c_lst_fbt_yr_start AND c_fbt_year_end /*Bug3263659 */
843 AND rppa.effective_date between c_fbt_year_start AND c_fbt_year_end
844 AND pay_balance_pkg.get_value(c_fbt_defined_balance_id, rpac.assignment_action_id
845 + decode(rppa.payroll_id, 0, 0, 0),c_legal_employer,null,null,null,null) > to_number(g_fbt_threshold)
846 AND rppa.action_type in ('R','Q','B','I')
847 AND rpac.tax_unit_id = c_legal_employer
848 AND rppa.payroll_action_id = rpac.payroll_action_id
849 AND rpac.action_status = 'C'
850 AND rppa.payroll_id = paaf.payroll_id
851 AND rpac.assignment_id = paa.assignment_id
852 AND paaf.assignment_id = paa.assignment_id
853 AND rppa.effective_date between paaf.effective_start_date AND paaf.effective_end_date);
854
855 --------------------------------------------------------------------+
856 -- Cursor : csr_assignment_only_paysum
857 -- Description : Fetches assignments For Recconciling Payment Summary
858 -- Returns DISTINCT assignment_id for Distinct
859 -- Assignment.
860 --------------------------------------------------------------------+
861
862 cursor csr_assignment_only_paysum
863 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
864 ,c_start_person per_all_people_f.person_id%type
865 ,c_end_person per_all_people_f.person_id%type
866 ,c_employee_type per_all_people_f.current_employee_flag%type
867 ,c_business_group_id hr_all_organization_units.organization_id%type
868 ,c_legal_employer pay_assignment_actions.tax_unit_id%type
869 ,c_payroll_id varchar2
870 ,c_fin_year_start date
871 ,c_fin_year_end date
872 ,c_lst_fbt_yr_start date
873 ,c_fbt_year_start date
874 ,c_fbt_year_end date
875 ,c_lst_year_start date
876 ,c_fbt_defined_balance_id pay_defined_balances.defined_balance_id%type
877 ,c_assignment_id varchar2
878 ) is
879 SELECT /*+ INDEX(pap per_people_f_pk)
880 INDEX(paa per_assignments_f_fk1)
881 INDEX(paa per_assignments_f_N12)
882 INDEX(rppa pay_payroll_actions_pk)
883 INDEX(pps per_periods_of_service_n3)
884 */ distinct paa.assignment_id
885 from per_people_f pap
886 ,per_assignments_f paa
887 ,pay_payroll_actions rppa
888 ,per_periods_of_service pps
889 where rppa.payroll_action_id = c_payroll_action_id
890 and pap.person_id between c_start_person and c_end_person
891 and pap.person_id = paa.person_id
892 and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_fin_year_end)),1,'Y','N')) LIKE c_employee_type
893 and pps.period_of_service_id = paa.period_of_service_id
894 and pap.person_id = pps.person_id
895 and rppa.business_group_id=paa.business_group_id
896 and nvl(pps.actual_termination_date, c_lst_year_start) >= c_lst_year_start
897 and c_fin_year_end between pap.effective_start_date and pap.effective_end_date
898 -- and least(nvl(pps.actual_termination_date,v_fin_year_end),v_fin_year_end) between a.effective_start_date and a.effective_end_date
899 and paa.effective_end_date = (select max(effective_end_date) /*4377367*/
900 From per_assignments_f iipaf
901 WHERE iipaf.assignment_id = paa.assignment_id
902 and iipaf.effective_end_date >= c_fbt_year_start
903 and iipaf.effective_start_date <= c_fin_year_end
904 AND iipaf.payroll_id IS NOT NULL) /*Bug 4688800*/
905 and paa.payroll_id like c_payroll_id
906 and paa.assignment_id = c_assignment_id
907 AND EXISTS (SELECT /*+ ORDERED */''
908 FROM per_assignments_f paaf
909 ,pay_assignment_actions rpac
910 ,pay_payroll_actions rppa
911 WHERE rppa.effective_date between c_fin_year_start AND c_fin_year_end /*Bug3048962 */
912 AND rppa.action_type in ('R','Q','B','I')
913 AND rpac.tax_unit_id = c_legal_employer
914 AND rppa.payroll_action_id = rpac.payroll_action_id
915 AND rpac.action_status = 'C'
916 AND rppa.payroll_id = paaf.payroll_id
917 AND rpac.assignment_id = paa.assignment_id
918 AND paaf.assignment_id = paa.assignment_id
919 AND rppa.effective_date between paaf.effective_start_date AND paaf.effective_end_date
920 UNION
921 SELECT /*+ ORDERED */''
922 FROM per_assignments_f paaf
923 ,pay_assignment_actions rpac
924 ,pay_payroll_actions rppa
925 WHERE pps.actual_termination_date between c_lst_fbt_yr_start AND c_fbt_year_end /*Bug3263659 */
926 AND rppa.effective_date between c_fbt_year_start AND c_fbt_year_end
927 AND pay_balance_pkg.get_value(c_fbt_defined_balance_id, rpac.assignment_action_id
928 + decode(rppa.payroll_id, 0, 0, 0),c_legal_employer,null,null,null,null) > to_number(g_fbt_threshold)
929 AND rppa.action_type in ('R','Q','B','I')
930 AND rpac.tax_unit_id = c_legal_employer
931 AND rppa.payroll_action_id = rpac.payroll_action_id
932 AND rpac.action_status = 'C'
933 AND rppa.payroll_id = paaf.payroll_id
934 AND rpac.assignment_id = paa.assignment_id
935 AND paaf.assignment_id = paa.assignment_id
936 AND rppa.effective_date between paaf.effective_start_date AND paaf.effective_end_date);
937
938 /*
939 Bug 7138494 - Added Cursor for Range Person
940 - Uses person_id in pay_population_ranges
941 --------------------------------------------------------------------+
942 -- Cursor : csr_range_assignment_paysum
943 -- Description : Fetches assignments For Recconciling Payment Summary
944 -- Returns DISTINCT assignment_id
945 -- Used when RANGE_PERSON_ID feature is enabled
946 --------------------------------------------------------------------+
947 */
948
949 CURSOR csr_range_assignment_paysum
950 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
951 ,c_chunk NUMBER
952 ,c_employee_type per_all_people_f.current_employee_flag%type
953 ,c_business_group_id hr_all_organization_units.organization_id%type
954 ,c_legal_employer pay_assignment_actions.tax_unit_id%type
955 ,c_payroll_id varchar2
956 ,c_fin_year_start date
957 ,c_fin_year_end date
958 ,c_lst_fbt_yr_start date
959 ,c_fbt_year_start date
960 ,c_fbt_year_end date
961 ,c_lst_year_start date
962 ,c_fbt_defined_balance_id pay_defined_balances.defined_balance_id%type
963 ,c_assignment_id varchar2
964 )
965 IS
966 SELECT paa.assignment_id
967 from per_people_f pap
968 ,per_assignments_f paa
969 ,pay_payroll_actions rppa
970 ,per_periods_of_service pps
971 ,pay_population_ranges ppr
972 WHERE rppa.payroll_action_id = c_payroll_action_id
973 AND rppa.payroll_action_id = ppr.payroll_action_id
974 and ppr.payroll_action_id = c_payroll_action_id
975 AND ppr.chunk_number = c_chunk
976 AND pap.person_id = ppr.person_id
977 AND pap.person_id = paa.person_id
978 and paa.person_id = ppr.person_id
979 AND decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_fin_year_end)),1,'Y','N')) LIKE c_employee_type
980 AND pps.period_of_service_id = paa.period_of_service_id
981 AND pap.person_id = pps.person_id
982 AND rppa.business_group_id=paa.business_group_id
983 AND nvl(pps.actual_termination_date, c_lst_year_start) >= c_lst_year_start
984 AND c_fin_year_end between pap.effective_start_date AND pap.effective_end_date
985 AND paa.effective_end_date = (SELECT MAX(effective_end_date) /*4377367*/
986 FROM per_assignments_f iipaf
987 WHERE iipaf.assignment_id = paa.assignment_id
988 and iipaf.person_id = paa.person_id
989 AND iipaf.effective_end_date >= c_fbt_year_start
990 AND iipaf.effective_start_date <= c_fin_year_end
991 AND iipaf.payroll_id IS NOT NULL) /*Bug 4688800*/
992 AND paa.payroll_id like c_payroll_id
993 AND paa.assignment_id like c_assignment_id
994 AND EXISTS (SELECT /*+ ORDERED */''
995 FROM per_assignments_f paaf
996 ,pay_assignment_actions rpac
997 ,pay_payroll_actions rppa
998 WHERE rppa.effective_date between c_fin_year_start AND c_fin_year_end /*Bug3048962 */
999 AND rppa.action_type in ('R','Q','B','I')
1000 AND rpac.tax_unit_id = c_legal_employer
1001 AND rppa.payroll_action_id = rpac.payroll_action_id
1002 AND rpac.action_status = 'C'
1003 AND rppa.payroll_id = paaf.payroll_id
1004 AND rpac.assignment_id = paa.assignment_id
1005 AND paaf.assignment_id = paa.assignment_id
1006 AND rppa.effective_date between paaf.effective_start_date AND paaf.effective_end_date
1007 UNION
1008 SELECT /*+ ORDERED */''
1009 FROM per_assignments_f paaf
1010 ,pay_assignment_actions rpac
1011 ,pay_payroll_actions rppa
1012 WHERE pps.actual_termination_date between c_lst_fbt_yr_start AND c_fbt_year_end /*Bug3263659 */
1013 AND rppa.effective_date between c_fbt_year_start AND c_fbt_year_end
1014 AND pay_balance_pkg.get_value(c_fbt_defined_balance_id, rpac.assignment_action_id
1015 + decode(rppa.payroll_id, 0, 0, 0),c_legal_employer,null,null,null,null) > to_number(g_fbt_threshold)
1016 AND rppa.action_type in ('R','Q','B','I')
1017 AND rpac.tax_unit_id = c_legal_employer
1018 AND rppa.payroll_action_id = rpac.payroll_action_id
1019 AND rpac.action_status = 'C'
1020 AND rppa.payroll_id = paaf.payroll_id
1021 AND rpac.assignment_id = paa.assignment_id
1022 AND paaf.assignment_id = paa.assignment_id
1023 AND rppa.effective_date between paaf.effective_start_date AND paaf.effective_end_date);
1024
1025
1026 Cursor c_fbt_balance is
1027 select pdb.defined_balance_id
1028 from pay_balance_types pbt,
1029 pay_defined_balances pdb,
1030 pay_balance_dimensions pbd
1031 where pbt.balance_name ='Fringe Benefits'
1032 and pbt.balance_type_id = pdb.balance_type_id
1033 and pdb.balance_dimension_id = pbd.balance_dimension_id /* Bug 2501105 */
1034 and pbd.legislation_code ='AU'
1035 and pbd.dimension_name ='_ASG_LE_FBT_YTD' --2610141
1036 and pbd.legislation_code = pbt.legislation_code
1037 and pbd.legislation_code = pdb.legislation_code;
1038
1039 cursor csr_next_action_id is
1040 select pay_assignment_actions_s.nextval
1041 from dual;
1042
1043 /* Bug 5708255 */
1044 -------------------------------------------
1045 -- Added cursor to get value of global FBT_THRESHOLD
1046 --------------------------------------------
1047 CURSOR c_get_fbt_global(c_year_end DATE)
1048 IS
1049 SELECT global_value
1050 FROM ff_globals_f
1051 WHERE global_name = 'FBT_THRESHOLD'
1052 AND legislation_code = 'AU'
1053 AND c_year_end BETWEEN effective_start_date
1054 AND effective_end_date ;
1055
1056
1057 l_next_assignment_action_id pay_assignment_actions.assignment_action_id%type;
1058 l_procedure varchar2(200) ;
1059 i number;
1060
1061 l_lst_yr_start date;
1062 l_lst_fbt_yr_start date;
1063
1064 begin
1065
1066 g_debug :=hr_utility.debug_enabled;
1067 if g_debug
1068 then
1069 l_procedure := '.assignment_action_code';
1070 hr_utility.set_location('Entering '||l_procedure,1);
1071 end if;
1072
1073 g_arc_payroll_action_id := p_payroll_action_id;
1074
1075 /* Fetch Params */
1076 open get_ps_params(p_payroll_action_id);
1077 fetch get_ps_params into g_parameters;
1078 close get_ps_params;
1079
1080
1081 g_business_group_id := g_parameters.business_group_id;
1082 g_start_date := g_parameters.fin_year_start_date;
1083 g_end_date := g_parameters.fin_year_end_date;
1084 g_tax_unit_id := g_parameters.legal_employer;
1085
1086 /* Bug 5708255 */
1087 open c_get_fbt_global (add_months(g_end_date,-3));
1088 fetch c_get_fbt_global into g_fbt_threshold;
1089 close c_get_fbt_global;
1090
1091
1092 if (g_parameters.lst_year_term ='Y' or g_parameters.lst_year_term is NULL )
1093 then
1094 l_lst_yr_start := add_months(g_parameters.fin_year_start_date,-12);
1095 l_lst_fbt_yr_start := g_parameters.fbt_year_start_date;
1096 else
1097 l_lst_yr_start := TO_DATE('01-01-1900','DD-MM-YYYY');
1098 l_lst_fbt_yr_start := TO_DATE('01-01-1900','DD-MM-YYYY');
1099 end if;
1100
1101 if g_debug
1102 then
1103 hr_utility.set_location('p_payroll_action_id.........= ' || p_payroll_action_id,30);
1104 hr_utility.set_location('p_start_person..............= ' || p_start_person,30);
1105 hr_utility.set_location('p_end_person................= ' || p_end_person,30);
1106 hr_utility.set_location('g_parameters.business_group_id.........= ' || g_parameters.business_group_id,30);
1107 hr_utility.set_location('g_parameters.payroll_id..............= ' || g_parameters.payroll_id,30);
1108 hr_utility.set_location('g_parameters.legal_employer.........= ' || g_parameters.legal_employer,30);
1109 hr_utility.set_location('g_parameters.assignment_id.........= ' || g_parameters.assignment_id,30);
1110 hr_utility.set_location('g_parameters.fin_year_start_date..............= ' || g_parameters.fin_year_start_date,30);
1111 hr_utility.set_location('g_parameters.fin_year_end_date................= ' || g_parameters.fin_year_end_date,30);
1112 hr_utility.set_location('g_parameters.fbt_year_start_date..............= ' || g_parameters.fbt_year_start_date,30);
1113 hr_utility.set_location('g_parameters.fbt_year_end_date................= ' || g_parameters.fbt_year_end_date,30);
1114 hr_utility.set_location('g_parameters.employee_type..........= '||g_parameters.employee_type,30);
1115 hr_utility.set_location('g_parameters.delete_actions..........= '||g_parameters.delete_actions,30); /*Bug 4142159*/
1116 hr_utility.set_location('l_lst_yr_start.........................='||l_lst_yr_start,30);
1117 hr_utility.set_location('l_lst_fbt_yr_start.....................='||l_lst_fbt_yr_start,30);
1118 hr_utility.set_location('g_parameters.report_mode..........= '||g_parameters.report_mode,30); /*Bug 3891577*/
1119 end if;
1120
1121 /* SET FBT Defined_balance ID */
1122 If g_fbt_defined_balance_id is null OR g_fbt_defined_balance_id =0 Then
1123 Open c_fbt_balance;
1124 Fetch c_fbt_balance into g_fbt_defined_balance_id;
1125 Close c_fbt_balance;
1126 End if;
1127
1128 IF (g_parameters.payroll_id <> '%' and g_parameters.assignment_id <> '%')
1129 THEN
1130
1131 FOR csr_rec in csr_assignment_only_paysum(p_payroll_action_id,
1132 p_start_person,
1133 p_end_person,
1134 g_parameters.employee_type,
1135 g_parameters.business_group_id,
1136 g_parameters.legal_employer,
1137 g_parameters.payroll_id,
1138 g_parameters.fin_year_start_date,
1139 g_parameters.fin_year_end_date,
1140 l_lst_fbt_yr_start,
1141 g_parameters.fbt_year_start_date,
1142 g_parameters.fbt_year_end_date,
1143 l_lst_yr_start,
1144 g_fbt_defined_balance_id,
1145 g_parameters.assignment_id)
1146 LOOP /* LOOP FOR Payment Summary - Archives for each Assignment ID*/
1147
1148 open csr_next_action_id;
1149 fetch csr_next_action_id into l_next_assignment_action_id;
1150 close csr_next_action_id;
1151
1152 IF g_debug THEN
1153 hr_utility.set_location('Calling hr_nonrun_asact.insact for assignment id :'||csr_rec.assignment_id,2);
1154 END if;
1155
1156 hr_nonrun_asact.insact(l_next_assignment_action_id,
1157 csr_rec.assignment_id,
1158 p_payroll_action_id,
1159 p_chunk,
1160 null);
1161
1162 END LOOP;
1163 ELSE
1164
1165 /* Bug 7138494 - Added Changes for Range Person
1166 - Call Cursor using pay_population_ranges if Range Person Enabled
1167 Else call Old Cursor
1168 */
1169
1170 IF range_person_on(g_parameters.report_mode)
1171 THEN
1172
1173 FOR csr_rec in csr_range_assignment_paysum(p_payroll_action_id,
1174 p_chunk,
1175 g_parameters.employee_type,
1176 g_parameters.business_group_id,
1177 g_parameters.legal_employer,
1178 g_parameters.payroll_id,
1179 g_parameters.fin_year_start_date,
1180 g_parameters.fin_year_end_date,
1181 l_lst_fbt_yr_start,
1182 g_parameters.fbt_year_start_date,
1183 g_parameters.fbt_year_end_date,
1184 l_lst_yr_start,
1185 g_fbt_defined_balance_id,
1186 g_parameters.assignment_id)
1187 LOOP /* LOOP FOR Payment Summary - Archives for each Assignment ID*/
1188
1189 open csr_next_action_id;
1190 fetch csr_next_action_id into l_next_assignment_action_id;
1191 close csr_next_action_id;
1192
1193 IF g_debug THEN
1194 hr_utility.set_location('Calling hr_nonrun_asact.insact for assignment id :'||csr_rec.assignment_id,2);
1195 END if;
1196
1197 hr_nonrun_asact.insact(l_next_assignment_action_id,
1198 csr_rec.assignment_id,
1199 p_payroll_action_id,
1200 p_chunk,
1201 null);
1202
1203 END LOOP;
1204
1205 ELSE /* Old Code - No Range Person */
1206
1207
1208 FOR csr_rec in csr_assignment_paysum(p_payroll_action_id,
1209 p_start_person,
1210 p_end_person,
1211 g_parameters.employee_type,
1212 g_parameters.business_group_id,
1213 g_parameters.legal_employer,
1214 g_parameters.payroll_id,
1215 g_parameters.fin_year_start_date,
1216 g_parameters.fin_year_end_date,
1217 l_lst_fbt_yr_start,
1218 g_parameters.fbt_year_start_date,
1219 g_parameters.fbt_year_end_date,
1220 l_lst_yr_start,
1221 g_fbt_defined_balance_id,
1222 g_parameters.assignment_id)
1223 LOOP /* LOOP FOR Payment Summary - Arcbives for each Assignment ID*/
1224
1225 open csr_next_action_id;
1226 fetch csr_next_action_id into l_next_assignment_action_id;
1227 close csr_next_action_id;
1228
1229 IF g_debug THEN
1230 hr_utility.set_location('Calling hr_nonrun_asact.insact for assignment id :'||csr_rec.assignment_id,2);
1231 END if;
1232
1233 hr_nonrun_asact.insact(l_next_assignment_action_id,
1234 csr_rec.assignment_id,
1235 p_payroll_action_id,
1236 p_chunk,
1237 null);
1238
1239 END LOOP;
1240 END IF; /* End Range Person Check */
1241 END IF;
1242
1243 if g_debug then
1244 hr_utility.set_location('Leaving '||l_procedure,1000);
1245 end if;
1246 exception
1247 when others then
1248 hr_utility.set_location('Error in '||l_procedure,999999);
1249 raise;
1250 end assignment_action_code;
1251
1252 --------------------------------------------------------------------+
1253 -- Name : Archive_code
1254 -- Type : Procedure
1255 -- Access: Public
1256 -- This procedure archives employee details for Assignment in
1257 -- pay_action_information.
1258 -- Identifies if Assignment is an FBT or Normal Employee and
1259 -- fetches appropriate Balance values using BBR.
1260 --------------------------------------------------------------------+
1261
1262 procedure archive_code
1263 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
1264 ,p_effective_date in pay_payroll_actions.effective_date%type
1265 )
1266 is
1267
1268 --------------------------------------------------------------------+
1269 -- Cursor : c_employee_details
1270 -- Description : Fetches employee details to be displayed on Report.
1271 -- Latest Values as on End Date is fetched.
1272 --------------------------------------------------------------------+
1273 cursor c_employee_details
1274 ( c_business_group_id hr_all_organization_units.organization_id%TYPE,
1275 c_archive_assignment_action_id number,
1276 c_start_date date,
1277 c_end_date date)
1278 is
1279 select pap.full_name,
1280 paa.assignment_number,
1281 paa.assignment_id,
1282 to_number(pro.proposed_salary_n) actual_salary,
1283 paa.normal_hours,
1284 pps.actual_termination_date,
1285 pps.date_start,
1286 pgr.name grade,
1287 paa.organization_id,
1288 paa.payroll_id,
1289 hsc.segment1 tax_unit_id,
1290 hou.NAME organization_name,
1291 hou1.name legal_employer
1292 -- papf.payroll_name /*Bug 4688800*/
1293 from per_people_f pap,
1294 per_assignments_f paa,
1295 per_grades_tl pgr,
1296 per_periods_of_service pps,
1297 per_pay_bases ppb,
1298 per_pay_proposals pro,
1299 per_assignment_status_types past,
1300 hr_all_organization_units hou,
1301 pay_assignment_actions paa1
1302 ,hr_soft_coding_keyflex hsc
1303 ,hr_all_organization_units hou1
1304 -- ,pay_payrolls_f papf /*Bug 4688800*/
1305 where pap.person_id = paa.person_id
1306 and paa.assignment_id = paa1.assignment_id
1307 and paa1.assignment_action_id = c_archive_assignment_action_id
1308 and paa.business_group_id = c_business_group_id
1309 and paa.grade_id = pgr.grade_id(+)
1310 and pgr.language(+) = userenv('LANG')
1311 and paa.pay_basis_id = ppb.pay_basis_id(+)
1312 and paa.assignment_id = pro.assignment_id(+)
1313 AND hou.organization_id = paa.organization_id
1314 and hsc.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
1315 and hou1.organization_id = hsc.segment1
1316 -- and papf.payroll_id = paa.payroll_id /*Bug 4688800*/
1317 -- and c_end_date between papf.effective_start_date and papf.effective_end_date /*Bug 4688800*/
1318 and pps.period_of_service_id = paa.period_of_service_id
1319 and paa.assignment_status_type_id = past.assignment_status_type_id
1320 and paa.effective_end_date = ( select max(effective_end_date)
1321 from per_assignments_f
1322 WHERE assignment_id = paa.assignment_id
1323 and effective_end_date >= c_start_date
1324 and effective_start_date <= c_end_date)
1325 and c_end_date between pap.effective_start_date and pap.effective_end_date
1326 and pps.person_id = pap.person_id
1327 and pro.change_date(+) <= c_end_date
1328 and nvl(pro.approved,'Y') = 'Y'
1329 and nvl(pro.change_date,to_date('4712/12/31','YYYY/MM/DD')) = (select nvl(max(pro1.change_date),to_date('4712/12/31','YYYY/MM/DD'))
1330 from per_pay_proposals pro1
1331 where pro1.assignment_id(+) = paa.assignment_id
1332 and pro1.change_date(+) <= c_end_date
1333 and nvl(pro1.approved,'Y')='Y');
1334
1335
1336 /*Bug 4688800 - Introduced a new cursor to get the payroll name for the employee. This has been done to take care of cases
1337 where assignment has payroll attached to it for few months but is not attached at the end of year*/
1338
1339 CURSOR c_get_payroll_name(c_assignment_id number,
1340 c_start_date date,
1341 c_end_date date)
1342 IS
1343 SELECT paaf.payroll_id, pay.payroll_name
1344 FROM per_all_assignments_f paaf,
1345 pay_payrolls_f pay
1346 WHERE paaf.assignment_id = c_assignment_id
1347 and paaf.effective_end_date = (select max(effective_end_date)
1348 From per_assignments_f iipaf
1349 WHERE iipaf.assignment_id = c_assignment_id
1350 and iipaf.effective_end_date >= c_start_date
1351 and iipaf.effective_start_date <= c_end_date
1352 AND iipaf.payroll_id IS NOT NULL)
1353 AND pay.payroll_id = paaf.payroll_id
1354 AND paaf.effective_end_date BETWEEN pay.effective_start_date AND pay.effective_end_date;
1355
1356 /* Bug 3891577 - Employee details cursor */
1357 --------------------------------------------------------------------+
1358 -- Cursor : c_summary_employee_details
1359 -- Description : Fetches employee details to be displayed on
1360 -- Summary Report - Pay Sum Mode
1361 -- Latest Values as on End Date is fetched.
1362 --------------------------------------------------------------------+
1363 cursor c_summary_employee_details
1364 ( c_business_group_id hr_all_organization_units.organization_id%TYPE,
1365 c_archive_assignment_action_id number,
1366 c_start_date date,
1367 c_end_date date)
1368 is
1369 select pap.full_name,
1370 paf.assignment_number,
1371 paf.assignment_id,
1372 pps.date_start,
1373 pps.actual_termination_date
1374 from per_people_f pap,
1375 per_assignments_f paf,
1376 per_periods_of_service pps,
1377 pay_assignment_actions paa
1378 where pap.person_id = paf.person_id
1379 and paf.assignment_id = paa.assignment_id
1380 and pps.person_id = pap.person_id
1381 and pps.period_of_service_id = paf.period_of_service_id
1382 and paf.business_group_id = c_business_group_id
1383 and paa.assignment_action_id = c_archive_assignment_action_id
1384 and paf.effective_end_date = ( select max(effective_end_date)
1385 from per_assignments_f
1386 WHERE assignment_id = paf.assignment_id
1387 and effective_end_date >= c_start_date
1388 and effective_start_date <= c_end_date)
1389 and c_end_date between pap.effective_start_date and pap.effective_end_date;
1390
1391 --------------------------------------------------------------------+
1392 -- Cursor : get_max_action
1393 -- Description : Fetches Maximum Assignment Action Processed for
1394 -- Assignment between Start/End Dates
1395 --------------------------------------------------------------------+
1396
1397 cursor get_max_action(c_assignment_id number
1398 ,c_start_date date,c_end_date date
1399 ,c_tax_unit_id varchar2)
1400 is
1401 select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
1402 ,max(paa.action_sequence) action_sequence
1403 from pay_assignment_actions paa
1404 , pay_payroll_actions ppa
1405 , per_assignments_f paf
1406 where paa.assignment_id = paf.assignment_id
1407 and paf.assignment_id = c_assignment_id
1408 and paa.assignment_id = c_assignment_id
1409 and ppa.payroll_action_id = paa.payroll_action_id
1410 and ppa.effective_date between c_start_date and c_end_date
1411 and ppa.payroll_id = paf.payroll_id
1412 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
1413 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
1414 and paa.action_status='C'
1415 AND paa.tax_unit_id = c_tax_unit_id;
1416
1417 --------------------------------------------------------------------+
1418 -- Cursor : c_payment_summary_details
1419 -- Description : Fetches Manual PS Issued for Fin Year.
1420 -- Supports Legal Employer segment to have
1421 -- NULL,All,Legal Employer
1422 --------------------------------------------------------------------+
1423
1424 cursor c_payment_summary_details(c_assignment_id number,
1425 c_fin_date date,
1426 c_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE)
1427 is
1428 select hr.meaning fin_year
1429 from per_assignment_extra_info pae,
1430 hr_lookups hr
1431 where pae.aei_information_category = 'HR_PS_ISSUE_DATE_AU'
1432 and pae.information_type = 'HR_PS_ISSUE_DATE_AU'
1433 and pae.assignment_id = c_assignment_id
1434 and pae.aei_information1 = to_char(c_fin_date,'YY')
1435 and nvl(aei_information2,c_tax_unit_id) = decode(aei_information2,'-999',aei_information2,c_tax_unit_id)
1436 and pae.aei_information1 = hr.lookup_code
1437 and hr.lookup_type = 'AU_PS_FINANCIAL_YEAR';
1438
1439 cursor get_balance_name (c_defined_balance_id pay_defined_balances.defined_balance_id%type) is
1440 select NVL(pbt.reporting_name,pbt.balance_name) balance_name
1441 from pay_defined_balances pdb,
1442 pay_balance_types pbt
1443 where pdb.defined_balance_id = c_defined_balance_id
1444 and pdb.balance_type_id = pbt.balance_type_id
1445 and pdb.business_group_id = g_business_group_id;
1446
1447 l_procedure varchar2(200) ;
1448 l_employee_details c_employee_details%ROWTYPE;
1449 l_fin_year varchar2(80);
1450 l_action_sequence number;
1451 l_assignment_action_id number;
1452 l_dummy_action_sequence number;
1453 p_fw_union_tab tab_allownace_balance;
1454 e_prepost_error exception;
1455 l_pre01jul1983_days NUMBER;
1456 l_post30jun1983_days NUMBER;
1457 l_pre01jul1983_ratio NUMBER;
1458 l_post30jun1983_ratio NUMBER;
1459 l_pre01jul1983_value NUMBER;
1460 l_post30jun1983_value NUMBER;
1461 l_etp_service_date date;
1462 l_le_etp_service_date date; /* Bug 4177679 */
1463 l_result NUMBER;
1464 l_lst_fbt_yr_start date;
1465 l_payroll_id number; /*Bug 4688800*/
1466 l_payroll_name pay_payrolls_f.payroll_name%type; /*Bug 4688800*/
1467 l_etp_new_bal_total number ; /* Bug 9226023 - Variable declared to hold the sum of Taxable and Tax Free portions of
1468 ETP balances introduced as part of bug 8769345*/
1469 /* Changes for 9147438 start */
1470 l_fw_f_type varchar2(1);
1471 l_fw_j_type varchar2(1);
1472 counter number;
1473 l_fw_tot_net_balance number :=0;
1474 l_fw_tot_sub_balance number :=0;
1475 /* Changes for 9147438 end */
1476 l_retro_fw_gross number;
1477 l_retro_fw_tax number;
1478 n number;
1479
1480 /* Start bug 10331262 */
1481 l_lve_earnings NUMBER;
1482 l_lve_tax NUMBER;
1483 l_term_earnings NUMBER;
1484 l_term_tax NUMBER;
1485 l_lumpsuma NUMBER;
1486 /* End bug 10331262 */
1487
1488 begin
1489
1490 g_debug :=hr_utility.debug_enabled ;
1491
1492 if g_debug then
1493 l_procedure := g_package||'archive_code';
1494 hr_utility.set_location('Entering '||l_procedure,300);
1495 hr_utility.set_location('p_assignment_action_id......= '|| p_assignment_action_id,10);
1496 hr_utility.set_location('p_effective_date............= '|| to_char(p_effective_date,'DD-MON-YYYY'),10);
1497 end if;
1498
1499 if (g_parameters.lst_year_term ='Y' or g_parameters.lst_year_term is NULL )
1500 then
1501 l_lst_fbt_yr_start := g_parameters.fbt_year_start_date;
1502 else
1503 l_lst_fbt_yr_start := TO_DATE('01-01-1900','DD-MM-YYYY');
1504 end if;
1505
1506 /* Bug 3891577 - Checks added for Summary/Detail Report.
1507 Based on Report Mode flag,appropriate cursors will be called and employee details fetched
1508 Start Bug 3891577 */
1509
1510 if g_parameters.report_mode = 'D' /* Detail Report */
1511 then
1512 open c_employee_details(g_business_group_id,p_assignment_action_id,add_months(g_start_date,-3),g_end_date);
1513 fetch c_employee_details into l_employee_details;
1514 if c_employee_details%notfound
1515 then
1516 if g_debug then
1517 hr_utility.set_location('No Employee details found!!',3002);
1518 end if;
1519 end if;
1520 close c_employee_details;
1521
1522 else /* Summary Report */
1523
1524 open c_summary_employee_details(g_business_group_id,p_assignment_action_id,add_months(g_start_date,-3),g_end_date);
1525 fetch c_summary_employee_details into l_employee_details.full_name,
1526 l_employee_details.assignment_number,
1527 l_employee_details.assignment_id,
1528 l_employee_details.date_start,
1529 l_employee_details.actual_termination_date;
1530 close c_summary_employee_details;
1531
1532 end if;
1533 /* End Bug 3891577*/
1534
1535 /* Bug 4726352 - Manual PS Details fetched for both Summary and Detail Report
1536 Get Manual PS Issued and store in l_fin_year */
1537
1538 OPEN c_payment_summary_details(l_employee_details.assignment_id,g_start_date,g_tax_unit_id);
1539 FETCH c_payment_summary_details into l_fin_year;
1540 CLOSE c_payment_summary_details;
1541
1542
1543 /*Bug 4688800*/
1544 OPEN c_get_payroll_name(l_employee_details.assignment_id,g_start_date,g_end_date);
1545 FETCH c_get_payroll_name INTO l_payroll_id, l_payroll_name;
1546 CLOSE c_get_payroll_name;
1547
1548 insert into pay_action_information(
1549 action_information_id,
1550 action_context_id,
1551 action_context_type,
1552 effective_date,
1553 source_id,
1554 tax_unit_id,
1555 action_information_category,
1556 action_information1,
1557 action_information2,
1558 action_information3,
1559 action_information4,
1560 action_information5,
1561 action_information6,
1562 action_information7,
1563 action_information8,
1564 action_information9,
1565 action_information10,
1566 action_information11,
1567 action_information12,
1568 action_information13,
1569 assignment_id)
1570 values(
1571 pay_action_information_s.nextval,
1572 g_arc_payroll_action_id,
1573 'PA',
1574 p_effective_date,
1575 null,
1576 l_employee_details.tax_unit_id,
1577 'AU_EMPLOYEE_RECON_DETAILS',
1578 l_employee_details.full_name,
1579 l_employee_details.assignment_number,
1580 l_employee_details.actual_salary,
1581 l_employee_details.grade,
1582 l_employee_details.normal_hours,
1583 l_employee_details.actual_termination_date,
1584 l_fin_year,
1585 l_employee_details.organization_id,
1586 g_tax_unit_id,
1587 l_employee_details.payroll_id,
1588 l_employee_details.organization_name,
1589 l_employee_details.legal_employer,
1590 l_payroll_name, /*Bug 4688800*/
1591 l_employee_details.assignment_id);
1592
1593
1594 /* Reset all Global Values */
1595 /* Bug 4201894,9190980,9638323 - Initialize the g_adjusted_lump_sum_e_pay Value,
1596 g_adj_lump_sum_pre_tax,g_tax_free_etp, g_taxable_etp global variables */
1597 /* 9700346 - Moved the globals variables from IF condition, such that globals will
1598 be initialized to zero before archival of balances */
1599
1600 g_fbt_balance_value := 0;
1601 g_allowance_balance_value := 0;
1602 g_adjusted_lump_sum_e_pay := 0;
1603 g_adj_lump_sum_pre_tax := 0;
1604 g_tax_free_etp := 0;
1605 g_taxable_etp := 0;
1606 g_payg_fw_type :='P';
1607 /* Bug 13051557 */
1608 g_fw_gross_type := '';
1609 g_net_balance:=0;
1610
1611
1612 /* Changes for 9147438 start */
1613
1614 g_fw_lump_sumE :=0;
1615 g_fw_earnings :=0;
1616 g_fw_workplace :=0;
1617 g_fw_Total_Tax :=0;
1618 g_fw_Foreign_Tax :=0;
1619 g_fw_lump_sumA :=0;
1620 g_fw_lump_sumD :=0;
1621 g_fw_resc :=0;
1622 g_fw_union :=0;
1623 g_fw_cdep :=0;
1624 g_fw_fbt_term_check := 'NN';
1625 g_fw_fbt_check := 'NN';
1626
1627 /* Changes for 9147438 end */
1628 /* start bug 9950136*/
1629 g_fw_lt12_prev_earnings :=0;
1630 g_fw_lt12_curr_earnings :=0;
1631 g_retro_fw_gross :=0;
1632 g_retro_fw_tax :=0;
1633 /* end bug 9950136*/
1634
1635 if g_debug then
1636 hr_utility.set_location('Assignment_id' ||l_employee_details.assignment_id,300);
1637 end if;
1638
1639 open get_max_action(l_employee_details.assignment_id,g_start_date,g_end_date,g_tax_unit_id);
1640 fetch get_max_action into l_assignment_action_id,l_action_sequence;
1641
1642 if get_max_action%notfound then
1643 /* No Runs in the Financial Year - FBT Employee*/
1644 l_assignment_action_id := null;
1645 l_action_sequence := null;
1646 end if;
1647 close get_max_action;
1648
1649 if g_debug then
1650 hr_utility.set_location('Assignment_ACTION_id ' ||l_assignment_action_id,300);
1651 end if;
1652
1653 /* Changes for 9147438 start */
1654 if (to_number(to_char(g_start_date,'YYYY')) >= 2010) then
1655
1656 g_fw_check := pay_au_foreign_workers.check_foreign_worker(l_employee_details.assignment_id, g_tax_unit_id, g_start_date, g_end_date);
1657
1658 if g_debug then
1659 hr_utility.set_location('FW EMP check .. g_fw_check for assignment_id '||l_employee_details.assignment_id||' is '||g_fw_check, 2);
1660 end if;
1661
1662 end if;
1663 /* Changes for 9147438 end */
1664
1665 if ( l_employee_details.actual_termination_date is NOT NULL
1666 and l_employee_details.actual_termination_date
1667 between l_lst_fbt_yr_start and g_parameters.fbt_year_end_date
1668 and l_assignment_action_id IS NULL)
1669 then
1670 /* Employee is FBT employee */
1671 /* Archive only FBT Balance - Set all other Balance Values to 0 */
1672
1673 /* Changes for 9147438 start */
1674 if (to_number(to_char(g_start_date,'YYYY')) >= 2010) then
1675
1676 l_fw_f_type := pay_au_foreign_workers.check_foreign_worker(l_employee_details.assignment_id, g_tax_unit_id, add_months(g_start_date,-3), (g_start_date - 1),
1677 pay_au_foreign_workers.g_fw_f_type);
1678 l_fw_j_type := pay_au_foreign_workers.check_foreign_worker(l_employee_details.assignment_id, g_tax_unit_id, add_months(g_start_date,-3), (g_start_date - 1),
1679 pay_au_foreign_workers.g_fw_j_type);
1680
1681 if l_fw_f_type = 'Y' then
1682 if l_fw_j_type = 'Y' then
1683 g_fw_fbt_term_check := 'YY';
1684 g_fw_gross_type := 'FJ';
1685 else
1686 g_fw_fbt_term_check := 'YN';
1687 g_fw_gross_type := 'F';
1688 end if;
1689 elsif l_fw_j_type = 'Y' then
1690 g_fw_fbt_term_check := 'NY';
1691 g_fw_gross_type := 'J';
1692 else
1693 g_fw_fbt_term_check := 'NN';
1694 g_fw_gross_type := 'P';
1695 end if;
1696
1697 end if;
1698
1699 if g_debug then
1700 hr_utility.set_location('Value of global variable g_fw_fbt_term_check'||' '||g_fw_fbt_term_check,150);
1701 hr_utility.set_location('Value of global variable g_fw_gross_type '||' '||g_fw_gross_type,170);
1702 end if;
1703
1704 /* Changes for 9147438 end */
1705
1706 if g_debug
1707 then
1708 hr_utility.set_location('FBT Employee Assignment ID======.....'||l_employee_details.assignment_id,300);
1709 end if;
1710
1711 get_fbt_balance(p_assignment_id => l_employee_details.assignment_id
1712 ,p_start_date => add_months(g_start_date,-3)
1713 ,p_end_date => g_start_date-1
1714 ,p_action_sequence => l_action_sequence
1715 );
1716
1717 if g_debug
1718 then
1719 hr_utility.set_location('FBT Balance Value ======.....'||g_fbt_balance_value,300);
1720 end if;
1721
1722 g_result_table.delete;
1723 FOR i in 1..22
1724 LOOP
1725 g_result_table(i).balance_value := 0;
1726 END LOOP;
1727
1728 l_pre01jul1983_ratio := 0;
1729 l_post30jun1983_ratio := 0;
1730 else
1731 /* Not an FBT Employee - Set all Balances */
1732
1733 /* Changes for 9147438 start */
1734
1735 if (to_number(to_char(g_start_date,'YYYY')) >= 2010) then
1736
1737 l_fw_f_type := pay_au_foreign_workers.check_foreign_worker(l_employee_details.assignment_id, g_tax_unit_id, add_months(g_start_date,-3), (g_end_date - 3),
1738 pay_au_foreign_workers.g_fw_f_type);
1739 l_fw_j_type := pay_au_foreign_workers.check_foreign_worker(l_employee_details.assignment_id, g_tax_unit_id, add_months(g_start_date,-3), (g_end_date - 3),
1740 pay_au_foreign_workers.g_fw_j_type);
1741
1742 if l_fw_f_type = 'Y' then
1743 if l_fw_j_type = 'Y' then
1744 g_fw_fbt_check := 'YY';
1745 else
1746 g_fw_fbt_check := 'YN';
1747 end if;
1748 elsif l_fw_j_type = 'Y' then
1749 g_fw_fbt_check := 'NY';
1750 else
1751 g_fw_fbt_check := 'NN';
1752 end if;
1753
1754 end if;
1755
1756 if g_debug then
1757 hr_utility.set_location('Value of global variable g_fw_fbt_check'||' '||g_fw_fbt_check,250);
1758 end if;
1759
1760 /* Changes for 9147438 end */
1761
1762 get_fbt_balance(p_assignment_id => l_employee_details.assignment_id
1763 ,p_start_date => add_months(g_start_date,-3)
1764 ,p_end_date => add_months(g_end_date,-3)
1765 ,p_action_sequence => l_dummy_action_sequence
1766 );
1767
1768 g_result_table.delete;
1769 g_context_table.delete;
1770
1771 g_context_table(1).tax_unit_id := g_tax_unit_id;
1772
1773 if (l_assignment_action_id is NOT NULL)
1774 then
1775 pay_balance_pkg.get_value(p_assignment_action_id => l_assignment_action_id,
1776 p_defined_balance_lst=>g_balance_value_tab,
1777 p_context_lst =>g_context_table,
1778 p_output_table=>g_result_table);
1779
1780 else
1781 if g_debug then
1782 hr_utility.set_location('Assingment Action ID not found for Run in Year!!',300);
1783 end if;
1784 end if;
1785
1786 if g_debug
1787 then
1788 hr_utility.trace('Balance values for YTD dimension');
1789 hr_utility.trace('-------------------------------------');
1790 hr_utility.set_location('FBT Balance Value ======.....'||g_fbt_balance_value,100);
1791 hr_utility.trace('Earnings_Total ===>'||g_result_table(1).balance_value);
1792 hr_utility.trace('Workplace Giving Deductions ===>' || g_result_table(21).balance_value);
1793 hr_utility.trace('Direct Payments ===>'||g_result_table(2).balance_value);
1794 hr_utility.trace('Termination_Payments ===>'||g_result_table(3).balance_value);
1795 hr_utility.trace('Involuntary Deductions ===>'||g_result_table(4).balance_value);
1796 hr_utility.trace('Pre Tax Deductions ===>'||g_result_table(5).balance_value);
1797 hr_utility.trace('Termination Deductions ===>'||g_result_table(6).balance_value);
1798 hr_utility.trace('Voluntary Deductions ===>'||g_result_table(7).balance_value);
1799 hr_utility.trace('Total_Tax_Deduction ===>'||g_result_table(8).balance_value);
1800 hr_utility.trace('Earnings_Non_Taxable ===>'||g_result_table(9).balance_value);
1801 hr_utility.trace('Employer_Charges ===>'||g_result_table(10).balance_value);
1802 hr_utility.trace('Lump Sum A Payments ===>'||g_result_table(11).balance_value);
1803 hr_utility.trace('Lump Sum B Payments ===>'||g_result_table(12).balance_value);
1804 hr_utility.trace('Lump Sum C Payments ===>'||g_result_table(13).balance_value);
1805 hr_utility.trace('Lump Sum D Payments ===>'||g_result_table(14).balance_value);
1806 hr_utility.trace('Lump Sum E Payments ===>'||g_result_table(15).balance_value);
1807 hr_utility.trace('Invalidity Payments ===>'||g_result_table(16).balance_value);
1808 hr_utility.trace('CDEP ===>'||g_result_table(17).balance_value);
1809 hr_utility.trace('Leave Payments Marginal ===>'||g_result_table(18).balance_value);
1810 hr_utility.trace('Reportable Employer Superannuation Contributions ===>'||g_result_table(19).balance_value); /* Bug 8587013 */
1811 hr_utility.trace('Union Fees ===>'||g_result_table(20).balance_value);
1812 hr_utility.trace('Exempt Foreign Employment Income ===>'||g_result_table(22).balance_value); /* Bug 8587013 */
1813 hr_utility.trace('ETP Tax Free Payments Excluded ===>' || g_result_table(23).balance_value); /*start bug 14703826*/
1814 hr_utility.trace('ETP Tax Free Payments Non Excluded ===>' || g_result_table(24).balance_value);
1815 hr_utility.trace('ETP Taxable Payments Excluded ===>' || g_result_table(25).balance_value);
1816 hr_utility.trace('ETP Taxable Payments NOn Excluded ===>' || g_result_table(26).balance_value); /*end bug 14703826*/
1817 hr_utility.trace('Retro Earnings Leave Loading GT 12 Mths Amount ===>' || g_result_table(31).balance_value); -- start 8711855
1818 hr_utility.trace('Retro Earnings Spread GT 12 Mths Amount ===>' || g_result_table(32).balance_value);
1819 hr_utility.trace('Retro Pre Tax GT 12 Mths Amount ===>' || g_result_table(33).balance_value); -- end 8711855
1820 hr_utility.trace('Lump Sum C Deductions ===>' || g_result_table(34).balance_value);
1821 hr_utility.trace('Foreign Leave Payments ===>' || g_result_table(35).balance_value);
1822 hr_utility.trace('Foreign Leave Payments Marginal ===>' || g_result_table(36).balance_value);
1823 hr_utility.trace('Foreign Lump Sum A Payments ===>' || g_result_table(37).balance_value);
1824 hr_utility.trace('Foreign Leave Component Deduction ===>' || g_result_table(38).balance_value);
1825 hr_utility.trace('Foreign Lump Sum A Deduction ===>' || g_result_table(39).balance_value);
1826 hr_utility.trace('Retro Earnings Additional GT 12 Mths Amount ===>' || g_result_table(40).balance_value);
1827 end if;
1828
1829 g_net_balance := g_net_balance + (g_result_table(1).balance_value + g_result_table(21).balance_value + g_result_table(18).balance_value )+
1830 (g_result_table(8).balance_value + g_result_table(6).balance_value - g_result_table(34).balance_value) +
1831 g_result_table(11).balance_value +
1832 g_result_table(12).balance_value +
1833 g_result_table(14).balance_value +
1834 g_result_table(22).balance_value +
1835 g_result_table(19).balance_value ;
1836
1837 /* Changes for 9147438 Starts */
1838
1839 if g_fw_check = 'Y' then
1840
1841 f_fw_date_tab_g.delete;
1842 j_fw_date_tab_g.delete;
1843
1844 l_fw_f_type := pay_au_foreign_workers.check_foreign_worker(l_employee_details.assignment_id, g_tax_unit_id, g_start_date, g_end_date, pay_au_foreign_workers.g_fw_f_type);
1845 l_fw_j_type := pay_au_foreign_workers.check_foreign_worker(l_employee_details.assignment_id, g_tax_unit_id, g_start_date, g_end_date, pay_au_foreign_workers.g_fw_j_type);
1846
1847 if l_fw_f_type = 'Y' then
1848 pay_au_foreign_workers.get_foreign_worker_dates(l_employee_details.assignment_id, g_tax_unit_id, g_start_date, g_end_date, pay_au_foreign_workers.g_fw_f_type, f_fw_date_tab_g);
1849 end if;
1850
1851 if l_fw_j_type = 'Y' then
1852 pay_au_foreign_workers.get_foreign_worker_dates(l_employee_details.assignment_id, g_tax_unit_id, g_start_date, g_end_date, pay_au_foreign_workers.g_fw_j_type, j_fw_date_tab_g);
1853 end if;
1854
1855 if f_fw_date_tab_g.count > 0 then
1856 t_fw_gross_type(1) := 'F';
1857 g_fw_gross_type := t_fw_gross_type(1);
1858 if j_fw_date_tab_g.count >0 then
1859 t_fw_gross_type(2) := 'J';
1860 g_fw_gross_type := g_fw_gross_type||t_fw_gross_type(2);
1861 end if;
1862 elsif j_fw_date_tab_g.count > 0 then
1863 t_fw_gross_type(1) := 'J';
1864 g_fw_gross_type := t_fw_gross_type(1);
1865 end if;
1866
1867 if g_debug then
1868 hr_utility.set_location('t_fw_gross_type.count '||t_fw_gross_type.count,2);
1869 hr_utility.set_location('g_fw_gross_type '||g_fw_gross_type,3);
1870 end if;
1871
1872 For i_idx in t_fw_gross_type.first .. t_fw_gross_type.last loop
1873
1874 if g_debug then
1875 hr_utility.set_location('FW Gross Type : ' ||t_fw_gross_type(i_idx),10);
1876 end if;
1877
1878 g_fw_result_table.delete;
1879 IF t_fw_gross_type(i_idx) = 'F' THEN
1880 pay_au_foreign_workers.get_foreign_payment_amounts(l_employee_details.assignment_id,
1881 g_tax_unit_id,
1882 f_fw_date_tab_g,
1883 g_start_date,
1884 g_end_date,
1885 pay_au_foreign_workers.g_fw_f_type,
1886 p_fw_balance_type_tab,
1887 g_fw_result_table) ;
1888 ELSIF t_fw_gross_type(i_idx) = 'J' THEN
1889 pay_au_foreign_workers.get_foreign_payment_amounts(l_employee_details.assignment_id,
1890 g_tax_unit_id,
1891 j_fw_date_tab_g,
1892 g_start_date,
1893 g_end_date,
1894 pay_au_foreign_workers.g_fw_j_type,
1895 p_fw_balance_type_tab,
1896 g_fw_result_table) ;
1897 END IF;
1898
1899 IF g_debug THEN
1900 hr_utility.trace('FW Balance Values are ');
1901 hr_utility.trace('-------------------------------------');
1902 hr_utility.trace('Earnings_Total ===>' || g_fw_result_table(1).balance_value);
1903 hr_utility.trace('Leave Payments Marginal ===>' || g_fw_result_table(2).balance_value);
1904 hr_utility.trace('Workplace Giving Deductions ===>' || g_fw_result_table(3).balance_value);
1905 hr_utility.trace('Lump Sum E Payments ===>' || g_fw_result_table(4).balance_value);
1906 hr_utility.trace('Retro Earnings Leave Loading GT 12 Mths Amoun===>' || g_fw_result_table(5).balance_value);
1907 hr_utility.trace('Retro Earnings Spread GT 12 Mths Amount ===>' || g_fw_result_table(6).balance_value);
1908 hr_utility.trace('Retro Pre Tax GT 12 Mths Amount ===>' || g_fw_result_table(7).balance_value);
1909 hr_utility.trace('Total_Tax_Deductions ===>' || g_fw_result_table(8).balance_value);
1910 hr_utility.trace('Termination Deductions ===>' || g_fw_result_table(9).balance_value);
1911 hr_utility.trace('Lump Sum C Deductions ===>' || g_fw_result_table(10).balance_value);
1912 hr_utility.trace('Foreign Tax Deductions ===>' || g_fw_result_table(11).balance_value);
1913 hr_utility.trace('Lump Sum A Payments ===>' || g_fw_result_table(12).balance_value);
1914 hr_utility.trace('Lump Sum D Payments ===>' || g_fw_result_table(13).balance_value);
1915 hr_utility.trace('Reportable Employer Superannuation Contrib ===>' || g_fw_result_table(14).balance_value);
1916 hr_utility.trace('Union Fees ===>' || g_fw_result_table(15).balance_value);
1917 hr_utility.trace('CDEP ===>' || g_fw_result_table(16).balance_value);
1918 hr_utility.trace('Exempt Foreign Employment Income ===>' || g_fw_result_table(17).balance_value);
1919 hr_utility.trace('Retro LT 12 Mths Prev Yr Amount ===>' || g_fw_result_table(18).balance_value);
1920 hr_utility.trace('Retro Earnings Leave Loading LT 12 Mths Prev Yr Amount ===>' || g_fw_result_table(19).balance_value);
1921 hr_utility.trace('Retro Earnings Spread LT 12 Mths Prev Yr Amount ===>' || g_fw_result_table(20).balance_value);
1922 hr_utility.trace('Retro Pre Tax LT 12 Mths Prev Yr Amount ===>' || g_fw_result_table(21).balance_value);
1923 hr_utility.trace('Retro LT 12 Mths Curr Yr Amount ===>' || g_fw_result_table(22).balance_value);
1924 hr_utility.trace('Retro Earnings Leave Loading LT 12 Mths Curr Yr Amount ===>' || g_fw_result_table(23).balance_value);
1925 hr_utility.trace('Retro Earnings Spread LT 12 Mths Curr Amount ===>' || g_fw_result_table(24).balance_value);
1926 hr_utility.trace('Retro Tax GT12 Amount ===>' || g_fw_result_table(25).balance_value);
1927 hr_utility.trace('Retro Tax LT12 Prev Amount ===>' || g_fw_result_table(26).balance_value);
1928 hr_utility.trace('Retro Tax LT12 Curr Amount ===>' || g_fw_result_table(27).balance_value);
1929 hr_utility.trace('Foreign Leave Payments ===>' || g_fw_result_table(28).balance_value);
1930 hr_utility.trace('Retro Earnings Additional GT 12 Mths Amount ===>' || g_fw_result_table(29).balance_value);
1931 hr_utility.trace('Retro Earnings Additional LT12 Prev Mths Amount ===>' || g_fw_result_table(30).balance_value);
1932 hr_utility.trace('Retro Earnings Additional LT12 Curr Mths Amount ===>' || g_fw_result_table(31).balance_value);
1933 end if;
1934
1935
1936 /* Bug 10331262 - Reporting of Termination Payments and Foreign Leave Payments
1937 Leave Payments:
1938 l_lve_earnings = Foreign Leave Payment (for non FW-periods)
1939 l_lve_tax = Tax on Foreign Leave Payment (for non FW-periods)
1940 l_term_earnings = Foreign Leave Payments Marginal
1941 l_lump_sum_a = Foreign Lump Sum A Payments
1942 l_term_tax = Foreign Leave Components Deduction + Foreign Lump Sum A Deduction
1943 */
1944 IF (i_idx = 1)
1945 THEN
1946
1947 hr_utility.set_location('Anitha I enter here',1000);
1948 l_term_earnings := g_result_table(36).balance_value;
1949 l_lumpsuma := g_result_table(37).balance_value;
1950 l_term_tax := g_result_table(38).balance_value + g_result_table(39).balance_value;
1951
1952 IF (g_result_table(35).balance_value - g_fw_result_table(28).balance_value) <> 0
1953 THEN
1954
1955 pay_au_payment_summary.get_foreign_leave_payments
1956 (l_employee_details.assignment_id
1957 ,g_tax_unit_id
1958 ,g_start_date
1959 ,g_end_date
1960 ,l_lve_earnings
1961 ,l_lve_tax);
1962 ELSE
1963 l_lve_earnings := 0;
1964 l_lve_tax := 0;
1965 END IF;
1966
1967 ELSE
1968 l_lve_earnings := 0;
1969 l_lve_tax := 0;
1970 l_term_earnings := 0;
1971 l_term_tax := 0;
1972 l_lumpsuma := 0;
1973
1974 END IF;
1975 /* End Bug 10331262 */
1976
1977 hr_utility.set_location('2: Anitha I enter here',1000);
1978
1979 /* Bug 10331262 - Modified termination payments added to earnings */
1980 g_fw_earnings := g_fw_earnings
1981 + g_fw_result_table(1).balance_value
1982 + l_lve_earnings + l_term_earnings
1983 + g_fw_result_table(3).balance_value
1984 - g_fw_result_table(17).balance_value; --bug#10143762
1985
1986 g_fw_lump_sumE := g_fw_lump_sumE +
1987 + g_fw_result_table(4).balance_value + g_fw_result_table(5).balance_value
1988 + g_fw_result_table(6).balance_value - g_fw_result_table(7).balance_value
1989 + g_fw_result_table(29).balance_value ; -- bug 13362286
1990
1991 /* start bug 9950136 */
1992 l_retro_fw_gross := 0;
1993 l_retro_fw_tax := 0;
1994 IF t_fw_gross_type(i_idx) = 'F' THEN
1995 n := pay_au_payment_summary.adjust_retro_fw(l_employee_details.assignment_id
1996 ,g_tax_unit_id
1997 ,g_start_date
1998 ,g_end_date
1999 ,pay_au_foreign_workers.g_fw_f_type
2000 ,l_retro_fw_gross
2001 ,l_retro_fw_tax);
2002 ELSIF t_fw_gross_type(i_idx) = 'J' THEN
2003 n := pay_au_payment_summary.adjust_retro_fw(l_employee_details.assignment_id
2004 ,g_tax_unit_id
2005 ,g_start_date
2006 ,g_end_date
2007 ,pay_au_foreign_workers.g_fw_j_type
2008 ,l_retro_fw_gross
2009 ,l_retro_fw_tax);
2010 END IF;
2011
2012 g_fw_lt12_prev_earnings := g_fw_lt12_prev_earnings +
2013 + g_fw_result_table(18).balance_value + g_fw_result_table(19).balance_value
2014 + g_fw_result_table(20).balance_value - g_fw_result_table(21).balance_value
2015 + g_fw_result_table(30).balance_value ; -- bug 13362286
2016
2017 g_fw_lt12_curr_earnings := g_fw_lt12_curr_earnings +
2018 + g_fw_result_table(22).balance_value + g_fw_result_table(23).balance_value
2019 + g_fw_result_table(24).balance_value + g_fw_result_table(31).balance_value; -- bug 13362286
2020
2021 g_retro_fw_gross := g_retro_fw_gross + l_retro_fw_gross ;
2022
2023 /* Bug 10331262 - Replaced Termination Deductions, Lump Sum C with
2024 Foreign Termination Deductions */
2025 g_fw_Total_Tax := g_fw_Total_Tax
2026 + g_fw_result_table(8).balance_value
2027 + l_term_tax + l_lve_tax
2028 - g_fw_result_table(25).balance_value
2029 - g_fw_result_table(26).balance_value
2030 - g_fw_result_table(27).balance_value;
2031
2032 g_retro_fw_tax := g_retro_fw_tax + l_retro_fw_tax ;
2033 /* end bug 9950136 */
2034
2035 g_fw_workplace := g_fw_workplace + g_fw_result_table(3).balance_value;
2036
2037
2038 /* Bug 10331262 - Replace Lump Sum A with Foreign Lump Sum A Pay Values */
2039 g_fw_Foreign_Tax := g_fw_Foreign_Tax + g_fw_result_table(11).balance_value;
2040 g_fw_lump_sumA := g_fw_lump_sumA + l_lumpsuma;
2041 g_fw_lump_sumD := g_fw_lump_sumD + g_fw_result_table(13).balance_value;
2042 g_fw_resc := g_fw_resc + g_fw_result_table(14).balance_value;
2043 g_fw_union := g_fw_union + g_fw_result_table(15).balance_value;
2044 g_fw_cdep := g_fw_cdep + g_fw_result_table(16).balance_value;
2045
2046 end loop;
2047
2048 /* start bug 9950136 */
2049 g_fw_earnings := g_fw_earnings - g_fw_lump_sumE - g_fw_lt12_prev_earnings - g_fw_lt12_curr_earnings + g_retro_fw_gross ;
2050 g_fw_lump_sumE := 0;
2051 g_fw_Total_Tax := g_fw_Total_Tax + g_retro_fw_tax;
2052 /* end bug 9950136 */
2053
2054 l_fw_tot_net_balance := g_fw_earnings + g_fw_lump_sumE + g_fw_Total_Tax + g_fw_lump_sumA + g_fw_lump_sumD + g_fw_resc + g_fw_fbt_balance ;
2055 l_fw_tot_sub_balance := g_fw_lump_sumD + g_fw_resc + g_fw_fbt_balance;
2056
2057 end if;
2058
2059 if g_debug then
2060 hr_utility.set_location(' g_fw_earnings '||g_fw_earnings,41);
2061 hr_utility.set_location(' g_fw_lump_sumE '||g_fw_lump_sumE,41);
2062 hr_utility.set_location(' g_fw_Total_Tax '||g_fw_Total_Tax,41);
2063 hr_utility.set_location(' g_fw_lump_sumA '||g_fw_lump_sumA,41);
2064 hr_utility.set_location(' g_fw_lump_sumD '||g_fw_lump_sumD,41);
2065 hr_utility.set_location(' g_fw_resc '||g_fw_resc,41);
2066 hr_utility.set_location(' g_fw_fbt_balance '||g_fw_fbt_balance,41);
2067 hr_utility.set_location(' g_fw_cdep '||g_fw_cdep,41);
2068 hr_utility.set_location(' g_net_balance '||g_net_balance,41);
2069 hr_utility.set_location(' l_fw_tot_net_balance '||l_fw_tot_net_balance,41);
2070 hr_utility.set_location(' l_fw_tot_sub_balance '||l_fw_tot_sub_balance,41);
2071 end if;
2072
2073 if l_fw_tot_net_balance <> 0 then
2074 if (g_net_balance - l_fw_tot_net_balance) <> 0 then
2075 if l_fw_tot_net_balance - l_fw_tot_sub_balance = 0 then
2076 g_payg_fw_type := 'P';
2077 else
2078 g_payg_fw_type := 'PF';
2079 end if;
2080 else
2081 g_payg_fw_type := 'F';
2082 end if;
2083 elsif g_fw_fbt_balance <> 0 then
2084 if g_net_balance - g_fw_fbt_balance <> 0 then
2085 g_payg_fw_type := 'P';
2086 else
2087 g_payg_fw_type := 'F';
2088 end if;
2089 end if;
2090
2091 if g_debug then
2092 hr_utility.set_location(' g_payg_fw_type '||g_payg_fw_type,41);
2093 end if;
2094 /* Changes for 9147438 ends*/
2095
2096 /* Call procedure to Adjust Lump SUM E Payments Value
2097 */
2098 Adjust_lumpsum_E_payments(l_employee_details.assignment_id);
2099
2100 if g_debug
2101 then
2102 hr_utility.trace('Lump Sum E Payments ===>'||g_result_table(15).balance_value);
2103 end if;
2104
2105 /* If Lump Sum C Payments get Pre-Post Ratios
2106 */
2107
2108 if (g_result_table(13).balance_value > 0)
2109 then
2110 l_result := pay_au_terminations.etp_prepost_ratios(
2111 l_employee_details.assignment_id -- number in
2112 ,l_employee_details.date_start -- date in
2113 ,l_employee_details.actual_termination_date -- date in
2114 ,'N' -- Bug#2819479 Flag to check whether this function called by Termination Form.
2115 ,l_pre01jul1983_days -- number out
2116 ,l_post30jun1983_days -- number out
2117 ,l_pre01jul1983_ratio -- number out
2118 ,l_post30jun1983_ratio -- number out
2119 ,l_etp_service_date -- date out
2120 ,l_le_etp_service_date
2121 ); -- date out /* Bug 4177679 */
2122
2123 if l_result = 0 then
2124 raise e_prepost_error;
2125 end if;
2126 else
2127 l_pre01jul1983_ratio := 0;
2128 l_post30jun1983_ratio := 0;
2129 end if;
2130
2131 /* bug 14703826 - Removed bug 9226023 for taxable and taxfee portion logic as already divided */
2132 g_tax_free_etp := g_result_table(23).balance_value + g_result_table(24).balance_value +
2133 g_result_table(16).balance_value;
2134
2135 g_taxable_etp := g_result_table(25).balance_value + g_result_table(26).balance_value ;
2136
2137
2138 /* Bug 3891577 - 1) Element details archived only for Detail Report
2139 Start Bug 3891577
2140 */
2141 if g_parameters.report_mode ='D' /* Detail Report */
2142 then
2143 archive_element_details(p_assignment_action_id
2144 ,l_employee_details.assignment_id
2145 ,p_effective_date
2146 ,l_pre01jul1983_ratio
2147 ,l_post30jun1983_ratio);
2148 end if;
2149 /* End Bug 3891577 */
2150
2151 /*bug 7571001 - Call get_allowance_balances for detail and summary reports */
2152 get_allowance_balances(l_employee_details.assignment_id,l_assignment_action_id);
2153
2154 end if; /* End of Not an FBT Employee */
2155
2156
2157 archive_balance_details(p_assignment_action_id
2158 ,l_employee_details.assignment_id
2159 ,p_effective_date
2160 ,l_pre01jul1983_ratio
2161 ,l_post30jun1983_ratio
2162 ,l_action_sequence);
2163
2164 if g_debug then
2165 hr_utility.set_location('Leaving '||l_procedure,300);
2166 end if;
2167
2168 end archive_code;
2169
2170 --------------------------------------------------------------------+
2171 -- Name : Get_fbt_balance
2172 -- Type : Procedure
2173 -- Access: Public
2174 -- This procedure archives sets the FBT Balance Value for an
2175 -- Assignment. Based in Input Start/End Dates Max Assignment Action
2176 -- is fetched. FBT Balance Value computed using Globals FBT_Rate
2177 -- and Medicare Levy
2178 --------------------------------------------------------------------+
2179
2180
2181 procedure get_fbt_balance(p_assignment_id in pay_assignment_actions.assignment_id%type
2182 ,p_start_date in date
2183 ,p_end_date in date
2184 ,p_action_sequence out nocopy number)
2185 is
2186 cursor c_max_assignment_action_id(
2187 c_assignment_id pay_assignment_actions.assignment_id%TYPE,
2188 c_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE,
2189 c_start_date date,
2190 c_end_date date)
2191 IS
2192 select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
2193 ,max(paa.action_sequence) action_sequence
2194 from pay_assignment_actions paa
2195 , pay_payroll_actions ppa
2196 , per_assignments_f paf
2197 where paa.assignment_id = paf.assignment_id
2198 and paf.assignment_id = c_assignment_id
2199 and paa.assignment_id = c_assignment_id
2200 and ppa.payroll_action_id = paa.payroll_action_id
2201 and ppa.effective_date between c_start_date and c_end_date
2202 and ppa.payroll_id = paf.payroll_id
2203 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
2204 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
2205 and paa.action_status='C'
2206 AND paa.tax_unit_id = c_tax_unit_id;
2207
2208 CURSOR c_get_global(c_name VARCHAR2
2209 ,c_year_end DATE)
2210 IS
2211 SELECT global_value
2212 FROM ff_globals_f
2213 WHERE global_name = c_name
2214 AND legislation_code = 'AU'
2215 AND c_year_end BETWEEN effective_start_date
2216 AND effective_end_date ;
2217
2218 r_global c_get_global%ROWTYPE;
2219 l_bal_value number;
2220 l_max_assignment_action_id pay_assignment_actions.assignment_action_id%type;
2221 l_max_action_sequence pay_assignment_actions.action_sequence%type;
2222 l_med_levy number;
2223 l_fbt_rate number;
2224 l_procedure varchar2(240);
2225
2226 /* Changes for 9147438 start */
2227 l_fw_fbt_output_tab pay_balance_pkg.t_detailed_bal_out_tab;
2228 l_fw_f_type varchar2(1);
2229 l_fw_j_type varchar2(1);
2230 f_fw_date_tab pay_au_foreign_workers.tab_fw_dates;
2231 j_fw_date_tab pay_au_foreign_workers.tab_fw_dates;
2232 /* Changes for 9147438 end */
2233
2234 begin
2235 g_fw_reporting_fbt:=0;
2236 g_fw_fbt_balance :=0;
2237 g_debug := hr_utility.debug_enabled;
2238
2239 if g_debug then
2240 l_procedure := g_package||'.Get_fbt_balance' ;
2241 hr_utility.set_location('Entering '||l_procedure,400);
2242 end if;
2243
2244 open c_max_assignment_action_id(p_assignment_id,g_tax_unit_id,p_start_date,p_end_date);
2245 fetch c_max_assignment_action_id into l_max_assignment_action_id,l_max_action_sequence;
2246 close c_max_assignment_action_id;
2247
2248 if (l_max_assignment_action_id is NOT null)
2249 then
2250 l_bal_value := pay_balance_pkg.get_value(g_fbt_defined_balance_id
2251 ,l_max_assignment_action_id
2252 ,g_tax_unit_id
2253 ,null,null,null,null,null);
2254
2255 /* Bug 4133326 - Added FBT Balance > 1000 Check */
2256 if (l_bal_value <= to_number(g_fbt_threshold)) /* Bug 5708255 */
2257 then
2258 l_bal_value := 0;
2259 end if;
2260 else
2261 l_bal_value := 0;
2262 end if;
2263
2264 open c_get_global('FBT_RATE',add_months(g_end_date,-3)); /* Add_months included for bug 5333143 */
2265 fetch c_get_global into r_global;
2266 close c_get_global;
2267
2268 l_fbt_rate := to_number(r_global.global_value);
2269
2270 open c_get_global('MEDICARE_LEVY',add_months(g_end_date,-3)); /* Add_months included for bug 5333143 */
2271 fetch c_get_global into r_global;
2272 close c_get_global;
2273
2274 l_med_levy := to_number(r_global.global_value);
2275
2276 g_fbt_balance_value := l_bal_value / (1 - (l_fbt_rate + l_med_levy));
2277
2278 /* Changes for 9147438 start */
2279 g_net_balance:=g_net_balance+l_bal_value;
2280 /* Changes for 9147438 end */
2281
2282 if g_debug then
2283 hr_utility.set_location('Fringe Balance Value got := '||l_bal_value,302);
2284 hr_utility.set_location('Value to be archived := '||g_fbt_balance_value,302);
2285 end if;
2286
2287 p_action_sequence := l_max_action_sequence;
2288
2289 /* Changes for 9147438 start */
2290 if g_debug then
2291 hr_utility.set_location('Starting FW FBT check .. '||
2292 'g_fw_fbt_term_check '||g_fw_fbt_term_check||' , g_fw_fbt_check '||g_fw_fbt_check, 5);
2293 end if;
2294
2295 if g_fw_fbt_term_check <> 'NN' or g_fw_fbt_check <> 'NN' then
2296
2297 l_fw_fbt_output_tab.delete;
2298 t_fw_gross_type.delete;
2299 f_fw_date_tab.delete;
2300 j_fw_date_tab.delete;
2301
2302 if g_fw_fbt_term_check <> 'NN' then -- Case for terminated employee with FB before the financial year start
2303
2304 if g_fw_fbt_term_check = 'YY' or g_fw_fbt_term_check = 'YN' then
2305 pay_au_foreign_workers.get_foreign_worker_dates(p_assignment_id, g_tax_unit_id, add_months(g_start_date,-3), (g_start_date - 1),
2306 pay_au_foreign_workers.g_fw_f_type, f_fw_date_tab);
2307 end if;
2308
2309 if g_fw_fbt_term_check = 'YY' or g_fw_fbt_term_check = 'NY' then
2310 pay_au_foreign_workers.get_foreign_worker_dates(p_assignment_id, g_tax_unit_id, add_months(g_start_date,-3), (g_start_date - 1),
2311 pay_au_foreign_workers.g_fw_j_type, j_fw_date_tab);
2312 end if;
2313
2314 if f_fw_date_tab.count > 0 then
2315 t_fw_gross_type(1) := 'F';
2316 if j_fw_date_tab.count >0 then
2317 t_fw_gross_type(2) := 'J';
2318 end if;
2319 elsif j_fw_date_tab.count > 0 then
2320 t_fw_gross_type(1) := 'J';
2321 end if;
2322
2323 for i_idx in t_fw_gross_type.first .. t_fw_gross_type.last loop
2324
2325 IF t_fw_gross_type(i_idx) = 'F' THEN
2326
2327 pay_au_foreign_workers.get_foreign_payment_amounts(p_assignment_id,
2328 g_tax_unit_id,
2329 f_fw_date_tab,
2330 add_months(g_start_date,-3),
2331 (g_start_date - 1),
2332 pay_au_foreign_workers.g_fw_f_type,
2333 p_fw_fbt_bal_type_tab,
2334 l_fw_fbt_output_tab) ;
2335
2336 ELSIF t_fw_gross_type(i_idx) = 'J' THEN
2337 pay_au_foreign_workers.get_foreign_payment_amounts(p_assignment_id,
2338 g_tax_unit_id,
2339 j_fw_date_tab,
2340 add_months(g_start_date,-3),
2341 (g_start_date - 1),
2342 pay_au_foreign_workers.g_fw_j_type,
2343 p_fw_fbt_bal_type_tab,
2344 l_fw_fbt_output_tab) ;
2345 END IF;
2346
2347 if l_fw_fbt_output_tab(1).balance_value >0 then
2348 g_fw_fbt_balance := g_fw_fbt_balance + l_fw_fbt_output_tab(1).balance_value;
2349 end if;
2350 end loop;
2351
2352 g_fw_reporting_fbt := g_fw_fbt_balance/(1-(l_fbt_rate+l_med_levy));
2353 IF l_bal_value = 0 THEN
2354 g_fw_reporting_fbt := 0;
2355 END IF;
2356 elsif g_fw_fbt_check <> 'NN' then -- Case for employees in normal FB year
2357
2358 if g_fw_fbt_check = 'YY' or g_fw_fbt_check = 'YN' then
2359 pay_au_foreign_workers.get_foreign_worker_dates(p_assignment_id, g_tax_unit_id, add_months(g_start_date,-3), add_months(g_end_date,-3),
2360 pay_au_foreign_workers.g_fw_f_type, f_fw_date_tab);
2361 end if;
2362 if g_fw_fbt_check = 'YY' or g_fw_fbt_check = 'NY' then
2363 pay_au_foreign_workers.get_foreign_worker_dates(p_assignment_id, g_tax_unit_id, add_months(g_start_date,-3), add_months(g_end_date,-3),
2364 pay_au_foreign_workers.g_fw_j_type, j_fw_date_tab);
2365 end if;
2366
2367 if f_fw_date_tab.count > 0 then
2368 t_fw_gross_type(1) := 'F';
2369 if j_fw_date_tab.count >0 then
2370 t_fw_gross_type(2) := 'J';
2371 end if;
2372 elsif j_fw_date_tab.count > 0 then
2373 t_fw_gross_type(1) := 'J';
2374 end if;
2375
2376 for i_idx in t_fw_gross_type.first .. t_fw_gross_type.last loop
2377
2378 IF t_fw_gross_type(i_idx) = 'F' THEN
2379
2380 pay_au_foreign_workers.get_foreign_payment_amounts(p_assignment_id,
2381 g_tax_unit_id,
2382 f_fw_date_tab,
2383 add_months(g_start_date,-3),
2384 add_months(g_end_date,-3),
2385 pay_au_foreign_workers.g_fw_f_type,
2386 p_fw_fbt_bal_type_tab,
2387 l_fw_fbt_output_tab) ;
2388
2389 ELSIF t_fw_gross_type(i_idx) = 'J' THEN
2390 pay_au_foreign_workers.get_foreign_payment_amounts(p_assignment_id,
2391 g_tax_unit_id,
2392 j_fw_date_tab,
2393 add_months(g_start_date,-3),
2394 add_months(g_end_date,-3),
2395 pay_au_foreign_workers.g_fw_j_type,
2396 p_fw_fbt_bal_type_tab,
2397 l_fw_fbt_output_tab) ;
2398 END IF;
2399
2400 if l_fw_fbt_output_tab(1).balance_value > 0 then
2401 g_fw_fbt_balance := g_fw_fbt_balance + l_fw_fbt_output_tab(1).balance_value;
2402 end if;
2403 end loop;
2404 g_fw_reporting_fbt := g_fw_fbt_balance/(1-(l_fbt_rate+l_med_levy));
2405 IF l_bal_value = 0 THEN
2406 g_fw_reporting_fbt := 0;
2407 END IF;
2408 end if;
2409
2410 if g_debug then
2411 hr_utility.set_location('Leaving FW FBT g_fw_fbt_balance : '||g_fw_fbt_balance,5);
2412 end if;
2413
2414
2415 end if;
2416 /* Changes for 9147438 end */
2417
2418 if g_debug then
2419 hr_utility.set_location('Leaving '||l_procedure,400);
2420 end if;
2421
2422 end;
2423
2424 --------------------------------------------------------------------+
2425 -- Name : Archive_element_details
2426 -- Type : Procedure
2427 -- Access: Public
2428 -- This procedure identifies all Elements processed for Assignment
2429 -- in Financial Year.
2430 -- Archives all The Lump Sum Payments and ETP payments.
2431 -- In case of Allowance elements,builds a PL/SQL table with
2432 -- the Allowance Balance_Type_ID's
2433 --------------------------------------------------------------------+
2434
2435 procedure archive_element_details(p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE
2436 ,p_assignment_id in pay_assignment_actions.assignment_id%TYPE
2437 ,p_effective_date in date
2438 ,p_pre01jul1983_ratio in number
2439 ,p_post30jun1983_ratio in number)
2440 is
2441
2442 --------------------------------------------------------------------+
2443 -- Cursor : c_ps_element_details
2444 -- Description : Fetches all Elements processed for Assignment.
2445 -- Ignores elements feeding Lump Sum Payment Balances
2446 -- and Invalidity Payments.
2447 --------------------------------------------------------------------+
2448 /* Bug 4179109 - Check for Non Taxable Allowances included */
2449 /* Bug 5063359 - Modified decode for Employer Charges to return classification as Employer Charges rather than Employer Superannuation Contribution */
2450 /* Bug 5119734 - Modified decode to check for Allowances if classifcation is Earnings */
2451 /*Bug 5603254 - Removed tables piv2 and prrv2 and their joins from cursor , added a call to function pay_au_rec_det_archive.get_element_payment_hours to get the value for hours and rate */
2452 /*Bug 5846278 - Added Lump Sum E Payments in Not exists Clause */
2453 /* bug 7571001 - Removed Allowance classification from the existing cursor */
2454 /*bug 9190980 - Added Retro Earnings Leave Loading GT 12 Mths Amount, Retro Earnings Spread GT 12 Mths Amount for Lump Sum E in Not Exist clause
2455 Retro Pre Tax GT 12 Mths Amount is commented out because it is to be reported in Pre Tax Deductions section*/
2456 /* Bug 9190980 - Uncommented Retro Pre Tax GT 12 Mths Amount element such that it is not reported */
2457 /*Bug 9147421 - Added check for element classification 'Foreign Tax Deductions'
2458 - Negate the amount for foreign tax deductions and classify as Tax Deductions
2459 */
2460 cursor c_ps_element_details
2461 (c_assignment_id pay_assignment_actions.assignment_id%TYPE,
2462 c_business_group_id hr_all_organization_units.organization_id%TYPE,
2463 c_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE,
2464 c_start_date date,
2465 c_end_date date)
2466 is
2467 select element_name,label classification_name,sum(amount) payment,sum(hours) hours,rate
2468 from
2469 (select distinct
2470 nvl(pet.reporting_name, pet.element_name) element_name,
2471 decode(instr(pec.classification_name, 'Earnings'), 0, null,
2472 decode(pec2.classification_name, 'Non Taxable', 'Non Taxable Earnings','Taxable Earnings'))|| /* Bug 4179109, 5119734, 7571001*/
2473 decode(instr(pec.classification_name, 'Payments'), 0, null,
2474 decode(instr(pec.classification_name, 'Direct'), 0, 'Taxable Earnings', 'Direct Payments')) ||
2475 decode(instr(pec.classification_name, 'Deductions'), 0, null,
2476 decode(pec.classification_name , 'Termination Deductions' , 'Tax Deductions'
2477 , 'Involuntary Deductions' , 'Post Tax Deductions'
2478 , 'Voluntary Deductions' , 'Post Tax Deductions'
2479 , 'Foreign Tax Deductions' , 'Tax Deductions'
2480 , pec.classification_name )) ||
2481 decode(instr(pec.classification_name, 'Employer Charges'), 0,null,'Employer Charges') label,
2482 decode(pec.classification_name,'Foreign Tax Deductions',(-1 * NVL(prrv1.result_value,0)), prrv1.result_value) amount,
2483 pay_au_rec_det_archive.get_element_payment_hours(prr.assignment_action_id,pet.element_type_id,prr.run_result_id,ppa.effective_date) hours, /*Bug 5603254 */
2484 decode(pay_au_rec_det_archive.get_element_payment_rate(prr.assignment_action_id,pet.element_type_id,prr.run_result_id,ppa.effective_date), null,
2485 (prrv1.result_value/pay_au_rec_det_archive.get_element_payment_hours(prr.assignment_action_id,pet.element_type_id,prr.run_result_id, ppa.effective_date)),
2486 pay_au_rec_det_archive.get_element_payment_rate(prr.assignment_action_id,pet.element_type_id,prr.run_result_id,ppa.effective_date)) rate, /* 5599310 */
2487 prr.run_result_id,
2488 paa.source_action_id
2489 from pay_element_types_f pet
2490 ,pay_input_values_f piv1
2491 ,pay_element_classifications pec
2492 ,pay_assignment_actions paa
2493 ,pay_payroll_actions ppa
2494 ,per_assignments_f paaf
2495 ,pay_run_results prr
2496 ,pay_run_result_values prrv1
2497 ,pay_element_classifications pec2
2498 ,pay_sub_classification_rules_f pscr
2499 where paaf.assignment_id = c_assignment_id
2500 and paaf.business_group_id = c_business_group_id
2501 and paa.assignment_id = c_assignment_id
2502 and paaf.assignment_id = paa.assignment_id
2503 and paa.payroll_action_id = ppa.payroll_action_id
2504 and paa.action_status = 'C'
2505 and paa.tax_unit_id like c_tax_unit_id
2506 and paaf.payroll_id = ppa.payroll_id
2507 and paaf.business_group_id = ppa.business_group_id
2508 and ppa.effective_date between c_start_date and c_end_date
2509 and ppa.action_type in ('Q','R','I','B','V')
2510 and prr.assignment_action_id = paa.assignment_action_id
2511 and pet.element_type_id = prr.element_type_id
2512 and pet.element_type_id = piv1.element_type_id
2513 and prr.run_result_id = prrv1.run_result_id
2514 and prrv1.input_value_id = piv1.input_value_id
2515 and pet.classification_id = pec.classification_id
2516 and pec.legislation_code = 'AU'
2517 and piv1.name = 'Pay Value'
2518 and pet.classification_id = pec.classification_id
2519 and (instr(pec.classification_name, 'Earnings') > 0
2520 or instr(pec.classification_name, 'Payments') > 0
2521 or instr(pec.classification_name, 'Deductions') > 0
2522 or instr(pec.classification_name, 'Employer Charges' ) > 0 )
2523 and pet.element_type_id = pscr.element_type_id (+)
2524 and ppa.effective_date between nvl(pscr.effective_start_date, ppa.effective_date)
2525 and nvl(pscr.effective_end_date, ppa.effective_date)
2526 and pscr.classification_id = pec2.classification_id(+)
2527 and ppa.date_earned between pet.effective_start_date and pet.effective_end_date
2528 and ppa.effective_date between paaf.effective_start_date and paaf.effective_end_date
2529 and prr.status in ('P','PA')
2530 and NOT EXISTS
2531 (
2532 select 1
2533 from pay_run_results prr1,
2534 pay_element_types_f pet1
2535 where prr1.assignment_action_id = paa.assignment_action_id
2536 and prr1.element_type_id = pet1.element_type_id
2537 and pet1.element_name in ('Retropay GT 12 Mths Amount',
2538 'Retro Pre Tax GT 12 Mths Amount', /*start 9190980*/
2539 'Retropay Earnings Spread GT 12 Mths Amount',
2540 'Retropay Earnings Leave Loading GT 12 Mths Amount', /*end 9190980*/
2541 'Retropay Earnings Additional GT12 Amount') -- bug 13362286
2542 and prr1.source_id = prr.source_id
2543 and prr.source_type='E' /*Bug 4363057 */
2544 )
2545 and NOT EXISTS
2546 (
2547 select 1
2548 from pay_balance_feeds_f pbf,
2549 pay_balance_types pbt
2550 where pbt.balance_type_id = pbf.balance_type_id
2551 and pbt.balance_name in ('Invalidity Payments','Lump Sum A Payments',
2552 'Lump Sum B Payments','Lump Sum C Payments',
2553 'Lump Sum D Payments','Lump Sum E Payments',/*Bug 5846278 */
2554 'Retro Pre Tax GT 12 Mths Amount', /*start 9190980*/
2555 'Retro Earnings Leave Loading GT 12 Mths Amount',
2556 'Retro Earnings Spread GT 12 Mths Amount', /*end 9190980*/
2557 'Retro Earnings Additional GT 12 Mths Amount') -- bug 13362286
2558 and pbt.legislation_code = 'AU'
2559 and pbf.input_value_id = piv1.input_value_id
2560 )
2561 and not exists /* added for bug 7571001 */
2562 (
2563 select 1
2564 from
2565 PAY_BALANCE_ATTRIBUTES pba
2566 ,pay_defined_balances pdb
2567 ,pay_balance_dimensions pbd
2568 ,PAY_BALANCE_FEEDS_F pbf
2569 where pba.attribute_id = g_attribute_id
2570 AND pba.defined_balance_id = pdb.defined_balance_id
2571 AND pbd.balance_dimension_id = pdb.balance_dimension_id
2572 AND pbd.dimension_name = '_ASG_LE_YTD'
2573 and pbd.legislation_code = 'AU'
2574 AND pdb.balance_type_id = pbf.balance_type_id
2575 and pbf.business_group_id = c_business_group_id
2576 AND pbf.input_value_id = piv1.input_value_id
2577 )
2578 )
2579 group by element_name,label,rate;
2580
2581 /* bug 7571001 - Added the new cursor for Allowance classification */
2582 /* Bug 8760756 - Added join between pay_run_result_values and pay_input_values_f tables and
2583 a condition to fetch amount value only if the element input value is of 'Pay Value'.
2584 Removed references to hours and rate columns from the cursor ,as they are not displayed anywhere in EOY reports. */
2585 /* bug 12615137 - Added ORDERED hint and re-arranged FROM clause */
2586 cursor c_ps_alw_details
2587 (c_assignment_id pay_assignment_actions.assignment_id%TYPE,
2588 c_business_group_id hr_all_organization_units.organization_id%TYPE,
2589 c_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE,
2590 c_start_date date,
2591 c_end_date date) is
2592 select /*+ ORDERED */ element_name,label, sum(amount) payment
2593 from
2594 (select distinct
2595 nvl(pet.reporting_name, pet.element_name) element_name,
2596 'Allowance' label, -- bug 7571001 Allowance only
2597 prrv.result_value amount,
2598 prr.run_result_id,
2599 pac.source_action_id
2600 FROM
2601 per_all_assignments_f paa
2602 ,pay_assignment_actions pac
2603 ,pay_payroll_actions ppa
2604 ,pay_run_results prr
2605 ,pay_element_types_f pet
2606 ,pay_input_values_f piv
2607 ,pay_run_result_values prrv
2608 ,pay_balance_feeds_f pbf
2609 ,pay_defined_balances pdb
2610 ,pay_balance_dimensions pbd
2611 , pay_balance_attributes pba
2612 WHERE pba.attribute_id = g_attribute_id
2613 AND pdb.defined_balance_id = pba.defined_balance_id
2614 AND pbd.balance_dimension_id = pdb.balance_dimension_id
2615 AND pbd.dimension_name = '_ASG_LE_YTD'
2616 AND pbd.legislation_code = 'AU'
2617 AND pdb.balance_type_id = pbf.balance_type_id
2618 AND pbf.input_value_id = piv.input_value_id
2619 and piv.name ='Pay Value' -- Bug 8760756
2620 AND piv.element_type_id = pet.element_type_id
2621 and paa.assignment_id = c_assignment_id -- bug 12615137
2622 and paa.business_group_id = c_business_group_id
2623 AND pac.assignment_id = c_assignment_id
2624 and pac.tax_unit_id = c_tax_unit_id
2625 and paa.assignment_id = pac.assignment_id
2626 and pac.payroll_action_id = ppa.payroll_Action_id
2627 and ppa.payroll_id = paa.payroll_id
2628 and ppa.action_type in ('Q','R','B','I','V')
2629 and pac.assignment_action_id = prr.assignment_Action_id
2630 and prr.element_type_id = pet.element_type_id
2631 and prr.run_result_id = prrv.run_result_id
2632 and prrv.input_value_id = piv.input_value_id -- Bug 8760756
2633 AND prr.status in ('P','PA')
2634 AND pac.action_status ='C'
2635 and ppa.effective_date between c_start_date and c_end_date
2636 and ppa.effective_date between paa.effective_start_date and paa.effective_end_date
2637 and ppa.effective_date between piv.effective_start_date and piv.effective_end_date
2638 and ppa.effective_date between pet.effective_start_date and pet.effective_end_date
2639 and ppa.effective_date between pbf.effective_start_date and pbf.effective_end_date
2640 )
2641 group by element_name,label
2642 ;
2643
2644 TYPE char_tab_type IS TABLE OF VARCHAR2(350) INDEX BY BINARY_INTEGER;
2645 TYPE num_tab_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2646
2647 l_prev_bal_type_id number;
2648 l_procedure varchar2(240);
2649
2650 l_ele_name char_tab_type;
2651 l_ele_pay_value num_tab_type;
2652 l_ele_classification_name char_tab_type;
2653
2654 counter number;
2655
2656 begin
2657
2658 g_debug := hr_utility.debug_enabled;
2659 l_prev_bal_type_id := 0;
2660
2661 if g_debug
2662 then
2663 l_procedure := g_package||'Element_Details';
2664 hr_utility.set_location('Entering ' || l_procedure,500);
2665 end if;
2666
2667 FOR csr_ele_det IN c_ps_element_details(p_assignment_id,g_business_group_id,g_tax_unit_id,g_start_date,g_end_date)
2668 LOOP
2669
2670 if g_debug
2671 then
2672 hr_utility.set_location('Element Name '||csr_ele_det.element_name,500);
2673 hr_utility.set_location('Classification Name '||csr_ele_det.classification_name,500);
2674 end if;
2675
2676 insert into pay_action_information (
2677 action_information_id,
2678 action_context_id,
2679 action_context_type,
2680 effective_date,
2681 source_id,
2682 tax_unit_id,
2683 action_information_category,
2684 action_information1,
2685 action_information2,
2686 action_information3,
2687 action_information4,
2688 action_information5,
2689 action_information6,
2690 assignment_id)
2691 values (
2692 pay_action_information_s.nextval,
2693 p_assignment_action_id,
2694 'AAP',
2695 p_effective_date,
2696 null,
2697 g_tax_unit_id,
2698 'AU_ELEMENT_RECON_DETAILS',
2699 csr_ele_det.element_name,
2700 csr_ele_det.classification_name,
2701 null,
2702 csr_ele_det.hours,
2703 csr_ele_det.rate,
2704 csr_ele_det.payment,
2705 p_assignment_id);
2706
2707 if g_debug then
2708 hr_utility.set_location('After Inserting Element Values ',500);
2709 end if;
2710 END LOOP;
2711
2712 FOR csr_alw_det IN c_ps_alw_details(p_assignment_id,g_business_group_id,g_tax_unit_id,g_start_date,g_end_date) LOOP
2713
2714 if g_debug
2715 then
2716 hr_utility.set_location('Element Name '||csr_alw_det.element_name,600);
2717 end if;
2718
2719 /* Bug 8760756 - As hours and rate columns are removed from c_ps_alw_details cursor query ,
2720 the corresponding column values in pay_action_information table are made null */
2721
2722 insert into pay_action_information (
2723 action_information_id,
2724 action_context_id,
2725 action_context_type,
2726 effective_date,
2727 source_id,
2728 tax_unit_id,
2729 action_information_category,
2730 action_information1,
2731 action_information2,
2732 action_information3,
2733 action_information4,
2734 action_information5,
2735 action_information6,
2736 assignment_id)
2737 values (
2738 pay_action_information_s.nextval,
2739 p_assignment_action_id,
2740 'AAP',
2741 p_effective_date,
2742 null,
2743 g_tax_unit_id,
2744 'AU_ELEMENT_RECON_DETAILS',
2745 csr_alw_det.element_name,
2746 'Allowance',
2747 null,
2748 null, -- Bug 8760756
2749 null, -- Bug 8760756
2750 csr_alw_det.payment,
2751 p_assignment_id);
2752
2753 if g_debug then
2754 hr_utility.set_location('After Inserting Allowance Element Values ',600);
2755 end if;
2756 END LOOP;
2757
2758 /* Now Archive Lump Sum Payments and Invalidity */
2759 /* Use g_result_values_tab to get the Values
2760 'Lump Sum A Payments ===>' g_result_table(11).balance_value
2761 'Lump Sum B Payments ===>' g_result_table(12).balance_value
2762 'Lump Sum C Payments ===>' g_result_table(13).balance_value
2763 'Lump Sum D Payments ===>' g_result_table(14).balance_value
2764 'Lump Sum E Payments ===>' g_result_table(15).balance_value
2765 'Invalidity Payments ===>' g_result_table(16).balance_value
2766 */
2767
2768 if g_debug then
2769 hr_utility.set_location('p_pre01jul1983_ratio........=>'||p_pre01jul1983_ratio,200);
2770 hr_utility.set_location('p_post30jun1983_ratio........=>'||p_post30jun1983_ratio,200);
2771 end if;
2772
2773 counter := 0;
2774 if (g_result_table(11).balance_value > 0)
2775 then
2776 counter := counter + 1;
2777 l_ele_name(counter) := 'Lump Sum A Payment';
2778 l_ele_pay_value(counter) := g_result_table(11).balance_value;
2779 l_ele_classification_name(counter) := 'Lump Sum A Payments';
2780 end if;
2781 if (g_result_table(12).balance_value > 0)
2782 then
2783 counter := counter + 1;
2784 l_ele_name(counter) := 'Lump Sum B Payment';
2785 l_ele_pay_value(counter) := g_result_table(12).balance_value;
2786 l_ele_classification_name(counter) := 'Lump Sum B Payments';
2787 end if;
2788 /* Bug 8769345 - The sum of all ETP Tax Free balances constitue pre 83 ETP component and
2789 the sum of all ETP Taxable balances constitute post 83 ETP component */
2790 /* Bug 9146069 - Changed the terms for Lump Sum C Payments */
2791 /* Bug 9226023 - Added global variables to store Taxable and Tax Free values */
2792 if (g_result_table(13).balance_value > 0 OR g_result_table(16).balance_value > 0)
2793 then
2794 counter := counter + 1;
2795 l_ele_name(counter) := 'Tax Free Component';
2796 l_ele_pay_value(counter) := g_tax_free_etp;
2797 l_ele_classification_name(counter) := 'Lump Sum C Payments';
2798
2799 counter := counter + 1;
2800 l_ele_name(counter) := 'Taxable Component';
2801 l_ele_pay_value(counter) := g_taxable_etp;
2802 l_ele_classification_name(counter) := 'Lump Sum C Payments';
2803 end if;
2804 /* End 9226023 */
2805
2806 if (g_result_table(14).balance_value > 0)
2807 then
2808 counter := counter + 1;
2809 l_ele_name(counter) := 'Lump Sum D Payment';
2810 l_ele_pay_value(counter) := g_result_table(14).balance_value;
2811 l_ele_classification_name(counter) := 'Lump Sum D Payments';
2812 end if;
2813 /* Bug 9190980 - Added code to report Lump Sum E Payments sum after adjdusment of Retro GT12 Pre Tax < $400 payments */
2814 if ((g_result_table(15).balance_value + (g_result_table(33).balance_value - g_adj_lump_sum_pre_tax)) > 0)
2815 then
2816 counter := counter + 1;
2817 l_ele_name(counter) := 'Lump Sum E Payment';
2818 l_ele_pay_value(counter) := g_result_table(15).balance_value + (g_result_table(33).balance_value - g_adj_lump_sum_pre_tax);
2819 l_ele_classification_name(counter) := 'Lump Sum E Payments';
2820 end if;
2821
2822 /* Bug 4201894 - Archive Adjusted Lump Sum E Amount (< $400 in a period)
2823 as Taxable Earnings*/
2824 if (g_adjusted_lump_sum_e_pay <> 0) -- bug8711855 condition is changed to <> due to Retro Pre Tax GT12 balance
2825 then
2826 counter := counter + 1;
2827 l_ele_name(counter) := 'Retro Payment < $400';
2828 l_ele_pay_value(counter) := g_adjusted_lump_sum_e_pay ;
2829 l_ele_classification_name(counter) := 'Taxable Earnings';
2830 end if;
2831 /* End Bug 4201894 */
2832
2833 /* Start 9190980 - Added code to report Lump Sum E Payments > $400 under 'Lump Sum E Pre Tax'
2834 and if Lump Sum E Payments < $400 ,the corresponding Retro GT 12 Pre Tax Deductions
2835 are reported under Retro Pre Tax < $400 */
2836
2837 if ((g_result_table(33).balance_value - g_adj_lump_sum_pre_tax) <> 0)
2838 then
2839 counter := counter + 1;
2840 l_ele_name(counter) := 'Lump Sum E Pre Tax';
2841 l_ele_pay_value(counter) := g_result_table(33).balance_value - g_adj_lump_sum_pre_tax ;
2842 l_ele_classification_name(counter) := 'Pre Tax Deductions';
2843 end if;
2844
2845 if (g_adj_lump_sum_pre_tax <> 0)
2846 then
2847 counter := counter + 1;
2848 l_ele_name(counter) := 'Retro Pre Tax < $400';
2849 l_ele_pay_value(counter) := g_adj_lump_sum_pre_tax ;
2850 l_ele_classification_name(counter) := 'Pre Tax Deductions';
2851 end if;
2852 /*End 9190980*/
2853
2854 if (counter >= 1)
2855 then
2856 for i in 1..counter
2857 loop
2858
2859 insert into pay_action_information (
2860 action_information_id,
2861 action_context_id,
2862 action_context_type,
2863 effective_date,
2864 source_id,
2865 tax_unit_id,
2866 action_information_category,
2867 action_information1,
2868 action_information2,
2869 action_information3,
2870 action_information4,
2871 action_information5,
2872 action_information6,
2873 assignment_id)
2874 values (
2875 pay_action_information_s.nextval,
2876 p_assignment_action_id,
2877 'AAP',
2878 p_effective_date,
2879 null,
2880 g_tax_unit_id,
2881 'AU_ELEMENT_RECON_DETAILS',
2882 l_ele_name(i),
2883 l_ele_classification_name(i),
2884 null,
2885 null,
2886 null,
2887 l_ele_pay_value(i),
2888 p_assignment_id);
2889 end loop;
2890 end if;
2891
2892 if g_debug then
2893 hr_utility.set_location('Leaving '||l_procedure,700 );
2894 end if;
2895
2896 end archive_element_details;
2897
2898 --------------------------------------------------------------------+
2899 -- Name : Adjust_lumpsum_E_payments
2900 -- Type : Procedure
2901 -- Access: Public
2902 -- This procedure identifies all Runs in a period for the Assignment
2903 -- in Financial Year and check is Lump Sum E Payment PTD < $400.In
2904 -- that case the YTD value is adjusted for the same.
2905 --------------------------------------------------------------------+
2906 /* Bug 4201894 - Adjusted Amount will be cumulatively stored in
2907 g_adjusted_lump_sum_e_pay and displayed in Taxable Earnings
2908 element section */
2909 /*bug8711855 - p_assignment_action_id and p_registered_employer parameter are added to call
2910 pay_au_payment_summary.get_retro_lumpsumE_value function */
2911 --------------------------------------------------------------------+
2912 procedure Adjust_lumpsum_E_payments(p_assignment_id in pay_assignment_actions.assignment_id%type)
2913 is
2914
2915 l_procedure varchar2(240);
2916 v_lump_sum_E_ytd number;
2917 v_adj_lump_sum_E_ptd number; /* Bug 4201894 */
2918 v_adj_lump_sum_pre_tax number;
2919 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
2920
2921
2922 begin
2923
2924 g_debug := hr_utility.debug_enabled;
2925
2926 if g_debug then
2927 l_procedure := 'Adjust_lumpsum_E_payments';
2928 hr_utility.set_location('In procedure '||g_package||l_procedure,600);
2929 end if;
2930
2931 /*bug8711855 - The calculation logic is changed to add retro GT12 and call
2932 pay_au_payment_summary.get_lumpsumE_value function to adjust by single Lump Sum E paymetns */
2933 /* bug 9190980 - Added argument v_adj_lump_sum_pre_tax in call to the function for fetching adjusted Retro GT12 Pre Tax Deductions*/
2934 v_lump_sum_E_ytd := g_result_table(15).balance_value + g_result_table(31).balance_value
2935 + g_result_table(32).balance_value - g_result_table(33).balance_value
2936 + g_result_table(40).balance_value ; -- bug 13362286
2937
2938 if v_lump_sum_E_ytd <> 0 then
2939
2940 v_lump_sum_E_ytd := pay_au_payment_summary.get_lumpsumE_value(g_tax_unit_id, p_assignment_id, g_start_date,
2941 g_end_date, p_lump_sum_E_ptd_tab, v_lump_sum_E_ytd, v_adj_lump_sum_E_ptd
2942 ,v_adj_lump_sum_pre_tax); -- Bug 9190980
2943 end if;
2944
2945 g_result_table(15).balance_value := v_lump_sum_E_ytd;
2946 g_adjusted_lump_sum_e_pay := v_adj_lump_sum_E_ptd; /* Bug 4201894*/
2947 g_adj_lump_sum_pre_tax := v_adj_lump_sum_pre_tax;
2948
2949 if g_debug then
2950 hr_utility.set_location('In procedure '||g_package||l_procedure,600);
2951 end if;
2952 end;
2953
2954 --------------------------------------------------------------------+
2955 -- Name : Get_allowance_balances
2956 -- Type : Procedure
2957 -- Access: Public
2958 -- Reads the PL/SQL table populated by Elements procedure for
2959 -- Allowances. Procedure Fetches the balance values and adjusts any
2960 -- Retro Payments.
2961 --------------------------------------------------------------------+
2962
2963 /* bug 7571001 - the way to retrieve allowance is based on Balance Attribute */
2964 procedure get_allowance_balances(p_assignment_id in pay_assignment_actions.assignment_id%type
2965 ,p_run_assignment_action_id in pay_assignment_actions.assignment_action_id%type)
2966 is
2967
2968
2969 CURSOR c_allowance_balances IS
2970 SELECT pdb.defined_balance_id
2971 ,NVL(pbt.reporting_name,pbt.balance_name) balance_name
2972 ,pay_balance_pkg.get_value(pdb.defined_balance_id
2973 ,p_run_assignment_action_id
2974 ,g_tax_unit_id
2975 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL) balance_value
2976 FROM pay_balance_attributes pba
2977 ,pay_defined_balances pdb
2978 ,pay_balance_types pbt
2979 ,pay_balance_dimensions pbd
2980 WHERE pba.attribute_id = g_attribute_id
2981 AND pdb.defined_balance_id = pba.defined_balance_id
2982 AND pbt.balance_type_id = pdb.balance_type_id
2983 AND pdb.balance_type_id = pbt.balance_type_id
2984 AND pdb.business_group_id = g_business_group_id
2985 and pbd.balance_dimension_id = pdb.balance_dimension_id
2986 and pbd.dimension_name = '_ASG_LE_YTD'
2987 and pbd.legislation_code = 'AU'
2988 ;
2989
2990 /* Changes for 9147438 start */
2991
2992 CURSOR c_get_fw_alw_bal_type_id IS
2993 select pbt.balance_type_id,
2994 pbt.balance_name
2995 from pay_bal_attribute_definitions pbad
2996 ,pay_balance_attributes pba
2997 ,pay_defined_balances pdb
2998 ,pay_balance_types pbt
2999 ,pay_balance_dimensions pbd
3000 where pbad.attribute_name = 'AU_EOY_ALLOWANCE'
3001 and pba.attribute_id = pbad.attribute_id
3002 and pba.defined_balance_id = pdb.defined_balance_id
3003 and pdb.balance_type_id = pbt.balance_type_id
3004 and pdb.business_group_id = g_business_group_id
3005 and pbd.balance_dimension_id = pdb.balance_dimension_id
3006 and pbd.dimension_name = '_ASG_LE_YTD'
3007 and pbd.legislation_code = 'AU';
3008
3009 cursor get_balance_name (c_defined_balance_id pay_defined_balances.defined_balance_id%type) is
3010 select NVL(pbt.reporting_name,pbt.balance_name) balance_name
3011 from pay_defined_balances pdb,
3012 pay_balance_types pbt
3013 where pdb.defined_balance_id = c_defined_balance_id
3014 and pdb.balance_type_id = pbt.balance_type_id
3015 and pdb.business_group_id = g_business_group_id;
3016
3017 /* Changes for 9147438 end */
3018
3019 t_allowance_balance pay_au_payment_summary.t_allowance_balance%TYPE;
3020 counter number := 1;
3021 l_YTD_allowance number := 0;
3022 l_result number;
3023 l_procedure varchar2(240);
3024
3025 /* Changes for 9147438 start */
3026 l_fw_f_type varchar2(1);
3027 l_fw_j_type varchar2(1);
3028 l_alw_total number;
3029 cnt number;
3030 idx number;
3031 x number;
3032 /* Changes for 9147438 end */
3033
3034 begin
3035
3036 g_debug := hr_utility.debug_enabled;
3037 if g_debug then
3038 l_procedure := g_package||'.Get_allowance_balances';
3039 hr_utility.set_location('Entering '||l_procedure,700);
3040 end if;
3041
3042 /* Changes for 9147438 start*/
3043 idx:=1;
3044 g_fw_alw_total:=0;
3045
3046 g_fw_input_alw_table.delete;
3047 for csr_rec in c_get_fw_alw_bal_type_id loop
3048 g_fw_input_alw_table(idx).balance_type_id := csr_rec.balance_type_id;
3049 idx := idx + 1;
3050 end loop;
3051 /* Changes for 9147438 end */
3052
3053 for rec_allowance_balances in c_allowance_balances loop
3054
3055 if nvl(rec_allowance_balances.balance_value,0) >0 then
3056 t_allowance_balance(counter).balance_name := rec_allowance_balances.balance_name;
3057 t_allowance_balance(counter).balance_value := rec_allowance_balances.balance_value;
3058
3059 if g_debug then
3060 hr_utility.set_location(l_procedure, 3);
3061 hr_utility.trace('t_allowance_ balance name ('||counter||') = '|| t_allowance_balance(counter).balance_name);
3062 hr_utility.trace('t_allowance_balance value ('||counter||') = '||t_allowance_balance(counter).balance_value);
3063 end if;
3064
3065 counter := counter +1;
3066
3067 end if;
3068 end loop;
3069
3070 IF t_allowance_balance.count > 0 THEN
3071
3072 l_result := pay_au_payment_summary.adjust_retro_allowances(t_allowance_balance,
3073 g_start_date,
3074 g_end_date,
3075 p_assignment_id,
3076 g_tax_unit_id);
3077
3078 for i in t_allowance_balance.FIRST..t_allowance_balance.LAST
3079 loop
3080 l_YTD_allowance := l_YTD_allowance + nvl(t_allowance_balance(i).balance_value,0);
3081 end loop;
3082
3083 END IF;
3084
3085 g_allowance_balance_value := l_YTD_allowance;
3086
3087 /* Changes for 9147438 start */
3088
3089 if g_fw_check = 'Y' then
3090
3091 f_fw_date_tab_g.delete;
3092 j_fw_date_tab_g.delete;
3093
3094 l_fw_f_type := pay_au_foreign_workers.check_foreign_worker(p_assignment_id
3095 ,g_tax_unit_id
3096 ,g_start_date
3097 ,g_end_date
3098 ,pay_au_foreign_workers.g_fw_f_type);
3099 l_fw_j_type := pay_au_foreign_workers.check_foreign_worker(p_assignment_id
3100 ,g_tax_unit_id
3101 ,g_start_date
3102 ,g_end_date
3103 ,pay_au_foreign_workers.g_fw_j_type);
3104
3105 if l_fw_f_type = 'Y' then
3106 pay_au_foreign_workers.get_foreign_worker_dates(p_assignment_id
3107 ,g_tax_unit_id
3108 ,g_start_date
3109 ,g_end_date
3110 ,pay_au_foreign_workers.g_fw_f_type
3111 ,f_fw_date_tab_g);
3112 end if;
3113
3114 if l_fw_j_type = 'Y' then
3115 pay_au_foreign_workers.get_foreign_worker_dates(p_assignment_id
3116 ,g_tax_unit_id
3117 ,g_start_date
3118 ,g_end_date
3119 ,pay_au_foreign_workers.g_fw_j_type
3120 ,j_fw_date_tab_g);
3121 end if;
3122
3123 if f_fw_date_tab_g.count > 0 then
3124 t_fw_gross_type(1) := 'F';
3125 if j_fw_date_tab_g.count >0 then
3126 t_fw_gross_type(2) := 'J';
3127 end if;
3128 elsif j_fw_date_tab_g.count > 0 then
3129 t_fw_gross_type(1) := 'J';
3130 end if;
3131
3132 For i_idx in t_fw_gross_type.first .. t_fw_gross_type.last loop
3133
3134 g_fw_result_alw_table.delete;
3135 IF t_fw_gross_type(i_idx) = 'F' THEN
3136 pay_au_foreign_workers.get_foreign_payment_amounts(p_assignment_id,
3137 g_tax_unit_id,
3138 f_fw_date_tab_g,
3139 g_start_date,
3140 g_end_date,
3141 pay_au_foreign_workers.g_fw_f_type,
3142 g_fw_input_alw_table,
3143 g_fw_result_alw_table) ;
3144 ELSIF t_fw_gross_type(i_idx) = 'J' THEN
3145 pay_au_foreign_workers.get_foreign_payment_amounts(p_assignment_id,
3146 g_tax_unit_id,
3147 j_fw_date_tab_g,
3148 g_start_date,
3149 g_end_date,
3150 pay_au_foreign_workers.g_fw_j_type,
3151 g_fw_input_alw_table,
3152 g_fw_result_alw_table) ;
3153 END IF;
3154
3155
3156 if g_fw_result_alw_table.count > 0 then
3157
3158 p_fw_allowance_tab.delete;
3159 cnt := 1;
3160 for i in g_fw_result_alw_table.first .. g_fw_result_alw_table.last loop
3161 if g_fw_result_alw_table(i).balance_value > 0 then
3162
3163 for alw_rec in get_balance_name(g_fw_result_alw_table(i).defined_balance_id) loop
3164 p_fw_allowance_tab(cnt).balance_name := alw_rec.balance_name;
3165 p_fw_allowance_tab(cnt).balance_value := g_fw_result_alw_table(i).balance_value;
3166 cnt := cnt + 1;
3167 end loop;
3168
3169 end if;
3170 end loop;
3171
3172 if p_fw_allowance_tab.count >0 then
3173
3174 IF t_fw_gross_type(i_idx) = 'F' THEN
3175 for i in f_fw_date_tab_g.FIRST .. f_fw_date_tab_g.LAST loop
3176 x := pay_au_payment_summary.adjust_retro_allowances
3177 (p_fw_allowance_tab
3178 ,f_fw_date_tab_g(i).start_date
3179 ,f_fw_date_tab_g(i).end_date
3180 ,p_assignment_id
3181 ,g_tax_unit_id
3182 );
3183 end loop;
3184 ELSIF t_fw_gross_type(i_idx) = 'J' THEN
3185 for i in j_fw_date_tab_g.FIRST ..j_fw_date_tab_g.LAST loop
3186 x := pay_au_payment_summary.adjust_retro_allowances
3187 (p_fw_allowance_tab
3188 ,j_fw_date_tab_g(i).start_date
3189 ,j_fw_date_tab_g(i).end_date
3190 ,p_assignment_id
3191 ,g_tax_unit_id
3192 );
3193 end loop;
3194 END IF;
3195
3196 for i in p_fw_allowance_tab.first .. p_fw_allowance_tab.last loop
3197 g_fw_alw_total := g_fw_alw_total + p_fw_allowance_tab(i).balance_value;
3198 end loop;
3199
3200 end if;
3201
3202 end if;
3203
3204 end loop;
3205 end if;
3206 /*Changes for 9147438 end */
3207
3208 if g_debug then
3209 hr_utility.set_location('Set the Global Balance Value '||g_allowance_balance_value,700);
3210 hr_utility.set_location('Set the Global FW Balance Value '||g_fw_alw_total,700);
3211 hr_utility.set_location('Leaving '||l_procedure,700);
3212 end if;
3213 end;
3214
3215 --------------------------------------------------------------------+
3216 -- Name : Archive_balance_details
3217 -- Type : Procedure
3218 -- Access: Public
3219 -- Computes and Archives all the Balances in table
3220 -- pay_action_information with context,
3221 -- Normal Balances - AU_BALANCE_RECON_DETAILS_YTD
3222 -- Payment Summary Balances - AU_PS_BALANCE_RECON_DETAILS
3223 --------------------------------------------------------------------+
3224
3225 procedure archive_balance_details(p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE
3226 ,p_assignment_id in pay_assignment_actions.assignment_id%TYPE
3227 ,p_effective_date in date
3228 ,p_pre01jul1983_ratio in number
3229 ,p_post30jun1983_ratio in number
3230 ,p_run_action_sequence in pay_assignment_actions.action_sequence%type)
3231 is
3232 l_procedure varchar2(80);
3233
3234 l_YTD_TAXABLE_EARNINGS number;
3235 l_YTD_NON_TAXABLE_EARNINGS number;
3236 l_YTD_GROSS_EARNINGS number;
3237 l_YTD_PRE_TAX_DEDUCTIONS number;
3238 l_YTD_DIRECT_PAYMENTS number;
3239 l_YTD_DEDUCTIONS number;
3240 l_YTD_TAX number;
3241 l_YTD_NET_PAYMENT number;
3242 l_YTD_EMPLOYER_CHARGES number;
3243 l_YTD_PAYSUM_GROSS number;
3244 l_YTD_LUMPSUM_PAY number ;
3245 l_YTD_ALLOWANCE number;
3246 l_YTD_RFB number;
3247 l_ETP_PAY number;
3248 l_ASSESSABLE_ETP number;
3249 l_YTD_CDEP number;
3250 l_YTD_UNION_FEES number;
3251 l_YTD_WORKPLACE_GIVING_DED number; /*4085496 */
3252 /* Begin 8587013 */
3253 l_YTD_RESC number;
3254 l_YTD_FOREIGN_INCOME number;
3255 /* End 8587013 */
3256
3257 /* Changes for 9147438 start */
3258 l_YTD_FW_PAYSUM_GROSS number;
3259 l_YTD_FW_LUMPSUM_PAY number;
3260 l_YTD_FW_ALLOWANCE number;
3261 l_YTD_FW_RFB number;
3262 l_YTD_FW_TOTAL_TAX number;
3263 l_YTD_FW_FOREIGN_TAX number;
3264 l_YTD_FW_CDEP number;
3265 l_YTD_FW_UNION_FEES number;
3266 l_YTD_FW_WORKPLACE_GIVING_DED number;
3267 l_YTD_FW_RESC number;
3268 /* Changes for 9147438 end */
3269
3270 begin
3271
3272 g_debug := hr_utility.debug_enabled;
3273 if g_debug then
3274 l_procedure := 'archive_balance_details';
3275 hr_utility.set_location('In procedure '||g_package||l_procedure,800);
3276 end if;
3277 /*
3278 ===============================================================================================
3279 Balances for Archive are computed in the following manner,
3280
3281 l_YTD_GROSS_EARNINGS = Earnings Total + Termination Payments + Pre Tax Deductions
3282 l_YTD_TAXABLE_EARNINGS = Gross Earnings - Pre_tax_deductions - Earnings_non_taxable
3283 l_YTD_NON_TAXABLE_EARNINGS = Earnings_non_taxable
3284 l_YTD_PRE_TAX_DEDUCTIONS = Pre_tax_deductions
3285 l_YTD_DIRECT_PAYMENTS = Direct_Payments
3286 l_YTD_DEDUCTIONS = Involuntary_deductions + Voluntary_deductions
3287 l_YTD_TAX = Tax_deductions + Termination_deductions
3288 l_YTD_NET_PAYMENT = Taxable_earnings + Non_taxable_earnings - Tax - Deductions + Direct_Payments
3289 l_YTD_EMPLOYER_CHARGES = Employer_charges
3290
3291 l_YTD_PAYSUM_GROSS = Earnings_Total + Leave Payments Marginal - (All Allowance) - CDEP - Lump Sum E Payments + Workplace Giving Deductions. - 4085496
3292 - Exempt Foreign Employment Income bug#10143762
3293 l_YTD_LUMPSUM_PAY = Lump Sum A Payments + Lump Sum B Payments + Lump Sum D Payments
3294 + Lump Sum E Payments
3295 l_YTD_ALLOWANCE = Allowances YTD
3296 l_YTD_RFB = Fringe Benefits /(1 - (FBT Rate + Med Levy))
3297 l_ETP_PAY = (Lump Sum C Payments + p_pre01jul1983_ratio) +
3298 (Lump Sum C Payments + p_post30jun1983_ratio) +
3299 Invalidity Payments
3300 l_ASSESSABLE_ETP = (Lump Sum C Payments + p_post30jun1983_ratio) +
3301 l_YTD_CDEP = CDEP
3302 l_YTD_UNION_FEES = Union Fees
3303 l_YTD_WORKPLACE_GIVING_DED = Workplace Giving Deductions - 4085496
3304 l_YTD_RESC = Reportable Employer Superannuation Contributions - 8587013
3305 l_YTD_FOREIGN_INCOME = Exempt Foreign Employment Income - 8587013
3306 ===============================================================================================
3307 */
3308
3309
3310 l_YTD_GROSS_EARNINGS := g_result_table(1).balance_value + g_result_table(3).balance_value + g_result_table(5).balance_value;
3311 l_YTD_TAXABLE_EARNINGS := l_YTD_GROSS_EARNINGS - g_result_table(5).balance_value - g_result_table(9).balance_value ;
3312 l_YTD_NON_TAXABLE_EARNINGS := g_result_table(9).balance_value;
3313 l_YTD_PRE_TAX_DEDUCTIONS := g_result_table(5).balance_value;
3314 l_YTD_DIRECT_PAYMENTS := g_result_table(2).balance_value;
3315 l_YTD_DEDUCTIONS := g_result_table(4).balance_value + g_result_table(7).balance_value ;
3316 l_YTD_TAX := g_result_table(8).balance_value + g_result_table(6).balance_value ;
3317 l_YTD_NET_PAYMENT := l_YTD_TAXABLE_EARNINGS + l_YTD_NON_TAXABLE_EARNINGS - l_YTD_TAX - l_YTD_DEDUCTIONS + l_YTD_DIRECT_PAYMENTS ;
3318 l_YTD_EMPLOYER_CHARGES := g_result_table(10).balance_value ;
3319
3320 /* Changes for 9147438 start */
3321
3322 l_YTD_FW_PAYSUM_GROSS := g_fw_earnings - g_fw_alw_total - g_fw_cdep - g_fw_lump_sumE;
3323 l_YTD_FW_TOTAL_TAX := g_fw_Total_Tax;
3324 l_YTD_FW_FOREIGN_TAX := g_fw_Foreign_Tax;
3325 l_YTD_FW_CDEP := g_fw_cdep;
3326 l_YTD_FW_UNION_FEES := g_fw_union;
3327 l_YTD_FW_WORKPLACE_GIVING_DED := g_fw_workplace;
3328 l_YTD_FW_ALLOWANCE := g_fw_alw_total;
3329
3330
3331 if g_payg_fw_type in ('PF','P') then
3332 l_YTD_FW_LUMPSUM_PAY := g_fw_lump_sumA + g_fw_lump_sumE;
3333 l_YTD_FW_RFB :=0;
3334 l_YTD_FW_RESC :=0;
3335 else
3336 l_YTD_FW_LUMPSUM_PAY := g_fw_lump_sumA + g_fw_lump_sumD + g_fw_lump_sumE;
3337 l_YTD_FW_RFB := g_fw_reporting_fbt;
3338 l_YTD_FW_RESC := g_fw_resc;
3339 end if;
3340
3341
3342 /* Changes for 9147438 end */
3343
3344 l_YTD_PAYSUM_GROSS := g_result_table(1).balance_value + g_result_table(18).balance_value
3345 + g_result_table(21).balance_value
3346 - g_result_table(22).balance_value --bug#10143762
3347 - g_allowance_balance_value
3348 - g_result_table(17).balance_value
3349 - g_result_table(15).balance_value - l_YTD_FW_PAYSUM_GROSS ;
3350
3351 l_YTD_LUMPSUM_PAY := g_result_table(11).balance_value + g_result_table(12).balance_value + g_result_table(14).balance_value
3352 + g_result_table(15).balance_value - l_YTD_FW_LUMPSUM_PAY;
3353
3354 l_YTD_ALLOWANCE := g_allowance_balance_value - l_YTD_FW_ALLOWANCE;
3355 l_YTD_RFB := g_fbt_balance_value - l_YTD_FW_RFB;
3356
3357 l_ETP_PAY := (g_result_table(13).balance_value * p_pre01jul1983_ratio)
3358 +(g_result_table(13).balance_value * p_post30jun1983_ratio)
3359 + g_result_table(16).balance_value;
3360
3361 /* Bug 8769345 - The Assessible ETP value will be sum of all the ETP Taxable balances */
3362 /* Bug 9226023 - The Assessible ETP value will be the taxable etp stored in global value */
3363 l_ASSESSABLE_ETP := round(g_taxable_etp,2); /* Bug 4872594 - Added round off */
3364
3365 /* Bug No : 7030285 - Assessable Income modified */
3366 l_YTD_CDEP := g_result_table(17).balance_value - l_YTD_FW_CDEP;
3367 l_YTD_UNION_FEES := g_result_table(20).balance_value - l_YTD_FW_UNION_FEES;
3368 l_YTD_WORKPLACE_GIVING_DED := g_result_table(21).balance_value - l_YTD_FW_WORKPLACE_GIVING_DED; /* 4085496 */
3369
3370 /* Begin 8587013 - Added code to hold the values of RESC and Exempt Foreign Employment Income balances*/
3371 l_YTD_RESC := g_result_table(19).balance_value - l_YTD_FW_RESC;
3372 l_YTD_FOREIGN_INCOME := g_result_table(22).balance_value;
3373 /* End 8587013 */
3374
3375
3376 insert into pay_action_information (
3377 action_information_id,
3378 action_context_id,
3379 action_context_type,
3380 effective_date,
3381 source_id,
3382 tax_unit_id,
3383 assignment_id,
3384 action_information_category,
3385 action_information1,
3386 action_information2,
3387 action_information3,
3388 action_information4,
3389 action_information5,
3390 action_information6,
3391 action_information7,
3392 action_information8,
3393 action_information9,
3394 action_information10
3395 )
3396 values (
3397 pay_action_information_s.nextval,
3398 p_assignment_action_id,
3399 'AAP',
3400 p_effective_date,
3401 null,
3402 g_tax_unit_id,
3403 p_assignment_id,
3404 'AU_BALANCE_RECON_DETAILS_YTD',
3405 l_YTD_TAXABLE_EARNINGS,
3406 l_YTD_NON_TAXABLE_EARNINGS,
3407 l_YTD_DEDUCTIONS,
3408 l_YTD_TAX,
3409 l_YTD_NET_PAYMENT,
3410 l_YTD_EMPLOYER_CHARGES,
3411 l_YTD_GROSS_EARNINGS,
3412 l_YTD_PRE_TAX_DEDUCTIONS,
3413 l_YTD_DIRECT_PAYMENTS,
3414 p_run_action_sequence);
3415
3416 /*Bug 8587013 - The balances RESC and Exempt Foreign Employment Income are inserted into next available two columns
3417 of PAY_ACTION_INFORMATION table and the Other Income balance is removed from archival*/
3418
3419 insert into pay_action_information (
3420 action_information_id,
3421 action_context_id,
3422 action_context_type,
3423 effective_date,
3424 source_id,
3425 tax_unit_id,
3426 assignment_id,
3427 action_information_category,
3428 action_information1,
3429 action_information2,
3430 action_information3,
3431 action_information4,
3432 action_information5,
3433 action_information6,
3434 action_information7,
3435 action_information9,
3436 action_information10,
3437 action_information11,
3438 action_information12,
3439 action_information13
3440 )
3441 values (
3442 pay_action_information_s.nextval,
3443 p_assignment_action_id,
3444 'AAP',
3445 p_effective_date,
3446 null,
3447 g_tax_unit_id,
3448 p_assignment_id,
3449 'AU_PS_BALANCE_RECON_DETAILS',
3450 l_YTD_PAYSUM_GROSS,
3451 l_YTD_LUMPSUM_PAY,
3452 l_YTD_ALLOWANCE,
3453 l_YTD_RFB,
3454 l_ETP_PAY,
3455 l_ASSESSABLE_ETP,
3456 l_YTD_CDEP,
3457 l_YTD_UNION_FEES,
3458 p_run_action_sequence,
3459 L_YTD_WORKPLACE_GIVING_DED,
3460 l_YTD_RESC,
3461 l_YTD_FOREIGN_INCOME
3462
3463 ); /* 4015082 , 8587013 */
3464
3465 /* Changes for 9147438 start */
3466 insert into pay_action_information (
3467 action_information_id,
3468 action_context_id,
3469 action_context_type,
3470 effective_date,
3471 source_id,
3472 tax_unit_id,
3473 assignment_id,
3474 action_information_category,
3475 action_information1,
3476 action_information2,
3477 action_information3,
3478 action_information4,
3479 action_information5,
3480 action_information6,
3481 action_information7,
3482 action_information8,
3483 action_information9,
3484 action_information10,
3485 action_information11,
3486 action_information12
3487 )
3488 values (
3489 pay_action_information_s.nextval,
3490 p_assignment_action_id,
3491 'AAP',
3492 p_effective_date,
3493 null,
3494 g_tax_unit_id,
3495 p_assignment_id,
3496 'AU_FWPS_BALANCE_RECON_DETAILS',
3497 l_YTD_FW_PAYSUM_GROSS,
3498 l_YTD_FW_TOTAL_TAX,
3499 l_YTD_FW_FOREIGN_TAX,
3500 l_YTD_FW_LUMPSUM_PAY,
3501 l_YTD_FW_ALLOWANCE,
3502 l_YTD_FW_RFB,
3503 l_YTD_FW_CDEP,
3504 l_YTD_FW_UNION_FEES,
3505 l_YTD_FW_WORKPLACE_GIVING_DED,
3506 l_YTD_FW_RESC,
3507 p_run_action_sequence,
3508 g_fw_gross_type
3509 );
3510 /* Changes for 9147438 end */
3511 if g_debug then
3512 hr_utility.set_location('Leaving '||g_package||l_procedure,800);
3513 end if;
3514
3515 end archive_balance_details;
3516
3517 --------------------------------------------------------------------+
3518 -- Name : spawn_archive_reports
3519 -- Type : Procedure
3520 -- Access: Public
3521 -- This procedure calls the Detail report
3522 -- Using the parameters passed, this proc calls the Reconciliation
3523 -- Detail report.
3524 -- This proc is called as deinitialization code of archive process.
3525
3526 --------------------------------------------------------------------+
3527
3528 procedure spawn_archive_reports
3529 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type)
3530 is
3531 l_count number;
3532 ps_request_id NUMBER;
3533 l_print_style VARCHAR2(2);
3534 l_print_together VARCHAR2(80);
3535 l_print_return BOOLEAN;
3536 l_procedure varchar2(50);
3537 l_short_report_name VARCHAR2(30); /* 6839263 */
3538 l_xml_options BOOLEAN ; /* 6839263 */
3539 --------------------------------------------------------------------+
3540 -- Cursor : csr_params
3541 -- Description : Fetches User Parameters from Legislative_paramters
3542 -- column.
3543 --------------------------------------------------------------------+
3544
3545 CURSOR csr_report_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
3546 IS
3547 select pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters) business_group_id
3548 ,pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters) legal_employer
3549 ,pay_core_utils.get_parameter('PAYROLL',legislative_parameters) payroll_id
3550 ,pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters) assignment_id
3551 ,pay_core_utils.get_parameter('EMPLOYEE_TYPE',legislative_parameters) employee_type
3552 ,to_date('01-07-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fin_year_start_date
3553 ,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),6,4),'DD-MM-YYYY') fin_year_end_date
3554 ,to_date('01-04-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_start_date
3555 ,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_end_date
3556 ,pay_core_utils.get_parameter('LST_YR_TERM',legislative_parameters) lst_year_term
3557 ,pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions /*Bug 4142159*/
3558 ,decode(pay_core_utils.get_parameter('REP_MODE',legislative_parameters),'SUMM','S','D') report_mode /* Bug 3891577*/
3559 ,pay_core_utils.get_parameter('OUTPUT_TYPE',legislative_parameters)p_output_type
3560 from pay_payroll_actions
3561 where payroll_action_id = c_payroll_action_id;
3562
3563
3564 cursor csr_get_print_options(p_payroll_action_id NUMBER) IS
3565 SELECT printer,
3566 print_style,
3567 decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output
3568 ,number_of_copies /* Bug 4116833 */
3569 FROM pay_payroll_actions pact,
3570 fnd_concurrent_requests fcr
3571 WHERE fcr.request_id = pact.request_id
3572 AND pact.payroll_action_id = p_payroll_action_id;
3573
3574 /* Declaration - Report Flags to be SET */
3575 l_paysum_flag varchar2(2);
3576 l_ytd_totals varchar2(2);
3577 l_negative_records varchar2(2);
3578 l_zero_records varchar2(2);
3579
3580 l_dummy varchar2(240);
3581
3582 rec_print_options csr_get_print_options%ROWTYPE;
3583
3584 l_parameters csr_report_params%ROWTYPE; /* Bug 6839263 */
3585
3586 begin
3587 l_count :=0;
3588 ps_request_id :=-1;
3589 g_debug :=hr_utility.debug_enabled ;
3590
3591 if g_debug then
3592 l_procedure := g_package||' spawn_archive_reports';
3593 hr_utility.set_location('Entering '||l_procedure,999);
3594 end if;
3595
3596 -- Set User Parameters for Report.
3597
3598 open csr_report_params(p_payroll_action_id);
3599 fetch csr_report_params into l_parameters;
3600 close csr_report_params;
3601
3602 /* Start Bug 6839263 */
3603 IF l_parameters.p_output_type = 'XML_PDF'
3604 THEN
3605 l_short_report_name := 'PYAUREPSR_XML';
3606
3607 l_xml_options := fnd_request.add_layout
3608 (template_appl_name => 'PAY',
3609 template_code => 'PYAUREPSR_XML',
3610 template_language => 'en',
3611 template_territory => 'US',
3612 output_format => 'PDF');
3613
3614 ELSE
3615 l_short_report_name := 'PYAUREPSR';
3616 END IF;
3617 /* End Bug 6839263 */
3618
3619
3620 --Set REPORT FLAGS values.
3621 l_paysum_flag := 'Y'; /*Indicate Payment Summary Report*/
3622 l_ytd_totals := 'Y'; /* YTD Balances to be displayed */
3623 l_negative_records := 'N'; /* Do not Suppress Records with Negative Earnings */
3624 l_zero_records:= 'N'; /* Do not Suppress Records with Zero Earnings */
3625
3626 if g_debug then
3627 hr_utility.set_location('payroll_parameters.action '||p_payroll_action_id,900);
3628 hr_utility.set_location('in BG_ID '||l_parameters.business_group_id,901);
3629 hr_utility.set_location('in payroll_parameters.id '||l_parameters.payroll_id,903);
3630 hr_utility.set_location('in asg_id '||l_parameters.assignment_id,904);
3631 hr_utility.set_location('in legal employer '||l_parameters.legal_employer,908);
3632 hr_utility.set_location('in YTD totals '||l_ytd_totals,910);
3633 hr_utility.set_location('in zero records'||l_zero_records,911);
3634 hr_utility.set_location('in Negative records'||l_negative_records,912);
3635 hr_utility.set_location('in emp_type '||l_parameters.employee_type,914);
3636 hr_utility.set_location('In Start Date '||l_parameters.fin_year_start_date,916);
3637 hr_utility.set_location('In End Date '||l_parameters.fin_year_end_date,917);
3638 hr_utility.set_location('In Last Year Term'||l_parameters.lst_year_term,918);
3639 hr_utility.set_location('In Delete Actions'||l_parameters.delete_actions,919); /*Bug 4142159*/
3640 hr_utility.set_location('In Output Type '||l_parameters.p_output_type,920);
3641 end if;
3642
3643 if g_debug then
3644 hr_utility.set_location('Afer payroll action ' || p_payroll_action_id , 900);
3645 hr_utility.set_location('Before calling report',900);
3646 end if;
3647
3648 OPEN csr_get_print_options(p_payroll_action_id);
3649 FETCH csr_get_print_options INTO rec_print_options;
3650 CLOSE csr_get_print_options;
3651 --
3652 l_print_together := nvl(fnd_profile.value('CONC_PRINT_TOGETHER'), 'N');
3653 --
3654 -- Set printer options
3655 l_print_return := fnd_request.set_print_options
3656 (printer => rec_print_options.printer,
3657 style => rec_print_options.print_style,
3658 copies => rec_print_options.number_of_copies, /*Bug 4116833 */
3659 save_output => hr_general.char_to_bool(rec_print_options.save_output),
3660 print_together => l_print_together);
3661 -- Submit report
3662 if g_debug then
3663 hr_utility.set_location('payroll_action id '|| p_payroll_action_id,900);
3664 end if;
3665
3666 ps_request_id := fnd_request.submit_request
3667 ('PAY',
3668 l_short_report_name, /* Bug 6839263 */
3669 null,
3670 null,
3671 false,
3672 'P_PAYROLL_ACTION_ID='||to_char(p_payroll_action_id),
3673 'P_BUSINESS_GROUP_ID='||to_char(l_parameters.business_group_id),
3674 'P_ORGANIZATION_ID='||l_dummy,
3675 'P_PAYROLL_ID='||l_parameters.payroll_id, /* Bug 4353285 removed the to_char */
3676 'P_REGISTERED_EMPLOYER='||to_char(l_parameters.legal_employer),
3677 'P_ASSIGNMENT_ID='||l_parameters.assignment_id, /* Bug 4353285 removed the to_char */
3678 'P_START_DATE='||to_char(l_parameters.fin_year_start_date,'YYYY/MM/DD'),
3679 'P_END_DATE='||to_char(l_parameters.fin_year_end_date,'YYYY/MM/DD'),
3680 'P_PAYROLL_RUN_ID='||l_dummy,
3681 'P_PERIOD_END_DATE='||l_dummy,
3682 'P_EMPLOYEE_TYPE='||l_parameters.employee_type,
3683 'P_YTD_TOTALS='||l_ytd_totals,
3684 'P_ZERO_RECORDS='||l_zero_records,
3685 'P_NEGATIVE_RECORDS='||l_negative_records,
3686 'P_SORT_ORDER_1='||l_dummy,
3687 'P_SORT_ORDER_2='||l_dummy,
3688 'P_SORT_ORDER_3='||l_dummy,
3689 'P_SORT_ORDER_4='||l_dummy,
3690 'P_PAYSUM_FLAG='||l_paysum_flag,
3691 'P_LST_YEAR_TERM='||l_parameters.lst_year_term,
3692 'P_DELETE_ACTIONS='||l_parameters.delete_actions, /*Bug 4142159*/
3693 'BLANKPAGES=NO',
3694 NULL, NULL, NULL, NULL, NULL, NULL,
3695 NULL, NULL, NULL, NULL, NULL, NULL,
3696 NULL, NULL, NULL, NULL, NULL, NULL,
3697 NULL, NULL, NULL, NULL, NULL, NULL,
3698 NULL, NULL, NULL, NULL, NULL, NULL,
3699 NULL, NULL, NULL, NULL, NULL, NULL,
3700 NULL, NULL, NULL, NULL, NULL, NULL,
3701 NULL, NULL, NULL, NULL, NULL, NULL,
3702 NULL, NULL, NULL, NULL, NULL, NULL,
3703 NULL, NULL, NULL, NULL, NULL, NULL,
3704 NULL, NULL, NULL, NULL, NULL, NULL,
3705 NULL, NULL, NULL, NULL, NULL, NULL,
3706 NULL, NULL, NULL, NULL
3707 );
3708 if g_debug then
3709 hr_utility.set_location('After calling report',900);
3710 end if;
3711
3712 end;
3713
3714 procedure spawn_summary_reports
3715 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type)
3716 is
3717 l_count number;
3718 ps_request_id NUMBER;
3719 l_print_style VARCHAR2(2);
3720 l_print_together VARCHAR2(80);
3721 l_print_return BOOLEAN;
3722 l_procedure varchar2(100); /*bug 14703826*/
3723
3724 --------------------------------------------------------------------+
3725 -- Cursor : csr_params
3726 -- Description : Fetches User Parameters from Legislative_paramters
3727 -- column.
3728 --------------------------------------------------------------------+
3729
3730 CURSOR csr_report_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
3731 IS
3732 select pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters) business_group_id
3733 ,pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters) legal_employer
3734 ,pay_core_utils.get_parameter('PAYROLL',legislative_parameters) payroll_id
3735 ,pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters) assignment_id
3736 ,pay_core_utils.get_parameter('EMPLOYEE_TYPE',legislative_parameters) employee_type
3737 ,to_date('01-07-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fin_year_start_date
3738 ,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),6,4),'DD-MM-YYYY') fin_year_end_date
3739 ,to_date('01-04-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_start_date
3740 ,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_end_date
3741 ,pay_core_utils.get_parameter('LST_YR_TERM',legislative_parameters) lst_year_term
3742 ,pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions
3743 ,decode(pay_core_utils.get_parameter('REP_MODE',legislative_parameters),'SUMM','S','D') report_mode
3744 from pay_payroll_actions
3745 where payroll_action_id = c_payroll_action_id;
3746
3747
3748 cursor csr_get_print_options(p_payroll_action_id NUMBER) IS
3749 SELECT printer,
3750 print_style,
3751 decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output
3752 ,number_of_copies
3753 FROM pay_payroll_actions pact,
3754 fnd_concurrent_requests fcr
3755 WHERE fcr.request_id = pact.request_id
3756 AND pact.payroll_action_id = p_payroll_action_id;
3757
3758 /* Declaration - Report Flags to be SET */
3759 l_paysum_flag varchar2(2);
3760 l_ytd_totals varchar2(2);
3761 l_negative_records varchar2(2);
3762 l_zero_records varchar2(2);
3763
3764 l_dummy varchar2(240);
3765
3766 rec_print_options csr_get_print_options%ROWTYPE;
3767
3768 l_parameters csr_report_params%ROWTYPE;
3769
3770 begin
3771 l_count :=0;
3772 ps_request_id :=-1;
3773 g_debug :=hr_utility.debug_enabled ;
3774
3775 if g_debug then
3776 l_procedure := g_package||' spawn_summary_archive_reports';
3777 hr_utility.set_location('Entering '||l_procedure,999);
3778 end if;
3779
3780 -- Set User Parameters for Report.
3781
3782 open csr_report_params(p_payroll_action_id);
3783 fetch csr_report_params into l_parameters;
3784 close csr_report_params;
3785
3786 --Set REPORT FLAGS values.
3787 l_paysum_flag := 'Y'; /*Indicate Payment Summary Report*/
3788 l_ytd_totals := 'Y'; /* YTD Balances to be displayed */
3789 l_negative_records := 'N'; /* Do not Suppress Records with Negative Earnings */
3790 l_zero_records:= 'N'; /* Do not Suppress Records with Zero Earnings */
3791
3792 if g_debug then
3793 hr_utility.set_location('payroll_parameters.action '||p_payroll_action_id,900);
3794 hr_utility.set_location('in BG_ID '||l_parameters.business_group_id,901);
3795 hr_utility.set_location('in payroll_parameters.id '||l_parameters.payroll_id,903);
3796 hr_utility.set_location('in asg_id '||l_parameters.assignment_id,904);
3797 hr_utility.set_location('in legal employer '||l_parameters.legal_employer,908);
3798 hr_utility.set_location('in YTD totals '||l_ytd_totals,910);
3799 hr_utility.set_location('in zero records'||l_zero_records,911);
3800 hr_utility.set_location('in Negative records'||l_negative_records,912);
3801 hr_utility.set_location('in emp_type '||l_parameters.employee_type,914);
3802 hr_utility.set_location('In Start Date '||l_parameters.fin_year_start_date,916);
3803 hr_utility.set_location('In End Date '||l_parameters.fin_year_end_date,917);
3804 hr_utility.set_location('In Last Year Term'||l_parameters.lst_year_term,918);
3805 hr_utility.set_location('In Delete Actions'||l_parameters.delete_actions,919);
3806 hr_utility.set_location('In Report Mode'||l_parameters.report_mode,920);
3807 end if;
3808
3809 if g_debug then
3810 hr_utility.set_location('Afer payroll action ' || p_payroll_action_id , 900);
3811 hr_utility.set_location('Before calling report',900);
3812 end if;
3813
3814 OPEN csr_get_print_options(p_payroll_action_id);
3815 FETCH csr_get_print_options INTO rec_print_options;
3816 CLOSE csr_get_print_options;
3817 --
3818 l_print_together := nvl(fnd_profile.value('CONC_PRINT_TOGETHER'), 'N');
3819 --
3820 -- Set printer options
3821 l_print_return := fnd_request.set_print_options
3822 (printer => rec_print_options.printer,
3823 style => rec_print_options.print_style,
3824 copies => rec_print_options.number_of_copies, /*Bug 4116833 */
3825 save_output => hr_general.char_to_bool(rec_print_options.save_output),
3826 print_together => l_print_together);
3827 -- Submit report
3828 if g_debug then
3829 hr_utility.set_location('payroll_action id '|| p_payroll_action_id,900);
3830 end if;
3831
3832 ps_request_id := fnd_request.submit_request
3833 ('PAY',
3834 'PYAUPSSAR',
3835 null,
3836 null,
3837 false,
3838 'P_PAYROLL_ACTION_ID='||to_char(p_payroll_action_id),
3839 'P_BUSINESS_GROUP_ID='||l_parameters.business_group_id,
3840 'P_ORGANIZATION_ID='||l_dummy,
3841 'P_PAYROLL_ID='||l_parameters.payroll_id,
3842 'P_REGISTERED_EMPLOYER='||l_parameters.legal_employer,
3843 'P_ASSIGNMENT_ID='||l_parameters.assignment_id,
3844 'P_START_DATE='||to_char(l_parameters.fin_year_start_date,'YYYY/MM/DD'),
3845 'P_END_DATE='||to_char(l_parameters.fin_year_end_date,'YYYY/MM/DD'),
3846 'P_PAYROLL_RUN_ID='||l_dummy,
3847 'P_PERIOD_END_DATE='||l_dummy,
3848 'P_EMPLOYEE_TYPE='||l_parameters.employee_type,
3849 'P_YTD_TOTALS='||l_ytd_totals,
3850 'P_ZERO_RECORDS='||l_zero_records,
3851 'P_NEGATIVE_RECORDS='||l_negative_records,
3852 'P_SORT_ORDER_1='||l_dummy,
3853 'P_SORT_ORDER_2='||l_dummy,
3854 'P_SORT_ORDER_3='||l_dummy,
3855 'P_SORT_ORDER_4='||l_dummy,
3856 'P_PAYSUM_FLAG='||l_paysum_flag,
3857 'P_LST_YEAR_TERM='||l_parameters.lst_year_term,
3858 'P_DELETE_ACTIONS='||l_parameters.delete_actions, /*Bug 4142159*/
3859 'BLANKPAGES=NO',
3860 NULL, NULL, NULL, NULL, NULL, NULL,
3861 NULL, NULL, NULL, NULL, NULL, NULL,
3862 NULL, NULL, NULL, NULL, NULL, NULL,
3863 NULL, NULL, NULL, NULL, NULL, NULL,
3864 NULL, NULL, NULL, NULL, NULL, NULL,
3865 NULL, NULL, NULL, NULL, NULL, NULL,
3866 NULL, NULL, NULL, NULL, NULL, NULL,
3867 NULL, NULL, NULL, NULL, NULL, NULL,
3868 NULL, NULL, NULL, NULL, NULL, NULL,
3869 NULL, NULL, NULL, NULL, NULL, NULL,
3870 NULL, NULL, NULL, NULL, NULL, NULL,
3871 NULL, NULL, NULL, NULL, NULL, NULL,
3872 NULL, NULL, NULL, NULL
3873 );
3874 if g_debug then
3875 hr_utility.set_location('After calling report',900);
3876 end if;
3877
3878 end spawn_summary_reports;
3879
3880
3881 end pay_au_rec_det_paysum_mode;