CREATE CUSTOM TABLES
CREATE TABLE CUST_FLEX_VALUES
(
LV_FLEX_VALUE_SET_NAME VARCHAR2(60 BYTE),
LV_FLEX_VALUE VARCHAR2(150 BYTE),
LV_FLEX_VALUE_MEANING VARCHAR2(150 BYTE),
LV_DESCRIPTION VARCHAR2(240 BYTE),
LV_SUMMARY_FLAG VARCHAR2(1 BYTE),
LV_ENABLED_FLAG VARCHAR2(1 BYTE),
LV_FLEX_VALUE_SET_ID NUMBER(10),
LV_FLEX_VALUE_ID NUMBER(15),
LV_PARENT_FLEX_VALUE VARCHAR2(240 BYTE)
);
Control File ...
LOAD DATA
INFILE 'flex_values.csv'
INSERT
INTO TABLE CUST_FLEX_VALUES
REPLACE FIELDS TERMINATED BY ','
TRAILING NULLCOLS
( LV_FLEX_VALUE_SET_NAME "trim(:LV_FLEX_VALUE_SET_NAME)",
LV_FLEX_VALUE "trim(:LV_FLEX_VALUE)",
LV_FLEX_VALUE_MEANING "trim(:LV_FLEX_VALUE_MEANING)",
LV_DESCRIPTION "trim(:LV_DESCRIPTION)",
LV_SUMMARY_FLAG "trim(:LV_SUMMARY_FLAG)",
LV_ENABLED_FLAG "trim(:LV_ENABLED_FLAG)",
LV_FLEX_VALUE_SET_ID constant 1,
LV_FLEX_VALUE_ID constant 1,
LV_PARENT_FLEX_VALUE "trim(:LV_PARENT_FLEX_VALUE)")
FORMAT
Value Set Name | VALUE | TRANSLATED VALUE | Description | Summary Flag | Enabled Flag | Parent Value | TEMP |
FG_INV_SYS_ITEM_EAM | 02/MM/CF-BB.BB4.COND.MONT | 02/MM/CF-BB.BB4.COND.MONT | CONDITION MONITORING | N | Y | AC | 1 |
PROCEDURE TO BE EXECUTED...
AS
CURSOR c_entity
IS
SELECT *
FROM apps.cust_flex_values;
lv_flex_value_set_name VARCHAR2 (60);
lv_flex_value VARCHAR2 (150);
lv_flex_value_meaning VARCHAR2 (150);
lv_description VARCHAR2 (240);
lv_summary_flag VARCHAR2 (1);
lv_enabled_flag VARCHAR2 (1);
lv_flex_value_set_id NUMBER (10);
lv_flex_value_id NUMBER (15);
lv_parent_flex_value VARCHAR2 (240);
BEGIN
DBMS_OUTPUT.put_line ('Start');
OPEN c_entity;
DBMS_OUTPUT.put_line ('Open');
FETCH c_entity
INTO lv_flex_value_set_name, lv_flex_value, lv_flex_value_meaning,
lv_description, lv_summary_flag, lv_enabled_flag,
lv_flex_value_set_id, lv_flex_value_id, lv_parent_flex_value;
LOOP
DBMS_OUTPUT.put_line ('loop');
EXIT WHEN c_entity%NOTFOUND;
SELECT MAX (flex_value_set_id)
INTO lv_flex_value_set_id
FROM applsys.fnd_flex_value_sets
WHERE UPPER (flex_value_set_name) = UPPER (lv_flex_value_set_name);
-- 4648 is user_id , Change it to your own ...
INSERT INTO fnd_flex_values
(flex_value_set_id, flex_value_id,
flex_value, last_update_date, last_updated_by,
creation_date, created_by, last_update_login,
enabled_flag, summary_flag,
parent_flex_value_low
)
VALUES (lv_flex_value_set_id, applsys.fnd_flex_values_s.NEXTVAL,
lv_flex_value, SYSDATE, 4648,
SYSDATE, 4648, 4648,
lv_enabled_flag, NVL (lv_summary_flag, 'N'),
lv_parent_flex_value
);
INSERT INTO fnd_flex_values_tl
(flex_value_id, LANGUAGE, last_update_date,
last_updated_by, creation_date, created_by,
last_update_login, description, source_lang,
flex_value_meaning
)
VALUES (applsys.fnd_flex_values_s.CURRVAL, 'US', SYSDATE,
4648, SYSDATE, 4648,
4648, lv_description, 'US',
lv_flex_value_meaning
);
FETCH c_entity
INTO lv_flex_value_set_name, lv_flex_value, lv_flex_value_meaning,
lv_description, lv_summary_flag, lv_enabled_flag,
lv_flex_value_set_id, lv_flex_value_id, lv_parent_flex_value;
END LOOP;
DBMS_OUTPUT.put_line ('end loop');
CLOSE c_entity;
COMMIT;
END;
/
No comments:
Post a Comment