SRAVAN ORACLE APPS HRMS: HRMS-Employee Person Conversion

create or replace PACKAGE xx_hr_person_conv_pkg
AS
PROCEDURE xx_hr_person_conv_proc
(
xc_errbuf OUT VARCHAR2,
xc_retcode OUT VARCHAR2,
pc_country IN VARCHAR2);
END xx_hr_person_conv_pkg;
/
create or replace PACKAGE BODY xx_hr_person_conv_pkg
AS
PROCEDURE xx_hr_person_conv_proc
(
xc_errbuf OUT VARCHAR2,
xc_retcode OUT VARCHAR2,
pc_country IN VARCHAR2)
IS
-- +================================================================+
-- | Name : xx_hr_person_conv_proc
-- | Type : Procedure
-- | Description : Procedure to create address details for the employee
-- | Parameters IN : p_country
-- | Returns OUT : p_err_buf
-- | p_retcode
-- +================================================================+
lc_err_flag VARCHAR2(1); --if any is validation is errored then lc_err_flag ='E' else lc_err_flag='N'
lc_err_mesg VARCHAR2(4000);
ln_tot_cnt NUMBER DEFAULT 0;
ln_tot_suc NUMBER DEFAULT 0;
ln_tot_fal NUMBER DEFAULT 0;
lc_title VARCHAR2(10);
lc_middle_names VARCHAR2(20);
lc_hire_date DATE;
lc_nationality VARCHAR2(25);
lc_country_of_birth VARCHAR2(50);
lc_i_9_status_tax VARCHAR2(25);
ld_i_9_expiration DATE;
ln_person_id NUMBER;
ln_assignment_id NUMBER;
lc_status VARCHAR2(30);
ln_year NUMBER;
lc_work_schedule VARCHAR2(30);
ln_per_object_version_number NUMBER;
ln_asg_object_version_number NUMBER;
ld_per_effective_start_date DATE;
ld_per_effective_end_date DATE;
lc_full_name VARCHAR2 (50);
ln_per_comment_id NUMBER;
ln_count_emp NUMBER;
ln_assignment_sequence NUMBER;
ln_assignment_number VARCHAR2(50);
lb_name_combination_warning BOOLEAN;
lb_assign_payroll_warning BOOLEAN;
lb_orig_hire_warning BOOLEAN;
ln_pdp_object_version_number NUMBER;
ln_comment_id NUMBER;
lc_gender VARCHAR2(10);
lc_person_type_id NUMBER;
ln_business_group_id NUMBER;
lc_system_person_type VARCHAR2(100);
lc_registered_disabled VARCHAR2(50);
lc_email_address VARCHAR2(50);
lc_date_of_birth VARCHAR2(50);
lc_f_date_of_birth VARCHAR2(50);
ld_ethnic_origin NUMBER;
lc_ethnicity_disclosed VARCHAR2(10);
lc_prefix VARCHAR2(50);
lc_suffix VARCHAR2(50);
lc_ssn VARCHAR2(50);
CURSOR cur_xx_person-- select columns which needs to be import.
IS
SELECT XHECS.rowid
, XHECS.*
FROM xx.xx_hr_emp_create_stg XHECS
WHERE NVL(process_flag,'N') <> 'Y'
AND country_code = pc_country
AND employee_number IS NOT NULL;
CURSOR cur_xx_person_err
IS
SELECT *
FROM xx.xx_hr_emp_create_stg
WHERE process_flag ='E'
AND country_code = pc_country;
BEGIN
FOR xx_person_cur_rec IN cur_xx_person
LOOP
ln_tot_cnt := ln_tot_cnt + 1;
lc_err_mesg := NULL;
lc_err_flag := NULL;
--*****************************VALIDATIONS*******************************
-- ------------------------------------------------------------
-- Deriving Business_Group_id
-- ------------------------------------------------------------
IF (xx_person_cur_rec.Business_Group_Name IS NULL) THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'Business Group Name should not be NULL';
ELSE
BEGIN
SELECT business_group_id
INTO ln_business_group_id
FROM per_business_groups
WHERE name = xx_person_cur_rec.business_group_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'No business_group_id for Business_Group_id' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
WHEN OTHERS THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'Error fetching Business_Group_id' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
END;
END IF;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee Business Group Id is : ' || ln_business_group_id);
------------------------------------------------------------
-- Validating Employee Number
-- ------------------------------------------------------------
BEGIN
SELECT COUNT(1)
INTO ln_count_emp
FROM per_all_people_f
WHERE employee_number=xx_person_cur_rec.employee_number
AND business_group_id = ln_business_group_id;

FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee_number is : ' || xx_person_cur_rec.employee_number);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee ln_count_emp is : ' || ln_count_emp);
IF ln_count_emp >=1 THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||xx_person_cur_rec.employee_number||'Employee Number allready exists in the system';
END IF;
END;
------------------------------------------------------------
-- Validating Last Name of the Employee
-- ------------------------------------------------------------
BEGIN
IF (xx_person_cur_rec.last_name IS NULL) THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'Last Name should not be NULL';
END IF;
END;
-- FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee last_name is : ' || xx_person_cur_rec.last_name);
-- ------------------------------------------------------------
-- Validating Gender Lookup code
-- ------------------------------------------------------------
IF (xx_person_cur_rec.sex IS NULL) THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'Gender Cannot be NULL';
ELSE
BEGIN
SELECT DISTINCT lookup_code
INTO lc_gender
FROM fnd_lookup_values
WHERE upper(meaning) = upper(trim(REPLACE(REPLACE(REPLACE(xx_person_cur_rec.sex,CHR(10)), CHR(13)), CHR(9))))
AND lookup_type = 'SEX'
AND enabled_flag = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'No Lookup Code found for the Gender' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
WHEN OTHERS THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'Error fetching Gender' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
END;
END IF;
-- FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee Gender is : ' || lc_gender);
------------------------------------------------------------
-- Validating Hire Date
-- ------------------------------------------------------------
BEGIN
IF (xx_person_cur_rec.hire_date IS NULL) THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'Hire Date Cannot be NULL';
ELSE
SELECT xx_person_cur_rec.hire_date
INTO lc_hire_date
FROM DUAL;
END IF;
END;
-- FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee Hire Date Is : ' || lc_hire_date);
-- --------------------------------------------------------
-- Validating Marital Status Lookup code
-- ------------------------------------------------------------
IF xx_person_cur_rec.marital_status IS NULL THEN
lc_status := NULL;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Marital status is null');
ELSE
BEGIN
SELECT DISTINCT lookup_code
INTO lc_status
FROM fnd_lookup_values
WHERE meaning = xx_person_cur_rec.marital_status
AND lookup_type = 'MARITAL_STATUS'
AND enabled_flag = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'No Lookup Code Found for the MARITAL STATUS' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
FND_FILE.PUT_LINE(FND_FILE.LOG,'No lookup code found');
WHEN OTHERS THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'Error fetching MARITAL_STATUS' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
END;
END IF;
-- FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee Marital status Is : ' || lc_status);
-- --------------------------------------------------------
-- Validating country of birth Lookup code
-- ------------------------------------------------------------
IF xx_person_cur_rec.country_of_birth IS NULL THEN
lc_country_of_birth := NULL;
ELSE
BEGIN
SELECT DISTINCT territory_code
INTO lc_country_of_birth
FROM fnd_territories_tl
WHERE upper(territory_short_name) = upper(xx_person_cur_rec.country_of_birth);
EXCEPTION
WHEN NO_DATA_FOUND THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'No territory_code found for country of birth' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
WHEN OTHERS THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'Error fetching country of birth' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
END;
END IF;
-- FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee country_of_birth: ' || lc_country_of_birth);
--------------------------------------------------------
-- Validating Nationality Lookup code
-- ------------------------------------------------------------
IF xx_person_cur_rec.nationality IS NULL THEN
lc_nationality := NULL;
ELSE
BEGIN
SELECT DISTINCT lookup_code
INTO lc_nationality
FROM hr_lookups
WHERE meaning = xx_person_cur_rec.nationality
AND lookup_type = 'NATIONALITY'
AND enabled_flag = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'No Data found for the Nationality' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
WHEN OTHERS THEN
lc_err_flag := 'E';
lc_err_mesg := 'Error fetching Nationality' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
END;
END IF;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee nationality is : ' || lc_nationality);
--------------------------------------------------------
-- Validating TITLE Lookup code
-- ------------------------------------------------------------
IF xx_person_cur_rec.title IS NULL THEN
lc_title := NULL;
ELSE
BEGIN
SELECT DISTINCT lookup_code
INTO lc_title
FROM hr_lookups
WHERE meaning = xx_person_cur_rec.Title
AND lookup_type = 'TITLE'
AND enabled_flag = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'No Data Found For the Title' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
WHEN OTHERS THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'Error fetching Title' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
END;
END IF;
-- FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee title is : ' || lc_title);
--------------------------------------------------------
-- Validating Registerd Disabled
-- ------------------------------------------------------------
IF xx_person_cur_rec.registered_disabled IS NULL THEN
lc_registered_disabled := NULL;
ELSE
BEGIN
SELECT DISTINCT lookup_code
INTO lc_registered_disabled
FROM hr_lookups
WHERE meaning = xx_person_cur_rec.registered_disabled
AND lookup_type = 'REGISTERED_DISABLED'
AND enabled_flag = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'No Data Found For the Registerd Disabled' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
WHEN OTHERS THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'Error fetching lookup code for Title Registerd Disabled' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
END;
END IF;
-- FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee REGISTERED_DISABLED is : ' || lc_registered_disabled);
--------------------------------------------------------
-- Validating XX Email Address
-- ------------------------------------------------------------
BEGIN
IF xx_person_cur_rec.email_address IS NULL THEN
lc_email_address := NULL;
ELSE
lc_email_address := xx_person_cur_rec.email_address;
END IF;
END;
-- FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee Email Address is : ' || lc_email_address);
--------------------------------------------------------
-- Validating Date Of Birth
-- ------------------------------------------------------------
BEGIN
IF xx_person_cur_rec.date_of_birth IS NULL THEN
lc_f_date_of_birth := NULL;
ELSE
--lc_date_of_birth := xx_person_cur_rec.date_of_birth;
SELECT TO_CHAR(TO_DATE(xx_person_cur_rec.date_of_birth,'DD-MON-RRRR'),'DD-MON-YYYY')
INTO lc_date_of_birth
FROM DUAL;

FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee date_of_birth is : ' || lc_date_of_birth);
SELECT TO_CHAR(TO_DATE(xx_person_cur_rec.date_of_birth,'DD-MON-RRRR'),'YY')
INTO ln_year
FROM DUAL ;

FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee Full Year is : ' || ln_year);
IF ln_year < 50 THEN
SELECT TO_CHAR(TO_DATE(lc_date_of_birth,'DD-MON-RRRR'),'DD-MON-')
||'19'
|| ln_year
INTO lc_f_date_of_birth
FROM DUAL;

FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee Converted Date : ' || lc_f_date_of_birth);
ELSE
lc_f_date_of_birth:= lc_date_of_birth;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee Full Date Of Birth : ' || lc_f_date_of_birth);
END IF;
END IF;
END;
--------------------------------------------------------
-- Validating Ethnic Of Origin For US
-- ------------------------------------------------------------
IF xx_person_cur_rec.Business_Group_Name = 'XXUS' AND xx_person_cur_rec.ethinic_origin IS NOT NULL THEN
BEGIN
SELECT DISTINCT lookup_code
INTO ld_ethnic_origin
FROM hr_lookups
WHERE upper(meaning)= upper(xx_person_cur_rec.ethinic_origin)
AND lookup_type = 'US_ETHNIC_GROUP'
AND enabled_flag = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'No Data Found For the ethnic origin ' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
WHEN OTHERS THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'Error fetching lookup code for ethnic origin ' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
END;
ELSIF xx_person_cur_rec.ethinic_origin IS NULL THEN
ld_ethnic_origin := NULL;
END IF;
-- FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee ethnic_origin is : ' || ld_ethnic_origin);
---------------------------------------------------------------
-- Validating Ethnicity Disclosed
-- ------------------------------------------------------------
IF xx_person_cur_rec.ethnicity_disclosed IS NULL THEN
lc_ethnicity_disclosed := NULL;
ELSE
BEGIN
SELECT DECODE(xx_person_cur_rec.ethnicity_disclosed ,'Yes','Y','No','N')
INTO lc_ethnicity_disclosed
FROM DUAL;
EXCEPTION
WHEN NO_DATA_FOUND THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'No data Found For the ethnicity disclosed' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
WHEN OTHERS THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'Error fetching lookup code for ethnicity disclosed ' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
END;
END IF;
-- FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee ethnicity_disclosed is : ' || lc_ethnicity_disclosed);
--------------------------------------------------------
-- Validating I9 Status For US
-- ------------------------------------------------------------
BEGIN
IF xx_person_cur_rec.business_group_name = 'XXUS' AND xx_person_cur_rec.i_9_status IS NOT NULL THEN
BEGIN
SELECT DISTINCT lookup_code
INTO lc_i_9_status_tax
FROM hr_lookups
WHERE upper(meaning)= upper(xx_person_cur_rec.i_9_status )
AND lookup_type = 'PER_US_I9_STATE'
AND enabled_flag = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'No Look Up Found For the I-9 Status ' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
WHEN OTHERS THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'Error fetching lookup code For the I-9 Status ' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
END;
END IF;

