[Home] [Help]
PACKAGE BODY: APPS.PAY_IE_P30LOCK
Source
1 PACKAGE BODY pay_ie_p30lock AS
2 /* $Header: pyiep30p.pkb 120.12 2011/09/15 09:35:53 rsahai ship $ */
3
4 g_package CONSTANT VARCHAR2(30) := 'Pay_ie_P30lock';
5
6
7 FUNCTION get_parameter(
8 p_parameter_string in varchar2
9 ,p_token in varchar2
10 ,p_segment_number in number ) RETURN varchar2
11 IS
12 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
13 l_start_pos NUMBER;
14 l_delimiter varchar2(1):=' ';
18 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
15 l_proc VARCHAR2(160):= g_package||'.get parameter ';
16 BEGIN
17 hr_utility.set_location('Entering ' || l_proc, 20);
19 IF l_start_pos = 0 THEN
20 l_delimiter := '|';
21 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
22 end if;
23 IF l_start_pos <> 0 THEN
24 l_start_pos := l_start_pos + length(p_token||'=');
25 l_parameter := substr(p_parameter_string,
26 l_start_pos,
27 instr(p_parameter_string||' ',
28 l_delimiter,l_start_pos)
29 - l_start_pos);
30 IF p_segment_number IS NOT NULL THEN
31 l_parameter := ':'||l_parameter||':';
32 l_parameter := substr(l_parameter,
33 instr(l_parameter,':',1,p_segment_number)+1,
34 instr(l_parameter,':',1,p_segment_number+1) -1
35 - instr(l_parameter,':',1,p_segment_number));
36 END IF;
37 END IF;
38 hr_utility.set_location('Leaving ' || l_proc, 100);
39 RETURN l_parameter;
40
41 END get_parameter;
42
43
44 PROCEDURE get_all_parameters ( p_payroll_action_id in number
45 ,p_token in varchar2
46 ,p_business_group_id out NOCOPY number
47 ,p_token_value out NOCOPY VARCHAR2 ) IS
48
49 CURSOR csr_parameter_info(p_payroll_action_id IN NUMBER) IS
50 SELECT pay_ie_p30lock.get_parameter(legislative_parameters, p_token)
51 ,business_group_id
52 FROM pay_payroll_actions
53 WHERE payroll_action_id = p_payroll_action_id;
54 l_proc VARCHAR2(160):= g_package||'.get_all_parameters ';
55
56 BEGIN
57
58 hr_utility.set_location('Entering ' || l_proc, 20);
59
60 OPEN csr_parameter_info (p_payroll_action_id);
61 FETCH csr_parameter_info INTO p_token_value,
62 p_business_group_id;
63 CLOSE csr_parameter_info;
64
65 hr_utility.set_location('Leaving ' || l_proc, 100);
66
67 END get_all_parameters;
68
69
70 -- First Lock Process to lock the PRG data
71
72 PROCEDURE range_code (pactid IN NUMBER,
73 sqlstr OUT NOCOPY VARCHAR2)
74 -- public procedure which archives the payroll information, then returns a
75 -- varchar2 defining a SQL statement to select all the people that may be
76 -- eligible for payslip reports.
77 -- The archiver uses this cursor to split the people into chunks for parallel
78 -- processing.
79 IS
80 --
81 l_proc CONSTANT VARCHAR2(150):= g_package||'.range_code';
82 l_business_group_id NUMBER;
83 l_canonical_end_date DATE;
84 l_end_date VARCHAR2(20);
85
86 BEGIN
87
88 hr_utility.set_location('Entering ' || l_proc,10);
89
90 pay_ie_p30lock.get_all_parameters
91 (p_payroll_action_id => pactid,
92 p_token => 'END_DATE',
93 p_business_group_id => l_business_group_id,
94 p_token_value => l_end_date);
95
96
97 hr_utility.set_location('Step ' || l_proc,20);
98 hr_utility.set_location('l_end_date = ' || l_end_date,20);
99
100 l_canonical_end_date := TO_DATE(l_end_date,'yyyy/mm/dd');
101 hr_utility.set_location('l_canonical_end_date = ' || l_canonical_end_date,20);
102
103 -- Used per_people_f 4555600
104 -- Changed the cursor to reduce the cost (5042843)
105 sqlstr := 'SELECT distinct asg.person_id
106 FROM per_periods_of_service pos,
107 per_assignments_f asg,
108 pay_payroll_actions ppa
109 WHERE ppa.payroll_action_id = :payroll_action_id
110 AND pos.person_id = asg.person_id
111 AND pos.period_of_service_id = asg.period_of_service_id
112 AND pos.business_group_id = ppa.business_group_id
113 AND asg.business_group_id = ppa.business_group_id
114 ORDER BY asg.person_id';
115
116 hr_utility.set_location('Leaving ' || l_proc,40);
117
118 EXCEPTION
119 WHEN OTHERS THEN
120 hr_utility.set_location(' Leaving with EXCEPTION: '||l_proc,100);
121 -- Return cursor that selects no rows
122 sqlstr := sqlerrm;
123 END range_code;
124
125 PROCEDURE prg_assignment_action_code(pactid in number,
126 stperson in number,
127 endperson in number,
128 chunk in number) is
129
130 l_actid NUMBER;
131 l_canonical_end_date DATE;
132 l_end_date VARCHAR2(20);
133 l_business_group_id NUMBER;
134 l_consolidation_set pay_all_payrolls_f.consolidation_set_id%type;
135 l_proc VARCHAR2(150) := g_package||'.prg_assignment_action_code';
136 l_arch_action_id number := 0;
137
138 --Bug 4557715
139 CURSOR csr_assact is
140 select /*+ ORDERED USE_NL(pp1 asg)
141 INDEX(ppa PAY_PAYROLL_ACTIONS_PK)*/ paa.assignment_action_id,
142 paa.assignment_id,
143 paa.payroll_action_id,
144 ppa.date_earned,
145 paa_run.tax_unit_id
146 from per_assignments_f asg,
147 pay_payroll_actions pp1,
148 pay_assignment_actions paa,
149 pay_payroll_actions ppa,
150 pay_action_interlocks pai_pre,
151 pay_assignment_actions paa_run,
152 pay_payroll_actions ppa_run,
153 pay_all_payrolls_f pap
154 ,pay_action_information pai --12943478
155 where pp1.payroll_action_id = pactid
156 and asg.business_group_id = pp1.business_group_id
157 and asg.person_id between stperson and endperson
158 and asg.assignment_id = paa.assignment_id
159 and paa.source_action_id is null
160 and paa.payroll_action_id = ppa.payroll_action_id
161 and ppa_run.effective_date between asg.effective_start_date and asg.effective_end_date
162 and ppa_run.effective_date between to_date(substr(l_end_date,1,4)||'/01/01','YYYY/MM/DD')
163 and to_date(l_end_date,'YYYY/MM/DD')
164 and paa.action_status = 'C'
165 and ppa.action_type = 'X'
166 and ppa.report_type = 'IEPS'
167 and ppa.report_category = 'ARCHIVE'
168 and pai_pre.locking_action_id = paa.assignment_action_id
169 and pai_pre.locked_action_id = paa_run.assignment_action_id
170 and paa_run.action_status IN ('C','S') --10225372
171 and paa_run.payroll_action_id = ppa_run.payroll_action_id
172 and paa_run.source_action_id IS NULL
173 and ppa_run.action_type in ('Q','R')
174 --12943478
175 and pai.action_context_id=paa.assignment_action_id
176 and pai.action_information_category = 'IE EMPLOYEE DETAILS'
177 and pai.action_context_type='AAP'
178 --12943478
179 and not exists (select /*+ INDEX(payact PAY_PAYROLL_ACTIONS_PK) */ null
180 from pay_action_interlocks alock,
181 pay_assignment_actions assact,
182 pay_payroll_actions payact
183 where alock.locked_action_id = paa.assignment_action_id
184 and assact.assignment_action_id = alock.locking_action_id
185 and assact.payroll_action_id = payact.payroll_action_id
186 and payact.action_type = 'X'
187 and payact.report_type = 'IEP30_PRGLOCK'
188 and payact.report_category = 'ARCHIVE'
189 -- 4317512
190 /* Added to check whether the archiver is locked by a P30 datalock whose effective date is greater than effective date of payroll
191 run locked by archiver */
192 and to_date(substr(payact.legislative_parameters,instr(payact.legislative_parameters,'END_DATE=')+9,10),'YYYY/MM/DD') >= ppa_run.effective_date)
193 --Added for bug fix 3567562, to restrict assignments to the consoliation set selected.
194 and asg.payroll_id = pap.payroll_id
195 and ppa_run.effective_date between pap.effective_start_date and pap.effective_end_date
196 and (pap.consolidation_set_id = l_consolidation_set or l_consolidation_set is null)
197 ORDER BY paa.assignment_id,
198 paa.assignment_action_id;
199
200
201 BEGIN
202 hr_utility.set_location('Entering ' || l_proc,10);
203 pay_ie_p30lock.get_all_parameters
204 (p_payroll_action_id => pactid,
205 p_token => 'END_DATE',
206 p_business_group_id => l_business_group_id,
207 p_token_value => l_end_date);
208
209 --Added for bug fix 3567562,to get the consolidation set parameter
210 pay_ie_p30lock.get_all_parameters (
211 p_payroll_action_id => pactid
212 , p_token => 'CONSOLIDATION'
213 , p_business_group_id => l_business_group_id
214 , p_token_value => l_consolidation_set);
215
216 hr_utility.set_location('Step ' || l_proc,20);
217 hr_utility.set_location('l_end_date = ' || l_end_date,20);
218 l_canonical_end_date := TO_DATE(l_end_date,'yyyy/mm/dd');
219 FOR csr_rec IN csr_assact
220 LOOP
221 IF l_arch_action_id <> csr_rec.assignment_action_id THEN
222 hr_utility.set_location('inside loop ' || l_proc,12);
223 hr_utility.set_location('-----------------------------------------',13);
224 hr_utility.set_location('Assignment_action_id ' || csr_rec.assignment_action_id,14);
225 hr_utility.set_location('pactid_id ' || pactid,15);
226 hr_utility.set_location('Payroll_action_id ' || csr_rec.payroll_action_id,15);
227 hr_utility.set_location('-----------------------------------------',17);
228 SELECT pay_assignment_actions_s.NEXTVAL
229 INTO l_actid
230 FROM dual;
231 hr_nonrun_asact.insact(l_actid,csr_rec.assignment_id,pactid,chunk,csr_rec.tax_unit_id);
232 hr_utility.set_location('created pay_ass_act ' || l_actid || ' to ' || pactid,20);
233 hr_nonrun_asact.insint(l_actid, csr_rec.assignment_action_id);
234 hr_utility.set_location('created interlocks ' || l_actid || ' to ' || csr_rec.assignment_action_id,20);
235 l_arch_action_id := csr_rec.assignment_action_id;
236 END IF;
237 END LOOP;
238
239 hr_utility.set_location('Leaving ' || l_proc,20);
240
241 END prg_assignment_action_code;
242
243
244 -- Second Lock Process for p30 Report Lock process
245
246 PROCEDURE rep_assignment_action_code(pactid in number,
247 stperson in number,
248 endperson in number,
249 chunk in number) is
250
251 l_actid NUMBER;
252 l_canonical_end_date DATE;
253 l_pay_action_id VARCHAR2(20);
254 l_business_group_id NUMBER;
255 l_proc VARCHAR2(150) := g_package||'.rep_assignment_action_code';
256
257
258 CURSOR csr_assact is
259 select paa.assignment_action_id,
260 paa.assignment_id,
261 paa.payroll_action_id,
262 ppa.date_earned,
263 paa.tax_unit_id
264 from pay_payroll_actions ppa,
265 pay_assignment_actions paa
266 -- per_assignments_f asg -- Bug Fix 4260031
267 where ppa.payroll_action_id = l_pay_action_id
268 -- and asg.business_group_id = ppa.business_group_id -- Bug Fix 4260031
269 -- and asg.person_id between stperson and endperson
270 -- and asg.assignment_id = paa.assignment_id
271 and paa.source_action_id is null
272 and paa.payroll_action_id = ppa.payroll_action_id
273 -- and ppa.date_earned between asg.effective_start_date and asg.effective_end_date
274 and paa.assignment_id in (select asg.assignment_id -- Bug Fix 4260031
275 from per_assignments_f asg
276 where asg.business_group_id = ppa.business_group_id
277 and asg.person_id between stperson and endperson)
278 and ppa.action_status = 'C'
279 and ppa.action_type = 'X'
280 and ppa.report_type = 'IEP30_PRGLOCK'
281 and ppa.report_category = 'ARCHIVE'
282 -- bug fix 5371061, added ordered join to remove merge join cartesian.
283 and not exists (select /*+ ORDERED */ null
284 from pay_payroll_actions payact,
285 pay_assignment_actions assact,
286 pay_action_interlocks alock
287 where alock.locked_action_id = paa.assignment_action_id
288 and assact.assignment_action_id = alock.locking_action_id
289 and assact.payroll_action_id = payact.payroll_action_id
290 and payact.action_type = 'X'
291 and payact.report_type = 'IEP30_REPLOCK'
292 and payact.report_category = 'ARCHIVE')
293 ORDER BY paa.assignment_id;
294
295
296 BEGIN
297
298 hr_utility.set_location('Entering ' || l_proc,10);
299
300 pay_ie_p30lock.get_all_parameters
301 (p_payroll_action_id => pactid,
302 p_token => 'PAYROLL_ACTION_ID',
303 p_business_group_id => l_business_group_id,
304 p_token_value => l_pay_action_id);
305
306
307 hr_utility.set_location('Step ' || l_proc,20);
308 hr_utility.set_location('l_pay_action_id = ' || l_pay_action_id,20);
309
310 FOR csr_rec IN csr_assact
311 LOOP
312 hr_utility.set_location('inside loop ' || l_proc,12);
313 hr_utility.set_location('-----------------------------------------',13);
314 hr_utility.set_location('Assignment_action_id ' || csr_rec.assignment_action_id,14);
315 hr_utility.set_location('Pact_id ' || pactid,15);
316 hr_utility.set_location('Payroll_action_id ' || csr_rec.payroll_action_id,15);
317 hr_utility.set_location('-----------------------------------------',17);
318 SELECT pay_assignment_actions_s.NEXTVAL
319 INTO l_actid
320 FROM dual;
321 hr_nonrun_asact.insact(l_actid,csr_rec.assignment_id,pactid,chunk,csr_rec.tax_unit_id);
322 hr_utility.set_location('created pay_ass_act ' || l_actid || ' to ' || pactid,20);
323 hr_nonrun_asact.insint(l_actid, csr_rec.assignment_action_id);
324 hr_utility.set_location('created interlocks ' || l_actid || ' to ' || csr_rec.assignment_action_id,20);
325
326 END LOOP;
327
328 hr_utility.set_location('Leaving ' || l_proc,20);
329
330 END rep_assignment_action_code;
331
332 ---------------------------------------------------------------------
336 errbuf out NOCOPY varchar2
333 -- Procedure generate_xml - Generates P30 XML Output File
334 ---------------------------------------------------------------------
335 PROCEDURE generate_xml(
337 ,retcode out NOCOPY varchar2
338 ,p_p30_data_lock_process in number
339 ,p_supplementary_run in varchar2
340 ,p_period_type in varchar2) IS
341
342
343 CURSOR cur_employer_info(
344 c_p30_data_lock_process pay_payroll_actions.payroll_action_id%TYPE) IS
345 SELECT ppa_p30.payroll_action_id report_id,
346 paa_p30.assignment_id assignment_id,
347 pact_er.action_information5 employer_paye_number
348 FROM pay_payroll_actions ppa_p30,
349 pay_assignment_actions paa_p30,
350 pay_action_interlocks pai_p30,
351 pay_assignment_actions paa_arc,
352 pay_action_interlocks pai_arc,
353 pay_assignment_actions paa_prepay,
354 pay_action_interlocks pai_prepay,
355 pay_action_information pact_er,
356 pay_action_information pact_ee
357 -- pay_pre_payments ppp -- Bug Fix 3725003
358 WHERE ppa_p30.payroll_action_id = c_p30_data_lock_process
359 AND ppa_p30.payroll_action_id = paa_p30.payroll_action_id
360 AND paa_p30.assignment_action_id = pai_p30.locking_action_id
361 AND paa_arc.assignment_action_id = pai_p30.locked_action_id
362 AND paa_arc.assignment_action_id = pai_arc.locking_action_id
363 AND paa_prepay.assignment_action_id = pai_arc.locked_action_id
364 AND paa_prepay.assignment_action_id = pai_prepay.locking_action_id
365 AND pact_er.action_context_type = 'PA'
366 AND pact_er.action_information_category = 'EMEA PAYROLL INFO'
367 AND pact_er.action_context_id = paa_arc.payroll_action_id
368 AND pact_er.action_information1 = paa_prepay.payroll_action_id
369 AND pact_ee.action_information_category = 'EMPLOYEE DETAILS'
370 AND pact_ee.action_context_id = paa_arc.assignment_action_id
371 --Added for Bug fix : 3725003
372 -- AND ppp.assignment_action_id = pai_arc.locked_action_id
373 GROUP BY ppa_p30.payroll_action_id
374 ,paa_p30.assignment_id
375 ,pact_er.action_information5
376 ORDER BY ppa_p30.payroll_action_id;
377 --
378 CURSOR cur_get_archive_pactid(
379 c_p30_data_lock_process pay_payroll_actions.payroll_action_id%TYPE) IS
380 SELECT max(ppa_arc.payroll_action_id) archive_pactid
381 FROM pay_assignment_actions paa_p30,
382 pay_action_interlocks pai_p30,
383 pay_assignment_actions paa_arc,
384 pay_payroll_actions ppa_arc
385 WHERE paa_p30.payroll_Action_id = c_p30_data_lock_process
386 AND paa_p30.assignment_action_id = pai_p30.locking_action_id
387 AND paa_arc.assignment_action_id = pai_p30.locked_action_id
388 AND ppa_arc.payroll_action_id = paa_arc.payroll_action_id;
389 --
390 CURSOR cur_p30_start_date(
391 c_p30_data_lock_process pay_payroll_actions.payroll_action_id%TYPE) IS
392 SELECT to_char(MIN(ppa_arc.start_date),'DD/MM/RRRR') start_date
393 FROM pay_assignment_actions paa_p30,
394 pay_action_interlocks pai_p30,
395 pay_assignment_actions paa_arc,
396 pay_payroll_actions ppa_arc
397 WHERE paa_p30.payroll_Action_id = c_p30_data_lock_process
398 AND paa_p30.assignment_action_id = pai_p30.locking_action_id
399 AND paa_arc.assignment_action_id = pai_p30.locked_action_id
400 AND ppa_arc.payroll_action_id = paa_arc.payroll_action_id;
401 --
402 CURSOR cur_employer_address(
403 c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE) IS
404 SELECT substr(pai.action_information5,1,30) employer_tax_addr1
405 ,substr(pai.action_information6,1,30) employer_tax_addr2
406 ,substr(pai.action_information7,1,30) employer_tax_addr3
407 ,substr(pai.action_information26,1,30) employer_tax_contact
408 ,substr(pai.action_information27,1,12) employer_tax_ref_phone
409 ,substr(pai.action_information28,1,30) employer_tax_rep_name
410 FROM pay_action_information pai
411 WHERE pai.action_context_id = c_payroll_action_id
412 AND pai.action_context_type = 'PA'
413 AND pai.action_information_category = 'ADDRESS DETAILS'
414 AND pai.action_information14 = 'IE Employer Tax Address';
415 --
416 -- 4317512
417 /* YTD_Balances fill fetch that balance value whose source ids effective date is less than effective date
418 of P30 datalock */
419 CURSOR YTD_Balances (vp_Payroll_Action_Id Pay_Payroll_Actions.Payroll_Action_ID%TYPE,
420 vp_Assignment_Id Pay_Assignment_Actions.Assignment_Action_ID%TYPE,
421 vp_Balance_Name Pay_Balance_Types.Balance_Name%TYPE,
422 vp_Dimension_Name varchar2,
423 vp_action_context_id number,
424 vp_effective_date date)IS -- SR 17318286.6 rbhardwa
425 SELECT pact_ytdbal.action_information4 Balance_Value
426 FROM pay_assignment_actions paa_p30,
427 pay_action_interlocks pai_p30,
428 pay_assignment_actions paa_arc,
429 pay_action_information pact_ytdbal,
430 pay_defined_balances pdb_ytdbal,
431 pay_balance_types pbt_ytdbal,
432 pay_balance_dimensions pbd_ytdbal,
433 pay_assignment_actions paa_src,
434 pay_payroll_actions ppa_src
435 WHERE paa_p30.payroll_action_id = vp_Payroll_Action_Id
436 AND paa_p30.assignment_id = vp_Assignment_Id
437 AND paa_p30.assignment_action_id = pai_p30.locking_action_id
438 AND paa_arc.assignment_action_id = pai_p30.locked_action_id
442 AND pact_ytdbal.ACTION_CONTEXT_TYPE = 'AAP'
439 AND pact_ytdbal.action_information_category= 'EMEA BALANCES'
440 AND pact_ytdbal.ACTION_CONTEXT_ID = vp_action_context_id --SR 17318286.6 rbhardwa
441 AND pact_ytdbal.ACTION_CONTEXT_ID = paa_arc.assignment_action_id
443 AND pdb_ytdbal.defined_balance_id = pact_ytdbal.action_information1
444 AND pdb_ytdbal.balance_type_id = pbt_ytdbal.balance_type_id
445 AND pbt_ytdbal.balance_name = vp_Balance_Name
446 AND pbd_ytdbal.dimension_name = vp_Dimension_Name
447 AND pbd_ytdbal.balance_dimension_id = pdb_ytdbal.balance_dimension_id
448 AND pbt_ytdbal.legislation_code = 'IE'
449 AND pact_ytdbal.action_context_type = 'AAP'
450 AND paa_src.assignment_action_id = pact_ytdbal.source_id
451 AND paa_src.payroll_action_id = ppa_src.payroll_action_id
452 AND ppa_src.effective_date <= vp_effective_date
453 ORDER BY pact_ytdbal.effective_date DESC -- Fix Tar 4033038.994
454 ,pact_ytdbal.ACTION_CONTEXT_ID DESC
455 ,nvl(pact_ytdbal.action_information5,0) DESC;
456 --
457 CURSOR cur_get_prev_p30_lock_id (vp_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE
458 ,vp_assignment_id pay_assignment_actions.assignment_action_id%TYPE
459 ,vp_tax_start_date date
460 ,vp_report_end_date date) IS
461 SELECT ppa.payroll_action_id prev_data_lock_id,
462 to_date(substr(ppa.legislative_parameters,instr(ppa.legislative_parameters,'END_DATE=')+9,10),'YYYY/MM/DD')
463 FROM pay_payroll_actions ppa
464 ,pay_assignment_actions paa
465 WHERE ppa.payroll_action_id = paa.payroll_action_id
466 AND ppa.report_type = 'IEP30_PRGLOCK'
467 AND paa.assignment_id = vp_assignment_id
468 AND paa.assignment_action_id = (
469 SELECT to_number(substr(max(lpad(paa2.action_sequence,15,'0')||
470 paa2.assignment_action_id),16))--Bug No 3318509
471 FROM pay_payroll_actions ppa2
472 ,pay_assignment_actions paa2
473 WHERE ppa2.payroll_action_id = paa2.payroll_action_id
474 AND ppa2.report_type = 'IEP30_PRGLOCK'
475 AND paa2.assignment_id = vp_assignment_id
476 AND ppa2.payroll_action_id <> vp_payroll_action_id
477 AND to_date(substr(ppa2.legislative_parameters ,instr(ppa2.legislative_parameters,'END_DATE=')+9,10),'YYYY/MM/DD')
478 BETWEEN vp_tax_start_date AND vp_report_end_date)
479 ORDER BY ppa.payroll_action_id DESC;
480
481 -- Report End Date
482 CURSOR cur_end_date(vp_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE) IS
483 SELECT to_date(substr(ppa_p30.legislative_parameters,instr(ppa_p30.legislative_parameters,'END_DATE=')+9,10),'YYYY/MM/DD') end_date
484 FROM pay_payroll_actions ppa_p30
485 WHERE ppa_p30.payroll_action_id=vp_payroll_action_id;
486
487 -- Start date of Tax Year
488 CURSOR cur_get_start_date (vp_report_end_date date) IS
489 SELECT to_date(rule_mode || '/' || to_char(vp_report_end_date,'YYYY'),'dd/mm/yyyy')
490 FROM pay_legislation_rules
491 WHERE legislation_code = 'IE'
492 AND rule_type = 'L';
493
494 /* SR 17318286.6 rbhardwa changes start here */
495 --Bug Fix 4032212 This cursor is added to get the max action_context_id against the P30 Data Lock id
496 -- Changed to fetch correct action context if Archiver is run for Period 2 first and then for period1
497 CURSOR get_action_context (vp_Payroll_Action_Id Pay_Payroll_Actions.Payroll_Action_ID%TYPE,
498 vp_Assignment_Id Pay_Assignment_Actions.Assignment_Action_ID%TYPE) IS
499 SELECT fnd_number.canonical_to_number(substr(max(lpad(paa_run.action_sequence,15,'0')||pact_ytdbal.ACTION_CONTEXT_ID),16))
500 FROM pay_assignment_actions paa_p30,
501 pay_action_interlocks pai_p30,
502 pay_assignment_actions paa_arc,
503 pay_action_information pact_ytdbal,
504 pay_action_interlocks pai_arc,
505 pay_assignment_actions paa_run,
506 pay_payroll_actions ppa_run
507 -- ,pay_pre_payments ppp --Bug Fix 4049831 Added join with pay_pre_payments table
508 WHERE paa_p30.payroll_action_id = vp_Payroll_Action_Id
509 AND paa_p30.assignment_id = vp_Assignment_Id
510 AND paa_p30.assignment_action_id = pai_p30.locking_action_id
511 AND paa_arc.assignment_action_id = pai_p30.locked_action_id
512 AND paa_arc.assignment_action_id = pai_arc.locking_action_id
513 AND pact_ytdbal.ACTION_CONTEXT_ID = paa_arc.assignment_action_id
514 AND paa_run.assignment_action_id = pai_arc.locked_action_id
515 AND pact_ytdbal.action_context_type = 'AAP'
516 AND paa_run.source_action_id IS NULL
517 AND paa_run.payroll_action_id = ppa_run.payroll_action_id
518 AND ppa_run.action_type in ('R','Q');
519
520 /* SR 17318286.6 rbhardwa changes end here */
521
522 v_prev_data_lock_id number;
523 v_pre_date_eff_date date;
524 v_cur_employer_info cur_employer_info%ROWTYPE;
525 v_Curr_YTD_Balances YTD_Balances%ROWTYPE;
526 v_Prev_YTD_Balances YTD_Balances%ROWTYPE;
527
528 v_action_context_id Number(15,0); --SR 17318286.6 rbhardwa
529
530 --
531 l_report_end_date date;
532 l_tax_start_date date;
533 --
534 l_PAYE_PTD number:=0;
535 l_EE_PRSI_PTD number:=0;
536 l_ER_PRSI_PTD number:=0;
537 --
538 l_PAYE_YTD number:=0;
539 l_EE_PRSI_YTD number:=0;
540 l_ER_PRSI_YTD number:=0;
541 --
542 l_root_start_tag varchar2(200);
543 l_root_end_tag varchar2(50);
544 --
545 l_employer_start_tag varchar2(20);
546 l_employer_end_tag varchar2(20);
547 --
548 l_p30_start_tag varchar2(20);
549 l_p30_end_tag varchar2(20);
550 --
551 l_archive_pactid pay_payroll_actions.payroll_action_id%TYPE;
552 --
553 l_employer_paye_number varchar2(80);
554 l_employer_number varchar2(10);
555 l_employer_name varchar2(30);
556 l_employer_add1 varchar2(30);
557 l_employer_add2 varchar2(30);
558 l_employer_add3 varchar2(30);
559 l_employer_contact varchar2(20);
560 l_employer_phone varchar2(12);
561 --
562 l_p30_start varchar2(10);
563 l_p30_paye number:=0;
564 l_p30_prsi number:=0;
565 l_p30_type varchar2(1);
566 l_period_type varchar2(1); -- For bug 5119350
567
568 BEGIN
569
570 l_root_start_tag :='<P30File currency="E" formversion="1" language="E">';
571 l_root_end_tag :='</P30File>';
572
573 l_employer_start_tag :=' <Employer ';
574 l_employer_end_tag :=' />';
575
576 l_p30_start_tag :=' <P30 ';
577 l_p30_end_tag :=' />';
578
579 -- Get payroll_action_id of the legislative archive process
580 OPEN cur_get_archive_pactid(p_p30_data_lock_process);
581 FETCH cur_get_archive_pactid INTO l_archive_pactid;
582 CLOSE cur_get_archive_pactid;
583
584 -- Get Employer Address
585 OPEN cur_employer_address(l_archive_pactid);
586 FETCH cur_employer_address INTO l_employer_add1
587 ,l_employer_add2
588 ,l_employer_add3
589 ,l_employer_contact
590 ,l_employer_phone
591 ,l_employer_name;
592 CLOSE cur_employer_address;
593
594
595
596 -- Start of xml doc
597 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<?xml version="1.0" encoding="UTF-8"?>');
598
599 -- P30File root ELEMENT
600 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_root_start_tag);
601
602 -- Bug 2659864 - 'This Report' Figure calculation logic changed to
603 -- (Curr P30 YTD value - Prev P30 YTD Value)
604
605 -- Report End Date
606 OPEN cur_end_date(p_p30_data_lock_process);
607 FETCH cur_end_date INTO l_report_end_date;
608 CLOSE cur_end_date;
609
610 -- Get Tax Year Start Date
611 OPEN cur_get_start_date(l_report_end_date);
612 FETCH cur_get_start_date INTO l_tax_start_date;
613 CLOSE cur_get_start_date;
614
615 FOR v_cur_employer_info IN cur_employer_info(p_p30_data_lock_process) LOOP
616 l_employer_paye_number := v_cur_employer_info.employer_paye_number;
617 --
618 -- SR 17318286.6 rbhardwa changes start here
619 OPEN get_action_context(v_cur_employer_info.report_id,v_cur_employer_info.assignment_id);
620 FETCH get_action_context INTO v_action_context_id;
621 CLOSE get_action_context;
622 -- SR 17318286.6 rbhardwa changes end here
623
624 --Fetch Curr PAYE YTD Balance from PAY ACTION INFORMATION
625 OPEN YTD_Balances(v_cur_employer_info.report_id
626 ,v_cur_employer_info.assignment_id
627 ,'IE Net Tax'
628 ,'_ASG_YTD'
629 ,v_action_context_id
630 ,l_report_end_date);
631 FETCH YTD_Balances INTO v_Curr_YTD_Balances;
632 CLOSE YTD_Balances;
633 l_PAYE_YTD := NVL(v_Curr_YTD_Balances.Balance_Value,0);
634 v_Curr_YTD_Balances.Balance_Value := NULL;
635 --Fetch Curr EE PRSI YTD Balance from PAY ACTION INFORMATION
636 OPEN YTD_Balances(v_cur_employer_info.report_id
637 ,v_cur_employer_info.assignment_id
638 ,'IE PRSI Employee'
639 ,'_ASG_YTD'
640 ,v_action_context_id
641 ,l_report_end_date);
642 FETCH YTD_Balances INTO v_Curr_YTD_Balances;
643 CLOSE YTD_Balances;
644 l_EE_PRSI_YTD := NVL(v_Curr_YTD_Balances.Balance_Value,0);
645 v_Curr_YTD_Balances.Balance_Value := NULL;
646 -- Bug 3436737 : Added code to sum up K and M figures for a severance
647 -- payment
648 OPEN YTD_Balances(v_cur_employer_info.report_id
649 ,v_cur_employer_info.assignment_id
650 ,'IE PRSI K Employee Lump Sum'
651 ,'_ASG_YTD'
652 ,v_action_context_id
653 ,l_report_end_date);
654 FETCH YTD_Balances INTO v_Curr_YTD_Balances;
655 CLOSE YTD_Balances;
656 l_EE_PRSI_YTD := l_EE_PRSI_YTD + NVL(v_Curr_YTD_Balances.Balance_Value,0);
657 v_Curr_YTD_Balances.Balance_Value := NULL;
658 OPEN YTD_Balances(v_cur_employer_info.report_id
659 ,v_cur_employer_info.assignment_id
660 ,'IE PRSI M Employee Lump Sum'
661 ,'_ASG_YTD'
662 ,v_action_context_id
663 ,l_report_end_date);
664 FETCH YTD_Balances INTO v_Curr_YTD_Balances;
665 CLOSE YTD_Balances;
666 l_EE_PRSI_YTD := l_EE_PRSI_YTD + NVL(v_Curr_YTD_Balances.Balance_Value,0);
667 v_Curr_YTD_Balances.Balance_Value := NULL;
668 -- Total PRSI Employee figure has been evaluated above
669 --Fetch Curr ER PRSI YTD Balance from PAY ACTION INFORMATION
670 OPEN YTD_Balances(v_cur_employer_info.report_id
671 ,v_cur_employer_info.assignment_id
672 ,'IE PRSI Employer'
673 ,'_ASG_YTD'
674 ,v_action_context_id
675 ,l_report_end_date);
676 FETCH YTD_Balances INTO v_Curr_YTD_Balances;
677 CLOSE YTD_Balances;
678 l_ER_PRSI_YTD := NVL(v_Curr_YTD_Balances.Balance_Value,0);
679 v_Curr_YTD_Balances.Balance_Value := NULL;
680 OPEN YTD_Balances(v_cur_employer_info.report_id
681 ,v_cur_employer_info.assignment_id
682 ,'IE PRSI K Employer Lump Sum'
683 ,'_ASG_YTD'
684 ,v_action_context_id
685 ,l_report_end_date);
686 FETCH YTD_Balances INTO v_Curr_YTD_Balances;
687 CLOSE YTD_Balances;
688 l_ER_PRSI_YTD := l_ER_PRSI_YTD + NVL(v_Curr_YTD_Balances.Balance_Value,0);
689 v_Curr_YTD_Balances.Balance_Value := NULL;
690 OPEN YTD_Balances(v_cur_employer_info.report_id
691 ,v_cur_employer_info.assignment_id
692 ,'IE PRSI M Employer Lump Sum'
693 ,'_ASG_YTD'
694 ,v_action_context_id
695 ,l_report_end_date);
696 FETCH YTD_Balances INTO v_Curr_YTD_Balances;
697 CLOSE YTD_Balances;
698 l_ER_PRSI_YTD := l_ER_PRSI_YTD + NVL(v_Curr_YTD_Balances.Balance_Value,0);
699 v_Curr_YTD_Balances.Balance_Value := NULL;
700
701
702 /* 7691477 */
703 IF l_tax_start_date < to_date('01-01-2011','dd-mm-rrrr') --10418202
704 THEN
705 OPEN YTD_Balances(v_cur_employer_info.report_id
706 ,v_cur_employer_info.assignment_id
707 ,'IE Income Tax Levy'
708 ,'_ASG_YTD'
709 ,v_action_context_id
710 ,l_report_end_date);
711 FETCH YTD_Balances INTO v_Curr_YTD_Balances;
712 CLOSE YTD_Balances;
713
714
715 l_PAYE_YTD :=l_PAYE_YTD + NVL(v_Curr_YTD_Balances.Balance_Value,0);
716 v_Curr_YTD_Balances.Balance_Value := NULL;
717 END IF; --10418202
718
719 --10418202
720 IF l_tax_start_date >= to_date('01-01-2011','dd-mm-rrrr')
721 THEN
722 OPEN YTD_Balances(v_cur_employer_info.report_id
723 ,v_cur_employer_info.assignment_id
727 ,l_report_end_date);
724 ,'IE USC Balance'
725 ,'_ASG_YTD'
726 ,v_action_context_id
728 FETCH YTD_Balances INTO v_Curr_YTD_Balances;
729 CLOSE YTD_Balances;
730
731 l_PAYE_YTD :=l_PAYE_YTD + NVL(v_Curr_YTD_Balances.Balance_Value,0);
732
733 v_Curr_YTD_Balances.Balance_Value := NULL;
734
735 END IF;
736 --10418202
737
738 OPEN YTD_Balances(v_cur_employer_info.report_id
739 ,v_cur_employer_info.assignment_id
740 ,'IE Parking Levy'
741 ,'_ASG_YTD'
742 ,v_action_context_id
743 ,l_report_end_date);
744 FETCH YTD_Balances INTO v_Curr_YTD_Balances;
745 CLOSE YTD_Balances;
746
747 IF l_tax_start_date < to_date('01-01-2011','dd-mm-rrrr') --10418202
748 THEN
749 l_EE_PRSI_YTD := l_EE_PRSI_YTD + NVL(v_Curr_YTD_Balances.Balance_Value,0);
750 END IF;
751 --10418202
752 IF l_tax_start_date >= to_date('01-01-2011','dd-mm-rrrr')
753 THEN
754 l_PAYE_YTD :=l_PAYE_YTD + NVL(v_Curr_YTD_Balances.Balance_Value,0);
755 END IF;
756 --10418202
757 v_Curr_YTD_Balances.Balance_Value := NULL;
758
759
760 -- Total PRSI Employer figure has been evaluated above
761 -- Fetch PAYE/PRSI 'This Report' values
762 -- The 'This Report' value are calculated as (Current P30s YTD values - Previous P30s YTD values)
763 OPEN cur_get_prev_p30_lock_id(v_cur_employer_info.report_id
764 ,v_cur_employer_info.assignment_id
765 ,l_tax_start_date
766 ,l_report_end_date);
767 FETCH cur_get_prev_p30_lock_id INTO v_prev_data_lock_id,v_pre_date_eff_date;
768 IF cur_get_prev_p30_lock_id%NOTFOUND THEN
769 v_prev_data_lock_id :=NULL;
770 v_pre_date_eff_date := NULL;
771 l_PAYE_PTD := l_PAYE_YTD;
772 l_EE_PRSI_PTD := l_EE_PRSI_YTD;
773 l_ER_PRSI_PTD := l_ER_PRSI_YTD;
774 ELSE
775
776 -- SR 17318286.6 rbhardwa changes start here
777 OPEN get_action_context(v_prev_data_lock_id,v_cur_employer_info.assignment_id);
778 FETCH get_action_context INTO v_action_context_id;
779 CLOSE get_action_context;
780 -- SR 17318286.6 rbhardwa changes end here
781
782
783 --Fetch Prev PAYE YTD Balance from PAY ACTION INFORMATION of Prev P30 Run
784 OPEN YTD_Balances(v_prev_data_lock_id,v_cur_employer_info.assignment_id,'IE Net Tax','_ASG_YTD',v_action_context_id,v_pre_date_eff_date);
785 FETCH YTD_Balances INTO v_Prev_YTD_Balances;
786 CLOSE YTD_Balances;
787 l_PAYE_PTD := l_PAYE_YTD - NVL(v_Prev_YTD_Balances.Balance_Value,0);
788 v_Prev_YTD_Balances.Balance_Value := NULL;
789
790 --Fetch Prev EE PRSI YTD Balance from PAY ACTION INFORMATION of Prev P30 Run
791 OPEN YTD_Balances(v_prev_data_lock_id,v_cur_employer_info.assignment_id,'IE PRSI Employee','_ASG_YTD',v_action_context_id,v_pre_date_eff_date);
792 FETCH YTD_Balances INTO v_Prev_YTD_Balances;
793 CLOSE YTD_Balances;
794 l_EE_PRSI_PTD := l_EE_PRSI_YTD - NVL(v_Prev_YTD_Balances.Balance_Value,0);
795 v_Prev_YTD_Balances.Balance_Value := NULL;
796
797 -- Bug 3436737 : Added code to sum up K and M Employee figures for a severance
798 -- payment
799 OPEN YTD_Balances(v_prev_data_lock_id,v_cur_employer_info.assignment_id,'IE PRSI K Employee Lump Sum','_ASG_YTD',v_action_context_id,v_pre_date_eff_date);
800 FETCH YTD_Balances INTO v_Prev_YTD_Balances;
801 CLOSE YTD_Balances;
802 l_EE_PRSI_PTD := l_EE_PRSI_PTD - NVL(v_Prev_YTD_Balances.Balance_Value,0);
803 v_Prev_YTD_Balances.Balance_Value := NULL;
804
805 OPEN YTD_Balances(v_prev_data_lock_id,v_cur_employer_info.assignment_id,'IE PRSI M Employee Lump Sum','_ASG_YTD',v_action_context_id,v_pre_date_eff_date);
806 FETCH YTD_Balances INTO v_Prev_YTD_Balances;
807 CLOSE YTD_Balances;
808 l_EE_PRSI_PTD := l_EE_PRSI_PTD - NVL(v_Prev_YTD_Balances.Balance_Value,0);
809 v_Prev_YTD_Balances.Balance_Value := NULL;
810
811 --Fetch Prev ER PRSI YTD Balance from PAY ACTION INFORMATION of Prev P30
812 OPEN YTD_Balances(v_prev_data_lock_id,v_cur_employer_info.assignment_id,'IE PRSI Employer','_ASG_YTD',v_action_context_id,v_pre_date_eff_date);
813 FETCH YTD_Balances INTO v_Prev_YTD_Balances;
814 CLOSE YTD_Balances;
815 l_ER_PRSI_PTD := l_ER_PRSI_YTD - NVL(v_Prev_YTD_Balances.Balance_Value,0);
816 v_Prev_YTD_Balances.Balance_Value := NULL;
817
818 -- Added code to sum up K and M Employer figures for a severance payment
819 OPEN YTD_Balances(v_prev_data_lock_id,v_cur_employer_info.assignment_id,'IE PRSI K Employer Lump Sum','_ASG_YTD',v_action_context_id,v_pre_date_eff_date);
820 FETCH YTD_Balances INTO v_Prev_YTD_Balances;
821 CLOSE YTD_Balances;
822 l_ER_PRSI_PTD := l_ER_PRSI_PTD - NVL(v_Prev_YTD_Balances.Balance_Value,0);
823 v_Prev_YTD_Balances.Balance_Value := NULL;
824
825 OPEN YTD_Balances(v_prev_data_lock_id,v_cur_employer_info.assignment_id,'IE PRSI M Employer Lump Sum','_ASG_YTD',v_action_context_id,v_pre_date_eff_date);
826 FETCH YTD_Balances INTO v_Prev_YTD_Balances;
827 CLOSE YTD_Balances;
828 l_ER_PRSI_PTD := l_ER_PRSI_PTD - NVL(v_Prev_YTD_Balances.Balance_Value,0);
829 v_Prev_YTD_Balances.Balance_Value := NULL;
830
831 /* 7691477 */
832 IF l_tax_start_date < to_date('01-01-2011','dd-mm-rrrr') --10418202
833 THEN
834 OPEN YTD_Balances(v_prev_data_lock_id,v_cur_employer_info.assignment_id,'IE Income Tax Levy','_ASG_YTD',v_action_context_id,v_pre_date_eff_date);
835 FETCH YTD_Balances INTO v_Prev_YTD_Balances;
836 CLOSE YTD_Balances;
837 l_PAYE_PTD := l_PAYE_PTD - NVL(v_Prev_YTD_Balances.Balance_Value,0);
838 v_Prev_YTD_Balances.Balance_Value := NULL;
839 END IF; --10418202
840
841 --10418202
842 IF l_tax_start_date >= to_date('01-01-2011','dd-mm-rrrr')
843 THEN
844 OPEN YTD_Balances(v_prev_data_lock_id,v_cur_employer_info.assignment_id,'IE USC Balance','_ASG_YTD',v_action_context_id,v_pre_date_eff_date);
845 FETCH YTD_Balances INTO v_Prev_YTD_Balances;
846 CLOSE YTD_Balances;
847 l_PAYE_PTD := l_PAYE_PTD - NVL(v_Prev_YTD_Balances.Balance_Value,0);
848 v_Prev_YTD_Balances.Balance_Value := NULL;
849 END IF;
850 --10418202
851
852 OPEN YTD_Balances(v_prev_data_lock_id,v_cur_employer_info.assignment_id,'IE Parking Levy','_ASG_YTD',v_action_context_id,v_pre_date_eff_date);
853 FETCH YTD_Balances INTO v_Prev_YTD_Balances;
854 CLOSE YTD_Balances;
855 IF l_tax_start_date < to_date('01-01-2011','dd-mm-rrrr') --10418202
856 THEN
857 l_EE_PRSI_PTD := l_EE_PRSI_PTD - NVL(v_Prev_YTD_Balances.Balance_Value,0);
858 END IF; --10418202
859 --10418202
860 IF l_tax_start_date >= to_date('01-01-2011','dd-mm-rrrr')
861 THEN
862 l_PAYE_PTD := l_PAYE_PTD - NVL(v_Prev_YTD_Balances.Balance_Value,0);
863 END IF;
864 --10418202
865 v_Prev_YTD_Balances.Balance_Value := NULL;
866
867
868 END IF;
869 CLOSE cur_get_prev_p30_lock_id;
870 l_p30_paye := l_p30_paye + NVL(l_PAYE_PTD,0);
871 l_p30_prsi := l_p30_prsi + NVL(l_EE_PRSI_PTD,0) + NVL(l_ER_PRSI_PTD,0);
872
873 END LOOP;
874
875 -- Employer ELEMENT
876 FND_FILE.PUT(FND_FILE.OUTPUT,l_employer_start_tag);
877
878 FND_FILE.PUT(FND_FILE.OUTPUT,'number="' || l_employer_paye_number ||'" ');
879 FND_FILE.PUT(FND_FILE.OUTPUT,'name="' || l_employer_name ||'" ');
880
881 IF l_employer_add1 IS NOT NULL THEN
882 FND_FILE.PUT(FND_FILE.OUTPUT,'address1="' || l_employer_add1 ||'" ');
883 END IF;
884
885 IF l_employer_add2 IS NOT NULL THEN
886 FND_FILE.PUT(FND_FILE.OUTPUT,'address2="' || l_employer_add2 ||'" ');
887 END IF;
888
889 IF l_employer_add3 IS NOT NULL THEN
890 FND_FILE.PUT(FND_FILE.OUTPUT,'address3="' || l_employer_add3 ||'" ');
891 END IF;
892
893 FND_FILE.PUT(FND_FILE.OUTPUT,'contact="' || l_employer_contact ||'" ');
894
895 IF l_employer_phone IS NOT NULL THEN
896 FND_FILE.PUT(FND_FILE.OUTPUT,'phone="' || replace(replace(l_employer_phone,'('),')') ||'" ');
897 END IF;
898
899
900
901 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_employer_end_tag);
902
903 -- P30 ELEMENT
904 FND_FILE.PUT(FND_FILE.OUTPUT,l_p30_start_tag);
905
906 -- Get Start Date
907 OPEN cur_p30_start_date(p_p30_data_lock_process);
908 FETCH cur_p30_start_date INTO l_p30_start;
909 CLOSE cur_p30_start_date;
910
911 IF p_supplementary_run = 'Y' THEN
912 l_p30_type := '1';
913 ELSE
914 l_p30_type := '0';
915 END IF;
916 -- For bug 5119350, checks if period type is monthly set it to 0 else 1.
917 IF p_period_type='M' then
918 l_period_type := '0';
919 ELSE
920 l_period_type := '1';
921 END IF;
922
923 FND_FILE.PUT(FND_FILE.OUTPUT,'period="' || l_period_type ||'" ');
924 -- End bug 5119350
925 FND_FILE.PUT(FND_FILE.OUTPUT,'start="' || l_p30_start ||'" ');
926
927 /* Bug 2502060 P30 XML FAILED REVENUE ON LINE VALIDATION
928 Change in requirement- Totals in PAYE and PRSI element to display
929 in whole Euros and with no ',' seperating the thousands.
930
931 FND_FILE.PUT(FND_FILE.OUTPUT,'PAYE="' || to_char(l_p30_paye,'FM999G999G999') ||'" ');
932 FND_FILE.PUT(FND_FILE.OUTPUT,'PRSI="' || to_char(l_p30_prsi,'FM999G999G999') ||'" ');
933
934 */
935
936 FND_FILE.PUT(FND_FILE.OUTPUT,'PAYE="' || to_char(l_p30_paye,'FM999999999') ||'" ');
937 FND_FILE.PUT(FND_FILE.OUTPUT,'PRSI="' || to_char(l_p30_prsi,'FM999999999') ||'" ');
938 FND_FILE.PUT(FND_FILE.OUTPUT,'type="' || l_p30_type ||'" ');
939
940 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_p30_end_tag);
941
942 -- End of ROOT P30File ELEMENT
943 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_root_end_tag);
944
945 END generate_xml;
946
947 END;