Steps by Step to Upload Values in Lookup by Using Package FND_LOOKUP_VALUES_PKG in Oracle Apps EBS R12
Step 1:Create Lookup in Application
Step 2:
-------------********* Create Custom Table for Lookup **************----------------
DROP TABLE TEMP_LOOKUP_T
--- Create Custom Table for Lookup ---
CREATE TABLE TEMP_LOOKUP_T(
LOOKUP_TYPE VARCHAR2 (30 Byte) ,
LANGUAGE VARCHAR2 (30 Byte) ,
LOOKUP_CODE VARCHAR2 (30 Byte) ,
MEANING VARCHAR2 (80 Byte) ,
DESCRIPTION VARCHAR2 (240 Byte) ,
ENABLED_FLAG VARCHAR2 (1 Byte) ,
START_DATE_ACTIVE DATE ,
END_DATE_ACTIVE DATE ,
CREATED_BY NUMBER (15) ,
CREATION_DATE DATE ,
LAST_UPDATED_BY NUMBER (15) ,
LAST_UPDATE_LOGIN NUMBER (15) ,
LAST_UPDATE_DATE DATE ,
SOURCE_LANG VARCHAR2 (4 Byte) ,
SECURITY_GROUP_ID NUMBER (15) ,
VIEW_APPLICATION_ID NUMBER (15) ,
TERRITORY_CODE VARCHAR2 (2 Byte) ,
ATTRIBUTE_CATEGORY VARCHAR2 (30 Byte) ,
ATTRIBUTE1 VARCHAR2 (150 Byte) ,
ATTRIBUTE2 VARCHAR2 (150 Byte) ,
ATTRIBUTE3 VARCHAR2 (150 Byte) ,
ATTRIBUTE4 VARCHAR2 (150 Byte) ,
ATTRIBUTE5 VARCHAR2 (150 Byte) ,
ATTRIBUTE6 VARCHAR2 (150 Byte) ,
ATTRIBUTE7 VARCHAR2 (150 Byte) ,
ATTRIBUTE8 VARCHAR2 (150 Byte) ,
ATTRIBUTE9 VARCHAR2 (150 Byte) ,
ATTRIBUTE10 VARCHAR2 (150 Byte) ,
ATTRIBUTE11 VARCHAR2 (150 Byte) ,
ATTRIBUTE12 VARCHAR2 (150 Byte) ,
ATTRIBUTE13 VARCHAR2 (150 Byte) ,
ATTRIBUTE14 VARCHAR2 (150 Byte) ,
ATTRIBUTE15 VARCHAR2 (150 Byte) ,
TAG VARCHAR2 (150 Byte) ,
LEAF_NODE VARCHAR2 (1 Byte) ,
ERROR_MESSAGE VARCHAR2 (2000 BYTE) ,
STATUS VARCHAR2 (1 BYTE) ,
SLNO NUMBER
);
SELECT * FROM TEMP_LOOKUP_T;
-- Insert Data into Custom Table TEMP_LOOKUP_T --
-- User can do this by using SQLLDR.
-- For Sample data I will take following 3 lookup Values
-------------********* END the Script of Custom Table for Lookup **************----------------
Step 3 : Make a ANOMALOUS PROGRAM
-------------- START ANOMALOUS PROGRAM FOR LOAD THE LOOKUP DATA ---------
DECLARE
L_BUSINESS_GROUP_ID NUMBER := 0;
/* OUT PARAMETERS */
L_STATUS VARCHAR2 (5) := 'True';
L_ERROR_MESSAGE VARCHAR2 (300);
L_VALIDATE_CNT NUMBER;
L_TOTAL_RECORDS NUMBER := 0;
L_SUCCESS_RECORDS NUMBER := 0;
L_FAILURE_RECORDS NUMBER := 0;
X_ROWID VARCHAR2 (2000) := NULL;
V_CODE CHAR (5) := '00000';
/* Get_lookup_details */
/*
CURSOR get_lookup_details
IS
SELECT ltype.application_id,
ltype.customization_level,
ltype.creation_date,
ltype.created_by,
ltype.last_update_date,
ltype.last_updated_by,
ltype.last_update_login,
tl.lookup_type,
tl.security_group_id,
tl.view_application_id,
tl.description,
tl.meaning
FROM fnd_lookup_types_tl tl, fnd_lookup_types ltype
WHERE ltype.lookup_type = 'XX_LOAN_MASTER'
AND ltype.lookup_type = tl.lookup_type;
*/
/* CURSOR TO FETCH TEMP RECORDS */
CURSOR CUR_TEMP_LOOKUP
IS
SELECT *
FROM TEMP_LOOKUP_T T;
BEGIN
DBMS_OUTPUT.PUT_LINE ('#############################################################');
DBMS_OUTPUT.PUT_LINE ('Data Migration Of Lookups :');
DBMS_OUTPUT.PUT_LINE ('#############################################################');
FOR CUR_REC IN CUR_TEMP_LOOKUP
LOOP
BEGIN
L_TOTAL_RECORDS := L_TOTAL_RECORDS + 1;
L_ERROR_MESSAGE := '';
L_STATUS := 'True';
IF (L_STATUS = 'True') THEN
FND_LOOKUP_VALUES_PKG.INSERT_ROW
(X_ROWID => X_ROWID,
X_LOOKUP_TYPE => 'XX_LOAN_MASTER', -- LOOKUP TYPE
X_SECURITY_GROUP_ID => 0, -- SECURITY GROUP ID
X_VIEW_APPLICATION_ID => 260, -- APPLICATION ID (0-AOL)
X_LOOKUP_CODE => CUR_REC.LOOKUP_CODE, --'9471408289001',
X_TAG => CUR_REC.TAG,
X_ATTRIBUTE_CATEGORY => CUR_REC.ATTRIBUTE_CATEGORY, --'LOAN TEMPLATE',
X_ATTRIBUTE1 => CUR_REC.ATTRIBUTE1,
X_ATTRIBUTE2 => CUR_REC.ATTRIBUTE2,
X_ATTRIBUTE3 => CUR_REC.ATTRIBUTE3,
X_ATTRIBUTE4 => CUR_REC.ATTRIBUTE4,
X_ENABLED_FLAG => 'Y',
X_START_DATE_ACTIVE => TO_DATE ('30-SEP-2015','DD-MON-YYYY'),
X_END_DATE_ACTIVE => TO_DATE ('31-JAN-2018','DD-MON-YYYY'),
X_TERRITORY_CODE => NULL,
X_ATTRIBUTE5 => CUR_REC.ATTRIBUTE5,
X_ATTRIBUTE6 => CUR_REC.ATTRIBUTE6,
X_ATTRIBUTE7 => CUR_REC.ATTRIBUTE7,
X_ATTRIBUTE8 => CUR_REC.ATTRIBUTE8,
X_ATTRIBUTE9 => CUR_REC.ATTRIBUTE9,
X_ATTRIBUTE10 => CUR_REC.ATTRIBUTE10,
X_ATTRIBUTE11 => CUR_REC.ATTRIBUTE11,
X_ATTRIBUTE12 => CUR_REC.ATTRIBUTE12,
X_ATTRIBUTE13 => CUR_REC.ATTRIBUTE13,
X_ATTRIBUTE14 => CUR_REC.ATTRIBUTE14,
X_ATTRIBUTE15 => CUR_REC.ATTRIBUTE15,
X_MEANING => CUR_REC.MEANING, --'LTL', --LOOKUP MEANING
X_DESCRIPTION => CUR_REC.DESCRIPTION, --'LONG TERM LOAN',
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => 0,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => 0,
X_LAST_UPDATE_LOGIN => -1
);
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS
THEN
L_FAILURE_RECORDS := L_FAILURE_RECORDS + 1;
L_ERROR_MESSAGE := SUBSTR (SQLERRM, 1, 200);
UPDATE TEMP_LOOKUP_T
SET ERROR_MESSAGE = L_ERROR_MESSAGE,
STATUS = 'E'
WHERE SLNO = CUR_REC.SLNO;
COMMIT;
END;
---- IF Successfull Data Load Then Update the Status 'P' --
UPDATE TEMP_LOOKUP_T
SET ERROR_MESSAGE = L_ERROR_MESSAGE,
STATUS = 'P'
WHERE SLNO = CUR_REC.SLNO;
COMMIT;
END LOOP;
END;
-------------- END ANOMALOUS PROGRAM FOR LOAD THE LOOKUP DATA ---------
No comments:
Post a Comment