[Home] [Help]
PACKAGE BODY: APPS.FEM_DIM_HIER_UTIL_PKG
Source
1 PACKAGE BODY FEM_Dim_Hier_Util_Pkg AS
2 --$Header: fem_dimhier_pkb.plb 120.3 2005/06/13 15:30:56 appldev $
3 /*==========================================================================+
4 | Copyright (c) 1997 Oracle Corporation, Redwood Shores, CA, USA |
5 | All rights reserved. |
6 +==========================================================================+
7 | FILENAME
8 |
9 | fem_di9mhier_pkh.pls
10 |
11 | NAME fem_dim_hier_util_pkg
12 |
13 | DESCRIPTION
14 |
15 | Package Body for fem_dim_hier_util_pkg
16 |
17 | HISTORY
18 |
19 | 17-JAN-05 tmoore Bug 4106880 - added following APIs:
20 | New_Hier_Object
21 | New_Hier_Object_Def
22 | New_GL_Cal_Period_Hier
23 | 13-JUN-05 gcheng Bug 4425976. Modified New_Hier_Object to validate
24 | against FND Lookups instead of relying on a
25 | hard-coded list of values.
26 |
27 +=========================================================================*/
28
29 -----------------------
30 -- Package Constants --
31 -----------------------
32 c_resp_app_id CONSTANT NUMBER := FND_GLOBAL.RESP_APPL_ID;
33
34 c_user_id CONSTANT NUMBER := FND_GLOBAL.USER_ID;
35 c_login_id NUMBER := FND_GLOBAL.Login_Id;
36
37 c_module_pkg CONSTANT VARCHAR2(80) := 'fem.plsql.fem_dimension_util_pkg';
38
39 f_set_status BOOLEAN;
40
41 c_log_level_1 CONSTANT NUMBER := fnd_log.level_statement;
42 c_log_level_2 CONSTANT NUMBER := fnd_log.level_procedure;
43 c_log_level_3 CONSTANT NUMBER := fnd_log.level_event;
44 c_log_level_4 CONSTANT NUMBER := fnd_log.level_exception;
45 c_log_level_5 CONSTANT NUMBER := fnd_log.level_error;
46 c_log_level_6 CONSTANT NUMBER := fnd_log.level_unexpected;
47
48 -----------------------
49 -- Package Variables --
50 -----------------------
51 v_module_log VARCHAR2(255);
52
53 v_session_ledger_id NUMBER := NULL;
54
55 v_varchar VARCHAR2(255);
56 v_param_req BOOLEAN;
57
58 v_attr_label VARCHAR2(30);
59 v_attr_code VARCHAR2(150);
60
61 v_token_value VARCHAR2(150);
62 v_token_trans VARCHAR2(1);
63
64 v_msg_text VARCHAR2(4000);
65
66 gv_prg_msg VARCHAR2(2000);
67 gv_callstack VARCHAR2(2000);
68
69 ------------------------
70 -- Package Exceptions --
71 ------------------------
72 e_bad_param_value EXCEPTION;
73 e_null_param_value EXCEPTION;
74 e_no_value_found EXCEPTION;
75 e_many_values_found EXCEPTION;
76 e_no_version_name EXCEPTION;
77 e_bad_dim_id EXCEPTION;
78 e_dup_mem_id EXCEPTION;
79 e_user_exception EXCEPTION;
80 e_dup_display_code EXCEPTION;
81 e_req_attr_assign EXCEPTION;
82 e_FEM_XDIM_UTIL_ATTR_NODEFAULT EXCEPTION;
83
84 /*************************************************************************
85
86 New_Hier_Object
87
88 *************************************************************************/
89
90 PROCEDURE New_Hier_Object (
91 p_api_version IN NUMBER DEFAULT c_api_version,
92 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
93 p_commit IN VARCHAR2 DEFAULT c_false,
94 p_encoded IN VARCHAR2 DEFAULT c_true,
95 x_return_status OUT NOCOPY VARCHAR2,
96 x_msg_count OUT NOCOPY NUMBER,
97 x_msg_data OUT NOCOPY VARCHAR2,
98 x_hier_obj_id OUT NOCOPY NUMBER,
99 x_hier_obj_def_id OUT NOCOPY NUMBER,
100 p_folder_id IN NUMBER,
101 p_global_vs_combo_id IN NUMBER,
102 p_object_access_code IN VARCHAR2,
103 p_object_origin_code IN VARCHAR2,
104 p_object_name IN VARCHAR2,
105 p_description IN VARCHAR2,
106 p_effective_start_date IN DATE DEFAULT sysdate,
107 p_effective_end_date IN DATE DEFAULT to_date
108 ('9999/01/01','YYYY/MM/DD'),
109 p_obj_def_name IN VARCHAR2,
110 p_dimension_id IN NUMBER,
111 p_hier_type_code IN VARCHAR2,
112 p_grp_seq_code IN VARCHAR2,
113 p_multi_top_flg IN VARCHAR2,
114 p_fin_ctg_flg IN VARCHAR2,
115 p_multi_vs_flg IN VARCHAR2,
116 p_hier_usage_code IN VARCHAR2,
117 p_flat_rows_flag IN VARCHAR2 DEFAULT 'N',
118 p_gl_period_type IN VARCHAR2 DEFAULT NULL,
119 p_calendar_id IN NUMBER DEFAULT NULL,
120 p_val_set_id1 IN NUMBER DEFAULT NULL,
121 p_val_set_id2 IN NUMBER DEFAULT NULL,
122 p_val_set_id3 IN NUMBER DEFAULT NULL,
123 p_val_set_id4 IN NUMBER DEFAULT NULL,
124 p_val_set_id5 IN NUMBER DEFAULT NULL,
125 p_val_set_id6 IN NUMBER DEFAULT NULL,
126 p_val_set_id7 IN NUMBER DEFAULT NULL,
127 p_val_set_id8 IN NUMBER DEFAULT NULL,
128 p_val_set_id9 IN NUMBER DEFAULT NULL,
129 p_dim_grp_id1 IN NUMBER DEFAULT NULL,
130 p_dim_grp_id2 IN NUMBER DEFAULT NULL,
131 p_dim_grp_id3 IN NUMBER DEFAULT NULL,
132 p_dim_grp_id4 IN NUMBER DEFAULT NULL,
133 p_dim_grp_id5 IN NUMBER DEFAULT NULL,
134 p_dim_grp_id6 IN NUMBER DEFAULT NULL,
135 p_dim_grp_id7 IN NUMBER DEFAULT NULL,
136 p_dim_grp_id8 IN NUMBER DEFAULT NULL,
137 p_dim_grp_id9 IN NUMBER DEFAULT NULL
138 )
139 IS
140
141 v_dim_name VARCHAR2(80);
142 v_dim_label VARCHAR2(150);
143 v_dim_hier_table VARCHAR2(30);
144 v_hier_allowed_code VARCHAR2(30);
145 v_group_use_code VARCHAR2(30);
146 v_vs_reqd_flg VARCHAR2(1);
147 v_object_id NUMBER;
148 v_obj_def_id NUMBER;
149 v_val_set_id NUMBER;
150 v_dim_grp_id NUMBER;
151 v_dim_grp_seq NUMBER;
152 v_calendar_id NUMBER;
153 v_dim_grp_count NUMBER;
154 v_val_set_count NUMBER;
155 v_lookup_code FND_LOOKUP_VALUES.lookup_code%TYPE;
156
157 -------------------------------------
158 -- Cursor to fetch Value Sets
159 -------------------------------------
160 CURSOR c_value_sets IS
161 SELECT value_set_id
162 FROM fem_value_sets_b
163 WHERE dimension_id = p_dimension_id
164 AND value_set_id IN
165 (p_val_set_id1,p_val_set_id2,p_val_set_id3,
166 p_val_set_id4,p_val_set_id5,p_val_set_id6,
167 p_val_set_id7,p_val_set_id8,p_val_set_id9);
168
169 -------------------------------------------
170 -- Cursor to fetch Dimension Groups
171 -------------------------------------------
172 CURSOR c_dim_grps IS
173 SELECT dimension_group_id
174 FROM fem_dimension_grps_b
175 WHERE dimension_id = p_dimension_id
176 AND dimension_group_id IN
177 (p_dim_grp_id1,p_dim_grp_id2,p_dim_grp_id3,
178 p_dim_grp_id4,p_dim_grp_id5,p_dim_grp_id6,
179 p_dim_grp_id7,p_dim_grp_id8,p_dim_grp_id9)
180 ORDER BY dimension_group_seq;
181
182 -------------------------------------------
183 -- Cursor to fetch FND Lookup Codes
184 -------------------------------------------
185 CURSOR c_fnd_lookups(p_lookup_type VARCHAR2, p_lookup_code VARCHAR2) IS
186 SELECT lookup_code
187 FROM fnd_lookup_values_vl
188 WHERE lookup_type = p_lookup_type
189 AND lookup_code = p_lookup_code;
190
191 ----------------------------------------------------
192 -- Arrays to store Value Set and Dimension Group IDs
193 ----------------------------------------------------
194 TYPE number_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
195 a_val_set_id number_array;
196 a_dim_grp_id number_array;
197 v_arr NUMBER; -- Array index
198
199 -------------
200 -- Exceptions
201 -------------
202 e_bad_dim_id EXCEPTION;
203 e_no_hiers EXCEPTION;
204 e_bad_hier_type EXCEPTION;
205 e_no_hier_type EXCEPTION;
206 e_bad_multi_top EXCEPTION;
207 e_bad_fin_ctg EXCEPTION;
208 e_bad_hier_usage EXCEPTION;
209 e_bad_multi_vs EXCEPTION;
210 e_no_calendar EXCEPTION;
211 e_bad_calendar EXCEPTION;
212 e_no_val_sets EXCEPTION;
213 e_no_multi_vs EXCEPTION;
214 e_bad_val_set EXCEPTION;
215 e_no_value_set EXCEPTION;
216 e_bad_grp_seq1 EXCEPTION;
217 e_bad_grp_seq2 EXCEPTION;
218 e_bad_grp_seq3 EXCEPTION;
219 e_no_dim_grps EXCEPTION;
220 e_bad_dim_grp EXCEPTION;
221 e_no_dim_group EXCEPTION;
222
223 BEGIN
224
225 x_return_status := c_success;
226 x_hier_obj_id := -1;
227 x_hier_obj_def_id := -1;
228
229 FEM_Dimension_Util_Pkg.Validate_OA_Params (
230 p_api_version => p_api_version,
231 p_init_msg_list => p_init_msg_list,
232 p_commit => p_commit,
233 p_encoded => p_encoded,
234 x_return_status => x_return_status);
235
236 IF (x_return_status <> c_success)
237 THEN
238 FND_MSG_PUB.Count_and_Get(
239 p_encoded => c_false,
240 p_count => x_msg_count,
241 p_data => x_msg_data);
242 RETURN;
243 END IF;
244
245 ----------------------------
246 -- Get dimension xdim values
247 ----------------------------
248 BEGIN
249 SELECT dimension_name,
250 dimension_varchar_label,
251 hierarchy_table_name,
252 hier_type_allowed_code,
253 group_use_code,
254 decode(dimension_varchar_label,'CAL_PERIOD','C',
255 value_set_required_flag)
256 INTO v_dim_name,
257 v_dim_label,
258 v_dim_hier_table,
259 v_hier_allowed_code,
260 v_group_use_code,
261 v_vs_reqd_flg
262 FROM fem_xdim_dimensions X,
263 fem_dimensions_vl V
264 WHERE X.dimension_id = p_dimension_id
265 AND V.dimension_id = X.dimension_id;
266 EXCEPTION
267 WHEN no_data_found THEN
268 RAISE e_bad_dim_id;
269 END;
270
271 ---------------------------------------------
272 -- Verify that dimension can have hierarchies
273 ---------------------------------------------
274 IF (v_dim_hier_table IS NULL OR
275 v_hier_allowed_code = 'NONE')
276 THEN
277 RAISE e_no_hiers;
278 END IF;
279
280 --------------------------------------
281 -- Verify hierarchy type specification
282 --------------------------------------
283 OPEN c_fnd_lookups('FEM_HIERARCHY_TYPE_DSC',p_hier_type_code);
284 FETCH c_fnd_lookups INTO v_lookup_code;
285 IF c_fnd_lookups%NOTFOUND THEN
286 CLOSE c_fnd_lookups;
287 RAISE e_bad_hier_type;
288 ELSE
289 CLOSE c_fnd_lookups;
290 END IF;
291
292 -- Hier type code need to match the hier allowed code
293 -- unless what is allowed is ALL
294 IF (v_hier_allowed_code <> 'ALL' AND
295 v_hier_allowed_code <> p_hier_type_code)
296 THEN
297 RAISE e_no_hier_type;
298 END IF;
299
300 ----------------------------------------
301 -- Verify other hierarchy specifications
302 ----------------------------------------
303 IF (p_multi_top_flg NOT IN ('Y','N'))
304 THEN
305 RAISE e_bad_multi_top;
306 END IF;
307
308 IF (p_fin_ctg_flg NOT IN ('Y','N'))
309 THEN
310 RAISE e_bad_fin_ctg;
311 END IF;
312
313 OPEN c_fnd_lookups('FEM_HIERARCHY_USAGE_DSC',p_hier_usage_code);
314 FETCH c_fnd_lookups INTO v_lookup_code;
315 IF c_fnd_lookups%NOTFOUND THEN
316 CLOSE c_fnd_lookups;
317 RAISE e_bad_hier_usage;
318 ELSE
319 CLOSE c_fnd_lookups;
320 END IF;
321
322 --------------------------------
323 -- Verify value set requirements
324 --------------------------------
325 IF (v_vs_reqd_flg <> 'C' AND
326 p_multi_vs_flg NOT IN ('Y','N'))
327 THEN
328 RAISE e_bad_multi_vs;
329 END IF;
330
331 IF (v_vs_reqd_flg = 'C' AND p_calendar_id IS NULL)
332 THEN
333 RAISE e_no_calendar;
334 END IF;
335
336 IF (v_vs_reqd_flg = 'C')
337 THEN
338 BEGIN
339 SELECT calendar_id
340 INTO v_calendar_id
341 FROM fem_calendars_b
342 WHERE calendar_id = p_calendar_id;
343 EXCEPTION
344 WHEN no_data_found THEN
345 RAISE e_bad_calendar;
346 END;
347 END IF;
348
349 --------------------
350 -- Verify Value Sets
351 --------------------
352 v_val_set_count := 0;
353 IF (v_vs_reqd_flg <> 'C')
354 THEN
355 a_val_set_id(1) := p_val_set_id1;
356 a_val_set_id(2) := p_val_set_id2;
357 a_val_set_id(3) := p_val_set_id3;
358 a_val_set_id(4) := p_val_set_id4;
359 a_val_set_id(5) := p_val_set_id5;
360 a_val_set_id(6) := p_val_set_id6;
361 a_val_set_id(7) := p_val_set_id7;
362 a_val_set_id(8) := p_val_set_id8;
363 a_val_set_id(9) := p_val_set_id9;
364
365 FOR v_arr IN 1..9 LOOP
366 IF (a_val_set_id(v_arr) IS NOT NULL)
367 THEN
368 IF (v_vs_reqd_flg = 'N')
369 THEN
370 RAISE e_no_val_sets;
371 END IF;
372
373 v_val_set_count := v_val_set_count + 1;
374
375 IF (p_multi_vs_flg = 'N' AND
376 v_val_set_count > 1)
377 THEN
378 RAISE e_no_multi_vs;
379 END IF;
380
381 BEGIN
382 SELECT value_set_id
383 INTO v_val_set_id
384 FROM fem_value_sets_b
385 WHERE dimension_id = p_dimension_id
386 AND value_set_id = a_val_set_id(v_arr);
387 EXCEPTION
388 WHEN no_data_found THEN
389 v_val_set_id := a_val_set_id(v_arr);
390 RAISE e_bad_val_set;
391 END;
392 END IF;
393 END LOOP;
394
395 IF (v_vs_reqd_flg = 'Y' AND
396 v_val_set_count = 0)
397 THEN
398 RAISE e_no_value_set;
399 END IF;
400 END IF;
401
402 --------------------------------
403 -- Verify grouping specification
404 --------------------------------
405 OPEN c_fnd_lookups('FEM_GROUP_SEQ_ENFORCED_DSC',p_grp_seq_code);
406 FETCH c_fnd_lookups INTO v_lookup_code;
407 IF c_fnd_lookups%NOTFOUND THEN
408 CLOSE c_fnd_lookups;
409 RAISE e_bad_grp_seq1;
410 ELSE
411 CLOSE c_fnd_lookups;
412 END IF;
413
414 IF (v_group_use_code = 'REQUIRED' AND
415 p_grp_seq_code = 'NO_GROUPS')
416 THEN
417 RAISE e_bad_grp_seq2;
418 END IF;
419
420 IF (v_group_use_code = 'NOT_SUPPORTED' AND
421 p_grp_seq_code <> 'NO_GROUPS')
422 THEN
423 RAISE e_bad_grp_seq3;
424 END IF;
425
426 --------------------------
427 -- Verify dimension groups
428 --------------------------
429 v_dim_grp_count := 0;
430
431 a_dim_grp_id(1) := p_dim_grp_id1;
432 a_dim_grp_id(2) := p_dim_grp_id2;
433 a_dim_grp_id(3) := p_dim_grp_id3;
434 a_dim_grp_id(4) := p_dim_grp_id4;
435 a_dim_grp_id(5) := p_dim_grp_id5;
436 a_dim_grp_id(6) := p_dim_grp_id6;
437 a_dim_grp_id(7) := p_dim_grp_id7;
438 a_dim_grp_id(8) := p_dim_grp_id8;
442 IF (a_dim_grp_id(v_arr) IS NOT NULL)
439 a_dim_grp_id(9) := p_dim_grp_id9;
440
441 FOR v_arr IN 1..9 LOOP
443 THEN
444 IF (p_grp_seq_code = 'NO_GROUPS')
445 THEN
446 RAISE e_no_dim_grps;
447 END IF;
448
449 v_dim_grp_count := v_dim_grp_count + 1;
450
451 BEGIN
452 SELECT dimension_group_id
453 INTO v_dim_grp_id
454 FROM fem_dimension_grps_b
455 WHERE dimension_id = p_dimension_id
456 AND dimension_group_id = a_dim_grp_id(v_arr);
457 EXCEPTION
458 WHEN no_data_found THEN
459 v_dim_grp_id := a_dim_grp_id(v_arr);
460 RAISE e_bad_dim_grp;
461 END;
462
463 END IF;
464 END LOOP;
465
466 IF (p_grp_seq_code <> 'NO_GROUPS' AND
467 v_dim_grp_count = 0)
468 THEN
469 RAISE e_no_dim_group;
470 END IF;
471
472 ----------------
473 -- Create Object
474 ----------------
475 FEM_Object_Catalog_Util_Pkg.Create_Object (
476 x_object_id => v_object_id,
477 x_object_definition_id => v_obj_def_id,
478 x_msg_count => x_msg_count,
479 x_msg_data => x_msg_data,
480 x_return_status => x_return_status,
481 p_api_version => p_api_version,
482 p_commit => p_commit,
483 p_object_type_code => 'HIERARCHY',
484 p_folder_id => p_folder_id,
485 p_local_vs_combo_id => p_global_vs_combo_id,
486 p_object_access_code => p_object_access_code,
487 p_object_origin_code => p_object_origin_code,
488 p_object_name => p_object_name,
489 p_description => p_description,
490 p_effective_start_date => p_effective_start_date,
491 p_effective_end_date => p_effective_end_date,
492 p_obj_def_name => p_obj_def_name);
493
494 IF (x_return_status <> c_success)
495 THEN
496 FND_MSG_PUB.Count_and_Get(
497 p_encoded => c_false,
498 p_count => x_msg_count,
499 p_data => x_msg_data);
500 RETURN;
501 END IF;
502
503 -------------------
504 -- Create Hierarchy
505 -------------------
506 INSERT INTO fem_hierarchies
507 (hierarchy_obj_id,
508 dimension_id,
509 hierarchy_type_code,
510 group_sequence_enforced_code,
511 multi_top_flag,
512 financial_category_flag,
513 multi_value_set_flag,
514 calendar_id,
515 period_type,
516 hierarchy_usage_code,
517 creation_date,
518 created_by,
519 last_updated_by,
520 last_update_date,
521 last_update_login,
522 personal_flag,
523 flattened_rows_flag,
524 object_version_number)
525 VALUES
526 (v_object_id,
527 p_dimension_id,
528 p_hier_type_code,
529 p_grp_seq_code,
530 p_multi_top_flg,
531 p_fin_ctg_flg,
532 p_multi_vs_flg,
533 v_calendar_id,
534 p_gl_period_type,
535 p_hier_usage_code,
536 sysdate,
537 c_user_id,
538 c_user_id,
539 sysdate,
540 null,
541 'N',
542 p_flat_rows_flag,
543 1);
544
545 INSERT INTO fem_hier_definitions
546 (hierarchy_obj_def_id,
547 creation_date,
548 created_by,
549 last_updated_by,
550 last_update_date,
551 last_update_login,
552 object_version_number,
553 flattened_rows_completion_code)
554 VALUES
555 (v_obj_def_id,
556 sysdate,
557 c_user_id,
558 c_user_id,
559 sysdate,
560 null,
561 1,'PENDING');
562
563 --------------------------
564 -- Create Hierarchy Groups
565 --------------------------
566 IF (p_grp_seq_code <> 'NO_GROUPS')
567 THEN
568 v_dim_grp_seq := 0;
569 FOR r_dim_grp IN c_dim_grps
570 LOOP
571 v_dim_grp_seq := v_dim_grp_seq + 1;
572 v_dim_grp_id := r_dim_grp.dimension_group_id;
573
574 INSERT INTO fem_hier_dimension_grps
575 (dimension_group_id,
576 hierarchy_obj_id,
577 relative_dimension_group_seq,
578 creation_date,
579 created_by,
580 last_updated_by,
581 last_update_date,
582 last_update_login,
583 object_version_number)
584 VALUES
585 (v_dim_grp_id,
586 v_object_id,
587 v_dim_grp_seq,
588 sysdate,
589 c_user_id,
590 c_user_id,
591 sysdate,
592 null,1);
593
594 END LOOP;
595 END IF;
596
597 ------------------------------
598 -- Create Hierarchy Value Sets
599 ------------------------------
600 IF (v_vs_reqd_flg = 'Y')
601 THEN
602 FOR r_value_set IN c_value_sets
603 LOOP
604 v_val_set_id := r_value_set.value_set_id;
605
606 INSERT INTO fem_hier_value_sets
607 (hierarchy_obj_id,
608 value_set_id,
609 creation_date,
610 created_by,
611 last_updated_by,
612 last_update_date,
613 last_update_login,
614 object_version_number)
615 VALUES
616 (v_object_id,
617 v_val_set_id,
618 sysdate,
619 c_user_id,
620 c_user_id,
621 sysdate,
622 null,1);
623 END LOOP;
624
625 ELSIF (v_vs_reqd_flg = 'C')
626 THEN
627 INSERT INTO fem_hier_value_sets
628 (hierarchy_obj_id,
629 value_set_id,
630 creation_date,
631 created_by,
632 last_updated_by,
633 last_update_date,
637 (v_object_id,
634 last_update_login,
635 object_version_number)
636 VALUES
638 v_calendar_id,
639 sysdate,
640 c_user_id,
641 c_user_id,
642 sysdate,
643 null,1);
644 END IF;
645
646 IF (p_commit = c_true)
647 THEN
648 COMMIT;
649 END IF;
650
651 x_hier_obj_id := v_object_id;
652 x_hier_obj_def_id := v_obj_def_id;
653
654 EXCEPTION
655 WHEN e_bad_dim_id THEN
656 FEM_ENGINES_PKG.Put_Message(
657 p_app_name => 'FEM',
658 p_msg_name => 'FEM_NEW_HIER_OBJ_BAD_DIM_ID',
659 p_token1 => 'DIM_ID',
660 p_value1 => p_dimension_id);
661 FND_MSG_PUB.Count_and_Get(
662 p_encoded => p_encoded,
663 p_count => x_msg_count,
664 p_data => x_msg_data);
665 x_return_status := c_error;
666
667 WHEN e_no_hiers THEN
668 FEM_ENGINES_PKG.Put_Message(
669 p_app_name => 'FEM',
670 p_msg_name => 'FEM_NEW_HIER_OBJ_NO_HIERS',
671 p_token1 => 'DIM_NAME',
672 p_value1 => v_dim_name);
673 FND_MSG_PUB.Count_and_Get(
674 p_encoded => p_encoded,
675 p_count => x_msg_count,
676 p_data => x_msg_data);
677 x_return_status := c_error;
678
679 WHEN e_bad_hier_type THEN
680 FEM_ENGINES_PKG.Put_Message(
681 p_app_name => 'FEM',
682 p_msg_name => 'FEM_NEW_HIER_OBJ_BAD_HIER_TYPE',
683 p_token1 => 'HIER_TYPE_CODE',
684 p_value1 => p_hier_type_code);
685 FND_MSG_PUB.Count_and_Get(
686 p_encoded => p_encoded,
687 p_count => x_msg_count,
688 p_data => x_msg_data);
689 x_return_status := c_error;
690
691 WHEN e_no_hier_type THEN
692 FEM_ENGINES_PKG.Put_Message(
693 p_app_name => 'FEM',
694 p_msg_name => 'FEM_NEW_HIER_OBJ_NO_HIER_TYPE',
695 p_token1 => 'DIM_NAME',
696 p_value1 => v_dim_name,
697 p_token2 => 'HIER_TYPE_CODE',
698 p_value2 => v_hier_allowed_code);
699 FND_MSG_PUB.Count_and_Get(
700 p_encoded => p_encoded,
701 p_count => x_msg_count,
702 p_data => x_msg_data);
703 x_return_status := c_error;
704
705 WHEN e_bad_multi_top THEN
706 FEM_ENGINES_PKG.Put_Message(
707 p_app_name => 'FEM',
708 p_msg_name => 'FEM_NEW_HIER_OBJ_BAD_MULTI_TOP');
709 FND_MSG_PUB.Count_and_Get(
710 p_encoded => p_encoded,
711 p_count => x_msg_count,
712 p_data => x_msg_data);
713 x_return_status := c_error;
714
715 WHEN e_bad_fin_ctg THEN
716 FEM_ENGINES_PKG.Put_Message(
717 p_app_name => 'FEM',
718 p_msg_name => 'FEM_NEW_HIER_OBJ_BAD_FIN_CTG');
719 FND_MSG_PUB.Count_and_Get(
720 p_encoded => p_encoded,
721 p_count => x_msg_count,
722 p_data => x_msg_data);
723 x_return_status := c_error;
724
725 WHEN e_bad_hier_usage THEN
726 FEM_ENGINES_PKG.Put_Message(
727 p_app_name => 'FEM',
728 p_msg_name => 'FEM_NEW_HIER_OBJ_BAD_HIER_USG',
729 p_token1 => 'HIER_USAGE',
730 p_value1 => p_hier_usage_code);
731 FND_MSG_PUB.Count_and_Get(
732 p_encoded => p_encoded,
733 p_count => x_msg_count,
734 p_data => x_msg_data);
735 x_return_status := c_error;
736
737 WHEN e_bad_multi_vs THEN
738 FEM_ENGINES_PKG.Put_Message(
739 p_app_name => 'FEM',
740 p_msg_name => 'FEM_NEW_HIER_OBJ_BAD_MULTI_VS');
741 FND_MSG_PUB.Count_and_Get(
742 p_encoded => p_encoded,
743 p_count => x_msg_count,
744 p_data => x_msg_data);
745 x_return_status := c_error;
746
747 WHEN e_no_calendar THEN
748 FEM_ENGINES_PKG.Put_Message(
749 p_app_name => 'FEM',
750 p_msg_name => 'FEM_NEW_HIER_OBJ_NO_CALENDAR',
751 p_token1 => 'DIM_NAME',
752 p_value1 => v_dim_name);
753 FND_MSG_PUB.Count_and_Get(
754 p_encoded => p_encoded,
755 p_count => x_msg_count,
756 p_data => x_msg_data);
757 x_return_status := c_error;
758
759 WHEN e_bad_calendar THEN
760 FEM_ENGINES_PKG.Put_Message(
761 p_app_name => 'FEM',
762 p_msg_name => 'FEM_NEW_HIER_OBJ_BAD_CALENDAR',
763 p_token1 => 'CAL_ID',
764 p_value1 => p_calendar_id);
765 FND_MSG_PUB.Count_and_Get(
766 p_encoded => p_encoded,
767 p_count => x_msg_count,
768 p_data => x_msg_data);
769 x_return_status := c_error;
770
771 WHEN e_no_val_sets THEN
772 FEM_ENGINES_PKG.Put_Message(
773 p_app_name => 'FEM',
774 p_msg_name => 'FEM_NEW_HIER_OBJ_NO_VAL_SETS',
775 p_token1 => 'DIM_NAME',
776 p_value1 => v_dim_name);
777 FND_MSG_PUB.Count_and_Get(
778 p_encoded => p_encoded,
779 p_count => x_msg_count,
780 p_data => x_msg_data);
781 x_return_status := c_error;
782
783 WHEN e_bad_val_set THEN
784 FEM_ENGINES_PKG.Put_Message(
785 p_app_name => 'FEM',
786 p_msg_name => 'FEM_NEW_HIER_OBJ_BAD_VAL_SET',
787 p_token1 => 'VAL_SET',
788 p_value1 => v_val_set_id,
789 p_token2 => 'DIM_NAME',
790 p_value2 => v_dim_name);
791 FND_MSG_PUB.Count_and_Get(
792 p_encoded => p_encoded,
793 p_count => x_msg_count,
794 p_data => x_msg_data);
795 x_return_status := c_error;
799 p_app_name => 'FEM',
796
797 WHEN e_no_multi_vs THEN
798 FEM_ENGINES_PKG.Put_Message(
800 p_msg_name => 'FEM_NEW_HIER_OBJ_NO_MULTI_VS');
801 FND_MSG_PUB.Count_and_Get(
802 p_encoded => p_encoded,
803 p_count => x_msg_count,
804 p_data => x_msg_data);
805 x_return_status := c_error;
806
807 WHEN e_no_value_set THEN
808 FEM_ENGINES_PKG.Put_Message(
809 p_app_name => 'FEM',
810 p_msg_name => 'FEM_NEW_HIER_OBJ_NO_VALUE_SET',
811 p_token1 => 'DIM_NAME',
812 p_value1 => v_dim_name);
813 FND_MSG_PUB.Count_and_Get(
814 p_encoded => p_encoded,
815 p_count => x_msg_count,
816 p_data => x_msg_data);
817 x_return_status := c_error;
818
819 WHEN e_bad_grp_seq1 THEN
820 FEM_ENGINES_PKG.Put_Message(
821 p_app_name => 'FEM',
822 p_msg_name => 'FEM_NEW_HIER_OBJ_BAD_GRP_SEQ1',
823 p_token1 => 'GRP_SEQ_CODE',
824 p_value1 => p_grp_seq_code);
825 FND_MSG_PUB.Count_and_Get(
826 p_encoded => p_encoded,
827 p_count => x_msg_count,
828 p_data => x_msg_data);
829 x_return_status := c_error;
830
831 WHEN e_bad_grp_seq2 THEN
832 FEM_ENGINES_PKG.Put_Message(
833 p_app_name => 'FEM',
834 p_msg_name => 'FEM_NEW_HIER_OBJ_BAD_GRP_SEQ2',
835 p_token1 => 'DIM_NAME',
836 p_value1 => v_dim_name);
837 FND_MSG_PUB.Count_and_Get(
838 p_encoded => p_encoded,
839 p_count => x_msg_count,
840 p_data => x_msg_data);
841 x_return_status := c_error;
842
843 WHEN e_bad_grp_seq3 THEN
844 FEM_ENGINES_PKG.Put_Message(
845 p_app_name => 'FEM',
846 p_msg_name => 'FEM_NEW_HIER_OBJ_BAD_GRP_SEQ3',
847 p_token1 => 'DIM_NAME',
848 p_value1 => v_dim_name);
849 FND_MSG_PUB.Count_and_Get(
850 p_encoded => p_encoded,
851 p_count => x_msg_count,
852 p_data => x_msg_data);
853 x_return_status := c_error;
854
855 WHEN e_no_dim_grps THEN
856 FEM_ENGINES_PKG.Put_Message(
857 p_app_name => 'FEM',
858 p_msg_name => 'FEM_NEW_HIER_OBJ_NO_DIM_GRPS',
859 p_token1 => 'DIM_NAME',
860 p_value1 => v_dim_name);
861 FND_MSG_PUB.Count_and_Get(
862 p_encoded => p_encoded,
863 p_count => x_msg_count,
864 p_data => x_msg_data);
865 x_return_status := c_error;
866
867 WHEN e_bad_dim_grp THEN
868 FEM_ENGINES_PKG.Put_Message(
869 p_app_name => 'FEM',
870 p_msg_name => 'FEM_NEW_HIER_OBJ_BAD_DIM_GRP',
871 p_token1 => 'DIM_GRP',
872 p_value1 => v_dim_grp_id,
873 p_token2 => 'DIM_NAME',
874 p_value2 => v_dim_name);
875 FND_MSG_PUB.Count_and_Get(
876 p_encoded => p_encoded,
877 p_count => x_msg_count,
878 p_data => x_msg_data);
879 x_return_status := c_error;
880
881 WHEN e_no_dim_group THEN
882 FEM_ENGINES_PKG.Put_Message(
883 p_app_name => 'FEM',
884 p_msg_name => 'FEM_NEW_HIER_OBJ_NO_DIM_GROUP',
885 p_token1 => 'DIM_NAME',
886 p_value1 => v_dim_name);
887 FND_MSG_PUB.Count_and_Get(
888 p_encoded => p_encoded,
889 p_count => x_msg_count,
890 p_data => x_msg_data);
891 x_return_status := c_error;
892
893 END New_Hier_Object;
894
895 /*************************************************************************
896
897 New_Hier_Object_Def
898
899 *************************************************************************/
900
901 PROCEDURE New_Hier_Object_Def (
902 p_api_version IN NUMBER DEFAULT c_api_version,
903 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
904 p_commit IN VARCHAR2 DEFAULT c_false,
905 p_encoded IN VARCHAR2 DEFAULT c_true,
906 x_return_status OUT NOCOPY VARCHAR2,
907 x_msg_count OUT NOCOPY NUMBER,
908 x_msg_data OUT NOCOPY VARCHAR2,
909 x_hier_obj_def_id OUT NOCOPY NUMBER,
910 p_hier_obj_id IN NUMBER,
911 p_obj_def_name IN VARCHAR2,
912 p_effective_start_date IN DATE,
913 p_effective_end_date IN DATE,
914 p_object_origin_code IN VARCHAR2
915 )
916 IS
917
918 v_hier_obj_id NUMBER;
919 v_obj_def_id NUMBER;
920
921 e_no_hier_obj_id EXCEPTION;
922
923 BEGIN
924
925 x_return_status := c_success;
926 x_hier_obj_def_id := -1;
927
928 FEM_Dimension_Util_Pkg.Validate_OA_Params (
929 p_api_version => p_api_version,
930 p_init_msg_list => p_init_msg_list,
931 p_commit => p_commit,
932 p_encoded => p_encoded,
933 x_return_status => x_return_status);
934
935 IF (x_return_status <> c_success)
936 THEN
937 FND_MSG_PUB.Count_and_Get(
938 p_encoded => c_false,
939 p_count => x_msg_count,
940 p_data => x_msg_data);
941 RETURN;
942 END IF;
943
944 -----------------------------
945 -- Verify Hierarchy Object ID
946 -----------------------------
947 BEGIN
948 SELECT hierarchy_obj_id
949 INTO v_hier_obj_id
950 FROM fem_hierarchies
951 WHERE hierarchy_obj_id = p_hier_obj_id;
952 EXCEPTION
953 WHEN no_data_found THEN
954 RAISE e_no_hier_obj_id;
955 END;
956
957 ------------------------------
958 -- Create Hierarchy Definition
962 x_msg_count => x_msg_count,
959 ------------------------------
960 FEM_Object_Catalog_Util_Pkg.Create_Object_Definition (
961 x_object_definition_id => v_obj_def_id,
963 x_msg_data => x_msg_data,
964 x_return_status => x_return_status,
965 p_api_version => p_api_version,
966 p_commit => p_commit,
967 p_object_id => v_hier_obj_id,
968 p_effective_start_date => p_effective_start_date,
969 p_effective_end_date => p_effective_end_date,
970 p_obj_def_name => p_obj_def_name,
971 p_object_origin_code => p_object_origin_code);
972
973 IF (x_return_status <> c_success)
974 THEN
975 FND_MSG_PUB.Count_and_Get(
976 p_encoded => c_false,
977 p_count => x_msg_count,
978 p_data => x_msg_data);
979 RETURN;
980 END IF;
981
982 INSERT INTO fem_hier_definitions
983 (hierarchy_obj_def_id,
984 creation_date,
985 created_by,
986 last_updated_by,
987 last_update_date,
988 last_update_login,
989 object_version_number,
990 flattened_rows_completion_code)
991 VALUES
992 (v_obj_def_id,
993 sysdate,
994 c_user_id,
995 c_user_id,
996 sysdate,
997 null,
998 1,'PENDING');
999
1000 IF (p_commit = c_true)
1001 THEN
1002 COMMIT;
1003 END IF;
1004
1005 x_hier_obj_def_id := v_obj_def_id;
1006
1007 EXCEPTION
1008 WHEN e_no_hier_obj_id THEN
1009 FEM_ENGINES_PKG.Put_Message(
1010 p_app_name => 'FEM',
1011 p_msg_name => 'FEM_NEW_HIER_OBJ_NO_HIER_OBJ',
1012 p_token1 => 'HIER_OBJ',
1013 p_value1 => p_hier_obj_id);
1014 FND_MSG_PUB.Count_and_Get(
1015 p_encoded => p_encoded,
1016 p_count => x_msg_count,
1017 p_data => x_msg_data);
1018 x_return_status := c_error;
1019
1020 END New_Hier_Object_Def;
1021
1022 /*************************************************************************
1023
1024 New_GL_Cal_Period_Hier
1025
1026 *************************************************************************/
1027
1028 PROCEDURE New_GL_Cal_Period_Hier (
1029 p_api_version IN NUMBER DEFAULT c_api_version,
1030 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
1031 p_commit IN VARCHAR2 DEFAULT c_false,
1032 p_encoded IN VARCHAR2 DEFAULT c_true,
1033 x_return_status OUT NOCOPY VARCHAR2,
1034 x_msg_count OUT NOCOPY NUMBER,
1035 x_msg_data OUT NOCOPY VARCHAR2,
1036 x_hier_obj_id OUT NOCOPY NUMBER,
1037 x_hier_obj_def_id OUT NOCOPY NUMBER,
1038 p_folder_id IN NUMBER,
1039 p_object_access_code IN VARCHAR2,
1040 p_object_origin_code IN VARCHAR2,
1041 p_object_name IN VARCHAR2,
1042 p_description IN VARCHAR2,
1043 p_effective_start_date IN DATE DEFAULT sysdate,
1044 p_effective_end_date IN DATE DEFAULT to_date
1045 ('9999/01/01','YYYY/MM/DD'),
1046 p_obj_def_name IN VARCHAR2,
1047 p_grp_seq_code IN VARCHAR2,
1048 p_multi_top_flg IN VARCHAR2,
1049 p_gl_period_type IN VARCHAR2,
1050 p_dim_grp_id IN NUMBER,
1051 p_calendar_id IN NUMBER
1052 )
1053 IS
1054
1055 v_hier_allowed_code VARCHAR2(30);
1056 v_cal_per_dim_id NUMBER;
1057 v_year_grp_id NUMBER;
1058 v_qtr_grp_id NUMBER;
1059
1060 BEGIN
1061
1062 FEM_Dimension_Util_Pkg.Validate_OA_Params (
1063 p_api_version => p_api_version,
1064 p_init_msg_list => p_init_msg_list,
1065 p_commit => p_commit,
1066 p_encoded => p_encoded,
1067 x_return_status => x_return_status);
1068
1069 IF (x_return_status <> c_success)
1070 THEN
1071 FND_MSG_PUB.Count_and_Get(
1072 p_encoded => c_false,
1073 p_count => x_msg_count,
1074 p_data => x_msg_data);
1075 RETURN;
1076 END IF;
1077
1078 -----------------------------
1079 -- Get Cal Period Xdim Values
1080 -----------------------------
1081 SELECT B.dimension_id,
1082 X.hier_type_allowed_code
1083 INTO v_cal_per_dim_id,
1084 v_hier_allowed_code
1085 FROM fem_xdim_dimensions X,
1086 fem_dimensions_b B
1087 WHERE B.dimension_varchar_label = 'CAL_PERIOD'
1088 AND B.dimension_id = X.dimension_id;
1089
1090 -----------------------------------------------
1091 -- Get Dimension Group IDs for Year and Quarter
1092 -----------------------------------------------
1093 IF (p_dim_grp_id IS NOT NULL)
1094 THEN
1095 SELECT Y.dimension_group_id,
1096 Q.dimension_group_id
1097 INTO v_year_grp_id,
1098 v_qtr_grp_id
1099 FROM fem_dimension_grps_b Y,
1100 fem_dimension_grps_b Q
1101 WHERE Y.dimension_group_display_code = 'Year'
1102 AND Q.dimension_group_display_code = 'Quarter'
1103 AND Y.dimension_id = 1
1104 AND Q.dimension_id = 1;
1105 ELSE
1106 v_year_grp_id := null;
1107 v_qtr_grp_id := null;
1108 END IF;
1109
1110 -------------------
1111 -- Create Hierarchy
1112 -------------------
1113 New_Hier_Object (
1114 p_api_version => p_api_version,
1115 p_init_msg_list => p_init_msg_list,
1116 p_commit => p_commit,
1117 p_encoded => p_encoded,
1118 x_return_status => x_return_status,
1119 x_msg_count => x_msg_count,
1120 x_msg_data => x_msg_data,
1121 x_hier_obj_id => x_hier_obj_id,
1122 x_hier_obj_def_id => x_hier_obj_def_id,
1123 p_folder_id => p_folder_id,
1124 p_global_vs_combo_id => null,
1125 p_object_access_code => p_object_access_code,
1126 p_object_origin_code => p_object_origin_code,
1127 p_object_name => p_object_name,
1128 p_description => p_description,
1129 p_effective_start_date => p_effective_start_date,
1130 p_effective_end_date => p_effective_end_date,
1131 p_obj_def_name => p_obj_def_name,
1132 p_dimension_id => v_cal_per_dim_id,
1133 p_hier_type_code => v_hier_allowed_code,
1134 p_grp_seq_code => p_grp_seq_code,
1135 p_multi_top_flg => p_multi_top_flg,
1136 p_fin_ctg_flg => 'N',
1137 p_multi_vs_flg => 'N',
1138 p_hier_usage_code => 'STANDARD',
1139 p_gl_period_type => p_gl_period_type,
1140 p_calendar_id => p_calendar_id,
1141 p_dim_grp_id1 => p_dim_grp_id,
1142 p_dim_grp_id2 => v_year_grp_id,
1143 p_dim_grp_id3 => v_qtr_grp_id);
1144
1145 IF (p_commit = c_true)
1146 THEN
1147 COMMIT;
1148 END IF;
1149
1150 END New_GL_Cal_Period_Hier;
1151
1152 END FEM_Dim_Hier_Util_Pkg;