[Home] [Help]
PACKAGE BODY: APPS.FEM_DIM_SNAPSHOT_ENG_PKG
Source
1 PACKAGE BODY FEM_DIM_SNAPSHOT_ENG_PKG AS
2 --$Header: fem_dimsnap_eng.plb 120.0 2005/10/19 19:27:27 appldev noship $
3 /*==========================================================================+
4 | Copyright (c) 1997 Oracle Corporation, Redwood Shores, CA, USA |
5 | All rights reserved. |
6 +==========================================================================+
7 | FILENAME
8 |
9 | fem_dim_snapshot_eng.plb
10 |
11 | NAME fem_dim_snapshot_eng_pkg
12 |
13 | DESCRIPTION
14 |
15 | Package body for fem_dim_snapshot_eng_pkg.
16 | For more information about the purpose of this package, please refer
17 | to the package spec.
18 |
19 | HISTORY
20 |
21 | 29-JUN-05 Created
22 |
23 |
24 +=========================================================================*/
25
26 -----------------------
27 -- Package Constants --
28 -----------------------
29 pc_pkg_name CONSTANT VARCHAR2(30) := 'fem_object_catalog_util_pkg';
30 pc_log_level_unexpected CONSTANT NUMBER := fnd_log.level_unexpected;
31 pc_log_level_1 CONSTANT NUMBER := fnd_log.level_statement;
32 pc_log_level_2 CONSTANT NUMBER := fnd_log.level_procedure;
33 pc_log_level_3 CONSTANT NUMBER := fnd_log.level_event;
34 pc_log_level_4 CONSTANT NUMBER := fnd_log.level_exception;
35 pc_log_level_5 CONSTANT NUMBER := fnd_log.level_error;
36
37
38 -----------------------
39 -- Package Variables --
40 -----------------------
41 gv_prg_msg VARCHAR2(2000);
42 gv_callstack VARCHAR2(2000);
43
44
45
46 PROCEDURE Validate_OA_Params (
47 p_init_msg_list IN VARCHAR2,
48 p_commit IN VARCHAR2,
49 p_encoded IN VARCHAR2,
50 x_return_status OUT NOCOPY VARCHAR2
51 );
52
53
54
55 PROCEDURE Validate_OA_Params (
56 p_init_msg_list IN VARCHAR2,
57 p_commit IN VARCHAR2,
58 p_encoded IN VARCHAR2,
59 x_return_status OUT NOCOPY VARCHAR2
60 )
61 IS
62 e_bad_p_init_msg_list EXCEPTION;
63 e_bad_p_encoded EXCEPTION;
64
65
66 BEGIN
67
68 x_return_status := c_success;
69
70 ----------------------------------------
71 -- Validate Input params
72 ----------------------------------------
73 -- NOTE: The engine ignores the value in p_commit
74 -- since the it always commits by dimension in order
75 -- to prevent rollback errors
76
77 CASE p_init_msg_list
78 WHEN c_false THEN NULL;
79 WHEN c_true THEN
80 FND_MSG_PUB.Initialize;
81 ELSE RAISE e_bad_p_init_msg_list;
82 END CASE;
83
84 CASE p_encoded
85 WHEN c_false THEN NULL;
86 WHEN c_true THEN NULL;
87 ELSE RAISE e_bad_p_encoded;
88 END CASE;
89
90
91 EXCEPTION
92 WHEN e_bad_p_init_msg_list THEN
93 FEM_ENGINES_PKG.Put_Message(
94 p_app_name => 'FEM',
95 p_msg_name => 'FEM_BAD_P_INIT_MSG_LIST_ERR');
96 x_return_status := c_error;
97
98 WHEN e_bad_p_encoded THEN
99 FEM_ENGINES_PKG.Put_Message(
100 p_app_name => 'FEM',
101 p_msg_name => 'FEM_BAD_P_ENCODED_ERR');
102 x_return_status := c_error;
103
104
105
106 END Validate_OA_Params;
107
108
109 /*==========================================================================+
110 | Copyright (c) 1997 Oracle Corporation, Redwood Shores, CA, USA |
111 | All rights reserved. |
112 +==========================================================================+
113 |
114 | PROCEDURE NAME: Main
115 |
116 | This is the procedure called to launch the Dimension Snapshot
117 | Engine. It performs the following:
118 |
119 | 1. Validates the input paramters
120 | 2. Deletes all previously captured version TL information for the
121 | same created_by_object_id
122 | 3. Inserts the version TL information into the
123 | FEM_DSNP_DIM_ATTR_VRS_TL table for all shared versions in the system.
124 | As of FEM.D, there is no "personal version" concept (i.e,. all versions
125 | are shared), however the dynamic SQL filters on personal_Flag = 'N'
126 | just in case for future implementation of such a feature.
127 | 4. Deletes all previously captured attribute assignments from the
128 | target DNSP ATTR table for the same created_by_object_id
129 | 5. Fetches the list of all attributed dimensions (with target snapshot
130 | tables into a cursor
131 | 6. For each fetched dimension it inserts all attribute assignment rows
132 | for shared dimension members into the target DNSP ATTR table. It
133 | commits after each dimension.
134 |
135 | HISTORY
136 |
137 | 29-JUN-05 Created
138 |
139 |
140 +=========================================================================*/
141
142 PROCEDURE Main (
143 x_return_status OUT NOCOPY VARCHAR2,
144 x_msg_count OUT NOCOPY NUMBER,
145 x_msg_data OUT NOCOPY VARCHAR2,
146 p_api_version IN NUMBER DEFAULT c_api_version,
147 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
148 p_commit IN VARCHAR2 DEFAULT c_true,
149 p_encoded IN VARCHAR2 DEFAULT c_true,
150 p_dim_snapshot_obj_def_id IN NUMBER
151 )
152
153 IS
154 c_api_name CONSTANT VARCHAR2(30) := 'main';
155
156 v_object_id NUMBER; -- Dim Snapshot object_id of the obj_def_id parm
157 v_count NUMBER;
158 v_sysdate DATE; -- tracks the sysdate so that all records get
159 -- the exact same creation_date/last_update_date
160
161 -- variables for dynamic sql statements
162 v_sql_delete_stmt VARCHAR2(4000);
163 v_sql_insert_stmt VARCHAR2(4000);
164 v_sql_vers_delete_stmt VARCHAR2(4000);
165 v_sql_vers_insert_stmt VARCHAR2(4000);
166
167 e_invalid_obj_def_id EXCEPTION;
168 e_invalid_dim EXCEPTION;
169
170 CURSOR c1_attr_dims IS
171 SELECT X.dimension_id
172 , X.dimension_varchar_label
173 , X.value_set_required_flag
174 , X.member_col
175 , X.attribute_table_name
176 , X.dsnp_attribute_table_name
177 , X.member_b_table_name
178 FROM fem_xdim_dimensions_vl X, fem_dsnp_rule_dims R
179 WHERE X.attribute_table_name is not null
180 AND X.dsnp_attribute_table_name is not null
181 AND X.dimension_id = R.dimension_id
182 AND R.dim_snapshot_obj_def_id = p_dim_snapshot_obj_def_id
183 ORDER BY dimension_id;
184
185
186 BEGIN
187
188 x_return_status := c_success;
189
190 /* Standard call to check for call compatibility. */
191 IF NOT FND_API.Compatible_API_Call (c_api_version,
192 p_api_version,
193 c_api_name,
194 pc_pkg_name)
195 THEN
196 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
197 END IF;
198
199 -- Validate the OA framework parameters, but ignore p_commit
200 -- since the engine always performs a commit after each dimension
201 Validate_OA_Params (
202 p_init_msg_list => p_init_msg_list,
203 p_commit => p_commit,
204 p_encoded => p_encoded,
205 x_return_status => x_return_status);
206
207 IF (x_return_status <> c_success)
208 THEN
209 FND_MSG_PUB.Count_and_Get(
210 p_encoded => c_false,
211 p_count => x_msg_count,
212 p_data => x_msg_data);
213 RETURN;
214 END IF;
215
216 -- Checking if the specified object_definition is a Dimension Snapshot rule def
217 -- and getting the object_id for it
218 SELECT O.object_id
219 INTO v_object_id
220 FROM fem_object_definition_b D, fem_object_catalog_b O
221 WHERE D.object_definition_id = p_dim_snapshot_obj_def_id
222 AND D.object_id = O.object_id
223 AND O.object_type_code = 'DIMENSION_SNAPSHOT';
224
225 IF v_count = 0 THEN
226 RAISE e_invalid_obj_def_id;
227 END IF;
228
229
230 -- get sysdate for insert time
231 SELECT sysdate
232 INTO v_sysdate
233 FROM dual;
234
235 -- build the delete statement for the target DSNP VERS table
236 v_sql_vers_delete_stmt :=
237 'DELETE FROM fem_dsnp_dim_attr_vrs_tl '||
238 ' WHERE created_by_object_id = '||v_object_id;
239
240 FEM_ENGINES_PKG.Tech_Message
241 (p_severity => pc_log_level_1,
242 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name||'.v_sql_vers_delete_stmt',
243 p_msg_text => v_sql_vers_delete_stmt);
244
245
246 -- build the insert statement for the DSNP VERS table
247 v_sql_vers_insert_stmt :=
248 'INSERT INTO fem_dsnp_dim_attr_vrs_tl '||
249 '(created_by_object_id'||
250 ', version_id'||
251 ', language'||
252 ', source_lang'||
253 ', version_name'||
254 ', description'||
255 ', created_by'||
256 ', creation_date'||
257 ', last_updated_by'||
258 ', last_update_date'||
259 ', last_update_login)'||
260 ' SELECT '||v_object_id||
261 ', V.version_id'||
262 ', V.language'||
263 ', V.source_lang'||
264 ', V.version_name'||
265 ', V.description'||
266 ' ,'||c_user_id||
267 ' ,:b_v_sysdate'||
268 ' ,'||c_user_id||
269 ' ,:b_v_sysdate'||
270 ' ,'||c_login_id||
271 ' FROM fem_dim_attr_Versions_tl V, fem_dsnp_rule_dims R'||
272 ', fem_dim_attributes_b A ,fem_dim_attr_versions_b VB'||
273 ' WHERE VB.default_version_flag = ''Y'''||
274 ' AND VB.personal_flag = ''N'''||
275 ' AND V.version_id = VB.version_id'||
276 ' AND VB.attribute_id = A.attribute_id'||
277 ' AND A.dimension_id = R.dimension_id'||
278 ' AND R.dim_snapshot_obj_def_id = '||p_dim_snapshot_obj_def_id;
279
280 FEM_ENGINES_PKG.Tech_Message
281 (p_severity => pc_log_level_1,
282 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name||'.v_sql_vers_insert_stmt',
283 p_msg_text => v_sql_vers_insert_stmt);
284
285 -- Delete/Insert the version data for FEM_DSNP_DIM_ATTR_VERS_TL
286 EXECUTE IMMEDIATE v_sql_vers_delete_stmt;
287 COMMIT;
288
289 FEM_ENGINES_PKG.Tech_Message
290 (p_severity => pc_log_level_1,
291 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
292 p_msg_text => 'after version delete');
293
294 EXECUTE IMMEDIATE v_sql_vers_insert_stmt
295 USING v_sysdate
296 ,v_sysdate;
297 COMMIT;
298
299 FEM_ENGINES_PKG.Tech_Message
300 (p_severity => pc_log_level_1,
301 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
302 p_msg_text => 'after version insert');
303
304
305 FOR dim IN c1_attr_dims LOOP
306
307 -- build delete statement for the target DNSP ATTR table
308 v_sql_delete_stmt :=
309 'DELETE FROM '||dim.dsnp_attribute_table_name||
310 ' WHERE created_by_object_id = '||v_object_id;
311
312
313 -- build insert statement for the target DSNP ATTR table
314 IF dim.value_set_required_flag = 'Y' THEN
315 v_sql_insert_stmt :=
316 'INSERT INTO '||dim.dsnp_attribute_table_name||
317 ' (created_by_object_id'||
318 ' ,attribute_id'||
319 ' ,version_id'||
320 ' ,'||dim.member_col||
321 ' ,value_set_id'||
322 ' ,dim_attribute_numeric_member'||
323 ' ,dim_attribute_value_set_id'||
324 ' ,dim_attribute_varchar_member'||
325 ' ,number_assign_value'||
326 ' ,varchar_assign_value'||
327 ' ,date_assign_value'||
328 ' ,created_by'||
329 ' ,creation_date'||
330 ' ,last_updated_by'||
331 ' ,last_update_date'||
332 ' ,last_update_login)'||
333 ' SELECT '||v_object_id||
334 ' ,A.attribute_id'||
335 ' ,A.version_id'||
336 ' ,A.'||dim.member_col||
337 ' ,A.value_set_id'||
338 ' ,A.dim_attribute_numeric_member'||
339 ' ,A.dim_attribute_value_set_id'||
340 ' ,A.dim_attribute_varchar_member'||
341 ' ,A.number_assign_value'||
342 ' ,A.varchar_assign_value'||
343 ' ,A.date_assign_value'||
344 ' ,'||c_user_id||
345 ' ,:b_v_sysdate'||
346 ' ,'||c_user_id||
347 ' ,:b_v_sysdate'||
348 ' ,'||c_login_id||
349 ' FROM '||dim.attribute_table_name||' A'||
350 ', fem_dim_attr_versions_b V'||
351 ','||dim.member_b_table_name||' B'||
352 ' WHERE V.version_id = A.version_id'||
353 ' AND V.default_version_flag = ''Y'''||
354 ' AND A.'||dim.member_col||' = B.'||dim.member_col||
355 ' AND A.value_set_id = B.value_set_id'||
356 ' AND B.personal_flag = ''N''';
357 ELSE
358 v_sql_insert_stmt :=
359 'INSERT INTO '||dim.dsnp_attribute_table_name||
360 '(created_by_object_id'||
361 ' ,attribute_id'||
362 ' ,version_id'||
363 ' ,'||dim.member_col||
364 ' ,dim_attribute_numeric_member'||
365 ' ,dim_attribute_value_set_id'||
366 ' ,dim_attribute_varchar_member'||
367 ' ,number_assign_value'||
368 ' ,varchar_assign_value'||
369 ' ,date_assign_value'||
370 ' ,created_by'||
371 ' ,creation_date'||
372 ' ,last_updated_by'||
373 ' ,last_update_date'||
374 ' ,last_update_login)'||
375 ' SELECT '||v_object_id||
376 ' ,A.attribute_id'||
377 ' ,A.version_id'||
378 ' ,A.'||dim.member_col||
379 ' ,A.dim_attribute_numeric_member'||
380 ' ,A.dim_attribute_value_set_id'||
381 ' ,A.dim_attribute_varchar_member'||
382 ' ,A.number_assign_value'||
383 ' ,A.varchar_assign_value'||
384 ' ,A.date_assign_value'||
385 ' ,'||c_user_id||
386 ' ,:b_v_sysdate'||
387 ' ,'||c_user_id||
388 ' ,:b_v_sysdate'||
389 ' ,'||c_login_id||
390 ' FROM '||dim.attribute_table_name||' A'||
391 ', fem_dim_attr_versions_b V'||
392 ','||dim.member_b_table_name||' B'||
393 ' WHERE V.version_id = A.version_id'||
394 ' AND V.default_version_flag = ''Y'''||
395 ' AND A.'||dim.member_col||' = B.'||dim.member_col||
396 ' AND B.personal_flag = ''N''';
397 END IF;
398
399 FEM_ENGINES_PKG.Tech_Message
400 (p_severity => pc_log_level_1,
401 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name||'.v_sql_insert_stmt',
402 p_msg_text => v_sql_insert_stmt);
403
404
405 -- delete from the target table for the object_id
406 EXECUTE IMMEDIATE v_sql_delete_stmt;
407 COMMIT;
408
409 FEM_ENGINES_PKG.Tech_Message
410 (p_severity => pc_log_level_1,
411 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
412 p_msg_text => 'after attribute delete');
413
414
415 -- insert attr rows into the target table
416 EXECUTE IMMEDIATE v_sql_insert_stmt
417 USING v_sysdate
418 ,v_sysdate;
419 COMMIT;
420
421
422 END LOOP;
423
424 EXCEPTION
425
426 WHEN e_invalid_obj_def_id THEN
427
428 x_return_status := c_error;
429 FEM_ENGINES_PKG.Put_Message(
430 p_app_name => 'FEM',
431 p_msg_name => 'FEM_DSNP_INVALID_OBJ_DEF',
432 p_token1 => 'OBJDEF_ID',
433 p_value1 => p_dim_snapshot_obj_def_id);
434 x_return_status := c_error;
435
436 FND_MSG_PUB.Count_And_Get
437 (p_count => x_msg_count,
438 p_data => x_msg_data);
439
440 WHEN OTHERS THEN
441 /* Unexpected exceptions */
442 x_return_status := c_error;
443 gv_prg_msg := SQLERRM;
444 gv_callstack := dbms_utility.format_call_stack;
445
446 /* Log the call stack and the Oracle error message to
447 ** FND_LOG with the "unexpected exception" severity level. */
448
449 FEM_ENGINES_PKG.Tech_Message
450 (p_severity => pc_log_level_unexpected,
451 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
452 p_msg_text => gv_prg_msg);
453
454 FEM_ENGINES_PKG.Tech_Message
455 (p_severity => pc_log_level_unexpected,
456 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
457 p_msg_text => gv_callstack);
458
459 /* Log the Oracle error message to the stack. */
460 FEM_ENGINES_PKG.put_message(p_app_name =>'FEM',
461 p_msg_name => 'FEM_UNEXPECTED_ERROR',
462 P_TOKEN1 => 'ERR_MSG',
463 P_VALUE1 => gv_prg_msg);
464
465
466
467 END Main;
468
469 END fem_dim_snapshot_eng_pkg;