[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
70.1 12-JAN-93 SZWILLIA Changed SELECTS to DISTINCT
where necessary.
70.3 19-JAN-93 SZWILLIA Changed status type selects
to UNIONS with amendments.
70.4 20-JAN-93 SZWILLIA Corrected error handling and
warning setting.
70.6 04-MAR-93 SZWILLIA Changed parameters to DATEs.
70.7 10-MAR-93 SZWILLIA Changed parameter lists for
employees and applicants.
Made significant changes to
allow for vacancies and locations.
Changed insert_assignment to
perform third party population
of letters and budget values.
70.8 11-MAR-93 NKHAN Added 'exit' at the end
70.9 18-MAR-93 SZWILLIA Changed interface to create_applicant
create_employee and
create_secondary_assign.
70.10 19-MAR-93 SZWILLIA Minor corrections.
70.11 19-MAR-93 SZWILLIA Re-instated exit.
70.12 29-MAR-93 SZWILLIA Changed default for expense check
to match domain.
70.13 26-APR-93 TMATHERS Changed call to derive_full_name
to account for it's change
to a procedure.
70.14 11-JUN-93 TMathers Changed call to validate_dob
removed the current_emp_or_apl_flag
parameter.
70.15 13-JUN-93 TMathers Changed call to generate_number
added l_person_id parameter.
70.20 23-NOV-94 rfine Suppressed index on business_group_id
in all where clauses.
110.1 2-APR-98 SASmith Change to procedure call to hr_assignment.load_budget_values
to include the assignment's effective start and end dates.
This is required as the budget values table is now date tracked.
110.2 5-FEB-99 LSIGRIST Checked, upadated MLS and date formats
for release 11.5 compliancy.
115.3 22-APR-99 ALogue Canonical number support for
normal_hours in insert_assignment.
115.4 24-AUG-99 JPBard Remove work_telephone from insert list
115.5 26-JUN-00 CCarter Changed per_jobs to per_jobs_v for
Job Groups.
115.11 15-SEP-00 GPERRY Leapfrog of 115.9 with fix for
WWBUG 1390173.
115.12 19-OCT-00 GPERRY Fixed WWBUG 1408379.
Added hook calls to OAB so life event triggers
work.
115.13 29-MAY-01 A.Sahay PTU Changes
115.13 19-JUN-01 GPERRY Performance fixes for WWBUG 1833930.
Changed references from hr_locations to
hr_locations_all. This is done because
of the join using location_code which
is consistent with hr_locations_all and
not hz_locations.
115.14 06-JUL-01 PBODLA Bug 1877018 - Passed final_process_date
while calling ben_pps_trg.ler_chk
to detect potential life events.
115.18 20-JUN-02 vbanner changes in call to generate_number
and validate_unique_number
to allow compilation
Bug 2426235.
115.19 09-Dec-02 joward MLS enabled grade name
115.20 15-Jan-03 pkakar updated function insert assignment to
include vacancy_id
115.21 29-Jun-04 vanantha Performance fixes(Bug 3648477)
115.22 20-jan-05 irgonzal 3889584 Added call to new routine to
derive person names.
================================================================= */
--
--
FUNCTION insert_period_of_service
(p_person_id NUMBER
,p_business_group_id NUMBER
,p_date_start DATE
,p_accepted_termination_date DATE
,p_actual_termination_date DATE
,p_final_process_date DATE
,p_last_standard_process_date DATE
,p_leaving_reason VARCHAR2
,p_notified_termination_date DATE
,p_projected_termination_date DATE
,p_term_accepted_person_id NUMBER)
return NUMBER IS
--
l_period_of_service_id NUMBER;
hr_utility.set_location('per_db_per_additional.insert_period_of_service',1);
SELECT per_periods_of_service_s.nextval
INTO l_period_of_service_id
FROM sys.dual;
hr_utility.set_location('per_db_per_additional.insert_period_of_service',2);
SELECT 'Y'
INTO l_emp_check
FROM sys.dual
WHERE p_date_start = (SELECT min(effective_start_date)
FROM per_people_f pp
WHERE pp.person_id = p_person_id
AND pp.current_employee_flag = 'Y');
hr_utility.set_location('per_db_per_additional.insert_period_of_service',5);
INSERT INTO per_periods_of_service
(period_of_service_id
,business_group_id
,person_id
,date_start
,accepted_termination_date
,actual_termination_date
,final_process_date
,last_standard_process_date
,leaving_reason
,notified_termination_date
,projected_termination_date
,termination_accepted_person_id
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date)
values
(l_period_of_service_id
,p_business_group_id
,p_person_id
,p_date_start
,p_accepted_termination_date
,p_actual_termination_date
,p_final_process_date
,p_last_standard_process_date
,p_leaving_reason
,p_notified_termination_date
,p_projected_termination_date
,p_term_accepted_person_id
,SYSDATE
,0
,0
,0
,SYSDATE);
,p_event => 'INSERTING'
,p_effective_date => p_date_start);
end insert_period_of_service;
FUNCTION insert_application
(p_person_id NUMBER
,p_business_group_id NUMBER
,p_date_received DATE
,p_date_end DATE
,p_current_employer VARCHAR2
,p_projected_hire_date DATE
,p_termination_reason VARCHAR2)
return NUMBER IS
--
l_application_id NUMBER;
hr_utility.set_location('per_db_per_additional.insert_application',1);
SELECT per_applications_s.nextval
INTO l_application_id
FROM sys.dual;
hr_utility.set_location('per_db_per_additional.insert_application',2);
SELECT 'Y'
INTO l_apl_check
FROM sys.dual
WHERE p_date_received = (SELECT min(effective_start_date)
FROM per_people_f pp
WHERE pp.person_id = p_person_id
AND pp.current_applicant_flag = 'Y');
hr_utility.set_location('per_db_per_additional.insert_application',5);
INSERT INTO per_applications
(application_id
,business_group_id
,person_id
,date_received
,date_end
,current_employer
,projected_hire_date
,termination_reason
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date)
values
(l_application_id
,p_business_group_id
,p_person_id
,p_date_received
,p_date_end
,p_current_employer
,p_projected_hire_date
,l_termination_reason
,SYSDATE
,0
,0
,0
,SYSDATE);
end insert_application;
FUNCTION insert_person
(p_effective_start_date DATE
,p_effective_end_date DATE
,p_business_group_id NUMBER
,p_person_type_id NUMBER
,p_last_name VARCHAR2
,p_applicant_number VARCHAR2
,p_current_applicant_flag VARCHAR2
,p_current_employee_flag VARCHAR2
,p_current_emp_or_apl_flag VARCHAR2
,p_employee_data_verified DATE
,p_date_of_birth DATE
,p_employee_number VARCHAR2
,p_expense_chk_send_to_address VARCHAR2
,p_first_name VARCHAR2
,p_known_as VARCHAR2
,p_marital_status VARCHAR2
,p_middle_names VARCHAR2
,p_nationality VARCHAR2
,p_national_identifier VARCHAR2
,p_previous_last_name VARCHAR2
,p_registered_disabled_flag VARCHAR2
,p_sex VARCHAR2
,p_title VARCHAR2
,p_work_telephone VARCHAR2)
return NUMBER
IS
--
l_person_id NUMBER;
hr_utility.set_location('per_db_per_additional.insert_person',1);
SELECT per_people_s.nextval
INTO l_person_id
FROM sys.dual;
hr_utility.set_location('per_db_per_additional.insert_person',2);
hr_utility.set_location('per_db_per_additional.insert_person',3);
hr_utility.set_location('per_db_per_additional.insert_person',4);
hr_utility.set_location('per_db_per_additional.insert_person',5);
hr_utility.set_location('per_db_per_additional.insert_person',6);
hr_utility.set_location('per_db_per_additional.insert_person',7);
INSERT INTO per_people_f
(person_id
,effective_start_date
,effective_end_date
,business_group_id
,person_type_id
,last_name
,start_date
,applicant_number
,current_applicant_flag
,current_employee_flag
,current_emp_or_apl_flag
,date_employee_data_verified
,date_of_birth
,employee_number
,expense_check_send_to_address
,first_name
,full_name
,known_as
,marital_status
,middle_names
,nationality
,national_identifier
,previous_last_name
,registered_disabled_flag
,sex
,title
,order_name
,global_name
,local_name
-- ,work_telephone
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date)
values
(l_person_id
,p_effective_start_date
,p_effective_end_date
,p_business_group_id
,p_person_type_id
,p_last_name
,p_effective_start_date
,l_applicant_number
,p_current_applicant_flag
,p_current_employee_flag
,p_current_emp_or_apl_flag
,p_employee_data_verified
,p_date_of_birth
,l_employee_number
,p_expense_chk_send_to_address
,p_first_name
,l_full_name
,p_known_as
,p_marital_status
,p_middle_names
,p_nationality
,p_national_identifier
,p_previous_last_name
,p_registered_disabled_flag
,p_sex
,p_title
,l_order_name
,l_global_name
,l_local_name
-- ,p_work_telephone
,SYSDATE
,0
,0
,0
,SYSDATE);
end insert_person;
FUNCTION insert_assignment
(p_effective_start_date DATE
,p_effective_end_date DATE
,p_business_group_id NUMBER
,p_person_id NUMBER
,p_assignment_type VARCHAR2
,p_organization_id NUMBER
,p_grade_id NUMBER
,p_job_id NUMBER
,p_position_id NUMBER
,p_payroll_id NUMBER
,p_location_id NUMBER
,p_vacancy_id NUMBER
,p_people_group_id NUMBER
,p_soft_coding_keyflex_id NUMBER
,p_assignment_status_type_id NUMBER
,p_primary_flag VARCHAR2
,p_manager_flag VARCHAR2
,p_change_reason VARCHAR2
,p_date_probation_end DATE
,p_frequency VARCHAR2
,p_internal_address_line VARCHAR2
,p_normal_hours VARCHAR2
,p_probation_period VARCHAR2
,p_probation_unit VARCHAR2
,p_recruiter_id NUMBER
,p_special_ceiling_step_id NUMBER
,p_supervisor_id NUMBER
,p_recruitment_activity_id NUMBER
,p_person_referred_by_id NUMBER
,p_source_organization_id NUMBER
,p_time_normal_finish VARCHAR2
,p_time_normal_start VARCHAR2)
return NUMBER IS
--
l_assignment_id NUMBER;
hr_utility.set_location('per_db_per_additional.insert_assignment',1);
SELECT per_assignments_s.nextval
, pp.employee_number
INTO l_assignment_id
, l_employee_number
FROM per_people_f pp
WHERE pp.person_id = p_person_id
AND p_effective_start_date BETWEEN pp.effective_start_date
AND pp.effective_end_date;
hr_utility.set_location('per_db_per_additional.insert_assignment',20);
SELECT period_of_service_id
INTO l_period_of_service_id
FROM per_periods_of_service
WHERE person_id = p_person_id
AND p_effective_start_date
BETWEEN date_start AND
nvl(actual_termination_date,to_date('4712/12/31','YYYY/MM/DD'))
AND p_effective_end_date <=
nvl(actual_termination_date,to_date('4712/12/31','YYYY/MM/DD'));
SELECT application_id
INTO l_application_id
FROM per_applications
WHERE person_id = p_person_id
AND p_effective_start_date BETWEEN date_received AND
nvl(date_end,to_date('4712/12/31','YYYY/MM/DD'))
AND p_effective_end_date <=
nvl(date_end,to_date('4712/12/31','YYYY/MM/DD'));
hr_utility.set_location('per_db_per_additional.insert_assignment',2);
hr_utility.set_location('per_db_per_additional.insert_assignment',3);
hr_utility.set_location('per_db_per_additional.insert_assignment',4);
hr_utility.set_location('per_db_per_additional.insert_assignment',5);
INSERT INTO per_all_assignments_f
(assignment_id
,effective_start_date
,effective_end_date
,business_group_id
,grade_id
,position_id
,job_id
,assignment_status_type_id
,payroll_id
,location_id
,person_id
,organization_id
,people_group_id
,soft_coding_keyflex_id
,vacancy_id
,assignment_sequence
,assignment_type
,manager_flag
,primary_flag
,application_id
,assignment_number
,change_reason
,date_probation_end
,frequency
,internal_address_line
,normal_hours
,period_of_service_id
,probation_period
,probation_unit
,recruiter_id
,special_ceiling_step_id
,supervisor_id
,recruitment_activity_id
,person_referred_by_id
,source_organization_id
,time_normal_finish
,time_normal_start
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date)
values (l_assignment_id
, p_effective_start_date
, p_effective_end_date
, p_business_group_id
, p_grade_id
, p_position_id
, p_job_id
, p_assignment_status_type_id
, p_payroll_id
, p_location_id
, p_person_id
, p_organization_id
, p_people_group_id
, p_soft_coding_keyflex_id
, p_vacancy_id
, l_assignment_sequence
, p_assignment_type
, nvl(p_manager_flag,'N')
, p_primary_flag
, l_application_id
, l_assignment_number
, p_change_reason
, p_date_probation_end
, p_frequency
, p_internal_address_line
, fnd_number.canonical_to_number(p_normal_hours)
, l_period_of_service_id
, p_probation_period
, p_probation_unit
, p_recruiter_id
, p_special_ceiling_step_id
, p_supervisor_id
, p_recruitment_activity_id
, p_person_referred_by_id
, p_source_organization_id
, p_time_normal_finish
, p_time_normal_start
, SYSDATE
, 0
, 0
, 0
, SYSDATE);
hr_utility.set_location('per_db_per_additional.insert_assignment',8);
hr_utility.set_location('per_db_per_additional.insert_assignment',10);
hr_utility.set_location('per_db_per_additional.insert_assignment',15);
SELECT letter_type_id
INTO l_letter_type_id
FROM per_letter_gen_statuses
WHERE business_group_id + 0 = p_business_group_id
AND assignment_status_type_id = p_assignment_status_type_id
AND enabled_flag = 'Y';
hr_utility.set_location('per_db_per_additional.insert_assignment',20);
hr_utility.set_location('per_db_per_additional.insert_assignment',25);
SELECT letter_request_id
INTO l_letter_request_id
FROM per_letter_requests
WHERE letter_type_id = l_letter_type_id
AND business_group_id + 0 = p_business_group_id
AND vacancy_id = p_vacancy_id
AND request_status = 'PENDING' ;
hr_utility.set_location('per_db_per_additional.insert_assignment',30);
hr_utility.set_location('per_db_per_additional.insert_assignment',35);
SELECT per_letter_requests_s.nextval
INTO l_letter_request_id
FROM sys.dual;
hr_utility.set_location('per_db_per_additional.insert_assignment',40);
INSERT INTO per_letter_requests
(letter_request_id
,business_group_id
,letter_type_id
,date_from
,request_status
,auto_or_manual
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,vacancy_id)
VALUES
(l_letter_request_id
,p_business_group_id
,l_letter_type_id
,p_effective_start_date
,'PENDING'
,'AUTO'
,SYSDATE
,0
,0
,0
,SYSDATE
,p_vacancy_id);
hr_utility.set_location('per_db_per_additional.insert_assignment',45);
INSERT INTO per_letter_request_lines
(letter_request_line_id
,business_group_id
,letter_request_id
,person_id
,assignment_id
,assignment_status_type_id
,date_from
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date)
VALUES
(per_letter_request_lines_s.nextval
,p_business_group_id
,l_letter_request_id
,p_person_id
,l_assignment_id
,p_assignment_status_type_id
,p_effective_start_date
,SYSDATE
,0
,0
,0
,SYSDATE);
end insert_assignment;
SELECT business_group_id
, people_group_structure
INTO l_business_group_id
, l_people_group_structure
FROM per_business_groups
WHERE name = p_business_group;
SELECT person_type_id
INTO l_person_type_id
FROM per_person_types
WHERE business_group_id = l_business_group_id --Bug fix 3648477
AND system_person_type = 'APL'
AND default_flag = 'Y';
SELECT assignment_status_type_id
INTO l_assignment_status_type_id
FROM per_ass_status_type_amends
WHERE business_group_id + 0 = l_business_group_id
AND default_flag = 'Y'
AND per_system_status = 'ACTIVE_APL'
UNION
SELECT ast.assignment_status_type_id
FROM per_assignment_status_types ast
WHERE nvl(ast.business_group_id,l_business_group_id)
= l_business_group_id
AND ast.default_flag = 'Y'
AND ast.per_system_status = 'ACTIVE_APL'
AND NOT EXISTS (SELECT null
FROM per_ass_status_type_amends ast1
WHERE ast1.business_group_id + 0 = l_business_group_id
AND ast1.default_flag = 'Y'
AND ast1.assignment_status_type_id =
ast.assignment_status_type_id) ;
l_person_id := insert_person(l_effective_start_date
,l_effective_end_date
,l_business_group_id
,l_person_type_id
,p_last_name
,p_applicant_number
,l_current_applicant_flag
,l_current_employee_flag
,l_current_emp_or_apl_flag
,null
,l_date_of_birth
,null
,null
,p_first_name
,p_known_as
,p_marital_status
,p_middle_names
,p_nationality
,null
,p_previous_last_name
,p_registered_disabled_flag
,p_sex
,p_title
,p_work_telephone );
l_application_id := insert_application(l_person_id
,l_business_group_id
,l_effective_start_date
,null
,p_current_employer
,p_projected_hire_date
,null);
SELECT location_id
INTO l_location_id
FROM hr_locations_all
WHERE location_code = p_location
AND l_effective_start_date <= nvl(inactive_date,to_date('4712/12/31',
'YYYY/MM/DD'));
SELECT organization_id
, location_id
INTO l_organization_id
, l_location_id
FROM hr_organization_units
WHERE name = p_organization
AND business_group_id + 0 = l_business_group_id
AND l_effective_start_date BETWEEN date_from
AND nvl(date_to,to_date('4712/12/31',
'YYYY/MM/DD'));
SELECT job_id
INTO l_job_id
FROM per_jobs_v
WHERE name = p_job
AND business_group_id + 0 = l_business_group_id
AND l_effective_start_date BETWEEN date_from
AND nvl(date_to,to_date('4712/12/31',
'YYYY/MM/DD'));
SELECT pos.position_id
, pos.job_id
, pos.organization_id
, nvl(pos.location_id,nvl(org.location_id,l_location_id))
INTO l_position_id
, l_job_id
, l_organization_id
, l_location_id
FROM per_organization_units org
, per_positions pos
WHERE pos.name = p_position
AND pos.business_group_id + 0 = l_business_group_id
AND pos.organization_id = org.organization_id
AND l_effective_start_date BETWEEN pos.date_effective
AND nvl(pos.date_end,to_date('4712/12/31',
'YYYY/MM/DD'));
SELECT grade_id
INTO l_grade_id
FROM per_grades_vl
WHERE name = p_grade
AND business_group_id + 0 = l_business_group_id
AND l_effective_start_date BETWEEN date_from
AND nvl(date_to,to_date('4712/12/31',
'YYYY/MM/DD'));
SELECT vacancy_id
, position_id
, job_id
, organization_id
, grade_id
, people_group_id
, location_id
INTO l_vacancy_id
, l_position_id
, l_job_id
, l_organization_id
, l_grade_id
, l_people_group_id
, l_location_id
FROM per_vacancies
WHERE name = p_vacancy
AND l_effective_start_date BETWEEN date_from
AND nvl(date_to,to_date('4712/12/31','YYYY/MM/DD'));
SELECT recruitment_activity_id
INTO l_recruitment_activity_id
FROM per_recruitment_activity_for
WHERE business_group_id + 0 = l_business_group_id
AND vacancy_id = l_vacancy_id;
SELECT people_group_id
INTO l_people_group_id
FROM pay_people_groups
WHERE people_group_id = p_people_group_id
AND id_flex_num = l_people_group_structure;
SELECT organization_id
INTO l_source_organization_id
FROM per_organization_units
WHERE organization_id = p_source_organization_id
AND business_group_id + 0 = l_business_group_id
AND l_effective_start_date BETWEEN date_from
AND nvl(date_to,to_date('4712/12/31','YYYY/MM/DD'));
SELECT person_id
INTO l_recruiter_id
FROM per_people_f
WHERE (business_group_id = l_business_group_id or
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N') = 'Y')
AND person_id = p_recruiter_id
AND current_employee_flag = 'Y'
AND l_effective_start_date BETWEEN effective_start_date
AND effective_end_date;
SELECT person_id
INTO l_person_referred_by_id
FROM per_people_f
WHERE (business_group_id = l_business_group_id or
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N') = 'Y')
AND person_id = p_person_referred_by_id
AND current_employee_flag = 'Y'
AND l_effective_start_date BETWEEN effective_start_date
AND effective_end_date;
l_assignment_id := insert_assignment(l_effective_start_date
, l_effective_end_date
, l_business_group_id
, l_person_id
, 'A'
, l_organization_id
, l_grade_id
, l_job_id
, l_position_id
, null
, l_location_id
, l_vacancy_id
, l_people_group_id
, null
, l_assignment_status_type_id
, 'Y'
, null
, p_change_reason
, null
, p_frequency
, p_internal_address_line
, p_normal_hours
, null
, null
, l_recruiter_id
, null
, null
, l_recruitment_activity_id
, l_person_referred_by_id
, l_source_organization_id
, p_time_normal_finish
, p_time_normal_start);
SELECT business_group_id
, people_group_structure
, cost_allocation_structure
INTO l_business_group_id
, l_people_group_structure
, l_cost_allocation_structure
FROM per_business_groups
WHERE name = p_business_group;
SELECT person_type_id
INTO l_person_type_id
FROM per_person_types
WHERE business_group_id = l_business_group_id --Bug fix 3648477
AND system_person_type = 'EMP'
AND default_flag = 'Y';
SELECT assignment_status_type_id
INTO l_assignment_status_type_id
FROM per_ass_status_type_amends
WHERE business_group_id + 0 = l_business_group_id
AND default_flag = 'Y'
AND per_system_status = 'ACTIVE_ASSIGN'
UNION
SELECT ast.assignment_status_type_id
FROM per_assignment_status_types ast
WHERE nvl(ast.business_group_id,l_business_group_id)
= l_business_group_id
AND ast.default_flag = 'Y'
AND ast.per_system_status = 'ACTIVE_ASSIGN'
AND NOT EXISTS (SELECT null
FROM per_ass_status_type_amends ast1
WHERE ast1.business_group_id + 0 = l_business_group_id
AND ast1.default_flag = 'Y'
AND ast1.assignment_status_type_id =
ast.assignment_status_type_id) ;
l_person_id := insert_person(l_effective_start_date
,l_effective_end_date
,l_business_group_id
,l_person_type_id
,p_last_name
,null
,l_current_applicant_flag
,l_current_employee_flag
,l_current_emp_or_apl_flag
,p_employee_data_verified
,l_date_of_birth
,p_employee_number
,p_expense_chk_send_to_address
,p_first_name
,p_known_as
,p_marital_status
,p_middle_names
,p_nationality
,p_national_identifier
,p_previous_last_name
,p_registered_disabled_flag
,p_sex
,p_title
,p_work_telephone );
l_period_of_service_id := insert_period_of_service(l_person_id
,l_business_group_id
,l_effective_start_date
,null
,null
,null
,null
,null
,null
,null
,null);
SELECT location_id
INTO l_location_id
FROM hr_locations_all
WHERE location_code = p_location
AND l_effective_start_date <= nvl(inactive_date,to_date('4712/12/31',
'YYYY/MM/DD'));
SELECT organization_id
, nvl(location_id, l_location_id)
INTO l_organization_id
, l_location_id
FROM hr_organization_units
WHERE name = p_organization
AND business_group_id + 0 = l_business_group_id
AND l_effective_start_date BETWEEN date_from
AND nvl(date_to,to_date('4712/12/31',
'YYYY/MM/DD'));
SELECT job_id
INTO l_job_id
FROM per_jobs_v
WHERE name = p_job
AND business_group_id + 0 = l_business_group_id
AND l_effective_start_date BETWEEN date_from
AND nvl(date_to,to_date('4712/12/31',
'YYYY/MM/DD'));
SELECT pos.position_id
, pos.job_id
, pos.organization_id
, nvl(pos.location_id,nvl(org.location_id,l_location_id))
INTO l_position_id
, l_job_id
, l_organization_id
, l_location_id
FROM per_organization_units org
, per_positions pos
WHERE pos.name = p_position
AND pos.business_group_id + 0 = l_business_group_id
AND pos.organization_id = org.organization_id
AND l_effective_start_date BETWEEN pos.date_effective
AND nvl(pos.date_end,to_date('4712/12/31',
'YYYY/MM/DD'));
SELECT grade_id
INTO l_grade_id
FROM per_grades_vl
WHERE name = p_grade
AND business_group_id + 0 = l_business_group_id
AND l_effective_start_date BETWEEN date_from
AND nvl(date_to,to_date('4712/12/31',
'YYYY/MM/DD'));
SELECT pa.payroll_id
INTO l_payroll_id
FROM pay_payrolls_f pa
WHERE pa.payroll_name = p_payroll
AND pa.business_group_id + 0 = l_business_group_id
AND l_effective_start_date BETWEEN pa.effective_start_date
AND pa.effective_end_date
AND l_effective_end_date <= (SELECT max(pa1.effective_end_date)
FROM pay_payrolls_f pa1
WHERE pa1.business_group_id + 0 =
l_business_group_id
AND pa1.payroll_id = pa.payroll_id) ;
SELECT person_id
INTO l_supervisor_id
FROM per_people_f
WHERE (business_group_id = l_business_group_id OR
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
AND person_id = p_supervisor_id
AND current_employee_flag = 'Y'
AND l_effective_start_date BETWEEN effective_start_date
AND effective_end_date;
SELECT cost_allocation_keyflex_id
INTO l_cost_allocation_keyflex_id
FROM pay_cost_allocation_keyflex
WHERE cost_allocation_keyflex_id = p_cost_allocation_keyflex_id
AND id_flex_num = l_cost_allocation_structure;
SELECT people_group_id
INTO l_people_group_id
FROM pay_people_groups
WHERE people_group_id = p_people_group_id
AND id_flex_num = l_people_group_structure;
l_assignment_id := insert_assignment(l_effective_start_date
, l_effective_end_date
, l_business_group_id
, l_person_id
, 'E'
, l_organization_id
, l_grade_id
, l_job_id
, l_position_id
, l_payroll_id
, l_location_id
, l_cost_allocation_keyflex_id
, l_people_group_id
, null
, l_assignment_status_type_id
, 'Y'
, p_manager_flag
, p_change_reason
, p_date_probation_end
, p_frequency
, p_internal_address_line
, p_normal_hours
, p_probation_period
, p_probation_unit
, null
, l_special_ceiling_step_id
, l_supervisor_id
, null
, null
, null
, p_time_normal_finish
, p_time_normal_start);
SELECT business_group_id
INTO l_business_group_id
FROM per_business_groups
WHERE name = p_business_group;
SELECT person_type_id
INTO l_person_type_id
FROM per_person_types
WHERE business_group_id = l_business_group_id --Bug fix 3648477
AND system_person_type = 'OTHER'
AND default_flag = 'Y';
l_person_id := insert_person(l_effective_start_date
,l_effective_end_date
,l_business_group_id
,l_person_type_id
,p_last_name
,null
,l_current_applicant_flag
,l_current_employee_flag
,l_current_emp_or_apl_flag
,null
,p_date_of_birth
,null
,p_expense_chk_send_to_address
,p_first_name
,p_known_as
,p_marital_status
,p_middle_names
,p_nationality
,p_national_identifier
,p_previous_last_name
,p_registered_disabled_flag
,p_sex
,p_title
,p_work_telephone );
SELECT business_group_id
, cost_allocation_structure
, people_group_structure
INTO l_business_group_id
, l_cost_allocation_structure
, l_people_group_structure
FROM per_business_groups
WHERE name = p_business_group;
SELECT 'Y'
INTO l_type_check
FROM per_people_f pp
WHERE pp.person_id = p_person_id
AND l_effective_start_date BETWEEN pp.effective_start_date
AND pp.effective_end_date
AND ((p_assignment_type = 'E'
AND pp.current_employee_flag = 'Y')
OR (p_assignment_type = 'A'
AND pp.current_applicant_flag = 'Y'));
SELECT assignment_status_type_id
INTO l_assignment_status_type_id
FROM per_ass_status_type_amends
WHERE business_group_id + 0 = l_business_group_id
AND default_flag = 'Y'
AND per_system_status = 'ACTIVE_ASSIGN'
UNION
SELECT ast.assignment_status_type_id
FROM per_assignment_status_types ast
WHERE nvl(ast.business_group_id,l_business_group_id)
= l_business_group_id
AND ast.default_flag = 'Y'
AND ast.per_system_status = 'ACTIVE_ASSIGN'
AND NOT EXISTS (SELECT null
FROM per_ass_status_type_amends ast1
WHERE ast1.business_group_id + 0 = l_business_group_id
AND ast1.default_flag = 'Y'
AND ast1.assignment_status_type_id =
ast.assignment_status_type_id) ;
SELECT assignment_status_type_id
INTO l_assignment_status_type_id
FROM per_ass_status_type_amends
WHERE business_group_id + 0 = l_business_group_id
AND default_flag = 'Y'
AND per_system_status = 'ACTIVE_APL'
UNION
SELECT ast.assignment_status_type_id
FROM per_assignment_status_types ast
WHERE nvl(ast.business_group_id,l_business_group_id)
= l_business_group_id
AND ast.default_flag = 'Y'
AND ast.per_system_status = 'ACTIVE_APL'
AND NOT EXISTS (SELECT null
FROM per_ass_status_type_amends ast1
WHERE ast1.business_group_id + 0 = l_business_group_id
AND ast1.default_flag = 'Y'
AND ast1.assignment_status_type_id =
ast.assignment_status_type_id) ;
SELECT location_id
INTO l_location_id
FROM hr_locations_all
WHERE location_code = p_location
AND l_effective_start_date <= nvl(inactive_date,to_date('4712/12/31',
'YYYY/MM/DD'));
SELECT organization_id
, nvl(location_id, l_location_id)
INTO l_organization_id
, l_location_id
FROM hr_organization_units
WHERE name = p_organization
AND business_group_id + 0 = l_business_group_id
AND l_effective_start_date BETWEEN date_from
AND nvl(date_to,to_date('4712/12/31',
'YYYY/MM/DD'));
SELECT job_id
INTO l_job_id
FROM per_jobs_v
WHERE name = p_job
AND business_group_id + 0 = l_business_group_id
AND l_effective_start_date BETWEEN date_from
AND nvl(date_to,to_date('4712/12/31',
'YYYY/MM/DD'));
SELECT pos.position_id
, pos.job_id
, pos.organization_id
, nvl(pos.location_id,nvl(org.location_id,l_location_id))
INTO l_position_id
, l_job_id
, l_organization_id
, l_location_id
FROM per_organization_units org
, per_positions pos
WHERE pos.name = p_position
AND pos.business_group_id + 0 = l_business_group_id
AND pos.organization_id = org.organization_id
AND l_effective_start_date BETWEEN pos.date_effective
AND nvl(pos.date_end,to_date('4712/12/31',
'YYYY/MM/DD'));
SELECT grade_id
INTO l_grade_id
FROM per_grades_vl
WHERE name = p_grade
AND business_group_id + 0 = l_business_group_id
AND l_effective_start_date BETWEEN date_from
AND nvl(date_to,to_date('4712/12/31',
'YYYY/MM/DD'));
SELECT pa.payroll_id
INTO l_payroll_id
FROM pay_payrolls_f pa
WHERE pa.payroll_name = p_payroll
AND pa.business_group_id + 0 = l_business_group_id
AND l_effective_start_date BETWEEN pa.effective_start_date
AND pa.effective_end_date
AND l_effective_end_date <= (SELECT max(pa1.effective_end_date)
FROM pay_payrolls_f pa1
WHERE pa1.business_group_id + 0 =
l_business_group_id
AND pa1.payroll_id = pa.payroll_id) ;
SELECT person_id
INTO l_supervisor_id
FROM per_people_f
WHERE business_group_id + 0 = l_business_group_id
AND person_id = p_supervisor_id
AND current_employee_flag = 'Y'
AND l_effective_start_date BETWEEN effective_start_date
AND effective_end_date;
SELECT cost_allocation_keyflex_id
INTO l_cost_allocation_keyflex_id
FROM pay_cost_allocation_keyflex
WHERE cost_allocation_keyflex_id = p_cost_allocation_keyflex_id
AND id_flex_num = l_cost_allocation_structure;
SELECT people_group_id
INTO l_people_group_id
FROM pay_people_groups
WHERE people_group_id = p_people_group_id
AND id_flex_num = l_people_group_structure;
SELECT location_id
INTO l_location_id
FROM hr_locations_all
WHERE location_code = p_location
AND l_effective_start_date <= nvl(inactive_date,to_date('4712/12/31',
'YYYY/MM/DD'));
SELECT organization_id
, location_id
INTO l_organization_id
, l_location_id
FROM hr_organization_units
WHERE name = p_organization
AND business_group_id + 0 = l_business_group_id
AND l_effective_start_date BETWEEN date_from
AND nvl(date_to,to_date('4712/12/31',
'YYYY/MM/DD'));
SELECT job_id
INTO l_job_id
FROM per_jobs_v
WHERE name = p_job
AND business_group_id + 0 = l_business_group_id
AND l_effective_start_date BETWEEN date_from
AND nvl(date_to,to_date('4712/12/31',
'YYYY/MM/DD'));
SELECT pos.position_id
, pos.job_id
, pos.organization_id
, nvl(pos.location_id,nvl(org.location_id,l_location_id))
INTO l_position_id
, l_job_id
, l_organization_id
, l_location_id
FROM per_organization_units org
, per_positions pos
WHERE pos.name = p_position
AND pos.business_group_id + 0 = l_business_group_id
AND pos.organization_id = org.organization_id
AND l_effective_start_date BETWEEN pos.date_effective
AND nvl(pos.date_end,to_date('4712/12/31',
'YYYY/MM/DD'));
SELECT grade_id
INTO l_grade_id
FROM per_grades_vl
WHERE name = p_grade
AND business_group_id + 0 = l_business_group_id
AND l_effective_start_date BETWEEN date_from
AND nvl(date_to,to_date('4712/12/31',
'YYYY/MM/DD'));
SELECT vacancy_id
, position_id
, job_id
, organization_id
, grade_id
, people_group_id
, location_id
INTO l_vacancy_id
, l_position_id
, l_job_id
, l_organization_id
, l_grade_id
, l_people_group_id
, l_location_id
FROM per_vacancies
WHERE name = p_vacancy
AND l_effective_start_date BETWEEN date_from
AND nvl(date_to,to_date('4712/12/31','YYYY/MM/DD'));
SELECT recruitment_activity_id
INTO l_recruitment_activity_id
FROM per_recruitment_activity_for
WHERE business_group_id + 0 = l_business_group_id
AND vacancy_id = l_vacancy_id;
SELECT organization_id
INTO l_organization_id
FROM per_organization_units
WHERE organization_id = p_source_organization_id
AND business_group_id + 0 = l_business_group_id
AND l_effective_start_date BETWEEN date_from
AND nvl(date_to,to_date('4712/12/31','YYYY/MM/DD'));
SELECT person_id
INTO l_recruiter_id
FROM per_people_f
WHERE (business_group_id = l_business_group_id or
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N') = 'Y')
AND person_id = p_recruiter_id
AND current_employee_flag = 'Y'
AND l_effective_start_date BETWEEN effective_start_date
AND effective_end_date;
SELECT person_id
INTO l_person_referred_by_id
FROM per_people_f
WHERE (business_group_id = l_business_group_id or
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N') = 'Y')
AND person_id = p_person_referred_by_id
AND current_employee_flag = 'Y'
AND l_effective_start_date BETWEEN effective_start_date
AND effective_end_date;
SELECT people_group_id
INTO l_people_group_id
FROM pay_people_groups
WHERE people_group_id = p_people_group_id
AND id_flex_num = l_people_group_structure;
l_assignment_id := insert_assignment(l_effective_start_date
,l_effective_end_date
,l_business_group_id
,p_person_id
,p_assignment_type
,l_organization_id
,l_grade_id
,l_job_id
,l_position_id
,l_payroll_id
,l_location_id
,l_vacancy_id
,l_people_group_id
,l_cost_allocation_keyflex_id
,l_assignment_status_type_id
,'N'
,p_manager_flag
,p_change_reason
,p_date_probation_end
,p_frequency
,p_internal_address_line
,p_normal_hours
,p_probation_period
,p_probation_unit
,l_recruiter_id
,p_special_ceiling_step_id
,l_supervisor_id
,l_recruitment_activity_id
,l_person_referred_by_id
,l_source_organization_id
,p_time_normal_finish
,p_time_normal_start );
SELECT business_group_id
INTO l_business_group_id
FROM per_business_groups
WHERE name = p_business_group;
SELECT pp.person_id
INTO l_person_id
FROM per_periods_of_service pos
, per_people_f pp
WHERE pp.business_group_id + 0 = l_business_group_id
AND pp.current_employee_flag = 'Y'
AND pp.employee_number = p_employee_number
AND pp.person_id = pos.person_id
AND l_effective_start_date BETWEEN
pp.effective_start_date AND pp.effective_end_date
AND l_effective_start_date BETWEEN pos.date_start
AND nvl(pos.actual_termination_date,to_date('4712/12/31','YYYY/MM/DD'))
AND l_effective_end_date <=
nvl(pos.actual_termination_date,to_date('4712/12/31','YYYY/MM/DD'));
SELECT DISTINCT person_id
INTO l_contact_person_id
FROM per_people_f
WHERE business_group_id + 0 = l_business_group_id
AND person_id = p_contact_person_id;
SELECT lookup_code
INTO l_contact_type
FROM hr_lookups
WHERE lookup_type = 'CONTACT'
AND meaning = p_relationship;
SELECT per_contact_relationships_s.nextval
INTO l_contact_relationship_id
FROM sys.dual;
INSERT INTO per_contact_relationships
(contact_relationship_id
,business_group_id
,person_id
,contact_person_id
,contact_type
,dependent_flag
,primary_contact_flag
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date)
values
(l_contact_relationship_id
,l_business_group_id
,l_person_id
,l_contact_person_id
,l_contact_type
,p_dependent_flag
,p_primary_flag
,SYSDATE
,0
,0
,0
,SYSDATE);