IF xx_person_cur_rec.business_group_name = 'XXAU' AND xx_person_cur_rec.payroll_state_tax IS NOT NULL THEN
BEGIN
SELECT DISTINCT lookup_code
INTO lc_i_9_status_tax
FROM fnd_common_lookups
WHERE upper(lookup_code)= upper(xx_person_cur_rec.payroll_state_tax )
AND lookup_type = 'AU_STATE'
AND enabled_flag = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'No Look Up Found For payroll_state_tax in AU_STATE Lookup ' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
WHEN OTHERS THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'Error fetching lookup code For payroll_state_tax in AU_STATE Lookup ' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
END;
END IF;

END;

-- FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee lc_i_9_status is : ' || lc_i_9_status);
-----------------------------------------------------------
-- Validating I9 Expiration
-- ------------------------------------------------------------
BEGIN
IF xx_person_cur_rec.i_9_expiration IS NULL THEN
ld_i_9_expiration := NULL;
ELSE
ld_i_9_expiration := xx_person_cur_rec.i_9_expiration;
END IF;
END;
BEGIN
IF xx_person_cur_rec.prefix IS NULL THEN
lc_prefix := NULL;
ELSE
lc_prefix := xx_person_cur_rec.prefix;
END IF;
END;
-- FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee prefix is : ' || lc_prefix);
--------------------------------------------------------
-- Validating Suffix
-- ------------------------------------------------------------
BEGIN
IF xx_person_cur_rec.suffix IS NULL THEN
lc_suffix := NULL;
ELSE
lc_suffix := xx_person_cur_rec.suffix;
END IF;
END;
-- FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee suffix is : ' || lc_suffix);
--------------------------------------------------------
-- Validating Middle Name
-- ------------------------------------------------------------
BEGIN
IF xx_person_cur_rec.middle_name IS NULL THEN
lc_middle_names := NULL;
ELSE
lc_middle_names := xx_person_cur_rec.middle_name;
END IF;
END;
-- FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee middle_name is : ' || lc_middle_names);
--------------------------------------------------------
-- Validating SSN Number
-- ------------------------------------------------------------
BEGIN
IF xx_person_cur_rec.ssn IS NULL THEN
lc_ssn := NULL;
ELSIF xx_person_cur_rec.business_group_name = 'XXCA' THEN
BEGIN
SELECT SUBSTR(xx_person_cur_rec.ssn,1,3)
||' '
|| SUBSTR(xx_person_cur_rec.ssn,4,3)
||' '
|| SUBSTR(xx_person_cur_rec.ssn,7)ssn
INTO lc_ssn
FROM DUAL ;
EXCEPTION
WHEN OTHERS THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'Exception fetching ssn code For Canada ' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
END;

ELSE
lc_ssn := xx_person_cur_rec.ssn;
END IF;
END;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee lc_ssn is : ' || lc_ssn);

--------------------------------------------------------
-- Validating Work Schedule
-- ------------------------------------------------------------
BEGIN

IF xx_person_cur_rec.work_schedule IS NULL THEN
lc_work_schedule := NULL;
ELSE
BEGIN
SELECT DISTINCT lookup_code
INTO lc_work_schedule
FROM fnd_common_lookups
WHERE upper(meaning)= upper(xx_person_cur_rec.work_schedule)
AND lookup_type = 'WORK_SCHEDULE'
AND enabled_flag = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'No Look Up Found For the WORK SCHEDULE ' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
WHEN OTHERS THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'Error fetching lookup code For the WORK SCHEDULE ' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
END;
END IF;
END;
BEGIN
SELECT DISTINCT person_type_id
,system_person_type
INTO lc_person_type_id,
lc_system_person_type
FROM per_person_types
WHERE user_person_type =xx_person_cur_rec.person_type_name
AND system_person_type IN ('EMP','CWK')
AND business_group_id =ln_business_group_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'No data Found For This Person Type ' ||xx_person_cur_rec.person_type_name|| TO_CHAR(SQLCODE)||'-'||SQLERRM;
WHEN OTHERS THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'Exception fetching lookup code for Person Type '||xx_person_cur_rec.person_type_name || TO_CHAR(SQLCODE)||'-'||SQLERRM;
END;
IF lc_system_person_type <> 'EMP' AND lc_system_person_type <> 'CWK' THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'Error Person Type is Not Employee or Contingent Worker' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
END IF;
IF NVL(lc_err_flag,'N') <>'E' AND lc_system_person_type = 'EMP' THEN
BEGIN
HR_EMPLOYEE_API.CREATE_EMPLOYEE
(p_validate =>false
,p_hire_date =>TRUNC(lc_hire_date)
,p_business_group_id =>ln_business_group_id
,p_last_name =>xx_person_cur_rec.last_name
,p_sex =>lc_gender
,p_person_type_id =>lc_person_type_id
,p_per_comments =>NULL
,p_date_employee_data_verified =>NULL
,p_date_of_birth =>TRUNC(to_date(lc_f_date_of_birth,'DD-MON-RRRR'))
,p_email_address =>lc_email_address
,p_employee_number =>xx_person_cur_rec.employee_number
,p_expense_check_send_to_addres =>NULL
,p_first_name =>xx_person_cur_rec.first_name
,p_known_as =>xx_person_cur_rec.preferred_name
,p_marital_status =>lc_status
,p_middle_names =>lc_middle_names
,p_nationality =>lc_nationality
,p_national_identifier =>lc_ssn
,p_previous_last_name =>NULL
,p_registered_disabled_flag =>lc_registered_disabled
,p_title =>lc_title
,p_vendor_id =>NULL
,p_work_telephone =>NULL
,p_coord_ben_no_cvg_flag =>'N'
,p_dpdnt_vlntry_svce_flag =>'N'
,p_attribute_category =>NULL--lc_attribute_category
,p_attribute1 =>NULL--lc_benefit_number
,p_attribute2 =>NULL--lc_personal_email_address
,p_attribute3 =>NULL--lc_include_in_headcount
,p_attribute4 =>NULL--NVL(xx_person_cur_rec.individual_tax_payor_number,'')
,p_attribute5 =>NULL--NVL(xx_person_cur_rec.ctps_number ,'')
,p_attribute6 =>NULL--NVL(xx_person_cur_rec.first_job ,'')
,p_attribute7 =>NULL--NVL(xx_person_cur_rec.social_integration_program ,'')
,p_attribute8 =>NULL--NVL(xx_person_cur_rec.income_tax_number ,'')
,p_attribute9 =>NULL--NVL(xx_person_cur_rec.voters_card_number ,'')
,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 =>NULL
,p_per_information1 =>ld_ethnic_origin
,p_per_information2 =>lc_i_9_status_tax
,p_per_information3 =>to_char(ld_i_9_expiration,'RRRR-MON-DD')
,p_per_information7 =>NULL
,p_per_information11 =>lc_ethnicity_disclosed
,p_pre_name_adjunct =>lc_prefix
,p_suffix =>lc_suffix
,p_benefit_group_id =>NULL
,p_receipt_of_death_cert_date =>NULL
,p_coord_ben_med_pln_no =>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_original_date_of_hire =>NULL
,p_adjusted_svc_date =>NULL
,p_town_of_birth =>NVL(xx_person_cur_rec.town_of_birth ,'')
,p_region_of_birth =>NULL
,p_country_of_birth =>lc_country_of_birth
,p_global_person_id =>NULL
,p_party_id =>NULL
,p_work_schedule =>lc_work_schedule
,p_person_id =>ln_person_id
,p_assignment_id =>ln_assignment_id
,p_per_object_version_number =>ln_per_object_version_number
,p_asg_object_version_number =>ln_asg_object_version_number
,p_per_effective_start_date =>ld_per_effective_start_date
,p_per_effective_end_date =>ld_per_effective_end_date
,p_full_name =>lc_full_name
,p_per_comment_id =>ln_per_comment_id
,p_assignment_sequence =>ln_assignment_sequence
,p_assignment_number =>ln_assignment_number
,p_name_combination_warning =>lb_name_combination_warning
,p_assign_payroll_warning =>lb_assign_payroll_warning
,p_orig_hire_warning =>lb_orig_hire_warning );
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee Inserted is : ' || xx_person_cur_rec.employee_number);
UPDATE xx_hr_emp_create_stg
SET process_flag = 'Y'
,message = NVL(lc_err_mesg,NULL)
WHERE employee_number = xx_person_cur_rec.employee_number
AND rowid = xx_person_cur_rec.rowid ;

