[Home] [Help]
PACKAGE BODY: APPS.FEM_INTG_HIER_RULE_ENG_PKG
Source
1 PACKAGE BODY FEM_INTG_HIER_RULE_ENG_PKG AS
2 /* $Header: fem_intg_hir_eng.plb 120.25 2008/04/01 06:57:34 rguerrer ship $ */
3 /***********************************************************************
4 * PACKAGE VARIABLES *
5 ***********************************************************************/
6 pc_log_level_statement CONSTANT NUMBER := FND_LOG.level_statement;
7 pc_log_level_procedure CONSTANT NUMBER := FND_LOG.level_procedure;
8 pc_log_level_event CONSTANT NUMBER := FND_LOG.level_event;
9 pc_log_level_error CONSTANT NUMBER := FND_LOG.level_error;
10 pc_log_level_unexpected CONSTANT NUMBER := FND_LOG.level_unexpected;
11 pc_api_version CONSTANT NUMBER := 1.0;
12 pv_req_id CONSTANT NUMBER := FND_GLOBAL.Conc_Request_Id;
13 pv_user_id CONSTANT NUMBER := FND_GLOBAL.User_Id;
14 pv_login_id CONSTANT NUMBER := FND_GLOBAL.Login_Id;
15 pc_max_disp_len constant number := 15;
16 pc_success constant number := 0;
17 pc_failure constant number := 2;
18 v_new_hier_obj_def_created BOOLEAN := FALSE;
19 pv_new_hier_obj_created BOOLEAN := FALSE;
20 pv_hier_obj_id NUMBER;
21 pv_hier_rule_obj_name VARCHAR2(150);
22 pv_folder_id NUMBER;
23 pv_hier_rule_start_date DATE;
24 pv_hier_rule_end_date DATE;
25 pv_dim_mapping_option_code VARCHAR2(30);
26 v_req_id NUMBER;
27 pv_flatten_hier_flag VARCHAR2(1);
28 pv_sequence_enforced_flag VARCHAR2(1);
29 pv_grp_seq_code VARCHAR2(30);
30 pv_top_dimension_group_id NUMBER;
31 v_dim_group_seq NUMBER;
32 /***********************************************************************
33 * PRIVATE FUNCTIONS *
34 ***********************************************************************/
35 -- ======================================================================
36 -- Procedure
37 -- Init
38 -- Purpose
39 -- This routine will initailize the package variables.
40 -- History
41 -- 10-28-04 Jee Kim Created
42 -- 10-20-05 A.Budnik Modification for MULTISEG case.
43 -- Arguments
44 -- p_hier_rule_obj_def_id The hierarchy rule version to be processed
45 -- x_completion_code Completion status of the routine
46 -- ======================================================================
47 PROCEDURE Init (p_hier_rule_obj_def_id IN NUMBER,
48 x_completion_code OUT NOCOPY NUMBER) IS
49 -- Added items below to support the Mulit Segment Hierarchy case ****
50 v_Num_hiers NUMBER;
51 v_aol_vs_id1 NUMBER;
52 v_aol_vs_id2 NUMBER;
53 v_aol_vs_id3 NUMBER;
54 v_aol_vs_id4 NUMBER;
55 v_aol_vs_id5 NUMBER;
56 v_app_col_name1 varchar2(12);
57 v_app_col_name2 varchar2(12);
58 v_app_col_name3 varchar2(12);
59 v_app_col_name4 varchar2(12);
60 v_app_col_name5 varchar2(12);
61 -- defined for the traversal array
62 CURSOR c_traversal_info is
63 SELECT display_order_num,
64 application_column_name,
65 top_parent_value
66 FROM fem_intg_hier_def_segs
67 WHERE hier_rule_obj_def_id = pv_hier_rule_obj_def_id
68 ORDER BY display_order_num;
69 v_traversal_info c_traversal_info%ROWTYPE;
70 l_rec r_hier_traversal;
71 FEM_INTG_fatal_err EXCEPTION;
72 BEGIN
73 FEM_ENGINES_PKG.Tech_Message
74 (p_severity => pc_log_level_procedure,
75 p_module => 'fem.plsql.fem_intg.hier_eng.Init',
76 p_app_name => 'FEM',
77 p_msg_name => 'FEM_GL_POST_201',
78 p_token1 => 'FUNC_NAME',
79 p_value1 => 'FEM_INTG_HIER_RULE_ENG_PKG.Init',
80 p_token2 => 'TIME',
81 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
82 -- Obtain hierarchy object ID and the latest hierarchy object
83 -- definition ID associated with the hierarchy rule definition
84 BEGIN
85 SELECT nvl(h.hierarchy_obj_id, -1), o.object_name
86 INTO pv_hier_obj_id,
87 pv_hier_rule_obj_name
88 FROM fem_object_definition_b b,
89 fem_object_catalog_vl o,
90 fem_intg_hier_rules h
91 WHERE b.object_definition_id = pv_hier_rule_obj_def_id
92 AND b.object_id = o.object_id
93 AND o.object_id = h.hier_rule_obj_id;
94 EXCEPTION
95 WHEN NO_DATA_FOUND THEN
96 pv_hier_obj_id := -1;
97 END;
98 -- Get hier_obj_def_id if (pv_hier_obj_id <> -1)
99 IF (pv_hier_obj_id <> -1) THEN
100 BEGIN
101 SELECT nvl(hier_obj_def_id,-1)
102 INTO pv_hier_obj_def_id
103 FROM fem_intg_hier_def_segs
104 WHERE hier_rule_obj_def_id = pv_hier_rule_obj_def_id
105 AND display_order_num = 1;
106 EXCEPTION
107 WHEN NO_DATA_FOUND THEN
108 pv_hier_obj_def_id := -1;
109 END;
110 END IF;
111 FEM_ENGINES_PKG.Tech_Message
112 (p_severity => pc_log_level_statement,
113 p_module => 'fem.plsql.fem_intg_hier_rule_eng_pkg',
114 p_msg_text => 'pv_hier_obj_id:' || pv_hier_obj_id
115 ||' pv_hier_rule_obj_name:'||pv_hier_rule_obj_name
116 ||' pv_hier_obj_def_id:'||pv_hier_obj_def_id);
117 -- Initialize dimension rule related information just for
118 -- pv_dim_mapping_option_code. Must be available to do MUlit segment
119 -- case below.
120 BEGIN
121 SELECT DIM_MAPPING_OPTION_CODE
122 INTO pv_dim_mapping_option_code
123 FROM fem_intg_hier_rules h,
124 fem_intg_dim_rule_defs d
125 WHERE HIER_RULE_OBJ_ID = pv_hier_rule_obj_id
126 AND h.DIM_RULE_OBJ_DEF_ID = d.DIM_RULE_OBJ_DEF_ID;
127 EXCEPTION
128 WHEN NO_DATA_FOUND THEN
129 RAISE FEM_INTG_fatal_err;
130 END;
131 FEM_ENGINES_PKG.Tech_Message
132 (p_severity => pc_log_level_statement,
133 p_module => 'fem.plsql.fem_intg_hier_rule_eng_pkg.get_mapping_option',
134 p_msg_text => 'pv_dim_mapping_option_code:'||pv_dim_mapping_option_code);
135 BEGIN
136 CASE
137 pv_dim_mapping_option_code
138 WHEN 'SINGLESEG' THEN
139 SELECT dr.chart_of_accounts_id,
140 dr.dimension_id,
141 drf.fem_value_set_id,
142 seg.top_parent_value,
143 v.flex_value_id,
144 d.member_vl_object_name,
145 d.member_tl_table_name,
146 d.member_b_table_name,
147 d.member_col,
148 d.member_display_code_col,
149 d.member_name_col,
150 d.member_description_col,
151 d.hierarchy_table_name,
152 d.attribute_table_name,
153 drf.segment_count,
154 hir.dim_rule_obj_def_id,
155 dr.dim_rule_obj_id,
156 v.flex_value_set_id,
157 nvl(hir.flatten_hier_flag,'N'),
158 nvl(hir.sequence_enforced_flag,'N')
159 INTO pv_coa_id,
160 pv_dim_id,
161 pv_dim_vs_id,
162 pv_top_parent_disp_code,
163 pv_top_parent_id,
164 pv_dim_memb_vl_obj,
165 pv_dim_memb_tl_tab,
166 pv_dim_memb_b_tab,
167 pv_dim_memb_col,
168 pv_dim_memb_disp_col,
169 pv_dim_memb_name_col,
170 pv_dim_memb_desc_col,
171 pv_dim_hier_tab,
172 pv_dim_attr_tab,
173 pv_segment_count,
174 pv_dim_rule_obj_def_id,
175 pv_dim_rule_obj_id,
176 pv_aol_vs_id,
177 pv_flatten_hier_flag,
178 pv_sequence_enforced_flag
179 FROM fem_intg_hier_rules hir,
180 fem_object_definition_b b1,
181 fem_intg_dim_rules dr,
182 fem_intg_dim_rule_defs drf,
183 fem_intg_hier_def_segs seg,
184 fnd_flex_values v,
185 fem_xdim_dimensions d,
186 fem_intg_aol_valset_map m
187 WHERE hir.hier_rule_obj_id = pv_hier_rule_obj_id
188 AND b1.object_definition_id = hir.dim_rule_obj_def_id
189 AND dr.dim_rule_obj_id = b1.object_id
190 AND drf.dim_rule_obj_def_id = hir.dim_rule_obj_def_id
191 AND seg.hier_rule_obj_def_id = pv_hier_rule_obj_def_id
192 AND drf.fem_value_set_id = m.fem_value_set_id
193 AND v.flex_value_set_id = m.segment1_value_set_id
194 AND v.flex_value = seg.top_parent_value
195 AND d.dimension_id = dr.dimension_id;
196 -- 11AUG05 For multi segment case *****************
197 WHEN 'MULTISEG' THEN
198 SELECT distinct dr.chart_of_accounts_id,
199 dr.dimension_id,
200 drf.fem_value_set_id,
201 d.member_vl_object_name,
202 d.member_tl_table_name,
203 d.member_b_table_name,
204 d.member_col,
205 d.member_display_code_col,
206 d.member_name_col,
207 d.member_description_col,
208 d.hierarchy_table_name,
209 d.attribute_table_name,
210 drf.segment_count,
211 hir.dim_rule_obj_def_id,
212 dr.dim_rule_obj_id,
213 m.segment1_value_set_id,
214 m.segment2_value_set_id,
215 m.segment3_value_set_id,
216 m.segment4_value_set_id,
217 m.segment5_value_set_id,
218 nvl(hir.flatten_hier_flag,'N'),
219 nvl(hir.sequence_enforced_flag,'N'),
220 drf.application_column_name1,
221 drf.application_column_name2,
222 drf.application_column_name3,
223 drf.application_column_name4,
224 drf.application_column_name5
225 INTO pv_coa_id,
226 pv_dim_id,
227 pv_dim_vs_id,
228 pv_dim_memb_vl_obj,
229 pv_dim_memb_tl_tab,
230 pv_dim_memb_b_tab,
231 pv_dim_memb_col,
232 pv_dim_memb_disp_col,
233 pv_dim_memb_name_col,
234 pv_dim_memb_desc_col,
235 pv_dim_hier_tab,
236 pv_dim_attr_tab,
237 pv_segment_count,
238 pv_dim_rule_obj_def_id,
239 pv_dim_rule_obj_id,
240 v_aol_vs_id1,
241 v_aol_vs_id2,
242 v_aol_vs_id3,
243 v_aol_vs_id4,
244 v_aol_vs_id5,
245 pv_flatten_hier_flag,
246 pv_sequence_enforced_flag,
247 v_app_col_name1,
248 v_app_col_name2,
249 v_app_col_name3,
250 v_app_col_name4,
251 v_app_col_name5
252 FROM fem_intg_hier_rules hir,
253 fem_object_definition_b b1,
254 fem_intg_dim_rules dr,
255 fem_intg_dim_rule_defs drf,
256 fem_intg_hier_def_segs seg,
257 fnd_flex_values v,
258 fem_xdim_dimensions d,
259 fem_intg_aol_valset_map m
260 WHERE hir.hier_rule_obj_id = pv_hier_rule_obj_id
261 AND b1.object_definition_id = hir.dim_rule_obj_def_id
262 AND dr.dim_rule_obj_id = b1.object_id
263 AND drf.dim_rule_obj_def_id = hir.dim_rule_obj_def_id
264 AND seg.hier_rule_obj_def_id = pv_hier_rule_obj_def_id
265 AND drf.fem_value_set_id = m.fem_value_set_id
266 AND v.flex_value_set_id = m.segment1_value_set_id
267 AND v.flex_value = seg.top_parent_value
268 AND d.dimension_id = dr.dimension_id;
269 if pv_traversal_rarray.count > 0 then
270 pv_traversal_rarray.DELETE;
271 end if;
272 -- Population of pv_traversal_rarray - dispaly_order will overload the fem_intg_dim_hier_gt.hier_obj_def_id
273 -- as ID for temporary component hierarchy
274 FOR v_traversal_info in c_traversal_info LOOP
275 l_rec.display_order := v_traversal_info.display_order_num;
276 l_rec.top_parent_value := v_traversal_info.top_parent_value;
277 -- Using application_column_name to get value set ID
278 SELECT decode(v_traversal_info.application_column_name,
279 v_app_col_name1, v_aol_vs_id1,
280 v_app_col_name2, v_aol_vs_id2,
281 v_app_col_name3, v_aol_vs_id3,
282 v_app_col_name4, v_aol_vs_id4,
283 v_app_col_name5, v_aol_vs_id5,
284 null) INTO l_rec.aol_vs_id FROM DUAL;
285 -- Using application_column_name to get dimension segment concatenation order
286 SELECT decode(v_traversal_info.application_column_name,
287 v_app_col_name1, 1,
288 v_app_col_name2, 2,
289 v_app_col_name3, 3,
290 v_app_col_name4, 4,
291 v_app_col_name5, 5,
292 null) INTO l_rec.concat_segment FROM DUAL;
293 -- Using l_rec.aol_vs_id and l_rec.top_parent_value to get l_rec.top_parent_id
294 select flex_value_id into l_rec.top_parent_id
295 from fnd_flex_values
296 where flex_value_set_id=l_rec.aol_vs_id
297 and flex_value= l_rec.top_parent_value ;
298 pv_traversal_rarray.extend;
299 pv_traversal_rarray(c_traversal_info%ROWCOUNT) := l_rec;
300 v_Num_hiers := c_traversal_info%ROWCOUNT;
301 END LOOP;
302 FEM_ENGINES_PKG.Tech_Message
303 (p_severity => pc_log_level_event,
304 p_module => 'fem.plsql.fem_intg_hier_eng.Init.traversal_rarray_set',
305 p_msg_text => ' v_Num_hiers:' || v_num_hiers ||
306 ' pv_dim_mapping_option_code:' || pv_dim_mapping_option_code,
307 p_token1 => 'TIME',
308 p_value1 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
309 -- Mult seg definition only has one sgement
310 if v_num_hiers < 2 then
311 FEM_ENGINES_PKG.Tech_Message
312 (p_severity => pc_log_level_unexpected,
313 p_module => 'fem.plsql.fem_intg_hier_eng.Init.traversal_rarray_set' ,
314 p_msg_text => 'Cannot initialize MULTISEG option with < two hierarchies.'
315 ||' pv_dim_mapping_option_code:'||pv_dim_mapping_option_code);
316 RAISE FEM_INTG_fatal_err;
317 end if;
318 ELSE
319 -- if no MULTISEG or SINGLESEG case then error
320 FEM_ENGINES_PKG.Tech_Message
321 (p_severity => pc_log_level_unexpected,
322 p_module => 'fem.plsql.fem_intg_hier_eng' ,
323 p_msg_text => 'Cannot initialize dimension rule information'
324 ||' pv_dim_mapping_option_code:'||pv_dim_mapping_option_code);
325 RAISE FEM_INTG_fatal_err;
326 END CASE;
327 EXCEPTION
328 WHEN NO_DATA_FOUND THEN
329 FEM_ENGINES_PKG.Tech_Message
330 (p_severity => pc_log_level_unexpected,
331 p_module => 'fem.plsql.fem_intg_hier_eng' ,
332 p_msg_text => 'Cannot initialize dimension rule information');
333 RAISE FEM_INTG_fatal_err;
334 END;
335 FEM_ENGINES_PKG.Tech_Message
336 (p_severity => pc_log_level_statement,
337 p_module => 'fem.plsql.fem_intg_hier_rule_eng_pkg',
338 p_msg_text => 'pv_coa_id:' || pv_coa_id
339 ||' pv_dim_id:'||pv_dim_id
340 ||' pv_dim_vs_id:'||pv_dim_vs_id
341 ||' pv_aol_vs_id:'||pv_aol_vs_id
342 ||' pv_top_parent_disp_code:'||pv_top_parent_disp_code
343 ||' pv_top_parent_id:'||pv_top_parent_id);
344 FEM_ENGINES_PKG.Tech_Message
345 (p_severity => pc_log_level_statement,
346 p_module => 'fem.plsql.fem_intg_hier_rule_eng_pkg',
347 p_msg_text => 'pv_dim_memb_vl_obj:'||pv_dim_memb_vl_obj
348 ||' pv_dim_memb_tl_tab:'||pv_dim_memb_tl_tab
349 ||' pv_dim_memb_b_tab:'||pv_dim_memb_b_tab
350 ||' pv_dim_memb_col:'||pv_dim_memb_col
351 ||' pv_dim_memb_disp_col:'||pv_dim_memb_disp_col
352 ||' pv_dim_memb_name_col:'||pv_dim_memb_name_col
353 ||' pv_dim_memb_desc_col:'||pv_dim_memb_desc_col);
354 FEM_ENGINES_PKG.Tech_Message
355 (p_severity => pc_log_level_statement,
356 p_module => 'fem.plsql.fem_intg_hier_rule_eng_pkg',
357 p_msg_text =>'pv_dim_hier_tab:'||pv_dim_hier_tab
358 ||' pv_dim_attr_tab:'||pv_dim_attr_tab
359 ||' pv_dim_mapping_option_code:'||pv_dim_mapping_option_code
360 ||' pv_segment_count:'||pv_segment_count
361 ||' pv_dim_rule_obj_def_id:'||pv_dim_rule_obj_def_id
362 ||' pv_dim_rule_obj_id:'||pv_dim_rule_obj_id);
363 FEM_ENGINES_PKG.Tech_Message
364 (p_severity => pc_log_level_statement,
365 p_module => 'fem.plsql.fem_intg_hier_rule_eng_pkg',
366 p_msg_text => 'pv_hier_rule_start_date:' ||pv_hier_rule_start_date
367 ||' pv_hier_rule_end_date:'||pv_hier_rule_end_date);
368 FEM_ENGINES_PKG.Tech_Message
369 (p_severity => pc_log_level_statement,
370 p_module => 'fem.plsql.fem_intg_hier_rule_eng_pkg',
371 p_msg_text => 'pv_flatten_hier_flag:' ||pv_flatten_hier_flag);
372 -- Initialize global value set combo ID
373 BEGIN
374 SELECT global_vs_combo_id
375 INTO pv_gvsc_id
376 FROM fem_intg_coa_gvsc_map
377 WHERE chart_of_accounts_id = pv_coa_id
378 AND effective_start_date <= pv_hier_rule_start_date
379 AND effective_end_date >= pv_hier_rule_end_date;
380 EXCEPTION
381 WHEN NO_DATA_FOUND THEN
382 FEM_ENGINES_PKG.Tech_Message
383 (p_severity => pc_log_level_unexpected,
384 p_module => 'fem.plsql.fem_intg_hier_eng' ,
385 p_msg_text => 'Cannot find Global Value Set Combination');
386 RAISE FEM_INTG_fatal_err;
387 END;
388 FEM_ENGINES_PKG.Tech_Message
389 (p_severity => pc_log_level_statement,
390 p_module => 'fem.plsql.fem_intg_hier_rule_eng_pkg',
391 p_msg_text => 'pv_gvsc_id:'||pv_gvsc_id);
392 -- Initialize the variables requred for FEM_INTG_DIM_RULE_ENG_PKG.Init
393 FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_rule_obj_id := pv_dim_rule_obj_id;
394 FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_rule_obj_def_id := pv_dim_rule_obj_def_id;
395 FEM_INTG_DIM_RULE_ENG_PKG.Init;
396 FEM_ENGINES_PKG.Tech_Message
397 (p_severity => pc_log_level_procedure,
398 p_module => 'fem.plsql.fem_intg_hier_eng.Init.',
399 p_msg_text => ' pv_dim_varchar_label:' || FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label);
400 x_completion_code := 0;
401 FEM_ENGINES_PKG.Tech_Message
402 (p_severity => pc_log_level_procedure,
403 p_module => 'fem.plsql.fem_intg_hier_eng.init.',
404 p_app_name => 'FEM',
405 p_msg_name => 'FEM_GL_POST_202',
406 p_token1 => 'FUNC_NAME',
407 p_value1 => 'FEM_INTG_HIER_RULE_ENG_PKG.Init',
408 p_token2 => 'TIME',
409 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
410 return;
411 EXCEPTION
412 WHEN FEM_INTG_fatal_err THEN
413 ROLLBACk;
414 FEM_ENGINES_PKG.Tech_Message
415 (p_severity => pc_log_level_unexpected,
416 p_module => 'fem.plsql.fem_intg_hier_eng.init.'||'exception ',
417 p_app_name => 'FEM',
418 p_msg_name => 'FEM_GL_POST_215',
419 p_token1 => 'ERR_MSG',
420 p_value1 => SQLERRM);
421 FEM_ENGINES_PKG.User_Message
422 (p_app_name => 'FEM',
423 p_msg_name => 'FEM_GL_POST_215',
424 p_token1 => 'ERR_MSG',
425 p_value1 => SQLERRM);
426 FEM_ENGINES_PKG.Tech_Message
427 (p_severity => pc_log_level_procedure,
428 p_module => 'fem.plsql.fem_intg_hier_eng.init.'||'exception ',
429 p_app_name => 'FEM',
430 p_msg_name => 'FEM_GL_POST_203',
431 p_token1 => 'FUNC_NAME',
432 p_value1 => 'FEM_INTG_HIER_RULE_ENG_PKG.Init',
433 p_token2 => 'TIME',
434 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
435 x_completion_code := 2;
436 return;
437 WHEN OTHERS THEN
438 ROLLBACK;
439 --raise;
440 FEM_ENGINES_PKG.Tech_Message
441 (p_severity => pc_log_level_unexpected,
442 p_module => 'fem.plsql.fem_intg_hier_eng.init.' ||'exception others',
443 p_app_name => 'FEM',
444 p_msg_name => 'FEM_GL_POST_215',
445 p_token1 => 'ERR_MSG',
446 p_value1 => SQLERRM);
447 FEM_ENGINES_PKG.Tech_Message
448 (p_severity => pc_log_level_procedure,
449 p_module => 'fem.plsql.fem_intg_hier_eng.init.' || '',
450 p_app_name => 'FEM',
451 p_msg_name => 'FEM_GL_POST_203',
452 p_token1 => 'FUNC_NAME',
453 p_value1 => 'FEM_INTG_HIER_RULE_ENG_PKG.Init',
454 p_token2 => 'TIME',
455 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
456 x_completion_code := 2;
457 return;
458 END Init;
459 -- ======================================================================
460 -- Procedure
461 -- Bld_Hier_Single_Segment
462 -- Purpose
463 -- This routine will populate the global temporary hierarchy table
464 -- with hierarchical information based on the starting parent value
465 -- and the mapped segment in the selected hierarchy rule. It is also
466 -- responsible for calling routines to create new parent members and
467 -- to populate their attributes.
468 -- History
469 -- 10-28-04 Jee Kim Created
470 -- Arguments
471 -- x_completion_code Completion status of the routine
472 -- x_row_count_tot Number of records inserted
473 -- ======================================================================
474 PROCEDURE Bld_Hier_Single_Segment
475 (x_completion_code OUT NOCOPY NUMBER,
476 x_row_count_tot OUT NOCOPY NUMBER) IS
477 FEM_INTG_fatal_err EXCEPTION;
478 v_msg_count NUMBER;
479 v_msg_data VARCHAR2(4000);
480 v_API_return_status VARCHAR2(30);
481 v_row_count NUMBER;
482 v_row_count2 NUMBER;
483 v_row_count3 NUMBER;
484 v_parent_level NUMBER;
485 v_seq_name VARCHAR2(30);
486 v_seq_stmt VARCHAR2(2000);
487 v_sql_stmt VARCHAR2(2000);
488 v_compl_code NUMBER;
489 v_dimension_group_id NUMBER;
490 v_dim_group_name_seq NUMBER;
491 v_rel_dim_group_seq NUMBER;
492 BEGIN
493 FEM_ENGINES_PKG.Tech_Message
494 (p_severity => pc_log_level_procedure,
495 p_module => 'fem.plsql.fem_intg.hier_eng.Bld_Hier_Single_Segment',
496 p_app_name => 'FEM',
497 p_msg_name => 'FEM_GL_POST_201',
498 p_token1 => 'FUNC_NAME',
499 p_value1 => 'FEM_INTG_HIER_RULE_ENG_PKG.Bld_Hier_Single_Segment',
500 p_token2 => 'TIME',
501 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
502 x_completion_code := 0;
503 x_row_count_tot := 0;
504 FEM_ENGINES_PKG.Tech_Message
505 (p_severity => pc_log_level_procedure,
506 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Hier_Single_Segment.',
507 p_msg_text => ' pv_hier_obj_def_id:' || pv_hier_obj_def_id ||
508 ' pv_top_parent_id:' || pv_top_parent_id ||
509 ' pv_top_parent_disp_code:' || pv_top_parent_disp_code);
510
511 -- Insert self mapping record for hierarchy top node into the _GT table
512 INSERT INTO fem_intg_dim_hier_gt
513 (hierarchy_obj_def_id,
514 parent_depth_num,
515 parent_id,
516 parent_display_code,
517 child_depth_num,
518 child_id,
519 child_display_code,
520 single_depth_flag,
521 display_order_num,
522 dimension_group_id)
523 VALUES
524 (pv_hier_obj_def_id,
525 1,
526 pv_top_parent_id,
527 pv_top_parent_disp_code,
528 1,
529 pv_top_parent_id,
530 pv_top_parent_disp_code,
531 'Y',
532 1,
533 pv_top_dimension_group_id);
534
535
536
537 v_row_count := SQL%ROWCOUNT;
538 x_row_count_tot := x_row_count_tot + v_row_count;
539 FEM_ENGINES_PKG.Tech_Message
540 (p_severity => pc_log_level_procedure,
541 p_module => 'fem.plsql.fem_intg_hier_eng.row_count.',
542 p_msg_text => 'v_row_count:' || v_row_count ||
543 ' x_row_count_tot:' || x_row_count_tot);
544 COMMIT;
545 v_parent_level := 1;
546 v_seq_name := 'FEM_INTG_HIER_SEQ_' || pv_req_id || '_S';
547 v_seq_stmt := 'CREATE SEQUENCE '||v_seq_name||' START WITH 2';
548 FEM_ENGINES_PKG.Tech_Message
549 (p_severity => pc_log_level_procedure,
550 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Hier_Single_Segment.',
551 p_app_name => 'FEM',
552 p_msg_name => 'FEM_GL_POST_204',
553 p_token1 => 'VAR_NAME',
554 p_value1 => 'v_seq_stmt',
555 p_token2 => 'VAR_VAL',
556 p_value2 => v_seq_stmt);
557 EXECUTE IMMEDIATE v_seq_stmt;
558
559 COMMIT;
560 -- Infinite loop to insert immediate children for each parent member.
561 -- Only children who are themselves parenet values will be inserted here.
562 -- The exit condition is when the inserted number of row is 0
563 LOOP
564 -- to create level-based hierarchy
565 v_dim_group_name_seq := (v_parent_level+1)*100;
566 FEM_ENGINES_PKG.Tech_Message
567 (p_severity => pc_log_level_procedure,
568 p_module => 'fem.plsql.fem_intg_hier_eng.Infinite loop.',
569 p_msg_text => 'v_dim_group_name_seq:' || v_dim_group_name_seq);
570 -- Call API to create new dimension group
571 --dedutta : 5035567 : if check for pv_sequence_enforced_flag
572 IF (pv_sequence_enforced_flag = 'Y') THEN
573 FEM_DIM_GROUPS_UTIL_PKG.create_dim_group
574 (x_return_status => v_API_return_status,
575 x_msg_count => v_msg_count,
576 x_msg_data => v_msg_data,
577 p_encoded => FND_API.G_FALSE,
578 p_init_msg_list => FND_API.G_TRUE,
579 x_dimension_group_id => v_dimension_group_id,
580 x_dim_group_sequence => v_dim_group_seq,
581 p_dimension_varchar_label =>
582 FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label,
583 p_dim_group_name =>
584 FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label ||' '|| v_dim_group_name_seq,
585 p_dim_group_display_code =>
586 FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label ||' '|| v_dim_group_name_seq,
587 p_dim_group_description =>
588 FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label ||' '|| v_dim_group_name_seq);
589 END IF;
590
591 FEM_ENGINES_PKG.Tech_Message
592 (p_severity => pc_log_level_procedure,
593 p_module => 'fem.plsql.fem_intg_hier_eng.Infinite loop.',
594 p_msg_text => 'v_dimension_group_id:' || v_dimension_group_id);
595 IF (v_API_return_status NOT IN ('S')) THEN
596 FEM_ENGINES_PKG.Tech_Message
597 (p_severity => pc_log_level_statement,
598 p_module => 'fem.plsql.fem_intg_hier_rule_eng_pkg.'||'return_status',
599 p_msg_text => 'v_API_return_status:' || v_API_return_status);
600 FEM_ENGINES_PKG.Tech_Message
601 (p_severity => pc_log_level_statement,
602 p_module => 'fem.plsql.fem_intg_hier_rule_eng_pkg.'||'After create_dim_group',
603 p_msg_text => 'v_msg_data:' || v_msg_data);
604 FEM_ENGINES_PKG.User_Message
605 (p_app_name => 'FEM',
606 p_msg_text => v_msg_data);
607 FEM_ENGINES_PKG.User_Message
608 (p_app_name => 'FEM',
609 p_msg_name => 'FEM_INTG_FAIL_DIM_GRP');
610 RAISE FEM_INTG_fatal_err;
611 END IF;
612 FEM_ENGINES_PKG.Tech_Message
613 (p_severity => pc_log_level_procedure,
614 p_module => 'fem.plsql.fem_intg_hier_eng.Infinite loop.',
615 p_msg_text => 'pv_hier_obj_def_id:' || pv_hier_obj_def_id ||
616 ' v_parent_level:' || v_parent_level ||
617 ' pv_dim_vs_id:' || pv_dim_vs_id);
618 -- Insert immediate children for each parent into the _GT table
619 v_sql_stmt :=
620 'INSERT INTO fem_intg_dim_hier_gt
621 (hierarchy_obj_def_id,
622 parent_depth_num,
623 parent_id,
624 parent_display_code,
625 child_depth_num,
626 child_id,
627 child_display_code,
628 single_depth_flag,
629 display_order_num,
630 dimension_group_id)
631 SELECT DISTINCT :pv_hier_obj_def_id,
632 gt.child_depth_num,
633 gt.child_id,
634 gt.child_display_code,
635 (gt.child_depth_num + 1),
636 ff.flex_value_id,
637 ff.flex_value,
638 ''Y'',
639 -1,
640 :v_dimension_group_id
641 FROM fem_intg_dim_hier_gt gt,
642 fnd_flex_value_norm_hierarchy vh,
643 fnd_flex_values ff
644 WHERE gt.child_depth_num = :v_parent_level
645 AND vh.flex_value_set_id = :pv_aol_vs_id
646 AND vh.parent_flex_value = gt.child_display_code
647 AND vh.range_attribute = ''P''
648 AND ff.flex_value_set_id = :pv_aol_vs_id
649 AND ff.summary_flag = ''Y''
650 AND ff.flex_value
651 BETWEEN vh.child_flex_value_low
652 AND vh.child_flex_value_high
653 ORDER BY ff.flex_value';
654 FEM_ENGINES_PKG.Tech_Message
655 (p_severity => pc_log_level_procedure,
656 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Hier_Single_Segment.',
657 p_app_name => 'FEM',
658 p_msg_name => 'FEM_GL_POST_204',
659 p_token1 => 'VAR_NAME',
660 p_value1 => 'v_sql_stmt',
661 p_token2 => 'VAR_VAL',
662 p_value2 => v_sql_stmt);
663
664 EXECUTE IMMEDIATE v_sql_stmt
665 USING pv_hier_obj_def_id,
666 v_dimension_group_id,
667 v_parent_level,
668 pv_aol_vs_id,
669 pv_aol_vs_id;
670 v_row_count2 := SQL%ROWCOUNT;
671
672
673 x_row_count_tot := x_row_count_tot + v_row_count2;
674 COMMIT;
675 FEM_ENGINES_PKG.Tech_Message
676 (p_severity => pc_log_level_procedure,
677 p_module => 'fem.plsql.fem_intg_hier_eng.row_count.',
678 p_msg_text => 'v_row_count2:' || v_row_count2 ||
679 ' x_row_count_tot:' || x_row_count_tot);
680 -- update the display_order num. Not handled in above insert statement
681 -- because the distinct not allowed with nextval.
682 v_sql_stmt :=
683 'UPDATE fem_intg_dim_hier_gt
684 SET display_order_num = '||v_seq_name||'.nextval
685 WHERE rowid in
686 (select rowid
687 from fem_intg_dim_hier_gt)
688 AND display_order_num = -1';
689
690
691 FEM_ENGINES_PKG.Tech_Message
692 (p_severity => pc_log_level_procedure,
693 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Hier_Single_Segment.seq.nextval',
694 p_app_name => 'FEM',
695 p_msg_name => 'FEM_GL_POST_204',
696 p_token1 => 'VAR_NAME',
697 p_value1 => 'v_sql_stmt',
698 p_token2 => 'VAR_VAL',
699 p_value2 => v_sql_stmt);
700 EXECUTE IMMEDIATE v_sql_stmt;
701
702 -- When no more row is inserted, Exit the loop
703 IF (v_row_count2 = 0) THEN
704 EXIT;
705 END IF;
706 IF (pv_new_hier_obj_created) THEN
707 v_rel_dim_group_seq := v_parent_level+1;
708 FEM_ENGINES_PKG.Tech_Message
709 (p_severity => pc_log_level_statement,
710 p_module => 'fem.plsql.'||'insert into fem_hier_dimension_grps',
711 p_msg_text => 'v_dimension_group_id:' || v_dimension_group_id ||
712 ' v_rel_dim_group_seq:' || v_rel_dim_group_seq);
713 -- insert the new level to fem_hier_dimension_grps
714 --dedutta : 5035567 : if check for pv_sequence_enforced_flag
715 IF (pv_sequence_enforced_flag = 'Y') THEN
716 INSERT INTO fem_hier_dimension_grps
717 (dimension_group_id,
718 hierarchy_obj_id,
719 relative_dimension_group_seq,
720 creation_date,
721 created_by,
722 last_updated_by,
723 last_update_date,
724 last_update_login,
725 object_version_number)
726 VALUES
727 (v_dimension_group_id,
728 pv_hier_obj_id,
729 v_rel_dim_group_seq,
730 sysdate,
731 pv_user_id,
732 pv_user_id,
733 sysdate,
734 pv_login_id,
735 1);
736 END IF;
737 END IF;
738 FEM_ENGINES_PKG.Tech_Message
739 (p_severity => pc_log_level_procedure,
740 p_module => 'fem.plsql.fem_intg_hier_eng.',
741 p_msg_text => 'v_parent_level :' || v_parent_level );
742 v_parent_level := v_parent_level + 1;
743 END LOOP;
744 COMMIT;
745 FEM_INTG_NEW_DIM_MEMBER_PKG.Create_Parent_Members(
746 x_completion_code => v_compl_code);
747 IF (v_compl_code = 2) THEN
748 RAISE FEM_INTG_fatal_err;
749 END IF;
750 -- to create level-based hierarchy for bottom level children
751 v_dim_group_name_seq := (v_parent_level+1)*100;
752 FEM_ENGINES_PKG.Tech_Message
753 (p_severity => pc_log_level_procedure,
754 p_module => 'fem.plsql.fem_intg_hier_eng.Infinite loop.',
755 p_msg_text => 'v_dim_group_name_seq:' || v_dim_group_name_seq);
756 -- Call API to create new dimension group
757 --dedutta : 5035567 : if check for pv_sequence_enforced_flag
758 IF (pv_sequence_enforced_flag = 'Y') THEN
759 FEM_DIM_GROUPS_UTIL_PKG.create_dim_group
760 (x_return_status => v_API_return_status,
761 x_msg_count => v_msg_count,
762 x_msg_data => v_msg_data,
763 p_encoded => FND_API.G_FALSE,
764 p_init_msg_list => FND_API.G_TRUE,
765 x_dimension_group_id => v_dimension_group_id,
766 x_dim_group_sequence => v_dim_group_seq,
767 p_dimension_varchar_label =>
768 FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label,
769 p_dim_group_name =>
770 FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label ||' '|| v_dim_group_name_seq,
771 p_dim_group_display_code =>
772 FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label ||' '|| v_dim_group_name_seq,
773 p_dim_group_description =>
774 FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label ||' '|| v_dim_group_name_seq);
775 END IF;
776
777 FEM_ENGINES_PKG.Tech_Message
778 (p_severity => pc_log_level_procedure,
779 p_module => 'fem.plsql.fem_intg_hier_eng.',
780 p_msg_text => 'v_dimension_group_id:' || v_dimension_group_id);
781 IF (v_API_return_status NOT IN ('S')) THEN
782 FEM_ENGINES_PKG.Tech_Message
783 (p_severity => pc_log_level_statement,
784 p_module => 'fem.plsql.fem_intg_hier_rule_eng_pkg.'||'return_status',
785 p_msg_text => 'v_API_return_status:' || v_API_return_status);
786 FEM_ENGINES_PKG.Tech_Message
787 (p_severity => pc_log_level_statement,
788 p_module => 'fem.plsql.fem_intg_hier_rule_eng_pkg.'||'After create_dim_group',
789 p_msg_text => 'v_msg_data:' || v_msg_data);
790 FEM_ENGINES_PKG.User_Message
791 (p_app_name => 'FEM',
792 p_msg_text => v_msg_data);
793 FEM_ENGINES_PKG.User_Message
794 (p_app_name => 'FEM',
795 p_msg_name => 'FEM_INTG_FAIL_DIM_GRP');
796 RAISE FEM_INTG_fatal_err;
797 END IF;
798 IF (pv_new_hier_obj_created) THEN
799 v_rel_dim_group_seq := v_parent_level+1 ;
800 FEM_ENGINES_PKG.Tech_Message
801 (p_severity => pc_log_level_statement,
802 p_module => 'fem.plsql.'||'insert into fem_hier_dimension_grps',
803 p_msg_text => 'v_dimension_group_id:' || v_dimension_group_id ||
804 ' v_rel_dim_group_seq:' || v_rel_dim_group_seq);
805 -- insert the new level to fem_hier_dimension_grps
806 --dedutta : 5035567 : if check for pv_sequence_enforced_flag
807 IF (pv_sequence_enforced_flag = 'Y') THEN
808 INSERT INTO fem_hier_dimension_grps
809 (dimension_group_id,
810 hierarchy_obj_id,
811 relative_dimension_group_seq,
812 creation_date,
813 created_by,
814 last_updated_by,
815 last_update_date,
816 last_update_login,
817 object_version_number)
818 VALUES
819 (v_dimension_group_id,
820 pv_hier_obj_id,
821 v_rel_dim_group_seq,
822 sysdate,
823 pv_user_id,
824 pv_user_id,
825 sysdate,
826 pv_login_id,
827 1);
828 END IF;
829 END IF;
830
831 FEM_ENGINES_PKG.Tech_Message
832 (p_severity => pc_log_level_procedure,
833 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Hier_Single_Segment.',
834 p_msg_text => 'pv_hier_obj_def_id:' || pv_hier_obj_def_id ||
835 ' pv_dim_memb_col:' ||pv_dim_memb_col ||
836 ' pv_dim_memb_disp_col:' || pv_dim_memb_disp_col||
837 ' pv_dim_memb_b_tab:' ||pv_dim_memb_b_tab ||
838 ' pv_dim_vs_id:' || pv_dim_vs_id||
839 ' pv_dim_memb_disp_col:' ||pv_dim_memb_disp_col);
840
841 -- insert all bottom level detail children
842 --dedutta : 5035567 : introduced the bind variable dgid
843 v_sql_stmt :=
844 'INSERT INTO fem_intg_dim_hier_gt
845 (hierarchy_obj_def_id,
846 parent_depth_num,
847 parent_id,
848 parent_display_code,
849 child_depth_num,
850 child_id,
851 child_display_code,
852 single_depth_flag,
853 display_order_num,
854 dimension_group_id)
855 SELECT DISTINCT '
856 ||pv_hier_obj_def_id||',
857 gt.child_depth_num,
858 gt.child_id,
859 gt.child_display_code,
860 (gt.child_depth_num + 1),
861 m.'||pv_dim_memb_col||',
862 m.'||pv_dim_memb_disp_col||',
863 ''Y'',
864 -1,
865 :dgid
866 FROM fem_intg_dim_hier_gt gt,
867 fnd_flex_value_norm_hierarchy vh,
868 '||pv_dim_memb_b_tab||' m
869 WHERE vh.flex_value_set_id = '||pv_aol_vs_id||'
870 AND vh.parent_flex_value = gt.child_display_code
871 AND vh.range_attribute = ''C''
872 AND m.value_set_id = '||pv_dim_vs_id||'
873 AND m.'||pv_dim_memb_disp_col||'
874 BETWEEN vh.child_flex_value_low AND vh.child_flex_value_high
875 AND m.'||pv_dim_memb_col||' NOT IN
876 (SELECT inner_gt.child_id
877 FROM fem_intg_dim_hier_gt inner_gt)
878 AND m.'||pv_dim_memb_col||' NOT IN
879 (SELECT mh.parent_id
880 FROM '||pv_dim_hier_tab||' mh,
881 fem_object_definition_b odb,
882 fem_intg_hier_rules ihr
883 WHERE mh.hierarchy_obj_def_id = odb.object_definition_id
884 AND odb.object_id = ihr.hierarchy_obj_id
885 AND ihr.dim_rule_obj_def_id = '||pv_dim_rule_obj_def_id||'
886 AND mh.parent_value_set_id = '||pv_dim_vs_id||'
887 AND mh.child_value_set_id = '||pv_dim_vs_id||'
888 AND mh.child_id <> mh.parent_id)
889 ORDER BY m.'||pv_dim_memb_disp_col;
890
891
892
893 v_row_count3 := SQL%ROWCOUNT;
894 x_row_count_tot := x_row_count_tot + v_row_count3;
895 FEM_ENGINES_PKG.Tech_Message
896 (p_severity => pc_log_level_procedure,
897 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Hier_Single_Segment',
898 p_app_name => 'FEM',
899 p_msg_name => 'FEM_GL_POST_204',
900 p_token1 => 'VAR_NAME',
901 p_value1 => 'v_sql_stmt',
902 p_token2 => 'VAR_VAL',
903 p_value2 => v_sql_stmt);
904 EXECUTE IMMEDIATE v_sql_stmt using v_dimension_group_id;
905 COMMIT;
906 -- update the display_order num. Not handled in above insert statement
907 -- because the distinct not allowed with nextval.
908 v_sql_stmt :=
909 'UPDATE fem_intg_dim_hier_gt
910 SET display_order_num = '||v_seq_name||'.nextval
911 WHERE rowid in
912 (select rowid
913 from fem_intg_dim_hier_gt)
914 AND display_order_num = -1';
915 FEM_ENGINES_PKG.Tech_Message
916 (p_severity => pc_log_level_procedure,
917 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Hier_Single_Segment.seq.nextval',
918 p_app_name => 'FEM',
919 p_msg_name => 'FEM_GL_POST_204',
920 p_token1 => 'VAR_NAME',
921 p_value1 => 'v_sql_stmt',
922 p_token2 => 'VAR_VAL',
923 p_value2 => v_sql_stmt);
924 EXECUTE IMMEDIATE v_sql_stmt;
925 v_seq_stmt := 'DROP SEQUENCE ' || v_seq_name;
926 FEM_ENGINES_PKG.Tech_Message
927 (p_severity => pc_log_level_procedure,
928 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Hier_Single_Segment',
929 p_app_name => 'FEM',
930 p_msg_name => 'FEM_GL_POST_204',
931 p_token1 => 'VAR_NAME',
932 p_value1 => 'v_seq_stmt',
933 p_token2 => 'VAR_VAL',
934 p_value2 => v_seq_stmt);
935 EXECUTE IMMEDIATE v_seq_stmt;
936 COMMIT;
937 x_completion_code := 0;
938 FEM_ENGINES_PKG.Tech_Message
939 (p_severity => pc_log_level_procedure,
940 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Hier_Single_Segment.',
941 p_app_name => 'FEM',
942 p_msg_name => 'FEM_GL_POST_202',
943 p_token1 => 'FUNC_NAME',
944 p_value1 => 'FEM_INTG_HIER_RULE_ENG_PKG.Bld_Hier_Single_Segment',
945 p_token2 => 'TIME',
946 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
947 return;
948 EXCEPTION
949 WHEN FEM_INTG_fatal_err THEN
950 ROLLBACk;
951 FEM_ENGINES_PKG.Tech_Message
952 (p_severity => pc_log_level_unexpected,
953 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Hier_Single_Segment.' ||'FEM_INTG_fatal_err',
954 p_app_name => 'FEM',
955 p_msg_name => 'FEM_GL_POST_215',
956 p_token1 => 'ERR_MSG',
957 p_value1 => SQLERRM);
958 FEM_ENGINES_PKG.User_Message
959 (p_app_name => 'FEM',
960 p_msg_name => 'FEM_GL_POST_215',
961 p_token1 => 'ERR_MSG',
962 p_value1 => SQLERRM);
963 FEM_ENGINES_PKG.Tech_Message
964 (p_severity => pc_log_level_procedure,
965 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Hier_Single_Segment.' || 'FEM_INTG_fatal_err',
966 p_app_name => 'FEM',
967 p_msg_name => 'FEM_GL_POST_203',
968 p_token1 => 'FUNC_NAME',
969 p_value1 => 'FEM_INTG_HIER_RULE_ENG_PKG.Bld_Hier_Single_Segment',
970 p_token2 => 'TIME',
971 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
972 x_completion_code := 2;
973 return;
974 WHEN OTHERS THEN
975 ROLLBACK;
976 IF (v_seq_name IS NOT NULL) THEN
977 v_seq_stmt := 'DROP SEQUENCE ' || v_seq_name;
978 FEM_ENGINES_PKG.Tech_Message
979 (p_severity => pc_log_level_procedure,
980 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Hier_Single_Segment.'
981 || 'exceptoin others',
982 p_app_name => 'FEM',
983 p_msg_name => 'FEM_GL_POST_204',
984 p_token1 => 'VAR_NAME',
985 p_value1 => 'v_seq_stmt',
986 p_token2 => 'VAR_VAL',
987 p_value2 => v_seq_stmt);
988 EXECUTE IMMEDIATE v_seq_stmt;
989 COMMIT;
990 END IF;
991 FEM_ENGINES_PKG.Tech_Message
992 (p_severity => pc_log_level_unexpected,
993 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Hier_Single_Segment.' || 'exceptoin others',
994 p_app_name => 'FEM',
995 p_msg_name => 'FEM_GL_POST_215',
996 p_token1 => 'ERR_MSG',
997 p_value1 => SQLERRM);
998 FEM_ENGINES_PKG.User_Message
999 (p_app_name => 'FEM',
1000 p_msg_name => 'FEM_GL_POST_215',
1001 p_token1 => 'ERR_MSG',
1002 p_value1 => SQLERRM);
1003 FEM_ENGINES_PKG.Tech_Message
1004 (p_severity => pc_log_level_procedure,
1005 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Hier_Single_Segment.' || 'exceptoin others',
1006 p_app_name => 'FEM',
1007 p_msg_name => 'FEM_GL_POST_203',
1008 p_token1 => 'FUNC_NAME',
1009 p_value1 => 'FEM_INTG_HIER_RULE_ENG_PKG.Bld_Hier_Single_Segment',
1010 p_token2 => 'TIME',
1011 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1012 x_completion_code := 2;
1013 return;
1014 END Bld_Hier_Single_Segment;
1015 -- ======================================================================
1016 -- Procedure
1017 -- Bld_Component_Hiers
1018 -- Purpose
1019 -- Populates the fem_intg_dim_hier_gt table with each single segement
1020 -- hierarchy to be used as base components for to build the
1021 -- mult segment concatenatned hierarchy.
1022 -- The selected Multi Segment hierarchy rule is the driving defintion.
1023 -- Uses PL/SQL table tr_hier_traversal to do this.
1024 -- History
1025 -- 08-03-05 A. Budnik Created
1026 -- Arguments
1027 -- x_completion_code Completion status of the routine
1028 -- ======================================================================
1029 PROCEDURE Bld_Component_Hiers
1030 (x_completion_code OUT NOCOPY NUMBER) IS
1031 FEM_INTG_fatal_err EXCEPTION;
1032 v_row_count NUMBER;
1033 v_row_count2 NUMBER;
1034 v_row_count3 NUMBER;
1035 v_parent_level NUMBER;
1036 v_Num_hiers NUMBER;
1037 v_aol_vs_id number;
1038 v_sumcol varchar2(30);
1039 v_seq_name VARCHAR2(30);
1040 v_duplicate_parent VARCHAR2(12);
1041 V_summary varchar2(2000);
1042 v_seq_stmt VARCHAR2(2000);
1043 v_sql_stmt VARCHAR2(2000);
1044 i_hier_ctr number;
1045 i_concat number;
1046 V_sql_stmt_start VARCHAR2(4000);
1047 V_sql_stmt_end VARCHAR2(4000);
1048 v_add_where VARCHAR2(4000);
1049 V_where VARCHAR2(4000);
1050
1051 -- bug fix 4563603
1052 v_display_code VARCHAR2(150);
1053 v_parent_display_code VARCHAR2(150);
1054 v_offending_parents_list VARCHAR2(4000);
1055
1056 TYPE ReferenceCursor IS REF CURSOR;
1057
1058 c_child_of_multi_parent ReferenceCursor;
1059 c_multi_parent ReferenceCursor;
1060
1061 -- bug fix 4563603
1062
1063 BEGIN
1064
1065 FEM_ENGINES_PKG.Tech_Message
1066 (p_severity => pc_log_level_procedure,
1067 p_module => 'fem.plsql.fem_intg.hier_eng.Bld_Component_Hier',
1068 p_app_name => 'FEM',
1069 p_msg_name => 'FEM_GL_POST_201',
1070 p_token1 => 'FUNC_NAME',
1071 p_value1 => 'FEM_INTG_HIER_RULE_ENG_PKG.Bld_Component_Hier',
1072 p_token2 => 'TIME',
1073 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1074 x_completion_code := 0;
1075
1076 FEM_ENGINES_PKG.Tech_Message
1077 (p_severity => pc_log_level_procedure,
1078 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Component_Hier.Dimension_Init.',
1079 p_msg_text => ' pv_hier_obj_def_id:' || pv_hier_obj_def_id ||
1080 ' pv_dim_rule_obj_id:' || pv_dim_rule_obj_id ||
1081 ' pv_dim_rule_obj_def_id:' || pv_dim_rule_obj_def_id ||
1082 ' pv_dim_id:' || pv_dim_id ||
1083 ' pv_coa_id' || pv_coa_id);
1084
1085 -- run intialize dimension package variables
1086 -- sets up pv_mapped_segs structure which is used below.
1087 FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_rule_obj_id := pv_dim_rule_obj_id;
1088 FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_rule_obj_def_id := pv_dim_rule_obj_def_id;
1089 FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_id := pv_dim_id;
1090 FEM_INTG_DIM_RULE_ENG_PKG.pv_coa_id := pv_coa_id;
1091
1092 -- bug 4752271 - Add code to populate the company and cost center
1093 -- dimension and value set id values.
1094 IF pv_dim_id = 8 THEN
1095
1096 SELECT dimension_id
1097 INTO FEM_INTG_DIM_RULE_ENG_PKG.pv_com_dim_id
1098 FROM fem_dimensions_b
1099 WHERE dimension_varchar_label = 'COMPANY';
1100
1101 SELECT dimension_id
1102 INTO FEM_INTG_DIM_RULE_ENG_PKG.pv_cc_dim_id
1103 FROM fem_dimensions_b
1104 WHERE dimension_varchar_label = 'COST_CENTER';
1105
1106 SELECT value_set_id
1107 INTO FEM_INTG_DIM_RULE_ENG_PKG.pv_com_vs_id
1108 FROM fem_global_vs_combo_defs
1109 WHERE global_vs_combo_id = pv_gvsc_id
1110 AND dimension_id = FEM_INTG_DIM_RULE_ENG_PKG.pv_com_dim_id;
1111
1112 SELECT value_set_id
1113 INTO FEM_INTG_DIM_RULE_ENG_PKG.pv_cc_vs_id
1114 FROM fem_global_vs_combo_defs
1115 WHERE global_vs_combo_id = pv_gvsc_id
1116 AND dimension_id = FEM_INTG_DIM_RULE_ENG_PKG.pv_cc_dim_id;
1117
1118 END IF;
1119
1120 FEM_INTG_DIM_RULE_ENG_PKG.Init;
1121
1122 -- loop and buid each component hierarchy based on
1123 -- pv_traversal_rarray which is base on hier rule definition
1124 i_hier_ctr := pv_traversal_rarray.first;
1125
1126 while i_hier_ctr is not null
1127 loop
1128
1129 -- Mapping pv_traversal_rarray to .pv_mapped_segs()
1130 i_Concat := pv_traversal_rarray(i_hier_ctr).concat_segment;
1131 v_aol_vs_id := pv_traversal_rarray(i_hier_ctr).aol_vs_id;
1132
1133 -- these should be the same or there are rule definition inconsistencies
1134 if pv_traversal_rarray(i_hier_ctr).aol_vs_id <>
1135 FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(i_Concat).vs_id THEN
1136
1137 -- set messages and have fatial error.
1138 FEM_ENGINES_PKG.Tech_Message
1139 (p_severity => pc_log_level_unexpected,
1140 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Component_Hier.VSID_MISSMATCH.' ,
1141 p_msg_text => 'Structure pv_mapped_segs().vs_id does not correspond to pv_traversal_rarray().aol_vs_id');
1142 RAISE FEM_INTG_fatal_err;
1143
1144 else
1145
1146 -- Insert top node
1147 FEM_ENGINES_PKG.Tech_Message
1148 (p_severity => pc_log_level_procedure,
1149 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Component_Hier.Main_Loop.',
1150 p_msg_text => ' pv_hier_obj_def_id:' || pv_hier_obj_def_id ||
1151 ' traversal display_order:' || pv_traversal_rarray(i_hier_ctr).display_order ||
1152 ' traversal top_parent_id:' || pv_traversal_rarray(i_hier_ctr).top_parent_id ||
1153 ' traversal top_parent_disp_code:' || pv_traversal_rarray(i_hier_ctr).top_parent_value);
1154
1155
1156 CASE pv_traversal_rarray(i_hier_ctr).display_order
1157 WHEN 1 THEN
1158 INSERT INTO fem_intg_dim_hier_c1_gt
1159 (parent_depth_num,
1160 parent_id,
1161 parent_display_code,
1162 child_depth_num,
1163 child_id,
1164 child_display_code,
1165 single_depth_flag,
1166 display_order_num,
1167 child_leaf_flag)
1168 VALUES
1169 (1,
1170 pv_traversal_rarray(i_hier_ctr).top_parent_id,
1171 pv_traversal_rarray(i_hier_ctr).top_parent_value,
1172 1,
1173 pv_traversal_rarray(i_hier_ctr).top_parent_id,
1174 pv_traversal_rarray(i_hier_ctr).top_parent_value,
1175 'Y',
1176 1,
1177 'N');
1178
1179 WHEN 2 THEN
1180 INSERT INTO fem_intg_dim_hier_c2_gt
1181 (parent_depth_num,
1182 parent_id,
1183 parent_display_code,
1184 child_depth_num,
1185 child_id,
1186 child_display_code,
1187 single_depth_flag,
1188 display_order_num,
1189 child_leaf_flag)
1190 VALUES
1191 (1,
1192 pv_traversal_rarray(i_hier_ctr).top_parent_id,
1193 pv_traversal_rarray(i_hier_ctr).top_parent_value,
1194 1,
1195 pv_traversal_rarray(i_hier_ctr).top_parent_id,
1196 pv_traversal_rarray(i_hier_ctr).top_parent_value,
1197 'Y',
1198 1,
1199 'N');
1200
1201 WHEN 3 THEN
1202 INSERT INTO fem_intg_dim_hier_c3_gt
1203 (parent_depth_num,
1204 parent_id,
1205 parent_display_code,
1206 child_depth_num,
1207 child_id,
1208 child_display_code,
1209 single_depth_flag,
1210 display_order_num,
1211 child_leaf_flag)
1212 VALUES
1213 (1,
1214 pv_traversal_rarray(i_hier_ctr).top_parent_id,
1215 pv_traversal_rarray(i_hier_ctr).top_parent_value,
1216 1,
1217 pv_traversal_rarray(i_hier_ctr).top_parent_id,
1218 pv_traversal_rarray(i_hier_ctr).top_parent_value,
1219 'Y',
1220 1,
1221 'N');
1222
1223 WHEN 4 THEN
1224 INSERT INTO fem_intg_dim_hier_c4_gt
1225 (parent_depth_num,
1226 parent_id,
1227 parent_display_code,
1228 child_depth_num,
1229 child_id,
1230 child_display_code,
1231 single_depth_flag,
1232 display_order_num,
1233 child_leaf_flag)
1234 VALUES
1235 (1,
1236 pv_traversal_rarray(i_hier_ctr).top_parent_id,
1237 pv_traversal_rarray(i_hier_ctr).top_parent_value,
1238 1,
1239 pv_traversal_rarray(i_hier_ctr).top_parent_id,
1240 pv_traversal_rarray(i_hier_ctr).top_parent_value,
1241 'Y',
1242 1,
1243 'N');
1244
1245 WHEN 5 THEN
1246 INSERT INTO fem_intg_dim_hier_c5_gt
1247 (parent_depth_num,
1248 parent_id,
1249 parent_display_code,
1250 child_depth_num,
1251 child_id,
1252 child_display_code,
1253 single_depth_flag,
1254 display_order_num,
1255 child_leaf_flag)
1256 VALUES
1257 (1,
1258 pv_traversal_rarray(i_hier_ctr).top_parent_id,
1259 pv_traversal_rarray(i_hier_ctr).top_parent_value,
1260 1,
1261 pv_traversal_rarray(i_hier_ctr).top_parent_id,
1262 pv_traversal_rarray(i_hier_ctr).top_parent_value,
1263 'Y',
1264 1,
1265 'N');
1266
1267 END CASE; -- End initial insert
1268
1269 v_row_count := SQL%ROWCOUNT;
1270
1271 FEM_ENGINES_PKG.Tech_Message
1272 (p_severity => pc_log_level_procedure,
1273 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Component_Hier.row_count.',
1274 p_msg_text => 'traversal hierarchy_obj_def_id:' || pv_traversal_rarray(i_hier_ctr).display_order ||
1275 ' v_row_count:' || v_row_count);
1276
1277 -- insert intermediate nodes LOOP!
1278 -- loop to insert immediate children for each parent member.
1279 -- Only children who are themselves parenet values will be inserted here.
1280 -- The exit condition is when the inserted number of row is 0
1281 v_parent_level := 1;
1282 v_seq_name := 'FEM_INTG_HIER_SEQ_' || pv_req_id || '_S';
1283 v_seq_stmt := 'CREATE SEQUENCE '||v_seq_name||' START WITH 2';
1284
1285 FEM_ENGINES_PKG.Tech_Message
1286 (p_severity => pc_log_level_procedure,
1287 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Component_Hier.',
1288 p_app_name => 'FEM',
1289 p_msg_name => 'FEM_GL_POST_204',
1290 p_token1 => 'VAR_NAME',
1291 p_value1 => 'v_seq_stmt',
1292 p_token2 => 'VAR_VAL',
1293 p_value2 => v_seq_stmt);
1294 EXECUTE IMMEDIATE v_seq_stmt;
1295
1296 -- Insert immediate children for each parent into the _GT table
1297 LOOP
1298
1299 FEM_ENGINES_PKG.Tech_Message
1300 (p_severity => pc_log_level_procedure,
1301 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Component_Hier.Intermediate_Loop.',
1302 p_msg_text => 'pv_hier_obj_def_id:' || pv_hier_obj_def_id ||
1303 ' traversal hierarchy_obj_def_id:' || pv_traversal_rarray(i_hier_ctr).display_order ||
1304 ' v_parent_level:' || v_parent_level);
1305
1306 CASE pv_traversal_rarray(i_hier_ctr).display_order
1307 WHEN 1 THEN
1308 INSERT INTO fem_intg_dim_hier_c1_gt
1309 (parent_depth_num,
1310 parent_id,
1311 parent_display_code,
1312 child_depth_num,
1313 child_id,
1314 child_display_code,
1315 single_depth_flag,
1316 display_order_num,
1317 child_leaf_flag)
1318 SELECT DISTINCT gt.child_depth_num,
1319 gt.child_id,
1320 gt.child_display_code,
1321 (gt.child_depth_num + 1),
1322 ff.flex_value_id,
1323 ff.flex_value,
1324 'Y',
1325 -1,
1326 'N'
1327 FROM fem_intg_dim_hier_c1_gt gt,
1328 fnd_flex_value_norm_hierarchy vh,
1329 fnd_flex_values ff
1330 WHERE gt.child_depth_num = v_parent_level
1331 AND vh.flex_value_set_id = v_aol_vs_id
1332 AND vh.parent_flex_value = gt.child_display_code
1333 AND vh.range_attribute = 'P'
1334 AND ff.flex_value_set_id = v_aol_vs_id
1335 AND ff.summary_flag = 'Y'
1336 AND ff.flex_value
1337 BETWEEN vh.child_flex_value_low
1338 AND vh.child_flex_value_high
1339 ORDER BY ff.flex_value;
1340
1341 WHEN 2 THEN
1342 INSERT INTO fem_intg_dim_hier_c2_gt
1343 (parent_depth_num,
1344 parent_id,
1345 parent_display_code,
1346 child_depth_num,
1347 child_id,
1348 child_display_code,
1349 single_depth_flag,
1350 display_order_num,
1351 child_leaf_flag)
1352 SELECT DISTINCT gt.child_depth_num,
1353 gt.child_id,
1354 gt.child_display_code,
1355 (gt.child_depth_num + 1),
1356 ff.flex_value_id,
1357 ff.flex_value,
1358 'Y',
1359 -1,
1360 'N'
1361 FROM fem_intg_dim_hier_c2_gt gt,
1362 fnd_flex_value_norm_hierarchy vh,
1363 fnd_flex_values ff
1364 WHERE gt.child_depth_num = v_parent_level
1365 AND vh.flex_value_set_id = v_aol_vs_id
1366 AND vh.parent_flex_value = gt.child_display_code
1367 AND vh.range_attribute = 'P'
1368 AND ff.flex_value_set_id = v_aol_vs_id
1369 AND ff.summary_flag = 'Y'
1370 AND ff.flex_value
1371 BETWEEN vh.child_flex_value_low
1372 AND vh.child_flex_value_high
1373 ORDER BY ff.flex_value;
1374
1375 WHEN 3 THEN
1376 INSERT INTO fem_intg_dim_hier_c3_gt
1377 (parent_depth_num,
1378 parent_id,
1379 parent_display_code,
1380 child_depth_num,
1381 child_id,
1382 child_display_code,
1383 single_depth_flag,
1384 display_order_num,
1385 child_leaf_flag)
1386 SELECT DISTINCT gt.child_depth_num,
1387 gt.child_id,
1388 gt.child_display_code,
1389 (gt.child_depth_num + 1),
1390 ff.flex_value_id,
1391 ff.flex_value,
1392 'Y',
1393 -1,
1394 'N'
1395 FROM fem_intg_dim_hier_c3_gt gt,
1396 fnd_flex_value_norm_hierarchy vh,
1397 fnd_flex_values ff
1398 WHERE gt.child_depth_num = v_parent_level
1399 AND vh.flex_value_set_id = v_aol_vs_id
1400 AND vh.parent_flex_value = gt.child_display_code
1401 AND vh.range_attribute = 'P'
1402 AND ff.flex_value_set_id = v_aol_vs_id
1403 AND ff.summary_flag = 'Y'
1404 AND ff.flex_value
1405 BETWEEN vh.child_flex_value_low
1406 AND vh.child_flex_value_high
1407 ORDER BY ff.flex_value;
1408
1409 WHEN 4 THEN
1410 INSERT INTO fem_intg_dim_hier_c4_gt
1411 (parent_depth_num,
1412 parent_id,
1413 parent_display_code,
1414 child_depth_num,
1415 child_id,
1416 child_display_code,
1417 single_depth_flag,
1418 display_order_num,
1419 child_leaf_flag)
1420 SELECT DISTINCT gt.child_depth_num,
1421 gt.child_id,
1422 gt.child_display_code,
1423 (gt.child_depth_num + 1),
1424 ff.flex_value_id,
1425 ff.flex_value,
1426 'Y',
1427 -1,
1428 'N'
1429 FROM fem_intg_dim_hier_c4_gt gt,
1430 fnd_flex_value_norm_hierarchy vh,
1431 fnd_flex_values ff
1432 WHERE gt.child_depth_num = v_parent_level
1433 AND vh.flex_value_set_id = v_aol_vs_id
1434 AND vh.parent_flex_value = gt.child_display_code
1435 AND vh.range_attribute = 'P'
1436 AND ff.flex_value_set_id = v_aol_vs_id
1437 AND ff.summary_flag = 'Y'
1438 AND ff.flex_value
1439 BETWEEN vh.child_flex_value_low
1440 AND vh.child_flex_value_high
1441 ORDER BY ff.flex_value;
1442
1443 WHEN 5 THEN
1444 INSERT INTO fem_intg_dim_hier_c5_gt
1445 (parent_depth_num,
1446 parent_id,
1447 parent_display_code,
1448 child_depth_num,
1449 child_id,
1450 child_display_code,
1451 single_depth_flag,
1452 display_order_num,
1453 child_leaf_flag)
1454 SELECT DISTINCT gt.child_depth_num,
1455 gt.child_id,
1456 gt.child_display_code,
1457 (gt.child_depth_num + 1),
1458 ff.flex_value_id,
1459 ff.flex_value,
1460 'Y',
1461 -1,
1462 'N'
1463 FROM fem_intg_dim_hier_c5_gt gt,
1464 fnd_flex_value_norm_hierarchy vh,
1465 fnd_flex_values ff
1466 WHERE gt.child_depth_num = v_parent_level
1467 AND vh.flex_value_set_id = v_aol_vs_id
1468 AND vh.parent_flex_value = gt.child_display_code
1469 AND vh.range_attribute = 'P'
1470 AND ff.flex_value_set_id = v_aol_vs_id
1471 AND ff.summary_flag = 'Y'
1472 AND ff.flex_value
1473 BETWEEN vh.child_flex_value_low
1474 AND vh.child_flex_value_high
1475 ORDER BY ff.flex_value;
1476
1477 END CASE; -- Finish finding immediate children that are also parent values
1478
1479 v_row_count2 := SQL%ROWCOUNT;
1480
1481 FEM_ENGINES_PKG.Tech_Message
1482 (p_severity => pc_log_level_procedure,
1483 p_module => 'fem.plsql.fem_intg.hier_eng.Bld_Component_Hier.Intermediate_Inserted.',
1484 p_msg_text => ' v_row_count2:' || v_row_count2 ||
1485 ' v_parent_level :' || v_parent_level );
1486
1487 -- When no more row is inserted, Exit the loop
1488 IF (v_row_count2 = 0) THEN
1489 EXIT;
1490 END IF;
1491
1492 -- 2nd update the display_order num. Not handled in above insert statement
1493 -- because the distinct not allowed with nextval.
1494 v_sql_stmt :=
1495 'UPDATE fem_intg_dim_hier_c' || pv_traversal_rarray(i_hier_ctr).display_order || '_gt
1496 SET display_order_num = '||v_seq_name||'.nextval
1497 WHERE display_order_num = -1';
1498
1499 FEM_ENGINES_PKG.Tech_Message
1500 (p_severity => pc_log_level_procedure,
1501 p_module => 'fem.plsql.fem_intg_hier_eng.hier_eng.Bld_Component_Hier.seq.nextval',
1502 p_app_name => 'FEM',
1503 p_msg_name => 'FEM_GL_POST_204',
1504 p_token1 => 'VAR_NAME',
1505 p_value1 => 'v_sql_stmt',
1506 p_token2 => 'VAR_VAL',
1507 p_value2 => v_sql_stmt);
1508
1509 EXECUTE IMMEDIATE v_sql_stmt;
1510
1511 v_parent_level := v_parent_level + 1;
1512
1513 END loop; -- end of intermediate paraent loop
1514
1515 -- Test to use user defind tables versus fnd_flex_values for bottom leaves
1516 CASE FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(i_Concat).table_validated_flag
1517 WHEN 'N' THEN
1518
1519 -- bottom level detail from fnd_flex_values
1520
1521 FEM_ENGINES_PKG.Tech_Message
1522 (p_severity => pc_log_level_procedure,
1523 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Component_Hier.Bottom_Standard.',
1524 p_msg_text => 'pv_hier_obj_def_id:' || pv_hier_obj_def_id ||
1525 ' traversal hierarchy_obj_def_id:' || pv_traversal_rarray(i_hier_ctr).display_order);
1526
1527 CASE pv_traversal_rarray(i_hier_ctr).display_order
1528 WHEN 1 THEN
1529 INSERT INTO fem_intg_dim_hier_c1_gt
1530 (parent_depth_num,
1531 parent_id,
1532 parent_display_code,
1533 child_depth_num,
1534 child_id,
1535 child_display_code,
1536 single_depth_flag,
1537 display_order_num,
1538 child_leaf_flag)
1539 SELECT DISTINCT gt.child_depth_num,
1540 gt.child_id,
1541 gt.child_display_code,
1542 (gt.child_depth_num + 1),
1543 ff.flex_value_id,
1544 ff.flex_value,
1545 'Y',
1546 -1,
1547 'Y'
1548 FROM fem_intg_dim_hier_c1_gt gt,
1549 fnd_flex_value_norm_hierarchy vh,
1550 fnd_flex_values ff
1551 WHERE vh.flex_value_set_id = v_aol_vs_id
1552 AND vh.parent_flex_value = gt.child_display_code
1553 AND vh.range_attribute = 'C'
1554 AND ff.flex_value_set_id = v_aol_vs_id
1555 AND ff.summary_flag = 'N'
1556 AND ff.flex_value BETWEEN vh.child_flex_value_low AND vh.child_flex_value_high
1557 ORDER BY ff.flex_value;
1558
1559 WHEN 2 THEN
1560 INSERT INTO fem_intg_dim_hier_c2_gt
1561 (parent_depth_num,
1562 parent_id,
1563 parent_display_code,
1564 child_depth_num,
1565 child_id,
1566 child_display_code,
1567 single_depth_flag,
1568 display_order_num,
1569 child_leaf_flag)
1570 SELECT DISTINCT gt.child_depth_num,
1571 gt.child_id,
1572 gt.child_display_code,
1573 (gt.child_depth_num + 1),
1574 ff.flex_value_id,
1575 ff.flex_value,
1576 'Y',
1577 -1,
1578 'Y'
1579 FROM fem_intg_dim_hier_c2_gt gt,
1580 fnd_flex_value_norm_hierarchy vh,
1581 fnd_flex_values ff
1582 WHERE vh.flex_value_set_id = v_aol_vs_id
1583 AND vh.parent_flex_value = gt.child_display_code
1584 AND vh.range_attribute = 'C'
1585 AND ff.flex_value_set_id = v_aol_vs_id
1586 AND ff.summary_flag = 'N'
1587 AND ff.flex_value BETWEEN vh.child_flex_value_low AND vh.child_flex_value_high
1588 ORDER BY ff.flex_value;
1589
1590 WHEN 3 THEN
1591 INSERT INTO fem_intg_dim_hier_c3_gt
1592 (parent_depth_num,
1593 parent_id,
1594 parent_display_code,
1595 child_depth_num,
1596 child_id,
1597 child_display_code,
1598 single_depth_flag,
1599 display_order_num,
1600 child_leaf_flag)
1601 SELECT DISTINCT gt.child_depth_num,
1602 gt.child_id,
1603 gt.child_display_code,
1604 (gt.child_depth_num + 1),
1605 ff.flex_value_id,
1606 ff.flex_value,
1607 'Y',
1608 -1,
1609 'Y'
1610 FROM fem_intg_dim_hier_c3_gt gt,
1611 fnd_flex_value_norm_hierarchy vh,
1612 fnd_flex_values ff
1613 WHERE vh.flex_value_set_id = v_aol_vs_id
1614 AND vh.parent_flex_value = gt.child_display_code
1615 AND vh.range_attribute = 'C'
1616 AND ff.flex_value_set_id = v_aol_vs_id
1617 AND ff.summary_flag = 'N'
1618 AND ff.flex_value BETWEEN vh.child_flex_value_low AND vh.child_flex_value_high
1619 ORDER BY ff.flex_value;
1620
1621 WHEN 4 THEN
1622 INSERT INTO fem_intg_dim_hier_c4_gt
1623 (parent_depth_num,
1624 parent_id,
1625 parent_display_code,
1626 child_depth_num,
1627 child_id,
1628 child_display_code,
1629 single_depth_flag,
1630 display_order_num,
1631 child_leaf_flag)
1632 SELECT DISTINCT gt.child_depth_num,
1633 gt.child_id,
1634 gt.child_display_code,
1635 (gt.child_depth_num + 1),
1636 ff.flex_value_id,
1637 ff.flex_value,
1638 'Y',
1639 -1,
1640 'Y'
1641 FROM fem_intg_dim_hier_c4_gt gt,
1642 fnd_flex_value_norm_hierarchy vh,
1643 fnd_flex_values ff
1644 WHERE vh.flex_value_set_id = v_aol_vs_id
1645 AND vh.parent_flex_value = gt.child_display_code
1646 AND vh.range_attribute = 'C'
1647 AND ff.flex_value_set_id = v_aol_vs_id
1648 AND ff.summary_flag = 'N'
1649 AND ff.flex_value BETWEEN vh.child_flex_value_low AND vh.child_flex_value_high
1650 ORDER BY ff.flex_value;
1651
1652 WHEN 5 THEN
1653 INSERT INTO fem_intg_dim_hier_c5_gt
1654 (parent_depth_num,
1655 parent_id,
1656 parent_display_code,
1657 child_depth_num,
1658 child_id,
1659 child_display_code,
1660 single_depth_flag,
1661 display_order_num,
1662 child_leaf_flag)
1663 SELECT DISTINCT gt.child_depth_num,
1664 gt.child_id,
1665 gt.child_display_code,
1666 (gt.child_depth_num + 1),
1667 ff.flex_value_id,
1668 ff.flex_value,
1669 'Y',
1670 -1,
1671 'Y'
1672 FROM fem_intg_dim_hier_c5_gt gt,
1673 fnd_flex_value_norm_hierarchy vh,
1674 fnd_flex_values ff
1675 WHERE vh.flex_value_set_id = v_aol_vs_id
1676 AND vh.parent_flex_value = gt.child_display_code
1677 AND vh.range_attribute = 'C'
1678 AND ff.flex_value_set_id = v_aol_vs_id
1679 AND ff.summary_flag = 'N'
1680 AND ff.flex_value BETWEEN vh.child_flex_value_low AND vh.child_flex_value_high
1681 ORDER BY ff.flex_value;
1682
1683
1684 END CASE; -- Finish working with independent value set leaf values
1685
1686 v_row_count3 := SQL%ROWCOUNT;
1687
1688 ELSE
1689
1690 -- dynamic sql generation
1691 /*
1692 bottom level detail based on user defined table
1693 we use pv_mapped_segs to get this info and use dynamic sql to
1694 construct a statement like this:
1695 The varing items are:
1696 FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(i_Concat).val_col_name
1697 FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(i_Concat).table_name
1698 FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(i_Concat).id_col_name
1699 INSERT INTO fem_intg_dim_hier_gt
1700 (hierarchy_obj_def_id,
1701 parent_depth_num,
1702 parent_id,
1703 parent_display_code,
1704 child_depth_num,
1705 child_id,
1706 child_display_code,
1707 single_depth_flag,
1708 display_order_num,
1709 child_leaf_flag)
1710 SELECT DISTINCT 1,
1711 gt.child_depth_num,
1712 gt.child_id,
1713 gt.child_display_code,
1714 (gt.child_depth_num + 1),
1715 -1,
1716 MEANING,
1717 'Y',
1718 -1,
1719 'Y'
1720 FROM fem_intg_dim_hier_gt gt,
1721 FND_LOOKUPS,
1722 fnd_flex_value_norm_hierarchy vh
1723 WHERE FND_LOOKUPS.LOOKUP_TYPE = 'YES_NO'
1724 AND vh.flex_value_set_id = 1002723
1725 AND gt.hierarchy_obj_def_id = 1
1726 AND vh.parent_flex_value = gt.child_display_code
1727 AND vh.range_attribute = 'C'
1728 AND MEANING
1729 BETWEEN vh.child_flex_value_low
1730 AND vh.child_flex_value_high
1731 ORDER BY MEANING
1732 */
1733
1734 FEM_ENGINES_PKG.Tech_Message
1735 (p_severity => pc_log_level_procedure,
1736 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Component_Hier.Bottom_User_table.',
1737 p_msg_text => 'pv_hier_obj_def_id:' || pv_hier_obj_def_id ||
1738 ' traversal hierarchy_obj_def_id:' || pv_traversal_rarray(i_hier_ctr).display_order);
1739
1740 -- Use the pv_mapped_segs(i_Concat) structure to construct dynamic sql
1741 -- statement to insert bottom level children
1742 -- If ADDITIONAL_WHERE_CLAUSE is populated use it in dynamic sql.
1743 Select ADDITIONAL_WHERE_CLAUSE
1744 into v_add_where
1745 from fnd_flex_validation_tables
1746 where pv_traversal_rarray(i_hier_ctr).aol_vs_id = FLEX_VALUE_SET_ID;
1747
1748 if v_add_where is NULL or Instr(upper(v_add_where), 'WHERE', 1) = 0 then
1749 V_Where := ' WHERE vh.flex_value_set_id = ' || v_aol_vs_id || '
1750 AND vh.parent_flex_value = gt.child_display_code
1751 AND vh.range_attribute = ''C'' ' ;
1752 Else
1753 V_Where := v_add_where || '
1754 AND vh.flex_value_set_id = ' || v_aol_vs_id || '
1755 AND vh.parent_flex_value = gt.child_display_code
1756 AND vh.range_attribute = ''C'' ';
1757 End if;
1758
1759 -- Begining of dynamic insert
1760 V_sql_stmt_start := 'INSERT INTO fem_intg_dim_hier_c' || pv_traversal_rarray(i_hier_ctr).display_order || '_gt
1761 (parent_depth_num,
1762 parent_id,
1763 parent_display_code,
1764 child_depth_num,
1765 child_id,
1766 child_display_code,
1767 single_depth_flag,
1768 display_order_num,
1769 child_leaf_flag)
1770 SELECT DISTINCT gt.child_depth_num,
1771 gt.child_id,
1772 gt.child_display_code,
1773 (gt.child_depth_num + 1),
1774 -1,
1775 ' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(i_Concat).val_col_name || ',
1776 ''Y'',
1777 -1,
1778 ''Y''
1779 FROM fem_intg_dim_hier_c' || pv_traversal_rarray(i_hier_ctr).display_order || '_gt gt,
1780 ' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(i_Concat).table_name || ',
1781 fnd_flex_value_norm_hierarchy vh
1782 ';
1783 -- ending concatinated to where that was assigned above
1784 V_sql_stmt_end := '
1785 AND ' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(i_Concat).val_col_name || '
1786 ' || 'BETWEEN vh.child_flex_value_low
1787 AND vh.child_flex_value_high
1788 ORDER BY ' || FEM_INTG_DIM_RULE_ENG_PKG.pv_mapped_segs(i_Concat).val_col_name ;
1789 -- concatenate three sections
1790 V_sql_stmt := V_sql_stmt_start || v_where || V_sql_stmt_end;
1791
1792 FEM_ENGINES_PKG.Tech_Message
1793 (p_severity => pc_log_level_procedure,
1794 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Component_Hier',
1795 p_app_name => 'FEM',
1796 p_msg_name => 'FEM_GL_POST_204',
1797 p_token1 => 'VAR_NAME',
1798 p_value1 => 'v_sql_stmt',
1799 p_token2 => 'VAR_VAL',
1800 p_value2 => v_sql_stmt);
1801
1802 EXECUTE IMMEDIATE v_sql_stmt;
1803 v_row_count3 := SQL%ROWCOUNT;
1804
1805 END CASE; -- end bottom leaf inserts
1806
1807 FEM_ENGINES_PKG.Tech_Message
1808 (p_severity => pc_log_level_procedure,
1809 p_module => 'fem.plsql.fem_intg.hier_eng.Bld_Component_Hier.Bottom_level.',
1810 p_msg_text => ' v_row_count3:' || v_row_count3 ||
1811 ' v_parent_level :' || v_parent_level);
1812 -- 3rd update the display_order num. Not handled in above insert statement
1813 -- because the distinct not allowed with nextval.
1814 v_sql_stmt :=
1815 'UPDATE fem_intg_dim_hier_c' || pv_traversal_rarray(i_hier_ctr).display_order || '_gt
1816 SET display_order_num = '||v_seq_name||'.nextval
1817 WHERE display_order_num = -1';
1818
1819 EXECUTE IMMEDIATE v_sql_stmt;
1820
1821 v_seq_stmt := 'DROP SEQUENCE ' || v_seq_name;
1822 EXECUTE IMMEDIATE v_seq_stmt;
1823
1824 END if; -- end of build of this component single seg hierarchy
1825
1826 -- Check if any children within the hierarchy are assigned to
1827 -- multiple parents.
1828 BEGIN
1829
1830 CASE pv_traversal_rarray(i_hier_ctr).display_order
1831
1832 WHEN 1 THEN
1833 SELECT 'Duplicate'
1834 INTO v_duplicate_parent
1835 FROM dual
1836 WHERE EXISTS
1837 (SELECT gt.child_display_code
1838 FROM fem_intg_dim_hier_c1_gt gt
1839 WHERE gt.parent_display_code <> gt.child_display_code
1840 GROUP BY gt.child_display_code
1841 HAVING count(gt.child_display_code) > 1);
1842
1843 WHEN 2 THEN
1844 SELECT 'Duplicate'
1845 INTO v_duplicate_parent
1846 FROM dual
1847 WHERE EXISTS
1848 (SELECT gt.child_display_code
1849 FROM fem_intg_dim_hier_c2_gt gt
1850 WHERE gt.parent_display_code <> gt.child_display_code
1851 GROUP BY gt.child_display_code
1852 HAVING count(gt.child_display_code) > 1);
1853
1854 WHEN 3 THEN
1855 SELECT 'Duplicate'
1856 INTO v_duplicate_parent
1857 FROM dual
1858 WHERE EXISTS
1859 (SELECT gt.child_display_code
1860 FROM fem_intg_dim_hier_c3_gt gt
1861 WHERE gt.parent_display_code <> gt.child_display_code
1862 GROUP BY gt.child_display_code
1863 HAVING count(gt.child_display_code) > 1);
1864
1865 WHEN 4 THEN
1866 SELECT 'Duplicate'
1867 INTO v_duplicate_parent
1868 FROM dual
1869 WHERE EXISTS
1870 (SELECT gt.child_display_code
1871 FROM fem_intg_dim_hier_c4_gt gt
1872 WHERE gt.parent_display_code <> gt.child_display_code
1873 GROUP BY gt.child_display_code
1874 HAVING count(gt.child_display_code) > 1);
1875
1876 WHEN 5 THEN
1877 SELECT 'Duplicate'
1878 INTO v_duplicate_parent
1879 FROM dual
1880 WHERE EXISTS
1881 (SELECT gt.child_display_code
1882 FROM fem_intg_dim_hier_c5_gt gt
1883 WHERE gt.parent_display_code <> gt.child_display_code
1884 GROUP BY gt.child_display_code
1885 HAVING count(gt.child_display_code) > 1);
1886
1887 END CASE; -- End duplicate check
1888
1889 EXCEPTION
1890 WHEN NO_DATA_FOUND THEN
1891 null;
1892 END;
1893
1894 IF (v_duplicate_parent = 'Duplicate') THEN
1895 FEM_ENGINES_PKG.Tech_Message
1896 (p_severity => pc_log_level_procedure,
1897 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Component_Hier.' || 'duplicate',
1898 p_app_name => 'FEM',
1899 p_msg_name => 'FEM_INTG_HIER_MULTI_PARENT_ERR',
1900 p_token1 => 'VAR_NAME',
1901 p_value1 => 'v_duplicate_parent',
1902 p_token2 => 'VAR_VAL',
1903 p_value2 => v_duplicate_parent);
1904 FEM_ENGINES_PKG.User_Message
1905 (p_app_name => 'FEM',
1906 p_msg_name => 'FEM_INTG_HIER_MULTI_PARENT_ERR');
1907
1908 --bug fix 4563603
1909 FEM_ENGINES_PKG.Tech_Message
1910 (p_severity => pc_log_level_procedure,
1911 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Component_Hier.' || 'duplicate',
1912 p_app_name => 'FEM',
1913 p_msg_text => ' ');
1914
1915 FEM_ENGINES_PKG.User_Message
1916 (p_app_name => 'FEM',
1917 p_msg_text => ' ');
1918
1919 FEM_ENGINES_PKG.Tech_Message
1920 (p_severity => pc_log_level_procedure,
1921 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Component_Hier.' || 'duplicate',
1922 p_app_name => 'FEM',
1923 p_msg_name => 'FEM_INTG_HIER_MULT_PARENTS');
1924
1925 FEM_ENGINES_PKG.User_Message
1926 (p_app_name => 'FEM',
1927 p_msg_name => 'FEM_INTG_HIER_MULT_PARENTS');
1928
1929 OPEN c_child_of_multi_parent FOR
1930 'SELECT gt.child_display_code
1931 FROM fem_intg_dim_hier_c' || pv_traversal_rarray(i_hier_ctr).display_order || '_gt gt
1932 WHERE gt.parent_display_code <> gt.child_display_code
1933 GROUP BY gt.child_display_code
1934 HAVING count(gt.child_display_code) > 1';
1935
1936 LOOP
1937 FETCH c_child_of_multi_parent INTO v_display_code;
1938 EXIT WHEN c_child_of_multi_parent%NOTFOUND;
1939
1940 --Bug fix 5577544
1941 v_offending_parents_list := NULL;
1942
1943 OPEN c_multi_parent FOR
1944 'SELECT DISTINCT gt.parent_display_code
1945 FROM fem_intg_dim_hier_c' || pv_traversal_rarray(i_hier_ctr).display_order || '_gt gt
1946 WHERE gt.parent_display_code <> gt.child_display_code
1947 and gt.child_display_code = :child_display_code' USING v_display_code;
1948
1949 LOOP
1950 FETCH c_multi_parent INTO v_parent_display_code;
1951 EXIT WHEN c_multi_parent%NOTFOUND;
1952 v_offending_parents_list := v_offending_parents_list || v_parent_display_code || ', ';
1953 END LOOP;
1954 CLOSE c_multi_parent;
1955 v_offending_parents_list := SUBSTR(v_offending_parents_list,1,LENGTH(v_offending_parents_list)-2);
1956
1957 FEM_ENGINES_PKG.Tech_Message
1958 (p_severity => pc_log_level_procedure,
1959 p_module => 'fem.plsql.fem_intg.hier_eng.',
1960 p_app_name => 'FEM',
1961 p_msg_name => 'FEM_INTG_HIER_PARENT_CHILD_LST',
1962 p_token1 => 'CHILD',
1963 p_value1 => v_display_code,
1964 p_token2 => 'PARENTS',
1965 p_value2 => v_offending_parents_list);
1966 FEM_ENGINES_PKG.User_Message
1967 (p_app_name => 'FEM',
1968 p_msg_name => 'FEM_INTG_HIER_PARENT_CHILD_LST',
1969 p_token1 => 'CHILD',
1970 p_value1 => v_display_code,
1971 p_token2 => 'PARENTS',
1972 p_value2 => v_offending_parents_list);
1973
1974 END LOOP;
1975
1976 CLOSE c_child_of_multi_parent;
1977 FEM_ENGINES_PKG.Tech_Message
1978 (p_severity => pc_log_level_procedure,
1979 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Component_Hier.' || 'duplicate',
1980 p_app_name => 'FEM',
1981 p_msg_text => ' ');
1982
1983 FEM_ENGINES_PKG.User_Message
1984 (p_app_name => 'FEM',
1985 p_msg_text => ' ');
1986 --bug fix 4563603
1987
1988 RAISE FEM_INTG_fatal_err;
1989 END IF;
1990
1991 i_hier_ctr := pv_traversal_rarray.next(i_hier_ctr);
1992 commit; -- one component hier has been built to fem_intg_dim_hier_gt
1993
1994 END loop; -- Main Loop for traversing each component hierarchy
1995
1996 x_completion_code := 0;
1997
1998 FEM_ENGINES_PKG.Tech_Message
1999 (p_severity => pc_log_level_procedure,
2000 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Component_Hier.',
2001 p_app_name => 'FEM',
2002 p_msg_name => 'FEM_GL_POST_202',
2003 p_token1 => 'FUNC_NAME',
2004 p_value1 => 'FEM_INTG_HIER_RULE_ENG_PKG.Bld_Component_Hier',
2005 p_token2 => 'TIME',
2006 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2007
2008
2009 return;
2010
2011 EXCEPTION
2012 WHEN FEM_INTG_fatal_err THEN
2013 ROLLBACK;
2014 FEM_ENGINES_PKG.Tech_Message
2015 (p_severity => pc_log_level_unexpected,
2016 p_module => 'fem.plsql.fem_intg.hier_eng.Bld_Component_Hier.' ||'FEM_INTG_fatal_err',
2017 p_app_name => 'FEM',
2018 p_msg_name => 'FEM_GL_POST_215',
2019 p_token1 => 'ERR_MSG',
2020 p_value1 => SQLERRM);
2021 FEM_ENGINES_PKG.User_Message
2022 (p_app_name => 'FEM',
2023 p_msg_name => 'FEM_GL_POST_215',
2024 p_token1 => 'ERR_MSG',
2025 p_value1 => SQLERRM);
2026 FEM_ENGINES_PKG.Tech_Message
2027 (p_severity => pc_log_level_procedure,
2028 p_module => 'fem.plsql.fem_intg.hier_eng.Bld_Component_Hier.' || 'FEM_INTG_fatal_err',
2029 p_app_name => 'FEM',
2030 p_msg_name => 'FEM_GL_POST_203',
2031 p_token1 => 'FUNC_NAME',
2032 p_value1 => 'FEM_INTG_HIER_RULE_ENG_PKG.Bld_Component_Hier',
2033 p_token2 => 'TIME',
2034 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2035 x_completion_code := 2;
2036 return;
2037 WHEN OTHERS THEN
2038 ROLLBACK;
2039 IF (v_seq_name IS NOT NULL) THEN
2040 v_seq_stmt := 'DROP SEQUENCE ' || v_seq_name;
2041 FEM_ENGINES_PKG.Tech_Message
2042 (p_severity => pc_log_level_procedure,
2043 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Component_Hier.'
2044 || 'exceptoin others',
2045 p_app_name => 'FEM',
2046 p_msg_name => 'FEM_GL_POST_204',
2047 p_token1 => 'VAR_NAME',
2048 p_value1 => 'v_seq_stmt',
2049 p_token2 => 'VAR_VAL',
2050 p_value2 => v_seq_stmt);
2051 EXECUTE IMMEDIATE v_seq_stmt;
2052 COMMIT;
2053 END IF;
2054 FEM_ENGINES_PKG.Tech_Message
2055 (p_severity => pc_log_level_unexpected,
2056 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Component_Hier.' || 'exceptoin others',
2057 p_app_name => 'FEM',
2058 p_msg_name => 'FEM_GL_POST_215',
2059 p_token1 => 'ERR_MSG',
2060 p_value1 => SQLERRM);
2061 FEM_ENGINES_PKG.User_Message
2062 (p_app_name => 'FEM',
2063 p_msg_name => 'FEM_GL_POST_215',
2064 p_token1 => 'ERR_MSG',
2065 p_value1 => SQLERRM);
2066 FEM_ENGINES_PKG.Tech_Message
2067 (p_severity => pc_log_level_procedure,
2068 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Component_Hier.' || 'exceptoin others',
2069 p_app_name => 'FEM',
2070 p_msg_name => 'FEM_GL_POST_203',
2071 p_token1 => 'FUNC_NAME',
2072 p_value1 => 'FEM_INTG_HIER_RULE_ENG_PKG.Bld_Component_Hier',
2073 p_token2 => 'TIME',
2074 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2075 x_completion_code := 2;
2076 return;
2077 END Bld_Component_Hiers;
2078 -- ======================================================================
2079 -- Procedure
2080 -- Bld_Hier_Multi_Segment
2081 -- Purpose
2082 --
2083 --
2084 --
2085 --
2086 -- History
2087 -- 10-18-05 A. Budnik bugs 4652450 and 4681970
2088 -- 08-14-05 Piush Gupta Added code to stub
2089 -- 08-03-05 A. Budnik Created
2090 -- Arguments
2091 -- x_completion_code Completion status of the routine
2092 --
2093 -- To Do : Use bind variables in dynamically generated SQL to minimize
2094 -- parsing.
2095 -- ======================================================================
2096 PROCEDURE Bld_Hier_Multi_Segment (x_completion_code OUT NOCOPY NUMBER) IS
2097 FEM_INTG_fatal_err EXCEPTION;
2098 v_compl_code NUMBER;
2099 v_Num_hiers NUMBER;
2100 v_concated_segment NUMBER(1);
2101 v_counter integer;
2102 v_hier_counter integer;
2103 TYPE t_concat_order is table of number;
2104 v_concat_rarray t_concat_order := t_concat_order();
2105 v_sql varchar2(4000);
2106 v_debug number;
2107 v_sql_temp varchar2(4000);
2108 v_completion_code number;
2109 v_dim_process_row_cnt number;
2110 v_dim_group_name_seq number;
2111 V_API_RETURN_STATUS varchar2(30);
2112 v_msg_count number;
2113 v_msg_data varchar2(4000);
2114 V_DIMENSION_GROUP_ID number;
2115 v_seq_name VARCHAR2(30);
2116 v_seq_stmt VARCHAR2(2000);
2117 v_sql_stmt VARCHAR2(2000);
2118 BEGIN
2119 FEM_ENGINES_PKG.Tech_Message
2120 (p_severity => pc_log_level_procedure,
2121 p_module => 'fem.plsql.fem_intg.hier_eng.Bld_Hier_Multi_Segment',
2122 p_app_name => 'FEM',
2123 p_msg_name => 'FEM_GL_POST_201',
2124 p_token1 => 'FUNC_NAME',
2125 p_value1 => 'FEM_INTG_HIER_RULE_ENG_PKG.Bld_Hier_Multi_Segment',
2126 p_token2 => 'TIME',
2127 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2128 -- trim the display codes to 15 chars
2129 update FEM_INTG_DIM_HIER_GT
2130 set child_display_code = substr(child_display_code, 1, pc_max_disp_len)
2131 , parent_display_code = substr(parent_display_code, 1, pc_max_disp_len);
2132 -- create a pl/sql table that holds the segments in concatenations order
2133 v_concat_rarray.extend(pv_traversal_rarray.count);
2134 v_counter := pv_traversal_rarray.first;
2135 while v_counter is not null
2136 loop
2137 v_concat_rarray(pv_traversal_rarray(v_counter).concat_segment) := v_counter;
2138 v_counter := pv_traversal_rarray.next(v_counter);
2139 end loop;
2140 -- create dynamic SQL to insert the leaf nodes
2141 /*
2142 Sample SQL stmt :
2143 INSERT INTO FEM_INTG_DIM_HIER_GT
2144 (HIERARCHY_OBJ_DEF_ID
2145 , child_display_code
2146 , child_id
2147 , parent_display_code
2148 , parent_id
2149 , child_depth_num
2150 , parent_depth_num
2151 , child_leaf_flag
2152 , single_depth_flag
2153 , display_order_num
2154 )
2155 (SELECT 20861
2156 , gt1.child_display_code || '-' || gt2.child_display_code || '-' || gt3.child_display_code || '-' ||
2157 gt4.child_display_code || '-' || gt5.child_display_code, b.CUSTOMER_ID ,gt1.parent_display_code || '-' ||
2158 gt2.child_display_code || '-' || gt3.child_display_code || '-' || gt4.child_display_code || '-' ||
2159 gt5.child_display_code
2160 , -1
2161 , (gt1.child_depth_num + gt2.child_depth_num + gt3.child_depth_num + gt4.child_depth_num + gt5.child_depth_num- 5 + 1)
2162 , (gt1.parent_depth_num + gt2.parent_depth_num + gt3.parent_depth_num + gt4.parent_depth_num + gt5.parent_depth_num)
2163 , 'Y'
2164 , 'N'
2165 , -1
2166 from
2167 FEM_INTG_DIM_HIER_GT gt1
2168 , FEM_INTG_DIM_HIER_GT gt2
2169 , FEM_INTG_DIM_HIER_GT gt3
2170 , FEM_INTG_DIM_HIER_GT gt4
2171 , FEM_INTG_DIM_HIER_GT gt5
2172 , FEM_CUSTOMERS_B b
2173 WHERE gt1.hierarchy_obj_def_id = 1
2174 and gt1.child_leaf_flag = 'Y'
2175 and gt2.hierarchy_obj_def_id = 2
2176 and gt2.child_leaf_flag = 'Y'
2177 and gt3.hierarchy_obj_def_id = 3
2178 and gt3.child_leaf_flag = 'Y'
2179 and gt4.hierarchy_obj_def_id = 4
2180 and gt4.child_leaf_flag = 'Y'
2181 and gt5.hierarchy_obj_def_id = 5
2182 and gt5.child_leaf_flag = 'Y'
2183 and b.CUSTOMER_DISPLAY_CODE = gt1.child_display_code || '-' || gt2.child_display_code || '-' || gt3.child_display_code || '-' || gt4.child_display_code || '-' || gt5.child_display_code
2184 and b.value_set_id = :pv_aol_vs_id)
2185 */
2186 v_sql := 'INSERT INTO FEM_INTG_DIM_HIER_GT
2187 (HIERARCHY_OBJ_DEF_ID
2188 , child_display_code
2189 , child_id
2190 , parent_display_code
2191 , parent_id
2192 , child_depth_num
2193 , parent_depth_num
2194 , child_leaf_flag
2195 , single_depth_flag
2196 , display_order_num
2197 )
2198 (SELECT ' || pv_hier_obj_def_id || '
2199 , ';
2200
2201 -- construct sql for : gt1.child_display_code||'-'||gt2.child_display_code
2202 v_counter := v_concat_rarray.first;
2203 while v_counter is not null
2204 loop
2205 v_sql := v_sql || 'gt' || v_concat_rarray(v_counter) || '.child_display_code';
2206 if v_counter < v_concat_rarray.count then
2207 v_sql := v_sql || ' || ''-'' || ';
2208 end if;
2209 v_counter := v_concat_rarray.next(v_counter);
2210 end loop;
2211 v_sql := v_sql || ', b.' || pv_dim_memb_col || ' ,';
2212
2213 -- construct sql for : gt1.parent_display_code||'-'||gt2.child_display_code
2214 v_counter := v_concat_rarray.first;
2215 while v_counter is not null
2216 loop
2217 v_sql := v_sql || 'gt' || v_concat_rarray(v_counter);
2218 if v_concat_rarray(v_counter) = pv_traversal_rarray.first then
2219 v_sql := v_sql || '.parent_display_code';
2220 else
2221 v_sql := v_sql || '.child_display_code';
2222 end if;
2223 if v_counter < v_concat_rarray.count then
2224 v_sql := v_sql || ' || ''-'' || ';
2225 end if;
2226 v_counter := v_concat_rarray.next(v_counter);
2227 end loop;
2228 v_sql := v_sql || '
2229 , -1';
2230
2231 -- construct sql for : , (gt1.child_depth_num + gt2.child_depth_num - p_num_hiers + 1)
2232 v_sql_temp := ', (';
2233 v_counter := pv_traversal_rarray.first;
2234 while v_counter is not null
2235 loop
2236 v_sql_temp := v_sql_temp || 'gt' || v_counter || '.child_depth_num';
2237 if v_counter < pv_traversal_rarray.count then
2238 v_sql_temp := v_sql_temp || ' + ';
2239 end if;
2240 v_counter := pv_traversal_rarray.next(v_counter);
2241 end loop;
2242 v_sql_temp := v_sql_temp || '- ' || pv_traversal_rarray.count || ' + 1)';
2243 v_sql := v_sql || v_sql_temp || '
2244 , (';
2245
2246 -- Construct sql for : , (gt1.parent_depth_num + gt2.parent_depth_num)
2247 v_counter := pv_traversal_rarray.first;
2248 while v_counter is not null
2249 loop
2250 v_sql := v_sql || 'gt' || v_counter || '.parent_depth_num';
2251 if v_counter < pv_traversal_rarray.count then
2252 v_sql := v_sql || ' + ';
2253 end if;
2254 v_counter := pv_traversal_rarray.next(v_counter);
2255 end loop;
2256 v_sql := v_sql || ')
2257 , ''Y''
2258 , ''N''
2259 , -1
2260 from ';
2261
2262 -- construct sql for : FROM FEM_INTG_DIM_HIER_GT gt1, FEM_INTG_DIM_HIER_GT gt2, fem_cctr_orgs_b b
2263 v_counter := pv_traversal_rarray.first;
2264 while v_counter is not null
2265 loop
2266 v_sql := v_sql || 'FEM_INTG_DIM_HIER_C' || v_counter || '_GT gt' || v_counter;
2267 if v_counter < pv_traversal_rarray.count then
2268 v_sql := v_sql || ', ';
2269 end if;
2270 v_counter := pv_traversal_rarray.next(v_counter);
2271 end loop;
2272 v_sql := v_sql || ', ' || pv_dim_memb_b_tab || ' b WHERE ';
2273
2274 -- construct sql for : gt1.child_leaf_flag = 'Y'
2275 v_counter := pv_traversal_rarray.first;
2276 while v_counter is not null
2277 loop
2278 v_sql := v_sql || ' gt' || v_counter || '.child_leaf_flag = ''Y''';
2279 if v_counter < pv_traversal_rarray.count then
2280 v_sql := v_sql || ' and ';
2281 end if;
2282 v_counter := pv_traversal_rarray.next(v_counter);
2283 end loop;
2284
2285 -- construct sql for : and b.CCTR_ORG_DISPLAY_CODE = gt1.child_display_code||'-'||gt2.child_display_code
2286 v_sql := v_sql || ' and b.' || pv_dim_memb_disp_col || ' = ';
2287 v_counter := v_concat_rarray.first;
2288 while v_counter is not null
2289 loop
2290 v_sql := v_sql || 'gt' || v_concat_rarray(v_counter) || '.child_display_code';
2291 if v_counter < v_concat_rarray.count then
2292 v_sql := v_sql || ' || ''-'' || ';
2293 end if;
2294 v_counter := v_concat_rarray.next(v_counter);
2295 end loop;
2296
2297 -- contruct SQL for : and b.value_set_id = :pv_dim_vs_id
2298 v_sql := v_sql || ' and b.value_set_id = :pv_dim_vs_id ';
2299
2300 -- the final )
2301 v_sql := v_sql || ')';
2302
2303 -- execute the SQL to insert the leaf nodes
2304 select count(*) into v_debug from FEM_INTG_DIM_HIER_GT;
2305
2306 FEM_ENGINES_PKG.Tech_Message(
2307 p_severity => pc_log_level_statement,
2308 p_module => 'fem.plsql.Bld_Hier_Multi_Segment1',
2309 p_msg_text => v_sql);
2310
2311 execute immediate v_sql using pv_dim_vs_id;
2312 COMMIT;
2313 -- walk up the concatenated hierarchy one-level-per-segment at a time
2314 v_hier_counter := pv_traversal_rarray.first;
2315 v_counter := 1;
2316 while v_hier_counter is not null
2317 loop
2318 loop
2319 /*
2320 Sample SQL stmt :
2321 INSERT INTO FEM_INTG_DIM_HIER_GT
2322 (HIERARCHY_OBJ_DEF_ID
2323 , child_display_code
2324 , child_id
2325 , parent_display_code
2326 , parent_id
2327 , child_depth_num
2328 , parent_depth_num
2329 , child_leaf_flag
2330 , single_depth_flag
2331 , display_order_num
2332 --, counter_num
2333 )'
2334 (SELECT distinct 20861
2335 , gtm.parent_display_code
2336 , gtm.parent_id
2337
2338
2339 ------------ before hierarchy rule performance fix
2340 , decode(1,
2341 1, gts.parent_display_code || '-' || substr(gtm.parent_display_code, instr(gtm.parent_display_code, '-')+1),
2342 5, substr(gtm.parent_display_code, 1, instr(gtm.parent_display_code, '-', 1, 5-1)-1) || '-' || gts.parent_display_code,
2343 substr(gtm.parent_display_code, 1, instr(gtm.parent_display_code, '-', 1, 0)-1) || '-' ||
2344 gts.parent_display_code || '-' ||
2345 substr(gtm.parent_display_code, instr(gtm.parent_display_code, '-', 1, 1)+1))
2346 ------------ before hierarchy rule performance fix
2347 ------------ after (one of the three)
2348 1. , gts.parent_display_code || '-' || substr(gtm.parent_display_code, instr(gtm.parent_display_code, '-')+1)
2349 2. , substr(gtm.parent_display_code, 1, instr(gtm.parent_display_code, '-', 1, 5-1)-1) || '-' || gts.parent_display_code
2350 3. , substr(gtm.parent_display_code, 1, instr(gtm.parent_display_code, '-', 1, 0)-1) || '-' ||
2351 gts.parent_display_code || '-' ||
2352 substr(gtm.parent_display_code, instr(gtm.parent_display_code, '-', 1, 1)+1))
2353 ------------ after (one of the three)
2354
2355
2356 , -1
2357 , gtm.parent_depth_num
2358 , gtm.parent_depth_num-1
2359 , 'N'
2360 , 'N'
2361 , -1
2362 --, 1
2363 FROM
2364 FEM_INTG_DIM_HIER_GT gtm
2365 , FEM_INTG_DIM_HIER_GT gts
2366 ------------ WHERE gtm.HIERARCHY_OBJ_DEF_ID = :pv_hier_obj_def_id
2367 ------------ AND gts.hierarchy_obj_def_id = :display_order
2368 ------------ AND gts.child_display_code =
2369 WHERE gts.child_display_code =
2370
2371 ------------ before hierarchy rule performance fix
2372 decode(1,
2373 1, substr(gtm.parent_display_code, 1, instr(gtm.parent_display_code, '-')-1),
2374 5, substr(gtm.parent_display_code, instr(gtm.parent_display_code, '-', 1, 0)+1),
2375 substr(gtm.parent_display_code, instr(gtm.parent_display_code, '-', 1, 0)+1,
2376 instr(gtm.parent_display_code, '-', 1, 1)-instr(gtm.parent_display_code, '-', 1, 0)-1))
2377 ------------ before hierarchy rule performance fix
2378 ------------ after (one of the three)
2379 1. substr(gtm.parent_display_code, 1, instr(gtm.parent_display_code, '-')-1)
2380 2. substr(gtm.parent_display_code, instr(gtm.parent_display_code, '-', 1, 0)+1)
2381 3. substr(gtm.parent_display_code, instr(gtm.parent_display_code, '-', 1, 0)+1,
2382 instr(gtm.parent_display_code, '-', 1, 1)-instr(gtm.parent_display_code, '-', 1, 0)-1))
2383 ------------ after (one of the three)
2384
2385
2386 AND gts.child_display_code <> gts.parent_display_code
2387 and not exists (select 1 from FEM_INTG_DIM_HIER_GT gte where gte.child_display_code = gtm.parent_display_code)
2388 */
2389 v_sql := 'INSERT INTO FEM_INTG_DIM_HIER_GT
2390 (HIERARCHY_OBJ_DEF_ID
2391 , child_display_code
2392 , child_id
2393 , parent_display_code
2394 , parent_id
2395 , child_depth_num
2396 , parent_depth_num
2397 , child_leaf_flag
2398 , single_depth_flag
2399 , display_order_num
2400 )
2401 (SELECT distinct ' || pv_hier_obj_def_id || '
2402 , gtm.parent_display_code
2403 , gtm.parent_id
2404 , ';
2405
2406 IF v_hier_counter = v_concat_rarray(v_concat_rarray.first) THEN
2407 v_sql := v_sql || 'gts.parent_display_code || ''-'' || substr(gtm.parent_display_code, instr(gtm.parent_display_code, ''-'')+1)';
2408 ELSIF v_hier_counter = v_concat_rarray(v_concat_rarray.last) THEN
2409 v_sql := v_sql || 'substr(gtm.parent_display_code, 1, instr(gtm.parent_display_code, ''-'', 1, ' || v_concat_rarray.count || '-1)-1) || ''-'' || gts.parent_display_code';
2410 ELSE
2411 v_sql := v_sql || 'substr(gtm.parent_display_code, 1, instr(gtm.parent_display_code, ''-'', 1, ' ||
2412 (pv_traversal_rarray(v_hier_counter).concat_segment-1) ||
2413 ')-1) || ''-'' || gts.parent_display_code || ''-'' || substr(gtm.parent_display_code, instr(gtm.parent_display_code, ''-'', 1, ' ||
2414 pv_traversal_rarray(v_hier_counter).concat_segment ||
2415 ')+1)';
2416 END IF;
2417
2418 v_sql := v_sql || '
2419 , -1
2420 , gtm.parent_depth_num
2421 , gtm.parent_depth_num-1
2422 , ''N''
2423 , ''N''
2424 , -1
2425 FROM
2426 FEM_INTG_DIM_HIER_GT gtm
2427 , FEM_INTG_DIM_HIER_C' || pv_traversal_rarray(v_hier_counter).display_order || '_GT gts
2428 WHERE gts.child_display_code = ';
2429
2430 IF v_hier_counter = v_concat_rarray(v_concat_rarray.first) THEN
2431 v_sql := v_sql || 'substr(gtm.parent_display_code, 1, instr(gtm.parent_display_code, ''-'')-1)';
2432 ELSIF v_hier_counter = v_concat_rarray(v_concat_rarray.last) THEN
2433 v_sql := v_sql || 'substr(gtm.parent_display_code, instr(gtm.parent_display_code, ''-'', 1, ' || (pv_traversal_rarray(v_hier_counter).concat_segment-1) || ')+1)';
2434 ELSE
2435 v_sql := v_sql ||
2436 'substr(gtm.parent_display_code, instr(gtm.parent_display_code, ''-'', 1, ' ||
2437 (pv_traversal_rarray(v_hier_counter).concat_segment-1) ||
2438 ')+1, instr(gtm.parent_display_code, ''-'', 1, ' ||
2439 (pv_traversal_rarray(v_hier_counter).concat_segment) ||
2440 ')-instr(gtm.parent_display_code, ''-'', 1, ' ||
2441 (pv_traversal_rarray(v_hier_counter).concat_segment-1) ||
2442 ')-1)';
2443 END IF;
2444
2445 v_sql := v_sql || '
2446 AND gts.child_display_code <> gts.parent_display_code
2447 and not exists (select 1 from FEM_INTG_DIM_HIER_GT gte where gte.child_display_code = gtm.parent_display_code))';
2448 select count(*) into v_debug from FEM_INTG_DIM_HIER_GT;
2449
2450 FEM_ENGINES_PKG.Tech_Message(
2451 p_severity => pc_log_level_statement,
2452 p_module => 'fem.plsql.Bld_Hier_Multi_Segment2',
2453 p_msg_text => v_sql);
2454
2455 execute immediate v_sql;
2456 v_counter := v_counter + 1;
2457 exit when SQL%ROWCOUNT = 0;
2458 select count(*) into v_debug from FEM_INTG_DIM_HIER_GT;
2459 end loop;
2460 v_hier_counter := pv_traversal_rarray.next(v_hier_counter);
2461 end loop;
2462 -- insert the SQL for the top member
2463 INSERT INTO FEM_INTG_DIM_HIER_GT
2464 (HIERARCHY_OBJ_DEF_ID
2465 , child_display_code
2466 , child_id
2467 , parent_display_code
2468 , parent_id
2469 , child_depth_num
2470 , parent_depth_num
2471 , child_leaf_flag
2472 , single_depth_flag
2473 , display_order_num
2474 )
2475 (SELECT distinct pv_hier_obj_def_id
2476 , gtm.parent_display_code
2477 , gtm.parent_id
2478 , gtm.parent_display_code
2479 , gtm.parent_id
2480 , gtm.parent_depth_num
2481 , gtm.parent_depth_num
2482 , 'N'
2483 , 'N'
2484 , -1
2485 FROM FEM_INTG_DIM_HIER_GT gtm
2486 WHERE not exists (select 1 from FEM_INTG_DIM_HIER_GT gte where gte.child_display_code = gtm.parent_display_code));
2487 -- Processing for creating level based hierarchies
2488 v_counter :=1;
2489 -- 17OCT2005 BUG 4681970
2490 -- CREATE sequence for display order
2491 v_seq_name := 'FEM_INTG_HIER_SEQ_' || pv_req_id || '_S';
2492 v_seq_stmt := 'CREATE SEQUENCE '||v_seq_name||' START WITH 1';
2493 EXECUTE IMMEDIATE v_seq_stmt;
2494 COMMIT;
2495 loop
2496 v_dim_group_name_seq := v_counter*100;
2497 -- Call API to create new dimension group
2498 --dedutta : 5035567 : if check for pv_sequence_enforced_flag
2499 IF (pv_sequence_enforced_flag = 'Y') THEN
2500 FEM_DIM_GROUPS_UTIL_PKG.create_dim_group
2501 (x_return_status => v_API_return_status,
2502 x_msg_count => v_msg_count,
2503 x_msg_data => v_msg_data,
2504 p_encoded => FND_API.G_FALSE,
2505 p_init_msg_list => FND_API.G_TRUE,
2506 x_dimension_group_id => v_dimension_group_id,
2507 x_dim_group_sequence => v_dim_group_seq,
2508 p_dimension_varchar_label => FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label,
2509 p_dim_group_name => FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label ||' '|| v_dim_group_name_seq,
2510 p_dim_group_display_code => FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label ||' '|| v_dim_group_name_seq,
2511 p_dim_group_description => FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label ||' '|| v_dim_group_name_seq
2512 );
2513 END IF;
2514 IF (v_API_return_status NOT IN ('S')) THEN
2515 FEM_ENGINES_PKG.Tech_Message
2516 (p_severity => pc_log_level_statement,
2517 p_module => 'fem.plsql.fem_intg_hier_rule_eng_pkg.'||'return_status',
2518 p_msg_text => 'v_API_return_status:' || v_API_return_status);
2519 FEM_ENGINES_PKG.Tech_Message
2520 (p_severity => pc_log_level_statement,
2521 p_module => 'fem.plsql.fem_intg_hier_rule_eng_pkg.'||'After create_dim_group',
2522 p_msg_text => 'v_msg_data:' || v_msg_data);
2523 FEM_ENGINES_PKG.User_Message
2524 (p_app_name => 'FEM',
2525 p_msg_text => v_msg_data);
2526 FEM_ENGINES_PKG.User_Message
2527 (p_app_name => 'FEM',
2528 p_msg_name => 'FEM_INTG_FAIL_DIM_GRP');
2529 RAISE FEM_INTG_fatal_err;
2530 END IF;
2531 -- insert the new level to fem_hier_dimension_grps
2532 -- 14OCT2005 b4652450 MOVED HERE WAS AFTER fem_hier_dimension_grps INSERT
2533 -- update the _GT table with the dimension_group_id
2534 update fem_intg_dim_hier_gt
2535 set dimension_group_id = v_dimension_group_id
2536 where child_depth_num = v_counter;
2537 if SQL%ROWCOUNT <= 0 then
2538 exit;
2539 end if;
2540 -- 17OCT2005 BUG 4681970
2541 v_sql_stmt :=
2542 'UPDATE fem_intg_dim_hier_gt
2543 SET display_order_num = '||v_seq_name||'.nextval
2544 WHERE child_depth_num = '||v_counter||' AND display_order_num = -1';
2545
2546 FEM_ENGINES_PKG.Tech_Message(
2547 p_severity => pc_log_level_statement,
2548 p_module => 'fem.plsql.Bld_Hier_Multi_Segment3',
2549 p_msg_text => v_sql_stmt);
2550
2551 EXECUTE IMMEDIATE v_sql_stmt;
2552 COMMIT;
2553 IF (pv_new_hier_obj_created) THEN
2554 FEM_ENGINES_PKG.Tech_Message(
2555 p_severity => pc_log_level_statement,
2556 p_module => 'fem.plsql.'||'insert into fem_hier_dimension_grps',
2557 p_msg_text => 'v_dimension_group_id:' || v_dimension_group_id || ' v_rel_dim_group_seq:' || v_counter);
2558 -- 14OCT2005 b4652450 first is already added from call in Main()
2559 if v_counter > 1 then
2560 --dedutta : 5035567 : if check for pv_sequence_enforced_flag
2561 IF (pv_sequence_enforced_flag = 'Y') THEN
2562 INSERT INTO fem_hier_dimension_grps
2563 (dimension_group_id,
2564 hierarchy_obj_id,
2565 relative_dimension_group_seq,
2566 creation_date,
2567 created_by,
2568 last_updated_by,
2569 last_update_date,
2570 last_update_login,
2571 object_version_number)
2572 VALUES
2573 (v_dimension_group_id,
2574 pv_hier_obj_id,
2575 v_counter,
2576 sysdate,
2577 pv_user_id,
2578 pv_user_id,
2579 sysdate,
2580 pv_login_id,
2581 1);
2582 END IF;
2583 end if;
2584 END IF;
2585 v_counter := v_counter + 1;
2586 end loop;
2587 -- 17OCT2005 BUG 4681970
2588 v_seq_stmt := 'DROP SEQUENCE ' || v_seq_name;
2589 EXECUTE IMMEDIATE v_seq_stmt;
2590 COMMIT;
2591 FEM_INTG_NEW_DIM_MEMBER_PKG.Detail_Multi_Segment (v_completion_code, v_dim_process_row_cnt, 'HIER_MULTI_SEG');
2592 IF v_completion_code <> 0
2593 THEN
2594 raise_application_error(-20001, fnd_message.get);
2595 END IF;
2596 -- update the hier GT table with the member_ids
2597 v_sql := 'update FEM_INTG_DIM_HIER_GT gt
2598 set gt.parent_id = (select distinct b1.' || fem_intg_new_dim_member_pkg.pv_local_member_col || ' from ' || FEM_INTG_DIM_RULE_ENG_PKG.pv_member_b_table_name || ' b1 ';
2599 v_sql := v_sql || ' where b1.' || FEM_INTG_DIM_RULE_ENG_PKG.pv_member_display_code_col || ' = gt.parent_display_code and b1.VALUE_SET_ID = :pv_dim_vs_id)';
2600 v_sql := v_sql || ', gt.child_id = (select distinct b2.' || fem_intg_new_dim_member_pkg.pv_local_member_col || ' from ' || FEM_INTG_DIM_RULE_ENG_PKG.pv_member_b_table_name || ' b2 ';
2601 v_sql := v_sql || ' where b2.' || FEM_INTG_DIM_RULE_ENG_PKG.pv_member_display_code_col || ' = gt.child_display_code and b2.VALUE_SET_ID = :pv_dim_vs_id)';
2602
2603 FEM_ENGINES_PKG.Tech_Message(
2604 p_severity => pc_log_level_statement,
2605 p_module => 'fem.plsql.Bld_Hier_Multi_Segment4',
2606 p_msg_text => v_sql);
2607
2608 execute immediate v_sql using pv_dim_vs_id, pv_dim_vs_id;
2609 COMMIT;
2610 x_completion_code := PC_SUCCESS;
2611 return;
2612 EXCEPTION
2613 WHEN others THEN
2614 x_completion_code := PC_FAILURE;
2615 END Bld_Hier_Multi_segment;
2616 /*****************************************************************
2617 * PUBLIC PROCEDURES *
2618 *****************************************************************/
2619 -- ======================================================================
2620 -- Procedure
2621 -- Main
2622 -- Purpose
2623 -- This routine is the Main of the FEM_INTG_HIER_RULE_ENG_PKG
2624 -- History
2625 -- 10-28-04 Jee Kim Created
2626 -- 10-20-05 A.Budnik Modification for MultiSeg case.
2627 -- Arguments
2628 -- x_errbuf Standard Concurrent Program parameter
2629 -- x_retcode Standard Concurrent Program parameter
2630 -- p_hier_rule_obj_def_id Hierarchy rule version ID
2631 -- ======================================================================
2632 PROCEDURE Main (x_errbuf OUT NOCOPY VARCHAR2,
2633 x_retcode OUT NOCOPY VARCHAR2,
2634 p_hier_rule_obj_def_id IN NUMBER) IS
2635 FEM_INTG_fatal_err EXCEPTION;
2636 TYPE DimensionGroupID_cursor IS REF CURSOR;
2637 DimensionGroupID DimensionGroupID_cursor;
2638 pv_pgm_id CONSTANT NUMBER := FND_GLOBAL.Conc_Program_Id;
2639 pv_pgm_app_id CONSTANT NUMBER := FND_GLOBAL.Prog_Appl_ID;
2640 v_msg_count NUMBER;
2641 v_msg_data VARCHAR2(4000);
2642 v_API_return_status VARCHAR2(30);
2643 v_compl_code NUMBER;
2644 v_row_count_tot NUMBER;
2645 v_err_count_tot NUMBER := 0;
2646 v_duplicate_parent VARCHAR2(30);
2647 v_ret_status BOOLEAN;
2648 v_sql_stmt VARCHAR2(2000);
2649 v_data_edit_lock_exists VARCHAR2(30);
2650 v_rowcount NUMBER;
2651 v_dim_group_conflict BOOLEAN := FALSE;
2652 v_child_display_code VARCHAR2(150);
2653 l_temp_top_dimension_group_id NUMBER;
2654 -- bug fix 4563603
2655 v_display_code VARCHAR2(150);
2656 v_parent_display_code VARCHAR2(150);
2657 v_offending_parents_list VARCHAR2(4000);
2658 CURSOR c_child_of_multi_parent IS
2659 SELECT gt.child_display_code
2660 FROM fem_intg_dim_hier_gt gt
2661 WHERE gt.parent_display_code <> gt.child_display_code
2662 and gt.hierarchy_obj_def_id = pv_hier_obj_def_id
2663 GROUP BY gt.child_display_code
2664 HAVING count(gt.child_display_code) > 1;
2665
2666 CURSOR c_multi_parent (p_child_display_code VARCHAR2) IS
2667 SELECT DISTINCT gt.parent_display_code
2668 FROM fem_intg_dim_hier_gt gt
2669 WHERE gt.parent_display_code <> gt.child_display_code
2670 and gt.hierarchy_obj_def_id = pv_hier_obj_def_id
2671 and gt.child_display_code = p_child_display_code;
2672 -- bug fix 4563603
2673 BEGIN
2674 -- Main
2675 -- 1. Get the hierarchy rule object ID associated with the rule version
2676 -- 2. Initaillize the requried variables - call Init()
2677 -- 3. If the hierarchy rule has never been processed before.
2678 -- (1) Create Object - FEM_Dim_Hier_Util_Pkg.New_Hier_Object
2679 -- (2) Update hierarchy_obj_id
2680 -- (3) Update fem_intg_hier_def_segs.hier_obj_def_id
2681 -- ELSIF the version has not been run before
2682 -- (1) Create Object Definition - FEM_Dim_Hier_Util_Pkg.New_Hier_Object_Def
2683 -- (2) Update fem_intg_hier_def_segs.hier_obj_def_id
2684 -- ELSE
2685 -- (1) Call FEM_PL_PKG.Obj_Def_Data_Edit_Lock_Exists
2686 -- 4.If (pv_dim_mapping_option_code = SINGLESEG) then
2687 -- Call Bld_Hier_Single_Segment( )
2688 -- 5. Check if any children within the hierarchy are assigned to
2689 -- multiple parents.
2690 -- 6. IF v_new_hier_obj_def_created = TRUE THEN
2691 -- Call INSERT statement to copy hierarchy structure from the
2692 -- global temporary table FEM_INTG_DIM_HIER_GT to the FEM hierarchy table
2693 -- ELSE
2694 -- Call DELETE statement to delete existing hierarchy structure
2695 -- for the hierarchy object definition.
2696 -- Call INSERT statement to copy hierarchy structure from the
2697 -- global temporary table FEM_INTG_DIM_HIER_GT to the FEM hierarchy table
2698 -- 7. Call routine FEM_INTG_PL_PKG.Final_Process_Logging( ) to complete
2699 -- final process logging. Message name to print will be
2700 -- FEM_INTG_PROC_SUCCESS and the number of output rows will be the
2701 -- return row count from the hierarchy building routine
2702 -- 8. Call the Concurrent Program DHMHVFLW to flatten out every hierarchy
2703 -- version after it has been pushed in FEM
2704 FEM_ENGINES_PKG.Tech_Message
2705 (p_severity => pc_log_level_procedure,
2706 p_module => 'fem.plsql.fem_intg.hier_eng.main',
2707 p_app_name => 'FEM',
2708 p_msg_name => 'FEM_GL_POST_201',
2709 p_token1 => 'FUNC_NAME',
2710 p_value1 => 'FEM_INTG_HIER_RULE_ENG_PKG.Main',
2711 p_token2 => 'TIME',
2712 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2713 BEGIN
2714 -- obtain the hierarchy rule object ID associated
2715 -- with the rule version
2716 SELECT o.object_id, o.folder_id, b.object_definition_id,
2717 b.effective_start_date, b.effective_end_date
2718 INTO pv_hier_rule_obj_id,
2719 pv_folder_id,
2720 pv_hier_rule_obj_def_id,
2721 pv_hier_rule_start_date,
2722 pv_hier_rule_end_date
2723 FROM fem_object_definition_b b,
2724 fem_object_catalog_b o
2725 WHERE b.object_definition_id = p_hier_rule_obj_def_id
2726 AND o.object_id = b.object_id
2727 AND o.object_type_code='OGL_INTG_HIER_RULE';
2728 EXCEPTION
2729 -- p_hier_rule_obj_def_id is invalid
2730 WHEN NO_DATA_FOUND THEN
2731 FEM_ENGINES_PKG.Tech_Message
2732 (p_severity => pc_log_level_procedure,
2733 p_module => 'fem.plsql.fem_intg.hier_eng.main.no_data_found',
2734 p_app_name => 'FEM',
2735 p_msg_name => 'FEM_INTG_HIER_OBJ_NOTFOUND_ERR',
2736 p_token1 => 'ERR_MSG',
2737 p_value1 => SQLERRM);
2738 FEM_ENGINES_PKG.User_Message
2739 (p_app_name => 'FEM',
2740 p_msg_name => 'FEM_INTG_HIER_OBJ_NOTFOUND_ERR',
2741 p_token1 => 'ERR_MSG',
2742 p_value1 => SQLERRM);
2743 RAISE FEM_INTG_fatal_err;
2744 END;
2745 FEM_ENGINES_PKG.Tech_Message
2746 (p_severity => pc_log_level_statement,
2747 p_module => 'fem.plsql.fem_intg_hier_rule_eng_pkg',
2748 p_msg_text => 'pv_user_id:' || pv_user_id
2749 ||' pv_folder_id:'||pv_folder_id);
2750
2751 FEM_ENGINES_PKG.Tech_Message
2752 (p_severity => pc_log_level_statement,
2753 p_module => 'fem.plsql.fem_intg_hier_rule_eng_pkg',
2754 p_msg_text => 'pv_hier_rule_obj_id:' || pv_hier_rule_obj_id
2755 ||' pv_hier_rule_obj_def_id:'|| pv_hier_rule_obj_def_id);
2756
2757 FEM_INTG_PL_PKG.Register_Process_Execution
2758 (p_obj_id => pv_hier_rule_obj_id,
2759 p_obj_def_id => pv_hier_rule_obj_def_id,
2760 p_req_id => pv_req_id,
2761 p_user_id => pv_user_id,
2762 p_login_id => pv_login_id,
2763 p_pgm_id => pv_pgm_id,
2764 p_pgm_app_id => pv_pgm_app_id,
2765 p_module_name => 'fem.plsql.fem_intg_hier_eng_pkg.' ||
2766 'register_process_execution',
2767 p_hierarchy_name => 'Hierarchy for Rule ' ||pv_hier_rule_obj_name,
2768 x_completion_code => v_compl_code);
2769 IF (v_compl_code = 2) THEN
2770 RAISE FEM_INTG_fatal_err;
2771 END IF;
2772 -- Initialize package variables
2773 Init
2774 (p_hier_rule_obj_def_id => p_hier_rule_obj_def_id,
2775 x_completion_code => v_compl_code);
2776 IF (v_compl_code = 2) THEN
2777 RAISE FEM_INTG_fatal_err;
2778 END IF;
2779 -- to create level-based hierarchy
2780 -- Call API to create new dimension group
2781 -- 14OCT2005 b4652450 removed space afte 100 in call
2782 --dedutta : 5035567 : if check for pv_sequence_enforced_flag
2783 IF (pv_sequence_enforced_flag = 'Y') THEN
2784 FEM_DIM_GROUPS_UTIL_PKG.create_dim_group
2785 (x_return_status => v_API_return_status,
2786 x_msg_count => v_msg_count,
2787 x_msg_data => v_msg_data,
2788 p_encoded => FND_API.G_FALSE,
2789 p_init_msg_list => FND_API.G_TRUE,
2790 x_dimension_group_id => pv_top_dimension_group_id,
2791 x_dim_group_sequence => v_dim_group_seq,
2792 p_dimension_varchar_label =>
2793 FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label,
2794 p_dim_group_name =>
2795 FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label || ' 100',
2796 p_dim_group_display_code =>
2797 FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label || ' 100',
2798 p_dim_group_description =>
2799 FEM_INTG_DIM_RULE_ENG_PKG.pv_dim_varchar_label || ' 100');
2800 END IF;
2801 FEM_ENGINES_PKG.Tech_Message
2802 (p_severity => pc_log_level_procedure,
2803 p_module => 'fem.plsql.fem_intg_hier_eng.Infinite loop.',
2804 p_msg_text => 'pv_top_dimension_group_id:' || pv_top_dimension_group_id);
2805 IF (v_API_return_status NOT IN ('S')) THEN
2806 FEM_ENGINES_PKG.Tech_Message
2807 (p_severity => pc_log_level_statement,
2808 p_module => 'fem.plsql.fem_intg_hier_rule_eng_pkg.'||'return_status',
2809 p_msg_text => 'v_API_return_status:' || v_API_return_status);
2810 FEM_ENGINES_PKG.Tech_Message
2811 (p_severity => pc_log_level_statement,
2812 p_module => 'fem.plsql.fem_intg_hier_rule_eng_pkg.'||'After create_dim_group',
2813 p_msg_text => 'v_msg_data:' || v_msg_data);
2814 FEM_ENGINES_PKG.User_Message
2815 (p_app_name => 'FEM',
2816 p_msg_text => v_msg_data);
2817 FEM_ENGINES_PKG.User_Message
2818 (p_app_name => 'FEM',
2819 p_msg_name => 'FEM_INTG_FAIL_DIM_GRP');
2820 RAISE FEM_INTG_fatal_err;
2821 END IF;
2822 IF (pv_sequence_enforced_flag = 'N') THEN
2823 pv_grp_seq_code := 'NO_GROUPS';
2824 l_temp_top_dimension_group_id := NULL;
2825 ElSE
2826 pv_grp_seq_code := 'SEQUENCE_ENFORCED_SKIP_LEVEL';
2827 l_temp_top_dimension_group_id := pv_top_dimension_group_id;
2828 END IF;
2829 -- This hierarchy rule has never been processed before,
2830 -- then create hier object.
2831 IF (pv_hier_obj_id = -1) THEN
2832 FEM_Dim_Hier_Util_Pkg.New_Hier_Object
2833 (p_api_version => pc_api_version,
2834 p_commit => 'T',
2835 p_encoded => FND_API.G_FALSE,
2836 p_init_msg_list => FND_API.G_TRUE,
2837 x_return_status => v_API_return_status,
2838 x_msg_count => v_msg_count,
2839 x_msg_data => v_msg_data,
2840 x_hier_obj_id => pv_hier_obj_id,
2841 x_hier_obj_def_id => pv_hier_obj_def_id,
2842 p_folder_id => pv_folder_id,
2843 p_global_vs_combo_id => pv_gvsc_id,
2844 p_object_access_code => 'R',
2845 p_object_origin_code => 'USER',
2846 p_object_name => 'Hierarchy for Rule ' || substr(pv_hier_rule_obj_name,1,100),
2847 p_description => NULL,
2848 p_effective_start_date => pv_hier_rule_start_date,
2849 p_effective_end_date => pv_hier_rule_end_date,
2850 p_obj_def_name => 'Hierarchy for Rule ' || substr(pv_hier_rule_obj_name,1,100) ||' '|| to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'),
2851 p_dimension_id => pv_dim_id,
2852 p_hier_type_code => 'OPEN',
2853 p_grp_seq_code => pv_grp_seq_code,
2854 p_multi_top_flg => 'N',
2855 p_fin_ctg_flg => 'N',
2856 p_multi_vs_flg => 'N',
2857 p_hier_usage_code => 'STANDARD',
2858 p_val_set_id1 => pv_dim_vs_id,
2859 p_dim_grp_id1 => l_temp_top_dimension_group_id,
2860 p_flat_rows_flag => pv_flatten_hier_flag);
2861 IF (v_API_return_status NOT IN ('S')) THEN
2862 FEM_ENGINES_PKG.Tech_Message
2863 (p_severity => pc_log_level_statement,
2864 p_module => 'fem.plsql.fem_intg_hier_rule_eng_pkg.'||'return_status',
2865 p_msg_text => 'v_API_return_status:' || v_API_return_status);
2866 FEM_ENGINES_PKG.Tech_Message
2867 (p_severity => pc_log_level_statement,
2868 p_module => 'fem.plsql.fem_intg_hier_eng.' ||'after New_Hier_Object',
2869 p_msg_text => 'v_msg_data:' || v_msg_data);
2870 FEM_ENGINES_PKG.User_Message
2871 (p_app_name => 'FEM',
2872 p_msg_text => v_msg_data);
2873 FEM_ENGINES_PKG.User_Message
2874 (p_app_name => 'FEM',
2875 p_msg_name => 'FEM_INTG_FAIL_NEW_HIER');
2876 RAISE FEM_INTG_fatal_err;
2877 END IF;
2878 pv_new_hier_obj_created := TRUE;
2879 FEM_ENGINES_PKG.Tech_Message
2880 (p_severity => pc_log_level_statement,
2881 p_module => 'fem.plsql.fem_intg_hier_eng.' ||'after New_Hier_Object',
2882 p_msg_text => 'pv_hier_obj_id:' || pv_hier_obj_id||
2883 ' pv_hier_obj_def_id:' || pv_hier_obj_def_id);
2884 -- Update hierarchy_obj_id
2885 UPDATE fem_intg_hier_rules
2886 SET hierarchy_obj_id = pv_hier_obj_id,
2887 last_updated_by = pv_user_id,
2888 last_update_date = sysdate,
2889 last_update_login = pv_login_id
2890 WHERE hier_rule_obj_id = pv_hier_rule_obj_id;
2891 -- Update fem_hierarchies
2892 UPDATE fem_hierarchies
2893 SET value_set_id = pv_dim_vs_id
2894 WHERE hierarchy_obj_id = pv_hier_obj_id;
2895 -- Update hierarchy_obj_id
2896 UPDATE fem_intg_hier_def_segs
2897 SET hier_obj_def_id = pv_hier_obj_def_id,
2898 last_updated_by = pv_user_id,
2899 last_update_date = sysdate,
2900 last_update_login = pv_login_id
2901 WHERE hier_rule_obj_def_id = pv_hier_rule_obj_def_id
2902 AND display_order_num = 1;
2903 v_new_hier_obj_def_created := TRUE;
2904 COMMIT;
2905 ELSIF (pv_hier_obj_def_id = -1) THEN
2906 -- If the rule version has never been run before,
2907 -- create a new version of the hierarchy.
2908 FEM_Dim_Hier_Util_Pkg.New_Hier_Object_Def
2909 (p_api_version => pc_api_version,
2910 p_commit => 'T',
2911 p_encoded => FND_API.G_FALSE,
2912 p_init_msg_list => FND_API.G_TRUE,
2913 x_return_status => v_API_return_status,
2914 x_msg_count => v_msg_count,
2915 x_msg_data => v_msg_data,
2916 x_hier_obj_def_id => pv_hier_obj_def_id,
2917 p_hier_obj_id => pv_hier_obj_id,
2918 p_obj_def_name => 'Hierarchy for Rule ' || substr(pv_hier_rule_obj_name,1,100) ||' '|| to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'),
2919 p_effective_start_date => pv_hier_rule_start_date,
2920 p_effective_end_date => pv_hier_rule_end_date,
2921 p_object_origin_code => 'USER');
2922 IF (v_API_return_status NOT IN ('S')) THEN
2923 FEM_ENGINES_PKG.Tech_Message
2924 (p_severity => pc_log_level_statement,
2925 p_module => 'fem.plsql.fem_intg_hier_rule_eng_pkg.'||'return_status',
2926 p_msg_text => 'v_API_return_status:' || v_API_return_status);
2927 FEM_ENGINES_PKG.Tech_Message
2928 (p_severity => pc_log_level_statement,
2929 p_module => 'fem.plsql.fem_intg_hier_eng.' ||'after New_Hier_Object_Def',
2930 p_msg_text => 'v_msg_data:' || v_msg_data);
2931 FEM_ENGINES_PKG.User_Message
2932 (p_app_name => 'FEM',
2933 p_msg_text => v_msg_data);
2934 FEM_ENGINES_PKG.User_Message
2935 (p_app_name => 'FEM',
2936 p_msg_name => 'FEM_INTG_FAIL_NEW_HIER');
2937 RAISE FEM_INTG_fatal_err;
2938 END IF;
2939 FEM_ENGINES_PKG.Tech_Message
2940 (p_severity => pc_log_level_statement,
2941 p_module => 'fem.plsql.fem_intg_hier_eng.' ||'after New_Hier_Object_Def',
2942 p_msg_text => 'pv_hier_obj_id:' || pv_hier_obj_id||
2943 ' pv_hier_obj_def_id:' || pv_hier_obj_def_id);
2944 -- Update hierarchy obj def id
2945 UPDATE fem_intg_hier_def_segs
2946 SET hier_obj_def_id = pv_hier_obj_def_id,
2947 last_updated_by = pv_user_id,
2948 last_update_date = sysdate,
2949 last_update_login = pv_login_id
2950 WHERE hier_rule_obj_def_id = pv_hier_rule_obj_def_id
2951 AND display_order_num = 1;
2952 v_new_hier_obj_def_created := TRUE;
2953 COMMIT;
2954 ELSE
2955 -- the hierarchy object definition already exists,
2956 -- check for the data edit locks for overwrite.
2957 FEM_PL_PKG.Obj_Def_Data_Edit_Lock_Exists(
2958 p_object_definition_id => pv_hier_obj_def_id,
2959 x_data_edit_lock_exists => v_data_edit_lock_exists);
2960 IF (v_data_edit_lock_exists = 'T') THEN
2961 FEM_ENGINES_PKG.User_Message
2962 (p_app_name => 'FEM',
2963 p_msg_name => 'FEM_INTG_DATA_LOCK_EXIST');
2964 RAISE FEM_INTG_fatal_err;
2965 END IF;
2966 END IF;
2967 IF (pv_dim_mapping_option_code = 'SINGLESEG') THEN
2968 FEM_ENGINES_PKG.Tech_Message
2969 (p_severity => pc_log_level_statement,
2970 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Hier_Single_Segment',
2971 p_msg_text => 'pv_dim_mapping_option_code:' || pv_dim_mapping_option_code);
2972 Bld_Hier_Single_Segment
2973 (x_completion_code => v_compl_code,
2974 x_row_count_tot => v_row_count_tot);
2975
2976 IF (v_compl_code = 2) THEN
2977 RAISE FEM_INTG_fatal_err;
2978 END IF;
2979 -- MULTISEG Hierarchy case
2980 ELSIF (pv_dim_mapping_option_code = 'MULTISEG') THEN
2981 FEM_ENGINES_PKG.Tech_Message
2982 (p_severity => pc_log_level_statement,
2983 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_component_Hiers',
2984 p_msg_text => 'pv_dim_mapping_option_code:' || pv_dim_mapping_option_code);
2985 -- Build single segment component hierarchies as basis for concatenated hierarcy
2986 Bld_component_Hiers
2987 (x_completion_code => v_compl_code);
2988 IF (v_compl_code = 2) THEN
2989 RAISE FEM_INTG_fatal_err;
2990 END IF;
2991
2992 -- Build Multi segment hierarchy using above componenets
2993 Bld_Hier_Multi_Segment (x_completion_code => v_compl_code);
2994
2995 IF (v_compl_code = 2) THEN
2996 RAISE FEM_INTG_fatal_err;
2997 END IF;
2998 ELSE
2999 -- Not single seg or multi seg so raise error
3000 FEM_ENGINES_PKG.Tech_Message
3001 (p_severity => pc_log_level_procedure,
3002 p_module => 'fem.plsql.fem_intg_hier_eng.main.',
3003 p_app_name => 'FEM',
3004 p_msg_name => 'FEM_INTG_HIER_MULTISEG_ERR',
3005 p_token1 => 'VAR_NAME',
3006 p_value1 => 'pv_dim_mapping_option_code',
3007 p_token2 => 'VAR_VAL',
3008 p_value2 => pv_dim_mapping_option_code);
3009 FEM_ENGINES_PKG.User_Message
3010 (p_app_name => 'FEM',
3011 p_msg_name => 'FEM_INTG_HIER_MULTISEG_ERR');
3012 RAISE FEM_INTG_fatal_err;
3013 END IF;
3014 -- Check if any children within the hierarchy are assigned to
3015 -- multiple parents. Just single segment case here. For multi seg case
3016 -- this is done in bld_component_hiers in loop for each component hier
3017 IF (pv_dim_mapping_option_code = 'SINGLESEG') THEN
3018 BEGIN
3019 SELECT 'Duplicate'
3020 INTO v_duplicate_parent
3021 FROM dual
3022 WHERE EXISTS
3023 (SELECT gt.child_id
3024 FROM fem_intg_dim_hier_gt gt
3025 WHERE gt.parent_id <> gt.child_id
3026 GROUP BY gt.child_id
3027 HAVING count(gt.child_id) > 1);
3028 EXCEPTION
3029 WHEN NO_DATA_FOUND THEN
3030 null;
3031 END;
3032 IF (v_duplicate_parent = 'Duplicate') THEN
3033 FEM_ENGINES_PKG.Tech_Message
3034 (p_severity => pc_log_level_procedure,
3035 p_module => 'fem.plsql.fem_intg_hier_eng.main.' || 'duplicate',
3036 p_app_name => 'FEM',
3037 p_msg_name => 'FEM_INTG_HIER_MULTI_PARENT_ERR',
3038 p_token1 => 'VAR_NAME',
3039 p_value1 => 'v_duplicate_parent',
3040 p_token2 => 'VAR_VAL',
3041 p_value2 => v_duplicate_parent);
3042 FEM_ENGINES_PKG.User_Message
3043 (p_app_name => 'FEM',
3044 p_msg_name => 'FEM_INTG_HIER_MULTI_PARENT_ERR');
3045 --bug fix 4563603
3046 FEM_ENGINES_PKG.Tech_Message
3047 (p_severity => pc_log_level_procedure,
3048 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Component_Hier.' || 'duplicate',
3049 p_app_name => 'FEM',
3050 p_msg_text => ' ');
3051
3052 FEM_ENGINES_PKG.User_Message
3053 (p_app_name => 'FEM',
3054 p_msg_text => ' ');
3055
3056 FEM_ENGINES_PKG.Tech_Message
3057 (p_severity => pc_log_level_procedure,
3058 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Component_Hier.' || 'duplicate',
3059 p_app_name => 'FEM',
3060 p_msg_name => 'FEM_INTG_HIER_MULT_PARENTS');
3061
3062 FEM_ENGINES_PKG.User_Message
3063 (p_app_name => 'FEM',
3064 p_msg_name => 'FEM_INTG_HIER_MULT_PARENTS');
3065
3066 OPEN c_child_of_multi_parent;
3067 LOOP
3068 FETCH c_child_of_multi_parent INTO v_display_code;
3069 EXIT WHEN c_child_of_multi_parent%NOTFOUND;
3070
3071 --Bug fix 5577544
3072 v_offending_parents_list := NULL;
3073
3074 OPEN c_multi_parent(v_display_code);
3075 LOOP
3076 FETCH c_multi_parent INTO v_parent_display_code;
3077 EXIT WHEN c_multi_parent%NOTFOUND;
3078 v_offending_parents_list := v_offending_parents_list || v_parent_display_code || ', ';
3079 END LOOP;
3080 CLOSE c_multi_parent;
3081 v_offending_parents_list := SUBSTR(v_offending_parents_list,1,LENGTH(v_offending_parents_list)-2);
3082
3083 FEM_ENGINES_PKG.Tech_Message
3084 (p_severity => pc_log_level_procedure,
3085 p_module => 'fem.plsql.fem_intg.hier_eng.',
3086 p_app_name => 'FEM',
3087 p_msg_name => 'FEM_INTG_HIER_PARENT_CHILD_LST',
3088 p_token1 => 'CHILD',
3089 p_value1 => v_display_code,
3090 p_token2 => 'PARENTS',
3091 p_value2 => v_offending_parents_list);
3092
3093 FEM_ENGINES_PKG.User_Message
3094 (p_app_name => 'FEM',
3095 p_msg_name => 'FEM_INTG_HIER_PARENT_CHILD_LST',
3096 p_token1 => 'CHILD',
3097 p_value1 => v_display_code,
3098 p_token2 => 'PARENTS',
3099 p_value2 => v_offending_parents_list);
3100
3101 END LOOP;
3102 CLOSE c_child_of_multi_parent;
3103 FEM_ENGINES_PKG.Tech_Message
3104 (p_severity => pc_log_level_procedure,
3105 p_module => 'fem.plsql.fem_intg_hier_eng.Bld_Component_Hier.' || 'duplicate',
3106 p_app_name => 'FEM',
3107 p_msg_text => ' ');
3108
3109 FEM_ENGINES_PKG.User_Message
3110 (p_app_name => 'FEM',
3111 p_msg_text => ' ');
3112 --bug fix 4563603
3113
3114 RAISE FEM_INTG_fatal_err;
3115 END IF;
3116 END IF;
3117 -- To check for the confict dimension group
3118 v_sql_stmt :=
3119 'SELECT gt.child_display_code
3120 FROM '||pv_dim_memb_b_tab||' b,
3121 fem_intg_dim_hier_gt gt
3122 WHERE b.'||pv_dim_memb_disp_col ||'= gt.child_display_code
3123 AND b.value_set_id = '||pv_dim_vs_id||'
3124 AND b.dimension_group_id <> gt.dimension_group_id';
3125 FEM_ENGINES_PKG.Tech_Message
3126 (p_severity => pc_log_level_procedure,
3127 p_module => 'fem.plsql.fem_intg_hier_eng.main.' ,
3128 p_app_name => 'FEM',
3129 p_msg_name => 'FEM_GL_POST_204',
3130 p_token1 => 'VAR_NAME',
3131 p_value1 => 'v_sql_stmt',
3132 p_token2 => 'VAR_VAL',
3133 p_value2 => v_sql_stmt);
3134 OPEN DimensionGroupID FOR v_sql_stmt;
3135 LOOP
3136 FETCH DimensionGroupID INTO v_child_display_code;
3137 EXIT WHEN DimensionGroupID%NOTFOUND;
3138 v_dim_group_conflict := TRUE;
3139 FEM_ENGINES_PKG.Tech_Message
3140 (p_severity => pc_log_level_procedure,
3141 p_module => 'fem.plsql.fem_intg.hier_eng.',
3142 p_app_name => 'FEM',
3143 p_msg_name => 'FEM_INTG_HIER_CONFLICT_DIM_GRP',
3144 p_token1 => 'DISP_CODE',
3145 p_value1 => v_child_display_code);
3146 FEM_ENGINES_PKG.User_Message
3147 (p_app_name => 'FEM',
3148 p_msg_name => 'FEM_INTG_HIER_CONFLICT_DIM_GRP',
3149 p_token1 => 'DISP_CODE',
3150 p_value1 => v_child_display_code);
3151 END LOOP;
3152 CLOSE DimensionGroupID;
3153 IF (v_dim_group_conflict = TRUE) THEN
3154 RAISE FEM_INTG_fatal_err;
3155 END IF;
3156 -- Update member b table for the dimension_group_id
3157 IF (pv_sequence_enforced_flag = 'Y') THEN
3158 v_sql_stmt :=
3159 'UPDATE ' ||pv_dim_memb_b_tab||'
3160 SET dimension_group_id =
3161 (SELECT dimension_group_id
3162 FROM fem_intg_dim_hier_gt
3163 WHERE child_display_code = '||pv_dim_memb_disp_col||'),
3164 last_updated_by = :pv_user_id,
3165 last_update_date = sysdate,
3166 last_update_login = :pv_login_id
3167 WHERE value_set_id = :pv_dim_vs_id
3168 AND '||pv_dim_memb_col||' IN
3169 (SELECT child_id
3170 FROM fem_intg_dim_hier_gt)';
3171 FEM_ENGINES_PKG.Tech_Message
3172 (p_severity => pc_log_level_procedure,
3173 p_module => 'fem.plsql.fem_intg_hier_eng.main.' ,
3174 p_app_name => 'FEM',
3175 p_msg_name => 'FEM_GL_POST_204',
3176 p_token1 => 'VAR_NAME',
3177 p_value1 => 'v_sql_stmt',
3178 p_token2 => 'VAR_VAL',
3179 p_value2 => v_sql_stmt);
3180 EXECUTE IMMEDIATE v_sql_stmt
3181 USING pv_user_id,
3182 pv_login_id,
3183 pv_dim_vs_id;
3184 END IF;
3185 COMMIT;
3186 IF (v_new_hier_obj_def_created = TRUE) THEN
3187 -- Call INSERT statement to copy hierarchy structure from the
3188 -- global temporary table FEM_INTG_DIM_HIER_GT to the FEM hierarchy table
3189 -- 29AUG05 added WHERE hierarchy_obj_def_id = :pv_hier_obj_def_id
3190 v_sql_stmt :=
3191 'INSERT INTO '||pv_dim_hier_tab||'
3192 (hierarchy_obj_def_id, parent_depth_num, parent_id,
3193 parent_value_set_id, child_depth_num, child_id,
3194 child_value_set_id, single_depth_flag,
3195 display_order_num, weighting_pct,
3196 creation_date, created_by, last_update_date,
3197 last_updated_by, last_update_login, object_version_number)
3198 SELECT
3199 :pv_hier_obj_def_id,
3200 gt.parent_depth_num,
3201 gt.parent_id,
3202 :pv_dim_vs_id,
3203 gt.child_depth_num,
3204 gt.child_id,
3205 :pv_dim_vs_id,
3206 ''Y'',
3207 gt.display_order_num, NULL,
3208 SYSDATE,
3209 :pv_user_id,
3210 SYSDATE,
3211 :pv_user_id,
3212 :pv_login_id,
3213 1
3214 FROM fem_intg_dim_hier_gt gt
3215 WHERE hierarchy_obj_def_id = :pv_hier_obj_def_id';
3216 FEM_ENGINES_PKG.Tech_Message
3217 (p_severity => pc_log_level_procedure,
3218 p_module => 'fem.plsql.fem_intg_hier_eng.main.' ,
3219 p_app_name => 'FEM',
3220 p_msg_name => 'FEM_GL_POST_204',
3221 p_token1 => 'VAR_NAME',
3222 p_value1 => 'v_sql_stmt',
3223 p_token2 => 'VAR_VAL',
3224 p_value2 => v_sql_stmt);
3225 EXECUTE IMMEDIATE v_sql_stmt
3226 USING pv_hier_obj_def_id,
3227 pv_dim_vs_id,
3228 pv_dim_vs_id,
3229 pv_user_id,
3230 pv_user_id,
3231 pv_login_id,
3232 pv_hier_obj_def_id;
3233 COMMIT;
3234 ELSE
3235 -- Call DELETE statement to delete existing hierarchy structure
3236 -- for the hierarchy object definition.
3237 v_sql_stmt := 'DELETE FROM '||pv_dim_hier_tab||'
3238 WHERE hierarchy_obj_def_id = :pv_hier_obj_def_id';
3239 FEM_ENGINES_PKG.Tech_Message
3240 (p_severity => pc_log_level_procedure,
3241 p_module => 'fem.plsql.fem_intg_hier_eng.main.',
3242 p_app_name => 'FEM',
3243 p_msg_name => 'FEM_GL_POST_204',
3244 p_token1 => 'VAR_NAME',
3245 p_value1 => 'v_sql_stmt',
3246 p_token2 => 'VAR_VAL',
3247 p_value2 => v_sql_stmt);
3248 EXECUTE IMMEDIATE v_sql_stmt
3249 USING pv_hier_obj_def_id;
3250 -- Call INSERT statement to copy hierarchy structure from the
3251 -- global temporary table FEM_INTG_DIM_HIER_GT to the FEM hierarchy table
3252 v_sql_stmt :=
3253 'INSERT INTO '||pv_dim_hier_tab||'
3254 (hierarchy_obj_def_id, parent_depth_num, parent_id,
3255 parent_value_set_id, child_depth_num, child_id,
3256 child_value_set_id, single_depth_flag,
3257 display_order_num, weighting_pct,
3258 creation_date, created_by, last_update_date,
3259 last_updated_by, last_update_login, object_version_number)
3260 SELECT
3261 :pv_hier_obj_def_id,
3262 gt.parent_depth_num,
3263 gt.parent_id,
3264 :pv_dim_vs_id,
3265 gt.child_depth_num,
3266 gt.child_id,
3267 :pv_dim_vs_id,
3268 ''Y'',
3269 gt.display_order_num, NULL,
3270 SYSDATE,
3271 :pv_user_id,
3272 SYSDATE,
3273 :pv_user_id,
3274 :pv_login_id,
3275 1
3276 FROM fem_intg_dim_hier_gt gt
3277 WHERE gt.hierarchy_obj_def_id = :pv_hier_obj_def_id';
3278 FEM_ENGINES_PKG.Tech_Message
3279 (p_severity => pc_log_level_procedure,
3280 p_module => 'fem.plsql.fem_intg_hier_eng.main.',
3281 p_app_name => 'FEM',
3282 p_msg_name => 'FEM_GL_POST_204',
3283 p_token1 => 'VAR_NAME',
3284 p_value1 => 'v_sql_stmt',
3285 p_token2 => 'VAR_VAL',
3286 p_value2 => v_sql_stmt);
3287 EXECUTE IMMEDIATE v_sql_stmt
3288 USING pv_hier_obj_def_id,
3289 pv_dim_vs_id,
3290 pv_dim_vs_id,
3291 pv_user_id,
3292 pv_user_id,
3293 pv_login_id,
3294 pv_hier_obj_def_id;
3295 END IF;
3296 COMMIT;
3297 FEM_ENGINES_PKG.Tech_Message
3298 (p_severity => pc_log_level_procedure,
3299 p_module => 'fem.plsql.fem_intg_hier_eng.',
3300 p_msg_text => 'before the Final_Process_Logging');
3301 FEM_ENGINES_PKG.Tech_Message
3302 (p_severity => pc_log_level_procedure,
3303 p_module => 'fem.plsql.fem_intg_hier_eng.',
3304 p_msg_text => 'pv_hier_rule_obj_id: ' ||pv_hier_rule_obj_id||
3305 ' pv_hier_rule_obj_def_id:' ||pv_hier_rule_obj_def_id ||
3306 ' pv_req_id:' ||pv_req_id ||
3307 ' pv_user_id:' ||pv_user_id ||
3308 ' pv_login_id:' || pv_login_id);
3309 -- Call routine FEM_INTG_PL_PKG.Final_Process_Logging( ) to complete
3310 -- final process logging. Message name to print will be
3311 -- FEM_INTG_PROC_SUCCESS and the number of output rows will be the
3312 -- return row count from the hierarchy building routine
3313 FEM_INTG_PL_PKG.Final_Process_Logging
3314 (p_obj_id => pv_hier_rule_obj_id,
3315 p_obj_def_id => pv_hier_rule_obj_def_id,
3316 p_req_id => pv_req_id,
3317 p_user_id => pv_user_id,
3318 p_login_id => pv_login_id,
3319 p_exec_status => 'SUCCESS',
3320 p_row_num_loaded => v_row_count_tot,
3321 p_err_num_count => v_err_count_tot,
3322 p_final_msg_name => 'FEM_INTG_PROC_SUCCESS',
3323 p_module_name =>'fem.plsql.fem_intg_hier_eng_pkg.' ||
3324 'final_process_logging',
3325 x_completion_code => v_compl_code);
3326 FEM_ENGINES_PKG.Tech_Message
3327 (p_severity => pc_log_level_procedure,
3328 p_module => 'fem.plsql.fem_intg_hier_eng.',
3329 p_msg_text => 'after the Final_Process_Logging');
3330 FEM_ENGINES_PKG.Tech_Message
3331 (p_severity => pc_log_level_procedure,
3332 p_module => 'fem.plsql.fem_intg_hier_eng.',
3333 p_msg_text => 'v_row_count_tot:' || v_row_count_tot||
3334 ' v_err_count_tot:' || v_err_count_tot||
3335 ' v_compl_code:' ||v_compl_code);
3336 COMMIT;
3337 FEM_ENGINES_PKG.Tech_Message
3338 (p_severity => pc_log_level_procedure,
3339 p_module => 'fem.plsql.fem_intg_hier_eng.main.' ,
3340 p_app_name => 'FEM',
3341 p_msg_name => 'FEM_GL_POST_202',
3342 p_token1 => 'FUNC_NAME',
3343 p_value1 => 'FEM_INTG_HIER_RULE_ENG_PKG.Main',
3344 p_token2 => 'TIME',
3345 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
3346 v_ret_status := FND_CONCURRENT.Set_Completion_Status
3347 (status => 'NORMAL', message => NULL);
3348 IF (pv_flatten_hier_flag = 'Y') THEN
3349 -- To flatten out every hierarchy version after it has been pushed in FEM.
3350 -- To do this, call the Concurrent Program DHMHVFLW
3351 v_req_id := FND_REQUEST.Submit_Request
3352 (application => 'FEM',
3353 program => 'DHMHVFLW',
3354 argument1 => pv_hier_obj_id,
3355 argument2 => pv_hier_obj_def_id);
3356 FEM_ENGINES_PKG.Tech_Message
3357 (p_severity => pc_log_level_procedure,
3358 p_module => 'fem.plsql.fem_intg.hier_eng.Main',
3359 p_app_name => 'FEM',
3360 p_msg_name => 'FEM_INTG_HIER_DHMHVFLW_SUBMIT',
3361 p_token1 => 'REQ_ID',
3362 p_value1 => v_req_id);
3363 FEM_ENGINES_PKG.User_Message
3364 (p_app_name => 'FEM',
3365 p_msg_name => 'FEM_INTG_HIER_DHMHVFLW_SUBMIT',
3366 p_token1 => 'REQ_ID',
3367 p_value1 => v_req_id);
3368
3369 END IF;
3370 EXCEPTION
3371 WHEN FEM_INTG_fatal_err THEN
3372 ROLLBACk;
3373 FEM_INTG_PL_PKG.Final_Process_Logging
3374 (p_obj_id => pv_hier_rule_obj_id,
3375 p_obj_def_id => pv_hier_rule_obj_def_id,
3376 p_req_id => pv_req_id,
3377 p_user_id => pv_user_id,
3378 p_login_id => pv_login_id,
3379 p_exec_status => 'ERROR_RERUN',
3380 p_row_num_loaded => 0,
3381 p_err_num_count => v_err_count_tot,
3382 p_final_msg_name => 'FEM_INTG_PROC_FAILURE',
3383 p_module_name =>'fem.plsql.fem_intg_hier_eng_pkg.' ||
3384 'final_process_logging',
3385 x_completion_code => v_compl_code);
3386 FEM_ENGINES_PKG.Tech_Message
3387 (p_severity => pc_log_level_procedure,
3388 p_module => 'fem.plsql.fem_intg_hier_eng.main.',
3389 p_app_name => 'FEM',
3390 p_msg_name => 'FEM_GL_POST_203',
3391 p_token1 => 'FUNC_NAME',
3392 p_value1 => 'FEM_INTG_HIER_RULE_ENG_PKG.Main',
3393 p_token2 => 'TIME',
3394 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
3395 v_ret_status := FND_CONCURRENT.Set_Completion_Status
3396 (status => 'ERROR', message => NULL);
3397 WHEN OTHERS THEN
3398 ROLLBACK;
3399 FEM_ENGINES_PKG.Tech_Message
3400 (p_severity => pc_log_level_unexpected,
3401 p_module => 'fem.plsql.fem_intg_hier_eng.main.' ,
3402 p_app_name => 'FEM',
3403 p_msg_name => 'FEM_GL_POST_215',
3404 p_token1 => 'ERR_MSG',
3405 p_value1 => SQLERRM);
3406 FEM_ENGINES_PKG.User_Message
3407 (p_app_name => 'FEM',
3408 p_msg_name => 'FEM_GL_POST_215',
3409 p_token1 => 'ERR_MSG',
3410 p_value1 => SQLERRM);
3411 FEM_INTG_PL_PKG.Final_Process_Logging
3412 (p_obj_id => pv_hier_rule_obj_id,
3413 p_obj_def_id => pv_hier_rule_obj_def_id,
3414 p_req_id => pv_req_id,
3415 p_user_id => pv_user_id,
3416 p_login_id => pv_login_id,
3417 p_exec_status => 'ERROR_RERUN',
3418 p_row_num_loaded => 0,
3419 p_err_num_count => v_err_count_tot,
3420 p_final_msg_name => 'FEM_INTG_PROC_FAILURE',
3421 p_module_name =>'fem.plsql.fem_intg_hier_eng_pkg.' ||
3422 'final_process_logging',
3423 x_completion_code => v_compl_code);
3424 FEM_ENGINES_PKG.Tech_Message
3425 (p_severity => pc_log_level_procedure,
3426 p_module => 'fem.plsql.fem_intg_hier_eng.main.',
3427 p_app_name => 'FEM',
3428 p_msg_name => 'FEM_GL_POST_203',
3429 p_token1 => 'FUNC_NAME',
3430 p_value1 => 'FEM_INTG_HIER_RULE_ENG_PKG.Main',
3431 p_token2 => 'TIME',
3432 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
3433 v_ret_status := FND_CONCURRENT.Set_Completion_Status
3434 (status => 'ERROR', message => NULL);
3435 END Main;
3436 END FEM_INTG_HIER_RULE_ENG_PKG;