[Home] [Help]
PACKAGE BODY: APPS.FEM_DIS_HIER_PKG
Source
1 PACKAGE BODY FEM_DIS_HIER_PKG AS
2 /* $Header: fem_dis_hier.plb 120.1 2007/07/18 22:13:14 srawat ship $ */
3 --
4 -- Package variables
5 --
6 pv_req_id NUMBER;
7 pv_user_id NUMBER;
8 pv_login_id NUMBER;
9
10 --
11 -- Constants
12 --
13 pc_delimiter CONSTANT VARCHAR2(10) := '/';
14 pc_delimiter_length CONSTANT NUMBER := LENGTH(pc_delimiter);
15
16 pc_dummy_member_id CONSTANT NUMBER := -1;
17
18 -- Orphan nodes also use the same display order num
19 pc_dummy_display_order_num CONSTANT NUMBER := 10000;
20
21 pc_log_level_statement CONSTANT NUMBER := FND_LOG.level_statement;
22 pc_log_level_procedure CONSTANT NUMBER := FND_LOG.level_procedure;
23 pc_log_level_event CONSTANT NUMBER := FND_LOG.level_event;
24 pc_log_level_exception CONSTANT NUMBER := FND_LOG.level_exception;
25 pc_log_level_error CONSTANT NUMBER := FND_LOG.level_error;
26 pc_log_level_unexpected CONSTANT NUMBER := FND_LOG.level_unexpected;
27
28
29 -- ======================================================================
30 -- Procedure
31 -- Print_DSQL_Insert_B
32 -- Purpose
33 -- Print Dynamic SQL for Insert B table
34 -- History
35 -- 06-22-05 Shintaro Okuda Created
36 -- Arguments
37 -- p_module Module Name
38 -- p_dis_hierarchy_table_name_b Table Name
39 -- p_insert_b1 Insert Statement Fragment 1
40 -- p_insert_b2 Insert Statement Fragment 2
41 -- p_insert_b3 Insert Statement Fragment 3
42 -- p_insert_b4 Insert Statement Fragment 4
43 -- p_insert_b5 Insert Statement Fragment 5
44 -- p_insert_b6 Insert Statement Fragment 6
45 -- p_insert_b7 Insert Statement Fragment 7
46 -- p_insert_b8 Insert Statement Fragment 8
47 -- p_insert_b9 Insert Statement Fragment 9
48 -- p_insert_b10 Insert Statement Fragment 10
49 -- p_insert_b11 Insert Statement Fragment 11
50 -- p_insert_b12 Insert Statement Fragment 12
51 -- p_insert_b13 Insert Statement Fragment 13
52 -- p_insert_b14 Insert Statement Fragment 14
53 -- p_using1 Using Statement Fragment 1
54 -- p_using2 Using Statement Fragment 2
55 -- p_using3 Using Statement Fragment 3
56 -- p_using4 Using Statement Fragment 4
57 -- p_using5 Using Statement Fragment 5
58 -- p_using6 Using Statement Fragment 6
59 -- p_using7 Using Statement Fragment 7
60 -- p_using8 Using Statement Fragment 8
61 -- p_using9 Using Statement Fragment 9
62 -- p_using10 Using Statement Fragment 10
63 -- p_using11 Using Statement Fragment 11
64 -- ======================================================================
65 PROCEDURE Print_DSQL_Insert_B(
66 p_module IN VARCHAR2,
67 p_dis_hierarchy_table_name_b IN VARCHAR2,
68 p_insert_b1 IN VARCHAR2,
69 p_insert_b2 IN VARCHAR2,
70 p_insert_b3 IN VARCHAR2,
71 p_insert_b4 IN VARCHAR2,
72 p_insert_b5 IN VARCHAR2,
73 p_insert_b6 IN VARCHAR2,
74 p_insert_b7 IN VARCHAR2,
75 p_insert_b8 IN VARCHAR2,
76 p_insert_b9 IN VARCHAR2,
77 p_insert_b10 IN VARCHAR2,
78 p_insert_b11 IN VARCHAR2,
79 p_insert_b12 IN VARCHAR2,
80 p_insert_b13 IN VARCHAR2,
81 p_insert_b14 IN VARCHAR2,
82 p_using1 IN VARCHAR2,
83 p_using2 IN VARCHAR2,
84 p_using3 IN VARCHAR2,
85 p_using4 IN VARCHAR2,
86 p_using5 IN VARCHAR2,
87 p_using6 IN VARCHAR2,
88 p_using7 IN VARCHAR2,
89 p_using8 IN VARCHAR2,
90 p_using9 IN VARCHAR2,
91 p_using10 IN VARCHAR2,
92 p_using11 IN VARCHAR2
93 ) IS
94 BEGIN
95
96 --
97 -- Print Dynamic SQL Elements for INSERT INTO _B table to Debug Log
98 --
99 FEM_ENGINES_PKG.Tech_Message(
100 p_severity => pc_log_level_statement,
101 p_module => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_b,
102 p_msg_text => p_insert_b1
103 );
104
105 FEM_ENGINES_PKG.Tech_Message(
106 p_severity => pc_log_level_statement,
107 p_module => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_b,
108 p_msg_text => p_insert_b2
109 );
110
111 FEM_ENGINES_PKG.Tech_Message(
112 p_severity => pc_log_level_statement,
113 p_module => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_b,
114 p_msg_text => p_insert_b3
115 );
116
117 IF p_insert_b4 IS NOT NULL THEN
118 FEM_ENGINES_PKG.Tech_Message(
119 p_severity => pc_log_level_statement,
120 p_module => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_b,
121 p_msg_text => p_insert_b4
122 );
123 END IF;
124
125 FEM_ENGINES_PKG.Tech_Message(
126 p_severity => pc_log_level_statement,
127 p_module => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_b,
128 p_msg_text => p_insert_b5
129 );
130
131 FEM_ENGINES_PKG.Tech_Message(
132 p_severity => pc_log_level_statement,
133 p_module => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_b,
134 p_msg_text => p_insert_b6
135 );
136
137 FEM_ENGINES_PKG.Tech_Message(
138 p_severity => pc_log_level_statement,
139 p_module => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_b,
140 p_msg_text => p_insert_b7
141 );
142
143 FEM_ENGINES_PKG.Tech_Message(
144 p_severity => pc_log_level_statement,
145 p_module => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_b,
146 p_msg_text => p_insert_b8
147 );
148
149 FEM_ENGINES_PKG.Tech_Message(
150 p_severity => pc_log_level_statement,
151 p_module => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_b,
152 p_msg_text => p_insert_b9
153 );
154
155 FEM_ENGINES_PKG.Tech_Message(
156 p_severity => pc_log_level_statement,
157 p_module => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_b,
158 p_msg_text => p_insert_b10
159 );
160
161 IF p_insert_b11 IS NOT NULL THEN
162 FEM_ENGINES_PKG.Tech_Message(
163 p_severity => pc_log_level_statement,
164 p_module => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_b,
165 p_msg_text => p_insert_b11
166 );
167 END IF;
168
169 FEM_ENGINES_PKG.Tech_Message(
170 p_severity => pc_log_level_statement,
171 p_module => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_b,
172 p_msg_text => p_insert_b12
173 );
174
175 FEM_ENGINES_PKG.Tech_Message(
176 p_severity => pc_log_level_statement,
177 p_module => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_b,
178 p_msg_text => p_insert_b13
179 );
180
181 FEM_ENGINES_PKG.Tech_Message(
182 p_severity => pc_log_level_statement,
183 p_module => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_b,
184 p_msg_text => p_insert_b14
185 );
186 FEM_ENGINES_PKG.Tech_Message(
187 p_severity => pc_log_level_statement,
188 p_module => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_b,
189 p_msg_text => 'USING ' ||
190 p_using1 || ', ' ||
191 p_using2 || ', ' ||
192 p_using3 || ', ''' ||
193 p_using4 || ''', ' ||
194 p_using5 || ', ' ||
195 p_using6 || ', ' ||
196 p_using7 || ', ' ||
197 p_using8 || ', ' ||
198 p_using9 || ', ' ||
199 p_using10 || ', ' ||
200 p_using11 || '
201 '
202 );
203
204 END Print_DSQL_Insert_B;
205
206
207 -- ======================================================================
208 -- Procedure
209 -- Print_DSQL_Insert_TL
210 -- Purpose
211 -- Print Dynamic SQL for Insert into TL table
212 -- History
213 -- 06-22-05 Shintaro Okuda Created
214 -- Arguments
215 -- p_module Module Name
216 -- p_dis_hierarchy_table_name_tl Table Name
217 -- p_insert_tl1 Insert Statement Fragment 1
218 -- p_insert_tl2 Insert Statement Fragment 2
219 -- p_insert_tl3 Insert Statement Fragment 3
220 -- p_insert_tl4 Insert Statement Fragment 4
221 -- p_insert_tl5 Insert Statement Fragment 5
222 -- p_using1 Using Statement Fragment 1
223 -- p_using2 Using Statement Fragment 2
224 -- p_using3 Using Statement Fragment 3
225 -- ======================================================================
226 PROCEDURE Print_DSQL_Insert_TL(
227 p_module IN VARCHAR2,
228 p_dis_hierarchy_table_name_tl IN VARCHAR2,
229 p_insert_tl1 IN VARCHAR2,
230 p_insert_tl2 IN VARCHAR2,
231 p_insert_tl3 IN VARCHAR2,
232 p_insert_tl4 IN VARCHAR2,
233 p_insert_tl5 IN VARCHAR2,
234 p_using1 IN VARCHAR2,
235 p_using2 IN VARCHAR2,
236 p_using3 IN VARCHAR2
237 ) IS
238 BEGIN
239
240 --
241 -- Print Dynamic SQL Elements for INSERT INTO _TL table to Debug Log
242 --
243 FEM_ENGINES_PKG.Tech_Message(
244 p_severity => pc_log_level_statement,
245 p_module => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_tl,
246 p_msg_text => p_insert_tl1
247 );
248
249 FEM_ENGINES_PKG.Tech_Message(
250 p_severity => pc_log_level_statement,
251 p_module => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_tl,
252 p_msg_text => p_insert_tl2
253 );
254
255 FEM_ENGINES_PKG.Tech_Message(
256 p_severity => pc_log_level_statement,
257 p_module => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_tl,
258 p_msg_text => p_insert_tl3
259 );
260
261 FEM_ENGINES_PKG.Tech_Message(
262 p_severity => pc_log_level_statement,
263 p_module => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_tl,
264 p_msg_text => p_insert_tl4
265 );
266
267 FEM_ENGINES_PKG.Tech_Message(
268 p_severity => pc_log_level_statement,
269 p_module => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_tl,
270 p_msg_text => p_insert_tl5
271 );
272
273 FEM_ENGINES_PKG.Tech_Message(
274 p_severity => pc_log_level_statement,
275 p_module => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_tl,
276 p_msg_text => 'USING ' ||
277 p_using1 || ', ' ||
278 p_using2 || ', ' ||
279 p_using3 || '
280 '
281 );
282
283 END Print_DSQL_Insert_TL;
284
285
286 -- ======================================================================
287 -- Function
288 -- Transformation
289 -- Purpose
290 -- Transforms an individual dimension's hierarchy
291 -- History
292 -- 06-22-05 Shintaro Okuda Created
293 -- Arguments
294 -- p_dimension_varchar_label Dimension Varchar Label
295 -- ======================================================================
296 FUNCTION Transformation(
297 p_dimension_varchar_label IN VARCHAR2
298 ) RETURN VARCHAR2 IS
299
300 v_dimension_id FEM_DIMENSIONS_VL.DIMENSION_ID%TYPE;
301 v_dimension_name FEM_DIMENSIONS_VL.DIMENSION_NAME%TYPE;
302 v_member_b_table_name FEM_XDIM_DIMENSIONS.MEMBER_B_TABLE_NAME%TYPE;
303 v_member_tl_table_name FEM_XDIM_DIMENSIONS.MEMBER_TL_TABLE_NAME%TYPE;
304 v_member_vl_object_name FEM_XDIM_DIMENSIONS.MEMBER_VL_OBJECT_NAME%TYPE;
305 v_member_col FEM_XDIM_DIMENSIONS.MEMBER_COL%TYPE;
306 v_member_display_code_col FEM_XDIM_DIMENSIONS.MEMBER_DISPLAY_CODE_COL%TYPE;
307 v_member_name_col FEM_XDIM_DIMENSIONS.MEMBER_NAME_COL%TYPE;
308 v_member_description_col FEM_XDIM_DIMENSIONS.MEMBER_DESCRIPTION_COL%TYPE;
309 v_hierarchy_table_name FEM_XDIM_DIMENSIONS.HIERARCHY_TABLE_NAME%TYPE;
310 v_attribute_table_name FEM_XDIM_DIMENSIONS.ATTRIBUTE_TABLE_NAME%TYPE;
311 v_composite_dimension_flag FEM_XDIM_DIMENSIONS.COMPOSITE_DIMENSION_FLAG%TYPE;
312 v_value_set_required_flag FEM_XDIM_DIMENSIONS.VALUE_SET_REQUIRED_FLAG%TYPE;
313 v_dummy_parent_name FEM_LOOKUPS.MEANING%TYPE;
314
315 v_dis_hierarchy_table_name VARCHAR2(30);
316 v_dis_hierarchy_table_name_b VARCHAR2(30);
317 v_dis_hierarchy_table_name_tl VARCHAR2(30);
318
319 v_attribute_from_orphan VARCHAR2(200);
320 v_attribute_where_orphan VARCHAR2(1000);
321
322 v_from_composite_only VARCHAR2(400);
323 v_where_composite_only VARCHAR2(400);
324
325 v_vs_column_parent_union1 VARCHAR2(100);
326 v_vs_column_child_union1 VARCHAR2(100);
327 v_vs_column_parent_union2 VARCHAR2(100);
328 v_vs_column_child_union2 VARCHAR2(100);
329 v_vs_column_parent_union3 VARCHAR2(100);
330 v_vs_column_child_union3 VARCHAR2(100);
331 v_vs_column_orphan VARCHAR2(100);
332 v_vs_column_subquery_q VARCHAR2(100);
333 v_vs_column_subquery_r VARCHAR2(100);
334 v_vs_column_list VARCHAR2(100);
335 v_vs_column_member_b_table VARCHAR2(100);
336
337 v_vs_where_attribute VARCHAR2(200);
338 v_vs_where_exclude_nonleaf VARCHAR2(200);
339 v_vs_where_connect_by VARCHAR2(200);
340 v_vs_where_display_code VARCHAR2(200);
341 v_vs_where_display_order_num VARCHAR2(200);
342 v_vs_where_root VARCHAR2(200);
343 v_vs_where_root_o VARCHAR2(200);
344 v_vs_where_hier VARCHAR2(400);
345 v_vs_where_member VARCHAR2(200);
346 v_vs_where_tl VARCHAR2(200);
347
348 v_insert_b_column_list VARCHAR2(4000);
349 v_insert_b_display_code1 VARCHAR2(4000);
350 v_insert_b_display_code2 VARCHAR2(4000);
351 v_insert_b_with_o VARCHAR2(4000);
352 v_insert_b_with_p1 VARCHAR2(4000);
353 v_insert_b_with_p2 VARCHAR2(4000);
354 v_insert_b_with_p3 VARCHAR2(4000);
355 v_insert_b_subquery_r1 VARCHAR2(4000);
356 v_insert_b_subquery_r2 VARCHAR2(4000);
357 v_insert_b_subquery_h11 VARCHAR2(4000);
358 v_insert_b_subquery_h11_o VARCHAR2(4000);
359 v_insert_b_subquery_h12 VARCHAR2(4000);
360 v_insert_b_exclude_nonleaf VARCHAR2(4000);
361 v_insert_b_exclude_nonleaf_o VARCHAR2(4000);
362 v_insert_b_subquery_q1 VARCHAR2(4000);
363 v_insert_b_subquery_q2 VARCHAR2(4000);
364
365 v_insert_b_column_list_l NUMBER;
366 v_insert_b_display_code1_l NUMBER;
367 v_insert_b_display_code2_l NUMBER;
368 v_insert_b_with_o_l NUMBER;
369 v_insert_b_with_p1_l NUMBER;
370 v_insert_b_with_p2_l NUMBER;
371 v_insert_b_with_p3_l NUMBER;
372 v_insert_b_subquery_r1_l NUMBER;
373 v_insert_b_subquery_r2_l NUMBER;
374 v_insert_b_subquery_h11_l NUMBER;
375 v_insert_b_subquery_h11_o_l NUMBER;
376 v_insert_b_subquery_h12_l NUMBER;
377 v_insert_b_exclude_nonleaf_l NUMBER;
378 v_insert_b_exclude_nonleaf_o_l NUMBER;
379 v_insert_b_subquery_q1_l NUMBER;
380 v_insert_b_subquery_q2_l NUMBER;
381
382 v_insert_tl_column_list VARCHAR2(4000);
383 v_insert_tl_name1 VARCHAR2(4000);
384 v_insert_tl_name2 VARCHAR2(4000);
385 v_insert_tl_description1 VARCHAR2(4000);
386 v_insert_tl_description2 VARCHAR2(4000);
387
388 v_insert_tl_column_list_l NUMBER;
389 v_insert_tl_name1_l NUMBER;
390 v_insert_tl_name2_l NUMBER;
391 v_insert_tl_description1_l NUMBER;
392 v_insert_tl_description2_l NUMBER;
393
394 v_deleted_b NUMBER;
395 v_deleted_tl NUMBER;
396 v_inserted_b1 NUMBER;
397 v_inserted_b2 NUMBER;
398 v_inserted_tl NUMBER;
399
400 TYPE Varchar2Tab IS TABLE OF VARCHAR2(150);
401 TYPE NumberTab IS TABLE OF NUMBER;
402
403 v_object_name_array Varchar2Tab;
404 v_object_id_array NumberTab;
405 v_multi_vs_num NUMBER;
406
407 v_module VARCHAR2(100) := 'fem.plsql.fem_dis_hier_pkg.transformation';
408 v_func_name VARCHAR2(100) := 'FEM_DIS_HIER_PKG.Transformation';
409
410 BEGIN
411
412 v_module := v_module || '.' || LOWER(p_dimension_varchar_label);
413 v_func_name := v_func_name || '.' || p_dimension_varchar_label;
414
415 FEM_ENGINES_PKG.Tech_Message(
416 p_severity => pc_log_level_procedure,
417 p_module => v_module || '.begin',
418 p_app_name => 'FEM',
419 p_msg_name => 'FEM_GL_POST_201',
420 p_token1 => 'FUNC_NAME',
421 p_value1 => v_func_name,
422 p_token2 => 'TIME',
423 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
424 );
425
426 FEM_ENGINES_PKG.User_Message(
427 p_app_name => 'FEM',
428 p_msg_name => 'FEM_GL_POST_201',
429 p_token1 => 'FUNC_NAME',
430 p_value1 => v_func_name,
431 p_token2 => 'TIME',
432 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
433 );
434
435 /************************************************************************
436 Get dimension properties
437 *************************************************************************/
438 SELECT
439 V.DIMENSION_ID,
440 V.DIMENSION_NAME,
441 X.MEMBER_B_TABLE_NAME,
442 X.MEMBER_TL_TABLE_NAME,
443 X.MEMBER_VL_OBJECT_NAME,
444 X.MEMBER_COL,
445 X.MEMBER_DISPLAY_CODE_COL,
446 X.MEMBER_NAME_COL,
447 X.MEMBER_DESCRIPTION_COL,
448 X.HIERARCHY_TABLE_NAME,
449 X.ATTRIBUTE_TABLE_NAME,
450 X.COMPOSITE_DIMENSION_FLAG,
451 X.VALUE_SET_REQUIRED_FLAG
452 INTO
453 v_dimension_id,
454 v_dimension_name,
455 v_member_b_table_name,
456 v_member_tl_table_name,
457 v_member_vl_object_name,
458 v_member_col,
462 v_hierarchy_table_name,
459 v_member_display_code_col,
460 v_member_name_col,
461 v_member_description_col,
463 v_attribute_table_name,
464 v_composite_dimension_flag,
465 v_value_set_required_flag
466 FROM
467 FEM_DIMENSIONS_VL V,
468 FEM_XDIM_DIMENSIONS X
469 WHERE
470 X.DIMENSION_ID = V.DIMENSION_ID AND
471 V.DIMENSION_VARCHAR_LABEL = p_dimension_varchar_label;
472
473 /************************************************************************
474 Get translated name for dummay parent node
475 *************************************************************************/
476 BEGIN
477 SELECT MEANING
478 INTO v_dummy_parent_name
479 FROM FEM_LOOKUPS
480 WHERE LOOKUP_TYPE = 'FEM_DIS_DUMMY_PARENT'
481 AND LOOKUP_CODE = 'DUMMY_PARENT_NAME';
482 EXCEPTION
483 WHEN NO_DATA_FOUND THEN
484 v_dummy_parent_name := 'DUMMY';
485 WHEN OTHERS THEN
486 RAISE;
487 END;
488
489 /************************************************************************
490 Constructlements of the SQL statement based on dimension's
491 properties in FEM_XDIM_DIMENSIONS (value_set_required_flag,
492 attribute_table_name, hierarchy_table_name, composite_dimension_flag,
493 and etc)
494 *************************************************************************/
495 --
496 -- Based on hierarchy_table_name
497 --
498 --
499 v_dis_hierarchy_table_name := replace(
500 v_hierarchy_table_name,
501 'FEM_',
502 'FEM_DIS_'
503 );
504
505 IF p_dimension_varchar_label = 'CAL_PERIOD' THEN
506 v_dis_hierarchy_table_name := 'FEM_DIS_CAL_PER_HIER';
507 ELSIF p_dimension_varchar_label = 'COST_CENTER' THEN
508 v_dis_hierarchy_table_name := 'FEM_DIS_CST_CNTRS_HIER';
509 ELSIF p_dimension_varchar_label = 'PRODUCT_TYPE' THEN
510 v_dis_hierarchy_table_name := 'FEM_DIS_PRD_TYPES_HIER';
511 ELSIF SUBSTR(p_dimension_varchar_label, 1, 8) = 'USER_DIM' THEN
512 v_dis_hierarchy_table_name := 'FEM_DIS_USR_DIM' ||
513 SUBSTR(p_dimension_varchar_label, 9) ||
514 '_HIER';
515 END IF;
516
517 IF v_member_tl_table_name IS NOT NULL THEN
518 v_dis_hierarchy_table_name_b := v_dis_hierarchy_table_name || '_B';
519 v_dis_hierarchy_table_name_tl := v_dis_hierarchy_table_name || '_TL';
520 ELSE
521 v_dis_hierarchy_table_name_b := v_dis_hierarchy_table_name || '_B';
522 v_dis_hierarchy_table_name_tl := NULL;
523 END IF;
524
525 --
526 -- Based on value_set_required_flag
527 --
528 IF v_value_set_required_flag = 'Y' THEN
529
530 v_vs_column_parent_union1 := '
531 H.PARENT_VALUE_SET_ID,';
532
533 v_vs_column_child_union1 := '
534 H.CHILD_VALUE_SET_ID,';
535
536 v_vs_column_parent_union2 := '
537 O.CHILD_VALUE_SET_ID PARENT_VALUE_SET_ID,';
538
539 v_vs_column_child_union2 := '
540 O.CHILD_VALUE_SET_ID,';
541
542 v_vs_column_parent_union3 := '
543 O.CHILD_VALUE_SET_ID PARENT_VALUE_SET_ID,';
544
545 v_vs_column_child_union3 := '
546 O.VALUE_SET_ID CHILD_VALUE_SET_ID,';
547
548 v_vs_column_orphan := '
549 H.CHILD_VALUE_SET_ID,
550 M.VALUE_SET_ID VALUE_SET_ID,';
551
552 v_where_composite_only := NULL;
553
554 v_from_composite_only := NULL;
555
556 v_vs_column_subquery_r := '
557 H1.CHILD_VALUE_SET_ID VALUE_SET_ID,';
558
559 v_vs_column_list := '
560 VALUE_SET_ID,';
561
562 v_vs_column_member_b_table := '
563 B.VALUE_SET_ID,';
564
565 v_vs_column_subquery_q := '
566 P.VALUE_SET_ID,';
567
568 v_vs_where_attribute := '
569 A.VALUE_SET_ID = M.VALUE_SET_ID AND';
570
571 v_vs_where_exclude_nonleaf := '
572 H1.CHILD_VALUE_SET_ID = H3.PARENT_VALUE_SET_ID AND';
573
574 v_vs_where_connect_by := '
575 H1.PARENT_VALUE_SET_ID = PRIOR H1.CHILD_VALUE_SET_ID AND';
576
577 v_vs_where_display_code := '
578 AND B.VALUE_SET_ID = Q.VALUE_SET_ID';
579
580 v_vs_where_display_order_num := '
581 AND H4.PARENT_VALUE_SET_ID = R.VALUE_SET_ID
582 AND H4.CHILD_VALUE_SET_ID = R.VALUE_SET_ID';
583
584 v_vs_where_root := 'H.PARENT_VALUE_SET_ID = H.CHILD_VALUE_SET_ID AND ';
585
586 v_vs_where_root_o := 'H1.PARENT_VALUE_SET_ID = H1.CHILD_VALUE_SET_ID AND ';
587
588 --
589 -- This is used for WITH query O to filter out hierarchy versions
590 -- which do not have a value set used by a orphan node.
591 --
592 v_vs_where_hier := '
593 EXISTS (
594 SELECT 1
595 FROM FEM_HIER_VALUE_SETS VS
596 WHERE
597 VS.HIERARCHY_OBJ_ID = HIER.HIERARCHY_OBJ_ID AND
598 VS.VALUE_SET_ID = M.VALUE_SET_ID
599 ) AND';
600
601 v_vs_where_member := '
602 H2.CHILD_VALUE_SET_ID = M.VALUE_SET_ID AND';
603
604 v_vs_where_tl := '
605 AND TL.VALUE_SET_ID = B.VALUE_SET_ID';
606
607
608 ELSE -- IF v_value_set_required_flag = 'Y' THEN
609
610 v_vs_column_orphan := NULL;
611 v_vs_where_attribute := NULL;
612 v_vs_where_exclude_nonleaf := NULL;
613 v_vs_where_connect_by := NULL;
614 v_vs_where_member := NULL;
615
616 --
617 -- Based on composite_dimension_flag (to be supported in later release)
618 --
619 IF v_composite_dimension_flag = 'Y' THEN
620
621 v_vs_column_parent_union1 := '
622 M.LOCAL_VS_COMBO_ID,';
623
624 v_vs_column_child_union1 := '
625 M.LOCAL_VS_COMBO_ID,';
626
627 v_vs_column_parent_union2 := '
628 M.LOCAL_VS_COMBO_ID,';
629
630 v_vs_column_child_union2 := '
631 M.LOCAL_VS_COMBO_ID,';
632
633 v_vs_column_parent_union3 := '
634 M.LOCAL_VS_COMBO_ID,';
635
636 v_vs_column_child_union3 := '
637 M.LOCAL_VS_COMBO_ID,';
638
639 v_where_composite_only := 'AND
640 M.' || v_member_col || ' = H.CHILD_ID';
641
642 v_from_composite_only := ',
643 ' || v_member_vl_object_name || ' M';
644
645 v_vs_column_subquery_r := '
646 H1.LOCAL_VS_COMBO_ID,';
647
648 v_vs_column_list := '
649 LOCAL_VS_COMBO_ID,';
650
651 v_vs_column_member_b_table := '
652 B.LOCAL_VS_COMBO_ID,';
653
654 v_vs_column_subquery_q := '
655 P.LOCAL_VS_COMBO_ID,';
656
657 v_vs_where_display_code := '
658 AND B.LOCAL_VS_COMBO_ID = Q.LOCAL_VS_COMBO_ID';
659
660 v_vs_where_display_order_num := NULL;
661
662 ELSE -- IF v_composite_dimension_flag = 'Y' THEN
663
664 v_vs_column_parent_union1 := NULL;
665 v_vs_column_child_union1 := NULL;
666 v_vs_column_parent_union2 := NULL;
667 v_vs_column_child_union2 := NULL;
668 v_vs_column_parent_union3 := NULL;
669 v_vs_column_child_union3 := NULL;
670 v_where_composite_only := NULL;
671 v_from_composite_only := NULL;
672 v_vs_column_subquery_r := NULL;
673 v_vs_column_list := NULL;
674 v_vs_column_member_b_table := NULL;
675 v_vs_column_subquery_q := NULL;
676 v_vs_where_display_code := NULL;
677 v_vs_where_display_order_num := NULL;
678
679 END IF; -- IF v_composite_dimension_flag = 'Y' THEN
680
681 v_vs_where_root := NULL;
682 v_vs_where_root_o := NULL;
683 v_vs_where_hier := NULL;
684 v_vs_where_tl := NULL;
685
686 END IF; -- IF v_value_set_required_flag = 'Y' THEN
687
688 --
689 -- Based on attribute_table_name
690 --
691 IF v_attribute_table_name IS NOT NULL THEN
692
693 v_attribute_from_orphan := '
694 FEM_DIM_ATTRIBUTES_B ATTR,
695 FEM_DIM_ATTR_VERSIONS_B ATTRV,
696 ' || v_attribute_table_name || ' A,';
697
698 v_attribute_where_orphan := ' AND
699 ATTR.DIMENSION_ID = :dimension_id AND
700 ATTR.ATTRIBUTE_VARCHAR_LABEL = ''RECON_LEAF_NODE_FLAG'' AND
701 ATTRV.ATTRIBUTE_ID = ATTR.ATTRIBUTE_ID AND
702 ATTRV.DEFAULT_VERSION_FLAG = ''Y'' AND
703 A.ATTRIBUTE_ID = ATTR.ATTRIBUTE_ID AND
704 A.VERSION_ID = ATTRV.VERSION_ID AND
705 A.' || v_member_col || ' = M.' || v_member_col || ' AND ' ||
706 v_vs_where_attribute || '
707 A.DIM_ATTRIBUTE_VARCHAR_MEMBER = ''Y''';
708
709 ELSE -- IF v_attribute_table_name IS NOT NULL THEN
710
711 v_attribute_from_orphan := NULL;
712 v_attribute_where_orphan := NULL;
713
714 END IF; -- IF v_attribute_table_name IS NOT NULL THEN
715
716
720 --
717 /************************************************************************
718 Construct Dynamic SQL Elements for INSERT INTO _B table
719 *************************************************************************/
721 -- Construct Dynamic SQL Elements for INSERT INTO _B table (column list)
722 --
723 v_insert_b_column_list := '
724 INSERT INTO ' || v_dis_hierarchy_table_name_b || ' (
725 OBJECT_ID,
726 OBJECT_DEFINITION_ID,' ||
727 v_vs_column_list || '
728 LEVEL1_ID,
729 LEVEL2_ID,
730 LEVEL3_ID,
731 LEVEL4_ID,
732 LEVEL5_ID,
733 LEVEL6_ID,
734 LEVEL7_ID,
735 LEVEL8_ID,
736 LEVEL9_ID,
737 LEVEL10_ID,
738 LEVEL11_ID,
739 LEVEL12_ID,
740 LEVEL13_ID,
741 LEVEL14_ID,
742 LEVEL15_ID,
743 LEVEL16_ID,
744 LEVEL17_ID,
745 LEVEL18_ID,
746 LEVEL19_ID,
747 LEVEL20_ID,
748 LEVEL1_DISPLAY_ORDER_NUM,
749 LEVEL2_DISPLAY_ORDER_NUM,
750 LEVEL3_DISPLAY_ORDER_NUM,
751 LEVEL4_DISPLAY_ORDER_NUM,
752 LEVEL5_DISPLAY_ORDER_NUM,
753 LEVEL6_DISPLAY_ORDER_NUM,
754 LEVEL7_DISPLAY_ORDER_NUM,
755 LEVEL8_DISPLAY_ORDER_NUM,
756 LEVEL9_DISPLAY_ORDER_NUM,
757 LEVEL10_DISPLAY_ORDER_NUM,
758 LEVEL11_DISPLAY_ORDER_NUM,
759 LEVEL12_DISPLAY_ORDER_NUM,
760 LEVEL13_DISPLAY_ORDER_NUM,
761 LEVEL14_DISPLAY_ORDER_NUM,
762 LEVEL15_DISPLAY_ORDER_NUM,
763 LEVEL16_DISPLAY_ORDER_NUM,
764 LEVEL17_DISPLAY_ORDER_NUM,
765 LEVEL18_DISPLAY_ORDER_NUM,
766 LEVEL19_DISPLAY_ORDER_NUM,
767 LEVEL20_DISPLAY_ORDER_NUM,
768 LEVEL1_DISPLAY_CODE,
769 LEVEL2_DISPLAY_CODE,
770 LEVEL3_DISPLAY_CODE,
771 LEVEL4_DISPLAY_CODE,
772 LEVEL5_DISPLAY_CODE,
773 LEVEL6_DISPLAY_CODE,
774 LEVEL7_DISPLAY_CODE,
775 LEVEL8_DISPLAY_CODE,
776 LEVEL9_DISPLAY_CODE,
777 LEVEL10_DISPLAY_CODE,
778 LEVEL11_DISPLAY_CODE,
779 LEVEL12_DISPLAY_CODE,
780 LEVEL13_DISPLAY_CODE,
781 LEVEL14_DISPLAY_CODE,
782 LEVEL15_DISPLAY_CODE,
783 LEVEL16_DISPLAY_CODE,
784 LEVEL17_DISPLAY_CODE,
785 LEVEL18_DISPLAY_CODE,
786 LEVEL19_DISPLAY_CODE,
787 LEVEL20_DISPLAY_CODE,
788 CREATION_DATE,
789 CREATED_BY,
790 LAST_UPDATE_DATE,
791 LAST_UPDATED_BY,
792 LAST_UPDATE_LOGIN
793 )
794 SELECT
795 Q.*,';
796
797 FEM_ENGINES_PKG.Tech_Message(
798 p_severity => pc_log_level_statement,
799 p_module => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
800 p_msg_text => 'v_insert_b_column_list_l='||LENGTH(v_insert_b_column_list)
801 );
802
803 --
804 -- Construct Dynamic SQL Elements for INSERT INTO _B table (display_code 1)
805 --
806 v_insert_b_display_code1 := '
807 (
808 SELECT B.' || v_member_display_code_col || '
809 FROM ' || v_member_b_table_name || ' B
810 WHERE B.' || v_member_col || ' = Q.LEVEL1_ID ' ||
811 v_vs_where_display_code || '
812 ),
813 DECODE(Q.LEVEL2_ID, ' || pc_dummy_member_id || ', ''' || v_dummy_parent_name || ''',
814 (
815 SELECT B.' || v_member_display_code_col || '
816 FROM ' || v_member_b_table_name || ' B
817 WHERE B.' || v_member_col || ' = Q.LEVEL2_ID ' ||
818 v_vs_where_display_code || '
819 )
820 ),
821 (
822 SELECT B.' || v_member_display_code_col || '
823 FROM ' || v_member_b_table_name || ' B
824 WHERE B.' || v_member_col || ' = Q.LEVEL3_ID ' ||
825 v_vs_where_display_code || '
826 ),
827 (
828 SELECT B.' || v_member_display_code_col || '
829 FROM ' || v_member_b_table_name || ' B
830 WHERE B.' || v_member_col || ' = Q.LEVEL4_ID ' ||
831 v_vs_where_display_code || '
832 ),
833 (
834 SELECT B.' || v_member_display_code_col || '
835 FROM ' || v_member_b_table_name || ' B
836 WHERE B.' || v_member_col || ' = Q.LEVEL5_ID ' ||
837 v_vs_where_display_code || '
838 ),
839 (
840 SELECT B.' || v_member_display_code_col || '
841 FROM ' || v_member_b_table_name || ' B
842 WHERE B.' || v_member_col || ' = Q.LEVEL6_ID ' ||
843 v_vs_where_display_code || '
844 ),
845 (
846 SELECT B.' || v_member_display_code_col || '
847 FROM ' || v_member_b_table_name || ' B
848 WHERE B.' || v_member_col || ' = Q.LEVEL7_ID ' ||
849 v_vs_where_display_code || '
850 ),
851 (
852 SELECT B.' || v_member_display_code_col || '
853 FROM ' || v_member_b_table_name || ' B
854 WHERE B.' || v_member_col || ' = Q.LEVEL8_ID ' ||
855 v_vs_where_display_code || '
856 ),
857 (
858 SELECT B.' || v_member_display_code_col || '
859 FROM ' || v_member_b_table_name || ' B
860 WHERE B.' || v_member_col || ' = Q.LEVEL9_ID ' ||
861 v_vs_where_display_code || '
862 ),
863 (
864 SELECT B.' || v_member_display_code_col || '
865 FROM ' || v_member_b_table_name || ' B
866 WHERE B.' || v_member_col || ' = Q.LEVEL10_ID ' ||
867 v_vs_where_display_code || '
868 ),';
869
870 FEM_ENGINES_PKG.Tech_Message(
871 p_severity => pc_log_level_statement,
875
872 p_module => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
873 p_msg_text => 'v_insert_b_display_code1_l='||LENGTH(v_insert_b_display_code1)
874 );
876 --
877 -- Construct Dynamic SQL Elements for INSERT INTO _B table (display_code 2)
878 --
879 v_insert_b_display_code2 := '
880 (
881 SELECT B.' || v_member_display_code_col || '
882 FROM ' || v_member_b_table_name || ' B
883 WHERE B.' || v_member_col || ' = Q.LEVEL11_ID ' ||
884 v_vs_where_display_code || '
885 ),
886 (
887 SELECT B.' || v_member_display_code_col || '
888 FROM ' || v_member_b_table_name || ' B
889 WHERE B.' || v_member_col || ' = Q.LEVEL12_ID ' ||
890 v_vs_where_display_code || '
891 ),
892 (
893 SELECT B.' || v_member_display_code_col || '
894 FROM ' || v_member_b_table_name || ' B
895 WHERE B.' || v_member_col || ' = Q.LEVEL13_ID ' ||
896 v_vs_where_display_code || '
897 ),
898 (
899 SELECT B.' || v_member_display_code_col || '
900 FROM ' || v_member_b_table_name || ' B
901 WHERE B.' || v_member_col || ' = Q.LEVEL14_ID ' ||
902 v_vs_where_display_code || '
903 ),
904 (
905 SELECT B.' || v_member_display_code_col || '
906 FROM ' || v_member_b_table_name || ' B
907 WHERE B.' || v_member_col || ' = Q.LEVEL15_ID ' ||
908 v_vs_where_display_code || '
909 ),
910 (
911 SELECT B.' || v_member_display_code_col || '
912 FROM ' || v_member_b_table_name || ' B
913 WHERE B.' || v_member_col || ' = Q.LEVEL16_ID ' ||
914 v_vs_where_display_code || '
915 ),
916 (
917 SELECT B.' || v_member_display_code_col || '
918 FROM ' || v_member_b_table_name || ' B
919 WHERE B.' || v_member_col || ' = Q.LEVEL17_ID ' ||
920 v_vs_where_display_code || '
921 ),
922 (
923 SELECT B.' || v_member_display_code_col || '
924 FROM ' || v_member_b_table_name || ' B
925 WHERE B.' || v_member_col || ' = Q.LEVEL18_ID ' ||
926 v_vs_where_display_code || '
927 ),
928 (
929 SELECT B.' || v_member_display_code_col || '
930 FROM ' || v_member_b_table_name || ' B
931 WHERE B.' || v_member_col || ' = Q.LEVEL19_ID ' ||
932 v_vs_where_display_code || '
933 ),
934 (
935 SELECT B.' || v_member_display_code_col || '
936 FROM ' || v_member_b_table_name || ' B
937 WHERE B.' || v_member_col || ' = Q.LEVEL20_ID ' ||
938 v_vs_where_display_code || '
939 ),
940 SYSDATE CREATION_DATE,
941 :pv_user_id CREATED_BY,
942 SYSDATE LAST_UPDATE_DATE,
943 :pv_user_id LAST_UPDATED_BY,
944 :pv_login_id LAST_UPDATE_LOGIN
945 FROM (
946 WITH'; -- Q
947
948 FEM_ENGINES_PKG.Tech_Message(
949 p_severity => pc_log_level_statement,
950 p_module => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
951 p_msg_text => 'v_insert_b_display_code2_l='||LENGTH(v_insert_b_display_code2)
952 );
953
954 --
955 -- Construct Dynamic SQL Elements for INSERT INTO _B table (with o)
956 --
957 -- This WITH query returns orphan nodes - only used for RECONCILIATION type.
958 --
959 v_insert_b_with_o := '
960 O AS (
961 SELECT
962 H.HIERARCHY_OBJ_DEF_ID,
963 ' || pc_dummy_display_order_num || ' DUMMY_DISPLAY_ORDER_NUM,
964 ' || pc_dummy_member_id || ' DUMMY_MEMBER_ID,' ||
965 v_vs_column_orphan || '
966 H.CHILD_ID,
967 M.' || v_member_col || ' MEMBER_ID
968 FROM
969 FEM_HIERARCHIES HIER,
970 FEM_OBJECT_DEFINITION_B DEF,
971 FEM_OBJECT_CATALOG_B CAT, ' ||
972 v_attribute_from_orphan || '
973 ' || v_member_b_table_name || ' M,
974 ' || v_hierarchy_table_name || ' H ' ||
975 v_from_composite_only || '
976 WHERE
977 HIER.MULTI_VALUE_SET_FLAG = ''N'' AND
978 HIER.HIERARCHY_TYPE_CODE = ''RECONCILIATION'' AND
979 HIER.DIMENSION_ID = :dimension_id AND
980 HIER.PERSONAL_FLAG = ''N'' AND
981 CAT.OBJECT_ID = HIER.HIERARCHY_OBJ_ID AND
982 DEF.OBJECT_ID = CAT.OBJECT_ID AND
983 H.HIERARCHY_OBJ_DEF_ID = DEF.OBJECT_DEFINITION_ID AND ' ||
984 v_vs_where_root || '
985 H.PARENT_ID = H.CHILD_ID AND
986 H.PARENT_DEPTH_NUM = 1 AND ' ||
987 v_vs_where_hier || '
988 NOT EXISTS (
989 SELECT 1
990 FROM
991 FEM_HIERARCHIES HIER2,
992 FEM_OBJECT_DEFINITION_B DEF2,
993 FEM_OBJECT_CATALOG_B CAT2,
994 ' || v_hierarchy_table_name || ' H2
995 WHERE
996 HIER2.MULTI_VALUE_SET_FLAG = ''N'' AND
997 HIER2.HIERARCHY_TYPE_CODE = ''RECONCILIATION'' AND
998 HIER2.DIMENSION_ID = :dimension_id AND
999 HIER2.PERSONAL_FLAG = ''N'' AND
1000 CAT2.OBJECT_ID = HIER2.HIERARCHY_OBJ_ID AND
1001 DEF2.OBJECT_ID = CAT2.OBJECT_ID AND
1002 H2.HIERARCHY_OBJ_DEF_ID = DEF2.OBJECT_DEFINITION_ID AND ' ||
1003 v_vs_where_member || '
1004 H2.CHILD_ID = M.' || v_member_col || '
1005 )' ||
1006 v_attribute_where_orphan ||
1007 v_where_composite_only || '
1011 p_severity => pc_log_level_statement,
1008 ),';
1009
1010 FEM_ENGINES_PKG.Tech_Message(
1012 p_module => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
1013 p_msg_text => 'v_insert_b_with_o_l='||LENGTH(v_insert_b_with_o)
1014 );
1015
1016 --
1017 -- Construct Dynamic SQL Elements for INSERT INTO _B table (with p1)
1018 --
1019 -- In multi-top hierarchy, relying solely on a single LEVEL_ID
1020 -- is not sufficient as there are multiple child-parent
1021 -- relationships. Child ID and parent ID are needed to find a
1022 -- DISPLAY_ORDER_NUM. Furthermore, if both child ID and parent ID
1023 -- are padded, a tree needs to be traversed to find out the last
1024 -- non-padded child-parent relationship.
1025 --
1026 -- To support these, conditions on both child ID and parent ID
1027 -- are added to the sub queries for DISPLAY_ORDER_NUM and these
1028 -- sub queries were moved from outside of the sub query P to
1029 -- inside of the sub query P, where padding is not performed yet.
1030 --
1031 v_insert_b_with_p1 := '
1032 P AS (
1033 SELECT
1034 R.*,
1035 (
1036 SELECT H4.DISPLAY_ORDER_NUM
1037 FROM ' || v_hierarchy_table_name || ' H4
1038 WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
1039 v_vs_where_display_order_num || '
1040 AND H4.PARENT_ID = R.L1
1041 AND H4.CHILD_ID = R.L1
1042 ) N1,
1043 (
1044 SELECT H4.DISPLAY_ORDER_NUM
1045 FROM ' || v_hierarchy_table_name || ' H4
1046 WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
1047 v_vs_where_display_order_num || '
1048 AND H4.PARENT_ID = R.L1
1049 AND H4.CHILD_ID = R.L2
1050 ) N2,
1051 (
1052 SELECT H4.DISPLAY_ORDER_NUM
1053 FROM ' || v_hierarchy_table_name || ' H4
1054 WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
1055 v_vs_where_display_order_num || '
1056 AND H4.PARENT_ID = R.L2
1057 AND H4.CHILD_ID = R.L3
1058 ) N3,
1059 (
1060 SELECT H4.DISPLAY_ORDER_NUM
1061 FROM ' || v_hierarchy_table_name || ' H4
1062 WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
1063 v_vs_where_display_order_num || '
1064 AND H4.PARENT_ID = R.L3
1065 AND H4.CHILD_ID = R.L4
1066 ) N4,
1067 (
1068 SELECT H4.DISPLAY_ORDER_NUM
1069 FROM ' || v_hierarchy_table_name || ' H4
1070 WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
1071 v_vs_where_display_order_num || '
1072 AND H4.PARENT_ID = R.L4
1073 AND H4.CHILD_ID = R.L5
1074 ) N5,
1075 (
1076 SELECT H4.DISPLAY_ORDER_NUM
1077 FROM ' || v_hierarchy_table_name || ' H4
1078 WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
1079 v_vs_where_display_order_num || '
1080 AND H4.PARENT_ID = R.L5
1081 AND H4.CHILD_ID = R.L6
1082 ) N6,';
1083
1084 FEM_ENGINES_PKG.Tech_Message(
1085 p_severity => pc_log_level_statement,
1086 p_module => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
1087 p_msg_text => 'v_insert_b_with_p1_l='||LENGTH(v_insert_b_with_p1)
1088 );
1089
1090
1091 --
1092 -- Construct Dynamic SQL Elements for INSERT INTO _B table (with p2)
1093 --
1094 v_insert_b_with_p2:= '
1095 (
1096 SELECT H4.DISPLAY_ORDER_NUM
1097 FROM ' || v_hierarchy_table_name || ' H4
1098 WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
1099 v_vs_where_display_order_num || '
1100 AND H4.PARENT_ID = R.L6
1101 AND H4.CHILD_ID = R.L7
1102 ) N7,
1103 (
1104 SELECT H4.DISPLAY_ORDER_NUM
1105 FROM ' || v_hierarchy_table_name || ' H4
1106 WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
1107 v_vs_where_display_order_num || '
1108 AND H4.PARENT_ID = R.L7
1109 AND H4.CHILD_ID = R.L8
1110 ) N8,
1111 (
1112 SELECT H4.DISPLAY_ORDER_NUM
1113 FROM ' || v_hierarchy_table_name || ' H4
1114 WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
1115 v_vs_where_display_order_num || '
1116 AND H4.PARENT_ID = R.L8
1117 AND H4.CHILD_ID = R.L9
1118 ) N9,
1119 (
1120 SELECT H4.DISPLAY_ORDER_NUM
1121 FROM ' || v_hierarchy_table_name || ' H4
1122 WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
1123 v_vs_where_display_order_num || '
1124 AND H4.PARENT_ID = R.L9
1125 AND H4.CHILD_ID = R.L10
1126 ) N10,
1127 (
1128 SELECT H4.DISPLAY_ORDER_NUM
1129 FROM ' || v_hierarchy_table_name || ' H4
1130 WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
1131 v_vs_where_display_order_num || '
1132 AND H4.PARENT_ID = R.L10
1133 AND H4.CHILD_ID = R.L11
1134 ) N11,
1135 (
1136 SELECT H4.DISPLAY_ORDER_NUM
1137 FROM ' || v_hierarchy_table_name || ' H4
1138 WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
1142 ) N12,
1139 v_vs_where_display_order_num || '
1140 AND H4.PARENT_ID = R.L11
1141 AND H4.CHILD_ID = R.L12
1143 (
1144 SELECT H4.DISPLAY_ORDER_NUM
1145 FROM ' || v_hierarchy_table_name || ' H4
1146 WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
1147 v_vs_where_display_order_num || '
1148 AND H4.PARENT_ID = R.L12
1149 AND H4.CHILD_ID = R.L13
1150 ) N13,';
1151
1152 FEM_ENGINES_PKG.Tech_Message(
1153 p_severity => pc_log_level_statement,
1154 p_module => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
1155 p_msg_text => 'v_insert_b_with_p2_l='||LENGTH(v_insert_b_with_p2)
1156 );
1157
1158
1159 --
1160 -- Construct Dynamic SQL Elements for INSERT INTO _B table (with p3)
1161 --
1162 v_insert_b_with_p3 := '
1163 (
1164 SELECT H4.DISPLAY_ORDER_NUM
1165 FROM ' || v_hierarchy_table_name || ' H4
1166 WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
1167 v_vs_where_display_order_num || '
1168 AND H4.PARENT_ID = R.L13
1169 AND H4.CHILD_ID = R.L14
1170 ) N14,
1171 (
1172 SELECT H4.DISPLAY_ORDER_NUM
1173 FROM ' || v_hierarchy_table_name || ' H4
1174 WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
1175 v_vs_where_display_order_num || '
1176 AND H4.PARENT_ID = R.L14
1177 AND H4.CHILD_ID = R.L15
1178 ) N15,
1179 (
1180 SELECT H4.DISPLAY_ORDER_NUM
1181 FROM ' || v_hierarchy_table_name || ' H4
1182 WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
1183 v_vs_where_display_order_num || '
1184 AND H4.PARENT_ID = R.L15
1185 AND H4.CHILD_ID = R.L16
1186 ) N16,
1187 (
1188 SELECT H4.DISPLAY_ORDER_NUM
1189 FROM ' || v_hierarchy_table_name || ' H4
1190 WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
1191 v_vs_where_display_order_num || '
1192 AND H4.PARENT_ID = R.L16
1193 AND H4.CHILD_ID = R.L17
1194 ) N17,
1195 (
1196 SELECT H4.DISPLAY_ORDER_NUM
1197 FROM ' || v_hierarchy_table_name || ' H4
1198 WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
1199 v_vs_where_display_order_num || '
1200 AND H4.PARENT_ID = R.L17
1201 AND H4.CHILD_ID = R.L18
1202 ) N18,
1203 (
1204 SELECT H4.DISPLAY_ORDER_NUM
1205 FROM ' || v_hierarchy_table_name || ' H4
1206 WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
1207 v_vs_where_display_order_num || '
1208 AND H4.PARENT_ID = R.L18
1209 AND H4.CHILD_ID = R.L19
1210 ) N19,
1211 (
1212 SELECT H4.DISPLAY_ORDER_NUM
1213 FROM ' || v_hierarchy_table_name || ' H4
1214 WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
1215 v_vs_where_display_order_num || '
1216 AND H4.PARENT_ID = R.L19
1217 AND H4.CHILD_ID = R.L20
1218 ) N20
1219 FROM ('; -- R
1220
1221 FEM_ENGINES_PKG.Tech_Message(
1222 p_severity => pc_log_level_statement,
1223 p_module => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
1224 p_msg_text => 'v_insert_b_with_p3_l='||LENGTH(v_insert_b_with_p3)
1225 );
1226
1227 --
1228 -- Construct Dynamic SQL Elements for INSERT INTO _B table (subquery r1)
1229 --
1230 v_insert_b_subquery_r1 := '
1231 SELECT
1232 H1.HIERARCHY_OBJ_DEF_ID, ' ||
1233 v_vs_column_subquery_r || '
1234 CAST(
1235 SUBSTR(
1236 SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
1237 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 1)+' || pc_delimiter_length || ',
1238 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 2)-
1239 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 1)-' || pc_delimiter_length || '
1240 ) AS NUMBER
1241 ) L1,
1242 CAST(
1243 SUBSTR(
1244 SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
1245 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 2)+' || pc_delimiter_length ||',
1246 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 3)-
1247 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 2)-' || pc_delimiter_length || '
1248 ) AS NUMBER
1249 ) L2,
1250 CAST(
1251 SUBSTR(
1252 SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
1253 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 3)+' || pc_delimiter_length || ',
1257 ) L3,
1254 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 4)-
1255 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 3)-' || pc_delimiter_length || '
1256 ) AS NUMBER
1258 CAST(
1259 SUBSTR(
1260 SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
1261 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 4)+' || pc_delimiter_length || ',
1262 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 5)-
1263 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 4)-' || pc_delimiter_length || '
1264 ) AS NUMBER
1265 ) L4,
1266 CAST(
1267 SUBSTR(
1268 SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
1269 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 5)+' || pc_delimiter_length || ',
1270 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 6)-
1271 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 5)-' || pc_delimiter_length || '
1272 ) AS NUMBER
1273 ) L5,
1274 CAST(
1275 SUBSTR(
1276 SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
1277 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 6)+' || pc_delimiter_length || ',
1278 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 7)-
1279 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 6)-' || pc_delimiter_length || '
1280 ) AS NUMBER
1281 ) L6,
1282 CAST(
1283 SUBSTR(
1284 SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
1285 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 7)+' || pc_delimiter_length || ',
1286 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 8)-
1287 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 7)-' || pc_delimiter_length || '
1288 ) AS NUMBER
1289 ) L7,
1290 CAST(
1291 SUBSTR(
1292 SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
1293 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 8)+' || pc_delimiter_length || ',
1294 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 9)-
1295 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 8)-' || pc_delimiter_length || '
1296 ) AS NUMBER
1297 ) L8,
1298 CAST(
1299 SUBSTR(
1300 SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
1301 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 9)+' || pc_delimiter_length || ',
1302 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 10)-
1303 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 9)-' || pc_delimiter_length || '
1304 ) AS NUMBER
1305 ) L9,
1306 CAST(
1307 SUBSTR(
1308 SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
1309 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 10)+' || pc_delimiter_length || ',
1310 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 11)-
1311 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 10)-' || pc_delimiter_length || '
1312 ) AS NUMBER
1313 ) L10,';
1314
1315 FEM_ENGINES_PKG.Tech_Message(
1316 p_severity => pc_log_level_statement,
1317 p_module => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
1318 p_msg_text => 'v_insert_b_subquery_r1_l='||LENGTH(v_insert_b_subquery_r1)
1319 );
1320
1321 --
1322 -- Construct Dynamic SQL Elements for INSERT INTO _B table (subquery r2)
1323 --
1324 v_insert_b_subquery_r2 := '
1325 CAST(
1326 SUBSTR(
1327 SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
1331 ) AS NUMBER
1328 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 11)+' || pc_delimiter_length || ',
1329 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 12)-
1330 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 11)-' || pc_delimiter_length || '
1332 ) L11,
1333 CAST(
1334 SUBSTR(
1335 SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
1336 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 12)+' || pc_delimiter_length || ',
1337 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 13)-
1338 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 12)-' || pc_delimiter_length || '
1339 ) AS NUMBER
1340 ) L12,
1341 CAST(
1342 SUBSTR(
1343 SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
1344 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 13)+' || pc_delimiter_length || ',
1345 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 14)-
1346 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 13)-' || pc_delimiter_length || '
1347 ) AS NUMBER
1348 ) L13,
1349 CAST(
1350 SUBSTR(
1351 SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
1352 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 14)+' || pc_delimiter_length || ',
1353 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 15)-
1354 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 14)-' || pc_delimiter_length || '
1355 ) AS NUMBER
1356 ) L14,
1357 CAST(
1358 SUBSTR(
1359 SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
1360 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 15)+' || pc_delimiter_length || ',
1361 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 16)-
1362 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 15)-' || pc_delimiter_length || '
1363 ) AS NUMBER
1364 ) L15,
1365 CAST(
1366 SUBSTR(
1367 SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
1368 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 16)+' || pc_delimiter_length || ',
1369 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 17)-
1370 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 16)-' || pc_delimiter_length || '
1371 ) AS NUMBER
1372 ) L16,
1373 CAST(
1374 SUBSTR(
1375 SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
1376 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 17)+' || pc_delimiter_length || ',
1377 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 18)-
1378 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 17)-' || pc_delimiter_length || '
1379 ) AS NUMBER
1380 ) L17,
1381 CAST(
1382 SUBSTR(
1383 SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
1384 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 18)+' || pc_delimiter_length || ',
1385 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 19)-
1386 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 18)-' || pc_delimiter_length || '
1387 ) AS NUMBER
1388 ) L18,
1389 CAST(
1390 SUBSTR(
1391 SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
1392 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 19)+' || pc_delimiter_length || ',
1393 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 20)-
1397 CAST(
1394 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 19)-' || pc_delimiter_length || '
1395 ) AS NUMBER
1396 ) L19,
1398 SUBSTR(
1399 SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
1400 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 20)+' || pc_delimiter_length || ',
1401 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 21)-
1402 INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 20)-' || pc_delimiter_length || '
1403 ) AS NUMBER
1404 ) L20';
1405
1406 FEM_ENGINES_PKG.Tech_Message(
1407 p_severity => pc_log_level_statement,
1408 p_module => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
1409 p_msg_text => 'v_insert_b_subquery_r2_l='||LENGTH(v_insert_b_subquery_r2)
1410 );
1411
1412
1413 --
1414 -- Construct Dynamic SQL Elements for INSERT INTO _B table (subquery h11)
1415 --
1416 -- (for RECONCILIATION hierarchy type)
1417 v_insert_b_subquery_h11 := '
1418 FROM ( -- H1
1419 SELECT
1420 H.HIERARCHY_OBJ_DEF_ID,
1421 H.DISPLAY_ORDER_NUM, ' ||
1422 v_vs_column_parent_union1 || '
1423 CASE
1424 WHEN H.PARENT_ID = H.CHILD_ID AND H.PARENT_DEPTH_NUM = 1 THEN NULL
1425 ELSE H.PARENT_ID
1426 END PARENT_ID, ' ||
1427 v_vs_column_child_union1 || '
1428 H.CHILD_ID
1429 FROM
1430 FEM_HIERARCHIES HIER,
1431 FEM_OBJECT_DEFINITION_B DEF,
1432 FEM_OBJECT_CATALOG_B CAT,
1433 ' || v_hierarchy_table_name || ' H ' ||
1434 v_from_composite_only || '
1435 WHERE
1436 HIER.MULTI_VALUE_SET_FLAG = ''N'' AND
1437 HIER.HIERARCHY_TYPE_CODE = :hier_type_code AND
1438 HIER.DIMENSION_ID = :dimension_id AND
1439 HIER.PERSONAL_FLAG = ''N'' AND
1440 CAT.OBJECT_ID = HIER.HIERARCHY_OBJ_ID AND
1441 DEF.OBJECT_ID = CAT.OBJECT_ID AND
1442 H.HIERARCHY_OBJ_DEF_ID = DEF.OBJECT_DEFINITION_ID AND
1443 ((' || v_vs_where_root || 'H.PARENT_ID = H.CHILD_ID AND H.PARENT_DEPTH_NUM = 1) OR
1444 H.CHILD_DEPTH_NUM - H.PARENT_DEPTH_NUM = 1) ' ||
1445 v_where_composite_only;
1446
1447 FEM_ENGINES_PKG.Tech_Message(
1448 p_severity => pc_log_level_statement,
1449 p_module => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
1450 p_msg_text => 'v_insert_b_subquery_h11_l='||LENGTH(v_insert_b_subquery_h11)
1451 );
1452
1453
1454 --
1455 -- Construct Dynamic SQL Elements for INSERT INTO _B table (subquery h11_open)
1456 --
1457 -- (for OPEN hierarchy type)
1458 v_insert_b_subquery_h11_o := '
1459 FROM
1460 FEM_HIERARCHIES HIER,
1461 FEM_OBJECT_DEFINITION_B DEF,
1462 FEM_OBJECT_CATALOG_B CAT,
1463 ' || v_hierarchy_table_name || ' H1 ' ||
1464 v_from_composite_only || '
1465 WHERE
1466 HIER.MULTI_VALUE_SET_FLAG = ''N'' AND
1467 HIER.HIERARCHY_TYPE_CODE = :hier_type_code AND
1468 HIER.DIMENSION_ID = :dimension_id AND
1469 HIER.PERSONAL_FLAG = ''N'' AND
1470 CAT.OBJECT_ID = HIER.HIERARCHY_OBJ_ID AND
1471 DEF.OBJECT_ID = CAT.OBJECT_ID AND
1472 H1.HIERARCHY_OBJ_DEF_ID = DEF.OBJECT_DEFINITION_ID AND
1473 ((' || v_vs_where_root_o || 'H1.PARENT_ID = H1.CHILD_ID AND H1.PARENT_DEPTH_NUM = 1) OR
1474 H1.CHILD_DEPTH_NUM - H1.PARENT_DEPTH_NUM = 1) ' ||
1475 v_where_composite_only;
1476
1477 FEM_ENGINES_PKG.Tech_Message(
1478 p_severity => pc_log_level_statement,
1479 p_module => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
1480 p_msg_text => 'v_insert_b_subquery_h11_o_l='||LENGTH(v_insert_b_subquery_h11_o)
1481 );
1482
1483
1484 --
1485 -- Construct Dynamic SQL Elements for INSERT INTO _B table (subquery h12)
1486 --
1487 -- (for RECONCILIATION hierarchy type which contains orphan node)
1488 --
1489 -- For a root node and a dummy parent node reltionship,
1490 -- use root node's VALUE_SET_ID (PARENT_VALUE_SET_ID = CHILD_VALUE_SET_ID)
1491 -- for both a root node and a dummy parent node
1492 --
1493 -- For a dummy parent node and an orphan node reltionship,
1494 -- use root node's VALUE_SET_ID (PARENT_VALUE_SET_ID = CHILD_VALUE_SET_ID)
1495 -- for a dummy parent node and
1496 -- use orphan node's VALUE_SET_ID for an orphan node
1497 --
1498 v_insert_b_subquery_h12 := '
1499 UNION ALL
1500 SELECT DISTINCT
1501 O.HIERARCHY_OBJ_DEF_ID,
1502 O.DUMMY_DISPLAY_ORDER_NUM, ' ||
1503 v_vs_column_parent_union2 || '
1504 O.CHILD_ID PARENT_ID, ' ||
1505 v_vs_column_child_union2 || '
1506 O.DUMMY_MEMBER_ID CHILD_ID
1507 FROM O
1508 UNION ALL
1509 SELECT
1510 O.HIERARCHY_OBJ_DEF_ID,
1511 O.DUMMY_DISPLAY_ORDER_NUM, ' ||
1512 v_vs_column_parent_union3 || '
1513 O.DUMMY_MEMBER_ID PARENT_ID, ' ||
1514 v_vs_column_child_union3 || '
1515 O.MEMBER_ID CHILD_ID
1516 FROM O';
1517
1521 p_msg_text => 'v_insert_b_subquery_h12_l='||LENGTH(v_insert_b_subquery_h12)
1518 FEM_ENGINES_PKG.Tech_Message(
1519 p_severity => pc_log_level_statement,
1520 p_module => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
1522 );
1523
1524 --
1525 -- Construct Dynamic SQL Elements for INSERT INTO _B table (exclude nonleaf)
1526 --
1527 -- (for RECONCILIATION hierarchy type)
1528 -- These conditoins are needed to filter out intermediate nodes.
1529 -- Having paths from a root node to intermediate nodes will generate
1530 -- duplicate values at intermediate levels, e.g. /1/2/3 and /1/2.
1531 --
1532 v_insert_b_exclude_nonleaf := '
1533 ) H1
1534 WHERE
1535 H1.CHILD_ID <> ' || pc_dummy_member_id || ' AND
1536 NOT EXISTS (
1537 SELECT 1
1538 FROM
1539 FEM_HIERARCHIES HIER3,
1540 FEM_OBJECT_DEFINITION_B DEF3,
1541 FEM_OBJECT_CATALOG_B CAT3,
1542 ' || v_hierarchy_table_name || ' H3
1543 WHERE
1544 HIER3.MULTI_VALUE_SET_FLAG = ''N'' AND
1545 HIER3.HIERARCHY_TYPE_CODE = :hier_type_code AND
1546 HIER3.DIMENSION_ID = :dimension_id AND
1547 HIER3.PERSONAL_FLAG = ''N'' AND
1548 CAT3.OBJECT_ID = HIER3.HIERARCHY_OBJ_ID AND
1549 DEF3.OBJECT_ID = CAT3.OBJECT_ID AND
1550 H3.HIERARCHY_OBJ_DEF_ID = DEF3.OBJECT_DEFINITION_ID AND
1551 H3.CHILD_DEPTH_NUM - H3.PARENT_DEPTH_NUM = 1 AND
1552 H1.HIERARCHY_OBJ_DEF_ID = H3.HIERARCHY_OBJ_DEF_ID AND ' ||
1553 v_vs_where_exclude_nonleaf || '
1554 H1.CHILD_ID =
1555 CASE
1556 WHEN H3.PARENT_ID = H3.CHILD_ID AND H3.PARENT_DEPTH_NUM = 1 THEN NULL
1557 ELSE H3.PARENT_ID
1558 END
1559 )
1560 START WITH
1561 H1.PARENT_ID IS NULL
1562 CONNECT BY
1563 H1.HIERARCHY_OBJ_DEF_ID = PRIOR H1.HIERARCHY_OBJ_DEF_ID AND ' ||
1564 v_vs_where_connect_by || '
1565 H1.PARENT_ID = PRIOR H1.CHILD_ID
1566 ) R
1567 )'; -- P
1568
1569 FEM_ENGINES_PKG.Tech_Message(
1570 p_severity => pc_log_level_statement,
1571 p_module => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
1572 p_msg_text => 'v_insert_b_exclude_nonleaf_l='||LENGTH(v_insert_b_exclude_nonleaf)
1573 );
1574
1575
1576 --
1577 -- Construct Dynamic SQL Elements for INSERT INTO _B table (exclude nonleaf)
1578 --
1579 -- (for OPEN hierarchy type)
1580 -- These conditoins are needed to filter out intermediate nodes.
1581 -- Having paths from a root node to intermediate nodes will generate
1582 -- duplicate values at intermediate levels, e.g. /1/2/3 and /1/2.
1583 --
1584 v_insert_b_exclude_nonleaf_o := '
1585 AND H1.CHILD_ID <> ' || pc_dummy_member_id || ' AND
1586 NOT EXISTS (
1587 SELECT 1
1588 FROM
1589 FEM_HIERARCHIES HIER3,
1590 FEM_OBJECT_DEFINITION_B DEF3,
1591 FEM_OBJECT_CATALOG_B CAT3,
1592 ' || v_hierarchy_table_name || ' H3
1593 WHERE
1594 HIER3.MULTI_VALUE_SET_FLAG = ''N'' AND
1595 HIER3.HIERARCHY_TYPE_CODE = :hier_type_code AND
1596 HIER3.DIMENSION_ID = :dimension_id AND
1597 HIER3.PERSONAL_FLAG = ''N'' AND
1598 CAT3.OBJECT_ID = HIER3.HIERARCHY_OBJ_ID AND
1599 DEF3.OBJECT_ID = CAT3.OBJECT_ID AND
1600 H3.HIERARCHY_OBJ_DEF_ID = DEF3.OBJECT_DEFINITION_ID AND
1601 H3.CHILD_DEPTH_NUM - H3.PARENT_DEPTH_NUM = 1 AND
1602 H1.HIERARCHY_OBJ_DEF_ID = H3.HIERARCHY_OBJ_DEF_ID AND ' ||
1603 v_vs_where_exclude_nonleaf || '
1604 H1.CHILD_ID = H3.PARENT_ID AND
1605 NOT (H3.PARENT_ID = H3.CHILD_ID AND H3.PARENT_DEPTH_NUM = 1)
1606 )
1607 START WITH
1608 H1.PARENT_ID = H1.CHILD_ID AND H1.PARENT_DEPTH_NUM = 1
1609 CONNECT BY
1610 H1.HIERARCHY_OBJ_DEF_ID = PRIOR H1.HIERARCHY_OBJ_DEF_ID AND ' ||
1611 v_vs_where_connect_by || '
1612 H1.PARENT_ID = PRIOR H1.CHILD_ID
1613 AND H1.SINGLE_DEPTH_FLAG = ''Y'' AND H1.CHILD_DEPTH_NUM <> 1
1614 ) R
1615 )'; -- P
1616
1617 FEM_ENGINES_PKG.Tech_Message(
1618 p_severity => pc_log_level_statement,
1619 p_module => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
1620 p_msg_text => 'v_insert_b_exclude_nonleaf_o_l='||LENGTH(v_insert_b_exclude_nonleaf_o)
1621 );
1622
1623
1624 --
1625 -- Construct Dynamic SQL Elements for INSERT INTO _B table (subquery q1)
1626 --
1627 v_insert_b_subquery_q1 := '
1628 SELECT
1629 D.OBJECT_ID,
1630 D.OBJECT_DEFINITION_ID, ' ||
1631 v_vs_column_subquery_q || '
1632 P.L1 LEVEL1_ID,
1633 COALESCE(P.L2,P.L1) LEVEL2_ID,
1634 COALESCE(P.L3,P.L2,P.L1) LEVEL3_ID,
1635 COALESCE(P.L4,P.L3,P.L2,P.L1) LEVEL4_ID,
1636 COALESCE(P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL5_ID,
1637 COALESCE(P.L6,P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL6_ID,
1638 COALESCE(P.L7,P.L6,P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL7_ID,
1639 COALESCE(P.L8,P.L7,P.L6,P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL8_ID,
1640 COALESCE(P.L9,P.L8,P.L7,P.L6,P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL9_ID,
1641 COALESCE(P.L10,P.L9,P.L8,P.L7,P.L6,P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL10_ID,
1642 COALESCE(P.L11,P.L10,P.L9,P.L8,P.L7,P.L6,P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL11_ID,
1643 COALESCE(P.L12,P.L11,P.L10,P.L9,P.L8,P.L7,P.L6,P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL12_ID,
1647 COALESCE(P.L16,P.L15,P.L14,P.L13,P.L12,P.L11,P.L10,P.L9,P.L8,P.L7,P.L6,P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL16_ID,
1644 COALESCE(P.L13,P.L12,P.L11,P.L10,P.L9,P.L8,P.L7,P.L6,P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL13_ID,
1645 COALESCE(P.L14,P.L13,P.L12,P.L11,P.L10,P.L9,P.L8,P.L7,P.L6,P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL14_ID,
1646 COALESCE(P.L15,P.L14,P.L13,P.L12,P.L11,P.L10,P.L9,P.L8,P.L7,P.L6,P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL15_ID,
1648 COALESCE(P.L17,P.L16,P.L15,P.L14,P.L13,P.L12,P.L11,P.L10,P.L9,P.L8,P.L7,P.L6,P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL17_ID,
1649 COALESCE(P.L18,P.L17,P.L16,P.L15,P.L14,P.L13,P.L12,P.L11,P.L10,P.L9,P.L8,P.L7,P.L6,P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL18_ID,
1650 COALESCE(P.L19,P.L18,P.L17,P.L16,P.L15,P.L14,P.L13,P.L12,P.L11,P.L10,P.L9,P.L8,P.L7,P.L6,P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL19_ID,
1651 COALESCE(P.L20,P.L19,P.L18,P.L17,P.L16,P.L15,P.L14,P.L13,P.L12,P.L11,P.L10,P.L9,P.L8,P.L7,P.L6,P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL20_ID,';
1652
1653 FEM_ENGINES_PKG.Tech_Message(
1654 p_severity => pc_log_level_statement,
1655 p_module => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
1656 p_msg_text => 'v_insert_b_subquery_q1_l='||LENGTH(v_insert_b_subquery_q1)
1657 );
1658
1659
1660 --
1661 -- Construct Dynamic SQL Elements for INSERT INTO _B table (subquery q2)
1662 --
1663 v_insert_b_subquery_q2 := '
1664 P.N1 LEVEL1_DISPLAY_ORDER_NUM,
1665 COALESCE(P.N2,P.N1) LEVEL2_DISPLAY_ORDER_NUM,
1666 COALESCE(P.N3,P.N2,P.N1) LEVEL3_DISPLAY_ORDER_NUM,
1667 COALESCE(P.N4,P.N3,P.N2,P.N1) LEVEL4_DISPLAY_ORDER_NUM,
1668 COALESCE(P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL5_DISPLAY_ORDER_NUM,
1669 COALESCE(P.N6,P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL6_DISPLAY_ORDER_NUM,
1670 COALESCE(P.N7,P.N6,P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL7_DISPLAY_ORDER_NUM,
1671 COALESCE(P.N8,P.N7,P.N6,P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL8_DISPLAY_ORDER_NUM,
1672 COALESCE(P.N9,P.N8,P.N7,P.N6,P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL9_DISPLAY_ORDER_NUM,
1673 COALESCE(P.N10,P.N9,P.N8,P.N7,P.N6,P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL10_DISPLAY_ORDER_NUM,
1674 COALESCE(P.N11,P.N10,P.N9,P.N8,P.N7,P.N6,P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL11_DISPLAY_ORDER_NUM,
1675 COALESCE(P.N12,P.N11,P.N10,P.N9,P.N8,P.N7,P.N6,P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL12_DISPLAY_ORDER_NUM,
1676 COALESCE(P.N13,P.N12,P.N11,P.N10,P.N9,P.N8,P.N7,P.N6,P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL13_DISPLAY_ORDER_NUM,
1677 COALESCE(P.N14,P.N13,P.N12,P.N11,P.N10,P.N9,P.N8,P.N7,P.N6,P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL14_DISPLAY_ORDER_NUM,
1678 COALESCE(P.N15,P.N14,P.N13,P.N12,P.N11,P.N10,P.N9,P.N8,P.N7,P.N6,P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL15_DISPLAY_ORDER_NUM,
1679 COALESCE(P.N16,P.N15,P.N14,P.N13,P.N12,P.N11,P.N10,P.N9,P.N8,P.N7,P.N6,P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL16_DISPLAY_ORDER_NUM,
1680 COALESCE(P.N17,P.N16,P.N15,P.N14,P.N13,P.N12,P.N11,P.N10,P.N9,P.N8,P.N7,P.N6,P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL17_DISPLAY_ORDER_NUM,
1681 COALESCE(P.N18,P.N17,P.N16,P.N15,P.N14,P.N13,P.N12,P.N11,P.N10,P.N9,P.N8,P.N7,P.N6,P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL18_DISPLAY_ORDER_NUM,
1682 COALESCE(P.N19,P.N18,P.N17,P.N16,P.N15,P.N14,P.N13,P.N12,P.N11,P.N10,P.N9,P.N8,P.N7,P.N6,P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL19_DISPLAY_ORDER_NUM,
1683 COALESCE(P.N20,P.N19,P.N18,P.N17,P.N16,P.N15,P.N14,P.N13,P.N12,P.N11,P.N10,P.N9,P.N8,P.N7,P.N6,P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL20_DISPLAY_ORDER_NUM
1684 FROM
1685 P,
1686 FEM_HIERARCHIES H,
1687 FEM_OBJECT_DEFINITION_B D,
1688 FEM_OBJECT_CATALOG_B C
1689 WHERE
1690 D.OBJECT_DEFINITION_ID = P.HIERARCHY_OBJ_DEF_ID AND
1691 H.DIMENSION_ID = :dimension_id AND
1692 D.OBJECT_ID = H.HIERARCHY_OBJ_ID AND
1693 C.OBJECT_ID = D.OBJECT_ID
1694 ) Q';
1695
1696 FEM_ENGINES_PKG.Tech_Message(
1697 p_severity => pc_log_level_statement,
1698 p_module => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
1699 p_msg_text => 'v_insert_b_subquery_q2_l='||LENGTH(v_insert_b_subquery_q2)
1700 );
1701
1702
1703 /************************************************************************
1704 Construct Dynamic SQL Elements for INSERT INTO _TL table
1705 *************************************************************************/
1706 --
1707 -- Construct Dynamic SQL Elements for INSERT INTO _TL table (Part 1)
1708 --
1709 v_insert_tl_column_list := '
1710 INSERT INTO ' || v_dis_hierarchy_table_name_tl || ' (
1711 OBJECT_ID,
1712 OBJECT_DEFINITION_ID, ' ||
1713 v_vs_column_list || '
1714 LEVEL1_ID,
1715 LEVEL2_ID,
1716 LEVEL3_ID,
1717 LEVEL4_ID,
1718 LEVEL5_ID,
1719 LEVEL6_ID,
1720 LEVEL7_ID,
1721 LEVEL8_ID,
1722 LEVEL9_ID,
1723 LEVEL10_ID,
1724 LEVEL11_ID,
1725 LEVEL12_ID,
1726 LEVEL13_ID,
1727 LEVEL14_ID,
1728 LEVEL15_ID,
1729 LEVEL16_ID,
1730 LEVEL17_ID,
1731 LEVEL18_ID,
1732 LEVEL19_ID,
1733 LEVEL20_ID,
1734 LANGUAGE,
1735 SOURCE_LANG,
1736 OBJECT_NAME,
1737 OBJECT_DEFINITION_NAME,
1738 LEVEL1_NAME,
1739 LEVEL2_NAME,
1740 LEVEL3_NAME,
1741 LEVEL4_NAME,
1742 LEVEL5_NAME,
1743 LEVEL6_NAME,
1744 LEVEL7_NAME,
1745 LEVEL8_NAME,
1746 LEVEL9_NAME,
1747 LEVEL10_NAME,
1748 LEVEL11_NAME,
1749 LEVEL12_NAME,
1750 LEVEL13_NAME,
1751 LEVEL14_NAME,
1752 LEVEL15_NAME,
1753 LEVEL16_NAME,
1754 LEVEL17_NAME,
1755 LEVEL18_NAME,
1756 LEVEL19_NAME,
1757 LEVEL20_NAME,
1758 LEVEL1_DESCRIPTION,
1759 LEVEL2_DESCRIPTION,
1760 LEVEL3_DESCRIPTION,
1761 LEVEL4_DESCRIPTION,
1762 LEVEL5_DESCRIPTION,
1763 LEVEL6_DESCRIPTION,
1764 LEVEL7_DESCRIPTION,
1765 LEVEL8_DESCRIPTION,
1769 LEVEL12_DESCRIPTION,
1766 LEVEL9_DESCRIPTION,
1767 LEVEL10_DESCRIPTION,
1768 LEVEL11_DESCRIPTION,
1770 LEVEL13_DESCRIPTION,
1771 LEVEL14_DESCRIPTION,
1772 LEVEL15_DESCRIPTION,
1773 LEVEL16_DESCRIPTION,
1774 LEVEL17_DESCRIPTION,
1775 LEVEL18_DESCRIPTION,
1776 LEVEL19_DESCRIPTION,
1777 LEVEL20_DESCRIPTION,
1778 CREATION_DATE,
1779 CREATED_BY,
1780 LAST_UPDATE_DATE,
1781 LAST_UPDATED_BY,
1782 LAST_UPDATE_LOGIN
1783 )
1784 SELECT
1785 B.OBJECT_ID,
1786 B.OBJECT_DEFINITION_ID, ' ||
1787 v_vs_column_member_b_table || '
1788 B.LEVEL1_ID,
1789 B.LEVEL2_ID,
1790 B.LEVEL3_ID,
1791 B.LEVEL4_ID,
1792 B.LEVEL5_ID,
1793 B.LEVEL6_ID,
1794 B.LEVEL7_ID,
1795 B.LEVEL8_ID,
1796 B.LEVEL9_ID,
1797 B.LEVEL10_ID,
1798 B.LEVEL11_ID,
1799 B.LEVEL12_ID,
1800 B.LEVEL13_ID,
1801 B.LEVEL14_ID,
1802 B.LEVEL15_ID,
1803 B.LEVEL16_ID,
1804 B.LEVEL17_ID,
1805 B.LEVEL18_ID,
1806 B.LEVEL19_ID,
1807 B.LEVEL20_ID,
1808 L.LANGUAGE_CODE,';
1809
1810 --
1811 -- Construct Dynamic SQL Elements for INSERT INTO _TL table (Part 2)
1812 --
1813 v_insert_tl_name1 := '
1814 (
1815 SELECT SL.LANGUAGE_CODE
1816 FROM FND_LANGUAGES SL
1817 WHERE SL.INSTALLED_FLAG = ''B''
1818 ),
1819 (
1820 SELECT TL.OBJECT_NAME
1821 FROM FEM_OBJECT_CATALOG_TL TL
1822 WHERE TL.OBJECT_ID = B.OBJECT_ID
1823 AND TL.LANGUAGE = L.LANGUAGE_CODE
1824 ),
1825 (
1826 SELECT TL.DISPLAY_NAME
1827 FROM FEM_OBJECT_DEFINITION_TL TL
1828 WHERE TL.OBJECT_DEFINITION_ID = B.OBJECT_DEFINITION_ID
1829 AND TL.LANGUAGE = L.LANGUAGE_CODE
1830 ),
1831 (
1832 SELECT TL.' || v_member_name_col || '
1833 FROM ' || v_member_tl_table_name || ' TL
1834 WHERE TL.' || v_member_col || ' = B.LEVEL1_ID ' ||
1835 v_vs_where_tl || '
1836 AND TL.LANGUAGE = L.LANGUAGE_CODE
1837 ),
1838 DECODE(B.LEVEL2_ID, ' || pc_dummy_member_id || ', ''' || v_dummy_parent_name || ''',
1839 (
1840 SELECT TL.' || v_member_name_col || '
1841 FROM ' || v_member_tl_table_name || ' TL
1842 WHERE TL.' || v_member_col || ' = B.LEVEL2_ID ' ||
1843 v_vs_where_tl || '
1844 AND TL.LANGUAGE = L.LANGUAGE_CODE
1845 )
1846 ),
1847 (
1848 SELECT TL.' || v_member_name_col || '
1849 FROM ' || v_member_tl_table_name || ' TL
1850 WHERE TL.' || v_member_col || ' = B.LEVEL3_ID ' ||
1851 v_vs_where_tl || '
1852 AND TL.LANGUAGE = L.LANGUAGE_CODE
1853 ),
1854 (
1855 SELECT TL.' || v_member_name_col || '
1856 FROM ' || v_member_tl_table_name || ' TL
1857 WHERE TL.' || v_member_col || ' = B.LEVEL4_ID ' ||
1858 v_vs_where_tl || '
1859 AND TL.LANGUAGE = L.LANGUAGE_CODE
1860 ),
1861 (
1862 SELECT TL.' || v_member_name_col || '
1863 FROM ' || v_member_tl_table_name || ' TL
1864 WHERE TL.' || v_member_col || ' = B.LEVEL5_ID ' ||
1865 v_vs_where_tl || '
1866 AND TL.LANGUAGE = L.LANGUAGE_CODE
1867 ),
1868 (
1869 SELECT TL.' || v_member_name_col || '
1870 FROM ' || v_member_tl_table_name || ' TL
1871 WHERE TL.' || v_member_col || ' = B.LEVEL6_ID ' ||
1872 v_vs_where_tl || '
1873 AND TL.LANGUAGE = L.LANGUAGE_CODE
1874 ),
1875 (
1876 SELECT TL.' || v_member_name_col || '
1877 FROM ' || v_member_tl_table_name || ' TL
1878 WHERE TL.' || v_member_col || ' = B.LEVEL7_ID ' ||
1879 v_vs_where_tl || '
1880 AND TL.LANGUAGE = L.LANGUAGE_CODE
1881 ),
1882 (
1883 SELECT TL.' || v_member_name_col || '
1884 FROM ' || v_member_tl_table_name || ' TL
1885 WHERE TL.' || v_member_col || ' = B.LEVEL8_ID ' ||
1886 v_vs_where_tl || '
1887 AND TL.LANGUAGE = L.LANGUAGE_CODE
1888 ),
1889 (
1890 SELECT TL.' || v_member_name_col || '
1891 FROM ' || v_member_tl_table_name || ' TL
1892 WHERE TL.' || v_member_col || ' = B.LEVEL9_ID ' ||
1893 v_vs_where_tl || '
1894 AND TL.LANGUAGE = L.LANGUAGE_CODE
1895 ),
1896 (
1897 SELECT TL.' || v_member_name_col || '
1898 FROM ' || v_member_tl_table_name || ' TL
1899 WHERE TL.' || v_member_col || ' = B.LEVEL10_ID ' ||
1900 v_vs_where_tl || '
1901 AND TL.LANGUAGE = L.LANGUAGE_CODE
1902 ),';
1903
1904 --
1905 -- Construct Dynamic SQL Elements for INSERT INTO _TL table (Part 3)
1906 --
1907 v_insert_tl_name2 := '
1908 (
1909 SELECT TL.' || v_member_name_col || '
1910 FROM ' || v_member_tl_table_name || ' TL
1911 WHERE TL.' || v_member_col || ' = B.LEVEL11_ID ' ||
1912 v_vs_where_tl || '
1913 AND TL.LANGUAGE = L.LANGUAGE_CODE
1914 ),
1915 (
1916 SELECT TL.' || v_member_name_col || '
1917 FROM ' || v_member_tl_table_name || ' TL
1918 WHERE TL.' || v_member_col || ' = B.LEVEL12_ID ' ||
1919 v_vs_where_tl || '
1920 AND TL.LANGUAGE = L.LANGUAGE_CODE
1921 ),
1922 (
1923 SELECT TL.' || v_member_name_col || '
1924 FROM ' || v_member_tl_table_name || ' TL
1925 WHERE TL.' || v_member_col || ' = B.LEVEL13_ID ' ||
1926 v_vs_where_tl || '
1930 SELECT TL.' || v_member_name_col || '
1927 AND TL.LANGUAGE = L.LANGUAGE_CODE
1928 ),
1929 (
1931 FROM ' || v_member_tl_table_name || ' TL
1932 WHERE TL.' || v_member_col || ' = B.LEVEL14_ID ' ||
1933 v_vs_where_tl || '
1934 AND TL.LANGUAGE = L.LANGUAGE_CODE
1935 ),
1936 (
1937 SELECT TL.' || v_member_name_col || '
1938 FROM ' || v_member_tl_table_name || ' TL
1939 WHERE TL.' || v_member_col || ' = B.LEVEL15_ID ' ||
1940 v_vs_where_tl || '
1941 AND TL.LANGUAGE = L.LANGUAGE_CODE
1942 ),
1943 (
1944 SELECT TL.' || v_member_name_col || '
1945 FROM ' || v_member_tl_table_name || ' TL
1946 WHERE TL.' || v_member_col || ' = B.LEVEL16_ID ' ||
1947 v_vs_where_tl || '
1948 AND TL.LANGUAGE = L.LANGUAGE_CODE
1949 ),
1950 (
1951 SELECT TL.' || v_member_name_col || '
1952 FROM ' || v_member_tl_table_name || ' TL
1953 WHERE TL.' || v_member_col || ' = B.LEVEL17_ID ' ||
1954 v_vs_where_tl || '
1955 AND TL.LANGUAGE = L.LANGUAGE_CODE
1956 ),
1957 (
1958 SELECT TL.' || v_member_name_col || '
1959 FROM ' || v_member_tl_table_name || ' TL
1960 WHERE TL.' || v_member_col || ' = B.LEVEL18_ID ' ||
1961 v_vs_where_tl || '
1962 AND TL.LANGUAGE = L.LANGUAGE_CODE
1963 ),
1964 (
1965 SELECT TL.' || v_member_name_col || '
1966 FROM ' || v_member_tl_table_name || ' TL
1967 WHERE TL.' || v_member_col || ' = B.LEVEL19_ID ' ||
1968 v_vs_where_tl || '
1969 AND TL.LANGUAGE = L.LANGUAGE_CODE
1970 ),
1971 (
1972 SELECT TL.' || v_member_name_col || '
1973 FROM ' || v_member_tl_table_name || ' TL
1974 WHERE TL.' || v_member_col || ' = B.LEVEL20_ID ' ||
1975 v_vs_where_tl || '
1976 AND TL.LANGUAGE = L.LANGUAGE_CODE
1977 ),';
1978
1979 --
1980 -- Construct Dynamic SQL Elements for INSERT INTO _TL table (Part 4)
1981 --
1982 v_insert_tl_description1 := '
1983 (
1984 SELECT TL.DESCRIPTION
1985 FROM ' || v_member_tl_table_name || ' TL
1986 WHERE TL.' || v_member_col || ' = B.LEVEL1_ID ' ||
1987 v_vs_where_tl || '
1988 AND TL.LANGUAGE = L.LANGUAGE_CODE
1989 ),
1990 DECODE(B.LEVEL2_ID, ' || pc_dummy_member_id || ', ''' || v_dummy_parent_name || ''',
1991 (
1992 SELECT TL.DESCRIPTION
1993 FROM ' || v_member_tl_table_name || ' TL
1994 WHERE TL.' || v_member_col || ' = B.LEVEL2_ID ' ||
1995 v_vs_where_tl || '
1996 AND TL.LANGUAGE = L.LANGUAGE_CODE
1997 )
1998 ),
1999 (
2000 SELECT TL.DESCRIPTION
2001 FROM ' || v_member_tl_table_name || ' TL
2002 WHERE TL.' || v_member_col || ' = B.LEVEL3_ID ' ||
2003 v_vs_where_tl || '
2004 AND TL.LANGUAGE = L.LANGUAGE_CODE
2005 ),
2006 (
2007 SELECT TL.DESCRIPTION
2008 FROM ' || v_member_tl_table_name || ' TL
2009 WHERE TL.' || v_member_col || ' = B.LEVEL4_ID ' ||
2010 v_vs_where_tl || '
2011 AND TL.LANGUAGE = L.LANGUAGE_CODE
2012 ),
2013 (
2014 SELECT TL.DESCRIPTION
2015 FROM ' || v_member_tl_table_name || ' TL
2016 WHERE TL.' || v_member_col || ' = B.LEVEL5_ID ' ||
2017 v_vs_where_tl || '
2018 AND TL.LANGUAGE = L.LANGUAGE_CODE
2019 ),
2020 (
2021 SELECT TL.DESCRIPTION
2022 FROM ' || v_member_tl_table_name || ' TL
2023 WHERE TL.' || v_member_col || ' = B.LEVEL6_ID ' ||
2024 v_vs_where_tl || '
2025 AND TL.LANGUAGE = L.LANGUAGE_CODE
2026 ),
2027 (
2028 SELECT TL.DESCRIPTION
2029 FROM ' || v_member_tl_table_name || ' TL
2030 WHERE TL.' || v_member_col || ' = B.LEVEL7_ID ' ||
2031 v_vs_where_tl || '
2032 AND TL.LANGUAGE = L.LANGUAGE_CODE
2033 ),
2034 (
2035 SELECT TL.DESCRIPTION
2036 FROM ' || v_member_tl_table_name || ' TL
2037 WHERE TL.' || v_member_col || ' = B.LEVEL8_ID ' ||
2038 v_vs_where_tl || '
2039 AND TL.LANGUAGE = L.LANGUAGE_CODE
2040 ),
2041 (
2042 SELECT TL.DESCRIPTION
2043 FROM ' || v_member_tl_table_name || ' TL
2044 WHERE TL.' || v_member_col || ' = B.LEVEL9_ID ' ||
2045 v_vs_where_tl || '
2046 AND TL.LANGUAGE = L.LANGUAGE_CODE
2047 ),
2048 (
2049 SELECT TL.DESCRIPTION
2050 FROM ' || v_member_tl_table_name || ' TL
2051 WHERE TL.' || v_member_col || ' = B.LEVEL10_ID ' ||
2052 v_vs_where_tl || '
2053 AND TL.LANGUAGE = L.LANGUAGE_CODE
2054 ),';
2055
2056 --
2057 -- Construct Dynamic SQL Elements for INSERT INTO _TL table (Part 5)
2058 --
2059 v_insert_tl_description2 := '
2060 (
2061 SELECT TL.DESCRIPTION
2062 FROM ' || v_member_tl_table_name || ' TL
2063 WHERE TL.' || v_member_col || ' = B.LEVEL11_ID ' ||
2064 v_vs_where_tl || '
2065 AND TL.LANGUAGE = L.LANGUAGE_CODE
2066 ),
2067 (
2068 SELECT TL.DESCRIPTION
2069 FROM ' || v_member_tl_table_name || ' TL
2070 WHERE TL.' || v_member_col || ' = B.LEVEL12_ID ' ||
2071 v_vs_where_tl || '
2072 AND TL.LANGUAGE = L.LANGUAGE_CODE
2073 ),
2074 (
2075 SELECT TL.DESCRIPTION
2076 FROM ' || v_member_tl_table_name || ' TL
2077 WHERE TL.' || v_member_col || ' = B.LEVEL13_ID ' ||
2078 v_vs_where_tl || '
2082 SELECT TL.DESCRIPTION
2079 AND TL.LANGUAGE = L.LANGUAGE_CODE
2080 ),
2081 (
2083 FROM ' || v_member_tl_table_name || ' TL
2084 WHERE TL.' || v_member_col || ' = B.LEVEL14_ID ' ||
2085 v_vs_where_tl || '
2086 AND TL.LANGUAGE = L.LANGUAGE_CODE
2087 ),
2088 (
2089 SELECT TL.DESCRIPTION
2090 FROM ' || v_member_tl_table_name || ' TL
2091 WHERE TL.' || v_member_col || ' = B.LEVEL15_ID ' ||
2092 v_vs_where_tl || '
2093 AND TL.LANGUAGE = L.LANGUAGE_CODE
2094 ),
2095 (
2096 SELECT TL.DESCRIPTION
2097 FROM ' || v_member_tl_table_name || ' TL
2098 WHERE TL.' || v_member_col || ' = B.LEVEL16_ID ' ||
2099 v_vs_where_tl || '
2100 AND TL.LANGUAGE = L.LANGUAGE_CODE
2101 ),
2102 (
2103 SELECT TL.DESCRIPTION
2104 FROM ' || v_member_tl_table_name || ' TL
2105 WHERE TL.' || v_member_col || ' = B.LEVEL17_ID ' ||
2106 v_vs_where_tl || '
2107 AND TL.LANGUAGE = L.LANGUAGE_CODE
2108 ),
2109 (
2110 SELECT TL.DESCRIPTION
2111 FROM ' || v_member_tl_table_name || ' TL
2112 WHERE TL.' || v_member_col || ' = B.LEVEL18_ID ' ||
2113 v_vs_where_tl || '
2114 AND TL.LANGUAGE = L.LANGUAGE_CODE
2115 ),
2116 (
2117 SELECT TL.DESCRIPTION
2118 FROM ' || v_member_tl_table_name || ' TL
2119 WHERE TL.' || v_member_col || ' = B.LEVEL19_ID ' ||
2120 v_vs_where_tl || '
2121 AND TL.LANGUAGE = L.LANGUAGE_CODE
2122 ),
2123 (
2124 SELECT TL.DESCRIPTION
2125 FROM ' || v_member_tl_table_name || ' TL
2126 WHERE TL.' || v_member_col || ' = B.LEVEL20_ID ' ||
2127 v_vs_where_tl || '
2128 AND TL.LANGUAGE = L.LANGUAGE_CODE
2129 ),
2130 SYSDATE CREATION_DATE,
2131 :pv_user_id CREATED_BY,
2132 SYSDATE LAST_UPDATE_DATE,
2133 :pv_user_id LAST_UPDATED_BY,
2134 :pv_login_id LAST_UPDATE_LOGIN
2135 FROM
2136 ' || v_dis_hierarchy_table_name_b || ' B,
2137 FND_LANGUAGES L
2138 WHERE
2139 L.INSTALLED_FLAG IN (''B'', ''I'')';
2140
2141
2142 /************************************************************************
2143 Execute Dynamic SQLs for _B
2144 *************************************************************************/
2145 --
2146 -- Execute Dynamic SQL for DELETE FROM _B table
2147 --
2148 FEM_ENGINES_PKG.Tech_Message(
2149 p_severity => pc_log_level_statement,
2150 p_module => v_module || '.delete_from_b',
2151 p_msg_text => 'Deleting from _B table'
2152 );
2153
2154 EXECUTE IMMEDIATE '
2155 DELETE FROM ' || v_dis_hierarchy_table_name_b;
2156
2157 v_deleted_b := SQL%ROWCOUNT;
2158
2159 --
2160 -- Execute Dynamic SQL for INSERT INTO _B table
2161 --
2162 -- Open hierarchy type
2163 FEM_ENGINES_PKG.Tech_Message(
2164 p_severity => pc_log_level_statement,
2165 p_module => v_module || '.insert_into_b_open_attr_enabled_dim',
2166 p_msg_text => 'Inserting into _B table for Open hierarchy (attribute enabled dimension)'
2167 );
2168
2169 Print_DSQL_Insert_B(
2170 v_module,
2171 v_dis_hierarchy_table_name_b,
2172 v_insert_b_column_list,
2173 v_insert_b_display_code1,
2174 v_insert_b_display_code2,
2175 NULL,
2176 v_insert_b_with_p1,
2177 v_insert_b_with_p2,
2178 v_insert_b_with_p3,
2179 v_insert_b_subquery_r1,
2180 v_insert_b_subquery_r2,
2181 v_insert_b_subquery_h11_o,
2182 NULL,
2183 v_insert_b_exclude_nonleaf_o,
2184 v_insert_b_subquery_q1,
2185 v_insert_b_subquery_q2,
2186 pv_user_id,
2187 pv_user_id,
2188 pv_login_id,
2189 'OPEN',
2190 v_dimension_id,
2191 v_dimension_id,
2192 'OPEN',
2193 v_dimension_id,
2194 NULL,
2195 NULL,
2196 NULL
2197 );
2198
2199 EXECUTE IMMEDIATE
2200 v_insert_b_column_list ||
2201 v_insert_b_display_code1 ||
2202 v_insert_b_display_code2 ||
2203 '' ||
2204 v_insert_b_with_p1 ||
2205 v_insert_b_with_p2 ||
2206 v_insert_b_with_p3 ||
2207 v_insert_b_subquery_r1 ||
2208 v_insert_b_subquery_r2 ||
2209 v_insert_b_subquery_h11_o ||
2210 '' ||
2211 v_insert_b_exclude_nonleaf_o ||
2212 v_insert_b_subquery_q1 ||
2213 v_insert_b_subquery_q2
2214 USING
2215 pv_user_id,
2216 pv_user_id,
2217 pv_login_id,
2218 'OPEN',
2219 v_dimension_id,
2220 'OPEN',
2221 v_dimension_id,
2222 v_dimension_id;
2223
2224 v_inserted_b1 := SQL%ROWCOUNT;
2225
2226 IF v_attribute_table_name IS NOT NULL THEN
2227
2228 -- Reconciliation hierarchy type
2229 FEM_ENGINES_PKG.Tech_Message(
2230 p_severity => pc_log_level_statement,
2231 p_module => v_module || '.insert_into_b_recon_attr_enabled_dim',
2232 p_msg_text => 'Inserting into _B table for Reconciliation hierarchy (attribute enabled dimension)'
2233 );
2234
2235 Print_DSQL_Insert_B(
2236 v_module,
2237 v_dis_hierarchy_table_name_b,
2241 v_insert_b_with_o,
2238 v_insert_b_column_list,
2239 v_insert_b_display_code1,
2240 v_insert_b_display_code2,
2242 v_insert_b_with_p1,
2243 v_insert_b_with_p2,
2244 v_insert_b_with_p3,
2245 v_insert_b_subquery_r1,
2246 v_insert_b_subquery_r2,
2247 v_insert_b_subquery_h11,
2248 v_insert_b_subquery_h12,
2249 v_insert_b_exclude_nonleaf,
2250 v_insert_b_subquery_q1,
2251 v_insert_b_subquery_q2,
2252 pv_user_id,
2253 pv_user_id,
2254 pv_login_id,
2255 v_dimension_id,
2256 v_dimension_id,
2257 v_dimension_id,
2258 'RECONCILIATION',
2259 v_dimension_id,
2260 'RECONCILIATION',
2261 v_dimension_id,
2262 v_dimension_id
2263 );
2264
2265 EXECUTE IMMEDIATE
2266 v_insert_b_column_list ||
2267 v_insert_b_display_code1 ||
2268 v_insert_b_display_code2 ||
2269 v_insert_b_with_o ||
2270 v_insert_b_with_p1 ||
2271 v_insert_b_with_p2 ||
2272 v_insert_b_with_p3 ||
2273 v_insert_b_subquery_r1 ||
2274 v_insert_b_subquery_r2 ||
2275 v_insert_b_subquery_h11 ||
2276 v_insert_b_subquery_h12 ||
2277 v_insert_b_exclude_nonleaf ||
2278 v_insert_b_subquery_q1 ||
2279 v_insert_b_subquery_q2
2280 USING
2281 pv_user_id,
2282 pv_user_id,
2283 pv_login_id,
2284 v_dimension_id,
2285 v_dimension_id,
2286 v_dimension_id,
2287 'RECONCILIATION',
2288 v_dimension_id,
2289 'RECONCILIATION',
2290 v_dimension_id,
2291 v_dimension_id;
2292
2293 ELSE -- IF v_attribute_table_name IS NOT NULL THEN
2294
2295 -- Reconciliation hierarchy type
2296 FEM_ENGINES_PKG.Tech_Message(
2297 p_severity => pc_log_level_statement,
2298 p_module => v_module || '.insert_into_b_recon_attr_disabled_dim',
2299 p_msg_text => 'Inserting into _B table for Reconciliation hierarchy (attribute disabled dimension)'
2300 );
2301
2302 Print_DSQL_Insert_B(
2303 v_module,
2304 v_dis_hierarchy_table_name_b,
2305 v_insert_b_column_list,
2306 v_insert_b_display_code1,
2307 v_insert_b_display_code2,
2308 v_insert_b_with_o,
2309 v_insert_b_with_p1,
2310 v_insert_b_with_p2,
2311 v_insert_b_with_p3,
2312 v_insert_b_subquery_r1,
2313 v_insert_b_subquery_r2,
2314 v_insert_b_subquery_h11,
2315 v_insert_b_subquery_h12,
2316 v_insert_b_exclude_nonleaf,
2317 v_insert_b_subquery_q1,
2318 v_insert_b_subquery_q2,
2319 pv_user_id,
2320 pv_user_id,
2321 pv_login_id,
2322 v_dimension_id,
2323 v_dimension_id,
2324 'RECONCILIATION',
2325 v_dimension_id,
2326 'RECONCILIATION',
2327 v_dimension_id,
2328 v_dimension_id,
2329 NULL
2330 );
2331
2332 EXECUTE IMMEDIATE
2333 v_insert_b_column_list ||
2334 v_insert_b_display_code1 ||
2335 v_insert_b_display_code2 ||
2336 v_insert_b_with_o ||
2337 v_insert_b_with_p1 ||
2338 v_insert_b_with_p2 ||
2339 v_insert_b_with_p3 ||
2340 v_insert_b_subquery_r1 ||
2341 v_insert_b_subquery_r2 ||
2342 v_insert_b_subquery_h11 ||
2343 v_insert_b_subquery_h12 ||
2344 v_insert_b_exclude_nonleaf ||
2345 v_insert_b_subquery_q1 ||
2346 v_insert_b_subquery_q2
2347 USING
2348 pv_user_id,
2349 pv_user_id,
2350 pv_login_id,
2351 v_dimension_id,
2352 v_dimension_id,
2353 'RECONCILIATION',
2354 v_dimension_id,
2355 'RECONCILIATION',
2356 v_dimension_id,
2357 v_dimension_id;
2358
2359 END IF;
2360
2361 v_inserted_b2 := SQL%ROWCOUNT;
2362
2363
2364 /************************************************************************
2365 Execute Dynamic SQLs for _TL
2366 *************************************************************************/
2367 IF v_member_tl_table_name IS NOT NULL THEN
2368
2369 --
2370 -- Execute Dynamic SQL for DELETE FROM _TL table
2371 --
2372 FEM_ENGINES_PKG.Tech_Message(
2373 p_severity => pc_log_level_statement,
2374 p_module => v_module || '.delete_from_tl',
2375 p_msg_text => 'Deleting from _TL table'
2376 );
2377
2378 EXECUTE IMMEDIATE '
2379 DELETE FROM ' || v_dis_hierarchy_table_name_tl;
2380
2381 v_deleted_tl := SQL%ROWCOUNT;
2382
2383 --
2384 -- Execute Dynamic SQL for INSERT INTO _TL table
2385 --
2386 FEM_ENGINES_PKG.Tech_Message(
2387 p_severity => pc_log_level_statement,
2388 p_module => v_module || '.delete_from_tl',
2389 p_msg_text => 'Inserting into _TL table'
2390 );
2391
2392 Print_DSQL_Insert_TL(
2396 v_insert_tl_name1,
2393 v_module,
2394 v_dis_hierarchy_table_name_tl,
2395 v_insert_tl_column_list,
2397 v_insert_tl_name2,
2398 v_insert_tl_description1,
2399 v_insert_tl_description2,
2400 pv_user_id,
2401 pv_user_id,
2402 pv_login_id
2403 );
2404
2405 EXECUTE IMMEDIATE
2406 v_insert_tl_column_list ||
2407 v_insert_tl_name1 ||
2408 v_insert_tl_name2 ||
2409 v_insert_tl_description1 ||
2410 v_insert_tl_description2
2411 USING
2412 pv_user_id,
2413 pv_user_id,
2414 pv_login_id;
2415
2416 v_inserted_tl := SQL%ROWCOUNT;
2417
2418 END IF;
2419
2420 COMMIT;
2421
2422 --
2423 -- Get a list of muti value set enabled hierarchies
2424 --
2425 EXECUTE IMMEDIATE '
2426 SELECT DISTINCT
2427 CAT.OBJECT_NAME,
2428 CAT.OBJECT_ID
2429 FROM
2430 FEM_HIERARCHIES HIER,
2431 FEM_OBJECT_DEFINITION_VL DEF,
2432 FEM_OBJECT_CATALOG_VL CAT,
2433 ' || v_hierarchy_table_name || ' H
2434 WHERE
2435 HIER.MULTI_VALUE_SET_FLAG = ''Y'' AND
2436 HIER.DIMENSION_ID = :dimension_id AND
2437 HIER.PERSONAL_FLAG = ''N'' AND
2438 CAT.OBJECT_ID = HIER.HIERARCHY_OBJ_ID AND
2439 DEF.OBJECT_ID = CAT.OBJECT_ID AND
2440 H.HIERARCHY_OBJ_DEF_ID = DEF.OBJECT_DEFINITION_ID AND ' ||
2441 v_vs_where_root || '
2442 H.PARENT_ID = H.CHILD_ID AND H.PARENT_DEPTH_NUM = 1'
2443 BULK COLLECT INTO
2444 v_object_name_array,
2445 v_object_id_array
2446 USING v_dimension_id;
2447
2448 v_multi_vs_num := SQL%ROWCOUNT;
2449
2450 IF v_multi_vs_num > 0 THEN
2451 FEM_ENGINES_PKG.Tech_Message(
2452 p_severity => pc_log_level_statement,
2453 p_module => v_module || '.multi_vs',
2454 p_app_name => 'FEM',
2455 p_msg_name => 'FEM_DIS_HIER_MULTI_VALUE_SETS',
2456 p_token1 => 'DIMENSION',
2457 p_value1 => v_dimension_name
2458 );
2459
2460 FEM_ENGINES_PKG.User_Message(
2461 p_app_name => 'FEM',
2462 p_msg_name => 'FEM_DIS_HIER_MULTI_VALUE_SETS',
2463 p_token1 => 'DIMENSION',
2464 p_value1 => v_dimension_name
2465 );
2466
2467 FOR i IN 1..v_multi_vs_num LOOP
2468 FEM_ENGINES_PKG.Tech_Message(
2469 p_severity => pc_log_level_statement,
2470 p_module => v_module || '.multi_vs',
2471 p_msg_text => ' ' ||
2472 v_object_name_array(i) || '(' ||
2473 v_object_id_array(i) || ')'
2474 );
2475
2476 FEM_ENGINES_PKG.User_Message(
2477 p_msg_text => ' ' ||
2478 v_object_name_array(i) || '(' ||
2479 v_object_id_array(i) || ')'
2480 );
2481 END LOOP;
2482
2483 END IF;
2484
2485 FEM_ENGINES_PKG.Tech_Message(
2486 p_severity => pc_log_level_statement,
2487 p_module => v_module || '.rowcount',
2488 p_msg_text => 'v_deleted_b=' || v_deleted_b || ', ' ||
2489 'v_inserted_b1=' || v_inserted_b1 || ', ' ||
2490 'v_inserted_b2=' || v_inserted_b2 || ', ' ||
2491 'v_deleted_tl=' || v_deleted_tl || ', ' ||
2492 'v_inserted_tl=' || v_inserted_tl
2493 );
2494
2495 FEM_ENGINES_PKG.Tech_Message(
2496 p_severity => pc_log_level_procedure,
2497 p_module => v_module || '.end',
2498 p_app_name => 'FEM',
2499 p_msg_name => 'FEM_GL_POST_202',
2500 p_token1 => 'FUNC_NAME',
2501 p_value1 => v_func_name,
2502 p_token2 => 'TIME',
2503 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
2504 );
2505
2506 FEM_ENGINES_PKG.User_Message(
2507 p_app_name => 'FEM',
2508 p_msg_name => 'FEM_GL_POST_202',
2509 p_token1 => 'FUNC_NAME',
2510 p_value1 => v_func_name,
2511 p_token2 => 'TIME',
2512 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
2513 );
2514
2515 RETURN('NORMAL');
2516
2517 EXCEPTION
2518 WHEN OTHERS THEN
2519 ROLLBACK;
2520
2521 FEM_ENGINES_PKG.Tech_Message(
2522 p_severity => pc_log_level_exception,
2523 p_module => v_module || '.others',
2524 p_app_name => 'FEM',
2525 p_msg_name => 'FEM_GL_POST_215',
2526 p_token1 => 'ERR_MSG',
2527 p_value1 => SQLERRM
2528 );
2529
2530 FEM_ENGINES_PKG.User_Message(
2531 p_app_name => 'FEM',
2532 p_msg_name => 'FEM_GL_POST_215',
2533 p_token1 => 'ERR_MSG',
2534 p_value1 => SQLERRM
2535 );
2536
2537 v_insert_b_column_list_l := LENGTH(v_insert_b_column_list_l);
2538 v_insert_b_display_code1_l := LENGTH(v_insert_b_display_code1_l);
2539 v_insert_b_display_code2_l := LENGTH(v_insert_b_display_code2_l);
2540 v_insert_b_with_p1_l := LENGTH(v_insert_b_with_p1_l);
2541 v_insert_b_with_p2_l := LENGTH(v_insert_b_with_p2_l);
2545 v_insert_b_subquery_h11_o_l := LENGTH(v_insert_b_subquery_h11_o_l);
2542 v_insert_b_with_p3_l := LENGTH(v_insert_b_with_p3_l);
2543 v_insert_b_subquery_r1_l := LENGTH(v_insert_b_subquery_r1_l);
2544 v_insert_b_subquery_r2_l := LENGTH(v_insert_b_subquery_r2_l);
2546 v_insert_b_subquery_h11_l := LENGTH(v_insert_b_subquery_h11_l);
2547 v_insert_b_subquery_h12_l := LENGTH(v_insert_b_subquery_h12_l);
2548 v_insert_b_exclude_nonleaf_o_l := LENGTH(v_insert_b_exclude_nonleaf_o_l);
2549 v_insert_b_exclude_nonleaf_l := LENGTH(v_insert_b_exclude_nonleaf_l);
2550 v_insert_b_subquery_q1_l := LENGTH(v_insert_b_subquery_q1_l);
2551 v_insert_b_subquery_q2_l := LENGTH(v_insert_b_subquery_q2_l);
2552
2553 v_insert_tl_column_list_l := LENGTH(v_insert_tl_column_list);
2554 v_insert_tl_name1_l := LENGTH(v_insert_tl_name1);
2555 v_insert_tl_name2_l := LENGTH(v_insert_tl_name2);
2556 v_insert_tl_description1_l := LENGTH(v_insert_tl_description1);
2557 v_insert_tl_description2_l := LENGTH(v_insert_tl_description2);
2558
2559 FEM_ENGINES_PKG.Tech_Message(
2560 p_severity => pc_log_level_exception,
2561 p_module => v_module || '.others',
2562 p_msg_text => 'v_insert_b_column_list_l='|| v_insert_b_column_list_l || ', ' ||
2563 'v_insert_b_display_code1_l='|| v_insert_b_display_code1_l || ', ' ||
2564 'v_insert_b_display_code2_l='|| v_insert_b_display_code2_l || ', ' ||
2565 'v_insert_b_with_p1_l='|| v_insert_b_with_p1_l || ', ' ||
2566 'v_insert_b_with_p2_l='|| v_insert_b_with_p2_l || ', ' ||
2567 'v_insert_b_with_p3_l='|| v_insert_b_with_p3_l || ', ' ||
2568 'v_insert_b_subquery_r1_l='|| v_insert_b_subquery_r1_l || ', ' ||
2569 'v_insert_b_subquery_r2_l='|| v_insert_b_subquery_r2_l || ', ' ||
2570 'v_insert_b_subquery_h11_o_l='|| v_insert_b_subquery_h11_o_l || ', ' ||
2571 'v_insert_b_subquery_h11_l='|| v_insert_b_subquery_h11_l || ', ' ||
2572 'v_insert_b_subquery_h12_l='|| v_insert_b_subquery_h12_l || ', ' ||
2573 'v_insert_b_exclude_nonleaf_o_l='|| v_insert_b_exclude_nonleaf_o_l || ', ' ||
2574 'v_insert_b_exclude_nonleaf_l='|| v_insert_b_exclude_nonleaf_l || ', ' ||
2575 'v_insert_b_subquery_q1_l='|| v_insert_b_subquery_q1_l || ', ' ||
2576 'v_insert_b_subquery_q2_l='|| v_insert_b_subquery_q2_l || ', ' ||
2577 'v_insert_tl_column_list_l=' ||v_insert_tl_column_list_l ||', ' ||
2578 'v_insert_tl_name1_l=' ||v_insert_tl_name1_l ||', ' ||
2579 'v_insert_tl_name2_l=' ||v_insert_tl_name2_l ||', ' ||
2580 'v_insert_tl_description1_l=' ||v_insert_tl_description1_l ||', ' ||
2581 'v_insert_tl_description2_l=' ||v_insert_tl_description2_l
2582 );
2583
2584
2585 FEM_ENGINES_PKG.Tech_Message(
2586 p_severity => pc_log_level_exception,
2587 p_module => v_module || '.others',
2588 p_app_name => 'FEM',
2589 p_msg_name => 'FEM_GL_POST_203',
2590 p_token1 => 'FUNC_NAME',
2591 p_value1 => v_func_name,
2592 p_token2 => 'TIME',
2593 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
2594 );
2595
2596 FEM_ENGINES_PKG.User_Message(
2597 p_app_name => 'FEM',
2598 p_msg_name => 'FEM_GL_POST_203',
2599 p_token1 => 'FUNC_NAME',
2600 p_value1 => v_func_name,
2601 p_token2 => 'TIME',
2602 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
2603 );
2604
2605 RETURN('ERROR');
2606
2607 END Transformation;
2608
2609
2610 -- ======================================================================
2611 -- Procedure
2612 -- Run_Transformation
2613 -- Purpose
2614 -- Runs Hierarchy Transformation for all supported dimensions
2615 -- History
2616 -- 06-22-05 Shintaro Okuda Created
2617 -- Arguments
2618 -- x_errbuf Standard Concurrent Program parameter
2619 -- x_retcode Standard Concurrent Program parameter
2620 -- p_dimension_varchar_label Dimension Varchar Label
2621 -- ======================================================================
2622 PROCEDURE Run_Transformation(
2623 x_errbuf OUT NOCOPY VARCHAR2,
2624 x_retcode OUT NOCOPY VARCHAR2,
2625 p_dimension_varchar_label IN VARCHAR2
2626 ) IS
2627
2628 CURSOR CurDim IS
2629 SELECT *
2630 FROM FEM_DIS_DIMENSIONS_V
2631 WHERE DIMENSION_VARCHAR_LABEL <> 'ALL';
2632
2633 CURSOR CurReq(p_parent_request_id NUMBER) IS
2634 SELECT *
2635 FROM FND_CONCURRENT_REQUESTS
2636 WHERE PARENT_REQUEST_ID = p_parent_request_id;
2637
2638 TYPE DisDims IS TABLE OF FEM_DIS_DIMENSIONS_V%ROWTYPE;
2639 TYPE ChildReqs IS TABLE OF FND_CONCURRENT_REQUESTS%ROWTYPE;
2640
2641 v_dis_dims DisDims;
2642 v_child_requests ChildReqs;
2643
2644 v_request_data VARCHAR2(100);
2645
2646 v_child_request_id NUMBER;
2647
2648 v_dimension_name VARCHAR2(80);
2649
2650 v_phase VARCHAR2(100);
2651 v_status VARCHAR2(100);
2652 v_dev_phase VARCHAR2(100);
2656 v_dummy_number NUMBER;
2653 v_dev_status VARCHAR2(100);
2654 v_message VARCHAR2(500);
2655
2657 v_dummy_boolean BOOLEAN;
2658
2659 v_completion_status VARCHAR2(30);
2660
2661 v_warnings NUMBER := 0;
2662 v_errors NUMBER := 0;
2663
2664 v_module VARCHAR2(100) := 'fem.plsql.fem_dis_hier_pkg.run_transformation';
2665 v_func_name VARCHAR2(100) := 'FEM_DIS_HIER_PKG.Run_Transformation';
2666
2667 HIER_TRANS_INVALID_DIMENSION EXCEPTION;
2668 HIER_TRANS_CHILD_SUB_FAILED EXCEPTION;
2669
2670 BEGIN
2671
2672 FEM_ENGINES_PKG.Tech_Message(
2673 p_severity => pc_log_level_procedure,
2674 p_module => v_module || '.begin',
2675 p_app_name => 'FEM',
2676 p_msg_name => 'FEM_GL_POST_201',
2677 p_token1 => 'FUNC_NAME',
2678 p_value1 => v_func_name,
2679 p_token2 => 'TIME',
2680 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
2681 );
2682
2683 FEM_ENGINES_PKG.User_Message(
2684 p_app_name => 'FEM',
2685 p_msg_name => 'FEM_GL_POST_201',
2686 p_token1 => 'FUNC_NAME',
2687 p_value1 => v_func_name,
2688 p_token2 => 'TIME',
2689 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
2690 );
2691
2692 --
2693 -- Initialize package variables
2694 --
2695 pv_req_id := NVL(FND_GLOBAL.Conc_Request_ID,-1);
2696 pv_user_id := NVL(FND_GLOBAL.User_ID,'-1');
2697 pv_login_id := NVL(FND_GLOBAL.Conc_Login_ID, FND_GLOBAL.Login_ID);
2698
2699 --
2700 -- Validate input parameter
2701 --
2702 BEGIN
2703 SELECT 1 INTO v_dummy_number
2704 FROM FEM_DIS_DIMENSIONS_V
2705 WHERE DIMENSION_VARCHAR_LABEL = p_dimension_varchar_label;
2706 EXCEPTION
2707 WHEN NO_DATA_FOUND THEN
2708 RAISE HIER_TRANS_INVALID_DIMENSION;
2709 WHEN OTHERS THEN
2710 RAISE;
2711 END;
2712
2713
2714 IF p_dimension_varchar_label <> 'ALL' THEN
2715
2716 /***********************************************************************
2717 Individual execution does not use FND_CONC_GLOBAL
2718 ************************************************************************/
2719
2720 -- Run Transformation
2721 v_completion_status :=
2722 Transformation(p_dimension_varchar_label => p_dimension_varchar_label);
2723
2724 ELSE
2725
2726 /***********************************************************************
2727 Batch execution uses FND_CONC_GLOBAL
2728
2729 Read the value from REQUEST_DATA. If this is the first run of
2730 the program, then this value will be NULL. Thus, submitting
2731 child requests. Otherwise,the program is reawaken and REQUEST_DATA
2732 will be the value that we passed to SET_REQ_GLOBALS on the previous
2733 run.
2734
2735 References for PL/SQL Concurrent Processing Recursive Calls
2736 -----------------------------------------------------------
2737 1. Chapter 21: PL/SQL APIs for Concurrent Processing,
2738 Oracle Applications Developers Guide,
2739 2. Note 221542.1: Sample Code for FND_SUBMIT and FND_REQUEST API's
2740 3. WSHDDSHB.pls
2741 4. cefcshfb.pls
2742 ************************************************************************/
2743
2744 v_request_data := FND_CONC_GLOBAL.Request_Data;
2745
2746 IF v_request_data IS NULL THEN
2747
2748 /**********************************************************************
2749 Parent is initiated
2750 **********************************************************************/
2751 -- Get Dimension information
2752 OPEN CurDim;
2753 FETCH CurDim BULK COLLECT INTO v_dis_dims;
2754 CLOSE CUrDIm;
2755
2756 -- Run transformation for each dimension using a child process
2757 FOR i IN 1..v_dis_dims.LAST LOOP
2758
2759 v_child_request_id :=
2760 FND_REQUEST.Submit_Request(
2761 application => 'FEM',
2762 program => 'FEM_DIS_HIER_TRANS',
2763 description => v_dis_dims(i).dimension_name,
2764 start_time => NULL,
2765 sub_request => TRUE,
2766 argument1 => v_dis_dims(i).dimension_varchar_label
2767 );
2768
2769 IF v_child_request_id = 0 THEN
2770
2771 -- If a request submission is failed, raise an exception
2772
2773 v_dimension_name := v_dis_dims(i).dimension_name;
2774
2775 x_errbuf := FND_MESSAGE.Get;
2776
2777 RAISE HIER_TRANS_CHILD_SUB_FAILED;
2778
2779 ELSE
2780
2781 FEM_ENGINES_PKG.User_Message(
2782 p_app_name => 'FEM',
2783 p_msg_name => 'FEM_DIS_HIER_REQ_SUB_SUCCESS',
2784 p_token1 => 'DIMENSION',
2785 p_value1 => v_dis_dims(i).dimension_name,
2786 p_token2 => 'REQ_ID',
2787 p_value2 => TO_CHAR(v_child_request_id)
2788 );
2789
2790 FEM_ENGINES_PKG.Tech_Message(
2791 p_severity => pc_log_level_statement,
2792 p_module => v_module || '.child_req_submission',
2793 p_app_name => 'FEM',
2797 p_token2 => 'REQ_ID',
2794 p_msg_name => 'FEM_DIS_HIER_REQ_SUB_SUCCESS',
2795 p_token1 => 'DIMENSION',
2796 p_value1 => v_dis_dims(i).dimension_name,
2798 p_value2 => TO_CHAR(v_child_request_id)
2799 );
2800
2801 END IF;
2802
2803 END LOOP;
2804
2805 --
2806 -- Put the program into the PAUSED status and indicate the end of
2807 -- initial execution
2808 --
2809 FND_CONC_GLOBAL.Set_Req_Globals(
2810 conc_status => 'PAUSED',
2811 request_data => 'SUBMITTED'
2812 );
2813
2814 v_completion_status := 'NORMAL';
2815
2816 ELSE -- IF v_request_data IS NULL THEN
2817
2818 /**********************************************************************
2819 Parent is reawaken
2820 **********************************************************************/
2821 -- Get child process ids
2822 OPEN CurReq(pv_req_id);
2823 FETCH CurReq BULK COLLECT INTO v_child_requests;
2824 CLOSE CurReq;
2825
2826 FOR i IN 1..v_child_requests.LAST LOOP
2827
2828 v_status := NULL;
2829 v_dev_status := NULL;
2830
2831 v_dummy_boolean :=
2832 FND_CONCURRENT.Get_Request_Status(
2833 request_id => v_child_requests(i).request_id,
2834 phase => v_phase,
2835 status => v_status,
2836 dev_phase => v_dev_phase,
2837 dev_status => v_dev_status,
2838 message => v_message
2839 );
2840
2841 IF v_dev_status = 'WARNING' THEN
2842 v_warnings:= v_warnings + 1;
2843 ELSIF v_dev_status <> 'NORMAL' THEN
2844 v_errors := v_errors + 1;
2845 END IF;
2846
2847 FEM_ENGINES_PKG.Tech_Message(
2848 p_severity => pc_log_level_statement,
2849 p_module => v_module || 'child_req_status',
2850 p_app_name => 'FEM',
2851 p_msg_name => 'FEM_DIS_HIER_REQ_STATUS',
2852 p_token1 => 'DIMENSION',
2853 p_value1 => v_child_requests(i).description,
2854 p_token2 => 'REQ_ID',
2855 p_value2 => TO_CHAR(v_child_requests(i).request_id),
2856 p_token3 => 'STATUS',
2857 p_value3 => v_status
2858 );
2859
2860 FEM_ENGINES_PKG.User_Message(
2861 p_app_name => 'FEM',
2862 p_msg_name => 'FEM_DIS_HIER_REQ_STATUS',
2863 p_token1 => 'DIMENSION',
2864 p_value1 => v_child_requests(i).description,
2865 p_token2 => 'REQ_ID',
2866 p_value2 => TO_CHAR(v_child_requests(i).request_id),
2867 p_token3 => 'STATUS',
2868 p_value3 => v_status
2869 );
2870
2871 END LOOP;
2872
2873 IF v_errors = 0 AND v_warnings = 0 THEN
2874 -- If all dimensions transformations are successful
2875 v_completion_status := 'NORMAL';
2876
2877 ELSIF (v_errors > 0) AND (v_errors = v_child_requests.count) THEN
2878 -- If all dimensions transformations are failed
2879 v_completion_status := 'ERROR';
2880
2881 ELSE
2882 -- If some dimensions transformations are successful
2883 v_completion_status := 'WARNING';
2884
2885 END IF;
2886
2887 END IF; -- IF v_request_data IS NULL THEN
2888
2889 END IF; -- IF p_dimension_varchar_label = 'ALL' THEN
2890
2891 --
2892 -- Set request completion status
2893 --
2894 IF v_completion_status = 'NORMAL' THEN
2895 x_retcode := '0';
2896
2897 FEM_ENGINES_PKG.Tech_Message(
2898 p_severity => pc_log_level_procedure,
2899 p_module => v_module || '.end',
2900 p_app_name => 'FEM',
2901 p_msg_name => 'FEM_GL_POST_202',
2902 p_token1 => 'FUNC_NAME',
2903 p_value1 => v_func_name,
2904 p_token2 => 'TIME',
2905 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
2906 );
2907
2908 FEM_ENGINES_PKG.User_Message(
2909 p_app_name => 'FEM',
2910 p_msg_name => 'FEM_GL_POST_202',
2911 p_token1 => 'FUNC_NAME',
2912 p_value1 => v_func_name,
2913 p_token2 => 'TIME',
2914 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
2915 );
2916
2917 ELSIF v_completion_status = 'WARNING' THEN
2918 x_retcode := '1';
2919
2920 FEM_ENGINES_PKG.Tech_Message(
2921 p_severity => pc_log_level_procedure,
2922 p_module => v_module || '.end',
2923 p_app_name => 'FEM',
2924 p_msg_name => 'FEM_GL_POST_206'
2925 );
2926
2927 FEM_ENGINES_PKG.User_Message(
2928 p_app_name => 'FEM',
2929 p_msg_name => 'FEM_GL_POST_206'
2930 );
2931
2932 ELSE
2933 x_retcode := '2';
2934
2935 FEM_ENGINES_PKG.Tech_Message(
2936 p_severity => pc_log_level_procedure,
2937 p_module => v_module || '.end',
2938 p_app_name => 'FEM',
2939 p_msg_name => 'FEM_GL_POST_203',
2940 p_token1 => 'FUNC_NAME',
2941 p_value1 => v_func_name,
2942 p_token2 => 'TIME',
2943 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
2944 );
2945
2946 FEM_ENGINES_PKG.User_Message(
2947 p_app_name => 'FEM',
2948 p_msg_name => 'FEM_GL_POST_203',
2949 p_token1 => 'FUNC_NAME',
2950 p_value1 => v_func_name,
2951 p_token2 => 'TIME',
2952 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
2953 );
2954
2955 END IF;
2956
2957 EXCEPTION
2958 WHEN HIER_TRANS_INVALID_DIMENSION THEN
2959 ROLLBACK;
2960
2961 x_retcode := '2';
2962
2963 FEM_ENGINES_PKG.Tech_Message(
2964 p_severity => pc_log_level_exception,
2965 p_module => v_module || '.invalid_dimension',
2966 p_app_name => 'FEM',
2967 p_msg_name => 'FEM_DIS_HIER_INVALID_DIMENSION',
2968 p_token1 => 'DIMENSION',
2969 p_value1 => p_dimension_varchar_label
2970 );
2971
2972 FEM_ENGINES_PKG.User_Message(
2973 p_app_name => 'FEM',
2974 p_msg_name => 'FEM_DIS_HIER_INVALID_DIMENSION',
2975 p_token1 => 'DIMENSION',
2976 p_value1 => p_dimension_varchar_label
2977 );
2978
2979 FEM_ENGINES_PKG.Tech_Message(
2980 p_severity => pc_log_level_exception,
2981 p_module => v_module || '.invalid_dimension',
2982 p_app_name => 'FEM',
2983 p_msg_name => 'FEM_GL_POST_203',
2984 p_token1 => 'FUNC_NAME',
2985 p_value1 => v_func_name,
2986 p_token2 => 'TIME',
2987 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
2988 );
2989
2990 FEM_ENGINES_PKG.User_Message(
2991 p_app_name => 'FEM',
2992 p_msg_name => 'FEM_GL_POST_203',
2993 p_token1 => 'FUNC_NAME',
2994 p_value1 => v_func_name,
2995 p_token2 => 'TIME',
2996 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
2997 );
2998
2999 WHEN HIER_TRANS_CHILD_SUB_FAILED THEN
3000 ROLLBACK;
3001
3002 x_retcode := '2';
3003
3004 FEM_ENGINES_PKG.Tech_Message(
3005 p_severity => pc_log_level_exception,
3006 p_module => v_module || '.sub_failed',
3007 p_app_name => 'FEM',
3008 p_msg_name => 'FEM_DIS_HIER_REQ_SUB_FAILURE',
3009 p_token1 => 'DIMENSION',
3010 p_value1 => v_dimension_name
3011 );
3012
3013 FEM_ENGINES_PKG.User_Message(
3014 p_app_name => 'FEM',
3015 p_msg_name => 'FEM_DIS_HIER_REQ_SUB_FAILURE',
3016 p_token1 => 'DIMENSION',
3017 p_value1 => v_dimension_name
3018 );
3019
3020 FEM_ENGINES_PKG.Tech_Message(
3021 p_severity => pc_log_level_exception,
3022 p_module => v_module || '.sub_failed',
3023 p_app_name => 'FEM',
3024 p_msg_name => 'FEM_GL_POST_203',
3025 p_token1 => 'FUNC_NAME',
3026 p_value1 => v_func_name,
3027 p_token2 => 'TIME',
3028 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
3029 );
3030
3031 FEM_ENGINES_PKG.User_Message(
3032 p_app_name => 'FEM',
3033 p_msg_name => 'FEM_GL_POST_203',
3034 p_token1 => 'FUNC_NAME',
3035 p_value1 => v_func_name,
3036 p_token2 => 'TIME',
3037 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
3038 );
3039
3040 WHEN OTHERS THEN
3041 ROLLBACK;
3042
3043 x_retcode := '2';
3044
3045 FEM_ENGINES_PKG.Tech_Message(
3046 p_severity => pc_log_level_exception,
3047 p_module => v_module || '.others',
3048 p_app_name => 'FEM',
3049 p_msg_name => 'FEM_GL_POST_215',
3050 p_token1 => 'ERR_MSG',
3051 p_value1 => SQLERRM
3052 );
3053
3054 FEM_ENGINES_PKG.User_Message(
3055 p_app_name => 'FEM',
3056 p_msg_name => 'FEM_GL_POST_215',
3057 p_token1 => 'ERR_MSG',
3058 p_value1 => SQLERRM
3059 );
3060
3061 FEM_ENGINES_PKG.Tech_Message(
3062 p_severity => pc_log_level_exception,
3063 p_module => v_module || '.others',
3064 p_app_name => 'FEM',
3065 p_msg_name => 'FEM_GL_POST_203',
3066 p_token1 => 'FUNC_NAME',
3067 p_value1 => v_func_name,
3068 p_token2 => 'TIME',
3069 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
3070 );
3071
3072 FEM_ENGINES_PKG.User_Message(
3073 p_app_name => 'FEM',
3074 p_msg_name => 'FEM_GL_POST_203',
3075 p_token1 => 'FUNC_NAME',
3076 p_value1 => v_func_name,
3077 p_token2 => 'TIME',
3078 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
3079 );
3080
3081 END Run_Transformation;
3082
3083 END FEM_DIS_HIER_PKG;