[Home] [Help]
635: -- and have been terminated during the FBT year
636:
637: procedure assignment_action_code
638: (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
639: p_start_person_id in per_all_people_f.person_id%type,
640: p_end_person_id in per_all_people_f.person_id%type,
641: p_chunk in number) is
642:
643:
636:
637: procedure assignment_action_code
638: (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
639: p_start_person_id in per_all_people_f.person_id%type,
640: p_end_person_id in per_all_people_f.person_id%type,
641: p_chunk in number) is
642:
643:
644: v_next_action_id pay_assignment_actions.assignment_action_id%type;
683: ----------------------------------------------+
684: /* 4926521 Modified adn put group by in 2 sub queries for performacne */
685: /* 5099419 Removed fix for bug 4926521 */
686: Cursor process_assignments(c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
687: c_start_person_id in per_all_people_f.person_id%type,
688: c_end_person_id in per_all_people_f.person_id%type)
689: is
690: select /*+ INDEX(p per_people_f_pk)
691: INDEX(a per_assignments_f_fk1)
684: /* 4926521 Modified adn put group by in 2 sub queries for performacne */
685: /* 5099419 Removed fix for bug 4926521 */
686: Cursor process_assignments(c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
687: c_start_person_id in per_all_people_f.person_id%type,
688: c_end_person_id in per_all_people_f.person_id%type)
689: is
690: select /*+ INDEX(p per_people_f_pk)
691: INDEX(a per_assignments_f_fk1)
692: INDEX(a per_assignments_f_N12)
770: /* Cursor added for bug3019374 -- Processed when a single assignment is entered*/
771:
772:
773: Cursor process_assignments_only(c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
774: c_start_person_id in per_all_people_f.person_id%type,
775: c_end_person_id in per_all_people_f.person_id%type)
776: is
777: select distinct a.assignment_id
778: from per_people_f p /*Bug3043049*/
771:
772:
773: Cursor process_assignments_only(c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
774: c_start_person_id in per_all_people_f.person_id%type,
775: c_end_person_id in per_all_people_f.person_id%type)
776: is
777: select distinct a.assignment_id
778: from per_people_f p /*Bug3043049*/
779: ,per_assignments_f a /*Bug3043049*/
1133:
1134:
1135: ---------------------------------
1136: --Functions
1137: function get_max_effective_person_date (p_person_id per_all_people_f .person_id%type)
1138: return date
1139: is
1140: l_effective_date date ;
1141: cursor c_effective_date
1140: l_effective_date date ;
1141: cursor c_effective_date
1142: is
1143: select max(effective_start_date)
1144: from per_all_people_f p
1145: where person_id =p_person_id ;
1146: begin
1147: open c_effective_date;
1148: fetch c_effective_date into l_effective_date ;
1193: ff_database_items dbi
1194: where user_entity_name =c_user_entity_name
1195: and fue.user_entity_id =dbi.user_entity_id;
1196:
1197: v_full_name per_all_people_f.full_name%type;
1198: v_user_entity_id ff_user_entities.user_entity_id%type;
1199: v_archive_item_id ff_archive_items.archive_item_id%type;
1200: v_data_type ff_database_items.data_type%type;
1201: v_object_version_number ff_archive_items.object_version_number%type;
2665: , pay_input_values_f piv
2666: , pay_element_types_f pet
2667: , pay_element_entries_f pee
2668: , pay_element_links_f pel
2669: , per_all_people_f pap
2670: , per_periods_of_service pps
2671: , per_all_assignments_f paa
2672: where pet.element_type_id = piv.element_type_id
2673: and pbf.input_value_id = piv.input_value_id
2835: p_transitional_flag out nocopy varchar2, /*Bug 6192381 Added New Parameters p_transitional_flag and p_part_prev_etp_flag */
2836: p_part_prev_etp_flag out nocopy varchar2) is
2837:
2838:
2839: l_etp_last_name per_all_people_f.last_name%type;
2840: l_etp_first_name per_all_people_f.first_name%type;
2841: l_etp_middle_name per_all_people_f.middle_names%type;
2842: l_etp_address_1 hr_locations.address_line_1%type;
2843: l_etp_address_2 hr_locations.address_line_2%type;
2836: p_part_prev_etp_flag out nocopy varchar2) is
2837:
2838:
2839: l_etp_last_name per_all_people_f.last_name%type;
2840: l_etp_first_name per_all_people_f.first_name%type;
2841: l_etp_middle_name per_all_people_f.middle_names%type;
2842: l_etp_address_1 hr_locations.address_line_1%type;
2843: l_etp_address_2 hr_locations.address_line_2%type;
2844: l_etp_address_3 hr_locations.address_line_3%type;
2837:
2838:
2839: l_etp_last_name per_all_people_f.last_name%type;
2840: l_etp_first_name per_all_people_f.first_name%type;
2841: l_etp_middle_name per_all_people_f.middle_names%type;
2842: l_etp_address_1 hr_locations.address_line_1%type;
2843: l_etp_address_2 hr_locations.address_line_2%type;
2844: l_etp_address_3 hr_locations.address_line_3%type;
2845: l_etp_suburb hr_locations.town_or_city%type;
2845: l_etp_suburb hr_locations.town_or_city%type;
2846: l_etp_state hr_locations.region_1%type;
2847: l_etp_postcode hr_locations.postal_code%type;
2848: l_etp_country fnd_territories_tl.territory_short_name%type;
2849: l_etp_employee_number per_all_people_f.employee_number%type;
2850: l_etp_date_of_birth per_all_people_f.date_of_birth%type;
2851: l_etp_employee_start_date per_periods_of_service.date_start%type;
2852: l_etp_death_benefit per_periods_of_service.leaving_reason%type;
2853: l_asgmnt_loc hr_locations.location_code%type;
2846: l_etp_state hr_locations.region_1%type;
2847: l_etp_postcode hr_locations.postal_code%type;
2848: l_etp_country fnd_territories_tl.territory_short_name%type;
2849: l_etp_employee_number per_all_people_f.employee_number%type;
2850: l_etp_date_of_birth per_all_people_f.date_of_birth%type;
2851: l_etp_employee_start_date per_periods_of_service.date_start%type;
2852: l_etp_death_benefit per_periods_of_service.leaving_reason%type;
2853: l_asgmnt_loc hr_locations.location_code%type;
2854: l_emp_no per_all_people_f.employee_number%type;
2850: l_etp_date_of_birth per_all_people_f.date_of_birth%type;
2851: l_etp_employee_start_date per_periods_of_service.date_start%type;
2852: l_etp_death_benefit per_periods_of_service.leaving_reason%type;
2853: l_asgmnt_loc hr_locations.location_code%type;
2854: l_emp_no per_all_people_f.employee_number%type;
2855: l_payroll pay_all_payrolls_f.payroll_name%type;
2856: l_emp_type per_all_people_f.current_employee_flag%type;
2857: l_address_date_from date;
2858:
2852: l_etp_death_benefit per_periods_of_service.leaving_reason%type;
2853: l_asgmnt_loc hr_locations.location_code%type;
2854: l_emp_no per_all_people_f.employee_number%type;
2855: l_payroll pay_all_payrolls_f.payroll_name%type;
2856: l_emp_type per_all_people_f.current_employee_flag%type;
2857: l_address_date_from date;
2858:
2859: -----------------------------------------------------------------------------------------------+
2860: -- cursor to fetch terminated employees details
2894: pay_element_links_f pel,
2895: pay_element_entries_f pee,
2896: pay_element_entry_values_f pev,
2897: per_all_assignments_f paa,
2898: per_all_people_f pap,
2899: per_addresses pad,
2900: fnd_territories_tl fta,
2901: per_periods_of_service pps,
2902: pay_payroll_actions ppa,
3565: from hr_organization_information hoi
3566: ,hr_locations hlc
3567: ,fnd_territories_tl ftl
3568: ,hr_organization_units hou
3569: ,per_all_people_f papcont
3570: ,per_all_people_f papsign
3571: where hou.business_group_id = p_business_group_id
3572: and hou.organization_id = p_registered_employer
3573: and hou.organization_id = hoi.organization_id
3566: ,hr_locations hlc
3567: ,fnd_territories_tl ftl
3568: ,hr_organization_units hou
3569: ,per_all_people_f papcont
3570: ,per_all_people_f papsign
3571: where hou.business_group_id = p_business_group_id
3572: and hou.organization_id = p_registered_employer
3573: and hou.organization_id = hoi.organization_id
3574: and hoi.org_information_context = 'AU_LEGAL_EMPLOYER'
3576: and ftl.language = userenv('LANG')
3577: and hlc.location_id = hou.location_id
3578: and papcont.person_id = hoi.org_information7
3579: and papcont.effective_start_date = (select max(effective_start_date)
3580: from per_all_people_f p
3581: where papcont.person_id=p.person_id)
3582: and papsign.person_id = hoi.org_information8
3583: and papsign.effective_start_date = (select max(effective_start_date)
3584: from per_all_people_f p
3580: from per_all_people_f p
3581: where papcont.person_id=p.person_id)
3582: and papsign.person_id = hoi.org_information8
3583: and papsign.effective_start_date = (select max(effective_start_date)
3584: from per_all_people_f p
3585: where papsign.person_id=p.person_id);
3586:
3587:
3588: Begin
3744: p_end_date_flag in varchar2,
3745: p_fbt_year_start IN pay_payroll_Actions.effective_date%type) is /*Bug# 4653934*/
3746:
3747: l_tfn_no pay_element_entry_values_f.screen_entry_value%type;
3748: l_first_name per_all_people_f.first_name%type;
3749: l_middle_name per_all_people_f.middle_names%type;
3750: l_surname per_all_people_f.last_name%type;
3751: l_address_1 hr_locations.address_line_1%type;
3752: l_address_2 hr_locations.address_line_2%type;
3745: p_fbt_year_start IN pay_payroll_Actions.effective_date%type) is /*Bug# 4653934*/
3746:
3747: l_tfn_no pay_element_entry_values_f.screen_entry_value%type;
3748: l_first_name per_all_people_f.first_name%type;
3749: l_middle_name per_all_people_f.middle_names%type;
3750: l_surname per_all_people_f.last_name%type;
3751: l_address_1 hr_locations.address_line_1%type;
3752: l_address_2 hr_locations.address_line_2%type;
3753: l_address_3 hr_locations.address_line_3%type;
3746:
3747: l_tfn_no pay_element_entry_values_f.screen_entry_value%type;
3748: l_first_name per_all_people_f.first_name%type;
3749: l_middle_name per_all_people_f.middle_names%type;
3750: l_surname per_all_people_f.last_name%type;
3751: l_address_1 hr_locations.address_line_1%type;
3752: l_address_2 hr_locations.address_line_2%type;
3753: l_address_3 hr_locations.address_line_3%type;
3754: l_suburb hr_locations.town_or_city%type;
3758: l_start_date VARCHAR2(20);
3759: l_termination_date VARCHAR2(20);
3760: l_dob VARCHAR2(20);
3761: l_asgmnt_loc hr_locations.location_code%type;
3762: l_emp_no per_all_people_f.employee_number%type;
3763: l_payroll pay_all_payrolls_f.payroll_name%type;
3764: l_emp_type per_all_people_f.current_employee_flag%type;
3765: l_address_date_from date;
3766: l_date_earned pay_payroll_actions.date_earned%type;
3760: l_dob VARCHAR2(20);
3761: l_asgmnt_loc hr_locations.location_code%type;
3762: l_emp_no per_all_people_f.employee_number%type;
3763: l_payroll pay_all_payrolls_f.payroll_name%type;
3764: l_emp_type per_all_people_f.current_employee_flag%type;
3765: l_address_date_from date;
3766: l_date_earned pay_payroll_actions.date_earned%type;
3767: l_effective_date date; -- Bug3263659
3768: l_actual_termination_date per_periods_of_service.actual_termination_date%type;
3866: hr_soft_coding_keyflex hsc,
3867: hr_locations hlc, /* Bug No : 2263587 */
3868: per_all_assignments_f paa,
3869: per_all_assignments_f paaf, /* Bug : 2610141 */
3870: per_all_people_f pap,
3871: per_addresses pad,
3872: fnd_territories_tl fta,
3873: per_periods_of_service pps,
3874: pay_payroll_actions ppa,
3918: and nvl(pps.actual_termination_date,p_year_end)
3919: between a.effective_Start_date and pap.effective_end_date /*2689175*/
3920: and a.effective_end_date >= least(nvl(pps.actual_termination_date,p_year_start),p_year_start))--Added for bug 4177679,4299506
3921: and pap.effective_start_date = (select max(effective_Start_date)
3922: from per_all_people_f p
3923: where p.person_id = pap.person_id
3924: and nvl(pps.actual_termination_date,p_year_end) between p.effective_Start_date and p.effective_end_date)
3925: ORDER BY pad.date_from DESC;/*Bug2977533*/
3926:
4269: l_report_end_date varchar2(20);
4270: l_supplier_number hr_organization_information.org_information1%type;
4271: l_supplier_name hr_organization_information.org_information3%type;
4272: l_supplier_abn hr_organization_information.org_information5%type;
4273: l_supplier_contact_name per_all_people_f.full_name%type;
4274: l_supplier_contact_phone per_addresses.telephone_number_1%type;
4275: l_supplier_address_1 hr_locations.address_line_1%type;
4276: l_supplier_address_2 hr_locations.address_line_2%type;
4277: l_supplier_address_3 hr_locations.address_line_3%type;
4303: from hr_organization_information hoi
4304: ,hr_organization_units hou
4305: ,hr_locations hrl
4306: ,fnd_territories_tl ftl
4307: ,per_all_people_f pap
4308: where hou.business_group_id = p_business_group_id
4309: and hou.organization_id = p_registered_employer
4310: and hou.organization_id = hoi.organization_id
4311: and hoi.org_information_context = 'AU_LEGAL_EMPLOYER'
4313: and ftl.territory_code = hrl.country
4314: and ftl.language = userenv('LANG')
4315: and hoi.org_information7 = pap.person_id
4316: and pap.effective_start_date = (select max(effective_start_date)
4317: from per_all_people_f p
4318: where pap.person_id=p.person_id);
4319:
4320: Begin
4321: IF g_debug THEN
4567: l_current_le hr_organization_units.organization_id%type; --4363057
4568: l_payroll_action_id pay_payroll_actions.payroll_action_id%type ;
4569: l_year_start pay_payroll_Actions.effective_date%type;
4570: l_year_end pay_payroll_actions.effective_date%type;
4571: l_employee_type per_all_people_f.current_Employee_Flag%type;
4572: l_current_employee_flag per_all_people_f.current_employee_flag%type :='Y';
4573: l_actual_termination_date per_periods_of_service.actual_termination_date%TYPE;
4574: l_date_start per_periods_of_service.date_start%TYPE;
4575: l_asg_start pay_payroll_actions.effective_date%type;
4568: l_payroll_action_id pay_payroll_actions.payroll_action_id%type ;
4569: l_year_start pay_payroll_Actions.effective_date%type;
4570: l_year_end pay_payroll_actions.effective_date%type;
4571: l_employee_type per_all_people_f.current_Employee_Flag%type;
4572: l_current_employee_flag per_all_people_f.current_employee_flag%type :='Y';
4573: l_actual_termination_date per_periods_of_service.actual_termination_date%TYPE;
4574: l_date_start per_periods_of_service.date_start%TYPE;
4575: l_asg_start pay_payroll_actions.effective_date%type;
4576: l_asg_end pay_payroll_actions.effective_date%type;
4634: date_start,
4635: pps.pds_information2,
4636: to_number(substr(max(lpad(ppa.action_sequence,15,'0')||ppa.assignment_action_id),16)), --3755305
4637: pps.final_process_date final_process_date --3263659
4638: from per_all_people_f p,
4639: per_all_assignments_f a,
4640: per_periods_of_service pps,
4641: pay_all_payrolls_f papf, --4281290
4642: pay_payroll_actions pa,
4661: )
4662: )
4663: and a.assignment_id = c_assignment_id
4664: and p.effective_start_date = (select max(pp.effective_start_date)
4665: from per_all_people_f pp
4666: where p.person_id = pp.person_id
4667: and p.business_group_id = c_business_group_id
4668: ) -- Bug 2856638
4669: and a.effective_start_date = (select max(aa.effective_start_date)