[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.22.12010000.6 2008/08/06 06:54:52 ubhat 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 *** ------------------------------------------------------------------------+
61 */
62 /* Package - Functional comments.
63 This package is used to archive data in pay_action_information for TWO reports,
64 i. End of Year Reconciliation Detail Report (Bug 3899641)
65 ii. End of Year Reconciliation Summary Report (Bug 3891577)
66
67 Parameter REP_MODE in legislative parameters is used to distinguish the type of Report.
68 For Detail report - REP_MODE returns NULL
69 For Summary report - REP_MODE returns 'SUMM'
70 Based on the type of report,appropriate data is archived.
71
72 Any changes made to this package must be Functionally/Technically tested against both reports.
73 */
74 g_arc_payroll_action_id pay_payroll_actions.payroll_action_id%type;
75 g_business_group_id hr_all_organization_units.organization_id%type;
76
77 g_debug boolean ;
78
79 g_package constant varchar2(60) := 'pay_au_rec_det_paysum_mode.'; -- Global to store package name for tracing.
80
81 g_end_date date;
82 g_start_date date;
83 g_tax_unit_id pay_assignment_actions.tax_unit_id%type;
84
85 g_balance_type_tab g_bal_type_tab; /* TO Store the Allowances Balance Type IDs */
86 g_fbt_threshold ff_globals_f.global_value%TYPE ; /* Bug 5708255 */
87
88 --------------------------------------------------------------------
89 -- Name : range_code
90 -- Type : Proedure
91 -- Access: Public
92 -- This procedure returns a sql string to select a range
93 -- of assignments eligible for archival.
94 --
95 --------------------------------------------------------------------
96
97 procedure range_code
98 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type
99 ,p_sql out NOCOPY varchar2
100 ) is
101
102 l_procedure varchar2(200) ;
103
104 begin
105
106 g_debug :=hr_utility.debug_enabled ;
107
108 if g_debug then
109 l_procedure := g_package||'range_code';
110 hr_utility.set_location('Entering '||l_procedure,1);
111 end if ;
112
113 -- Archive the payroll action level data and EIT defintions.
114 -- sql string to SELECT a range of assignments eligible for archival.
115 p_sql := ' select distinct p.person_id' ||
116 ' from per_people_f p,' ||
117 ' pay_payroll_actions pa' ||
118 ' where pa.payroll_action_id = :payroll_action_id' ||
119 ' and p.business_group_id = pa.business_group_id' ||
120 ' order by p.person_id';
121
122 if g_debug then
123 hr_utility.set_location('Leaving '||l_procedure,1000);
124 end if;
125
126 end range_code;
127
128 --------------------------------------------------------------------
129 -- Name : initialization_code
130 -- Type : Proedure
131 -- Access: Public
132 -- This procedure builds a PL/SQL table with Defined Balance ID's
133 -- of Balances which need to be fetched using
134 --
135 --------------------------------------------------------------------
136
137
138 procedure initialization_code
139 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type)
140 is
141
142 CURSOR csr_defined_balance_id
143 IS
144 SELECT decode(pbt.balance_name,'Earnings_Total',1,'Direct Payments',2,'Termination_Payments',3,
145 'Involuntary Deductions',4,'Pre Tax Deductions',5,'Termination Deductions',6,
146 'Voluntary Deductions',7,'Total_Tax_Deductions',8,'Earnings_Non_Taxable',9,
147 'Employer_Charges',10,
148 'Lump Sum A Payments',11,'Lump Sum B Payments',12,'Lump Sum C Payments',13,
149 'Lump Sum D Payments',14,'Lump Sum E Payments',15,'Invalidity Payments',16,'CDEP',17,
150 'Leave Payments Marginal',18,'Other Income',19,'Union Fees',20,
151 'Workplace Giving Deductions' ,21) sort_index, /*4085496 */
152 pdb.defined_balance_id defined_balance_id,
153 pbt.balance_name
154 FROM pay_balance_types pbt,
155 pay_balance_dimensions pbd,
156 pay_defined_balances pdb
157 WHERE pbt.balance_name IN ('Earnings_Total','Direct Payments','Termination_Payments','Involuntary Deductions',
158 'Pre Tax Deductions','Termination Deductions','Voluntary Deductions','Total_Tax_Deductions',
159 'Earnings_Non_Taxable','Employer_Charges','Lump Sum A Payments','Lump Sum B Payments','Lump Sum C Payments',
160 'Lump Sum D Payments','Lump Sum E Payments','Invalidity Payments','CDEP','Leave Payments Marginal','Other Income','Union Fees', 'Workplace Giving Deductions') /*4085496 */
161 AND pbd.database_item_suffix = '_ASG_LE_YTD'
162 AND pbt.balance_type_id = pdb.balance_type_id
163 AND pbd.balance_dimension_id = pdb.balance_dimension_id
164 AND pbt.legislation_code = 'AU'
165 ORDER BY sort_index;
166
167 /*Bug 4132149 - Modification begins here */
168 cursor get_ps_params(c_payroll_action_id per_all_assignments_f.assignment_id%type)
169 is
170 select pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters) business_group_id
171 ,pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters) legal_employer
172 ,decode(pay_core_utils.get_parameter('PAYROLL',legislative_parameters),null,'%',pay_core_utils.get_parameter('PAYROLL',legislative_parameters)) payroll_id
173 ,decode(pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters),null,'%',pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters)) assignment_id
174 ,decode(pay_core_utils.get_parameter('EMPLOYEE_TYPE',legislative_parameters),'C','Y','T','N','B','%') employee_type
175 ,to_date('01-07-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fin_year_state_date
176 ,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),6,4),'DD-MM-YYYY') fin_year_end_date
177 ,to_date('01-04-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_start_date
178 ,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_end_date
179 ,pay_core_utils.get_parameter('LST_YR_TERM',legislative_parameters) lst_year_term
180 ,pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions /*Bug 4142159*/
181 ,decode(pay_core_utils.get_parameter('REP_MODE',legislative_parameters),'SUMM','S','D') report_mode /*Bug 3891577*/
182 from pay_payroll_actions
183 where payroll_action_id = c_payroll_action_id;
184
185 Cursor c_fbt_balance is
186 select pdb.defined_balance_id
187 from pay_balance_types pbt,
188 pay_defined_balances pdb,
189 pay_balance_dimensions pbd
190 where pbt.balance_name ='Fringe Benefits'
191 and pbt.balance_type_id = pdb.balance_type_id
192 and pdb.balance_dimension_id = pbd.balance_dimension_id /* Bug 2501105 */
193 and pbd.legislation_code ='AU'
194 and pbd.dimension_name ='_ASG_LE_FBT_YTD' --2610141
195 and pbd.legislation_code = pbt.legislation_code
196 and pbd.legislation_code = pdb.legislation_code;
197
198 /*Bug 4132149 - Modification ends here */
199
200 l_procedure varchar2(200) ;
201
202 begin
203
204 g_debug :=hr_utility.debug_enabled ;
205 if g_debug then
206 l_procedure := g_package||'initialization_code';
207 hr_utility.set_location('Entering '||l_procedure,1);
208 end if;
209
210 g_balance_value_tab.delete;
211
212 /*Bug 4132149 - Modification begins here */
213
214 g_arc_payroll_action_id := p_payroll_action_id;
215
216 /* Fetch Params */
217 open get_ps_params(p_payroll_action_id);
218 fetch get_ps_params into g_parameters;
219 close get_ps_params;
220
221 g_business_group_id := g_parameters.business_group_id;
222 g_start_date := g_parameters.fin_year_start_date;
223 g_end_date := g_parameters.fin_year_end_date;
224 g_tax_unit_id := g_parameters.legal_employer;
225
226 if g_debug
227 then
228 hr_utility.set_location('p_payroll_action_id.........= ' || p_payroll_action_id,30);
229 hr_utility.set_location('g_parameters.business_group_id.........= ' || g_parameters.business_group_id,30);
230 hr_utility.set_location('g_parameters.payroll_id..............= ' || g_parameters.payroll_id,30);
231 hr_utility.set_location('g_parameters.legal_employer.........= ' || g_parameters.legal_employer,30);
232 hr_utility.set_location('g_parameters.assignment_id.........= ' || g_parameters.assignment_id,30);
233 hr_utility.set_location('g_parameters.fin_year_start_date..............= ' || g_parameters.fin_year_start_date,30);
234 hr_utility.set_location('g_parameters.fin_year_end_date................= ' || g_parameters.fin_year_end_date,30);
235 hr_utility.set_location('g_parameters.fbt_year_start_date..............= ' || g_parameters.fbt_year_start_date,30);
236 hr_utility.set_location('g_parameters.fbt_year_end_date................= ' || g_parameters.fbt_year_end_date,30);
237 hr_utility.set_location('g_parameters.employee_type..........= '||g_parameters.employee_type,30);
238 hr_utility.set_location('g_parameters.delete_actions..........= '||g_parameters.delete_actions,30); /*Bug 4142159*/
239 hr_utility.set_location('g_parameters.report_mode..........= '||g_parameters.report_mode,30); /*Bug 3891577*/
240 end if;
241
242 /* SET FBT Defined_balance ID */
243 If g_fbt_defined_balance_id is null OR g_fbt_defined_balance_id =0 Then
244 Open c_fbt_balance;
245 Fetch c_fbt_balance into g_fbt_defined_balance_id;
246 Close c_fbt_balance;
247 End if;
248
249
250 /*Bug 4132149 - Modification ends here */
251
252 FOR csr_rec IN csr_defined_balance_id
253 LOOP
254 g_balance_value_tab(csr_rec.sort_index).defined_balance_id := csr_rec.defined_balance_id;
255 END LOOP;
256
257
258 IF g_debug THEN
259 hr_utility.trace('Defined Balance ids for YTD dimension');
260 hr_utility.trace('-------------------------------------');
261 hr_utility.trace('Earnings_Total ===>' || g_balance_value_tab(1).defined_balance_id);
262 hr_utility.trace('Workplace Giving Deductions ===>' || g_balance_value_tab(21).defined_balance_id);
263 hr_utility.trace('Direct Payments ===>' || g_balance_value_tab(2).defined_balance_id);
264 hr_utility.trace('Termination_Payments ===>' || g_balance_value_tab(3).defined_balance_id);
265 hr_utility.trace('Involuntary Deductions===>' || g_balance_value_tab(4).defined_balance_id);
266 hr_utility.trace('Pre Tax Deductions ===>' || g_balance_value_tab(5).defined_balance_id);
267 hr_utility.trace('Termination Deductions===>' || g_balance_value_tab(6).defined_balance_id);
268 hr_utility.trace('Voluntary Deductionsn ===>' || g_balance_value_tab(7).defined_balance_id);
269 hr_utility.trace('Total_Tax_Deduction ===>' || g_balance_value_tab(8).defined_balance_id);
270 hr_utility.trace('Earnings_Non_Taxable ===>' || g_balance_value_tab(9).defined_balance_id);
271 hr_utility.trace('Employer_Charges ===>' || g_balance_value_tab(10).defined_balance_id);
272 hr_utility.trace('Lump Sum A Payments ===>' || g_balance_value_tab(11).defined_balance_id);
273 hr_utility.trace('Lump Sum B Payments ===>' || g_balance_value_tab(12).defined_balance_id);
274 hr_utility.trace('Lump Sum C Payments ===>' || g_balance_value_tab(13).defined_balance_id);
275 hr_utility.trace('Lump Sum D Payments ===>' || g_balance_value_tab(14).defined_balance_id);
276 hr_utility.trace('Lump Sum E Payments ===>' || g_balance_value_tab(15).defined_balance_id);
277 hr_utility.trace('Invalidity Payments ===>' || g_balance_value_tab(16).defined_balance_id);
278 hr_utility.trace('CDEP ===>' || g_balance_value_tab(17).defined_balance_id);
279 hr_utility.trace('Leave Payments Marginal===>' || g_balance_value_tab(18).defined_balance_id);
280 hr_utility.trace('Other Income ===>' || g_balance_value_tab(19).defined_balance_id);
281 hr_utility.trace('Union Fees ===>' || g_balance_value_tab(20).defined_balance_id);
282 end if;
283
284 if g_debug then
285 hr_utility.set_location('Leaving '||l_procedure,1000);
286 end if;
287
288 exception
289 when others then
290 hr_utility.set_location('Error in '||l_procedure,999999);
291 raise;
292 end initialization_code;
293
294
295 /*
296 Bug 7138494 - Added Function range_person_on
297 --------------------------------------------------------------------
298 Name : range_person_on
299 Type : Function
300 Access: Private
301 Description: Checks if RANGE_PERSON_ID is enabled for
302 Archive process.
303 --------------------------------------------------------------------
304 */
305
306 FUNCTION range_person_on
307 (p_report_mode IN VARCHAR2)
308 RETURN BOOLEAN
309 IS
310
311 CURSOR csr_action_parameter is
312 select parameter_value
313 from pay_action_parameters
314 where parameter_name = 'RANGE_PERSON_ID';
315
316 CURSOR csr_range_format_param(c_report_type VARCHAR2)
317 IS
318 SELECT par.parameter_value
319 FROM pay_report_format_parameters par,
320 pay_report_format_mappings_f map
321 WHERE map.report_format_mapping_id = par.report_format_mapping_id
322 AND map.report_type = c_report_type
323 AND map.report_format = c_report_type
324 AND map.report_qualifier = 'AU'
325 AND par.parameter_name = 'RANGE_PERSON_ID'; -- Bug fix 5567246
326
327 l_return boolean;
328 l_action_param_val varchar2(30);
329 l_report_param_val varchar2(30);
330 l_report_type VARCHAR2(50);
331
332 BEGIN
333
334 g_debug := hr_utility.debug_enabled;
335
336 IF g_debug
337 THEN
338 hr_utility.set_location('range_person_on',10);
339 END IF;
340
341 BEGIN
342
343 open csr_action_parameter;
344 fetch csr_action_parameter into l_action_param_val;
345 close csr_action_parameter;
346
347 IF g_debug
348 THEN
349 hr_utility.set_location('range_person_on',20);
350 END IF;
351
352 IF p_report_mode = 'S'
353 THEN
354 l_report_type := 'AU_REC_PS_SUMM_ARCHIVE';
355 ELSE
356 l_report_type := 'AU_REC_PS_DET_ARCHIVE';
357 END IF;
358
359
360 open csr_range_format_param(l_report_type);
361 fetch csr_range_format_param into l_report_param_val;
362 close csr_range_format_param;
363 IF g_debug
364 THEN
365 hr_utility.set_location('range_person_on',30);
366 END IF;
367 EXCEPTION WHEN NO_DATA_FOUND THEN
368 l_return := FALSE;
369 END;
370 --
371 IF g_debug
372 THEN
373 hr_utility.set_location('range_person_on',40);
374 END IF;
375
376 IF l_action_param_val = 'Y' AND l_report_param_val = 'Y' THEN
377 l_return := TRUE;
378
379 IF g_debug
380 THEN
381 hr_utility.trace('Range Person = True');
382 END IF;
383 ELSE
384 l_return := FALSE;
385 END IF;
386 --
387 RETURN l_return;
388 --
389 END range_person_on;
390
391 --------------------------------------------------------------------+
392 -- Name : assignment_Action_code
393 -- Type : Procedure
394 -- Access: Public
395 -- This procedure further restricts the assignment_id's
396 -- returned by range_code
397 -- This procedure gets the parameters given by user and restricts
398 -- the assignments to be archived.
399 -- it then calls hr_nonrun.insact to create an assignment action id
400 -- it then archives Payroll Run assignment action id details
401 --------------------------------------------------------------------+
402
403 procedure assignment_action_code
404 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type
405 ,p_start_person in per_all_people_f.person_id%type
406 ,p_end_person in per_all_people_f.person_id%type
407 ,p_chunk in number
408 ) is
409 cursor get_ps_params(c_payroll_action_id per_all_assignments_f.assignment_id%type)
410 is
411 select pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters) business_group_id
412 ,pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters) legal_employer
413 ,decode(pay_core_utils.get_parameter('PAYROLL',legislative_parameters),null,'%',pay_core_utils.get_parameter('PAYROLL',legislative_parameters)) payroll_id
414 ,decode(pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters),null,'%',pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters)) assignment_id
415 ,decode(pay_core_utils.get_parameter('EMPLOYEE_TYPE',legislative_parameters),'C','Y','T','N','B','%') employee_type
416 ,to_date('01-07-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fin_year_state_date
417 ,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),6,4),'DD-MM-YYYY') fin_year_end_date
418 ,to_date('01-04-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_start_date
419 ,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_end_date
420 ,pay_core_utils.get_parameter('LST_YR_TERM',legislative_parameters) lst_year_term
421 ,pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions /*Bug 4142159*/
422 ,decode(pay_core_utils.get_parameter('REP_MODE',legislative_parameters),'SUMM','S','D') report_mode /*Bug 3891577*/
423 from pay_payroll_actions
424 where payroll_action_id = c_payroll_action_id;
425
426 --------------------------------------------------------------------+
427 -- Cursor : csr_assignment_paysum
428 -- Description : Fetches assignments For Recconciling Payment Summary
429 -- Returns DISTINCT assignment_id
430 --
431 --------------------------------------------------------------------+
432 cursor csr_assignment_paysum
433 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
434 ,c_start_person per_all_people_f.person_id%type
435 ,c_end_person per_all_people_f.person_id%type
436 ,c_employee_type per_all_people_f.current_employee_flag%type
437 ,c_business_group_id hr_all_organization_units.organization_id%type
438 ,c_legal_employer pay_assignment_actions.tax_unit_id%type
439 ,c_payroll_id varchar2
440 ,c_fin_year_start date
441 ,c_fin_year_end date
442 ,c_lst_fbt_yr_start date
443 ,c_fbt_year_start date
444 ,c_fbt_year_end date
445 ,c_lst_year_start date
446 ,c_fbt_defined_balance_id pay_defined_balances.defined_balance_id%type
447 ,c_assignment_id varchar2
448 ) is
449 SELECT /*+ INDEX(pap per_people_f_pk)
450 INDEX(paa per_assignments_f_fk1)
451 INDEX(paa per_assignments_f_N12)
452 INDEX(rppa pay_payroll_actions_pk)
453 INDEX(pps per_periods_of_service_n3)
454 */ distinct paa.assignment_id
455 from per_people_f pap
456 ,per_assignments_f paa
457 ,pay_payroll_actions rppa
458 ,per_periods_of_service pps
459 where rppa.payroll_action_id = c_payroll_action_id
460 and pap.person_id between c_start_person and c_end_person
461 and pap.person_id = paa.person_id
462 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
463 and pps.period_of_service_id = paa.period_of_service_id
464 and pap.person_id = pps.person_id
465 and rppa.business_group_id=paa.business_group_id
466 and nvl(pps.actual_termination_date, c_lst_year_start) >= c_lst_year_start
467 and c_fin_year_end between pap.effective_start_date and pap.effective_end_date
468 -- 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
469 and paa.effective_end_date = (select max(effective_end_date) /*4377367*/
470 From per_assignments_f iipaf
471 WHERE iipaf.assignment_id = paa.assignment_id
472 and iipaf.effective_end_date >= c_fbt_year_start
473 and iipaf.effective_start_date <= c_fin_year_end
474 AND iipaf.payroll_id IS NOT NULL) /*Bug 4688800*/
475 and paa.payroll_id like c_payroll_id
476 and paa.assignment_id like c_assignment_id
477 AND EXISTS (SELECT /*+ INDEX(rpac PAY_ASSIGNMENT_ACTIONS_N51)
478 INDEX(rpac pay_assignment_actions_n1)
479 INDEX(rppa PAY_PAYROLL_ACTIONS_N51)
480 INDEX(rppa PAY_PAYROLL_ACTIONS_PK) */''
481 FROM per_assignments_f paaf
482 ,pay_payroll_actions rppa
483 ,pay_assignment_actions rpac
484 where (rppa.effective_date between c_fin_year_start and c_fin_year_end /*Bug3048962 */
485 or ( pps.actual_termination_date between c_lst_fbt_yr_start and c_fbt_year_end /*Bug3263659 */
486 and rppa.effective_date between c_fbt_year_start and c_fbt_year_end
487 and pay_balance_pkg.get_value(c_fbt_defined_balance_id, rpac.assignment_action_id
488 + decode(rppa.payroll_id, 0, 0, 0),c_legal_employer,null,null,null,null) > to_number(g_fbt_threshold)) /* Bug 5708255 */
489 )
490 and rppa.action_type in ('R','Q','B','I')
491 and rpac.tax_unit_id = c_legal_employer
492 and rppa.payroll_action_id = rpac.payroll_action_id
493 and rpac.action_status = 'C'
494 and rpac.assignment_id = paaf.assignment_id
495 and paaf.assignment_id = paa.assignment_id
496 and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date
497 and rppa.payroll_id = paaf.payroll_id );
498
499 --------------------------------------------------------------------+
500 -- Cursor : csr_assignment_only_paysum
501 -- Description : Fetches assignments For Recconciling Payment Summary
502 -- Returns DISTINCT assignment_id for Distinct
503 -- Assignment.
504 --------------------------------------------------------------------+
505
506 cursor csr_assignment_only_paysum
507 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
508 ,c_start_person per_all_people_f.person_id%type
509 ,c_end_person per_all_people_f.person_id%type
510 ,c_employee_type per_all_people_f.current_employee_flag%type
511 ,c_business_group_id hr_all_organization_units.organization_id%type
512 ,c_legal_employer pay_assignment_actions.tax_unit_id%type
513 ,c_payroll_id varchar2
514 ,c_fin_year_start date
515 ,c_fin_year_end date
516 ,c_lst_fbt_yr_start date
517 ,c_fbt_year_start date
518 ,c_fbt_year_end date
519 ,c_lst_year_start date
520 ,c_fbt_defined_balance_id pay_defined_balances.defined_balance_id%type
521 ,c_assignment_id varchar2
522 ) is
523 SELECT /*+ INDEX(pap per_people_f_pk)
524 INDEX(paa per_assignments_f_fk1)
525 INDEX(paa per_assignments_f_N12)
526 INDEX(rppa pay_payroll_actions_pk)
527 INDEX(pps per_periods_of_service_n3)
528 */ distinct paa.assignment_id
529 from per_people_f pap
530 ,per_assignments_f paa
531 ,pay_payroll_actions rppa
532 ,per_periods_of_service pps
533 where rppa.payroll_action_id = c_payroll_action_id
534 and pap.person_id between c_start_person and c_end_person
535 and pap.person_id = paa.person_id
536 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
537 and pps.period_of_service_id = paa.period_of_service_id
538 and pap.person_id = pps.person_id
539 and rppa.business_group_id=paa.business_group_id
540 and nvl(pps.actual_termination_date, c_lst_year_start) >= c_lst_year_start
541 and c_fin_year_end between pap.effective_start_date and pap.effective_end_date
542 -- 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
543 and paa.effective_end_date = (select max(effective_end_date) /*4377367*/
544 From per_assignments_f iipaf
545 WHERE iipaf.assignment_id = paa.assignment_id
546 and iipaf.effective_end_date >= c_fbt_year_start
547 and iipaf.effective_start_date <= c_fin_year_end
548 AND iipaf.payroll_id IS NOT NULL) /*Bug 4688800*/
549 and paa.payroll_id like c_payroll_id
550 and paa.assignment_id = c_assignment_id
551 AND EXISTS (SELECT /*+ INDEX(rpac PAY_ASSIGNMENT_ACTIONS_N51)
552 INDEX(rpac pay_assignment_actions_n1)
553 INDEX(rppa PAY_PAYROLL_ACTIONS_N51)
554 INDEX(rppa PAY_PAYROLL_ACTIONS_PK) */''
555 FROM per_assignments_f paaf
556 ,pay_payroll_actions rppa
557 ,pay_assignment_actions rpac
558 where (rppa.effective_date between c_fin_year_start and c_fin_year_end /*Bug3048962 */
559 or ( pps.actual_termination_date between c_lst_fbt_yr_start and c_fbt_year_end /*Bug3263659 */
560 and rppa.effective_date between c_fbt_year_start and c_fbt_year_end
561 and pay_balance_pkg.get_value(c_fbt_defined_balance_id, rpac.assignment_action_id
562 + decode(rppa.payroll_id, 0, 0, 0),c_legal_employer,null,null,null,null) > to_number(g_fbt_threshold)) /* Bug 5708255 */
563 )
564 and rppa.action_type in ('R','Q','B','I')
565 and rpac.tax_unit_id = c_legal_employer
566 and rppa.payroll_action_id = rpac.payroll_action_id
567 and rpac.action_status = 'C'
568 and rpac.assignment_id = paaf.assignment_id
569 and paaf.assignment_id = paa.assignment_id
570 and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date
571 and rppa.payroll_id = paaf.payroll_id );
572
573 /*
574 Bug 7138494 - Added Cursor for Range Person
575 - Uses person_id in pay_population_ranges
576 --------------------------------------------------------------------+
577 -- Cursor : csr_range_assignment_paysum
578 -- Description : Fetches assignments For Recconciling Payment Summary
579 -- Returns DISTINCT assignment_id
580 -- Used when RANGE_PERSON_ID feature is enabled
581 --------------------------------------------------------------------+
582 */
583
584 CURSOR csr_range_assignment_paysum
585 (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
586 ,c_chunk NUMBER
587 ,c_employee_type per_all_people_f.current_employee_flag%type
588 ,c_business_group_id hr_all_organization_units.organization_id%type
589 ,c_legal_employer pay_assignment_actions.tax_unit_id%type
590 ,c_payroll_id varchar2
591 ,c_fin_year_start date
592 ,c_fin_year_end date
593 ,c_lst_fbt_yr_start date
594 ,c_fbt_year_start date
595 ,c_fbt_year_end date
596 ,c_lst_year_start date
597 ,c_fbt_defined_balance_id pay_defined_balances.defined_balance_id%type
598 ,c_assignment_id varchar2
599 )
600 IS
601 SELECT /*+ INDEX(pap per_people_f_pk)
602 INDEX(rppa pay_payroll_actions_pk)
603 INDEX(ppr PAY_POPULATION_RANGES_N4)
604 INDEX(paa per_assignments_f_N12)
605 INDEX(pps per_periods_of_service_PK)
606 */ paa.assignment_id
607 from per_people_f pap
608 ,per_assignments_f paa
609 ,pay_payroll_actions rppa
610 ,per_periods_of_service pps
611 ,pay_population_ranges ppr
612 WHERE rppa.payroll_action_id = c_payroll_action_id
613 AND rppa.payroll_action_id = ppr.payroll_action_id
614 AND ppr.chunk_number = c_chunk
615 AND pap.person_id = ppr.person_id
616 AND pap.person_id = paa.person_id
617 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
618 AND pps.period_of_service_id = paa.period_of_service_id
619 AND pap.person_id = pps.person_id
620 AND rppa.business_group_id=paa.business_group_id
621 AND nvl(pps.actual_termination_date, c_lst_year_start) >= c_lst_year_start
622 AND c_fin_year_end between pap.effective_start_date AND pap.effective_end_date
623 AND paa.effective_end_date = (SELECT MAX(effective_end_date) /*4377367*/
624 FROM per_assignments_f iipaf
625 WHERE iipaf.assignment_id = paa.assignment_id
626 AND iipaf.effective_end_date >= c_fbt_year_start
627 AND iipaf.effective_start_date <= c_fin_year_end
628 AND iipaf.payroll_id IS NOT NULL) /*Bug 4688800*/
629 AND paa.payroll_id like c_payroll_id
630 AND paa.assignment_id like c_assignment_id
631 AND EXISTS (SELECT /*+ INDEX(rpac PAY_ASSIGNMENT_ACTIONS_N51)
632 INDEX(rppa PAY_PAYROLL_ACTIONS_N51)
633 */''
634 FROM per_assignments_f paaf
635 ,pay_payroll_actions rppa
636 ,pay_assignment_actions rpac
637 WHERE (rppa.effective_date between c_fin_year_start AND c_fin_year_end /*Bug3048962 */
638 or ( pps.actual_termination_date between c_lst_fbt_yr_start AND c_fbt_year_end /*Bug3263659 */
639 AND rppa.effective_date between c_fbt_year_start AND c_fbt_year_end
640 AND pay_balance_pkg.get_value(c_fbt_defined_balance_id, rpac.assignment_action_id
641 + decode(rppa.payroll_id, 0, 0, 0),c_legal_employer,null,null,null,null) > to_number(g_fbt_threshold)) /* Bug 5708255 */
642 )
643 AND rppa.action_type in ('R','Q','B','I')
644 AND rpac.tax_unit_id = c_legal_employer
645 AND rppa.payroll_action_id = rpac.payroll_action_id
646 AND rpac.action_status = 'C'
647 AND rpac.assignment_id = paaf.assignment_id
648 AND paaf.assignment_id = paa.assignment_id
649 AND rppa.effective_date between paaf.effective_start_date AND paaf.effective_end_date
650 AND rppa.payroll_id = paaf.payroll_id );
651
652 Cursor c_fbt_balance is
653 select pdb.defined_balance_id
654 from pay_balance_types pbt,
655 pay_defined_balances pdb,
656 pay_balance_dimensions pbd
657 where pbt.balance_name ='Fringe Benefits'
658 and pbt.balance_type_id = pdb.balance_type_id
659 and pdb.balance_dimension_id = pbd.balance_dimension_id /* Bug 2501105 */
660 and pbd.legislation_code ='AU'
661 and pbd.dimension_name ='_ASG_LE_FBT_YTD' --2610141
662 and pbd.legislation_code = pbt.legislation_code
663 and pbd.legislation_code = pdb.legislation_code;
664
665 cursor csr_next_action_id is
666 select pay_assignment_actions_s.nextval
667 from dual;
668
669 /* Bug 5708255 */
670 -------------------------------------------
671 -- Added cursor to get value of global FBT_THRESHOLD
672 --------------------------------------------
673 CURSOR c_get_fbt_global(c_year_end DATE)
674 IS
675 SELECT global_value
676 FROM ff_globals_f
677 WHERE global_name = 'FBT_THRESHOLD'
678 AND legislation_code = 'AU'
679 AND c_year_end BETWEEN effective_start_date
680 AND effective_end_date ;
681
682
683 l_next_assignment_action_id pay_assignment_actions.assignment_action_id%type;
684 l_procedure varchar2(200) ;
685 i number;
686
687 l_lst_yr_start date;
688 l_lst_fbt_yr_start date;
689
690 begin
691
692 g_debug :=hr_utility.debug_enabled;
693 if g_debug
694 then
695 l_procedure := '.assignment_action_code';
696 hr_utility.set_location('Entering '||l_procedure,1);
697 end if;
698
699 g_arc_payroll_action_id := p_payroll_action_id;
700
701 /* Fetch Params */
702 open get_ps_params(p_payroll_action_id);
703 fetch get_ps_params into g_parameters;
704 close get_ps_params;
705
706
707 g_business_group_id := g_parameters.business_group_id;
708 g_start_date := g_parameters.fin_year_start_date;
709 g_end_date := g_parameters.fin_year_end_date;
710 g_tax_unit_id := g_parameters.legal_employer;
711
712 /* Bug 5708255 */
713 open c_get_fbt_global (add_months(g_end_date,-3));
714 fetch c_get_fbt_global into g_fbt_threshold;
715 close c_get_fbt_global;
716
717
718 if (g_parameters.lst_year_term ='Y' or g_parameters.lst_year_term is NULL )
719 then
720 l_lst_yr_start := add_months(g_parameters.fin_year_start_date,-12);
721 l_lst_fbt_yr_start := g_parameters.fbt_year_start_date;
722 else
723 l_lst_yr_start := TO_DATE('01-01-1900','DD-MM-YYYY');
724 l_lst_fbt_yr_start := TO_DATE('01-01-1900','DD-MM-YYYY');
725 end if;
726
727 if g_debug
728 then
729 hr_utility.set_location('p_payroll_action_id.........= ' || p_payroll_action_id,30);
730 hr_utility.set_location('p_start_person..............= ' || p_start_person,30);
731 hr_utility.set_location('p_end_person................= ' || p_end_person,30);
732 hr_utility.set_location('g_parameters.business_group_id.........= ' || g_parameters.business_group_id,30);
733 hr_utility.set_location('g_parameters.payroll_id..............= ' || g_parameters.payroll_id,30);
734 hr_utility.set_location('g_parameters.legal_employer.........= ' || g_parameters.legal_employer,30);
735 hr_utility.set_location('g_parameters.assignment_id.........= ' || g_parameters.assignment_id,30);
736 hr_utility.set_location('g_parameters.fin_year_start_date..............= ' || g_parameters.fin_year_start_date,30);
737 hr_utility.set_location('g_parameters.fin_year_end_date................= ' || g_parameters.fin_year_end_date,30);
738 hr_utility.set_location('g_parameters.fbt_year_start_date..............= ' || g_parameters.fbt_year_start_date,30);
739 hr_utility.set_location('g_parameters.fbt_year_end_date................= ' || g_parameters.fbt_year_end_date,30);
740 hr_utility.set_location('g_parameters.employee_type..........= '||g_parameters.employee_type,30);
741 hr_utility.set_location('g_parameters.delete_actions..........= '||g_parameters.delete_actions,30); /*Bug 4142159*/
742 hr_utility.set_location('l_lst_yr_start.........................='||l_lst_yr_start,30);
743 hr_utility.set_location('l_lst_fbt_yr_start.....................='||l_lst_fbt_yr_start,30);
744 hr_utility.set_location('g_parameters.report_mode..........= '||g_parameters.report_mode,30); /*Bug 3891577*/
745 end if;
746
747 /* SET FBT Defined_balance ID */
748 If g_fbt_defined_balance_id is null OR g_fbt_defined_balance_id =0 Then
749 Open c_fbt_balance;
750 Fetch c_fbt_balance into g_fbt_defined_balance_id;
751 Close c_fbt_balance;
752 End if;
753
754 IF (g_parameters.payroll_id <> '%' and g_parameters.assignment_id <> '%')
755 THEN
756
757 FOR csr_rec in csr_assignment_only_paysum(p_payroll_action_id,
758 p_start_person,
759 p_end_person,
760 g_parameters.employee_type,
761 g_parameters.business_group_id,
762 g_parameters.legal_employer,
763 g_parameters.payroll_id,
764 g_parameters.fin_year_start_date,
765 g_parameters.fin_year_end_date,
766 l_lst_fbt_yr_start,
767 g_parameters.fbt_year_start_date,
768 g_parameters.fbt_year_end_date,
769 l_lst_yr_start,
770 g_fbt_defined_balance_id,
771 g_parameters.assignment_id)
772 LOOP /* LOOP FOR Payment Summary - Archives for each Assignment ID*/
773
774 open csr_next_action_id;
775 fetch csr_next_action_id into l_next_assignment_action_id;
776 close csr_next_action_id;
777
778 IF g_debug THEN
779 hr_utility.set_location('Calling hr_nonrun_asact.insact for assignment id :'||csr_rec.assignment_id,2);
780 END if;
781
782 hr_nonrun_asact.insact(l_next_assignment_action_id,
783 csr_rec.assignment_id,
784 p_payroll_action_id,
785 p_chunk,
786 null);
787
788 END LOOP;
789 ELSE
790
791 /* Bug 7138494 - Added Changes for Range Person
792 - Call Cursor using pay_population_ranges if Range Person Enabled
793 Else call Old Cursor
794 */
795
796 IF range_person_on(g_parameters.report_mode)
797 THEN
798
799 FOR csr_rec in csr_range_assignment_paysum(p_payroll_action_id,
800 p_chunk,
801 g_parameters.employee_type,
802 g_parameters.business_group_id,
803 g_parameters.legal_employer,
804 g_parameters.payroll_id,
805 g_parameters.fin_year_start_date,
806 g_parameters.fin_year_end_date,
807 l_lst_fbt_yr_start,
808 g_parameters.fbt_year_start_date,
809 g_parameters.fbt_year_end_date,
810 l_lst_yr_start,
811 g_fbt_defined_balance_id,
812 g_parameters.assignment_id)
813 LOOP /* LOOP FOR Payment Summary - Archives for each Assignment ID*/
814
815 open csr_next_action_id;
816 fetch csr_next_action_id into l_next_assignment_action_id;
817 close csr_next_action_id;
818
819 IF g_debug THEN
820 hr_utility.set_location('Calling hr_nonrun_asact.insact for assignment id :'||csr_rec.assignment_id,2);
821 END if;
822
823 hr_nonrun_asact.insact(l_next_assignment_action_id,
824 csr_rec.assignment_id,
825 p_payroll_action_id,
826 p_chunk,
827 null);
828
829 END LOOP;
830
831 ELSE /* Old Code - No Range Person */
832
833
834 FOR csr_rec in csr_assignment_paysum(p_payroll_action_id,
835 p_start_person,
836 p_end_person,
837 g_parameters.employee_type,
838 g_parameters.business_group_id,
839 g_parameters.legal_employer,
840 g_parameters.payroll_id,
841 g_parameters.fin_year_start_date,
842 g_parameters.fin_year_end_date,
843 l_lst_fbt_yr_start,
844 g_parameters.fbt_year_start_date,
845 g_parameters.fbt_year_end_date,
846 l_lst_yr_start,
847 g_fbt_defined_balance_id,
848 g_parameters.assignment_id)
849 LOOP /* LOOP FOR Payment Summary - Arcbives for each Assignment ID*/
850
851 open csr_next_action_id;
852 fetch csr_next_action_id into l_next_assignment_action_id;
853 close csr_next_action_id;
854
855 IF g_debug THEN
856 hr_utility.set_location('Calling hr_nonrun_asact.insact for assignment id :'||csr_rec.assignment_id,2);
857 END if;
858
859 hr_nonrun_asact.insact(l_next_assignment_action_id,
860 csr_rec.assignment_id,
861 p_payroll_action_id,
862 p_chunk,
863 null);
864
865 END LOOP;
866 END IF; /* End Range Person Check */
867 END IF;
868
869 if g_debug then
870 hr_utility.set_location('Leaving '||l_procedure,1000);
871 end if;
872 exception
873 when others then
874 hr_utility.set_location('Error in '||l_procedure,999999);
875 raise;
876 end assignment_action_code;
877
878 --------------------------------------------------------------------+
879 -- Name : Archive_code
880 -- Type : Procedure
881 -- Access: Public
882 -- This procedure archives employee details for Assignment in
883 -- pay_action_information.
884 -- Identifies if Assignment is an FBT or Normal Employee and
885 -- fetches appropriate Balance values using BBR.
886 --------------------------------------------------------------------+
887
888 procedure archive_code
889 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
890 ,p_effective_date in pay_payroll_actions.effective_date%type
891 )
892 is
893
894 --------------------------------------------------------------------+
895 -- Cursor : c_employee_details
896 -- Description : Fetches employee details to be displayed on Report.
897 -- Latest Values as on End Date is fetched.
898 --------------------------------------------------------------------+
899 cursor c_employee_details
900 ( c_business_group_id hr_all_organization_units.organization_id%TYPE,
901 c_archive_assignment_action_id number,
902 c_start_date date,
903 c_end_date date)
904 is
905 select pap.full_name,
906 paa.assignment_number,
907 paa.assignment_id,
908 to_number(pro.proposed_salary_n) actual_salary,
909 paa.normal_hours,
910 pps.actual_termination_date,
911 pps.date_start,
912 pgr.name grade,
913 paa.organization_id,
914 paa.payroll_id,
915 hsc.segment1 tax_unit_id,
916 hou.NAME organization_name,
917 hou1.name legal_employer
918 -- papf.payroll_name /*Bug 4688800*/
919 from per_people_f pap,
920 per_assignments_f paa,
921 per_grades_tl pgr,
922 per_periods_of_service pps,
923 per_pay_bases ppb,
924 per_pay_proposals pro,
925 per_assignment_status_types past,
926 hr_organization_units hou,
927 pay_assignment_actions paa1
928 ,hr_soft_coding_keyflex hsc
929 ,hr_organization_units hou1
930 -- ,pay_payrolls_f papf /*Bug 4688800*/
931 where pap.person_id = paa.person_id
932 and paa.assignment_id = paa1.assignment_id
933 and paa1.assignment_action_id = c_archive_assignment_action_id
934 and paa.business_group_id = c_business_group_id
935 and paa.grade_id = pgr.grade_id(+)
936 and pgr.language(+) = userenv('LANG')
937 and paa.pay_basis_id = ppb.pay_basis_id(+)
938 and paa.assignment_id = pro.assignment_id(+)
939 AND hou.organization_id = paa.organization_id
940 and hsc.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
941 and hou1.organization_id = hsc.segment1
942 -- and papf.payroll_id = paa.payroll_id /*Bug 4688800*/
943 -- and c_end_date between papf.effective_start_date and papf.effective_end_date /*Bug 4688800*/
944 and pps.period_of_service_id = paa.period_of_service_id
945 and paa.assignment_status_type_id = past.assignment_status_type_id
946 and paa.effective_end_date = ( select max(effective_end_date)
947 from per_assignments_f
948 WHERE assignment_id = paa.assignment_id
949 and effective_end_date >= c_start_date
950 and effective_start_date <= c_end_date)
951 and c_end_date between pap.effective_start_date and pap.effective_end_date
952 and pps.person_id = pap.person_id
953 and pro.change_date(+) <= c_end_date
954 and nvl(pro.approved,'Y') = 'Y'
955 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'))
956 from per_pay_proposals pro1
957 where pro1.assignment_id(+) = paa.assignment_id
958 and pro1.change_date(+) <= c_end_date
959 and nvl(pro1.approved,'Y')='Y');
960
961
962 /*Bug 4688800 - Introduced a new cursor to get the payroll name for the employee. This has been done to take care of cases
963 where assignment has payroll attached to it for few months but is not attached at the end of year*/
964 CURSOR c_get_payroll_name(c_assignment_id number,
965 c_start_date date,
966 c_end_date date)
967 IS
968 SELECT paaf.payroll_id, pay.payroll_name
969 FROM per_all_assignments_f paaf,
970 pay_payrolls_f pay
971 WHERE paaf.assignment_id = c_assignment_id
972 and paaf.effective_end_date = (select max(effective_end_date)
973 From per_assignments_f iipaf
974 WHERE iipaf.assignment_id = c_assignment_id
975 and iipaf.effective_end_date >= c_start_date
976 and iipaf.effective_start_date <= c_end_date
977 AND iipaf.payroll_id IS NOT NULL)
978 AND pay.payroll_id = paaf.payroll_id
979 AND paaf.effective_end_date BETWEEN pay.effective_start_date AND pay.effective_end_date;
980
981 /* Bug 3891577 - Employee details cursor */
982 --------------------------------------------------------------------+
983 -- Cursor : c_summary_employee_details
984 -- Description : Fetches employee details to be displayed on
985 -- Summary Report - Pay Sum Mode
986 -- Latest Values as on End Date is fetched.
987 --------------------------------------------------------------------+
988 cursor c_summary_employee_details
989 ( c_business_group_id hr_all_organization_units.organization_id%TYPE,
990 c_archive_assignment_action_id number,
991 c_start_date date,
992 c_end_date date)
993 is
994 select pap.full_name,
995 paf.assignment_number,
996 paf.assignment_id,
997 pps.date_start,
998 pps.actual_termination_date
999 from per_people_f pap,
1000 per_assignments_f paf,
1001 per_periods_of_service pps,
1002 pay_assignment_actions paa
1003 where pap.person_id = paf.person_id
1004 and paf.assignment_id = paa.assignment_id
1005 and pps.person_id = pap.person_id
1006 and pps.period_of_service_id = paf.period_of_service_id
1007 and paf.business_group_id = c_business_group_id
1008 and paa.assignment_action_id = c_archive_assignment_action_id
1009 and paf.effective_end_date = ( select max(effective_end_date)
1010 from per_assignments_f
1011 WHERE assignment_id = paf.assignment_id
1012 and effective_end_date >= c_start_date
1013 and effective_start_date <= c_end_date)
1014 and c_end_date between pap.effective_start_date and pap.effective_end_date;
1015
1016 --------------------------------------------------------------------+
1017 -- Cursor : get_max_action
1018 -- Description : Fetches Maximum Assignment Action Processed for
1019 -- Assignment between Start/End Dates
1020 --------------------------------------------------------------------+
1021
1022 cursor get_max_action(c_assignment_id number
1023 ,c_start_date date,c_end_date date
1024 ,c_tax_unit_id varchar2)
1025 is
1026 select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
1027 ,max(paa.action_sequence) action_sequence
1028 from pay_assignment_actions paa
1029 , pay_payroll_actions ppa
1030 , per_assignments_f paf
1031 where paa.assignment_id = paf.assignment_id
1032 and paf.assignment_id = c_assignment_id
1033 and ppa.payroll_action_id = paa.payroll_action_id
1034 and ppa.effective_date between c_start_date and c_end_date
1035 and ppa.payroll_id = paf.payroll_id
1036 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
1037 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
1038 and paa.action_status='C'
1039 AND paa.tax_unit_id = c_tax_unit_id;
1040
1041 --------------------------------------------------------------------+
1042 -- Cursor : c_payment_summary_details
1043 -- Description : Fetches Manual PS Issued for Fin Year.
1044 -- Supports Legal Employer segment to have
1045 -- NULL,All,Legal Employer
1046 --------------------------------------------------------------------+
1047
1048 cursor c_payment_summary_details(c_assignment_id number,
1049 c_fin_date date,
1050 c_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE)
1051 is
1052 select hr.meaning fin_year
1053 from per_assignment_extra_info pae,
1054 hr_lookups hr
1055 where pae.aei_information_category = 'HR_PS_ISSUE_DATE_AU'
1056 and pae.information_type = 'HR_PS_ISSUE_DATE_AU'
1057 and pae.assignment_id = c_assignment_id
1058 and pae.aei_information1 = to_char(c_fin_date,'YY')
1059 and nvl(aei_information2,c_tax_unit_id) = decode(aei_information2,'-999',aei_information2,c_tax_unit_id)
1060 and pae.aei_information1 = hr.lookup_code
1061 and hr.lookup_type = 'AU_PS_FINANCIAL_YEAR';
1062
1063
1064 l_procedure varchar2(200) ;
1065
1066 l_employee_details c_employee_details%ROWTYPE;
1067 l_fin_year varchar2(80);
1068
1069 l_action_sequence number;
1070 l_assignment_action_id number;
1071 l_dummy_action_sequence number;
1072 l_allowance_exist number;
1073
1074 e_prepost_error exception;
1075 l_pre01jul1983_days NUMBER;
1076 l_post30jun1983_days NUMBER;
1077 l_pre01jul1983_ratio NUMBER;
1078 l_post30jun1983_ratio NUMBER;
1079 l_pre01jul1983_value NUMBER;
1080 l_post30jun1983_value NUMBER;
1081 l_etp_service_date date;
1082 l_le_etp_service_date date; /* Bug 4177679 */
1083 l_result NUMBER;
1084
1085 /* Set Archive Check Date */
1086 l_lst_fbt_yr_start date;
1087 l_payroll_id number; /*Bug 4688800*/
1088 l_payroll_name pay_payrolls_f.payroll_name%type; /*Bug 4688800*/
1089
1090 begin
1091
1092 g_debug :=hr_utility.debug_enabled ;
1093
1094 if g_debug then
1095 l_procedure := g_package||'archive_code';
1096 hr_utility.set_location('Entering '||l_procedure,300);
1097 hr_utility.set_location('p_assignment_action_id......= '|| p_assignment_action_id,10);
1098 hr_utility.set_location('p_effective_date............= '|| to_char(p_effective_date,'DD-MON-YYYY'),10);
1099 end if;
1100
1101 if (g_parameters.lst_year_term ='Y' or g_parameters.lst_year_term is NULL )
1102 then
1103 l_lst_fbt_yr_start := g_parameters.fbt_year_start_date;
1104 else
1105 l_lst_fbt_yr_start := TO_DATE('01-01-1900','DD-MM-YYYY');
1106 end if;
1107
1108 /* Bug 3891577 - Checks added for Summary/Detail Report.
1109 Based on Report Mode flag,appropriate cursors will be called and employee details fetched
1110 Start Bug 3891577 */
1111 if g_parameters.report_mode = 'D' /* Detail Report */
1112 then
1113 open c_employee_details(g_business_group_id,p_assignment_action_id,add_months(g_start_date,-3),g_end_date);
1114 fetch c_employee_details into l_employee_details;
1115 if c_employee_details%notfound
1116 then
1117 if g_debug then
1118 hr_utility.set_location('No Employee details found!!',3002);
1119 end if;
1120 end if;
1121 close c_employee_details;
1122
1123 else /* Summary Report */
1124
1125 open c_summary_employee_details(g_business_group_id,p_assignment_action_id,add_months(g_start_date,-3),g_end_date);
1126 fetch c_summary_employee_details into l_employee_details.full_name,
1127 l_employee_details.assignment_number,
1128 l_employee_details.assignment_id,
1129 l_employee_details.date_start,
1130 l_employee_details.actual_termination_date;
1131 close c_summary_employee_details;
1132
1133 end if;
1134 /* End Bug 3891577*/
1135
1136 /* Bug 4726352 - Manual PS Details fetched for both Summary and Detail Report
1137 Get Manual PS Issued and store in l_fin_year */
1138
1139 OPEN c_payment_summary_details(l_employee_details.assignment_id,g_start_date,g_tax_unit_id);
1140 FETCH c_payment_summary_details into l_fin_year;
1141 CLOSE c_payment_summary_details;
1142
1143
1144 /*Bug 4688800*/
1145 OPEN c_get_payroll_name(l_employee_details.assignment_id,g_start_date,g_end_date);
1146 FETCH c_get_payroll_name INTO l_payroll_id, l_payroll_name;
1147 CLOSE c_get_payroll_name;
1148
1149 insert into pay_action_information(
1150 action_information_id,
1151 action_context_id,
1152 action_context_type,
1153 effective_date,
1154 source_id,
1155 tax_unit_id,
1156 action_information_category,
1157 action_information1,
1158 action_information2,
1159 action_information3,
1160 action_information4,
1161 action_information5,
1162 action_information6,
1163 action_information7,
1164 action_information8,
1165 action_information9,
1166 action_information10,
1167 action_information11,
1168 action_information12,
1169 action_information13,
1170 assignment_id)
1171 values(
1172 pay_action_information_s.nextval,
1173 g_arc_payroll_action_id,
1174 'PA',
1175 p_effective_date,
1176 null,
1177 l_employee_details.tax_unit_id,
1178 'AU_EMPLOYEE_RECON_DETAILS',
1179 l_employee_details.full_name,
1180 l_employee_details.assignment_number,
1181 l_employee_details.actual_salary,
1182 l_employee_details.grade,
1183 l_employee_details.normal_hours,
1184 l_employee_details.actual_termination_date,
1185 l_fin_year,
1186 l_employee_details.organization_id,
1187 g_tax_unit_id,
1188 l_employee_details.payroll_id,
1189 l_employee_details.organization_name,
1190 l_employee_details.legal_employer,
1191 l_payroll_name, /*Bug 4688800*/
1192 l_employee_details.assignment_id);
1193
1194
1195 /* Reset all Global Values */
1196 g_fbt_balance_value := 0;
1197 g_allowance_balance_value := 0;
1198
1199
1200 if g_debug then
1201 hr_utility.set_location('Assignment_id' ||l_employee_details.assignment_id,300);
1202 end if;
1203
1204 open get_max_action(l_employee_details.assignment_id,g_start_date,g_end_date,g_tax_unit_id);
1205 fetch get_max_action into l_assignment_action_id,l_action_sequence;
1206
1207 if get_max_action%notfound then
1208 /* No Runs in the Financial Year - FBT Employee*/
1209 l_assignment_action_id := null;
1210 l_action_sequence := null;
1211 end if;
1212 close get_max_action;
1213
1214 if g_debug then
1215 hr_utility.set_location('Assignment_ACTION_id ' ||l_assignment_action_id,300);
1216 end if;
1217
1218 if ( l_employee_details.actual_termination_date is NOT NULL
1219 and l_employee_details.actual_termination_date
1220 between l_lst_fbt_yr_start and g_parameters.fbt_year_end_date
1221 and l_assignment_action_id IS NULL)
1222 then
1223 /* Employee is FBT employee */
1224 /* Archive only FBT Balance - Set all other Balance Values to 0 */
1225
1226 if g_debug
1227 then
1228 hr_utility.set_location('FBT Employee Assignment ID======.....'||l_employee_details.assignment_id,300);
1229 end if;
1230
1231 get_fbt_balance(p_assignment_id => l_employee_details.assignment_id
1232 ,p_start_date => add_months(g_start_date,-3)
1233 ,p_end_date => g_start_date-1
1234 ,p_action_sequence => l_action_sequence
1235 );
1236
1237 if g_debug
1238 then
1239 hr_utility.set_location('FBT Balance Value ======.....'||g_fbt_balance_value,300);
1240 end if;
1241
1242 g_result_table.delete;
1243 FOR i in 1..21
1244 LOOP
1245 g_result_table(i).balance_value := 0;
1246 END LOOP;
1247
1248 l_pre01jul1983_ratio := 0;
1249 l_post30jun1983_ratio := 0;
1250 else
1251 /* Not an FBT Employee - Set all Balances */
1252 get_fbt_balance(p_assignment_id => l_employee_details.assignment_id
1253 ,p_start_date => add_months(g_start_date,-3)
1254 ,p_end_date => add_months(g_end_date,-3)
1255 ,p_action_sequence => l_dummy_action_sequence
1256 );
1257
1258 g_result_table.delete;
1259 g_context_table.delete;
1260
1261 g_context_table(1).tax_unit_id := g_tax_unit_id;
1262
1263 if (l_assignment_action_id is NOT NULL)
1264 then
1265 pay_balance_pkg.get_value(p_assignment_action_id => l_assignment_action_id,
1266 p_defined_balance_lst=>g_balance_value_tab,
1267 p_context_lst =>g_context_table,
1268 p_output_table=>g_result_table);
1269
1270 else
1271 if g_debug then
1272 hr_utility.set_location('Assingment Action ID not found for Run in Year!!',300);
1273 end if;
1274 end if;
1275
1276 if g_debug
1277 then
1278 hr_utility.set_location('FBT Balance Value ======.....'||g_fbt_balance_value,100);
1279 hr_utility.trace('Balance values for YTD dimension');
1280 hr_utility.trace('-------------------------------------');
1281 hr_utility.trace('Earnings_Total ===>'||g_result_table(1).balance_value);
1282 hr_utility.trace('Workplace Giving Deductions ===>' || g_balance_value_tab(21).defined_balance_id);
1283 hr_utility.trace('Direct Payments ===>'||g_result_table(2).balance_value);
1284 hr_utility.trace('Termination_Payments ===>'||g_result_table(3).balance_value);
1285 hr_utility.trace('Involuntary Deductions ===>'||g_result_table(4).balance_value);
1286 hr_utility.trace('Pre Tax Deductions ===>'||g_result_table(5).balance_value);
1287 hr_utility.trace('Termination Deductions ===>'||g_result_table(6).balance_value);
1288 hr_utility.trace('Voluntary Deductions ===>'||g_result_table(7).balance_value);
1289 hr_utility.trace('Total_Tax_Deduction ===>'||g_result_table(8).balance_value);
1290 hr_utility.trace('Earnings_Non_Taxable ===>'||g_result_table(9).balance_value);
1291 hr_utility.trace('Employer_Charges ===>'||g_result_table(10).balance_value);
1292 hr_utility.trace('Lump Sum A Payments ===>'||g_result_table(11).balance_value);
1293 hr_utility.trace('Lump Sum B Payments ===>'||g_result_table(12).balance_value);
1294 hr_utility.trace('Lump Sum C Payments ===>'||g_result_table(13).balance_value);
1295 hr_utility.trace('Lump Sum D Payments ===>'||g_result_table(14).balance_value);
1296 hr_utility.trace('Lump Sum E Payments ===>'||g_result_table(15).balance_value);
1297 hr_utility.trace('Invalidity Payments ===>'||g_result_table(16).balance_value);
1298 hr_utility.trace('CDEP ===>'||g_result_table(17).balance_value);
1299 hr_utility.trace('Leave Payments Marginal ===>'||g_result_table(18).balance_value);
1300 hr_utility.trace('Other Income ===>'||g_result_table(19).balance_value);
1301 hr_utility.trace('Union Fees ===>'||g_result_table(20).balance_value);
1302 end if;
1303
1304 /* Call procedure to Adjust Lump SUM E Payments Value
1305 */
1306 /* Bug 4201894 - Initialize the g_adjusted_lump_sum_e_pay Value */
1307 g_adjusted_lump_sum_e_pay := 0;
1308
1309 Adjust_lumpsum_E_payments(l_employee_details.assignment_id);
1310
1311 if g_debug
1312 then
1313 hr_utility.trace('Lump Sum E Payments ===>'||g_result_table(15).balance_value);
1314 end if;
1315
1316 /* If Lump Sum C Payments get Pre-Post Ratios
1317 */
1318
1319 if (g_result_table(13).balance_value > 0)
1320 then
1321 l_result := pay_au_terminations.etp_prepost_ratios(
1322 l_employee_details.assignment_id -- number in
1323 ,l_employee_details.date_start -- date in
1324 ,l_employee_details.actual_termination_date -- date in
1325 ,'N' -- Bug#2819479 Flag to check whether this function called by Termination Form.
1326 ,l_pre01jul1983_days -- number out
1327 ,l_post30jun1983_days -- number out
1328 ,l_pre01jul1983_ratio -- number out
1329 ,l_post30jun1983_ratio -- number out
1330 ,l_etp_service_date -- date out
1331 ,l_le_etp_service_date
1332 ); -- date out /* Bug 4177679 */
1333
1334 if l_result = 0 then
1335 raise e_prepost_error;
1336 end if;
1337 else
1338 l_pre01jul1983_ratio := 0;
1339 l_post30jun1983_ratio := 0;
1340 end if;
1341
1342 /* Bug 3891577 -
1343 1) Element details archived only for Detail Report
1344 2) If Summary report - Call procedure to populate Allowance balances
1345 Start Bug 3891577
1346 */
1347 l_allowance_exist := 0;
1348 if g_parameters.report_mode ='D' /* Detail Report */
1349 then
1350 archive_element_details(p_assignment_action_id
1351 ,l_employee_details.assignment_id
1352 ,p_effective_date
1353 ,l_pre01jul1983_ratio
1354 ,l_post30jun1983_ratio
1355 ,l_allowance_exist);
1356 else /* Summary Report */
1357 summary_rep_populate_allowance(l_employee_details.assignment_id,
1358 l_allowance_exist);
1359 end if;
1360 /* End Bug 3891577 */
1361
1362 if ( l_allowance_exist > 0)
1363 then
1364 /* Allowance Exists
1365 Calculate and adjust Retro Allowance Values.*/
1366
1367 get_allowance_balances(l_employee_details.assignment_id,l_assignment_action_id);
1368
1369 end if;
1370 end if; /* End of Not an FBT Employee */
1371
1372
1373 archive_balance_details(p_assignment_action_id
1374 ,l_employee_details.assignment_id
1375 ,p_effective_date
1376 ,l_pre01jul1983_ratio
1377 ,l_post30jun1983_ratio
1378 ,l_action_sequence);
1379
1380 if g_debug then
1381 hr_utility.set_location('Leaving '||l_procedure,300);
1382 end if;
1383
1384 end archive_code;
1385
1386 --------------------------------------------------------------------+
1387 -- Name : Get_fbt_balance
1388 -- Type : Procedure
1389 -- Access: Public
1390 -- This procedure archives sets the FBT Balance Value for an
1391 -- Assignment. Based in Input Start/End Dates Max Assignment Action
1392 -- is fetched. FBT Balance Value computed using Globals FBT_Rate
1393 -- and Medicare Levy
1394 --------------------------------------------------------------------+
1395
1396
1397 procedure get_fbt_balance(p_assignment_id in pay_assignment_actions.assignment_id%type
1398 ,p_start_date in date
1399 ,p_end_date in date
1400 ,p_action_sequence out nocopy number)
1401 is
1402 cursor c_max_assignment_action_id(
1403 c_assignment_id pay_assignment_actions.assignment_id%TYPE,
1404 c_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE,
1405 c_start_date date,
1406 c_end_date date)
1407 IS
1408 select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
1409 ,max(paa.action_sequence) action_sequence
1410 from pay_assignment_actions paa
1411 , pay_payroll_actions ppa
1412 , per_assignments_f paf
1413 where paa.assignment_id = paf.assignment_id
1414 and paf.assignment_id = c_assignment_id
1415 and ppa.payroll_action_id = paa.payroll_action_id
1416 and ppa.effective_date between c_start_date and c_end_date
1417 and ppa.payroll_id = paf.payroll_id
1418 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
1419 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
1420 and paa.action_status='C'
1421 AND paa.tax_unit_id = c_tax_unit_id;
1422
1423 CURSOR c_get_global(c_name VARCHAR2
1424 ,c_year_end DATE)
1425 IS
1426 SELECT global_value
1427 FROM ff_globals_f
1428 WHERE global_name = c_name
1429 AND legislation_code = 'AU'
1430 AND c_year_end BETWEEN effective_start_date
1431 AND effective_end_date ;
1432
1433 r_global c_get_global%ROWTYPE;
1434 l_bal_value number;
1435 l_max_assignment_action_id pay_assignment_actions.assignment_action_id%type;
1436 l_max_action_sequence pay_assignment_actions.action_sequence%type;
1437 l_med_levy number;
1438 l_fbt_rate number;
1439
1440 l_procedure varchar2(240);
1441
1442 begin
1443
1444 g_debug := hr_utility.debug_enabled;
1445
1446 if g_debug then
1447 l_procedure := g_package||'.Get_fbt_balance' ;
1448 hr_utility.set_location('Entering '||l_procedure,400);
1449 end if;
1450
1451 open c_max_assignment_action_id(p_assignment_id,g_tax_unit_id,p_start_date,p_end_date);
1452 fetch c_max_assignment_action_id into l_max_assignment_action_id,l_max_action_sequence;
1453 close c_max_assignment_action_id;
1454
1455 if (l_max_assignment_action_id is NOT null)
1456 then
1457 l_bal_value := pay_balance_pkg.get_value(g_fbt_defined_balance_id
1458 ,l_max_assignment_action_id
1459 ,g_tax_unit_id
1460 ,null,null,null,null,null);
1461
1462 /* Bug 4133326 - Added FBT Balance > 1000 Check */
1463 if (l_bal_value <= to_number(g_fbt_threshold)) /* Bug 5708255 */
1464 then
1465 l_bal_value := 0;
1466 end if;
1467 else
1468 l_bal_value := 0;
1469 end if;
1470
1471 open c_get_global('FBT_RATE',add_months(g_end_date,-3)); /* Add_months included for bug 5333143 */
1472 fetch c_get_global into r_global;
1473 close c_get_global;
1474
1475 l_fbt_rate := to_number(r_global.global_value);
1476
1477 open c_get_global('MEDICARE_LEVY',add_months(g_end_date,-3)); /* Add_months included for bug 5333143 */
1478 fetch c_get_global into r_global;
1479 close c_get_global;
1480
1481 l_med_levy := to_number(r_global.global_value);
1482
1483 g_fbt_balance_value := l_bal_value / (1 - (l_fbt_rate + l_med_levy));
1484
1485 if g_debug then
1486 hr_utility.set_location('Fringe Balance Value got := '||l_bal_value,302);
1487 hr_utility.set_location('Value to be archived := '||g_fbt_balance_value,302);
1488 end if;
1489
1490 p_action_sequence := l_max_action_sequence;
1491
1492 if g_debug then
1493 hr_utility.set_location('Leaving '||l_procedure,400);
1494 end if;
1495
1496 end;
1497
1498 --------------------------------------------------------------------+
1499 -- Name : Archive_element_details
1500 -- Type : Procedure
1501 -- Access: Public
1502 -- This procedure identifies all Elements processed for Assignment
1503 -- in Financial Year.
1504 -- Archives all The Lump Sum Payments and ETP payments.
1505 -- In case of Allowance elements,builds a PL/SQL table with
1506 -- the Allowance Balance_Type_ID's
1507 --------------------------------------------------------------------+
1508
1509 procedure archive_element_details(p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE
1510 ,p_assignment_id in pay_assignment_actions.assignment_id%TYPE
1511 ,p_effective_date in date
1512 ,p_pre01jul1983_ratio in number
1513 ,p_post30jun1983_ratio in number
1514 ,p_allowance_exists out nocopy number)
1515 is
1516
1517 --------------------------------------------------------------------+
1518 -- Cursor : c_ps_element_details
1519 -- Description : Fetches all Elements processed for Assignment.
1520 -- Ignores elements feeding Lump Sum Payment Balances
1521 -- and Invalidity Payments.
1522 --------------------------------------------------------------------+
1523 /* Bug 4179109 - Check for Non Taxable Allowances included */
1524 /* Bug 5063359 - Modified decode for Employer Charges to return classification as Employer Charges rather than Employer Superannuation Contribution */
1525 /* Bug 5119734 - Modified decode to check for Allowances if classifcation is Earnings */
1526 /*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 */
1527 /*Bug 5846278 - Added Lump Sum E Payments in Not exists Clause */
1528
1529 cursor c_ps_element_details
1530 (c_assignment_id pay_assignment_actions.assignment_id%TYPE,
1531 c_business_group_id hr_all_organization_units.organization_id%TYPE,
1532 c_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE,
1533 c_start_date date,
1534 c_end_date date)
1535 is
1536 select element_name,label classification_name,sum(amount) payment,sum(hours) hours,rate,balance_type_id
1537 from
1538 (select distinct
1539 nvl(pet.reporting_name, pet.element_name) element_name,
1540 decode(instr(pec.classification_name, 'Earnings'), 0, null,
1541 decode(pet.element_information1,'Y','Allowance',decode(pec2.classification_name, 'Non Taxable', 'Non Taxable Earnings','Taxable Earnings')))|| /* Bug 4179109, 5119734*/
1542 decode(instr(pec.classification_name, 'Payments'), 0, null,
1543 decode(instr(pec.classification_name, 'Direct'), 0, 'Taxable Earnings', 'Direct Payments')) ||
1544 decode(instr(pec.classification_name, 'Deductions'), 0, null,
1545 decode(pec.classification_name , 'Termination Deductions' , 'Tax Deductions'
1546 , 'Involuntary Deductions' , 'Post Tax Deductions'
1547 , 'Voluntary Deductions' , 'Post Tax Deductions'
1548 , pec.classification_name )) ||
1549 decode(instr(pec.classification_name, 'Employer Charges'), 0,null,'Employer Charges') label,
1550 prrv1.result_value amount,
1551 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 */
1552 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,
1553 (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)),
1554 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 */
1555 prr.run_result_id,
1556 paa.source_action_id
1557 ,nvl(pet.element_information2,0) balance_type_id
1558 from pay_element_types_f pet
1559 ,pay_input_values_f piv1
1560 ,pay_element_classifications pec
1561 ,pay_assignment_actions paa
1562 ,pay_payroll_actions ppa
1563 ,per_assignments_f paaf
1564 ,pay_run_results prr
1565 ,pay_run_result_values prrv1
1566 ,pay_element_classifications pec2
1567 ,pay_sub_classification_rules_f pscr
1568 where pet.element_type_id = piv1.element_type_id
1569 and pet.element_type_id = prr.element_type_id
1570 and prr.assignment_action_id = paa.assignment_action_id
1571 and paaf.assignment_id = paa.assignment_id
1572 and paaf.business_group_id = c_business_group_id
1573 and prr.run_result_id = prrv1.run_result_id
1574 and prrv1.input_value_id = piv1.input_value_id
1575 and pet.classification_id = pec.classification_id
1576 and pec.legislation_code = 'AU'
1577 and paaf.assignment_id = c_assignment_id
1578 and paaf.payroll_id = ppa.payroll_id
1579 and ppa.effective_date between c_start_date and c_end_date
1580 and ppa.action_type in ('Q','R','I','B','V')
1581 and paa.action_status = 'C'
1582 and paa.tax_unit_id like c_tax_unit_id
1583 and paa.payroll_action_id = ppa.payroll_action_id
1584 and piv1.name = 'Pay Value'
1585 and pet.classification_id = pec.classification_id
1586 and (instr(pec.classification_name, 'Earnings') > 0
1587 or instr(pec.classification_name, 'Payments') > 0
1588 or instr(pec.classification_name, 'Deductions') > 0
1589 or instr(pec.classification_name, 'Employer Charges' ) > 0 )
1590 and pet.element_type_id = pscr.element_type_id (+)
1591 and ppa.effective_date between nvl(pscr.effective_start_date, ppa.effective_date)
1592 and nvl(pscr.effective_end_date, ppa.effective_date)
1593 and pscr.classification_id = pec2.classification_id(+)
1594 and ppa.date_earned between pet.effective_start_date and pet.effective_end_date
1595 and ppa.effective_date between paaf.effective_start_date and paaf.effective_end_date
1596 and prr.status in ('P','PA')
1597 and NOT EXISTS
1598 (
1599 select 1
1600 from pay_run_results prr1,
1601 pay_element_types_f pet1
1602 where prr1.assignment_action_id = paa.assignment_action_id
1603 and prr1.element_type_id = pet1.element_type_id
1604 and pet1.element_name in ('Retropay GT 12 Mths Amount')
1605 and prr1.source_id = prr.source_id
1606 and prr.source_type='E' /*Bug 4363057 */
1607 )
1608 and NOT EXISTS
1609 (
1610 select 1
1611 from pay_balance_feeds_f pbf,
1612 pay_balance_types pbt
1613 where pbt.balance_type_id = pbf.balance_type_id
1614 and pbt.balance_name in ('Invalidity Payments','Lump Sum A Payments',
1615 'Lump Sum B Payments','Lump Sum C Payments',
1616 'Lump Sum D Payments','Lump Sum E Payments')/*Bug 5846278 */
1617 and pbf.input_value_id = piv1.input_value_id
1618 )
1619 )
1620 group by balance_type_id,element_name,label,rate;
1621
1622
1623 TYPE char_tab_type IS TABLE OF VARCHAR2(350) INDEX BY BINARY_INTEGER;
1624 TYPE num_tab_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1625
1626 l_counter number;
1627
1628 l_prev_bal_type_id number;
1629 l_procedure varchar2(240);
1630
1631 l_ele_name char_tab_type;
1632 l_ele_pay_value num_tab_type;
1633 l_ele_classification_name char_tab_type;
1634
1635 counter number;
1636
1637 begin
1638
1639 g_debug := hr_utility.debug_enabled;
1640 l_counter := 0;
1641 l_prev_bal_type_id := 0;
1642
1643 g_balance_type_tab.delete;
1644
1645 if g_debug
1646 then
1647 l_procedure := g_package||'Element_Details';
1648 hr_utility.set_location('Entering ' || l_procedure,500);
1649 end if;
1650
1651 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)
1652 LOOP
1653
1654 if g_debug
1655 then
1656 hr_utility.set_location('Element Name '||csr_ele_det.element_name,500);
1657 hr_utility.set_location('Classification Name '||csr_ele_det.classification_name,500);
1658 end if;
1659
1660 /* In case of Allowance,store the Balance Type ID's in PL/SQL table.
1661 Ensure the Balance Type ID is not duplicated in table.
1662 */
1663 if (csr_ele_det.classification_name = 'Allowance')
1664 then
1665 if (csr_ele_det.balance_type_id is not null and l_prev_bal_type_id <> csr_ele_det.balance_type_id )
1666 then
1667 l_counter := l_counter + 1;
1668 g_balance_type_tab(l_counter) := to_number(csr_ele_det.balance_type_id);
1669 l_prev_bal_type_id := csr_ele_det.balance_type_id;
1670 end if;
1671 end if;
1672 insert into pay_action_information (
1673 action_information_id,
1674 action_context_id,
1675 action_context_type,
1676 effective_date,
1677 source_id,
1678 tax_unit_id,
1679 action_information_category,
1680 action_information1,
1681 action_information2,
1682 action_information3,
1683 action_information4,
1684 action_information5,
1685 action_information6,
1686 assignment_id)
1687 values (
1688 pay_action_information_s.nextval,
1689 p_assignment_action_id,
1690 'AAP',
1691 p_effective_date,
1692 null,
1693 g_tax_unit_id,
1694 'AU_ELEMENT_RECON_DETAILS',
1695 csr_ele_det.element_name,
1696 csr_ele_det.classification_name,
1697 null,
1698 csr_ele_det.hours,
1699 csr_ele_det.rate,
1700 csr_ele_det.payment,
1701 p_assignment_id);
1702
1703 if g_debug then
1704 hr_utility.set_location('After Inserting Element Values ',500);
1705 end if;
1706 END LOOP;
1707
1708 p_allowance_exists := l_counter ;/* To Show Allowances Exist */
1709
1710 /* Now Archive Lump Sum Payments and Invalidity */
1711 /* Use g_result_values_tab to get the Values
1712 'Lump Sum A Payments ===>' g_result_table(11).balance_value
1713 'Lump Sum B Payments ===>' g_result_table(12).balance_value
1714 'Lump Sum C Payments ===>' g_result_table(13).balance_value
1715 'Lump Sum D Payments ===>' g_result_table(14).balance_value
1716 'Lump Sum E Payments ===>' g_result_table(15).balance_value
1717 'Invalidity Payments ===>' g_result_table(16).balance_value
1718 */
1719
1720 if g_debug then
1721 hr_utility.set_location('p_pre01jul1983_ratio........=>'||p_pre01jul1983_ratio,200);
1722 hr_utility.set_location('p_post30jun1983_ratio........=>'||p_post30jun1983_ratio,200);
1723 end if;
1724
1725 counter := 0;
1726 if (g_result_table(11).balance_value > 0)
1727 then
1728 counter := counter + 1;
1729 l_ele_name(counter) := 'Lump Sum A Payment';
1730 l_ele_pay_value(counter) := g_result_table(11).balance_value;
1731 l_ele_classification_name(counter) := 'Lump Sum A Payments';
1732 end if;
1733 if (g_result_table(12).balance_value > 0)
1734 then
1735 counter := counter + 1;
1736 l_ele_name(counter) := 'Lump Sum B Payment';
1737 l_ele_pay_value(counter) := g_result_table(12).balance_value;
1738 l_ele_classification_name(counter) := 'Lump Sum B Payments';
1739 end if;
1740 if (g_result_table(13).balance_value > 0)
1741 then
1742 counter := counter + 1;
1743 l_ele_name(counter) := 'Pre Jul 83';
1744 l_ele_pay_value(counter) := (g_result_table(13).balance_value * p_pre01jul1983_ratio);
1745 l_ele_classification_name(counter) := 'Lump Sum C Payments';
1746 counter := counter + 1;
1747 l_ele_name(counter) := 'Post Jun 83 untaxed';
1748 l_ele_pay_value(counter) := (g_result_table(13).balance_value * p_post30jun1983_ratio);
1749 l_ele_classification_name(counter) := 'Lump Sum C Payments';
1750 counter := counter + 1;
1751 l_ele_name(counter) := 'Post Jun 83 taxed';
1752 l_ele_pay_value(counter) := 0;
1753 l_ele_classification_name(counter) := 'Lump Sum C Payments';
1754 end if;
1755 if (g_result_table(14).balance_value > 0)
1756 then
1757 counter := counter + 1;
1758 l_ele_name(counter) := 'Lump Sum D Payment';
1759 l_ele_pay_value(counter) := g_result_table(14).balance_value;
1760 l_ele_classification_name(counter) := 'Lump Sum D Payments';
1761 end if;
1762 if (g_result_table(15).balance_value > 0)
1763 then
1764 counter := counter + 1;
1765 l_ele_name(counter) := 'Lump Sum E Payment';
1766 l_ele_pay_value(counter) := g_result_table(15).balance_value;
1767 l_ele_classification_name(counter) := 'Lump Sum E Payments';
1768 end if;
1769 if (g_result_table(16).balance_value > 0)
1770 then
1771 counter := counter + 1;
1772 l_ele_name(counter) := 'Jun 94 Invalidity';
1773 l_ele_pay_value(counter) := g_result_table(16).balance_value;
1774 l_ele_classification_name(counter) := 'Invalidity Payments';
1775 end if;
1776 /* Bug 4201894 - Archive Adjusted Lump Sum E Amount (< $400 in a period)
1777 as Taxable Earnings*/
1778 if (g_adjusted_lump_sum_e_pay > 0)
1779 then
1780 counter := counter + 1;
1781 l_ele_name(counter) := 'Retro Payment < $400';
1782 l_ele_pay_value(counter) := g_adjusted_lump_sum_e_pay ;
1783 l_ele_classification_name(counter) := 'Taxable Earnings';
1784 end if;
1785 /* End Bug 4201894 */
1786
1787
1788 if (counter >= 1)
1789 then
1790 for i in 1..counter
1791 loop
1792
1793 insert into pay_action_information (
1794 action_information_id,
1795 action_context_id,
1796 action_context_type,
1797 effective_date,
1798 source_id,
1799 tax_unit_id,
1800 action_information_category,
1801 action_information1,
1802 action_information2,
1803 action_information3,
1804 action_information4,
1805 action_information5,
1806 action_information6,
1807 assignment_id)
1808 values (
1809 pay_action_information_s.nextval,
1810 p_assignment_action_id,
1811 'AAP',
1812 p_effective_date,
1813 null,
1814 g_tax_unit_id,
1815 'AU_ELEMENT_RECON_DETAILS',
1816 l_ele_name(i),
1817 l_ele_classification_name(i),
1818 null,
1819 null,
1820 null,
1821 l_ele_pay_value(i),
1822 p_assignment_id);
1823 end loop;
1824 end if;
1825
1826 if g_debug then
1827 hr_utility.set_location('Leaving '||l_procedure,500 );
1828 end if;
1829
1830 end archive_element_details;
1831
1832 --------------------------------------------------------------------+
1833 -- Name : Adjust_lumpsum_E_payments
1834 -- Type : Procedure
1835 -- Access: Public
1836 -- This procedure identifies all Runs in a period for the Assignment
1837 -- in Financial Year and check is Lump Sum E Payment PTD < $400.In
1838 -- that case the YTD value is adjusted for the same.
1839 --------------------------------------------------------------------+
1840 /* Bug 4201894 - Adjusted Amount will be cumulatively stored in
1841 g_adjusted_lump_sum_e_pay and displayed in Taxable Earnings
1842 element section */
1843 --------------------------------------------------------------------+
1844 procedure Adjust_lumpsum_E_payments(p_assignment_id in pay_assignment_actions.assignment_id%type)
1845 is
1846 CURSOR c_single_lumpsum_E_payment(c_assignment_action_id pay_assignment_actions.assignment_action_id%type,
1847 c_tax_unit_id pay_assignment_actions.tax_unit_id%type)
1848 IS
1849 select sum(pay_balance_pkg.get_value(pdb.defined_balance_id,
1850 c_assignment_action_id,
1851 c_tax_unit_id,
1852 null,null,null,null)) --2610141
1853 FROM pay_balance_types pbt,
1854 pay_defined_balances pdb,
1855 pay_balance_dimensions pbd
1856 WHERE pbt.legislation_code = 'AU'
1857 AND pbt.balance_name = 'Lump Sum E Payments'
1858 AND pbt.balance_type_id = pdb.balance_type_id
1859 AND pbd.balance_dimension_id = pdb.balance_dimension_id
1860 AND pbd.dimension_name = '_ASG_LE_PTD';
1861
1862 /*Bug 4363057 - Cursor has been modified so that the Lump Sum E Payments given to previous legal employers
1863 can be taken into account while calculating payment summary gross.*/
1864
1865 CURSOR c_get_pay_effective_date(c_assignment_id pay_assignment_actions.assignment_id%type
1866 ,c_year_start in DATE
1867 ,c_year_end in DATE
1868 ,c_tax_unit_id pay_assignment_actions.tax_unit_id%type )
1869 IS
1870 select max(paa.assignment_action_id) assignment_action_id
1871 from per_assignments_f paf,
1872 pay_payroll_Actions ppa,
1873 pay_assignment_Actions paa,
1874 per_time_periods ptp
1875 where ppa.payroll_Action_id = paa.payroll_Action_id
1876 and paa.assignment_id = c_assignment_id
1877 and paf.assignment_id = paa.assignment_id
1878 and paa.tax_unit_id = c_tax_unit_id
1879 and action_type in ('Q','R','V')
1880 AND (paa.source_action_id IS NULL
1881 OR (paa.source_action_id IS NOT NULL AND ppa.run_type_id IS NULL)) /*Bug 4363057*/
1882 and ppa.effective_date between c_year_start and c_year_end
1883 AND ptp.time_period_id = ppa.time_period_id
1884 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
1885 GROUP BY ptp.time_period_id;
1886
1887
1888 l_procedure varchar2(240);
1889 v_lump_sum_E_ytd number;
1890 v_lump_sum_E_ptd number;
1891 v_adj_lump_sum_E_ptd number; /* Bug 4201894 */
1892
1893 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
1894
1895
1896 begin
1897
1898 g_debug := hr_utility.debug_enabled;
1899 if g_debug then
1900 l_procedure := 'Adjust_lumpsum_E_payments';
1901 hr_utility.set_location('In procedure '||g_package||l_procedure,600);
1902 end if;
1903
1904 /*'Lump Sum E Payments ===>' g_result_table(15).balance_value */
1905
1906 v_lump_sum_E_ytd := g_result_table(15).balance_value;
1907 v_adj_lump_sum_E_ptd := 0 ; /* Bug 4201894*/
1908
1909 if v_lump_sum_E_ytd <> 0 then
1910 FOR csr_eff_date in c_get_pay_effective_date(p_assignment_id
1911 ,g_start_date
1912 ,g_end_date
1913 ,g_tax_unit_id)
1914 LOOP
1915 open c_single_lumpsum_E_payment(csr_eff_date.assignment_action_id,g_tax_unit_id);
1916 fetch c_single_lumpsum_E_payment into v_lump_sum_E_ptd;
1917
1918 if v_lump_sum_E_ptd < 400 then
1919 v_lump_sum_E_ytd := v_lump_sum_E_ytd - v_lump_sum_E_ptd;
1920 v_adj_lump_sum_E_ptd := v_adj_lump_sum_E_ptd + v_lump_sum_E_ptd ; /* Bug 4201894 */
1921 end if;
1922 close c_single_lumpsum_E_payment;
1923 END LOOP;
1924 end if;
1925 /* Adjust the Balance Value - */
1926
1927 g_result_table(15).balance_value := v_lump_sum_E_ytd;
1928 g_adjusted_lump_sum_e_pay := v_adj_lump_sum_E_ptd; /* Bug 4201894*/
1929
1930 if g_debug then
1931 hr_utility.set_location('In procedure '||g_package||l_procedure,600);
1932 end if;
1933 end;
1934
1935 --------------------------------------------------------------------+
1936 -- Name : Get_allowance_balances
1937 -- Type : Procedure
1938 -- Access: Public
1939 -- Reads the PL/SQL table populated by Elements procedure for
1940 -- Allowances. Procedure Fetches the balance values and adjusts any
1941 -- Retro Payments.
1942 --------------------------------------------------------------------+
1943
1944
1945 procedure get_allowance_balances(p_assignment_id in pay_assignment_actions.assignment_id%type
1946 ,p_run_assignment_action_id in pay_assignment_actions.assignment_action_id%type)
1947 is
1948
1949 cursor c_allowance_balance(c_balance_type_id pay_balance_types.balance_type_id%type)
1950 is
1951 select nvl(pbt.reporting_name,pbt.balance_name) balance_name, /* Bug 5743196 added nvl */
1952 pdb.defined_balance_id
1953 from pay_balance_types pbt
1954 ,pay_defined_balances pdb
1955 ,pay_balance_dimensions pbd
1956 where pbt.balance_type_id = c_balance_type_id
1957 and pdb.balance_type_id = pbt.balance_type_id
1958 and pdb.balance_dimension_id = pbd.balance_dimension_id
1959 and pbd.dimension_name = '_ASG_LE_YTD'
1960 and pbd.legislation_code ='AU';
1961
1962
1963 allow_balance_value_tab pay_balance_pkg.t_balance_value_tab; /* 4177630 */
1964 allow_context_table pay_balance_pkg.t_context_tab; /* 4177630 */
1965 allow_result_table pay_balance_pkg.t_detailed_bal_out_tab; /* 4177630 */
1966
1967 tab_allownace_balance pay_au_payment_summary.t_allowance_balance%TYPE;
1968
1969 l_YTD_allowance number;
1970 l_result number;
1971 l_procedure varchar2(240);
1972
1973
1974 begin
1975
1976 g_debug := hr_utility.debug_enabled;
1977
1978 if g_debug then
1979 l_procedure := g_package||'.Get_allowance_balances';
1980 hr_utility.set_location('Entering '||l_procedure,700);
1981 end if;
1982
1983 for i in g_balance_type_tab.FIRST..g_balance_type_tab.LAST
1984 loop
1985 if g_debug then
1986 hr_utility.set_location('g_balance_type_tab(i) '||g_balance_type_tab(i),700);
1987 end if;
1988
1989 open c_allowance_balance(g_balance_type_tab(i));
1990 fetch c_allowance_balance into tab_allownace_balance(i).balance_name, allow_balance_value_tab(i).defined_balance_id; /* 4177630 */
1991 IF c_allowance_balance%NOTFOUND THEN
1992 hr_utility.set_location('Defined balanced id not found for balance type id :'||g_balance_type_tab(i),700); /* 4177630 */
1993 raise_application_error(-20101, 'Allowance Balance Type ' || g_balance_type_tab(i) || ' not associated with dimension _ASG_LE_YTD'); /* 4177630 */
1994 END IF;
1995 close c_allowance_balance;
1996
1997 end loop;
1998
1999 /* 4177630 - Modification for BBR call Starts */
2000
2001 allow_result_table.delete;
2002 allow_context_table.delete;
2003
2004 allow_context_table(1).tax_unit_id := g_tax_unit_id;
2005
2006 if (p_run_assignment_action_id is NOT NULL)
2007 then
2008 pay_balance_pkg.get_value(p_assignment_action_id => p_run_assignment_action_id,
2009 p_defined_balance_lst=>allow_balance_value_tab,
2010 p_context_lst =>allow_context_table,
2011 p_output_table=>allow_result_table);
2012
2013 else
2014 if g_debug then
2015 hr_utility.set_location('Assingment Action ID not found for Run in Year!!',300);
2016 end if;
2017 end if;
2018
2019 for i in g_balance_type_tab.FIRST..g_balance_type_tab.LAST
2020 loop
2021 if g_debug then
2022 hr_utility.set_location('g_balance_type_tab(i) '||g_balance_type_tab(i),700);
2023 end if;
2024
2025 tab_allownace_balance(i).balance_value := allow_result_table(i).balance_value;
2026
2027 end loop;
2028
2029 /* 4177630 - Modification end here*/
2030 l_result := pay_au_payment_summary.adjust_retro_allowances(tab_allownace_balance,
2031 g_start_date,
2032 g_end_date,
2033 p_assignment_id,
2034 g_tax_unit_id);
2035 l_YTD_allowance := 0;
2036
2037 for i in tab_allownace_balance.FIRST..tab_allownace_balance.LAST
2038 loop
2039 l_YTD_allowance := l_YTD_allowance + nvl(tab_allownace_balance(i).balance_value,0);
2040 end loop;
2041
2042 g_allowance_balance_value := l_YTD_allowance;
2043
2044 if g_debug then
2045 hr_utility.set_location('Set the Global Balance Value '||l_YTD_allowance,700);
2046 hr_utility.set_location('Leaving '||l_procedure,700);
2047 end if;
2048 end;
2049
2050 --------------------------------------------------------------------+
2051 -- Name : Archive_balance_details
2052 -- Type : Procedure
2053 -- Access: Public
2054 -- Computes and Archives all the Balances in table
2055 -- pay_action_information with context,
2056 -- Normal Balances - AU_BALANCE_RECON_DETAILS_YTD
2057 -- Payment Summary Balances - AU_PS_BALANCE_RECON_DETAILS
2058 --------------------------------------------------------------------+
2059
2060 procedure archive_balance_details(p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE
2061 ,p_assignment_id in pay_assignment_actions.assignment_id%TYPE
2062 ,p_effective_date in date
2063 ,p_pre01jul1983_ratio in number
2064 ,p_post30jun1983_ratio in number
2065 ,p_run_action_sequence in pay_assignment_actions.action_sequence%type)
2066 is
2067 l_procedure varchar2(80);
2068
2069 l_YTD_TAXABLE_EARNINGS number;
2070 l_YTD_NON_TAXABLE_EARNINGS number;
2071 l_YTD_GROSS_EARNINGS number;
2072 l_YTD_PRE_TAX_DEDUCTIONS number;
2073 l_YTD_DIRECT_PAYMENTS number;
2074 l_YTD_DEDUCTIONS number;
2075 l_YTD_TAX number;
2076 l_YTD_NET_PAYMENT number;
2077 l_YTD_EMPLOYER_CHARGES number;
2078
2079 l_YTD_PAYSUM_GROSS number;
2080 l_YTD_LUMPSUM_PAY number ;
2081 l_YTD_ALLOWANCE number;
2082 l_YTD_RFB number;
2083 l_ETP_PAY number;
2084 l_ASSESSABLE_ETP number;
2085 l_YTD_CDEP number;
2086 l_YTD_OTHER_INCOME number;
2087 l_YTD_UNION_FEES number;
2088 l_YTD_WORKPLACE_GIVING_DED number; /*4085496 */
2089
2090 begin
2091
2092 g_debug := hr_utility.debug_enabled;
2093 if g_debug then
2094 l_procedure := 'archive_balance_details';
2095 hr_utility.set_location('In procedure '||g_package||l_procedure,800);
2096 end if;
2097 /*
2098 ===============================================================================================
2099 Balances for Archive are computed in the following manner,
2100
2101 l_YTD_GROSS_EARNINGS = Earnings Total + Termination Payments + Pre Tax Deductions
2102 l_YTD_TAXABLE_EARNINGS = Gross Earnings - Pre_tax_deductions - Earnings_non_taxable
2103 l_YTD_NON_TAXABLE_EARNINGS = Earnings_non_taxable
2104 l_YTD_PRE_TAX_DEDUCTIONS = Pre_tax_deductions
2105 l_YTD_DIRECT_PAYMENTS = Direct_Payments
2106 l_YTD_DEDUCTIONS = Involuntary_deductions + Voluntary_deductions
2107 l_YTD_TAX = Tax_deductions + Termination_deductions
2108 l_YTD_NET_PAYMENT = Taxable_earnings + Non_taxable_earnings - Tax - Deductions + Direct_Payments
2109 l_YTD_EMPLOYER_CHARGES = Employer_charges
2110
2111 l_YTD_PAYSUM_GROSS = Earnings_Total + Leave Payments Marginal - (All Allowance) - CDEP - Other Income - Lump Sum E Payments + Workplace Giving Deductions. - 4085496
2112 l_YTD_LUMPSUM_PAY = Lump Sum A Payments + Lump Sum B Payments + Lump Sum D Payments
2113 + Lump Sum E Payments
2114 l_YTD_ALLOWANCE = Allowances YTD
2115 l_YTD_RFB = Fringe Benefits /(1 - (FBT Rate + Med Levy))
2116 l_ETP_PAY = (Lump Sum C Payments + p_pre01jul1983_ratio) +
2117 (Lump Sum C Payments + p_post30jun1983_ratio) +
2118 Invalidity Payments
2119 l_ASSESSABLE_ETP = (Lump Sum C Payments + p_post30jun1983_ratio) +
2120 l_YTD_CDEP = CDEP
2121 l_YTD_OTHER_INCOME = Other Income
2122 l_YTD_UNION_FEES = Union Fees
2123 l_YTD_WORKPLACE_GIVING_DED = Workplace Giving Deductions - 4085496
2124 ===============================================================================================
2125 */
2126
2127
2128 l_YTD_GROSS_EARNINGS := g_result_table(1).balance_value + g_result_table(3).balance_value + g_result_table(5).balance_value;
2129 l_YTD_TAXABLE_EARNINGS := l_YTD_GROSS_EARNINGS - g_result_table(5).balance_value - g_result_table(9).balance_value ;
2130 l_YTD_NON_TAXABLE_EARNINGS := g_result_table(9).balance_value;
2131 l_YTD_PRE_TAX_DEDUCTIONS := g_result_table(5).balance_value;
2132 l_YTD_DIRECT_PAYMENTS := g_result_table(2).balance_value;
2133 l_YTD_DEDUCTIONS := g_result_table(4).balance_value + g_result_table(7).balance_value ;
2134 l_YTD_TAX := g_result_table(8).balance_value + g_result_table(6).balance_value ;
2135 l_YTD_NET_PAYMENT := l_YTD_TAXABLE_EARNINGS + l_YTD_NON_TAXABLE_EARNINGS - l_YTD_TAX - l_YTD_DEDUCTIONS + l_YTD_DIRECT_PAYMENTS ;
2136 l_YTD_EMPLOYER_CHARGES := g_result_table(10).balance_value ;
2137
2138 l_YTD_PAYSUM_GROSS := g_result_table(1).balance_value + g_result_table(18).balance_value
2139 + g_result_table(21).balance_value
2140 - g_allowance_balance_value
2141 - g_result_table(17).balance_value - g_result_table(19).balance_value
2142 - g_result_table(15).balance_value ;/* $400 Check Adjusted Value */
2143 l_YTD_LUMPSUM_PAY := g_result_table(11).balance_value + g_result_table(12).balance_value + g_result_table(14).balance_value
2144 + g_result_table(15).balance_value ;
2145 l_YTD_ALLOWANCE := g_allowance_balance_value;
2146 l_YTD_RFB := g_fbt_balance_value ;
2147 l_ETP_PAY := (g_result_table(13).balance_value * p_pre01jul1983_ratio)
2148 +(g_result_table(13).balance_value * p_post30jun1983_ratio)
2149 + g_result_table(16).balance_value;
2150 l_ASSESSABLE_ETP := round((g_result_table(13).balance_value * p_post30jun1983_ratio),2); /* Bug 4872594 - Added round off */
2151 /* Bug No : 7030285 - Assessable Income modified */
2152 l_YTD_CDEP := g_result_table(17).balance_value;
2153 l_YTD_OTHER_INCOME := g_result_table(19).balance_value;
2154 l_YTD_UNION_FEES := g_result_table(20).balance_value;
2155 l_YTD_WORKPLACE_GIVING_DED := g_result_table(21).balance_value; /* 4085496 */
2156
2157 insert into pay_action_information (
2158 action_information_id,
2159 action_context_id,
2160 action_context_type,
2161 effective_date,
2162 source_id,
2163 tax_unit_id,
2164 assignment_id,
2165 action_information_category,
2166 action_information1,
2167 action_information2,
2168 action_information3,
2169 action_information4,
2170 action_information5,
2171 action_information6,
2172 action_information7,
2173 action_information8,
2174 action_information9,
2175 action_information10
2176 )
2177 values (
2178 pay_action_information_s.nextval,
2179 p_assignment_action_id,
2180 'AAP',
2181 p_effective_date,
2182 null,
2183 g_tax_unit_id,
2184 p_assignment_id,
2185 'AU_BALANCE_RECON_DETAILS_YTD',
2186 l_YTD_TAXABLE_EARNINGS,
2187 l_YTD_NON_TAXABLE_EARNINGS,
2188 l_YTD_DEDUCTIONS,
2189 l_YTD_TAX,
2190 l_YTD_NET_PAYMENT,
2191 l_YTD_EMPLOYER_CHARGES,
2192 l_YTD_GROSS_EARNINGS,
2193 l_YTD_PRE_TAX_DEDUCTIONS,
2194 l_YTD_DIRECT_PAYMENTS,
2195 p_run_action_sequence);
2196
2197 insert into pay_action_information (
2198 action_information_id,
2199 action_context_id,
2200 action_context_type,
2201 effective_date,
2202 source_id,
2203 tax_unit_id,
2204 assignment_id,
2205 action_information_category,
2206 action_information1,
2207 action_information2,
2208 action_information3,
2209 action_information4,
2210 action_information5,
2211 action_information6,
2212 action_information7,
2213 action_information8,
2214 action_information9,
2215 action_information10,
2216 action_information11
2217 )
2218 values (
2219 pay_action_information_s.nextval,
2220 p_assignment_action_id,
2221 'AAP',
2222 p_effective_date,
2223 null,
2224 g_tax_unit_id,
2225 p_assignment_id,
2226 'AU_PS_BALANCE_RECON_DETAILS',
2227 l_YTD_PAYSUM_GROSS,
2228 l_YTD_LUMPSUM_PAY,
2229 l_YTD_ALLOWANCE,
2230 l_YTD_RFB,
2231 l_ETP_PAY,
2232 l_ASSESSABLE_ETP,
2233 l_YTD_CDEP,
2234 l_YTD_OTHER_INCOME,
2235 l_YTD_UNION_FEES,
2236 p_run_action_sequence,
2237 L_YTD_WORKPLACE_GIVING_DED); /* 4015082 */
2238
2239 if g_debug then
2240 hr_utility.set_location('Leaving '||g_package||l_procedure,800);
2241 end if;
2242
2243 end archive_balance_details;
2244
2245 --------------------------------------------------------------------+
2246 -- Name : spawn_archive_reports
2247 -- Type : Procedure
2248 -- Access: Public
2249 -- This procedure calls the Detail report
2250 -- Using the parameters passed, this proc calls the Reconciliation
2251 -- Detail report.
2252 -- This proc is called as deinitialization code of archive process.
2253
2254 --------------------------------------------------------------------+
2255
2256 procedure spawn_archive_reports
2257 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type)
2258 is
2259 l_count number;
2260 ps_request_id NUMBER;
2261 l_print_style VARCHAR2(2);
2262 l_print_together VARCHAR2(80);
2263 l_print_return BOOLEAN;
2264 l_procedure varchar2(50);
2265 l_short_report_name VARCHAR2(30); /* 6839263 */
2266 l_xml_options BOOLEAN ; /* 6839263 */
2267 --------------------------------------------------------------------+
2268 -- Cursor : csr_params
2269 -- Description : Fetches User Parameters from Legislative_paramters
2270 -- column.
2271 --------------------------------------------------------------------+
2272
2273 CURSOR csr_report_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
2274 IS
2275 select pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters) business_group_id
2276 ,pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters) legal_employer
2277 ,pay_core_utils.get_parameter('PAYROLL',legislative_parameters) payroll_id
2278 ,pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters) assignment_id
2279 ,pay_core_utils.get_parameter('EMPLOYEE_TYPE',legislative_parameters) employee_type
2280 ,to_date('01-07-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fin_year_start_date
2281 ,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),6,4),'DD-MM-YYYY') fin_year_end_date
2282 ,to_date('01-04-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_start_date
2283 ,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_end_date
2284 ,pay_core_utils.get_parameter('LST_YR_TERM',legislative_parameters) lst_year_term
2285 ,pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions /*Bug 4142159*/
2286 ,decode(pay_core_utils.get_parameter('REP_MODE',legislative_parameters),'SUMM','S','D') report_mode /* Bug 3891577*/
2287 ,pay_core_utils.get_parameter('OUTPUT_TYPE',legislative_parameters)p_output_type
2288 from pay_payroll_actions
2289 where payroll_action_id = c_payroll_action_id;
2290
2291
2292 cursor csr_get_print_options(p_payroll_action_id NUMBER) IS
2293 SELECT printer,
2294 print_style,
2295 decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output
2296 ,number_of_copies /* Bug 4116833 */
2297 FROM pay_payroll_actions pact,
2298 fnd_concurrent_requests fcr
2299 WHERE fcr.request_id = pact.request_id
2300 AND pact.payroll_action_id = p_payroll_action_id;
2301
2302 /* Declaration - Report Flags to be SET */
2303 l_paysum_flag varchar2(2);
2304 l_ytd_totals varchar2(2);
2305 l_negative_records varchar2(2);
2306 l_zero_records varchar2(2);
2307
2308 l_dummy varchar2(240);
2309
2310 rec_print_options csr_get_print_options%ROWTYPE;
2311
2312 l_parameters csr_report_params%ROWTYPE; /* Bug 6839263 */
2313
2314 begin
2315 l_count :=0;
2316 ps_request_id :=-1;
2317 g_debug :=hr_utility.debug_enabled ;
2318
2319 if g_debug then
2320 l_procedure := g_package||' spawn_archive_reports';
2321 hr_utility.set_location('Entering '||l_procedure,999);
2322 end if;
2323
2324 -- Set User Parameters for Report.
2325
2326 open csr_report_params(p_payroll_action_id);
2327 fetch csr_report_params into l_parameters;
2328 close csr_report_params;
2329
2330 /* Start Bug 6839263 */
2331 IF l_parameters.p_output_type = 'XML_PDF'
2332 THEN
2333 l_short_report_name := 'PYAUREPSR_XML';
2334
2335 l_xml_options := fnd_request.add_layout
2336 (template_appl_name => 'PAY',
2337 template_code => 'PYAUREPSR_XML',
2338 template_language => 'en',
2339 template_territory => 'US',
2340 output_format => 'PDF');
2341
2342 ELSE
2343 l_short_report_name := 'PYAUREPSR';
2344 END IF;
2345 /* End Bug 6839263 */
2346
2347
2348 --Set REPORT FLAGS values.
2349 l_paysum_flag := 'Y'; /*Indicate Payment Summary Report*/
2350 l_ytd_totals := 'Y'; /* YTD Balances to be displayed */
2351 l_negative_records := 'N'; /* Do not Suppress Records with Negative Earnings */
2352 l_zero_records:= 'N'; /* Do not Suppress Records with Zero Earnings */
2353
2354 if g_debug then
2355 hr_utility.set_location('payroll_parameters.action '||p_payroll_action_id,900);
2356 hr_utility.set_location('in BG_ID '||l_parameters.business_group_id,901);
2357 hr_utility.set_location('in payroll_parameters.id '||l_parameters.payroll_id,903);
2358 hr_utility.set_location('in asg_id '||l_parameters.assignment_id,904);
2359 hr_utility.set_location('in legal employer '||l_parameters.legal_employer,908);
2360 hr_utility.set_location('in YTD totals '||l_ytd_totals,910);
2361 hr_utility.set_location('in zero records'||l_zero_records,911);
2362 hr_utility.set_location('in Negative records'||l_negative_records,912);
2363 hr_utility.set_location('in emp_type '||l_parameters.employee_type,914);
2364 hr_utility.set_location('In Start Date '||l_parameters.fin_year_start_date,916);
2365 hr_utility.set_location('In End Date '||l_parameters.fin_year_end_date,917);
2366 hr_utility.set_location('In Last Year Term'||l_parameters.lst_year_term,918);
2367 hr_utility.set_location('In Delete Actions'||l_parameters.delete_actions,919); /*Bug 4142159*/
2368 hr_utility.set_location('In Output Type '||l_parameters.p_output_type,920);
2369 end if;
2370
2371 if g_debug then
2372 hr_utility.set_location('Afer payroll action ' || p_payroll_action_id , 900);
2373 hr_utility.set_location('Before calling report',900);
2374 end if;
2375
2376 OPEN csr_get_print_options(p_payroll_action_id);
2377 FETCH csr_get_print_options INTO rec_print_options;
2378 CLOSE csr_get_print_options;
2379 --
2380 l_print_together := nvl(fnd_profile.value('CONC_PRINT_TOGETHER'), 'N');
2381 --
2382 -- Set printer options
2383 l_print_return := fnd_request.set_print_options
2384 (printer => rec_print_options.printer,
2385 style => rec_print_options.print_style,
2386 copies => rec_print_options.number_of_copies, /*Bug 4116833 */
2387 save_output => hr_general.char_to_bool(rec_print_options.save_output),
2388 print_together => l_print_together);
2389 -- Submit report
2390 if g_debug then
2391 hr_utility.set_location('payroll_action id '|| p_payroll_action_id,900);
2392 end if;
2393
2394 ps_request_id := fnd_request.submit_request
2395 ('PAY',
2396 l_short_report_name, /* Bug 6839263 */
2397 null,
2398 null,
2399 false,
2400 'P_PAYROLL_ACTION_ID='||to_char(p_payroll_action_id),
2401 'P_BUSINESS_GROUP_ID='||to_char(l_parameters.business_group_id),
2402 'P_ORGANIZATION_ID='||l_dummy,
2403 'P_PAYROLL_ID='||l_parameters.payroll_id, /* Bug 4353285 removed the to_char */
2404 'P_REGISTERED_EMPLOYER='||to_char(l_parameters.legal_employer),
2405 'P_ASSIGNMENT_ID='||l_parameters.assignment_id, /* Bug 4353285 removed the to_char */
2406 'P_START_DATE='||to_char(l_parameters.fin_year_start_date,'YYYY/MM/DD'),
2407 'P_END_DATE='||to_char(l_parameters.fin_year_end_date,'YYYY/MM/DD'),
2408 'P_PAYROLL_RUN_ID='||l_dummy,
2409 'P_PERIOD_END_DATE='||l_dummy,
2410 'P_EMPLOYEE_TYPE='||l_parameters.employee_type,
2411 'P_YTD_TOTALS='||l_ytd_totals,
2412 'P_ZERO_RECORDS='||l_zero_records,
2413 'P_NEGATIVE_RECORDS='||l_negative_records,
2414 'P_SORT_ORDER_1='||l_dummy,
2415 'P_SORT_ORDER_2='||l_dummy,
2416 'P_SORT_ORDER_3='||l_dummy,
2417 'P_SORT_ORDER_4='||l_dummy,
2418 'P_PAYSUM_FLAG='||l_paysum_flag,
2419 'P_LST_YEAR_TERM='||l_parameters.lst_year_term,
2420 'P_DELETE_ACTIONS='||l_parameters.delete_actions, /*Bug 4142159*/
2421 'BLANKPAGES=NO',
2422 NULL, NULL, NULL, NULL, NULL, NULL,
2423 NULL, NULL, NULL, NULL, NULL, NULL,
2424 NULL, NULL, NULL, NULL, NULL, NULL,
2425 NULL, NULL, NULL, NULL, NULL, NULL,
2426 NULL, NULL, NULL, NULL, NULL, NULL,
2427 NULL, NULL, NULL, NULL, NULL, NULL,
2428 NULL, NULL, NULL, NULL, NULL, NULL,
2429 NULL, NULL, NULL, NULL, NULL, NULL,
2430 NULL, NULL, NULL, NULL, NULL, NULL,
2431 NULL, NULL, NULL, NULL, NULL, NULL,
2432 NULL, NULL, NULL, NULL, NULL, NULL,
2433 NULL, NULL, NULL, NULL, NULL, NULL,
2434 NULL, NULL, NULL, NULL
2435 );
2436 if g_debug then
2437 hr_utility.set_location('After calling report',900);
2438 end if;
2439
2440 end;
2441
2442
2443 /* Bug 3891577 - Support for Summary Report - Payment Summary Mode */
2444 --------------------------------------------------------------------+
2445 -- Name : summary_rep_populate_allowance
2446 -- Type : Procedure
2447 -- Access: Public
2448 -- This procedure is called for the Summary report
2449 -- This procedure identifies the allowance balances processed for
2450 -- assignment in the financial year and populates PL/SQL table.
2451 --------------------------------------------------------------------+
2452 procedure summary_rep_populate_allowance(p_assignment_id in per_all_assignments_f.assignment_id%type
2453 ,p_allowance_exists out nocopy number)
2454 IS
2455
2456 /* Bug 7138494 - Changed rom Date Earned to Effective date for better
2457 performance */
2458 cursor get_allowance_balance_types
2459 (c_assignment_id pay_assignment_actions.assignment_id%type
2460 ,c_start_date date
2461 ,c_end_date date
2462 ,c_tax_unit_id pay_assignment_actions.tax_unit_id%type)
2463 IS
2464 SELECT DISTINCT pet.element_information2 balance_type_id
2465 FROM pay_element_types_f pet
2466 ,per_all_assignments_f paf
2467 ,pay_payroll_actions ppa
2468 ,pay_assignment_actions paa
2469 ,pay_run_results prr
2470 WHERE ppa.effective_date BETWEEN c_start_date AND c_end_date
2471 AND ppa.action_type in ('R','Q','B','V','I')
2472 AND ppa.payroll_id = paf.payroll_id
2473 AND paa.assignment_id = c_assignment_id
2474 AND paa.assignment_id = paf.assignment_id
2475 AND paa.assignment_action_id = prr.assignment_Action_id
2476 AND prr.element_type_id = pet.element_type_id
2477 AND prr.status in ('P','PA')
2478 AND paa.tax_unit_id = c_tax_unit_id
2479 AND paa.action_status ='C'
2480 AND pet.element_information_category = 'AU_EARNINGS'
2481 AND pet.element_information1 = 'Y'
2482 AND paa.payroll_action_id = ppa.payroll_Action_id
2483 AND ppa.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
2484 AND ppa.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date;
2485
2486 l_counter number;
2487 l_procedure varchar2(80);
2488
2489 begin
2490 l_counter := 0;
2491 g_debug := hr_utility.debug_enabled ;
2492 g_balance_type_tab.delete;
2493
2494 if g_debug then
2495 l_procedure := g_package||' summary_rep_populate_allowance';
2496 hr_utility.set_location('Entering '||l_procedure,999);
2497 end if;
2498
2499 FOR csr_bal in get_allowance_balance_types(p_assignment_id
2500 ,g_start_date
2501 ,g_end_date
2502 ,g_tax_unit_id)
2503 LOOP
2504 l_counter := l_counter + 1 ;
2505 g_balance_type_tab(l_counter) := to_number(csr_bal.balance_type_id);
2506 END LOOP;
2507
2508 p_allowance_exists := l_counter;
2509
2510 if g_debug then
2511 hr_utility.set_location('Leaving '||l_procedure,999);
2512 end if;
2513
2514 end summary_rep_populate_allowance;
2515
2516 procedure spawn_summary_reports
2517 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type)
2518 is
2519 l_count number;
2520 ps_request_id NUMBER;
2521 l_print_style VARCHAR2(2);
2522 l_print_together VARCHAR2(80);
2523 l_print_return BOOLEAN;
2524 l_procedure varchar2(50);
2525
2526 --------------------------------------------------------------------+
2527 -- Cursor : csr_params
2528 -- Description : Fetches User Parameters from Legislative_paramters
2529 -- column.
2530 --------------------------------------------------------------------+
2531
2532 CURSOR csr_report_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
2533 IS
2534 select pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters) business_group_id
2535 ,pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters) legal_employer
2536 ,pay_core_utils.get_parameter('PAYROLL',legislative_parameters) payroll_id
2537 ,pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters) assignment_id
2538 ,pay_core_utils.get_parameter('EMPLOYEE_TYPE',legislative_parameters) employee_type
2539 ,to_date('01-07-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fin_year_start_date
2540 ,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),6,4),'DD-MM-YYYY') fin_year_end_date
2541 ,to_date('01-04-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_start_date
2542 ,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_end_date
2543 ,pay_core_utils.get_parameter('LST_YR_TERM',legislative_parameters) lst_year_term
2544 ,pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions
2545 ,decode(pay_core_utils.get_parameter('REP_MODE',legislative_parameters),'SUMM','S','D') report_mode
2546 from pay_payroll_actions
2547 where payroll_action_id = c_payroll_action_id;
2548
2549
2550 cursor csr_get_print_options(p_payroll_action_id NUMBER) IS
2551 SELECT printer,
2552 print_style,
2553 decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output
2554 ,number_of_copies
2555 FROM pay_payroll_actions pact,
2556 fnd_concurrent_requests fcr
2557 WHERE fcr.request_id = pact.request_id
2558 AND pact.payroll_action_id = p_payroll_action_id;
2559
2560 /* Declaration - Report Flags to be SET */
2561 l_paysum_flag varchar2(2);
2562 l_ytd_totals varchar2(2);
2563 l_negative_records varchar2(2);
2564 l_zero_records varchar2(2);
2565
2566 l_dummy varchar2(240);
2567
2568 rec_print_options csr_get_print_options%ROWTYPE;
2569
2570 l_parameters csr_report_params%ROWTYPE;
2571
2572 begin
2573 l_count :=0;
2574 ps_request_id :=-1;
2575 g_debug :=hr_utility.debug_enabled ;
2576
2577 if g_debug then
2578 l_procedure := g_package||' spawn_summary_archive_reports';
2579 hr_utility.set_location('Entering '||l_procedure,999);
2580 end if;
2581
2582 -- Set User Parameters for Report.
2583
2584 open csr_report_params(p_payroll_action_id);
2585 fetch csr_report_params into l_parameters;
2586 close csr_report_params;
2587
2588 --Set REPORT FLAGS values.
2589 l_paysum_flag := 'Y'; /*Indicate Payment Summary Report*/
2590 l_ytd_totals := 'Y'; /* YTD Balances to be displayed */
2591 l_negative_records := 'N'; /* Do not Suppress Records with Negative Earnings */
2592 l_zero_records:= 'N'; /* Do not Suppress Records with Zero Earnings */
2593
2594 if g_debug then
2595 hr_utility.set_location('payroll_parameters.action '||p_payroll_action_id,900);
2596 hr_utility.set_location('in BG_ID '||l_parameters.business_group_id,901);
2597 hr_utility.set_location('in payroll_parameters.id '||l_parameters.payroll_id,903);
2598 hr_utility.set_location('in asg_id '||l_parameters.assignment_id,904);
2599 hr_utility.set_location('in legal employer '||l_parameters.legal_employer,908);
2600 hr_utility.set_location('in YTD totals '||l_ytd_totals,910);
2601 hr_utility.set_location('in zero records'||l_zero_records,911);
2602 hr_utility.set_location('in Negative records'||l_negative_records,912);
2603 hr_utility.set_location('in emp_type '||l_parameters.employee_type,914);
2604 hr_utility.set_location('In Start Date '||l_parameters.fin_year_start_date,916);
2605 hr_utility.set_location('In End Date '||l_parameters.fin_year_end_date,917);
2606 hr_utility.set_location('In Last Year Term'||l_parameters.lst_year_term,918);
2607 hr_utility.set_location('In Delete Actions'||l_parameters.delete_actions,919);
2608 hr_utility.set_location('In Report Mode'||l_parameters.report_mode,920);
2609 end if;
2610
2611 if g_debug then
2612 hr_utility.set_location('Afer payroll action ' || p_payroll_action_id , 900);
2613 hr_utility.set_location('Before calling report',900);
2614 end if;
2615
2616 OPEN csr_get_print_options(p_payroll_action_id);
2617 FETCH csr_get_print_options INTO rec_print_options;
2618 CLOSE csr_get_print_options;
2619 --
2620 l_print_together := nvl(fnd_profile.value('CONC_PRINT_TOGETHER'), 'N');
2621 --
2622 -- Set printer options
2623 l_print_return := fnd_request.set_print_options
2624 (printer => rec_print_options.printer,
2625 style => rec_print_options.print_style,
2626 copies => rec_print_options.number_of_copies, /*Bug 4116833 */
2627 save_output => hr_general.char_to_bool(rec_print_options.save_output),
2628 print_together => l_print_together);
2629 -- Submit report
2630 if g_debug then
2631 hr_utility.set_location('payroll_action id '|| p_payroll_action_id,900);
2632 end if;
2633
2634 ps_request_id := fnd_request.submit_request
2635 ('PAY',
2636 'PYAUPSSAR',
2637 null,
2638 null,
2639 false,
2640 'P_PAYROLL_ACTION_ID='||to_char(p_payroll_action_id),
2641 'P_BUSINESS_GROUP_ID='||l_parameters.business_group_id,
2642 'P_ORGANIZATION_ID='||l_dummy,
2643 'P_PAYROLL_ID='||l_parameters.payroll_id,
2644 'P_REGISTERED_EMPLOYER='||l_parameters.legal_employer,
2645 'P_ASSIGNMENT_ID='||l_parameters.assignment_id,
2646 'P_START_DATE='||to_char(l_parameters.fin_year_start_date,'YYYY/MM/DD'),
2647 'P_END_DATE='||to_char(l_parameters.fin_year_end_date,'YYYY/MM/DD'),
2648 'P_PAYROLL_RUN_ID='||l_dummy,
2649 'P_PERIOD_END_DATE='||l_dummy,
2650 'P_EMPLOYEE_TYPE='||l_parameters.employee_type,
2651 'P_YTD_TOTALS='||l_ytd_totals,
2652 'P_ZERO_RECORDS='||l_zero_records,
2653 'P_NEGATIVE_RECORDS='||l_negative_records,
2654 'P_SORT_ORDER_1='||l_dummy,
2655 'P_SORT_ORDER_2='||l_dummy,
2656 'P_SORT_ORDER_3='||l_dummy,
2657 'P_SORT_ORDER_4='||l_dummy,
2658 'P_PAYSUM_FLAG='||l_paysum_flag,
2659 'P_LST_YEAR_TERM='||l_parameters.lst_year_term,
2660 'P_DELETE_ACTIONS='||l_parameters.delete_actions, /*Bug 4142159*/
2661 'BLANKPAGES=NO',
2662 NULL, NULL, NULL, NULL, NULL, NULL,
2663 NULL, NULL, NULL, NULL, NULL, NULL,
2664 NULL, NULL, NULL, NULL, NULL, NULL,
2665 NULL, NULL, NULL, NULL, NULL, NULL,
2666 NULL, NULL, NULL, NULL, NULL, NULL,
2667 NULL, NULL, NULL, NULL, NULL, NULL,
2668 NULL, NULL, NULL, NULL, NULL, NULL,
2669 NULL, NULL, NULL, NULL, NULL, NULL,
2670 NULL, NULL, NULL, NULL, NULL, NULL,
2671 NULL, NULL, NULL, NULL, NULL, NULL,
2672 NULL, NULL, NULL, NULL, NULL, NULL,
2673 NULL, NULL, NULL, NULL, NULL, NULL,
2674 NULL, NULL, NULL, NULL
2675 );
2676 if g_debug then
2677 hr_utility.set_location('After calling report',900);
2678 end if;
2679
2680 end spawn_summary_reports;
2681
2682
2683 end pay_au_rec_det_paysum_mode;