[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);
97: ,p_start_date date
98: ,p_end_date date
99: ,p_bg_id number)
100: RETURN NUMBER AS
101: l_person_id per_people_f.person_id%TYPE;
102: l_actid pay_assignment_actions.assignment_action_id%TYPE;
103:
104: cursor csr_check_multiple_asg IS
105: select paaf.person_id
236: MIN (ptp.end_date) minimum_effective_date,
237: asg.primary_flag,
238: paa.assignment_action_id,
239: trim(rrv1.result_value) result_value
240: FROM per_people_f per,
241: per_assignments_f asg,
242: pay_assignment_actions paa,
243: pay_payroll_actions ppa,
244: per_time_periods ptp,
249: WHERE per.person_id = p_person_id
250: AND per.current_employee_flag = 'Y'
251: AND per.effective_start_date =
252: (SELECT MIN (per2.effective_start_date)
253: FROM per_people_f per2
254: , per_periods_of_service pos2
255: WHERE per2.person_id = per.person_id
256: AND per2.effective_start_date <= p_end_date
257: AND pos2.person_id = per2.person_id
776: l_locked_action NUMBER;
777: l_out_var VARCHAR2 (30);
778: l_aact_id pay_assignment_actions.assignment_action_id%TYPE;
779: l_set_flag hr_assignment_set_amendments.include_or_exclude%TYPE ;
780: l_temp_person_id per_people_f.person_id%TYPE :=0;
781: l_start_date date;
782: l_end_date date;
783: l_bg_id number;
784:
1184: l_arch_hire_date pay_action_information.action_information24%TYPE;
1185: l_arch_payroll_action_id pay_assignment_actions.payroll_action_id%TYPE;
1186: l_arch_annual_tax_credit pay_action_information.action_information26%TYPE;
1187: l_arch_term_date pay_action_information.action_information25%TYPE;
1188: l_arch_mothers_name per_people_f.per_information1%TYPE;
1189: l_arch_totwks_insurble_emplmnt pay_run_result_values.result_value%TYPE;
1190: l_arch_initial_class pay_run_result_values.result_value%TYPE;
1191: l_arch_second_class pay_run_result_values.result_value%TYPE;
1192: l_arch_third_class pay_run_result_values.result_value%TYPE;
1429: per_assignments_f paf,
1430: per_periods_of_service pps,
1431: -- pay_ie_paye_details_f payef,
1432: per_time_periods ptp,
1433: per_people_f ppf
1434: WHERE pact_iedi.action_information_category = 'IE EMPLOYEE DETAILS'
1435: AND pact_iedi.action_context_type = 'AAP'
1436: AND pact_iedi.action_context_id = p_locked_action
1437: AND pact_edi.action_information_category = 'EMPLOYEE DETAILS'
1458: AND paf.person_id = pps.person_id
1459: AND ppf.person_id = paf.person_id
1460: AND ppf.effective_end_date =
1461: (SELECT MAX (per.effective_end_date)
1462: FROM per_people_f per
1463: WHERE per.person_id = ppf.person_id
1464: AND per.effective_start_date <=
1465: l_end_date --pay_ie_p35.get_start_date()
1466: AND per.effective_end_date >=
1630: 0
1631: ) -- Q1_Previous_Emp_Tax
1632: FROM pay_action_interlocks pai,
1633: pay_assignment_actions paa,
1634: per_people_f ppf,
1635: per_periods_of_service pps,
1636: per_assignments_f paf,
1637: per_addresses pad,
1638: -- pay_ie_paye_details_f payef, --Bug 4154171
1657: AND asg.effective_start_date <= l_end_date
1658: AND asg.effective_end_date >= l_start_date)
1659: AND ppf.effective_end_date =
1660: (SELECT MAX (per.effective_end_date)
1661: FROM per_people_f per
1662: WHERE per.person_id = ppf.person_id
1663: AND per.effective_start_date <= l_end_date
1664: AND per.effective_end_date >= l_start_date)
1665: AND paf.period_of_service_id = pps.period_of_service_id
1833: ),
1834: 0
1835: ) -- Q1_Previous_Emp_Tax
1836: FROM pay_assignment_actions paa,
1837: per_people_f ppf,
1838: per_periods_of_service pps,
1839: per_assignments_f paf,
1840: per_addresses pad,
1841: -- pay_ie_paye_details_f payef,
1858: AND asg.effective_start_date <= l_end_date
1859: AND asg.effective_end_date >= l_start_date)
1860: AND ppf.effective_end_date =
1861: (SELECT MAX (per.effective_end_date)
1862: FROM per_people_f per
1863: WHERE per.person_id = ppf.person_id
1864: AND per.effective_start_date <= l_end_date
1865: AND per.effective_end_date >= l_start_date)
1866: AND paf.period_of_service_id = pps.period_of_service_id
4670: IS
4671: SELECT /*+ ORDERED USE_NL(paa, ppa, ptp)
4672: fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
4673: paa.assignment_action_id),16)) assignment_action_id --bug fix 4004470
4674: FROM per_people_f ppf,
4675: per_assignments_f paf,
4676: pay_assignment_actions paa,
4677: pay_payroll_actions ppa,
4678: per_time_periods ptp
4715: MIN (ptp.end_date) minimum_effective_date,
4716: asg.primary_flag,
4717: paa.assignment_action_id,
4718: trim(rrv1.result_value) result_value
4719: FROM per_people_f per,
4720: per_assignments_f asg,
4721: pay_assignment_actions paa,
4722: pay_payroll_actions ppa,
4723: per_time_periods ptp,
4735: --AND asg.assignment_id = p_assignment_id -- vik added code for getting values for given assignment
4736: AND per.current_employee_flag = 'Y'
4737: AND per.effective_start_date =
4738: (SELECT MIN (per2.effective_start_date)
4739: FROM per_people_f per2
4740: -- Bug Fix 4004384
4741: , per_periods_of_service pos2
4742: WHERE per2.person_id = per.person_id
4743: AND per2.effective_start_date <= p_end_date
4835: 'Context Contribution Class', rrv1.result_value,
4836: '0'
4837: )
4838: ) class_name
4839: FROM per_people_f per,
4840: per_assignments_f asg,
4841: pay_assignment_actions paa,
4842: pay_payroll_actions ppa,
4843: per_time_periods ptp,
4848: WHERE per.person_id = p_person_id
4849: --ANd asg.assignment_id = p_assignment_id -- vik added code for getting values for given assignment
4850: AND per.effective_start_date =
4851: (SELECT MIN (per2.effective_start_date)
4852: FROM per_people_f per2
4853: WHERE per.person_id = per2.person_id
4854: AND per2.effective_start_date <= p_end_date
4855: AND NVL (per2.effective_end_date, p_end_date) >=
4856: p_start_date