[Home] [Help]
PACKAGE BODY: APPS.PAY_IE_P30LOCK
Source
1 PACKAGE BODY pay_ie_p30lock AS
2 /* $Header: pyiep30p.pkb 120.7.12000000.2 2007/06/21 11:53:56 vikgupta 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):=' ';
15 l_proc VARCHAR2(160):= g_package||'.get parameter ';
16 BEGIN
17 hr_utility.set_location('Entering ' || l_proc, 20);
18 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
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 where pp1.payroll_action_id = pactid
155 and asg.business_group_id = pp1.business_group_id
156 and asg.person_id between stperson and endperson
157 and asg.assignment_id = paa.assignment_id
158 and paa.source_action_id is null
159 and paa.payroll_action_id = ppa.payroll_action_id
160 and ppa_run.effective_date between asg.effective_start_date and asg.effective_end_date
161 and ppa_run.effective_date between to_date(substr(l_end_date,1,4)||'/01/01','YYYY/MM/DD')
162 and to_date(l_end_date,'YYYY/MM/DD')
163 and paa.action_status = 'C'
164 and ppa.action_type = 'X'
165 and ppa.report_type = 'IEPS'
166 and ppa.report_category = 'ARCHIVE'
167 and pai_pre.locking_action_id = paa.assignment_action_id
168 and pai_pre.locked_action_id = paa_run.assignment_action_id
169 and paa_run.action_status = 'C'
170 and paa_run.payroll_action_id = ppa_run.payroll_action_id
171 and paa_run.source_action_id IS NULL
172 and ppa_run.action_type in ('Q','R')
173 and not exists (select /*+ INDEX(payact PAY_PAYROLL_ACTIONS_PK) */ null
174 from pay_action_interlocks alock,
175 pay_assignment_actions assact,
176 pay_payroll_actions payact
177 where alock.locked_action_id = paa.assignment_action_id
178 and assact.assignment_action_id = alock.locking_action_id
179 and assact.payroll_action_id = payact.payroll_action_id
180 and payact.action_type = 'X'
181 and payact.report_type = 'IEP30_PRGLOCK'
182 and payact.report_category = 'ARCHIVE'
183 -- 4317512
184 /* Added to check whether the archiver is locked by a P30 datalock whose effective date is greater than effective date of payroll
185 run locked by archiver */
186 and to_date(substr(payact.legislative_parameters,instr(payact.legislative_parameters,'END_DATE=')+9,10),'YYYY/MM/DD') >= ppa_run.effective_date)
187 --Added for bug fix 3567562, to restrict assignments to the consoliation set selected.
188 and asg.payroll_id = pap.payroll_id
189 and ppa_run.effective_date between pap.effective_start_date and pap.effective_end_date
190 and (pap.consolidation_set_id = l_consolidation_set or l_consolidation_set is null)
191 ORDER BY paa.assignment_id,
192 paa.assignment_action_id;
193
194
195 BEGIN
196 hr_utility.set_location('Entering ' || l_proc,10);
197 pay_ie_p30lock.get_all_parameters
198 (p_payroll_action_id => pactid,
199 p_token => 'END_DATE',
200 p_business_group_id => l_business_group_id,
201 p_token_value => l_end_date);
202
203 --Added for bug fix 3567562,to get the consolidation set parameter
204 pay_ie_p30lock.get_all_parameters (
205 p_payroll_action_id => pactid
206 , p_token => 'CONSOLIDATION'
207 , p_business_group_id => l_business_group_id
208 , p_token_value => l_consolidation_set);
209
210 hr_utility.set_location('Step ' || l_proc,20);
211 hr_utility.set_location('l_end_date = ' || l_end_date,20);
212 l_canonical_end_date := TO_DATE(l_end_date,'yyyy/mm/dd');
213 FOR csr_rec IN csr_assact
214 LOOP
215 IF l_arch_action_id <> csr_rec.assignment_action_id THEN
216 hr_utility.set_location('inside loop ' || l_proc,12);
217 hr_utility.set_location('-----------------------------------------',13);
218 hr_utility.set_location('Assignment_action_id ' || csr_rec.assignment_action_id,14);
219 hr_utility.set_location('pactid_id ' || pactid,15);
220 hr_utility.set_location('Payroll_action_id ' || csr_rec.payroll_action_id,15);
221 hr_utility.set_location('-----------------------------------------',17);
222 SELECT pay_assignment_actions_s.NEXTVAL
223 INTO l_actid
224 FROM dual;
225 hr_nonrun_asact.insact(l_actid,csr_rec.assignment_id,pactid,chunk,csr_rec.tax_unit_id);
226 hr_utility.set_location('created pay_ass_act ' || l_actid || ' to ' || pactid,20);
227 hr_nonrun_asact.insint(l_actid, csr_rec.assignment_action_id);
228 hr_utility.set_location('created interlocks ' || l_actid || ' to ' || csr_rec.assignment_action_id,20);
229 l_arch_action_id := csr_rec.assignment_action_id;
230 END IF;
231 END LOOP;
232
233 hr_utility.set_location('Leaving ' || l_proc,20);
234
235 END prg_assignment_action_code;
236
237
238 -- Second Lock Process for p30 Report Lock process
239
240 PROCEDURE rep_assignment_action_code(pactid in number,
241 stperson in number,
242 endperson in number,
243 chunk in number) is
244
245 l_actid NUMBER;
246 l_canonical_end_date DATE;
247 l_pay_action_id VARCHAR2(20);
248 l_business_group_id NUMBER;
249 l_proc VARCHAR2(150) := g_package||'.rep_assignment_action_code';
250
251
252 CURSOR csr_assact is
253 select paa.assignment_action_id,
254 paa.assignment_id,
255 paa.payroll_action_id,
256 ppa.date_earned,
257 paa.tax_unit_id
258 from pay_payroll_actions ppa,
259 pay_assignment_actions paa
260 -- per_assignments_f asg -- Bug Fix 4260031
261 where ppa.payroll_action_id = l_pay_action_id
262 -- and asg.business_group_id = ppa.business_group_id -- Bug Fix 4260031
263 -- and asg.person_id between stperson and endperson
264 -- and asg.assignment_id = paa.assignment_id
265 and paa.source_action_id is null
266 and paa.payroll_action_id = ppa.payroll_action_id
267 -- and ppa.date_earned between asg.effective_start_date and asg.effective_end_date
268 and paa.assignment_id in (select asg.assignment_id -- Bug Fix 4260031
269 from per_assignments_f asg
270 where asg.business_group_id = ppa.business_group_id
271 and asg.person_id between stperson and endperson)
272 and ppa.action_status = 'C'
273 and ppa.action_type = 'X'
274 and ppa.report_type = 'IEP30_PRGLOCK'
275 and ppa.report_category = 'ARCHIVE'
276 -- bug fix 5371061, added ordered join to remove merge join cartesian.
277 and not exists (select /*+ ORDERED */ null
278 from pay_payroll_actions payact,
279 pay_assignment_actions assact,
280 pay_action_interlocks alock
281 where alock.locked_action_id = paa.assignment_action_id
282 and assact.assignment_action_id = alock.locking_action_id
283 and assact.payroll_action_id = payact.payroll_action_id
284 and payact.action_type = 'X'
285 and payact.report_type = 'IEP30_REPLOCK'
286 and payact.report_category = 'ARCHIVE')
287 ORDER BY paa.assignment_id;
288
289
290 BEGIN
291
292 hr_utility.set_location('Entering ' || l_proc,10);
293
294 pay_ie_p30lock.get_all_parameters
295 (p_payroll_action_id => pactid,
296 p_token => 'PAYROLL_ACTION_ID',
297 p_business_group_id => l_business_group_id,
298 p_token_value => l_pay_action_id);
299
300
301 hr_utility.set_location('Step ' || l_proc,20);
302 hr_utility.set_location('l_pay_action_id = ' || l_pay_action_id,20);
303
304 FOR csr_rec IN csr_assact
305 LOOP
306 hr_utility.set_location('inside loop ' || l_proc,12);
307 hr_utility.set_location('-----------------------------------------',13);
308 hr_utility.set_location('Assignment_action_id ' || csr_rec.assignment_action_id,14);
309 hr_utility.set_location('Pact_id ' || pactid,15);
310 hr_utility.set_location('Payroll_action_id ' || csr_rec.payroll_action_id,15);
311 hr_utility.set_location('-----------------------------------------',17);
312 SELECT pay_assignment_actions_s.NEXTVAL
313 INTO l_actid
314 FROM dual;
315 hr_nonrun_asact.insact(l_actid,csr_rec.assignment_id,pactid,chunk,csr_rec.tax_unit_id);
316 hr_utility.set_location('created pay_ass_act ' || l_actid || ' to ' || pactid,20);
317 hr_nonrun_asact.insint(l_actid, csr_rec.assignment_action_id);
318 hr_utility.set_location('created interlocks ' || l_actid || ' to ' || csr_rec.assignment_action_id,20);
319
320 END LOOP;
321
322 hr_utility.set_location('Leaving ' || l_proc,20);
323
324 END rep_assignment_action_code;
325
326 ---------------------------------------------------------------------
327 -- Procedure generate_xml - Generates P30 XML Output File
328 ---------------------------------------------------------------------
329 PROCEDURE generate_xml(
330 errbuf out NOCOPY varchar2
331 ,retcode out NOCOPY varchar2
332 ,p_p30_data_lock_process in number
333 ,p_supplementary_run in varchar2
334 ,p_period_type in varchar2) IS
335
336
337 CURSOR cur_employer_info(
338 c_p30_data_lock_process pay_payroll_actions.payroll_action_id%TYPE) IS
339 SELECT ppa_p30.payroll_action_id report_id,
340 paa_p30.assignment_id assignment_id,
341 pact_er.action_information5 employer_paye_number
342 FROM pay_payroll_actions ppa_p30,
343 pay_assignment_actions paa_p30,
344 pay_action_interlocks pai_p30,
345 pay_assignment_actions paa_arc,
346 pay_action_interlocks pai_arc,
347 pay_assignment_actions paa_prepay,
348 pay_action_interlocks pai_prepay,
349 pay_action_information pact_er,
350 pay_action_information pact_ee
351 -- pay_pre_payments ppp -- Bug Fix 3725003
352 WHERE ppa_p30.payroll_action_id = c_p30_data_lock_process
353 AND ppa_p30.payroll_action_id = paa_p30.payroll_action_id
354 AND paa_p30.assignment_action_id = pai_p30.locking_action_id
355 AND paa_arc.assignment_action_id = pai_p30.locked_action_id
356 AND paa_arc.assignment_action_id = pai_arc.locking_action_id
357 AND paa_prepay.assignment_action_id = pai_arc.locked_action_id
358 AND paa_prepay.assignment_action_id = pai_prepay.locking_action_id
359 AND pact_er.action_context_type = 'PA'
360 AND pact_er.action_information_category = 'EMEA PAYROLL INFO'
364 AND pact_ee.action_context_id = paa_arc.assignment_action_id
361 AND pact_er.action_context_id = paa_arc.payroll_action_id
362 AND pact_er.action_information1 = paa_prepay.payroll_action_id
363 AND pact_ee.action_information_category = 'EMPLOYEE DETAILS'
365 --Added for Bug fix : 3725003
366 -- AND ppp.assignment_action_id = pai_arc.locked_action_id
367 GROUP BY ppa_p30.payroll_action_id
368 ,paa_p30.assignment_id
369 ,pact_er.action_information5
370 ORDER BY ppa_p30.payroll_action_id;
371 --
372 CURSOR cur_get_archive_pactid(
373 c_p30_data_lock_process pay_payroll_actions.payroll_action_id%TYPE) IS
374 SELECT max(ppa_arc.payroll_action_id) archive_pactid
375 FROM pay_assignment_actions paa_p30,
376 pay_action_interlocks pai_p30,
377 pay_assignment_actions paa_arc,
378 pay_payroll_actions ppa_arc
379 WHERE paa_p30.payroll_Action_id = c_p30_data_lock_process
380 AND paa_p30.assignment_action_id = pai_p30.locking_action_id
381 AND paa_arc.assignment_action_id = pai_p30.locked_action_id
382 AND ppa_arc.payroll_action_id = paa_arc.payroll_action_id;
383 --
384 CURSOR cur_p30_start_date(
385 c_p30_data_lock_process pay_payroll_actions.payroll_action_id%TYPE) IS
386 SELECT to_char(MIN(ppa_arc.start_date),'DD/MM/RRRR') start_date
387 FROM pay_assignment_actions paa_p30,
388 pay_action_interlocks pai_p30,
389 pay_assignment_actions paa_arc,
390 pay_payroll_actions ppa_arc
391 WHERE paa_p30.payroll_Action_id = c_p30_data_lock_process
392 AND paa_p30.assignment_action_id = pai_p30.locking_action_id
393 AND paa_arc.assignment_action_id = pai_p30.locked_action_id
394 AND ppa_arc.payroll_action_id = paa_arc.payroll_action_id;
395 --
396 CURSOR cur_employer_address(
397 c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE) IS
398 SELECT substr(pai.action_information5,1,30) employer_tax_addr1
399 ,substr(pai.action_information6,1,30) employer_tax_addr2
400 ,substr(pai.action_information7,1,30) employer_tax_addr3
401 ,substr(pai.action_information26,1,30) employer_tax_contact
402 ,substr(pai.action_information27,1,12) employer_tax_ref_phone
403 ,substr(pai.action_information28,1,30) employer_tax_rep_name
404 FROM pay_action_information pai
405 WHERE pai.action_context_id = c_payroll_action_id
406 AND pai.action_context_type = 'PA'
407 AND pai.action_information_category = 'ADDRESS DETAILS'
408 AND pai.action_information14 = 'IE Employer Tax Address';
409 --
410 -- 4317512
411 /* YTD_Balances fill fetch that balance value whose source ids effective date is less than effective date
412 of P30 datalock */
413 CURSOR YTD_Balances (vp_Payroll_Action_Id Pay_Payroll_Actions.Payroll_Action_ID%TYPE,
414 vp_Assignment_Id Pay_Assignment_Actions.Assignment_Action_ID%TYPE,
415 vp_Balance_Name Pay_Balance_Types.Balance_Name%TYPE,
416 vp_Dimension_Name varchar2,
417 vp_action_context_id number,
418 vp_effective_date date)IS -- SR 17318286.6 rbhardwa
419 SELECT pact_ytdbal.action_information4 Balance_Value
420 FROM pay_assignment_actions paa_p30,
421 pay_action_interlocks pai_p30,
422 pay_assignment_actions paa_arc,
423 pay_action_information pact_ytdbal,
424 pay_defined_balances pdb_ytdbal,
425 pay_balance_types pbt_ytdbal,
426 pay_balance_dimensions pbd_ytdbal,
427 pay_assignment_actions paa_src,
428 pay_payroll_actions ppa_src
429 WHERE paa_p30.payroll_action_id = vp_Payroll_Action_Id
430 AND paa_p30.assignment_id = vp_Assignment_Id
431 AND paa_p30.assignment_action_id = pai_p30.locking_action_id
432 AND paa_arc.assignment_action_id = pai_p30.locked_action_id
433 AND pact_ytdbal.action_information_category= 'EMEA BALANCES'
434 AND pact_ytdbal.ACTION_CONTEXT_ID = vp_action_context_id --SR 17318286.6 rbhardwa
435 AND pact_ytdbal.ACTION_CONTEXT_ID = paa_arc.assignment_action_id
436 AND pact_ytdbal.ACTION_CONTEXT_TYPE = 'AAP'
437 AND pdb_ytdbal.defined_balance_id = pact_ytdbal.action_information1
438 AND pdb_ytdbal.balance_type_id = pbt_ytdbal.balance_type_id
439 AND pbt_ytdbal.balance_name = vp_Balance_Name
440 AND pbd_ytdbal.dimension_name = vp_Dimension_Name
441 AND pbd_ytdbal.balance_dimension_id = pdb_ytdbal.balance_dimension_id
442 AND pbt_ytdbal.legislation_code = 'IE'
443 AND pact_ytdbal.action_context_type = 'AAP'
444 AND paa_src.assignment_action_id = pact_ytdbal.source_id
445 AND paa_src.payroll_action_id = ppa_src.payroll_action_id
446 AND ppa_src.effective_date <= vp_effective_date
447 ORDER BY pact_ytdbal.effective_date DESC -- Fix Tar 4033038.994
448 ,pact_ytdbal.ACTION_CONTEXT_ID DESC
449 ,nvl(pact_ytdbal.action_information5,0) DESC;
450 --
451 CURSOR cur_get_prev_p30_lock_id (vp_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE
452 ,vp_assignment_id pay_assignment_actions.assignment_action_id%TYPE
453 ,vp_tax_start_date date
454 ,vp_report_end_date date) IS
455 SELECT ppa.payroll_action_id prev_data_lock_id,
459 WHERE ppa.payroll_action_id = paa.payroll_action_id
456 to_date(substr(ppa.legislative_parameters,instr(ppa.legislative_parameters,'END_DATE=')+9,10),'YYYY/MM/DD')
457 FROM pay_payroll_actions ppa
458 ,pay_assignment_actions paa
460 AND ppa.report_type = 'IEP30_PRGLOCK'
461 AND paa.assignment_id = vp_assignment_id
462 AND paa.assignment_action_id = (
463 SELECT to_number(substr(max(lpad(paa2.action_sequence,15,'0')||
464 paa2.assignment_action_id),16))--Bug No 3318509
465 FROM pay_payroll_actions ppa2
466 ,pay_assignment_actions paa2
467 WHERE ppa2.payroll_action_id = paa2.payroll_action_id
468 AND ppa2.report_type = 'IEP30_PRGLOCK'
469 AND paa2.assignment_id = vp_assignment_id
470 AND ppa2.payroll_action_id <> vp_payroll_action_id
471 AND to_date(substr(ppa2.legislative_parameters ,instr(ppa2.legislative_parameters,'END_DATE=')+9,10),'YYYY/MM/DD')
472 BETWEEN vp_tax_start_date AND vp_report_end_date)
473 ORDER BY ppa.payroll_action_id DESC;
474
475 -- Report End Date
476 CURSOR cur_end_date(vp_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE) IS
477 SELECT to_date(substr(ppa_p30.legislative_parameters,instr(ppa_p30.legislative_parameters,'END_DATE=')+9,10),'YYYY/MM/DD') end_date
478 FROM pay_payroll_actions ppa_p30
479 WHERE ppa_p30.payroll_action_id=vp_payroll_action_id;
480
481 -- Start date of Tax Year
482 CURSOR cur_get_start_date (vp_report_end_date date) IS
483 SELECT to_date(rule_mode || '/' || to_char(vp_report_end_date,'YYYY'),'dd/mm/yyyy')
484 FROM pay_legislation_rules
485 WHERE legislation_code = 'IE'
486 AND rule_type = 'L';
487
488 /* SR 17318286.6 rbhardwa changes start here */
489 --Bug Fix 4032212 This cursor is added to get the max action_context_id against the P30 Data Lock id
490 -- Changed to fetch correct action context if Archiver is run for Period 2 first and then for period1
491 CURSOR get_action_context (vp_Payroll_Action_Id Pay_Payroll_Actions.Payroll_Action_ID%TYPE,
492 vp_Assignment_Id Pay_Assignment_Actions.Assignment_Action_ID%TYPE) IS
493 SELECT fnd_number.canonical_to_number(substr(max(lpad(paa_run.action_sequence,15,'0')||pact_ytdbal.ACTION_CONTEXT_ID),16))
494 FROM pay_assignment_actions paa_p30,
495 pay_action_interlocks pai_p30,
496 pay_assignment_actions paa_arc,
497 pay_action_information pact_ytdbal,
498 pay_action_interlocks pai_arc,
499 pay_assignment_actions paa_run,
500 pay_payroll_actions ppa_run
501 -- ,pay_pre_payments ppp --Bug Fix 4049831 Added join with pay_pre_payments table
502 WHERE paa_p30.payroll_action_id = vp_Payroll_Action_Id
503 AND paa_p30.assignment_id = vp_Assignment_Id
504 AND paa_p30.assignment_action_id = pai_p30.locking_action_id
505 AND paa_arc.assignment_action_id = pai_p30.locked_action_id
506 AND paa_arc.assignment_action_id = pai_arc.locking_action_id
507 AND pact_ytdbal.ACTION_CONTEXT_ID = paa_arc.assignment_action_id
508 AND paa_run.assignment_action_id = pai_arc.locked_action_id
509 AND pact_ytdbal.action_context_type = 'AAP'
510 AND paa_run.source_action_id IS NULL
511 AND paa_run.payroll_action_id = ppa_run.payroll_action_id
512 AND ppa_run.action_type in ('R','Q');
513
514 /* SR 17318286.6 rbhardwa changes end here */
515
516 v_prev_data_lock_id number;
517 v_pre_date_eff_date date;
518 v_cur_employer_info cur_employer_info%ROWTYPE;
519 v_Curr_YTD_Balances YTD_Balances%ROWTYPE;
520 v_Prev_YTD_Balances YTD_Balances%ROWTYPE;
521
522 v_action_context_id Number(15,0); --SR 17318286.6 rbhardwa
523
524 --
525 l_report_end_date date;
526 l_tax_start_date date;
527 --
528 l_PAYE_PTD number:=0;
529 l_EE_PRSI_PTD number:=0;
530 l_ER_PRSI_PTD number:=0;
531 --
532 l_PAYE_YTD number:=0;
533 l_EE_PRSI_YTD number:=0;
534 l_ER_PRSI_YTD number:=0;
535 --
536 l_root_start_tag varchar2(200);
537 l_root_end_tag varchar2(50);
538 --
539 l_employer_start_tag varchar2(20);
540 l_employer_end_tag varchar2(20);
541 --
542 l_p30_start_tag varchar2(20);
543 l_p30_end_tag varchar2(20);
544 --
545 l_archive_pactid pay_payroll_actions.payroll_action_id%TYPE;
546 --
547 l_employer_paye_number varchar2(80);
548 l_employer_number varchar2(10);
549 l_employer_name varchar2(30);
550 l_employer_add1 varchar2(30);
551 l_employer_add2 varchar2(30);
552 l_employer_add3 varchar2(30);
553 l_employer_contact varchar2(20);
554 l_employer_phone varchar2(12);
555 --
556 l_p30_start varchar2(10);
557 l_p30_paye number:=0;
558 l_p30_prsi number:=0;
559 l_p30_type varchar2(1);
560 l_period_type varchar2(1); -- For bug 5119350
561
562 BEGIN
563
564 l_root_start_tag :='<P30File currency="E" formversion="1" language="E">';
565 l_root_end_tag :='</P30File>';
566
567 l_employer_start_tag :=' <Employer ';
568 l_employer_end_tag :=' />';
569
570 l_p30_start_tag :=' <P30 ';
571 l_p30_end_tag :=' />';
572
573 -- Get payroll_action_id of the legislative archive process
574 OPEN cur_get_archive_pactid(p_p30_data_lock_process);
578 -- Get Employer Address
575 FETCH cur_get_archive_pactid INTO l_archive_pactid;
576 CLOSE cur_get_archive_pactid;
577
579 OPEN cur_employer_address(l_archive_pactid);
580 FETCH cur_employer_address INTO l_employer_add1
581 ,l_employer_add2
582 ,l_employer_add3
583 ,l_employer_contact
584 ,l_employer_phone
585 ,l_employer_name;
586 CLOSE cur_employer_address;
587
588
589
590 -- Start of xml doc
591 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<?xml version="1.0" encoding="UTF-8"?>');
592
593 -- P30File root ELEMENT
594 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_root_start_tag);
595
596 -- Bug 2659864 - 'This Report' Figure calculation logic changed to
597 -- (Curr P30 YTD value - Prev P30 YTD Value)
598
599 -- Report End Date
600 OPEN cur_end_date(p_p30_data_lock_process);
601 FETCH cur_end_date INTO l_report_end_date;
602 CLOSE cur_end_date;
603
604 -- Get Tax Year Start Date
605 OPEN cur_get_start_date(l_report_end_date);
606 FETCH cur_get_start_date INTO l_tax_start_date;
607 CLOSE cur_get_start_date;
608
609 FOR v_cur_employer_info IN cur_employer_info(p_p30_data_lock_process) LOOP
610 l_employer_paye_number := v_cur_employer_info.employer_paye_number;
611 --
612 -- SR 17318286.6 rbhardwa changes start here
613 OPEN get_action_context(v_cur_employer_info.report_id,v_cur_employer_info.assignment_id);
614 FETCH get_action_context INTO v_action_context_id;
615 CLOSE get_action_context;
616 -- SR 17318286.6 rbhardwa changes end here
617
618 --Fetch Curr PAYE YTD Balance from PAY ACTION INFORMATION
619 OPEN YTD_Balances(v_cur_employer_info.report_id
620 ,v_cur_employer_info.assignment_id
621 ,'IE Net Tax'
622 ,'_ASG_YTD'
623 ,v_action_context_id
624 ,l_report_end_date);
625 FETCH YTD_Balances INTO v_Curr_YTD_Balances;
626 CLOSE YTD_Balances;
627 l_PAYE_YTD := NVL(v_Curr_YTD_Balances.Balance_Value,0);
628 v_Curr_YTD_Balances.Balance_Value := NULL;
629 --Fetch Curr EE PRSI YTD Balance from PAY ACTION INFORMATION
630 OPEN YTD_Balances(v_cur_employer_info.report_id
631 ,v_cur_employer_info.assignment_id
632 ,'IE PRSI Employee'
633 ,'_ASG_YTD'
634 ,v_action_context_id
635 ,l_report_end_date);
636 FETCH YTD_Balances INTO v_Curr_YTD_Balances;
637 CLOSE YTD_Balances;
638 l_EE_PRSI_YTD := NVL(v_Curr_YTD_Balances.Balance_Value,0);
639 v_Curr_YTD_Balances.Balance_Value := NULL;
640 -- Bug 3436737 : Added code to sum up K and M figures for a severance
641 -- payment
642 OPEN YTD_Balances(v_cur_employer_info.report_id
643 ,v_cur_employer_info.assignment_id
644 ,'IE PRSI K Employee Lump Sum'
645 ,'_ASG_YTD'
646 ,v_action_context_id
647 ,l_report_end_date);
648 FETCH YTD_Balances INTO v_Curr_YTD_Balances;
649 CLOSE YTD_Balances;
650 l_EE_PRSI_YTD := l_EE_PRSI_YTD + NVL(v_Curr_YTD_Balances.Balance_Value,0);
651 v_Curr_YTD_Balances.Balance_Value := NULL;
652 OPEN YTD_Balances(v_cur_employer_info.report_id
653 ,v_cur_employer_info.assignment_id
654 ,'IE PRSI M Employee Lump Sum'
655 ,'_ASG_YTD'
656 ,v_action_context_id
657 ,l_report_end_date);
658 FETCH YTD_Balances INTO v_Curr_YTD_Balances;
659 CLOSE YTD_Balances;
660 l_EE_PRSI_YTD := l_EE_PRSI_YTD + NVL(v_Curr_YTD_Balances.Balance_Value,0);
661 v_Curr_YTD_Balances.Balance_Value := NULL;
662 -- Total PRSI Employee figure has been evaluated above
663 --Fetch Curr ER PRSI YTD Balance from PAY ACTION INFORMATION
664 OPEN YTD_Balances(v_cur_employer_info.report_id
665 ,v_cur_employer_info.assignment_id
666 ,'IE PRSI Employer'
667 ,'_ASG_YTD'
668 ,v_action_context_id
669 ,l_report_end_date);
670 FETCH YTD_Balances INTO v_Curr_YTD_Balances;
671 CLOSE YTD_Balances;
672 l_ER_PRSI_YTD := NVL(v_Curr_YTD_Balances.Balance_Value,0);
673 v_Curr_YTD_Balances.Balance_Value := NULL;
674 OPEN YTD_Balances(v_cur_employer_info.report_id
675 ,v_cur_employer_info.assignment_id
676 ,'IE PRSI K Employer Lump Sum'
677 ,'_ASG_YTD'
678 ,v_action_context_id
679 ,l_report_end_date);
680 FETCH YTD_Balances INTO v_Curr_YTD_Balances;
681 CLOSE YTD_Balances;
682 l_ER_PRSI_YTD := l_ER_PRSI_YTD + NVL(v_Curr_YTD_Balances.Balance_Value,0);
683 v_Curr_YTD_Balances.Balance_Value := NULL;
684 OPEN YTD_Balances(v_cur_employer_info.report_id
685 ,v_cur_employer_info.assignment_id
686 ,'IE PRSI M Employer Lump Sum'
687 ,'_ASG_YTD'
688 ,v_action_context_id
689 ,l_report_end_date);
690 FETCH YTD_Balances INTO v_Curr_YTD_Balances;
691 CLOSE YTD_Balances;
695 -- Fetch PAYE/PRSI 'This Report' values
692 l_ER_PRSI_YTD := l_ER_PRSI_YTD + NVL(v_Curr_YTD_Balances.Balance_Value,0);
693 v_Curr_YTD_Balances.Balance_Value := NULL;
694 -- Total PRSI Employer figure has been evaluated above
696 -- The 'This Report' value are calculated as (Current P30s YTD values - Previous P30s YTD values)
697 OPEN cur_get_prev_p30_lock_id(v_cur_employer_info.report_id
698 ,v_cur_employer_info.assignment_id
699 ,l_tax_start_date
700 ,l_report_end_date);
701 FETCH cur_get_prev_p30_lock_id INTO v_prev_data_lock_id,v_pre_date_eff_date;
702 IF cur_get_prev_p30_lock_id%NOTFOUND THEN
703 v_prev_data_lock_id :=NULL;
704 v_pre_date_eff_date := NULL;
705 l_PAYE_PTD := l_PAYE_YTD;
706 l_EE_PRSI_PTD := l_EE_PRSI_YTD;
707 l_ER_PRSI_PTD := l_ER_PRSI_YTD;
708 ELSE
709
710 -- SR 17318286.6 rbhardwa changes start here
711 OPEN get_action_context(v_prev_data_lock_id,v_cur_employer_info.assignment_id);
712 FETCH get_action_context INTO v_action_context_id;
713 CLOSE get_action_context;
714 -- SR 17318286.6 rbhardwa changes end here
715
716
717 --Fetch Prev PAYE YTD Balance from PAY ACTION INFORMATION of Prev P30 Run
718 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);
719 FETCH YTD_Balances INTO v_Prev_YTD_Balances;
720 CLOSE YTD_Balances;
721 l_PAYE_PTD := l_PAYE_YTD - NVL(v_Prev_YTD_Balances.Balance_Value,0);
722 v_Prev_YTD_Balances.Balance_Value := NULL;
723
724 --Fetch Prev EE PRSI YTD Balance from PAY ACTION INFORMATION of Prev P30 Run
725 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);
726 FETCH YTD_Balances INTO v_Prev_YTD_Balances;
727 CLOSE YTD_Balances;
728 l_EE_PRSI_PTD := l_EE_PRSI_YTD - NVL(v_Prev_YTD_Balances.Balance_Value,0);
729 v_Prev_YTD_Balances.Balance_Value := NULL;
730
731 -- Bug 3436737 : Added code to sum up K and M Employee figures for a severance
732 -- payment
733 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);
734 FETCH YTD_Balances INTO v_Prev_YTD_Balances;
735 CLOSE YTD_Balances;
736 l_EE_PRSI_PTD := l_EE_PRSI_PTD - NVL(v_Prev_YTD_Balances.Balance_Value,0);
737 v_Prev_YTD_Balances.Balance_Value := NULL;
738
739 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);
740 FETCH YTD_Balances INTO v_Prev_YTD_Balances;
741 CLOSE YTD_Balances;
742 l_EE_PRSI_PTD := l_EE_PRSI_PTD - NVL(v_Prev_YTD_Balances.Balance_Value,0);
743 v_Prev_YTD_Balances.Balance_Value := NULL;
744
745 --Fetch Prev ER PRSI YTD Balance from PAY ACTION INFORMATION of Prev P30
746 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);
747 FETCH YTD_Balances INTO v_Prev_YTD_Balances;
748 CLOSE YTD_Balances;
749 l_ER_PRSI_PTD := l_ER_PRSI_YTD - NVL(v_Prev_YTD_Balances.Balance_Value,0);
750 v_Prev_YTD_Balances.Balance_Value := NULL;
751
752 -- Added code to sum up K and M Employer figures for a severance payment
753 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);
754 FETCH YTD_Balances INTO v_Prev_YTD_Balances;
755 CLOSE YTD_Balances;
756 l_ER_PRSI_PTD := l_ER_PRSI_PTD - NVL(v_Prev_YTD_Balances.Balance_Value,0);
757 v_Prev_YTD_Balances.Balance_Value := NULL;
758
759 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);
760 FETCH YTD_Balances INTO v_Prev_YTD_Balances;
761 CLOSE YTD_Balances;
762 l_ER_PRSI_PTD := l_ER_PRSI_PTD - NVL(v_Prev_YTD_Balances.Balance_Value,0);
763 v_Prev_YTD_Balances.Balance_Value := NULL;
764
765 END IF;
766 CLOSE cur_get_prev_p30_lock_id;
767 l_p30_paye := l_p30_paye + NVL(l_PAYE_PTD,0);
768 l_p30_prsi := l_p30_prsi + NVL(l_EE_PRSI_PTD,0) + NVL(l_ER_PRSI_PTD,0);
769
770 END LOOP;
771
772 -- Employer ELEMENT
773 FND_FILE.PUT(FND_FILE.OUTPUT,l_employer_start_tag);
774
775 FND_FILE.PUT(FND_FILE.OUTPUT,'number="' || l_employer_paye_number ||'" ');
776 FND_FILE.PUT(FND_FILE.OUTPUT,'name="' || l_employer_name ||'" ');
777
778 IF l_employer_add1 IS NOT NULL THEN
779 FND_FILE.PUT(FND_FILE.OUTPUT,'address1="' || l_employer_add1 ||'" ');
780 END IF;
781
782 IF l_employer_add2 IS NOT NULL THEN
783 FND_FILE.PUT(FND_FILE.OUTPUT,'address2="' || l_employer_add2 ||'" ');
784 END IF;
785
786 IF l_employer_add3 IS NOT NULL THEN
787 FND_FILE.PUT(FND_FILE.OUTPUT,'address3="' || l_employer_add3 ||'" ');
788 END IF;
789
790 FND_FILE.PUT(FND_FILE.OUTPUT,'contact="' || l_employer_contact ||'" ');
791
792 IF l_employer_phone IS NOT NULL THEN
793 FND_FILE.PUT(FND_FILE.OUTPUT,'phone="' || replace(replace(l_employer_phone,'('),')') ||'" ');
794 END IF;
795
796
797
798 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_employer_end_tag);
799
800 -- P30 ELEMENT
801 FND_FILE.PUT(FND_FILE.OUTPUT,l_p30_start_tag);
802
803 -- Get Start Date
804 OPEN cur_p30_start_date(p_p30_data_lock_process);
805 FETCH cur_p30_start_date INTO l_p30_start;
806 CLOSE cur_p30_start_date;
807
808 IF p_supplementary_run = 'Y' THEN
809 l_p30_type := '1';
810 ELSE
811 l_p30_type := '0';
812 END IF;
813 -- For bug 5119350, checks if period type is monthly set it to 0 else 1.
814 IF p_period_type='M' then
815 l_period_type := '0';
816 ELSE
817 l_period_type := '1';
818 END IF;
819
820 FND_FILE.PUT(FND_FILE.OUTPUT,'period="' || l_period_type ||'" ');
821 -- End bug 5119350
822 FND_FILE.PUT(FND_FILE.OUTPUT,'start="' || l_p30_start ||'" ');
823
824 /* Bug 2502060 P30 XML FAILED REVENUE ON LINE VALIDATION
825 Change in requirement- Totals in PAYE and PRSI element to display
826 in whole Euros and with no ',' seperating the thousands.
827
828 FND_FILE.PUT(FND_FILE.OUTPUT,'PAYE="' || to_char(l_p30_paye,'FM999G999G999') ||'" ');
829 FND_FILE.PUT(FND_FILE.OUTPUT,'PRSI="' || to_char(l_p30_prsi,'FM999G999G999') ||'" ');
830
831 */
832
833 FND_FILE.PUT(FND_FILE.OUTPUT,'PAYE="' || to_char(l_p30_paye,'FM999999999') ||'" ');
834 FND_FILE.PUT(FND_FILE.OUTPUT,'PRSI="' || to_char(l_p30_prsi,'FM999999999') ||'" ');
835 FND_FILE.PUT(FND_FILE.OUTPUT,'type="' || l_p30_type ||'" ');
836
837 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_p30_end_tag);
838
839 -- End of ROOT P30File ELEMENT
840 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_root_end_tag);
841
842 END generate_xml;
843
844 END;