[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.2 2008/08/11 13:45:12 namgoyal 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
21 ============================================================================*/
22
23 g_package CONSTANT VARCHAR2(20):= 'pay_gb_edi_rollback.';
24 --- PRIVATE PROCEDURE ---
25 FUNCTION get_version(p_assig_id in number,
26 p_type in varchar2,
27 p_aei_id out nocopy number) return number
28 IS
29 l_proc CONSTANT VARCHAR2(50):= g_package||'get_version';
30 l_ovn number;
31 cursor csr_ovn is
32 select object_version_number,
33 assignment_extra_info_id
34 from per_assignment_extra_info
35 where assignment_id = p_assig_id
36 and information_type = p_type;
37 BEGIN
38 hr_utility.set_location('Entering: '||l_proc,1);
39 open csr_ovn;
40 fetch csr_ovn into l_ovn, p_aei_id;
41 close csr_ovn;
42
43 return l_ovn;
44 hr_utility.set_location('Leaving: '||l_proc,999);
45 END;
46
47 PROCEDURE restored(p_type in varchar2,
48 p_pactid in number)
49 IS
50 l_proc CONSTANT VARCHAR2(50):= g_package||'restored';
51 l_ovn number;
52 l_aei_id number;
53
54 --For bug 7208046: Start
55 l_assignment_id NUMBER;
56 --For bug 7208046: End
57
58 cursor csr_archive_details is
59 select paa.assignment_id,
60 pai.action_information1,
61 pai.action_information2,
62 pai.action_information3,
63 pai.action_information4,
64 pai.action_information5,
65 pai.action_information6,
66 pai.action_information7,
67 pai.action_information8,
68 pai.action_information9,
69 pai.action_information10,
70 pai.action_information11,
71 pai.action_information12
72 from pay_assignment_actions paa,
73 pay_action_information pai
74 where paa.payroll_action_id = p_pactid
75 and paa.assignment_action_id = pai.action_context_id
76 and pai.action_information_category = p_type
77 and pai.action_context_type = 'AAP';
78
79 --For bug 7208046: Start
80 CURSOR csr_extra_details(l_assignment_id IN NUMBER)
81 IS
82 SELECT pei.aei_information_category,
83 pei.aei_information1,
84 pei.aei_information2,
85 pei.aei_information3,
86 pei.aei_information4,
87 pei.aei_information5,
88 pei.aei_information6
89 FROM per_assignment_extra_info pei
90 WHERE pei.assignment_id = l_assignment_id
91 AND pei.information_type = 'GB_P46';
92 --For bug 7208046: End
93
94 BEGIN
95 hr_utility.set_location('Entering: '||l_proc,1);
96 if p_type = 'GB WNU EDI' then
97 hr_utility.set_location(p_type,10);
98 for archive_rec in csr_archive_details loop
99 hr_utility.set_location(p_type,20);
100 l_ovn := get_version(archive_rec.assignment_id,'GB_WNU',l_aei_id);
101 if l_ovn is not null and not(l_ovn <> archive_rec.action_information1) then
102 hr_utility.set_location(p_type,30);
103 hr_assignment_extra_info_api.update_assignment_extra_info
104 (p_validate => false,
105 p_object_version_number => l_ovn,
106 p_assignment_extra_info_id => l_aei_id,
107 p_aei_information_category => 'GB_WNU',
108 p_aei_information1 => archive_rec.action_information2,
109 p_aei_information2 => archive_rec.action_information3,
110 p_aei_information3 => archive_rec.action_information4);
111 end if;
112 end loop;
113 end if;
114
115 if p_type = 'GB P45(3) EDI' then
116 hr_utility.set_location(p_type,10);
117 for archive_rec in csr_archive_details loop
118 hr_utility.set_location(p_type,20);
119 l_ovn := get_version(archive_rec.assignment_id,'GB_P45_3',l_aei_id);
120 if l_ovn is not null and not(l_ovn <> archive_rec.action_information1) then
121 hr_utility.set_location(p_type,30);
122 hr_assignment_extra_info_api.update_assignment_extra_info
123 (p_validate => false,
124 p_object_version_number => l_ovn,
125 p_assignment_extra_info_id => l_aei_id,
126 p_aei_information_category => 'GB_P45_3',
127 p_aei_information1 => 'Y');
128 end if;
129 end loop;
130 end if;
131
132 if p_type = 'GB P46 EDI' then
133 hr_utility.set_location(p_type,10);
134 for archive_rec in csr_archive_details loop
135 hr_utility.set_location(p_type,20);
136 l_ovn := get_version(archive_rec.assignment_id,'GB_P46',l_aei_id);
137 if l_ovn is not null and not(l_ovn <> archive_rec.action_information1) then
138 hr_utility.set_location(p_type,30);
139 hr_assignment_extra_info_api.update_assignment_extra_info
140 (p_validate => false,
141 p_object_version_number => l_ovn,
142 p_assignment_extra_info_id => l_aei_id,
143 p_aei_information_category => 'GB_P46',
144 p_aei_information1 => 'Y');
145 end if;
146 end loop;
147 end if;
148
149 --For bug 7208046: Start
150 IF p_type = 'GB P46_5 EDI'
151 THEN
152 hr_utility.set_location(p_type,10);
153 FOR archive_rec IN csr_archive_details
154 LOOP
155 FOR asg_extra IN csr_extra_details(archive_rec.assignment_id)
156 LOOP
157 l_ovn := get_version(archive_rec.assignment_id,'GB_P46',l_aei_id);
158
159 IF archive_rec.action_information4 = 'Y' --This is a default Rollback
160 THEN
161 IF (asg_extra.aei_information5 = 'N'
162 AND asg_extra.aei_information6 = 'Y')
163 THEN
164 IF (asg_extra.aei_information1 IS NULL
165 OR (asg_extra.aei_information1 = 'N'
166 AND asg_extra.aei_information3 <> 'Y'))
167 THEN
168 --This is just a default rollback
169 hr_assignment_extra_info_api.update_assignment_extra_info
170 (p_validate => false,
171 p_object_version_number => l_ovn,
172 p_assignment_extra_info_id => l_aei_id,
173 p_aei_information_category => 'GB_P46',
174 p_aei_information5 => 'Y');
175
176 ELSIF asg_extra.aei_information1 = 'Y'
177 THEN
178 --Remove the Send EDI flag also along with default rollback
179 hr_assignment_extra_info_api.update_assignment_extra_info
180 (p_validate => false,
181 p_object_version_number => l_ovn,
182 p_assignment_extra_info_id => l_aei_id,
183 p_aei_information_category => 'GB_P46',
184 p_aei_information5 => 'Y',
185 p_aei_information1 => NULL,
186 p_aei_information3 => 'N');
187 ELSE
188 fnd_file.put_line(fnd_file.log,'P46 Run is there and it needs to be rollbacked first '
189 ||'for assignment_id : '||archive_rec.assignment_id);
190 END IF;
191 END IF;
192
193 ELSE --This is a send EDI Rollback
194 IF (asg_extra.aei_information1 = 'N'
195 AND asg_extra.aei_information3 = 'Y')
196 THEN
197 hr_assignment_extra_info_api.update_assignment_extra_info
198 (p_validate => false,
199 p_object_version_number => l_ovn,
200 p_assignment_extra_info_id => l_aei_id,
201 p_aei_information_category => 'GB_P46',
202 p_aei_information1 => 'Y');
203 END IF;
204 END IF;
205 END LOOP;
206 END LOOP;
207 END IF;
208 --For bug 7208046: End
209
210 if p_type = 'GB P46 Pension EDI' then
211 hr_utility.set_location(p_type,10);
212 for archive_rec in csr_archive_details loop
213 hr_utility.set_location(p_type,20);
214 l_ovn := get_version(archive_rec.assignment_id,'GB_P46PENNOT',l_aei_id);
215 if l_ovn is not null and not(l_ovn <> archive_rec.action_information1) then
216 hr_utility.set_location(p_type,30);
217 hr_assignment_extra_info_api.update_assignment_extra_info
218 (p_validate => false,
219 p_object_version_number => l_ovn,
220 p_assignment_extra_info_id => l_aei_id,
221 p_aei_information_category => 'GB_P46PENNOT',
222 p_aei_information1 => 'Y');
223 end if;
224 end loop;
225 end if;
226 hr_utility.set_location('Leaving: '||l_proc,999);
227 END;
228 --- PUBLIC PROCEDURE ---
229 PROCEDURE edi_rollback(errbuf out NOCOPY VARCHAR2,
230 retcode out NOCOPY NUMBER,
231 p_type in varchar2,
232 p_year in number,
233 p_actid in number)
234 IS
235 l_proc CONSTANT VARCHAR2(50):= g_package||'edi_rollback';
236 l_id number;
237 BEGIN
238 hr_utility.set_location('Entering: '||l_proc,1);
239 hr_utility.set_location('Type : ' || p_type, 10);
240 hr_utility.set_location('Year : ' || p_year, 10);
241 hr_utility.set_location('Action : ' || p_actid, 10);
242 -- If type is on of the following, do manual stuff first
243 if p_type = 'GBEDIWNU' or p_type = 'GBEDIWNU3' then -- 5398360
244 restored('GB WNU EDI',p_actid);
245 end if;
246 if p_type = 'P45_3_EDI' then
247 restored('GB P45(3) EDI',p_actid);
248 end if;
249 if p_type = 'GB_P46' then
250 restored('GB P46 EDI',p_actid);
251 end if;
252
253 --For bug 7208046: Start
254 IF p_type = 'GB_P46_5'
255 THEN
256 restored('GB P46_5 EDI',p_actid);
257 END IF;
258 --For bug 7208046: End
259
260 if p_type = 'P46_PENNOT_EDI' then
261 restored('GB P46 Pension EDI',p_actid);
262 end if;
263
264 hr_utility.set_location('Calling Core Rollback routine',20);
265 -- Next called the Core's ROLLBACK routine
266 l_id := fnd_request.submit_request(application => 'PAY',
267 program => 'ROLLBACK',
268 argument1 => 'ROLLBACK',
269 argument2 => null,
270 argument3 => null,
271 argument4 => p_year,
272 argument5 => 'X', -- magnetic report
273 argument6 => p_actid, -- payroll action_id
274 argument7 => null, -- assignmenet_set
275 argument8 => 'PAYROLL_ACTION_ID='||p_actid,
276 argument9 => null);
277 hr_utility.set_location('Leaving: '||l_proc,999);
278 END edi_rollback;
279
280 END PAY_GB_EDI_ROLLBACK;