[Home] [Help]
PACKAGE BODY: APPS.FEM_DIM_SNAPSHOT_UTIL_PKG
Source
1 PACKAGE BODY FEM_DIM_SNAPSHOT_UTIL_PKG AS
2 --$Header: fem_dimsnap_utl.plb 120.0 2005/10/19 19:25:45 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_utl.plb
10 |
11 | NAME fem_dim_snapshot_utl_pkg
12 |
13 | DESCRIPTION
14 |
15 | Package body for fem_dim_snapshot_utl_pkg.
16 | For more information about the purpose of this package, please refer
17 | to the package spec.
18 |
19 | HISTORY
20 |
21 | 1-JUL-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
32 -----------------------
33 -- Package Variables --
34 -----------------------
35 gv_prg_msg VARCHAR2(2000);
36 gv_callstack VARCHAR2(2000);
37
38
39 PROCEDURE Validate_OA_Params (
40 p_init_msg_list IN VARCHAR2,
41 p_commit IN VARCHAR2,
42 p_encoded IN VARCHAR2,
43 x_return_status OUT NOCOPY VARCHAR2
44 );
45
46
47
48 PROCEDURE Validate_OA_Params (
49 p_init_msg_list IN VARCHAR2,
50 p_commit IN VARCHAR2,
51 p_encoded IN VARCHAR2,
52 x_return_status OUT NOCOPY VARCHAR2
53 )
54 IS
55 e_bad_p_api_ver EXCEPTION;
56 e_bad_p_init_msg_list EXCEPTION;
57 e_bad_p_commit EXCEPTION;
58 e_bad_p_encoded EXCEPTION;
59 BEGIN
60
61 x_return_status := c_success;
62
63 CASE p_init_msg_list
64 WHEN c_false THEN NULL;
65 WHEN c_true THEN
66 FND_MSG_PUB.Initialize;
67 ELSE RAISE e_bad_p_init_msg_list;
68 END CASE;
69
70 CASE p_encoded
71 WHEN c_false THEN NULL;
72 WHEN c_true THEN NULL;
73 ELSE RAISE e_bad_p_encoded;
74 END CASE;
75
76 CASE p_commit
77 WHEN c_false THEN NULL;
78 WHEN c_true THEN NULL;
79 ELSE RAISE e_bad_p_commit;
80 END CASE;
81
82 EXCEPTION
83 WHEN e_bad_p_init_msg_list THEN
84 FEM_ENGINES_PKG.Put_Message(
85 p_app_name => 'FEM',
86 p_msg_name => 'FEM_BAD_P_INIT_MSG_LIST_ERR');
87 x_return_status := c_error;
88
89 WHEN e_bad_p_encoded THEN
90 FEM_ENGINES_PKG.Put_Message(
91 p_app_name => 'FEM',
92 p_msg_name => 'FEM_BAD_P_ENCODED_ERR');
93 x_return_status := c_error;
94
95 WHEN e_bad_p_commit THEN
96 FEM_ENGINES_PKG.Put_Message(
97 p_app_name => 'FEM',
98 p_msg_name => 'FEM_BAD_P_COMMIT_ERR');
99 x_return_status := c_error;
100
101 END Validate_OA_Params;
102
103
104 /*===========================================================================+
105 | PROCEDURE
106 | ADD_DIMENSION
107 |
108 | DESCRIPTION
109 | Procedure for adding a dimension to a Dimension Snapshot
110 | Rule definition
111 |
112 | SCOPE - PUBLIC
113 |
114 | NOTES
115 | If the dimension to be added already exists in the rule definition,
116 | the procedure does nothing (i.e., - it still returns success).
117 |
118 |
119 | MODIFICATION HISTORY
120 | Rob Flippo 01-JUL-05 Created
121 +===========================================================================*/
122
123
124 PROCEDURE Add_Dimension (
125 x_return_status OUT NOCOPY VARCHAR2,
126 x_msg_count OUT NOCOPY NUMBER,
127 x_msg_data OUT NOCOPY VARCHAR2,
128 p_api_version IN NUMBER DEFAULT c_api_version,
129 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
130 p_commit IN VARCHAR2 DEFAULT c_false,
131 p_encoded IN VARCHAR2 DEFAULT c_true,
132 p_dim_snapshot_obj_def_id IN NUMBER,
133 p_dimension_varchar_label IN VARCHAR2
134 )
135
136 IS
137
138 e_invalid_dim EXCEPTION;
139 e_invalid_obj_def_id EXCEPTION;
140 e_invalid_oa_parms EXCEPTION;
141 v_count NUMBER;
142 v_dimension_id NUMBER;
143
144 c_api_name CONSTANT VARCHAR2(30) := 'add_dimension';
145
146
147 BEGIN
148
149 /* Standard Start of API savepoint */
150 SAVEPOINT add_dimension;
151
152 x_return_status := c_success;
153 /* Standard call to check for call compatibility. */
154 IF NOT FND_API.Compatible_API_Call (c_api_version,
155 p_api_version,
156 c_api_name,
157 pc_pkg_name)
158 THEN
159 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
160 END IF;
161
162
163 Validate_OA_Params (
164 p_init_msg_list => p_init_msg_list,
165 p_commit => p_commit,
166 p_encoded => p_encoded,
167 x_return_status => x_return_status);
168
169 IF (x_return_status <> c_success) THEN
170 RAISE e_invalid_oa_parms;
171 END IF;
172
173 -- Checking if the specified object_definition is a Dimension Snapshot rule def
174 SELECT count(*)
175 INTO v_count
176 FROM fem_object_definition_b D, fem_object_catalog_b O
177 WHERE D.object_definition_id = p_dim_snapshot_obj_def_id
178 AND D.object_id = O.object_id
179 AND O.object_type_code = 'DIMENSION_SNAPSHOT';
180
181 IF v_count = 0 THEN
182 RAISE e_invalid_obj_def_id;
183 END IF;
184
185 -- Checking if the dimension passed in is an actual dimension in fem_xdim_dimensions
186 BEGIN
187 SELECT dimension_id
188 INTO v_dimension_id
189 FROM fem_xdim_dimensions_vl
190 WHERE dimension_varchar_label = p_dimension_varchar_label;
191 EXCEPTION
192 WHEN no_data_found THEN raise e_invalid_dim;
193 END;
194
195 BEGIN
196 INSERT INTO fem_dsnp_rule_dims (
197 DIM_SNAPSHOT_OBJ_DEF_ID
198 ,DIMENSION_ID
199 ,CREATION_DATE
200 ,CREATED_BY
201 ,LAST_UPDATED_BY
202 ,LAST_UPDATE_DATE
203 ,LAST_UPDATE_LOGIN )
204 SELECT p_dim_snapshot_obj_def_id
205 ,v_dimension_id
206 ,sysdate
207 ,c_user_id
208 ,c_user_id
209 ,sysdate
210 ,c_login_id
211 FROM dual;
212 EXCEPTION
213 WHEN dup_val_on_index THEN null;
214 END;
215
216 IF FND_API.To_Boolean( p_commit ) THEN
217 COMMIT WORK;
218 END IF;
219
220
221 EXCEPTION
222 WHEN e_invalid_oa_parms THEN
223 ROLLBACK TO add_dimension;
224 x_return_status := c_error;
225
226 FND_MSG_PUB.Count_and_Get(
227 p_encoded => p_encoded,
228 p_count => x_msg_count,
229 p_data => x_msg_data);
230
231 WHEN e_invalid_obj_def_id THEN
232 ROLLBACK TO add_dimension;
233 x_return_status := c_error;
234 FEM_ENGINES_PKG.Put_Message(
235 p_app_name => 'FEM',
236 p_msg_name => 'FEM_DSNP_INVALID_OBJ_DEF',
237 p_token1 => 'OBJDEF_ID',
238 p_value1 => p_dim_snapshot_obj_def_id);
239 x_return_status := c_error;
240
241 FND_MSG_PUB.Count_and_Get(
242 p_encoded => p_encoded,
243 p_count => x_msg_count,
244 p_data => x_msg_data);
245
246 WHEN e_invalid_dim THEN
247 ROLLBACK TO add_dimension;
248 x_return_status := c_error;
249 FEM_ENGINES_PKG.Put_Message(
250 p_app_name => 'FEM',
251 p_msg_name => 'FEM_XDIM_INVALID_DIM',
252 p_token1 => 'LABEL',
253 p_value1 => p_dimension_varchar_label);
254
255 x_return_status := c_error;
256
257 FND_MSG_PUB.Count_and_Get(
258 p_encoded => p_encoded,
259 p_count => x_msg_count,
260 p_data => x_msg_data);
261
262 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
263 ROLLBACK TO add_dimension;
264 x_return_status := c_error;
265
266 fem_engines_pkg.tech_message(p_severity => pc_log_level_unexpected,
267 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
268 p_msg_name => 'FEM_BAD_P_API_VER_ERR'
269 ,p_token1 => 'VALUE'
270 ,p_value1 => p_api_version
271 ,p_trans1 => 'N');
272
273 FND_MSG_PUB.Count_and_Get(
274 p_encoded => p_encoded,
275 p_count => x_msg_count,
276 p_data => x_msg_data);
277
278 WHEN OTHERS THEN
279 /* Unexpected exceptions */
280 x_return_status := c_error;
281 gv_prg_msg := SQLERRM;
282 gv_callstack := dbms_utility.format_call_stack;
283
284 /* Log the call stack and the Oracle error message to
285 ** FND_LOG with the "unexpected exception" severity level. */
286
287 FEM_ENGINES_PKG.Tech_Message
288 (p_severity => pc_log_level_unexpected,
289 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
290 p_msg_text => gv_prg_msg);
291
292 FEM_ENGINES_PKG.Tech_Message
293 (p_severity => pc_log_level_unexpected,
294 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
295 p_msg_text => gv_callstack);
296
297 /* Log the Oracle error message to the stack. */
298 FEM_ENGINES_PKG.put_message(p_app_name =>'FEM',
299 p_msg_name => 'FEM_UNEXPECTED_ERROR',
300 P_TOKEN1 => 'ERR_MSG',
301 P_VALUE1 => gv_prg_msg);
302 ROLLBACK TO add_dimension;
303
304 FND_MSG_PUB.Count_and_Get(
305 p_encoded => p_encoded,
306 p_count => x_msg_count,
307 p_data => x_msg_data);
308
309 END Add_Dimension;
310
311 /*===========================================================================+
312 | PROCEDURE
313 | REMOVE_DIMENSION
314 |
315 | DESCRIPTION
316 | Procedure for removing a dimension to a Dimension Snapshot
317 | Rule definition
318 |
319 | SCOPE - PUBLIC
320 |
321 | NOTES
322 | If the dimension to be removed doesn't exist in the rule definition,
323 | the procedure does nothing (i.e., - it still returns success).
324 |
325 | MODIFICATION HISTORY
326 | Rob Flippo 01-JUL-05 Created
327 +===========================================================================*/
328
329 PROCEDURE Remove_Dimension (
330 x_return_status OUT NOCOPY VARCHAR2,
331 x_msg_count OUT NOCOPY NUMBER,
332 x_msg_data OUT NOCOPY VARCHAR2,
333 p_api_version IN NUMBER DEFAULT c_api_version,
334 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
335 p_commit IN VARCHAR2 DEFAULT c_false,
336 p_encoded IN VARCHAR2 DEFAULT c_true,
337 p_dim_snapshot_obj_def_id IN NUMBER,
338 p_dimension_varchar_label IN VARCHAR2
339 )
340
341 IS
342
343 e_invalid_dim EXCEPTION;
344 e_invalid_obj_def_id EXCEPTION;
345 e_invalid_oa_parms EXCEPTION;
346 v_count NUMBER;
347 v_dimension_id NUMBER;
348
349 c_api_name CONSTANT VARCHAR2(30) := 'remove_dimension';
350
351
352
353 BEGIN
354 /* Standard Start of API savepoint */
355 SAVEPOINT remove_dimension;
356
357
358 x_return_status := c_success;
359
360 /* Standard call to check for call compatibility. */
361 IF NOT FND_API.Compatible_API_Call (c_api_version,
362 p_api_version,
363 c_api_name,
364 pc_pkg_name)
365 THEN
366 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
367 END IF;
368
369
370 Validate_OA_Params (
371 p_init_msg_list => p_init_msg_list,
372 p_commit => p_commit,
373 p_encoded => p_encoded,
374 x_return_status => x_return_status);
375
376 IF (x_return_status <> c_success) THEN
377 RAISE e_invalid_oa_parms;
378 END IF;
379
380 -- Checking if the specified object_definition is a Dimension Snapshot rule def
381 SELECT count(*)
382 INTO v_count
383 FROM fem_object_definition_b D, fem_object_catalog_b O
384 WHERE D.object_definition_id = p_dim_snapshot_obj_def_id
385 AND D.object_id = O.object_id
386 AND O.object_type_code = 'DIMENSION_SNAPSHOT';
387
388 IF v_count = 0 THEN
389 RAISE e_invalid_obj_def_id;
390 END IF;
391
392 -- Checking if the dimension passed in is an actual dimension in fem_xdim_dimensions
393 BEGIN
394 SELECT dimension_id
395 INTO v_dimension_id
396 FROM fem_xdim_dimensions_vl
397 WHERE dimension_varchar_label = p_dimension_varchar_label;
398 EXCEPTION
399 WHEN no_data_found THEN raise e_invalid_dim;
400 END;
401
402 DELETE FROM fem_dsnp_rule_dims
403 WHERE dim_snapshot_obj_def_id = p_dim_snapshot_obj_def_id
404 AND dimension_id = v_dimension_id;
405
406 IF FND_API.To_Boolean( p_commit ) THEN
407 COMMIT WORK;
408 END IF;
409
410 EXCEPTION
411 WHEN e_invalid_oa_parms THEN
412 ROLLBACK TO remove_dimension;
413 x_return_status := c_error;
414
415 FND_MSG_PUB.Count_and_Get(
416 p_encoded => p_encoded,
417 p_count => x_msg_count,
418 p_data => x_msg_data);
419
420 WHEN e_invalid_obj_def_id THEN
421 ROLLBACK TO remove_dimension;
422 x_return_status := c_error;
423 FEM_ENGINES_PKG.Put_Message(
424 p_app_name => 'FEM',
425 p_msg_name => 'FEM_DSNP_INVALID_OBJ_DEF',
426 p_token1 => 'OBJDEF_ID',
427 p_value1 => p_dim_snapshot_obj_def_id);
428 x_return_status := c_error;
429
430 FND_MSG_PUB.Count_and_Get(
431 p_encoded => p_encoded,
432 p_count => x_msg_count,
433 p_data => x_msg_data);
434
435 WHEN e_invalid_dim THEN
436 ROLLBACK TO remove_dimension;
437 x_return_status := c_error;
438 FEM_ENGINES_PKG.Put_Message(
439 p_app_name => 'FEM',
440 p_msg_name => 'FEM_XDIM_INVALID_DIM',
441 p_token1 => 'LABEL',
442 p_value1 => p_dimension_varchar_label);
443
444 x_return_status := c_error;
445
446 FND_MSG_PUB.Count_and_Get(
447 p_encoded => p_encoded,
448 p_count => x_msg_count,
449 p_data => x_msg_data);
450
451 WHEN OTHERS THEN
452 /* Unexpected exceptions */
453 x_return_status := c_error;
454 gv_prg_msg := SQLERRM;
455 gv_callstack := dbms_utility.format_call_stack;
456
457 /* Log the call stack and the Oracle error message to
458 ** FND_LOG with the "unexpected exception" severity level. */
459
460 FEM_ENGINES_PKG.Tech_Message
461 (p_severity => pc_log_level_unexpected,
462 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
463 p_msg_text => gv_prg_msg);
464
465 FEM_ENGINES_PKG.Tech_Message
466 (p_severity => pc_log_level_unexpected,
467 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
468 p_msg_text => gv_callstack);
469
470 /* Log the Oracle error message to the stack. */
471 FEM_ENGINES_PKG.put_message(p_app_name =>'FEM',
472 p_msg_name => 'FEM_UNEXPECTED_ERROR',
473 P_TOKEN1 => 'ERR_MSG',
474 P_VALUE1 => gv_prg_msg);
475 ROLLBACK TO remove_dimension;
476
477 FND_MSG_PUB.Count_and_Get(
478 p_encoded => p_encoded,
479 p_count => x_msg_count,
480 p_data => x_msg_data);
481
482 END Remove_Dimension;
483
484
485 END fem_dim_snapshot_util_pkg;