[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_EDI_ROLLBACK
Source
1 PACKAGE BODY PAY_GB_EDI_ROLLBACK as
2 /* $Header: pygbedir.pkb 120.2.12010000.10 2010/01/22 15:11:15 krreddy ship $ */
3 --
4 /*===========================================================================+
5 | Copyright (c) 1993 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +============================================================================
9 Name :PAY_GB_EDI_ROLLBACK
10 Purpose :Package to contol rollback process for
11 P45, P45(3), P46, P46 Car, P46PENNOT, WNU and P11D
12
13 History
14 Date Name Version Bug Description
15 ----------- ------------- -------- ---------- ------------------------------
16 15-JUN-2006 K.Thampan 115.0 Created.
17 28-JUN-2006 K.Thampan 115.1 Fix GSCC error
18 09-AUG-2006 tukumar 115.2 WNU 3.0 5398360
19 11-Aug-2008 namgoyal 115.3 7208046 Added logic to handle EDI Rollback
20 for P46 EDI process version 5
24 P46Expat Notification
21 16-OCT-2008 dwkrishn 115.4 7433580 Added code for 2009 legislative changes
22 28-NOV-2008 dwkrishn 115.4 7433580 Added Pennot Changes
23 11-Feb-2009 krreddy 115.7 8216080 Modified the code to implement
25 11-Feb-2009 krreddy 115.8 8216080 Modified the code to implement
26 P46Expat Notification
27 05-Jan-2010 namgoyal 115.9 9186359 Added rollback for eText based WNU3.0.
28 This code would only be called for
29 release 12.1.3.
30 21-Jan-2010 namgoyal 115.10 9255173 Added rollback for eText based P46 V6
31 This code would only be called for
32 release 12.1.3.
33 21-Jan-2010 krreddy 115.10 9255183 Added rollback for eText based P46EXP V6
34 This code would only be called for
35 release 12.1.3.
36 ============================================================================*/
37
38 g_package CONSTANT VARCHAR2(20):= 'pay_gb_edi_rollback.';
39 --- PRIVATE PROCEDURE ---
40 FUNCTION get_version(p_assig_id in number,
41 p_type in varchar2,
42 p_aei_id out nocopy number) return number
43 IS
44 l_proc CONSTANT VARCHAR2(50):= g_package||'get_version';
45 l_ovn number;
46 cursor csr_ovn is
47 select object_version_number,
48 assignment_extra_info_id
49 from per_assignment_extra_info
50 where assignment_id = p_assig_id
51 and information_type = p_type;
52 BEGIN
53 hr_utility.set_location('Entering: '||l_proc,1);
54 open csr_ovn;
55 fetch csr_ovn into l_ovn, p_aei_id;
56 close csr_ovn;
57
58 return l_ovn;
59 hr_utility.set_location('Leaving: '||l_proc,999);
60 END;
61
62 PROCEDURE restored(p_type in varchar2,
63 p_pactid in number)
64 IS
65 l_proc CONSTANT VARCHAR2(50):= g_package||'restored';
66 l_ovn number;
67 l_aei_id number;
68
69 --For bug 7208046: Start
70 l_assignment_id NUMBER;
71 --For bug 7208046: End
72
73 cursor csr_archive_details is
74 select paa.assignment_id,
75 pai.action_information1,
76 pai.action_information2,
77 pai.action_information3,
78 pai.action_information4,
79 pai.action_information5,
80 pai.action_information6,
81 pai.action_information7,
82 pai.action_information8,
83 pai.action_information9,
84 pai.action_information10,
85 pai.action_information11,
86 pai.action_information12
87 from pay_assignment_actions paa,
88 pay_action_information pai
89 where paa.payroll_action_id = p_pactid
90 and paa.assignment_action_id = pai.action_context_id
91 and pai.action_information_category = p_type
92 and pai.action_context_type = 'AAP';
93
94 --For bug 7208046: Start
95 CURSOR csr_extra_details(l_assignment_id IN NUMBER)
96 IS
97 SELECT pei.aei_information_category,
98 pei.aei_information1,
99 pei.aei_information2,
100 pei.aei_information3,
101 pei.aei_information4,
102 pei.aei_information5,
103 pei.aei_information6
104 FROM per_assignment_extra_info pei
105 WHERE pei.assignment_id = l_assignment_id
106 AND pei.information_type = 'GB_P46';
107 --For bug 7208046: End
108
109 BEGIN
110 hr_utility.set_location('Entering: '||l_proc,1);
111 if p_type = 'GB WNU EDI' then
112 hr_utility.set_location(p_type,10);
113 for archive_rec in csr_archive_details loop
114 hr_utility.set_location(p_type,20);
115 l_ovn := get_version(archive_rec.assignment_id,'GB_WNU',l_aei_id);
116 if l_ovn is not null and not(l_ovn <> archive_rec.action_information1) then
117 hr_utility.set_location(p_type,30);
118 hr_assignment_extra_info_api.update_assignment_extra_info
119 (p_validate => false,
120 p_object_version_number => l_ovn,
121 p_assignment_extra_info_id => l_aei_id,
122 p_aei_information_category => 'GB_WNU',
123 p_aei_information1 => archive_rec.action_information2,
124 p_aei_information2 => archive_rec.action_information3,
125 p_aei_information3 => archive_rec.action_information4);
126 end if;
127 end loop;
128 end if;
129
130 if p_type = 'GB P45(3) EDI' then
131 hr_utility.set_location(p_type,10);
132 for archive_rec in csr_archive_details loop
133 hr_utility.set_location(p_type,20);
134 l_ovn := get_version(archive_rec.assignment_id,'GB_P45_3',l_aei_id);
135 if l_ovn is not null and not(l_ovn <> archive_rec.action_information1) then
136 hr_utility.set_location(p_type,30);
137 hr_assignment_extra_info_api.update_assignment_extra_info
138 (p_validate => false,
139 p_object_version_number => l_ovn,
140 p_assignment_extra_info_id => l_aei_id,
141 p_aei_information_category => 'GB_P45_3',
142 p_aei_information1 => 'Y');
143 end if;
144 end loop;
145 end if;
146 /* EOY Changes P45(3) Version 6 Starts */
150 hr_utility.set_location(p_type,20);
147 if p_type = 'GB P45(3) EDI' then
148 hr_utility.set_location(p_type,10);
149 for archive_rec in csr_archive_details loop
151 l_ovn := get_version(archive_rec.assignment_id,'GB_P45_3',l_aei_id);
152 if l_ovn is not null and not(l_ovn <> archive_rec.action_information1) then
153 hr_utility.set_location(p_type,30);
154 hr_assignment_extra_info_api.update_assignment_extra_info
155 (p_validate => false,
156 p_object_version_number => l_ovn,
157 p_assignment_extra_info_id => l_aei_id,
158 p_aei_information_category => 'GB_P45_3',
159 p_aei_information1 => 'Y');
160 end if;
161 end loop;
162 end if;
163 /* EOY Changes P45(3) Version 6 Ends */
164
165 if p_type = 'GB P46 EDI' then
166 hr_utility.set_location(p_type,10);
167 for archive_rec in csr_archive_details loop
168 hr_utility.set_location(p_type,20);
169 l_ovn := get_version(archive_rec.assignment_id,'GB_P46',l_aei_id);
170 if l_ovn is not null and not(l_ovn <> archive_rec.action_information1) then
171 hr_utility.set_location(p_type,30);
172 hr_assignment_extra_info_api.update_assignment_extra_info
173 (p_validate => false,
174 p_object_version_number => l_ovn,
175 p_assignment_extra_info_id => l_aei_id,
176 p_aei_information_category => 'GB_P46',
177 p_aei_information1 => 'Y');
178 end if;
179 end loop;
180 end if;
181
182 /*Changes for P46EXP_Ver6 starts*/
183
184 if p_type = 'GB P46EXP EDI' then
185 hr_utility.set_location(p_type,10);
186 for archive_rec in csr_archive_details loop
187 hr_utility.set_location(p_type,20);
188 l_ovn := get_version(archive_rec.assignment_id,'GB_P46EXP',l_aei_id);
189 if l_ovn is not null and not(l_ovn <> archive_rec.action_information1) then
190 hr_utility.set_location(p_type,30);
191 hr_assignment_extra_info_api.update_assignment_extra_info
192 (p_validate => false,
193 p_object_version_number => l_ovn,
194 p_assignment_extra_info_id => l_aei_id,
195 p_aei_information_category => 'GB_P46EXP',
196 p_aei_information1 => 'Y');
197 end if;
198 end loop;
199 end if;
200
201 /*Changes for P46EXP_Ver6 End*/
202
203 --For bug 7208046: Start
204 IF p_type = 'GB P46_5 EDI'
205 THEN
206 hr_utility.set_location(p_type,10);
207 FOR archive_rec IN csr_archive_details
208 LOOP
209 FOR asg_extra IN csr_extra_details(archive_rec.assignment_id)
210 LOOP
211 l_ovn := get_version(archive_rec.assignment_id,'GB_P46',l_aei_id);
212
213 IF archive_rec.action_information4 = 'Y' --This is a default Rollback
214 THEN
215 IF (asg_extra.aei_information5 = 'N'
216 AND asg_extra.aei_information6 = 'Y')
217 THEN
218 IF (asg_extra.aei_information1 IS NULL
219 OR (asg_extra.aei_information1 = 'N'
220 AND asg_extra.aei_information3 <> 'Y'))
221 THEN
222 --This is just a default rollback
223 hr_assignment_extra_info_api.update_assignment_extra_info
224 (p_validate => false,
225 p_object_version_number => l_ovn,
226 p_assignment_extra_info_id => l_aei_id,
227 p_aei_information_category => 'GB_P46',
228 p_aei_information5 => 'Y');
229
230 ELSIF asg_extra.aei_information1 = 'Y'
231 THEN
232 --Remove the Send EDI flag also along with default rollback
233 hr_assignment_extra_info_api.update_assignment_extra_info
234 (p_validate => false,
235 p_object_version_number => l_ovn,
236 p_assignment_extra_info_id => l_aei_id,
237 p_aei_information_category => 'GB_P46',
238 p_aei_information5 => 'Y',
239 p_aei_information1 => NULL,
240 p_aei_information3 => 'N');
241 ELSE
242 fnd_file.put_line(fnd_file.log,'P46 Run is there and it needs to be rollbacked first '
243 ||'for assignment_id : '||archive_rec.assignment_id);
244 END IF;
245 END IF;
246
247 ELSE --This is a send EDI Rollback
248 IF (asg_extra.aei_information1 = 'N'
249 AND asg_extra.aei_information3 = 'Y')
250 THEN
251 hr_assignment_extra_info_api.update_assignment_extra_info
252 (p_validate => false,
253 p_object_version_number => l_ovn,
254 p_assignment_extra_info_id => l_aei_id,
255 p_aei_information_category => 'GB_P46',
256 p_aei_information1 => 'Y');
257 END IF;
258 END IF;
259 END LOOP;
260 END LOOP;
261 END IF;
262 --For bug 7208046: End
263
264 if p_type = 'GB P46 PENNOT EDI' then ---GB P46 Pension EDI
265 hr_utility.set_location(p_type,10);
266 for archive_rec in csr_archive_details loop
267 hr_utility.set_location(p_type,20);
268 l_ovn := get_version(archive_rec.assignment_id,'GB_P46PENNOT',l_aei_id);
269 if l_ovn is not null and not(l_ovn <> archive_rec.action_information1) then
270 hr_utility.set_location(p_type,30);
271 hr_assignment_extra_info_api.update_assignment_extra_info
272 (p_validate => false,
273 p_object_version_number => l_ovn,
274 p_assignment_extra_info_id => l_aei_id,
275 p_aei_information_category => 'GB_P46PENNOT',
276 p_aei_information1 => 'Y');
277 end if;
278 end loop;
279 end if;
280 hr_utility.set_location('Leaving: '||l_proc,999);
281 END;
282 --- PUBLIC PROCEDURE ---
283 PROCEDURE edi_rollback(errbuf out NOCOPY VARCHAR2,
284 retcode out NOCOPY NUMBER,
285 p_type in varchar2,
286 p_year in number,
287 p_actid in number)
288 IS
289 l_proc CONSTANT VARCHAR2(50):= g_package||'edi_rollback';
290 l_id number;
291 BEGIN
292 hr_utility.set_location('Entering: '||l_proc,1);
293 hr_utility.set_location('Type : ' || p_type, 10);
294 hr_utility.set_location('Year : ' || p_year, 10);
295 hr_utility.set_location('Action : ' || p_actid, 10);
296 -- If type is on of the following, do manual stuff first
297 if p_type = 'GBEDIWNU' or p_type = 'GBEDIWNU3' or p_type = 'GBEDIWNU3ET' then -- 5398360 --Added GBEDIWNU3ET for bug 9186359
298 restored('GB WNU EDI',p_actid);
299 end if;
300 if p_type = 'P45_3_EDI' then
301 restored('GB P45(3) EDI',p_actid);
302 end if;
303 /* EOY Changes P45(3) Version 6 Starts */
304 if p_type = 'P45PT_3_VER6' then
305 restored('GB P45(3) EDI',p_actid);
306 end if;
307 /* EOY Changes P45(3) Version 6 Ends */
308
309 if p_type = 'GB_P46' then
310 restored('GB P46 EDI',p_actid);
311 end if;
312 /*Changes for P46EXP_Ver6 starts*/
313 if p_type in ('GB_P46EXP_V6','GB_P46EXP_V6ET') then --Added GB_P46EXP_V6ET for bug 9255183
314 restored('GB P46EXP EDI',p_actid);
315 end if;
316 /*Changes for P46EXP_Ver6 End*/
317
318 --For bug 7208046: Start
319 --IF p_type = 'GB_P46_5'
320 IF p_type in('GB_P46_5' ,'GB_P46_V6','GB_P46_V6ET') THEN --Added GB_P46_V6ET for bug 9255173
321 restored('GB P46_5 EDI',p_actid);
322 END IF;
323 --For bug 7208046: End
324
325 /*if p_type = 'P46_PENNOT_EDI' then
326 restored('GB P46 Pension EDI',p_actid);
327 end if;*/
328
329 if p_type in ('P46_PENNOT_EDI','P46_5_PENNOT_EDI','P46_VER6_PENNOT') then
330 restored('GB P46 PENNOT EDI',p_actid);
331 end if;
332
333 hr_utility.set_location('Calling Core Rollback routine',20);
334 -- Next called the Core's ROLLBACK routine
335 l_id := fnd_request.submit_request(application => 'PAY',
336 program => 'ROLLBACK',
337 argument1 => 'ROLLBACK',
338 argument2 => null,
339 argument3 => null,
340 argument4 => p_year,
341 argument5 => 'X', -- magnetic report
342 argument6 => p_actid, -- payroll action_id
343 argument7 => null, -- assignmenet_set
344 argument8 => 'PAYROLL_ACTION_ID='||p_actid,
345 argument9 => null);
346 hr_utility.set_location('Leaving: '||l_proc,999);
347 END edi_rollback;
348
349 END PAY_GB_EDI_ROLLBACK;