[Home] [Help]
PACKAGE BODY: APPS.FEM_HIER_LOADER_PKG
Source
1 PACKAGE BODY FEM_HIER_LOADER_PKG AS
2 /* $Header: femhierldr_phb.plb 120.7 2008/02/14 16:49:47 gcheng ship $ */
3
4
5 -------------------------------
6 -- Declare package constants --
7 -------------------------------
8 g_object_version_number constant number := 1;
9 g_connect_by_loop constant number := -1436;
10
11 -- Constants for p_exec_status_code
12 g_exec_status_error_rerun constant varchar2(30) := 'ERROR_RERUN';
13 g_exec_status_success constant varchar2(30) := 'SUCCESS';
14
15 -- Constants for p_execution_mode
16 g_snapshot constant varchar2(1) := 'S';
17 g_error_reprocessing constant varchar2(1) := 'E';
18
19 -- Constants for ld_load_type
20 g_new_hier constant varchar2(30) := 'NEW_HIER';
21 g_new_hier_def constant varchar2(30) := 'NEW_HIER_DEF';
22 g_update_hier_def constant varchar2(30) := 'UPDATE_HIER_DEF';
23
24 g_default_fetch_limit constant number := 99999;
25
26 g_log_level_1 constant number := fnd_log.level_statement;
27 g_log_level_2 constant number := fnd_log.level_procedure;
28 g_log_level_3 constant number := fnd_log.level_event;
29 g_log_level_4 constant number := fnd_log.level_exception;
30 g_log_level_5 constant number := fnd_log.level_error;
31 g_log_level_6 constant number := fnd_log.level_unexpected;
32
33
34 ------------------------------
35 -- Declare package messages --
36 ------------------------------
37 G_UNEXPECTED_ERROR constant varchar2(30) := 'FEM_UNEXPECTED_ERROR';
38 G_DIM_NOT_FOUND_ERR constant varchar2(30) := 'FEM_DIM_NOT_FOUND';
39 G_PL_REG_REQUEST_ERR constant varchar2(30) := 'FEM_PL_REG_REQUEST_ERR';
40 G_PL_OBJ_EXEC_LOCK_ERR constant varchar2(30) := 'FEM_PL_OBJ_EXEC_LOCK_ERR';
41 G_PL_OBJ_EXECLOCK_EXISTS_ERR constant varchar2(30) := 'FEM_PL_OBJ_EXECLOCK_EXISTS_ERR';
42 G_EXEC_RERUN constant varchar2(30) := 'FEM_EXEC_RERUN';
43 G_EXEC_SUCCESS constant varchar2(30) := 'FEM_EXEC_SUCCESS';
44 G_EXEC_NO_FOLDER_ACCESS_ERR constant varchar2(30) := 'FEM_EXEC_NO_FOLDER_ACCESS_ERR';
45 G_EXT_LDR_POST_PROC_ERR constant varchar2(30) := 'FEM_EXT_LDR_POST_PROC_ERR';
46 G_EXT_LDR_BAD_LDR_OBJ_ERR constant varchar2(30) := 'FEM_EXT_LDR_BAD_LDR_OBJ_ERR';
47 G_EXT_LDR_EXEC_MODE_ERR constant varchar2(30) := 'FEM_EXT_LDR_EXEC_MODE_ERR';
48 G_EXT_LDR_INV_MEMBER_ERR constant varchar2(30) := 'FEM_EXT_LDR_INV_MEMBER_ERR';
49 G_EXT_LDR_INV_DIM_GRP_ERR constant varchar2(30) := 'FEM_EXT_LDR_INV_DIM_GRP_ERR';
50 G_EXT_LDR_INV_VALUE_SET_ERR constant varchar2(30) := 'FEM_EXT_LDR_INV_VALUE_SET_ERR';
51 G_HIER_LDR_MULTI_PARENT_ERR constant varchar2(30) := 'FEM_HIER_LDR_MULTI_PARENT_ERR';
52 G_HIER_LDR_INV_ROOT_NODE_ERR constant varchar2(30) := 'FEM_HIER_LDR_INV_ROOT_NODE_ERR';
53 G_HIER_LDR_MULTI_TOP_ERR constant varchar2(30) := 'FEM_HIER_LDR_MULTI_TOP_ERR';
54 G_HIER_LDR_GRP_SEQ_RULE_ERR constant varchar2(30) := 'FEM_HIER_LDR_GRP_SEQ_RULE_ERR';
55 G_HIER_LDR_INV_HIER_ERR constant varchar2(30) := 'FEM_HIER_LDR_INV_HIER_ERR';
56 G_HIER_LDR_CIRC_HIER_ERR constant varchar2(30) := 'FEM_HIER_LDR_CIRC_HIER_ERR';
57 G_HIER_LDR_RECON_LEAF_ERR constant varchar2(30) := 'FEM_HIER_LDR_RECON_LEAF_ERR';
58 G_HIER_LDR_RECON_NODE_ERR constant varchar2(30) := 'FEM_HIER_LDR_RECON_NODE_ERR';
59 G_HIER_LDR_INV_CALENDAR_ERR constant varchar2(30) := 'FEM_HIER_LDR_INV_CALENDAR_ERR';
60 G_HIER_LDR_MULTI_VS_ERR constant varchar2(30) := 'FEM_HIER_LDR_MULTI_VS_ERR';
61 G_HIER_LDR_DIM_GRPS_REQ_ERR constant varchar2(30) := 'FEM_HIER_LDR_DIM_GRPS_REQ_ERR';
62 G_HIER_LDR_MISSING_ROOT_ERR constant varchar2(30) := 'FEM_HIER_LDR_MISSING_ROOT_ERR';
63 G_HIER_LDR_NO_HIER_ERR constant varchar2(30) := 'FEM_HIER_LDR_NO_HIER_ERR';
64 G_HIER_LDR_MULTI_HIER_ERR constant varchar2(30) := 'FEM_HIER_LDR_MULTI_HIER_ERR';
65 G_HIER_LDR_FOLDER_ERR constant varchar2(30) := 'FEM_HIER_LDR_FOLDER_ERR';
66 G_HIER_LDR_EFF_DATE_RANG_ERR constant varchar2(30) := 'FEM_HIER_LDR_EFF_DATE_RANG_ERR';
67 G_HIER_LDR_EFF_DATE_OVLP_ERR constant varchar2(30) := 'FEM_HIER_LDR_EFF_DATE_OVLP_ERR';
68 G_HIER_LDR_DEF_DATA_LOCK_ERR constant varchar2(30) := 'FEM_HIER_LDR_DEF_DATA_LOCK_ERR';
69 G_HIER_LDR_DEF_DATA_RANG_ERR constant varchar2(30) := 'FEM_HIER_LDR_DEF_DATA_RANG_ERR';
70 G_HIER_LDR_HIER_TYPE_CD_ERR constant varchar2(30) := 'FEM_HIER_LDR_HIER_TYPE_CD_ERR';
71 G_HIER_LDR_HIER_USG_CD_ERR constant varchar2(30) := 'FEM_HIER_LDR_HIER_USG_CD_ERR';
72 G_HIER_LDR_GRP_SQ_ENF_CD_ERR constant varchar2(30) := 'FEM_HIER_LDR_GRP_SQ_ENF_CD_ERR';
73 G_HIER_LDR_GRP_SQ_REQ_ERR constant varchar2(30) := 'FEM_HIER_LDR_GRP_SQ_REQ_ERR';
74 G_HIER_LDR_MULTI_TOP_FLG_ERR constant varchar2(30) := 'FEM_HIER_LDR_MULTI_TOP_FLG_ERR';
75 G_HIER_LDR_MULTI_VS_FLG_ERR constant varchar2(30) := 'FEM_HIER_LDR_MULTI_VS_FLG_ERR';
76 G_HIER_LDR_FLAT_ROWS_FLG_ERR constant varchar2(30) := 'FEM_HIER_LDR_FLAT_ROWS_FLG_ERR';
77 G_HIER_LDR_CALENDAR_ERR constant varchar2(30) := 'FEM_HIER_LDR_CALENDAR_ERR';
78 G_HIER_LDR_NO_HIER_VS_ERR constant varchar2(30) := 'FEM_HIER_LDR_NO_HIER_VS_ERR';
79 G_HIER_LDR_HIER_DETAILS_ERR constant varchar2(30) := 'FEM_HIER_LDR_HIER_DETAILS_ERR';
80 G_HIER_LDR_HIER_FLATTEN_ERR constant varchar2(30) := 'FEM_HIER_LDR_HIER_FLATTEN_ERR';
81 G_HIER_LDR_NO_LEAF_ATTR_ERR constant varchar2(30) := 'FEM_HIER_LDR_NO_LEAF_ATTR_ERR';
82 G_HIER_LDR_READONLY_HIER_ERR constant varchar2(30) := 'FEM_HIER_LDR_READONLY_HIER_ERR';
83 G_HIER_LDR_GRP_SQ_VAL_ERR constant varchar2(30) := 'FEM_HIER_LDR_GRP_SQ_VAL_ERR';
84 G_HIER_LDR_MULT_VS_FLG_V_ERR constant varchar2(30) := 'FEM_HIER_LDR_MULT_VS_FLG_V_ERR';
85 G_HIER_LDR_HIER_TYPE_VAL_ERR constant varchar2(30) := 'FEM_HIER_LDR_HIER_TYPE_VAL_ERR';
86
87
88 --------------------------------------
89 -- Declare package type definitions --
90 --------------------------------------
91 t_return_status varchar2(1);
92 t_msg_count number;
93 t_msg_data varchar2(2000);
94
95
96 -------------------------------
97 -- Declare package variables --
98 -------------------------------
99 -- Exception variables
100 gv_prg_msg varchar2(2000);
101 gv_callstack varchar2(2000);
102
103 -- Bulk Fetch Limit
104 gv_fetch_limit number;
105
106 -- Default Effective Start and End dates
107 gv_default_start_date date;
108 gv_default_end_date date;
109
110
111 -----------------------------------------------
112 -- Declare private procedures and functions --
113 -----------------------------------------------
114 PROCEDURE get_dimension_info (
115 p_dimension_varchar_label in varchar2
116 ,x_dimension_id out nocopy number
117 ,x_target_hier_table out nocopy varchar2
118 ,x_source_hier_table out nocopy varchar2
119 ,x_member_b_table out nocopy varchar2
120 ,x_member_attr_table out nocopy varchar2
121 ,x_member_col out nocopy varchar2
122 ,x_member_dc_col out nocopy varchar2
123 ,x_group_use_code out nocopy varchar2
124 ,x_value_set_required_flag out nocopy varchar2
125 ,x_hier_type_allowed_code out nocopy varchar2
126 ,x_hier_versioning_type_code out nocopy varchar2
127 );
128
129 PROCEDURE register_process_execution (
130 p_request_id in number
131 ,p_object_id in number
132 ,p_obj_def_id in number
133 ,p_execution_mode in varchar
134 ,p_user_id in number
135 ,p_login_id in number
136 ,p_pgm_id in number
137 ,p_pgm_app_id in number
138 ,p_hierarchy_object_name in varchar2
139 );
140
141 PROCEDURE eng_master_post_proc (
142 p_request_id in number
143 ,p_object_id in number
144 ,p_exec_status_code in varchar2
145 ,p_user_id in number
146 ,p_login_id in number
147 ,p_dimension_varchar_label in varchar2
148 ,p_execution_mode in varchar2
149 ,p_target_hierval_table in varchar2
150 );
151
152 PROCEDURE get_put_messages (
153 p_msg_count in number
154 ,p_msg_data in varchar2
155 );
156
157 FUNCTION get_default_start_date
158 RETURN date;
159
160 FUNCTION get_default_end_date
161 RETURN date;
162
163 PROCEDURE set_hier_table_err_msg (
164 p_hier_table_name in varchar2
165 ,p_status in varchar2
166 );
167
168 PROCEDURE bld_bad_value_sets_stmt (
169 p_dimension_varchar_label in varchar2
170 ,p_execution_mode in varchar2
171 ,p_value_set_required_flag in varchar2
172 ,x_bad_value_sets_stmt out nocopy varchar2
173 );
174
175 PROCEDURE bld_bad_dim_groups_stmt (
176 p_dimension_varchar_label in varchar2
177 ,p_execution_mode in varchar2
178 ,x_bad_dim_groups_stmt out nocopy varchar2
179 );
180
181 PROCEDURE bld_bad_hier_calendars_stmt (
182 p_dimension_varchar_label in varchar2
183 ,p_execution_mode in varchar2
184 ,p_source_hier_table in varchar2
185 ,x_bad_hier_calendars_stmt out nocopy varchar2
186 );
187
188 PROCEDURE bld_bad_hier_value_sets_t_stmt (
189 p_dimension_varchar_label in varchar2
190 ,p_execution_mode in varchar2
191 ,p_value_set_required_flag in varchar2
192 ,p_source_hier_table in varchar2
193 ,x_bad_hier_value_sets_t_stmt out nocopy varchar2
194 );
195
196 PROCEDURE bld_bad_hier_value_sets_stmt (
197 p_dimension_varchar_label in varchar2
198 ,p_execution_mode in varchar2
199 ,p_value_set_required_flag in varchar2
200 ,p_source_hier_table in varchar2
201 ,x_bad_hier_value_sets_stmt out nocopy varchar2
202 );
203
204 PROCEDURE bld_bad_hier_multi_vs_stmt (
205 p_dimension_varchar_label in varchar2
206 ,p_execution_mode in varchar2
207 ,p_value_set_required_flag in varchar2
208 ,p_source_hier_table in varchar2
209 ,x_bad_hier_multi_vs_stmt out nocopy varchar2
210 );
211
212 PROCEDURE bld_bad_hier_members_stmt (
213 p_dimension_varchar_label in varchar2
214 ,p_execution_mode in varchar2
215 ,p_value_set_required_flag in varchar2
216 ,p_source_hier_table in varchar2
217 ,p_member_b_table in varchar2
218 ,p_member_col in varchar2
219 ,p_member_dc_col in varchar2
220 ,x_bad_hier_members_stmt out nocopy varchar2
221 );
222
223 PROCEDURE bld_bad_hier_dups_stmt (
224 p_dimension_varchar_label in varchar2
225 ,p_execution_mode in varchar2
226 ,p_value_set_required_flag in varchar2
227 ,p_source_hier_table in varchar2
228 ,x_bad_hier_dups_stmt out nocopy varchar2
229 );
230
231 PROCEDURE bld_bad_hier_rec_leafs_stmt (
232 p_dimension_varchar_label in varchar2
233 ,p_target_hierval_table in varchar2
234 ,p_member_attr_table in varchar2
235 ,p_member_col in varchar2
236 ,x_bad_hier_rec_leafs_stmt out nocopy varchar2
237 );
238
239 PROCEDURE bld_bad_hier_rec_nodes_stmt (
240 p_dimension_varchar_label in varchar2
241 ,p_target_hierval_table in varchar2
242 ,p_member_attr_table in varchar2
243 ,p_member_col in varchar2
244 ,x_bad_hier_rec_nodes_stmt out nocopy varchar2
245 );
246
247 PROCEDURE bld_bad_hier_roots_stmt (
248 p_dimension_varchar_label in varchar2
249 ,p_execution_mode in varchar2
250 ,p_value_set_required_flag in varchar2
251 ,p_source_hier_table in varchar2
252 ,p_member_b_table in varchar2
253 ,p_member_col in varchar2
254 ,p_member_dc_col in varchar2
255 ,x_bad_hier_roots_stmt out nocopy varchar2
256 );
257
258 PROCEDURE bld_bad_hier_dim_groups_t_stmt (
259 p_dimension_varchar_label in varchar2
260 ,p_execution_mode in varchar2
261 ,p_target_hierval_table in varchar2
262 ,x_bad_hier_dim_groups_t_stmt out nocopy varchar2
263 );
264
265 PROCEDURE bld_bad_hier_dim_groups_stmt (
266 p_dimension_varchar_label in varchar2
267 ,p_target_hierval_table in varchar2
268 ,x_bad_hier_dim_groups_stmt out nocopy varchar2
269 );
270
271 PROCEDURE bld_bad_hier_dim_grp_skp_stmt (
272 p_dimension_varchar_label in varchar2
273 ,p_target_hierval_table in varchar2
274 ,x_bad_hier_dim_grp_skp_stmt out nocopy varchar2
275 );
276
277 PROCEDURE bld_bad_hier_dim_grp_reg_stmt (
278 p_dimension_varchar_label in varchar2
279 ,p_target_hierval_table in varchar2
280 ,x_bad_hier_dim_grp_reg_stmt out nocopy varchar2
281 );
282
283 PROCEDURE bld_root_node_count_stmt (
284 p_dimension_varchar_label in varchar2
285 ,p_execution_mode in varchar2
286 ,p_value_set_required_flag in varchar2
287 ,p_source_hier_table in varchar2
288 ,x_root_node_count_stmt out nocopy varchar2
289 );
290
291 PROCEDURE bld_get_value_sets_stmt (
292 p_dimension_varchar_label in varchar2
293 ,p_execution_mode in varchar2
294 ,p_value_set_required_flag in varchar2
295 ,x_get_value_sets_stmt out nocopy varchar2
296 );
297
298 PROCEDURE bld_get_dim_groups_t_stmt (
299 p_dimension_varchar_label in varchar2
300 ,p_execution_mode in varchar2
301 ,x_get_dim_groups_t_stmt out nocopy varchar2
302 );
303
304 PROCEDURE bld_get_dim_groups_stmt (
305 p_dimension_varchar_label in varchar2
306 ,p_execution_mode in varchar2
307 ,x_get_dim_groups_stmt out nocopy varchar2
308 );
309
310 PROCEDURE bld_get_hier_defs_stmt (
311 p_dimension_varchar_label in varchar2
312 ,p_execution_mode in varchar2
313 ,x_get_hier_defs_stmt out nocopy varchar2
314 );
315
316 PROCEDURE bld_get_hier_roots_stmt (
317 p_dimension_varchar_label in varchar2
318 ,p_execution_mode in varchar2
319 ,p_value_set_required_flag in varchar2
320 ,p_group_use_code in varchar2
321 ,p_source_hier_table in varchar2
322 ,p_member_b_table in varchar2
323 ,p_member_col in varchar2
324 ,p_member_dc_col in varchar2
325 ,x_get_hier_roots_stmt out nocopy varchar2
326 );
327
328 PROCEDURE bld_get_hier_rels_stmt (
329 p_dimension_varchar_label in varchar2
330 ,p_execution_mode in varchar2
331 ,p_value_set_required_flag in varchar2
332 ,p_group_use_code in varchar2
333 ,p_source_hier_table in varchar2
334 ,p_member_b_table in varchar2
335 ,p_member_col in varchar2
336 ,p_member_dc_col in varchar2
337 ,x_get_hier_rels_stmt out nocopy varchar2
338 );
339
340 PROCEDURE bld_insert_hier_rels_stmt (
341 p_dimension_varchar_label in varchar2
342 ,p_value_set_required_flag in varchar2
343 ,p_target_hier_table in varchar2
344 ,p_target_hierval_table in varchar2
345 ,x_insert_hier_rels_stmt out nocopy varchar2
346 );
347
348 PROCEDURE bld_delete_hier_rels_stmt (
349 p_dimension_varchar_label in varchar2
350 ,p_execution_mode in varchar2
351 ,p_source_hier_table in varchar2 := null
352 ,p_target_hier_table in varchar2 := null
353 ,p_target_hierval_table in varchar2 := null
354 ,x_delete_hier_rels_stmt out nocopy varchar2
355 );
356
357 PROCEDURE bld_insert_hierval_rels_stmt (
358 p_dimension_varchar_label in varchar2
359 ,p_target_hierval_table in varchar2
360 ,x_insert_hierval_rels_stmt out nocopy varchar2
361 );
362
363 FUNCTION bld_update_status_stmt (
364 p_dimension_varchar_label in varchar2
365 ,p_execution_mode in varchar2
366 ,p_value_set_required_flag in varchar2 := null
367 ,p_source_hier_table in varchar2
368 ,p_rowid_flag in varchar2 := null
369 ,p_hier_object_name_flag in varchar2 := null
370 ,p_hier_obj_def_name_flag in varchar2 := null
371 ,p_parent_flag in varchar2 := null
372 ,p_child_flag in varchar2 := null
373 )
374 RETURN varchar2;
375
376
377 -----------------------------------------------------------------------------
378 -- Package bodies for functions/procedures
379 -----------------------------------------------------------------------------
380
381 /*===========================================================================+
382 | PROCEDURE
383 | Main
384 |
385 | DESCRIPTION
386 | Main engine procedure for loading dimension hierarchies
387 | into FEM
388 |
389 | SCOPE - PUBLIC
390 |
391 | MODIFICATION HISTORY
392 | nmartine 24-NOV-2003 Created
393 |
394 +===========================================================================*/
395
396 PROCEDURE Main (
397 errbuf out nocopy varchar2
398 ,retcode out nocopy varchar2
399 ,p_object_definition_id in number
400 ,p_execution_mode in varchar2
401 ,p_dimension_varchar_label in varchar2
402 ,p_hierarchy_object_name in varchar2
403 ,p_hier_obj_def_display_name in varchar2
404 )
405 IS
406
407 -----------------------
408 -- Declare constants --
409 -----------------------
410 l_api_name constant varchar2(30) := 'Main';
411
412 -----------------------
413 -- Declare variables --
414 -----------------------
415
416 ----------------------------
417 -- Common abbreviations:
418 ----------------------------
419 -- dc = display_code
420 -- _t = interface table
421 -- source = interface table
422 -- target = FEM table
423 ----------------------------
424
425 -- Rowid parameter for PKG API's used when inserting rows.
426 l_rowid rowid;
427
428 -- Concurrent Request Parameters
429 l_user_id number;
430 l_login_id number;
431 l_request_id number;
432 l_pgm_id number;
433 l_pgm_app_id number;
434
435 -- Hierarchy Loader Object ID and Object Definition ID
436 l_loader_object_id number;
437 l_loader_obj_def_id number;
438 l_loader_folder_name varchar2(150);
439
440 -- FEM_HIERARCHIES_T Parameters and their corresponding ID values
441 ld_rowid rowid;
442 ld_hierarchy_object_id number;
443 ld_hier_obj_def_id number;
444 ld_folder_id number;
445 ld_value_set_id number;
446 ld_calendar_id number;
447 ld_effective_start_date date;
448 ld_effective_end_date date;
449 ld_status varchar2(30);
450 ld_dimension_varchar_label varchar2(30);
451 ld_hierarchy_type_code varchar2(30);
452 ld_group_seq_enforced_code varchar2(30);
453 ld_hierarchy_usage_code varchar2(30);
454 ld_load_type varchar2(30);
455 ld_folder_name varchar2(150); -- bug#3657227
456 ld_hierarchy_object_name varchar2(150);
457 ld_hier_obj_def_display_name varchar2(150);
458 ld_calendar_dc varchar2(150);
459 ld_multi_top_flag varchar2(1);
460 ld_multi_value_set_flag varchar2(1);
461 ld_flattened_rows_flag varchar2(1);
462 ld_language varchar2(4);
463
464 -- Helper parameters for specific validation queries
465 l_count number;
466 l_vs_count number;
467 l_num_roots number;
468
469 l_attribute_id number;
470 l_attr_version_id number;
471
472 l_max_object_definition_id number;
473 l_new_max_obj_def_id number;
474
475 l_dummy number;
476 l_sql_err_code number;
477
478 l_max_effective_start_date date;
479 l_max_effective_end_date date;
480 l_new_max_eff_end_date date;
481 l_current_date date;
482
483 l_completion_status boolean;
484 l_hierarchy_error_flag boolean;
485
486 l_date_incl_rslt_data varchar2(1);
487 l_approval_edit_lock_exists varchar2(1);
488 l_data_edit_lock_exists varchar2(1);
489
490 -- These variables are retrieved as part of GET_DIMENSION_INFO
491 l_dimension_id number;
492 l_target_hier_table varchar2(30);
493 l_source_hier_table varchar2(30);
494 l_member_b_table varchar2(30);
495 l_member_attr_table varchar2(30);
496 l_member_col varchar2(30);
497 l_member_dc_col varchar2(30);
498 l_group_use_code varchar2(30);
499 l_value_set_required_flag varchar2(1);
500 l_hier_type_allowed_code varchar2(30);
501 l_hier_versioning_type_code varchar2(30);
502
503 -- Variable to store the HIERVAL table.
504 l_target_hierval_table varchar2(30);
505
506 -- Variables storing return status, message count, and message data for
507 -- internal procedure calls
508 l_return_status t_return_status%TYPE;
509 l_msg_count t_msg_count%TYPE;
510 l_msg_data t_msg_data%TYPE;
511
512 -- Dynamic SQL: statement variables
513 l_bad_value_sets_stmt varchar2(10000);
514 l_bad_dim_groups_stmt varchar2(10000);
515 l_bad_hier_calendars_stmt varchar2(10000);
516 l_bad_hier_value_sets_t_stmt varchar2(10000);
517 l_bad_hier_value_sets_stmt varchar2(10000);
518 l_bad_hier_multi_vs_stmt varchar2(10000);
519 l_bad_hier_members_stmt varchar2(10000);
520 l_bad_hier_dups_stmt varchar2(10000);
521 l_bad_hier_rec_leafs_stmt varchar2(10000);
522 l_bad_hier_rec_nodes_stmt varchar2(10000);
523 l_bad_hier_roots_stmt varchar2(10000);
524 l_bad_hier_dim_groups_t_stmt varchar2(10000);
525 l_bad_hier_dim_groups_stmt varchar2(10000);
526 l_bad_hier_dim_grp_skp_stmt varchar2(10000);
527 l_bad_hier_dim_grp_reg_stmt varchar2(10000);
528 l_root_node_count_stmt varchar2(10000);
529 l_get_value_sets_stmt varchar2(10000);
530 l_get_dim_groups_t_stmt varchar2(10000);
531 l_get_dim_groups_stmt varchar2(10000);
532 l_get_hier_defs_stmt varchar2(10000);
533 l_get_hier_roots_stmt varchar2(10000);
534 l_get_hier_rels_stmt varchar2(10000);
535 l_insert_hier_rels_stmt varchar2(10000);
536 l_delete_hier_t_rels_stmt varchar2(10000);
537 l_delete_hier_rels_stmt varchar2(10000);
538 l_insert_hierval_rels_stmt varchar2(10000);
539
540 -- Dynamic SQL: STATUS column where clause
541 l_status_clause varchar2(100) := '';
542
543
544 -------------------------------------
545 -- Declare bulk collection columns --
546 -------------------------------------
547
548 ------------------------------------------------
549 -- Common abbreviations:
550 ------------------------------------------------
551 -- t_ = array of FEM_HIERARCHY_T rows
552 -- tg_ = array of FEM_HIER_DIM_GRPS_T rows
553 -- tv_ = array of FEM_HIER_VALUES_SETS_T rows
554 -- th_ = array of FEM_xName_HIER_T rows
555 ------------------------------------------------
556
557 tg_rowid rowid_type;
558 tv_rowid rowid_type;
559 th_rowid rowid_type;
560
561 tg_dimension_group_id number_type;
562 tg_relative_dim_group_seq number_type;
563 tg_depth_num number_type;
564
565 tv_value_set_id number_type;
566
567 th_parent_depth_num number_type;
568 th_parent_id number_type;
569 th_parent_value_set_id number_type;
570 th_parent_cal_period_number number_type;
571 th_parent_dimension_grp_id number_type;
572 th_child_depth_num number_type;
573 th_child_id number_type;
574 th_child_value_set_id number_type;
575 th_child_cal_period_number number_type;
576 th_child_dimension_grp_id number_type;
577 th_display_order_num number_type;
578
579 th_wt_pct pct_type;
580
581 th_parent_cal_period_end_date date_type;
582 th_child_cal_period_end_date date_type;
583
584 tg_status varchar2_std_type;
585 tv_status varchar2_std_type;
586 th_status varchar2_std_type;
587
588 th_parent_dc varchar2_150_type;
589 th_parent_value_set_dc varchar2_150_type;
590 th_parent_dim_grp_dc varchar2_150_type;
591 th_child_dc varchar2_150_type;
592 th_child_value_set_dc varchar2_150_type;
593 th_child_dim_grp_dc varchar2_150_type;
594
595 -----------------------------
596 -- Declare dynamic cursors --
597 -----------------------------
598 cv_bad_value_sets cv_curs;
599 cv_bad_dim_groups cv_curs;
600 cv_bad_hier_calendars cv_curs;
601 cv_bad_hier_value_sets cv_curs;
602 cv_bad_hier_members cv_curs;
603 cv_bad_hier_dups cv_curs;
604 cv_bad_hier_rec_leafs cv_curs;
605 cv_bad_hier_rec_nodes cv_curs;
606 cv_bad_hier_roots cv_curs;
607 cv_bad_hier_dim_groups cv_curs;
608 cv_bad_hier_dim_grp_sq cv_curs;
609 cv_get_value_sets cv_curs;
610 cv_get_dim_groups cv_curs;
611 cv_get_hier_defs cv_curs;
612 cv_get_hier_roots cv_curs;
613 cv_get_hier_rels cv_curs;
614
615 ----------------------------
616 -- Declare static cursors --
617 ----------------------------
618
619
620 -----------------------------------------------------------
621 -- Declare flags to keep track of which cursors are open --
622 -----------------------------------------------------------
623 l_bad_value_sets_is_open boolean := false;
624 l_bad_dim_groups_is_open boolean := false;
625 l_bad_hier_calendars_is_open boolean := false;
626 l_bad_hier_value_sets_is_open boolean := false;
627 l_bad_hier_members_is_open boolean := false;
628 l_bad_hier_dups_is_open boolean := false;
629 l_bad_hier_rec_leafs_is_open boolean := false;
630 l_bad_hier_rec_nodes_is_open boolean := false;
631 l_bad_hier_roots_is_open boolean := false;
632 l_bad_hier_dim_groups_is_open boolean := false;
633 l_bad_hier_dim_grp_sq_is_open boolean := false;
634 l_get_value_sets_is_open boolean := false;
635 l_get_dim_groups_is_open boolean := false;
636 l_get_hier_defs_is_open boolean := false;
637 l_get_hier_roots_is_open boolean := false;
638 l_get_hier_rels_is_open boolean := false;
639
640 -----------------------------------------------------------
641 -- Index indicating last row number for a cursor.
642 -----------------------------------------------------------
643 l_bad_value_sets_last_row number := 0;
644 l_bad_dim_groups_last_row number := 0;
645 l_bad_hier_calendars_last_row number := 0;
646 l_bad_hier_value_sets_last_row number := 0;
647 l_bad_hier_members_last_row number := 0;
648 l_bad_hier_dups_last_row number := 0;
649 l_bad_hier_rec_leafs_last_row number := 0;
650 l_bad_hier_rec_nodes_last_row number := 0;
651 l_bad_hier_roots_last_row number := 0;
652 l_bad_hier_dim_groups_last_row number := 0;
653 l_bad_hier_dim_grp_sq_last_row number := 0;
654 l_get_value_sets_last_row number := 0;
655 l_get_dim_groups_last_row number := 0;
656 l_get_hier_roots_last_row number := 0;
657 l_get_hier_rels_last_row number := 0;
658
659
660
661 /**************************************************************************
662 * *
663 * Load Dim Hierarchies *
664 * Execution Block *
665 * *
666 **************************************************************************/
667
668 BEGIN
669
670 -- Necessary defaulting of loader object definition id as OA Fwk cannot
671 -- handle defaults in the concurrent program registration.
672 l_loader_obj_def_id := p_object_definition_id;
673 if (l_loader_obj_def_id is null) then
674 l_loader_obj_def_id := 1400;
675 end if;
676
677 -- Initialize Message Stack on FND_MSG_PUB
678 FND_MSG_PUB.Initialize;
679
680 FEM_ENGINES_PKG.tech_message (
681 p_severity => g_log_level_2
682 ,p_module => G_BLOCK||'.'||l_api_name
683 ,p_msg_text => 'BEGIN'
684 );
685
686 FEM_ENGINES_PKG.tech_message (
687 p_severity => g_log_level_1
688 ,p_module => G_BLOCK||'.'||l_api_name
689 ,p_msg_text =>
690 ' p_execution_mode='||p_execution_mode||
691 ' p_object_definition_id='||p_object_definition_id||
692 ' p_dimension_varchar_label='||p_dimension_varchar_label||
693 ' p_hierarchy_object_name='||p_hierarchy_object_name||
694 ' p_hier_obj_def_display_name='||p_hier_obj_def_display_name
695 );
696
697 -- Get all Global Parameters
698 l_user_id := FND_GLOBAL.user_id;
699 l_login_id := FND_GLOBAL.login_id;
700 l_request_id := FND_GLOBAL.conc_request_id;
701 l_pgm_id := FND_GLOBAL.conc_program_id;
702 l_pgm_app_id := FND_GLOBAL.prog_appl_id;
703
704 FEM_ENGINES_PKG.tech_message (
705 p_severity => g_log_level_1
706 ,p_module => G_BLOCK||'.'||l_api_name
707 ,p_msg_text =>
708 ' l_user_id='||l_user_id||
709 ' l_login_id='||l_login_id||
710 ' l_request_id='||l_request_id||
711 ' l_pgm_id='||l_pgm_id||
712 ' l_pgm_app_id='||l_pgm_app_id
713 );
714
715 -- Get the limit for bulk fetches
716 gv_fetch_limit :=
717 nvl(FND_PROFILE.value_specific('FEM_BULK_FETCH_LIMIT',l_user_id,null,null)
718 ,g_default_fetch_limit);
719
720 ------------------------------------------------------------------------------
721 -- Get the object id for the specified object definition id. Needed for
722 -- process locking.
723 ------------------------------------------------------------------------------
724 begin
725 select object_id
726 into l_loader_object_id
727 from fem_object_definition_b
728 where object_definition_id = l_loader_obj_def_id
729 and object_id in (
730 select object_id
731 from fem_object_catalog_b
732 where object_type_code = 'HIERARCHY_LOADER'
733 );
734 exception
735 when no_data_found then
736 l_loader_object_id := 1400;
737 when others then
738 FEM_ENGINES_PKG.user_message (
739 p_app_name => G_FEM
740 ,p_msg_name => G_EXT_LDR_BAD_LDR_OBJ_ERR
741 ,p_token1 => 'OBJECT_DEFINITION_ID'
742 ,p_value1 => l_loader_obj_def_id
743 );
744 raise e_loader_error;
745 end;
746
747 ------------------------------------------------------------------------------
748 -- Check to see if the user can execute the hier loader. In FEM.D, if a user
749 -- can read a rule, then they can execute a rule.
750 ------------------------------------------------------------------------------
751 select count(*)
752 into l_count
753 from fem_object_catalog_b o
754 ,fem_user_folders u
755 where o.object_id = l_loader_object_id
756 and u.folder_id = o.folder_id
757 and u.user_id = l_user_id;
758
759 if (l_count = 0) then
760
761 select f.folder_name
762 into l_loader_folder_name
763 from fem_folders_vl f
764 ,fem_object_catalog_b o
765 where o.object_id = l_loader_object_id
766 and f.folder_id = o.folder_id;
767
768 FEM_ENGINES_PKG.user_message (
769 p_app_name => G_FEM
770 ,p_msg_name => G_EXEC_NO_FOLDER_ACCESS_ERR
771 ,p_token1 => 'FOLDER_NAME'
772 ,p_value1 => l_loader_folder_name
773 );
774 raise e_loader_error;
775
776 end if;
777
778 ------------------------------------------------------------------------------
779 -- Validate the execution mode input parameter.
780 ------------------------------------------------------------------------------
781 if p_execution_mode not in (g_snapshot,g_error_reprocessing) then
782 FEM_ENGINES_PKG.user_message (
783 p_app_name => G_FEM
784 ,p_msg_name => G_EXT_LDR_EXEC_MODE_ERR
785 );
786 raise e_loader_error;
787 end if;
788
789 ------------------------------------------------------------------------------
790 -- Validate the Dimension input parameter and get the source and target
791 -- hierarchy table names and other hierarchy information
792 ------------------------------------------------------------------------------
793 get_dimension_info (
794 p_dimension_varchar_label => p_dimension_varchar_label
795 ,x_dimension_id => l_dimension_id
796 ,x_target_hier_table => l_target_hier_table
797 ,x_source_hier_table => l_source_hier_table
798 ,x_member_b_table => l_member_b_table
799 ,x_member_attr_table => l_member_attr_table
800 ,x_member_col => l_member_col
801 ,x_member_dc_col => l_member_dc_col
802 ,x_group_use_code => l_group_use_code
803 ,x_value_set_required_flag => l_value_set_required_flag
804 ,x_hier_type_allowed_code => l_hier_type_allowed_code
805 ,x_hier_versioning_type_code => l_hier_versioning_type_code
806 );
807
808 -- If execution mode is Snapshot, then we must add the status where clause
809 -- to all queries on interface tables.
810 if (p_execution_mode = g_snapshot) then
811 l_status_clause := ' and status = ''LOAD''';
812 end if;
813
814 bld_bad_value_sets_stmt (
815 p_dimension_varchar_label => p_dimension_varchar_label
816 ,p_execution_mode => p_execution_mode
817 ,p_value_set_required_flag => l_value_set_required_flag
818 ,x_bad_value_sets_stmt => l_bad_value_sets_stmt
819 );
820
821 bld_bad_dim_groups_stmt (
822 p_dimension_varchar_label => p_dimension_varchar_label
823 ,p_execution_mode => p_execution_mode
824 ,x_bad_dim_groups_stmt => l_bad_dim_groups_stmt
825 );
826
827 if (p_dimension_varchar_label = 'CAL_PERIOD') then
828
829 bld_bad_hier_calendars_stmt (
830 p_dimension_varchar_label => p_dimension_varchar_label
831 ,p_execution_mode => p_execution_mode
832 ,p_source_hier_table => l_source_hier_table
833 ,x_bad_hier_calendars_stmt => l_bad_hier_calendars_stmt
834 );
835
836 -- HIERVAL table for the CAL_PERIOD dimension
837 l_target_hierval_table := 'FEM_HIERVAL_CALP_T';
838
839 else
840
841 -- HIERVAL table for all VSR dimensions
842 l_target_hierval_table := 'FEM_HIERVAL_VSR_T';
843
844 end if;
845
846 bld_bad_hier_value_sets_t_stmt (
847 p_dimension_varchar_label => p_dimension_varchar_label
848 ,p_execution_mode => p_execution_mode
849 ,p_value_set_required_flag => l_value_set_required_flag
850 ,p_source_hier_table => l_source_hier_table
851 ,x_bad_hier_value_sets_t_stmt => l_bad_hier_value_sets_t_stmt
852 );
853
854 bld_bad_hier_value_sets_stmt (
855 p_dimension_varchar_label => p_dimension_varchar_label
856 ,p_execution_mode => p_execution_mode
857 ,p_value_set_required_flag => l_value_set_required_flag
858 ,p_source_hier_table => l_source_hier_table
859 ,x_bad_hier_value_sets_stmt => l_bad_hier_value_sets_stmt
860 );
861
862 bld_bad_hier_multi_vs_stmt (
863 p_dimension_varchar_label => p_dimension_varchar_label
864 ,p_execution_mode => p_execution_mode
865 ,p_value_set_required_flag => l_value_set_required_flag
866 ,p_source_hier_table => l_source_hier_table
867 ,x_bad_hier_multi_vs_stmt => l_bad_hier_multi_vs_stmt
868 );
869
870 bld_bad_hier_members_stmt (
871 p_dimension_varchar_label => p_dimension_varchar_label
872 ,p_execution_mode => p_execution_mode
873 ,p_value_set_required_flag => l_value_set_required_flag
874 ,p_source_hier_table => l_source_hier_table
875 ,p_member_b_table => l_member_b_table
876 ,p_member_col => l_member_col
877 ,p_member_dc_col => l_member_dc_col
878 ,x_bad_hier_members_stmt => l_bad_hier_members_stmt
879 );
880
881 bld_bad_hier_dups_stmt (
882 p_dimension_varchar_label => p_dimension_varchar_label
883 ,p_execution_mode => p_execution_mode
884 ,p_value_set_required_flag => l_value_set_required_flag
885 ,p_source_hier_table => l_source_hier_table
886 ,x_bad_hier_dups_stmt => l_bad_hier_dups_stmt
887 );
888
889 bld_bad_hier_rec_leafs_stmt (
890 p_dimension_varchar_label => p_dimension_varchar_label
891 ,p_target_hierval_table => l_target_hierval_table
892 ,p_member_attr_table => l_member_attr_table
893 ,p_member_col => l_member_col
894 ,x_bad_hier_rec_leafs_stmt => l_bad_hier_rec_leafs_stmt
895 );
896
897 bld_bad_hier_rec_nodes_stmt (
898 p_dimension_varchar_label => p_dimension_varchar_label
899 ,p_target_hierval_table => l_target_hierval_table
900 ,p_member_attr_table => l_member_attr_table
901 ,p_member_col => l_member_col
902 ,x_bad_hier_rec_nodes_stmt => l_bad_hier_rec_nodes_stmt
903 );
904
905 bld_bad_hier_roots_stmt (
906 p_dimension_varchar_label => p_dimension_varchar_label
907 ,p_execution_mode => p_execution_mode
908 ,p_value_set_required_flag => l_value_set_required_flag
909 ,p_source_hier_table => l_source_hier_table
910 ,p_member_b_table => l_member_b_table
911 ,p_member_col => l_member_col
912 ,p_member_dc_col => l_member_dc_col
913 ,x_bad_hier_roots_stmt => l_bad_hier_roots_stmt
914 );
915
916 bld_bad_hier_dim_groups_t_stmt (
917 p_dimension_varchar_label => p_dimension_varchar_label
918 ,p_execution_mode => p_execution_mode
919 ,p_target_hierval_table => l_target_hierval_table
920 ,x_bad_hier_dim_groups_t_stmt => l_bad_hier_dim_groups_t_stmt
921 );
922
923 bld_bad_hier_dim_groups_stmt (
924 p_dimension_varchar_label => p_dimension_varchar_label
925 ,p_target_hierval_table => l_target_hierval_table
926 ,x_bad_hier_dim_groups_stmt => l_bad_hier_dim_groups_stmt
927 );
928
929 bld_bad_hier_dim_grp_skp_stmt (
930 p_dimension_varchar_label => p_dimension_varchar_label
931 ,p_target_hierval_table => l_target_hierval_table
932 ,x_bad_hier_dim_grp_skp_stmt => l_bad_hier_dim_grp_skp_stmt
933 );
934
935 bld_bad_hier_dim_grp_reg_stmt (
936 p_dimension_varchar_label => p_dimension_varchar_label
937 ,p_target_hierval_table => l_target_hierval_table
938 ,x_bad_hier_dim_grp_reg_stmt => l_bad_hier_dim_grp_reg_stmt
939 );
940
941 bld_root_node_count_stmt (
942 p_dimension_varchar_label => p_dimension_varchar_label
943 ,p_execution_mode => p_execution_mode
944 ,p_value_set_required_flag => l_value_set_required_flag
945 ,p_source_hier_table => l_source_hier_table
946 ,x_root_node_count_stmt => l_root_node_count_stmt
947 );
948
949 bld_get_value_sets_stmt (
950 p_dimension_varchar_label => p_dimension_varchar_label
951 ,p_execution_mode => p_execution_mode
952 ,p_value_set_required_flag => l_value_set_required_flag
953 ,x_get_value_sets_stmt => l_get_value_sets_stmt
954 );
955
956 bld_get_dim_groups_t_stmt (
957 p_dimension_varchar_label => p_dimension_varchar_label
958 ,p_execution_mode => p_execution_mode
959 ,x_get_dim_groups_t_stmt => l_get_dim_groups_t_stmt
960 );
961
962 bld_get_dim_groups_stmt (
963 p_dimension_varchar_label => p_dimension_varchar_label
964 ,p_execution_mode => p_execution_mode
965 ,x_get_dim_groups_stmt => l_get_dim_groups_stmt
966 );
967
968 bld_get_hier_defs_stmt (
969 p_dimension_varchar_label => p_dimension_varchar_label
970 ,p_execution_mode => p_execution_mode
971 ,x_get_hier_defs_stmt => l_get_hier_defs_stmt
972 );
973
974 bld_get_hier_roots_stmt (
975 p_dimension_varchar_label => p_dimension_varchar_label
976 ,p_execution_mode => p_execution_mode
977 ,p_value_set_required_flag => l_value_set_required_flag
978 ,p_group_use_code => l_group_use_code
979 ,p_source_hier_table => l_source_hier_table
980 ,p_member_b_table => l_member_b_table
981 ,p_member_col => l_member_col
982 ,p_member_dc_col => l_member_dc_col
983 ,x_get_hier_roots_stmt => l_get_hier_roots_stmt
984 );
985
986 bld_get_hier_rels_stmt (
987 p_dimension_varchar_label => p_dimension_varchar_label
988 ,p_execution_mode => p_execution_mode
989 ,p_value_set_required_flag => l_value_set_required_flag
990 ,p_group_use_code => l_group_use_code
991 ,p_source_hier_table => l_source_hier_table
992 ,p_member_b_table => l_member_b_table
993 ,p_member_col => l_member_col
994 ,p_member_dc_col => l_member_dc_col
995 ,x_get_hier_rels_stmt => l_get_hier_rels_stmt
996 );
997
998 bld_insert_hier_rels_stmt (
999 p_dimension_varchar_label => p_dimension_varchar_label
1000 ,p_value_set_required_flag => l_value_set_required_flag
1001 ,p_target_hier_table => l_target_hier_table
1002 ,p_target_hierval_table => l_target_hierval_table
1003 ,x_insert_hier_rels_stmt => l_insert_hier_rels_stmt
1004 );
1005
1006 bld_delete_hier_rels_stmt (
1007 p_dimension_varchar_label => p_dimension_varchar_label
1008 ,p_execution_mode => p_execution_mode
1009 ,p_source_hier_table => l_source_hier_table
1010 ,x_delete_hier_rels_stmt => l_delete_hier_t_rels_stmt
1011 );
1012
1013 bld_delete_hier_rels_stmt (
1014 p_dimension_varchar_label => p_dimension_varchar_label
1015 ,p_execution_mode => p_execution_mode
1016 ,p_target_hier_table => l_target_hier_table
1017 ,x_delete_hier_rels_stmt => l_delete_hier_rels_stmt
1018 );
1019
1020 bld_insert_hierval_rels_stmt (
1021 p_dimension_varchar_label => p_dimension_varchar_label
1022 ,p_target_hierval_table => l_target_hierval_table
1023 ,x_insert_hierval_rels_stmt => l_insert_hierval_rels_stmt
1024 );
1025
1026
1027 ------------------------------------------------------------------------------
1028 -- STEP 1: Check to see that the specified hierarchy object name and
1029 -- hierarchy object definition name exist in FEM_HIERARCHIES_T for the
1030 -- given dimension.
1031 ------------------------------------------------------------------------------
1032 FEM_ENGINES_PKG.tech_message (
1033 p_severity => g_log_level_1
1034 ,p_module => G_BLOCK||'.'||l_api_name
1035 ,p_msg_text => 'Step 1: Hierarchy Count in FEM_HIERARCHIES_T'
1036 );
1037
1038 execute immediate
1039 ' select count(*)'||
1040 ' from fem_hierarchies_t'||
1041 ' where hierarchy_object_name = :b_hierarchy_object_name'||
1042 ' and hier_obj_def_display_name = :b_hier_obj_def_display_name'||
1043 ' and dimension_varchar_label = :b_dimension_varchar_label'||
1044 l_status_clause||
1045 ' and language = userenv(''LANG'')'
1046 into l_count
1047 using p_hierarchy_object_name
1048 ,p_hier_obj_def_display_name
1049 ,p_dimension_varchar_label;
1050
1051 if (l_count = 0) then
1052 -- Hierarchy not found in FEM_HIERARCHIES_T
1053 FEM_ENGINES_PKG.user_message (
1054 p_app_name => G_FEM
1055 ,p_msg_name => G_HIER_LDR_NO_HIER_ERR
1056 ,p_token1 => 'HIERARCHY_OBJECT_NAME'
1057 ,p_value1 => p_hierarchy_object_name
1058 ,p_token2 => 'HIER_OBJ_DEF_DISPLAY_NAME'
1059 ,p_value2 => p_hier_obj_def_display_name
1060 );
1061 raise e_loader_error;
1062 elsif (l_count > 1) then
1063 -- Multiple hierarchy definitions found in FEM_HIERARCHIES_T
1064 FEM_ENGINES_PKG.user_message (
1065 p_app_name => G_FEM
1066 ,p_msg_name => G_HIER_LDR_MULTI_HIER_ERR
1067 ,p_token1 => 'HIERARCHY_OBJECT_NAME'
1068 ,p_value1 => p_hierarchy_object_name
1069 ,p_token2 => 'HIER_OBJ_DEF_DISPLAY_NAME'
1070 ,p_value2 => p_hier_obj_def_display_name
1071 );
1072 raise e_loader_error;
1073 end if;
1074
1075
1076 ------------------------------------------------------------------------------
1077 -- STEP 2: Register the process execution in the Processing Locks data model
1078 ------------------------------------------------------------------------------
1079 FEM_ENGINES_PKG.tech_message (
1080 p_severity => g_log_level_1
1081 ,p_module => G_BLOCK||'.'||l_api_name
1082 ,p_msg_text => 'Step 2: Register Process Execution'
1083 );
1084
1085 register_process_execution (
1086 p_request_id => l_request_id
1087 ,p_object_id => l_loader_object_id
1088 ,p_obj_def_id => l_loader_obj_def_id
1089 ,p_execution_mode => p_execution_mode
1090 ,p_user_id => l_user_id
1091 ,p_login_id => l_login_id
1092 ,p_pgm_id => l_pgm_id
1093 ,p_pgm_app_id => l_pgm_app_id
1094 ,p_hierarchy_object_name => p_hierarchy_object_name
1095 );
1096
1097
1098 ------------------------------------------------------------------------------
1099 -- Load all hierarchy information.
1100 ------------------------------------------------------------------------------
1101 open cv_get_hier_defs
1102 for l_get_hier_defs_stmt
1103 using p_dimension_varchar_label
1104 ,p_hierarchy_object_name
1105 ,p_hier_obj_def_display_name;
1106
1107 l_get_hier_defs_is_open := true;
1108
1109 loop
1110
1111 fetch cv_get_hier_defs into
1112 ld_rowid
1113 ,ld_folder_name
1114 ,ld_hierarchy_object_name
1115 ,ld_hier_obj_def_display_name
1116 ,ld_effective_start_date
1117 ,ld_effective_end_date
1118 ,ld_calendar_dc
1119 ,ld_language
1120 ,ld_dimension_varchar_label
1121 ,ld_hierarchy_type_code
1122 ,ld_group_seq_enforced_code
1123 ,ld_multi_top_flag
1124 ,ld_multi_value_set_flag
1125 ,ld_hierarchy_usage_code
1126 ,ld_flattened_rows_flag
1127 ,ld_status;
1128
1129 exit when cv_get_hier_defs%NOTFOUND;
1130
1131 -- Initialize hierarchy error flag to false
1132 l_hierarchy_error_flag := false;
1133
1134 <<to_next_hier_for_loading>>
1135 loop
1136
1137 FEM_ENGINES_PKG.tech_message (
1138 p_severity => g_log_level_1
1139 ,p_module => G_BLOCK||'.'||l_api_name||'.cv_get_hier_defs'
1140 ,p_msg_text =>
1141 ' ld_folder_name='||ld_folder_name||
1142 ' ld_hierarchy_object_name='||ld_hierarchy_object_name||
1143 ' ld_hier_obj_def_display_name='||ld_hier_obj_def_display_name||
1144 ' ld_effective_start_date='||ld_effective_start_date||
1145 ' ld_effective_end_date='||ld_effective_end_date||
1146 ' ld_calendar_dc='||ld_calendar_dc||
1147 ' ld_language='||ld_language||
1148 ' ld_dimension_varchar_label='||ld_dimension_varchar_label||
1149 ' ld_hierarchy_type_code='||ld_hierarchy_type_code||
1150 ' ld_group_seq_enforced_code='||ld_group_seq_enforced_code||
1151 ' ld_multi_top_flag='||ld_multi_top_flag||
1152 ' ld_multi_value_set_flag='||ld_multi_value_set_flag||
1153 ' ld_hierarchy_usage_code='||ld_hierarchy_usage_code||
1154 ' ld_flattened_rows_flag='||ld_flattened_rows_flag||
1155 ' ld_status='||ld_status
1156 );
1157
1158 ------------------------------------------------------------------------------
1159 -- STEP 3: Check for Existing Object ID and Object Definition ID, and check
1160 -- Effective Dates.
1161 ------------------------------------------------------------------------------
1162 FEM_ENGINES_PKG.tech_message (
1163 p_severity => g_log_level_1
1164 ,p_module => G_BLOCK||'.'||l_api_name
1165 ,p_msg_text => 'Step 3: Catalog of Objects record and effective date checks'
1166 );
1167
1168 ------------------------------------------------------------------------------
1169 -- STEP 3.1: Check to see if there is an existing object id with the same
1170 -- hierarchy object name and dimension id.
1171 ------------------------------------------------------------------------------
1172 FEM_ENGINES_PKG.tech_message (
1173 p_severity => g_log_level_1
1174 ,p_module => G_BLOCK||'.'||l_api_name
1175 ,p_msg_text => 'Step 3.1: Check for Existing Object ID'
1176 );
1177
1178 -- Start by assuming that the load will be an update to an existing
1179 -- hierarchy definition.
1180 ld_load_type := g_update_hier_def;
1181
1182 begin
1183
1184 select h.hierarchy_obj_id
1185 ,h.hierarchy_type_code
1186 ,h.group_sequence_enforced_code
1187 ,h.multi_top_flag
1188 ,h.multi_value_set_flag
1189 ,h.hierarchy_usage_code
1190 ,h.flattened_rows_flag
1191 ,h.calendar_id
1192 into ld_hierarchy_object_id
1193 ,ld_hierarchy_type_code
1194 ,ld_group_seq_enforced_code
1195 ,ld_multi_top_flag
1196 ,ld_multi_value_set_flag
1197 ,ld_hierarchy_usage_code
1198 ,ld_flattened_rows_flag
1199 ,ld_calendar_id
1200 from fem_object_catalog_vl cat
1201 ,fem_hierarchies h
1202 where cat.object_name = ld_hierarchy_object_name
1203 and cat.object_type_code = 'HIERARCHY'
1204 and h.hierarchy_obj_id = cat.object_id
1205 and h.dimension_id = l_dimension_id;
1206
1207 FEM_ENGINES_PKG.tech_message (
1208 p_severity => g_log_level_1
1209 ,p_module => G_BLOCK||'.'||l_api_name||'.get_hier_obj_id'
1210 ,p_msg_text =>
1211 ' ld_hierarchy_object_id='||ld_hierarchy_object_id||
1212 ' ld_hierarchy_type_code='||ld_hierarchy_type_code||
1213 ' ld_group_seq_enforced_code='||ld_group_seq_enforced_code||
1214 ' ld_multi_top_flag='||ld_multi_top_flag||
1215 ' ld_multi_value_set_flag='||ld_multi_value_set_flag||
1216 ' ld_hierarchy_usage_code='||ld_hierarchy_usage_code||
1217 ' ld_flattened_rows_flag='||ld_flattened_rows_flag
1218 );
1219
1220 exception
1221 when no_data_found then
1222 -- No hierarchy object id found. New Hierarchy Load.
1223 ld_load_type := g_new_hier;
1224 end;
1225
1226 ------------------------------------------------------------------------------
1227 -- STEP 3.2: For an existing object id, check to see if there is an existing
1228 -- object definition id with the same hierarchy object definition display
1229 -- name.
1230 ------------------------------------------------------------------------------
1231 FEM_ENGINES_PKG.tech_message (
1232 p_severity => g_log_level_1
1233 ,p_module => G_BLOCK||'.'||l_api_name
1234 ,p_msg_text => 'Step 3.2: Check for Existing Object Definition ID'
1235 );
1236
1237 if (ld_load_type <> g_new_hier) then
1238
1239 begin
1240
1241 select object_definition_id
1242 ,effective_start_date
1243 ,effective_end_date
1244 into ld_hier_obj_def_id
1245 ,ld_effective_start_date
1246 ,ld_effective_end_date
1247 from fem_object_definition_vl
1248 where object_id = ld_hierarchy_object_id
1249 and display_name = ld_hier_obj_def_display_name
1250 and old_approved_copy_flag = 'N';
1251
1252 FEM_ENGINES_PKG.tech_message (
1253 p_severity => g_log_level_1
1254 ,p_module => G_BLOCK||'.'||l_api_name||'.get_hier_obj_def_id'
1255 ,p_msg_text =>
1256 ' ld_hier_obj_def_id='||ld_hier_obj_def_id||
1257 ' ld_effective_start_date='||ld_effective_start_date||
1258 ' ld_effective_end_date='||ld_effective_end_date
1259 );
1260
1261 exception
1262 when no_data_found then
1263 -- No hierarchy object definition id found. New Hierarchy Load.
1264 ld_load_type := g_new_hier_def;
1265 end;
1266
1267 end if;
1268
1269 --BEGIN:effective_date_validation
1270 ------------------------------------------------------------------------------
1271 -- STEP 3.3: Effective Date Validations. We must make sure that there are no
1272 -- overlaps with existing object definitions. If the user does not specify
1273 -- values for the effective start and end dates, then we must try to provide
1274 -- default values.
1275 ------------------------------------------------------------------------------
1276 FEM_ENGINES_PKG.tech_message (
1277 p_severity => g_log_level_1
1278 ,p_module => G_BLOCK||'.'||l_api_name
1279 ,p_msg_text => 'Step 3.3: Effective Date Validations'
1280 );
1281
1282 l_new_max_obj_def_id := null;
1283 l_new_max_eff_end_date := null;
1284
1285 if (ld_load_type = g_new_hier) then
1286
1287 -- If end date is null, then use the default end date.
1288 if (ld_effective_end_date is null) then
1289 ld_effective_end_date := get_default_end_date;
1290 end if;
1291 -- Make sure that the end date does not have a time component.
1292 ld_effective_end_date := trunc(ld_effective_end_date);
1293
1294 -- If start date is null, then use the default start date.
1295 if (ld_effective_start_date is null) then
1296 ld_effective_start_date := get_default_start_date;
1297 end if;
1298 -- Make sure that the start date does not have a time component.
1299 ld_effective_start_date := trunc(ld_effective_start_date);
1300
1301 -- Check that start date is not greater than end date
1302 if (ld_effective_start_date > ld_effective_end_date) then
1303
1304 FEM_ENGINES_PKG.user_message (
1305 p_app_name => G_FEM
1306 ,p_msg_name => G_HIER_LDR_EFF_DATE_RANG_ERR
1307 ,p_token1 => 'END_DATE'
1308 ,p_value1 => FND_DATE.date_to_chardate(ld_effective_end_date)
1309 ,p_token2 => 'START_DATE'
1310 ,p_value2 => FND_DATE.date_to_chardate(ld_effective_start_date)
1311 );
1312 l_hierarchy_error_flag := true;
1313
1314 end if;
1315
1316 elsif (ld_load_type = g_new_hier_def) then
1317
1318 -- if end date is null, then use the default end date.
1319 if (ld_effective_end_date is null) then
1320 ld_effective_end_date := get_default_end_date;
1321 end if;
1322 -- Make sure that the end date does not have a time component.
1323 ld_effective_end_date := trunc(ld_effective_end_date);
1324
1325 -- If the start date is null, then we must find the existing object
1326 -- definition with the largest end date.
1327 if (ld_effective_start_date is null) then
1328
1329 select object_definition_id
1330 ,trunc(effective_start_date)
1331 ,trunc(effective_end_date)
1332 into l_max_object_definition_id
1333 ,l_max_effective_start_date
1334 ,l_max_effective_end_date
1335 from fem_object_definition_b b1
1336 where object_id = ld_hierarchy_object_id
1337 and old_approved_copy_flag = 'N'
1338 and effective_end_date = (
1339 select max(effective_end_date)
1340 from fem_object_definition_b b2
1341 where b2.object_id = b1.object_id
1342 and b2.old_approved_copy_flag = b1.old_approved_copy_flag
1343 );
1344
1345 -- If the largest end date is greater than the default end date, then
1346 -- the largest object definition is invalid.
1347 if (l_max_effective_end_date > get_default_end_date) then
1348
1349 FEM_ENGINES_PKG.user_message (
1350 p_app_name => G_FEM
1351 ,p_msg_name => G_HIER_LDR_EFF_DATE_OVLP_ERR
1352 ,p_token1 => 'HIERARCHY_OBJECT_NAME'
1353 ,p_value1 => ld_hierarchy_object_name
1354 );
1355 l_hierarchy_error_flag := true;
1356
1357 -- If the largest end date is equal to the default end date, then
1358 -- then we must adjust the largest end date to be the sysdate. This
1359 -- will leave room for the new object definition to have a date range
1360 -- from sysdate+1 to default end date.
1361 elsif (l_max_effective_end_date = get_default_end_date) then
1362
1363 -- Get the sysdate without any time component.
1364 l_current_date := trunc(sysdate);
1365
1366 -- Do not allow an update on the largest object definition if its
1367 -- start date is greater than the sysdate. Otherwise
1368 -- the largest object definition will be updated to have an
1369 -- end date that is smaller than its start date.
1370 if (l_max_effective_start_date > l_current_date) then
1371
1372 FEM_ENGINES_PKG.user_message (
1373 p_app_name => G_FEM
1374 ,p_msg_name => G_HIER_LDR_EFF_DATE_OVLP_ERR
1375 ,p_token1 => 'HIERARCHY_OBJECT_NAME'
1376 ,p_value1 => ld_hierarchy_object_name
1377 );
1378 l_hierarchy_error_flag := true;
1379
1380 else
1381
1382 l_max_effective_end_date := l_current_date;
1383 ld_effective_start_date := l_current_date + 1;
1384
1385 -- Check that an update to the end date of the largest object
1386 -- definition will not invalidate any dependent data.
1387 FEM_PL_PKG.effective_date_incl_rslt_data(
1388 p_object_definition_id => l_max_object_definition_id
1389 ,p_new_effective_start_date => l_max_effective_start_date
1390 ,p_new_effective_end_date => l_max_effective_end_date
1391 ,x_msg_count => l_msg_count
1392 ,x_msg_data => l_msg_data
1393 ,x_date_incl_rslt_data => l_date_incl_rslt_data
1394 );
1395
1396 -- If true, then largest object definition is still valid and it
1397 -- is OK to update max object definition.
1398 if (FND_API.To_Boolean(l_date_incl_rslt_data)) then
1399
1400 l_new_max_obj_def_id := l_max_object_definition_id;
1401 l_new_max_eff_end_date := l_max_effective_end_date;
1402
1403 else
1404
1405 --todo: this API should be put in a common loader package.
1406 get_put_messages (
1407 p_msg_count => l_msg_count
1408 ,p_msg_data => l_msg_data
1409 );
1410
1411 FEM_ENGINES_PKG.user_message (
1412 p_app_name => G_FEM
1413 ,p_msg_name => G_HIER_LDR_DEF_DATA_RANG_ERR
1414 ,p_token1 => 'HIERARCHY_OBJECT_NAME'
1415 ,p_value1 => ld_hierarchy_object_name
1416 );
1417 l_hierarchy_error_flag := true;
1418
1419 end if;
1420
1421 end if;
1422
1423 -- If the largest end date is less that the default end date, then
1424 -- only need to set the new start date to be the day after the
1425 -- end date of the largest object definition.
1426 else -- (l_max_effective_end_date < DEFAULT_END_DATE)
1427
1428 ld_effective_start_date := l_max_effective_end_date + 1;
1429
1430 end if;
1431
1432 -- As the user specified a start date, we must check that the date
1433 -- range of the new object definition will not overlap with any other
1434 -- object definitions.
1435 else -- (ld_effective_start_date is not null)
1436
1437 -- Make sure that the start date does not have a time component.
1438 ld_effective_start_date := trunc(ld_effective_start_date);
1439
1440 -- Check that start date is not greater than end date
1441 if (ld_effective_start_date > ld_effective_end_date) then
1442
1443 FEM_ENGINES_PKG.user_message (
1444 p_app_name => G_FEM
1445 ,p_msg_name => G_HIER_LDR_EFF_DATE_RANG_ERR
1446 ,p_token1 => 'END_DATE'
1447 ,p_value1 => FND_DATE.date_to_chardate(ld_effective_end_date)
1448 ,p_token2 => 'START_DATE'
1449 ,p_value2 => FND_DATE.date_to_chardate(ld_effective_start_date)
1450 );
1451 l_hierarchy_error_flag := true;
1452
1453 else
1454
1455 -- Perform the overlap check by calling the appropriate API
1456 -- from the business rule framework.
1457 FEM_BUSINESS_RULE_PVT.CheckOverlapObjDefs(
1458 p_obj_id => ld_hierarchy_object_id
1459 ,p_exclude_obj_def_id => null
1460 ,p_effective_start_date => ld_effective_start_date
1461 ,p_effective_end_date => ld_effective_end_date
1462 ,p_init_msg_list => FND_API.G_TRUE
1463 ,x_return_status => l_return_status
1464 ,x_msg_count => l_msg_count
1465 ,x_msg_data => l_msg_data
1466 );
1467
1468 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1469
1470 --todo: this API should be put in a common loader package.
1471 get_put_messages (
1472 p_msg_count => l_msg_count
1473 ,p_msg_data => l_msg_data
1474 );
1475
1476 FEM_ENGINES_PKG.user_message (
1477 p_app_name => G_FEM
1478 ,p_msg_name => G_HIER_LDR_EFF_DATE_OVLP_ERR
1479 ,p_token1 => 'HIERARCHY_OBJECT_NAME'
1480 ,p_value1 => ld_hierarchy_object_name
1481 );
1482 l_hierarchy_error_flag := true;
1483
1484 end if;
1485
1486 end if;
1487
1488 end if;
1489
1490 end if;
1491 --END:effective_date_validation
1492
1493 ------------------------------------------------------------------------------
1494 -- STEP 3.4: Update Hierarchy Definition Validations.
1495 ------------------------------------------------------------------------------
1496 FEM_ENGINES_PKG.tech_message (
1497 p_severity => g_log_level_1
1498 ,p_module => G_BLOCK||'.'||l_api_name
1499 ,p_msg_text => 'Step 3.4: Update Hierarchy Definition Validations'
1500 );
1501
1502 if (ld_load_type = g_update_hier_def) then
1503
1504 ------------------------------------------------------------------------------
1505 -- STEP 3.4.1: Check for Edit or Approval Lock.
1506 ------------------------------------------------------------------------------
1507 FEM_ENGINES_PKG.tech_message (
1508 p_severity => g_log_level_1
1509 ,p_module => G_BLOCK||'.'||l_api_name
1510 ,p_msg_text => 'Step 3.4.1: Check for Edit or Approval Lock'
1511 );
1512
1513 FEM_PL_PKG.get_object_def_edit_locks(
1514 p_object_definition_id => ld_hier_obj_def_id
1515 ,x_approval_edit_lock_exists => l_approval_edit_lock_exists
1516 ,x_data_edit_lock_exists => l_data_edit_lock_exists
1517 );
1518
1519 FEM_ENGINES_PKG.tech_message (
1520 p_severity => g_log_level_1
1521 ,p_module => G_BLOCK||'.'||l_api_name||'.get_object_def_edit_locks'
1522 ,p_msg_text => 'l_approval_edit_lock_exists='||l_approval_edit_lock_exists||' l_data_edit_lock_exists='||l_data_edit_lock_exists
1523 );
1524
1525 if ( FND_API.To_Boolean(l_approval_edit_lock_exists)
1526 or FND_API.To_Boolean(l_data_edit_lock_exists) ) then
1527
1528 FEM_ENGINES_PKG.user_message (
1529 p_app_name => G_FEM
1530 ,p_msg_name => G_HIER_LDR_DEF_DATA_LOCK_ERR
1531 ,p_token1 => 'HIERARCHY_OBJECT_NAME'
1532 ,p_value1 => ld_hierarchy_object_name
1533 ,p_token2 => 'HIER_OBJ_DEF_DISPLAY_NAME'
1534 ,p_value2 => ld_hier_obj_def_display_name
1535 );
1536 -- Raise exception as we cannot allow the hierarchy to be updated.
1537 -- No need for further validations.
1538 raise e_hierarchy_error;
1539
1540 end if;
1541
1542 ------------------------------------------------------------------------------
1543 -- STEP 3.4.2: Check for Read Only Relationships.
1544 ------------------------------------------------------------------------------
1545 FEM_ENGINES_PKG.tech_message (
1546 p_severity => g_log_level_1
1547 ,p_module => G_BLOCK||'.'||l_api_name
1548 ,p_msg_text => 'Step 3.4.2: Check for Read Only Relationships'
1549 );
1550
1551 execute immediate
1552 ' select count(*)'||
1553 ' from '||l_target_hier_table||
1554 ' where hierarchy_obj_def_id = :b_hier_obj_def_id'||
1555 ' and read_only_flag = ''Y'''
1556 into l_count
1557 using ld_hier_obj_def_id;
1558
1559 if (l_count > 0) then
1560
1561 -- Cannot update an existing hierarchy object definition if it
1562 -- has read only parent-child relationships.
1563 FEM_ENGINES_PKG.user_message (
1564 p_app_name => G_FEM
1565 ,p_msg_name => G_HIER_LDR_READONLY_HIER_ERR
1566 );
1567
1568 -- Raise exception as we cannot allow the hierarchy to be updated.
1569 -- No need for further validations.
1570 raise e_hierarchy_error;
1571
1572 end if;
1573
1574 end if;
1575
1576 --BEGIN:eng_master_prep
1577 ------------------------------------------------------------------------------
1578 -- STEP 4: Check to see if the user executing the hierarchy load has write
1579 -- access to the folder. If yes, the obtain the folder id.
1580 ------------------------------------------------------------------------------
1581 FEM_ENGINES_PKG.tech_message (
1582 p_severity => g_log_level_1
1583 ,p_module => G_BLOCK||'.'||l_api_name
1584 ,p_msg_text => 'Step 4: Folder Validation'
1585 );
1586
1587 begin
1588 -- Bug Fix 3584893: HIER LOADER - ADD FOLDER SECURITY CHECK
1589 select f.folder_id
1590 into ld_folder_id
1591 from fem_folders_vl f
1592 ,fem_user_folders uf
1593 where uf.folder_id = f.folder_id
1594 and uf.user_id = l_user_id
1595 and uf.write_flag = 'Y'
1596 and f.folder_name = ld_folder_name;
1597 exception
1598 when no_data_found then
1599 FEM_ENGINES_PKG.user_message (
1600 p_app_name => G_FEM
1601 ,p_msg_name => G_HIER_LDR_FOLDER_ERR
1602 ,p_token1 => 'FOLDER_NAME'
1603 ,p_value1 => ld_folder_name
1604 );
1605 l_hierarchy_error_flag := true;
1606 end;
1607
1608
1609 ------------------------------------------------------------------------------
1610 -- STEP 5: New hierarchies listed in FEM_HIERARCHIES_T must pass all
1611 -- hierarchy object validations.
1612 ------------------------------------------------------------------------------
1613 if (ld_load_type = g_new_hier) then
1614
1615 FEM_ENGINES_PKG.tech_message (
1616 p_severity => g_log_level_1
1617 ,p_module => G_BLOCK||'.'||l_api_name
1618 ,p_msg_text => 'Step 5: New Hierarchy Validations'
1619 );
1620
1621 ------------------------------------------------------------------------------
1622 -- STEP 5.1: Verify that HIERARCHY_TYPE_CODE on FEM_HIERARCHIES_T
1623 -- is valid (OPEN, RECONCILIATION, DAG).
1624 ------------------------------------------------------------------------------
1625 FEM_ENGINES_PKG.tech_message (
1626 p_severity => g_log_level_1
1627 ,p_module => G_BLOCK||'.'||l_api_name
1628 ,p_msg_text => 'Step 5.1: HIERARCHY_TYPE_CODE Validation'
1629 );
1630
1631 begin
1632 select 1
1633 into l_dummy
1634 from fem_lookups
1635 where lookup_type = 'FEM_HIERARCHY_TYPE_DSC'
1636 and lookup_code = ld_hierarchy_type_code;
1637 exception
1638 when no_data_found then
1639 FEM_ENGINES_PKG.user_message (
1640 p_app_name => G_FEM
1641 ,p_msg_name => G_HIER_LDR_HIER_TYPE_CD_ERR
1642 ,p_token1 => 'HIERARCHY_TYPE_CODE'
1643 ,p_value1 => ld_hierarchy_type_code
1644 );
1645 l_hierarchy_error_flag := true;
1646 end;
1647
1648 if ( ( l_hier_type_allowed_code <> 'ALL')
1649 and ( l_hier_type_allowed_code <> ld_hierarchy_type_code) ) then
1650
1651 FEM_ENGINES_PKG.user_message (
1652 p_app_name => G_FEM
1653 ,p_msg_name => G_HIER_LDR_HIER_TYPE_VAL_ERR
1654 ,p_token1 => 'HIERARCHY_TYPE_CODE'
1655 ,p_value1 => l_hier_type_allowed_code
1656 );
1657 l_hierarchy_error_flag := true;
1658
1659 end if;
1660
1661 ------------------------------------------------------------------------------
1662 -- STEP 5.2: Verify that HIERARCHY_USAGE_CODE on FEM_HIERARCHIES_T
1663 -- is valid (PLANNING, STANDARD, CONSOLIDATION).
1664 ------------------------------------------------------------------------------
1665 FEM_ENGINES_PKG.tech_message (
1666 p_severity => g_log_level_1
1667 ,p_module => G_BLOCK||'.'||l_api_name
1668 ,p_msg_text => 'Step 5.2: HIERARCHY_USAGE_CODE Validation'
1669 );
1670
1671 begin
1672 select 1
1673 into l_dummy
1674 from fem_lookups
1675 where lookup_type = 'FEM_HIERARCHY_USAGE_DSC'
1676 and lookup_code = ld_hierarchy_usage_code;
1677 exception
1678 when no_data_found then
1679 FEM_ENGINES_PKG.user_message (
1680 p_app_name => G_FEM
1681 ,p_msg_name => G_HIER_LDR_HIER_USG_CD_ERR
1682 ,p_token1 => 'HIERARCHY_USAGE_CODE'
1683 ,p_value1 => ld_hierarchy_usage_code
1684 );
1685 l_hierarchy_error_flag := true;
1686 end;
1687
1688 ------------------------------------------------------------------------------
1689 -- STEP 5.3: Verify that GROUP_SEQUENCE_ENFORCED_CODE on FEM_HIERARCHIES_T
1690 -- is valid.
1691 ------------------------------------------------------------------------------
1692 FEM_ENGINES_PKG.tech_message (
1693 p_severity => g_log_level_1
1694 ,p_module => G_BLOCK||'.'||l_api_name
1695 ,p_msg_text => 'Step 5.3: GROUP_SEQUENCE_ENFORCED_CODE Validation'
1696 );
1697
1698 begin
1699 select 1
1700 into l_dummy
1701 from fem_lookups
1702 where lookup_type = 'FEM_GROUP_SEQ_ENFORCED_DSC'
1703 and lookup_code = ld_group_seq_enforced_code;
1704 exception
1705 when no_data_found then
1706 FEM_ENGINES_PKG.user_message (
1707 p_app_name => G_FEM
1708 ,p_msg_name => G_HIER_LDR_GRP_SQ_ENF_CD_ERR
1709 ,p_token1 => 'GROUP_SEQ_ENFORCED_CODE'
1710 ,p_value1 => ld_group_seq_enforced_code
1711 );
1712 l_hierarchy_error_flag := true;
1713 end;
1714
1715 if (l_group_use_code = 'REQUIRED') then
1716
1717 if (ld_group_seq_enforced_code = 'NO_GROUPS') then
1718
1719 FEM_ENGINES_PKG.user_message (
1720 p_app_name => G_FEM
1721 ,p_msg_name => G_HIER_LDR_GRP_SQ_REQ_ERR
1722 );
1723 l_hierarchy_error_flag := true;
1724
1725 end if;
1726
1727 elsif (l_group_use_code = 'NOT_SUPPORTED') then
1728
1729 if (ld_group_seq_enforced_code <> 'NO_GROUPS') then
1730
1731 FEM_ENGINES_PKG.user_message (
1732 p_app_name => G_FEM
1733 ,p_msg_name => G_HIER_LDR_GRP_SQ_VAL_ERR
1734 ,p_token1 => 'GROUP_SEQ_ENFORCED_CODE'
1735 ,p_value1 => 'NO_GROUPS'
1736 );
1737 l_hierarchy_error_flag := true;
1738
1739 end if;
1740
1741 end if;
1742
1743 ------------------------------------------------------------------------------
1744 -- STEP 5.4: Verify that MULTI_TOP_FLAG on FEM_HIERARCHIES_T is valid (Y,N).
1745 ------------------------------------------------------------------------------
1746 FEM_ENGINES_PKG.tech_message (
1747 p_severity => g_log_level_1
1748 ,p_module => G_BLOCK||'.'||l_api_name
1749 ,p_msg_text => 'Step 5.4: MULTI_TOP_FLAG Validation'
1750 );
1751
1752 if (ld_multi_top_flag not in ('Y','N')) then
1753
1754 FEM_ENGINES_PKG.user_message (
1755 p_app_name => G_FEM
1756 ,p_msg_name => G_HIER_LDR_MULTI_TOP_FLG_ERR
1757 ,p_token1 => 'MULTI_TOP_FLAG'
1758 ,p_value1 => ld_multi_top_flag
1759 );
1760 l_hierarchy_error_flag := true;
1761
1762 end if;
1763
1764 ------------------------------------------------------------------------------
1765 -- STEP 5.5: Verify that MULTI_VALUE_SET_FLAG on FEM_HIERARCHIES_T is
1766 -- valid (Y,N).
1767 ------------------------------------------------------------------------------
1768 FEM_ENGINES_PKG.tech_message (
1769 p_severity => g_log_level_1
1770 ,p_module => G_BLOCK||'.'||l_api_name
1771 ,p_msg_text => 'Step 5.5: MULTI_VALUE_SET_FLAG Validation'
1772 );
1773
1774 if (ld_multi_value_set_flag not in ('Y','N')) then
1775
1776 FEM_ENGINES_PKG.user_message (
1777 p_app_name => G_FEM
1778 ,p_msg_name => G_HIER_LDR_MULTI_VS_FLG_ERR
1779 ,p_token1 => 'MULTI_VALUE_SET_FLAG'
1780 ,p_value1 => ld_multi_value_set_flag
1781 );
1782 l_hierarchy_error_flag := true;
1783
1784 else
1785
1786 if ( (l_value_set_required_flag = 'N')
1787 and (ld_multi_value_set_flag = 'Y') ) then
1788
1789 FEM_ENGINES_PKG.user_message (
1790 p_app_name => G_FEM
1791 ,p_msg_name => G_HIER_LDR_MULT_VS_FLG_V_ERR
1792 ,p_token1 => 'MULTI_VALUE_SET_FLAG'
1793 ,p_value1 => 'Y'
1794 );
1795 l_hierarchy_error_flag := true;
1796
1797 end if;
1798
1799 end if;
1800
1801 ------------------------------------------------------------------------------
1802 -- STEP 5.6: Verify that FLATTENED_ROWS_FLAG on FEM_HIERARCHIES_T is
1803 -- valid (Y,N).
1804 ------------------------------------------------------------------------------
1805 FEM_ENGINES_PKG.tech_message (
1806 p_severity => g_log_level_1
1807 ,p_module => G_BLOCK||'.'||l_api_name
1808 ,p_msg_text => 'Step 5.6: FLATTENED_ROWS_FLAG Validation'
1809 );
1810
1811 if (ld_flattened_rows_flag not in ('Y','N')) then
1812
1813 FEM_ENGINES_PKG.user_message (
1814 p_app_name => G_FEM
1815 ,p_msg_name => G_HIER_LDR_FLAT_ROWS_FLG_ERR
1816 ,p_token1 => 'FLATTENED_ROWS_FLAG'
1817 ,p_value1 => ld_flattened_rows_flag
1818 );
1819 l_hierarchy_error_flag := true;
1820
1821 end if;
1822
1823 ------------------------------------------------------------------------------
1824 -- STEP 5.7: Only for the CAL_PERIOD dimension, verify that a valid
1825 -- CALENDAR_DISPLAY_CODE has been specified for the hierarchy in
1826 -- FEM_HIERARCHIES_T.
1827 ------------------------------------------------------------------------------
1828 if (p_dimension_varchar_label = 'CAL_PERIOD') then
1829
1830 FEM_ENGINES_PKG.tech_message (
1831 p_severity => g_log_level_1
1832 ,p_module => G_BLOCK||'.'||l_api_name
1833 ,p_msg_text => 'Step 5.7: CALENDAR_DISPLAY_CODE Validation'
1834 );
1835
1836 begin
1837 select calendar_id
1838 into ld_calendar_id
1839 from fem_calendars_b
1840 where calendar_display_code = ld_calendar_dc;
1841 exception
1842 when no_data_found then
1843 FEM_ENGINES_PKG.user_message (
1844 p_app_name => G_FEM
1845 ,p_msg_name => G_HIER_LDR_CALENDAR_ERR
1846 ,p_token1 => 'CALENDAR_DISPLAY_CODE'
1847 ,p_value1 => ld_calendar_dc
1848 );
1849 l_hierarchy_error_flag := true;
1850 end;
1851
1852 end if;
1853
1854 ------------------------------------------------------------------------------
1855 -- STEP 5.8: Value Set validations for new hierarchies.
1856 ------------------------------------------------------------------------------
1857 if (l_value_set_required_flag = 'Y') then
1858
1859 FEM_ENGINES_PKG.tech_message (
1860 p_severity => g_log_level_1
1861 ,p_module => G_BLOCK||'.'||l_api_name
1862 ,p_msg_text => 'Step 5.8: Value Set Validations'
1863 );
1864
1865 ------------------------------------------------------------------------------
1866 -- STEP 5.8.1: Verify that MULTI_VALUE_SET_FLAG on FEM_HIERARCHIES_T
1867 -- is valid. If flag ='N', check that only one value set row is listed in
1868 -- FEM_HIER_VALUE_SETS_T for that hierarchy.
1869 ------------------------------------------------------------------------------
1870 FEM_ENGINES_PKG.tech_message (
1871 p_severity => g_log_level_1
1872 ,p_module => G_BLOCK||'.'||l_api_name
1873 ,p_msg_text => 'Step 5.8.1: MULTI_VALUE_SET_FLAG Validation'
1874 );
1875
1876 execute immediate
1877 ' select count(*)'||
1878 ' from fem_hier_value_sets_t'||
1879 ' where hierarchy_object_name = :b_hierarchy_object_name'||
1880 l_status_clause||
1881 ' and language = userenv(''LANG'')'
1882 into l_vs_count
1883 using ld_hierarchy_object_name;
1884
1885 if (l_vs_count = 0) then
1886
1887 FEM_ENGINES_PKG.user_message (
1888 p_app_name => G_FEM
1889 ,p_msg_name => G_HIER_LDR_NO_HIER_VS_ERR
1890 ,p_token1 => 'HIERARCHY_OBJECT_NAME'
1891 ,p_value1 => ld_hierarchy_object_name
1892 );
1893 l_hierarchy_error_flag := true;
1894
1895 elsif ((l_vs_count > 1) and (ld_multi_value_set_flag = 'N')) then
1896
1897 l_hierarchy_error_flag := true;
1898 ld_status := 'MULTIPLE_VALUE_SETS';
1899
1900 set_hier_table_err_msg (
1901 p_hier_table_name => 'FEM_HIER_VALUE_SETS_T'
1902 ,p_status => ld_status
1903 );
1904
1905 execute immediate
1906 ' update fem_hier_value_sets_t'||
1907 ' set status = :b_status'||
1908 ' where hierarchy_object_name = :b_hierarchy_object_name'||
1909 l_status_clause||
1910 ' and language = userenv(''LANG'')'
1911 using ld_status
1912 ,ld_hierarchy_object_name;
1913
1914 commit;
1915
1916 end if;
1917
1918 exit to_next_hier_for_loading when l_hierarchy_error_flag;
1919
1920 end if; -- End of value set validations for new hierarchies
1921
1922 ------------------------------------------------------------------------------
1923 -- STEP 5.9: Dimension Group validations.
1924 ------------------------------------------------------------------------------
1925 if (ld_group_seq_enforced_code <> 'NO_GROUPS') then
1926
1927 FEM_ENGINES_PKG.tech_message (
1928 p_severity => g_log_level_1
1929 ,p_module => G_BLOCK||'.'||l_api_name
1930 ,p_msg_text => 'Step 5.9: Dimension Group Validations'
1931 );
1932
1933 ------------------------------------------------------------------------------
1934 -- STEP 5.9.1: Verify that FEM_HIER_DIM_GRPS_T is not empty
1935 ------------------------------------------------------------------------------
1936 FEM_ENGINES_PKG.tech_message (
1937 p_severity => g_log_level_1
1938 ,p_module => G_BLOCK||'.'||l_api_name
1939 ,p_msg_text => 'Step 5.9.1: FEM_HIER_DIM_GRPS_T Not Empty Validation'
1940 );
1941
1942 -- Validation added with bug 4449780
1943 execute immediate
1944 ' select count(*)'||
1945 ' from fem_hier_dim_grps_t'||
1946 ' where hierarchy_object_name = :b_hierarchy_object_name'||
1947 l_status_clause||
1948 ' and language = userenv(''LANG'')'
1949 into l_count
1950 using p_hierarchy_object_name;
1951
1952 if (l_count = 0) then
1953
1954 FEM_ENGINES_PKG.user_message (
1955 p_app_name => G_FEM
1956 ,p_msg_name => G_HIER_LDR_DIM_GRPS_REQ_ERR
1957 );
1958 l_hierarchy_error_flag := true;
1959
1960 end if;
1961
1962 exit to_next_hier_for_loading when l_hierarchy_error_flag;
1963
1964 ------------------------------------------------------------------------------
1965 -- STEP 5.9.2: Verify that DIMENSION_GROUP_DISPLAY_CODE on FEM_HIER_DIM_GRPS_T
1966 -- is a valid dimension group for the dimension.
1967 ------------------------------------------------------------------------------
1968 FEM_ENGINES_PKG.tech_message (
1969 p_severity => g_log_level_1
1970 ,p_module => G_BLOCK||'.'||l_api_name
1971 ,p_msg_text => 'Step 5.9.2: DIMENSION_GROUP_DISPLAY_CODE in FEM_HIER_DIM_GRPS_T Validation'
1972 );
1973
1974 open cv_bad_dim_groups
1975 for l_bad_dim_groups_stmt
1976 using ld_hierarchy_object_name
1977 ,l_dimension_id;
1978
1979 l_bad_dim_groups_is_open := true;
1980
1981 loop
1982
1983 fetch cv_bad_dim_groups
1984 bulk collect into
1985 tg_rowid
1986 ,tg_status
1987 limit gv_fetch_limit;
1988
1989 l_bad_dim_groups_last_row := tg_rowid.LAST;
1990 if (l_bad_dim_groups_last_row is null) then
1991 exit;
1992 end if;
1993
1994 l_hierarchy_error_flag := true;
1995 ld_status := tg_status(1);
1996
1997 set_hier_table_err_msg (
1998 p_hier_table_name => 'FEM_HIER_DIM_GRPS_T'
1999 ,p_status => ld_status
2000 );
2001
2002 forall j in 1..l_bad_dim_groups_last_row
2003 execute immediate
2004 ' update fem_hier_dim_grps_t'||
2005 ' set status = :b_status'||
2006 ' where rowid = :b_rowid'||
2007 l_status_clause||
2008 ' and language = userenv(''LANG'')'
2009 using tg_status(j)
2010 ,tg_rowid(j);
2011
2012 commit;
2013
2014 tg_rowid.DELETE;
2015 tg_status.DELETE;
2016
2017 end loop;
2018
2019 close cv_bad_dim_groups;
2020 l_bad_dim_groups_is_open := false;
2021
2022 exit to_next_hier_for_loading when l_hierarchy_error_flag;
2023
2024 end if; -- End of dimension group validations
2025
2026 end if; -- End of new hierarchy validations
2027 --END:eng_master_prep
2028
2029 ------------------------------------------------------------------------------
2030 -- STEP 5.10: Value Set validations for new and updated hierarchies.
2031 ------------------------------------------------------------------------------
2032 if (l_value_set_required_flag = 'Y') then
2033
2034 ------------------------------------------------------------------------------
2035 -- STEP 5.10.1: Verify that VALUE_SET_DISPLAY_CODE on FEM_HIER_VALUE_SETS_T
2036 -- is a valid value set for the dimension. This is needed for all new
2037 -- hiearchies, and for updates on multi value set hierarchies where we allow
2038 -- more value sets to be added (Bug 4661474).
2039 ------------------------------------------------------------------------------
2040 if ( (ld_load_type = g_new_hier) or (ld_multi_value_set_flag = 'Y') ) then
2041
2042 FEM_ENGINES_PKG.tech_message (
2043 p_severity => g_log_level_1
2044 ,p_module => G_BLOCK||'.'||l_api_name
2045 ,p_msg_text => 'Step 5.10.1: VALUE_SET_DISPLAY_CODE in FEM_HIER_VALUE_SETS_T Validation'
2046 );
2047
2048 open cv_bad_value_sets
2049 for l_bad_value_sets_stmt
2050 using ld_hierarchy_object_name
2051 ,l_dimension_id;
2052
2053 l_bad_value_sets_is_open := true;
2054
2055 loop
2056
2057 fetch cv_bad_value_sets
2058 bulk collect into
2059 tv_rowid
2060 ,tv_status
2061 limit gv_fetch_limit;
2062
2063 l_bad_value_sets_last_row := tv_rowid.LAST;
2064 if (l_bad_value_sets_last_row is null) then
2065 exit;
2066 end if;
2067
2068 l_hierarchy_error_flag := true;
2069 ld_status := tv_status(1);
2070
2071 set_hier_table_err_msg (
2072 p_hier_table_name => 'FEM_HIER_VALUE_SETS_T'
2073 ,p_status => ld_status
2074 );
2075
2076 forall j in 1..l_bad_value_sets_last_row
2077 execute immediate
2078 ' update fem_hier_value_sets_t'||
2079 ' set status = :b_status'||
2080 ' where rowid = :b_rowid'||
2081 l_status_clause||
2082 ' and language = userenv(''LANG'')'
2083 using tv_status(j)
2084 ,tv_rowid(j);
2085
2086 commit;
2087
2088 tv_rowid.DELETE;
2089 tv_status.DELETE;
2090
2091 end loop;
2092
2093 close cv_bad_value_sets;
2094 l_bad_value_sets_is_open := false;
2095
2096 exit to_next_hier_for_loading when l_hierarchy_error_flag;
2097
2098 end if;
2099
2100 end if; -- End of value set validations
2101
2102
2103 -- Must raise hierarchy exception if any hierarchy errors were found.
2104 -- No further hierarchy validations are performed.
2105 if (l_hierarchy_error_flag) then
2106 raise e_hierarchy_error;
2107 end if;
2108
2109
2110 --BEGIN:multi_thread_validation
2111 ------------------------------------------------------------------------------
2112 -- STEP 6: Parent-Child relationship validations. All hierarchy load types
2113 -- must pass this validation (new hierarchy, new hierarchy definition, and
2114 -- update hierarchy definition).
2115 ------------------------------------------------------------------------------
2116 FEM_ENGINES_PKG.tech_message (
2117 p_severity => g_log_level_1
2118 ,p_module => G_BLOCK||'.'||l_api_name
2119 ,p_msg_text => 'Step 6: Parent-Child Validations'
2120 );
2121
2122 ------------------------------------------------------------------------------
2123 -- STEP 6.1: Only for the CAL_PERIOD dimension, verify that the
2124 -- CALENDAR_DISPLAY_CODE is valid for the hierarchy in FEM_CAL_PERIODS_HIER_T
2125 ------------------------------------------------------------------------------
2126 if (p_dimension_varchar_label = 'CAL_PERIOD') then
2127
2128 FEM_ENGINES_PKG.tech_message (
2129 p_severity => g_log_level_1
2130 ,p_module => G_BLOCK||'.'||l_api_name
2131 ,p_msg_text => 'Step 6.1: CALENDAR_DISPLAY_CODE in FEM_CAL_PERIODS_HIER_T Validation'
2132 );
2133
2134 open cv_bad_hier_calendars
2135 for l_bad_hier_calendars_stmt
2136 using ld_hierarchy_object_name
2137 ,ld_hier_obj_def_display_name
2138 ,ld_calendar_dc;
2139
2140 l_bad_hier_calendars_is_open := true;
2141
2142 loop
2143
2144 fetch cv_bad_hier_calendars
2145 bulk collect into
2146 th_rowid
2147 ,th_status
2148 limit gv_fetch_limit;
2149
2150 l_bad_hier_calendars_last_row := th_rowid.LAST;
2151 if (l_bad_hier_calendars_last_row is null) then
2152 exit;
2153 end if;
2154
2155 l_hierarchy_error_flag := true;
2156 ld_status := th_status(1);
2157
2158 set_hier_table_err_msg (
2159 p_hier_table_name => l_source_hier_table
2160 ,p_status => ld_status
2161 );
2162
2163 forall j in 1..l_bad_hier_calendars_last_row
2164 execute immediate
2165 bld_update_status_stmt (
2166 p_dimension_varchar_label => p_dimension_varchar_label
2167 ,p_execution_mode => p_execution_mode
2168 ,p_source_hier_table => l_source_hier_table
2169 ,p_rowid_flag => 'Y'
2170 )
2171 using th_status(j)
2172 ,th_rowid(j);
2173
2174 commit;
2175
2176 th_rowid.DELETE;
2177 th_status.DELETE;
2178
2179 end loop;
2180
2181 close cv_bad_hier_calendars;
2182 l_bad_hier_calendars_is_open := false;
2183
2184 exit to_next_hier_for_loading when l_hierarchy_error_flag;
2185
2186 end if;
2187
2188 ------------------------------------------------------------------------------
2189 -- STEP 6.2: Verify that the PARENT_VALUE_SET_DISPLAY_CODE and
2190 -- CHILD_VALUE_SET_DISPLAY_CODE are valid for the hierarchy in
2191 -- FEM_HIER_VALUE_SETS_T (new) or FEM_HIER_VALUE_SETS (existing)
2192 ------------------------------------------------------------------------------
2193 if (l_value_set_required_flag = 'Y') then
2194
2195 FEM_ENGINES_PKG.tech_message (
2196 p_severity => g_log_level_1
2197 ,p_module => G_BLOCK||'.'||l_api_name
2198 ,p_msg_text => 'Step 6.2: PARENT_VALUE_SET_DISPLAY_CODE and CHILD_VALUE_SET_DISPLAY_CODE Validations'
2199 );
2200
2201 if (ld_load_type = g_new_hier) then
2202 open cv_bad_hier_value_sets
2203 for l_bad_hier_value_sets_t_stmt
2204 using ld_hierarchy_object_name
2205 ,ld_hier_obj_def_display_name;
2206 elsif (ld_multi_value_set_flag = 'Y') then
2207 open cv_bad_hier_value_sets
2208 for l_bad_hier_multi_vs_stmt
2209 using ld_hierarchy_object_name
2210 ,ld_hier_obj_def_display_name
2211 ,ld_hierarchy_object_id
2212 ,ld_hierarchy_object_id;
2213 else
2214 open cv_bad_hier_value_sets
2215 for l_bad_hier_value_sets_stmt
2216 using ld_hierarchy_object_name
2217 ,ld_hier_obj_def_display_name
2218 ,ld_hierarchy_object_id
2219 ,ld_hierarchy_object_id;
2220 end if;
2221
2222 l_bad_hier_value_sets_is_open := true;
2223
2224 loop
2225
2226 fetch cv_bad_hier_value_sets
2227 bulk collect into
2228 th_rowid
2229 ,th_status
2230 limit gv_fetch_limit;
2231
2232 l_bad_hier_value_sets_last_row := th_rowid.LAST;
2233 if (l_bad_hier_value_sets_last_row is null) then
2234 exit;
2235 end if;
2236
2237 l_hierarchy_error_flag := true;
2238 ld_status := th_status(1);
2239
2240 set_hier_table_err_msg (
2241 p_hier_table_name => l_source_hier_table
2242 ,p_status => ld_status
2243 );
2244
2245 forall j in 1..l_bad_hier_value_sets_last_row
2246 execute immediate
2247 bld_update_status_stmt (
2248 p_dimension_varchar_label => p_dimension_varchar_label
2249 ,p_execution_mode => p_execution_mode
2250 ,p_source_hier_table => l_source_hier_table
2251 ,p_rowid_flag => 'Y'
2252 )
2253 using th_status(j)
2254 ,th_rowid(j);
2255
2256 commit;
2257
2258 th_rowid.DELETE;
2259 th_status.DELETE;
2260
2261 end loop;
2262
2263 close cv_bad_hier_value_sets;
2264 l_bad_hier_value_sets_is_open := false;
2265
2266 exit to_next_hier_for_loading when l_hierarchy_error_flag;
2267
2268 end if;
2269
2270 ------------------------------------------------------------------------------
2271 -- STEP 6.3: Verify that all PARENT_DISPLAY_CODE and CHILD_DISPLAY_CODE values
2272 -- are valid dimension members in the appropriate FEM_xName_B table.
2273 ------------------------------------------------------------------------------
2274 FEM_ENGINES_PKG.tech_message (
2275 p_severity => g_log_level_1
2276 ,p_module => G_BLOCK||'.'||l_api_name
2277 ,p_msg_text => 'Step 6.3: PARENT_ID and CHILD_ID Validations'
2278 );
2279
2280 if (p_dimension_varchar_label = 'CAL_PERIOD') then
2281 open cv_bad_hier_members
2282 for l_bad_hier_members_stmt
2283 using ld_hierarchy_object_name
2284 ,ld_hier_obj_def_display_name
2285 ,ld_calendar_dc
2286 ,l_dimension_id
2287 ,ld_calendar_id
2288 ,l_dimension_id
2289 ,ld_calendar_id;
2290 else
2291 if (l_value_set_required_flag = 'Y') then
2292 open cv_bad_hier_members
2293 for l_bad_hier_members_stmt
2294 using ld_hierarchy_object_name
2295 ,ld_hier_obj_def_display_name
2296 ,l_dimension_id
2297 ,l_dimension_id;
2298 else
2299 open cv_bad_hier_members
2300 for l_bad_hier_members_stmt
2301 using ld_hierarchy_object_name
2302 ,ld_hier_obj_def_display_name;
2303 end if;
2304 end if;
2305
2306 l_bad_hier_members_is_open := true;
2307
2308 loop
2309
2310 fetch cv_bad_hier_members
2311 bulk collect into
2312 th_rowid
2313 ,th_status
2314 limit gv_fetch_limit;
2315
2316 l_bad_hier_members_last_row := th_rowid.LAST;
2317 if (l_bad_hier_members_last_row is null) then
2318 exit;
2319 end if;
2320
2321 l_hierarchy_error_flag := true;
2322 ld_status := th_status(1);
2323
2324 set_hier_table_err_msg (
2325 p_hier_table_name => l_source_hier_table
2326 ,p_status => ld_status
2327 );
2328
2329 forall j in 1..l_bad_hier_members_last_row
2330 execute immediate
2331 bld_update_status_stmt (
2332 p_dimension_varchar_label => p_dimension_varchar_label
2333 ,p_execution_mode => p_execution_mode
2334 ,p_source_hier_table => l_source_hier_table
2335 ,p_rowid_flag => 'Y'
2336 )
2337 using th_status(j)
2338 ,th_rowid(j);
2339
2340 commit;
2341
2342 th_rowid.DELETE;
2343 th_status.DELETE;
2344
2345 end loop;
2346
2347 close cv_bad_hier_members;
2348 l_bad_hier_members_is_open := false;
2349
2350 exit to_next_hier_for_loading when l_hierarchy_error_flag;
2351
2352 ------------------------------------------------------------------------------
2353 -- STEP 6.4: Verify for HIERARCHY_TYPE_CODE = (OPEN, RECONCILIATION) that
2354 -- each child only has a single parent in the FEM_xName_HIER_T table.
2355 ------------------------------------------------------------------------------
2356 if (ld_hierarchy_type_code in ('OPEN','RECONCILIATION')) then
2357
2358 FEM_ENGINES_PKG.tech_message (
2359 p_severity => g_log_level_1
2360 ,p_module => G_BLOCK||'.'||l_api_name
2361 ,p_msg_text => 'Step 6.4: Single Parent Validation'
2362 );
2363
2364 if (p_dimension_varchar_label = 'CAL_PERIOD') then
2365 open cv_bad_hier_dups
2366 for l_bad_hier_dups_stmt
2367 using ld_hierarchy_object_name
2368 ,ld_hier_obj_def_display_name
2369 ,ld_calendar_dc;
2370 else
2371 open cv_bad_hier_dups
2372 for l_bad_hier_dups_stmt
2373 using ld_hierarchy_object_name
2374 ,ld_hier_obj_def_display_name;
2375 end if;
2376
2377 l_bad_hier_dups_is_open := true;
2378
2379 loop
2380
2381 fetch cv_bad_hier_dups
2382 bulk collect into
2383 th_child_dc
2384 ,th_child_value_set_dc
2385 ,th_child_dim_grp_dc
2386 ,th_child_cal_period_end_date
2387 ,th_child_cal_period_number
2388 ,th_status
2389 limit gv_fetch_limit;
2390
2391 l_bad_hier_dups_last_row := th_child_dc.LAST;
2392 if (l_bad_hier_dups_last_row is null) then
2393 exit;
2394 end if;
2395
2396 l_hierarchy_error_flag := true;
2397 ld_status := th_status(1);
2398
2399 set_hier_table_err_msg (
2400 p_hier_table_name => l_source_hier_table
2401 ,p_status => ld_status
2402 );
2403
2404 if (p_dimension_varchar_label = 'CAL_PERIOD') then
2405 forall j in 1..l_bad_hier_dups_last_row
2406 execute immediate
2407 bld_update_status_stmt (
2408 p_dimension_varchar_label => p_dimension_varchar_label
2409 ,p_execution_mode => p_execution_mode
2410 ,p_source_hier_table => l_source_hier_table
2411 ,p_hier_object_name_flag => 'Y'
2412 ,p_hier_obj_def_name_flag => 'Y'
2413 ,p_child_flag => 'Y'
2414 )
2415 using th_status(j)
2416 ,ld_hierarchy_object_name
2417 ,ld_hier_obj_def_display_name
2418 ,ld_calendar_dc
2419 ,th_child_dim_grp_dc(j)
2420 ,th_child_cal_period_end_date(j)
2421 ,th_child_cal_period_number(j);
2422 else
2423 if (l_value_set_required_flag = 'Y') then
2424 forall j in 1..l_bad_hier_dups_last_row
2425 execute immediate
2426 bld_update_status_stmt (
2427 p_dimension_varchar_label => p_dimension_varchar_label
2428 ,p_execution_mode => p_execution_mode
2429 ,p_value_set_required_flag => l_value_set_required_flag
2430 ,p_source_hier_table => l_source_hier_table
2431 ,p_hier_object_name_flag => 'Y'
2432 ,p_hier_obj_def_name_flag => 'Y'
2433 ,p_child_flag => 'Y'
2434 )
2435 using th_status(j)
2436 ,ld_hierarchy_object_name
2437 ,ld_hier_obj_def_display_name
2438 ,th_child_dc(j)
2439 ,th_child_value_set_dc(j);
2440 else
2441 forall j in 1..l_bad_hier_dups_last_row
2442 execute immediate
2443 bld_update_status_stmt (
2444 p_dimension_varchar_label => p_dimension_varchar_label
2445 ,p_execution_mode => p_execution_mode
2446 ,p_value_set_required_flag => l_value_set_required_flag
2447 ,p_source_hier_table => l_source_hier_table
2448 ,p_hier_object_name_flag => 'Y'
2449 ,p_hier_obj_def_name_flag => 'Y'
2450 ,p_child_flag => 'Y'
2451 )
2452 using th_status(j)
2453 ,ld_hierarchy_object_name
2454 ,ld_hier_obj_def_display_name
2455 ,th_child_dc(j);
2456 end if;
2457 end if;
2458
2459 commit;
2460
2461 th_child_dc.DELETE;
2462 th_child_value_set_dc.DELETE;
2463 th_child_dim_grp_dc.DELETE;
2464 th_child_cal_period_end_date.DELETE;
2465 th_child_cal_period_number.DELETE;
2466 th_status.DELETE;
2467
2468 end loop;
2469
2470 close cv_bad_hier_dups;
2471 l_bad_hier_dups_is_open := false;
2472
2473 exit to_next_hier_for_loading when l_hierarchy_error_flag;
2474
2475 end if;
2476 --END:multi_thread_validation
2477
2478
2479 --BEGIN:single_thread_validation
2480 ------------------------------------------------------------------------------
2481 -- STEP 6.5: Verify for MULTI_TOP_FLAG = N that there is a single top node
2482 -- for the hierarchy in FEM_xName_HIER_T.
2483 ------------------------------------------------------------------------------
2484 FEM_ENGINES_PKG.tech_message (
2485 p_severity => g_log_level_1
2486 ,p_module => G_BLOCK||'.'||l_api_name
2487 ,p_msg_text => 'Step 6.5: Missing and Single Root Node Validations'
2488 );
2489
2490 if (p_dimension_varchar_label = 'CAL_PERIOD') then
2491 execute immediate l_root_node_count_stmt
2492 into l_num_roots
2493 using ld_hierarchy_object_name
2494 ,ld_hier_obj_def_display_name
2495 ,ld_calendar_dc;
2496 else
2497 execute immediate l_root_node_count_stmt
2498 into l_num_roots
2499 using ld_hierarchy_object_name
2500 ,ld_hier_obj_def_display_name;
2501 end if;
2502
2503 if (l_num_roots = 0) then
2504
2505 FEM_ENGINES_PKG.user_message (
2506 p_app_name => G_FEM
2507 ,p_msg_name => G_HIER_LDR_MISSING_ROOT_ERR
2508 );
2509 l_hierarchy_error_flag := true;
2510
2511 elsif ((l_num_roots > 1) and (ld_multi_top_flag = 'N')) then
2512
2513 if (p_dimension_varchar_label = 'CAL_PERIOD') then
2514 open cv_get_hier_roots
2515 for l_get_hier_roots_stmt
2516 using ld_hierarchy_object_name
2517 ,ld_hier_obj_def_display_name
2518 ,ld_calendar_dc
2519 ,l_dimension_id
2520 ,ld_calendar_id;
2521 else
2522 if (l_value_set_required_flag = 'Y') then
2523 open cv_get_hier_roots
2524 for l_get_hier_roots_stmt
2525 using ld_hierarchy_object_name
2526 ,ld_hier_obj_def_display_name
2527 ,l_dimension_id;
2528 else
2529 open cv_get_hier_roots
2530 for l_get_hier_roots_stmt
2531 using ld_hierarchy_object_name
2532 ,ld_hier_obj_def_display_name;
2533 end if;
2534 end if;
2535
2536 l_get_hier_roots_is_open := true;
2537
2538 loop
2539
2540 fetch cv_get_hier_roots
2541 bulk collect into
2542 th_rowid
2543 ,th_child_id
2544 ,th_child_value_set_id
2545 ,th_child_dimension_grp_id
2546 ,th_display_order_num
2547 limit gv_fetch_limit;
2548
2549 l_get_hier_roots_last_row := th_rowid.LAST;
2550 if (l_get_hier_roots_last_row is null) then
2551 exit;
2552 end if;
2553
2554 l_hierarchy_error_flag := true;
2555 ld_status := 'MULTIPLE_TOP';
2556
2557 set_hier_table_err_msg (
2558 p_hier_table_name => l_source_hier_table
2559 ,p_status => ld_status
2560 );
2561
2562 forall j in 1..l_get_hier_roots_last_row
2563 execute immediate
2564 bld_update_status_stmt (
2565 p_dimension_varchar_label => p_dimension_varchar_label
2566 ,p_execution_mode => p_execution_mode
2567 ,p_source_hier_table => l_source_hier_table
2568 ,p_rowid_flag => 'Y'
2569 )
2570 using ld_status
2571 ,th_rowid(j);
2572
2573 commit;
2574
2575 th_rowid.DELETE;
2576 th_child_id.DELETE;
2577 th_child_value_set_id.DELETE;
2578 th_child_dimension_grp_id.DELETE;
2579 th_display_order_num.DELETE;
2580
2581 end loop;
2582
2583 close cv_get_hier_roots;
2584 l_get_hier_roots_is_open := false;
2585
2586 end if;
2587
2588 exit to_next_hier_for_loading when l_hierarchy_error_flag;
2589 --END:single_thread_validation
2590
2591 --BEGIN:build_root_node_slices
2592 ------------------------------------------------------------------------------
2593 -- STEP 6.6: Verify that root nodes do not appear as children of other
2594 -- nodes for the hierarchy in FEM_xName_HIER_T.
2595 ------------------------------------------------------------------------------
2596 FEM_ENGINES_PKG.tech_message (
2597 p_severity => g_log_level_1
2598 ,p_module => G_BLOCK||'.'||l_api_name
2599 ,p_msg_text => 'Step 6.6: Invalid Root Node Validation'
2600 );
2601
2602 if (p_dimension_varchar_label = 'CAL_PERIOD') then
2603 open cv_bad_hier_roots
2604 for l_bad_hier_roots_stmt
2605 using ld_hierarchy_object_name
2606 ,ld_hier_obj_def_display_name
2607 ,ld_calendar_dc
2608 ,l_dimension_id
2609 ,ld_calendar_id;
2610 else
2611 if (l_value_set_required_flag = 'Y') then
2612 open cv_bad_hier_roots
2613 for l_bad_hier_roots_stmt
2614 using ld_hierarchy_object_name
2615 ,ld_hier_obj_def_display_name
2616 ,l_dimension_id;
2617 else
2618 open cv_bad_hier_roots
2619 for l_bad_hier_roots_stmt
2620 using ld_hierarchy_object_name
2621 ,ld_hier_obj_def_display_name;
2622 end if;
2623 end if;
2624
2625 l_bad_hier_roots_is_open := true;
2626
2627 loop
2628
2629 fetch cv_bad_hier_roots
2630 bulk collect into
2631 th_rowid
2632 ,th_status
2633 limit gv_fetch_limit;
2634
2635 l_bad_hier_roots_last_row := th_rowid.LAST;
2636 if (l_bad_hier_roots_last_row is null) then
2637 exit;
2638 end if;
2639
2640 l_hierarchy_error_flag := true;
2641 ld_status := th_status(1);
2642
2643 set_hier_table_err_msg (
2644 p_hier_table_name => l_source_hier_table
2645 ,p_status => ld_status
2646 );
2647
2648 forall j in 1..l_bad_hier_roots_last_row
2649 execute immediate
2650 bld_update_status_stmt (
2651 p_dimension_varchar_label => p_dimension_varchar_label
2652 ,p_execution_mode => p_execution_mode
2653 ,p_source_hier_table => l_source_hier_table
2654 ,p_rowid_flag => 'Y'
2655 )
2656 using th_status(j)
2657 ,th_rowid(j);
2658
2659 commit;
2660
2661 th_rowid.DELETE;
2662 th_status.DELETE;
2663
2664 end loop;
2665
2666 close cv_bad_hier_roots;
2667 l_bad_hier_roots_is_open := false;
2668
2669 exit to_next_hier_for_loading when l_hierarchy_error_flag;
2670
2671 ------------------------------------------------------------------------------
2672 -- STEP 7: Load hierarchy records from FEM_xNAME_HIER_T into
2673 -- FEM_HIERVAL_VSR_T/_CALP_T by flattening hierarchy.
2674 ------------------------------------------------------------------------------
2675
2676 FEM_ENGINES_PKG.tech_message (
2677 p_severity => g_log_level_1
2678 ,p_module => G_BLOCK||'.'||l_api_name
2679 ,p_msg_text => 'Step 7: Inserting Flattened Hierarchy Records in '||l_target_hierval_table
2680 );
2681
2682 ------------------------------------------------------------------------------
2683 -- STEP 7.1: Insert root node records from FEM_xNAME_HIER_T into
2684 -- FEM_HIERVAL_VSR_T/_CALP_T.
2685 ------------------------------------------------------------------------------
2686
2687 FEM_ENGINES_PKG.tech_message (
2688 p_severity => g_log_level_1
2689 ,p_module => G_BLOCK||'.'||l_api_name
2690 ,p_msg_text => 'Step 7.1: Inserting Root Nodes Records in '||l_target_hierval_table
2691 );
2692
2693 if (p_dimension_varchar_label = 'CAL_PERIOD') then
2694 open cv_get_hier_roots
2695 for l_get_hier_roots_stmt
2696 using ld_hierarchy_object_name
2697 ,ld_hier_obj_def_display_name
2698 ,ld_calendar_dc
2699 ,l_dimension_id
2700 ,ld_calendar_id;
2701 else
2702 if (l_value_set_required_flag = 'Y') then
2703 open cv_get_hier_roots
2704 for l_get_hier_roots_stmt
2705 using ld_hierarchy_object_name
2706 ,ld_hier_obj_def_display_name
2707 ,l_dimension_id;
2708 else
2709 open cv_get_hier_roots
2710 for l_get_hier_roots_stmt
2711 using ld_hierarchy_object_name
2712 ,ld_hier_obj_def_display_name;
2713 end if;
2714 end if;
2715
2716 l_get_hier_roots_is_open := true;
2717
2718 loop
2719
2720 fetch cv_get_hier_roots
2721 bulk collect into
2722 th_rowid
2723 ,th_child_id
2724 ,th_child_value_set_id
2725 ,th_child_dimension_grp_id
2726 ,th_display_order_num
2727 limit gv_fetch_limit;
2728
2729 l_get_hier_roots_last_row := th_rowid.LAST;
2730 if (l_get_hier_roots_last_row is null) then
2731 exit;
2732 end if;
2733
2734 if (p_dimension_varchar_label = 'CAL_PERIOD') then
2735 forall j in 1..l_get_hier_roots_last_row
2736 execute immediate l_insert_hierval_rels_stmt
2737 using l_request_id
2738 ,th_rowid(j)
2739 ,1
2740 ,th_child_id(j)
2741 ,th_child_dimension_grp_id(j)
2742 ,1
2743 ,th_child_id(j)
2744 ,th_child_dimension_grp_id(j)
2745 ,th_display_order_num(j)
2746 ,to_number(null);
2747 else
2748 forall j in 1..l_get_hier_roots_last_row
2749 execute immediate l_insert_hierval_rels_stmt
2750 using l_request_id
2751 ,th_rowid(j)
2752 ,1
2753 ,th_child_id(j)
2754 ,th_child_value_set_id(j)
2755 ,th_child_dimension_grp_id(j)
2756 ,1
2757 ,th_child_id(j)
2758 ,th_child_value_set_id(j)
2759 ,th_child_dimension_grp_id(j)
2760 ,th_display_order_num(j)
2761 ,to_number(null);
2762 end if;
2763
2764 th_rowid.DELETE;
2765 th_child_id.DELETE;
2766 th_child_value_set_id.DELETE;
2767 th_child_dimension_grp_id.DELETE;
2768 th_display_order_num.DELETE;
2769
2770 end loop;
2771
2772 close cv_get_hier_roots;
2773 l_get_hier_roots_is_open := false;
2774
2775 commit;
2776 --END:build_root_node_slices
2777
2778
2779 --BEGIN:connect_by_processing
2780 ------------------------------------------------------------------------------
2781 -- STEP 7.2: Insert parent/child relationships from FEM_xName_HIER_T into
2782 -- FEM_HIERVAL_VSR_T/_CALP_T. Only the "connect by" records need to be
2783 -- created at this stage.
2784 ------------------------------------------------------------------------------
2785
2786 FEM_ENGINES_PKG.tech_message (
2787 p_severity => g_log_level_1
2788 ,p_module => G_BLOCK||'.'||l_api_name
2789 ,p_msg_text => 'Step 7.2: Inserting Relationship Records in '||l_target_hierval_table
2790 );
2791
2792 if (p_dimension_varchar_label = 'CAL_PERIOD') then
2793 open cv_get_hier_rels
2794 for l_get_hier_rels_stmt
2795 using ld_hierarchy_object_name
2796 ,ld_hier_obj_def_display_name
2797 ,ld_calendar_dc
2798 ,ld_hierarchy_object_name
2799 ,ld_hier_obj_def_display_name
2800 ,ld_calendar_dc
2801 ,l_dimension_id
2802 ,ld_calendar_id
2803 ,ld_calendar_id;
2804 else
2805 if (l_value_set_required_flag = 'Y') then
2806 open cv_get_hier_rels
2807 for l_get_hier_rels_stmt
2808 using ld_hierarchy_object_name
2809 ,ld_hier_obj_def_display_name
2810 ,ld_hierarchy_object_name
2811 ,ld_hier_obj_def_display_name
2812 ,l_dimension_id;
2813 else
2814 open cv_get_hier_rels
2815 for l_get_hier_rels_stmt
2816 using ld_hierarchy_object_name
2817 ,ld_hier_obj_def_display_name
2818 ,ld_hierarchy_object_name
2819 ,ld_hier_obj_def_display_name;
2820 end if;
2821 end if;
2822
2823 l_get_hier_rels_is_open := true;
2824
2825 loop
2826
2827 -- This fetch can throw an exception if there are circular references
2828 -- in the FEM_xName_HIER_T table. Catch this exception to display it
2829 -- to user with a user friendly message.
2830 begin
2831
2832 fetch cv_get_hier_rels
2833 bulk collect into
2834 th_rowid
2835 ,th_parent_depth_num
2836 ,th_parent_id
2837 ,th_parent_value_set_id
2838 ,th_parent_dimension_grp_id
2839 ,th_child_depth_num
2840 ,th_child_id
2841 ,th_child_value_set_id
2842 ,th_child_dimension_grp_id
2843 ,th_display_order_num
2844 ,th_wt_pct
2845 ,th_status
2846 limit gv_fetch_limit;
2847
2848 exception
2849
2850 when others then
2851
2852 -- If the error code corresponds to a circular reference in a
2853 -- connect-by query, then update hierarchy status to indicate
2854 -- error.
2855 l_sql_err_code := SQLCODE;
2856 if (l_sql_err_code = g_connect_by_loop) then
2857
2858 close cv_get_hier_rels;
2859 l_get_hier_rels_is_open := false;
2860
2861 l_hierarchy_error_flag := true;
2862 ld_status := 'CIRCULAR_HIERARCHY';
2863
2864 set_hier_table_err_msg (
2865 p_hier_table_name => l_source_hier_table
2866 ,p_status => ld_status
2867 );
2868
2869 execute immediate
2870 bld_update_status_stmt (
2871 p_dimension_varchar_label => p_dimension_varchar_label
2872 ,p_execution_mode => p_execution_mode
2873 ,p_source_hier_table => l_source_hier_table
2874 ,p_hier_object_name_flag => 'Y'
2875 ,p_hier_obj_def_name_flag => 'Y'
2876 )
2877 using ld_status
2878 ,ld_hierarchy_object_name
2879 ,ld_hier_obj_def_display_name;
2880
2881 commit;
2882
2883 -- Do not perform any further hierarchy validations. Exit out
2884 -- gracefully.
2885 exit to_next_hier_for_loading;
2886
2887 else
2888 raise;
2889 end if;
2890
2891 end;
2892
2893 l_get_hier_rels_last_row := th_rowid.LAST;
2894 if (l_get_hier_rels_last_row is null) then
2895 exit;
2896 end if;
2897
2898 if (p_dimension_varchar_label = 'CAL_PERIOD') then
2899 forall j in 1..l_get_hier_rels_last_row
2900 execute immediate l_insert_hierval_rels_stmt
2901 using l_request_id
2902 ,th_rowid(j)
2903 ,th_parent_depth_num(j)
2904 ,th_parent_id(j)
2905 ,th_parent_dimension_grp_id(j)
2906 ,th_child_depth_num(j)
2907 ,th_child_id(j)
2908 ,th_child_dimension_grp_id(j)
2909 ,th_display_order_num(j)
2910 ,th_wt_pct(j);
2911 else
2912 forall j in 1..l_get_hier_rels_last_row
2913 execute immediate l_insert_hierval_rels_stmt
2914 using l_request_id
2915 ,th_rowid(j)
2916 ,th_parent_depth_num(j)
2917 ,th_parent_id(j)
2918 ,th_parent_value_set_id(j)
2919 ,th_parent_dimension_grp_id(j)
2920 ,th_child_depth_num(j)
2921 ,th_child_id(j)
2922 ,th_child_value_set_id(j)
2923 ,th_child_dimension_grp_id(j)
2924 ,th_display_order_num(j)
2925 ,th_wt_pct(j);
2926 end if;
2927
2928 commit;
2929
2930 th_rowid.DELETE;
2931 th_parent_depth_num.DELETE;
2932 th_parent_id.DELETE;
2933 th_parent_value_set_id.DELETE;
2934 th_parent_dimension_grp_id.DELETE;
2935 th_child_depth_num.DELETE;
2936 th_child_id.DELETE;
2937 th_child_value_set_id.DELETE;
2938 th_child_dimension_grp_id.DELETE;
2939 th_display_order_num.DELETE;
2940 th_wt_pct.DELETE;
2941 th_status.DELETE;
2942
2943 end loop;
2944
2945 close cv_get_hier_rels;
2946 l_get_hier_rels_is_open := false;
2947 --END:connect_by_processing
2948
2949 --BEGIN:final_hierval
2950 ------------------------------------------------------------------------------
2951 -- STEP 8: Verify for HIERARCHY_TYPE_CODE = RECONCILIATION that
2952 -- all dimension members have the correct "Reconciliation Child"
2953 -- attribute assignment.
2954 ------------------------------------------------------------------------------
2955 if (ld_hierarchy_type_code = 'RECONCILIATION') then
2956
2957 ------------------------------------------------------------------------------
2958 -- STEP 8.1: Verify that all leaf nodes have RECON_LEAF_NODE_FLAG = Y
2959 ------------------------------------------------------------------------------
2960 FEM_ENGINES_PKG.tech_message (
2961 p_severity => g_log_level_1
2962 ,p_module => G_BLOCK||'.'||l_api_name
2963 ,p_msg_text => 'Step 8.1: Reconciliation Leaf Validation'
2964 );
2965
2966 begin
2967 select att.attribute_id
2968 ,ver.version_id
2969 into l_attribute_id
2970 ,l_attr_version_id
2971 from fem_dim_attributes_b att
2972 ,fem_dim_attr_versions_b ver
2973 where att.attribute_varchar_label = 'RECON_LEAF_NODE_FLAG'
2974 and att.dimension_id = l_dimension_id
2975 and ver.attribute_id = att.attribute_id
2976 and ver.default_version_flag = 'Y';
2977 exception
2978 when others then
2979 FEM_ENGINES_PKG.user_message (
2980 p_app_name => G_FEM
2981 ,p_msg_name => G_HIER_LDR_NO_LEAF_ATTR_ERR
2982 ,p_token1 => 'DIMENSION'
2983 ,p_value1 => p_dimension_varchar_label
2984 );
2985 l_hierarchy_error_flag := true;
2986 -- Do not perform any further hierarchy validations. Exit out
2987 -- gracefully.
2988 exit to_next_hier_for_loading;
2989 end;
2990
2991 open cv_bad_hier_rec_leafs
2992 for l_bad_hier_rec_leafs_stmt
2993 using l_request_id
2994 ,l_attribute_id
2995 ,l_attr_version_id;
2996
2997 l_bad_hier_rec_leafs_is_open := true;
2998
2999 loop
3000
3001 fetch cv_bad_hier_rec_leafs
3002 bulk collect into
3003 th_rowid
3004 ,th_status
3005 limit gv_fetch_limit;
3006
3007 l_bad_hier_rec_leafs_last_row := th_rowid.LAST;
3008 if (l_bad_hier_rec_leafs_last_row is null) then
3009 exit;
3010 end if;
3011
3012 l_hierarchy_error_flag := true;
3013 ld_status := th_status(1);
3014
3015 set_hier_table_err_msg (
3016 p_hier_table_name => l_source_hier_table
3017 ,p_status => ld_status
3018 );
3019
3020 forall j in 1..l_bad_hier_rec_leafs_last_row
3021 execute immediate
3022 bld_update_status_stmt (
3023 p_dimension_varchar_label => p_dimension_varchar_label
3024 ,p_execution_mode => p_execution_mode
3025 ,p_source_hier_table => l_source_hier_table
3026 ,p_rowid_flag => 'Y'
3027 )
3028 using th_status(j)
3029 ,th_rowid(j);
3030
3031 commit;
3032
3033 th_rowid.DELETE;
3034 th_status.DELETE;
3035
3036 end loop;
3037
3038 close cv_bad_hier_rec_leafs;
3039 l_bad_hier_rec_leafs_is_open := false;
3040
3041 exit to_next_hier_for_loading when l_hierarchy_error_flag;
3042
3043 ------------------------------------------------------------------------------
3044 -- STEP 8.2: Verify that all parent nodes have RECON_LEAF_NODE_FLAG = N
3045 ------------------------------------------------------------------------------
3046 FEM_ENGINES_PKG.tech_message (
3047 p_severity => g_log_level_1
3048 ,p_module => G_BLOCK||'.'||l_api_name
3049 ,p_msg_text => 'Step 8.2: Reconciliation Non-Leaf Validation'
3050 );
3051
3052 open cv_bad_hier_rec_nodes
3053 for l_bad_hier_rec_nodes_stmt
3054 using l_request_id
3055 ,l_attribute_id
3056 ,l_attr_version_id;
3057
3058 l_bad_hier_rec_nodes_is_open := true;
3059
3060 loop
3061
3062 fetch cv_bad_hier_rec_nodes
3063 bulk collect into
3064 th_rowid
3065 ,th_status
3066 limit gv_fetch_limit;
3067
3068 l_bad_hier_rec_nodes_last_row := th_rowid.LAST;
3069 if (l_bad_hier_rec_nodes_last_row is null) then
3070 exit;
3071 end if;
3072
3073 l_hierarchy_error_flag := true;
3074 ld_status := th_status(1);
3075
3076 set_hier_table_err_msg (
3077 p_hier_table_name => l_source_hier_table
3078 ,p_status => ld_status
3079 );
3080
3081 forall j in 1..l_bad_hier_rec_nodes_last_row
3082 execute immediate
3083 bld_update_status_stmt (
3084 p_dimension_varchar_label => p_dimension_varchar_label
3085 ,p_execution_mode => p_execution_mode
3086 ,p_source_hier_table => l_source_hier_table
3087 ,p_rowid_flag => 'Y'
3088 )
3089 using th_status(j)
3090 ,th_rowid(j);
3091
3092 commit;
3093
3094 th_rowid.DELETE;
3095 th_status.DELETE;
3096
3097 end loop;
3098
3099 close cv_bad_hier_rec_nodes;
3100 l_bad_hier_rec_nodes_is_open := false;
3101
3102 exit to_next_hier_for_loading when l_hierarchy_error_flag;
3103
3104 end if;
3105
3106
3107 ------------------------------------------------------------------------------
3108 -- STEP 9: Verify for GROUP_SEQUENCE_ENFORCED_CODE <> NO_GROUPS
3109 -- that all parent/child relationships obey the Group sequence rules.
3110 ------------------------------------------------------------------------------
3111 if (ld_group_seq_enforced_code <> 'NO_GROUPS') then
3112
3113 FEM_ENGINES_PKG.tech_message (
3114 p_severity => g_log_level_1
3115 ,p_module => G_BLOCK||'.'||l_api_name
3116 ,p_msg_text => 'Step 9: Group Sequencing Validation'
3117 );
3118
3119 ------------------------------------------------------------------------------
3120 -- STEP 9.1: Validating that all hierarchy Nodes belong to a hierarchy
3121 -- dimension group.
3122 ------------------------------------------------------------------------------
3123
3124 FEM_ENGINES_PKG.tech_message (
3125 p_severity => g_log_level_1
3126 ,p_module => G_BLOCK||'.'||l_api_name
3127 ,p_msg_text => 'Step 9.1: Validating that all Hierarchy Nodes belong to a Hierarchy Dimension Group'
3128 );
3129
3130 if (ld_load_type = g_new_hier) then
3131 open cv_bad_hier_dim_groups
3132 for l_bad_hier_dim_groups_t_stmt
3133 using l_request_id
3134 ,ld_hierarchy_object_name
3135 ,l_dimension_id;
3136 else
3137 open cv_bad_hier_dim_groups
3138 for l_bad_hier_dim_groups_stmt
3139 using l_request_id
3140 ,ld_hierarchy_object_id;
3141 end if;
3142
3143 l_bad_hier_dim_groups_is_open := true;
3144
3145 loop
3146
3147 fetch cv_bad_hier_dim_groups
3148 bulk collect into
3149 th_rowid
3150 ,th_status
3151 limit gv_fetch_limit;
3152
3153 l_bad_hier_dim_groups_last_row := th_rowid.LAST;
3154 if (l_bad_hier_dim_groups_last_row is null) then
3155 exit;
3156 end if;
3157
3158 l_hierarchy_error_flag := true;
3159 ld_status := th_status(1);
3160
3161 set_hier_table_err_msg (
3162 p_hier_table_name => l_source_hier_table
3163 ,p_status => ld_status
3164 );
3165
3166 forall j in 1..l_bad_hier_dim_groups_last_row
3167 execute immediate
3168 bld_update_status_stmt (
3169 p_dimension_varchar_label => p_dimension_varchar_label
3170 ,p_execution_mode => p_execution_mode
3171 ,p_source_hier_table => l_source_hier_table
3172 ,p_rowid_flag => 'Y'
3173 )
3174 using th_status(j)
3175 ,th_rowid(j);
3176
3177 commit;
3178
3179 th_rowid.DELETE;
3180 th_status.DELETE;
3181
3182 end loop;
3183
3184 close cv_bad_hier_dim_groups;
3185 l_bad_hier_dim_groups_is_open := false;
3186
3187 exit to_next_hier_for_loading when l_hierarchy_error_flag;
3188
3189 ------------------------------------------------------------------------------
3190 -- STEP 9.2: Validating that all hierarchy nodes follow the correct hierarchy
3191 -- group sequencing order
3192 ------------------------------------------------------------------------------
3193
3194 FEM_ENGINES_PKG.tech_message (
3195 p_severity => g_log_level_1
3196 ,p_module => G_BLOCK||'.'||l_api_name
3197 ,p_msg_text => 'Step 9.2: Validating that all Hierarchy Nodes follow the correct Hierarchy Group Sequencing Order'
3198 );
3199
3200 -- Bug Fix 3923880: Provided support for skip-level hierarchies
3201 -- after DHM added SEQUENCE_ENFORCED_SKIP_LEVEL. This bug fix is an
3202 -- enhancement to Bug Fix 3638231, where skip-level hierarchies
3203 -- were first implemented in the hierarchy loader.
3204
3205 if (ld_group_seq_enforced_code = 'SEQUENCE_ENFORCED_SKIP_LEVEL') then
3206
3207 ------------------------------------------------------------------------------
3208 -- STEP 9.2.1: Skip-Level Hierarchy Group Sequence Validation
3209 ------------------------------------------------------------------------------
3210
3211 FEM_ENGINES_PKG.tech_message (
3212 p_severity => g_log_level_1
3213 ,p_module => G_BLOCK||'.'||l_api_name
3214 ,p_msg_text => 'Step 9.2.1: Skip-Level Hierarchy Group Sequence validation'
3215 );
3216
3217 open cv_bad_hier_dim_grp_sq
3218 for l_bad_hier_dim_grp_skp_stmt
3219 using l_request_id;
3220
3221 l_bad_hier_dim_grp_sq_is_open := true;
3222
3223 loop
3224
3225 fetch cv_bad_hier_dim_grp_sq
3226 bulk collect into
3227 th_rowid
3228 ,th_status
3229 limit gv_fetch_limit;
3230
3231 l_bad_hier_dim_grp_sq_last_row := th_rowid.LAST;
3232 if (l_bad_hier_dim_grp_sq_last_row is null) then
3233 exit;
3234 end if;
3235
3236 l_hierarchy_error_flag := true;
3237 ld_status := th_status(1);
3238
3239 set_hier_table_err_msg (
3240 p_hier_table_name => l_source_hier_table
3241 ,p_status => ld_status
3242 );
3243
3244 forall j in 1..l_bad_hier_dim_grp_sq_last_row
3245 execute immediate
3246 bld_update_status_stmt (
3247 p_dimension_varchar_label => p_dimension_varchar_label
3248 ,p_execution_mode => p_execution_mode
3249 ,p_source_hier_table => l_source_hier_table
3250 ,p_rowid_flag => 'Y'
3251 )
3252 using th_status(j)
3253 ,th_rowid(j);
3254
3255 commit;
3256
3257 th_rowid.DELETE;
3258 th_status.DELETE;
3259
3260 end loop;
3261
3262 close cv_bad_hier_dim_grp_sq;
3263 l_bad_hier_dim_grp_sq_is_open := false;
3264
3265 elsif (ld_group_seq_enforced_code = 'SEQUENCE_ENFORCED') then
3266
3267 ------------------------------------------------------------------------------
3268 -- STEP 9.2.2: Standard Hierarchy Group Sequence Validation
3269 ------------------------------------------------------------------------------
3270
3271 FEM_ENGINES_PKG.tech_message (
3272 p_severity => g_log_level_1
3273 ,p_module => G_BLOCK||'.'||l_api_name
3274 ,p_msg_text => 'Step 9.2.2: Standard Hierarchy Group Sequence validation'
3275 );
3276
3277 if (ld_load_type = g_new_hier) then
3278 open cv_get_dim_groups
3279 for l_get_dim_groups_t_stmt
3280 using ld_hierarchy_object_name
3281 ,l_dimension_id;
3282 else
3283 open cv_get_dim_groups
3284 for l_get_dim_groups_stmt
3285 using ld_hierarchy_object_id;
3286 end if;
3287
3288 l_get_dim_groups_is_open := true;
3289
3290 loop
3291
3292 fetch cv_get_dim_groups
3293 bulk collect into
3294 tg_dimension_group_id
3295 ,tg_depth_num
3296 limit gv_fetch_limit;
3297
3298 l_get_dim_groups_last_row := tg_dimension_group_id.LAST;
3299 if (l_get_dim_groups_last_row is null) then
3300 exit;
3301 end if;
3302
3303 -- Loop through all dimension groups specified for this hierarchy
3304 for i in 1..l_get_dim_groups_last_row loop
3305
3306 open cv_bad_hier_dim_grp_sq
3307 for l_bad_hier_dim_grp_reg_stmt
3308 using l_request_id
3309 ,tg_depth_num(i)
3310 ,tg_dimension_group_id(i);
3311
3312 l_bad_hier_dim_grp_sq_is_open := true;
3313
3314 loop
3315
3316 fetch cv_bad_hier_dim_grp_sq
3317 bulk collect into
3318 th_rowid
3319 ,th_status
3320 limit gv_fetch_limit;
3321
3322 l_bad_hier_dim_grp_sq_last_row := th_rowid.LAST;
3323 if (l_bad_hier_dim_grp_sq_last_row is null) then
3324 exit;
3325 end if;
3326
3327 l_hierarchy_error_flag := true;
3328 ld_status := th_status(1);
3329
3330 set_hier_table_err_msg (
3331 p_hier_table_name => l_source_hier_table
3332 ,p_status => ld_status
3333 );
3334
3335 forall j in 1..l_bad_hier_dim_grp_sq_last_row
3336 execute immediate
3337 bld_update_status_stmt (
3338 p_dimension_varchar_label => p_dimension_varchar_label
3339 ,p_execution_mode => p_execution_mode
3340 ,p_source_hier_table => l_source_hier_table
3341 ,p_rowid_flag => 'Y'
3342 )
3343 using th_status(j)
3344 ,th_rowid(j);
3345
3346 commit;
3347
3348 th_rowid.DELETE;
3349 th_status.DELETE;
3350
3351 end loop; -- cv_bad_hier_dim_grp_sq
3352
3353 close cv_bad_hier_dim_grp_sq;
3354 l_bad_hier_dim_grp_sq_is_open := false;
3355
3356 end loop;
3357
3358 tg_dimension_group_id.DELETE;
3359 tg_depth_num.DELETE;
3360
3361 end loop; -- cv_get_dim_groups
3362
3363 close cv_get_dim_groups;
3364 l_get_dim_groups_is_open := false;
3365
3366 end if;
3367
3368 exit to_next_hier_for_loading when l_hierarchy_error_flag;
3369
3370 end if;
3371 --END:final_hierval
3372
3373 --BEGIN:multi_thread_final_insert
3374 ------------------------------------------------------------------------------
3375 -- STEP 10: If HIERARCHY_OBJECT_NAME does not exist, then insert the
3376 -- necessary rows in FEM_OBJECT_CATALOG_B/_TL, FEM_HIERARCHIES,
3377 -- FEM_HIER_DIMENSION_GRPS, and FEM_HIER_VALUE_SETS
3378 ------------------------------------------------------------------------------
3379
3380 if (ld_load_type = g_new_hier) then
3381
3382 -- Bug Fix 3920423: SINGLE VALUE_SET_ID HIERARCHY DOES NOT SHOW UP IN
3383 -- DEF LOV
3384 --
3385 -- If the hierarchy has a single value set defined, we must get the
3386 -- VALUE_SET_ID for FEM_HIERARCHIES. This must be done before any
3387 -- inserts are made in FEM_OBJECT_CATALOG_B/_TL, as we can have an
3388 -- exception when trying to get the VALUE_SET_ID.
3389 if ( (l_value_set_required_flag = 'Y')
3390 and (ld_multi_value_set_flag = 'N') ) then
3391
3392 ------------------------------------------------------------------------------
3393 -- STEP 10.1: Get the VALUE_SET_ID for FEM_HIERARCHIES
3394 ------------------------------------------------------------------------------
3395 FEM_ENGINES_PKG.tech_message (
3396 p_severity => g_log_level_1
3397 ,p_module => G_BLOCK||'.'||l_api_name
3398 ,p_msg_text => 'Step 10.1: Get the VALUE_SET_ID for FEM_HIERARCHIES'
3399 );
3400
3401 begin
3402
3403 execute immediate l_get_value_sets_stmt
3404 into ld_value_set_id
3405 using ld_hierarchy_object_name
3406 ,l_dimension_id;
3407
3408 exception
3409
3410 when no_data_found then
3411 FEM_ENGINES_PKG.user_message (
3412 p_app_name => G_FEM
3413 ,p_msg_name => G_HIER_LDR_NO_HIER_VS_ERR
3414 ,p_token1 => 'HIERARCHY_OBJECT_NAME'
3415 ,p_value1 => ld_hierarchy_object_name
3416 );
3417 l_hierarchy_error_flag := true;
3418
3419 when too_many_rows then
3420 l_hierarchy_error_flag := true;
3421 ld_status := 'MULTIPLE_VALUE_SETS';
3422
3423 set_hier_table_err_msg (
3424 p_hier_table_name => 'FEM_HIER_VALUE_SETS_T'
3425 ,p_status => ld_status
3426 );
3427
3428 execute immediate
3429 ' update fem_hier_value_sets_t'||
3430 ' set status = :b_status'||
3431 ' where hierarchy_object_name = :b_hierarchy_object_name'||
3432 l_status_clause||
3433 ' and language = userenv(''LANG'')'
3434 using ld_status
3435 ,ld_hierarchy_object_name;
3436
3437 end;
3438
3439 -- Exit immediately in an exception occurred to prevent any
3440 -- hierarchy inserts from happening.
3441 exit to_next_hier_for_loading when l_hierarchy_error_flag;
3442
3443 else
3444
3445 ld_value_set_id := null;
3446
3447 end if;
3448
3449 ------------------------------------------------------------------------------
3450 -- STEP 10.2: Insert into FEM_OBJECT_CATALOG_B/_TL
3451 ------------------------------------------------------------------------------
3452 FEM_ENGINES_PKG.tech_message (
3453 p_severity => g_log_level_1
3454 ,p_module => G_BLOCK||'.'||l_api_name
3455 ,p_msg_text => 'Step 10.2: Insert into FEM_OBJECT_CATALOG_B/_TL'
3456 );
3457
3458 select fem_object_id_seq.nextval
3459 into ld_hierarchy_object_id
3460 from dual;
3461
3462 FEM_OBJECT_CATALOG_PKG.INSERT_ROW (
3463 x_rowid => l_rowid
3464 ,x_object_id => ld_hierarchy_object_id
3465 ,x_object_type_code => 'HIERARCHY'
3466 ,x_folder_id => ld_folder_id
3467 ,x_local_vs_combo_id => null
3468 ,x_object_access_code => 'W' --todo
3469 ,x_object_origin_code => 'IMPORT' --todo
3470 ,x_object_version_number => g_object_version_number
3471 ,x_object_name => ld_hierarchy_object_name
3472 ,x_description => ld_hierarchy_object_name
3473 ,x_creation_date => sysdate
3474 ,x_created_by => l_user_id
3475 ,x_last_update_date => sysdate
3476 ,x_last_updated_by => l_user_id
3477 ,x_last_update_login => l_login_id
3478 );
3479
3480 ------------------------------------------------------------------------------
3481 -- STEP 10.3: Insert into FEM_HIERARCHIES
3482 ------------------------------------------------------------------------------
3483 FEM_ENGINES_PKG.tech_message (
3484 p_severity => g_log_level_1
3485 ,p_module => G_BLOCK||'.'||l_api_name
3486 ,p_msg_text => 'Step 10.3: Insert into FEM_HIERARCHIES'
3487 );
3488
3489 insert into fem_hierarchies (
3490 hierarchy_obj_id
3491 ,dimension_id
3492 ,hierarchy_type_code
3493 ,group_sequence_enforced_code
3494 ,multi_top_flag
3495 ,financial_category_flag
3496 ,value_set_id
3497 ,calendar_id
3498 ,period_type
3499 ,personal_flag
3500 ,flattened_rows_flag
3501 ,creation_date
3502 ,created_by
3503 ,last_updated_by
3504 ,last_update_date
3505 ,last_update_login
3506 ,hierarchy_usage_code
3507 ,multi_value_set_flag
3508 ,object_version_number
3509 ) values (
3510 ld_hierarchy_object_id
3511 ,l_dimension_id
3512 ,ld_hierarchy_type_code
3513 ,ld_group_seq_enforced_code
3514 ,ld_multi_top_flag
3515 ,'N'
3516 ,ld_value_set_id
3517 ,ld_calendar_id
3518 ,null
3519 ,'N'
3520 ,ld_flattened_rows_flag
3521 ,sysdate
3522 ,l_user_id
3523 ,l_user_id
3524 ,sysdate
3525 ,l_login_id
3526 ,ld_hierarchy_usage_code
3527 ,ld_multi_value_set_flag
3528 ,g_object_version_number
3529 );
3530
3531 ------------------------------------------------------------------------------
3532 -- STEP 10.4: Insert into FEM_HIER_DIMENSION_GRPS
3533 ------------------------------------------------------------------------------
3534 if (ld_group_seq_enforced_code <> 'NO_GROUPS') then
3535
3536 FEM_ENGINES_PKG.tech_message (
3537 p_severity => g_log_level_1
3538 ,p_module => G_BLOCK||'.'||l_api_name
3539 ,p_msg_text => 'Step 10.4: Insert into FEM_HIER_DIMENSION_GRPS'
3540 );
3541
3542 open cv_get_dim_groups
3543 for l_get_dim_groups_t_stmt
3544 using ld_hierarchy_object_name
3545 ,l_dimension_id;
3546
3547 l_get_dim_groups_is_open := true;
3548
3549 loop
3550
3551 fetch cv_get_dim_groups
3552 bulk collect into
3553 tg_dimension_group_id
3554 ,tg_relative_dim_group_seq
3555 limit gv_fetch_limit;
3556
3557 l_get_dim_groups_last_row := tg_dimension_group_id.LAST;
3558 if (l_get_dim_groups_last_row is null) then
3559 exit;
3560 end if;
3561
3562 forall j in 1..l_get_dim_groups_last_row
3563 insert into fem_hier_dimension_grps (
3564 dimension_group_id
3565 ,hierarchy_obj_id
3566 ,relative_dimension_group_seq
3567 ,creation_date
3568 ,created_by
3569 ,last_updated_by
3570 ,last_update_date
3571 ,last_update_login
3572 ,object_version_number
3573 ) values (
3574 tg_dimension_group_id(j)
3575 ,ld_hierarchy_object_id
3576 ,tg_relative_dim_group_seq(j)
3577 ,sysdate
3578 ,l_user_id
3579 ,l_user_id
3580 ,sysdate
3581 ,l_login_id
3582 ,g_object_version_number
3583 );
3584
3585 tg_dimension_group_id.DELETE;
3586 tg_relative_dim_group_seq.DELETE;
3587
3588 end loop;
3589
3590 close cv_get_dim_groups;
3591 l_get_dim_groups_is_open := false;
3592
3593 end if;
3594
3595 commit;
3596
3597 end if;
3598
3599 ------------------------------------------------------------------------------
3600 -- STEP 10.5: Insert into FEM_HIER_VALUE_SETS for new and updated hierarchies
3601 -- that must have value sets or that have a calendar context.
3602 ------------------------------------------------------------------------------
3603 if (l_value_set_required_flag = 'Y') then
3604
3605 -- Insert for all new single or multi value set hiearchies, and for
3606 -- updates on multi value set hierarchies where we allow more value sets
3607 -- to be added (Bug 4661474).
3608 if ( (ld_load_type = g_new_hier) or (ld_multi_value_set_flag = 'Y') ) then
3609
3610 FEM_ENGINES_PKG.tech_message (
3611 p_severity => g_log_level_1
3612 ,p_module => G_BLOCK||'.'||l_api_name
3613 ,p_msg_text => 'Step 10.5: Insert into FEM_HIER_VALUE_SETS'
3614 );
3615
3616 -- For updates on multi value set hierarchies, we must first delete
3617 -- all the hierarchy value sets in FEM_HIER_VALUE_SETS_T hat have
3618 -- already been assigned to the existing hierarchy (Bug 4661474).
3619 if (ld_load_type <> g_new_hier) then
3620
3621 execute immediate
3622 ' delete from fem_hier_value_sets_t hvst'||
3623 ' where hvst.hierarchy_object_name = :b_hierarchy_object_name'||
3624 l_status_clause||
3625 ' and hvst.language = userenv(''LANG'')'||
3626 ' and exists ('||
3627 ' select 1'||
3628 ' from fem_hier_value_sets hvs'||
3629 ' ,fem_value_sets_b vsb'||
3630 ' where hvs.hierarchy_obj_id = :b_hierarchy_object_id'||
3631 ' and vsb.value_set_id = hvs.value_set_id'||
3632 ' and vsb.value_set_display_code = hvst.value_set_display_code'||
3633 ' )'
3634 using ld_hierarchy_object_name
3635 ,ld_hierarchy_object_id;
3636
3637 end if;
3638
3639 open cv_get_value_sets
3640 for l_get_value_sets_stmt
3641 using ld_hierarchy_object_name
3642 ,l_dimension_id;
3643
3644 l_get_value_sets_is_open := true;
3645
3646 loop
3647
3648 fetch cv_get_value_sets
3649 bulk collect into
3650 tv_value_set_id
3651 limit gv_fetch_limit;
3652
3653 l_get_value_sets_last_row := tv_value_set_id.LAST;
3654 if (l_get_value_sets_last_row is null) then
3655 exit;
3656 end if;
3657
3658 forall j in 1..l_get_value_sets_last_row
3659 insert into fem_hier_value_sets (
3660 hierarchy_obj_id
3661 ,value_set_id
3662 ,creation_date
3663 ,created_by
3664 ,last_updated_by
3665 ,last_update_date
3666 ,last_update_login
3667 ,object_version_number
3668 ) values (
3669 ld_hierarchy_object_id
3670 ,tv_value_set_id(j)
3671 ,sysdate
3672 ,l_user_id
3673 ,l_user_id
3674 ,sysdate
3675 ,l_login_id
3676 ,g_object_version_number
3677 );
3678
3679 tv_value_set_id.DELETE;
3680
3681 end loop;
3682
3683 close cv_get_value_sets;
3684 l_get_value_sets_is_open := false;
3685
3686 commit;
3687
3688 end if;
3689
3690 -- Bug Fix 3789176: Add Calendar ID row in FEM_HIER_VALUE_SETS
3691 -- table for DHM when loading a new CAL_PERIOD hierarchy.
3692 elsif (p_dimension_varchar_label = 'CAL_PERIOD') then
3693
3694 if (ld_load_type = g_new_hier) then
3695
3696 insert into fem_hier_value_sets (
3697 hierarchy_obj_id
3698 ,value_set_id
3699 ,creation_date
3700 ,created_by
3701 ,last_updated_by
3702 ,last_update_date
3703 ,last_update_login
3704 ,object_version_number
3705 ) values (
3706 ld_hierarchy_object_id
3707 ,ld_calendar_id
3708 ,sysdate
3709 ,l_user_id
3710 ,l_user_id
3711 ,sysdate
3712 ,l_login_id
3713 ,g_object_version_number
3714 );
3715
3716 commit;
3717
3718 end if;
3719
3720 end if;
3721
3722
3723 ------------------------------------------------------------------------------
3724 -- STEP 11: For each HIERARCHY_OBJ_DEF_DISPLAY_NAME check to see if it exists
3725 -- in FEM_OBJECT_DEFINITION_B/_TL for the designated language. Create
3726 -- the necessary rows in FEM_OBJECT_DEFINITION_B/_TL and FEM_HIER_DEFINITIONS
3727 -- it does not exist.
3728 ------------------------------------------------------------------------------
3729
3730 if (ld_load_type in (g_new_hier, g_new_hier_def)) then
3731
3732 FEM_ENGINES_PKG.tech_message (
3733 p_severity => g_log_level_1
3734 ,p_module => G_BLOCK||'.'||l_api_name
3735 ,p_msg_text => 'Step 11: Insert Hierarchy Object Definition Records'
3736 );
3737
3738 if (l_new_max_obj_def_id is not null) then
3739
3740 update fem_object_definition_b
3741 set effective_end_date = l_new_max_eff_end_date
3742 where object_definition_id = l_new_max_obj_def_id;
3743
3744 l_new_max_obj_def_id := null;
3745 l_new_max_eff_end_date := null;
3746
3747 end if;
3748
3749 select fem_object_definition_id_seq.nextval
3750 into ld_hier_obj_def_id
3751 from dual;
3752
3753 FEM_OBJECT_DEFINITION_PKG.INSERT_ROW (
3754 x_rowid => l_rowid
3755 ,x_object_definition_id => ld_hier_obj_def_id
3756 ,x_object_id => ld_hierarchy_object_id
3757 ,x_effective_start_date => ld_effective_start_date
3758 ,x_effective_end_date => ld_effective_end_date
3759 ,x_object_origin_code => 'IMPORT' --todo
3760 ,x_approval_status_code => 'NOT_APPLICABLE'
3761 ,x_old_approved_copy_flag => 'N'
3762 ,x_old_approved_copy_obj_def_id => null
3763 ,x_approved_by => null
3764 ,x_approval_date => null
3765 ,x_display_name => ld_hier_obj_def_display_name
3766 ,x_description => ld_hier_obj_def_display_name
3767 ,x_creation_date => sysdate
3768 ,x_created_by => l_user_id
3769 ,x_last_update_date => sysdate
3770 ,x_last_updated_by => l_user_id
3771 ,x_last_update_login => l_login_id
3772 ,x_object_version_number => g_object_version_number
3773 );
3774
3775 insert into fem_hier_definitions (
3776 hierarchy_obj_def_id
3777 ,flattened_rows_completion_code
3778 ,creation_date
3779 ,created_by
3780 ,last_updated_by
3781 ,last_update_date
3782 ,last_update_login
3783 ,object_version_number
3784 ) values (
3785 ld_hier_obj_def_id
3786 ,decode(ld_flattened_rows_flag,'Y','PENDING','COMPLETED')
3787 ,sysdate
3788 ,l_user_id
3789 ,l_user_id
3790 ,sysdate
3791 ,l_login_id
3792 ,g_object_version_number
3793 );
3794
3795 commit;
3796
3797 end if;
3798
3799
3800 ------------------------------------------------------------------------------
3801 -- STEP 12: If the object definition already existed, then delete all rows in
3802 -- FEM_xName_HIER for that object definition.
3803 ------------------------------------------------------------------------------
3804
3805 if (ld_load_type = g_update_hier_def) then
3806
3807 FEM_ENGINES_PKG.tech_message (
3808 p_severity => g_log_level_1
3809 ,p_module => G_BLOCK||'.'||l_api_name
3810 ,p_msg_text => 'Step 12: Deleting Hierarchy Relationship Records'
3811 );
3812
3813 execute immediate l_delete_hier_rels_stmt
3814 using ld_hier_obj_def_id;
3815
3816 commit;
3817
3818 -- Also update the Hierarchy Definition to 'PENDING' if the hierarchy
3819 -- will be flattened after loading completes.
3820 if (ld_flattened_rows_flag = 'Y') then
3821
3822 update fem_hier_definitions
3823 set flattened_rows_completion_code = 'PENDING'
3824 ,last_updated_by = l_user_id
3825 ,last_update_date = sysdate
3826 ,last_update_login = l_login_id
3827 where hierarchy_obj_def_id = ld_hier_obj_def_id;
3828
3829 commit;
3830
3831 end if;
3832
3833 end if;
3834
3835
3836 ------------------------------------------------------------------------------
3837 -- STEP 13: Insert all records for this request id from
3838 -- FEM_HIERVAL_VSR_T/_CALP_T into FEM_xName_HIER.
3839 ------------------------------------------------------------------------------
3840
3841 FEM_ENGINES_PKG.tech_message (
3842 p_severity => g_log_level_1
3843 ,p_module => G_BLOCK||'.'||l_api_name
3844 ,p_msg_text => 'Step 13: Inserting Relationship Records into '||l_target_hierval_table
3845 );
3846
3847 execute immediate l_insert_hier_rels_stmt
3848 using ld_hier_obj_def_id
3849 ,l_user_id
3850 ,l_user_id
3851 ,l_login_id
3852 ,g_object_version_number
3853 ,l_request_id;
3854
3855 commit;
3856
3857
3858 ------------------------------------------------------------------------------
3859 -- STEP 14: Delete from the FEM_xName_HIER_T table rows for each hierarchy
3860 -- object definition that was successfully loaded.
3861 ------------------------------------------------------------------------------
3862 FEM_ENGINES_PKG.tech_message (
3863 p_severity => g_log_level_1
3864 ,p_module => G_BLOCK||'.'||l_api_name
3865 ,p_msg_text => 'Step 14: Purging Relationship Interface Records in '||l_source_hier_table
3866 );
3867
3868 execute immediate l_delete_hier_t_rels_stmt
3869 using ld_hierarchy_object_name
3870 ,ld_hier_obj_def_display_name;
3871
3872 commit;
3873
3874
3875 ------------------------------------------------------------------------------
3876 -- STEP 15: Delete from the FEM_HIERARCHIES_T table rows for each hierarchy
3877 -- object definition that was successfully loaded.
3878 ------------------------------------------------------------------------------
3879 FEM_ENGINES_PKG.tech_message (
3880 p_severity => g_log_level_1
3881 ,p_module => G_BLOCK||'.'||l_api_name
3882 ,p_msg_text => 'Step 15: Purging all other Hierarchy Interface tables'
3883 );
3884
3885 -- Only delete hierarchy value sets from interface tables if they
3886 -- were inserted because a new hierarchy was loaded or an existing
3887 -- multi value set hierarchy was updated (Bug 4661474).
3888 if (l_value_set_required_flag = 'Y') then
3889
3890 if ( (ld_load_type = g_new_hier) or (ld_multi_value_set_flag = 'Y') ) then
3891
3892 execute immediate
3893 ' delete from fem_hier_value_sets_t'||
3894 ' where hierarchy_object_name = :b_hierarchy_object_name'||
3895 l_status_clause
3896 using ld_hierarchy_object_name;
3897
3898 end if;
3899
3900 end if;
3901
3902 -- Only delete hierarchy dimension groups from interface tables if
3903 -- they were inserted because a new hierarchy was loaded.
3904 if (ld_load_type = g_new_hier) then
3905
3906 if (ld_group_seq_enforced_code <> 'NO_GROUPS') then
3907 execute immediate
3908 ' delete from fem_hier_dim_grps_t'||
3909 ' where hierarchy_object_name = :b_hierarchy_object_name'||
3910 l_status_clause
3911 using ld_hierarchy_object_name;
3912 end if;
3913
3914 end if;
3915
3916 execute immediate
3917 ' delete from fem_hierarchies_t'||
3918 ' where rowid = :b_rowid'||
3919 l_status_clause
3920 using ld_rowid;
3921
3922 commit;
3923
3924
3925 ------------------------------------------------------------------------------
3926 -- STEP 16: If FLATTENED_ROWS_FLAG = Y, then create all of the additional
3927 -- exploded rows by calling the DHM api.
3928 ------------------------------------------------------------------------------
3929 if (ld_flattened_rows_flag = 'Y') then
3930
3931 FEM_ENGINES_PKG.tech_message (
3932 p_severity => g_log_level_1
3933 ,p_module => G_BLOCK||'.'||l_api_name
3934 ,p_msg_text => 'Step 16: Flattening Hierarchy'
3935 );
3936
3937 FEM_HIER_UTILS_PVT.Flatten_Whole_Hier_Version (
3938 p_api_version => 1.0
3939 ,p_commit => FND_API.G_TRUE
3940 ,p_hier_obj_defn_id => ld_hier_obj_def_id
3941 ,x_return_status => l_return_status
3942 ,x_msg_count => l_msg_count
3943 ,x_msg_data => l_msg_data
3944 );
3945
3946 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
3947
3948 --todo: this API should be put in a common loader package.
3949 get_put_messages (
3950 p_msg_count => l_msg_count
3951 ,p_msg_data => l_msg_data
3952 );
3953
3954 FEM_ENGINES_PKG.user_message (
3955 p_app_name => G_FEM
3956 ,p_msg_name => G_HIER_LDR_HIER_FLATTEN_ERR
3957 );
3958
3959 -- Set the Concurrent Process to WARNING.
3960 l_completion_status := FND_CONCURRENT.set_completion_status('WARNING',null);
3961
3962 exit to_next_hier_for_loading;
3963
3964 end if;
3965
3966 end if;
3967
3968 --END:multi_thread_final_insert
3969
3970 -- Always exit the to_next_hier_for_loading loop at the end to ensure
3971 -- only one pass.
3972 exit to_next_hier_for_loading;
3973
3974 end loop to_next_hier_for_loading;
3975
3976
3977 if (l_hierarchy_error_flag) then
3978
3979 FEM_ENGINES_PKG.tech_message (
3980 p_severity => g_log_level_1
3981 ,p_module => G_BLOCK||'.'||l_api_name
3982 ,p_msg_text => 'Validation Error. Updating Status on Interface Tables.'
3983 );
3984
3985 execute immediate
3986 ' update fem_hierarchies_t'||
3987 ' set status = :b_status'||
3988 ' where rowid = :b_rowid'||
3989 l_status_clause
3990 using ld_status
3991 ,ld_rowid;
3992
3993 -- For performance reasons, do not update all other tables and records
3994 -- to INVALID_HIERARCHY.
3995 --
3996 -- execute immediate
3997 -- ' update fem_hier_value_sets_t'||
3998 -- ' set status = ''INVALID_HIERARCHY'''||
3999 -- ' where hierarchy_object_name = ld_hierarchy_object_name'||
4000 -- l_status_clause
4001 -- using ld_hierarchy_object_name;
4002 --
4003 -- execute immediate
4004 -- ' update fem_hier_dim_grps_t'||
4005 -- ' set status = ''INVALID_HIERARCHY'''||
4006 -- ' where hierarchy_object_name = ld_hierarchy_object_name'||
4007 -- l_status_clause
4008 -- using ld_hierarchy_object_name;
4009 --
4010 -- execute immediate
4011 -- bld_update_status_stmt (
4012 -- p_dimension_varchar_label => p_dimension_varchar_label
4013 -- ,p_execution_mode => p_execution_mode
4014 -- ,p_source_hier_table => l_source_hier_table
4015 -- ,p_hier_object_name_flag => 'Y'
4016 -- ,p_hier_obj_def_name_flag => 'Y'
4017 -- )
4018 -- using 'INVALID_HIERARCHY'
4019 -- ,ld_hierarchy_object_name
4020 -- ,ld_hier_obj_def_display_name;
4021
4022 commit;
4023
4024 -- Raise exception to perform engine post processing for an error.
4025 raise e_hierarchy_error;
4026
4027 end if;
4028
4029 end loop;
4030
4031 close cv_get_hier_defs;
4032 l_get_hier_defs_is_open := false;
4033
4034 ------------------------------------------------------------------------------
4035 -- STEP 17: Engine Master Post Processing.
4036 ------------------------------------------------------------------------------
4037 FEM_ENGINES_PKG.tech_message (
4038 p_severity => g_log_level_1
4039 ,p_module => G_BLOCK||'.'||l_api_name
4040 ,p_msg_text => 'Step 17: Post Processing'
4041 );
4042
4043 eng_master_post_proc (
4044 p_request_id => l_request_id
4045 ,p_object_id => l_loader_object_id
4046 ,p_exec_status_code => g_exec_status_success
4047 ,p_user_id => l_user_id
4048 ,p_login_id => l_login_id
4049 ,p_dimension_varchar_label => p_dimension_varchar_label
4050 ,p_execution_mode => p_execution_mode
4051 ,p_target_hierval_table => l_target_hierval_table
4052 );
4053
4054 FEM_ENGINES_PKG.tech_message (
4055 p_severity => g_log_level_2
4056 ,p_module => G_BLOCK||'.'||l_api_name
4057 ,p_msg_text => 'END'
4058 );
4059
4060 EXCEPTION
4061
4062 when e_loader_error then
4063
4064 FEM_ENGINES_PKG.tech_message(
4065 p_severity => g_log_level_6
4066 ,p_module => G_BLOCK||'.'||l_api_name
4067 ,p_msg_text => 'Dimension Hierarchy Loader Exception'
4068 );
4069
4070 l_completion_status := FND_CONCURRENT.set_completion_status('ERROR',null);
4071
4072 eng_master_post_proc (
4073 p_request_id => l_request_id
4074 ,p_object_id => l_loader_object_id
4075 ,p_exec_status_code => g_exec_status_error_rerun
4076 ,p_user_id => l_user_id
4077 ,p_login_id => l_login_id
4078 ,p_dimension_varchar_label => p_dimension_varchar_label
4079 ,p_execution_mode => p_execution_mode
4080 ,p_target_hierval_table => l_target_hierval_table
4081 );
4082
4083 when e_hierarchy_error then
4084
4085 FEM_ENGINES_PKG.tech_message(
4086 p_severity => g_log_level_6
4087 ,p_module => G_BLOCK||'.'||l_api_name
4088 ,p_msg_text => 'Hierarchy Exception'
4089 );
4090
4091 l_completion_status := FND_CONCURRENT.set_completion_status('ERROR',null);
4092
4093 if (l_get_hier_defs_is_open) then
4094 close cv_get_hier_defs;
4095 end if;
4096
4097 eng_master_post_proc (
4098 p_request_id => l_request_id
4099 ,p_object_id => l_loader_object_id
4100 ,p_exec_status_code => g_exec_status_error_rerun
4101 ,p_user_id => l_user_id
4102 ,p_login_id => l_login_id
4103 ,p_dimension_varchar_label => p_dimension_varchar_label
4104 ,p_execution_mode => p_execution_mode
4105 ,p_target_hierval_table => l_target_hierval_table
4106 );
4107
4108 when others then
4109
4110 gv_prg_msg := sqlerrm;
4111 gv_callstack := dbms_utility.format_call_stack;
4112
4113 FEM_ENGINES_PKG.tech_message(
4114 p_severity => g_log_level_6
4115 ,p_module => G_BLOCK||'.'||l_api_name||'.Unexpected_Exception.Error_Message'
4116 ,p_msg_text => gv_prg_msg
4117 );
4118
4119 FEM_ENGINES_PKG.tech_message(
4120 p_severity => g_log_level_6
4121 ,p_module => G_BLOCK||'.'||l_api_name||'.Unexpected_Exception.Callstack'
4122 ,p_msg_text => gv_callstack
4123 );
4124
4125 FEM_ENGINES_PKG.User_Message (
4126 p_app_name => G_FEM
4127 ,p_msg_name => G_UNEXPECTED_ERROR
4128 ,p_token1 => 'ERR_MSG'
4129 ,p_value1 => gv_prg_msg
4130 );
4131
4132 l_completion_status := FND_CONCURRENT.set_completion_status('ERROR',null);
4133
4134 if (l_bad_value_sets_is_open) then
4135 close cv_bad_value_sets;
4136 end if;
4137 if (l_bad_dim_groups_is_open) then
4138 close cv_bad_dim_groups;
4139 end if;
4140 if (l_bad_hier_calendars_is_open) then
4141 close cv_bad_hier_calendars;
4142 end if;
4143 if (l_bad_hier_value_sets_is_open) then
4144 close cv_bad_hier_value_sets;
4145 end if;
4146 if (l_bad_hier_members_is_open) then
4147 close cv_bad_hier_members;
4148 end if;
4149 if (l_bad_hier_dups_is_open) then
4150 close cv_bad_hier_dups;
4151 end if;
4152 if (l_bad_hier_rec_leafs_is_open) then
4153 close cv_bad_hier_rec_leafs;
4154 end if;
4155 if (l_bad_hier_rec_nodes_is_open) then
4156 close cv_bad_hier_rec_nodes;
4157 end if;
4158 if (l_bad_hier_roots_is_open) then
4159 close cv_bad_hier_roots;
4160 end if;
4161 if (l_bad_hier_dim_groups_is_open) then
4162 close cv_bad_hier_dim_groups;
4163 end if;
4164 if (l_bad_hier_dim_grp_sq_is_open) then
4165 close cv_bad_hier_dim_grp_sq;
4166 end if;
4167 if (l_get_dim_groups_is_open) then
4168 close cv_get_dim_groups;
4169 end if;
4170 if (l_get_value_sets_is_open) then
4171 close cv_get_value_sets;
4172 end if;
4173 if (l_get_hier_defs_is_open) then
4174 close cv_get_hier_defs;
4175 end if;
4176 if (l_get_hier_roots_is_open) then
4177 close cv_get_hier_roots;
4178 end if;
4179 if (l_get_hier_rels_is_open) then
4180 close cv_get_hier_rels;
4181 end if;
4182
4183 eng_master_post_proc (
4184 p_request_id => l_request_id
4185 ,p_object_id => l_loader_object_id
4186 ,p_exec_status_code => g_exec_status_error_rerun
4187 ,p_user_id => l_user_id
4188 ,p_login_id => l_login_id
4189 ,p_dimension_varchar_label => p_dimension_varchar_label
4190 ,p_execution_mode => p_execution_mode
4191 ,p_target_hierval_table => l_target_hierval_table
4192 );
4193
4194 -- Bug Fix 3657227: removing this raise statement because it causes
4195 -- ORACLE error 6502 in FDPSTP when it is called.
4196 --
4197 -- raise;
4198
4199 END Main;
4200
4201
4202
4203 /*===========================================================================+
4204 | PROCEDURE
4205 | GET_DIMENSION_INFO
4206 |
4207 | DESCRIPTION
4208 | Validates the input dimension and obtains object and column names
4209 | for the dimension
4210 |
4211 | SCOPE - PRIVATE
4212 |
4213 +===========================================================================*/
4214
4215 PROCEDURE get_dimension_info (
4216 p_dimension_varchar_label in varchar2
4217 ,x_dimension_id out nocopy number
4218 ,x_target_hier_table out nocopy varchar2
4219 ,x_source_hier_table out nocopy varchar2
4220 ,x_member_b_table out nocopy varchar2
4221 ,x_member_attr_table out nocopy varchar2
4222 ,x_member_col out nocopy varchar2
4223 ,x_member_dc_col out nocopy varchar2
4224 ,x_group_use_code out nocopy varchar2
4225 ,x_value_set_required_flag out nocopy varchar2
4226 ,x_hier_type_allowed_code out nocopy varchar2
4227 ,x_hier_versioning_type_code out nocopy varchar2
4228 )
4229 IS
4230
4231 l_api_name constant varchar2(30) := 'get_dimension_info';
4232
4233 BEGIN
4234
4235 FEM_ENGINES_PKG.tech_message (
4236 p_severity => g_log_level_2
4237 ,p_module => G_BLOCK||'.'||l_api_name
4238 ,p_msg_text => 'BEGIN'
4239 );
4240
4241 select dimension_id
4242 ,hierarchy_table_name
4243 ,hierarchy_table_name||'_T'
4244 ,member_b_table_name
4245 ,attribute_table_name
4246 ,member_col
4247 ,member_display_code_col
4248 ,group_use_code
4249 ,value_set_required_flag
4250 ,hier_type_allowed_code
4251 ,hier_versioning_type_code
4252 into x_dimension_id
4253 ,x_target_hier_table
4254 ,x_source_hier_table
4255 ,x_member_b_table
4256 ,x_member_attr_table
4257 ,x_member_col
4258 ,x_member_dc_col
4259 ,x_group_use_code
4260 ,x_value_set_required_flag
4261 ,x_hier_type_allowed_code
4262 ,x_hier_versioning_type_code
4263 from fem_xdim_dimensions_vl
4264 where dimension_varchar_label = p_dimension_varchar_label
4265 and composite_dimension_flag = 'N'
4266 and hierarchy_table_name is not null
4267 and read_only_flag = 'N';
4268
4269 FEM_ENGINES_PKG.tech_message (
4270 p_severity => g_log_level_2
4271 ,p_module => G_BLOCK||'.'||l_api_name
4272 ,p_msg_text => 'END'
4273 );
4274
4275 EXCEPTION
4276
4277 when no_data_found then
4278 FEM_ENGINES_PKG.user_message (
4279 p_app_name => G_FEM
4280 ,p_msg_name => G_DIM_NOT_FOUND_ERR
4281 );
4282 raise e_loader_error;
4283
4284 END get_dimension_info;
4285
4286
4287
4288 /*===========================================================================+
4289 | PROCEDURE
4290 | REGISTER_PROCESS_EXECUTION
4291 |
4292 | DESCRIPTION
4293 | Registers the request, object execution and object definition in the
4294 | processing locks tables.
4295 |
4296 | SCOPE - PRIVATE
4297 |
4298 +===========================================================================*/
4299
4300 PROCEDURE register_process_execution (
4301 p_request_id in number
4302 ,p_object_id in number
4303 ,p_obj_def_id in number
4304 ,p_execution_mode in varchar
4305 ,p_user_id in number
4306 ,p_login_id in number
4307 ,p_pgm_id in number
4308 ,p_pgm_app_id in number
4309 ,p_hierarchy_object_name in varchar2
4310 )
4311 IS
4312
4313 l_api_name constant varchar2(30) := 'register_process_execution';
4314
4315 l_exec_state varchar2(30); -- normal, restart, rerun
4316 l_stmt_type fem_pl_tables.statement_type%TYPE;
4317 l_prev_request_id number;
4318
4319 l_return_status t_return_status%TYPE;
4320 l_msg_count t_msg_count%TYPE;
4321 l_msg_data t_msg_data%TYPE;
4322
4323 e_pl_register_req_failed exception;
4324 e_pl_register_exec_failed exception;
4325 e_pl_register_obj_def_failed exception;
4326
4327 BEGIN
4328
4329 FEM_ENGINES_PKG.tech_message (
4330 p_severity => g_log_level_2
4331 ,p_module => G_BLOCK||'.'||l_api_name
4332 ,p_msg_text => 'BEGIN'
4333 );
4334
4335 savepoint register_process_execution_pub;
4336
4337 -- Call the FEM_PL_PKG.Register_Request API procedure to register
4338 -- the concurrent request in FEM_PL_REQUESTS. For hierarchy loader process
4339 -- locks, we must pass hierarchy_object_name to make sure that specified
4340 -- hierarchy object can only be loaded by one user at a time. Do not pass
4341 -- dimension id, as the PL fwk uses that for dimension loader process locks.
4342 FEM_PL_PKG.register_request (
4343 p_api_version => 1.0
4344 ,p_request_id => p_request_id
4345 ,p_user_id => p_user_id
4346 ,p_last_update_login => p_login_id
4347 ,p_program_id => p_pgm_id
4348 ,p_program_login_id => p_login_id
4349 ,p_program_application_id => p_pgm_app_id
4350 ,p_exec_mode_code => p_execution_mode
4351 ,p_hierarchy_name => p_hierarchy_object_name
4352 ,x_msg_count => l_msg_count
4353 ,x_msg_data => l_msg_data
4354 ,x_return_status => l_return_status
4355 );
4356
4357 -- Request Lock exists
4358 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
4359 --todo: this API should be put in a common loader package.
4360 get_put_messages (
4361 p_msg_count => l_msg_count
4362 ,p_msg_data => l_msg_data
4363 );
4364 raise e_pl_register_req_failed;
4365 end if;
4366
4367 -- Call the FEM_PL_PKG.Register_Object_Execution API procedure to register
4368 -- the object execution in FEM_PL_OBJECT_EXECUTIONS to obtain an execution
4369 -- lock.
4370 FEM_PL_PKG.register_object_execution (
4371 p_api_version => 1.0
4372 ,p_request_id => p_request_id
4373 ,p_object_id => p_object_id
4374 ,p_exec_object_definition_id => p_obj_def_id
4375 ,p_user_id => p_user_id
4376 ,p_last_update_login => p_login_id
4377 ,p_exec_mode_code => p_execution_mode
4378 ,x_exec_state => l_exec_state
4379 ,x_prev_request_id => l_prev_request_id
4380 ,x_msg_count => l_msg_count
4381 ,x_msg_data => l_msg_data
4382 ,x_return_status => l_return_status
4383 );
4384
4385 -- Object Execution Lock exists
4386 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
4387 --todo: this API should be put in a common loader package.
4388 get_put_messages (
4389 p_msg_count => l_msg_count
4390 ,p_msg_data => l_msg_data
4391 );
4392 raise e_pl_register_exec_failed;
4393 end if;
4394
4395 FEM_PL_PKG.register_object_def (
4396 p_api_version => 1.0
4397 ,p_request_id => p_request_id
4398 ,p_object_id => p_object_id
4399 ,p_object_definition_id => p_obj_def_id
4400 ,p_user_id => p_user_id
4401 ,p_last_update_login => p_login_id
4402 ,x_msg_count => l_msg_count
4403 ,x_msg_data => l_msg_data
4404 ,x_return_status => l_return_status
4405 );
4406
4407 -- Object Definition Lock exists
4408 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
4409 --todo: this API should be put in a common loader package.
4410 get_put_messages (
4411 p_msg_count => l_msg_count
4412 ,p_msg_data => l_msg_data
4413 );
4414 raise e_pl_register_obj_def_failed;
4415 end if;
4416
4417 commit;
4418
4419 FEM_ENGINES_PKG.tech_message (
4420 p_severity => g_log_level_2
4421 ,p_module => G_BLOCK||'.'||l_api_name
4422 ,p_msg_text => 'END'
4423 );
4424
4425 EXCEPTION
4426
4427 when e_pl_register_req_failed then
4428
4429 rollback to register_process_execution_pub;
4430 FEM_ENGINES_PKG.tech_message(
4431 p_severity => g_log_level_6
4432 ,p_module => G_BLOCK||'.'||l_api_name
4433 ,p_msg_text => 'Reqister Request Exception'
4434 );
4435 FEM_ENGINES_PKG.user_message (
4436 p_app_name => G_FEM
4437 ,p_msg_name => G_PL_REG_REQUEST_ERR
4438 );
4439 raise e_loader_error;
4440
4441 when e_pl_register_exec_failed then
4442
4443 rollback to register_process_execution_pub;
4444 FEM_ENGINES_PKG.tech_message(
4445 p_severity => g_log_level_6
4446 ,p_module => G_BLOCK||'.'||l_api_name
4447 ,p_msg_text => 'Reqister Object Execution Exception'
4448 );
4449 FEM_ENGINES_PKG.user_message (
4450 p_app_name => G_FEM
4451 ,p_msg_name => G_PL_OBJ_EXEC_LOCK_ERR
4452 );
4453 raise e_loader_error;
4454
4455 when e_pl_register_obj_def_failed then
4456
4457 rollback to register_process_execution_pub;
4458 FEM_ENGINES_PKG.tech_message(
4459 p_severity => g_log_level_6
4460 ,p_module => G_BLOCK||'.'||l_api_name
4461 ,p_msg_text => 'Reqister Object Definition Exception'
4462 );
4463 FEM_ENGINES_PKG.user_message (
4464 p_app_name => G_FEM
4465 ,p_msg_name => G_PL_OBJ_EXECLOCK_EXISTS_ERR
4466 ,p_token1 => 'OBJECT_ID'
4467 ,p_value1 => p_object_id
4468 );
4469 raise e_loader_error;
4470
4471 END register_process_execution;
4472
4473
4474
4475 /*===========================================================================+
4476 | PROCEDURE
4477 | ENG_MASTER_POST_PROC
4478 |
4479 | DESCRIPTION
4480 | Updates the status of the request and object execution in the
4481 | processing locks tables.
4482 |
4483 | SCOPE - PRIVATE
4484 |
4485 +===========================================================================*/
4486
4487 PROCEDURE eng_master_post_proc (
4488 p_request_id in number
4489 ,p_object_id in number
4490 ,p_exec_status_code in varchar2
4491 ,p_user_id in number
4492 ,p_login_id in number
4493 ,p_dimension_varchar_label in varchar2
4494 ,p_execution_mode in varchar2
4495 ,p_target_hierval_table in varchar2
4496 )
4497 IS
4498
4499 l_api_name constant varchar2(30) := 'register_process_execution';
4500
4501 l_delete_hierval_rels_stmt varchar2(10000);
4502
4503 l_return_status t_return_status%TYPE;
4504 l_msg_count t_msg_count%TYPE;
4505 l_msg_data t_msg_data%TYPE;
4506
4507 l_completion_status boolean;
4508
4509 e_post_process exception;
4510
4511 BEGIN
4512
4513 FEM_ENGINES_PKG.tech_message (
4514 p_severity => g_log_level_2
4515 ,p_module => G_BLOCK||'.'||l_api_name
4516 ,p_msg_text => 'BEGIN'
4517 );
4518
4519 ------------------------------------------------------------------------------
4520 -- STEP 1: Delete all records for this request id in the
4521 -- FEM_HIERVAL_VSR_T/_CALP_T table.
4522 ------------------------------------------------------------------------------
4523 if (p_target_hierval_table is not null) then
4524
4525 FEM_ENGINES_PKG.tech_message (
4526 p_severity => g_log_level_1
4527 ,p_module => G_BLOCK||'.'||l_api_name
4528 ,p_msg_text => 'Step 1: Purging Relationship Records in '||p_target_hierval_table
4529 );
4530
4531 bld_delete_hier_rels_stmt (
4532 p_dimension_varchar_label => p_dimension_varchar_label
4533 ,p_execution_mode => p_execution_mode
4534 ,p_target_hierval_table => p_target_hierval_table
4535 ,x_delete_hier_rels_stmt => l_delete_hierval_rels_stmt
4536 );
4537
4538 execute immediate l_delete_hierval_rels_stmt
4539 using p_request_id;
4540
4541 commit;
4542
4543 end if;
4544
4545 ------------------------------------------------------------------------------
4546 -- STEP 2: Update Object Execution Errors and Status.
4547 ------------------------------------------------------------------------------
4548
4549 FEM_ENGINES_PKG.tech_message (
4550 p_severity => g_log_level_1
4551 ,p_module => G_BLOCK||'.'||l_api_name
4552 ,p_msg_text => 'Step 2: Update Object Execution Errors and the Status'
4553 );
4554
4555 if (p_exec_status_code <> g_exec_status_success) then
4556
4557 -- Since a hierarchy load is an all or nothing process, the number
4558 -- of errors reported will be 1.
4559 FEM_PL_PKG.update_obj_exec_errors (
4560 p_api_version => 1.0
4561 ,p_request_id => p_request_id
4562 ,p_object_id => p_object_id
4563 ,p_errors_reported => 1
4564 ,p_errors_reprocessed => 0
4565 ,p_user_id => p_user_id
4566 ,p_last_update_login => p_login_id
4567 ,x_msg_count => l_msg_count
4568 ,x_msg_data => l_msg_data
4569 ,x_return_status => l_return_status
4570 );
4571
4572 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
4573 --todo: this API should be put in a common loader package.
4574 get_put_messages (
4575 p_msg_count => l_msg_count
4576 ,p_msg_data => l_msg_data
4577 );
4578 raise e_post_process;
4579 end if;
4580
4581 end if;
4582
4583 ------------------------------------
4584 -- Update Object Execution Status --
4585 ------------------------------------
4586 FEM_PL_PKG.update_obj_exec_status (
4587 p_api_version => 1.0
4588 ,p_request_id => p_request_id
4589 ,p_object_id => p_object_id
4590 ,p_exec_status_code => p_exec_status_code
4591 ,p_user_id => p_user_id
4592 ,p_last_update_login => p_login_id
4593 ,x_msg_count => l_msg_count
4594 ,x_msg_data => l_msg_data
4595 ,x_return_status => l_return_status
4596 );
4597
4598 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
4599 --todo: this API should be put in a common loader package.
4600 get_put_messages (
4601 p_msg_count => l_msg_count
4602 ,p_msg_data => l_msg_data
4603 );
4604 raise e_post_process;
4605 end if;
4606
4607 ---------------------------
4608 -- Update Request Status --
4609 ---------------------------
4610 FEM_PL_PKG.update_request_status (
4611 p_api_version => 1.0
4612 ,p_request_id => p_request_id
4613 ,p_exec_status_code => p_exec_status_code
4614 ,p_user_id => p_user_id
4615 ,p_last_update_login => p_login_id
4616 ,x_msg_count => l_msg_count
4617 ,x_msg_data => l_msg_data
4618 ,x_return_status => l_return_status
4619 );
4620
4621 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
4622 --todo: this API should be put in a common loader package.
4623 get_put_messages (
4624 p_msg_count => l_msg_count
4625 ,p_msg_data => l_msg_data
4626 );
4627 raise e_post_process;
4628 end if;
4629
4630 commit;
4631
4632 -- Set the final execution status message in the Log File
4633 if (p_exec_status_code = g_exec_status_success) then
4634 FEM_ENGINES_PKG.user_message (
4635 p_app_name => G_FEM
4636 ,p_msg_name => G_EXEC_SUCCESS
4637 );
4638 else
4639 FEM_ENGINES_PKG.user_message (
4640 p_app_name => G_FEM
4641 ,p_msg_name => G_EXEC_RERUN
4642 );
4643 end if;
4644
4645 FEM_ENGINES_PKG.tech_message (
4646 p_severity => g_log_level_2
4647 ,p_module => G_BLOCK||'.'||l_api_name
4648 ,p_msg_text => 'END'
4649 );
4650
4651 EXCEPTION
4652
4653 when e_post_process then
4654
4655 FEM_ENGINES_PKG.tech_message(
4656 p_severity => g_log_level_6
4657 ,p_module => G_BLOCK||'.'||l_api_name
4658 ,p_msg_text => 'Post Process Exception'
4659 );
4660
4661 FEM_ENGINES_PKG.user_message (
4662 p_app_name => G_FEM
4663 ,p_msg_name => G_EXT_LDR_POST_PROC_ERR
4664 );
4665
4666 -- Set the final execution status message in the Log File to RERUN
4667 FEM_ENGINES_PKG.user_message (
4668 p_app_name => G_FEM
4669 ,p_msg_name => G_EXEC_RERUN
4670 );
4671
4672 -- Set the Concurrent Request status to ERROR
4673 l_completion_status := FND_CONCURRENT.set_completion_status('ERROR',null);
4674
4675 END eng_master_post_proc;
4676
4677
4678
4679 /*===========================================================================+
4680 | PROCEDURE
4681 | GET_PUT_MESSAGES
4682 |
4683 | DESCRIPTION
4684 | Copied from FEM_DATAX_LOADER_PKG. Will be replaced when GET_PUT_MESSAGES
4685 | is placed in the common loader package.
4686 |
4687 | SCOPE - PRIVATE
4688 |
4689 +===========================================================================*/
4690
4691 PROCEDURE get_put_messages (
4692 p_msg_count in number
4693 ,p_msg_data in varchar2
4694 )
4695 IS
4696
4697 l_msg_count t_msg_count%TYPE;
4698 l_msg_data t_msg_data%TYPE;
4699 l_msg_out t_msg_count%TYPE;
4700 l_message t_msg_data%TYPE;
4701
4702 l_api_name constant varchar2(80) := 'get_put_messages';
4703
4704 BEGIN
4705
4706 FEM_ENGINES_PKG.tech_message (
4707 p_severity => g_log_level_2
4708 ,p_module => G_BLOCK||'.'||l_api_name
4709 ,p_msg_text => 'msg_count='||p_msg_count
4710 );
4711
4712 l_msg_data := p_msg_data;
4713
4714 if (p_msg_count = 1) then
4715
4716 FND_MESSAGE.set_encoded(l_msg_data);
4717 l_message := FND_MESSAGE.get;
4718
4719 FEM_ENGINES_PKG.user_message(
4720 p_msg_text => l_message
4721 );
4722
4723 FEM_ENGINES_PKG.tech_message (
4724 p_severity => g_log_level_2
4725 ,p_module => G_BLOCK||'.'||l_api_name
4726 ,p_msg_text => 'msg_data='||l_message
4727 );
4728
4729 elsif (p_msg_count > 1) then
4730
4731 for i in 1..p_msg_count loop
4732
4733 FND_MSG_PUB.get (
4734 p_msg_index => i
4735 ,p_encoded => FND_API.G_FALSE
4736 ,p_data => l_message
4737 ,p_msg_index_out => l_msg_out
4738 );
4739
4740 FEM_ENGINES_PKG.user_message (
4741 p_msg_text => l_message
4742 );
4743
4744 FEM_ENGINES_PKG.tech_message (
4745 p_severity => g_log_level_2
4746 ,p_module => G_BLOCK||'.'||l_api_name
4747 ,p_msg_text => 'msg_data='||l_message
4748 );
4749
4750 end loop;
4751
4752 end if;
4753
4754 FND_MSG_PUB.Initialize;
4755
4756 END get_put_messages;
4757
4758
4759
4760 /*===========================================================================+
4761 | PROCEDURE
4762 | GET_DEFAULT_START_DATE
4763 |
4764 | DESCRIPTION
4765 | Gets the default start date.
4766 |
4767 | SCOPE - PRIVATE
4768 |
4769 +===========================================================================*/
4770
4771 FUNCTION get_default_start_date
4772 RETURN date
4773 IS
4774
4775 l_api_name constant varchar2(80) := 'get_default_start_date';
4776
4777 BEGIN
4778
4779 if (gv_default_start_date is null) then
4780 gv_default_start_date := FEM_BUSINESS_RULE_PVT.GetDefaultStartDate;
4781 end if;
4782
4783 return gv_default_start_date;
4784
4785 END get_default_start_date;
4786
4787
4788
4789 /*===========================================================================+
4790 | PROCEDURE
4791 | GET_DEFAULT_END_DATE
4792 |
4793 | DESCRIPTION
4794 | Gets the default end date.
4795 |
4796 | SCOPE - PRIVATE
4797 |
4798 +===========================================================================*/
4799
4800 FUNCTION get_default_end_date
4801 RETURN date
4802 IS
4803
4804 l_api_name constant varchar2(80) := 'get_default_end_date';
4805
4806 BEGIN
4807
4808 if (gv_default_end_date is null) then
4809 gv_default_end_date := FEM_BUSINESS_RULE_PVT.GetDefaultEndDate;
4810 end if;
4811
4812 return gv_default_end_date;
4813
4814 END get_default_end_date;
4815
4816
4817
4818 /*===========================================================================+
4819 | PROCEDURE
4820 | SET_HIER_TABLE_ERR_MSG
4821 |
4822 | DESCRIPTION
4823 | Set a user message to indicate that a hierarchy validition exception
4824 | occurred in the specified hierarchy interface table. Users will have to
4825 | check the STATUS column of the specified table for details on the
4826 | validation exception.
4827 |
4828 | SCOPE - PRIVATE
4829 |
4830 +===========================================================================*/
4831
4832 PROCEDURE set_hier_table_err_msg (
4833 p_hier_table_name in varchar2
4834 ,p_status in varchar2
4835 )
4836 IS
4837
4838 l_message_name varchar2(30);
4839
4840 BEGIN
4841
4842 l_message_name :=
4843 case p_status
4844 when 'CHILD_WITH_MULTIPLE_PARENTS' then G_HIER_LDR_MULTI_PARENT_ERR
4845 when 'CIRCULAR_HIERARCHY' then G_HIER_LDR_CIRC_HIER_ERR
4846 when 'GROUP_SEQ_RULE_VIOLATED' then G_HIER_LDR_GRP_SEQ_RULE_ERR
4847 when 'INVALID_CALENDAR' then G_HIER_LDR_INV_CALENDAR_ERR
4848 when 'INVALID_DIMENSION_GROUP' then G_EXT_LDR_INV_DIM_GRP_ERR
4849 when 'INVALID_MEMBER' then G_EXT_LDR_INV_MEMBER_ERR
4850 when 'INVALID_ROOT_NODE' then G_HIER_LDR_INV_ROOT_NODE_ERR
4851 when 'INVALID_RECONCILIATION_LEAF' then G_HIER_LDR_RECON_LEAF_ERR
4852 when 'INVALID_RECONCILIATION_NODE' then G_HIER_LDR_RECON_NODE_ERR
4853 when 'INVALID_VALUE_SET' then G_EXT_LDR_INV_VALUE_SET_ERR
4854 when 'MULTIPLE_TOP' then G_HIER_LDR_MULTI_TOP_ERR
4855 when 'MULTIPLE_VALUE_SETS' then G_HIER_LDR_MULTI_VS_ERR
4856 end;
4857
4858 if (l_message_name is not null) then
4859 FND_MESSAGE.Set_Name(G_FEM,l_message_name);
4860 end if;
4861
4862 FEM_ENGINES_PKG.user_message (
4863 p_app_name => G_FEM
4864 ,p_msg_name => G_HIER_LDR_HIER_DETAILS_ERR
4865 ,p_token1 => 'HIER_TABLE_NAME'
4866 ,p_value1 => p_hier_table_name
4867 ,p_token2 => 'ERROR_MESSAGE_TEXT'
4868 ,p_value2 => FND_MESSAGE.Get
4869 ,p_token3 => 'STATUS_CODE'
4870 ,p_value3 => p_status
4871 );
4872
4873 END set_hier_table_err_msg;
4874
4875
4876 /******************************************************************************/
4877
4878 PROCEDURE bld_bad_value_sets_stmt (
4879 p_dimension_varchar_label in varchar2
4880 ,p_execution_mode in varchar2
4881 ,p_value_set_required_flag in varchar2
4882 ,x_bad_value_sets_stmt out nocopy varchar2
4883 )
4884 IS
4885
4886 l_api_name constant varchar2(30) := 'bld_bad_value_sets_stmt';
4887 l_status_clause varchar2(100) := '';
4888
4889 BEGIN
4890
4891 FEM_ENGINES_PKG.tech_message (
4892 p_severity => g_log_level_2
4893 ,p_module => G_BLOCK||'.'||l_api_name
4894 ,p_msg_text => 'BEGIN'
4895 );
4896
4897 if (p_execution_mode = g_snapshot) then
4898 l_status_clause := ' and hvst.status = ''LOAD''';
4899 end if;
4900
4901 if (p_dimension_varchar_label = 'CAL_PERIOD') then
4902
4903 x_bad_value_sets_stmt := '';
4904
4905 else
4906
4907 if (p_value_set_required_flag = 'Y') then
4908
4909 x_bad_value_sets_stmt :=
4910 ' select hvst.rowid'||
4911 ' ,''INVALID_VALUE_SET'''||
4912 ' from fem_hier_value_sets_t hvst'||
4913 ' where hvst.hierarchy_object_name = :b_hierarchy_object_name'||
4914 l_status_clause||
4915 ' and hvst.language = userenv(''LANG'')'||
4916 ' and not exists ('||
4917 ' select 1'||
4918 ' from fem_value_sets_b vs'||
4919 ' where vs.value_set_display_code = hvst.value_set_display_code'||
4920 ' and vs.dimension_id = :b_dimension_id'||
4921 ' )';
4922
4923 else
4924
4925 x_bad_value_sets_stmt := '';
4926
4927 end if;
4928
4929 end if;
4930
4931 FEM_ENGINES_PKG.tech_message (
4932 p_severity => g_log_level_2
4933 ,p_module => G_BLOCK||'.'||l_api_name
4934 ,p_msg_text => 'END'
4935 );
4936
4937 END bld_bad_value_sets_stmt;
4938
4939 --------------------------------------------------------------------------------
4940
4941 PROCEDURE bld_bad_dim_groups_stmt (
4942 p_dimension_varchar_label in varchar2
4943 ,p_execution_mode in varchar2
4944 ,x_bad_dim_groups_stmt out nocopy varchar2
4945 )
4946 IS
4947
4948 l_api_name constant varchar2(30) := 'bld_bad_dim_groups_stmt';
4949 l_status_clause varchar2(100) := '';
4950
4951 BEGIN
4952
4953 FEM_ENGINES_PKG.tech_message (
4954 p_severity => g_log_level_2
4955 ,p_module => G_BLOCK||'.'||l_api_name
4956 ,p_msg_text => 'BEGIN'
4957 );
4958
4959 if (p_execution_mode = g_snapshot) then
4960 l_status_clause := ' and hdgt.status = ''LOAD''';
4961 end if;
4962
4963 x_bad_dim_groups_stmt :=
4964 ' select hdgt.rowid'||
4965 ' ,''INVALID_DIMENSION_GROUP'''||
4966 ' from fem_hier_dim_grps_t hdgt'||
4967 ' where hdgt.hierarchy_object_name = :b_hierarchy_object_name'||
4968 l_status_clause||
4969 ' and hdgt.language = userenv(''LANG'')'||
4970 ' and not exists ('||
4971 ' select 1'||
4972 ' from fem_dimension_grps_b dg'||
4973 ' where dg.dimension_group_display_code = hdgt.dimension_group_display_code'||
4974 ' and dg.dimension_id = :b_dimension_id'||
4975 ' )';
4976
4977 FEM_ENGINES_PKG.tech_message (
4978 p_severity => g_log_level_2
4979 ,p_module => G_BLOCK||'.'||l_api_name
4980 ,p_msg_text => 'END'
4981 );
4982
4983 END bld_bad_dim_groups_stmt;
4984
4985 --------------------------------------------------------------------------------
4986
4987 PROCEDURE bld_bad_hier_calendars_stmt (
4988 p_dimension_varchar_label in varchar2
4989 ,p_execution_mode in varchar2
4990 ,p_source_hier_table in varchar2
4991 ,x_bad_hier_calendars_stmt out nocopy varchar2
4992 )
4993 IS
4994
4995 l_api_name constant varchar2(30) := 'bld_bad_hier_calendars_stmt';
4996 l_status_clause varchar2(100) := '';
4997
4998 BEGIN
4999
5000 FEM_ENGINES_PKG.tech_message (
5001 p_severity => g_log_level_2
5002 ,p_module => G_BLOCK||'.'||l_api_name
5003 ,p_msg_text => 'BEGIN'
5004 );
5005
5006 if (p_execution_mode = g_snapshot) then
5007 l_status_clause := ' and ht.status = ''LOAD''';
5008 end if;
5009
5010 if (p_dimension_varchar_label = 'CAL_PERIOD') then
5011
5012 x_bad_hier_calendars_stmt :=
5013 ' select ht.rowid'||
5014 ' ,''INVALID_CALENDAR'''||
5015 ' from '||p_source_hier_table||' ht'||
5016 ' where ht.hierarchy_object_name = :b_hierarchy_object_name'||
5017 ' and ht.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
5018 ' and ht.calendar_display_code <> :b_calendar_display_code'||
5019 l_status_clause||
5020 ' and ht.language = userenv(''LANG'')';
5021
5022 else
5023
5024 x_bad_hier_calendars_stmt := '';
5025
5026 end if;
5027
5028 FEM_ENGINES_PKG.tech_message (
5029 p_severity => g_log_level_2
5030 ,p_module => G_BLOCK||'.'||l_api_name
5031 ,p_msg_text => 'END'
5032 );
5033
5034 END bld_bad_hier_calendars_stmt;
5035
5036 --------------------------------------------------------------------------------
5037
5038 PROCEDURE bld_bad_hier_value_sets_t_stmt (
5039 p_dimension_varchar_label in varchar2
5040 ,p_execution_mode in varchar2
5041 ,p_value_set_required_flag in varchar2
5042 ,p_source_hier_table in varchar2
5043 ,x_bad_hier_value_sets_t_stmt out nocopy varchar2
5044 )
5045 IS
5046
5047 l_api_name constant varchar2(30) := 'bld_bad_hier_value_sets_t_stmt';
5048 l_status_clause_1 varchar2(100) := '';
5049 l_status_clause_2 varchar2(100) := '';
5050
5051 BEGIN
5052
5053 FEM_ENGINES_PKG.tech_message (
5054 p_severity => g_log_level_2
5055 ,p_module => G_BLOCK||'.'||l_api_name
5056 ,p_msg_text => 'BEGIN'
5057 );
5058
5059 if (p_execution_mode = g_snapshot) then
5060 l_status_clause_1 := ' and ht.status = ''LOAD''';
5061 l_status_clause_2 := ' and hvst.status = ''LOAD''';
5062 end if;
5063
5064 if (p_dimension_varchar_label = 'CAL_PERIOD') then
5065
5066 x_bad_hier_value_sets_t_stmt := '';
5067
5068 else
5069
5070 if (p_value_set_required_flag = 'Y') then
5071
5072 x_bad_hier_value_sets_t_stmt :=
5073 ' select ht.rowid'||
5074 ' ,''INVALID_VALUE_SET'''||
5075 ' from '||p_source_hier_table||' ht'||
5076 ' where ht.hierarchy_object_name = :b_hierarchy_object_name'||
5077 ' and ht.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
5078 l_status_clause_1||
5079 ' and ht.language = userenv(''LANG'')'||
5080 ' and ('||
5081 ' not exists ('||
5082 ' select 1'||
5083 ' from fem_hier_value_sets_t hvst'||
5084 ' where hvst.hierarchy_object_name = ht.hierarchy_object_name'||
5085 ' and hvst.value_set_display_code = ht.parent_value_set_display_code'||
5086 l_status_clause_2||
5087 ' and hvst.language = userenv(''LANG'')'||
5088 ' )'||
5089 ' or'||
5090 ' not exists ('||
5091 ' select 1'||
5092 ' from fem_hier_value_sets_t hvst'||
5093 ' where hvst.hierarchy_object_name = ht.hierarchy_object_name'||
5094 ' and hvst.value_set_display_code = ht.child_value_set_display_code'||
5095 l_status_clause_2||
5096 ' and hvst.language = userenv(''LANG'')'||
5097 ' )'||
5098 ' )';
5099
5100 else
5101
5102 x_bad_hier_value_sets_t_stmt := '';
5103
5104 end if;
5105
5106 end if;
5107
5108 FEM_ENGINES_PKG.tech_message (
5109 p_severity => g_log_level_2
5110 ,p_module => G_BLOCK||'.'||l_api_name
5111 ,p_msg_text => 'END'
5112 );
5113
5114 END bld_bad_hier_value_sets_t_stmt;
5115
5116 --------------------------------------------------------------------------------
5117
5118 PROCEDURE bld_bad_hier_value_sets_stmt (
5119 p_dimension_varchar_label in varchar2
5120 ,p_execution_mode in varchar2
5121 ,p_value_set_required_flag in varchar2
5122 ,p_source_hier_table in varchar2
5123 ,x_bad_hier_value_sets_stmt out nocopy varchar2
5124 )
5125 IS
5126
5127 l_api_name constant varchar2(30) := 'bld_bad_hier_value_sets_stmt';
5128 l_status_clause varchar2(100) := '';
5129
5130 BEGIN
5131
5132 FEM_ENGINES_PKG.tech_message (
5133 p_severity => g_log_level_2
5134 ,p_module => G_BLOCK||'.'||l_api_name
5135 ,p_msg_text => 'BEGIN'
5136 );
5137
5138 if (p_execution_mode = g_snapshot) then
5139 l_status_clause := ' and ht.status = ''LOAD''';
5140 end if;
5141
5142 if (p_dimension_varchar_label = 'CAL_PERIOD') then
5143
5144 x_bad_hier_value_sets_stmt := '';
5145
5146 else
5147
5148 if (p_value_set_required_flag = 'Y') then
5149
5150 x_bad_hier_value_sets_stmt :=
5151 ' select ht.rowid'||
5152 ' ,''INVALID_VALUE_SET'''||
5153 ' from '||p_source_hier_table||' ht'||
5154 ' where ht.hierarchy_object_name = :b_hierarchy_object_name'||
5155 ' and ht.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
5156 l_status_clause||
5157 ' and ht.language = userenv(''LANG'')'||
5158 ' and ('||
5159 ' not exists ('||
5160 ' select 1'||
5161 ' from fem_hier_value_sets hvs'||
5162 ' ,fem_value_sets_b vsb'||
5163 ' where hvs.hierarchy_obj_id = :b_hierarchy_object_id'||
5164 ' and vsb.value_set_id = hvs.value_set_id'||
5165 ' and vsb.value_set_display_code = ht.parent_value_set_display_code'||
5166 ' )'||
5167 ' or'||
5168 ' not exists ('||
5169 ' select 1'||
5170 ' from fem_hier_value_sets hvs'||
5171 ' ,fem_value_sets_b vsb'||
5172 ' where hvs.hierarchy_obj_id = :b_hierarchy_object_id'||
5173 ' and vsb.value_set_id = hvs.value_set_id'||
5174 ' and vsb.value_set_display_code = ht.child_value_set_display_code'||
5175 ' )'||
5176 ' )';
5177
5178 else
5179
5180 x_bad_hier_value_sets_stmt := '';
5181
5182 end if;
5183
5184 end if;
5185
5186 FEM_ENGINES_PKG.tech_message (
5187 p_severity => g_log_level_2
5188 ,p_module => G_BLOCK||'.'||l_api_name
5189 ,p_msg_text => 'END'
5190 );
5191
5192 END bld_bad_hier_value_sets_stmt;
5193
5194 --------------------------------------------------------------------------------
5195
5196 PROCEDURE bld_bad_hier_multi_vs_stmt (
5197 p_dimension_varchar_label in varchar2
5198 ,p_execution_mode in varchar2
5199 ,p_value_set_required_flag in varchar2
5200 ,p_source_hier_table in varchar2
5201 ,x_bad_hier_multi_vs_stmt out nocopy varchar2
5202 )
5203 IS
5204
5205 l_api_name constant varchar2(30) := 'bld_bad_hier_multi_vs_stmt';
5206 l_status_clause_1 varchar2(100) := '';
5207 l_status_clause_2 varchar2(100) := '';
5208
5209 BEGIN
5210
5211 FEM_ENGINES_PKG.tech_message (
5212 p_severity => g_log_level_2
5213 ,p_module => G_BLOCK||'.'||l_api_name
5214 ,p_msg_text => 'BEGIN'
5215 );
5216
5217 if (p_execution_mode = g_snapshot) then
5218 l_status_clause_1 := ' and ht.status = ''LOAD''';
5219 l_status_clause_2 := ' and hvst.status = ''LOAD''';
5220 end if;
5221
5222 if (p_dimension_varchar_label = 'CAL_PERIOD') then
5223
5224 x_bad_hier_multi_vs_stmt := '';
5225
5226 else
5227
5228 if (p_value_set_required_flag = 'Y') then
5229
5230 x_bad_hier_multi_vs_stmt :=
5231 ' select ht.rowid'||
5232 ' ,''INVALID_VALUE_SET'''||
5233 ' from '||p_source_hier_table||' ht'||
5234 ' where ht.hierarchy_object_name = :b_hierarchy_object_name'||
5235 ' and ht.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
5236 l_status_clause_1||
5237 ' and ht.language = userenv(''LANG'')'||
5238 ' and ('||
5239 ' ('||
5240 ' not exists ('||
5241 ' select 1'||
5242 ' from fem_hier_value_sets hvs'||
5243 ' ,fem_value_sets_b vsb'||
5244 ' where hvs.hierarchy_obj_id = :b_hierarchy_object_id'||
5245 ' and vsb.value_set_id = hvs.value_set_id'||
5246 ' and vsb.value_set_display_code = ht.parent_value_set_display_code'||
5247 ' )'||
5248 ' and'||
5249 ' not exists ('||
5250 ' select 1'||
5251 ' from fem_hier_value_sets_t hvst'||
5252 ' where hvst.hierarchy_object_name = ht.hierarchy_object_name'||
5253 ' and hvst.value_set_display_code = ht.parent_value_set_display_code'||
5254 l_status_clause_2||
5255 ' and hvst.language = userenv(''LANG'')'||
5256 ' )'||
5257 ' )'||
5258 ' or ('||
5259 ' not exists ('||
5260 ' select 1'||
5261 ' from fem_hier_value_sets hvs'||
5262 ' ,fem_value_sets_b vsb'||
5263 ' where hvs.hierarchy_obj_id = :b_hierarchy_object_id'||
5264 ' and vsb.value_set_id = hvs.value_set_id'||
5265 ' and vsb.value_set_display_code = ht.child_value_set_display_code'||
5266 ' )'||
5267 ' and'||
5268 ' not exists ('||
5269 ' select 1'||
5270 ' from fem_hier_value_sets_t hvst'||
5271 ' where hvst.hierarchy_object_name = ht.hierarchy_object_name'||
5272 ' and hvst.value_set_display_code = ht.child_value_set_display_code'||
5273 l_status_clause_2||
5274 ' and hvst.language = userenv(''LANG'')'||
5275 ' )'||
5276 ' )'||
5277 ' )';
5278
5279 else
5280
5281 x_bad_hier_multi_vs_stmt := '';
5282
5283 end if;
5284
5285 end if;
5286
5287 FEM_ENGINES_PKG.tech_message (
5288 p_severity => g_log_level_2
5289 ,p_module => G_BLOCK||'.'||l_api_name
5290 ,p_msg_text => 'END'
5291 );
5292
5293 END bld_bad_hier_multi_vs_stmt;
5294
5295 --------------------------------------------------------------------------------
5296
5297 PROCEDURE bld_bad_hier_members_stmt (
5298 p_dimension_varchar_label in varchar2
5299 ,p_execution_mode in varchar2
5300 ,p_value_set_required_flag in varchar2
5301 ,p_source_hier_table in varchar2
5302 ,p_member_b_table in varchar2
5303 ,p_member_col in varchar2
5304 ,p_member_dc_col in varchar2
5305 ,x_bad_hier_members_stmt out nocopy varchar2
5306 )
5307 IS
5308
5309 l_api_name constant varchar2(30) := 'bld_bad_hier_members_stmt';
5310 l_status_clause varchar2(100) := '';
5311
5312 BEGIN
5313
5314 FEM_ENGINES_PKG.tech_message (
5315 p_severity => g_log_level_2
5316 ,p_module => G_BLOCK||'.'||l_api_name
5317 ,p_msg_text => 'BEGIN'
5318 );
5319
5320 if (p_execution_mode = g_snapshot) then
5321 l_status_clause := ' and ht.status = ''LOAD''';
5322 end if;
5323
5324 if (p_dimension_varchar_label = 'CAL_PERIOD') then
5325
5326 x_bad_hier_members_stmt :=
5327 ' select ht.rowid'||
5328 ' ,''INVALID_MEMBER'''||
5329 ' from '||p_source_hier_table||' ht'||
5330 ' where ht.hierarchy_object_name = :b_hierarchy_object_name'||
5331 ' and ht.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
5332 ' and ht.calendar_display_code = :b_calendar_display_code'||
5333 l_status_clause||
5334 ' and ht.language = userenv(''LANG'')'||
5335 ' and ('||
5336 ' not exists ('||
5337 ' select 1'||
5338 ' from fem_dimension_grps_b dg'||
5339 ' ,'||p_member_b_table||' b'||
5340 ' where dg.dimension_group_display_code = ht.parent_dim_grp_display_code'||
5341 ' and dg.dimension_id = :b_dimension_id'||
5342 ' and b.'||p_member_col||' = FEM_DIMENSION_UTIL_PKG.Generate_Member_ID(ht.parent_cal_period_end_date,ht.parent_cal_period_number,:b_calendar_id,dg.dimension_group_id)'||
5343 -- Bug 5083961 -- UNABLE TO CREATE HIERARCHIES WITH DISABLED MEMBERS
5344 -- ' and b.enabled_flag = ''Y'''||
5345 ' and b.personal_flag = ''N'''||
5346 ' )'||
5347 ' or'||
5348 ' not exists ('||
5349 ' select 1'||
5350 ' from fem_dimension_grps_b dg'||
5351 ' ,'||p_member_b_table||' b'||
5352 ' where dg.dimension_group_display_code = ht.child_dim_grp_display_code'||
5353 ' and dg.dimension_id = :b_dimension_id'||
5354 ' and b.'||p_member_col||' = FEM_DIMENSION_UTIL_PKG.Generate_Member_ID(ht.child_cal_period_end_date,ht.child_cal_period_number,:b_calendar_id,dg.dimension_group_id)'||
5355 -- Bug 5083961 -- UNABLE TO CREATE HIERARCHIES WITH DISABLED MEMBERS
5356 --' and b.enabled_flag = ''Y'''||
5357 ' and b.personal_flag = ''N'''||
5358 ' )'||
5359 ' )';
5360
5361 else
5362
5363 if (p_value_set_required_flag = 'Y') then
5364
5365 x_bad_hier_members_stmt :=
5366 ' select ht.rowid'||
5367 ' ,''INVALID_MEMBER'''||
5368 ' from '||p_source_hier_table||' ht'||
5369 ' where ht.hierarchy_object_name = :b_hierarchy_object_name'||
5370 ' and ht.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
5371 l_status_clause||
5372 ' and ht.language = userenv(''LANG'')'||
5373 ' and ('||
5374 ' not exists ('||
5375 ' select 1'||
5376 ' from '||p_member_b_table||' b'||
5377 ' ,fem_value_sets_b vs'||
5378 ' where b.'||p_member_dc_col||' = ht.parent_display_code'||
5379 ' and b.value_set_id = vs.value_set_id'||
5380 -- Bug 5083961 -- UNABLE TO CREATE HIERARCHIES WITH DISABLED MEMBERS
5381 -- ' and b.enabled_flag = ''Y'''||
5382 ' and b.personal_flag = ''N'''||
5383 ' and vs.value_set_display_code = ht.parent_value_set_display_code'||
5384 ' and vs.dimension_id = :b_dimension_id'||
5385 ' )'||
5386 ' or'||
5387 ' not exists ('||
5388 ' select 1'||
5389 ' from '||p_member_b_table||' b'||
5390 ' ,fem_value_sets_b vs'||
5391 ' where b.'||p_member_dc_col||' = ht.child_display_code'||
5392 ' and b.value_set_id = vs.value_set_id'||
5393 -- Bug 5083961 -- UNABLE TO CREATE HIERARCHIES WITH DISABLED MEMBERS
5394 -- ' and b.enabled_flag = ''Y'''||
5395 ' and b.personal_flag = ''N'''||
5396 ' and vs.value_set_display_code = ht.child_value_set_display_code'||
5397 ' and vs.dimension_id = :b_dimension_id'||
5398 ' )'||
5399 ' )';
5400
5401 else
5402
5403 x_bad_hier_members_stmt :=
5404 ' select ht.rowid'||
5405 ' ,''INVALID_MEMBER'''||
5406 ' from '||p_source_hier_table||' ht'||
5407 ' where ht.hierarchy_object_name = :b_hierarchy_object_name'||
5408 ' and ht.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
5409 l_status_clause||
5410 ' and ht.language = userenv(''LANG'')'||
5411 ' and ('||
5412 ' not exists ('||
5413 ' select 1'||
5414 ' from '||p_member_b_table||' b'||
5415 ' where b.'||p_member_dc_col||' = ht.parent_display_code'||
5416 -- Bug 5083961 -- UNABLE TO CREATE HIERARCHIES WITH DISABLED MEMBERS
5417 -- ' and b.enabled_flag = ''Y'''||
5418 ' and b.personal_flag = ''N'''||
5419 ' )'||
5420 ' or'||
5421 ' not exists ('||
5422 ' select 1'||
5423 ' from '||p_member_b_table||' b'||
5424 ' where b.'||p_member_dc_col||' = ht.child_display_code'||
5425 -- Bug 5083961 -- UNABLE TO CREATE HIERARCHIES WITH DISABLED MEMBERS
5426 -- ' and b.enabled_flag = ''Y'''||
5427 ' and b.personal_flag = ''N'''||
5428 ' )'||
5429 ' )';
5430
5431 end if;
5432
5433 end if;
5434
5435 FEM_ENGINES_PKG.tech_message (
5436 p_severity => g_log_level_2
5437 ,p_module => G_BLOCK||'.'||l_api_name
5438 ,p_msg_text => 'END'
5439 );
5440
5441 END bld_bad_hier_members_stmt;
5442
5443 --------------------------------------------------------------------------------
5444
5445 PROCEDURE bld_bad_hier_dups_stmt (
5446 p_dimension_varchar_label in varchar2
5447 ,p_execution_mode in varchar2
5448 ,p_value_set_required_flag in varchar2
5449 ,p_source_hier_table in varchar2
5450 ,x_bad_hier_dups_stmt out nocopy varchar2
5451 )
5452 IS
5453
5454 l_api_name constant varchar2(30) := 'bld_bad_hier_dups_stmt';
5455 l_status_clause varchar2(100) := '';
5456
5457 BEGIN
5458
5459 FEM_ENGINES_PKG.tech_message (
5460 p_severity => g_log_level_2
5461 ,p_module => G_BLOCK||'.'||l_api_name
5462 ,p_msg_text => 'BEGIN'
5463 );
5464
5465 if (p_execution_mode = g_snapshot) then
5466 l_status_clause := ' and ht.status = ''LOAD''';
5467 end if;
5468
5469 if (p_dimension_varchar_label = 'CAL_PERIOD') then
5470
5471 x_bad_hier_dups_stmt :=
5472 ' select null'||
5473 ' ,null'||
5474 ' ,ht.child_dim_grp_display_code'||
5475 ' ,ht.child_cal_period_end_date'||
5476 ' ,ht.child_cal_period_number'||
5477 ' ,''CHILD_WITH_MULTIPLE_PARENTS'''||
5478 ' from '||p_source_hier_table||' ht'||
5479 ' where ht.hierarchy_object_name = :b_hierarchy_object_name'||
5480 ' and ht.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
5481 ' and ht.calendar_display_code = :b_calendar_display_code'||
5482 l_status_clause||
5483 ' and ht.language = userenv(''LANG'')'||
5484 ' and not ('||
5485 ' ht.parent_dim_grp_display_code = ht.child_dim_grp_display_code'||
5486 ' and ht.parent_cal_period_end_date = ht.child_cal_period_end_date'||
5487 ' and ht.parent_cal_period_number = ht.child_cal_period_number'||
5488 ' )'||
5489 ' group by ht.child_dim_grp_display_code'||
5490 ' ,ht.child_cal_period_end_date'||
5491 ' ,ht.child_cal_period_number'||
5492 ' having count(ht.parent_cal_period_number) > 1';
5493
5494 else
5495
5496 if (p_value_set_required_flag = 'Y') then
5497
5498 x_bad_hier_dups_stmt :=
5499 ' select ht.child_display_code'||
5500 ' ,ht.child_value_set_display_code'||
5501 ' ,null'||
5502 ' ,null'||
5503 ' ,null'||
5504 ' ,''CHILD_WITH_MULTIPLE_PARENTS'''||
5505 ' from '||p_source_hier_table||' ht'||
5506 ' where ht.hierarchy_object_name = :b_hierarchy_object_name'||
5507 ' and ht.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
5508 l_status_clause||
5509 ' and ht.language = userenv(''LANG'')'||
5510 ' and not ('||
5511 ' ht.parent_display_code = ht.child_display_code'||
5512 ' and ht.parent_value_set_display_code = ht.child_value_set_display_code'||
5513 ' )'||
5514 ' group by ht.child_display_code'||
5515 ' ,ht.child_value_set_display_code'||
5516 ' having count(ht.parent_display_code) > 1';
5517
5518 else
5519
5520 x_bad_hier_dups_stmt :=
5521 ' select ht.child_display_code'||
5522 ' ,null'||
5523 ' ,null'||
5524 ' ,null'||
5525 ' ,null'||
5526 ' ,''CHILD_WITH_MULTIPLE_PARENTS'''||
5527 ' from '||p_source_hier_table||' ht'||
5528 ' where ht.hierarchy_object_name = :b_hierarchy_object_name'||
5529 ' and ht.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
5530 l_status_clause||
5531 ' and ht.language = userenv(''LANG'')'||
5532 ' and ht.parent_display_code <> ht.child_display_code'||
5533 ' group by ht.child_display_code'||
5534 ' having count(ht.parent_display_code) > 1';
5535
5536 end if;
5537
5538 end if;
5539
5540 FEM_ENGINES_PKG.tech_message (
5541 p_severity => g_log_level_2
5542 ,p_module => G_BLOCK||'.'||l_api_name
5543 ,p_msg_text => 'END'
5544 );
5545
5546 END bld_bad_hier_dups_stmt;
5547
5548 --------------------------------------------------------------------------------
5549
5550 PROCEDURE bld_bad_hier_rec_leafs_stmt (
5551 p_dimension_varchar_label in varchar2
5552 ,p_target_hierval_table in varchar2
5553 ,p_member_attr_table in varchar2
5554 ,p_member_col in varchar2
5555 ,x_bad_hier_rec_leafs_stmt out nocopy varchar2
5556 )
5557 IS
5558
5559 l_api_name constant varchar2(30) := 'bld_bad_hier_rec_leafs_stmt';
5560
5561 BEGIN
5562
5563 FEM_ENGINES_PKG.tech_message (
5564 p_severity => g_log_level_2
5565 ,p_module => G_BLOCK||'.'||l_api_name
5566 ,p_msg_text => 'BEGIN'
5567 );
5568
5569 if (p_dimension_varchar_label = 'CAL_PERIOD') then
5570
5571 x_bad_hier_rec_leafs_stmt :=
5572 ' select leaf.row_id'||
5573 ' ,''INVALID_RECONCILIATION_LEAF'''||
5574 ' from ('||
5575 ' select root.source_hier_t_rowid as row_id'||
5576 ' ,root.child_id'||
5577 ' from '||p_target_hierval_table||' root'||
5578 ' where root.request_id = :b_request_id'||
5579 ' and root.child_depth_num <> 1'||
5580 ' and not exists ('||
5581 ' select 1'||
5582 ' from '||p_target_hierval_table||' parent'||
5583 ' where parent.request_id = root.request_id'||
5584 ' and parent.parent_id = root.child_id'||
5585 ' )'||
5586 ' ) leaf'||
5587 ' where not exists ('||
5588 ' select 1'||
5589 ' from '||p_member_attr_table||' attv'||
5590 ' where attv.'||p_member_col||' = leaf.child_id'||
5591 ' and attv.dim_attribute_varchar_member = ''Y'''||
5592 ' and attv.attribute_id = :b_attribute_id'||
5593 ' and attv.version_id = :b_attr_version_id'||
5594 ' )';
5595
5596 else
5597
5598 x_bad_hier_rec_leafs_stmt :=
5599 ' select leaf.row_id'||
5600 ' ,''INVALID_RECONCILIATION_LEAF'''||
5601 ' from ('||
5602 ' select root.source_hier_t_rowid as row_id'||
5603 ' ,root.child_id'||
5604 ' ,root.child_value_set_id'||
5605 ' from '||p_target_hierval_table||' root'||
5606 ' where root.request_id = :b_request_id'||
5607 ' and root.child_depth_num <> 1'||
5608 ' and not exists ('||
5609 ' select 1'||
5610 ' from '||p_target_hierval_table||' parent'||
5611 ' where parent.request_id = root.request_id'||
5612 ' and parent.parent_id = root.child_id'||
5613 ' and parent.parent_value_set_id = root.child_value_set_id'||
5614 ' )'||
5615 ' ) leaf'||
5616 ' where not exists ('||
5617 ' select 1'||
5618 ' from '||p_member_attr_table||' attv'||
5619 ' where attv.value_set_id = leaf.child_value_set_id'||
5620 ' and attv.'||p_member_col||' = leaf.child_id'||
5621 ' and attv.dim_attribute_varchar_member = ''Y'''||
5622 ' and attv.attribute_id = :b_attribute_id'||
5623 ' and attv.version_id = :b_attr_version_id'||
5624 ' )';
5625
5626 end if;
5627
5628 FEM_ENGINES_PKG.tech_message (
5629 p_severity => g_log_level_2
5630 ,p_module => G_BLOCK||'.'||l_api_name
5631 ,p_msg_text => 'END'
5632 );
5633
5634 END bld_bad_hier_rec_leafs_stmt;
5635
5636 --------------------------------------------------------------------------------
5637
5638 PROCEDURE bld_bad_hier_rec_nodes_stmt (
5639 p_dimension_varchar_label in varchar2
5640 ,p_target_hierval_table in varchar2
5641 ,p_member_attr_table in varchar2
5642 ,p_member_col in varchar2
5643 ,x_bad_hier_rec_nodes_stmt out nocopy varchar2
5644 )
5645 IS
5646
5647 l_api_name constant varchar2(30) := 'bld_bad_hier_rec_nodes_stmt';
5648
5649 BEGIN
5650
5651 FEM_ENGINES_PKG.tech_message (
5652 p_severity => g_log_level_2
5653 ,p_module => G_BLOCK||'.'||l_api_name
5654 ,p_msg_text => 'BEGIN'
5655 );
5656
5657 if (p_dimension_varchar_label = 'CAL_PERIOD') then
5658
5659 x_bad_hier_rec_nodes_stmt :=
5660 ' select node.row_id'||
5661 ' ,''INVALID_RECONCILIATION_NODE'''||
5662 ' from ('||
5663 ' select min(parent.source_hier_t_rowid) as row_id'||
5664 ' ,parent.parent_id'||
5665 ' from '||p_target_hierval_table||' parent'||
5666 ' where parent.request_id = :b_request_id'||
5667 ' and parent.child_depth_num <> 1'||
5668 ' group by parent.parent_id'||
5669 ' ) node'||
5670 ' where not exists ('||
5671 ' select 1'||
5672 ' from '||p_member_attr_table||' attv'||
5673 ' where attv.'||p_member_col||' = node.parent_id'||
5674 ' and attv.dim_attribute_varchar_member = ''N'''||
5675 ' and attv.attribute_id = :b_attribute_id'||
5676 ' and attv.version_id = :b_attr_version_id'||
5677 ' )';
5678
5679 else
5680
5681 x_bad_hier_rec_nodes_stmt :=
5682 ' select node.row_id'||
5683 ' ,''INVALID_RECONCILIATION_NODE'''||
5684 ' from ('||
5685 ' select min(parent.source_hier_t_rowid) as row_id'||
5686 ' ,parent.parent_id'||
5687 ' ,parent.parent_value_set_id'||
5688 ' from '||p_target_hierval_table||' parent'||
5689 ' where parent.request_id = :b_request_id'||
5690 ' and parent.child_depth_num <> 1'||
5691 ' group by parent.parent_id'||
5692 ' ,parent.parent_value_set_id'||
5693 ' ) node'||
5694 ' where not exists ('||
5695 ' select 1'||
5696 ' from '||p_member_attr_table||' attv'||
5697 ' where attv.value_set_id = node.parent_value_set_id'||
5698 ' and attv.'||p_member_col||' = node.parent_id'||
5699 ' and attv.dim_attribute_varchar_member = ''N'''||
5700 ' and attv.attribute_id = :b_attribute_id'||
5701 ' and attv.version_id = :b_attr_version_id'||
5702 ' )';
5703
5704 end if;
5705
5706 FEM_ENGINES_PKG.tech_message (
5707 p_severity => g_log_level_2
5708 ,p_module => G_BLOCK||'.'||l_api_name
5709 ,p_msg_text => 'END'
5710 );
5711
5712 END bld_bad_hier_rec_nodes_stmt;
5713
5714 --------------------------------------------------------------------------------
5715
5716 PROCEDURE bld_bad_hier_roots_stmt (
5717 p_dimension_varchar_label in varchar2
5718 ,p_execution_mode in varchar2
5719 ,p_value_set_required_flag in varchar2
5720 ,p_source_hier_table in varchar2
5721 ,p_member_b_table in varchar2
5722 ,p_member_col in varchar2
5723 ,p_member_dc_col in varchar2
5724 ,x_bad_hier_roots_stmt out nocopy varchar2
5725 )
5726 IS
5727
5728 l_api_name constant varchar2(30) := 'bld_bad_hier_roots_stmt';
5729 l_status_clause varchar2(100) := '';
5730
5731 BEGIN
5732
5733 FEM_ENGINES_PKG.tech_message (
5734 p_severity => g_log_level_2
5735 ,p_module => G_BLOCK||'.'||l_api_name
5736 ,p_msg_text => 'BEGIN'
5737 );
5738
5739 if (p_execution_mode = g_snapshot) then
5740 l_status_clause := ' and root.status = ''LOAD''';
5741 end if;
5742
5743 if (p_dimension_varchar_label = 'CAL_PERIOD') then
5744
5745 x_bad_hier_roots_stmt :=
5746 ' select root.rowid'||
5747 ' ,''INVALID_ROOT_NODE'''||
5748 ' from '||p_source_hier_table||' root'||
5749 ' ,'||p_member_b_table||' b'||
5750 ' ,fem_dimension_grps_b dg'||
5751 ' where root.child_dim_grp_display_code = root.parent_dim_grp_display_code'||
5752 ' and root.child_cal_period_end_date = root.parent_cal_period_end_date'||
5753 ' and root.child_cal_period_number = root.parent_cal_period_number'||
5754 ' and root.hierarchy_object_name = :b_hierarchy_object_name'||
5755 ' and root.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
5756 ' and root.calendar_display_code = :b_calendar_display_code'||
5757 l_status_clause||
5758 ' and root.language = userenv(''LANG'')'||
5759 ' and dg.dimension_group_display_code = root.parent_dim_grp_display_code'||
5760 ' and dg.dimension_id = :b_dimension_id'||
5761 ' and b.'||p_member_col||' = FEM_DIMENSION_UTIL_PKG.Generate_Member_ID(root.parent_cal_period_end_date,root.parent_cal_period_number,:b_calendar_id,dg.dimension_group_id)'||
5762 ' and exists ('||
5763 ' select 1'||
5764 ' from '||p_source_hier_table||' child'||
5765 ' where child.child_dim_grp_display_code = root.parent_dim_grp_display_code'||
5766 ' and child.child_cal_period_end_date = root.parent_cal_period_end_date'||
5767 ' and child.child_cal_period_number = root.parent_cal_period_number'||
5768 ' and child.hierarchy_object_name = root.hierarchy_object_name'||
5769 ' and child.hierarchy_obj_def_display_name = root.hierarchy_obj_def_display_name'||
5770 ' and child.calendar_display_code = root.calendar_display_code'||
5771 ' and child.status = root.status'||
5772 ' and child.language = root.language'||
5773 ' and not ('||
5774 ' child.parent_dim_grp_display_code = child.child_dim_grp_display_code'||
5775 ' and child.parent_cal_period_end_date = child.child_cal_period_end_date'||
5776 ' and child.parent_cal_period_number = child.child_cal_period_number'||
5777 ' )'||
5778 ' )';
5779
5780 else
5781
5782 if (p_value_set_required_flag = 'Y') then
5783
5784 x_bad_hier_roots_stmt :=
5785 ' select root.rowid'||
5786 ' ,''INVALID_ROOT_NODE'''||
5787 ' from '||p_source_hier_table||' root'||
5788 ' ,'||p_member_b_table||' b'||
5789 ' ,fem_value_sets_b vs'||
5790 ' where root.child_display_code = root.parent_display_code'||
5791 ' and root.child_value_set_display_code = root.parent_value_set_display_code'||
5792 ' and root.hierarchy_object_name = :b_hierarchy_object_name'||
5793 ' and root.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
5794 l_status_clause||
5795 ' and root.language = userenv(''LANG'')'||
5796 ' and vs.value_set_display_code = root.parent_value_set_display_code'||
5797 ' and vs.dimension_id = :b_dimension_id'||
5798 ' and b.value_set_id = vs.value_set_id'||
5799 ' and b.'||p_member_dc_col||' = root.parent_display_code'||
5800 ' and exists ('||
5801 ' select 1'||
5802 ' from '||p_source_hier_table||' child'||
5803 ' where child.child_display_code = root.parent_display_code'||
5804 ' and child.child_value_set_display_code = root.parent_value_set_display_code'||
5805 ' and child.hierarchy_object_name = root.hierarchy_object_name'||
5806 ' and child.hierarchy_obj_def_display_name = root.hierarchy_obj_def_display_name'||
5807 ' and child.status = root.status'||
5808 ' and child.language = root.language'||
5809 ' and not ('||
5810 ' child.parent_display_code = child.child_display_code'||
5811 ' and child.parent_value_set_display_code = child.child_value_set_display_code'||
5812 ' )'||
5813 ' )';
5814
5815 else
5816
5817 x_bad_hier_roots_stmt :=
5818 ' select root.rowid'||
5819 ' ,''INVALID_ROOT_NODE'''||
5820 ' from '||p_source_hier_table||' root'||
5821 ' ,'||p_member_b_table||' b'||
5822 ' where root.child_display_code = root.parent_display_code'||
5823 ' and root.hierarchy_object_name = :b_hierarchy_object_name'||
5824 ' and root.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
5825 l_status_clause||
5826 ' and root.language = userenv(''LANG'')'||
5827 ' and b.'||p_member_dc_col||' = root.parent_display_code'||
5828 ' and exists ('||
5829 ' select 1'||
5830 ' from '||p_source_hier_table||' child'||
5831 ' where child.child_display_code = root.parent_display_code'||
5832 ' and child.hierarchy_object_name = root.hierarchy_object_name'||
5833 ' and child.hierarchy_obj_def_display_name = root.hierarchy_obj_def_display_name'||
5834 ' and child.status = root.status'||
5835 ' and child.language = root.language'||
5836 ' and child.parent_display_code <> child.child_display_code'||
5837 ' )';
5838
5839 end if;
5840
5841 end if;
5842
5843 FEM_ENGINES_PKG.tech_message (
5844 p_severity => g_log_level_2
5845 ,p_module => G_BLOCK||'.'||l_api_name
5846 ,p_msg_text => 'END'
5847 );
5848
5849 END bld_bad_hier_roots_stmt;
5850
5851 --------------------------------------------------------------------------------
5852
5853 PROCEDURE bld_bad_hier_dim_groups_t_stmt (
5854 p_dimension_varchar_label in varchar2
5855 ,p_execution_mode in varchar2
5856 ,p_target_hierval_table in varchar2
5857 ,x_bad_hier_dim_groups_t_stmt out nocopy varchar2
5858 )
5859 IS
5860
5861 l_api_name constant varchar2(30) := 'bld_bad_hier_dim_groups_t_stmt';
5862 l_status_clause varchar2(100) := '';
5863
5864 BEGIN
5865
5866 FEM_ENGINES_PKG.tech_message (
5867 p_severity => g_log_level_2
5868 ,p_module => G_BLOCK||'.'||l_api_name
5869 ,p_msg_text => 'BEGIN'
5870 );
5871
5872 if (p_execution_mode = g_snapshot) then
5873 l_status_clause := ' and hdgt.status = ''LOAD''';
5874 end if;
5875
5876 x_bad_hier_dim_groups_t_stmt :=
5877 ' select hv.source_hier_t_rowid'||
5878 ' ,''GROUP_SEQ_RULE_VIOLATED'''||
5879 ' from '||p_target_hierval_table||' hv'||
5880 ' where hv.request_id = :b_request_id'||
5881 ' and not exists ('||
5882 ' select 1'||
5883 ' from fem_hier_dim_grps_t hdgt'||
5884 ' ,fem_dimension_grps_b dg'||
5885 ' where hdgt.hierarchy_object_name = :b_hierarchy_object_name'||
5886 l_status_clause||
5887 ' and hdgt.language = userenv(''LANG'')'||
5888 ' and dg.dimension_group_display_code = hdgt.dimension_group_display_code'||
5889 ' and dg.dimension_id = :b_dimension_id'||
5890 ' and dg.dimension_group_id = hv.child_dimension_grp_id'||
5891 ' )';
5892
5893 FEM_ENGINES_PKG.tech_message (
5894 p_severity => g_log_level_2
5895 ,p_module => G_BLOCK||'.'||l_api_name
5896 ,p_msg_text => 'END'
5897 );
5898
5899 END bld_bad_hier_dim_groups_t_stmt;
5900
5901 --------------------------------------------------------------------------------
5902
5903 PROCEDURE bld_bad_hier_dim_groups_stmt (
5904 p_dimension_varchar_label in varchar2
5905 ,p_target_hierval_table in varchar2
5906 ,x_bad_hier_dim_groups_stmt out nocopy varchar2
5907 )
5908 IS
5909
5910 l_api_name constant varchar2(30) := 'bld_bad_hier_dim_groups_stmt';
5911
5912 BEGIN
5913
5914 FEM_ENGINES_PKG.tech_message (
5915 p_severity => g_log_level_2
5916 ,p_module => G_BLOCK||'.'||l_api_name
5917 ,p_msg_text => 'BEGIN'
5918 );
5919
5920 x_bad_hier_dim_groups_stmt :=
5921 ' select hv.source_hier_t_rowid'||
5922 ' ,''GROUP_SEQ_RULE_VIOLATED'''||
5923 ' from '||p_target_hierval_table||' hv'||
5924 ' where hv.request_id = :b_request_id'||
5925 ' and not exists ('||
5926 ' select 1'||
5927 ' from fem_hier_dimension_grps hdg'||
5928 ' where hdg.hierarchy_obj_id = :b_hierarchy_object_id'||
5929 ' and hdg.dimension_group_id = hv.child_dimension_grp_id'||
5930 ' )';
5931
5932 FEM_ENGINES_PKG.tech_message (
5933 p_severity => g_log_level_2
5934 ,p_module => G_BLOCK||'.'||l_api_name
5935 ,p_msg_text => 'END'
5936 );
5937
5938 END bld_bad_hier_dim_groups_stmt;
5939
5940 --------------------------------------------------------------------------------
5941
5942 PROCEDURE bld_bad_hier_dim_grp_skp_stmt (
5943 p_dimension_varchar_label in varchar2
5944 ,p_target_hierval_table in varchar2
5945 ,x_bad_hier_dim_grp_skp_stmt out nocopy varchar2
5946 )
5947 IS
5948
5949 l_api_name constant varchar2(30) := 'bld_bad_hier_dim_grp_skp_stmt';
5950
5951 BEGIN
5952
5953 FEM_ENGINES_PKG.tech_message (
5954 p_severity => g_log_level_2
5955 ,p_module => G_BLOCK||'.'||l_api_name
5956 ,p_msg_text => 'BEGIN'
5957 );
5958
5959 if (p_dimension_varchar_label = 'CAL_PERIOD') then
5960
5961 x_bad_hier_dim_grp_skp_stmt :=
5962 ' select hv.source_hier_t_rowid'||
5963 ' ,''GROUP_SEQ_RULE_VIOLATED'''||
5964 ' from '||p_target_hierval_table||' hv'||
5965 ' ,fem_dimension_grps_b dgp'||
5966 ' ,fem_dimension_grps_b dgc'||
5967 ' where hv.request_id = :b_request_id'||
5968 ' and not ('||
5969 ' hv.parent_id = hv.child_id'||
5970 ' )'||
5971 ' and dgp.dimension_group_id = hv.parent_dimension_grp_id'||
5972 ' and dgc.dimension_group_id = hv.child_dimension_grp_id'||
5973 ' and dgp.dimension_group_seq >= dgc.dimension_group_seq';
5974
5975 else
5976
5977 x_bad_hier_dim_grp_skp_stmt :=
5978 ' select hv.source_hier_t_rowid'||
5979 ' ,''GROUP_SEQ_RULE_VIOLATED'''||
5980 ' from '||p_target_hierval_table||' hv'||
5981 ' ,fem_dimension_grps_b dgp'||
5982 ' ,fem_dimension_grps_b dgc'||
5983 ' where hv.request_id = :b_request_id'||
5984 ' and not ('||
5985 ' hv.parent_id = hv.child_id'||
5986 ' and hv.parent_value_set_id = hv.child_value_set_id'||
5987 ' )'||
5988 ' and dgp.dimension_group_id = hv.parent_dimension_grp_id'||
5989 ' and dgc.dimension_group_id = hv.child_dimension_grp_id'||
5990 ' and dgp.dimension_group_seq >= dgc.dimension_group_seq';
5991
5992 end if;
5993
5994 FEM_ENGINES_PKG.tech_message (
5995 p_severity => g_log_level_2
5996 ,p_module => G_BLOCK||'.'||l_api_name
5997 ,p_msg_text => 'END'
5998 );
5999
6000 END bld_bad_hier_dim_grp_skp_stmt;
6001
6002 --------------------------------------------------------------------------------
6003
6004 PROCEDURE bld_bad_hier_dim_grp_reg_stmt (
6005 p_dimension_varchar_label in varchar2
6006 ,p_target_hierval_table in varchar2
6007 ,x_bad_hier_dim_grp_reg_stmt out nocopy varchar2
6008 )
6009 IS
6010
6011 l_api_name constant varchar2(30) := 'bld_bad_hier_dim_grp_reg_stmt';
6012
6013 BEGIN
6014
6015 FEM_ENGINES_PKG.tech_message (
6016 p_severity => g_log_level_2
6017 ,p_module => G_BLOCK||'.'||l_api_name
6018 ,p_msg_text => 'BEGIN'
6019 );
6020
6021 x_bad_hier_dim_grp_reg_stmt :=
6022 ' select source_hier_t_rowid'||
6023 ' ,''GROUP_SEQ_RULE_VIOLATED'''||
6024 ' from '||p_target_hierval_table||
6025 ' where request_id = :b_request_id'||
6026 ' and child_depth_num = :b_depth_num'||
6027 ' and child_dimension_grp_id <> :b_dimension_group_id';
6028
6029 FEM_ENGINES_PKG.tech_message (
6030 p_severity => g_log_level_2
6031 ,p_module => G_BLOCK||'.'||l_api_name
6032 ,p_msg_text => 'END'
6033 );
6034
6035 END bld_bad_hier_dim_grp_reg_stmt;
6036
6037 --------------------------------------------------------------------------------
6038
6039 PROCEDURE bld_root_node_count_stmt (
6040 p_dimension_varchar_label in varchar2
6041 ,p_execution_mode in varchar2
6042 ,p_value_set_required_flag in varchar2
6043 ,p_source_hier_table in varchar2
6044 ,x_root_node_count_stmt out nocopy varchar2
6045 )
6046 IS
6047
6048 l_api_name constant varchar2(30) := 'bld_root_node_count_stmt';
6049 l_status_clause varchar2(100) := '';
6050
6051 BEGIN
6052
6053 FEM_ENGINES_PKG.tech_message (
6054 p_severity => g_log_level_2
6055 ,p_module => G_BLOCK||'.'||l_api_name
6056 ,p_msg_text => 'BEGIN'
6057 );
6058
6059 if (p_execution_mode = g_snapshot) then
6060 l_status_clause := ' and root.status = ''LOAD''';
6061 end if;
6062
6063 if (p_dimension_varchar_label = 'CAL_PERIOD') then
6064
6065 x_root_node_count_stmt :=
6066 ' select count(*)'||
6067 ' from '||p_source_hier_table||' root'||
6068 ' where root.child_dim_grp_display_code = root.parent_dim_grp_display_code'||
6069 ' and root.child_cal_period_end_date = root.parent_cal_period_end_date'||
6070 ' and root.child_cal_period_number = root.parent_cal_period_number'||
6071 ' and root.hierarchy_object_name = :b_hierarchy_object_name'||
6072 ' and root.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
6073 ' and root.calendar_display_code = :b_calendar_display_code'||
6074 l_status_clause||
6075 ' and root.language = userenv(''LANG'')';
6076
6077 else
6078
6079 if (p_value_set_required_flag = 'Y') then
6080
6081 x_root_node_count_stmt :=
6082 ' select count(*)'||
6083 ' from '||p_source_hier_table||' root'||
6084 ' where root.child_display_code = root.parent_display_code'||
6085 ' and root.child_value_set_display_code = root.parent_value_set_display_code'||
6086 ' and root.hierarchy_object_name = :b_hierarchy_object_name'||
6087 ' and root.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
6088 l_status_clause||
6089 ' and root.language = userenv(''LANG'')';
6090
6091 else
6092
6093 x_root_node_count_stmt :=
6094 ' select count(*)'||
6095 ' from '||p_source_hier_table||' root'||
6096 ' where root.child_display_code = root.parent_display_code'||
6097 ' and root.hierarchy_object_name = :b_hierarchy_object_name'||
6098 ' and root.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
6099 l_status_clause||
6100 ' and root.language = userenv(''LANG'')';
6101
6102 end if;
6103
6104 end if;
6105
6106 FEM_ENGINES_PKG.tech_message (
6107 p_severity => g_log_level_2
6108 ,p_module => G_BLOCK||'.'||l_api_name
6109 ,p_msg_text => 'END'
6110 );
6111
6112 END bld_root_node_count_stmt;
6113
6114 --------------------------------------------------------------------------------
6115
6116 PROCEDURE bld_get_value_sets_stmt (
6117 p_dimension_varchar_label in varchar2
6118 ,p_execution_mode in varchar2
6119 ,p_value_set_required_flag in varchar2
6120 ,x_get_value_sets_stmt out nocopy varchar2
6121 )
6122 IS
6123
6124 l_api_name constant varchar2(30) := 'bld_get_value_sets_stmt';
6125 l_status_clause varchar2(100) := '';
6126
6127 BEGIN
6128
6129 FEM_ENGINES_PKG.tech_message (
6130 p_severity => g_log_level_2
6131 ,p_module => G_BLOCK||'.'||l_api_name
6132 ,p_msg_text => 'BEGIN'
6133 );
6134
6135 if (p_execution_mode = g_snapshot) then
6136 l_status_clause := ' and hvst.status = ''LOAD''';
6137 end if;
6138
6139 if (p_dimension_varchar_label = 'CAL_PERIOD') then
6140
6141 x_get_value_sets_stmt := '';
6142
6143 else
6144
6145 if (p_value_set_required_flag = 'Y') then
6146
6147 x_get_value_sets_stmt :=
6148 ' select vs.value_set_id'||
6149 ' from fem_hier_value_sets_t hvst'||
6150 ' ,fem_value_sets_b vs'||
6151 ' where hvst.hierarchy_object_name = :b_hierarchy_object_name'||
6152 l_status_clause||
6153 ' and hvst.language = userenv(''LANG'')'||
6154 ' and vs.value_set_display_code = hvst.value_set_display_code'||
6155 ' and vs.dimension_id = :b_dimension_id';
6156
6157 else
6158
6159 x_get_value_sets_stmt := '';
6160
6161 end if;
6162
6163 end if;
6164
6165 FEM_ENGINES_PKG.tech_message (
6166 p_severity => g_log_level_2
6167 ,p_module => G_BLOCK||'.'||l_api_name
6168 ,p_msg_text => 'END'
6169 );
6170
6171 END bld_get_value_sets_stmt;
6172
6173 --------------------------------------------------------------------------------
6174
6175 PROCEDURE bld_get_dim_groups_t_stmt (
6176 p_dimension_varchar_label in varchar2
6177 ,p_execution_mode in varchar2
6178 ,x_get_dim_groups_t_stmt out nocopy varchar2
6179 )
6180 IS
6181
6182 l_api_name constant varchar2(30) := 'bld_get_dim_groups_t_stmt';
6183 l_status_clause varchar2(100) := '';
6184
6185 BEGIN
6186
6187 FEM_ENGINES_PKG.tech_message (
6188 p_severity => g_log_level_2
6189 ,p_module => G_BLOCK||'.'||l_api_name
6190 ,p_msg_text => 'BEGIN'
6191 );
6192
6193 if (p_execution_mode = g_snapshot) then
6194 l_status_clause := ' and hdgt.status = ''LOAD''';
6195 end if;
6196
6197 x_get_dim_groups_t_stmt :=
6198 ' select dimension_group_id'||
6199 ' ,rownum as depth_num'||
6200 ' from ('||
6201 ' select dg.dimension_group_id'||
6202 ' from fem_hier_dim_grps_t hdgt'||
6203 ' ,fem_dimension_grps_b dg'||
6204 ' where hdgt.hierarchy_object_name = :b_hierarchy_object_name'||
6205 l_status_clause||
6206 ' and hdgt.language = userenv(''LANG'')'||
6207 ' and dg.dimension_group_display_code = hdgt.dimension_group_display_code'||
6208 ' and dg.dimension_id = :b_dimension_id'||
6209 ' order by dg.dimension_group_seq'||
6210 ' )';
6211
6212 FEM_ENGINES_PKG.tech_message (
6213 p_severity => g_log_level_2
6214 ,p_module => G_BLOCK||'.'||l_api_name
6215 ,p_msg_text => 'END'
6216 );
6217
6218 END bld_get_dim_groups_t_stmt;
6219
6220 --------------------------------------------------------------------------------
6221
6222 PROCEDURE bld_get_dim_groups_stmt (
6223 p_dimension_varchar_label in varchar2
6224 ,p_execution_mode in varchar2
6225 ,x_get_dim_groups_stmt out nocopy varchar2
6226 )
6227 IS
6228
6229 l_api_name constant varchar2(30) := 'bld_get_dim_groups_stmt';
6230
6231 BEGIN
6232
6233 FEM_ENGINES_PKG.tech_message (
6234 p_severity => g_log_level_2
6235 ,p_module => G_BLOCK||'.'||l_api_name
6236 ,p_msg_text => 'BEGIN'
6237 );
6238
6239 x_get_dim_groups_stmt :=
6240 ' select dimension_group_id'||
6241 ' ,rownum as depth_num'||
6242 ' from ('||
6243 ' select hdg.dimension_group_id'||
6244 ' from fem_hier_dimension_grps hdg'||
6245 ' where hdg.hierarchy_obj_id = :b_hierarchy_object_id'||
6246 ' order by hdg.relative_dimension_group_seq'||
6247 ' )';
6248
6249 FEM_ENGINES_PKG.tech_message (
6250 p_severity => g_log_level_2
6251 ,p_module => G_BLOCK||'.'||l_api_name
6252 ,p_msg_text => 'END'
6253 );
6254
6255 END bld_get_dim_groups_stmt;
6256
6257 --------------------------------------------------------------------------------
6258
6259 PROCEDURE bld_get_hier_defs_stmt (
6260 p_dimension_varchar_label in varchar2
6261 ,p_execution_mode in varchar2
6262 ,x_get_hier_defs_stmt out nocopy varchar2
6263 )
6264 IS
6265
6266 l_api_name constant varchar2(30) := 'bld_get_hier_defs_stmt';
6267 l_status_clause varchar2(100) := '';
6268
6269 BEGIN
6270
6271 FEM_ENGINES_PKG.tech_message (
6272 p_severity => g_log_level_2
6273 ,p_module => G_BLOCK||'.'||l_api_name
6274 ,p_msg_text => 'BEGIN'
6275 );
6276
6277 if (p_execution_mode = g_snapshot) then
6278 l_status_clause := ' and ht.status = ''LOAD''';
6279 end if;
6280
6281 x_get_hier_defs_stmt :=
6282 ' select ht.rowid'||
6283 ' ,ht.folder_name'||
6284 ' ,ht.hierarchy_object_name'||
6285 ' ,ht.hier_obj_def_display_name'||
6286 ' ,ht.effective_start_date'||
6287 ' ,ht.effective_end_date'||
6288 ' ,ht.calendar_display_code'||
6289 ' ,ht.language'||
6290 ' ,ht.dimension_varchar_label'||
6291 ' ,ht.hierarchy_type_code'||
6292 ' ,ht.group_sequence_enforced_code'||
6293 ' ,ht.multi_top_flag'||
6294 ' ,ht.multi_value_set_flag'||
6295 ' ,ht.hierarchy_usage_code'||
6296 ' ,ht.flattened_rows_flag'||
6297 ' ,ht.status'||
6298 ' from fem_hierarchies_t ht'||
6299 ' where ht.dimension_varchar_label = :b_dimension_varchar_label'||
6300 ' and ht.hierarchy_object_name = :b_hierarchy_object_name'||
6301 ' and ht.hier_obj_def_display_name = :b_hier_obj_def_display_name'||
6302 l_status_clause||
6303 ' and ht.language = userenv(''LANG'')';
6304
6305 FEM_ENGINES_PKG.tech_message (
6306 p_severity => g_log_level_2
6307 ,p_module => G_BLOCK||'.'||l_api_name
6308 ,p_msg_text => 'END'
6309 );
6310
6311 END bld_get_hier_defs_stmt;
6312
6313 --------------------------------------------------------------------------------
6314
6315 PROCEDURE bld_get_hier_roots_stmt (
6316 p_dimension_varchar_label in varchar2
6317 ,p_execution_mode in varchar2
6318 ,p_value_set_required_flag in varchar2
6319 ,p_group_use_code in varchar2
6320 ,p_source_hier_table in varchar2
6321 ,p_member_b_table in varchar2
6322 ,p_member_col in varchar2
6323 ,p_member_dc_col in varchar2
6324 ,x_get_hier_roots_stmt out nocopy varchar2
6325 )
6326 IS
6327
6328 l_api_name constant varchar2(30) := 'bld_get_hier_roots_stmt';
6329
6330 l_status_clause varchar2(100) := '';
6331 l_dim_grp_clause varchar2(100);
6332
6333 BEGIN
6334
6335 FEM_ENGINES_PKG.tech_message (
6336 p_severity => g_log_level_2
6337 ,p_module => G_BLOCK||'.'||l_api_name
6338 ,p_msg_text => 'BEGIN'
6339 );
6340
6341 if (p_execution_mode = g_snapshot) then
6342 l_status_clause := ' and root.status = ''LOAD''';
6343 end if;
6344
6345 if (p_dimension_varchar_label = 'CAL_PERIOD') then
6346
6347 x_get_hier_roots_stmt :=
6348 ' select root.rowid'||
6349 ' ,b.'||p_member_col||
6350 ' ,null'||
6351 ' ,b.dimension_group_id'||
6352 ' ,root.display_order_num'||
6353 ' from '||p_source_hier_table||' root'||
6354 ' ,'||p_member_b_table||' b'||
6355 ' ,fem_dimension_grps_b dg'||
6356 ' where root.child_dim_grp_display_code = root.parent_dim_grp_display_code'||
6357 ' and root.child_cal_period_end_date = root.parent_cal_period_end_date'||
6358 ' and root.child_cal_period_number = root.parent_cal_period_number'||
6359 ' and root.hierarchy_object_name = :b_hierarchy_object_name'||
6360 ' and root.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
6361 ' and root.calendar_display_code = :b_calendar_display_code'||
6362 l_status_clause||
6363 ' and root.language = userenv(''LANG'')'||
6364 ' and dg.dimension_group_display_code = root.parent_dim_grp_display_code'||
6365 ' and dg.dimension_id = :b_dimension_id'||
6366 ' and b.'||p_member_col||' = FEM_DIMENSION_UTIL_PKG.Generate_Member_ID(root.parent_cal_period_end_date,root.parent_cal_period_number,:b_calendar_id,dg.dimension_group_id)';
6367
6368 else
6369
6370 if (p_group_use_code = 'NOT_SUPPORTED') then
6371 l_dim_grp_clause := ' ,null';
6372 else
6373 l_dim_grp_clause := ' ,b.dimension_group_id';
6374 end if;
6375
6376 if (p_value_set_required_flag = 'Y') then
6377
6378 x_get_hier_roots_stmt :=
6379 ' select root.rowid'||
6380 ' ,b.'||p_member_col||
6381 ' ,vs.value_set_id'||
6382 l_dim_grp_clause||
6383 ' ,root.display_order_num'||
6384 ' from '||p_source_hier_table||' root'||
6385 ' ,'||p_member_b_table||' b'||
6386 ' ,fem_value_sets_b vs'||
6387 ' where root.child_display_code = root.parent_display_code'||
6388 ' and root.child_value_set_display_code = root.parent_value_set_display_code'||
6389 ' and root.hierarchy_object_name = :b_hierarchy_object_name'||
6390 ' and root.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
6391 l_status_clause||
6392 ' and root.language = userenv(''LANG'')'||
6393 ' and vs.value_set_display_code = root.parent_value_set_display_code'||
6394 ' and vs.dimension_id = :b_dimension_id'||
6395 ' and b.value_set_id = vs.value_set_id'||
6396 ' and b.'||p_member_dc_col||' = root.parent_display_code';
6397
6398 else
6399
6400 -- FEM_HIERVAL_VSR_T_PK requires PARENT_VALUE_SET_ID and CHILD_VALUES_SET_ID
6401 x_get_hier_roots_stmt :=
6402 ' select root.rowid'||
6403 ' ,b.'||p_member_col||
6404 ' ,-1'||
6405 l_dim_grp_clause||
6406 ' ,root.display_order_num'||
6407 ' from '||p_source_hier_table||' root'||
6408 ' ,'||p_member_b_table||' b'||
6409 ' where root.child_display_code = root.parent_display_code'||
6410 ' and root.hierarchy_object_name = :b_hierarchy_object_name'||
6411 ' and root.hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
6412 l_status_clause||
6413 ' and root.language = userenv(''LANG'')'||
6414 ' and b.'||p_member_dc_col||' = root.parent_display_code';
6415
6416 end if;
6417
6418 end if;
6419
6420 FEM_ENGINES_PKG.tech_message (
6421 p_severity => g_log_level_2
6422 ,p_module => G_BLOCK||'.'||l_api_name
6423 ,p_msg_text => 'END'
6424 );
6425
6426 END bld_get_hier_roots_stmt;
6427
6428 --------------------------------------------------------------------------------
6429
6430 PROCEDURE bld_get_hier_rels_stmt (
6431 p_dimension_varchar_label in varchar2
6432 ,p_execution_mode in varchar2
6433 ,p_value_set_required_flag in varchar2
6434 ,p_group_use_code in varchar2
6435 ,p_source_hier_table in varchar2
6436 ,p_member_b_table in varchar2
6437 ,p_member_col in varchar2
6438 ,p_member_dc_col in varchar2
6439 ,x_get_hier_rels_stmt out nocopy varchar2
6440 )
6441 IS
6442
6443 l_api_name constant varchar2(30) := 'bld_get_hier_rels_stmt';
6444
6445 l_status_clause varchar2(100) := '';
6446 l_parent_dim_grp_clause varchar2(100);
6447 l_child_dim_grp_clause varchar2(100);
6448
6449 BEGIN
6450
6451 FEM_ENGINES_PKG.tech_message (
6452 p_severity => g_log_level_2
6453 ,p_module => G_BLOCK||'.'||l_api_name
6454 ,p_msg_text => 'BEGIN'
6455 );
6456
6457 if (p_execution_mode = g_snapshot) then
6458 l_status_clause := ' and status = ''LOAD''';
6459 end if;
6460
6461 if (p_dimension_varchar_label = 'CAL_PERIOD') then
6462
6463 x_get_hier_rels_stmt :=
6464 ' select rel.row_id'||
6465 ' ,rel.depth_num'||
6466 ' ,bp.'||p_member_col||
6467 ' ,null'||
6468 ' ,bp.dimension_group_id'||
6469 ' ,rel.depth_num+1'||
6470 ' ,bc.'||p_member_col||
6471 ' ,null'||
6472 ' ,bc.dimension_group_id'||
6473 ' ,rel.display_order_num'||
6474 ' ,rel.weighting_pct'||
6475 ' ,rel.status'||
6476 ' from ('||
6477 ' select row_id'||
6478 ' ,level as depth_num'||
6479 ' ,display_order_num'||
6480 ' ,weighting_pct'||
6481 ' ,status'||
6482 ' ,parent_dim_grp_display_code'||
6483 ' ,parent_cal_period_end_date'||
6484 ' ,parent_cal_period_number'||
6485 ' ,child_dim_grp_display_code'||
6486 ' ,child_cal_period_end_date'||
6487 ' ,child_cal_period_number'||
6488 ' from ('||
6489 ' select rowid as row_id'||
6490 ' ,display_order_num'||
6491 ' ,weighting_pct'||
6492 ' ,status'||
6493 ' ,parent_dim_grp_display_code'||
6494 ' ,parent_cal_period_end_date'||
6495 ' ,parent_cal_period_number'||
6496 ' ,child_dim_grp_display_code'||
6497 ' ,child_cal_period_end_date'||
6498 ' ,child_cal_period_number'||
6499 ' from '||p_source_hier_table||
6500 ' where hierarchy_object_name = :b_hierarchy_object_name'||
6501 ' and hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
6502 ' and calendar_display_code = :b_calendar_display_code'||
6503 ' and not ('||
6504 ' parent_dim_grp_display_code = child_dim_grp_display_code'||
6505 ' and parent_cal_period_end_date = child_cal_period_end_date'||
6506 ' and parent_cal_period_number = child_cal_period_number'||
6507 ' )'||
6508 l_status_clause||
6509 ' and language = userenv(''LANG'')'||
6510 ' )'||
6511 ' start with (parent_dim_grp_display_code,parent_cal_period_end_date,parent_cal_period_number) in ('||
6512 ' select parent_dim_grp_display_code'||
6513 ' ,parent_cal_period_end_date'||
6514 ' ,parent_cal_period_number'||
6515 ' from '||p_source_hier_table||
6516 ' where hierarchy_object_name = :b_hierarchy_object_name'||
6517 ' and hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
6518 ' and calendar_display_code = :b_calendar_display_code'||
6519 ' and child_dim_grp_display_code = parent_dim_grp_display_code'||
6520 ' and child_cal_period_end_date = parent_cal_period_end_date'||
6521 ' and child_cal_period_number = parent_cal_period_number'||
6522 l_status_clause||
6523 ' and language = userenv(''LANG'')'||
6524 ' )'||
6525 ' connect by prior child_dim_grp_display_code = parent_dim_grp_display_code'||
6526 ' and prior child_cal_period_end_date = parent_cal_period_end_date'||
6527 ' and prior child_cal_period_number = parent_cal_period_number'||
6528 ' ) rel'||
6529 ' ,'||p_member_b_table||' bp'||
6530 ' ,'||p_member_b_table||' bc'||
6531 ' ,fem_dimension_grps_b dgp'||
6532 ' ,fem_dimension_grps_b dgc'||
6533 ' where dgp.dimension_group_display_code = rel.parent_dim_grp_display_code'||
6534 ' and dgp.dimension_id = :b_dimension_id'||
6535 ' and dgc.dimension_group_display_code = rel.child_dim_grp_display_code'||
6536 ' and dgc.dimension_id = dgp.dimension_id'||
6537 ' and bp.'||p_member_col||' = FEM_DIMENSION_UTIL_PKG.Generate_Member_ID(rel.parent_cal_period_end_date,rel.parent_cal_period_number,:b_calendar_id,dgp.dimension_group_id)'||
6538 ' and bc.'||p_member_col||' = FEM_DIMENSION_UTIL_PKG.Generate_Member_ID(rel.child_cal_period_end_date,rel.child_cal_period_number,:b_calendar_id,dgc.dimension_group_id)';
6539
6540 else
6541
6542 if (p_group_use_code = 'NOT_SUPPORTED') then
6543 l_parent_dim_grp_clause := ' ,null';
6544 l_child_dim_grp_clause := ' ,null';
6545 else
6546 l_parent_dim_grp_clause := ' ,bp.dimension_group_id';
6547 l_child_dim_grp_clause := ' ,bc.dimension_group_id';
6548 end if;
6549
6550 if (p_value_set_required_flag = 'Y') then
6551
6552 x_get_hier_rels_stmt :=
6553 ' select rel.row_id'||
6554 ' ,rel.depth_num'||
6555 ' ,bp.'||p_member_col||
6556 ' ,vp.value_set_id'||
6557 l_parent_dim_grp_clause||
6558 ' ,rel.depth_num+1'||
6559 ' ,bc.'||p_member_col||
6560 ' ,vc.value_set_id'||
6561 l_child_dim_grp_clause||
6562 ' ,rel.display_order_num'||
6563 ' ,rel.weighting_pct'||
6564 ' ,rel.status'||
6565 ' from ('||
6566 ' select row_id'||
6567 ' ,level as depth_num'||
6568 ' ,display_order_num'||
6569 ' ,weighting_pct'||
6570 ' ,status'||
6571 ' ,parent_display_code'||
6572 ' ,parent_value_set_display_code'||
6573 ' ,child_display_code'||
6574 ' ,child_value_set_display_code'||
6575 ' from ('||
6576 ' select rowid as row_id'||
6577 ' ,display_order_num'||
6578 ' ,weighting_pct'||
6579 ' ,status'||
6580 ' ,parent_display_code'||
6581 ' ,parent_value_set_display_code'||
6582 ' ,child_display_code'||
6583 ' ,child_value_set_display_code'||
6584 ' from '||p_source_hier_table||
6585 ' where hierarchy_object_name = :b_hierarchy_object_name'||
6586 ' and hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
6587 ' and not ('||
6588 ' parent_display_code = child_display_code'||
6589 ' and parent_value_set_display_code = child_value_set_display_code'||
6590 ' )'||
6591 l_status_clause||
6592 ' and language = userenv(''LANG'')'||
6593 ' )'||
6594 ' start with (parent_display_code, parent_value_set_display_code) in ('||
6595 ' select parent_display_code'||
6596 ' ,parent_value_set_display_code'||
6597 ' from '||p_source_hier_table||
6598 ' where hierarchy_object_name = :b_hierarchy_object_name'||
6599 ' and hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
6600 ' and child_display_code = parent_display_code'||
6601 ' and child_value_set_display_code = parent_value_set_display_code'||
6602 l_status_clause||
6603 ' and language = userenv(''LANG'')'||
6604 ' )'||
6605 ' connect by prior child_display_code = parent_display_code'||
6606 ' and prior child_value_set_display_code = parent_value_set_display_code'||
6607 ' ) rel'||
6608 ' ,'||p_member_b_table||' bp'||
6609 ' ,fem_value_sets_b vp'||
6610 ' ,'||p_member_b_table||' bc'||
6611 ' ,fem_value_sets_b vc'||
6612 ' where bp.'||p_member_dc_col||' = rel.parent_display_code'||
6613 ' and bp.value_set_id = vp.value_set_id'||
6614 ' and vp.value_set_display_code = rel.parent_value_set_display_code'||
6615 ' and vp.dimension_id = :b_dimension_id'||
6616 ' and bc.'||p_member_dc_col||' = rel.child_display_code'||
6617 ' and bc.value_set_id = vc.value_set_id'||
6618 ' and vc.value_set_display_code = rel.child_value_set_display_code'||
6619 ' and vc.dimension_id = vp.dimension_id';
6620
6621 else
6622
6623 -- FEM_HIERVAL_VSR_T_PK requires PARENT_VALUE_SET_ID and CHILD_VALUES_SET_ID
6624 x_get_hier_rels_stmt :=
6625 ' select rel.row_id'||
6626 ' ,rel.depth_num'||
6627 ' ,bp.'||p_member_col||
6628 ' ,-1'||
6629 l_parent_dim_grp_clause||
6630 ' ,rel.depth_num+1'||
6631 ' ,bc.'||p_member_col||
6632 ' ,-1'||
6633 l_child_dim_grp_clause||
6634 ' ,rel.display_order_num'||
6635 ' ,rel.weighting_pct'||
6636 ' ,rel.status'||
6637 ' from ('||
6638 ' select row_id'||
6639 ' ,level as depth_num'||
6640 ' ,display_order_num'||
6641 ' ,weighting_pct'||
6642 ' ,status'||
6643 ' ,parent_display_code'||
6644 ' ,child_display_code'||
6645 ' from ('||
6646 ' select rowid as row_id'||
6647 ' ,display_order_num'||
6648 ' ,weighting_pct'||
6649 ' ,status'||
6650 ' ,parent_display_code'||
6651 ' ,child_display_code'||
6652 ' from '||p_source_hier_table||
6653 ' where hierarchy_object_name = :b_hierarchy_object_name'||
6654 ' and hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
6655 ' and parent_display_code <> child_display_code'||
6656 l_status_clause||
6657 ' and language = userenv(''LANG'')'||
6658 ' )'||
6659 ' start with parent_display_code in ('||
6660 ' select parent_display_code'||
6661 ' from '||p_source_hier_table||
6662 ' where hierarchy_object_name = :b_hierarchy_object_name'||
6663 ' and hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
6664 ' and child_display_code = parent_display_code'||
6665 l_status_clause||
6666 ' and language = userenv(''LANG'')'||
6667 ' )'||
6668 ' connect by prior child_display_code = parent_display_code'||
6669 ' ) rel'||
6670 ' ,'||p_member_b_table||' bp'||
6671 ' ,'||p_member_b_table||' bc'||
6672 ' where bp.'||p_member_dc_col||' = rel.parent_display_code'||
6673 ' and bc.'||p_member_dc_col||' = rel.child_display_code';
6674
6675 end if;
6676
6677 end if;
6678
6679 FEM_ENGINES_PKG.tech_message (
6680 p_severity => g_log_level_2
6681 ,p_module => G_BLOCK||'.'||l_api_name
6682 ,p_msg_text => 'END'
6683 );
6684
6685 END bld_get_hier_rels_stmt;
6686
6687 --------------------------------------------------------------------------------
6688
6689 PROCEDURE bld_insert_hier_rels_stmt (
6690 p_dimension_varchar_label in varchar2
6691 ,p_value_set_required_flag in varchar2
6692 ,p_target_hier_table in varchar2
6693 ,p_target_hierval_table in varchar2
6694 ,x_insert_hier_rels_stmt out nocopy varchar2
6695 )
6696 IS
6697
6698 l_api_name constant varchar2(30) := 'bld_insert_hier_rels_stmt';
6699
6700 BEGIN
6701
6702 FEM_ENGINES_PKG.tech_message (
6703 p_severity => g_log_level_2
6704 ,p_module => G_BLOCK||'.'||l_api_name
6705 ,p_msg_text => 'BEGIN'
6706 );
6707
6708 if (p_dimension_varchar_label = 'CAL_PERIOD') then
6709
6710 x_insert_hier_rels_stmt :=
6711 ' insert into '||p_target_hier_table||' ('||
6712 ' hierarchy_obj_def_id'||
6713 ' ,parent_depth_num'||
6714 ' ,parent_id'||
6715 ' ,child_depth_num'||
6716 ' ,child_id'||
6717 ' ,single_depth_flag'||
6718 ' ,display_order_num'||
6719 ' ,weighting_pct'||
6720 ' ,read_only_flag'||
6721 ' ,creation_date'||
6722 ' ,created_by'||
6723 ' ,last_updated_by'||
6724 ' ,last_update_date'||
6725 ' ,last_update_login'||
6726 ' ,object_version_number'||
6727 ' )'||
6728 ' select :b_hier_obj_def_id'||
6729 ' ,parent_depth_num'||
6730 ' ,parent_id'||
6731 ' ,child_depth_num'||
6732 ' ,child_id'||
6733 ' ,''Y'''||
6734 ' ,display_order_num'||
6735 ' ,weighting_pct'||
6736 ' ,''N'''||
6737 ' ,sysdate'||
6738 ' ,:b_user_id'||
6739 ' ,:b_user_id'||
6740 ' ,sysdate'||
6741 ' ,:b_login_id'||
6742 ' ,:b_object_version_number'||
6743 ' from '||p_target_hierval_table||
6744 ' where request_id = :b_request_id';
6745
6746 else
6747
6748 if (p_value_set_required_flag = 'Y') then
6749
6750 x_insert_hier_rels_stmt :=
6751 ' insert into '||p_target_hier_table||' ('||
6752 ' hierarchy_obj_def_id'||
6753 ' ,parent_depth_num'||
6754 ' ,parent_id'||
6755 ' ,parent_value_set_id'||
6756 ' ,child_depth_num'||
6757 ' ,child_id'||
6758 ' ,child_value_set_id'||
6759 ' ,single_depth_flag'||
6760 ' ,display_order_num'||
6761 ' ,weighting_pct'||
6762 ' ,read_only_flag'||
6763 ' ,creation_date'||
6764 ' ,created_by'||
6765 ' ,last_updated_by'||
6766 ' ,last_update_date'||
6767 ' ,last_update_login'||
6768 ' ,object_version_number'||
6769 ' )'||
6770 ' select :b_hier_obj_def_id'||
6771 ' ,parent_depth_num'||
6772 ' ,parent_id'||
6773 ' ,parent_value_set_id'||
6774 ' ,child_depth_num'||
6775 ' ,child_id'||
6776 ' ,child_value_set_id'||
6777 ' ,''Y'''||
6778 ' ,display_order_num'||
6779 ' ,weighting_pct'||
6780 ' ,''N'''||
6781 ' ,sysdate'||
6782 ' ,:b_user_id'||
6783 ' ,:b_user_id'||
6784 ' ,sysdate'||
6785 ' ,:b_login_id'||
6786 ' ,:b_object_version_number'||
6787 ' from '||p_target_hierval_table||
6788 ' where request_id = :b_request_id';
6789
6790 else
6791
6792 x_insert_hier_rels_stmt :=
6793 ' insert into '||p_target_hier_table||' ('||
6794 ' hierarchy_obj_def_id'||
6795 ' ,parent_depth_num'||
6796 ' ,parent_id'||
6797 ' ,child_depth_num'||
6798 ' ,child_id'||
6799 ' ,single_depth_flag'||
6800 ' ,display_order_num'||
6801 ' ,weighting_pct'||
6802 ' ,read_only_flag'||
6803 ' ,creation_date'||
6804 ' ,created_by'||
6805 ' ,last_updated_by'||
6806 ' ,last_update_date'||
6807 ' ,last_update_login'||
6808 ' ,object_version_number'||
6809 ' )'||
6810 ' select :b_hier_obj_def_id'||
6811 ' ,parent_depth_num'||
6812 ' ,parent_id'||
6813 ' ,child_depth_num'||
6814 ' ,child_id'||
6815 ' ,''Y'''||
6816 ' ,display_order_num'||
6817 ' ,weighting_pct'||
6818 ' ,''N'''||
6819 ' ,sysdate'||
6820 ' ,:b_user_id'||
6821 ' ,:b_user_id'||
6822 ' ,sysdate'||
6823 ' ,:b_login_id'||
6824 ' ,:b_object_version_number'||
6825 ' from '||p_target_hierval_table||
6826 ' where request_id = :b_request_id';
6827
6828 end if;
6829
6830 end if;
6831
6832 FEM_ENGINES_PKG.tech_message (
6833 p_severity => g_log_level_2
6834 ,p_module => G_BLOCK||'.'||l_api_name
6835 ,p_msg_text => 'END'
6836 );
6837
6838 END bld_insert_hier_rels_stmt;
6839
6840 --------------------------------------------------------------------------------
6841
6842 PROCEDURE bld_delete_hier_rels_stmt (
6843 p_dimension_varchar_label in varchar2
6844 ,p_execution_mode in varchar2
6845 ,p_source_hier_table in varchar2 := null
6846 ,p_target_hier_table in varchar2 := null
6847 ,p_target_hierval_table in varchar2 := null
6848 ,x_delete_hier_rels_stmt out nocopy varchar2
6849 )
6850 IS
6851
6852 l_api_name constant varchar2(30) := 'bld_delete_hier_rels_stmt';
6853 l_status_clause varchar2(100) := '';
6854
6855 BEGIN
6856
6857 FEM_ENGINES_PKG.tech_message (
6858 p_severity => g_log_level_2
6859 ,p_module => G_BLOCK||'.'||l_api_name
6860 ,p_msg_text => 'BEGIN'
6861 );
6862
6863 x_delete_hier_rels_stmt := null;
6864
6865 if (p_source_hier_table is not null) then
6866
6867 if (p_execution_mode = g_snapshot) then
6868 l_status_clause := ' and status = ''LOAD''';
6869 end if;
6870
6871 x_delete_hier_rels_stmt :=
6872 ' delete from '||p_source_hier_table||
6873 ' where hierarchy_object_name = :b_hierarchy_object_name'||
6874 ' and hierarchy_obj_def_display_name = :b_hier_obj_def_display_name'||
6875 l_status_clause||
6876 ' and language = userenv(''LANG'')';
6877
6878 elsif (p_target_hier_table is not null) then
6879
6880 x_delete_hier_rels_stmt :=
6881 ' delete from '||p_target_hier_table||
6882 ' where hierarchy_obj_def_id = :b_hier_obj_def_id';
6883
6884 elsif (p_target_hierval_table is not null) then
6885
6886 x_delete_hier_rels_stmt :=
6887 ' delete from '||p_target_hierval_table||
6888 ' where request_id = :b_request_id';
6889
6890 end if;
6891
6892 FEM_ENGINES_PKG.tech_message (
6893 p_severity => g_log_level_2
6894 ,p_module => G_BLOCK||'.'||l_api_name
6895 ,p_msg_text => 'END'
6896 );
6897
6898 END bld_delete_hier_rels_stmt;
6899
6900 --------------------------------------------------------------------------------
6901
6902 PROCEDURE bld_insert_hierval_rels_stmt (
6903 p_dimension_varchar_label in varchar2
6904 ,p_target_hierval_table in varchar2
6905 ,x_insert_hierval_rels_stmt out nocopy varchar2
6906 )
6907 IS
6908
6909 l_api_name constant varchar2(30) := 'bld_insert_hierval_rels_stmt';
6910
6911 BEGIN
6912
6913 FEM_ENGINES_PKG.tech_message (
6914 p_severity => g_log_level_2
6915 ,p_module => G_BLOCK||'.'||l_api_name
6916 ,p_msg_text => 'BEGIN'
6917 );
6918
6919 if (p_dimension_varchar_label = 'CAL_PERIOD') then
6920
6921 x_insert_hierval_rels_stmt :=
6922 ' insert into '||p_target_hierval_table||' ('||
6923 ' request_id'||
6924 ' ,source_hier_t_rowid'||
6925 ' ,parent_depth_num'||
6926 ' ,parent_id'||
6927 ' ,parent_dimension_grp_id'||
6928 ' ,child_depth_num'||
6929 ' ,child_id'||
6930 ' ,child_dimension_grp_id'||
6931 ' ,display_order_num'||
6932 ' ,weighting_pct'||
6933 ' ) values ('||
6934 ' :request_id'||
6935 ' ,:b_rowid'||
6936 ' ,:b_parent_depth_num'||
6937 ' ,:b_parent_id'||
6938 ' ,:b_parent_dimension_grp_id'||
6939 ' ,:b_child_depth_num'||
6940 ' ,:b_child_id'||
6941 ' ,:b_child_dimension_grp_id'||
6942 ' ,:b_display_order_num'||
6943 ' ,:b_wt_pct'||
6944 ' )';
6945
6946 else
6947
6948 x_insert_hierval_rels_stmt :=
6949 ' insert into '||p_target_hierval_table||' ('||
6950 ' request_id'||
6951 ' ,source_hier_t_rowid'||
6952 ' ,parent_depth_num'||
6953 ' ,parent_id'||
6954 ' ,parent_value_set_id'||
6955 ' ,parent_dimension_grp_id'||
6956 ' ,child_depth_num'||
6957 ' ,child_id'||
6958 ' ,child_value_set_id'||
6959 ' ,child_dimension_grp_id'||
6960 ' ,display_order_num'||
6961 ' ,weighting_pct'||
6962 ' ) values ('||
6963 ' :request_id'||
6964 ' ,:b_rowid'||
6965 ' ,:b_parent_depth_num'||
6966 ' ,:b_parent_id'||
6967 ' ,:b_parent_value_set_id'||
6968 ' ,:b_parent_dimension_grp_id'||
6969 ' ,:b_child_depth_num'||
6970 ' ,:b_child_id'||
6971 ' ,:b_child_value_set_id'||
6972 ' ,:b_child_dimension_grp_id'||
6973 ' ,:b_display_order_num'||
6974 ' ,:b_wt_pct'||
6975 ' )';
6976
6977 end if;
6978
6979 FEM_ENGINES_PKG.tech_message (
6980 p_severity => g_log_level_2
6981 ,p_module => G_BLOCK||'.'||l_api_name
6982 ,p_msg_text => 'END'
6983 );
6984
6985 END bld_insert_hierval_rels_stmt;
6986
6987 --------------------------------------------------------------------------------
6988
6989 FUNCTION bld_update_status_stmt (
6990 p_dimension_varchar_label in varchar2
6991 ,p_execution_mode in varchar2
6992 ,p_value_set_required_flag in varchar2 := null
6993 ,p_source_hier_table in varchar2
6994 ,p_rowid_flag in varchar2 := null
6995 ,p_hier_object_name_flag in varchar2 := null
6996 ,p_hier_obj_def_name_flag in varchar2 := null
6997 ,p_parent_flag in varchar2 := null
6998 ,p_child_flag in varchar2 := null
6999 )
7000 RETURN varchar2
7001 IS
7002
7003 l_api_name constant varchar2(30) := 'bld_update_status_stmt';
7004 l_status_clause varchar2(100) := '';
7005 l_update_status_stmt varchar2(4000);
7006
7007 BEGIN
7008
7009 FEM_ENGINES_PKG.tech_message (
7010 p_severity => g_log_level_2
7011 ,p_module => G_BLOCK||'.'||l_api_name
7012 ,p_msg_text => 'BEGIN'
7013 );
7014
7015 if (p_execution_mode = g_snapshot) then
7016 l_status_clause := ' and status = ''LOAD''';
7017 end if;
7018
7019 l_update_status_stmt :=
7020 ' update '||p_source_hier_table||
7021 ' set status = :b_status'||
7022 ' where language = userenv(''LANG'')'||
7023 l_status_clause;
7024
7025 if (p_rowid_flag is not null) then
7026 l_update_status_stmt := l_update_status_stmt ||
7027 ' and rowid = :b_rowid';
7028 end if;
7029
7030 if (p_hier_object_name_flag is not null) then
7031 l_update_status_stmt := l_update_status_stmt ||
7032 ' and hierarchy_object_name = :b_hierarchy_object_name';
7033 end if;
7034
7035 if (p_hier_obj_def_name_flag is not null) then
7036 l_update_status_stmt := l_update_status_stmt ||
7037 ' and hierarchy_obj_def_display_name = :b_hier_obj_def_display_name';
7038 end if;
7039
7040 if (p_dimension_varchar_label = 'CAL_PERIOD') then
7041
7042 if (p_parent_flag is not null) then
7043 l_update_status_stmt := l_update_status_stmt ||
7044 ' and calendar_display_code = :b_calendar_display_code'||
7045 ' and parent_dim_grp_display_code = :b_parent_dim_grp_dc'||
7046 ' and parent_cal_period_end_date = :b_parent_cal_period_end_date'||
7047 ' and parent_cal_period_number = :b_parent_cal_period_number'||
7048 ' and not ('||
7049 ' parent_dim_grp_display_code = child_dim_grp_display_code'||
7050 ' and parent_cal_period_end_date = child_cal_period_end_date'||
7051 ' and parent_cal_period_number = child_cal_period_number'||
7052 ' )';
7053 elsif (p_child_flag is not null) then
7054 l_update_status_stmt := l_update_status_stmt ||
7055 ' and calendar_display_code = :b_calendar_display_code'||
7056 ' and child_dim_grp_display_code = :b_child_dim_grp_dc'||
7057 ' and child_cal_period_end_date = :b_child_cal_period_end_date'||
7058 ' and child_cal_period_number = :b_child_cal_period_number'||
7059 ' and not ('||
7060 ' parent_dim_grp_display_code = child_dim_grp_display_code'||
7061 ' and parent_cal_period_end_date = child_cal_period_end_date'||
7062 ' and parent_cal_period_number = child_cal_period_number'||
7063 ' )';
7064 end if;
7065
7066 else
7067
7068 if (p_value_set_required_flag = 'Y') then
7069
7070 if (p_parent_flag is not null) then
7071 l_update_status_stmt := l_update_status_stmt ||
7072 ' and parent_display_code = :b_parent_dc'||
7073 ' and parent_value_set_display_code = :b_parent_value_set_dc'||
7074 ' and not ('||
7075 ' parent_display_code = child_display_code'||
7076 ' and parent_value_set_display_code = child_value_set_display_code'||
7077 ' )';
7078 elsif (p_child_flag is not null) then
7079 l_update_status_stmt := l_update_status_stmt ||
7080 ' and child_display_code = :b_child_dc'||
7081 ' and child_value_set_display_code = :b_child_value_set_dc'||
7082 ' and not ('||
7083 ' parent_display_code = child_display_code'||
7084 ' and parent_value_set_display_code = child_value_set_display_code'||
7085 ' )';
7086 end if;
7087
7088 else
7089
7090 if (p_parent_flag is not null) then
7091 l_update_status_stmt := l_update_status_stmt ||
7092 ' and parent_display_code = :b_parent_dc'||
7093 ' and parent_display_code <> child_display_code';
7094 elsif (p_child_flag is not null) then
7095 l_update_status_stmt := l_update_status_stmt ||
7096 ' and child_display_code = :b_child_dc'||
7097 ' and parent_display_code <> child_display_code';
7098 end if;
7099
7100 end if;
7101
7102 end if;
7103
7104 FEM_ENGINES_PKG.tech_message (
7105 p_severity => g_log_level_2
7106 ,p_module => G_BLOCK||'.'||l_api_name
7107 ,p_msg_text => 'END'
7108 );
7109
7110 return l_update_status_stmt;
7111
7112 END bld_update_status_stmt;
7113
7114
7115
7116 END FEM_HIER_LOADER_PKG;