HRMS Employee Migration In Oracle Applications

DECLARE
V_API_ERROR                   VARCHAR2(500);
p_employee_number    varchar2(200);
V_ERROR_MSG                   VARCHAR2(500) := NULL; 
P_NATIONALITY                 VARCHAR2(200);
p_person_type_id              NUMBER; 
P_business_group_id           NUMBER ;
P_EMP_NO                      VARCHAR2(200);
P_LAST_NAME                   VARCHAR2(200);
P_FULL_NAME                   VARCHAR2(200);
P_PERSON_TYPE_ID              VARCHAR2(200);
P_SEX                         VARCHAR2(200);
P_RELIGION                    VARCHAR2(200);
P_NATIONAL_IDENTIFER          VARCHAR2(200);
P_DATE_OF_BIRTH               VARCHAR2(200);
P_TOWN_OF_BIRTH               VARCHAR2(200);
P_MARITAL_STATUS              VARCHAR2(200);
P_NATIONALITY                 VARCHAR2(200);
P_NATIONAL_ID_TYPE            VARCHAR2(200);
P_ID_NO                       VARCHAR2(200);
P_ID_START_DATE               VARCHAR2(200);
P_ID_END_DATE                 VARCHAR2(200);
P_ID_PLACE                    VARCHAR2(200);
P_ID_GOV                      VARCHAR2(200);
P_EFFECTIVE_START_DATE        VARCHAR2(200);
p_person_id                           number;
p_assignment_id                       number;
p_per_object_version_number           number;
p_asg_object_version_number           number;
p_per_effective_start_date            date;
p_per_effective_end_date              date;
p_per_comment_id                      number;
p_assignment_sequence                 number;
p_assignment_number                   varchar2(200);
p_name_combination_warning            boolean;
p_assign_payroll_warning              boolean;
p_orig_hire_warning                   boolean;
Gov_Date   varchar2(20);
cursor xx_emp is
select * 
FROM XX_HR_EMPLOYYE_API_INTERFACE
where ser between 7 and 7 ;

BEGIN
 
for HR_data in xx_emp loop
begin
Select h.H_DATE into  Gov_Date From   kfu_hijra h where  h.G_DATE = hr_data.gov_date   ;
exception
when others then null;
end;

BEGIN
hr_employee_api.create_employee ( 
p_hire_date                   =>HR_data.HIRE_DATE , 
p_business_group_id           => 81,
p_title                       => hr_data.emp_title,
p_email_address               => hr_data.email,
p_first_name                  => HR_data.first_name,
p_last_name                   => HR_data.LAST_NAME,
p_sex                         => HR_data.sex,
p_person_type_id              => HR_data.person_type_id,
p_date_of_birth               => HR_data.DATE_OF_BIRTH,
p_employee_number             => p_employee_number ,
p_marital_status              => HR_data.MARITAL_STATUS,
p_nationality                 => HR_data.NATIONALITY,
p_national_identifier         => HR_data.NATIONAL_IDENTIFIER ,
p_pre_name_adjunct            => NULL,
p_town_of_birth               => HR_data.TOWN_OF_BIRTH ,            
p_attribute1                  => hr_data.emp_old_no ,
p_attribute2                  => Gov_Date ,
p_person_id                   => p_person_id,
p_assignment_id               => p_assignment_id,
p_per_object_version_number   => p_per_object_version_number,
p_asg_object_version_number   => p_asg_object_version_number,
p_per_effective_start_date    => p_per_effective_start_date,
p_per_effective_end_date      => p_per_effective_end_date,
p_full_name                   => p_full_name,
p_per_comment_id              => p_per_comment_id,        
p_assignment_sequence         => p_assignment_sequence,          
p_assignment_number           => p_assignment_number,        
p_name_combination_warning    => p_name_combination_warning,      
p_assign_payroll_warning      => p_assign_payroll_warning,
p_per_information1            => hr_data.second_name , 
p_per_information2            => trim(hr_data.third_name),   
p_per_information7            => hr_data.RELIGION,        
p_orig_hire_warning           => p_orig_hire_warning);

commit;
begin
update  XX_HR_EMPLOYYE_API_INTERFACE
set Status =  'Done'  , PERSON_ID = p_person_id , new_number = p_employee_number , ERROR_MSG = null
where EMP_old_NO = HR_data.EMP_old_NO;                           

exception 
when others then null;
end;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
V_API_ERROR  := SQLERRM;
V_ERROR_MSG  := V_ERROR_MSG||'   '||V_API_ERROR;

UPDATE XX_HR_EMPLOYYE_API_INTERFACE
SET ERROR_MSG = V_ERROR_MSG , Status =  'Error'
where EMP_old_NO = HR_data.EMP_old_NO;


COMMIT;
 

END;
end loop;
END ;