CREATE OR REPLACE
PACKAGE BODY XXARJ_HR_CNV_EMP_IND_PKG
IS
--DATES HARDCODED REMOVE
g_debug VARCHAR2(1);
gv_retcode NUMBER := 0;
CURSOR CUR_PERSON_ID(p_employee_number VARCHAR,p_type VARCHAR2)
IS
SELECT person_id,
object_version_number
FROM apps.PER_ALL_PEOPLE_F
WHERE DECODE(p_type,'EMP',employee_number,NPW_NUMBER) = p_employee_number
AND NVL(effective_end_date,sysdate) >= sysdate ;
CURSOR CUR_PARTY_ID(p_employee_number VARCHAR2,p_type VARCHAR2)
IS
SELECT party_id,
person_id
FROM apps.PER_ALL_PEOPLE_F
WHERE DECODE(p_type,'EMP',employee_number,NPW_NUMBER) = p_employee_number;
g_business_group_id NUMBER :=FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');
eskip EXCEPTION;
v_error_msg VARCHAR2(4000);
v_error_msg1 VARCHAR2(4000);
v_employee_number VARCHAR2(50);
v_employee_numbers VARCHAR2(50);
g_person_type VARCHAR2(50);
g_v_appl_id NUMBER;
g_v_bus_date DATE;
-------------------------------------------------MAIN------------------------------------------------------
PROCEDURE MAIN(
ERRBUF OUT VARCHAR2,
RETCODE OUT NUMBER,
P_DEBUG IN VARCHAR2,
P_LOAD_EMP IN VARCHAR2,
P_LOAD_ASSI IN VARCHAR2,
P_EMPLOYEE IN VARCHAR2,
P_LOAD_PHN IN VARCHAR2,
P_LOAD_QUAL IN VARCHAR2,
P_LOAD_ADDR IN VARCHAR2,
P_TERM_EMP IN VARCHAR2)
IS
v_con_status BOOLEAN;
v_retcode NUMBER := 0;
v_org_enable VARCHAR2(3);
BEGIN
G_DEBUG := P_DEBUG;
P_LOG ( 'Calling CREATE_EMPLOYEE for Creating Employees...');
SELECT APPLICATION_ID
INTO g_v_appl_id
FROM apps.FND_APPLICATION
WHERE APPLICATION_SHORT_NAME = 'PER';
SELECT DATE_FROM
INTO g_v_bus_date
FROM apps.PER_BUSINESS_GROUPS
WHERE BUSINESS_GROUP_ID = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');
IF P_LOAD_EMP = 'Y' THEN
P_LOG ( 'Calling CREATE_EMPLOYEE for Creating Employees...');
CREATE_EMPLOYEE ( P_EMPLOYEE ) ;
END IF;
IF P_LOAD_ASSI = 'Y' THEN
P_LOG ( 'Calling CREATE_ASSIGNMENT for Creating Assignment...');
CREATE_ASSIGNMENT ( P_EMPLOYEE );
END IF;
--Added by Amit Abhishek 14-Jun-10
IF P_LOAD_PHN = 'Y' THEN
P_LOG ( 'Calling CREATE_PHONE procedure...');
CREATE_PHONE ;
END IF;
IF P_LOAD_QUAL = 'Y' THEN
P_LOG ( 'Calling CREATE_QUALIFICATION procedure...');
CREATE_QUALIFICATION ;
END IF;
--Change Over 14-Jun-10
--Added by Amit Abhishek 17-Jun-10
IF P_LOAD_ADDR = 'Y' THEN
P_LOG ( 'Calling CREATE_ADDRESS procedure...');
CREATE_ADDRESS ;
END IF;
IF P_TERM_EMP = 'Y' THEN
P_LOG ( 'Calling TERMINATE_EMPLOYEE procedure...');
TERMINATE_EMPLOYEE ;
END IF;
--Change Over 17-Jun-10
--P_LOG ( 'Calling Error Report printing...');
--PRINT_ERROR; Commented by Amit Abhishek 07-Jun-10
retcode := gv_retcode;
EXCEPTION
WHEN OTHERS THEN
RETCODE := 2;
p_log ('Unexpected error in MAIN :' || SQLERRM );
END MAIN;
---------------------------------------------------END MAIN-------------------------------------------------------------
---------------------------------------------------CREATE EMPLOYEE------------------------------------------------------
PROCEDURE CREATE_EMPLOYEE(
P_EMPLOYEE IN VARCHAR2 )
IS
/*** This cursor will consider all the employees in the staging table and process them.
It will ignore supervisor for all the employees in creation.
***/
CURSOR CUR_SUP
IS
SELECT xxpll.ROWID ROW_ID ,
xxpll.*
FROM XXPLL_HR_CNV_PEOPLE_TMP xxpll
WHERE NVL(XXPLL.process_flag,'N') IN ('N','E')
ORDER BY hire_date;
--AND EMPLOYEE_NUMBER = NVL ( P_EMPLOYEE , EMPLOYEE_NUMBER );
--
v_person_type_id PER_PERSON_TYPES.PERSON_TYPE_ID%TYPE;
v_person_id apps.PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
v_assignment_id PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE;
v_per_object_version_number NUMBER;
v_asg_object_version_number NUMBER;
v_per_effective_start_date DATE;
v_per_effective_end_date DATE;
v_full_name VARCHAR2(100);
v_per_comment_id NUMBER;
v_assignment_sequence NUMBER;
v_assignment_number PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER%TYPE;
v_name_combination_warning BOOLEAN;
v_assign_payroll_warning BOOLEAN;
v_orig_hire_warning BOOLEAN;
v_address_id PER_ADDRESSES.ADDRESS_ID%TYPE;
v_object_version_number NUMBER;
v_party_id apps.PER_ALL_PEOPLE_F.PARTY_ID%TYPE;
v_row_count NUMBER := 0;
v_err_count NUMBER := 0;
v_Gender VARCHAR2(1);
v_emp_exists VARCHAR2(1) := 'N';
v_commit NUMBER := 0;
v_date DATE ; --:= to_date('01-JAN-1900','dd-MON-yyyy');
v_hiredate DATE;
v_omitted NUMBER := 0;
V_PDP_OBJECT_VERSION_NUMBER NUMBER;
--V_employee_numbers VARCHAR2(50) := '';
v_npw_number VARCHAR2(50);
v_npw_number1 VARCHAR2(50);
v_eff_start_date DATE;
l_dob DATE;
v_emp_count NUMBER;
v_blood_grp VARCHAR2(10); --Added by Amit Abhishek 18-Jun-10
v_nationality VARCHAR2(10); --Added by Amit Abhishek 18-Jun-10
v_panno VARCHAR2(10); --Added by Amit Abhishek 18-Jun-10
v_pan_count NUMBER; --Added by Amit Abhishek 18-Jun-10
-----Added by Subrata on 28.01.2011-----------------------
s_person_id number;
s_employee_number VARCHAR2(4000);
s_asg_eff_start_date date;
s_object_version_number number;
s_person_type_id number;
s_last_name VARCHAR2(4000);
s_first_name VARCHAR2(4000);
s_middle_name VARCHAR2(4000);
s_date_of_birth date;
s_email_address VARCHAR2(4000);
s_marital_status VARCHAR2(4000);
s_nationality VARCHAR2(4000);
s_national_identifier VARCHAR2(4000);
s_disability VARCHAR2(4000);
s_gender VARCHAR2(4000);
s_title VARCHAR2(4000);
s_date_of_death date;
s_background_check_status VARCHAR2(4000);
s_background_date_check date;
s_blood_type VARCHAR2(4000);
s_original_date_of_hire date;
s_town_of_birth VARCHAR2(4000);
s_region_of_birth VARCHAR2(4000);
s_country_of_birth VARCHAR2(4000);
s_effective_start_date date;
s_effective_end_date date;
s_full_name VARCHAR2(4000);
s_comment_id number;
s_name_combination_warning boolean;
s_assign_payroll_warning boolean;
s_orig_hire_warning boolean;
---------------------------------------------------------
BEGIN
p_log ( 'Business Group ID from the Profile PER_BUSINESS_GROUP_ID = ' || g_business_group_id );
FOR emp_sup IN CUR_SUP
LOOP
----------Added on 25.01.2011---------------------------
Begin
SELECT ppf.person_id,
ppf.employee_number,
ppf.effective_start_date,
ppf.object_version_number,
ppf.person_type_id,
ppf.last_name,
ppf.first_name,
ppf.middle_names,
ppf.date_of_birth,
ppf.email_address,
ppf.marital_status,
ppf.nationality,
ppf.national_identifier,
ppf.registered_disabled_flag,
ppf.sex,
ppf.title,
ppf.date_of_death,
ppf.background_check_status,
ppf.background_date_check,
ppf.blood_type,
ppf.original_date_of_hire,
ppf.town_of_birth,
ppf.region_of_birth,
ppf.country_of_birth
INTO s_person_id,
s_employee_number,
s_asg_eff_start_date,
s_object_version_number,
s_person_type_id,
s_last_name,
s_first_name,
s_middle_name,
s_date_of_birth,
s_email_address,
s_marital_status,
s_nationality,
s_national_identifier,
s_disability,
s_gender,
s_title,
s_date_of_death,
s_background_check_status,
s_background_date_check,
s_blood_type,
s_original_date_of_hire,
s_town_of_birth,
s_region_of_birth,
s_country_of_birth
FROM PER_ALL_PEOPLE_F ppf
WHERE ppf.employee_number = emp_sup.employee_number
AND ppf.business_group_id = g_business_group_id
AND ppf.current_employee_flag = 'Y'
AND SYSDATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
IF NVL(emp_sup.Gender,'X') NOT IN ('M','F') THEN
IF upper(nvl(emp_sup.Title,'MR.')) <> 'MS.' THEN
s_Gender := 'M';
ELSE
s_Gender := 'F';
END IF;
ELSE
s_Gender := emp_sup.Gender;
END IF;
/*apps.HR_PERSON_API.update_person
(
p_validate => false
,p_effective_date => s_asg_eff_start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_person_id => s_person_id
,p_object_version_number => s_object_version_number
,p_person_type_id => s_person_type_id
,p_last_name => nvl(emp_sup.last_name,s_last_name)
,p_applicant_number =>null
,p_comments =>null
,p_date_employee_data_verified =>null
,p_date_of_birth => nvl(emp_sup.date_of_birth,s_date_of_birth)
,p_email_address => nvl(emp_sup.email_address,s_email_address)
,p_employee_number => s_employee_number
,p_expense_check_send_to_addres =>null
,p_first_name => nvl(emp_sup.first_name,s_first_name)
,p_known_as =>null
,p_marital_status => nvl(emp_sup.marital_status,s_marital_status)
,p_middle_names => nvl(emp_sup.middle_name,s_middle_name)
,p_nationality => nvl(emp_sup.nationality,s_nationality)
,p_national_identifier => null --nvl(emp_sup.national_identifier,s_national_identifier)
,p_previous_last_name =>null
,p_registered_disabled_flag => nvl(emp_sup.disabilty,s_disability)
,p_sex => s_Gender
,p_title => nvl(emp_sup.title,s_title)
,p_vendor_id =>null
,p_work_telephone =>null
,p_attribute_category =>TO_CHAR(FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'))
,p_attribute1 =>null
,p_attribute2 =>null
,p_attribute3 =>null
,p_attribute4 =>null
,p_attribute5 =>null
,p_attribute6 =>null
,p_attribute7 =>null
,p_attribute8 =>null
,p_attribute9 =>null
,p_attribute10 =>null
,p_attribute11 =>null
,p_attribute12 =>null
,p_attribute13 =>null
,p_attribute14 =>null
,p_attribute15 =>null
,p_attribute16 =>null
,p_attribute17 =>null
,p_attribute18 =>null
,p_attribute19 =>null
,p_attribute20 =>null
,p_attribute21 =>null
,p_attribute22 =>null
,p_attribute23 =>null
,p_attribute24 =>null
,p_attribute25 =>null
,p_attribute26 =>null
,p_attribute27 =>null
,p_attribute28 =>null
,p_attribute29 =>null
,p_attribute30 =>null
,p_per_information_category =>'IN'
,p_per_information1 =>null
,p_per_information2 =>null
,p_per_information3 =>null
,p_per_information4 =>null
,p_per_information5 =>null
,p_per_information6 =>null
,p_per_information7 =>'RO'
,p_per_information8 =>null
,p_per_information9 =>null
,p_per_information10 =>null
,p_per_information11 =>null
,p_per_information12 =>null
,p_per_information13 =>null
,p_per_information14 =>null
,p_per_information15 =>null
,p_per_information16 =>null
,p_per_information17 =>null
,p_per_information18 =>null
,p_per_information19 =>null
,p_per_information20 =>null
,p_per_information21 =>null
,p_per_information22 =>null
,p_per_information23 =>null
,p_per_information24 =>null
,p_per_information25 =>null
,p_per_information26 =>null
,p_per_information27 =>null
,p_per_information28 =>null
,p_per_information29 =>null
,p_per_information30 =>null
,p_date_of_death =>s_date_of_death
,p_background_check_status =>s_background_check_status
,p_background_date_check =>s_background_date_check
,p_blood_type =>s_blood_type
,p_correspondence_language =>null
,p_fast_path_employee =>null
,p_fte_capacity =>null
,p_hold_applicant_date_until =>null
,p_honors =>null
,p_internal_location =>null
,p_last_medical_test_by =>null
,p_last_medical_test_date =>null
,p_mailstop =>null
,p_office_number =>null
,p_on_military_service =>null
,p_pre_name_adjunct =>null
,p_projected_start_date =>null
,p_rehire_authorizor =>null
,p_rehire_recommendation =>null
,p_resume_exists =>null
,p_resume_last_updated =>null
,p_second_passport_exists =>null
,p_student_status =>null
,p_work_schedule =>null
,p_rehire_reason =>null
,p_suffix =>null
,p_benefit_group_id =>null
,p_receipt_of_death_cert_date =>null
,p_coord_ben_med_pln_no =>null
,p_coord_ben_no_cvg_flag =>null
,p_coord_ben_med_ext_er =>null
,p_coord_ben_med_pl_name =>null
,p_coord_ben_med_insr_crr_name =>null
,p_coord_ben_med_insr_crr_ident =>null
,p_coord_ben_med_cvg_strt_dt =>null
,p_coord_ben_med_cvg_end_dt =>null
,p_uses_tobacco_flag =>null
,p_dpdnt_adoption_date =>null
,p_dpdnt_vlntry_svce_flag =>null
,p_original_date_of_hire =>nvl(emp_sup.hire_date,s_original_date_of_hire)
,p_adjusted_svc_date =>null
,p_town_of_birth =>nvl(emp_sup.town_of_birth,s_town_of_birth)
,p_region_of_birth => nvl(emp_sup.region_of_birth,s_region_of_birth)
,p_country_of_birth => nvl(emp_sup.country_of_birth ,s_country_of_birth)
,p_global_person_id =>null
,p_party_id =>null
,p_npw_number =>null
,p_per_effective_start_date => s_effective_start_date
,p_per_effective_end_date => s_effective_end_date
,p_full_name => s_full_name
,p_comment_id => s_comment_id
,p_name_combination_warning => s_name_combination_warning
,p_assign_payroll_warning => s_assign_payroll_warning
,p_orig_hire_warning => s_orig_hire_warning
);
*/
apps.HR_PERSON_API.update_person(
p_validate => false
,p_effective_date => s_asg_eff_start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_object_version_number => s_object_version_number
,p_employee_number => s_employee_number
,p_comments =>null
,p_last_name => nvl(emp_sup.last_name,s_last_name)
,p_person_id => s_person_id
,p_person_type_id => s_person_type_id
,p_expense_check_send_to_addres =>null
,p_first_name => nvl(emp_sup.first_name,s_first_name)
,p_known_as =>null
,p_marital_status => nvl(emp_sup.marital_status,s_marital_status)
,p_middle_names => nvl(emp_sup.middle_name,s_middle_name)
,p_nationality => nvl(emp_sup.nationality,s_nationality)
,p_national_identifier => null --nvl(emp_sup.national_identifier,s_national_identifier)
,p_previous_last_name =>null
,p_registered_disabled_flag => nvl(emp_sup.disabilty,s_disability)
,p_sex => s_Gender
,p_title => nvl(emp_sup.title,s_title)
,p_vendor_id =>null
,p_work_telephone =>null
,p_attribute_category =>TO_CHAR(FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'))
,p_attribute1 =>null
,p_attribute2 =>null
,p_attribute3 =>null
,p_attribute4 =>null
,p_attribute5 =>null
,p_attribute6 =>null
,p_attribute7 =>null
,p_attribute8 =>null
,p_attribute9 =>null
,p_attribute10 =>null
,p_attribute11 =>null
,p_attribute12 =>null
,p_attribute13 =>null
,p_attribute14 =>null
,p_attribute15 =>null
,p_attribute16 =>null
,p_attribute17 =>null
,p_attribute18 =>null
,p_attribute19 =>null
,p_attribute20 =>null
,p_attribute21 =>null
,p_attribute22 =>null
,p_attribute23 =>null
,p_attribute24 =>null
,p_attribute25 =>null
,p_attribute26 =>null
,p_attribute27 =>null
,p_attribute28 =>null
,p_attribute29 =>null
,p_attribute30 =>null
, p_per_information_category => 'IN'
,p_per_information1 =>null
,p_per_information2 =>null
,p_per_information3 =>null
,p_per_information4 =>null
,p_per_information5 =>null
,p_per_information6 =>null
,p_per_information7 =>'RO'
,p_per_information8 =>null
,p_per_information9 =>null
,p_per_information10 =>null
,p_per_information11 =>null
,p_per_information12 =>null
,p_per_information13 =>null
,p_per_information14 =>null
,p_per_information15 =>null
,p_per_information16 =>null
,p_per_information17 =>null
,p_per_information18 =>null
,p_per_information19 =>null
,p_per_information20 =>null
,p_per_information21 =>null
,p_per_information22 =>null
,p_per_information23 =>null
,p_per_information24 =>null
,p_per_information25 =>null
,p_per_information26 =>null
,p_per_information27 =>null
,p_per_information28 =>null
,p_per_information29 =>null
,p_per_information30 =>null
,p_date_of_death =>s_date_of_death
,p_background_check_status =>s_background_check_status
,p_background_date_check =>s_background_date_check
,p_blood_type =>s_blood_type
,p_correspondence_language =>null
,p_fast_path_employee =>null
,p_fte_capacity =>null
,p_hold_applicant_date_until =>null
,p_honors =>null
,p_internal_location =>null
,p_last_medical_test_by =>null
,p_last_medical_test_date =>null
,p_mailstop =>null
,p_office_number =>null
,p_on_military_service =>null
,p_pre_name_adjunct =>null
,p_projected_start_date =>null
,p_rehire_authorizor =>null
,p_rehire_recommendation =>null
,p_resume_exists =>null
,p_resume_last_updated =>null
,p_second_passport_exists =>null
,p_student_status =>null
,p_work_schedule =>null
,p_rehire_reason =>null
,p_suffix =>null
,p_benefit_group_id =>null
,p_receipt_of_death_cert_date =>null
,p_coord_ben_med_pln_no =>null
,p_coord_ben_no_cvg_flag =>null
,p_coord_ben_med_ext_er =>null
,p_coord_ben_med_pl_name =>null
,p_coord_ben_med_insr_crr_name =>null
,p_coord_ben_med_insr_crr_ident =>null
,p_coord_ben_med_cvg_strt_dt =>null
,p_coord_ben_med_cvg_end_dt =>null
,p_uses_tobacco_flag =>null
,p_dpdnt_adoption_date =>null
,p_dpdnt_vlntry_svce_flag =>null
,p_original_date_of_hire =>nvl(emp_sup.hire_date,s_original_date_of_hire)
,p_adjusted_svc_date =>null
,p_town_of_birth =>nvl(emp_sup.town_of_birth,s_town_of_birth)
,p_region_of_birth => nvl(emp_sup.region_of_birth,s_region_of_birth)
,p_country_of_birth => nvl(emp_sup.country_of_birth ,s_country_of_birth)
,p_global_person_id =>null
,p_party_id =>null
,p_npw_number =>null
,p_comment_id => s_comment_id
,p_effective_start_date => s_effective_start_date
,p_effective_end_date => s_effective_end_date
,p_full_name => s_full_name
,p_name_combination_warning => s_name_combination_warning
,p_assign_payroll_warning => s_assign_payroll_warning
,p_orig_hire_warning =>s_orig_hire_warning
);
TRACE1('Employee Updated Successfully............ ' );
exception when no_data_found then
TRACE1('Call Create Employee API ' );
--------------------------------------------------------
v_row_count := v_row_count + 1;
v_error_msg := NULL;
v_error_msg1 := NULL;
G_EMP_NUMBER := NULL;
v_blood_grp := NULL; --Added by Amit Abhishek 18-Jun-10
v_panno := NULL; --Added by Amit Abhishek 18-Jun-10
v_nationality :=NULL; --Added by Amit Abhishek 18-Jun-10
v_pan_count :=0; --Added by Amit Abhishek 18-Jun-10
v_Gender := NULL; --Added by Amit Abhishek 30-Jun-10
--V_employee_numbers := NULL;
g_person_type := NULL;
v_person_type_id :=NULL;
IF MOD ( CUR_SUP%ROWCOUNT , 10 ) = 0 THEN
COMMIT;
END IF;
BEGIN
TRACE1('---------------------------------------------------------------------');
-- TRACE1('Processing person SSO = ' || emp_sup.employee_number );
TRACE1('USER PERSON TYPE = ' || emp_sup.user_person_type );
v_hiredate := NULL;
v_full_name := NULL;
v_error_msg := NULL;
-- v_employee_number := emp_sup.employee_number;
g_person_type := UPPER(emp_sup.system_person_type);
TRACE1('Validating Person type - ' || emp_sup.user_person_type );
--Modified by Amit Abhishek 11-Jun-10
BEGIN
SELECT person_type_id
INTO v_person_type_id
FROM apps.PER_PERSON_TYPES_V
WHERE system_person_type = emp_sup.system_person_type
AND UPPER(user_person_type) = UPPER(emp_sup.user_person_type);
EXCEPTION WHEN OTHERS
THEN
TRACE1('Invalid Person Type');
RAISE_APPLICATION_ERROR ( -20001, 'Invalid Person Type - ' || emp_sup.user_person_type ) ;
END;
--Change Over 11-Jun-10
TRACE1 ( 'Person Type ID = ' || v_person_type_id );
--Validating Gender
--Modified by Amit Abhishek 30-Jun-10
IF NVL(emp_sup.Gender,'X') NOT IN ('M','F') THEN
IF upper(nvl(emp_sup.Title,'MR.')) <> 'MS.' THEN
v_Gender := 'M';
ELSE
v_Gender := 'F';
END IF;
ELSE
v_Gender := emp_sup.Gender;
END IF;
--Change Over 30-Jun-10
v_eff_start_date := emp_sup.EFFECTIVE_START_DATE;
v_hiredate := emp_sup.hire_date;
IF v_eff_start_date < g_v_bus_date THEN
RAISE_APPLICATION_ERROR ( -20001, 'Effective Start date cannot be less than Business Group Start Date.' );
END IF;
TRACE1('HIRE DATE OF THE EMPLOYEE '||v_hiredate);
--Added by Amit ABhishek on 04-Jun-2010
l_dob := NULL;
IF emp_sup.date_of_birth > SYSDATE THEN
l_dob := add_months(emp_sup.date_of_birth,-1200);
ELSE
l_dob := emp_sup.date_of_birth;
END IF;
--Change Over 04-Jun-2010
-- IF g_person_type =UPPER(emp_sup.user_person_type) then
IF g_person_type = 'EMP' THEN
BEGIN
v_emp_count := 0;
--Modified by Amit Abhishek to check for duplicate at enterprise level 16-Nov-10
SELECT count(*)
INTO v_emp_count
FROM apps.PER_ALL_PEOPLE_F ppf,
apps.PER_ENT_SECURITY_GROUPS sg,
apps.PER_ENT_SECURITY_GROUPS sg1
WHERE attribute1 = emp_sup.employee_number
AND sg.business_group_id = g_business_group_id
AND sg1.business_group_id = ppf.business_group_id
AND sg.enterprise_id = sg1.enterprise_id;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
--Added by Amit Abhishek 18-Jun-10
BEGIN
--Validate Nationality
SELECT lookup_code
INTO v_nationality
FROM apps.hr_lookups
WHERE upper(lookup_type) = 'NATIONALITY'
AND upper(meaning) = upper(emp_sup.nationality)
AND enabled_flag = 'Y'
AND NVL(end_date_active,SYSDATE+1)>SYSDATE;
EXCEPTION
WHEN OTHERS THEN
v_error_msg1 := v_error_msg1||' Invalid Nationality.';
END;
BEGIN
--Validate Blood Group
SELECT lookup_code
INTO v_blood_grp
FROM apps.hr_lookups
WHERE upper(lookup_type) = 'BLOOD_TYPE'
AND lookup_code = emp_sup.Blood_type
AND enabled_flag = 'Y'
AND NVL(end_date_active,SYSDATE+1)>SYSDATE;
EXCEPTION
WHEN OTHERS THEN
v_error_msg1 := v_error_msg1||'Invalid Blood Group.';
END;
--Validate PAN
IF length(emp_sup.pan_number) = 10 THEN
v_panno := emp_sup.pan_number;
SELECT count(*)
INTO v_pan_count
FROM apps.PER_ALL_PEOPLE_F
WHERE per_information4 = v_panno
AND business_group_id = g_business_group_id
AND TRUNC(SYSDATE) between effective_start_date and effective_end_date
AND current_employee_flag = 'Y';
IF v_pan_count>0 THEN
v_panno := NULL;
v_error_msg1 := v_error_msg1||' Duplicate PAN number. PAN Number must be unique for the employees.';
END IF;
ELSE
v_error_msg1 := v_error_msg1||' Invalid PAN Number.';
END IF;
--Change Over 18-Jun-10
IF v_emp_count =0 THEN
IF emp_sup.system_person_type ='EMP' AND emp_sup.user_person_type='Permanent' THEN
G_EMP_NUMBER :='P'||xxplg_emp_global_number_seq.nextval;
ELSIF emp_sup.system_person_type='EMP' AND emp_sup.user_person_type='Contract' THEN
G_EMP_NUMBER :='C'||xxplg_con_global_number_seq.nextval;
END IF;
TRACE1('CALLING CREATE EMPLOYEE API FOR THE SSO '|| G_EMP_NUMBER||' ~ Person Type : '||emp_sup.user_person_type);
apps.HR_EMPLOYEE_API.CREATE_EMPLOYEE (
p_validate => false
,p_hire_date => v_eff_start_date -- NVL(emp_sup.EFFECTIVE_START_DATE,SYSDATE) ---NVL(emp_sup.hire_date,'01-JAN-1900')
,p_business_group_id => g_business_group_id
,p_last_name => initcap(emp_sup.last_name)
,p_sex => v_Gender
,p_person_type_id => v_person_type_id
,p_per_comments => NULL --emp_sup.comments
,p_date_employee_data_verified => NULL
,p_date_of_birth => l_dob
,p_email_address => emp_sup.email_address
,p_employee_number => G_EMP_NUMBER
,p_expense_check_send_to_addres => NULL --emp_sup.expense_check_send_to_addres
,p_first_name => initcap(emp_sup.first_name)
,p_known_as => NULL --emp_sup.known_as
,p_marital_status => emp_sup.Marital_Status --emp_sup.marital_status
,p_middle_names => initcap(emp_sup.middle_name)
,p_nationality => v_nationality --Modified by Amit Abhishek 18-Jun-10
,p_national_identifier => NULL
,p_previous_last_name => NULL
,p_registered_disabled_flag => NULL
,p_title => upper(emp_sup.Title)
,p_vendor_id => NULL
,p_work_telephone => NULL--emp_sup.DIRECT_NUMBER --emp_sup.work_telephone
--,p_attribute_category => null
,p_date_of_death => NULL
,p_background_check_status => NULL
,p_background_date_check => NULL
,p_blood_type => v_blood_grp --Modified by Amit Abhishek 18-Jun-10
,p_correspondence_language => NULL
,p_fast_path_employee => NULL
,p_fte_capacity => NULL
,p_honors => NULL
,p_internal_location => NULL --emp_sup.internal_location
,p_last_medical_test_by => NULL
,p_last_medical_test_date => NULL
,p_mailstop => NULL
,p_office_number => NULL --emp_sup.office_number
,p_on_military_service => NULL
,p_pre_name_adjunct => NULL
,p_projected_start_date => NULL --emp_sup.projected_start_date
,p_resume_exists => NULL
,p_resume_last_updated => NULL
,p_second_passport_exists => NULL
,p_student_status => NULL
,p_work_schedule => NULL
,p_suffix => NULL
,p_benefit_group_id => NULL
,p_receipt_of_death_cert_date => NULL
,p_coord_ben_med_pln_no => NULL
,p_coord_ben_no_cvg_flag => 'N'
,p_coord_ben_med_ext_er => NULL
,p_coord_ben_med_pl_name => NULL
,p_coord_ben_med_insr_crr_name => NULL
,p_coord_ben_med_insr_crr_ident => NULL
,p_coord_ben_med_cvg_strt_dt => NULL
,p_coord_ben_med_cvg_end_dt => NULL
,p_uses_tobacco_flag => NULL
,p_dpdnt_adoption_date => NULL
,p_dpdnt_vlntry_svce_flag => 'N'
,p_original_date_of_hire => v_hiredate --null
,p_adjusted_svc_date => NULL
,p_town_of_birth => emp_sup.town_of_birth
,p_region_of_birth => emp_sup.region_of_birth
,p_country_of_birth => emp_sup.country_of_birth
,p_global_person_id => NULL
,p_party_id => NULL
,p_person_id => v_person_id -- Out parameters
,p_assignment_id => v_assignment_id
,p_per_object_version_number => v_per_object_version_number
,p_asg_object_version_number => v_asg_object_version_number
,p_per_effective_start_date => v_per_effective_start_date
,p_per_effective_end_date => v_per_effective_end_date
,p_full_name => v_full_name
,p_per_comment_id => v_per_comment_id
,p_assignment_sequence => v_assignment_sequence
,p_assignment_number => v_assignment_number
,p_name_combination_warning => v_name_combination_warning
,p_assign_payroll_warning => v_assign_payroll_warning
,p_orig_hire_warning => v_orig_hire_warning
,p_attribute_category => TO_CHAR(FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')) --Modified by Amit Abhishek 04-Jun-10
,p_attribute1 => emp_sup.employee_number
,p_attribute2 => emp_sup.BGC_CONDUCTED
,p_attribute3 => emp_sup.BGC_STATUS
,p_attribute4 => emp_sup.BGC_CONDUCTED_ON
,p_per_information_category => 'IN'
,p_per_information1 => NULL
,p_per_information2 => NULL
,p_per_information3 => NULL
,p_per_information4 => v_panno --Modified by Amit Abhishek 18-Jun-10
,p_per_information5 => emp_sup.pan_applied_for
,p_per_information6 => emp_sup.ex_serviceman
,p_per_information7 => 'RO'--emp_sup.residential_status
,p_per_information8 => emp_sup.pf_number
,p_per_information9 => emp_sup.esi_number
,p_per_information10 => emp_sup.superannuation_number
,p_per_information11 => emp_sup.group_insurance_number
,p_per_information12 => emp_sup.gratuity_number
,p_per_information13 => emp_sup.pension_fund_number
,p_per_information14 => emp_sup.pan_reference_number
,p_per_information15 => emp_sup.NSSN
,p_per_information16 => NULL
,p_per_information17 => NULL
,p_per_information18 => NULL
,p_per_information19 => NULL
,p_per_information20 => NULL
,p_per_information21 => NULL
,p_per_information22 => NULL
,p_per_information23 => NULL
,p_per_information24 => NULL
,p_per_information25 => NULL
,p_per_information26 => NULL
,p_per_information27 => NULL
,p_per_information28 => NULL
,p_per_information29 => NULL
,p_per_information30 => NULL ) ;
TRACE1('>>>>>>>>OUT OF THE CREATE EMPLOYEE API');
p_update_errm ( p_process_flag => 'C' , p_errm => v_error_msg1, p_rowid => emp_sup.row_id );
TRACE1('>>>>>EMPLOYEE CREATION SUCCEEDED.' );
TRACE1('THE EMPLOYEE_Number '||G_EMP_NUMBER);
UPDATE XXPLL_HR_CNV_PEOPLE_TMP
SET New_Employee_number=G_EMP_NUMBER
WHERE employee_number =emp_sup.Employee_number;
ELSE
--Added by Amit Abhishek 11-Jun-10
p_update_errm ( p_process_flag => 'E' , p_errm => 'Employee already exists', p_rowid => emp_sup.row_id );
v_err_count := v_err_count + 1;
--Change over 11-Jun-10
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
v_err_count := v_err_count + 1;
v_error_msg1 := SUBSTR ( SQLERRM ,12 );
TRACE1('>>>>>EMPLOYEE CREATION FAILED:'|| v_error_msg1 );
p_update_errm ( p_process_flag => 'E' , p_errm => v_error_msg1, p_rowid => emp_sup.row_id );
v_error_msg := SUBSTR(SQLERRM,1,1000);
END;
end;----Added on 25.01.2011
END LOOP;
COMMIT;
p_log ( '--------------------------------------------------------------------------');
p_log ( 'Number of records procesed for Employee Creation : ' || v_row_count );
p_log ( 'Number of records errorred for Employee Creation : ' || v_err_count );
p_log ( '--------------------------------------------------------------------------');
EXCEPTION
WHEN OTHERS THEN
p_log ('Unexpected error in CREATE_EMPLOYEE :' || SQLERRM );
RAISE;
END CREATE_EMPLOYEE ;
-----------------------------------------------------------END CREATE EMPLOYEE--------------------------------------------
----------------------------------------------------------CREATE ASSIGNMENT----------------------------------------------
-- This Procedure Written for Creation of an Assignment of an Employee---
-- TRACE1('THE EMPLOYEE_Number '||V_employee_numbers);
PROCEDURE CREATE_ASSIGNMENT(
P_EMPLOYEE IN VARCHAR2 )
IS
v_person_type_id PER_PERSON_TYPES.PERSON_TYPE_ID%TYPE;
v_person_id apps.PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
v_assignment_id PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE;
v_per_object_version_number NUMBER;
v_asg_object_version_number NUMBER;
v_per_effective_start_date DATE;
v_per_effective_end_date DATE;
v_full_name VARCHAR2(4000);
v_per_comment_id NUMBER;
v_assignment_sequence NUMBER;
v_assignment_number PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER%TYPE;
v_name_combination_warning BOOLEAN;
v_assign_payroll_warning BOOLEAN;
v_orig_hire_warning BOOLEAN;
v_address_id PER_ADDRESSES.ADDRESS_ID%TYPE;
v_object_version_number NUMBER;
v_party_id apps.PER_ALL_PEOPLE_F.PARTY_ID%TYPE;
v_row_count NUMBER := 0;
v_err_count NUMBER := 0;
v_sex VARCHAR2(1);
v_emp_exists VARCHAR2(1) := 'N';
v_commit NUMBER := 0;
v_date DATE ; --:= to_date('01-JAN-1900','dd-MON-yyyy');
v_hiredate DATE;
v_omitted NUMBER := 0;
V_PDP_OBJECT_VERSION_NUMBER NUMBER;
v_npw_number VARCHAR2(50);
v_npw_number1 VARCHAR2(50);
CURSOR CUR_EMP
IS
SELECT xxpll.ROWID ROW_ID,
xxpll.*
FROM XXPLL_HR_CNV_ASSIGN_TMP xxpll
WHERE NVL(process_flag,'N') IN ('N','E')
and employee_number in ('SSSS','PPPP','RRRR')
ORDER BY EMPLOYEE_NUMBER,EFFECTIVE_START_DATE;
-- AND EMPLOYEE_NUMBER = NVL ( P_EMPLOYEE , EMPLOYEE_NUMBER );
BEGIN
p_log ( '------------------------------------------------------------------------');
p_log ('Entering CREATE_ASSIGNMENT for Assignment handling...' );
p_log ( '------------------------------------------------------------------------');
----Added on 02.02.2011-------------------------------------------------------------
delete_assignment;
------------------------------------------------------------------------------------
FOR emp_cur IN CUR_EMP
LOOP
BEGIN
v_row_count := v_row_count + 1;
v_error_msg1 := NULL;
v_error_msg := NULL;
G_EMP_NUMBER :=NULL;
-- v_employee_number := NULL;
-- V_employee_numbers :=NULL;
g_person_type := NULL;
IF MOD ( CUR_EMP%ROWCOUNT, 10 ) = 0 THEN
COMMIT;
END IF;
TRACE1('------------------------------------------------------------------------------------');
--Added by Amit Abhishek 01-Jul-10
BEGIN
SELECT employee_number
INTO G_EMP_NUMBER
FROM per_all_people_f
WHERE attribute1 = emp_cur.employee_number
AND business_group_id = g_business_group_id
AND current_employee_flag = 'Y'
AND SYSDATE BETWEEN effective_start_date and effective_end_date;
EXCEPTION WHEN OTHERS
THEN
NULL;
END;
--G_EMP_NUMBER := emp_cur.New_Employee_number;
--Change Over 01-Jul-10
--g_person_type := UPPER(emp_cur.system_person_type);
TRACE1('EMPLOYEE_NUMBER : '|| G_EMP_NUMBER||' ~ Person Type = ' ||g_person_type );
--IF g_person_type = 'EMP' THEN
p_check_emp_exist ( p_emp_number => emp_cur.employee_number , p_omitted => v_omitted );
--END IF;
IF v_omitted > 0 THEN
BEGIN
v_hiredate := null;
--TRACE1('>>>>>>>>INSIDE LOOP EMP_CUR IN CUR_EMP',g_debug);
--v_hiredate := emp_cur.hire_date;
/*IF emp_cur.ADDRESS_LINE1 IS NOT NULL THEN
TRACE1('Calling CREATE_ADDRESS');
CREATE_ADDRESS(G_EMP_NUMBER
,TRUNC(SYSDATE)
,'Y'
,emp_cur.ADDRESS_LINE1
,emp_cur.ADDRESS_LINE2
,emp_cur.ADDRESS_LINE3
,emp_cur.TOWN_OR_CITY
,emp_cur.POSTAL_CODE
,emp_cur.COUNTRY
,emp_cur.address_style
,g_person_type
,emp_cur.row_id);
END IF;*/
TRACE1('Calling UPDATE_ASSIGNMENT....');
UPDATE_ASSIGNMENT(
G_EMP_NUMBER
,emp_cur.supervisor_number
,g_business_group_id
,emp_cur.location_code
,emp_cur.present_position_title
,emp_cur.JOB_Name
,emp_cur.Grade
,emp_cur.ORGANIZATION_NAME_ORG
,emp_cur.group_name
,emp_cur.subgroup_name
,emp_cur.PROBATION_PERIOD
,emp_cur.PROBATION_UNIT
,emp_cur.Assignment_Category
,emp_cur.GREORLEGALENTITY
,emp_cur.PFORGANIZATION
,emp_cur.PROFESSIONALTAXORGANISATION
,emp_cur.ESIORGANIZATION
,emp_cur.FACTORY
,emp_cur.ESTABLISHMENT
,emp_cur.COVERED_BY_GRATUITY_ACT
,emp_cur.HAVING_SUBSTANTIAL_INTEREST
,emp_cur.DIRECTOR
,emp_cur.SPECIFIED_EMPLOYEE
,emp_cur.PFEPSCONTRIBUTION
,g_person_type
,emp_cur.payroll_name
,emp_cur.role_if_any
,emp_cur.FUNCTIONAL_MGR
,emp_cur.effective_start_date --Added by Amit Abhishek 01-Jul-10
,emp_cur.effective_end_date --Added by Amit Abhishek 01-Jul-10
,emp_cur.row_id
);
TRACE1('After Calling UPDATE_ASSIGNMENT....');
IF v_error_msg1 IS NULL THEN
p_update_asg_errm ( p_process_flag => 'S' , p_errm => v_error_msg1, p_empnum => G_EMP_NUMBER, p_rowid => emp_cur.row_id );
TRACE1('>>>>>EMPLOYEE CREATION/UPDATION ASSIGNMENT SUCCEEDED.' );
ELSE
v_err_count := v_err_count + 1;
p_update_asg_errm ( p_process_flag => 'E' , p_errm => v_error_msg1, p_empnum => G_EMP_NUMBER, p_rowid => emp_cur.row_id );
TRACE1('>>>>>EMPLOYEE CREATION/UPDATION ASSIGNMENT HAS EITHER FAILED OR COMPLETED WITH WARNING:'|| v_error_msg1 );
END IF;
EXCEPTION
WHEN OTHERS THEN
v_err_count := v_err_count + 1;
v_error_msg1 := SUBSTR(SQLERRM,12 );
TRACE1('>>>>>EMPLOYEE CREATION/UPDATION ASSIGNMENT FAILED:'|| v_error_msg1 );
p_update_asg_errm ( p_process_flag => 'E' , p_errm => v_error_msg1, p_empnum => G_EMP_NUMBER, p_rowid => emp_cur.row_id );
END;
END IF;
EXCEPTION
WHEN OTHERS THEN
v_err_count := v_err_count + 1;
v_error_msg1 := SUBSTR(SQLERRM,12 );
TRACE1('>>>>>EMPLOYEE CREATE_ASSIGNMENT FAILED:'|| v_error_msg1 );
p_update_asg_errm ( p_process_flag => 'E' , p_errm => v_error_msg1, p_empnum => G_EMP_NUMBER, p_rowid => emp_cur.row_id );
END;
END LOOP;
p_log ( '--------------------------------------------------------------------------');
p_log ( 'Number of records procesed for Assignment Creation or Updation : ' || v_row_count );
p_log ( 'Number of records errorred for Assignment Creation or Updation : ' || v_err_count );
p_log ( '--------------------------------------------------------------------------');
EXCEPTION
WHEN OTHERS THEN
p_log ('Unexpected error in CREATE_ASSIGNMENT :' || SQLERRM );
RAISE;
END CREATE_ASSIGNMENT;
------------------------------------------------------------END CREATE ASSIGNMENT-------------------------------------------------------
------------------------------------------------------------UPDATE ASSIGNMENT------------------------------------------------------------
PROCEDURE UPDATE_ASSIGNMENT(
P_EMPLOYEE_NUMBER IN VARCHAR2 ,
P_SUPERVISOR_SSO IN VARCHAR2 ,
P_ORGANIZATION_ID IN NUMBER ,
P_LOCATION IN VARCHAR2 ,
P_POSITION_NAME IN VARCHAR2 ,
P_JOB IN VARCHAR2 ,
P_Grade IN VARCHAR2 ,
p_organization IN VARCHAR2 ,
P_SEGMENT1 IN VARCHAR2 ,
P_SEGMENT2 IN VARCHAR2 ,
P_Probation_PERIOD IN VARCHAR2 ,
P_Probation_Unit IN VARCHAR2 ,
P_Employement_Category IN VARCHAR2 ,
PS_SEGMENT1 IN VARCHAR2 ,
PS_SEGMENT2 IN VARCHAR2 ,
PS_SEGMENT3 IN VARCHAR2 ,
PS_SEGMENT4 IN VARCHAR2 ,
PS_SEGMENT5 IN VARCHAR2 ,
PS_SEGMENT6 IN VARCHAR2
-- ,PS_SEGMENT7 IN VARCHAR2
,
PS_SEGMENT8 IN VARCHAR2 ,
PS_SEGMENT9 IN VARCHAR2 ,
PS_SEGMENT10 IN VARCHAR2 ,
PS_SEGMENT11 IN VARCHAR2 ,
PS_SEGMENT12 IN VARCHAR2 ,
P_TYPE IN VARCHAR2 ,
P_PAYROLL_NAME IN VARCHAR2,
P_ROLE IN VARCHAR2, --Added by Amit Abhishek 18-Jun-10
P_FUNC_MGR IN VARCHAR2, --Added by Amit Abhishek 18-Jun-10
P_EFFECTIVE_START_DATE DATE, --Added by Amit Abhishek 01-Jul-10
P_EFFECTIVE_END_DATE DATE, --Added by Amit Abhishek 01-Jul-10
P_ROWID IN ROWID
)
IS
-- Get Assignment id
CURSOR CUR_ASSIGNMENT_ID(p_employee_number VARCHAR2)
IS
SELECT assignment_id ,
person_id,
assignment_number ,
object_version_number
FROM apps.PER_ALL_ASSIGNMENTS_F
WHERE person_id =
(SELECT person_id
FROM apps.PER_ALL_PEOPLE_F
WHERE employee_number = p_employee_number
AND NVL(effective_end_date,sysdate) >= sysdate
)
AND business_group_id =P_ORGANIZATION_ID
AND assignment_type = 'E'
AND assignment_status_type_id =1
AND SYSDATE BETWEEN effective_start_date and effective_end_date
ORDER BY object_version_number DESC;
-- Declaration of Variable Start
v_asg_eff_start_date DATE := NULL;
v_asg_eff_end_date DATE := NULL;
v_concatenated_segments VARCHAR2(4000);
v_comment_id NUMBER;
v_effective_start_date DATE := NULL;
v_effective_end_date DATE :=NULL;
v_no_managers_warning BOOLEAN;
v_other_manager_warning BOOLEAN;
v_object_version_number NUMBER;
v_emp_number VARCHAR2(240);
v_assignment_id PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE;
v_supervisor_id NUMBER := NULL;
v_effective_date DATE;
v_people_group_id NUMBER;
v_special_ceiling_step_id NUMBER;
v_group_name VARCHAR2(240);
v_org_now_no_manager_warning BOOLEAN;
v_spp_delete_warning BOOLEAN;
v_entries_changed_warning VARCHAR2(4000);
v_tax_district_changed_warning BOOLEAN;
v_location_id HR_LOCATIONS.LOCATION_ID%TYPE;
v_position_id HR_POSITIONS_F.POSITION_ID%TYPE;
v_job_id PER_JOBS.JOB_ID%TYPE;
v_grade_id PER_GRADES.GRADE_ID%TYPE;
V_ORG_ID hr_organization_units.organization_id%Type;
V_Soft_Coding_id per_all_assignments_f.soft_coding_keyflex_id%type;
v_assignment_number PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER%TYPE;
v_row_count NUMBER := 0;
v_err_count NUMBER := 0;
v_set_of_book_name apps.GL_SETS_OF_BOOKS.NAME%TYPE;
v_set_of_book_id apps.GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID%TYPE;
v_commit NUMBER :=0;
v_org_name VARCHAR2(4000);
v_emp_found VARCHAR2(1) :='T';
v_organization_name VARCHAR2(4000);
V_HOURLY_SALARIED_WARNING BOOLEAN;
--v_org_name varchar2(30);
l_object_ver NUMBER;
l_sup_effective_start_date DATE;
l_payroll_start_date DATE;
l_payroll_id NUMBER;
v_role VARCHAR2(4000) :=NULL;
v_fmgr_id NUMBER :=NULL;
v_fmgr_name VARCHAR2(240):=NULL;
v_role_name VARCHAR2(150):=NULL;
v_hist_flag VARCHAR2(1) := 'Y'; --Added by Amit Abhishek 01-Jul-2010
v_count_asg NUMBER :=0; --Added by Amit Abhishek 01-Jul-2010
v_count_ovn NUMBER :=0; --Added by Amit Abhishek 01-Jul-2010
v_person_id NUMBER :=NULL; --Added by Amit Abhishek 01-Jul-2010
BEGIN
v_emp_found := 'T';
TRACE1('---------------------------------------------------------------------------------------------------------');
v_emp_number := P_EMPLOYEE_NUMBER ;
v_assignment_id := NULL;
v_assignment_number := NULL;
v_object_version_number := NULL;
v_job_id := NULL;
v_supervisor_id := NULL;
--v_asg_eff_start_date := P_EFFECTIVE_START_DATE;
--v_asg_eff_end_date := P_EFFECTIVE_END_DATE;
OPEN CUR_ASSIGNMENT_ID(v_emp_number);
FETCH CUR_ASSIGNMENT_ID
INTO v_assignment_id,
v_person_id,
v_assignment_number,
v_object_version_number;
TRACE1('v_object_version_number for Ravi - '||v_object_version_number);
IF(CUR_ASSIGNMENT_ID%NOTFOUND) THEN
TRACE1('ASSIGNMENT_ID NOT FOUND FOR THE EMPLOYEE: '||P_EMPLOYEE_NUMBER);
v_emp_found := 'F';
ELSE
TRACE1('ASSIGNMENT_NUMBER FOUND FOR THE EMPLOYEE: ' || P_EMPLOYEE_NUMBER );
END IF;
CLOSE CUR_ASSIGNMENT_ID;
IF v_emp_found ='T' THEN
IF p_location IS NOT NULL THEN
v_location_id := NULL;
BEGIN
SELECT location_id
INTO v_location_id
FROM apps.HR_LOCATIONS
WHERE UPPER(location_code) = UPPER(p_location);
EXCEPTION WHEN OTHERS
THEN
v_location_id := NULL;
END;
END IF;
-- To Fetch the ORG
v_org_id:= NULL;
IF p_organization IS NOT NULL THEN
BEGIN
SELECT organization_id
INTO v_org_id
FROM apps.hr_all_organization_units
WHERE UPPER(name) = UPPER(p_organization)
AND business_group_id = P_ORGANIZATION_ID
AND NVL(date_to,SYSDATE+1)>SYSDATE;
EXCEPTION WHEN OTHERS
THEN
v_error_msg1 := v_error_msg1 || ' ~ ' || 'ORG Not found';
v_org_id := NULL;
END;
--Fetch organization Location if location code doesn't exists
IF v_location_id IS NULL and v_org_id IS NOT NULL THEN
BEGIN
SELECT location_id
INTO v_location_id
FROM apps.hr_all_organization_units
WHERE organization_id = v_org_id;
EXCEPTION
WHEN OTHERS THEN
v_location_id :=NULL;
END;
END IF;
--Fetch business group location if org location is null
IF v_location_id IS NULL THEN
BEGIN
SELECT location_id
INTO v_location_id
FROM apps.hr_all_organization_units
WHERE organization_id = P_ORGANIZATION_ID;
EXCEPTION
WHEN OTHERS THEN
v_location_id :=NULL;
v_error_msg1 := v_error_msg1 || ' ~ ' || 'Location Not found';
END;
END IF;
TRACE1('V_LOCATION_ID '||v_location_id);
ELSE
v_error_msg1 := v_error_msg1 || ' ~ ' || 'ORG Not found';
END IF;
/* --Commented by Amit Abhishek 01-Jul-10
BEGIN
v_effective_date:=null;
SELECT effective_start_date
INTO v_effective_date
FROM apps.PER_ALL_PEOPLE_F
WHERE employee_number = v_emp_number
AND NVL(effective_end_date,sysdate) >= sysdate;
EXCEPTION WHEN OTHERS
THEN
v_error_msg1 := v_error_msg1 || ' ~ ' || 'Effective Date Not Found';
END;*/
--Added by Amit Abhishek 01-Jul-10
IF P_EFFECTIVE_START_DATE IS NULL THEN
v_error_msg1 := v_error_msg1 || ' ~ ' || 'Effective Date Not Found';
ELSE
v_effective_start_date := P_EFFECTIVE_START_DATE;
END IF;
IF P_EFFECTIVE_END_DATE IS NULL THEN
v_effective_end_date := '31-DEC-4712';
ELSE
v_effective_end_date := P_EFFECTIVE_END_DATE;
END IF;
--Change over 01-Jul-10
-- To Fetch the SuperVisor
BEGIN
v_supervisor_id :=NULL;
IF p_supervisor_sso IS NOT NULL THEN
SELECT person_id
INTO v_supervisor_id
FROM apps.PER_ALL_PEOPLE_F
WHERE attribute1 = p_supervisor_sso
AND business_group_id = P_ORGANIZATION_ID
AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
ELSE
v_error_msg1 := v_error_msg1 || ' ~ ' || 'Supervisor Not found';
END IF;
EXCEPTION WHEN OTHERS
THEN
v_error_msg1 := v_error_msg1 || ' ~ ' || 'Supervisor Not found';
v_supervisor_id := NULL;
END;
-- To Fetch the JOB
-- Modified by Amit Abhishek 04-Jun-10
BEGIN
v_job_id := NULL;
IF P_JOB IS NOT NULL THEN
SELECT job_id
INTO v_job_id
FROM apps.PER_JOBS
WHERE name = p_job
AND business_group_id=P_ORGANIZATION_ID
AND NVL(date_to,SYSDATE+1)>SYSDATE;
ELSE
v_error_msg1 := v_error_msg1 || ' ~ ' || 'Job Not found';
END IF;
EXCEPTION WHEN OTHERS
THEN
v_job_id := null;
v_error_msg1 := v_error_msg1 || ' ~ ' || 'Job Not found';
END;
/* To Fetch the Grade*/
-- Modified by Amit Abhishek 04-Jun-10
BEGIN
v_Grade_id := NULL;
IF P_Grade IS NOT NULL THEN
SELECT Grade_id
INTO V_Grade_ID
FROM apps.PER_Grades
WHERE name = p_Grade
AND business_group_id=P_ORGANIZATION_ID
AND NVL(date_to,SYSDATE+1)>SYSDATE;
TRACE1('V_Grade_id '||V_Grade_ID);
ELSE
v_error_msg1 := v_error_msg1 || ' ~ ' || 'Grade Not found';
END IF;
EXCEPTION WHEN OTHERS
THEN
v_error_msg1 := v_error_msg1 || ' ~ ' || 'Grade Not found';
v_Grade_id := null;
END;
--Change over 04-Jun-10
--Added by Amit Abhishek 02-JUN-2010
BEGIN
l_sup_effective_start_date :=NULL;
IF v_supervisor_id IS NOT NULL THEN
SELECT effective_start_date
INTO l_sup_effective_start_date
FROM apps.PER_ALL_PEOPLE_F
WHERE person_id = v_supervisor_id
AND business_group_id = P_ORGANIZATION_ID
AND SYSDATE BETWEEN effective_start_date AND effective_end_date ;
END IF;
l_payroll_start_date :=NULL;
l_payroll_id :=NULL;
SELECT payroll_id,
effective_start_date
INTO l_payroll_id,
l_payroll_start_date
FROM apps.pay_all_payrolls_f
WHERE payroll_name = P_PAYROLL_NAME
--from pay_all_payrolls_f where payroll_name = 'Monthly Payroll PLE'
--from pay_all_payrolls_f where payroll_name = 'Monthly Payroll - Corporate'
AND business_group_id = P_ORGANIZATION_ID
AND sysdate BETWEEN effective_start_date AND effective_end_date;
EXCEPTION
WHEN OTHERS THEN
l_payroll_start_date :=NULL;
l_payroll_id :=NULL;
v_error_msg1 := v_error_msg1 || ' ~ ' || 'Payroll Not found';
END;
--Change Over 02-Jun-2010
/* To Get People Group */
TRACE1('p_Segment1 '||p_Segment1);
v_people_group_id :=NULL;
IF (p_Segment1 IS NOT NULL)THEN
BEGIN
SELECT people_group_id
INTO v_people_group_id
FROM apps.pay_people_groups
WHERE segment1 = p_segment1
AND (( segment2 = p_segment2 AND p_segment2 IS NOT NULL)
OR
(segment2 IS NULL AND p_segment2 IS NULL)
);
EXCEPTION WHEN OTHERS
THEN
v_people_group_id :=NULL;
v_error_msg1 := v_error_msg1 || ' ~ ' || 'People Group Not found';
END;
END IF;
TRACE1('v_people_group_id '||v_people_group_id);
TRACE1('~~~ Done with people grp');
/* To Get SOFT Coded Key FlexFiled*/
BEGIN
v_soft_coding_id := NULL;
--Modified by Amit Abhishek 01-Jul-10
SELECT soft_coding_keyflex_id
INTO v_soft_coding_id
FROM apps.HR_SOFT_CODING_KEYFLEX HSCK,
apps.fnd_id_flex_structures_vl FFSV
WHERE HSCK.id_flex_num =HSCK.id_flex_num
AND ffsv.id_flex_structure_code='IN_STATUTORY_INFO'
AND HSCK.Segment1 = DECODE(PS_SEGMENT1, 'Punj Lloyd Group Corporate', '82','PL Engineering Limited','1296', 'XX')
AND NVL(hsck.segment2,'XX') = DECODE(PS_SEGMENT2,'Punj Lloyd Group EPF Trust','826','XX')
AND NVL(hsck.segment3,'XX') = NVL(PS_SEGMENT3,'XX')
AND NVL(hsck.segment4,'XX') = NVL(PS_SEGMENT4,'XX')
AND NVL(hsck.segment5,'XX') = NVL(PS_SEGMENT5,'XX')
AND NVL(hsck.segment6,'XX') = NVL(PS_SEGMENT6,'XX')
AND hsck.segment8 = NVL(PS_SEGMENT8,'N')
AND hsck.segment9 = NVL(PS_SEGMENT9,'N')
AND hsck.segment10 = NVL(PS_SEGMENT10,'N')
AND hsck.segment11 = NVL(PS_SEGMENT11,'Y')
AND NVL(hsck.segment12,'XX') = NVL(PS_SEGMENT12,'XX');
--Change Over 01-Jul-10
EXCEPTION WHEN OTHERS
THEN
v_soft_coding_id := NULL;
v_error_msg1 := v_error_msg1 || ' ~ ' || 'Soft Coding Not found';
END;
--Added by Amit Abhishek 18-Jun-10
BEGIN
--Validating functional manager's employee number
SELECT person_id,
apps.hr_general.decode_person_name(person_id) --Added by Amit Abhishek 24-Jun-10
INTO v_fmgr_id, v_fmgr_name
FROM apps.PER_ALL_PEOPLE_F
WHERE attribute1 = P_FUNC_MGR
AND business_group_id = P_ORGANIZATION_ID
AND current_employee_flag = 'Y'
AND SYSDATE BETWEEN effective_start_date and effective_end_date;
EXCEPTION WHEN OTHERS
THEN
v_error_msg1 := v_error_msg1 ||' ~ ' ||'Functional Mgr Not Found.';
END;
--Validating Role
BEGIN
SELECT lookup_code,
apps.hr_general.decode_lookup('XXPLG_ROLE',lookup_code) --Added by Amit Abhishek 24-Jun-10
INTO v_role, v_role_name
FROM apps.FND_LOOKUP_VALUES
WHERE lookup_type='XXPLG_ROLE'
AND ( tag like ('%+'||to_char(P_ORGANIZATION_ID)||'+%')
or tag is null)
and upper(meaning) = upper(P_ROLE)
AND enabled_flag = 'Y'
AND NVL(end_date_active,SYSDATE+1)>SYSDATE;
EXCEPTION WHEN OTHERS
THEN
v_error_msg1 := v_error_msg1 ||' ~ ' ||'Role Not Found.';
END;
--Change Over 18-Jun-10
TRACE1(' V_Soft_Coding_id '|| V_Soft_Coding_id);
--END IF;
--
TRACE1('~ ~ ~ Calling for Pro Unit');
/* --Commented by Amit Abhishek on 30-Jun-10
--Do not default organization
IF v_org_id IS NULL THEN
v_org_id :=P_ORGANIZATION_ID;
END IF;*/
TRACE1('AFTER IF V_ORG_id - '||V_Org_ID);
--Added by Amit Abhishek 01-Jul-2010
BEGIN
--Check for initial assignment
SELECT count(*)
INTO v_count_asg
FROM per_all_assignments_f
WHERE person_id = v_person_id
AND business_group_id = P_ORGANIZATION_ID;
IF v_count_asg = 1 THEN
SELECT count(*)
INTO v_count_ovn
FROM per_all_assignments_f
WHERE person_id = v_person_id
AND business_group_id = P_ORGANIZATION_ID
AND object_version_number =1;
IF v_count_ovn =1 THEN
v_hist_flag := 'N';
END IF;
END IF;
EXCEPTION WHEN OTHERS
THEN
NULL;
END;
--TRACE1('v_effective_start_date '||v_effective_start_date);
--Change Over 01-Jul-2010
-- IF v_org_id IS NOT NULL AND v_hist_flag = 'N' THEN --Modified by Amit Abhishek 01-Jul-10
BEGIN
apps.HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA (
p_effective_date => v_effective_start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => v_assignment_id
,p_validate => false
,p_called_from_mass_update => false
,p_position_id => v_position_id
,p_job_id => v_job_id
-- ,p_payroll_id => NVL(l_payroll_id,apps.hr_api.g_number)
,p_grade_id => v_grade_id
,p_location_id => v_location_id
,p_organization_id => V_ORG_ID
,p_pay_basis_id => apps.hr_api.g_number
,p_employment_category => P_Employement_Category
,p_concat_segments => apps.hr_api.g_varchar2
,p_people_group_id => v_people_group_id
,p_object_version_number => v_object_version_number
,p_special_ceiling_step_id => v_special_ceiling_step_id
,p_group_name => v_group_name
,p_effective_start_date => v_asg_eff_start_date
,p_effective_end_date => v_asg_eff_end_date
,p_org_now_no_manager_warning => v_org_now_no_manager_warning
,p_other_manager_warning => v_other_manager_warning
,p_spp_delete_warning => v_spp_delete_warning
,p_entries_changed_warning => v_entries_changed_warning
,p_tax_district_changed_warning => v_tax_district_changed_warning
);
EXCEPTION WHEN OTHERS THEN
v_error_msg1 := SUBSTR(SQLERRM,1,200) ;
TRACE1 ( 'Error in UPDATE_EMP_ASG_CRITERIA : ' || v_error_msg1 );
-- p_update_emp ( p_process_flag => 'E' , p_errm => v_error_msg1, p_empno => V_EMP_NUMBER );
RAISE_APPLICATION_ERROR ( -20001, 'Error in UPDATE_EMP_ASG_CRITERIA : ' || v_error_msg1 );
END;
BEGIN
--Modified by Amit Abhishek 02-Jun-2010
IF NVL(l_sup_effective_start_date,v_effective_start_date-1) <= v_effective_start_date THEN
TRACE1 ( 'Calling HR_ASSIGNMENT_API.UPDATE_EMP_ASG for Employees...' );
apps.HR_ASSIGNMENT_API.UPDATE_EMP_ASG (
p_validate => false
,p_effective_date => v_effective_start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => v_assignment_id
,p_object_version_number => v_object_version_number
,p_supervisor_id => v_supervisor_id
,p_assignment_number => v_assignment_number
,p_change_reason => apps.hr_api.g_varchar2
,p_date_probation_end =>apps.hr_api.g_date
,p_probation_period => P_Probation_PERIOD
,p_probation_unit => P_PROBATION_UNIT
,p_default_code_comb_id => apps.hr_api.g_number
,p_frequency => apps.hr_api.g_varchar2
,p_internal_address_line => apps.hr_api.g_varchar2
,p_manager_flag => apps.hr_api.g_varchar2
,p_set_of_books_id => v_set_of_book_id
,p_title => apps.hr_api.g_varchar2
,p_ass_attribute_category => to_char(g_business_group_id) --Added by Amit Abhishek 18-Jun-10
,p_ass_attribute1 => v_role --Added by Amit Abhishek 18-Jun-10
,p_ass_attribute3 => to_char(v_fmgr_id) --Added by Amit Abhishek 18-Jun-10
,p_ass_attribute4 => v_fmgr_name --Added by Amit Abhishek 24-Jun-10
,p_ass_attribute5 => v_role_name --Added by Amit Abhishek 24-Jun-10
--,p_employee_category => 'LOCAL'
,p_concat_segments => apps.hr_api.g_varchar2
,p_concatenated_segments => v_concatenated_segments
,p_soft_coding_keyflex_id => V_Soft_Coding_id
,p_comment_id => v_comment_id
,p_effective_start_date => v_asg_eff_start_date
,p_effective_end_date => v_asg_eff_end_date
,p_no_managers_warning => v_no_managers_warning
,p_other_manager_warning => v_other_manager_warning
);
ELSE
apps.HR_ASSIGNMENT_API.UPDATE_EMP_ASG (
p_validate => false
,p_effective_date => l_sup_effective_start_date
,p_datetrack_update_mode => 'UPDATE'
,p_assignment_id => v_assignment_id
,p_object_version_number => v_object_version_number
,p_supervisor_id => v_supervisor_id
,p_assignment_number => v_assignment_number
,p_change_reason => apps.hr_api.g_varchar2
--,p_comments => assign_cur.comments
,p_date_probation_end => apps.hr_api.g_date
,p_probation_period => P_Probation_PERIOD
,p_probation_unit => P_PROBATION_UNIT
,p_default_code_comb_id => apps.hr_api.g_number
,p_frequency => apps.hr_api.g_varchar2
,p_internal_address_line => apps.hr_api.g_varchar2
,p_manager_flag => apps.hr_api.g_varchar2
,p_set_of_books_id => v_set_of_book_id
,p_title => apps. hr_api.g_varchar2
,p_ass_attribute_category => to_char(g_business_group_id) --Added by Amit Abhishek 18-Jun-10
,p_ass_attribute1 => v_role --Added by Amit Abhishek 18-Jun-10
,p_ass_attribute3 => to_char(v_fmgr_id) --Added by Amit Abhishek 18-Jun-10
,p_ass_attribute4 => v_fmgr_name --Added by Amit Abhishek 24-Jun-10
,p_ass_attribute5 => v_role_name --Added by Amit Abhishek 24-Jun-10
,p_concat_segments => apps.hr_api.g_varchar2
,p_concatenated_segments => v_concatenated_segments
,p_soft_coding_keyflex_id => V_Soft_Coding_id
,p_comment_id => v_comment_id
,p_effective_start_date => v_asg_eff_start_date
,p_effective_end_date => v_asg_eff_end_date
,p_no_managers_warning => v_no_managers_warning
,p_other_manager_warning => v_other_manager_warning
);
END IF;
-- Change Over 02-JUN-2010
EXCEPTION
WHEN OTHERS THEN
v_error_msg1 := SUBSTR(SQLERRM,1,200);
TRACE1 ( 'Error in UPDATE_EMP_ASG : ' || v_error_msg1 );
RAISE_APPLICATION_ERROR ( -20001, 'Error in UPDATE_EMP_ASG : ' || v_error_msg1 ); --Added by Amit Abhishek 02-Jun-2010
END;
--Insert History Record --Modified by Amit Abhishek 01-Jul-10
/* ELSIF v_org_id IS NOT NULL AND v_hist_flag = 'Y' THEN
apps.HR_ASSIGNMENT_API.UPDATE_EMP_ASG (
p_validate => false
,p_effective_date => v_effective_start_date
,p_datetrack_update_mode => 'UPDATE'
,p_assignment_id => v_assignment_id
,p_object_version_number => v_object_version_number
,p_supervisor_id => v_supervisor_id
,p_assignment_number => v_assignment_number
,p_change_reason => apps.hr_api.g_varchar2
--,p_comments => assign_cur.comments
,p_date_probation_end => apps.hr_api.g_date
,p_probation_period => P_Probation_PERIOD
,p_probation_unit => P_PROBATION_UNIT
,p_default_code_comb_id => apps.hr_api.g_number
,p_frequency => apps.hr_api.g_varchar2
,p_internal_address_line => apps.hr_api.g_varchar2
,p_manager_flag => apps.hr_api.g_varchar2
,p_set_of_books_id => v_set_of_book_id
,p_title => apps. hr_api.g_varchar2
,p_ass_attribute_category => to_char(g_business_group_id) --Added by Amit Abhishek 18-Jun-10
,p_ass_attribute1 => v_role --Added by Amit Abhishek 18-Jun-10
,p_ass_attribute3 => to_char(v_fmgr_id) --Added by Amit Abhishek 18-Jun-10
,p_ass_attribute4 => v_fmgr_name --Added by Amit Abhishek 24-Jun-10
,p_ass_attribute5 => v_role_name --Added by Amit Abhishek 24-Jun-10
,p_concat_segments => apps.hr_api.g_varchar2
,p_concatenated_segments => v_concatenated_segments
,p_soft_coding_keyflex_id => V_Soft_Coding_id
,p_comment_id => v_comment_id
,p_effective_start_date => v_asg_eff_start_date
,p_effective_end_date => v_asg_eff_end_date
,p_no_managers_warning => v_no_managers_warning
,p_other_manager_warning => v_other_manager_warning
);
--Change Over 01-Jul-10
END IF;*/
TRACE1 ( 'After processing all in UPDATE_ASSIGNMENT....' );
COMMIT;
v_object_version_number := NULL;
v_effective_start_date := NULL;
BEGIN
SELECT object_version_number, effective_start_date
INTO v_object_version_number, v_effective_start_date
FROM per_all_assignments_f
WHERE assignment_id = v_assignment_id
AND assignment_status_type_id =1
AND assignment_type ='E'
AND primary_flag ='Y'
AND SYSDATE BETWEEN effective_start_date AND effective_end_date;
EXCEPTION
WHEN OTHERS THEN
v_error_msg1 := SUBSTR(SQLERRM,1,200) ;
TRACE1 ( 'Error in UPDATE_EMP_ASG_CRITERIA : ' || v_error_msg1 );
RAISE_APPLICATION_ERROR ( -20001, 'Error in UPDATE_EMP_ASG_CRITERIA : ' || v_error_msg1 );
END;
--IF v_org_id IS NOT NULL AND v_hist_flag = 'N' THEN --Modified by Amit Abhishek 01-Jul-10
TRACE1 ( 'Calling HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA for Employee..' );
-- TRACE1('OBJECT VERSION NUMBER - '||p_object_version_number);
-- v_object_version_number := v_object_version_number +1;
TRACE1('v_object_version_number - ' || v_object_version_number );
TRACE1(' v_job_id ' || v_job_id );
/* ASSIGNEMNT API START*/
BEGIN
IF NVL(l_payroll_start_date,v_effective_start_date-1 )<=v_effective_start_date THEN
apps.HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA (
p_effective_date => v_effective_start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => v_assignment_id
,p_validate => false
,p_called_from_mass_update => false
--,p_position_id => v_position_id
--,p_job_id => v_job_id
,p_payroll_id => NVL(l_payroll_id,apps.hr_api.g_number)
--,p_grade_id => v_grade_id
--,p_location_id => v_location_id
--,p_organization_id => V_ORG_ID
,p_pay_basis_id => apps.hr_api.g_number
,p_employment_category => P_Employement_Category
,p_concat_segments => apps.hr_api.g_varchar2
,p_people_group_id => v_people_group_id
,p_object_version_number => v_object_version_number
,p_special_ceiling_step_id => v_special_ceiling_step_id
,p_group_name => v_group_name
,p_effective_start_date => v_asg_eff_start_date
,p_effective_end_date => v_asg_eff_end_date
,p_org_now_no_manager_warning => v_org_now_no_manager_warning
,p_other_manager_warning => v_other_manager_warning
,p_spp_delete_warning => v_spp_delete_warning
,p_entries_changed_warning => v_entries_changed_warning
,p_tax_district_changed_warning => v_tax_district_changed_warning
);
ELSE
apps. HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA (
p_effective_date => l_payroll_start_date
,p_datetrack_update_mode => 'UPDATE'
,p_assignment_id => v_assignment_id
,p_validate => false
,p_called_from_mass_update => false
--,p_grade_id => v_grade_id
--,p_position_id => v_position_id
--,p_job_id => v_job_id
,p_payroll_id => l_payroll_id
--,p_location_id => v_location_id
--,p_organization_id => V_ORG_ID
,p_pay_basis_id => apps.hr_api.g_number
,p_employment_category => P_Employement_Category
,p_concat_segments => apps.hr_api.g_varchar2
,p_people_group_id => v_people_group_id
,p_object_version_number => v_object_version_number
,p_special_ceiling_step_id => v_special_ceiling_step_id
,p_group_name => v_group_name
,p_effective_start_date => v_asg_eff_start_date
,p_effective_end_date => v_asg_eff_end_date
,p_org_now_no_manager_warning => v_org_now_no_manager_warning
,p_other_manager_warning => v_other_manager_warning
,p_spp_delete_warning => v_spp_delete_warning
,p_entries_changed_warning => v_entries_changed_warning
,p_tax_district_changed_warning => v_tax_district_changed_warning
);
END IF;
EXCEPTION
WHEN OTHERS THEN
v_error_msg1 := SUBSTR(SQLERRM,1,200) ;
TRACE1 ( 'Error in UPDATE_EMP_ASG_CRITERIA : ' || v_error_msg1 );
-- p_update_emp ( p_process_flag => 'E' , p_errm => v_error_msg1, p_empno => V_EMP_NUMBER );
RAISE_APPLICATION_ERROR ( -20001, 'Error in UPDATE_EMP_ASG_CRITERIA : ' || v_error_msg1 );
END;
----Modified by Amit Abhishek 01-Jul-10 Insert history data
/* ELSIF v_org_id IS NOT NULL AND v_hist_flag = 'Y' THEN
apps. HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA (
p_effective_date => v_effective_start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => v_assignment_id
,p_validate => false
,p_called_from_mass_update => false
,p_grade_id => v_grade_id
,p_position_id => v_position_id
,p_job_id => v_job_id
,p_payroll_id => l_payroll_id
,p_location_id => v_location_id
,p_organization_id => V_ORG_ID
,p_pay_basis_id => apps.hr_api.g_number
,p_employment_category => P_Employement_Category
,p_concat_segments => apps.hr_api.g_varchar2
,p_people_group_id => v_people_group_id
,p_object_version_number => v_object_version_number
,p_special_ceiling_step_id => v_special_ceiling_step_id
,p_group_name => v_group_name
,p_effective_start_date => v_asg_eff_start_date
,p_effective_end_date => v_asg_eff_end_date
,p_org_now_no_manager_warning => v_org_now_no_manager_warning
,p_other_manager_warning => v_other_manager_warning
,p_spp_delete_warning => v_spp_delete_warning
,p_entries_changed_warning => v_entries_changed_warning
,p_tax_district_changed_warning => v_tax_district_changed_warning
);
-- Change over 01-Jul-10
END IF;*/
END IF;
COMMIT;
END UPDATE_ASSIGNMENT;
/* ASSIGNMENT API END*/
-----------------------------------------------------------------------END UPDATE ASSIGNMENT-------------------------------------------------------
-----------------------------------------------------------------------CREATE ADDRESS--------------------------------------------------------------
/* ADRESS CREATION STARTED*/
PROCEDURE CREATE_ADDRESS
IS
v_address_id PER_ADDRESSES.ADDRESS_ID%TYPE;
v_object_version_number NUMBER;
v_emp_number VARCHAR2(240);
v_person_id apps.PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
--g_business_group_id NUMBER := FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');
v_party_id apps.PER_ALL_PEOPLE_F.PARTY_ID%TYPE;
v_primary_flag VARCHAR2(1);
v_row_count NUMBER := 0;
v_err_count NUMBER := 0;
v_commit NUMBER := 0;
v_country VARCHAR2(60);
v_date_from DATE;
v_add_style VARCHAR2(30);
v_count NUMBER := 0;
v_count1 NUMBER;
v_count2 NUMBER;
v_add_type VARCHAR2(10);
v_add_information14 VARCHAR2(60);
v_town_or_city VARCHAR2(60);
v_add_information15 VARCHAR2(60);
v_effective_date DATE;
BEGIN
FOR c_rec IN
( SELECT addr.rowid row_id, addr.*
FROM XXPLG_ADDRESS_TMP addr
WHERE NVL(status,'N') in ('N','E')
)
LOOP
v_row_count := v_row_count +1;
v_person_id := NULL;
v_address_id := NULL;
v_date_from := NULL;
v_error_msg1 := NULL;
v_party_id := NULL;
v_add_style := NULL;
v_country := NULL;
v_object_version_number := NULL;
v_add_information14 := NULL;
v_add_information15 := NULL;
v_town_or_city := NULL;
v_count :=0;
v_count1 :=0;
v_count2 :=0;
v_primary_flag := c_rec.primary_flag;
v_add_type :=NULL;
v_effective_date :=NULL;
BEGIN
SELECT person_id, party_id, original_date_of_hire
INTO v_person_id, v_party_id, v_date_from
FROM apps.PER_ALL_PEOPLE_F
WHERE attribute1 = c_rec.employee_number
AND business_group_id = g_business_group_id
AND SYSDATE BETWEEN effective_start_date and effective_end_date;
EXCEPTION
WHEN OTHERS THEN
v_error_msg1 := ' Employee Not Found.';
v_err_count := v_err_count +1;
UPDATE XXPLG_ADDRESS_TMP
SET ERROR_MSG = v_error_msg1,
STATUS = 'E',
CREATION_DATE = TRUNC(SYSDATE)
WHERE rowid = c_rec.row_id;
END;
v_effective_date :=v_date_from;
IF c_rec.effective_date IS NOT NULL
THEN
v_date_from :=c_rec.effective_date;
END IF;
IF v_error_msg1 IS NULL
THEN
BEGIN
--Validating address style
BEGIN
SELECT DESCRIPTIVE_FLEX_CONTEXT_CODE
INTO v_add_style
FROM apps.FND_DESCR_FLEX_CONTEXTS_VL
WHERE APPLICATION_ID = g_v_appl_id
AND DESCRIPTIVE_FLEXFIELD_NAME ='Address Structure'
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = upper(NVL(c_rec.address_style,'GENERIC'));
EXCEPTION WHEN OTHERS
THEN
NULL;
END;
--If addr style not found consider generic address style
IF v_add_style IS NULL
THEN
SELECT DESCRIPTIVE_FLEX_CONTEXT_CODE
INTO v_add_style
FROM apps.FND_DESCR_FLEX_CONTEXTS_VL
WHERE APPLICATION_ID = g_v_appl_id
AND DESCRIPTIVE_FLEXFIELD_NAME ='Address Structure'
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = 'GENERIC';
END IF;
--Check for duplicate record
SELECT count(*)
INTO v_count
FROM apps.PER_ADDRESSES
WHERE person_id = v_person_id
AND upper(address_line1) = upper(c_rec.address_line1)
AND NVL(postal_code,'XX') = NVL(c_rec.POSTAL_CODE,'XX')
AND upper(style) = v_add_style
AND business_group_id = g_business_group_id;
--If it is a new address then enter
IF v_count = 0 THEN
--Check if primary address exists for the employee
IF v_primary_flag IS NULL
THEN
v_primary_flag := 'Y';
SELECT count(*)
INTO v_count1
FROM apps.PER_ADDRESSES
WHERE person_id = v_person_id
AND primary_flag = 'Y'
--AND upper(style) = v_add_style
AND business_group_id = g_business_group_id;
--If primary address exists then set the primary flag and date_from fields
IF v_count1 > 0 THEN
/* BEGIN
SELECT date_from
INTO v_date_from
FROM apps.PER_ADDRESSES
WHERE person_id = v_person_id
AND primary_flag = 'Y'
AND business_group_id = g_business_group_id;
EXCEPTION WHEN OTHERS
THEN
v_date_from := TRUNC(SYSDATE);
END;
IF v_date_from < c_rec.effective_date THEN
v_date_from := c_rec.effective_date;
END IF;*/
v_primary_flag := 'N';
END IF;
END IF;
IF v_add_style <> 'GENERIC' THEN
v_add_information14 := c_rec.ADDRESS_INFORMATION14;
--Validate Country
BEGIN
SELECT TERRITORY_CODE
INTO v_country
FROM apps.FND_TERRITORIES_VL
WHERE UPPER(territory_short_name) = UPPER(c_rec.country);
EXCEPTION WHEN OTHERS
THEN
v_error_msg1 := ' Invalid Country.';
END;
--Validate States
IF v_add_style = 'IN' THEN
BEGIN
SELECT LOOKUP_CODE
INTO v_add_information15
FROM apps.HR_LOOKUPS
WHERE UPPER(meaning) = UPPER(c_rec.ADDRESS_INFORMATION15)
AND lookup_type = 'IN_STATES'
AND enabled_flag = 'Y'
AND NVL(end_date_active,SYSDATE+1)>SYSDATE;
EXCEPTION WHEN OTHERS
THEN
v_add_information15 := NULL;
v_error_msg1 := v_error_msg1||' Invalid State.';
END;
END IF;
ELSE
v_country := c_rec.country;
v_town_or_city := c_rec.ADDRESS_INFORMATION14;
END IF;
--Validate address type
IF c_rec.ADDR_TYPE IS NOT NULL
THEN
BEGIN
SELECT LOOKUP_CODE
INTO v_add_type
FROM apps.hr_lookups
WHERE lookup_type = 'ADDRESS_TYPE'
AND UPPER(meaning) = UPPER(c_rec.ADDR_TYPE)
AND enabled_flag = 'Y'
AND NVL(end_date_active,SYSDATE+1)>SYSDATE;
EXCEPTION WHEN OTHERS
THEN
v_error_msg1 := v_error_msg1||' Invalid Address Type.';
END;
ELSE
--If address type is not available then by default set the address type as permanent address
BEGIN
v_add_type := 'IN_P';
--Check if permanent address is already entered for employee.
SELECT count(*)
INTO v_count2
FROM apps.PER_ADDRESSES
WHERE person_id = v_person_id
--AND primary_flag = 'Y'
AND business_group_id = g_business_group_id;
--If permanent address already entered then set the address type as current address
IF v_count2 > 0 THEN
v_add_type := 'IN_C';
END IF;
EXCEPTION WHEN OTHERS
THEN
NULL;
END;
END IF;
--IF v_error_msg1 IS NULL
--THEN
BEGIN
TRACE1('BEFORE CRE_OR_UPD_PERSON_ADDRESS - '||c_rec.effective_date);
TRACE1('Address Style - '||v_add_style);
--TRACE1('Effective Date '||v_effective_date);
TRACE1('TOWN/CITY - '||c_rec.ADDRESS_INFORMATION14);
-- ADDRESS API START
apps.HR_PERSON_ADDRESS_API.CRE_OR_UPD_PERSON_ADDRESS(
p_update_mode => 'CORRECTION'
,p_validate => false
,p_address_id => v_address_id
,p_object_version_number => v_object_version_number
,p_effective_date => v_date_from
,p_pradd_ovlapval_override => false
--,p_validate_county => false
,p_person_id => v_person_id
,p_business_group_id => g_business_group_id
,p_primary_flag => v_primary_flag
,p_style => v_add_style
,p_date_from => v_date_from
,p_date_to => c_rec.date_to
,p_address_type => v_add_type
-- ,p_comments => r_address_rec.comments
,p_telephone_number_1 => c_rec.TELEPHONE_NUMBER_1 --Added by Amit Abhishek 18-Jun-10
,p_telephone_number_2 => c_rec.TELEPHONE_NUMBER_2 --Added by Amit Abhishek 18-Jun-10
,p_telephone_number_3 => c_rec.TELEPHONE_NUMBER_3 --Added by Amit Abhishek 18-Jun-10
,p_address_line1 => INITCAP(c_rec.ADDRESS_LINE1)
,p_address_line2 => INITCAP(c_rec.ADDRESS_LINE2)
,p_address_line3 => INITCAP(c_rec.ADDRESS_LINE3)
,p_add_information14 => INITCAP(v_add_information14)
,p_add_information15 => v_add_information15 --Added by Amit Abhishek 18-Jun-10
,p_town_or_city => v_town_or_city
--,p_region_1 => P_state
,p_postal_code => c_rec.POSTAL_CODE
,p_country => v_country
,p_party_id => v_party_id
);
TRACE1('ADDRESS CREATED'||V_ADDRESS_ID);
UPDATE XXPLG_ADDRESS_TMP
SET ERROR_MSG = v_error_msg1,
STATUS = 'S',
CREATION_DATE = TRUNC(SYSDATE)
WHERE rowid = c_rec.row_id;
EXCEPTION WHEN OTHERS
THEN
v_error_msg1 := SUBSTR(SQLERRM,1,200);
v_err_count := v_err_count +1;
UPDATE XXPLG_ADDRESS_TMP
SET ERROR_MSG = v_error_msg1,
STATUS = 'E',
CREATION_DATE = TRUNC(SYSDATE)
WHERE rowid = c_rec.row_id;
END;
/*ELSE
v_err_count := v_err_count +1;
UPDATE XXPLG_ADDRESS_TMP
SET ERROR_MSG = v_error_msg1,
STATUS = 'E',
CREATION_DATE = TRUNC(SYSDATE)
WHERE rowid = c_rec.row_id;*/
--END IF;
ELSE
v_error_msg1 := ' Address already exist.';
v_err_count := v_err_count +1;
UPDATE XXPLG_ADDRESS_TMP
SET ERROR_MSG = v_error_msg1,
STATUS = 'E',
CREATION_DATE = TRUNC(SYSDATE)
WHERE rowid = c_rec.row_id;
END IF;
END;
END IF;
IF MOD(v_row_count,20)=0 THEN
COMMIT;
END IF;
END LOOP;
apps.FND_FILE.PUT_LINE(apps.FND_FILE.OUTPUT,'************************************************************');
apps.FND_FILE.PUT_LINE(apps.FND_FILE.OUTPUT, 'Total Records to be loaded: '||v_row_count);
apps.FND_FILE.PUT_LINE(apps.FND_FILE.OUTPUT, 'Total Records errored out: '||v_err_count);
apps.FND_FILE.PUT_LINE(apps.FND_FILE.OUTPUT,'************************************************************');
END CREATE_ADDRESS;
---------------------------------------------------------------------END CREATE ADDRESS----------------------------------------------------------
---------------------------------------------------------------------CREATE PHONE----------------------------------------------------------------
-- Phone Creation Start
--Added by Amit Abhishek 14-Jun-10
PROCEDURE CREATE_PHONE
IS
v_phone_id PER_PHONES.PHONE_ID%TYPE;
v_object_version_number NUMBER;
v_party_id apps.PER_ALL_PEOPLE_F.PARTY_ID%TYPE;
v_person_id apps.PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
--v_emp_number VARCHAR2(30);
v_phone_type VARCHAR(2);
v_err_count NUMBER := 0;
v_row_count NUMBER := 0;
v_commit NUMBER := 0;
v_count NUMBER;
v_hire_date DATE;
BEGIN
FOR c_rec IN
(SELECT PH.ROWID ROW_ID, PH.*
FROM XXPLG_PHONE_TMP PH
WHERE NVL(status, 'N') in ('N','E')
)
LOOP
v_row_count :=v_row_count + 1;
v_error_msg1 := NULL;
v_party_id := NULL;
v_person_id := NULL;
v_phone_id := NULL;
v_hire_date:=NULL;
v_object_version_number := NULL;
--v_emp_number := c_rec.employee_number;
BEGIN
SELECT party_id,
person_id,
original_date_of_hire
INTO v_party_id, v_person_id, v_hire_date
FROM apps.PER_ALL_PEOPLE_F
WHERE attribute1 = c_rec.employee_number
AND business_group_id = g_business_group_id
AND SYSDATE BETWEEN effective_start_date and effective_end_date;
EXCEPTION
WHEN OTHERS THEN
TRACE1('EMPLOYEE NOT FOUND');
v_error_msg1 := ' Employee Not Found';
END;
BEGIN
--Added by AA 16-Jun-10
v_phone_type := NULL;
IF c_rec.phone_type IS NULL
THEN
IF LENGTH(c_rec.PHONE_NUMBER)=10 THEN
v_count := 0;
v_phone_type := 'M';
--Check for duplicate phone numbers
SELECT COUNT(*)
INTO v_count
FROM apps.PER_PHONES pp, apps.PER_ALL_PEOPLE_F ppf
WHERE pp.parent_table = 'PER_ALL_PEOPLE_F'
AND pp.phone_type = v_phone_type
--AND pp.phone_number = c_rec.phone_number
AND pp.parent_id = ppf.person_id
AND ppf.business_group_id = g_business_group_id
AND ppf.person_id = v_person_id
AND SYSDATE BETWEEN ppf.effective_start_date and ppf.effective_end_date;
IF v_count>0 THEN
v_phone_type := 'H1';
v_count :=0;
--Check for duplicate phone numbers
SELECT COUNT(*)
INTO v_count
FROM apps.PER_PHONES pp, apps.PER_ALL_PEOPLE_F ppf
WHERE pp.parent_table = 'PER_ALL_PEOPLE_F'
AND pp.phone_type = v_phone_type
--AND pp.phone_number = c_rec.phone_number
AND pp.parent_id = ppf.person_id
AND ppf.business_group_id = g_business_group_id
AND ppf.person_id = v_person_id
AND SYSDATE BETWEEN ppf.effective_start_date and ppf.effective_end_date;
IF v_count>0 THEN
v_phone_type := 'W1';
v_count :=0;
--Check for duplicate phone numbers
SELECT COUNT(*)
INTO v_count
FROM apps.PER_PHONES pp, apps.PER_ALL_PEOPLE_F ppf
WHERE pp.parent_table = 'PER_ALL_PEOPLE_F'
AND pp.phone_type = v_phone_type
--AND pp.phone_number = c_rec.phone_number
AND pp.parent_id = ppf.person_id
AND ppf.business_group_id = g_business_group_id
AND ppf.person_id = v_person_id
AND SYSDATE BETWEEN ppf.effective_start_date and ppf.effective_end_date;
IF v_count>0 THEN
v_phone_type := 'O';
END IF;
END IF;
END IF;
ELSE
v_phone_type := 'H1';
v_count :=0;
--Check for duplicate phone numbers
SELECT COUNT(*)
INTO v_count
FROM apps.PER_PHONES pp, apps.PER_ALL_PEOPLE_F ppf
WHERE pp.parent_table = 'PER_ALL_PEOPLE_F'
AND pp.phone_type = v_phone_type
--AND pp.phone_number = c_rec.phone_number
AND pp.parent_id = ppf.person_id
AND ppf.business_group_id = g_business_group_id
AND ppf.person_id = v_person_id
AND SYSDATE BETWEEN ppf.effective_start_date and ppf.effective_end_date;
IF v_count>0 THEN
v_phone_type := 'W1';
v_count :=0;
--Check for duplicate phone numbers
SELECT COUNT(*)
INTO v_count
FROM apps.PER_PHONES pp, apps.PER_ALL_PEOPLE_F ppf
WHERE pp.parent_table = 'PER_ALL_PEOPLE_F'
AND pp.phone_type = v_phone_type
--AND pp.phone_number = c_rec.phone_number
AND pp.parent_id = ppf.person_id
AND ppf.business_group_id = g_business_group_id
AND ppf.person_id = v_person_id
AND SYSDATE BETWEEN ppf.effective_start_date and ppf.effective_end_date;
IF v_count>0 THEN
v_phone_type := 'O';
END IF;
END IF;
END IF;
ELSE
SELECT lookup_code
INTO v_phone_type
FROM apps.hr_lookups
WHERE lookup_type = 'PHONE_TYPE'
AND UPPER(meaning) = UPPER(c_rec.phone_type)
AND enabled_flag = 'Y'
AND NVL(end_date_active,SYSDATE+1)>SYSDATE;
END IF;
--Change over 16-Jun-10
EXCEPTION WHEN OTHERS
THEN
v_error_msg1 := v_error_msg1||' Phone Type Not Found';
END;
IF v_error_msg1 IS NOT NULL THEN
v_err_count := v_err_count + 1;
UPDATE XXPLG_PHONE_TMP
SET STATUS = 'E',
ERROR_MESSAGE = v_error_msg1,
CREATION_DATE = SYSDATE
WHERE ROWID = c_rec.row_id;
END IF;
BEGIN
v_count := 0;
--Check for duplicate phone numbers
SELECT COUNT(*)
INTO v_count
FROM apps.PER_PHONES pp, apps.PER_ALL_PEOPLE_F ppf
WHERE pp.parent_table = 'PER_ALL_PEOPLE_F'
AND pp.phone_type = v_phone_type
AND pp.phone_number = c_rec.phone_number
AND pp.parent_id = ppf.person_id
AND ppf.business_group_id = g_business_group_id
AND ppf.person_id = v_person_id
AND SYSDATE BETWEEN ppf.effective_start_date and ppf.effective_end_date;
IF v_count = 0 AND v_error_msg1 IS NULL THEN
TRACE1('CREATE OR UPDATE PHONE ');
-- PHONE API START
apps.HR_PHONE_API.CREATE_OR_UPDATE_PHONE (
p_phone_id => v_phone_id
, p_object_version_number => v_object_version_number
, p_date_from => v_hire_date
--, p_date_to => apps.hr_api.g_date
, p_phone_type => v_phone_type
, p_phone_number => c_rec.phone_number
, p_parent_id => v_person_id
, p_parent_table => 'PER_ALL_PEOPLE_F'
, p_validate => false
, p_effective_date => v_hire_date
, p_party_id => v_party_id
--, p_validity => apps.hr_api.g_varchar2
);
TRACE1('PHONE CREATION SUCCEDED' || v_phone_id);
UPDATE XXPLG_PHONE_TMP
SET STATUS = 'S',
ERROR_MESSAGE = null,
CREATION_DATE = SYSDATE
WHERE ROWID = c_rec.row_id;
ELSIF v_error_msg1 IS NULL
THEN
v_error_msg1 := 'Phone number already exists';
v_err_count := v_err_count + 1;
UPDATE XXPLG_PHONE_TMP
SET STATUS = 'E',
ERROR_MESSAGE = v_error_msg1,
CREATION_DATE = SYSDATE
WHERE ROWID = c_rec.row_id;
END IF;
EXCEPTION
WHEN OTHERS THEN
v_err_count := v_err_count + 1;
v_error_msg1 := SUBSTR(SQLERRM,1,200);
TRACE1('PHONE CREATION FAILED'||v_error_msg1);
UPDATE XXPLG_PHONE_TMP
SET STATUS = 'E',
ERROR_MESSAGE = v_error_msg1,
CREATION_DATE = SYSDATE
WHERE ROWID = c_rec.row_id;
END;
IF MOD(v_row_count,20)=0 THEN
COMMIT;
END IF;
END LOOP;
apps.FND_FILE.PUT_LINE(apps.FND_FILE.OUTPUT,'************************************************************');
apps.FND_FILE.PUT_LINE(apps.FND_FILE.OUTPUT, 'Total Records to be loaded: '||v_row_count);
apps.FND_FILE.PUT_LINE(apps.FND_FILE.OUTPUT, 'Total Records errored out: '||v_err_count);
apps.FND_FILE.PUT_LINE(apps.FND_FILE.OUTPUT,'************************************************************');
END CREATE_PHONE;
------------------------------------------------------END CREATE PHONE------------------------------------------------------------
FUNCTION val_address_style(
p_style IN VARCHAR2 )
RETURN VARCHAR2
IS
v_dummy VARCHAR2(1000);
BEGIN
SELECT DESCRIPTIVE_FLEX_CONTEXT_CODE
INTO v_dummy
FROM apps.FND_DESCR_FLEX_CONTEXTS_VL
WHERE APPLICATION_ID = g_v_appl_id
AND DESCRIPTIVE_FLEXFIELD_NAME ='Address Structure'
AND upper(DESCRIPTIVE_FLEX_CONTEXT_CODE) = upper(NVL(p_style,'GENERIC'));
RETURN v_dummy;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR ( -20001 , 'Invalid Address Style - ' || p_style );
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR ( -20001 , 'U:Error in val_address_style:' || SQLERRM );
END val_address_style;
--------------------------------------------------------------------------------------
PROCEDURE TRACE1(
P_MESSAGE VARCHAR2 )
IS
BEGIN
IF G_DEBUG = 'Y' THEN
apps.FND_FILE.PUT_LINE(apps.FND_FILE.LOG, 'Debug : ' || P_MESSAGE );
END IF;
END TRACE1;
PROCEDURE P_LOG(
P_MESSAGE VARCHAR2)
IS
BEGIN
apps.FND_FILE.PUT_LINE(apps.FND_FILE.LOG, P_MESSAGE);
END;
PROCEDURE P_OUT(
P_MESSAGE VARCHAR2)
IS
BEGIN
apps.FND_FILE.PUT_LINE(apps.FND_FILE.OUTPUT, P_MESSAGE);
END ;
-------------------------------Genrate Error Report-------------------------------------
PROCEDURE PRINT_ERROR
AS
CURSOR cur_error_report
IS
SELECT EMPLOYEE_NUMBER,
ERROR_MESSAGE
FROM XXPLL_HR_CNV_PEOPLE_TMP
WHERE process_flag='E'
AND REQUEST_ID = apps.FND_GLOBAL.CONC_REQUEST_ID;
BEGIN
FOR cur_report_rec IN cur_error_report
LOOP
IF cur_error_report%ROWCOUNT = 1 THEN
P_OUT ( '----------------------------------------------------------------------------');
P_OUT (RPAD('Employee_Number',30) || 'Error Message' );
gv_retcode := 1;
END IF;
P_OUT(RPAD(cur_report_rec.employee_number, 30) || cur_report_rec.error_message );
END LOOP;
P_OUT ( '----------------------------------------------------------------------------');
END PRINT_ERROR;
PROCEDURE p_update_errm(
p_process_flag VARCHAR2 ,
p_errm VARCHAR2,
p_rowid ROWID )
IS
BEGIN
UPDATE XXPLL_HR_CNV_PEOPLE_TMP
SET process_flag = p_process_flag,
ERROR_MESSAGE = p_errm,
last_update_date = SYSDATE,
last_UPDATED_BY = apps.FND_GLOBAL.USER_ID,
request_id = apps.FND_GLOBAL.CONC_REQUEST_ID,
last_update_login = apps.FND_GLOBAL.LOGIN_ID
WHERE ROWID = p_rowid;
END p_update_errm;
--Added by Amit Abhishek 01-Jul-2010
PROCEDURE p_update_asg_errm(
p_process_flag VARCHAR2 ,
p_errm VARCHAR2,
p_empnum VARCHAR2,
p_rowid ROWID )
IS
BEGIN
UPDATE XXPLL_HR_CNV_ASSIGN_TMP
SET process_flag = p_process_flag,
ERROR_MESSAGE = p_errm,
last_update_date = SYSDATE,
last_UPDATED_BY = apps.FND_GLOBAL.USER_ID,
request_id = apps.FND_GLOBAL.CONC_REQUEST_ID,
last_update_login = apps.FND_GLOBAL.LOGIN_ID,
new_employee_number = p_empnum
WHERE ROWID = p_rowid;
END p_update_asg_errm;
--Change Over 01-Jul-2010
PROCEDURE p_update_emp(
p_process_flag VARCHAR2 ,
p_errm VARCHAR2,
p_rowid ROWID )
IS
BEGIN
UPDATE XXPLL_HR_CNV_PEOPLE_TMP
SET process_flag = p_process_flag,
ERROR_MESSAGE = p_errm,
last_update_date = SYSDATE,
last_UPDATED_BY = apps.FND_GLOBAL.USER_ID,
request_id = apps.FND_GLOBAL.CONC_REQUEST_ID,
last_update_login = apps.FND_GLOBAL.LOGIN_ID
WHERE ROWID = p_rowid;
END p_update_emp;
PROCEDURE p_check_emp_exist(
p_emp_number IN VARCHAR2 ,
p_omitted IN OUT VARCHAR2 )
IS
BEGIN
SELECT 1
INTO p_omitted
FROM apps.PER_ALL_PEOPLE_F
WHERE attribute1 = p_emp_number
AND ROWNUM < 2;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_omitted := 0;
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR ( -20001, 'U:Error while checking Employee Number in Assignment.' );
END p_check_emp_exist;
PROCEDURE p_check_cwk_exist(
p_npw_number IN VARCHAR2 ,
p_omitted IN OUT VARCHAR2 )
IS
BEGIN
SELECT 1
INTO p_omitted
FROM apps.PER_ALL_PEOPLE_F
WHERE NPW_NUMBER = p_npw_number
AND ROWNUM < 2;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_omitted := 0;
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR ( -20001, 'U:Error while checking Employee Number in Assignment.' );
END p_check_cwk_exist;
--Added by Amit Abhishek 14-Jun-10
PROCEDURE CREATE_QUALIFICATION
IS
l_error_msg VARCHAR2(200);
l_business_group_id NUMBER := FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');
l_person_id NUMBER;
l_qualification_type_id NUMBER;
l_date DATE;
l_object_version_number NUMBER(30);
l_qualification_id NUMBER;
l_total_records NUMBER := 0;
l_success_records NUMBER := 0;
l_failure_records NUMBER := 0;
l_specialization VARCHAR2(150); --ADDED BY AMIT ABHISHEK 24-JUN-10
l_count NUMBER;
v_ovn NUMBER;
v_att_id NUMBER;
l_establishment VARCHAR2(240);
l_estab_id NUMBER;
l_status VARCHAR2(10);
BEGIN
FOR c_rec IN
(
SELECT Q.ROWID row_id,Q.*
FROM XXPLG_QUALIFICATIONS_TMP Q
WHERE NVL(status,'N') IN ('N','E')
)
LOOP
l_error_msg := NULL;
l_total_records := l_total_records + 1;
l_person_id := NULL;
l_qualification_type_id := NULL;
l_qualification_id := NULL;
l_object_version_number :=NULL;
l_specialization :=NULL; --ADDED BY AMIT ABHISHEK 24-JUN-10
l_count := 0;
v_ovn := NULL;
v_att_id :=NULL;
l_status :=NULL;
l_establishment :=NULL;
l_estab_id := NULL;
--Get Person ID
BEGIN
SELECT person_id
INTO l_person_id
FROM apps.PER_ALL_PEOPLE_F
WHERE business_group_id = l_business_group_id
AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date
AND attribute1 = c_rec.employee_number;
EXCEPTION
WHEN OTHERS THEN
l_error_msg := 'Employee does not exist.';
END;
--Get Qualification Type ID
BEGIN
SELECT qualification_type_id
INTO l_qualification_type_id
FROM apps.PER_QUALIFICATION_TYPES
WHERE upper(name) = upper(c_rec.qual_type);
EXCEPTION WHEN OTHERS
THEN
l_error_msg := l_error_msg || ' Invalid Qualification Type. ';
END;
--Change Over 24-Jun-10
IF l_error_msg IS NOT NULL THEN
l_failure_records := l_failure_records +1;
UPDATE XXPLG_QUALIFICATIONS_TMP
SET STATUS = 'E',
CREATION_DATE = SYSDATE,
ERROR_MESSAGE = l_error_msg
WHERE ROWID = c_rec.row_id;
ELSE
--Validate Specialization Added by Amit Abhishek 24-Jun-10
BEGIN
SELECT flex_value
INTO l_specialization
FROM apps.FND_FLEX_VALUE_SETS ffvs,
apps.fnd_flex_values ffv
WHERE ffvs.flex_value_set_id = ffv.flex_value_set_id
AND ffvs.FLEX_VALUE_SET_NAME = 'XX_SPECIALIZATION'
AND upper(flex_value)=upper(c_rec.qualification_title)
AND ffv.enabled_flag = 'Y'
AND NVL(ffv.end_date_active,SYSDATE+1)>SYSDATE;
EXCEPTION WHEN OTHERS
THEN
l_error_msg := l_error_msg || ' Invalid Specialization. ';
END;
/*BEGIN
SELECT lookup_code
INTO l_status
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE = 'PER_SUBJECT_STATUSES'
AND UPPER(MEANING)= UPPER(c_rec.)
END;*/
--Validate Establishment
--Added by Amit Abhishek 10-Aug-10
BEGIN
SELECT name, establishment_id
INTO l_establishment, l_estab_id
FROM per_establishments
WHERE upper(NAME) = upper(c_rec.establishment);
EXCEPTION WHEN OTHERS
THEN
NULL;
END;
IF l_establishment IS NULL THEN
BEGIN
SELECT name, establishment_id
INTO l_establishment, l_estab_id
FROM per_establishments
WHERE upper(NAME) = 'OTHERS';
EXCEPTION WHEN OTHERS
THEN
NULL;
END;
END IF;
--Change Over 10-Aug-10
BEGIN
SELECT start_date
INTO l_date
FROM apps.PER_ALL_PEOPLE_F
WHERE person_id = l_person_id
AND business_group_id = l_business_group_id
AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
--Check for duplicates
SELECT count(*)
INTO l_count
FROM apps.per_qualifications_v
WHERE business_group_id = l_business_group_id
AND UPPER(NVL(attribute1,'XX')) = UPPER(NVL(c_rec.qualification_title,'XX'))
AND UPPER(name) = UPPER(c_rec.qual_type)
AND NVL(establishment,'XX')=NVL(l_establishment,'XX')
AND person_id = l_person_id;
IF l_count = 0 THEN
--Added by Amit Abhishek 30-Nov-10
SELECT COUNT(*)
INTO l_count
FROM per_establishment_attendances
WHERE person_id = l_person_id
AND establishment = l_establishment;
--Change Over 30-Nov-10
IF l_count = 0 THEN
--Added by Amit Abhishek 10-Aug-10
PER_ESTAB_ATTENDANCES_API.CREATE_ATTENDED_ESTAB
( p_validate => false
,p_effective_date => SYSDATE
,p_fulltime =>'Y'
--,p_attended_end_date => '01-Jan-2005'
,p_establishment => l_establishment
--,p_establishment_id => l_estab_id
,p_business_group_id =>l_business_group_id
,p_person_id => l_person_id
,p_attendance_id => v_att_id
,p_object_version_number => v_ovn
);
ELSE
SELECT attendance_id
INTO v_att_id
FROM per_establishment_attendances
WHERE person_id = l_person_id
AND establishment = l_establishment;
END IF;
--Change Over 10-Aug-10
per_qualifications_api.create_qualification (
p_validate => FALSE
,p_effective_date => l_date
,p_business_group_id => l_business_group_id
,p_qualification_type_id => l_qualification_type_id
,p_person_id => l_person_id
,p_grade_attained=>c_rec.grade
,p_attendance_id => v_att_id --Added by Amit Abhishek 10-Aug-10
--,p_title => c_rec.qualification_title
,p_status => 'C'
,p_attribute1 => l_specialization --ADDED BY AMIT ABHISHEK 24-JUN-10
,p_start_date => c_rec.start_date
,p_end_date => c_rec.end_date
,p_qualification_id => l_qualification_id
,p_object_version_number => l_object_version_number );
l_success_records := l_success_records + 1;
UPDATE XXPLG_QUALIFICATIONS_TMP
SET status = 'S',
error_message = l_error_msg,
CREATION_DATE = SYSDATE
WHERE rowid = c_rec.row_id;
ELSE
l_failure_records := l_failure_records +1;
UPDATE XXPLG_QUALIFICATIONS_TMP
SET STATUS = 'E',
CREATION_DATE = SYSDATE,
ERROR_MESSAGE = 'Qualification record already exist'
WHERE ROWID = c_rec.row_id;
END IF;
EXCEPTION
WHEN OTHERS THEN
l_error_msg := SUBSTR(SQLERRM,1,200);
l_failure_records := l_failure_records +1;
UPDATE XXPLG_QUALIFICATIONS_TMP
SET STATUS = 'E',
CREATION_DATE = SYSDATE,
ERROR_MESSAGE = l_error_msg
WHERE ROWID = c_rec.row_id;
END;
END IF;
IF MOD(l_total_records,20)=0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
apps.FND_FILE.put_line(apps.FND_FILE.output,'Time : ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
apps.FND_FILE.put_line(apps.FND_FILE.output,'#############################################################');
apps.FND_FILE.put_line(apps.FND_FILE.output,'Total Records To Be Loaded : ' || l_total_records);
apps.FND_FILE.put_line(apps.FND_FILE.output,'Total Success Records : ' || l_success_records);
apps.FND_FILE.put_line(apps.FND_FILE.output,'Total Failure Records : ' || l_failure_records);
apps.FND_FILE.put_line(apps.FND_FILE.output,'#############################################################');
END CREATE_QUALIFICATION;
-------------------------------------------< Terminate_Employee >------------------------------------------------------
PROCEDURE terminate_employee
IS
l_business_group_id NUMBER := FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');
l_final_process_date DATE;
l_supervisor_warning BOOLEAN;
l_event_warning BOOLEAN;
l_interview_warning BOOLEAN;
l_review_warning BOOLEAN;
l_recruiter_warning BOOLEAN;
l_entries_changed_warning VARCHAR2(100);
l_pay_proposal_warning BOOLEAN;
l_dod_warning BOOLEAN;
l_org_now_no_manager_warning BOOLEAN;
l_asg_future_changes_warning BOOLEAN;
l_alu_change_warning VARCHAR2(100);
l_term_reason_code VARCHAR2(100);
l_period_of_service_id NUMBER(15);
l_object_version_number NUMBER(15);
l_term_person_type_id NUMBER(15);
l_error_msg VARCHAR2(2000);
l_validate_cnt NUMBER;
l_status VARCHAR2(10) := 'True';
l_sqlerrm VARCHAR2(1000);
l_errmsg VARCHAR2(1000);
l_total_records NUMBER := 0;
l_success_records NUMBER := 0;
l_failure_records NUMBER := 0;
l_last_standard_process_date DATE;
l_count NUMBER;
/* Cursor to fetch the Temporary Data Those records which are to be validated*/
CURSOR c1_term_employee
IS
SELECT rowid row_id, xtp.*
FROM xxplg_terminate_employee xtp
WHERE NVL(status,'N') IN ('N','E');
CURSOR c1_term_employee1
IS
SELECT rowid row_id, xtp.*
FROM xxplg_terminate_employee xtp
WHERE status IN ('V','E');
/* Generic Cursor for all LOOK UP CODES */
CURSOR lookup_csr (p_lookup_type VARCHAR2,p_lookup_meaning VARCHAR2)
IS
SELECT lookup_code
FROM apps.fnd_common_lookups
WHERE UPPER(lookup_type) = UPPER(p_lookup_type)
AND UPPER(meaning) = UPPER(p_lookup_meaning)
AND enabled_flag = 'Y';
/* Get the Person Type Id */
CURSOR person_type_csr(p_person_type VARCHAR2)
IS
SELECT person_type_id
FROM apps.per_person_types
WHERE user_person_type = p_person_type
AND business_group_id = l_business_group_id
AND active_flag = 'Y';
/* get the period of service/OVN */
CURSOR period_of_service_csr(emp_no IN VARCHAR2)
IS
SELECT ppos.period_of_service_id,
ppos.object_version_number
FROM apps.per_periods_of_service ppos,
apps.per_all_people_f papf
WHERE papf.person_id = ppos.person_id
AND papf.attribute1 = emp_no
AND papf.business_group_id = l_business_group_id
AND SYSDATE between papf.effective_start_date and papf.effective_end_date;
BEGIN
apps.FND_FILE.PUT_LINE(apps.FND_FILE.output,'#############################################################');
apps.FND_FILE.PUT_LINE(apps.FND_FILE.output,'DATA MIGRATION - TERMINATION OF EMPLOYEES :');
apps.FND_FILE.PUT_LINE(apps.FND_FILE.output,'#############################################################');
apps.FND_FILE.PUT_LINE(apps.FND_FILE.output,'START TIME : ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
FOR c1_rec IN c1_term_employee
LOOP
l_errmsg := '';
l_status := 'True';
l_term_reason_code := NULL;
OPEN lookup_csr('LEAV_REAS',NVL(c1_rec.termination_reason,'Termination of Employement'));
FETCH lookup_csr INTO l_term_reason_code;
IF (lookup_csr%NOTFOUND) THEN
l_errmsg := l_errmsg || 'Leaving Reason Not Found. ';
l_status := 'False';
END IF;
CLOSE lookup_csr;
/* If all the above cond's satisfies then */
IF (l_status = 'True') THEN
UPDATE xxplg_terminate_employee
SET status = 'V',
term_reason_code = l_term_reason_code,
error_message = '',
creation_date = SYSDATE
WHERE rowid = c1_rec.row_id;
COMMIT;
ELSE
UPDATE xxplg_terminate_employee
SET status = 'E',
error_message = l_errmsg,
creation_date = SYSDATE
WHERE rowid = c1_rec.row_id;
COMMIT;
END IF;
END LOOP;
/* All the records with proper data */
FOR termination_rec IN c1_term_employee1
LOOP
l_errmsg := '';
l_status := 'True';
l_total_records := l_total_records + 1;
l_count :=0;
l_object_version_number := NULL;
l_period_of_service_id := NULL;
l_term_person_type_id := NULL;
l_final_process_date := NVL(termination_rec.final_process_date,LAST_DAY(termination_rec.actual_termination_date));
l_org_now_no_manager_warning :=NULL;
l_asg_future_changes_warning :=NULL;
l_entries_changed_warning :=NULL;
l_last_standard_process_date := LAST_DAY(termination_rec.actual_termination_date);
BEGIN
SELECT count(*)
INTO l_count
FROM per_all_people_f
WHERE business_group_id = l_business_group_id
AND attribute1 = termination_rec.employee_number
AND current_employee_flag = 'Y'
AND SYSDATE BETWEEN effective_start_date AND effective_end_date;
IF l_count>0 THEN
FOR period_rec IN period_of_service_csr(termination_rec.employee_number)
LOOP
l_period_of_service_id := period_rec.period_of_service_id;
l_object_version_number := period_rec.object_version_number;
END LOOP;
FOR person_type_rec IN person_type_csr(termination_rec.termination_type)
LOOP
l_term_person_type_id := person_type_rec.person_type_id;
END LOOP;
--apps.FND_FILE.PUT_LINE(apps.FND_FILE.output,'Before Actual Termination');
apps.hr_ex_employee_api.ACTUAL_TERMINATION_EMP (
p_effective_date => termination_rec.actual_termination_date
,p_period_of_service_id => l_period_of_service_id
,p_person_type_id => l_term_person_type_id
,p_object_version_number => l_object_version_number
,p_actual_termination_date => termination_rec.actual_termination_date
,p_leaving_reason => termination_rec.term_reason_code
,p_last_standard_process_date => l_last_standard_process_date
,p_supervisor_warning => l_supervisor_warning
,p_event_warning => l_event_warning
,p_interview_warning => l_interview_warning
,p_review_warning => l_review_warning
,p_recruiter_warning => l_recruiter_warning
,p_asg_future_changes_warning => l_asg_future_changes_warning
,p_entries_changed_warning => l_entries_changed_warning
,p_pay_proposal_warning => l_pay_proposal_warning
,p_dod_warning => l_dod_warning
,p_alu_change_warning => l_alu_change_warning
);
--apps.FND_FILE.PUT_LINE(apps.FND_FILE.output,'Before update term');
apps.hr_ex_employee_api.update_term_details_emp (
p_period_of_service_id => l_period_of_service_id
, p_effective_date => termination_rec.actual_termination_date
, p_object_version_number => l_object_version_number
, p_leaving_reason => termination_rec.term_reason_code
, p_notified_termination_date => termination_rec.actual_termination_date-30
);
--apps.FND_FILE.PUT_LINE(apps.FND_FILE.output,'Before final process');
apps.hr_ex_employee_api.final_process_emp (
p_period_of_service_id => l_period_of_service_id
, p_object_version_number => l_object_version_number
, p_final_process_date => l_final_process_date
, p_org_now_no_manager_warning => l_org_now_no_manager_warning
, p_asg_future_changes_warning => l_asg_future_changes_warning
, p_entries_changed_warning => l_entries_changed_warning );
UPDATE xxplg_terminate_employee
SET status = 'S',
error_message = '',
creation_date = SYSDATE
WHERE rowid = termination_rec.row_id;
l_success_records := l_success_records + 1;
ELSE
l_failure_records := l_failure_records + 1;
l_errmsg := l_errmsg|| ' Employee Not Found.';
UPDATE xxplg_terminate_employee
SET status = 'E',
error_message = l_errmsg,
creation_date = SYSDATE
WHERE rowid = termination_rec.row_id;
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
l_status := 'False';
l_errmsg := SUBSTR(SQLERRM,1,200);
l_failure_records := l_failure_records + 1;
UPDATE xxplg_terminate_employee
SET status = 'E',
error_message = l_errmsg,
creation_date = SYSDATE
WHERE rowid = termination_rec.row_id;
END;
IF MOD(l_total_records,20)=0 THEN
COMMIT;
END IF;
END LOOP;
apps.FND_FILE.put_line(apps.FND_FILE.output,'End Time : ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
apps.FND_FILE.put_line(apps.FND_FILE.output,'#############################################################');
apps.FND_FILE.put_line(apps.FND_FILE.output,'Total Records To Be Loaded : ' || l_total_records);
apps.FND_FILE.put_line(apps.FND_FILE.output,'Total Success Records : ' || l_success_records);
apps.FND_FILE.put_line(apps.FND_FILE.output,'Total Failure Records : ' || l_failure_records);
apps.FND_FILE.put_line(apps.FND_FILE.output,'#############################################################');
END terminate_employee;
END XXARJ_HR_CNV_EMP_IND_PKG;
--select * from user_errors where name='XXPLG_HR_CNV_EMP_IND_PKG' and attribute='ERROR'
PACKAGE BODY XXARJ_HR_CNV_EMP_IND_PKG
IS
--DATES HARDCODED REMOVE
g_debug VARCHAR2(1);
gv_retcode NUMBER := 0;
CURSOR CUR_PERSON_ID(p_employee_number VARCHAR,p_type VARCHAR2)
IS
SELECT person_id,
object_version_number
FROM apps.PER_ALL_PEOPLE_F
WHERE DECODE(p_type,'EMP',employee_number,NPW_NUMBER) = p_employee_number
AND NVL(effective_end_date,sysdate) >= sysdate ;
CURSOR CUR_PARTY_ID(p_employee_number VARCHAR2,p_type VARCHAR2)
IS
SELECT party_id,
person_id
FROM apps.PER_ALL_PEOPLE_F
WHERE DECODE(p_type,'EMP',employee_number,NPW_NUMBER) = p_employee_number;
g_business_group_id NUMBER :=FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');
eskip EXCEPTION;
v_error_msg VARCHAR2(4000);
v_error_msg1 VARCHAR2(4000);
v_employee_number VARCHAR2(50);
v_employee_numbers VARCHAR2(50);
g_person_type VARCHAR2(50);
g_v_appl_id NUMBER;
g_v_bus_date DATE;
-------------------------------------------------MAIN------------------------------------------------------
PROCEDURE MAIN(
ERRBUF OUT VARCHAR2,
RETCODE OUT NUMBER,
P_DEBUG IN VARCHAR2,
P_LOAD_EMP IN VARCHAR2,
P_LOAD_ASSI IN VARCHAR2,
P_EMPLOYEE IN VARCHAR2,
P_LOAD_PHN IN VARCHAR2,
P_LOAD_QUAL IN VARCHAR2,
P_LOAD_ADDR IN VARCHAR2,
P_TERM_EMP IN VARCHAR2)
IS
v_con_status BOOLEAN;
v_retcode NUMBER := 0;
v_org_enable VARCHAR2(3);
BEGIN
G_DEBUG := P_DEBUG;
P_LOG ( 'Calling CREATE_EMPLOYEE for Creating Employees...');
SELECT APPLICATION_ID
INTO g_v_appl_id
FROM apps.FND_APPLICATION
WHERE APPLICATION_SHORT_NAME = 'PER';
SELECT DATE_FROM
INTO g_v_bus_date
FROM apps.PER_BUSINESS_GROUPS
WHERE BUSINESS_GROUP_ID = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');
IF P_LOAD_EMP = 'Y' THEN
P_LOG ( 'Calling CREATE_EMPLOYEE for Creating Employees...');
CREATE_EMPLOYEE ( P_EMPLOYEE ) ;
END IF;
IF P_LOAD_ASSI = 'Y' THEN
P_LOG ( 'Calling CREATE_ASSIGNMENT for Creating Assignment...');
CREATE_ASSIGNMENT ( P_EMPLOYEE );
END IF;
--Added by Amit Abhishek 14-Jun-10
IF P_LOAD_PHN = 'Y' THEN
P_LOG ( 'Calling CREATE_PHONE procedure...');
CREATE_PHONE ;
END IF;
IF P_LOAD_QUAL = 'Y' THEN
P_LOG ( 'Calling CREATE_QUALIFICATION procedure...');
CREATE_QUALIFICATION ;
END IF;
--Change Over 14-Jun-10
--Added by Amit Abhishek 17-Jun-10
IF P_LOAD_ADDR = 'Y' THEN
P_LOG ( 'Calling CREATE_ADDRESS procedure...');
CREATE_ADDRESS ;
END IF;
IF P_TERM_EMP = 'Y' THEN
P_LOG ( 'Calling TERMINATE_EMPLOYEE procedure...');
TERMINATE_EMPLOYEE ;
END IF;
--Change Over 17-Jun-10
--P_LOG ( 'Calling Error Report printing...');
--PRINT_ERROR; Commented by Amit Abhishek 07-Jun-10
retcode := gv_retcode;
EXCEPTION
WHEN OTHERS THEN
RETCODE := 2;
p_log ('Unexpected error in MAIN :' || SQLERRM );
END MAIN;
---------------------------------------------------END MAIN-------------------------------------------------------------
---------------------------------------------------CREATE EMPLOYEE------------------------------------------------------
PROCEDURE CREATE_EMPLOYEE(
P_EMPLOYEE IN VARCHAR2 )
IS
/*** This cursor will consider all the employees in the staging table and process them.
It will ignore supervisor for all the employees in creation.
***/
CURSOR CUR_SUP
IS
SELECT xxpll.ROWID ROW_ID ,
xxpll.*
FROM XXPLL_HR_CNV_PEOPLE_TMP xxpll
WHERE NVL(XXPLL.process_flag,'N') IN ('N','E')
ORDER BY hire_date;
--AND EMPLOYEE_NUMBER = NVL ( P_EMPLOYEE , EMPLOYEE_NUMBER );
--
v_person_type_id PER_PERSON_TYPES.PERSON_TYPE_ID%TYPE;
v_person_id apps.PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
v_assignment_id PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE;
v_per_object_version_number NUMBER;
v_asg_object_version_number NUMBER;
v_per_effective_start_date DATE;
v_per_effective_end_date DATE;
v_full_name VARCHAR2(100);
v_per_comment_id NUMBER;
v_assignment_sequence NUMBER;
v_assignment_number PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER%TYPE;
v_name_combination_warning BOOLEAN;
v_assign_payroll_warning BOOLEAN;
v_orig_hire_warning BOOLEAN;
v_address_id PER_ADDRESSES.ADDRESS_ID%TYPE;
v_object_version_number NUMBER;
v_party_id apps.PER_ALL_PEOPLE_F.PARTY_ID%TYPE;
v_row_count NUMBER := 0;
v_err_count NUMBER := 0;
v_Gender VARCHAR2(1);
v_emp_exists VARCHAR2(1) := 'N';
v_commit NUMBER := 0;
v_date DATE ; --:= to_date('01-JAN-1900','dd-MON-yyyy');
v_hiredate DATE;
v_omitted NUMBER := 0;
V_PDP_OBJECT_VERSION_NUMBER NUMBER;
--V_employee_numbers VARCHAR2(50) := '';
v_npw_number VARCHAR2(50);
v_npw_number1 VARCHAR2(50);
v_eff_start_date DATE;
l_dob DATE;
v_emp_count NUMBER;
v_blood_grp VARCHAR2(10); --Added by Amit Abhishek 18-Jun-10
v_nationality VARCHAR2(10); --Added by Amit Abhishek 18-Jun-10
v_panno VARCHAR2(10); --Added by Amit Abhishek 18-Jun-10
v_pan_count NUMBER; --Added by Amit Abhishek 18-Jun-10
-----Added by Subrata on 28.01.2011-----------------------
s_person_id number;
s_employee_number VARCHAR2(4000);
s_asg_eff_start_date date;
s_object_version_number number;
s_person_type_id number;
s_last_name VARCHAR2(4000);
s_first_name VARCHAR2(4000);
s_middle_name VARCHAR2(4000);
s_date_of_birth date;
s_email_address VARCHAR2(4000);
s_marital_status VARCHAR2(4000);
s_nationality VARCHAR2(4000);
s_national_identifier VARCHAR2(4000);
s_disability VARCHAR2(4000);
s_gender VARCHAR2(4000);
s_title VARCHAR2(4000);
s_date_of_death date;
s_background_check_status VARCHAR2(4000);
s_background_date_check date;
s_blood_type VARCHAR2(4000);
s_original_date_of_hire date;
s_town_of_birth VARCHAR2(4000);
s_region_of_birth VARCHAR2(4000);
s_country_of_birth VARCHAR2(4000);
s_effective_start_date date;
s_effective_end_date date;
s_full_name VARCHAR2(4000);
s_comment_id number;
s_name_combination_warning boolean;
s_assign_payroll_warning boolean;
s_orig_hire_warning boolean;
---------------------------------------------------------
BEGIN
p_log ( 'Business Group ID from the Profile PER_BUSINESS_GROUP_ID = ' || g_business_group_id );
FOR emp_sup IN CUR_SUP
LOOP
----------Added on 25.01.2011---------------------------
Begin
SELECT ppf.person_id,
ppf.employee_number,
ppf.effective_start_date,
ppf.object_version_number,
ppf.person_type_id,
ppf.last_name,
ppf.first_name,
ppf.middle_names,
ppf.date_of_birth,
ppf.email_address,
ppf.marital_status,
ppf.nationality,
ppf.national_identifier,
ppf.registered_disabled_flag,
ppf.sex,
ppf.title,
ppf.date_of_death,
ppf.background_check_status,
ppf.background_date_check,
ppf.blood_type,
ppf.original_date_of_hire,
ppf.town_of_birth,
ppf.region_of_birth,
ppf.country_of_birth
INTO s_person_id,
s_employee_number,
s_asg_eff_start_date,
s_object_version_number,
s_person_type_id,
s_last_name,
s_first_name,
s_middle_name,
s_date_of_birth,
s_email_address,
s_marital_status,
s_nationality,
s_national_identifier,
s_disability,
s_gender,
s_title,
s_date_of_death,
s_background_check_status,
s_background_date_check,
s_blood_type,
s_original_date_of_hire,
s_town_of_birth,
s_region_of_birth,
s_country_of_birth
FROM PER_ALL_PEOPLE_F ppf
WHERE ppf.employee_number = emp_sup.employee_number
AND ppf.business_group_id = g_business_group_id
AND ppf.current_employee_flag = 'Y'
AND SYSDATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
IF NVL(emp_sup.Gender,'X') NOT IN ('M','F') THEN
IF upper(nvl(emp_sup.Title,'MR.')) <> 'MS.' THEN
s_Gender := 'M';
ELSE
s_Gender := 'F';
END IF;
ELSE
s_Gender := emp_sup.Gender;
END IF;
/*apps.HR_PERSON_API.update_person
(
p_validate => false
,p_effective_date => s_asg_eff_start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_person_id => s_person_id
,p_object_version_number => s_object_version_number
,p_person_type_id => s_person_type_id
,p_last_name => nvl(emp_sup.last_name,s_last_name)
,p_applicant_number =>null
,p_comments =>null
,p_date_employee_data_verified =>null
,p_date_of_birth => nvl(emp_sup.date_of_birth,s_date_of_birth)
,p_email_address => nvl(emp_sup.email_address,s_email_address)
,p_employee_number => s_employee_number
,p_expense_check_send_to_addres =>null
,p_first_name => nvl(emp_sup.first_name,s_first_name)
,p_known_as =>null
,p_marital_status => nvl(emp_sup.marital_status,s_marital_status)
,p_middle_names => nvl(emp_sup.middle_name,s_middle_name)
,p_nationality => nvl(emp_sup.nationality,s_nationality)
,p_national_identifier => null --nvl(emp_sup.national_identifier,s_national_identifier)
,p_previous_last_name =>null
,p_registered_disabled_flag => nvl(emp_sup.disabilty,s_disability)
,p_sex => s_Gender
,p_title => nvl(emp_sup.title,s_title)
,p_vendor_id =>null
,p_work_telephone =>null
,p_attribute_category =>TO_CHAR(FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'))
,p_attribute1 =>null
,p_attribute2 =>null
,p_attribute3 =>null
,p_attribute4 =>null
,p_attribute5 =>null
,p_attribute6 =>null
,p_attribute7 =>null
,p_attribute8 =>null
,p_attribute9 =>null
,p_attribute10 =>null
,p_attribute11 =>null
,p_attribute12 =>null
,p_attribute13 =>null
,p_attribute14 =>null
,p_attribute15 =>null
,p_attribute16 =>null
,p_attribute17 =>null
,p_attribute18 =>null
,p_attribute19 =>null
,p_attribute20 =>null
,p_attribute21 =>null
,p_attribute22 =>null
,p_attribute23 =>null
,p_attribute24 =>null
,p_attribute25 =>null
,p_attribute26 =>null
,p_attribute27 =>null
,p_attribute28 =>null
,p_attribute29 =>null
,p_attribute30 =>null
,p_per_information_category =>'IN'
,p_per_information1 =>null
,p_per_information2 =>null
,p_per_information3 =>null
,p_per_information4 =>null
,p_per_information5 =>null
,p_per_information6 =>null
,p_per_information7 =>'RO'
,p_per_information8 =>null
,p_per_information9 =>null
,p_per_information10 =>null
,p_per_information11 =>null
,p_per_information12 =>null
,p_per_information13 =>null
,p_per_information14 =>null
,p_per_information15 =>null
,p_per_information16 =>null
,p_per_information17 =>null
,p_per_information18 =>null
,p_per_information19 =>null
,p_per_information20 =>null
,p_per_information21 =>null
,p_per_information22 =>null
,p_per_information23 =>null
,p_per_information24 =>null
,p_per_information25 =>null
,p_per_information26 =>null
,p_per_information27 =>null
,p_per_information28 =>null
,p_per_information29 =>null
,p_per_information30 =>null
,p_date_of_death =>s_date_of_death
,p_background_check_status =>s_background_check_status
,p_background_date_check =>s_background_date_check
,p_blood_type =>s_blood_type
,p_correspondence_language =>null
,p_fast_path_employee =>null
,p_fte_capacity =>null
,p_hold_applicant_date_until =>null
,p_honors =>null
,p_internal_location =>null
,p_last_medical_test_by =>null
,p_last_medical_test_date =>null
,p_mailstop =>null
,p_office_number =>null
,p_on_military_service =>null
,p_pre_name_adjunct =>null
,p_projected_start_date =>null
,p_rehire_authorizor =>null
,p_rehire_recommendation =>null
,p_resume_exists =>null
,p_resume_last_updated =>null
,p_second_passport_exists =>null
,p_student_status =>null
,p_work_schedule =>null
,p_rehire_reason =>null
,p_suffix =>null
,p_benefit_group_id =>null
,p_receipt_of_death_cert_date =>null
,p_coord_ben_med_pln_no =>null
,p_coord_ben_no_cvg_flag =>null
,p_coord_ben_med_ext_er =>null
,p_coord_ben_med_pl_name =>null
,p_coord_ben_med_insr_crr_name =>null
,p_coord_ben_med_insr_crr_ident =>null
,p_coord_ben_med_cvg_strt_dt =>null
,p_coord_ben_med_cvg_end_dt =>null
,p_uses_tobacco_flag =>null
,p_dpdnt_adoption_date =>null
,p_dpdnt_vlntry_svce_flag =>null
,p_original_date_of_hire =>nvl(emp_sup.hire_date,s_original_date_of_hire)
,p_adjusted_svc_date =>null
,p_town_of_birth =>nvl(emp_sup.town_of_birth,s_town_of_birth)
,p_region_of_birth => nvl(emp_sup.region_of_birth,s_region_of_birth)
,p_country_of_birth => nvl(emp_sup.country_of_birth ,s_country_of_birth)
,p_global_person_id =>null
,p_party_id =>null
,p_npw_number =>null
,p_per_effective_start_date => s_effective_start_date
,p_per_effective_end_date => s_effective_end_date
,p_full_name => s_full_name
,p_comment_id => s_comment_id
,p_name_combination_warning => s_name_combination_warning
,p_assign_payroll_warning => s_assign_payroll_warning
,p_orig_hire_warning => s_orig_hire_warning
);
*/
apps.HR_PERSON_API.update_person(
p_validate => false
,p_effective_date => s_asg_eff_start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_object_version_number => s_object_version_number
,p_employee_number => s_employee_number
,p_comments =>null
,p_last_name => nvl(emp_sup.last_name,s_last_name)
,p_person_id => s_person_id
,p_person_type_id => s_person_type_id
,p_expense_check_send_to_addres =>null
,p_first_name => nvl(emp_sup.first_name,s_first_name)
,p_known_as =>null
,p_marital_status => nvl(emp_sup.marital_status,s_marital_status)
,p_middle_names => nvl(emp_sup.middle_name,s_middle_name)
,p_nationality => nvl(emp_sup.nationality,s_nationality)
,p_national_identifier => null --nvl(emp_sup.national_identifier,s_national_identifier)
,p_previous_last_name =>null
,p_registered_disabled_flag => nvl(emp_sup.disabilty,s_disability)
,p_sex => s_Gender
,p_title => nvl(emp_sup.title,s_title)
,p_vendor_id =>null
,p_work_telephone =>null
,p_attribute_category =>TO_CHAR(FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'))
,p_attribute1 =>null
,p_attribute2 =>null
,p_attribute3 =>null
,p_attribute4 =>null
,p_attribute5 =>null
,p_attribute6 =>null
,p_attribute7 =>null
,p_attribute8 =>null
,p_attribute9 =>null
,p_attribute10 =>null
,p_attribute11 =>null
,p_attribute12 =>null
,p_attribute13 =>null
,p_attribute14 =>null
,p_attribute15 =>null
,p_attribute16 =>null
,p_attribute17 =>null
,p_attribute18 =>null
,p_attribute19 =>null
,p_attribute20 =>null
,p_attribute21 =>null
,p_attribute22 =>null
,p_attribute23 =>null
,p_attribute24 =>null
,p_attribute25 =>null
,p_attribute26 =>null
,p_attribute27 =>null
,p_attribute28 =>null
,p_attribute29 =>null
,p_attribute30 =>null
, p_per_information_category => 'IN'
,p_per_information1 =>null
,p_per_information2 =>null
,p_per_information3 =>null
,p_per_information4 =>null
,p_per_information5 =>null
,p_per_information6 =>null
,p_per_information7 =>'RO'
,p_per_information8 =>null
,p_per_information9 =>null
,p_per_information10 =>null
,p_per_information11 =>null
,p_per_information12 =>null
,p_per_information13 =>null
,p_per_information14 =>null
,p_per_information15 =>null
,p_per_information16 =>null
,p_per_information17 =>null
,p_per_information18 =>null
,p_per_information19 =>null
,p_per_information20 =>null
,p_per_information21 =>null
,p_per_information22 =>null
,p_per_information23 =>null
,p_per_information24 =>null
,p_per_information25 =>null
,p_per_information26 =>null
,p_per_information27 =>null
,p_per_information28 =>null
,p_per_information29 =>null
,p_per_information30 =>null
,p_date_of_death =>s_date_of_death
,p_background_check_status =>s_background_check_status
,p_background_date_check =>s_background_date_check
,p_blood_type =>s_blood_type
,p_correspondence_language =>null
,p_fast_path_employee =>null
,p_fte_capacity =>null
,p_hold_applicant_date_until =>null
,p_honors =>null
,p_internal_location =>null
,p_last_medical_test_by =>null
,p_last_medical_test_date =>null
,p_mailstop =>null
,p_office_number =>null
,p_on_military_service =>null
,p_pre_name_adjunct =>null
,p_projected_start_date =>null
,p_rehire_authorizor =>null
,p_rehire_recommendation =>null
,p_resume_exists =>null
,p_resume_last_updated =>null
,p_second_passport_exists =>null
,p_student_status =>null
,p_work_schedule =>null
,p_rehire_reason =>null
,p_suffix =>null
,p_benefit_group_id =>null
,p_receipt_of_death_cert_date =>null
,p_coord_ben_med_pln_no =>null
,p_coord_ben_no_cvg_flag =>null
,p_coord_ben_med_ext_er =>null
,p_coord_ben_med_pl_name =>null
,p_coord_ben_med_insr_crr_name =>null
,p_coord_ben_med_insr_crr_ident =>null
,p_coord_ben_med_cvg_strt_dt =>null
,p_coord_ben_med_cvg_end_dt =>null
,p_uses_tobacco_flag =>null
,p_dpdnt_adoption_date =>null
,p_dpdnt_vlntry_svce_flag =>null
,p_original_date_of_hire =>nvl(emp_sup.hire_date,s_original_date_of_hire)
,p_adjusted_svc_date =>null
,p_town_of_birth =>nvl(emp_sup.town_of_birth,s_town_of_birth)
,p_region_of_birth => nvl(emp_sup.region_of_birth,s_region_of_birth)
,p_country_of_birth => nvl(emp_sup.country_of_birth ,s_country_of_birth)
,p_global_person_id =>null
,p_party_id =>null
,p_npw_number =>null
,p_comment_id => s_comment_id
,p_effective_start_date => s_effective_start_date
,p_effective_end_date => s_effective_end_date
,p_full_name => s_full_name
,p_name_combination_warning => s_name_combination_warning
,p_assign_payroll_warning => s_assign_payroll_warning
,p_orig_hire_warning =>s_orig_hire_warning
);
TRACE1('Employee Updated Successfully............ ' );
exception when no_data_found then
TRACE1('Call Create Employee API ' );
--------------------------------------------------------
v_row_count := v_row_count + 1;
v_error_msg := NULL;
v_error_msg1 := NULL;
G_EMP_NUMBER := NULL;
v_blood_grp := NULL; --Added by Amit Abhishek 18-Jun-10
v_panno := NULL; --Added by Amit Abhishek 18-Jun-10
v_nationality :=NULL; --Added by Amit Abhishek 18-Jun-10
v_pan_count :=0; --Added by Amit Abhishek 18-Jun-10
v_Gender := NULL; --Added by Amit Abhishek 30-Jun-10
--V_employee_numbers := NULL;
g_person_type := NULL;
v_person_type_id :=NULL;
IF MOD ( CUR_SUP%ROWCOUNT , 10 ) = 0 THEN
COMMIT;
END IF;
BEGIN
TRACE1('---------------------------------------------------------------------');
-- TRACE1('Processing person SSO = ' || emp_sup.employee_number );
TRACE1('USER PERSON TYPE = ' || emp_sup.user_person_type );
v_hiredate := NULL;
v_full_name := NULL;
v_error_msg := NULL;
-- v_employee_number := emp_sup.employee_number;
g_person_type := UPPER(emp_sup.system_person_type);
TRACE1('Validating Person type - ' || emp_sup.user_person_type );
--Modified by Amit Abhishek 11-Jun-10
BEGIN
SELECT person_type_id
INTO v_person_type_id
FROM apps.PER_PERSON_TYPES_V
WHERE system_person_type = emp_sup.system_person_type
AND UPPER(user_person_type) = UPPER(emp_sup.user_person_type);
EXCEPTION WHEN OTHERS
THEN
TRACE1('Invalid Person Type');
RAISE_APPLICATION_ERROR ( -20001, 'Invalid Person Type - ' || emp_sup.user_person_type ) ;
END;
--Change Over 11-Jun-10
TRACE1 ( 'Person Type ID = ' || v_person_type_id );
--Validating Gender
--Modified by Amit Abhishek 30-Jun-10
IF NVL(emp_sup.Gender,'X') NOT IN ('M','F') THEN
IF upper(nvl(emp_sup.Title,'MR.')) <> 'MS.' THEN
v_Gender := 'M';
ELSE
v_Gender := 'F';
END IF;
ELSE
v_Gender := emp_sup.Gender;
END IF;
--Change Over 30-Jun-10
v_eff_start_date := emp_sup.EFFECTIVE_START_DATE;
v_hiredate := emp_sup.hire_date;
IF v_eff_start_date < g_v_bus_date THEN
RAISE_APPLICATION_ERROR ( -20001, 'Effective Start date cannot be less than Business Group Start Date.' );
END IF;
TRACE1('HIRE DATE OF THE EMPLOYEE '||v_hiredate);
--Added by Amit ABhishek on 04-Jun-2010
l_dob := NULL;
IF emp_sup.date_of_birth > SYSDATE THEN
l_dob := add_months(emp_sup.date_of_birth,-1200);
ELSE
l_dob := emp_sup.date_of_birth;
END IF;
--Change Over 04-Jun-2010
-- IF g_person_type =UPPER(emp_sup.user_person_type) then
IF g_person_type = 'EMP' THEN
BEGIN
v_emp_count := 0;
--Modified by Amit Abhishek to check for duplicate at enterprise level 16-Nov-10
SELECT count(*)
INTO v_emp_count
FROM apps.PER_ALL_PEOPLE_F ppf,
apps.PER_ENT_SECURITY_GROUPS sg,
apps.PER_ENT_SECURITY_GROUPS sg1
WHERE attribute1 = emp_sup.employee_number
AND sg.business_group_id = g_business_group_id
AND sg1.business_group_id = ppf.business_group_id
AND sg.enterprise_id = sg1.enterprise_id;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
--Added by Amit Abhishek 18-Jun-10
BEGIN
--Validate Nationality
SELECT lookup_code
INTO v_nationality
FROM apps.hr_lookups
WHERE upper(lookup_type) = 'NATIONALITY'
AND upper(meaning) = upper(emp_sup.nationality)
AND enabled_flag = 'Y'
AND NVL(end_date_active,SYSDATE+1)>SYSDATE;
EXCEPTION
WHEN OTHERS THEN
v_error_msg1 := v_error_msg1||' Invalid Nationality.';
END;
BEGIN
--Validate Blood Group
SELECT lookup_code
INTO v_blood_grp
FROM apps.hr_lookups
WHERE upper(lookup_type) = 'BLOOD_TYPE'
AND lookup_code = emp_sup.Blood_type
AND enabled_flag = 'Y'
AND NVL(end_date_active,SYSDATE+1)>SYSDATE;
EXCEPTION
WHEN OTHERS THEN
v_error_msg1 := v_error_msg1||'Invalid Blood Group.';
END;
--Validate PAN
IF length(emp_sup.pan_number) = 10 THEN
v_panno := emp_sup.pan_number;
SELECT count(*)
INTO v_pan_count
FROM apps.PER_ALL_PEOPLE_F
WHERE per_information4 = v_panno
AND business_group_id = g_business_group_id
AND TRUNC(SYSDATE) between effective_start_date and effective_end_date
AND current_employee_flag = 'Y';
IF v_pan_count>0 THEN
v_panno := NULL;
v_error_msg1 := v_error_msg1||' Duplicate PAN number. PAN Number must be unique for the employees.';
END IF;
ELSE
v_error_msg1 := v_error_msg1||' Invalid PAN Number.';
END IF;
--Change Over 18-Jun-10
IF v_emp_count =0 THEN
IF emp_sup.system_person_type ='EMP' AND emp_sup.user_person_type='Permanent' THEN
G_EMP_NUMBER :='P'||xxplg_emp_global_number_seq.nextval;
ELSIF emp_sup.system_person_type='EMP' AND emp_sup.user_person_type='Contract' THEN
G_EMP_NUMBER :='C'||xxplg_con_global_number_seq.nextval;
END IF;
TRACE1('CALLING CREATE EMPLOYEE API FOR THE SSO '|| G_EMP_NUMBER||' ~ Person Type : '||emp_sup.user_person_type);
apps.HR_EMPLOYEE_API.CREATE_EMPLOYEE (
p_validate => false
,p_hire_date => v_eff_start_date -- NVL(emp_sup.EFFECTIVE_START_DATE,SYSDATE) ---NVL(emp_sup.hire_date,'01-JAN-1900')
,p_business_group_id => g_business_group_id
,p_last_name => initcap(emp_sup.last_name)
,p_sex => v_Gender
,p_person_type_id => v_person_type_id
,p_per_comments => NULL --emp_sup.comments
,p_date_employee_data_verified => NULL
,p_date_of_birth => l_dob
,p_email_address => emp_sup.email_address
,p_employee_number => G_EMP_NUMBER
,p_expense_check_send_to_addres => NULL --emp_sup.expense_check_send_to_addres
,p_first_name => initcap(emp_sup.first_name)
,p_known_as => NULL --emp_sup.known_as
,p_marital_status => emp_sup.Marital_Status --emp_sup.marital_status
,p_middle_names => initcap(emp_sup.middle_name)
,p_nationality => v_nationality --Modified by Amit Abhishek 18-Jun-10
,p_national_identifier => NULL
,p_previous_last_name => NULL
,p_registered_disabled_flag => NULL
,p_title => upper(emp_sup.Title)
,p_vendor_id => NULL
,p_work_telephone => NULL--emp_sup.DIRECT_NUMBER --emp_sup.work_telephone
--,p_attribute_category => null
,p_date_of_death => NULL
,p_background_check_status => NULL
,p_background_date_check => NULL
,p_blood_type => v_blood_grp --Modified by Amit Abhishek 18-Jun-10
,p_correspondence_language => NULL
,p_fast_path_employee => NULL
,p_fte_capacity => NULL
,p_honors => NULL
,p_internal_location => NULL --emp_sup.internal_location
,p_last_medical_test_by => NULL
,p_last_medical_test_date => NULL
,p_mailstop => NULL
,p_office_number => NULL --emp_sup.office_number
,p_on_military_service => NULL
,p_pre_name_adjunct => NULL
,p_projected_start_date => NULL --emp_sup.projected_start_date
,p_resume_exists => NULL
,p_resume_last_updated => NULL
,p_second_passport_exists => NULL
,p_student_status => NULL
,p_work_schedule => NULL
,p_suffix => NULL
,p_benefit_group_id => NULL
,p_receipt_of_death_cert_date => NULL
,p_coord_ben_med_pln_no => NULL
,p_coord_ben_no_cvg_flag => 'N'
,p_coord_ben_med_ext_er => NULL
,p_coord_ben_med_pl_name => NULL
,p_coord_ben_med_insr_crr_name => NULL
,p_coord_ben_med_insr_crr_ident => NULL
,p_coord_ben_med_cvg_strt_dt => NULL
,p_coord_ben_med_cvg_end_dt => NULL
,p_uses_tobacco_flag => NULL
,p_dpdnt_adoption_date => NULL
,p_dpdnt_vlntry_svce_flag => 'N'
,p_original_date_of_hire => v_hiredate --null
,p_adjusted_svc_date => NULL
,p_town_of_birth => emp_sup.town_of_birth
,p_region_of_birth => emp_sup.region_of_birth
,p_country_of_birth => emp_sup.country_of_birth
,p_global_person_id => NULL
,p_party_id => NULL
,p_person_id => v_person_id -- Out parameters
,p_assignment_id => v_assignment_id
,p_per_object_version_number => v_per_object_version_number
,p_asg_object_version_number => v_asg_object_version_number
,p_per_effective_start_date => v_per_effective_start_date
,p_per_effective_end_date => v_per_effective_end_date
,p_full_name => v_full_name
,p_per_comment_id => v_per_comment_id
,p_assignment_sequence => v_assignment_sequence
,p_assignment_number => v_assignment_number
,p_name_combination_warning => v_name_combination_warning
,p_assign_payroll_warning => v_assign_payroll_warning
,p_orig_hire_warning => v_orig_hire_warning
,p_attribute_category => TO_CHAR(FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')) --Modified by Amit Abhishek 04-Jun-10
,p_attribute1 => emp_sup.employee_number
,p_attribute2 => emp_sup.BGC_CONDUCTED
,p_attribute3 => emp_sup.BGC_STATUS
,p_attribute4 => emp_sup.BGC_CONDUCTED_ON
,p_per_information_category => 'IN'
,p_per_information1 => NULL
,p_per_information2 => NULL
,p_per_information3 => NULL
,p_per_information4 => v_panno --Modified by Amit Abhishek 18-Jun-10
,p_per_information5 => emp_sup.pan_applied_for
,p_per_information6 => emp_sup.ex_serviceman
,p_per_information7 => 'RO'--emp_sup.residential_status
,p_per_information8 => emp_sup.pf_number
,p_per_information9 => emp_sup.esi_number
,p_per_information10 => emp_sup.superannuation_number
,p_per_information11 => emp_sup.group_insurance_number
,p_per_information12 => emp_sup.gratuity_number
,p_per_information13 => emp_sup.pension_fund_number
,p_per_information14 => emp_sup.pan_reference_number
,p_per_information15 => emp_sup.NSSN
,p_per_information16 => NULL
,p_per_information17 => NULL
,p_per_information18 => NULL
,p_per_information19 => NULL
,p_per_information20 => NULL
,p_per_information21 => NULL
,p_per_information22 => NULL
,p_per_information23 => NULL
,p_per_information24 => NULL
,p_per_information25 => NULL
,p_per_information26 => NULL
,p_per_information27 => NULL
,p_per_information28 => NULL
,p_per_information29 => NULL
,p_per_information30 => NULL ) ;
TRACE1('>>>>>>>>OUT OF THE CREATE EMPLOYEE API');
p_update_errm ( p_process_flag => 'C' , p_errm => v_error_msg1, p_rowid => emp_sup.row_id );
TRACE1('>>>>>EMPLOYEE CREATION SUCCEEDED.' );
TRACE1('THE EMPLOYEE_Number '||G_EMP_NUMBER);
UPDATE XXPLL_HR_CNV_PEOPLE_TMP
SET New_Employee_number=G_EMP_NUMBER
WHERE employee_number =emp_sup.Employee_number;
ELSE
--Added by Amit Abhishek 11-Jun-10
p_update_errm ( p_process_flag => 'E' , p_errm => 'Employee already exists', p_rowid => emp_sup.row_id );
v_err_count := v_err_count + 1;
--Change over 11-Jun-10
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
v_err_count := v_err_count + 1;
v_error_msg1 := SUBSTR ( SQLERRM ,12 );
TRACE1('>>>>>EMPLOYEE CREATION FAILED:'|| v_error_msg1 );
p_update_errm ( p_process_flag => 'E' , p_errm => v_error_msg1, p_rowid => emp_sup.row_id );
v_error_msg := SUBSTR(SQLERRM,1,1000);
END;
end;----Added on 25.01.2011
END LOOP;
COMMIT;
p_log ( '--------------------------------------------------------------------------');
p_log ( 'Number of records procesed for Employee Creation : ' || v_row_count );
p_log ( 'Number of records errorred for Employee Creation : ' || v_err_count );
p_log ( '--------------------------------------------------------------------------');
EXCEPTION
WHEN OTHERS THEN
p_log ('Unexpected error in CREATE_EMPLOYEE :' || SQLERRM );
RAISE;
END CREATE_EMPLOYEE ;
-----------------------------------------------------------END CREATE EMPLOYEE--------------------------------------------
----------------------------------------------------------CREATE ASSIGNMENT----------------------------------------------
-- This Procedure Written for Creation of an Assignment of an Employee---
-- TRACE1('THE EMPLOYEE_Number '||V_employee_numbers);
PROCEDURE CREATE_ASSIGNMENT(
P_EMPLOYEE IN VARCHAR2 )
IS
v_person_type_id PER_PERSON_TYPES.PERSON_TYPE_ID%TYPE;
v_person_id apps.PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
v_assignment_id PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE;
v_per_object_version_number NUMBER;
v_asg_object_version_number NUMBER;
v_per_effective_start_date DATE;
v_per_effective_end_date DATE;
v_full_name VARCHAR2(4000);
v_per_comment_id NUMBER;
v_assignment_sequence NUMBER;
v_assignment_number PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER%TYPE;
v_name_combination_warning BOOLEAN;
v_assign_payroll_warning BOOLEAN;
v_orig_hire_warning BOOLEAN;
v_address_id PER_ADDRESSES.ADDRESS_ID%TYPE;
v_object_version_number NUMBER;
v_party_id apps.PER_ALL_PEOPLE_F.PARTY_ID%TYPE;
v_row_count NUMBER := 0;
v_err_count NUMBER := 0;
v_sex VARCHAR2(1);
v_emp_exists VARCHAR2(1) := 'N';
v_commit NUMBER := 0;
v_date DATE ; --:= to_date('01-JAN-1900','dd-MON-yyyy');
v_hiredate DATE;
v_omitted NUMBER := 0;
V_PDP_OBJECT_VERSION_NUMBER NUMBER;
v_npw_number VARCHAR2(50);
v_npw_number1 VARCHAR2(50);
CURSOR CUR_EMP
IS
SELECT xxpll.ROWID ROW_ID,
xxpll.*
FROM XXPLL_HR_CNV_ASSIGN_TMP xxpll
WHERE NVL(process_flag,'N') IN ('N','E')
and employee_number in ('SSSS','PPPP','RRRR')
ORDER BY EMPLOYEE_NUMBER,EFFECTIVE_START_DATE;
-- AND EMPLOYEE_NUMBER = NVL ( P_EMPLOYEE , EMPLOYEE_NUMBER );
BEGIN
p_log ( '------------------------------------------------------------------------');
p_log ('Entering CREATE_ASSIGNMENT for Assignment handling...' );
p_log ( '------------------------------------------------------------------------');
----Added on 02.02.2011-------------------------------------------------------------
delete_assignment;
------------------------------------------------------------------------------------
FOR emp_cur IN CUR_EMP
LOOP
BEGIN
v_row_count := v_row_count + 1;
v_error_msg1 := NULL;
v_error_msg := NULL;
G_EMP_NUMBER :=NULL;
-- v_employee_number := NULL;
-- V_employee_numbers :=NULL;
g_person_type := NULL;
IF MOD ( CUR_EMP%ROWCOUNT, 10 ) = 0 THEN
COMMIT;
END IF;
TRACE1('------------------------------------------------------------------------------------');
--Added by Amit Abhishek 01-Jul-10
BEGIN
SELECT employee_number
INTO G_EMP_NUMBER
FROM per_all_people_f
WHERE attribute1 = emp_cur.employee_number
AND business_group_id = g_business_group_id
AND current_employee_flag = 'Y'
AND SYSDATE BETWEEN effective_start_date and effective_end_date;
EXCEPTION WHEN OTHERS
THEN
NULL;
END;
--G_EMP_NUMBER := emp_cur.New_Employee_number;
--Change Over 01-Jul-10
--g_person_type := UPPER(emp_cur.system_person_type);
TRACE1('EMPLOYEE_NUMBER : '|| G_EMP_NUMBER||' ~ Person Type = ' ||g_person_type );
--IF g_person_type = 'EMP' THEN
p_check_emp_exist ( p_emp_number => emp_cur.employee_number , p_omitted => v_omitted );
--END IF;
IF v_omitted > 0 THEN
BEGIN
v_hiredate := null;
--TRACE1('>>>>>>>>INSIDE LOOP EMP_CUR IN CUR_EMP',g_debug);
--v_hiredate := emp_cur.hire_date;
/*IF emp_cur.ADDRESS_LINE1 IS NOT NULL THEN
TRACE1('Calling CREATE_ADDRESS');
CREATE_ADDRESS(G_EMP_NUMBER
,TRUNC(SYSDATE)
,'Y'
,emp_cur.ADDRESS_LINE1
,emp_cur.ADDRESS_LINE2
,emp_cur.ADDRESS_LINE3
,emp_cur.TOWN_OR_CITY
,emp_cur.POSTAL_CODE
,emp_cur.COUNTRY
,emp_cur.address_style
,g_person_type
,emp_cur.row_id);
END IF;*/
TRACE1('Calling UPDATE_ASSIGNMENT....');
UPDATE_ASSIGNMENT(
G_EMP_NUMBER
,emp_cur.supervisor_number
,g_business_group_id
,emp_cur.location_code
,emp_cur.present_position_title
,emp_cur.JOB_Name
,emp_cur.Grade
,emp_cur.ORGANIZATION_NAME_ORG
,emp_cur.group_name
,emp_cur.subgroup_name
,emp_cur.PROBATION_PERIOD
,emp_cur.PROBATION_UNIT
,emp_cur.Assignment_Category
,emp_cur.GREORLEGALENTITY
,emp_cur.PFORGANIZATION
,emp_cur.PROFESSIONALTAXORGANISATION
,emp_cur.ESIORGANIZATION
,emp_cur.FACTORY
,emp_cur.ESTABLISHMENT
,emp_cur.COVERED_BY_GRATUITY_ACT
,emp_cur.HAVING_SUBSTANTIAL_INTEREST
,emp_cur.DIRECTOR
,emp_cur.SPECIFIED_EMPLOYEE
,emp_cur.PFEPSCONTRIBUTION
,g_person_type
,emp_cur.payroll_name
,emp_cur.role_if_any
,emp_cur.FUNCTIONAL_MGR
,emp_cur.effective_start_date --Added by Amit Abhishek 01-Jul-10
,emp_cur.effective_end_date --Added by Amit Abhishek 01-Jul-10
,emp_cur.row_id
);
TRACE1('After Calling UPDATE_ASSIGNMENT....');
IF v_error_msg1 IS NULL THEN
p_update_asg_errm ( p_process_flag => 'S' , p_errm => v_error_msg1, p_empnum => G_EMP_NUMBER, p_rowid => emp_cur.row_id );
TRACE1('>>>>>EMPLOYEE CREATION/UPDATION ASSIGNMENT SUCCEEDED.' );
ELSE
v_err_count := v_err_count + 1;
p_update_asg_errm ( p_process_flag => 'E' , p_errm => v_error_msg1, p_empnum => G_EMP_NUMBER, p_rowid => emp_cur.row_id );
TRACE1('>>>>>EMPLOYEE CREATION/UPDATION ASSIGNMENT HAS EITHER FAILED OR COMPLETED WITH WARNING:'|| v_error_msg1 );
END IF;
EXCEPTION
WHEN OTHERS THEN
v_err_count := v_err_count + 1;
v_error_msg1 := SUBSTR(SQLERRM,12 );
TRACE1('>>>>>EMPLOYEE CREATION/UPDATION ASSIGNMENT FAILED:'|| v_error_msg1 );
p_update_asg_errm ( p_process_flag => 'E' , p_errm => v_error_msg1, p_empnum => G_EMP_NUMBER, p_rowid => emp_cur.row_id );
END;
END IF;
EXCEPTION
WHEN OTHERS THEN
v_err_count := v_err_count + 1;
v_error_msg1 := SUBSTR(SQLERRM,12 );
TRACE1('>>>>>EMPLOYEE CREATE_ASSIGNMENT FAILED:'|| v_error_msg1 );
p_update_asg_errm ( p_process_flag => 'E' , p_errm => v_error_msg1, p_empnum => G_EMP_NUMBER, p_rowid => emp_cur.row_id );
END;
END LOOP;
p_log ( '--------------------------------------------------------------------------');
p_log ( 'Number of records procesed for Assignment Creation or Updation : ' || v_row_count );
p_log ( 'Number of records errorred for Assignment Creation or Updation : ' || v_err_count );
p_log ( '--------------------------------------------------------------------------');
EXCEPTION
WHEN OTHERS THEN
p_log ('Unexpected error in CREATE_ASSIGNMENT :' || SQLERRM );
RAISE;
END CREATE_ASSIGNMENT;
------------------------------------------------------------END CREATE ASSIGNMENT-------------------------------------------------------
------------------------------------------------------------UPDATE ASSIGNMENT------------------------------------------------------------
PROCEDURE UPDATE_ASSIGNMENT(
P_EMPLOYEE_NUMBER IN VARCHAR2 ,
P_SUPERVISOR_SSO IN VARCHAR2 ,
P_ORGANIZATION_ID IN NUMBER ,
P_LOCATION IN VARCHAR2 ,
P_POSITION_NAME IN VARCHAR2 ,
P_JOB IN VARCHAR2 ,
P_Grade IN VARCHAR2 ,
p_organization IN VARCHAR2 ,
P_SEGMENT1 IN VARCHAR2 ,
P_SEGMENT2 IN VARCHAR2 ,
P_Probation_PERIOD IN VARCHAR2 ,
P_Probation_Unit IN VARCHAR2 ,
P_Employement_Category IN VARCHAR2 ,
PS_SEGMENT1 IN VARCHAR2 ,
PS_SEGMENT2 IN VARCHAR2 ,
PS_SEGMENT3 IN VARCHAR2 ,
PS_SEGMENT4 IN VARCHAR2 ,
PS_SEGMENT5 IN VARCHAR2 ,
PS_SEGMENT6 IN VARCHAR2
-- ,PS_SEGMENT7 IN VARCHAR2
,
PS_SEGMENT8 IN VARCHAR2 ,
PS_SEGMENT9 IN VARCHAR2 ,
PS_SEGMENT10 IN VARCHAR2 ,
PS_SEGMENT11 IN VARCHAR2 ,
PS_SEGMENT12 IN VARCHAR2 ,
P_TYPE IN VARCHAR2 ,
P_PAYROLL_NAME IN VARCHAR2,
P_ROLE IN VARCHAR2, --Added by Amit Abhishek 18-Jun-10
P_FUNC_MGR IN VARCHAR2, --Added by Amit Abhishek 18-Jun-10
P_EFFECTIVE_START_DATE DATE, --Added by Amit Abhishek 01-Jul-10
P_EFFECTIVE_END_DATE DATE, --Added by Amit Abhishek 01-Jul-10
P_ROWID IN ROWID
)
IS
-- Get Assignment id
CURSOR CUR_ASSIGNMENT_ID(p_employee_number VARCHAR2)
IS
SELECT assignment_id ,
person_id,
assignment_number ,
object_version_number
FROM apps.PER_ALL_ASSIGNMENTS_F
WHERE person_id =
(SELECT person_id
FROM apps.PER_ALL_PEOPLE_F
WHERE employee_number = p_employee_number
AND NVL(effective_end_date,sysdate) >= sysdate
)
AND business_group_id =P_ORGANIZATION_ID
AND assignment_type = 'E'
AND assignment_status_type_id =1
AND SYSDATE BETWEEN effective_start_date and effective_end_date
ORDER BY object_version_number DESC;
-- Declaration of Variable Start
v_asg_eff_start_date DATE := NULL;
v_asg_eff_end_date DATE := NULL;
v_concatenated_segments VARCHAR2(4000);
v_comment_id NUMBER;
v_effective_start_date DATE := NULL;
v_effective_end_date DATE :=NULL;
v_no_managers_warning BOOLEAN;
v_other_manager_warning BOOLEAN;
v_object_version_number NUMBER;
v_emp_number VARCHAR2(240);
v_assignment_id PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE;
v_supervisor_id NUMBER := NULL;
v_effective_date DATE;
v_people_group_id NUMBER;
v_special_ceiling_step_id NUMBER;
v_group_name VARCHAR2(240);
v_org_now_no_manager_warning BOOLEAN;
v_spp_delete_warning BOOLEAN;
v_entries_changed_warning VARCHAR2(4000);
v_tax_district_changed_warning BOOLEAN;
v_location_id HR_LOCATIONS.LOCATION_ID%TYPE;
v_position_id HR_POSITIONS_F.POSITION_ID%TYPE;
v_job_id PER_JOBS.JOB_ID%TYPE;
v_grade_id PER_GRADES.GRADE_ID%TYPE;
V_ORG_ID hr_organization_units.organization_id%Type;
V_Soft_Coding_id per_all_assignments_f.soft_coding_keyflex_id%type;
v_assignment_number PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER%TYPE;
v_row_count NUMBER := 0;
v_err_count NUMBER := 0;
v_set_of_book_name apps.GL_SETS_OF_BOOKS.NAME%TYPE;
v_set_of_book_id apps.GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID%TYPE;
v_commit NUMBER :=0;
v_org_name VARCHAR2(4000);
v_emp_found VARCHAR2(1) :='T';
v_organization_name VARCHAR2(4000);
V_HOURLY_SALARIED_WARNING BOOLEAN;
--v_org_name varchar2(30);
l_object_ver NUMBER;
l_sup_effective_start_date DATE;
l_payroll_start_date DATE;
l_payroll_id NUMBER;
v_role VARCHAR2(4000) :=NULL;
v_fmgr_id NUMBER :=NULL;
v_fmgr_name VARCHAR2(240):=NULL;
v_role_name VARCHAR2(150):=NULL;
v_hist_flag VARCHAR2(1) := 'Y'; --Added by Amit Abhishek 01-Jul-2010
v_count_asg NUMBER :=0; --Added by Amit Abhishek 01-Jul-2010
v_count_ovn NUMBER :=0; --Added by Amit Abhishek 01-Jul-2010
v_person_id NUMBER :=NULL; --Added by Amit Abhishek 01-Jul-2010
BEGIN
v_emp_found := 'T';
TRACE1('---------------------------------------------------------------------------------------------------------');
v_emp_number := P_EMPLOYEE_NUMBER ;
v_assignment_id := NULL;
v_assignment_number := NULL;
v_object_version_number := NULL;
v_job_id := NULL;
v_supervisor_id := NULL;
--v_asg_eff_start_date := P_EFFECTIVE_START_DATE;
--v_asg_eff_end_date := P_EFFECTIVE_END_DATE;
OPEN CUR_ASSIGNMENT_ID(v_emp_number);
FETCH CUR_ASSIGNMENT_ID
INTO v_assignment_id,
v_person_id,
v_assignment_number,
v_object_version_number;
TRACE1('v_object_version_number for Ravi - '||v_object_version_number);
IF(CUR_ASSIGNMENT_ID%NOTFOUND) THEN
TRACE1('ASSIGNMENT_ID NOT FOUND FOR THE EMPLOYEE: '||P_EMPLOYEE_NUMBER);
v_emp_found := 'F';
ELSE
TRACE1('ASSIGNMENT_NUMBER FOUND FOR THE EMPLOYEE: ' || P_EMPLOYEE_NUMBER );
END IF;
CLOSE CUR_ASSIGNMENT_ID;
IF v_emp_found ='T' THEN
IF p_location IS NOT NULL THEN
v_location_id := NULL;
BEGIN
SELECT location_id
INTO v_location_id
FROM apps.HR_LOCATIONS
WHERE UPPER(location_code) = UPPER(p_location);
EXCEPTION WHEN OTHERS
THEN
v_location_id := NULL;
END;
END IF;
-- To Fetch the ORG
v_org_id:= NULL;
IF p_organization IS NOT NULL THEN
BEGIN
SELECT organization_id
INTO v_org_id
FROM apps.hr_all_organization_units
WHERE UPPER(name) = UPPER(p_organization)
AND business_group_id = P_ORGANIZATION_ID
AND NVL(date_to,SYSDATE+1)>SYSDATE;
EXCEPTION WHEN OTHERS
THEN
v_error_msg1 := v_error_msg1 || ' ~ ' || 'ORG Not found';
v_org_id := NULL;
END;
--Fetch organization Location if location code doesn't exists
IF v_location_id IS NULL and v_org_id IS NOT NULL THEN
BEGIN
SELECT location_id
INTO v_location_id
FROM apps.hr_all_organization_units
WHERE organization_id = v_org_id;
EXCEPTION
WHEN OTHERS THEN
v_location_id :=NULL;
END;
END IF;
--Fetch business group location if org location is null
IF v_location_id IS NULL THEN
BEGIN
SELECT location_id
INTO v_location_id
FROM apps.hr_all_organization_units
WHERE organization_id = P_ORGANIZATION_ID;
EXCEPTION
WHEN OTHERS THEN
v_location_id :=NULL;
v_error_msg1 := v_error_msg1 || ' ~ ' || 'Location Not found';
END;
END IF;
TRACE1('V_LOCATION_ID '||v_location_id);
ELSE
v_error_msg1 := v_error_msg1 || ' ~ ' || 'ORG Not found';
END IF;
/* --Commented by Amit Abhishek 01-Jul-10
BEGIN
v_effective_date:=null;
SELECT effective_start_date
INTO v_effective_date
FROM apps.PER_ALL_PEOPLE_F
WHERE employee_number = v_emp_number
AND NVL(effective_end_date,sysdate) >= sysdate;
EXCEPTION WHEN OTHERS
THEN
v_error_msg1 := v_error_msg1 || ' ~ ' || 'Effective Date Not Found';
END;*/
--Added by Amit Abhishek 01-Jul-10
IF P_EFFECTIVE_START_DATE IS NULL THEN
v_error_msg1 := v_error_msg1 || ' ~ ' || 'Effective Date Not Found';
ELSE
v_effective_start_date := P_EFFECTIVE_START_DATE;
END IF;
IF P_EFFECTIVE_END_DATE IS NULL THEN
v_effective_end_date := '31-DEC-4712';
ELSE
v_effective_end_date := P_EFFECTIVE_END_DATE;
END IF;
--Change over 01-Jul-10
-- To Fetch the SuperVisor
BEGIN
v_supervisor_id :=NULL;
IF p_supervisor_sso IS NOT NULL THEN
SELECT person_id
INTO v_supervisor_id
FROM apps.PER_ALL_PEOPLE_F
WHERE attribute1 = p_supervisor_sso
AND business_group_id = P_ORGANIZATION_ID
AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
ELSE
v_error_msg1 := v_error_msg1 || ' ~ ' || 'Supervisor Not found';
END IF;
EXCEPTION WHEN OTHERS
THEN
v_error_msg1 := v_error_msg1 || ' ~ ' || 'Supervisor Not found';
v_supervisor_id := NULL;
END;
-- To Fetch the JOB
-- Modified by Amit Abhishek 04-Jun-10
BEGIN
v_job_id := NULL;
IF P_JOB IS NOT NULL THEN
SELECT job_id
INTO v_job_id
FROM apps.PER_JOBS
WHERE name = p_job
AND business_group_id=P_ORGANIZATION_ID
AND NVL(date_to,SYSDATE+1)>SYSDATE;
ELSE
v_error_msg1 := v_error_msg1 || ' ~ ' || 'Job Not found';
END IF;
EXCEPTION WHEN OTHERS
THEN
v_job_id := null;
v_error_msg1 := v_error_msg1 || ' ~ ' || 'Job Not found';
END;
/* To Fetch the Grade*/
-- Modified by Amit Abhishek 04-Jun-10
BEGIN
v_Grade_id := NULL;
IF P_Grade IS NOT NULL THEN
SELECT Grade_id
INTO V_Grade_ID
FROM apps.PER_Grades
WHERE name = p_Grade
AND business_group_id=P_ORGANIZATION_ID
AND NVL(date_to,SYSDATE+1)>SYSDATE;
TRACE1('V_Grade_id '||V_Grade_ID);
ELSE
v_error_msg1 := v_error_msg1 || ' ~ ' || 'Grade Not found';
END IF;
EXCEPTION WHEN OTHERS
THEN
v_error_msg1 := v_error_msg1 || ' ~ ' || 'Grade Not found';
v_Grade_id := null;
END;
--Change over 04-Jun-10
--Added by Amit Abhishek 02-JUN-2010
BEGIN
l_sup_effective_start_date :=NULL;
IF v_supervisor_id IS NOT NULL THEN
SELECT effective_start_date
INTO l_sup_effective_start_date
FROM apps.PER_ALL_PEOPLE_F
WHERE person_id = v_supervisor_id
AND business_group_id = P_ORGANIZATION_ID
AND SYSDATE BETWEEN effective_start_date AND effective_end_date ;
END IF;
l_payroll_start_date :=NULL;
l_payroll_id :=NULL;
SELECT payroll_id,
effective_start_date
INTO l_payroll_id,
l_payroll_start_date
FROM apps.pay_all_payrolls_f
WHERE payroll_name = P_PAYROLL_NAME
--from pay_all_payrolls_f where payroll_name = 'Monthly Payroll PLE'
--from pay_all_payrolls_f where payroll_name = 'Monthly Payroll - Corporate'
AND business_group_id = P_ORGANIZATION_ID
AND sysdate BETWEEN effective_start_date AND effective_end_date;
EXCEPTION
WHEN OTHERS THEN
l_payroll_start_date :=NULL;
l_payroll_id :=NULL;
v_error_msg1 := v_error_msg1 || ' ~ ' || 'Payroll Not found';
END;
--Change Over 02-Jun-2010
/* To Get People Group */
TRACE1('p_Segment1 '||p_Segment1);
v_people_group_id :=NULL;
IF (p_Segment1 IS NOT NULL)THEN
BEGIN
SELECT people_group_id
INTO v_people_group_id
FROM apps.pay_people_groups
WHERE segment1 = p_segment1
AND (( segment2 = p_segment2 AND p_segment2 IS NOT NULL)
OR
(segment2 IS NULL AND p_segment2 IS NULL)
);
EXCEPTION WHEN OTHERS
THEN
v_people_group_id :=NULL;
v_error_msg1 := v_error_msg1 || ' ~ ' || 'People Group Not found';
END;
END IF;
TRACE1('v_people_group_id '||v_people_group_id);
TRACE1('~~~ Done with people grp');
/* To Get SOFT Coded Key FlexFiled*/
BEGIN
v_soft_coding_id := NULL;
--Modified by Amit Abhishek 01-Jul-10
SELECT soft_coding_keyflex_id
INTO v_soft_coding_id
FROM apps.HR_SOFT_CODING_KEYFLEX HSCK,
apps.fnd_id_flex_structures_vl FFSV
WHERE HSCK.id_flex_num =HSCK.id_flex_num
AND ffsv.id_flex_structure_code='IN_STATUTORY_INFO'
AND HSCK.Segment1 = DECODE(PS_SEGMENT1, 'Punj Lloyd Group Corporate', '82','PL Engineering Limited','1296', 'XX')
AND NVL(hsck.segment2,'XX') = DECODE(PS_SEGMENT2,'Punj Lloyd Group EPF Trust','826','XX')
AND NVL(hsck.segment3,'XX') = NVL(PS_SEGMENT3,'XX')
AND NVL(hsck.segment4,'XX') = NVL(PS_SEGMENT4,'XX')
AND NVL(hsck.segment5,'XX') = NVL(PS_SEGMENT5,'XX')
AND NVL(hsck.segment6,'XX') = NVL(PS_SEGMENT6,'XX')
AND hsck.segment8 = NVL(PS_SEGMENT8,'N')
AND hsck.segment9 = NVL(PS_SEGMENT9,'N')
AND hsck.segment10 = NVL(PS_SEGMENT10,'N')
AND hsck.segment11 = NVL(PS_SEGMENT11,'Y')
AND NVL(hsck.segment12,'XX') = NVL(PS_SEGMENT12,'XX');
--Change Over 01-Jul-10
EXCEPTION WHEN OTHERS
THEN
v_soft_coding_id := NULL;
v_error_msg1 := v_error_msg1 || ' ~ ' || 'Soft Coding Not found';
END;
--Added by Amit Abhishek 18-Jun-10
BEGIN
--Validating functional manager's employee number
SELECT person_id,
apps.hr_general.decode_person_name(person_id) --Added by Amit Abhishek 24-Jun-10
INTO v_fmgr_id, v_fmgr_name
FROM apps.PER_ALL_PEOPLE_F
WHERE attribute1 = P_FUNC_MGR
AND business_group_id = P_ORGANIZATION_ID
AND current_employee_flag = 'Y'
AND SYSDATE BETWEEN effective_start_date and effective_end_date;
EXCEPTION WHEN OTHERS
THEN
v_error_msg1 := v_error_msg1 ||' ~ ' ||'Functional Mgr Not Found.';
END;
--Validating Role
BEGIN
SELECT lookup_code,
apps.hr_general.decode_lookup('XXPLG_ROLE',lookup_code) --Added by Amit Abhishek 24-Jun-10
INTO v_role, v_role_name
FROM apps.FND_LOOKUP_VALUES
WHERE lookup_type='XXPLG_ROLE'
AND ( tag like ('%+'||to_char(P_ORGANIZATION_ID)||'+%')
or tag is null)
and upper(meaning) = upper(P_ROLE)
AND enabled_flag = 'Y'
AND NVL(end_date_active,SYSDATE+1)>SYSDATE;
EXCEPTION WHEN OTHERS
THEN
v_error_msg1 := v_error_msg1 ||' ~ ' ||'Role Not Found.';
END;
--Change Over 18-Jun-10
TRACE1(' V_Soft_Coding_id '|| V_Soft_Coding_id);
--END IF;
--
TRACE1('~ ~ ~ Calling for Pro Unit');
/* --Commented by Amit Abhishek on 30-Jun-10
--Do not default organization
IF v_org_id IS NULL THEN
v_org_id :=P_ORGANIZATION_ID;
END IF;*/
TRACE1('AFTER IF V_ORG_id - '||V_Org_ID);
--Added by Amit Abhishek 01-Jul-2010
BEGIN
--Check for initial assignment
SELECT count(*)
INTO v_count_asg
FROM per_all_assignments_f
WHERE person_id = v_person_id
AND business_group_id = P_ORGANIZATION_ID;
IF v_count_asg = 1 THEN
SELECT count(*)
INTO v_count_ovn
FROM per_all_assignments_f
WHERE person_id = v_person_id
AND business_group_id = P_ORGANIZATION_ID
AND object_version_number =1;
IF v_count_ovn =1 THEN
v_hist_flag := 'N';
END IF;
END IF;
EXCEPTION WHEN OTHERS
THEN
NULL;
END;
--TRACE1('v_effective_start_date '||v_effective_start_date);
--Change Over 01-Jul-2010
-- IF v_org_id IS NOT NULL AND v_hist_flag = 'N' THEN --Modified by Amit Abhishek 01-Jul-10
BEGIN
apps.HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA (
p_effective_date => v_effective_start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => v_assignment_id
,p_validate => false
,p_called_from_mass_update => false
,p_position_id => v_position_id
,p_job_id => v_job_id
-- ,p_payroll_id => NVL(l_payroll_id,apps.hr_api.g_number)
,p_grade_id => v_grade_id
,p_location_id => v_location_id
,p_organization_id => V_ORG_ID
,p_pay_basis_id => apps.hr_api.g_number
,p_employment_category => P_Employement_Category
,p_concat_segments => apps.hr_api.g_varchar2
,p_people_group_id => v_people_group_id
,p_object_version_number => v_object_version_number
,p_special_ceiling_step_id => v_special_ceiling_step_id
,p_group_name => v_group_name
,p_effective_start_date => v_asg_eff_start_date
,p_effective_end_date => v_asg_eff_end_date
,p_org_now_no_manager_warning => v_org_now_no_manager_warning
,p_other_manager_warning => v_other_manager_warning
,p_spp_delete_warning => v_spp_delete_warning
,p_entries_changed_warning => v_entries_changed_warning
,p_tax_district_changed_warning => v_tax_district_changed_warning
);
EXCEPTION WHEN OTHERS THEN
v_error_msg1 := SUBSTR(SQLERRM,1,200) ;
TRACE1 ( 'Error in UPDATE_EMP_ASG_CRITERIA : ' || v_error_msg1 );
-- p_update_emp ( p_process_flag => 'E' , p_errm => v_error_msg1, p_empno => V_EMP_NUMBER );
RAISE_APPLICATION_ERROR ( -20001, 'Error in UPDATE_EMP_ASG_CRITERIA : ' || v_error_msg1 );
END;
BEGIN
--Modified by Amit Abhishek 02-Jun-2010
IF NVL(l_sup_effective_start_date,v_effective_start_date-1) <= v_effective_start_date THEN
TRACE1 ( 'Calling HR_ASSIGNMENT_API.UPDATE_EMP_ASG for Employees...' );
apps.HR_ASSIGNMENT_API.UPDATE_EMP_ASG (
p_validate => false
,p_effective_date => v_effective_start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => v_assignment_id
,p_object_version_number => v_object_version_number
,p_supervisor_id => v_supervisor_id
,p_assignment_number => v_assignment_number
,p_change_reason => apps.hr_api.g_varchar2
,p_date_probation_end =>apps.hr_api.g_date
,p_probation_period => P_Probation_PERIOD
,p_probation_unit => P_PROBATION_UNIT
,p_default_code_comb_id => apps.hr_api.g_number
,p_frequency => apps.hr_api.g_varchar2
,p_internal_address_line => apps.hr_api.g_varchar2
,p_manager_flag => apps.hr_api.g_varchar2
,p_set_of_books_id => v_set_of_book_id
,p_title => apps.hr_api.g_varchar2
,p_ass_attribute_category => to_char(g_business_group_id) --Added by Amit Abhishek 18-Jun-10
,p_ass_attribute1 => v_role --Added by Amit Abhishek 18-Jun-10
,p_ass_attribute3 => to_char(v_fmgr_id) --Added by Amit Abhishek 18-Jun-10
,p_ass_attribute4 => v_fmgr_name --Added by Amit Abhishek 24-Jun-10
,p_ass_attribute5 => v_role_name --Added by Amit Abhishek 24-Jun-10
--,p_employee_category => 'LOCAL'
,p_concat_segments => apps.hr_api.g_varchar2
,p_concatenated_segments => v_concatenated_segments
,p_soft_coding_keyflex_id => V_Soft_Coding_id
,p_comment_id => v_comment_id
,p_effective_start_date => v_asg_eff_start_date
,p_effective_end_date => v_asg_eff_end_date
,p_no_managers_warning => v_no_managers_warning
,p_other_manager_warning => v_other_manager_warning
);
ELSE
apps.HR_ASSIGNMENT_API.UPDATE_EMP_ASG (
p_validate => false
,p_effective_date => l_sup_effective_start_date
,p_datetrack_update_mode => 'UPDATE'
,p_assignment_id => v_assignment_id
,p_object_version_number => v_object_version_number
,p_supervisor_id => v_supervisor_id
,p_assignment_number => v_assignment_number
,p_change_reason => apps.hr_api.g_varchar2
--,p_comments => assign_cur.comments
,p_date_probation_end => apps.hr_api.g_date
,p_probation_period => P_Probation_PERIOD
,p_probation_unit => P_PROBATION_UNIT
,p_default_code_comb_id => apps.hr_api.g_number
,p_frequency => apps.hr_api.g_varchar2
,p_internal_address_line => apps.hr_api.g_varchar2
,p_manager_flag => apps.hr_api.g_varchar2
,p_set_of_books_id => v_set_of_book_id
,p_title => apps. hr_api.g_varchar2
,p_ass_attribute_category => to_char(g_business_group_id) --Added by Amit Abhishek 18-Jun-10
,p_ass_attribute1 => v_role --Added by Amit Abhishek 18-Jun-10
,p_ass_attribute3 => to_char(v_fmgr_id) --Added by Amit Abhishek 18-Jun-10
,p_ass_attribute4 => v_fmgr_name --Added by Amit Abhishek 24-Jun-10
,p_ass_attribute5 => v_role_name --Added by Amit Abhishek 24-Jun-10
,p_concat_segments => apps.hr_api.g_varchar2
,p_concatenated_segments => v_concatenated_segments
,p_soft_coding_keyflex_id => V_Soft_Coding_id
,p_comment_id => v_comment_id
,p_effective_start_date => v_asg_eff_start_date
,p_effective_end_date => v_asg_eff_end_date
,p_no_managers_warning => v_no_managers_warning
,p_other_manager_warning => v_other_manager_warning
);
END IF;
-- Change Over 02-JUN-2010
EXCEPTION
WHEN OTHERS THEN
v_error_msg1 := SUBSTR(SQLERRM,1,200);
TRACE1 ( 'Error in UPDATE_EMP_ASG : ' || v_error_msg1 );
RAISE_APPLICATION_ERROR ( -20001, 'Error in UPDATE_EMP_ASG : ' || v_error_msg1 ); --Added by Amit Abhishek 02-Jun-2010
END;
--Insert History Record --Modified by Amit Abhishek 01-Jul-10
/* ELSIF v_org_id IS NOT NULL AND v_hist_flag = 'Y' THEN
apps.HR_ASSIGNMENT_API.UPDATE_EMP_ASG (
p_validate => false
,p_effective_date => v_effective_start_date
,p_datetrack_update_mode => 'UPDATE'
,p_assignment_id => v_assignment_id
,p_object_version_number => v_object_version_number
,p_supervisor_id => v_supervisor_id
,p_assignment_number => v_assignment_number
,p_change_reason => apps.hr_api.g_varchar2
--,p_comments => assign_cur.comments
,p_date_probation_end => apps.hr_api.g_date
,p_probation_period => P_Probation_PERIOD
,p_probation_unit => P_PROBATION_UNIT
,p_default_code_comb_id => apps.hr_api.g_number
,p_frequency => apps.hr_api.g_varchar2
,p_internal_address_line => apps.hr_api.g_varchar2
,p_manager_flag => apps.hr_api.g_varchar2
,p_set_of_books_id => v_set_of_book_id
,p_title => apps. hr_api.g_varchar2
,p_ass_attribute_category => to_char(g_business_group_id) --Added by Amit Abhishek 18-Jun-10
,p_ass_attribute1 => v_role --Added by Amit Abhishek 18-Jun-10
,p_ass_attribute3 => to_char(v_fmgr_id) --Added by Amit Abhishek 18-Jun-10
,p_ass_attribute4 => v_fmgr_name --Added by Amit Abhishek 24-Jun-10
,p_ass_attribute5 => v_role_name --Added by Amit Abhishek 24-Jun-10
,p_concat_segments => apps.hr_api.g_varchar2
,p_concatenated_segments => v_concatenated_segments
,p_soft_coding_keyflex_id => V_Soft_Coding_id
,p_comment_id => v_comment_id
,p_effective_start_date => v_asg_eff_start_date
,p_effective_end_date => v_asg_eff_end_date
,p_no_managers_warning => v_no_managers_warning
,p_other_manager_warning => v_other_manager_warning
);
--Change Over 01-Jul-10
END IF;*/
TRACE1 ( 'After processing all in UPDATE_ASSIGNMENT....' );
COMMIT;
v_object_version_number := NULL;
v_effective_start_date := NULL;
BEGIN
SELECT object_version_number, effective_start_date
INTO v_object_version_number, v_effective_start_date
FROM per_all_assignments_f
WHERE assignment_id = v_assignment_id
AND assignment_status_type_id =1
AND assignment_type ='E'
AND primary_flag ='Y'
AND SYSDATE BETWEEN effective_start_date AND effective_end_date;
EXCEPTION
WHEN OTHERS THEN
v_error_msg1 := SUBSTR(SQLERRM,1,200) ;
TRACE1 ( 'Error in UPDATE_EMP_ASG_CRITERIA : ' || v_error_msg1 );
RAISE_APPLICATION_ERROR ( -20001, 'Error in UPDATE_EMP_ASG_CRITERIA : ' || v_error_msg1 );
END;
--IF v_org_id IS NOT NULL AND v_hist_flag = 'N' THEN --Modified by Amit Abhishek 01-Jul-10
TRACE1 ( 'Calling HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA for Employee..' );
-- TRACE1('OBJECT VERSION NUMBER - '||p_object_version_number);
-- v_object_version_number := v_object_version_number +1;
TRACE1('v_object_version_number - ' || v_object_version_number );
TRACE1(' v_job_id ' || v_job_id );
/* ASSIGNEMNT API START*/
BEGIN
IF NVL(l_payroll_start_date,v_effective_start_date-1 )<=v_effective_start_date THEN
apps.HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA (
p_effective_date => v_effective_start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => v_assignment_id
,p_validate => false
,p_called_from_mass_update => false
--,p_position_id => v_position_id
--,p_job_id => v_job_id
,p_payroll_id => NVL(l_payroll_id,apps.hr_api.g_number)
--,p_grade_id => v_grade_id
--,p_location_id => v_location_id
--,p_organization_id => V_ORG_ID
,p_pay_basis_id => apps.hr_api.g_number
,p_employment_category => P_Employement_Category
,p_concat_segments => apps.hr_api.g_varchar2
,p_people_group_id => v_people_group_id
,p_object_version_number => v_object_version_number
,p_special_ceiling_step_id => v_special_ceiling_step_id
,p_group_name => v_group_name
,p_effective_start_date => v_asg_eff_start_date
,p_effective_end_date => v_asg_eff_end_date
,p_org_now_no_manager_warning => v_org_now_no_manager_warning
,p_other_manager_warning => v_other_manager_warning
,p_spp_delete_warning => v_spp_delete_warning
,p_entries_changed_warning => v_entries_changed_warning
,p_tax_district_changed_warning => v_tax_district_changed_warning
);
ELSE
apps. HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA (
p_effective_date => l_payroll_start_date
,p_datetrack_update_mode => 'UPDATE'
,p_assignment_id => v_assignment_id
,p_validate => false
,p_called_from_mass_update => false
--,p_grade_id => v_grade_id
--,p_position_id => v_position_id
--,p_job_id => v_job_id
,p_payroll_id => l_payroll_id
--,p_location_id => v_location_id
--,p_organization_id => V_ORG_ID
,p_pay_basis_id => apps.hr_api.g_number
,p_employment_category => P_Employement_Category
,p_concat_segments => apps.hr_api.g_varchar2
,p_people_group_id => v_people_group_id
,p_object_version_number => v_object_version_number
,p_special_ceiling_step_id => v_special_ceiling_step_id
,p_group_name => v_group_name
,p_effective_start_date => v_asg_eff_start_date
,p_effective_end_date => v_asg_eff_end_date
,p_org_now_no_manager_warning => v_org_now_no_manager_warning
,p_other_manager_warning => v_other_manager_warning
,p_spp_delete_warning => v_spp_delete_warning
,p_entries_changed_warning => v_entries_changed_warning
,p_tax_district_changed_warning => v_tax_district_changed_warning
);
END IF;
EXCEPTION
WHEN OTHERS THEN
v_error_msg1 := SUBSTR(SQLERRM,1,200) ;
TRACE1 ( 'Error in UPDATE_EMP_ASG_CRITERIA : ' || v_error_msg1 );
-- p_update_emp ( p_process_flag => 'E' , p_errm => v_error_msg1, p_empno => V_EMP_NUMBER );
RAISE_APPLICATION_ERROR ( -20001, 'Error in UPDATE_EMP_ASG_CRITERIA : ' || v_error_msg1 );
END;
----Modified by Amit Abhishek 01-Jul-10 Insert history data
/* ELSIF v_org_id IS NOT NULL AND v_hist_flag = 'Y' THEN
apps. HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA (
p_effective_date => v_effective_start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => v_assignment_id
,p_validate => false
,p_called_from_mass_update => false
,p_grade_id => v_grade_id
,p_position_id => v_position_id
,p_job_id => v_job_id
,p_payroll_id => l_payroll_id
,p_location_id => v_location_id
,p_organization_id => V_ORG_ID
,p_pay_basis_id => apps.hr_api.g_number
,p_employment_category => P_Employement_Category
,p_concat_segments => apps.hr_api.g_varchar2
,p_people_group_id => v_people_group_id
,p_object_version_number => v_object_version_number
,p_special_ceiling_step_id => v_special_ceiling_step_id
,p_group_name => v_group_name
,p_effective_start_date => v_asg_eff_start_date
,p_effective_end_date => v_asg_eff_end_date
,p_org_now_no_manager_warning => v_org_now_no_manager_warning
,p_other_manager_warning => v_other_manager_warning
,p_spp_delete_warning => v_spp_delete_warning
,p_entries_changed_warning => v_entries_changed_warning
,p_tax_district_changed_warning => v_tax_district_changed_warning
);
-- Change over 01-Jul-10
END IF;*/
END IF;
COMMIT;
END UPDATE_ASSIGNMENT;
/* ASSIGNMENT API END*/
-----------------------------------------------------------------------END UPDATE ASSIGNMENT-------------------------------------------------------
-----------------------------------------------------------------------CREATE ADDRESS--------------------------------------------------------------
/* ADRESS CREATION STARTED*/
PROCEDURE CREATE_ADDRESS
IS
v_address_id PER_ADDRESSES.ADDRESS_ID%TYPE;
v_object_version_number NUMBER;
v_emp_number VARCHAR2(240);
v_person_id apps.PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
--g_business_group_id NUMBER := FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');
v_party_id apps.PER_ALL_PEOPLE_F.PARTY_ID%TYPE;
v_primary_flag VARCHAR2(1);
v_row_count NUMBER := 0;
v_err_count NUMBER := 0;
v_commit NUMBER := 0;
v_country VARCHAR2(60);
v_date_from DATE;
v_add_style VARCHAR2(30);
v_count NUMBER := 0;
v_count1 NUMBER;
v_count2 NUMBER;
v_add_type VARCHAR2(10);
v_add_information14 VARCHAR2(60);
v_town_or_city VARCHAR2(60);
v_add_information15 VARCHAR2(60);
v_effective_date DATE;
BEGIN
FOR c_rec IN
( SELECT addr.rowid row_id, addr.*
FROM XXPLG_ADDRESS_TMP addr
WHERE NVL(status,'N') in ('N','E')
)
LOOP
v_row_count := v_row_count +1;
v_person_id := NULL;
v_address_id := NULL;
v_date_from := NULL;
v_error_msg1 := NULL;
v_party_id := NULL;
v_add_style := NULL;
v_country := NULL;
v_object_version_number := NULL;
v_add_information14 := NULL;
v_add_information15 := NULL;
v_town_or_city := NULL;
v_count :=0;
v_count1 :=0;
v_count2 :=0;
v_primary_flag := c_rec.primary_flag;
v_add_type :=NULL;
v_effective_date :=NULL;
BEGIN
SELECT person_id, party_id, original_date_of_hire
INTO v_person_id, v_party_id, v_date_from
FROM apps.PER_ALL_PEOPLE_F
WHERE attribute1 = c_rec.employee_number
AND business_group_id = g_business_group_id
AND SYSDATE BETWEEN effective_start_date and effective_end_date;
EXCEPTION
WHEN OTHERS THEN
v_error_msg1 := ' Employee Not Found.';
v_err_count := v_err_count +1;
UPDATE XXPLG_ADDRESS_TMP
SET ERROR_MSG = v_error_msg1,
STATUS = 'E',
CREATION_DATE = TRUNC(SYSDATE)
WHERE rowid = c_rec.row_id;
END;
v_effective_date :=v_date_from;
IF c_rec.effective_date IS NOT NULL
THEN
v_date_from :=c_rec.effective_date;
END IF;
IF v_error_msg1 IS NULL
THEN
BEGIN
--Validating address style
BEGIN
SELECT DESCRIPTIVE_FLEX_CONTEXT_CODE
INTO v_add_style
FROM apps.FND_DESCR_FLEX_CONTEXTS_VL
WHERE APPLICATION_ID = g_v_appl_id
AND DESCRIPTIVE_FLEXFIELD_NAME ='Address Structure'
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = upper(NVL(c_rec.address_style,'GENERIC'));
EXCEPTION WHEN OTHERS
THEN
NULL;
END;
--If addr style not found consider generic address style
IF v_add_style IS NULL
THEN
SELECT DESCRIPTIVE_FLEX_CONTEXT_CODE
INTO v_add_style
FROM apps.FND_DESCR_FLEX_CONTEXTS_VL
WHERE APPLICATION_ID = g_v_appl_id
AND DESCRIPTIVE_FLEXFIELD_NAME ='Address Structure'
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = 'GENERIC';
END IF;
--Check for duplicate record
SELECT count(*)
INTO v_count
FROM apps.PER_ADDRESSES
WHERE person_id = v_person_id
AND upper(address_line1) = upper(c_rec.address_line1)
AND NVL(postal_code,'XX') = NVL(c_rec.POSTAL_CODE,'XX')
AND upper(style) = v_add_style
AND business_group_id = g_business_group_id;
--If it is a new address then enter
IF v_count = 0 THEN
--Check if primary address exists for the employee
IF v_primary_flag IS NULL
THEN
v_primary_flag := 'Y';
SELECT count(*)
INTO v_count1
FROM apps.PER_ADDRESSES
WHERE person_id = v_person_id
AND primary_flag = 'Y'
--AND upper(style) = v_add_style
AND business_group_id = g_business_group_id;
--If primary address exists then set the primary flag and date_from fields
IF v_count1 > 0 THEN
/* BEGIN
SELECT date_from
INTO v_date_from
FROM apps.PER_ADDRESSES
WHERE person_id = v_person_id
AND primary_flag = 'Y'
AND business_group_id = g_business_group_id;
EXCEPTION WHEN OTHERS
THEN
v_date_from := TRUNC(SYSDATE);
END;
IF v_date_from < c_rec.effective_date THEN
v_date_from := c_rec.effective_date;
END IF;*/
v_primary_flag := 'N';
END IF;
END IF;
IF v_add_style <> 'GENERIC' THEN
v_add_information14 := c_rec.ADDRESS_INFORMATION14;
--Validate Country
BEGIN
SELECT TERRITORY_CODE
INTO v_country
FROM apps.FND_TERRITORIES_VL
WHERE UPPER(territory_short_name) = UPPER(c_rec.country);
EXCEPTION WHEN OTHERS
THEN
v_error_msg1 := ' Invalid Country.';
END;
--Validate States
IF v_add_style = 'IN' THEN
BEGIN
SELECT LOOKUP_CODE
INTO v_add_information15
FROM apps.HR_LOOKUPS
WHERE UPPER(meaning) = UPPER(c_rec.ADDRESS_INFORMATION15)
AND lookup_type = 'IN_STATES'
AND enabled_flag = 'Y'
AND NVL(end_date_active,SYSDATE+1)>SYSDATE;
EXCEPTION WHEN OTHERS
THEN
v_add_information15 := NULL;
v_error_msg1 := v_error_msg1||' Invalid State.';
END;
END IF;
ELSE
v_country := c_rec.country;
v_town_or_city := c_rec.ADDRESS_INFORMATION14;
END IF;
--Validate address type
IF c_rec.ADDR_TYPE IS NOT NULL
THEN
BEGIN
SELECT LOOKUP_CODE
INTO v_add_type
FROM apps.hr_lookups
WHERE lookup_type = 'ADDRESS_TYPE'
AND UPPER(meaning) = UPPER(c_rec.ADDR_TYPE)
AND enabled_flag = 'Y'
AND NVL(end_date_active,SYSDATE+1)>SYSDATE;
EXCEPTION WHEN OTHERS
THEN
v_error_msg1 := v_error_msg1||' Invalid Address Type.';
END;
ELSE
--If address type is not available then by default set the address type as permanent address
BEGIN
v_add_type := 'IN_P';
--Check if permanent address is already entered for employee.
SELECT count(*)
INTO v_count2
FROM apps.PER_ADDRESSES
WHERE person_id = v_person_id
--AND primary_flag = 'Y'
AND business_group_id = g_business_group_id;
--If permanent address already entered then set the address type as current address
IF v_count2 > 0 THEN
v_add_type := 'IN_C';
END IF;
EXCEPTION WHEN OTHERS
THEN
NULL;
END;
END IF;
--IF v_error_msg1 IS NULL
--THEN
BEGIN
TRACE1('BEFORE CRE_OR_UPD_PERSON_ADDRESS - '||c_rec.effective_date);
TRACE1('Address Style - '||v_add_style);
--TRACE1('Effective Date '||v_effective_date);
TRACE1('TOWN/CITY - '||c_rec.ADDRESS_INFORMATION14);
-- ADDRESS API START
apps.HR_PERSON_ADDRESS_API.CRE_OR_UPD_PERSON_ADDRESS(
p_update_mode => 'CORRECTION'
,p_validate => false
,p_address_id => v_address_id
,p_object_version_number => v_object_version_number
,p_effective_date => v_date_from
,p_pradd_ovlapval_override => false
--,p_validate_county => false
,p_person_id => v_person_id
,p_business_group_id => g_business_group_id
,p_primary_flag => v_primary_flag
,p_style => v_add_style
,p_date_from => v_date_from
,p_date_to => c_rec.date_to
,p_address_type => v_add_type
-- ,p_comments => r_address_rec.comments
,p_telephone_number_1 => c_rec.TELEPHONE_NUMBER_1 --Added by Amit Abhishek 18-Jun-10
,p_telephone_number_2 => c_rec.TELEPHONE_NUMBER_2 --Added by Amit Abhishek 18-Jun-10
,p_telephone_number_3 => c_rec.TELEPHONE_NUMBER_3 --Added by Amit Abhishek 18-Jun-10
,p_address_line1 => INITCAP(c_rec.ADDRESS_LINE1)
,p_address_line2 => INITCAP(c_rec.ADDRESS_LINE2)
,p_address_line3 => INITCAP(c_rec.ADDRESS_LINE3)
,p_add_information14 => INITCAP(v_add_information14)
,p_add_information15 => v_add_information15 --Added by Amit Abhishek 18-Jun-10
,p_town_or_city => v_town_or_city
--,p_region_1 => P_state
,p_postal_code => c_rec.POSTAL_CODE
,p_country => v_country
,p_party_id => v_party_id
);
TRACE1('ADDRESS CREATED'||V_ADDRESS_ID);
UPDATE XXPLG_ADDRESS_TMP
SET ERROR_MSG = v_error_msg1,
STATUS = 'S',
CREATION_DATE = TRUNC(SYSDATE)
WHERE rowid = c_rec.row_id;
EXCEPTION WHEN OTHERS
THEN
v_error_msg1 := SUBSTR(SQLERRM,1,200);
v_err_count := v_err_count +1;
UPDATE XXPLG_ADDRESS_TMP
SET ERROR_MSG = v_error_msg1,
STATUS = 'E',
CREATION_DATE = TRUNC(SYSDATE)
WHERE rowid = c_rec.row_id;
END;
/*ELSE
v_err_count := v_err_count +1;
UPDATE XXPLG_ADDRESS_TMP
SET ERROR_MSG = v_error_msg1,
STATUS = 'E',
CREATION_DATE = TRUNC(SYSDATE)
WHERE rowid = c_rec.row_id;*/
--END IF;
ELSE
v_error_msg1 := ' Address already exist.';
v_err_count := v_err_count +1;
UPDATE XXPLG_ADDRESS_TMP
SET ERROR_MSG = v_error_msg1,
STATUS = 'E',
CREATION_DATE = TRUNC(SYSDATE)
WHERE rowid = c_rec.row_id;
END IF;
END;
END IF;
IF MOD(v_row_count,20)=0 THEN
COMMIT;
END IF;
END LOOP;
apps.FND_FILE.PUT_LINE(apps.FND_FILE.OUTPUT,'************************************************************');
apps.FND_FILE.PUT_LINE(apps.FND_FILE.OUTPUT, 'Total Records to be loaded: '||v_row_count);
apps.FND_FILE.PUT_LINE(apps.FND_FILE.OUTPUT, 'Total Records errored out: '||v_err_count);
apps.FND_FILE.PUT_LINE(apps.FND_FILE.OUTPUT,'************************************************************');
END CREATE_ADDRESS;
---------------------------------------------------------------------END CREATE ADDRESS----------------------------------------------------------
---------------------------------------------------------------------CREATE PHONE----------------------------------------------------------------
-- Phone Creation Start
--Added by Amit Abhishek 14-Jun-10
PROCEDURE CREATE_PHONE
IS
v_phone_id PER_PHONES.PHONE_ID%TYPE;
v_object_version_number NUMBER;
v_party_id apps.PER_ALL_PEOPLE_F.PARTY_ID%TYPE;
v_person_id apps.PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
--v_emp_number VARCHAR2(30);
v_phone_type VARCHAR(2);
v_err_count NUMBER := 0;
v_row_count NUMBER := 0;
v_commit NUMBER := 0;
v_count NUMBER;
v_hire_date DATE;
BEGIN
FOR c_rec IN
(SELECT PH.ROWID ROW_ID, PH.*
FROM XXPLG_PHONE_TMP PH
WHERE NVL(status, 'N') in ('N','E')
)
LOOP
v_row_count :=v_row_count + 1;
v_error_msg1 := NULL;
v_party_id := NULL;
v_person_id := NULL;
v_phone_id := NULL;
v_hire_date:=NULL;
v_object_version_number := NULL;
--v_emp_number := c_rec.employee_number;
BEGIN
SELECT party_id,
person_id,
original_date_of_hire
INTO v_party_id, v_person_id, v_hire_date
FROM apps.PER_ALL_PEOPLE_F
WHERE attribute1 = c_rec.employee_number
AND business_group_id = g_business_group_id
AND SYSDATE BETWEEN effective_start_date and effective_end_date;
EXCEPTION
WHEN OTHERS THEN
TRACE1('EMPLOYEE NOT FOUND');
v_error_msg1 := ' Employee Not Found';
END;
BEGIN
--Added by AA 16-Jun-10
v_phone_type := NULL;
IF c_rec.phone_type IS NULL
THEN
IF LENGTH(c_rec.PHONE_NUMBER)=10 THEN
v_count := 0;
v_phone_type := 'M';
--Check for duplicate phone numbers
SELECT COUNT(*)
INTO v_count
FROM apps.PER_PHONES pp, apps.PER_ALL_PEOPLE_F ppf
WHERE pp.parent_table = 'PER_ALL_PEOPLE_F'
AND pp.phone_type = v_phone_type
--AND pp.phone_number = c_rec.phone_number
AND pp.parent_id = ppf.person_id
AND ppf.business_group_id = g_business_group_id
AND ppf.person_id = v_person_id
AND SYSDATE BETWEEN ppf.effective_start_date and ppf.effective_end_date;
IF v_count>0 THEN
v_phone_type := 'H1';
v_count :=0;
--Check for duplicate phone numbers
SELECT COUNT(*)
INTO v_count
FROM apps.PER_PHONES pp, apps.PER_ALL_PEOPLE_F ppf
WHERE pp.parent_table = 'PER_ALL_PEOPLE_F'
AND pp.phone_type = v_phone_type
--AND pp.phone_number = c_rec.phone_number
AND pp.parent_id = ppf.person_id
AND ppf.business_group_id = g_business_group_id
AND ppf.person_id = v_person_id
AND SYSDATE BETWEEN ppf.effective_start_date and ppf.effective_end_date;
IF v_count>0 THEN
v_phone_type := 'W1';
v_count :=0;
--Check for duplicate phone numbers
SELECT COUNT(*)
INTO v_count
FROM apps.PER_PHONES pp, apps.PER_ALL_PEOPLE_F ppf
WHERE pp.parent_table = 'PER_ALL_PEOPLE_F'
AND pp.phone_type = v_phone_type
--AND pp.phone_number = c_rec.phone_number
AND pp.parent_id = ppf.person_id
AND ppf.business_group_id = g_business_group_id
AND ppf.person_id = v_person_id
AND SYSDATE BETWEEN ppf.effective_start_date and ppf.effective_end_date;
IF v_count>0 THEN
v_phone_type := 'O';
END IF;
END IF;
END IF;
ELSE
v_phone_type := 'H1';
v_count :=0;
--Check for duplicate phone numbers
SELECT COUNT(*)
INTO v_count
FROM apps.PER_PHONES pp, apps.PER_ALL_PEOPLE_F ppf
WHERE pp.parent_table = 'PER_ALL_PEOPLE_F'
AND pp.phone_type = v_phone_type
--AND pp.phone_number = c_rec.phone_number
AND pp.parent_id = ppf.person_id
AND ppf.business_group_id = g_business_group_id
AND ppf.person_id = v_person_id
AND SYSDATE BETWEEN ppf.effective_start_date and ppf.effective_end_date;
IF v_count>0 THEN
v_phone_type := 'W1';
v_count :=0;
--Check for duplicate phone numbers
SELECT COUNT(*)
INTO v_count
FROM apps.PER_PHONES pp, apps.PER_ALL_PEOPLE_F ppf
WHERE pp.parent_table = 'PER_ALL_PEOPLE_F'
AND pp.phone_type = v_phone_type
--AND pp.phone_number = c_rec.phone_number
AND pp.parent_id = ppf.person_id
AND ppf.business_group_id = g_business_group_id
AND ppf.person_id = v_person_id
AND SYSDATE BETWEEN ppf.effective_start_date and ppf.effective_end_date;
IF v_count>0 THEN
v_phone_type := 'O';
END IF;
END IF;
END IF;
ELSE
SELECT lookup_code
INTO v_phone_type
FROM apps.hr_lookups
WHERE lookup_type = 'PHONE_TYPE'
AND UPPER(meaning) = UPPER(c_rec.phone_type)
AND enabled_flag = 'Y'
AND NVL(end_date_active,SYSDATE+1)>SYSDATE;
END IF;
--Change over 16-Jun-10
EXCEPTION WHEN OTHERS
THEN
v_error_msg1 := v_error_msg1||' Phone Type Not Found';
END;
IF v_error_msg1 IS NOT NULL THEN
v_err_count := v_err_count + 1;
UPDATE XXPLG_PHONE_TMP
SET STATUS = 'E',
ERROR_MESSAGE = v_error_msg1,
CREATION_DATE = SYSDATE
WHERE ROWID = c_rec.row_id;
END IF;
BEGIN
v_count := 0;
--Check for duplicate phone numbers
SELECT COUNT(*)
INTO v_count
FROM apps.PER_PHONES pp, apps.PER_ALL_PEOPLE_F ppf
WHERE pp.parent_table = 'PER_ALL_PEOPLE_F'
AND pp.phone_type = v_phone_type
AND pp.phone_number = c_rec.phone_number
AND pp.parent_id = ppf.person_id
AND ppf.business_group_id = g_business_group_id
AND ppf.person_id = v_person_id
AND SYSDATE BETWEEN ppf.effective_start_date and ppf.effective_end_date;
IF v_count = 0 AND v_error_msg1 IS NULL THEN
TRACE1('CREATE OR UPDATE PHONE ');
-- PHONE API START
apps.HR_PHONE_API.CREATE_OR_UPDATE_PHONE (
p_phone_id => v_phone_id
, p_object_version_number => v_object_version_number
, p_date_from => v_hire_date
--, p_date_to => apps.hr_api.g_date
, p_phone_type => v_phone_type
, p_phone_number => c_rec.phone_number
, p_parent_id => v_person_id
, p_parent_table => 'PER_ALL_PEOPLE_F'
, p_validate => false
, p_effective_date => v_hire_date
, p_party_id => v_party_id
--, p_validity => apps.hr_api.g_varchar2
);
TRACE1('PHONE CREATION SUCCEDED' || v_phone_id);
UPDATE XXPLG_PHONE_TMP
SET STATUS = 'S',
ERROR_MESSAGE = null,
CREATION_DATE = SYSDATE
WHERE ROWID = c_rec.row_id;
ELSIF v_error_msg1 IS NULL
THEN
v_error_msg1 := 'Phone number already exists';
v_err_count := v_err_count + 1;
UPDATE XXPLG_PHONE_TMP
SET STATUS = 'E',
ERROR_MESSAGE = v_error_msg1,
CREATION_DATE = SYSDATE
WHERE ROWID = c_rec.row_id;
END IF;
EXCEPTION
WHEN OTHERS THEN
v_err_count := v_err_count + 1;
v_error_msg1 := SUBSTR(SQLERRM,1,200);
TRACE1('PHONE CREATION FAILED'||v_error_msg1);
UPDATE XXPLG_PHONE_TMP
SET STATUS = 'E',
ERROR_MESSAGE = v_error_msg1,
CREATION_DATE = SYSDATE
WHERE ROWID = c_rec.row_id;
END;
IF MOD(v_row_count,20)=0 THEN
COMMIT;
END IF;
END LOOP;
apps.FND_FILE.PUT_LINE(apps.FND_FILE.OUTPUT,'************************************************************');
apps.FND_FILE.PUT_LINE(apps.FND_FILE.OUTPUT, 'Total Records to be loaded: '||v_row_count);
apps.FND_FILE.PUT_LINE(apps.FND_FILE.OUTPUT, 'Total Records errored out: '||v_err_count);
apps.FND_FILE.PUT_LINE(apps.FND_FILE.OUTPUT,'************************************************************');
END CREATE_PHONE;
------------------------------------------------------END CREATE PHONE------------------------------------------------------------
FUNCTION val_address_style(
p_style IN VARCHAR2 )
RETURN VARCHAR2
IS
v_dummy VARCHAR2(1000);
BEGIN
SELECT DESCRIPTIVE_FLEX_CONTEXT_CODE
INTO v_dummy
FROM apps.FND_DESCR_FLEX_CONTEXTS_VL
WHERE APPLICATION_ID = g_v_appl_id
AND DESCRIPTIVE_FLEXFIELD_NAME ='Address Structure'
AND upper(DESCRIPTIVE_FLEX_CONTEXT_CODE) = upper(NVL(p_style,'GENERIC'));
RETURN v_dummy;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR ( -20001 , 'Invalid Address Style - ' || p_style );
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR ( -20001 , 'U:Error in val_address_style:' || SQLERRM );
END val_address_style;
--------------------------------------------------------------------------------------
PROCEDURE TRACE1(
P_MESSAGE VARCHAR2 )
IS
BEGIN
IF G_DEBUG = 'Y' THEN
apps.FND_FILE.PUT_LINE(apps.FND_FILE.LOG, 'Debug : ' || P_MESSAGE );
END IF;
END TRACE1;
PROCEDURE P_LOG(
P_MESSAGE VARCHAR2)
IS
BEGIN
apps.FND_FILE.PUT_LINE(apps.FND_FILE.LOG, P_MESSAGE);
END;
PROCEDURE P_OUT(
P_MESSAGE VARCHAR2)
IS
BEGIN
apps.FND_FILE.PUT_LINE(apps.FND_FILE.OUTPUT, P_MESSAGE);
END ;
-------------------------------Genrate Error Report-------------------------------------
PROCEDURE PRINT_ERROR
AS
CURSOR cur_error_report
IS
SELECT EMPLOYEE_NUMBER,
ERROR_MESSAGE
FROM XXPLL_HR_CNV_PEOPLE_TMP
WHERE process_flag='E'
AND REQUEST_ID = apps.FND_GLOBAL.CONC_REQUEST_ID;
BEGIN
FOR cur_report_rec IN cur_error_report
LOOP
IF cur_error_report%ROWCOUNT = 1 THEN
P_OUT ( '----------------------------------------------------------------------------');
P_OUT (RPAD('Employee_Number',30) || 'Error Message' );
gv_retcode := 1;
END IF;
P_OUT(RPAD(cur_report_rec.employee_number, 30) || cur_report_rec.error_message );
END LOOP;
P_OUT ( '----------------------------------------------------------------------------');
END PRINT_ERROR;
PROCEDURE p_update_errm(
p_process_flag VARCHAR2 ,
p_errm VARCHAR2,
p_rowid ROWID )
IS
BEGIN
UPDATE XXPLL_HR_CNV_PEOPLE_TMP
SET process_flag = p_process_flag,
ERROR_MESSAGE = p_errm,
last_update_date = SYSDATE,
last_UPDATED_BY = apps.FND_GLOBAL.USER_ID,
request_id = apps.FND_GLOBAL.CONC_REQUEST_ID,
last_update_login = apps.FND_GLOBAL.LOGIN_ID
WHERE ROWID = p_rowid;
END p_update_errm;
--Added by Amit Abhishek 01-Jul-2010
PROCEDURE p_update_asg_errm(
p_process_flag VARCHAR2 ,
p_errm VARCHAR2,
p_empnum VARCHAR2,
p_rowid ROWID )
IS
BEGIN
UPDATE XXPLL_HR_CNV_ASSIGN_TMP
SET process_flag = p_process_flag,
ERROR_MESSAGE = p_errm,
last_update_date = SYSDATE,
last_UPDATED_BY = apps.FND_GLOBAL.USER_ID,
request_id = apps.FND_GLOBAL.CONC_REQUEST_ID,
last_update_login = apps.FND_GLOBAL.LOGIN_ID,
new_employee_number = p_empnum
WHERE ROWID = p_rowid;
END p_update_asg_errm;
--Change Over 01-Jul-2010
PROCEDURE p_update_emp(
p_process_flag VARCHAR2 ,
p_errm VARCHAR2,
p_rowid ROWID )
IS
BEGIN
UPDATE XXPLL_HR_CNV_PEOPLE_TMP
SET process_flag = p_process_flag,
ERROR_MESSAGE = p_errm,
last_update_date = SYSDATE,
last_UPDATED_BY = apps.FND_GLOBAL.USER_ID,
request_id = apps.FND_GLOBAL.CONC_REQUEST_ID,
last_update_login = apps.FND_GLOBAL.LOGIN_ID
WHERE ROWID = p_rowid;
END p_update_emp;
PROCEDURE p_check_emp_exist(
p_emp_number IN VARCHAR2 ,
p_omitted IN OUT VARCHAR2 )
IS
BEGIN
SELECT 1
INTO p_omitted
FROM apps.PER_ALL_PEOPLE_F
WHERE attribute1 = p_emp_number
AND ROWNUM < 2;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_omitted := 0;
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR ( -20001, 'U:Error while checking Employee Number in Assignment.' );
END p_check_emp_exist;
PROCEDURE p_check_cwk_exist(
p_npw_number IN VARCHAR2 ,
p_omitted IN OUT VARCHAR2 )
IS
BEGIN
SELECT 1
INTO p_omitted
FROM apps.PER_ALL_PEOPLE_F
WHERE NPW_NUMBER = p_npw_number
AND ROWNUM < 2;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_omitted := 0;
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR ( -20001, 'U:Error while checking Employee Number in Assignment.' );
END p_check_cwk_exist;
--Added by Amit Abhishek 14-Jun-10
PROCEDURE CREATE_QUALIFICATION
IS
l_error_msg VARCHAR2(200);
l_business_group_id NUMBER := FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');
l_person_id NUMBER;
l_qualification_type_id NUMBER;
l_date DATE;
l_object_version_number NUMBER(30);
l_qualification_id NUMBER;
l_total_records NUMBER := 0;
l_success_records NUMBER := 0;
l_failure_records NUMBER := 0;
l_specialization VARCHAR2(150); --ADDED BY AMIT ABHISHEK 24-JUN-10
l_count NUMBER;
v_ovn NUMBER;
v_att_id NUMBER;
l_establishment VARCHAR2(240);
l_estab_id NUMBER;
l_status VARCHAR2(10);
BEGIN
FOR c_rec IN
(
SELECT Q.ROWID row_id,Q.*
FROM XXPLG_QUALIFICATIONS_TMP Q
WHERE NVL(status,'N') IN ('N','E')
)
LOOP
l_error_msg := NULL;
l_total_records := l_total_records + 1;
l_person_id := NULL;
l_qualification_type_id := NULL;
l_qualification_id := NULL;
l_object_version_number :=NULL;
l_specialization :=NULL; --ADDED BY AMIT ABHISHEK 24-JUN-10
l_count := 0;
v_ovn := NULL;
v_att_id :=NULL;
l_status :=NULL;
l_establishment :=NULL;
l_estab_id := NULL;
--Get Person ID
BEGIN
SELECT person_id
INTO l_person_id
FROM apps.PER_ALL_PEOPLE_F
WHERE business_group_id = l_business_group_id
AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date
AND attribute1 = c_rec.employee_number;
EXCEPTION
WHEN OTHERS THEN
l_error_msg := 'Employee does not exist.';
END;
--Get Qualification Type ID
BEGIN
SELECT qualification_type_id
INTO l_qualification_type_id
FROM apps.PER_QUALIFICATION_TYPES
WHERE upper(name) = upper(c_rec.qual_type);
EXCEPTION WHEN OTHERS
THEN
l_error_msg := l_error_msg || ' Invalid Qualification Type. ';
END;
--Change Over 24-Jun-10
IF l_error_msg IS NOT NULL THEN
l_failure_records := l_failure_records +1;
UPDATE XXPLG_QUALIFICATIONS_TMP
SET STATUS = 'E',
CREATION_DATE = SYSDATE,
ERROR_MESSAGE = l_error_msg
WHERE ROWID = c_rec.row_id;
ELSE
--Validate Specialization Added by Amit Abhishek 24-Jun-10
BEGIN
SELECT flex_value
INTO l_specialization
FROM apps.FND_FLEX_VALUE_SETS ffvs,
apps.fnd_flex_values ffv
WHERE ffvs.flex_value_set_id = ffv.flex_value_set_id
AND ffvs.FLEX_VALUE_SET_NAME = 'XX_SPECIALIZATION'
AND upper(flex_value)=upper(c_rec.qualification_title)
AND ffv.enabled_flag = 'Y'
AND NVL(ffv.end_date_active,SYSDATE+1)>SYSDATE;
EXCEPTION WHEN OTHERS
THEN
l_error_msg := l_error_msg || ' Invalid Specialization. ';
END;
/*BEGIN
SELECT lookup_code
INTO l_status
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE = 'PER_SUBJECT_STATUSES'
AND UPPER(MEANING)= UPPER(c_rec.)
END;*/
--Validate Establishment
--Added by Amit Abhishek 10-Aug-10
BEGIN
SELECT name, establishment_id
INTO l_establishment, l_estab_id
FROM per_establishments
WHERE upper(NAME) = upper(c_rec.establishment);
EXCEPTION WHEN OTHERS
THEN
NULL;
END;
IF l_establishment IS NULL THEN
BEGIN
SELECT name, establishment_id
INTO l_establishment, l_estab_id
FROM per_establishments
WHERE upper(NAME) = 'OTHERS';
EXCEPTION WHEN OTHERS
THEN
NULL;
END;
END IF;
--Change Over 10-Aug-10
BEGIN
SELECT start_date
INTO l_date
FROM apps.PER_ALL_PEOPLE_F
WHERE person_id = l_person_id
AND business_group_id = l_business_group_id
AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
--Check for duplicates
SELECT count(*)
INTO l_count
FROM apps.per_qualifications_v
WHERE business_group_id = l_business_group_id
AND UPPER(NVL(attribute1,'XX')) = UPPER(NVL(c_rec.qualification_title,'XX'))
AND UPPER(name) = UPPER(c_rec.qual_type)
AND NVL(establishment,'XX')=NVL(l_establishment,'XX')
AND person_id = l_person_id;
IF l_count = 0 THEN
--Added by Amit Abhishek 30-Nov-10
SELECT COUNT(*)
INTO l_count
FROM per_establishment_attendances
WHERE person_id = l_person_id
AND establishment = l_establishment;
--Change Over 30-Nov-10
IF l_count = 0 THEN
--Added by Amit Abhishek 10-Aug-10
PER_ESTAB_ATTENDANCES_API.CREATE_ATTENDED_ESTAB
( p_validate => false
,p_effective_date => SYSDATE
,p_fulltime =>'Y'
--,p_attended_end_date => '01-Jan-2005'
,p_establishment => l_establishment
--,p_establishment_id => l_estab_id
,p_business_group_id =>l_business_group_id
,p_person_id => l_person_id
,p_attendance_id => v_att_id
,p_object_version_number => v_ovn
);
ELSE
SELECT attendance_id
INTO v_att_id
FROM per_establishment_attendances
WHERE person_id = l_person_id
AND establishment = l_establishment;
END IF;
--Change Over 10-Aug-10
per_qualifications_api.create_qualification (
p_validate => FALSE
,p_effective_date => l_date
,p_business_group_id => l_business_group_id
,p_qualification_type_id => l_qualification_type_id
,p_person_id => l_person_id
,p_grade_attained=>c_rec.grade
,p_attendance_id => v_att_id --Added by Amit Abhishek 10-Aug-10
--,p_title => c_rec.qualification_title
,p_status => 'C'
,p_attribute1 => l_specialization --ADDED BY AMIT ABHISHEK 24-JUN-10
,p_start_date => c_rec.start_date
,p_end_date => c_rec.end_date
,p_qualification_id => l_qualification_id
,p_object_version_number => l_object_version_number );
l_success_records := l_success_records + 1;
UPDATE XXPLG_QUALIFICATIONS_TMP
SET status = 'S',
error_message = l_error_msg,
CREATION_DATE = SYSDATE
WHERE rowid = c_rec.row_id;
ELSE
l_failure_records := l_failure_records +1;
UPDATE XXPLG_QUALIFICATIONS_TMP
SET STATUS = 'E',
CREATION_DATE = SYSDATE,
ERROR_MESSAGE = 'Qualification record already exist'
WHERE ROWID = c_rec.row_id;
END IF;
EXCEPTION
WHEN OTHERS THEN
l_error_msg := SUBSTR(SQLERRM,1,200);
l_failure_records := l_failure_records +1;
UPDATE XXPLG_QUALIFICATIONS_TMP
SET STATUS = 'E',
CREATION_DATE = SYSDATE,
ERROR_MESSAGE = l_error_msg
WHERE ROWID = c_rec.row_id;
END;
END IF;
IF MOD(l_total_records,20)=0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
apps.FND_FILE.put_line(apps.FND_FILE.output,'Time : ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
apps.FND_FILE.put_line(apps.FND_FILE.output,'#############################################################');
apps.FND_FILE.put_line(apps.FND_FILE.output,'Total Records To Be Loaded : ' || l_total_records);
apps.FND_FILE.put_line(apps.FND_FILE.output,'Total Success Records : ' || l_success_records);
apps.FND_FILE.put_line(apps.FND_FILE.output,'Total Failure Records : ' || l_failure_records);
apps.FND_FILE.put_line(apps.FND_FILE.output,'#############################################################');
END CREATE_QUALIFICATION;
-------------------------------------------< Terminate_Employee >------------------------------------------------------
PROCEDURE terminate_employee
IS
l_business_group_id NUMBER := FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');
l_final_process_date DATE;
l_supervisor_warning BOOLEAN;
l_event_warning BOOLEAN;
l_interview_warning BOOLEAN;
l_review_warning BOOLEAN;
l_recruiter_warning BOOLEAN;
l_entries_changed_warning VARCHAR2(100);
l_pay_proposal_warning BOOLEAN;
l_dod_warning BOOLEAN;
l_org_now_no_manager_warning BOOLEAN;
l_asg_future_changes_warning BOOLEAN;
l_alu_change_warning VARCHAR2(100);
l_term_reason_code VARCHAR2(100);
l_period_of_service_id NUMBER(15);
l_object_version_number NUMBER(15);
l_term_person_type_id NUMBER(15);
l_error_msg VARCHAR2(2000);
l_validate_cnt NUMBER;
l_status VARCHAR2(10) := 'True';
l_sqlerrm VARCHAR2(1000);
l_errmsg VARCHAR2(1000);
l_total_records NUMBER := 0;
l_success_records NUMBER := 0;
l_failure_records NUMBER := 0;
l_last_standard_process_date DATE;
l_count NUMBER;
/* Cursor to fetch the Temporary Data Those records which are to be validated*/
CURSOR c1_term_employee
IS
SELECT rowid row_id, xtp.*
FROM xxplg_terminate_employee xtp
WHERE NVL(status,'N') IN ('N','E');
CURSOR c1_term_employee1
IS
SELECT rowid row_id, xtp.*
FROM xxplg_terminate_employee xtp
WHERE status IN ('V','E');
/* Generic Cursor for all LOOK UP CODES */
CURSOR lookup_csr (p_lookup_type VARCHAR2,p_lookup_meaning VARCHAR2)
IS
SELECT lookup_code
FROM apps.fnd_common_lookups
WHERE UPPER(lookup_type) = UPPER(p_lookup_type)
AND UPPER(meaning) = UPPER(p_lookup_meaning)
AND enabled_flag = 'Y';
/* Get the Person Type Id */
CURSOR person_type_csr(p_person_type VARCHAR2)
IS
SELECT person_type_id
FROM apps.per_person_types
WHERE user_person_type = p_person_type
AND business_group_id = l_business_group_id
AND active_flag = 'Y';
/* get the period of service/OVN */
CURSOR period_of_service_csr(emp_no IN VARCHAR2)
IS
SELECT ppos.period_of_service_id,
ppos.object_version_number
FROM apps.per_periods_of_service ppos,
apps.per_all_people_f papf
WHERE papf.person_id = ppos.person_id
AND papf.attribute1 = emp_no
AND papf.business_group_id = l_business_group_id
AND SYSDATE between papf.effective_start_date and papf.effective_end_date;
BEGIN
apps.FND_FILE.PUT_LINE(apps.FND_FILE.output,'#############################################################');
apps.FND_FILE.PUT_LINE(apps.FND_FILE.output,'DATA MIGRATION - TERMINATION OF EMPLOYEES :');
apps.FND_FILE.PUT_LINE(apps.FND_FILE.output,'#############################################################');
apps.FND_FILE.PUT_LINE(apps.FND_FILE.output,'START TIME : ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
FOR c1_rec IN c1_term_employee
LOOP
l_errmsg := '';
l_status := 'True';
l_term_reason_code := NULL;
OPEN lookup_csr('LEAV_REAS',NVL(c1_rec.termination_reason,'Termination of Employement'));
FETCH lookup_csr INTO l_term_reason_code;
IF (lookup_csr%NOTFOUND) THEN
l_errmsg := l_errmsg || 'Leaving Reason Not Found. ';
l_status := 'False';
END IF;
CLOSE lookup_csr;
/* If all the above cond's satisfies then */
IF (l_status = 'True') THEN
UPDATE xxplg_terminate_employee
SET status = 'V',
term_reason_code = l_term_reason_code,
error_message = '',
creation_date = SYSDATE
WHERE rowid = c1_rec.row_id;
COMMIT;
ELSE
UPDATE xxplg_terminate_employee
SET status = 'E',
error_message = l_errmsg,
creation_date = SYSDATE
WHERE rowid = c1_rec.row_id;
COMMIT;
END IF;
END LOOP;
/* All the records with proper data */
FOR termination_rec IN c1_term_employee1
LOOP
l_errmsg := '';
l_status := 'True';
l_total_records := l_total_records + 1;
l_count :=0;
l_object_version_number := NULL;
l_period_of_service_id := NULL;
l_term_person_type_id := NULL;
l_final_process_date := NVL(termination_rec.final_process_date,LAST_DAY(termination_rec.actual_termination_date));
l_org_now_no_manager_warning :=NULL;
l_asg_future_changes_warning :=NULL;
l_entries_changed_warning :=NULL;
l_last_standard_process_date := LAST_DAY(termination_rec.actual_termination_date);
BEGIN
SELECT count(*)
INTO l_count
FROM per_all_people_f
WHERE business_group_id = l_business_group_id
AND attribute1 = termination_rec.employee_number
AND current_employee_flag = 'Y'
AND SYSDATE BETWEEN effective_start_date AND effective_end_date;
IF l_count>0 THEN
FOR period_rec IN period_of_service_csr(termination_rec.employee_number)
LOOP
l_period_of_service_id := period_rec.period_of_service_id;
l_object_version_number := period_rec.object_version_number;
END LOOP;
FOR person_type_rec IN person_type_csr(termination_rec.termination_type)
LOOP
l_term_person_type_id := person_type_rec.person_type_id;
END LOOP;
--apps.FND_FILE.PUT_LINE(apps.FND_FILE.output,'Before Actual Termination');
apps.hr_ex_employee_api.ACTUAL_TERMINATION_EMP (
p_effective_date => termination_rec.actual_termination_date
,p_period_of_service_id => l_period_of_service_id
,p_person_type_id => l_term_person_type_id
,p_object_version_number => l_object_version_number
,p_actual_termination_date => termination_rec.actual_termination_date
,p_leaving_reason => termination_rec.term_reason_code
,p_last_standard_process_date => l_last_standard_process_date
,p_supervisor_warning => l_supervisor_warning
,p_event_warning => l_event_warning
,p_interview_warning => l_interview_warning
,p_review_warning => l_review_warning
,p_recruiter_warning => l_recruiter_warning
,p_asg_future_changes_warning => l_asg_future_changes_warning
,p_entries_changed_warning => l_entries_changed_warning
,p_pay_proposal_warning => l_pay_proposal_warning
,p_dod_warning => l_dod_warning
,p_alu_change_warning => l_alu_change_warning
);
--apps.FND_FILE.PUT_LINE(apps.FND_FILE.output,'Before update term');
apps.hr_ex_employee_api.update_term_details_emp (
p_period_of_service_id => l_period_of_service_id
, p_effective_date => termination_rec.actual_termination_date
, p_object_version_number => l_object_version_number
, p_leaving_reason => termination_rec.term_reason_code
, p_notified_termination_date => termination_rec.actual_termination_date-30
);
--apps.FND_FILE.PUT_LINE(apps.FND_FILE.output,'Before final process');
apps.hr_ex_employee_api.final_process_emp (
p_period_of_service_id => l_period_of_service_id
, p_object_version_number => l_object_version_number
, p_final_process_date => l_final_process_date
, p_org_now_no_manager_warning => l_org_now_no_manager_warning
, p_asg_future_changes_warning => l_asg_future_changes_warning
, p_entries_changed_warning => l_entries_changed_warning );
UPDATE xxplg_terminate_employee
SET status = 'S',
error_message = '',
creation_date = SYSDATE
WHERE rowid = termination_rec.row_id;
l_success_records := l_success_records + 1;
ELSE
l_failure_records := l_failure_records + 1;
l_errmsg := l_errmsg|| ' Employee Not Found.';
UPDATE xxplg_terminate_employee
SET status = 'E',
error_message = l_errmsg,
creation_date = SYSDATE
WHERE rowid = termination_rec.row_id;
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
l_status := 'False';
l_errmsg := SUBSTR(SQLERRM,1,200);
l_failure_records := l_failure_records + 1;
UPDATE xxplg_terminate_employee
SET status = 'E',
error_message = l_errmsg,
creation_date = SYSDATE
WHERE rowid = termination_rec.row_id;
END;
IF MOD(l_total_records,20)=0 THEN
COMMIT;
END IF;
END LOOP;
apps.FND_FILE.put_line(apps.FND_FILE.output,'End Time : ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
apps.FND_FILE.put_line(apps.FND_FILE.output,'#############################################################');
apps.FND_FILE.put_line(apps.FND_FILE.output,'Total Records To Be Loaded : ' || l_total_records);
apps.FND_FILE.put_line(apps.FND_FILE.output,'Total Success Records : ' || l_success_records);
apps.FND_FILE.put_line(apps.FND_FILE.output,'Total Failure Records : ' || l_failure_records);
apps.FND_FILE.put_line(apps.FND_FILE.output,'#############################################################');
END terminate_employee;
END XXARJ_HR_CNV_EMP_IND_PKG;
--select * from user_errors where name='XXPLG_HR_CNV_EMP_IND_PKG' and attribute='ERROR'
No comments:
Post a Comment