APPS.EDW_DIM_SV SQL Statements

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

Line: 12

g_last_update_date_exists boolean := false;

Line: 30

	SELECT value_set_type type, count(*) count
	FROM	edw_flex_seg_mappings
	WHERE	dimension_short_name = dim_name
	AND	instance_code = edw_gen_view.g_instance
	GROUP BY value_set_type;

Line: 74

	select collection_view_name from edw_source_views
	where object_name = obj_short_name
	order by level_name;

Line: 133

g_last_update_date_exists := false;

Line: 142

   Open cv for 'select object_type from user_objects where object_name=:s1' using l_table;

Line: 149

	Open cv for 'select table_name from user_synonyms where synonym_name=:s1' using l_table;

Line: 166

stmt := 'SELECT distinct column_name FROM all_tab_columns@'||edw_gen_view.g_source_db_link
	 ||' WHERE column_name in( ''LAST_UPDATE_DATE'', ''CREATION_DATE'', ''DESCRIPTION'' )'
     ||' and table_name  = :table_name ';

Line: 186

         IF (l_column = 'LAST_UPDATE_DATE') THEN
            g_last_update_date_exists := true;

Line: 384

    stmt1 := 'SELECT distinct APPLICATION_TABLE_NAME, value_set_id, value_column_name, meaning_column_name,
        id_column_name, value_set_name FROM edw_flex_seg_mappings a,'||
     '  fnd_flex_validation_tables@' ||edw_gen_view.g_source_db_link
	 ||' b WHERE a.value_set_id = b.flex_value_set_id '
	 ||' AND a.dimension_short_name = :d1 AND A.VALUE_SET_TYPE = :d2';

Line: 423

                    stmt2 := 'SELECT DISTINCT ''SELECT ''||''''''''|| a.instance_code ||'':''||a.value_set_id||'':''''''||''||''|| value_column_name ||'' L2_PK,
                    ''|| VALUE_COLUMN_NAME|| ''  actual_value,
                    '' ||''''''';

Line: 428

                	stmt2 := 'SELECT DISTINCT ''SELECT ''||''''''''|| a.instance_code ||'':''||a.value_set_id||'':''''''||''||''|| id_column_name ||'' L2_PK,
                    ''|| id_COLUMN_NAME|| ''  actual_value,'' ||''''''';

Line: 431

    		        stmt2 := 'SELECT DISTINCT ''SELECT ''||''''''''|| a.instance_code ||'':''||a.value_set_id||'':''''''||''||''|| id_column_name ||'' L2_PK,
                    ''|| value_COLUMN_NAME|| ''  actual_value,
                    '' ||''''''';

Line: 440

       IF (g_last_update_date_exists) THEN
        	edw_gen_view.writelog('Last Update date exists');

Line: 444

        		stmt2 := stmt2||' '||l_table_alias||'.last_update_date ';

Line: 446

        		stmt2 := stmt2||' '||l_final_table||'.last_update_date ';

Line: 448

           stmt2 := stmt2 || ' last_update_date, ';

Line: 450

            stmt2 := stmt2 || '  to_date(null, ''''mm/dd/yyyy hh24:mi:ss'''') last_update_date, ';

Line: 512

    	    edw_gen_view.g_where_clause.delete;

Line: 531

    		      edw_gen_view.writelog('Select Clause IS : '|| clause);

Line: 559

	SELECT distinct a.value_set_type, a.value_set_id, a.parent_value_set_id
	FROM edw_flex_seg_mappings a
	WHERE
	dimension_short_name = p_dim_name
	and instance_code = edw_gen_view.g_instance
	AND ( value_set_type = 'I' /* OR value_set_type = 'N' OR
		value_set_type = 'F' */)
	AND NOT EXISTS
	(SELECT 1 FROM edw_flex_seg_mappings  b
	where b.parent_value_set_id = a.value_set_id
	AND b.dimension_short_name = a.dimension_short_name
	AND b.structure_num = a.structure_num);

Line: 573

	SELECT parent.value_set_type, parent.value_set_id, parent.parent_value_set_id , parent.structure_num
	FROM edw_flex_seg_mappings parent, edw_flex_seg_mappings child
	WHERE
	parent.dimension_short_name = child.dimension_short_name
	AND parent.dimension_short_name = p_dim_name
	AND parent.instance_code =  edw_gen_view.g_instance
	AND child.instance_code =  edw_gen_view.g_instance
	AND parent.value_set_type = 'I'
	AND child.value_set_type = 'D'
	AND child.parent_value_set_id = parent.value_set_id
    	AND parent.structure_num = child.structure_num;

Line: 595

	   src_view :=  	'SELECT  '||''''||edw_gen_view.g_instance||':''||'||'flex_value_set_id||'':''||flex_value L2_PK, flex_value actual_value,  ' ||newline||
				' '''||edw_gen_view.g_instance||''''||' instance,  last_update_date, '||
				newline||'  creation_date, description,  ''NA_EDW'' L2_FK '||
				newline||' FROM  fnd_flex_values_vl '||
				newline||' WHERE  flex_value_set_id IN ( ';

Line: 612

        src_view :=  	'SELECT  '||''''||edw_gen_view.g_instance||':''||'||'a.flex_value_set_id||'':''||a.flex_value L1_PK,  ' ||newline||
					' flex_value actual_value, '||
					''''||edw_gen_view.g_instance||''''||' instance,    '||newline||
					' a.last_update_date,  a.creation_date, a.description,  ''NA_EDW'' L1_FK '||
					newline||' FROM  fnd_flex_values_vl a ' ||
					' WHERE a.flex_value_set_id IN ( ';

