APPS.EDW_UPDATE_ATTRIBUTES SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 106

	SELECT count (1) into l_map_count
	FROM EDW_ATTRIBUTE_MAPPINGS ATTR, EDW_FLEX_ATTRIBUTE_MAPPINGS FLEX
	WHERE ATTR.ATTR_MAPPING_PK = FLEX.ATTR_MAPPING_FK
	AND ATTR.OBJECT_SHORT_NAME = l_object_name
	AND NVL(ATTR.LEVEL_NAME, 'null') = NVL(l_level_name, 'null')
	AND FLEX.VALUE_SET_TYPE = 'F';

Line: 278

			l_tables := edw_update_attributes.add_db_links_to_string(l_tables, p_link);

Line: 321

	l_stmt := 'select additional_where_clause from fnd_flex_validation_tables@'||
		p_link||' where flex_value_set_id = :vsid';

Line: 390

	SELECT warehouse_to_instance_link INTO l_dblink
	FROM   edw_source_instances_vl
	WHERE  instance_code = p_instance;

Line: 406

	l_stmt := 'select column_name from user_tab_columns';

Line: 454

	g_where_clause.delete;

Line: 456

	l_stmt :=  'SELECT APPLICATION_TABLE_NAME, value_column_name, '||
		' meaning_column_name, id_column_name FROM '||
		' fnd_flex_validation_tables@' ||p_link||
		' a WHERE a.flex_value_set_id =  '||p_vsid;

Line: 468

	IF (upper(l_table_name) like 'SELECT %' OR upper(l_table_name) like '% SELECT %') THEN
		writelog('Inline tables not supported. Tables defined in the value set need to be database objects');

Line: 475

	l_clause := 'SELECT '||l_id_col||' id_column_name, '||
		nvl(l_meaning_col, nvl(l_value_col, 'null'))
		||' meaning_column_name, '||
		nvl(l_value_col, nvl(l_meaning_col, 'null')) ||
		' value_column_name from '||l_table_name ;

Line: 533

	SELECT warehouse_to_instance_link into l_db_link
	from EDW_SOURCE_INSTANCES_VL
	WHERE instance_code = p_instance;

Line: 537

	stmt := 'select substr(RELEASE_NAME, 1,8) from fnd_product_groups@'|| l_db_link;

Line: 559

SELECT
distinct FLEX.instance_code,
FLEX.VALUE_SET_ID,
decode(flex.flex_field_type, 'K', to_char(FLEX.STRUCTURE_NUM), FLEX.STRUCTURE_NAME) struct
FROM EDW_ATTRIBUTE_MAPPINGS ATTR, EDW_FLEX_ATTRIBUTE_MAPPINGS FLEX
WHERE ATTR.ATTR_MAPPING_PK = FLEX.ATTR_MAPPING_FK
AND ATTR.OBJECT_SHORT_NAME = p_object_name
AND NVL(ATTR.LEVEL_NAME, 'null') = NVL(p_level_name, 'null')
AND FLEX.VALUE_SET_TYPE = 'F';

Line: 576

SELECT
distinct attr.attribute_name
FROM EDW_ATTRIBUTE_MAPPINGS ATTR, EDW_FLEX_ATTRIBUTE_MAPPINGS FLEX
WHERE ATTR.ATTR_MAPPING_PK = FLEX.ATTR_MAPPING_FK
AND ATTR.OBJECT_SHORT_NAME = p_object_name
AND NVL(ATTR.LEVEL_NAME, 'null') = NVL(p_level_name, 'null')
AND FLEX.VALUE_SET_TYPE = 'F'
AND FLEX.VALUE_SET_ID = vsid ;

Line: 649

FUNCTION update_stg(
p_object_name IN VARCHAR2,
p_start_mode IN VARCHAR2,
p_logfile_dir IN VARCHAR2 default null) return boolean is

TYPE CurTyp IS REF CURSOR;

Line: 662

 SELECT attr.instance_code, attr.attribute_name,
 flex.value_set_id, assg.flex_field_prefix,
 vws.generated_view_name, vws.interface_table_name
 from
  edw_attribute_mappings attr,
  edw_flex_attribute_mappings flex,
  edw_sv_flex_assignments assg,
  edw_source_views vws
 where
  attr.attribute_name = attr_name and
  attr.object_short_name = assg.object_name and
  attr.attr_mapping_pk = flex.attr_mapping_fk and
  attr.object_short_name= obj_name and
  flex.id_flex_code = assg.flex_field_code and
  assg.version = edw_update_attributes.getAppsVersion(attr.instance_code) and
  attr.object_short_name = vws.object_name and
  nvl(attr.level_name, 'xxx') = nvl(vws.level_name, 'xxx') and
  nvl(attr.level_name, 'xxx') = nvl(lvl_name, 'xxx') and
  assg.version = vws.version and
  flex.value_set_type = 'F' and
  attr.flex_flag = 'Y' ;

Line: 687

SELECT distinct attribute_name
from edw_attribute_mappings a, edw_flex_attribute_mappings b
where object_short_name = obj_name
and level_name = lvl_name
and a.attr_mapping_pk = b.attr_mapping_fk
and nvl(level_name, 'xxx') = nvl(lvl_name, 'xxx')
and b.value_set_type = 'F'
order by attribute_name ;

Line: 712

