[Home] [Help]
PACKAGE BODY: APPS.MSD_DEM_DEMANTRA_UTILITIES
Source
1 PACKAGE BODY MSD_DEM_DEMANTRA_UTILITIES AS
2 /* $Header: msddemdub.pls 120.2.12010000.2 2008/05/07 11:01:53 sjagathe 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 RETURN;
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 RETURN;
110 END LOG_DEBUG;
111
112
113
114 /*** PUBLIC PROCEDURES - END ***/
115
116
117
118
119 /*** PUBLIC FUNCTIONS - BEGIN ***
120 * GET_SEQUENCE_NEXTVAL
121 * CREATE_SERIES
122 * DELETE_SERIES
123 * ADD_SERIES_TO_COMPONENT
124 * CREATE_INTEGRATION_INTERFACE
125 * DELETE_INTEGRATION_INTERFACE
126 * CREATE_DATA_PROFILE
127 * ADD_SERIES_TO_PROFILE
128 * ADD_LEVEL_TO_PROFILE
129 * CREATE_WORKFLOW_SCHEMA
130 * DELETE_WORKFLOW_SCHEMA
131 * GET_DEMANTRA_SCHEMA
132 * CREATE_DEMANTRA_DB_OBJECT
133 * DROP_DEMANTRA_DB_OBJECT
134 * CREATE_SYNONYM_IN_EBS
135 */
136
137
138 /*
139 * This function calls the GET_SEQ_NEXTVAL procedure in the Demantra schema.
140 * The function returns -
141 * n : next value for the given sequence
142 * -1 : If table is not present
143 * -2 : If column is not present
144 * -3 : Unable to set demantra schema name
145 * -4 : Any other error
146 */
147 FUNCTION GET_SEQUENCE_NEXTVAL (
148 p_table_name IN VARCHAR2,
149 p_column_name IN VARCHAR2,
150 p_seq_name IN VARCHAR2)
151 RETURN NUMBER
152 IS
153
154 x_sequence_nextval NUMBER := NULL;
155 x_sql VARCHAR2(2000) := NULL;
156
157 BEGIN
158
159 IF (g_demantra_schema_name IS NULL)
160 THEN
161 IF (set_demantra_schema = -1)
162 THEN
163 RETURN -3;
164 END IF;
165 END IF;
166
167 log_debug ('Entering msd_dem_demantra_utilities.get_sequence_nextval ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
168
169 IF (g_is_demantra_schema = FALSE)
170 THEN
171 log_debug ('Alter the schema for the session to ' || g_demantra_schema_name );
172 x_sql := 'ALTER SESSION SET CURRENT_SCHEMA = ' || g_demantra_schema_name;
173 log_debug (x_sql);
174 EXECUTE IMMEDIATE x_sql;
175 END IF;
176
177 log_debug ('Calling Demantra procedure GET_SEQ_NEXTVAL');
178 x_sql := 'BEGIN ' || g_demantra_schema_name || '.get_seq_nextval ( :1, :2, :3, :4); END;';
179 log_debug (x_sql);
180
181 EXECUTE IMMEDIATE x_sql USING p_table_name, p_column_name, p_seq_name, OUT x_sequence_nextval;
182
183 IF (x_sequence_nextval = -1)
184 THEN
185 log_message ('Error(1) in function msd_dem_demantra_utilities.get_sequence_nextval ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
186 log_message ('Table ' || p_table_name || 'does not exist.');
187 RETURN -1;
188 ELSIF (x_sequence_nextval = -1)
189 THEN
190 log_message ('Error(2) in function msd_dem_demantra_utilities.get_sequence_nextval ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
191 log_message ('Column ' || p_column_name || 'does not exist in the table ' || p_table_name || '.');
192 RETURN -2;
193 END IF;
194
195 log_debug ('Table : ' || p_table_name || ', Column Name : ' || p_column_name || ', Sequence Name : ' || p_seq_name);
196 log_debug ('The sequence next val is : ' || to_char(x_sequence_nextval));
197
198 IF (g_is_demantra_schema = FALSE)
199 THEN
200 log_debug ('Alter the schema for the session to APPS' );
201 x_sql := 'ALTER SESSION SET CURRENT_SCHEMA = APPS';
202 log_debug (x_sql);
203 EXECUTE IMMEDIATE x_sql;
204 END IF;
205
206 log_debug ('Exiting msd_dem_demantra_utilities.get_sequence_nextval ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
207 RETURN x_sequence_nextval;
208
209 EXCEPTION
210 WHEN OTHERS THEN
211
212 IF (g_is_demantra_schema = FALSE)
213 THEN
214 log_debug ('Alter the schema for the session to APPS' );
215 x_sql := 'ALTER SESSION SET CURRENT_SCHEMA = APPS';
216 log_debug (x_sql);
217 EXECUTE IMMEDIATE x_sql;
218 END IF;
219
220 log_message ('Exception in function msd_dem_demantra_utilities.get_sequence_nextval ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
221 log_message (substr(SQLERRM,1,150));
222 RETURN -4;
223
224 END GET_SEQUENCE_NEXTVAL;
225
226
227
228
229 /*
230 * This function creates the series given in Demantra schema.
231 * The function returns -
232 * n : The series id in case of success
233 * -1 : in case of error
234 * -3 : Unable to set demantra schema name
235 * -4 : Some of the mandatory parameters are NULL.
236 * -5 : Unable to get next sequence value for forecast type id
237 * -6 : Column already present in the table
238 */
239 FUNCTION CREATE_SERIES (
240 p_computed_name IN VARCHAR2,
241 p_exp_template IN VARCHAR2,
242 p_computed_title IN VARCHAR2,
243 p_sum_func IN VARCHAR2,
244 p_scaleble IN NUMBER,
245 p_editable IN NUMBER,
246 p_is_proportion IN NUMBER,
247 p_dbname IN VARCHAR2,
248 p_hint_message IN VARCHAR2,
249 p_hist_pred_type IN NUMBER,
250 p_data_table_name IN VARCHAR2,
251 p_prop_calc_series IN NUMBER,
252 p_base_level IN NUMBER,
253 p_expression_type IN NUMBER,
254 p_int_aggr_func IN VARCHAR2,
255 p_aggr_by IN NUMBER,
256 p_preservation_type IN NUMBER,
257 p_move_preservation_type IN NUMBER,
258 p_data_type IN NUMBER)
259 RETURN NUMBER
260
261 IS
262
263 x_forecast_type_id NUMBER := NULL;
264 x_disp_order NUMBER := NULL;
265 x_return_value NUMBER := NULL;
266 x_sql VARCHAR2(4000) := NULL;
267
268 BEGIN
269
270 IF (g_demantra_schema_name IS NULL)
271 THEN
272 IF (set_demantra_schema = -1)
273 THEN
274 RETURN -3;
275 END IF;
276 END IF;
277
278
279 log_debug ('Entering msd_dem_demantra_utilities.create_series ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
280
281 log_debug ('Verify that mandatory parameters should be not null');
282 IF ( p_computed_name IS NULL
283 OR p_exp_template IS NULL
284 OR p_computed_title IS NULL
285 OR p_sum_func IS NULL
286 OR p_scaleble IS NULL
287 OR p_editable IS NULL
288 OR p_is_proportion IS NULL
289 OR p_hist_pred_type IS NULL
290 OR p_data_table_name IS NULL
291 OR p_base_level IS NULL
292 OR p_expression_type IS NULL
293 OR p_int_aggr_func IS NULL
294 OR p_data_type IS NULL)
295 THEN
296 log_message ('Error(1) in function msd_dem_demantra_utilities.create_series ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
297 log_message ('Some of the mandatory parameters are null');
298 RETURN -4;
299 END IF;
300
301
302 log_debug ('Get the next sequence value for forecast type id');
303 x_forecast_type_id := get_sequence_nextval (
304 'COMPUTED_FIELDS',
305 'FORECAST_TYPE_ID',
306 'COMPUTED_FIELDS_SEQ');
307 IF (x_forecast_type_id < 0)
308 THEN
309 log_message ('Error(2) in function msd_dem_demantra_utilities.create_series ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
310 log_message ('Unable to get next sequence value for forecast type id');
311 RETURN -5;
312 END IF;
313 log_debug ('Next sequence value for forecast_type_id : ' || to_char(x_forecast_type_id));
314
315
316 log_debug ('Get the next value for disp order');
317 x_sql := 'SELECT max(disp_order) + 1 FROM ' || g_demantra_schema_name || '.COMPUTED_FIELDS ';
318 log_debug (x_sql);
319 EXECUTE IMMEDIATE x_sql INTO x_disp_order;
320 log_debug ('Next value for disp_order : ' || to_char(x_disp_order));
321
322
323 log_debug ('If parameter p_dbname is not null, then create the column in SALES_DATA table');
324 IF (p_dbname IS NOT NULL)
325 THEN
326
327 log_debug ('First check if the column ' || p_dbname || ' already exists or not.');
328 x_sql := 'SELECT count(1) FROM ALL_TAB_COLUMNS WHERE '
329 || ' owner = upper( :1 ) '
330 || ' AND table_name = ''SALES_DATA'' '
331 || ' AND column_name = upper (:2 ) ';
332 log_debug (x_sql);
333 EXECUTE IMMEDIATE x_sql
334 INTO x_return_value
335 USING g_demantra_schema_name, p_dbname;
336
337 IF (x_return_value = 1)
338 THEN
339 log_message ('Error(3) in function msd_dem_demantra_utilities.create_series ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
340 log_message ('Column ' || p_dbname || ' already exists in the SALES_DATA table');
341 RETURN -6;
342 END IF;
343
344 x_sql := 'ALTER TABLE ' || g_demantra_schema_name || '.SALES_DATA'
345 || ' ADD ( ' || p_dbname || ' NUMBER(20,10)) ';
346 log_debug (x_sql);
347 EXECUTE IMMEDIATE x_sql;
348 log_debug ('Column ' || p_dbname || 'created successfully');
349
350 END IF;
351
352 log_debug ('Build insert statement for creating record for the new series in the computed_fields table');
353 x_sql := NULL;
354
355 x_sql := 'INSERT INTO ' || g_demantra_schema_name || '.COMPUTED_FIELDS ( '
356 || 'FORECAST_TYPE_ID, COMPUTED_NAME, EXP_TEMPLATE, DISP_COLOR, DISP_LSTYLE, '
357 || 'DISP_LSYMBOL, PRINT_COLOR, PRINT_LSTYLE, PRINT_LSYMBOL, DISP_ORDER, '
358 || 'INFO_TYPE, TABLE_FORMAT, DO_HAVING, COMPUTED_TITLE, FIELD_TYPE, '
359 || 'SUM_FUNC, SCALEBLE, MODULE_TYPE, EDITABLE, IS_PROPORTION, '
360 || 'NULL_AS_ZERO, DBNAME, IS_DDLB, IS_CHECK, SERIES_WIDTH, '
361 || 'IS_DEFAULT, HINT_MESSAGE, HIST_PRED_TYPE, DATA_TABLE_NAME, LOOKUP_TYPE, '
362 || 'COL_SERIES_WIDTH, PROP_CALC_SERIES, BASE_LEVEL, EXPRESSION_TYPE, INT_AGGR_FUNC, '
363 || 'AGGR_BY, PRESERVATION_TYPE, IS_EDITABLE_SUMMARY, MOVE_PRESERVATION_TYPE, DATA_TYPE, '
364 || 'SAME_VAL_UPDATE )'
365 || ' VALUES ( '
366 || ' :1, :2, :3, 255, 1, '
367 || ' 1, 255, 1, 1, :4, '
368 || ' 1, ''###,###'', 0, :5, 1, '
369 || ' :6, :7, 0, :8, :9, '
370 || ' 0, :10, 0, 0, 250, '
371 || ' 0, :11, :12, :13, 0, '
372 || ' 10, :14, :15, :16, :17, '
373 || ' :18, :19, 0, :20, :21, '
374 || ' 0 )';
375 log_debug (x_sql);
376 EXECUTE IMMEDIATE x_sql USING
377 x_forecast_type_id, p_computed_name, p_exp_template,
378 x_disp_order,
379 p_computed_title,
380 p_sum_func, p_scaleble, p_editable, p_is_proportion,
381 p_dbname,
382 p_hint_message, p_hist_pred_type, p_data_table_name,
383 p_prop_calc_series, p_base_level, p_expression_type, p_int_aggr_func,
384 p_aggr_by, p_preservation_type, p_move_preservation_type, p_data_type;
385 COMMIT;
386 log_debug ('Insert statement executed successfully');
387
388 log_debug ('Exiting msd_dem_demantra_utilities.create_series ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
392 WHEN OTHERS THEN
389 RETURN x_forecast_type_id;
390
391 EXCEPTION
393 log_message ('Exception in function msd_dem_demantra_utilities.create_series ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
394 log_message (substr(SQLERRM,1,150));
395 RETURN -1;
396
397 END CREATE_SERIES;
398
399
400
401
402 /*
403 * This function deletes the series given in Demantra Schema.
404 * The function returns -
405 * n : The series id in case of success
406 * -1 : in case of error
407 * -2 : if series is not present
408 * -3 : Unable to set demantra schema name
409 */
410 FUNCTION DELETE_SERIES ( p_computed_name IN VARCHAR2 )
411 RETURN NUMBER
412 IS
413
414 x_series_id NUMBER := NULL;
415 x_dbname VARCHAR2(30) := NULL;
416 x_return_value NUMBER := NULL;
417 x_sql VARCHAR2(4000) := NULL;
418
419 BEGIN
420
421 IF (g_demantra_schema_name IS NULL)
422 THEN
423 IF (set_demantra_schema = -1)
424 THEN
425 RETURN -3;
426 END IF;
427 END IF;
428
429
430 log_debug ('Entering msd_dem_demantra_utilities.delete_series ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
431
432 log_debug ('Get series info for ' || p_computed_name);
433 x_sql := 'SELECT forecast_type_id, dbname '
434 || ' FROM ' || g_demantra_schema_name || '.COMPUTED_FIELDS '
435 || ' WHERE computed_name = :1 ';
436 log_debug (x_sql);
437 EXECUTE IMMEDIATE x_sql
438 INTO x_series_id, x_dbname
439 USING p_computed_name;
440
441 log_debug ('Deleting all records for series id : ' || to_char(x_series_id));
442
443 log_debug ('Deleting records from series_groups_m table ');
444 x_sql := 'DELETE FROM ' || g_demantra_schema_name || '.SERIES_GROUPS_M WHERE series_id = :1 ';
445 log_debug (x_sql);
446 EXECUTE IMMEDIATE x_sql USING x_series_id;
447
448 log_debug ('Deleting records from user_security_series table ');
449 x_sql := 'DELETE FROM ' || g_demantra_schema_name || '.USER_SECURITY_SERIES WHERE series_id = :1 ';
450 log_debug (x_sql);
451 EXECUTE IMMEDIATE x_sql USING x_series_id;
452
453 log_debug ('Deleting records from dcm_products_series table ');
454 x_sql := 'DELETE FROM ' || g_demantra_schema_name || '.DCM_PRODUCTS_SERIES WHERE series_id = :1 ';
455 log_debug (x_sql);
456 EXECUTE IMMEDIATE x_sql USING x_series_id;
457
458 log_debug ('Deleting records from transfer_query_series table ');
459 x_sql := 'DELETE FROM ' || g_demantra_schema_name || '.TRANSFER_QUERY_SERIES WHERE series_id = :1 ';
460 log_debug (x_sql);
461 EXECUTE IMMEDIATE x_sql USING x_series_id;
462
463 log_debug ('Deleting records from computed_fields table ');
464 x_sql := 'DELETE FROM ' || g_demantra_schema_name || '.COMPUTED_FIELDS WHERE forecast_type_id = :1 ';
465 log_debug (x_sql);
466 EXECUTE IMMEDIATE x_sql USING x_series_id;
467
468 COMMIT;
469 log_debug ('Series deleted successfully');
470
471
472 log_debug ('Drop the db column for the series if present');
473 IF (x_dbname IS NOT NULL)
474 THEN
475
476 log_debug ('First check if the column ' || x_dbname || ' exists or not.');
477 x_sql := 'SELECT count(1) FROM ALL_TAB_COLUMNS WHERE '
478 || ' owner = upper( :1 ) '
479 || ' AND table_name = ''SALES_DATA'' '
480 || ' AND column_name = upper (:2 ) ';
481 log_debug (x_sql);
482 EXECUTE IMMEDIATE x_sql
483 INTO x_return_value
484 USING g_demantra_schema_name, x_dbname;
485
486 IF (x_return_value = 0)
487 THEN
488 log_message ('Column ' || x_dbname || ' does not exists in the SALES_DATA table');
489 RETURN x_series_id;
490 END IF;
491
492 x_sql := 'ALTER TABLE ' || g_demantra_schema_name || '.SALES_DATA'
493 || ' DROP ( ' || x_dbname || ' ) ';
494 log_debug (x_sql);
495 EXECUTE IMMEDIATE x_sql;
496 log_debug ('Column ' || x_dbname || 'dropped successfully');
497
498 END IF;
499
500 log_debug ('Exiting msd_dem_demantra_utilities.delete_series ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
501 RETURN x_series_id;
502
503 EXCEPTION
504 WHEN NO_DATA_FOUND THEN
505 log_message ('Exception(1) in function msd_dem_demantra_utilities.delete_series ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
506 log_message (substr(SQLERRM,1,150));
507 log_message ('Series : ' || p_computed_name || ' is not present. ');
508 RETURN -2;
512 RETURN -1;
509 WHEN OTHERS THEN
510 log_message ('Exception(2) in function msd_dem_demantra_utilities.delete_series ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
511 log_message (substr(SQLERRM,1,150));
513
514 END DELETE_SERIES;
515
516
517
518
519 /*
520 * This function adds the given series to the given component and also
521 * to the user who owns the component.
522 * The function returns -
523 * 0 : In case of success
524 * -1 : in case of error
525 * -3 : Unable to set demantra schema name
526 */
527 FUNCTION ADD_SERIES_TO_COMPONENT (
528 p_series_id IN NUMBER,
529 p_component_id IN NUMBER)
530 RETURN NUMBER
531 IS
532
533 x_computed_title VARCHAR2(100) := NULL;
534 x_product_name VARCHAR2(255) := NULL;
535 x_user_id NUMBER := NULL;
536 x_sql VARCHAR2(4000) := NULL;
537
538 BEGIN
539
540 IF (g_demantra_schema_name IS NULL)
541 THEN
542 IF (set_demantra_schema = -1)
543 THEN
544 RETURN -3;
545 END IF;
546 END IF;
547
548
549 log_debug ('Entering msd_dem_demantra_utilities.add_series_to_component ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
550
551 log_debug ('Check if series with id : ' || to_char(p_series_id) || ' is present or not.');
552 x_sql := ' SELECT computed_title FROM ' || g_demantra_schema_name || '.COMPUTED_FIELDS WHERE forecast_type_id = :1 ';
553 log_debug (x_sql);
554 EXECUTE IMMEDIATE x_sql
555 INTO x_computed_title
556 USING p_series_id;
557
558 log_debug ('Check if component with id : ' || to_char(p_component_id) || ' is present or not.');
559 x_sql := 'SELECT product_name, user_id FROM ' || g_demantra_schema_name || '.DCM_PRODUCTS WHERE dcm_product_id = :1 ';
560 log_debug (x_sql);
561 EXECUTE IMMEDIATE x_sql
562 INTO x_product_name,
563 x_user_id
564 USING p_component_id;
565
566 log_debug ('Deleting series : ' || x_computed_title || ' from the component : ' || x_product_name ||' if it exists');
567 x_sql := 'DELETE FROM ' || g_demantra_schema_name || '.DCM_PRODUCTS_SERIES WHERE DCM_PRODUCT_ID = :1 AND SERIES_ID = :2 ';
568 log_debug (x_sql);
569 EXECUTE IMMEDIATE x_sql USING p_component_id, p_series_id;
570
571 log_debug ('Adding series : ' || x_computed_title || ' to the component : ' || x_product_name);
572 x_sql := 'INSERT INTO ' || g_demantra_schema_name || '.DCM_PRODUCTS_SERIES ( DCM_PRODUCT_ID, SERIES_ID ) '
573 || ' VALUES ( :1, :2 ) ';
574 log_debug (x_sql);
575 EXECUTE IMMEDIATE x_sql USING p_component_id, p_series_id;
576
577 log_debug ('Deleting series : ' || x_computed_title || ' from the user with id : ' || to_char(x_user_id)||' if it exists');
578 x_sql := 'DELETE FROM ' || g_demantra_schema_name || '.USER_SECURITY_SERIES WHERE USER_ID = :1 AND SERIES_ID= :2 ';
579 log_debug (x_sql);
580 EXECUTE IMMEDIATE x_sql USING x_user_id, p_series_id;
581
582 log_debug ('Adding series : ' || x_computed_title || ' to the user with id : ' || to_char(x_user_id));
583 x_sql := 'INSERT INTO ' || g_demantra_schema_name || '.USER_SECURITY_SERIES ( USER_ID, SERIES_ID ) '
584 || ' VALUES ( :1, :2 ) ';
585 log_debug (x_sql);
586 EXECUTE IMMEDIATE x_sql USING x_user_id, p_series_id;
587
588 COMMIT;
589 log_debug ('Series successfully added to the component and its owner.');
590
591 log_debug ('Exiting msd_dem_demantra_utilities.add_series_to_component ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
592 RETURN 0;
593
594
595 EXCEPTION
596 WHEN OTHERS THEN
597 log_message ('Exception in function msd_dem_demantra_utilities.add_series_to_component ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
598 log_message (substr(SQLERRM,1,150));
599 RETURN -1;
600
601 END ADD_SERIES_TO_COMPONENT;
602
603
604
605
606 /*
607 * This function creates an integration interface given name and description
608 * the the owning user.
609 * The function returns -
610 * n : integration interface id
611 * -1 : in case of error
612 * -3 : Unable to set demantra schema name
613 * -4 : If an integration interface with the same name already exists
614 * -5 : Unable to get next sequence value for integration interface id
615 */
616 FUNCTION CREATE_INTEGRATION_INTERFACE (
617 p_name IN VARCHAR2,
618 p_description IN VARCHAR2,
619 p_user_id IN NUMBER)
620 RETURN NUMBER
621 IS
622
623 x_return_value NUMBER := NULL;
624 x_integration_interface_id NUMBER := NULL;
625 x_sql VARCHAR2(4000) := NULL;
626
627 BEGIN
628
629 IF (g_demantra_schema_name IS NULL)
633 RETURN -3;
630 THEN
631 IF (set_demantra_schema = -1)
632 THEN
634 END IF;
635 END IF;
636
637 log_debug ('Entering msd_dem_demantra_utilities.create_integration_interface ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
638
639 log_debug ('Check if an integration interface with the same name already exists');
640 x_sql := 'SELECT count(1) FROM ' || g_demantra_schema_name || '.TRANSFER_LIST WHERE name = :1 ';
641 log_debug (x_sql);
642 EXECUTE IMMEDIATE x_sql
643 INTO x_return_value
644 USING p_name;
645
646 IF (x_return_value = 1)
647 THEN
648 log_message ('Error(1) in function msd_dem_demantra_utilities.create_integration_interface ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
649 log_message ('Integration interface : ' || p_name || ' already exists.');
650 RETURN -4;
651 END IF;
652
653
654 log_debug ('Get the next sequence value for integration interface id');
655 x_integration_interface_id := get_sequence_nextval (
656 'TRANSFER_LIST',
657 'ID',
658 'TRANSFER_ID_SEQ');
659 IF (x_integration_interface_id < 0)
660 THEN
661 log_message ('Error(2) in function msd_dem_demantra_utilities.create_integration_interface ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
662 log_message ('Unable to get next sequence value for integration interface id');
663 RETURN -5;
664 END IF;
665 log_debug ('Next sequence value for id : ' || to_char(x_integration_interface_id));
666
667
668 log_debug ('Insert a row into TRANSFER_LIST for the integration interface');
669 x_sql := 'INSERT INTO ' || g_demantra_schema_name || '.TRANSFER_LIST '
670 || ' ( ID, NAME, DESCRIPTION, USER_ID, USE_EXTERNAL_SCHEMA ) '
671 || ' VALUES ( '
672 || ' :1, :2, :3, :4, 0 ) ';
673 log_debug(x_sql);
674 EXECUTE IMMEDIATE x_sql USING x_integration_interface_id, p_name, p_description, p_user_id;
675
676 COMMIT;
677 log_debug ('Integration Interface created successfully');
678
679 log_debug ('Exiting msd_dem_demantra_utilities.create_integration_interface ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
680 RETURN x_integration_interface_id;
681
682 EXCEPTION
683 WHEN OTHERS THEN
684 log_message ('Exception in function msd_dem_demantra_utilities.create_integration_interface ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
685 log_message (substr(SQLERRM,1,150));
686 RETURN -1;
687
688 END CREATE_INTEGRATION_INTERFACE;
689
690
691
692
693 /*
694 * This function creates an integration interface given name and description
695 * the the owning user.
696 * The function returns -
697 * 0 : in case of success (includes absence of the given integration interface name)
698 * -1 : in case of error
699 * -3 : Unable to set demantra schema name
700 */
701 FUNCTION DELETE_INTEGRATION_INTERFACE (p_name IN VARCHAR2)
702 RETURN NUMBER
703 IS
704
705 x_sql VARCHAR2(4000) := NULL;
706
707 BEGIN
708
709 IF (g_demantra_schema_name IS NULL)
710 THEN
711 IF (set_demantra_schema = -1)
712 THEN
713 RETURN -3;
714 END IF;
715 END IF;
716
717 log_debug ('Entering msd_dem_demantra_utilities.delete_integration_interface ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
718
719 log_debug ('Deleting the integration interface '|| p_name || ' all the profiles under it.');
720 x_sql := 'DELETE FROM ' || g_demantra_schema_name || '.TRANSFER_LIST WHERE name = :1 ';
721 log_debug (x_sql);
722 EXECUTE IMMEDIATE x_sql USING p_name;
723
724 COMMIT;
725 log_debug ('Integration Interface deleted successfully');
726
727 log_debug ('Exiting msd_dem_demantra_utilities.delete_integration_interface ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
728 RETURN 0;
729
730 EXCEPTION
731 WHEN OTHERS THEN
732 log_message ('Exception in function msd_dem_demantra_utilities.delete_integration_interface ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
733 log_message (substr(SQLERRM,1,150));
734 RETURN -1;
735
736 END DELETE_INTEGRATION_INTERFACE;
737
738
739
740
741 /*
742 * This function creates a data profile
743 * The function returns -
744 * n : the data profile id
745 * -1 : in case of error
746 * -3 : Unable to set demantra schema name
747 * -4 : Data Profile Name given already exists
748 * -5 : Unable to get next sequence value for data profile id
749 */
750 FUNCTION CREATE_DATA_PROFILE (
754 p_view_type IN NUMBER,
751 p_transfer_id IN NUMBER,
752 p_view_name IN VARCHAR2,
753 p_table_name IN VARCHAR2,
755 p_use_real_proportion IN NUMBER,
756 p_insertnewcombinations IN NUMBER,
757 p_insertforecasthorizon IN NUMBER,
758 p_query_name IN VARCHAR2,
759 p_description IN VARCHAR2,
760 p_time_res_id IN NUMBER,
761 p_from_date IN DATE,
762 p_until_date IN DATE,
763 p_relative_date IN NUMBER,
764 p_relative_from_date IN NUMBER,
765 p_relative_until_date IN NUMBER,
766 p_integration_type IN NUMBER,
767 p_export_type IN NUMBER,
768 p_base_level_id IN NUMBER)
769 RETURN NUMBER
770 IS
771
772 x_return_value NUMBER := NULL;
773 x_data_profile_id NUMBER := NULL;
774 x_sql VARCHAR2(4000) := NULL;
775
776 BEGIN
777
778 IF (g_demantra_schema_name IS NULL)
779 THEN
780 IF (set_demantra_schema = -1)
781 THEN
782 RETURN -3;
783 END IF;
784 END IF;
785
786 log_debug ('Entering msd_dem_demantra_utilities.create_data_profile ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
787
788 log_debug ('Check if a data profile with the same name already exists or not');
789 x_sql := 'SELECT count(1) FROM ' || g_demantra_schema_name || '.TRANSFER_QUERY WHERE query_name = :1 ';
790 log_debug (x_sql);
791 EXECUTE IMMEDIATE x_sql
792 INTO x_return_value
793 USING p_query_name;
794
795 IF (x_return_value = 1)
796 THEN
797 log_message ('Error(1) in function msd_dem_demantra_utilities.create_data_profile ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
798 log_message ('Data Profile : ' || p_query_name || ' already exists.');
799 RETURN -4;
800 END IF;
801
802
803 log_debug ('Get the next sequence value for data profile id');
804 x_data_profile_id := get_sequence_nextval (
805 'TRANSFER_QUERY',
806 'ID',
807 'TRANSFER_QUERY_SEQ');
808 IF (x_data_profile_id < 0)
809 THEN
810 log_message ('Error(2) in function msd_dem_demantra_utilities.create_data_profile ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
811 log_message ('Unable to get next sequence value for data profile id');
812 RETURN -5;
813 END IF;
814 log_debug ('Next sequence value for id : ' || to_char(x_data_profile_id));
815
816
817 log_debug ('Insert a row for the data profile into the table TRANSFER_QUERY');
818 x_sql := 'INSERT INTO ' || g_demantra_schema_name || '.TRANSFER_QUERY ( '
819 || ' ID, TRANSFER_ID, PRESENTATION_TYPE, VIEW_NAME, TABLE_NAME, '
820 || ' FILE_NAME, DELIMITER, IS_FIXED_WIDTH, VIEW_TYPE, USE_REAL_PROPORTION, '
821 || ' INSERTNEWCOMBINATIONS, INSERTFORECASTHORIZON, QUERY_NAME, DESCRIPTION, TIME_RES_ID, '
822 || ' FROM_DATE, UNTIL_DATE, RELATIVE_DATE, RELATIVE_FROM_DATE, RELATIVE_UNTIL_DATE, '
823 || ' UNIT_ID, INDEX_ID, DATA_SCALE, DM_ID, SCHEMA_ID, '
824 || ' QUERY_ID, INTEGRATION_TYPE, EXPORT_TYPE, BATCH_FILE, IMPORT_FROM_FILE, '
825 || ' LAST_EXPORT_DATE, FILTER_SD_BY_GL ) '
826 || ' VALUES ( '
827 || ' :1, :2, 1, :3, :4, '
828 || ' null, null, null, :5, :6, '
829 || ' :7, :8, :9, :10, :11, '
830 || ' :12, :13, :14, :15, :16, '
831 || ' 1, null, 1, null, null, '
832 || ' null, :17, :18, null, 0, '
833 || ' null, 0 ) ';
834 log_debug (x_sql);
835 EXECUTE IMMEDIATE x_sql
836 USING x_data_profile_id, p_transfer_id, p_view_name, p_table_name,
837 p_view_type, p_use_real_proportion,
838 p_insertnewcombinations, p_insertforecasthorizon, p_query_name, p_description, p_time_res_id,
839 p_from_date, p_until_date, p_relative_date, p_relative_from_date, p_relative_until_date,
840 p_integration_type, p_export_type;
841
842
843 log_debug ('Insert a row for the data profile into the table TRANSFER_QUERY_INTERSECTIONS');
844 x_sql := 'INSERT INTO ' || g_demantra_schema_name || '.TRANSFER_QUERY_INTERSECTIONS ( '
845 || ' ID, BASE_LEVEL_ID, TYPE ) '
846 || ' VALUES ( '
847 || ' :1, :2, 1 ) ';
848 log_debug (x_sql);
849 EXECUTE IMMEDIATE x_sql USING x_data_profile_id, p_base_level_id;
850
851 COMMIT;
852 log_debug ('Data Profile created successfully.');
853
854 log_debug ('Exiting msd_dem_demantra_utilities.create_data_profile ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
855 RETURN x_data_profile_id;
856
857 EXCEPTION
858 WHEN OTHERS THEN
862
859 log_message ('Exception in function msd_dem_demantra_utilities.create_data_profile ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
860 log_message (substr(SQLERRM,1,150));
861 RETURN -1;
863 END CREATE_DATA_PROFILE;
864
865
866
867
868 /*
869 * This function adds the given series to the data profile.
870 * The function returns -
871 * 0 : in case of success
872 * -1 : in case of error
873 * -3 : Unable to set demantra schema name
874 */
875 FUNCTION ADD_SERIES_TO_PROFILE (
876 p_data_profile_id IN NUMBER,
877 p_series_id IN NUMBER )
878 RETURN NUMBER
879 IS
880
881 x_sql VARCHAR2(4000) := NULL;
882
883 BEGIN
884
885 IF (g_demantra_schema_name IS NULL)
886 THEN
887 IF (set_demantra_schema = -1)
888 THEN
889 RETURN -3;
890 END IF;
891 END IF;
892
893 log_debug ('Entering msd_dem_demantra_utilities.add_series_to_profile ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
894
895 log_debug ('Adding series : ' || to_char(p_series_id) || ' to the profile : ' || to_char(p_data_profile_id));
896 x_sql := 'INSERT INTO ' || g_demantra_schema_name || '.TRANSFER_QUERY_SERIES ( ID, SERIES_ID, LOAD_OPTION, PURGE_OPTION ) '
897 || ' VALUES ( :1, :2, 0, 0 ) ';
898 log_debug (x_sql);
899 EXECUTE IMMEDIATE x_sql USING p_data_profile_id, p_series_id;
900
901 COMMIT;
902 log_debug ('Series added successfully to the data profile');
903
904 log_debug ('Exiting msd_dem_demantra_utilities.add_series_to_profile ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
905 RETURN 0;
906
907 EXCEPTION
908 WHEN OTHERS THEN
909 log_message ('Exception in function msd_dem_demantra_utilities.add_series_to_profile ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
910 log_message (substr(SQLERRM,1,150));
911 RETURN -1;
912
913 END ADD_SERIES_TO_PROFILE;
914
915
916 /*
917 * This function adds the given level to the data profile.
918 * The function returns -
919 * 0 : in case of success
920 * -1 : in case of error
921 * -3 : Unable to set demantra schema name
922 */
923 FUNCTION ADD_LEVEL_TO_PROFILE (
924 p_data_profile_id IN NUMBER,
925 p_level_id IN NUMBER,
926 p_lorder IN NUMBER )
927 RETURN NUMBER
928 IS
929
930 x_sql VARCHAR2(4000) := NULL;
931
932 BEGIN
933
934 IF (g_demantra_schema_name IS NULL)
935 THEN
936 IF (set_demantra_schema = -1)
937 THEN
938 RETURN -3;
939 END IF;
940 END IF;
941
942 log_debug ('Entering msd_dem_demantra_utilities.add_level_to_profile ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
943
944 log_debug ('Adding level : ' || to_char(p_level_id) || ' to the profile : ' || to_char(p_data_profile_id));
945 x_sql := 'INSERT INTO ' || g_demantra_schema_name || '.TRANSFER_QUERY_LEVELS ( ID, LEVEL_ID, LORDER ) '
946 || ' VALUES ( :1, :2, :3 ) ';
947 log_debug (x_sql);
948 EXECUTE IMMEDIATE x_sql USING p_data_profile_id, p_level_id, p_lorder;
949
950 COMMIT;
951 log_debug ('Level added successfully to the data profile');
952
953 log_debug ('Exiting msd_dem_demantra_utilities.add_level_to_profile ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
954 RETURN 0;
955
956 EXCEPTION
957 WHEN OTHERS THEN
958 log_message ('Exception in function msd_dem_demantra_utilities.add_level_to_profile ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
959 log_message (substr(SQLERRM,1,150));
960 RETURN -1;
961
962 END ADD_LEVEL_TO_PROFILE;
963
964
965
966
967
968 /*
969 * This function creates the given workflow schema.
970 * The function returns -
971 * n : the schema id
972 * -1 : in case of error
973 * -3 : Unable to set demantra schema name
974 * -4 : If the given workflow schema name already exists
975 * -5 : Unable to get next sequence value for schema id
976 */
977 FUNCTION CREATE_WORKFLOW_SCHEMA (
978 p_schema_name IN VARCHAR2,
979 p_schema_data IN VARCHAR2,
980 p_owner_id IN NUMBER,
981 p_creation_date IN DATE,
982 p_modified_date IN DATE,
983 p_schema_type IN NUMBER )
984 RETURN NUMBER
985 IS
986
987 x_return_value NUMBER := NULL;
988 x_schema_id NUMBER := NULL;
989 x_sql VARCHAR2(4000) := NULL;
990
991 BEGIN
992
993 IF (g_demantra_schema_name IS NULL)
994 THEN
995 IF (set_demantra_schema = -1)
996 THEN
997 RETURN -3;
998 END IF;
999 END IF;
1000
1001 log_debug ('Entering msd_dem_demantra_utilities.create_workflow_schema ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1002
1003 log_debug ('First check if the workflow schema : ' || p_schema_name || ' already exists or not. ');
1004 x_sql := 'SELECT count(1) FROM ' || g_demantra_schema_name || '.WF_SCHEMAS WHERE schema_name = :1 ';
1005 log_debug (x_sql);
1006 EXECUTE IMMEDIATE x_sql
1007 INTO x_return_value
1008 USING p_schema_name;
1009
1010 IF (x_return_value <> 0)
1011 THEN
1012 log_message ('Error(1) in function msd_dem_demantra_utilities.create_workflow_schema ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1013 log_message ('Workflow Schema : ' || p_schema_name || ' already exists.');
1014 RETURN -4;
1015 END IF;
1016
1017
1018 log_debug ('Get the next sequence value for schema id');
1019 x_schema_id := get_sequence_nextval (
1020 'WF_SCHEMAS',
1021 'SCHEMA_ID',
1022 'WF_SCHEMA_ID_SEQ');
1023 IF (x_schema_id < 0)
1024 THEN
1025 log_message ('Error(2) in function msd_dem_demantra_utilities.create_workflow_schema ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1026 log_message ('Unable to get next sequence value for schema id');
1027 RETURN -5;
1028 END IF;
1029 log_debug ('Next sequence value for id : ' || to_char(x_schema_id));
1030
1031
1032 log_debug ('Insert a row for the workflow schema into the table WF_SCHEMAS');
1033 x_sql := 'INSERT INTO ' || g_demantra_schema_name || '.WF_SCHEMAS ( '
1034 || ' SCHEMA_ID, SCHEMA_NAME, SCHEMA_DATA, STATUS, OWNER_ID, '
1035 || ' CREATION_DATE, MODIFIED_DATE, TEMPORARY, SCHEMA_TYPE ) '
1036 || ' VALUES ( '
1037 || ' :1, :2, :3, 1, :4, '
1038 || ' :5, :6, 0, :7 ) ';
1039 log_debug (x_sql);
1040 EXECUTE IMMEDIATE x_sql
1041 USING x_schema_id, p_schema_name, p_schema_data, p_owner_id,
1042 p_creation_date, p_modified_date, p_schema_type;
1043
1044 COMMIT;
1045 log_debug ('Workflow Schema created successfully');
1046
1047 log_debug ('Exiting msd_dem_demantra_utilities.create_workflow_schema ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1048 RETURN x_schema_id;
1049
1050 EXCEPTION
1051 WHEN OTHERS THEN
1052 log_message ('Exception in function msd_dem_demantra_utilities.create_workflow_schema ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1053 log_message (substr(SQLERRM,1,150));
1054 RETURN -1;
1055
1056 END CREATE_WORKFLOW_SCHEMA;
1057
1058
1059
1060
1061 /*
1062 * This function deletes the workflow schema given.
1063 * The function returns -
1064 * 0 : in case of success
1065 * -1 : in case of error
1066 * -3 : Unable to set demantra schema name
1067 */
1068 FUNCTION DELETE_WORKFLOW_SCHEMA ( p_schema_name IN VARCHAR2 )
1069 RETURN NUMBER
1070 IS
1071
1072 x_sql VARCHAR2(4000) := NULL;
1073
1074 BEGIN
1075
1076 IF (g_demantra_schema_name IS NULL)
1077 THEN
1078 IF (set_demantra_schema = -1)
1079 THEN
1080 RETURN -3;
1081 END IF;
1082 END IF;
1083
1084 log_debug ('Entering msd_dem_demantra_utilities.delete_workflow_schema ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1085
1086 log_debug ('Deleting workflow schema : ' || p_schema_name);
1087 x_sql := 'DELETE FROM ' || g_demantra_schema_name || '.WF_SCHEMAS WHERE schema_name = :1 ';
1088 log_debug (x_sql);
1089 EXECUTE IMMEDIATE x_sql USING p_schema_name;
1090
1091 COMMIT;
1092 log_debug ('Workflow Schema deleted successfully.');
1093
1094 log_debug ('Exiting msd_dem_demantra_utilities.delete_workflow_schema ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1095 RETURN 0;
1096
1097 EXCEPTION
1098 WHEN OTHERS THEN
1099 log_message ('Exception in function msd_dem_demantra_utilities.delete_workflow_schema ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1100 log_message (substr(SQLERRM,1,150));
1101 RETURN -1;
1102
1103 END DELETE_WORKFLOW_SCHEMA;
1104
1105
1106
1107
1108 /*
1109 * This function gets the demantra schema name.
1110 * <demantra schema name> : if demantra is installed.
1111 * null : if demantra is not installed.
1112 */
1113 FUNCTION GET_DEMANTRA_SCHEMA
1114 RETURN VARCHAR2
1115 IS
1116 BEGIN
1117
1118 IF (g_demantra_schema_name IS NULL)
1119 THEN
1120 IF (set_demantra_schema = -1)
1121 THEN
1122 RETURN null;
1123 END IF;
1124 END IF;
1125
1126 log_debug ('Exiting msd_dem_demantra_utilities.get_demantra_schema ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1127 RETURN g_demantra_schema_name;
1128
1129 EXCEPTION
1130 WHEN OTHERS THEN
1131 log_message ('Exception in function msd_dem_demantra_utilities.get_demantra_schema ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1132 log_message (substr(SQLERRM,1,150));
1133 RETURN null;
1134
1138
1135 END GET_DEMANTRA_SCHEMA;
1136
1137
1139
1140 /*
1141 * This function creates the given db object.
1142 * The function returns -
1143 * 0 : in case of success
1144 * -1 : in case of error
1145 * -3 : Unable to set demantra schema name
1146 */
1147 FUNCTION CREATE_DEMANTRA_DB_OBJECT (
1148 p_object_type IN VARCHAR2,
1149 p_object_name IN VARCHAR2,
1150 p_create_sql IN VARCHAR2)
1151 RETURN NUMBER
1152 IS
1153 BEGIN
1154
1155 IF (g_demantra_schema_name IS NULL)
1156 THEN
1157 IF (set_demantra_schema = -1)
1158 THEN
1159 RETURN -3;
1160 END IF;
1161 END IF;
1162
1163 log_debug ('Entering msd_dem_demantra_utilities.create_demantra_db_object ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1164
1165 log_debug ('Demantra object : ' || p_object_name);
1166 log_debug ('Object Type : ' || p_object_type);
1167
1168 log_debug ('Creating DB object.');
1169 log_debug (p_create_sql);
1170 EXECUTE IMMEDIATE p_create_sql;
1171
1172 log_debug ('DB Object created successfully.');
1173
1174 log_debug ('Exiting msd_dem_demantra_utilities.create_demantra_db_object ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1175 RETURN 0;
1176
1177 EXCEPTION
1178 WHEN OTHERS THEN
1179 log_message ('Exception in function msd_dem_demantra_utilities.create_demantra_db_object ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1180 log_message (substr(SQLERRM,1,150));
1181 RETURN -1;
1182
1183 END CREATE_DEMANTRA_DB_OBJECT;
1184
1185
1186
1187
1188 /*
1189 * This function drop the given demantra db object.
1190 * The function returns -
1191 * 0 : in case of success/object not present
1192 * -1 : in case of error
1193 * -3 : Unable to set demantra schema name
1194 */
1195 FUNCTION DROP_DEMANTRA_DB_OBJECT (
1196 p_object_type IN VARCHAR2,
1197 p_object_name IN VARCHAR2 )
1198 RETURN NUMBER
1199 IS
1200
1201 x_return_value NUMBER := NULL;
1202 x_sql VARCHAR2(4000) := NULL;
1203
1204 BEGIN
1205
1206 IF (g_demantra_schema_name IS NULL)
1207 THEN
1208 IF (set_demantra_schema = -1)
1209 THEN
1210 RETURN -3;
1211 END IF;
1212 END IF;
1213
1214 log_debug ('Entering msd_dem_demantra_utilities.drop_demantra_db_object ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1215
1216 log_debug ('Demantra object : ' || p_object_name);
1217 log_debug ('Object Type : ' || p_object_type);
1218
1219 log_debug ('First check if the object already exists or not');
1220 x_sql := 'SELECT 1 FROM all_objects WHERE owner = :1 AND object_type = :2 AND object_name = :3';
1221 log_debug (x_sql);
1222 EXECUTE IMMEDIATE x_sql
1223 INTO x_return_value
1224 USING g_demantra_schema_name, p_object_type, p_object_name;
1225
1226 log_debug ('Dropping Demantra DB object');
1227 x_sql := ' DROP ' || p_object_type || ' ' || g_demantra_schema_name || '.' || p_object_name;
1228 log_debug (x_sql);
1229 EXECUTE IMMEDIATE x_sql;
1230
1231 log_debug ('Demantra Object dropped successfully');
1232
1233 log_debug ('Exiting msd_dem_demantra_utilities.drop_demantra_db_object ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1234 RETURN 0;
1235
1236 EXCEPTION
1237
1238 WHEN NO_DATA_FOUND THEN
1239 log_message ('Exception(1) in function msd_dem_demantra_utilities.drop_demantra_db_object ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1240 log_message (substr(SQLERRM,1,150));
1241 log_message ('Object to be dropped not found. Ignore this error.');
1242 RETURN 0;
1243
1244 WHEN OTHERS THEN
1245 log_message ('Exception(2) in function msd_dem_demantra_utilities.drop_demantra_db_object ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1246 log_message (substr(SQLERRM,1,150));
1247 RETURN -1;
1248
1249 END DROP_DEMANTRA_DB_OBJECT;
1250
1251
1252
1253
1254 /*
1255 * This function work only if Demantra and APS are on the same DB instance.
1256 * This function creates a synonym in the APPS schema using the given sql
1257 * script.
1258 * The function returns -
1259 * 0 : in case of success
1260 * -1 : in case of error
1261 * -3 : Unable to set demantra schema name
1262 */
1263 FUNCTION CREATE_SYNONYM_IN_EBS (
1264 p_object_name IN VARCHAR2,
1265 p_create_replace_sql IN VARCHAR2)
1266 RETURN NUMBER
1267 IS
1268 BEGIN
1269
1270 IF (g_demantra_schema_name IS NULL)
1271 THEN
1272 IF (set_demantra_schema = -1)
1273 THEN
1274 RETURN -3;
1275 END IF;
1276 END IF;
1277
1278 log_debug ('Entering msd_dem_demantra_utilities.create_synonym_in_ebs ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1279
1280 log_debug ('Object Name : ' || p_object_name);
1281
1282 /* First check if Demantra and APS are on the same DB instance */
1283 IF (g_is_demantra_schema)
1284 THEN
1285 log_message ('Note in function msd_dem_demantra_utilities.create_synonym_in_ebs ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1286 log_message ('Synonym ' || p_object_name || 'not created. Create the synonym manually.');
1287 RETURN 0;
1288 END IF;
1289
1290 log_debug ('Creating Synonym.');
1291 log_debug (p_create_replace_sql);
1292 EXECUTE IMMEDIATE p_create_replace_sql;
1293
1294 log_debug ('Synonym created successfully.');
1295
1296 log_debug ('Exiting msd_dem_demantra_utilities.create_synonym_in_ebs ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1297 RETURN 0;
1298
1299 EXCEPTION
1300 WHEN OTHERS THEN
1301 log_message ('Exception in function msd_dem_demantra_utilities.create_synonym_in_ebs ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1302 log_message (substr(SQLERRM,1,150));
1303 RETURN -1;
1304
1305 END CREATE_SYNONYM_IN_EBS;
1306
1307
1308
1309
1310 /*** PUBLIC FUNCTIONS - END ***/
1311
1312
1313
1314
1315
1316 END MSD_DEM_DEMANTRA_UTILITIES;