[Home] [Help]
PACKAGE BODY: APPS.MTH_EVENT_GEN_PKG
Source
1 PACKAGE BODY MTH_EVENT_GEN_PKG AS
2 /*$Header: mthevgb.pls 120.3.12020000.1 2012/07/24 16:11:03 sasuren noship $*/
3
4 /* ****************************************************************************
5 * Procedure : MTH_GEN_EQP_DOWN_W_RZ_EVENT *
6 * Description :The new procedure MTH_GEN_EQP_DOWN_W_RZ_EVENT *
7 generates and handles Equipment down with reason code events from MTH_TAG_REASON_READINGS table.*
8 * File Name :MTHEVGB.PLS *
9 * Visibility :Private *
10 * Parameters : *
11 **************************************************************************** */
12
13 PROCEDURE MTH_GEN_EQP_DOWN_W_RZ_EVENT (v_log_from_date IN DATE) IS
14 cursor c_readings is SELECT TRR.REASON_TYPE REASON_TYPE,
15 TRR.EQUIPMENT_FK_KEY EQUIPMENT_FK_KEY,
16 FL.MEANING MEANING,
17 TRR.FROM_DATE FROM_DATE,
18 TRR.TO_DATE TO_DATE,
19 TRR.REASON_CODE REASON_CODE,
20 TRR.LAST_UPDATE_DATE LAST_UPDATE_DATE
21 FROM MTH_TAG_REASON_READINGS TRR ,FND_LOOKUPS FL
22 WHERE TRR.LAST_UPDATE_DATE >= v_log_from_date AND
23 TRR.REASON_TYPE = 1 AND
24 TRR.REASON_CODE = FL.LOOKUP_CODE And
25 FL.LOOKUP_TYPE = 'MTH_EQUIP_DOWNTIME_REASON' ;
26
27 r_readings c_readings%ROWTYPE ;
28 BEGIN
29 open c_readings;
30 loop
31 fetch c_readings into r_readings ;
32 exit when c_readings%NOTFOUND ;
33 MTH_EVENT_PKG.HANDLE_EVENT (r_readings.equipment_fk_key,'DOWN',NULL ,NULL ,r_readings.from_date,r_readings.reason_code,3 ,'Equipment is down due to reason "' || r_readings.meaning || '"');
34 end loop;
35 close c_readings ;
36 END MTH_GEN_EQP_DOWN_W_RZ_EVENT;
37 /* ****************************************************************************
38 * Procedure : MTH_GEN_EQP_IDLE_PRD_EVENT *
39 * Description :The new procedure MTH_GEN_EQP_IDLE_PRD_EVENT *
40 generates and handles Equipment idle over a period events*
41 * File Name :MTHEVGB.PLS *
42 * Visibility :Private *
43 * Parameters : *
44 **************************************************************************** */
45
46 PROCEDURE MTH_GEN_EQP_IDLE_PRD_EVENT (v_log_from_date IN DATE) IS
47 cursor c_readings is SELECT EQP_IDLE_PER.SHIFT_WORKDAY_FK_KEY SHIFT_WORKDAY_FK_KEY,
48 EQP_IDLE_PER.EQUIPMENT_FK_KEY EQUIPMENT_FK_KEY,
49 EQP_IDLE_PER.FROM_DATE FROM_DATE,
50 EQP_IDLE_PER.TO_DATE TO_DATE,
51 EQP_IDLE_PER.STATUS STATUS,
52 EQP_IDLE_PER.DURATION_IN_MINUTES DURATION_IN_MINUTES,
53 EQP_IDLE_PER.LAG_STATUS LAG_STATUS,
54 EQP_IDLE_PER.LEAD_STATUS LEAD_STATUS
55 FROM MTH_EVENTS EVENTS1,
56 (SELECT IDLE_PER.SHIFT_WORKDAY_FK_KEY SHIFT_WORKDAY_FK_KEY,
57 IDLE_PER.EQUIPMENT_FK_KEY EQUIPMENT_FK_KEY,
58 IDLE_PER.FROM_DATE FROM_DATE,
59 CASE WHEN (Lead( IDLE_PER.STATUS, 1) OVER(PARTITION BY IDLE_PER.EQUIPMENT_FK_KEY , IDLE_PER.SHIFT_WORKDAY_FK_KEY ORDER BY IDLE_PER.FROM_DATE )) IS NULL
60 THEN IDLE_PER.TO_DATE
61 WHEN (Lead( IDLE_PER.STATUS, 1) OVER(PARTITION BY IDLE_PER.EQUIPMENT_FK_KEY , IDLE_PER.SHIFT_WORKDAY_FK_KEY ORDER BY IDLE_PER.FROM_DATE )) = IDLE_PER.STATUS
62 THEN (Lead( IDLE_PER.TO_DATE, 1)OVER(PARTITION BY IDLE_PER.EQUIPMENT_FK_KEY , IDLE_PER.SHIFT_WORKDAY_FK_KEY ORDER BY IDLE_PER.FROM_DATE ))
63 WHEN (Lead( IDLE_PER.STATUS, 1) OVER(PARTITION BY IDLE_PER.EQUIPMENT_FK_KEY , IDLE_PER.SHIFT_WORKDAY_FK_KEY ORDER BY IDLE_PER.FROM_DATE )) <> IDLE_PER.STATUS
64 THEN (Lead(IDLE_PER.FROM_DATE , 1) OVER(PARTITION BY IDLE_PER.EQUIPMENT_FK_KEY , IDLE_PER.SHIFT_WORKDAY_FK_KEY ORDER BY IDLE_PER.FROM_DATE )) - 1/86400
65 ELSE IDLE_PER.TO_DATE
66 END TO_DATE,
67 IDLE_PER.STATUS STATUS,
68 CASE WHEN (Lead( IDLE_PER.STATUS, 1) OVER(PARTITION BY IDLE_PER.EQUIPMENT_FK_KEY , IDLE_PER.SHIFT_WORKDAY_FK_KEY ORDER BY IDLE_PER.FROM_DATE )) IS NULL
69 THEN IDLE_PER.DURATION_IN_MINUTES
70 WHEN (Lead( IDLE_PER.STATUS, 1) OVER(PARTITION BY IDLE_PER.EQUIPMENT_FK_KEY , IDLE_PER.SHIFT_WORKDAY_FK_KEY ORDER BY IDLE_PER.FROM_DATE )) = IDLE_PER.STATUS
71 THEN (Lead( IDLE_PER.RUNNING_TOTAL_DURATION_IN_MIN , 1) OVER(PARTITION BY IDLE_PER.EQUIPMENT_FK_KEY , IDLE_PER.SHIFT_WORKDAY_FK_KEY ORDER BY IDLE_PER.FROM_DATE ))
72 - IDLE_PER.RUNNING_TOTAL_DURATION_IN_MIN + IDLE_PER.DURATION_IN_MINUTES
73 WHEN (Lead( IDLE_PER.STATUS, 1) OVER(PARTITION BY IDLE_PER.EQUIPMENT_FK_KEY , IDLE_PER.SHIFT_WORKDAY_FK_KEY ORDER BY IDLE_PER.FROM_DATE )) <> IDLE_PER.STATUS
74 THEN (Lead( IDLE_PER.RUNNING_TOTAL_DURATION_IN_MIN , 1) OVER(PARTITION BY IDLE_PER.EQUIPMENT_FK_KEY , IDLE_PER.SHIFT_WORKDAY_FK_KEY ORDER BY IDLE_PER.FROM_DATE ))
75 - IDLE_PER.RUNNING_TOTAL_DURATION_IN_MIN + IDLE_PER.DURATION_IN_MINUTES-
79 IDLE_PER.LAG_STATUS LAG_STATUS,
76 (Lead( IDLE_PER.DURATION_IN_MINUTES , 1) OVER(PARTITION BY IDLE_PER.EQUIPMENT_FK_KEY , IDLE_PER.SHIFT_WORKDAY_FK_KEY ORDER BY IDLE_PER.FROM_DATE ))
77 ELSE IDLE_PER.DURATION_IN_MINUTES
78 END DURATION_IN_MINUTES,
80 IDLE_PER.LEAD_STATUS LEAD_STATUS
81 FROM (SELECT SHIFTS.SHIFT_WORKDAY_FK_KEY SHIFT_WORKDAY_FK_KEY,
82 SHIFTS.EQUIPMENT_FK_KEY EQUIPMENT_FK_KEY,
83 ESD.AVAILABILITY_FLAG AVAILABILITY_FLAG,
84 greatest ( ESD.FROM_DATE , ES.FROM_DATE) FROM_DATE,
85 LEAST(NVL( ES.TO_DATE, SYSDATE ), ESD.TO_DATE ) TO_DATE,
86 (LEAST(NVL( ES.TO_DATE, SYSDATE ), ESD.TO_DATE ) - (greatest ( ESD.FROM_DATE , ES.FROM_DATE) )) * 1440 + 1 / 60 DURATION_IN_MINUTES,
87 ES.STATUS STATUS,
88 LAG( ES.STATUS , 1) OVER(PARTITION BY SHIFTS.EQUIPMENT_FK_KEY , SHIFTS.SHIFT_WORKDAY_FK_KEY ORDER BY (greatest (ESD.FROM_DATE , ES.FROM_DATE )))LAG_STATUS,
89 Lead( ES.STATUS , 1) OVER(PARTITION BY SHIFTS.EQUIPMENT_FK_KEY , SHIFTS.SHIFT_WORKDAY_FK_KEY ORDER BY (greatest (ESD.FROM_DATE , ES.FROM_DATE )) ) LEAD_STATUS,
90 SUM( ( (LEAST(NVL( ES.TO_DATE, SYSDATE ),ESD.TO_DATE ))
91 - (greatest ( ESD.FROM_DATE , ES.FROM_DATE )) ) * 1440 + 1 / 60 )
92 OVER(PARTITION BY SHIFTS.EQUIPMENT_FK_KEY , SHIFTS.SHIFT_WORKDAY_FK_KEY ORDER BY (greatest ( ESD.FROM_DATE , ES.FROM_DATE )) ROWS UNBOUNDED PRECEDING) RUNNING_TOTAL_DURATION_IN_MIN
93 FROM ( SELECT AGG_INPUT.SHIFT_WORKDAY_FK_KEY SHIFT_WORKDAY_FK_KEY,
94 AGG_INPUT.EQUIPMENT_FK_KEY
95 FROM (SELECT ES.SHIFT_WORKDAY_FK_KEY SHIFT_WORKDAY_FK_KEY,
96 ES.FROM_DATE FROM_DATE,
97 ES.TO_DATE TO_DATE,
98 ES.STATUS STATUS,
99 ES.EQUIPMENT_FK_KEY EQUIPMENT_FK_KEY
100 FROM MTH_EQUIP_STATUSES ES
101 WHERE ES.LAST_UPDATE_DATE >= v_log_from_date Or ES.TO_DATE IS NULL ) AGG_INPUT
102 GROUP BY AGG_INPUT.EQUIPMENT_FK_KEY , AGG_INPUT.SHIFT_WORKDAY_FK_KEY ) SHIFTS ,
103 MTH_EQUIP_STATUSES ES,
104 MTH_EQUIPMENT_SHIFTS_D ESD
105 WHERE SHIFTS.SHIFT_WORKDAY_FK_KEY = ES.SHIFT_WORKDAY_FK_KEY AND
106 SHIFTS.EQUIPMENT_FK_KEY = ES.EQUIPMENT_FK_KEY AND
107 SHIFTS.SHIFT_WORKDAY_FK_KEY = ESD.SHIFT_WORKDAY_FK_KEY AND
108 SHIFTS.EQUIPMENT_FK_KEY =ESD.EQUIPMENT_FK_KEY AND
109 ( ES.FROM_DATE BETWEEN ESD.FROM_DATE AND ESD.TO_DATE Or ESD.FROM_DATE BETWEEN ES.FROM_DATE AND NVL (ES.TO_DATE , SYSDATE ))) IDLE_PER
110 WHERE IDLE_PER.LAG_STATUS <> IDLE_PER.STATUS or
111 IDLE_PER.LAG_STATUS IS NULL Or
112 IDLE_PER.LEAD_STATUS IS NULL ) EQP_IDLE_PER
113 WHERE EQP_IDLE_PER.STATUS = 2 AND
114 (EQP_IDLE_PER.LEAD_STATUS IS NOT NULL Or
115 EQP_IDLE_PER.LAG_STATUS IS NULL or
116 EQP_IDLE_PER.LEAD_STATUS IS NULL And
117 EQP_IDLE_PER.STATUS <> EQP_IDLE_PER.LAG_STATUS ) AND
118 EQP_IDLE_PER.DURATION_IN_MINUTES > v_dur_idle_limit_in_minutes and
119 EQP_IDLE_PER.EQUIPMENT_FK_KEY = EVENTS1.EQUIPMENT_FK_KEY (+) And
120 EQP_IDLE_PER.FROM_DATE = EVENTS1.TAG_READING_TIME (+) And
121 EQP_IDLE_PER.STATUS = EVENTS1.EQUIP_STATUS (+) And
122 EVENTS1.EVENT_ID IS NULL ;
123 r_readings c_readings%ROWTYPE ;
124
125 BEGIN
126 open c_readings;
127 loop
128 fetch c_readings into r_readings ;
129 exit when c_readings%NOTFOUND ;
130 MTH_EVENT_PKG.HANDLE_EVENT (r_readings.equipment_fk_key,'IDLE',NULL,NULL,r_readings.from_date ,NULL,To_Number(r_readings.status) ,'Equipment is idle for more than ' || v_dur_idle_limit_in_minutes || ' minutes');
131 end loop;
132 close c_readings ;
133 END ;
134
135 /* ****************************************************************************
136 * Procedure : MTH_GEN_EQP_DWN_PRD_EVENT *
137 * Description :The new procedure MTH_GEN_EQP_DWN_PRD_EVENT *
138 generates and handles Equipment down over a period events*
139 * File Name :MTHEVGB.PLS *
140 * Visibility :Private *
141 * Parameters : *
142 **************************************************************************** */
143
144
145 PROCEDURE MTH_GEN_EQP_DWN_PRD_EVENT (v_log_from_date IN DATE) IS
146 --v_dur_down_limit_in_minutes NUMBER :=15;
147 cursor c_readings is SELECT equip_down_pd.SHIFT_WORKDAY_FK_KEY SHIFT_WORKDAY_FK_KEY,
148 equip_down_pd.EQUIPMENT_FK_KEY EQUIPMENT_FK_KEY,
149 equip_down_pd.FROM_DATE FROM_DATE,
150 equip_down_pd.TO_DATE TO_DATE,
151 equip_down_pd.STATUS STATUS,
155 FROM MTH_EVENTS EVENTS1, (SELECT RNA.SHIFT_WORKDAY_FK_KEY SHIFT_WORKDAY_FK_KEY,
152 equip_down_pd.LAG_STATUS LAG_STATUS,
153 equip_down_pd.LEAD_STATUS LEAD_STATUS,
154 equip_down_pd.DURATION_IN_MINUTES DURATION_IN_MINUTES
156 RNA.EQUIPMENT_FK_KEY EQUIPMENT_FK_KEY,
157 RNA.FROM_DATE FROM_DATE,
158 CASE WHEN (Lead( RNA.STATUS, 1) OVER(PARTITION BY RNA.EQUIPMENT_FK_KEY,RNA.SHIFT_WORKDAY_FK_KEY ORDER BY RNA.FROM_DATE )) IS NULL
159 THEN RNA.TO_DATE
160 WHEN (Lead( RNA.STATUS, 1) OVER(PARTITION BY RNA.EQUIPMENT_FK_KEY ,RNA.SHIFT_WORKDAY_FK_KEY ORDER BY RNA.FROM_DATE )) = RNA.STATUS
161 THEN (Lead( RNA.TO_DATE , 1) OVER(PARTITION BY RNA.EQUIPMENT_FK_KEY,RNA.SHIFT_WORKDAY_FK_KEY ORDER BY RNA.FROM_DATE))
162 WHEN (Lead( RNA.STATUS , 1) OVER(PARTITION BY RNA.EQUIPMENT_FK_KEY, RNA.SHIFT_WORKDAY_FK_KEY ORDER BY RNA.FROM_DATE )) <> RNA.STATUS
163 THEN (Lead(RNA.FROM_DATE , 1) OVER(PARTITION BY RNA.EQUIPMENT_FK_KEY ,RNA.SHIFT_WORKDAY_FK_KEY ORDER BY RNA.FROM_DATE )) - 1/86400
164 ELSE RNA.TO_DATE
165 END TO_DATE,
166 RNA.STATUS STATUS,
167 RNA.LAG_STATUS LAG_STATUS,
168 RNA.LEAD_STATUS LEAD_STATUS,
169 CASE WHEN (Lead(RNA.STATUS , 1) OVER(PARTITION BY RNA.EQUIPMENT_FK_KEY ,RNA.SHIFT_WORKDAY_FK_KEY ORDER BY RNA.FROM_DATE )) IS NULL
170 THEN RNA.DURATION_IN_MINUTES
171 WHEN (Lead( RNA.STATUS , 1) OVER(PARTITION BY RNA.EQUIPMENT_FK_KEY,RNA.SHIFT_WORKDAY_FK_KEY ORDER BY RNA.FROM_DATE )) =RNA.STATUS
172 THEN (Lead( RNA.DURATION_RUNNING_TOTAL , 1)
173 OVER(PARTITION BY RNA.EQUIPMENT_FK_KEY,RNA.SHIFT_WORKDAY_FK_KEY ORDER BY RNA.FROM_DATE ))
174 - RNA.DURATION_RUNNING_TOTAL + RNA.DURATION_IN_MINUTES
175 WHEN (Lead(RNA.STATUS , 1) OVER(PARTITION BY RNA.EQUIPMENT_FK_KEY ,RNA.SHIFT_WORKDAY_FK_KEY ORDER BY RNA.FROM_DATE )) <> RNA.STATUS
176 THEN (Lead( RNA.DURATION_RUNNING_TOTAL , 1)
177 OVER(PARTITION BY RNA.EQUIPMENT_FK_KEY, RNA.SHIFT_WORKDAY_FK_KEY
178 ORDER BY RNA.FROM_DATE )) - RNA.DURATION_RUNNING_TOTAL + RNA.DURATION_IN_MINUTES
179 - (Lead( RNA.DURATION_IN_MINUTES , 1) OVER(PARTITION BY RNA.EQUIPMENT_FK_KEY,RNA.SHIFT_WORKDAY_FK_KEY ORDER BY RNA.FROM_DATE ))
180 ELSE RNA.DURATION_IN_MINUTES
181 END DURATION_IN_MINUTES
182 FROM (SELECT ROWS_IN_SHIFTS.SHIFT_WORKDAY_FK_KEY SHIFT_WORKDAY_FK_KEY,
183 ROWS_IN_SHIFTS.EQUIPMENT_FK_KEY EQUIPMENT_FK_KEY,
184 ROWS_IN_SHIFTS.AVAILABILITY_FLAG AVAILABILITY_FLAG,
185 ROWS_IN_SHIFTS.FROM_DATE FROM_DATE,
186 ROWS_IN_SHIFTS.TO_DATE TO_DATE,
187 ROWS_IN_SHIFTS.DURATION_IN_MINUTES DURATION_IN_MINUTES,
188 ROWS_IN_SHIFTS.STATUS STATUS,
189 LAG( ROWS_IN_SHIFTS.STATUS , 1) OVER(PARTITION BY ROWS_IN_SHIFTS.EQUIPMENT_FK_KEY , ROWS_IN_SHIFTS.SHIFT_WORKDAY_FK_KEY ORDER BY ROWS_IN_SHIFTS.FROM_DATE ) LAG_STATUS,
190 LEAD( ROWS_IN_SHIFTS.STATUS , 1) OVER(PARTITION BY ROWS_IN_SHIFTS.EQUIPMENT_FK_KEY ,ROWS_IN_SHIFTS.SHIFT_WORKDAY_FK_KEY ORDER BY ROWS_IN_SHIFTS.FROM_DATE ) LEAD_STATUS,
191 SUM( (ROWS_IN_SHIFTS.TO_DATE - ROWS_IN_SHIFTS.FROM_DATE ) * 1440 + 1 / 60 )
192 OVER(PARTITION BY ROWS_IN_SHIFTS.EQUIPMENT_FK_KEY , ROWS_IN_SHIFTS.SHIFT_WORKDAY_FK_KEY
193 ORDER BY ROWS_IN_SHIFTS.FROM_DATE) DURATION_RUNNING_TOTAL
194 FROM (SELECT SHIFTS.SHIFT_WORKDAY_FK_KEY SHIFT_WORKDAY_FK_KEY,
195 SHIFTS.EQUIPMENT_FK_KEY EQUIPMENT_FK_KEY,
196 ESD.AVAILABILITY_FLAG AVAILABILITY_FLAG,
197 greatest ( ESD.FROM_DATE , ES.FROM_DATE) FROM_DATE,
198 LEAST(NVL( ES.TO_DATE, SYSDATE ), ESD.TO_DATE ) TO_DATE,
199 (LEAST(NVL( ES.TO_DATE, SYSDATE ), ESD.TO_DATE ) - (greatest ( ESD.FROM_DATE , ES.FROM_DATE) )) * 1440 + 1 / 60 DURATION_IN_MINUTES,
200 ES.STATUS STATUS
201 FROM ( SELECT AGG_INPUT.SHIFT_WORKDAY_FK_KEY SHIFT_WORKDAY_FK_KEY,
202 AGG_INPUT.EQUIPMENT_FK_KEY
203 FROM (SELECT ES.SHIFT_WORKDAY_FK_KEY SHIFT_WORKDAY_FK_KEY,
204 ES.FROM_DATE FROM_DATE,
205 ES.TO_DATE TO_DATE,
206 ES.STATUS STATUS,
207 ES.EQUIPMENT_FK_KEY EQUIPMENT_FK_KEY
208 FROM MTH_EQUIP_STATUSES ES
209 WHERE ES.LAST_UPDATE_DATE >= v_log_from_date Or ES.TO_DATE IS NULL ) AGG_INPUT
210 GROUP BY AGG_INPUT.EQUIPMENT_FK_KEY ,
211 AGG_INPUT.SHIFT_WORKDAY_FK_KEY ) SHIFTS ,
212 MTH_EQUIP_STATUSES ES ,
213 MTH_EQUIPMENT_SHIFTS_D ESD
214 WHERE SHIFTS.SHIFT_WORKDAY_FK_KEY = ES.SHIFT_WORKDAY_FK_KEY AND
215 SHIFTS.EQUIPMENT_FK_KEY = ES.EQUIPMENT_FK_KEY AND
216 SHIFTS.SHIFT_WORKDAY_FK_KEY = ESD.SHIFT_WORKDAY_FK_KEY AND
217 SHIFTS.EQUIPMENT_FK_KEY =ESD.EQUIPMENT_FK_KEY AND
218 ( ES.FROM_DATE BETWEEN ESD.FROM_DATE AND ESD.TO_DATE Or ESD.FROM_DATE BETWEEN ES.FROM_DATE AND NVL (ES.TO_DATE , SYSDATE ) )) ROWS_IN_SHIFTS
219 WHERE ROWS_IN_SHIFTS.AVAILABILITY_FLAG IS NOT NULL AND
220 (ROWS_IN_SHIFTS.AVAILABILITY_FLAG = 'Y' Or
221 ROWS_IN_SHIFTS.STATUS <> 3) )RNA
222 WHERE RNA.LAG_STATUS <> RNA.STATUS Or
223 RNA.LAG_STATUS IS NULL Or
224 RNA.LEAD_STATUS IS NULL ) equip_down_pd
225 WHERE equip_down_pd.STATUS = 3 AND
226 (equip_down_pd.LEAD_STATUS IS NOT NULL Or
227 equip_down_pd.LAG_STATUS IS NULL or
228 equip_down_pd.LEAD_STATUS IS NULL And
229 equip_down_pd.STATUS <> equip_down_pd.LAG_STATUS ) AND
230 equip_down_pd.DURATION_IN_MINUTES >v_dur_down_limit_in_minutes and
231 equip_down_pd.EQUIPMENT_FK_KEY = events1.EQUIPMENT_FK_KEY (+) And
232 equip_down_pd.FROM_DATE = events1.TAG_READING_TIME (+) And
233 equip_down_pd.STATUS = events1.EQUIP_STATUS (+) AND
234 'DOW_PD' = events1.EVENT_TYPE (+) AND
235 events1.EVENT_ID IS NULL ;
236
237 r_readings c_readings%ROWTYPE ;
238
239 BEGIN
240
241 open c_readings;
242 loop
243 fetch c_readings into r_readings ;
244 exit when c_readings%NOTFOUND ;
245 MTH_EVENT_PKG.HANDLE_EVENT (r_readings.equipment_fk_key,'DOW_PD',NULL,NULL,r_readings.from_date ,NULL,To_Number(r_readings.status) ,'Equipment is down for more than ' ||v_dur_down_limit_in_minutes || ' minutes');
246 end loop;
247 close c_readings ;
248 END ;
249 /* ****************************************************************************
250 * Procedure : MTH_GEN_HIGH_SCRAP_EVENT *
251 * Description :The new procedure MTH_GEN_HIGH_SCRAP_EVENT *
252 generates and handles High Scrap events.*
253 * File Name MTHEVGB.PLS *
254 * Visibility :Private *
255 * Parameters : *
256 **************************************************************************** */
257
258 PROCEDURE MTH_GEN_HIGH_SCRAP_EVENT (v_log_from_date IN DATE) IS
259 --v_hi_scrap_threshold NUMBER :=10;
260 cursor c_readings is SELECT OS.EQUIPMENT_FK_KEY EQUIPMENT_FK_KEY,
261 OS.SHIFT_WORKDAY_FK_KEY SHIFT_WORKDAY_FK_KEY,
262 OS.WORKORDER_FK_KEY WORKORDER_FK_KEY,
263 OS.TOTAL_SCRAP QTY_SCRAP,
264 OS.TOTAL_REWORK QTY_REWORK,
265 OS.TOTAL_OUTPUT QTY_OUTPUT
266 FROM MTH_EVENTS EVENTS1, (SELECT EOS.EQUIPMENT_FK_KEY ,
267 EOS.SHIFT_WORKDAY_FK_KEY,
268 EOS.WORKORDER_FK_KEY,
269 SUM(EOS.QTY_SCRAP) TOTAL_SCRAP,
270 SUM(EOS.QTY_REWORK) TOTAL_REWORK,
271 SUM(EOS.QTY_OUTPUT) TOTAL_OUTPUT
272 FROM ( SELECT MEOS.EQUIPMENT_FK_KEY ,
273 MEOS.SHIFT_WORKDAY_FK_KEY,
274 MEOS.WORKORDER_FK_KEY,
275 MEOS.QTY_SCRAP,
276 MEOS.QTY_REWORK ,
277 MEOS.QTY_OUTPUT
278 FROM MTH_EQUIP_OUTPUT_SUMMARY MEOS
279 WHERE MEOS.LAST_UPDATE_DATE >=v_log_from_date
280 )EOS
281 GROUP BY EOS.EQUIPMENT_FK_KEY ,
282 EOS.SHIFT_WORKDAY_FK_KEY,
283 EOS.WORKORDER_FK_KEY
284 )OS
285 WHERE OS.TOTAL_SCRAP IS NOT NULL And
286 OS.TOTAL_OUTPUT IS NOT NULL And
287 OS.TOTAL_SCRAP > 0 And
288 OS.TOTAL_OUTPUT > 0 And
289 OS.TOTAL_SCRAP / OS.TOTAL_OUTPUT * 100 > v_hi_scrap_threshold AND
290 OS.EQUIPMENT_FK_KEY = EVENTS1.EQUIPMENT_FK_KEY (+) And
291 OS.SHIFT_WORKDAY_FK_KEY = EVENTS1.SHIFT_WORKDAY_FK_KEY (+) And
292 OS.WORKORDER_FK_KEY = EVENTS1.WORKORDER_FK_KEY (+) And
293 EVENTS1.EVENT_ID IS NULL And 'HI_SCRP' = EVENTS1.EVENT_TYPE (+);
294 r_readings c_readings%ROWTYPE ;
295
296 BEGIN
297
298 open c_readings;
299 loop
300 fetch c_readings into r_readings ;
301 exit when c_readings%NOTFOUND ;
302 MTH_EVENT_PKG.HANDLE_EVENT (r_readings.equipment_fk_key,'HI_SCRP',r_readings.shift_workday_fk_key,r_readings.workorder_fk_key,NULL ,NULL,NULL ,'Scrap % is greater than ' || v_hi_scrap_threshold || '%');
303 end loop;
304 close c_readings ;
305 END ;
306 /* ****************************************************************************
307 * Procedure : MTH_GEN_HIGH_REWORK_EVENT *
308 * Description :The new procedure MTH_GEN_HIGH_REWORK_EVENT *
309 generates and handles High Rework events*
310 * File Name :MTHEVGB.PLS *
311 * Visibility :Private *
312 * Parameters : *
313 **************************************************************************** */
314
318 OS.SHIFT_WORKDAY_FK_KEY SHIFT_WORKDAY_FK_KEY,
315 PROCEDURE MTH_GEN_HIGH_REWORK_EVENT (v_log_from_date IN DATE) IS
316 --v_hi_rework_threshold NUMBER := 10;
317 cursor c_readings is SELECT OS.EQUIPMENT_FK_KEY EQUIPMENT_FK_KEY,
319 OS.WORKORDER_FK_KEY WORKORDER_FK_KEY,
320 OS.TOTAL_SCRAP QTY_SCRAP,
321 OS.TOTAL_REWORK QTY_REWORK,
322 OS.TOTAL_OUTPUT QTY_OUTPUT
323 FROM MTH_EVENTS EVENTS1, (SELECT EOS.EQUIPMENT_FK_KEY ,
324 EOS.SHIFT_WORKDAY_FK_KEY,
325 EOS.WORKORDER_FK_KEY,
326 SUM(EOS.QTY_SCRAP) TOTAL_SCRAP,
327 SUM(EOS.QTY_REWORK) TOTAL_REWORK,
328 SUM(EOS.QTY_OUTPUT) TOTAL_OUTPUT
329 FROM ( SELECT MEOS.EQUIPMENT_FK_KEY ,
330 MEOS.SHIFT_WORKDAY_FK_KEY,
331 MEOS.WORKORDER_FK_KEY,
332 MEOS.QTY_SCRAP,
333 MEOS.QTY_REWORK ,
334 MEOS.QTY_OUTPUT
335 FROM MTH_EQUIP_OUTPUT_SUMMARY MEOS
336 WHERE MEOS.LAST_UPDATE_DATE >=v_log_from_date
337 )EOS
338 GROUP BY EOS.EQUIPMENT_FK_KEY ,
339 EOS.SHIFT_WORKDAY_FK_KEY,
340 EOS.WORKORDER_FK_KEY
341 )OS
342 WHERE OS.TOTAL_REWORK IS NOT NULL And
343 OS.TOTAL_OUTPUT IS NOT NULL And
344 OS.TOTAL_REWORK > 0 And
345 OS.TOTAL_OUTPUT > 0 And
346 OS.TOTAL_REWORK / OS.TOTAL_OUTPUT * 100 > v_hi_rework_threshold AND
347 OS.EQUIPMENT_FK_KEY = EVENTS1.EQUIPMENT_FK_KEY (+) And
348 OS.SHIFT_WORKDAY_FK_KEY = EVENTS1.SHIFT_WORKDAY_FK_KEY (+) And
349 OS.WORKORDER_FK_KEY = EVENTS1.WORKORDER_FK_KEY (+) And
350 EVENTS1.EVENT_ID IS NULL And 'HI_RWRK' = EVENTS1.EVENT_TYPE (+);
351
352 r_readings c_readings%ROWTYPE ;
353
354 BEGIN
355
356 open c_readings;
357 loop
358 fetch c_readings into r_readings ;
359 exit when c_readings%NOTFOUND ;
360 MTH_EVENT_PKG.HANDLE_EVENT (r_readings.equipment_fk_key,'HI_RWRK',r_readings.shift_workday_fk_key,r_readings.workorder_fk_key,NULL ,NULL,NULL ,'Rework % is greater than ' || v_hi_rework_threshold || '%');
361 end loop;
362 close c_readings ;
363 END ;
364
365 /* ****************************************************************************
366 * Procedure : MTH_GEN_HDL_EVTS_FROM_STS_OUT *
367 * Description :The new procedure MTH_GEN_HDL_EVTS_FROM_STS_OUT *
368 generates and handles events from MTH_EQUIP_STATUSES, MTH_TAG_REASON_READINGS, and MTH_EQUIP_OUTPUT_SUMMARY*
369 * File Name :MTHEVGB.PLS *
370 * Visibility :Private *
371 * Parameters : *
372 **************************************************************************** */
373
374 PROCEDURE MTH_GEN_HDL_EVTS_FROM_STS_OUT (p_mode IN VARCHAR2,
375 p_ret_code OUT NOCOPY NUMBER
376 )IS
377 v_log_from_date DATE;
378 v_log_to_date DATE;
379 v_unassigned_val VARCHAR2(30);
380 v_count number;
381 v_desc VARCHAR2(100);
382 l_sql_stmt VARCHAR2(1000);
383 l_ret_code NUMBER;
384 BEGIN
385 l_ret_code := 0;
386 mth_util_pkg.log_msg('GEN_HDL_EVENTS_FROM_STS_OUTPUT start', mth_util_pkg.G_DBG_PROC_FUN_START);
387 p_ret_code := l_ret_code;
388 -- Initialize default parameters
389 v_log_to_date := sysdate;
390 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
391
392 IF p_mode = 'INIT' THEN
393 mth_util_pkg.mth_run_log_pre_load('MTH_EVENT_RULE_TABLES',v_unassigned_val,'INITIAL',NULL,0,v_log_to_date);
394
395 ELSE
396 mth_util_pkg.mth_run_log_pre_load('MTH_EVENT_RULE_TABLES',v_unassigned_val,'INCR',NULL,0,v_log_to_date);
397
398 END IF;
399 -- Call mth_run_log_pre_load
400 --mth_util_pkg.mth_run_log_pre_load('MTH_EVENT_RULE_TABLES',v_unassigned_val,NULL,NULL,0,v_log_to_date);
401
402 -- Call GET_RUN_LOG_DATES
403 mth_util_pkg.GET_RUN_LOG_DATES('MTH_EVENT_RULE_TABLES',NULL,NULL,NULL,v_log_from_date,v_log_to_date);
404
405 MTH_GEN_EQP_DOWN_W_RZ_EVENT(v_log_from_date);
406 MTH_GEN_EQP_IDLE_PRD_EVENT (v_log_from_date) ;
407 MTH_GEN_EQP_DWN_PRD_EVENT (v_log_from_date);
408 MTH_GEN_HIGH_SCRAP_EVENT (v_log_from_date) ;
409 MTH_GEN_HIGH_REWORK_EVENT (v_log_from_date);
410
411
412 SELECT Count(*) INTO v_count FROM FND_LOOKUPS B WHERE B.LOOKUP_TYPE IN ('MTH_EVENT_GEN_API');
413 IF (v_count>0) THEN
414 SELECT B.DESCRIPTION INTO v_desc FROM FND_LOOKUPS B WHERE B.LOOKUP_TYPE IN ('MTH_EVENT_GEN_API');
415 l_sql_stmt:= 'BEGIN ' || v_desc || '; END;';
416 BEGIN
417 IF(v_desc IS NOT NULL )THEN
418 EXECUTE IMMEDIATE l_sql_stmt;
419 END IF;
420 EXCEPTION
421 WHEN OTHERS THEN
422 mth_util_pkg.log_msg('Exception while calling custom hook - ' || v_desc, mth_util_pkg.G_DBG_EXCEPTION);
423 END;
424 END IF;
425
426 mth_util_pkg.mth_run_log_post_load('MTH_EVENT_RULE_TABLES',v_unassigned_val);
427 mth_util_pkg.log_msg('GEN_HDL_EVENTS_FROM_STS_OUTPUT end', mth_util_pkg.G_DBG_PROC_FUN_END);
428 EXCEPTION
429 WHEN OTHERS THEN
430 l_ret_code := 2;
431 mth_util_pkg.log_msg('Exception OTHERS in GEN_HDL_EVENTS_FROM_STS_OUTPUT', mth_util_pkg.G_DBG_EXCEPTION);
432 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
433 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
434 RAISE;
435 END MTH_GEN_HDL_EVTS_FROM_STS_OUT ;
436 END MTH_EVENT_GEN_PKG;