[Home] [Help]
PACKAGE BODY: APPS.FEM_AW_SNAPSHOT_PKG
Source
1 PACKAGE BODY FEM_AW_Snapshot_Pkg AS
2 -- $Header: fem_aw_snapshot.plb 120.1 2005/07/07 15:26:13 appldev ship $
3
4 PROCEDURE Create_Snapshot(
5 x_err_code OUT NOCOPY NUMBER,
6 x_num_msg OUT NOCOPY NUMBER
7 )
8
9 IS
10
11 v_dim_id NUMBER;
12 v_dim_attr_tab VARCHAR2(30);
13 v_member_col VARCHAR2(30);
14 v_attr_id NUMBER;
15 v_attr_name VARCHAR2(80);
16 v_old_ver_id NUMBER;
17 v_new_ver_id NUMBER;
18 v_def_ver_id NUMBER;
19 v_source_lang VARCHAR2(4);
20 v_ver_name VARCHAR2(80);
21 v_ver_dc VARCHAR2(150);
22 v_ver_desc VARCHAR2(255);
23 v_vs_col_exists NUMBER;
24 v_tl_ver_exists NUMBER;
25
26 v_rowid VARCHAR2(20) := '';
27 c_user_id CONSTANT NUMBER := FND_GLOBAL.USER_ID;
28 c_lang CONSTANT VARCHAR2(4) := userenv('LANG');
29
30 v_msg_num NUMBER;
31
32 v_sql_cmd1 VARCHAR2(4000);
33 v_sql_cmd2 VARCHAR2(4000);
34
35 CURSOR cv_dims IS
36 SELECT distinct dimension_id
37 FROM fem_dim_attributes_b;
38
39 CURSOR cv_attrs IS
40 SELECT attribute_id
41 FROM fem_dim_attributes_b
42 WHERE dimension_id = v_dim_id;
43
44 BEGIN
45
46 x_err_code := 0;
47 x_num_msg := 0;
48
49 /****************************************
50 Stubbed for bug#4173291 since the Dimension Snapshot Engine
51 replaces this package
52
53 --------------------------
54 -- Get Distinct Dimensions
55 --------------------------
56 FOR r_dims IN cv_dims
57 LOOP
58 v_dim_id := r_dims.dimension_id;
59
60 SELECT member_col,
61 attribute_table_name
62 INTO v_member_col,
63 v_dim_attr_tab
64 FROM fem_xdim_dimensions
65 WHERE dimension_id = v_dim_id;
66
67 ---------------------------------------
68 -- Get Attributes for Current Dimension
69 ---------------------------------------
70 FOR r_attrs IN cv_attrs
71 LOOP
72 v_attr_id := r_attrs.attribute_id;
73
74 ---------------------
75 -- Get Attribute Name
76 ---------------------
77 v_attr_name := FEM_Dimension_Util_Pkg.Get_Dim_Attr_Name
78 (p_attr_id => v_attr_id);
79
80 -------------------------
81 -- Delete Old AW Snapshot
82 -------------------------
83 BEGIN
84 SELECT version_id
85 INTO v_old_ver_id
86 FROM fem_dim_attr_versions_b
87 WHERE attribute_id = v_attr_id
88 AND aw_snapshot_flag = 'Y';
89
90 FEM_DIM_ATTR_VERSIONS_PKG.DELETE_ROW
91 (x_version_id => v_old_ver_id);
92
93 IF (v_dim_attr_tab IS NOT NULL)
94 THEN
95 EXECUTE IMMEDIATE
96 'DELETE FROM '||v_dim_attr_tab||
97 ' WHERE attribute_id = '||v_attr_id||
98 ' AND version_id = '||v_old_ver_id;
99 END IF;
100 EXCEPTION
101 WHEN no_data_found THEN null;
102 END;
103
104 ----------------------------------
105 -- Get Default Version to Snapshot
106 ----------------------------------
107 BEGIN
108 SELECT version_id
109 INTO v_def_ver_id
110 FROM fem_dim_attr_versions_b
111 WHERE attribute_id = v_attr_id
112 AND default_version_flag = 'Y';
113 EXCEPTION
114 WHEN no_data_found THEN
115 v_def_ver_id := '';
116
117 FEM_ENGINES_PKG.PUT_MESSAGE
118 (p_app_name => 'FEM',
119 p_msg_name => 'FEM_AWSS_NO_DEF_VER_WARN',
120 p_token1 => 'ATTRIBUTE',
121 p_value1 => v_attr_name);
122
123 x_err_code := 1;
124 x_num_msg := x_num_msg + 1;
125
126 WHEN too_many_rows THEN
127 v_def_ver_id := '';
128
129 FEM_ENGINES_PKG.PUT_MESSAGE
130 (p_app_name => 'FEM',
131 p_msg_name => 'FEM_AWSS_MANY_DEF_VER_WARN',
132 p_token1 => 'ATTRIBUTE',
133 p_value1 => v_attr_name);
134
135 x_err_code := 1;
136 x_num_msg := x_num_msg + 1;
137 END;
138
139 IF (v_def_ver_id IS NOT NULL)
140 THEN
141 ----------------------------------
142 -- Get Default Version's TL Record
143 ----------------------------------
144 v_tl_ver_exists := 1;
145 BEGIN
146 SELECT source_lang,
147 version_name,
148 description,
149 version_display_code
150 INTO v_source_lang,
151 v_ver_name,
152 v_ver_desc,
153 v_ver_dc
154 FROM fem_dim_attr_versions_tl T,
155 fem_dim_attr_versions_b B
156 WHERE T.version_id = v_def_ver_id
157 AND B.version_id = T.version_id
158 AND T.language = c_lang;
159 EXCEPTION
160 WHEN no_data_found THEN
161 v_tl_ver_exists := 0;
162
163 FEM_ENGINES_PKG.PUT_MESSAGE
164 (p_app_name => 'FEM',
165 p_msg_name => 'FEM_AWSS_NO_DEF_VER_TL_WARN',
166 p_token1 => 'ATTRIBUTE',
167 p_value1 => v_attr_name);
168
169 x_err_code := 1;
170 x_num_msg := x_num_msg + 1;
171
172 WHEN too_many_rows THEN
173 v_tl_ver_exists := 0;
174
175 FEM_ENGINES_PKG.PUT_MESSAGE
176 (p_app_name => 'FEM',
177 p_msg_name => 'FEM_AWSS_MANY_DEF_VER_TL_WARN',
178 p_token1 => 'ATTRIBUTE',
179 p_value1 => v_attr_name);
180
181 x_err_code := 1;
182 x_num_msg := x_num_msg + 1;
183 END;
184
185 IF (v_tl_ver_exists = 1)
186 THEN
187 ------------------------------------------
188 -- Create Snapshot Copy of Default Version
189 ------------------------------------------
190 SELECT fem_dim_attr_versions_b_s.NEXTVAL
191 INTO v_new_ver_id FROM dual;
192
193 v_ver_dc := v_ver_dc||':'||v_new_ver_id;
194
195 FEM_DIM_ATTR_VERSIONS_PKG.INSERT_ROW(
196 x_rowid => v_rowid,
197 x_version_id => v_new_ver_id,
198 x_aw_snapshot_flag => 'Y',
199 x_version_display_code => v_ver_dc,
200 x_object_version_number => 1,
201 x_default_version_flag => 'N',
202 x_personal_flag => 'N',
203 x_attribute_id => v_attr_id,
204 x_version_name => v_ver_name,
205 x_description => v_ver_desc,
206 x_creation_date => sysdate,
207 x_created_by => c_user_id,
208 x_last_update_date => sysdate,
209 x_last_updated_by => c_user_id,
210 x_last_update_login => null);
211
212 IF (v_dim_attr_tab IS NULL)
213 THEN
214 FEM_ENGINES_PKG.PUT_MESSAGE
215 (p_app_name => 'FEM',
216 p_msg_name => 'FEM_AWSS_NO_ATTR_TAB_WARN',
217 p_token1 => 'ATTRIBUTE',
218 p_value1 => v_attr_name);
219
220 x_err_code := 1;
221 x_num_msg := x_num_msg + 1;
222 ELSE
223 ----------------------------------------------
224 -- Build SQL Statement to Create Snapshot Copy
225 -- in Dimension's ATTR Table
226 ----------------------------------------------
227 v_sql_cmd1 :=
228 'INSERT INTO '||v_dim_attr_tab||
229 '(attribute_id,'||
230 'version_id,'||
231 v_member_col||','||
232 'dim_attribute_numeric_member,'||
233 'dim_attribute_varchar_member,'||
234 'number_assign_value,'||
235 'varchar_assign_value,'||
236 'date_assign_value,'||
237 'creation_date,'||
238 'created_by,'||
239 'last_updated_by,'||
240 'last_update_date,'||
241 'object_version_number,'||
242 'aw_snapshot_flag';
243 v_sql_cmd2 :=
244 'SELECT '||
245 v_attr_id||','||
246 v_new_ver_id||','||
247 v_member_col||','||
248 'dim_attribute_numeric_member,'||
249 'dim_attribute_varchar_member,'||
250 'number_assign_value,'||
251 'varchar_assign_value,'||
252 'date_assign_value,'||
253 ''''||sysdate||''','||
254 c_user_id||','||
255 c_user_id||','||
256 ''''||sysdate||''','||
257 1||','||
258 '''Y'' ';
259
260 --------------------------------------
261 -- Determine if Dimension's ATTR Table
262 -- has VALUE_SET_ID column
263 --------------------------------------
264 BEGIN
265 SELECT 1
266 INTO v_vs_col_exists
267 FROM all_tab_columns
268 WHERE owner = 'FEM'
269 AND table_name = v_dim_attr_tab
270 AND column_name = 'VALUE_SET_ID';
271 EXCEPTION
272 WHEN no_data_found THEN
273 v_vs_col_exists := 0;
274 END;
275
276 IF (v_vs_col_exists = 1)
277 THEN
278 v_sql_cmd1 := v_sql_cmd1||','||
279 'value_set_id)';
280 v_sql_cmd2 := v_sql_cmd2||','||
281 'value_set_id';
282 ELSE
283 v_sql_cmd1 := v_sql_cmd1||')';
284 END IF;
285
286 v_sql_cmd2 := v_sql_cmd2||
287 ' FROM '||v_dim_attr_tab||
288 ' WHERE attribute_id = '||v_attr_id||
289 ' AND version_id = '||v_def_ver_id;
290
291 ------------------------------------------------
292 -- Execute SQL Statement to Create Snapshot Copy
293 -- in Dimension's ATTR Table
294 ------------------------------------------------
295 BEGIN
296 EXECUTE IMMEDIATE v_sql_cmd1||v_sql_cmd2;
297 EXCEPTION
298 WHEN no_data_found THEN
299 FEM_ENGINES_PKG.PUT_MESSAGE
300 (p_app_name => 'FEM',
304 p_token2 => 'ATTR_TAB',
301 p_msg_name => 'FEM_AWSS_NO_ATTR_DEF_WARN',
302 p_token1 => 'ATTRIBUTE',
303 p_value1 => v_attr_name,
305 p_value2 => v_dim_attr_tab);
306
307 x_err_code := 1;
308 x_num_msg := x_num_msg + 1;
309 END;
310
311 END IF;
312 END IF;
313 END IF;
314
315 END LOOP;
316 END LOOP;
317
318 COMMIT;
319 */
320 END Create_Snapshot;
321
322 END FEM_AW_Snapshot_Pkg;