[Home] [Help]
11: -- Bug c_context_name - PRSI Context Balance Design Change
12: l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
13: l_context_id ff_contexts.context_id%TYPE;
14: l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
15: p_person_id_global per_people_f.person_id%TYPE; -- to store the person_id in a package level variable to be used in c_context_name cursor
16: l_class_count NUMBER (4);
17: l_weeks_at_initial_class NUMBER (4);
18: l_weeks_at_second_class NUMBER (4);
19: l_weeks_at_third_class NUMBER (4);
104: ,p_start_date date
105: ,p_end_date date
106: ,p_bg_id number)
107: RETURN NUMBER AS
108: l_person_id per_people_f.person_id%TYPE;
109: l_actid pay_assignment_actions.assignment_action_id%TYPE;
110:
111: cursor csr_check_multiple_asg IS
112: select paaf.person_id
243: MIN (ptp.end_date) minimum_effective_date,
244: asg.primary_flag,
245: paa.assignment_action_id,
246: trim(rrv1.result_value) result_value
247: FROM per_people_f per,
248: per_assignments_f asg,
249: pay_assignment_actions paa,
250: pay_payroll_actions ppa,
251: per_time_periods ptp,
256: WHERE per.person_id = p_person_id
257: AND per.current_employee_flag = 'Y'
258: AND per.effective_start_date =
259: (SELECT MIN (per2.effective_start_date)
260: FROM per_people_f per2
261: , per_periods_of_service pos2
262: WHERE per2.person_id = per.person_id
263: AND per2.effective_start_date <= p_end_date
264: AND pos2.person_id = per2.person_id
783: l_locked_action NUMBER;
784: l_out_var VARCHAR2 (30);
785: l_aact_id pay_assignment_actions.assignment_action_id%TYPE;
786: l_set_flag hr_assignment_set_amendments.include_or_exclude%TYPE ;
787: l_temp_person_id per_people_f.person_id%TYPE :=0;
788: l_start_date date;
789: l_end_date date;
790: l_bg_id number;
791:
1441: l_arch_hire_date pay_action_information.action_information24%TYPE;
1442: l_arch_payroll_action_id pay_assignment_actions.payroll_action_id%TYPE;
1443: l_arch_annual_tax_credit pay_action_information.action_information26%TYPE;
1444: l_arch_term_date pay_action_information.action_information25%TYPE;
1445: l_arch_mothers_name per_people_f.per_information1%TYPE;
1446: l_arch_totwks_insurble_emplmnt pay_run_result_values.result_value%TYPE;
1447: l_arch_initial_class pay_run_result_values.result_value%TYPE;
1448: l_arch_second_class pay_run_result_values.result_value%TYPE;
1449: l_arch_third_class pay_run_result_values.result_value%TYPE;
1768: per_assignments_f paf,
1769: per_periods_of_service pps,
1770: -- pay_ie_paye_details_f payef,
1771: per_time_periods ptp,
1772: per_people_f ppf
1773: WHERE pact_iedi.action_information_category = 'IE EMPLOYEE DETAILS'
1774: AND pact_iedi.action_context_type = 'AAP'
1775: AND pact_iedi.action_context_id = p_locked_action
1776: AND pact_edi.action_information_category = 'EMPLOYEE DETAILS'
1797: AND paf.person_id = pps.person_id
1798: AND ppf.person_id = paf.person_id
1799: AND ppf.effective_end_date =
1800: (SELECT MAX (per.effective_end_date)
1801: FROM per_people_f per
1802: WHERE per.person_id = ppf.person_id
1803: AND per.effective_start_date <=
1804: l_end_date --pay_ie_p35.get_start_date()
1805: AND per.effective_end_date >=
2003: ) -- Q1_Previous_Emp_USC
2004: --14659262
2005: FROM pay_action_interlocks pai,
2006: pay_assignment_actions paa,
2007: per_people_f ppf,
2008: per_periods_of_service pps,
2009: per_assignments_f paf,
2010: per_addresses pad,
2011: -- pay_ie_paye_details_f payef, --Bug 4154171
2030: AND asg.effective_start_date <= l_end_date
2031: AND asg.effective_end_date >= l_start_date)
2032: AND ppf.effective_end_date =
2033: (SELECT MAX (per.effective_end_date)
2034: FROM per_people_f per
2035: WHERE per.person_id = ppf.person_id
2036: AND per.effective_start_date <= l_end_date
2037: AND per.effective_end_date >= l_start_date)
2038: AND paf.period_of_service_id = pps.period_of_service_id
2240: 0
2241: ) -- Q1_Previous_Emp_USC
2242: --14659262
2243: FROM pay_assignment_actions paa,
2244: per_people_f ppf,
2245: per_periods_of_service pps,
2246: per_assignments_f paf,
2247: per_addresses pad,
2248: -- pay_ie_paye_details_f payef,
2265: AND asg.effective_start_date <= l_end_date
2266: AND asg.effective_end_date >= l_start_date)
2267: AND ppf.effective_end_date =
2268: (SELECT MAX (per.effective_end_date)
2269: FROM per_people_f per
2270: WHERE per.person_id = ppf.person_id
2271: AND per.effective_start_date <= l_end_date
2272: AND per.effective_end_date >= l_start_date)
2273: AND paf.period_of_service_id = pps.period_of_service_id
7734: IS
7735: SELECT /*+ ORDERED USE_NL(paa, ppa, ptp)
7736: fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
7737: paa.assignment_action_id),16)) assignment_action_id --bug fix 4004470
7738: FROM per_people_f ppf,
7739: per_assignments_f paf,
7740: pay_assignment_actions paa,
7741: pay_payroll_actions ppa,
7742: per_time_periods ptp
7780: MIN (ptp.end_date) minimum_effective_date,
7781: asg.primary_flag,
7782: paa.assignment_action_id,
7783: trim(rrv1.result_value) result_value
7784: FROM per_people_f per,
7785: per_assignments_f asg,
7786: pay_assignment_actions paa,
7787: pay_payroll_actions ppa,
7788: per_time_periods ptp,
7800: --AND asg.assignment_id = p_assignment_id -- vik added code for getting values for given assignment
7801: AND per.current_employee_flag = 'Y'
7802: AND per.effective_start_date =
7803: (SELECT MIN (per2.effective_start_date)
7804: FROM per_people_f per2
7805: -- Bug Fix 4004384
7806: , per_periods_of_service pos2
7807: WHERE per2.person_id = per.person_id
7808: AND per2.effective_start_date <= p_end_date
7902: 'Context Contribution Class', rrv1.result_value,
7903: '0'
7904: )
7905: ) class_name
7906: FROM per_people_f per,
7907: per_assignments_f asg,
7908: pay_assignment_actions paa,
7909: pay_payroll_actions ppa,
7910: per_time_periods ptp,
7915: WHERE per.person_id = p_person_id
7916: --ANd asg.assignment_id = p_assignment_id -- vik added code for getting values for given assignment
7917: AND per.effective_start_date =
7918: (SELECT MIN (per2.effective_start_date)
7919: FROM per_people_f per2
7920: WHERE per.person_id = per2.person_id
7921: AND per2.effective_start_date <= p_end_date
7922: AND NVL (per2.effective_end_date, p_end_date) >=
7923: p_start_date
8012: asg.business_group_id bg_id,
8013: asg.person_id per_id,
8014: MIN (ptp.end_date) min_eff_date,
8015: ptp.time_period_id
8016: FROM per_people_f per,
8017: per_assignments_f asg,
8018: pay_assignment_actions paa,
8019: pay_payroll_actions ppa,
8020: per_time_periods ptp,
8029: AND nvl(paei.aei_information1,'X') = nvl(p_ppsn_override,'X')
8030: AND per.current_employee_flag = 'Y'
8031: AND per.effective_start_date =
8032: (SELECT MIN (per2.effective_start_date)
8033: FROM per_people_f per2
8034: , per_periods_of_service pos2
8035: WHERE per2.person_id = per.person_id
8036: AND per2.effective_start_date <= p_end_date
8037: AND pos2.person_id = per2.person_id