[Home] [Help]
PACKAGE BODY: APPS.EDW_TRUNC_STG
Source
1 Package Body EDW_TRUNC_STG AS
2 /* $Header: EDWTRCTB.pls 115.10 2004/02/13 05:06:25 smulye noship $*/
3 g_dummy VARCHAR2(30);
4 g_dummy_int NUMBER;
5 cid NUMBER;
6 l_fact BOOLEAN := TRUE;
7 g_errbuf varchar2(200) := null;
8 g_retcode number := 0;
9 g_name varchar2(100) := null;
10
11
12 /* cursor alldimm is
13 select
14 dim.longname dim_name
15 from
16 cmprelationmapping_v dim_map,
17 cmpwbdimension_v dim
18 where
19 dim_map.targetdataentity=dim.elementid; */
20
21 cursor alldimm is
22 select
23 dim.dim_long_name dim_name
24 from edw_relationmapping_md_v dim_map,
25 edw_dimensions_md_v dim
26 where dim_map.targetdataentity=dim.dim_id;
27
28 /* cursor dimm(p_dim_name varchar2) is
29 select
30 src_relation.name table_name
31 from
32 cmprelation_v relation,
33 cmprelation_v src_relation,
34 cmprelationmapping_v dim_map,
35 cmprelationmapping_v lvl_map,
36 cmpwbrelationusage_v dim_ru,
37 cmpwbdimension_v dim
38 where
39 dim.longname=rtrim(p_dim_name)
40 and dim_map.targetdataentity=dim.elementid
41 and dim_ru.cmprelationmapping=dim_map.elementid
42 and dim_ru.source=1
43 and relation.elementid=dim_ru.cmprelation
44 and relation.classname='CMPWBTable'
45 and lvl_map.targetdataentity=relation.elementid
46 and src_relation.elementid=lvl_map.sourcedataentity; */
47
48 cursor dimm(p_dim_name varchar2) is
49 select lstg.relation_name table_name
50 from edw_relations_md_v ltc,edw_relations_md_v lstg,
51 edw_relationmapping_md_v map,edw_dimensions_md_v dim,
52 edw_levels_md_v lvl
53 where dim.dim_long_name=p_dim_name
54 and lvl.dim_id=dim.dim_id
55 and ltc.relation_name=lvl.level_name||'_LTC'
56 and map.targetdataentity=ltc.relation_id
57 and lstg.relation_id=map.sourcedataentity;
58
59
60 /* cursor allfactt is
61 select cube.longname fact_name
62 from cmprelationmapping_v map,
63 cmpwbcube_v cube
64 where cube.elementid = map.targetdataentity; */
65
66 /**cursor allfactt is
67 select cube.fact_longname fact_name
68 from edw_relationmapping_md_v map,edw_facts_md_v cube
69 where cube.fact_id = map.targetdataentity;**/
70
71 ----fix bug 2109920.Exclude derived facts from the fact list---
72 cursor allfactt is
73 select cube1.fact_longname fact_name
74 from edw_relationmapping_md_v map1,edw_facts_md_v cube1
75 where cube1.fact_id = map1.targetdataentity
76 and cube1.fact_longname not in
77 (select distinct cube.fact_longname
78 from
79 edw_facts_md_v cube,
80 edw_facts_md_v cube_src,
81 edw_relationmapping_md_v map where
82 map.targetdataentity=cube.fact_id
83 and map.sourcedataentity=cube_src.fact_id);
84
85
86 /* cursor factt(p_fact_name varchar2) is
87 select
88 relation.name table_name
89 from
90 cmprelation_v relation,
91 cmprelationmapping_v map,
92 cmprelation_v lvl_relation
93 where
94 lvl_relation.longname=rtrim(p_fact_name)
95 and map.targetdataentity=lvl_relation.elementid
96 and relation.elementid=map.sourcedataentity; */
97 cursor factt(p_fact_name varchar2) is
98 select relation.relation_name table_name
99 from edw_relations_md_v relation,
100 edw_relationmapping_md_v map,
101 edw_relations_md_v lvl_relation
102 where lvl_relation.relation_long_name=p_fact_name
103 and map.targetdataentity=lvl_relation.relation_id
104 and relation.relation_id=map.sourcedataentity;
105
106
107 cursor table_owner(p_tab_name varchar2) is
108 select
109 table_owner
110 from user_synonyms
111 where table_name=p_tab_name;
112
113 Procedure truncate_Table(p_stg_name in varchar2) IS
114 l_count NUMBER:=0;
115 l_owner varchar2(10);
116 begin
117 open table_owner(p_stg_name);
118 fetch table_owner into l_owner;
119 if table_owner%notfound then
120 null;
121 end if;
122 close table_owner;
123 edw_log.put_line('owner '||l_owner);
124 edw_log.put_line('staging '||p_stg_name);
125 if l_owner is not null then
126 cid := DBMS_SQL.open_cursor;
127 DBMS_SQL.PARSE(cid, 'truncate table '||l_owner||'.'||p_stg_name||' drop storage', dbms_sql.native);
128 g_dummy_int:=DBMS_SQL.EXECUTE(cid);
129 commit;
130 end if;
131 EXCEPTION
132 when others then
133 g_errbuf := sqlerrm;
134 g_retcode := -1;
135 end;
136
137
138
139 Procedure truncate_Dimension(Errbuf in out NOCOPY varchar2, Retcode in out NOCOPY varchar2, p_dim_name in varchar2) IS
140
141 BEGIN
142 edw_log.put_line('Dimension name is '||p_dim_name);
143 IF (p_dim_name IS NULL) THEN
144 truncate_All_Dimensions;
145 ELSE
146 truncate_One_Dimension(p_dim_name);
147 END IF;
148 END;
149
150 Procedure truncate_One_Dimension(p_dim_name in varchar2) IS
151 BEGIN
152 /* truncate the level staging tables */
153 edw_log.put_line('Truncating staging tables for '||p_dim_name);
154 FOR r1 IN dimm(p_dim_name) LOOP
155 truncate_Table(r1.table_name);
156 edw_log.put_line('Truncated staging table '||r1.table_name);
157 END LOOP;
158 END;
159
160 Procedure truncate_All_Dimensions IS
161 BEGIN
162 for r1 in alldimm loop
163 truncate_One_Dimension(r1.dim_name);
164 end loop;
165 END;
166
167
168 Procedure truncate_Fact(Errbuf in out NOCOPY varchar2, Retcode in out NOCOPY varchar2, p_fact_name in varchar2) IS
169 l_staging_table varchar2(120) := null;
170 BEGIN
171 IF (p_fact_name IS NULL) THEN
172 truncate_All_Facts;
173 ELSE
174 truncate_One_fact(p_fact_name);
175 END IF;
176
177 END;
178
179 Procedure truncate_One_Fact(p_fact_name in varchar2) IS
180 l_staging_table varchar2(120) := null;
181 BEGIN
182 open factt(p_fact_name);
183 fetch factt into l_staging_table;
184 close factt;
185 edw_log.put_line('Truncating staging tables for '||p_fact_name);
186 truncate_Table(l_staging_table);
187 edw_log.put_line('Truncated staging table '||l_staging_table);
188 END;
189
190
191 Procedure truncate_All_Facts IS
192 BEGIN
193 for r1 in allfactt loop
194 truncate_One_Fact(r1.fact_name);
195 end loop;
196
197 END;
198
199 END; -- Package Body EDW_TRUNC_STG