Line: 644

	SELECT distinct a.value_set_type, a.value_set_id, a.parent_value_set_id
	FROM edw_flex_seg_mappings a WHERE
	dimension_short_name = p_dim_name
	and a.instance_code =  edw_gen_view.g_instance
	AND a.parent_value_set_id <> 0 ; /* dependant value sets */

Line: 660

  	src_view :=   '  SELECT '||''''||edw_gen_view.g_instance||':''||'||' childvl.flex_value_set_id||'':''||parentvl.flex_value|| '':''|| childvl.flex_value L2_PK,  parentvl.flex_value||'':''||childvl.flex_value actual_value,';

Line: 662

    	newline||' childvl.last_update_date, childvl.creation_date, '||
		newline||' childvl.description, '||''''||edw_gen_view.g_instance||':''||'||' parentvl.flex_value_set_id  '|| '||'':''||'||' parentvl.flex_value  L2_FK ';

Line: 699

select1 varchar2(1000);

Line: 700

select2 varchar2(1000);

Line: 701

select3 varchar2(1000);

Line: 702

select4 varchar2(1000);

Line: 733

select1 := 'SELECT b.APPLICATION_TABLE_NAME , a.structure_num, a.STRUCTURE_NAME,'||newline;

Line: 744

finalStmt := select1||whereKeyFlex||'UNION ALL ' ||newline||select1 ||whereDescFlex;

Line: 757

    select1 := 'SELECT ''SELECT DISTINCT ''||''''''''|| a.instance_code ||'':''||a.value_set_id||'':''''||''||application_column_name ||'' L2_PK, '''||newline;

Line: 758

    select2 := '|| application_COLUMN_NAME ||'' ACTUAL_VALUE,'' ||''''''''||a.instance_code||''''''''||';

