1 Package MTH_UTIL_PKG AS
2 /*$Header: mthutils.pls 120.3.12010000.5 2008/11/06 01:37:17 tkan ship $ */
3
4
5 /* ****************************************************************************
6 * Procedure :MTH_RUN_LOG_PRE_LOAD *
7 * Description :This procedure is used for the population of the *
8 * mth_run_log table for the initial and incremental load. The procedure is *
9 * called at the begenning of the mapping execution sequence to set the *
10 * boundary conditions for the ebs collection for the corresponding fact *
11 * or dimension. *
12 * File Name :MTHUTILS.PLS *
13 * Visibility :Public *
14 * Parameters : *
15 * Modification log : *
16 * Author Date Change *
17 * Ankit Goyal 31-May-2007 Initial Creation *
18 **************************************************************************** */
19
20 PROCEDURE mth_run_log_pre_load(p_fact_table IN VARCHAR2,p_db_global_name
21 IN VARCHAR2,p_run_mode IN VARCHAR2,p_run_start_date IN DATE, p_is_fact IN NUMBER
22 ,p_to_date IN DATE);
23
24
25 /* ****************************************************************************
26 * Procedure :MTH_RUN_LOG_POST_LOAD *
27 * Description :This procedure is used for the population of the *
28 * mth_run_log table for the initial and incremental load. The procedure is *
29 * called at the end of the mapping execution sequence to set the *
30 * boundary conditions for the ebs collection for the corresponding fact *
31 * File Name :MTHUTILS.PLS *
32 * Visibility :Public *
33 * Parameters : *
34 * Modification log : *
35 * Author Date Change *
36 * Ankit Goyal 31-May-2007 Initial Creation *
37 **************************************************************************** */
38 PROCEDURE mth_run_log_post_load(p_fact_table IN VARCHAR2,
39 p_db_global_name IN VARCHAR2);
40
41 /* ****************************************************************************
42 * Procedure :MTH_hrchy_BALANCE_LOAD *
43 * Description :This procedure is used for the balancing of the *
44 * hierarchy. The algorithm used for the balancing is down balancing *
45 * Please refer to the Item fdd for more details on this. *
46 * File Name :MTHUTILS.PLS *
47 * Visibility :Public *
48 * Parameters :fact table name *
49 * Modification log : *
50 * Author Date Change *
51 * Ankit Goyal 17-Aug-2007 Initial Creation *
52 **************************************************************************** */
53 PROCEDURE mth_hrchy_balance_load(p_fact_table IN VARCHAR2);
54
55 /* ****************************************************************************
56 * Procedure :MTH_TRUNCATE_TABLE *
57 * Description :This procedure is used to truncate the table in the *
58 * MTH Schema. Thsi can be overriden by spefying a custom schema name as well. *
59 * File Name :MTHUTILS.PLS *
60 * Visibility :Public *
61 * Parameters :Table name *
62 * Modification log : *
63 * Author Date Change *
64 * Ankit Goyal 11-Oct-2007 Initial Creation *
65 **************************************************************************** */
66
67 PROCEDURE mth_truncate_table(p_table_name IN VARCHAR2);
68
69 /* ****************************************************************************
70 * Procedure :MTH_TRUNCATE_TABLE *
71 * Description :This procedure is used to truncate the table in the *
72 * specified schema. *
73 * File Name :MTHUTILS.PLS *
74 * Visibility :Public *
75 * Parameters :Table name , schema name *
76 * Modification log : *
77 * Author Date Change *
78 * Yong Feng July-18-2008 Initial Creation *
79 **************************************************************************** */
80
81 PROCEDURE mth_truncate_table(p_table_name IN VARCHAR2,
82 p_schema_name IN VARCHAR2);
83
84 /* ****************************************************************************
85 * Procedure :MTH_TRUNCATE_TABLES *
86 * Description :This procedure is used to truncate the tables in the *
87 * list separated by comma. *
88 * File Name :MTHUTILS.PLS *
89 * Visibility :Public *
90 * Parameters :p_list_table_names: List of table names separated *
91 * by commas. *
92 * Modification log : *
93 * Author Date Change *
94 * Yong Feng Aug-07-2008 Initial Creation *
95 **************************************************************************** */
96
97 PROCEDURE mth_truncate_tables(p_list_table_names IN VARCHAR2);
98
99 /* ****************************************************************************
100 * Procedure :MTH_TRUNCATE_MV_LOGS *
101 * Description :This procedure is used to truncate the Materialized *
102 * View log created on the tables *
103 * list separated by comma. *
104 * File Name :MTHUTILS.PLS *
105 * Visibility :Public *
106 * Parameters :p_list_table_names: List of table names separated *
107 * by commas. *
108 * Modification log : *
109 * Author Date Change *
110 * Yong Feng Aug-07-2008 Initial Creation *
111 **************************************************************************** */
112
113 PROCEDURE MTH_TRUNCATE_MV_LOGS (p_list_table_names IN VARCHAR2);
114
115 /* ****************************************************************************
116 * Function :MTH_UA_GET_VAL *
117 * Description : This procedure is used to return the lookup code for*
118 * the unasssigned *
119 * File Name :MTHUTILS.PLS *
120 * Visibility :Public *
121 * Parameters :Table name *
122 * Modification log : *
123 * Author Date Change *
124 * Ankit Goyal 11-Oct-2007 Initial Creation *
125 **************************************************************************** */
126
127 Function mth_ua_get_val RETURN NUMBER;
128
129 /* ****************************************************************************
130 * Function :MTH_UA_GET_MEANING *
131 * Description :This procedure is used to return the lookup meaning *
132 * for the unasssigned *
133 * File Name :MTHUTILS.PLS *
134 * Visibility :Public *
135 * Parameters :Table name *
136 * Modification log : *
137 * Author Date Change *
138 * Ankit Goyal 23-Oct-2007 Initial Creation *
139 **************************************************************************** */
140
141 Function mth_ua_get_meaning RETURN Varchar2;
142 /* ****************************************************************************
143 * Procedure :request_lock *
144 * Description :This procedure is used to request an exclusive *
145 * lock with p_key_table as the key using rdbms_lock package. The current *
146 * will wait indifinitely if the lock was held by others until the release *
147 * of the lock. *
148 * File Name :MTHUTILB.PLS *
149 * Visibility :Private *
150 * Parameters *
151 * p_key_table : The name used to request an exclusive lock. *
152 * p_retval : The return value of the operation: *
153 * 0 - Success *
154 * 1 - Timeout *
155 * 2 - Deadlock *
156 * 3 - Parameter Error *
157 * 4 - Already owned *
158 * 5 - Illegal Lock Handle *
159 * Modification log : *
160 * Author Date Change *
161 * Yong Feng 17-Oct-2007 Initial Creation *
162 **************************************************************************** */
163
164 PROCEDURE request_lock(p_key_table IN VARCHAR2, p_retval OUT NOCOPY INTEGER);
165
166 /* ****************************************************************************
167 * Procedure :generate_new_time_range *
168 * Description :This procedure is used to generate a time range *
169 * starting from the last end date up to current time, sysdate, using *
170 * the p_key_table name as the key to look up the entry in MTH_RUN_LOG *
171 * table. If the entry does not exist, create one and set the time range *
172 * to a hard-coded past time to current time. *
173 * File Name :MTHUTILB.PLS *
174 * Visibility :Public *
175 * Parameters *
176 * p_key_table : Name to uniquely identify one entry in the *
177 * mth_run_log table. *
178 * p_start_date : An output value that specifies the start time *
179 * of the new time period. *
180 * p_end_date : An output value that specifies the end time *
181 * of the new time period. *
182 * p_exclusive_lock : Specify whether it needs to request an exclusive *
183 * lock using p_key_table as the key so that only *
184 * one procedure will be running at one point of time. *
185 * If the value is 1, then it will run in exclusive *
186 * mode. The lock will be released when the *
187 * transaction is either committed or rollbacked. *
188 * Modification log : *
189 * Author Date Change *
190 * Yong Feng 17-Oct-2007 Initial Creation *
191 **************************************************************************** */
192
193 PROCEDURE generate_new_time_range(p_key_table IN VARCHAR2,
194 p_start_date OUT NOCOPY DATE,
195 p_end_date OUT NOCOPY DATE,
196 p_exclusive_lock IN NUMBER DEFAULT 1);
197
198
199
200 /* ****************************************************************************
201 * Function :GET_PROFILE_VAL *
202 * Description :This function is used to retrive the value of the *
203 * profile for the profile name provided by the user *
204 * File Name :MTHSOURCEPATCHS.PLS *
205 * Visibility :Public *
206 * Return : V_PROFILE_NAME - Global name of the source DB *
207 * Modification log : *
208 * Author Date Change *
209 * Ankit Goyal 29-Oct-2007 Initial Creation *
210 ******************************************************************************/
211 FUNCTION get_profile_val(p_profile_name IN VARCHAR2) RETURN VARCHAR2;
212
213 /* ****************************************************************************
214 * Function :Get_UDA_Eq_HId *
215 * Description :This function is used to retrive the hierarchy id of *
216 * the UDA Equipment profile *
217 * File Name :MTHUTILS.PLS *
218 * Visibility :Public *
219 * Return :Hierarchy id for the equipment UDA profile *
220 * Modification log : *
221 * Author Date Change *
222 * Vivek 18-Jan-2008 Initial Creation *
223 ******************************************************************************/
224 FUNCTION Get_UDA_Eq_HId RETURN VARCHAR;
225
226 /* ****************************************************************************
227 * Function :Get_UDA_Eq_LNo *
228 * Description :This function is used to retrive the Level Number of *
229 * the UDA Equipment profile *
230 * File Name :MTHUTILS.PLS *
231 * Visibility :Public *
232 * Return :Level Number for the equipment UDA profile *
233 * Modification log : *
234 * Author Date Change *
235 * Vivek 18-Jan-2008 Initial Creation *
236 ******************************************************************************/
237
238 FUNCTION Get_UDA_Eq_LNo RETURN VARCHAR;
239
240 /* ****************************************************************************
241 * Procedure :REFRESH_MV *
242 * Description :This procedure is used to call DBMS_MVIEW.REFRESH *
243 * procedure to refresh MVs. *
244 * File Name :MTHUTILB.PLS *
245 * Visibility :Public *
246 * Parameters *
247 * p_list : Comma-separated list of materialized views that *
248 * you want to refresh. *
249 * p_method :A string of refresh methods indicating how to *
250 * refresh the listed materialized views. *
251 * - An f indicates fast refresh *
252 * - ? indicates force refresh *
253 * - C or c indicates complete refresh *
254 * - A or a indicates always refresh. A and C are *
255 * equivalent. *
256 * p_rollback_seg :Name of the materialized view site rollback segment *
257 * to use while refreshing materialized views. *
258 * p_push_deferred_rpc : Used by updatable materialized views only. *
259 * p_refresh_after_errors: *
260 * p_purge_option : *
261 * p_parallelism : 0 specifies serial propagation *
262 * p_heap_size : *
263 * p_atomic_refresh : *
264 * Modification log : *
265 * Author Date Change *
266 * Yong Feng 11-July-2008 Initial Creation *
267 **************************************************************************** */
268
269 PROCEDURE REFRESH_MV(
270 p_list IN VARCHAR2,
271 p_method IN VARCHAR2 := NULL,
272 p_rollback_seg IN VARCHAR2 := NULL,
273 p_push_deferred_rpc IN BOOLEAN := true,
274 p_refresh_after_errors IN BOOLEAN := false,
275 p_purge_option IN BINARY_INTEGER := 1,
276 p_parallelism IN BINARY_INTEGER := 0,
277 p_heap_size IN BINARY_INTEGER := 0,
278 p_atomic_refresh IN BOOLEAN := true
279 );
280
281
282 /* ****************************************************************************
283 * Procedure :REFRESH_ONE_MV *
284 * Description :This procedure is used to call refresh one MV. *
285 * File Name :MTHUTILB.PLS *
286 * Visibility :Public *
287 * Parameters *
288 * p_mv_name : Name of the materialized view to be refreshed. *
289 * p_method :A string of refresh methods indicating how to *
290 * refresh the listed materialized views. *
291 * - An f indicates fast refresh *
292 * - ? indicates force refresh *
293 * - C or c indicates complete refresh *
294 * - A or a indicates always refresh. A and C are *
295 * equivalent. *
296 * p_rollback_seg :Name of the materialized view site rollback segment *
297 * to use while refreshing materialized views. *
298 * p_refresh_mode :A string of refresh mode: *
299 * - C , c or NULL indicates complete refresh. *
300 * - R or r indicates resume refresh that has been *
301 * started earlier. The MV will be refreshed if the *
302 * refresh date is earlier than the date stored in *
303 * to_date column in MTH_RUN_LOG for MTH_ALL_MVS entry. *
304 * p_push_deferred_rpc : Used by updatable materialized views only. *
305 * Modification log : *
306 * Author Date Change *
307 * Yong Feng 19-Aug-2008 Initial Creation *
308 **************************************************************************** */
309
310 PROCEDURE REFRESH_ONE_MV(
311 p_mv_name IN VARCHAR2,
312 p_method IN VARCHAR2 := NULL,
313 p_rollback_seg IN VARCHAR2 := NULL,
314 p_refresh_mode IN VARCHAR2 := NULL
315 );
316
317 /* *****************************************************************************
318 * Procedure :PUT_EQUIP_DENORM_LEVEL_NUM *
319 * Description :This procedure is used to insert the level_num column *
320 * in the mth_equipment_denorm_d table *
321 * File Name :MTHUTILS.PLS *
322 * Visibility :Private *
323 * Modification log : *
324 * Author Date Change *
325 * shanthi donthu 16-Jul-2008 Initial Creation *
326 ***************************************************************************** */
327
328 PROCEDURE PUT_EQUIP_DENORM_LEVEL_NUM;
329
330 /* *****************************************************************************
331 * Procedure :update_equip_hrchy_gid *
332 * Description :This procedue is used for updating the group_id column in *
333 * the mth_equip_hierarchy table. The group id will be used to determine the *
334 * sequence in which a particular record will be processed in the equipment SCD *
335 * logic. The oldest relationships will have the lowest group id =1 and the new *
336 * relationships will have higher group id. All the catch all relationships i.e.*
337 * the relationship with parent = -99999 and effective date = 1-Jan-1900 will *
338 * have group id defaulted to 1 inside the MTH_EQUIP_HRCHY_UA_ALL_MAP map. *
339 * File Name :MTHUTILB.PLS *
340 * Visibility :Public *
341 * Parameters : none *
342 * Modification log : *
343 * Author Date Change *
344 * Ankit Goyal 26-Aug-2008 Initial Creation *
348
345 ***************************************************************************** */
346
347 PROCEDURE update_equip_hrchy_gid;
349 /* *****************************************************************************
350 * Function :get_min_max_gid *
351 * Description :This finction returns the minimum or maximum group id in the *
352 * Equipment hierarchy table. *
353 * File Name :MTHUTILB.PLS *
354 * Visibility :Public *
355 * Parameters : minmax Number. minmax= 1 Minimum, minmax =2 Maximum *
356 * Modification log : *
357 * Author Date Change *
358 * Ankit Goyal 26-Aug-2008 Initial Creation *
359 ***************************************************************************** */
360
361 FUNCTION get_min_max_gid(minmax IN NUMBER) RETURN NUMBER;
362
363 /* *****************************************************************************
364 * Procedure :switch_column_default_value *
365 * Description :This procedure will determine the current value of the *
366 * processing_flag of the table, issue an alter table statement to switch *
367 * the default values to another (1 to 2, or 2 to 1,) and return the *
368 * current value. If there are no data in the table, do nothing and return *
369 * 0. *
370 * File Name :MTHUTILB.PLS *
371 * Visibility :Public *
372 * Parameters : *
373 * p_table_name: table name *
374 * p_current_processing_flag: the current value of processing_flag *
375 * It could be 1, or 2 for normal case. *
376 * If it is 0, then no data is available *
377 * the table. So no process is needed. *
378 * Modification log : *
379 * Author Date Change: Yong Feng 10/2/08 Initial creation *
380 ***************************************************************************** */
381
382 PROCEDURE switch_column_default_value (p_table_name IN VARCHAR2,
383 p_current_processing_flag OUT NOCOPY NUMBER);
384
385 /* *****************************************************************************
386 * Procedure :truncate_table_partition *
387 * Description :This procedure will truncate the partition corresponding *
388 * to the value of p_current_processing_flag. *
389 * File Name :MTHUTILB.PLS *
390 * Visibility :Public *
391 * Parameters : *
392 * p_table_name: table name *
393 * p_current_processing_flag: Used to determine the partition to be *
394 * truncated. Truncate p1 if the value is 1; truncate p2 if 2. *
395 * Modification log : *
396 * Author Date Change: Yong Feng 10/2/08 Initial creation *
397 ***************************************************************************** */
398
399 PROCEDURE truncate_table_partition (p_table_name IN VARCHAR2,
400 p_current_processing_flag IN NUMBER);
401
402
403 END MTH_UTIL_PKG;
[Home] [Help]