Create Contact Relations

CREATE OR REPLACE PROCEDURE XXX_Hrmig_Proc_Contact_Rel AS
LN_ROWS_PROCESSED NUMBER := 0; 
LN_COMMIT_POINT NUMBER := 50; 
LS_FIRST_NAME VARCHAR2(20);
LS_MIDDLE_NAME VARCHAR2(60);
LS_LAST_NAME VARCHAR2(40);
LN_PERSON_ID NUMBER;
LN_ASSIGNMENT_ID NUMBER;
LN_PER_OBJECT_VERSION_NUMBER NUMBER;
LN_ASG_OBJECT_VERSION_NUMBER NUMBER;
LD_PER_EFFECTIVE_START_DATE DATE;
LD_PER_EFFECTIVE_END_DATE DATE;
LS_FULL_NAME VARCHAR2(100);
LN_PER_COMMENT_ID NUMBER;
LN_ASSIGNMENT_SEQUENCE NUMBER;
LN_ASSIGNMENT_NUMBER NUMBER;
LB_NAME_COMBINATION_WARNING BOOLEAN;
LB_ASSIGN_PAYROLL_WARNING BOOLEAN;
LB_ORIG_HIRE_WARNING BOOLEAN;
LS_ERROR_TEXT VARCHAR2(2000);
LN_BUSINESS_GROUP_ID NUMBER;
LN_LOOP_COUNTER NUMBER;
LS_SEX VARCHAR2(30);
LN_CTR_OBJECT_VERSION_NUMBER NUMBER;
LN_CONTACT_RELATIONSHIP_ID NUMBER;
L_DATE_OF_JOINING DATE;
L_PERSON_ID NUMBER(10);
L_VALID VARCHAR2(10) := 'Y';
L_ERR_MESSAGE VARCHAR2(1000);
L_CONTACT_TYPE VARCHAR2(50);
LS_ERR_MSG VARCHAR2(1000);
L_ROWS_PROCESSED NUMBER(10);
L_COMMIT_POINT NUMBER(10);
L_PERSONAL_FLAG VARCHAR2(10);
L_MEDICLAIM_NOMINEE VARCHAR2(2);
L_CONTACT_PERSON_ID NUMBER(10);
START_DATE DATE;
L_GRATUITY_NOMINEE VARCHAR2(3);
L_PF_NOMINEE VARCHAR2(3);

CURSOR CUR_EMP_APP IS
SELECT *
FROM XXX_HRMIG_EMP_CONTACT_16MAR07
WHERE NVL(REL_VERIFY_FLAG,'N') = 'N'
AND VERIFY_FLAG = 'Y';
--AND EMPLOYEE_NUMBER = '7614';

BEGIN
-- HR_GENERAL.G_DATA_MIGRATOR_MODE := 'Y';

SELECT ORGANIZATION_ID
INTO LN_BUSINESS_GROUP_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL
WHERE UPPER(NAME) = 'BUSINESS GROUP';

FOR SEL IN CUR_EMP_APP LOOP

BEGIN

L_ERR_MESSAGE := NULL;
LS_ERR_MSG := NULL;

BEGIN

SELECT HL.LOOKUP_CODE
INTO L_CONTACT_TYPE
FROM HR_LOOKUPS HL
WHERE UPPER(TRIM(HL.MEANING))=UPPER(TRIM(SEL.RELATION))
AND HL.LOOKUP_TYPE = 'CONTACT';

EXCEPTION
WHEN NO_DATA_FOUND THEN
L_ERR_MESSAGE :=L_ERR_MESSAGE|| 'INVALID RELATION TYPE.';
WHEN OTHERS THEN
L_ERR_MESSAGE :=SQLERRM;
L_VALID := 'N';
END;

BEGIN
SELECT PPOS.DATE_START,
PAPF.PERSON_ID
INTO L_DATE_OF_JOINING,
L_PERSON_ID
FROM PER_ALL_PEOPLE_F PAPF,
PER_PERIODS_OF_SERVICE PPOS
WHERE PAPF.PERSON_ID = PPOS.PERSON_ID
AND PAPF.BUSINESS_GROUP_ID = 385
AND PAPF.EMPLOYEE_NUMBER = trim(SEL.EMPLOYEE_NUMBER)
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE;
EXCEPTION
WHEN OTHERS THEN
L_ERR_MESSAGE :=L_ERR_MESSAGE|| 'INVALID EMPLOYEE NUMBER.';
L_VALID := 'N';
END;

IF (UPPER(SEL.MEDICLAIM_NOMINEE)='YES') THEN
L_MEDICLAIM_NOMINEE:='Y';
ELSE L_MEDICLAIM_NOMINEE:='N';
END IF;

