[Home] [Help]
1175: select /*+ INDEX(p per_people_f_pk)
1176: INDEX(a per_assignments_f_fk1)
1177: INDEX(a per_assignments_f_N12)
1178: INDEX(pa pay_payroll_actions_pk)
1179: INDEX(pps per_periods_of_service_n3)
1180: */ distinct a.assignment_id
1181: from per_people_f p /*Bug3043049*/
1182: ,per_assignments_f a /*Bug3043049*/
1183: ,pay_payroll_actions pa
1180: */ distinct a.assignment_id
1181: from per_people_f p /*Bug3043049*/
1182: ,per_assignments_f a /*Bug3043049*/
1183: ,pay_payroll_actions pa
1184: ,per_periods_of_service pps
1185: where pa.payroll_action_id = c_payroll_action_id
1186: and p.person_id between c_start_person_id and c_end_person_id
1187: and p.person_id = a.person_id
1188: and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (v_fin_year_end)),1,'Y','N')) LIKE v_employee_type --Bug#3744930
1275: select distinct a.assignment_id
1276: from per_people_f p /*Bug3043049*/
1277: ,per_assignments_f a /*Bug3043049*/
1278: ,pay_payroll_actions pa
1279: ,per_periods_of_service pps
1280: where pa.payroll_action_id = c_payroll_action_id
1281: and p.person_id between c_start_person_id and c_end_person_id
1282: and p.person_id = a.person_id
1283: and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (v_fin_year_end)),1,'Y','N')) LIKE v_employee_type ----Bug#3744930
1378: SELECT a.assignment_id
1379: FROM per_people_f p /*Bug3043049*/
1380: ,per_assignments_f a /*Bug3043049*/
1381: ,pay_payroll_actions pa
1382: ,per_periods_of_service pps
1383: ,pay_population_ranges ppr
1384: WHERE pa.payroll_action_id = c_payroll_action_id
1385: AND pa.payroll_action_id = ppr.payroll_action_id
1386: AND ppr.payroll_action_id = c_payroll_action_id
1514: SELECT a.assignment_id
1515: FROM per_people_f p
1516: ,per_assignments_f a
1517: ,pay_payroll_actions pa
1518: ,per_periods_of_service pps
1519: ,pay_population_ranges ppr
1520: ,hr_assignment_set_amendments hasa
1521: WHERE pa.payroll_action_id = c_payroll_action_id
1522: AND pa.payroll_action_id = ppr.payroll_action_id
1611: select /*+ INDEX(p per_people_f_pk)
1612: INDEX(a per_assignments_f_fk1)
1613: INDEX(a per_assignments_f_N12)
1614: INDEX(pa pay_payroll_actions_pk)
1615: INDEX(pps per_periods_of_service_n3)
1616: */ distinct a.assignment_id
1617: from per_people_f p /*Bug3043049*/
1618: ,per_assignments_f a /*Bug3043049*/
1619: ,pay_payroll_actions pa
1616: */ distinct a.assignment_id
1617: from per_people_f p /*Bug3043049*/
1618: ,per_assignments_f a /*Bug3043049*/
1619: ,pay_payroll_actions pa
1620: ,per_periods_of_service pps
1621: ,hr_assignment_set_amendments hasa
1622: where pa.payroll_action_id = c_payroll_action_id
1623: and p.person_id between c_start_person_id and c_end_person_id
1624: and p.person_id = a.person_id
2713: pee.assignment_id assignment_id,
2714: pac.tax_unit_id, /* Added for bug #5846278 */
2715: pdb.balance_type_id
2716: FROM per_all_assignments_f paa
2717: ,per_periods_of_service pps
2718: ,pay_assignment_actions pac
2719: ,pay_payroll_actions ppa
2720: ,pay_element_entries_f pee
2721: ,pay_run_results prr
3283: ,p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
3284: ,p_year_start in DATE
3285: ,p_year_end in DATE
3286: ,p_fw_check in varchar2
3287: ,p_actual_termination_date in per_periods_of_service.actual_termination_date%TYPE
3288: ,p_fw_alw_bal_exist in varchar2
3289: ) is
3290:
3291: cursor get_balance_name (c_defined_balance_id pay_defined_balances.defined_balance_id%type) is
3309: , pay_element_types_f pet
3310: , pay_element_entries_f pee
3311: , pay_element_links_f pel
3312: , per_all_people_f pap
3313: , per_periods_of_service pps
3314: , per_all_assignments_f paa
3315: where pet.element_type_id = piv.element_type_id
3316: and pbf.input_value_id = piv.input_value_id
3317: and pbf.balance_type_id = pbt.balance_type_id
4532: c_year_start date ,
4533: c_year_end date )
4534: is select to_char(pps.actual_termination_date,'DDMMYYYY')
4535: from per_all_assignments_f paa,
4536: per_periods_of_service pps
4537: where paa.person_id = pps.person_id
4538: and paa.period_of_service_id = pps.period_of_service_id
4539: and paa.assignment_id = c_assignment_id
4540: and pps.actual_termination_date between paa.effective_start_date
4938: , pay_element_types_f pet
4939: , pay_element_entries_f pee
4940: , pay_element_links_f pel
4941: , per_all_people_f pap
4942: , per_periods_of_service pps
4943: , per_all_assignments_f paa
4944: where pet.element_type_id = piv.element_type_id
4945: and pbf.input_value_id = piv.input_value_id
4946: and pbf.balance_type_id = pbt.balance_type_id
5125: l_etp_postcode hr_locations.postal_code%type;
5126: l_etp_country fnd_territories_tl.territory_short_name%type;
5127: l_etp_employee_number per_all_people_f.employee_number%type;
5128: l_etp_date_of_birth per_all_people_f.date_of_birth%type;
5129: l_etp_employee_start_date per_periods_of_service.date_start%type;
5130: l_etp_death_benefit per_periods_of_service.leaving_reason%type;
5131: l_asgmnt_loc hr_locations.location_code%type;
5132: l_emp_no per_all_people_f.employee_number%type;
5133: l_payroll pay_all_payrolls_f.payroll_name%type;
5126: l_etp_country fnd_territories_tl.territory_short_name%type;
5127: l_etp_employee_number per_all_people_f.employee_number%type;
5128: l_etp_date_of_birth per_all_people_f.date_of_birth%type;
5129: l_etp_employee_start_date per_periods_of_service.date_start%type;
5130: l_etp_death_benefit per_periods_of_service.leaving_reason%type;
5131: l_asgmnt_loc hr_locations.location_code%type;
5132: l_emp_no per_all_people_f.employee_number%type;
5133: l_payroll pay_all_payrolls_f.payroll_name%type;
5134: l_emp_type per_all_people_f.current_employee_flag%type;
5177: per_all_assignments_f paa,
5178: per_all_people_f pap,
5179: per_addresses pad,
5180: fnd_territories_tl fta,
5181: per_periods_of_service pps,
5182: pay_payroll_actions ppa,
5183: pay_assignment_actions pac,
5184: hr_locations_all hlc /* Bug No : 2263587 */
5185: where hou.business_group_id = c_business_group_id
5445: ,p_registered_employer in pay_assignment_actions.TAX_UNIT_ID%TYPE --2610141
5446: ,p_legislation_code in pay_defined_balances.legislation_code%TYPE
5447: ,p_assignment_id in pay_assignment_actions.ASSIGNMENT_ID%type
5448: ,p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE
5449: ,p_actual_termination_date in per_periods_of_service.actual_termination_date%TYPE
5450: ,p_date_start in per_periods_of_service.date_start%TYPE
5451: ,p_year_start in pay_payroll_actions.effective_date%type
5452: ,p_year_end in pay_payroll_Actions.effective_date%type
5453: ,p_transitional_flag in varchar2 /*Bug 6192381 Added New Parameters p_transitional_flag and p_part_prev_etp_flag */
5446: ,p_legislation_code in pay_defined_balances.legislation_code%TYPE
5447: ,p_assignment_id in pay_assignment_actions.ASSIGNMENT_ID%type
5448: ,p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE
5449: ,p_actual_termination_date in per_periods_of_service.actual_termination_date%TYPE
5450: ,p_date_start in per_periods_of_service.date_start%TYPE
5451: ,p_year_start in pay_payroll_actions.effective_date%type
5452: ,p_year_end in pay_payroll_Actions.effective_date%type
5453: ,p_transitional_flag in varchar2 /*Bug 6192381 Added New Parameters p_transitional_flag and p_part_prev_etp_flag */
5454: ,p_part_prev_etp_flag in varchar2) is
5982: ,p_registered_employer in pay_assignment_actions.TAX_UNIT_ID%TYPE --2610141
5983: ,p_legislation_code in pay_defined_balances.legislation_code%TYPE
5984: ,p_assignment_id in pay_assignment_actions.ASSIGNMENT_ID%type
5985: ,p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE
5986: ,p_actual_termination_date in per_periods_of_service.actual_termination_date%TYPE
5987: ,p_date_start in per_periods_of_service.date_start%TYPE
5988: ,p_year_start in pay_payroll_actions.effective_date%type
5989: ,p_year_end in pay_payroll_Actions.effective_date%type) is
5990:
5983: ,p_legislation_code in pay_defined_balances.legislation_code%TYPE
5984: ,p_assignment_id in pay_assignment_actions.ASSIGNMENT_ID%type
5985: ,p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE
5986: ,p_actual_termination_date in per_periods_of_service.actual_termination_date%TYPE
5987: ,p_date_start in per_periods_of_service.date_start%TYPE
5988: ,p_year_start in pay_payroll_actions.effective_date%type
5989: ,p_year_end in pay_payroll_Actions.effective_date%type) is
5990:
5991: cursor c_get_etp_deduction is
7207: l_emp_type per_all_people_f.current_employee_flag%type;
7208: l_address_date_from date;
7209: l_date_earned pay_payroll_actions.date_earned%type;
7210: l_effective_date date; -- Bug3263659
7211: l_actual_termination_date per_periods_of_service.actual_termination_date%type;
7212: l_final_process_date date; -- Bug3263659
7213: l_fpd_archive VARCHAR2(20); -- Bug3098353
7214: l_le_start_date VARCHAR2(20);
7215: l_le_end_date VARCHAR2(20); -- Bug 2610141
7314: per_all_assignments_f paaf, /* Bug : 2610141 */
7315: per_all_people_f pap,
7316: per_addresses pad,
7317: fnd_territories_tl fta,
7318: per_periods_of_service pps,
7319: pay_payroll_actions ppa,
7320: pay_assignment_actions pac,
7321: pay_payroll_actions ppa1, /* Bug# 2448441 */
7322: pay_assignment_actions pac1 /* Bug# 2448441 */
8031: l_year_start pay_payroll_Actions.effective_date%type;
8032: l_year_end pay_payroll_actions.effective_date%type;
8033: l_employee_type per_all_people_f.current_Employee_Flag%type;
8034: l_current_employee_flag per_all_people_f.current_employee_flag%type :='Y';
8035: l_actual_termination_date per_periods_of_service.actual_termination_date%TYPE;
8036: l_date_start per_periods_of_service.date_start%TYPE;
8037: l_asg_start pay_payroll_actions.effective_date%type;
8038: l_asg_end pay_payroll_actions.effective_date%type;
8039: l_effective_date pay_payroll_actions.effective_date%type;
8032: l_year_end pay_payroll_actions.effective_date%type;
8033: l_employee_type per_all_people_f.current_Employee_Flag%type;
8034: l_current_employee_flag per_all_people_f.current_employee_flag%type :='Y';
8035: l_actual_termination_date per_periods_of_service.actual_termination_date%TYPE;
8036: l_date_start per_periods_of_service.date_start%TYPE;
8037: l_asg_start pay_payroll_actions.effective_date%type;
8038: l_asg_end pay_payroll_actions.effective_date%type;
8039: l_effective_date pay_payroll_actions.effective_date%type;
8040: l_death_benefit_type varchar2(100);
8044: l_fbt_assignment_action_id pay_assignment_actions.assignment_action_id%type; --2610141
8045: lump_sum_c_found boolean := false;
8046: l_final_process_date date; --263659
8047: l_term_date varchar2(10); --3263659
8048: l_fetched_termination_date per_periods_of_service.actual_termination_date%TYPE; --3263659
8049: l_reporting_flag varchar2(5) := 'YES'; --3098353
8050: l_bal_value varchar2(20); --3098353
8051: l_alw_bal_exist varchar2(20); --3098353
8052: l_lst_yr_term varchar2(10); --3661230
8101: to_number(substr(max(lpad(ppa.action_sequence,15,'0')||ppa.assignment_action_id),16)), --3755305
8102: pps.final_process_date final_process_date --3263659
8103: from per_all_people_f p,
8104: per_all_assignments_f a,
8105: per_periods_of_service pps,
8106: pay_all_payrolls_f papf, --4281290
8107: pay_payroll_actions pa,
8108: pay_assignment_actions ppa
8109: where a.person_id = p.person_id
9643: ppa.date_earned date_earned,
9644: ppa.effective_date,
9645: pec.classification_name
9646: FROM per_all_assignments_f paa
9647: ,per_periods_of_service pps
9648: ,pay_assignment_actions pac
9649: ,pay_payroll_actions ppa
9650: ,pay_element_entries_f pee
9651: ,pay_run_results prr