Create Contacts

CREATE OR REPLACE PROCEDURE XXX_Hrmig_Proc_Contact_Api AS

LN_ROWS_PROCESSED NUMBER := 0; -- ROWS PROCESSED BY API
LN_COMMIT_POINT NUMBER := 50; -- COMMIT AFTER X SUCCESSFUL ROWS
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);
L_GENDER VARCHAR2(5);
L_TITLE VARCHAR2(10);
LN_BUSINESS_GROUP_ID NUMBER(10);
L_QUALIFICATION VARCHAR2(10);
L_DATE_START DATE;
START_DATE DATE;

CURSOR CUR_EXT_APP IS
SELECT *
FROM XXX_HRMIG_EMP_CONTACT_16MAR07
WHERE NVL(VERIFY_FLAG,'N') = 'N';
--- 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_EXT_APP LOOP
BEGIN
L_ERR_MESSAGE := NULL;
LS_ERR_MSG := NULL;

BEGIN
SELECT LOOKUP_CODE
INTO L_TITLE
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE = 'TITLE'
AND UPPER(MEANING) = UPPER(TRIM(SEL.TITLE));
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
WHEN OTHERS THEN
L_ERR_MESSAGE :=L_ERR_MESSAGE|| 'INVALID TITLE TAGGED';
L_VERIFY_FLAG := 'N';
END;

BEGIN
SELECT DATE_START
INTO L_DATE_START
FROM PER_PERIODS_OF_SERVICE PPOS,
PER_ALL_PEOPLE_F PAPF
WHERE PAPF.PERSON_ID = PPOS.PERSON_ID
AND PAPF.EMPLOYEE_NUMBER = TRIM(SEL.EMPLOYEE_NUMBER)
AND PAPF.BUSINESS_GROUP_ID = 385
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE;
EXCEPTION
WHEN OTHERS THEN
L_ERR_MESSAGE :=L_ERR_MESSAGE|| 'EMPLOYEE NUMBER HAS NOT BEEN CREATED';
L_VERIFY_FLAG := 'N';
END;

BEGIN

SELECT LOOKUP_CODE
INTO L_GENDER
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE = 'SEX'
AND UPPER(MEANING) = UPPER(TRIM(SEL.GENDER));

EXCEPTION

WHEN OTHERS THEN
L_ERR_MESSAGE :=L_ERR_MESSAGE|| 'INVALID GENDER TAGGED';
L_VERIFY_FLAG := 'N';
END; 

IF (SEL.DATE_OF_BIRTH < L_DATE_START) THEN

START_DATE:=L_DATE_START;

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

ELSE START_DATE:=SEL.DATE_OF_BIRTH;

END IF;

/*BEGIN
SELECT FLEX_VALUE
INTO L_QUALIFICATION
FROM FND_FLEX_VALUES_VL
WHERE FLEX_VALUE_SET_ID = 1011580
AND UPPER(DESCRIPTION) = UPPER(TRIM(SEL.QUALIFICATION));

EXCEPTION
-- WHEN NO_DATA_FOUND THEN NULL;
WHEN OTHERS THEN
L_ERR_MESSAGE :=L_ERR_MESSAGE|| 'INVALID QUALIFICATION';
-- L_VERIFY_FLAG := 'N';
END;
*/
IF(L_VERIFY_FLAG<>'N') THEN

BEGIN

Hr_Contact_Api.CREATE_PERSON
(P_START_DATE => START_DATE
,P_BUSINESS_GROUP_ID => 385
,P_LAST_NAME => TRIM(SEL.LAST_NAME)
,P_FIRST_NAME => TRIM(SEL.FIRST_NAME)
,p_middle_names => TRIM(SEL.MIDDLE_NAME)
,P_TITLE => L_TITLE
,P_SEX => NVL(L_GENDER,NULL)
,P_DATE_OF_BIRTH => SEL.DATE_OF_BIRTH
,P_ATTRIBUTE_CATEGORY => 'OTHER'
,P_ATTRIBUTE10 => TRIM(SEL.COMPANY_NAME)
,P_ATTRIBUTE11 => SEL.DESIGNATION
-------,P_ATTRIBUTE18 => SEL.QUALIFICATION--L_QUALIFICATION
,P_PERSON_TYPE_ID => 182
,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_EMP_CONTACT_16MAR07
SET VERIFY_FLAG = 'Y',
CONTACT_PERSON_ID = LN_PERSON_ID
WHERE TRIM(LAST_NAME) = TRIM(SEL.LAST_NAME)
AND NVL(DATE_OF_BIRTH,'31-DEC-4712') = NVL(SEL.DATE_OF_BIRTH,'31-DEC-4712')
AND NVL(FIRST_NAME,'FIRST_NAME') = NVL(SEL.FIRST_NAME,'FIRST_NAME')
AND TRIM(RELATION) = TRIM(SEL.RELATION)
AND TRIM(EMPLOYEE_NUMBER) = TRIM(SEL.EMPLOYEE_NUMBER);

EXCEPTION
WHEN OTHERS THEN
LS_ERR_MSG := SQLERRM;
UPDATE XXX_HRMIG_EMP_CONTACT_16MAR07
SET VERIFY_FLAG = 'N',
ERROR_MESSAGE = L_ERR_MESSAGE || LS_ERR_MSG
WHERE TRIM(LAST_NAME) = TRIM(SEL.LAST_NAME)
AND NVL(DATE_OF_BIRTH,'31-DEC-4712') = NVL(SEL.DATE_OF_BIRTH,'31-DEC-4712')
AND NVL(FIRST_NAME,'FIRST_NAME') = NVL(SEL.FIRST_NAME,'FIRST_NAME')
AND TRIM(RELATION) = TRIM(SEL.RELATION)
AND TRIM(EMPLOYEE_NUMBER) = TRIM(SEL.EMPLOYEE_NUMBER);
END;

ELSE

UPDATE XXX_HRMIG_EMP_CONTACT_16MAR07
SET VERIFY_FLAG = 'N',
ERROR_MESSAGE = L_ERR_MESSAGE || LS_ERROR_TEXT
WHERE TRIM(LAST_NAME) = TRIM(SEL.LAST_NAME)
AND NVL(DATE_OF_BIRTH,'31-DEC-4712') = NVL(SEL.DATE_OF_BIRTH,'31-DEC-4712')
AND NVL(FIRST_NAME,'FIRST_NAME') = NVL(SEL.FIRST_NAME,'FIRST_NAME')
AND TRIM(RELATION) = TRIM(SEL.RELATION)
AND TRIM(EMPLOYEE_NUMBER) = TRIM(SEL.EMPLOYEE_NUMBER);

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