How to upload flex values in value set in Oracle Applications
CREATE OR REPLACE PROCEDURE APPS.CUST_LOAD_FLEX_VALUES
IS
CURSOR C_COMP
IS
SELECT FLEX_VALUE_SET_NAME, FLEX_VALUE, FLEX_VALUE_MEANING, DESCRIPTION,
ENABLED_FLAG, SUMMARY_FLAG, QUALIFIER1, QUALIFIER2, QUALIFIER3,
HIERARCHY_LEVEL
FROM DEV_SNGPL_ENTITY;
/*
CREATE TABLE DEV_SNGPL_ENTITY
(
FLEX_VALUE_SET_NAME VARCHAR2(60 BYTE),
FLEX_VALUE VARCHAR2(150 BYTE),
FLEX_VALUE_MEANING VARCHAR2(150 BYTE),
DESCRIPTION VARCHAR2(240 BYTE),
ENABLED_FLAG VARCHAR2(1 BYTE),
SUMMARY_FLAG VARCHAR2(1 BYTE),
QUALIFIER1 VARCHAR2(1 BYTE),
QUALIFIER2 VARCHAR2(1 BYTE),
QUALIFIER3 VARCHAR2(1 BYTE),
FINAL_QUALIFIER VARCHAR2(2000 BYTE),
FLEX_VALUE_SET_ID NUMBER(10),
FLEX_VALUE_ID NUMBER(15),
HIERARCHY_LEVEL VARCHAR2(30 BYTE)
)*/
--V_ENABLED_FLAG VARCHAR2 (2) := 'Y';
--V_SUMMARY_FLAG VARCHAR2 (2) := 'Y';
V_START_DATE_ACTIVE DATE := SYSDATE;
V_ERROR_MSG VARCHAR2 (1000) :=
NULL;
V_WHO_TYPE FND_FLEX_LOADER_APIS.WHO_TYPE;
V_REQUEST_ID NUMBER;
V_REC_SUCCESS NUMBER;
V_REC_ERROR NUMBER;
V_REC_CNT NUMBER := 0;
V_USER_ID NUMBER := FND_GLOBAL.USER_ID;
V_LOGIN_ID NUMBER := FND_GLOBAL.LOGIN_ID;
V_REQ_ID NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
lc_c_comp c_comp%ROWTYPE;
BEGIN
OPEN C_COMP;
LOOP
FETCH C_COMP
INTO LC_C_COMP;
EXIT WHEN C_COMP%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Before Begin');
BEGIN
V_WHO_TYPE.CREATED_BY := V_USER_ID;
V_WHO_TYPE.CREATION_DATE := SYSDATE;
V_WHO_TYPE.LAST_UPDATED_BY := V_USER_ID;
V_WHO_TYPE.LAST_UPDATE_DATE := SYSDATE;
V_WHO_TYPE.LAST_UPDATE_LOGIN := V_LOGIN_ID;
DBMS_OUTPUT.PUT_LINE('After
Begin');
FND_FLEX_VALUES_PKG.LOAD_ROW
(X_FLEX_VALUE_SET_NAME => LC_C_COMP.FLEX_VALUE_SET_NAME,
X_PARENT_FLEX_VALUE_LOW => NULL,
X_FLEX_VALUE => LC_C_COMP.FLEX_VALUE,
X_WHO => V_WHO_TYPE,
X_ENABLED_FLAG => LC_C_COMP.ENABLED_FLAG ,
X_SUMMARY_FLAG => LC_C_COMP.SUMMARY_FLAG,
X_START_DATE_ACTIVE => V_START_DATE_ACTIVE,
X_END_DATE_ACTIVE => NULL,
X_PARENT_FLEX_VALUE_HIGH => NULL,
X_STRUCTURED_HIERARCHY_LEVEL => NULL,
X_HIERARCHY_LEVEL => LC_C_COMP.HIERARCHY_LEVEL,
X_COMPILED_VALUE_ATTRIBUTES => NULL,
X_VALUE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_ATTRIBUTE16 => NULL,
X_ATTRIBUTE17 => NULL,
X_ATTRIBUTE18 => NULL,
X_ATTRIBUTE19 => NULL,
X_ATTRIBUTE20 => NULL,
X_ATTRIBUTE21 => NULL,
X_ATTRIBUTE22 => NULL,
X_ATTRIBUTE23 => NULL,
X_ATTRIBUTE24 => NULL,
X_ATTRIBUTE25 => NULL,
X_ATTRIBUTE26 => NULL,
X_ATTRIBUTE27 => NULL,
X_ATTRIBUTE28 => NULL,
X_ATTRIBUTE29 => NULL,
X_ATTRIBUTE30 => NULL,
X_ATTRIBUTE31 => NULL,
X_ATTRIBUTE32 => NULL,
X_ATTRIBUTE33 => NULL,
X_ATTRIBUTE34 => NULL,
X_ATTRIBUTE35 => NULL,
X_ATTRIBUTE36 => NULL,
X_ATTRIBUTE37 => NULL,
X_ATTRIBUTE38 => NULL,
X_ATTRIBUTE39 => NULL,
X_ATTRIBUTE40 => NULL,
X_ATTRIBUTE41 => NULL,
X_ATTRIBUTE42 => NULL,
X_ATTRIBUTE43 => NULL,
X_ATTRIBUTE44 => NULL,
X_ATTRIBUTE45 => NULL,
X_ATTRIBUTE46 => NULL,
X_ATTRIBUTE47 => NULL,
X_ATTRIBUTE48 => NULL,
X_ATTRIBUTE49 => NULL,
X_ATTRIBUTE50 => NULL,
X_ATTRIBUTE_SORT_ORDER => NULL,
X_FLEX_VALUE_MEANING => LC_C_COMP.FLEX_VALUE_MEANING,
X_DESCRIPTION => LC_C_COMP.DESCRIPTION
);
END;
DELETE FROM DEV_SNGPL_ENTITY
WHERE FLEX_VALUE = LC_C_COMP.FLEX_VALUE;
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE
('ERROR IS ' || SUBSTR (SQLERRM, 1, 1000));
END;
/
0 comments: