Create Agency Address

CREATE OR REPLACE PROCEDURE XXX_Hrmig_Proc_Agency_Add AS
l_object_version_number NUMBER(30);
l_address_id NUMBER(30);
l_err_message VARCHAR2(1000);
ls_err_msg VARCHAR2(200);
l_valid VARCHAR2(10) := 'Y';
l_effective_date DATE;
l_address_type VARCHAR2(25);
l_eff_start_date DATE;
l_eff_end_date DATE;
l_rows_processed NUMBER := 0;
l_commit_point NUMBER := 10;
l_employment_category VARCHAR2(30);
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_person_id NUMBER(10);
l_city VARCHAR2(100);
l_state VARCHAR2(50);
L_primary_flag CHAR(5) := 'Y';
CURSOR cur_emp_app1 IS
SELECT *
FROM XXX_HRMIG_AGENT_ADD_29JAN07
WHERE NVL(verify_flag,'N') = 'N';
---AND LAST_NAME= 'Career Growth,Chandigarh';

BEGIN
Hr_General.g_data_migrator_mode := 'Y';
FOR sel IN cur_emp_app1 LOOP
BEGIN
l_err_message := NULL;
ls_err_msg := NULL;

BEGIN

SELECT person_id
INTO l_person_id
FROM per_all_people_f
WHERE UPPER(TRIM(LAST_NAME)) =UPPER( TRIM(sel.LAST_NAME))
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 Agency Name';
l_valid := 'N';
END;

BEGIN
SELECT LOOKUP_CODE
INTO L_ADDRESS_TYPE
FROM FND_LOOKUP_VALUES_VL
WHERE MEANING = 'Present'
AND lookup_type = 'ADDRESS_TYPE';
EXCEPTION
WHEN OTHERS THEN
l_err_message :=l_err_message|| 'InValid address type';
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; 

BEGIN
SELECT effective_end_date
INTO L_eff_end_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 end date';
l_valid := 'N';
END; 

BEGIN
SELECT FLEX_VALUE
INTO l_state
FROM FND_FLEX_VALUES_VL 
WHERE UPPER(description) = UPPER(TRIM(sel.state))
AND FLEX_VALUE_SET_ID = 1011723;
EXCEPTION
WHEN OTHERS THEN
l_err_message :=l_err_message|| 'InValid STATE';
l_valid := 'N';
END;

BEGIN
SELECT FLEX_VALUE
INTO l_city
FROM FND_FLEX_VALUES_VL 
WHERE UPPER(description) = UPPER(TRIM(sel.city))
AND FLEX_VALUE_SET_ID = 1011743
AND PARENT_FLEX_VALUE_LOW = L_STATE;
EXCEPTION
WHEN OTHERS THEN
l_err_message :=l_err_message|| 'InValid CITY ';
l_valid := 'N';
END;


IF (l_valid <> 'N') THEN
BEGIN
Hr_Person_Address_Api.create_person_address
(p_effective_date => '01-JAN-1985'
,p_person_id => l_person_id
,p_primary_flag => 'Y'
,p_style => 'IN'
,p_date_from => l_eff_start_date
--,p_date_to => l_eff_end_date
,p_address_type => l_address_type
,p_address_line1 => TRIM(sel.address_line1)
,p_address_line2 => TRIM(sel.address_line2)
,p_address_line3 => TRIM(sel.address_line3)
,p_postal_code => TRIM(sel.pincode)
,p_country => 'IN'
,p_add_information15 => L_state
,p_add_information18 => l_city
,p_address_id => l_address_id
--,p_addr_attribute11 => TRIM(sel.email_address)
,p_object_version_number => l_object_version_number);

UPDATE XXX_HRMIG_AGENT_ADD_29JAN07
SET VERIFY_FLAG = 'Y'
,PERSON_ID = L_PERSON_ID
WHERE UPPER(TRIM(LAST_NAME)) = UPPER(TRIM(sel.LAST_NAME));

EXCEPTION
WHEN OTHERS THEN
ls_err_msg := SQLERRM;
UPDATE XXX_HRMIG_AGENT_ADD_29JAN07
SET VERIFY_FLAG = 'N'
,PERSON_ID = L_PERSON_ID
,err_message = l_err_message || ls_err_msg
WHERE UPPER(TRIM(LAST_NAME)) = UPPER(TRIM(sel.LAST_NAME));
END;
COMMIT;

ELSE
UPDATE XXX_HRMIG_AGENT_ADD_29JAN07
SET VERIFY_FLAG = 'N'
,PERSON_ID = L_PERSON_ID 
,err_message = l_err_message
WHERE UPPER(TRIM(LAST_NAME)) = UPPER(TRIM(sel.LAST_NAME));
END IF;
l_valid := 'Y';
END;
END LOOP;
COMMIT;
END XXX_Hrmig_Proc_Agency_Add ;
/