[Home] [Help]
746: l_person_id per_all_people_f.person_id%TYPE;
747: l_pos_id per_all_assignments_f.period_of_service_id%TYPE;
748: l_new_min_active per_all_assignments_f.effective_start_date%TYPE;
749: l_term_and_xfer VARCHAR2(1);
750: l_old_paye_ref hr_soft_coding_keyflex.segment1%TYPE;
751: l_another_active_asg_xfer VARCHAR2(1);
752: --
753: -- cursor to get max effective_start date on the given PAYE Ref
754: -- for given assignment on or before the given date
756: SELECT max(paaf.effective_start_date) first_st_date, max(person_id) person_id
757: FROM per_all_assignments_f paaf,
758: per_assignment_status_types past,
759: pay_all_payrolls_f papf,
760: hr_soft_coding_keyflex flex
761: WHERE paaf.assignment_id = p_asg_id
762: AND paaf.assignment_status_type_id = past.assignment_status_type_id
763: AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
764: AND paaf.payroll_id = papf.payroll_id
774: CURSOR get_first_start IS
775: SELECT max(paaf.effective_start_date) first_st_date, max(person_id) person_id, max(period_of_service_id) pos_id
776: FROM per_all_assignments_f paaf,
777: pay_all_payrolls_f papf,
778: hr_soft_coding_keyflex flex
779: WHERE paaf.assignment_id = p_asg_id
780: AND paaf.payroll_id = papf.payroll_id
781: AND p_proll_eff_date BETWEEN papf.effective_start_date and papf.effective_end_date
782: AND papf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
789: SELECT 'Y' term_and_xfer, flex.segment1 old_paye_ref
790: FROM per_all_assignments_f paaf,
791: per_assignment_status_types past,
792: pay_all_payrolls_f papf,
793: hr_soft_coding_keyflex flex
794: WHERE paaf.assignment_id = p_asg_id
795: AND paaf.assignment_status_type_id = past.assignment_status_type_id
796: AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
797: AND paaf.payroll_id = papf.payroll_id
808: SELECT 'Y'
809: FROM per_all_assignments_f paaf1,
810: per_assignment_status_types past1,
811: pay_all_payrolls_f papf1,
812: hr_soft_coding_keyflex flex1
813: WHERE paaf1.period_of_service_id = l_pos_id
814: AND paaf1.assignment_id <> p_asg_id
815: AND l_min_active BETWEEN paaf1.effective_start_date
816: AND paaf1.effective_end_date
823: AND EXISTS ( SELECT 1
824: FROM per_all_assignments_f paaf2,
825: per_assignment_status_types past2,
826: pay_all_payrolls_f papf2,
827: hr_soft_coding_keyflex flex2
828: WHERE paaf2.assignment_id = paaf1.assignment_id
829: AND l_min_active-1 BETWEEN paaf2.effective_start_date
830: AND paaf2.effective_end_date
831: AND paaf2.assignment_status_type_id = past2.assignment_status_type_id
841: SELECT min(paaf.effective_start_date) min_active
842: FROM per_all_assignments_f paaf,
843: per_assignment_status_types past,
844: pay_all_payrolls_f papf,
845: hr_soft_coding_keyflex flex
846: WHERE paaf.person_id = l_person_id
847: AND (l_min_active-1) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
848: AND paaf.assignment_status_type_id = past.assignment_status_type_id
849: AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
947: l_person_id per_all_people_f.person_id%TYPE;
948: l_pos_id per_all_assignments_f.period_of_service_id%TYPE;
949: l_new_max_active per_all_assignments_f.effective_start_date%TYPE;
950: l_term_and_xfer VARCHAR2(1);
951: l_old_paye_ref hr_soft_coding_keyflex.segment1%TYPE;
952: l_another_active_asg_xfer VARCHAR2(1);
953: --
954: -- cursor to get max effective_end_date on the given PAYE Ref
955: -- for given assignment
957: SELECT max(paaf.effective_end_date) last_end_date, max(person_id) person_id
958: FROM per_all_assignments_f paaf,
959: per_assignment_status_types past,
960: pay_all_payrolls_f papf,
961: hr_soft_coding_keyflex flex
962: WHERE paaf.assignment_id = p_asg_id
963: AND paaf.assignment_status_type_id = past.assignment_status_type_id
964: AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
965: AND paaf.payroll_id = papf.payroll_id
975: CURSOR get_first_start IS
976: SELECT max(paaf.effective_start_date) first_st_date, max(person_id) person_id, max(period_of_service_id) pos_id
977: FROM per_all_assignments_f paaf,
978: pay_all_payrolls_f papf,
979: hr_soft_coding_keyflex flex
980: WHERE paaf.assignment_id = p_asg_id
981: AND paaf.payroll_id = papf.payroll_id
982: AND p_proll_eff_date BETWEEN papf.effective_start_date and papf.effective_end_date
983: AND papf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
990: SELECT 'Y' term_and_xfer, flex.segment1 old_paye_ref
991: FROM per_all_assignments_f paaf,
992: per_assignment_status_types past,
993: pay_all_payrolls_f papf,
994: hr_soft_coding_keyflex flex
995: WHERE paaf.assignment_id = p_asg_id
996: AND paaf.assignment_status_type_id = past.assignment_status_type_id
997: AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
998: AND paaf.payroll_id = papf.payroll_id
1009: SELECT 'Y'
1010: FROM per_all_assignments_f paaf1,
1011: per_assignment_status_types past1,
1012: pay_all_payrolls_f papf1,
1013: hr_soft_coding_keyflex flex1
1014: WHERE paaf1.period_of_service_id = l_pos_id
1015: AND paaf1.assignment_id <> p_asg_id
1016: AND l_min_active BETWEEN paaf1.effective_start_date
1017: AND paaf1.effective_end_date
1024: AND EXISTS ( SELECT 1
1025: FROM per_all_assignments_f paaf2,
1026: per_assignment_status_types past2,
1027: pay_all_payrolls_f papf2,
1028: hr_soft_coding_keyflex flex2
1029: WHERE paaf2.assignment_id = paaf1.assignment_id
1030: AND l_min_active-1 BETWEEN paaf2.effective_start_date
1031: AND paaf2.effective_end_date
1032: AND paaf2.assignment_status_type_id = past2.assignment_status_type_id
1042: SELECT max(paaf.effective_end_date) max_active
1043: FROM per_all_assignments_f paaf,
1044: per_assignment_status_types past,
1045: pay_all_payrolls_f papf,
1046: hr_soft_coding_keyflex flex
1047: WHERE paaf.person_id = l_person_id
1048: AND (l_max_active+1) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
1049: AND paaf.assignment_status_type_id = past.assignment_status_type_id
1050: AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
1346: flex.segment17 * 100 spp_recovered,
1347: flex.segment18 * 100 spp_compensation
1348: End 4011263 */
1349: FROM pay_all_payrolls_f p,
1350: hr_soft_coding_keyflex flex,
1351: hr_organization_information org
1352: WHERE p.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
1353: AND org.org_information_context = 'Tax Details References'
1354: AND org.org_information1 = flex.segment1
1366: p_end_year DATE,
1367: p_paye_ref VARCHAR2) IS
1368: SELECT flex.segment1
1369: FROM pay_all_payrolls_f p,
1370: hr_soft_coding_keyflex flex
1371: WHERE p.soft_coding_keyflex_id = flex.soft_coding_keyflex_id(+)
1372: AND p.payroll_id = p_payroll_id
1373: AND p.effective_start_date <= hr_gbbal.span_end(p_end_year)
1374: AND p.effective_end_date >= hr_gbbal.span_start(p_end_year)
1373: AND p.effective_start_date <= hr_gbbal.span_end(p_end_year)
1374: AND p.effective_end_date >= hr_gbbal.span_start(p_end_year)
1375: AND nvl(flex.segment1, 'XYZ') <> nvl(p_paye_ref, 'ABC');
1376: --
1377: l_another_paye_ref hr_soft_coding_keyflex.segment1%TYPE;
1378: --
1379: cursor csr_payroll_year (p_payroll_id NUMBER,
1380: p_start_year DATE,
1381: p_end_year DATE) IS
1836: -- Add Ordered Index Hints for CBO issue.
1837: -- added p_asg_set_id and usage of hr_assignment_sets (and _amendments) tables
1838: SELECT /*+ ORDERED INDEX (asg PER_ASSIGNMENTS_F_N12,
1839: ppf PAY_PAYROLLS_F_PK,
1840: flex HR_SOFT_CODING_KEYFLEX_PK,
1841: org HR_ORGANIZATION_INFORMATIO_FK1,
1842: per PER_PEOPLE_F_PK)
1843: USE_NL(asg,ppf,flex,org,per) */
1844: asg.assignment_id,
1854: rowidtochar(asg.ROWID) charrowid,
1855: 'N' tax_ref_xfer
1856: FROM per_all_assignments_f asg,
1857: pay_all_payrolls_f ppf,
1858: hr_soft_coding_keyflex flex,
1859: hr_organization_information org,
1860: per_all_people_f per
1861: WHERE asg.person_id BETWEEN stperson AND endperson
1862: AND asg.business_group_id +0 = p_bg_id
1914: UNION
1915: SELECT /*+ ORDERED INDEX (PASS PER_ASSIGNMENTS_F_N12,
1916: ASS PER_ASSIGNMENTS_F_PK,
1917: NROLL PAY_PAYROLLS_F_PK,
1918: FLEX HR_SOFT_CODING_KEYFLEX_PK,
1919: PROLL PAY_PAYROLLS_F_PK,
1920: pflex HR_SOFT_CODING_KEYFLEX_PK,
1921: per PER_PEOPLE_F_PK)
1922: USE_NL(PASS,ASS,NROLL,FLEX,PROLL,pflex,per) */
1916: ASS PER_ASSIGNMENTS_F_PK,
1917: NROLL PAY_PAYROLLS_F_PK,
1918: FLEX HR_SOFT_CODING_KEYFLEX_PK,
1919: PROLL PAY_PAYROLLS_F_PK,
1920: pflex HR_SOFT_CODING_KEYFLEX_PK,
1921: per PER_PEOPLE_F_PK)
1922: USE_NL(PASS,ASS,NROLL,FLEX,PROLL,pflex,per) */
1923: pass.assignment_id,
1924: pass.effective_start_date,
1936: per_all_people_f per
1937: ,per_all_assignments_f PASS
1938: ,per_all_assignments_f ASS
1939: ,pay_all_payrolls_f NROLL
1940: ,hr_soft_coding_keyflex FLEX
1941: ,pay_all_payrolls_f PROLL
1942: ,hr_soft_coding_keyflex pflex
1943: WHERE NROLL.payroll_id = ASS.payroll_id
1944: AND ASS.effective_start_date between
1938: ,per_all_assignments_f ASS
1939: ,pay_all_payrolls_f NROLL
1940: ,hr_soft_coding_keyflex FLEX
1941: ,pay_all_payrolls_f PROLL
1942: ,hr_soft_coding_keyflex pflex
1943: WHERE NROLL.payroll_id = ASS.payroll_id
1944: AND ASS.effective_start_date between
1945: NROLL.effective_start_date and NROLL.effective_end_date
1946: AND NROLL.soft_coding_keyflex_id = FLEX.soft_coding_keyflex_id
3290: SELECT min(paaf.effective_start_date) min_active, max(paaf.effective_end_date) max_active
3291: FROM per_all_assignments_f paaf,
3292: per_assignment_status_types past,
3293: pay_all_payrolls_f papf,
3294: hr_soft_coding_keyflex flex
3295: WHERE paaf.assignment_id = p_asg_id
3296: AND paaf.assignment_status_type_id = past.assignment_status_type_id
3297: AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
3298: AND paaf.payroll_id = papf.payroll_id
3359: p_start_year DATE, p_end_year DATE) IS
3360: SELECT max(ass.effective_start_date)
3361: FROM per_all_assignments_f ass
3362: ,pay_all_payrolls_f nroll
3363: ,hr_soft_coding_keyflex flex
3364: ,per_all_assignments_f pass
3365: ,pay_all_payrolls_f proll
3366: ,hr_soft_coding_keyflex pflex
3367: WHERE ass.assignment_id = p_asg_id
3362: ,pay_all_payrolls_f nroll
3363: ,hr_soft_coding_keyflex flex
3364: ,per_all_assignments_f pass
3365: ,pay_all_payrolls_f proll
3366: ,hr_soft_coding_keyflex pflex
3367: WHERE ass.assignment_id = p_asg_id
3368: AND ass.effective_start_date < p_asg_end
3369: AND nroll.payroll_id = ass.payroll_id
3370: AND ass.effective_start_date BETWEEN
3639: ( SELECT nvl(max(ass.effective_start_date),
3640: to_date('01-01-0001','DD-MM-YYYY'))
3641: FROM per_all_assignments_f ass
3642: ,pay_all_payrolls_f nroll
3643: ,hr_soft_coding_keyflex flex
3644: ,per_all_assignments_f pass -- previous assignment
3645: ,pay_all_payrolls_f proll
3646: ,hr_soft_coding_keyflex pflex
3647: WHERE ass.assignment_id = bal_assact.assignment_id
3642: ,pay_all_payrolls_f nroll
3643: ,hr_soft_coding_keyflex flex
3644: ,per_all_assignments_f pass -- previous assignment
3645: ,pay_all_payrolls_f proll
3646: ,hr_soft_coding_keyflex pflex
3647: WHERE ass.assignment_id = bal_assact.assignment_id
3648: AND nroll.payroll_id = ass.payroll_id
3649: AND ass.effective_start_date BETWEEN
3650: nroll.effective_start_date AND nroll.effective_end_date
3914: is
3915: select count(*)
3916: from per_all_assignments_f paaf,
3917: pay_all_payrolls_f papf,
3918: hr_soft_coding_keyflex flex
3919: where paaf.assignment_id = lv_assignment_id
3920: and paaf.payroll_id = lv_payroll_id
3921: and paaf.effective_start_date < l_active_start
3922: and paaf.payroll_id = papf.payroll_id