1 Package MTH_UTIL_PKG AUTHID CURRENT_USER AS
2 /*$Header: mthutils.pls 120.20.12020000.3 2012/10/10 06:24:11 aksachde ship $ */
3
4 --Added for ODI
5 G_PROCESSING_FLAG number;
6 g_mth_debug number;
7 g_execution_id number;
8 g_debug_file_dir varchar2(500);
9 g_debug_indent_level number;
10 G_DBG_USER_ERROR CONSTANT NUMBER := 0;
11 G_DBG_USER_WARNING CONSTANT NUMBER := 0;
12 G_DBG_USER_INFO CONSTANT NUMBER := 0;
13 G_DBG_PROC_FUN_START CONSTANT NUMBER := 0.8;
14 G_DBG_PROC_FUN_END CONSTANT NUMBER := 0.9;
15 G_DBG_EXCEPTION CONSTANT NUMBER := 1;
16 G_DBG_MAIN_PARAM CONSTANT NUMBER := 1;
17 G_DBG_PARAM_VAL CONSTANT NUMBER := 2;
18 G_DBG_VAR_VAL CONSTANT NUMBER := 3;
19 G_DBG_DYN_SQL CONSTANT NUMBER := 3;
20 G_DBG_ROW_CNT CONSTANT NUMBER := 3;
21 G_DBG_OTH CONSTANT NUMBER := 4;
22
23 /* ****************************************************************************
24 * Procedure :MTH_RUN_LOG_PRE_LOAD *
25 * Description :This procedure is used for the population of the *
26 * mth_run_log table for the initial and incremental load. The procedure is *
27 * called at the begenning of the mapping execution sequence to set the *
28 * boundary conditions for the ebs collection for the corresponding fact *
29 * or dimension. *
30 * File Name :MTHUTILS.PLS *
31 * Visibility :Public *
32 * Parameters : *
33 * Modification log : *
34 * Author Date Change *
35 * Ankit Goyal 31-May-2007 Initial Creation *
36 **************************************************************************** */
37
38 PROCEDURE mth_run_log_pre_load(p_fact_table IN VARCHAR2,p_db_global_name
39 IN VARCHAR2,p_run_mode IN VARCHAR2,p_run_start_date IN DATE, p_is_fact IN NUMBER
40 ,p_to_date IN DATE);
41
42
43 /* ****************************************************************************
44 * Procedure :MTH_RUN_LOG_POST_LOAD *
45 * Description :This procedure is used for the population of the *
46 * mth_run_log table for the initial and incremental load. The procedure is *
47 * called at the end of the mapping execution sequence to set the *
48 * boundary conditions for the ebs collection for the corresponding fact *
49 * File Name :MTHUTILS.PLS *
50 * Visibility :Public *
51 * Parameters : *
52 * Modification log : *
53 * Author Date Change *
54 * Ankit Goyal 31-May-2007 Initial Creation *
55 **************************************************************************** */
56 PROCEDURE mth_run_log_post_load(p_fact_table IN VARCHAR2,
57 p_db_global_name IN VARCHAR2);
58
59 /* ****************************************************************************
60 * Procedure :MTH_hrchy_BALANCE_LOAD *
61 * Description :This procedure is used for the balancing of the *
62 * hierarchy. The algorithm used for the balancing is down balancing *
63 * Please refer to the Item fdd for more details on this. *
64 * File Name :MTHUTILS.PLS *
65 * Visibility :Public *
66 * Parameters :fact table name *
67 * Modification log : *
68 * Author Date Change *
69 * Ankit Goyal 17-Aug-2007 Initial Creation *
70 **************************************************************************** */
71 PROCEDURE mth_hrchy_balance_load(p_fact_table IN VARCHAR2);
72
73 /* ****************************************************************************
74 * Procedure :MTH_TRUNCATE_TABLE *
75 * Description :This procedure is used to truncate the table in the *
76 * MTH Schema. Thsi can be overriden by spefying a custom schema name as well. *
77 * File Name :MTHUTILS.PLS *
78 * Visibility :Public *
79 * Parameters :Table name *
80 * Modification log : *
81 * Author Date Change *
82 * Ankit Goyal 11-Oct-2007 Initial Creation *
83 **************************************************************************** */
84
85 PROCEDURE mth_truncate_table(p_table_name IN VARCHAR2);
86
87 /* ****************************************************************************
88 * Procedure :MTH_TRUNCATE_TABLE *
89 * Description :This procedure is used to truncate the table in the *
90 * specified schema. *
91 * File Name :MTHUTILS.PLS *
92 * Visibility :Public *
93 * Parameters :Table name , schema name *
94 * Modification log : *
95 * Author Date Change *
96 * Yong Feng July-18-2008 Initial Creation *
97 **************************************************************************** */
98
99 PROCEDURE mth_truncate_table(p_table_name IN VARCHAR2,
100 p_schema_name IN VARCHAR2);
101
102 /* ****************************************************************************
103 * Procedure :MTH_TRUNCATE_TABLES *
104 * Description :This procedure is used to truncate the tables in the *
105 * list separated by comma. *
106 * File Name :MTHUTILS.PLS *
107 * Visibility :Public *
108 * Parameters :p_list_table_names: List of table names separated *
109 * by commas. *
110 * Modification log : *
111 * Author Date Change *
112 * Yong Feng Aug-07-2008 Initial Creation *
113 **************************************************************************** */
114
115 PROCEDURE mth_truncate_tables(p_list_table_names IN VARCHAR2);
116
117 /* ****************************************************************************
118 * Procedure :MTH_TRUNCATE_MV_LOGS *
119 * Description :This procedure is used to truncate the Materialized *
120 * View log created on the tables *
121 * list separated by comma. *
122 * File Name :MTHUTILS.PLS *
123 * Visibility :Public *
124 * Parameters :p_list_table_names: List of table names separated *
125 * by commas. *
126 * Modification log : *
127 * Author Date Change *
128 * Yong Feng Aug-07-2008 Initial Creation *
129 **************************************************************************** */
130
131 PROCEDURE MTH_TRUNCATE_MV_LOGS (p_list_table_names IN VARCHAR2);
132
133 /* ****************************************************************************
134 * Function :MTH_UA_GET_VAL *
135 * Description : This procedure is used to return the lookup code for*
136 * the unasssigned *
137 * File Name :MTHUTILS.PLS *
138 * Visibility :Public *
139 * Parameters :Table name *
140 * Modification log : *
141 * Author Date Change *
142 * Ankit Goyal 11-Oct-2007 Initial Creation *
143 **************************************************************************** */
144
145 Function mth_ua_get_val RETURN NUMBER;
146
147 /* ****************************************************************************
148 * Function :MTH_UA_GET_MEANING *
149 * Description :This procedure is used to return the lookup meaning *
150 * for the unasssigned *
151 * File Name :MTHUTILS.PLS *
152 * Visibility :Public *
153 * Parameters :Table name *
154 * Modification log : *
155 * Author Date Change *
156 * Ankit Goyal 23-Oct-2007 Initial Creation *
157 **************************************************************************** */
158
159 Function mth_ua_get_meaning RETURN Varchar2;
160 /* ****************************************************************************
161 * Procedure :request_lock *
162 * Description :This procedure is used to request an exclusive *
163 * lock with p_key_table as the key using rdbms_lock package. The current *
164 * will wait indifinitely if the lock was held by others until the release *
165 * of the lock. *
166 * File Name :MTHUTILB.PLS *
167 * Visibility :Private *
168 * Parameters *
169 * p_key_table : The name used to request an exclusive lock. *
170 * p_retval : The return value of the operation: *
171 * 0 - Success *
172 * 1 - Timeout *
173 * 2 - Deadlock *
174 * 3 - Parameter Error *
175 * 4 - Already owned *
176 * 5 - Illegal Lock Handle *
177 * Modification log : *
178 * Author Date Change *
179 * Yong Feng 17-Oct-2007 Initial Creation *
180 **************************************************************************** */
181
182 PROCEDURE request_lock(p_key_table IN VARCHAR2, p_retval OUT NOCOPY INTEGER);
183
184 /* ****************************************************************************
185 * Procedure :generate_new_time_range *
186 * Description :This procedure is used to generate a time range *
187 * starting from the last end date up to current time, sysdate, using *
188 * the p_key_table name as the key to look up the entry in MTH_RUN_LOG *
189 * table. If the entry does not exist, create one and set the time range *
190 * to a hard-coded past time to current time. *
191 * File Name :MTHUTILB.PLS *
192 * Visibility :Public *
193 * Parameters *
194 * p_key_table : Name to uniquely identify one entry in the *
195 * mth_run_log table. *
196 * p_start_date : An output value that specifies the start time *
197 * of the new time period. *
198 * p_end_date : An output value that specifies the end time *
199 * of the new time period. *
200 * p_exclusive_lock : Specify whether it needs to request an exclusive *
201 * lock using p_key_table as the key so that only *
202 * one procedure will be running at one point of time. *
203 * If the value is 1, then it will run in exclusive *
204 * mode. The lock will be released when the *
205 * transaction is either committed or rollbacked. *
206 * Modification log : *
207 * Author Date Change *
208 * Yong Feng 17-Oct-2007 Initial Creation *
209 **************************************************************************** */
210
211 PROCEDURE generate_new_time_range(p_key_table IN VARCHAR2,
212 p_start_date OUT NOCOPY DATE,
213 p_end_date OUT NOCOPY DATE,
214 p_exclusive_lock IN NUMBER DEFAULT 1);
215
216
217
218 /* ****************************************************************************
219 * Function :GET_PROFILE_VAL *
220 * Description :This function is used to retrive the value of the *
221 * profile for the profile name provided by the user *
222 * File Name :MTHSOURCEPATCHS.PLS *
223 * Visibility :Public *
224 * Return : V_PROFILE_NAME - Global name of the source DB *
225 * Modification log : *
226 * Author Date Change *
227 * Ankit Goyal 29-Oct-2007 Initial Creation *
228 ******************************************************************************/
229 FUNCTION get_profile_val(p_profile_name IN VARCHAR2) RETURN VARCHAR2;
230
231 /* *****************************************************************************
232 * Function :CHECK_REFERENCE *
233 * Description :This function is used to retrive the value of the *
234 * count of rows from mv *
235 * File Name :MTHUTILS.PLS *
236 * Visibility :Public *
237 * Return : v_count Value *
238 * Modification log : *
239 * Author Date Change *
240 * Mandar Gijare 20-Dec-2010 Initial Creation *
241 ******************************************************************************/
242 FUNCTION check_reference RETURN NUMBER;
243
244 /* *****************************************************************************
245 * Function :FIND_METERS *
246 * Description :This function is used to retrive the value of the *
247 * meters from the virtual meter formula *
248 * File Name :MTHUTILS.PLS *
249 * Visibility :Public *
250 * Return : v_str Value *
251 * Modification log : *
252 * Author Date Change *
253 * Mandar Gijare 31-May-2011 Initial Creation *
254 ******************************************************************************/
255 FUNCTION find_meters(p_meter_formula IN VARCHAR2) RETURN VARCHAR2;
256
257 /* ****************************************************************************
258 * Function :Get_UDA_Eq_HId *
259 * Description :This function is used to retrive the hierarchy id of *
260 * the UDA Equipment profile *
261 * File Name :MTHUTILS.PLS *
262 * Visibility :Public *
263 * Return :Hierarchy id for the equipment UDA profile *
264 * Modification log : *
265 * Author Date Change *
266 * Vivek 18-Jan-2008 Initial Creation *
267 ******************************************************************************/
268 FUNCTION Get_UDA_Eq_HId RETURN VARCHAR;
269
270 /* ****************************************************************************
271 * Function :Get_UDA_Eq_LNo *
272 * Description :This function is used to retrive the Level Number of *
273 * the UDA Equipment profile *
274 * File Name :MTHUTILS.PLS *
275 * Visibility :Public *
276 * Return :Level Number for the equipment UDA profile *
277 * Modification log : *
278 * Author Date Change *
279 * Vivek 18-Jan-2008 Initial Creation *
280 ******************************************************************************/
281
282 FUNCTION Get_UDA_Eq_LNo RETURN VARCHAR;
283
284 /* ****************************************************************************
285 * Procedure :REFRESH_MV *
286 * Description :This procedure is used to call DBMS_MVIEW.REFRESH *
287 * procedure to refresh MVs. *
288 * File Name :MTHUTILB.PLS *
289 * Visibility :Public *
290 * Parameters *
294 * refresh the listed materialized views. *
291 * p_list : Comma-separated list of materialized views that *
292 * you want to refresh. *
293 * p_method :A string of refresh methods indicating how to *
295 * - An f indicates fast refresh *
296 * - ? indicates force refresh *
297 * - C or c indicates complete refresh *
298 * - A or a indicates always refresh. A and C are *
299 * equivalent. *
300 * p_rollback_seg :Name of the materialized view site rollback segment *
301 * to use while refreshing materialized views. *
302 * p_push_deferred_rpc : Used by updatable materialized views only. *
303 * p_refresh_after_errors: *
304 * p_purge_option : *
305 * p_parallelism : 0 specifies serial propagation *
306 * p_heap_size : *
307 * p_atomic_refresh : *
308 * Modification log : *
309 * Author Date Change *
310 * Yong Feng 11-July-2008 Initial Creation *
311 **************************************************************************** */
312
313 PROCEDURE REFRESH_MV(
314 p_list IN VARCHAR2,
315 p_method IN VARCHAR2 := NULL,
316 p_rollback_seg IN VARCHAR2 := NULL,
317 p_push_deferred_rpc IN BOOLEAN := true,
318 p_refresh_after_errors IN BOOLEAN := false,
319 p_purge_option IN BINARY_INTEGER := 1,
320 p_parallelism IN BINARY_INTEGER := 0,
321 p_heap_size IN BINARY_INTEGER := 0,
322 p_atomic_refresh IN BOOLEAN := true
323 );
324
325 /* ****************************************************************************
326 * Procedure :REFRESH_MV_ONE_ALL *
327 * Description :This procedure is used to call DBMS_MVIEW.REFRESH *
328 * procedure to refresh MVs. *
329 * File Name :MTHUTILB.PLS *
330 * Visibility :Public *
331 * Parameters *
332 * list : Comma-separated list of materialized views that *
333 * you want to refresh. *
334 * *
335 * Modification log : *
336 * Author Date Change *
337 * Mandar Gijare 24-May-2012 Initial Creation *
338 **************************************************************************** */
339 PROCEDURE REFRESH_MV_ONE_ALL(
340 p_mview_name IN VARCHAR2
341 );
342
343 /* ****************************************************************************
344 * Procedure :REFRESH_ONE_MV *
345 * Description :This procedure is used to call refresh one MV. *
346 * File Name :MTHUTILB.PLS *
347 * Visibility :Public *
348 * Parameters *
349 * p_mv_name : Name of the materialized view to be refreshed. *
350 * p_method :A string of refresh methods indicating how to *
351 * refresh the listed materialized views. *
352 * - An f indicates fast refresh *
353 * - ? indicates force refresh *
354 * - C or c indicates complete refresh *
355 * - A or a indicates always refresh. A and C are *
356 * equivalent. *
357 * p_rollback_seg :Name of the materialized view site rollback segment *
358 * to use while refreshing materialized views. *
359 * p_refresh_mode :A string of refresh mode: *
360 * - C , c or NULL indicates complete refresh. *
361 * - R or r indicates resume refresh that has been *
362 * started earlier. The MV will be refreshed if the *
363 * refresh date is earlier than the date stored in *
364 * to_date column in MTH_RUN_LOG for MTH_ALL_MVS entry. *
365 * p_push_deferred_rpc : Used by updatable materialized views only. *
366 * Modification log : *
367 * Author Date Change *
368 * Yong Feng 19-Aug-2008 Initial Creation *
369 **************************************************************************** */
370
371 PROCEDURE REFRESH_ONE_MV(
372 p_mv_name IN VARCHAR2,
373 p_method IN VARCHAR2 := NULL,
374 p_rollback_seg IN VARCHAR2 := NULL,
375 p_refresh_mode IN VARCHAR2 := NULL
376 );
377
378 /* *****************************************************************************
379 * Procedure :PUT_EQUIP_DENORM_LEVEL_NUM *
380 * Description :This procedure is used to insert the level_num column *
381 * in the mth_equipment_denorm_d table *
382 * File Name :MTHUTILS.PLS *
383 * Visibility :Private *
384 * Modification log : *
388
385 * Author Date Change *
386 * shanthi donthu 16-Jul-2008 Initial Creation *
387 ***************************************************************************** */
389 PROCEDURE PUT_EQUIP_DENORM_LEVEL_NUM;
390
391 /* *****************************************************************************
392 * Procedure :update_equip_hrchy_gid *
393 * Description :This procedue is used for updating the group_id column in *
394 * the mth_equip_hierarchy table. The group id will be used to determine the *
395 * sequence in which a particular record will be processed in the equipment SCD *
396 * logic. The oldest relationships will have the lowest group id =1 and the new *
397 * relationships will have higher group id. All the catch all relationships i.e.*
398 * the relationship with parent = -99999 and effective date = 1-Jan-1900 will *
399 * have group id defaulted to 1 inside the MTH_EQUIP_HRCHY_UA_ALL_MAP map. *
400 * File Name :MTHUTILB.PLS *
401 * Visibility :Public *
402 * Parameters : none *
403 * Modification log : *
404 * Author Date Change *
405 * Ankit Goyal 26-Aug-2008 Initial Creation *
406 ***************************************************************************** */
407
408 PROCEDURE update_equip_hrchy_gid;
409
410 /* *****************************************************************************
411 * Function :get_min_max_gid *
412 * Description :This finction returns the minimum or maximum group id in the *
413 * Equipment hierarchy table. *
414 * File Name :MTHUTILB.PLS *
415 * Visibility :Public *
416 * Parameters : minmax Number. minmax= 1 Minimum, minmax =2 Maximum *
417 * Modification log : *
418 * Author Date Change *
419 * Ankit Goyal 26-Aug-2008 Initial Creation *
420 ***************************************************************************** */
421
422 FUNCTION get_min_max_gid(minmax IN NUMBER) RETURN NUMBER;
423
424 /* *****************************************************************************
425 * Procedure :switch_column_default_value *
426 * Description :This procedure will determine the current value of the *
427 * processing_flag of the table, issue an alter table statement to switch *
428 * the default values to another (1 to 2, or 2 to 1,) and return the *
429 * current value. If there are no data in the table, do nothing and return *
430 * 0. *
431 * File Name :MTHUTILB.PLS *
432 * Visibility :Public *
433 * Parameters : *
434 * p_table_name: table name *
435 * p_current_processing_flag: the current value of processing_flag *
436 * It could be 1, or 2 for normal case. *
437 * If it is 0, then no data is available *
438 * the table. So no process is needed. *
439 * Modification log : *
440 * Author Date Change: Yong Feng 10/2/08 Initial creation *
441 ***************************************************************************** */
442
443 PROCEDURE switch_column_default_value (p_table_name IN VARCHAR2,
444 p_current_processing_flag OUT NOCOPY NUMBER);
445
446 /* *****************************************************************************
447 * Procedure :truncate_table_partition *
448 * Description :This procedure will truncate the partition corresponding *
449 * to the value of p_current_processing_flag. *
450 * File Name :MTHUTILB.PLS *
451 * Visibility :Public *
452 * Parameters : *
453 * p_table_name: table name *
454 * p_current_processing_flag: Used to determine the partition to be *
455 * truncated. Truncate p1 if the value is 1; truncate p2 if 2. *
456 * Modification log : *
457 * Author Date Change: Yong Feng 10/2/08 Initial creation *
458 ***************************************************************************** */
459
460 PROCEDURE truncate_table_partition (p_table_name IN VARCHAR2,
461 p_current_processing_flag IN NUMBER);
462
463 /* *****************************************************************************
464 * Procedure :mth_run_log_pre_load *
465 * Description :This procedure will log entries when a map is run taking *
466 * transaction id and populating from_txn_id and to_txn_id *
467 * File Name :MTHUTILB.PLS *
468 * Visibility :Public *
469 * Modification log : *
470 * Author Date Change: Vivek Sharma 21-Jan-2009 Initial creation *
474 p_run_mode IN VARCHAR2,
471 ***************************************************************************** */
472
473 PROCEDURE mth_run_log_pre_load(p_fact_table IN VARCHAR2,p_db_global_name IN VARCHAR2,
475 p_run_start_date IN DATE, p_is_fact IN NUMBER
476 ,p_to_date IN DATE, p_to_txn_id IN NUMBER);
477
478
479 /* ****************************************************************************
480 * Function :GET_ATTR_EXT_COLUMN *
481 * Description :This function is used to retrive column name in *
482 * MTH_EQUIPMENTS_EXT_B that stores the value of an given *
483 * attribute name and attribute-group name. *
484 * File Name :MTHUTILB.PLS *
485 * Visibility :Public
486 * Parameters : *
487 * p_attr_name: Attribute name *
488 * p_att_grp_name: Attribute group name *
489 * Return : COLUMN_NAME - Column name in MTH_EQUIPMENTS_EXT_B that *
490 * stores the value of an given attribute name. *
491 * Modification log : *
492 * Author Date Change: Yong Feng 14-Jul-2009 Initial Creation *
493 ******************************************************************************/
494 FUNCTION GET_ATTR_EXT_COLUMN(p_attr_name IN VARCHAR2,
495 p_att_grp_name IN VARCHAR2 DEFAULT 'SPECIFICATIONS'
496 ) RETURN VARCHAR2;
497
498 /* ****************************************************************************
499 * Function :GET_ATTR_GROUP_ID *
500 * Description :This function is used to retrive attribute group ID *
501 * from EGO_ATTR_GROUPS_V for a given attribute group name. *
502 * File Name :MTHUTILB.PLS *
503 * Visibility :Public
504 * Parameters : *
505 * p_att_grp_name: Attribute group name *
506 * Return : Attribute group id for the specified attribute group name *
507 * Modification log : *
508 * Author Date Change: Yong Feng 26-Aug-2009 Initial Creation *
509 ******************************************************************************/
510 FUNCTION GET_ATTR_GROUP_ID(p_att_grp_name IN VARCHAR2 DEFAULT 'SPECIFICATIONS'
511 ) RETURN NUMBER;
512 /* ****************************************************************************
513 * Procedure :GET_UPPER_LOWER_LIMITS *
514 * Description Find and return the UPPER and LOWER limit for the *
515 * equipment specified. *
516 * File Name :MTHUTILB.PLS *
517 * Visibility :Public
518 * Parameters : *
519 * p_equipment_fk_key: Equipment fk key *
520 * p_attr_name: Attribute name *
521 * p_att_grp_name: attribute group name *
522 * p_low_lim_name: attribute name in EGO_ATTRS_V *
523 * p_upp_lim_name: another attribute name in EGO_ATTRS_V *
524 * p_ret_LOWER_LIMIT : Lower limit returned *
525 * p_ret_UPPER_LIMIT : Upper limit returned *
526 * Modification log : *
527 * Author Date Change: Yong Feng 14-Jul-2009 Initial Creation *
528 ******************************************************************************/
529 PROCEDURE GET_UPPER_LOWER_LIMITS(p_equipment_fk_key IN NUMBER,
530 p_attr_name in VARCHAR2,
531 p_att_grp_name IN VARCHAR2
532 DEFAULT 'SPECIFICATIONS',
533 p_low_lim_name IN VARCHAR2
534 DEFAULT 'LLIMIT',
535 p_upp_lim_name IN VARCHAR2
536 DEFAULT 'ULIMIT',
537 p_ret_LOWER_LIMIT OUT NOCOPY NUMBER,
538 p_ret_UPPER_LIMIT OUT NOCOPY NUMBER);
539
540
541 /* ****************************************************************************
542 * Function :GET_PREV_TAG_READING *
543 * Description :This function is used to retrive the previous reading *
544 * from mth_tag_readings_stg, mth_tag_readings, *
545 and mth_tag_readings_err *
546 * for the given tag_code and reading time is earlier than *
547 * the reading time specified and within the range specified *
548 * by the range_in_hour *
549 * File Name :MTHUTILB.PLS *
550 * Visibility :Public
551 * Parameters : *
552 * p_tag_code: TAG code name *
553 * p_reading_time: current reading_time *
554 * p_range_in_hours: Number of hours, which is used to limit *
555 * the search of the prevous reading to the range that is earlier *
556 * than the reading_time and later than *
557 * reading_time + p_range_in_hours / 24. *
558 * Return : Previous tag reading for the same tag *
559 * Modification log : *
560 * Author Date Change: Yong Feng 14-Jul-2009 Initial Creation *
561 ******************************************************************************/
562 FUNCTION GET_PREV_TAG_READING(p_tag_code IN VARCHAR2,
563 p_reading_time IN DATE,
564 p_range_in_hours IN NUMBER DEFAULT NULL)
565 RETURN VARCHAR2;
566
567
568 /* ****************************************************************************
569 * Procedure :GET_PREV_TAG_READING_INFO *
570 * Description :This function is used to retrive the previous reading *
571 * from mth_tag_readings_stg, mth_tag_readings, *
575 * by the range_in_hour *
572 and mth_tag_readings_err *
573 * for the given tag_code and reading time is earlier than *
574 * the reading time specified and within the range specified *
576 * File Name :MTHUTILB.PLS *
577 * Visibility :Public
578 * Parameters : *
579 * p_tag_code: TAG code name *
580 * p_reading_time: current reading_time *
581 * p_range_in_hours: Number of hours, which is used to limit *
582 * the search of the prevous reading to the range that is earlier *
583 * than the reading_time and later than *
584 * reading_time + p_range_in_hours / 24. *
585 * p_pre_tag_data: Previous tag reading for the same tag code *
586 * p_pre_reading_time: reading time for the previous tag reading *
587 * p_pre_eqp_availability: The availability_flag in the
588 * mth_equipment_shifts_d table *
589 * Y - available *
590 * N - not available *
591 * NULL - no schedule available *
592 * Modification log : *
593 * Author Date Change: Yong Feng 14-Jul-2009 Initial Creation *
594 ******************************************************************************/
595 PROCEDURE GET_PREV_TAG_READING_INFO(p_tag_code IN VARCHAR2,
596 p_reading_time IN DATE,
597 p_range_in_hours IN NUMBER DEFAULT NULL,
598 p_pre_tag_data OUT NOCOPY VARCHAR2,
599 p_pre_reading_time OUT NOCOPY DATE,
600 p_pre_eqp_availability OUT NOCOPY VARCHAR2);
601
602
603 /* ****************************************************************************
604 * Procedure :GET_PREV_TAG_READING_SET *
605 * Description :This function is used to retrive the previous reading set *
606 * from mth_tag_readings_stg, mth_tag_readings, *
607 and mth_tag_readings_err *
608 * for the given tag_codes and reading time is earlier than *
609 * the reading time specified and within the range specified *
610 * by the range_in_hour. The reading set bounded by the same *
611 * group id contains both tags *
612 * File Name :MTHUTILB.PLS *
613 * Visibility :Public
614 * Parameters : *
615 * p_tag_code1: TAG code name *
616 * p_reading_time1: corresponding reading_time *
617 * p_tag_code2: Another tag code name *
618 * p_reading_time2: corresponding reading_time to the second tag *
619 * p_range_in_hours: Number of hours, which is used to limit *
620 * the search of the prevous reading to the range that is earlier *
621 * than the reading_time and later than *
622 * reading_time + p_range_in_hours / 24. *
623 * p_pre_tag_data1: Previous tag reading for the tag_code1 *
624 * p_pre_tag_data2: Previous tag reading for the tag_code2 *
625 * Modification log : *
626 * Author Date Change: Yong Feng 14-Jul-2009 Initial Creation *
627 ******************************************************************************/
628 PROCEDURE GET_PREV_TAG_READING_SET(p_tag_code1 IN VARCHAR2,
629 p_reading_time1 IN DATE,
630 p_tag_code2 IN VARCHAR2,
631 p_reading_time2 IN DATE,
632 p_range_in_hours IN NUMBER DEFAULT NULL,
633 p_pre_tag_data1 OUT NOCOPY VARCHAR2,
634 p_pre_tag_data2 OUT NOCOPY VARCHAR2);
635
636
637
638 /* ****************************************************************************
639 * Function :VERIFY_TAG_DATA_TREND *
640 * Description Check consecutive values of tag readings is above *
641 * mean value (or) below mean value and the previous set of *
642 * data does not satisfy this condition *
643 * File Name :MTHUTILB.PLS *
644 * Visibility :Public
645 * Parameters : *
646 * p_tag_code: tag code name *
647 * p_tag_data: tag data *
648 * p_reading_time: corresponding reading_time *
649 * p_att_grp_name: group name *
650 * p_mean_attr_name: attribute name in EGO_ATTRS_V *
651 * p_num_of_readings: Number of consective readings to check *
652 * p_range_in_hours: Number of hours, which is used to limit *
653 * the search of the prevous reading to the range that is earlier *
654 * than the reading_time and later than *
655 * reading_time + p_range_in_hours / 24. *
656 * RETURN: 0 Does not satisfy the condition *
657 * 1 Has a up trend *
658 * 2 Has a down trend *
659 * Modification log : *
660 * Author Date Change: Yong Feng 14-Jul-2009 Initial Creation *
661 ******************************************************************************/
662 FUNCTION VERIFY_TAG_DATA_TREND(p_tag_code IN VARCHAR2,
663 p_tag_data IN VARCHAR2,
664 p_reading_time IN DATE,
665 p_att_grp_name IN VARCHAR2
666 DEFAULT 'SPECIFICATIONS',
667 p_mean_attr_name IN VARCHAR2
668 DEFAULT 'MEAN',
669 p_num_of_readings IN NUMBER,
673 /*****************************************************************************
670 p_range_in_hours IN NUMBER DEFAULT NULL)
671 RETURN NUMBER;
672
674 * Procedure :PUT_DOWN_STS_EXPECTED_UPTIME *
675 * Description :This procedure puts expected_up_time for planned *
676 * downtime in the mth_equip_statuses table *
677 * File Name :MTHUTILB.PLS *
678 * Visibility :Public
679 * Modification log : *
680 * Author Date Change: Shanthi Swaroop Donthu 18-Jul-2009 Initial Creation *
681 ******************************************************************************/
682 PROCEDURE PUT_DOWN_STS_EXPECTED_UPTIME;
683
684
685 /*****************************************************************************
686 * Procedure :MTH_LOAD_HOUR_STATUS *
687 * Description :This procedure is used to break the shift level status data *
688 * into hour level and populates into mth_equip_statuses table *
689 * File Name :MTHUTILB.PLS *
690 * Visibility :Public
691 * Modification log : *
692 * Author Date Change: Shanthi Swaroop Donthu 08-Dec-2009 Initial Creation *
693 ******************************************************************************/
694 PROCEDURE MTH_LOAD_HOUR_STATUS (p_equipment_fk_key IN NUMBER,p_shift_workday_fk_key IN NUMBER,
695 p_from_date IN DATE, p_to_date IN DATE,p_status IN VARCHAR2,
696 p_system_fk_key IN NUMBER,p_user_dim1_fk_key IN NUMBER, p_user_dim2_fk_key IN NUMBER,
697 p_user_dim3_fk_key IN NUMBER,p_user_dim4_fk_key IN NUMBER, p_user_dim5_fk_key IN NUMBER,
698 p_user_attr1 IN VARCHAR2,p_user_attr2 IN VARCHAR2, p_user_attr3 IN VARCHAR2,
699 p_user_attr4 IN VARCHAR2,p_user_attr5 IN VARCHAR2, p_user_measure1 IN NUMBER,
700 p_user_measure2 IN NUMBER,p_user_measure3 IN NUMBER, p_user_measure4 IN NUMBER,
701 p_user_measure5 IN NUMBER ,p_hour_fk_key IN NUMBER,p_hour_fk IN VARCHAR2,p_hour_to_time IN DATE);
702
703
704
705 /* ****************************************************************************
706 * Procedure :GENERATE_SHIFTS *
707 * Description :This procedure generates the shifts in workday shifts *
708 * and equipment shifts table *
709 * File Name :MTHUTILB.PLS *
710 * Visibility :Public
711 * Modification log : *
712 * Author Date Change: Amrit Kaur 04-Dec-2009 Initial Creation *
713 * Mandar Gijare 20-Dec-2010 Shift MED modification *
714 ******************************************************************************/
715
716
717 PROCEDURE GENERATE_SHIFTS( p_plant_fk_key IN NUMBER,
718 p_start_date IN DATE,
719 p_end_date IN DATE,
720 p_comp_state OUT NOCOPY VARCHAR2);
721
722 /* ****************************************************************************
723 * Procedure :INSERT_OPEN_SALES_ORDERS*
724 * Description :The procedure will insert open sales orders in the table, passed as a parameter,
725 *in the partition that is empty. In the initial run, both the partitions will be empty,
726 *then the data will move to partition with partition key=1. The alternate partition key
727 *will be passed as the out parameter *
728 * File Name :MTHUTILB.PLS *
729 * Visibility :Public
730 * Modification log : *
731 * Author Date Change: Amrit Kaur 28-Jul-2011 Initial Creation *
732 *
733 ******************************************************************************/
734
735
736 PROCEDURE insert_open_sales_orders(p_table_name IN VARCHAR2, p_current_processing_flag OUT NOCOPY NUMBER);
737
738
739
740 /* ****************************************************************************
741 * Function :get_incremental_tag_data *
742 * Description :Insert the error row into the error with the error code *
743 * File Name :MTHSUSAB.PLS *
744 * Visibility :Private *
745 * Parameters :p_tag_value - tag value *
746 * p_is_number - 1 if tag value is number; 0 otherwise *
747 * p_is_cumulative - 1 to apply incremental logic; *
748 * 0 otherwise *
749 * p_is_assending - 1 if tag is assending order; *
750 * 0 otherwise *
751 * p_initial_value - Tag initial value *
752 * p_max_reset_value - *
753 * p_prev_tag_value - Previous tag value *
754 * Return Value :Incremental value if incremental logic needs to be *
755 * be applied; return p_tag_value otherwise *
756 **************************************************************************** */
757 FUNCTION get_incremental_tag_data(P_TAG_VALUE IN VARCHAR2,
758 P_IS_NUMBER IN NUMBER,
759 P_IS_CUMULATIVE IN NUMBER,
760 P_IS_ASSENDING IN NUMBER,
761 P_INITIAL_VALUE IN NUMBER,
762 P_MAX_RESET_VALUE IN NUMBER,
763 p_prev_tag_value IN VARCHAR2) RETURN VARCHAR2;
764
765
766 /* ****************************************************************************
767 * Procedure :update_tag_to_latest_tab *
768 * Description :Update an existing the latest reading time and tag value *
769 * for a tag if table MTH_TAG_READINGS_LATEST already *
773 * Parameters :p_tag_code - tag code *
770 * has a entry for the tag. Otherwise, insert a new row *
771 * File Name :MTHUTILB.PLS *
772 * Visibility :Private *
774 * p_latest_reading_time - reading time of the latest *
775 * p_latest_tag_value - latest tag reading *
776 * p_lookup_entry_exist - whether the entry with the *
777 * same tag code exists in the *
778 * MTH_TAG_READINGS_LATEST or not *
779 * Return Value :None *
780 **************************************************************************** */
781 PROCEDURE update_tag_to_latest_tab(p_tag_code IN VARCHAR2,
782 p_latest_reading_time IN DATE,
783 p_latest_tag_value IN VARCHAR2,
784 p_lookup_entry_exist IN BOOLEAN);
785
786
787 /* ****************************************************************************
788 * Function :MTH_IS_TAG_RAW_DATA_ROW_VALID *
789 * Description :Check if the raw from MTH_TAG_READINGS_RAW is valid *
790 * or not. *
791 * File Name :MTHUTILB.PLS *
792 * Visibility :Private *
793 * Parameters :p_tag_code - Tag code *
794 * p_reading_time - Reading time *
795 * p_tag_value - tag value *
796 * p_is_number - 1 if tag value is number; 0 otherwise *
797 * p_is_cumulative - 1 to apply incremental logic; *
798 * 0 otherwise *
799 * p_is_assending - 1 if tag is assending order; *
800 * 0 otherwise *
801 * p_initial_value - Tag initial value *
802 * p_max_reset_value - *
803 * p_prev_reading_time - reading time for the previous *
804 * tag reading *
805 * OUT Parameters: p_is_valid_timestamp -Whether the reading time from *
806 * the current entry can be used *
807 * to calculate the from time of *
808 * the next reading *
809 * Return Value : Found violations of the following rules: *
810 * 'NGV' - Usage value is negative. *
811 * 'OTR' - Usage value is out of range defined *
812 * for a cumulative tag. *
813 * 'OTO' - The raw reading data is out of order. *
814 * 'DUP' - The raw reading data is duplicated. *
815 * 'FTD' - Data in future time *
816 * NULL - Valid row *
817 ***************************************************************************** */
818 FUNCTION MTH_IS_TAG_RAW_DATA_ROW_VALID
819 (p_tag_code IN VARCHAR2,
820 p_mast_tag_code IN VARCHAR2,
821 p_reading_time IN DATE,
822 p_tag_value IN VARCHAR2,
823 p_is_number IN NUMBER,
824 p_is_cumulative IN NUMBER,
825 p_is_assending IN NUMBER,
826 p_initial_value IN NUMBER,
827 p_max_reset_value IN NUMBER,
828 p_prev_reading_time IN DATE,
829 p_is_valid_timestamp OUT NOCOPY BOOLEAN ) RETURN VARCHAR2;
830
831
832
833 /* ****************************************************************************
834 * Procedure :MTH_LOAD_TAG_RAW_TO_PROCESSED *
835 * Description :Load data from the table MTH_TAG_READINGS_RAW *
836 * into meter readings table MTH_TAG_READINGS_RAW_PROCESSED *
837 * File Name :MTHUTILB.PLS *
838 * Visibility :Private *
839 * Parameters :p_curr_partition (value of the partition column *
840 * :p_from_tz (value for from time zone ) *
841 * :p_to_tz (value for to time zone ) *
842 **************************************************************************** */
843 PROCEDURE MTH_LOAD_TAG_RAW_TO_PROCESSED(p_curr_partition IN NUMBER, p_from_tz IN VARCHAR2,p_to_tz IN VARCHAR2);
844
845
846
847 /*****************************************************************************
848 * Procedure : Get_Run_Log_Dates *
849 * Description : The procedure returns from and to dates from run log. *
850 * File Name : MTHUTILB.pls *
851 * Visibility : Public *
852 *****************************************************************************/
853 PROCEDURE Get_Run_Log_Dates(
854 p_fact_table IN VARCHAR2,
855 p_db_global_name IN VARCHAR2 DEFAULT NULL,
856 p_hub_organization_code IN VARCHAR2 DEFAULT NULL,
857 p_ebs_organization_id IN NUMBER DEFAULT NULL,
858 p_from_date OUT NOCOPY DATE,
859 p_to_date OUT NOCOPY DATE);
860
861
862 /*****************************************************************************
866 * to set a unique execution_id. This is an optional step when *
863 * Procedure : Initialize_Debug *
864 * Description : The procedure obtains execution_id from MTH_EXECUTION_S. *
865 * This is required when being called from OWB task to be able *
867 * being called from concurrent program. *
868 * File Name : MTHUTILB.pls *
869 * Visibility : Public *
870 * Parameters : p_context_desc (current context description) *
871 * p_execution_id *
872 *****************************************************************************/
873 PROCEDURE Initialize_Debug(
874 p_context_desc IN VARCHAR2 DEFAULT NULL,
875 p_execution_id IN OUT NOCOPY NUMBER
876 );
877
878
879 /*****************************************************************************
880 * Procedure : Log_Msg *
881 * Description : The procedure logs the passed message appropriately. This *
882 * takes two parameters 1. p_msg_text and 2. p_msg_level. If *
883 * p_msg_level is greater than or equal to profile option *
884 * MTH_DEBUG_LEVEL then the message will be logged. *
885 * File Name : MTHUTILB.pls *
886 * Visibility : Public *
887 * Parameters : p_msg_txt *
888 * p_msg_lvl *
889 *****************************************************************************/
890 PROCEDURE Log_Msg(
891 p_msg_txt IN VARCHAR2,
892 p_msg_lvl IN NUMBER DEFAULT NULL
893 );
894
895
896 /* ****************************************************************************
897 * Procedure :INCR_EQUIP_SHIFTS*
898 * Description :The procedure will insert records in equipment shifts table by taking the data*
899 from workday shifts and mth_all_entities_v table *
900 * File Name :MTHUTILS.PLS *
901 * Visibility :Public
902 * Modification log : *
903 * Author Date Change: Amrit Kaur 27-Mar-2012 Initial Creation *
904 *
905 ******************************************************************************/
906
907 PROCEDURE INCR_EQUIP_SHIFTS;
908 /* ****************************************************************************
909 * Procedure :MTH_WORKDAY_SHIFTS_SF*
910 * Description :The procedure will insert valid records in workday shifts table and*
911 invalid records in workday shifts error table *
912 * File Name :MTHUTILB.PLS *
913 * Visibility :Public
914 * Modification log : *
915 * Author Date Change: Amrit Kaur 27-Mar-2012 Initial Creation *
916 *
917 ******************************************************************************/
918
919 PROCEDURE MTH_WORKDAY_SHIFTS_SF ;
920
921 /* ****************************************************************************
922 * Procedure :MTH_RESET_SEQUENCE *
923 * Description :This procedure is used to reset the sequence in the *
924 * specified schema. *
925 * File Name :MTHUTILS.PLS *
926 * Visibility :Public *
927 * Parameters :Table name , schema name *
928 * Modification log : *
929 * Author Date Change *
930 * Akanksha verma April-17-2012 Initial Creation *
931 **************************************************************************** */
932
933 PROCEDURE mth_Reset_sequence(p_seq_name IN VARCHAR2,v_schema_name IN VARCHAR2);
934
935 /* ****************************************************************************
936 * Procedure :CLEAN_UP_TABLE_DATA *
937 * Description :This procedure is used to truncate table,materialized view
938 * and reset sequence in the specified schema based on input parameter as MTH,
939 * SSDM or ALL. *
940 * File Name :MTHUTILS.PLS *
941 * Visibility :Public *
942 * Parameters :Table name , schema name *
943 * Modification log : *
944 * Author Date Change *
945 * Akanksha verma April-17-2012 Initial Creation *
946 **************************************************************************** */
947
948 PROCEDURE CLEAN_UP_TABLE_DATA(p_err_buff OUT NOCOPY VARCHAR2,
949 p_retcode OUT NOCOPY NUMBER,
950 P_PROD_TO_BE_CLEANUP IN VARCHAR2);
951
952
953 /*****************************************************************************
954 * Procedure : Get_Processing_Flag *
955 * Description : Returns the value of variable G_PROCESSING_FLAG. *
956 * Used in ODI IKM for SF interfaces *
957 * File Name : MTHUTILS.pls *
958 * Visibility : Public *
959 * Parameters : *
960 *****************************************************************************/
961 FUNCTION Get_Processing_Flag(p_table_name IN VARCHAR2 DEFAULT 'default') RETURN NUMBER;
962
963
964 END MTH_UTIL_PKG;
[Home] [Help]