[Home] [Help]
522: paa.action_sequence,
523: paaf.assignment_id,
524: paa.tax_unit_id
525: from per_people_f pap,
526: per_assignments_f paaf,
527: pay_payroll_actions ppa,
528: pay_payroll_actions ppa1,
529: pay_assignment_actions paa,
530: hr_organization_units hou,
547: and hou.organization_id = c_organization_id
548: and ppa1.effective_date between c_archive_start_date and c_archive_end_date
549: and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type --Bug 4161540
550: and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
551: From per_assignments_f iipaf
552: WHERE iipaf.assignment_id = paaf.assignment_id
553: and iipaf.effective_end_date >= c_archive_start_date
554: and iipaf.effective_start_date <= c_archive_end_date)
555: order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
575: paa.action_sequence,
576: paaf.assignment_id,
577: paa.tax_unit_id
578: from per_people_f pap,
579: per_assignments_f paaf,
580: pay_payroll_actions ppa,
581: pay_payroll_actions ppa1,
582: pay_assignment_actions paa,
583: hr_organization_units hou,
628: paa.tax_unit_id,
629: paa.source_action_id master_action_id, /*Bug# 3935471*/
630: paa2.tax_unit_id master_tax_unit_id /*Bug# 3935471*/
631: from per_people_f pap,
632: per_assignments_f paaf,
633: pay_payroll_actions ppa,
634: pay_payroll_actions ppa1,
635: pay_assignment_actions paa,
636: pay_assignment_actions paa2, /*Bug# 3935471*/
654: and paa.tax_unit_id = c_legal_employer
655: and ppa1.effective_date between c_archive_start_date and c_archive_end_date
656: and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type --Bug 4161540
657: and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
658: From per_assignments_f iipaf
659: WHERE iipaf.assignment_id = paaf.assignment_id
660: and iipaf.effective_end_date >= c_archive_start_date
661: and iipaf.effective_start_date <= c_archive_end_date)
662: order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id, paa.source_action_id, paa2.tax_unit_id; /*Bug# 3935471*/
687: paa.tax_unit_id,
688: paa.source_action_id master_action_id, /*Bug# 3935471*/
689: paa2.tax_unit_id master_tax_unit_id /*Bug# 3935471*/
690: from per_people_f pap,
691: per_assignments_f paaf,
692: pay_payroll_actions ppa,
693: pay_payroll_actions ppa1,
694: pay_assignment_actions paa,
695: pay_assignment_actions paa2, /*Bug# 3935471*/
736: paa.action_sequence,
737: paaf.assignment_id,
738: paa.tax_unit_id
739: from per_people_f pap,
740: per_assignments_f paaf,
741: pay_payroll_actions ppa,
742: pay_payroll_actions ppa1,
743: pay_assignment_actions paa,
744: per_periods_of_service pps
758: and ppa1.effective_date between c_archive_start_date and c_archive_end_date
759: AND paaf.payroll_id = c_payroll_id /*Bug 4040688*/
760: and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type --Bug 4161540
761: and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
762: From per_assignments_f iipaf
763: WHERE iipaf.assignment_id = paaf.assignment_id
764: and iipaf.effective_end_date >= c_archive_start_date
765: and iipaf.effective_start_date <= c_archive_end_date
766: AND iipaf.payroll_id IS NOT NULL) /*Bug#4688800*/
786: paa.action_sequence,
787: paaf.assignment_id,
788: paa.tax_unit_id
789: from per_people_f pap,
790: per_assignments_f paaf,
791: pay_payroll_actions ppa,
792: pay_payroll_actions ppa1,
793: pay_assignment_actions paa,
794: per_periods_of_service pps
832: paa.action_sequence,
833: paaf.assignment_id,
834: paa.tax_unit_id
835: from per_people_f pap,
836: per_assignments_f paaf,
837: pay_payroll_actions ppa,
838: pay_payroll_actions ppa1,
839: pay_assignment_actions paa,
840: per_periods_of_service pps
854: and paa.assignment_id = c_assignment_id
855: and ppa1.effective_date between c_archive_start_date and c_archive_end_date
856: and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type --Bug 4161540
857: and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
858: From per_assignments_f iipaf
859: WHERE iipaf.assignment_id = paaf.assignment_id
860: and iipaf.effective_end_date >= c_archive_start_date
861: and iipaf.effective_start_date <= c_archive_end_date)
862: order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
881: paa.action_sequence,
882: paaf.assignment_id,
883: paa.tax_unit_id
884: from per_people_f pap,
885: per_assignments_f paaf,
886: pay_payroll_actions ppa,
887: pay_payroll_actions ppa1,
888: pay_assignment_actions paa,
889: per_periods_of_service pps
925: paa.action_sequence,
926: paaf.assignment_id,
927: paa.tax_unit_id
928: from per_people_f pap,
929: per_assignments_f paaf,
930: pay_payroll_actions ppa,
931: pay_payroll_actions ppa1,
932: pay_assignment_actions paa,
933: per_periods_of_service pps
946: and paa.source_action_id is null
947: and ppa1.effective_date between c_archive_start_date and c_archive_end_date
948: and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type --Bug 4161540
949: and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
950: From per_assignments_f iipaf
951: WHERE iipaf.assignment_id = paaf.assignment_id
952: and iipaf.effective_end_date >= c_archive_start_date
953: and iipaf.effective_start_date <= c_archive_end_date)
954: order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
972: paa.action_sequence,
973: paaf.assignment_id,
974: paa.tax_unit_id
975: from per_people_f pap,
976: per_assignments_f paaf,
977: pay_payroll_actions ppa,
978: pay_payroll_actions ppa1,
979: pay_assignment_actions paa,
980: per_periods_of_service pps
1741: ) is
1742:
1743: /* Bug 3873942
1744: Cursor c_employee_details - Added join with table per_grades_tl to fetch and archive the grade name.
1745: Outer Join introduced between per_grades_tl and per_assignments_f based on grade_id.
1746: */
1747:
1748: cursor c_employee_details(c_business_group_id hr_all_organization_units.organization_id%TYPE,
1749: c_assignment_id number,c_end_date date, c_start_date date) /*Bug#3662449 c_start_date parameter added*/
1757: -- papf.payroll_name, /*Bug 4132525, Bug 4688800*/
1758: hsc.segment1 tax_unit_id, /*Bug 4040688*/
1759: hou1.NAME Legal_Employer /*Bug 4132525*/
1760: from per_people_f pap,
1761: per_assignments_f paa,
1762: hr_soft_coding_keyflex hsc, /*Bug 4040688*/
1763: hr_organization_units hou, /*Bug 4132525*/
1764: hr_organization_units hou1 /*Bug 4132525*/
1765: -- pay_payrolls_f papf /*Bug 4132525, Bug 4688800*/
1771: AND hou1.organization_id = hsc.segment1 /*Bug 4132525*/
1772: -- AND papf.payroll_id = paa.payroll_id /*Bug 4132525, Bug 4688800*/
1773: -- AND c_end_date BETWEEN papf.effective_start_date AND papf.effective_end_date /*Bug 4132525, Bug 4688800*/
1774: and paa.effective_end_date = ( select max(effective_end_date) /*Bug#3662449 sub query added*/
1775: from per_assignments_f
1776: WHERE assignment_id = c_assignment_id
1777: and effective_end_date >= c_start_date
1778: and effective_start_date <= c_end_date)
1779: and c_end_date between pap.effective_start_date and pap.effective_end_date;
1787: FROM per_all_assignments_f paaf,
1788: pay_payrolls_f pay
1789: WHERE paaf.assignment_id = c_assignment_id
1790: and paaf.effective_end_date = (select max(effective_end_date)
1791: From per_assignments_f iipaf
1792: WHERE iipaf.assignment_id = c_assignment_id
1793: and iipaf.effective_end_date >= c_start_date
1794: and iipaf.effective_start_date <= c_end_date
1795: AND iipaf.payroll_id IS NOT NULL)
1812: select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
1813: ,max(paa.action_sequence)
1814: from pay_assignment_actions paa
1815: , pay_payroll_actions ppa
1816: , per_assignments_f paf
1817: where paa.assignment_id = paf.assignment_id
1818: and paf.assignment_id = c_assignment_id
1819: and ppa.payroll_action_id = paa.payroll_action_id
1820: and ppa.effective_date between c_start_date and c_end_date
1832: select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
1833: ,max(paa.action_sequence)
1834: from pay_assignment_actions paa
1835: , pay_payroll_actions ppa
1836: , per_assignments_f paf
1837: where paa.assignment_id = paf.assignment_id
1838: and paf.assignment_id = c_assignment_id
1839: and ppa.payroll_action_id = paa.payroll_action_id
1840: and ppa.payroll_action_id = c_payroll_action_id