[Home] [Help]
PACKAGE BODY: APPS.FEM_DIM_GROUPS_UTIL_PKG
Source
1 PACKAGE BODY FEM_DIM_GROUPS_UTIL_PKG AS
2 /* $Header: fem_dimgrp_utl.plb 120.2 2005/07/21 10:09:30 appldev ship $ */
3 /*=======================================================================+
4 Copyright (c) 1995 Oracle Corporation Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | FILENAME
8 | fem_dimgrp_utl.plb
9 |
10 | DESCRIPTION
11 | Utility package for Dimension Groups (also known as "levels")
12 |
13 | MODIFICATION HISTORY
14 | Robert Flippo 06/03/2005 Created
15 | Robert Flippo 07/14/2005 Bug#4494300 OA Compliance issues
16 | Robert Flippo 07/20/2005 Bug#4504983 Changes requested by OGL
17 |
18 *=======================================================================*/
19
20 /* ***********************
21 ** Package constants
22 ** ***********************/
23
24
25 /* ***********************
26 ** Package variables
27 ** ***********************/
28 --dbms_utility.format_call_stack VARCHAR2(2000);
29
30 /* ***********************
31 ** Package exceptions
32 ** ***********************/
33
34 gv_prg_msg VARCHAR2(2000);
35 gv_callstack VARCHAR2(2000);
36
37
38 /*===========================================================================+
39 | PROCEDURE
40 | Create_dim_group
41 |
42 | DESCRIPTION
43 | Creates a new dimension group or returns the identifier
44 | for an existing group if the input parameters
45 | match an existing group
46 |
47 | SCOPE - PRIVATE
48 |
49 |
50 | NOTES
51 | API logic is:
52 | IF user passes in a dimension group display_code that already
53 | exists the API returns the dimension_group_id and dimension_grp_seq
54 | for that group. This is true even when the user provides a sequence
55 | as an input parameter that is different than the dim grp sequence
56 | of the existing group.
57 |
58 | If the dimension group display code does not exist for that dimension,
59 | the API will create it. If the user passes in a sequence that is already
60 | in use, the API will return an error. If the user passes in a sequence
61 | that is not in use, or passes in null for the sequence, the API will
62 | create the group. When null is passed in, it uses max+10 of the
63 | existing dimension group for that dimension as the value for the
64 | sequence.
65 |
66 | MODIFICATION HISTORY
67 | Rob Flippo 06/03/2005 Created
68 | Rob Flippo 07/20/2005 Bug#4504983 Changes requested by OGL
69 +===========================================================================*/
70
71 PROCEDURE create_dim_group (x_dimension_group_id OUT NOCOPY NUMBER
72 ,x_dim_group_sequence OUT NOCOPY NUMBER
73 ,x_msg_count OUT NOCOPY NUMBER
74 ,x_msg_data OUT NOCOPY VARCHAR2
75 ,x_return_status OUT NOCOPY VARCHAR2
76 ,p_api_version IN NUMBER DEFAULT 1.0
77 ,p_init_msg_list IN VARCHAR2 DEFAULT pc_false
78 ,p_commit IN VARCHAR2 DEFAULT pc_false
79 ,p_encoded IN VARCHAR2 DEFAULT pc_true
80 ,p_dimension_varchar_label IN VARCHAR2
81 ,p_dim_group_display_code IN VARCHAR2
82 ,p_dim_group_name IN VARCHAR2
83 ,p_dim_group_description IN VARCHAR2
84 ,p_dim_group_sequence IN NUMBER DEFAULT NULL
85 ,p_time_group_type_code IN VARCHAR2 DEFAULT NULL)
86 IS
87
88 c_api_name CONSTANT VARCHAR2(30) := 'create_dim_group';
89 c_api_version CONSTANT NUMBER := 1.0;
90 v_rowid VARCHAR2(100);
91 v_count NUMBER;
92
93 v_dimension_id NUMBER;
94 v_time_dimension_group_key NUMBER;
95
96
97 -- Exceptions
98 e_invalid_dimension EXCEPTION;
99 e_duplicate_display_code EXCEPTION;
100 e_duplicate_name EXCEPTION;
101 e_duplicate_dimgrp_seq EXCEPTION;
102 e_invalid_time_group_type EXCEPTION;
103 e_dimgrp_already_exists EXCEPTION;
104
105
106 BEGIN
107
108 fem_engines_pkg.tech_message(p_severity => pc_log_level_statement,
109 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
110 p_msg_text => 'Begin. '||
111 ' P_DIMENSION_VARCHAR_LABEL:'||p_dimension_varchar_label||
112 ' P_DIM_GROUP_DISPLAY_CODE:'||p_dim_group_display_code||
113 ' P_DIM_GROUP_NAME:'||p_dim_group_name||
114 ' P_DIM_GROUP_DESCRIPTION:'||p_dim_group_description||
115 ' P_DIM_GROUP_SEQUENCE:'||p_dim_group_sequence||
116 ' P_TIME_GROUP_TYPE_CODE:'||p_time_group_type_code||
117 ' P_COMMIT: '||p_commit);
118
119 /* Standard Start of API savepoint */
120 SAVEPOINT create_dim_group_pub;
121
122 /* Standard call to check for call compatibility. */
123 IF NOT FND_API.Compatible_API_Call (c_api_version,
124 p_api_version,
125 c_api_name,
126 pc_pkg_name)
127 THEN
128 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
129 END IF;
130
131 /* Initialize API return status to success */
132 x_return_status := pc_ret_sts_success;
133 ---------------------------
134 -- Verify the OA parameters
135 ---------------------------
136 FEM_Dimension_Util_Pkg.Validate_OA_Params (
137 p_api_version => c_api_version,
138 p_init_msg_list => p_init_msg_list,
139 p_commit => p_commit,
140 p_encoded => p_encoded,
141 x_return_status => x_return_status);
142
143 IF (x_return_status <> pc_ret_sts_success)
144 THEN
145 FND_MSG_PUB.Count_and_Get(
146 p_encoded => pc_false,
147 p_count => x_msg_count,
148 p_data => x_msg_data);
149 END IF;
150
151 /* Validate that the Dimension Varchar Label support Dimension Groups */
152 BEGIN
153 SELECT B.dimension_id
154 INTO v_dimension_id
155 FROM fem_dimensions_b B, fem_xdim_dimensions X
156 WHERE B.dimension_varchar_label = p_dimension_varchar_label
157 AND B.dimension_id = X.dimension_id
158 AND X.group_use_code IN ('OPTIONAL','REQUIRED');
159
160 EXCEPTION
161 WHEN no_data_found THEN
162 RAISE e_invalid_dimension;
163 END;
164
165 /* check to see if there is an existing dimension_group
166 for the specified dimension, display_code */
167 BEGIN
168 SELECT dimension_group_id,dimension_group_seq
169 INTO x_dimension_group_id, x_dim_group_sequence
170 FROM fem_dimension_grps_b
171 WHERE dimension_id = v_dimension_id
172 AND dimension_group_display_code = p_dim_group_display_code;
173
174 IF x_dimension_group_id IS NOT NULL THEN
175 RAISE e_dimgrp_already_exists;
176 END IF;
177 EXCEPTION
178 WHEN no_data_found THEN null;
179 END;
180
181 /* Validate that the Dimension Group Name does not already exist
182 in any language for the specified dimension*/
183 SELECT count(*)
184 INTO v_count
185 FROM fem_dimension_grps_tl
186 WHERE dimension_group_name = p_dim_group_name
187 AND dimension_id = v_dimension_id;
188
189 IF v_count > 0 THEN
190 RAISE e_duplicate_name;
191 END IF; /* duplicate_name validation */
192
193 /* Validate that the Dimension Group Sequence does not already exist
194 for the specified dimension*/
195 IF p_dim_group_sequence IS NOT NULL THEN
196 SELECT count(*)
197 INTO v_count
198 FROM fem_dimension_grps_b
199 WHERE dimension_group_seq = p_dim_group_sequence
200 AND dimension_id = v_dimension_id;
201
202 IF v_count > 0 THEN
203 RAISE e_duplicate_dimgrp_seq;
204 END IF; /* duplicate_dimgrp_seq */
205 END IF;
206
207 /* For CAL_PERIOD dimension, verify that the TIME_GROUP_TYPE_CODE exists
208 and get the time dimension group key sequence number*/
209 IF p_dimension_varchar_label = 'CAL_PERIOD' THEN
210 SELECT count(*)
211 INTO v_count
212 FROM fem_time_group_types_b
213 WHERE time_group_type_code = p_time_group_type_code
214 AND ENABLED_FLAG = 'Y'
215 AND PERSONAL_FLAG = 'N';
216
217 IF v_count = 0 THEN
218 RAISE e_invalid_time_group_type;
219 ELSE
220 SELECT fem_time_dimension_group_key_s.nextval
221 INTO v_time_dimension_group_key
222 FROM dual;
223 END IF; /* time group type validation */
224 END IF;
225
226 SELECT fem_dimension_grps_b_s.nextval
227 INTO x_dimension_group_id
228 FROM dual;
229
230
231 -- Get the next dimension_group_seq value if the user passes in null
232 IF p_dim_group_sequence IS NULL THEN
233 SELECT nvl(max(dimension_group_seq)+100,1000)
234 INTO x_dim_group_sequence
235 FROM fem_dimension_grps_b
236 WHERE dimension_id = v_dimension_id;
237 ELSE x_dim_group_sequence := p_dim_group_sequence;
238 END IF;
239
240 FEM_DIMENSION_GRPS_PKG.INSERT_ROW(
241 X_ROWID => v_rowid
242 ,X_DIMENSION_GROUP_ID => x_dimension_group_id
243 ,X_TIME_DIMENSION_GROUP_KEY => v_time_dimension_group_key
244 ,X_DIMENSION_ID => v_dimension_id
245 ,X_DIMENSION_GROUP_SEQ => x_dim_group_sequence
246 ,X_TIME_GROUP_TYPE_CODE => p_time_group_type_code
247 ,X_READ_ONLY_FLAG => 'N'
248 ,X_OBJECT_VERSION_NUMBER => 1
249 ,X_PERSONAL_FLAG => 'N'
250 ,X_ENABLED_FLAG => 'Y'
251 ,X_DIMENSION_GROUP_DISPLAY_CODE => p_dim_group_display_code
252 ,X_DIMENSION_GROUP_NAME => p_dim_group_name
253 ,X_DESCRIPTION => p_dim_group_description
254 ,X_CREATION_DATE => sysdate
255 ,X_CREATED_BY => pc_user_id
256 ,X_LAST_UPDATE_DATE => sysdate
257 ,X_LAST_UPDATED_BY => pc_user_id
258 ,X_LAST_UPDATE_LOGIN => pc_last_update_login);
259
260
261 IF FND_API.To_Boolean( p_commit ) THEN
262 COMMIT WORK;
263 END IF;
264
265
266 EXCEPTION
267 WHEN e_dimgrp_already_exists THEN null;
268
269
270 WHEN e_invalid_dimension THEN
271 ROLLBACK TO create_dim_group_pub;
272 x_return_status := pc_ret_sts_error;
273 x_dimension_group_id := -1;
274 x_dim_group_sequence := -1;
275
276 fem_engines_pkg.put_message(p_app_name =>'FEM'
277 ,p_msg_name =>'FEM_DIMGRP_UTIL_INVALID_DIM'
278 ,p_token1 => 'DIM_LABEL'
279 ,p_value1 => p_dimension_varchar_label);
280
281 FND_MSG_PUB.Count_And_Get
282 (p_encoded => p_encoded,
283 p_count => x_msg_count,
284 p_data => x_msg_data);
285
286 WHEN e_duplicate_display_code THEN
287 ROLLBACK TO create_dim_group_pub;
288 x_return_status := pc_ret_sts_error;
289 fem_engines_pkg.put_message(p_app_name =>'FEM'
290 ,p_msg_name =>'FEM_DIMGRP_UTIL_DUP_DC'
291 ,p_token1 => 'DISPLAY_CODE'
292 ,p_value1 => p_dim_group_display_code
293 ,p_token2 => 'DIM_LABEL'
294 ,p_value2 => p_dimension_varchar_label);
295
296 FND_MSG_PUB.Count_And_Get
297 (p_encoded => p_encoded,
298 p_count => x_msg_count,
299 p_data => x_msg_data);
300
301 WHEN e_duplicate_name THEN
302 ROLLBACK TO create_dim_group_pub;
303 x_return_status := pc_ret_sts_error;
304 x_dimension_group_id := -1;
305 x_dim_group_sequence := -1;
306
307 fem_engines_pkg.put_message(p_app_name =>'FEM'
308 ,p_msg_name =>'FEM_DIMGRP_UTIL_DUP_NAME'
309 ,p_token1 => 'NAME'
310 ,p_value1 => p_dim_group_name
311 ,p_token2 => 'DIM_LABEL'
312 ,p_value2 => p_dimension_varchar_label);
313
314 FND_MSG_PUB.Count_And_Get
315 (p_encoded => p_encoded,
316 p_count => x_msg_count,
317 p_data => x_msg_data);
318
319 WHEN e_duplicate_dimgrp_seq THEN
320 ROLLBACK TO create_dim_group_pub;
321 x_return_status := pc_ret_sts_error;
322 x_dimension_group_id := -1;
323 x_dim_group_sequence := -1;
324
325 fem_engines_pkg.put_message(p_app_name =>'FEM'
326 ,p_msg_name =>'FEM_DIMGRP_UTIL_DUP_SEQ'
327 ,p_token1 => 'SEQUENCE'
328 ,p_value1 => p_dim_group_sequence
329 ,p_token2 => 'DIM_LABEL'
330 ,p_value2 => p_dimension_varchar_label);
331
332 FND_MSG_PUB.Count_And_Get
333 (p_encoded => p_encoded,
334 p_count => x_msg_count,
335 p_data => x_msg_data);
336
337 WHEN e_invalid_time_group_type THEN
338 ROLLBACK TO create_dim_group_pub;
339 x_return_status := pc_ret_sts_error;
340 fem_engines_pkg.put_message(p_app_name =>'FEM'
341 ,p_msg_name =>'FEM_DIMGRP_UTIL_INVALID_TIMGRP'
342 ,p_token1 => 'TIME_GROUP'
343 ,p_value1 => p_time_group_type_code);
344
345 FND_MSG_PUB.Count_And_Get
346 (p_encoded => p_encoded,
347 p_count => x_msg_count,
348 p_data => x_msg_data);
349
350
351 WHEN OTHERS THEN
352 /* Unexpected exceptions */
353 x_return_status := pc_ret_sts_unexp_error;
354 gv_prg_msg := gv_prg_msg;
355 gv_callstack := gv_callstack;
356
357 /* Log the call stack and the Oracle error message to
358 ** FND_LOG with the "unexpected exception" severity level. */
359
360 FEM_ENGINES_PKG.Tech_Message
361 (p_severity => pc_log_level_unexpected,
362 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
363 p_msg_text => gv_prg_msg);
364
365 FEM_ENGINES_PKG.Tech_Message
366 (p_severity => pc_log_level_unexpected,
367 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
368 p_msg_text => gv_callstack);
369
370 /* Log the Oracle error message to the stack. */
371 FEM_ENGINES_PKG.put_message(p_app_name =>'FEM',
372 p_msg_name => 'FEM_UNEXPECTED_ERROR',
373 P_TOKEN1 => 'ERR_MSG',
374 P_VALUE1 => gv_prg_msg);
375 ROLLBACK TO create_dim_group_pub;
376
377 FND_MSG_PUB.Count_And_Get
378 (p_encoded => p_encoded,
379 p_count => x_msg_count,
380 p_data => x_msg_data);
381
382 END create_dim_group;
383
384
385 END FEM_DIM_GROUPS_UTIL_PKG;