[Home] [Help]
172:
173: l_addr_rec csr_address%rowtype;
174:
175: BEGIN
176: hr_utility.set_location('Entering : '||l_proc,111);
177: l_arch := true;
178:
179: open csr_address;
180: fetch csr_address into l_addr_rec;
233: l_addr_rec.post_code:= ' ';
234: end if;
235:
236: --Trace Messages
237: hr_utility.trace('Address Line 1 : '||l_address_line1);
238: hr_utility.trace('Address Line 2 : '||l_address_line2);
239: hr_utility.trace('Address Line 3 : '||l_address_line3);
240: hr_utility.trace('Address Line 4 : '||l_address_line4);
241:
234: end if;
235:
236: --Trace Messages
237: hr_utility.trace('Address Line 1 : '||l_address_line1);
238: hr_utility.trace('Address Line 2 : '||l_address_line2);
239: hr_utility.trace('Address Line 3 : '||l_address_line3);
240: hr_utility.trace('Address Line 4 : '||l_address_line4);
241:
242: p_addr_rec.assignment_id := p_assignment_id;
235:
236: --Trace Messages
237: hr_utility.trace('Address Line 1 : '||l_address_line1);
238: hr_utility.trace('Address Line 2 : '||l_address_line2);
239: hr_utility.trace('Address Line 3 : '||l_address_line3);
240: hr_utility.trace('Address Line 4 : '||l_address_line4);
241:
242: p_addr_rec.assignment_id := p_assignment_id;
243: p_addr_rec.action_info_category := 'ADDRESS DETAILS';
236: --Trace Messages
237: hr_utility.trace('Address Line 1 : '||l_address_line1);
238: hr_utility.trace('Address Line 2 : '||l_address_line2);
239: hr_utility.trace('Address Line 3 : '||l_address_line3);
240: hr_utility.trace('Address Line 4 : '||l_address_line4);
241:
242: p_addr_rec.assignment_id := p_assignment_id;
243: p_addr_rec.action_info_category := 'ADDRESS DETAILS';
244: p_addr_rec.act_info5 := l_address_line1;
247: p_addr_rec.act_info8 := l_address_line4;
248: p_addr_rec.act_info12 := l_addr_rec.post_code;
249: p_addr_rec.act_info13 := l_addr_rec.country;
250:
251: hr_utility.set_location('Leaving: '||l_proc,999);
252: return l_arch;
253:
254: END fetch_address_rec;
255: --
340:
341: l_person_rec csr_person_details_active%rowtype;
342: BEGIN
343:
344: hr_utility.set_location('Entering: '||l_proc,1);
345: l_arch := true;
346:
347: open csr_person_details_active;
348: fetch csr_person_details_active into l_person_rec;
359: close csr_person_details_terminated;
360: end if;
361: close csr_person_details_active;
362:
363: hr_utility.trace('Termination date ' ||l_person_rec.actual_termination_date);
364: hr_utility.trace('Start date ' ||l_person_rec.effective_start_date);
365: hr_utility.trace('Current employee flag' ||l_person_rec.current_employee_flag);
366:
367: p_person_rec.person_id := l_person_rec.p_person_id;
360: end if;
361: close csr_person_details_active;
362:
363: hr_utility.trace('Termination date ' ||l_person_rec.actual_termination_date);
364: hr_utility.trace('Start date ' ||l_person_rec.effective_start_date);
365: hr_utility.trace('Current employee flag' ||l_person_rec.current_employee_flag);
366:
367: p_person_rec.person_id := l_person_rec.p_person_id;
368: p_person_rec.assignment_id := l_person_rec.assignment_id;
361: close csr_person_details_active;
362:
363: hr_utility.trace('Termination date ' ||l_person_rec.actual_termination_date);
364: hr_utility.trace('Start date ' ||l_person_rec.effective_start_date);
365: hr_utility.trace('Current employee flag' ||l_person_rec.current_employee_flag);
366:
367: p_person_rec.person_id := l_person_rec.p_person_id;
368: p_person_rec.assignment_id := l_person_rec.assignment_id;
369: p_person_rec.action_info_category := 'GB RTI EMPLOYEE DETAILS';
379: p_person_rec.act_info17 := l_person_rec.emp_no;
380: p_person_rec.act_info18 := l_person_rec.current_employee_flag;
381: p_person_rec.act_info19 := to_char(l_person_rec.actual_termination_date,'YYYY-MM-DD');
382:
383: hr_utility.set_location('Leaving: '||l_proc,999);
384: return l_arch;
385: END fetch_person_rec;
386: --
387:
425:
426: -- Source 2
427: -- Configuration values for expat_indicator
428: if l_expat_indicator is null then
429: hr_utility.trace('in config ');
430:
431: -- Use below API to get the config values for the given context in the business group
432: PQP_UTILITIES.get_config_type_values( p_configuration_type => 'PAY_GB_FPS_EXPAT_INDICATOR' ,
433: p_business_group_id => p_business_group_id ,
439: l_context_name := l_config_values(l_config_values.FIRST).pcv_information4;
440: l_column_name := l_config_values(l_config_values.FIRST).pcv_information5;
441: l_function_name := l_config_values(l_config_values.FIRST).pcv_information6;
442:
443: hr_utility.trace('l_source_name for expat_indicator: ' || l_source_name);
444: hr_utility.trace('l_context_name for expat_indicator: ' || l_context_name);
445: hr_utility.trace('l_column_name for expat_indicator: ' || l_column_name);
446: hr_utility.trace('l_function_name for expat_indicator: ' || l_function_name);
447:
440: l_column_name := l_config_values(l_config_values.FIRST).pcv_information5;
441: l_function_name := l_config_values(l_config_values.FIRST).pcv_information6;
442:
443: hr_utility.trace('l_source_name for expat_indicator: ' || l_source_name);
444: hr_utility.trace('l_context_name for expat_indicator: ' || l_context_name);
445: hr_utility.trace('l_column_name for expat_indicator: ' || l_column_name);
446: hr_utility.trace('l_function_name for expat_indicator: ' || l_function_name);
447:
448: /**
441: l_function_name := l_config_values(l_config_values.FIRST).pcv_information6;
442:
443: hr_utility.trace('l_source_name for expat_indicator: ' || l_source_name);
444: hr_utility.trace('l_context_name for expat_indicator: ' || l_context_name);
445: hr_utility.trace('l_column_name for expat_indicator: ' || l_column_name);
446: hr_utility.trace('l_function_name for expat_indicator: ' || l_function_name);
447:
448: /**
449:
442:
443: hr_utility.trace('l_source_name for expat_indicator: ' || l_source_name);
444: hr_utility.trace('l_context_name for expat_indicator: ' || l_context_name);
445: hr_utility.trace('l_column_name for expat_indicator: ' || l_column_name);
446: hr_utility.trace('l_function_name for expat_indicator: ' || l_function_name);
447:
448: /**
449:
450: - Assignment ==> Extra details
455: l_sqlstr := 'select ' || l_column_name ||
456: ' from per_assignment_extra_info where assignment_id = :assignment_id and AEI_INFORMATION_CATEGORY = :l_context_name';
457: begin
458:
459: hr_utility.trace('string '||l_sqlstr);
460: EXECUTE immediate l_sqlstr INTO l_expat_indicator USING p_assignment_id,l_context_name;
461:
462: exception
463: WHEN OTHERS THEN
460: EXECUTE immediate l_sqlstr INTO l_expat_indicator USING p_assignment_id,l_context_name;
461:
462: exception
463: WHEN OTHERS THEN
464: hr_utility.trace('Exception in expat_indicator.Assignment Developer DFF.');
465: hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
466: return null;
467: end;
468:
461:
462: exception
463: WHEN OTHERS THEN
464: hr_utility.trace('Exception in expat_indicator.Assignment Developer DFF.');
465: hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
466: return null;
467: end;
468:
469: elsif l_source_name = 'PER_ASSIGNMENTS' THEN
475: EXECUTE immediate l_sqlstr INTO l_expat_indicator USING p_assignment_id,l_context_name;
476:
477: exception
478: WHEN OTHERS THEN
479: hr_utility.trace('Exception in expat_indicator.PER_ASSIGNMENTS.');
480: hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
481: return null;
482: end;
483:
476:
477: exception
478: WHEN OTHERS THEN
479: hr_utility.trace('Exception in expat_indicator.PER_ASSIGNMENTS.');
480: hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
481: return null;
482: end;
483:
484: elsif l_source_name = 'Function' THEN
490: WHERE name = l_function_name
491: and business_group_id = p_business_group_id;
492:
493:
494: hr_utility.trace('l_package'||l_package);
495:
496: l_sqlstr :=
497: 'SELECT '
498: || l_package
499: || ' (:p_assignment_id,:p_eff_date) '
500: || 'FROM DUAL';
501: EXECUTE IMMEDIATE l_sqlstr into l_expat_indicator USING p_assignment_id,p_effective_start_date;
502:
503: hr_utility.trace('Function value l_expat_indicator '||l_expat_indicator);
504:
505: exception
506: WHEN OTHERS THEN
507: hr_utility.trace('Exception in expat_indicator.Function.');
503: hr_utility.trace('Function value l_expat_indicator '||l_expat_indicator);
504:
505: exception
506: WHEN OTHERS THEN
507: hr_utility.trace('Exception in expat_indicator.Function.');
508: hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
509: return null;
510: end;
511:
504:
505: exception
506: WHEN OTHERS THEN
507: hr_utility.trace('Exception in expat_indicator.Function.');
508: hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
509: return null;
510: end;
511:
512: END IF;
513:
514: END IF;
515: END IF;--source 2 end
516:
517: hr_utility.trace(' l_expat_indicator : ' || l_expat_indicator);
518: hr_utility.trace('Leaving get_expat_indicator');
519:
520: return l_expat_indicator;
521:
514: END IF;
515: END IF;--source 2 end
516:
517: hr_utility.trace(' l_expat_indicator : ' || l_expat_indicator);
518: hr_utility.trace('Leaving get_expat_indicator');
519:
520: return l_expat_indicator;
521:
522: EXCEPTION
520: return l_expat_indicator;
521:
522: EXCEPTION
523: WHEN OTHERS THEN
524: hr_utility.trace('Exception in get_expat_indicator end of function block.');
525: hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
526: fnd_file.put_line(fnd_file.LOG,'Exception:' || SQLCODE || ' - ' || SQLERRM);
527: return null;
528: END get_expat_indicator;
521:
522: EXCEPTION
523: WHEN OTHERS THEN
524: hr_utility.trace('Exception in get_expat_indicator end of function block.');
525: hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
526: fnd_file.put_line(fnd_file.LOG,'Exception:' || SQLCODE || ' - ' || SQLERRM);
527: return null;
528: END get_expat_indicator;
529:
568:
569: -- Source 2
570: -- Configuration values for occ_pension_indicator
571: if l_occ_pension_indicator is null then
572: hr_utility.trace('in config ');
573:
574: -- Use below API to get the config values for the given context in the business group
575: PQP_UTILITIES.get_config_type_values( p_configuration_type => 'PAY_GB_FPS_OCC_PENSION_IND' ,
576: p_business_group_id => p_business_group_id ,
582: l_context_name := l_config_values(l_config_values.FIRST).pcv_information4;
583: l_column_name := l_config_values(l_config_values.FIRST).pcv_information5;
584: l_function_name := l_config_values(l_config_values.FIRST).pcv_information6;
585:
586: hr_utility.trace('l_source_name for occ_pension_indicator: ' || l_source_name);
587: hr_utility.trace('l_context_name for occ_pension_indicator: ' || l_context_name);
588: hr_utility.trace('l_column_name for occ_pension_indicator: ' || l_column_name);
589: hr_utility.trace('l_function_name for occ_pension_indicator: ' || l_function_name);
590:
583: l_column_name := l_config_values(l_config_values.FIRST).pcv_information5;
584: l_function_name := l_config_values(l_config_values.FIRST).pcv_information6;
585:
586: hr_utility.trace('l_source_name for occ_pension_indicator: ' || l_source_name);
587: hr_utility.trace('l_context_name for occ_pension_indicator: ' || l_context_name);
588: hr_utility.trace('l_column_name for occ_pension_indicator: ' || l_column_name);
589: hr_utility.trace('l_function_name for occ_pension_indicator: ' || l_function_name);
590:
591: /**
584: l_function_name := l_config_values(l_config_values.FIRST).pcv_information6;
585:
586: hr_utility.trace('l_source_name for occ_pension_indicator: ' || l_source_name);
587: hr_utility.trace('l_context_name for occ_pension_indicator: ' || l_context_name);
588: hr_utility.trace('l_column_name for occ_pension_indicator: ' || l_column_name);
589: hr_utility.trace('l_function_name for occ_pension_indicator: ' || l_function_name);
590:
591: /**
592:
585:
586: hr_utility.trace('l_source_name for occ_pension_indicator: ' || l_source_name);
587: hr_utility.trace('l_context_name for occ_pension_indicator: ' || l_context_name);
588: hr_utility.trace('l_column_name for occ_pension_indicator: ' || l_column_name);
589: hr_utility.trace('l_function_name for occ_pension_indicator: ' || l_function_name);
590:
591: /**
592:
593: - Assignment ==> Extra details
598: l_sqlstr := 'select ' || l_column_name ||
599: ' from per_assignment_extra_info where assignment_id = :assignment_id and AEI_INFORMATION_CATEGORY = :l_context_name';
600: begin
601:
602: hr_utility.trace('string '||l_sqlstr);
603: EXECUTE immediate l_sqlstr INTO l_occ_pension_indicator USING p_assignment_id,l_context_name;
604:
605: exception
606: WHEN OTHERS THEN
603: EXECUTE immediate l_sqlstr INTO l_occ_pension_indicator USING p_assignment_id,l_context_name;
604:
605: exception
606: WHEN OTHERS THEN
607: hr_utility.trace('Exception in occ_pension_indicator.Assignment Developer DFF.');
608: hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
609: return null;
610: end;
611:
604:
605: exception
606: WHEN OTHERS THEN
607: hr_utility.trace('Exception in occ_pension_indicator.Assignment Developer DFF.');
608: hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
609: return null;
610: end;
611:
612: elsif l_source_name = 'PER_ASSIGNMENTS' THEN
618: EXECUTE immediate l_sqlstr INTO l_occ_pension_indicator USING p_assignment_id,l_context_name;
619:
620: exception
621: WHEN OTHERS THEN
622: hr_utility.trace('Exception in occ_pension_indicator.PER_ASSIGNMENTS.');
623: hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
624: return null;
625: end;
626:
619:
620: exception
621: WHEN OTHERS THEN
622: hr_utility.trace('Exception in occ_pension_indicator.PER_ASSIGNMENTS.');
623: hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
624: return null;
625: end;
626:
627: elsif l_source_name = 'Function' THEN
633: WHERE name = l_function_name
634: and business_group_id = p_business_group_id;
635:
636:
637: hr_utility.trace('l_package'||l_package);
638:
639: l_sqlstr :=
640: 'SELECT '
641: || l_package
642: || ' (:p_assignment_id,:p_eff_date) '
643: || 'FROM DUAL';
644: EXECUTE IMMEDIATE l_sqlstr into l_occ_pension_indicator USING p_assignment_id,p_effective_start_date;
645:
646: hr_utility.trace('Function value l_occ_pension_indicator '||l_occ_pension_indicator);
647:
648: exception
649: WHEN OTHERS THEN
650: hr_utility.trace('Exception in occ_pension_indicator.Function.');
646: hr_utility.trace('Function value l_occ_pension_indicator '||l_occ_pension_indicator);
647:
648: exception
649: WHEN OTHERS THEN
650: hr_utility.trace('Exception in occ_pension_indicator.Function.');
651: hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
652: return null;
653: end;
654:
647:
648: exception
649: WHEN OTHERS THEN
650: hr_utility.trace('Exception in occ_pension_indicator.Function.');
651: hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
652: return null;
653: end;
654:
655: END IF;
656:
657: END IF;
658: END IF;--source 2 end
659:
660: hr_utility.trace(' l_occ_pension_indicator : ' || l_occ_pension_indicator);
661: hr_utility.trace('Leaving get_occ_pension_indicator');
662:
663: return l_occ_pension_indicator;
664:
657: END IF;
658: END IF;--source 2 end
659:
660: hr_utility.trace(' l_occ_pension_indicator : ' || l_occ_pension_indicator);
661: hr_utility.trace('Leaving get_occ_pension_indicator');
662:
663: return l_occ_pension_indicator;
664:
665: EXCEPTION
663: return l_occ_pension_indicator;
664:
665: EXCEPTION
666: WHEN OTHERS THEN
667: hr_utility.trace('Exception in get_occ_pension_indicator end of function block.');
668: hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
669: fnd_file.put_line(fnd_file.LOG,'Exception:' || SQLCODE || ' - ' || SQLERRM);
670: return null;
671: END get_occ_pension_indicator;
664:
665: EXCEPTION
666: WHEN OTHERS THEN
667: hr_utility.trace('Exception in get_occ_pension_indicator end of function block.');
668: hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
669: fnd_file.put_line(fnd_file.LOG,'Exception:' || SQLCODE || ' - ' || SQLERRM);
670: return null;
671: END get_occ_pension_indicator;
672:
710:
711: -- Source 2
712: -- Configuration values for pay_non_indiv_ind
713: if l_pay_non_indiv_ind is null then
714: hr_utility.trace('in config ');
715:
716: -- Use below API to get the config values for the given context in the business group
717: PQP_UTILITIES.get_config_type_values( p_configuration_type => 'PAY_GB_FPS_PAY_NON_INDIV_IND' ,
718: p_business_group_id => p_business_group_id ,
724: l_context_name := l_config_values(l_config_values.FIRST).pcv_information4;
725: l_column_name := l_config_values(l_config_values.FIRST).pcv_information5;
726: l_function_name := l_config_values(l_config_values.FIRST).pcv_information6;
727:
728: hr_utility.trace('l_source_name for pay_non_indiv_ind: ' || l_source_name);
729: hr_utility.trace('l_context_name for pay_non_indiv_ind: ' || l_context_name);
730: hr_utility.trace('l_column_name for pay_non_indiv_ind: ' || l_column_name);
731: hr_utility.trace('l_function_name for pay_non_indiv_ind: ' || l_function_name);
732:
725: l_column_name := l_config_values(l_config_values.FIRST).pcv_information5;
726: l_function_name := l_config_values(l_config_values.FIRST).pcv_information6;
727:
728: hr_utility.trace('l_source_name for pay_non_indiv_ind: ' || l_source_name);
729: hr_utility.trace('l_context_name for pay_non_indiv_ind: ' || l_context_name);
730: hr_utility.trace('l_column_name for pay_non_indiv_ind: ' || l_column_name);
731: hr_utility.trace('l_function_name for pay_non_indiv_ind: ' || l_function_name);
732:
733: /**
726: l_function_name := l_config_values(l_config_values.FIRST).pcv_information6;
727:
728: hr_utility.trace('l_source_name for pay_non_indiv_ind: ' || l_source_name);
729: hr_utility.trace('l_context_name for pay_non_indiv_ind: ' || l_context_name);
730: hr_utility.trace('l_column_name for pay_non_indiv_ind: ' || l_column_name);
731: hr_utility.trace('l_function_name for pay_non_indiv_ind: ' || l_function_name);
732:
733: /**
734:
727:
728: hr_utility.trace('l_source_name for pay_non_indiv_ind: ' || l_source_name);
729: hr_utility.trace('l_context_name for pay_non_indiv_ind: ' || l_context_name);
730: hr_utility.trace('l_column_name for pay_non_indiv_ind: ' || l_column_name);
731: hr_utility.trace('l_function_name for pay_non_indiv_ind: ' || l_function_name);
732:
733: /**
734:
735: - Assignment ==> Extra details
740: l_sqlstr := 'select ' || l_column_name ||
741: ' from per_assignment_extra_info where assignment_id = :assignment_id and AEI_INFORMATION_CATEGORY = :l_context_name';
742: begin
743:
744: hr_utility.trace('string '||l_sqlstr);
745: EXECUTE immediate l_sqlstr INTO l_pay_non_indiv_ind USING p_assignment_id,l_context_name;
746:
747: exception
748: WHEN OTHERS THEN
745: EXECUTE immediate l_sqlstr INTO l_pay_non_indiv_ind USING p_assignment_id,l_context_name;
746:
747: exception
748: WHEN OTHERS THEN
749: hr_utility.trace('Exception in pay_non_indiv_ind.Assignment Developer DFF.');
750: hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
751: return null;
752: end;
753:
746:
747: exception
748: WHEN OTHERS THEN
749: hr_utility.trace('Exception in pay_non_indiv_ind.Assignment Developer DFF.');
750: hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
751: return null;
752: end;
753:
754: elsif l_source_name = 'PER_ASSIGNMENTS' THEN
761: EXECUTE immediate l_sqlstr INTO l_pay_non_indiv_ind USING p_assignment_id,l_context_name;
762:
763: exception
764: WHEN OTHERS THEN
765: hr_utility.trace('Exception in pay_non_indiv_ind.PER_ASSIGNMENTS.');
766: hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
767: return null;
768: end;
769:
762:
763: exception
764: WHEN OTHERS THEN
765: hr_utility.trace('Exception in pay_non_indiv_ind.PER_ASSIGNMENTS.');
766: hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
767: return null;
768: end;
769:
770: elsif l_source_name = 'Function' THEN
776: WHERE name = l_function_name
777: and business_group_id = p_business_group_id;
778:
779:
780: hr_utility.trace('l_package'||l_package);
781:
782: l_sqlstr :=
783: 'SELECT '
784: || l_package
785: || ' (:p_assignment_id,:p_eff_date) '
786: || 'FROM DUAL';
787: EXECUTE IMMEDIATE l_sqlstr into l_pay_non_indiv_ind USING p_assignment_id,p_effective_start_date;
788:
789: hr_utility.trace('Function value l_pay_non_indiv_ind '||l_pay_non_indiv_ind);
790:
791: exception
792: WHEN OTHERS THEN
793: hr_utility.trace('Exception in pay_non_indiv_ind.Function.');
789: hr_utility.trace('Function value l_pay_non_indiv_ind '||l_pay_non_indiv_ind);
790:
791: exception
792: WHEN OTHERS THEN
793: hr_utility.trace('Exception in pay_non_indiv_ind.Function.');
794: hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
795: return null;
796: end;
797:
790:
791: exception
792: WHEN OTHERS THEN
793: hr_utility.trace('Exception in pay_non_indiv_ind.Function.');
794: hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
795: return null;
796: end;
797:
798: END IF;
799:
800: END IF;
801: END IF;--source 2 end
802:
803: hr_utility.trace(' l_pay_non_indiv_ind : ' || l_pay_non_indiv_ind);
804: hr_utility.trace('Leaving get_pay_non_indiv_ind');
805:
806: return l_pay_non_indiv_ind;
807:
800: END IF;
801: END IF;--source 2 end
802:
803: hr_utility.trace(' l_pay_non_indiv_ind : ' || l_pay_non_indiv_ind);
804: hr_utility.trace('Leaving get_pay_non_indiv_ind');
805:
806: return l_pay_non_indiv_ind;
807:
808: EXCEPTION
806: return l_pay_non_indiv_ind;
807:
808: EXCEPTION
809: WHEN OTHERS THEN
810: hr_utility.trace('Exception in get_pay_non_indiv_ind end of function block.');
811: hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
812: fnd_file.put_line(fnd_file.LOG,'Exception:' || SQLCODE || ' - ' || SQLERRM);
813: return null;
814: END get_pay_non_indiv_ind;
807:
808: EXCEPTION
809: WHEN OTHERS THEN
810: hr_utility.trace('Exception in get_pay_non_indiv_ind end of function block.');
811: hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
812: fnd_file.put_line(fnd_file.LOG,'Exception:' || SQLCODE || ' - ' || SQLERRM);
813: return null;
814: END get_pay_non_indiv_ind;
815:
971: and p_eff_date between papf.effective_start_date and papf.effective_end_date;
972:
973:
974: BEGIN
975: hr_utility.set_location('Entering: '||l_proc,1);
976: l_arch := true;
977:
978: SELECT distinct element_type_id
979: into g_paye_details_id
1008:
1009: -- To fetch Terminated Employee Details
1010: if p_person_rec.act_info18 is null or p_person_rec.act_info18 <> 'Y' then
1011: l_effective_date := least(l_eff_date,(to_date(substr(p_person_rec.act_info19,1,10),'YYYY-MM-DD')));
1012: hr_utility.trace('Effective date : '||l_eff_date||' Termination Date : '||p_person_rec.act_info19);
1013: hr_utility.trace('l_effective_date : '||l_effective_date);
1014: else
1015: l_effective_date := least (p_effective_date, l_final_eff_end_date);
1016: hr_utility.trace('l_effective_date : '||l_effective_date);
1009: -- To fetch Terminated Employee Details
1010: if p_person_rec.act_info18 is null or p_person_rec.act_info18 <> 'Y' then
1011: l_effective_date := least(l_eff_date,(to_date(substr(p_person_rec.act_info19,1,10),'YYYY-MM-DD')));
1012: hr_utility.trace('Effective date : '||l_eff_date||' Termination Date : '||p_person_rec.act_info19);
1013: hr_utility.trace('l_effective_date : '||l_effective_date);
1014: else
1015: l_effective_date := least (p_effective_date, l_final_eff_end_date);
1016: hr_utility.trace('l_effective_date : '||l_effective_date);
1017: end if;
1012: hr_utility.trace('Effective date : '||l_eff_date||' Termination Date : '||p_person_rec.act_info19);
1013: hr_utility.trace('l_effective_date : '||l_effective_date);
1014: else
1015: l_effective_date := least (p_effective_date, l_final_eff_end_date);
1016: hr_utility.trace('l_effective_date : '||l_effective_date);
1017: end if;
1018:
1019: open csr_get_asg_no;
1020: fetch csr_get_asg_no into l_asg_no,l_effective_start_date;
1037: end if;
1038:
1039:
1040: -- Trace Messages
1041: hr_utility.set_location('Asg id : '||p_person_rec.assignment_id,20);
1042: hr_utility.set_location('Asg no : '||l_asg_no,20);
1043: hr_utility.set_location('Tax code : '||l_tax_screen_value,20);
1044: hr_utility.set_location('Tax basis : '||l_taxbasis_screen_value,20);
1045: hr_utility.set_location('Start Date: '||l_final_eff_start_date,20);
1038:
1039:
1040: -- Trace Messages
1041: hr_utility.set_location('Asg id : '||p_person_rec.assignment_id,20);
1042: hr_utility.set_location('Asg no : '||l_asg_no,20);
1043: hr_utility.set_location('Tax code : '||l_tax_screen_value,20);
1044: hr_utility.set_location('Tax basis : '||l_taxbasis_screen_value,20);
1045: hr_utility.set_location('Start Date: '||l_final_eff_start_date,20);
1046: hr_utility.set_location('End Date : '||l_final_eff_end_date,20);
1039:
1040: -- Trace Messages
1041: hr_utility.set_location('Asg id : '||p_person_rec.assignment_id,20);
1042: hr_utility.set_location('Asg no : '||l_asg_no,20);
1043: hr_utility.set_location('Tax code : '||l_tax_screen_value,20);
1044: hr_utility.set_location('Tax basis : '||l_taxbasis_screen_value,20);
1045: hr_utility.set_location('Start Date: '||l_final_eff_start_date,20);
1046: hr_utility.set_location('End Date : '||l_final_eff_end_date,20);
1047:
1040: -- Trace Messages
1041: hr_utility.set_location('Asg id : '||p_person_rec.assignment_id,20);
1042: hr_utility.set_location('Asg no : '||l_asg_no,20);
1043: hr_utility.set_location('Tax code : '||l_tax_screen_value,20);
1044: hr_utility.set_location('Tax basis : '||l_taxbasis_screen_value,20);
1045: hr_utility.set_location('Start Date: '||l_final_eff_start_date,20);
1046: hr_utility.set_location('End Date : '||l_final_eff_end_date,20);
1047:
1048: -- If tax basis is non cumulative('N') then making it as 'Y' else ' '
1041: hr_utility.set_location('Asg id : '||p_person_rec.assignment_id,20);
1042: hr_utility.set_location('Asg no : '||l_asg_no,20);
1043: hr_utility.set_location('Tax code : '||l_tax_screen_value,20);
1044: hr_utility.set_location('Tax basis : '||l_taxbasis_screen_value,20);
1045: hr_utility.set_location('Start Date: '||l_final_eff_start_date,20);
1046: hr_utility.set_location('End Date : '||l_final_eff_end_date,20);
1047:
1048: -- If tax basis is non cumulative('N') then making it as 'Y' else ' '
1049: if l_taxbasis_screen_value = 'N' then
1042: hr_utility.set_location('Asg no : '||l_asg_no,20);
1043: hr_utility.set_location('Tax code : '||l_tax_screen_value,20);
1044: hr_utility.set_location('Tax basis : '||l_taxbasis_screen_value,20);
1045: hr_utility.set_location('Start Date: '||l_final_eff_start_date,20);
1046: hr_utility.set_location('End Date : '||l_final_eff_end_date,20);
1047:
1048: -- If tax basis is non cumulative('N') then making it as 'Y' else ' '
1049: if l_taxbasis_screen_value = 'N' then
1050: l_taxbasis_screen_value := 'Y';
1087: p_eas_rec.act_info11 := l_occ_pension_indicator;
1088: p_eas_rec.act_info12 := l_pay_non_indiv_ind;
1089:
1090:
1091: hr_utility.set_location('Leaving: '||l_proc,999);
1092: return l_arch;
1093: END fetch_eas_asg_rec;
1094: --
1095: --
1139:
1140: COMMIT;
1141: EXCEPTION
1142: WHEN OTHERS THEN
1143: hr_utility.trace('Error occured in populate_run_msg');
1144: RAISE;
1145: END populate_run_msg;
1146:
1147: -- Function to validate assignment details for EAS report
1173: l_occ_pension_indicator varchar2(100);
1174: l_pay_non_indiv_ind varchar2(100);
1175:
1176: BEGIN
1177: hr_utility.set_location('Entering: '||l_proc,1);
1178:
1179: edi_validation_fail := 'N'; -- Initialized validation fail to N
1180: l_assignment_number := p_tab_rec_data(0).act_info17;
1181: l_payroll_id := p_tab_rec_data(2).act_info3;
1189: l_occ_pension_indicator := p_tab_rec_data(2).act_info11;
1190: l_pay_non_indiv_ind := p_tab_rec_data(2).act_info12;
1191:
1192: -- Trace Messages
1193: hr_utility.set_location('Payroll Id : '||l_payroll_id,10);
1194: hr_utility.set_location('Process type : '||l_process_type_in_use,20);
1195: hr_utility.set_location('Tax code : '||l_tax_code_in_use,30);
1196: hr_utility.set_location('Tax basis : '||l_tax_basis_in_use,40);
1197: hr_utility.set_location('Start date : '||l_eas_start_asg_date,50);
1190: l_pay_non_indiv_ind := p_tab_rec_data(2).act_info12;
1191:
1192: -- Trace Messages
1193: hr_utility.set_location('Payroll Id : '||l_payroll_id,10);
1194: hr_utility.set_location('Process type : '||l_process_type_in_use,20);
1195: hr_utility.set_location('Tax code : '||l_tax_code_in_use,30);
1196: hr_utility.set_location('Tax basis : '||l_tax_basis_in_use,40);
1197: hr_utility.set_location('Start date : '||l_eas_start_asg_date,50);
1198: hr_utility.set_location('End date : '||l_eas_end_asg_date,60);
1191:
1192: -- Trace Messages
1193: hr_utility.set_location('Payroll Id : '||l_payroll_id,10);
1194: hr_utility.set_location('Process type : '||l_process_type_in_use,20);
1195: hr_utility.set_location('Tax code : '||l_tax_code_in_use,30);
1196: hr_utility.set_location('Tax basis : '||l_tax_basis_in_use,40);
1197: hr_utility.set_location('Start date : '||l_eas_start_asg_date,50);
1198: hr_utility.set_location('End date : '||l_eas_end_asg_date,60);
1199: hr_utility.set_location('Expat Indicator : '||l_expat_indicator,70);
1192: -- Trace Messages
1193: hr_utility.set_location('Payroll Id : '||l_payroll_id,10);
1194: hr_utility.set_location('Process type : '||l_process_type_in_use,20);
1195: hr_utility.set_location('Tax code : '||l_tax_code_in_use,30);
1196: hr_utility.set_location('Tax basis : '||l_tax_basis_in_use,40);
1197: hr_utility.set_location('Start date : '||l_eas_start_asg_date,50);
1198: hr_utility.set_location('End date : '||l_eas_end_asg_date,60);
1199: hr_utility.set_location('Expat Indicator : '||l_expat_indicator,70);
1200: hr_utility.set_location('occ pension Indicator : '||l_occ_pension_indicator,80);
1193: hr_utility.set_location('Payroll Id : '||l_payroll_id,10);
1194: hr_utility.set_location('Process type : '||l_process_type_in_use,20);
1195: hr_utility.set_location('Tax code : '||l_tax_code_in_use,30);
1196: hr_utility.set_location('Tax basis : '||l_tax_basis_in_use,40);
1197: hr_utility.set_location('Start date : '||l_eas_start_asg_date,50);
1198: hr_utility.set_location('End date : '||l_eas_end_asg_date,60);
1199: hr_utility.set_location('Expat Indicator : '||l_expat_indicator,70);
1200: hr_utility.set_location('occ pension Indicator : '||l_occ_pension_indicator,80);
1201: hr_utility.set_location('pay non indiv ind : '||l_pay_non_indiv_ind,90);
1194: hr_utility.set_location('Process type : '||l_process_type_in_use,20);
1195: hr_utility.set_location('Tax code : '||l_tax_code_in_use,30);
1196: hr_utility.set_location('Tax basis : '||l_tax_basis_in_use,40);
1197: hr_utility.set_location('Start date : '||l_eas_start_asg_date,50);
1198: hr_utility.set_location('End date : '||l_eas_end_asg_date,60);
1199: hr_utility.set_location('Expat Indicator : '||l_expat_indicator,70);
1200: hr_utility.set_location('occ pension Indicator : '||l_occ_pension_indicator,80);
1201: hr_utility.set_location('pay non indiv ind : '||l_pay_non_indiv_ind,90);
1202:
1195: hr_utility.set_location('Tax code : '||l_tax_code_in_use,30);
1196: hr_utility.set_location('Tax basis : '||l_tax_basis_in_use,40);
1197: hr_utility.set_location('Start date : '||l_eas_start_asg_date,50);
1198: hr_utility.set_location('End date : '||l_eas_end_asg_date,60);
1199: hr_utility.set_location('Expat Indicator : '||l_expat_indicator,70);
1200: hr_utility.set_location('occ pension Indicator : '||l_occ_pension_indicator,80);
1201: hr_utility.set_location('pay non indiv ind : '||l_pay_non_indiv_ind,90);
1202:
1203:
1196: hr_utility.set_location('Tax basis : '||l_tax_basis_in_use,40);
1197: hr_utility.set_location('Start date : '||l_eas_start_asg_date,50);
1198: hr_utility.set_location('End date : '||l_eas_end_asg_date,60);
1199: hr_utility.set_location('Expat Indicator : '||l_expat_indicator,70);
1200: hr_utility.set_location('occ pension Indicator : '||l_occ_pension_indicator,80);
1201: hr_utility.set_location('pay non indiv ind : '||l_pay_non_indiv_ind,90);
1202:
1203:
1204: IF ((l_payroll_id <> ' ' and l_payroll_id IS NOT NULL)
1197: hr_utility.set_location('Start date : '||l_eas_start_asg_date,50);
1198: hr_utility.set_location('End date : '||l_eas_end_asg_date,60);
1199: hr_utility.set_location('Expat Indicator : '||l_expat_indicator,70);
1200: hr_utility.set_location('occ pension Indicator : '||l_occ_pension_indicator,80);
1201: hr_utility.set_location('pay non indiv ind : '||l_pay_non_indiv_ind,90);
1202:
1203:
1204: IF ((l_payroll_id <> ' ' and l_payroll_id IS NOT NULL)
1205: and validate_input(l_payroll_id,'PAYROLLID') > 0) THEN
1229: CLOSE get_effective_date;
1230:
1231:
1232: l_eff_date := trunc (l_eff_date);
1233: hr_utility.trace('l_eff_date = '|| l_eff_date);
1234:
1235: l_msg_value := pay_gb_eoy_magtape.validate_tax_code_yrfil(p_assactid,l_tax_code_in_use,l_eff_date); -- Tax code validation
1236:
1237: /* EAS 2013 Changes */
1287: fnd_file.put_line (fnd_file.LOG,'Tax Basis ' ||l_tax_basis_in_use ||' for assignment ' || l_assignment_number||' is invalid.');
1288: edi_validation_fail := 'Y';
1289: END IF;
1290:
1291: hr_utility.set_location('Leaving: '||l_proc,111);
1292:
1293: END eas_asg_etext_validations;
1294: --
1295: -- Person Address validations
1343:
1344:
1345:
1346: BEGIN
1347: hr_utility.set_location('Entering '||l_proc,10);
1348:
1349: l_count_char_errors := 0;
1350: l_count_missing_val := 0;
1351:
1524: fnd_file.put_line (fnd_file.LOG,'The Date of Birth of the assignment '|| l_assignment_number || ' is invalid.');
1525: edi_validation_fail := 'Y';
1526: END IF;
1527:
1528: hr_utility.set_location('Leaving'||l_proc,10);
1529: END person_addr_validations;
1530: --
1531:
1532: -- Person Address validations
1574: l_year1 number;
1575: l_year2 number;
1576:
1577: BEGIN
1578: hr_utility.set_location('Entering '||l_proc,10);
1579:
1580: l_count_char_errors := 0;
1581: l_count_missing_val := 0;
1582:
1737: fnd_file.put_line (fnd_file.LOG,'The Date of Birth of the employee '|| l_employee_number || ' is invalid.');
1738: edi_validation_fail := 'Y';
1739: END IF;
1740:
1741: hr_utility.set_location('Leaving'||l_proc,10);
1742: END person_addr_validations_nino;
1743: --
1744:
1745: --
1764: l_translated_value varchar2(200); -- Required to output failing char.
1765: --
1766: BEGIN
1767: --
1768: hr_utility.trace('Entering validate_input');
1769: hr_utility.trace('p_validate_mode='||p_validate_mode);
1770: hr_utility.trace('p_input_value='||p_input_value);
1771: --
1772:
1765: --
1766: BEGIN
1767: --
1768: hr_utility.trace('Entering validate_input');
1769: hr_utility.trace('p_validate_mode='||p_validate_mode);
1770: hr_utility.trace('p_input_value='||p_input_value);
1771: --
1772:
1773: if p_validate_mode = 'EMP_NAME' then
1766: BEGIN
1767: --
1768: hr_utility.trace('Entering validate_input');
1769: hr_utility.trace('p_validate_mode='||p_validate_mode);
1770: hr_utility.trace('p_input_value='||p_input_value);
1771: --
1772:
1773: if p_validate_mode = 'EMP_NAME' then
1774: if ( substr(p_input_value,1,1) =' ') then
1771: --
1772:
1773: if p_validate_mode = 'EMP_NAME' then
1774: if ( substr(p_input_value,1,1) =' ') then
1775: hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
1776: l_valid := 2;
1777: else
1778: l_translated_value :=
1779: translate(p_input_value,
1779: translate(p_input_value,
1780: l_invalid_char||l_mix_chars||l_number_chk||l_char_set_A||l_space,l_invalid_char);
1781:
1782: if l_translated_value is not null then
1783: hr_utility.trace('Invalid chars found: '||l_translated_value);
1784: l_valid := 1; -- Not valid
1785: else
1786: l_valid := 0; -- Valid
1787: end if;
1795: translate(p_input_value,
1796: l_invalid_char||l_number_chk,l_invalid_char);
1797:
1798: if l_translated_value is not null then
1799: hr_utility.trace('Invalid chars found: '||l_translated_value);
1800: l_valid := 1; -- Not valid
1801: else
1802: l_valid := 0; -- Valid
1803: end if;
1806: --
1807: -- Check for Valid First Char
1808: --
1809: if ( substr(p_input_value,1,1) =' ') then
1810: hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
1811: l_valid := 2;
1812: else
1813: l_translated_value :=
1814: translate(p_input_value,
1814: translate(p_input_value,
1815: l_invalid_char||l_char_chk||l_number_chk||l_char_set_B||l_space,l_invalid_char);
1816:
1817: if l_translated_value is not null then
1818: hr_utility.trace('Invalid chars found: '||l_translated_value);
1819: l_valid := 1; -- Not valid
1820: else
1821: l_valid := 0; -- Valid
1822: end if;
1827: -- Check for Valid First Char
1828: --
1829: if not (substr(p_input_value,1,1) between 'A' and 'Z'
1830: or substr(p_input_value,1,1) between 'a' and 'z') then
1831: hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
1832: l_valid := 2;
1833: else
1834: l_translated_value :=
1835: translate(p_input_value,
1835: translate(p_input_value,
1836: l_invalid_char||l_mix_chars||l_char_set_D||l_space,l_invalid_char);
1837:
1838: if l_translated_value is not null then
1839: hr_utility.trace('Invalid chars found: '||l_translated_value);
1840: l_valid := 1; -- Not valid
1841: else
1842: l_valid := 0; -- Valid
1843: end if;
1848: -- Check for Valid First Char
1849: --
1850: if not (substr(p_input_value,1,1) between 'A' and 'Z'
1851: or substr(p_input_value,1,1) between 'a' and 'z') then
1852: hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
1853: l_valid := 2;
1854: else
1855: l_translated_value :=
1856: translate(p_input_value,
1856: translate(p_input_value,
1857: l_invalid_char||l_mix_chars||l_char_set_C||l_space,l_invalid_char);
1858:
1859: if l_translated_value is not null then
1860: hr_utility.trace('Invalid chars found: '||l_translated_value);
1861: l_valid := 1; -- Not valid
1862: else
1863: l_valid := 0; -- Valid
1864: end if;
1870: --
1871: if not (substr(p_input_value,1,1) between 'A' and 'Z'
1872: or substr(p_input_value,1,1) between 'a' and 'z' or substr(p_input_value,1,1) between '0' and '9') then
1873: -- First char invalid
1874: hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
1875: l_valid := 2;
1876: else
1877: l_translated_value :=
1878: translate(p_input_value,
1878: translate(p_input_value,
1879: l_invalid_char||l_mix_chars||l_number_chk||l_space||l_char_set_B,l_invalid_char);
1880:
1881: if l_translated_value is not null then
1882: hr_utility.trace('Invalid chars found: '||l_translated_value);
1883: l_valid := 1; --Invalid
1884: else
1885: l_valid := 0; -- Valid
1886: end if;
1888:
1889: elsif p_validate_mode = 'COUNTRY' then
1890: if not (substr(p_input_value,1,1) between 'A' and 'Z' or
1891: substr(p_input_value,1,1) between 'a' and 'z' ) then
1892: hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
1893: l_valid := 2;
1894: else
1895: l_translated_value :=
1896: translate(p_input_value,
1896: translate(p_input_value,
1897: l_invalid_char||l_mix_chars||l_number_chk||l_space||l_char_set_B,l_invalid_char);
1898:
1899: if l_translated_value is not null then
1900: hr_utility.trace('Invalid chars found: '||l_translated_value);
1901: l_valid := 1; -- Not valid
1902: else
1903: l_valid := 0; -- Valid
1904: end if;
1909:
1910: if not (substr(p_input_value,1,1) between 'A' and 'Z'
1911: or substr(p_input_value,1,1) between 'a' and 'z' or substr(p_input_value,1,1) between '0' and '9') then
1912: -- First char invalid
1913: hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
1914: l_valid := 2;
1915: else
1916: l_translated_value := translate(p_input_value,
1917: l_invalid_char||l_mix_chars||l_number_chk||l_space,l_invalid_char);
1916: l_translated_value := translate(p_input_value,
1917: l_invalid_char||l_mix_chars||l_number_chk||l_space,l_invalid_char);
1918: --
1919: if l_translated_value is not null then
1920: hr_utility.trace('Invalid chars found: '||l_translated_value);
1921: l_valid := 1; -- Not valid
1922: else
1923: l_valid := 0; -- Valid
1924: end if;
1930:
1931: if not (substr(p_input_value,1,1) between 'A' and 'Z'
1932: or substr(p_input_value,1,1) between 'a' and 'z' or substr(p_input_value,1,1) between '0' and '9') then
1933: -- First char invalid
1934: hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
1935: l_valid := 2;
1936: else
1937: l_translated_value := translate(p_input_value,
1938: l_invalid_char||l_mix_chars||l_number_chk||l_space,l_invalid_char);
1937: l_translated_value := translate(p_input_value,
1938: l_invalid_char||l_mix_chars||l_number_chk||l_space,l_invalid_char);
1939: --
1940: if l_translated_value is not null then
1941: hr_utility.trace('Invalid chars found: '||l_translated_value);
1942: l_valid := 1; -- Not valid
1943: else
1944: l_valid := 0; -- Valid
1945: end if;
1946: end if;
1947:
1948: elsif p_validate_mode = 'PAYROLLID' then
1949: if ( substr(p_input_value,1,1) =' ') then
1950: hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
1951: l_valid := 2;
1952: else
1953: l_translated_value :=
1954: translate(p_input_value,
1954: translate(p_input_value,
1955: l_invalid_char||l_mix_chars||l_number_chk||l_char_set_B||l_space,l_invalid_char);
1956:
1957: if l_translated_value is not null then
1958: hr_utility.trace('Invalid chars found: '||l_translated_value);
1959: l_valid := 1; -- Not valid
1960: else
1961: l_valid := 0; -- Valid
1962: end if;
1963: end if ;
1964:
1965: END IF;
1966: --
1967: hr_utility.trace('Leaving validate_input');
1968: return l_valid;
1969: end validate_input;
1970:
1971: -- Function to set New Starter RTI Sent flag
1985: l_starter_flag varchar2(1);
1986: l_object_version_number number;
1987:
1988: begin
1989: hr_utility.trace('Entering set_new_starter_rti_sent');
1990:
1991: open csr_asg_extra_info;
1992: fetch csr_asg_extra_info into l_asg_extra_info_id,l_starter_flag,l_object_version_number;
1993: close csr_asg_extra_info;
2005: p_object_version_number => l_object_version_number,
2006: p_assignment_extra_info_id => l_asg_extra_info_id
2007: );
2008:
2009: hr_utility.trace('New Assignment extra info id : '||l_asg_extra_info_id);
2010:
2011:
2012: elsif l_starter_flag <> 'Y' then
2013: -- Update the flag to Yes
2018: p_aei_information_category => 'GB_RTI_ASG_DETAILS',
2019: p_aei_information8 => 'Y'
2020: );
2021:
2022: hr_utility.trace('Existing Assignment extra info id : '||l_asg_extra_info_id);
2023:
2024: end if;
2025:
2026: hr_utility.trace('Leaving set_new_starter_rti_sent');
2022: hr_utility.trace('Existing Assignment extra info id : '||l_asg_extra_info_id);
2023:
2024: end if;
2025:
2026: hr_utility.trace('Leaving set_new_starter_rti_sent');
2027:
2028: end set_new_starter_rti_sent;
2029:
2030: -- Procedure to insert data to the archive table pay_action_information
2034: l_proc CONSTANT VARCHAR2(50):= g_package||'insert_archive_row';
2035: l_ovn number;
2036: l_action_id number;
2037: BEGIN
2038: hr_utility.set_location('Entering: '||l_proc,1);
2039: if p_tab_rec_data.count > 0 then
2040: for i in p_tab_rec_data.first .. p_tab_rec_data.last loop
2041: hr_utility.trace('Defining category '|| p_tab_rec_data(i).action_info_category);
2042: hr_utility.trace('action_context_id = '|| p_assactid);
2037: BEGIN
2038: hr_utility.set_location('Entering: '||l_proc,1);
2039: if p_tab_rec_data.count > 0 then
2040: for i in p_tab_rec_data.first .. p_tab_rec_data.last loop
2041: hr_utility.trace('Defining category '|| p_tab_rec_data(i).action_info_category);
2042: hr_utility.trace('action_context_id = '|| p_assactid);
2043: if p_tab_rec_data(i).action_info_category is not null then
2044: pay_action_information_api.create_action_information(
2045: p_action_information_id => l_action_id,
2038: hr_utility.set_location('Entering: '||l_proc,1);
2039: if p_tab_rec_data.count > 0 then
2040: for i in p_tab_rec_data.first .. p_tab_rec_data.last loop
2041: hr_utility.trace('Defining category '|| p_tab_rec_data(i).action_info_category);
2042: hr_utility.trace('action_context_id = '|| p_assactid);
2043: if p_tab_rec_data(i).action_info_category is not null then
2044: pay_action_information_api.create_action_information(
2045: p_action_information_id => l_action_id,
2046: p_object_version_number => l_ovn,
2082: );
2083: end if;
2084: end loop;
2085: end if;
2086: hr_utility.set_location('Leaving: '||l_proc,999);
2087: END insert_archive_row;
2088:
2089: function check_resubmission(pactid number,tax_ref varchar2,permit varchar2,bg_id number)
2090: return varchar2 is
2166: where payroll_action_id = pactid;
2167:
2168: l_payroll_rec csr_payroll_details%rowtype;
2169: BEGIN
2170: hr_utility.set_location('Entering: '||l_proc,1);
2171:
2172: l_err := FALSE;
2173:
2174: open csr_payroll_details(pactid);
2182: l_report_type := l_payroll_rec.report_type;
2183:
2184:
2185: IF l_report_type = 'RTI_EAS_REP_13' THEN
2186: hr_utility.set_location('Calling header validations', 10);
2187:
2188: /* EAS 2013 Changes*/
2189:
2190: if l_payroll_rec.effective_date < fnd_date.canonical_to_date('2013/04/06 00:00:00') then
2187:
2188: /* EAS 2013 Changes*/
2189:
2190: if l_payroll_rec.effective_date < fnd_date.canonical_to_date('2013/04/06 00:00:00') then
2191: hr_utility.set_location('Effective Date must be later than 06-April-2013',10);
2192: fnd_file.put_line(fnd_file.output,'Effective Date must be later than 06-April-2013.');
2193: l_err := true;
2194: end if;
2195: if l_payroll_rec.no_of_parts = ' ' or l_payroll_rec.no_of_parts is null then
2192: fnd_file.put_line(fnd_file.output,'Effective Date must be later than 06-April-2013.');
2193: l_err := true;
2194: end if;
2195: if l_payroll_rec.no_of_parts = ' ' or l_payroll_rec.no_of_parts is null then
2196: hr_utility.set_location('Enter the Number of Parts for Submission. This is a mandatory field.',10);
2197: fnd_file.put_line(fnd_file.output,'Enter the Number of Parts for Submission. This is a mandatory field.');
2198: l_err := true;
2199: elsif substr(l_payroll_rec.no_of_parts,1,2) = '00' or substr(l_payroll_rec.no_of_parts,1,2) ='0'
2200: then
2197: fnd_file.put_line(fnd_file.output,'Enter the Number of Parts for Submission. This is a mandatory field.');
2198: l_err := true;
2199: elsif substr(l_payroll_rec.no_of_parts,1,2) = '00' or substr(l_payroll_rec.no_of_parts,1,2) ='0'
2200: then
2201: hr_utility.set_location('Number of Parts '||l_payroll_rec.no_of_parts||' must be numeric and in the range of 01 to 99 only.',10);
2202: fnd_file.put_line(fnd_file.output,'Number of Parts '||l_payroll_rec.no_of_parts||' must be numeric and in the range of 01 to 99 only.');
2203: l_err := true;
2204: elsif validate_input(substr(l_payroll_rec.no_of_parts,1,2),'NUMBER') > 0
2205: then
2202: fnd_file.put_line(fnd_file.output,'Number of Parts '||l_payroll_rec.no_of_parts||' must be numeric and in the range of 01 to 99 only.');
2203: l_err := true;
2204: elsif validate_input(substr(l_payroll_rec.no_of_parts,1,2),'NUMBER') > 0
2205: then
2206: hr_utility.set_location('Number of Parts '||l_payroll_rec.no_of_parts||' must be numeric and in the range of 01 to 99 only.',10);
2207: fnd_file.put_line(fnd_file.output,'Number of Parts '||l_payroll_rec.no_of_parts||' must be numeric and in the range of 01 to 99 only.');
2208: l_err := true;
2209: end if;
2210:
2208: l_err := true;
2209: end if;
2210:
2211: if ((l_payroll_rec.unique_part_id = ' ' or l_payroll_rec.unique_part_id is null) and (l_payroll_rec.no_of_parts > 1)) then
2212: hr_utility.set_location('The value in the Number of Parts field is greater than 01. The Unique Part Identifier field is now mandatory. Enter a value for the Unique Part Identifier.',10);
2213: fnd_file.put_line(fnd_file.output,'The value in the Number of Parts field is greater than 01. The Unique Part Identifier field is now mandatory. Enter a value for the Unique Part Identifier.');
2214: l_err := true;
2215: elsif (check_resubmission(pactid,l_paye_ref,l_payroll_rec.unique_part_id,l_payroll_rec.business_group_id) = 'Y')
2216: then
2213: fnd_file.put_line(fnd_file.output,'The value in the Number of Parts field is greater than 01. The Unique Part Identifier field is now mandatory. Enter a value for the Unique Part Identifier.');
2214: l_err := true;
2215: elsif (check_resubmission(pactid,l_paye_ref,l_payroll_rec.unique_part_id,l_payroll_rec.business_group_id) = 'Y')
2216: then
2217: hr_utility.set_location('The Unique Part Identifier '||l_payroll_rec.unique_part_id|| ' has been previously used. Change the Unique Part Identifier and resubmit.',10);
2218: fnd_file.put_line(fnd_file.output,'The Unique Part Identifier '||l_payroll_rec.unique_part_id|| ' has been previously used. Change the Unique Part Identifier and resubmit.');
2219: l_err := true;
2220:
2221:
2220:
2221:
2222: elsif validate_input(substr(l_payroll_rec.unique_part_id,1,12),'UNIQUE_PART_ID') > 0
2223: then
2224: hr_utility.set_location('The Unique Part Identifier '||l_payroll_rec.unique_part_id||' has invalid characters. Verify and submit a valid Unique Part Identifier.',10);
2225: fnd_file.put_line(fnd_file.output,'The Unique Part Identifier '||l_payroll_rec.unique_part_id||' has invalid characters. Verify and submit a valid Unique Part Identifier.');
2226: l_err := true;
2227: end if;
2228: END IF;
2238: 'pay_payroll_actions ppa '||
2239: 'where ppa.payroll_action_id = :payroll_action_id '||
2240: 'and ppa.business_group_id = ppf.business_group_id '||
2241: 'order by ppf.person_id';
2242: hr_utility.trace(' Range Cursor Statement : '||sqlstr);
2243: hr_utility.set_location(' Leaving: '||l_proc,100);
2244: END IF;
2245:
2246:
2239: 'where ppa.payroll_action_id = :payroll_action_id '||
2240: 'and ppa.business_group_id = ppf.business_group_id '||
2241: 'order by ppf.person_id';
2242: hr_utility.trace(' Range Cursor Statement : '||sqlstr);
2243: hr_utility.set_location(' Leaving: '||l_proc,100);
2244: END IF;
2245:
2246:
2247:
2263: p_payroll_action_id => pactid,
2264: p_token_name => 'EMPID',
2265: p_token_value => l_employee_number);
2266:
2267: hr_utility.trace(' Payroll : '||l_payroll_id);
2268: hr_utility.trace(' Assignment Set : '||l_assignment_set);
2269: hr_utility.trace(' Employee ID : '||l_employee_number);
2270:
2271: IF (l_payroll_id is null) THEN
2264: p_token_name => 'EMPID',
2265: p_token_value => l_employee_number);
2266:
2267: hr_utility.trace(' Payroll : '||l_payroll_id);
2268: hr_utility.trace(' Assignment Set : '||l_assignment_set);
2269: hr_utility.trace(' Employee ID : '||l_employee_number);
2270:
2271: IF (l_payroll_id is null) THEN
2272: sqlstr := 'select distinct person_id '||
2265: p_token_value => l_employee_number);
2266:
2267: hr_utility.trace(' Payroll : '||l_payroll_id);
2268: hr_utility.trace(' Assignment Set : '||l_assignment_set);
2269: hr_utility.trace(' Employee ID : '||l_employee_number);
2270:
2271: IF (l_payroll_id is null) THEN
2272: sqlstr := 'select distinct person_id '||
2273: 'from per_all_people_f ppf, '||
2316:
2317:
2318: END IF;
2319: END IF;
2320: hr_utility.set_location('Leaving '|| l_proc, 10);
2321: EXCEPTION
2322: when l_exp then
2323: g_validation_check := 'N';
2324: raise_application_error(-20001,'Errors found while archiving data.');
2517: --l_first_asg varchar2(1) :='Y';
2518:
2519: BEGIN
2520:
2521: hr_utility.set_location('Entering: '||l_proc,1);
2522:
2523: open csr_parameter_info;
2524: fetch csr_parameter_info into l_tax_ref,
2525: l_effective_date,
2526: l_no_of_parts,
2527: l_unique_part_id,
2528: l_business_group_id;
2529: close csr_parameter_info;
2530: hr_utility.set_location('Process effective date'||l_effective_date,20);
2531: l_tax_year_start_date := fetch_soy(l_effective_date);
2532:
2533: hr_utility.set_location('Active Assignments',15);
2534: -- To fetch Active Assignments
2529: close csr_parameter_info;
2530: hr_utility.set_location('Process effective date'||l_effective_date,20);
2531: l_tax_year_start_date := fetch_soy(l_effective_date);
2532:
2533: hr_utility.set_location('Active Assignments',15);
2534: -- To fetch Active Assignments
2535: for asg_rec in csr_asg_active loop
2536:
2537: hr_utility.set_location('Assignment ID :' || asg_rec.assignment_id,15);
2533: hr_utility.set_location('Active Assignments',15);
2534: -- To fetch Active Assignments
2535: for asg_rec in csr_asg_active loop
2536:
2537: hr_utility.set_location('Assignment ID :' || asg_rec.assignment_id,15);
2538: l_flag:= 'Y';
2539: -- l_first_asg := 'Y';
2540: /* EAS 2013 changes */
2541: -- If Aggregate flag is set then ignore consecutive assignments
2539: -- l_first_asg := 'Y';
2540: /* EAS 2013 changes */
2541: -- If Aggregate flag is set then ignore consecutive assignments
2542: --if asg_rec.per_agg_flag = 'Y' then
2543: --hr_utility.trace('g_person_id : '||g_person_id);
2544: --hr_utility.trace('asg_rec.person_id : '||asg_rec.person_id);
2545: --if (g_person_id <> asg_rec.person_id) then
2546: -- l_first_asg := 'Y';
2547:
2540: /* EAS 2013 changes */
2541: -- If Aggregate flag is set then ignore consecutive assignments
2542: --if asg_rec.per_agg_flag = 'Y' then
2543: --hr_utility.trace('g_person_id : '||g_person_id);
2544: --hr_utility.trace('asg_rec.person_id : '||asg_rec.person_id);
2545: --if (g_person_id <> asg_rec.person_id) then
2546: -- l_first_asg := 'Y';
2547:
2548: --else
2548: --else
2549: --l_first_asg :='N';
2550: --end if;
2551: -- end if;
2552: --hr_utility.set_location('l_first_asg :' || l_first_asg,15);
2553:
2554: /*
2555: if asg_rec.asg_primary_flag <> 'Y' then
2556: hr_utility.trace(' Aggregation Found. This is not primary assignment.');
2552: --hr_utility.set_location('l_first_asg :' || l_first_asg,15);
2553:
2554: /*
2555: if asg_rec.asg_primary_flag <> 'Y' then
2556: hr_utility.trace(' Aggregation Found. This is not primary assignment.');
2557: l_flag:= 'N';
2558: end if;
2559: */
2560: -- P45 check.If issued do not pick the assignment
2568: open csr_get_terminated_date(asg_rec.assignment_id);
2569: fetch csr_get_terminated_date into l_asg_end_date;
2570: close csr_get_terminated_date;
2571:
2572: hr_utility.trace('Assignment actual end date : '||l_asg_end_date);
2573:
2574: if l_asg_end_date < l_tax_year_start_date then
2575: hr_utility.trace(' Assignment terminated before start of the Tax Year.Ignore the assignment.');
2576: l_flag:= 'N';
2571:
2572: hr_utility.trace('Assignment actual end date : '||l_asg_end_date);
2573:
2574: if l_asg_end_date < l_tax_year_start_date then
2575: hr_utility.trace(' Assignment terminated before start of the Tax Year.Ignore the assignment.');
2576: l_flag:= 'N';
2577: end if;
2578:
2579: -- To check whether continous employment exist for this assignment
2580: if asg_rec.status = 'TERM_ASSIGN' and l_flag = 'Y' then
2581: open csr_find_next_asg(asg_rec.person_id,asg_rec.assignment_id);
2582: fetch csr_find_next_asg into l_next_asg;
2583: if csr_find_next_asg%found then
2584: hr_utility.trace('Continous Employment.Ignore this assignment.');
2585: l_flag:= 'N';
2586: end if;
2587: close csr_find_next_asg;
2588: end if;
2589:
2590: if (l_flag = 'Y') then
2591:
2592: -- Create one assignment action for every assignment
2593: hr_utility.set_location('Archiving for assignment_id '||asg_rec.assignment_id, 50);
2594: select pay_assignment_actions_s.nextval
2595: into lockingactid
2596: from dual;
2597:
2605: null
2606: );
2607: end if;
2608: --g_person_id := asg_rec.person_id;
2609: --hr_utility.trace('g_person_id : '||g_person_id);
2610: end loop;
2611:
2612: -- To fetch Terminated assignments
2613: hr_utility.set_location('Terminated Assignments',15);
2609: --hr_utility.trace('g_person_id : '||g_person_id);
2610: end loop;
2611:
2612: -- To fetch Terminated assignments
2613: hr_utility.set_location('Terminated Assignments',15);
2614: for asg_rec in csr_asg_terminated loop
2615: l_flag := 'Y';
2616: /* Check the cached value of person id .
2617: If the assignment has been archived already, skip the assignment again
2617: If the assignment has been archived already, skip the assignment again
2618: when appers in date tracked records.
2619: */
2620: if l_prev_asg <> asg_rec.assignment_id then
2621: hr_utility.set_location('Assignment ID :' || asg_rec.assignment_id,15);
2622:
2623: -- If Aggregate flag is set then ignore non primary assignments
2624:
2625: open csr_get_agg_flag(asg_rec.person_id);
2624:
2625: open csr_get_agg_flag(asg_rec.person_id);
2626: fetch csr_get_agg_flag into l_per_agg_flag;
2627: close csr_get_agg_flag;
2628: hr_utility.set_location('Aggregation Flag:'||l_per_agg_flag,1515);
2629: /* EAS 2013 changes */
2630: /*if l_per_agg_flag = 'Y' then
2631: if asg_rec.asg_primary_flag <> 'Y' then
2632: hr_utility.trace(' Aggregation Found. This is not primary assignment.');
2628: hr_utility.set_location('Aggregation Flag:'||l_per_agg_flag,1515);
2629: /* EAS 2013 changes */
2630: /*if l_per_agg_flag = 'Y' then
2631: if asg_rec.asg_primary_flag <> 'Y' then
2632: hr_utility.trace(' Aggregation Found. This is not primary assignment.');
2633: l_flag:= 'N';
2634: end if;
2635: end if;*/
2636:
2638: open csr_get_terminated_date(asg_rec.assignment_id);
2639: fetch csr_get_terminated_date into l_asg_end_date;
2640: close csr_get_terminated_date;
2641:
2642: hr_utility.trace('Assignment actual end date : '||l_asg_end_date);
2643:
2644: if l_asg_end_date < l_tax_year_start_date then
2645: hr_utility.trace(' Assignment terminated before start of the Tax Year.Ignore the assignment.');
2646: l_flag:= 'N';
2641:
2642: hr_utility.trace('Assignment actual end date : '||l_asg_end_date);
2643:
2644: if l_asg_end_date < l_tax_year_start_date then
2645: hr_utility.trace(' Assignment terminated before start of the Tax Year.Ignore the assignment.');
2646: l_flag:= 'N';
2647: end if;
2648:
2649: if l_flag = 'Y' then
2648:
2649: if l_flag = 'Y' then
2650:
2651: -- Create one assignment action for every assignment
2652: hr_utility.set_location('Archiving for assignment_id '||asg_rec.assignment_id, 50);
2653: select pay_assignment_actions_s.nextval
2654: into lockingactid
2655: from dual;
2656:
2668: end if;
2669: end if; -- cache check end
2670: end loop;
2671:
2672: hr_utility.set_location('Leaving: '||l_proc,999);
2673: END internal_action_creation_eas;
2674:
2675: --
2676: --
2753:
2754: l_prev_person number:=0;
2755: l_flag varchar2(1) :='Y';
2756: BEGIN
2757: hr_utility.set_location('Entering: '||l_proc,1);
2758: open csr_parameter_info;
2759: fetch csr_parameter_info into l_payroll_id,
2760: l_tax_ref,
2761: l_emp_num,
2766: PAY_GB_P11D_ARCHIVE_SS.get_parameters(
2767: p_payroll_action_id => pactid,
2768: p_token_name => 'ASSET',
2769: p_token_value => l_assignment_set);
2770: hr_utility.set_location('Assignment Set Id '||l_assignment_set,11);
2771:
2772: hr_utility.set_location('Before CSR_ASG cursor effective_date '|| to_char(l_effective_date),10);
2773: for asg_rec in csr_asg loop
2774: l_flag := 'Y';
2768: p_token_name => 'ASSET',
2769: p_token_value => l_assignment_set);
2770: hr_utility.set_location('Assignment Set Id '||l_assignment_set,11);
2771:
2772: hr_utility.set_location('Before CSR_ASG cursor effective_date '|| to_char(l_effective_date),10);
2773: for asg_rec in csr_asg loop
2774: l_flag := 'Y';
2775: hr_utility.set_location('Assignment ID :' || asg_rec.assignment_id,15);
2776:
2771:
2772: hr_utility.set_location('Before CSR_ASG cursor effective_date '|| to_char(l_effective_date),10);
2773: for asg_rec in csr_asg loop
2774: l_flag := 'Y';
2775: hr_utility.set_location('Assignment ID :' || asg_rec.assignment_id,15);
2776:
2777:
2778:
2779: open csr_exc_inc ;
2787: pqp_budget_maintenance.get_asg_set_details(p_assignment_set_id => l_assignment_set
2788: ,p_formula_id => l_formula_id
2789: ,p_tab_asg_set_amnds => l_tab_asg_set_amnds
2790: );
2791: hr_utility.set_location('Assignment Set -FormulaID :' || l_formula_id,11);
2792: hr_utility.set_location('Assignment Set -Amendments Count :' || l_tab_asg_set_amnds.count,11);
2793:
2794: If l_formula_id is null and l_tab_asg_set_amnds.count = 0 then
2795: hr_utility.set_location('Assignment Set -FormulaID :' || asg_rec.assignment_id,15);
2788: ,p_formula_id => l_formula_id
2789: ,p_tab_asg_set_amnds => l_tab_asg_set_amnds
2790: );
2791: hr_utility.set_location('Assignment Set -FormulaID :' || l_formula_id,11);
2792: hr_utility.set_location('Assignment Set -Amendments Count :' || l_tab_asg_set_amnds.count,11);
2793:
2794: If l_formula_id is null and l_tab_asg_set_amnds.count = 0 then
2795: hr_utility.set_location('Assignment Set -FormulaID :' || asg_rec.assignment_id,15);
2796: l_flag := 'N'; -- Assignment Action not to be created
2791: hr_utility.set_location('Assignment Set -FormulaID :' || l_formula_id,11);
2792: hr_utility.set_location('Assignment Set -Amendments Count :' || l_tab_asg_set_amnds.count,11);
2793:
2794: If l_formula_id is null and l_tab_asg_set_amnds.count = 0 then
2795: hr_utility.set_location('Assignment Set -FormulaID :' || asg_rec.assignment_id,15);
2796: l_flag := 'N'; -- Assignment Action not to be created
2797: end if;
2798:
2799:
2802: ,p_formula_id => l_formula_id
2803: ,p_tab_asg_set_amnds => l_tab_asg_set_amnds
2804: ,p_effective_date => l_effective_date
2805: );
2806: hr_utility.set_location('Assignment Id - l_flag :' || l_flag,11);
2807: End if;
2808:
2809:
2810: if l_flag = 'Y' then
2809:
2810: if l_flag = 'Y' then
2811:
2812: -- Create one assignment action for every assignment
2813: hr_utility.set_location('Archiving for assignment_id '||asg_rec.assignment_id, 50);
2814: select pay_assignment_actions_s.nextval
2815: into lockingactid
2816: from dual;
2817:
2828: end if;
2829: --l_prev_person := asg_rec.person_id;
2830: end loop;
2831:
2832: hr_utility.set_location('Leaving: '||l_proc,999);
2833: END internal_action_creation_nino;
2834: --
2835:
2836: --
2891:
2892: l_payroll_rec csr_payroll_details%rowtype;
2893:
2894: BEGIN
2895: hr_utility.set_location('Entering '|| l_proc, 10);
2896: l_err := FALSE;
2897:
2898: open csr_payroll_details(p_payroll_action_id);
2899: fetch csr_payroll_details into l_payroll_rec;
2904: close csr_get_paye_ref;
2905:
2906: l_rep_typ := l_payroll_rec.report_type;
2907:
2908: hr_utility.set_location('l_sender_id '|| l_sender_id, 10);
2909:
2910: IF l_rep_typ in ('RTI_EAS_REP_13','RTI_NINO_REP')
2911: THEN
2912: hr_utility.set_location('Calling header validations', 10);
2908: hr_utility.set_location('l_sender_id '|| l_sender_id, 10);
2909:
2910: IF l_rep_typ in ('RTI_EAS_REP_13','RTI_NINO_REP')
2911: THEN
2912: hr_utility.set_location('Calling header validations', 10);
2913:
2914: if l_payroll_rec.sender_id = ' ' or l_payroll_rec.sender_id is null then
2915: hr_utility.set_location('The Sender Id is missing.',10);
2916: fnd_file.put_line(fnd_file.output,'The Sender Id is missing.');
2911: THEN
2912: hr_utility.set_location('Calling header validations', 10);
2913:
2914: if l_payroll_rec.sender_id = ' ' or l_payroll_rec.sender_id is null then
2915: hr_utility.set_location('The Sender Id is missing.',10);
2916: fnd_file.put_line(fnd_file.output,'The Sender Id is missing.');
2917: l_err := true;
2918: end if;
2919:
2917: l_err := true;
2918: end if;
2919:
2920: if l_payroll_rec.tax_office_no = ' ' or l_payroll_rec.tax_office_no is null then
2921: hr_utility.set_location('The HMRC Office Number is missing.',10);
2922: fnd_file.put_line(fnd_file.output,'The HMRC Office Number is missing.');
2923: l_err := true;
2924: elsif validate_input(substr(l_payroll_rec.tax_office_no,1,3),'NUMBER') > 0
2925: then
2922: fnd_file.put_line(fnd_file.output,'The HMRC Office Number is missing.');
2923: l_err := true;
2924: elsif validate_input(substr(l_payroll_rec.tax_office_no,1,3),'NUMBER') > 0
2925: then
2926: hr_utility.set_location('The HMRC Office Number '||l_payroll_rec.tax_office_no||' has invalid characters.',10);
2927: fnd_file.put_line(fnd_file.output,'The HMRC Office Number '||l_payroll_rec.tax_office_no||' has invalid characters.');
2928: l_err := true;
2929: end if;
2930:
2928: l_err := true;
2929: end if;
2930:
2931: if l_payroll_rec.tax_ref_no = ' ' or l_payroll_rec.tax_ref_no is null then
2932: hr_utility.set_location('The Employer PAYE Reference is missing.',10);
2933: fnd_file.put_line(fnd_file.output,'The Employer PAYE Reference is missing.');
2934: l_err := true;
2935: elsif validate_input(l_payroll_rec.tax_ref_no,'FULL_EDI') > 0 then
2936: hr_utility.set_location('The Employer PAYE Reference '||l_payroll_rec.tax_ref_no||' has invalid characters.',10);
2932: hr_utility.set_location('The Employer PAYE Reference is missing.',10);
2933: fnd_file.put_line(fnd_file.output,'The Employer PAYE Reference is missing.');
2934: l_err := true;
2935: elsif validate_input(l_payroll_rec.tax_ref_no,'FULL_EDI') > 0 then
2936: hr_utility.set_location('The Employer PAYE Reference '||l_payroll_rec.tax_ref_no||' has invalid characters.',10);
2937: fnd_file.put_line(fnd_file.output,'The Employer PAYE Reference '||l_payroll_rec.tax_ref_no||' has invalid characters.');
2938: l_err := true;
2939: end if;
2940:
2938: l_err := true;
2939: end if;
2940:
2941: if l_payroll_rec.employer_name = ' ' or l_payroll_rec.employer_name is null then
2942: hr_utility.set_location('The Employer Name is missing.',10);
2943: fnd_file.put_line(fnd_file.output,'The Employer Name is missing.');
2944: l_err := true;
2945: elsif validate_input(l_payroll_rec.employer_name,'EMP_NAME') > 0 then
2946: hr_utility.set_location('The Employer Name '||l_payroll_rec.employer_name||' has invalid characters.',10);
2942: hr_utility.set_location('The Employer Name is missing.',10);
2943: fnd_file.put_line(fnd_file.output,'The Employer Name is missing.');
2944: l_err := true;
2945: elsif validate_input(l_payroll_rec.employer_name,'EMP_NAME') > 0 then
2946: hr_utility.set_location('The Employer Name '||l_payroll_rec.employer_name||' has invalid characters.',10);
2947: fnd_file.put_line(fnd_file.output,'The Employer Name '||l_payroll_rec.employer_name||' has invalid characters.');
2948: l_err := true;
2949: end if;
2950:
2948: l_err := true;
2949: end if;
2950:
2951: if l_payroll_rec.acc_ref_no = ' ' or l_payroll_rec.acc_ref_no is null then
2952: hr_utility.set_location('The Employer Accounts Office Ref is missing.',10);
2953: fnd_file.put_line(fnd_file.output,'The Employer Accounts Office Ref is missing.');
2954: l_err := true;
2955:
2956: elsif (length(l_payroll_rec.acc_ref_no) > 13
2955:
2956: elsif (length(l_payroll_rec.acc_ref_no) > 13
2957: OR REGEXP_INSTR(l_payroll_rec.acc_ref_no,'^([[:digit:]]{1,3})P([[:alpha:]]{1})([[:digit:]]{1,7})([[:digit:]]|X)$') = 0 ) THEN
2958:
2959: hr_utility.set_location ('Incorrect format entered for Employer Accounts Office Ref field. Valid format is NNNPANNNNNNNX.',10);
2960: fnd_file.put_line(fnd_file.output,'Incorrect format entered for Employer Accounts Office Ref field. Valid format is NNNPANNNNNNNX.');
2961: l_err := true;
2962: END IF;
2963:
2966: if (l_err) then
2967: raise l_exp;
2968: end if;
2969:
2970: hr_utility.set_location('Leaving '|| l_proc, 10);
2971: EXCEPTION
2972: when l_exp then
2973: g_validation_check := 'N';
2974: raise_application_error(-20001,'Errors found while archiving data.');
3105: and paaf1.assignment_type = 'E'
3106: and paaf1.effective_start_date <= p_effective_date);
3107:
3108: BEGIN
3109: hr_utility.set_location('Entering: '||l_proc,1);
3110:
3111: open csr_archive_type;
3112: fetch csr_archive_type into l_archive_type,l_pact_id,l_business_group_id,l_tax_ref;
3113: close csr_archive_type;
3128: -- For EAS
3129: IF l_archive_type = 'RTI_EAS_REP_13'
3130: THEN l_effective_date := l_eff_date;
3131: END IF;
3132: hr_utility.set_location('Archive Code l_effective_date Value:'||l_effective_date,909);
3133: l_archive_person := fetch_person_rec(p_assactid, l_effective_date,l_archive_tab(0));
3134:
3135: l_archive_addr := fetch_address_rec(l_archive_tab(0).person_id,
3136: l_archive_tab(0).assignment_id,
3138: l_archive_tab(1));
3139:
3140: -- Fetching element details
3141:
3142: hr_utility.trace('employee flag '||l_archive_tab(0).act_info18);
3143:
3144: --Fetching assignments details for EAS
3145: IF l_archive_type = 'RTI_EAS_REP_13'
3146: THEN
3143:
3144: --Fetching assignments details for EAS
3145: IF l_archive_type = 'RTI_EAS_REP_13'
3146: THEN
3147: hr_utility.set_location('Fetching Assignment details ',30);
3148: l_archive_asg := fetch_eas_asg_rec(l_eff_date,p_assactid,l_archive_tab(0),l_archive_tab(2));
3149: END IF;
3150:
3151: -- Validating fetched Person,Address,Assignment details for EAS
3210: open processed_asg(l_business_group_id,l_archive_tab(0).assignment_id,l_tax_ref,l_effective_date);
3211: loop
3212: fetch processed_asg into c_pact_id;
3213: exit when processed_asg%notfound;
3214: hr_utility.trace('payroll_action_id fetched by cursor: '||c_pact_id);
3215: end loop;
3216: if processed_asg%rowcount = 0 then
3217: null;
3218: elsif processed_asg%rowcount = 1 then
3231: raise warning_found;
3232: end if;
3233: end if;
3234:
3235: hr_utility.set_location('Calling Person Address validations', 10);
3236: person_addr_validations(p_assactid, p_effective_date, l_archive_tab, l_archive_type, l_per_addr_val_flag);
3237: hr_utility.trace('person address' ||l_per_addr_val_flag);
3238: IF l_per_addr_val_flag = 'Y' THEN -- If validation fails
3239: l_personaddr_val_err := TRUE;
3233: end if;
3234:
3235: hr_utility.set_location('Calling Person Address validations', 10);
3236: person_addr_validations(p_assactid, p_effective_date, l_archive_tab, l_archive_type, l_per_addr_val_flag);
3237: hr_utility.trace('person address' ||l_per_addr_val_flag);
3238: IF l_per_addr_val_flag = 'Y' THEN -- If validation fails
3239: l_personaddr_val_err := TRUE;
3240: END IF;
3241:
3238: IF l_per_addr_val_flag = 'Y' THEN -- If validation fails
3239: l_personaddr_val_err := TRUE;
3240: END IF;
3241:
3242: hr_utility.set_location('Calling EAS validations', 10);
3243: eas_asg_etext_validations(p_assactid, p_effective_date, l_archive_tab, l_eas_etext_asg_flag);
3244: IF l_eas_etext_asg_flag = 'Y' THEN -- If validation fails
3245: l_eas_val_err := TRUE;
3246: END IF;
3248: END IF;
3249: -- Validating fetched Person,Address details for NINO
3250: IF l_archive_type = 'RTI_NINO_REP'
3251: THEN
3252: hr_utility.set_location('Calling Person Address validations', 10);
3253: person_addr_validations_nino(p_assactid, p_effective_date, l_archive_tab, l_archive_type, l_per_addr_val_flag);
3254: hr_utility.trace('person address' ||l_per_addr_val_flag);
3255: IF l_per_addr_val_flag = 'Y' THEN -- If validation fails
3256: l_personaddr_val_err := TRUE;
3250: IF l_archive_type = 'RTI_NINO_REP'
3251: THEN
3252: hr_utility.set_location('Calling Person Address validations', 10);
3253: person_addr_validations_nino(p_assactid, p_effective_date, l_archive_tab, l_archive_type, l_per_addr_val_flag);
3254: hr_utility.trace('person address' ||l_per_addr_val_flag);
3255: IF l_per_addr_val_flag = 'Y' THEN -- If validation fails
3256: l_personaddr_val_err := TRUE;
3257: END IF;
3258: END IF;
3262: IF l_archive_type = 'RTI_EAS_REP_13'
3263: THEN
3264: IF l_personaddr_val_err OR l_eas_val_err -- If validation fails , that record will not be archived.
3265: THEN
3266: hr_utility.set_location('Validation failed, raise error.',999);
3267: raise error_found;
3268: ELSE
3269: hr_utility.set_location('Validation successful, archive data.',999);
3270: /* EAS 2013 changes*/
3265: THEN
3266: hr_utility.set_location('Validation failed, raise error.',999);
3267: raise error_found;
3268: ELSE
3269: hr_utility.set_location('Validation successful, archive data.',999);
3270: /* EAS 2013 changes*/
3271: /* set RTI sent flag for all aggregated assignments under the same PAYE reference*/
3272: set_new_starter_rti_sent(l_archive_tab(0).assignment_id);
3273:
3289: IF l_archive_type = 'RTI_NINO_REP'
3290: THEN
3291: IF l_personaddr_val_err -- If validation fails , that record will not be archived.
3292: THEN
3293: hr_utility.set_location('Validation failed, raise error.',999);
3294: raise error_found;
3295: ELSE
3296: hr_utility.set_location('Validation successful, archive data.',999);
3297: insert_archive_row(p_assactid, p_effective_date,l_archive_tab);
3292: THEN
3293: hr_utility.set_location('Validation failed, raise error.',999);
3294: raise error_found;
3295: ELSE
3296: hr_utility.set_location('Validation successful, archive data.',999);
3297: insert_archive_row(p_assactid, p_effective_date,l_archive_tab);
3298: END IF;
3299: END IF;
3300:
3301: else
3302: raise error_found;
3303: end if;
3304:
3305: hr_utility.set_location('Leaving: '||l_proc,999);
3306:
3307: EXCEPTION
3308:
3309: when warning_found then
3306:
3307: EXCEPTION
3308:
3309: when warning_found then
3310: hr_utility.trace('Warnings found');
3311:
3312: when error_found then
3313: IF l_archive_type in ('RTI_EAS_REP_13','RTI_NINO_REP')
3314: THEN
3313: IF l_archive_type in ('RTI_EAS_REP_13','RTI_NINO_REP')
3314: THEN
3315: raise_application_error(-20001,'Errors found while archiving data.');
3316: ELSE
3317: hr_utility.raise_error;
3318: END IF;
3319:
3320: END archive_code;
3321: --
3771: open csr_parameter_date;
3772: fetch csr_parameter_date into l_nino_eff_date;
3773: close csr_parameter_date;
3774:
3775: hr_utility.set_location('l_nino_eff_date '||l_nino_eff_date,009);
3776: end if;
3777: --Completed Records
3778: IF p_type = 'ET' THEN
3779: FOR action_id IN get_asg_action_id LOOP
3981:
3982: ---------------------
3983: ---------------------
3984: BEGIN
3985: hr_utility.trace('Entering deinit: '||l_proc);
3986:
3987: -- To avoid re-archiving while Retry
3988: delete from pay_action_information pai
3989: where pai.action_context_id = pactid
3999:
4000: --g_part_no=g_part_no + 1;
4001: pay_balance_pkg.set_context('PAYROLL_ACTION_ID',pactid);
4002:
4003: hr_utility.set_location('l_payroll_rec.bus_grp_id = ' || l_payroll_rec.bus_grp_id,10);
4004: hr_utility.set_location('l_payroll_rec.act_param_grp_id = ' || l_payroll_rec.act_param_grp_id,20);
4005: hr_utility.set_location('l_sender_id = '||l_payroll_rec.sender_id,30);
4006: hr_utility.set_location('l_test_indicator = '||l_payroll_rec.test_indicator,40);
4007: hr_utility.set_location('l_request_id = '||l_payroll_rec.request_id,50);
4000: --g_part_no=g_part_no + 1;
4001: pay_balance_pkg.set_context('PAYROLL_ACTION_ID',pactid);
4002:
4003: hr_utility.set_location('l_payroll_rec.bus_grp_id = ' || l_payroll_rec.bus_grp_id,10);
4004: hr_utility.set_location('l_payroll_rec.act_param_grp_id = ' || l_payroll_rec.act_param_grp_id,20);
4005: hr_utility.set_location('l_sender_id = '||l_payroll_rec.sender_id,30);
4006: hr_utility.set_location('l_test_indicator = '||l_payroll_rec.test_indicator,40);
4007: hr_utility.set_location('l_request_id = '||l_payroll_rec.request_id,50);
4008:
4001: pay_balance_pkg.set_context('PAYROLL_ACTION_ID',pactid);
4002:
4003: hr_utility.set_location('l_payroll_rec.bus_grp_id = ' || l_payroll_rec.bus_grp_id,10);
4004: hr_utility.set_location('l_payroll_rec.act_param_grp_id = ' || l_payroll_rec.act_param_grp_id,20);
4005: hr_utility.set_location('l_sender_id = '||l_payroll_rec.sender_id,30);
4006: hr_utility.set_location('l_test_indicator = '||l_payroll_rec.test_indicator,40);
4007: hr_utility.set_location('l_request_id = '||l_payroll_rec.request_id,50);
4008:
4009:
4002:
4003: hr_utility.set_location('l_payroll_rec.bus_grp_id = ' || l_payroll_rec.bus_grp_id,10);
4004: hr_utility.set_location('l_payroll_rec.act_param_grp_id = ' || l_payroll_rec.act_param_grp_id,20);
4005: hr_utility.set_location('l_sender_id = '||l_payroll_rec.sender_id,30);
4006: hr_utility.set_location('l_test_indicator = '||l_payroll_rec.test_indicator,40);
4007: hr_utility.set_location('l_request_id = '||l_payroll_rec.request_id,50);
4008:
4009:
4010: hr_utility.set_location('Archiving RTI PAYROLL INFO',60);
4003: hr_utility.set_location('l_payroll_rec.bus_grp_id = ' || l_payroll_rec.bus_grp_id,10);
4004: hr_utility.set_location('l_payroll_rec.act_param_grp_id = ' || l_payroll_rec.act_param_grp_id,20);
4005: hr_utility.set_location('l_sender_id = '||l_payroll_rec.sender_id,30);
4006: hr_utility.set_location('l_test_indicator = '||l_payroll_rec.test_indicator,40);
4007: hr_utility.set_location('l_request_id = '||l_payroll_rec.request_id,50);
4008:
4009:
4010: hr_utility.set_location('Archiving RTI PAYROLL INFO',60);
4011:
4006: hr_utility.set_location('l_test_indicator = '||l_payroll_rec.test_indicator,40);
4007: hr_utility.set_location('l_request_id = '||l_payroll_rec.request_id,50);
4008:
4009:
4010: hr_utility.set_location('Archiving RTI PAYROLL INFO',60);
4011:
4012: pay_action_information_api.create_action_information (
4013: p_action_information_id => l_action_info_id
4014: , p_action_context_id => pactid
4055:
4056: -- For Spawning the output Concurrent Program
4057: IF (l_is_etext_report='RTI_EAS_REP_13') THEN
4058:
4059: hr_utility.set_location('Spawning RTI Employer Alignment Submission Output process',1);
4060:
4061: xml_layout := FND_REQUEST.ADD_LAYOUT('PAY','PYGBRTIEASOP13','en','US','ETEXT');
4062: IF (xml_layout = true and g_validation_check = 'Y')
4063: THEN
4071: Commit;
4072: --check for process submit error
4073: IF l_request_id = 0
4074: THEN
4075: hr_utility.set_location('Error spawning new process',1);
4076: END IF;
4077: END IF;
4078: END IF;
4079: IF (l_is_etext_report='RTI_NINO_REP') THEN
4077: END IF;
4078: END IF;
4079: IF (l_is_etext_report='RTI_NINO_REP') THEN
4080:
4081: hr_utility.set_location('Spawning RTI NINO verification Output process',1);
4082:
4083: xml_layout := FND_REQUEST.ADD_LAYOUT('PAY','PYGBRTININOOP','en','US','ETEXT');
4084:
4085: IF xml_layout = true
4115:
4116: --check for process submit error
4117: IF l_request_id = 0
4118: THEN
4119: hr_utility.set_location('Error spawning new process',1);
4120: END IF;
4121: END IF;
4122:
4123:
4122:
4123:
4124: END IF;
4125:
4126: hr_utility.set_location('Leaving: '||l_proc,999);
4127:
4128: END deinitialization_code;
4129: --
4130: -- Function for validating Date fields
4170: l_tax_year_start := add_months(l_tax_date,-12)+1 ;
4171:
4172: l_date_valid := p_validate_date;
4173:
4174: hr_utility.set_location('date_validate.l_effective_date '||l_effective_date,1);
4175: hr_utility.set_location('date_validate.l_tax_date'||l_tax_date,1);
4176:
4177: -- Start Date Validation
4178: if (p_mode = 'UK_EMPL_DATE') then
4171:
4172: l_date_valid := p_validate_date;
4173:
4174: hr_utility.set_location('date_validate.l_effective_date '||l_effective_date,1);
4175: hr_utility.set_location('date_validate.l_tax_date'||l_tax_date,1);
4176:
4177: -- Start Date Validation
4178: if (p_mode = 'UK_EMPL_DATE') then
4179: if (l_test_submission = 'N') then