Employee As Supplier Migration

Staging Table :-

CREATE TABLE XXX_AP_EMP_SUP_STG
(
VENDOR_NAME VARCHAR2(240),
VENDOR_TYPE VARCHAR2(10),
EMPLOYEE_NAME VARCHAR2(240),
EMPLOYEE_NUMBER NUMBER(10),
VENDOR_SITE VARCHAR2(15),
CITY VARCHAR2(25),
ZIP VARCHAR2(10),
PAYMENT_TERMS VARCHAR2(20),
PAY_GROUP VARCHAR2(10),
LIABILITY_PURCHASE VARCHAR2(40),
PREPAYMENT VARCHAR2(40),
H_VERIFY_FLAG CHAR(1),
L_VERIFY_FLAG CHAR(1 ),
ERROR_MESSAGE VARCHAR2(3000)
)


Run the following script to upload data from staging table to interface table


CREATE OR REPLACE PROCEDURE xxx_EMP_SUP_CREATE_PRC AS

L_ERROR_MESSAGE VARCHAR2(2000);
L_VENDOR_TYPE VARCHAR2(40);
L_TERM_ID NUMBER(10);
L_LOCATION_ID NUMBER(10);
L_VERIFY_FLAG CHAR(1);
L_ORG_ID NUMBER(10);
L_VENDOR_SITE_CODE VARCHAR2(15);
L_PAY_CODE_COMBINATION_ID NUMBER(10);
L_PREPAY_CODE_COMBINATION_ID NUMBER(10);
L_EMP_SUPP_EXIST NUMBER(10);


CURSOR C_EMP IS
SELECT PAPF.PERSON_ID,
TES.VENDOR_TYPE,
TES.EMPLOYEE_NUMBER,
TES.PAYMENT_TERMS,
tES.VENDOR_NAME,
TES.LIABILITY_PURCHASE,
TES.PREPAYMENT,
TES.PAY_GROUP
FROM xxx_AP_EMP_SUP_STG TES,
PER_ALL_PEOPLE_F PAPF
WHERE TO_CHAR(TES.EMPLOYEE_NUMBER) = PAPF.EMPLOYEE_NUMBER
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE
AND NVL(H_VERIFY_FLAG,'N') = 'N';


CURSOR C_EMP_SITE (P_EMPLOYEE_NUMBER VARCHAR2)IS
SELECT *
FROM xxx_AP_EMP_SUP_STG
WHERE EMPLOYEE_NUMBER = P_EMPLOYEE_NUMBER ;

BEGIN

<>
FOR C1 IN C_EMP LOOP

L_VERIFY_FLAG := 'Y' ;
L_ERROR_MESSAGE := NULL;
L_EMP_SUPP_EXIST := NULL;

BEGIN
SELECT ORGANIZATION_ID
INTO L_ORG_ID
FROM HR_OPERATING_UNITS
WHERE NAME = 'xxx Operating Unit';
EXCEPTION
WHEN OTHERS THEN
L_VERIFY_FLAG := 'N';
L_ERROR_MESSAGE := L_ERROR_MESSAGE||'Operating Unit is Invalid';
END;


BEGIN
SELECT LOCATION_ID
INTO L_LOCATION_ID
FROM HR_LOCATIONS
WHERE LOCATION_CODE = 'xxx Main Store Location';
EXCEPTION
WHEN OTHERS THEN
L_VERIFY_FLAG := 'N';
L_ERROR_MESSAGE := L_ERROR_MESSAGE||'Location is Not Valid';
END;


BEGIN
SELECT EMPLOYEE_ID
INTO L_EMP_SUPP_EXIST
FROM PO_VENDORS
WHERE VENDOR_TYPE_LOOKUP_CODE = 'EMPLOYEE'
AND EMPLOYEE_ID = C1.PERSON_ID ;
IF L_EMP_SUPP_EXIST > 0 THEN
L_VERIFY_FLAG := 'N';
L_ERROR_MESSAGE := 'SUPPLIER ALREADY EXISTING FOR THIS EMPLOYEE';
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;


