FND API to Delete, Add and Update value set values in Oracle Applications
01. Need to create a table like :
CREATE TABLE APPS.XXMFZ_FND_SEG_VALUE_STG
(
LS_VALUE_SET_NAME VARCHAR2(250 BYTE),
LS_FLEX_VALUE VARCHAR2(250 BYTE),
LS_ENABLE_FLAG CHAR(1 BYTE),
LS_COMPILED_ATTR VARCHAR2(10 BYTE),
START_DATE_ACTIVE DATE,
END_DATE_ACTIVE DATE,
LS_MEANING VARCHAR2(250 BYTE),
LS_DESCRIPTION VARCHAR2(250 BYTE),
VALUE_CATEGORY VARCHAR2(250 BYTE),
PROCESS_FLAG CHAR(1 BYTE),
ERR_MSG VARCHAR2(500 BYTE)
);
02. API :
CREATE OR REPLACE PROCEDURE APPS.xxmfz_load_flex_values(XXflex_value_set_name VARCHAR2) -- Value Set name
IS
l_row_id VARCHAR2 (500) := NULL;
l_flex_value_set_id NUMBER := NULL;
l_flex_value_id NUMBER := NULL;
l_err_msg VARCHAR2 (500) := NULL;
l_db_nls_language VARCHAR2 (500) := NULL;
CURSOR fetch_details
IS
SELECT * FROM XXMFZ_FND_SEG_VALUE_STG where process_flag = 'N';
BEGIN
fnd_global.set_nls (p_nls_language => 'US',
p_nls_date_format => NULL,
p_nls_date_language => NULL,
p_nls_numeric_characters => NULL,
p_nls_sort => NULL,
p_nls_territory => NULL,
p_db_nls_language => l_db_nls_language,
p_db_nls_date_format => l_db_nls_language,
p_db_nls_date_language => l_db_nls_language,
p_db_nls_numeric_characters => l_db_nls_language,
p_db_nls_sort => l_db_nls_language,
p_db_nls_territory => l_db_nls_language,
p_db_nls_charset => l_db_nls_language);
-- Get Value Set ID
SELECT flex_value_set_id
INTO l_flex_value_set_id
FROM apps.fnd_flex_value_sets
WHERE flex_value_set_name = XXflex_value_set_name; --' Change the Name as per Value Set
FOR get_details IN fetch_details
LOOP
-- Get Next Sequence Number
SELECT apps.fnd_flex_values_s.NEXTVAL INTO l_flex_value_id FROM DUAL;
BEGIN
apps.fnd_flex_values_pkg.
insert_row (
x_rowid => l_row_id,
x_flex_value_id => l_flex_value_id,
x_attribute_sort_order => NULL,
x_flex_value_set_id => l_flex_value_set_id,
x_flex_value => get_details.ls_flex_value,
x_enabled_flag => 'Y',
x_summary_flag => 'N',
x_start_date_active => NULL, --TO_DATE ('01-JUL-2019', 'DD-MON-YYYY'),
x_end_date_active => NULL,
x_parent_flex_value_low => NULL,
x_parent_flex_value_high => NULL,
x_structured_hierarchy_level => NULL,
x_hierarchy_level => NULL,
x_compiled_value_attributes => 'Y'||CHR(10)||'Y',
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_flex_value_meaning => get_details.ls_meaning,
x_description => get_details.ls_description,
x_creation_date => SYSDATE,
x_created_by => fnd_global.user_id,
x_last_update_date => SYSDATE,
x_last_updated_by => fnd_global.user_id,
x_last_update_login => fnd_global.user_id);
UPDATE XXMFZ_FND_SEG_VALUE_STG
SET process_flag = 'Y'
WHERE ls_meaning = get_details.ls_meaning;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_err_msg := SQLERRM;
UPDATE XXMFZ_FND_SEG_VALUE_STG
SET process_flag = 'N', err_msg = l_err_msg
WHERE ls_meaning = get_details.ls_meaning;
COMMIT;
END;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
l_err_msg := SQLERRM;
DBMS_OUTPUT.put_line ('Main Exception: ' || l_err_msg);
END;
/
03. call API:
exec apps.xxmfz_load_flex_values('MFZ_BU_VS');
if upload from another Instance, create a DB link and Insert Data to the staging table :
insert into apps.XXMFZ_FND_SEG_VALUE_STG(LS_VALUE_SET_NAME,LS_FLEX_VALUE, LS_ENABLE_FLAG, LS_MEANING, LS_DESCRIPTION, PROCESS_FLAG)
select flex_value_set_name, ffv.FLEX_VALUE, ENABLED_FLAG, FLEX_VALUE_MEANING, ffvt.DESCRIPTION, 'N' pros_flag from
fnd_flex_values@prod ffv, fnd_flex_values_tl@prod ffvt, apps.fnd_flex_value_sets@prod vst
where flex_value_set_name='PRG_FUTURE2_VS' --ffv.flex_value_set_id = 1014876
AND ffv.flex_value_id = ffvt.flex_value_id and ENABLED_FLAG='Y' and ffv.flex_value_set_id=vst.flex_value_set_id
order by ffv.FLEX_VALUE
No comments:
Post a Comment