1 Package Body pay_gb_wnu_rules as
2 /* $Header: pygbwnu1.pkb 115.6 2003/12/18 00:27:09 asengar noship $ */
3 Procedure wnu_update
4 (p_assignment_id in number,
5 p_effective_date in date,
6 p_assignment_number in varchar2 default null,
7 p_assignment_number_old in varchar2 default null,
8 p_not_included_in_wnu in varchar2 default null,
9 p_object_version_number in out NOCOPY number,
10 p_assignment_extra_info_id out NOCOPY number
11 ) is
12 --
13 l_ass_extra_info_id number(9):= null;
14 l_ass_extra_info_id_out number(9);
15 l_legislation_code varchar2(2);
16 l_proc varchar2(72) := 'pay_gb_wnu_rules';
17 l_current_employee varchar2(30) := null;
18 l_assignment_id number(15):= null;
19 l_ass_number varchar2(30):= null;
20 l_ass_number_old varchar2(30):= null;
21 l_not_included_in_wnu varchar2(30);
22 l_ovn number(15):=null;
23 l_ovn_out number(15);
24 --
25 cursor csr_employee is
26 select upper(apf.current_employee_flag)
27 from per_all_people_f apf,
28 per_all_assignments_f aaf
29 where aaf.person_id = apf.person_id
30 and aaf.assignment_id = p_assignment_id
31 and p_effective_date between
32 apf.effective_start_date and apf.effective_end_date;
33 --
34 cursor csr_extra_info is
35 select aei.assignment_extra_info_id ,
36 object_version_number,
37 aei_information2
38 from per_assignment_extra_info aei
39 where aei.assignment_id = p_assignment_id
40 and information_type = 'GB_WNU';
41 --
42 cursor csr_bg is
43 select pbg.legislation_code
44 from per_business_groups pbg,
45 per_all_assignments_f aaf
46 where aaf.assignment_id = p_assignment_id
47 and aaf.business_group_id = pbg.business_group_id
48 and p_effective_date between
49 aaf.effective_start_date and aaf.effective_end_date;
50 --
51 begin
52 --
53 hr_utility.set_location('Entering:'|| l_proc, 10);
54 --
55 -- Assign Variables
56 --
57 l_assignment_id := p_assignment_id;
58 l_ass_number_old := p_assignment_number_old;
59 l_ass_number := p_assignment_number;
60 --
61 -- Only perform the upadate if the Assignment Numer
62 -- has been amended.
63
64 --if upper(l_ass_number) <> upper(l_ass_number_old) then
65
66 --
67 -- Will only update Currnt Employee Records
68 --
69 open csr_employee;
70 fetch csr_employee into l_current_employee ;
71 close csr_employee;
72 --
73 if l_current_employee = 'Y' then
74 --
75 -- Validation in addition to Row Handlers
76 -- Check that the specified business group is valid.
77 --
78 open csr_bg;
79 fetch csr_bg into l_legislation_code;
80 if csr_bg%notfound then
81 close csr_bg;
82 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
83 hr_utility.raise_error;
84 end if;
85 --
86 close csr_bg;
87 --
88 -- Check that the legislation of the specified business
89 -- group is 'GB'. Allow for the fact that the package
90 -- could be called via API or Forms.
91 --
92 if l_legislation_code = 'GB' then
93 --
94 --
95 -- check to see if assignment extra info
96 -- exists for this assignemnt_id
97 --
98 open csr_extra_info;
99 fetch csr_extra_info into l_ass_extra_info_id, l_ovn, l_not_included_in_wnu;
100
101 if csr_extra_info%notfound then
102 --
103 hr_utility.set_location(l_proc, 20);
104 --
105 -- Create an entry for WNU
106 --
107 if p_not_included_in_wnu is not null then
108 l_not_included_in_wnu := p_not_included_in_wnu;
109 else
110 l_not_included_in_wnu := 'N';
111 end if;
112 hr_assignment_extra_info_api.create_assignment_extra_info
113 (p_validate => false,
114 p_assignment_id => l_assignment_id,
115 p_information_type => 'GB_WNU',
116 p_aei_information_category => 'GB_WNU',
117 p_aei_information1 => l_ass_number_old,
118 p_aei_information2 => l_not_included_in_wnu,
119 p_object_version_number => l_ovn_out,
120 p_assignment_extra_info_id => l_ass_extra_info_id_out
121 );
122
123 p_object_version_number := l_ovn_out;
124 p_assignment_extra_info_id := l_ass_extra_info_id_out;
125 close csr_extra_info;
126
127 else
128 --
129 hr_utility.set_location(l_proc, 30);
130 --
131 -- Update Existing Entry for WNU
132 --
133 if p_not_included_in_wnu is not null then
134 l_not_included_in_wnu := p_not_included_in_wnu;
135 end if;
136 hr_assignment_extra_info_api.update_assignment_extra_info
137 (p_validate => false,
138 p_object_version_number => l_ovn,
142 p_aei_information2 => l_not_included_in_wnu
139 p_assignment_extra_info_id => l_ass_extra_info_id,
140 p_aei_information_category => 'GB_WNU',
141 p_aei_information1 => l_ass_number_old,
143 );
144 p_object_version_number := l_ovn;
145 --
146 close csr_extra_info;
147 --
148 end if ;
149 --
150 end if;
151 --
152 end if;
153 --
154 --end if;
155 --
156 hr_utility.set_location('Leaving:'|| l_proc, 100);
157 --
158 end;
159 --
160 -- BUG 3294480 Added this for the case when NI gets updated
161 Procedure wnu_update
162 (p_person_id in number,
163 p_effective_date in date,
164 p_aggregated_assignment in varchar2 default null,
165 p_ni_number_update in varchar2 default null,
166 p_not_included_in_wnu in varchar2 default null,
167 p_object_version_number in out NOCOPY number,
168 p_assignment_extra_info_id out NOCOPY number
169 ) is
170 --
171 l_ass_extra_info_id number(9):= null;
172 l_ass_extra_info_id_out number(9);
173 l_legislation_code varchar2(2);
174 l_proc varchar2(72) := 'pay_gb_wnu_rules';
175 l_current_employee varchar2(30) := null;
176 l_assignment_id number(15):= null;
177 l_ass_number varchar2(30):= null;
178 l_ass_number_old varchar2(30):= null;
179 l_not_included_in_wnu varchar2(30);
180 l_ovn number(15):=null;
181 l_ovn_out number(15);
182 l_ni_number_update varchar2(30):= null;
183 --
184 cursor csr_employee is
185 select upper(apf.current_employee_flag)
186 from per_all_people_f apf
187 where apf.person_id = p_person_id
188 and p_effective_date between
189 apf.effective_start_date and apf.effective_end_date;
190 --
191 cursor csr_extra_info(c_assignment_id NUMBER) is
192 select aei.assignment_extra_info_id ,
193 object_version_number,
194 aei_information1,
195 aei_information2,
196 aei_information3
197 from per_assignment_extra_info aei
198 where aei.assignment_id = c_assignment_id
199 and information_type = 'GB_WNU';
200 --
201 cursor csr_assignment is
202 select aaf.assignment_id assignment_id
203 from per_all_assignments_f aaf
204 where aaf.person_id = p_person_id
205 and p_effective_date between
206 aaf.effective_start_date and aaf.effective_end_date;
207 --
208 cursor csr_bg(c_assignment_id NUMBER) is
209 select pbg.legislation_code
210 from per_business_groups pbg,
211 per_all_assignments_f aaf
212 where aaf.assignment_id = c_assignment_id
213 and aaf.business_group_id = pbg.business_group_id
214 and p_effective_date between
215 aaf.effective_start_date and aaf.effective_end_date;
216 --
217 cursor csr_agg_assignment is
218 select min(aaf.assignment_id) assignment_id
219 from per_all_assignments_f aaf,
220 hr_soft_coding_keyflex hsck,
221 pay_all_payrolls_f papf,
222 per_assignment_status_types past
223 where aaf.person_id = p_person_id
224 AND p_effective_date between
225 aaf.effective_start_date and aaf.effective_end_date
226 AND hsck.soft_coding_keyflex_id = papf.soft_coding_keyflex_id
227 AND papf.payroll_id =aaf.payroll_id
228 AND past.assignment_status_type_id = aaf.assignment_status_type_id
229 AND aaf.person_id = p_person_id
230 AND past.per_system_status='ACTIVE_ASSIGN'
231 AND p_effective_date BETWEEN aaf.effective_start_date AND aaf.effective_end_date
232 AND p_effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
233 AND hsck.segment1 in ( SELECT distinct(hsck.segment1)
234 FROM hr_soft_coding_keyflex hsck2,
235 pay_all_payrolls_f papf2,
236 per_all_assignments_f paaf,
237 per_assignment_status_types past2
238 WHERE hsck2.soft_coding_keyflex_id = papf2.soft_coding_keyflex_id
239 AND papf2.payroll_id =paaf.payroll_id
240 AND past2.assignment_status_type_id = paaf.assignment_status_type_id
241 AND paaf.person_id = p_person_id
242 AND past2.per_system_status='ACTIVE_ASSIGN'
243 AND p_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
244 AND p_effective_date BETWEEN papf2.effective_start_date AND papf2.effective_end_date)
245 GROUP BY hsck.segment1;
246 --
247 begin
248 --
249 hr_utility.set_location('Entering:'|| l_proc, 10);
250
251 -- Will only update Currnt Employee Records
252 --
253 open csr_employee;
254 fetch csr_employee into l_current_employee ;
255 close csr_employee;
256 --
257 if l_current_employee = 'Y' then
258 --
259 -- Validation in addition to Row Handlers
260 -- Check that the specified business group is valid.
261 --
262 if p_aggregated_assignment = 'Y' then
263 -- This is for the case when there are aggregated assignments.
264 --
265 for asg_id in csr_agg_assignment loop
266 --
267 open csr_bg(asg_id.assignment_id);
268 fetch csr_bg into l_legislation_code;
269 if csr_bg%notfound then
270 close csr_bg;
271 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
272 hr_utility.raise_error;
273 end if;
274 --
275 close csr_bg;
276 --
277 -- Check that the legislation of the specified business
278 -- group is 'GB'. Allow for the fact that the package
279 -- could be called via API or Forms.
280 --
281 if l_legislation_code = 'GB' then
282 --
283 --
284 -- check to see if assignment extra info
285 -- exists for this assignemnt_id
286 --
287 open csr_extra_info(asg_id.assignment_id);
288 fetch csr_extra_info into l_ass_extra_info_id, l_ovn,l_ass_number_old, l_not_included_in_wnu,l_ni_number_update;
289
290 if csr_extra_info%notfound then
291 --
292 hr_utility.set_location(l_proc, 20);
293 --
294 -- Create an entry for WNU
295 --
296 if p_not_included_in_wnu is not null then
297 l_not_included_in_wnu := p_not_included_in_wnu;
298 else
299 l_not_included_in_wnu := 'N';
300 end if;
301 hr_assignment_extra_info_api.create_assignment_extra_info
302 (p_validate => false,
303 p_assignment_id => asg_id.assignment_id,
304 p_information_type => 'GB_WNU',
305 p_aei_information_category => 'GB_WNU',
306 p_aei_information1 => null,
307 p_aei_information2 => l_not_included_in_wnu,
308 p_aei_information3 => p_ni_number_update,
309 p_object_version_number => l_ovn_out,
310 p_assignment_extra_info_id => l_ass_extra_info_id_out
311 );
312
313 p_object_version_number := l_ovn_out;
314 p_assignment_extra_info_id := l_ass_extra_info_id_out;
315 close csr_extra_info;
316
317 else
318 --
319 hr_utility.set_location(l_proc, 30);
320 --
321 -- Update Existing Entry for WNU
322 --
323 if p_not_included_in_wnu is not null then
324 l_not_included_in_wnu := p_not_included_in_wnu;
325 end if;
326 if nvl(l_ni_number_update,'N') <> 'Y' then
327 hr_assignment_extra_info_api.update_assignment_extra_info
328 (p_validate => false,
329 p_object_version_number => l_ovn,
330 p_assignment_extra_info_id => l_ass_extra_info_id,
331 p_aei_information_category => 'GB_WNU',
332 p_aei_information1 => l_ass_number_old,
333 p_aei_information2 => l_not_included_in_wnu,
334 p_aei_information3 => p_ni_number_update
335 );
336 p_object_version_number := l_ovn;
337 --
338 end if;
339 --
340 close csr_extra_info;
341 --
342 end if ;
343 --
344 end if;
345 --
346 end loop;
347 else
348 -- This is for the case when there are no aggregated assignment
349 for asg_id in csr_assignment loop
350
351 open csr_bg(asg_id.assignment_id);
352 fetch csr_bg into l_legislation_code;
353 if csr_bg%notfound then
354 close csr_bg;
355 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
356 hr_utility.raise_error;
357 end if;
358 --
359 close csr_bg;
360 --
361 -- Check that the legislation of the specified business
362 -- group is 'GB'. Allow for the fact that the package
363 -- could be called via API or Forms.
364 --
365 if l_legislation_code = 'GB' then
366 --
367 --
368 -- check to see if assignment extra info
369 -- exists for this assignemnt_id
370 --
371 open csr_extra_info(asg_id.assignment_id);
372 fetch csr_extra_info into l_ass_extra_info_id, l_ovn,l_ass_number_old, l_not_included_in_wnu,l_ni_number_update;
373
374 if csr_extra_info%notfound then
375 --
376 hr_utility.set_location(l_proc, 20);
377 --
378 -- Create an entry for WNU
379 --
380 if p_not_included_in_wnu is not null then
381 l_not_included_in_wnu := p_not_included_in_wnu;
382 else
383 l_not_included_in_wnu := 'N';
384 end if;
385 hr_assignment_extra_info_api.create_assignment_extra_info
386 (p_validate => false,
387 p_assignment_id => asg_id.assignment_id,
388 p_information_type => 'GB_WNU',
389 p_aei_information_category => 'GB_WNU',
390 p_aei_information1 => null,
391 p_aei_information2 => l_not_included_in_wnu,
392 p_object_version_number => l_ovn_out,
393 p_assignment_extra_info_id => l_ass_extra_info_id_out,
394 p_aei_information3 => p_ni_number_update
395 );
396
397 p_object_version_number := l_ovn_out;
398 p_assignment_extra_info_id := l_ass_extra_info_id_out;
399 close csr_extra_info;
400
401 else
402 --
403 hr_utility.set_location(l_proc, 30);
404 --
405 -- Update Existing Entry for WNU
406 --
407 if p_not_included_in_wnu is not null then
408 l_not_included_in_wnu := p_not_included_in_wnu;
409 end if;
410 if nvl(l_ni_number_update,'N') <> 'Y' then
411 hr_assignment_extra_info_api.update_assignment_extra_info
412 (p_validate => false,
413 p_object_version_number => l_ovn,
414 p_assignment_extra_info_id => l_ass_extra_info_id,
415 p_aei_information_category => 'GB_WNU',
416 p_aei_information1 => l_ass_number_old,
417 p_aei_information2 => l_not_included_in_wnu,
418 p_aei_information3 => p_ni_number_update
419 );
420 p_object_version_number := l_ovn;
421 --
422 end if;
423 close csr_extra_info;
424 --
425 end if ;
426 --
427 end if;
428 --
429 end loop;
430 end if;
431 --
432 end if;
433 --
434 hr_utility.set_location('Leaving:'|| l_proc, 100);
435 --
436 end;
437 --
438 end pay_gb_wnu_rules;
[Home] [Help]