[Home] [Help]
PACKAGE BODY: APPS.PAY_IE_WNU_EDI
Source
1 Package Body PAY_IE_WNU_EDI as
2 /* $Header: pyiewnue.pkb 120.0 2005/05/29 05:48:24 appldev noship $ */
3 --
4 -- Globals
5 g_package CONSTANT VARCHAR2(20):= 'PAY_IE_WNU_EDI.';
6 --
7 /* Procedure wnu_update_extra_info calls apis to insert/update records in
8 PER_EXTRA_ASSIGNMENT_INFO */
9 --
10 PROCEDURE wnu_update_extra_info
11 (p_assignment_id in number,
12 p_effective_date in date,
13 p_include_in_wnu in varchar2 default null
14 ) IS
15 --
16 l_ass_extra_info_id number(9):= null;
17 l_ass_extra_info_id_out number(9);
18 l_proc varchar2(72) := 'wnu_update_extra_info';
19 l_current_employee varchar2(30) := null;
20 l_assignment_id number(15):= null;
21 l_include_in_wnu varchar2(30);
22 l_ovn_out number(15);
23 l_ovn 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 and p_effective_date between
34 aaf.effective_start_date and aaf.effective_end_date;
35 --
36 cursor csr_extra_info is
37 select aei.assignment_extra_info_id ,
38 object_version_number
39 from per_assignment_extra_info aei
40 where aei.assignment_id = p_assignment_id
41 and information_type = 'IE_WNU';
42 --
43 begin
44 --
45 hr_utility.set_location('Entering:'|| g_package||l_proc, 10);
46 --
47 -- Assign Variables
48 --
49 l_assignment_id := p_assignment_id;
50 l_include_in_wnu := p_include_in_wnu;
51 --
52 -- check for updation of records
53 --
54 -- Will only update Current Employee Records
55 --
56 open csr_employee;
57 fetch csr_employee into l_current_employee ;
58 close csr_employee;
59 --
60 if l_current_employee = 'Y' then
61 --
62 --
63 -- check to see if assignment extra info
64 -- exists for this assignemnt_id
65 --
66 open csr_extra_info;
67 fetch csr_extra_info into l_ass_extra_info_id, l_ovn;
68 if csr_extra_info%notfound then
69 --
70 hr_utility.set_location(l_proc, 20);
71 --
72 --
73 if l_include_in_wnu = 'Y' THEN
74 -- Create an entry for WNU only when flag is set to Yes
75 -- Check included so that unnecessary records with flag No aren't created when running in FULL Mode
76 --
77 hr_utility.set_location(l_proc, 30);
78 hr_assignment_extra_info_api.create_assignment_extra_info
79 (p_validate => false,
80 p_assignment_id => l_assignment_id,
81 p_information_type => 'IE_WNU',
82 p_aei_information_category => 'IE_WNU',
83 p_aei_information1 => l_include_in_wnu,
84 p_object_version_number => l_ovn_out,
85 p_assignment_extra_info_id => l_ass_extra_info_id_out
86 );
87 hr_utility.trace('Created flag');
88 hr_utility.set_location(l_proc, 40);
89 end if;
90
91 close csr_extra_info;
92
93 else
94 --
95 hr_utility.set_location(l_proc, 30);
96 --
97 -- Update Existing Entry for WNU
98 --
99 hr_assignment_extra_info_api.update_assignment_extra_info
100 (p_validate => false,
101 p_object_version_number => l_ovn,
102 p_assignment_extra_info_id => l_ass_extra_info_id,
103 p_aei_information_category => 'IE_WNU',
104 p_aei_information1 => l_include_in_wnu
105 );
106 hr_utility.trace('Updated flag');
107 --
108 close csr_extra_info;
109 --
110 end if ; -- Extra Info cursor
111 --
112 --
113 end if; -- Current Employee
114 --
115 --
116 hr_utility.set_location('Leaving:'|| l_proc, 100);
117 --
118 end wnu_update_extra_info;
119 --
120 --
121
122 --
123 PROCEDURE range_cursor (pactid IN NUMBER,
124 sqlstr OUT nocopy VARCHAR2)
125 IS
126 l_proc CONSTANT VARCHAR2(60):= g_package||'range_cursor';
127 BEGIN
128 --
129 hr_utility.set_location('Entering: '||l_proc,1);
130 --
131 -- Note: There must be one and only one entry of :payroll_action_id in
132 -- the string, and the statement must be, order by person_id
133 --
134 sqlstr := 'select distinct person_id '||
135 'from per_people_f ppf, '||
136 'pay_payroll_actions ppa '||
137 'where ppa.payroll_action_id = :payroll_action_id '||
138 'and ppa.business_group_id = ppf.business_group_id '||
139 'order by ppf.person_id';
140 --
141 hr_utility.set_location(' Leaving: '||l_proc,100);
142 END range_cursor;
143 --
144 /* PROCEDURE wnu_full_action_creation:
145 This PROC creates assignment actions when running the process in FULL Mode */
146 --
147 Procedure wnu_full_action_creation (pactid in number,
148 stperson in number,
149 endperson in number,
150 chunk in number)
151 IS
152 --
153 -- Bug Number : 4369280
154 -- commented hr_organization_information as the new legal employer classification is now
155 -- attached because of which we dont need the tax reference and the paye reference rather
156 -- we pass the employee reference. This change comes in effect due to a new legal employer
157 -- classification being used.
158
159 cursor csr_state(p_payroll_id NUMBER, p_emp_ref VARCHAR2, p_assignment_set_id NUMBER) IS
160 select asg.assignment_id assignment_id,
161 ppa.effective_date effective_date
162 from per_all_assignments_f asg,
163 pay_payroll_actions ppa,
164 per_all_people_f pap,
165 per_periods_of_service serv,
166 -- hr_organization_information hoi,
167 pay_all_payrolls_f pay,
168 hr_soft_coding_keyflex sck
169 where ppa.payroll_action_id = pactid
170 --and hoi.organization_id = ppa.business_group_id
171 and sck.segment4 = p_emp_ref
172 and asg.business_group_id = ppa.business_group_id
173 and asg.PRIMARY_FLAG = 'Y'
174 and asg.payroll_id = pay.payroll_id
175 and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
176 --and upper(p_tax_ref) = upper(sck.segment1)
177 -- For bug Fix 3567562 added condition to filter records based on PAYE Reference specified as parameter.
178 --and upper(sck.segment3) = upper(hoi.org_information2)
179 --and upper(p_paye_ref) = upper(sck.segment3)
180 --and upper(sck.segment1) = upper(hoi.org_information1)
181 and pay.payroll_id = NVL(p_payroll_id,pay.payroll_id)
182 and (p_assignment_set_id is null
183 OR exists
184 ( select 1
185 from HR_ASSIGNMENT_SET_AMENDMENTS amend,
186 hr_assignment_sets aset
187 where
188 ( P_ASSIGNMENT_SET_ID IS NOT NULL and
189 aset.assignment_set_id = P_ASSIGNMENT_SET_ID
190 -- Bug2856413 To handle payroll in Assgt sets
191 and nvl(aset.payroll_id,pay.payroll_id) = pay.payroll_id
192 and amend.assignment_set_id(+)= aset.assignment_set_id
193 and
194 (
195 (amend.include_or_exclude is not null AND
196 ((amend.include_or_exclude='I' and amend.assignment_id = asg.assignment_id)
197 OR
198 (amend.include_or_exclude='E' and amend.assignment_id <> asg.assignment_id)))
199 OR
200 amend.include_or_exclude is null)
201 )
202 )
203 )
204 and asg.person_id = pap.person_id
205 and serv.person_id = pap.person_id
206 and serv.period_of_service_id = asg.period_of_service_id
207 and serv.date_start = (select max(s.date_start)
208 from per_periods_of_service s
209 where s.person_id = pap.person_id
210 and ppa.effective_date >= s.date_start)
211 and pap.current_employee_flag = 'Y'
212 and asg.person_id between
213 stperson and endperson
214 and ppa.effective_date between
215 asg.effective_start_date and asg.effective_end_date
216 and ppa.effective_date between
217 pap.effective_start_date and pap.effective_end_date
218 and ppa.effective_date between
219 pay.effective_start_date and pay.effective_end_date
220 order by asg.assignment_id;
221 --
222
223 --
224 lockingactid number;
225 l_proc CONSTANT VARCHAR2(60):= g_package||'wnu_full_action_creation';
226 l_payroll_id number(15):= null;
227 --l_tax_ref varchar2(20):=null;
228 l_assignment_set_id number(15) :=null;
229 l_stored_asg_id NUMBER;
230 l_effective_date date;
231 --Added for bug fix 3567562
232 --l_paye_ref varchar2(20):=null;
233 l_emp_ref varchar2(20):=null;
234 --
235 begin
236 --
237 hr_utility.set_location('Entering: '||l_proc,1);
238 --
239
240 -- get parameter values from legislative parameters
241 l_payroll_id := to_number(PAY_IE_ARCHIVE_DETAIL_PKG.get_parameter(pactid,'PAYROLL_ID'));
242 --l_tax_ref := substr(trim(PAY_IE_ARCHIVE_DETAIL_PKG.get_parameter(pactid,'TAX_REF')),1,20);
243 l_assignment_set_id := to_number(PAY_IE_ARCHIVE_DETAIL_PKG.get_parameter(pactid,'ASSIGNMENT_SET_ID')) ;
244 --Added for bug fix 3567562
245 --l_paye_ref := substr(trim(PAY_IE_ARCHIVE_DETAIL_PKG.get_parameter(pactid,'PAYE_REF')),1,20);
246
247
248 --Bug 4369280
249 --commented the tax reference and the paye reference parameters as in the new legal employer
250 --classification they are not needed
251 --rather a new parameter is used for the legal employer reference .
252
253 l_emp_ref := substr(trim(PAY_IE_ARCHIVE_DETAIL_PKG.get_parameter(pactid,'EMP_REF')),1,20);
254 --
255 BEGIN
256
257 for asgrec in csr_state(l_payroll_id, l_emp_ref, l_assignment_set_id) loop
258 --
259 hr_utility.set_location(l_proc,10);
260 --
261 --
262 l_stored_asg_id := asgrec.assignment_id;
263 l_effective_date := asgrec.effective_date;
264 --
265 -- Create the assignment_action
266 --
267 select pay_assignment_actions_s.nextval
268 into lockingactid
269 from dual;
270 --
271 -- insert into pay_assignment_actions.
272 hr_utility.trace('Storing Asg: '|| to_char(l_stored_asg_id));
273 --
274 hr_nonrun_asact.insact(lockingactid=> lockingactid,
275 assignid =>l_stored_asg_id,
276 pactid => pactid,
277 chunk => chunk,
278 greid =>null);
279
280 --
281 hr_utility.set_location(l_proc,20);
282 --
283 -- Update flag set in PER_ASSIGNMENT_EXTRA_INFO for this assignment
284 hr_utility.trace('Updating AEI flag, for asg:'||to_char(l_stored_asg_id));
285 PAY_IE_WNU_EDI.wnu_update_extra_info
286 (p_assignment_id => asgrec.assignment_id,
287 p_effective_date => l_effective_date,
288 p_include_in_wnu => 'N');
289 --
290 hr_utility.trace('Succesfully updated flag');
291
292 end loop;
293 --
294 EXCEPTION WHEN OTHERS THEN
295 hr_utility.trace('Error in Assgt Action cursor'); RAISE;
296 END;
297 --
298 hr_utility.set_location(' Leaving: '||l_proc,100);
299 --
300 end wnu_full_action_creation;
301 --
302 --
303 /* PROCEDURE wnu_update_action_creation:
304 This PROC creates assignment actions when running the process in UPDATE Mode */
305 --
306 Procedure wnu_update_action_creation (pactid in number,
307 stperson in number,
308 endperson in number,
309 chunk in number)
310 IS
311 --
312 -- Bug Number : 4369280
313 -- commented hr_organization_information as the new legal employer classification is now
314 -- attached because of which we dont need the tax reference and the paye reference rather
315 -- we pass the employee reference. This change comes in effect due to a new legal employer
316 -- classification being used.
317
318 cursor csr_state(p_payroll_id NUMBER, p_emp_ref VARCHAR2, p_assignment_set_id NUMBER) is
319 select asg.assignment_id assignment_id,
320 ppa.effective_date effective_date
321 from per_all_assignments_f asg,
322 pay_payroll_actions ppa,
323 per_all_people_f pap,
324 per_periods_of_service serv,
325 per_assignment_extra_info aei,
326 -- hr_organization_information hoi,
327 pay_all_payrolls_f pay,
328 hr_soft_coding_keyflex sck
329 where ppa.payroll_action_id = pactid
330 --and hoi.organization_id = ppa.business_group_id
331 and sck.segment4 = p_emp_ref
332 and asg.business_group_id = ppa.business_group_id
333 and asg.payroll_id = pay.payroll_id
334 and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
335 --and upper(p_tax_ref) = upper(sck.segment1)
336 --and upper(sck.segment1) = upper(hoi.org_information1)
337 -- For bug Fix 3567562 added condition to filter records based on PAYE Reference specified as parameter.
338 --and upper(sck.segment3) = upper(hoi.org_information2)
339 --and upper(p_paye_ref) = upper(sck.segment3)
340 and pay.payroll_id = NVL(p_payroll_id,pay.payroll_id)
341 and (p_assignment_set_id is null
342 OR exists
343 ( select 1
344 from HR_ASSIGNMENT_SET_AMENDMENTS amend,
345 hr_assignment_sets aset
346 where
347 ( P_ASSIGNMENT_SET_ID IS NOT NULL and
348 aset.assignment_set_id = P_ASSIGNMENT_SET_ID
349 -- Bug2856413 To handle payroll in Assgt sets
350 and nvl(aset.payroll_id,pay.payroll_id) = pay.payroll_id
351 and amend.assignment_set_id(+)= aset.assignment_set_id
352 and
353 (
354 (amend.include_or_exclude is not null AND
355 ((amend.include_or_exclude='I' and amend.assignment_id = asg.assignment_id)
356 OR
357 (amend.include_or_exclude='E' and amend.assignment_id <> asg.assignment_id)))
358 OR
359 amend.include_or_exclude is null)
360 )))
361 and asg.person_id = pap.person_id
362 and serv.person_id = pap.person_id
363 and serv.period_of_service_id = asg.period_of_service_id
364 and serv.date_start = (select max(s.date_start)
365 from per_periods_of_service s
366 where s.person_id = pap.person_id
367 and ppa.effective_date >= s.date_start)
368 and pap.current_employee_flag = 'Y'
369 and asg.assignment_id = aei.assignment_id
370 and asg.PRIMARY_FLAG = 'Y'
371 and aei.information_type = 'IE_WNU'
372 and aei.aei_information1 = 'Y'
373 and asg.person_id between
374 stperson and endperson
375 and ppa.effective_date between
376 asg.effective_start_date and asg.effective_end_date
377 and ppa.effective_date between
378 pap.effective_start_date and pap.effective_end_date
379 and ppa.effective_date between
380 pay.effective_start_date and pay.effective_end_date
381 order by asg.assignment_id;
382 --
383 lockingactid number;
384 l_proc CONSTANT VARCHAR2(60):= g_package||'wnu_update_action_creation';
385 l_payroll_id number(15):= null;
386 --l_tax_ref varchar2(20):=null;
387 l_assignment_set_id number(15) :=null;
388 l_stored_asg_id NUMBER;
389 l_effective_date DATE;
390 --Added for bug fix 3567562
391 --l_paye_ref varchar2(20):=null;
392
393 --Bug 4369280
394 --commented the tax reference and the paye reference parameters as in the new legal employer
395 --classification they are not needed
396 --rather a new parameter is used for the legal employer reference .
397
398 l_emp_ref varchar2(20):=null;
399 --
400 begin
401 --
402 hr_utility.set_location('Entering: '||l_proc,1);
403 --
404 -- get parameter values from legislative parameters
405 l_payroll_id := to_number(PAY_IE_ARCHIVE_DETAIL_PKG.get_parameter(pactid,'PAYROLL_ID'));
406 --l_tax_ref := substr(trim(PAY_IE_ARCHIVE_DETAIL_PKG.get_parameter(pactid,'TAX_REF')),1,20);
407 l_assignment_set_id := to_number(PAY_IE_ARCHIVE_DETAIL_PKG.get_parameter(pactid,'ASSIGNMENT_SET_ID')) ;
408 --Added for Bug Fix 3567562
409 --l_paye_ref := substr(trim(PAY_IE_ARCHIVE_DETAIL_PKG.get_parameter(pactid,'PAYE_REF')),1,20);
410 l_emp_ref := substr(trim(PAY_IE_ARCHIVE_DETAIL_PKG.get_parameter(pactid,'EMP_REF')),1,20);
411 --
412 --
413 BEGIN
414 for asgrec in csr_state(l_payroll_id, l_emp_ref,l_assignment_set_id) loop
415 --
416 hr_utility.set_location(l_proc,10);
417 --
418 --
419 l_stored_asg_id := asgrec.assignment_id;
420 l_effective_date := asgrec.effective_date;
421 --
422 -- Create the assignment_action
423 --
424 select pay_assignment_actions_s.nextval
425 into lockingactid
426 from dual;
427 --
428 -- insert into pay_assignment_actions.
429 --
430 hr_utility.trace('Storing Asg: '|| to_char(l_stored_asg_id));
431 --
432 hr_nonrun_asact.insact(lockingactid=> lockingactid,
433 assignid =>l_stored_asg_id,
434 pactid => pactid,
435 chunk => chunk,
436 greid =>null);
437
438 hr_utility.set_location(l_proc,20);
439 --
440 --
441 -- Update flag set in PER_ASSIGNMENT_EXTRA_INFO for this assignment
442 hr_utility.trace('Updating AEI flag, for asg:'||to_char(l_stored_asg_id));
443 PAY_IE_WNU_EDI.wnu_update_extra_info
444 (p_assignment_id => asgrec.assignment_id,
445 p_effective_date => l_effective_date,
446 p_include_in_wnu => 'N');
447 --
448 hr_utility.trace('Succesfully updated flag');
449 end loop;
450 --
451 EXCEPTION WHEN OTHERS THEN
452 hr_utility.trace('Error in Update Assgt Action cursor');
453 RAISE;
454 END;
455 --
456 hr_utility.set_location(' Leaving: '||l_proc,100);
457 --
458 end wnu_update_action_creation;
459 --
460 end PAY_IE_WNU_EDI;