Create Agency Phone details

CREATE OR REPLACE PROCEDURE XXX_Hrmig_Proc_Agency_Phone AS
l_object_version_number NUMBER(30);
l_phone_id NUMBER(30);
l_err_message VARCHAR2(1000);
ls_err_msg VARCHAR2(200);
l_valid VARCHAR2(10) := 'Y';
l_effective_date DATE;
l_rows_processed NUMBER := 0;
l_commit_point NUMBER := 10;
l_eff_start_date DATE;
l_count NUMBER(10);
l_comment_id NUMBER(10);
l_effective_start_date DATE := NULL;
l_effective_end_date DATE := NULL;
l_last_object_ver NUMBER(10) := 0;
l_party_id NUMBER(10);
l_phone_type VARCHAR2(25);
l_state VARCHAR2(50);
l_busn_grp_id NUMBER(10) := 385;
l_person_id NUMBER(10);
CURSOR cur_ext_app IS
SELECT *
FROM XXX_HRMIG_AGENCY_PHONE_25JAN07
WHERE NVL(verify_flag,'N') = 'N';

BEGIN
Hr_General.g_data_migrator_mode := 'Y';
/*SELECT organization_id
INTO l_busn_grp_id
FROM hr_all_organization_units_tl
WHERE name = 'KMBL Business Group';*/

FOR i IN 1..100 LOOP

FOR sel IN cur_ext_app LOOP

BEGIN
l_err_message := NULL;
ls_err_msg := NULL;

/*BEGIN
--fetch assignment id and object version number for the employee assignment
SELECT lookup_code
INTO l_phone_type --M-mobile,W1-office
FROM hr_lookups
WHERE UPPER(meaning) = UPPER(TRIM(sel.phone_type))
AND lookup_type = 'PHONE_TYPE';
EXCEPTION
WHEN OTHERS THEN
l_err_message :=l_err_message|| 'InValid Phone Type.';
l_valid := 'N';
END; */

BEGIN
SELECT party_id,person_id
INTO l_party_id,l_person_id
FROM per_all_people_f
WHERE last_name = sel.last_name
AND business_group_id = l_busn_grp_id
AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
EXCEPTION
WHEN OTHERS THEN
l_err_message :=l_err_message|| 'InValid Employee Number.';
l_valid := 'N';
END;

BEGIN
SELECT effective_start_date
INTO L_eff_start_date
FROM per_all_people_f
WHERE person_id = l_person_id
AND business_group_id = 385
AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
EXCEPTION
WHEN OTHERS THEN
l_err_message :=l_err_message|| 'InValid start date';
l_valid := 'N';
END; 

IF (l_valid <> 'N') THEN
BEGIN
Hr_Phone_Api.create_phone
(p_date_from => '01-JAN-1985',
p_phone_type => 'W1',
p_phone_number => sel.phone,
p_effective_date => L_eff_start_date,
-- p_party_id => l_party_id,
p_parent_id => l_person_id,
p_parent_table => 'PER_ALL_PEOPLE_F',
p_object_version_number => l_object_version_number,
p_phone_id => l_phone_id);
EXCEPTION
WHEN OTHERS THEN
ls_err_msg := SQLERRM;
UPDATE XXX_HRMIG_AGENCY_PHONE_25JAN07
SET verify_flag = 'N',err_message = l_err_message || ls_err_msg
,person_id = l_person_id
WHERE last_name = sel.last_name;
END;

UPDATE XXX_HRMIG_AGENCY_PHONE_25JAN07
SET verify_flag = 'Y'
WHERE last_name = sel.last_name;

ELSE
UPDATE XXX_HRMIG_AGENCY_PHONE_25JAN07
SET verify_flag = 'N',err_message = l_err_message || ls_err_msg,
person_id = l_person_id
WHERE last_name = sel.last_name;
END IF;

l_valid := 'Y';
END;
END LOOP;
COMMIT;
END LOOP;
END XXX_Hrmig_Proc_Agency_Phone ;
/