[Home] [Help]
722: l_person_id per_all_people_f.person_id%TYPE;
723: l_pos_id per_all_assignments_f.period_of_service_id%TYPE;
724: l_new_min_active per_all_assignments_f.effective_start_date%TYPE;
725: l_term_and_xfer VARCHAR2(1);
726: l_old_paye_ref hr_soft_coding_keyflex.segment1%TYPE;
727: l_another_active_asg_xfer VARCHAR2(1);
728: --
729: -- cursor to get max effective_start date on the given PAYE Ref
730: -- for given assignment on or before the given date
732: SELECT max(paaf.effective_start_date) first_st_date, max(person_id) person_id
733: FROM per_all_assignments_f paaf,
734: per_assignment_status_types past,
735: pay_all_payrolls_f papf,
736: hr_soft_coding_keyflex flex
737: WHERE paaf.assignment_id = p_asg_id
738: AND paaf.assignment_status_type_id = past.assignment_status_type_id
739: AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
740: AND paaf.payroll_id = papf.payroll_id
750: CURSOR get_first_start IS
751: SELECT max(paaf.effective_start_date) first_st_date, max(person_id) person_id, max(period_of_service_id) pos_id
752: FROM per_all_assignments_f paaf,
753: pay_all_payrolls_f papf,
754: hr_soft_coding_keyflex flex
755: WHERE paaf.assignment_id = p_asg_id
756: AND paaf.payroll_id = papf.payroll_id
757: AND p_proll_eff_date BETWEEN papf.effective_start_date and papf.effective_end_date
758: AND papf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
765: SELECT 'Y' term_and_xfer, flex.segment1 old_paye_ref
766: FROM per_all_assignments_f paaf,
767: per_assignment_status_types past,
768: pay_all_payrolls_f papf,
769: hr_soft_coding_keyflex flex
770: WHERE paaf.assignment_id = p_asg_id
771: AND paaf.assignment_status_type_id = past.assignment_status_type_id
772: AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
773: AND paaf.payroll_id = papf.payroll_id
784: SELECT 'Y'
785: FROM per_all_assignments_f paaf1,
786: per_assignment_status_types past1,
787: pay_all_payrolls_f papf1,
788: hr_soft_coding_keyflex flex1
789: WHERE paaf1.period_of_service_id = l_pos_id
790: AND paaf1.assignment_id <> p_asg_id
791: AND l_min_active BETWEEN paaf1.effective_start_date
792: AND paaf1.effective_end_date
799: AND EXISTS ( SELECT 1
800: FROM per_all_assignments_f paaf2,
801: per_assignment_status_types past2,
802: pay_all_payrolls_f papf2,
803: hr_soft_coding_keyflex flex2
804: WHERE paaf2.assignment_id = paaf1.assignment_id
805: AND l_min_active-1 BETWEEN paaf2.effective_start_date
806: AND paaf2.effective_end_date
807: AND paaf2.assignment_status_type_id = past2.assignment_status_type_id
817: SELECT min(paaf.effective_start_date) min_active
818: FROM per_all_assignments_f paaf,
819: per_assignment_status_types past,
820: pay_all_payrolls_f papf,
821: hr_soft_coding_keyflex flex
822: WHERE paaf.person_id = l_person_id
823: AND (l_min_active-1) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
824: AND paaf.assignment_status_type_id = past.assignment_status_type_id
825: AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
923: l_person_id per_all_people_f.person_id%TYPE;
924: l_pos_id per_all_assignments_f.period_of_service_id%TYPE;
925: l_new_max_active per_all_assignments_f.effective_start_date%TYPE;
926: l_term_and_xfer VARCHAR2(1);
927: l_old_paye_ref hr_soft_coding_keyflex.segment1%TYPE;
928: l_another_active_asg_xfer VARCHAR2(1);
929: --
930: -- cursor to get max effective_end_date on the given PAYE Ref
931: -- for given assignment
933: SELECT max(paaf.effective_end_date) last_end_date, max(person_id) person_id
934: FROM per_all_assignments_f paaf,
935: per_assignment_status_types past,
936: pay_all_payrolls_f papf,
937: hr_soft_coding_keyflex flex
938: WHERE paaf.assignment_id = p_asg_id
939: AND paaf.assignment_status_type_id = past.assignment_status_type_id
940: AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
941: AND paaf.payroll_id = papf.payroll_id
951: CURSOR get_first_start IS
952: SELECT max(paaf.effective_start_date) first_st_date, max(person_id) person_id, max(period_of_service_id) pos_id
953: FROM per_all_assignments_f paaf,
954: pay_all_payrolls_f papf,
955: hr_soft_coding_keyflex flex
956: WHERE paaf.assignment_id = p_asg_id
957: AND paaf.payroll_id = papf.payroll_id
958: AND p_proll_eff_date BETWEEN papf.effective_start_date and papf.effective_end_date
959: AND papf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
966: SELECT 'Y' term_and_xfer, flex.segment1 old_paye_ref
967: FROM per_all_assignments_f paaf,
968: per_assignment_status_types past,
969: pay_all_payrolls_f papf,
970: hr_soft_coding_keyflex flex
971: WHERE paaf.assignment_id = p_asg_id
972: AND paaf.assignment_status_type_id = past.assignment_status_type_id
973: AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
974: AND paaf.payroll_id = papf.payroll_id
985: SELECT 'Y'
986: FROM per_all_assignments_f paaf1,
987: per_assignment_status_types past1,
988: pay_all_payrolls_f papf1,
989: hr_soft_coding_keyflex flex1
990: WHERE paaf1.period_of_service_id = l_pos_id
991: AND paaf1.assignment_id <> p_asg_id
992: AND l_min_active BETWEEN paaf1.effective_start_date
993: AND paaf1.effective_end_date
1000: AND EXISTS ( SELECT 1
1001: FROM per_all_assignments_f paaf2,
1002: per_assignment_status_types past2,
1003: pay_all_payrolls_f papf2,
1004: hr_soft_coding_keyflex flex2
1005: WHERE paaf2.assignment_id = paaf1.assignment_id
1006: AND l_min_active-1 BETWEEN paaf2.effective_start_date
1007: AND paaf2.effective_end_date
1008: AND paaf2.assignment_status_type_id = past2.assignment_status_type_id
1018: SELECT max(paaf.effective_end_date) max_active
1019: FROM per_all_assignments_f paaf,
1020: per_assignment_status_types past,
1021: pay_all_payrolls_f papf,
1022: hr_soft_coding_keyflex flex
1023: WHERE paaf.person_id = l_person_id
1024: AND (l_max_active+1) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
1025: AND paaf.assignment_status_type_id = past.assignment_status_type_id
1026: AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
1320: flex.segment17 * 100 spp_recovered,
1321: flex.segment18 * 100 spp_compensation
1322: End 4011263 */
1323: FROM pay_all_payrolls_f p,
1324: hr_soft_coding_keyflex flex,
1325: hr_organization_information org
1326: WHERE p.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
1327: AND org.org_information_context = 'Tax Details References'
1328: AND org.org_information1 = flex.segment1
1340: p_end_year DATE,
1341: p_paye_ref VARCHAR2) IS
1342: SELECT flex.segment1
1343: FROM pay_all_payrolls_f p,
1344: hr_soft_coding_keyflex flex
1345: WHERE p.soft_coding_keyflex_id = flex.soft_coding_keyflex_id(+)
1346: AND p.payroll_id = p_payroll_id
1347: AND p.effective_start_date <= hr_gbbal.span_end(p_end_year)
1348: AND p.effective_end_date >= hr_gbbal.span_start(p_end_year)
1347: AND p.effective_start_date <= hr_gbbal.span_end(p_end_year)
1348: AND p.effective_end_date >= hr_gbbal.span_start(p_end_year)
1349: AND nvl(flex.segment1, 'XYZ') <> nvl(p_paye_ref, 'ABC');
1350: --
1351: l_another_paye_ref hr_soft_coding_keyflex.segment1%TYPE;
1352: --
1353: cursor csr_payroll_year (p_payroll_id NUMBER,
1354: p_start_year DATE,
1355: p_end_year DATE) IS
1789: -- Add Ordered Index Hints for CBO issue.
1790: -- added p_asg_set_id and usage of hr_assignment_sets (and _amendments) tables
1791: SELECT /*+ ORDERED INDEX (asg PER_ASSIGNMENTS_F_N12,
1792: ppf PAY_PAYROLLS_F_PK,
1793: flex HR_SOFT_CODING_KEYFLEX_PK,
1794: org HR_ORGANIZATION_INFORMATIO_FK1,
1795: per PER_PEOPLE_F_PK)
1796: USE_NL(asg,ppf,flex,org,per) */
1797: asg.assignment_id,
1807: rowidtochar(asg.ROWID) charrowid,
1808: 'N' tax_ref_xfer
1809: FROM per_all_assignments_f asg,
1810: pay_all_payrolls_f ppf,
1811: hr_soft_coding_keyflex flex,
1812: hr_organization_information org,
1813: per_all_people_f per
1814: WHERE asg.person_id BETWEEN stperson AND endperson
1815: AND asg.business_group_id +0 = p_bg_id
1866: UNION
1867: SELECT /*+ ORDERED INDEX (PASS PER_ASSIGNMENTS_F_N12,
1868: ASS PER_ASSIGNMENTS_F_PK,
1869: NROLL PAY_PAYROLLS_F_PK,
1870: FLEX HR_SOFT_CODING_KEYFLEX_PK,
1871: PROLL PAY_PAYROLLS_F_PK,
1872: pflex HR_SOFT_CODING_KEYFLEX_PK,
1873: per PER_PEOPLE_F_PK)
1874: USE_NL(PASS,ASS,NROLL,FLEX,PROLL,pflex,per) */
1868: ASS PER_ASSIGNMENTS_F_PK,
1869: NROLL PAY_PAYROLLS_F_PK,
1870: FLEX HR_SOFT_CODING_KEYFLEX_PK,
1871: PROLL PAY_PAYROLLS_F_PK,
1872: pflex HR_SOFT_CODING_KEYFLEX_PK,
1873: per PER_PEOPLE_F_PK)
1874: USE_NL(PASS,ASS,NROLL,FLEX,PROLL,pflex,per) */
1875: pass.assignment_id,
1876: pass.effective_start_date,
1888: per_all_people_f per
1889: ,per_all_assignments_f PASS
1890: ,per_all_assignments_f ASS
1891: ,pay_all_payrolls_f NROLL
1892: ,hr_soft_coding_keyflex FLEX
1893: ,pay_all_payrolls_f PROLL
1894: ,hr_soft_coding_keyflex pflex
1895: WHERE NROLL.payroll_id = ASS.payroll_id
1896: AND ASS.effective_start_date between
1890: ,per_all_assignments_f ASS
1891: ,pay_all_payrolls_f NROLL
1892: ,hr_soft_coding_keyflex FLEX
1893: ,pay_all_payrolls_f PROLL
1894: ,hr_soft_coding_keyflex pflex
1895: WHERE NROLL.payroll_id = ASS.payroll_id
1896: AND ASS.effective_start_date between
1897: NROLL.effective_start_date and NROLL.effective_end_date
1898: AND NROLL.soft_coding_keyflex_id = FLEX.soft_coding_keyflex_id
3163: SELECT min(paaf.effective_start_date) min_active, max(paaf.effective_end_date) max_active
3164: FROM per_all_assignments_f paaf,
3165: per_assignment_status_types past,
3166: pay_all_payrolls_f papf,
3167: hr_soft_coding_keyflex flex
3168: WHERE paaf.assignment_id = p_asg_id
3169: AND paaf.assignment_status_type_id = past.assignment_status_type_id
3170: AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
3171: AND paaf.payroll_id = papf.payroll_id
3232: p_start_year DATE, p_end_year DATE) IS
3233: SELECT max(ass.effective_start_date)
3234: FROM per_all_assignments_f ass
3235: ,pay_all_payrolls_f nroll
3236: ,hr_soft_coding_keyflex flex
3237: ,per_all_assignments_f pass
3238: ,pay_all_payrolls_f proll
3239: ,hr_soft_coding_keyflex pflex
3240: WHERE ass.assignment_id = p_asg_id
3235: ,pay_all_payrolls_f nroll
3236: ,hr_soft_coding_keyflex flex
3237: ,per_all_assignments_f pass
3238: ,pay_all_payrolls_f proll
3239: ,hr_soft_coding_keyflex pflex
3240: WHERE ass.assignment_id = p_asg_id
3241: AND ass.effective_start_date < p_asg_end
3242: AND nroll.payroll_id = ass.payroll_id
3243: AND ass.effective_start_date BETWEEN
3504: ( SELECT nvl(max(ass.effective_start_date),
3505: to_date('01-01-0001','DD-MM-YYYY'))
3506: FROM per_all_assignments_f ass
3507: ,pay_all_payrolls_f nroll
3508: ,hr_soft_coding_keyflex flex
3509: ,per_all_assignments_f pass /* previous assignment */
3510: ,pay_all_payrolls_f proll
3511: ,hr_soft_coding_keyflex pflex
3512: WHERE ass.assignment_id = bal_assact.assignment_id
3507: ,pay_all_payrolls_f nroll
3508: ,hr_soft_coding_keyflex flex
3509: ,per_all_assignments_f pass /* previous assignment */
3510: ,pay_all_payrolls_f proll
3511: ,hr_soft_coding_keyflex pflex
3512: WHERE ass.assignment_id = bal_assact.assignment_id
3513: AND nroll.payroll_id = ass.payroll_id
3514: AND ass.effective_start_date BETWEEN
3515: nroll.effective_start_date AND nroll.effective_end_date