[Home] [Help]
Skip to content
PACKAGE BODY: APPS.PAY_GB_RTI_UPD
Source
1 PACKAGE BODY pay_gb_rti_upd AS
2 /* $Header: paygbrtiupd.pkb 120.0.12020000.14 2013/04/04 06:38:35 ssarap noship $ */
3
4 FUNCTION check_for_active_emp
5 (p_person_id IN per_all_people_f.person_id%TYPE
6 ,p_effective_date IN date) RETURN number IS
7 var number;
8 BEGIN
9 SELECT 1
10 INTO var
11 FROM per_all_people_f pap
12 ,per_person_types ppt
13 WHERE p_effective_date BETWEEN pap.effective_start_date
14 AND pap.effective_end_date
15 AND pap.person_id = p_person_id
16 AND ppt.system_person_type = 'EMP'
17 AND pap.person_type_id = ppt.person_type_id;
18
19
20 RETURN var;
21 EXCEPTION
22 WHEN no_data_found THEN
23 var := 0;
24
25
26 RETURN var;
27 END check_for_active_emp;
28
29 FUNCTION check_profile_exists
30 (p_profile_name IN varchar2) RETURN boolean IS
31 l_profile_value varchar2(30);
32 BEGIN
33 fnd_profile.get (p_profile_name
34 ,l_profile_value);
35
36 IF l_profile_value = 'PARTIAL'
37 OR l_profile_value = 'ALL' THEN
38 RETURN TRUE;
39 ELSE
40 RETURN FALSE;
41 END IF;
42 END check_profile_exists;
43
44 FUNCTION get_soy_date
45 (p_effective_date IN date) RETURN date IS
46 l_date_soy date;
47 BEGIN
48
49 IF p_effective_date
50 >= to_date ('06-04-'
51 || substr (to_char (p_effective_date
52 ,'YYYY/MON/DD')
53 ,1
54 ,4)
55 ,'DD-MM-YYYY') THEN
56 l_date_soy := to_date ('06-04-'
57 || substr (to_char (p_effective_date
58 ,'YYYY/MON/DD')
59 ,1
60 ,4)
61 ,'DD-MM-YYYY');
62
63 hr_utility.trace ('if part:'
64 || l_date_soy);
65 ELSE
66 l_date_soy := to_date ('06-04-'
67 || to_char (to_number (substr (to_char (p_effective_date
68 ,'YYYY/MON/DD')
69 ,1
70 ,4)) - 1)
71 ,'DD-MM-YYYY');
72
73 hr_utility.trace ('else part:'
74 || l_date_soy);
75 END IF;
76
77 hr_utility.trace ('date after soy check:'
78 || l_date_soy);
79 RETURN fnd_date.date_to_displaydate (l_date_soy);
80 END get_soy_date;
81
82 PROCEDURE get_extra_info_exists
83 (p_assignment_id IN per_all_assignments_f.assignment_id%type
84 ,p_assignment_extra_info_id OUT NOCOPY number
85 ,p_object_version_number OUT NOCOPY number) IS
86 l_number number;
87 BEGIN
88 SELECT assignment_extra_info_id
89 ,object_version_number
90 INTO p_assignment_extra_info_id
91 ,p_object_version_number
92 FROM per_assignment_extra_info
93 WHERE assignment_id = p_assignment_id
94 AND aei_information_category = 'GB_RTI_AGGREGATION';
95 EXCEPTION
96 WHEN no_data_found THEN
97 p_assignment_extra_info_id := - 1;
98 END get_extra_info_exists;
99
100 FUNCTION get_ni_reporting_flag
101 (p_assignment_id IN number
102 ,p_effective_date IN date
103 ,p_person_id IN number
104 ,p_paye_reference in varchar2) RETURN varchar2 IS
105 v_per_ni_flag varchar2(2);
106 v_per_agg_flag varchar2(2);
107 v_primary_flag varchar2(2);
108 l_ni_reporting_flag varchar2(2);
109 l_assignment_id NUMBER;
110
111 cursor csr_primary_exists is
112 select paf.assignment_id
113 from
114 per_all_people_f pap,
115 per_all_assignments_f paf
116 where paf.person_id = pap.person_id
117 and paf.person_id = p_person_id
118 and p_effective_date between paf.effective_start_date and paf.effective_end_date
119 and p_effective_date between pap.effective_start_date and pap.effective_end_date
120 and nvl(paf.primary_flag,'N')='Y'
121 and paf.assignment_type='E';
122
123 cursor csr_primary_in_curr_paye is
124 select paf.assignment_id
125 from
126 per_all_people_f pap,
127 per_all_assignments_f paf,
128 pay_all_payrolls_f pay,
129 hr_soft_coding_keyflex hsc
130 where paf.person_id = pap.person_id
131 and paf.assignment_id = p_assignment_id
132 and paf.payroll_id = pay.payroll_id
133 and pay.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
134 and upper(hsc.segment1) = upper(p_paye_reference)
135 and p_effective_date between paf.effective_start_date and paf.effective_end_date
136 and p_effective_date between pap.effective_start_date and pap.effective_end_date
137 and p_effective_date between pay.effective_start_date and pay.effective_end_date
138 and nvl(paf.primary_flag,'N')='Y'
139 and paf.assignment_type='E';
140
141 -- find minimum assignment
142 cursor csr_oldest_asg is
143 select min(paf.assignment_id) from
144 per_all_assignments_f paf,
145 pay_all_payrolls_f pay,
146 hr_soft_coding_keyflex hsc
147 where paf.payroll_id= pay.payroll_id
148 and pay.soft_coding_keyflex_id= hsc.soft_coding_keyflex_id
149 and p_effective_date between pay.effective_start_date and pay.effective_end_date
150 and p_effective_date between paf.effective_start_date and paf.effective_end_date
151 and paf.person_id = p_person_id
152 and upper(hsc.segment1)= upper(p_paye_reference)
153 and paf.assignment_type='E';
154
155 cursor scr_ni_paye_aggr_flag is
156 SELECT trim (pap.per_information10) per_agg_flag
157 ,trim (pap.per_information9) per_ni_flag
158 ,nvl (paf.primary_flag
159 ,'N') primary_flag
160
161 FROM per_all_people_f pap
162 ,per_all_assignments_f paf
163 WHERE paf.person_id = pap.person_id
164 AND paf.assignment_id = p_assignment_id
165 AND p_effective_date BETWEEN paf.effective_start_date
166 AND paf.effective_end_date
167 AND p_effective_date BETWEEN pap.effective_start_date
168 AND pap.effective_end_date;
169
170
171 BEGIN
172 hr_utility.trace('Entering get_ni_reporting_flag function..');
173 hr_utility.trace('Parameters are:');
174 hr_utility.trace('p_assignment_id:'||p_assignment_id);
175 hr_utility.trace('p_person_id:'||p_person_id);
176 hr_utility.trace('p_effective_date:'||p_effective_date);
177 hr_utility.trace('p_paye_reference:'||p_paye_reference);
178
179 l_ni_reporting_flag:=null;
180 open scr_ni_paye_aggr_flag;
181 fetch scr_ni_paye_aggr_flag INTO v_per_agg_flag
182 ,v_per_ni_flag
183 ,v_primary_flag;
184 close scr_ni_paye_aggr_flag;
185 /* SELECT trim (pap.per_information10) per_agg_flag
186 ,trim (pap.per_information9) per_ni_flag
187 ,nvl (paf.primary_flag
188 ,'N') primary_flag
189 INTO v_per_agg_flag
190 ,v_per_ni_flag
191 ,v_primary_flag
192 FROM per_all_people_f pap
193 ,per_all_assignments_f paf
194 WHERE paf.person_id = pap.person_id
195 AND paf.assignment_id = p_assignment_id
196 AND p_effective_date BETWEEN paf.effective_start_date
197 AND paf.effective_end_date
198 AND p_effective_date BETWEEN pap.effective_start_date
199 AND pap.effective_end_date;*/
200
201 hr_utility.trace('fetched ni and paye aggregation flag');
202 hr_utility.trace('v_per_agg_flag:'||v_per_agg_flag);
203 hr_utility.trace('v_per_ni_flag:'||v_per_ni_flag);
204 hr_utility.trace('v_primary_flag:'||v_primary_flag);
205 IF nvl (v_per_agg_flag
206 ,'N') = 'N'
207 AND nvl (v_per_ni_flag
208 ,'N') = 'Y' then
209 --AND nvl (v_primary_flag
210 --,'N') = 'Y' THEN
211 hr_utility.trace('Assignments are NI aggregated, hence in if part');
212 open csr_primary_exists;
213 fetch csr_primary_exists into l_assignment_id;
214 hr_utility.trace('Checking if Primary Assignment exists or not');
215 if csr_primary_exists%notfound then
216 l_ni_reporting_flag:= null;
217 hr_utility.trace('Primary Assignment is not found');
218 else-- primary flag exists
219 open csr_primary_in_curr_paye;
220 fetch csr_primary_in_curr_paye into l_assignment_id;-- check for primary flag in current paye reference
221 hr_utility.trace('Checking if Primary Assignment exists in same PAYE reference');
222 if csr_primary_in_curr_paye%notfound then
223 hr_utility.trace('Primary not present in current PAYE reference');
224 open csr_oldest_asg;
225 fetch csr_oldest_asg into l_assignment_id;
226 close csr_oldest_asg;
227 end if;
228 close csr_primary_in_curr_paye;
229 end if;
230 close csr_primary_exists;
231 hr_utility.trace('The primary assignment_id fetched by the cursor is:'||l_assignment_id);
232 if l_assignment_id is not null then
233 if l_assignment_id=p_assignment_id then
234 l_ni_reporting_flag:='Y';
235 hr_utility.trace('Current One is Primary Assignment hence flag is set');
236 else
237 hr_utility.trace('Current One is not Primary Assignment hence flag is not set');
238 l_ni_reporting_flag:='N';
239 end if;
240
241 end if;
242 else
243 hr_utility.trace('Assignments are NOT NI aggregated, hence in else part, setting the flag to No');
244 l_ni_reporting_flag:='N';
245 END IF;
246 hr_utility.trace('leaving get_ni_reporting_flag for assignment:'||p_assignment_id||'- is '||l_ni_reporting_flag);
247 RETURN l_ni_reporting_flag;
248 END get_ni_reporting_flag;
249
250 FUNCTION check_primary_assignment(p_person_id in number, p_effective_date in date)
251 return boolean
252 is
253 v_status boolean;
254 v_no number;
255 begin
256 SELECT 1
257 INTO v_no
258 FROM per_all_assignments_f
259 WHERE person_id = p_person_id
260 AND nvl (primary_flag
261 ,'N') = 'Y'
262 AND assignment_type = 'E'
263 AND p_effective_date BETWEEN effective_start_date
264 AND effective_end_date;
265 v_status:= true;
266 return v_status;
267 exception when no_data_found then
268 v_status:= false;
269 return v_status;
270 end check_primary_assignment;
271 FUNCTION get_rti_payroll_id
272 (p_person_id IN per_all_people_f.person_id%TYPE
273 ,p_assignment_id IN per_all_assignments_f.assignment_id%type
274 ,p_aggregation_flag IN varchar2
275 ,p_ni_aggregation_flag IN varchar2
276 ,p_effective_date IN date
277 ) RETURN varchar2 IS
278 v_rti_payroll_id varchar2(30);
279 v_rti_assignment_id varchar2(30);
280 v_check_data varchar2(10);
281 BEGIN
282 hr_utility.trace('start of rti_payroll_id');
283 IF nvl (p_aggregation_flag
284 ,'N') = 'Y' THEN
285 SELECT assignment_number
286 INTO v_rti_payroll_id
287 FROM per_all_assignments_f
288 WHERE person_id = p_person_id
289 AND nvl (primary_flag
290 ,'N') = 'Y'
291 AND p_effective_date BETWEEN effective_start_date
292 AND effective_end_date;
293
294 ELSE
295 if nvl(p_ni_aggregation_flag,'N')='Y' then
296 -- Only NI aggregated, check if there is primary assignment available
297 if check_primary_assignment(p_person_id, p_effective_date ) then
298 SELECT assignment_number
299 INTO v_rti_payroll_id
300 FROM per_all_assignments_f
301 WHERE assignment_id = p_assignment_id
302 AND p_effective_date BETWEEN effective_start_date
303 AND effective_end_date;
304 else
305 -- no primary assignment found
306 v_rti_payroll_id:= null;
307 end if;
308 else
309 SELECT assignment_number
310 INTO v_rti_payroll_id
311 FROM per_all_assignments_f
312 WHERE assignment_id = p_assignment_id
313 AND p_effective_date BETWEEN effective_start_date
314 AND effective_end_date;
315 end if;
316
317 END IF;
318 RETURN v_rti_payroll_id;
319 exception when no_data_found then
320 v_rti_payroll_id:= NULL;
321 RETURN v_rti_payroll_id;
322 END get_rti_payroll_id;
323
324
325 PROCEDURE update_rti_agg_update_asg
326 (p_assignment_id IN per_all_assignments_f.assignment_id%type
330 SELECT DISTINCT
327 ,p_effective_date IN date) IS
328 p_person_id number;
329 BEGIN
331 person_id
332 INTO p_person_id
333 FROM per_all_assignments_f
334 WHERE assignment_id = p_assignment_id
335 AND p_effective_date BETWEEN effective_start_date
336 AND effective_end_date;
337
338 pay_gb_rti_upd.update_rti_agg_person (p_person_id
339 ,p_effective_date);
340
341 END update_rti_agg_update_asg;
342
343 PROCEDURE update_rti_agg_new_person
344 (p_person_id IN per_all_people_f.person_id%TYPE
345 ,p_hire_date IN date) IS
346 BEGIN
347 pay_gb_rti_upd.update_rti_agg_person
348 (p_person_id => p_person_id
349 ,p_effective_date => p_hire_date);
350 END update_rti_agg_new_person;
351
352 PROCEDURE update_rti_agg_person
353 (p_person_id IN per_all_people_f.person_id%TYPE
354 ,p_effective_date IN date) IS
355 CURSOR csr_ni_paye_flag IS
356 SELECT trim (pap.per_information10) per_agg_flag
357 ,trim (pap.per_information9) per_ni_flag
358 FROM per_all_people_f pap
359 WHERE pap.person_id = p_person_id
360 AND p_effective_date BETWEEN pap.effective_start_date
361 AND pap.effective_end_date;
362 CURSOR csr_current_person_details IS
363 SELECT trim (paf.primary_flag) asg_primary_flag
364 ,trim (pap.per_information10) per_agg_flag
365 ,trim (pap.per_information9) per_ni_flag
366 ,paf.assignment_id assignment_id
367 ,paf.assignment_number assignment_number
368 ,paf.effective_start_date assignment_start_date
369 ,pap.business_group_id business_group_id
370 FROM per_all_people_f pap
371 ,per_all_assignments_f paf
372 WHERE paf.person_id = pap.person_id
373 AND pap.person_id = p_person_id
374 AND p_effective_date BETWEEN pap.effective_start_date
375 AND pap.effective_end_date
376 AND p_effective_date BETWEEN paf.effective_start_date
377 AND paf.effective_end_date;
378 v_person_details csr_current_person_details%ROWTYPE;
379 v_person_flags csr_ni_paye_flag%ROWTYPE;
380 p_object_version_number number;
381 l_assignment_number varchar2(30);
382 l_ni_reporting_flag varchar2(2);
383 l_rti_payroll_id varchar2(30);
384 l_payroll_id number;
385 p_assignment_extra_info_id number;
386 p_effect_date date;
387 BEGIN
388
389
390 hr_utility.trace ('inside hook call new rti aggregation package');
391
392 hr_utility.trace ('person_id:'
393 || p_person_id);
394
395 hr_utility.trace ('p_effective_date:'
396 || p_effective_date);
397
398 IF ((check_profile_exists ('GB RTI UPTAKE'))
399 AND (check_for_active_emp (p_person_id
400 ,p_effective_date) = 1)) THEN
401 hr_utility.trace ('profile is active so inside extra entry');
402
403 OPEN csr_ni_paye_flag;
404
405 FETCH csr_ni_paye_flag
406 INTO v_person_flags;
407
408 CLOSE csr_ni_paye_flag;
409
410 FOR c_person_info IN csr_current_person_details LOOP
411 l_rti_payroll_id := get_rti_payroll_id (p_person_id
412 ,c_person_info.assignment_id
413 ,v_person_flags.per_agg_flag
414 ,v_person_flags.per_ni_flag
415 ,p_effective_date);
416
417 hr_utility.trace ('v_person_flags.per_agg_flag:'
418 || c_person_info.per_agg_flag);
419
420 hr_utility.trace ('p_effective_date:'
421 || p_effective_date);
422
423 l_ni_reporting_flag := get_ni_reporting_flag (c_person_info.assignment_id
424 ,p_effective_date,p_person_id, null);
425
426 get_extra_info_exists (c_person_info.assignment_id
427 ,p_assignment_extra_info_id
428 ,p_object_version_number);
429
430 hr_utility.trace ('p_assignment_extra_info_id:'
431 || p_assignment_extra_info_id);
432
433 hr_utility.trace ('assignment_id:'
434 || c_person_info.assignment_id);
435
436 p_effect_date := get_soy_date (fnd_date.date_to_displaydate (p_effective_date));
437
438 hr_utility.trace ('p_effect_date:'
439 || p_effect_date);
440
441 IF (p_effect_date <= c_person_info.assignment_start_date) THEN
442 p_effect_date := c_person_info.assignment_start_date;
443 END IF;
444
445 hr_utility.trace (' after if loop p_effect_date:'
446 || p_effect_date);
447
448 IF p_assignment_extra_info_id = - 1 THEN
449 pay_gb_aei_api.pay_gb_ins_rti_agg_strt
450 (p_assignment_id => c_person_info.assignment_id
451 ,p_business_group_id => c_person_info.business_group_id
452 ,p_information_type => 'GB_RTI_AGGREGATION'
453 ,p_aei_information_category => 'GB_RTI_AGGREGATION'
454 ,p_aei_information1 => l_ni_reporting_flag
455 ,p_aei_information2 => fnd_date.date_to_canonical (p_effect_date)
456 ,p_aei_information3 => l_rti_payroll_id
460 ELSE
457 ,p_aei_information4 => l_payroll_id
458 ,p_object_version_number => p_object_version_number
459 ,p_assignment_extra_info_id => p_assignment_extra_info_id);
461 pay_gb_aei_api.pay_gb_upd_rti_agg_strt
462 (p_assignment_extra_info_id => p_assignment_extra_info_id
463 ,p_business_group_id => c_person_info.business_group_id
464 ,p_object_version_number => p_object_version_number
465 ,p_aei_information_category => 'GB_RTI_AGGREGATION'
466 ,p_aei_information1 => l_ni_reporting_flag
467 ,p_aei_information2 => fnd_date.date_to_canonical (p_effect_date)
468 ,p_aei_information3 => l_rti_payroll_id
469 ,p_aei_information4 => l_payroll_id);
470 END IF;
471 END LOOP;
472 END IF;
473 EXCEPTION
474 WHEN others THEN
475 hr_utility.trace (sqlerrm);
476
477 hr_utility.trace ('leaving update_rti_agg_person');
478
479
480 END update_rti_agg_person;
481
482 PROCEDURE py_gb_rti_payroll_updt
483 (errbuf OUT NOCOPY varchar2
484 ,retcode OUT NOCOPY number
485 ,tax_ref_no IN varchar2
486 ,p_effective_date IN varchar2
487 ,l_business_group_id IN number) IS
488 l_effective_date date;
489 CURSOR c_person_details(cp_soy_date DATE) IS
490 SELECT DISTINCT
491 asg.assignment_id assignment_id
492 ,trim (asg.primary_flag) asg_primary_flag
493 ,trim (pap.per_information10) per_agg_flag
494 ,trim (pap.per_information9) per_ni_flag
495 ,pap.person_id person_id
496 ,REGEXP_REPLACE(asg.assignment_number,'[]\#^}{_.@\[\$]','')
497 assignment_number
498 ,asg.payroll_id payroll_id
499 ,asg.effective_start_date assignment_start_date
500 ,REGEXP_REPLACE( nvl(
501 (SELECT MIN(paaf2.assignment_number)
502 FROM per_all_assignments_f paaf2,
503 pay_all_payrolls_f papf,
504 hr_soft_coding_keyflex hsck
505 WHERE paaf2.person_id = asg.person_id
506 AND papf.payroll_id = paaf2.payroll_id
507 AND papf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
508 AND upper(hsck.SEGMENT1) = upper(tax_ref_no)
509 AND paaf2.assignment_type = 'E'
510 AND paaf2.primary_flag = 'Y'
511 AND asg.effective_start_date BETWEEN paaf2.effective_start_date
512 AND paaf2.effective_end_date
513 AND pay.effective_start_date BETWEEN papf.effective_start_date
514 AND papf.effective_end_date)
515 ,
516 (SELECT MIN(paaf2.assignment_number)
517 FROM per_all_assignments_f paaf2,
518 pay_all_payrolls_f papf,
519 hr_soft_coding_keyflex hsck
520 WHERE paaf2.person_id = asg.person_id
521 AND papf.payroll_id = paaf2.payroll_id
522 AND papf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
523 AND upper(hsck.SEGMENT1) = upper(tax_ref_no)
524 AND paaf2.assignment_type = 'E'
525 AND asg.effective_start_date BETWEEN paaf2.effective_start_date
526 AND paaf2.effective_end_date
527 AND pay.effective_start_date BETWEEN papf.effective_start_date
528 AND papf.effective_end_date)),'[]\#^}{_.@\[\$]','') primary_assignment_number
529
530 ,pap.employee_number
531 FROM per_all_people_f pap
532 ,per_all_assignments_f asg
533 ,per_assignment_status_types past
534 --,per_periods_of_service serv
535 ,pay_all_payrolls_f pay
536 ,hr_soft_coding_keyflex sck
537 WHERE --pap.current_employee_flag = 'Y'
538 pap.person_id = asg.person_id
539 AND asg.business_group_id = l_business_group_id
540 AND asg.assignment_status_type_id = past.assignment_status_type_id
541 --AND past.per_system_status IN ('ACTIVE_ASSIGN','SUSP_ASSIGN','TERM_ASSIGN')
542 AND asg.payroll_id = pay.payroll_id
543 --AND asg.period_of_service_id = serv.period_of_service_id
544 AND pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
545 AND upper (tax_ref_no) = upper (sck.segment1)
546 AND asg.assignment_type = 'E'
547 AND fnd_date.canonical_to_date(p_effective_date) between pap.effective_start_date
548 and pap.effective_end_date
549 AND fnd_date.canonical_to_date(p_effective_date) between asg.effective_start_date
550 and asg.effective_end_date
551 AND fnd_date.canonical_to_date(p_effective_date) between pay.effective_start_date
552 and pay.effective_end_date
553 /*AND pap.effective_start_date =
554 (
555 SELECT MAX(papf2.effective_start_date)
556 FROM per_all_people_f papf2
557 WHERE papf2.person_id = pap.person_id
558 )
559 AND asg.effective_start_date =
560 (
561 SELECT MAX(paaf2.effective_start_date)
562 FROM per_all_assignments_f paaf2
563 WHERE paaf2.assignment_id = asg.assignment_id
564 AND paaf2.assignment_type = 'E'
565 )
569 ORDER BY person_id
566 AND asg.effective_end_date >= cp_soy_date
567 AND TRUNC(sysdate) BETWEEN pay.effective_start_date
568 AND pay.effective_end_date*/
570 ,asg_primary_flag DESC;
571
572
573 CURSOR c_payroll_details
574 (v_assignment_id IN number) IS
575 SELECT paei.assignment_extra_info_id assignment_extra_info_id
576 ,paei.assignment_id assignment_id
577 ,paei.aei_information3 assignment_number
578 ,paei.aei_information4 payroll_id
579 ,paei.object_version_number object_version_number
580 FROM per_assignment_extra_info paei
581 WHERE paei.assignment_id = v_assignment_id
582 AND information_type = 'GB_RTI_AGGREGATION';
583
584
585 v_person_details c_person_details%ROWTYPE;
586 l_payroll_id varchar2(150);
587 l_primary_asg_id number;
588 v_assignment_extra_info_id per_assignment_extra_info.assignment_extra_info_id%TYPE;
589 v_payroll_details c_payroll_details%ROWTYPE;
590 l_person_id number;
591 check_payroll_exp EXCEPTION;
592 l_assignment_number varchar2(100);
593 p_assignment_extra_info_id number;
594 p_object_version_number number;
595 l_ni_reporting_flag varchar2(1);
596 l_ni_reporting_flag_display varchar2(3);
597 p_effect_date date;
598 l_rti_payroll_id varchar2(30);
599 l_count_processed number;
600 l_err_employee ERR_EMPLOYEE;
601 l_err_count NUMBER;
602 l_prev_emp_number per_all_people_f.employee_number%type;
603 l_pre_emp_length number;
604 l_curr_emp_length number;
605 l_proc CONSTANT varchar2(30) := 'PY_GB_RTI_PAYROLL_UPDT';
606 l_total_count NUMBER;
607 l_asg_pr_count NUMBER;
608 l_asg_not_pr_count NUMBER;
609 l_current_asg_id NUMBER;
610
611 ret_normal_status CONSTANT NUMBER := 0;
612 ret_warning_status CONSTANT NUMBER := 1;
613 ret_error_status CONSTANT NUMBER := 2;
614
615 -- User Exceptions
616 ex_complete_normal EXCEPTION;
617 ex_complete_warning EXCEPTION;
618 ex_complete_error EXCEPTION;
619 BEGIN
620 l_err_count:= 0;
621 l_asg_pr_count:=0;
622 l_asg_not_pr_count:=0;
623 l_total_count:=0;
624
625 retcode:=ret_error_status;
626 l_prev_emp_number:= null;
627 hr_utility.trace('Entering py_gb_rti_payroll_updt');
628 hr_utility.trace('Parameters are:');
629 hr_utility.trace('tax_ref_no:'||tax_ref_no);
630 hr_utility.trace('p_effective_date:'||p_effective_date);
631 hr_utility.trace('l_business_group_id:'||l_business_group_id);
632 p_effect_date := get_soy_date (fnd_date.canonical_to_date(p_effective_date));
633 hr_utility.trace('Effective Date after get_soy_date call:'||p_effect_date);
634
635 fnd_file.put_line (fnd_file.output
636 ,rpad ('-----------------------------------------------------------------'
637 ,80));
638
639 fnd_file.put_line (fnd_file.output
640 ,rpad ('RTI Payroll Report'
641 ,80)
642 || rpad (' '
643 ,37
644 ,' ')
645 || sysdate);
646
647 fnd_file.put_line (fnd_file.output
648 ,' ');
649
650 fnd_file.put_line (fnd_file.output
651 ,'Business Group : '
652 || l_business_group_id);
653
654 fnd_file.put_line (fnd_file.output
655 ,'Effective Date : '
656 || fnd_date.canonical_to_date(p_effective_date));
657 fnd_file.put_line (fnd_file.output
658 ,rpad (' '
659 ,80));
660 fnd_file.put_line (fnd_file.output
661 ,rpad (' '
662 ,80));
663
664 fnd_file.put_line (fnd_file.output
665 ,rpad ('-----------------------------------------------------------------'
666 ,80));
667
668
669 fnd_file.put_line (fnd_file.output
670 ,rpad ('Assignments Processed in this Run'
671 ,80));
672 fnd_file.put_line (fnd_file.output
673 ,rpad ('-----------------------------------------------------------------'
674 ,80));
675 fnd_file.put_line (fnd_file.output
676 ,rpad ('-----------------------------------------------------------------'
677 ,80));
678
679 hr_utility.trace('before start of the loop');
680 FOR v_person_details IN c_person_details(p_effect_date) LOOP
681 hr_utility.trace('In v_person_details Loop:');
682 hr_utility.trace('Person_id:'||v_person_details.person_id);
683 hr_utility.trace('Assignment_id:'||v_person_details.assignment_id);
684 hr_utility.trace('Primary Flag:'||v_person_details.asg_primary_flag);
685 l_total_count:= l_total_count +1;
686 l_current_asg_id:=v_person_details.assignment_id;
687 IF v_person_details.asg_primary_flag = 'Y' THEN
688 l_primary_asg_id := v_person_details.assignment_id;
689 END IF;
690
691 --Setting it to current assignment number, then later change conditionally.
692 l_rti_payroll_id:= v_person_details.assignment_number;
693
694 if nvl (v_person_details.per_agg_flag
695 ,'N') = 'Y' then
696 l_rti_payroll_id:= v_person_details.primary_assignment_number;
697 end if;
701 ELSE
698 hr_utility.trace('RTI Payroll ID is:'||l_rti_payroll_id);
699 IF (p_effect_date <= v_person_details.assignment_start_date) THEN
700 l_effective_date := v_person_details.assignment_start_date;
702 l_effective_date:=p_effect_date;
703 END IF;
704
705 hr_utility.trace('Effective Date for the assignment is now set to:'||l_effective_date);
706 hr_utility.trace('calling NI reporting flag function...');
707 l_ni_reporting_flag := null;
708 l_ni_reporting_flag := get_ni_reporting_flag (v_person_details.assignment_id
709 ,l_effective_date,
710 v_person_details.person_id, tax_ref_no);
711 hr_utility.trace('Back to Main Function after NI reporting flag fetch...');
712
713 if l_rti_payroll_id is not null
714 and l_ni_reporting_flag is not null then
715
716 hr_utility.trace('RTI PayrollID is not null for assignment:'||v_person_details.assignment_id);
717 OPEN c_payroll_details (v_person_details.assignment_id);
718
719 FETCH c_payroll_details
720 INTO v_payroll_details;
721
722 IF c_payroll_details%NOTFOUND THEN
723 hr_utility.trace('For this assignment extra_information doesnt exists hence inserting it');
724 SELECT per_assignment_extra_info_s.nextval
725 INTO v_assignment_extra_info_id
726 FROM dual;
727
728 hr_utility.trace ('assignment_id:'
729 || v_person_details.assignment_id);
730
731 hr_utility.trace ('l_assignment_number:'
732 || l_assignment_number);
733
734 hr_utility.trace ('p_effect_date:'
735 || p_effect_date);
736
737 hr_utility.trace('Calling the Extra Information Insert API...');
738 hr_utility.trace('Parameters are:');
739 hr_utility.trace('p_aei_information1:'||l_ni_reporting_flag);
740 hr_utility.trace('p_aei_information2:'||fnd_date.date_to_canonical(p_effect_date));
741 hr_utility.trace('p_aei_information3:'||l_rti_payroll_id);
742
743 pay_gb_aei_api.pay_gb_ins_rti_agg_strt
744 (p_assignment_id => v_person_details.assignment_id
745 ,p_business_group_id => l_business_group_id
746 ,p_information_type => 'GB_RTI_AGGREGATION'
747 ,p_aei_information_category => 'GB_RTI_AGGREGATION'
748 ,p_aei_information1 => l_ni_reporting_flag
749 ,p_aei_information2 => fnd_date.date_to_canonical(l_effective_date)
750 ,p_aei_information3 => l_rti_payroll_id
751 ,p_aei_information4 => l_payroll_id
752 ,p_object_version_number => p_object_version_number
753 ,p_assignment_extra_info_id => p_assignment_extra_info_id);
754
755 hr_utility.trace('Back to main program after insert api call...');
756 l_asg_pr_count := l_asg_pr_count +1;
757 fnd_file.put_line (fnd_file.output
758 ,rpad ('Person ID: '
759 || v_person_details.person_id
760 ,80));
761
762 fnd_file.put_line (fnd_file.output
763 ,rpad ('Assignment ID: '
764 || v_person_details.assignment_id
765 ,80));
766
767 fnd_file.put_line (fnd_file.output
768 ,rpad ('RTI Payroll ID: '
769 || l_rti_payroll_id
770 ,80));
771
772 IF l_ni_reporting_flag = 'Y' THEN
773 l_ni_reporting_flag_display := 'Yes';
774 ELSE
775 l_ni_reporting_flag_display := 'No';
776 END IF;
777
778 fnd_file.put_line (fnd_file.output
779 ,rpad ('NI Reporting Flag: '
780 || l_ni_reporting_flag_display
781 ,80));
782
783 fnd_file.put_line (fnd_file.output
784 ,rpad ('-----------------------------------------------------------------'
785 ,80));
786 ELSE
787 l_count_processed := 1;
788 l_asg_not_pr_count:=l_asg_not_pr_count +1;
789 hr_utility.trace('For this assignment extra_information already exists hence skipping it');
790 END IF;
791
792 CLOSE c_payroll_details;
793 else
794 hr_utility.trace('RTI Payroll ID is null, cause may be data corruption for this person');
795 l_pre_emp_length:= length(l_prev_emp_number);
796 l_curr_emp_length:= length(v_person_details.employee_number);
797 if v_person_details.employee_number = l_prev_emp_number then
798 null;
799
800 else
801 hr_utility.trace('both are not equal');
802 hr_utility.trace('v_person_details.employee_number <> l_prev_emp_number');
803 l_err_count:= l_err_count + 1;
804 l_err_employee(l_err_count):= v_person_details.employee_number;
805 l_prev_emp_number:= v_person_details.employee_number;
806 end if;
807
808 -- add the employee number and say these assignments dont have primary flag set
809 end if;
810 hr_utility.trace('End of loop');
811 END LOOP;
812 commit;
813
814 hr_utility.trace('Total Count is:'||l_total_count);
815 hr_utility.trace('Assignment Count Processesed:'||l_asg_pr_count);
816 hr_utility.trace('Assignment Count Not Processesed:'||l_asg_not_pr_count);
817 hr_utility.trace('Error count is:'||l_err_count);
818 if l_err_count>0 then
819 --loop through the employees and show the errors
820
821 fnd_file.put_line (fnd_file.output
822 ,rpad (' '
823 ,80));
824 fnd_file.put_line (fnd_file.output
825 ,rpad (' '
826 ,80));
827
828 fnd_file.put_line (fnd_file.output
829 ,rpad ('-----------------------------------------------------------------'
830 ,80));
831 fnd_file.put_line (fnd_file.output
832 ,rpad ('Employees Not Processed in this Run'
833 ,80));
834 fnd_file.put_line (fnd_file.output
835 ,rpad ('-----------------------------------------------------------------'
836 ,80));
837 fnd_file.put_line (fnd_file.output
838 ,rpad ('-----------------------------------------------------------------'
839 ,80));
840 fnd_file.put_line (fnd_file.output
841 ,rpad ('Employee Numbers with no Primary Assignment:'
842 ,80));
843
844
845 for l_err_emp_number in l_err_employee.first..l_err_employee.last loop
846 hr_utility.trace('l_err_employee(l_err_emp_number):'||l_err_employee(l_err_emp_number));
847 fnd_file.put_line (fnd_file.output
848 ,rpad(l_err_employee(l_err_emp_number),80)
849 );
850
851
852 end loop;
853 RAISE ex_complete_warning;
854 fnd_file.put_line (fnd_file.output
855 ,rpad ('-----------------------------------------------------------------'
856 ,80));
857 null;
858 end if;
859 IF l_count_processed = 1 and l_err_count=0 THEN
860 fnd_file.put_line (fnd_file.output
861 ,rpad ('No Assignments Found'
862 ,80));
863 END IF;
864 fnd_file.put_line (fnd_file.output
865 ,rpad ('-----------------------------------------------------------------'
866 ,80));
867
868 fnd_file.put_line(fnd_file.output,rpad('Total Assignment Count Picked up by the process:'||l_total_count,80));
869 fnd_file.put_line(fnd_file.output,rpad('Assignment Count Processesed in this run:'||l_asg_pr_count,80));
870 fnd_file.put_line(fnd_file.output,rpad('Assignment Count Not Processesed in this run:'||l_asg_not_pr_count,80));
871 retcode:=ret_normal_status;
872 EXCEPTION
873 WHEN check_payroll_exp THEN
874 raise_application_error (- 20101
875 ,'Person with person id'
876 || l_person_id
877 || ' already has payroll id populated please check');
878
879 when ex_complete_warning then
880 retcode := ret_warning_status;
881 errbuf := l_proc || ' : Completed with Warning!';
882 when ex_complete_error then
883 retcode := ret_error_status;
884 errbuf := l_proc || ' : Completed with Error!';
885 when others then
886 hr_utility.trace('Error occured :'||SQLERRM);
887 hr_utility.trace('Count Values till now:');
888 hr_utility.trace('Total Assignment Count is:'||l_total_count);
889 hr_utility.trace('Count of Assignments Processesed:'||l_asg_pr_count);
890 hr_utility.trace('Count of Assignments Not Processesed:'||l_asg_not_pr_count);
891 hr_utility.trace('Current Assignment is:'||l_current_asg_id);
892 hr_utility.trace('Error count is:'||l_err_count);
893 END py_gb_rti_payroll_updt;
894 END pay_gb_rti_upd;