APPS.EDW_DEL_STG SQL Statements

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

Line: 12

select
dim.name dim_name
from
cmprelationmapping_v  dim_map,
cmpwbdimension_v  dim
where
dim_map.targetdataentity=dim.elementid; */

Line: 21

select
dim.dim_long_name dim_name
from edw_relationmapping_md_v  dim_map,
  edw_dimensions_md_v  dim
where dim_map.targetdataentity=dim.dim_id;

Line: 28

select
src_relation.name table_name
from
cmprelation_v relation,
cmprelation_v src_relation,
cmprelationmapping_v dim_map,
cmprelationmapping_v lvl_map,
cmpwbrelationusage_v dim_ru,
cmpwbdimension_v dim
where
dim.longname=rtrim(p_dim_name)
and dim_map.targetdataentity=dim.elementid
and dim_ru.cmprelationmapping=dim_map.elementid
and dim_ru.source=1
and relation.elementid=dim_ru.cmprelation
and relation.classname='CMPWBTable'
and lvl_map.targetdataentity=relation.elementid
and src_relation.elementid=lvl_map.sourcedataentity; */

Line: 48

select lstg.relation_name  table_name
from edw_relations_md_v ltc,edw_relations_md_v lstg,
     edw_relationmapping_md_v map,edw_dimensions_md_v dim,
     edw_levels_md_v lvl
where dim.dim_long_name=p_dim_name
 and lvl.dim_id=dim.dim_id
 and ltc.relation_name=lvl.level_name||'_LTC'
 and map.targetdataentity=ltc.relation_id
 and lstg.relation_id=map.sourcedataentity;

Line: 59

select cube.name fact_name
from cmprelationmapping_v map,
cmpwbcube_v cube
where cube.elementid = map.targetdataentity; */

Line: 65

select cube.fact_longname fact_name
from edw_relationmapping_md_v map,edw_facts_md_v cube
where cube.fact_id = map.targetdataentity;**/

Line: 71

select cube1.fact_longname fact_name
from edw_relationmapping_md_v map1,edw_facts_md_v cube1
where cube1.fact_id = map1.targetdataentity
and  cube1.fact_longname not in
(select distinct cube.fact_longname
from
edw_facts_md_v cube,
edw_facts_md_v cube_src,
edw_relationmapping_md_v map where
map.targetdataentity=cube.fact_id
and map.sourcedataentity=cube_src.fact_id);

Line: 86

select  relation.relation_name table_name
from  edw_relations_md_v relation,
      edw_relationmapping_md_v map,
      edw_relations_md_v lvl_relation
where lvl_relation.relation_long_name=p_fact_name
 and map.targetdataentity=lvl_relation.relation_id
 and relation.relation_id=map.sourcedataentity;

Line: 95

Procedure Delete_Table(p_stg_name in varchar2) IS
l_count NUMBER:=0;

Line: 104

       DBMS_SQL.PARSE(cid, 'DELETE FROM '||p_stg_name||
        ' WHERE nvl(COLLECTION_STATUS, ''ERROR'') = ''COLLECTED'' OR nvl(COLLECTION_STATUS, ''ERROR'') =''DUPLICATE-COLLECT''', dbms_sql.native);

Line: 120

          l_stmt := 'select /*+ FIRST_ROWS */ 1 from '|| p_stg_name ||' where rownum=1';

Line: 140

Procedure Delete_Dimension(Errbuf in out NOCOPY varchar2, Retcode in out NOCOPY varchar2, p_dim_name in varchar2, p_purge_option in number) IS
l_mode number;

Line: 145

    		l_mode:=0;  -- 0 for delete, 1 for truncate

Line: 152

        	        Delete_All_Dimensions;

Line: 154

			Delete_One_Dimension(p_dim_name);

Line: 166

Procedure Delete_One_Dimension(p_dim_name in varchar2) IS

BEGIN

	/* delete from the level staging tables */
	edw_log.put_line('Deleting staging tables for '||p_dim_name);

Line: 173

       		Delete_Table(r1.table_name);

Line: 174

		edw_log.put_line('Deleted staging table '||r1.table_name);

Line: 178

Procedure Delete_All_Dimensions IS
BEGIN
        for r1 in alldimm loop
                Delete_One_Dimension(r1.dim_name);

Line: 186

Procedure Delete_Fact(Errbuf in out NOCOPY varchar2, Retcode in out NOCOPY varchar2, p_fact_name in varchar2, p_purge_option in number) IS
l_mode number;

Line: 193

		   -- 0 for delete loaded recods, 1 for truncate all records
        end if;

Line: 200

        	        Delete_All_Facts;

Line: 202

			Delete_One_fact(p_fact_name);

Line: 216

Procedure Delete_One_Fact(p_fact_name in varchar2) IS
l_staging_table varchar2(120) := null;

Line: 223

	Delete_Table(l_staging_table);

Line: 224

	edw_log.put_line('Deleted staging table '||l_staging_table);

Line: 229

Procedure Delete_All_Facts IS
BEGIN
	for r1 in allfactt loop
        	Delete_One_Fact(r1.fact_name);