[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT typ.segment_attribute_type segment_attribute_type,
typ.segment_prompt segment_prompt,
typ.description description,
typ.global_flag global_flag,
typ.required_flag required_flag,
typ.unique_flag unique_flag,
val.attribute_value attribute_value
FROM fnd_segment_attribute_values val,
fnd_segment_attribute_types typ
WHERE val.id_flex_num =p_id_flex_num -- Bug 3813504
AND val.id_flex_code = p_id_flex_code
AND val.id_flex_code = typ.id_flex_code
AND typ.segment_attribute_type = val.segment_attribute_type
AND val.application_id = p_application_id
AND val.application_id = typ.application_id
AND val.application_column_name = p_app_seg_name;
SELECT sequence,
att.application_id,
att.id_flex_code,
att.id_flex_num,
att.attr_segment_name,
att.application_column_name,
val.flex_value_set_name value_set_name,
att.user_column_name,
att.index_flag,
att.form_left_prompt,
att.form_above_prompt,
att.display_size,
att.description,
att.table_id,
att.attribute_num,
att.segment_name,
fdu.default_type ,
fdu.default_value ,
fdu.range_code
FROM fnd_seg_rpt_attributes att,
fnd_flex_value_sets val ,
fnd_descr_flex_column_usages fdu
WHERE segment_name = p_seg_name
AND att.attr_value_set_id = val.flex_value_set_id
AND att.enabled_flag = 'Y'
AND att.id_flex_num = p_coa_id
AND fdu.descriptive_flexfield_name = 'FND_FLEX_VALUES'
AND fdu.descriptive_flex_context_code = p_seg_vset_name
AND fdu.flex_value_set_id = val.flex_value_set_id
AND att.application_column_name = fdu.end_user_column_name
AND fdu.application_id=p_application_id
ORDER BY 1;
SELECT chart_of_accounts_id
INTO p_coa_id
FROM gl_ledgers
WHERE ledger_id = p_ledger_id;
SELECT COUNT(*)
INTO ln_no_of_attributes
FROM fnd_seg_rpt_attributes
WHERE application_id = 101
AND id_flex_code = 'GLAT'
AND id_flex_num = p_coa_id;
debug (l_module_name, 'dynamic_inserts='||lflx_gl_flex.dynamic_inserts);
debug (l_module_name, 'dynamic_insert_flag='||lstr_gl_struc.dynamic_insert_flag);
debug (l_module_name, 'dynamic_inserts='||lflx_glat_flex.dynamic_inserts);
debug (l_module_name, 'dynamic_inserts='||lflx_gl_flex.dynamic_inserts);
dynamic_inserts =>lflx_gl_flex.dynamic_inserts,
allow_id_value_sets =>lflx_gl_flex.allow_id_value_sets,
index_flag =>lflx_gl_flex.index_flag,
concat_seg_len_max =>lflx_gl_flex.concat_seg_len_max,
concat_len_warning =>lflx_gl_flex.concat_len_warning
);
debug (l_module_name, 'dynamic_inserts='||lflx_new_flex.dynamic_inserts);
debug (l_module_name, 'dynamic_inserts='||lflx_glat_flex.dynamic_inserts);
debug (l_module_name, 'dynamic_insert_flag='||lstr_glat_struc.dynamic_insert_flag);
lstr_new_struc.dynamic_insert_flag :=lstr_gl_struc.dynamic_insert_flag;
debug (l_module_name, 'dynamic_insert_flag='||lstr_new_struc.dynamic_insert_flag);
debug (l_module_name, 'dynamic_insert_flag='||lstr_glat_struc.dynamic_insert_flag);
UPDATE fnd_segment_attribute_values sav
SET sav.attribute_value = 'N'
WHERE sav.application_id = 101
AND sav.id_flex_code = 'GLAT'
AND sav.attribute_value = 'Y'
AND sav.segment_attribute_type = cv_del_seg_attr_cur.segment_attribute_type;
debug (l_module_name, 'Updated '||SQL%ROWCOUNT||' rows.');
debug (l_module_name, 'Calling fnd_flex_key_api.delete_flex_qualifier');
ln_func:=fnd_flex_key_api.delete_flex_qualifier
(
flexfield=>lflx_glat_flex,
qualifier_name=>cv_del_seg_attr_cur.segment_attribute_type
);
debug (l_module_name, 'Calling fnd_flex_key_api.delete_structure');
fnd_flex_key_api.delete_structure
(
lflx_glat_flex,
lstr_glat_struc
);
lstr_new_struc.dynamic_insert_flag :=lstr_gl_struc.dynamic_insert_flag;
SELECT id_flex_num
FROM fnd_id_flex_structures_vl
WHERE application_id = 101
AND id_flex_code = 'GLAT'
AND id_flex_num <> p_coa_id;
lc_select_stmt varchar2(2000);
ln_last_updated_by number;
SELECT chart_of_accounts_id
INTO ln_p_coa_id
FROM gl_ledgers
WHERE ledger_id = p_ledger_id;
ln_last_updated_by := to_number(ln_userid);
lc_select_stmt:=
'SELECT nvl(attr.flex_value_set_id,0),
attr.attribute_num,
attr.table_id,
attr.application_column_name,
attr.segment_name,
attr.segment_num,
valset.validation_type,
attr.attr_segment_name
FROM fnd_seg_rpt_attributes attr,
fnd_flex_value_sets valset
WHERE valset.flex_value_set_id = attr.flex_value_set_id
AND attr.id_flex_num = :ln_p_coa_id ';
lc_select_stmt:=
'SELECT nvl(attr.flex_value_set_id,0),
attr.attribute_num,
attr.table_id,
attr.application_column_name,
attr.segment_name,
attr.segment_num,
valset.validation_type
FROM fnd_seg_rpt_attributes attr,
fnd_flex_value_sets valset
WHERE valset.flex_value_set_id = attr.flex_value_set_id
AND attr.id_flex_num = :ln_p_coa_id '||
'AND attr.segment_name = :lc_p_segment_name '||
'AND attr.attr_segment_name = :lc_p_denorm_seg ';
DBMS_SQL.PARSE(li_cursor_id,lc_select_stmt,DBMS_SQL.v7);
lc_sql_stmt:='UPDATE gl_code_combinations glcc SET ';
lc_sql_stmt :=lc_sql_stmt||'=(SELECT '|| lc_attribute_num||
' FROM fnd_flex_values ffval'||
' WHERE flex_value_set_id = ' ||ln_flex_value_set_id||
' AND ffval.flex_value = glcc.'||lc_segment_name;
SELECT application_column_name
INTO lc_parent_seg_name
FROM fnd_id_flex_segments
WHERE id_flex_code = 'GLAT'
AND flex_value_set_id =
(SELECT parent_flex_value_set_id
FROM fnd_flex_value_sets
WHERE flex_value_set_id = ln_flex_value_set_id);
SELECT user_table_name
INTO lc_val_table_name
FROM fnd_tables
WHERE application_id = 101
AND table_id = ln_table_id;
SELECT value_column_name
INTO lc_seg_column_val_name
FROM fnd_flex_validation_tables
WHERE flex_value_set_id = ln_flex_value_set_id;
lc_sql_stmt :=lc_sql_stmt||' = (SELECT '|| lc_attr_seg_name||
' FROM '|| lc_val_table_name||
' VAL WHERE VAL.'|| lc_seg_column_val_name||
'= glcc.'|| lc_segment_name;
lc_sql_stmt := lc_sql_stmt|| 'LAST_UPDATE_DATE = sysdate,'||
'LAST_UPDATED_BY = '||ln_last_updated_by||
' WHERE CHART_OF_ACCOUNTS_ID = :ln_p_coa_id ';