1 PACKAGE BODY mth_util_pkg AS
2 /*$Header: mthutilb.pls 120.36.12020000.5 2012/11/29 12:53:44 sasuren 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
80 END IF;
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');
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 * viveksha 30-Jan-2009 Updated the procedure to update txn ids *
238 ***************************************************************************** */
239 PROCEDURE mth_run_log_post_load(p_fact_table IN VARCHAR2,
240 p_db_global_name IN VARCHAR2)
241
242 IS
243
244 --local variables initialization
245 l_fact_table mth_run_log.fact_table%TYPE;--fact table
246 l_sysdate DATE := sysdate;--variable for sysdate
247 l_last_update_system_id mth_run_log.last_update_system_id%TYPE;
248 l_last_update_date mth_run_log.last_update_date%TYPE;
249 l_system_fk_key mth_systems_setup.system_pk_key%TYPE;
250 l_to_date mth_run_log.to_date%TYPE;--variable for storing to_date
251 l_from_date mth_run_log.from_date%TYPE;--variable for storing from_date
252 l_ebs_organization_id mth_run_log.ebs_organization_id%TYPE;
253 --Hub organization code
254 l_hub_organization_code mth_run_log.hub_organization_code%TYPE;
255 --TXN IDS
256 l_to_txn_id mth_run_log.to_txn_id%TYPE;
257 l_from_txn_id mth_run_log.from_txn_id%TYPE;
258
259 --cursor for iterating through the ebs organizations in mth_plants_d
260 --the rows in the mth_run_log will be at organization granularity
261
262 CURSOR c_ebs_orgs
263 IS
264 SELECT ebs_organization_id,system_fk_key
265 FROM mth_plants_d, mth_systems_setup,mth_organizations_l
266 WHERE system_pk_key = system_fk_key
267 AND system_pk = p_db_global_name
268 AND NVL(to_date,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)--pick active plants only
269 AND plant_fk_key=plant_pk_key;
270
271
272 BEGIN
273
274 --initialize the varialbles common to the initial and incremental
275 -- loading
276
277 l_fact_table := p_fact_table;
278 l_last_update_system_id := -99999;
279 l_last_update_date := l_sysdate;
280 --iterate through the cursor
281
282 /* The to_date for all organizations populated for a given fact and system are bound to be the same because
283 all organizations are populated with sysdate(source or target) as the to_date. So giving a generic update command here would work */
284
285 SELECT min(to_date) --min is to avoid getting duplicate rows
286 INTO l_from_date--from date set to previous to_date
287 FROM mth_run_log
288 WHERE fact_table = l_fact_table
289 AND db_global_name = p_db_global_name;
290
291 --select the next starting txn id into l_from_txn_id
292 SELECT MIN(to_txn_id)
293 INTO l_from_txn_id--from txn id to be set to previous to txn id
294 FROM mth_run_log
295 WHERE fact_table = l_fact_table
296 AND db_global_name = p_db_global_name;
297
298
299 --if statement to restrict the accidental re run of the block
300 IF l_from_date IS NOT NULL THEN
301 --update the mth run log for next incremental run
302
303 UPDATE mth_run_log
304 SET from_date = l_from_date,--from date set to previous to_date
305 to_date = NULL,--to_date set to null for next run
306 last_update_date = l_last_update_date,
307 from_txn_id = l_from_txn_id, -- from txn id set to previous to txn id
308 to_txn_id = NULL
312 END IF;
309 Where fact_table = l_fact_table
310 AND db_global_name = p_db_global_name;
311
313
314
315
316 /*
317 FOR l_orgs IN c_ebs_orgs
318 LOOP
319 --initialize the variables for current cursor value
320 l_ebs_organization_id := l_orgs.ebs_organization_id;
321
322
323 --select the next starting date into l_from_date
324 SELECT to_date
325 INTO l_from_date--from date set to previous to_date
326 FROM mth_run_log
327 WHERE fact_table = l_fact_table
328 AND db_global_name = p_db_global_name
329 AND ebs_organization_id = l_ebs_organization_id;
330
331
332
333
334 --if statement to restrict the accidental re run of the block
335 IF l_from_date IS NOT NULL THEN
336 --update the mth run log for next incremental run
337
338 UPDATE mth_run_log
339 SET from_date = l_from_date,--from date set to previous to_date
340 to_date = NULL,--to_date set to null for next run
341 from_txn_id = l_from_txn_id, -- from txn id set to previous to txn id
342 to_txn_id = NULL,
343 last_update_date = l_last_update_date
344 Where fact_table = l_fact_table
345 AND ebs_organization_id = l_ebs_organization_id
346 AND db_global_name = p_db_global_name;
347
348 END IF;
349
350 END LOOP;
351 */
352
353 --handle exceptions
354 EXCEPTION
355 WHEN NO_DATA_FOUND THEN
356 RAISE_APPLICATION_ERROR (-20001,
357 'Exception has occured');
358 END mth_run_log_post_load;
359
360
361 /* *****************************************************************************
362 * Procedure :MTH_HRCHY_BALANCE_LOAD *
363 * Description :This procedure is used for the balancing of the *
364 * hierarchy. The algorithm used for the balancing is down balancing *
365 * Please refer to the Item fdd for more details on this. *
366 * File Name :MTHUTILS.PLS *
367 * Visibility :Public *
368 * Parameters :fact table name *
369 * Modification log : *
370 * Author Date Change *
371 * Ankit Goyal 17-Aug-2007 Initial Creation *
372 ****************************************************************************** */
373 PROCEDURE mth_hrchy_balance_load(p_fact_table IN VARCHAR2) is
374
375 v_fact_table VARCHAR2(120);
376
377 --user defined type for array of records
378 TYPE denorm_rec_tab_type IS TABLE OF NUMBER;
379 TYPE denorm_rec_name_tab_type IS TABLE OF VARCHAR2(240);
380
381 --user defined type of record of arrays
382 TYPE denorm_rec_type IS RECORD (level9_fk_key denorm_rec_tab_type,
383 hierarchy_id denorm_rec_tab_type,
384 baselevel_fk_key denorm_rec_tab_type,
385 level7_fk_key denorm_rec_tab_type,
386 level6_fk_key denorm_rec_tab_type,
387 level5_fk_key denorm_rec_tab_type,
388 level4_fk_key denorm_rec_tab_type,
389 level3_fk_key denorm_rec_tab_type,
390 level2_fk_key denorm_rec_tab_type,
391 level1_fk_key denorm_rec_tab_type,
392 level9_name denorm_rec_name_tab_type,
393 level7_name denorm_rec_name_tab_type,
394 level6_name denorm_rec_name_tab_type,
395 level5_name denorm_rec_name_tab_type,
396 level4_name denorm_rec_name_tab_type,
397 level3_name denorm_rec_name_tab_type,
398 level2_name denorm_rec_name_tab_type,
399 level1_name denorm_rec_name_tab_type
400 );
401
402 --instantiation of the user defined type
403 --this will be the placeholder for the records fetched from the denorm table
404 denorm_rec denorm_rec_type;
405
406 --user defined cursor to hold the bulk collection of records
407 item_cur SYS_REFCURSOR;
408
409 --variable for the limit of the bulk collection
410 v_limit NUMBER :=5000;
411
412
413 BEGIN
414
415 --initialize the collection
416 denorm_rec := NULL;
417
418 --initialize the fact table name
419 v_fact_table :=p_fact_table;
420
421 --open the cursor
422 OPEN item_cur FOR 'SELECT --select for the newe levels
423 level9_fk_key,hierarchy_id,item_fk_key,
424 Decode(diff_level,1,level8_fk_key,level9_fk_key) level7_fk_key_new,
425 Decode(diff_level,1,level7_fk_key,2,level8_fk_key,level9_fk_key)
426 level6_fk_key_new,
427 Decode(diff_level,1,level6_fk_key,2,level7_fk_key,3,level8_fk_key,
428 level9_fk_key) level5_fk_key_new,
429 Decode(diff_level,1,level5_fk_key,2,level6_fk_key,3,level7_fk_key,4,
430 level8_fk_key,level9_fk_key) level4_fk_key_new,
431 Decode(diff_level,1,level4_fk_key,2,level5_fk_key,3,level6_fk_key,4,
432 level7_fk_key,5,level8_fk_key,level9_fk_key) level3_fk_key_new,
433 Decode(diff_level,1,level3_fk_key,2,level4_fk_key,3,level5_fk_key,4,
434 level6_fk_key,5,level7_fk_key,6,level8_fk_key,level9_fk_key)
435 level2_fk_key_new,
436 Decode(diff_level,1,level2_fk_key,2,level3_fk_key,3,level4_fk_key,4,
437 level5_fk_key,5,level6_fk_key,6,level7_fk_key,7,level8_fk_key,
438 level9_fk_key) level1_fk_key_new,
439 level9_name,
440 Decode(diff_level,1,level8_name,level9_name) level7_name_new,
441 Decode(diff_level,1,level7_name,2,level8_name,level9_name)
442 level6_name_new,
443 Decode(diff_level,1,level6_name,2,level7_name,3,level8_name,
444 level9_name) level5_name_new,
445 Decode(diff_level,1,level5_name,2,level6_name,3,level7_name,4,
446 level8_name,level9_name) level4_name_new,
447 Decode(diff_level,1,level4_name,2,level5_name,3,level6_name,4,
448 level7_name,5,level8_name,level9_name) level3_name_new,
449 Decode(diff_level,1,level3_name,2,level4_name,3,level5_name,4,
450 level6_name,5,level7_name,6,level8_name,level9_name)
451 level2_name_new,
452 Decode(diff_level,1,level2_name,2,level3_name,3,level4_name,4,
453 level5_name,5,level6_name,6,level7_name,7,level8_name,
454 level9_name) level1_name_new
455 from
456 (--select the levels to be balanced
457 SELECT hierarchy_id ,item_fk_key,
458 level9_fk_key,level8_fk_key,level7_fk_key,level6_fk_key,
459 level5_fk_key,level4_fk_key,level3_fk_key,level2_fk_key,
460 level1_fk_key,
461 level9_name,level8_name,level7_name,level6_name,
462 level5_name,level4_name,level3_name,level2_name,
463 level1_name,
464 max_level-c_level diff_level
465 FROM
466 (
467 SELECT hierarchy_id ,item_fk_key,
468 level9_fk_key,level8_fk_key,level7_fk_key,level6_fk_key,
469 level5_fk_key,level4_fk_key,level3_fk_key,level2_fk_key,
470 level1_fk_key,
471 level9_name,level8_name,level7_name,level6_name,
472 level5_name,level4_name,level3_name,level2_name,
473 level1_name,
474 decode(level9_fk_key,NULL,0,1) +
475 decode(level8_fk_key,NULL,0,1) +
476 decode(level7_fk_key,NULL,0,1) +
477 decode(level6_fk_key,NULL,0,1) +
478 decode(level5_fk_key,NULL,0,1) +
479 decode(level4_fk_key,NULL,0,1) +
480 decode(level3_fk_key,NULL,0,1) +
481 decode(level2_fk_key,NULL,0,1) +
482 decode(level1_fk_key,NULL,0,1) c_level,--current level
483 Max(decode(level9_fk_key,NULL,0,1) +
484 decode(level8_fk_key,NULL,0,1) +
485 decode(level7_fk_key,NULL,0,1) +
486 decode(level6_fk_key,NULL,0,1) +
487 decode(level5_fk_key,NULL,0,1) +
488 decode(level4_fk_key,NULL,0,1) +
489 decode(level3_fk_key,NULL,0,1) +
490 decode(level2_fk_key,NULL,0,1) +
491 decode(level1_fk_key,NULL,0,1)) over(PARTITION BY hierarchy_id)
492 max_level--maximum level in the hierarchy
493 FROM mth_item_denorm_d
494 WHERE item_fk_key != MTH_UTIL_PKG.MTH_UA_GET_VAL
495 )
496 WHERE c_level<max_level
497 AND level9_fk_key IS NOT NULL
498 )';
499 LOOP
500 --fetch the rows in in cursor. Bulk collect
501 FETCH item_cur BULK COLLECT INTO denorm_rec.level9_fk_key,
502 denorm_rec.hierarchy_id,
503 denorm_rec.baselevel_fk_key,denorm_rec.level7_fk_key,
504 denorm_rec.level6_fk_key,
505 denorm_rec.level5_fk_key,denorm_rec.level4_fk_key,
506 denorm_rec.level3_fk_key,denorm_rec.level2_fk_key,
507 denorm_rec.level1_fk_key,
508 denorm_rec.level9_name,
509 denorm_rec.level7_name,
510 denorm_rec.level6_name,
511 denorm_rec.level5_name,
512 denorm_rec.level4_name,
513 denorm_rec.level3_name,
514 denorm_rec.level2_name,
515 denorm_rec.level1_name
516 LIMIT v_limit;
517
518 --terminating condition
519 EXIT WHEN denorm_rec.baselevel_fk_key.count =0;
520
521 --bulk update using forall
522 FORALL i IN
523 denorm_rec.baselevel_fk_key.first..denorm_rec.baselevel_fk_key.last
524 UPDATE mth_item_denorm_d
525 SET
526 level8_fk_key = denorm_rec.level9_fk_key(i),
527 level7_fk_key = denorm_rec.level7_fk_key(i),
528 level6_fk_key = denorm_rec.level6_fk_key(i),
529 level5_fk_key = denorm_rec.level5_fk_key(i),
530 level4_fk_key = denorm_rec.level4_fk_key(i),
531 level3_fk_key = denorm_rec.level3_fk_key(i),
532 level2_fk_key = denorm_rec.level2_fk_key(i),
533 level1_fk_key = denorm_rec.level1_fk_key(i),
534 level8_name = denorm_rec.level9_name(i),
535 level7_name = denorm_rec.level7_name(i),
536 level6_name = denorm_rec.level6_name(i),
537 level5_name = denorm_rec.level5_name(i),
538 level4_name = denorm_rec.level4_name(i),
539 level3_name = denorm_rec.level3_name(i),
540 level2_name = denorm_rec.level2_name(i),
541 level1_name = denorm_rec.level1_name(i)
542 WHERE
543 item_fk_key = denorm_rec.baselevel_fk_key(i)
544 AND hierarchy_id= denorm_rec.hierarchy_id(i);
545 END LOOP;
546 --close the cursor
547 CLOSE item_cur;
548
549 --handle exceptions
550 EXCEPTION
551 WHEN NO_DATA_FOUND THEN
552 RAISE_APPLICATION_ERROR (-20001,
553 'Exception has occured');
554
555 END mth_hrchy_balance_load ;
556
557
558 /* *****************************************************************************
559 * Procedure :MTH_TRUNCATE_TABLE *
560 * Description :This procedure is used to truncate the table in the *
561 * MTH Schema. Thsi can be overriden by spefying a custom schema name as well. *
562 * File Name :MTHUTILS.PLS *
563 * Visibility :Public *
564 * Parameters :Table name *
565 * Modification log : *
566 * Author Date Change *
567 * Ankit Goyal 11-Oct-2007 Initial Creation *
568 ***************************************************************************** */
569
570 PROCEDURE mth_truncate_table(p_table_name IN VARCHAR2) IS
571
572 --initialize variables here
573 v_stmt VARCHAR2(2000);
574 v_schema_name VARCHAR2(100);
575 v_status VARCHAR2(30) ;
576 v_industry VARCHAR2(30) ;
577
578 -- main body
579 BEGIN
580 IF (FND_INSTALLATION.GET_APP_INFO(
581 application_short_name => 'MTH'
582 , status => v_status
583 , industry => v_industry
584 , oracle_schema => v_schema_name))
585 THEN
586 --Prepare the truncate statement using schema name and table name
587 v_stmt := 'TRUNCATE TABLE '||v_schema_name||'.'||p_table_name;
588 EXECUTE IMMEDIATE v_stmt;
589 END IF;
590
591 END mth_truncate_table;
592
593 /* *****************************************************************************
594 * Procedure :MTH_TRUNCATE_TABLE *
595 * Description :This procedure is used to truncate the table in the *
596 * specified schema. *
597 * File Name :MTHUTILS.PLS *
598 * Visibility :Public *
599 * Parameters :Table name *
600 * Schema name *
601 * Modification log : *
602 * Author Date Change *
603 * Yong Feng July 18, 2008 Initial Creation *
604 ***************************************************************************** */
605
606 PROCEDURE mth_truncate_table(p_table_name IN VARCHAR2,
607 p_schema_name IN VARCHAR2) IS
608 --initialize variables here
609 v_stmt VARCHAR2(2000);
610
611 BEGIN
612 --Prepare the truncate statement using schema name and table name
613 v_stmt := 'TRUNCATE TABLE '||p_schema_name||'.'||p_table_name;
614 EXECUTE IMMEDIATE v_stmt;
615 -- Truncate called for table
616 mth_util_pkg.log_msg('Truncated table '||p_table_name, mth_util_pkg.G_DBG_USER_INFO);
617
618 END mth_truncate_table;
619
620 /* ****************************************************************************
621 * Procedure :MTH_TRUNCATE_TABLES *
622 * Description :This procedure is used to truncate the tables in the *
623 * list separated by comma. *
624 * File Name :MTHUTILS.PLS *
625 * Visibility :Public *
626 * Parameters :p_list_table_names: List of table names separated *
627 * by commas. *
628 * :p_schema_name: The schema name for all listed tables.*
629 * Modification log : *
630 * Author Date Change *
631 * Yong Feng Aug-07-2008 Initial Creation *
632 **************************************************************************** */
633
634 PROCEDURE mth_truncate_tables(p_list_table_names IN VARCHAR2) IS
635 -- Index to identify the beginning of a table name in the list
636 v_bidx number := 1;
637 -- Index to identify the ending of a table name in the list
638 v_eidx number;
639 v_has_schema_name BOOLEAN;
640 v_table_name varchar2(30);
641 v_user_name varchar2(30);
642 v_status VARCHAR2(30) ;
643 v_industry VARCHAR2(30) ;
644 v_mth_name varchar2(30);
645 v_schema_name varchar2(30);
646 v_list_length number;
647 cursor getSchema (p_table_name in varchar2,
648 p_owner1 in varchar2,
649 p_owner2 in varchar2) is
650 SELECT owner
651 FROM ALL_TABLES
652 WHERE table_name = p_table_name
653 AND owner in (p_owner1, p_owner2);
654
655 BEGIN
656 IF p_list_table_names IS NULL OR LENGTH(p_list_table_names) = 0 THEN
657 RETURN;
658 END IF;
659
660 v_user_name :=user;
661
662 IF (NOT FND_INSTALLATION.GET_APP_INFO(
663 application_short_name => 'MTH'
664 , status => v_status
665 , industry => v_industry
666 , oracle_schema => v_mth_name)) THEN
667 RAISE_APPLICATION_ERROR (-20001,
668 'Could not find MTH product.');
669 END IF;
670
671 v_list_length := LENGTH(p_list_table_names);
672
673 -- Parse the list of table name and truncate each table
674 v_eidx := INSTR(p_list_table_names, ',', v_bidx);
675
676 -- Handle the case where there is only one element in the list
677 IF (v_eidx = 0 AND v_bidx <= v_list_length) THEN
678 v_eidx := v_list_length + 1;
679 END IF;
680
681 WHILE (v_eidx > 0 AND v_eidx > v_bidx) LOOP
682 v_table_name := SUBSTR(p_list_table_names, v_bidx, (v_eidx - v_bidx));
683 v_table_name := UPPER(TRIM(BOTH FROM v_table_name));
684
685 -- Get the schema name for that table
686 IF v_table_name IS NOT NULL AND LENGTH(v_table_name) > 0 THEN
687 OPEN getSchema ( v_table_name, v_user_name, v_mth_name);
688 FETCH getSchema INTO v_schema_name;
689 IF (getSchema%FOUND) THEN
690 MTH_TRUNCATE_TABLE(v_table_name, v_schema_name);
691 CLOSE getSchema;
692 ELSE
693 CLOSE getSchema;
694 RAISE_APPLICATION_ERROR (-20001,
695 'Could not find table ' || v_table_name || ' in either ' || v_user_name || ' or ' || v_mth_name || '.');
696 END IF;
697 END IF;
698 v_bidx := v_eidx + 1;
699 v_eidx := INSTR(p_list_table_names, ',', v_bidx);
700 -- Handle the case for end of the list
701 IF (v_eidx = 0 AND v_bidx <= v_list_length) THEN
702 v_eidx := v_list_length + 1;
703 END IF;
704 END LOOP;
705 END mth_truncate_tables;
706
707
708
709 /* ****************************************************************************
710 * Procedure :MTH_TRUNCATE_MV_LOGS *
711 * Description :This procedure is used to truncate the Materialized *
712 * View log created on the tables *
713 * list separated by comma. *
714 * File Name :MTHUTILS.PLS *
715 * Visibility :Public *
716 * Parameters :p_list_table_names: List of table names separated *
717 * by commas. *
718 * Modification log : *
719 * Author Date Change *
720 * Yong Feng Aug-07-2008 Initial Creation *
721 **************************************************************************** */
722
723 PROCEDURE MTH_TRUNCATE_MV_LOGS (p_list_table_names IN VARCHAR2) IS
724 -- Index to identify the beginning of a table name in the list
725 v_bidx number := 1;
726 -- Index to identify the ending of a table name in the list
727 v_eidx number;
728 v_has_schema_name BOOLEAN;
729 v_table_name varchar2(30);
730 v_user_name varchar2(30);
731 v_status VARCHAR2(30) ;
732 v_industry VARCHAR2(30) ;
733 v_mth_name varchar2(30);
734 v_log_owner varchar2(30);
735 v_log_table varchar2(30);
736 v_list_length number;
737 cursor getLogTableSchema (p_table_name in varchar2,
738 p_owner1 in varchar2,
739 p_owner2 in varchar2) is
740 SELECT log_owner, log_table
741 FROM ALL_SNAPSHOT_LOGS
742 WHERE master = p_table_name
743 AND log_owner in (p_owner1, p_owner2);
744
745 BEGIN
746 IF p_list_table_names IS NULL OR LENGTH(p_list_table_names) = 0 THEN
747 RETURN;
748 END IF;
749
750 v_user_name :=user;
751
752 IF (NOT FND_INSTALLATION.GET_APP_INFO(
753 application_short_name => 'MTH'
754 , status => v_status
755 , industry => v_industry
756 , oracle_schema => v_mth_name)) THEN
757 RAISE_APPLICATION_ERROR (-20001,
758 'Could not find MTH product.');
759 END IF;
760
761 v_list_length := LENGTH(p_list_table_names);
762
763 -- Parse the list of table name and truncate each table
764 v_eidx := INSTR(p_list_table_names, ',', v_bidx);
765
766 -- Handle the case where there is only one element in the list
767 IF (v_eidx = 0 AND v_bidx <= v_list_length) THEN
768 v_eidx := v_list_length + 1;
769 END IF;
770
771 WHILE (v_eidx > 0 AND v_eidx > v_bidx) LOOP
772 v_table_name := SUBSTR(p_list_table_names, v_bidx, (v_eidx - v_bidx));
773 v_table_name := UPPER(TRIM(BOTH FROM v_table_name));
774
775 -- Get the mv log name and schema name for that table
776 IF v_table_name IS NOT NULL AND LENGTH(v_table_name) > 0 THEN
777 OPEN getLogTableSchema ( v_table_name, v_user_name, v_mth_name);
778 FETCH getLogTableSchema INTO v_log_owner, v_log_table;
779 IF (getLogTableSchema%FOUND) THEN
780 MTH_TRUNCATE_TABLE(v_log_table, v_log_owner);
781 CLOSE getLogTableSchema ;
782 ELSE
783 CLOSE getLogTableSchema;
784 RAISE_APPLICATION_ERROR (-20001,
785 'Could not find Materialized View Log on table ' || v_table_name || ' in either ' || v_user_name || ' or ' || v_mth_name || '.');
786 END IF;
787 END IF;
788 v_bidx := v_eidx + 1;
789 v_eidx := INSTR(p_list_table_names, ',', v_bidx);
790 -- Handle the case for end of the list
791 IF (v_eidx = 0 AND v_bidx <= v_list_length) THEN
792 v_eidx := v_list_length + 1;
793 END IF;
794 END LOOP;
795 END MTH_TRUNCATE_MV_LOGS;
796
797
798
799
800 /* *****************************************************************************
801 * Function :MTH_UA_GET_VAL *
802 * Description : This procedure is used to return the lookup code for *
803 * the unasssigned *
804 * File Name :MTHUTILS.PLS *
805 * Visibility :Public *
806 * Parameters :None *
807 * Return Value :v_lookup_code : Unassigned lookup code value *
808 * Modification log : *
809 * Author Date Change *
810 * Ankit Goyal 11-Oct-2007 Initial Creation *
811 ***************************************************************************** */
812
813 Function mth_ua_get_val RETURN NUMBER IS
814 v_lookup_code varchar2(30);
815 BEGIN
816 --NULL
817 SELECT lookup_code INTO v_lookup_code FROM FND_LOOKUP_VALUES WHERE
818 lookup_type='MTH_UNASSIGNED_L' AND language=userenv('LANG');
819
820 RETURN(to_number(v_lookup_code));
821
822 --handle exceptions
823 EXCEPTION
824 WHEN NO_DATA_FOUND THEN
825 RAISE_APPLICATION_ERROR (-20001,
826 'Exception has occured');
827
828 END mth_ua_get_val;
829
830
831
832 /* *****************************************************************************
833 * Function :MTH_UA_GET_MEANING *
834 * Description :This procedure is used to return the lookup meaning *
835 * for the unasssigned *
836 * File Name :MTHUTILS.PLS *
837 * Visibility :Public *
838 * Parameters :None *
839 * Return Value :v_lookup_code : Unassigned lookup code value *
840 * Modification log : *
841 * Author Date Change *
842 * Ankit Goyal 23-Oct-2007 Initial Creation *
843 ***************************************************************************** */
844
845 Function mth_ua_get_meaning RETURN VARCHAR2 IS
846 v_lookup_meaning varchar2(80);
847 BEGIN
848 --NULL
849 SELECT meaning INTO v_lookup_meaning FROM FND_LOOKUP_VALUES WHERE
850 lookup_type='MTH_UNASSIGNED_L' AND language=userenv('LANG');
851
852 RETURN(v_lookup_meaning);
853
854 --handle exceptions
855 EXCEPTION
856 WHEN NO_DATA_FOUND THEN
857 RAISE_APPLICATION_ERROR (-20001,
858 'Exception has occured');
859
860 END mth_ua_get_meaning;
861
862 /* ****************************************************************************
863 * Procedure :request_lock *
864 * Description :This procedure is used to request an exclusive *
865 * lock with p_key_table as the key using rdbms_lock package. The current *
866 * will wait indifinitely if the lock was held by others until the release *
867 * of the lock. *
868 * File Name :MTHUTILB.PLS *
869 * Visibility :Private *
870 * Parameters *
871 * p_key_table : The name used to request an exclusive lock. *
872 * p_retval : The return value of the operation: *
873 * 0 - Success *
874 * 1 - Timeout *
875 * 2 - Deadlock *
876 * 3 - Parameter Error *
877 * 4 - Already owned *
878 * 5 - Illegal Lock Handle *
879 * Modification log : *
880 * Author Date Change *
881 * Yong Feng 17-Oct-2007 Initial Creation *
882 **************************************************************************** */
883 PROCEDURE request_lock(p_key_table IN VARCHAR2, p_retval OUT NOCOPY INTEGER)
884 IS
885 v_lockhandle VARCHAR2(200);
886 BEGIN
887 dbms_lock.allocate_unique(p_key_table, v_lockhandle);
888 p_retval := dbms_lock.request(v_lockhandle, dbms_lock.x_mode);
889 END request_lock;
890
891 /* ****************************************************************************
892 * Procedure :generate_new_time_range *
893 * Description :This procedure is used to generate a time range *
894 * starting from the last end date up to current time, sysdate, using *
895 * the p_key_table name as the key to look up the entry in mth_run_log *
896 * table. If the entry does not exist, create one and set the time range *
897 * to a hard-coded past time to current time. *
898 * File Name :MTHUTILB.PLS *
899 * Visibility :Public *
900 * Parameters *
901 * p_key_table : Name to uniquely identify one entry in the *
902 * mth_run_log table. *
903 * p_start_date : An output value that specifies the start time *
904 * of the new time period. *
905 * p_end_date : An output value that specifies the end time *
906 * of the new time period. *
907 * p_exclusive_lock : Specify whether it needs to request an exclusive *
908 * lock using p_key_table as the key so that only *
909 * one procedure will be running at one point of time. *
910 * If the value is 1, then it will run in exclusive *
911 * mode. The lock will be released when the *
912 * transaction is either committed or rollbacked. *
913 * Modification log : *
914 * Author Date Change *
915 * Yong Feng 17-Oct-2007 Initial Creation *
916 **************************************************************************** */
917 PROCEDURE generate_new_time_range(p_key_table IN VARCHAR2,
918 p_start_date OUT NOCOPY DATE,
919 p_end_date OUT NOCOPY DATE,
920 p_exclusive_lock IN NUMBER DEFAULT 1)
921 IS
922 v_from_date mth_run_log.from_date%TYPE;
923 v_to_date mth_run_log.to_date%TYPE;
924 v_is_new_entry BOOLEAN := FALSE;
925 v_default_start_date DATE := to_date('1950', 'YYYY');
926 v_sysdate DATE := sysdate;
927 v_retval number := 0;
928
929 CURSOR c_lookup IS
930 SELECT to_date
931 FROM mth_run_log
932 WHERE fact_table = p_key_table and rownum=1;
933 BEGIN
934 -- 1. Validate the p_fact_table input value.
935 IF (p_key_table is not null) THEN
936
937 -- 2. Check to see if we need to request an exclusive lock.
938 -- It will wait infinitively if it cannot get the lock.
939 -- Do not request lock any more for the mappinging. Request one
940 -- for the process flow instead.
941 --IF p_exclusive_lock = 1 THEN
942 -- request_lock(p_key_table, v_retval);
943 --END IF;
944
945 --IF v_retval = 0 THEN
946
947 -- 3. Do the look up
948 open c_lookup;
949 fetch c_lookup into v_to_date;
950 IF c_lookup%NOTFOUND THEN
951 v_is_new_entry := TRUE;
952 END IF;
953 close c_lookup;
954
955 v_from_date := v_to_date;
956 v_to_date := v_sysdate;
957
958 -- 4. Create a new entry if not exist. Otherwise, update the entry
959 IF v_is_new_entry THEN
960 v_from_date := v_default_start_date;
961 INSERT INTO mth_run_log
962 (fact_table, ebs_organization_id, ebs_organization_code, from_date,
963 to_date, source, db_global_name, creation_date, last_update_date,
964 creation_system_id, last_update_system_id, plant_start_date)
965 VALUES
966 (p_key_table, -1, '-1', v_from_date,
967 v_to_date, -1, '-99999', v_sysdate, v_sysdate,
968 -1, -1, v_default_start_date);
969 ELSE
970 UPDATE mth_run_log
971 SET TO_DATE = v_to_date,
972 FROM_DATE = v_from_date,
973 LAST_UPDATE_DATE = v_sysdate
974 WHERE
975 fact_table = p_key_table;
976 END IF;
977
978 -- 5. Set the output variables
979 p_start_date := v_from_date;
980 p_end_date := v_to_date;
981 --END IF;
982 END IF;
983 END generate_new_time_range;
984
985
986
987 /* *****************************************************************************
988 * Function :GET_PROFILE_VAL *
989 * Description :This function is used to retrive the value of the *
990 * profile for the profile name provided by the user *
991 * File Name :MTHSOURCEPATCHS.PLS *
992 * Visibility :Public *
993 * Return : V_PROFILE_NAME - Global name of the source DB *
994 * Modification log : *
995 * Author Date Change *
996 * Ankit Goyal 29-Oct-2007 Initial Creation *
997 ******************************************************************************/
998 FUNCTION get_profile_val(p_profile_name IN VARCHAR2) RETURN VARCHAR2
999 IS
1000 --local variable declation
1001 v_profile_value varchar2(120);
1002
1003 v_uid number := -1;
1004 v_rid number := -1;
1005 v_applid number := 1;
1006
1007 BEGIN
1008
1009 --Initialize the session for default values -1.
1010 /* fnd_global.apps_initialize(v_uid, v_rid, v_applid); */
1011
1012 --select the value of the profile into the local variable.
1013 select fnd_profile.value(p_profile_name) into v_profile_value from dual;
1014
1015 --Return the value.
1016 RETURN(v_profile_value);
1017
1018 --End the function
1019 END;
1020
1021 /* *****************************************************************************
1022 * Function :CHECK_REFERENCE *
1023 * Description :This function is used to retrive the value of the *
1024 * count of rows from mv *
1025 * File Name :MTHUTILS.PLS *
1026 * Visibility :Public *
1027 * Return : v_count Value *
1028 * Modification log : *
1029 * Author Date Change *
1030 * Mandar Gijare 20-Dec-2010 Initial Creation *
1031 ******************************************************************************/
1032 FUNCTION check_reference RETURN NUMBER
1033 IS
1034 --local variable declation
1035 v_count number;
1036
1037 BEGIN
1038
1039 --select the count value into the local variable.
1040 SELECT Count(*) into v_count FROM mth_shift_reference_mv;
1041
1042 --Return the value.
1043 RETURN(v_count);
1044
1045 --End the function
1046 END;
1047
1048 /* *****************************************************************************
1049 * Function :FIND_METERS *
1050 * Description :This function is used to retrive the value of the *
1051 * meters from the virtual meter formula *
1052 * File Name :MTHUTILS.PLS *
1053 * Visibility :Public *
1054 * Return : v_str Value *
1055 * Modification log : *
1056 * Author Date Change *
1057 * Mandar Gijare 31-May-2011 Initial Creation *
1058 ******************************************************************************/
1059 FUNCTION find_meters(p_meter_formula IN VARCHAR2) RETURN VARCHAR2
1060 IS
1061 --local variable declaration
1062 v_meter_id VARCHAR2(1000);
1063 v_str VARCHAR2(4000);
1064 v_str_len NUMBER;
1065 v_meter_name VARCHAR2(4000);
1066 v_meter_name_str VARCHAR2(4000);
1067 v_virt_meter_formula VARCHAR2(4000);
1068 v_meter_type VARCHAR2(10);
1069 v_position NUMBER;
1070
1071 BEGIN
1072 v_str := p_meter_formula;
1073 v_str_len := InStr(v_str,'ID#');
1074
1075 WHILE (v_str_len > 0)
1076 LOOP
1077 SELECT Least(
1078 Decode(InStr(v_str,'+',v_str_len),0,4000,InStr(v_str,'+',v_str_len)),
1079 Decode(InStr(v_str,'-',v_str_len),0,4000,InStr(v_str,'-',v_str_len)),
1080 Decode(InStr(v_str,'*',v_str_len),0,4000,InStr(v_str,'*',v_str_len)),
1081 Decode(InStr(v_str,'/',v_str_len),0,4000,InStr(v_str,'/',v_str_len)),
1082 Decode(InStr(v_str,'(',v_str_len),0,4000,InStr(v_str,'(',v_str_len)),
1083 Decode(InStr(v_str,')',v_str_len),0,4000,InStr(v_str,')',v_str_len)))
1084 INTO v_position
1085 FROM dual;
1086
1087 SELECT SubStr(v_str,v_str_len,v_position-1-(v_str_len-1))
1088 INTO v_meter_id
1089 FROM dual;
1090 IF(v_meter_id IS NULL)
1091 THEN
1092 v_meter_id := v_str;
1093 END IF;
1094
1095 IF v_meter_id IS NOT NULL
1096 THEN
1097 SELECT meter_name, meter_type, virtual_meter_formula
1098 INTO v_meter_name, v_meter_type, v_virt_meter_formula
1099 FROM mth_meters
1100 WHERE meter_pk_key IN (SELECT COMPONENT_VALUE
1101 FROM MTH_VIRTUAL_METER_COMPONENTS
1102 WHERE component_name = v_meter_id);
1103 END IF;
1104
1105
1106 IF v_virt_meter_formula IS NOT NULL
1107 THEN
1108 v_meter_name := v_meter_name || ' [ ' || FIND_METERS(v_virt_meter_formula) || ' ] ';
1109 ELSE
1110 v_meter_name := v_meter_name;
1111 END IF;
1112
1113 v_str := SubStr(v_str,1,v_str_len-1) || v_meter_name || SubStr(v_str,v_position);
1114 v_str_len := InStr(v_str,'ID#');
1115
1116 END LOOP;
1117
1118 RETURN v_str;
1119 END;
1120
1121
1122 /* ****************************************************************************
1123 * Function :Get_UDA_Eq_HId *
1124 * Description :This function is used to retrive the hierarchy id of *
1125 * the UDA Equipment profile *
1126 * File Name :MTHUTILS.PLS *
1127 * Visibility :Public *
1128 * Return :Hierarchy id for the equipment UDA profile *
1129 * Modification log : *
1130 * Author Date Change *
1131 * Vivek 18-Jan-2008 Initial Creation *
1132 ******************************************************************************/
1133
1134 FUNCTION Get_UDA_Eq_HId RETURN VARCHAR IS
1135
1136 v_profile VARCHAR2(6);
1137 v_pos NUMBER;
1138 v_hId VARCHAR2(3);
1139
1140 BEGIN
1141
1142 -- Get the profile value, it will be of form h_id:level_no
1143 v_profile := FND_PROFILE.VALUE('MTH_UDA_EQUIPMENT_PROFILE');
1144
1145 -- based on the position of :, retrieve h_id
1146 v_pos := INSTR(v_profile,':');
1147 v_hId := SUBSTR(v_profile,1,v_pos-1);
1148
1149 RETURN v_hId;
1150
1151 EXCEPTION
1152 WHEN OTHERS THEN NULL;
1153 END;
1154
1155 /* ****************************************************************************
1156 * Function :Get_UDA_Eq_LNo *
1157 * Description :This function is used to retrive the Level Number of *
1158 * the UDA Equipment profile *
1159 * File Name :MTHUTILS.PLS *
1160 * Visibility :Public *
1161 * Return :Level Number for the equipment UDA profile *
1162 * Modification log : *
1163 * Author Date Change *
1164 * Vivek 18-Jan-2008 Initial Creation *
1165 ******************************************************************************/
1166
1167 FUNCTION Get_UDA_Eq_LNo RETURN VARCHAR IS
1168
1169 v_profile VARCHAR2(6);
1170 v_pos NUMBER;
1171 v_lNo VARCHAR2(3);
1172 v_length NUMBER;
1173
1174 BEGIN
1175
1176 -- Get the profile value, it will be of form h_id:level_no
1177 v_profile := FND_PROFILE.VALUE('MTH_UDA_EQUIPMENT_PROFILE');
1178
1179 -- based on the position of :, retrieve h_id
1180 v_pos := INSTR(v_profile,':');
1181 v_length := LENGTH(v_profile);
1182 v_lNo := SUBSTR(v_profile,v_pos+1,v_length);
1183
1184 RETURN v_lNo;
1185
1186 EXCEPTION
1187 WHEN OTHERS THEN NULL;
1188 END;
1189
1190 /* ****************************************************************************
1191 * Procedure :REFRESH_MV *
1192 * Description :This procedure is used to call DBMS_MVIEW.REFRESH *
1193 * procedure to refresh MVs. *
1194 * File Name :MTHUTILB.PLS *
1195 * Visibility :Public *
1196 * Parameters *
1197 * list : Comma-separated list of materialized views that *
1198 * you want to refresh. *
1199 * method :A string of refresh methods indicating how to *
1200 * refresh the listed materialized views. *
1201 * - An f indicates fast refresh *
1202 * - ? indicates force refresh *
1203 * - C or c indicates complete refresh *
1204 * - A or a indicates always refresh. A and C are *
1205 * equivalent. *
1206 * rollback_seg :Name of the materialized view site rollback segment *
1207 * to use while refreshing materialized views. *
1208 * push_deferred_rpc : Used by updatable materialized views only. *
1209 * refresh_after_errors : *
1210 * purge_option : *
1211 * parallelism : 0 specifies serial propagation *
1212 * heap_size : *
1213 * atomic_refresh : *
1214 * Modification log : *
1215 * Author Date Change *
1216 * Yong Feng 11-July-2008 Initial Creation *
1217 **************************************************************************** */
1218 PROCEDURE REFRESH_MV(
1219 p_list IN VARCHAR2,
1220 p_method IN VARCHAR2 := NULL,
1221 p_rollback_seg IN VARCHAR2 := NULL,
1222 p_push_deferred_rpc IN BOOLEAN := true,
1223 p_refresh_after_errors IN BOOLEAN := false,
1224 p_purge_option IN BINARY_INTEGER := 1,
1225 p_parallelism IN BINARY_INTEGER := 0,
1226 p_heap_size IN BINARY_INTEGER := 0,
1227 p_atomic_refresh IN BOOLEAN := true
1228 )
1229 IS
1230 BEGIN
1231 DBMS_MVIEW.REFRESH(p_list, p_method, p_rollback_seg, p_push_deferred_rpc,
1232 p_refresh_after_errors, p_purge_option,
1233 p_parallelism, p_heap_size, p_atomic_refresh);
1234 END REFRESH_MV;
1235
1236 /* ****************************************************************************
1237 * Procedure :REFRESH_MV_ONE_ALL *
1238 * Description :This procedure is used to call DBMS_MVIEW.REFRESH *
1239 * procedure to refresh MVs. *
1240 * File Name :MTHUTILB.PLS *
1241 * Visibility :Public *
1242 * Parameters *
1243 * list : Comma-separated list of materialized views that *
1244 * you want to refresh. *
1245 * *
1246 * Modification log : *
1247 * Author Date Change *
1248 * Mandar Gijare 24-May-2012 Initial Creation *
1249 **************************************************************************** */
1250 PROCEDURE REFRESH_MV_ONE_ALL(
1251 p_mview_name IN VARCHAR2
1252 )
1253 IS
1254 p_list VARCHAR2(2000) := NULL;
1255 p_method VARCHAR2(1) := '?';
1256 p_rollback_seg VARCHAR2(10) := NULL;
1257 p_push_deferred_rpc BOOLEAN := true;
1258 p_refresh_after_errors BOOLEAN := false;
1259 p_purge_option BINARY_INTEGER := 1;
1260 p_parallelism BINARY_INTEGER := 0;
1261 p_heap_size BINARY_INTEGER := 0;
1262 p_atomic_refresh BOOLEAN := true;
1263 BEGIN
1264 p_list := 'MTH_SHIFT_GREGORIAN_DENORM_MV,MTH_RES_TXN_IT_MV,MTH_RES_TXN_LT_MV,MTH_MTL_CONS_IT_MV,MTH_MTL_CONS_LT_MV,MTH_MTL_PROD_IT_MV,MTH_MTL_PROD_LT_MV,
1265 MTH_RES_REQ_IT_MV,MTH_RES_REQ_LT_MV,MTH_EQUIP_OP_SUM_DD_MV,MTH_EQUIP_OP_SUM_PM_MV,MTH_EQUIP_ST_SUM_DD_MV,MTH_EQUIP_ST_SUM_PM_MV,MTH_EQUIP_PROD_SCH_DD_MV,
1266 MTH_EQUIP_PROD_SCH_PM_MV,MTH_EQUIP_SHFT_DD_MV,MTH_EQUIP_SHFT_PM_MV,MTH_RESOURCE_COST_MV,MTH_ITEM_COST_MV,MTH_ALL_ENTITIES_MV,MTH_ENTITY_PLANNED_USAGE_MV,
1267 MTH_ENTITY_PLANNED_USAGE_HR_MV,MTH_ENTITY_PLANNED_USAGE_SM_MV,MTH_445_PERIOD_CAL_HOUR_MV';
1268
1269 IF(p_mview_name = 'ALL')
1270 THEN
1271
1272 DBMS_MVIEW.REFRESH(p_list, p_method, p_rollback_seg, p_push_deferred_rpc,
1273 p_refresh_after_errors, p_purge_option,
1274 p_parallelism, p_heap_size, p_atomic_refresh);
1275
1276 ELSE
1277 DBMS_MVIEW.REFRESH(p_mview_name,p_method);
1278 END IF;
1279 END REFRESH_MV_ONE_ALL;
1280
1281
1282 /* ****************************************************************************
1283 * Procedure :REFRESH_ONE_MV *
1284 * Description :This procedure is used to call refresh one MV. *
1285 * File Name :MTHUTILB.PLS *
1286 * Visibility :Public *
1287 * Parameters *
1288 * p_mv_name : Name of the materialized view to be refreshed. *
1289 * p_method :A string of refresh methods indicating how to *
1290 * refresh the listed materialized views. *
1291 * - An f indicates fast refresh *
1292 * - ? indicates force refresh *
1293 * - C or c indicates complete refresh *
1294 * - A or a indicates always refresh. A and C are *
1295 * equivalent. *
1296 * p_rollback_seg :Name of the materialized view site rollback segment *
1297 * to use while refreshing materialized views. *
1298 * p_refresh_mode :A string of refresh mode: *
1299 * - C , c or NULL indicates complete refresh. *
1300 * - R or r indicates resume refresh that has been *
1301 * started earlier. The MV will be refreshed if the *
1302 * refresh date is earlier than the date stored in *
1303 * to_date column in MTH_RUN_LOG for MTH_ALL_MVS entry. *
1304 * p_push_deferred_rpc : Used by updatable materialized views only. *
1305 * Modification log : *
1306 * Author Date Change *
1307 * Yong Feng 19-Aug-2008 Initial Creation *
1308 **************************************************************************** */
1309
1310 PROCEDURE REFRESH_ONE_MV(
1311 p_mv_name IN VARCHAR2,
1312 p_method IN VARCHAR2 := NULL,
1313 p_rollback_seg IN VARCHAR2 := NULL,
1314 p_refresh_mode IN VARCHAR2 := NULL
1315 )
1316 IS
1317 v_bneed_refresh BOOLEAN := FALSE;
1318 v_last_refresh_date DATE;
1319 v_refresh_date_required DATE;
1320 v_unassigned_string VARCHAR2(20) := to_char(mth_util_pkg.mth_ua_get_val);
1321 v_mv_name varchar2(30);
1322 cursor getRefreshDate (p_mv_name in varchar2) is
1323 SELECT last_refresh_date
1324 FROM user_mviews
1325 WHERE mview_name = p_mv_name;
1326 BEGIN
1327 v_mv_name := UPPER(TRIM(p_mv_name));
1328 IF v_mv_name IS NULL OR LENGTH(v_mv_name) = 0 THEN
1329 RETURN;
1330 END IF;
1331
1332 -- Check whether the MV needs to be refreshed
1333 IF (p_refresh_mode IS NULL OR upper(p_refresh_mode) <> 'R') THEN
1334 -- Need to refresh in mode C
1335 v_bneed_refresh := TRUE;
1336 ELSE
1337 -- It is a resume operation
1338 -- First, find the refresh date in MTH_RUN_LOG in resume case
1339 SELECT max(to_date) into v_refresh_date_required
1340 FROM mth_run_log
1341 WHERE fact_table = 'MTH_ALL_MVS' AND db_global_name = v_unassigned_string;
1342 IF (v_refresh_date_required IS NULL) THEN
1343 -- Refresh the MV if there is no entry found in MTH_RUN_LOG
1344 v_bneed_refresh := TRUE;
1345 ELSE
1346 -- First get the last refresh date of the MV
1347 -- Then, compare the last refresh date of the MV with the date from the MTH_RUN_LOG
1348 -- to decide whether the MV needs to be refreshed
1349 OPEN getRefreshDate ( v_mv_name);
1350 FETCH getRefreshDate INTO v_last_refresh_date;
1351 IF (getRefreshDate%FOUND) THEN
1352 v_bneed_refresh := (v_last_refresh_date IS NULL OR v_last_refresh_date <= v_refresh_date_required);
1353 CLOSE getRefreshDate ;
1354 ELSE
1355 CLOSE getRefreshDate ;
1356 RAISE_APPLICATION_ERROR (-20001,
1357 'Could not find Materialized View ' || v_mv_name || '.');
1358 END IF;
1359 END IF;
1360 END IF;
1361
1362 IF v_bneed_refresh THEN
1363 DBMS_MVIEW.REFRESH(v_mv_name, p_method, p_rollback_seg);
1364 END IF;
1365 END REFRESH_ONE_MV;
1369 * Description :This procedure is used to insert the level_num column *
1366
1367 /* *****************************************************************************
1368 * Procedure :PUT_EQUIP_DENORM_LEVEL_NUM *
1370 * in the mth_equipment_denorm_d table *
1371 * File Name :MTHUTILB.PLS *
1372 * Visibility :Private *
1373 * Modification log : *
1374 * Author Date Change *
1375 * shanthi donthu 16-Jul-2008 Initial Creation *
1376 ***************************************************************************** */
1377
1378 PROCEDURE PUT_EQUIP_DENORM_LEVEL_NUM
1379 IS
1380 BEGIN
1381 UPDATE MTH_EQUIPMENT_DENORM_D SET LEVEL_NUM = (
1382 CASE WHEN EQUIPMENT_FK_KEY IS NOT NULL THEN 10
1383 ELSE CASE WHEN LEVEL9_LEVEL_KEY IS NOT NULL THEN 9
1384 ELSE CASE WHEN LEVEL8_LEVEL_KEY IS NOT NULL THEN 8
1385 ELSE CASE WHEN LEVEL7_LEVEL_KEY IS NOT NULL THEN 7
1386 ELSE CASE WHEN LEVEL6_LEVEL_KEY IS NOT NULL THEN 6
1387 ELSE CASE WHEN LEVEL5_LEVEL_KEY IS NOT NULL THEN 5
1388 ELSE CASE WHEN LEVEL4_LEVEL_KEY IS NOT NULL THEN 4
1389 ELSE CASE WHEN LEVEL3_LEVEL_KEY IS NOT NULL THEN 3
1390 ELSE CASE WHEN LEVEL2_LEVEL_KEY IS NOT NULL THEN 2
1391 ELSE CASE WHEN LEVEL1_LEVEL_KEY IS NOT NULL THEN 1
1392 END
1393 END
1394 END
1395 END
1396 END
1397 END
1398 END
1399 END
1400 END
1401 END )
1402 WHERE LEVEL_NUM IS NULL;
1403
1404 END PUT_EQUIP_DENORM_LEVEL_NUM;
1405
1406 /* *****************************************************************************
1407 * Procedure :update_equip_hrchy_gid *
1408 * Description :This procedue is used for updating the group_id column in *
1409 * the mth_equip_hierarchy table. The group id will be used to determine the *
1410 * sequence in which a particular record will be processed in the equipment SCD *
1411 * logic. The oldest relationships will have the lowest group id =1 and the new *
1412 * relationships will have higher group id. All the catch all relationships i.e.*
1413 * the relationship with parent = -99999 and effective date = 1-Jan-1900 will *
1414 * have group id defaulted to 1 inside the MTH_EQUIP_HRCHY_UA_ALL_MAP map. *
1415 * File Name :MTHUTILB.PLS *
1416 * Visibility :Public *
1417 * Parameters : none *
1418 * Modification log : *
1419 * Author Date Change *
1420 * Ankit Goyal 26-Aug-2008 Initial Creation *
1421 ***************************************************************************** */
1422 PROCEDURE update_equip_hrchy_gid
1423 IS
1424 /*variable to track # of conlficting rows*/
1425 l_max_gid NUMBER := 0;
1426 /*variable to get current maximum group id*/
1427 l_new_rows NUMBER := 0;
1428 v_new_ed DATE ;
1429 /*Variable to track the numnber of new rows */
1430 /*This cursor will fetch the number of rows that are in conflict. The rows
1431 are said to be in conflict when the incoming new rows have effective date
1432 less than the effective date of the current rows. This implies that the
1433 existing rows need to be processed after the new rows. This cursor fetches
1434 those rows and then logic in the program will manipulate those rows
1435 and increase theor group id.*/
1436 CURSOR cr_conflict_rows
1437 IS
1438 SELECT old_rows.hierarchy_id,
1439 old_rows.level_num ,
1440 old_rows.group_id ,
1441 old_rows.level_fk_key ,
1442 old_rows.effective_date
1443 FROM
1444 (SELECT hierarchy_id ,
1445 level_fk_key ,
1446 level_num ,
1447 effective_date effective_date,
1448 group_id
1449 FROM mth_equip_hierarchy
1450 WHERE group_id > 1
1451 /*group_id==1 are catch all rows. */
1452 GROUP BY hierarchy_id,
1453 level_fk_key ,
1454 level_num ,
1455 group_id ,
1456 effective_date
1457 ) old_rows ,
1458 (SELECT hierarchy_id,
1459 level_fk_key ,
1460 level_num ,
1461 effective_date ,
1462 parent_fk_key
1463 FROM mth_equip_hierarchy
1464 WHERE group_id IS NULL
1465 ) new_rows
1466 /*new relationships with group id as null */
1467 WHERE old_rows.hierarchy_id = new_rows.hierarchy_id
1468 AND old_rows.level_fk_key = new_rows.level_fk_key
1469 AND old_rows.level_num = new_rows.level_num
1470 AND old_rows.effective_date > new_rows.effective_date;
1471 /*The effective date comparison will tell us if there are conflits */
1472 /*This cursor fetches the row among the conflict rows with the minimum
1473 effective date.This tells us all the rows that will need to be updated so
1474 that they are processed in the correct groups*/
1475 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)
1476 IS
1477 SELECT new_ed FROM (
1478 SELECT old_rows.hierarchy_id ,
1479 old_rows.level_num ,
1480 MIN(old_rows.group_id) group_id,
1481 /*to skip group by */
1482 old_rows.level_fk_key ,
1483 MIN(old_rows.effective_date) effective_date,
1484 /*effecitve date of the old row */
1485 new_rows.effective_date new_ed
1486 /*effecitve date of the new row */
1487 FROM
1488 (SELECT hierarchy_id ,
1489 level_fk_key ,
1490 level_num ,
1491 effective_date effective_date,
1492 group_id
1493 FROM mth_equip_hierarchy
1494 WHERE group_id > 1
1495 /*group_id==1 are catch all rows. */
1496 ) old_rows ,
1497 (SELECT hierarchy_id,
1498 level_fk_key ,
1499 level_num ,
1500 effective_date ,
1501 parent_fk_key
1502 FROM mth_equip_hierarchy
1503 WHERE group_id IS NULL
1504 ) new_rows
1505 /*new relationships with group id as null */
1506 WHERE old_rows.hierarchy_id = new_rows.hierarchy_id
1507 AND old_rows.level_fk_key = new_rows.level_fk_key
1508 AND old_rows.level_num = new_rows.level_num
1509 AND old_rows.effective_date > new_rows.effective_date
1510 GROUP BY old_rows.hierarchy_id,
1511 old_rows.level_num ,
1512 old_rows.level_fk_key ,
1513 new_rows.effective_date) c_rows WHERE
1514 c_rows.effective_date =p_effective_date
1515 AND c_rows.hierarchy_id = p_hierarchy_id
1516 AND c_rows.level_fk_key = p_level_fk_key
1517 AND c_rows.level_num = p_level_num ;
1518 /*This cursor will fetch all the new rows for which the group id
1519 assignments have not been done.*/
1520 CURSOR cr_new_rows
1521 IS
1522 SELECT effective_date,
1523 hierarchy_id ,
1524 level_fk_key ,
1525 level_num
1526 FROM mth_equip_hierarchy
1527 WHERE group_id IS NULL;
1528 BEGIN
1529 FOR l_rows IN cr_conflict_rows
1530 LOOP
1531 /*All rows in conflict */
1532 OPEN cr_aggr_conflict_rows(l_rows.effective_date,l_rows.hierarchy_id, l_rows.level_fk_key,l_rows.level_num);
1533 FETCH cr_aggr_conflict_rows into v_new_ed ;
1534 /*All aggregated rows in conflict */
1535 /*This part of the logic deals with updating the group id
1536 of the new rows. */
1537 IF(cr_aggr_conflict_rows%FOUND) then
1538 /*set the group id of the new row equal to the group id of the old
1539 row which matches the effective date*/
1540 UPDATE mth_equip_hierarchy
1541 SET group_id = l_rows.group_id
1542 WHERE effective_date = v_new_ed
1543 /*This is the determining condition */
1544 AND hierarchy_id = l_rows.hierarchy_id
1545 AND level_fk_key = l_rows.level_fk_key
1546 AND level_num = l_rows.level_num;
1547 END IF;
1548 CLOSE cr_aggr_conflict_rows;
1549 /*Update the odl rows and increment the group id by 1 */
1550 UPDATE mth_equip_hierarchy
1551 SET group_id = l_rows.group_id + 1
1552 WHERE effective_date = l_rows.effective_date
1553 AND hierarchy_id = l_rows.hierarchy_id
1554 AND level_fk_key = l_rows.level_fk_key
1555 AND level_num = l_rows.level_num;
1556 END LOOP;
1557 /*This part of the logic will update any rows which did not cause a conflict
1558 with the old rows. This logic is necessary as the data can contain both the
1559 conflit rows and non conflict rows*/
1560 /*get the number of new rows remaining to be updated. */
1561 SELECT COUNT(* )
1562 INTO l_new_rows
1563 FROM mth_equip_hierarchy
1564 WHERE group_id IS NULL;
1565 IF l_new_rows > 0 THEN
1566 /*if new rows found */
1567 FOR new_rows IN cr_new_rows
1568 LOOP
1569 SELECT MAX(group_id)
1570 INTO l_max_gid
1571 FROM mth_equip_hierarchy
1572 WHERE hierarchy_id = new_rows.hierarchy_id
1573 AND level_fk_key = new_rows.level_fk_key
1574 AND level_num = new_rows.level_num;
1575 /*update the new rows gorup_id column and set it = group_id of old row + 1*/
1576 UPDATE mth_equip_hierarchy
1577 SET group_id = l_max_gid + 1
1578 WHERE hierarchy_id = new_rows.hierarchy_id
1579 AND level_fk_key = new_rows.level_fk_key
1580 AND level_num = new_rows.level_num
1581 AND effective_date = new_rows.effective_date;
1582 END LOOP;
1583 END IF;
1584 --handle exceptions
1585 EXCEPTION
1586 WHEN NO_DATA_FOUND THEN
1587 RAISE_APPLICATION_ERROR (-20001,
1588 'Exception has occured');
1589
1590 END update_equip_hrchy_gid;
1591
1592 /* *****************************************************************************
1593 * Function :get_min_max_gid *
1594 * Description :This finction returns the minimum or maximum group id in the *
1595 * Equipment hierarchy table. *
1596 * File Name :MTHUTILB.PLS *
1597 * Visibility :Public *
1598 * Parameters : Mode Number. Mode= 1 Minimum, Mode =2 Maximum *
1599 * Modification log : *
1600 * Author Date Change *
1601 * Ankit Goyal 26-Aug-2008 Initial Creation *
1602 ***************************************************************************** */
1603
1604 FUNCTION get_min_max_gid
1605 (minmax IN NUMBER)
1606 RETURN NUMBER
1607 IS
1608 v_minmax NUMBER := 0;
1609 BEGIN
1610 IF minmax = 1 THEN
1611 SELECT MIN(group_id)
1612 INTO v_minmax
1613 FROM mth_equip_hierarchy;
1614 ELSE
1615 SELECT MAX(group_id)
1616 INTO v_minmax
1617 FROM mth_equip_hierarchy;
1618 END IF;
1619
1620 RETURN v_minmax;
1621 END get_min_max_gid;
1622
1623 /* *****************************************************************************
1624 * Procedure :switch_column_default_value *
1625 * Description :This procedure will determine the current value of the *
1626 * processing_flag of the table, issue an alter table statement to switch *
1627 * the default values to another (1 to 2, or 2 to 1,) and return the *
1628 * current value. If there are no data in the table, do nothing and return *
1629 * 0. *
1630 * File Name :MTHUTILB.PLS *
1631 * Visibility :Public *
1632 * Parameters : *
1633 * p_table_name: table name *
1634 * p_current_processing_flag: the current value of processing_flag *
1635 * It could be 1, or 2 for normal case. *
1636 * If it is 0, then no data is available *
1637 * the table. So no process is needed. *
1638 * Modification log : *
1639 * Author Date Change: Yong Feng 10/2/08 Initial creation *
1640 ***************************************************************************** */
1641
1642 PROCEDURE switch_column_default_value (
1643 p_table_name IN VARCHAR2,
1644 p_current_processing_flag OUT NOCOPY NUMBER)
1645 IS
1646 v_stmt varchar2(500);
1647 v_current_value NUMBER;
1648 v_new_value NUMBER;
1649 e_wrong_value EXCEPTION;
1650 v_schema_name VARCHAR2(100);
1651 v_status VARCHAR2(30) ;
1652 v_industry VARCHAR2(30) ;
1653 BEGIN
1654 IF p_table_name IS NULL OR LENGTH(p_table_name) = 0 THEN
1655 RETURN;
1656 END IF;
1657
1658 IF (NOT FND_INSTALLATION.GET_APP_INFO(
1659 application_short_name => 'MTH'
1660 , status => v_status
1661 , industry => v_industry
1662 , oracle_schema => v_schema_name)) THEN
1663 RAISE_APPLICATION_ERROR (-20002,
1664 'Could not find MTH product.');
1665 END IF;
1666
1667 v_stmt := 'SELECT processing_flag FROM ' ||
1668 v_schema_name || '.' || p_table_name ||
1669 ' WHERE rownum < 2';
1670 EXECUTE IMMEDIATE v_stmt INTO v_current_value;
1671
1672 IF v_current_value = 1 THEN
1673 v_new_value := 2;
1674 ELSIF v_current_value = 2 THEN
1675 v_new_value := 1;
1676 ELSE RAISE e_wrong_value;
1677 END IF;
1678
1679 v_stmt := 'ALTER TABLE ' || v_schema_name || '.' || p_table_name ||
1680 ' MODIFY PROCESSING_FLAG DEFAULT ' || v_new_value;
1681 EXECUTE IMMEDIATE v_stmt;
1682 p_current_processing_flag := v_current_value;
1683 EXCEPTION
1684 WHEN NO_DATA_FOUND THEN
1685 p_current_processing_flag := 0;
1686 WHEN e_wrong_value THEN
1687 RAISE_APPLICATION_ERROR (-20001, 'The table ' || p_table_name ||
1688 ' contains wrong value ' || v_current_value ||
1689 ' in column PROCESSING_FLAG.');
1690 END switch_column_default_value;
1691
1692
1693 /* *****************************************************************************
1694 * Procedure :truncate_table_partition *
1695 * Description :This procedure will truncate the partition corresponding *
1696 * to the value of p_current_processing_flag. *
1697 * File Name :MTHUTILB.PLS *
1698 * Visibility :Public *
1699 * Parameters : *
1700 * p_table_name: table name *
1701 * p_current_processing_flag: Used to determine the partition to be *
1702 * truncated. Truncate p1 if the value is 1; truncate p2 if 2. *
1703 * Modification log : *
1704 * Author Date Change: Yong Feng 10/2/08 Initial creation *
1705 ***************************************************************************** */
1706
1707 PROCEDURE truncate_table_partition (
1708 p_table_name IN VARCHAR2,
1709 p_current_processing_flag IN NUMBER)
1710 IS
1711 v_stmt VARCHAR2(500);
1712 v_schema_name VARCHAR2(100);
1713 v_status VARCHAR2(30) ;
1714 v_industry VARCHAR2(30) ;
1715 v_partition_name VARCHAR2(30) ;
1716
1717 BEGIN
1718 IF (NOT FND_INSTALLATION.GET_APP_INFO(
1719 application_short_name => 'MTH'
1720 , status => v_status
1721 , industry => v_industry
1722 , oracle_schema => v_schema_name)) THEN
1723 RAISE_APPLICATION_ERROR (-20002,
1724 'Could not find MTH product.');
1725 END IF;
1726
1727 IF (p_current_processing_flag = 1) THEN
1728 v_partition_name := 'P1';
1729 ELSIF p_current_processing_flag = 2 THEN
1730 v_partition_name := 'P2';
1731 ELSE
1732 RAISE_APPLICATION_ERROR (-20003, 'The table ' || p_table_name ||
1733 ' cannot have the value ' || p_current_processing_flag ||
1734 ' in column PROCESSING_FLAG.');
1735 END IF;
1736 v_stmt := 'ALTER TABLE '||v_schema_name||'.'||p_table_name ||
1737 ' TRUNCATE PARTITION ' || v_partition_name;
1738 EXECUTE IMMEDIATE v_stmt;
1739 END truncate_table_partition;
1740
1741 /* *****************************************************************************
1742 * Procedure :mth_run_log_pre_load *
1743 * Description :This procedure will log entries when a map is run taking *
1744 * transaction id and populating from_txn_id and to_txn_id *
1745 * File Name :MTHUTILS.PLS *
1746 * Visibility :Public *
1747 * Modification log : *
1748 * Author Date Change: Vivek Sharma 21-Jan-2009 Initial creation *
1749 ***************************************************************************** */
1750
1751 PROCEDURE mth_run_log_pre_load( p_fact_table IN VARCHAR2,
1752 p_db_global_name IN VARCHAR2,
1753 p_run_mode IN VARCHAR2,
1754 p_run_start_date IN DATE,
1755 p_is_fact IN NUMBER,
1756 p_to_date IN DATE,
1757 p_to_txn_id IN NUMBER)
1758 IS
1759 --local variable declation
1760
1761 l_fact_table mth_run_log.fact_table%TYPE;--fact table name
1762 l_ebs_organization_id mth_run_log.ebs_organization_id%TYPE;
1763 l_ebs_organization_code mth_run_log.ebs_organization_code%TYPE;
1764 l_from_date mth_run_log.from_date%TYPE;--from date of the run
1765 l_to_date mth_run_log.to_date%TYPE;--to date of the run
1766 l_source mth_run_log.source%TYPE;--process or discrete. Not used currently
1767 l_system_fk_key mth_systems_setup.system_pk_key%TYPE;
1768 l_creation_date mth_run_log.creation_date%TYPE;--who column
1769 l_last_update_date mth_run_log.last_update_date%TYPE;--who column
1770 l_creation_system_id mth_run_log.creation_system_id%TYPE;--who column
1771 l_last_update_system_id mth_run_log.last_update_system_id%TYPE;--who column
1772 --l_sysdate to holding the current date
1773 l_sysdate DATE := sysdate;--target system sysdate
1774 --l_mode is used to determine the run type , initial 0 or incremental 1
1775 l_mode NUMBER := 0;--initial default
1776 l_plant_start_date DATE; --Plant end date
1777 --Hub organization code
1778 l_hub_organization_code mth_run_log.hub_organization_code%TYPE;
1779 --TXN IDS
1780 l_to_txn_id mth_run_log.to_txn_id%TYPE;
1781 l_from_txn_id mth_run_log.from_txn_id%TYPE;
1782
1783 --cursor for iterating through the ebs organizations in mth_plants_d
1784 --the rows in the mth_run_log will be at organization granularity
1785 CURSOR c_ebs_orgs IS
1786 SELECT ebs_organization_id,organization_code,
1787 source,plant_pk,system_fk_key,from_date
1788 FROM mth_plants_d, mth_systems_setup,mth_organizations_l
1789 WHERE system_pk_key = system_fk_key
1790 AND system_pk = p_db_global_name
1791 AND NVL(to_date,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)--pick active plants only
1792 AND plant_fk_key=plant_pk_key;
1793
1794 BEGIN
1795 IF p_run_mode = 'INITIAL' THEN--Initial load
1796 l_from_txn_id := 0;
1797 --delete the rows from run log
1798 DELETE FROM mth_run_log WHERE fact_table = p_fact_table;
1799
1800 END IF;
1801 --initialize the local variables and who columns
1802 l_fact_table := p_fact_table;
1803 l_last_update_system_id := -99999;
1804 l_last_update_date := l_sysdate;
1805 l_to_date := p_to_date;
1806 l_to_txn_id := p_to_txn_id;
1807
1808 --initialize the global start date
1809 IF p_is_fact = 0 THEN--for dimensions only
1810 l_from_date := To_Date('01-01-1900','MM-DD-YYYY');
1811 END IF;
1812
1813 --iterate through the cursor
1814 FOR l_orgs IN c_ebs_orgs
1815 LOOP
1816
1817 --initialize the variables for current cursor value
1818 l_ebs_organization_id := l_orgs.ebs_organization_id;
1819 l_source := l_orgs.source;--pick source column from the plants
1820 l_ebs_organization_code := l_orgs.organization_code;
1821 l_creation_date := l_sysdate;
1822 l_creation_system_id := -1;
1823
1824 IF p_is_fact = 1 THEN--for facts only
1825 l_from_date := l_orgs.from_date;--run start date= plant start date
1826 END IF;
1827
1828 l_plant_start_date := l_orgs.from_date;
1829 l_hub_organization_code := l_orgs.plant_pk;
1830
1831
1832 IF l_orgs.ebs_organization_id is null THEN
1833 /* We are dealing with non-ebs org configured to the passed system*/
1834 /* Check if there are records for non-ebs organizations from same system and same fact and same plant */
1835 SELECT COUNT(*)
1836 INTO l_mode
1837 FROM mth_run_log
1838 WHERE fact_table = l_fact_table
1839 AND db_global_name = p_db_global_name
1840 AND hub_organization_code = l_orgs.plant_pk;
1841
1842 IF l_mode = 0 OR UPPER(p_run_mode) = 'INITIAL' THEN /* Initial Load */
1843
1844 --statement for insert
1845
1846 INSERT INTO mth_run_log (fact_table, ebs_organization_id,
1847 ebs_organization_code,from_date,to_date, source, db_global_name,
1848 creation_date,last_update_date,creation_system_id,
1849 last_update_system_id,plant_start_date,hub_organization_code,from_txn_id,to_txn_id)
1850 VALUES(l_fact_table,l_ebs_organization_id,l_ebs_organization_code,
1851 l_from_date,l_to_date,l_source,p_db_global_name,l_creation_date,
1852 l_last_update_date,l_creation_system_id,l_last_update_system_id,
1853 l_plant_start_date,l_hub_organization_code,l_from_txn_id,l_to_txn_id);
1854
1855 ELSE
1856 /* update all non_ebs organizations from same system and plant with to_date as the passed date */
1857 --Custom Logic for the time dimension
1858 IF p_fact_table = 'MTH_WORKDAY_SHIFTS_D'
1859 THEN
1860 UPDATE mth_run_log
1861 SET from_date = p_run_start_date
1862 WHERE
1863 fact_table = p_fact_table and db_global_name=p_db_global_name;
1864 END IF ;
1865
1866 --statment for update
1867 UPDATE mth_run_log
1868 SET TO_DATE = l_to_date,
1869 TO_TXN_ID = l_to_txn_id,
1870 LAST_UPDATE_DATE = l_last_update_date,
1871 LAST_UPDATE_SYSTEM_ID =l_last_update_system_id
1872 WHERE
1873 fact_table =l_fact_table
1874 AND db_global_name = p_db_global_name
1875 AND hub_organization_code = l_hub_organization_code;
1876
1877
1878 END IF; /* END of Initial VS Incremental */
1879
1880 ELSE
1881 /* We are dealing with EBS Organizations */
1882
1883 --determine if there are any rows in the mth_run_log for
1884 --the fact_table corresponding to the org
1885 SELECT COUNT(*)
1886 INTO l_mode
1887 FROM mth_run_log
1888 WHERE fact_table = l_fact_table
1889 AND ebs_organization_id = l_orgs.ebs_organization_id
1890 AND db_global_name = p_db_global_name
1891 AND hub_organization_code = l_orgs.plant_pk;
1892
1893 --l_mode = 0 means that it is a initial run. p_run_mode is
1894 --for forceful execution of the initial load
1895
1896 IF l_mode = 0 OR UPPER(p_run_mode) = 'INITIAL' THEN--initial load
1897
1898 --initialize the variables for initial load
1899 l_creation_date := l_sysdate;
1900 l_creation_system_id := -1;
1901
1902 --statement for insert
1903
1904 INSERT INTO mth_run_log (fact_table, ebs_organization_id,
1905 ebs_organization_code,from_date,to_date, source, db_global_name,
1906 creation_date,last_update_date,creation_system_id,
1907 last_update_system_id,plant_start_date,hub_organization_code,from_txn_id,to_txn_id)
1908 VALUES(l_fact_table,l_ebs_organization_id,l_ebs_organization_code,
1909 l_from_date,l_to_date,l_source,p_db_global_name,l_creation_date,
1910 l_last_update_date,l_creation_system_id,l_last_update_system_id,
1911 l_plant_start_date,l_hub_organization_code,l_from_txn_id,l_to_txn_id);
1912
1913
1914
1915 --if the above condition fails then update the row
1916 ELSE--incremental load
1917
1918 --Custom Logic for the time dimension
1919 IF p_fact_table = 'MTH_WORKDAY_SHIFTS_D'
1920 THEN
1921 UPDATE mth_run_log
1922 SET from_date = p_run_start_date
1923 WHERE
1924 fact_table = p_fact_table;
1925 END IF ;
1926 --statment for update
1927 UPDATE mth_run_log
1928 SET TO_DATE = l_to_date,
1929 TO_TXN_ID = l_to_txn_id,
1930 LAST_UPDATE_DATE = l_last_update_date,
1931 LAST_UPDATE_SYSTEM_ID =l_last_update_system_id
1932 WHERE
1933 fact_table =l_fact_table
1934 AND source =l_source
1935 AND db_global_name = p_db_global_name
1936 AND ebs_organization_id = l_ebs_organization_id
1937 AND hub_organization_code = l_hub_organization_code;
1938
1939 --end of if clause
1940 END IF; /* END of Initial VS Incremental */
1941
1942 END IF; /* End of EBS VS NON-EBS */
1943 --end of the for loop
1944 END LOOP;
1945
1946 --handle exceptions
1947 EXCEPTION
1948 WHEN NO_DATA_FOUND THEN
1949 RAISE_APPLICATION_ERROR (-20001,
1950 'Exception has occured');
1951
1952 END mth_run_log_pre_load;
1953
1954
1955
1956 /* ****************************************************************************
1957 * Function :GET_ATTR_EXT_COLUMN *
1958 * Description :This function is used to retrive column name in *
1959 * MTH_EQUIPMENTS_EXT_B that stores the value of an given *
1960 * attribute name. *
1961 * File Name :MTHUTILB.PLS *
1962 * Visibility :Public
1963 * Parameters : *
1964 * p_attr_name: Attribute name *
1965 * Return : COLUMN_NAME - Column name in MTH_EQUIPMENTS_EXT_B that *
1966 * stores the value of an given attribute name. *
1967 * Modification log : *
1968 * Author Date Change: Yong Feng 14-Jul-2009 Initial Creation *
1969 ******************************************************************************/
1970 FUNCTION GET_ATTR_EXT_COLUMN(p_attr_name IN VARCHAR2,
1971 p_att_grp_name IN VARCHAR2 DEFAULT 'SPECIFICATIONS'
1972 ) RETURN VARCHAR2 IS
1973 cursor getColumnName (p_attr_name in varchar2,
1974 p_att_grp_name IN VARCHAR2) is
1975 SELECT DATABASE_COLUMN
1976 FROM EGO_ATTRS_V
1977 WHERE application_id = 9001 AND
1978 ATTR_GROUP_TYPE = 'MTH_EQUIPMENTS_GROUP' AND
1979 attr_group_name = p_att_grp_name AND
1980 ATTR_NAME = p_attr_name;
1981 v_attr_dbcol varchar2(30) := NULL;
1982 BEGIN
1983
1984 IF p_attr_name IS NULL OR LENGTH(p_attr_name) = 0 OR
1985 p_att_grp_name IS NULL OR LENGTH(p_att_grp_name) = 0 THEN
1986 RETURN NULL;
1987 END IF;
1988
1989 OPEN getColumnName ( p_attr_name, p_att_grp_name );
1990 FETCH getColumnName INTO v_attr_dbcol;
1991 CLOSE getColumnName;
1992 RETURN v_attr_dbcol;
1993 END GET_ATTR_EXT_COLUMN;
1994
1995
1996 /* ****************************************************************************
1997 * Function :GET_ATTR_GROUP_ID *
1998 * Description :This function is used to retrive attribute group ID *
1999 * from EGO_ATTR_GROUPS_V for a given attribute group name. *
2000 * File Name :MTHUTILB.PLS *
2001 * Visibility :Public
2002 * Parameters : *
2003 * p_att_grp_name: Attribute group name *
2004 * Return : Attribute group id for the specified attribute group name *
2005 * Modification log : *
2006 * Author Date Change: Yong Feng 26-Aug-2009 Initial Creation *
2007 ******************************************************************************/
2008 FUNCTION GET_ATTR_GROUP_ID(p_att_grp_name IN VARCHAR2 DEFAULT 'SPECIFICATIONS'
2009 ) RETURN NUMBER IS
2010 cursor getAttrGroupId (p_att_grp_name IN VARCHAR2) is
2011 SELECT ATTR_GROUP_ID
2012 FROM EGO_ATTR_GROUPS_V
2013 WHERE application_id = 9001 AND
2014 ATTR_GROUP_TYPE = 'MTH_EQUIPMENTS_GROUP' AND
2015 attr_group_name = p_att_grp_name;
2016 v_attr_grp_id NUMBER := NULL;
2017 BEGIN
2018
2019 IF p_att_grp_name IS NULL OR LENGTH(p_att_grp_name) = 0 THEN
2020 RETURN NULL;
2021 END IF;
2022
2023 OPEN getAttrGroupId ( p_att_grp_name );
2024 FETCH getAttrGroupId INTO v_attr_grp_id;
2025 CLOSE getAttrGroupId;
2026 RETURN v_attr_grp_id;
2027 END GET_ATTR_GROUP_ID;
2028
2029
2030 /* ****************************************************************************
2031 * Procedure :GET_UPPER_LOWER_LIMITS *
2032 * Description Find and return the UPPER and LOWER limit for the *
2033 * equipment specified. *
2034 * File Name :MTHUTILB.PLS *
2035 * Visibility :Public
2036 * Parameters : *
2037 * p_equipment_fk_key: Equipment fk key *
2038 * p_attr_name: Attribute name *
2039 * p_att_grp_name: attribute group name *
2040 * p_low_lim_name: attribute name in EGO_ATTRS_V *
2041 * p_upp_lim_name: another attribute name in EGO_ATTRS_V *
2042 * p_ret_LOWER_LIMIT : Lower limit returned *
2043 * p_ret_UPPER_LIMIT : Upper limit returned *
2044 * Modification log : *
2045 * Author Date Change: Yong Feng 14-Jul-2009 Initial Creation *
2046 ******************************************************************************/
2047 PROCEDURE GET_UPPER_LOWER_LIMITS(p_equipment_fk_key IN NUMBER,
2048 p_attr_name in VARCHAR2,
2049 p_att_grp_name IN VARCHAR2
2050 DEFAULT 'SPECIFICATIONS',
2051 p_low_lim_name IN VARCHAR2
2052 DEFAULT 'LLIMIT',
2053 p_upp_lim_name IN VARCHAR2
2054 DEFAULT 'ULIMIT',
2055 p_ret_LOWER_LIMIT OUT NOCOPY NUMBER,
2056 p_ret_UPPER_LIMIT OUT NOCOPY NUMBER) IS
2057
2058 TYPE cur_typ IS REF CURSOR;
2059 c cur_typ;
2060 query_str VARCHAR2(200);
2061
2062 v_upp_column_name varchar2(30);
2063 v_low_column_name varchar2(30);
2064 v_parameter_col_name varchar2(30);
2065 v_attr_grp_id number;
2066
2067 BEGIN
2068 v_attr_grp_id := GET_ATTR_GROUP_ID(p_att_grp_name);
2069 v_parameter_col_name := GET_ATTR_EXT_COLUMN('PARAMETER', p_att_grp_name);
2070 v_low_column_name := GET_ATTR_EXT_COLUMN(p_low_lim_name, p_att_grp_name);
2071 v_upp_column_name := GET_ATTR_EXT_COLUMN(p_upp_lim_name, p_att_grp_name);
2072 p_ret_LOWER_LIMIT := NULL;
2073 p_ret_UPPER_LIMIT := NULL;
2074
2075 -- RETURN because LLIMIT or ULIMIT has not been setup properly
2076 if (v_low_column_name is NULL or LENGTH(v_low_column_name) = 0 or
2077 v_upp_column_name is NULL or LENGTH(v_upp_column_name) = 0 or
2078 v_parameter_col_name is NULL or LENGTH(v_parameter_col_name) = 0 or
2079 v_attr_grp_id is NULL ) THEN
2080 RETURN;
2081 END IF;
2082
2083 query_str := 'SELECT ' || v_low_column_name || ', ' || v_upp_column_name ||
2084 ' FROM mth_equipments_ext_b' ||
2085 ' WHERE equipment_pk_key = :e_id AND ' ||
2086 v_parameter_col_name || ' = :attr_name AND ' ||
2087 ' attr_group_id = :attr_group_id';
2088 OPEN c FOR query_str USING p_equipment_fk_key, p_attr_name, v_attr_grp_id;
2089 FETCH c INTO p_ret_LOWER_LIMIT, p_ret_UPPER_LIMIT;
2090 CLOSE c;
2091 END GET_UPPER_LOWER_LIMITS;
2092
2093
2094 /* ****************************************************************************
2095 * Procedure :GET_LATEST_READINGS *
2096 * Description Find the latest readings bwtween two readings *
2097 * File Name :MTHUTILB.PLS *
2098 * Visibility :Private
2099 * Parameters : *
2100 * p_tag_data1: One tag data *
2101 * p_reading_time1: One reading time *
2102 * p_tag_data2: Another input tag data and return the latest one *
2103 * among two *
2104 * p_reading_time2: Another input reading time and return the *
2105 * latest one among two *
2106 * Modification log : *
2107 * Author Date Change: Yong Feng 14-Jul-2009 Initial Creation *
2108 ******************************************************************************/
2109 PROCEDURE GET_LATEST_READINGS(p_tag_data1 IN VARCHAR2,
2110 p_reading_time1 in DATE,
2111 p_tag_data2 IN OUT NOCOPY VARCHAR2,
2112 p_reading_time2 IN OUT NOCOPY DATE) IS
2113
2114 BEGIN
2115 IF p_reading_time2 IS NULL OR
2116 p_reading_time1 IS NOT NULL and p_reading_time2 IS NOT NULL AND
2117 p_reading_time1 > p_reading_time2
2118 THEN
2122 END GET_LATEST_READINGS;
2119 p_tag_data2 := p_tag_data1;
2120 p_reading_time2 := p_reading_time1;
2121 END IF;
2123
2124
2125 /* ****************************************************************************
2126 * Function :GET_PREV_TAG_READING *
2127 * Description :This function is used to retrive the previous reading *
2128 * from mth_tag_readings_stg, mth_tag_readings, *
2129 and mth_tag_readings_err *
2130 * for the given tag_code and reading time is earlier than *
2131 * the reading time specified and within the range specified *
2132 * by the range_in_hour *
2133 * File Name :MTHUTILB.PLS *
2134 * Visibility :Public
2135 * Parameters : *
2136 * p_tag_code: TAG code name *
2137 * p_reading_time: current reading_time *
2138 * p_range_in_hours: Number of hours, which is used to limit *
2139 * the search of the prevous reading to the range that is earlier *
2140 * than the reading_time and later than *
2141 * reading_time + p_range_in_hours / 24. *
2142 * Return : Previous tag reading for the same tag *
2143 * Modification log : *
2144 * Author Date Change: Yong Feng 14-Jul-2009 Initial Creation *
2145 ******************************************************************************/
2146 FUNCTION GET_PREV_TAG_READING(p_tag_code IN VARCHAR2,
2147 p_reading_time IN DATE,
2148 p_range_in_hours IN NUMBER DEFAULT NULL)
2149 RETURN VARCHAR2 IS
2150 v_end_date DATE;
2151 v_pre_tag_data_stg VARCHAR2(255) := null;
2152 v_pre_reading_time_stg date := null;
2153 v_processed_flag NUMBER;
2154 v_pre_tag_data VARCHAR2(255) := null;
2155 v_pre_reading_time date := null;
2156 v_pre_tag_data_err VARCHAR2(255) := null;
2157 v_pre_reading_time_err date := null;
2158 v_pre_tag_data_ret VARCHAR2(255) := null;
2159 v_pre_reading_time_ret date := null;
2160 v_pre_tag_data_ret2 VARCHAR2(255) := null;
2161 v_pre_reading_time_ret2 date := null;
2162
2163 v_found_pre_in_readings boolean := false;
2164 v_found_pre_in_err boolean := false;
2165 v_found_pre_in_stg boolean := false;
2166
2167 CURSOR c_readings_stg (p_tag_code IN VARCHAR2,
2168 p_start_reading_time IN DATE,
2169 p_end_reading_time IN DATE) IS
2170 SELECT tag_data, reading_time, processed_flag
2171 FROM mth_tag_readings_stg
2172 WHERE tag_code = p_tag_code AND
2173 reading_time < p_start_reading_time AND
2174 reading_time >= p_end_reading_time
2175 ORDER BY reading_time desc;
2176
2177 CURSOR c_readings (p_tag_code IN VARCHAR2,
2178 p_start_reading_time IN DATE,
2179 p_end_reading_time IN DATE) IS
2180 SELECT tag_data, reading_time
2181 FROM mth_tag_readings
2182 WHERE tag_code = p_tag_code AND
2183 reading_time < p_start_reading_time AND
2184 reading_time >= p_end_reading_time
2185 ORDER BY reading_time desc;
2186
2187 CURSOR c_readings_err (p_tag_code IN VARCHAR2,
2188 p_start_reading_time IN DATE,
2189 p_end_reading_time IN DATE) IS
2190 SELECT tag_data, reading_time
2191 FROM mth_tag_readings_err
2192 WHERE tag_code = p_tag_code AND
2193 reading_time < p_start_reading_time AND
2194 reading_time >= p_end_reading_time
2195 ORDER BY reading_time desc;
2196
2197 BEGIN
2198 IF p_range_in_hours is not NULL and p_range_in_hours > 0 THEN
2199 v_end_date := p_reading_time - p_range_in_hours / 24;
2200 ELSE
2201 v_end_date := p_reading_time - 36500;
2202 END IF;
2203
2204 OPEN c_readings_stg ( p_tag_code, p_reading_time, v_end_date);
2205 FETCH c_readings_stg INTO
2206 v_pre_tag_data_stg, v_pre_reading_time_stg, v_processed_flag;
2207 v_found_pre_in_stg := c_readings_stg%FOUND;
2208 CLOSE c_readings_stg;
2209
2210 -- If found the previous reading in staging table and the data
2211 -- has not been processed yet, then that is the latest.
2212 IF v_found_pre_in_stg = true THEN
2213 RETURN v_pre_tag_data_stg;
2214 END IF;
2215
2216 -- Otherwise, look into readings and err tables.
2217 OPEN c_readings ( p_tag_code, p_reading_time, v_end_date);
2218 FETCH c_readings INTO v_pre_tag_data, v_pre_reading_time;
2219 v_found_pre_in_readings := c_readings%FOUND;
2220 CLOSE c_readings;
2221
2222 OPEN c_readings_err ( p_tag_code, p_reading_time, v_end_date);
2223 FETCH c_readings_err INTO v_pre_tag_data_err, v_pre_reading_time_err;
2224 v_found_pre_in_err := c_readings_err%FOUND;
2225 CLOSE c_readings_err;
2226
2227 GET_LATEST_READINGS(v_pre_tag_data_err,
2228 v_pre_reading_time_err,
2229 v_pre_tag_data,
2230 v_pre_reading_time);
2231 return v_pre_tag_data;
2232 END GET_PREV_TAG_READING;
2233
2234
2235
2236
2237 /* ****************************************************************************
2238 * Procedure :GET_PREV_TAG_READING_INFO *
2239 * Description :This function is used to retrive the previous reading *
2240 * from mth_tag_readings_stg, mth_tag_readings, *
2241 and mth_tag_readings_err *
2242 * for the given tag_code and reading time is earlier than *
2243 * the reading time specified and within the range specified *
2247 * Parameters : *
2244 * by the range_in_hour *
2245 * File Name :MTHUTILB.PLS *
2246 * Visibility :Public
2248 * p_tag_code: TAG code name *
2249 * p_reading_time: current reading_time *
2250 * p_range_in_hours: Number of hours, which is used to limit *
2251 * the search of the prevous reading to the range that is earlier *
2252 * than the reading_time and later than *
2253 * reading_time + p_range_in_hours / 24. *
2254 * p_pre_tag_data: Previous tag reading for the same tag code *
2255 * p_pre_reading_time: reading time for the previous tag reading *
2256 * p_pre_eqp_availability: The availability_flag in the
2257 * mth_equipment_shifts_d table *
2258 * Y - available *
2259 * N - not available *
2260 * NULL - no schedule available *
2261 * Modification log : *
2262 * Author Date Change: Yong Feng 14-Jul-2009 Initial Creation *
2263 ******************************************************************************/
2264 PROCEDURE GET_PREV_TAG_READING_INFO(p_tag_code IN VARCHAR2,
2265 p_reading_time IN DATE,
2266 p_range_in_hours IN NUMBER DEFAULT NULL,
2267 p_pre_tag_data OUT NOCOPY VARCHAR2,
2268 p_pre_reading_time OUT NOCOPY DATE,
2269 p_pre_eqp_availability OUT NOCOPY VARCHAR2)
2270 IS
2271 v_end_date DATE;
2272 v_pre_tag_data_stg VARCHAR2(255) := null;
2273 v_pre_reading_time_stg date := null;
2274 v_processed_flag NUMBER;
2275 v_pre_tag_data VARCHAR2(255) := null;
2276 v_pre_reading_time date := null;
2277 v_pre_tag_data_err VARCHAR2(255) := null;
2278 v_pre_reading_time_err date := null;
2279 v_pre_tag_data_ret VARCHAR2(255) := null;
2280 v_pre_reading_time_ret date := null;
2281 v_pre_tag_data_ret2 VARCHAR2(255) := null;
2282 v_pre_reading_time_ret2 date := null;
2283 v_pre_eqp_availability varchar2(1) := null;
2284
2285 v_found_pre_in_readings boolean := false;
2286 v_found_pre_in_err boolean := false;
2287 v_found_pre_in_stg boolean := false;
2288 v_equipment_fk_key number;
2289
2290 CURSOR c_readings_stg (p_tag_code IN VARCHAR2,
2291 p_start_reading_time IN DATE,
2292 p_end_reading_time IN DATE) IS
2293 SELECT tag_data, reading_time, equipment_fk_key
2294 FROM mth_tag_readings_stg
2295 WHERE tag_code = p_tag_code AND
2296 reading_time < p_start_reading_time AND
2297 reading_time >= p_end_reading_time
2298 ORDER BY reading_time desc;
2299
2300 CURSOR c_readings (p_tag_code IN VARCHAR2,
2301 p_start_reading_time IN DATE,
2302 p_end_reading_time IN DATE) IS
2303 SELECT tag_data, reading_time, equipment_fk_key
2304 FROM mth_tag_readings
2305 WHERE tag_code = p_tag_code AND
2306 reading_time < p_start_reading_time AND
2307 reading_time >= p_end_reading_time
2308 ORDER BY reading_time desc;
2309
2310 CURSOR c_readings_err (p_tag_code IN VARCHAR2,
2311 p_start_reading_time IN DATE,
2312 p_end_reading_time IN DATE) IS
2313 SELECT tag_data, reading_time, equipment_fk_key
2314 FROM mth_tag_readings_err
2315 WHERE tag_code = p_tag_code AND
2316 reading_time < p_start_reading_time AND
2317 reading_time >= p_end_reading_time
2318 ORDER BY reading_time desc;
2319
2320 cursor c_avail (p_equipment_fk_key IN NUMBER, p_reading_time IN DATE ) IS
2321 SELECT s.availability_flag
2322 FROM MTH_EQUIPMENT_SHIFTS_D s
2323 WHERE s.equipment_fk_key = p_equipment_fk_key AND
2324 p_reading_time BETWEEN s.from_date AND s.To_Date;
2325
2326
2327 BEGIN
2328 IF p_range_in_hours is not NULL and p_range_in_hours > 0 THEN
2329 v_end_date := p_reading_time - p_range_in_hours / 24;
2330 ELSE
2331 v_end_date := p_reading_time - 36500;
2332 END IF;
2333
2334 OPEN c_readings_stg ( p_tag_code, p_reading_time, v_end_date);
2335 FETCH c_readings_stg INTO
2336 v_pre_tag_data, v_pre_reading_time, v_equipment_fk_key;
2337 v_found_pre_in_stg := c_readings_stg%FOUND;
2338 CLOSE c_readings_stg;
2339
2340 -- If found the previous reading in staging table and the data
2341 -- has not been processed yet, then that is the latest.
2342 -- Otherwise, look into readings and err tables.
2343 IF v_found_pre_in_stg = false THEN
2344 begin
2345
2346 OPEN c_readings ( p_tag_code, p_reading_time, v_end_date);
2347 FETCH c_readings INTO v_pre_tag_data, v_pre_reading_time,
2348 v_equipment_fk_key;
2349 v_found_pre_in_readings := c_readings%FOUND;
2350 CLOSE c_readings;
2351
2352 OPEN c_readings_err ( p_tag_code, p_reading_time, v_end_date);
2353 FETCH c_readings_err INTO v_pre_tag_data_err, v_pre_reading_time_err,
2354 v_equipment_fk_key;
2355 v_found_pre_in_err := c_readings_err%FOUND;
2356 CLOSE c_readings_err;
2357
2358 GET_LATEST_READINGS(v_pre_tag_data_err,
2359 v_pre_reading_time_err,
2360 v_pre_tag_data,
2361 v_pre_reading_time);
2362 end;
2363 end if;
2364
2365 IF ( v_pre_tag_data is not null and v_pre_reading_time is not NULL ) THEN
2366 OPEN c_avail (v_equipment_fk_key, v_pre_reading_time);
2367 FETCH c_avail INTO v_pre_eqp_availability;
2368 CLOSE c_avail;
2369 END IF;
2370
2374
2371 p_pre_tag_data := v_pre_tag_data;
2372 p_pre_reading_time := v_pre_reading_time;
2373 p_pre_eqp_availability := v_pre_eqp_availability;
2375 END GET_PREV_TAG_READING_INFO;
2376
2377
2378 /* ****************************************************************************
2379 * Procedure :GET_PREV_TAG_READING_SET *
2380 * Description :This function is used to retrive the previous reading set *
2381 * from mth_tag_readings_stg, mth_tag_readings, *
2382 and mth_tag_readings_err *
2383 * for the given tag_codes and reading time is earlier than *
2384 * the reading time specified and within the range specified *
2385 * by the range_in_hour. The reading set bounded by the same *
2386 * group id contains both tags *
2387 * File Name :MTHUTILB.PLS *
2388 * Visibility :Public
2389 * Parameters : *
2390 * p_tag_code1: TAG code name *
2391 * p_reading_time1: corresponding reading_time *
2392 * p_tag_code2: Another tag code name *
2393 * p_reading_time2: corresponding reading_time to the second tag *
2394 * p_range_in_hours: Number of hours, which is used to limit *
2395 * the search of the prevous reading to the range that is earlier *
2396 * than the reading_time and later than *
2397 * reading_time + p_range_in_hours / 24. *
2398 * p_pre_tag_data1: Previous tag reading for the tag_code1 *
2399 * p_pre_tag_data2: Previous tag reading for the tag_code2 *
2400 * Modification log : *
2401 * Author Date Change: Yong Feng 14-Jul-2009 Initial Creation *
2402 ******************************************************************************/
2403 PROCEDURE GET_PREV_TAG_READING_SET(p_tag_code1 IN VARCHAR2,
2404 p_reading_time1 IN DATE,
2405 p_tag_code2 IN VARCHAR2,
2406 p_reading_time2 IN DATE,
2407 p_range_in_hours IN NUMBER DEFAULT NULL,
2408 p_pre_tag_data1 OUT NOCOPY VARCHAR2,
2409 p_pre_tag_data2 OUT NOCOPY VARCHAR2) IS
2410 CURSOR c_pre_data_set(p_tag_code1 IN VARCHAR2, p_reading_time1 IN DATE,
2411 p_tag_code2 IN VARCHAR2, p_reading_time2 IN DATE,
2412 p_end_time IN DATE) IS
2413 SELECT tag_data1, reading_time1, tag_data2, reading_time2
2414 FROM (
2415 SELECT r1.tag_data tag_data1, r1.reading_time reading_time1,
2416 r2.tag_data tag_data2, r2.reading_time reading_time2
2417 FROM mth_tag_readings_stg r1, mth_tag_readings_stg r2
2418 WHERE r1.group_id = r2.GROUP_id AND
2419 r1.reading_time < p_reading_time1 AND
2420 r2.reading_time < p_reading_time2 AND
2421 r1.reading_time >= p_end_time AND
2422 r2.reading_time >= p_end_time AND
2423 r1.tag_code = p_tag_code1 AND
2424 r2.tag_code = p_tag_code2
2425 UNION ALL
2426 SELECT r1.tag_data tag_data1, r1.reading_time reading_time1,
2427 r2.tag_data tag_data2, r2.reading_time reading_time2
2428 FROM mth_tag_readings r1, mth_tag_readings r2
2429 WHERE r1.group_id = r2.GROUP_id AND
2430 r1.reading_time < p_reading_time1 AND
2431 r2.reading_time < p_reading_time2 AND
2432 r1.reading_time >= p_end_time AND
2433 r2.reading_time >= p_end_time AND
2434 r1.tag_code = p_tag_code1 AND
2435 r2.tag_code = p_tag_code2
2436 UNION ALL
2437 SELECT r1.tag_data tag_data1, r1.reading_time reading_time1,
2438 r2.tag_data tag_data2, r2.reading_time reading_time2
2439 FROM mth_tag_readings_err r1, mth_tag_readings_err r2
2440 WHERE r1.group_id = r2.GROUP_id AND
2441 r1.reading_time < p_reading_time1 AND
2442 r2.reading_time < p_reading_time2 AND
2443 r1.reading_time >= p_end_time AND
2444 r2.reading_time >= p_end_time AND
2445 r1.tag_code = p_tag_code1 AND
2446 r2.tag_code = p_tag_code2
2447 )
2448 ORDER BY reading_time1 DESC, reading_time2 DESC;
2449
2450 v_reading_time1 DATE;
2451 v_reading_time2 DATE;
2452 v_begin_date DATE;
2453 v_end_date DATE;
2454 BEGIN
2455 v_begin_date := LEAST(p_reading_time1, p_reading_time2);
2456 IF (v_begin_date IS NULL) THEN
2457 v_begin_date := SYSDATE;
2458 END IF;
2459 IF p_range_in_hours is not NULL and p_range_in_hours > 0 THEN
2460 v_end_date := v_begin_date - p_range_in_hours / 24;
2461 ELSE
2462 v_end_date := v_begin_date - 36500;
2463 END IF;
2464
2465 OPEN c_pre_data_set(p_tag_code1, p_reading_time1,
2466 p_tag_code2, p_reading_time2, v_end_date);
2467 FETCH c_pre_data_set INTO p_pre_tag_data1, v_reading_time1,
2468 p_pre_tag_data2, v_reading_time2;
2469 CLOSE c_pre_data_set;
2470 END GET_PREV_TAG_READING_SET;
2471
2472
2473 /* ****************************************************************************
2474 * Function :VERIFY_TAG_DATA_TREND *
2475 * Description Check consecutive values of tag readings is above *
2476 * mean value (or) below mean value and the previous set of *
2477 * data does not satisfy this condition *
2478 * File Name :MTHUTILB.PLS *
2479 * Visibility :Public
2480 * Parameters : *
2481 * p_tag_code: tag code name *
2482 * p_tag_data: tag data *
2483 * p_reading_time: corresponding reading_time *
2484 * p_att_grp_name: group name *
2488 * the search of the prevous reading to the range that is earlier *
2485 * p_mean_attr_name: attribute name in EGO_ATTRS_V *
2486 * p_num_of_readings: Number of consective readings to check *
2487 * p_range_in_hours: Number of hours, which is used to limit *
2489 * than the reading_time and later than *
2490 * reading_time + p_range_in_hours / 24. *
2491 * RETURN: 0 Does not satisfy the condition *
2492 * 1 Has a up trend *
2493 * 2 Has a down trend *
2494 * Modification log : *
2495 * Author Date Change: Yong Feng 14-Jul-2009 Initial Creation *
2496 ******************************************************************************/
2497 FUNCTION VERIFY_TAG_DATA_TREND(p_tag_code IN VARCHAR2,
2498 p_tag_data IN VARCHAR2,
2499 p_reading_time IN DATE,
2500 p_att_grp_name IN VARCHAR2
2501 DEFAULT 'SPECIFICATIONS',
2502 p_mean_attr_name IN VARCHAR2
2503 DEFAULT 'MEAN',
2504 p_num_of_readings IN NUMBER,
2505 p_range_in_hours IN NUMBER DEFAULT NULL)
2506 RETURN NUMBER
2507 IS
2508 CURSOR c_attr_name (p_tag_code IN VARCHAR2) IS
2509 SELECT a.attr_name, t.equipment_fk_key
2510 FROM mth_tag_destination_map t, ego_attrs_v a, EGO_ATTR_GROUPS_V g
2511 WHERE t.tag_code = p_tag_code and t.attribute = a.attr_id AND
2512 t.attribute_group = g.attr_group_id AND a.application_id = 9001 AND
2513 a.application_id = g.application_id and
2514 a.attr_group_name = g.attr_group_name;
2515
2516 CURSOR c_pre_data_set(p_tag_code IN VARCHAR2, p_reading_time IN DATE,
2517 p_end_time IN DATE) IS
2518 SELECT tag_data, reading_time
2519 FROM (
2520 SELECT tag_data, reading_time
2521 FROM mth_tag_readings_stg
2522 WHERE reading_time < p_reading_time AND
2523 reading_time >= p_end_time AND
2524 tag_code = p_tag_code
2525 UNION ALL
2526 SELECT tag_data, reading_time
2527 FROM mth_tag_readings
2528 WHERE reading_time < p_reading_time AND
2529 reading_time >= p_end_time AND
2530 tag_code = p_tag_code
2531 UNION ALL
2532 SELECT tag_data, reading_time
2533 FROM mth_tag_readings_err
2534 WHERE reading_time < p_reading_time AND
2535 reading_time >= p_end_time AND
2536 tag_code = p_tag_code
2537 )
2538 ORDER BY reading_time DESC;
2539
2540 TYPE cur_typ IS REF CURSOR;
2541 c cur_typ;
2542 query_str VARCHAR2(200);
2543 v_mean_column_name varchar2(30);
2544 v_ret_value number := 1;
2545 v_attr_name varchar2(30);
2546 v_equipment_fk_key number;
2547 v_mean_value number;
2548 v_end_date DATE;
2549 v_is_up_trend boolean;
2550 v_num_data_examed number;
2551
2552 v_cur_tag_data varchar2(255);
2553 v_cur_reading_time date;
2554 v_pre_tag_data varchar2(255);
2555 v_pre_reading_time date;
2556 v_has_trend boolean;
2557 v_cur_tag_value number;
2558 v_pre_tag_value number;
2559 v_pre_has_trend boolean;
2560 v_has_more_data boolean;
2561 v_parameter_col_name varchar2(30);
2562 v_attr_grp_id number;
2563
2564 BEGIN
2565 v_attr_grp_id := GET_ATTR_GROUP_ID(p_att_grp_name);
2566 v_parameter_col_name := GET_ATTR_EXT_COLUMN('PARAMETER', p_att_grp_name);
2567
2568 -- Get the attribute name associated with tag code
2569 open c_attr_name(p_tag_code);
2570 fetch c_attr_name into v_attr_name, v_equipment_fk_key;
2571 close c_attr_name;
2572
2573 -- Construct the sql to get the value for that attribute
2574 v_mean_column_name := GET_ATTR_EXT_COLUMN(p_mean_attr_name, p_att_grp_name);
2575
2576 IF v_attr_name is null or length(v_attr_name) = 0 or
2577 v_parameter_col_name is NULL or LENGTH(v_parameter_col_name) = 0 or
2578 v_mean_column_name is NULL or LENGTH(v_mean_column_name) = 0 or
2579 v_attr_grp_id is NULL THEN
2580 return 0;
2581 END IF;
2582
2583 query_str := 'SELECT ' || v_mean_column_name ||
2584 ' FROM mth_equipments_ext_b' ||
2585 ' WHERE equipment_pk_key = :e_id AND ' ||
2586 v_parameter_col_name || ' = :attr_name AND ' ||
2587 ' attr_group_id = :attr_group_id';
2588 OPEN c FOR query_str USING v_equipment_fk_key, v_attr_name, v_attr_grp_id;
2589 FETCH c INTO v_mean_value;
2590 CLOSE c;
2591
2592 -- IF could not find the mean value, or a user-specified value, OR
2593 -- the mean value is equal to the current tag_data
2594 -- THEN return 0 for no trend found.
2595 IF (v_mean_value IS NULL OR to_number(p_tag_data) = v_mean_value ) THEN
2596 return 0;
2597 END IF;
2598
2599 IF p_range_in_hours is not NULL and p_range_in_hours > 0 THEN
2600 v_end_date := p_reading_time - p_range_in_hours / 24;
2601 ELSE
2602 v_end_date := p_reading_time - 36500;
2603 END IF;
2604 OPEN c_pre_data_set(p_tag_code, p_reading_time, v_end_date);
2605
2606 v_is_up_trend := (to_number(p_tag_data) > v_mean_value);
2607
2608 v_num_data_examed := 1;
2609 v_pre_tag_value := to_number(p_tag_data);
2610 v_has_trend := true;
2611 v_has_more_data := true;
2612 while v_num_data_examed <= p_num_of_readings and
2613 v_has_trend and v_has_more_data loop
2614 fetch c_pre_data_set into v_cur_tag_data, v_cur_reading_time;
2615 --EXIT WHEN c_pre_data_set%NOTFOUND;
2616 if (c_pre_data_set%NOTFOUND) THEN
2617 v_has_more_data := false;
2618 else
2622 if v_is_up_trend then
2619 begin
2620 v_cur_tag_value := to_number(v_cur_tag_data);
2621 v_pre_has_trend := v_has_trend;
2623 v_has_trend := ( -- v_pre_tag_value > v_cur_tag_value AND
2624 v_cur_tag_value > v_mean_value );
2625 else
2626 v_has_trend := ( -- v_pre_tag_value < v_cur_tag_value AND
2627 v_cur_tag_value < v_mean_value );
2628 end if;
2629
2630 v_num_data_examed := v_num_data_examed + 1;
2631 v_pre_tag_value := v_cur_tag_value;
2632 end;
2633 end if;
2634 end loop;
2635 close c_pre_data_set;
2636
2637 if ((v_num_data_examed = p_num_of_readings + 1) and
2638 v_pre_has_trend = true and v_has_trend = false) OR
2639 ((v_num_data_examed = p_num_of_readings) and v_has_trend = true) then
2640 IF v_is_up_trend THEN
2641 v_ret_value := 1;
2642 ELSE
2643 v_ret_value := 2;
2644 END IF;
2645 else
2646 v_ret_value := 0;
2647 end if;
2648 return v_ret_value;
2649
2650 END VERIFY_TAG_DATA_TREND;
2651
2652
2653 /* ****************************************************************************
2654 * Procedure :PUT_DOWN_STS_EXPECTED_UPTIME *
2655 * Description :This procedure puts expected_up_time for planned *
2656 * downtime in the mth_equip_statuses table *
2657 * File Name :MTHUTILB.PLS *
2658 * Visibility :Public
2659 * Modification log : *
2660 * Author Date Change: Shanthi Swaroop Donthu 18-Jul-2009 Initial Creation *
2661 ******************************************************************************/
2662 PROCEDURE PUT_DOWN_STS_EXPECTED_UPTIME
2663 IS
2664 v_start_time DATE;
2665 v_end_time DATE;
2666 v_run_date DATE;
2667 BEGIN
2668 SELECT DISTINCT from_date INTO v_run_date FROM mth_run_log WHERE fact_table = 'MTH_EQUIP_DOWN_STS_UPTIME';
2669
2670 FOR i IN (SELECT equipment_fk_key,shift_workday_fk_key,from_date,To_Date,system_fk_key,creation_date,last_update_date,creation_system_id,
2671 last_update_system_id FROM mth_equip_statuses WHERE status=3 AND To_Date IS NOT NULL AND last_update_date>=v_run_date)
2672 LOOP
2673 v_start_time := i.from_date;
2674 v_end_time := i.To_Date;
2675
2676 --Dbms_Output.PUT_LINE('v_start_time AND v_end_time are'||'-----'||To_Char(v_start_time,'dd-Mon-yyyy hh24:mi:ss')||'------'||To_Char(v_end_time,'dd-Mon-yyyy hh24:mi:ss'));
2677
2678 FOR j IN (SELECT equipment_fk_key,shift_workday_fk_key,from_date,To_Date FROM mth_equipment_shifts_d WHERE equipment_fk_key = i.equipment_fk_key
2679 AND shift_workday_fk_key = i.shift_workday_fk_key AND Upper(availability_flag)= 'N')
2680 LOOP
2681
2682 --Dbms_Output.PUT_LINE('Equip_shift'||'------'||To_Char(j.from_date,'dd-Mon-yyyy hh24:mi:ss')||'------'||To_Char(j.to_date,'dd-Mon-yyyy hh24:mi:ss'));
2683 /***************************************/
2684
2685 IF (v_start_time < j.from_date) THEN
2686 IF (v_end_time >= j.from_date) THEN
2687 IF (i.from_date = v_start_time) THEN
2688 UPDATE mth_equip_statuses SET To_Date = j.from_date WHERE equipment_fk_key = i.equipment_fk_key
2689 AND shift_workday_fk_key = i.shift_workday_fk_key AND from_date = v_start_time;
2690 /***** NO EXPECTE_DOWN_TIME ******/
2691 --Dbms_Output.PUT_LINE('Third Updated status record with the end date'||'---'||To_Char(j.from_date,'dd-Mon-yyyy hh24:mi:ss'));
2692 ELSE
2693 INSERT INTO mth_equip_statuses(equipment_fk_key,shift_workday_fk_key,from_date,To_Date,status,system_fk_key,
2694 creation_date,last_update_date,creation_system_id,last_update_system_id) VALUES (i.equipment_fk_key,
2695 i.shift_workday_fk_key,v_start_time,j.from_date,3,i.system_fk_key,i.creation_date,sysdate,
2696 i.creation_system_id,i.last_update_system_id);
2697
2698 --Dbms_Output.PUT_LINE('inserted status record with'||'---'||To_Char(v_start_time,'dd-Mon-yyyy hh24:mi:ss')||'---'||To_Char(j.from_date,'dd-Mon-yyyy hh24:mi:ss'));
2699
2700 END IF;
2701
2702 IF (v_end_time <= j.To_Date) THEN
2703
2704 INSERT INTO mth_equip_statuses(equipment_fk_key,shift_workday_fk_key,from_date,To_Date,status,system_fk_key,
2705 creation_date,last_update_date,creation_system_id,last_update_system_id,expected_up_time,status_type) VALUES (i.equipment_fk_key,
2706 i.shift_workday_fk_key,j.from_date,v_end_time,3,i.system_fk_key,i.creation_date,sysdate,
2707 i.creation_system_id,i.last_update_system_id,((j.To_Date-j.from_date)*24),'PLANNED DOWNTIME');/*** EXPECTED_UP_TIME = J.TO_DATE - J.FROM_DATE **/
2708 EXIT;
2709 --Dbms_Output.PUT_LINE('inserted status record with'||'---'||To_Char(j.from_date,'dd-Mon-yyyy hh24:mi:ss')||'---'||To_Char(v_end_time,'dd-Mon-yyyy hh24:mi:ss'));
2710
2711 ELSE
2712 INSERT INTO mth_equip_statuses(equipment_fk_key,shift_workday_fk_key,from_date,To_Date,status,system_fk_key,
2713 creation_date,last_update_date,creation_system_id,last_update_system_id,expected_up_time,status_type) VALUES (i.equipment_fk_key,
2714 i.shift_workday_fk_key,j.from_date,j.to_date,3,i.system_fk_key,i.creation_date,sysdate,
2715 i.creation_system_id,i.last_update_system_id,((j.To_Date-j.from_date)*24),'PLANNED DOWNTIME');/*** EXPECTED_UP_TIME = J.TO_DATE - J.FROM_DATE **/
2716
2720 v_start_time := j.To_Date;
2717 --Dbms_Output.PUT_LINE('inserted status record with'||'---'||To_Char(j.from_date,'dd-Mon-yyyy hh24:mi:ss')||'---'||To_Char(j.to_date,'dd-Mon-yyyy hh24:mi:ss'));
2718
2719
2721 END IF;
2722 END IF;
2723 END IF;
2724
2725 /****************************************/
2726
2727 IF (v_start_time >= j.from_date AND v_start_time < j.To_Date) THEN
2728 --Dbms_Output.PUT_LINE('First IF condition');
2729
2730 IF(v_end_time <= j.to_date) THEN
2731
2732 UPDATE mth_equip_statuses SET expected_up_time = ((j.to_date-v_start_time)*24), status_type = 'PLANNED DOWNTIME', last_update_date = SYSDATE
2733 WHERE equipment_fk_key = i.equipment_fk_key
2734 AND shift_workday_fk_key = i.shift_workday_fk_key AND from_date = v_start_time AND To_Date=v_end_time;
2735 /***UPDATE THE RECORD WITH *** EXPECTED_UP_TIME = V_END_TIME - V_START_TIMR **/
2736
2737 EXIT ;
2738
2739 ELSE
2740 UPDATE mth_equip_statuses SET To_Date = j.to_date, last_update_date = SYSDATE,expected_up_time=((j.To_Date - v_start_time)*24), status_type ='PLANNED DOWNTIME' WHERE equipment_fk_key = i.equipment_fk_key
2741 AND shift_workday_fk_key = i.shift_workday_fk_key AND from_date = v_start_time;
2742
2743 --Dbms_Output.PUT_LINE('Third Updated status record with the end date'||'---'||To_Char(j.to_date,'dd-Mon-yyyy hh24:mi:ss'));
2744
2745 INSERT INTO mth_equip_statuses(equipment_fk_key,shift_workday_fk_key,from_date,To_Date,status,system_fk_key,
2746 creation_date,last_update_date,creation_system_id,last_update_system_id) VALUES (i.equipment_fk_key,
2747 i.shift_workday_fk_key,j.to_date,v_end_time,3,i.system_fk_key,i.creation_date,sysdate,
2748 i.creation_system_id,i.last_update_system_id);
2749
2750 --Dbms_Output.PUT_LINE('inserted status record with'||'---'||To_Char(j.to_date,'dd-Mon-yyyy hh24:mi:ss')||'---'||To_Char(v_end_time,'dd-Mon-yyyy hh24:mi:ss'));
2751
2752 v_start_time := j.To_Date;
2753
2754 END IF;
2755 END IF;
2756 COMMIT;
2757
2758 END LOOP;
2759 COMMIT;
2760 END LOOP;
2761 DELETE FROM mth_equip_statuses WHERE from_date = To_Date;
2762 COMMIT;
2763
2764 END PUT_DOWN_STS_EXPECTED_UPTIME;
2765
2766
2767
2768 /*****************************************************************************
2769 * Procedure :MTH_LOAD_HOUR_STATUS *
2770 * Description :This procedure is used to break the shift level status data *
2771 * into hour level and populates into mth_equip_statuses table *
2772 * File Name :MTHUTILB.PLS *
2773 * Visibility :Public
2774 * Modification log : *
2775 * Author Date Change: Shanthi Swaroop Donthu 08-Dec-2009 Initial Creation *
2776 ******************************************************************************/
2777
2778 PROCEDURE MTH_LOAD_HOUR_STATUS(p_equipment_fk_key IN NUMBER,p_shift_workday_fk_key IN NUMBER,
2779 p_from_date IN DATE, p_to_date IN DATE,p_status IN VARCHAR2,
2780 p_system_fk_key IN NUMBER,p_user_dim1_fk_key IN NUMBER, p_user_dim2_fk_key IN NUMBER,
2781 p_user_dim3_fk_key IN NUMBER,p_user_dim4_fk_key IN NUMBER, p_user_dim5_fk_key IN NUMBER,
2782 p_user_attr1 IN VARCHAR2,p_user_attr2 IN VARCHAR2, p_user_attr3 IN VARCHAR2,
2783 p_user_attr4 IN VARCHAR2,p_user_attr5 IN VARCHAR2, p_user_measure1 IN NUMBER,
2784 p_user_measure2 IN NUMBER,p_user_measure3 IN NUMBER, p_user_measure4 IN NUMBER,
2785 p_user_measure5 IN NUMBER ,p_hour_fk_key IN NUMBER,p_hour_fk IN VARCHAR2,p_hour_to_time IN DATE) IS
2786
2787 --initialize variables here
2788 v_unassigned_string VARCHAR2(20) := to_char(mth_util_pkg.mth_ua_get_val);
2789 v_count NUMBER;
2790 v_next_hour_from_time DATE;
2791 v_next_hour_to_time DATE;
2792 v_next_hour_fk_key VARCHAR2(120);
2793 v_least_to_date DATE;
2794
2795 BEGIN
2796 IF (p_hour_to_time >= p_to_date)
2797 THEN
2798 MERGE INTO MTH_EQUIP_STATUSES stat
2799 USING (select p_equipment_fk_key p_equipment_fk_key, p_shift_workday_fk_key p_shift_workday_fk_key,
2800 p_hour_fk_key p_hour_fk_key, p_from_date p_from_date, p_to_date p_to_date, p_status p_status,
2801 p_system_fk_key p_system_fk_key,p_user_dim1_fk_key p_user_dim1_fk_key,
2802 p_user_dim2_fk_key p_user_dim2_fk_key, p_user_dim3_fk_key p_user_dim3_fk_key,
2803 p_user_dim4_fk_key p_user_dim4_fk_key, p_user_dim5_fk_key p_user_dim5_fk_key,
2804 p_user_attr1 p_user_attr1,p_user_attr2 p_user_attr2, p_user_attr3 p_user_attr3,
2805 p_user_attr4 p_user_attr4, p_user_attr5 p_user_attr5, p_user_measure1 p_user_measure1,
2806 p_user_measure2 p_user_measure2, p_user_measure3 p_user_measure3,
2807 p_user_measure4 p_user_measure4, p_user_measure5 p_user_measure5 FROM dual) var
2808 ON (stat.equipment_fk_key = var.p_equipment_fk_key AND
2809 stat.shift_workday_fk_key = var.p_shift_workday_fk_key AND
2810 stat.hour_fk_key = var.p_hour_fk_key AND
2811 stat.from_date = var.p_from_date)
2812 WHEN MATCHED THEN
2813 UPDATE SET To_Date = p_to_date, status = p_status, system_fk_key = Nvl(p_system_fk_key,v_unassigned_string),
2814 user_dim1_fk_key = p_user_dim1_fk_key, user_dim2_fk_key = p_user_dim2_fk_key,
2815 user_dim3_fk_key = p_user_dim3_fk_key, user_dim4_fk_key = p_user_dim4_fk_key,
2816 user_dim5_fk_key = p_user_dim1_fk_key, user_attr1 = p_user_attr1,user_attr2 =p_user_attr2,
2817 user_attr3 = p_user_attr3, user_attr4 = p_user_attr4, user_attr5 = p_user_attr5,
2818 user_measure1 = p_user_measure1, user_measure2 = p_user_measure2,
2822 WHEN NOT MATCHED THEN
2819 user_measure3 = p_user_measure3, user_measure4 = p_user_measure4,
2820 user_measure5 = p_user_measure5, last_update_date = SYSDATE,last_update_system_id =p_system_fk_key
2821
2823 INSERT (stat.EQUIPMENT_FK_KEY, stat.SHIFT_WORKDAY_FK_KEY, stat.FROM_DATE, stat.TO_DATE,
2824 stat.STATUS, stat.SYSTEM_FK_KEY, stat.USER_DIM1_FK_KEY, stat.USER_DIM2_FK_KEY, stat.USER_DIM3_FK_KEY,
2825 stat.USER_DIM4_FK_KEY, stat.USER_DIM5_FK_KEY, stat.USER_ATTR1, stat.USER_ATTR2, stat.USER_ATTR3,
2826 stat.USER_ATTR4, stat.USER_ATTR5, stat.USER_MEASURE1, stat.USER_MEASURE2, stat.USER_MEASURE3,
2827 stat.USER_MEASURE4, stat.USER_MEASURE5, stat.CREATION_DATE, stat.LAST_UPDATE_DATE, stat.CREATION_SYSTEM_ID,
2828 stat.LAST_UPDATE_SYSTEM_ID, stat.CREATED_BY, stat.LAST_UPDATE_LOGIN, stat.LAST_UPDATED_BY,
2829 stat.EXPECTED_UP_TIME, stat.STATUS_TYPE, stat.HOUR_FK_KEY)
2830 VALUES (p_equipment_fk_key, p_shift_workday_fk_key, p_from_date, p_to_date, p_status,
2831 Nvl(p_system_fk_key,v_unassigned_string),
2832 p_user_dim1_fk_key, p_user_dim2_fk_key, p_user_dim3_fk_key, p_user_dim4_fk_key, p_user_dim5_fk_key, p_user_attr1,
2833 p_user_attr2, p_user_attr3, p_user_attr4, p_user_attr5, p_user_measure1, p_user_measure2, p_user_measure3,
2834 p_user_measure4, p_user_measure5,SYSDATE,SYSDATE,Nvl(p_system_fk_key,v_unassigned_string),
2835 Nvl(p_system_fk_key,v_unassigned_string),NULL,NULL,NULL,NULL,NULL,p_hour_fk_key);
2836
2837 ELSE
2838
2839 MERGE INTO MTH_EQUIP_STATUSES stat
2840 USING (select p_equipment_fk_key p_equipment_fk_key, p_shift_workday_fk_key p_shift_workday_fk_key,
2841 p_hour_fk_key p_hour_fk_key, p_from_date p_from_date, p_to_date p_to_date, p_status p_status,
2842 p_system_fk_key p_system_fk_key,p_user_dim1_fk_key p_user_dim1_fk_key,
2843 p_user_dim2_fk_key p_user_dim2_fk_key, p_user_dim3_fk_key p_user_dim3_fk_key,
2844 p_user_dim4_fk_key p_user_dim4_fk_key, p_user_dim5_fk_key p_user_dim5_fk_key,p_user_attr1 p_user_attr1,
2845 p_user_attr2 p_user_attr2, p_user_attr3 p_user_attr3, p_user_attr4 p_user_attr4,
2846 p_user_attr5 p_user_attr5, p_user_measure1 p_user_measure1, p_user_measure2 p_user_measure2,
2847 p_user_measure3 p_user_measure3,p_user_measure4 p_user_measure4, p_user_measure5 p_user_measure5 FROM dual) var
2848 ON (stat.equipment_fk_key = var.p_equipment_fk_key AND
2849 stat.shift_workday_fk_key = var.p_shift_workday_fk_key AND
2850 stat.hour_fk_key = var.p_hour_fk_key AND
2851 stat.from_date = var.p_from_date)
2852 WHEN MATCHED THEN
2853 UPDATE SET To_Date = p_hour_to_time, status = p_status, system_fk_key = Nvl(p_system_fk_key,v_unassigned_string),
2854 user_dim1_fk_key = p_user_dim1_fk_key, user_dim2_fk_key = p_user_dim2_fk_key,
2855 user_dim3_fk_key = p_user_dim3_fk_key, user_dim4_fk_key = p_user_dim4_fk_key,
2856 user_dim5_fk_key = p_user_dim1_fk_key, user_attr1 = p_user_attr1,user_attr2 =p_user_attr2,
2857 user_attr3 = p_user_attr3, user_attr4 = p_user_attr4, user_attr5 = p_user_attr5,
2858 user_measure1 = p_user_measure1, user_measure2 = p_user_measure2,
2859 user_measure3 = p_user_measure3, user_measure4 = p_user_measure4,
2860 user_measure5 = p_user_measure5, last_update_date = SYSDATE,last_update_system_id =p_system_fk_key
2861
2862 WHEN NOT MATCHED THEN
2863 INSERT (stat.EQUIPMENT_FK_KEY, stat.SHIFT_WORKDAY_FK_KEY, stat.FROM_DATE, stat.TO_DATE,
2864 stat.STATUS, stat.SYSTEM_FK_KEY, stat.USER_DIM1_FK_KEY, stat.USER_DIM2_FK_KEY, stat.USER_DIM3_FK_KEY,
2865 stat.USER_DIM4_FK_KEY, stat.USER_DIM5_FK_KEY, stat.USER_ATTR1, stat.USER_ATTR2, stat.USER_ATTR3,
2866 stat.USER_ATTR4, stat.USER_ATTR5, stat.USER_MEASURE1, stat.USER_MEASURE2, stat.USER_MEASURE3,
2867 stat.USER_MEASURE4, stat.USER_MEASURE5, stat.CREATION_DATE, stat.LAST_UPDATE_DATE, stat.CREATION_SYSTEM_ID,
2868 stat.LAST_UPDATE_SYSTEM_ID, stat.CREATED_BY, stat.LAST_UPDATE_LOGIN, stat.LAST_UPDATED_BY,
2869 stat.EXPECTED_UP_TIME, stat.STATUS_TYPE, stat.HOUR_FK_KEY)
2870 VALUES (p_equipment_fk_key, p_shift_workday_fk_key, p_from_date, p_hour_to_time, p_status,
2871 Nvl(p_system_fk_key,v_unassigned_string),
2872 p_user_dim1_fk_key, p_user_dim2_fk_key, p_user_dim3_fk_key, p_user_dim4_fk_key, p_user_dim5_fk_key, p_user_attr1,
2873 p_user_attr2, p_user_attr3, p_user_attr4, p_user_attr5, p_user_measure1, p_user_measure2, p_user_measure3,
2874 p_user_measure4, p_user_measure5,SYSDATE,SYSDATE,Nvl(p_system_fk_key,v_unassigned_string),
2875 Nvl(p_system_fk_key,v_unassigned_string),NULL,NULL,NULL,NULL,NULL,p_hour_fk_key);
2876
2877 SELECT FLOOR((p_to_date -p_hour_to_time)*24) INTO v_count FROM DUAL;
2878
2879 FOR i IN 1..v_count+1 LOOP
2880
2881 SELECT LEAD_HOUR,LEAD_FROM_DATE,LEAD_TO_DATE, LEAST(p_to_date, LEAD_TO_DATE)
2882 INTO v_next_hour_fk_key, v_next_hour_from_time, v_next_hour_to_time, v_least_to_date
2883 FROM(
2884 SELECT HOUR_PK,LEAD( HOUR_PK_KEY ,i) OVER(ORDER BY FROM_TIME ) LEAD_HOUR, LEAD( FROM_TIME,i) OVER(ORDER BY FROM_TIME ) LEAD_FROM_DATE,
2885 LEAD( TO_TIME ,i) OVER(ORDER BY FROM_TIME) LEAD_TO_DATE FROM MTH_HOUR_D)
2886 WHERE HOUR_PK = p_hour_fk;
2887
2888 IF (v_next_hour_from_time <= v_least_to_date)
2889 THEN
2890 MERGE INTO MTH_EQUIP_STATUSES stat
2891 USING (select p_equipment_fk_key p_equipment_fk_key, p_shift_workday_fk_key p_shift_workday_fk_key,
2892 p_hour_fk_key p_hour_fk_key, p_from_date p_from_date, p_to_date p_to_date, p_status p_status,
2893 p_system_fk_key p_system_fk_key,p_user_dim1_fk_key p_user_dim1_fk_key,
2894 p_user_dim2_fk_key p_user_dim2_fk_key, p_user_dim3_fk_key p_user_dim3_fk_key,
2895 p_user_dim4_fk_key p_user_dim4_fk_key, p_user_dim5_fk_key p_user_dim5_fk_key,
2896 p_user_attr1 p_user_attr1,p_user_attr2 p_user_attr2, p_user_attr3 p_user_attr3,
2897 p_user_attr4 p_user_attr4, p_user_attr5 p_user_attr5, p_user_measure1 p_user_measure1,
2898 p_user_measure2 p_user_measure2, p_user_measure3 p_user_measure3,p_user_measure4 p_user_measure4,
2902 stat.shift_workday_fk_key = var.p_shift_workday_fk_key AND
2899 p_user_measure5 p_user_measure5,v_next_hour_fk_key v_next_hour_fk_key,
2900 v_next_hour_from_time v_next_hour_from_time,v_next_hour_to_time v_next_hour_to_time FROM dual) var
2901 ON (stat.equipment_fk_key = var.p_equipment_fk_key AND
2903 stat.hour_fk_key = var.v_next_hour_fk_key AND
2904 stat.from_date = var.v_next_hour_from_time)
2905 WHEN MATCHED THEN
2906 UPDATE SET To_Date = least(p_to_date,v_next_hour_to_time), status = p_status,
2907 system_fk_key = Nvl(p_system_fk_key,v_unassigned_string),
2908 user_dim1_fk_key = p_user_dim1_fk_key, user_dim2_fk_key = p_user_dim2_fk_key,
2909 user_dim3_fk_key = p_user_dim3_fk_key, user_dim4_fk_key = p_user_dim4_fk_key,
2910 user_dim5_fk_key = p_user_dim1_fk_key, user_attr1 = p_user_attr1,user_attr2 =p_user_attr2,
2911 user_attr3 = p_user_attr3, user_attr4 = p_user_attr4, user_attr5 = p_user_attr5,
2912 user_measure1 = p_user_measure1, user_measure2 = p_user_measure2,
2913 user_measure3 = p_user_measure3, user_measure4 = p_user_measure4,
2914 user_measure5 = p_user_measure5, last_update_date = SYSDATE,last_update_system_id =p_system_fk_key
2915
2916 WHEN NOT MATCHED THEN
2917 INSERT (stat.EQUIPMENT_FK_KEY, stat.SHIFT_WORKDAY_FK_KEY, stat.FROM_DATE, stat.TO_DATE,
2918 stat.STATUS, stat.SYSTEM_FK_KEY, stat.USER_DIM1_FK_KEY, stat.USER_DIM2_FK_KEY, stat.USER_DIM3_FK_KEY,
2919 stat.USER_DIM4_FK_KEY, stat.USER_DIM5_FK_KEY, stat.USER_ATTR1, stat.USER_ATTR2, stat.USER_ATTR3,
2920 stat.USER_ATTR4, stat.USER_ATTR5, stat.USER_MEASURE1, stat.USER_MEASURE2, stat.USER_MEASURE3,
2921 stat.USER_MEASURE4, stat.USER_MEASURE5, stat.CREATION_DATE, stat.LAST_UPDATE_DATE, stat.CREATION_SYSTEM_ID,
2922 stat.LAST_UPDATE_SYSTEM_ID, stat.CREATED_BY, stat.LAST_UPDATE_LOGIN, stat.LAST_UPDATED_BY,
2923 stat.EXPECTED_UP_TIME, stat.STATUS_TYPE, stat.HOUR_FK_KEY)
2924 VALUES (p_equipment_fk_key, p_shift_workday_fk_key, v_next_hour_from_time,
2925 least(p_to_date,v_next_hour_to_time), p_status,Nvl(p_system_fk_key,v_unassigned_string),
2926 p_user_dim1_fk_key, p_user_dim2_fk_key, p_user_dim3_fk_key, p_user_dim4_fk_key, p_user_dim5_fk_key, p_user_attr1,
2927 p_user_attr2, p_user_attr3, p_user_attr4, p_user_attr5, p_user_measure1, p_user_measure2, p_user_measure3,
2928 p_user_measure4, p_user_measure5,SYSDATE,SYSDATE,Nvl(p_system_fk_key,v_unassigned_string),
2929 Nvl(p_system_fk_key,v_unassigned_string),NULL,NULL,NULL,NULL,NULL,v_next_hour_fk_key);
2930
2931 END IF;
2932 END LOOP;
2933 COMMIT;
2934 END IF;
2935 COMMIT;
2936 END MTH_LOAD_HOUR_STATUS;
2937
2938
2939
2940 /* ****************************************************************************
2941 * Procedure :GENERATE_SHIFTS *
2942 * Description :This procedure generates the shifts in workday shifts *
2943 * and equipment shifts table *
2944 * File Name :MTHUTILB.PLS *
2945 * Visibility :Public
2946 * Modification log : *
2947 * Author Date Change: amrit Kaur 04-Dec-2009 Initial Creation *
2948 * Mandar Gijare 20-Dec-2010 Shift MED modification *
2949 ******************************************************************************/
2950
2951
2952 PROCEDURE GENERATE_SHIFTS( p_plant_fk_key IN NUMBER,
2953 p_start_date IN DATE,
2954 p_end_date IN DATE,
2955 p_comp_state OUT NOCOPY VARCHAR2)
2956 IS
2957 --local variable declation
2958
2959 l_plant_fk_key NUMBER ;
2960 l_sysdate DATE := sysdate;--variable for sysdate
2961 l_last_update_system_id NUMBER ;
2962 l_last_update_date DATE ;
2963 l_plant_pk VARCHAR2(120);
2964 l_start_time varchar2(8);--variable for storing start time
2965 l_end_time VARCHAR2(8);--variable for storing end_time
2966 l_graveyard VARCHAR2(30);
2967 l_shift_num number;
2968 l_shift_name VARCHAR2(240);
2969 l_line_num NUMBER ;
2970 l_start_date DATE ;
2971 l_end_date DATE ;
2972 l_start_time1 NUMBER;
2973 l_end_time1 NUMBER;
2974 l_equipment_fk_key NUMBER ;
2975 l1_plant_fk_key NUMBER ;
2976 N NUMBER:=0;
2977 l_creation_system_id NUMBER;
2978 --l_organization_code VARCHAR2(120);
2979 l_system_fk_key NUMBER;
2980 l_site_id NUMBER ;
2981 l_entity_pk_key NUMBER ;
2982 l_entity_name VARCHAR2(100);
2983 l_entity_type VARCHAR2(30);
2984 l_production_entity VARCHAR2(1);
2985 l_shift_workday_pk_key NUMBER ;
2986 l1_shift_date DATE ;
2987 l_from_date DATE ;
2988 l_to_date DATE ;
2989 l_shift_type VARCHAR2(30);
2990 l_comp_state VARCHAR2(50);
2991 l_sh_start_date DATE;
2992 -- l_graveyard_shift NUMBER;
2993 CURSOR c_shift_def IS
2994
2995 select start_time ,end_time ,graveyard ,shift_num , shift_name,shift_type
2996 -- into l_start_time, l_end_time, l_graveyard, l_shift_num,l_line_num,l_shift_name,l_shift_type
2997 from
2998
2999 mth_site_shift_definitions where plant_fk_key=p_plant_fk_key;
3000
3001 CURSOR c_shift_date(p_plant_key NUMBER)
3002 IS
3003 SELECT shift_date
3004 FROM mth_shift_reference_mv
3005 WHERE PLANT_FK_KEY = p_plant_key;
3006
3007 CURSOR c_check_compile_state
3008 IS
3009 SELECT compile_state
3010 FROM user_mviews
3011 WHERE mview_name = 'MTH_SHIFT_REFERENCE_MV';
3012
3013 BEGIN
3014
3015
3016
3017 DBMS_MVIEW.REFRESH('MTH_SHIFT_REFERENCE_MV','C');
3018
3019 OPEN c_check_compile_state;
3020 FETCH c_check_compile_state INTO l_comp_state;
3021 CLOSE c_check_compile_state;
3025 IF(l_comp_state = 'VALID')
3022
3023 p_comp_state := l_comp_state;
3024
3026 THEN
3027 l_plant_fk_key := p_plant_fk_key;
3028
3029 FOR cur_shift_date in c_shift_date(l_plant_fk_key)
3030 LOOP
3031 l_sh_start_date := cur_shift_date.shift_date;
3032 END LOOP;
3033
3034 IF(l_sh_start_date > p_start_date)
3035 THEN
3036 l_start_date := l_sh_start_date+1;
3037 ELSE
3038 l_start_date := p_start_date;
3039 END IF;
3040 l_end_date := p_end_date;
3041 l_system_fk_key := "MTH_UTIL_PKG"."MTH_UA_GET_VAL"();
3042
3043 IF (l_end_date > l_start_date)
3044 THEN
3045 --DELETE FROM mth_workday_shifts_D WHERE plant_fk_key = l_plant_fk_key and shift_date>=l_start_date ;
3046 DELETE FROM mth_workday_shifts_D WHERE plant_fk_key = l_plant_fk_key and Trunc(from_date)>=l_start_date AND Trunc(from_date)<=l_end_date;
3047 DELETE FROM mth_workday_shifts_D WHERE plant_fk_key = l_plant_fk_key and shift_date>=l_start_date AND shift_date<=l_end_date;
3048 DELETE FROM mth_equipment_shifts_d WHERE availability_date>=l_start_date AND availability_date<=l_end_date
3049 AND equipment_fk_key IN (SELECT DISTINCT(Nvl(a.equipment_fk_key,0))
3050 FROM mth_equipment_shifts_d a,mth_equipments_d b WHERE b.equipment_pk_key=a.equipment_fk_key AND b.plant_fk_key =l_plant_fk_key
3051 UNION ALL
3052 SELECT distinct(Nvl(a.equipment_fk_key,0) )
3053 FROM mth_equipment_shifts_d a,mth_resources_d b WHERE b.resource_pk_key=a.equipment_fk_key AND b.plant_fk_key =l_plant_fk_key
3054
3055 UNION ALL
3056 SELECT distinct(Nvl(a.equipment_fk_key,0) )
3057 FROM mth_equipment_shifts_d a,mth_plants_d b WHERE b.plant_pk_key=a.equipment_fk_key AND b.plant_pk_key =l_plant_fk_key
3058
3059 UNION ALL
3060 SELECT distinct(Nvl(a.equipment_fk_key,0) )
3061 FROM mth_equipment_shifts_d a,mth_equip_entities_mst b WHERE b.entity_pk_key=a.equipment_fk_key AND b.plant_fk_key =l_plant_fk_key
3062 );
3063
3064 FOR l_shift_def IN c_shift_def
3065 LOOP
3066
3067 l_start_time := l_shift_def.start_time;
3068 l_end_time := l_shift_def.end_time;
3069 l_graveyard := l_shift_def.graveyard;
3070 l_shift_num := l_shift_def.shift_num;
3071 -- l_line_num := l_shift_def.line_num;
3072 l_shift_name := l_shift_def.shift_name;
3073 l_shift_type := l_shift_def.shift_type;
3074
3075
3076
3077 select plant_pk into l_plant_pk from mth_plants_d where plant_pk_key=l_plant_fk_key;
3078 --SELECT organization_code INTO l_organization_code FROM mth_organizations_l WHERE plant_fk_key =
3079 --l_plant_fk_key;
3080 N := l_end_date-l_start_date;
3081
3082 for i IN 1.. N+1
3083 LOOP
3084
3085
3086
3087 l_last_update_system_id := -99999;
3088 l_last_update_date := l_sysdate;
3089 l_creation_system_id := -1;
3090 l_start_time1 := TO_NUMBER(SUBSTR(l_start_time,1,2))*3600+
3091 TO_NUMBER(SUBSTR(l_start_time,4,2))*60+
3092 TO_NUMBER(SUBSTR(l_start_time,7,2));
3093
3094 l_end_time1 := TO_NUMBER(SUBSTR(l_end_time,1,2))*3600+
3095 TO_NUMBER(SUBSTR(l_end_time,4,2))*60+
3096 TO_NUMBER(SUBSTR(l_end_time,7,2));
3097
3098
3099 INSERT INTO mth_workday_shifts_D(shift_workday_pk_key, shift_workday_pk,
3100 shift_date,shift_date_julian,plant_fk_key,shift_type,
3101 graveyard_shift,from_date,to_date, shift_num,shift_name,
3102 source_org_code,system_fk_key,
3103 creation_date,last_update_date,creation_system_id,
3104 last_update_system_id)
3105 VALUES(mth_workdays_shifts_s.nextval,
3106 to_char(DECODE(GREATEST( l_start_time1, l_end_time1 ) ,
3107 l_start_time1 , l_start_date ,
3108 l_start_date) + ((TO_NUMBER(SUBSTR(l_start_time,1,2))+
3109 (TO_NUMBER(SUBSTR(l_start_time,4,2))/60)+
3110 (TO_NUMBER(SUBSTR(l_start_time,7,2))/3600))/24),
3111 'yyyymmdd-hh24:mi:ss')||'-'||l_shift_num||'-'||l_plant_pk,
3112 DECODE(GREATEST( l_start_time1, l_end_time1 ) , l_start_time1 ,
3113 l_start_date + Decode( l_shift_def.graveyard, 'SED',1,0) , l_start_date) ,
3114 TO_NUMBER(TO_CHAR( l_start_date ,'J')),
3115 l_plant_fk_key,l_shift_type,l_graveyard,
3116 l_start_date + ((TO_NUMBER(SUBSTR(l_start_time,1,2))+
3117 (TO_NUMBER(SUBSTR(l_start_time,4,2))/60)+
3118 (TO_NUMBER(SUBSTR(l_start_time,7,2))/3600))/24),
3119 DECODE(GREATEST( l_start_time1, l_end_time1 ) , l_start_time1 ,
3120 l_start_date + ((TO_NUMBER(SUBSTR(l_end_time,1,2))+
3121 (TO_NUMBER(SUBSTR(l_end_time,4,2))/60)+
3122 (TO_NUMBER(SUBSTR(l_end_time,7,2))/3600))/24)+ Decode( l_shift_def.graveyard, 'SED',1,1) ,
3123 l_start_date + ((TO_NUMBER(SUBSTR(l_end_time,1,2))+
3124 (TO_NUMBER(SUBSTR(l_end_time,4,2))/60)+
3125 (TO_NUMBER(SUBSTR(l_end_time,7,2))/3600))/24)),
3126 l_shift_num,l_shift_name,null, l_system_fk_key,
3127 l_sysdate, l_sysdate,
3128 l_creation_system_id,l_last_update_system_id);
3129 l_start_date := l_start_date+1;
3130
3131
3132 END LOOP;
3133 COMMIT;
3134
3135 IF(l_sh_start_date > p_start_date)
3136 THEN
3137 l_start_date := l_sh_start_date+1;
3138 ELSE
3139 l_start_date := p_start_date;
3140 END IF;
3141
3142
3143 IF UPPER(l_shift_type)='BOTH' THEN
3147 SELECT b.entity_pk_key entity_pk_key,a.shift_date shift_date,a.shift_workday_pk_key shift_workday_fk_key,a.from_date from_date,a.to_date To_Date,1 line_num,'Y',
3144 INSERT INTO mth_equipment_shifts_d(equipment_fk_key,availability_date,shift_workday_fk_key,from_date,To_Date,line_num,availability_flag,entity_type,creation_date,last_update_date,creation_system_id,
3145 last_update_system_id) (
3146
3148 b.entity_type entity_type,SYSDATE,SYSDATE,-1,-99999
3149
3150 --INTO l_site_id,l_entity_pk_key,l_entity_name,l_entity_type,l_production_entity,
3151 --l_shift_workday_pk_key,l1_shift_date,l_from_date,l_to_date
3152 FROM mth_workday_shifts_d a ,
3153 (
3154 SELECT plant_fk_key site_id, entity_pk_key, entity_name, entity_type, production_entity production_entity
3155 FROM mth_equip_entities_mst
3156 UNION ALL
3157 SELECT plant_pk_key site_id, plant_pk_key entity_pk_key, plant_name entity_name, 'SITE', production_site production_entity
3158 FROM mth_plants_d
3159 UNION ALL
3160 SELECT plant_fk_key site_id, resource_pk_key entity_pk_key, resource_name entity_name, 'RESOURCE', production_resource production_entity
3161 FROM mth_resources_d
3162 UNION ALL
3163 SELECT plant_fk_key site_id, equipment_pk_key entity_pk_key, equipment_name entity_name, 'EQUIPMENT', production_equipment production_entity
3164 FROM mth_equipments_d
3165 )b
3166 WHERE b.site_id = a.plant_fk_key
3167 AND a.plant_fk_key=l_plant_fk_key
3168 AND shift_date>=l_start_date
3169 AND shift_date<=l_end_date
3170 AND UPPER(a.shift_type)='BOTH'
3171 --AND a.line_num=l_line_num
3172 AND a.shift_num=l_shift_num
3173 AND a.shift_name=l_shift_name
3174 AND l_shift_def.start_time=To_Char(from_date,'HH24:MI:SS')
3175 AND l_shift_def.end_time=To_Char(To_Date,'HH24:MI:SS'));
3176
3177 END IF;
3178 IF UPPER(l_shift_type)='PROD-SHIFT' THEN
3179 INSERT INTO mth_equipment_shifts_d(equipment_fk_key,availability_date,shift_workday_fk_key,from_date,To_Date,line_num,availability_flag,entity_type,creation_date,last_update_date,creation_system_id,
3180 last_update_system_id) (
3181
3182 SELECT b.entity_pk_key entity_pk_key,a.shift_date shift_date,a.shift_workday_pk_key shift_workday_fk_key,a.from_date from_date,a.to_date To_Date,1 line_num,'Y',
3183 b.entity_type entity_type,SYSDATE,SYSDATE,-1,-99999
3184
3185 --INTO l_site_id,l_entity_pk_key,l_entity_name,l_entity_type,l_production_entity,
3186 --l_shift_workday_pk_key,l1_shift_date,l_from_date,l_to_date
3187 FROM mth_workday_shifts_d a ,
3188 (
3189 SELECT plant_fk_key site_id, entity_pk_key, entity_name, entity_type, production_entity production_entity
3190 FROM mth_equip_entities_mst
3191 UNION ALL
3192 SELECT plant_pk_key site_id, plant_pk_key entity_pk_key, plant_name entity_name, 'SITE', production_site production_entity
3193 FROM mth_plants_d
3194 UNION ALL
3195 SELECT plant_fk_key site_id, resource_pk_key entity_pk_key, resource_name entity_name, 'RESOURCE', production_resource production_entity
3196 FROM mth_resources_d
3197 UNION ALL
3198 SELECT plant_fk_key site_id, equipment_pk_key entity_pk_key, equipment_name entity_name, 'EQUIPMENT', production_equipment production_entity
3199 FROM mth_equipments_d
3200 )b
3201 WHERE b.site_id = a.plant_fk_key
3202 AND a.plant_fk_key=l_plant_fk_key
3203 AND shift_date>=l_start_date
3204 AND shift_date<=l_end_date
3205 AND b.production_entity='Y'
3206 AND UPPER(a.shift_type)='PROD-SHIFT'
3207 --AND a.line_num=l_line_num
3208 AND a.shift_num=l_shift_num
3209 AND a.shift_name=l_shift_name
3210 AND l_shift_def.start_time=To_Char(from_date,'HH24:MI:SS')
3211 AND l_shift_def.end_time=To_Char(To_Date,'HH24:MI:SS'));
3212
3213 END IF;
3214 IF UPPER(l_shift_type)='NON-PROD-SHIFT' THEN
3215 INSERT INTO mth_equipment_shifts_d(equipment_fk_key,availability_date,shift_workday_fk_key,from_date,To_Date,line_num,availability_flag,entity_type,creation_date,last_update_date,creation_system_id,
3216 last_update_system_id) (
3217
3218 SELECT b.entity_pk_key entity_pk_key,a.shift_date shift_date,a.shift_workday_pk_key shift_workday_fk_key,a.from_date from_date,a.to_date To_Date,1 line_num,'Y',
3219 b.entity_type entity_type,SYSDATE,SYSDATE,-1,-99999
3220
3221 --INTO l_site_id,l_entity_pk_key,l_entity_name,l_entity_type,l_production_entity,
3222 --l_shift_workday_pk_key,l1_shift_date,l_from_date,l_to_date
3223 FROM mth_workday_shifts_d a ,
3224 (
3225 SELECT plant_fk_key site_id, entity_pk_key, entity_name, entity_type, production_entity production_entity
3226 FROM mth_equip_entities_mst
3227 UNION ALL
3228 SELECT plant_pk_key site_id, plant_pk_key entity_pk_key, plant_name entity_name, 'SITE', production_site production_entity
3229 FROM mth_plants_d
3230 UNION ALL
3231 SELECT plant_fk_key site_id, resource_pk_key entity_pk_key, resource_name entity_name, 'RESOURCE', production_resource production_entity
3232 FROM mth_resources_d
3233 UNION ALL
3234 SELECT plant_fk_key site_id, equipment_pk_key entity_pk_key, equipment_name entity_name, 'EQUIPMENT', production_equipment production_entity
3235 FROM mth_equipments_d
3236 )b
3237 WHERE b.site_id = a.plant_fk_key
3238 AND a.plant_fk_key=l_plant_fk_key
3239 AND shift_date>=l_start_date
3240 AND shift_date<=l_end_date
3241 AND b.production_entity='N'
3242 AND UPPER(a.shift_type)='NON-PROD-SHIFT'
3243 --AND a.line_num=l_line_num
3244 AND a.shift_num=l_shift_num
3245 AND a.shift_name=l_shift_name
3246 AND l_shift_def.start_time=To_Char(from_date,'HH24:MI:SS')
3247 AND l_shift_def.end_time=To_Char(To_Date,'HH24:MI:SS'));
3248
3249 END IF;
3250
3251 COMMIT;
3252 END LOOP;
3253
3254 IF(l_sh_start_date > p_start_date)
3255 THEN
3256 l_start_date := l_sh_start_date+1;
3257 ELSE
3258 l_start_date := p_start_date;
3259 END IF;
3260
3261 for i IN 1.. N+1
3262 LOOP
3263
3264
3265 l_last_update_system_id := -99999;
3266 l_last_update_date := l_sysdate;
3267 l_creation_system_id := -1;
3268
3269 INSERT INTO mth_workday_shifts_D(shift_workday_pk_key, shift_workday_pk,
3270 shift_date,shift_date_julian,plant_fk_key,shift_type,graveyard_shift,from_date,to_date, shift_num,shift_name,source_org_code,system_fk_key,
3271 creation_date,last_update_date,creation_system_id,
3272 last_update_system_id)
3273 VALUES(mth_workdays_shifts_s.nextval,TO_CHAR(l_start_date,'yyyymmdd-hh24:mi:ss')||'-'||l_plant_pk|| "MTH_UTIL_PKG"."MTH_UA_GET_VAL"(),l_start_date ,TO_NUMBER(TO_CHAR( l_start_date ,'J')),
3274 l_plant_fk_key,'BOTH',null,null,
3275 null,null,FND_PROFILE.VALUE('MTH_CATCH_ALL_NAME'),null, l_system_fk_key,
3276 l_sysdate, l_sysdate,l_creation_system_id,l_last_update_system_id);
3277 l_start_date := l_start_date+1;
3278
3279
3280 END LOOP;
3281 COMMIT;
3282 IF(l_sh_start_date > p_start_date)
3283 THEN
3284 l_start_date := l_sh_start_date+1;
3285 ELSE
3286 l_start_date := p_start_date;
3287 END IF;
3288
3289 INSERT INTO mth_equipment_shifts_d(equipment_fk_key,availability_date,shift_workday_fk_key,from_date,To_Date,line_num,availability_flag,entity_type,creation_date,last_update_date,creation_system_id,
3290 last_update_system_id) (
3291
3292 SELECT b.entity_pk_key entity_pk_key,a.shift_date shift_date,a.shift_workday_pk_key shift_workday_fk_key,null,null,1 line_num,'Y',
3293 b.entity_type entity_type,SYSDATE,SYSDATE,-1,-99999
3294
3295 --INTO l_site_id,l_entity_pk_key,l_entity_name,l_entity_type,l_production_entity,
3296 --l_shift_workday_pk_key,l1_shift_date,l_from_date,l_to_date
3297 FROM mth_workday_shifts_d a ,
3298 (
3299 SELECT plant_fk_key site_id, entity_pk_key, entity_name, entity_type, production_entity production_entity
3300 FROM mth_equip_entities_mst
3301 UNION ALL
3302 SELECT plant_pk_key site_id, plant_pk_key entity_pk_key, plant_name entity_name, 'SITE', production_site production_entity
3303 FROM mth_plants_d
3304 UNION ALL
3305 SELECT plant_fk_key site_id, resource_pk_key entity_pk_key, resource_name entity_name, 'RESOURCE', production_resource production_entity
3306 FROM mth_resources_d
3307 UNION ALL
3308 SELECT plant_fk_key site_id, equipment_pk_key entity_pk_key, equipment_name entity_name, 'EQUIPMENT', production_equipment production_entity
3309 FROM mth_equipments_d
3310 )b
3311 WHERE b.site_id = a.plant_fk_key
3312 AND a.plant_fk_key=l_plant_fk_key
3313 AND shift_date>=l_start_date
3314 AND shift_date<=l_end_date
3315 AND UPPER(a.shift_type)='BOTH'
3316 AND a.FROM_DATE IS NULL
3317 AND a .To_Date IS NULL
3318 --AND a.line_num=NULL
3319 AND a.shift_num IS NULL
3320 );
3321 END IF;
3322 END IF;
3323
3324 --handle exceptions
3325 --EXCEPTION
3326 -- WHEN NO_DATA_FOUND THEN
3327 -- RAISE_APPLICATION_ERROR (-20001,
3328 -- 'Exception has occured');
3329
3330 END GENERATE_SHIFTS;
3331
3332
3333
3334 /* ****************************************************************************
3335 * Function :get_incremental_tag_data *
3336 * Description :Insert the error row into the error with the error code *
3337 * File Name :MTHSUSAB.PLS *
3338 * Visibility :Private *
3339 * Parameters :p_tag_value - tag value *
3340 * p_is_number - 1 if tag value is number; 0 otherwise *
3341 * p_is_cumulative - 1 to apply incremental logic; *
3342 * 0 otherwise *
3343 * p_is_assending - 1 if tag is assending order; *
3344 * 0 otherwise *
3345 * p_initial_value - Tag initial value *
3349 * be applied; return p_tag_value otherwise *
3346 * p_max_reset_value - *
3347 * p_prev_tag_value - Previous tag value *
3348 * Return Value :Incremental value if incremental logic needs to be *
3350 **************************************************************************** */
3351
3352 FUNCTION get_incremental_tag_data(P_TAG_VALUE IN VARCHAR2,
3353 P_IS_NUMBER IN NUMBER,
3354 P_IS_CUMULATIVE IN NUMBER,
3355 P_IS_ASSENDING IN NUMBER,
3356 P_INITIAL_VALUE IN NUMBER,
3357 P_MAX_RESET_VALUE IN NUMBER,
3358 p_prev_tag_value IN VARCHAR2) RETURN VARCHAR2
3359 IS
3360 v_incr_value VARCHAR2(255);
3361 BEGIN
3362 -- 1. Do not need to apply the incremental logic
3363 IF (P_IS_NUMBER IS NULL OR P_IS_NUMBER <> 1 OR
3364 P_IS_CUMULATIVE IS NULL OR P_IS_CUMULATIVE <> 1) THEN
3365 v_incr_value := P_TAG_VALUE;
3366 ELSIF (P_IS_CUMULATIVE = 1 AND P_IS_ASSENDING = 1 AND
3367 p_prev_tag_value IS NOT NULL) THEN
3368 -- 2. Assending tag and it is not the first reding
3369 -- 2.1 not reset
3370 v_incr_value := CASE WHEN To_Number(P_TAG_VALUE) >= To_Number(p_prev_tag_value)
3371 THEN P_TAG_VALUE - p_prev_tag_value
3372 -- 2.2 after reset
3373 ELSE P_MAX_RESET_VALUE - p_prev_tag_value +
3374 P_TAG_VALUE
3375 END;
3376 ELSIF (P_IS_CUMULATIVE = 1 AND P_IS_ASSENDING = 1 AND
3377 p_prev_tag_value IS NULL) THEN
3378 -- 3. Assending tag and it is the first reding
3379 -- 3.1 First reading
3380 v_incr_value := CASE WHEN To_Number(P_TAG_VALUE) >= To_Number(P_INITIAL_VALUE)
3381 THEN P_TAG_VALUE - P_INITIAL_VALUE
3382 -- 3.2 First reading but reset already
3383 ELSE P_MAX_RESET_VALUE - P_INITIAL_VALUE + P_TAG_VALUE
3384 END;
3385 ELSIF (P_IS_CUMULATIVE = 1 AND P_IS_ASSENDING = 0 AND
3386 p_prev_tag_value IS NOT NULL) THEN
3387 -- 4. Descending tag and it is not the first reding
3388 -- 4.1 not reset
3389 v_incr_value := CASE WHEN To_Number(P_TAG_VALUE) <= To_Number(p_prev_tag_value)
3390 THEN p_prev_tag_value - P_TAG_VALUE
3391 -- 2.2 after reset
3392 ELSE p_prev_tag_value + P_MAX_RESET_VALUE -
3393 P_TAG_VALUE
3394 END;
3395 ELSIF (P_IS_CUMULATIVE = 1 AND P_IS_ASSENDING = 0 AND
3396 p_prev_tag_value IS NULL) THEN
3397 -- 5. Descending tag and it is the first reding
3398 -- 4.1 not reset
3399 v_incr_value := CASE WHEN To_Number(P_TAG_VALUE) <= To_Number(P_INITIAL_VALUE)
3400 THEN P_INITIAL_VALUE - P_TAG_VALUE
3401 -- 3.2 First reading but reset already
3402 ELSE P_INITIAL_VALUE + P_MAX_RESET_VALUE - P_TAG_VALUE
3403 END;
3404 END IF;
3405 RETURN v_incr_value;
3406
3407 END get_incremental_tag_data;
3408
3409
3410
3411 /* ****************************************************************************
3412 * Procedure :update_tag_to_latest_tab *
3413 * Description :Update an existing the latest reading time and tag value *
3414 * for a tag if table MTH_TAG_READINGS_LATEST already *
3415 * has a entry for the tag. Otherwise, insert a new row *
3416 * File Name :MTHUTILB.PLS *
3417 * Visibility :Private *
3418 * Parameters :p_tag_code - tag code *
3419 * p_latest_reading_time - reading time of the latest *
3420 * p_latest_tag_value - latest tag reading *
3421 * p_lookup_entry_exist - whether the entry with the *
3422 * same tag code exists in the *
3423 * MTH_TAG_READINGS_LATEST or not *
3424 * Return Value :None *
3425 **************************************************************************** */
3426
3427 PROCEDURE update_tag_to_latest_tab(p_tag_code IN VARCHAR2,
3428 p_latest_reading_time IN DATE,
3429 p_latest_tag_value IN VARCHAR2,
3430 p_lookup_entry_exist IN BOOLEAN)
3431 IS
3432 BEGIN
3433 -- If the entry exists, do the update; otherwise, do the insert
3434 IF (p_lookup_entry_exist) THEN
3435 UPDATE MTH_TAG_READINGS_LATEST
3436 SET reading_time = p_latest_reading_time, tag_value = p_latest_tag_value
3437 WHERE tag_code = p_tag_code;
3438 ELSE
3439 INSERT INTO MTH_TAG_READINGS_LATEST
3440 (TAG_CODE, READING_TIME, TAG_VALUE) VALUES
3441 (p_tag_code, p_latest_reading_time, p_latest_tag_value);
3442 END IF;
3443
3444 END update_tag_to_latest_tab;
3445
3446
3447 /* ****************************************************************************
3448 * Function :MTH_IS_TAG_RAW_DATA_ROW_VALID *
3449 * Description :Check if the raw from MTH_TAG_READINGS_RAW is valid *
3450 * or not. *
3451 * File Name :MTHUTILB.PLS *
3452 * Visibility :Private *
3453 * Parameters :p_tag_code - Tag code *
3454 * p_reading_time - Reading time *
3458 * 0 otherwise *
3455 * p_tag_value - tag value *
3456 * p_is_number - 1 if tag value is number; 0 otherwise *
3457 * p_is_cumulative - 1 to apply incremental logic; *
3459 * p_is_assending - 1 if tag is assending order; *
3460 * 0 otherwise *
3461 * p_initial_value - Tag initial value *
3462 * p_max_reset_value - *
3463 * p_prev_reading_time - reading time for the previous *
3464 * tag reading *
3465 * OUT parameters: p_is_valid_timestamp - Whether the reading time *
3466 * from the current entry can be used to calculate the *
3467 * from time of the next reading *
3468 * Return Value : Found violations of the following rules: *
3469 * 'NGV' - Usage value is negative. *
3470 * 'OTR' - Usage value is out of range defined *
3471 * for a cumulative tag. *
3472 * 'OTO' - The raw reading data is out of order. *
3473 * 'DUP' - The raw reading data is duplicated. *
3474 * 'FTD' - Data in future time *
3475 * NULL - Valid Row *
3476 ***************************************************************************** */
3477
3478 FUNCTION MTH_IS_TAG_RAW_DATA_ROW_VALID
3479 (p_tag_code IN VARCHAR2,
3480 p_mast_tag_code IN VARCHAR2,
3481 p_reading_time IN DATE,
3482 p_tag_value IN VARCHAR2,
3483 p_is_number IN NUMBER,
3484 p_is_cumulative IN NUMBER,
3485 p_is_assending IN NUMBER,
3486 p_initial_value IN NUMBER,
3487 p_max_reset_value IN NUMBER,
3488 p_prev_reading_time IN DATE,
3489 p_is_valid_timestamp OUT NOCOPY BOOLEAN) RETURN VARCHAR2
3490 IS
3491 v_err_code VARCHAR2(240) := '';
3492 BEGIN
3493 p_is_valid_timestamp := FALSE;
3494 IF (p_is_number = 1 AND p_tag_value < 0) THEN
3495 v_err_code := v_err_code || 'NGV ';
3496 IF p_is_cumulative = 0 THEN
3497 p_is_valid_timestamp := TRUE;
3498 END IF;
3499 END IF;
3500 IF(p_tag_value IS NULL) THEN
3501 v_err_code := v_err_code || 'ITD ';
3502 p_is_valid_timestamp := FALSE;
3503 END IF;
3504 IF (p_is_number = 1 AND p_is_cumulative = 1 AND
3505 p_tag_value > p_max_reset_value) THEN
3506 v_err_code := v_err_code || 'OTR ';
3507 p_is_valid_timestamp := FALSE;
3508 END IF;
3509 IF (p_prev_reading_time IS NOT NULL AND
3510 p_reading_time < p_prev_reading_time) THEN
3511 v_err_code := v_err_code || 'OTO ';
3512 p_is_valid_timestamp := FALSE;
3513 END IF;
3514 IF (p_prev_reading_time IS NOT NULL AND
3515 p_reading_time = p_prev_reading_time) THEN
3516 v_err_code := v_err_code || 'DUP ';
3517 p_is_valid_timestamp := FALSE;
3518 END IF;
3519 IF (p_reading_time > SYSDATE) THEN
3520 v_err_code := v_err_code || 'FTD ';
3521 p_is_valid_timestamp := FALSE;
3522 END IF;
3523 IF (p_mast_tag_code IS NULL) THEN
3524 v_err_code := v_err_code || 'MTC ';
3525 p_is_valid_timestamp := FALSE;
3526 END IF;
3527
3528 IF (Length(v_err_code) = 0) THEN
3529 v_err_code := NULL;
3530 p_is_valid_timestamp := TRUE;
3531 END IF;
3532 RETURN v_err_code;
3533 END MTH_IS_TAG_RAW_DATA_ROW_VALID;
3534
3535
3536
3537 /* ****************************************************************************
3538 * Procedure :MTH_LOAD_TAG_RAW_TO_PROCESSED *
3539 * Description :Load data from the table MTH_TAG_READINGS_RAW *
3540 * into meter readings table MTH_TAG_READINGS_RAW_PROCESSED *
3541 * File Name :MTHUTILB.PLS *
3542 * Visibility :Private *
3543 * Parameters :p_curr_partition (value of the partition column *
3544 * :p_from_tz (value for from time zone ) *
3545 * :p_to_tz (value for to time zone ) *
3546 **************************************************************************** */
3547
3548 PROCEDURE MTH_LOAD_TAG_RAW_TO_PROCESSED(p_curr_partition IN NUMBER, p_from_tz IN
3549 VARCHAR2, p_to_tz IN VARCHAR2)
3550 IS
3551
3552 --Paramters for handling time zone conversion
3553
3554 v_from_tz VARCHAR2(255) := p_from_tz;
3555 v_to_tz VARCHAR2(255) := p_to_tz;
3556 v_date_constant CONSTANT DATE := To_Date('11/16/2010','mm/dd/yyyy');
3557 v_factor_time_zone NUMBER := 0;
3558
3559 -- Fetch raw data for active tags from the patition
3560 -- ordered by TAG_CODE, READING_TIME
3561 CURSOR c_getRawData (p_processing_flag IN NUMBER,v_factor_time_zone IN NUMBER) IS
3562 SELECT R.TAG_CODE, T.TAG_CODE TAG_MAST_TAG_CODE,R.READING_TIME + v_factor_time_zone READING_TIME, R.TAG_DATA,
3563 Decode(DATA_TYPE, 'NUM', 1, 0) IS_NUMBER,
3564 Decode(T.READING_TYPE, 'CHNG', 1, 0) AS IS_CUMULATIVE,
3565 Decode(T.ORDER_TYPE, 'ASC', 1, 0) IS_ASSENDING,
3566 T.INITIAL_VALUE, T.MAX_RESET_VALUE, R.GROUP_ID, R.CREATION_DATE, R.USER_ATTR1, R.USER_ATTR2, R.USER_ATTR3, R.USER_ATTR4, R.USER_ATTR5, R.USER_MEASURE1, R.USER_MEASURE2, R.USER_MEASURE3, R.USER_MEASURE4, R.USER_MEASURE5, R.QUALITY_FLAG
3567 FROM MTH_TAG_READINGS_RAW R, MTH_TAG_MASTER T
3568 WHERE R.PROCESSING_FLAG = p_processing_flag AND
3569 R.TAG_CODE = T.TAG_CODE (+)
3573 CURSOR c_getPrevReadingTimeForTag (p_tag_code IN VARCHAR2) IS
3570 ORDER BY TAG_CODE, READING_TIME;
3571
3572 -- Fetch the previous reading time for the given tag code
3574 SELECT TAG_VALUE, READING_TIME
3575 FROM MTH_TAG_READINGS_LATEST
3576 WHERE TAG_CODE = p_tag_code;
3577
3578 v_curr_partition NUMBER := p_curr_partition;
3579 v_curr_tag_code VARCHAR2(255) := NULL;
3580 v_prev_tag_code VARCHAR2(255) := NULL;
3581 v_prev_reading_time DATE := NULL;
3582 v_prev_tag_value VARCHAR2(255) := NULL;
3583 v_lookup_entry_exist boolean;
3584 v_err_code VARCHAR2(240) := NULL;
3585 v_is_valid_timestamp BOOLEAN := FALSE;
3586 v_incr_tag_value VARCHAR2(255);
3587 -- Keep the information of the last reading to be used to update LATEST table
3588 -- after the LOOP for the latest valid timestamp of the last tag code
3589 v_last_tag_code VARCHAR2(255) := NULL;
3590 v_last_reading_time DATE := NULL;
3591 v_last_tag_value VARCHAR2(255) := NULL;
3592
3593
3594 Begin
3595
3596 -- 1. First switch the partition for the meter readings raw table
3597 --mth_util_pkg.switch_column_default_value(v_raw_tab_name, v_curr_partition);
3598 IF (v_curr_partition = 0) THEN
3599 -- No data available in the table to be processed
3600 RETURN;
3601 END IF;
3602
3603 -- If the from_tz and to_tz values are not null, then call the function to
3604 -- account for time zone conversion
3605
3606 IF (v_from_tz IS NOT NULL AND v_to_tz IS NOT NULL) THEN
3607
3608 v_factor_time_zone := MTH_TZ_UTIL.convert_tz(v_date_constant,
3609 v_from_tz,
3610 v_to_tz) - v_date_constant ;
3611 END IF;
3612
3613
3614 -- 2. Fetch the raw data for active tag and process each row
3615 FOR r_raw_data IN c_getRawData(v_curr_partition,v_factor_time_zone) LOOP
3616 v_curr_tag_code := r_raw_data.TAG_CODE;
3617
3618 -- 2.0 Update/Create entry in MTH_TAG_METER_READINGS_LATEST for previous tag
3619 -- that is stored in v_last_... .
3620 -- only if the there is no error or the timestamp is valid even though
3621 -- there is an error in the preivoud set of values kept in v_last....
3622 IF (v_last_tag_code IS NOT NULL AND v_last_tag_code <> v_curr_tag_code) THEN
3623 update_tag_to_latest_tab(v_last_tag_code,
3624 v_last_reading_time,
3625 v_last_tag_value,
3626 v_lookup_entry_exist);
3627
3628 -- Erase the information for the last record since it has been saved
3629 -- into the LATEST table already.
3630 v_last_tag_code := NULL;
3631 v_last_reading_time := NULL;
3632 v_last_tag_value := NULL;
3633 END IF;
3634
3635 -- 2.1 Find the meters and latest reading for the new tag code
3636 IF (v_prev_tag_code IS NULL OR v_prev_tag_code <> v_curr_tag_code) THEN
3637 -- 2.1.0 Reset the previous reading for the new tag code
3638 v_prev_tag_value := NULL;
3639 v_prev_reading_time := NULL;
3640
3641 -- 2.1.2 Find previous reading time and tag value for the currenttag
3642 -- in the lookup table MTH_TAG_READINGS_LATEST
3643 OPEN c_getPrevReadingTimeForTag(v_curr_tag_code);
3644 FETCH c_getPrevReadingTimeForTag INTO
3645 v_prev_tag_value, v_prev_reading_time;
3646 CLOSE c_getPrevReadingTimeForTag;
3647 v_lookup_entry_exist := v_prev_reading_time IS NOT NULL;
3648 END IF;
3649
3650 -- 2.2 Validate the raw data
3651 v_err_code := MTH_IS_TAG_RAW_DATA_ROW_VALID (r_raw_data.TAG_CODE,
3652 r_raw_data.TAG_MAST_TAG_CODE,
3653 r_raw_data.READING_TIME,
3654 r_raw_data.TAG_DATA,
3655 r_raw_data.IS_NUMBER,
3656 r_raw_data.IS_CUMULATIVE,
3657 r_raw_data.IS_ASSENDING,
3658 r_raw_data.INITIAL_VALUE,
3659 r_raw_data.MAX_RESET_VALUE,
3660 v_prev_reading_time,
3661 v_is_valid_timestamp);
3662
3663
3664 -- 2.3 Insert data into either meter readings or error table
3665 IF (v_err_code IS NOT NULL OR Length(v_err_code) > 0) THEN
3666 -- 2.3.1 Insert the error row to error table if there is any error
3667 INSERT INTO MTH_TAG_READINGS_UNPROCESS_ERR
3668 (GROUP_ID, READING_TIME , TAG_CODE, TAG_DATA, CREATION_DATE, USER_ATTR1, USER_ATTR2, USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1, USER_MEASURE2, USER_MEASURE3, USER_MEASURE4, USER_MEASURE5, QUALITY_FLAG, REPROCESSED_READY_YN, ERR_CODE)
3669 VALUES (r_raw_data.GROUP_ID, r_raw_data.READING_TIME, r_raw_data.TAG_CODE, r_raw_data.TAG_DATA, r_raw_data.CREATION_DATE, r_raw_data.USER_ATTR1, r_raw_data.USER_ATTR2, r_raw_data.USER_ATTR3, r_raw_data.USER_ATTR4, r_raw_data.USER_ATTR5,
3670 r_raw_data.USER_MEASURE1, r_raw_data.USER_MEASURE2, r_raw_data.USER_MEASURE3, r_raw_data.USER_MEASURE4, r_raw_data.USER_MEASURE5, r_raw_data.QUALITY_FLAG,'N', v_err_code);
3671 ELSE
3672 -- 2.3.2 Get the incremental value
3673 v_incr_tag_value := get_incremental_tag_data(r_raw_data.TAG_DATA,
3674 r_raw_data.IS_NUMBER,
3675 r_raw_data.IS_CUMULATIVE,
3676 r_raw_data.IS_ASSENDING,
3677 r_raw_data.INITIAL_VALUE,
3678 r_raw_data.MAX_RESET_VALUE,
3679 v_prev_tag_value);
3680
3681 -- 2.3.3 Insert the data into the mth_tag_readings_processed table
3682 INSERT INTO MTH_TAG_READINGS_RAW_PROCESSED (GROUP_ID, READING_TIME , TAG_CODE, TAG_DATA, CREATION_DATE, USER_ATTR1, USER_ATTR2, USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1, USER_MEASURE2, USER_MEASURE3, USER_MEASURE4,
3683 USER_MEASURE5, QUALITY_FLAG) VALUES ( r_raw_data.GROUP_ID, r_raw_data.READING_TIME, r_raw_data.TAG_CODE, v_incr_tag_value, r_raw_data.CREATION_DATE, r_raw_data.USER_ATTR1, r_raw_data.USER_ATTR2, r_raw_data.USER_ATTR3,
3684 r_raw_data.USER_ATTR4, r_raw_data.USER_ATTR5, r_raw_data.USER_MEASURE1, r_raw_data.USER_MEASURE2, r_raw_data.USER_MEASURE3, r_raw_data.USER_MEASURE4, r_raw_data.USER_MEASURE5, r_raw_data.QUALITY_FLAG);
3685
3686 END IF;
3687
3688 -- 2.4 Save the current data as previous data, which can be used for :
3689 -- - Use the previous reading time - 1 second as FROM TIME for new
3690 -- data
3691 -- - Use the previous tag value to calcuate the incremental value
3692 -- - Save all of above into lookup table MTH_TAG_METER_READINGS_LATEST
3693 -- when processing the last reading for this tag, wihch can be
3694 -- identified by find the next and different tag code
3695 IF (v_err_code IS NULL OR Length(v_err_code) = 0) or
3696 Length(v_err_code) > 0 and v_is_valid_timestamp THEN
3697 v_prev_tag_code := v_curr_tag_code;
3698 v_prev_tag_value := r_raw_data.TAG_DATA;
3699 v_prev_reading_time := Greatest(r_raw_data.READING_TIME,
3700 Nvl(v_prev_reading_time,
3701 r_raw_data.READING_TIME));
3702 -- Update the information for the last reading to update LATEST table
3703 -- for step 2.6
3704 v_last_tag_code := v_prev_tag_code;
3705 v_last_reading_time := v_prev_reading_time;
3706 v_last_tag_value := v_prev_tag_value;
3707 END IF;
3708 END LOOP;
3709
3710 -- 2.6 Update/Create entry in MTH_TAG_READINGS_LATEST for the last tag
3711 -- Since we have kept the information for the last reading, just
3712 -- need to make sure that v_last_tag_code exists.
3713 IF (v_last_tag_code is not NULL) THEN
3714 update_tag_to_latest_tab(v_last_tag_code,
3715 v_last_reading_time,
3716 v_last_tag_value,
3717 v_lookup_entry_exist);
3718 END IF;
3719
3720
3721
3722 END MTH_LOAD_TAG_RAW_TO_PROCESSED;
3723 /* ****************************************************************************
3724 * Procedure :INSERT_OPEN_SALES_ORDERS*
3725 * Description :The procedure will insert open sales orders in the table, passed as a parameter,
3726 *in the partition that is empty. In the initial run, both the partitions will be empty,
3727 *then the data will move to partition with partition key=1. The alternate partition key
3728 *will be passed as the out parameter *
3729 * File Name :MTHUTILB.PLS *
3730 * Visibility :Public
3731 * Modification log : *
3732 * Author Date Change: Amrit Kaur 28-Jul-2011 Initial Creation *
3733 *
3734 ******************************************************************************/
3735
3736
3737 PROCEDURE insert_open_sales_orders(p_table_name IN VARCHAR2, p_current_processing_flag OUT NOCOPY NUMBER)
3738 IS
3739 v_stmt VARCHAR2(400);
3740 v_stmt_no NUMBER;
3741 v_count NUMBER;
3742 v_schema_name VARCHAR2(100);
3743 v_status VARCHAR2(30) ;
3744 v_industry VARCHAR2(30) ;
3745 l_source_location VARCHAR2(100) := fnd_profile.value('MTH_OWB_SOURCE_LOCATION');
3746 l_source_service VARCHAR2(100) := fnd_profile.value('MTH_SOURCE_DB_SERVICE_NAME');
3747 l_dblink VARCHAR2(400);
3748 l_sql_stmt VARCHAR2(1000);
3749 l_installed VARCHAR2(1);
3750
3751 CURSOR get_db_link (source_service VARCHAR2, source_location VARCHAR2)
3752 IS
3753 SELECT db_link
3754 FROM user_db_links
3755 WHERE Upper(db_link) like Upper(source_service||'%@'||source_location);
3756
3757 BEGIN
3758
3759
3760
3761 OPEN get_db_link(l_source_service, l_source_location);
3762 FETCH get_db_link INTO l_dblink;
3763 CLOSE get_db_link;
3764
3765
3766
3767 /*The procedure will insert open sales orders in the table, passed as a parameter,
3768 *in the partition that is empty. In the initial run, both the partitions will be empty,
3769 *then the data will move to partition with partition key=1. The alternate partition key
3770 *will be passed as the out parameter*/
3771
3772 v_stmt_no := 10;
3773 -- IF (NOT FND_INSTALLATION.GET_APP_INFO@l_db_link(
3774 -- application_short_name => 'MSC'
3775 -- , status => v_status
3776 -- , industry => v_industry
3777 -- , oracle_schema => v_schema_name)) THEN
3778 -- RAISE_APPLICATION_ERROR (-20002,'Could not find MSC product');
3779 -- END IF;
3780 l_sql_stmt := 'DECLARE ' ||
3781 ' v_schema_name VARCHAR2(100); ' ||
3782 ' v_status VARCHAR2(30); ' ||
3783 ' v_industry VARCHAR2(30); ' ||
3784 ' v_out_value VARCHAR2(1); ' ||
3785 'BEGIN ' ||
3786 ' IF FND_INSTALLATION.GET_APP_INFO@' ||
3787 l_dblink || '( ' ||
3788 'application_short_name => ''MSC'', ' ||
3789 'status => v_status, ' ||
3790 'industry => v_industry, ' ||
3791 'oracle_schema => v_schema_name) THEN ' ||
3792 'v_out_value := ''Y''; ' ||
3793 ' ELSE ' ||
3794 'v_out_value := ''N''; ' ||
3795 ' END IF; ' ||
3796 ' :b1 := v_out_value; ' ||
3797 'END;';
3798
3799 -- Dbms_Output.put_line(l_sql_stmt);
3800
3801 EXECUTE IMMEDIATE l_sql_stmt USING OUT l_installed;
3802
3803 -- Dbms_Output.put_line(l_installed);
3804 IF(l_installed='N') THEN
3805 RAISE_APPLICATION_ERROR(-20002,'Could not find MSC product');
3806 END IF;
3807
3808 /*Check which partition is empty*/
3809 v_stmt_no := 20;
3810 v_stmt := 'SELECT COUNT(1) FROM '||p_table_name|| '@' || l_dblink || ' WHERE PROCESSING_FLAG = 1';
3811 EXECUTE IMMEDIATE v_stmt INTO v_count;
3812
3813 /*v_count will either be 0 or greater than 0. v_count greater than 0 implies that
3814 *partition 2 is empty. All the open sales orders should go in partition 2 while
3815 *partition 1 should be used in the ETL. For the initial load, any partition will be
3816 *empty.*/
3817
3818 v_stmt_no := 30;
3819 IF
3820 v_count > 0 THEN
3824 v_stmt := 'INSERT INTO '||p_table_name|| '@' || l_dblink || ' (EBS_HEADER_ID, PROCESSING_FLAG) (SELECT HEADER_ID, 2 FROM OE_ORDER_HEADERS_ALL@' ||l_dblink||' WHERE OPEN_FLAG = '||''''||'Y'||''''||')';
3821 /*partition 2 is empty. Open sales orders will be populated with PROCESSING_FLAG =2*/
3822 v_stmt_no := 40;
3823 p_current_processing_flag := 1;
3825 EXECUTE IMMEDIATE v_stmt;
3826 COMMIT;
3827
3828 ELSE
3829 /*partition 1 is empty Open sales orders will be populated with PROCESSING_FLAG =1*/
3830 v_stmt_no := 40;
3831 p_current_processing_flag := 2;
3832 v_stmt := 'INSERT INTO '||p_table_name|| '@' || l_dblink || ' (EBS_HEADER_ID, PROCESSING_FLAG) (SELECT HEADER_ID, 1 FROM OE_ORDER_HEADERS_ALL@' ||l_dblink||' WHERE OPEN_FLAG = '||''''||'Y'||''''||')';
3833 EXECUTE IMMEDIATE v_stmt;
3834 COMMIT;
3835
3836 END IF;
3837
3838
3839 EXCEPTION
3840 WHEN OTHERS THEN
3841 RAISE_APPLICATION_ERROR (-20002, SQLERRM||' at '||v_stmt_no);
3842 COMMIT;
3843 END insert_open_sales_orders;
3844
3845
3846
3847
3848 /*****************************************************************************
3849 * Procedure : Get_Run_Log_Dates *
3850 * Description : The procedure returns from and to dates from run log. *
3851 * File Name : MTHUTILB.pls *
3852 * Visibility : Public *
3853 *****************************************************************************/
3854 PROCEDURE Get_Run_Log_Dates(
3855 p_fact_table IN VARCHAR2,
3856 p_db_global_name IN VARCHAR2 DEFAULT NULL,
3857 p_hub_organization_code IN VARCHAR2 DEFAULT NULL,
3858 p_ebs_organization_id IN NUMBER DEFAULT NULL,
3859 p_from_date OUT NOCOPY DATE,
3860 p_to_date OUT NOCOPY DATE)
3861 IS
3862
3863 BEGIN
3864 mth_util_pkg.log_msg('GET_RUN_LOG_DATES start', mth_util_pkg.G_DBG_PROC_FUN_START);
3865 mth_util_pkg.log_msg('p_fact_table = ' || p_fact_table , mth_util_pkg.G_DBG_PARAM_VAL);
3866 mth_util_pkg.log_msg('p_db_global_name = ' || p_db_global_name , mth_util_pkg.G_DBG_PARAM_VAL);
3867 mth_util_pkg.log_msg('p_hub_organization_code = ' || p_hub_organization_code, mth_util_pkg.G_DBG_PARAM_VAL);
3868 mth_util_pkg.log_msg('p_ebs_organization_id = ' || p_ebs_organization_id , mth_util_pkg.G_DBG_PARAM_VAL);
3869
3870 SELECT MAX(FROM_DATE), MAX(TO_DATE)
3871 INTO p_from_date, p_to_date
3872 FROM MTH_RUN_LOG
3873 WHERE FACT_TABLE = p_fact_table
3874 AND (db_global_name = nvl(p_db_global_name,db_global_name)
3875 OR db_global_name IS NULL)
3876 AND (ebs_organization_id = nvl(p_ebs_organization_id,ebs_organization_id)
3877 OR ebs_organization_id IS NULL)
3878 AND (hub_organization_code = nvl(p_hub_organization_code,hub_organization_code)
3879 OR hub_organization_code IS NULL);
3880
3881 mth_util_pkg.log_msg('p_from_date = ' || to_char(p_from_date,'DD-MON-YYYY HH24:MI:SS'), mth_util_pkg.G_DBG_VAR_VAL);
3882 mth_util_pkg.log_msg('p_to_date = ' || to_char(p_to_date, 'DD-MON-YYYY HH24:MI:SS'), mth_util_pkg.G_DBG_VAR_VAL);
3883 mth_util_pkg.log_msg('GET_RUN_LOG_DATES end', mth_util_pkg.G_DBG_PROC_FUN_END);
3884
3885 END GET_RUN_LOG_DATES;
3886
3887
3888 /*****************************************************************************
3889 * Procedure : Initialize_Debug *
3890 * Description : The procedure obtains execution_id from MTH_EXECUTION_S. *
3891 * This is required when being called from OWB task to be able *
3892 * to set a unique execution_id. This is an optional step when *
3893 * being called from concurrent program. *
3894 * File Name : MTHUTILB.pls *
3895 * Visibility : Public *
3896 * Parameters : p_context_desc (current context description) *
3897 * p_execution_id *
3898 *****************************************************************************/
3899 PROCEDURE Initialize_Debug(
3900 p_context_desc IN VARCHAR2 DEFAULT NULL,
3901 p_execution_id IN OUT NOCOPY NUMBER
3902 ) IS
3903 BEGIN
3904
3905 mth_util_pkg.g_mth_debug := nvl(fnd_profile.value('MTH_DEBUG_LEVEL'),0);
3906 mth_util_pkg.g_debug_indent_level := 0;
3907 if FND_GLOBAL.CONC_REQUEST_ID <> -1 then
3908 mth_util_pkg.g_execution_id := -1;
3909 else
3910 if p_execution_id is null then
3911 mth_util_pkg.g_execution_id := MTH_EXECUTION_S.NEXTVAL;
3912 p_execution_id := mth_util_pkg.g_execution_id;
3913 else
3914 mth_util_pkg.g_execution_id := p_execution_id;
3915 end if;
3916
3917 select value
3918 into mth_util_pkg.g_debug_file_dir
3919 from v$parameter2
3920 where name='utl_file_dir'
3921 and rownum = 1;
3922
3923 end if;
3924 mth_util_pkg.log_msg('-----------------------------------------------------', 1);
3925 mth_util_pkg.log_msg('Initialize called for - ' || p_context_desc, 1);
3926
3927 EXCEPTION
3928 WHEN OTHERS THEN
3929 return;
3930
3931 END Initialize_Debug;
3932
3933
3934 /*****************************************************************************
3935 * Procedure : Log_Msg *
3936 * Description : The procedure logs the passed message appropriately. This *
3937 * takes two parameters 1. p_msg_text and 2. p_msg_level. If *
3938 * p_msg_level is less than or equal to profile option *
3942 * Parameters : p_msg_txt *
3939 * MTH_DEBUG_LEVEL then the message will be logged. *
3940 * File Name : MTHUTILB.pls *
3941 * Visibility : Public *
3943 * p_msg_lvl *
3944 *****************************************************************************/
3945 PROCEDURE Log_Msg(
3946 p_msg_txt IN VARCHAR2,
3947 p_msg_lvl IN NUMBER DEFAULT NULL
3948 ) IS
3949 fname utl_file.file_type;
3950 l_indent_level NUMBER;
3951 l_execution_id NUMBER;
3952 BEGIN
3953
3954 if mth_util_pkg.g_mth_debug is null then
3955 Initialize_Debug('Unknown',l_execution_id);
3956 end if;
3957
3958 if p_msg_lvl <= mth_util_pkg.g_mth_debug then
3959
3960 if p_msg_lvl = mth_util_pkg.G_DBG_PROC_FUN_END then
3961 mth_util_pkg.g_debug_indent_level := mth_util_pkg.g_debug_indent_level - 1;
3962 end if;
3963 if p_msg_lvl = 0 then
3964 l_indent_level := 0;
3965 else
3966 l_indent_level := 2 * mth_util_pkg.g_debug_indent_level;
3967 end if;
3968 if p_msg_lvl = mth_util_pkg.G_DBG_PROC_FUN_START then
3969 mth_util_pkg.g_debug_indent_level := mth_util_pkg.g_debug_indent_level + 1;
3970 end if;
3971
3972 if mth_util_pkg.g_execution_id = -1 then
3973 fnd_file.put_line(FND_FILE.LOG, LPad(' ',l_indent_level,' ') || p_msg_txt);
3974 else
3975 fname := utl_file.fopen(mth_util_pkg.g_debug_file_dir,'MOC-'||mth_util_pkg.g_execution_id,'a');
3976 utl_file.put(fname, LPad(' ',l_indent_level,' ') || p_msg_txt);
3977 -- dbms_output.put_line(LPad(' ',l_indent_level,' ') || p_msg_txt); --to be removed before checkin
3978 utl_file.fflush(fname);
3979 utl_file.fclose(fname);
3980 end if;
3981
3982 end if;
3983
3984 EXCEPTION
3985 WHEN OTHERS THEN
3986 return;
3987
3988 END Log_Msg;
3989
3990 /* ****************************************************************************
3991 * Procedure :INCR_EQUIP_SHIFTS*
3992 * Description :The procedure will insert records in equipment shifts table by taking the data*
3993 from workday shifts and mth_all_entities_v table *
3994 * File Name :MTHUTILB.PLS *
3995 * Visibility :Public
3996 * Modification log : *
3997 * Author Date Change: Amrit Kaur 27-Mar-2012 Initial Creation *
3998 *
3999 ******************************************************************************/
4000
4001 PROCEDURE INCR_EQUIP_SHIFTS IS
4002 v_log_from_date DATE;
4003 v_log_to_date DATE;
4004 v_unassigned_val VARCHAR2(30);
4005
4006 BEGIN
4007 mth_util_pkg.log_msg('INCR_EQUIPMENT_SHIFTS start', mth_util_pkg.G_DBG_PROC_FUN_START);
4008
4009 -- Initialize default parameters
4010 v_log_to_date := sysdate;
4011 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
4012
4013
4014 -- Call mth_run_log_pre_load
4015 mth_util_pkg.mth_run_log_pre_load('MTH_EQUIPMENT_SHIFTS_D',v_unassigned_val,'INCR',NULL,0,v_log_to_date);
4016
4017 -- Call GET_RUN_LOG_DATES
4018 mth_util_pkg.GET_RUN_LOG_DATES('MTH_EQUIPMENT_SHIFTS_D',NULL,NULL,NULL,v_log_from_date,v_log_to_date);
4019
4020 DELETE
4021 FROM MTH_EQUIPMENT_SHIFTS_D ESD
4022 WHERE EXISTS (SELECT 1
4023 FROM MTH_WORKDAY_SHIFTS_D WSD,MTH_ALL_ENTITIES_V MAV
4024 WHERE WSD.LAST_UPDATE_DATE > v_log_from_date
4025 AND (MAV.PRODUCTION_ENTITY ='Y'or MAV.PRODUCTION_ENTITY ='N')
4026 AND WSD.PLANT_FK_KEY = MAV.SITE_ID
4027 AND
4028 ESD.EQUIPMENT_FK_KEY = MAV.ENTITY_PK_KEY AND
4029 ESD.SHIFT_WORKDAY_FK_KEY = WSD.SHIFT_WORKDAY_PK_KEY ) ;
4030 mth_util_pkg.log_msg('Number of rows deleted from MTH_EQUIPMENT_SHIFTS_D - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4031
4032 INSERT INTO MTH_EQUIPMENT_SHIFTS_D (EQUIPMENT_FK_KEY,
4033 AVAILABILITY_DATE,
4034 SHIFT_WORKDAY_FK_KEY,
4035 FROM_DATE,
4036 TO_DATE,
4037 AVAILABILITY_FLAG,
4038 CREATION_DATE,
4039 LAST_UPDATE_DATE,
4040 CREATION_SYSTEM_ID,
4041 LAST_UPDATE_SYSTEM_ID,
4042 LINE_NUM,
4043 RESOURCE_FK_KEY,
4044 RESOURCE_COST,
4045 ENTITY_TYPE)
4046 (SELECT
4047 RES.EQUIPMENT_PK_KEY,
4048 RES.SHIFT_DATE,
4049 RES.SHIFT_WORKDAY_PK_KEY ,
4050 RES.FROM_DATE,
4051 RES.TO_DATE,
4052 'Y' ,
4053 v_log_to_date,
4054 v_log_to_date,
4055 v_unassigned_val,
4056 v_unassigned_val ,
4057 1 ,
4058 COST.LEVEL9_LEVEL_KEY,
4059 COST.COST,
4060 RES.ENTITY_TYPE
4061 FROM
4062 ( SELECT MAV.ENTITY_PK_KEY EQUIPMENT_PK_KEY,
4063 WSD.SHIFT_WORKDAY_PK_KEY,
4064 WSD.SHIFT_DATE,
4065 WSD.FROM_DATE,
4066 WSD.TO_DATE,
4067 MAV.ENTITY_TYPE
4068 FROM MTH_ALL_ENTITIES_V MAV,
4069 MTH_WORKDAY_SHIFTS_D WSD
4070 WHERE (WSD.LAST_UPDATE_DATE > v_log_from_date OR
4071 ( MAV.CREATION_DATE > v_log_from_date AND
4072 TRUNC( MAV.CREATION_DATE ) <= WSD.SHIFT_DATE))
4073 AND (MAV.PRODUCTION_ENTITY ='Y'or MAV.PRODUCTION_ENTITY ='N')
4074 AND WSD.PLANT_FK_KEY = MAV.SITE_ID
4075 ) RES ,( SELECT MED.EQUIPMENT_FK_KEY,
4076 MED.LEVEL9_LEVEL_KEY,
4077 MED.EQUIPMENT_EFFECTIVE_DATE,
4078 MED.EQUIPMENT_EXPIRATION_DATE,
4082 FROM
4079 MRC.RESOURCE_FK_KEY,
4080 MRC.COST
4081
4083 MTH_EQUIPMENT_DENORM_D MED ,MTH_RESOURCE_COST_MV MRC
4084 WHERE MED.EQUIPMENT_FK_KEY IS NOT NULL AND
4085 MED.EQUIPMENT_HIERARCHY_KEY = -2
4086 AND MED.LEVEL9_LEVEL_KEY = MRC.RESOURCE_FK_KEY (+))COST
4087 WHERE
4088
4089 RES.EQUIPMENT_PK_KEY = COST.EQUIPMENT_FK_KEY (+) And
4090 ( RES.FROM_DATE >= COST.EQUIPMENT_EFFECTIVE_DATE (+) AND
4091 RES.FROM_DATE <= NVL ( COST.EQUIPMENT_EXPIRATION_DATE (+) ,TO_DATE('4000-01-01', 'YYYY-MM-DD') )
4092 ) )
4093 ;
4094 mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIPMENT_SHIFTS_D - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4095
4096 ----Call mth_run_log_post_load
4097 mth_util_pkg.mth_run_log_post_load('MTH_EQUIPMENT_SHIFTS_D',v_unassigned_val);
4098
4099
4100 mth_util_pkg.log_msg('INCR_EQUIPMENT_SHIFTS end', mth_util_pkg.G_DBG_PROC_FUN_END);
4101 COMMIT;
4102 EXCEPTION
4103 WHEN OTHERS THEN
4104 mth_util_pkg.log_msg('Exception OTHERS in INCR_EQUIPMENT_SHIFTS', mth_util_pkg.G_DBG_EXCEPTION);
4105 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
4106 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
4107 ROLLBACK;
4108 RAISE;
4109
4110 END INCR_EQUIP_SHIFTS;
4111 /* ****************************************************************************
4112 * Procedure :MTH_WORKDAY_SHIFTS_SF*
4113 * Description :The procedure will insert valid records in workday shifts table and*
4114 invalid records in workday shifts error table *
4115 * File Name :MTHUTILB.PLS *
4116 * Visibility :Public
4117 * Modification log : *
4118 * Author Date Change: Amrit Kaur 27-Mar-2012 Initial Creation *
4119 *
4120 ******************************************************************************/
4121
4122 PROCEDURE MTH_WORKDAY_SHIFTS_SF IS
4123 v_log_date DATE;
4124 v_processing_flag NUMBER;
4125 v_unassigned_val VARCHAR2(30);
4126 v_profile VARCHAR2(240);
4127 v_reprocess_flag number;
4128 BEGIN
4129 -- Initialize default parameters
4130 v_log_date := sysdate;
4131 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
4132 -- mth_util_pkg.switch_column_default_value ( 'MTH_WORKDAY_SHIFTS_STG', v_processing_flag);
4133 -- SELECT Count(*) INTO v_reprocess_flag FROM MTH_WORKDAY_SHIFTS_ERR WHERE REPROCESS_READY_YN='Y';
4134
4135 -- IF ( v_processing_flag=1 OR v_processing_flag=2 OR v_reprocess_flag>0 ) THEN
4136
4137
4138 mth_util_pkg.log_msg('MTH_WORKDAY_SHIFTS_SF start', mth_util_pkg.G_DBG_PROC_FUN_START);
4139
4140
4141 SELECT MTH_UTIL_PKG.GET_PROFILE_VAL('MTH_SHIFT_SETUP' ) INTO v_profile FROM DUAL ;
4142
4143 INSERT INTO MTH_WORKDAY_SHIFTS_STG
4144 (SHIFT_DATE,
4145 SOURCE_ORG_CODE,
4146 USER_ATTR1,
4147 USER_ATTR2,
4148 USER_ATTR3,
4149 USER_ATTR4,
4150 USER_ATTR5,
4151 USER_MEASURE1,
4152 USER_MEASURE2,
4153 USER_MEASURE3,
4154 USER_MEASURE4,
4155 USER_MEASURE5,
4156 SHIFT_WORKDAY_PK,
4157 SYSTEM_FK,
4158 FROM_DATE,
4159 SHIFT_NUM,
4160 TO_DATE,
4161 SHIFT_NAME,
4162 SHIFT_TYPE,
4163 GRAVEYARD_SHIFT)
4164 (SELECT SHIFT_DATE,
4165 SOURCE_ORG_CODE,
4166 USER_ATTR1 ,
4167 USER_ATTR2,
4168 USER_ATTR3,
4169 USER_ATTR4,
4170 USER_ATTR5,
4171 USER_MEASURE1,
4172 USER_MEASURE2,
4173 USER_MEASURE3,
4174 USER_MEASURE4,
4175 USER_MEASURE5 ,
4176 SHIFT_WORKDAY_PK ,
4177 SYSTEM_FK,
4178 FROM_DATE,
4179 SHIFT_NUM,
4180 TO_DATE,
4181 SHIFT_NAME,
4182 SHIFT_TYPE,
4183 GRAVEYARD_SHIFT
4184 FROM MTH_WORKDAY_SHIFTS_ERR
4185 WHERE UPPER( REPROCESS_READY_YN ) = 'Y'
4186 )
4187 ;
4188 mth_util_pkg.log_msg('Number of rows inserted in MTH_WORKDAY_SHIFTS_STG from error table - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4189
4190 --delete data from the output error table
4191 DELETE FROM MTH_WORKDAY_SHIFTS_ERR
4192 WHERE UPPER(REPROCESS_READY_YN) IN ('D','Y'); --Bug 14753663
4193 mth_util_pkg.log_msg('Number of rows deleted in MTH_WORKDAY_SHIFTS_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4194 mth_util_pkg.REFRESH_ONE_MV ('MTH_SHIFT_REFERENCE_MV',null,null,'C');
4195 mth_util_pkg.switch_column_default_value('MTH_WORKDAY_SHIFTS_STG',v_processing_flag);
4196 IF ( v_processing_flag=1 OR v_processing_flag=2 ) THEN
4197
4198 --Execute all validations on csv records
4199
4200 -- Validation for Duplicate record
4201 UPDATE mth_workday_shifts_stg stg
4202 SET stg.err_code = stg.err_code || 'DUP '
4203 WHERE EXISTS ( SELECT * FROM ( SELECT shift_workday_pk,Count(shift_workday_pk) cnt
4204 FROM mth_workday_shifts_stg
4205 GROUP BY shift_workday_pk) dup
4206 WHERE dup.cnt>1
4207 AND dup.shift_workday_pk = stg.shift_workday_pk
4208 AND stg.processing_flag = v_processing_flag );
4209 mth_util_pkg.log_msg('Number of rows with DUP in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4210
4211 -- Validation for Null From Date
4212 UPDATE mth_workday_shifts_stg stg
4213 SET stg.err_code = stg.err_code || 'NFD '
4214 WHERE stg.from_date IS NULL
4215 AND stg.processing_flag = v_processing_flag;
4216 mth_util_pkg.log_msg('Number of rows with NFD in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4217
4218
4219 -- Validation for Null To Date
4223 AND stg.processing_flag = v_processing_flag;
4220 UPDATE mth_workday_shifts_stg stg
4221 SET stg.err_code = stg.err_code || 'NTD '
4222 WHERE stg.to_date IS NULL
4224 mth_util_pkg.log_msg('Number of rows with NTD in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4225
4226 -- Validation for From Date = To date
4227 UPDATE mth_workday_shifts_stg stg
4228 SET stg.err_code = stg.err_code || 'SDT '
4229 WHERE stg.from_date= stg.to_date
4230 AND stg.processing_flag = v_processing_flag;
4231 mth_util_pkg.log_msg('Number of rows with SDT in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4232
4233 -- Validation for From Date > To date
4234 UPDATE mth_workday_shifts_stg stg
4235 SET stg.err_code = stg.err_code || 'DTE '
4236 WHERE Nvl(stg.from_date,TO_DATE ('01-01-1900', 'DD-MM-YYYY')) >Nvl ( stg.To_Date, TO_DATE ('01-01-2999', 'DD-MM-YYYY') )
4237 AND stg.processing_flag = v_processing_flag;
4238 mth_util_pkg.log_msg('Number of rows with DTE in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4239
4240 -- Validation for shift_type
4241
4242 UPDATE mth_workday_shifts_stg stg
4243 SET stg.err_code = stg.err_code || 'SFT '
4244 WHERE stg.SHIFT_TYPE IS NOT NULL
4245 AND EXISTS (SELECT * FROM (SELECT stg.SHIFT_TYPE SHIFT_TYPE ,fl.meaning
4246 FROM mth_workday_shifts_stg stg,
4247 ( SELECT meaning FROM fnd_lookup_values fl WHERE fl.LOOKUP_TYPE ='MTH_SHIFT_TYPE'
4248
4249 AND fl.LANGUAGE = USERENV('LANG')
4250 AND fl.ENABLED_FLAG ='Y' ) fl
4251 WHERE stg.SHIFT_TYPE = fl.meaning(+)
4252 AND stg.SHIFT_TYPE IS NOT NULL
4253 ) fls
4254 WHERE fls.SHIFT_TYPE = stg.SHIFT_TYPE
4255 AND stg.processing_flag = v_processing_flag
4256 AND fls.meaning IS NULL);
4257 mth_util_pkg.log_msg('Number of rows with SFT in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4258
4259 -- Validation for graveyard shift
4260 UPDATE mth_workday_shifts_stg stg
4261 SET stg.err_code = stg.err_code || 'GRA '
4262 WHERE stg.graveyard_shift IS NOT NULL
4263 AND EXISTS (SELECT * FROM (SELECT fl.meaning, stg.graveyard_shift
4264 FROM mth_workday_shifts_stg stg,
4265 ( SELECT meaning FROM fnd_lookup_values fl WHERE fl.LOOKUP_TYPE ='MTH_GRAVEYARD_SHIFT_TYPE'
4266
4267 AND fl.LANGUAGE = USERENV('LANG')
4268 AND fl.ENABLED_FLAG ='Y' )fl
4269 WHERE stg.graveyard_shift = fl.meaning (+)
4270 AND stg.graveyard_shift IS NOT NULL
4271 )flg
4272 WHERE flg.graveyard_shift = stg.graveyard_shift
4273 AND stg.processing_flag = v_processing_flag
4274 AND flg.meaning IS NULL);
4275 mth_util_pkg.log_msg('Number of rows with GRA in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4276
4277 -- Validation for incorrect plant
4278 UPDATE mth_workday_shifts_stg stg
4279 SET stg.err_code = stg.err_code || 'PLT '
4280 WHERE EXISTS (SELECT * FROM ( SELECT PLANTS.PLANT_PK_KEY,STG.SHIFT_WORKDAY_PK FROM MTH_WORKDAY_SHIFTS_STG STG
4281
4282 , ( SELECT MOL.ORGANIZATION_CODE,MP.PLANT_PK_KEY,MS.SYSTEM_PK FROM MTH_PLANTS_D MP,
4283 MTH_ORGANIZATIONS_L MOL,
4284 MTH_SYSTEMS_SETUP MS
4285 WHERE NVL(MP.TO_DATE,SYSDATE) >= TRUNC(SYSDATE)
4286 AND MP.PLANT_PK_KEY = MOL.PLANT_FK_KEY
4287 AND MOL.SYSTEM_FK_KEY = MS.SYSTEM_PK_KEY )PLANTS
4288 WHERE STG.SOURCE_ORG_CODE = PLANTS.ORGANIZATION_CODE (+)
4289 AND NVL(STG.SYSTEM_FK, MTH_UTIL_PKG.MTH_UA_GET_VAL() ) = PLANTS.SYSTEM_PK (+)
4290 )plants1
4291 WHERE stg.shift_workday_pk = plants1.shift_workday_pk
4292 AND stg.processing_flag = v_processing_flag
4293 AND plants1.plant_pk_key IS NULL);
4294 mth_util_pkg.log_msg('Number of rows with PLT in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4295
4296 --Validation for OVP
4297 UPDATE mth_workday_shifts_stg stag
4298 SET stag.err_code = stag.err_code || 'OVP '
4299 WHERE EXISTS (SELECT *
4300 FROM
4301 (SELECT CASE
4302 WHEN (LAG(stg.from_date) OVER (PARTITION BY stg.source_org_code,stg.system_fk ORDER BY stg.from_date)) IS NOT NULL
4303 AND (LAG (stg.to_date) OVER (PARTITION BY stg.source_org_code,stg.system_fk ORDER BY stg.from_date)) IS NOT NULL
4304 AND ((stg.from_date >= (LAG(stg.from_date) OVER ( PARTITION BY stg.source_org_code,stg.system_fk ORDER BY stg.from_date))
4305 AND
4306 stg.from_date <= (LAG (stg.to_date) OVER (PARTITION BY stg.source_org_code,stg.system_fk ORDER BY stg.from_date)))
4307 OR
4308 (stg.to_date >= (LAG(stg.from_date) OVER (PARTITION BY stg.source_org_code,stg.system_fk ORDER BY stg.from_date))
4312 THEN 1 END overlap,
4309 AND
4310 stg.to_date <= (LAG(stg.to_date) OVER (PARTITION BY stg.source_org_code,stg.system_fk ORDER BY stg.from_date )))
4311 )
4313 stg.shift_workday_pk,
4314 stg.from_date,
4315 stg.to_date
4316 FROM mth_workday_shifts_stg stg ) ovp
4317 WHERE ovp.overlap = 1
4318 AND stag.shift_workday_pk = ovp.shift_workday_pk
4319 AND stag.from_date = ovp.from_date
4320 AND stag.To_Date = ovp.To_Date);
4321 mth_util_pkg.log_msg('Number of rows with OVP in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4322
4323 --Validation for SAE
4324 UPDATE mth_workday_shifts_stg stg
4325 SET stg.err_code = stg.err_code || 'SAE '
4326 WHERE EXISTS (SELECT *
4327 FROM
4328 (SELECT stg.shift_workday_pk
4329 FROM mth_workday_shifts_stg stg,
4330 MTH_SHIFT_REFERENCE_MV MSV,
4331 mth_plants_d plants,
4332 mth_organizations_l mol,
4333 mth_systems_setup ms
4334 WHERE NVL(MSV.SHIFT_DATE,stg.shift_date-1) >= stg.shift_date
4335 AND NVL(plants.TO_DATE,SYSDATE) >= TRUNC(SYSDATE)
4336 AND plants.PLANT_PK_KEY = MOL.PLANT_FK_KEY
4337 AND MOL.SYSTEM_FK_KEY = MS.SYSTEM_PK_KEY
4338 AND STG.SOURCE_ORG_CODE = MOL.ORGANIZATION_CODE (+)
4339 AND NVL(STG.SYSTEM_FK, MTH_UTIL_PKG.MTH_UA_GET_VAL() ) = MS.SYSTEM_PK (+)
4340 AND plants.PLANT_PK_KEY=MSV.PLANT_FK_KEY )sae
4341 WHERE stg.shift_workday_pk = sae.shift_workday_pk);
4342 mth_util_pkg.log_msg('Number of rows with SAE in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4343
4344 --Validation for profile value
4345 UPDATE mth_workday_shifts_stg stg
4346 SET stg.err_code = stg.err_code || 'PRF '
4347 WHERE v_profile<>'External Source';
4348 mth_util_pkg.log_msg('Number of rows with PRF in MTH_WORKDAY_SHIFTS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4349
4350
4351 --Insert records into mth_workday_shifts_err
4352
4353 INSERT INTO MTH_WORKDAY_SHIFTS_ERR(REPROCESS_READY_YN,
4354 SHIFT_DATE,
4355 SOURCE_ORG_CODE,
4356 USER_ATTR1,
4357 USER_ATTR2,
4358 USER_ATTR3,
4359 USER_ATTR4,
4360 USER_ATTR5,
4361 USER_MEASURE1,
4362 USER_MEASURE2,
4363 USER_MEASURE3,
4364 USER_MEASURE4,
4365 USER_MEASURE5,
4366 SHIFT_WORKDAY_PK,
4367 SYSTEM_FK,
4368 FROM_DATE,
4369 TO_DATE,
4370 SHIFT_NUM,
4371 SHIFT_NAME,
4372 ERR_CODE,
4373 SHIFT_TYPE,
4374 GRAVEYARD_SHIFT)
4375 (SELECT 'N',
4376 SHIFT_DATE,
4377 SOURCE_ORG_CODE,
4378 USER_ATTR1,
4379 USER_ATTR2,
4380 USER_ATTR3,
4381 USER_ATTR4,
4382 USER_ATTR5,
4383 USER_MEASURE1,
4384 USER_MEASURE2,
4385 USER_MEASURE3,
4386 USER_MEASURE4,
4387 USER_MEASURE5,
4388 SHIFT_WORKDAY_PK,
4389 SYSTEM_FK,
4390 FROM_DATE,
4391 TO_DATE,
4392 SHIFT_NUM,
4393 SHIFT_NAME,
4394 ERR_CODE,
4395 SHIFT_TYPE,
4396 GRAVEYARD_SHIFT
4397 FROM mth_workday_shifts_stg
4398 WHERE err_code IS NOT NULL
4399 );
4400 mth_util_pkg.log_msg('Number of rows inserted in MTH_WORKDAY_SHIFTS_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4401
4402
4403
4404 DELETE FROM mth_equipment_shifts_D
4405 WHERE shift_workday_fk_key IN (SELECT shift_workday_pk_key FROM
4406 mth_workday_shifts_D wsd,
4407 mth_shift_reference_MV mv,
4408 mth_workday_shifts_stg stg,
4409 mth_plants_d plants ,
4410 MTH_ORGANIZATIONS_L MOL,
4414 TRUNC( wsd.FROM_DATE ) = TRUNC ( stg.SHIFT_DATE ) )
4411 MTH_SYSTEMS_SETUP MS
4412 WHERE wsd.SHIFT_DATE > Nvl( mv.SHIFT_DATE ,wsd.shift_date-1)
4413 And (TRUNC( wsd.SHIFT_DATE ) = TRUNC ( stg.SHIFT_DATE ) OR
4415 And wsd.PLANT_FK_KEY = mv.PLANT_FK_KEY(+)
4416 And wsd.PLANT_FK_KEY = plants.PLANT_PK_KEY
4417 AND NVL(plants.TO_DATE,SYSDATE) >= TRUNC(SYSDATE)
4418 AND plants.PLANT_PK_KEY = MOL.PLANT_FK_KEY
4419 AND MOL.SYSTEM_FK_KEY = MS.SYSTEM_PK_KEY
4420 AND STG.SOURCE_ORG_CODE = MOL.ORGANIZATION_CODE
4421 AND NVL(STG.SYSTEM_FK, MTH_UTIL_PKG.MTH_UA_GET_VAL() ) = MS.SYSTEM_PK
4422 AND stg.err_code IS NULL
4423 And stg.SOURCE_ORG_CODE = nvl ( wsd.SOURCE_ORG_CODE , stg.SOURCE_ORG_CODE ));
4424
4425 mth_util_pkg.log_msg('Number of rows deleted from mth_equipment_shifts_D - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4426
4427 DELETE FROM mth_workday_shifts_D
4428 WHERE shift_workday_pk_key IN (SELECT shift_workday_pk_key FROM
4429 mth_workday_shifts_D wsd,
4430 mth_shift_reference_MV mv,
4431 mth_workday_shifts_stg stg,
4432 mth_plants_d plants ,
4433 MTH_ORGANIZATIONS_L MOL,
4434 MTH_SYSTEMS_SETUP MS
4435 WHERE wsd.SHIFT_DATE > Nvl( mv.SHIFT_DATE,wsd.shift_date-1)
4436 And (TRUNC( wsd.SHIFT_DATE ) = TRUNC ( stg.SHIFT_DATE ) OR
4437 TRUNC( wsd.FROM_DATE ) = TRUNC ( stg.SHIFT_DATE ) )
4438 And wsd.PLANT_FK_KEY = mv.PLANT_FK_KEY(+)
4439 And wsd.PLANT_FK_KEY = plants.PLANT_PK_KEY
4440 AND NVL(plants.TO_DATE,SYSDATE) >= TRUNC(SYSDATE)
4441 AND plants.PLANT_PK_KEY = MOL.PLANT_FK_KEY
4442 AND MOL.SYSTEM_FK_KEY = MS.SYSTEM_PK_KEY
4443 AND STG.SOURCE_ORG_CODE = MOL.ORGANIZATION_CODE
4444 AND NVL(STG.SYSTEM_FK, MTH_UTIL_PKG.MTH_UA_GET_VAL() ) = MS.SYSTEM_PK
4445 AND stg.err_code IS NULL
4446 And stg.SOURCE_ORG_CODE = nvl ( wsd.SOURCE_ORG_CODE , stg.SOURCE_ORG_CODE ));
4447
4448 mth_util_pkg.log_msg('Number of rows deleted from mth_workday_shifts_D - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4449
4450 INSERT INTO MTH_WORKDAY_SHIFTS_D
4451 (SHIFT_WORKDAY_PK_KEY,
4452 SHIFT_WORKDAY_PK,
4453 SHIFT_DATE,
4454 SHIFT_DATE_JULIAN,
4455 PLANT_FK_KEY,
4456 SYSTEM_FK_KEY,
4457 USER_ATTR1,
4458 USER_ATTR2,
4459 USER_ATTR3,
4460 USER_ATTR4,
4461 USER_ATTR5,
4462 USER_MEASURE1,
4463 USER_MEASURE2,
4464 USER_MEASURE3,
4465 USER_MEASURE4,
4466 USER_MEASURE5,
4467 CREATION_DATE,
4468 LAST_UPDATE_DATE,
4469 CREATION_SYSTEM_ID,
4470 LAST_UPDATE_SYSTEM_ID,
4471 FROM_DATE,
4472 TO_DATE,
4473 SHIFT_NUM,
4474 SHIFT_NAME,
4475 SOURCE_ORG_CODE,
4476 SHIFT_TYPE,
4477 GRAVEYARD_SHIFT)
4478
4479 (SELECT MTH_WORKDAYS_SHIFTS_S.NEXTVAL,
4480 stg.SHIFT_WORKDAY_PK,
4481 stg.SHIFT_DATE,
4482 TO_NUMBER(TO_CHAR( stg.SHIFT_DATE ,'J')),
4483 PD.plant_pk_key,
4484 NVL( SP.SYSTEM_PK_KEY , MTH_UTIL_PKG.MTH_UA_GET_VAL() ),
4485 STG.USER_ATTR1,
4486 stg.USER_ATTR2,
4487 stg.USER_ATTR3,
4488 stg.USER_ATTR4,
4489 stg.USER_ATTR5,
4490 stg.USER_MEASURE1,
4491 stg.USER_MEASURE2,
4492 stg.USER_MEASURE3,
4493 stg.USER_MEASURE4,
4494 stg.USER_MEASURE5,
4495 SYSDATE,
4496 SYSDATE,
4497 NVL( SP.SYSTEM_PK_KEY , MTH_UTIL_PKG.MTH_UA_GET_VAL() ),
4498 NVL( SP.SYSTEM_PK_KEY , MTH_UTIL_PKG.MTH_UA_GET_VAL() ),
4499 stg.FROM_DATE,
4500 stg.TO_DATE,
4501 STG.SHIFT_NUM,
4502 STG.SHIFT_NAME,
4503 STG.SOURCE_ORG_CODE,
4504 Decode(STG.SHIFT_TYPE,NULL,'PROD-SHIFT',FL2.LOOKUP_CODE),
4505 DECODE(STG.GRAVEYARD_SHIFT,NULL,NULL,FL1.LOOKUP_CODE)
4506 FROM
4507 MTH_WORKDAY_SHIFTS_STG STG ,MTH_SYSTEMS_SETUP SP,MTH_PLANTS_D PD,MTH_ORGANIZATIONS_L MOL,FND_LOOKUP_VALUES FL1,FND_LOOKUP_VALUES FL2
4508 WHERE NVL ( STG.SYSTEM_FK , MTH_UTIL_PKG.MTH_UA_GET_VAL ) = SP.SYSTEM_PK
4509 AND NVL(PD.TO_DATE,SYSDATE) >= TRUNC(SYSDATE)
4510 AND PD.PLANT_PK_KEY = MOL.PLANT_FK_KEY
4511 AND MOL.SYSTEM_FK_KEY = SP.SYSTEM_PK_KEY
4512 AND STG.SOURCE_ORG_CODE = MOL.ORGANIZATION_CODE
4513 AND NVL(STG.SYSTEM_FK, MTH_UTIL_PKG.MTH_UA_GET_VAL() ) = SP.SYSTEM_PK (+)
4514 AND FL1.LOOKUP_TYPE ='MTH_GRAVEYARD_SHIFT_TYPE'
4515 AND FL1.LANGUAGE = USERENV('LANG')
4516 AND FL1.ENABLED_FLAG ='Y'
4517 AND FL1.MEANING =NVL(STG.GRAVEYARD_SHIFT,'Shift End Date')
4518 AND FL2.LOOKUP_TYPE ='MTH_SHIFT_TYPE'
4519 AND FL2.LANGUAGE = USERENV('LANG')
4520 AND FL2.ENABLED_FLAG ='Y'
4521 AND NVL( STG.SHIFT_TYPE ,'Production Shift') = FL2.MEANING
4522 AND STG.processing_flag = v_processing_flag
4523 AND STG.ERR_CODE IS NULL );
4524
4525 mth_util_pkg.log_msg('Number of rows inserted in mth_workday_shifts_D - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4526
4527
4528 INSERT INTO MTH_WORKDAY_SHIFTS_D
4529 (SHIFT_WORKDAY_PK_KEY,
4530 SHIFT_WORKDAY_PK,
4531 SHIFT_DATE,
4532 SHIFT_DATE_JULIAN,
4533 PLANT_FK_KEY,
4534 SYSTEM_FK_KEY,
4535 USER_ATTR1,
4536 USER_ATTR2,
4537 USER_ATTR3,
4538 USER_ATTR4,
4539 USER_ATTR5,
4540 USER_MEASURE1,
4541 USER_MEASURE2,
4542 USER_MEASURE3,
4543 USER_MEASURE4,
4544 USER_MEASURE5,
4545 CREATION_DATE,
4549 FROM_DATE,
4546 LAST_UPDATE_DATE,
4547 CREATION_SYSTEM_ID,
4548 LAST_UPDATE_SYSTEM_ID,
4550 TO_DATE,
4551 SHIFT_NUM,
4552 SHIFT_NAME,
4553 SOURCE_ORG_CODE,
4554 SHIFT_TYPE,
4555 GRAVEYARD_SHIFT)
4556
4557 (SELECT MTH_WORKDAYS_SHIFTS_S.NEXTVAL,
4558 TO_CHAR( CATCH_ALL.DAY,'yyyymmdd-hh24:mi:ss') ||'-'|| PD.PLANT_PK || MTH_UTIL_PKG.MTH_UA_GET_VAL(),
4559 CATCH_ALL.DAY,
4560 TO_NUMBER(TO_CHAR( CATCH_ALL.DAY ,'J')),
4561 PD.plant_pk_key,
4562 NVL( SP.SYSTEM_PK_KEY , MTH_UTIL_PKG.MTH_UA_GET_VAL() ),
4563 NULL,
4564 NULL,
4565 NULL,
4566 NULL,
4567 NULL,
4568 NULL,
4569 NULL,
4570 NULL,
4571 NULL,
4572 NULL,
4573 SYSDATE,
4574 SYSDATE,
4575 NVL( SP.SYSTEM_PK_KEY , MTH_UTIL_PKG.MTH_UA_GET_VAL() ),
4576 NVL( SP.SYSTEM_PK_KEY , MTH_UTIL_PKG.MTH_UA_GET_VAL() ),
4577 NULL,
4578 NULL,
4579 NULL,
4580 MTH_UTIL_PKG.GET_PROFILE_VAL('MTH_CATCH_ALL_NAME'),
4581 STG.SOURCE_ORG_CODE,
4582 'BOTH' ,
4583 NULL
4584 FROM
4585 ( SELECT DISTINCT SHIFT_DATE,SOURCE_ORG_CODE,system_fk from
4586 MTH_WORKDAY_SHIFTS_STG STG where STG.processing_flag = v_processing_flag
4587 AND STG.ERR_CODE IS NULL
4588 ) STG ,MTH_SYSTEMS_SETUP SP,MTH_PLANTS_D PD,MTH_ORGANIZATIONS_L MOL,(SELECT DISTINCT DAY FROM(SELECT DAY FROM MTH_445_PERIOD_CALENDAR
4589 UNION
4590 SELECT DAY FROM MTH_GREGORIAN_CALENDAR
4591 UNION
4592 SELECT REPORT_DATE DAY FROM MTH_DAY_D )) CATCH_ALL
4593 WHERE NVL ( STG.SYSTEM_FK , MTH_UTIL_PKG.MTH_UA_GET_VAL ) = SP.SYSTEM_PK
4594 AND NVL(PD.TO_DATE,SYSDATE) >= TRUNC(SYSDATE)
4595 AND PD.PLANT_PK_KEY = MOL.PLANT_FK_KEY
4596 AND MOL.SYSTEM_FK_KEY = SP.SYSTEM_PK_KEY
4597 AND STG.SOURCE_ORG_CODE = MOL.ORGANIZATION_CODE
4598 AND CATCH_ALL.DAY = stg.SHIFT_DATE
4599 );
4600
4601 mth_util_pkg.log_msg('Number of catch all rows inserted in mth_workday_shifts_D - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4602
4603 mth_util_pkg.truncate_table_partition(' MTH_WORKDAY_SHIFTS_STG',v_processing_flag);
4604
4605 mth_util_pkg.log_msg('MTH_WORKDAY_SHIFTS_SF end', mth_util_pkg.G_DBG_PROC_FUN_END);
4606 mth_util_pkg.refresh_mv('MTH_SHIFT_GREGORIAN_DENORM_MV','f',null,null,null,null,null,null,null);
4607 COMMIT;
4608 ELSE
4609 mth_util_pkg.log_msg('There are no records in MTH_WORKDAY_SHIFTS_STG for processing');
4610 END IF ;
4611
4612
4613 EXCEPTION
4614 WHEN OTHERS THEN
4615 --Call logging API and then throw exception
4616 mth_util_pkg.log_msg('Exception OTHERS in MTH_WORKDAY_SHIFTS_SF', mth_util_pkg.G_DBG_EXCEPTION);
4617 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
4618 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
4619 ROLLBACK;
4620 RAISE;
4621
4622
4623 END MTH_WORKDAY_SHIFTS_SF;
4624
4625 /* ****************************************************************************
4626 * Procedure :MTH_RESET_SEQUENCE *
4627 * Description :This procedure is used to reset the sequence in the *
4628 * specified schema. *
4629 * File Name :MTHUTILS.PLS *
4630 * Visibility :Public *
4631 * Parameters :Table name , schema name *
4632 * Modification log : *
4633 * Author Date Change *
4634 * Akanksha verma April-17-2012 Initial Creation *
4635 **************************************************************************** */
4636
4637 PROCEDURE mth_Reset_sequence(p_seq_name IN VARCHAR2,v_schema_name IN VARCHAR2) IS
4638
4639 --initialize variables here
4640 v_stmt VARCHAR2(2000);
4641
4642 TYPE cur_typ IS REF CURSOR;
4643
4644 c cur_typ;
4645 v_seq_val NUMBER;
4646 -- main body
4647 BEGIN
4648
4649 --Prepare the truncate statement using schema name and table name
4650 v_stmt := 'SELECT ' || p_seq_name || '.nextval FROM dual';
4651 OPEN c FOR v_stmt;
4652 FETCH c INTO v_seq_val;
4653 CLOSE c;
4654 IF(v_seq_val > 1)
4655 THEN
4656 v_stmt := 'ALTER SEQUENCE ' || V_SCHEMA_NAME || '.' || p_seq_name || ' MAXVALUE ' || v_seq_val || ' CYCLE NOCACHE';
4657
4658 EXECUTE IMMEDIATE v_stmt;
4659
4660 v_stmt := 'SELECT ' || p_seq_name || '.nextval FROM dual';
4661 OPEN c FOR v_stmt;
4662 FETCH c INTO v_seq_val;
4663 CLOSE c;
4664 v_stmt := 'ALTER SEQUENCE ' || V_SCHEMA_NAME || '.' || p_seq_name || ' MAXVALUE 2147483647 NOCYCLE CACHE 20';
4665
4666 EXECUTE IMMEDIATE v_stmt;
4667 END IF;
4668
4669
4670 END mth_Reset_sequence;
4671
4672 /* ****************************************************************************
4673 * Procedure : *
4674 * Description :This procedure is used to truncate table,materialized view
4675 * and reset sequence in the specified schema based on input parameter as MTH,
4676 * SSDM or ALL. *
4677 * File Name :MTHUTILS.PLS *
4678 * Visibility :Public *
4679 * Parameters :Table name , schema name *
4680 * Modification log : *
4681 * Author Date Change *
4682 * Akanksha verma April-17-2012 Initial Creation *
4683 **************************************************************************** */
4684
4685 PROCEDURE CLEAN_UP_TABLE_DATA(p_err_buff OUT NOCOPY VARCHAR2,
4686 p_retcode OUT NOCOPY NUMBER,
4687 P_PROD_TO_BE_CLEANUP IN VARCHAR2)
4688 IS
4689
4690 CURSOR c_tabs (p_lookup_type IN varchar2) IS
4691 SELECT LOOKUP_CODE
4692 FROM FND_LOOKUP_Values
4693 WHERE LOOKUP_TYPE=p_lookup_type
4694 AND DESCRIPTION = 'TABLE'
4695 AND LANGUAGE = userenv('LANG');
4696
4697 CURSOR c_seqs (p_lookup_type IN varchar2) IS
4698 SELECT LOOKUP_CODE
4699 FROM FND_LOOKUP_Values
4700 WHERE LOOKUP_TYPE=p_lookup_type
4701 AND DESCRIPTION = 'SEQUENCE'
4702 AND LANGUAGE = userenv('LANG');
4703
4704 CURSOR c_mvs (p_lookup_type IN varchar2) IS
4705 SELECT LOOKUP_CODE
4706 FROM FND_LOOKUP_Values
4707 WHERE LOOKUP_TYPE=p_lookup_type
4708 AND DESCRIPTION = 'MVIEW'
4709 AND LANGUAGE = userenv('LANG');
4710
4711 v_lookup_type VARCHAR2(100);
4712 v_schema_name VARCHAR2(100);
4713 v_status VARCHAR2(30) ;
4714 v_industry VARCHAR2(30) ;
4715
4716
4717 BEGIN
4718 mth_util_pkg.log_msg('MAIN start', mth_util_pkg.G_DBG_PROC_FUN_START);
4719
4720 If P_PROD_TO_BE_CLEANUP = 'ALL' THEN
4721 v_lookup_type := 'MTH_ALL_CLEANUP_LIST';
4722 elsIf P_PROD_TO_BE_CLEANUP = 'MOC' THEN
4723 v_lookup_type := 'MTH_MOC_CLEANUP_LIST';
4724 elsIf P_PROD_TO_BE_CLEANUP = 'SSDM' THEN
4725 v_lookup_type := 'MTH_SSDM_CLEANUP_LIST';
4726 End If;
4727
4728 IF (FND_INSTALLATION.GET_APP_INFO(
4729 application_short_name => 'MTH'
4730 , status => v_status
4731 , industry => v_industry
4732 , oracle_schema => v_schema_name))
4733 THEN
4734
4735 FOR r_tab IN c_tabs(v_lookup_type) LOOP
4736
4737 mth_util_pkg.mth_truncate_table(r_tab.LOOKUP_CODE,v_schema_name);
4738
4739 END LOOP;
4740
4741 FOR r_seq IN c_seqs(v_lookup_type) LOOP
4742
4743 mth_util_pkg.mth_Reset_sequence(r_seq.LOOKUP_CODE,v_schema_name);
4744
4745 END LOOP;
4746
4747 FOR r_mv IN c_mvs(v_lookup_type) LOOP
4748
4749 Dbms_Mview.REFRESH(r_mv.LOOKUP_CODE, 'C', '', FALSE, FALSE, 0, 0, 0, TRUE);
4750
4751 END LOOP;
4752
4753 DELETE FROM MTH_RUN_LOG rl
4754 WHERE exists (
4755 SELECT 1
4756 FROM FND_LOOKUP_VALUES lv
4757 WHERE lv.LOOKUP_TYPE=v_lookup_type
4758 AND lv.DESCRIPTION = 'TABLE'
4759 AND lv.LANGUAGE = userenv('LANG')
4760 AND lv.LOOKUP_CODE like rl.FACT_TABLE || '%');
4761 commit;
4762
4763 p_retcode := 0;
4764 mth_util_pkg.log_msg('MAIN end', mth_util_pkg.G_DBG_PROC_FUN_END);
4765
4766 END IF;
4767
4768 EXCEPTION
4769 WHEN OTHERS THEN
4770 mth_util_pkg.log_msg('Exception in CLEAN_UP_TABLE_DATA.', mth_util_pkg.G_DBG_EXCEPTION);
4771 mth_util_pkg.log_msg('Unknown Exception occured while processing');
4772 mth_util_pkg.log_msg(-20001, mth_util_pkg.G_DBG_EXCEPTION);
4773 p_retcode := 2;
4774 p_err_buff := mth_util_pkg.G_DBG_EXCEPTION;
4775 RAISE;
4776
4777 END CLEAN_UP_TABLE_DATA;
4778
4779
4780
4781
4782
4783
4784 /*****************************************************************************
4785 * Procedure : Get_Processing_Flag *
4786 * Description : Returns the value of variable G_PROCESSING_FLAG. *
4787 * Used in ODI IKM for SF interfaces *
4788 * File Name : MTHUTILB.pls *
4789 * Visibility : Public *
4790 * Parameters : *
4791 *****************************************************************************/
4792 FUNCTION Get_Processing_Flag(p_table_name IN VARCHAR2 DEFAULT 'default') RETURN NUMBER IS
4793 v_flag VARCHAR2(100);
4794 v_schema_name VARCHAR2(100);
4795 v_status VARCHAR2(30) ;
4796 v_industry VARCHAR2(30) ;
4797 BEGIN
4798 IF p_table_name = 'default' THEN
4799 return mth_util_pkg.g_processing_flag;
4800 ELSE
4801 IF (FND_INSTALLATION.GET_APP_INFO(
4802 application_short_name => 'MTH'
4803 , status => v_status
4804 , industry => v_industry
4805 , oracle_schema => v_schema_name))
4806 THEN
4807
4808 SELECT c.DATA_DEFAULT
4809 into v_flag
4810 FROM user_synonyms s
4811 LEFT OUTER JOIN
4812 (all_editioning_views ev
4813 INNER JOIN all_editioning_view_cols evc
4814 ON evc.owner = ev.owner
4815 AND evc.view_name = ev.view_name)
4816 ON ev.view_name = s.table_name
4817 AND ev.owner = s.table_owner
4818 INNER JOIN all_tab_columns c
4819 ON Nvl(ev.OWNER,s.table_owner) = c.owner
4820 AND Nvl(ev.table_name,s.table_name) = c.table_name
4821 AND Nvl(evc.TABLE_COLUMN_NAME,c.COLUMN_NAME) = c.COLUMN_NAME
4822 WHERE s.synonym_name = p_table_name
4823 AND Nvl(evc.TABLE_COLUMN_NAME,c.COLUMN_NAME) = 'PROCESSING_FLAG';
4824 /*
4825 FROM all_tab_columns c
4826 WHERE column_name = 'PROCESSING_FLAG'
4827 AND TABLE_NAME = p_table_name
4828 AND owner = v_schema_name;
4829 */
4830 IF v_flag = '1' THEN
4831 return 2;
4832 ELSE
4833 return 1;
4834 END IF;
4835 END IF;
4836 END IF;
4837 END Get_Processing_Flag;
4838
4839
4840 END mth_util_pkg;
[Home] [Help]