1 PACKAGE BODY mth_util_pkg AS
2 /*$Header: mthutilb.pls 120.8.12010000.6 2008/11/06 01:37:01 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 :MTHUTILB.PLS *
13 * Visibility :Public *
14 * Parameters : p_fact_name :name of the fact *
15 * p_db_global_name :source system global name *
16 * p_run_mode :run mode 'INITIAL' or blank *
17 * p_run_start_date :run start date for the run *
18 * p_is_fact :0=false, 1=true *
19 * p_to_date :to_date for the run *
20 * Modification log : *
21 * Author Date Change *
22 * Ankit Goyal 31-May-2007 Initial Creation *
23 * Ankit Goyal 03-Jul-2007 Incorporated pushkala comments *
24 * Amrit Kaur 14-mar-2008 Commented apps_initilalize due to Bug 6737820 *
25 ***************************************************************************** */
26 PROCEDURE mth_run_log_pre_load( p_fact_table IN VARCHAR2,
27 p_db_global_name IN VARCHAR2,
28 p_run_mode IN VARCHAR2,
29 p_run_start_date IN DATE,
30 p_is_fact IN NUMBER,
31 p_to_date IN DATE)
32 IS
33 --local variable declation
34
35 l_fact_table mth_run_log.fact_table%TYPE;--fact table name
36 l_ebs_organization_id mth_run_log.ebs_organization_id%TYPE;
37 l_ebs_organization_code mth_run_log.ebs_organization_code%TYPE;
38 l_from_date mth_run_log.from_date%TYPE;--from date of the run
39 l_to_date mth_run_log.to_date%TYPE;--to date of the run
40 l_source mth_run_log.source%TYPE;--process or discrete. Not used currently
41 l_system_fk_key mth_systems_setup.system_pk_key%TYPE;
42 l_creation_date mth_run_log.creation_date%TYPE;--who column
43 l_last_update_date mth_run_log.last_update_date%TYPE;--who column
44 l_creation_system_id mth_run_log.creation_system_id%TYPE;--who column
45 l_last_update_system_id mth_run_log.last_update_system_id%TYPE;--who column
46 --l_sysdate to holding the current date
47 l_sysdate DATE := sysdate;--target system sysdate
48 --l_mode is used to determine the run type , initial 0 or incremental 1
49 l_mode NUMBER := 0;--initial default
50 l_plant_start_date DATE; --Plant end date
51 --Hub organization code
52 l_hub_organization_code mth_run_log.hub_organization_code%TYPE;
53
54 --cursor for iterating through the ebs organizations in mth_plants_d
55 --the rows in the mth_run_log will be at organization granularity
56 CURSOR c_ebs_orgs IS
57 SELECT ebs_organization_id,organization_code,
58 source,plant_pk,system_fk_key,from_date
59 FROM mth_plants_d, mth_systems_setup,mth_organizations_l
60 WHERE system_pk_key = system_fk_key
61 AND system_pk = p_db_global_name
62 AND NVL(to_date,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)--pick active plants only
63 AND plant_fk_key=plant_pk_key;
64
65 BEGIN
66 IF p_run_mode = 'INITIAL' THEN--Initial load
67 --delete the rows from run log
68 DELETE FROM mth_run_log WHERE fact_table = p_fact_table;
69
70 END IF;
71 --initialize the local variables and who columns
72 l_fact_table := p_fact_table;
73 l_last_update_system_id := -99999;
74 l_last_update_date := l_sysdate;
75 l_to_date := p_to_date;
76
77 --initialize the global start date
78 IF p_is_fact = 0 THEN--for dimensions only
79 l_from_date := To_Date('01-01-1900','MM-DD-YYYY');
80 END IF;
81
82 --iterate through the cursor
83 FOR l_orgs IN c_ebs_orgs
84 LOOP
85
86 --initialize the variables for current cursor value
87 l_ebs_organization_id := l_orgs.ebs_organization_id;
88 l_source := l_orgs.source;--pick source column from the plants
89 l_ebs_organization_code := l_orgs.organization_code;
90 l_creation_date := l_sysdate;
91 l_creation_system_id := -1;
92
93 IF p_is_fact = 1 THEN--for facts only
94 l_from_date := l_orgs.from_date;--run start date= plant start date
95 END IF;
96
97 l_plant_start_date := l_orgs.from_date;
98 l_hub_organization_code := l_orgs.plant_pk;
99
100
101 IF l_orgs.ebs_organization_id is null THEN
102 /* We are dealing with non-ebs org configured to the passed system*/
103 /* Check if there are records for non-ebs organizations from same system and same fact and same plant */
104 SELECT COUNT(*)
105 INTO l_mode
106 FROM mth_run_log
107 WHERE fact_table = l_fact_table
108 AND db_global_name = p_db_global_name
109 AND hub_organization_code = l_orgs.plant_pk;
110
111 IF l_mode = 0 OR UPPER(p_run_mode) = 'INITIAL' THEN /* Initial Load */
112
113 --statement for insert
114
115 INSERT INTO mth_run_log (fact_table, ebs_organization_id,
116 ebs_organization_code,from_date,to_date, source, db_global_name,
117 creation_date,last_update_date,creation_system_id,
118 last_update_system_id,plant_start_date,hub_organization_code)
119 VALUES(l_fact_table,l_ebs_organization_id,l_ebs_organization_code,
120 l_from_date,l_to_date,l_source,p_db_global_name,l_creation_date,
121 l_last_update_date,l_creation_system_id,l_last_update_system_id,
122 l_plant_start_date,l_hub_organization_code);
123
124 ELSE
125 /* update all non_ebs organizations from same system and plant with to_date as the passed date */
126 --Custom Logic for the time dimension
127 IF p_fact_table = 'MTH_WORKDAY_SHIFTS_D'
128 THEN
129 UPDATE mth_run_log
130 SET from_date = p_run_start_date
131 WHERE
132 fact_table = p_fact_table and db_global_name=p_db_global_name;
133 END IF ;
134
135 --statment for update
136 UPDATE mth_run_log
137 SET TO_DATE = l_to_date,
138 LAST_UPDATE_DATE = l_last_update_date,
139 LAST_UPDATE_SYSTEM_ID =l_last_update_system_id
140 WHERE
141 fact_table =l_fact_table
142 AND db_global_name = p_db_global_name
143 AND hub_organization_code = l_hub_organization_code;
144
145
146 END IF; /* END of Initial VS Incremental */
147
148 ELSE
149 /* We are dealing with EBS Organizations */
150
151 --determine if there are any rows in the mth_run_log for
152 --the fact_table corresponding to the org
153 SELECT COUNT(*)
154 INTO l_mode
155 FROM mth_run_log
156 WHERE fact_table = l_fact_table
157 AND ebs_organization_id = l_orgs.ebs_organization_id
158 AND db_global_name = p_db_global_name
159 AND hub_organization_code = l_orgs.plant_pk;
160
161 --l_mode = 0 means that it is a initial run. p_run_mode is
162 --for forceful execution of the initial load
163
164 IF l_mode = 0 OR UPPER(p_run_mode) = 'INITIAL' THEN--initial load
165
166 --initialize the variables for initial load
167 l_creation_date := l_sysdate;
168 l_creation_system_id := -1;
169
170 --statement for insert
171
172 INSERT INTO mth_run_log (fact_table, ebs_organization_id,
173 ebs_organization_code,from_date,to_date, source, db_global_name,
174 creation_date,last_update_date,creation_system_id,
175 last_update_system_id,plant_start_date,hub_organization_code)
176 VALUES(l_fact_table,l_ebs_organization_id,l_ebs_organization_code,
177 l_from_date,l_to_date,l_source,p_db_global_name,l_creation_date,
178 l_last_update_date,l_creation_system_id,l_last_update_system_id,
179 l_plant_start_date,l_hub_organization_code);
180
181
182
183 --if the above condition fails then update the row
184 ELSE--incremental load
185
186 --Custom Logic for the time dimension
187 IF p_fact_table = 'MTH_WORKDAY_SHIFTS_D'
188 THEN
189 UPDATE mth_run_log
190 SET from_date = p_run_start_date
191 WHERE
192 fact_table = p_fact_table;
193 END IF ;
194 --statment for update
195 UPDATE mth_run_log
196 SET TO_DATE = l_to_date,
197 LAST_UPDATE_DATE = l_last_update_date,
198 LAST_UPDATE_SYSTEM_ID =l_last_update_system_id
199 WHERE
200 fact_table =l_fact_table
201 AND source =l_source
202 AND db_global_name = p_db_global_name
203 AND ebs_organization_id = l_ebs_organization_id
204 AND hub_organization_code = l_hub_organization_code;
205
206 --end of if clause
207 END IF; /* END of Initial VS Incremental */
208
209 END IF; /* End of EBS VS NON-EBS */
210 --end of the for loop
211 END LOOP;
212
213 --handle exceptions
214 EXCEPTION
215 WHEN NO_DATA_FOUND THEN
216 RAISE_APPLICATION_ERROR (-20001,
217 'Exception has occured');
218
219 END mth_run_log_pre_load;
220
221
222 /* *****************************************************************************
223 * Procedure :MTH_RUN_LOG_POST_LOAD *
224 * Description :This procedure is used for the population of the *
225 * mth_run_log table for the initial and incremental load. The procedure is *
226 * called at the end of the mapping execution sequence to set the *
227 * boundary conditions for the ebs collection for the corresponding fact *
228 * or dimension. *
229 * File Name : MTHUTILB.PLS *
230 * Visibility : Public *
231 * Parameters : p_fact_name : name of the fact table in the hub *
232 * p_global_name: source system global name *
233 * Modification log : *
234 * Author Date Change *
235 * Ankit Goyal 31-May-2007 Initial Creation *
236 * Ankit Goyal 03-Jul-2007 Incorporated pushkala's comments *
237 ***************************************************************************** */
238 PROCEDURE mth_run_log_post_load(p_fact_table IN VARCHAR2,
239 p_db_global_name IN VARCHAR2)
240
241 IS
242
243 --local variables initialization
244 l_fact_table mth_run_log.fact_table%TYPE;--fact table
245 l_sysdate DATE := sysdate;--variable for sysdate
246 l_last_update_system_id mth_run_log.last_update_system_id%TYPE;
247 l_last_update_date mth_run_log.last_update_date%TYPE;
248 l_system_fk_key mth_systems_setup.system_pk_key%TYPE;
249 l_to_date mth_run_log.to_date%TYPE;--variable for storing to_date
250 l_from_date mth_run_log.from_date%TYPE;--variable for storing from_date
251 l_ebs_organization_id mth_run_log.ebs_organization_id%TYPE;
252 --Hub organization code
253 l_hub_organization_code mth_run_log.hub_organization_code%TYPE;
254
255 --cursor for iterating through the ebs organizations in mth_plants_d
256 --the rows in the mth_run_log will be at organization granularity
257
258 CURSOR c_ebs_orgs
259 IS
260 SELECT ebs_organization_id,system_fk_key
261 FROM mth_plants_d, mth_systems_setup,mth_organizations_l
262 WHERE system_pk_key = system_fk_key
263 AND system_pk = p_db_global_name
264 AND NVL(to_date,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)--pick active plants only
265 AND plant_fk_key=plant_pk_key;
266
267
268 BEGIN
269
270 --initialize the varialbles common to the initial and incremental
271 -- loading
272
273 l_fact_table := p_fact_table;
274 l_last_update_system_id := -99999;
275 l_last_update_date := l_sysdate;
276 --iterate through the cursor
277
278 /* The to_date for all organizations populated for a given fact and system are bound to be the same because
279 all organizations are populated with sysdate(source or target) as the to_date. So giving a generic update command here would work */
280
281 SELECT min(to_date) --min is to avoid getting duplicate rows
282 INTO l_from_date--from date set to previous to_date
283 FROM mth_run_log
284 WHERE fact_table = l_fact_table
285 AND db_global_name = p_db_global_name;
286
287 --if statement to restrict the accidental re run of the block
288 IF l_from_date IS NOT NULL THEN
289 --update the mth run log for next incremental run
290
291 UPDATE mth_run_log
292 SET from_date = l_from_date,--from date set to previous to_date
293 to_date = NULL,--to_date set to null for next run
294 last_update_date = l_last_update_date
295 Where fact_table = l_fact_table
296 AND db_global_name = p_db_global_name;
297
298 END IF;
299
300
301
302 /*
303 FOR l_orgs IN c_ebs_orgs
304 LOOP
305 --initialize the variables for current cursor value
306 l_ebs_organization_id := l_orgs.ebs_organization_id;
307
308
309 --select the next starting date into l_from_date
310 SELECT to_date
311 INTO l_from_date--from date set to previous to_date
312 FROM mth_run_log
313 WHERE fact_table = l_fact_table
314 AND db_global_name = p_db_global_name
315 AND ebs_organization_id = l_ebs_organization_id;
316
317
318 --if statement to restrict the accidental re run of the block
319 IF l_from_date IS NOT NULL THEN
320 --update the mth run log for next incremental run
321
322 UPDATE mth_run_log
323 SET from_date = l_from_date,--from date set to previous to_date
324 to_date = NULL,--to_date set to null for next run
325 last_update_date = l_last_update_date
329
326 Where fact_table = l_fact_table
327 AND ebs_organization_id = l_ebs_organization_id
328 AND db_global_name = p_db_global_name;
330 END IF;
331
332 END LOOP;
333 */
334
335 --handle exceptions
336 EXCEPTION
337 WHEN NO_DATA_FOUND THEN
338 RAISE_APPLICATION_ERROR (-20001,
339 'Exception has occured');
340 END mth_run_log_post_load;
341
342
343 /* *****************************************************************************
344 * Procedure :MTH_HRCHY_BALANCE_LOAD *
345 * Description :This procedure is used for the balancing of the *
346 * hierarchy. The algorithm used for the balancing is down balancing *
347 * Please refer to the Item fdd for more details on this. *
348 * File Name :MTHUTILS.PLS *
349 * Visibility :Public *
350 * Parameters :fact table name *
351 * Modification log : *
352 * Author Date Change *
353 * Ankit Goyal 17-Aug-2007 Initial Creation *
354 ****************************************************************************** */
355 PROCEDURE mth_hrchy_balance_load(p_fact_table IN VARCHAR2) is
356
357 v_fact_table VARCHAR2(120);
358
359 --user defined type for array of records
360 TYPE denorm_rec_tab_type IS TABLE OF NUMBER;
361 TYPE denorm_rec_name_tab_type IS TABLE OF VARCHAR2(240);
362
363 --user defined type of record of arrays
364 TYPE denorm_rec_type IS RECORD (level9_fk_key denorm_rec_tab_type,
365 hierarchy_id denorm_rec_tab_type,
366 baselevel_fk_key denorm_rec_tab_type,
367 level7_fk_key denorm_rec_tab_type,
368 level6_fk_key denorm_rec_tab_type,
369 level5_fk_key denorm_rec_tab_type,
370 level4_fk_key denorm_rec_tab_type,
371 level3_fk_key denorm_rec_tab_type,
372 level2_fk_key denorm_rec_tab_type,
373 level1_fk_key denorm_rec_tab_type,
374 level9_name denorm_rec_name_tab_type,
375 level7_name denorm_rec_name_tab_type,
376 level6_name denorm_rec_name_tab_type,
377 level5_name denorm_rec_name_tab_type,
378 level4_name denorm_rec_name_tab_type,
379 level3_name denorm_rec_name_tab_type,
380 level2_name denorm_rec_name_tab_type,
381 level1_name denorm_rec_name_tab_type
382 );
383
384 --instantiation of the user defined type
385 --this will be the placeholder for the records fetched from the denorm table
386 denorm_rec denorm_rec_type;
387
388 --user defined cursor to hold the bulk collection of records
389 item_cur SYS_REFCURSOR;
390
391 --variable for the limit of the bulk collection
392 v_limit NUMBER :=5000;
393
394
395 BEGIN
396
397 --initialize the collection
398 denorm_rec := NULL;
399
400 --initialize the fact table name
401 v_fact_table :=p_fact_table;
402
403 --open the cursor
404 OPEN item_cur FOR 'SELECT --select for the newe levels
405 level9_fk_key,hierarchy_id,item_fk_key,
406 Decode(diff_level,1,level8_fk_key,level9_fk_key) level7_fk_key_new,
407 Decode(diff_level,1,level7_fk_key,2,level8_fk_key,level9_fk_key)
408 level6_fk_key_new,
409 Decode(diff_level,1,level6_fk_key,2,level7_fk_key,3,level8_fk_key,
410 level9_fk_key) level5_fk_key_new,
411 Decode(diff_level,1,level5_fk_key,2,level6_fk_key,3,level7_fk_key,4,
412 level8_fk_key,level9_fk_key) level4_fk_key_new,
413 Decode(diff_level,1,level4_fk_key,2,level5_fk_key,3,level6_fk_key,4,
414 level7_fk_key,5,level8_fk_key,level9_fk_key) level3_fk_key_new,
415 Decode(diff_level,1,level3_fk_key,2,level4_fk_key,3,level5_fk_key,4,
416 level6_fk_key,5,level7_fk_key,6,level8_fk_key,level9_fk_key)
417 level2_fk_key_new,
418 Decode(diff_level,1,level2_fk_key,2,level3_fk_key,3,level4_fk_key,4,
419 level5_fk_key,5,level6_fk_key,6,level7_fk_key,7,level8_fk_key,
420 level9_fk_key) level1_fk_key_new,
421 level9_name,
422 Decode(diff_level,1,level8_name,level9_name) level7_name_new,
423 Decode(diff_level,1,level7_name,2,level8_name,level9_name)
424 level6_name_new,
425 Decode(diff_level,1,level6_name,2,level7_name,3,level8_name,
426 level9_name) level5_name_new,
427 Decode(diff_level,1,level5_name,2,level6_name,3,level7_name,4,
428 level8_name,level9_name) level4_name_new,
429 Decode(diff_level,1,level4_name,2,level5_name,3,level6_name,4,
430 level7_name,5,level8_name,level9_name) level3_name_new,
431 Decode(diff_level,1,level3_name,2,level4_name,3,level5_name,4,
432 level6_name,5,level7_name,6,level8_name,level9_name)
433 level2_name_new,
434 Decode(diff_level,1,level2_name,2,level3_name,3,level4_name,4,
435 level5_name,5,level6_name,6,level7_name,7,level8_name,
436 level9_name) level1_name_new
437 from
438 (--select the levels to be balanced
439 SELECT hierarchy_id ,item_fk_key,
440 level9_fk_key,level8_fk_key,level7_fk_key,level6_fk_key,
441 level5_fk_key,level4_fk_key,level3_fk_key,level2_fk_key,
442 level1_fk_key,
443 level9_name,level8_name,level7_name,level6_name,
444 level5_name,level4_name,level3_name,level2_name,
445 level1_name,
446 max_level-c_level diff_level
447 FROM
448 (
452 level1_fk_key,
449 SELECT hierarchy_id ,item_fk_key,
450 level9_fk_key,level8_fk_key,level7_fk_key,level6_fk_key,
451 level5_fk_key,level4_fk_key,level3_fk_key,level2_fk_key,
453 level9_name,level8_name,level7_name,level6_name,
454 level5_name,level4_name,level3_name,level2_name,
455 level1_name,
456 decode(level9_fk_key,NULL,0,1) +
457 decode(level8_fk_key,NULL,0,1) +
458 decode(level7_fk_key,NULL,0,1) +
459 decode(level6_fk_key,NULL,0,1) +
460 decode(level5_fk_key,NULL,0,1) +
461 decode(level4_fk_key,NULL,0,1) +
462 decode(level3_fk_key,NULL,0,1) +
463 decode(level2_fk_key,NULL,0,1) +
464 decode(level1_fk_key,NULL,0,1) c_level,--current level
465 Max(decode(level9_fk_key,NULL,0,1) +
466 decode(level8_fk_key,NULL,0,1) +
467 decode(level7_fk_key,NULL,0,1) +
468 decode(level6_fk_key,NULL,0,1) +
469 decode(level5_fk_key,NULL,0,1) +
470 decode(level4_fk_key,NULL,0,1) +
471 decode(level3_fk_key,NULL,0,1) +
472 decode(level2_fk_key,NULL,0,1) +
473 decode(level1_fk_key,NULL,0,1)) over(PARTITION BY hierarchy_id)
474 max_level--maximum level in the hierarchy
475 FROM mth_item_denorm_d
476 WHERE item_fk_key != MTH_UTIL_PKG.MTH_UA_GET_VAL
477 )
478 WHERE c_level<max_level
479 AND level9_fk_key IS NOT NULL
480 )';
481 LOOP
482 --fetch the rows in in cursor. Bulk collect
483 FETCH item_cur BULK COLLECT INTO denorm_rec.level9_fk_key,
484 denorm_rec.hierarchy_id,
485 denorm_rec.baselevel_fk_key,denorm_rec.level7_fk_key,
486 denorm_rec.level6_fk_key,
487 denorm_rec.level5_fk_key,denorm_rec.level4_fk_key,
488 denorm_rec.level3_fk_key,denorm_rec.level2_fk_key,
489 denorm_rec.level1_fk_key,
490 denorm_rec.level9_name,
491 denorm_rec.level7_name,
492 denorm_rec.level6_name,
493 denorm_rec.level5_name,
494 denorm_rec.level4_name,
495 denorm_rec.level3_name,
496 denorm_rec.level2_name,
497 denorm_rec.level1_name
498 LIMIT v_limit;
499
500 --terminating condition
501 EXIT WHEN denorm_rec.baselevel_fk_key.count =0;
502
503 --bulk update using forall
504 FORALL i IN
505 denorm_rec.baselevel_fk_key.first..denorm_rec.baselevel_fk_key.last
506 UPDATE mth_item_denorm_d
507 SET
508 level8_fk_key = denorm_rec.level9_fk_key(i),
509 level7_fk_key = denorm_rec.level7_fk_key(i),
510 level6_fk_key = denorm_rec.level6_fk_key(i),
511 level5_fk_key = denorm_rec.level5_fk_key(i),
512 level4_fk_key = denorm_rec.level4_fk_key(i),
513 level3_fk_key = denorm_rec.level3_fk_key(i),
514 level2_fk_key = denorm_rec.level2_fk_key(i),
515 level1_fk_key = denorm_rec.level1_fk_key(i),
516 level8_name = denorm_rec.level9_name(i),
517 level7_name = denorm_rec.level7_name(i),
518 level6_name = denorm_rec.level6_name(i),
519 level5_name = denorm_rec.level5_name(i),
520 level4_name = denorm_rec.level4_name(i),
521 level3_name = denorm_rec.level3_name(i),
522 level2_name = denorm_rec.level2_name(i),
523 level1_name = denorm_rec.level1_name(i)
524 WHERE
525 item_fk_key = denorm_rec.baselevel_fk_key(i)
526 AND hierarchy_id= denorm_rec.hierarchy_id(i);
527 END LOOP;
528 --close the cursor
529 CLOSE item_cur;
530
531 --handle exceptions
532 EXCEPTION
533 WHEN NO_DATA_FOUND THEN
534 RAISE_APPLICATION_ERROR (-20001,
535 'Exception has occured');
536
537 END mth_hrchy_balance_load ;
538
539
540 /* *****************************************************************************
541 * Procedure :MTH_TRUNCATE_TABLE *
542 * Description :This procedure is used to truncate the table in the *
543 * MTH Schema. Thsi can be overriden by spefying a custom schema name as well. *
544 * File Name :MTHUTILS.PLS *
545 * Visibility :Public *
546 * Parameters :Table name *
547 * Modification log : *
548 * Author Date Change *
549 * Ankit Goyal 11-Oct-2007 Initial Creation *
550 ***************************************************************************** */
551
552 PROCEDURE mth_truncate_table(p_table_name IN VARCHAR2) IS
553
554 --initialize variables here
555 v_stmt VARCHAR2(2000);
556 v_schema_name VARCHAR2(100);
557 v_status VARCHAR2(30) ;
558 v_industry VARCHAR2(30) ;
559
560 -- main body
561 BEGIN
562 IF (FND_INSTALLATION.GET_APP_INFO(
563 application_short_name => 'MTH'
564 , status => v_status
565 , industry => v_industry
569 v_stmt := 'TRUNCATE TABLE '||v_schema_name||'.'||p_table_name;
566 , oracle_schema => v_schema_name))
567 THEN
568 --Prepare the truncate statement using schema name and table name
570 EXECUTE IMMEDIATE v_stmt;
571 END IF;
572
573 END mth_truncate_table;
574
575 /* *****************************************************************************
576 * Procedure :MTH_TRUNCATE_TABLE *
577 * Description :This procedure is used to truncate the table in the *
578 * specified schema. *
579 * File Name :MTHUTILS.PLS *
580 * Visibility :Public *
581 * Parameters :Table name *
582 * Schema name *
583 * Modification log : *
584 * Author Date Change *
585 * Yong Feng July 18, 2008 Initial Creation *
586 ***************************************************************************** */
587
588 PROCEDURE mth_truncate_table(p_table_name IN VARCHAR2,
589 p_schema_name IN VARCHAR2) IS
590 --initialize variables here
591 v_stmt VARCHAR2(2000);
592
593 BEGIN
594 --Prepare the truncate statement using schema name and table name
595 v_stmt := 'TRUNCATE TABLE '||p_schema_name||'.'||p_table_name;
596 EXECUTE IMMEDIATE v_stmt;
597
598 END mth_truncate_table;
599
600 /* ****************************************************************************
601 * Procedure :MTH_TRUNCATE_TABLES *
602 * Description :This procedure is used to truncate the tables in the *
603 * list separated by comma. *
604 * File Name :MTHUTILS.PLS *
605 * Visibility :Public *
606 * Parameters :p_list_table_names: List of table names separated *
607 * by commas. *
608 * :p_schema_name: The schema name for all listed tables.*
609 * Modification log : *
610 * Author Date Change *
611 * Yong Feng Aug-07-2008 Initial Creation *
612 **************************************************************************** */
613
614 PROCEDURE mth_truncate_tables(p_list_table_names IN VARCHAR2) IS
615 -- Index to identify the beginning of a table name in the list
616 v_bidx number := 1;
617 -- Index to identify the ending of a table name in the list
618 v_eidx number;
619 v_has_schema_name BOOLEAN;
620 v_table_name varchar2(30);
621 v_user_name varchar2(30);
622 v_status VARCHAR2(30) ;
623 v_industry VARCHAR2(30) ;
624 v_mth_name varchar2(30);
625 v_schema_name varchar2(30);
626 v_list_length number;
627 cursor getSchema (p_table_name in varchar2,
628 p_owner1 in varchar2,
629 p_owner2 in varchar2) is
630 SELECT owner
631 FROM ALL_TABLES
632 WHERE table_name = p_table_name
633 AND owner in (p_owner1, p_owner2);
634
635 BEGIN
636 IF p_list_table_names IS NULL OR LENGTH(p_list_table_names) = 0 THEN
637 RETURN;
638 END IF;
639
640 v_user_name :=user;
641
642 IF (NOT FND_INSTALLATION.GET_APP_INFO(
643 application_short_name => 'MTH'
644 , status => v_status
645 , industry => v_industry
646 , oracle_schema => v_mth_name)) THEN
647 RAISE_APPLICATION_ERROR (-20001,
648 'Could not find MTH product.');
649 END IF;
650
651 v_list_length := LENGTH(p_list_table_names);
652
653 -- Parse the list of table name and truncate each table
654 v_eidx := INSTR(p_list_table_names, ',', v_bidx);
655
656 -- Handle the case where there is only one element in the list
657 IF (v_eidx = 0 AND v_bidx <= v_list_length) THEN
658 v_eidx := v_list_length + 1;
659 END IF;
660
661 WHILE (v_eidx > 0 AND v_eidx > v_bidx) LOOP
662 v_table_name := SUBSTR(p_list_table_names, v_bidx, (v_eidx - v_bidx));
663 v_table_name := UPPER(TRIM(BOTH FROM v_table_name));
664
665 -- Get the schema name for that table
666 IF v_table_name IS NOT NULL AND LENGTH(v_table_name) > 0 THEN
667 OPEN getSchema ( v_table_name, v_user_name, v_mth_name);
668 FETCH getSchema INTO v_schema_name;
669 IF (getSchema%FOUND) THEN
670 MTH_TRUNCATE_TABLE(v_table_name, v_schema_name);
671 CLOSE getSchema;
672 ELSE
673 CLOSE getSchema;
674 RAISE_APPLICATION_ERROR (-20001,
675 'Could not find table ' || v_table_name || ' in either ' || v_user_name || ' or ' || v_mth_name || '.');
676 END IF;
677 END IF;
678 v_bidx := v_eidx + 1;
679 v_eidx := INSTR(p_list_table_names, ',', v_bidx);
680 -- Handle the case for end of the list
681 IF (v_eidx = 0 AND v_bidx <= v_list_length) THEN
682 v_eidx := v_list_length + 1;
683 END IF;
684 END LOOP;
685 END mth_truncate_tables;
686
687
688
689 /* ****************************************************************************
690 * Procedure :MTH_TRUNCATE_MV_LOGS *
691 * Description :This procedure is used to truncate the Materialized *
692 * View log created on the tables *
693 * list separated by comma. *
694 * File Name :MTHUTILS.PLS *
695 * Visibility :Public *
696 * Parameters :p_list_table_names: List of table names separated *
697 * by commas. *
698 * Modification log : *
699 * Author Date Change *
700 * Yong Feng Aug-07-2008 Initial Creation *
701 **************************************************************************** */
702
703 PROCEDURE MTH_TRUNCATE_MV_LOGS (p_list_table_names IN VARCHAR2) IS
704 -- Index to identify the beginning of a table name in the list
705 v_bidx number := 1;
706 -- Index to identify the ending of a table name in the list
707 v_eidx number;
708 v_has_schema_name BOOLEAN;
709 v_table_name varchar2(30);
710 v_user_name varchar2(30);
711 v_status VARCHAR2(30) ;
712 v_industry VARCHAR2(30) ;
713 v_mth_name varchar2(30);
714 v_log_owner varchar2(30);
715 v_log_table varchar2(30);
716 v_list_length number;
717 cursor getLogTableSchema (p_table_name in varchar2,
718 p_owner1 in varchar2,
719 p_owner2 in varchar2) is
720 SELECT log_owner, log_table
721 FROM ALL_SNAPSHOT_LOGS
722 WHERE master = p_table_name
723 AND log_owner in (p_owner1, p_owner2);
724
725 BEGIN
726 IF p_list_table_names IS NULL OR LENGTH(p_list_table_names) = 0 THEN
727 RETURN;
728 END IF;
729
730 v_user_name :=user;
731
732 IF (NOT FND_INSTALLATION.GET_APP_INFO(
733 application_short_name => 'MTH'
734 , status => v_status
735 , industry => v_industry
736 , oracle_schema => v_mth_name)) THEN
737 RAISE_APPLICATION_ERROR (-20001,
738 'Could not find MTH product.');
739 END IF;
740
741 v_list_length := LENGTH(p_list_table_names);
742
743 -- Parse the list of table name and truncate each table
744 v_eidx := INSTR(p_list_table_names, ',', v_bidx);
745
746 -- Handle the case where there is only one element in the list
747 IF (v_eidx = 0 AND v_bidx <= v_list_length) THEN
748 v_eidx := v_list_length + 1;
749 END IF;
750
751 WHILE (v_eidx > 0 AND v_eidx > v_bidx) LOOP
752 v_table_name := SUBSTR(p_list_table_names, v_bidx, (v_eidx - v_bidx));
753 v_table_name := UPPER(TRIM(BOTH FROM v_table_name));
754
755 -- Get the mv log name and schema name for that table
759 IF (getLogTableSchema%FOUND) THEN
756 IF v_table_name IS NOT NULL AND LENGTH(v_table_name) > 0 THEN
757 OPEN getLogTableSchema ( v_table_name, v_user_name, v_mth_name);
758 FETCH getLogTableSchema INTO v_log_owner, v_log_table;
760 MTH_TRUNCATE_TABLE(v_log_table, v_log_owner);
761 CLOSE getLogTableSchema ;
762 ELSE
763 CLOSE getLogTableSchema;
764 RAISE_APPLICATION_ERROR (-20001,
765 'Could not find Materialized View Log on table ' || v_table_name || ' in either ' || v_user_name || ' or ' || v_mth_name || '.');
766 END IF;
767 END IF;
768 v_bidx := v_eidx + 1;
769 v_eidx := INSTR(p_list_table_names, ',', v_bidx);
770 -- Handle the case for end of the list
771 IF (v_eidx = 0 AND v_bidx <= v_list_length) THEN
772 v_eidx := v_list_length + 1;
773 END IF;
774 END LOOP;
775 END MTH_TRUNCATE_MV_LOGS;
776
777
778
779
780 /* *****************************************************************************
781 * Function :MTH_UA_GET_VAL *
782 * Description : This procedure is used to return the lookup code for *
783 * the unasssigned *
784 * File Name :MTHUTILS.PLS *
785 * Visibility :Public *
786 * Parameters :None *
787 * Return Value :v_lookup_code : Unassigned lookup code value *
788 * Modification log : *
789 * Author Date Change *
790 * Ankit Goyal 11-Oct-2007 Initial Creation *
791 ***************************************************************************** */
792
793 Function mth_ua_get_val RETURN NUMBER IS
794 v_lookup_code varchar2(30);
795 BEGIN
796 --NULL
797 SELECT lookup_code INTO v_lookup_code FROM FND_LOOKUP_VALUES WHERE
798 lookup_type='MTH_UNASSIGNED_L' AND language=userenv('LANG');
799
800 RETURN(to_number(v_lookup_code));
801
802 --handle exceptions
803 EXCEPTION
804 WHEN NO_DATA_FOUND THEN
805 RAISE_APPLICATION_ERROR (-20001,
806 'Exception has occured');
807
808 END mth_ua_get_val;
809
810
811
812 /* *****************************************************************************
813 * Function :MTH_UA_GET_MEANING *
814 * Description :This procedure is used to return the lookup meaning *
815 * for the unasssigned *
816 * File Name :MTHUTILS.PLS *
817 * Visibility :Public *
818 * Parameters :None *
819 * Return Value :v_lookup_code : Unassigned lookup code value *
820 * Modification log : *
821 * Author Date Change *
822 * Ankit Goyal 23-Oct-2007 Initial Creation *
823 ***************************************************************************** */
824
825 Function mth_ua_get_meaning RETURN VARCHAR2 IS
826 v_lookup_meaning varchar2(80);
827 BEGIN
828 --NULL
829 SELECT meaning INTO v_lookup_meaning FROM FND_LOOKUP_VALUES WHERE
830 lookup_type='MTH_UNASSIGNED_L' AND language=userenv('LANG');
831
832 RETURN(v_lookup_meaning);
833
834 --handle exceptions
835 EXCEPTION
836 WHEN NO_DATA_FOUND THEN
837 RAISE_APPLICATION_ERROR (-20001,
838 'Exception has occured');
839
840 END mth_ua_get_meaning;
841
842 /* ****************************************************************************
843 * Procedure :request_lock *
844 * Description :This procedure is used to request an exclusive *
845 * lock with p_key_table as the key using rdbms_lock package. The current *
846 * will wait indifinitely if the lock was held by others until the release *
847 * of the lock. *
848 * File Name :MTHUTILB.PLS *
849 * Visibility :Private *
850 * Parameters *
851 * p_key_table : The name used to request an exclusive lock. *
852 * p_retval : The return value of the operation: *
853 * 0 - Success *
854 * 1 - Timeout *
855 * 2 - Deadlock *
856 * 3 - Parameter Error *
857 * 4 - Already owned *
858 * 5 - Illegal Lock Handle *
859 * Modification log : *
860 * Author Date Change *
861 * Yong Feng 17-Oct-2007 Initial Creation *
862 **************************************************************************** */
863 PROCEDURE request_lock(p_key_table IN VARCHAR2, p_retval OUT NOCOPY INTEGER)
864 IS
865 v_lockhandle VARCHAR2(200);
866 BEGIN
867 dbms_lock.allocate_unique(p_key_table, v_lockhandle);
868 p_retval := dbms_lock.request(v_lockhandle, dbms_lock.x_mode);
869 END request_lock;
870
871 /* ****************************************************************************
872 * Procedure :generate_new_time_range *
873 * Description :This procedure is used to generate a time range *
874 * starting from the last end date up to current time, sysdate, using *
875 * the p_key_table name as the key to look up the entry in mth_run_log *
876 * table. If the entry does not exist, create one and set the time range *
877 * to a hard-coded past time to current time. *
878 * File Name :MTHUTILB.PLS *
879 * Visibility :Public *
880 * Parameters *
881 * p_key_table : Name to uniquely identify one entry in the *
882 * mth_run_log table. *
883 * p_start_date : An output value that specifies the start time *
884 * of the new time period. *
885 * p_end_date : An output value that specifies the end time *
886 * of the new time period. *
887 * p_exclusive_lock : Specify whether it needs to request an exclusive *
888 * lock using p_key_table as the key so that only *
889 * one procedure will be running at one point of time. *
890 * If the value is 1, then it will run in exclusive *
891 * mode. The lock will be released when the *
892 * transaction is either committed or rollbacked. *
893 * Modification log : *
894 * Author Date Change *
895 * Yong Feng 17-Oct-2007 Initial Creation *
896 **************************************************************************** */
897 PROCEDURE generate_new_time_range(p_key_table IN VARCHAR2,
898 p_start_date OUT NOCOPY DATE,
899 p_end_date OUT NOCOPY DATE,
900 p_exclusive_lock IN NUMBER DEFAULT 1)
901 IS
902 v_from_date mth_run_log.from_date%TYPE;
903 v_to_date mth_run_log.to_date%TYPE;
904 v_is_new_entry BOOLEAN := FALSE;
905 v_default_start_date DATE := to_date('1950', 'YYYY');
906 v_sysdate DATE := sysdate;
907 v_retval number := 0;
908
909 CURSOR c_lookup IS
910 SELECT to_date
911 FROM mth_run_log
912 WHERE fact_table = p_key_table and rownum=1;
913 BEGIN
914 -- 1. Validate the p_fact_table input value.
915 IF (p_key_table is not null) THEN
916
917 -- 2. Check to see if we need to request an exclusive lock.
918 -- It will wait infinitively if it cannot get the lock.
919 -- Do not request lock any more for the mappinging. Request one
920 -- for the process flow instead.
921 --IF p_exclusive_lock = 1 THEN
922 -- request_lock(p_key_table, v_retval);
923 --END IF;
924
925 --IF v_retval = 0 THEN
926
927 -- 3. Do the look up
928 open c_lookup;
929 fetch c_lookup into v_to_date;
930 IF c_lookup%NOTFOUND THEN
931 v_is_new_entry := TRUE;
932 END IF;
933 close c_lookup;
934
935 v_from_date := v_to_date;
936 v_to_date := v_sysdate;
937
938 -- 4. Create a new entry if not exist. Otherwise, update the entry
939 IF v_is_new_entry THEN
940 v_from_date := v_default_start_date;
941 INSERT INTO mth_run_log
942 (fact_table, ebs_organization_id, ebs_organization_code, from_date,
943 to_date, source, db_global_name, creation_date, last_update_date,
944 creation_system_id, last_update_system_id, plant_start_date)
945 VALUES
946 (p_key_table, -1, '-1', v_from_date,
947 v_to_date, -1, '-99999', v_sysdate, v_sysdate,
948 -1, -1, v_default_start_date);
949 ELSE
950 UPDATE mth_run_log
951 SET TO_DATE = v_to_date,
952 FROM_DATE = v_from_date,
953 LAST_UPDATE_DATE = v_sysdate
954 WHERE
955 fact_table = p_key_table;
956 END IF;
957
958 -- 5. Set the output variables
959 p_start_date := v_from_date;
960 p_end_date := v_to_date;
961 --END IF;
962 END IF;
963 END generate_new_time_range;
964
965
966
967 /* *****************************************************************************
968 * Function :GET_PROFILE_VAL *
969 * Description :This function is used to retrive the value of the *
970 * profile for the profile name provided by the user *
971 * File Name :MTHSOURCEPATCHS.PLS *
972 * Visibility :Public *
973 * Return : V_PROFILE_NAME - Global name of the source DB *
974 * Modification log : *
975 * Author Date Change *
976 * Ankit Goyal 29-Oct-2007 Initial Creation *
977 ******************************************************************************/
978 FUNCTION get_profile_val(p_profile_name IN VARCHAR2) RETURN VARCHAR2
979 IS
980 --local variable declation
981 v_profile_value varchar2(120);
982
983 v_uid number := -1;
984 v_rid number := -1;
985 v_applid number := 1;
986
987 BEGIN
988
989 --Initialize the session for default values -1.
990 /* fnd_global.apps_initialize(v_uid, v_rid, v_applid); */
991
992 --select the value of the profile into the local variable.
993 select fnd_profile.value(p_profile_name) into v_profile_value from dual;
994
995 --Return the value.
996 RETURN(v_profile_value);
997
998 --End the function
999 END;
1000
1001 /* ****************************************************************************
1002 * Function :Get_UDA_Eq_HId *
1003 * Description :This function is used to retrive the hierarchy id of *
1004 * the UDA Equipment profile *
1005 * File Name :MTHUTILS.PLS *
1006 * Visibility :Public *
1007 * Return :Hierarchy id for the equipment UDA profile *
1008 * Modification log : *
1009 * Author Date Change *
1010 * Vivek 18-Jan-2008 Initial Creation *
1011 ******************************************************************************/
1012
1013 FUNCTION Get_UDA_Eq_HId RETURN VARCHAR IS
1014
1015 v_profile VARCHAR2(6);
1016 v_pos NUMBER;
1017 v_hId VARCHAR2(3);
1018
1019 BEGIN
1020
1021 -- Get the profile value, it will be of form h_id:level_no
1022 v_profile := FND_PROFILE.VALUE('MTH_UDA_EQUIPMENT_PROFILE');
1023
1024 -- based on the position of :, retrieve h_id
1025 v_pos := INSTR(v_profile,':');
1026 v_hId := SUBSTR(v_profile,1,v_pos-1);
1027
1028 RETURN v_hId;
1029
1030 EXCEPTION
1031 WHEN OTHERS THEN NULL;
1032 END;
1033
1034 /* ****************************************************************************
1038 * File Name :MTHUTILS.PLS *
1035 * Function :Get_UDA_Eq_LNo *
1036 * Description :This function is used to retrive the Level Number of *
1037 * the UDA Equipment profile *
1039 * Visibility :Public *
1040 * Return :Level Number for the equipment UDA profile *
1041 * Modification log : *
1042 * Author Date Change *
1043 * Vivek 18-Jan-2008 Initial Creation *
1044 ******************************************************************************/
1045
1046 FUNCTION Get_UDA_Eq_LNo RETURN VARCHAR IS
1047
1048 v_profile VARCHAR2(6);
1049 v_pos NUMBER;
1050 v_lNo VARCHAR2(3);
1051 v_length NUMBER;
1052
1053 BEGIN
1054
1055 -- Get the profile value, it will be of form h_id:level_no
1056 v_profile := FND_PROFILE.VALUE('MTH_UDA_EQUIPMENT_PROFILE');
1057
1058 -- based on the position of :, retrieve h_id
1059 v_pos := INSTR(v_profile,':');
1060 v_length := LENGTH(v_profile);
1061 v_lNo := SUBSTR(v_profile,v_pos+1,v_length);
1062
1063 RETURN v_lNo;
1064
1065 EXCEPTION
1066 WHEN OTHERS THEN NULL;
1067 END;
1068
1069 /* ****************************************************************************
1070 * Procedure :REFRESH_MV *
1071 * Description :This procedure is used to call DBMS_MVIEW.REFRESH *
1072 * procedure to refresh MVs. *
1073 * File Name :MTHUTILB.PLS *
1074 * Visibility :Public *
1075 * Parameters *
1076 * list : Comma-separated list of materialized views that *
1077 * you want to refresh. *
1078 * method :A string of refresh methods indicating how to *
1079 * refresh the listed materialized views. *
1080 * - An f indicates fast refresh *
1081 * - ? indicates force refresh *
1082 * - C or c indicates complete refresh *
1083 * - A or a indicates always refresh. A and C are *
1084 * equivalent. *
1085 * rollback_seg :Name of the materialized view site rollback segment *
1086 * to use while refreshing materialized views. *
1087 * push_deferred_rpc : Used by updatable materialized views only. *
1088 * refresh_after_errors : *
1089 * purge_option : *
1090 * parallelism : 0 specifies serial propagation *
1091 * heap_size : *
1092 * atomic_refresh : *
1093 * Modification log : *
1094 * Author Date Change *
1095 * Yong Feng 11-July-2008 Initial Creation *
1096 **************************************************************************** */
1097 PROCEDURE REFRESH_MV(
1098 p_list IN VARCHAR2,
1099 p_method IN VARCHAR2 := NULL,
1100 p_rollback_seg IN VARCHAR2 := NULL,
1101 p_push_deferred_rpc IN BOOLEAN := true,
1102 p_refresh_after_errors IN BOOLEAN := false,
1103 p_purge_option IN BINARY_INTEGER := 1,
1104 p_parallelism IN BINARY_INTEGER := 0,
1105 p_heap_size IN BINARY_INTEGER := 0,
1106 p_atomic_refresh IN BOOLEAN := true
1107 )
1108 IS
1109 BEGIN
1110 DBMS_MVIEW.REFRESH(p_list, p_method, p_rollback_seg, p_push_deferred_rpc,
1111 p_refresh_after_errors, p_purge_option,
1112 p_parallelism, p_heap_size, p_atomic_refresh);
1113 END REFRESH_MV;
1114
1115
1116 /* ****************************************************************************
1117 * Procedure :REFRESH_ONE_MV *
1118 * Description :This procedure is used to call refresh one MV. *
1119 * File Name :MTHUTILB.PLS *
1120 * Visibility :Public *
1121 * Parameters *
1122 * p_mv_name : Name of the materialized view to be refreshed. *
1123 * p_method :A string of refresh methods indicating how to *
1124 * refresh the listed materialized views. *
1125 * - An f indicates fast refresh *
1126 * - ? indicates force refresh *
1127 * - C or c indicates complete refresh *
1128 * - A or a indicates always refresh. A and C are *
1129 * equivalent. *
1130 * p_rollback_seg :Name of the materialized view site rollback segment *
1131 * to use while refreshing materialized views. *
1132 * p_refresh_mode :A string of refresh mode: *
1133 * - C , c or NULL indicates complete refresh. *
1134 * - R or r indicates resume refresh that has been *
1135 * started earlier. The MV will be refreshed if the *
1136 * refresh date is earlier than the date stored in *
1137 * to_date column in MTH_RUN_LOG for MTH_ALL_MVS entry. *
1138 * p_push_deferred_rpc : Used by updatable materialized views only. *
1139 * Modification log : *
1140 * Author Date Change *
1144 PROCEDURE REFRESH_ONE_MV(
1141 * Yong Feng 19-Aug-2008 Initial Creation *
1142 **************************************************************************** */
1143
1145 p_mv_name IN VARCHAR2,
1146 p_method IN VARCHAR2 := NULL,
1147 p_rollback_seg IN VARCHAR2 := NULL,
1148 p_refresh_mode IN VARCHAR2 := NULL
1149 )
1150 IS
1151 v_bneed_refresh BOOLEAN := FALSE;
1152 v_last_refresh_date DATE;
1153 v_refresh_date_required DATE;
1154 v_unassigned_string VARCHAR2(20) := to_char(mth_util_pkg.mth_ua_get_val);
1155 v_mv_name varchar2(30);
1156 cursor getRefreshDate (p_mv_name in varchar2) is
1157 SELECT last_refresh_date
1158 FROM user_mviews
1159 WHERE mview_name = p_mv_name;
1160 BEGIN
1161 v_mv_name := UPPER(TRIM(p_mv_name));
1162 IF v_mv_name IS NULL OR LENGTH(v_mv_name) = 0 THEN
1163 RETURN;
1164 END IF;
1165
1166 -- Check whether the MV needs to be refreshed
1167 IF (p_refresh_mode IS NULL OR upper(p_refresh_mode) <> 'R') THEN
1168 -- Need to refresh in mode C
1169 v_bneed_refresh := TRUE;
1170 ELSE
1171 -- It is a resume operation
1172 -- First, find the refresh date in MTH_RUN_LOG in resume case
1173 SELECT max(to_date) into v_refresh_date_required
1174 FROM mth_run_log
1175 WHERE fact_table = 'MTH_ALL_MVS' AND db_global_name = v_unassigned_string;
1176 IF (v_refresh_date_required IS NULL) THEN
1177 -- Refresh the MV if there is no entry found in MTH_RUN_LOG
1178 v_bneed_refresh := TRUE;
1179 ELSE
1180 -- First get the last refresh date of the MV
1181 -- Then, compare the last refresh date of the MV with the date from the MTH_RUN_LOG
1182 -- to decide whether the MV needs to be refreshed
1183 OPEN getRefreshDate ( v_mv_name);
1184 FETCH getRefreshDate INTO v_last_refresh_date;
1185 IF (getRefreshDate%FOUND) THEN
1186 v_bneed_refresh := (v_last_refresh_date IS NULL OR v_last_refresh_date <= v_refresh_date_required);
1187 CLOSE getRefreshDate ;
1188 ELSE
1189 CLOSE getRefreshDate ;
1190 RAISE_APPLICATION_ERROR (-20001,
1191 'Could not find Materialized View ' || v_mv_name || '.');
1192 END IF;
1193 END IF;
1194 END IF;
1195
1196 IF v_bneed_refresh THEN
1197 DBMS_MVIEW.REFRESH(v_mv_name, p_method, p_rollback_seg);
1198 END IF;
1199 END REFRESH_ONE_MV;
1200
1201 /* *****************************************************************************
1202 * Procedure :PUT_EQUIP_DENORM_LEVEL_NUM *
1203 * Description :This procedure is used to insert the level_num column *
1204 * in the mth_equipment_denorm_d table *
1205 * File Name :MTHUTILB.PLS *
1206 * Visibility :Private *
1207 * Modification log : *
1208 * Author Date Change *
1209 * shanthi donthu 16-Jul-2008 Initial Creation *
1210 ***************************************************************************** */
1211
1212 PROCEDURE PUT_EQUIP_DENORM_LEVEL_NUM
1213 IS
1214 BEGIN
1215 UPDATE MTH_EQUIPMENT_DENORM_D SET LEVEL_NUM = (
1216 CASE WHEN EQUIPMENT_FK_KEY IS NOT NULL THEN 10
1217 ELSE CASE WHEN LEVEL9_LEVEL_KEY IS NOT NULL THEN 9
1218 ELSE CASE WHEN LEVEL8_LEVEL_KEY IS NOT NULL THEN 8
1219 ELSE CASE WHEN LEVEL7_LEVEL_KEY IS NOT NULL THEN 7
1220 ELSE CASE WHEN LEVEL6_LEVEL_KEY IS NOT NULL THEN 6
1221 ELSE CASE WHEN LEVEL5_LEVEL_KEY IS NOT NULL THEN 5
1222 ELSE CASE WHEN LEVEL4_LEVEL_KEY IS NOT NULL THEN 4
1223 ELSE CASE WHEN LEVEL3_LEVEL_KEY IS NOT NULL THEN 3
1224 ELSE CASE WHEN LEVEL2_LEVEL_KEY IS NOT NULL THEN 2
1225 ELSE CASE WHEN LEVEL1_LEVEL_KEY IS NOT NULL THEN 1
1226 END
1227 END
1228 END
1229 END
1230 END
1231 END
1232 END
1233 END
1234 END
1235 END )
1236 WHERE LEVEL_NUM IS NULL;
1237
1238 END PUT_EQUIP_DENORM_LEVEL_NUM;
1239
1240 /* *****************************************************************************
1241 * Procedure :update_equip_hrchy_gid *
1242 * Description :This procedue is used for updating the group_id column in *
1243 * the mth_equip_hierarchy table. The group id will be used to determine the *
1244 * sequence in which a particular record will be processed in the equipment SCD *
1245 * logic. The oldest relationships will have the lowest group id =1 and the new *
1246 * relationships will have higher group id. All the catch all relationships i.e.*
1247 * the relationship with parent = -99999 and effective date = 1-Jan-1900 will *
1248 * have group id defaulted to 1 inside the MTH_EQUIP_HRCHY_UA_ALL_MAP map. *
1249 * File Name :MTHUTILB.PLS *
1250 * Visibility :Public *
1251 * Parameters : none *
1252 * Modification log : *
1253 * Author Date Change *
1254 * Ankit Goyal 26-Aug-2008 Initial Creation *
1255 ***************************************************************************** */
1256 PROCEDURE update_equip_hrchy_gid
1257 IS
1258 /*variable to track # of conlficting rows*/
1259 l_max_gid NUMBER := 0;
1260 /*variable to get current maximum group id*/
1261 l_new_rows NUMBER := 0;
1262 v_new_ed DATE ;
1263 /*Variable to track the numnber of new rows */
1264 /*This cursor will fetch the number of rows that are in conflict. The rows
1268 those rows and then logic in the program will manipulate those rows
1265 are said to be in conflict when the incoming new rows have effective date
1266 less than the effective date of the current rows. This implies that the
1267 existing rows need to be processed after the new rows. This cursor fetches
1269 and increase theor group id.*/
1270 CURSOR cr_conflict_rows
1271 IS
1272 SELECT old_rows.hierarchy_id,
1273 old_rows.level_num ,
1274 old_rows.group_id ,
1275 old_rows.level_fk_key ,
1276 old_rows.effective_date
1277 FROM
1278 (SELECT hierarchy_id ,
1279 level_fk_key ,
1280 level_num ,
1281 effective_date effective_date,
1282 group_id
1283 FROM mth_equip_hierarchy
1284 WHERE group_id > 1
1285 /*group_id==1 are catch all rows. */
1286 GROUP BY hierarchy_id,
1287 level_fk_key ,
1288 level_num ,
1289 group_id ,
1290 effective_date
1291 ) old_rows ,
1292 (SELECT hierarchy_id,
1293 level_fk_key ,
1294 level_num ,
1295 effective_date ,
1296 parent_fk_key
1297 FROM mth_equip_hierarchy
1298 WHERE group_id IS NULL
1299 ) new_rows
1300 /*new relationships with group id as null */
1301 WHERE old_rows.hierarchy_id = new_rows.hierarchy_id
1302 AND old_rows.level_fk_key = new_rows.level_fk_key
1303 AND old_rows.level_num = new_rows.level_num
1304 AND old_rows.effective_date > new_rows.effective_date;
1305 /*The effective date comparison will tell us if there are conflits */
1306 /*This cursor fetches the row among the conflict rows with the minimum
1307 effective date.This tells us all the rows that will need to be updated so
1308 that they are processed in the correct groups*/
1309 CURSOR cr_aggr_conflict_rows(p_effective_date IN date,p_hierarchy_id IN number,p_level_fk_key IN number,p_level_num IN number)
1310 IS
1311 SELECT new_ed FROM (
1312 SELECT old_rows.hierarchy_id ,
1313 old_rows.level_num ,
1314 MIN(old_rows.group_id) group_id,
1315 /*to skip group by */
1316 old_rows.level_fk_key ,
1317 MIN(old_rows.effective_date) effective_date,
1318 /*effecitve date of the old row */
1319 new_rows.effective_date new_ed
1320 /*effecitve date of the new row */
1321 FROM
1322 (SELECT hierarchy_id ,
1323 level_fk_key ,
1324 level_num ,
1325 effective_date effective_date,
1326 group_id
1327 FROM mth_equip_hierarchy
1328 WHERE group_id > 1
1329 /*group_id==1 are catch all rows. */
1330 ) old_rows ,
1331 (SELECT hierarchy_id,
1332 level_fk_key ,
1333 level_num ,
1334 effective_date ,
1335 parent_fk_key
1336 FROM mth_equip_hierarchy
1337 WHERE group_id IS NULL
1338 ) new_rows
1339 /*new relationships with group id as null */
1340 WHERE old_rows.hierarchy_id = new_rows.hierarchy_id
1341 AND old_rows.level_fk_key = new_rows.level_fk_key
1342 AND old_rows.level_num = new_rows.level_num
1343 AND old_rows.effective_date > new_rows.effective_date
1344 GROUP BY old_rows.hierarchy_id,
1345 old_rows.level_num ,
1346 old_rows.level_fk_key ,
1347 new_rows.effective_date) c_rows WHERE
1348 c_rows.effective_date =p_effective_date
1349 AND c_rows.hierarchy_id = p_hierarchy_id
1350 AND c_rows.level_fk_key = p_level_fk_key
1351 AND c_rows.level_num = p_level_num ;
1352 /*This cursor will fetch all the new rows for which the group id
1353 assignments have not been done.*/
1354 CURSOR cr_new_rows
1355 IS
1356 SELECT effective_date,
1357 hierarchy_id ,
1358 level_fk_key ,
1359 level_num
1360 FROM mth_equip_hierarchy
1361 WHERE group_id IS NULL;
1362 BEGIN
1363 FOR l_rows IN cr_conflict_rows
1364 LOOP
1365 /*All rows in conflict */
1366 OPEN cr_aggr_conflict_rows(l_rows.effective_date,l_rows.hierarchy_id, l_rows.level_fk_key,l_rows.level_num);
1367 FETCH cr_aggr_conflict_rows into v_new_ed ;
1368 /*All aggregated rows in conflict */
1369 /*This part of the logic deals with updating the group id
1370 of the new rows. */
1371 IF(cr_aggr_conflict_rows%FOUND) then
1372 /*set the group id of the new row equal to the group id of the old
1373 row which matches the effective date*/
1374 UPDATE mth_equip_hierarchy
1375 SET group_id = l_rows.group_id
1376 WHERE effective_date = v_new_ed
1377 /*This is the determining condition */
1378 AND hierarchy_id = l_rows.hierarchy_id
1379 AND level_fk_key = l_rows.level_fk_key
1380 AND level_num = l_rows.level_num;
1381 END IF;
1382 CLOSE cr_aggr_conflict_rows;
1383 /*Update the odl rows and increment the group id by 1 */
1384 UPDATE mth_equip_hierarchy
1385 SET group_id = l_rows.group_id + 1
1386 WHERE effective_date = l_rows.effective_date
1387 AND hierarchy_id = l_rows.hierarchy_id
1388 AND level_fk_key = l_rows.level_fk_key
1389 AND level_num = l_rows.level_num;
1390 END LOOP;
1391 /*This part of the logic will update any rows which did not cause a conflict
1392 with the old rows. This logic is necessary as the data can contain both the
1393 conflit rows and non conflict rows*/
1394 /*get the number of new rows remaining to be updated. */
1395 SELECT COUNT(* )
1396 INTO l_new_rows
1397 FROM mth_equip_hierarchy
1401 FOR new_rows IN cr_new_rows
1398 WHERE group_id IS NULL;
1399 IF l_new_rows > 0 THEN
1400 /*if new rows found */
1402 LOOP
1403 SELECT MAX(group_id)
1404 INTO l_max_gid
1405 FROM mth_equip_hierarchy
1406 WHERE hierarchy_id = new_rows.hierarchy_id
1407 AND level_fk_key = new_rows.level_fk_key
1408 AND level_num = new_rows.level_num;
1409 /*update the new rows gorup_id column and set it = group_id of old row + 1*/
1410 UPDATE mth_equip_hierarchy
1411 SET group_id = l_max_gid + 1
1412 WHERE hierarchy_id = new_rows.hierarchy_id
1413 AND level_fk_key = new_rows.level_fk_key
1414 AND level_num = new_rows.level_num
1415 AND effective_date = new_rows.effective_date;
1416 END LOOP;
1417 END IF;
1418 --handle exceptions
1419 EXCEPTION
1420 WHEN NO_DATA_FOUND THEN
1421 RAISE_APPLICATION_ERROR (-20001,
1422 'Exception has occured');
1423
1424 END update_equip_hrchy_gid;
1425
1426 /* *****************************************************************************
1427 * Function :get_min_max_gid *
1428 * Description :This finction returns the minimum or maximum group id in the *
1429 * Equipment hierarchy table. *
1430 * File Name :MTHUTILB.PLS *
1431 * Visibility :Public *
1432 * Parameters : Mode Number. Mode= 1 Minimum, Mode =2 Maximum *
1433 * Modification log : *
1434 * Author Date Change *
1435 * Ankit Goyal 26-Aug-2008 Initial Creation *
1436 ***************************************************************************** */
1437
1438 FUNCTION get_min_max_gid
1439 (minmax IN NUMBER)
1440 RETURN NUMBER
1441 IS
1442 v_minmax NUMBER := 0;
1443 BEGIN
1444 IF minmax = 1 THEN
1445 SELECT MIN(group_id)
1446 INTO v_minmax
1447 FROM mth_equip_hierarchy;
1448 ELSE
1449 SELECT MAX(group_id)
1450 INTO v_minmax
1451 FROM mth_equip_hierarchy;
1452 END IF;
1453
1454 RETURN v_minmax;
1455 END get_min_max_gid;
1456
1457 /* *****************************************************************************
1458 * Procedure :switch_column_default_value *
1459 * Description :This procedure will determine the current value of the *
1460 * processing_flag of the table, issue an alter table statement to switch *
1461 * the default values to another (1 to 2, or 2 to 1,) and return the *
1462 * current value. If there are no data in the table, do nothing and return *
1463 * 0. *
1464 * File Name :MTHUTILB.PLS *
1465 * Visibility :Public *
1466 * Parameters : *
1467 * p_table_name: table name *
1468 * p_current_processing_flag: the current value of processing_flag *
1469 * It could be 1, or 2 for normal case. *
1470 * If it is 0, then no data is available *
1471 * the table. So no process is needed. *
1472 * Modification log : *
1473 * Author Date Change: Yong Feng 10/2/08 Initial creation *
1474 ***************************************************************************** */
1475
1476 PROCEDURE switch_column_default_value (
1477 p_table_name IN VARCHAR2,
1478 p_current_processing_flag OUT NOCOPY NUMBER)
1479 IS
1480 v_stmt varchar2(500);
1481 v_current_value NUMBER;
1482 v_new_value NUMBER;
1483 e_wrong_value EXCEPTION;
1484 v_schema_name VARCHAR2(100);
1485 v_status VARCHAR2(30) ;
1486 v_industry VARCHAR2(30) ;
1487 BEGIN
1488 IF p_table_name IS NULL OR LENGTH(p_table_name) = 0 THEN
1489 RETURN;
1490 END IF;
1491
1492 IF (NOT FND_INSTALLATION.GET_APP_INFO(
1493 application_short_name => 'MTH'
1494 , status => v_status
1495 , industry => v_industry
1496 , oracle_schema => v_schema_name)) THEN
1497 RAISE_APPLICATION_ERROR (-20002,
1498 'Could not find MTH product.');
1499 END IF;
1500
1501 v_stmt := 'SELECT processing_flag FROM ' ||
1502 v_schema_name || '.' || p_table_name ||
1503 ' WHERE rownum < 2';
1504 EXECUTE IMMEDIATE v_stmt INTO v_current_value;
1505
1506 IF v_current_value = 1 THEN
1507 v_new_value := 2;
1508 ELSIF v_current_value = 2 THEN
1509 v_new_value := 1;
1510 ELSE RAISE e_wrong_value;
1511 END IF;
1512
1513 v_stmt := 'ALTER TABLE ' || v_schema_name || '.' || p_table_name ||
1514 ' MODIFY PROCESSING_FLAG DEFAULT ' || v_new_value;
1515 EXECUTE IMMEDIATE v_stmt;
1516 p_current_processing_flag := v_current_value;
1517 EXCEPTION
1518 WHEN NO_DATA_FOUND THEN
1519 p_current_processing_flag := 0;
1520 WHEN e_wrong_value THEN
1521 RAISE_APPLICATION_ERROR (-20001, 'The table ' || p_table_name ||
1522 ' contains wrong value ' || v_current_value ||
1523 ' in column PROCESSING_FLAG.');
1524 END switch_column_default_value;
1525
1526
1527 /* *****************************************************************************
1528 * Procedure :truncate_table_partition *
1529 * Description :This procedure will truncate the partition corresponding *
1530 * to the value of p_current_processing_flag. *
1531 * File Name :MTHUTILB.PLS *
1532 * Visibility :Public *
1533 * Parameters : *
1534 * p_table_name: table name *
1535 * p_current_processing_flag: Used to determine the partition to be *
1536 * truncated. Truncate p1 if the value is 1; truncate p2 if 2. *
1537 * Modification log : *
1538 * Author Date Change: Yong Feng 10/2/08 Initial creation *
1539 ***************************************************************************** */
1540
1541 PROCEDURE truncate_table_partition (
1542 p_table_name IN VARCHAR2,
1543 p_current_processing_flag IN NUMBER)
1544 IS
1545 v_stmt VARCHAR2(500);
1546 v_schema_name VARCHAR2(100);
1547 v_status VARCHAR2(30) ;
1548 v_industry VARCHAR2(30) ;
1549 v_partition_name VARCHAR2(30) ;
1550
1551 BEGIN
1552 IF (NOT FND_INSTALLATION.GET_APP_INFO(
1553 application_short_name => 'MTH'
1554 , status => v_status
1555 , industry => v_industry
1556 , oracle_schema => v_schema_name)) THEN
1557 RAISE_APPLICATION_ERROR (-20002,
1558 'Could not find MTH product.');
1559 END IF;
1560
1561 IF (p_current_processing_flag = 1) THEN
1562 v_partition_name := 'P1';
1563 ELSIF p_current_processing_flag = 2 THEN
1564 v_partition_name := 'P2';
1565 ELSE
1566 RAISE_APPLICATION_ERROR (-20003, 'The table ' || p_table_name ||
1567 ' cannot have the value ' || p_current_processing_flag ||
1568 ' in column PROCESSING_FLAG.');
1569 END IF;
1570 v_stmt := 'ALTER TABLE '||v_schema_name||'.'||p_table_name ||
1571 ' TRUNCATE PARTITION ' || v_partition_name;
1572 EXECUTE IMMEDIATE v_stmt;
1573 END truncate_table_partition;
1574
1575
1576 END mth_util_pkg;
[Home] [Help]