[Home] [Help]
PACKAGE BODY: APPS.PAY_NZ_EDF
Source
1 PACKAGE BODY pay_nz_edf AS
2 /** $Header: pynzedf.pkb 120.0.12020000.3 2013/03/01 09:13:42 mingyhua ship $
3 *** +======================================================================+
4 *** | Copyright (c) 2000 Oracle Corporation |
5 *** | Redwood Shores, California, USA |
6 *** | All rights reserved. |
7 *** +======================================================================+
8 ***
9 *** Package Body Name : PAY_NZ_EDF
10 *** Package File Name : pynzedf.pkb
11 *** Description : This package is created for supporting the new
12 *** 'EDF/IR345 (New Zealand)' process in New Zealand
13 *** payroll. The cursors and procedures in the package
14 *** will be called by the XML generation process and adds
15 *** required XML tags in output XML.
16 ***
17 *** Name Date Version Bug Text
18 *** ------------ ----------- ------- ------- ------------------------------
19 *** PMATAMSR 07-APR-2010 115.0 9299387 Created
20 *** PMATAMSR 12-APR-2010 115.1 9299387 Removed code from assignment_action_code.
21 *** PMATAMSR 12-APR-2010 115.2 9299387 Corrected GSCC errors.
22 *** PMATAMSR 24-APR-2010 115.3 9612150 Added cursor get_session_date.
23 *** PMATAMSR 24-APR-2010 115.4 9612150 Corrected GSCC Warnings.
24 *** PMATAMSR 29-APR-2010 115.5 9666103 Modified cursor csr_edf_record in generate_detail_xml procedure.
25 *** DDUVVURI 28-FEB-2012 115.7 13627558 Added SLCIR Deductions and SLBOR Deduction Balances to Student Loan Deduction balances
26 for 2012 NZ statutory updates
27 *** MINGYHUA 01-MAR-2013 115.8 16239379 Added start date to confine the start and
28 *** end date of report; Excluded ESCT deductions
29 *** from KiwiSaver ER Contribution;
30 *** Simplified date/char type converions.
31 **/
32
33 g_debug BOOLEAN;
34 g_proc_name VARCHAR2(50) := 'pay_nz_edf.';
35
36 /*
37 --------------------------------------------------------------------
38 Name : range_code
39 Type : Procedure
40 Access: Public
41 Description: This procedure returns a sql string to
42 select a range of assignments eligible for archival.
43 --------------------------------------------------------------------
44 */
45
46 PROCEDURE range_code(p_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE,
47 p_sql OUT NOCOPY VARCHAR2)
48 IS
49
50 l_proc_name VARCHAR2(100) := g_proc_name ||'range_code';
51
52 BEGIN
53
54 g_debug := hr_utility.debug_enabled;
55
56 IF g_debug THEN
57 hr_utility.set_location('Entering : '||l_proc_name, 10);
58 END if;
59
60 p_sql := ' select distinct p.person_id' ||
61 ' from per_people_f p,' ||
62 ' pay_payroll_actions pa' ||
63 ' where pa.payroll_action_id = :payroll_action_id' ||
64 ' and p.business_group_id = pa.business_group_id' ||
65 ' order by p.person_id';
66
67 hr_utility.set_location('Leaving : '||l_proc_name, 20);
68
69 END range_code;
70
71 /*
72 --------------------------------------------------------------------
73 Name : assignment_action_code
74 Type : Procedure
75 Access: Public
76 Description:This procedure further restricts the assignment_id's
77 returned by range_code.
78 The procedure uses the Business Group ID and Tax Unit ID
79 parameters and restricts assignments to be archived.
80 It then calls hr_nonrun.insact to create an Assignment Action Id.
81 --------------------------------------------------------------------
82 */
83
84 /* 9299387 - The assignment actions created by archival process are
85 not used by any other process.Hence,to avoid redudancy ,the
86 assignment action creation code is removed from the procedure. */
87
88 PROCEDURE assignment_action_code(p_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE,
89 p_start_person_id IN per_all_people_f.person_id%TYPE,
90 p_end_person_id IN per_all_people_f.person_id%TYPE,
91 p_chunk IN NUMBER)
92 IS
93
94 l_proc_name VARCHAR2(100) := g_proc_name ||'assignment_action_code';
95
96 BEGIN
97 g_debug := hr_utility.debug_enabled;
98
99 IF g_debug THEN
100 hr_utility.set_location('Entering : '||l_proc_name, 10);
101 END IF;
102
103 IF g_debug THEN
104 hr_utility.set_location('Leaving : '||l_proc_name, 20);
105 END IF;
106
107 END assignment_action_code;
108
109 /*
110 --------------------------------------------------------------------
111 Name : load_xml
112 Type : Procedure
113 Access: Private
114 Description:This procedure generates the required XML tags
115 and is called by header,detail and footer procedures.
116 --------------------------------------------------------------------
117 */
118
119 PROCEDURE load_xml (p_node_type IN VARCHAR2,
120 p_node IN VARCHAR2,
121 p_data IN VARCHAR2)
122 IS
123 l_proc_name varchar2(100) := g_proc_name ||'load_xml';
124 l_data varchar2(500);
125 BEGIN
126
127 g_debug := hr_utility.debug_enabled;
128
129 IF g_debug THEN
130 hr_utility.set_location('Entering : '||l_proc_name, 10);
131 END IF;
132
133 IF p_node_type = 'CS' THEN
134 pay_core_files.write_to_magtape_lob('<'||p_node||'>');
135 ELSIF p_node_type = 'CE' THEN
136 pay_core_files.write_to_magtape_lob('</'||p_node||'>');
137 ELSIF p_node_type = 'D' THEN
138 l_data := REPLACE (p_data, '&', '&');
139 l_data := REPLACE (l_data, '>', '>');
140 l_data := REPLACE (l_data, '<', '<');
141 l_data := REPLACE (l_data, '''', ''');
142 l_data := REPLACE (l_data, '"', '"');
143 pay_core_files.write_to_magtape_lob('<'||p_node||'>'||l_data||'</'||p_node||'>');
144 END IF;
145
146 IF g_debug THEN
147 hr_utility.set_location('Leaving : '||l_proc_name, 20);
148 END IF;
149
150 END load_xml;
151
152 /*
153 --------------------------------------------------------------------
154 Name : generate_header_xml
155 Type : Procedure
156 Access: Private
157 Description:This procedure is used to generate header XML tags
158 for New Zeland new EDF process.
159 --------------------------------------------------------------------
160 */
161
162 PROCEDURE generate_header_xml
163 IS
164 l_proc_name varchar2(100) := g_proc_name ||'generate_header_xml';
165 BEGIN
166 g_debug := hr_utility.debug_enabled;
167
168 IF g_debug THEN
169 hr_utility.set_location('Entering : '||l_proc_name, 10);
170 END IF;
171
172 load_xml('CS','PYNZEDF','');
173
174 IF g_debug THEN
175 hr_utility.set_location('Leaving : '||l_proc_name, 20);
176 END IF;
177
178 END generate_header_xml;
179
180 /*
181 --------------------------------------------------------------------
182 Name : generate_footer_xml
183 Type : Procedure
184 Access: Private
185 Description:This procedure is used to generate footer XML tags
186 for New Zeland new EDF process.
187 --------------------------------------------------------------------
188 */
189
190 PROCEDURE generate_footer_xml
191 IS
192 l_proc_name varchar2(100) := g_proc_name ||'generate_footer_xml';
193 BEGIN
194 g_debug := hr_utility.debug_enabled;
195
196 IF g_debug THEN
197 hr_utility.set_location('Entering : '||l_proc_name, 10);
198 END IF;
199
200 load_xml('CE','PYNZEDF','');
201
202 IF g_debug THEN
203 hr_utility.set_location('Leaving : '||l_proc_name, 20);
204 END IF;
205
206 END generate_footer_xml;
207
208 /*
209 --------------------------------------------------------------------
210 Name : generate_detail_xml
211 Type : Procedure
212 Access: Private
213 Description:This procedure is used to generate detail XML tags
214 for New Zeland new EDF process.
215 (A) The cursor in the procedure retrieves summary
216 of payments.
217 (B) The procedure then calls LOAD_XML procedure for
218 generating XML tags.
219 --------------------------------------------------------------------
220 */
221
222 PROCEDURE generate_detail_xml
223 IS
224
225 l_proc_name VARCHAR2 (100) := g_proc_name ||'generate_detail_xml';
226 l_addr1 per_addresses.address_line1%TYPE;
227 l_addr2 per_addresses.address_line2%TYPE;
228 l_addr3 per_addresses.address_line3%TYPE;
229 l_country per_addresses.country%TYPE;
230 l_city per_addresses.town_or_city%TYPE;
231 l_postal_code per_addresses.postal_code%TYPE;
232 l_er_name hr_organization_units.name%TYPE;
233 l_er_ird_number hr_organization_information.org_information1%TYPE;
234 l_paye_ded pay_run_result_values.result_value%TYPE;
235 l_child_support_ded pay_run_result_values.result_value%TYPE;
236 l_student_loan_ded pay_run_result_values.result_value%TYPE;
237 l_esct_ded pay_run_result_values.result_value%TYPE;
238 l_ks_employee_ded pay_run_result_values.result_value%TYPE;
239 l_ks_employer_ded pay_run_result_values.result_value%TYPE;
240 l_period_start_date DATE;
241 l_period_end_date DATE;
242 l_prd_end_date DATE;
243 l_registered_employer varchar2(100);
244 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
245 l_tax_unit_id hr_organization_units.organization_id%TYPE;
246 l_effective_start_date VARCHAR2(20);
247 l_effective_end_date VARCHAR2(20);
248 l_leg_param pay_payroll_actions.legislative_parameters%TYPE ;
249 l_prd_end_date_ch VARCHAR2(20);
250 l_prd_end_date_fmt VARCHAR2(20);
251 l_payroll_action_id number;
252 l_bg_name per_business_groups.name%TYPE;
253 l_bus_group_id per_business_groups.business_group_id%TYPE;
254 l_session_date fnd_sessions.effective_date%TYPE; /* Bug 9612150 */
255
256 CURSOR get_parameters(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
257 IS
258 SELECT pay_core_utils.get_parameter('BUSINESS_GROUP_ID',ppa.legislative_parameters),
259 pay_core_utils.get_parameter('REGISTERED_EMPLOYER',ppa.legislative_parameters),
260 pay_core_utils.get_parameter('START_DATE',ppa.legislative_parameters),
261 pay_core_utils.get_parameter('END_DATE',ppa.legislative_parameters)
262 FROM pay_payroll_actions ppa
263 WHERE ppa.payroll_action_id = c_payroll_action_id;
264
265 CURSOR get_registered_employer(c_business_group_id hr_organization_units.business_group_id%TYPE,
266 c_registered_employer_id hr_organization_units.organization_id%TYPE)
267 IS
268 SELECT name
269 FROM hr_nz_tax_unit_v
270 WHERE business_group_id = c_business_group_id
271 AND tax_unit_id = c_registered_employer_id;
272
273 CURSOR get_business_group(c_business_group_id hr_organization_units.business_group_id%TYPE)
274 IS
275 SELECT name
276 FROM per_business_groups
277 WHERE business_group_id = c_business_group_id
278 AND legislation_code = 'NZ';
279
280 /* 9612150 - Added cursor to fetch the session date.If effective date parameter of
281 * EDF E-FIle process is null,then sesssion date is used. */
282 CURSOR get_session_date
283 IS
284 SELECT TRUNC(effective_date)
285 FROM fnd_sessions
286 WHERE session_id = userenv('SESSIONID');
287
288 /* 9666103 - Removed per_time_periods join and added pact join to fetch the
289 employees processed based on pact.effective_date */
290
291 CURSOR csr_edf_record(c_business_group_id IN hr_organization_units.business_group_id%TYPE,
292 c_registered_employer_id IN hr_organization_units.organization_id%TYPE,
293 c_period_start_date IN DATE,
294 c_period_end_date IN DATE)
295 IS
296 SELECT
297 o.name employer_name,
298 l.address_line_1,
299 l.address_line_2,
300 l.address_line_3,
301 l.town_or_city,
302 l.postal_code,
303 l.country country,
304 i.org_information1 er_ird_number,
305 (SUM(decode(pbt.balance_name,'PAYE Tax Deductions',target.result_value * feed.scale
306 ,'Withholding Tax Deductions Record',target.result_value * feed.scale
307 ,'Retro PAYE Tax Deductions',target.result_value * feed.scale,0))
308 + SUM(decode(pbt.balance_name,'Payroll Giving Tax Credits',target.result_value * feed.scale,0))) paye_deductions,
309 SUM(decode(pbt.balance_name,'Child Support Deductions',target.result_value * feed.scale,0)) child_support_deductions,
310 (SUM(decode(pbt.balance_name,'Student Loan Deductions',target.result_value * feed.scale
311 ,'Retro Student Loan Deductions',target.result_value * feed.scale,0))
312 + SUM(decode(pbt.balance_name,'SLCIR Deduction',target.result_value * feed.scale,0))
313 + SUM(decode(pbt.balance_name,'SLBOR Deduction',target.result_value * feed.scale,0))) student_loan_deductions,
314 SUM(decode(pbt.balance_name,'SSCWT Deductions' ,target.result_value * feed.scale,0)) sscwt_deductions,
315 SUM(decode(pbt.balance_name,'KiwiSaver Employee Contributions',target.result_value * feed.scale,0)) kiwisaver_employee_deductions,
316 SUM(decode(pbt.balance_name,'KiwiSaver Employer Contributions',target.result_value * feed.scale,0)) kiwisaver_employer_deductions
317 FROM
318 hr_organization_units o,
319 hr_organization_information i,
320 hr_locations l,
321 hr_soft_coding_keyflex scl,
322 per_assignments_f asg,
323 pay_balance_types pbt,
324 pay_run_result_values target,
325 pay_run_results rr,
326 pay_balance_feeds_f feed,
327 pay_assignment_actions assact,
328 pay_payroll_actions pact
329 WHERE o.business_group_id = c_business_group_id
330 AND o.organization_id = c_registered_employer_id
331 AND i.organization_id = o.organization_id
332 AND i.org_information_context = 'NZ_IRD_EMPLOYER'
333 AND l.location_id = o.location_id
334 AND feed.input_value_id = target.input_value_id
335 AND feed.balance_type_id = pbt.balance_type_id
336 AND nvl(target.result_value,'0') <> '0'
337 AND target.run_result_id = rr.run_result_id
338 AND rr.assignment_action_id = assact.assignment_action_id
339 AND assact.payroll_action_id = pact.payroll_action_id
340 AND pact.effective_date BETWEEN feed.effective_start_date AND feed.effective_end_date
341 AND rr.status IN ('P','PA')
342 AND pact.effective_date BETWEEN c_period_start_date AND c_period_end_date
343 AND asg.assignment_id = assact.assignment_id
344 AND asg.effective_start_date <= c_period_end_date
345 AND asg.effective_end_date >= c_period_start_date
346 AND asg.effective_start_date = (
347 SELECT max(asg_d.effective_start_date)
348 FROM per_assignments_f asg_d
349 WHERE asg_d.assignment_id = asg.assignment_id
350 AND asg_d.effective_start_date <= c_period_end_date )
351 AND scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
352 AND pbt.balance_name IN ( 'PAYE Tax Deductions' , 'Child Support Deductions' ,
353 'Student Loan Deductions' , 'SSCWT Deductions' ,
354 'Withholding Tax Deductions Record' , 'Retro PAYE Tax Deductions' ,
355 'Retro Student Loan Deductions' , 'KiwiSaver Employee Contributions' ,
356 'KiwiSaver Employer Contributions','Payroll Giving Tax Credits' ,'SLCIR Deduction','SLBOR Deduction')
357 AND asg.business_group_id = o.business_group_id
358 AND (scl.segment1) = to_char(o.organization_id)
359 GROUP BY
360 o.name,
361 l.address_line_1,
362 l.address_line_2,
363 l.address_line_3,
364 l.town_or_city,
365 l.postal_code,
366 l.country,
367 i.org_information1;
368
369 BEGIN
370
371 g_debug := hr_utility.debug_enabled;
372
373 IF g_debug THEN
374 hr_utility.set_location('Entering : '||l_proc_name, 10);
375 END IF;
376
377 l_payroll_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
378
379 OPEN get_parameters(l_payroll_action_id);
380 FETCH get_parameters INTO l_bus_group_id,
381 l_tax_unit_id,
382 l_effective_start_date,
383 l_effective_end_date;
384 CLOSE get_parameters;
385
386 OPEN get_registered_employer(l_bus_group_id,l_tax_unit_id);
387 FETCH get_registered_employer INTO l_registered_employer;
388 CLOSE get_registered_employer;
389
390 OPEN get_business_group(l_bus_group_id);
391 FETCH get_business_group INTO l_bg_name;
392 CLOSE get_business_group;
393
394 OPEN get_session_date;
395 FETCH get_session_date INTO l_session_date;
396 CLOSE get_session_date;
397
398
399 l_effective_start_date := nvl(l_effective_start_date,
400 to_char(pay_nz_tax.half_month_start(l_session_date), 'DD-MON-YYYY'));
401 l_effective_end_date := nvl(l_effective_end_date,
402 to_char(pay_nz_tax.half_month_end(l_session_date), 'DD-MON-YYYY'));
403
404 l_period_start_date := l_effective_start_date;
405 l_period_end_date := l_effective_end_date;
406
407 l_prd_end_date := l_period_end_date;
408
409 l_prd_end_date_ch := to_char(l_prd_end_date,'DD-MON-YYYY');
410 l_prd_end_date_fmt := to_char(l_prd_end_date,'YYYYMMDD');
411
412
413 OPEN csr_edf_record(l_bus_group_id,l_tax_unit_id,l_period_start_date,l_period_end_date);
414 FETCH csr_edf_record INTO
415 l_er_name,
416 l_addr1,
417 l_addr2,
418 l_addr3,
419 l_city,
420 l_postal_code,
421 l_country,
422 l_er_ird_number,
423 l_paye_ded,
424 l_child_support_ded,
425 l_student_loan_ded,
426 l_esct_ded,
427 l_ks_employee_ded,
428 l_ks_employer_ded;
429 CLOSE csr_edf_record;
430
431
432 load_xml('CS','G_ER_DATA','');
433 load_xml('D','BG_NAME',l_bg_name);
434 load_xml('D','REG_NAME',l_registered_employer);
435 load_xml('D','ER_NAME',l_er_name);
436 load_xml('D','END_DATE',l_prd_end_date_fmt);
437 load_xml('D','PRD_END_DATE',l_prd_end_date_ch);
438 load_xml('D','RPT_END_DATE',l_effective_end_date);
439 load_xml('D','ADDR1',l_addr1);
440 load_xml('D','ADDR2',l_addr2);
441 load_xml('D','ADDR3',l_addr3);
442 load_xml('D','CITY',l_city);
443 load_xml('D','POSTAL_CODE',l_postal_code);
444 load_xml('D','COUNTRY',l_country);
445 load_xml('D','ER_IRD_NO',l_er_ird_number);
446 load_xml('D','PAYE_DED',l_paye_ded);
447 load_xml('D','CHD_SUP_DED',l_child_support_ded);
448 load_xml('D','STU_LOAN_DED',l_student_loan_ded);
449 load_xml('D','ESCT_DED',l_esct_ded);
450 load_xml('D','KS_EE_DED',l_ks_employee_ded);
451 load_xml('D','KS_ER_DED',l_ks_employer_ded - l_esct_ded);
452 load_xml('CE','G_ER_DATA','');
453
454 IF g_debug THEN
455 hr_utility.set_location('Leaving : '||l_proc_name, 20);
456 END IF;
457
458 EXCEPTION
459 WHEN OTHERS THEN
460 IF g_debug THEN
461 hr_utility.set_location('Error in '||l_proc_name||' : '||SQLERRM,100);
462 hr_utility.set_location('Leaving : '||l_proc_name, 100);
463 END IF;
464 RAISE;
465
466 END generate_detail_xml;
467
468 END pay_nz_edf;