SELECT
FLEX.instance_code,
FLEX.VALUE_SET_ID,
decode(flex.flex_field_type, 'K', to_char(FLEX.STRUCTURE_NUM), FLEX.STRUCTURE_NAME) struct
FROM EDW_ATTRIBUTE_MAPPINGS ATTR, EDW_FLEX_ATTRIBUTE_MAPPINGS FLEX
WHERE ATTR.ATTR_MAPPING_PK = FLEX.ATTR_MAPPING_FK
AND ATTR.OBJECT_SHORT_NAME = c_obj_name
AND NVL(ATTR.LEVEL_NAME, 'null') = NVL(c_lvl, 'null')
AND ATTR.ATTRIBUTE_NAME = c_attr
AND FLEX.VALUE_SET_TYPE = 'F';

Line: 737

l_select_clause	varchar2(1000);

Line: 805

	select dim_name into l_object_name
	from edw_levels_md_v
	where level_name||'_LTC' = p_object_name;

Line: 814

	SELECT level_id INTO l_object_id
	from EDW_LEVELS_MD_V
	WHERE level_name||'_LTC' = p_object_name;

Line: 825

		SELECT fact_id INTO l_object_id
		from EDW_FACTS_MD_V
		WHERE fact_name = p_object_name;

Line: 881

		l_stmt := 'select cols.column_name from	edw_relations_md_v rel,
			edw_unique_keys_md_v keys, edw_unique_key_columns_md_v cols
			where
			rel.relation_name = :s1
			and rel.relation_id = keys.entity_id
			and keys.key_id = cols.key_id';

Line: 904

			writelog('Table already created... inserting');

Line: 905

			execute immediate 'INSERT INTO '||l_current_table||
			'(row_id, value, context,instance ) select a.rowid row_id, a.'||
			c_mapped_attr.attribute_name||' value, b.'||c_mapped_attr.flex_field_prefix||
			'_CONTEXT context, '||''''||c_mapped_attr.instance_code|| ''''||' FROM '||c_mapped_attr.interface_table_name || ' a, '||
			c_mapped_attr.generated_view_name || '@'||l_link||' b WHERE a.'
			||c_mapped_attr.attribute_name ||' IS NOT NULL AND A.'||
			l_stg_pk ||' = b.'||l_bg_pk ||' AND A.collection_status ='||''''||'READY'||'''';

Line: 914

			writelog('Table does not exist... create as select..');

Line: 920

			||' tablespace '||l_op_table_space||' as select a.rowid row_id, '||
			c_mapped_attr.attribute_name||' value, b.'||c_mapped_attr.flex_field_prefix||
			'_CONTEXT context, '||''''||c_mapped_attr.instance_code||''''
			 ||' instance FROM '||c_mapped_attr.interface_table_name || ' a, '||
			c_mapped_attr.generated_view_name||'@'||l_link || ' b WHERE a.'
			||c_mapped_attr.attribute_name ||' IS NOT NULL AND A.'||
			l_stg_pk ||' = b.'||l_bg_pk||' AND  A.collection_status ='||''''||'READY'||'''';

Line: 1005

			writelog('Going to insert into '||l_current_table);

Line: 1008

			l_stmt :=  ' INSERT INTO '||l_current_table
			||' (ROW_ID, VALUE) select a.row_id row_id, '||
			' decode(b.value_column_name, null, a.value, b.value_column_name) '||
			' FROM '||l_bis_schema||'.INT_'||l_object_id||'_'||l_attribute_name
			|| ' a, ('||
			l_tab_clause || ') b WHERE a.value=to_char(b.id_column_name(+)) and '||
			' a.instance = '||''''||ctx_row.instance_code||''''||
			' and a.context = '||''''||ctx_row.struct||'''';

Line: 1027

			||' tablespace '||l_op_table_space||' as select a.row_id row_id, '||
			' decode(b.value_column_name, null, a.value, b.value_column_name) VALUE '||
			' FROM '|| l_bis_schema||'.INT_'||l_object_id||'_'||l_attribute_name
			|| ' a, ('||
			l_tab_clause || ') b WHERE a.value=to_char(b.id_column_name(+)) and '||
			' a.instance = '||''''||ctx_row.instance_code||''''||
			' and a.context = '||''''||ctx_row.struct||'''';

Line: 1073

		||' tablespace '||l_op_table_space||' as select rowid row_id from '|| l_it_name||' where collection_status = '||''''||'READY'||'''';

Line: 1088

	/* Now merge all these smaller tables into one big table needed for bulk update */

	drop_temp_table (l_bis_schema, 'INT_USER_ATTRIBUTES_'||l_object_id);

Line: 1095

		l_op_Table_space ||' AS SELECT ';

Line: 1099

	l_select_clause := null;

Line: 1106

	l_select_clause := l_select_clause|| ' a.row_id, ';

Line: 1109

		l_select_clause := l_select_clause || ', ';

Line: 1117

	  l_select_clause := l_select_clause||l_current_col||'.VALUE '||l_current_col;

Line: 1127

	l_stmt := l_stmt || l_select_clause|| l_from_clause||l_where_clause;

Line: 1129

	writelog('Select clause is '||l_select_clause);

Line: 1161

	/* Now UPDATE the staging table using a single update */

	l_stmt := 'UPDATE '||l_it_name|| ' stg SET ';

Line: 1164

	l_select_clause := ' (SELECT ';

Line: 1176

		l_select_clause := l_select_clause||', ';

Line: 1179

	  l_select_clause := l_select_clause||' A.'||
				l_tables_created(l_count).column_name;

Line: 1190

	l_stmt := l_stmt|| '('||l_set_clause ||') = '||l_select_clause|| l_from_clause|| ' )';

Line: 1193

	writelog('Final update stmt is : ');