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 ;
/
Tuesday, October 20, 2009
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;
/
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;
/
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 ;
/
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 ;
/
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 ;
/
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 ;
/
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;
/
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;
/
Create Employee Records
How to create Employee records through "Hr_Employee_Api.CREATE_EMPLOYEE".
Please make the necessary changes according to your requirements.
Please create the structure for the staging table "XXX_HRMIG_EMP_PER_16MAR07".
CREATE OR REPLACE PROCEDURE XXX_Hrmig_Proc_Create_Emp AS
LN_ROWS_PROCESSED NUMBER := 0;
LN_COMMIT_POINT NUMBER := 50;
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_EMP_NO VARCHAR2(10);
L_ERR_MESSAGE VARCHAR2(1000);
LS_ERR_MSG VARCHAR2(1000);
L_TITLE VARCHAR2(10);
L_GENDER VARCHAR2(10);
L_HOUSING_STATUS VARCHAR2(100);
L_DOMICILE VARCHAR2(100);
L_RELIGION VARCHAR2(100);
L_BLOOD_GROUP VARCHAR2(20);
L_RESERVATION_CATEGORY VARCHAR2(30);
L_MARITAL_STATUS VARCHAR2(20);
L_RESIDENCE_ABROAD VARCHAR2(30);
L_VERIFY_FLAG VARCHAR2(10):='Y';
L_ANNIVERSARY_DATE DATE;
L_NATIONALITY VARCHAR2(100);
L_HAVE_A_PAN VARCHAR2(100);
L_RETIREMENT_DATE DATE;
L_PERSON_TYPE_ID NUMBER(10);
CURSOR CUR_EMP_APP IS
SELECT *
FROM XXX_HRMIG_EMP_PER_16MAR07
WHERE NVL(VERIFY_FLAG,'N') = 'N';
-- AND EMPLOYEE_NUMBER = '7103';
BEGIN
Hr_General.G_DATA_MIGRATOR_MODE := 'Y';
SELECT ORGANIZATION_ID
INTO LN_BUSINESS_GROUP_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL
WHERE ORGANIZATION_NAME = 'BUSINESS GROUP NAME';
FOR SEL IN CUR_EMP_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 MEANING = trim(SEL.TITLE);
EXCEPTION
WHEN OTHERS THEN
l_err_message :=l_err_message|| 'InValid Title Tagged';
l_verify_flag := 'N';
END;
BEGIN
SELECT LOOKUP_CODE
INTO L_GENDER
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE = 'SEX'
AND MEANING = trim(SEL.GENDER);
EXCEPTION
WHEN OTHERS THEN
l_err_message :=l_err_message|| 'InValid Gender Tagged';
l_verify_flag := 'N';
END;
BEGIN
SELECT NVL(LOOKUP_CODE,'NOT KNOWN')
INTO L_BLOOD_GROUP
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE (+)= 'BLOOD_TYPE'
AND lookup_code (+)= TRIM(SEL.BLOOD_GROUP);
EXCEPTION
WHEN NO_DATA_FOUND THEN
L_BLOOD_GROUP:='NOT KNOWN';
WHEN OTHERS THEN
L_VERIFY_FLAG:='N';
l_err_message :=l_err_message|| 'InValid Blood Group Tagged';
END;
BEGIN
SELECT LOOKUP_CODE
INTO L_MARITAL_STATUS
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE = 'MAR_STATUS'
AND MEANING = TRIM(SEL.MARITAL_STATUS);
EXCEPTION
WHEN NO_DATA_FOUND THEN
L_MARITAL_STATUS :=NULL;
WHEN OTHERS THEN
l_err_message :=l_err_message|| 'InValid Marital Status Tagged';
l_verify_flag := 'N';
END;
BEGIN
SELECT LOOKUP_CODE
INTO L_NATIONALITY
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE = 'NATIONALITY'
AND MEANING = TRIM(SEL.NATIONALITY);
EXCEPTION
WHEN NO_DATA_FOUND THEN
L_NATIONALITY:=NULL;
WHEN OTHERS THEN
L_VERIFY_FLAG:='N';
l_err_message :=l_err_message|| 'InValid NATIONALITY Tagged';
END;
BEGIN
SELECT FLEX_VALUE
INTO L_DOMICILE
FROM FND_FLEX_VALUES_VL
WHERE FLEX_VALUE_SET_ID = 1011575
AND DESCRIPTION = TRIM(INITCAP(SEL.DOMICILE));
EXCEPTION
WHEN NO_DATA_FOUND THEN
L_DOMICILE:=NULL;
WHEN OTHERS THEN
l_err_message :=l_err_message|| 'InValid domicile Tagged';
L_VERIFY_FLAG:='N';
END;
BEGIN
SELECT FLEX_VALUE
INTO L_RELIGION
FROM FND_FLEX_VALUES_VL
WHERE FLEX_VALUE_SET_ID = 1011578
AND DESCRIPTION = TRIM(INITCAP(SEL.RELIGION));
EXCEPTION
WHEN NO_DATA_FOUND THEN
L_RELIGION:=NULL;
WHEN OTHERS THEN
l_err_message :=l_err_message|| 'InValid Religion Tagged';
l_verify_flag := 'N';
END;
BEGIN
SELECT PERSON_TYPE_ID
INTO L_PERSON_TYPE_ID
FROM per_person_types
WHERE business_group_id = 385
AND user_person_type = trim(SEL.PERSON_TYPE);
EXCEPTION
WHEN OTHERS THEN
L_ERR_MESSAGE := L_ERR_MESSAGE||'Invalide Person Type Tagged';
L_VERIFY_FLAG := 'N';
END;
-- L_RETIREMENT_DATE := LAST_DAY(ADD_MONTHS(SEL.DOB,696));
IF(L_VERIFY_FLAG<>'N') THEN
BEGIN
Hr_Employee_Api.CREATE_EMPLOYEE
(P_HIRE_DATE => TRIM(SEL.DOJ)
,P_BUSINESS_GROUP_ID => LN_BUSINESS_GROUP_ID
,P_LAST_NAME => TRIM(SEL.LAST_NAME)
,P_SEX => L_GENDER
,p_person_type_id => L_PERSON_TYPE_ID
,P_EMPLOYEE_NUMBER => SEL.EMPLOYEE_NUMBER
,P_FIRST_NAME => TRIM(SEL.FIRST_NAME)
,P_MIDDLE_NAMES => TRIM(SEL.MIDDLE_NAME)
,P_DATE_OF_BIRTH => TRIM(SEL.DOB)
,P_TOWN_OF_BIRTH => TRIM(SEL.PLACE_OF_BIRTH)
,P_KNOWN_AS => TRIM(SEL.FIRST_NAME)-----
,P_MARITAL_STATUS => L_MARITAL_STATUS
,P_NATIONALITY => L_NATIONALITY
,P_TITLE => L_TITLE
,P_PER_INFORMATION_CATEGORY => 'IN'
,P_PER_INFORMATION5 => SEL.HAVE_A_PAN
,P_PER_INFORMATION4 => TRIM(SEL.PAN_NO)
-- ,P_PER_INFORMATION7 => 'RO'--L_RESIDENCE_ABROAD
,P_PER_INFORMATION18 => TRIM(SEL.APEX_CODE)
,P_PER_INFORMATION8 => TRIM(SEL.PF_NO)
,P_PER_INFORMATION15 => TRIM(SEL.FPF_NO)
,P_PER_INFORMATION19 => L_DOMICILE
,P_PER_INFORMATION11 => TRIM(SEL.GI_NUMBER)
,P_PER_INFORMATION12 => TRIM(SEL.GRATUITY_NUMBER)
,P_ATTRIBUTE_CATEGORY => 'EMP'
-- ,P_ATTRIBUTE17 => L_RETIREMENT_DATE
,P_ATTRIBUTE19 => TRIM(SEL.HEIGHT)
,P_ATTRIBUTE20 => TRIM(SEL.WEIGHT)
,P_ATTRIBUTE22 => SEL.RESERVATION_CATEGORY
,P_ATTRIBUTE23 => L_RELIGION
,P_BLOOD_TYPE => L_BLOOD_GROUP
,P_EMAIL_ADDRESS => TRIM(SEL.OFFICE_EMAIL_ID)
,P_PERSON_ID => LN_PERSON_ID
,P_ASSIGNMENT_ID => LN_ASSIGNMENT_ID
,P_PER_OBJECT_VERSION_NUMBER => LN_PER_OBJECT_VERSION_NUMBER
,P_ASG_OBJECT_VERSION_NUMBER => LN_ASG_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_PER_COMMENT_ID => LN_PER_COMMENT_ID
,P_ASSIGNMENT_SEQUENCE => LN_ASSIGNMENT_SEQUENCE
,P_ASSIGNMENT_NUMBER => LN_ASSIGNMENT_NUMBER
,P_NAME_COMBINATION_WARNING => LB_NAME_COMBINATION_WARNING
,P_ASSIGN_PAYROLL_WARNING => LB_ASSIGN_PAYROLL_WARNING
,P_ORIG_HIRE_WARNING => LB_ORIG_HIRE_WARNING
);
UPDATE XXX_HRMIG_EMP_PER_16MAR07
SET VERIFY_FLAG = 'Y',
PERSON_ID = LN_PERSON_ID
WHERE EMPLOYEE_NUMBER = SEL.EMPLOYEE_NUMBER;
EXCEPTION
WHEN OTHERS THEN
LS_ERR_MSG := SQLERRM;
UPDATE XXX_HRMIG_EMP_PER_16MAR07
SET VERIFY_FLAG = 'N',
ERR_MESSAGE = L_ERR_MESSAGE || LS_ERR_MSG
WHERE EMPLOYEE_NUMBER = SEL.EMPLOYEE_NUMBER;
END;
ELSE
UPDATE XXX_HRMIG_EMP_PER_16MAR07
SET VERIFY_FLAG = 'N',
ERR_MESSAGE = L_ERR_MESSAGE || LS_ERROR_TEXT
WHERE EMPLOYEE_NUMBER = SEL.EMPLOYEE_NUMBER;
END IF;
L_VERIFY_FLAG:='Y';
END;
END LOOP;
COMMIT;
END XXX_Hrmig_Proc_Create_Emp;
/
Please make the necessary changes according to your requirements.
Please create the structure for the staging table "XXX_HRMIG_EMP_PER_16MAR07".
CREATE OR REPLACE PROCEDURE XXX_Hrmig_Proc_Create_Emp AS
LN_ROWS_PROCESSED NUMBER := 0;
LN_COMMIT_POINT NUMBER := 50;
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_EMP_NO VARCHAR2(10);
L_ERR_MESSAGE VARCHAR2(1000);
LS_ERR_MSG VARCHAR2(1000);
L_TITLE VARCHAR2(10);
L_GENDER VARCHAR2(10);
L_HOUSING_STATUS VARCHAR2(100);
L_DOMICILE VARCHAR2(100);
L_RELIGION VARCHAR2(100);
L_BLOOD_GROUP VARCHAR2(20);
L_RESERVATION_CATEGORY VARCHAR2(30);
L_MARITAL_STATUS VARCHAR2(20);
L_RESIDENCE_ABROAD VARCHAR2(30);
L_VERIFY_FLAG VARCHAR2(10):='Y';
L_ANNIVERSARY_DATE DATE;
L_NATIONALITY VARCHAR2(100);
L_HAVE_A_PAN VARCHAR2(100);
L_RETIREMENT_DATE DATE;
L_PERSON_TYPE_ID NUMBER(10);
CURSOR CUR_EMP_APP IS
SELECT *
FROM XXX_HRMIG_EMP_PER_16MAR07
WHERE NVL(VERIFY_FLAG,'N') = 'N';
-- AND EMPLOYEE_NUMBER = '7103';
BEGIN
Hr_General.G_DATA_MIGRATOR_MODE := 'Y';
SELECT ORGANIZATION_ID
INTO LN_BUSINESS_GROUP_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL
WHERE ORGANIZATION_NAME = 'BUSINESS GROUP NAME';
FOR SEL IN CUR_EMP_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 MEANING = trim(SEL.TITLE);
EXCEPTION
WHEN OTHERS THEN
l_err_message :=l_err_message|| 'InValid Title Tagged';
l_verify_flag := 'N';
END;
BEGIN
SELECT LOOKUP_CODE
INTO L_GENDER
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE = 'SEX'
AND MEANING = trim(SEL.GENDER);
EXCEPTION
WHEN OTHERS THEN
l_err_message :=l_err_message|| 'InValid Gender Tagged';
l_verify_flag := 'N';
END;
BEGIN
SELECT NVL(LOOKUP_CODE,'NOT KNOWN')
INTO L_BLOOD_GROUP
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE (+)= 'BLOOD_TYPE'
AND lookup_code (+)= TRIM(SEL.BLOOD_GROUP);
EXCEPTION
WHEN NO_DATA_FOUND THEN
L_BLOOD_GROUP:='NOT KNOWN';
WHEN OTHERS THEN
L_VERIFY_FLAG:='N';
l_err_message :=l_err_message|| 'InValid Blood Group Tagged';
END;
BEGIN
SELECT LOOKUP_CODE
INTO L_MARITAL_STATUS
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE = 'MAR_STATUS'
AND MEANING = TRIM(SEL.MARITAL_STATUS);
EXCEPTION
WHEN NO_DATA_FOUND THEN
L_MARITAL_STATUS :=NULL;
WHEN OTHERS THEN
l_err_message :=l_err_message|| 'InValid Marital Status Tagged';
l_verify_flag := 'N';
END;
BEGIN
SELECT LOOKUP_CODE
INTO L_NATIONALITY
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE = 'NATIONALITY'
AND MEANING = TRIM(SEL.NATIONALITY);
EXCEPTION
WHEN NO_DATA_FOUND THEN
L_NATIONALITY:=NULL;
WHEN OTHERS THEN
L_VERIFY_FLAG:='N';
l_err_message :=l_err_message|| 'InValid NATIONALITY Tagged';
END;
BEGIN
SELECT FLEX_VALUE
INTO L_DOMICILE
FROM FND_FLEX_VALUES_VL
WHERE FLEX_VALUE_SET_ID = 1011575
AND DESCRIPTION = TRIM(INITCAP(SEL.DOMICILE));
EXCEPTION
WHEN NO_DATA_FOUND THEN
L_DOMICILE:=NULL;
WHEN OTHERS THEN
l_err_message :=l_err_message|| 'InValid domicile Tagged';
L_VERIFY_FLAG:='N';
END;
BEGIN
SELECT FLEX_VALUE
INTO L_RELIGION
FROM FND_FLEX_VALUES_VL
WHERE FLEX_VALUE_SET_ID = 1011578
AND DESCRIPTION = TRIM(INITCAP(SEL.RELIGION));
EXCEPTION
WHEN NO_DATA_FOUND THEN
L_RELIGION:=NULL;
WHEN OTHERS THEN
l_err_message :=l_err_message|| 'InValid Religion Tagged';
l_verify_flag := 'N';
END;
BEGIN
SELECT PERSON_TYPE_ID
INTO L_PERSON_TYPE_ID
FROM per_person_types
WHERE business_group_id = 385
AND user_person_type = trim(SEL.PERSON_TYPE);
EXCEPTION
WHEN OTHERS THEN
L_ERR_MESSAGE := L_ERR_MESSAGE||'Invalide Person Type Tagged';
L_VERIFY_FLAG := 'N';
END;
-- L_RETIREMENT_DATE := LAST_DAY(ADD_MONTHS(SEL.DOB,696));
IF(L_VERIFY_FLAG<>'N') THEN
BEGIN
Hr_Employee_Api.CREATE_EMPLOYEE
(P_HIRE_DATE => TRIM(SEL.DOJ)
,P_BUSINESS_GROUP_ID => LN_BUSINESS_GROUP_ID
,P_LAST_NAME => TRIM(SEL.LAST_NAME)
,P_SEX => L_GENDER
,p_person_type_id => L_PERSON_TYPE_ID
,P_EMPLOYEE_NUMBER => SEL.EMPLOYEE_NUMBER
,P_FIRST_NAME => TRIM(SEL.FIRST_NAME)
,P_MIDDLE_NAMES => TRIM(SEL.MIDDLE_NAME)
,P_DATE_OF_BIRTH => TRIM(SEL.DOB)
,P_TOWN_OF_BIRTH => TRIM(SEL.PLACE_OF_BIRTH)
,P_KNOWN_AS => TRIM(SEL.FIRST_NAME)-----
,P_MARITAL_STATUS => L_MARITAL_STATUS
,P_NATIONALITY => L_NATIONALITY
,P_TITLE => L_TITLE
,P_PER_INFORMATION_CATEGORY => 'IN'
,P_PER_INFORMATION5 => SEL.HAVE_A_PAN
,P_PER_INFORMATION4 => TRIM(SEL.PAN_NO)
-- ,P_PER_INFORMATION7 => 'RO'--L_RESIDENCE_ABROAD
,P_PER_INFORMATION18 => TRIM(SEL.APEX_CODE)
,P_PER_INFORMATION8 => TRIM(SEL.PF_NO)
,P_PER_INFORMATION15 => TRIM(SEL.FPF_NO)
,P_PER_INFORMATION19 => L_DOMICILE
,P_PER_INFORMATION11 => TRIM(SEL.GI_NUMBER)
,P_PER_INFORMATION12 => TRIM(SEL.GRATUITY_NUMBER)
,P_ATTRIBUTE_CATEGORY => 'EMP'
-- ,P_ATTRIBUTE17 => L_RETIREMENT_DATE
,P_ATTRIBUTE19 => TRIM(SEL.HEIGHT)
,P_ATTRIBUTE20 => TRIM(SEL.WEIGHT)
,P_ATTRIBUTE22 => SEL.RESERVATION_CATEGORY
,P_ATTRIBUTE23 => L_RELIGION
,P_BLOOD_TYPE => L_BLOOD_GROUP
,P_EMAIL_ADDRESS => TRIM(SEL.OFFICE_EMAIL_ID)
,P_PERSON_ID => LN_PERSON_ID
,P_ASSIGNMENT_ID => LN_ASSIGNMENT_ID
,P_PER_OBJECT_VERSION_NUMBER => LN_PER_OBJECT_VERSION_NUMBER
,P_ASG_OBJECT_VERSION_NUMBER => LN_ASG_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_PER_COMMENT_ID => LN_PER_COMMENT_ID
,P_ASSIGNMENT_SEQUENCE => LN_ASSIGNMENT_SEQUENCE
,P_ASSIGNMENT_NUMBER => LN_ASSIGNMENT_NUMBER
,P_NAME_COMBINATION_WARNING => LB_NAME_COMBINATION_WARNING
,P_ASSIGN_PAYROLL_WARNING => LB_ASSIGN_PAYROLL_WARNING
,P_ORIG_HIRE_WARNING => LB_ORIG_HIRE_WARNING
);
UPDATE XXX_HRMIG_EMP_PER_16MAR07
SET VERIFY_FLAG = 'Y',
PERSON_ID = LN_PERSON_ID
WHERE EMPLOYEE_NUMBER = SEL.EMPLOYEE_NUMBER;
EXCEPTION
WHEN OTHERS THEN
LS_ERR_MSG := SQLERRM;
UPDATE XXX_HRMIG_EMP_PER_16MAR07
SET VERIFY_FLAG = 'N',
ERR_MESSAGE = L_ERR_MESSAGE || LS_ERR_MSG
WHERE EMPLOYEE_NUMBER = SEL.EMPLOYEE_NUMBER;
END;
ELSE
UPDATE XXX_HRMIG_EMP_PER_16MAR07
SET VERIFY_FLAG = 'N',
ERR_MESSAGE = L_ERR_MESSAGE || LS_ERROR_TEXT
WHERE EMPLOYEE_NUMBER = SEL.EMPLOYEE_NUMBER;
END IF;
L_VERIFY_FLAG:='Y';
END;
END LOOP;
COMMIT;
END XXX_Hrmig_Proc_Create_Emp;
/
Friday, July 25, 2008
Item on hand quantity , Lot and Serial No Interfaces
TO MIGRATE ITEM ON HAND QUANTITY , LOT and SERIAL NO
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Interface Tables are :-
------------------------
mtl_transactions_interface
mtl_transaction_lots_interface
mtl_serial_numbers_interface
How to run the Import Pgm :
-------------------------------
Inventory Super User ->
Oracle Inventory ->
Setup ->
Transactions ->
Interface Manager ->
SubInventory ->
Material Transaction ->
Tool from Menu ->
Launch Manager ->
Process Transaction Interface
-----------------------------------
If error is occurring go to Transactions -> Open Transaction Interface -> Error -> See error
After correcting the error apply the below code
/* update mtl_transactions_interface
set process_flag = 1,
lock_flag = 2,
transaction_mode = 3,
validation_required = null,
error_code = null,
error_explanation = null
where process_flag in (1,3); */
----------------------------------------------------------------
The script to migrate legacy data into interface tables
-----------------------------------------------------
CREATE OR REPLACE procedure xxx_onhand_qty_update
as
x number:=null;
l_err_msg varchar2(2000);
l_row_status varchar2(3);
l_inventory_item_id number:=null;
l_organization_id number;
l_count number:=null;
l_inventory_location_id number := null;
cursor c_onhand is
select *
from xxx_tab_onhand_stg
where nvl(row_status,'N') = 'N'
order by from_serial;
begin
for c_onhand_rec in c_onhand
loop
l_row_status := 'Y';
begin
select organization_id
into l_organization_id
from org_organization_definitions
where organization_code = c_onhand_rec.sub_inventory_code;
exception
when others then
update xxx_tab_onhand_stg
set row_status='N'
,error_message=error_message||'No Organization.'
where item_name = c_onhand_rec.item_name;
l_row_status := 'N';
end;
select count(*) into l_count
from mtl_system_items_b
where upper(segment1) =upper(c_onhand_rec.item_name)
and organization_id =l_organization_id
and inventory_item_status_code='Active';
if l_count=0 then
update xxx_tab_onhand_stg
set row_status='N'
,error_message=error_message||'No item existing in Given organization.'
where item_name = c_onhand_rec.item_name;
l_row_status := 'N';
commit;
elsif l_count>1 then
update xxx_tab_onhand_stg
set row_status='N'
,error_message=error_message||'Multiple items existing Given organization.'
where item_name = c_onhand_rec.item_name;
l_row_status := 'N';
commit;
elsif l_count=1 then
select inventory_item_id into l_inventory_item_id
from mtl_system_items_b
where upper(segment1)=upper(c_onhand_rec.item_name)
and organization_id=l_organization_id
and inventory_item_status_code='Active';
if l_inventory_item_id is not null then
update xxx_tab_onhand_stg
set transaction_uom=(select distinct primary_uom_code
from mtl_system_items_b
where upper(segment1)=upper(c_onhand_rec.item_name))
,inventory_item_id=(select distinct(inventory_item_id)
from mtl_system_items_b
where upper(segment1)=upper(c_onhand_rec.item_name)
and organization_id=l_organization_id)
,dist_account=(select code_combination_id
from gl_code_combinations
where segment4 = 1327108)
,userid= (select user_id
from fnd_user
where user_name = 'XXXX')
,transaction_interface_id=mtl_material_transactions_s.nextval
,transaction_type_id = (select transaction_type_id
from mtl_transaction_types
where transaction_type_name = 'Miscellaneous receipt')
,row_status='Y'
,organization_id = (select organization_id
from org_organization_definitions
where organization_code = c_onhand_rec.sub_inventory_code)
where item_name = c_onhand_rec.item_name;
commit;
else
update xxx_tab_onhand_stg
set row_status='N'
,error_message=error_message||'Inventory Item Id is not Existing'
where item_name = c_onhand_rec.item_name;
l_row_status := 'N';
commit;
end if;
else
update xxx_tab_onhand_stg
set row_status='N'
,error_message=error_message||'Item Name or Organization ID is not Valid'
where item_name = c_onhand_rec.item_name;
l_row_status := 'N';
commit;
end if;
commit;
begin
select inventory_location_id
into l_inventory_location_id
from mtl_item_locations
where segment1 = c_onhand_rec.locator
and subinventory_code = trim(c_onhand_rec.sub_inventory_code);
exception
when others then
update xxx_tab_onhand_stg
set row_status='N'
,error_message=error_message||'Invalid Locator'
where item_name = c_onhand_rec.item_name;
l_row_status := 'N';
end;
if l_row_status = 'Y' then
--On hand quantity Migration
begin
insert into mtl_transactions_interface
(
transaction_type_id
,transaction_uom
,transaction_date
,organization_id
,transaction_quantity
,last_update_date
,last_updated_by
,creation_date
,created_by
,transaction_mode
,process_flag
,source_header_id
,source_line_id
,source_code
,lock_flag
,flow_schedule
,scheduled_flag
,transaction_header_id
,inventory_item_id
,transaction_interface_id
,subinventory_code
,distribution_account_id
,transaction_cost
,locator_id
,transaction_reference
)
select
transaction_type_id
,transaction_uom
,'30-DEC-2007'
,organization_id
,transaction_quantity
,sysdate
,userid
,sysdate
,userid
,3
,1
,transaction_interface_id
,transaction_interface_id
,'inv'
,2
,'Y'
,2
,transaction_interface_id
,inventory_item_id
,transaction_interface_id
,sub_inventory_code
,dist_account
,transaction_cost
, l_inventory_location_id
,'Migration'
from xxx_tab_onhand_stg
where upper(item_name)=upper(c_onhand_rec.item_name)
and organization_id=l_organization_id;
commit;
exception
when others then
l_err_msg := sqlerrm;
update xxx_tab_onhand_stg
set error_message = 'Exception While Inserting to mtl_transactions_interface : '||l_err_msg
,row_status = 'N'
where item_name = c_onhand_rec.item_name;
end;
---- END
-----ON HAND QUANTITY LOTS MIGRATION
begin
insert into mtl_transaction_lots_interface
(
transaction_interface_id
,lot_number
,transaction_quantity
,last_update_date
,last_updated_by
,creation_date
,created_by
,product_code
,last_update_login
,product_transaction_id
,primary_quantity
,lot_expiration_date)
select transaction_interface_id
,lot_number
,transaction_quantity
,sysdate
,userid
,sysdate
,userid
,'inv'
,userid
,transaction_interface_id
,transaction_quantity
, expiry_date
from xxx_tab_onhand_stg
where item_name = c_onhand_rec.item_name;
commit;
exception
when others then
l_err_msg := sqlerrm;
update xxx_tab_onhand_stg
set error_message = 'Exception While Inserting to mtl_transaction_lots_interface : '||l_err_msg
,row_status = 'N'
where item_name = c_onhand_rec.item_name;
end;
--- END
---- SERIAL NUMBER MIGRATION -----------
begin
insert into mtl_serial_numbers_interface
(
transaction_interface_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
fm_serial_number,
to_serial_number,
product_code,
product_transaction_id
)
select
transaction_interface_id,
sysdate,
userid,
sysdate,
userid,
userid,
from_serial,
to_serial,
'inv',
transaction_interface_id
from xxx_tab_onhand_stg
where item_name = c_onhand_rec.item_name;
commit;
exception
when others then
l_err_msg := sqlerrm;
update xxx_tab_onhand_stg
set error_message = 'Exception While Inserting to MTL_SERIAL_NUMBERS_INTERFACE : '||l_err_msg
,row_status = 'N'
where item_name = c_onhand_rec.item_name;
end;
end if;
end loop;
END xxx_onhand_qty_update;
/
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Interface Tables are :-
------------------------
mtl_transactions_interface
mtl_transaction_lots_interface
mtl_serial_numbers_interface
How to run the Import Pgm :
-------------------------------
Inventory Super User ->
Oracle Inventory ->
Setup ->
Transactions ->
Interface Manager ->
SubInventory ->
Material Transaction ->
Tool from Menu ->
Launch Manager ->
Process Transaction Interface
-----------------------------------
If error is occurring go to Transactions -> Open Transaction Interface -> Error -> See error
After correcting the error apply the below code
/* update mtl_transactions_interface
set process_flag = 1,
lock_flag = 2,
transaction_mode = 3,
validation_required = null,
error_code = null,
error_explanation = null
where process_flag in (1,3); */
----------------------------------------------------------------
The script to migrate legacy data into interface tables
-----------------------------------------------------
CREATE OR REPLACE procedure xxx_onhand_qty_update
as
x number:=null;
l_err_msg varchar2(2000);
l_row_status varchar2(3);
l_inventory_item_id number:=null;
l_organization_id number;
l_count number:=null;
l_inventory_location_id number := null;
cursor c_onhand is
select *
from xxx_tab_onhand_stg
where nvl(row_status,'N') = 'N'
order by from_serial;
begin
for c_onhand_rec in c_onhand
loop
l_row_status := 'Y';
begin
select organization_id
into l_organization_id
from org_organization_definitions
where organization_code = c_onhand_rec.sub_inventory_code;
exception
when others then
update xxx_tab_onhand_stg
set row_status='N'
,error_message=error_message||'No Organization.'
where item_name = c_onhand_rec.item_name;
l_row_status := 'N';
end;
select count(*) into l_count
from mtl_system_items_b
where upper(segment1) =upper(c_onhand_rec.item_name)
and organization_id =l_organization_id
and inventory_item_status_code='Active';
if l_count=0 then
update xxx_tab_onhand_stg
set row_status='N'
,error_message=error_message||'No item existing in Given organization.'
where item_name = c_onhand_rec.item_name;
l_row_status := 'N';
commit;
elsif l_count>1 then
update xxx_tab_onhand_stg
set row_status='N'
,error_message=error_message||'Multiple items existing Given organization.'
where item_name = c_onhand_rec.item_name;
l_row_status := 'N';
commit;
elsif l_count=1 then
select inventory_item_id into l_inventory_item_id
from mtl_system_items_b
where upper(segment1)=upper(c_onhand_rec.item_name)
and organization_id=l_organization_id
and inventory_item_status_code='Active';
if l_inventory_item_id is not null then
update xxx_tab_onhand_stg
set transaction_uom=(select distinct primary_uom_code
from mtl_system_items_b
where upper(segment1)=upper(c_onhand_rec.item_name))
,inventory_item_id=(select distinct(inventory_item_id)
from mtl_system_items_b
where upper(segment1)=upper(c_onhand_rec.item_name)
and organization_id=l_organization_id)
,dist_account=(select code_combination_id
from gl_code_combinations
where segment4 = 1327108)
,userid= (select user_id
from fnd_user
where user_name = 'XXXX')
,transaction_interface_id=mtl_material_transactions_s.nextval
,transaction_type_id = (select transaction_type_id
from mtl_transaction_types
where transaction_type_name = 'Miscellaneous receipt')
,row_status='Y'
,organization_id = (select organization_id
from org_organization_definitions
where organization_code = c_onhand_rec.sub_inventory_code)
where item_name = c_onhand_rec.item_name;
commit;
else
update xxx_tab_onhand_stg
set row_status='N'
,error_message=error_message||'Inventory Item Id is not Existing'
where item_name = c_onhand_rec.item_name;
l_row_status := 'N';
commit;
end if;
else
update xxx_tab_onhand_stg
set row_status='N'
,error_message=error_message||'Item Name or Organization ID is not Valid'
where item_name = c_onhand_rec.item_name;
l_row_status := 'N';
commit;
end if;
commit;
begin
select inventory_location_id
into l_inventory_location_id
from mtl_item_locations
where segment1 = c_onhand_rec.locator
and subinventory_code = trim(c_onhand_rec.sub_inventory_code);
exception
when others then
update xxx_tab_onhand_stg
set row_status='N'
,error_message=error_message||'Invalid Locator'
where item_name = c_onhand_rec.item_name;
l_row_status := 'N';
end;
if l_row_status = 'Y' then
--On hand quantity Migration
begin
insert into mtl_transactions_interface
(
transaction_type_id
,transaction_uom
,transaction_date
,organization_id
,transaction_quantity
,last_update_date
,last_updated_by
,creation_date
,created_by
,transaction_mode
,process_flag
,source_header_id
,source_line_id
,source_code
,lock_flag
,flow_schedule
,scheduled_flag
,transaction_header_id
,inventory_item_id
,transaction_interface_id
,subinventory_code
,distribution_account_id
,transaction_cost
,locator_id
,transaction_reference
)
select
transaction_type_id
,transaction_uom
,'30-DEC-2007'
,organization_id
,transaction_quantity
,sysdate
,userid
,sysdate
,userid
,3
,1
,transaction_interface_id
,transaction_interface_id
,'inv'
,2
,'Y'
,2
,transaction_interface_id
,inventory_item_id
,transaction_interface_id
,sub_inventory_code
,dist_account
,transaction_cost
, l_inventory_location_id
,'Migration'
from xxx_tab_onhand_stg
where upper(item_name)=upper(c_onhand_rec.item_name)
and organization_id=l_organization_id;
commit;
exception
when others then
l_err_msg := sqlerrm;
update xxx_tab_onhand_stg
set error_message = 'Exception While Inserting to mtl_transactions_interface : '||l_err_msg
,row_status = 'N'
where item_name = c_onhand_rec.item_name;
end;
---- END
-----ON HAND QUANTITY LOTS MIGRATION
begin
insert into mtl_transaction_lots_interface
(
transaction_interface_id
,lot_number
,transaction_quantity
,last_update_date
,last_updated_by
,creation_date
,created_by
,product_code
,last_update_login
,product_transaction_id
,primary_quantity
,lot_expiration_date)
select transaction_interface_id
,lot_number
,transaction_quantity
,sysdate
,userid
,sysdate
,userid
,'inv'
,userid
,transaction_interface_id
,transaction_quantity
, expiry_date
from xxx_tab_onhand_stg
where item_name = c_onhand_rec.item_name;
commit;
exception
when others then
l_err_msg := sqlerrm;
update xxx_tab_onhand_stg
set error_message = 'Exception While Inserting to mtl_transaction_lots_interface : '||l_err_msg
,row_status = 'N'
where item_name = c_onhand_rec.item_name;
end;
--- END
---- SERIAL NUMBER MIGRATION -----------
begin
insert into mtl_serial_numbers_interface
(
transaction_interface_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
fm_serial_number,
to_serial_number,
product_code,
product_transaction_id
)
select
transaction_interface_id,
sysdate,
userid,
sysdate,
userid,
userid,
from_serial,
to_serial,
'inv',
transaction_interface_id
from xxx_tab_onhand_stg
where item_name = c_onhand_rec.item_name;
commit;
exception
when others then
l_err_msg := sqlerrm;
update xxx_tab_onhand_stg
set error_message = 'Exception While Inserting to MTL_SERIAL_NUMBERS_INTERFACE : '||l_err_msg
,row_status = 'N'
where item_name = c_onhand_rec.item_name;
end;
end if;
end loop;
END xxx_onhand_qty_update;
/
Subscribe to:
Posts (Atom)