How to load Descriptive Flexifield values into base tables if multiple records are present.

By Jag - August 20, 2013
create or replace
 
PROCEDURE XXTCM_ACCOUNT_IMPORT_PROCESS
IS
CURSOR account_segment_cur
IS
SELECT
VALUE,
TRANSLATE,
DESCRIPTION,
DECODE (UPPER(PARENT),'YES','Y','NO','N',NULL,'N',parent) Parent,
ACCOUNT_LEVEL ,
BEGIN_VALUE,
END_VALUE,
decode(UPPER(ALLOW_BUDGET),'YES','Y','NO','N', null,'N') allow_budget,
decode(UPPER(ALLOW_POSTING),'YES','Y','NO','N',null,'N') allow_posting,
ACCOUNT_TYPE,
decode(UPPER(THIRD_CNT_ACC),'YES','Y','NO','N',null,'N') THIRD_CNT_ACC,
DECODE(UPPER(RECONCILE),'YES','Y','NO','N',null,'N') RECONCILE,
FILE_NAME
FROM XXTCM_ACCOUNT_DATA_TMP
WHERE NVL(VALID_FLAG,'N') ='N'
AND FILE_NAME ='Account'
-- AND value='82020114'
order by value
for update;

CURSOR account_parent_cur

IS
SELECT *
FROM XXTCM_ACCOUNT_DATA_TMP
WHERE VALID_FLAG ='I'
AND UPPER(PARENT) = UPPER('Yes')
order by value
for update;
 l_rowid VARCHAR2 (240);
l_total_records NUMBER := 0;
l_sucesses_records NUMBER := 0;
l_error_records NUMBER := 0;
l_attribute_sort_order NUMBER;
l_flex_value_set_id NUMBER;
l_account_type VARCHAR2(20);
l_compiled_value_attributes VARCHAR2(20);
x_storage_value_out NUMBER;
v_msg VARCHAR2(2000);
l_flex_value number;
l_user_id NUMBER :=1111;--fnd_profile.value('user_id');
l_resp_id NUMBER :=20639;--fnd_profile.value('resp_id');
l_appl_id NUMBER :=200;-- fnd_profile.value('application_id');
l_value NUMBER;
l_max_value NUMBER;
l_flex_value_id NUMBER;
BEGIN
fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);
FOR account_segment_rec IN account_segment_cur
LOOP
Begin
SELECT substr(account_segment_rec.account_type,1,1)
into l_account_type
from dual;
begin
l_compiled_value_attributes := account_segment_rec.ALLOW_BUDGET ||CHR (10)||
account_segment_rec.ALLOW_POSTING ||CHR (10)||
l_account_type ||CHR (10)||
account_segment_rec.THIRD_CNT_ACC ||CHR (10)||
account_segment_rec.RECONCILE ;
exception
when others then
dbms_output.put_line('Attribute values are'||sqlerrm);
end;
select fvv.flex_value_id,fvs.flex_value_set_id
into l_flex_value ,l_flex_value_set_id
from fnd_flex_values_vl fvv,fnd_flex_value_sets fvs
where fvv.flex_value_set_id = fvs.flex_value_set_id
and UPPER(fvs.flex_value_set_name) = UPPER('XX_UAT_NATURAL_ACCOUNT')
AND fvv.FLEX_VALUE = account_segment_rec.VALUE;
-- fnd_file.put_line ( fnd_file.LOG, 'Calling Updating package' );
fnd_flex_values_pkg.UPDATE_ROW (
X_FLEX_VALUE_ID => l_flex_value,
X_ATTRIBUTE_SORT_ORDER => l_attribute_sort_order,
X_FLEX_VALUE_SET_ID => l_flex_value_set_id,
X_FLEX_VALUE => account_segment_rec.value,
X_ENABLED_FLAG => 'Y',
X_SUMMARY_FLAG => account_segment_rec.PARENT,
X_START_DATE_ACTIVE => null,
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 => account_segment_rec.ACCOUNT_LEVEL,
X_COMPILED_VALUE_ATTRIBUTES => l_compiled_value_attributes,
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 => account_segment_rec.value,
X_DESCRIPTION => account_segment_rec.DESCRIPTION,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => l_user_id
);
exception
when others Then
SELECT fnd_flex_values_s.NEXTVAL
INTO l_flex_value_id
FROM DUAL;
SELECT flex_value_set_id 
INTO l_flex_value_set_id
FROM fnd_flex_value_sets
WHERE upper(flex_value_set_name) = UPPER('XX_UAT_NATURAL_ACCOUNT');
--fnd_file.put_line ( fnd_file.LOG, 'Calling Insert package' );
fnd_flex_values_pkg.insert_row
(x_rowid => l_rowid,
x_flex_value_id => l_flex_value_id,
x_attribute_sort_order => l_attribute_sort_order,
x_flex_value_set_id => l_flex_value_set_id,
x_flex_value => account_segment_rec.value,
x_enabled_flag => 'Y',
x_summary_flag => account_segment_rec.PARENT,
x_start_date_active => NULL,
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 => account_segment_rec.ACCOUNT_LEVEL,
x_compiled_value_attributes => l_compiled_value_attributes,
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 => account_segment_rec.value,
x_description => account_segment_rec.DESCRIPTION,
x_creation_date => SYSDATE,
x_created_by => l_user_id,
x_last_update_date => SYSDATE,
x_last_updated_by => l_user_id,
x_last_update_login => l_user_id
);
--fnd_file.put_line ( fnd_file.LOG , x_storage_value_out);
end;
update XXTCM_ACCOUNT_DATA_TMP
set VALID_FLAG = 'I'
where current of account_segment_cur;
l_sucesses_records := l_sucesses_records+1;
END LOOP;
FOR account_parent_rec IN account_parent_cur
LOOP
--IF upper(account_parent_rec.PARENT) = upper('YES') THEN
BEGIN
/*SELECT begin_value,end_value
INTO l_value,l_max_value
FROM XXGG_ACCOUNT_STG
WHERE VALUE = account_parent_rec.VALUE; */
FND_FLEX_VAL_API.create_value_hierarchy
(p_flex_value_set_name => 'XX_NATURAL_ACCOUNT',
p_parent_flex_value => account_parent_rec.VALUE,
p_range_attribute => 'C',
p_child_flex_value_low => account_parent_rec.begin_value,
p_child_flex_value_high => account_parent_rec.end_value);
EXCEPTION
when others then
--fnd_file.put_line ( fnd_file.LOG, 'Error Occured while Inserting Parent data Values Set Data for:' || account_parent_rec.VALUE || sqlerrm );
update XXTCM_ACCOUNT_DATA_TMP
set VALID_FLAG = 'E'
where current of account_parent_cur;
end;
update XXTCM_ACCOUNT_DATA_TMP
set VALID_FLAG = 'S'
where current of account_parent_cur;
--END IF;
END LOOP;
dbms_output.put_line ('Total Sucessesful records : '|| l_sucesses_records );
--fnd_file.put_line ( fnd_file.LOG,'Sucessfully Inserted The Values Set Data');
dbms_output.put_line('Sucessfully Inserted The Values Set Data');
EXCEPTION
when others then
v_msg:=fnd_flex_val_api.message;
--fnd_file.put_line ( fnd_file.LOG, 'Error Occured while Inserting The Values Set Data' ||sqlerrm);
dbms_output.put_line( 'Error Occured while Inserting The Values Set Data' ||sqlerrm);
END XXTCM_ACCOUNT_IMPORT_PROCESS;

/
  • Share:

You Might Also Like

0 comments