[Home] [Help]
PACKAGE BODY: APPS.MTH_PROD_DATA_PROCESS_PKG
Source
1 PACKAGE BODY MTH_PROD_DATA_PROCESS_PKG AS
2 /*$Header: mthpdpb.pls 120.2.12020000.3 2012/08/09 09:25:30 mgijare ship $ */
3
4 /* ****************************************************************************
5 * Procedure :process_epp *
6 * Description :... *
7 * File Name :MTHPDPB.PLS *
8 * Visibility :Public *
9 * Parameters : *
10 ******************************************************************************/
11
12 PROCEDURE process_epp( p_err_buff OUT NOCOPY VARCHAR2,
13 p_retcode OUT NOCOPY NUMBER,
14 p_mode IN VARCHAR2 --INIT, INCR
15 )
16 IS
17 l_execution_id NUMBER;
18 v_msg VARCHAR2(300);
19 BEGIN
20 mth_util_pkg.initialize_debug('Concurrent Program "Process EPP"', l_execution_id);
21 mth_util_pkg.log_msg('process_epp start', mth_util_pkg.G_DBG_PROC_FUN_START);
22 mth_util_pkg.log_msg('p_mode = ' || p_mode , mth_util_pkg.G_DBG_MAIN_PARAM);
23
24 IF(p_mode = 'INIT')
25 THEN
26 DELETE FROM MTH_EQUIP_PROD_PERFORMANCE_F;
27 DELETE FROM MTH_EQUIP_PROD_PERF_DETAIL_F;
28 DELETE FROM MTH_EQUIP_PROD_SUSTAIN_F;
29 populate_EPP_from_output(p_mode);
30 update_EPP_from_status(p_mode);
31 populate_EPP_for_NonProd_Equip(p_mode);
32 populate_prod_perf_detail(p_mode);
33 populate_prod_sust_detail(p_mode);
34 ELSE
35 populate_EPP_from_output(p_mode);
36 update_EPP_from_status(p_mode);
37 populate_EPP_for_NonProd_Equip(p_mode);
38 populate_prod_perf_detail(p_mode);
39 populate_prod_sust_detail(p_mode);
40 END IF;
41
42 COMMIT;
43
44 EXCEPTION
45 WHEN OTHERS THEN
46 --Call logging API and then throw exception
47 mth_util_pkg.log_msg('Exception OTHERS in process_epp', mth_util_pkg.G_DBG_EXCEPTION);
48 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
49 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
50
51 END process_epp;
52
53
54 /* ****************************************************************************
55 * Procedure :insert_into_EPP_per_output_rec *
56 * Description : Break this EPP entry by shifts and hours and insert them *
57 * into EPP table. *
58 * equipment *
59 * File Name :MTHPDPB.PLS *
60 * Visibility :Private *
61 * Parameters :p_actual_from_date - Actual from date *
62 * Return Value :None *
63 **************************************************************************** */
64 PROCEDURE insert_into_EPP_per_output_rec (p_actual_from_date DATE,
65 p_actual_to_date DATE,
66 p_equipment_fk_key NUMBER,
67 p_shift_workday_fk_key NUMBER,
68 p_workorder_fk_key NUMBER,
69 p_segment_fk_key NUMBER,
70 p_item_fk_key NUMBER,
71 p_system_fk_key NUMBER,
72 p_user_dim1_fk_key NUMBER,
73 p_user_dim2_fk_key NUMBER,
74 p_user_dim3_fk_key NUMBER,
75 p_user_dim4_fk_key NUMBER,
76 p_user_dim5_fk_key NUMBER,
77 p_user_attr1 VARCHAR2,
78 p_user_attr2 VARCHAR2,
79 p_user_attr3 VARCHAR2,
80 p_user_attr4 VARCHAR2,
81 p_user_attr5 VARCHAR2,
82 p_user_measure1 NUMBER,
83 p_user_measure2 NUMBER,
84 p_user_measure3 NUMBER,
85 p_user_measure4 NUMBER,
86 p_user_measure5 NUMBER,
87 p_sysdate DATE,
88 p_creation_system_id NUMBER,
89 p_last_update_system_id NUMBER,
90 p_created_by NUMBER,
91 p_last_update_login NUMBER,
92 p_last_updated_by NUMBER,
93 p_qty_completed NUMBER,
94 p_qty_scrap NUMBER,
95 p_qty_output NUMBER,
96 p_qty_rejected NUMBER,
97 p_qty_rework NUMBER,
98 p_qty_good NUMBER,
99 p_qty_uom VARCHAR2)
100
101 IS
102 -- This is used to calculate the beginning time of the catch all shift.
103 -- It should be the actual_to_date of the last reading.
104 v_b_time_4_cs DATE;
105 BEGIN
106
107 mth_util_pkg.log_msg('insert_into_EPP_per_output_rec start', mth_util_pkg.G_DBG_PROC_FUN_START);
108 -- Set it to be the last reading time. In case this is the first
109 -- reading, ideally v_b_time_4_cs should be the same as the
110 -- p_actual_from_date in that case. But moving one second
111 -- backward should return the same result in that case.
112 v_b_time_4_cs := p_actual_from_date - 1/86400;
113
114
115 -- Query for insert EPP for both regular shift and catch-all shifts
116 INSERT INTO MTH_EQUIP_PROD_PERFORMANCE_F
117 (equipment_fk_key, shift_workday_fK_key, hour_fk_key,
118 workorder_fk_key, segment_fk_key, item_fk_key,
119 actual_from_date, actual_to_date, system_fk_key,
120 user_dim1_fk_key, user_dim2_fk_key, user_dim3_fk_key,
121 user_dim4_fk_key, user_dim5_fk_key,
122 user_attr1, user_attr2, user_attr3, user_attr4, user_attr5,
123 user_measure1, user_measure2, user_measure3,
124 user_measure4, user_measure5,
125 creation_date, last_update_date,
126 creation_system_id, last_update_system_id,
127 created_by, last_update_login, last_updated_by,
128 equip_prod_perf_pk_key, run_hours, down_hours,
129 idle_hours, off_hours,
130 qty_completed,
131 qty_scrap,
132 qty_output,
133 qty_rejected,
134 qty_rework,
135 qty_good,
136 qty_uom)
137 SELECT p_equipment_fk_key equipment_fk_key,
138 shift_workday_fK_key,
139 hour_fk_key,
140 CASE WHEN entry_type = 3
141 THEN p_workorder_fk_key
142 ELSE -99999 END workorder_fk_key,
143 CASE WHEN entry_type = 3
144 THEN p_segment_fk_key
145 ELSE -99999 END segment_fk_key,
146 CASE WHEN entry_type = 3
147 THEN p_item_fk_key
148 ELSE -99999 END item_fk_key,
149 actual_from_date,
150 actual_to_date,
151 p_system_fk_key system_fk_key,
152 CASE WHEN entry_type = 3
153 THEN p_user_dim1_fk_key
154 ELSE NULL END user_dim1_fk_key,
155 CASE WHEN entry_type = 3
156 THEN p_user_dim2_fk_key
157 ELSE NULL END user_dim2_fk_key,
158 CASE WHEN entry_type = 3
159 THEN p_user_dim3_fk_key
160 ELSE NULL END user_dim3_fk_key,
161 CASE WHEN entry_type = 3
162 THEN p_user_dim4_fk_key
163 ELSE NULL END user_dim4_fk_key,
164 CASE WHEN entry_type = 3
165 THEN p_user_dim5_fk_key
166 ELSE NULL END user_dim5_fk_key,
167 CASE WHEN entry_type = 3
168 THEN p_user_attr1
169 ELSE NULL END user_attr1,
170 CASE WHEN entry_type = 3
171 THEN p_user_attr2
172 ELSE NULL END user_attr2,
173 CASE WHEN entry_type = 3
174 THEN p_user_attr3
175 ELSE NULL END user_attr3,
176 CASE WHEN entry_type = 3
177 THEN p_user_attr4
178 ELSE NULL END user_attr4,
179 CASE WHEN entry_type = 3
180 THEN p_user_attr5
181 ELSE NULL END user_attr5,
182 CASE WHEN entry_type = 3
183 THEN p_user_measure1
184 ELSE NULL END user_measure1,
185 CASE WHEN entry_type = 3
186 THEN p_user_measure2
187 ELSE NULL END user_measure2,
188 CASE WHEN entry_type = 3
189 THEN p_user_measure3
190 ELSE NULL END user_measure3,
191 CASE WHEN entry_type = 3
192 THEN p_user_measure4
193 ELSE NULL END user_measure4,
194 CASE WHEN entry_type = 3
195 THEN p_user_measure5
196 ELSE NULL END user_measure5,
197 p_sysdate creation_date,
198 p_sysdate last_update_date,
199 p_creation_system_id creation_system_id,
200 p_last_update_system_id last_update_system_id,
201 p_created_by created_by,
202 p_last_update_login last_update_login,
203 p_last_updated_by last_updated_by,
204 MTH_EQUIP_PROD_PERF_S.NEXTVAL equip_prod_perf_pk_key,
205 0 run_hours,
206 0 down_hours,
207 0 idle_hours,
208 0 off_hours,
209 CASE WHEN entry_type = 3
210 THEN allocation_pct * p_qty_completed
211 ELSE 0 END qty_completed,
212 CASE WHEN entry_type = 3
213 THEN allocation_pct * p_qty_scrap
214 ELSE 0 END qty_scrap,
215 CASE WHEN entry_type = 3
216 THEN allocation_pct * p_qty_output
217 ELSE 0 END qty_output,
218 CASE WHEN entry_type = 3
219 THEN allocation_pct * p_qty_rejected
220 ELSE 0 END qty_rejected,
221 CASE WHEN entry_type = 3
222 THEN allocation_pct * p_qty_rework
223 ELSE 0 END qty_rework,
224 CASE WHEN entry_type = 3
225 THEN allocation_pct * p_qty_good
226 ELSE 0 END qty_good,
227 CASE WHEN entry_type = 3
228 THEN p_qty_uom
229 ELSE NULL END qty_uom
230 FROM (
231 SELECT s.shift_workday_fK_key ,
232 h.hour_pk_key hour_fk_key,
233 greatest(h.from_time, s.from_date, p_actual_from_date) actual_from_date,
234 least(h.to_time, s.to_date, p_actual_to_date) actual_to_date,
235 CASE WHEN s.availability_flag = 'Y'
236 THEN (least(h.to_time, s.to_date, p_actual_to_date) -
237 greatest(h.from_time, s.from_date, p_actual_from_date) + 1 /86400)/
238 Sum((least(h.to_time, s.to_date, p_actual_to_date) -
239 greatest(h.from_time, s.from_date, p_actual_from_date) + 1 /86400))
240 over (PARTITION BY s.equipment_fk_key, s.shift_workday_fK_key,
241 s.availability_flag) ELSE 0 END allocation_pct,
242 CASE WHEN s.availability_flag = 'Y' THEN 2 ELSE 0 END +
243 CASE WHEN s.shift_workday_fk_key = p_shift_workday_fK_key
244 THEN 1 ELSE 0 END entry_type
245 FROM (SELECT equipment_fk_key,
246 shift_workday_fk_key,
247 from_date,
248 To_Date,
249 availability_flag
250 FROM MTH_EQUIPMENT_SHIFTS_D
251 WHERE equipment_fk_key = p_equipment_fk_key AND
252 from_date IS NOT NULL AND
253 to_date IS NOT NULL AND
254 from_date <> To_Date AND
255 (p_actual_to_date BETWEEN from_date AND To_Date OR
256 p_actual_from_date BETWEEN from_date AND To_Date OR
257 from_date BETWEEN p_actual_from_date AND p_actual_to_date OR
258 To_Date BETWEEN p_actual_from_date AND p_actual_to_date )
259 ) s,
260 mth_hour_d h
261 WHERE equipment_fk_key = p_equipment_fk_key AND
262 s.from_date IS NOT NULL AND
263 s.to_date IS NOT NULL AND
264 s.from_date <> s.To_Date AND
265 (p_actual_to_date BETWEEN s.from_date AND s.To_Date OR
266 p_actual_from_date BETWEEN s.from_date AND s.To_Date OR
267 from_date BETWEEN p_actual_from_date AND p_actual_to_date OR
271 from_time BETWEEN p_actual_from_date AND p_actual_to_date OR
268 To_Date BETWEEN p_actual_from_date AND p_actual_to_date ) AND
269 (p_actual_to_date BETWEEN h.from_time AND h.To_time OR
270 p_actual_from_date BETWEEN h.from_time AND h.To_time OR
272 to_time BETWEEN p_actual_from_date AND p_actual_to_date ) AND
273 (s.from_date BETWEEN h.from_time AND h.To_time OR
274 s.to_date BETWEEN h.from_time AND h.To_time OR
275 h.from_time BETWEEN s.from_date AND s.To_Date OR
276 h.to_time BETWEEN s.from_date AND s.To_Date )
277 UNION ALL
278 SELECT Nvl(cs.shift_workday_fk_key, -99999) shift_workday_fK_key,
279 h.hour_pk_key hour_fk_key,
280 greatest(h.from_time, s.catch_all_from_date) actual_from_date,
281 least(h.to_time, s.catch_all_to_date) actual_to_date,
282 0 allocation_pct,
283 -1 entry_type -- for catch-all shift
284 FROM (SELECT equipment_fk_key,
285 lead ( from_date )
286 OVER ( PARTITION BY EQUIPMENT_FK_KEY
287 ORDER BY from_date ) - 1/86400 catch_all_to_date,
288 To_Date + 1/86400 catch_all_from_date
289 FROM MTH_EQUIPMENT_SHIFTS_D
290 WHERE equipment_fk_key = p_equipment_fk_key AND
291 from_date is not null and
292 to_date is not null and
293 from_date <> to_date and
294 (p_actual_to_date BETWEEN from_date AND To_Date OR
295 v_b_time_4_cs BETWEEN from_date AND To_Date OR
296 from_date BETWEEN v_b_time_4_cs AND p_actual_to_date OR
297 To_Date BETWEEN v_b_time_4_cs AND p_actual_to_date )
298 )s,
299 mth_hour_d h ,
300 (SELECT equipment_fk_key,
301 shift_workday_fk_key,
302 availability_date
303 FROM MTH_EQUIPMENT_SHIFTS_D
304 WHERE (from_date = To_Date OR
305 from_date IS NULL AND To_Date IS NULL) AND
306 equipment_fk_key = p_equipment_fk_key) cs
307 WHERE s.equipment_fk_key = p_equipment_fk_key AND
308 s.catch_all_from_date < s.catch_all_to_date AND
309 (p_actual_to_date BETWEEN s.catch_all_from_date AND s.catch_all_to_date OR
310 v_b_time_4_cs BETWEEN s.catch_all_from_date AND
311 s.catch_all_to_date OR
312 s.catch_all_from_date BETWEEN v_b_time_4_cs AND p_actual_to_date OR
313 s.catch_all_to_date BETWEEN v_b_time_4_cs AND p_actual_to_date ) AND
314 (p_actual_to_date BETWEEN h.from_time AND h.To_time OR
315 v_b_time_4_cs BETWEEN h.from_time AND h.To_time OR
316 h.from_time BETWEEN v_b_time_4_cs AND p_actual_to_date OR
317 h.to_time BETWEEN v_b_time_4_cs AND p_actual_to_date ) AND
318 (s.catch_all_from_date BETWEEN h.from_time AND h.To_time OR
319 s.catch_all_to_date BETWEEN h.from_time AND h.To_time OR
320 h.from_time BETWEEN s.catch_all_from_date AND s.catch_all_to_date OR
321 h.to_time BETWEEN s.catch_all_from_date AND s.catch_all_to_date ) AND
322 Trunc(h.from_time) = cs.availability_date (+)
323 --Trunc(s.catch_all_from_date) = cs.availability_date (+) AND
324 --s.equipment_fk_key = cs.equipment_fk_key (+)
325 );
326
327 mth_util_pkg.log_msg('insert_into_EPP_per_output_rec end', mth_util_pkg.G_DBG_PROC_FUN_END);
328
329 END insert_into_EPP_per_output_rec;
330
331
332 /* ****************************************************************************
333 * Procedure :populate_EPP_from_output *
334 * Description : Populate data from equipment output to EPP for production *
335 * equipment *
336 * File Name :MTHPDPB.PLS *
337 * Visibility :Public *
338 * Parameters :p_run_mode - Run mode: 'INITIAL' or 'INCREMENTAL' *
339 * Return Value :None *
340 **************************************************************************** */
341 PROCEDURE populate_EPP_from_output(p_run_mode IN VARCHAR2)
342
343 IS
344 -- Get the latest entries FOR EACH equipment IN EPP used TO calculate
345 -- actual FROM DATE FOR the NEXT entry
346 CURSOR c_getEPPLatestEntries IS
347 SELECT equipment_fk_key,
348 Max(Nvl(actual_to_date, actual_from_date))
349 latest_actual_to_date
350 FROM MTH_EQUIP_PROD_PERFORMANCE_F
351 GROUP BY equipment_fk_key;
352
353
354 -- Get the new set of output data since last run
355 CURSOR c_getNewOutputData IS
356 SELECT o.EQUIPMENT_FK_KEY,
357 o.SHIFT_WORKDAY_FK_KEY,
358 o.WORKORDER_FK_KEY,
359 o.SEGMENT_FK_KEY,
360 o.ITEM_FK_KEY,
361 o.READING_TIME,
362 o.HOUR_FK_KEY,
363 NVL(o.QTY_COMPLETED, 0) QTY_COMPLETED,
364 NVL(o.QTY_SCRAP, 0) QTY_SCRAP,
365 NVL(o.QTY_REJECTED, 0) QTY_REJECTED,
366 NVL(o.QTY_REWORK, 0) QTY_REWORK,
367 o.QTY_UOM,
368 NVL(o.QTY_GOOD, 0) QTY_GOOD,
369 NVL(o.QTY_OUTPUT, 0) QTY_OUTPUT,
370 o.SYSTEM_FK_KEY,
371 o.USER_DIM1_FK_KEY,
372 o.USER_DIM2_FK_KEY,
373 o.USER_DIM3_FK_KEY,
374 o.USER_DIM4_FK_KEY,
375 o.USER_DIM5_FK_KEY,
376 o.USER_ATTR1,
377 o.USER_ATTR2,
378 o.USER_ATTR3,
379 o.USER_ATTR4,
380 o.USER_ATTR5,
381 o.USER_MEASURE1,
382 o.USER_MEASURE2,
383 o.USER_MEASURE3,
384 o.USER_MEASURE4,
388 o.READING_TIME + 4000 actual_from_date, -- As a place holder
385 o.USER_MEASURE5,
386 Min(s.from_date) shift_from_date,
387 Max(s.To_Date) shift_to_date,
389 o.READING_TIME actual_to_date
390 FROM MTH_EQUIP_OUTPUT o,
391 MTH_EQUIPMENTS_D e,
392 MTH_EQUIPMENT_SHIFTS_D s,
393 (SELECT from_date, To_Date
394 FROM MTH_RUN_LOG
395 WHERE fact_table = 'MTH_EQUIP_PROD_PERFORMANCE_F' AND
396 ROWNUM = 1) rl
397 WHERE o.last_update_date > rl.from_date AND
398 o.last_update_date <= rl.To_Date AND
399 o.equipment_fk_key = e.equipment_pk_key AND
400 o.equipment_fk_key = s.equipment_fk_key AND
401 o.shift_workday_fk_key = s.shift_workday_fk_key AND
402 Upper(s.availability_flag) = 'Y' AND
403 Upper(e.production_equipment) = 'Y'
404 GROUP BY o.EQUIPMENT_FK_KEY,
405 o.SHIFT_WORKDAY_FK_KEY,
406 o.WORKORDER_FK_KEY,
407 o.SEGMENT_FK_KEY,
408 o.ITEM_FK_KEY,
409 o.READING_TIME,
410 o.HOUR_FK_KEY,
411 o.QTY_COMPLETED,
412 o.QTY_SCRAP,
413 o.QTY_REJECTED,
414 o.QTY_REWORK,
415 o.QTY_UOM,
416 o.QTY_GOOD,
417 o.QTY_OUTPUT,
418 o.SYSTEM_FK_KEY,
419 o.USER_DIM1_FK_KEY,
420 o.USER_DIM2_FK_KEY,
421 o.USER_DIM3_FK_KEY,
422 o.USER_DIM4_FK_KEY,
423 o.USER_DIM5_FK_KEY,
424 o.USER_ATTR1,
425 o.USER_ATTR2,
426 o.USER_ATTR3,
427 o.USER_ATTR4,
428 o.USER_ATTR5,
429 o.USER_MEASURE1,
430 o.USER_MEASURE2,
431 o.USER_MEASURE3,
432 o.USER_MEASURE4,
433 o.USER_MEASURE5
434 ORDER BY o.equipment_fk_key, o.reading_time, o.workorder_fk_key,
435 o.segment_fk_key, o.item_fk_key;
436
437 v_fact_table_name VARCHAR2(30) := 'MTH_EQUIP_PROD_PERFORMANCE_F';
438 v_epp_lookup_table EPPLatestTimeLookupTableType;
439 v_equipment_fk_key NUMBER;
440
441 -- The following three are prevous, current and next output record.
442 v_p_o_rec c_getNewOutputData%ROWTYPE;
443 v_c_o_rec c_getNewOutputData%ROWTYPE;
444 v_n_o_rec c_getNewOutputData%ROWTYPE;
445 v_rec_count NUMBER := 0;
446
447 BEGIN
448 mth_util_pkg.log_msg('populate_EPP_from_output start', mth_util_pkg.G_DBG_PROC_FUN_START);
449 -- 1. Call MTH_UTIL_PKG.MTH_RUN_LOG_PRE_LOAD() to set the cut-off date.
450 IF (p_run_mode = 'INIT') THEN
451 MTH_UTIL_PKG.MTH_RUN_LOG_PRE_LOAD(
452 P_FACT_TABLE => v_fact_table_name,
453 P_DB_GLOBAL_NAME => MTH_UTIL_PKG.MTH_UA_GET_VAL(),
454 P_RUN_MODE => 'INITIAL',
455 P_RUN_START_DATE => NULL,
456 P_IS_FACT => 0,
457 P_TO_DATE => SYSDATE);
458 ELSE
459 MTH_UTIL_PKG.MTH_RUN_LOG_PRE_LOAD(
460 P_FACT_TABLE => v_fact_table_name,
461 P_DB_GLOBAL_NAME => MTH_UTIL_PKG.MTH_UA_GET_VAL(),
462 P_RUN_MODE => 'INCR',
463 P_RUN_START_DATE => NULL,
464 P_IS_FACT => 0,
465 P_TO_DATE => SYSDATE);
466 END IF;
467
468 --2. Get the latest entries from EPP for each equipment along with
469 -- corresponding from_date, to_date, and line_num from the equipment shift.
470 FOR r_epp_latest_entry IN c_getEPPLatestEntries LOOP
471 v_equipment_fk_key := r_epp_latest_entry.equipment_fk_key;
472
473 v_epp_lookup_table(v_equipment_fk_key) :=
474 r_epp_latest_entry.latest_actual_to_date;
475
476 END LOOP;
477
478 -- 3. Fetch the new equipment output entries and process each row
479 OPEN c_getNewOutputData;
480 FETCH c_getNewOutputData INTO v_c_o_rec;
481 v_p_o_rec.EQUIPMENT_FK_KEY := NULL;
482 WHILE c_getNewOutputData%FOUND LOOP
483
484 -- 3.1 Calculate actual_from_date if it has not been calculated yet
485
486 -- Initially, v_c_o_rec.actual_from_date is set to be larger
487 -- than v_c_o_rec.actual_to_date, which indicates that actual from date
488 -- needs to be calculated.
489 IF v_c_o_rec.actual_from_date > v_c_o_rec.actual_to_date
490 THEN
491 IF ( v_p_o_rec.EQUIPMENT_FK_KEY IS NOT NULL AND
492 v_p_o_rec.EQUIPMENT_FK_KEY = v_c_o_rec.EQUIPMENT_FK_KEY ) THEN
493 -- Calculate the actual from date from the previous entry within
494 -- output records being fetched
495 v_c_o_rec.actual_from_date := v_p_o_rec.actual_to_date + 1/86400;
496 ELSIF v_epp_lookup_table.EXISTS(v_c_o_rec.EQUIPMENT_FK_KEY) THEN
497 v_c_o_rec.actual_from_date :=
498 v_epp_lookup_table(v_c_o_rec.EQUIPMENT_FK_KEY) + 1/86400;
499 ELSIF v_c_o_rec.actual_from_date IS NULL OR
500 v_c_o_rec.actual_from_date > v_c_o_rec.actual_to_date
501 THEN -- The first entry in EPP, set it to be the beginning of shift
502 v_c_o_rec.actual_from_date := v_c_o_rec.shift_from_date;
503 END IF;
504
505 END IF;
506
507 -- 3.2 Merge entries if next output record has the same keys.
508 FETCH c_getNewOutputData INTO v_n_o_rec;
509
510 IF c_getNewOutputData%FOUND AND
514 v_n_o_rec.SEGMENT_FK_KEY = v_c_o_rec.SEGMENT_FK_KEY AND
511 v_n_o_rec.EQUIPMENT_FK_KEY = v_c_o_rec.EQUIPMENT_FK_KEY AND
512 v_n_o_rec.SHIFT_WORKDAY_FK_KEY = v_c_o_rec.SHIFT_WORKDAY_FK_KEY AND
513 v_n_o_rec.WORKORDER_FK_KEY = v_c_o_rec.WORKORDER_FK_KEY AND
515 v_n_o_rec.ITEM_FK_KEY = v_c_o_rec.ITEM_FK_KEY
516 THEN
517 -- Merge the next entry with the current one. Use user-defined attributes
518 -- from the next entry
519 v_n_o_rec.actual_from_date := v_c_o_rec.actual_from_date;
520 v_n_o_rec.qty_completed:=v_n_o_rec.qty_completed+v_c_o_rec.qty_completed;
521 v_n_o_rec.qty_scrap := v_n_o_rec.qty_scrap + v_c_o_rec.qty_scrap;
522 v_n_o_rec.qty_rejected := v_n_o_rec.qty_rejected + v_c_o_rec.qty_rejected;
523 v_n_o_rec.qty_rework := v_n_o_rec.qty_rework + v_c_o_rec.qty_rework;
524 v_n_o_rec.qty_good := v_n_o_rec.qty_good + v_c_o_rec.qty_good;
525 v_n_o_rec.qty_output := v_n_o_rec.qty_output + v_c_o_rec.qty_output;
526
527 v_c_o_rec := v_n_o_rec;
528
529 ELSE
530
531 -- 3.3 Adjust the actual_from_date for the co-product produced
532 -- at the same time as the previous output reading
533 IF v_p_o_rec.EQUIPMENT_FK_KEY = v_c_o_rec.EQUIPMENT_FK_KEY AND
534 v_p_o_rec.SHIFT_WORKDAY_FK_KEY = v_c_o_rec.SHIFT_WORKDAY_FK_KEY AND
535 v_p_o_rec.WORKORDER_FK_KEY = v_c_o_rec.WORKORDER_FK_KEY AND
536 v_p_o_rec.SEGMENT_FK_KEY = v_c_o_rec.SEGMENT_FK_KEY AND
537 v_p_o_rec.ITEM_FK_KEY <> v_c_o_rec.ITEM_FK_KEY AND
538 v_p_o_rec.actual_to_date = v_c_o_rec.actual_to_date THEN
539 v_c_o_rec.actual_from_date := Greatest(v_c_o_rec.shift_from_date,
540 v_p_o_rec.actual_from_date);
541 END IF;
542
543 -- 3.4 Break entry by shifts, shift lines, and/or hours and then
544 -- insert each entry into EPP
545
546 IF (v_c_o_rec.actual_to_date < v_c_o_rec.actual_from_date ) THEN
547 RAISE_APPLICATION_ERROR (-20001,
548 'The reading time of ' ||
549 To_Char(v_c_o_rec.actual_to_date, 'mm/dd/yyyy hh24:mi:ss') ||
550 ' for the current reading with the equipment fk key ' ||
551 v_c_o_rec.equipment_fk_key ||
552 ' is no later than the existing data in equipment production' ||
553 ' performance table.');
554 END IF;
555
556 insert_into_EPP_per_output_rec (v_c_o_rec.actual_from_date,
557 v_c_o_rec.actual_to_date,
558 v_c_o_rec.equipment_fk_key,
559 v_c_o_rec.shift_workday_fk_key,
560 v_c_o_rec.workorder_fk_key,
561 v_c_o_rec.segment_fk_key,
562 v_c_o_rec.item_fk_key,
563 v_c_o_rec.system_fk_key,
564 v_c_o_rec.user_dim1_fk_key,
565 v_c_o_rec.user_dim2_fk_key,
566 v_c_o_rec.user_dim3_fk_key,
567 v_c_o_rec.user_dim4_fk_key,
568 v_c_o_rec.user_dim5_fk_key,
569 v_c_o_rec.user_attr1,
570 v_c_o_rec.user_attr2,
571 v_c_o_rec.user_attr3,
572 v_c_o_rec.user_attr4,
573 v_c_o_rec.user_attr5,
574 v_c_o_rec.user_measure1,
575 v_c_o_rec.user_measure2,
576 v_c_o_rec.user_measure3,
577 v_c_o_rec.user_measure4,
578 v_c_o_rec.user_measure5,
579 SYSDATE,
580 -99999,
581 -99999,
582 -99999,
583 -99999,
584 -99999,
585 v_c_o_rec.qty_completed,
586 v_c_o_rec.qty_scrap,
587 v_c_o_rec.qty_output,
588 v_c_o_rec.qty_rejected,
589 v_c_o_rec.qty_rework,
590 v_c_o_rec.qty_good,
591 v_c_o_rec.qty_uom);
592
593 v_p_o_rec := v_c_o_rec;
594
595 IF c_getNewOutputData%FOUND THEN
596 v_c_o_rec := v_n_o_rec;
597 ELSE
598 FETCH c_getNewOutputData INTO v_c_o_rec;
599 END IF;
600
601
602 END IF;
603 v_rec_count := v_rec_count + 1;
604 END LOOP;
605 CLOSE c_getNewOutputData;
606
607 mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_PROD_PERFORMANCE_F - ' || v_rec_count, mth_util_pkg.G_DBG_ROW_CNT);
608
609 --Call post load
610 MTH_UTIL_PKG.MTH_RUN_LOG_POST_LOAD(
611 P_FACT_TABLE => v_fact_table_name,
612 P_DB_GLOBAL_NAME => MTH_UTIL_PKG.MTH_UA_GET_VAL());
613
614 mth_util_pkg.log_msg('populate_EPP_from_output end', mth_util_pkg.G_DBG_PROC_FUN_END);
615 END populate_EPP_from_output;
616
617
618 /* ****************************************************************************
619 * Procedure :populate_EPP_for_NonProd_Equip *
620 * Description : Populate data for non-production equipments from *
621 * equpiment status in to EPP *
625 * Return Value :None *
622 * File Name :MTHPDPB.PLS *
623 * Visibility :Public *
624 * Parameters :None *
626 **************************************************************************** */
627 PROCEDURE populate_EPP_for_NonProd_Equip(p_run_mode VARCHAR2)
628
629 IS
630
631 v_fact_table_name VARCHAR2(30) := 'MTH_EQUIP_NON_PROD_EPP';
632
633 BEGIN
634
635 mth_util_pkg.log_msg('POPULATE_EPP_FOR_NONPROD_EQUIP start', mth_util_pkg.G_DBG_PROC_FUN_START);
636
637 -- 1. Call MTH_UTIL_PKG.MTH_RUN_LOG_PRE_LOAD() to set the cut-off date.
638 IF (p_run_mode = 'INIT') THEN
639 MTH_UTIL_PKG.MTH_RUN_LOG_PRE_LOAD(
640 P_FACT_TABLE => v_fact_table_name,
641 P_DB_GLOBAL_NAME => MTH_UTIL_PKG.MTH_UA_GET_VAL(),
642 P_RUN_MODE => 'INITIAL',
643 P_RUN_START_DATE => NULL,
644 P_IS_FACT => 0,
645 P_TO_DATE => SYSDATE);
646 ELSE
647 MTH_UTIL_PKG.MTH_RUN_LOG_PRE_LOAD(
648 P_FACT_TABLE => v_fact_table_name,
649 P_DB_GLOBAL_NAME => MTH_UTIL_PKG.MTH_UA_GET_VAL(),
650 P_RUN_MODE => 'INCR',
651 P_RUN_START_DATE => NULL,
652 P_IS_FACT => 0,
653 P_TO_DATE => SYSDATE);
654 END IF;
655
656 -- 2. Insert Non-Production Equipment Status rows into EPP.
657
658 MERGE INTO MTH_EQUIP_PROD_PERFORMANCE_F
659 USING (
660 SELECT EQUIPMENT_FK_KEY,
661 SHIFT_WORKDAY_FK_KEY,
662 HOUR_FK_KEY,
663 MTH_UTIL_PKG.MTH_UA_GET_VAL() SYSTEM_FK_KEY,
664 MTH_UTIL_PKG.MTH_UA_GET_VAL() WORKORDER_FK_KEY,
665 MTH_UTIL_PKG.MTH_UA_GET_VAL() SEGMENT_FK_KEY,
666 MTH_UTIL_PKG.MTH_UA_GET_VAL() ITEM_FK_KEY,
667 NEW_FROM_DATE,
668 CASE WHEN TEMP_TO_DATE > SYSDATE THEN NULL ELSE NEW_TO_DATE END NEW_TO_DATE,
669 RUN_HOURS,
670 IDLE_HOURS,
671 DOWN_HOURS,
672 OFF_HOURS,
673 0 QTY_COMPLETED,
674 0 QTY_SCRAP,
675 0 QTY_REJECTED,
676 0 QTY_REWORK,
677 0 QTY_GOOD,
678 0 QTY_OUTPUT,
679 SYSDATE CREATION_DATE,
680 SYSDATE LAST_UPDATE_DATE,
681 MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID,
682 MTH_UTIL_PKG.MTH_UA_GET_VAL() LAST_UPDATE_SYSTEM_ID,
683 MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATED_BY
684 FROM (
685 SELECT EQUIPMENT_FK_KEY,
686 SHIFT_WORKDAY_FK_KEY,
687 HOUR_FK_KEY,
688 Min(STATUS_FROM_DATE) NEW_FROM_DATE,
689 Max(STATUS_TO_DATE) NEW_TO_DATE,
690 Max(CALC_STATUS_TO_DATE) TEMP_TO_DATE,
691 Sum(CASE WHEN STATUS = '1'
692 THEN (((NVL(STATUS_TO_DATE, SYSDATE) - STATUS_FROM_DATE)*24)+(1/3600))
693 ELSE 0 END) RUN_HOURS,
694 Sum(CASE WHEN STATUS = '2'
695 THEN (((NVL(STATUS_TO_DATE, SYSDATE) - STATUS_FROM_DATE)*24)+(1/3600))
696 ELSE 0 END) IDLE_HOURS,
697 Sum(CASE WHEN STATUS = '3'
698 THEN (((NVL(STATUS_TO_DATE, SYSDATE) - STATUS_FROM_DATE)*24)+(1/3600))
699 ELSE 0 END) DOWN_HOURS,
700 Sum(CASE WHEN STATUS = '4'
701 THEN (((NVL(STATUS_TO_DATE, SYSDATE) - STATUS_FROM_DATE)*24)+(1/3600))
702 ELSE 0 END) OFF_HOURS
703 FROM ( SELECT agg_sts.EQUIPMENT_FK_KEY,
704 agg_sts.SHIFT_WORKDAY_FK_KEY,
705 agg_sts.STATUS_FROM_DATE,
706 Nvl(agg_sts.STATUS_TO_DATE, Least(SYSDATE, h.TO_TIME, es.To_Date)) STATUS_TO_DATE,
707 NVL(agg_sts.STATUS_TO_DATE, SYSDATE+10) CALC_STATUS_TO_DATE,
708 agg_sts.STATUS,
709 agg_sts.HOUR_FK_KEY
710 FROM (
711 SELECT sts_all.EQUIPMENT_FK_KEY,
712 sts_all.SHIFT_WORKDAY_FK_KEY,
713 sts_all.FROM_DATE STATUS_FROM_DATE,
714 sts_all.To_Date STATUS_TO_DATE,
715 sts_all.STATUS,
716 sts_all.SYSTEM_FK_KEY,
717 sts_all.HOUR_FK_KEY
718 FROM MTH_EQUIP_STATUSES sts_all,
719 (SELECT sts.EQUIPMENT_FK_KEY,
720 sts.SHIFT_WORKDAY_FK_KEY,
721 sts.FROM_DATE STATUS_FROM_DATE,
722 sts.To_Date STATUS_TO_DATE,
723 sts.STATUS,
724 sts.SYSTEM_FK_KEY,
725 sts.HOUR_FK_KEY
726 FROM MTH_EQUIP_STATUSES sts,
727 (SELECT FACT_TABLE, FROM_DATE RUN_LOG_FROM_DATE,
728 TO_DATE RUN_LOG_TO_DATE
729 FROM MTH_RUN_LOG
730 WHERE FACT_TABLE = 'MTH_EQUIP_NON_PROD_EPP'
731 AND ROWNUM =1) run_log,
732 MTH_EQUIPMENTS_D equip
733 WHERE sts.LAST_UPDATE_DATE > run_log.RUN_LOG_FROM_DATE
734 AND sts.LAST_UPDATE_DATE <= run_log.RUN_LOG_TO_DATE
735 AND equip.EQUIPMENT_PK_KEY = sts.EQUIPMENT_FK_KEY
736 AND Upper(Nvl(equip.PRODUCTION_EQUIPMENT, 'N')) = 'N')sts_new_rows
737 WHERE sts_all.EQUIPMENT_FK_KEY = sts_new_rows.EQUIPMENT_FK_KEY
738 AND sts_all.SHIFT_WORKDAY_FK_KEY = sts_new_rows.SHIFT_WORKDAY_FK_KEY
739 AND sts_all.HOUR_FK_KEY = sts_new_rows.HOUR_FK_KEY ) agg_sts,
740 MTH_EQUIPMENT_SHIFTS_D es, MTH_HOUR_D h
741 WHERE es.ENTITY_TYPE = 'EQUIPMENT'
742 AND es.SHIFT_WORKDAY_FK_KEY = agg_sts.SHIFT_WORKDAY_FK_KEY
743 AND es.EQUIPMENT_FK_KEY = agg_sts.EQUIPMENT_FK_KEY
744 AND h.HOUR_PK_KEY = agg_sts.HOUR_FK_KEY
748 NVL(agg_sts.STATUS_TO_DATE, SYSDATE+10))
745 GROUP BY agg_sts.EQUIPMENT_FK_KEY, agg_sts.SHIFT_WORKDAY_FK_KEY, agg_sts.HOUR_FK_KEY, agg_sts.STATUS,
746 agg_sts.STATUS_FROM_DATE, STATUS_TO_DATE,
747 Nvl(agg_sts.STATUS_TO_DATE, Least(SYSDATE, h.TO_TIME, es.To_Date)),
749 GROUP BY EQUIPMENT_FK_KEY, SHIFT_WORKDAY_FK_KEY, HOUR_FK_KEY)) MERGE_QUERY
750 ON (MTH_EQUIP_PROD_PERFORMANCE_F.EQUIPMENT_FK_KEY = MERGE_QUERY.EQUIPMENT_FK_KEY
751 AND MTH_EQUIP_PROD_PERFORMANCE_F.SHIFT_WORKDAY_FK_KEY = MERGE_QUERY.SHIFT_WORKDAY_FK_KEY
752 AND MTH_EQUIP_PROD_PERFORMANCE_F.ACTUAL_FROM_DATE = MERGE_QUERY.NEW_FROM_DATE
753 AND MTH_EQUIP_PROD_PERFORMANCE_F.WORKORDER_FK_KEY = MERGE_QUERY.WORKORDER_FK_KEY
754 AND MTH_EQUIP_PROD_PERFORMANCE_F.WORKORDER_FK_KEY = MERGE_QUERY.SEGMENT_FK_KEY
755 AND MTH_EQUIP_PROD_PERFORMANCE_F.WORKORDER_FK_KEY = MERGE_QUERY.ITEM_FK_KEY)
756
757 WHEN MATCHED THEN
758 UPDATE
759 SET ACTUAL_TO_DATE = MERGE_QUERY.NEW_TO_DATE,
760 SYSTEM_FK_KEY = MERGE_QUERY.SYSTEM_FK_KEY,
761 RUN_HOURS = MERGE_QUERY.RUN_HOURS,
762 IDLE_HOURS = MERGE_QUERY.IDLE_HOURS,
763 DOWN_HOURS = MERGE_QUERY.DOWN_HOURS,
764 OFF_HOURS = MERGE_QUERY.OFF_HOURS,
765 QTY_COMPLETED = MERGE_QUERY.QTY_COMPLETED,
766 QTY_SCRAP = MERGE_QUERY.QTY_SCRAP,
767 QTY_REJECTED = MERGE_QUERY.QTY_REJECTED,
768 QTY_REWORK = MERGE_QUERY.QTY_REWORK,
769 QTY_GOOD = MERGE_QUERY.QTY_GOOD,
770 QTY_OUTPUT = MERGE_QUERY.QTY_OUTPUT,
771 HOUR_FK_KEY = MERGE_QUERY.HOUR_FK_KEY,
772 LAST_UPDATE_DATE = MERGE_QUERY.LAST_UPDATE_DATE,
773 LAST_UPDATE_SYSTEM_ID = MERGE_QUERY.LAST_UPDATE_SYSTEM_ID,
774 CREATED_BY = MERGE_QUERY.CREATED_BY
775
776 WHEN NOT MATCHED THEN
777 INSERT (EQUIPMENT_FK_KEY,
778 SHIFT_WORKDAY_FK_KEY,
779 HOUR_FK_KEY,
780 SYSTEM_FK_KEY,
781 WORKORDER_FK_KEY,
782 SEGMENT_FK_KEY,
783 ITEM_FK_KEY,
784 ACTUAL_FROM_DATE,
785 ACTUAL_TO_DATE,
786 RUN_HOURS,
787 IDLE_HOURS,
788 DOWN_HOURS,
789 OFF_HOURS,
790 QTY_COMPLETED,
791 QTY_SCRAP,
792 QTY_REJECTED,
793 QTY_REWORK,
794 QTY_GOOD,
795 QTY_OUTPUT,
796 CREATION_DATE,
797 LAST_UPDATE_DATE,
798 CREATION_SYSTEM_ID,
799 LAST_UPDATE_SYSTEM_ID,
800 CREATED_BY,
801 EQUIP_PROD_PERF_PK_KEY)
802 VALUES(
803 MERGE_QUERY.EQUIPMENT_FK_KEY,
804 MERGE_QUERY.SHIFT_WORKDAY_FK_KEY,
805 MERGE_QUERY.HOUR_FK_KEY,
806 MERGE_QUERY.SYSTEM_FK_KEY,
807 MERGE_QUERY.WORKORDER_FK_KEY,
808 MERGE_QUERY.SEGMENT_FK_KEY,
809 MERGE_QUERY.ITEM_FK_KEY,
810 MERGE_QUERY.NEW_FROM_DATE,
811 MERGE_QUERY.NEW_TO_DATE,
812 MERGE_QUERY.RUN_HOURS,
813 MERGE_QUERY.IDLE_HOURS,
814 MERGE_QUERY.DOWN_HOURS,
815 MERGE_QUERY.OFF_HOURS,
816 MERGE_QUERY.QTY_COMPLETED,
817 MERGE_QUERY.QTY_SCRAP,
818 MERGE_QUERY.QTY_REJECTED,
819 MERGE_QUERY.QTY_REWORK,
820 MERGE_QUERY.QTY_GOOD,
821 MERGE_QUERY.QTY_OUTPUT,
822 MERGE_QUERY.CREATION_DATE,
823 MERGE_QUERY.LAST_UPDATE_DATE,
824 MERGE_QUERY.CREATION_SYSTEM_ID,
825 MERGE_QUERY.LAST_UPDATE_SYSTEM_ID,
826 MERGE_QUERY.CREATED_BY,
827 MTH_EQUIP_PROD_PERF_S.NEXTVAL) ;
828
829 mth_util_pkg.log_msg('Number of rows merged in MTH_EQUIP_PROD_PERFORMANCE_F - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
830
831 -- 3. Call MTH_UTIL_PKG.MTH_RUN_LOG_POST_LOAD() to save the cut-off date as
832 -- the last run time.
833 MTH_UTIL_PKG.MTH_RUN_LOG_POST_LOAD(
834 P_FACT_TABLE => v_fact_table_name,
835 P_DB_GLOBAL_NAME => MTH_UTIL_PKG.MTH_UA_GET_VAL());
836
837
838 mth_util_pkg.log_msg('POPULATE_EPP_FOR_NONPROD_EQUIP end', mth_util_pkg.G_DBG_PROC_FUN_END);
839
840
841 EXCEPTION
842 WHEN OTHERS THEN
843 --Call logging API and then throw exception
844 mth_util_pkg.log_msg('Exception OTHERS in populate_EPP_for_NonProd_Equip', mth_util_pkg.G_DBG_EXCEPTION);
845 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
846 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
847
848 END populate_EPP_for_NonProd_Equip;
849
850 /* ****************************************************************************
851 * Procedure :update_EPP_from_status *
852 * Description : Update run_hours,idle_hours,down_hours,off_hours *
853 * of EPP from status table *
854 * File Name :MTHPDPB.PLS *
855 * Visibility :Public *
856 * Parameters :None *
857 * Return Value :None *
858 **************************************************************************** */
859 PROCEDURE update_EPP_from_status(p_run_mode VARCHAR2)
860
861 IS
862 v_fact_table_name VARCHAR2(30) := 'MTH_EQUIP_PROD_PERFORMANCE_F_S';
863 v_last_update_date DATE;
864
865 BEGIN
866
867 v_last_update_date := SYSDATE;
868
869 mth_util_pkg.log_msg('UPDATE_EPP_FROM_STATUS start', mth_util_pkg.G_DBG_PROC_FUN_START);
870
871 IF (p_run_mode = 'INIT') THEN
872 MTH_UTIL_PKG.MTH_RUN_LOG_PRE_LOAD(
873 P_FACT_TABLE => v_fact_table_name,
874 P_DB_GLOBAL_NAME => MTH_UTIL_PKG.MTH_UA_GET_VAL(),
875 P_RUN_MODE => 'INITIAL',
879 ELSE
876 P_RUN_START_DATE => NULL,
877 P_IS_FACT => 0,
878 P_TO_DATE => SYSDATE);
880 MTH_UTIL_PKG.MTH_RUN_LOG_PRE_LOAD(
881 P_FACT_TABLE => v_fact_table_name,
882 P_DB_GLOBAL_NAME => MTH_UTIL_PKG.MTH_UA_GET_VAL(),
883 P_RUN_MODE => 'INCR',
884 P_RUN_START_DATE => NULL,
885 P_IS_FACT => 0,
886 P_TO_DATE => SYSDATE);
887 END IF;
888
889 MERGE
890 INTO
891 MTH_EQUIP_PROD_PERFORMANCE_F eppf
892 USING
893 (
894 SELECT Sum(Decode(sts_shift.status,1,((Least(epp.prod_perf_actual_to_date,sts_shift.status_to_date)-Greatest(epp.actual_from_date,sts_shift.from_date))*24)+(1/3600),0)) run_hours,
895 Sum(Decode(sts_shift.status,3,((Least(epp.prod_perf_actual_to_date,sts_shift.status_to_date)-Greatest(epp.actual_from_date,sts_shift.from_date))*24)+(1/3600),0)) down_hours,
896 Sum(Decode(sts_shift.status,2,((Least(epp.prod_perf_actual_to_date,sts_shift.status_to_date)-Greatest(epp.actual_from_date,sts_shift.from_date))*24)+(1/3600),0)) idle_hours,
897 Sum(Decode(sts_shift.status,4,((Least(epp.prod_perf_actual_to_date,sts_shift.status_to_date)-Greatest(epp.actual_from_date,sts_shift.from_date))*24)+(1/3600),0)) off_hours,
898 epp.equipment_fk_key,
899 epp.workorder_fk_key,
900 epp.segment_fk_key,
901 epp.shift_workday_fk_key,
902 epp.hour_fk_key,
903 epp.item_fk_key,
904 epp.actual_from_date
905 FROM (SELECT DISTINCT epp.equipment_fk_key,
906 epp.workorder_fk_key,
907 epp.segment_fk_key,
908 epp.shift_workday_fk_key,
909 epp.item_fk_key,
910 epp.actual_from_date,
911 epp.actual_to_date,
912 epp.run_hours,
913 epp.down_hours,
914 epp.idle_hours,
915 epp.hour_fk_key,
916 Nvl(epp.actual_to_date,SYSDATE) prod_perf_actual_to_date,
917 epp.last_update_date
918 FROM mth_equip_prod_performance_f epp,
919 mth_equip_statuses sts
920 WHERE epp.equipment_fk_key = sts.equipment_fk_key
921 AND epp.shift_workday_fk_key = sts.shift_workday_fk_key
922 AND epp.hour_fk_key = sts.hour_fk_key)epp,
923 (SELECT sts.equipment_fk_key,
924 sts.shift_workday_fk_key,
925 sts.hour_fk_key,
926 sts.from_date,
927 sts.status,
928 Min(sts.To_Date) to_date,
929 Least(Nvl(Min(Decode(sts.To_Date,NULL,mhd.to_time,sts.To_Date)),SYSDATE),Max(shift.To_Date)) status_to_date
930 FROM mth_equip_statuses sts,
931 mth_equipment_shifts_d shift,
932 mth_hour_d mhd
933 WHERE sts.equipment_fk_key = shift.equipment_fk_key
934 AND sts.shift_workday_fk_key = shift.shift_workday_fk_key
935 AND sts.hour_fk_key = mhd.hour_pk_key
936 GROUP BY sts.equipment_fk_key,
937 sts.shift_workday_fk_key,
938 sts.hour_fk_key,
939 sts.from_date,
940 sts.status,
941 shift.equipment_fk_key,
942 shift.shift_workday_fk_key) sts_shift,
943 (SELECT from_date, To_Date
944 FROM mth_run_log
945 WHERE fact_table = v_fact_table_name
946 AND ROWNUM = 1) mrl
947 WHERE epp.equipment_fk_key = sts_shift.equipment_fk_key
948 AND epp.shift_workday_fk_key = sts_shift.shift_workday_fk_key
949 AND epp.last_update_date > mrl.from_date AND epp.last_update_date <= mrl.to_date
950 AND epp.hour_fk_key = sts_shift.hour_fk_key
951 AND (( epp.prod_perf_actual_to_date BETWEEN sts_shift.from_date AND sts_shift.status_to_date )
952 OR ( epp.actual_from_date BETWEEN sts_shift.from_date AND sts_shift.status_to_date )
953 OR ( sts_shift.status_to_date BETWEEN epp.actual_from_date AND epp.prod_perf_actual_to_date )
954 OR ( sts_shift.from_date BETWEEN epp.actual_from_date AND epp.prod_perf_actual_to_date ))
955 GROUP BY epp.equipment_fk_key,
956 epp.workorder_fk_key,
957 epp.segment_fk_key,
958 epp.shift_workday_fk_key,
959 epp.hour_fk_key,
960 epp.item_fk_key,
961 epp.actual_from_date) cal_hours
962 ON
963 ( eppf.equipment_fk_key = cal_hours.equipment_fk_key
964 AND eppf.workorder_fk_key = cal_hours.workorder_fk_key
965 AND eppf.segment_fk_key = cal_hours.segment_fk_key
966 AND eppf.shift_workday_fk_key = cal_hours.shift_workday_fk_key
967 AND eppf.item_fk_key = cal_hours.item_fk_key
968 AND eppf.actual_from_date = cal_hours.actual_from_date
969 AND eppf.hour_fk_key = cal_hours.hour_fk_key
970 )
971 WHEN MATCHED THEN
972 UPDATE
973 SET
974 eppf.last_update_date = v_last_update_date,
975 eppf.run_hours = cal_hours.run_hours,
976 eppf.down_hours = cal_hours.down_hours,
977 eppf.idle_hours = cal_hours.idle_hours,
978 eppf.off_hours = cal_hours.off_hours;
979
980 mth_util_pkg.log_msg('Number of rows merged in MTH_EQUIP_PROD_PERFORMANCE_F - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
981
982 --Call post load
983 MTH_UTIL_PKG.MTH_RUN_LOG_POST_LOAD(
984 P_FACT_TABLE => v_fact_table_name,
985 P_DB_GLOBAL_NAME => MTH_UTIL_PKG.MTH_UA_GET_VAL());
986
987
988 mth_util_pkg.log_msg('UPDATE_EPP_FROM_STATUS end', mth_util_pkg.G_DBG_PROC_FUN_END);
989
990 EXCEPTION
991 WHEN OTHERS THEN
992 --Call logging API and then throw exception
996
993 mth_util_pkg.log_msg('Exception OTHERS in update_EPP_from_status', mth_util_pkg.G_DBG_EXCEPTION);
994 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
995 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
997 END update_EPP_from_status;
998
999 /* ****************************************************************************
1000 * Procedure :populate_prod_perf_detail *
1001 * Description : Populate data for equipments into prodcution performance *
1002 * details table. *
1003 * File Name :MTHPDPB.PLS *
1004 * Visibility :Public *
1005 * Parameters :None *
1006 * Return Value :None *
1007 **************************************************************************** */
1008 PROCEDURE populate_prod_perf_detail(p_run_mode IN VARCHAR2)
1009 IS
1010 v_fact_table_name VARCHAR2(100);
1011 v_log_date DATE;
1012 v_ua_val VARCHAR2(100);
1013
1014 BEGIN
1015 --Initialize parameters
1016 v_fact_table_name := 'MTH_EQUIP_PROD_PERF_DETAIL_F';
1017 v_log_date := SYSDATE;
1018 v_ua_val := mth_util_pkg.mth_ua_get_val();
1019
1020 mth_util_pkg.log_msg('POPULATE_PROD_PERF_DETAIL start', mth_util_pkg.G_DBG_PROC_FUN_START);
1021
1022 --Call run log pre load
1023 IF (p_run_mode = 'INIT')
1024 THEN
1025 MTH_UTIL_PKG.MTH_RUN_LOG_PRE_LOAD(
1026 P_FACT_TABLE => v_fact_table_name,
1027 P_DB_GLOBAL_NAME => MTH_UTIL_PKG.MTH_UA_GET_VAL(),
1028 P_RUN_MODE => 'INITIAL',
1029 P_RUN_START_DATE => NULL,
1030 P_IS_FACT => 0,
1031 P_TO_DATE => SYSDATE);
1032 ELSE
1033 MTH_UTIL_PKG.MTH_RUN_LOG_PRE_LOAD(
1034 P_FACT_TABLE => v_fact_table_name,
1035 P_DB_GLOBAL_NAME => MTH_UTIL_PKG.MTH_UA_GET_VAL(),
1036 P_RUN_MODE => 'INCR',
1037 P_RUN_START_DATE => NULL,
1038 P_IS_FACT => 0,
1039 P_TO_DATE => SYSDATE);
1040 END IF;
1041
1042 --Merge statement
1043 MERGE
1044 INTO
1045 MTH_EQUIP_PROD_PERF_DETAIL_F epd
1046 USING
1047 (
1048 SELECT epp.equip_prod_perf_fk_key,
1049 epp.equipment_fk_key,
1050 epp.system_fk_key,
1051 sts.status,
1052 Greatest(epp.actual_from_date, sts.from_date) from_time,
1053 Least(epp.actual_to_date,sts.to_date) to_time,
1054 epp.workorder_fk_key,
1055 epp.item_fk_key,
1056 epp.segment_fk_key,
1057 epp.shift_workday_fk_key,
1058 epp.hour_fk_key,
1059 ((Least(epp.actual_to_date,sts.to_date) - Greatest(epp.actual_from_date,sts.from_date )) * 24 + 1/3600) duration_in_hours,
1060 v_log_date creation_date,
1061 v_log_date last_update_date,
1062 v_ua_val creation_system_id,
1063 v_ua_val last_update_system_id,
1064 v_ua_val created_by,
1065 v_ua_val last_updated_by,
1066 v_ua_val last_update_login
1067 FROM (SELECT DISTINCT Max(epp.equip_prod_perf_pk_key) equip_prod_perf_fk_key,
1068 epp.equipment_fk_key,
1069 epp.actual_from_date,
1070 epp.actual_to_date,
1071 epp.workorder_fk_key,
1072 epp.segment_fk_key,
1073 epp.shift_workday_fk_key,
1074 Max(epp.item_fk_key) item_fk_key,
1075 Max(epp.system_fk_key) system_fk_key,
1076 epp.hour_fk_key
1077 FROM mth_equip_prod_performance_f epp,
1078 mth_run_log mrl
1079 WHERE epp.actual_to_date IS NOT NULL
1080 AND (epp.run_hours > 0 OR epp.down_hours > 0 OR epp.idle_hours > 0 OR epp.off_hours > 0)
1081 AND mrl.fact_table = 'MTH_EQUIP_PROD_PERF_DETAIL_F'
1082 AND epp.last_update_date > mrl.from_date AND epp.last_update_date <= mrl.To_Date
1083 GROUP BY epp.equipment_fk_key,
1084 epp.actual_from_date,
1085 epp.actual_to_date,
1086 epp.workorder_fk_key,
1087 epp.segment_fk_key,
1088 epp.shift_workday_fk_key,
1089 epp.hour_fk_key) epp,
1090 mth_equip_statuses sts
1091 WHERE epp.equipment_fk_key = sts.equipment_fk_key
1092 AND ((epp.actual_from_date >= sts.from_date AND epp.actual_from_date <= sts.to_date)
1093 OR (epp.actual_to_date >= sts.from_date AND epp.actual_to_date <= sts.to_date )
1094 OR (sts.from_date >= epp.actual_from_date AND sts.from_date <= epp.actual_to_date ))
1095 AND sts.to_date IS NOT NULL) epp
1096 ON
1097 (
1098 epd.equipment_fk_key = epp.equipment_fk_key
1099 AND epd.status = epp.status
1100 AND epd.from_time = epp.from_time
1101 AND epd.to_time = epp.to_time
1102 )
1103 WHEN MATCHED
1104 THEN
1105 UPDATE
1106 SET
1107 epd.system_fk_key = epp.system_fk_key
1108 WHEN NOT MATCHED
1109 THEN
1110 INSERT
1111 ( epd.epp_detail_pk_key,
1112 epd.equipment_fk_key,
1113 epd.system_fk_key,
1114 epd.status,
1115 epd.from_time,
1116 epd.to_time,
1117 epd.workorder_fk_key,
1118 epd.item_fk_key,
1119 epd.segment_fk_key,
1120 epd.shift_workday_fk_key,
1121 epd.hour_fk_key,
1125 epd.creation_system_id,
1122 epd.duration_in_hours,
1123 epd.creation_date,
1124 epd.last_update_date,
1126 epd.last_update_system_id,
1127 epd.created_by,
1128 epd.last_updated_by,
1129 epd.last_udpate_login,
1130 epd.equip_prod_perf_fk_key)
1131 VALUES
1132 ( mth_equip_prod_perf_detail_f_s.NEXTVAL,
1133 epp.equipment_fk_key,
1134 epp.system_fk_key,
1135 epp.status,
1136 epp.from_time,
1137 epp.to_time,
1138 epp.workorder_fk_key,
1139 epp.item_fk_key,
1140 epp.segment_fk_key,
1141 epp.shift_workday_fk_key,
1142 epp.hour_fk_key,
1143 epp.duration_in_hours,
1144 epp.creation_date,
1145 epp.last_update_date,
1146 epp.creation_system_id,
1147 epp.last_update_system_id,
1148 epp.created_by,
1149 epp.last_updated_by,
1150 epp.last_update_login,
1151 epp.equip_prod_perf_fk_key);
1152
1153 mth_util_pkg.log_msg('Number of rows merged in MTH_EQUIP_PROD_PERF_DETAIL_F - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1154
1155 --Call run log post load
1156 MTH_UTIL_PKG.MTH_RUN_LOG_POST_LOAD(
1157 P_FACT_TABLE => v_fact_table_name,
1158 P_DB_GLOBAL_NAME => MTH_UTIL_PKG.MTH_UA_GET_VAL());
1159
1160
1161 mth_util_pkg.log_msg('POPULATE_PROD_PERF_DETAIL end', mth_util_pkg.G_DBG_PROC_FUN_END);
1162
1163 EXCEPTION
1164 WHEN OTHERS THEN
1165 --Call logging API and then throw exception
1166 mth_util_pkg.log_msg('Exception OTHERS in update_EPP_from_status', mth_util_pkg.G_DBG_EXCEPTION);
1167 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
1168 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
1169
1170 END populate_prod_perf_detail;
1171
1172 /* ****************************************************************************
1173 * Procedure :populate_prod_sust_detail *
1174 * Description : Populate data for equipments into prodcution sustain fact *
1175 * table. *
1176 * File Name :MTHPDPB.PLS *
1177 * Visibility :Public *
1178 * Parameters :None *
1179 * Return Value :None *
1180 **************************************************************************** */
1181 PROCEDURE populate_prod_sust_detail(p_run_mode IN VARCHAR2)
1182 IS
1183 v_fact_table_name VARCHAR2(100);
1184 v_log_date DATE;
1185 v_ua_val VARCHAR2(100);
1186
1187 BEGIN
1188 --Initialize parameters
1189 v_fact_table_name := 'MTH_EQUIP_PROD_SUSTAIN_F';
1190 v_log_date := SYSDATE;
1191 v_ua_val := mth_util_pkg.mth_ua_get_val();
1192
1193 mth_util_pkg.log_msg('POPULATE_PROD_SUST_DETAIL start', mth_util_pkg.G_DBG_PROC_FUN_START);
1194
1195 --Call run log pre load
1196 IF (p_run_mode = 'INIT')
1197 THEN
1198 MTH_UTIL_PKG.MTH_RUN_LOG_PRE_LOAD(
1199 P_FACT_TABLE => v_fact_table_name,
1200 P_DB_GLOBAL_NAME => v_ua_val,
1201 P_RUN_MODE => 'INITIAL',
1202 P_RUN_START_DATE => NULL,
1203 P_IS_FACT => 0,
1204 P_TO_DATE => SYSDATE);
1205 ELSE
1206 MTH_UTIL_PKG.MTH_RUN_LOG_PRE_LOAD(
1207 P_FACT_TABLE => v_fact_table_name,
1208 P_DB_GLOBAL_NAME => v_ua_val,
1209 P_RUN_MODE => 'INCR',
1210 P_RUN_START_DATE => NULL,
1211 P_IS_FACT => 0,
1212 P_TO_DATE => SYSDATE);
1213 END IF;
1214
1215 --Call the merge statement
1216 MERGE
1217 INTO
1218 MTH_EQUIP_PROD_SUSTAIN_F epsf
1219 USING
1220 (
1221 SELECT epp_sust_aspect.epp_detail_pk_key epp_detail_fk_key,
1222 epp_sust_aspect.sustain_aspect_fk_key,
1223 Sum(epp_sust_aspect.usage_value *
1224 (Least(epp_sust_aspect.epp_to_time,epp_sust_aspect.esa_to_time) -
1225 Greatest(epp_sust_aspect.epp_from_time,epp_sust_aspect.esa_from_time) + 1/86400) /
1226 (epp_sust_aspect.esa_to_time - epp_sust_aspect.esa_from_time + 1/86400) +
1227 Nvl(eps.sustain_aspect_usage_value,0)) sustain_aspect_usage_value,
1228 Max(epp_sust_aspect.usage_uom) sustain_aspect_uom,
1229 Sum(epp_sust_aspect.usage_cost *
1230 (Least(epp_sust_aspect.epp_to_time,epp_sust_aspect.esa_to_time) -
1231 Greatest(epp_sust_aspect.epp_from_time,epp_sust_aspect.esa_from_time) + 1/86400) /
1232 (epp_sust_aspect.esa_to_time - epp_sust_aspect.esa_from_time + 1/86400) +
1233 Nvl(eps.sustain_aspect_usage_cost,0)) sustain_aspect_usage_cost,
1234 epp_sust_aspect.equip_prod_perf_fk_key
1235 FROM mth_equip_prod_sustain_f eps,
1236 (SELECT epp.epp_detail_pk_key,
1237 esa.sustain_aspect_fk_key,
1238 esa.from_time esa_from_time,
1239 esa.to_time esa_to_time,
1240 esa.usage_value,
1241 esa.usage_cost,
1242 epp.from_time epp_from_time,
1243 epp.to_time epp_to_time,
1244 esa.usage_uom usage_uom,
1245 epp.equip_prod_perf_fk_key equip_prod_perf_fk_key
1246 FROM mth_equip_prod_perf_detail_f epp,
1247 mth_entity_sustain_aspect esa,
1248 (SELECT DISTINCT from_date,
1249 To_Date
1250 FROM mth_run_log
1251 WHERE fact_table = 'MTH_EQUIP_PROD_SUSTAIN_F') mrl
1252 WHERE ((esa.last_update_date > mrl.from_date AND esa.last_update_date <= mrl.To_Date)
1253 OR (epp.last_update_date > mrl.from_date AND epp.last_update_date <= mrl.To_Date))
1254 AND esa.entity_fk_key = epp.equipment_fk_key
1255 AND esa.entity_type IN ('Equipment', 'EQUIPMENT')
1256 AND ((epp.from_time >= esa.from_time AND epp.from_time <= esa.to_time )
1257 OR ( epp.to_time >= esa.from_time AND epp.to_time <= esa.to_time )
1258 OR ( esa.from_time >= epp.from_time AND esa.from_time <= epp.to_time ))
1259 AND esa.hour_fk_key = epp.hour_fk_key) epp_sust_aspect
1260 WHERE epp_sust_aspect.epp_detail_pk_key = eps.epp_detail_fk_key (+)
1261 AND epp_sust_aspect.sustain_aspect_fk_key = eps.sustain_aspect_fk_key(+)
1262 GROUP BY epp_sust_aspect.equip_prod_perf_fk_key,
1263 epp_sust_aspect.epp_detail_pk_key,
1264 epp_sust_aspect.sustain_aspect_fk_key) merge_subquery
1265 ON
1266 ( epsf.epp_detail_fk_key = merge_subquery.epp_detail_fk_key
1267 AND epsf.sustain_aspect_fk_key = merge_subquery.sustain_aspect_fk_key
1268 AND epsf.equip_prod_perf_fk_key = merge_subquery.equip_prod_perf_fk_key)
1269 WHEN MATCHED THEN
1270 UPDATE
1271 SET
1272 epsf.sustain_aspect_usage_value = merge_subquery.sustain_aspect_usage_value,
1273 epsf.sustain_aspect_uom = merge_subquery.sustain_aspect_uom,
1274 epsf.sustain_aspect_usage_cost = merge_subquery.sustain_aspect_usage_cost,
1275 epsf.last_update_date = v_log_date,
1276 epsf.last_update_system_id = v_ua_val,
1277 epsf.last_updated_by = v_ua_val,
1278 epsf.last_update_login = v_ua_val
1279
1280 WHEN NOT MATCHED THEN
1281 INSERT
1282 (epsf.epp_detail_fk_key,
1283 epsf.sustain_aspect_fk_key,
1284 epsf.sustain_aspect_usage_value,
1285 epsf.sustain_aspect_uom,
1286 epsf.sustain_aspect_usage_cost,
1287 epsf.creation_date,
1288 epsf.last_update_date,
1289 epsf.creation_system_id,
1290 epsf.last_update_system_id,
1291 epsf.created_by,
1292 epsf.last_updated_by,
1293 epsf.last_update_login,
1294 epsf.equip_prod_perf_fk_key)
1295 VALUES
1296 (merge_subquery.epp_detail_fk_key,
1297 merge_subquery.sustain_aspect_fk_key,
1298 merge_subquery.sustain_aspect_usage_value,
1299 merge_subquery.sustain_aspect_uom,
1300 merge_subquery.sustain_aspect_usage_cost,
1301 v_log_date,
1302 v_log_date,
1303 v_ua_val,
1304 v_ua_val,
1305 v_ua_val,
1306 v_ua_val,
1307 v_ua_val,
1308 merge_subquery.equip_prod_perf_fk_key);
1309 mth_util_pkg.log_msg('Number of rows merged in MTH_EQUIP_PROD_SUSTAIN_F - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1310
1311 --Call run log post load
1312 MTH_UTIL_PKG.MTH_RUN_LOG_POST_LOAD(
1313 P_FACT_TABLE => v_fact_table_name,
1314 P_DB_GLOBAL_NAME => v_ua_val);
1315
1316
1317 mth_util_pkg.log_msg('POPULATE_PROD_SUST_DETAIL end', mth_util_pkg.G_DBG_PROC_FUN_END);
1318
1319 EXCEPTION
1320 WHEN OTHERS THEN
1321 --Call logging API and then throw exception
1322 mth_util_pkg.log_msg('Exception OTHERS in populate_prod_sust_detail', mth_util_pkg.G_DBG_EXCEPTION);
1323 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
1324 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
1325
1326
1327 END populate_prod_sust_detail;
1328
1329 END MTH_PROD_DATA_PROCESS_PKG;