IF (UPPER(SEL.PF_NOMINEE)='YES') THEN
L_PF_NOMINEE:='Y';
ELSE L_PF_NOMINEE:='N';
END IF;

IF (UPPER(SEL.GRATUITY_NOMINEE)='YES') THEN
L_GRATUITY_NOMINEE:='Y';
ELSE L_GRATUITY_NOMINEE:='N';
END IF;

IF (UPPER(SEL.RELATION) = 'SPOUSE') THEN
l_PERSONAL_FLAG := 'Y';
ELSE
l_PERSONAL_FLAG := 'N';
END IF;

IF (SEL.DATE_OF_BIRTH < L_DATE_OF_JOINING) THEN

START_DATE:=L_DATE_OF_JOINING;

ELSIF SEL.DATE_OF_BIRTH IS NULL THEN START_DATE:=L_DATE_OF_JOINING;

ELSE START_DATE:=SEL.DATE_OF_BIRTH;

END IF;

IF (L_VALID <> 'N') THEN

BEGIN

Hr_Contact_Rel_Api.CREATE_CONTACT
(P_BUSINESS_GROUP_ID => LN_BUSINESS_GROUP_ID
,P_START_DATE => START_DATE
,P_PERSON_ID => L_PERSON_ID
,P_CONTACT_PERSON_ID => SEL.CONTACT_PERSON_ID
,P_CONT_INFORMATION_CATEGORY => 'IN'
,P_CONT_INFORMATION16 => L_MEDICLAIM_NOMINEE
,P_CONT_INFORMATION11 => L_PF_NOMINEE
,P_CONT_INFORMATION12 => SEL.PF_SHARE
,P_CONT_INFORMATION19 => L_GRATUITY_NOMINEE
,P_CONT_INFORMATION20 => SEL.GRATUITY_SHARE
,P_CONTACT_TYPE => TRIM(L_CONTACT_TYPE)
,P_CONTACT_RELATIONSHIP_ID => LN_CONTACT_RELATIONSHIP_ID
,P_CTR_OBJECT_VERSION_NUMBER => LN_CTR_OBJECT_VERSION_NUMBER
,P_PER_PERSON_ID => LN_PERSON_ID
,P_PER_OBJECT_VERSION_NUMBER => LN_PER_OBJECT_VERSION_NUMBER
,P_PER_EFFECTIVE_START_DATE => LD_PER_EFFECTIVE_START_DATE
,P_PER_EFFECTIVE_END_DATE => LD_PER_EFFECTIVE_END_DATE
,P_FULL_NAME => LS_FULL_NAME
,P_PERSONAL_FLAG => L_PERSONAL_FLAG
,P_PER_COMMENT_ID => LN_PER_COMMENT_ID
,P_NAME_COMBINATION_WARNING => LB_NAME_COMBINATION_WARNING
,P_ORIG_HIRE_WARNING => LB_ORIG_HIRE_WARNING);

UPDATE XXX_HRMIG_EMP_CONTACT_16MAR07
SET REL_VERIFY_FLAG = 'Y',
CONTACT_RELATIONSHIP_ID = LN_CONTACT_RELATIONSHIP_ID
WHERE EMPLOYEE_NUMBER = SEL.EMPLOYEE_NUMBER
--AND FIRST_NAME = SEL.FIRST_NAME --(+)
AND CONTACT_PERSON_ID = SEL.CONTACT_PERSON_ID;

EXCEPTION
WHEN OTHERS THEN
LS_ERR_MSG := SQLERRM;

UPDATE XXX_HRMIG_EMP_CONTACT_16MAR07
SET REL_VERIFY_FLAG = 'N',
REL_ERR_MESSAGE = L_ERR_MESSAGE || LS_ERR_MSG
WHERE EMPLOYEE_NUMBER = SEL.EMPLOYEE_NUMBER
--AND FIRST_NAME = SEL.FIRST_NAME --(+)
AND CONTACT_PERSON_ID = SEL.CONTACT_PERSON_ID;

END;

ELSE
UPDATE XXX_HRMIG_EMP_CONTACT_16MAR07
SET REL_VERIFY_FLAG = 'N',
REL_ERR_MESSAGE = L_ERR_MESSAGE
WHERE EMPLOYEE_NUMBER = SEL.EMPLOYEE_NUMBER
AND CONTACT_PERSON_ID = SEL.CONTACT_PERSON_ID;

END IF;
L_VALID := 'Y';
END;
END LOOP;
END XXX_Hrmig_Proc_Contact_Rel ;
/