BEGIN
SELECT TERM_ID
INTO L_TERM_ID
FROM AP_TERMS
WHERE UPPER(NAME) = UPPER(TRIM(C1.PAYMENT_TERMS)) ;
EXCEPTION
WHEN OTHERS THEN
L_VERIFY_FLAG := 'N';
L_ERROR_MESSAGE := L_ERROR_MESSAGE||'Payment Term is not valid';
END;


BEGIN
SELECT LOOKUP_CODE
INTO L_VENDOR_TYPE
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE(+) = 'VENDOR TYPE'
AND UPPER(LOOKUP_CODE) = UPPER(TRIM(C1.VENDOR_TYPE));
EXCEPTION
WHEN OTHERS THEN
L_VERIFY_FLAG := 'N';
L_ERROR_MESSAGE := L_ERROR_MESSAGE||'Vendor Type Lookup Code not existing';
END;


BEGIN
SELECT CODE_COMBINATION_ID
INTO L_PAY_CODE_COMBINATION_ID
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT1||'.'||SEGMENT2||'.'||
SEGMENT3||'.'||SEGMENT4||'.'||
SEGMENT5||'.'||SEGMENT6 = C1.LIABILITY_PURCHASE ;
EXCEPTION
WHEN OTHERS THEN
L_VERIFY_FLAG := 'N';
L_ERROR_MESSAGE := L_ERROR_MESSAGE||'Accounts Pay Code Combination is Not Valid';
END;


BEGIN
SELECT CODE_COMBINATION_ID
INTO L_PREPAY_CODE_COMBINATION_ID
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT1||'.'||SEGMENT2||'.'||
SEGMENT3||'.'||SEGMENT4||'.'||
SEGMENT5||'.'||SEGMENT6=C1.PREPAYMENT;
EXCEPTION
WHEN OTHERS THEN
L_VERIFY_FLAG := 'N';
L_ERROR_MESSAGE := L_ERROR_MESSAGE||'Pre-Pay Code Combination is Not Valid';
END;


IF L_VERIFY_FLAG <> 'N' THEN

BEGIN
INSERT INTO AP.AP_SUPPLIERS_INT
(
VENDOR_INTERFACE_ID
,VENDOR_NAME
,VENDOR_NAME_ALT
,VENDOR_TYPE_LOOKUP_CODE
,EMPLOYEE_ID
,TERMS_ID
,BILL_TO_LOCATION_ID
,SHIP_TO_LOCATION_ID
,RECEIVING_ROUTING_ID
,INSPECTION_REQUIRED_FLAG
,RECEIPT_REQUIRED_FLAG
,PAY_GROUP_LOOKUP_CODE
)
VALUES (
AP_SUPPLIERS_INT_S.NEXTVAL
,TRIM(C1.VENDOR_NAME)
,TRIM(C1.VENDOR_NAME)
,L_VENDOR_TYPE
,C1.PERSON_ID
,l_term_id
,l_location_id
,l_location_id
,1
,'N'
,'Y'
,C1.PAY_GROUP 
) ;


UPDATE xxx_AP_EMP_SUP_STG
SET H_VERIFY_FLAG = 'Y'
WHERE EMPLOYEE_NUMBER = C1.EMPLOYEE_NUMBER ;

EXCEPTION

WHEN OTHERS THEN
L_ERROR_MESSAGE := SQLERRM ;
L_VERIFY_FLAG := 'N';
UPDATE xxx_AP_EMP_SUP_STG
SET H_VERIFY_FLAG = 'N',
ERROR_MESSAGE = L_ERROR_MESSAGE
WHERE EMPLOYEE_NUMBER = C1.EMPLOYEE_NUMBER;
GOTO NEXT_SUPP;

END ;


<>
FOR L1 IN C_EMP_SITE(C1.EMPLOYEE_NUMBER)
LOOP

L_VENDOR_SITE_CODE := NULL;



