Create Agency in HRMS

CREATE OR REPLACE PROCEDURE XXX_Hrmig_Proc_Agency AS

LN_ROWS_PROCESSED NUMBER := 0; 
LN_COMMIT_POINT NUMBER := 50; 
LS_ERROR_TEXT VARCHAR2(2000);
L_ERR_MESSAGE VARCHAR2(1000);
L_VERIFY_FLAG VARCHAR2(10):='Y';
LN_PERSON_ID NUMBER;
L_OBJECT_VERSION_NUMBER NUMBER(10); 
L_EFF_START_DATE DATE;
L_EFF_END_DATE DATE;
L_FULL_NAME VARCHAR2(100);
L_COMENT_ID NUMBER(10);
L_NAME_COMBINATION_WARNING BOOLEAN;
L_ORIG_HIRE_WARNING BOOLEAN;
LS_ERR_MSG VARCHAR2(1000);

CURSOR CUR_EXT_APP IS
SELECT *
FROM XXX_HRMIG_AGENCY_PER_25JAN07
WHERE NVL(VERIFY_FLAG,'N') = 'N';

BEGIN
Hr_General.G_DATA_MIGRATOR_MODE := 'Y';

FOR SEL IN CUR_EXT_APP LOOP
BEGIN
L_ERR_MESSAGE := NULL;
LS_ERR_MSG := NULL;

IF(L_VERIFY_FLAG<>'N') THEN
BEGIN

Hr_Contact_Api.CREATE_PERSON
(p_start_date => '01-JAN-1985'
,p_business_group_id => 385
,p_last_name => SEL.LAST_NAME
,p_sex => 'M'
,P_PERSON_TYPE_ID => 183
,p_person_id => LN_PERSON_ID 
,p_object_version_number => L_OBJECT_VERSION_NUMBER 
,p_effective_start_date => L_EFF_START_DATE
,p_effective_end_date => L_EFF_END_DATE
,p_full_name => L_FULL_NAME
,p_comment_id => L_COMENT_ID
,p_name_combination_warning => L_NAME_COMBINATION_WARNING
,p_orig_hire_warning => L_ORIG_HIRE_WARNING
);

UPDATE XXX_HRMIG_AGENCY_PER_25JAN07
SET VERIFY_FLAG = 'Y'
WHERE LAST_NAME = SEL.LAST_NAME;

EXCEPTION
WHEN OTHERS THEN
LS_ERR_MSG := SQLERRM;
UPDATE XXX_HRMIG_AGENCY_PER_25JAN07
SET VERIFY_FLAG = 'N',
ERR_MESSAGE = L_ERR_MESSAGE || LS_ERR_MSG
WHERE PERSON_ID = SEL.PERSON_ID;
END;

ELSE

UPDATE XXX_HRMIG_AGENCY_PER_25JAN07
SET VERIFY_FLAG = 'N',
ERR_MESSAGE = L_ERR_MESSAGE || LS_ERROR_TEXT
WHERE LAST_NAME = SEL.LAST_NAME;

END IF;
L_VERIFY_FLAG:='Y';
END;
END LOOP;
COMMIT;
END Kli_Hrmig_Proc_Agency;
/