ln_tot_suc := ln_tot_suc + 1;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'Exception when calling API of Employee:' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
UPDATE xx_hr_emp_create_stg
SET process_flag = 'E'
,message = NVL(lc_err_mesg,NULL)
WHERE employee_number = xx_person_cur_rec.employee_number
AND rowid = xx_person_cur_rec.rowid ;

ln_tot_fal := ln_tot_fal + 1;
END;
ELSIF NVL(lc_err_flag,'N') <>'E' AND lc_system_person_type = 'CWK' THEN
BEGIN
HR_CONTINGENT_WORKER_API.CREATE_CWK
(p_validate =>false
,p_start_date =>TRUNC(lc_hire_date)
,p_business_group_id =>ln_business_group_id
,p_last_name =>xx_person_cur_rec.last_name
,p_person_type_id =>lc_person_type_id
,p_npw_number =>xx_person_cur_rec.employee_number
,p_date_of_birth =>TRUNC(to_date(lc_f_date_of_birth,'DD-MON-RRRR'))
,p_date_of_death =>NULL
,p_dpdnt_adoption_date =>NULL
,p_dpdnt_vlntry_svce_flag =>NULL
,p_email_address =>lc_email_address
,p_first_name =>xx_person_cur_rec.first_name
,p_known_as =>xx_person_cur_rec.preferred_name
,p_middle_names =>lc_middle_names
,p_national_identifier =>lc_ssn
,p_registered_disabled_flag =>lc_registered_disabled
,p_resume_exists =>NULL
,p_resume_last_updated =>NULL
,p_second_passport_exists =>NULL
,p_sex =>lc_gender
,p_title =>lc_title
,p_work_telephone =>NULL
,p_town_of_birth =>NVL(xx_person_cur_rec.town_of_birth ,'')
,p_country_of_birth =>lc_country_of_birth
,p_person_id =>ln_person_id
,p_per_object_version_number =>ln_per_object_version_number
,p_per_effective_start_date =>ld_per_effective_start_date
,p_per_effective_end_date =>ld_per_effective_end_date
,p_pdp_object_version_number =>ln_pdp_object_version_number
,p_full_name =>lc_full_name
,p_comment_id =>ln_comment_id
,p_assignment_id =>ln_assignment_id
,p_asg_object_version_number =>ln_asg_object_version_number
,p_assignment_sequence =>ln_assignment_sequence
,p_assignment_number =>ln_assignment_number
,p_name_combination_warning =>lb_name_combination_warning
);
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Contigent Worker Inserted is : ' || xx_person_cur_rec.employee_number);
UPDATE xx_hr_emp_create_stg
SET process_flag = 'Y'
,message = NVL(lc_err_mesg,NULL)
WHERE employee_number = xx_person_cur_rec.employee_number
AND rowid = xx_person_cur_rec.rowid ;

