[Home] [Help]
1563:
1564: CURSOR csr_person_id
1565: IS
1566: SELECT paa.person_id
1567: FROM per_assignments_f paa
1568: WHERE paa.assignment_id = p_assignment_id
1569: AND p_date_earned BETWEEN paa.effective_start_date
1570: AND paa.effective_end_date;
1571: --
1590: --
1591: SELECT hoi.org_information1
1592: FROM hr_soft_coding_keyflex hsck
1593: ,hr_organization_information hoi
1594: ,per_assignments_f paaf
1595: ,pay_assignment_actions paa
1596: ,pay_payroll_actions ppa
1597: WHERE paa.assignment_action_id = p_assactid
1598: AND paa.payroll_action_id = ppa.payroll_action_id
1653: -- Cursor to get the Registered Name of the GRE/Legal Entity
1654: Cursor c_reg_name
1655: IS
1656: SELECT hou.name
1657: FROM per_assignments_f peaf
1658: ,hr_soft_coding_keyflex hrscf
1659: ,hr_organization_information hoi
1660: ,hr_organization_units hou
1661: WHERE peaf.assignment_id=p_assignment_id
1665: AND hou.organization_id=hoi.org_information4
1666: AND p_date_earned between peaf.effective_start_date and peaf.effective_end_date;
1667: --
1668: --
1669: l_person_id per_assignments_f.person_id%TYPE;
1670:
1671: l_action_info_id NUMBER;
1672: l_ovn NUMBER;
1673: l_tax_unit_id NUMBER;
1982: ,con.contact_person_id contact
1983: ,con.contact_type -- added for ECR
1984: ,pep.sex -- added for ECR
1985: ,pep.date_of_birth -- added for ECR
1986: FROM per_assignments_f asg
1987: ,per_people_f pep
1988: ,pay_assignment_actions paa
1989: ,per_contact_relationships con
1990: WHERE asg.PERSON_ID=pep.person_id
2007: IS
2008: select DISTINCT hoi.organization_id source_id
2009: from hr_organization_units hoi
2010: ,hr_soft_coding_keyflex scf
2011: ,per_assignments_f asg
2012: ,pay_assignment_actions paa
2013: where asg.assignment_id=paa.assignment_id
2014: and paa.assignment_action_id=p_assignment_action_id
2015: and asg.SOFT_CODING_KEYFLEX_ID=scf.SOFT_CODING_KEYFLEX_ID
2052: ,p_pay_start DATE
2053: ,p_pay_end DATE)
2054: IS
2055: SELECT MAX(paf.effective_end_date)
2056: FROM per_assignments_f paf
2057: ,hr_soft_coding_keyflex scl
2058: WHERE paf.soft_coding_keyflex_id=scl.soft_coding_keyflex_id
2059: AND paf.assignment_id=p_assignment_id
2060: AND scl.segment2 = p_source_id
2068: IS
2069: SELECT ppf.per_information13 pension_num
2070: ,ppf.per_information8 PF_Number
2071: FROM per_people_f ppf
2072: ,per_assignments_f paf
2073: WHERE ppf.person_id = paf.person_id
2074: AND paf.assignment_id = p_assignment_id
2075: AND p_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
2076: AND p_date BETWEEN paf.effective_start_date AND paf.effective_end_date;
2081: ,l_source_id NUMBER)
2082: IS
2083: SELECT min(paf.effective_start_date)
2084: FROM per_people_f ppf
2085: ,per_assignments_f paf
2086: ,hr_soft_coding_keyflex scl
2087: WHERE ppf.person_id = l_person_id
2088: AND paf.person_id =ppf.person_id
2089: AND paf.soft_coding_keyflex_id =scl.soft_coding_keyflex_id
2095: ,l_asg_start_date varchar2)
2096: IS
2097: SELECT '1'
2098: FROM per_people_f ppf
2099: ,per_assignments_f paf
2100: ,hr_soft_coding_keyflex scl
2101: WHERE ppf.person_id = l_person_id
2102: AND paf.person_id =ppf.person_id
2103: AND paf.soft_coding_keyflex_id =scl.soft_coding_keyflex_id
2140: distinct
2141: pps.actual_termination_date term_date, -- Termination Date
2142: pps.pds_information3 efile -- Termination Reason(EFile)
2143: FROM per_people_f ppf,
2144: per_assignments_f paf,
2145: per_periods_of_service pps,
2146: hr_soft_coding_keyflex scl
2147: WHERE paf.person_id = ppf.person_id
2148: AND paf.period_of_service_id = pps.period_of_service_id
2653: IS
2654: SELECT DISTINCT hoi.organization_id source_id
2655: FROM hr_organization_units hoi
2656: ,hr_soft_coding_keyflex scf
2657: ,per_assignments_f asg
2658: ,pay_assignment_actions paa
2659: WHERE asg.assignment_id=paa.assignment_id
2660: AND paa.assignment_action_id=p_assignment_action_id
2661: AND asg.SOFT_CODING_KEYFLEX_ID=scf.SOFT_CODING_KEYFLEX_ID
2682: IS
2683: SELECT hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title) Employee_Name
2684: ,pep.per_information9 ESI_Number
2685: ,pep.person_id person_id
2686: FROM per_assignments_f asg
2687: ,per_people_f pep
2688: ,pay_assignment_actions paa
2689: WHERE asg.PERSON_ID=pep.person_id
2690: AND asg.assignment_id=paa.assignment_id
2786: ,p_pay_start DATE
2787: ,p_pay_end DATE)
2788: IS
2789: SELECT MAX(paf.effective_end_date)
2790: FROM per_assignments_f paf
2791: ,hr_soft_coding_keyflex scl
2792: WHERE paf.soft_coding_keyflex_id=scl.soft_coding_keyflex_id
2793: AND paf.assignment_id=p_assignment_id
2794: AND scl.segment4 = p_source_id
2800: ,p_date DATE)
2801: IS
2802: SELECT ppf.per_information9 ESI_Number
2803: FROM per_people_f ppf
2804: ,per_assignments_f paf
2805: WHERE ppf.person_id = paf.person_id
2806: AND paf.assignment_id = p_assignment_id
2807: AND p_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
2808: AND p_date BETWEEN paf.effective_start_date AND paf.effective_end_date;
3366: ,p_effective_date DATE)
3367: IS
3368: SELECT nvl(pos.name,job.name) name
3369: FROM per_positions pos
3370: ,per_assignments_f asg
3371: ,per_jobs job
3372: WHERE asg.position_id=pos.position_id(+)
3373: AND asg.job_id=job.job_id(+)
3374: AND asg.person_id = p_person_id
3624: IS
3625: SELECT DISTINCT pay_in_prof_tax_pkg.get_state(hoi.organization_id) jurisdiction_code
3626: FROM hr_organization_units hoi
3627: ,hr_soft_coding_keyflex scf
3628: ,per_assignments_f asg
3629: ,pay_assignment_actions paa
3630: WHERE asg.assignment_id = paa.assignment_id
3631: AND paa.assignment_action_id = p_assignment_action_id
3632: AND asg.soft_coding_keyflex_id = scf.soft_coding_keyflex_id
3645: ,hoi.name name
3646: ,asg.effective_end_date
3647: FROM hr_organization_units hoi
3648: ,hr_soft_coding_keyflex scf
3649: ,per_assignments_f asg
3650: ,pay_assignment_actions paa
3651: WHERE asg.assignment_id = paa.assignment_id
3652: AND paa.assignment_action_id = p_assignment_action_id
3653: AND asg.soft_coding_keyflex_id = scf.soft_coding_keyflex_id
3663: CURSOR c_person_full_name(p_assignment_action_id NUMBER)
3664: IS
3665: SELECT hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title) Employee_Name
3666: ,pep.person_id person_id
3667: FROM per_assignments_f asg
3668: ,per_people_f pep
3669: ,pay_assignment_actions paa
3670: WHERE asg.person_id = pep.person_id
3671: AND asg.assignment_id = paa.assignment_id
3736: l_pt_org_name hr_organization_units.name%TYPE;
3737: l_element_type_id pay_element_types_f.element_type_id%TYPE;
3738: l_state_iv_id pay_input_values_f.input_value_id%TYPE;
3739: l_reason_iv_id pay_input_values_f.input_value_id%TYPE;
3740: l_asg_id per_assignments_f.assignment_id%TYPE;
3741: l_message VARCHAR2(255);
3742: l_procedure VARCHAR2(100);
3743:
3744: -- Added as a part of bug fix 4774108.
4042: ,p_effective_date DATE)
4043: IS
4044: SELECT nvl(pos.name,job.name) name
4045: FROM per_positions pos
4046: ,per_assignments_f asg
4047: ,per_jobs job
4048: WHERE asg.position_id=pos.position_id(+)
4049: AND asg.job_id=job.job_id(+)
4050: AND asg.person_id = p_person_id