Line: 759

    select3 := ''' INSTANCE, ';

Line: 761

    IF (g_last_update_date_exists) THEN
        select3 := select3 || newline||'  LAST_UPDATE_DATE, ';

Line: 764

        select3 := select3 || newline||'  to_date(null, ''''mm/dd/yyyy hh24:mi:ss'''') last_update_date, ';

Line: 767

        select3 := select3 || newline||'  CREATION_DATE, ';

Line: 769

        select3 := select3 || newline||'  to_date(null, ''''mm/dd/yyyy hh24:mi:ss'''') creation_date, ';

Line: 772

        select3 := select3 || newline||'  DESCRIPTION, ';

Line: 774

        select3 := select3 || newline||'  null DESCRIPTION, ';

Line: 777

    select3 := select3 ||'''''NA_EDW'''' L2_FK FROM ''';

Line: 778

    select4 := '  || b.APPLICATION_TABLE_NAME||'' WHERE ''||application_column_name ||'' is not null ';

Line: 782

        select4 := select4 ||' AND '||l_structure_col  || ' = '''''||l_structure_id ||'''''';

Line: 787

		 select4 := select4 ||' AND '||l_structure_col  || ' = '''''||l_structure_id ||'''''';

Line: 793

		  select4 := select4 ||' AND '||l_structure_col  || ' = '''''|| l_struct_name ||'''''';

Line: 797

    select4 := select4||''' FROM edw_flex_seg_mappings a, '||newline;

Line: 809

    finalStmt := select1||select2||select3||select4||whereKeyFlex||'  UNION  ALL ' ||newline||
                 select1||select2||select3||select4||whereDescFlex;

Line: 861

	SELECT flex_view_name, generated_view_name, collection_view_name
	INTO g_std_flex_view_name, g_std_generated_view_name, g_std_collection_view_name
	FROM edw_source_views
	WHERE object_name = dim_name AND level_name = level AND version =edw_gen_view.g_version;

Line: 897

	SELECT generated_view_name INTO view_name
	FROM edw_source_views
	WHERE object_name = dim_name AND level_name = level AND version =edw_gen_view.g_version;

Line: 937

  select attribute_name, attribute_type
  from edw_attribute_properties
  where skip_flag = 'Y'
    and object_short_name = p_object_short_name
    and level_name = p_level_name;

Line: 972

	stmt := 'SELECT distinct column_name, data_type FROM all_tab_columns@'||edw_gen_view.g_source_db_link;

Line: 1004

	srcview := ' CREATE OR REPLACE FORCE VIEW ' ||view_name || ' AS '||newline|| 'SELECT ';

Line: 1122

    srcview := replace (srcview, 'SELECT ', 'SELECT '||newline);

Line: 1147

	SELECT DISTINCT level_name FROM edw_attribute_mappings
	WHERE object_short_name = dim_name;

Line: 1175

selectClause VARCHAR2(32000) := null;

Line: 1191

	select value_set_type, value_set_id, parent_value_set_id
	from edw_flex_seg_mappings where
	dimension_short_name = obj_name
	and instance_code = edw_gen_view.g_instance
	order by value_set_type;

Line: 1239

		selectClause := getDepVSClause(dim_name);

Line: 1255

			selectClause := selectClause || newline||'UNION ALL '||newline;

Line: 1258

		selectClause := selectClause || getTableValClause(dim_name);

Line: 1270

			selectClause := selectClause || newline||'UNION ALL '||newline;

Line: 1273

		selectClause := selectClause||getIndepVSClause(dim_name, 'LOWER');

Line: 1286

			selectClause := selectClause ||newline|| 'UNION ALL '||newline;

Line: 1289

		selectClause := selectClause||getNoneVSClause(dim_name);

Line: 1296

	src_view := src_view || selectClause;

Line: 1305

    src_view := replace (src_view, 'SELECT ', 'SELECT '||newline);

Line: 1316

	 src_view :=  	'CREATE OR REPLACE FORCE VIEW '||flexdim_viewname1 || ' as '||newline||'SELECT ''NA_EDW'' L1_PK, ''NA_EDW'' L1_FK, '||
		''''||edw_gen_view.g_instance||''''||' instance, null actual_value, '||newline||
		' null last_update_date, null description, null creation_date from dual where 1=2';