[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_ROLLBACK_PKG
Source
1 PACKAGE BODY PAY_GB_ROLLBACK_PKG AS
2 /* $Header: pygbrlbp.pkb 120.0.12020000.10 2013/02/06 06:21:56 rajganga noship $ */
3 /*===========================================================================+
4 | Copyright (c) 2012 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================
8 Name:
9 PAY_GB_ROLLBACK
10 Purpose:
11
12 This is a UK Specific payroll package.
13 History:
14 14-Sep-2012 krreddy 115.0 14576477 Created.
15 03-Oct-2012 krreddy 115.1 14704814 Included Additional Rollback funtionality
16 for EAS Process.
17 02-Nov-2012 ssarap 115.2 For NI only aggregation deleting the orphan data
18 in PAY_GB_FPS_DETAILS table.
19 02-Nov-2012 kvinayku 115.3 14843731 Added one more condition to reset pensioner RTI sent flag.
20 26-Nov-2012 sampmand 115.4 EAS 2013 Changes-Added RTI_EAS_REP_13 for new EAS program
21 29-Nov-2012 rajganga 115.5 15914459 Fixed Rti sent for other asgs of PAYE Agg.
22 27-Dec-2012 rajganga 115.6 15890022 FPS 2013-14 Changes.
23 6-Feb-2013 rajganga 115.9 15890022 EYU Changes.
24 =============================================================================*/
25 --
26 --
27 FUNCTION get_version(
28 p_assig_id IN NUMBER,
29 p_type IN VARCHAR2,
30 p_aei_id OUT nocopy NUMBER)
31 RETURN NUMBER
32 IS
33 l_proc CONSTANT VARCHAR2(50):= 'get_version';
34 l_ovn NUMBER;
35 CURSOR csr_ovn
36 IS
37 SELECT object_version_number,
38 assignment_extra_info_id
39 FROM per_assignment_extra_info
40 WHERE assignment_id = p_assig_id
41 AND information_type = p_type;
42 BEGIN
43 hr_utility.set_location('Entering: '||l_proc,1);
44 OPEN csr_ovn;
45 FETCH csr_ovn INTO l_ovn, p_aei_id;
46 CLOSE csr_ovn;
47 RETURN l_ovn;
48 hr_utility.set_location('Leaving: '||l_proc,999);
49 END;
50
51 FUNCTION get_report_type(p_pactid IN NUMBER)
52 RETURN VARCHAR2
53 IS
54 l_proc CONSTANT VARCHAR2(50):= 'get_report_type';
55 l_report_type varchar2(50);
56
57 CURSOR csr_report_type
58 IS
59 SELECT report_type
60 FROM pay_payroll_actions
61 WHERE payroll_action_id = p_pactid;
62
63 BEGIN
64 hr_utility.set_location('Entering: '||l_proc,1);
65
66 OPEN csr_report_type;
67 FETCH csr_report_type INTO l_report_type;
68 CLOSE csr_report_type;
69
70 hr_utility.set_location('l_report_type: '||l_report_type,1);
71
72 hr_utility.set_location('Leaving: '||l_proc,999);
73
74 RETURN l_report_type;
75
76 END;
77
78 PROCEDURE restored(
79 p_pactid IN NUMBER,
80 p_asgactid IN NUMBER default null)
81 IS
82 l_proc CONSTANT VARCHAR2(50):= 'restored';
83 l_ovn NUMBER;
84 l_aei_id NUMBER;
85 l_assignment_id NUMBER;
86 CURSOR csr_archive_details
87 IS
88 SELECT paa.assignment_id,
89 paa.assignment_action_id,
90 pai.action_information6, --EXPAT
91 pai.action_information7, --EXPAT
92 pai.action_information8, --EXPAT
93 pai.action_information9, --EXPAT
94 pai.action_information10, --EXPAT
95 pai.action_information12, --PENSIONER
96 pai.action_information26, --PENSIONER
97 pai.action_information4, --STARTER
98 pai.action_information5, --STARTER
99 pai.action_information20 -- Number of Periods Covered
100 FROM pay_assignment_actions paa,
101 pay_action_information pai
102 WHERE paa.payroll_action_id = p_pactid
103 AND paa.assignment_action_id = pai.action_context_id
104 AND pai.action_information_category = 'GB_RTI_FPS_ASG_DET1'
105 AND pai.action_context_type = 'AAP';
106
107 cursor csr_get_all_actions is
108 SELECT paa.assignment_id,
109 paa.assignment_action_id
110 FROM pay_assignment_actions paa
111 WHERE paa.payroll_action_id = p_pactid;
112
113 CURSOR csr_archive_asgact_details
114 IS
115 SELECT paa.assignment_id,
116 paa.assignment_action_id,
117 pai.action_information6, --EXPAT
118 pai.action_information7, --EXPAT
119 pai.action_information8, --EXPAT
120 pai.action_information9, --EXPAT
121 pai.action_information10, --EXPAT
122 pai.action_information12, --PENSIONER
123 pai.action_information26, --PENSIONER
124 pai.action_information4, --STARTER
125 pai.action_information5, --STARTER
126 pai.action_information20 -- Number of Periods Covered
127 FROM pay_assignment_actions paa,
128 pay_action_information pai
129 WHERE paa.payroll_action_id = p_pactid
130 AND paa.assignment_action_id = p_asgactid --9923676
131 AND paa.assignment_action_id = pai.action_context_id
132 AND pai.action_information_category = 'GB_RTI_FPS_ASG_DET1'
133 AND pai.action_context_type = 'AAP';
134
135 CURSOR csr_report_type
136 IS
137 SELECT report_type
138 FROM pay_payroll_actions
139 WHERE payroll_action_id = p_pactid;
140
141 l_asgact_rec csr_archive_asgact_details%rowtype;
142 l_report_type varchar2(50);
143 BEGIN
144 hr_utility.set_location('Entering: '||l_proc,1);
145 hr_utility.set_location('p_pactid: '||p_pactid,1);
146 hr_utility.set_location('p_asgactid: '||p_asgactid,1);
147
148 OPEN csr_report_type;
149 FETCH csr_report_type INTO l_report_type;
150 CLOSE csr_report_type;
151
152 hr_utility.set_location('l_report_type: '||l_report_type,1);
153
154 IF (NVL(l_report_type,'XXX') = 'RTI_FPS_REP') OR (NVL(l_report_type,'XXX') = 'RTI_FPS_REP_13') THEN
155
156 IF (p_asgactid is null) THEN
157 FOR archive_rec IN csr_archive_details
158 LOOP
159 -- Number of Periods Covered
160 l_ovn := get_version(archive_rec.assignment_id,'GB_PAY_RTI',l_aei_id);
161 IF l_ovn IS NOT NULL THEN
162 hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_PAY_RTI',
163 p_aei_information1 => archive_rec.action_information20);
164 END IF;
165 --EXPAT Flag
166 IF (archive_rec.action_information9 IS NOT NULL) OR (archive_rec.action_information10 IS NOT NULL) OR (archive_rec.action_information6 ='Y') OR (archive_rec.action_information7 ='Y') OR (archive_rec.action_information8 ='Y') THEN
167 l_ovn := get_version(archive_rec.assignment_id,'GB_RTI_ASG_DETAILS',l_aei_id);
168 IF l_ovn IS NOT NULL THEN
169 hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS',
170 p_aei_information19 => 'N');
171 END IF;
172 END IF;
173 --PENSIONER Flag
174 IF (archive_rec.action_information12 IS NOT NULL) OR (archive_rec.action_information26 IS NOT NULL) THEN
175 l_ovn := get_version(archive_rec.assignment_id,'GB_RTI_ASG_DETAILS',l_aei_id);
176 IF l_ovn IS NOT NULL THEN
177 hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS', p_aei_information9 => 'N');
178 END IF;
179 END IF;
180 --STARTER Flag
181 IF (archive_rec.action_information4 IS NOT NULL) OR (archive_rec.action_information5 IS NOT NULL) THEN
182 l_ovn := get_version(archive_rec.assignment_id,'GB_RTI_ASG_DETAILS',l_aei_id);
183 IF l_ovn IS NOT NULL THEN
184 hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS', p_aei_information8 => 'N');
185 END IF;
186 END IF;
187 -- Delete from pay_gb_fps_details
188 DELETE
189 FROM pay_gb_fps_details
190 WHERE FPS_ASG_ACT_ID = archive_rec.ASSIGNMENT_ACTION_ID
191 AND ASSIGNMENT_ID = archive_rec.ASSIGNMENT_ID;
192 END LOOP;
193 FOR del_rec IN csr_get_all_actions
194 loop
195 -- Delete from pay_gb_fps_details
196 DELETE
197 FROM pay_gb_fps_details
198 WHERE FPS_ASG_ACT_ID = del_rec.ASSIGNMENT_ACTION_ID
199 AND ASSIGNMENT_ID = del_rec.ASSIGNMENT_ID;
200 end loop;
201 ELSE -- for IF (p_asgactid is null) THEN condition
202
203 OPEN csr_archive_asgact_details;
204 FETCH csr_archive_asgact_details into l_asgact_rec;
205 l_ovn := get_version(l_asgact_rec.assignment_id,'GB_PAY_RTI',l_aei_id);
206 IF l_ovn IS NOT NULL THEN
207 hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_PAY_RTI',
208 p_aei_information1 => l_asgact_rec.action_information20);
209 END IF;
210 --EXPAT Flag
211 IF (l_asgact_rec.action_information9 IS NOT NULL) OR (l_asgact_rec.action_information10 IS NOT NULL) OR (l_asgact_rec.action_information6 ='Y') OR (l_asgact_rec.action_information7 ='Y') OR (l_asgact_rec.action_information8 ='Y') THEN
212 l_ovn := get_version(l_asgact_rec.assignment_id,'GB_RTI_ASG_DETAILS',l_aei_id);
213 IF l_ovn IS NOT NULL THEN
214 hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS',
215 p_aei_information19 => 'N');
216 END IF;
217 END IF;
218 --PENSIONER Flag
219 IF (l_asgact_rec.action_information12 IS NOT NULL) OR (l_asgact_rec.action_information26 IS NOT NULL) THEN
220 l_ovn := get_version(l_asgact_rec.assignment_id,'GB_RTI_ASG_DETAILS',l_aei_id);
221 IF l_ovn IS NOT NULL THEN
222 hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS', p_aei_information9 => 'N');
223 END IF;
224 END IF;
225 --STARTER Flag
226 IF (l_asgact_rec.action_information4 IS NOT NULL) OR (l_asgact_rec.action_information5 IS NOT NULL) THEN
227 l_ovn := get_version(l_asgact_rec.assignment_id,'GB_RTI_ASG_DETAILS',l_aei_id);
228 IF l_ovn IS NOT NULL THEN
229 hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS', p_aei_information8 => 'N');
230 END IF;
231 END IF;
232 -- Delete from pay_gb_fps_details
233 DELETE
234 FROM pay_gb_fps_details
235 WHERE FPS_ASG_ACT_ID = l_asgact_rec.ASSIGNMENT_ACTION_ID
236 AND ASSIGNMENT_ID = l_asgact_rec.ASSIGNMENT_ID;
237 CLOSE csr_archive_asgact_details;
238
239 END IF; -- for IF (p_asgactid is null) THEN condition
240 END IF;
241 END;
242 ---
243 PROCEDURE eyu_restored(
244 p_pactid IN NUMBER,
245 p_asgactid IN NUMBER default null)
246 IS
247 l_proc CONSTANT VARCHAR2(50):= 'restored';
248 l_ovn NUMBER;
249 l_aei_id NUMBER;
250 l_assignment_id NUMBER;
251 CURSOR csr_archive_details
252 IS
253 SELECT paa.assignment_id,
254 paa.assignment_action_id,
255 pai.action_information5, --EXPAT
256 pai.action_information6, --EXPAT
257 pai.action_information7, --EXPAT
258 pai.action_information8, --EXPAT
259 pai.action_information9, --EXPAT
260 pai.action_information11, --PENSIONER
261 pai.action_information12, --PENSIONER
262 pai.action_information3, --STARTER
263 pai.action_information4 --STARTER
264 FROM pay_assignment_actions paa,
265 pay_action_information pai
266 WHERE paa.payroll_action_id = p_pactid
267 AND paa.assignment_action_id = pai.action_context_id
268 AND pai.action_information_category = 'GB_RTI_EYU_ASG_DET'
269 AND pai.action_context_type = 'AAP';
270
271 cursor csr_get_all_actions is
272 SELECT paa.assignment_id,
273 paa.assignment_action_id
274 FROM pay_assignment_actions paa
275 WHERE paa.payroll_action_id = p_pactid;
276
277 CURSOR csr_archive_asgact_details
278 IS
279 SELECT paa.assignment_id,
280 paa.assignment_action_id,
281 pai.action_information5, --EXPAT
282 pai.action_information6, --EXPAT
283 pai.action_information7, --EXPAT
284 pai.action_information8, --EXPAT
285 pai.action_information9, --EXPAT
286 pai.action_information11, --PENSIONER
287 pai.action_information12, --PENSIONER
288 pai.action_information3, --STARTER
289 pai.action_information4 --STARTER
290 FROM pay_assignment_actions paa,
291 pay_action_information pai
292 WHERE paa.payroll_action_id = p_pactid
293 AND paa.assignment_action_id = p_asgactid --9923676
294 AND paa.assignment_action_id = pai.action_context_id
295 AND pai.action_information_category = 'GB_RTI_EYU_ASG_DET'
296 AND pai.action_context_type = 'AAP';
297
298 CURSOR csr_report_type
299 IS
300 SELECT report_type
301 FROM pay_payroll_actions
302 WHERE payroll_action_id = p_pactid;
303
304 l_asgact_rec csr_archive_asgact_details%rowtype;
305 l_report_type varchar2(50);
306 BEGIN
307 hr_utility.set_location('Entering: '||l_proc,1);
308 hr_utility.set_location('p_pactid: '||p_pactid,1);
309 hr_utility.set_location('p_asgactid: '||p_asgactid,1);
310
311 OPEN csr_report_type;
312 FETCH csr_report_type INTO l_report_type;
313 CLOSE csr_report_type;
314
315 hr_utility.set_location('l_report_type: '||l_report_type,1);
316
317 IF (NVL(l_report_type,'XXX') = 'RTI_EYU_REP_13') THEN
318
319 IF (p_asgactid is null) THEN
320 FOR archive_rec IN csr_archive_details
321 LOOP
322
323 --EXPAT Flag
324 IF (archive_rec.action_information8 IS NOT NULL) OR (archive_rec.action_information9 IS NOT NULL) OR (archive_rec.action_information5 ='Y') OR (archive_rec.action_information6 ='Y') OR (archive_rec.action_information7 ='Y') THEN
325 l_ovn := get_version(archive_rec.assignment_id,'GB_RTI_ASG_DETAILS',l_aei_id);
326 IF l_ovn IS NOT NULL THEN
327 hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS',
328 p_aei_information19 => 'N');
329 END IF;
330 END IF;
331 --PENSIONER Flag
332 IF (archive_rec.action_information11 IS NOT NULL) OR (archive_rec.action_information12 IS NOT NULL) THEN
333 l_ovn := get_version(archive_rec.assignment_id,'GB_RTI_ASG_DETAILS',l_aei_id);
334 IF l_ovn IS NOT NULL THEN
335 hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS', p_aei_information9 => 'N');
336 END IF;
337 END IF;
338 --STARTER Flag
339 IF (archive_rec.action_information3 IS NOT NULL) OR (archive_rec.action_information4 IS NOT NULL) THEN
340 l_ovn := get_version(archive_rec.assignment_id,'GB_RTI_ASG_DETAILS',l_aei_id);
341 IF l_ovn IS NOT NULL THEN
342 hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS', p_aei_information8 => 'N');
343 END IF;
344 END IF;
345
346 END LOOP;
347
348
349 ELSE -- for IF (p_asgactid is null) THEN condition
350
351 OPEN csr_archive_asgact_details;
352 FETCH csr_archive_asgact_details into l_asgact_rec;
353
354 --EXPAT Flag
355 IF (l_asgact_rec.action_information8 IS NOT NULL) OR (l_asgact_rec.action_information9 IS NOT NULL) OR (l_asgact_rec.action_information5 ='Y') OR (l_asgact_rec.action_information6 ='Y') OR (l_asgact_rec.action_information7 ='Y') THEN
356 l_ovn := get_version(l_asgact_rec.assignment_id,'GB_RTI_ASG_DETAILS',l_aei_id);
357 IF l_ovn IS NOT NULL THEN
358 hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS',
359 p_aei_information19 => 'N');
360 END IF;
361 END IF;
362 --PENSIONER Flag
363 IF (l_asgact_rec.action_information11 IS NOT NULL) OR (l_asgact_rec.action_information12 IS NOT NULL) THEN
364 l_ovn := get_version(l_asgact_rec.assignment_id,'GB_RTI_ASG_DETAILS',l_aei_id);
365 IF l_ovn IS NOT NULL THEN
366 hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS', p_aei_information9 => 'N');
367 END IF;
368 END IF;
369 --STARTER Flag
370 IF (l_asgact_rec.action_information3 IS NOT NULL) OR (l_asgact_rec.action_information4 IS NOT NULL) THEN
371 l_ovn := get_version(l_asgact_rec.assignment_id,'GB_RTI_ASG_DETAILS',l_aei_id);
372 IF l_ovn IS NOT NULL THEN
373 hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS', p_aei_information8 => 'N');
374 END IF;
375 END IF;
376
377 CLOSE csr_archive_asgact_details;
378
379 END IF; -- for IF (p_asgactid is null) THEN condition
380 END IF;
381 END eyu_restored;
382
383 PROCEDURE eas_restored(
384 p_pactid IN NUMBER,
385 p_asgactid IN NUMBER default null)
386 IS
387 l_proc CONSTANT VARCHAR2(50):= 'eas_restored';
388 l_ovn NUMBER;
389 l_aei_id NUMBER;
390
391 l_tax_ref varchar2(30);
392 l_eff_date varchar2(50);
393 l_effective_date date;
394 l_paye_agg varchar2(1);
395 l_assignment_id number;
396
397 CURSOR csr_asgs_processed
398 IS
399 SELECT DISTINCT paa.assignment_id
400 FROM pay_assignment_actions paa,
401 pay_payroll_actions ppa,
402 pay_action_information pai
403 WHERE paa.payroll_action_id = ppa.payroll_action_id
404 AND ppa.payroll_action_id = p_pactid --1688760
405 AND paa.assignment_action_id = pai.action_context_id;
406
407 cursor csr_fetch_details is
408 select fnd_date.canonical_to_date(substr(legislative_parameters,instr(legislative_parameters,'START=') + 6,10)) effective_date,
409 substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref
410 from pay_payroll_actions
411 where payroll_action_id = p_pactid;
412
413 cursor csr_fetch_agg(p_asg_id number)
414 is
415 select pai.ACTION_INFORMATION10
416 from pay_assignment_Actions paa, pay_action_information pai
417 where paa.payroll_action_id = p_pactid and paa.assignment_id = p_asg_id
418 and pai.action_context_id = paa.assignment_Action_id
419 and pai.ACTION_INFORMATION_CATEGORY = 'GB RTI EMPLOYEE DETAILS';
420
421 -- To fetch other assignments of the PAYE Aggregation
422 cursor fetch_other_paye_agg_asg(p_asg_id number)
423 is
424 select paaf.assignment_id
425 from per_all_assignments_f paaf,
426 pay_payrolls_f pay,
427 hr_soft_coding_keyflex sck
428 where paaf.person_id = (select distinct person_id from per_all_assignments_f where ASSIGNMENT_ID =p_asg_id)
429 and paaf.assignment_id <> p_asg_id
430 and pay.payroll_id = paaf.payroll_id
431 and pay.SOFT_CODING_KEYFLEX_ID = sck.SOFT_CODING_KEYFLEX_ID
432 and sck.SEGMENT1 = l_tax_ref
433 and paaf.EFFECTIVE_START_DATE =
434 ( select max(paaf1.effective_start_date)
435 from per_all_assignments_f paaf1
436 where paaf1.assignment_id = paaf.assignment_id
437 and paaf1.assignment_type = 'E'
438 and paaf1.effective_start_date <= l_effective_date
439 )
440 and l_effective_date between pay.EFFECTIVE_START_DATE and pay.EFFECTIVE_END_DATE;
441
442 BEGIN
443 hr_utility.set_location('Entering: '||l_proc,1);
444 hr_utility.set_location('p_pactid: '||p_pactid,1);
445 hr_utility.set_location('p_asgactid: '||p_asgactid,1);
446
447 open csr_fetch_details;
448 fetch csr_fetch_details into l_effective_date,l_tax_ref;
449 close csr_fetch_details;
450
451 FOR archive_rec IN csr_asgs_processed
452 LOOP
453 --STARTER Flag
454 l_ovn := get_version(archive_rec.assignment_id,'GB_RTI_ASG_DETAILS',l_aei_id);
455 IF l_ovn IS NOT NULL THEN
456 hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS', p_aei_information8 => 'N');
457 END IF;
458
459 open csr_fetch_agg(archive_rec.assignment_id);
460 fetch csr_fetch_agg into l_paye_agg;
461 close csr_fetch_agg;
462
463 if l_paye_agg = 'Y' then
464 open fetch_other_paye_agg_asg(archive_rec.assignment_id);
465 loop
466 fetch fetch_other_paye_agg_asg into l_assignment_id;
467 exit when fetch_other_paye_agg_asg%notfound;
468 --STARTER Flag
469 l_ovn := get_version(l_assignment_id,'GB_RTI_ASG_DETAILS',l_aei_id);
470 IF l_ovn IS NOT NULL THEN
471 hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS', p_aei_information8 => 'N');
472 END IF;
473 end loop;
474 close fetch_other_paye_agg_asg;
475 end if;
476
477
478 END LOOP;
479
480
481 hr_utility.set_location('Leaving eas_restored: '||p_pactid,1);
482 END eas_restored;
483 ---
484 ---
485
486 PROCEDURE eas13_restored(
487 p_pactid IN NUMBER,
488 p_asgactid IN NUMBER default null)
489 IS
490 l_proc CONSTANT VARCHAR2(50):= 'eas13_restored';
491 l_ovn NUMBER;
492 l_aei_id NUMBER;
493 l_assignment_id NUMBER;
494 l_prev_eas varchar2(1);
495
496
497 CURSOR csr_asgs_processed
498 IS
499 SELECT DISTINCT paa.assignment_id
500 FROM pay_assignment_actions paa,
501 pay_payroll_actions ppa
502 WHERE paa.payroll_action_id = ppa.payroll_action_id
503 AND ppa.payroll_action_id = p_pactid; --1688760
504
505 cursor is_prev_flag(p_asg_id number)
506 is
507 select 'Y' from dual where exists(select *
508 from pay_payroll_actions prev,
509 pay_assignment_actions paa
510 where paa.assignment_id = p_asg_id
511 and paa.payroll_action_id = prev.payroll_action_id
512 and prev.payroll_action_id <> p_pactid
513 and prev.ACTION_STATUS='C'
514 and prev.REPORT_TYPE = 'RTI_EAS_REP');
515
516
517 BEGIN
518 hr_utility.set_location('Entering: '||l_proc,1);
519 hr_utility.set_location('p_pactid: '||p_pactid,1);
520 hr_utility.set_location('p_asgactid: '||p_asgactid,1);
521
522
523
524 FOR archive_rec IN csr_asgs_processed
525 LOOP
526 --STARTER Flag
527 l_ovn := get_version(archive_rec.assignment_id,'GB_RTI_ASG_DETAILS',l_aei_id);
528 open is_prev_flag(archive_rec.assignment_id);
529 fetch is_prev_flag into l_prev_eas;
530 close is_prev_flag;
531 IF (l_ovn IS NOT NULL and l_prev_eas is null)THEN
532 hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS', p_aei_information8 => 'N');
533 END IF;
534 END LOOP;
535 hr_utility.set_location('Leaving eas_restored: '||p_pactid,1);
536 END eas13_restored;
537 ---
538 ---
539
540 PROCEDURE edi_restored(p_type in varchar2,
541 p_pactid in number)
542 IS
543 l_proc CONSTANT VARCHAR2(50):= 'edi_restored';
544 l_ovn number;
545 l_aei_id number;
546
547 l_assignment_id NUMBER;
548
549 cursor csr_archive_details is
550 select paa.assignment_id,
551 pai.action_information1,
552 pai.action_information2,
553 pai.action_information3,
554 pai.action_information4,
555 pai.action_information5,
556 pai.action_information6,
557 pai.action_information7,
558 pai.action_information8,
559 pai.action_information9,
560 pai.action_information10,
561 pai.action_information11,
562 pai.action_information12
563 from pay_assignment_actions paa,
564 pay_action_information pai
565 where paa.payroll_action_id = p_pactid
566 and paa.assignment_action_id = pai.action_context_id
567 and pai.action_information_category = p_type
568 and pai.action_context_type = 'AAP';
569
570 CURSOR csr_extra_details(l_assignment_id IN NUMBER)
571 IS
572 SELECT pei.aei_information_category,
573 pei.aei_information1,
574 pei.aei_information2,
575 pei.aei_information3,
576 pei.aei_information4,
577 pei.aei_information5,
578 pei.aei_information6
579 FROM per_assignment_extra_info pei
580 WHERE pei.assignment_id = l_assignment_id
581 AND pei.information_type = 'GB_P46';
582
583 BEGIN
584 hr_utility.set_location('Entering: '||l_proc,1);
585 if p_type = 'GB WNU EDI' then
586 hr_utility.set_location(p_type,10);
587 for archive_rec in csr_archive_details loop
588 hr_utility.set_location(p_type,20);
589 l_ovn := get_version(archive_rec.assignment_id,'GB_WNU',l_aei_id);
590 if l_ovn is not null and not(l_ovn <> archive_rec.action_information1) then
591 hr_utility.set_location(p_type,30);
592 hr_assignment_extra_info_api.update_assignment_extra_info
593 (p_validate => false,
594 p_object_version_number => l_ovn,
595 p_assignment_extra_info_id => l_aei_id,
596 p_aei_information_category => 'GB_WNU',
597 p_aei_information1 => archive_rec.action_information2,
598 p_aei_information2 => archive_rec.action_information3,
599 p_aei_information3 => archive_rec.action_information4);
600 end if;
601 end loop;
602 end if;
603
604 if p_type = 'GB P45(3) EDI' then
605 hr_utility.set_location(p_type,10);
606 for archive_rec in csr_archive_details loop
607 hr_utility.set_location(p_type,20);
608 l_ovn := get_version(archive_rec.assignment_id,'GB_P45_3',l_aei_id);
609 if l_ovn is not null and not(l_ovn <> archive_rec.action_information1) then
610 hr_utility.set_location(p_type,30);
611 hr_assignment_extra_info_api.update_assignment_extra_info
612 (p_validate => false,
613 p_object_version_number => l_ovn,
614 p_assignment_extra_info_id => l_aei_id,
615 p_aei_information_category => 'GB_P45_3',
616 p_aei_information1 => 'Y');
617 end if;
618 end loop;
619 end if;
620 /* EOY Changes P45(3) Version 6 Starts */
621 if p_type = 'GB P45(3) EDI' then
622 hr_utility.set_location(p_type,10);
623 for archive_rec in csr_archive_details loop
624 hr_utility.set_location(p_type,20);
625 l_ovn := get_version(archive_rec.assignment_id,'GB_P45_3',l_aei_id);
626 if l_ovn is not null and not(l_ovn <> archive_rec.action_information1) then
627 hr_utility.set_location(p_type,30);
628 hr_assignment_extra_info_api.update_assignment_extra_info
629 (p_validate => false,
630 p_object_version_number => l_ovn,
631 p_assignment_extra_info_id => l_aei_id,
632 p_aei_information_category => 'GB_P45_3',
633 p_aei_information1 => 'Y');
634 end if;
635 end loop;
636 end if;
637 /* EOY Changes P45(3) Version 6 Ends */
638
639 if p_type = 'GB P46 EDI' then
640 hr_utility.set_location(p_type,10);
641 for archive_rec in csr_archive_details loop
642 hr_utility.set_location(p_type,20);
643 l_ovn := get_version(archive_rec.assignment_id,'GB_P46',l_aei_id);
644 if l_ovn is not null and not(l_ovn <> archive_rec.action_information1) then
645 hr_utility.set_location(p_type,30);
646 hr_assignment_extra_info_api.update_assignment_extra_info
647 (p_validate => false,
648 p_object_version_number => l_ovn,
649 p_assignment_extra_info_id => l_aei_id,
650 p_aei_information_category => 'GB_P46',
651 p_aei_information1 => 'Y');
652 end if;
653 end loop;
654 end if;
655
656 /*Changes for P46EXP_Ver6 starts*/
657
658 if p_type = 'GB P46EXP EDI' then
659 hr_utility.set_location(p_type,10);
660 for archive_rec in csr_archive_details loop
661 hr_utility.set_location(p_type,20);
662 l_ovn := get_version(archive_rec.assignment_id,'GB_P46EXP',l_aei_id);
663 if l_ovn is not null and not(l_ovn <> archive_rec.action_information1) then
664 hr_utility.set_location(p_type,30);
665 hr_assignment_extra_info_api.update_assignment_extra_info
666 (p_validate => false,
667 p_object_version_number => l_ovn,
668 p_assignment_extra_info_id => l_aei_id,
669 p_aei_information_category => 'GB_P46EXP',
670 p_aei_information1 => 'Y');
671 end if;
672 end loop;
673 end if;
674
675 /*Changes for P46EXP_Ver6 End*/
676
677 IF p_type = 'GB P46_5 EDI'
678 THEN
679 hr_utility.set_location(p_type,10);
680 FOR archive_rec IN csr_archive_details
681 LOOP
682 FOR asg_extra IN csr_extra_details(archive_rec.assignment_id)
683 LOOP
684 l_ovn := get_version(archive_rec.assignment_id,'GB_P46',l_aei_id);
685
686 IF archive_rec.action_information4 = 'Y' --This is a default Rollback
687 THEN
688 IF (asg_extra.aei_information5 = 'N'
689 AND asg_extra.aei_information6 = 'Y')
690 THEN
691 IF (asg_extra.aei_information1 IS NULL
692 OR (asg_extra.aei_information1 = 'N'
693 AND asg_extra.aei_information3 <> 'Y'))
694 THEN
695 --This is just a default rollback
696 hr_assignment_extra_info_api.update_assignment_extra_info
697 (p_validate => false,
698 p_object_version_number => l_ovn,
699 p_assignment_extra_info_id => l_aei_id,
700 p_aei_information_category => 'GB_P46',
701 p_aei_information5 => 'Y');
702
703 ELSIF asg_extra.aei_information1 = 'Y'
704 THEN
705 --Remove the Send EDI flag also along with default rollback
706 hr_assignment_extra_info_api.update_assignment_extra_info
707 (p_validate => false,
708 p_object_version_number => l_ovn,
709 p_assignment_extra_info_id => l_aei_id,
710 p_aei_information_category => 'GB_P46',
711 p_aei_information5 => 'Y',
712 p_aei_information1 => NULL,
713 p_aei_information3 => 'N');
714 ELSE
715 fnd_file.put_line(fnd_file.log,'P46 Run is there and it needs to be rollbacked first '
716 ||'for assignment_id : '||archive_rec.assignment_id);
717 END IF;
718 END IF;
719
720 ELSE --This is a send EDI Rollback
721 IF (asg_extra.aei_information1 = 'N'
722 AND asg_extra.aei_information3 = 'Y')
723 THEN
724 hr_assignment_extra_info_api.update_assignment_extra_info
725 (p_validate => false,
726 p_object_version_number => l_ovn,
727 p_assignment_extra_info_id => l_aei_id,
728 p_aei_information_category => 'GB_P46',
729 p_aei_information1 => 'Y');
730 END IF;
731 END IF;
732 END LOOP;
733 END LOOP;
734 END IF;
735
736 if p_type = 'GB P46 PENNOT EDI' then ---GB P46 Pension EDI
737 hr_utility.set_location(p_type,10);
738 for archive_rec in csr_archive_details loop
739 hr_utility.set_location(p_type,20);
740 l_ovn := get_version(archive_rec.assignment_id,'GB_P46PENNOT',l_aei_id);
741 if l_ovn is not null and not(l_ovn <> archive_rec.action_information1) then
742 hr_utility.set_location(p_type,30);
743 hr_assignment_extra_info_api.update_assignment_extra_info
744 (p_validate => false,
745 p_object_version_number => l_ovn,
746 p_assignment_extra_info_id => l_aei_id,
747 p_aei_information_category => 'GB_P46PENNOT',
748 p_aei_information1 => 'Y');
749 end if;
750 end loop;
751 end if;
752 hr_utility.set_location('Leaving: '||l_proc,999);
753 END;
754 ---
755
756 PROCEDURE gb_rollback(
757 errbuf OUT NOCOPY VARCHAR2,
758 retcode OUT NOCOPY NUMBER,
759 p_year IN NUMBER,
760 p_type IN VARCHAR2,
761 p_actid IN NUMBER,
762 p_report_type IN VARCHAR2,
763 p_asgset in varchar2)
764 IS
765 l_proc CONSTANT VARCHAR2(50):= 'gb_rollback';
766 l_id NUMBER;
767 l_report_type VARCHAR2(50);
768 BEGIN
769 --hr_utility.trace_on(null, 'hyd_tra');
770 hr_utility.set_location('Entering: '||l_proc,1);
771 hr_utility.set_location('Year : ' || p_year, 10);
772 hr_utility.set_location('p_actid : ' || p_actid, 10);
773
774 l_report_type := get_report_type(p_actid);
775
776 hr_utility.set_location('l_report_type: '||l_report_type,10);
777
778 if l_report_type = 'GBEDIWNU' or l_report_type = 'GBEDIWNU3' or l_report_type = 'GBEDIWNU3ET' then -- 5398360 --Added GBEDIWNU3ET for bug 9186359
779 edi_restored('GB WNU EDI',p_actid);
780 end if;
781 if l_report_type = 'P45_3_EDI' then
782 edi_restored('GB P45(3) EDI',p_actid);
783 end if;
784 /* EOY Changes P45(3) Version 6 Starts */
785 if l_report_type = 'P45PT_3_VER6' then
786 edi_restored('GB P45(3) EDI',p_actid);
787 end if;
788 /* EOY Changes P45(3) Version 6 Ends */
789
790 if l_report_type = 'GB_P46' then
791 edi_restored('GB P46 EDI',p_actid);
792 end if;
793 /*Changes for P46EXP_Ver6 starts*/
794 if l_report_type in ('GB_P46EXP_V6','GB_P46EXP_V6ET') then --Added GB_P46EXP_V6ET for bug 9255183
795 edi_restored('GB P46EXP EDI',p_actid);
796 end if;
797 /*Changes for P46EXP_Ver6 End*/
798
799 IF l_report_type in('GB_P46_5' ,'GB_P46_V6','GB_P46_V6ET') THEN --Added GB_P46_V6ET for bug 9255173
800 edi_restored('GB P46_5 EDI',p_actid);
801 END IF;
802
803 if l_report_type in ('P46_PENNOT_EDI','P46_5_PENNOT_EDI','P46_VER6_PENNOT') then
804 edi_restored('GB P46 PENNOT EDI',p_actid);
805 end if;
806
807 if l_report_type = 'RTI_FPS_REP'
808 then
809 restored(p_actid);
810 end if;
811
812 if l_report_type = 'RTI_FPS_REP_13'
813 then
814 restored(p_actid);
815 end if;
816
817 if l_report_type = 'RTI_EAS_REP'
818 then
819 eas_restored(p_actid);
820 end if;
821
822 /* EAS 2013 Changes*/
823 /* Added RTI_EAS_REP_13 for new EAS program */
824 if l_report_type = 'RTI_EAS_REP_13'
825 then
826 eas13_restored(p_actid);
827 end if;
828
829 if l_report_type = 'RTI_EYU_REP_13'
830 then
831 eyu_restored(p_actid);
832 end if;
833
834 -- Next called the Core's ROLLBACK routine
835 l_id := fnd_request.submit_request(application => 'PAY',
836 program => 'ROLLBACK',
837 argument1 => 'ROLLBACK',
838 argument2 => NULL,
839 argument3 => NULL,
840 argument4 => p_year,
841 argument5 => 'X', -- magnetic report
842 argument6 => p_actid, -- payroll action_id
843 argument7 => p_asgset, -- assignmenet_set
844 argument8 => 'PAYROLL_ACTION_ID='||p_actid,
845 argument9 => 'ASG_SET_ID='||p_asgset);
846
847 hr_utility.set_location('Leaving: '||l_proc,999);
848 END gb_rollback;
849
850 END PAY_GB_ROLLBACK_PKG;