[Home] [Help]
1102: -- and have been terminated during the FBT year
1103:
1104: procedure assignment_action_code
1105: (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
1106: p_start_person_id in per_all_people_f.person_id%type,
1107: p_end_person_id in per_all_people_f.person_id%type,
1108: p_chunk in number) is
1109:
1110:
1103:
1104: procedure assignment_action_code
1105: (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
1106: p_start_person_id in per_all_people_f.person_id%type,
1107: p_end_person_id in per_all_people_f.person_id%type,
1108: p_chunk in number) is
1109:
1110:
1111: v_next_action_id pay_assignment_actions.assignment_action_id%type;
1168: ----------------------------------------------+
1169: /* 4926521 Modified adn put group by in 2 sub queries for performacne */
1170: /* 5099419 Removed fix for bug 4926521 */
1171: Cursor process_assignments(c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
1172: c_start_person_id in per_all_people_f.person_id%type,
1173: c_end_person_id in per_all_people_f.person_id%type)
1174: is
1175: select /*+ INDEX(p per_people_f_pk)
1176: INDEX(a per_assignments_f_fk1)
1169: /* 4926521 Modified adn put group by in 2 sub queries for performacne */
1170: /* 5099419 Removed fix for bug 4926521 */
1171: Cursor process_assignments(c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
1172: c_start_person_id in per_all_people_f.person_id%type,
1173: c_end_person_id in per_all_people_f.person_id%type)
1174: is
1175: select /*+ INDEX(p per_people_f_pk)
1176: INDEX(a per_assignments_f_fk1)
1177: INDEX(a per_assignments_f_N12)
1268: /* Cursor added for bug3019374 -- Processed when a single assignment is entered*/
1269:
1270:
1271: Cursor process_assignments_only(c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
1272: c_start_person_id in per_all_people_f.person_id%type,
1273: c_end_person_id in per_all_people_f.person_id%type)
1274: is
1275: select distinct a.assignment_id
1276: from per_people_f p /*Bug3043049*/
1269:
1270:
1271: Cursor process_assignments_only(c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
1272: c_start_person_id in per_all_people_f.person_id%type,
1273: c_end_person_id in per_all_people_f.person_id%type)
1274: is
1275: select distinct a.assignment_id
1276: from per_people_f p /*Bug3043049*/
1277: ,per_assignments_f a /*Bug3043049*/
1604: );
1605:
1606: /* Bug 13043341 Process assignment set when RANGE_PERSON_ID feature is diabled */
1607: Cursor process_assignment_set(c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
1608: c_start_person_id in per_all_people_f.person_id%type,
1609: c_end_person_id in per_all_people_f.person_id%type)
1610: is
1611: select /*+ INDEX(p per_people_f_pk)
1612: INDEX(a per_assignments_f_fk1)
1605:
1606: /* Bug 13043341 Process assignment set when RANGE_PERSON_ID feature is diabled */
1607: Cursor process_assignment_set(c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
1608: c_start_person_id in per_all_people_f.person_id%type,
1609: c_end_person_id in per_all_people_f.person_id%type)
1610: is
1611: select /*+ INDEX(p per_people_f_pk)
1612: INDEX(a per_assignments_f_fk1)
1613: INDEX(a per_assignments_f_N12)
1997:
1998:
1999: ---------------------------------
2000: --Functions
2001: function get_max_effective_person_date (p_person_id per_all_people_f .person_id%type)
2002: return date
2003: is
2004: l_effective_date date ;
2005: cursor c_effective_date
2004: l_effective_date date ;
2005: cursor c_effective_date
2006: is
2007: select max(effective_start_date)
2008: from per_all_people_f p
2009: where person_id =p_person_id ;
2010: begin
2011: open c_effective_date;
2012: fetch c_effective_date into l_effective_date ;
2057: ff_database_items dbi
2058: where user_entity_name =c_user_entity_name
2059: and fue.user_entity_id =dbi.user_entity_id;
2060:
2061: v_full_name per_all_people_f.full_name%type;
2062: v_user_entity_id ff_user_entities.user_entity_id%type;
2063: v_archive_item_id ff_archive_items.archive_item_id%type;
2064: v_data_type ff_database_items.data_type%type;
2065: v_object_version_number ff_archive_items.object_version_number%type;
3308: , pay_input_values_f piv
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
4937: , pay_input_values_f piv
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
5113: p_transitional_flag out nocopy varchar2, /*Bug 6192381 Added New Parameters p_transitional_flag and p_part_prev_etp_flag */
5114: p_part_prev_etp_flag out nocopy varchar2) is
5115:
5116:
5117: l_etp_last_name per_all_people_f.last_name%type;
5118: l_etp_first_name per_all_people_f.first_name%type;
5119: l_etp_middle_name per_all_people_f.middle_names%type;
5120: l_etp_address_1 hr_locations.address_line_1%type;
5121: l_etp_address_2 hr_locations.address_line_2%type;
5114: p_part_prev_etp_flag out nocopy varchar2) is
5115:
5116:
5117: l_etp_last_name per_all_people_f.last_name%type;
5118: l_etp_first_name per_all_people_f.first_name%type;
5119: l_etp_middle_name per_all_people_f.middle_names%type;
5120: l_etp_address_1 hr_locations.address_line_1%type;
5121: l_etp_address_2 hr_locations.address_line_2%type;
5122: l_etp_address_3 hr_locations.address_line_3%type;
5115:
5116:
5117: l_etp_last_name per_all_people_f.last_name%type;
5118: l_etp_first_name per_all_people_f.first_name%type;
5119: l_etp_middle_name per_all_people_f.middle_names%type;
5120: l_etp_address_1 hr_locations.address_line_1%type;
5121: l_etp_address_2 hr_locations.address_line_2%type;
5122: l_etp_address_3 hr_locations.address_line_3%type;
5123: l_etp_suburb hr_locations.town_or_city%type;
5123: l_etp_suburb hr_locations.town_or_city%type;
5124: l_etp_state hr_locations.region_1%type;
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;
5124: l_etp_state hr_locations.region_1%type;
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;
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;
5135: l_address_date_from date;
5136:
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;
5135: l_address_date_from date;
5136:
5137: -----------------------------------------------------------------------------------------------+
5138: -- cursor to fetch terminated employees details
5174: pay_element_links_f pel,
5175: pay_element_entries_f pee,
5176: pay_element_entry_values_f pev,
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,
7008: from hr_organization_information hoi
7009: ,hr_locations hlc
7010: ,fnd_territories_tl ftl
7011: ,hr_organization_units hou
7012: ,per_all_people_f papcont
7013: ,per_all_people_f papsign
7014: where hou.business_group_id = p_business_group_id
7015: and hou.organization_id = p_registered_employer
7016: and hou.organization_id = hoi.organization_id
7009: ,hr_locations hlc
7010: ,fnd_territories_tl ftl
7011: ,hr_organization_units hou
7012: ,per_all_people_f papcont
7013: ,per_all_people_f papsign
7014: where hou.business_group_id = p_business_group_id
7015: and hou.organization_id = p_registered_employer
7016: and hou.organization_id = hoi.organization_id
7017: and hoi.org_information_context = 'AU_LEGAL_EMPLOYER'
7019: and ftl.language = userenv('LANG')
7020: and hlc.location_id = hou.location_id
7021: and papcont.person_id = hoi.org_information7
7022: and papcont.effective_start_date = (select max(effective_start_date)
7023: from per_all_people_f p
7024: where papcont.person_id=p.person_id)
7025: and papsign.person_id = hoi.org_information8
7026: and papsign.effective_start_date = (select max(effective_start_date)
7027: from per_all_people_f p
7023: from per_all_people_f p
7024: where papcont.person_id=p.person_id)
7025: and papsign.person_id = hoi.org_information8
7026: and papsign.effective_start_date = (select max(effective_start_date)
7027: from per_all_people_f p
7028: where papsign.person_id=p.person_id);
7029:
7030:
7031: Begin
7187: p_end_date_flag in varchar2,
7188: p_fbt_year_start IN pay_payroll_Actions.effective_date%type) is /*Bug# 4653934*/
7189:
7190: l_tfn_no pay_element_entry_values_f.screen_entry_value%type;
7191: l_first_name per_all_people_f.first_name%type;
7192: l_middle_name per_all_people_f.middle_names%type;
7193: l_surname per_all_people_f.last_name%type;
7194: l_address_1 hr_locations.address_line_1%type;
7195: l_address_2 hr_locations.address_line_2%type;
7188: p_fbt_year_start IN pay_payroll_Actions.effective_date%type) is /*Bug# 4653934*/
7189:
7190: l_tfn_no pay_element_entry_values_f.screen_entry_value%type;
7191: l_first_name per_all_people_f.first_name%type;
7192: l_middle_name per_all_people_f.middle_names%type;
7193: l_surname per_all_people_f.last_name%type;
7194: l_address_1 hr_locations.address_line_1%type;
7195: l_address_2 hr_locations.address_line_2%type;
7196: l_address_3 hr_locations.address_line_3%type;
7189:
7190: l_tfn_no pay_element_entry_values_f.screen_entry_value%type;
7191: l_first_name per_all_people_f.first_name%type;
7192: l_middle_name per_all_people_f.middle_names%type;
7193: l_surname per_all_people_f.last_name%type;
7194: l_address_1 hr_locations.address_line_1%type;
7195: l_address_2 hr_locations.address_line_2%type;
7196: l_address_3 hr_locations.address_line_3%type;
7197: l_suburb hr_locations.town_or_city%type;
7201: l_start_date VARCHAR2(20);
7202: l_termination_date VARCHAR2(20);
7203: l_dob VARCHAR2(20);
7204: l_asgmnt_loc hr_locations.location_code%type;
7205: l_emp_no per_all_people_f.employee_number%type;
7206: l_payroll pay_all_payrolls_f.payroll_name%type;
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;
7203: l_dob VARCHAR2(20);
7204: l_asgmnt_loc hr_locations.location_code%type;
7205: l_emp_no per_all_people_f.employee_number%type;
7206: l_payroll pay_all_payrolls_f.payroll_name%type;
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;
7311: hr_soft_coding_keyflex hsc,
7312: hr_locations hlc, /* Bug No : 2263587 */
7313: per_all_assignments_f paa,
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,
7363: and nvl(pps.actual_termination_date,p_year_end)
7364: between a.effective_Start_date and pap.effective_end_date /*2689175*/
7365: and a.effective_end_date >= least(nvl(pps.actual_termination_date,p_year_start),p_year_start))--Added for bug 4177679,4299506
7366: and pap.effective_start_date = (select max(effective_Start_date)
7367: from per_all_people_f p
7368: where p.person_id = pap.person_id
7369: and nvl(pps.actual_termination_date,p_year_end) between p.effective_Start_date and p.effective_end_date)
7370: ORDER BY pad.date_from DESC;/*Bug2977533*/
7371:
7731: l_report_end_date varchar2(20);
7732: l_supplier_number hr_organization_information.org_information1%type;
7733: l_supplier_name hr_organization_information.org_information3%type;
7734: l_supplier_abn hr_organization_information.org_information5%type;
7735: l_supplier_contact_name per_all_people_f.full_name%type;
7736: l_supplier_contact_phone per_addresses.telephone_number_1%type;
7737: l_supplier_address_1 hr_locations.address_line_1%type;
7738: l_supplier_address_2 hr_locations.address_line_2%type;
7739: l_supplier_address_3 hr_locations.address_line_3%type;
7765: from hr_organization_information hoi
7766: ,hr_organization_units hou
7767: ,hr_locations hrl
7768: ,fnd_territories_tl ftl
7769: ,per_all_people_f pap
7770: where hou.business_group_id = p_business_group_id
7771: and hou.organization_id = p_registered_employer
7772: and hou.organization_id = hoi.organization_id
7773: and hoi.org_information_context = 'AU_LEGAL_EMPLOYER'
7775: and ftl.territory_code = hrl.country
7776: and ftl.language = userenv('LANG')
7777: and hoi.org_information7 = pap.person_id
7778: and pap.effective_start_date = (select max(effective_start_date)
7779: from per_all_people_f p
7780: where pap.person_id=p.person_id);
7781:
7782: Begin
7783: IF g_debug THEN
8029: l_current_le hr_organization_units.organization_id%type; --4363057
8030: l_payroll_action_id pay_payroll_actions.payroll_action_id%type ;
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;
8030: l_payroll_action_id pay_payroll_actions.payroll_action_id%type ;
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;
8099: date_start,
8100: pps.pds_information2,
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,
8130: )
8131: )
8132: and a.assignment_id = c_assignment_id
8133: and p.effective_start_date = (select max(pp.effective_start_date)
8134: from per_all_people_f pp
8135: where p.person_id = pp.person_id
8136: and p.business_group_id = c_business_group_id
8137: ) -- Bug 2856638
8138: and a.effective_start_date = (select max(aa.effective_start_date)