[Home] [Help]
PACKAGE BODY: APPS.MSD_DEM_DEMANTRA_UTILITIES
Source
1 PACKAGE BODY MSD_DEM_DEMANTRA_UTILITIES AS
2 /* $Header: msddemdub.pls 120.5.12020000.3 2012/11/22 10:57:28 nallkuma ship $ */
3
4
5
6
7
8 /*** GLOBAL VARIABLE - BEGIN ***/
9
10 g_demantra_schema_name VARCHAR2(30) := NULL;
11 g_is_demantra_schema BOOLEAN := FALSE;
12
13 /*** GLOBAL VARIABLE - END ***/
14
15
16
17
18
19 /*** PRIVATE FUNCTIONS - BEGIN ***
20 * SET_DEMANTRA_SCHEMA
21 */
22
23
24
25 /*
26 * This function sets the global variable g_demantra_schema_name
27 * to the Demantra schema name
28 * The function returns -
29 * 0 : in case of success
30 * -1 : in case of failure
31 */
32 FUNCTION SET_DEMANTRA_SCHEMA
33 RETURN NUMBER
34
35 IS
36
37 x_count NUMBER := 0;
38 x_session_user VARCHAR2(100) := 0;
39 x_sql VARCHAR2(4000) := 0;
40
41 BEGIN
42
43 EXECUTE IMMEDIATE 'SELECT SYS_CONTEXT (''USERENV'', ''SESSION_USER'') FROM DUAL ' INTO x_session_user;
44
45 EXECUTE IMMEDIATE 'SELECT count(1) FROM ALL_OBJECTS WHERE OWNER = :1 AND OBJECT_TYPE = ''TABLE'' AND OBJECT_NAME = ''MDP_MATRIX'' '
46 INTO x_count
47 USING x_session_user;
48 IF (x_count = 1)
49 THEN
50 g_is_demantra_schema := TRUE;
51 g_demantra_schema_name := x_session_user;
52 RETURN 0;
53 END IF;
54
55 x_count := 0;
56 EXECUTE IMMEDIATE 'SELECT FND_PROFILE.VALUE (''MSD_DEM_SCHEMA'') FROM DUAL ' INTO g_demantra_schema_name;
57
58 IF (g_demantra_schema_name IS NULL)
59 THEN
60 RETURN -1;
61 END IF;
62
63 EXECUTE IMMEDIATE 'SELECT count(1) FROM ' || g_demantra_schema_name || '.MDP_MATRIX WHERE rownum <2'
64 INTO x_count;
65
66 log_debug ('msd_dem_demantra_utilities.set_demantra_schema : the demantra schema is ' || g_demantra_schema_name);
67
68 log_debug ('Exiting msd_dem_demantra_utilities.set_demantra_schema ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
69 RETURN 0;
70
71 EXCEPTION
72 WHEN OTHERS THEN
73 g_demantra_schema_name := NULL;
74 RETURN -1;
75 END SET_DEMANTRA_SCHEMA;
76
77
78
79 /*** PRIVATE FUNCTIONS - END ***/
80
81
82
83
84 /*** PUBLIC PROCEDURES - BEGIN ***
85 * LOG_MESSAGE
86 * LOG_DEBUG
87 */
88
89
90 /* DUMMY PROCEDURE
91 * This procedure logs a given message text in ???
92 * param: p_buff - message text to be logged.
93 */
94 PROCEDURE LOG_MESSAGE ( p_buff IN VARCHAR2)
95 IS
96 BEGIN
97 fnd_file.put_line (fnd_file.log, p_buff);
98 END LOG_MESSAGE;
99
100
101 /* DUMMY PROCEDURE
102 * This procedure logs a given debug message text in ???
103 * only if the profile MSD_DEM_DEBUG is set to 'Yes'.
104 * param: p_buff - debug message text to be logged.
105 */
106 PROCEDURE LOG_DEBUG ( p_buff IN VARCHAR2)
107 IS
108 BEGIN
109 IF (C_MSD_DEM_DEBUG = 'Y') THEN
110 fnd_file.put_line (fnd_file.output, p_buff);
111 END IF;
112 END LOG_DEBUG;
113
114
115
116 /*** PUBLIC PROCEDURES - END ***/
117
118
119
120
121 /*** PUBLIC FUNCTIONS - BEGIN ***
122 * GET_SEQUENCE_NEXTVAL
123 * CREATE_SERIES
124 * DELETE_SERIES
125 * ADD_SERIES_TO_COMPONENT
126 * CREATE_INTEGRATION_INTERFACE
127 * DELETE_INTEGRATION_INTERFACE
128 * CREATE_DATA_PROFILE
129 * ADD_SERIES_TO_PROFILE
130 * ADD_LEVEL_TO_PROFILE
131 * CREATE_WORKFLOW_SCHEMA
132 * DELETE_WORKFLOW_SCHEMA
133 * GET_DEMANTRA_SCHEMA
134 * CREATE_DEMANTRA_DB_OBJECT
135 * DROP_DEMANTRA_DB_OBJECT
136 * CREATE_SYNONYM_IN_EBS
137 */
138
139
140 /*
141 * This function calls the GET_SEQ_NEXTVAL procedure in the Demantra schema.
142 * The function returns -
143 * n : next value for the given sequence
144 * -1 : If table is not present
145 * -2 : If column is not present
146 * -3 : Unable to set demantra schema name
147 * -4 : Any other error
148 */
149 FUNCTION GET_SEQUENCE_NEXTVAL (
150 p_table_name IN VARCHAR2,
151 p_column_name IN VARCHAR2,
152 p_seq_name IN VARCHAR2)
153 RETURN NUMBER
154 IS
155
156 x_sequence_nextval NUMBER := NULL;
157 x_sql VARCHAR2(2000) := NULL;
158
159 BEGIN
160
161 IF (g_demantra_schema_name IS NULL)
162 THEN
163 IF (set_demantra_schema = -1)
164 THEN
165 RETURN -3;
166 END IF;
167 END IF;
168
169 log_debug ('Entering msd_dem_demantra_utilities.get_sequence_nextval ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
170
171 IF (g_is_demantra_schema = FALSE)
172 THEN
173 log_debug ('Alter the schema for the session to ' || g_demantra_schema_name );
174 x_sql := 'ALTER SESSION SET CURRENT_SCHEMA = ' || g_demantra_schema_name;
175 log_debug (x_sql);
176 EXECUTE IMMEDIATE x_sql;
177 END IF;
178
179 log_debug ('Calling Demantra procedure GET_SEQ_NEXTVAL');
180 x_sql := 'BEGIN ' || g_demantra_schema_name || '.get_seq_nextval ( :1, :2, :3, :4); END;';
181 log_debug (x_sql);
182
183 EXECUTE IMMEDIATE x_sql USING p_table_name, p_column_name, p_seq_name, OUT x_sequence_nextval;
184
185 IF (x_sequence_nextval = -1)
186 THEN
187 log_message ('Error(1) in function msd_dem_demantra_utilities.get_sequence_nextval ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
188 log_message ('Table ' || p_table_name || 'does not exist.');
189 RETURN -1;
190 ELSIF (x_sequence_nextval = -1)
191 THEN
192 log_message ('Error(2) in function msd_dem_demantra_utilities.get_sequence_nextval ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
193 log_message ('Column ' || p_column_name || 'does not exist in the table ' || p_table_name || '.');
194 RETURN -2;
195 END IF;
196
197 log_debug ('Table : ' || p_table_name || ', Column Name : ' || p_column_name || ', Sequence Name : ' || p_seq_name);
198 log_debug ('The sequence next val is : ' || to_char(x_sequence_nextval));
199
200 IF (g_is_demantra_schema = FALSE)
201 THEN
202 log_debug ('Alter the schema for the session to APPS' );
203 x_sql := 'ALTER SESSION SET CURRENT_SCHEMA = APPS';
204 log_debug (x_sql);
205 EXECUTE IMMEDIATE x_sql;
206 END IF;
207
208 log_debug ('Exiting msd_dem_demantra_utilities.get_sequence_nextval ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
209 RETURN x_sequence_nextval;
210
211 EXCEPTION
212 WHEN OTHERS THEN
213
214 IF (g_is_demantra_schema = FALSE)
215 THEN
216 log_debug ('Alter the schema for the session to APPS' );
217 x_sql := 'ALTER SESSION SET CURRENT_SCHEMA = APPS';
218 log_debug (x_sql);
219 EXECUTE IMMEDIATE x_sql;
220 END IF;
221
222 log_message ('Exception in function msd_dem_demantra_utilities.get_sequence_nextval ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
223 log_message (substr(SQLERRM,1,150));
224 RETURN -4;
225
226 END GET_SEQUENCE_NEXTVAL;
227
228
229
230
231 /*
232 * This function creates the series given in Demantra schema.
233 * The function returns -
234 * n : The series id in case of success
235 * -1 : in case of error
236 * -3 : Unable to set demantra schema name
237 * -4 : Some of the mandatory parameters are NULL.
238 * -5 : Unable to get next sequence value for forecast type id
239 * -6 : Column already present in the table
240 */
241 FUNCTION CREATE_SERIES (
242 p_computed_name IN VARCHAR2,
243 p_exp_template IN VARCHAR2,
244 p_computed_title IN VARCHAR2,
245 p_sum_func IN VARCHAR2,
246 p_scaleble IN NUMBER,
247 p_editable IN NUMBER,
248 p_is_proportion IN NUMBER,
249 p_dbname IN VARCHAR2,
250 p_hint_message IN VARCHAR2,
251 p_hist_pred_type IN NUMBER,
252 p_data_table_name IN VARCHAR2,
253 p_prop_calc_series IN NUMBER,
254 p_base_level IN NUMBER,
255 p_expression_type IN NUMBER,
256 p_int_aggr_func IN VARCHAR2,
257 p_aggr_by IN NUMBER,
258 p_preservation_type IN NUMBER,
259 p_move_preservation_type IN NUMBER,
260 p_data_type IN NUMBER)
261 RETURN NUMBER
262
263 IS
264
265 x_forecast_type_id NUMBER := NULL;
266 x_disp_order NUMBER := NULL;
267 x_return_value NUMBER := NULL;
268 x_sql VARCHAR2(4000) := NULL;
269
270 BEGIN
271
272 IF (g_demantra_schema_name IS NULL)
273 THEN
274 IF (set_demantra_schema = -1)
275 THEN
276 RETURN -3;
277 END IF;
278 END IF;
279
280
281 log_debug ('Entering msd_dem_demantra_utilities.create_series ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
282
283 log_debug ('Verify that mandatory parameters should be not null');
284 IF ( p_computed_name IS NULL
285 OR p_exp_template IS NULL
286 OR p_computed_title IS NULL
287 OR p_sum_func IS NULL
288 OR p_scaleble IS NULL
289 OR p_editable IS NULL
290 OR p_is_proportion IS NULL
291 OR p_hist_pred_type IS NULL
292 OR p_data_table_name IS NULL
293 OR p_base_level IS NULL
294 OR p_expression_type IS NULL
295 OR p_int_aggr_func IS NULL
296 OR p_data_type IS NULL)
297 THEN
298 log_message ('Error(1) in function msd_dem_demantra_utilities.create_series ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
299 log_message ('Some of the mandatory parameters are null');
300 RETURN -4;
301 END IF;
302
303
304 log_debug ('Get the next sequence value for forecast type id');
305 x_forecast_type_id := get_sequence_nextval (
306 'COMPUTED_FIELDS',
307 'FORECAST_TYPE_ID',
308 'COMPUTED_FIELDS_SEQ');
309 IF (x_forecast_type_id < 0)
310 THEN
311 log_message ('Error(2) in function msd_dem_demantra_utilities.create_series ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
312 log_message ('Unable to get next sequence value for forecast type id');
313 RETURN -5;
314 END IF;
315 log_debug ('Next sequence value for forecast_type_id : ' || to_char(x_forecast_type_id));
316
317
318 log_debug ('Get the next value for disp order');
319 x_sql := 'SELECT max(disp_order) + 1 FROM ' || g_demantra_schema_name || '.COMPUTED_FIELDS ';
320 log_debug (x_sql);
321 EXECUTE IMMEDIATE x_sql INTO x_disp_order;
322 log_debug ('Next value for disp_order : ' || to_char(x_disp_order));
323
324
325 log_debug ('If parameter p_dbname is not null, then create the column in SALES_DATA table');
326 IF (p_dbname IS NOT NULL)
327 THEN
328
329 log_debug ('First check if the column ' || p_dbname || ' already exists or not.');
330 x_sql := 'SELECT count(1) FROM ALL_TAB_COLUMNS WHERE '
331 || ' owner = upper( :1 ) '
332 || ' AND table_name = ''SALES_DATA'' '
333 || ' AND column_name = upper (:2 ) ';
334 log_debug (x_sql);
335 EXECUTE IMMEDIATE x_sql
336 INTO x_return_value
337 USING g_demantra_schema_name, p_dbname;
338
339 IF (x_return_value = 1)
340 THEN
341 log_message ('Error(3) in function msd_dem_demantra_utilities.create_series ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
342 log_message ('Column ' || p_dbname || ' already exists in the SALES_DATA table');
343 RETURN -6;
344 END IF;
345
346 x_sql := 'ALTER TABLE ' || g_demantra_schema_name || '.SALES_DATA'
347 || ' ADD ( ' || p_dbname || ' NUMBER(20,10)) ';
348 log_debug (x_sql);
349 EXECUTE IMMEDIATE x_sql;
350 log_debug ('Column ' || p_dbname || 'created successfully');
351
352 END IF;
353
354 log_debug ('Build insert statement for creating record for the new series in the computed_fields table');
355 x_sql := NULL;
356
357 x_sql := 'INSERT INTO ' || g_demantra_schema_name || '.COMPUTED_FIELDS ( '
358 || 'FORECAST_TYPE_ID, COMPUTED_NAME, EXP_TEMPLATE, DISP_COLOR, DISP_LSTYLE, '
359 || 'DISP_LSYMBOL, PRINT_COLOR, PRINT_LSTYLE, PRINT_LSYMBOL, DISP_ORDER, '
360 || 'INFO_TYPE, TABLE_FORMAT, DO_HAVING, COMPUTED_TITLE, FIELD_TYPE, '
361 || 'SUM_FUNC, SCALEBLE, MODULE_TYPE, EDITABLE, IS_PROPORTION, '
362 || 'NULL_AS_ZERO, DBNAME, IS_DDLB, IS_CHECK, SERIES_WIDTH, '
363 || 'IS_DEFAULT, HINT_MESSAGE, HIST_PRED_TYPE, DATA_TABLE_NAME, LOOKUP_TYPE, '
364 || 'COL_SERIES_WIDTH, PROP_CALC_SERIES, BASE_LEVEL, EXPRESSION_TYPE, INT_AGGR_FUNC, '
365 || 'AGGR_BY, PRESERVATION_TYPE, IS_EDITABLE_SUMMARY, MOVE_PRESERVATION_TYPE, DATA_TYPE, '
366 || 'SAME_VAL_UPDATE )'
367 || ' VALUES ( '
368 || ' :1, :2, :3, 255, 1, '
369 || ' 1, 255, 1, 1, :4, '
370 || ' 1, ''###,###'', 0, :5, 1, '
371 || ' :6, :7, 0, :8, :9, '
372 || ' 0, :10, 0, 0, 250, '
373 || ' 0, :11, :12, :13, 0, '
374 || ' 10, :14, :15, :16, :17, '
375 || ' :18, :19, 0, :20, :21, '
376 || ' 0 )';
377 log_debug (x_sql);
378 EXECUTE IMMEDIATE x_sql USING
379 x_forecast_type_id, p_computed_name, p_exp_template,
380 x_disp_order,
381 p_computed_title,
382 p_sum_func, p_scaleble, p_editable, p_is_proportion,
383 p_dbname,
384 p_hint_message, p_hist_pred_type, p_data_table_name,
385 p_prop_calc_series, p_base_level, p_expression_type, p_int_aggr_func,
386 p_aggr_by, p_preservation_type, p_move_preservation_type, p_data_type;
387 COMMIT;
388 log_debug ('Insert statement executed successfully');
389
390 log_debug ('Exiting msd_dem_demantra_utilities.create_series ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
391 RETURN x_forecast_type_id;
392
393 EXCEPTION
394 WHEN OTHERS THEN
395 log_message ('Exception in function msd_dem_demantra_utilities.create_series ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
396 log_message (substr(SQLERRM,1,150));
397 RETURN -1;
398
399 END CREATE_SERIES;
400
401
402
403
404 /*
405 * This function deletes the series given in Demantra Schema.
406 * The function returns -
407 * n : The series id in case of success
408 * -1 : in case of error
409 * -2 : if series is not present
410 * -3 : Unable to set demantra schema name
411 */
412 FUNCTION DELETE_SERIES ( p_computed_name IN VARCHAR2 )
413 RETURN NUMBER
414 IS
415
416 x_series_id NUMBER := NULL;
417 x_dbname VARCHAR2(30) := NULL;
418 x_return_value NUMBER := NULL;
419 x_sql VARCHAR2(4000) := NULL;
420
421 BEGIN
422
423 IF (g_demantra_schema_name IS NULL)
424 THEN
425 IF (set_demantra_schema = -1)
426 THEN
427 RETURN -3;
428 END IF;
429 END IF;
430
431
432 log_debug ('Entering msd_dem_demantra_utilities.delete_series ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
433
434 log_debug ('Get series info for ' || p_computed_name);
435 x_sql := 'SELECT forecast_type_id, dbname '
436 || ' FROM ' || g_demantra_schema_name || '.COMPUTED_FIELDS '
437 || ' WHERE computed_name = :1 ';
438 log_debug (x_sql);
439 EXECUTE IMMEDIATE x_sql
440 INTO x_series_id, x_dbname
441 USING p_computed_name;
442
443 log_debug ('Deleting all records for series id : ' || to_char(x_series_id));
444
445 log_debug ('Deleting records from series_groups_m table ');
446 x_sql := 'DELETE FROM ' || g_demantra_schema_name || '.SERIES_GROUPS_M WHERE series_id = :1 ';
447 log_debug (x_sql);
448 EXECUTE IMMEDIATE x_sql USING x_series_id;
449
450 log_debug ('Deleting records from user_security_series table ');
451 x_sql := 'DELETE FROM ' || g_demantra_schema_name || '.USER_SECURITY_SERIES WHERE series_id = :1 ';
452 log_debug (x_sql);
453 EXECUTE IMMEDIATE x_sql USING x_series_id;
454
455 log_debug ('Deleting records from dcm_products_series table ');
456 x_sql := 'DELETE FROM ' || g_demantra_schema_name || '.DCM_PRODUCTS_SERIES WHERE series_id = :1 ';
457 log_debug (x_sql);
458 EXECUTE IMMEDIATE x_sql USING x_series_id;
459
460 log_debug ('Deleting records from transfer_query_series table ');
461 x_sql := 'DELETE FROM ' || g_demantra_schema_name || '.TRANSFER_QUERY_SERIES WHERE series_id = :1 ';
462 log_debug (x_sql);
463 EXECUTE IMMEDIATE x_sql USING x_series_id;
464
465 log_debug ('Deleting records from computed_fields table ');
466 x_sql := 'DELETE FROM ' || g_demantra_schema_name || '.COMPUTED_FIELDS WHERE forecast_type_id = :1 ';
467 log_debug (x_sql);
468 EXECUTE IMMEDIATE x_sql USING x_series_id;
469
470 COMMIT;
471 log_debug ('Series deleted successfully');
472
473
474 log_debug ('Drop the db column for the series if present');
475 IF (x_dbname IS NOT NULL)
476 THEN
477
478 log_debug ('First check if the column ' || x_dbname || ' exists or not.');
479 x_sql := 'SELECT count(1) FROM ALL_TAB_COLUMNS WHERE '
480 || ' owner = upper( :1 ) '
481 || ' AND table_name = ''SALES_DATA'' '
482 || ' AND column_name = upper (:2 ) ';
483 log_debug (x_sql);
484 EXECUTE IMMEDIATE x_sql
485 INTO x_return_value
486 USING g_demantra_schema_name, x_dbname;
487
488 IF (x_return_value = 0)
489 THEN
490 log_message ('Column ' || x_dbname || ' does not exists in the SALES_DATA table');
491 RETURN x_series_id;
492 END IF;
493
494 x_sql := 'ALTER TABLE ' || g_demantra_schema_name || '.SALES_DATA'
495 || ' DROP ( ' || x_dbname || ' ) ';
496 log_debug (x_sql);
497 EXECUTE IMMEDIATE x_sql;
498 log_debug ('Column ' || x_dbname || 'dropped successfully');
499
500 END IF;
501
502 log_debug ('Exiting msd_dem_demantra_utilities.delete_series ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
503 RETURN x_series_id;
504
505 EXCEPTION
506 WHEN NO_DATA_FOUND THEN
507 log_message ('Exception(1) in function msd_dem_demantra_utilities.delete_series ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
508 log_message (substr(SQLERRM,1,150));
509 log_message ('Series : ' || p_computed_name || ' is not present. ');
510 RETURN -2;
511 WHEN OTHERS THEN
512 log_message ('Exception(2) in function msd_dem_demantra_utilities.delete_series ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
513 log_message (substr(SQLERRM,1,150));
514 RETURN -1;
515
516 END DELETE_SERIES;
517
518
519
520
521 /*
522 * This function adds the given series to the given component and also
523 * to the user who owns the component.
524 * The function returns -
525 * 0 : In case of success
526 * -1 : in case of error
527 * -3 : Unable to set demantra schema name
528 */
529 FUNCTION ADD_SERIES_TO_COMPONENT (
530 p_series_id IN NUMBER,
531 p_component_id IN NUMBER)
532 RETURN NUMBER
533 IS
534
535 x_computed_title VARCHAR2(100) := NULL;
536 x_product_name VARCHAR2(255) := NULL;
537 x_user_id NUMBER := NULL;
538 x_sql VARCHAR2(4000) := NULL;
539
540 BEGIN
541
542 IF (g_demantra_schema_name IS NULL)
543 THEN
544 IF (set_demantra_schema = -1)
545 THEN
546 RETURN -3;
547 END IF;
548 END IF;
549
550
551 log_debug ('Entering msd_dem_demantra_utilities.add_series_to_component ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
552
553 log_debug ('Check if series with id : ' || to_char(p_series_id) || ' is present or not.');
554 x_sql := ' SELECT computed_title FROM ' || g_demantra_schema_name || '.COMPUTED_FIELDS WHERE forecast_type_id = :1 ';
555 log_debug (x_sql);
556 EXECUTE IMMEDIATE x_sql
557 INTO x_computed_title
558 USING p_series_id;
559
560 log_debug ('Check if component with id : ' || to_char(p_component_id) || ' is present or not.');
561 x_sql := 'SELECT product_name, user_id FROM ' || g_demantra_schema_name || '.DCM_PRODUCTS WHERE dcm_product_id = :1 ';
562 log_debug (x_sql);
563 EXECUTE IMMEDIATE x_sql
564 INTO x_product_name,
565 x_user_id
566 USING p_component_id;
567
568 log_debug ('Deleting series : ' || x_computed_title || ' from the component : ' || x_product_name ||' if it exists');
569 x_sql := 'DELETE FROM ' || g_demantra_schema_name || '.DCM_PRODUCTS_SERIES WHERE DCM_PRODUCT_ID = :1 AND SERIES_ID = :2 ';
570 log_debug (x_sql);
571 EXECUTE IMMEDIATE x_sql USING p_component_id, p_series_id;
572
573 log_debug ('Adding series : ' || x_computed_title || ' to the component : ' || x_product_name);
574 x_sql := 'INSERT INTO ' || g_demantra_schema_name || '.DCM_PRODUCTS_SERIES ( DCM_PRODUCT_ID, SERIES_ID ) '
575 || ' VALUES ( :1, :2 ) ';
576 log_debug (x_sql);
577 EXECUTE IMMEDIATE x_sql USING p_component_id, p_series_id;
578
579 log_debug ('Deleting series : ' || x_computed_title || ' from the user with id : ' || to_char(x_user_id)||' if it exists');
580 x_sql := 'DELETE FROM ' || g_demantra_schema_name || '.USER_SECURITY_SERIES WHERE USER_ID = :1 AND SERIES_ID= :2 ';
581 log_debug (x_sql);
582 EXECUTE IMMEDIATE x_sql USING x_user_id, p_series_id;
583
584 log_debug ('Adding series : ' || x_computed_title || ' to the user with id : ' || to_char(x_user_id));
585 x_sql := 'INSERT INTO ' || g_demantra_schema_name || '.USER_SECURITY_SERIES ( USER_ID, SERIES_ID ) '
586 || ' VALUES ( :1, :2 ) ';
587 log_debug (x_sql);
588 EXECUTE IMMEDIATE x_sql USING x_user_id, p_series_id;
589
590 COMMIT;
591 log_debug ('Series successfully added to the component and its owner.');
592
593 log_debug ('Exiting msd_dem_demantra_utilities.add_series_to_component ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
594 RETURN 0;
595
596
597 EXCEPTION
598 WHEN OTHERS THEN
599 log_message ('Exception in function msd_dem_demantra_utilities.add_series_to_component ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
600 log_message (substr(SQLERRM,1,150));
601 RETURN -1;
602
603 END ADD_SERIES_TO_COMPONENT;
604
605
606
607
608 /*
609 * This function creates an integration interface given name and description
610 * the the owning user.
611 * The function returns -
612 * n : integration interface id
613 * -1 : in case of error
614 * -3 : Unable to set demantra schema name
615 * -4 : If an integration interface with the same name already exists
616 * -5 : Unable to get next sequence value for integration interface id
617 */
618 FUNCTION CREATE_INTEGRATION_INTERFACE (
619 p_name IN VARCHAR2,
620 p_description IN VARCHAR2,
621 p_user_id IN NUMBER)
622 RETURN NUMBER
623 IS
624
625 x_return_value NUMBER := NULL;
626 x_integration_interface_id NUMBER := NULL;
627 x_sql VARCHAR2(4000) := NULL;
628
629 BEGIN
630
631 IF (g_demantra_schema_name IS NULL)
632 THEN
633 IF (set_demantra_schema = -1)
634 THEN
635 RETURN -3;
636 END IF;
637 END IF;
638
639 log_debug ('Entering msd_dem_demantra_utilities.create_integration_interface ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
640
641 log_debug ('Check if an integration interface with the same name already exists');
642 x_sql := 'SELECT count(1) FROM ' || g_demantra_schema_name || '.TRANSFER_LIST WHERE name = :1 ';
643 log_debug (x_sql);
644 EXECUTE IMMEDIATE x_sql
645 INTO x_return_value
646 USING p_name;
647
648 IF (x_return_value = 1)
649 THEN
650 log_message ('Error(1) in function msd_dem_demantra_utilities.create_integration_interface ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
651 log_message ('Integration interface : ' || p_name || ' already exists.');
652 RETURN -4;
653 END IF;
654
655
656 log_debug ('Get the next sequence value for integration interface id');
657 x_integration_interface_id := get_sequence_nextval (
658 'TRANSFER_LIST',
659 'ID',
660 'TRANSFER_ID_SEQ');
661 IF (x_integration_interface_id < 0)
662 THEN
663 log_message ('Error(2) in function msd_dem_demantra_utilities.create_integration_interface ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
664 log_message ('Unable to get next sequence value for integration interface id');
665 RETURN -5;
666 END IF;
667 log_debug ('Next sequence value for id : ' || to_char(x_integration_interface_id));
668
669
670 log_debug ('Insert a row into TRANSFER_LIST for the integration interface');
671 x_sql := 'INSERT INTO ' || g_demantra_schema_name || '.TRANSFER_LIST '
672 || ' ( ID, NAME, DESCRIPTION, USER_ID, USE_EXTERNAL_SCHEMA ) '
673 || ' VALUES ( '
674 || ' :1, :2, :3, :4, 0 ) ';
675 log_debug(x_sql);
676 EXECUTE IMMEDIATE x_sql USING x_integration_interface_id, p_name, p_description, p_user_id;
677
678 COMMIT;
679 log_debug ('Integration Interface created successfully');
680
681 log_debug ('Exiting msd_dem_demantra_utilities.create_integration_interface ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
682 RETURN x_integration_interface_id;
683
684 EXCEPTION
685 WHEN OTHERS THEN
686 log_message ('Exception in function msd_dem_demantra_utilities.create_integration_interface ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
687 log_message (substr(SQLERRM,1,150));
688 RETURN -1;
689
690 END CREATE_INTEGRATION_INTERFACE;
691
692
693
694
695 /*
696 * This function creates an integration interface given name and description
697 * the the owning user.
698 * The function returns -
699 * 0 : in case of success (includes absence of the given integration interface name)
700 * -1 : in case of error
701 * -3 : Unable to set demantra schema name
702 */
703 FUNCTION DELETE_INTEGRATION_INTERFACE (p_name IN VARCHAR2)
704 RETURN NUMBER
705 IS
706
707 x_sql VARCHAR2(4000) := NULL;
708
709 BEGIN
710
711 IF (g_demantra_schema_name IS NULL)
712 THEN
713 IF (set_demantra_schema = -1)
714 THEN
715 RETURN -3;
716 END IF;
717 END IF;
718
719 log_debug ('Entering msd_dem_demantra_utilities.delete_integration_interface ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
720
721 log_debug ('Deleting the integration interface '|| p_name || ' all the profiles under it.');
722 x_sql := 'DELETE FROM ' || g_demantra_schema_name || '.TRANSFER_LIST WHERE name = :1 ';
723 log_debug (x_sql);
724 EXECUTE IMMEDIATE x_sql USING p_name;
725
726 COMMIT;
727 log_debug ('Integration Interface deleted successfully');
728
729 log_debug ('Exiting msd_dem_demantra_utilities.delete_integration_interface ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
730 RETURN 0;
731
732 EXCEPTION
733 WHEN OTHERS THEN
734 log_message ('Exception in function msd_dem_demantra_utilities.delete_integration_interface ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
735 log_message (substr(SQLERRM,1,150));
736 RETURN -1;
737
738 END DELETE_INTEGRATION_INTERFACE;
739
740
741
742
743 /*
744 * This function creates a data profile
745 * The function returns -
746 * n : the data profile id
747 * -1 : in case of error
748 * -3 : Unable to set demantra schema name
749 * -4 : Data Profile Name given already exists
750 * -5 : Unable to get next sequence value for data profile id
751 */
752 FUNCTION CREATE_DATA_PROFILE (
753 p_transfer_id IN NUMBER,
754 p_view_name IN VARCHAR2,
755 p_table_name IN VARCHAR2,
756 p_view_type IN NUMBER,
757 p_use_real_proportion IN NUMBER,
758 p_insertnewcombinations IN NUMBER,
759 p_insertforecasthorizon IN NUMBER,
760 p_query_name IN VARCHAR2,
761 p_description IN VARCHAR2,
762 p_time_res_id IN NUMBER,
763 p_from_date IN DATE,
764 p_until_date IN DATE,
765 p_relative_date IN NUMBER,
766 p_relative_from_date IN NUMBER,
767 p_relative_until_date IN NUMBER,
768 p_integration_type IN NUMBER,
769 p_export_type IN NUMBER )
770 RETURN NUMBER
771 IS
772
773 x_return_value NUMBER := NULL;
774 x_data_profile_id NUMBER := NULL;
775 x_sql VARCHAR2(4000) := NULL;
776
777 BEGIN
778
779 IF (g_demantra_schema_name IS NULL)
780 THEN
781 IF (set_demantra_schema = -1)
782 THEN
783 RETURN -3;
784 END IF;
785 END IF;
786
787 log_debug ('Entering msd_dem_demantra_utilities.create_data_profile ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
788
789 log_debug ('Check if a data profile with the same name already exists or not');
790 x_sql := 'SELECT count(1) FROM ' || g_demantra_schema_name || '.TRANSFER_QUERY WHERE query_name = :1 ';
791 log_debug (x_sql);
792 EXECUTE IMMEDIATE x_sql
793 INTO x_return_value
794 USING p_query_name;
795
796 IF (x_return_value = 1)
797 THEN
798 log_message ('Error(1) in function msd_dem_demantra_utilities.create_data_profile ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
799 log_message ('Data Profile : ' || p_query_name || ' already exists.');
800 RETURN -4;
801 END IF;
802
803
804 log_debug ('Get the next sequence value for data profile id');
805 x_data_profile_id := get_sequence_nextval (
806 'TRANSFER_QUERY',
807 'ID',
808 'TRANSFER_QUERY_SEQ');
809 IF (x_data_profile_id < 0)
810 THEN
811 log_message ('Error(2) in function msd_dem_demantra_utilities.create_data_profile ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
812 log_message ('Unable to get next sequence value for data profile id');
813 RETURN -5;
814 END IF;
815 log_debug ('Next sequence value for id : ' || to_char(x_data_profile_id));
816
817
818 log_debug ('Insert a row for the data profile into the table TRANSFER_QUERY');
819 x_sql := 'INSERT INTO ' || g_demantra_schema_name || '.TRANSFER_QUERY ( '
820 || ' ID, TRANSFER_ID, PRESENTATION_TYPE, VIEW_NAME, TABLE_NAME, '
821 || ' FILE_NAME, DELIMITER, IS_FIXED_WIDTH, VIEW_TYPE, USE_REAL_PROPORTION, '
822 || ' INSERTNEWCOMBINATIONS, INSERTFORECASTHORIZON, QUERY_NAME, DESCRIPTION, TIME_RES_ID, '
823 || ' FROM_DATE, UNTIL_DATE, RELATIVE_DATE, RELATIVE_FROM_DATE, RELATIVE_UNTIL_DATE, '
824 || ' UNIT_ID, INDEX_ID, DATA_SCALE, DM_ID, SCHEMA_ID, '
825 || ' QUERY_ID, INTEGRATION_TYPE, EXPORT_TYPE, BATCH_FILE, IMPORT_FROM_FILE, '
826 || ' LAST_EXPORT_DATE, FILTER_SD_BY_GL ) '
827 || ' VALUES ( '
828 || ' :1, :2, 1, :3, :4, '
829 || ' null, null, null, :5, :6, '
830 || ' :7, :8, :9, :10, :11, '
831 || ' :12, :13, :14, :15, :16, '
832 || ' 1, null, 1, null, null, '
833 || ' null, :17, :18, null, 0, '
834 || ' null, 0 ) ';
835 log_debug (x_sql);
836 EXECUTE IMMEDIATE x_sql
837 USING x_data_profile_id, p_transfer_id, p_view_name, p_table_name,
838 p_view_type, p_use_real_proportion,
839 p_insertnewcombinations, p_insertforecasthorizon, p_query_name, p_description, p_time_res_id,
840 p_from_date, p_until_date, p_relative_date, p_relative_from_date, p_relative_until_date,
841 p_integration_type, p_export_type;
842
843 /* -- nallkuma
844 log_debug ('Insert a row for the data profile into the table TRANSFER_QUERY_INTERSECTIONS');
845 x_sql := 'INSERT INTO ' || g_demantra_schema_name || '.TRANSFER_QUERY_INTERSECTIONS ( '
846 || ' ID, BASE_LEVEL_ID, TYPE ) '
847 || ' VALUES ( '
848 || ' :1, :2, 1 ) ';
849 log_debug (x_sql);
850 EXECUTE IMMEDIATE x_sql USING x_data_profile_id, p_base_level_id;
851 */
852
853 COMMIT;
854 log_debug ('Data Profile created successfully.');
855
856 log_debug ('Exiting msd_dem_demantra_utilities.create_data_profile ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
857 RETURN x_data_profile_id;
858
859 EXCEPTION
860 WHEN OTHERS THEN
861 log_message ('Exception in function msd_dem_demantra_utilities.create_data_profile ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
862 log_message (substr(SQLERRM,1,150));
863 RETURN -1;
864
865 END CREATE_DATA_PROFILE;
866
867
868
869
870 /*
871 * This function adds the given series to the data profile.
872 * The function returns -
873 * 0 : in case of success
874 * -1 : in case of error
875 * -3 : Unable to set demantra schema name
876 */
877 FUNCTION ADD_SERIES_TO_PROFILE (
878 p_data_profile_id IN NUMBER,
879 p_series_id IN NUMBER )
880 RETURN NUMBER
881 IS
882
883 x_sql VARCHAR2(4000) := NULL;
884
885 BEGIN
886
887 IF (g_demantra_schema_name IS NULL)
888 THEN
889 IF (set_demantra_schema = -1)
890 THEN
891 RETURN -3;
892 END IF;
893 END IF;
894
895 log_debug ('Entering msd_dem_demantra_utilities.add_series_to_profile ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
896
897 log_debug ('Adding series : ' || to_char(p_series_id) || ' to the profile : ' || to_char(p_data_profile_id));
898 x_sql := 'INSERT INTO ' || g_demantra_schema_name || '.TRANSFER_QUERY_SERIES ( ID, SERIES_ID, LOAD_OPTION, PURGE_OPTION ) '
899 || ' VALUES ( :1, :2, 0, 0 ) ';
900 log_debug (x_sql);
901 EXECUTE IMMEDIATE x_sql USING p_data_profile_id, p_series_id;
902
903 COMMIT;
904 log_debug ('Series added successfully to the data profile');
905
906 log_debug ('Exiting msd_dem_demantra_utilities.add_series_to_profile ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
907 RETURN 0;
908
909 EXCEPTION
910 WHEN OTHERS THEN
911 log_message ('Exception in function msd_dem_demantra_utilities.add_series_to_profile ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
912 log_message (substr(SQLERRM,1,150));
913 RETURN -1;
914
915 END ADD_SERIES_TO_PROFILE;
916
917
918 /*
919 * This function adds the given level to the data profile.
920 * The function returns -
921 * 0 : in case of success
922 * -1 : in case of error
923 * -3 : Unable to set demantra schema name
924 */
925 FUNCTION ADD_LEVEL_TO_PROFILE (
926 p_data_profile_id IN NUMBER,
927 p_level_id IN NUMBER,
928 p_lorder IN NUMBER )
929 RETURN NUMBER
930 IS
931
932 x_sql VARCHAR2(4000) := NULL;
933
934 BEGIN
935
936 IF (g_demantra_schema_name IS NULL)
937 THEN
938 IF (set_demantra_schema = -1)
939 THEN
940 RETURN -3;
941 END IF;
942 END IF;
943
944 log_debug ('Entering msd_dem_demantra_utilities.add_level_to_profile ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
945
946 log_debug ('Adding level : ' || to_char(p_level_id) || ' to the profile : ' || to_char(p_data_profile_id));
947 x_sql := 'INSERT INTO ' || g_demantra_schema_name || '.TRANSFER_QUERY_LEVELS ( ID, LEVEL_ID, LORDER ) '
948 || ' VALUES ( :1, :2, :3 ) ';
949 log_debug (x_sql);
950 EXECUTE IMMEDIATE x_sql USING p_data_profile_id, p_level_id, p_lorder;
951
952 COMMIT;
953 log_debug ('Level added successfully to the data profile');
954
955 log_debug ('Exiting msd_dem_demantra_utilities.add_level_to_profile ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
956 RETURN 0;
957
958 EXCEPTION
959 WHEN OTHERS THEN
960 log_message ('Exception in function msd_dem_demantra_utilities.add_level_to_profile ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
961 log_message (substr(SQLERRM,1,150));
962 RETURN -1;
963
964 END ADD_LEVEL_TO_PROFILE;
965
966
967
968
969
970 /*
971 * This function creates the given workflow schema.
972 * The function returns -
973 * n : the schema id
974 * -1 : in case of error
975 * -3 : Unable to set demantra schema name
976 * -4 : If the given workflow schema name already exists
977 * -5 : Unable to get next sequence value for schema id
978 */
979 FUNCTION CREATE_WORKFLOW_SCHEMA (
980 p_schema_name IN VARCHAR2,
981 p_schema_data IN VARCHAR2,
982 p_owner_id IN NUMBER,
983 p_creation_date IN DATE,
984 p_modified_date IN DATE,
985 p_schema_type IN NUMBER )
986 RETURN NUMBER
987 IS
988
989 x_return_value NUMBER := NULL;
990 x_schema_id NUMBER := NULL;
991 x_sql VARCHAR2(4000) := NULL;
992
993 BEGIN
994
995 IF (g_demantra_schema_name IS NULL)
996 THEN
997 IF (set_demantra_schema = -1)
998 THEN
999 RETURN -3;
1000 END IF;
1001 END IF;
1002
1003 log_debug ('Entering msd_dem_demantra_utilities.create_workflow_schema ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1004
1005 log_debug ('First check if the workflow schema : ' || p_schema_name || ' already exists or not. ');
1006 x_sql := 'SELECT count(1) FROM ' || g_demantra_schema_name || '.WF_SCHEMAS WHERE schema_name = :1 ';
1007 log_debug (x_sql);
1008 EXECUTE IMMEDIATE x_sql
1009 INTO x_return_value
1010 USING p_schema_name;
1011
1012 IF (x_return_value <> 0)
1013 THEN
1014 log_message ('Error(1) in function msd_dem_demantra_utilities.create_workflow_schema ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1015 log_message ('Workflow Schema : ' || p_schema_name || ' already exists.');
1016 RETURN -4;
1017 END IF;
1018
1019
1020 log_debug ('Get the next sequence value for schema id');
1021 x_schema_id := get_sequence_nextval (
1022 'WF_SCHEMAS',
1023 'SCHEMA_ID',
1024 'WF_SCHEMA_ID_SEQ');
1025 IF (x_schema_id < 0)
1026 THEN
1027 log_message ('Error(2) in function msd_dem_demantra_utilities.create_workflow_schema ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1028 log_message ('Unable to get next sequence value for schema id');
1029 RETURN -5;
1030 END IF;
1031 log_debug ('Next sequence value for id : ' || to_char(x_schema_id));
1032
1033
1034 log_debug ('Insert a row for the workflow schema into the table WF_SCHEMAS');
1035 x_sql := 'INSERT INTO ' || g_demantra_schema_name || '.WF_SCHEMAS ( '
1036 || ' SCHEMA_ID, SCHEMA_NAME, SCHEMA_DATA, STATUS, OWNER_ID, '
1037 || ' CREATION_DATE, MODIFIED_DATE, TEMPORARY, SCHEMA_TYPE ) '
1038 || ' VALUES ( '
1039 || ' :1, :2, :3, 1, :4, '
1040 || ' :5, :6, 0, :7 ) ';
1041 log_debug (x_sql);
1042 EXECUTE IMMEDIATE x_sql
1043 USING x_schema_id, p_schema_name, p_schema_data, p_owner_id,
1044 p_creation_date, p_modified_date, p_schema_type;
1045
1046 COMMIT;
1047 log_debug ('Workflow Schema created successfully');
1048
1049 log_debug ('Exiting msd_dem_demantra_utilities.create_workflow_schema ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1050 RETURN x_schema_id;
1051
1052 EXCEPTION
1053 WHEN OTHERS THEN
1054 log_message ('Exception in function msd_dem_demantra_utilities.create_workflow_schema ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1055 log_message (substr(SQLERRM,1,150));
1056 RETURN -1;
1057
1058 END CREATE_WORKFLOW_SCHEMA;
1059
1060
1061
1062
1063 /*
1064 * This function deletes the workflow schema given.
1065 * The function returns -
1066 * 0 : in case of success
1067 * -1 : in case of error
1068 * -3 : Unable to set demantra schema name
1069 */
1070 FUNCTION DELETE_WORKFLOW_SCHEMA ( p_schema_name IN VARCHAR2 )
1071 RETURN NUMBER
1072 IS
1073
1074 x_sql VARCHAR2(4000) := NULL;
1075
1076 BEGIN
1077
1078 IF (g_demantra_schema_name IS NULL)
1079 THEN
1080 IF (set_demantra_schema = -1)
1081 THEN
1082 RETURN -3;
1083 END IF;
1084 END IF;
1085
1086 log_debug ('Entering msd_dem_demantra_utilities.delete_workflow_schema ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1087
1088 log_debug ('Deleting workflow schema : ' || p_schema_name);
1089 x_sql := 'DELETE FROM ' || g_demantra_schema_name || '.WF_SCHEMAS WHERE schema_name = :1 ';
1090 log_debug (x_sql);
1091 EXECUTE IMMEDIATE x_sql USING p_schema_name;
1092
1093 COMMIT;
1094 log_debug ('Workflow Schema deleted successfully.');
1095
1096 log_debug ('Exiting msd_dem_demantra_utilities.delete_workflow_schema ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1097 RETURN 0;
1098
1099 EXCEPTION
1100 WHEN OTHERS THEN
1101 log_message ('Exception in function msd_dem_demantra_utilities.delete_workflow_schema ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1102 log_message (substr(SQLERRM,1,150));
1103 RETURN -1;
1104
1105 END DELETE_WORKFLOW_SCHEMA;
1106
1107
1108
1109
1110 /*
1111 * This function gets the demantra schema name.
1112 * <demantra schema name> : if demantra is installed.
1113 * null : if demantra is not installed.
1114 */
1115 FUNCTION GET_DEMANTRA_SCHEMA
1116 RETURN VARCHAR2
1117 IS
1118 BEGIN
1119
1120 IF (g_demantra_schema_name IS NULL)
1121 THEN
1122 IF (set_demantra_schema = -1)
1123 THEN
1124 RETURN null;
1125 END IF;
1126 END IF;
1127
1128 log_debug ('Exiting msd_dem_demantra_utilities.get_demantra_schema ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1129 RETURN g_demantra_schema_name;
1130
1131 EXCEPTION
1132 WHEN OTHERS THEN
1133 log_message ('Exception in function msd_dem_demantra_utilities.get_demantra_schema ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1134 log_message (substr(SQLERRM,1,150));
1135 RETURN null;
1136
1137 END GET_DEMANTRA_SCHEMA;
1138
1139
1140
1141
1142 /*
1143 * This function creates the given db object.
1144 * The function returns -
1145 * 0 : in case of success
1146 * -1 : in case of error
1147 * -3 : Unable to set demantra schema name
1148 */
1149 FUNCTION CREATE_DEMANTRA_DB_OBJECT (
1150 p_object_type IN VARCHAR2,
1151 p_object_name IN VARCHAR2,
1152 p_create_sql IN VARCHAR2)
1153 RETURN NUMBER
1154 IS
1155 BEGIN
1156
1157 IF (g_demantra_schema_name IS NULL)
1158 THEN
1159 IF (set_demantra_schema = -1)
1160 THEN
1161 RETURN -3;
1162 END IF;
1163 END IF;
1164
1165 log_debug ('Entering msd_dem_demantra_utilities.create_demantra_db_object ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1166
1167 log_debug ('Demantra object : ' || p_object_name);
1168 log_debug ('Object Type : ' || p_object_type);
1169
1170 log_debug ('Creating DB object.');
1171 log_debug (p_create_sql);
1172 EXECUTE IMMEDIATE p_create_sql;
1173
1174 log_debug ('DB Object created successfully.');
1175
1176 log_debug ('Exiting msd_dem_demantra_utilities.create_demantra_db_object ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1177 RETURN 0;
1178
1179 EXCEPTION
1180 WHEN OTHERS THEN
1181 log_message ('Exception in function msd_dem_demantra_utilities.create_demantra_db_object ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1182 log_message (substr(SQLERRM,1,150));
1183 RETURN -1;
1184
1185 END CREATE_DEMANTRA_DB_OBJECT;
1186
1187
1188
1189
1190 /*
1191 * This function drop the given demantra db object.
1192 * The function returns -
1193 * 0 : in case of success/object not present
1194 * -1 : in case of error
1195 * -3 : Unable to set demantra schema name
1196 */
1197 FUNCTION DROP_DEMANTRA_DB_OBJECT (
1198 p_object_type IN VARCHAR2,
1199 p_object_name IN VARCHAR2 )
1200 RETURN NUMBER
1201 IS
1202
1203 x_return_value NUMBER := NULL;
1204 x_sql VARCHAR2(4000) := NULL;
1205
1206 BEGIN
1207
1208 IF (g_demantra_schema_name IS NULL)
1209 THEN
1210 IF (set_demantra_schema = -1)
1211 THEN
1212 RETURN -3;
1213 END IF;
1214 END IF;
1215
1216 log_debug ('Entering msd_dem_demantra_utilities.drop_demantra_db_object ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1217
1218 log_debug ('Demantra object : ' || p_object_name);
1219 log_debug ('Object Type : ' || p_object_type);
1220
1221 log_debug ('First check if the object already exists or not');
1222 x_sql := 'SELECT 1 FROM all_objects WHERE owner = :1 AND object_type = :2 AND object_name = :3';
1223 log_debug (x_sql);
1224 EXECUTE IMMEDIATE x_sql
1225 INTO x_return_value
1226 USING g_demantra_schema_name, p_object_type, p_object_name;
1227
1228 log_debug ('Dropping Demantra DB object');
1229 x_sql := ' DROP ' || p_object_type || ' ' || g_demantra_schema_name || '.' || p_object_name;
1230 log_debug (x_sql);
1231 EXECUTE IMMEDIATE x_sql;
1232
1233 log_debug ('Demantra Object dropped successfully');
1234
1235 log_debug ('Exiting msd_dem_demantra_utilities.drop_demantra_db_object ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1236 RETURN 0;
1237
1238 EXCEPTION
1239
1240 WHEN NO_DATA_FOUND THEN
1241 log_message ('Exception(1) in function msd_dem_demantra_utilities.drop_demantra_db_object ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1242 log_message (substr(SQLERRM,1,150));
1243 log_message ('Object to be dropped not found. Ignore this error.');
1244 RETURN 0;
1245
1246 WHEN OTHERS THEN
1247 log_message ('Exception(2) in function msd_dem_demantra_utilities.drop_demantra_db_object ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1248 log_message (substr(SQLERRM,1,150));
1249 RETURN -1;
1250
1251 END DROP_DEMANTRA_DB_OBJECT;
1252
1253
1254
1255
1256 /*
1257 * This function work only if Demantra and APS are on the same DB instance.
1258 * This function creates a synonym in the APPS schema using the given sql
1259 * script.
1260 * The function returns -
1261 * 0 : in case of success
1262 * -1 : in case of error
1263 * -3 : Unable to set demantra schema name
1264 */
1265 FUNCTION CREATE_SYNONYM_IN_EBS (
1266 p_object_name IN VARCHAR2,
1267 p_create_replace_sql IN VARCHAR2)
1268 RETURN NUMBER
1269 IS
1270 BEGIN
1271
1272 IF (g_demantra_schema_name IS NULL)
1273 THEN
1274 IF (set_demantra_schema = -1)
1275 THEN
1276 RETURN -3;
1277 END IF;
1278 END IF;
1279
1280 log_debug ('Entering msd_dem_demantra_utilities.create_synonym_in_ebs ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1281
1282 log_debug ('Object Name : ' || p_object_name);
1283
1284 /* First check if Demantra and APS are on the same DB instance */
1285 IF (g_is_demantra_schema)
1286 THEN
1287 log_message ('Note in function msd_dem_demantra_utilities.create_synonym_in_ebs ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1288 log_message ('Synonym ' || p_object_name || 'not created. Create the synonym manually.');
1289 RETURN 0;
1290 END IF;
1291
1292 log_debug ('Creating Synonym.');
1293 log_debug (p_create_replace_sql);
1294 EXECUTE IMMEDIATE p_create_replace_sql;
1295
1296 log_debug ('Synonym created successfully.');
1297
1298 log_debug ('Exiting msd_dem_demantra_utilities.create_synonym_in_ebs ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1299 RETURN 0;
1300
1301 EXCEPTION
1302 WHEN OTHERS THEN
1303 log_message ('Exception in function msd_dem_demantra_utilities.create_synonym_in_ebs ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1304 log_message (substr(SQLERRM,1,150));
1305 RETURN -1;
1306
1307 END CREATE_SYNONYM_IN_EBS;
1308
1309
1310
1311 /*
1312 * This function will insert base_level_id values
1313 * into the TRANSFER_QUERY_INTERSECTIONS table against the given data profile.
1314 * The TYPE column is always defaulted to value "1".
1315 * The function returns -
1316 * 0 : in case of success
1317 * -1 : in case of error
1318 * -3 : Unable to set demantra schema name
1319 */
1320 FUNCTION ADD_INTERSECT_TO_PROFILE (
1321 p_data_profile_id IN NUMBER,
1322 p_base_level_id IN VARCHAR2)
1323 RETURN NUMBER
1324 IS
1325
1326 x_sql VARCHAR2(4000) := NULL;
1327
1328 BEGIN
1329
1330 IF (g_demantra_schema_name IS NULL)
1331 THEN
1332 IF (set_demantra_schema = -1)
1333 THEN
1334 RETURN -3;
1335 END IF;
1336 END IF;
1337
1338 log_debug ('Entering msd_dem_demantra_utilities.add_intersect_to_profile ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1339
1340 log_debug ('Adding base_level_id : ' || to_char(p_base_level_id) || ' to the profile : ' || to_char(p_data_profile_id));
1341 x_sql := 'INSERT INTO ' || g_demantra_schema_name || '.TRANSFER_QUERY_INTERSECTIONS ( ID, BASE_LEVEL_ID, TYPE ) '
1342 || ' VALUES ( :1, :2, 1 ) ';
1343 log_debug (x_sql);
1344 EXECUTE IMMEDIATE x_sql USING p_data_profile_id, p_base_level_id;
1345
1346 COMMIT;
1347 log_debug ('Base level id added successfully to the data profile');
1348
1349 log_debug ('Exiting msd_dem_demantra_utilities.add_intersect_to_profile ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1350 RETURN 0;
1351
1352 EXCEPTION
1353 WHEN OTHERS THEN
1354 log_message ('Exception in function msd_dem_demantra_utilities.add_intersect_to_profile ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1355 log_message (substr(SQLERRM,1,150));
1356 RETURN -1;
1357
1358 END ADD_INTERSECT_TO_PROFILE;
1359
1360 /*
1361 * Update the synonyms MSD_DEM_TRANSFER_LIST and MSD_DEM_TRANSFER_QUERY
1362 * to point to the Demantra's tables TRANSFER_LIST and TRANSFER_QUERY
1363 * if Demantra is installed.
1364 * Sets the profile MSD_DEM_SCHEMA to the Demantra Schema Name
1365 * The checks if the table MDP_MATRIX exists in the Demantra Schema
1366 */
1367
1368 PROCEDURE UPDATE_SYNONYMS (
1369 errbuf OUT NOCOPY VARCHAR2,
1370 retcode OUT NOCOPY VARCHAR2,
1371 p_demantra_schema IN VARCHAR2 DEFAULT NULL)
1372
1373 IS
1374
1375 CURSOR c_get_dm_schema
1376 IS
1377 SELECT owner
1378 FROM dba_objects
1379 WHERE owner = owner
1380 AND object_type = 'TABLE'
1381 AND object_name = 'MDP_MATRIX'
1382 ORDER BY created desc;
1383
1384 CURSOR c_is_cols_present (p_owner VARCHAR2,
1385 p_table_name VARCHAR2,
1386 p_column_name VARCHAR2,
1387 p_data_type VARCHAR2)
1388 IS
1389 SELECT count(1)
1390 FROM dba_tab_columns
1391 WHERE owner = p_owner
1392 AND table_name = p_table_name
1393 AND column_name = p_column_name
1394 AND data_type = p_data_type;
1395
1396 CURSOR c_is_table_present (p_owner VARCHAR2,
1397 p_table_name VARCHAR2)
1398 IS
1399 SELECT count(1)
1400 FROM dba_tables
1401 WHERE owner = p_owner
1402 AND table_name = p_table_name;
1403
1404 x_dem_schema VARCHAR2(50) := NULL;
1405 x_create_synonym_sql VARCHAR2(200) := NULL;
1406 x_grant_sql VARCHAR2(200) := NULL;
1407 x_get_dem_ver_sql VARCHAR2(200) := NULL;
1408 x_dem_version VARCHAR2(20) := NULL;
1409 x_appl_home_page_mode VARCHAR2(20) := NULL;
1410 x_appl_home_page_url VARCHAR2(200) := NULL;
1411 x_ext_logout_url_sql VARCHAR2(200) := NULL;
1412 x_sql VARCHAR2(1000) := NULL;
1413 x_curr_val VARCHAR2(50) := NULL;
1414 x_success BOOLEAN := NULL;
1415 x_count1 NUMBER := NULL;
1416 x_count2 NUMBER := NULL;
1417 x_count3 NUMBER := NULL;
1418 x_count4 NUMBER := NULL;
1419
1420 x_col_present_flag NUMBER := NULL;
1421 x_boolean BOOLEAN := NULL;
1422 x_dummy1 VARCHAR2(100) := NULL;
1423 x_dummy2 VARCHAR2(100) := NULL;
1424 x_msc_schema VARCHAR2(30) := NULL;
1425 x_msd_schema VARCHAR2(30) := NULL;
1426
1427 BEGIN
1428
1429 IF (p_demantra_schema IS NULL)
1430 THEN
1431 OPEN c_get_dm_schema;
1432 FETCH c_get_dm_schema INTO x_dem_schema;
1433 CLOSE c_get_dm_schema;
1434 ELSE
1435 x_dem_schema := p_demantra_schema;
1436 END IF;
1437
1438 log_message ('The Demantra Schema Name is - ' || x_dem_schema);
1439
1440 X_boolean := fnd_installation.get_app_info ('MSD', X_dummy1, X_dummy2, X_msd_schema);
1441 X_boolean := fnd_installation.get_app_info ('MSC', X_dummy1, X_dummy2, X_msc_schema);
1442
1443 /* Demantra is Installed */
1444 IF (x_dem_schema IS NOT NULL)
1445 THEN
1446
1447
1448 /* Update synonym MSD_DEM_TRANSFER_LIST to point to Demantra table TRANSFER_LIST */
1449 x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM MSD_DEM_TRANSFER_LIST FOR ' ||
1450 x_dem_schema || '.TRANSFER_LIST';
1451
1452 EXECUTE IMMEDIATE x_create_synonym_sql;
1453 log_message ('Updated synonym MSD_DEM_TRANSFER_LIST');
1454
1455 /* Update synonym MSD_DEM_TRANSFER_QUERY to point to Demantra table TRANSFER_QUERY */
1456 x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM MSD_DEM_TRANSFER_QUERY FOR ' ||
1457 x_dem_schema || '.TRANSFER_QUERY';
1458
1459 EXECUTE IMMEDIATE x_create_synonym_sql;
1460 log_message ('Updated synonym MSD_DEM_TRANSFER_QUERY');
1461
1462
1463 /* Update synonym MSD_DEM_TRANSFER_QUERY_LEVELS to point to Demantra table TRANSFER_QUERY_LEVELS */
1464 x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM MSD_DEM_TRANSFER_QUERY_LEVELS FOR ' ||
1465 x_dem_schema || '.TRANSFER_QUERY_LEVELS';
1466
1467 EXECUTE IMMEDIATE x_create_synonym_sql;
1468 log_message ('Updated synonym MSD_DEM_TRANSFER_QUERY_LEVELS');
1469
1470
1471 /* Update synonym MSD_DEM_GROUP_TABLES to point to Demantra table GROUP_TABLES */
1472 x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM MSD_DEM_GROUP_TABLES FOR ' ||
1473 x_dem_schema || '.GROUP_TABLES';
1474
1475 EXECUTE IMMEDIATE x_create_synonym_sql;
1476 log_message ('Updated synonym MSD_DEM_GROUP_TABLES');
1477
1478
1479 /* Update synonym T_SRC_SALES_TMPL to point to Demantra table T_SRC_SALES_TMPL */
1480 x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM T_SRC_SALES_TMPL FOR '||
1481 x_dem_schema || '.T_SRC_SALES_TMPL';
1482
1483 EXECUTE IMMEDIATE x_create_synonym_sql;
1484 log_message ('Updated synonym T_SRC_SALES_TMPL');
1485
1486
1487 /* Update synonym MSD_DEM_RETURN_HISTORY to point to Demantra table MSD_DEM_RETURN_HISTORY */
1488 x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM MSD_DEM_RETURN_HISTORY FOR ' ||
1489 x_dem_schema || '.MSD_DEM_RETURN_HISTORY';
1490
1491 EXECUTE IMMEDIATE x_create_synonym_sql;
1492 log_message ('Updated synonym MSD_DEM_RETURN_HISTORY');
1493
1494 /* Grant execute permissions to Demantra Schema on pakcage MSD_DEM_UPLOAD_FORECAST */
1495 x_grant_sql := 'GRANT EXECUTE ON MSD_DEM_UPLOAD_FORECAST TO ' || x_dem_schema;
1496 EXECUTE IMMEDIATE x_grant_sql;
1497 log_message ('Execute privilege granted on package MSD_DEM_UPLOAD_FORECAST to ' || x_dem_schema || ' schema.');
1498
1499 x_grant_sql := 'GRANT EXECUTE ON MSD_DEM_SOP TO ' || x_dem_schema;
1500 EXECUTE IMMEDIATE x_grant_sql;
1501 log_message ('Execute privilege granted on package MSD_DEM_SOP to ' || x_dem_schema || ' schema.');
1502
1503 x_grant_sql := 'GRANT EXECUTE ON MSD_DEM_COMMON_UTILITIES TO ' || x_dem_schema;
1504 EXECUTE IMMEDIATE x_grant_sql;
1505 log_message ('Execute privilege granted on package MSD_DEM_COMMON_UTILITIES to ' || x_dem_schema || ' schema.');
1506
1507 x_grant_sql := 'GRANT EXECUTE ON MSC_PHUB_PKG TO ' || x_dem_schema;
1508 EXECUTE IMMEDIATE x_grant_sql;
1509 log_message ('Execute privilege granted on package MSC_PHUB_PKG to ' || x_dem_schema || ' schema.');
1510
1511 /* grant select on required tables */
1512 log_message ('Granting SELECT privilege on following tables to ' || x_dem_schema || ' schema.');
1513
1514 log_message('MSC.MSC_PLANS');
1515 x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_PLANS TO ' || x_dem_schema;
1516 execute immediate x_grant_sql;
1517
1518 log_message ('MSC.MSC_INT_PUBLISH_DATA');
1519 x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_INT_PUBLISH_DATA TO ' || x_dem_schema;
1520 execute immediate x_grant_sql;
1521
1522 log_message('MSC.MSC_INT_SUPPORTED_API');
1523 x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_INT_SUPPORTED_API TO ' || x_dem_schema;
1524 execute immediate x_grant_sql;
1525
1526 log_message('MSC.MSC_BIS_INV_DETAIL');
1527 x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_BIS_INV_DETAIL TO ' || x_dem_schema;
1528 execute immediate x_grant_sql;
1529
1530 log_message('MSC.MSC_SYSTEM_ITEMS');
1531 x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_SYSTEM_ITEMS TO ' || x_dem_schema;
1532 execute immediate x_grant_sql;
1533
1534 log_message('MSC.MSC_PLAN_ORGANIZATIONS');
1535 x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_PLAN_ORGANIZATIONS TO ' || x_dem_schema;
1536 execute immediate x_grant_sql;
1537
1538 log_message('MSC.MSC_TRADING_PARTNERS');
1539 x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_TRADING_PARTNERS TO ' || x_dem_schema;
1540 execute immediate x_grant_sql;
1541
1542 log_message('MSC.MSC_TP_ID_LID');
1543 x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_TP_ID_LID TO ' || x_dem_schema;
1544 execute immediate x_grant_sql;
1545
1546 log_message('MSC.MSC_TRADING_PARTNER_SITES');
1547 x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_TRADING_PARTNER_SITES TO ' || x_dem_schema;
1548 execute immediate x_grant_sql;
1549
1550 log_message('MSC.MSC_TP_SITE_ID_LID');
1551 x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_TP_SITE_ID_LID TO ' || x_dem_schema;
1552 execute immediate x_grant_sql;
1553
1554 log_message('MSC.MSC_REGIONS');
1555 x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_REGIONS TO ' || x_dem_schema;
1556 execute immediate x_grant_sql;
1557
1558 log_message('MSD.MSD_DEM_TIME');
1559 x_grant_sql := 'GRANT SELECT ON ' || x_msd_schema || '.MSD_DEM_TIME TO ' || x_dem_schema;
1560 execute immediate x_grant_sql;
1561
1562 log_message('MSD.MSD_DEM_CTO_BOM');
1563 x_grant_sql := 'GRANT SELECT ON ' || x_msd_schema || '.MSD_DEM_CTO_BOM TO ' || x_dem_schema;
1564 execute immediate x_grant_sql;
1565
1566 /* Set the profile MSD_DEM_SCHEMA if not set */
1567 x_curr_val := fnd_profile.value('MSD_DEM_SCHEMA');
1568
1569 IF (nvl(x_curr_val, '$$$') <> x_dem_schema)
1570 THEN
1571 x_success := fnd_profile.save ('MSD_DEM_SCHEMA', x_dem_schema, 'SITE');
1572 log_message ('Profile MSD_DEM: Schema has been set to ''' || x_dem_schema || ''' at the SITE level');
1573
1574 /*Setting global parameter */
1575 C_MSD_DEM_SCHEMA := x_dem_schema ;
1576 log_message ('Global Parameter C_MSD_DEM_SCHEMA has been set to ' || x_dem_schema );
1577
1578 MSD_DEM_SOP.C_MSD_DEM_SCHEMA := x_dem_schema;
1579
1580 MSD_DEM_CTO.CS_DEM_SYSDATE := msd_dem_common_utilities.get_demantra_date(sysdate, 1);
1581 MSD_DEM_CTO.CS_DEM_CTO_BASE_MODEL := msd_dem_common_utilities.get_lookup_code ('T_EP_CTO_DEMAND_TYPE', 1);
1582 MSD_DEM_CTO.CS_DEM_CTO_OPTION_CLASS := msd_dem_common_utilities.get_lookup_code ('T_EP_CTO_DEMAND_TYPE', 2);
1583 MSD_DEM_CTO.CS_DEM_CTO_OPTION := msd_dem_common_utilities.get_lookup_code ('T_EP_CTO_DEMAND_TYPE', 3);
1584 MSD_DEM_CTO.CS_DEM_LEVEL_ITEM_LABEL := msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_ITEM', 1, 'table_label');
1585 MSD_DEM_CTO.CS_DEM_LEVEL_ORG_LABEL := msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_ORGANIZATION', 1, 'table_label');
1586 MSD_DEM_CTO.CS_DEM_LEVEL_SITE_LABEL := msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_SITE', 1, 'table_label');
1587 MSD_DEM_CTO.CS_DEM_LEVEL_DC_LABEL := msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_DEMAND_CLASS', 1, 'table_label');
1588 MSD_DEM_CTO.CS_DEM_LEVEL_SC_LABEL := msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_SALES_CHANNEL', 1, 'table_label');
1589 MSD_DEM_CTO.CS_DEM_LEVEL_ITEM_ID := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_ITEM', 1, 'group_table_id'));
1590 MSD_DEM_CTO.CS_DEM_LEVEL_ORG_ID := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_ORGANIZATION', 1, 'group_table_id'));
1591 MSD_DEM_CTO.CS_DEM_LEVEL_SITE_ID := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_SITE', 1, 'group_table_id'));
1592 MSD_DEM_CTO.CS_DEM_LEVEL_DC_ID := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_DEMAND_CLASS', 1, 'group_table_id'));
1593 MSD_DEM_CTO.CS_DEM_LEVEL_SC_ID := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_SALES_CHANNEL', 1, 'group_table_id'));
1594
1595 MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_SPF_BASE_MODEL := msd_dem_common_utilities.get_lookup_code ('T_EP_SPF_DEMAND_TYPE', 1);
1596 MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_SPF_OPTION_CLASS := msd_dem_common_utilities.get_lookup_code ('T_EP_SPF_DEMAND_TYPE', 2);
1597 MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_SPF_OPTION := msd_dem_common_utilities.get_lookup_code ('T_EP_SPF_DEMAND_TYPE', 3);
1598
1599 MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_LEVEL_ASSET_GROUP_LABEL := msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_ASSET_GROUP', 1, 'table_label');
1600 MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_LEVEL_CLASS_CODE_LABEL := msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_CLASS_CODE', 1, 'table_label');
1601
1602 MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_LEVEL_ASSET_GROUP_ID := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_ASSET_GROUP', 1, 'group_table_id'));
1603 MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_LEVEL_CLASS_CODE_ID := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_CLASS_CODE', 1, 'group_table_id'));
1604
1605 -- Bug#15907861 (12.2.1) , 15907886 (12.3) --nallkuma
1606 MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_MATERIAL := msd_dem_common_utilities.get_lookup_code ('T_EP_I_ATT_10', 1);
1607 MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_PRODUCT := msd_dem_common_utilities.get_lookup_code ('T_EP_I_ATT_10', 2);
1608 MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_RESOURCE := msd_dem_common_utilities.get_lookup_code ('T_EP_I_ATT_10', 3);
1609 MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_WORK_ORDER := msd_dem_common_utilities.get_lookup_code ('T_EP_I_ATT_10', 4);
1610 MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_WORKORDER_ITEM := msd_dem_common_utilities.get_lookup_code ('T_EP_I_ATT_10', 4);
1611
1612 END IF;
1613
1614 /* Set the profile MSD_DEM_VERSION */ -- nallkuma
1615 x_get_dem_ver_sql := 'select version from ' || x_dem_schema || '.version_details' ;
1616 EXECUTE IMMEDIATE x_get_dem_ver_sql into x_dem_version ;
1617
1618 x_dem_version := SUBSTR(x_dem_version, 1, INSTR(x_dem_version, '.', 1, 2)-1) ;
1619
1620 x_success := fnd_profile.save ('MSD_DEM_VERSION', x_dem_version, 'SITE');
1621 log_message ('Profile MSD_DEM: Version has been set to ''' || x_dem_version || ''' at the SITE level');
1622
1623 /* Set the ExternalLogoutUrl parameter in demantra schema to the applications home page */ -- nallkuma 16-feb-2009
1624 /* 1st IF condtion :- This is only for demantra 7.3.X & above versions */ -- bug#7458724
1625 /* 2nd IF condtion :- Sets the ExternalLogoutUrl parameter to appl home page only if the appl home page mode is set "FWD" or else it will be null */
1626
1627 IF (TO_NUMBER(x_dem_version) >= 7.3)
1628 THEN
1629 x_appl_home_page_mode := fnd_profile.value('APPLICATIONS_HOME_PAGE') ;
1630 IF (x_appl_home_page_mode = 'FWK' OR x_appl_home_page_mode is null)
1631 THEN
1632 x_appl_home_page_url := fnd_profile.value('APPS_FRAMEWORK_AGENT') ;
1633 x_appl_home_page_url := trim(x_appl_home_page_url) || '/OA_HTML/OA.jsp?OAFunc=OAHOMEPAGE';
1634 x_ext_logout_url_sql := ' Update '|| x_dem_schema || '.sys_params' ||
1635 ' Set pval = ''' || x_appl_home_page_url ||
1636 ''' Where pname like ''ExternalLogoutUrl'' ' ;
1637 EXECUTE IMMEDIATE x_ext_logout_url_sql ;
1638 commit;
1639 log_message ('Updated ExternalLogoutUrl parameter in sys_params table to :- ' ||x_appl_home_page_url);
1640 END IF;
1641
1642 -- BUG#9211268 syenamar
1643 --Add new plan type lookup for RP in demantra (7.3)
1644 log_message('Checking for plan type lookup ''Rapid Plan'' in demantra');
1645
1646 x_sql := 'select count(1) from ' || x_dem_schema || '.plan_type_lookup where type_id = 2';
1647 execute immediate x_sql into x_count1;
1648
1649 IF (x_count1 = 0) THEN
1650 BEGIN
1651 log_message('Adding plan type lookup ''Rapid Plan'' in demantra');
1652 x_sql := 'INSERT INTO ' || x_dem_schema || '.plan_type_lookup (type_id, type_desc) values (2, ''Rapid Plan'')';
1653 execute immediate x_sql;
1654 commit;
1655 EXCEPTION
1656 WHEN OTHERS THEN
1657 log_message('Error when adding plan type lookup ''Rapid Plan'' in demantra - ' || substr(sqlerrm, 1, 150));
1658 END;
1659 END IF;
1660
1661 x_count1 := NULL;
1662 --syenamar
1663
1664 END IF;
1665
1666 /*
1667 * Set the system parameter Integration1CalendarLoad to query to table directly instead of synonym. EBR Impact.
1668 */
1669 EXECUTE IMMEDIATE ' UPDATE ' || x_dem_schema || '.sys_params set pval = ''' || x_msd_schema || '.MSD_DEM_TIME'' ' ||
1670 ' where pname = ''Integration1CalendarLoad''';
1671 COMMIT;
1672 log_message ('Updated Integration1CalendarLoad parameter in sys_params table to :- ' || x_msd_schema || '.MSD_DEM_TIME');
1673
1674 /* In case of Demantra 7.2.X, add the following columns
1675 * Table - T_SRC_SALES_TMPL, Columns to be added - COMPONENT_CODE, EBS_BASE_MODEL_SR_PK, COMPONENT_CODE_LEGACY and EBS_BASE_MODEL_CODE
1676 * Table - T_SRC_SALES_TMPL_ERR, Columns to be added - COMPONENT_CODE, EBS_BASE_MODEL_SR_PK, COMPONENT_CODE_LEGACY and EBS_BASE_MODEL_CODE
1677 */
1678 OPEN c_is_cols_present (x_dem_schema, 'T_SRC_SALES_TMPL', 'COMPONENT_CODE', 'VARCHAR2');
1679 FETCH c_is_cols_present INTO x_count1;
1680 CLOSE c_is_cols_present;
1681
1682 IF (x_count1 = 0)
1683 THEN
1684
1685 /* Start with dropping the standard error columns in the ERR table */
1686 x_sql := 'ALTER TABLE ' || x_dem_schema || '.T_SRC_SALES_TMPL_ERR '
1687 || ' DROP (ERROR_CODE_RECORD, LOAD_DATE, ERROR_MESSAGE_RECORD) ';
1688 EXECUTE IMMEDIATE x_sql;
1689 log_message ('Dropping columns ERROR_CODE_RECORD, LOAD_DATE and ERROR_MESSAGE_RECORD from Demantra table T_SRC_SALES_TMPL_ERR');
1690
1691
1692 /* Column - COMPONENT_CODE */
1693 EXECUTE IMMEDIATE 'ALTER TABLE ' || x_dem_schema || '.T_SRC_SALES_TMPL '
1694 || ' ADD (COMPONENT_CODE VARCHAR2(2000)) ';
1695 log_message ('Adding column COMPONENT_CODE to T_SRC_SALES_TMPL');
1696
1697 EXECUTE IMMEDIATE 'ALTER TABLE ' || x_dem_schema || '.T_SRC_SALES_TMPL_ERR '
1698 || ' ADD (COMPONENT_CODE VARCHAR2(2000)) ';
1699 log_message ('Adding column COMPONENT_CODE to T_SRC_SALES_TMPL_ERR');
1700
1701
1702 /* Column - EBS_BASE_MODEL_SR_PK */
1703 OPEN c_is_cols_present (x_dem_schema, 'T_SRC_SALES_TMPL', 'EBS_BASE_MODEL_SR_PK', 'NUMBER');
1704 FETCH c_is_cols_present INTO x_count2;
1705 CLOSE c_is_cols_present;
1706
1707 IF (x_count2 = 0)
1708 THEN
1709
1710 /* Column - EBS_BASE_MODEL_SR_PK */
1711 EXECUTE IMMEDIATE 'ALTER TABLE ' || x_dem_schema || '.T_SRC_SALES_TMPL '
1712 || ' ADD (EBS_BASE_MODEL_SR_PK NUMBER) ';
1713 log_message ('Adding column EBS_BASE_MODEL_SR_PK to T_SRC_SALES_TMPL');
1714
1715 EXECUTE IMMEDIATE 'ALTER TABLE ' || x_dem_schema || '.T_SRC_SALES_TMPL_ERR '
1716 || ' ADD (EBS_BASE_MODEL_SR_PK NUMBER) ';
1717 log_message ('Adding column EBS_BASE_MODEL_SR_PK to T_SRC_SALES_TMPL_ERR');
1718
1719 END IF;
1720
1721
1722 /* Column - COMPONENT_CODE_LEGACY */
1723 OPEN c_is_cols_present (x_dem_schema, 'T_SRC_SALES_TMPL', 'COMPONENT_CODE_LEGACY', 'VARCHAR2');
1724 FETCH c_is_cols_present INTO x_count2;
1725 CLOSE c_is_cols_present;
1726
1727 IF (x_count2 = 0)
1728 THEN
1729
1730 /* Column - COMPONENT_CODE_LEGACY */
1731 EXECUTE IMMEDIATE 'ALTER TABLE ' || x_dem_schema || '.T_SRC_SALES_TMPL '
1732 || ' ADD (COMPONENT_CODE_LEGACY VARCHAR2(4000)) ';
1733 log_message ('Adding column COMPONENT_CODE_LEGACY to T_SRC_SALES_TMPL');
1734
1735 EXECUTE IMMEDIATE 'ALTER TABLE ' || x_dem_schema || '.T_SRC_SALES_TMPL_ERR '
1736 || ' ADD (COMPONENT_CODE_LEGACY VARCHAR2(4000)) ';
1737 log_message ('Adding column COMPONENT_CODE_LEGACY to T_SRC_SALES_TMPL_ERR');
1738
1739 END IF;
1740
1741
1742 /* Column - EBS_BASE_MODEL_CODE */
1743 OPEN c_is_cols_present (x_dem_schema, 'T_SRC_SALES_TMPL', 'EBS_BASE_MODEL_CODE', 'VARCHAR2');
1744 FETCH c_is_cols_present INTO x_count2;
1745 CLOSE c_is_cols_present;
1746
1747 IF (x_count2 = 0)
1748 THEN
1749
1750 /* Column - EBS_BASE_MODEL_CODE */
1751 EXECUTE IMMEDIATE 'ALTER TABLE ' || x_dem_schema || '.T_SRC_SALES_TMPL '
1752 || ' ADD (EBS_BASE_MODEL_CODE VARCHAR2(240)) ';
1753 log_message ('Adding column EBS_BASE_MODEL_CODE to T_SRC_SALES_TMPL');
1754
1755 EXECUTE IMMEDIATE 'ALTER TABLE ' || x_dem_schema || '.T_SRC_SALES_TMPL_ERR '
1756 || ' ADD (EBS_BASE_MODEL_CODE VARCHAR2(240)) ';
1757 log_message ('Adding column EBS_BASE_MODEL_CODE to T_SRC_SALES_TMPL_ERR');
1758
1759 END IF;
1760
1761 /* Add back the standard error columns in the ERR table */
1762 x_sql := 'ALTER TABLE ' || x_dem_schema || '.T_SRC_SALES_TMPL_ERR '
1763 || ' ADD (ERROR_CODE_RECORD NUMBER(2), LOAD_DATE DATE, ERROR_MESSAGE_RECORD VARCHAR2(2000)) ';
1764 EXECUTE IMMEDIATE x_sql;
1765 log_message ('Adding columns ERROR_CODE_RECORD, LOAD_DATE and ERROR_MESSAGE_RECORD to Demantra table T_SRC_SALES_TMPL_ERR');
1766
1767 END IF;
1768
1769
1770 log_message('Check if the table EP_T_SRC_SALES_TMPL_LD is present -');
1771
1772 x_count3 :=0;
1773
1774 SELECT count(1)
1775 INTO x_count3
1776 FROM dba_tables
1777 WHERE owner = x_dem_schema
1778 AND table_name = 'EP_T_SRC_SALES_TMPL_LD';
1779
1780 IF (x_count3 = 1)
1781 THEN
1782 log_message ('Check, drop and recreate the table EP_T_SRC_SALES_TMPL_LD - ');
1783 x_count2 := -1;
1784 x_sql := 'SELECT COUNT(1) FROM ( SELECT COLUMN_NAME FROM DBA_TAB_COLUMNS WHERE owner = ''' || x_dem_schema || ''' and table_name = ''T_SRC_SALES_TMPL'' '
1785 || ' MINUS '
1786 || ' SELECT COLUMN_NAME FROM DBA_TAB_COLUMNS WHERE owner = ''' || x_dem_schema || ''' and table_name = ''EP_T_SRC_SALES_TMPL_LD'' ) ';
1787 log_debug (x_sql);
1788 EXECUTE IMMEDIATE x_sql INTO x_count2;
1789
1790 IF (x_count2 <> 0)
1791 THEN
1792
1793 log_message ('Dropping table EP_T_SRC_SALES_TMPL_LD');
1794 EXECUTE IMMEDIATE 'DROP TABLE ' || x_dem_schema || '.EP_T_SRC_SALES_TMPL_LD';
1795
1796 log_message ('Creating table EP_T_SRC_SALES_TMPL_LD');
1797 x_sql := 'CREATE TABLE ' || x_dem_schema || '.EP_T_SRC_SALES_TMPL_LD '
1798 || ' AS '
1799 || ' SELECT tsst.*, TRUNC(tsst.sales_date) AGGRE_SD '
1800 || ' FROM ' || x_dem_schema || '.T_SRC_SALES_TMPL tsst '
1801 || ' WHERE 1 = 2 ';
1802 log_debug (x_sql);
1803 EXECUTE IMMEDIATE x_sql;
1804
1805 END IF;
1806 END IF;
1807
1808 -- Bug#13854593
1809 IF (TO_NUMBER(x_dem_version) >= 12.2) THEN
1810
1811 SELECT count(1)
1812 INTO x_count4
1813 FROM dba_tables
1814 WHERE owner = x_dem_schema
1815 AND table_name = 'T_SRC_SALES_TMPL_TMP';
1816
1817 IF (x_count4 <> 0) THEN
1818
1819 log_message ('Calling CALL_DM_BUILD_PROCEDURES.');
1820 x_sql := 'BEGIN ' || x_dem_schema || '.CALL_DM_BUILD_PROCEDURES; END;' ;
1821
1822 log_debug (x_sql);
1823 EXECUTE IMMEDIATE x_sql;
1824
1825 END IF;
1826
1827 END IF;
1828
1829 /* In case of Demantra 7.2.X, add the following column
1830 * Table - T_SRC_LOC_TMPL, Column to be added - T_EP_LR2A_DESC
1831 * Table - T_SRC_LOC_TMPL_ERR, Column to be added - T_EP_LR2A_DESC
1832 * Table - T_SRC_LOC_TMPL_ERR, Columns dropped/added - ERROR_CODE_RECORD , LOAD_DATE & ERROR_MESSAGE_RECORD
1833 * Bug#8367471 - nallkuma
1834 */
1835
1836 OPEN c_is_cols_present (x_dem_schema, 'T_SRC_LOC_TMPL', 'T_EP_LR2A_DESC', 'VARCHAR2');
1837 FETCH c_is_cols_present INTO x_col_present_flag;
1838 CLOSE c_is_cols_present;
1839
1840 IF( x_col_present_flag = 0 ) THEN
1841
1842 /* Add T_EP_LR2A_DESC column to the T_SRC_LOC_TMPL table*/
1843 EXECUTE IMMEDIATE 'ALTER TABLE ' || x_dem_schema || '.T_SRC_LOC_TMPL '
1844 || ' ADD (T_EP_LR2A_DESC VARCHAR2(100)) ';
1845 log_message ('Adding column T_EP_LR2A_DESC to T_SRC_LOC_TMPL');
1846
1847 /* Dropping the standard error columns in the ERR table */
1848 x_sql := 'ALTER TABLE ' || x_dem_schema || '.T_SRC_LOC_TMPL_ERR '
1849 || ' DROP (ERROR_CODE_RECORD, LOAD_DATE, ERROR_MESSAGE_RECORD) ';
1850 EXECUTE IMMEDIATE x_sql;
1851 log_message ('Dropping columns ERROR_CODE_RECORD, LOAD_DATE and ERROR_MESSAGE_RECORD from Demantra table T_SRC_LOC_TMPL_ERR');
1852
1853 /* Add T_EP_LR2A_DESC column to the T_SRC_LOC_TMPL _ERR table*/
1854 EXECUTE IMMEDIATE 'ALTER TABLE ' || x_dem_schema || '.T_SRC_LOC_TMPL_ERR '
1855 || ' ADD (T_EP_LR2A_DESC VARCHAR2(100)) ';
1856 log_message ('Adding column T_EP_LR2A_DESC to T_SRC_LOC_TMPL_ERR');
1857
1858 /* Add back the standard error columns in the ERR table */
1859 x_sql := 'ALTER TABLE ' || x_dem_schema || '.T_SRC_LOC_TMPL_ERR '
1860 || ' ADD (ERROR_CODE_RECORD NUMBER(2), LOAD_DATE DATE, ERROR_MESSAGE_RECORD VARCHAR2(2000)) ';
1861 EXECUTE IMMEDIATE x_sql;
1862 log_message ('Adding columns ERROR_CODE_RECORD, LOAD_DATE and ERROR_MESSAGE_RECORD to Demantra table T_SRC_LOC_TMPL_ERR');
1863
1864 END IF;
1865
1866 /* In case of Demantra 7.3, create the synonym BIIO_DSR_SALES_DATA in the apps schema */
1867 x_count1 := 0;
1868 OPEN c_is_table_present (x_dem_schema, 'BIIO_DSR_SALES_DATA');
1869 FETCH c_is_table_present INTO x_count1;
1870 CLOSE c_is_table_present;
1871
1872 IF (x_count1 <> 0)
1873 THEN
1874
1875 /* Create synonym BIIO_DSR_SALES_DATA to point to Demantra table BIIO_DSR_SALES_DATA */
1876 x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM BIIO_DSR_SALES_DATA FOR ' ||
1877 x_dem_schema || '.BIIO_DSR_SALES_DATA';
1878
1879 EXECUTE IMMEDIATE x_create_synonym_sql;
1880 log_message ('Created synonym BIIO_DSR_SALES_DATA');
1881
1882 END IF;
1883 END IF;
1884
1885 msd_dem_common_utilities.update_dem_apcc_synonym(errbuf,retcode);
1886 COMMIT;
1887
1888 EXCEPTION
1889 WHEN OTHERS THEN
1890 log_message(substr(SQLERRM,1,150));
1891 log_debug(substr(SQLERRM,1,150));
1892 retcode := -1;
1893 END UPDATE_SYNONYMS;
1894
1895
1896
1897 /*** PUBLIC FUNCTIONS - END ***/
1898
1899
1900
1901
1902
1903 END MSD_DEM_DEMANTRA_UTILITIES;