Declare
l_address_id     			number;
l_object_version_number 		number;
v_error_code				varchar2(10);
v_error_desc				varchar2(500);
cursor c1 is 
SELECT mo.empl_id
      ,paf.full_name
      ,paf.person_id
      ,paf.effective_start_date
      ,mo.add_line1
      ,mo.add_line2
      ,mo.add_line3
      ,mo.city
      ,mo.state
      ,mo.pincode
  FROM abc.XXM_HR_EMP_PER_ADD_STG mo --this table should contain the data given by client for upload
      ,per_all_people_f paf
 WHERE TO_CHAR(mo.empl_id) = paf.employee_number
   AND SYSDATE < paf.effective_end_date
and mo.add_line1 is not null 
;
begin
for vrec IN c1
LOOP 
begin
hr_person_address_api.create_person_address
  (
   p_validate                     	=>FALSE
  ,p_effective_date                	=>vrec.effective_start_date
  ,p_person_id                     	=>vrec.person_id 
  ,p_primary_flag                  	=>'Y' 
  ,p_date_from                     	=>vrec.effective_start_date
  ,p_address_line1                 	=>vrec.add_line1 
  ,p_address_line2               	=>vrec.add_line2
  ,p_address_line3                 	=>vrec.add_line3
  ,p_add_information14  		=>vrec.city  
  ,p_add_information15  		=>vrec.state    
  ,p_postal_code                   	=>vrec.pincode 
  ,p_address_type                       =>'IN_C'
  ,p_style                     	        =>'IN_GLB' 
  ,p_address_id                    	=>l_address_id 
  ,p_object_version_number      	=>l_object_version_number 
  );
EXCEPTION
WHEN OTHERS THEN
v_error_code := SQLCODE;
v_error_desc := SQLERRM;
UPDATE abc.XXM_HR_EMP_PER_ADD_STG SET ERROR_CODE=v_error_code,ERROR_DESC=v_error_desc
WHERE empl_id=vrec.empl_id;
end;
commit;
END LOOP;
UPDATE abc.XXM_HR_EMP_PER_ADD_STG SET flag='N'
WHERE ERROR_CODE IS NOT null;
END emp_add_prc;
END xxm_hr_employee_add_pkg;
/