ln_tot_suc := ln_tot_suc + 1;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'Exception when calling API of Contigent Worker:' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
UPDATE xx_hr_emp_create_stg
SET process_flag = 'E'
,message = NVL(lc_err_mesg,NULL)
WHERE employee_number = xx_person_cur_rec.employee_number
AND rowid = xx_person_cur_rec.rowid ;

ln_tot_fal := ln_tot_fal + 1;
END;
ELSE
ln_tot_fal := ln_tot_fal + 1;
lc_err_flag := 'E';
lc_err_mesg := lc_err_mesg||'Error : Inserting Person Details - ' || TO_CHAR(SQLCODE)||'-'||SQLERRM;
UPDATE xx_hr_emp_create_stg
SET process_flag = 'E'
,message = NVL(lc_err_mesg,'Error')
WHERE employee_number = xx_person_cur_rec.employee_number
AND rowid = xx_person_cur_rec.rowid ;
COMMIT;
END IF;
END LOOP;
FND_FILE.PUT_LINE(FND_FILE.LOG,'*****************************************************************************************');
FND_FILE.PUT_LINE(FND_FILE.LOG,'*****************PERSON DETAILS UPLOAD REPORT*********************************');
FND_FILE.PUT_LINE(FND_FILE.LOG,'***************************************************************');
FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Numbers of Records Read : ' || ln_tot_cnt);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Numbers of Records Inserted : ' || ln_tot_suc);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Numbers of Records Errored Out : ' || ln_tot_fal);
FND_FILE.PUT_LINE(FND_FILE.LOG,'*****************************************************************************************');
FND_FILE.PUT_LINE(FND_FILE.LOG,'ERRORED OUT EMPLOYEE RECORDS ');
FOR cur_err_rec IN cur_xx_person_err
LOOP
FND_FILE.PUT_LINE(FND_FILE.LOG,'Employee Number : '||cur_err_rec.employee_number ||': '||cur_err_rec.message);
END LOOP;
COMMIT;
END xx_hr_person_conv_proc;
END xx_hr_person_conv_pkg;

SRAVAN ORACLE APPS HRMS

Tuesday, March 10, 2015

HRMS-Employee Person Conversion

No comments:

Post a Comment