BEGIN
SELECT VENDOR_SITE_CODE
INTO L_VENDOR_SITE_CODE
FROM PO_VENDOR_SITES_ALL A,
PO_VENDORS B
WHERE ORG_ID = L_ORG_ID
AND UPPER(VENDOR_SITE_CODE) = TRIM(UPPER(L1.VENDOR_SITE))
AND A.VENDOR_ID = B.VENDOR_ID
AND UPPER(B.VENDOR_NAME) = TRIM(UPPER(L1.VENDOR_NAME)) ;
IF L_VENDOR_SITE_CODE IS NOT NULL THEN
L_VERIFY_FLAG := 'N';
L_ERROR_MESSAGE := L_ERROR_MESSAGE ||'Vendor Site is already existing';
END IF;
EXCEPTION
WHEN OTHERS THEN
null;
END;


IF L_VERIFY_FLAG <> 'N' then

BEGIN

INSERT INTO AP.AP_SUPPLIER_SITES_INT
(
VENDOR_INTERFACE_ID,
VENDOR_SITE_CODE,
VENDOR_SITE_CODE_ALT,
CITY,
ZIP,
ORG_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE10,
SHIP_TO_LOCATION_ID,
BILL_TO_LOCATION_ID,
HOLD_UNMATCHED_INVOICES_FLAG,
ACCTS_PAY_CODE_COMBINATION_ID,
PREPAY_CODE_COMBINATION_ID 
)
VALUES (
AP_SUPPLIERS_INT_S.CURRVAL,
TRIM(L1.VENDOR_SITE),
TRIM(L1.VENDOR_SITE),
TRIM(L1.CITY),
TRIM(L1.ZIP),
L_ORG_ID,
'Baan Supplier Code',
TRIM(L1.BAAN_CODE),
L_LOCATION_ID,
L_LOCATION_ID,
'N',
L_PAY_CODE_COMBINATION_ID,
L_PREPAY_CODE_COMBINATION_ID
) ;

UPDATE xxx_AP_EMP_SUP_STG
SET L_VERIFY_FLAG = 'Y'
WHERE EMPLOYEE_NUMBER = L1.EMPLOYEE_NUMBER
AND VENDOR_SITE = L1.VENDOR_SITE;

EXCEPTION
WHEN OTHERS THEN

ROLLBACK TO SAVEPOINT A;

L_ERROR_MESSAGE := SQLERRM;

UPDATE xxx_AP_EMP_SUP_STG
SET L_VERIFY_FLAG = 'N',
ERROR_MESSAGE = L_ERROR_MESSAGE
WHERE EMPLOYEE_NUMBER = L1.EMPLOYEE_NUMBER
AND VENDOR_SITE = L1.VENDOR_SITE;

UPDATE xxx_AP_EMP_SUP_STG
SET H_VERIFY_FLAG = 'N'
WHERE EMPLOYEE_NUMBER = L1.EMPLOYEE_NUMBER;

GOTO NEXT_SUPP;

END;


ELSE

ROLLBACK TO SAVEPOINT A;

UPDATE xxx_AP_EMP_SUP_STG
SET L_VERIFY_FLAG = 'N',
ERROR_MESSAGE = L_ERROR_MESSAGE
WHERE EMPLOYEE_NUMBER = L1.EMPLOYEE_NUMBER
AND VENDOR_SITE = L1.VENDOR_SITE;

UPDATE xxx_AP_EMP_SUP_STG
SET H_VERIFY_FLAG = 'N'
WHERE EMPLOYEE_NUMBER = L1.EMPLOYEE_NUMBER;

GOTO NEXT_SUPP;

END IF;

END LOOP SUPP_SITE;


ELSE

UPDATE xxx_AP_EMP_SUP_STG
SET ERROR_MESSAGE = L_ERROR_MESSAGE,
H_VERIFY_FLAG = 'N'
WHERE EMPLOYEE_NUMBER = C1.EMPLOYEE_NUMBER;


END IF;


COMMIT; 
END LOOP supp_head; 

END xxx_EMP_SUP_CREATE_PRC;
/