[Home] [Help]
PACKAGE BODY: APPS.FEM_WEBADI_HIER_UTILS_PVT
Source
1 PACKAGE BODY FEM_WEBADI_HIER_UTILS_PVT AS
2 /* $Header: FEMVADIHIERUTILB.pls 120.2 2007/10/26 10:31:35 lkiran ship $*/
3
4 ------------------------------
5 -- Declare Global variables --
6 ------------------------------
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FEM_WEBADI_HIER_UTILS_PVT' ;
8 --
9 -- Global variables to handle population
10 -- of Hier Header info only once.
11 --
12 --
13 ------------------------------
14 -- Write Private Procedures --
15 ------------------------------
16 /*===========================================================================+
17 Procedure Name : Upload_Hierarchy_Header
18 +===========================================================================*/
19 PROCEDURE log_message
20 (p_debug_message IN VARCHAR2
21 )
22 IS
23 --
24 PRAGMA AUTONOMOUS_TRANSACTION ;
25 --
26 BEGIN
27 --
28 -- Put your debug message here.
29 NULL ;
30 --COMMIT ;
31 --
32 END ;
33
34 PROCEDURE Delete_Old_Run
35 ( p_hierarchy_object_name IN VARCHAR2
36 , p_hierarchy_obj_def_disp_name IN VARCHAR2
37 )
38 IS
39 --
40 l_delete_str VARCHAR2(4000) := NULL ;
41 l_status VARCHAR2(4) := 'LOAD' ;
42 --
43 BEGIN
44 --
45 -- Delete from fem_hier_value_sets_t table
46 --
47 DELETE FROM
48 fem_hier_value_sets_t hiervset
49 WHERE
50 hiervset.hierarchy_object_name = p_hierarchy_object_name
51 AND hiervset.value_set_display_code = g_global_val_tbl(1).value_set_display_code;
52
53 --
54 -- Delete from intf_hierarchy_table_name table
55 --
56 l_delete_str := 'DELETE FROM ' ||
57 g_global_val_tbl(1).hierarchy_intf_table_name|| ' hier' ||
58 ' WHERE ' ||
59 ' hier.hierarchy_object_name = ' ||
60 ' :b_hierarchy_object_name ' ||
61 ' AND hier.hierarchy_obj_def_display_name = ' ||
62 ' :b_hier_obj_def_display_name ' ;
63 --
64 --
65 EXECUTE IMMEDIATE
66 l_delete_str
67 USING
68 p_hierarchy_object_name
69 , p_hierarchy_obj_def_disp_name;
70 --
71 -- Delete from fem_hier_dim_grps_t table
72 --
73 DELETE FROM
74 fem_hier_dim_grps_t dimgrp
75 WHERE
76 dimgrp.hierarchy_object_name = p_hierarchy_object_name;
77
78 END Delete_Old_Run;
79
80 /*===========================================================================+
81 Procedure Name : Upload_Hierarchy_Header
82 Parameters :
83 IN : p_api_version NUMBER
84 p_init_msg_lis VARCHAR2
85 p_commit VARCHAR2
86 p_intf_hierarchy_table_name VARCHAR2
87 p_value_set_required_flag VARCHAR2
88 p_dimension_varchar_label VARCHAR2
89 p_hierarchy_object_name VARCHAR2
90 p_hierarchy_obj_def_disp_name VARCHAR2
91 p_folder_name VARCHAR2
92 p_hierarchy_type_code VARCHAR2
93 p_multi_top_flag VARCHAR2
94 p_multi_value_set_flag VARCHAR2
95 p_calendar_display_code VARCHAR2
96 p_hierarchy_usage_code VARCHAR2
97 p_group_sequence_enforced_code VARCHAR2
98 p_effective_start_date DATE
99 p_effective_end_date DATE
100 p_language VARCHAR2
101 OUT : x_return_status VARCHAR2
102 x_msg_count NUMBER
103 x_msg_data VARCHAR2
104
105 Description : Populates Hierarchy Header information.
106
107 Modification History :
108 Date Name Desc
109 ---------- --------- -------------------------------------------------------
110 10/05/2005 SHTRIPAT Created.
111 ---------- --------- -------------------------------------------------------
112 +===========================================================================*/
113 PROCEDURE Upload_Hierarchy_Header
114 ( x_return_status OUT NOCOPY VARCHAR2
115 , x_msg_count OUT NOCOPY NUMBER
116 , x_msg_data OUT NOCOPY VARCHAR2
117 , p_api_version IN NUMBER
118 , p_init_msg_list IN VARCHAR2
119 , p_commit IN VARCHAR2
120 , p_intf_hierarchy_table_name IN VARCHAR2
121 , p_value_set_required_flag IN VARCHAR2
122 , p_dimension_varchar_label IN VARCHAR2
123 , p_hierarchy_object_name IN VARCHAR2
124 , p_hierarchy_obj_def_disp_name IN VARCHAR2
125 , p_folder_name IN VARCHAR2
126 , p_hierarchy_type_code IN VARCHAR2
127 , p_multi_top_flag IN VARCHAR2
128 , p_multi_value_set_flag IN VARCHAR2
129 , p_calendar_display_code IN VARCHAR2
130 , p_hierarchy_usage_code IN VARCHAR2
131 , p_group_sequence_enforced_code IN VARCHAR2
132 , p_effective_start_date IN DATE
133 , p_effective_end_date IN DATE
134 , p_language IN VARCHAR2
135 )
136 IS
137 --
138 l_api_name CONSTANT VARCHAR2(30) := 'Upload_Hierarchy_Header' ;
139 --
140 l_return_status VARCHAR2(1) ;
141 l_msg_count NUMBER ;
142 l_msg_data VARCHAR2(2000) ;
143 --
144 l_record_count NUMBER := -1 ;
145 --
146 l_flattened_rows_flag VARCHAR2(1) := 'N' ;
147 l_status VARCHAR2(30) := NULL;
148 --
149 l_dim_migration_flag VARCHAR2(1) := NULL ;
150 --
151 BEGIN
152 --
153 SAVEPOINT Upload_Hierarchy_Header ;
154 --
155
156 BEGIN
157 SELECT STATUS
158 INTO l_status
159 FROM FEM_HIERARCHIES_T
160 WHERE HIERARCHY_OBJECT_NAME = p_hierarchy_object_name
161 AND HIER_OBJ_DEF_DISPLAY_NAME = p_hierarchy_obj_def_disp_name;
162 EXCEPTION
163 WHEN NO_DATA_FOUND THEN NULL;
164 END;
165
166 IF (l_status is null)
167 THEN
168
169 INSERT
170 INTO
171 fem_hierarchies_t
172 ( hierarchy_object_name
173 , folder_name
174 , language
175 , dimension_varchar_label
176 , hierarchy_type_code
177 , group_sequence_enforced_code
178 , multi_top_flag
179 , multi_value_set_flag
180 , hierarchy_usage_code
181 , flattened_rows_flag
182 , status
183 , hier_obj_def_display_name
184 , effective_start_date
185 , effective_end_date
186 , calendar_display_code
187 , created_by_dim_migration_flag
188 )
189 VALUES
190 ( p_hierarchy_object_name
191 , p_folder_name
192 , p_language
193 , p_dimension_varchar_label
194 , p_hierarchy_type_code
195 , p_group_sequence_enforced_code
196 , p_multi_top_flag
197 , p_multi_value_set_flag
198 , p_hierarchy_usage_code
199 , l_flattened_rows_flag
200 , 'LOAD'
201 , p_hierarchy_obj_def_disp_name
202 , p_effective_start_date
203 , p_effective_end_date
204 , p_calendar_display_code
205 , l_dim_migration_flag
206 ) ;
207
208 ELSIF (l_status <> 'LOAD')
209 THEN
210
211 UPDATE
212 fem_hierarchies_t hier
213 SET
214 hier.hierarchy_type_code = p_hierarchy_type_code
215 , hier.multi_top_flag = p_multi_top_flag
216 , hier.multi_value_set_flag = p_multi_value_set_flag
217 , hier.hierarchy_usage_code = p_hierarchy_usage_code
218 , hier.group_sequence_enforced_code = p_group_sequence_enforced_code
219 , hier.effective_start_date = p_effective_start_date
220 , hier.effective_end_date = p_effective_end_date
221 , hier.folder_name = p_folder_name
222 , hier.dimension_varchar_label = p_dimension_varchar_label
223 , language = p_language
224 , status = 'LOAD'
225 WHERE
226 hier.hierarchy_object_name = p_hierarchy_object_name
227 AND hier.hier_obj_def_display_name = p_hierarchy_obj_def_disp_name ;
228
229 Delete_Old_Run
230 ( p_hierarchy_object_name => p_hierarchy_object_name
231 , p_hierarchy_obj_def_disp_name => p_hierarchy_obj_def_disp_name
232 ) ;
233
234 END IF;
235 --
236 IF FND_API.To_Boolean ( p_commit )
237 THEN
238 COMMIT ;
239 END IF ;
240 --
241 -- Initialize API return status to success
242 x_return_status := FND_API.G_RET_STS_SUCCESS ;
243 --
244 -- Commenting out the Exception block to
245 -- propogate the exact exception back to
246 -- Web ADI.
247 -- Needs discussion.
248 /*EXCEPTION
249 WHEN FND_API.G_EXC_ERROR THEN
250 ROLLBACK TO Upload_Hierarchy_Header ;
251 --
252 x_return_status := FND_API.G_RET_STS_ERROR ;
253 --
254 FND_MSG_PUB.Count_And_Get
255 ( p_count => x_msg_count,
256 p_data => x_msg_data
257 ) ;
258 --
259 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
260 ROLLBACK TO Upload_Hierarchy_Header ;
261 --
262 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
263 --
264 FND_MSG_PUB.Count_And_Get
265 ( p_count => x_msg_count,
266 p_data => x_msg_data
267 ) ;
268 --
269 WHEN OTHERS THEN
270 ROLLBACK TO Upload_Hierarchy_Header ;
271 --
272 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
273 --
274 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
275 THEN
276 FND_MSG_PUB.Add_Exc_Msg
277 ( p_pkg_name => G_PKG_NAME,
278 p_procedure_name => l_api_name
279 ) ;
280 END IF;
281 --
282 FND_MSG_PUB.Count_And_Get
283 ( p_count => x_msg_count,
284 p_data => x_msg_data
285 ) ;
286 --*/
287 END Upload_Hierarchy_Header ;
288
289 /*===========================================================================+
290 Procedure Name : Insert_HierInfo_forTime_Dim
291 Parameters :
292 IN : p_hierarchy_object_name VARCHAR2
293 p_hierarchy_obj_def_disp_name VARCHAR2
294 p_parent_display_code VARCHAR2
295 p_child_display_code VARCHAR2
296 p_language VARCHAR2
297 OUT : x_return_status VARCHAR2
298 x_msg_count NUMBER
299 x_msg_data VARCHAR2
300
301 Description : Populates Hierarchy Detail information
302 for Time Dimension.
303
304 Modification History :
305 Date Name Desc
306 ---------- --------- -------------------------------------------------------
307 10/05/2005 SHTRIPAT Created.
308 ---------- --------- -------------------------------------------------------
309 +===========================================================================*/
310 PROCEDURE Insert_HierInfo_forTime_Dim
311 ( x_return_status OUT NOCOPY VARCHAR2
312 , x_msg_count OUT NOCOPY NUMBER
313 , x_msg_data OUT NOCOPY VARCHAR2
314 , p_hierarchy_object_name IN VARCHAR2
315 , p_hierarchy_obj_def_disp_name IN VARCHAR2
316 , p_parent_display_code IN VARCHAR2
317 , p_child_display_code IN VARCHAR2
318 , p_child_grp_disp_code IN VARCHAR2
319 , p_parent_grp_disp_code IN VARCHAR2
320 , p_language IN VARCHAR2
321 )
322 IS
323 --
324 l_api_name CONSTANT VARCHAR2(30) := 'Insert_HierInfo_forTime_Dim' ;
325 --
326 l_grp_dtl_sql VARCHAR2(4000) ; -- Dimension Group Detail sql
327 --
328 l_parnt_grp_disp_code
329 fem_dimension_grps_b.dimension_group_display_code%TYPE := NULL ;
330 l_parnt_grp_seq
331 fem_dimension_grps_b.dimension_group_seq%TYPE := NULL ;
332 l_child_grp_disp_code
333 fem_dimension_grps_b.dimension_group_display_code%TYPE := NULL ;
334 l_child_grp_seq
335 fem_dimension_grps_b.dimension_group_seq%TYPE := NULL ;
336 --
337 l_disp_order_num_sql VARCHAR2(4000) := NULL ;
338 l_next_disp_order_num NUMBER ;
339 l_required_flag VARCHAR2(1) := 'Y' ;
340 l_dimension_id NUMBER := NULL ;
341 --
342 l_parent_cp_end_date DATE := NULL ;
343 l_parent_period_num NUMBER := NULL ;
344 l_child_cp_end_date DATE := NULL ;
345 l_child_period_num NUMBER := NULL ;
346 --
347 l_end_date_label CONSTANT VARCHAR2(19) := 'CAL_PERIOD_END_DATE' ;
348 l_gl_period_label CONSTANT VARCHAR2(13) := 'GL_PERIOD_NUM' ;
349 --
350 l_hier_t_insert_sql VARCHAR2(4000) := NULL ;
351 l_hier_t_update_sql VARCHAR2(4000) := NULL ;
352 --
353 l_language VARCHAR2(50) := USERENV('LANG') ;
354 l_err_message_text VARCHAR2(4000) ;
355 --
356 l_status VARCHAR2(4) := 'LOAD' ;
357 --
358 -- Cursor to retrieve date_assign_value and number_assign_value
359 -- from CAL_PERIOD_ATTR table for given display_code
360 CURSOR l_cal_period_csr
361 ( display_code VARCHAR2
362 , dim_id NUMBER
363 , end_date_label VARCHAR2
364 , gl_period_label VARCHAR2
365 )
366 IS
367 SELECT
368 cpattr.date_assign_value
369 , cpattr.number_assign_value
370 , dimattr.attribute_varchar_label
371 FROM
372 fem_dim_attributes_b dimattr
373 , fem_cal_periods_attr cpattr
374 WHERE
375 cpattr.cal_period_id = display_code
376 AND cpattr.version_id = ( SELECT
377 attrver.version_id
378 FROM
379 fem_dim_attr_versions_b attrver
380 WHERE
381 attrver.attribute_id =
382 cpattr.attribute_id
383 AND attrver.default_version_flag =
384 l_required_flag
385 )
386 AND cpattr.attribute_id = dimattr.attribute_id
387 AND dimattr.dimension_id = dim_id
388 AND ( dimattr.attribute_varchar_label = end_date_label
389 OR
390 dimattr.attribute_varchar_label = gl_period_label
391 ) ;
392 --
393 BEGIN
394 --
395
396 SAVEPOINT Insert_HierInfo_forTime_Dim ;
397 --
398 l_dimension_id := g_global_val_tbl(1).dimension_id ;
399 --
400 -- Retrieve l_parent_cp_end_date and l_parent_period_num
401 FOR l_cal_period_csr_rec IN l_cal_period_csr ( p_parent_display_code
402 , l_dimension_id
403 , l_end_date_label
404 , l_gl_period_label
405 )
406 LOOP
407 --
411 ELSE
408 IF ( l_cal_period_csr_rec.attribute_varchar_label = l_end_date_label )
409 THEN
410 l_parent_cp_end_date := l_cal_period_csr_rec.date_assign_value ;
412 l_parent_period_num := l_cal_period_csr_rec.number_assign_value ;
413 END IF ;
414 --
415 END LOOP ;
416 --
417 -- Retrieve the next display_order_number from hier_intf table.
418 l_disp_order_num_sql := 'SELECT' ||
419 ' NVL(MAX(hier_intf.display_order_num), 0)' ||
420 ' FROM ' ||
421 g_global_val_tbl(1).hierarchy_intf_table_name ||
422 ' hier_intf ' ||
423 'WHERE ' ||
424 'hier_intf.parent_cal_period_end_date = :1' ||
425 ' AND hier_intf.parent_cal_period_number' ||
426 ' = :2' ;
427 --
428 BEGIN
429 --
430 EXECUTE IMMEDIATE
431 l_disp_order_num_sql
432 INTO
433 l_next_disp_order_num
434 USING
435 l_parent_cp_end_date
436 , l_parent_period_num ;
437 --
438 l_next_disp_order_num := l_next_disp_order_num + 1 ;
439 --
440 END ;
441 --
442 -- If p_child_disply_code <> p_parent_display_code, then
443 -- repeat the above logic to find out l_child_cp_end_date
444 -- and l_child_period_num
445 IF ( p_child_display_code <> p_parent_display_code )
446 THEN
447 --
448 FOR l_cal_period_csr_rec IN l_cal_period_csr ( p_child_display_code
449 , l_dimension_id
450 , l_end_date_label
451 , l_gl_period_label
452 )
453 LOOP
454 --
455 IF ( l_cal_period_csr_rec.attribute_varchar_label = l_end_date_label )
456 THEN
457 l_child_cp_end_date := l_cal_period_csr_rec.date_assign_value ;
458 ELSE
459 l_child_period_num := l_cal_period_csr_rec.number_assign_value ;
460 END IF ;
461 --
462 END LOOP ;
463 --
464 ELSE
465 --
466 l_child_cp_end_date := l_parent_cp_end_date ;
467 l_child_period_num := l_parent_period_num ;
468 --
469 END IF ;
470 --
471 -- Prepare insert sql for hier_t table.
472 l_hier_t_insert_sql := 'INSERT INTO ' ||
473 g_global_val_tbl(1).hierarchy_intf_table_name ||
474 '( hierarchy_object_name'||
475 ', hierarchy_obj_def_display_name' ||
476 ', parent_cal_period_end_date' ||
477 ', parent_cal_period_number' ||
478 ', child_cal_period_end_date' ||
479 ', child_cal_period_number' ||
480 ', parent_dim_grp_display_code' ||
481 ', child_dim_grp_display_code' ||
482 ', calendar_display_code' ||
483 ', display_order_num' ||
484 ', language' ||
485 ', status' ||
486 ')' ||
487 'VALUES' ||
488 '( :hier_obj_name' ||
489 ', :hier_obj_def_disp_name' ||
490 ', :parent_cal_period_end_date' ||
491 ', :parent_cp_number' ||
492 ', :child_cp_end_date' ||
493 ', :child_cp_number' ||
494 ', :parent_dim_grp_disp_code' ||
495 ', :child_dim_grp_disp_code' ||
496 ', :calendar_disp_code' ||
497 ', :disp_order_num' ||
498 ', :lang' ||
499 ', :stts' ||
500 ')' ;
501 --
502 BEGIN
503 --
504 EXECUTE IMMEDIATE
505 l_hier_t_insert_sql
506 USING
507 p_hierarchy_object_name
508 , p_hierarchy_obj_def_disp_name
509 , l_parent_cp_end_date
510 , l_parent_period_num
511 , l_child_cp_end_date
512 , l_child_period_num
513 , p_parent_grp_disp_code
514 , p_child_grp_disp_code
515 , g_global_val_tbl(1).calendar_display_code
516 , l_next_disp_order_num
517 , p_language
518 , 'LOAD' ;
519 --
520 EXCEPTION
521 --
522 WHEN DUP_VAL_ON_INDEX THEN
523 --
524 l_hier_t_update_sql := 'UPDATE ' ||
525 g_global_val_tbl(1).hierarchy_intf_table_name ||
526 ' SET ' ||
527 ' calendar_display_code = ' ||
528 ' :b_calendar_display_code' ||
529 ' , display_order_num = ' ||
530 ' :b_display_order_num' ||
531 ' , language = :b_language ' ||
532 ' , status = :b_status' ||
533 ' WHERE ' ||
534 ' hierarchy_object_name = ' ||
535 ' :b_hierarchy_object_name ' ||
539 ' :b_parent_dim_grp_disp_code ' ||
536 ' AND hierarchy_obj_def_display_name = ' ||
537 ' :b_hier_obj_def_disp_name ' ||
538 ' AND parent_dim_grp_display_code = ' ||
540 ' AND parent_cal_period_end_date = ' ||
541 ' :b_parent_cal_period_end_date ' ||
542 ' AND parent_cal_period_number = ' ||
543 ' :b_parent_cal_period_number ' ||
544 ' AND child_dim_grp_display_code = ' ||
545 ' :b_child_dim_grp_display_code ' ||
546 ' AND child_cal_period_end_date = ' ||
547 ' :b_child_cal_period_end_date ' ||
548 ' AND child_cal_period_number = ' ||
549 ' :b_child_cal_period_number' ;
550
551 EXECUTE IMMEDIATE
552 l_hier_t_update_sql
553 USING
554 g_global_val_tbl(1).calendar_display_code
555 , l_next_disp_order_num
556 , p_language
557 , 'LOAD'
558 , p_hierarchy_object_name
559 , p_hierarchy_obj_def_disp_name
560 , p_parent_grp_disp_code
561 , l_parent_cp_end_date
562 , l_parent_period_num
563 , p_child_grp_disp_code
564 , l_child_cp_end_date
565 , l_child_period_num ;
566 --
567 END ;
568 --
569 -- Initialize API return status to success
570 x_return_status := FND_API.G_RET_STS_SUCCESS ;
571 --
572 -- Commenting out the Exception block to
573 -- propogate the exact exception back to
574 -- Web ADI.
575 -- Needs discussion.
576 /*EXCEPTION
577 --
578 WHEN OTHERS THEN
579 ROLLBACK TO Insert_HierInfo_forTime_Dim ;
580 --
581 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
582 --
583 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
584 THEN
585 FND_MSG_PUB.Add_Exc_Msg
586 ( p_pkg_name => G_PKG_NAME,
587 p_procedure_name => l_api_name
588 ) ;
589 END IF;
590 --
591 FND_MSG_PUB.Count_And_Get
592 ( p_count => x_msg_count,
593 p_data => x_msg_data
594 ) ;
595 --*/
596 END Insert_HierInfo_forTime_Dim ;
597
598 /*===========================================================================+
599 Procedure Name : Insert_HierInfo_forNonTime_Dim
600 Parameters :
601 IN : p_hierarchy_object_name VARCHAR2
602 p_hierarchy_obj_def_disp_name VARCHAR2
603 p_parent_display_code VARCHAR2
604 p_child_display_code VARCHAR2
605 p_language VARCHAR2
606 OUT : x_return_status VARCHAR2
607 x_msg_count NUMBER
608 x_msg_data VARCHAR2
609
610 Description : Populates Hierarchy Detail information
611 for Non Time dimensions.
612
613 Modification History :
614 Date Name Desc
615 ---------- --------- -------------------------------------------------------
616 10/05/2005 SHTRIPAT Created.
617 ---------- --------- -------------------------------------------------------
618 +===========================================================================*/
619 PROCEDURE Insert_HierInfo_forNonTime_Dim
620 ( x_return_status OUT NOCOPY VARCHAR2
621 , x_msg_count OUT NOCOPY NUMBER
622 , x_msg_data OUT NOCOPY VARCHAR2
623 , p_hierarchy_object_name IN VARCHAR2
627 , p_language IN VARCHAR2
624 , p_hierarchy_obj_def_disp_name IN VARCHAR2
625 , p_parent_display_code IN VARCHAR2
626 , p_child_display_code IN VARCHAR2
628 )
629 IS
630 --
631 l_api_name CONSTANT VARCHAR2(30) :=
632 'Insert_HierInfo_forNonTime_Dim' ;
633 --
634 --Bug#5959147: Increase variable size to match db col size
635 l_parent_vs_disp_code VARCHAR2(150) := NULL ;
636 l_child_vs_disp_code VARCHAR2(150) := NULL ;
637 --
638 l_disp_order_num_sql VARCHAR2(4000) := NULL ;
639 l_next_disp_order_num NUMBER ;
640 --
641 l_hier_t_ins_select_clause VARCHAR2(4000) := NULL ;
642 l_hier_t_ins_values_clause VARCHAR2(4000) := NULL ;
643 l_hier_t_insert_sql VARCHAR2(4000) := NULL ;
644 --
645 l_hier_t_upd_set_clause VARCHAR2(4000) := NULL ;
646 l_hier_t_upd_where_clause VARCHAR2(4000) := NULL ;
647 l_hier_t_update_sql VARCHAR2(4000) := NULL ;
648 --
649 l_status VARCHAR2(4) := 'LOAD' ;
650 --
651 BEGIN
652 --
653 SAVEPOINT Insert_HierInfo_forNonTime_Dim ;
654 --
655 -- Now populate hierarchy_intf_table_name table.
656 IF ( g_global_val_tbl(1).value_set_required_flag = 'Y' )
657 THEN
658 --
659 l_parent_vs_disp_code := g_global_val_tbl(1).value_set_display_code ;
660 l_child_vs_disp_code := g_global_val_tbl(1).value_set_display_code ;
661 --
662 -- fem_hier_value_sets_t is an interface table that designates
663 -- the value sets for Analytic dimension hierarchies that will
664 -- be loaded using the Dimension Hierarchy Loader.
665 -- This will be done only when creating new hierarchies of any
666 -- dimension except for the Calendar Period.
667 BEGIN
668 INSERT FIRST
669 WHEN ( cnt = 0 ) THEN
670 INTO
671 fem_hier_value_sets_t
672 ( hierarchy_object_name
673 , value_set_display_code
674 , language
675 , status
676 )
677 VALUES
678 ( p_hierarchy_object_name
679 , l_parent_vs_disp_code -- Reusing the display code. Nothing special.
680 , p_language
681 , l_status
682 )
683 SELECT
684 COUNT(1) AS cnt
685 FROM
686 fem_hier_value_sets_t hiervs
687 WHERE
688 hiervs.hierarchy_object_name = p_hierarchy_object_name
689 AND hiervs.value_set_display_code = l_parent_vs_disp_code ;
690 --
691 IF ( SQL%ROWCOUNT = 0 ) -- Record already exists.
692 THEN
693 --
694 UPDATE
695 fem_hier_value_sets_t hiervset
696 SET
697 hiervset.language = p_language
698 , hiervset.status = l_status
699 WHERE
700 hiervset.hierarchy_object_name = p_hierarchy_object_name
701 AND hiervset.value_set_display_code = l_parent_vs_disp_code ;
702 --
703 END IF ;
704 END ;
705 --
706 END IF ;
707 --
708 -- Retrieve the next display_order_number from hier_intf table.
709 l_disp_order_num_sql := 'SELECT' ||
710 ' NVL(MAX(hier_intf.display_order_num), 0)' ||
711 ' FROM ' ||
712 g_global_val_tbl(1).hierarchy_intf_table_name ||
713 ' hier_intf' ||
714 ' WHERE' ||
715 ' hier_intf.parent_display_code = :disp_code ' ;
716 --
717 BEGIN
718 --
719 EXECUTE IMMEDIATE
720 l_disp_order_num_sql
721 INTO
722 l_next_disp_order_num
723 USING
724 p_parent_display_code ;
725 --
726 l_next_disp_order_num := l_next_disp_order_num + 1 ;
727 --
728 END ;
729 --
730 -- Prepare insert sql for hier_t table.
731 l_hier_t_ins_select_clause := 'INSERT INTO ' ||
732 g_global_val_tbl(1).hierarchy_intf_table_name ||
733 '( hierarchy_object_name'||
734 ', hierarchy_obj_def_display_name' ||
735 ', parent_display_code' ||
736 ', child_display_code' ||
737 ', display_order_num' ||
738 ', language' ||
739 ', status' ;
740 --
741 l_hier_t_ins_values_clause := 'VALUES' ||
742 '( :hier_obj_name' ||
743 ', :hier_obj_def_disp_name' ||
744 ', :parent_disp_code' ||
745 ', :child_disp_code' ||
746 ', :disp_order_num' ||
747 ', :lang' ||
748 ', :stts' ;
749 --
750 l_hier_t_upd_set_clause := 'UPDATE ' ||
751 g_global_val_tbl(1).hierarchy_intf_table_name ||
752 ' SET ' ||
753 ' display_order_num = :b_display_order_num' ||
754 ' , language = :b_language' ||
755 ' , status = :b_status' ;
756 --
760 ' AND hierarchy_obj_def_display_name = ' ||
757 l_hier_t_upd_where_clause := ' WHERE ' ||
758 ' hierarchy_object_name = ' ||
759 ' :b_hierarchy_object_name ' ||
761 ' :b_hier_obj_def_display_name ' ||
762 ' AND parent_display_code = ' ||
763 ' :b_parent_display_code ' ||
764 ' AND child_display_code = ' ||
765 ' :b_child_display_code ' ;
766 --
767 IF ( g_global_val_tbl(1).value_set_required_flag = 'Y' )
768 THEN
769 --
770 l_hier_t_ins_select_clause := l_hier_t_ins_select_clause ||
771 ', parent_value_set_display_code' ||
772 ', child_value_set_display_code' ||
773 ')' ;
774 --
775 l_hier_t_ins_values_clause := l_hier_t_ins_values_clause ||
776 ', :parent_vs_disp_code' ||
777 ', :child_vs_disp_code' ||
778 ')' ;
779 --
780 l_hier_t_insert_sql := l_hier_t_ins_select_clause ||
781 l_hier_t_ins_values_clause ;
782 --
783 BEGIN
784 --
785 EXECUTE IMMEDIATE
786 l_hier_t_insert_sql
787 USING
788 p_hierarchy_object_name
789 , p_hierarchy_obj_def_disp_name
790 , p_parent_display_code
791 , p_child_display_code
792 , l_next_disp_order_num
793 , p_language
794 , 'LOAD'
795 , l_parent_vs_disp_code
796 , l_child_vs_disp_code ;
797 --
798 EXCEPTION
799 --
800 WHEN DUP_VAL_ON_INDEX THEN
801 --
802 l_hier_t_upd_where_clause := l_hier_t_upd_where_clause ||
803 ' AND parent_value_set_display_code = ' ||
804 ' :b_parent_value_set_disp_code ' ||
805 ' AND child_value_set_display_code = ' ||
806 ' :b_child_value_set_disp_code' ;
807 --
808 l_hier_t_update_sql := l_hier_t_upd_set_clause ||
809 l_hier_t_upd_where_clause ;
810 --
811 EXECUTE IMMEDIATE
812 l_hier_t_update_sql
813 USING
814 l_next_disp_order_num
815 , p_language
816 , 'LOAD'
817 , p_hierarchy_object_name
818 , p_hierarchy_obj_def_disp_name
819 , p_parent_display_code
820 , p_child_display_code
821 , l_parent_vs_disp_code
822 , l_child_vs_disp_code ;
823 --
824 END ;
825 --
826 ELSE
827 --
828 l_hier_t_insert_sql := l_hier_t_ins_select_clause ||
829 ')' ||
830 l_hier_t_ins_values_clause ||
831 ')' ;
832 --
833 BEGIN
834 --
835 EXECUTE IMMEDIATE
836 l_hier_t_insert_sql
837 USING
838 p_hierarchy_object_name
839 , p_hierarchy_obj_def_disp_name
840 , p_parent_display_code
841 , p_child_display_code
842 , l_next_disp_order_num
843 , p_language
844 , 'LOAD' ;
845 --
846 EXCEPTION
847 --
848 WHEN DUP_VAL_ON_INDEX THEN
849 --
850 l_hier_t_update_sql := l_hier_t_upd_set_clause ||
851 l_hier_t_upd_where_clause ;
852 --
853 EXECUTE IMMEDIATE
854 l_hier_t_update_sql
855 USING
856 l_next_disp_order_num
857 , p_language
858 , 'LOAD'
859 , p_hierarchy_object_name
860 , p_hierarchy_obj_def_disp_name
861 , p_parent_display_code
862 , p_child_display_code ;
863 --
864 END ;
865 --
866 END IF ;
867 --
868 -- Commenting out the Exception block to
869 -- propogate the exact exception back to
870 -- Web ADI.
871 -- Needs discussion.
872 /*EXCEPTION
873 --
874 WHEN OTHERS THEN
875 ROLLBACK TO Upload_Hierarchy_Details ;
876 --
877 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
878 --
879 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
880 THEN
881 FND_MSG_PUB.Add_Exc_Msg
882 ( p_pkg_name => G_PKG_NAME,
883 p_procedure_name => l_api_name
884 ) ;
885 END IF;
886 --
887 FND_MSG_PUB.Count_And_Get
888 ( p_count => x_msg_count,
889 p_data => x_msg_data
890 ) ;
891 --*/
892 END Insert_HierInfo_forNonTime_Dim ;
893
894 /*===========================================================================+
895 Procedure Name : Upload_Hierarchy_Details
896 Parameters :
897 IN : p_api_version NUMBER
898 p_init_msg_lis VARCHAR2
899 p_commit VARCHAR2
900 p_intf_hierarchy_table_name VARCHAR2
904 p_hierarchy_obj_def_disp_name VARCHAR2
901 p_value_set_required_flag VARCHAR2
902 p_dimension_varchar_label VARCHAR2
903 p_hierarchy_object_name VARCHAR2
905 p_folder_name VARCHAR2
906 p_hierarchy_type_code VARCHAR2
907 p_multi_top_flag VARCHAR2
908 p_multi_value_set_flag VARCHAR2
909 p_calendar_display_code VARCHAR2
910 p_hierarchy_usage_code VARCHAR2
911 p_group_sequence_enforced_code VARCHAR2
912 p_effective_start_date DATE
913 p_effective_end_date DATE
914 p_language VARCHAR2
915 OUT : x_return_status VARCHAR2
916 x_msg_count NUMBER
917 x_msg_data VARCHAR2
918
919 Description : Populates Hierarchy Detail information.
920
921 Modification History :
922 Date Name Desc
923 ---------- --------- -------------------------------------------------------
924 10/05/2005 SHTRIPAT Created.
925 ---------- --------- -------------------------------------------------------
926 +===========================================================================*/
927 PROCEDURE Upload_Hierarchy_Details
928 ( x_return_status OUT NOCOPY VARCHAR2
929 , x_msg_count OUT NOCOPY NUMBER
930 , x_msg_data OUT NOCOPY VARCHAR2
931 , p_api_version IN NUMBER
932 , p_init_msg_list IN VARCHAR2
933 , p_commit IN VARCHAR2
934 , p_language IN VARCHAR2
935 , p_hierarchy_object_name IN VARCHAR2
936 , p_hierarchy_obj_def_disp_name IN VARCHAR2
937 , p_sequence_enforced_code IN VARCHAR2
938 , p_calendar_display_code IN VARCHAR2
939 , p_parent_display_code IN VARCHAR2
940 , p_child_display_code IN VARCHAR2
941 )
942 IS
943 --
944 l_api_name CONSTANT VARCHAR2(30) := 'Upload_Hierarchy_Details' ;
945 --
946 l_return_status VARCHAR2(1) ;
947 l_msg_count NUMBER ;
948 l_msg_data VARCHAR2(2000) ;
949 --
950 l_grp_dtl_sql VARCHAR2(4000) ; -- Dimension Group Detail sql
951 --
952 l_parnt_grp_disp_code
953 fem_dimension_grps_b.dimension_group_display_code%TYPE := NULL ;
954 l_parnt_grp_seq
955 fem_dimension_grps_b.dimension_group_seq%TYPE := NULL ;
956 l_child_grp_disp_code
957 fem_dimension_grps_b.dimension_group_display_code%TYPE := NULL ;
958 l_child_grp_seq
959 fem_dimension_grps_b.dimension_group_seq%TYPE := NULL ;
960 --
961 l_disp_order_num_sql VARCHAR2(4000) := NULL ;
962 l_next_disp_order_num NUMBER ;
963 l_required_flag VARCHAR2(1) := 'Y' ;
964 l_dimension_id NUMBER := NULL ;
965 --
966 l_parent_cp_end_date DATE := NULL ;
967 l_parent_period_num NUMBER := NULL ;
968 --
969 l_end_date_label CONSTANT VARCHAR2(19) := 'CAL_PERIOD_END_DATE' ;
970 l_gl_period_label CONSTANT VARCHAR2(13) := 'GL_PERIOD_NUM' ;
971 --
972 l_language VARCHAR2(50) := USERENV('LANG') ;
973 l_err_message_text VARCHAR2(4000) ;
974 --
975 l_status VARCHAR2(4) := 'LOAD' ;
976 --
977 BEGIN
978 --
979 SAVEPOINT Upload_Hierarchy_Details ;
980 --
981 --! STEP 1 !--
982 -- If a hierarchy does not use Groups
983 IF ( p_sequence_enforced_code <> 'NO_GROUPS' )
984 THEN
985 --
986 -- Frame the sql to retrieve parent_group_display_code, parent_group_seq
987 -- and child_group_display_code, child_group_seq.
988 l_grp_dtl_sql := 'SELECT' ||
989 ' dimgrp.dimension_group_display_code' ||
990 ', dimgrp.dimension_group_seq ' ||
991 'FROM' ||
992 ' ' || g_global_val_tbl(1).member_b_table_name ||
993 ' dimmem' ||
994 ', fem_dimension_grps_b dimgrp ' ||
995 'WHERE' ||
996 ' dimmem.'||
997 g_global_val_tbl(1).member_display_code_col ||
998 ' = :b_display_code' ||
999 ' AND dimmem.dimension_group_id =' ||
1000 ' dimgrp.dimension_group_id' ;
1001 --
1002 BEGIN
1003 --
1004 -- Run the query for child_display_code.
1005 EXECUTE IMMEDIATE
1006 l_grp_dtl_sql
1007 INTO
1008 l_child_grp_disp_code
1009 , l_child_grp_seq
1010 USING
1011 p_child_display_code;
1012 --
1013 END ;
1014 --
1015 -- Now do Insert/Update in fem_hier_dim_grps_t table
1016 -- with l_child_grp_disp_code.
1017 BEGIN
1018 --
1019 INSERT
1020 INTO
1021 fem_hier_dim_grps_t
1022 ( hierarchy_object_name
1023 , language
1027 VALUES
1024 , status
1025 , dimension_group_display_code
1026 )
1028 ( p_hierarchy_object_name
1029 , l_language
1030 , l_status
1031 , l_child_grp_disp_code
1032 ) ;
1033 --
1034 EXCEPTION
1035 WHEN DUP_VAL_ON_INDEX THEN
1036 --
1037 UPDATE
1038 fem_hier_dim_grps_t hiergrp
1039 SET
1040 hiergrp.language = l_language
1041 , hiergrp.status = l_status
1042 WHERE
1043 hiergrp.dimension_group_display_code = l_child_grp_disp_code
1044 AND hiergrp.hierarchy_object_name = p_hierarchy_object_name ;
1045 --
1046 END ;
1047 --
1048 -- If p_parent_display_code and p_child_display_code
1049 -- are different, get the details for p_parent_display_code
1050 -- and do the DML in fem_hier_dim_grps_t.
1051 IF ( p_parent_display_code <> p_child_display_code )
1052 THEN
1053 --
1054 BEGIN
1055 --
1056 -- Run the query for parent_display_code.
1057 EXECUTE IMMEDIATE
1058 l_grp_dtl_sql
1059 INTO
1060 l_parnt_grp_disp_code
1061 , l_parnt_grp_seq
1062 USING
1063 p_parent_display_code ;
1064 --
1065 INSERT
1066 INTO
1067 fem_hier_dim_grps_t
1068 ( hierarchy_object_name
1069 , language
1070 , status
1071 , dimension_group_display_code
1072 )
1073 VALUES
1074 ( p_hierarchy_object_name
1075 , l_language
1076 , l_status
1077 , l_parnt_grp_disp_code
1078 ) ;
1079 --
1080 EXCEPTION
1081 WHEN DUP_VAL_ON_INDEX THEN
1082 --
1083 UPDATE
1084 fem_hier_dim_grps_t hiergrp
1085 SET
1086 hiergrp.language = l_language
1087 , hiergrp.status = l_status
1088 WHERE
1089 hiergrp.dimension_group_display_code = l_parnt_grp_disp_code
1090 AND hiergrp.hierarchy_object_name = p_hierarchy_object_name ;
1091 --
1092 END ;
1093 --
1094 ELSE
1095 -- Since parent_display_code and p_child_code are same,
1096 -- assign l_parnt_grp_disp_code and l_parnt_grp_seq
1097 -- to l_child_grp_disp_code and l_child_grp_seq respectivily.
1098 l_parnt_grp_disp_code := l_child_grp_disp_code ;
1099 l_parnt_grp_seq := l_child_grp_seq ;
1100 END IF ;
1101 --
1102 END IF ;
1103 --
1104 --! STEP 2 !--
1105 -- Now populate hierarchy_intf_table_name table.
1106
1107 -- If not a TIME dimension, then proceed.
1108 IF ( g_global_val_tbl(1).dimension_type_code <> 'TIME' )
1109 THEN
1110 --
1111 Insert_HierInfo_forNonTime_Dim
1112 ( x_return_status => l_return_status
1113 , x_msg_count => l_msg_count
1114 , x_msg_data => l_msg_data
1115 , p_hierarchy_object_name => p_hierarchy_object_name
1116 , p_hierarchy_obj_def_disp_name => p_hierarchy_obj_def_disp_name
1117 , p_parent_display_code => p_parent_display_code
1118 , p_child_display_code => p_child_display_code
1119 , p_language => l_language
1120 ) ;
1121 --
1122 ELSE -- If TIME dimension then proceed.
1123 --
1124 Insert_HierInfo_forTime_Dim
1125 ( x_return_status => l_return_status
1126 , x_msg_count => l_msg_count
1127 , x_msg_data => l_msg_data
1128 , p_hierarchy_object_name => p_hierarchy_object_name
1129 , p_hierarchy_obj_def_disp_name => p_hierarchy_obj_def_disp_name
1130 , p_parent_display_code => p_parent_display_code
1131 , p_child_display_code => p_child_display_code
1132 , p_child_grp_disp_code => l_child_grp_disp_code
1133 , p_parent_grp_disp_code => l_parnt_grp_disp_code
1134 , p_language => l_language
1135 ) ;
1136 --
1137 END IF ;
1138 --
1139 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS )
1140 THEN
1141 RAISE FND_API.G_EXC_ERROR ;
1142 END IF ;
1143 --
1144 IF ( FND_API.To_Boolean (p_commit) )
1145 THEN
1146 COMMIT ;
1147 END IF ;
1148 --
1149 -- Initialize API return status to success
1150 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1151 --
1152 -- Commenting out the Exception block to
1153 -- propogate the exact exception back to
1154 -- Web ADI.
1155 -- Needs discussion.
1156 /*EXCEPTION
1157 WHEN FND_API.G_EXC_ERROR THEN
1158 ROLLBACK TO Upload_Hierarchy_Details ;
1159 --
1160 x_return_status := FND_API.G_RET_STS_ERROR ;
1161 --
1162 FND_MSG_PUB.Count_And_Get
1163 ( p_count => x_msg_count,
1164 p_data => x_msg_data
1165 ) ;
1166 --
1167 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1168 ROLLBACK TO Upload_Hierarchy_Details ;
1169 --
1170 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1171 --
1172 FND_MSG_PUB.Count_And_Get
1173 ( p_count => x_msg_count,
1174 p_data => x_msg_data
1175 ) ;
1176 --
1177 WHEN OTHERS THEN
1181 --
1178 ROLLBACK TO Upload_Hierarchy_Details ;
1179 --
1180 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1182 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1183 THEN
1184 FND_MSG_PUB.Add_Exc_Msg
1185 ( p_pkg_name => G_PKG_NAME,
1186 p_procedure_name => l_api_name
1187 ) ;
1188 END IF;
1189 --
1190 FND_MSG_PUB.Count_And_Get
1191 ( p_count => x_msg_count,
1192 p_data => x_msg_data
1193 ) ;
1194 --*/
1195 END Upload_Hierarchy_Details ;
1196
1197 /*===========================================================================+
1198 Procedure Name : Populate_Dim_Metadata_Info
1199 Parameters :
1200 IN : p_dimension_varchar_label VARCHAR2
1201 OUT : x_return_status VARCHAR2
1202
1203 Description : Populates global variables with metadata information
1204 of the supplied p_dimension_varchar_label.
1205
1206 Modification History :
1207 Date Name Desc
1208 ---------- --------- -------------------------------------------------------
1209 09/23/2005 SHTRIPAT Created.
1210 ---------- --------- -------------------------------------------------------
1211 +===========================================================================*/
1212 PROCEDURE Populate_Dim_Metadata_Info
1213 ( x_return_status OUT NOCOPY VARCHAR2
1214 , p_dimension_varchar_label IN VARCHAR2
1215 )
1216 IS
1217 --
1218 -- Retrieve the metadata information of
1219 -- the supplied p_dimension_varchar_label.
1220 CURSOR l_Ret_Dim_Metadata_csr
1221 IS
1222 SELECT
1223 dimension_id
1224 , intf_member_b_table_name
1225 , intf_member_tl_table_name
1226 , intf_attribute_table_name
1227 , member_b_table_name
1228 , member_display_code_col
1229 , member_name_col
1230 , hierarchy_table_name
1231 , dimension_type_code
1232 , group_use_code
1233 , value_set_required_flag
1234 FROM
1235 fem_xdim_dimensions_vl xDimVL
1236 WHERE
1237 xDimVL.dimension_varchar_label = p_dimension_varchar_label ;
1238 --
1239 -- Retrieve the value_set_display_code for given ledger_id
1240 -- and dimension_id.
1241 CURSOR l_VS_Disp_Code_csr
1242 IS
1243 SELECT
1244 VS.value_set_display_code
1245 FROM
1246 fem_Value_Sets_vl VS
1247 WHERE
1248 VS.value_set_id = ( FEM_DIMENSION_UTIL_PKG.Dimension_Value_Set_Id
1249 ( g_global_val_tbl(1).dimension_id -- p_dimension_id
1250 , g_global_val_tbl(1).ledger_id -- p_ledger_id
1251 )
1252 ) ;
1253 --
1254 BEGIN
1255 --
1256 -- Populate global variables with Dimemension metadata.
1257 FOR l_ret_dim_metadata_csr_rec IN l_Ret_Dim_Metadata_csr
1258 LOOP
1259 g_global_val_tbl(1).dimension_id :=
1260 l_ret_dim_metadata_csr_rec.dimension_id ;
1261 g_global_val_tbl(1).dimension_varchar_label :=
1262 p_dimension_varchar_label ;
1263 g_global_val_tbl(1).intf_member_b_table_name :=
1264 l_ret_dim_metadata_csr_rec.intf_member_b_table_name ;
1265 g_global_val_tbl(1).intf_member_tl_table_name :=
1266 l_ret_dim_metadata_csr_rec.intf_member_tl_table_name ;
1267 g_global_val_tbl(1).intf_attribute_table_name :=
1268 l_ret_dim_metadata_csr_rec.intf_attribute_table_name ;
1269 g_global_val_tbl(1).member_b_table_name :=
1270 l_ret_dim_metadata_csr_rec.member_b_table_name ;
1271 g_global_val_tbl(1).member_display_code_col :=
1272 l_ret_dim_metadata_csr_rec.member_display_code_col ;
1273 g_global_val_tbl(1).member_name_col :=
1274 l_ret_dim_metadata_csr_rec.member_name_col ;
1275 g_global_val_tbl(1).hierarchy_intf_table_name :=
1276 l_ret_dim_metadata_csr_rec.hierarchy_table_name || '_T' ;
1277 g_global_val_tbl(1).dimension_type_code :=
1278 NVL( l_ret_dim_metadata_csr_rec.dimension_type_code, 'XYZ' ) ;
1279 g_global_val_tbl(1).group_use_code :=
1280 NVL( l_ret_dim_metadata_csr_rec.group_use_code, 'NOT_SUPPORTED' ) ;
1281 g_global_val_tbl(1).value_set_required_flag :=
1282 NVL( l_ret_dim_metadata_csr_rec.value_set_required_flag, 'N' ) ;
1283 END LOOP ;
1284 --
1285 -- Get the Value_Set_Display_code if
1286 -- g_global_val_tbl(1).value_set_required_flag is Y.
1287 g_global_val_tbl(1).value_set_display_code := NULL ;
1288 IF ( g_global_val_tbl(1).value_set_required_flag = 'Y' )
1289 THEN
1290 --
1291 FOR l_VS_Disp_Code_csr_rec IN l_VS_Disp_Code_csr
1292 LOOP
1293 --
1294 g_global_val_tbl(1).value_set_display_code :=
1295 l_VS_Disp_Code_csr_rec.value_set_display_code ;
1296 --
1297 END LOOP ;
1298 --
1299 END IF ;
1300 --
1301 g_global_val_tbl(1).dim_grp_disp_code := NULL ;
1302 -- If group_use_code is <> NOT_SUPPORTED, then
1303 -- assign the global variable.
1304 IF ( g_global_val_tbl(1).group_use_code <> 'NOT_SUPPORTED' )
1305 THEN
1306 g_global_val_tbl(1).dim_grp_disp_code :=
1307 g_global_val_tbl(1).group_use_code ;
1308 END IF ;
1309 --
1310 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1314 -- Web ADI.
1311 --
1312 -- Commenting out the Exception block to
1313 -- propogate the exact exception back to
1315 -- Needs discussion.
1316 /*EXCEPTION
1317 WHEN FND_API.G_EXC_ERROR THEN
1318 ROLLBACK TO Populate_Dim_Metadata_Info ;
1319 --
1320 x_return_status := FND_API.G_RET_STS_ERROR ;
1321 --
1322 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1323 ROLLBACK TO Populate_Dim_Metadata_Info ;
1324 --
1325 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1326 --
1327 WHEN OTHERS THEN
1328 ROLLBACK TO Populate_Dim_Metadata_Info ;
1329 --
1330 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1331 --*/
1332 END Populate_Dim_Metadata_Info ;
1333
1334 -----------------------------
1335 -- Write Public Procedures --
1336 -----------------------------
1337
1338 /*===========================================================================+
1339 Procedure Name : Upload_Hierarchy_Interface
1340 Parameters :
1341 IN : p_folder_name VARCHAR2
1342 p_hierarchy_type_code VARCHAR2
1343 p_multi_top_flag VARCHAR2
1344 p_multi_value_set_flag VARCHAR2
1345 p_calendar_display_code VARCHAR2
1346 p_hierarchy_usage_code VARCHAR2
1347 p_effective_start_date DATE
1348 p_effective_end_date DATE
1349 p_value_set_display_code VARCHAR2
1350 p_language VARCHAR2
1351 p_dimension_varchar_label VARCHAR2
1352 p_hierarchy_object_name VARCHAR2
1353 p_hierarchy_obj_def_disp_name VARCHAR2
1354 p_parent_display_code VARCHAR2
1355 p_child_display_code VARCHAR2
1356 p_create_level VARCHAR2
1357 OUT : None
1358
1359 Description : This program writes hierarchy details information
1360 to the dimension hierarchy interface tables.
1361 Modification History :
1362 Date Name Desc
1363 ---------- --------- -------------------------------------------------------
1364 10/04/2005 SHTRIPAT Created.
1365 ---------- --------- -------------------------------------------------------
1366 +===========================================================================*/
1367
1368 PROCEDURE Upload_Hierarchy_Interface
1369 ( p_folder_name IN VARCHAR2
1370 , p_dimension_varchar_label IN VARCHAR2
1371 , p_hierarchy_object_name IN VARCHAR2
1372 , p_hierarchy_obj_def_disp_name IN VARCHAR2
1373 , p_ledger_id IN NUMBER
1374 , p_calendar_display_code IN VARCHAR2
1375 , p_group_seq_enforced_code IN VARCHAR2 -- p_use_level_flag
1376 , p_effective_start_date IN DATE
1377 , p_effective_end_date IN DATE
1378 , p_hierarchy_usage_code IN VARCHAR2
1379 , p_hierarchy_type_code IN VARCHAR2
1380 , p_multi_top_flag IN VARCHAR2
1381 , p_multi_value_set_flag IN VARCHAR2
1382 , p_parent_display_code IN VARCHAR2
1383 , p_child_display_code IN VARCHAR2
1384 )
1385 IS
1386 --
1387 l_api_name CONSTANT VARCHAR2(30) := 'Upload_Hierarchy_Interface' ;
1388 --
1389 l_return_status VARCHAR2(1) ;
1390 l_msg_count NUMBER ;
1391 l_msg_data VARCHAR2(2000) ;
1392 --
1393 l_grp_seq_enfrced_code fem_hierarchies_t.group_sequence_enforced_code%TYPE ;
1394 --
1395 --
1396 BEGIN
1397 --
1398 -- Populate global variables with metadata information of
1399 -- the supplied p_dimension_varchar_label.
1400 -- This will be done only if it has not already been done.
1401 -- Other APIs can reuse the populated global variables.
1402 IF ( ( g_global_val_tbl.EXISTS(1)
1403 AND
1404 ( g_global_val_tbl(1).dimension_varchar_label <>
1405 p_dimension_varchar_label OR
1406 g_global_val_tbl(1).ledger_id <> p_ledger_id )
1407 )
1408 OR
1409 g_global_val_tbl.COUNT = 0
1410 )
1411 THEN
1412 --
1413 g_global_val_tbl(1).ledger_id := p_ledger_id ;
1414 g_global_val_tbl(1).calendar_display_code := p_calendar_display_code ;
1415 --
1416 Populate_Dim_Metadata_Info
1417 ( x_return_status => l_return_status
1418 , p_dimension_varchar_label => p_dimension_varchar_label
1419 ) ;
1420
1421 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS )
1422 THEN
1423 RAISE FND_API.G_EXC_ERROR ;
1424 END IF ;
1425 --
1426 END IF ;
1427 --
1428 g_global_val_tbl(1).ledger_id := p_ledger_id ;
1429 g_global_val_tbl(1).calendar_display_code := p_calendar_display_code ;
1430 --
1431 -- Upload_Hierarchy_Header Start
1432
1433 Upload_Hierarchy_Header
1434 ( x_return_status => l_return_status
1435 , x_msg_count => l_msg_count
1436 , x_msg_data => l_msg_data
1437 , p_api_version => 1.0
1438 , p_init_msg_list => FND_API.g_false
1439 , p_commit => FND_API.g_false
1440 , p_intf_hierarchy_table_name =>
1441 g_global_val_tbl(1).hierarchy_intf_table_name
1442 , p_value_set_required_flag =>
1443 g_global_val_tbl(1).value_set_required_flag
1444 , p_dimension_varchar_label => p_dimension_varchar_label
1445 , p_hierarchy_object_name => p_hierarchy_object_name
1446 , p_hierarchy_obj_def_disp_name => p_hierarchy_obj_def_disp_name
1447 , p_folder_name => p_folder_name
1448 , p_hierarchy_type_code => 'OPEN'
1449 , p_multi_top_flag => p_multi_top_flag
1450 , p_multi_value_set_flag => p_multi_value_set_flag
1451 , p_calendar_display_code => p_calendar_display_code
1452 , p_hierarchy_usage_code => 'STANDARD'
1453 , p_group_sequence_enforced_code => p_group_seq_enforced_code
1454 , p_effective_start_date => p_effective_start_date
1455 , p_effective_end_date => p_effective_end_date
1456 , p_language => USERENV('LANG')
1457 ) ;
1458 --
1459 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS )
1460 THEN
1461 RAISE FND_API.G_EXC_ERROR ;
1462 END IF ;
1463 --
1464 -- Upload_Hierarchy_Header Done.
1465 --
1466 -- Upload_Hierarchy_Details Start.
1467 --
1468 Upload_Hierarchy_Details
1469 ( x_return_status => l_return_status
1470 , x_msg_count => l_msg_count
1471 , x_msg_data => l_msg_data
1472 , p_api_version => 1.0
1473 , p_init_msg_list => FND_API.g_false
1474 , p_commit => FND_API.g_false
1475 , p_language => USERENV('LANG')
1476 , p_hierarchy_object_name => p_hierarchy_object_name
1477 , p_hierarchy_obj_def_disp_name => p_hierarchy_obj_def_disp_name
1478 , p_sequence_enforced_code => p_group_seq_enforced_code
1479 , p_calendar_display_code => p_parent_display_code
1480 , p_parent_display_code =>
1481 NVL( p_parent_display_code, p_child_display_code )
1482 , p_child_display_code => p_child_display_code
1483 ) ;
1484
1485 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS )
1486 THEN
1487 RAISE FND_API.G_EXC_ERROR ;
1488 END IF ;
1489 --
1490 -- Upload_Hierarchy_Details Done.
1491 --
1492 -- Commenting out the Exception block to
1493 -- propogate the exact exception back to
1494 -- Web ADI.
1495 -- Needs discussion.
1496 /*EXCEPTION
1497 WHEN others THEN
1498 --
1499 -- *********************
1500 -- ***** IMPORTANT *****
1501 -- *********************
1502 -- For the time being, using Raise_Exception
1503 -- to raise the exception to Excel. Need to
1504 -- decide the text of error message.
1505 APP_EXCEPTION.Raise_Exception
1506 ( -20102
1507 , 'Last successful activity was:: ' || g_prev_activity || ',' ||
1508 'Last activity was:: ' || g_curr_activity
1509 ) ;
1510 --APP_EXCEPTION.Raise_Exception ;
1511 --*/
1512 END Upload_Hierarchy_Interface ;
1513 --
1514
1515
1516 END FEM_WEBADI_HIER_UTILS_PVT ;