[Home] [Help]
PACKAGE BODY: APPS.FEM_DIMENSION_MIGRATION_PKG
Source
1 PACKAGE BODY FEM_DIMENSION_MIGRATION_PKG AS
2 -- $Header: femdimmig_pkb.plb 120.7 2008/02/07 00:47:50 gcheng ship $
3
4 /***************************************************************************
5 Copyright (c) 2005 Oracle Corporation
6 Redwood Shores, CA, USA
7 All rights reserved.
8 ***************************************************************************
9 FILENAME
10 femdimmig_pkb.plb
11
12 DESCRIPTION
13 See femdimmig_pkh.pls for details
14
15 HISTORY
16 Penelope Brown 25-APR-05 Created
17 Rob Flippo 25-JUL-06 Bug#5331497 insert_value_sets procedure
18 default_member_id, default_load_member_id and
19 default_hierarchy_obj_id should be null when
20 value_set is created, since these values all come
21 from sequence that is not identical between source
22 and target db
23 mwickram 08-AUG-06 Bug 5287339 - DIMENSION HIERARCHY MIGRATION FAILS
24
25 **************************************************************************/
26
27 -------------------------------
28 -- Declare package variables --
29 -------------------------------
30 f_set_status BOOLEAN;
31
32 c_log_level_1 CONSTANT NUMBER := fnd_log.level_statement;
33 c_log_level_2 CONSTANT NUMBER := fnd_log.level_procedure;
34 c_log_level_3 CONSTANT NUMBER := fnd_log.level_event;
35 c_log_level_4 CONSTANT NUMBER := fnd_log.level_exception;
36 c_log_level_5 CONSTANT NUMBER := fnd_log.level_error;
37 c_log_level_6 CONSTANT NUMBER := fnd_log.level_unexpected;
38
39 v_log_level NUMBER;
40
41 gv_prg_msg VARCHAR2(2000);
42 gv_callstack VARCHAR2(2000);
43
44 -- Global Variables for Post Processing information
45 gv_rows_fetched NUMBER := 0;
46 gv_rows_rejected NUMBER := 0;
47 gv_rows_loaded NUMBER := 0;
48 gv_temp_rows_rejected NUMBER := 0;
49
50 gv_request_id NUMBER := fnd_global.conc_request_id;
51 gv_apps_user_id NUMBER := FND_GLOBAL.User_Id;
52 gv_login_id NUMBER := FND_GLOBAL.Login_Id;
53 gv_pgm_id NUMBER := FND_GLOBAL.Conc_Program_Id;
54 gv_pgm_app_id NUMBER := FND_GLOBAL.Prog_Appl_ID;
55 gv_concurrent_status BOOLEAN;
56
57 -- Execution Mode Clause for all Fetches against the interface tables
58 --gv_exec_mode_clause VARCHAR2(100);
59
60 -- Bulk Fetch profile no longer used
61 -- Default limit for all BULK Fetches
62 --gv_fetch_limit NUMBER := NVL(FND_PROFILE.Value_Specific(
63 -- 'FEM_BULK_FETCH_LIMIT',gv_apps_user_id,null,null),
64 -- c_fetch_limit);
65
66 gv_dim_props_rec DIMENSION_PROPS_REC;
67 gv_src_dim_props_rec DIMENSION_PROPS_REC;
68 gv_db_link_name VARCHAR2(40);
69
70 -----------------------------------------------
71 -- Declare private procedures and functions --
72 -----------------------------------------------
73
74 FUNCTION GET_DIM_ATTR_SQL(p_version_mode IN VARCHAR2,
75 p_source_db_link IN VARCHAR2,
76 p_dim_varchar_lbl IN VARCHAR2) RETURN VARCHAR2;
77
78 FUNCTION GET_DIM_HIER_RULE_SQL(p_dim_varchar_lbl IN VARCHAR2,
79 p_folder_name IN VARCHAR2,
80 p_hier_obj_name IN VARCHAR2,
81 p_hier_obj_def_name IN VARCHAR2,
82 p_source_db_link IN VARCHAR2,
83 p_eff_start_date IN DATE,
84 p_eff_end_date IN DATE) RETURN VARCHAR2;
85
86 FUNCTION GET_DIM_HIER_SQL(p_hier_obj_name IN VARCHAR2,
87 p_hier_obj_def_name IN VARCHAR2,
88 p_hier_obj_def_id IN NUMBER,
89 p_dim_varchar_lbl IN VARCHAR2,
90 p_source_db_link IN VARCHAR2) RETURN VARCHAR2;
91
92 FUNCTION GET_HIER_DG_SQL(p_hier_obj_name IN VARCHAR2,
93 p_source_db_link IN VARCHAR2) RETURN VARCHAR2;
94
95 FUNCTION GET_HIER_VS_SQL(p_hier_obj_name IN VARCHAR2,
96 p_source_db_link IN VARCHAR2) RETURN VARCHAR2;
97
98
99 FUNCTION GET_INSERT_B_SQL(p_source_db_link IN VARCHAR2,
100 p_dim_varchar_lbl IN VARCHAR2) RETURN VARCHAR2;
101
102 FUNCTION GET_INSERT_DIM_GRP_B_SQL(p_source_db_link IN VARCHAR2) RETURN VARCHAR2;
103
104 FUNCTION GET_INSERT_DIM_GRP_TL_SQL(p_source_db_link IN VARCHAR2) RETURN VARCHAR2;
105
106 FUNCTION GET_INSERT_TL_SQL(p_source_db_link IN VARCHAR2,
107 p_dim_varchar_lbl IN VARCHAR2) RETURN VARCHAR2;
108
109
110 FUNCTION VALIDATE_DB_LINK(p_db_link IN VARCHAR2) RETURN VARCHAR2;
111
112
113 PROCEDURE GET_DIMENSION_INFO(p_dim_name IN VARCHAR2,
114 p_source_user_dim_name IN VARCHAR2,
115 p_source_db_link IN VARCHAR2);
116
117 PROCEDURE GET_PUT_MESSAGES (p_msg_count IN NUMBER,
118 p_msg_data IN VARCHAR2);
119
120 PROCEDURE INSERT_CALENDARS(p_source_db_link IN VARCHAR2);
121
122 PROCEDURE INSERT_VALUE_SETS(p_source_db_link IN VARCHAR2);
123
124 PROCEDURE POST_PROCESS_HIERARCHY(p_execution_status IN VARCHAR2);
125
126 PROCEDURE POST_PROCESS_MEMBERS(p_execution_status IN VARCHAR2);
127
128 PROCEDURE PRE_PROCESS_HIERARCHY(p_source_db_link IN VARCHAR2,
129 p_dim_varchar_lbl IN VARCHAR2,
130 p_hier_obj_name IN VARCHAR2,
131 p_hier_obj_def_name IN VARCHAR2,
132 p_source_user_dim_name IN VARCHAR2,
133 x_folder_name OUT NOCOPY VARCHAR2,
134 x_hier_obj_id OUT NOCOPY NUMBER);
135
136 PROCEDURE PRE_PROCESS_MEMBERS(p_source_db_link IN VARCHAR2,
137 p_dim_varchar_lbl IN VARCHAR2,
138 p_autoload_dims IN VARCHAR2,
139 p_migrate_dependent_dims IN VARCHAR2,
140 p_version_mode IN VARCHAR2,
141 p_version_disp_cd IN VARCHAR2,
142 p_version_name IN VARCHAR2,
143 p_source_user_dim_name IN VARCHAR2,
144 p_hier_obj_name IN VARCHAR2,
145 p_hier_obj_def_name IN VARCHAR2);
146
147 PROCEDURE PROCESS_HIERARCHY(p_dim_varchar_lbl IN VARCHAR2,
148 p_folder_name IN VARCHAR2,
149 p_hier_obj_id IN NUMBER,
150 p_hier_obj_name IN VARCHAR2,
151 p_hier_obj_def_name IN VARCHAR2,
152 p_source_db_link IN VARCHAR2,
153 p_source_user_dim_name IN VARCHAR2);
154
155 PROCEDURE PROCESS_MEMBERS(p_dim_varchar_lbl IN VARCHAR2,
156 p_version_mode IN VARCHAR2,
157 p_source_db_link IN VARCHAR2);
158
159 PROCEDURE REGISTER_PROCESS_EXECUTION(p_object_id IN NUMBER,
160 p_obj_def_id IN NUMBER,
161 p_execution_mode IN VARCHAR2);
162
163 PROCEDURE UPDATE_CALP_ATTRIBUTES(p_source_db_link IN VARCHAR2);
164
165 PROCEDURE VALIDATE_PARAMETERS(p_source_db_link IN VARCHAR2,
166 p_autoload_dims IN VARCHAR2,
167 p_migrate_dependent_dims IN VARCHAR2,
168 p_version_mode IN VARCHAR2,
169 p_version_disp_cd IN VARCHAR2,
170 p_version_name IN VARCHAR2,
171 p_source_user_dim_name IN VARCHAR2,
172 p_hier_obj_name IN VARCHAR2,
173 p_hier_obj_def_name IN VARCHAR2);
174
175
176
177 -----------------------------------------------
178 -- Validate dbLink --
179 -----------------------------------------------
180 FUNCTION VALIDATE_DB_LINK(p_db_link IN VARCHAR2) RETURN VARCHAR2 IS
181
182 c_func_name VARCHAR2(30) := 'VALIDATE_DB_LINK';
183
184 l_db_link_name VARCHAR2(40);
185 l_db_link_sql VARCHAR2(100);
186
187 BEGIN
188
189 --Validate dbLink Parameter supplied is registered in FEM
190
191 SELECT DB_LINK_NAME
192 INTO gv_db_link_name
193 FROM FEM_DB_LINKS_VL
194 WHERE UPPER(DATABASE_LINK) = UPPER(p_db_link);
195
196 --Validate dbLink is functioning properly
197 l_db_link_sql := 'SELECT SYSDATE FROM DUAL@'||p_db_link;
198
199 EXECUTE IMMEDIATE l_db_link_sql;
200
201 RETURN 'VALID';
202
203 EXCEPTION
204 WHEN NO_DATA_FOUND THEN
205 RAISE e_db_link_not_registered;
206 WHEN OTHERS THEN
207 RAISE e_db_link_not_functional;
208 END;
209
210
211
212
213
214
215
216 -----------------------------------------------
217 -- Get Dimension Info --
218 -----------------------------------------------
219 PROCEDURE GET_DIMENSION_INFO(p_dim_name IN VARCHAR2,
220 p_source_user_dim_name IN VARCHAR2,
221 p_source_db_link IN VARCHAR2)
222
223 IS
224
225 l_temp_dim_name VARCHAR2(150);
226
227 l_chk_dim_name_sql VARCHAR2(1000) := 'SELECT DIMENSION_NAME ' ||
228 'FROM FEM_DIMENSIONS_TL '||'@'||p_source_db_link||' '||
229 'WHERE UPPER(DIMENSION_NAME) = UPPER(:src_dim_name)';
230 l_dim_name_sql VARCHAR2(2000) := 'SELECT DIMENSION_ID, '||
231 'USER_DEFINED_FLAG, '||
232 'GROUP_USE_CODE, '||
233 'VALUE_SET_REQUIRED_FLAG, '||
234 'MEMBER_B_TABLE_NAME, '||
235 'ATTRIBUTE_TABLE_NAME, '||
236 'MEMBER_DISPLAY_CODE_COL, '||
237 'MEMBER_NAME_COL, '||
238 'MEMBER_DESCRIPTION_COL, '||
239 'MEMBER_TL_TABLE_NAME, '||
240 'MEMBER_COL, '||
241 'DECODE(LOADER_OBJECT_DEF_ID, NULL, NULL, LOADER_OBJECT_DEF_ID+ 700), '||
242 'HIERARCHY_TABLE_NAME, '||
243 'DECODE(HIERARCHY_TABLE_NAME, NULL, NULL, HIERARCHY_TABLE_NAME||''_T'')'||
244 'FROM FEM_XDIM_DIMENSIONS_VL '||'@'||p_source_db_link||' '||
245 'WHERE DIMENSION_NAME = :p_source_user_dim_name';
246
247 BEGIN
248
249 SELECT DIMENSION_ID,
250 DECODE(LOADER_OBJECT_DEF_ID, NULL, NULL, LOADER_OBJECT_DEF_ID+ 700),
251 USER_DEFINED_FLAG,
252 GROUP_USE_CODE,
253 SIMPLE_DIMENSION_FLAG,
254 VALUE_SET_REQUIRED_FLAG,
255 INTF_MEMBER_B_TABLE_NAME,
256 INTF_MEMBER_TL_TABLE_NAME,
257 INTF_ATTRIBUTE_TABLE_NAME,
258 MEMBER_B_TABLE_NAME,
259 ATTRIBUTE_TABLE_NAME,
260 MEMBER_DISPLAY_CODE_COL,
261 MEMBER_NAME_COL,
262 MEMBER_DESCRIPTION_COL,
263 MEMBER_TL_TABLE_NAME,
264 MEMBER_COL,
265 HIERARCHY_TABLE_NAME,
266 DECODE(HIERARCHY_TABLE_NAME, NULL, NULL, HIERARCHY_TABLE_NAME||'_T')
267 INTO gv_dim_props_rec.DIMENSION_ID,
268 gv_dim_props_rec.MIGRATION_OBJ_DEF_ID,
269 gv_dim_props_rec.USER_DEFINED_FLAG,
270 gv_dim_props_rec.GROUP_USE_CODE,
271 gv_dim_props_rec.SIMPLE_DIMENSION_FLAG,
272 gv_dim_props_rec.VALUE_SET_REQUIRED_FLAG,
273 gv_dim_props_rec.INTF_MEMBER_B_TABLE_NAME,
274 gv_dim_props_rec.INTF_MEMBER_TL_TABLE_NAME,
275 gv_dim_props_rec.INTF_ATTRIBUTE_TABLE_NAME,
276 gv_dim_props_rec.MEMBER_B_TABLE_NAME,
277 gv_dim_props_rec.ATTRIBUTE_TABLE_NAME,
278 gv_dim_props_rec.MEMBER_DISPLAY_CODE_COL,
279 gv_dim_props_rec.MEMBER_NAME_COL,
280 gv_dim_props_rec.MEMBER_DESCRIPTION_COL,
281 gv_dim_props_rec.MEMBER_TL_TABLE_NAME,
282 gv_dim_props_rec.MEMBER_COL,
283 gv_dim_props_rec.HIERARCHY_TABLE_NAME,
284 gv_dim_props_rec.HIERARCHY_INTF_TABLE_NAME
285 FROM FEM_XDIM_DIMENSIONS_VL
286 WHERE DIMENSION_VARCHAR_LABEL = UPPER(p_dim_name);
287
288
289 IF (gv_dim_props_rec.MIGRATION_OBJ_DEF_ID IS NULL) THEN
290 RAISE e_dimension_not_supported;
291 END IF;
292
293 IF (p_source_user_dim_name IS NOT NULL AND gv_dim_props_rec.USER_DEFINED_FLAG = 'N') THEN
294 RAISE e_dim_not_user_extensible;
295 ELSIF (p_source_user_dim_name IS NOT NULL AND gv_dim_props_rec.USER_DEFINED_FLAG = 'Y') THEN
296 BEGIN
297
298 EXECUTE IMMEDIATE l_chk_dim_name_sql INTO l_temp_dim_name USING p_source_user_dim_name;
299
300 IF (SQL%FOUND) THEN
301 EXECUTE IMMEDIATE l_dim_name_sql
302 INTO gv_src_dim_props_rec.DIMENSION_ID,
303 gv_src_dim_props_rec.USER_DEFINED_FLAG,
304 gv_src_dim_props_rec.GROUP_USE_CODE,
305 gv_src_dim_props_rec.VALUE_SET_REQUIRED_FLAG,
306 gv_src_dim_props_rec.MEMBER_B_TABLE_NAME,
307 gv_src_dim_props_rec.ATTRIBUTE_TABLE_NAME,
308 gv_src_dim_props_rec.MEMBER_DISPLAY_CODE_COL,
309 gv_src_dim_props_rec.MEMBER_NAME_COL,
310 gv_src_dim_props_rec.MEMBER_DESCRIPTION_COL,
311 gv_src_dim_props_rec.MEMBER_TL_TABLE_NAME,
312 gv_src_dim_props_rec.MEMBER_COL,
313 gv_src_dim_props_rec.MIGRATION_OBJ_DEF_ID,
314 gv_src_dim_props_rec.HIERARCHY_TABLE_NAME,
315 gv_src_dim_props_rec.HIERARCHY_INTF_TABLE_NAME
316 USING p_source_user_dim_name;
317
318 END IF;
319
320 EXCEPTION
321 WHEN NO_DATA_FOUND THEN
322 RAISE e_src_dim_not_user_extensible;
323 END;
324
325 END IF;
326
327
328 IF (gv_src_dim_props_rec.USER_DEFINED_FLAG = 'N') THEN
329 RAISE e_src_dim_not_user_extensible;
330 END IF;
331
332 IF (gv_src_dim_props_rec.DIMENSION_ID IS NULL) THEN
333 gv_src_dim_props_rec.DIMENSION_ID := gv_dim_props_rec.DIMENSION_ID;
334 gv_src_dim_props_rec.GROUP_USE_CODE := gv_dim_props_rec.GROUP_USE_CODE;
335 gv_src_dim_props_rec.VALUE_SET_REQUIRED_FLAG:= gv_dim_props_rec.VALUE_SET_REQUIRED_FLAG;
336 gv_src_dim_props_rec.MEMBER_B_TABLE_NAME := gv_dim_props_rec.MEMBER_B_TABLE_NAME;
337 gv_src_dim_props_rec.ATTRIBUTE_TABLE_NAME := gv_dim_props_rec.ATTRIBUTE_TABLE_NAME;
338 gv_src_dim_props_rec.MEMBER_DISPLAY_CODE_COL := gv_dim_props_rec.MEMBER_DISPLAY_CODE_COL;
339 gv_src_dim_props_rec.MEMBER_NAME_COL := gv_dim_props_rec.MEMBER_NAME_COL;
340 gv_src_dim_props_rec.MEMBER_DESCRIPTION_COL := gv_dim_props_rec.MEMBER_DESCRIPTION_COL;
341 gv_src_dim_props_rec.MEMBER_TL_TABLE_NAME := gv_dim_props_rec.MEMBER_TL_TABLE_NAME;
342 gv_src_dim_props_rec.MEMBER_COL := gv_dim_props_rec.MEMBER_COL;
343 gv_src_dim_props_rec.MIGRATION_OBJ_DEF_ID := gv_dim_props_rec.MIGRATION_OBJ_DEF_ID;
344 gv_src_dim_props_rec.HIERARCHY_TABLE_NAME := gv_dim_props_rec.HIERARCHY_TABLE_NAME;
345 gv_src_dim_props_rec.HIERARCHY_INTF_TABLE_NAME := gv_dim_props_rec.HIERARCHY_INTF_TABLE_NAME;
346
347
348 END IF;
349
350 BEGIN
351
352 SELECT object_id
353 INTO gv_dim_props_rec.MIGRATION_OBJ_ID
354 FROM fem_object_definition_b
355 WHERE object_definition_id = gv_dim_props_rec.MIGRATION_OBJ_DEF_ID
356 AND object_id IN (SELECT object_id FROM fem_object_catalog_b
357 WHERE object_type_code = 'DIM_MEMBER_MIGRATION');
358
359 SELECT object_id
360 INTO gv_src_dim_props_rec.MIGRATION_OBJ_ID
361 FROM fem_object_definition_b
362 WHERE object_definition_id = gv_src_dim_props_rec.MIGRATION_OBJ_DEF_ID
363 AND object_id IN (SELECT object_id FROM fem_object_catalog_b
364 WHERE object_type_code = 'DIM_MEMBER_MIGRATION');
365
366 EXCEPTION
367
368 WHEN NO_DATA_FOUND THEN
369 RAISE e_invalid_obj_def;
370 END;
371
372 EXCEPTION
373
374 WHEN NO_DATA_FOUND THEN
375 RAISE e_invalid_dimension;
376 END;
377
378
379 -----------------------------------------------
380 -- Validate parameters --
381 -----------------------------------------------
382
383 PROCEDURE validate_parameters(p_source_db_link IN VARCHAR2,
384 p_autoload_dims IN VARCHAR2,
385 p_migrate_dependent_dims IN VARCHAR2,
386 p_version_mode IN VARCHAR2,
387 p_version_disp_cd IN VARCHAR2,
388 p_version_name IN VARCHAR2,
389 p_source_user_dim_name IN VARCHAR2,
390 p_hier_obj_name IN VARCHAR2,
391 p_hier_obj_def_name IN VARCHAR2)
392 IS
393
394 l_chk_version_name_sql VARCHAR2(1000) := 'SELECT VERSION_NAME ' ||
395 'FROM FEM_DIM_ATTR_VERSIONS_TL '||'@'||p_source_db_link||' '||
396 'WHERE UPPER(VERSION_NAME) = UPPER(:p_version_name)';
397
398 l_chk_version_disp_cd_sql VARCHAR2(1000) := 'SELECT VERSION_DISPLAY_CODE' ||
399 'FROM FEM_DIM_ATTR_VERSIONS_B '||'@'||p_source_db_link||' '||
400 'WHERE UPPER(VERSION_DISPLAY_CODE) = UPPER(:p_version_disp_cd)';
401
402 l_temp_version_name VARCHAR2(150);
403 l_temp_version_disp_cd VARCHAR2(150);
404
405 BEGIN
406
407
408 IF (p_version_mode IS NULL OR p_version_mode NOT IN ('ALL', 'DEFAULT', 'NEW')) THEN
409 RAISE e_invalid_version_param;
410 ELSIF (p_version_mode = 'NEW') THEN
411 IF (p_version_disp_cd IS NULL OR p_version_name IS NULL) THEN
412 RAISE e_missing_version_params;
413 ELSE
414 BEGIN
415
416 EXECUTE IMMEDIATE l_chk_version_name_sql INTO l_temp_version_name USING p_version_name;
417
418 IF (SQL%FOUND) THEN
419 RAISE e_invalid_version_name;
420 END IF;
421
422 EXECUTE IMMEDIATE l_chk_version_disp_cd_sql INTO l_temp_version_disp_cd USING p_version_disp_cd;
423
424 IF (SQL%FOUND) THEN
425 RAISE e_invalid_version_display_code;
426 END IF;
427 EXCEPTION
428 WHEN NO_DATA_FOUND THEN
429 --version name is good--
430 NULL;
431 END;
432 END IF;
433 END IF;
434
435 --TO DO: VALIDATE HIER PARAMS--
436 END;
437
438 -------------------------------------------------------------
439 -- Procedure for getting messages off the stack
440 -------------------------------------------------------------
441 PROCEDURE Get_Put_Messages (
442 p_msg_count IN NUMBER,
443 p_msg_data IN VARCHAR2
444 )
445 IS
446
447 v_msg_count NUMBER;
448 v_msg_data VARCHAR2(4000);
449 v_msg_out NUMBER;
450 v_message VARCHAR2(4000);
451
452 v_block CONSTANT VARCHAR2(80) :=
453 'fem.plsql.fem_dim_member_loader_pkg.get_put_messages';
454
455 BEGIN
456
457 FEM_ENGINES_PKG.TECH_MESSAGE
458 (p_severity => c_log_level_2,
459 p_module => v_block||'.msg_count',
460 p_msg_text => p_msg_count);
461
462 v_msg_data := p_msg_data;
463
464 IF (p_msg_count = 1)
465 THEN
466 FND_MESSAGE.Set_Encoded(v_msg_data);
467 v_message := FND_MESSAGE.Get;
468
469 FEM_ENGINES_PKG.User_Message(
470 p_msg_text => v_message);
471
472 FEM_ENGINES_PKG.TECH_MESSAGE
473 (p_severity => c_log_level_2,
474 p_module => v_block||'.msg_data',
475 p_msg_text => v_message);
476
477 ELSIF (p_msg_count > 1)
478 THEN
479 FOR i IN 1..p_msg_count
480 LOOP
481 FND_MSG_PUB.Get(
482 p_msg_index => i,
483 p_encoded => c_false,
484 p_data => v_message,
485 p_msg_index_out => v_msg_out);
486
487 FEM_ENGINES_PKG.User_Message(
488 p_msg_text => v_message);
489
490 FEM_ENGINES_PKG.TECH_MESSAGE
491 (p_severity => c_log_level_2,
492 p_module => v_block||'.msg_data',
493 p_msg_text => v_message);
494
495 END LOOP;
496 END IF;
497
498 FND_MSG_PUB.Initialize;
499
500 END Get_Put_Messages;
501
502 -----------------------------------------------
503 -- Build Insert _B SQL --
504 -----------------------------------------------
505 FUNCTION get_insert_b_sql(p_source_db_link IN VARCHAR2, p_dim_varchar_lbl IN VARCHAR2) RETURN VARCHAR2
506
507 IS
508
509 l_dim_vc_select VARCHAR2(35) := ''''||p_dim_varchar_lbl||'''';
510 l_base_select VARCHAR2(35) := 'B.'||gv_src_dim_props_rec.MEMBER_DISPLAY_CODE_COL;
511 l_vs_select VARCHAR2(35) := 'VS.VALUE_SET_DISPLAY_CODE';
512 l_dg_select VARCHAR2(35) := 'DG.DIMENSION_GROUP_DISPLAY_CODE';
513 l_status_select VARCHAR2(10) := '''LOAD''';
514 l_batch_name_select VARCHAR2(30) := '''Y''';
515
516 l_base_from VARCHAR2(100) := gv_src_dim_props_rec.MEMBER_B_TABLE_NAME||'@'||p_source_db_link||' B';
517 l_vs_from VARCHAR2(100) := 'FEM_VALUE_SETS_B@'||p_source_db_link||' VS';
518 l_dim_grp_from VARCHAR2(100) := 'FEM_DIMENSION_GRPS_B@'||p_source_db_link||' DG';
519
520 l_base_vs_where VARCHAR2(35) := 'B.VALUE_SET_ID = VS.VALUE_SET_ID';
521 l_base_dg_where VARCHAR2(50) := 'B.DIMENSION_GROUP_ID = DG.DIMENSION_GROUP_ID(+)';
522
523 l_insert_table VARCHAR2(100) := gv_dim_props_rec.INTF_MEMBER_B_TABLE_NAME;
524 l_comma1 VARCHAR2(2) := ', ';
525 l_semi VARCHAR2(1) := ';';
526 l_where_clause VARCHAR2(1000) := --' WHERE NOT EXISTS (SELECT 1 FROM '||l_insert_table||' INTF WHERE INTF.'||
527 -- gv_dim_props_rec.MEMBER_DISPLAY_CODE_COL||' = '||l_base_select||')'||
528 ' WHERE {{data_slice}}';
529
530
531 l_cal_period_select VARCHAR2(1000) := 'CA2.DATE_ASSIGN_VALUE, CA1.NUMBER_ASSIGN_VALUE, ''LOAD'', CAL.CALENDAR_DISPLAY_CODE, '||
532 'DG.DIMENSION_GROUP_DISPLAY_CODE, ''Y''';
533
534 l_cal_period_from VARCHAR2(1000) := 'FEM_CAL_PERIODS_B@'||p_source_db_link||' B, FEM_DIM_ATTRIBUTES_B@'||
535 p_source_db_link||' DA1 , FEM_DIM_ATTRIBUTES_B@'||p_source_db_link||' DA2, FEM_DIMENSION_GRPS_B@'||
536 p_source_db_link||' DG, '||
537 'FEM_CAL_PERIODS_ATTR@'||p_source_db_link||' CA1, FEM_CAL_PERIODS_ATTR@'||p_source_db_link||' CA2,'||
538 ' FEM_CALENDARS_B@'||p_source_db_link||' CAL ';
539
540 l_cal_period_where VARCHAR2(1000) := 'WHERE B.CAL_PERIOD_ID = CA1.CAL_PERIOD_ID AND B.CAL_PERIOD_ID = CA2.CAL_PERIOD_ID'||
541 ' AND B.CALENDAR_ID = CAL.CALENDAR_ID AND CA1.ATTRIBUTE_ID = DA1.ATTRIBUTE_ID'||
542 ' AND CA2.ATTRIBUTE_ID = DA2.ATTRIBUTE_ID AND DA1.ATTRIBUTE_VARCHAR_LABEL = ''GL_PERIOD_NUM'''||
543 ' AND DA2.ATTRIBUTE_VARCHAR_LABEL = ''CAL_PERIOD_END_DATE'''||
544 ' AND B.DIMENSION_GROUP_ID = DG.DIMENSION_GROUP_ID(+) AND {{data_slice}}'||
545 ' AND CA1.AW_SNAPSHOT_FLAG = ''N'' AND CA2.AW_SNAPSHOT_FLAG = ''N''';
546
547
548 l_insert_sql VARCHAR2(32767);
549
550
551 BEGIN
552
553
554 IF (p_dim_varchar_lbl = 'CAL_PERIOD') THEN
555 l_insert_sql := 'INSERT INTO '||l_insert_table||
556 '(SELECT '|| l_cal_period_select||
557 ' FROM '|| l_cal_period_from||l_cal_period_where||')';
558
559 ELSE
560
561
562 l_base_select := l_base_select||l_comma1;
563 l_status_select := l_status_select||l_comma1;
564
565
566 IF (gv_src_dim_props_rec.VALUE_SET_REQUIRED_FLAG = 'Y') THEN
567 l_vs_select := l_vs_select ||l_comma1;
568
569 ELSIF (gv_src_dim_props_rec.VALUE_SET_REQUIRED_FLAG = 'N') THEN
570 l_vs_select := NULL;
571 l_vs_from := NULL;
572 l_base_vs_where := NULL;
573 END IF;
574
575 IF (gv_src_dim_props_rec.GROUP_USE_CODE = 'NOT_SUPPORTED') THEN
576 l_dg_select := NULL;
577 l_base_dg_where := NULL;
578 l_dim_grp_from := NULL;
579 ELSE
580 l_dg_select := l_dg_select||l_comma1;
581 END IF;
582
583 IF (gv_dim_props_rec.INTF_MEMBER_B_TABLE_NAME = 'FEM_SIMPLE_DIMS_B_T') THEN
584 l_dim_vc_select := l_dim_vc_select||l_comma1;
585 ELSE
586 l_dim_vc_select := NULL;
587 END IF;
588
589 IF (l_base_vs_where IS NULL AND l_base_dg_where IS NULL) THEN
590 l_where_clause := l_where_clause;
591 ELSIF (l_base_vs_where IS NULL AND l_base_dg_where IS NOT NULL) THEN
592 l_base_from := l_base_from||l_comma1;
593 l_where_clause := l_where_clause||' AND '||l_base_dg_where;
594 ELSIF (l_base_vs_where IS NOT NULL AND l_base_dg_where IS NULL) THEN
595 l_base_from := l_base_from||l_comma1;
596 l_where_clause := l_where_clause||' AND '||l_base_vs_where;
597 ELSE
598 l_base_from := l_base_from||l_comma1;
599 l_vs_from := l_vs_from||l_comma1;
600 l_where_clause := l_where_clause||' AND '||l_base_vs_where|| ' AND '||l_base_dg_where;
601 END IF;
602
603 l_insert_sql := 'INSERT INTO '||l_insert_table||
604 '(SELECT '|| l_dim_vc_select||l_base_select||
605 l_vs_select||
606 l_status_select||
607 l_dg_select||
608 l_batch_name_select||
609 ' FROM '|| l_base_from||
610 l_vs_from||
611 l_dim_grp_from||l_where_clause||')';
612
613 END IF;
614
615
616 RETURN l_insert_sql;
617 END;
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632 -----------------------------------------------
633 -- Build Insert _TL SQL --
634 -----------------------------------------------
635 FUNCTION get_insert_tl_sql(p_source_db_link IN VARCHAR2, p_dim_varchar_lbl IN VARCHAR2) RETURN VARCHAR2
636
637 IS
638
639 l_dim_vc_select VARCHAR2(35) := ''''||p_dim_varchar_lbl||'''';
640 l_tl_dc_select VARCHAR2(35) := 'B.'||gv_src_dim_props_rec.MEMBER_DISPLAY_CODE_COL;
641 l_vs_select VARCHAR2(35) := 'VS.VALUE_SET_DISPLAY_CODE';
642 l_status_select VARCHAR2(10) := '''LOAD''';
643 l_tl_lang_select VARCHAR2(35) := 'TL.LANGUAGE';
644 l_tl_name_select VARCHAR2(35) := 'TL.'||gv_src_dim_props_rec.MEMBER_NAME_COL;
645 l_tl_desc_select VARCHAR2(35) := 'TL.'||gv_src_dim_props_rec.MEMBER_DESCRIPTION_COL;
646 l_batch_name_select VARCHAR2(30) := '''Y''';
647
648 l_tl_from VARCHAR2(500):= gv_src_dim_props_rec.MEMBER_B_TABLE_NAME||'@'||p_source_db_link||' B, '||
649 gv_src_dim_props_rec.MEMBER_TL_TABLE_NAME||'@'||p_source_db_link||' TL';
650 l_vs_from VARCHAR2(100) := 'FEM_VALUE_SETS_B@'||p_source_db_link||' VS';
651
652 l_tl_vs_where VARCHAR2(100) := 'TL.VALUE_SET_ID = VS.VALUE_SET_ID';
653
654 l_insert_table VARCHAR2(100) := gv_dim_props_rec.INTF_MEMBER_TL_TABLE_NAME;
655 l_comma1 VARCHAR2(2) := ', ';
656 l_semi VARCHAR2(1) := ';';
657 l_where_clause VARCHAR2(1000) := ' WHERE B.' ||gv_src_dim_props_rec.MEMBER_COL || ' = TL.'|| gv_src_dim_props_rec.MEMBER_COL||
658 --' AND NOT EXISTS (SELECT 1 FROM '||l_insert_table||' INTF WHERE INTF.'||
659 -- gv_dim_props_rec.MEMBER_DISPLAY_CODE_COL||' = '||l_tl_dc_select||')'||
660 ' AND {{data_slice}} AND EXISTS (SELECT 1 FROM FND_LANGUAGES L'||
661 ' WHERE TL.LANGUAGE = L.LANGUAGE_CODE AND L.INSTALLED_FLAG IN (''I'',''B''))';
662
663
664
665 l_cal_period_select VARCHAR2(1000) := 'CA2.DATE_ASSIGN_VALUE, CA1.NUMBER_ASSIGN_VALUE, TL.LANGUAGE, '||
666 'TL.CAL_PERIOD_NAME, TL.DESCRIPTION, ''LOAD'', CAL.CALENDAR_DISPLAY_CODE, '||
667 'DG.DIMENSION_GROUP_DISPLAY_CODE, ''Y''';
668
669 l_cal_period_from VARCHAR2(1000) := 'FEM_CAL_PERIODS_B@'||p_source_db_link||' B, FEM_CAL_PERIODS_TL@'||
670 p_source_db_link||' TL, FEM_DIM_ATTRIBUTES_B@'||p_source_db_link||
671 ' DA1 , FEM_DIM_ATTRIBUTES_B@'||p_source_db_link||' DA2, FEM_DIMENSION_GRPS_B@'||
672 p_source_db_link||' DG, '||
673 'FEM_CAL_PERIODS_ATTR@'||p_source_db_link||' CA1, FEM_CAL_PERIODS_ATTR@'||
674 p_source_db_link||' CA2, FEM_CALENDARS_B@'||p_source_db_link||' CAL ';
675
676 l_cal_period_where VARCHAR2(1000) := 'WHERE B.CAL_PERIOD_ID = TL.CAL_PERIOD_ID AND B.CAL_PERIOD_ID = CA1.CAL_PERIOD_ID AND B.CAL_PERIOD_ID = CA2.CAL_PERIOD_ID'||
677 ' AND B.CALENDAR_ID = CAL.CALENDAR_ID AND CA1.ATTRIBUTE_ID = DA1.ATTRIBUTE_ID'||
678 ' AND CA2.ATTRIBUTE_ID = DA2.ATTRIBUTE_ID AND DA1.ATTRIBUTE_VARCHAR_LABEL = ''GL_PERIOD_NUM'''||
679 ' AND DA2.ATTRIBUTE_VARCHAR_LABEL = ''CAL_PERIOD_END_DATE'''||
680 ' AND B.DIMENSION_GROUP_ID = DG.DIMENSION_GROUP_ID(+) AND {{data_slice}}'||
681 ' AND CA1.AW_SNAPSHOT_FLAG = ''N'' AND CA2.AW_SNAPSHOT_FLAG = ''N'''||
682 ' AND EXISTS (SELECT 1 FROM FND_LANGUAGES L'||
683 ' WHERE TL.LANGUAGE = L.LANGUAGE_CODE AND L.INSTALLED_FLAG IN (''I'',''B''))';
684
685 l_insert_sql VARCHAR2(32767);
686
687
688 BEGIN
689
690
691 IF (p_dim_varchar_lbl = 'CAL_PERIOD') THEN
692 l_insert_sql := 'INSERT INTO '||l_insert_table||
693 '(SELECT '|| l_cal_period_select||
694 ' FROM '|| l_cal_period_from||l_cal_period_where||')';
695
696 ELSE
697
698 l_tl_dc_select := l_tl_dc_select ||l_comma1;
699 l_tl_lang_select := l_tl_lang_select||l_comma1;
700 l_tl_name_select := l_tl_name_select ||l_comma1;
701 l_tl_desc_select := l_tl_desc_select ||l_comma1;
702 l_status_select := l_status_select||l_comma1;
703
704 IF (gv_dim_props_rec.INTF_MEMBER_TL_TABLE_NAME = 'FEM_SIMPLE_DIMS_TL_T') THEN
705 l_dim_vc_select := l_dim_vc_select||l_comma1;
706 ELSE
707 l_dim_vc_select := NULL;
708 END IF;
709
710 IF (gv_src_dim_props_rec.VALUE_SET_REQUIRED_FLAG = 'Y') THEN
711 l_vs_select := l_vs_select ||l_comma1;
712
713 ELSIF (gv_src_dim_props_rec.VALUE_SET_REQUIRED_FLAG = 'N') THEN
714 l_vs_select := NULL;
715 l_vs_from := NULL;
716 l_tl_vs_where := NULL;
717 END IF;
718
719 /*IF (gv_dim_props_rec.GROUP_USE_CODE = 'NOT_SUPPORTED') THEN
720 l_dg_select := NULL;
721 l_base_dg_where := NULL;
722 l_dim_grp_from := NULL;
723 ELSE
724 l_status_select := l_status_select||l_comma1;
725 END IF;*/
726
727 IF (l_tl_vs_where IS NULL) THEN
728 l_where_clause := l_where_clause;
729 ELSE
730 l_tl_from := l_tl_from||l_comma1;
731 l_where_clause := l_where_clause||' AND '||l_tl_vs_where;
732 END IF;
733
734 l_insert_sql := 'INSERT INTO '||l_insert_table||
735 '(SELECT '|| l_dim_vc_select||l_tl_dc_select||
736 l_vs_select||
737 l_tl_lang_select||
738 l_tl_name_select||
739 l_tl_desc_select||
740 l_status_select||
741 l_batch_name_select||
742 ' FROM '|| l_tl_from||
743 l_vs_from||l_where_clause||')';
744
745 END IF;
746
747
748 RETURN l_insert_sql;
749 END;
750
751
752
753
754
755 -----------------------------------------------
756 -- Build Insert _ATTR SQL --
757 -----------------------------------------------
758 FUNCTION get_dim_attr_sql(p_version_mode IN VARCHAR2, p_source_db_link IN VARCHAR2,p_dim_varchar_lbl IN VARCHAR2)
759 RETURN VARCHAR2
760
761 IS
762
763 l_insert_clause VARCHAR2(100) :=
764 ' INSERT INTO '|| gv_dim_props_rec.INTF_ATTRIBUTE_TABLE_NAME;
765
766 l_insert_cols VARCHAR2(2000) :=
767 ' ( '||gv_dim_props_rec.MEMBER_DISPLAY_CODE_COL||', ATTRIBUTE_VARCHAR_LABEL,'||
768 ' ATTRIBUTE_ASSIGN_VALUE, ATTR_ASSIGN_VS_DISPLAY_CODE, STATUS, CREATED_BY_DIM_MIGRATION_FLAG,'||
769 ' CALPATTR_CAL_DISPLAY_CODE, CALPATTR_DIMGRP_DISPLAY_CODE, CALPATTR_END_DATE, CALPATTR_PERIOD_NUM,'||
770 ' VERSION_DISPLAY_CODE'||
771 ' {{vs_insert_col}} {{calp_insert_col}} )';
772
773 l_calp_insert_cols VARCHAR2(500) :=
774 ' ,CAL_PERIOD_END_DATE, CAL_PERIOD_NUMBER, CALENDAR_DISPLAY_CODE, DIMENSION_GROUP_DISPLAY_CODE';
775
776 l_shared_vc_insert VARCHAR2(50) := 'DIMENSION_VARCHAR_LABEL';
777 l_shared_member_col VARCHAR2(50) :=
778 ' ,MEMBER_CODE';
779
780 l_vs_insert_cols VARCHAR2(50) :=
781 ' ,VALUE_SET_DISPLAY_CODE';
782
783 l_select_clause VARCHAR2(2000) :=
784 ' ( SELECT {{vc_select_clause}}'||
785 ' B.'|| gv_src_dim_props_rec.MEMBER_DISPLAY_CODE_COL||
786 ' ,DA.ATTRIBUTE_VARCHAR_LABEL'||
787 ' ,DECODE(DA.ATTRIBUTE_VALUE_COLUMN_NAME'||
788 ' ,''DATE_ASSIGN_VALUE'', TO_CHAR(ATTR.DATE_ASSIGN_VALUE, ''{{icx_date_format}}'')'||
789 ' ,''NUMBER_ASSIGN_VALUE'', TO_CHAR(ATTR.NUMBER_ASSIGN_VALUE)'||
790 ' ,''VARCHAR_ASSIGN_VALUE'', ATTR.VARCHAR_ASSIGN_VALUE'||
791 ' ,''DIM_ATTRIBUTE_NUMERIC_MEMBER'', FEM_DIMENSION_UTIL_PKG.Get_Dim_Member_Display_Code@'||p_source_db_link||'('||
792 ' DA.ATTRIBUTE_DIMENSION_ID'||
793 ' ,TO_CHAR(ATTR.DIM_ATTRIBUTE_NUMERIC_MEMBER)'||
794 ' ,ATTR.DIM_ATTRIBUTE_VALUE_SET_ID)'||
795 ' ,''DIM_ATTRIBUTE_VARCHAR_MEMBER'', FEM_DIMENSION_UTIL_PKG.Get_Dim_Member_Display_Code@'||p_source_db_link||'('||
796 ' DA.ATTRIBUTE_DIMENSION_ID'||
797 ' ,ATTR.DIM_ATTRIBUTE_VARCHAR_MEMBER'||
798 ' ,ATTR.DIM_ATTRIBUTE_VALUE_SET_ID)'||
799 ' ) AS ATTR_ASSIGN_VALUE'||
800 ' ,DECODE(DA.ATTRIBUTE_DATA_TYPE_CODE'||
801 ' ,''DIMENSION'', DECODE(ATTR.DIM_ATTRIBUTE_VALUE_SET_ID'||
802 ' ,NULL,NULL'||
803 ' ,FEM_MIR_PKG.Get_Dim_Member_Display_Code@'||p_source_db_link||'('||
804 ' ''VALUE_SET'',ATTR.DIM_ATTRIBUTE_VALUE_SET_ID)'||
805 ' )'||
806 ' ,NULL'||
807 ' ) AS ATTR_ASSIGN_VS_DISPLAY_CODE'||
808 ' ,DECODE(DA.ATTRIBUTE_DIMENSION_ID, 1, ''UPDATE'', ''LOAD'')'||
809 ' ,''Y'''||
810 ' ,NULL AS CALPATTR_CAL_DISPLAY_CODE'||
811 ' ,NULL AS CALPATTR_DIMGRP_DISPLAY_CODE'||
812 ' ,NULL AS CALPATTR_END_DATE'||
813 ' ,NULL AS CALP_ATTR_PERIOD_NUM'||
814 ' ,AV.VERSION_DISPLAY_CODE'||
815 ' {{vs_select_clause}} {{calp_select_clause}}';
816
817 l_dim_vc_select VARCHAR2(50) := ''''||p_dim_varchar_lbl||''''||',';
818
819 l_calp_select VARCHAR2(500) :=
820 ' ,CA2.DATE_ASSIGN_VALUE, CA1.NUMBER_ASSIGN_VALUE, CAL.CALENDAR_DISPLAY_CODE'||
821 ' ,DG.DIMENSION_GROUP_DISPLAY_CODE';
822
823 l_vs_select_clause VARCHAR2(50) :=
824 ' ,VS.VALUE_SET_DISPLAY_CODE';
825
826 l_from_clause VARCHAR2(2000) :=
827 ' FROM '|| gv_src_dim_props_rec.MEMBER_B_TABLE_NAME||'@'||p_source_db_link||' B'||
828 ' ,'||gv_src_dim_props_rec.ATTRIBUTE_TABLE_NAME||'@'||p_source_db_link||' ATTR'||
829 ' ,FEM_DIM_ATTRIBUTES_B@'||p_source_db_link||' DA'||
830 ' ,FEM_DIM_ATTR_VERSIONS_B@'||p_source_db_link||' AV'||
831 ' {{vs_from_table}} {{calp_from_clause}}';
832
833 l_vs_from_table VARCHAR2(100) :=
834 ' ,FEM_VALUE_SETS_B@'||p_source_db_link||' VS';
835
836 l_calp_from VARCHAR2(1000) :=
837 ' ,FEM_DIM_ATTRIBUTES_B@'||p_source_db_link||' DA1'||
838 ' ,FEM_DIM_ATTRIBUTES_B@'||p_source_db_link||' DA2'||
839 ' ,FEM_DIMENSION_GRPS_B@'||p_source_db_link||' DG'||
840 ' ,FEM_CAL_PERIODS_ATTR@'||p_source_db_link||' CA1'||
841 ' ,FEM_CAL_PERIODS_ATTR@'||p_source_db_link||' CA2'||
842 ' ,FEM_CALENDARS_B@'||p_source_db_link||' CAL';
843
844 l_where_clause VARCHAR2(2000) :=
845 ' WHERE B.'|| gv_src_dim_props_rec.MEMBER_COL||'= ATTR.'||gv_src_dim_props_rec.MEMBER_COL||
846 ' AND DA.ATTRIBUTE_ID = ATTR.ATTRIBUTE_ID'||
847 ' AND ATTR.VERSION_ID = AV.VERSION_ID'||
848 ' AND DA.DIMENSION_ID = '||gv_src_dim_props_rec.DIMENSION_ID||
849 ' AND {{data_slice}}'||
850 ' {{default_where_clause}} {{vs_where_clause}} {{calp_where_clause}} )';
851
852 l_default_where_clause VARCHAR2(50) :=
853 ' AND AV.DEFAULT_VERSION_FLAG = ''Y''';
854
855 l_vs_where_clause VARCHAR2(50) :=
856 ' AND ATTR.VALUE_SET_ID = VS.VALUE_SET_ID';
857
858 l_calp_where VARCHAR2(1000) :=
859 ' AND B.CAL_PERIOD_ID = CA1.CAL_PERIOD_ID'||
860 ' AND B.CAL_PERIOD_ID = CA2.CAL_PERIOD_ID'||
861 ' AND B.CALENDAR_ID = CAL.CALENDAR_ID'||
862 ' AND CA1.ATTRIBUTE_ID = DA1.ATTRIBUTE_ID'||
863 ' AND CA2.ATTRIBUTE_ID = DA2.ATTRIBUTE_ID'||
864 ' AND DA1.ATTRIBUTE_VARCHAR_LABEL = ''GL_PERIOD_NUM'''||
865 ' AND DA2.ATTRIBUTE_VARCHAR_LABEL = ''CAL_PERIOD_END_DATE'''||
866 ' AND B.DIMENSION_GROUP_ID = DG.DIMENSION_GROUP_ID(+)'||
867 ' AND CA1.AW_SNAPSHOT_FLAG = ''N'''||
868 ' AND CA2.AW_SNAPSHOT_FLAG = ''N''';
869
870 l_insert_sql VARCHAR2(32767);
871
872 l_icx_date_format VARCHAR2(50);
873
874 BEGIN
875
876 l_icx_date_format := FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK');
877
878 l_select_clause := REPLACE(l_select_clause,'{{icx_date_format}}',l_icx_date_format);
879
880 IF (p_dim_varchar_lbl = 'CAL_PERIOD') THEN
881
882 l_insert_cols := REPLACE(l_insert_cols, gv_dim_props_rec.MEMBER_DISPLAY_CODE_COL||', ', '');
883 l_insert_cols := REPLACE(l_insert_cols, '{{calp_insert_col}}', l_calp_insert_cols);
884 l_select_clause := REPLACE(l_select_clause,'{{calp_select_clause}}',l_calp_select);
885 l_select_clause := REPLACE(l_select_clause,'B.'|| gv_src_dim_props_rec.MEMBER_DISPLAY_CODE_COL ||',' , '');
886 l_from_clause := REPLACE(l_from_clause,'{{calp_from_clause}}', l_calp_from);
887 l_where_clause := REPLACE(l_where_clause,'{{calp_where_clause}}', l_calp_where);
888 else
889 l_insert_cols := REPLACE(l_insert_cols, '{{calp_insert_col}}','');
890 l_select_clause := REPLACE(l_select_clause,'{{calp_select_clause}}','');
891 l_from_clause := REPLACE(l_from_clause,'{{calp_from_clause}}', '');
892 l_where_clause := REPLACE(l_where_clause,'{{calp_where_clause}}', '');
893
894 END IF;
895
896 IF (gv_dim_props_rec.INTF_ATTRIBUTE_TABLE_NAME = 'FEM_SHARED_ATTR_T') THEN
897 l_insert_cols := REPLACE(l_insert_cols, gv_dim_props_rec.MEMBER_DISPLAY_CODE_COL,
898 l_shared_vc_insert||l_shared_member_col);
899 l_select_clause := REPLACE(l_select_clause,'{{vc_select_clause}}',l_dim_vc_select);
900 ELSE
901 l_select_clause := REPLACE(l_select_clause,'{{vc_select_clause}}','');
902
903 END IF;
904
905 IF (gv_src_dim_props_rec.VALUE_SET_REQUIRED_FLAG = 'Y') THEN
906 l_insert_cols := REPLACE(l_insert_cols,'{{vs_insert_col}}',l_vs_insert_cols);
907 l_select_clause := REPLACE(l_select_clause,'{{vs_select_clause}}',l_vs_select_clause);
908 l_from_clause := REPLACE(l_from_clause,'{{vs_from_table}}', l_vs_from_table);
909 l_where_clause := REPLACE(l_where_clause,'{{vs_where_clause}}', l_vs_where_clause);
910 ELSE
911 l_insert_cols := REPLACE(l_insert_cols,'{{vs_insert_col}}','');
912 l_select_clause := REPLACE(l_select_clause,'{{vs_select_clause}}','');
913 l_from_clause := REPLACE(l_from_clause,'{{vs_from_table}}', '');
914 l_where_clause := REPLACE(l_where_clause,'{{vs_where_clause}}', '');
915 END IF;
916
917 IF (p_version_mode = 'DEFAULT') THEN
918 l_where_clause := REPLACE(l_where_clause,'{{default_where_clause}}', l_default_where_clause);
919 ELSIF (p_version_mode = 'ALL') THEN
920 l_where_clause := REPLACE(l_where_clause,'{{default_where_clause}}', '');
921 END IF;
922
923 l_insert_sql := l_insert_clause||l_insert_cols||l_select_clause||l_from_clause||l_where_clause;
924
925
926 RETURN l_insert_sql;
927
928 END;
929
930 -----------------------------------------------
931 -- Build Get Attribute Assignment Value --
932 -----------------------------------------------
933
934 FUNCTION get_attr_assign_value(p_source_db_link IN VARCHAR2,
935 p_dimension_id IN NUMBER,
936 p_value IN VARCHAR2 ) RETURN VARCHAR2
937
938 IS
939
940 l_table_name VARCHAR2(30);
941 l_display_code_col VARCHAR2(30);
942 l_member_col VARCHAR2(30);
943 l_xdim_table VARCHAR2(100) := 'FEM_XDIM_DIMENSIONS@'||p_source_db_link;
944 l_dim_sql VARCHAR2(500) := 'SELECT MEMBER_B_TABLE_NAME,'||
945 ' MEMBER_DISPLAY_CODE_COL,'||
946 ' MEMBER_COL '||
947 ' FROM '||l_xdim_table||
948 ' WHERE DIMENSION_ID = :attr_dimension_id';
949
950 l_attr_dc_sql VARCHAR2(2000);
951 l_display_code_val VARCHAR2(1000);
952 l_attr_column_name VARCHAR2(30);
953
954 BEGIN
955
956
957 EXECUTE IMMEDIATE l_dim_sql INTO l_table_name, l_display_code_col, l_member_col USING p_dimension_id;
958
959 l_attr_dc_sql := 'SELECT '||l_display_code_col ||
960 ' FROM '||l_table_name ||
961 ' WHERE '||l_member_col||' = :value';
962
963 EXECUTE IMMEDIATE l_attr_dc_sql INTO l_display_code_val USING p_value;
964
965 RETURN l_display_code_val;
966
967 EXCEPTION
968
969 WHEN NO_DATA_FOUND THEN
970 RETURN p_value;
971
972 END;
973
974 -----------------------------------------------
975 -- Update Cal Period Attribute Assignments --
976 -----------------------------------------------
977
978 PROCEDURE update_calp_attributes(p_source_db_link IN VARCHAR2)
979
980 IS
981
982 l_attr_assign_value VARCHAR2(1000);
983 l_cal_display_code VARCHAR2(150);
984 l_dim_grp_display_code VARCHAR2(150);
985 l_calp_end_date DATE;
986 l_calp_period_num NUMBER;
987
988 l_calp_intf_attr_sql VARCHAR2(1000) :=
989 'SELECT ATTRIBUTE_ASSIGN_VALUE '||
990 ' FROM '||gv_dim_props_rec.INTF_ATTRIBUTE_TABLE_NAME||
991 ' WHERE STATUS = ''UPDATE''';
992
993 l_select_sql VARCHAR2(2000) :=
994 'SELECT CAL.CALENDAR_DISPLAY_CODE,'||
995 ' DG.DIMENSION_GROUP_DISPLAY_CODE,'||
996 ' ATTR.NUMBER_ASSIGN_VALUE AS GL_PERIOD_NUM,'||
997 ' ATTR1.DATE_ASSIGN_VALUE AS CAL_PERIOD_END_DATE'||
998 ' FROM FEM_CALENDARS_B@'||p_source_db_link||' CAL,'||
999 ' FEM_DIMENSION_GRPS_B@'||p_source_db_link||' DG,'||
1000 ' FEM_CAL_PERIODS_B@'||p_source_db_link||' CP,'||
1001 ' FEM_CAL_PERIODS_ATTR@'||p_source_db_link||' ATTR,'||
1002 ' FEM_DIM_ATTRIBUTES_B@'||p_source_db_link||' DA,'||
1003 ' FEM_DIM_ATTR_VERSIONS_B@'||p_source_db_link||' AV,'||
1004 ' FEM_CAL_PERIODS_ATTR@'||p_source_db_link||' ATTR1,'||
1005 ' FEM_DIM_ATTRIBUTES_B@'||p_source_db_link||' DA1,'||
1006 ' FEM_DIM_ATTR_VERSIONS_B@'||p_source_db_link||' AV1'||
1007 ' WHERE CP.CAL_PERIOD_ID = :cal_period_id'||
1008 ' AND CP.CALENDAR_ID = CAL.CALENDAR_ID'||
1009 ' AND DG.DIMENSION_GROUP_ID = CP.DIMENSION_GROUP_ID'||
1010 ' AND ATTR.ATTRIBUTE_ID = DA.ATTRIBUTE_ID'||
1011 ' AND ATTR.CAL_PERIOD_ID = CP.CAL_PERIOD_ID'||
1012 ' AND DA.ATTRIBUTE_VARCHAR_LABEL = ''GL_PERIOD_NUM'''||
1013 ' AND ATTR.VERSION_ID = AV.VERSION_ID'||
1014 ' AND AV.DEFAULT_VERSION_FLAG = ''Y'''||
1015 ' AND ATTR1.ATTRIBUTE_ID = DA1.ATTRIBUTE_ID'||
1016 ' AND ATTR1.CAL_PERIOD_ID = CP.CAL_PERIOD_ID'||
1017 ' AND DA1.ATTRIBUTE_VARCHAR_LABEL = ''CAL_PERIOD_END_DATE'''||
1018 ' AND ATTR1.VERSION_ID = AV1.VERSION_ID'||
1019 ' AND AV1.DEFAULT_VERSION_FLAG = ''Y''';
1020
1021 l_update_sql VARCHAR2(1000):= 'UPDATE '||gv_dim_props_rec.INTF_ATTRIBUTE_TABLE_NAME||
1022 ' SET ATTRIBUTE_ASSIGN_VALUE = NULL,'||
1023 ' CALPATTR_CAL_DISPLAY_CODE = :cal_display_code,'||
1024 ' CALPATTR_DIMGRP_DISPLAY_CODE = :dim_grp_display_code,'||
1025 ' CALPATTR_END_DATE = :calp_end_date,'||
1026 ' CALPATTR_PERIOD_NUM = :calp_period_num,'||
1027 ' STATUS = ''LOAD'''||
1028 ' WHERE ATTRIBUTE_ASSIGN_VALUE = :cal_period_id'||
1029 ' AND STATUS = ''UPDATE'' AND CREATED_BY_DIM_MIGRATION_FLAG = ''Y''';
1030
1031 TYPE UPDATE_CALP_CURSOR_TYPE IS REF CURSOR;
1032
1033 update_calp_cur UPDATE_CALP_CURSOR_TYPE;
1034
1035 BEGIN
1036
1037 OPEN update_calp_cur FOR l_calp_intf_attr_sql;
1038
1039 LOOP
1040
1041 FETCH update_calp_cur INTO l_attr_assign_value;
1042
1043 EXIT WHEN update_calp_cur%NOTFOUND;
1044
1045 EXECUTE IMMEDIATE l_select_sql
1046 INTO l_cal_display_code,
1047 l_dim_grp_display_code,
1048 l_calp_period_num,
1049 l_calp_end_date
1050 USING l_attr_assign_value;
1051
1052
1053 EXECUTE IMMEDIATE l_update_sql
1054 USING l_cal_display_code,
1055 l_dim_grp_display_code,
1056 l_calp_end_date,
1057 l_calp_period_num,
1058 l_attr_assign_value;
1059
1060 COMMIT;
1061
1062 END LOOP;
1063
1064 END;
1065
1066
1067
1068 -----------------------------------------------
1069 -- Build Insert _HIER SQL --
1070 -----------------------------------------------
1071 FUNCTION get_dim_hier_sql(p_hier_obj_name IN VARCHAR2,
1072 p_hier_obj_def_name IN VARCHAR2,
1073 p_hier_obj_def_id IN NUMBER,
1074 p_dim_varchar_lbl IN VARCHAR2,
1075 p_source_db_link IN VARCHAR2) RETURN VARCHAR2
1076
1077 IS
1078
1079 l_insert_clause VARCHAR2(100) := 'INSERT INTO '|| gv_dim_props_rec.HIERARCHY_INTF_TABLE_NAME;
1080
1081 l_insert_cols VARCHAR2(500) := '(HIERARCHY_OBJECT_NAME, HIERARCHY_OBJ_DEF_DISPLAY_NAME, DISPLAY_ORDER_NUM, WEIGHTING_PCT, STATUS, LANGUAGE, CREATED_BY_DIM_MIGRATION_FLAG {{dc_insert}} {{vs_insert}} {{calp_insert}})';
1082
1083 l_dc_insert VARCHAR2(100) := ', PARENT_DISPLAY_CODE, CHILD_DISPLAY_CODE';
1084
1085 l_calp_insert VARCHAR2(500) := ', CALENDAR_DISPLAY_CODE, PARENT_DIM_GRP_DISPLAY_CODE, CHILD_DIM_GRP_DISPLAY_CODE, PARENT_CAL_PERIOD_NUMBER, PARENT_CAL_PERIOD_END_DATE, CHILD_CAL_PERIOD_NUMBER, CHILD_CAL_PERIOD_END_DATE';
1086
1087 l_vs_insert VARCHAR2(100) := ', PARENT_VALUE_SET_DISPLAY_CODE, CHILD_VALUE_SET_DISPLAY_CODE';
1088
1089
1090 l_select_clause VARCHAR2(1500) := '(SELECT '''||p_hier_obj_name||''', '''||p_hier_obj_def_name||''','||
1091 ' HIER.DISPLAY_ORDER_NUM, HIER.WEIGHTING_PCT, ''LOAD'', '''||USERENV('LANG')||''', ''Y'' {{dc_select}} {{vs_select}} {{calp_select}}';
1092
1093
1094 l_dc_select VARCHAR2(100) := ', B1.'||gv_src_dim_props_rec.MEMBER_DISPLAY_CODE_COL||', B2.'||gv_src_dim_props_rec.MEMBER_DISPLAY_CODE_COL;
1095
1096 l_vs_select VARCHAR2(100) := ', VS1.VALUE_SET_DISPLAY_CODE, VS2.VALUE_SET_DISPLAY_CODE';
1097
1098
1099 l_calp_select VARCHAR2(500) := ', CAL.CALENDAR_DISPLAY_CODE, DG1.DIMENSION_GROUP_DISPLAY_CODE, DG2.DIMENSION_GROUP_DISPLAY_CODE, CA1.NUMBER_ASSIGN_VALUE, CA2.DATE_ASSIGN_VALUE, CA3.NUMBER_ASSIGN_VALUE, CA4.DATE_ASSIGN_VALUE';
1100
1101
1102 l_from_clause VARCHAR2(2000) := ' FROM '|| gv_src_dim_props_rec.HIERARCHY_TABLE_NAME||'@'||p_source_db_link||' HIER {{dc_from}} {{vs_from}} {{calp_from}}';
1103
1104 l_dc_from VARCHAR2(150) := ', '|| gv_src_dim_props_rec.MEMBER_B_TABLE_NAME||'@'||p_source_db_link||' B1, '||gv_src_dim_props_rec.MEMBER_B_TABLE_NAME||'@'||p_source_db_link||' B2';
1105
1106 l_vs_from VARCHAR2(150) := ', FEM_VALUE_SETS_B@'||p_source_db_link||' VS1, FEM_VALUE_SETS_B@'||p_source_db_link||' VS2';
1107
1108 l_calp_from VARCHAR2(1000) := ', FEM_CAL_PERIODS_B@'||p_source_db_link||' B1, FEM_CAL_PERIODS_B@'||p_source_db_link||
1109 ' B2, FEM_DIM_ATTRIBUTES_B@'||p_source_db_link||
1110 ' DA1 , FEM_DIM_ATTRIBUTES_B@'||p_source_db_link||' DA2, FEM_DIM_ATTRIBUTES_B@'||
1111 p_source_db_link||' DA3 , FEM_DIM_ATTRIBUTES_B@'||p_source_db_link||' DA4, FEM_DIMENSION_GRPS_B@'||
1112 p_source_db_link||' DG1, FEM_DIMENSION_GRPS_B@'||p_source_db_link||' DG2, '||
1113 'FEM_CAL_PERIODS_ATTR@'||p_source_db_link||' CA1, FEM_CAL_PERIODS_ATTR@'||
1114 p_source_db_link||' CA2, FEM_CAL_PERIODS_ATTR@'||p_source_db_link||' CA3, FEM_CAL_PERIODS_ATTR@'||
1115 p_source_db_link||' CA4, FEM_CALENDARS_B@'||p_source_db_link||' CAL ';
1116
1117 l_where_clause VARCHAR2(1500) := ' WHERE HIER.hierarchy_obj_def_id = '||p_hier_obj_def_id||' AND HIER.single_depth_flag = ''Y'''||' AND {{data_slice}} {{dc_where}} {{vs_where}} {{calp_where}})';
1118
1119 l_vs_where VARCHAR2(100) := ' AND HIER.PARENT_VALUE_SET_ID = VS1.VALUE_SET_ID AND HIER.CHILD_VALUE_SET_ID = VS2.VALUE_SET_ID';
1120
1121 l_dc_where VARCHAR2(200) := ' AND HIER.PARENT_ID = B1.'||gv_src_dim_props_rec.MEMBER_COL||' AND HIER.CHILD_ID = B2.'||gv_src_dim_props_rec.MEMBER_COL;
1122
1123 l_calp_where VARCHAR2(1000) := ' AND HIER.PARENT_ID = B1.CAL_PERIOD_ID'||
1124 ' AND HIER.CHILD_ID = B2.CAL_PERIOD_ID'||
1125 ' AND B1.CAL_PERIOD_ID = CA1.CAL_PERIOD_ID'||
1126 ' AND B1.CAL_PERIOD_ID = CA2.CAL_PERIOD_ID'||
1127 ' AND B2.CAL_PERIOD_ID = CA3.CAL_PERIOD_ID'||
1128 ' AND B2.CAL_PERIOD_ID = CA4.CAL_PERIOD_ID'||
1129 ' AND B1.CALENDAR_ID = CAL.CALENDAR_ID'||
1130 ' AND B2.CALENDAR_ID = CAL.CALENDAR_ID'||
1131 ' AND CA1.ATTRIBUTE_ID = DA1.ATTRIBUTE_ID'||
1132 ' AND CA2.ATTRIBUTE_ID = DA2.ATTRIBUTE_ID'||
1133 ' AND CA3.ATTRIBUTE_ID = DA3.ATTRIBUTE_ID'||
1134 ' AND CA4.ATTRIBUTE_ID = DA4.ATTRIBUTE_ID'||
1135 ' AND DA1.ATTRIBUTE_VARCHAR_LABEL = ''GL_PERIOD_NUM'''||
1136 ' AND DA2.ATTRIBUTE_VARCHAR_LABEL = ''CAL_PERIOD_END_DATE'''||
1137 ' AND DA3.ATTRIBUTE_VARCHAR_LABEL = ''GL_PERIOD_NUM'''||
1138 ' AND DA4.ATTRIBUTE_VARCHAR_LABEL = ''CAL_PERIOD_END_DATE'''||
1139 ' AND B1.DIMENSION_GROUP_ID = DG1.DIMENSION_GROUP_ID(+)'||
1140 ' AND B2.DIMENSION_GROUP_ID = DG2.DIMENSION_GROUP_ID(+)'||
1141 ' AND CA1.AW_SNAPSHOT_FLAG = ''N'''||
1142 ' AND CA2.AW_SNAPSHOT_FLAG = ''N'''||
1143 ' AND CA3.AW_SNAPSHOT_FLAG = ''N'''||
1144 ' AND CA4.AW_SNAPSHOT_FLAG = ''N''';
1145
1146 l_insert_sql VARCHAR2(32767);
1147
1148 BEGIN
1149
1150 IF p_dim_varchar_lbl <> 'CAL_PERIOD' THEN
1151 l_insert_cols := REPLACE(l_insert_cols, '{{dc_insert}}', l_dc_insert);
1152 l_insert_cols := REPLACE(l_insert_cols, '{{calp_insert}}', '');
1153
1154 l_select_clause:= REPLACE(l_select_clause, '{{dc_select}}', l_dc_select);
1155 l_select_clause:= REPLACE(l_select_clause, '{{calp_select}}', '');
1156
1157 l_from_clause:= REPLACE(l_from_clause, '{{dc_from}}', l_dc_from);
1158 l_from_clause:= REPLACE(l_from_clause, '{{calp_from}}', '');
1159
1160 l_where_clause:= REPLACE(l_where_clause, '{{dc_where}}', l_dc_where);
1161 l_where_clause:= REPLACE(l_where_clause, '{{calp_where}}', '');
1162
1163 ELSE
1164 l_insert_cols := REPLACE(l_insert_cols, '{{calp_insert}}', l_calp_insert);
1165 l_insert_cols := REPLACE(l_insert_cols, '{{dc_insert}}', '');
1166 l_select_clause:= REPLACE(l_select_clause, '{{calp_select}}', l_calp_select);
1167 l_select_clause:= REPLACE(l_select_clause, '{{dc_select}}', '');
1168
1169 l_from_clause:= REPLACE(l_from_clause, '{{calp_from}}', l_calp_from);
1170 l_from_clause:= REPLACE(l_from_clause, '{{dc_from}}', '');
1171
1172 l_where_clause:= REPLACE(l_where_clause, '{{calp_where}}', l_calp_where);
1173 l_where_clause:= REPLACE(l_where_clause, '{{dc_where}}', '');
1174
1175
1176 END IF;
1177
1178 IF (gv_dim_props_rec.VALUE_SET_REQUIRED_FLAG = 'Y') THEN
1179
1180 l_insert_cols := REPLACE(l_insert_cols, '{{vs_insert}}', l_vs_insert);
1181 l_select_clause:= REPLACE(l_select_clause, '{{vs_select}}', l_vs_select);
1182 l_from_clause:= REPLACE(l_from_clause, '{{vs_from}}', l_vs_from);
1183 l_where_clause:= REPLACE(l_where_clause, '{{vs_where}}', l_vs_where);
1184
1185 ELSE
1186 l_insert_cols := REPLACE(l_insert_cols, '{{vs_insert}}', '');
1187 l_select_clause:= REPLACE(l_select_clause, '{{vs_select}}', '');
1188 l_from_clause:= REPLACE(l_from_clause, '{{vs_from}}','');
1189 l_where_clause:= REPLACE(l_where_clause, '{{vs_where}}', '');
1190
1191 END IF;
1192
1193
1194 l_insert_sql := l_insert_clause||l_insert_cols||l_select_clause||l_from_clause||l_where_clause;
1195
1196
1197 RETURN l_insert_sql;
1198
1199 END;
1200
1201
1202
1203
1204
1205 -----------------------------------------------
1206 -- Build Insert Hier Rule SQL --
1207 -----------------------------------------------
1208 FUNCTION get_dim_hier_rule_sql(p_dim_varchar_lbl IN VARCHAR2,
1209 p_folder_name IN VARCHAR2,
1210 p_hier_obj_name IN VARCHAR2,
1211 p_hier_obj_def_name IN VARCHAR2,
1212 p_source_db_link IN VARCHAR2,
1213 p_eff_start_date IN DATE,
1214 p_eff_end_date IN DATE) RETURN VARCHAR2
1215
1216 IS
1217
1218 l_eff_start_date_str VARCHAR2(100) := 'NULL';
1219 l_eff_end_date_str VARCHAR2(100) := 'NULL';
1220
1221 l_insert_clause VARCHAR2(100) := 'INSERT INTO FEM_HIERARCHIES_T';
1222
1223
1224 l_select_clause VARCHAR2(1500);
1225
1226 l_from_clause VARCHAR2(500) := ' FROM FEM_HIERARCHIES@'||p_source_db_link;
1227
1228 l_where_clause VARCHAR2(1500) := ' WHERE HIERARCHY_OBJ_ID = :hier_obj_id)';
1229
1230 l_insert_sql VARCHAR2(32767);
1231
1232 l_eff_start_date VARCHAR2(50);
1233 l_eff_end_date VARCHAR2(50);
1234
1235 l_folder_name VARCHAR2(200);
1236
1237 BEGIN
1238
1239
1240 IF (p_eff_start_date IS NOT NULL) THEN
1241 l_eff_start_date := FND_DATE.date_to_canonical(p_eff_start_date);
1242
1243 l_eff_start_date_str := 'TO_DATE('||''''||l_eff_start_date||''''||','||'''YYYY/MM/DD HH24:MI:SS'''||')';
1244
1245 END IF;
1246
1247
1248 IF (p_eff_end_date IS NOT NULL) THEN
1249 l_eff_end_date := FND_DATE.date_to_canonical(p_eff_end_date);
1250
1251 l_eff_end_date_str := 'TO_DATE('||''''||l_eff_end_date||''''||','||'''YYYY/MM/DD HH24:MI:SS'''||')';
1252 END IF;
1253
1254 l_folder_name := REPLACE(p_folder_name, '''', '''''');
1255
1256 l_select_clause := '(SELECT '''||p_hier_obj_name||''', '''||l_folder_name||''','||
1257 ''''||USERENV('LANG')||''', '''||p_dim_varchar_lbl||''','||
1258 ' HIERARCHY_TYPE_CODE, GROUP_SEQUENCE_ENFORCED_CODE, MULTI_TOP_FLAG, MULTI_VALUE_SET_FLAG,'||
1259 ' HIERARCHY_USAGE_CODE, FLATTENED_ROWS_FLAG, ''LOAD'', '''||p_hier_obj_def_name||''','||
1260 l_eff_start_date_str||', '||l_eff_end_date_str||', NULL, ''Y''';
1261
1262 l_insert_sql := l_insert_clause||l_select_clause||l_from_clause||l_where_clause;
1263
1264 RETURN l_insert_sql;
1265
1266 END;
1267
1268
1269 -----------------------------------------------
1270 -- Build Insert Hier Valuesets SQL --
1271 -----------------------------------------------
1272 FUNCTION get_hier_vs_sql(p_hier_obj_name IN VARCHAR2, p_source_db_link IN VARCHAR2) RETURN VARCHAR2
1273
1274 IS
1275
1276 l_insert_clause VARCHAR2(100) := 'INSERT INTO FEM_HIER_VALUE_SETS_T';
1277
1278 l_insert_select VARCHAR2(1500) := '(SELECT '''||p_hier_obj_name||''', VS.VALUE_SET_DISPLAY_CODE, '''||USERENV('LANG')||''', ''LOAD'', ''Y''';
1279
1280
1281 l_insert_from VARCHAR2(200) := ' FROM FEM_HIER_VALUE_SETS@'||p_source_db_link||
1282 ' HVS, FEM_VALUE_SETS_B@'||p_source_db_link||' VS';
1283 l_insert_where VARCHAR2(500) := ' WHERE HVS.HIERARCHY_OBJ_ID = :hier_obj_id'||
1284 ' AND HVS.VALUE_SET_ID = VS.VALUE_SET_ID)';
1285
1286 l_insert_sql VARCHAR2(32767) := l_insert_clause || l_insert_select || l_insert_from || l_insert_where;
1287
1288 BEGIN
1289 RETURN l_insert_sql;
1290 END;
1291
1292
1293 -----------------------------------------------
1294 -- Build Insert Hier Dimension Group SQL --
1295 -----------------------------------------------
1296 FUNCTION get_hier_dg_sql(p_hier_obj_name IN VARCHAR2, p_source_db_link IN VARCHAR2) RETURN VARCHAR2
1297
1298 IS
1299
1300 l_insert_clause VARCHAR2(100) := 'INSERT INTO FEM_HIER_DIM_GRPS_T';
1301
1302 l_insert_select VARCHAR2(1500) := '(SELECT '''||p_hier_obj_name||''', '''||USERENV('LANG')||''', ''LOAD'', DG.DIMENSION_GROUP_DISPLAY_CODE, ''Y''';
1303
1304
1305 l_insert_from VARCHAR2(200) := ' FROM FEM_HIER_DIMENSION_GRPS@'||p_source_db_link||
1306 ' HDG, FEM_DIMENSION_GRPS_B@'||p_source_db_link||' DG';
1307 l_insert_where VARCHAR2(500) := ' WHERE HDG.HIERARCHY_OBJ_ID = :hier_obj_id'||
1308 ' AND HDG.DIMENSION_GROUP_ID = DG.DIMENSION_GROUP_ID)';
1309
1310 l_insert_sql VARCHAR2(32767) := l_insert_clause || l_insert_select || l_insert_from || l_insert_where;
1311
1312 BEGIN
1313 RETURN l_insert_sql;
1314 END;
1315
1316 -----------------------------------------------
1317 -- Build Insert Dimension Groups _B SQL --
1318 -----------------------------------------------
1319 FUNCTION get_insert_dim_grp_b_sql(p_source_db_link IN VARCHAR2) RETURN VARCHAR2
1320
1321 IS
1322
1323 l_insert_clause VARCHAR2(100) := 'INSERT INTO FEM_DIMENSION_GRPS_B_T';
1324 l_insert_select VARCHAR2(200) := ' (SELECT B.DIMENSION_GROUP_DISPLAY_CODE, D.DIMENSION_VARCHAR_LABEL,'||
1325 'B.DIMENSION_GROUP_SEQ, ''LOAD'', B.TIME_GROUP_TYPE_CODE, ''Y''';
1326
1327 l_insert_from VARCHAR2(200) := ' FROM FEM_DIMENSION_GRPS_B@'||p_source_db_link||
1328 ' B, FEM_DIMENSIONS_B@'||p_source_db_link||' D';
1329 l_insert_where VARCHAR2(500) := ' WHERE B.DIMENSION_ID = D.DIMENSION_ID'||
1330 ' AND D.DIMENSION_ID = :dimension_id'||
1331 ' AND NOT EXISTS (SELECT 1 FROM FEM_DIMENSION_GRPS_B_T'||
1332 ' WHERE DIMENSION_GROUP_DISPLAY_CODE = B.DIMENSION_GROUP_DISPLAY_CODE'||
1333 ' AND DIMENSION_VARCHAR_LABEL = D.DIMENSION_VARCHAR_LABEL'||
1334 ' AND DIMENSION_GROUP_SEQ = B.DIMENSION_GROUP_SEQ))';
1335
1336 l_insert_sql VARCHAR2(32767) := l_insert_clause || l_insert_select || l_insert_from || l_insert_where;
1337
1338 BEGIN
1339 RETURN l_insert_sql;
1340 END;
1341
1342
1343
1344
1345 -----------------------------------------------
1346 -- Build Insert Dimension Groups _TL SQL --
1347 -----------------------------------------------
1348 FUNCTION get_insert_dim_grp_tl_sql(p_source_db_link IN VARCHAR2) RETURN VARCHAR2
1349
1350 IS
1351
1352 l_insert_clause VARCHAR2(100) := 'INSERT INTO FEM_DIMENSION_GRPS_TL_T';
1353 l_insert_select VARCHAR2(200) := ' (SELECT B.DIMENSION_GROUP_DISPLAY_CODE, TL.LANGUAGE,'||
1354 ' TL.DIMENSION_GROUP_NAME, TL.DESCRIPTION, ''LOAD'','||
1355 ' D.DIMENSION_VARCHAR_LABEL, ''Y''';
1356
1357 l_insert_from VARCHAR2(200) := ' FROM FEM_DIMENSION_GRPS_B@'||p_source_db_link||
1358 ' B, FEM_DIMENSION_GRPS_TL@'||p_source_db_link||
1359 ' TL, FEM_DIMENSIONS_B@'||p_source_db_link||' D';
1360 l_insert_where VARCHAR2(1000) := ' WHERE B.DIMENSION_GROUP_ID = TL.DIMENSION_GROUP_ID'||
1361 ' AND B.DIMENSION_ID = D.DIMENSION_ID'||
1362 -- ' AND B.DIMENSION_ID = TL.DIMENSION_ID'||
1363 ' AND TL.DIMENSION_ID = :dimension_id'||
1364 ' AND EXISTS (SELECT 1 FROM FND_LANGUAGES L'||
1365 ' WHERE TL.LANGUAGE = L.LANGUAGE_CODE AND L.INSTALLED_FLAG IN (''I'',''B''))'||
1366 ' AND NOT EXISTS (SELECT 1 FROM FEM_DIMENSION_GRPS_TL_T'||
1367 ' WHERE DIMENSION_GROUP_DISPLAY_CODE = B.DIMENSION_GROUP_DISPLAY_CODE'||
1368 ' AND DIMENSION_VARCHAR_LABEL = D.DIMENSION_VARCHAR_LABEL'||
1369 ' AND LANGUAGE = TL.LANGUAGE'||
1370 ' AND DIMENSION_GROUP_NAME = TL.DIMENSION_GROUP_NAME))';
1371
1372 l_insert_sql VARCHAR2(32767) := l_insert_clause || l_insert_select || l_insert_from || l_insert_where;
1373
1374 BEGIN
1375
1376 RETURN l_insert_sql;
1377
1378 END;
1379
1380
1381
1382
1383 ------------------------------------------------------------------------------
1384 -- Insert Value Sets --
1385 -- 7/25/2006 Bug#5331497 default_member_id, default_load_member_id and
1386 -- default_hierarchy_obj_id should be null when value_set
1387 -- is created, since these values all come from sequence
1388 -- that is not identical between source and target db
1389 -------------------------------------------------------------------------------
1390
1391 PROCEDURE insert_value_sets(p_source_db_link IN VARCHAR2)
1392
1393 IS
1394
1395 l_row_id rowid_type; --ROWID;
1396 l_default_member_id number_type; --FEM_VALUE_SETS_B.DEFAULT_MEMBER_ID%TYPE;
1397 l_vs_dc varchar2_150_type; --FEM_VALUE_SETS_B.VALUE_SET_DISPLAY_CODE%TYPE;
1398 l_default_hier_obj_id number_type; --FEM_VALUE_SETS_B.DEFAULT_HIERARCHY_OBJ_ID%TYPE;
1399 l_read_only_flag flag_type; --FEM_VALUE_SETS_B.READ_ONLY_FLAG%TYPE;
1400 l_vs_id number_type; --FEM_VALUE_SETS_B.VALUE_SET_ID%TYPE;
1401 l_vs_name varchar2_150_type; --FEM_VALUE_SETS_TL.VALUE_SET_NAME%TYPE;
1402 l_vs_desc desc_type; --FEM_VALUE_SETS_TL.DESCRIPTION%TYPE;
1403 l_mbr_last_row NUMBER;
1404 l_dup_vs_name VARCHAR2(150);
1405
1406 l_vs_sql VARCHAR2(1000) :=
1407 'SELECT ROW_ID, FEM_VALUE_SETS_B_S.NEXTVAL,'||
1408 ' VALUE_SET_NAME, DESCRIPTION, null,'||
1409 ' VALUE_SET_DISPLAY_CODE,'||
1410 ' null,'||
1411 ' READ_ONLY_FLAG'||
1412 ' FROM FEM_VALUE_SETS_VL@'||p_source_db_link||' A'||
1413 ' WHERE A.DIMENSION_ID = '||gv_src_dim_props_rec.DIMENSION_ID||
1414 ' AND NOT EXISTS (SELECT 1'||
1415 ' FROM FEM_VALUE_SETS_VL'||
1416 ' WHERE VALUE_SET_DISPLAY_CODE = A.VALUE_SET_DISPLAY_CODE)';
1417
1418 TYPE INSERT_VS_CURSOR_TYPE IS REF CURSOR;
1419
1420 insert_vs_cur INSERT_VS_CURSOR_TYPE;
1421
1422 BEGIN
1423
1424 FEM_ENGINES_PKG.TECH_MESSAGE
1425 (p_severity => c_log_level_2,
1426 p_module => c_block||'.l_vs_sql',
1427 p_msg_text => l_vs_sql);
1428
1429 OPEN insert_vs_cur FOR l_vs_sql;
1430
1431 loop
1432
1433 FETCH insert_vs_cur BULK COLLECT INTO
1434 l_row_id,
1435 l_vs_id,
1436 l_vs_name,
1437 l_vs_desc,
1438 l_default_member_id,
1439 l_vs_dc,
1440 l_default_hier_obj_id,
1441 l_read_only_flag
1442 LIMIT 10000;
1443
1444 l_mbr_last_row := l_vs_id.LAST;
1445
1446 IF (l_mbr_last_row IS NULL)
1447 THEN
1448 EXIT;
1449 END IF;
1450
1451 FOR i IN 1..l_mbr_last_row
1452 LOOP
1453
1454
1455 BEGIN
1456
1457 SELECT VALUE_SET_NAME
1458 INTO l_dup_vs_name
1459 FROM FEM_VALUE_SETS_VL
1460 WHERE VALUE_SET_NAME = l_vs_name(i);
1461
1462 IF (SQL%FOUND) THEN
1463 FEM_ENGINES_PKG.TECH_MESSAGE
1464 (p_severity => c_log_level_2,
1465 p_module => c_block||'.insert_value_sets',
1466 p_msg_text => 'Duplicate vs name found in db '|| l_dup_vs_name);
1467 END IF;
1468
1469 EXCEPTION
1470 WHEN NO_DATA_FOUND THEN
1471 --VS IS GOOD--
1472 NULL;
1473 END;
1474
1475 FEM_VALUE_SETS_PKG.INSERT_ROW(X_ROWID => l_row_id(i),
1476 X_VALUE_SET_ID => l_vs_id(i),
1477 X_DEFAULT_LOAD_MEMBER_ID => null,
1478 X_DEFAULT_MEMBER_ID => null,
1479 X_OBJECT_VERSION_NUMBER => 1,
1480 X_DEFAULT_HIERARCHY_OBJ_ID => null,
1481 X_READ_ONLY_FLAG => l_read_only_flag(i),
1482 X_VALUE_SET_DISPLAY_CODE => l_vs_dc(i),
1483 X_DIMENSION_ID => gv_src_dim_props_rec.DIMENSION_ID,
1484 X_VALUE_SET_NAME => l_vs_name(i),
1485 X_DESCRIPTION => l_vs_desc(i),
1486 X_CREATION_DATE => sysdate,
1487 X_CREATED_BY => gv_apps_user_id,
1488 X_LAST_UPDATE_DATE => sysdate,
1489 X_LAST_UPDATED_BY => gv_apps_user_id,
1490 X_LAST_UPDATE_LOGIN => gv_login_id);
1491
1492 END LOOP;
1493 end loop;
1494
1495 END;
1496
1497
1498
1499
1500
1501 -----------------------------------------------
1502 -- Insert Calendars --
1503 -----------------------------------------------
1504
1505 PROCEDURE insert_calendars(p_source_db_link IN VARCHAR2)
1506
1507 IS
1508
1509 l_row_id rowid_type;
1510 l_cal_dc varchar2_150_type;
1511 l_read_only_flag flag_type;
1512 l_personal_flag flag_type;
1513 l_enabled_flag flag_type;
1514 l_cal_id number_type; --FEM_VALUE_SETS_B.VALUE_SET_ID%TYPE;
1515 l_cal_name varchar2_150_type; --FEM_VALUE_SETS_TL.VALUE_SET_NAME%TYPE;
1516 l_cal_desc desc_type; --FEM_VALUE_SETS_TL.DESCRIPTION%TYPE;
1517 l_mbr_last_row NUMBER;
1518 l_dup_cal_name VARCHAR2(150);
1519
1520 l_cal_sql VARCHAR2(1000) :=
1521 'SELECT ROW_ID, FEM_CALENDARS_B_S.NEXTVAL,'||
1522 ' CALENDAR_NAME, DESCRIPTION, ENABLED_FLAG,'||
1523 ' CALENDAR_DISPLAY_CODE,'||
1524 ' PERSONAL_FLAG,'||
1525 ' READ_ONLY_FLAG'||
1526 ' FROM FEM_CALENDARS_VL@'||p_source_db_link||' A'||
1527 ' WHERE NOT EXISTS (SELECT 1'||
1528 ' FROM FEM_CALENDARS_VL'||
1529 ' WHERE CALENDAR_DISPLAY_CODE = A.CALENDAR_DISPLAY_CODE)';
1530
1531 TYPE INSERT_CAL_CURSOR_TYPE IS REF CURSOR;
1532
1533 insert_cal_cur INSERT_CAL_CURSOR_TYPE;
1534
1535 BEGIN
1536
1537 FEM_ENGINES_PKG.TECH_MESSAGE
1538 (p_severity => c_log_level_2,
1539 p_module => c_block||'.l_cal_sql',
1540 p_msg_text => l_cal_sql);
1541
1542 OPEN insert_cal_cur FOR l_cal_sql;
1543
1544 loop
1545
1546 FETCH insert_cal_cur BULK COLLECT INTO
1547 l_row_id,
1548 l_cal_id,
1549 l_cal_name,
1550 l_cal_desc,
1551 l_enabled_flag,
1552 l_cal_dc,
1553 l_personal_flag,
1554 l_read_only_flag
1555 LIMIT 10000;
1556
1557 l_mbr_last_row := l_cal_id.LAST;
1558
1559 IF (l_mbr_last_row IS NULL)
1560 THEN
1561 EXIT;
1562 END IF;
1563
1564 FOR i IN 1..l_mbr_last_row
1565 LOOP
1566
1567 BEGIN
1568
1569 SELECT CALENDAR_NAME
1570 INTO l_dup_cal_name
1571 FROM FEM_CALENDARS_VL
1572 WHERE CALENDAR_NAME = l_cal_name(i);
1573
1574 EXCEPTION
1575 WHEN NO_DATA_FOUND THEN
1576
1577 --CALENDAR CAN BE INSERTED--
1578
1579 FEM_CALENDARS_PKG.INSERT_ROW (
1580 X_ROWID => l_row_id(i),
1581 X_CALENDAR_ID => l_cal_id(i),
1582 X_READ_ONLY_FLAG => l_read_only_flag(i),
1583 X_PERSONAL_FLAG => l_personal_flag(i),
1584 X_ENABLED_FLAG => l_enabled_flag(i),
1585 X_CALENDAR_DISPLAY_CODE => l_cal_dc(i),
1586 X_OBJECT_VERSION_NUMBER => 1,
1587 X_CALENDAR_NAME => l_cal_name(i),
1588 X_DESCRIPTION => l_cal_desc(i),
1589 X_CREATION_DATE => sysdate,
1590 X_CREATED_BY => gv_apps_user_id,
1591 X_LAST_UPDATE_DATE => sysdate,
1592 X_LAST_UPDATED_BY => gv_apps_user_id,
1593 X_LAST_UPDATE_LOGIN => gv_login_id);
1594 END;
1595
1596 END LOOP;
1597 end loop;
1598
1599 END;
1600
1601 -----------------------------------------------
1602 -- Process Execution --
1603 -----------------------------------------------
1604 PROCEDURE register_process_execution (p_object_id IN NUMBER
1605 ,p_obj_def_id IN NUMBER
1606 ,p_execution_mode IN VARCHAR2)
1607 IS
1608
1609 v_API_return_status VARCHAR2(30);
1610 v_exec_state VARCHAR2(30); -- NORMAL, RESTART, RERUN
1611 v_num_msg NUMBER;
1612 v_stmt_type fem_pl_tables.statement_type%TYPE;
1613 i PLS_INTEGER;
1614 v_msg_count NUMBER;
1615 v_msg_data VARCHAR2(4000);
1616 v_previous_request_id NUMBER;
1617 v_exec_lock_exists VARCHAR2(5);
1618 v_calling_context VARCHAR2(15);
1619
1620
1621 Exec_Lock_Exists EXCEPTION;
1622 e_pl_register_req_failed EXCEPTION;
1623 e_exec_lock_failed EXCEPTION;
1624
1625
1626 BEGIN
1627 --x_completion_status := 'SUCCESS';
1628
1629 FEM_ENGINES_PKG.Tech_Message
1630 (p_severity => c_log_level_2,
1631 p_module => c_block||'.'||'Register_process_execution',
1632 p_msg_text => 'BEGIN');
1633
1634 -- Call the FEM_PL_PKG.Register_Request API procedure to register
1635 -- the concurrent request in FEM_PL_REQUESTS.
1636
1637 FEM_PL_PKG.Register_Request
1638 (P_API_VERSION => c_api_version,
1639 P_COMMIT => c_false,
1640 P_CAL_PERIOD_ID => null,
1641 P_LEDGER_ID => null,
1642 P_DATASET_IO_OBJ_DEF_ID => null,
1643 P_OUTPUT_DATASET_CODE => null,
1644 P_SOURCE_SYSTEM_CODE => null,
1645 P_EFFECTIVE_DATE => null,
1646 P_RULE_SET_OBJ_DEF_ID => null,
1647 P_RULE_SET_NAME => null,
1648 P_REQUEST_ID => gv_request_id,
1649 P_USER_ID => gv_apps_user_id,
1650 P_LAST_UPDATE_LOGIN => gv_login_id,
1651 P_PROGRAM_ID => gv_pgm_id,
1652 P_PROGRAM_LOGIN_ID => gv_login_id,
1653 P_PROGRAM_APPLICATION_ID => gv_pgm_app_id,
1654 P_EXEC_MODE_CODE => p_execution_mode,
1655 P_DIMENSION_ID => null,
1656 P_TABLE_NAME => null,
1657 P_HIERARCHY_NAME => null,
1658 X_MSG_COUNT => v_msg_count,
1659 X_MSG_DATA => v_msg_data,
1660 X_RETURN_STATUS => v_API_return_status);
1661
1662 FEM_ENGINES_PKG.Tech_Message
1663 (p_severity => c_log_level_1,
1664 p_module => c_block||'.'||'Register_request.v_api_return_status',
1665 p_msg_text => v_API_return_status);
1666
1667 IF v_API_return_status NOT IN ('S') THEN
1668 RAISE e_pl_register_req_failed;
1669 END IF;
1670 -- Check for process locks and process overlaps and register
1671 -- the execution in FEM_PL_OBJECT_EXECUTIONS, obtaining an execution lock.
1672
1673
1674 FEM_PL_PKG.obj_execution_lock_exists
1675 (p_object_id => p_object_id,
1676 p_exec_object_definition_id => p_obj_def_id,
1677 p_calling_context => v_calling_context,
1678 x_exec_lock_exists => v_exec_lock_exists,
1679 x_exec_state => v_exec_state,
1680 X_prev_request_id => v_previous_request_id,
1681 x_msg_count => v_msg_count,
1682 x_msg_data => v_msg_data);
1683
1684
1685 FEM_PL_PKG.Register_Object_Execution
1686 (P_API_VERSION => c_api_version,
1687 P_COMMIT => c_false,
1688 P_REQUEST_ID => gv_request_id,
1689 P_OBJECT_ID => p_object_id,
1690 P_EXEC_OBJECT_DEFINITION_ID => p_obj_def_id,
1691 P_USER_ID => gv_apps_user_id,
1692 P_LAST_UPDATE_LOGIN => gv_login_id,
1693 P_EXEC_MODE_CODE => p_execution_mode,
1694 X_EXEC_STATE => v_exec_state,
1695 X_PREV_REQUEST_ID => v_previous_request_id,
1696 X_MSG_COUNT => v_msg_count,
1697 X_MSG_DATA => v_msg_data,
1698 X_RETURN_STATUS => v_API_return_status);
1699
1700 IF v_API_return_status NOT IN ('S') THEN
1701 -- Lock exists or API call failed
1702 RAISE e_exec_lock_failed;
1703 END IF;
1704
1705 FEM_PL_PKG.Register_Object_Def
1706 (P_API_VERSION => c_api_version,
1707 P_COMMIT => c_false,
1708 P_REQUEST_ID => gv_request_id,
1709 P_OBJECT_ID => p_object_id,
1710 P_OBJECT_DEFINITION_ID => p_obj_def_id,
1711 P_USER_ID => gv_apps_user_id,
1712 P_LAST_UPDATE_LOGIN => gv_login_id,
1713 X_MSG_COUNT => v_msg_count,
1714 X_MSG_DATA => v_msg_data,
1715 X_RETURN_STATUS => v_API_return_status);
1716
1717 IF v_API_return_status NOT IN ('S') THEN
1718 -- Lock exists or API call failed
1719 RAISE e_exec_lock_failed;
1720 END IF;
1721
1722 -- Successful completion
1723
1724 FEM_ENGINES_PKG.Tech_Message
1725 (p_severity => c_log_level_1,
1726 p_module => c_block||'.'||'Register_process_execution',
1727 p_msg_text => 'END');
1728
1729 COMMIT;
1730
1731 EXCEPTION
1732 WHEN e_pl_register_req_failed THEN
1733 -- get errors from the stack
1734 Get_Put_Messages (
1735 p_msg_count => v_msg_count,
1736 p_msg_data => v_msg_data);
1737
1738 -- display user message
1739 FEM_ENGINES_PKG.USER_MESSAGE
1740 (P_APP_NAME => c_fem
1741 ,P_MSG_NAME => G_PL_REG_REQUEST_ERR);
1742
1743 FEM_ENGINES_PKG.USER_MESSAGE
1744 (P_APP_NAME => c_fem
1745 ,P_MSG_NAME => G_PL_MIGRATION_ERROR);
1746
1747 --x_completion_status := 'ERROR';
1748
1749 RAISE e_pl_registration_failed;
1750
1751 WHEN e_exec_lock_failed THEN
1752 -- get errors from the stack
1753 Get_Put_Messages (
1754 p_msg_count => v_msg_count,
1755 p_msg_data => v_msg_data);
1756
1757 FEM_ENGINES_PKG.USER_MESSAGE
1758 (P_APP_NAME => c_fem
1759 ,P_MSG_NAME => G_PL_OBJ_EXEC_LOCK_ERR);
1760
1761
1762 FEM_ENGINES_PKG.Tech_Message
1763 (p_severity => c_log_level_1,
1764 p_module => c_block||'.'||'Register_process_execution',
1765 p_msg_text => 'raising Exec_Lock_failed');
1766
1767 FEM_PL_PKG.Unregister_Request(
1768 P_API_VERSION => c_api_version,
1769 P_COMMIT => c_true,
1770 P_REQUEST_ID => gv_request_id,
1771 X_MSG_COUNT => v_msg_count,
1772 X_MSG_DATA => v_msg_data,
1773 X_RETURN_STATUS => v_API_return_status);
1774 -- Technical messages have already been logged by the API;
1775
1776 --x_completion_status := 'ERROR';
1777
1778 RAISE e_pl_registration_failed;
1779
1780 END Register_Process_Execution;
1781
1782 -----------------------------------------------
1783 -- Pre-Process Members --
1784 -----------------------------------------------
1785 PROCEDURE PRE_PROCESS_MEMBERS(p_source_db_link IN VARCHAR2,
1786 p_dim_varchar_lbl IN VARCHAR2,
1787 p_autoload_dims IN VARCHAR2,
1788 p_migrate_dependent_dims IN VARCHAR2,
1789 p_version_mode IN VARCHAR2,
1790 p_version_disp_cd IN VARCHAR2,
1791 p_version_name IN VARCHAR2,
1792 p_source_user_dim_name IN VARCHAR2,
1793 p_hier_obj_name IN VARCHAR2,
1794 p_hier_obj_def_name IN VARCHAR2)
1795
1796 IS
1797
1798 c_proc_name VARCHAR2(30):= 'pre_process_members';
1799
1800 l_verify_db_link VARCHAR2(10);
1801 l_error_code VARCHAR2(10);
1802 l_return_code VARCHAR2(10);
1803 l_pl_exec_status VARCHAR2(30);
1804 l_dim_obj_id NUMBER;
1805 l_delete_b_sql VARCHAR2(200);
1806 l_delete_tl_sql VARCHAR2(200);
1807 l_delete_attr_sql VARCHAR2(200);
1808 l_delete_dg_b_sql VARCHAR2(200);
1809 l_delete_dg_tl_sql VARCHAR2(200);
1810
1811 BEGIN
1812
1813 l_verify_db_link := validate_db_link(p_source_db_link);
1814
1815 --TO DO: CHECK LANGUAGE SETTINGS -- TGT SESSION LANG MUST BE INSTALLED IN SRC')--
1816
1817 validate_parameters(p_source_db_link => p_source_db_link,
1818 p_autoload_dims => p_autoload_dims,
1819 p_migrate_dependent_dims => p_migrate_dependent_dims,
1820 p_version_mode => p_version_mode,
1821 p_version_disp_cd => p_version_disp_cd,
1822 p_version_name => p_version_name,
1823 p_source_user_dim_name => p_source_user_dim_name,
1824 p_hier_obj_name => p_hier_obj_name,
1825 p_hier_obj_def_name => p_hier_obj_def_name);
1826
1827
1828
1829 get_dimension_info(p_dim_varchar_lbl,
1830 p_source_user_dim_name,
1831 p_source_db_link);
1832
1833
1834 register_process_execution(p_object_id => gv_dim_props_rec.MIGRATION_OBJ_ID,
1835 p_obj_def_id => gv_dim_props_rec.MIGRATION_OBJ_DEF_ID,
1836 p_execution_mode => 'S');
1837
1838
1839 --delete existing data (move to procedure)
1840 l_delete_b_sql := 'DELETE FROM '||gv_dim_props_rec.INTF_MEMBER_B_TABLE_NAME||
1841 ' WHERE CREATED_BY_DIM_MIGRATION_FLAG = ''Y''';
1842
1843 l_delete_tl_sql := 'DELETE FROM '||gv_dim_props_rec.INTF_MEMBER_TL_TABLE_NAME||
1844 ' WHERE CREATED_BY_DIM_MIGRATION_FLAG = ''Y''';
1845
1846 l_delete_attr_sql := 'DELETE FROM '||gv_dim_props_rec.INTF_ATTRIBUTE_TABLE_NAME||
1847 ' WHERE CREATED_BY_DIM_MIGRATION_FLAG = ''Y''';
1848
1849 l_delete_dg_b_sql := 'DELETE FROM FEM_DIMENSION_GRPS_B_T'||
1850 ' WHERE DIMENSION_VARCHAR_LABEL = :dim_varchar_lbl AND CREATED_BY_DIM_MIGRATION_FLAG = ''Y''';
1851
1852 l_delete_dg_tl_sql := 'DELETE FROM FEM_DIMENSION_GRPS_TL_T'||
1853 ' WHERE DIMENSION_VARCHAR_LABEL = :dim_varchar_lbl AND CREATED_BY_DIM_MIGRATION_FLAG = ''Y''';
1854
1855 IF (gv_dim_props_rec.INTF_MEMBER_B_TABLE_NAME IS NOT NULL) THEN
1856
1857 FEM_ENGINES_PKG.TECH_MESSAGE
1858 (p_severity => c_log_level_2,
1859 p_module => c_block||'.l_delete_b_sql',
1860 p_msg_text => l_delete_b_sql);
1861
1862 EXECUTE IMMEDIATE l_delete_b_sql;
1863 END IF;
1864
1865 IF (gv_dim_props_rec.INTF_MEMBER_TL_TABLE_NAME IS NOT NULL) THEN
1866
1867 FEM_ENGINES_PKG.TECH_MESSAGE
1868 (p_severity => c_log_level_2,
1869 p_module => c_block||'.l_delete_tl_sql',
1870 p_msg_text => l_delete_tl_sql);
1871
1872 EXECUTE IMMEDIATE l_delete_tl_sql;
1873 END IF;
1874
1875 IF (gv_dim_props_rec.INTF_ATTRIBUTE_TABLE_NAME IS NOT NULL) THEN
1876
1877 FEM_ENGINES_PKG.TECH_MESSAGE
1878 (p_severity => c_log_level_2,
1879 p_module => c_block||'.l_delete_attr_sql',
1880 p_msg_text => l_delete_attr_sql);
1881
1882 EXECUTE IMMEDIATE l_delete_attr_sql;
1883 END IF;
1884
1885
1886 FEM_ENGINES_PKG.TECH_MESSAGE
1887 (p_severity => c_log_level_2,
1888 p_module => c_block||'.l_delete_dg_b_sql',
1889 p_msg_text => l_delete_dg_b_sql);
1890
1891 EXECUTE IMMEDIATE l_delete_dg_b_sql USING p_dim_varchar_lbl;
1892
1893
1894 FEM_ENGINES_PKG.TECH_MESSAGE
1895 (p_severity => c_log_level_2,
1896 p_module => c_block||'.l_delete_dg_tl_sql',
1897 p_msg_text => l_delete_dg_tl_sql);
1898
1899 EXECUTE IMMEDIATE l_delete_dg_tl_sql USING p_dim_varchar_lbl;
1900
1901 COMMIT;
1902
1903
1904 EXCEPTION
1905
1906 WHEN e_dimension_not_supported THEN
1907 FEM_ENGINES_PKG.TECH_MESSAGE
1908 (p_severity => c_log_level_5
1909 ,p_module => c_block||'.'||c_proc_name||'.Exception'
1910 ,p_app_name => c_fem
1911 ,p_msg_name => G_DIM_NOT_SUPPORTED
1912 ,P_TOKEN1 => 'DIM_NAME'
1913 ,P_VALUE1 => p_dim_varchar_lbl);
1914
1915 FEM_ENGINES_PKG.USER_MESSAGE
1916 (p_app_name => c_fem
1917 ,p_msg_name => G_DIM_NOT_SUPPORTED
1918 ,P_TOKEN1 => 'DIM_NAME'
1919 ,P_VALUE1 => p_dim_varchar_lbl);
1920
1921 RAISE e_main_terminate;
1922
1923 WHEN e_invalid_dimension THEN
1924 FEM_ENGINES_PKG.TECH_MESSAGE
1925 (p_severity => c_log_level_5
1926 ,p_module => c_block||'.'||c_proc_name||'.Exception'
1927 ,p_app_name => c_fem
1928 ,p_msg_name => G_INVALID_DIMENSION
1929 ,P_TOKEN1 => 'DIM_LBL'
1930 ,P_VALUE1 => p_dim_varchar_lbl);
1931
1932 FEM_ENGINES_PKG.USER_MESSAGE
1933 (p_app_name => c_fem
1934 ,p_msg_name => G_INVALID_DIMENSION
1935 ,P_TOKEN1 => 'DIM_LBL'
1936 ,P_VALUE1 => p_dim_varchar_lbl);
1937
1938 RAISE e_main_terminate;
1939
1940 WHEN e_db_link_not_registered THEN
1941 FEM_ENGINES_PKG.TECH_MESSAGE
1942 (p_severity => c_log_level_5
1943 ,p_module => c_block||'.'||c_proc_name||'.Exception'
1944 ,p_app_name => c_fem
1945 ,p_msg_name => G_DB_LINK_NOT_REGISTERED
1946 ,P_TOKEN1 => 'DB_LINK'
1947 ,P_VALUE1 => p_source_db_link);
1948
1949 FEM_ENGINES_PKG.USER_MESSAGE
1950 (p_app_name => c_fem
1951 ,p_msg_name => G_DB_LINK_NOT_REGISTERED
1952 ,P_TOKEN1 => 'DB_LINK'
1953 ,P_VALUE1 => p_source_db_link);
1954
1955 RAISE e_main_terminate;
1956
1957 WHEN e_db_link_not_functional THEN
1958 FEM_ENGINES_PKG.TECH_MESSAGE
1959 (p_severity => c_log_level_5
1960 ,p_module => c_block||'.'||c_proc_name||'.Exception'
1961 ,p_app_name => c_fem
1962 ,p_msg_name => G_DB_LINK_NOT_FUNCTIONAL
1963 ,P_TOKEN1 => 'DB_LINK'
1964 ,P_VALUE1 => p_source_db_link);
1965
1966 FEM_ENGINES_PKG.USER_MESSAGE
1967 (p_app_name => c_fem
1968 ,p_msg_name => G_DB_LINK_NOT_FUNCTIONAL
1969 ,P_TOKEN1 => 'DB_LINK'
1970 ,P_VALUE1 => p_source_db_link);
1971
1972 RAISE e_main_terminate;
1973
1974 WHEN e_invalid_version_param THEN
1975 FEM_ENGINES_PKG.TECH_MESSAGE
1976 (p_severity => c_log_level_5
1977 ,p_module => c_block||'.'||c_proc_name||'.Exception'
1978 ,p_app_name => c_fem
1979 ,p_msg_name => G_INVALID_VERSION_PARAM
1980 ,P_TOKEN1 => 'VERSION_NAME'
1981 ,P_VALUE1 => p_version_name);
1982
1983 FEM_ENGINES_PKG.USER_MESSAGE
1984 (p_app_name => c_fem
1985 ,p_msg_name => G_INVALID_VERSION_PARAM
1986 ,P_TOKEN1 => 'VERSION_NAME'
1987 ,P_VALUE1 => p_version_name);
1988
1989 RAISE e_main_terminate;
1990
1991
1992 WHEN e_dim_not_user_extensible THEN
1993 FEM_ENGINES_PKG.TECH_MESSAGE
1994 (p_severity => c_log_level_5
1995 ,p_module => c_block||'.'||c_proc_name||'.Exception'
1996 ,p_app_name => c_fem
1997 ,p_msg_name => G_USER_DIM_MISMATCH
1998 ,P_TOKEN1 => 'USER_DIM_NAME'
1999 ,P_VALUE1 => p_source_user_dim_name
2000 ,P_TOKEN2 => 'DIM_NAME'
2001 ,P_VALUE2 => p_dim_varchar_lbl);
2002
2003 FEM_ENGINES_PKG.USER_MESSAGE
2004 (p_app_name => c_fem
2005 ,p_msg_name => G_USER_DIM_MISMATCH
2006 ,P_TOKEN1 => 'USER_DIM_NAME'
2007 ,P_VALUE1 => p_source_user_dim_name
2008 ,P_TOKEN2 => 'DIM_NAME'
2009 ,P_VALUE2 => p_dim_varchar_lbl);
2010
2011 RAISE e_main_terminate;
2012
2013
2014 WHEN e_src_dim_not_user_extensible THEN
2015 FEM_ENGINES_PKG.TECH_MESSAGE
2016 (p_severity => c_log_level_5
2017 ,p_module => c_block||'.'||c_proc_name||'.Exception'
2018 ,p_app_name => c_fem
2019 ,p_msg_name => G_INVALID_SRC_USER_DIM
2020 ,P_TOKEN1 => 'USER_DIM_NAME'
2021 ,P_VALUE1 => p_source_user_dim_name);
2022
2023 FEM_ENGINES_PKG.USER_MESSAGE
2024 (p_app_name => c_fem
2025 ,p_msg_name => G_INVALID_SRC_USER_DIM
2026 ,P_TOKEN1 => 'USER_DIM_NAME'
2027 ,P_VALUE1 => p_source_user_dim_name);
2028
2029 RAISE e_main_terminate;
2030
2031
2032 WHEN e_missing_version_params THEN
2033 FEM_ENGINES_PKG.TECH_MESSAGE
2034 (p_severity => c_log_level_5
2035 ,p_module => c_block||'.'||c_proc_name||'.Exception'
2036 ,p_app_name => c_fem
2037 ,p_msg_name => G_MISSING_VERSION_PARAM);
2038
2039 FEM_ENGINES_PKG.USER_MESSAGE
2040 (p_app_name => c_fem
2041 ,p_msg_name => G_MISSING_VERSION_PARAM);
2042
2043 RAISE e_main_terminate;
2044
2045
2046 WHEN e_invalid_obj_def THEN
2047 FEM_ENGINES_PKG.TECH_MESSAGE
2048 (p_severity => c_log_level_5
2049 ,p_module => c_block||'.'||c_proc_name||'.Exception'
2050 ,p_app_name => c_fem
2051 ,p_msg_name => G_UNHANDLED_ERROR
2052 ,P_TOKEN1 => 'MIGR_PROG'
2053 ,P_VALUE1 => 'Dimension Member Migration'
2054 ,P_TOKEN2 => 'SQLERRM'
2055 ,P_VALUE2 => SQLERRM);
2056
2057 FEM_ENGINES_PKG.USER_MESSAGE
2058 (p_app_name => c_fem
2059 ,p_msg_name => G_UNHANDLED_ERROR
2060 ,P_TOKEN1 => 'MIGR_PROG'
2061 ,P_VALUE1 => 'Dimension Member Migration'
2062 ,P_TOKEN2 => 'SQLERRM'
2063 ,P_VALUE2 => SQLERRM);
2064
2065 RAISE e_main_terminate;
2066
2067
2068 WHEN e_invalid_version_display_code THEN
2069 FEM_ENGINES_PKG.TECH_MESSAGE
2070 (p_severity => c_log_level_5
2071 ,p_module => c_block||'.'||c_proc_name||'.Exception'
2072 ,p_app_name => c_fem
2073 ,p_msg_name => G_INVALID_VERSION_DISP_CD
2074 ,P_TOKEN1 => 'VERSION_CODE'
2075 ,P_VALUE1 => p_version_disp_cd);
2076
2077
2078 FEM_ENGINES_PKG.USER_MESSAGE
2079 (p_app_name => c_fem
2080 ,p_msg_name => G_INVALID_VERSION_DISP_CD
2081 ,P_TOKEN1 => 'VERSION_CODE'
2082 ,P_VALUE1 => p_version_disp_cd);
2083
2084 RAISE e_main_terminate;
2085
2086
2087 WHEN e_invalid_version_name THEN
2088 FEM_ENGINES_PKG.TECH_MESSAGE
2089 (p_severity => c_log_level_5
2090 ,p_module => c_block||'.'||c_proc_name||'.Exception'
2091 ,p_app_name => c_fem
2092 ,p_msg_name => G_INVALID_VERSION_NAME
2093 ,P_TOKEN1 => 'VERSION_NAME'
2094 ,P_VALUE1 => p_version_name);
2095
2096 FEM_ENGINES_PKG.USER_MESSAGE
2097 (p_app_name => c_fem
2098 ,p_msg_name => G_INVALID_VERSION_NAME
2099 ,P_TOKEN1 => 'VERSION_NAME'
2100 ,P_VALUE1 => p_version_name);
2101
2102 RAISE e_main_terminate;
2103
2104
2105 WHEN OTHERS THEN
2106 FEM_ENGINES_PKG.TECH_MESSAGE
2107 (p_severity => c_log_level_5
2108 ,p_module => c_block||'.'||c_proc_name||'.Exception'
2109 ,p_app_name => c_fem
2110 ,p_msg_name => G_UNHANDLED_ERROR
2111 ,P_TOKEN1 => 'MIGR_PROG'
2112 ,P_VALUE1 => 'Dimension Member Migration'
2113 ,P_TOKEN2 => 'SQLERRM'
2114 ,P_VALUE2 => SQLERRM);
2115
2116 FEM_ENGINES_PKG.USER_MESSAGE
2117 (p_app_name => c_fem
2118 ,p_msg_name => G_UNHANDLED_ERROR
2119 ,P_TOKEN1 => 'MIGR_PROG'
2120 ,P_VALUE1 => 'Dimension Member Migration'
2121 ,P_TOKEN2 => 'SQLERRM'
2122 ,P_VALUE2 => SQLERRM);
2123
2124 RAISE e_main_terminate;
2125
2126
2127 END;
2128
2129
2130 -----------------------------------------------
2131 -- Process Members --
2132 -----------------------------------------------
2133
2134 PROCEDURE PROCESS_MEMBERS(p_dim_varchar_lbl IN VARCHAR2,
2135 p_version_mode IN VARCHAR2,
2136 p_source_db_link IN VARCHAR2)
2137
2138 IS
2139
2140 c_proc_name VARCHAR2(30):= 'process_members';
2141
2142 l_insert_b_sql VARCHAR2(32767);
2143 l_insert_tl_sql VARCHAR2(32767);
2144 l_insert_attr_sql VARCHAR2(32767);
2145
2146 l_insert_dim_grp_b_sql VARCHAR2(32767);
2147 l_insert_dim_grp_tl_sql VARCHAR2(32767);
2148
2149 l_mp_status VARCHAR2(160);
2150 l_mp_exception VARCHAR2(160);
2151 l_attr_sql VARCHAR2(1000);
2152 sql_length number;
2153 l_data_table VARCHAR2(100);
2154 l_max_value NUMBER;
2155 l_min_value NUMBER;
2156 l_condition VARCHAR2(100);
2157 l_synonym VARCHAR2(200);
2158
2159
2160 BEGIN
2161
2162
2163 l_insert_b_sql := get_insert_b_sql(p_source_db_link, p_dim_varchar_lbl);
2164
2165 FEM_ENGINES_PKG.TECH_MESSAGE
2166 (p_severity => c_log_level_2,
2167 p_module => c_block||'.l_insert_b_sql',
2168 p_msg_text => l_insert_b_sql);
2169
2170
2171 l_insert_tl_sql := get_insert_tl_sql(p_source_db_link, p_dim_varchar_lbl);
2172
2173 FEM_ENGINES_PKG.TECH_MESSAGE
2174 (p_severity => c_log_level_2,
2175 p_module => c_block||'.l_insert_tl_sql',
2176 p_msg_text => l_insert_tl_sql);
2177
2178 l_insert_attr_sql := get_dim_attr_sql(p_version_mode, p_source_db_link, p_dim_varchar_lbl);
2179
2180 FEM_ENGINES_PKG.TECH_MESSAGE
2181 (p_severity => c_log_level_2,
2182 p_module => c_block||'.l_insert_attr_sql',
2183 p_msg_text => l_insert_attr_sql);
2184
2185 l_insert_dim_grp_b_sql := get_insert_dim_grp_b_sql(p_source_db_link);
2186
2187 FEM_ENGINES_PKG.TECH_MESSAGE
2188 (p_severity => c_log_level_2,
2189 p_module => c_block||'.l_insert_dim_grp_b_sql',
2190 p_msg_text => l_insert_dim_grp_b_sql);
2191
2192 l_insert_dim_grp_tl_sql := get_insert_dim_grp_tl_sql(p_source_db_link);
2193
2194 FEM_ENGINES_PKG.TECH_MESSAGE
2195 (p_severity => c_log_level_2,
2196 p_module => c_block||'.l_insert_dim_grp_tl_sql',
2197 p_msg_text => l_insert_dim_grp_tl_sql);
2198
2199
2200 insert_value_sets(p_source_db_link);
2201 COMMIT;
2202
2203 IF (p_dim_varchar_lbl = 'CAL_PERIOD') THEN
2204 insert_calendars(p_source_db_link);
2205 COMMIT;
2206 END IF;
2207
2208
2209 BEGIN
2210
2211 EXECUTE IMMEDIATE l_insert_dim_grp_b_sql USING gv_src_dim_props_rec.DIMENSION_ID;
2212 COMMIT;
2213
2214 EXECUTE IMMEDIATE l_insert_dim_grp_tl_sql USING gv_src_dim_props_rec.DIMENSION_ID;
2215 COMMIT;
2216
2217 EXCEPTION
2218 WHEN OTHERS THEN
2219 RAISE e_main_terminate;
2220 END;
2221
2222
2223 IF (gv_src_dim_props_rec.MEMBER_B_TABLE_NAME IS NOT NULL) THEN
2224 fem_multi_proc_pkg.MASTER
2225 (X_PRG_STAT => l_mp_status,
2226 X_EXCEPTION_CODE => l_mp_exception,
2227 P_RULE_ID => gv_src_dim_props_rec.MIGRATION_OBJ_ID,
2228 P_ENG_STEP => 'ALL',
2229 P_DATA_TABLE => gv_src_dim_props_rec.MEMBER_B_TABLE_NAME,
2230 P_ENG_SQL => l_insert_b_sql,
2231 P_TABLE_ALIAS => 'B',
2232 P_RUN_NAME => 'PROCESS MEMBERS B',
2233 P_ENG_PRG => 'FEM_DIM_MEMBER_MIGRATION_PKG.PROCESS_MEMBERS',
2234 P_CONDITION => NULL,
2235 P_FAILED_REQ_ID => NULL
2236 ,P_SOURCE_DB_LINK => p_source_db_link
2237 );
2238
2239
2240 IF l_mp_status NOT IN ('COMPLETE:NORMAL') THEN
2241 IF l_mp_exception IN ('FEM_MP_NO_DATA_SLICES_ERR') THEN
2242 null;
2243 ELSE
2244 RAISE e_insert_b_exception;
2245 END IF;
2246 END IF;
2247
2248 END IF;
2249
2250 IF (gv_src_dim_props_rec.MEMBER_TL_TABLE_NAME IS NOT NULL) THEN
2251
2252 fem_multi_proc_pkg.MASTER
2253 (X_PRG_STAT => l_mp_status,
2254 X_EXCEPTION_CODE => l_mp_exception,
2255 P_RULE_ID => gv_src_dim_props_rec.MIGRATION_OBJ_ID,
2256 P_ENG_STEP => 'ALL',
2257 P_DATA_TABLE => gv_src_dim_props_rec.MEMBER_TL_TABLE_NAME,
2258 P_ENG_SQL => l_insert_tl_sql,
2259 P_TABLE_ALIAS => 'TL',
2260 P_RUN_NAME => 'PROCESS MEMBERS TL',
2261 P_ENG_PRG => 'FEM_DIM_MEMBER_MIGRATION_PKG.PROCESS_MEMBERS',
2262 P_CONDITION => NULL,
2263 P_FAILED_REQ_ID => NULL,
2264 P_SOURCE_DB_LINK => p_source_db_link
2265 );
2266
2267 IF l_mp_status NOT IN ('COMPLETE:NORMAL') THEN
2268 IF l_mp_exception IN ('FEM_MP_NO_DATA_SLICES_ERR') THEN
2269 null;
2270 ELSE
2271 RAISE e_insert_tl_exception;
2272 END IF;
2273 END IF;
2274
2275 END IF;
2276
2277 IF (gv_src_dim_props_rec.ATTRIBUTE_TABLE_NAME IS NOT NULL) THEN
2278
2279 fem_multi_proc_pkg.MASTER
2280 (X_PRG_STAT => l_mp_status,
2281 X_EXCEPTION_CODE => l_mp_exception,
2282 P_RULE_ID => gv_src_dim_props_rec.MIGRATION_OBJ_ID,
2283 P_ENG_STEP => 'ALL',
2284 P_DATA_TABLE => gv_src_dim_props_rec.ATTRIBUTE_TABLE_NAME,
2285 P_ENG_SQL => l_insert_attr_sql,
2286 P_TABLE_ALIAS => 'ATTR',
2287 P_RUN_NAME => 'PROCESS MEMBERS ATTR',
2288 P_ENG_PRG => 'FEM_DIM_MEMBER_MIGRATION_PKG.PROCESS_MEMBERS',
2289 P_CONDITION => NULL,
2290 P_FAILED_REQ_ID => NULL,
2291 P_SOURCE_DB_LINK => p_source_db_link
2292 );
2293
2294 IF l_mp_status NOT IN ('COMPLETE:NORMAL') THEN
2295 IF l_mp_exception IN ('FEM_MP_NO_DATA_SLICES_ERR') THEN
2296 null;
2297 ELSE
2298 RAISE e_insert_attr_exception;
2299 END IF;
2300 END IF;
2301
2302 update_calp_attributes(p_source_db_link);
2303
2304 END IF;
2305
2306
2307
2308
2309
2310 EXCEPTION
2311 WHEN e_insert_b_exception THEN
2312 FEM_ENGINES_PKG.TECH_MESSAGE
2313 (p_severity => c_log_level_5
2314 ,p_module => c_block||'.'||c_proc_name||'.Exception'
2315 ,p_app_name => c_fem
2316 ,p_msg_name => G_INSERT_ERROR
2317 ,P_TOKEN1 => 'TABLE_NAME'
2318 ,P_VALUE1 => gv_dim_props_rec.INTF_MEMBER_B_TABLE_NAME);
2319
2320 FEM_ENGINES_PKG.USER_MESSAGE
2321 (p_app_name => c_fem
2322 ,p_msg_name => G_INSERT_ERROR
2323 ,P_TOKEN1 => 'TABLE_NAME'
2324 ,P_VALUE1 => gv_dim_props_rec.INTF_MEMBER_B_TABLE_NAME);
2325
2326 RAISE e_main_terminate;
2327
2328 WHEN e_insert_tl_exception THEN
2329 FEM_ENGINES_PKG.TECH_MESSAGE
2330 (p_severity => c_log_level_5
2331 ,p_module => c_block||'.'||c_proc_name||'.Exception'
2332 ,p_app_name => c_fem
2333 ,p_msg_name => G_INSERT_ERROR
2334 ,P_TOKEN1 => 'TABLE_NAME'
2335 ,P_VALUE1 => gv_dim_props_rec.INTF_MEMBER_TL_TABLE_NAME);
2336
2337 FEM_ENGINES_PKG.USER_MESSAGE
2338 (p_app_name => c_fem
2339 ,p_msg_name => G_INSERT_ERROR
2340 ,P_TOKEN1 => 'TABLE_NAME'
2341 ,P_VALUE1 => gv_dim_props_rec.INTF_MEMBER_TL_TABLE_NAME);
2342 RAISE e_main_terminate;
2343
2344 WHEN e_insert_attr_exception THEN
2345 FEM_ENGINES_PKG.TECH_MESSAGE
2346 (p_severity => c_log_level_5
2347 ,p_module => c_block||'.'||c_proc_name||'.Exception'
2348 ,p_app_name => c_fem
2349 ,p_msg_name => G_INSERT_ERROR
2350 ,P_TOKEN1 => 'TABLE_NAME'
2351 ,P_VALUE1 => gv_dim_props_rec.INTF_ATTRIBUTE_TABLE_NAME);
2352
2353 FEM_ENGINES_PKG.USER_MESSAGE
2354 (p_app_name => c_fem
2355 ,p_msg_name => G_INSERT_ERROR
2356 ,P_TOKEN1 => 'TABLE_NAME'
2357 ,P_VALUE1 => gv_dim_props_rec.INTF_ATTRIBUTE_TABLE_NAME);
2358
2359 RAISE e_main_terminate;
2360
2361 WHEN e_terminate THEN
2362
2363 RAISE e_main_terminate;
2364
2365 END PROCESS_MEMBERS;
2366
2367
2368
2369 -----------------------------------------------
2370 -- Pre-Process Hierarchy --
2371 -----------------------------------------------
2372 PROCEDURE PRE_PROCESS_HIERARCHY(
2373 p_source_db_link IN VARCHAR2,
2374 p_dim_varchar_lbl IN VARCHAR2,
2375 p_hier_obj_name IN VARCHAR2,
2376 p_hier_obj_def_name IN VARCHAR2,
2377 p_source_user_dim_name IN VARCHAR2,
2378 x_folder_name OUT NOCOPY VARCHAR2,
2379 x_hier_obj_id OUT NOCOPY NUMBER)
2380
2381 IS
2382
2383 c_proc_name VARCHAR2(30):= 'pre_process_hierarchy';
2384
2385 l_verify_db_link VARCHAR2(10);
2386 l_error_code VARCHAR2(10);
2387 l_return_code VARCHAR2(10);
2388 l_pl_exec_status VARCHAR2(30);
2389 l_dim_obj_id NUMBER;
2390 l_delete_b_sql VARCHAR2(200);
2391 l_delete_tl_sql VARCHAR2(200);
2392 l_delete_attr_sql VARCHAR2(200);
2393 l_delete_dg_b_sql VARCHAR2(200);
2394 l_delete_dg_tl_sql VARCHAR2(200);
2395 l_hier_obj_id NUMBER;
2396 l_delete_hier_rule_sql VARCHAR2(200);
2397 l_delete_hier_sql VARCHAR2(200);
2398 l_delete_hier_vs_sql VARCHAR2(200);
2399 l_delete_hier_dg_sql VARCHAR2(200);
2400 l_folder_name VARCHAR2(150);
2401
2402
2403 l_hier_rule_sql VARCHAR2(1000):= 'SELECT A.OBJECT_ID, B.FOLDER_NAME'||
2404 ' FROM FEM_OBJECT_CATALOG_VL@'||p_source_db_link||' A,'||
2405 ' FEM_FOLDERS_VL@'||p_source_db_link||' B'||
2406 ' WHERE A.OBJECT_TYPE_CODE = ''HIERARCHY'''||
2407 ' AND A.FOLDER_ID = B.FOLDER_ID'||
2408 ' AND A.OBJECT_NAME = :hier_obj_name';
2409
2410
2411 BEGIN
2412
2413 l_verify_db_link := validate_db_link(p_source_db_link);
2414
2415 --TO DO: CHECK LANGUAGE SETTINGS -- TGT SESSION LANG MUST BE INSTALLED IN SRC')--
2416
2417 --validate hierarchy--
2418 --MUST ALSO CHECK TO MAKE SURE HIERARCHY DOES NOT EXIST IN TARGET WHEN NO VERSION IS SPECIFIED--
2419 BEGIN
2420
2421 --is dimension hierarchy supported? need to fail if not!!!--
2422 get_dimension_info(p_dim_varchar_lbl,
2423 p_source_user_dim_name,
2424 p_source_db_link);
2425
2426 IF (gv_dim_props_rec.HIERARCHY_TABLE_NAME IS NULL) THEN
2427 RAISE e_dim_hier_not_supported;
2428 END IF;
2429
2430 EXECUTE IMMEDIATE l_hier_rule_sql
2431 INTO l_hier_obj_id, l_folder_name
2432 USING p_hier_obj_name;
2433
2434 x_hier_obj_id := l_hier_obj_id;
2435 x_folder_name := l_folder_name;
2436
2437 EXCEPTION
2438
2439 WHEN NO_DATA_FOUND THEN
2440
2441 RAISE e_invalid_hierarchy;
2442
2443 END;
2444 -- end validate hierarchy--
2445
2446
2447 register_process_execution(p_object_id => 2000,
2448 p_obj_def_id => 2000,
2449 p_execution_mode => 'S');
2450
2451
2452 --delete existing data (move to procedure)
2453 l_delete_hier_rule_sql := 'DELETE FROM FEM_HIERARCHIES_T'||
2454 ' WHERE HIERARCHY_OBJECT_NAME = :hier_obj_name AND CREATED_BY_DIM_MIGRATION_FLAG = ''Y''';
2455
2456 l_delete_hier_sql := 'DELETE FROM '||gv_dim_props_rec.HIERARCHY_INTF_TABLE_NAME||
2457 ' WHERE HIERARCHY_OBJECT_NAME = :hier_obj_name AND CREATED_BY_DIM_MIGRATION_FLAG = ''Y''';
2458
2459 l_delete_hier_vs_sql := 'DELETE FROM FEM_HIER_VALUE_SETS_T'||
2460 ' WHERE HIERARCHY_OBJECT_NAME = :hier_obj_name AND CREATED_BY_DIM_MIGRATION_FLAG = ''Y''';
2461
2462 l_delete_hier_dg_sql := 'DELETE FROM FEM_HIER_DIM_GRPS_T'||
2463 ' WHERE HIERARCHY_OBJECT_NAME = :hier_obj_name AND CREATED_BY_DIM_MIGRATION_FLAG = ''Y''';
2464
2465 EXECUTE IMMEDIATE l_delete_hier_rule_sql USING p_hier_obj_name;
2466 EXECUTE IMMEDIATE l_delete_hier_sql USING p_hier_obj_name;
2467 EXECUTE IMMEDIATE l_delete_hier_vs_sql USING p_hier_obj_name;
2468 EXECUTE IMMEDIATE l_delete_hier_dg_sql USING p_hier_obj_name;
2469
2470 COMMIT;
2471 --End delete
2472
2473
2474 EXCEPTION
2475
2476 WHEN e_dimension_not_supported THEN
2477 FEM_ENGINES_PKG.TECH_MESSAGE
2478 (p_severity => c_log_level_5
2479 ,p_module => c_block||'.'||c_proc_name||'.Exception'
2480 ,p_app_name => c_fem
2481 ,p_msg_name => G_DIM_NOT_SUPPORTED
2482 ,P_TOKEN1 => 'DIM_NAME'
2483 ,P_VALUE1 => p_dim_varchar_lbl);
2484
2485 FEM_ENGINES_PKG.USER_MESSAGE
2486 (p_app_name => c_fem
2487 ,p_msg_name => G_DIM_NOT_SUPPORTED
2488 ,P_TOKEN1 => 'DIM_NAME'
2489 ,P_VALUE1 => p_dim_varchar_lbl);
2490
2491 RAISE e_main_terminate;
2492
2493 WHEN e_invalid_dimension THEN
2494 FEM_ENGINES_PKG.TECH_MESSAGE
2495 (p_severity => c_log_level_5
2496 ,p_module => c_block||'.'||c_proc_name||'.Exception'
2497 ,p_app_name => c_fem
2498 ,p_msg_name => G_INVALID_DIMENSION
2499 ,P_TOKEN1 => 'DIM_LBL'
2500 ,P_VALUE1 => p_dim_varchar_lbl);
2501
2502 FEM_ENGINES_PKG.USER_MESSAGE
2503 (p_app_name => c_fem
2504 ,p_msg_name => G_INVALID_DIMENSION
2505 ,P_TOKEN1 => 'DIM_LBL'
2506 ,P_VALUE1 => p_dim_varchar_lbl);
2507
2508 RAISE e_main_terminate;
2509
2510 WHEN e_db_link_not_registered THEN
2511 FEM_ENGINES_PKG.TECH_MESSAGE
2512 (p_severity => c_log_level_5
2513 ,p_module => c_block||'.'||c_proc_name||'.Exception'
2514 ,p_app_name => c_fem
2515 ,p_msg_name => G_DB_LINK_NOT_REGISTERED
2516 ,P_TOKEN1 => 'DB_LINK'
2517 ,P_VALUE1 => p_source_db_link);
2518
2519 FEM_ENGINES_PKG.USER_MESSAGE
2520 (p_app_name => c_fem
2521 ,p_msg_name => G_DB_LINK_NOT_REGISTERED
2522 ,P_TOKEN1 => 'DB_LINK'
2523 ,P_VALUE1 => p_source_db_link);
2524
2525 RAISE e_main_terminate;
2526
2527 WHEN e_db_link_not_functional THEN
2528 FEM_ENGINES_PKG.TECH_MESSAGE
2529 (p_severity => c_log_level_5
2530 ,p_module => c_block||'.'||c_proc_name||'.Exception'
2531 ,p_app_name => c_fem
2532 ,p_msg_name => G_DB_LINK_NOT_FUNCTIONAL
2533 ,P_TOKEN1 => 'DB_LINK'
2534 ,P_VALUE1 => p_source_db_link);
2535
2536 FEM_ENGINES_PKG.USER_MESSAGE
2537 (p_app_name => c_fem
2538 ,p_msg_name => G_DB_LINK_NOT_FUNCTIONAL
2539 ,P_TOKEN1 => 'DB_LINK'
2540 ,P_VALUE1 => p_source_db_link);
2541
2542 RAISE e_main_terminate;
2543
2544
2545 WHEN e_dim_not_user_extensible THEN
2546 FEM_ENGINES_PKG.TECH_MESSAGE
2547 (p_severity => c_log_level_5
2548 ,p_module => c_block||'.'||c_proc_name||'.Exception'
2549 ,p_app_name => c_fem
2550 ,p_msg_name => G_USER_DIM_MISMATCH
2551 ,P_TOKEN1 => 'USER_DIM_NAME'
2552 ,P_VALUE1 => p_source_user_dim_name);
2553
2554 FEM_ENGINES_PKG.USER_MESSAGE
2555 (p_app_name => c_fem
2556 ,p_msg_name => G_USER_DIM_MISMATCH
2557 ,P_TOKEN1 => 'USER_DIM_NAME'
2558 ,P_VALUE1 => p_source_user_dim_name);
2559
2560 RAISE e_main_terminate;
2561
2562
2563 WHEN e_src_dim_not_user_extensible THEN
2564 FEM_ENGINES_PKG.TECH_MESSAGE
2565 (p_severity => c_log_level_5
2566 ,p_module => c_block||'.'||c_proc_name||'.Exception'
2567 ,p_app_name => c_fem
2568 ,p_msg_name => G_INVALID_SRC_USER_DIM
2569 ,P_TOKEN1 => 'USER_DIM_NAME'
2570 ,P_VALUE1 => p_source_user_dim_name);
2571
2572 FEM_ENGINES_PKG.USER_MESSAGE
2573 (p_app_name => c_fem
2574 ,p_msg_name => G_INVALID_SRC_USER_DIM
2575 ,P_TOKEN1 => 'USER_DIM_NAME'
2576 ,P_VALUE1 => p_source_user_dim_name);
2577
2578 RAISE e_main_terminate;
2579
2580
2581 WHEN e_invalid_hierarchy THEN
2582 FEM_ENGINES_PKG.TECH_MESSAGE
2583 (p_severity => c_log_level_5
2584 ,p_module => c_block||'.'||c_proc_name||'.Exception'
2585 ,p_app_name => c_fem
2586 ,p_msg_name => G_INVALID_HIERARCHY
2587 ,P_TOKEN1 => 'HIER'
2588 ,P_VALUE1 => p_hier_obj_name);
2589
2590 FEM_ENGINES_PKG.USER_MESSAGE
2591 (p_app_name => c_fem
2592 ,p_msg_name => G_INVALID_HIERARCHY
2593 ,P_TOKEN1 => 'HIER'
2594 ,P_VALUE1 => p_hier_obj_name);
2595
2596 RAISE e_main_terminate;
2597
2598 WHEN e_dim_hier_not_supported THEN
2599
2600 FEM_ENGINES_PKG.TECH_MESSAGE
2601 (p_severity => c_log_level_5
2602 ,p_module => c_block||'.'||c_proc_name||'.Exception'
2603 ,p_app_name => c_fem
2604 ,p_msg_name => G_DIM_HIER_NOT_SUPPORTED
2605 ,P_TOKEN1 => 'DIM_NAME'
2606 ,P_VALUE1 => p_dim_varchar_lbl);
2607
2608
2609 FEM_ENGINES_PKG.USER_MESSAGE
2610 (p_app_name => c_fem
2611 ,p_msg_name => G_DIM_HIER_NOT_SUPPORTED
2612 ,P_TOKEN1 => 'DIM_NAME'
2613 ,P_VALUE1 => p_dim_varchar_lbl);
2614
2615 RAISE e_main_terminate;
2616
2617 WHEN OTHERS THEN
2618 FEM_ENGINES_PKG.TECH_MESSAGE
2619 (p_severity => c_log_level_5
2620 ,p_module => c_block||'.'||c_proc_name||'.Exception'
2621 ,p_app_name => c_fem
2622 ,p_msg_name => G_UNHANDLED_ERROR
2623 ,P_TOKEN1 => 'MIGR_PROG'
2624 ,P_VALUE1 => 'Dimension Hierarchy Migration'
2625 ,P_TOKEN2 => 'SQLERRM'
2626 ,P_VALUE2 => SQLERRM);
2627
2628 FEM_ENGINES_PKG.USER_MESSAGE
2629 (p_app_name => c_fem
2630 ,p_msg_name => G_UNHANDLED_ERROR
2631 ,P_TOKEN1 => 'MIGR_PROG'
2632 ,P_VALUE1 => 'Dimension Hierarchy Migration'
2633 ,P_TOKEN2 => 'SQLERRM'
2634 ,P_VALUE2 => SQLERRM);
2635
2636 RAISE e_main_terminate;
2637
2638 END;
2639 -----------------------------------------------
2640 -- Process Hierarchy --
2641 -----------------------------------------------
2642
2643 PROCEDURE process_hierarchy(p_dim_varchar_lbl IN VARCHAR2,
2644 p_folder_name IN VARCHAR2,
2645 p_hier_obj_id IN NUMBER,
2646 p_hier_obj_name IN VARCHAR2,
2647 p_hier_obj_def_name IN VARCHAR2,
2648 p_source_db_link IN VARCHAR2,
2649 p_source_user_dim_name IN VARCHAR2)
2650
2651 IS
2652
2653 c_proc_name VARCHAR2(30) := 'process_hierarchy';
2654
2655 TYPE HIER_VERSIONS_TYPE IS REF CURSOR;
2656
2657 hier_versions_cur HIER_VERSIONS_TYPE;
2658
2659 l_hier_obj_id NUMBER;
2660 l_hier_obj_def_id NUMBER;
2661 l_insert_hier_sql VARCHAR2(32767);
2662 l_insert_hier_rule_sql VARCHAR2(32767);
2663 l_insert_hier_vs_sql VARCHAR2(32767);
2664 l_insert_hier_dg_sql VARCHAR2(32767);
2665 l_mp_status VARCHAR2(160);
2666 l_mp_exception VARCHAR2(160);
2667 l_hier_obj_def_name VARCHAR2(150);
2668 l_eff_start_date DATE;
2669 l_eff_end_date DATE;
2670 l_target_obj_id NUMBER;
2671 l_return_status VARCHAR2(1);
2672 l_msg_count NUMBER;
2673 l_msg_data VARCHAR2(4000);
2674
2675 l_hier_rule_sql VARCHAR2(1000):= 'SELECT A.OBJECT_ID'||
2676 ' FROM FEM_OBJECT_CATALOG_VL@'||p_source_db_link||' A'||
2677 ' WHERE A.OBJECT_TYPE_CODE = ''HIERARCHY'''||
2678 ' AND A.OBJECT_NAME = :hier_obj_name';
2679
2680 l_hier_version_sql VARCHAR2(1000):= 'SELECT B.OBJECT_DEFINITION_ID,'||
2681 ' B.DISPLAY_NAME,'||
2682 ' B.EFFECTIVE_START_DATE,'||
2683 ' B.EFFECTIVE_END_DATE'||
2684 ' FROM FEM_OBJECT_DEFINITION_VL@'||p_source_db_link||' B'||
2685 ' WHERE B.OBJECT_ID = :hier_obj_id'||'{{version_where}}';
2686
2687
2688 l_hier_version_where VARCHAR2(100) := ' AND B.DISPLAY_NAME = :hier_obj_def_name';
2689
2690 BEGIN
2691
2692 --Check to see if hierarchy rule exists in target system.
2693 BEGIN
2694
2695 SELECT OBJECT_ID
2696 INTO l_target_obj_id
2697 FROM FEM_OBJECT_CATALOG_VL
2698 WHERE OBJECT_NAME = p_hier_obj_name;
2699
2700 EXCEPTION
2701
2702 WHEN NO_DATA_FOUND THEN
2703 NULL;
2704 END;
2705
2706 --FEM_HIERARCHIES_T--
2707 IF (p_hier_obj_def_name IS NOT NULL) THEN
2708
2709 BEGIN
2710
2711 l_hier_version_sql := REPLACE(l_hier_version_sql, '{{version_where}}', l_hier_version_where);
2712
2713 FEM_ENGINES_PKG.TECH_MESSAGE
2714 (p_severity => c_log_level_2,
2715 p_module => c_block||'.l_hier_version_sql',
2716 p_msg_text => l_hier_version_sql);
2717
2718
2719 EXECUTE IMMEDIATE l_hier_version_sql
2720 INTO l_hier_obj_def_id, l_hier_obj_def_name, l_eff_start_date, l_eff_end_date
2721 USING p_hier_obj_id, p_hier_obj_def_name;
2722
2723 EXCEPTION
2724
2725 WHEN NO_DATA_FOUND THEN
2726
2727 RAISE e_invalid_hierarchy_version;
2728
2729 END;
2730
2731 --CHECK FOR OVERLAPPING EFFECTIVE DATES IF RULE EXISTS IN TARGET--
2732 IF (l_target_obj_id IS NOT NULL) THEN
2733 /*FEM_BUSINESS_RULE_PVT.CheckOverlapObjDefs(l_target_obj_id,
2734 NULL,
2735 l_eff_start_date,
2736 l_eff_end_date,
2737 FND_API.G_FALSE,
2738 l_return_status,
2739 l_msg_count,
2740 l_msg_data);*/
2741 FEM_BUSINESS_RULE_PVT.CheckOverlapObjDefs(
2742 p_api_version => 1.0
2743 ,p_init_msg_list => FND_API.G_FALSE
2744 ,x_return_status => l_return_status
2745 ,x_msg_count => l_msg_count
2746 ,x_msg_data => l_msg_data
2747 ,p_obj_id => l_target_obj_id
2748 ,p_exclude_obj_def_id => null
2749 ,p_effective_start_date => l_eff_start_date
2750 ,p_effective_end_date => l_eff_end_date
2751 );
2752
2753 IF (l_return_status <> 'S') THEN
2754 l_eff_start_date := NULL;
2755 l_eff_end_date := NULL;
2756 END IF;
2757 END IF;
2758
2759
2760 l_insert_hier_rule_sql := get_dim_hier_rule_sql(p_dim_varchar_lbl,
2761 p_folder_name,
2762 p_hier_obj_name,
2763 p_hier_obj_def_name,
2764 p_source_db_link,
2765 l_eff_start_date,
2766 l_eff_end_date);
2767
2768
2769 l_insert_hier_sql := get_dim_hier_sql(p_hier_obj_name,
2770 p_hier_obj_def_name,
2771 l_hier_obj_def_id,
2772 p_dim_varchar_lbl,
2773 p_source_db_link);
2774
2775
2776
2777
2778 FEM_ENGINES_PKG.TECH_MESSAGE
2779 (p_severity => c_log_level_2,
2780 p_module => c_block||'.l_insert_hier_rule_sql',
2781 p_msg_text => l_insert_hier_rule_sql);
2782
2783 EXECUTE IMMEDIATE l_insert_hier_rule_sql USING p_hier_obj_id;
2784 COMMIT;
2785
2786 --IF DUPLICATE DATA EXISTS THROW ERROR B/C IT CAME FROM SOME OTHER DATA SOURCE--
2787
2788 fem_multi_proc_pkg.MASTER
2789 (X_PRG_STAT => l_mp_status,
2790 X_EXCEPTION_CODE => l_mp_exception,
2791 P_RULE_ID => 2000,
2792 P_ENG_STEP => 'ALL',
2793 P_DATA_TABLE => gv_src_dim_props_rec.HIERARCHY_TABLE_NAME,
2794 P_ENG_SQL => l_insert_hier_sql,
2795 P_TABLE_ALIAS => 'HIER',
2796 P_RUN_NAME => 'PROCESS MEMBERS HIER',
2797 P_ENG_PRG => 'FEM_DIM_MEMBER_MIGRATION_PKG.PROCESS_MEMBERS',
2798 P_CONDITION => NULL,
2799 P_FAILED_REQ_ID => NULL,
2800 P_SOURCE_DB_LINK => p_source_db_link);
2801
2802 IF l_mp_status NOT IN ('COMPLETE:NORMAL') THEN
2803 IF l_mp_exception IN ('FEM_MP_NO_DATA_SLICES_ERR') THEN
2804 null;
2805 ELSE
2806 RAISE e_terminate;
2807 END IF;
2808 END IF;
2809
2810
2811 ELSE --no version specified
2812 --IF NO VERSION SPECIFIED, THEN HIERARCHY MUST NOT EXIST IN TARGET--
2813 IF (l_target_obj_id IS NOT NULL) THEN
2814 RAISE e_target_hierarchy_exists;
2815 END IF;
2816
2817 l_hier_version_sql := REPLACE(l_hier_version_sql, '{{version_where}}', '');
2818
2819 FEM_ENGINES_PKG.TECH_MESSAGE
2820 (p_severity => c_log_level_2,
2821 p_module => c_block||'.l_hier_version_sql',
2822 p_msg_text => l_hier_version_sql);
2823
2824 --CURSOR NEEDED TO RETRIEVE ALL VERSIONS--
2825
2826 OPEN hier_versions_cur FOR l_hier_version_sql USING p_hier_obj_id;
2827
2828 LOOP
2829
2830 FETCH hier_versions_cur
2831 INTO l_hier_obj_def_id,
2832 l_hier_obj_def_name,
2833 l_eff_start_date,
2834 l_eff_end_date;
2835
2836
2837 EXIT WHEN hier_versions_cur%NOTFOUND;
2838
2839 l_insert_hier_rule_sql := get_dim_hier_rule_sql(p_dim_varchar_lbl,
2840 p_folder_name,
2841 p_hier_obj_name,
2842 l_hier_obj_def_name,
2843 p_source_db_link,
2844 l_eff_start_date,
2845 l_eff_end_date);
2846
2847
2848 l_insert_hier_sql := get_dim_hier_sql(p_hier_obj_name,
2849 l_hier_obj_def_name,
2850 l_hier_obj_def_id,
2851 p_dim_varchar_lbl,
2852 p_source_db_link);
2853
2854
2855
2856 FEM_ENGINES_PKG.TECH_MESSAGE
2857 (p_severity => c_log_level_2,
2858 p_module => c_block||'.l_insert_hier_rule_sql',
2859 p_msg_text => l_insert_hier_rule_sql);
2860
2861 EXECUTE IMMEDIATE l_insert_hier_rule_sql USING p_hier_obj_id;
2862 COMMIT;
2863
2864 --IF DUPLICATE DATA EXISTS THROW ERROR B/C IT CAME FROM SOME OTHER DATA SOURCE--
2865
2866 FEM_ENGINES_PKG.TECH_MESSAGE
2867 (p_severity => c_log_level_2,
2868 p_module => c_block||'.l_insert_hier_sql',
2869 p_msg_text => l_insert_hier_sql);
2870
2871
2872 fem_multi_proc_pkg.MASTER
2873 (X_PRG_STAT => l_mp_status,
2874 X_EXCEPTION_CODE => l_mp_exception,
2875 P_RULE_ID => 2000,
2876 P_ENG_STEP => 'ALL',
2877 P_DATA_TABLE => gv_src_dim_props_rec.HIERARCHY_TABLE_NAME,
2878 P_ENG_SQL => l_insert_hier_sql,
2879 P_TABLE_ALIAS => 'HIER',
2880 P_RUN_NAME => 'PROCESS MEMBERS HIER',
2881 P_ENG_PRG => 'FEM_DIM_MEMBER_MIGRATION_PKG.PROCESS_MEMBERS',
2882 P_CONDITION => NULL,
2883 P_FAILED_REQ_ID => NULL
2884 ,P_SOURCE_DB_LINK => p_source_db_link
2885 );
2886
2887 IF l_mp_status NOT IN ('COMPLETE:NORMAL') THEN
2888 IF l_mp_exception IN ('FEM_MP_NO_DATA_SLICES_ERR') THEN
2889 null;
2890 ELSE
2891 RAISE e_insert_hier_exception;
2892 END IF;
2893 END IF;
2894
2895 END LOOP;
2896 END IF;
2897
2898
2899 --process value sets and dimension groups--
2900 l_insert_hier_vs_sql := get_hier_vs_sql(p_hier_obj_name,
2901 p_source_db_link);
2902 FEM_ENGINES_PKG.TECH_MESSAGE
2903 (p_severity => c_log_level_2,
2904 p_module => c_block||'.l_insert_hier_vs_sql',
2905 p_msg_text => l_insert_hier_vs_sql);
2906
2907 l_insert_hier_dg_sql := get_hier_dg_sql(p_hier_obj_name, p_source_db_link);
2908
2909
2910 FEM_ENGINES_PKG.TECH_MESSAGE
2911 (p_severity => c_log_level_2,
2912 p_module => c_block||'.l_insert_hier_dg_sql',
2913 p_msg_text => l_insert_hier_dg_sql);
2914
2915 EXECUTE IMMEDIATE l_insert_hier_vs_sql USING p_hier_obj_id;
2916 COMMIT;
2917
2918 EXECUTE IMMEDIATE l_insert_hier_dg_sql USING p_hier_obj_id;
2919 COMMIT;
2920
2921 --end process value sets and dimension groups--
2922
2923 EXCEPTION
2924
2925 WHEN e_invalid_hierarchy THEN
2926
2927 FEM_ENGINES_PKG.TECH_MESSAGE
2928 (p_severity => c_log_level_5
2929 ,p_module => c_block||'.'||c_proc_name||'.Exception'
2930 ,p_app_name => c_fem
2931 ,p_msg_name => G_INVALID_HIERARCHY
2932 ,P_TOKEN1 => 'HIER'
2933 ,P_VALUE1 => p_hier_obj_name);
2934
2935 FEM_ENGINES_PKG.USER_MESSAGE
2936 (p_app_name => c_fem
2937 ,p_msg_name => G_INVALID_HIERARCHY
2938 ,P_TOKEN1 => 'HIER'
2939 ,P_VALUE1 => p_hier_obj_name);
2940
2941 RAISE e_main_terminate;
2942
2943 WHEN e_insert_hier_exception THEN
2944
2945 FEM_ENGINES_PKG.TECH_MESSAGE
2946 (p_severity => c_log_level_5
2947 ,p_module => c_block||'.'||c_proc_name||'.Exception'
2948 ,p_app_name => c_fem
2949 ,p_msg_name => G_INSERT_ERROR
2950 ,P_TOKEN1 => 'TABLE_NAME'
2951 ,P_VALUE1 => gv_dim_props_rec.HIERARCHY_INTF_TABLE_NAME);
2952
2953 FEM_ENGINES_PKG.USER_MESSAGE
2954 (p_app_name => c_fem
2955 ,p_msg_name => G_INSERT_ERROR
2956 ,P_TOKEN1 => 'TABLE_NAME'
2957 ,P_VALUE1 => gv_dim_props_rec.HIERARCHY_INTF_TABLE_NAME);
2958
2959 RAISE e_main_terminate;
2960
2961 WHEN e_invalid_hierarchy_version THEN
2962
2963 FEM_ENGINES_PKG.TECH_MESSAGE
2964 (p_severity => c_log_level_5
2965 ,p_module => c_block||'.'||c_proc_name||'.Exception'
2966 ,p_app_name => c_fem
2967 ,p_msg_name => G_INVALID_HIER_VERSION
2968 ,P_TOKEN1 => 'HIER_VERSION'
2969 ,P_VALUE1 => p_hier_obj_def_name);
2970
2971 FEM_ENGINES_PKG.USER_MESSAGE
2972 (p_app_name => c_fem
2973 ,p_msg_name => G_INVALID_HIER_VERSION
2974 ,P_TOKEN1 => 'HIER_VERSION'
2975 ,P_VALUE1 => p_hier_obj_def_name);
2976
2977 RAISE e_main_terminate;
2978
2979 WHEN e_terminate THEN
2980 FEM_ENGINES_PKG.TECH_MESSAGE
2981 (p_severity => c_log_level_5
2982 ,p_module => c_block||'.'||c_proc_name||'.Exception'
2983 ,p_app_name => c_fem
2984 ,p_msg_name => G_UNHANDLED_ERROR
2985 ,P_TOKEN1 => 'MIGR_PROG'
2986 ,P_VALUE1 => 'Dimension Hierarchy Migration'
2987 ,P_TOKEN2 => 'SQLERRM'
2988 ,P_VALUE2 => SQLERRM);
2989
2990 FEM_ENGINES_PKG.USER_MESSAGE
2991 (p_app_name => c_fem
2992 ,p_msg_name => G_UNHANDLED_ERROR
2993 ,P_TOKEN1 => 'MIGR_PROG'
2994 ,P_VALUE1 => 'Dimension Hierarchy Migration'
2995 ,P_TOKEN2 => 'SQLERRM'
2996 ,P_VALUE2 => SQLERRM);
2997
2998 RAISE e_main_terminate;
2999
3000 WHEN e_target_hierarchy_exists THEN
3001 FEM_ENGINES_PKG.TECH_MESSAGE
3002 (p_severity => c_log_level_5
3003 ,p_module => c_block||'.'||c_proc_name||'.Exception'
3004 ,p_app_name => c_fem
3005 ,p_msg_name => G_HIERARCHY_RULE_EXISTS
3006 ,P_TOKEN1 => 'HIER_NAME'
3007 ,P_VALUE1 => p_hier_obj_name);
3008
3009
3010 FEM_ENGINES_PKG.USER_MESSAGE
3011 (p_app_name => c_fem
3012 ,p_msg_name => G_HIERARCHY_RULE_EXISTS
3013 ,P_TOKEN1 => 'HIER_NAME'
3014 ,P_VALUE1 => p_hier_obj_name);
3015
3016 RAISE e_main_terminate;
3017
3018 END;
3019
3020 -----------------------------------------------
3021 -- Post-Process Members --
3022 -----------------------------------------------
3023
3024 PROCEDURE POST_PROCESS_HIERARCHY(p_execution_status IN VARCHAR2)
3025
3026 IS
3027
3028 v_msg_count NUMBER;
3029 v_msg_data VARCHAR2(4000);
3030 v_API_return_status VARCHAR2(30);
3031
3032 BEGIN
3033
3034 ------------------------------------
3035 -- Update Object Execution Errors --
3036 ------------------------------------
3037 /*FEM_PL_PKG.Update_Obj_Exec_Errors(
3038 P_API_VERSION => c_api_version,
3039 P_COMMIT => c_true,
3040 P_REQUEST_ID => gv_request_id,
3041 P_OBJECT_ID => gv_dim_props_rec.MIGRATION_OBJ_ID,
3042 P_USER_ID => gv_apps_user_id,
3043 P_LAST_UPDATE_LOGIN => null,
3044 X_MSG_COUNT => v_msg_count,
3045 X_MSG_DATA => v_msg_data,
3046 X_RETURN_STATUS => v_API_return_status);
3047
3048 IF v_API_return_status NOT IN ('S') THEN
3049 RAISE e_post_process;
3050 END IF;*/
3051
3052 ------------------------------------
3053 -- Update Object Execution Status --
3054 ------------------------------------
3055 FEM_PL_PKG.Update_Obj_Exec_Status(
3056 P_API_VERSION => c_api_version,
3057 P_COMMIT => c_true,
3058 P_REQUEST_ID => gv_request_id,
3059 P_OBJECT_ID => gv_dim_props_rec.MIGRATION_OBJ_ID,
3060 P_EXEC_STATUS_CODE => p_execution_status,
3061 P_USER_ID => gv_apps_user_id,
3062 P_LAST_UPDATE_LOGIN => null,
3063 X_MSG_COUNT => v_msg_count,
3064 X_MSG_DATA => v_msg_data,
3065 X_RETURN_STATUS => v_API_return_status);
3066
3067 IF v_API_return_status NOT IN ('S') THEN
3068 RAISE e_post_process;
3069 END IF;
3070
3071 ---------------------------
3072 -- Update Request Status --
3073 ---------------------------
3074 FEM_PL_PKG.Update_Request_Status(
3075 P_API_VERSION => c_api_version,
3076 P_COMMIT => c_true,
3077 P_REQUEST_ID => gv_request_id,
3078 P_EXEC_STATUS_CODE => p_execution_status,
3079 P_USER_ID => gv_apps_user_id,
3080 P_LAST_UPDATE_LOGIN => null,
3081 X_MSG_COUNT => v_msg_count,
3082 X_MSG_DATA => v_msg_data,
3083 X_RETURN_STATUS => v_API_return_status);
3084
3085 IF v_API_return_status NOT IN ('S') THEN
3086 RAISE e_post_process;
3087 END IF;
3088
3089 IF (p_execution_status = 'SUCCESS') THEN
3090 gv_concurrent_status := fnd_concurrent.set_completion_status('NORMAL',null);
3091 ELSE
3092 gv_concurrent_status := fnd_concurrent.set_completion_status('ERROR',null);
3093 END IF;
3094
3095 EXCEPTION
3096 WHEN e_post_process THEN
3097 -- get messages from the stack
3098 Get_Put_Messages (
3099 p_msg_count => v_msg_count,
3100 p_msg_data => v_msg_data);
3101
3102 FEM_ENGINES_PKG.TECH_MESSAGE
3103 (p_severity => c_log_level_1,
3104 p_module => c_block||'.'||'Eng_Master_Post_Proc',
3105 p_msg_text => 'Post Process failed');
3106
3107 FEM_ENGINES_PKG.USER_MESSAGE
3108 (P_APP_NAME => c_fem
3109 ,P_MSG_NAME => G_PL_MIGRATION_ERROR);
3110
3111 END POST_PROCESS_HIERARCHY;
3112
3113
3114 -----------------------------------------------
3115 -- Migrate Members --
3116 -----------------------------------------------
3117 PROCEDURE MIGRATE_MEMBERS(x_retcode OUT NOCOPY VARCHAR2,
3118 x_errug OUT NOCOPY VARCHAR2,
3119 p_source_db_link IN VARCHAR2,
3120 p_dim_varchar_lbl IN VARCHAR2,
3121 -- p_version_mode IN VARCHAR2,
3122 -- p_version_disp_cd IN VARCHAR2,
3123 -- p_version_name IN VARCHAR2,
3124 -- p_version_desc IN VARCHAR2,
3125 p_source_user_dim_name IN VARCHAR2)
3126 IS
3127
3128 BEGIN
3129
3130 MIGRATE_MEMBERS(x_retcode => x_retcode,
3131 x_errug => x_errug,
3132 p_source_db_link => p_source_db_link,
3133 p_dim_varchar_lbl => p_dim_varchar_lbl,
3134 p_autoload_dims => 'NO',
3135 p_migrate_dependent_dims => 'NO',
3136 p_version_mode => 'DEFAULT',
3137 p_version_disp_cd => NULL,
3138 p_version_name => NULL,
3139 p_version_desc => NULL,
3140 p_hier_obj_name=> NULL,
3141 p_hier_obj_def_name => NULL,
3142 p_source_user_dim_name => p_source_user_dim_name);
3143
3144 END MIGRATE_MEMBERS;
3145
3146
3147 PROCEDURE MIGRATE_HIERARCHY(x_retcode OUT NOCOPY VARCHAR2,
3148 x_errug OUT NOCOPY VARCHAR2,
3149 p_source_db_link IN VARCHAR2,
3150 p_dim_varchar_lbl IN VARCHAR2,
3151 p_hier_obj_name IN VARCHAR2,
3152 p_hier_obj_def_name IN VARCHAR2,
3153 p_source_user_dim_name IN VARCHAR2)
3154 IS
3155
3156
3157
3158 l_hier_obj_id NUMBER;
3159 l_folder_name VARCHAR2(150);
3160
3161
3162 BEGIN
3163
3164 FEM_ENGINES_PKG.User_Message(
3165 p_msg_text => 'Migrating Hierarchy...');
3166
3167
3168 PRE_PROCESS_HIERARCHY(p_source_db_link,
3169 p_dim_varchar_lbl,
3170 p_hier_obj_name,
3171 p_hier_obj_def_name,
3172 p_source_user_dim_name,
3173 l_folder_name,
3174 l_hier_obj_id);
3175
3176
3177 process_hierarchy(p_dim_varchar_lbl,
3178 l_folder_name,
3179 l_hier_obj_id,
3180 p_hier_obj_name,
3181 p_hier_obj_def_name,
3182 p_source_db_link,
3183 p_source_user_dim_name);
3184
3185 POST_PROCESS_HIERARCHY('SUCCESS');
3186
3187 EXCEPTION
3188
3189 WHEN e_main_terminate THEN
3190
3191 gv_concurrent_status := fnd_concurrent.set_completion_status('ERROR',null);
3192 POST_PROCESS_HIERARCHY('ERROR_RERUN');
3193
3194 END MIGRATE_HIERARCHY;
3195
3196
3197 PROCEDURE MIGRATE_MEMBERS(x_retcode OUT NOCOPY VARCHAR2,
3198 x_errug OUT NOCOPY VARCHAR2,
3199 p_source_db_link IN VARCHAR2,
3200 p_dim_varchar_lbl IN VARCHAR2,
3201 p_autoload_dims IN VARCHAR2,
3202 p_migrate_dependent_dims IN VARCHAR2,
3203 p_version_mode IN VARCHAR2,
3204 p_version_disp_cd IN VARCHAR2,
3205 p_version_name IN VARCHAR2,
3206 p_version_desc IN VARCHAR2,
3207 p_hier_obj_name IN VARCHAR2,
3208 p_hier_obj_def_name IN VARCHAR2,
3209 p_source_user_dim_name IN VARCHAR2)
3210 IS
3211
3212
3213
3214 l_dim_obj_def_id NUMBER;
3215 l_return_status NUMBER;
3216
3217 BEGIN
3218
3219 FEM_ENGINES_PKG.User_Message(
3220 p_msg_text => 'Migrating Members...');
3221
3222
3223 PRE_PROCESS_MEMBERS(
3224 p_source_db_link => p_source_db_link,
3225 p_dim_varchar_lbl => p_dim_varchar_lbl,
3226 p_autoload_dims => 'NO',
3227 p_migrate_dependent_dims => 'NO',
3228 p_version_mode => p_version_mode,
3229 p_version_disp_cd => p_version_disp_cd,
3230 p_version_name => p_version_name,
3231 p_source_user_dim_name => p_source_user_dim_name,
3232 p_hier_obj_name => p_hier_obj_name,
3233 p_hier_obj_def_name => p_hier_obj_def_name);
3234
3235
3236 PROCESS_MEMBERS(p_dim_varchar_lbl, p_version_mode, p_source_db_link);
3237
3238
3239 POST_PROCESS_MEMBERS('SUCCESS');
3240
3241 EXCEPTION
3242
3243 WHEN e_main_terminate THEN
3244
3245 gv_concurrent_status := fnd_concurrent.set_completion_status('ERROR',null);
3246 POST_PROCESS_MEMBERS('ERROR_RERUN');
3247
3248 -- UPDATE PL EXECUTION
3249
3250 END MIGRATE_MEMBERS;
3251
3252
3253 -----------------------------------------------
3254 -- Post-Process Members --
3255 -----------------------------------------------
3256
3257 PROCEDURE POST_PROCESS_MEMBERS(p_execution_status IN VARCHAR2)
3258
3259 IS
3260
3261 v_msg_count NUMBER;
3262 v_msg_data VARCHAR2(4000);
3263 v_API_return_status VARCHAR2(30);
3264
3265 BEGIN
3266
3267 ------------------------------------
3268 -- Update Object Execution Errors --
3269 ------------------------------------
3270 /*FEM_PL_PKG.Update_Obj_Exec_Errors(
3271 P_API_VERSION => c_api_version,
3272 P_COMMIT => c_true,
3273 P_REQUEST_ID => gv_request_id,
3274 P_OBJECT_ID => gv_dim_props_rec.MIGRATION_OBJ_ID,
3275 P_USER_ID => gv_apps_user_id,
3276 P_LAST_UPDATE_LOGIN => null,
3277 X_MSG_COUNT => v_msg_count,
3278 X_MSG_DATA => v_msg_data,
3279 X_RETURN_STATUS => v_API_return_status);
3280
3281 IF v_API_return_status NOT IN ('S') THEN
3282 RAISE e_post_process;
3283 END IF;*/
3284
3285 ------------------------------------
3286 -- Update Object Execution Status --
3287 ------------------------------------
3288 FEM_PL_PKG.Update_Obj_Exec_Status(
3289 P_API_VERSION => c_api_version,
3290 P_COMMIT => c_true,
3291 P_REQUEST_ID => gv_request_id,
3292 P_OBJECT_ID => gv_dim_props_rec.MIGRATION_OBJ_ID,
3293 P_EXEC_STATUS_CODE => p_execution_status,
3294 P_USER_ID => gv_apps_user_id,
3295 P_LAST_UPDATE_LOGIN => null,
3296 X_MSG_COUNT => v_msg_count,
3297 X_MSG_DATA => v_msg_data,
3298 X_RETURN_STATUS => v_API_return_status);
3299
3300 IF v_API_return_status NOT IN ('S') THEN
3301 RAISE e_post_process;
3302 END IF;
3303
3304 ---------------------------
3305 -- Update Request Status --
3306 ---------------------------
3307 FEM_PL_PKG.Update_Request_Status(
3308 P_API_VERSION => c_api_version,
3309 P_COMMIT => c_true,
3310 P_REQUEST_ID => gv_request_id,
3311 P_EXEC_STATUS_CODE => p_execution_status,
3312 P_USER_ID => gv_apps_user_id,
3313 P_LAST_UPDATE_LOGIN => null,
3314 X_MSG_COUNT => v_msg_count,
3315 X_MSG_DATA => v_msg_data,
3316 X_RETURN_STATUS => v_API_return_status);
3317
3318 IF v_API_return_status NOT IN ('S') THEN
3319 RAISE e_post_process;
3320 END IF;
3321
3322 IF (p_execution_status = 'SUCCESS') THEN
3323 gv_concurrent_status := fnd_concurrent.set_completion_status('NORMAL',null);
3324 ELSE
3325 gv_concurrent_status := fnd_concurrent.set_completion_status('ERROR',null);
3326 END IF;
3327
3328 EXCEPTION
3329 WHEN e_post_process THEN
3330 -- get messages from the stack
3331 Get_Put_Messages (
3332 p_msg_count => v_msg_count,
3333 p_msg_data => v_msg_data);
3334
3335 FEM_ENGINES_PKG.TECH_MESSAGE
3336 (p_severity => c_log_level_1,
3337 p_module => c_block||'.'||'Eng_Master_Post_Proc',
3338 p_msg_text => 'Post Process failed');
3339
3340 FEM_ENGINES_PKG.USER_MESSAGE
3341 (P_APP_NAME => c_fem
3342 ,P_MSG_NAME => G_PL_MIGRATION_ERROR);
3343
3344 END POST_PROCESS_MEMBERS;
3345
3346
3347 END FEM_DIMENSION_MIGRATION_PKG;