DECLARE
TYPE global_column_rec_type IS RECORD(
view_column_name VARCHAR2(40),
application_column_name VARCHAR2(50),
global_application_column_name VARCHAR2(50));
TYPE t_global_column_tab IS TABLE OF global_column_rec_type
INDEX BY BINARY_INTEGER;
TYPE column_rec_type IS RECORD(
view_column_name VARCHAR2(40));
TYPE t_column_tab IS TABLE OF column_rec_type
INDEX BY BINARY_INTEGER;
TYPE attribute_rec_type IS RECORD(
application_column_name VARCHAR2(50),
descriptive_flex_context_code VARCHAR2(30));
TYPE t_attribute_tab IS TABLE OF attribute_rec_type
INDEX BY BINARY_INTEGER;
TYPE context_rec_type IS RECORD(
descriptive_flex_context_code VARCHAR2(30));
TYPE t_context_tab IS TABLE OF context_rec_type
INDEX BY BINARY_INTEGER;
TYPE con_attr_rec_type IS RECORD(
application_column_name VARCHAR2(30));
TYPE t_con_attr_tab IS TABLE OF con_attr_rec_type
INDEX BY BINARY_INTEGER;
p_application VARCHAR2(3) := 'ONT';
p_flex_field_name VARCHAR2(50) := 'OE_LINE_ATTRIBUTES';
l_table_name VARCHAR2(30);
l_view_name VARCHAR2(30);
l_context_name VARCHAR2(30);
l_context_prompt VARCHAR2(50);
l_global_column_tab t_global_column_tab;
l_column_tab t_column_tab;
l_attribute_tab t_attribute_tab;
l_context_tab t_context_tab;
l_con_attr_tab t_con_attr_tab;
l_view_header CLOB;
l_view_line CLOB;
l_view CLOB;
l_attribute CLOB;
l_context CLOB;
CURSOR cur_global_column
IS
SELECT DISTINCT UPPER(regexp_replace(end_user_column_name,'[^[:alnum:]""]','_')) view_column_name,
DECODE(fs.format_type,'N','FND_NUMBER.CANONICAL_TO_NUMBER('||fcu.application_column_name||')',
'X','TO_DATE('||fcu.application_column_name||', ''YYYY/MM/DD HH24:MI:SS'')',
'Y','TO_DATE('||fcu.application_column_name||', ''YYYY/MM/DD HH24:MI:SS'')',
'I','TO_DATE('||fcu.application_column_name||', ''YYYY/MM/DD HH24:MI:SS'')',
fcu.application_column_name) application_column_name,
fcu.application_column_name global_application_column_name
FROM fnd_descr_flex_column_usages fcu,
fnd_descr_flex_contexts fc,
fnd_flex_value_sets fs,
fnd_application fa
WHERE fcu.descriptive_flex_context_code = fc.descriptive_flex_context_code
AND fcu.descriptive_flexfield_name = fc.descriptive_flexfield_name
AND fcu.flex_value_set_id = fs.flex_value_set_id(+)
AND fcu.descriptive_flexfield_name = p_flex_field_name
AND fcu.application_id = fa.application_id
AND fcu.descriptive_flex_context_code = 'Global Data Elements'
AND fa.application_short_name = p_application
AND fc.enabled_flag = 'Y'
AND fcu.enabled_flag = 'Y'
ORDER BY fcu.application_column_name;
CURSOR cur_column
IS
SELECT DISTINCT UPPER(regexp_replace(end_user_column_name,'[^[:alnum:]""]','_')) view_column_name
FROM fnd_descr_flex_column_usages fcu,
fnd_descr_flex_contexts fc,
fnd_application fa
WHERE fcu.descriptive_flex_context_code = fc.descriptive_flex_context_code
AND fcu.descriptive_flexfield_name = fc.descriptive_flexfield_name
AND fcu.descriptive_flexfield_name = p_flex_field_name
AND fcu.application_id = fa.application_id
AND fa.application_short_name = p_application
AND fcu.descriptive_flex_context_code <> 'Global Data Elements'
AND fc.enabled_flag = 'Y'
AND fcu.enabled_flag = 'Y'
ORDER BY UPPER(regexp_replace(end_user_column_name,'[^[:alnum:]""]','_'));
CURSOR cur_attribute (p_end_user_column_name IN VARCHAR2)
IS
SELECT DECODE(fs.format_type,'N','FND_NUMBER.CANONICAL_TO_NUMBER('||fcu.application_column_name||')',
'X','TO_DATE('||fcu.application_column_name||', ''YYYY/MM/DD HH24:MI:SS'')',
'Y','TO_DATE('||fcu.application_column_name||', ''YYYY/MM/DD HH24:MI:SS'')',
'I','TO_DATE('||fcu.application_column_name||', ''YYYY/MM/DD HH24:MI:SS'')',
fcu.application_column_name) application_column_name,
fcu.descriptive_flex_context_code
FROM fnd_descr_flex_column_usages fcu,
fnd_descr_flex_contexts fc,
fnd_flex_value_sets fs,
fnd_application fa
WHERE fcu.descriptive_flex_context_code = fc.descriptive_flex_context_code
AND fcu.descriptive_flexfield_name = fc.descriptive_flexfield_name
AND fcu.flex_value_set_id = fs.flex_value_set_id(+)
AND fcu.descriptive_flexfield_name = p_flex_field_name
AND fcu.application_id = fa.application_id
AND fa.application_short_name = p_application
AND fcu.enabled_flag = 'Y'
AND fc.enabled_flag = 'Y'
AND UPPER(regexp_replace(fcu.end_user_column_name,'[^[:alnum:]""]','_')) = p_end_user_column_name
ORDER BY fcu.application_column_name,
fcu.descriptive_flex_context_code;
CURSOR cur_context
IS
SELECT descriptive_flex_context_code
FROM fnd_descr_flex_contexts fc,
fnd_application fa
WHERE descriptive_flexfield_name = p_flex_field_name
AND fc.application_id = fa.application_id
AND fa.application_short_name = p_application
AND descriptive_flex_context_code <> 'Global Data Elements'
AND enabled_flag = 'Y'
ORDER BY descriptive_flex_context_code;
CURSOR cur_con_attr (p_dff_context_code IN VARCHAR2)
IS
SELECT application_column_name
FROM fnd_descr_flex_column_usages fcu,
fnd_application fa
WHERE descriptive_flexfield_name = p_flex_field_name
AND fcu.application_id = fa.application_id
AND fa.application_short_name = p_application
AND enabled_flag = 'Y'
AND descriptive_flex_context_code = p_dff_context_code
ORDER BY to_number(SUBSTR(application_column_name,10));
CURSOR cur_table_name
IS
SELECT fd.application_table_name,
fd.concatenated_segs_view_name,
fd.context_column_name,
UPPER(regexp_replace(fdt.form_context_prompt,'[^[:alnum:]""]','_')) form_context_prompt
FROM fnd_descriptive_flexs fd,
FND_DESCRIPTIVE_FLEXS_TL fdt,
fnd_application fa
WHERE fd.descriptive_flexfield_name = fdt.descriptive_flexfield_name
AND fd.application_id = fdt.application_id
AND fdt.language = userenv('LANG')
AND fd.descriptive_flexfield_name = p_flex_field_name
AND fd.application_id = fa.application_id
AND fa.application_short_name = p_application;
BEGIN
OPEN cur_table_name;
FETCH cur_table_name
INTO l_table_name,
l_view_name,
l_context_name,
l_context_prompt;
CLOSE cur_table_name;
OPEN cur_global_column;
FETCH cur_global_column BULK COLLECT INTO l_global_column_tab;
CLOSE cur_global_column;
OPEN cur_column;
FETCH cur_column BULK COLLECT INTO l_column_tab;
CLOSE cur_column;
OPEN cur_context;
FETCH cur_context BULK COLLECT INTO l_context_tab;
CLOSE cur_context;
l_view_header := 'CREATE OR REPLACE FORCE VIEW '||l_view_name||' (ROW_ID, '||l_context_prompt;
l_view_line := 'SELECT ROWID, '||l_context_name||', ';
IF (l_global_column_tab.COUNT > 0 )THEN
FOR m IN l_global_column_tab.FIRST .. l_global_column_tab.LAST
LOOP
l_view_header := l_view_header||', '||l_global_column_tab(m).view_column_name;
l_view_line := l_view_line||l_global_column_tab(m).application_column_name||', ';
l_context := l_context || l_global_column_tab(m).global_application_column_name ||'|| ''.'' ||';
END LOOP;
END IF;
l_context := l_context||'(DECODE ('||l_context_name||', ';
IF (l_column_tab.COUNT > 0) THEN
FOR i IN l_column_tab.FIRST .. l_column_tab.LAST
LOOP
l_view_header := l_view_header||', '||l_column_tab(i).view_column_name;
l_view_line := l_view_line||'(DECODE ('||l_context_name||', ';
OPEN cur_attribute(l_column_tab(i).view_column_name);
FETCH cur_attribute BULK COLLECT INTO l_attribute_tab;
CLOSE cur_attribute;
IF (l_attribute_tab.COUNT > 0) THEN
FOR j IN l_attribute_tab.FIRST .. l_attribute_tab.LAST
LOOP
l_view_line := l_view_line||''''|| l_attribute_tab(j).descriptive_flex_context_code ||''''||', '|| l_attribute_tab(j).application_column_name||','||CHR(10);
END LOOP;
END IF;
l_view_line := l_view_line||'NULL )),'||CHR(10);
END LOOP;
END IF;
IF (l_context_tab.COUNT > 0) THEN
FOR k IN l_context_tab.FIRST .. l_context_tab.LAST
LOOP
OPEN cur_con_attr(l_context_tab(k).descriptive_flex_context_code);
FETCH cur_con_attr BULK COLLECT INTO l_con_attr_tab;
CLOSE cur_con_attr;
IF (l_con_attr_tab.COUNT > 0) THEN
FOR l IN l_con_attr_tab.FIRST .. l_con_attr_tab.LAST
LOOP
l_attribute := l_attribute || '|| ''.'' ||' ||l_con_attr_tab(l).application_column_name;
END LOOP;
END IF;
l_context := l_context||''''||l_context_tab(k).descriptive_flex_context_code||''''||', '||l_context_name||''||l_attribute||','||CHR(10);
l_attribute := NULL;
END LOOP;
END IF;
l_context := l_context||'NULL ))';
l_view := l_view_header||', CONCATENATED_SEGMENTS ) AS'||CHR(10)||l_view_line||l_context||CHR(10)|| 'FROM '||l_table_name;
BEGIN
EXECUTE IMMEDIATE l_view;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Error while compiling the view: '||SQLERRM);
retcode := 2;
END;
EXCEPTION
WHEN OTHERS THEN
errbuf := SQLERRM;
retcode := 2;
END;
TYPE global_column_rec_type IS RECORD(
view_column_name VARCHAR2(40),
application_column_name VARCHAR2(50),
global_application_column_name VARCHAR2(50));
TYPE t_global_column_tab IS TABLE OF global_column_rec_type
INDEX BY BINARY_INTEGER;
TYPE column_rec_type IS RECORD(
view_column_name VARCHAR2(40));
TYPE t_column_tab IS TABLE OF column_rec_type
INDEX BY BINARY_INTEGER;
TYPE attribute_rec_type IS RECORD(
application_column_name VARCHAR2(50),
descriptive_flex_context_code VARCHAR2(30));
TYPE t_attribute_tab IS TABLE OF attribute_rec_type
INDEX BY BINARY_INTEGER;
TYPE context_rec_type IS RECORD(
descriptive_flex_context_code VARCHAR2(30));
TYPE t_context_tab IS TABLE OF context_rec_type
INDEX BY BINARY_INTEGER;
TYPE con_attr_rec_type IS RECORD(
application_column_name VARCHAR2(30));
TYPE t_con_attr_tab IS TABLE OF con_attr_rec_type
INDEX BY BINARY_INTEGER;
p_application VARCHAR2(3) := 'ONT';
p_flex_field_name VARCHAR2(50) := 'OE_LINE_ATTRIBUTES';
l_table_name VARCHAR2(30);
l_view_name VARCHAR2(30);
l_context_name VARCHAR2(30);
l_context_prompt VARCHAR2(50);
l_global_column_tab t_global_column_tab;
l_column_tab t_column_tab;
l_attribute_tab t_attribute_tab;
l_context_tab t_context_tab;
l_con_attr_tab t_con_attr_tab;
l_view_header CLOB;
l_view_line CLOB;
l_view CLOB;
l_attribute CLOB;
l_context CLOB;
CURSOR cur_global_column
IS
SELECT DISTINCT UPPER(regexp_replace(end_user_column_name,'[^[:alnum:]""]','_')) view_column_name,
DECODE(fs.format_type,'N','FND_NUMBER.CANONICAL_TO_NUMBER('||fcu.application_column_name||')',
'X','TO_DATE('||fcu.application_column_name||', ''YYYY/MM/DD HH24:MI:SS'')',
'Y','TO_DATE('||fcu.application_column_name||', ''YYYY/MM/DD HH24:MI:SS'')',
'I','TO_DATE('||fcu.application_column_name||', ''YYYY/MM/DD HH24:MI:SS'')',
fcu.application_column_name) application_column_name,
fcu.application_column_name global_application_column_name
FROM fnd_descr_flex_column_usages fcu,
fnd_descr_flex_contexts fc,
fnd_flex_value_sets fs,
fnd_application fa
WHERE fcu.descriptive_flex_context_code = fc.descriptive_flex_context_code
AND fcu.descriptive_flexfield_name = fc.descriptive_flexfield_name
AND fcu.flex_value_set_id = fs.flex_value_set_id(+)
AND fcu.descriptive_flexfield_name = p_flex_field_name
AND fcu.application_id = fa.application_id
AND fcu.descriptive_flex_context_code = 'Global Data Elements'
AND fa.application_short_name = p_application
AND fc.enabled_flag = 'Y'
AND fcu.enabled_flag = 'Y'
ORDER BY fcu.application_column_name;
CURSOR cur_column
IS
SELECT DISTINCT UPPER(regexp_replace(end_user_column_name,'[^[:alnum:]""]','_')) view_column_name
FROM fnd_descr_flex_column_usages fcu,
fnd_descr_flex_contexts fc,
fnd_application fa
WHERE fcu.descriptive_flex_context_code = fc.descriptive_flex_context_code
AND fcu.descriptive_flexfield_name = fc.descriptive_flexfield_name
AND fcu.descriptive_flexfield_name = p_flex_field_name
AND fcu.application_id = fa.application_id
AND fa.application_short_name = p_application
AND fcu.descriptive_flex_context_code <> 'Global Data Elements'
AND fc.enabled_flag = 'Y'
AND fcu.enabled_flag = 'Y'
ORDER BY UPPER(regexp_replace(end_user_column_name,'[^[:alnum:]""]','_'));
CURSOR cur_attribute (p_end_user_column_name IN VARCHAR2)
IS
SELECT DECODE(fs.format_type,'N','FND_NUMBER.CANONICAL_TO_NUMBER('||fcu.application_column_name||')',
'X','TO_DATE('||fcu.application_column_name||', ''YYYY/MM/DD HH24:MI:SS'')',
'Y','TO_DATE('||fcu.application_column_name||', ''YYYY/MM/DD HH24:MI:SS'')',
'I','TO_DATE('||fcu.application_column_name||', ''YYYY/MM/DD HH24:MI:SS'')',
fcu.application_column_name) application_column_name,
fcu.descriptive_flex_context_code
FROM fnd_descr_flex_column_usages fcu,
fnd_descr_flex_contexts fc,
fnd_flex_value_sets fs,
fnd_application fa
WHERE fcu.descriptive_flex_context_code = fc.descriptive_flex_context_code
AND fcu.descriptive_flexfield_name = fc.descriptive_flexfield_name
AND fcu.flex_value_set_id = fs.flex_value_set_id(+)
AND fcu.descriptive_flexfield_name = p_flex_field_name
AND fcu.application_id = fa.application_id
AND fa.application_short_name = p_application
AND fcu.enabled_flag = 'Y'
AND fc.enabled_flag = 'Y'
AND UPPER(regexp_replace(fcu.end_user_column_name,'[^[:alnum:]""]','_')) = p_end_user_column_name
ORDER BY fcu.application_column_name,
fcu.descriptive_flex_context_code;
CURSOR cur_context
IS
SELECT descriptive_flex_context_code
FROM fnd_descr_flex_contexts fc,
fnd_application fa
WHERE descriptive_flexfield_name = p_flex_field_name
AND fc.application_id = fa.application_id
AND fa.application_short_name = p_application
AND descriptive_flex_context_code <> 'Global Data Elements'
AND enabled_flag = 'Y'
ORDER BY descriptive_flex_context_code;
CURSOR cur_con_attr (p_dff_context_code IN VARCHAR2)
IS
SELECT application_column_name
FROM fnd_descr_flex_column_usages fcu,
fnd_application fa
WHERE descriptive_flexfield_name = p_flex_field_name
AND fcu.application_id = fa.application_id
AND fa.application_short_name = p_application
AND enabled_flag = 'Y'
AND descriptive_flex_context_code = p_dff_context_code
ORDER BY to_number(SUBSTR(application_column_name,10));
CURSOR cur_table_name
IS
SELECT fd.application_table_name,
fd.concatenated_segs_view_name,
fd.context_column_name,
UPPER(regexp_replace(fdt.form_context_prompt,'[^[:alnum:]""]','_')) form_context_prompt
FROM fnd_descriptive_flexs fd,
FND_DESCRIPTIVE_FLEXS_TL fdt,
fnd_application fa
WHERE fd.descriptive_flexfield_name = fdt.descriptive_flexfield_name
AND fd.application_id = fdt.application_id
AND fdt.language = userenv('LANG')
AND fd.descriptive_flexfield_name = p_flex_field_name
AND fd.application_id = fa.application_id
AND fa.application_short_name = p_application;
BEGIN
OPEN cur_table_name;
FETCH cur_table_name
INTO l_table_name,
l_view_name,
l_context_name,
l_context_prompt;
CLOSE cur_table_name;
OPEN cur_global_column;
FETCH cur_global_column BULK COLLECT INTO l_global_column_tab;
CLOSE cur_global_column;
OPEN cur_column;
FETCH cur_column BULK COLLECT INTO l_column_tab;
CLOSE cur_column;
OPEN cur_context;
FETCH cur_context BULK COLLECT INTO l_context_tab;
CLOSE cur_context;
l_view_header := 'CREATE OR REPLACE FORCE VIEW '||l_view_name||' (ROW_ID, '||l_context_prompt;
l_view_line := 'SELECT ROWID, '||l_context_name||', ';
IF (l_global_column_tab.COUNT > 0 )THEN
FOR m IN l_global_column_tab.FIRST .. l_global_column_tab.LAST
LOOP
l_view_header := l_view_header||', '||l_global_column_tab(m).view_column_name;
l_view_line := l_view_line||l_global_column_tab(m).application_column_name||', ';
l_context := l_context || l_global_column_tab(m).global_application_column_name ||'|| ''.'' ||';
END LOOP;
END IF;
l_context := l_context||'(DECODE ('||l_context_name||', ';
IF (l_column_tab.COUNT > 0) THEN
FOR i IN l_column_tab.FIRST .. l_column_tab.LAST
LOOP
l_view_header := l_view_header||', '||l_column_tab(i).view_column_name;
l_view_line := l_view_line||'(DECODE ('||l_context_name||', ';
OPEN cur_attribute(l_column_tab(i).view_column_name);
FETCH cur_attribute BULK COLLECT INTO l_attribute_tab;
CLOSE cur_attribute;
IF (l_attribute_tab.COUNT > 0) THEN
FOR j IN l_attribute_tab.FIRST .. l_attribute_tab.LAST
LOOP
l_view_line := l_view_line||''''|| l_attribute_tab(j).descriptive_flex_context_code ||''''||', '|| l_attribute_tab(j).application_column_name||','||CHR(10);
END LOOP;
END IF;
l_view_line := l_view_line||'NULL )),'||CHR(10);
END LOOP;
END IF;
IF (l_context_tab.COUNT > 0) THEN
FOR k IN l_context_tab.FIRST .. l_context_tab.LAST
LOOP
OPEN cur_con_attr(l_context_tab(k).descriptive_flex_context_code);
FETCH cur_con_attr BULK COLLECT INTO l_con_attr_tab;
CLOSE cur_con_attr;
IF (l_con_attr_tab.COUNT > 0) THEN
FOR l IN l_con_attr_tab.FIRST .. l_con_attr_tab.LAST
LOOP
l_attribute := l_attribute || '|| ''.'' ||' ||l_con_attr_tab(l).application_column_name;
END LOOP;
END IF;
l_context := l_context||''''||l_context_tab(k).descriptive_flex_context_code||''''||', '||l_context_name||''||l_attribute||','||CHR(10);
l_attribute := NULL;
END LOOP;
END IF;
l_context := l_context||'NULL ))';
l_view := l_view_header||', CONCATENATED_SEGMENTS ) AS'||CHR(10)||l_view_line||l_context||CHR(10)|| 'FROM '||l_table_name;
BEGIN
EXECUTE IMMEDIATE l_view;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Error while compiling the view: '||SQLERRM);
retcode := 2;
END;
EXCEPTION
WHEN OTHERS THEN
errbuf := SQLERRM;
retcode := 2;
END;
No comments:
Post a Comment