[Home] [Help]
PACKAGE BODY: APPS.FEM_DIM_ATTRIBUTES_UTIL_PKG
Source
1 PACKAGE BODY FEM_DIM_ATTRIBUTES_UTIL_PKG AS
2 /* $Header: fem_dimattr_utl.plb 120.3 2006/08/16 22:10:06 rflippo ship $ */
3 /*=======================================================================+
4 Copyright (c) 1995 Oracle Corporation Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | FILENAME
8 | fem_dimattr_utl.plb
9 |
10 | DESCRIPTION
11 | Creates body for package used to create user defined attributes
12 |
13 | MODIFICATION HISTORY
14 | Robert Flippo 05/27/2004 Created
15 |
16 | Robert Flippo 06/30/2004 Fix problem with attr_dim_id being
17 | inserted as the dim_id
18 | Rob Flippo 03/21/2005 Bug#4215137 Added x_user_assign_allowed_flag to the
19 | INSERT_ROW call for FEM_DIM_ATTRIBUTES_PKG. All
20 | attributes created from this pkg will have 'Y'
21 | for this flag (since only seeded attributes can be
22 | 'N')
23 |
24 | Tim Moore 05/02/2005 Bug#4036498 Added following functions:
25 | Get_Dim_Attribute_Value
26 | Get_Dim_Attr_Value_Set
27 |
28 | Tim Moore 05/24/2005 Bug#4050785 Added following procedures:
29 | New_Dim_Attr_Version
30 | New_Dim_Attr_Default
31 | Rob Flippo 08/16/2006 Bug#5463488 overlad get_dim_attribute_value
32 | function so can be called via sql stmt
33 *=======================================================================*/
34
35 /* ***********************
36 ** Package constants
37 ** ***********************/
38
39
40 /* ***********************
41 ** Package variables
42 ** ***********************/
43 --dbms_utility.format_call_stack VARCHAR2(2000);
44
45 /* ***********************
46 ** Package exceptions
47 ** ***********************/
48 e_invalid_dimension EXCEPTION;
49 e_invalid_attr_dimension EXCEPTION;
50 e_existing_attr_varchar_label EXCEPTION;
51 e_invalid_order_type EXCEPTION;
52 e_existing_attr_name EXCEPTION;
53 e_invalid_attr_data_type_code EXCEPTION;
54
55 gv_prg_msg VARCHAR2(2000);
56 gv_callstack VARCHAR2(2000);
57
58
59 /*************************************************************************
60
61 Create Attibute
62
63 *************************************************************************/
64
65 PROCEDURE create_attribute (x_attribute_id OUT NOCOPY NUMBER
66 ,x_msg_count OUT NOCOPY NUMBER
67 ,x_msg_data OUT NOCOPY VARCHAR2
68 ,x_return_status OUT NOCOPY VARCHAR2
69 ,p_api_version IN NUMBER
70 ,p_commit IN VARCHAR2
71 ,p_attr_varchar_label IN VARCHAR2
72 ,p_attr_name IN VARCHAR2
73 ,p_attr_description IN VARCHAR2
74 ,p_dimension_varchar_label IN VARCHAR2
75 ,p_allow_mult_versions_flag IN VARCHAR2
76 ,p_queryable_for_reporting_flag IN VARCHAR2
77 ,p_use_inheritance_flag IN VARCHAR2
78 ,p_attr_order_type_code IN VARCHAR2
79 ,p_allow_mult_assign_flag IN VARCHAR2
80 ,p_personal_flag IN VARCHAR2
81 ,p_attr_data_type_code IN VARCHAR2
82 ,p_attr_dimension_varchar_label IN VARCHAR2
83 ,p_version_display_code IN VARCHAR2
84 ,p_version_name IN VARCHAR2
85 ,p_version_description IN VARCHAR2)
86 IS
87
88 /* ==========================================================================
89 ** This procedure creates a new user defined attribute in the FEM xDimension metadata.
90 ** It also creates a "default" attribute version for the attribute
91 ** ==========================================================================
92 ** ==========================================================================*/
93 c_api_name CONSTANT VARCHAR2(30) := 'create_attribute';
94 c_api_version CONSTANT NUMBER := 1.0;
95 v_rowid VARCHAR2(100);
96 v_count NUMBER;
97
98 v_dimension_id NUMBER;
99 v_attribute_id NUMBER;
100 v_allow_mult_versions_flag VARCHAR2(1);
101 v_queryable_for_reporting_flag VARCHAR2(1);
102 v_use_inheritance_flag VARCHAR2(1);
103 v_allow_mult_assign_flag VARCHAR2(1);
104 v_personal_flag VARCHAR2(1);
105 v_attr_required_flag VARCHAR2(1);
106 v_attr_data_type_code VARCHAR2(30);
107 v_attr_value_column_name VARCHAR2(30);
108 v_attr_dimension_id NUMBER;
109
110 v_version_id NUMBER;
111
112 BEGIN
113
114 fem_engines_pkg.tech_message(p_severity => pc_log_level_statement,
115 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
116 p_msg_text => 'Begin. P_ATTR_VARCHAR_LABEL: '||p_attr_varchar_label||
117 ' P_ATTR_NAME:'||p_attr_name||
118 ' P_ATTR_DESCRIPTION:'||p_attr_description||
119 ' P_DIMENSION_VARCHAR_LABEL:'||p_dimension_varchar_label||
120 ' P_ALLOW_MULT_VERSIONS_FLAG:'||p_allow_mult_versions_flag||
121 ' P_QUERYABLE_FOR_REPORTING_FLAG:'||p_queryable_for_reporting_flag||
122 ' P_USE_INHERITANCE_FLAG:'||p_use_inheritance_flag||
123 ' P_ATTR_ORDER_TYPE_CODE:'||p_attr_order_type_code||
124 ' P_ALLOW_MULT_ASSIGN_FLAG:'||p_allow_mult_assign_flag||
125 ' P_ATTR_DATA_TYPE_CODE:'||p_attr_data_type_code||
126 ' P_ATTR_DIMENSION_VARCHAR_LABEL:'||p_attr_dimension_varchar_label||
127 ' P_VERSION_DISPLAY_CODE:'||p_version_display_code||
128 ' P_VERSION_NAME:'||p_version_name||
129 ' P_VERSION_DESCRPTION:'||p_version_description||
130 ' P_COMMIT: '||p_commit);
131
132 /* Standard Start of API savepoint */
133 SAVEPOINT create_attribute_pub;
134
135 /* Standard call to check for call compatibility. */
136 IF NOT FND_API.Compatible_API_Call (c_api_version,
137 p_api_version,
138 c_api_name,
139 pc_pkg_name)
140 THEN
141 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
142 END IF;
143
144 /* Initialize API return status to success */
145 x_return_status := pc_ret_sts_success;
146
147 /* Validate that the Dimension Varchar Label exists */
148 BEGIN
149 SELECT B.dimension_id
150 INTO v_dimension_id
151 FROM fem_dimensions_b B, fem_xdim_dimensions X
152 WHERE B.dimension_varchar_label = p_dimension_varchar_label
153 AND B.dimension_id = X.dimension_id
154 AND X.attribute_table_name IS NOT NULL;
155
156 EXCEPTION
157 WHEN no_data_found THEN
158 RAISE e_invalid_dimension;
159 END;
160
161 /* Validate that the Attribute Varchar Label does not already exist
162 for the specified dimension */
163 SELECT count(*)
164 INTO v_count
165 FROM fem_dim_attributes_b
166 WHERE attribute_varchar_label = p_attr_varchar_label
167 AND dimension_id = v_dimension_id ;
168
169 IF v_count > 0 THEN
170 RAISE e_existing_attr_varchar_label;
171 END IF; /* attr_varchar_label validation */
172
173 /* Validate that the Attribute Name does not already exist
174 in any language*/
175 SELECT count(*)
176 INTO v_count
177 FROM fem_dim_attributes_tl
178 WHERE attribute_name = p_attr_name;
179
180 IF v_count > 0 THEN
181 RAISE e_existing_attr_name;
182 END IF; /* attr_name validation */
183
184 /* Validate that the Attribute Order Type Code exists in FND_LOOKUP_VALUES */
185 SELECT count(*)
186 INTO v_count
187 FROM fnd_lookup_values
188 WHERE lookup_type = 'FEM_ATTRIBUTE_ORDER_TYPE_DSC'
189 AND lookup_code = p_attr_order_type_code;
190
191 IF v_count = 0 THEN
192 RAISE e_invalid_order_type;
193 END IF; /* attr_order_type_code validation */
194
195 -- Set the flags - anything but a Y or y means the flag is 'N'
196 v_attr_required_flag := 'N';
197
198 IF p_allow_mult_versions_flag IN ('y', 'Y') THEN
199 v_allow_mult_versions_flag := 'Y';
200 ELSE v_allow_mult_versions_flag := 'N';
201 END IF;
202
203 IF p_queryable_for_reporting_flag IN ('y', 'Y') THEN
204 v_queryable_for_reporting_flag := 'Y';
205 ELSE v_queryable_for_reporting_flag := 'N';
206 END IF;
207
208 IF p_use_inheritance_flag IN ('y', 'Y') THEN
209 v_use_inheritance_flag := 'Y';
210 ELSE v_use_inheritance_flag := 'N';
211 END IF;
212
213 IF p_allow_mult_assign_flag IN ('y', 'Y') THEN
214 v_allow_mult_assign_flag := 'Y';
215 ELSE v_allow_mult_assign_flag := 'N';
216 END IF;
217
218 IF p_personal_flag IN ('y', 'Y') THEN
219 v_personal_flag := 'Y';
220 ELSE v_personal_flag := 'N';
221 END IF;
222
223
224 /* Validate that the Attribute Data Type Code is a valid value */
225 IF p_attr_data_type_code = 'NUMBER' THEN
226 v_attr_value_column_name := 'NUMBER_ASSIGN_VALUE';
227 ELSIF p_attr_data_type_code = 'VARCHAR' THEN
228 v_attr_value_column_name := 'VARCHAR_ASSIGN_VALUE';
229 ELSIF p_attr_data_type_code = 'DATE' THEN
230 v_attr_value_column_name := 'DATE_ASSIGN_VALUE';
231 ELSIF p_attr_data_type_code = 'DIMENSION' THEN
232 BEGIN
233 SELECT dimension_id,
234 DECODE(member_data_type_code,'VARCHAR',
235 'DIM_ATTRIBUTE_VARCHAR_MEMBER',
236 'NUMBER','DIM_ATTRIBUTE_NUMERIC_MEMBER',null)
237 INTO v_attr_dimension_id, v_attr_value_column_name
238 FROM fem_xdim_dimensions
239 WHERE dimension_id IN (SELECT dimension_id FROM fem_dimensions_b
240 WHERE dimension_varchar_label = p_attr_dimension_varchar_label);
241 EXCEPTION
242 WHEN no_data_found THEN
243 RAISE e_invalid_attr_dimension;
244 END;
245
246
247 ELSE RAISE e_invalid_attr_data_type_code;
248 END IF; /* attr_data_type_code validation */
249
250 SELECT fem_dim_attributes_b_s.nextval
251 INTO v_attribute_id
252 FROM dual;
253
254 SELECT fem_dim_attr_versions_b_s.nextval
255 INTO v_version_id
256 FROM dual;
257
258 FEM_DIM_ATTRIBUTES_PKG.INSERT_ROW(
259 X_ROWID => v_rowid
260 ,X_ATTRIBUTE_ID => v_attribute_id
261 ,X_READ_ONLY_FLAG => 'N'
262 ,X_OBJECT_VERSION_NUMBER => 1
263 ,X_USER_ASSIGN_ALLOWED_FLAG => 'Y'
264 ,X_ASSIGNMENT_IS_READ_ONLY_FLAG => 'N'
265 ,X_PERSONAL_FLAG => v_personal_flag
266 ,X_DIMENSION_ID => v_dimension_id
267 ,X_ATTRIBUTE_DIMENSION_ID => v_attr_dimension_id
268 ,X_ATTRIBUTE_VALUE_COLUMN_NAME => v_attr_value_column_name
269 ,X_ATTRIBUTE_DATA_TYPE_CODE => p_attr_data_type_code
270 ,X_ALLOW_MULTIPLE_ASSIGNMENT_FL => v_allow_mult_assign_flag
271 ,X_ATTRIBUTE_ORDER_TYPE_CODE => p_attr_order_type_code
272 ,X_ATTRIBUTE_REQUIRED_FLAG => v_attr_required_flag
273 ,X_USE_INHERITANCE_FLAG => v_use_inheritance_flag
274 ,X_QUERYABLE_FOR_REPORTING_FLAG => v_queryable_for_reporting_flag
275 ,X_ALLOW_MULTIPLE_VERSIONS_FLAG => v_allow_mult_versions_flag
276 ,X_ATTRIBUTE_VARCHAR_LABEL => p_attr_varchar_label
277 ,X_ATTRIBUTE_NAME => p_attr_name
278 ,X_DESCRIPTION => p_attr_description
279 ,X_CREATION_DATE => sysdate
280 ,X_CREATED_BY => pc_user_id
281 ,X_LAST_UPDATE_DATE => sysdate
282 ,X_LAST_UPDATED_BY => pc_user_id
283 ,X_LAST_UPDATE_LOGIN => pc_last_update_login);
284
285 FEM_DIM_ATTR_VERSIONS_PKG.INSERT_ROW(
286 X_ROWID => v_rowid
287 ,X_VERSION_ID => v_version_id
288 ,X_ATTRIBUTE_ID => v_attribute_id
289 ,X_AW_SNAPSHOT_FLAG => 'N'
290 ,X_VERSION_DISPLAY_CODE => p_version_display_code
291 ,X_OBJECT_VERSION_NUMBER => 1
292 ,X_DEFAULT_VERSION_FLAG => 'Y'
293 ,X_PERSONAL_FLAG => v_personal_flag
294 ,X_VERSION_NAME => p_version_name
295 ,X_DESCRIPTION => p_version_description
296 ,X_CREATION_DATE => sysdate
297 ,X_CREATED_BY => pc_user_id
298 ,X_LAST_UPDATE_DATE => sysdate
299 ,X_LAST_UPDATED_BY => pc_user_id
300 ,X_LAST_UPDATE_LOGIN => pc_last_update_login);
301
302
303 IF FND_API.To_Boolean( p_commit ) THEN
304 COMMIT WORK;
305 END IF;
306
307 x_attribute_id := v_attribute_id;
308
309 fem_engines_pkg.put_message(p_app_name =>'FEM'
310 ,p_msg_name =>'FEM_XDIM_ATTR_TXT'
311 ,p_token1 => 'LABEL'
312 ,p_value1 => p_attr_varchar_label
313 ,p_trans1 => 'N'
314 ,p_token2 => 'ATTR_ID'
315 ,p_value2 => x_attribute_id
316 ,p_trans2 => 'N' );
317
318 fem_engines_pkg.tech_message(p_severity => pc_log_level_statement,
319 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
320 p_msg_text => 'End. X_RETURN_STATUS: '||x_return_status);
321
322 FND_MSG_PUB.Count_And_Get
323 (p_count => x_msg_count,
324 p_data => x_msg_data);
325
326 EXCEPTION
327 WHEN e_invalid_dimension THEN
328 ROLLBACK TO create_attribute_pub;
329 x_return_status := pc_ret_sts_error;
330 fem_engines_pkg.put_message(p_app_name =>'FEM'
331 ,p_msg_name =>'FEM_XDIM_INVALID_ATTR_DIM'
332 ,p_token1 => 'LABEL'
333 ,p_value1 => p_dimension_varchar_label);
334
335 FND_MSG_PUB.Count_And_Get
336 (p_count => x_msg_count,
337 p_data => x_msg_data);
338
339 WHEN e_invalid_attr_dimension THEN
340 ROLLBACK TO create_attribute_pub;
341 x_return_status := pc_ret_sts_error;
342 fem_engines_pkg.put_message(p_app_name =>'FEM'
343 ,p_msg_name =>'FEM_XDIM_INVALID_DIM'
344 ,p_token1 => 'LABEL'
345 ,p_value1 => p_attr_dimension_varchar_label);
346
347 FND_MSG_PUB.Count_And_Get
348 (p_count => x_msg_count,
349 p_data => x_msg_data);
350
351 WHEN e_existing_attr_name THEN
352 ROLLBACK TO create_attribute_pub;
353 x_return_status := pc_ret_sts_error;
354 fem_engines_pkg.put_message(p_app_name =>'FEM'
355 ,p_msg_name =>'FEM_XDIM_ATTR_NAME_EXISTS'
356 ,p_token1 => 'NAME'
357 ,p_value1 => p_attr_name);
358
359 FND_MSG_PUB.Count_And_Get
360 (p_count => x_msg_count,
361 p_data => x_msg_data);
362
363 WHEN e_existing_attr_varchar_label THEN
364 ROLLBACK TO create_attribute_pub;
365 x_return_status := pc_ret_sts_error;
366 fem_engines_pkg.put_message(p_app_name =>'FEM'
367 ,p_msg_name =>'FEM_XDIM_ATTR_EXISTS'
368 ,p_token1 => 'ATTR'
369 ,p_value1 => p_attr_varchar_label
370 ,p_token2 => 'DIM'
371 ,p_value2 => p_dimension_varchar_label);
372
373 FND_MSG_PUB.Count_And_Get
374 (p_count => x_msg_count,
375 p_data => x_msg_data);
376
377 WHEN e_invalid_order_type THEN
378 ROLLBACK TO create_attribute_pub;
379 x_return_status := pc_ret_sts_error;
383 ,p_value1 => p_attr_order_type_code);
380 fem_engines_pkg.put_message(p_app_name =>'FEM'
381 ,p_msg_name =>'FEM_XDIM_INVALID_ATTR_ORDRTYP'
382 ,p_token1 => 'TYPE'
384
385 FND_MSG_PUB.Count_And_Get
386 (p_count => x_msg_count,
387 p_data => x_msg_data);
388
389 WHEN e_invalid_attr_data_type_code THEN
390 ROLLBACK TO create_attribute_pub;
391 x_return_status := pc_ret_sts_error;
392 fem_engines_pkg.put_message(p_app_name =>'FEM'
393 ,p_msg_name =>'FEM_XDIM_INVALID_ATTR_DATATYP'
394 ,p_token1 => 'TYPE'
395 ,p_value1 => p_attr_data_type_code);
396
397 FND_MSG_PUB.Count_And_Get
398 (p_count => x_msg_count,
399 p_data => x_msg_data);
400
401
402 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
403 ROLLBACK TO create_object_pub;
404 x_return_status := pc_ret_sts_unexp_error;
405
406 fem_engines_pkg.tech_message(p_severity => pc_log_level_unexpected,
407 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
408 p_msg_name => 'FEM_BAD_P_API_VER_ERR'
409 ,p_token1 => 'VALUE'
410 ,p_value1 => p_api_version
411 ,p_trans1 => 'N');
412
413 FND_MSG_PUB.Count_And_Get
414 (p_count => x_msg_count,
415 p_data => x_msg_data);
416
417 WHEN OTHERS THEN
418 /* Unexpected exceptions */
419 x_return_status := pc_ret_sts_unexp_error;
420 gv_prg_msg := gv_prg_msg;
421 gv_callstack := gv_callstack;
422
423 /* Log the call stack and the Oracle error message to
424 ** FND_LOG with the "unexpected exception" severity level. */
425
426 FEM_ENGINES_PKG.Tech_Message
427 (p_severity => pc_log_level_unexpected,
428 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
429 p_msg_text => gv_prg_msg);
430
431 FEM_ENGINES_PKG.Tech_Message
432 (p_severity => pc_log_level_unexpected,
433 p_module => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name,
434 p_msg_text => gv_callstack);
435
436 /* Log the Oracle error message to the stack. */
437 FEM_ENGINES_PKG.put_message(p_app_name =>'FEM',
438 p_msg_name => 'FEM_UNEXPECTED_ERROR',
439 P_TOKEN1 => 'ERR_MSG',
440 P_VALUE1 => gv_prg_msg);
441 ROLLBACK TO create_attribute_pub;
442
443 FND_MSG_PUB.Count_And_Get
444 (p_count => x_msg_count,
445 p_data => x_msg_data);
446
447 END create_attribute;
448
449 /*************************************************************************
450
451 Get Dim Attribute Value
452
453 This API returns the member value of an attribute assignment of either a
454 dimension member or a dimension member/value set combination.
455 If an attribute version is not specified, the default is used.
456 *************************************************************************/
457
458 FUNCTION Get_Dim_Attribute_Value (
459 p_api_version IN NUMBER DEFAULT 1.0,
460 p_init_msg_list IN VARCHAR2 DEFAULT pc_false,
461 p_commit IN VARCHAR2 DEFAULT pc_false,
462 p_encoded IN VARCHAR2 DEFAULT pc_true,
463 x_return_status OUT NOCOPY VARCHAR2,
464 x_msg_count OUT NOCOPY NUMBER,
465 x_msg_data OUT NOCOPY VARCHAR2,
466 p_dimension_varchar_label IN VARCHAR2,
467 p_attribute_varchar_label IN VARCHAR2,
468 p_member_id IN NUMBER,
469 p_value_set_id IN NUMBER DEFAULT NULL,
470 p_attr_version_display_code IN VARCHAR2 DEFAULT NULL,
471 p_return_attr_assign_mbr_id IN VARCHAR2 DEFAULT NULL
472 ) RETURN VARCHAR2
473 IS
474
475 c_api_version NUMBER := 1.0;
476
477 v_dim_id NUMBER;
478 v_attr_id NUMBER;
479 v_attr_tab VARCHAR2(30);
480 v_attr_val_col VARCHAR2(30);
481 v_attr_ver_id NUMBER;
482 v_vs_req_flg VARCHAR2(1);
483 v_vs_id NUMBER;
484 v_mem_id NUMBER;
485 v_mem_b_tab VARCHAR2(30);
486 v_mem_col VARCHAR2(30);
487 v_mem_dc_col VARCHAR2(30);
488 v_attr_dim_id NUMBER;
489
490 v_sql_stmt VARCHAR2(4000);
491
492 v_attr_value VARCHAR2(150) := -1; -- Stores return value
493
494 e_bad_dim_label EXCEPTION;
495 e_no_dim_attr EXCEPTION;
496 e_bad_attr_label EXCEPTION;
497 e_bad_vs_code EXCEPTION;
498 e_bad_mem_vs_code EXCEPTION;
499 e_bad_mem_code EXCEPTION;
500 e_vs_req EXCEPTION;
501 e_bad_default_ver EXCEPTION;
502 e_bad_version EXCEPTION;
503 e_no_attr_value EXCEPTION;
504
505 BEGIN
506
507 x_return_status := pc_ret_sts_success;
508
509 ---------------------------
510 -- Verify the OA parameters
511 ---------------------------
512 FEM_Dimension_Util_Pkg.Validate_OA_Params (
513 p_api_version => c_api_version,
517 x_return_status => x_return_status);
514 p_init_msg_list => p_init_msg_list,
515 p_commit => p_commit,
516 p_encoded => p_encoded,
518
519 IF (x_return_status <> pc_ret_sts_success)
520 THEN
521 FND_MSG_PUB.Count_and_Get(
522 p_encoded => pc_false,
523 p_count => x_msg_count,
524 p_data => x_msg_data);
525 RETURN -1;
526 END IF;
527
528 ---------------------------------
529 -- Verify the specified Dimension
530 ---------------------------------
531 BEGIN
532 SELECT dimension_id
533 INTO v_dim_id
534 FROM fem_dimensions_b
535 WHERE dimension_varchar_label = p_dimension_varchar_label;
536 EXCEPTION
537 WHEN no_data_found THEN
538 RAISE e_bad_dim_label;
539 END;
540
541 -------------------------------
542 -- Get the Dimension's metadata
543 -------------------------------
544 SELECT attribute_table_name,
545 member_b_table_name,
546 member_col,
547 value_set_required_flag
548 INTO v_attr_tab,
549 v_mem_b_tab,
550 v_mem_col,
551 v_vs_req_flg
552 FROM fem_xdim_dimensions
553 WHERE dimension_id = v_dim_id;
554
555 ------------------------------------------
556 -- Verify that the Dimension is attributed
557 ------------------------------------------
558 IF (v_attr_tab IS NULL)
559 THEN
560 RAISE e_no_dim_attr;
561 END IF;
562
563 ---------------------------------
564 -- Verify the specified Attribute
565 ---------------------------------
566 BEGIN
567 SELECT attribute_id,
568 attribute_dimension_id,
569 attribute_value_column_name
570 INTO v_attr_id,
571 v_attr_dim_id,
572 v_attr_val_col
573 FROM fem_dim_attributes_b
574 WHERE attribute_varchar_label = p_attribute_varchar_label
575 AND dimension_id = v_dim_id;
576 EXCEPTION
577 WHEN no_data_found THEN
578 RAISE e_bad_attr_label;
579 END;
580
581 ---------------------------------------------
582 -- Verify just the specified Dimension member
583 ---------------------------------------------
584 v_sql_stmt :=
585 'SELECT '||v_mem_col||
586 ' FROM '||v_mem_b_tab||
587 ' WHERE '||v_mem_col||' = :b_member_id';
588
589 BEGIN
590 EXECUTE IMMEDIATE v_sql_stmt
591 INTO v_mem_id
592 USING p_member_id;
593 EXCEPTION
594 WHEN no_data_found THEN
595 RAISE e_bad_mem_code;
596 END;
597
598 IF (v_vs_req_flg = 'Y')
599 THEN
600 --------------------------------------------------------------
601 -- Verify the specified Dimension member/Value Set combination
602 --------------------------------------------------------------
603 IF (p_value_set_id IS NOT NULL)
604 THEN
605 BEGIN
606 SELECT value_set_id
607 INTO v_vs_id
608 FROM fem_value_sets_b
609 WHERE value_set_id = p_value_set_id;
610 EXCEPTION
611 WHEN no_data_found THEN
612 RAISE e_bad_vs_code;
613 END;
614
615 v_sql_stmt :=
616 'SELECT '||v_mem_col||
617 ' FROM '||v_mem_b_tab||
618 ' WHERE '||v_mem_col||' = :b_member_id'||
619 ' AND value_set_id = :b_vs_id';
620
621 BEGIN
622 EXECUTE IMMEDIATE v_sql_stmt
623 INTO v_mem_id
624 USING p_member_id,
625 v_vs_id;
626 EXCEPTION
627 WHEN no_data_found THEN
628 RAISE e_bad_mem_vs_code;
629 END;
630 ELSE
631 RAISE e_vs_req;
632 END IF;
633 ELSE
634 v_vs_id := null;
635 END IF;
636
637 IF (p_attr_version_display_code IS NULL)
638 THEN
639 ------------------------------------
640 -- Get the default Attribute version
641 ------------------------------------
642 BEGIN
643 SELECT version_id
644 INTO v_attr_ver_id
645 FROM fem_dim_attr_versions_b
646 WHERE attribute_id =
647 (SELECT attribute_id
648 FROM fem_dim_attributes_b
649 WHERE attribute_id = v_attr_id
650 AND dimension_id = v_dim_id)
651 AND default_version_flag = 'Y';
652 EXCEPTION
653 WHEN no_data_found THEN
654 RAISE e_bad_default_ver;
655 WHEN too_many_rows THEN
656 RAISE e_bad_default_ver;
657 END;
658 ELSE
659 -----------------------------------------
660 -- Verify the specified Attribute version
661 -----------------------------------------
662 BEGIN
663 SELECT version_id
664 INTO v_attr_ver_id
665 FROM fem_dim_attr_versions_b
666 WHERE attribute_id =
667 (SELECT attribute_id
668 FROM fem_dim_attributes_b
669 WHERE attribute_id = v_attr_id
670 AND dimension_id = v_dim_id)
671 AND version_display_code = p_attr_version_display_code;
672 EXCEPTION
673 WHEN no_data_found THEN
674 RAISE e_bad_version;
675 END;
676 END IF;
677
678 -----------------------------------
679 -- Get the Attribute assigned value
680 -----------------------------------
681 IF (v_vs_id IS NOT NULL)
682 THEN
683 v_sql_stmt :=
684 'SELECT '||v_attr_val_col||
685 ' FROM '||v_attr_tab||' A,'||
686 ' fem_dim_attributes_b B'||
687 ' WHERE B.attribute_id = :b_attr_id'||
688 ' AND A.attribute_id = B.attribute_id'||
689 ' AND A.value_set_id = :b_vs_id'||
690 ' AND A.version_id = :b_attr_ver_id'||
694 EXECUTE IMMEDIATE v_sql_stmt
691 ' AND A.'||v_mem_col||' = :b_mem_id';
692
693 BEGIN
695 INTO v_attr_value
696 USING v_attr_id,
697 v_vs_id,
698 v_attr_ver_id,
699 v_mem_id;
700 EXCEPTION
701 WHEN no_data_found THEN
702 RAISE e_no_attr_value;
703 END;
704 ELSE
705 v_sql_stmt :=
706 'SELECT '||v_attr_val_col||
707 ' FROM '||v_attr_tab||' A,'||
708 ' fem_dim_attributes_b B'||
709 ' WHERE B.attribute_id = :b_attr_id'||
710 ' AND A.attribute_id = B.attribute_id'||
711 ' AND A.version_id = :b_attr_ver_id'||
712 ' AND A.'||v_mem_col||' = :b_mem_id';
713
714 BEGIN
715 EXECUTE IMMEDIATE v_sql_stmt
716 INTO v_attr_value
717 USING v_attr_id,
718 v_attr_ver_id,
719 v_mem_id;
720 EXCEPTION
721 WHEN no_data_found THEN
722 RAISE e_no_attr_value;
723 END;
724 END IF;
725
726 IF (v_attr_val_col = 'DIM_ATTRIBUTE_NUMERIC_MEMBER' AND
727 NVL(p_return_attr_assign_mbr_id,'N') = 'N')
728 THEN
729 -------------------------------
730 -- Get the Attribute's metadata
731 -------------------------------
732 SELECT member_b_table_name,
733 member_col,
734 member_display_code_col
735 INTO v_mem_b_tab,
736 v_mem_col,
737 v_mem_dc_col
738 FROM fem_xdim_dimensions
739 WHERE dimension_id = v_attr_dim_id;
740
741 --------------------------------------------------
742 -- Get the Attribute assigned value's Display Code
743 --------------------------------------------------
744 v_sql_stmt :=
745 'SELECT '||v_mem_dc_col||
746 ' FROM '||v_mem_b_tab||
747 ' WHERE '||v_mem_col||' = :b_attr_value';
748
749 BEGIN
750 EXECUTE IMMEDIATE v_sql_stmt
751 INTO v_attr_value
752 USING v_attr_value;
753 EXCEPTION
754 WHEN no_data_found THEN
755 RAISE e_no_attr_value;
756 END;
757 END IF;
758
759 RETURN v_attr_value;
760
761 EXCEPTION
762
763 WHEN e_bad_dim_label THEN
764 FEM_ENGINES_PKG.Put_Message(
765 p_app_name => 'FEM',
766 p_msg_name => 'FEM_DIM_ATTR_BAD_DIM_LABEL',
767 p_token1 => 'DIM_LABEL',
768 p_value1 => p_dimension_varchar_label);
769 FND_MSG_PUB.Count_and_Get(
770 p_encoded => p_encoded,
771 p_count => x_msg_count,
772 p_data => x_msg_data);
773 x_return_status := pc_ret_sts_error;
774 RETURN -1;
775
776 WHEN e_no_dim_attr THEN
777 FEM_ENGINES_PKG.Put_Message(
778 p_app_name => 'FEM',
779 p_msg_name => 'FEM_DIM_ATTR_NO_DIM_ATTR',
780 p_token1 => 'DIM_LABEL',
781 p_value1 => p_dimension_varchar_label);
782 FND_MSG_PUB.Count_and_Get(
783 p_encoded => p_encoded,
784 p_count => x_msg_count,
785 p_data => x_msg_data);
786 x_return_status := pc_ret_sts_error;
787 RETURN -1;
788
789 WHEN e_bad_attr_label THEN
790 FEM_ENGINES_PKG.Put_Message(
791 p_app_name => 'FEM',
792 p_msg_name => 'FEM_DIM_ATTR_BAD_ATTR_LABEL',
793 p_token1 => 'ATTR_LABEL',
794 p_value1 => p_attribute_varchar_label);
795 FND_MSG_PUB.Count_and_Get(
796 p_encoded => p_encoded,
797 p_count => x_msg_count,
798 p_data => x_msg_data);
799 x_return_status := pc_ret_sts_error;
800 RETURN -1;
801
802 WHEN e_bad_vs_code THEN
803 FEM_ENGINES_PKG.Put_Message(
804 p_app_name => 'FEM',
805 p_msg_name => 'FEM_DIM_ATTR_BAD_VS_CODE',
806 p_token1 => 'VS_CODE',
807 p_value1 => p_value_set_id);
808 FND_MSG_PUB.Count_and_Get(
809 p_encoded => p_encoded,
810 p_count => x_msg_count,
811 p_data => x_msg_data);
812 x_return_status := pc_ret_sts_error;
813 RETURN -1;
814
815 WHEN e_bad_mem_vs_code THEN
816 FEM_ENGINES_PKG.Put_Message(
817 p_app_name => 'FEM',
818 p_msg_name => 'FEM_DIM_ATTR_BAD_MEM_VS_CODE',
819 p_token1 => 'MEM_CODE',
820 p_value1 => p_member_id,
821 p_token2 => 'VS_CODE',
822 p_value2 => p_value_set_id);
823 FND_MSG_PUB.Count_and_Get(
824 p_encoded => p_encoded,
825 p_count => x_msg_count,
826 p_data => x_msg_data);
827 x_return_status := pc_ret_sts_error;
828 RETURN -1;
829
830 WHEN e_bad_mem_code THEN
831 FEM_ENGINES_PKG.Put_Message(
832 p_app_name => 'FEM',
833 p_msg_name => 'FEM_DIM_ATTR_BAD_MEM_CODE',
834 p_token1 => 'MEM_CODE',
835 p_value1 => p_member_id,
836 p_token2 => 'DIM_LABEL',
837 p_value2 => p_dimension_varchar_label);
838 FND_MSG_PUB.Count_and_Get(
839 p_encoded => p_encoded,
840 p_count => x_msg_count,
841 p_data => x_msg_data);
842 x_return_status := pc_ret_sts_error;
843 RETURN -1;
844
845 WHEN e_vs_req THEN
846 FEM_ENGINES_PKG.Put_Message(
847 p_app_name => 'FEM',
848 p_msg_name => 'FEM_DIM_ATTR_VS_REQUIRED',
849 p_token1 => 'DIM_LABEL',
850 p_value1 => p_dimension_varchar_label);
851 FND_MSG_PUB.Count_and_Get(
852 p_encoded => p_encoded,
853 p_count => x_msg_count,
854 p_data => x_msg_data);
855 x_return_status := pc_ret_sts_error;
856 RETURN -1;
857
858 WHEN e_bad_default_ver THEN
859 FEM_ENGINES_PKG.Put_Message(
860 p_app_name => 'FEM',
861 p_msg_name => 'FEM_DIM_ATTR_BAD_DEFAULT_VER',
862 p_token1 => 'DIM_LABEL',
866 FND_MSG_PUB.Count_and_Get(
863 p_value1 => p_dimension_varchar_label,
864 p_token2 => 'ATTR_LABEL',
865 p_value2 => p_attribute_varchar_label);
867 p_encoded => p_encoded,
868 p_count => x_msg_count,
869 p_data => x_msg_data);
870 x_return_status := pc_ret_sts_error;
871 RETURN -1;
872
873 WHEN e_bad_version THEN
874 FEM_ENGINES_PKG.Put_Message(
875 p_app_name => 'FEM',
876 p_msg_name => 'FEM_DIM_ATTR_BAD_VERSION',
877 p_token1 => 'ATTR_VER_CODE',
878 p_value1 => p_attr_version_display_code,
879 p_token2 => 'DIM_LABEL',
880 p_value2 => p_dimension_varchar_label,
881 p_token3 => 'ATTR_LABEL',
882 p_value3 => p_attribute_varchar_label);
883 FND_MSG_PUB.Count_and_Get(
884 p_encoded => p_encoded,
885 p_count => x_msg_count,
886 p_data => x_msg_data);
887 x_return_status := pc_ret_sts_error;
888 RETURN -1;
889
890 WHEN e_no_attr_value THEN
891 FEM_ENGINES_PKG.Put_Message(
892 p_app_name => 'FEM',
893 p_msg_name => 'FEM_DIM_ATTR_NO_ATTR_VALUE');
894 FND_MSG_PUB.Count_and_Get(
895 p_encoded => p_encoded,
896 p_count => x_msg_count,
897 p_data => x_msg_data);
898 x_return_status := pc_ret_sts_error;
899 RETURN -1;
900
901 END Get_Dim_Attribute_Value;
902
903
904 FUNCTION Get_Dim_Attribute_Value (
905 p_dimension_varchar_label IN VARCHAR2,
906 p_attribute_varchar_label IN VARCHAR2,
907 p_member_id IN NUMBER,
908 p_value_set_id IN NUMBER DEFAULT NULL,
909 p_attr_version_display_code IN VARCHAR2 DEFAULT NULL,
910 p_return_attr_assign_mbr_id IN VARCHAR2 DEFAULT NULL
911 ) RETURN VARCHAR2
912 IS
913
914 x_return_value VARCHAR2(1000);
915 v_return_status VARCHAR2(100);
916 v_msg_count NUMBER;
917 v_msg_data VARCHAR2(1000);
918
919 BEGIN
920
921 x_return_value := Get_Dim_Attribute_Value (
922 p_api_version => 1.0,
923 p_init_msg_list => pc_false,
924 p_commit => pc_false,
925 p_encoded => pc_true,
926 x_return_status => v_return_status,
927 x_msg_count => v_msg_count,
928 x_msg_data => v_msg_data,
929 p_dimension_varchar_label => p_dimension_varchar_label,
930 p_attribute_varchar_label => p_attribute_varchar_label,
931 p_member_id => p_member_id,
932 p_value_set_id => p_value_set_id,
933 p_attr_version_display_code => p_attr_version_display_code,
934 p_return_attr_assign_mbr_id => p_return_attr_assign_mbr_id);
935
936 RETURN x_return_value;
937
938 END Get_Dim_Attribute_Value;
939
940
941 /*************************************************************************
942
943 Get Dim Attr Value Set
944
945 This API returns the value set of an attribute assignment of either
946 a dimension member or a dimension member/value set combination.
947 If an attribute version is not specified, the default is used.
948 *************************************************************************/
949
950 FUNCTION Get_Dim_Attr_Value_Set (
951 p_api_version IN NUMBER DEFAULT 1.0,
952 p_init_msg_list IN VARCHAR2 DEFAULT pc_false,
953 p_commit IN VARCHAR2 DEFAULT pc_false,
954 p_encoded IN VARCHAR2 DEFAULT pc_true,
955 x_return_status OUT NOCOPY VARCHAR2,
956 x_msg_count OUT NOCOPY NUMBER,
957 x_msg_data OUT NOCOPY VARCHAR2,
958 p_dimension_varchar_label IN VARCHAR2,
959 p_attribute_varchar_label IN VARCHAR2,
960 p_member_id IN NUMBER,
961 p_value_set_id IN NUMBER DEFAULT NULL,
962 p_attr_version_display_code IN VARCHAR2 DEFAULT NULL,
963 p_return_attr_assign_vs_id IN VARCHAR2 DEFAULT NULL
964 ) RETURN VARCHAR2
965 IS
966
967 c_api_version NUMBER := 1.0;
968
969 v_dim_id NUMBER;
970 v_attr_id NUMBER;
971 v_attr_tab VARCHAR2(30);
972 v_attr_val_col VARCHAR2(30);
973 v_attr_ver_id NUMBER;
974 v_vs_req_flg VARCHAR2(1);
975 v_vs_id NUMBER;
976 v_mem_id NUMBER;
977 v_mem_b_tab VARCHAR2(30);
978 v_mem_col VARCHAR2(30);
979 v_mem_dc_col VARCHAR2(30);
980 v_attr_dim_id NUMBER;
981 v_attr_vs_id NUMBER;
982
983 v_sql_stmt VARCHAR2(4000);
984
985 v_attr_value VARCHAR2(150) := -1; -- Stores return value
986
987 e_bad_dim_label EXCEPTION;
988 e_no_dim_attr EXCEPTION;
989 e_bad_attr_label EXCEPTION;
990 e_bad_vs_code EXCEPTION;
991 e_bad_mem_vs_code EXCEPTION;
992 e_bad_mem_code EXCEPTION;
993 e_vs_req EXCEPTION;
994 e_bad_default_ver EXCEPTION;
995 e_bad_version EXCEPTION;
996 e_no_attr_value EXCEPTION;
997
998 BEGIN
999
1000 x_return_status := pc_ret_sts_success;
1001
1002 ---------------------------
1003 -- Verify the OA parameters
1004 ---------------------------
1005 FEM_Dimension_Util_Pkg.Validate_OA_Params (
1006 p_api_version => c_api_version,
1007 p_init_msg_list => p_init_msg_list,
1008 p_commit => p_commit,
1009 p_encoded => p_encoded,
1010 x_return_status => x_return_status);
1011
1012 IF (x_return_status <> pc_ret_sts_success)
1013 THEN
1014 FND_MSG_PUB.Count_and_Get(
1015 p_encoded => pc_false,
1016 p_count => x_msg_count,
1017 p_data => x_msg_data);
1018 RETURN -1;
1019 END IF;
1020
1021 ---------------------------------
1022 -- Verify the specified Dimension
1023 ---------------------------------
1024 BEGIN
1028 WHERE dimension_varchar_label = p_dimension_varchar_label;
1025 SELECT dimension_id
1026 INTO v_dim_id
1027 FROM fem_dimensions_b
1029 EXCEPTION
1030 WHEN no_data_found THEN
1031 RAISE e_bad_dim_label;
1032 END;
1033
1034 -------------------------------
1035 -- Get the Dimension's metadata
1036 -------------------------------
1037 SELECT attribute_table_name,
1038 member_b_table_name,
1039 member_col,
1040 value_set_required_flag
1041 INTO v_attr_tab,
1042 v_mem_b_tab,
1043 v_mem_col,
1044 v_vs_req_flg
1045 FROM fem_xdim_dimensions
1046 WHERE dimension_id = v_dim_id;
1047
1048 ------------------------------------------
1049 -- Verify that the Dimension is attributed
1050 ------------------------------------------
1051 IF (v_attr_tab IS NULL)
1052 THEN
1053 RAISE e_no_dim_attr;
1054 END IF;
1055
1056 ---------------------------------
1057 -- Verify the specified Attribute
1058 ---------------------------------
1059 BEGIN
1060 SELECT attribute_id,
1061 attribute_dimension_id,
1062 attribute_value_column_name
1063 INTO v_attr_id,
1064 v_attr_dim_id,
1065 v_attr_val_col
1066 FROM fem_dim_attributes_b
1067 WHERE attribute_varchar_label = p_attribute_varchar_label
1068 AND dimension_id = v_dim_id;
1069 EXCEPTION
1070 WHEN no_data_found THEN
1071 RAISE e_bad_attr_label;
1072 END;
1073
1074 ---------------------------------------------
1075 -- Verify just the specified Dimension member
1076 ---------------------------------------------
1077 v_sql_stmt :=
1078 'SELECT '||v_mem_col||
1079 ' FROM '||v_mem_b_tab||
1080 ' WHERE '||v_mem_col||' = :b_member_id';
1081
1082 BEGIN
1083 EXECUTE IMMEDIATE v_sql_stmt
1084 INTO v_mem_id
1085 USING p_member_id;
1086 EXCEPTION
1087 WHEN no_data_found THEN
1088 RAISE e_bad_mem_code;
1089 END;
1090
1091 IF (v_vs_req_flg = 'Y')
1092 THEN
1093 --------------------------------------------------------------
1094 -- Verify the specified Dimension member/Value Set combination
1095 --------------------------------------------------------------
1096 IF (p_value_set_id IS NOT NULL)
1097 THEN
1098 BEGIN
1099 SELECT value_set_id
1100 INTO v_vs_id
1101 FROM fem_value_sets_b
1102 WHERE value_set_id = p_value_set_id;
1103 EXCEPTION
1104 WHEN no_data_found THEN
1105 RAISE e_bad_vs_code;
1106 END;
1107
1108 v_sql_stmt :=
1109 'SELECT '||v_mem_col||
1110 ' FROM '||v_mem_b_tab||
1111 ' WHERE '||v_mem_col||' = :b_member_id'||
1112 ' AND value_set_id = :b_vs_id';
1113
1114 BEGIN
1115 EXECUTE IMMEDIATE v_sql_stmt
1116 INTO v_mem_id
1117 USING p_member_id,
1118 v_vs_id;
1119 EXCEPTION
1120 WHEN no_data_found THEN
1121 RAISE e_bad_mem_vs_code;
1122 END;
1123 ELSE
1124 RAISE e_vs_req;
1125 END IF;
1126 ELSE
1127 v_vs_id := null;
1128 END IF;
1129
1130 IF (p_attr_version_display_code IS NULL)
1131 THEN
1132 ------------------------------------
1133 -- Get the default Attribute version
1134 ------------------------------------
1135 BEGIN
1136 SELECT version_id
1137 INTO v_attr_ver_id
1138 FROM fem_dim_attr_versions_b
1139 WHERE attribute_id =
1140 (SELECT attribute_id
1141 FROM fem_dim_attributes_b
1142 WHERE attribute_id = v_attr_id
1143 AND dimension_id = v_dim_id)
1144 AND default_version_flag = 'Y';
1145 EXCEPTION
1146 WHEN no_data_found THEN
1147 RAISE e_bad_default_ver;
1148 WHEN too_many_rows THEN
1149 RAISE e_bad_default_ver;
1150 END;
1151 ELSE
1152 -----------------------------------------
1153 -- Verify the specified Attribute version
1154 -----------------------------------------
1155 BEGIN
1156 SELECT version_id
1157 INTO v_attr_ver_id
1158 FROM fem_dim_attr_versions_b
1159 WHERE attribute_id =
1160 (SELECT attribute_id
1161 FROM fem_dim_attributes_b
1162 WHERE attribute_id = v_attr_id
1163 AND dimension_id = v_dim_id)
1164 AND version_display_code = p_attr_version_display_code;
1165 EXCEPTION
1166 WHEN no_data_found THEN
1167 RAISE e_bad_version;
1168 END;
1169 END IF;
1170
1171 ---------------------------------
1172 -- Get the Attribute Value Set ID
1173 ---------------------------------
1174 IF (v_vs_id IS NOT NULL)
1175 THEN
1176 v_sql_stmt :=
1177 'SELECT dim_attribute_value_set_id'||
1178 ' FROM '||v_attr_tab||
1179 ' WHERE '||v_mem_col||' = :b_mem_id'||
1180 ' AND value_set_id = :b_vs_id'||
1181 ' AND attribute_id = :b_attr_id';
1182
1183 BEGIN
1184 EXECUTE IMMEDIATE v_sql_stmt
1185 INTO v_attr_vs_id
1186 USING v_mem_id,
1187 v_vs_id,
1188 v_attr_id;
1189 EXCEPTION
1190 WHEN no_data_found THEN
1191 RAISE e_no_attr_value;
1192 END;
1193 ELSE
1194 v_sql_stmt :=
1195 'SELECT dim_attribute_value_set_id'||
1196 ' FROM '||v_attr_tab||
1197 ' WHERE '||v_mem_col||' = :b_mem_id'||
1198 ' AND attribute_id = :b_attr_id';
1199
1200 BEGIN
1201 EXECUTE IMMEDIATE v_sql_stmt
1202 INTO v_attr_vs_id
1203 USING v_mem_id,
1204 v_attr_id;
1208 END;
1205 EXCEPTION
1206 WHEN no_data_found THEN
1207 RAISE e_no_attr_value;
1209 END IF;
1210
1211 IF (NVL(p_return_attr_assign_vs_id,'N') = 'N')
1212 THEN
1213 -------------------------------------------
1214 -- Get the Attribute Value Set Display Code
1215 -------------------------------------------
1216 SELECT value_set_display_code
1217 INTO v_attr_value
1218 FROM fem_value_sets_b
1219 WHERE value_set_id = v_attr_vs_id
1220 AND dimension_id = v_attr_dim_id;
1221 ELSE
1222 v_attr_value := v_attr_vs_id;
1223 END IF;
1224
1225 RETURN v_attr_value;
1226
1227 EXCEPTION
1228
1229 WHEN e_bad_dim_label THEN
1230 FEM_ENGINES_PKG.Put_Message(
1231 p_app_name => 'FEM',
1232 p_msg_name => 'FEM_DIM_ATTR_BAD_DIM_LABEL',
1233 p_token1 => 'DIM_LABEL',
1234 p_value1 => p_dimension_varchar_label);
1235 FND_MSG_PUB.Count_and_Get(
1236 p_encoded => p_encoded,
1237 p_count => x_msg_count,
1238 p_data => x_msg_data);
1239 x_return_status := pc_ret_sts_error;
1240 RETURN -1;
1241
1242 WHEN e_no_dim_attr THEN
1243 FEM_ENGINES_PKG.Put_Message(
1244 p_app_name => 'FEM',
1245 p_msg_name => 'FEM_DIM_ATTR_NO_DIM_ATTR',
1246 p_token1 => 'DIM_LABEL',
1247 p_value1 => p_dimension_varchar_label);
1248 FND_MSG_PUB.Count_and_Get(
1249 p_encoded => p_encoded,
1250 p_count => x_msg_count,
1251 p_data => x_msg_data);
1252 x_return_status := pc_ret_sts_error;
1253 RETURN -1;
1254
1255 WHEN e_bad_attr_label THEN
1256 FEM_ENGINES_PKG.Put_Message(
1257 p_app_name => 'FEM',
1258 p_msg_name => 'FEM_DIM_ATTR_BAD_ATTR_LABEL',
1259 p_token1 => 'ATTR_LABEL',
1260 p_value1 => p_attribute_varchar_label);
1261 FND_MSG_PUB.Count_and_Get(
1262 p_encoded => p_encoded,
1263 p_count => x_msg_count,
1264 p_data => x_msg_data);
1265 x_return_status := pc_ret_sts_error;
1266 RETURN -1;
1267
1268 WHEN e_bad_vs_code THEN
1269 FEM_ENGINES_PKG.Put_Message(
1270 p_app_name => 'FEM',
1271 p_msg_name => 'FEM_DIM_ATTR_BAD_VS_CODE',
1272 p_token1 => 'VS_CODE',
1273 p_value1 => p_value_set_id);
1274 FND_MSG_PUB.Count_and_Get(
1275 p_encoded => p_encoded,
1276 p_count => x_msg_count,
1277 p_data => x_msg_data);
1278 x_return_status := pc_ret_sts_error;
1279 RETURN -1;
1280
1281 WHEN e_bad_mem_vs_code THEN
1282 FEM_ENGINES_PKG.Put_Message(
1283 p_app_name => 'FEM',
1284 p_msg_name => 'FEM_DIM_ATTR_BAD_MEM_VS_CODE',
1285 p_token1 => 'MEM_CODE',
1286 p_value1 => p_member_id,
1287 p_token2 => 'VS_CODE',
1288 p_value2 => p_value_set_id);
1289 FND_MSG_PUB.Count_and_Get(
1290 p_encoded => p_encoded,
1291 p_count => x_msg_count,
1292 p_data => x_msg_data);
1293 x_return_status := pc_ret_sts_error;
1294 RETURN -1;
1295
1296 WHEN e_bad_mem_code THEN
1297 FEM_ENGINES_PKG.Put_Message(
1298 p_app_name => 'FEM',
1299 p_msg_name => 'FEM_DIM_ATTR_BAD_MEM_CODE',
1300 p_token1 => 'MEM_CODE',
1301 p_value1 => p_member_id,
1302 p_token2 => 'DIM_LABEL',
1303 p_value2 => p_dimension_varchar_label);
1304 FND_MSG_PUB.Count_and_Get(
1305 p_encoded => p_encoded,
1306 p_count => x_msg_count,
1307 p_data => x_msg_data);
1308 x_return_status := pc_ret_sts_error;
1309 RETURN -1;
1310
1311 WHEN e_vs_req THEN
1312 FEM_ENGINES_PKG.Put_Message(
1313 p_app_name => 'FEM',
1314 p_msg_name => 'FEM_DIM_ATTR_VS_REQUIRED',
1315 p_token1 => 'DIM_LABEL',
1316 p_value1 => p_dimension_varchar_label);
1317 FND_MSG_PUB.Count_and_Get(
1318 p_encoded => p_encoded,
1319 p_count => x_msg_count,
1320 p_data => x_msg_data);
1321 x_return_status := pc_ret_sts_error;
1322 RETURN -1;
1323
1324 WHEN e_bad_default_ver THEN
1325 FEM_ENGINES_PKG.Put_Message(
1326 p_app_name => 'FEM',
1327 p_msg_name => 'FEM_DIM_ATTR_BAD_DEFAULT_VER',
1328 p_token1 => 'DIM_LABEL',
1329 p_value1 => p_dimension_varchar_label,
1330 p_token2 => 'ATTR_LABEL',
1331 p_value2 => p_attribute_varchar_label);
1332 FND_MSG_PUB.Count_and_Get(
1333 p_encoded => p_encoded,
1334 p_count => x_msg_count,
1335 p_data => x_msg_data);
1336 x_return_status := pc_ret_sts_error;
1337 RETURN -1;
1338
1339 WHEN e_bad_version THEN
1340 FEM_ENGINES_PKG.Put_Message(
1341 p_app_name => 'FEM',
1342 p_msg_name => 'FEM_DIM_ATTR_BAD_VERSION',
1343 p_token1 => 'ATTR_VER_CODE',
1344 p_value1 => p_attr_version_display_code,
1345 p_token2 => 'DIM_LABEL',
1346 p_value2 => p_dimension_varchar_label,
1347 p_token3 => 'ATTR_LABEL',
1348 p_value3 => p_attribute_varchar_label);
1349 FND_MSG_PUB.Count_and_Get(
1350 p_encoded => p_encoded,
1351 p_count => x_msg_count,
1352 p_data => x_msg_data);
1353 x_return_status := pc_ret_sts_error;
1354 RETURN -1;
1355
1356 WHEN e_no_attr_value THEN
1357 FEM_ENGINES_PKG.Put_Message(
1358 p_app_name => 'FEM',
1359 p_msg_name => 'FEM_DIM_ATTR_NO_ATTR_VALUE');
1360 FND_MSG_PUB.Count_and_Get(
1361 p_encoded => p_encoded,
1362 p_count => x_msg_count,
1363 p_data => x_msg_data);
1364 x_return_status := pc_ret_sts_error;
1365 RETURN -1;
1366
1367 END Get_Dim_Attr_Value_Set;
1368
1369 /*************************************************************************
1370
1371 New Dim Attr Version
1375 *************************************************************************/
1372
1373 This API creates a new version for a specified dimension attribute
1374
1376
1377 PROCEDURE New_Dim_Attr_Version (
1378 p_api_version IN NUMBER DEFAULT 1.0,
1379 p_init_msg_list IN VARCHAR2 DEFAULT pc_false,
1380 p_commit IN VARCHAR2 DEFAULT pc_false,
1381 p_encoded IN VARCHAR2 DEFAULT pc_true,
1382 x_return_status OUT NOCOPY VARCHAR2,
1383 x_msg_count OUT NOCOPY NUMBER,
1384 x_msg_data OUT NOCOPY VARCHAR2,
1385 p_dimension_varchar_label IN VARCHAR2,
1386 p_attribute_varchar_label IN VARCHAR2,
1387 p_version_display_code IN VARCHAR2,
1388 p_version_name IN VARCHAR2,
1389 p_version_desc IN VARCHAR2 DEFAULT NULL,
1390 p_default_version_flag IN VARCHAR2 DEFAULT 'N'
1391 )
1392 IS
1393
1394 c_api_version CONSTANT NUMBER := 1.0;
1395
1396 c_pers_flg CONSTANT VARCHAR2(1) := 'N';
1397 c_obj_ver_no CONSTANT NUMBER := 1;
1398 c_aw_flg CONSTANT VARCHAR2(1) := 'N';
1399
1400 v_row_id VARCHAR2(20) := '';
1401
1402 v_dim_id NUMBER;
1403 v_attr_id NUMBER;
1404 v_attr_tab VARCHAR2(30);
1405 v_mult_ver_flg VARCHAR2(1);
1406 v_attr_req_flg VARCHAR2(1);
1407 v_ver_id NUMBER;
1408
1409 e_bad_dim_label EXCEPTION;
1410 e_no_dim_attr EXCEPTION;
1411 e_bad_attr_label EXCEPTION;
1412 e_no_mult_versions EXCEPTION;
1413 e_no_ver_disp_code EXCEPTION;
1414 e_no_version_name EXCEPTION;
1415
1416 BEGIN
1417
1418 x_return_status := pc_ret_sts_success;
1419
1420 ---------------------------
1421 -- Verify the OA parameters
1422 ---------------------------
1423 FEM_Dimension_Util_Pkg.Validate_OA_Params (
1424 p_api_version => c_api_version,
1425 p_init_msg_list => p_init_msg_list,
1426 p_commit => p_commit,
1427 p_encoded => p_encoded,
1428 x_return_status => x_return_status);
1429
1430 IF (x_return_status <> pc_ret_sts_success)
1431 THEN
1432 FND_MSG_PUB.Count_and_Get(
1433 p_encoded => pc_false,
1434 p_count => x_msg_count,
1435 p_data => x_msg_data);
1436 END IF;
1437
1438 ---------------------------------
1439 -- Verify the specified Dimension
1440 ---------------------------------
1441 BEGIN
1442 SELECT dimension_id
1443 INTO v_dim_id
1444 FROM fem_dimensions_b
1445 WHERE dimension_varchar_label = p_dimension_varchar_label;
1446 EXCEPTION
1447 WHEN no_data_found THEN
1448 RAISE e_bad_dim_label;
1449 END;
1450
1451 ------------------------------------------
1452 -- Verify that the Dimension is attributed
1453 ------------------------------------------
1454 SELECT attribute_table_name
1455 INTO v_attr_tab
1456 FROM fem_xdim_dimensions
1457 WHERE dimension_id = v_dim_id;
1458
1459 IF (v_attr_tab IS NULL)
1460 THEN
1461 RAISE e_no_dim_attr;
1462 END IF;
1463
1464 ---------------------------------
1465 -- Verify the specified Attribute
1466 ---------------------------------
1467 BEGIN
1468 SELECT attribute_id,
1469 attribute_required_flag,
1470 allow_multiple_versions_flag
1471 INTO v_attr_id,
1472 v_attr_req_flg,
1473 v_mult_ver_flg
1474 FROM fem_dim_attributes_b
1475 WHERE attribute_varchar_label = p_attribute_varchar_label
1476 AND dimension_id = v_dim_id;
1477 EXCEPTION
1478 WHEN no_data_found THEN
1479 RAISE e_bad_attr_label;
1480 END;
1481
1482 IF (v_mult_ver_flg = 'N' OR
1483 v_attr_req_flg = 'Y')
1484 THEN
1485 RAISE e_no_mult_versions;
1486 END IF;
1487
1488 ---------------------------------------
1489 -- Verify Version Display Code and Name
1490 ---------------------------------------
1491 IF (p_version_display_code IS NULL)
1492 THEN
1493 RAISE e_no_ver_disp_code;
1494 END IF;
1495
1496 IF (p_version_name IS NULL)
1497 THEN
1498 RAISE e_no_version_name;
1499 END IF;
1500
1501 ---------------------
1502 -- Create New Version
1503 ---------------------
1504 SELECT fem_dim_attr_versions_b_s.NEXTVAL
1505 INTO v_ver_id FROM dual;
1506
1507 FEM_DIM_ATTR_VERSIONS_PKG.INSERT_ROW(
1508 x_rowid => v_row_id,
1509 x_version_id => v_ver_id,
1510 x_aw_snapshot_flag => c_aw_flg,
1511 x_version_display_code => p_version_display_code,
1512 x_object_version_number => c_obj_ver_no,
1513 x_default_version_flag => p_default_version_flag,
1514 x_personal_flag => c_pers_flg,
1515 x_attribute_id => v_attr_id,
1516 x_version_name => p_version_name,
1517 x_description => p_version_desc,
1518 x_creation_date => sysdate,
1519 x_created_by => pc_user_id,
1520 x_last_update_date => sysdate,
1521 x_last_updated_by => pc_user_id,
1522 x_last_update_login => pc_last_update_login);
1523
1524 IF (p_default_version_flag = 'Y')
1525 THEN
1526 UPDATE fem_dim_attr_versions_b
1527 SET default_version_flag='N'
1528 WHERE attribute_id = v_attr_id
1529 AND version_id <> v_ver_id;
1530 END IF;
1531
1532 EXCEPTION
1533
1534 WHEN e_bad_dim_label THEN
1535 FEM_ENGINES_PKG.Put_Message(
1536 p_app_name => 'FEM',
1537 p_msg_name => 'FEM_DIM_ATTR_BAD_DIM_LABEL',
1538 p_token1 => 'DIM_LABEL',
1539 p_value1 => p_dimension_varchar_label);
1540 FND_MSG_PUB.Count_and_Get(
1541 p_encoded => p_encoded,
1542 p_count => x_msg_count,
1543 p_data => x_msg_data);
1544 x_return_status := pc_ret_sts_error;
1545
1549 p_msg_name => 'FEM_DIM_ATTR_NO_DIM_ATTR',
1546 WHEN e_no_dim_attr THEN
1547 FEM_ENGINES_PKG.Put_Message(
1548 p_app_name => 'FEM',
1550 p_token1 => 'DIM_LABEL',
1551 p_value1 => p_dimension_varchar_label);
1552 FND_MSG_PUB.Count_and_Get(
1553 p_encoded => p_encoded,
1554 p_count => x_msg_count,
1555 p_data => x_msg_data);
1556 x_return_status := pc_ret_sts_error;
1557
1558 WHEN e_bad_attr_label THEN
1559 FEM_ENGINES_PKG.Put_Message(
1560 p_app_name => 'FEM',
1561 p_msg_name => 'FEM_DIM_ATTR_BAD_ATTR_LABEL',
1562 p_token1 => 'ATTR_LABEL',
1563 p_value1 => p_attribute_varchar_label);
1564 FND_MSG_PUB.Count_and_Get(
1565 p_encoded => p_encoded,
1566 p_count => x_msg_count,
1567 p_data => x_msg_data);
1568 x_return_status := pc_ret_sts_error;
1569
1570 WHEN e_no_mult_versions THEN
1571 FEM_ENGINES_PKG.Put_Message(
1572 p_app_name => 'FEM',
1573 p_msg_name => 'FEM_DIM_ATTR_NO_MULT_VERSIONS');
1574 FND_MSG_PUB.Count_and_Get(
1575 p_encoded => p_encoded,
1576 p_count => x_msg_count,
1577 p_data => x_msg_data);
1578 x_return_status := pc_ret_sts_error;
1579
1580 WHEN e_no_version_name THEN
1581 FEM_ENGINES_PKG.Put_Message(
1582 p_app_name => 'FEM',
1583 p_msg_name => 'FEM_DIM_ATTR_NO_VERSION_NAME');
1584 FND_MSG_PUB.Count_and_Get(
1585 p_encoded => p_encoded,
1586 p_count => x_msg_count,
1587 p_data => x_msg_data);
1588 x_return_status := pc_ret_sts_error;
1589
1590 WHEN e_no_ver_disp_code THEN
1591 FEM_ENGINES_PKG.Put_Message(
1592 p_app_name => 'FEM',
1593 p_msg_name => 'FEM_DIM_ATTR_NO_VERSION_CODE');
1594 FND_MSG_PUB.Count_and_Get(
1595 p_encoded => p_encoded,
1596 p_count => x_msg_count,
1597 p_data => x_msg_data);
1598 x_return_status := pc_ret_sts_error;
1599
1600 END New_Dim_Attr_Version;
1601
1602 /*************************************************************************
1603
1604 New Dim Attr Default
1605
1606 This API changes the default version for a specified dimension attribute
1607
1608 *************************************************************************/
1609
1610 PROCEDURE New_Dim_Attr_Default (
1611 p_api_version IN NUMBER DEFAULT 1.0,
1612 p_init_msg_list IN VARCHAR2 DEFAULT pc_false,
1613 p_commit IN VARCHAR2 DEFAULT pc_false,
1614 p_encoded IN VARCHAR2 DEFAULT pc_true,
1615 x_return_status OUT NOCOPY VARCHAR2,
1616 x_msg_count OUT NOCOPY NUMBER,
1617 x_msg_data OUT NOCOPY VARCHAR2,
1618 p_dimension_varchar_label IN VARCHAR2,
1619 p_attribute_varchar_label IN VARCHAR2,
1620 p_version_display_code IN VARCHAR2
1621 )
1622 IS
1623
1624 c_api_version CONSTANT NUMBER := 1.0;
1625
1626 v_dim_id NUMBER;
1627 v_attr_id NUMBER;
1628 v_attr_tab VARCHAR2(30);
1629 v_mult_ver_flg VARCHAR2(1);
1630 v_attr_req_flg VARCHAR2(1);
1631 v_ver_id NUMBER;
1632
1633 e_bad_dim_label EXCEPTION;
1634 e_no_dim_attr EXCEPTION;
1635 e_bad_attr_label EXCEPTION;
1636 e_no_mult_versions EXCEPTION;
1637 e_no_ver_disp_code EXCEPTION;
1638
1639 BEGIN
1640
1641 x_return_status := pc_ret_sts_success;
1642
1643 ---------------------------
1644 -- Verify the OA parameters
1645 ---------------------------
1646 FEM_Dimension_Util_Pkg.Validate_OA_Params (
1647 p_api_version => c_api_version,
1648 p_init_msg_list => p_init_msg_list,
1649 p_commit => p_commit,
1650 p_encoded => p_encoded,
1651 x_return_status => x_return_status);
1652
1653 IF (x_return_status <> pc_ret_sts_success)
1654 THEN
1655 FND_MSG_PUB.Count_and_Get(
1656 p_encoded => pc_false,
1657 p_count => x_msg_count,
1658 p_data => x_msg_data);
1659 END IF;
1660
1661 ---------------------------------
1662 -- Verify the specified Dimension
1663 ---------------------------------
1664 BEGIN
1665 SELECT dimension_id
1666 INTO v_dim_id
1667 FROM fem_dimensions_b
1668 WHERE dimension_varchar_label = p_dimension_varchar_label;
1669 EXCEPTION
1670 WHEN no_data_found THEN
1671 RAISE e_bad_dim_label;
1672 END;
1673
1674 ------------------------------------------
1675 -- Verify that the Dimension is attributed
1676 ------------------------------------------
1677 SELECT attribute_table_name
1678 INTO v_attr_tab
1679 FROM fem_xdim_dimensions
1680 WHERE dimension_id = v_dim_id;
1681
1682 IF (v_attr_tab IS NULL)
1683 THEN
1684 RAISE e_no_dim_attr;
1685 END IF;
1686
1687 ---------------------------------
1688 -- Verify the specified Attribute
1689 ---------------------------------
1690 BEGIN
1691 SELECT attribute_id,
1692 attribute_required_flag,
1693 allow_multiple_versions_flag
1694 INTO v_attr_id,
1695 v_attr_req_flg,
1696 v_mult_ver_flg
1697 FROM fem_dim_attributes_b
1698 WHERE attribute_varchar_label = p_attribute_varchar_label
1699 AND dimension_id = v_dim_id;
1700 EXCEPTION
1701 WHEN no_data_found THEN
1702 RAISE e_bad_attr_label;
1703 END;
1704
1705 IF (v_mult_ver_flg = 'N' OR
1706 v_attr_req_flg = 'Y')
1707 THEN
1708 RAISE e_no_mult_versions;
1709 END IF;
1710
1711 ---------------------------
1712 -- Verify Attribute Version
1713 ---------------------------
1714 BEGIN
1715 SELECT version_id
1716 INTO v_ver_id
1717 FROM fem_dim_attr_versions_b
1721 WHEN no_data_found THEN
1718 WHERE attribute_id = v_attr_id
1719 AND version_display_code = p_version_display_code;
1720 EXCEPTION
1722 RAISE e_no_ver_disp_code;
1723 END;
1724
1725 ---------------------------
1726 -- Update Attribute Default
1727 ---------------------------
1728 UPDATE fem_dim_attr_versions_b
1729 SET default_version_flag='N'
1730 WHERE attribute_id = v_attr_id
1731 AND version_id <> v_ver_id;
1732
1733 UPDATE fem_dim_attr_versions_b
1734 SET default_version_flag='Y'
1735 WHERE attribute_id = v_attr_id
1736 AND version_id = v_ver_id;
1737
1738 EXCEPTION
1739
1740 WHEN e_bad_dim_label THEN
1741 FEM_ENGINES_PKG.Put_Message(
1742 p_app_name => 'FEM',
1743 p_msg_name => 'FEM_DIM_ATTR_BAD_DIM_LABEL',
1744 p_token1 => 'DIM_LABEL',
1745 p_value1 => p_dimension_varchar_label);
1746 FND_MSG_PUB.Count_and_Get(
1747 p_encoded => p_encoded,
1748 p_count => x_msg_count,
1749 p_data => x_msg_data);
1750 x_return_status := pc_ret_sts_error;
1751
1752 WHEN e_no_dim_attr THEN
1753 FEM_ENGINES_PKG.Put_Message(
1754 p_app_name => 'FEM',
1755 p_msg_name => 'FEM_DIM_ATTR_NO_DIM_ATTR',
1756 p_token1 => 'DIM_LABEL',
1757 p_value1 => p_dimension_varchar_label);
1758 FND_MSG_PUB.Count_and_Get(
1759 p_encoded => p_encoded,
1760 p_count => x_msg_count,
1761 p_data => x_msg_data);
1762 x_return_status := pc_ret_sts_error;
1763
1764 WHEN e_bad_attr_label THEN
1765 FEM_ENGINES_PKG.Put_Message(
1766 p_app_name => 'FEM',
1767 p_msg_name => 'FEM_DIM_ATTR_BAD_ATTR_LABEL',
1768 p_token1 => 'ATTR_LABEL',
1769 p_value1 => p_attribute_varchar_label);
1770 FND_MSG_PUB.Count_and_Get(
1771 p_encoded => p_encoded,
1772 p_count => x_msg_count,
1773 p_data => x_msg_data);
1774 x_return_status := pc_ret_sts_error;
1775
1776 WHEN e_no_mult_versions THEN
1777 FEM_ENGINES_PKG.Put_Message(
1778 p_app_name => 'FEM',
1779 p_msg_name => 'FEM_DIM_ATTR_NO_MULT_VERSIONS');
1780 FND_MSG_PUB.Count_and_Get(
1781 p_encoded => p_encoded,
1782 p_count => x_msg_count,
1783 p_data => x_msg_data);
1784 x_return_status := pc_ret_sts_error;
1785
1786 WHEN e_no_ver_disp_code THEN
1787 FEM_ENGINES_PKG.Put_Message(
1788 p_app_name => 'FEM',
1789 p_msg_name => 'FEM_DIM_ATTR_NO_VERSION_CODE');
1790 FND_MSG_PUB.Count_and_Get(
1791 p_encoded => p_encoded,
1792 p_count => x_msg_count,
1793 p_data => x_msg_data);
1794 x_return_status := pc_ret_sts_error;
1795
1796 END New_Dim_Attr_Default;
1797
1798 END FEM_DIM_ATTRIBUTES_UTIL_PKG;