[Home] [Help]
PACKAGE BODY: APPS.MTH_PROCESS_OUTPUT_PKG
Source
4 PROCEDURE INIT_OUTPUT_FROM_READING;
1 PACKAGE BODY MTH_PROCESS_OUTPUT_PKG AS
2 /*$Header: mtheqopb.pls 120.12.12020000.2 2012/08/27 08:02:01 aksachde noship $*/
3
5 PROCEDURE INCR_OUTPUT_FROM_READING;
6 PROCEDURE RECAL_OUTPUT_FROM_READING(p_recal_from_date IN DATE, --Recalculation from date
7 p_recal_to_date IN DATE DEFAULT NULL, --Recalculation to date
8 p_equipment_pk_key IN NUMBER DEFAULT NULL); --Equipment to recalculate
9
10 PROCEDURE INIT_OUTPUT_FROM_CSV;
11 PROCEDURE INCR_OUTPUT_FROM_CSV;
12 PROCEDURE RECAL_OUTPUT_FROM_CSV(p_recal_from_date IN DATE, --Recalculation from date
13 p_recal_to_date IN DATE DEFAULT NULL, --Recalculation to date
14 p_equipment_pk_key IN NUMBER DEFAULT NULL,--Equipment for recalculation
15 p_plant_pk_key IN NUMBER DEFAULT NULL);--Plant for recalculation
16
17 PROCEDURE PROCESS_OUTPUT_SMMRY_INIT;
18 PROCEDURE PROCESS_OUTPUT_SMMRY_INCR;
19 PROCEDURE PROCESS_OUTPUT_SMMRY_RECAL(p_recalc_from_date IN TIMESTAMP, --Recalculation from date
20 p_recalc_to_date IN TIMESTAMP, --Recalculation to date
21 p_recalc_equip_key IN NUMBER, --Equipment for recalculation
22 p_recalc_plant_key IN NUMBER);--Plant for recalculation
23
24 /*******************************************************************************
25 * Procedure :VALIDATE *
26 * Description :This procedure is the validate procedure *
27 * File Name :MTHEQOPB.PLS *
28 * Visibility :Public *
29 * Parameters : p_err_buff : Error message *
30 * p_retcode : Error Code *
31 * Modification log : *
32 * Author Date Change *
33 * Mandar Gijare 13-Sep-2011 Initial Creation *
34 *******************************************************************************/
35 PROCEDURE VALIDATE(
36 p_mode IN VARCHAR2,
37 p_source IN VARCHAR2,
38 p_retcode OUT NOCOPY NUMBER,
39 p_err_buff OUT NOCOPY VARCHAR2
40 )
41 IS
42 v_retcode NUMBER DEFAULT 0;
43 v_tag_reading_count NUMBER;
44 v_output_count NUMBER;
45 TYPE compileState IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
46 v_compile_state compileState;
47 v_msg VARCHAR2(300);
48 i NUMBER;
49 BEGIN
50
51 IF( (p_mode = 'RECAL') AND (p_source = 'TAG') ) THEN
52 SELECT Count(1)
53 INTO v_tag_reading_count
54 FROM mth_tag_readings mtr,
55 mth_entities mte,
56 mth_run_log mrl
57 WHERE mtr.mth_entity = mte.id
58 AND mte.mth_alias IN ('Scrap Quantity',
59 'Rejected Quantity',
60 'Output Quantity',
61 'Rework Quantity',
62 'Completed Quantity')
63 AND mrl.fact_table = 'MTH_EQUIP_OUTPUT'
64 AND mtr.last_update_date > mrl.from_date;
65
66 IF v_tag_reading_count > 0 THEN
67 mth_util_pkg.log_msg('All data from tag reading is still not populated to output table.',mth_util_pkg.G_DBG_EXCEPTION);
68 v_retcode := 2;
69 END IF;
70 END IF;
71
72 IF( p_mode = 'RECAL' ) THEN
73 SELECT Count(1)
74 INTO v_output_count
75 FROM mth_equip_output meo,
76 mth_run_log mrl
80 IF v_output_count > 0 THEN
77 WHERE mrl.fact_table = 'MTH_EQUIP_OUTPUT_SUMMARY'
78 AND meo.last_update_date > mrl.from_date;
79
81 v_msg:=fnd_message.get_string('MTH','MTH_OUTPUT_NOT_IN_SUMMARY');
82 mth_util_pkg.log_msg(v_msg,mth_util_pkg.G_DBG_EXCEPTION);
83
84 v_retcode := 2;
85 END IF;
86 END IF;
87
88 SELECT mview_name
89 BULK COLLECT
90 INTO v_compile_state
91 FROM dba_mviews
92 WHERE mview_name IN ('MTH_ITEM_COST_MV','MTH_RESOURCE_COST_MV')
93 AND owner = sys_context('USERENV','CURRENT_SCHEMA')
94 AND compile_state <> 'VALID' ;
95
96 IF v_compile_state.Count > 0
97 THEN
98 FOR i IN v_compile_state.FIRST .. v_compile_state.LAST
99 LOOP
100
101 FND_MESSAGE.SET_TOKEN('MOC_MV_TOKEN',v_compile_state(i));
102 v_msg:=fnd_message.get_string('MTH','MTH_MV_NOT_REFRESHED');
103 mth_util_pkg.log_msg(v_msg,mth_util_pkg.G_DBG_EXCEPTION);
104 v_retcode := 2;
105 END LOOP;
106 END IF;
107
108 p_retcode := v_retcode;
109 p_err_buff := 'Error in validate procedure of MTH_PROCESS_OUTPUT_PKG.';
110
111 END VALIDATE;
112
113 /*******************************************************************************
114 * Procedure :PROCESS_OUTPUT *
115 * Description :This procedure is the main procedure for output *
116 * File Name :MTHEQOPB.PLS *
117 * Visibility :Public *
118 * Parameters : p_mode : INIT,INCR,RECAL *
119 * p_source : TAG,CSV *
120 * p_recal_from_date : Recalculation from date *
121 * p_recal_to_date : Recalculation to date *
122 * p_equipment_pk_key : Equipment to recalculate *
123 * p_plant_pk_key : Plant for recalculation *
124 * Modification log : *
125 * Author Date Change *
126 * Mandar Gijare 02-Sep-2011 Initial Creation *
127 *******************************************************************************/
128
129 PROCEDURE PROCESS_OUTPUT( p_mode IN VARCHAR2, --INIT, INCR, RECAL
130 p_source IN VARCHAR2, --TAG, CSV
131 p_recal_from_date IN DATE DEFAULT NULL, --Recalculation from date
132 p_recal_to_date IN DATE DEFAULT NULL, --Recalculation to date
133 p_equipment_pk_key IN NUMBER DEFAULT NULL, --Equipment to recalculate
134 p_plant_pk_key IN NUMBER DEFAULT NULL, --Plant for recalcution
135 p_ret_code OUT NOCOPY NUMBER
136 ) IS
137 l_ret_code NUMBER;
138 BEGIN
139 l_ret_code := 0;
140 mth_util_pkg.log_msg('PROCESS_OUTPUT start', mth_util_pkg.G_DBG_PROC_FUN_START);
141 mth_util_pkg.log_msg('p_mode = ' || p_mode , mth_util_pkg.G_DBG_PARAM_VAL);
142 mth_util_pkg.log_msg('p_source = ' || p_source , mth_util_pkg.G_DBG_PARAM_VAL);
143
144 IF(p_mode = 'RECAL') THEN
145 mth_util_pkg.log_msg('p_recal_from_date = ' || to_char(p_recal_from_date,'DD-MON-YYYY HH24:MI:SS') , mth_util_pkg.G_DBG_PARAM_VAL);
146 mth_util_pkg.log_msg('p_recal_to_date = ' || to_char(p_recal_to_date,'DD-MON-YYYY HH24:MI:SS') , mth_util_pkg.G_DBG_PARAM_VAL);
147 mth_util_pkg.log_msg('p_equipment_pk_key = ' || p_equipment_pk_key, mth_util_pkg.G_DBG_PARAM_VAL);
148 mth_util_pkg.log_msg('p_plant_pk_key = ' || p_plant_pk_key, mth_util_pkg.G_DBG_PARAM_VAL);
149 END IF;
150
151 IF p_source = 'TAG' THEN
152 IF p_mode = 'INIT' THEN
153 INIT_OUTPUT_FROM_READING();
154 ELSIF p_mode = 'INCR' THEN
155 INCR_OUTPUT_FROM_READING();
156 ELSE
157 RECAL_OUTPUT_FROM_READING(p_recal_from_date,
158 p_recal_to_date,
159 p_equipment_pk_key
160 );
161 END IF;
162 END IF;
163
164 IF p_source IN ('CSVO','CSVSO') THEN
165 IF p_mode = 'INIT' THEN
166 INIT_OUTPUT_FROM_CSV();
167 ELSIF p_mode = 'INCR' THEN
168 INCR_OUTPUT_FROM_CSV();
169 ELSE
170 BEGIN
171 RECAL_OUTPUT_FROM_CSV(p_recal_from_date,
172 p_recal_to_date,
173 p_equipment_pk_key,
174 p_plant_pk_key );
175 EXCEPTION
176 WHEN MTH_PROCESS_TXN_PKG.VALIDATION_ERR THEN
177 l_ret_code := 1;
178 mth_util_pkg.log_msg('Exception VALIDATION_ERR in PROCESS_OUTPUT', mth_util_pkg.G_DBG_EXCEPTION);
179 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
180 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
181 END;
182 END IF;
183 END IF;
184 p_ret_code := l_ret_code;
185 mth_util_pkg.log_msg('PROCESS_OUTPUT end', mth_util_pkg.G_DBG_PROC_FUN_END);
186
187 EXCEPTION
188 WHEN OTHERS THEN
189 l_ret_code := 2;
190 p_ret_code := l_ret_code;
191 mth_util_pkg.log_msg('Exception OTHERS in PROCESS_OUTPUT', mth_util_pkg.G_DBG_EXCEPTION);
192 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
193 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
194 RAISE;
195
199 * Procedure :INIT_OUTPUT_FROM_READING *
196 END PROCESS_OUTPUT;
197
198 /*******************************************************************************
200 * Description :This procedure is used for calculating the output *
201 * in INIT mode *
202 * File Name :MTHEQOPB.PLS *
203 * Visibility :Public *
204 * Parameters : *
205 * *
206 * Modification log : *
207 * Author Date Change *
208 * Mandar Gijare 02-Sep-2011 Initial Creation *
209 *******************************************************************************/
210 PROCEDURE INIT_OUTPUT_FROM_READING IS
211 v_log_date DATE;
212 v_unassigned_val VARCHAR2(30);
213 BEGIN
214 mth_util_pkg.log_msg('INIT_OUTPUT_FROM_READING start', mth_util_pkg.G_DBG_PROC_FUN_START);
215
216 -- Initialize default parameters
217 v_log_date := sysdate;
218 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
219
220 -- Call mth_run_log_pre_load
221 mth_util_pkg.mth_run_log_pre_load('MTH_EQUIP_OUTPUT',v_unassigned_val,'INITIAL',NULL,0,v_log_date);
222
223 --delete output table
224 DELETE FROM MTH_EQUIP_OUTPUT;
225 mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_OUTPUT - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
226
227 INSERT INTO MTH_EQUIP_OUTPUT (
228 EQUIPMENT_FK_KEY,
229 ITEM_FK_KEY,
230 SHIFT_WORKDAY_FK_KEY,
231 WORKORDER_FK_KEY,
232 HOUR_FK_KEY,
233 READING_TIME,
234 QTY_COMPLETED,
235 QTY_SCRAP,
236 QTY_REJECTED,
237 QTY_REWORK,
238 QTY_GOOD,
239 QTY_OUTPUT,
240 SYSTEM_FK_KEY,
241 CREATION_DATE,
242 LAST_UPDATE_DATE,
243 CREATION_SYSTEM_ID,
244 LAST_UPDATE_SYSTEM_ID,
245 RECIPE_NUM,
246 RECIPE_VERSION,
247 SEGMENT_FK_KEY
248 )
249 SELECT a.EQUIPMENT_FK_KEY,
250 a.ITEM_FK_KEY,
251 a.SHIFT_WORKDAY_FK_KEY,
252 a.WORKORDER_FK_KEY,
253 a.HOUR_FK_KEY,
254 a.READING_TIME,
255 SUM((CASE b.MTH_ALIAS WHEN 'Completed Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_COMPLETED,
256 SUM((CASE b.MTH_ALIAS WHEN 'Scrap Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_SCRAP,
257 SUM((CASE b.MTH_ALIAS WHEN 'Rejected Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_REJECTED,
258 SUM((CASE b.MTH_ALIAS WHEN 'Rework Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_REWORK,
259 SUM((CASE b.MTH_ALIAS WHEN 'Completed Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_GOOD,
260 SUM((CASE b.MTH_ALIAS WHEN 'Output Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_OUTPUT,
261 v_unassigned_val,
262 SYSDATE,
263 SYSDATE,
264 v_unassigned_val,
265 v_unassigned_val,
266 a.RECIPE_NUM,
267 a.RECIPE_VERSION,
268 a.SEGMENT_FK_KEY
269 FROM MTH_TAG_READINGS a,
270 MTH_ENTITIES b
271 WHERE a.MTH_ENTITY = b.ID
272 AND a.EQUIPMENT_FK_KEY IS NOT NULL
273 AND a.WORKORDER_FK_KEY IS NOT NULL
274 AND a.SEGMENT_FK_KEY IS NOT NULL
275 AND a.SHIFT_WORKDAY_FK_KEY IS NOT NULL
276 AND a.ITEM_FK_KEY IS NOT NULL
277 AND a.HOUR_FK_KEY IS NOT NULL
278 AND b.MTH_ALIAS IN ( 'Completed Quantity' , 'Output Quantity' , 'Rejected Quantity' , 'Scrap Quantity' , 'Rework Quantity' )
279 AND a.LAST_UPDATE_DATE <= v_log_date
280 GROUP BY a.READING_TIME,
281 a.EQUIPMENT_FK_KEY,
282 a.WORKORDER_FK_KEY,
283 a.ITEM_FK_KEY,
284 a.SHIFT_WORKDAY_FK_KEY,
285 a.RECIPE_NUM,
286 a.RECIPE_VERSION,
287 a.SEGMENT_FK_KEY,
288 a.HOUR_FK_KEY;
289 mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
290
291 --update processed flag in readings table
292 update MTH_TAG_READINGS t
293 set PROCESSED_FLAG = 1,
294 last_update_date=sysdate
295 where exists (
296 select 1
297 from mth_entities m
298 where t.MTH_ENTITY = m.ID
299 AND t.PROCESSED_FLAG = 0
300 AND t.EQUIPMENT_FK_KEY IS NOT NULL
301 AND t.WORKORDER_FK_KEY IS NOT NULL
302 AND t.SEGMENT_FK_KEY IS NOT NULL
303 AND t.SHIFT_WORKDAY_FK_KEY IS NOT NULL
304 AND t.ITEM_FK_KEY IS NOT NULL
305 AND t.HOUR_FK_KEY IS NOT NULL
306 AND m.MTH_ALIAS IN ('Completed Quantity', 'Output Quantity', 'Rejected Quantity', 'Scrap Quantity', 'Rework Quantity')
307 AND t.LAST_UPDATE_DATE <= v_log_date);
308 mth_util_pkg.log_msg('Number of rows updated in MTH_TAG_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
309
310 ----Call mth_run_log_post_load
311 mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_OUTPUT',v_unassigned_val);
312
313
314 mth_util_pkg.log_msg('INIT_OUTPUT_FROM_READING end', mth_util_pkg.G_DBG_PROC_FUN_END);
315 EXCEPTION
316 WHEN OTHERS THEN
317 mth_util_pkg.log_msg('Exception OTHERS in INIT_OUTPUT_FROM_READING', mth_util_pkg.G_DBG_EXCEPTION);
321 END INIT_OUTPUT_FROM_READING;
318 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
319 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
320 RAISE;
322
323 /*******************************************************************************
324 * Procedure :INCR_OUTPUT_FROM_READING *
325 * Description :This procedure is used for calculating the output *
326 * in INCR mode *
327 * File Name :MTHEQOPB.PLS *
328 * Visibility :Public *
329 * Parameters : *
330 * *
331 * Modification log : *
332 * Author Date Change *
333 * Mandar Gijare 02-Sep-2011 Initial Creation *
334 *******************************************************************************/
335 PROCEDURE INCR_OUTPUT_FROM_READING IS
336 v_log_from_date DATE;
337 v_log_to_date DATE;
338 v_unassigned_val VARCHAR2(30);
339 BEGIN
340 mth_util_pkg.log_msg('INCR_OUTPUT_FROM_READING start', mth_util_pkg.G_DBG_PROC_FUN_START);
341
342 -- Initialize default parameters
343 v_log_to_date := sysdate;
344 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
345
346 -- Call mth_run_log_pre_load
347 mth_util_pkg.mth_run_log_pre_load('MTH_EQUIP_OUTPUT',v_unassigned_val,'INCR',NULL,0,v_log_to_date);
348
349 -- Call GET_RUN_LOG_DATES
350 mth_util_pkg.GET_RUN_LOG_DATES('MTH_EQUIP_OUTPUT',NULL,NULL,NULL,v_log_from_date,v_log_to_date);
351
352
353 MERGE INTO MTH_EQUIP_OUTPUT o USING
354 (SELECT a.EQUIPMENT_FK_KEY,
355 a.ITEM_FK_KEY,
356 a.SHIFT_WORKDAY_FK_KEY,
357 a.WORKORDER_FK_KEY,
358 a.HOUR_FK_KEY,
359 a.READING_TIME,
360 SUM((CASE b.MTH_ALIAS WHEN 'Completed Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_COMPLETED,
361 SUM((CASE b.MTH_ALIAS WHEN 'Scrap Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_SCRAP,
362 SUM((CASE b.MTH_ALIAS WHEN 'Rejected Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_REJECTED,
363 SUM((CASE b.MTH_ALIAS WHEN 'Rework Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_REWORK,
364 SUM((CASE b.MTH_ALIAS WHEN 'Completed Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_GOOD,
365 SUM((CASE b.MTH_ALIAS WHEN 'Output Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_OUTPUT,
366 v_unassigned_val SYSTEM_FK_KEY,
367 a.RECIPE_NUM,
368 a.RECIPE_VERSION,
369 a.SEGMENT_FK_KEY
370 FROM MTH_TAG_READINGS a,
371 MTH_ENTITIES b
372 WHERE a.MTH_ENTITY = b.ID
373 AND a.EQUIPMENT_FK_KEY IS NOT NULL
374 AND a.WORKORDER_FK_KEY IS NOT NULL
375 AND a.SEGMENT_FK_KEY IS NOT NULL
376 AND a.SHIFT_WORKDAY_FK_KEY IS NOT NULL
377 AND a.ITEM_FK_KEY IS NOT NULL
378 AND a.HOUR_FK_KEY IS NOT NULL
379 AND a.PROCESSED_FLAG = 0
380 AND b.MTH_ALIAS IN ( 'Completed Quantity' , 'Output Quantity' , 'Rejected Quantity' , 'Scrap Quantity' , 'Rework Quantity' )
381 AND a.LAST_UPDATE_DATE > v_log_from_date and a.LAST_UPDATE_DATE <=v_log_to_date
382 GROUP BY a.READING_TIME,
383 a.EQUIPMENT_FK_KEY,
384 a.WORKORDER_FK_KEY,
385 a.ITEM_FK_KEY,
386 a.SHIFT_WORKDAY_FK_KEY,
387 a.RECIPE_NUM,
388 a.RECIPE_VERSION,
389 a.SEGMENT_FK_KEY,
390 a.HOUR_FK_KEY) tr
391 ON (o.EQUIPMENT_FK_KEY = tr.EQUIPMENT_FK_KEY
392 AND o.ITEM_FK_KEY = tr.ITEM_FK_KEY
393 AND o.SHIFT_WORKDAY_FK_KEY = tr.SHIFT_WORKDAY_FK_KEY
394 AND o.WORKORDER_FK_KEY = tr.WORKORDER_FK_KEY
395 AND o.HOUR_FK_KEY = tr.HOUR_FK_KEY
396 AND o.READING_TIME = tr.READING_TIME
397 AND o.SEGMENT_FK_KEY = tr.SEGMENT_FK_KEY
398 AND nvl(o.RECIPE_NUM,'@@@') = nvl(tr.RECIPE_NUM,'@@@')
399 AND nvl(o.RECIPE_VERSION,'@@@') = nvl(tr.RECIPE_VERSION,'@@@')
400 AND o.SYSTEM_FK_KEY = tr.SYSTEM_FK_KEY)
401 WHEN MATCHED THEN
402 UPDATE SET
403 o.QTY_COMPLETED = o.QTY_COMPLETED + tr.QTY_COMPLETED,
404 o.QTY_SCRAP = o.QTY_SCRAP + tr.QTY_SCRAP,
405 o.QTY_REJECTED = o.QTY_REJECTED + tr.QTY_REJECTED,
406 o.QTY_REWORK = o.QTY_REWORK + tr.QTY_REWORK,
407 o.QTY_GOOD = o.QTY_GOOD + tr.QTY_GOOD,
408 o.QTY_OUTPUT = o.QTY_OUTPUT + tr.QTY_OUTPUT,
409 o.LAST_UPDATE_DATE = SYSDATE,
410 o.LAST_UPDATE_SYSTEM_ID = v_unassigned_val
411 WHEN NOT MATCHED THEN
412 INSERT (
413 o.EQUIPMENT_FK_KEY,
414 o.ITEM_FK_KEY,
415 o.SHIFT_WORKDAY_FK_KEY,
416 o.WORKORDER_FK_KEY,
417 o.HOUR_FK_KEY,
418 o.READING_TIME,
419 o.QTY_COMPLETED,
420 o.QTY_SCRAP,
421 o.QTY_REJECTED,
422 o.QTY_REWORK,
423 o.QTY_GOOD,
424 o.QTY_OUTPUT,
425 o.SYSTEM_FK_KEY,
426 o.CREATION_DATE,
427 o.LAST_UPDATE_DATE,
428 o.CREATION_SYSTEM_ID,
429 o.LAST_UPDATE_SYSTEM_ID,
430 o.RECIPE_NUM,
434 VALUES
431 o.RECIPE_VERSION,
432 o.SEGMENT_FK_KEY
433 )
435 (
436 tr.EQUIPMENT_FK_KEY,
437 tr.ITEM_FK_KEY,
438 tr.SHIFT_WORKDAY_FK_KEY,
439 tr.WORKORDER_FK_KEY,
440 tr.HOUR_FK_KEY,
441 tr.READING_TIME,
442 tr.QTY_COMPLETED,
443 tr.QTY_SCRAP,
444 tr.QTY_REJECTED,
445 tr.QTY_REWORK,
446 tr.QTY_GOOD,
447 tr.QTY_OUTPUT,
448 v_unassigned_val,
449 SYSDATE,
450 SYSDATE,
451 v_unassigned_val,
452 v_unassigned_val,
453 tr.RECIPE_NUM,
454 tr.RECIPE_VERSION,
455 tr.SEGMENT_FK_KEY
456 );
457 mth_util_pkg.log_msg('Number of rows merged in MTH_EQUIP_OUTPUT - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
458
459 --update processed flag in readings table
460 update MTH_TAG_READINGS t
461 set PROCESSED_FLAG = 1,
462 last_update_date=sysdate
463 where exists (
464 select 1
465 from mth_entities m
466 where t.MTH_ENTITY = m.ID
467 AND t.PROCESSED_FLAG = 0
468 AND t.EQUIPMENT_FK_KEY IS NOT NULL
469 AND t.WORKORDER_FK_KEY IS NOT NULL
470 AND t.SEGMENT_FK_KEY IS NOT NULL
471 AND t.SHIFT_WORKDAY_FK_KEY IS NOT NULL
472 AND t.ITEM_FK_KEY IS NOT NULL
473 AND t.HOUR_FK_KEY IS NOT NULL
474 AND m.MTH_ALIAS IN ('Completed Quantity', 'Output Quantity', 'Rejected Quantity', 'Scrap Quantity', 'Rework Quantity')
475 AND t.LAST_UPDATE_DATE BETWEEN v_log_from_date and v_log_to_date);
476 mth_util_pkg.log_msg('Number of rows updated in MTH_TAG_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
477
478 ----Call mth_run_log_post_load
479 mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_OUTPUT',v_unassigned_val);
480
481
482 mth_util_pkg.log_msg('INCR_OUTPUT_FROM_READING end', mth_util_pkg.G_DBG_PROC_FUN_END);
483 EXCEPTION
484 WHEN OTHERS THEN
485 mth_util_pkg.log_msg('Exception OTHERS in INCR_OUTPUT_FROM_READING', mth_util_pkg.G_DBG_EXCEPTION);
486 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
487 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
488 RAISE;
489 END INCR_OUTPUT_FROM_READING;
490
491 /*******************************************************************************
492 * Procedure :RECAL_OUTPUT_FROM_READING *
493 * Description :This procedure is used for calculating the output *
494 * in RECAL mode *
495 * File Name :MTHEQOPB.PLS *
496 * Visibility :Public *
497 * Parameters : p_recal_from_date : Recalculation from date *
498 * p_recal_to_date : Recalculation to date *
499 * p_equipment_pk_key : Equipment to recalculate *
500 * *
501 * Modification log : *
502 * Author Date Change *
503 * Mandar Gijare 02-Sep-2011 Initial Creation *
504 *******************************************************************************/
505 PROCEDURE RECAL_OUTPUT_FROM_READING(p_recal_from_date IN DATE, --Recalculation from date
506 p_recal_to_date IN DATE DEFAULT NULL, --Recalculation to date
507 p_equipment_pk_key IN NUMBER DEFAULT NULL --Equipment to recalculate
508 ) IS
509 v_unassigned_val VARCHAR2(30);
510 BEGIN
511 mth_util_pkg.log_msg('RECAL_OUTPUT_FROM_READING start', mth_util_pkg.G_DBG_PROC_FUN_START);
512 mth_util_pkg.log_msg('p_recal_from_date = ' || to_char(p_recal_from_date,'DD-MON-YYYY HH24:MI:SS') , mth_util_pkg.G_DBG_PARAM_VAL);
513 mth_util_pkg.log_msg('p_recal_to_date = ' || to_char(p_recal_to_date,'DD-MON-YYYY HH24:MI:SS') , mth_util_pkg.G_DBG_PARAM_VAL);
514 mth_util_pkg.log_msg('p_equipment_pk_key = ' || p_equipment_pk_key, mth_util_pkg.G_DBG_PARAM_VAL);
515
516 -- Initialize default parameters
517 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
518
519 --delete output table
520 DELETE FROM MTH_EQUIP_OUTPUT o
521 WHERE o.EQUIPMENT_FK_KEY = nvl(p_equipment_pk_key,o.EQUIPMENT_FK_KEY)
522 AND o.READING_TIME BETWEEN p_recal_from_date AND nvl(p_recal_to_date,o.READING_TIME);
523 mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_OUTPUT - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
524
525 INSERT INTO MTH_EQUIP_OUTPUT (
526 EQUIPMENT_FK_KEY,
527 ITEM_FK_KEY,
528 SHIFT_WORKDAY_FK_KEY,
529 WORKORDER_FK_KEY,
530 HOUR_FK_KEY,
531 READING_TIME,
532 QTY_COMPLETED,
533 QTY_SCRAP,
534 QTY_REJECTED,
535 QTY_REWORK,
536 QTY_GOOD,
537 QTY_OUTPUT,
538 SYSTEM_FK_KEY,
539 CREATION_DATE,
540 LAST_UPDATE_DATE,
541 CREATION_SYSTEM_ID,
542 LAST_UPDATE_SYSTEM_ID,
543 RECIPE_NUM,
544 RECIPE_VERSION,
545 SEGMENT_FK_KEY
546 )
547 SELECT a.EQUIPMENT_FK_KEY,
548 a.ITEM_FK_KEY,
549 a.SHIFT_WORKDAY_FK_KEY,
550 a.WORKORDER_FK_KEY,
551 a.HOUR_FK_KEY,
552 a.READING_TIME,
553 SUM((CASE b.MTH_ALIAS WHEN 'Completed Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_COMPLETED,
557 SUM((CASE b.MTH_ALIAS WHEN 'Completed Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_GOOD,
554 SUM((CASE b.MTH_ALIAS WHEN 'Scrap Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_SCRAP,
555 SUM((CASE b.MTH_ALIAS WHEN 'Rejected Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_REJECTED,
556 SUM((CASE b.MTH_ALIAS WHEN 'Rework Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_REWORK,
558 SUM((CASE b.MTH_ALIAS WHEN 'Output Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_OUTPUT,
559 v_unassigned_val,
560 SYSDATE,
561 SYSDATE,
562 v_unassigned_val,
563 v_unassigned_val,
564 a.RECIPE_NUM,
565 a.RECIPE_VERSION,
566 a.SEGMENT_FK_KEY
567 FROM MTH_TAG_READINGS a,
568 MTH_ENTITIES b
569 WHERE a.MTH_ENTITY = b.ID
570 AND a.EQUIPMENT_FK_KEY IS NOT NULL
571 AND a.WORKORDER_FK_KEY IS NOT NULL
572 AND a.SEGMENT_FK_KEY IS NOT NULL
573 AND a.SHIFT_WORKDAY_FK_KEY IS NOT NULL
574 AND a.ITEM_FK_KEY IS NOT NULL
575 AND a.HOUR_FK_KEY IS NOT NULL
576 AND a.EQUIPMENT_FK_KEY = nvl(p_equipment_pk_key, a.EQUIPMENT_FK_KEY)
577 AND b.MTH_ALIAS IN ( 'Completed Quantity' , 'Output Quantity' , 'Rejected Quantity' , 'Scrap Quantity' , 'Rework Quantity' )
578 AND a.READING_TIME BETWEEN p_recal_from_date AND nvl(p_recal_to_date,a.READING_TIME)
579 GROUP BY a.READING_TIME,
580 a.EQUIPMENT_FK_KEY,
581 a.WORKORDER_FK_KEY,
582 a.ITEM_FK_KEY,
583 a.SHIFT_WORKDAY_FK_KEY,
584 a.RECIPE_NUM,
585 a.RECIPE_VERSION,
586 a.SEGMENT_FK_KEY,
587 a.HOUR_FK_KEY;
588 mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
589
590 --update processed flag in readings table
591 update MTH_TAG_READINGS t
592 set PROCESSED_FLAG = 1,
593 last_update_date=sysdate
594 where exists (
595 select 1
596 from mth_entities m
597 where t.MTH_ENTITY = m.ID
598 AND t.PROCESSED_FLAG = 0
599 AND t.EQUIPMENT_FK_KEY IS NOT NULL
600 AND t.WORKORDER_FK_KEY IS NOT NULL
601 AND t.SEGMENT_FK_KEY IS NOT NULL
602 AND t.SHIFT_WORKDAY_FK_KEY IS NOT NULL
603 AND t.ITEM_FK_KEY IS NOT NULL
604 AND t.HOUR_FK_KEY IS NOT NULL
605 AND m.MTH_ALIAS IN ('Completed Quantity', 'Output Quantity', 'Rejected Quantity', 'Scrap Quantity', 'Rework Quantity')
606 AND t.READING_TIME BETWEEN p_recal_from_date AND nvl(p_recal_to_date,t.READING_TIME));
607 mth_util_pkg.log_msg('Number of rows updated in MTH_TAG_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
608
609
610 mth_util_pkg.log_msg('RECAL_OUTPUT_FROM_READING end', mth_util_pkg.G_DBG_PROC_FUN_END);
611 EXCEPTION
612 WHEN OTHERS THEN
613 mth_util_pkg.log_msg('Exception OTHERS in RECAL_OUTPUT_FROM_READING', mth_util_pkg.G_DBG_EXCEPTION);
614 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
615 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
616 RAISE;
617 END RECAL_OUTPUT_FROM_READING;
618
619 /*******************************************************************************
620 * Procedure :INIT_OUTPUT_FROM_CSV *
621 * Description :This procedure is used for calculating the output *
622 * in INIT mode from CSV *
623 * File Name :MTHEQOPB.PLS *
624 * Visibility :Private *
625 * Parameters : *
626 *******************************************************************************/
627 PROCEDURE INIT_OUTPUT_FROM_CSV IS
628 v_log_date DATE;
629 v_unassigned_val VARCHAR2(30);
630 v_processing_flag NUMBER;
631 BEGIN
632 mth_util_pkg.log_msg('INIT_OUTPUT_FROM_CSV start', mth_util_pkg.G_DBG_PROC_FUN_START);
633
634 -- Initialize default parameters
635 v_log_date := sysdate;
636 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
637
638
639 --delete data from the output table
640 DELETE FROM MTH_EQUIP_OUTPUT;
641 mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_OUTPUT - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
642
643 --Ensuring the functionality of ES map is introduced before the staging to fact is executed.
644 INSERT INTO mth_equip_output_stg(equipment_fk,
645 item_fk,
646 shift_workday_fk,
647 workorder_fk,
648 reading_time,
649 qty_completed,
650 qty_scrap,
651 qty_rejected,
652 qty_rework,
653 qty_uom,
654 qty_good,
655 qty_output,
656 system_fk,
657 recipe_version,
658 recipe_num,
659 segment_fk,
660 user_dim1_fk,
661 user_dim2_fk,
662 user_dim3_fk,
663 user_dim4_fk,
667 user_attr3,
664 user_dim5_fk,
665 user_attr1,
666 user_attr2,
668 user_attr4,
669 user_attr5,
670 user_measure1,
671 user_measure2,
672 user_measure3,
673 user_measure4,
674 user_measure5,
675 scrap_reason_code)
676 (SELECT equipment_fk,
677 item_fk,
678 shift_workday_fk,
679 workorder_fk,
680 reading_time,
681 qty_completed,
682 qty_scrap,
683 qty_rejected,
684 qty_rework,
685 qty_uom,
686 qty_good,
687 qty_output,
688 system_fk,
689 recipe_version,
690 recipe_num,
691 segment_fk,
692 user_dim1_fk,
693 user_dim2_fk,
694 user_dim3_fk,
695 user_dim4_fk,
696 user_dim5_fk,
697 user_attr1,
698 user_attr2,
699 user_attr3,
700 user_attr4,
701 user_attr5,
702 user_measure1,
703 user_measure2,
704 user_measure3,
705 user_measure4,
706 user_measure5,
707 scrap_reason_code
708 FROM mth_equip_output_err
709 WHERE reprocess_ready_yn = 'Y');
710 mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT_STG from error table - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
711
712 --delete data from the output error table
713 DELETE FROM MTH_EQUIP_OUTPUT_ERR
714 WHERE REPROCESS_READY_YN = 'Y';
715 mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_OUTPUT_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
716
717 mth_util_pkg.switch_column_default_value('MTH_EQUIP_OUTPUT_STG',v_processing_flag);
718
719 --Execute all validations on csv records
720
721 -- Validation for Invalid Item
722 UPDATE mth_equip_output_stg stg
723 SET stg.err_code = stg.err_code || 'ITM '
724 WHERE NOT EXISTS ( SELECT * FROM ( SELECT mid.item_pk_key, mid.item_pk
725 FROM mth_items_d mid,
726 mth_equip_output_stg stg
727 WHERE mid.item_pk = stg.item_fk) itm
728 WHERE itm.item_pk = stg.item_fk
729 AND stg.processing_flag = v_processing_flag );
730 mth_util_pkg.log_msg('Number of rows with ITM in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
731
732 -- Validation for Invalid Segment
733 UPDATE mth_equip_output_stg stg
734 SET stg.err_code = stg.err_code || 'SEG '
735 WHERE NOT EXISTS ( SELECT * FROM ( SELECT msf.segment_pk_key, msf.segment_pk
736 FROM mth_production_segments_f msf,
737 mth_equip_output_stg stg
738 WHERE msf.segment_pk = Nvl(stg.segment_fk,msf.segment_pk)) seg
739 WHERE seg.segment_pk = Nvl(stg.segment_fk,seg.segment_pk)
740 AND stg.processing_flag = v_processing_flag );
741 mth_util_pkg.log_msg('Number of rows with SEG in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
742
743 -- Validation for invalid work order
744 UPDATE mth_equip_output_stg stg
745 SET stg.err_code = stg.err_code || 'WKO '
746 WHERE stg.workorder_fk IS NOT NULL
747 AND NOT EXISTS ( SELECT * FROM ( SELECT mps.workorder_pk,mps.workorder_pk_key
748 FROM mth_production_schedules_f mps,
749 mth_equip_output_stg stg
750 WHERE stg.workorder_fk = mps.workorder_pk(+)
751 AND stg.workorder_fk IS NOT NULL) wko
752 WHERE wko.workorder_pk = stg.workorder_fk
753 AND stg.processing_flag = v_processing_flag);
754 mth_util_pkg.log_msg('Number of rows with WKO in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
755
756 -- Validation for Invalid Equipment
757 UPDATE mth_equip_output_stg stg
758 SET stg.err_code = stg.err_code || 'EQP '
759 WHERE NOT EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
760 FROM mth_equipments_d med,
761 mth_equip_output_stg stg
762 WHERE med.equipment_pk = stg.equipment_fk) eqp
766
763 WHERE eqp.equipment_pk = stg.equipment_fk
764 AND stg.processing_flag = v_processing_flag );
765 mth_util_pkg.log_msg('Number of rows with EQP in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
767 -- Validation for Inactive Equipment
768 UPDATE mth_equip_output_stg stg
769 SET stg.err_code = stg.err_code || 'IEQ '
770 WHERE EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
771 FROM mth_equipments_d med,
772 mth_equip_output_stg stg
773 WHERE med.equipment_pk = stg.equipment_fk
774 AND Nvl(med.status,'NULL') <> 'ACTIVE') eqp
775 WHERE eqp.equipment_pk = stg.equipment_fk
776 AND stg.processing_flag = v_processing_flag );
777 mth_util_pkg.log_msg('Number of rows with IEQ in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
778
779 -- Validation for Duplicate record
780 UPDATE mth_equip_output_stg stg
781 SET stg.err_code = stg.err_code || 'DUP '
782 WHERE EXISTS ( SELECT * FROM ( SELECT equipment_fk,shift_workday_fk,reading_time,item_fk,workorder_fk,segment_fk,Count(equipment_fk) cnt
783 FROM mth_equip_output_stg
784 GROUP BY equipment_fk,shift_workday_fk,reading_time,item_fk,workorder_fk,segment_fk) dup
785 WHERE dup.cnt>1
786 AND dup.equipment_fk = stg.equipment_fk
787 AND dup.shift_workday_fk = stg.shift_workday_fk
788 AND dup.reading_time = stg.reading_time
789 AND dup.item_fk = stg.item_fk
790 AND dup.workorder_fk = stg.workorder_fk
791 AND dup.segment_fk = stg.segment_fk
792 AND stg.processing_flag = v_processing_flag );
793 mth_util_pkg.log_msg('Number of rows with DUP in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
794
795 -- Validation for invalid shift
796 UPDATE mth_equip_output_stg stg
797 SET stg.err_code = stg.err_code || 'WDS '
798 WHERE stg.shift_workday_fk IS NOT NULL
799 AND NOT EXISTS ( SELECT * FROM ( SELECT mds.shift_workday_pk
800 FROM mth_workday_shifts_d mds,
801 mth_equip_output_stg stg
802 WHERE stg.shift_workday_fk = mds.shift_workday_pk(+)
803 AND stg.shift_workday_fk IS NOT NULL) wds
804 WHERE wds.shift_workday_pk = stg.shift_workday_fk
805 AND stg.processing_flag = v_processing_flag);
806 mth_util_pkg.log_msg('Number of rows with WDS in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
807
808 -- Validation for Null Work Day Shift
809 UPDATE mth_equip_output_stg stg
810 SET stg.err_code = stg.err_code || 'NWDS '
811 WHERE stg.shift_workday_fk IS NULL
812 AND stg.processing_flag = v_processing_flag;
813 mth_util_pkg.log_msg('Number of rows with NWDS in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
814
815 -- Validation for user dimension 1
816 UPDATE mth_equip_output_stg stg
817 SET stg.err_code = stg.err_code || 'UD1 '
818 WHERE stg.user_dim1_fk IS NOT NULL
819 AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim1_fk
820 FROM mth_user_dim_entities_mst mue,
821 mth_equip_output_stg stg
822 WHERE stg.user_dim1_fk = mue.entity_pk (+)
823 AND stg.user_dim1_fk IS NOT NULL) ud1
824 WHERE ud1.user_dim1_fk = stg.user_dim1_fk
825 AND ud1.entity_pk IS NULL
826 AND stg.processing_flag = v_processing_flag);
827 mth_util_pkg.log_msg('Number of rows with UD1 in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
828
829 -- Validation for user dimension 2
830 UPDATE mth_equip_output_stg stg
831 SET stg.err_code = stg.err_code || 'UD2 '
832 WHERE stg.user_dim2_fk IS NOT NULL
833 AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim2_fk
834 FROM mth_user_dim_entities_mst mue,
835 mth_equip_output_stg stg
836 WHERE stg.user_dim2_fk = mue.entity_pk (+)
837 AND stg.user_dim2_fk IS NOT NULL) ud2
838 WHERE ud2.user_dim2_fk = stg.user_dim2_fk
839 AND ud2.entity_pk IS NULL
840 AND stg.processing_flag = v_processing_flag);
841 mth_util_pkg.log_msg('Number of rows with UD2 in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
842
843 -- Validation for user dimension 3
844 UPDATE mth_equip_output_stg stg
845 SET stg.err_code = stg.err_code || 'UD3 '
846 WHERE stg.user_dim3_fk IS NOT NULL
847 AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim3_fk
848 FROM mth_user_dim_entities_mst mue,
849 mth_equip_output_stg stg
850 WHERE stg.user_dim3_fk = mue.entity_pk (+)
851 AND stg.user_dim3_fk IS NOT NULL) ud3
852 WHERE ud3.user_dim3_fk = stg.user_dim3_fk
856
853 AND ud3.entity_pk IS NULL
854 AND stg.processing_flag = v_processing_flag);
855 mth_util_pkg.log_msg('Number of rows with UD3 in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
857 -- Validation for user dimension 4
858 UPDATE mth_equip_output_stg stg
859 SET stg.err_code = stg.err_code || 'UD4 '
860 WHERE stg.user_dim4_fk IS NOT NULL
861 AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim4_fk
862 FROM mth_user_dim_entities_mst mue,
863 mth_equip_output_stg stg
864 WHERE stg.user_dim4_fk = mue.entity_pk (+)
865 AND stg.user_dim4_fk IS NOT NULL) ud4
866 WHERE ud4.user_dim4_fk = stg.user_dim4_fk
867 AND stg.processing_flag = v_processing_flag
868 AND ud4.entity_pk IS NULL);
869 mth_util_pkg.log_msg('Number of rows with UD4 in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
870
871 -- Validation for user dimension 5
872 UPDATE mth_equip_output_stg stg
873 SET stg.err_code = stg.err_code || 'UD5 '
874 WHERE stg.user_dim5_fk IS NOT NULL
875 AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim5_fk
876 FROM mth_user_dim_entities_mst mue,
877 mth_equip_output_stg stg
878 WHERE stg.user_dim5_fk = mue.entity_pk (+)
879 AND stg.user_dim5_fk IS NOT NULL) ud5
880 WHERE ud5.user_dim5_fk = stg.user_dim5_fk
881 AND stg.processing_flag = v_processing_flag
882 AND ud5.entity_pk IS NULL);
883 mth_util_pkg.log_msg('Number of rows with UD5 in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
884
885 -- Validation for SPR
886 UPDATE mth_equip_output_stg stg
887 SET stg.err_code = stg.err_code || 'SPR '
888 WHERE EXISTS (SELECT * FROM (SELECT flk.lookup_code, flk.lookup_type,stg.reading_time,stg.qty_scrap,stg.scrap_reason_code
889 FROM fnd_lookups flk,
890 mth_equip_output_stg stg
891 WHERE stg.scrap_reason_code = flk.lookup_code (+)
892 AND flk.lookup_type(+) = 'MTH_SCRAP_REASON') dtr
893 WHERE ((stg.qty_scrap IS NULL OR stg.qty_scrap <= 0) AND stg.scrap_reason_code IS NOT NULL) OR
894 ( dtr.lookup_code IS NULL AND stg.scrap_reason_code IS NOT NULL OR
895 stg.scrap_reason_code <> dtr.lookup_code)
896 AND dtr.reading_time = stg.reading_time
897 AND dtr.qty_scrap = stg.qty_scrap
898 AND dtr.scrap_reason_code = stg.scrap_reason_code
899 AND stg.processing_flag = v_processing_flag);
900 mth_util_pkg.log_msg('Number of rows with SPR in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
901
902 -- Validation for Future Time Date
903 UPDATE mth_equip_output_stg stg
904 SET stg.err_code = stg.err_code || 'FTD '
905 WHERE stg.reading_time > SYSDATE
906 AND stg.processing_flag = v_processing_flag;
907 mth_util_pkg.log_msg('Number of rows with FTD in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
908
909 -- Validation for ITR
910 UPDATE mth_equip_output_stg stg
911 SET stg.err_code = stg.err_code || 'ITR '
912 WHERE NOT EXISTS ( SELECT * FROM ( SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.to_date
913 FROM mth_workday_shifts_d mds,
914 mth_equip_output_stg stg,
915 mth_equipment_shifts_d mes,
916 mth_equipments_d med
917 WHERE stg.shift_workday_fk = mds.shift_workday_pk
918 AND stg.equipment_fk = med.equipment_pk
919 AND mds.shift_workday_pk_key = mes.shift_workday_fk_key
920 AND med.equipment_pk_key = mes.equipment_fk_key
921 AND stg.reading_time >= mes.from_date
922 AND stg.reading_time <= mes.to_date) itr
923 WHERE itr.shift_workday_pk = stg.shift_workday_fk
924 AND itr.equipment_pk = stg.equipment_fk
925 AND stg.reading_time >= itr.from_date
926 AND stg.reading_time <= itr.to_date
927 AND stg.processing_flag = v_processing_flag);
928 mth_util_pkg.log_msg('Number of rows with ITR in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
929
930 -- Validation for Duplicate Output
931
932 UPDATE mth_equip_output_stg stg
933 SET stg.err_code = stg.err_code || 'DOP '
934 WHERE EXISTS (SELECT * FROM (SELECT meo.reading_time,med.equipment_pk,wds.shift_workday_pk
935 FROM mth_equip_output meo,
936 mth_equip_output_stg stg,
937 mth_equipments_d med,
938 mth_workday_shifts_d wds
939 WHERE med.equipment_pk_key = meo.equipment_fk_key
943 AND meo.reading_time = stg.reading_time) dop
940 AND wds.shift_workday_pk_key = meo.shift_workday_fk_key
941 AND med.equipment_pk = stg.equipment_fk
942 AND wds.shift_workday_pk = stg.shift_workday_fk
944 WHERE dop.reading_time = stg.reading_time
945 AND dop.equipment_pk = stg.equipment_fk
946 AND dop.shift_workday_pk = stg.shift_workday_fk
947 AND stg.processing_flag = v_processing_flag );
948 mth_util_pkg.log_msg('Number of rows with DOP in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
949
950 --Insert records into mth_equip_output_err
951 INSERT INTO mth_equip_output_err(equipment_fk,
952 item_fk,
953 shift_workday_fk,
954 workorder_fk,
955 reading_time,
956 qty_completed,
957 qty_scrap,
958 qty_rejected,
959 qty_rework,
960 qty_uom,
961 qty_good,
962 qty_output,
963 system_fk,
964 recipe_version,
965 recipe_num,
966 segment_fk,
967 user_dim1_fk,
968 user_dim2_fk,
969 user_dim3_fk,
970 user_dim4_fk,
971 user_dim5_fk,
972 user_attr1,
973 user_attr2,
974 user_attr3,
975 user_attr4,
976 user_attr5,
977 user_measure1,
978 user_measure2,
979 user_measure3,
980 user_measure4,
981 user_measure5,
982 scrap_reason_code,
983 reprocess_ready_yn,
984 err_code)
985 (SELECT equipment_fk,
986 item_fk,
987 shift_workday_fk,
988 workorder_fk,
989 reading_time,
990 qty_completed,
991 qty_scrap,
992 qty_rejected,
993 qty_rework,
994 qty_uom,
995 qty_good,
996 qty_output,
997 system_fk,
998 recipe_version,
999 recipe_num,
1000 segment_fk,
1001 user_dim1_fk,
1002 user_dim2_fk,
1003 user_dim3_fk,
1004 user_dim4_fk,
1005 user_dim5_fk,
1006 user_attr1,
1007 user_attr2,
1008 user_attr3,
1009 user_attr4,
1010 user_attr5,
1011 user_measure1,
1012 user_measure2,
1013 user_measure3,
1014 user_measure4,
1015 user_measure5,
1016 scrap_reason_code,
1017 'N',
1018 err_code
1019 FROM mth_equip_output_stg
1020 WHERE err_code IS NOT NULL);
1021 mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1022
1023 --Insert records into MTH_EQUIP_OUTPUT table
1024 INSERT INTO mth_equip_output( equipment_fk_key,
1025 item_fk_key,
1026 shift_workday_fk_key,
1027 workorder_fk_key,
1028 reading_time,
1029 qty_completed,
1030 qty_scrap,
1031 qty_rejected,
1032 qty_rework,
1033 qty_uom,
1034 qty_good,
1035 qty_output,
1036 system_fk_key,
1037 recipe_version,
1038 recipe_num,
1039 segment_fk_key,
1043 user_dim4_fk_key,
1040 user_dim1_fk_key,
1041 user_dim2_fk_key,
1042 user_dim3_fk_key,
1044 user_dim5_fk_key,
1045 user_attr1 ,
1046 user_attr2 ,
1047 user_attr3 ,
1048 user_attr4 ,
1049 user_attr5 ,
1050 user_measure1 ,
1051 user_measure2 ,
1052 user_measure3 ,
1053 user_measure4 ,
1054 user_measure5 ,
1055 creation_date,
1056 last_update_date,
1057 creation_system_id,
1058 last_update_system_id,
1059 created_by,
1060 last_updated_by,
1061 last_update_login,
1062 hour_fk_key )
1063 (SELECT med.equipment_pk_key ,
1064 mid.item_pk_key ,
1065 wds.shift_workday_pk_key ,
1066 mps.workorder_pk_key ,
1067 stg.reading_time ,
1068 stg.qty_completed,
1069 stg.qty_scrap,
1070 stg.qty_rejected,
1071 stg.qty_rework,
1072 stg.qty_uom,
1073 stg.qty_good,
1074 stg.qty_output,
1075 Nvl(mss.system_pk_key,v_unassigned_val) ,
1076 stg.recipe_version ,
1077 stg.recipe_num,
1078 msf.segment_pk_key,
1079 mue1.ENTITY_PK_KEY ,
1080 mue2.ENTITY_PK_KEY ,
1081 mue3.ENTITY_PK_KEY ,
1082 mue4.ENTITY_PK_KEY ,
1083 mue5.ENTITY_PK_KEY ,
1084 stg.USER_ATTR1 ,
1085 stg.USER_ATTR2 ,
1086 stg.USER_ATTR3 ,
1087 stg.USER_ATTR4 ,
1088 stg.USER_ATTR5 ,
1089 stg.USER_MEASURE1 ,
1090 stg.USER_MEASURE2 ,
1091 stg.USER_MEASURE3 ,
1092 stg.USER_MEASURE4 ,
1093 stg.USER_MEASURE5 ,
1094 v_log_date,
1095 v_log_date,
1096 v_unassigned_val,
1097 v_unassigned_val,
1098 null,
1099 null,
1100 null,
1101 mhd.hour_pk_key
1102 FROM mth_equip_output_stg stg,
1103 mth_equipments_d med,
1104 mth_workday_shifts_d wds,
1105 mth_items_d mid,
1106 mth_production_segments_f msf,
1107 mth_production_schedules_f mps,
1108 mth_systems_setup mss,
1109 mth_user_dim_entities_mst mue1,
1110 mth_user_dim_entities_mst mue2,
1111 mth_user_dim_entities_mst mue3,
1112 mth_user_dim_entities_mst mue4,
1113 mth_user_dim_entities_mst mue5,
1114 fnd_lookups lkp,
1115 mth_hour_d mhd
1116 WHERE stg.EQUIPMENT_FK = med.EQUIPMENT_PK (+)
1117 AND stg.SHIFT_WORKDAY_FK = wds.SHIFT_WORKDAY_PK (+)
1118 AND stg.ITEM_FK = mid.ITEM_PK (+)
1119 AND stg.WORKORDER_FK = mps.WORKORDER_PK (+)
1120 AND stg.SEGMENT_FK = msf.SEGMENT_PK (+)
1121 AND stg.reading_time BETWEEN mhd.from_time AND mhd.to_time
1122 AND NVL (stg.SYSTEM_FK , v_unassigned_val) = mss.SYSTEM_PK (+)
1123 AND stg.USER_DIM1_FK = mue1.ENTITY_PK (+)
1124 AND stg.USER_DIM2_FK = mue2.ENTITY_PK (+)
1125 AND stg.USER_DIM3_FK = mue3.ENTITY_PK (+)
1126 AND stg.USER_DIM4_FK = mue4.ENTITY_PK (+)
1127 AND stg.USER_DIM5_FK = mue5.ENTITY_PK (+)
1128 AND lkp.LOOKUP_TYPE (+) = 'MTH_SCRAP_REASON'
1132 mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1129 AND stg.SCRAP_REASON_CODE = lkp.LOOKUP_CODE (+)
1130 AND stg.err_code IS NULL
1131 AND stg.processing_flag = v_processing_flag);
1133
1134 -- Insert into tag reason readings
1135 INSERT INTO MTH_TAG_REASON_READINGS (REASON_TYPE,
1136 EQUIPMENT_FK_KEY,
1137 FROM_DATE,
1138 To_DATE,
1139 REASON_CODE,
1140 CREATION_DATE,
1141 LAST_UPDATE_DATE,
1142 CREATION_SYSTEM_ID,
1143 LAST_UPDATE_SYSTEM_ID,
1144 CREATED_BY,
1145 LAST_UPDATE_LOGIN,
1146 LAST_UPDATED_BY,
1147 READING_TIME)
1148 (SELECT 2 reason_type,
1149 med.equipment_pk_key,
1150 stg.reading_time,
1151 stg.reading_time,
1152 stg.scrap_reason_code,
1153 v_log_date,
1154 v_log_date,
1155 v_unassigned_val,
1156 v_unassigned_val,
1157 NULL,
1158 NULL,
1159 NULL,
1160 stg.reading_time
1161 FROM mth_equip_output_stg stg,
1162 mth_equipments_d med
1163 WHERE med.equipment_pk = stg.equipment_fk
1164 AND med.status = 'ACTIVE'
1165 AND stg.processing_flag = v_processing_flag
1166 AND stg.ERR_CODE IS NULL
1167 AND stg.qty_scrap IS NOT NULL);
1168
1169 mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_REASON_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1170
1171 mth_util_pkg.truncate_table_partition('MTH_EQUIP_OUTPUT_STG',v_processing_flag);
1172
1173 mth_util_pkg.log_msg('INIT_OUTPUT_FROM_CSV end', mth_util_pkg.G_DBG_PROC_FUN_END);
1174
1175 EXCEPTION
1176 WHEN OTHERS THEN
1177 --Call logging API and then throw exception
1178 mth_util_pkg.log_msg('Exception OTHERS in INIT_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
1179 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
1180 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
1181 RAISE;
1182 END;
1183
1184 /*******************************************************************************
1185 * Procedure :INCR_OUTPUT_FROM_CSV *
1186 * Description :This procedure is used for calculating the output *
1187 * in INCR mode from CSV *
1188 * File Name :MTHEQOPB.PLS *
1189 * Visibility :Private *
1190 * Parameters : *
1191 *******************************************************************************/
1192 PROCEDURE INCR_OUTPUT_FROM_CSV IS
1193 v_log_date DATE;
1194 v_unassigned_val VARCHAR2(30);
1195 v_processing_flag NUMBER;
1196 BEGIN
1197 mth_util_pkg.log_msg('INCR_OUTPUT_FROM_CSV start', mth_util_pkg.G_DBG_PROC_FUN_START);
1198
1199 -- Initialize default parameters
1200 v_log_date := sysdate;
1201 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
1202
1203 --Ensuring the functionality of ES map is introduced before the staging to fact is executed.
1204 INSERT INTO mth_equip_output_stg(equipment_fk,
1205 item_fk,
1206 shift_workday_fk,
1207 workorder_fk,
1208 reading_time,
1209 qty_completed,
1210 qty_scrap,
1211 qty_rejected,
1212 qty_rework,
1213 qty_uom,
1214 qty_good,
1215 qty_output,
1216 system_fk,
1217 recipe_version,
1218 recipe_num,
1219 segment_fk,
1220 user_dim1_fk,
1221 user_dim2_fk,
1222 user_dim3_fk,
1223 user_dim4_fk,
1224 user_dim5_fk,
1225 user_attr1,
1226 user_attr2,
1227 user_attr3,
1228 user_attr4,
1229 user_attr5,
1230 user_measure1,
1231 user_measure2,
1235 scrap_reason_code)
1232 user_measure3,
1233 user_measure4,
1234 user_measure5,
1236 (SELECT equipment_fk,
1237 item_fk,
1238 shift_workday_fk,
1239 workorder_fk,
1240 reading_time,
1241 qty_completed,
1242 qty_scrap,
1243 qty_rejected,
1244 qty_rework,
1245 qty_uom,
1246 qty_good,
1247 qty_output,
1248 system_fk,
1249 recipe_version,
1250 recipe_num,
1251 segment_fk,
1252 user_dim1_fk,
1253 user_dim2_fk,
1254 user_dim3_fk,
1255 user_dim4_fk,
1256 user_dim5_fk,
1257 user_attr1,
1258 user_attr2,
1259 user_attr3,
1260 user_attr4,
1261 user_attr5,
1262 user_measure1,
1263 user_measure2,
1264 user_measure3,
1265 user_measure4,
1266 user_measure5,
1267 scrap_reason_code
1268 FROM mth_equip_output_err
1269 WHERE reprocess_ready_yn = 'Y');
1270 mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT_STG from error table - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1271
1272 --delete data from the output error table
1273 DELETE FROM MTH_EQUIP_OUTPUT_ERR
1274 WHERE REPROCESS_READY_YN = 'Y';
1275 mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_OUTPUT_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1276
1277 mth_util_pkg.switch_column_default_value('MTH_EQUIP_OUTPUT_STG',v_processing_flag);
1278
1279 --Execute all validations on csv records
1280
1281 -- Validation for Invalid Item
1282 UPDATE mth_equip_output_stg stg
1283 SET stg.err_code = stg.err_code || 'ITM '
1284 WHERE NOT EXISTS ( SELECT * FROM ( SELECT mid.item_pk_key, mid.item_pk
1285 FROM mth_items_d mid,
1286 mth_equip_output_stg stg
1287 WHERE mid.item_pk = stg.item_fk) itm
1288 WHERE itm.item_pk = stg.item_fk
1289 AND stg.processing_flag = v_processing_flag );
1290 mth_util_pkg.log_msg('Number of rows with ITM in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1291
1292 -- Validation for Invalid Segment
1293 UPDATE mth_equip_output_stg stg
1294 SET stg.err_code = stg.err_code || 'SEG '
1295 WHERE NOT EXISTS ( SELECT * FROM ( SELECT msf.segment_pk_key, msf.segment_pk
1296 FROM mth_production_segments_f msf,
1297 mth_equip_output_stg stg
1298 WHERE msf.segment_pk = Nvl(stg.segment_fk,msf.segment_pk)) seg
1299 WHERE seg.segment_pk = Nvl(stg.segment_fk,seg.segment_pk)
1300 AND stg.processing_flag = v_processing_flag );
1301 mth_util_pkg.log_msg('Number of rows with SEG in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1302
1303 -- Validation for invalid work order
1304 UPDATE mth_equip_output_stg stg
1305 SET stg.err_code = stg.err_code || 'WKO '
1306 WHERE stg.workorder_fk IS NOT NULL
1307 AND NOT EXISTS ( SELECT * FROM ( SELECT mps.workorder_pk,mps.workorder_pk_key
1308 FROM mth_production_schedules_f mps,
1309 mth_equip_output_stg stg
1310 WHERE stg.workorder_fk = mps.workorder_pk(+)
1311 AND stg.workorder_fk IS NOT NULL) wko
1312 WHERE wko.workorder_pk = stg.workorder_fk
1313 AND stg.processing_flag = v_processing_flag);
1314 mth_util_pkg.log_msg('Number of rows with WKO in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1315
1316 -- Validation for Invalid Equipment
1317 UPDATE mth_equip_output_stg stg
1318 SET stg.err_code = stg.err_code || 'EQP '
1319 WHERE NOT EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
1320 FROM mth_equipments_d med,
1321 mth_equip_output_stg stg
1322 WHERE med.equipment_pk = stg.equipment_fk) eqp
1323 WHERE eqp.equipment_pk = stg.equipment_fk
1324 AND stg.processing_flag = v_processing_flag );
1328 UPDATE mth_equip_output_stg stg
1325 mth_util_pkg.log_msg('Number of rows with EQP in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1326
1327 -- Validation for Inactive Equipment
1329 SET stg.err_code = stg.err_code || 'IEQ '
1330 WHERE EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
1331 FROM mth_equipments_d med,
1332 mth_equip_output_stg stg
1333 WHERE med.equipment_pk = stg.equipment_fk
1334 AND Nvl(med.status,'NULL') <> 'ACTIVE') eqp
1335 WHERE eqp.equipment_pk = stg.equipment_fk
1336 AND stg.processing_flag = v_processing_flag );
1337 mth_util_pkg.log_msg('Number of rows with IEQ in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1338
1339 -- Validation for Duplicate record
1340 UPDATE mth_equip_output_stg stg
1341 SET stg.err_code = stg.err_code || 'DUP '
1342 WHERE EXISTS ( SELECT * FROM ( SELECT equipment_fk,shift_workday_fk,reading_time,item_fk,workorder_fk,segment_fk,Count(equipment_fk) cnt
1343 FROM mth_equip_output_stg
1344 GROUP BY equipment_fk,shift_workday_fk,reading_time,item_fk,workorder_fk,segment_fk) dup
1345 WHERE dup.cnt>1
1346 AND dup.equipment_fk = stg.equipment_fk
1347 AND dup.shift_workday_fk = stg.shift_workday_fk
1348 AND dup.reading_time = stg.reading_time
1349 AND dup.item_fk = stg.item_fk
1350 AND dup.workorder_fk = stg.workorder_fk
1351 AND dup.segment_fk = stg.segment_fk
1352 AND stg.processing_flag = v_processing_flag );
1353 mth_util_pkg.log_msg('Number of rows with DUP in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1354
1355 -- Validation for invalid shift
1356 UPDATE mth_equip_output_stg stg
1357 SET stg.err_code = stg.err_code || 'WDS '
1358 WHERE stg.shift_workday_fk IS NOT NULL
1359 AND NOT EXISTS ( SELECT * FROM ( SELECT mds.shift_workday_pk
1360 FROM mth_workday_shifts_d mds,
1361 mth_equip_output_stg stg
1362 WHERE stg.shift_workday_fk = mds.shift_workday_pk(+)
1363 AND stg.shift_workday_fk IS NOT NULL) wds
1364 WHERE wds.shift_workday_pk = stg.shift_workday_fk
1365 AND stg.processing_flag = v_processing_flag);
1366 mth_util_pkg.log_msg('Number of rows with WDS in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1367
1368 -- Validation for Null Work Day Shift
1369 UPDATE mth_equip_output_stg stg
1370 SET stg.err_code = stg.err_code || 'NWDS '
1371 WHERE stg.shift_workday_fk IS NULL
1372 AND stg.processing_flag = v_processing_flag;
1373 mth_util_pkg.log_msg('Number of rows with NWDS in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1374
1375 -- Validation for user dimension 1
1376 UPDATE mth_equip_output_stg stg
1377 SET stg.err_code = stg.err_code || 'UD1 '
1378 WHERE stg.user_dim1_fk IS NOT NULL
1379 AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim1_fk
1380 FROM mth_user_dim_entities_mst mue,
1381 mth_equip_output_stg stg
1382 WHERE stg.user_dim1_fk = mue.entity_pk (+)
1383 AND stg.user_dim1_fk IS NOT NULL) ud1
1384 WHERE ud1.user_dim1_fk = stg.user_dim1_fk
1385 AND ud1.entity_pk IS NULL
1386 AND stg.processing_flag = v_processing_flag);
1387 mth_util_pkg.log_msg('Number of rows with UD1 in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1388
1389 -- Validation for user dimension 2
1390 UPDATE mth_equip_output_stg stg
1391 SET stg.err_code = stg.err_code || 'UD2 '
1392 WHERE stg.user_dim2_fk IS NOT NULL
1393 AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim2_fk
1394 FROM mth_user_dim_entities_mst mue,
1395 mth_equip_output_stg stg
1396 WHERE stg.user_dim2_fk = mue.entity_pk (+)
1397 AND stg.user_dim2_fk IS NOT NULL) ud2
1398 WHERE ud2.user_dim2_fk = stg.user_dim2_fk
1399 AND ud2.entity_pk IS NULL
1400 AND stg.processing_flag = v_processing_flag);
1401 mth_util_pkg.log_msg('Number of rows with UD2 in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1402
1403 -- Validation for user dimension 3
1404 UPDATE mth_equip_output_stg stg
1405 SET stg.err_code = stg.err_code || 'UD3 '
1406 WHERE stg.user_dim3_fk IS NOT NULL
1407 AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim3_fk
1408 FROM mth_user_dim_entities_mst mue,
1409 mth_equip_output_stg stg
1410 WHERE stg.user_dim3_fk = mue.entity_pk (+)
1411 AND stg.user_dim3_fk IS NOT NULL) ud3
1412 WHERE ud3.user_dim3_fk = stg.user_dim3_fk
1413 AND ud3.entity_pk IS NULL
1414 AND stg.processing_flag = v_processing_flag);
1418 UPDATE mth_equip_output_stg stg
1415 mth_util_pkg.log_msg('Number of rows with UD3 in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1416
1417 -- Validation for user dimension 4
1419 SET stg.err_code = stg.err_code || 'UD4 '
1420 WHERE stg.user_dim4_fk IS NOT NULL
1421 AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim4_fk
1422 FROM mth_user_dim_entities_mst mue,
1423 mth_equip_output_stg stg
1424 WHERE stg.user_dim4_fk = mue.entity_pk (+)
1425 AND stg.user_dim4_fk IS NOT NULL) ud4
1426 WHERE ud4.user_dim4_fk = stg.user_dim4_fk
1427 AND stg.processing_flag = v_processing_flag
1428 AND ud4.entity_pk IS NULL);
1429 mth_util_pkg.log_msg('Number of rows with UD4 in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1430
1431 -- Validation for user dimension 5
1432 UPDATE mth_equip_output_stg stg
1433 SET stg.err_code = stg.err_code || 'UD5 '
1434 WHERE stg.user_dim5_fk IS NOT NULL
1435 AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim5_fk
1436 FROM mth_user_dim_entities_mst mue,
1437 mth_equip_output_stg stg
1438 WHERE stg.user_dim5_fk = mue.entity_pk (+)
1439 AND stg.user_dim5_fk IS NOT NULL) ud5
1440 WHERE ud5.user_dim5_fk = stg.user_dim5_fk
1441 AND stg.processing_flag = v_processing_flag
1442 AND ud5.entity_pk IS NULL);
1443 mth_util_pkg.log_msg('Number of rows with UD5 in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1444
1445 -- Validation for SPR
1446 UPDATE mth_equip_output_stg stg
1447 SET stg.err_code = stg.err_code || 'SPR '
1448 WHERE EXISTS (SELECT * FROM (SELECT flk.lookup_code, flk.lookup_type,stg.reading_time,stg.qty_scrap,stg.scrap_reason_code
1449 FROM fnd_lookups flk,
1450 mth_equip_output_stg stg
1451 WHERE stg.scrap_reason_code = flk.lookup_code (+)
1452 AND flk.lookup_type(+) = 'MTH_SCRAP_REASON') dtr
1453 WHERE ((stg.qty_scrap IS NULL OR stg.qty_scrap <= 0) AND stg.scrap_reason_code IS NOT NULL) OR
1454 ( dtr.lookup_code IS NULL AND stg.scrap_reason_code IS NOT NULL OR
1455 stg.scrap_reason_code <> dtr.lookup_code)
1456 AND dtr.reading_time = stg.reading_time
1457 AND dtr.qty_scrap = stg.qty_scrap
1458 AND dtr.scrap_reason_code = stg.scrap_reason_code
1459 AND stg.processing_flag = v_processing_flag);
1460 mth_util_pkg.log_msg('Number of rows with SPR in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1461
1462 -- Validation for Future Time Date
1463 UPDATE mth_equip_output_stg stg
1464 SET stg.err_code = stg.err_code || 'FTD '
1465 WHERE stg.reading_time > SYSDATE
1466 AND stg.processing_flag = v_processing_flag;
1467 mth_util_pkg.log_msg('Number of rows with FTD in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1468
1469 -- Validation for ITR
1470 UPDATE mth_equip_output_stg stg
1471 SET stg.err_code = stg.err_code || 'ITR '
1472 WHERE NOT EXISTS ( SELECT * FROM ( SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.to_date
1473 FROM mth_workday_shifts_d mds,
1474 mth_equip_output_stg stg,
1475 mth_equipment_shifts_d mes,
1476 mth_equipments_d med
1477 WHERE stg.shift_workday_fk = mds.shift_workday_pk
1478 AND stg.equipment_fk = med.equipment_pk
1479 AND mds.shift_workday_pk_key = mes.shift_workday_fk_key
1480 AND med.equipment_pk_key = mes.equipment_fk_key
1481 AND stg.reading_time >= mes.from_date
1482 AND stg.reading_time <= mes.to_date) itr
1483 WHERE itr.shift_workday_pk = stg.shift_workday_fk
1484 AND itr.equipment_pk = stg.equipment_fk
1485 AND stg.reading_time >= itr.from_date
1486 AND stg.reading_time <= itr.to_date
1487 AND stg.processing_flag = v_processing_flag);
1488 mth_util_pkg.log_msg('Number of rows with ITR in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1489
1490 -- Validation for Duplicate Output
1491
1492 UPDATE mth_equip_output_stg stg
1493 SET stg.err_code = stg.err_code || 'DOP '
1494 WHERE EXISTS (SELECT * FROM (SELECT meo.reading_time,med.equipment_pk,wds.shift_workday_pk
1495 FROM mth_equip_output meo,
1496 mth_equip_output_stg stg,
1497 mth_equipments_d med,
1498 mth_workday_shifts_d wds
1499 WHERE med.equipment_pk_key = meo.equipment_fk_key
1500 AND wds.shift_workday_pk_key = meo.shift_workday_fk_key
1501 AND med.equipment_pk = stg.equipment_fk
1505 AND dop.equipment_pk = stg.equipment_fk
1502 AND wds.shift_workday_pk = stg.shift_workday_fk
1503 AND meo.reading_time = stg.reading_time) dop
1504 WHERE dop.reading_time = stg.reading_time
1506 AND dop.shift_workday_pk = stg.shift_workday_fk
1507 AND stg.processing_flag = v_processing_flag );
1508 mth_util_pkg.log_msg('Number of rows with DOP in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1509
1510 --Insert records into mth_equip_output_err
1511 INSERT INTO mth_equip_output_err(equipment_fk,
1512 item_fk,
1513 shift_workday_fk,
1514 workorder_fk,
1515 reading_time,
1516 qty_completed,
1517 qty_scrap,
1518 qty_rejected,
1519 qty_rework,
1520 qty_uom,
1521 qty_good,
1522 qty_output,
1523 system_fk,
1524 recipe_version,
1525 recipe_num,
1526 segment_fk,
1527 user_dim1_fk,
1528 user_dim2_fk,
1529 user_dim3_fk,
1530 user_dim4_fk,
1531 user_dim5_fk,
1532 user_attr1,
1533 user_attr2,
1534 user_attr3,
1535 user_attr4,
1536 user_attr5,
1537 user_measure1,
1538 user_measure2,
1539 user_measure3,
1540 user_measure4,
1541 user_measure5,
1542 scrap_reason_code,
1543 reprocess_ready_yn,
1544 err_code)
1545 (SELECT equipment_fk,
1546 item_fk,
1547 shift_workday_fk,
1548 workorder_fk,
1549 reading_time,
1550 qty_completed,
1551 qty_scrap,
1552 qty_rejected,
1553 qty_rework,
1554 qty_uom,
1555 qty_good,
1556 qty_output,
1557 system_fk,
1558 recipe_version,
1559 recipe_num,
1560 segment_fk,
1561 user_dim1_fk,
1562 user_dim2_fk,
1563 user_dim3_fk,
1564 user_dim4_fk,
1565 user_dim5_fk,
1566 user_attr1,
1567 user_attr2,
1568 user_attr3,
1569 user_attr4,
1570 user_attr5,
1571 user_measure1,
1572 user_measure2,
1573 user_measure3,
1574 user_measure4,
1575 user_measure5,
1576 scrap_reason_code,
1577 'N',
1578 err_code
1579 FROM mth_equip_output_stg
1580 WHERE err_code IS NOT NULL);
1581 mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1582
1583
1584 --Insert records into mth_equip_output
1585
1586 --Insert records into MTH_EQUIP_OUTPUT table
1587 INSERT INTO mth_equip_output( equipment_fk_key,
1588 item_fk_key,
1589 shift_workday_fk_key,
1590 workorder_fk_key,
1591 reading_time,
1592 qty_completed,
1593 qty_scrap,
1594 qty_rejected,
1595 qty_rework,
1596 qty_uom,
1597 qty_good,
1598 qty_output,
1599 system_fk_key,
1600 recipe_version,
1601 recipe_num,
1602 segment_fk_key,
1603 user_dim1_fk_key,
1604 user_dim2_fk_key,
1608 user_attr1 ,
1605 user_dim3_fk_key,
1606 user_dim4_fk_key,
1607 user_dim5_fk_key,
1609 user_attr2 ,
1610 user_attr3 ,
1611 user_attr4 ,
1612 user_attr5 ,
1613 user_measure1 ,
1614 user_measure2 ,
1615 user_measure3 ,
1616 user_measure4 ,
1617 user_measure5 ,
1618 creation_date,
1619 last_update_date,
1620 creation_system_id,
1621 last_update_system_id,
1622 created_by,
1623 last_updated_by,
1624 last_update_login,
1625 hour_fk_key )
1626 (SELECT med.equipment_pk_key ,
1627 mid.item_pk_key ,
1628 wds.shift_workday_pk_key ,
1629 mps.workorder_pk_key ,
1630 stg.reading_time ,
1631 stg.qty_completed,
1632 stg.qty_scrap,
1633 stg.qty_rejected,
1634 stg.qty_rework,
1635 stg.qty_uom,
1636 stg.qty_good,
1637 stg.qty_output,
1638 Nvl(mss.system_pk_key,v_unassigned_val) ,
1639 stg.recipe_version ,
1640 stg.recipe_num,
1641 msf.segment_pk_key,
1642 mue1.ENTITY_PK_KEY ,
1643 mue2.ENTITY_PK_KEY ,
1644 mue3.ENTITY_PK_KEY ,
1645 mue4.ENTITY_PK_KEY ,
1646 mue5.ENTITY_PK_KEY ,
1647 stg.USER_ATTR1 ,
1648 stg.USER_ATTR2 ,
1649 stg.USER_ATTR3 ,
1650 stg.USER_ATTR4 ,
1651 stg.USER_ATTR5 ,
1652 stg.USER_MEASURE1 ,
1653 stg.USER_MEASURE2 ,
1654 stg.USER_MEASURE3 ,
1655 stg.USER_MEASURE4 ,
1656 stg.USER_MEASURE5 ,
1657 v_log_date,
1658 v_log_date,
1659 v_unassigned_val,
1660 v_unassigned_val,
1661 null,
1662 null,
1663 null,
1664 mhd.hour_pk_key
1665 FROM mth_equip_output_stg stg,
1666 mth_equipments_d med,
1667 mth_workday_shifts_d wds,
1668 mth_items_d mid,
1669 mth_production_segments_f msf,
1670 mth_production_schedules_f mps,
1671 mth_systems_setup mss,
1672 mth_user_dim_entities_mst mue1,
1673 mth_user_dim_entities_mst mue2,
1674 mth_user_dim_entities_mst mue3,
1675 mth_user_dim_entities_mst mue4,
1676 mth_user_dim_entities_mst mue5,
1677 fnd_lookups lkp,
1678 mth_hour_d mhd
1679 WHERE stg.EQUIPMENT_FK = med.EQUIPMENT_PK (+)
1680 AND stg.SHIFT_WORKDAY_FK = wds.SHIFT_WORKDAY_PK (+)
1681 AND stg.ITEM_FK = mid.ITEM_PK (+)
1682 AND stg.WORKORDER_FK = mps.WORKORDER_PK (+)
1683 AND stg.SEGMENT_FK = msf.SEGMENT_PK (+)
1684 AND stg.reading_time BETWEEN mhd.from_time AND mhd.to_time
1685 AND NVL (stg.SYSTEM_FK , v_unassigned_val) = mss.SYSTEM_PK (+)
1686 AND stg.USER_DIM1_FK = mue1.ENTITY_PK (+)
1687 AND stg.USER_DIM2_FK = mue2.ENTITY_PK (+)
1688 AND stg.USER_DIM3_FK = mue3.ENTITY_PK (+)
1689 AND stg.USER_DIM4_FK = mue4.ENTITY_PK (+)
1690 AND stg.USER_DIM5_FK = mue5.ENTITY_PK (+)
1691 AND lkp.LOOKUP_TYPE (+) = 'MTH_SCRAP_REASON'
1692 AND stg.SCRAP_REASON_CODE = lkp.LOOKUP_CODE (+)
1696
1693 AND stg.err_code IS NULL
1694 AND stg.processing_flag = v_processing_flag);
1695 mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1697 -- Insert into tag reason readings
1698 INSERT INTO MTH_TAG_REASON_READINGS (REASON_TYPE,
1699 EQUIPMENT_FK_KEY,
1700 FROM_DATE,
1701 To_DATE,
1702 REASON_CODE,
1703 CREATION_DATE,
1704 LAST_UPDATE_DATE,
1705 CREATION_SYSTEM_ID,
1706 LAST_UPDATE_SYSTEM_ID,
1707 CREATED_BY,
1708 LAST_UPDATE_LOGIN,
1709 LAST_UPDATED_BY,
1710 READING_TIME)
1711 (SELECT 2 reason_type,
1712 med.equipment_pk_key,
1713 stg.reading_time,
1714 stg.reading_time,
1715 stg.scrap_reason_code,
1716 v_log_date,
1717 v_log_date,
1718 v_unassigned_val,
1719 v_unassigned_val,
1720 NULL,
1721 NULL,
1722 NULL,
1723 stg.reading_time
1724 FROM mth_equip_output_stg stg,
1725 mth_equipments_d med
1726 WHERE med.equipment_pk = stg.equipment_fk
1727 AND med.status = 'ACTIVE'
1728 AND stg.processing_flag = v_processing_flag
1729 AND stg.ERR_CODE IS NULL
1730 AND stg.qty_scrap IS NOT NULL);
1731
1732 mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_REASON_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1733
1734 mth_util_pkg.truncate_table_partition('MTH_EQUIP_OUTPUT_STG',v_processing_flag);
1735
1736 mth_util_pkg.log_msg('INCR_OUTPUT_FROM_CSV end', mth_util_pkg.G_DBG_PROC_FUN_END);
1737 EXCEPTION
1738 WHEN OTHERS THEN
1739 --Call logging API and then throw exception
1740 mth_util_pkg.log_msg('Exception OTHERS in INCR_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
1741 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
1742 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
1743 RAISE;
1744 END;
1745
1746 /*******************************************************************************
1747 * Procedure :RECAL_OUTPUT_FROM_CSV *
1748 * Description :This procedure is used for calculating the output *
1749 * in RECAL mode *
1750 * File Name :MTHEQOPB.PLS *
1751 * Visibility :Private *
1752 * Parameters : p_recal_from_date : Recalculation from date *
1753 * p_recal_to_date : Recalculation to date *
1754 * p_equipment_pk_key : Equipment to recalculate *
1755 * p_plant_pk_key : Plant for recalculation *
1756 *******************************************************************************/
1757 PROCEDURE RECAL_OUTPUT_FROM_CSV(p_recal_from_date IN DATE, --Recalculation from date
1758 p_recal_to_date IN DATE DEFAULT NULL, --Recalculation to date
1759 p_equipment_pk_key IN NUMBER DEFAULT NULL,--Equipment to recalculate
1760 p_plant_pk_key IN NUMBER DEFAULT NULL)--Plant for recalculation
1761 IS
1762 GAP_IN_RECAL_CSV EXCEPTION;
1763 EQUIP_MIS_ERR EXCEPTION;
1764 SITE_MIS_ERR EXCEPTION;
1765 --VALIDATION_ERR EXCEPTION;
1766 l_count NUMBER;
1767 p_min_reading_time_csv DATE;
1768 p_max_reading_time_csv DATE;
1769 v_log_date DATE;
1770 v_unassigned_val VARCHAR2(30);
1771 v_count NUMBER;
1772 v_msg VARCHAR2(300);
1773
1774 CURSOR c_error_rows IS
1775 SELECT equipment_fk,
1776 item_fk,
1777 shift_workday_fk,
1778 workorder_fk,
1779 reading_time,
1780 qty_completed,
1781 qty_scrap,
1782 qty_rejected,
1783 qty_rework,
1784 qty_uom,
1785 qty_good,
1786 qty_output,
1787 segment_fk,
1788 user_dim1_fk,
1789 user_dim2_fk,
1790 user_dim3_fk,
1791 user_dim4_fk,
1792 user_dim5_fk,
1793 scrap_reason_code,
1794 err_code
1795 FROM mth_equip_output_stg
1796 WHERE err_code IS NOT NULL;
1797
1798 TYPE fetch_err_rows IS TABLE OF c_error_rows%ROWTYPE;
1799 l_err_rows fetch_err_rows;
1800
1801 BEGIN
1802 mth_util_pkg.log_msg('RECAL_OUTPUT_FROM_CSV start', mth_util_pkg.G_DBG_PROC_FUN_START);
1803
1804 -- Initialize default parameters
1805 v_log_date := sysdate;
1806 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
1807
1808 IF (p_plant_pk_key IS NOT NULL)
1812 FROM mth_plants_d mpd
1809 THEN
1810 SELECT Count(*)
1811 INTO l_count
1813 WHERE mpd.plant_pk_key = p_plant_pk_key;
1814 END IF;
1815
1816 IF l_count < 1
1817 THEN
1818 v_msg:=fnd_message.get_string('MTH','MTH_SITE_MIS_ERR');
1819 mth_util_pkg.log_msg(v_msg,mth_util_pkg.G_DBG_USER_ERROR);
1820 RAISE SITE_MIS_ERR;
1821 END IF;
1822
1823
1824 IF (p_equipment_pk_key is not null)
1825 THEN
1826 SELECT Count(*)
1827 INTO l_count
1828 FROM mth_equipments_d med,
1829 MTH_equip_output_stg stg
1830 WHERE med.equipment_pk = stg.equipment_fk
1831 AND med.equipment_pk_key = p_equipment_pk_key
1832 AND med.plant_fk_key = Nvl(p_plant_pk_key,med.plant_fk_key);
1833 END IF;
1834
1835 IF l_count < 1
1836 THEN
1837 v_msg:=fnd_message.get_string('MTH','MTH_EQUIP_MIS_ERR');
1838 mth_util_pkg.log_msg(v_msg,mth_util_pkg.G_DBG_USER_ERROR);
1839 RAISE EQUIP_MIS_ERR;
1840 END IF;
1841
1842
1843 SELECT Min(reading_time)
1844 INTO p_min_reading_time_csv
1845 FROM MTH_equip_output_stg stg
1846 WHERE stg.equipment_fk = Nvl((SELECT equipment_pk
1847 FROM mth_equipments_d
1848 WHERE equipment_pk_key = p_equipment_pk_key),stg.equipment_fk);
1849
1850 SELECT Max(reading_time)
1851 INTO p_max_reading_time_csv
1852 FROM MTH_equip_output_stg stg
1853 WHERE stg.equipment_fk = Nvl((SELECT equipment_pk
1854 FROM mth_equipments_d
1855 WHERE equipment_pk_key = p_equipment_pk_key),stg.equipment_fk);
1856
1857
1858 IF(p_min_reading_time_csv < p_recal_from_date OR p_max_reading_time_csv > p_recal_to_date)
1859 THEN
1860 v_msg:=fnd_message.get_string('MTH','MTH_GAP_IN_RECAL_CSV');
1861 mth_util_pkg.log_msg(v_msg,mth_util_pkg.G_DBG_USER_ERROR);
1862 RAISE GAP_IN_RECAL_CSV;
1863 END IF;
1864
1865 --Delete records from output table for the recalculation time range
1866 IF (p_equipment_pk_key IS NULL AND p_plant_pk_key IS NOT NULL) THEN
1867 DELETE
1868 FROM mth_equip_output
1869 WHERE reading_time >= p_recal_from_date
1870 AND reading_time <= Nvl(p_recal_to_date,SYSDATE)
1871 AND equipment_fk_key IN ( SELECT equipment_pk_key
1872 FROM mth_equipments_d
1873 WHERE plant_fk_key = p_plant_pk_key);
1874 ELSE
1875 DELETE
1876 FROM mth_equip_output
1877 WHERE reading_time >= p_recal_from_date
1878 AND reading_time <= Nvl(p_recal_to_date,SYSDATE)
1879 AND equipment_fk_key = NVL(p_equipment_pk_key,equipment_fk_key);
1880 END IF;
1881
1882 --Delete records from tag reason readings table for the recalculation time range
1883 IF (p_equipment_pk_key IS NULL AND p_plant_pk_key IS NOT NULL) THEN
1884 DELETE
1885 FROM mth_tag_reason_readings
1886 WHERE reading_time >= p_recal_from_date
1887 AND reading_time <= Nvl(p_recal_to_date,SYSDATE)
1888 AND REASON_TYPE=2
1889 AND equipment_fk_key IN ( SELECT equipment_pk_key
1890 FROM mth_equipments_d
1891 WHERE plant_fk_key = p_plant_pk_key);
1892 ELSE
1893 DELETE
1894 FROM mth_tag_reason_readings
1895 WHERE reading_time >= p_recal_from_date
1896 AND reading_time <= Nvl(p_recal_to_date,SYSDATE)
1897 AND REASON_TYPE=2
1898 AND equipment_fk_key = NVL(p_equipment_pk_key,equipment_fk_key);
1899 END IF;
1900
1901 --Execute all validations on csv records
1902
1903 -- Validation for Invalid Item
1904 UPDATE mth_equip_output_stg stg
1905 SET stg.err_code = stg.err_code || 'ITM '
1906 WHERE NOT EXISTS ( SELECT * FROM ( SELECT mid.item_pk_key, mid.item_pk
1907 FROM mth_items_d mid,
1908 mth_equip_output_stg stg
1909 WHERE mid.item_pk = stg.item_fk) itm
1910 WHERE itm.item_pk = stg.item_fk );
1911 mth_util_pkg.log_msg('Number of rows with ITM in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1912
1913 -- Validation for Invalid Segment
1914 UPDATE mth_equip_output_stg stg
1915 SET stg.err_code = stg.err_code || 'SEG '
1916 WHERE NOT EXISTS ( SELECT * FROM ( SELECT msf.segment_pk_key, msf.segment_pk
1917 FROM mth_production_segments_f msf,
1918 mth_equip_output_stg stg
1922
1919 WHERE msf.segment_pk = Nvl(stg.segment_fk,msf.segment_pk)) seg
1920 WHERE seg.segment_pk = Nvl(stg.segment_fk,seg.segment_pk));
1921 mth_util_pkg.log_msg('Number of rows with SEG in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1923 -- Validation for invalid work order
1924 UPDATE mth_equip_output_stg stg
1925 SET stg.err_code = stg.err_code || 'WKO '
1926 WHERE stg.workorder_fk IS NOT NULL
1927 AND NOT EXISTS ( SELECT * FROM ( SELECT mps.workorder_pk,mps.workorder_pk_key
1928 FROM mth_production_schedules_f mps,
1929 mth_equip_output_stg stg
1930 WHERE stg.workorder_fk = mps.workorder_pk(+)
1931 AND stg.workorder_fk IS NOT NULL) wko
1932 WHERE wko.workorder_pk = stg.workorder_fk);
1933 mth_util_pkg.log_msg('Number of rows with WKO in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1934
1935 -- Validation for Invalid Equipment
1936 UPDATE mth_equip_output_stg stg
1937 SET stg.err_code = stg.err_code || 'EQP '
1938 WHERE NOT EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
1939 FROM mth_equipments_d med,
1940 mth_equip_output_stg stg
1941 WHERE med.equipment_pk = stg.equipment_fk) eqp
1942 WHERE eqp.equipment_pk = stg.equipment_fk);
1943 mth_util_pkg.log_msg('Number of rows with EQP in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1944
1945 -- Validation for Inactive Equipment
1946 UPDATE mth_equip_output_stg stg
1947 SET stg.err_code = stg.err_code || 'IEQ '
1948 WHERE EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
1949 FROM mth_equipments_d med,
1950 mth_equip_output_stg stg
1951 WHERE med.equipment_pk = stg.equipment_fk
1952 AND Nvl(med.status,'NULL') <> 'ACTIVE') eqp
1953 WHERE eqp.equipment_pk = stg.equipment_fk );
1954 mth_util_pkg.log_msg('Number of rows with IEQ in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1955
1956 -- Validation for Duplicate record
1957 UPDATE mth_equip_output_stg stg
1958 SET stg.err_code = stg.err_code || 'DUP '
1959 WHERE EXISTS ( SELECT * FROM ( SELECT equipment_fk,shift_workday_fk,reading_time,item_fk,workorder_fk,segment_fk,Count(equipment_fk) cnt
1960 FROM mth_equip_output_stg
1961 GROUP BY equipment_fk,shift_workday_fk,reading_time,item_fk,workorder_fk,segment_fk) dup
1962 WHERE dup.cnt>1
1963 AND dup.equipment_fk = stg.equipment_fk
1964 AND dup.shift_workday_fk = stg.shift_workday_fk
1965 AND dup.reading_time = stg.reading_time
1966 AND dup.item_fk = stg.item_fk
1967 AND dup.workorder_fk = stg.workorder_fk
1968 AND dup.segment_fk = stg.segment_fk );
1969 mth_util_pkg.log_msg('Number of rows with DUP in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1970
1971 -- Validation for invalid shift
1972 UPDATE mth_equip_output_stg stg
1973 SET stg.err_code = stg.err_code || 'WDS '
1974 WHERE stg.shift_workday_fk IS NOT NULL
1975 AND NOT EXISTS ( SELECT * FROM ( SELECT mds.shift_workday_pk
1976 FROM mth_workday_shifts_d mds,
1977 mth_equip_output_stg stg
1978 WHERE stg.shift_workday_fk = mds.shift_workday_pk(+)
1979 AND stg.shift_workday_fk IS NOT NULL) wds
1980 WHERE wds.shift_workday_pk = stg.shift_workday_fk);
1981 mth_util_pkg.log_msg('Number of rows with WDS in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1982
1983 -- Validation for Null Work Day Shift
1984 UPDATE mth_equip_output_stg stg
1985 SET stg.err_code = stg.err_code || 'NWDS '
1986 WHERE stg.shift_workday_fk IS NULL;
1987 mth_util_pkg.log_msg('Number of rows with NWDS in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1988
1989 -- Validation for user dimension 1
1990 UPDATE mth_equip_output_stg stg
1991 SET stg.err_code = stg.err_code || 'UD1 '
1992 WHERE stg.user_dim1_fk IS NOT NULL
1993 AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim1_fk
1994 FROM mth_user_dim_entities_mst mue,
1995 mth_equip_output_stg stg
1996 WHERE stg.user_dim1_fk = mue.entity_pk (+)
1997 AND stg.user_dim1_fk IS NOT NULL) ud1
1998 WHERE ud1.user_dim1_fk = stg.user_dim1_fk
1999 AND ud1.entity_pk IS NULL);
2000 mth_util_pkg.log_msg('Number of rows with UD1 in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2001
2002 -- Validation for user dimension 2
2003 UPDATE mth_equip_output_stg stg
2004 SET stg.err_code = stg.err_code || 'UD2 '
2005 WHERE stg.user_dim2_fk IS NOT NULL
2006 AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim2_fk
2007 FROM mth_user_dim_entities_mst mue,
2011 WHERE ud2.user_dim2_fk = stg.user_dim2_fk
2008 mth_equip_output_stg stg
2009 WHERE stg.user_dim2_fk = mue.entity_pk (+)
2010 AND stg.user_dim2_fk IS NOT NULL) ud2
2012 AND ud2.entity_pk IS NULL);
2013 mth_util_pkg.log_msg('Number of rows with UD2 in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2014
2015 -- Validation for user dimension 3
2016 UPDATE mth_equip_output_stg stg
2017 SET stg.err_code = stg.err_code || 'UD3 '
2018 WHERE stg.user_dim3_fk IS NOT NULL
2019 AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim3_fk
2020 FROM mth_user_dim_entities_mst mue,
2021 mth_equip_output_stg stg
2022 WHERE stg.user_dim3_fk = mue.entity_pk (+)
2023 AND stg.user_dim3_fk IS NOT NULL) ud3
2024 WHERE ud3.user_dim3_fk = stg.user_dim3_fk
2025 AND ud3.entity_pk IS NULL);
2026 mth_util_pkg.log_msg('Number of rows with UD3 in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2027
2028 -- Validation for user dimension 4
2029 UPDATE mth_equip_output_stg stg
2030 SET stg.err_code = stg.err_code || 'UD4 '
2031 WHERE stg.user_dim4_fk IS NOT NULL
2032 AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim4_fk
2033 FROM mth_user_dim_entities_mst mue,
2034 mth_equip_output_stg stg
2035 WHERE stg.user_dim4_fk = mue.entity_pk (+)
2036 AND stg.user_dim4_fk IS NOT NULL) ud4
2037 WHERE ud4.user_dim4_fk = stg.user_dim4_fk
2038 AND ud4.entity_pk IS NULL);
2039 mth_util_pkg.log_msg('Number of rows with UD4 in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2040
2041 -- Validation for user dimension 5
2042 UPDATE mth_equip_output_stg stg
2043 SET stg.err_code = stg.err_code || 'UD5 '
2044 WHERE stg.user_dim5_fk IS NOT NULL
2045 AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim5_fk
2046 FROM mth_user_dim_entities_mst mue,
2047 mth_equip_output_stg stg
2048 WHERE stg.user_dim5_fk = mue.entity_pk (+)
2049 AND stg.user_dim5_fk IS NOT NULL) ud5
2050 WHERE ud5.user_dim5_fk = stg.user_dim5_fk
2051 AND ud5.entity_pk IS NULL);
2052 mth_util_pkg.log_msg('Number of rows with UD5 in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2053
2054 -- Validation for SPR
2055 UPDATE mth_equip_output_stg stg
2056 SET stg.err_code = stg.err_code || 'SPR '
2057 WHERE EXISTS (SELECT * FROM (SELECT flk.lookup_code, flk.lookup_type,stg.reading_time,stg.qty_scrap,stg.scrap_reason_code
2058 FROM fnd_lookups flk,
2059 mth_equip_output_stg stg
2060 WHERE stg.scrap_reason_code = flk.lookup_code (+)
2061 AND flk.lookup_type(+) = 'MTH_SCRAP_REASON') dtr
2062 WHERE ((stg.qty_scrap IS NULL OR stg.qty_scrap <= 0) AND stg.scrap_reason_code IS NOT NULL) OR
2063 ( dtr.lookup_code IS NULL AND stg.scrap_reason_code IS NOT NULL OR
2064 stg.scrap_reason_code <> dtr.lookup_code)
2065 AND dtr.reading_time = stg.reading_time
2066 AND dtr.qty_scrap = stg.qty_scrap
2067 AND dtr.scrap_reason_code = stg.scrap_reason_code);
2068 mth_util_pkg.log_msg('Number of rows with SPR in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2069
2070 -- Validation for Future Time Date
2071 UPDATE mth_equip_output_stg stg
2072 SET stg.err_code = stg.err_code || 'FTD '
2073 WHERE stg.reading_time > SYSDATE;
2074 mth_util_pkg.log_msg('Number of rows with FTD in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2075
2076 -- Validation for ITR
2077 UPDATE mth_equip_output_stg stg
2078 SET stg.err_code = stg.err_code || 'ITR '
2079 WHERE NOT EXISTS ( SELECT * FROM ( SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.to_date
2080 FROM mth_workday_shifts_d mds,
2081 mth_equip_output_stg stg,
2082 mth_equipment_shifts_d mes,
2083 mth_equipments_d med
2084 WHERE stg.shift_workday_fk = mds.shift_workday_pk
2085 AND stg.equipment_fk = med.equipment_pk
2086 AND mds.shift_workday_pk_key = mes.shift_workday_fk_key
2087 AND med.equipment_pk_key = mes.equipment_fk_key
2088 AND stg.reading_time >= mes.from_date
2089 AND stg.reading_time <= mes.to_date) itr
2090 WHERE itr.shift_workday_pk = stg.shift_workday_fk
2091 AND itr.equipment_pk = stg.equipment_fk
2092 AND stg.reading_time >= itr.from_date
2093 AND stg.reading_time <= itr.to_date);
2094 mth_util_pkg.log_msg('Number of rows with ITR in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2098 UPDATE mth_equip_output_stg stg
2095
2096 -- Validation for Duplicate Output
2097
2099 SET stg.err_code = stg.err_code || 'DOP '
2100 WHERE EXISTS (SELECT * FROM (SELECT meo.reading_time,med.equipment_pk,wds.shift_workday_pk
2101 FROM mth_equip_output meo,
2102 mth_equip_output_stg stg,
2103 mth_equipments_d med,
2104 mth_workday_shifts_d wds
2105 WHERE med.equipment_pk_key = meo.equipment_fk_key
2106 AND wds.shift_workday_pk_key = meo.shift_workday_fk_key
2107 AND med.equipment_pk = stg.equipment_fk
2108 AND wds.shift_workday_pk = stg.shift_workday_fk
2109 AND meo.reading_time = stg.reading_time) dop
2110 WHERE dop.reading_time = stg.reading_time
2111 AND dop.equipment_pk = stg.equipment_fk
2112 AND dop.shift_workday_pk = stg.shift_workday_fk );
2113 mth_util_pkg.log_msg('Number of rows with DOP in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2114
2115
2116 OPEN c_error_rows;
2117 LOOP
2118 FETCH c_error_rows BULK COLLECT INTO l_err_rows;
2119 EXIT WHEN c_error_rows%NOTFOUND;
2120 END LOOP;
2121 CLOSE c_error_rows;
2122
2123 IF l_err_rows.COUNT > 0
2124 THEN
2125 mth_util_pkg.log_msg('Following are the errored records in OUTPUT CSV Recalculation',mth_util_pkg.G_DBG_USER_ERROR);
2126 mth_util_pkg.log_msg('EQUIPMENT_FK,ITEM_FK,SHIFT_WORKDAY_FK,WORKORDER_FK,READING_TIME,QTY_COMPLETED,QTY_SCRAP,QTY_REJECTED,QTY_REWORK,QTY_UOM,QTY_GOOD,' ||
2127 'QTY_OUTPUT,SEGMENT_FK,USER_DIM1_FK,USER_DIM2_FK,USER_DIM3_FK,USER_DIM4_FK,USER_DIM5_FK,' ||
2128 'SCRAP_REASON_CODE,ERR_CODE',mth_util_pkg.G_DBG_USER_ERROR);
2129
2130 FOR i IN l_err_rows.FIRST..l_err_rows.LAST
2131 LOOP
2132 mth_util_pkg.log_msg(l_err_rows(i).equipment_fk||','||l_err_rows(i).item_fk||','||l_err_rows(i).shift_workday_fk||','||l_err_rows(i).workorder_fk||','||fnd_date.date_to_canonical(l_err_rows(i).reading_time)||','||
2133 l_err_rows(i).qty_completed||','||l_err_rows(i).qty_scrap||','||l_err_rows(i).qty_rejected||','||
2134 l_err_rows(i).qty_rework||','||l_err_rows(i).qty_uom||','||l_err_rows(i).qty_good||','||l_err_rows(i).qty_output||','||
2135 l_err_rows(i).segment_fk||','||l_err_rows(i).user_dim1_fk||','||l_err_rows(i).user_dim2_fk||','||l_err_rows(i).user_dim3_fk||','||
2136 l_err_rows(i).user_dim4_fk||','||l_err_rows(i).user_dim5_fk||','||l_err_rows(i).scrap_reason_code||','||l_err_rows(i).err_code
2137 ,mth_util_pkg.G_DBG_USER_ERROR);
2138 END LOOP;
2139 RAISE MTH_PROCESS_TXN_PKG.VALIDATION_ERR;
2140 END IF;
2141
2142
2143 --Insert records into mth_equip_output
2144
2145 --Insert records into MTH_EQUIP_OUTPUT table
2146 INSERT INTO mth_equip_output( equipment_fk_key,
2147 item_fk_key,
2148 shift_workday_fk_key,
2149 workorder_fk_key,
2150 reading_time,
2151 qty_completed,
2152 qty_scrap,
2153 qty_rejected,
2154 qty_rework,
2155 qty_uom,
2156 qty_good,
2157 qty_output,
2158 system_fk_key,
2159 recipe_version,
2160 recipe_num,
2161 segment_fk_key,
2162 user_dim1_fk_key,
2163 user_dim2_fk_key,
2164 user_dim3_fk_key,
2165 user_dim4_fk_key,
2166 user_dim5_fk_key,
2167 user_attr1 ,
2168 user_attr2 ,
2169 user_attr3 ,
2170 user_attr4 ,
2171 user_attr5 ,
2172 user_measure1 ,
2173 user_measure2 ,
2174 user_measure3 ,
2175 user_measure4 ,
2176 user_measure5 ,
2177 creation_date,
2178 last_update_date,
2179 creation_system_id,
2180 last_update_system_id,
2181 created_by,
2182 last_updated_by,
2183 last_update_login,
2184 hour_fk_key )
2185 (SELECT med.equipment_pk_key ,
2186 mid.item_pk_key ,
2187 wds.shift_workday_pk_key ,
2188 mps.workorder_pk_key ,
2189 stg.reading_time ,
2190 stg.qty_completed,
2194 stg.qty_uom,
2191 stg.qty_scrap,
2192 stg.qty_rejected,
2193 stg.qty_rework,
2195 stg.qty_good,
2196 stg.qty_output,
2197 Nvl(mss.system_pk_key,v_unassigned_val) ,
2198 stg.recipe_version ,
2199 stg.recipe_num,
2200 msf.segment_pk_key,
2201 mue1.ENTITY_PK_KEY ,
2202 mue2.ENTITY_PK_KEY ,
2203 mue3.ENTITY_PK_KEY ,
2204 mue4.ENTITY_PK_KEY ,
2205 mue5.ENTITY_PK_KEY ,
2206 stg.USER_ATTR1 ,
2207 stg.USER_ATTR2 ,
2208 stg.USER_ATTR3 ,
2209 stg.USER_ATTR4 ,
2210 stg.USER_ATTR5 ,
2211 stg.USER_MEASURE1 ,
2212 stg.USER_MEASURE2 ,
2213 stg.USER_MEASURE3 ,
2214 stg.USER_MEASURE4 ,
2215 stg.USER_MEASURE5 ,
2216 v_log_date,
2217 v_log_date,
2218 v_unassigned_val,
2219 v_unassigned_val,
2220 null,
2221 null,
2222 null,
2223 mhd.hour_pk_key
2224 FROM mth_equip_output_stg stg,
2225 mth_equipments_d med,
2226 mth_workday_shifts_d wds,
2227 mth_items_d mid,
2228 mth_production_segments_f msf,
2229 mth_production_schedules_f mps,
2230 mth_systems_setup mss,
2231 mth_user_dim_entities_mst mue1,
2232 mth_user_dim_entities_mst mue2,
2233 mth_user_dim_entities_mst mue3,
2234 mth_user_dim_entities_mst mue4,
2235 mth_user_dim_entities_mst mue5,
2236 fnd_lookups lkp,
2237 mth_hour_d mhd
2238 WHERE stg.EQUIPMENT_FK = med.EQUIPMENT_PK (+)
2239 AND stg.SHIFT_WORKDAY_FK = wds.SHIFT_WORKDAY_PK (+)
2240 AND stg.ITEM_FK = mid.ITEM_PK (+)
2241 AND stg.WORKORDER_FK = mps.WORKORDER_PK (+)
2242 AND stg.SEGMENT_FK = msf.SEGMENT_PK (+)
2243 AND stg.reading_time BETWEEN mhd.from_time AND mhd.to_time
2244 AND NVL (stg.SYSTEM_FK , v_unassigned_val) = mss.SYSTEM_PK (+)
2245 AND stg.USER_DIM1_FK = mue1.ENTITY_PK (+)
2246 AND stg.USER_DIM2_FK = mue2.ENTITY_PK (+)
2247 AND stg.USER_DIM3_FK = mue3.ENTITY_PK (+)
2248 AND stg.USER_DIM4_FK = mue4.ENTITY_PK (+)
2249 AND stg.USER_DIM5_FK = mue5.ENTITY_PK (+)
2250 AND lkp.LOOKUP_TYPE (+) = 'MTH_SCRAP_REASON'
2251 AND stg.SCRAP_REASON_CODE = lkp.LOOKUP_CODE (+)
2252 AND stg.err_code IS NULL);
2253 mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2254 v_count := SQL%ROWCOUNT;
2255
2256 -- Insert into tag reason readings
2257 INSERT INTO MTH_TAG_REASON_READINGS (REASON_TYPE,
2258 EQUIPMENT_FK_KEY,
2259 FROM_DATE,
2260 To_DATE,
2261 REASON_CODE,
2262 CREATION_DATE,
2263 LAST_UPDATE_DATE,
2264 CREATION_SYSTEM_ID,
2265 LAST_UPDATE_SYSTEM_ID,
2266 CREATED_BY,
2267 LAST_UPDATE_LOGIN,
2268 LAST_UPDATED_BY,
2269 READING_TIME)
2270 (SELECT 2 reason_type,
2271 med.equipment_pk_key,
2272 stg.reading_time,
2273 stg.reading_time,
2274 stg.scrap_reason_code,
2275 v_log_date,
2276 v_log_date,
2280 NULL,
2277 v_unassigned_val,
2278 v_unassigned_val,
2279 NULL,
2281 NULL,
2282 stg.reading_time
2283 FROM mth_equip_output_stg stg,
2284 mth_equipments_d med
2285 WHERE med.equipment_pk = stg.equipment_fk
2286 AND med.status = 'ACTIVE'
2287 AND stg.ERR_CODE IS NULL
2288 AND stg.qty_scrap IS NOT NULL);
2289
2290 mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_REASON_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2291
2292 IF (v_count > 0) THEN
2293 DELETE FROM MTH_EQUIP_OUTPUT_STG;
2294 END IF;
2295
2296 mth_util_pkg.log_msg('RECAL_OUTPUT_FROM_CSV end', mth_util_pkg.G_DBG_PROC_FUN_END);
2297 EXCEPTION
2298 WHEN EQUIP_MIS_ERR THEN
2299 --Call logging API and then throw exception
2300 mth_util_pkg.log_msg('Exception EQUIP_MIS_ERR in RECAL_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
2301 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2302 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2303 RAISE;
2304 WHEN SITE_MIS_ERR THEN
2305 --Call logging API and then throw exception
2306 mth_util_pkg.log_msg('Exception SITE_MIS_ERR in RECAL_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
2307 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2308 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2309 RAISE;
2310 WHEN GAP_IN_RECAL_CSV THEN
2311 --Call logging API and then throw exception
2312 mth_util_pkg.log_msg('Exception GAP_IN_RECAL_CSV in RECAL_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
2313 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2314 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2315 RAISE;
2316 WHEN MTH_PROCESS_TXN_PKG.VALIDATION_ERR THEN
2317 --Call logging API and then throw exception
2318 mth_util_pkg.log_msg('Exception VALIDATION_ERR in RECAL_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
2319 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2320 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2321 RAISE;
2322 WHEN OTHERS THEN
2323 --Call logging API and then throw exception
2324 mth_util_pkg.log_msg('Exception OTHERS in RECAL_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
2325 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2326 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2327 RAISE;
2328 END;
2329
2330
2331 /*******************************************************************************
2332 * Procedure :PROCESS_OUTPUT_SUMMARY *
2333 * Description :This procedure is the main procedure for output *
2334 * summary
2335 * File Name :MTHEQOPB.PLS *
2336 * Visibility :Public *
2337 * Parameters : p_fact_name : name of the fact table *
2338 * p_from_date : from_date for the run *
2339 * p_to_date : to_date for the run *
2340 * Modification log : *
2341 * Author Date Change *
2342 * Mandar Gijare 02-Sep-2011 Initial Creation *
2343 *******************************************************************************/
2344 PROCEDURE PROCESS_OUTPUT_SUMMARY(p_mode IN VARCHAR2, --INIT,INCR,RECAL
2345 p_recal_from_date IN TIMESTAMP , --Recalculation from date
2346 p_recal_to_date IN TIMESTAMP DEFAULT NULL, --Recalculation to date
2347 p_equipment_pk_key IN NUMBER DEFAULT NULL, --Equipment to recalculate
2348 p_plant_pk_key IN NUMBER DEFAULT NULL,
2349 p_ret_code OUT NOCOPY NUMBER
2350 )
2351 IS
2352 l_ret_code NUMBER;
2353 BEGIN
2354 l_ret_code := 0;
2355 mth_util_pkg.log_msg('PROCESS_OUTPUT_SUMMARY start', mth_util_pkg.G_DBG_PROC_FUN_START);
2356 mth_util_pkg.log_msg('p_mode = ' || p_mode , mth_util_pkg.G_DBG_PARAM_VAL);
2357 mth_util_pkg.log_msg('p_recal_from_date = ' || to_char(p_recal_from_date,'DD-MON-YYYY HH24:MI:SS') , mth_util_pkg.G_DBG_PARAM_VAL);
2358 mth_util_pkg.log_msg('p_recal_to_date = ' || to_char(p_recal_to_date,'DD-MON-YYYY HH24:MI:SS') , mth_util_pkg.G_DBG_PARAM_VAL);
2359 mth_util_pkg.log_msg('p_equipment_pk_key = ' || p_equipment_pk_key, mth_util_pkg.G_DBG_PARAM_VAL);
2360
2361 IF p_mode = 'INIT' THEN
2362 PROCESS_OUTPUT_SMMRY_INIT();
2363 ELSIF p_mode = 'INCR' THEN
2364 PROCESS_OUTPUT_SMMRY_INCR();
2365 ELSE
2366 PROCESS_OUTPUT_SMMRY_RECAL(
2367 p_recal_from_date,
2368 p_recal_to_date,
2369 p_equipment_pk_key,
2370 p_plant_pk_key
2371 );
2372 END IF;
2373
2374 p_ret_code := l_ret_code;
2375 mth_util_pkg.log_msg('PROCESS_OUTPUT_SUMMARY end', mth_util_pkg.G_DBG_PROC_FUN_END);
2376
2377 EXCEPTION
2378 WHEN OTHERS THEN
2379 l_ret_code := 2;
2380 p_ret_code := l_ret_code;
2384 RAISE;
2381 mth_util_pkg.log_msg('Exception OTHERS in PROCESS_OUTPUT_SUMMARY', mth_util_pkg.G_DBG_EXCEPTION);
2382 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2383 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2385 END PROCESS_OUTPUT_SUMMARY;
2386
2387 /*******************************************************************************
2388 * Procedure :PROCESS_OUTPUT_SMMRY_INIT *
2389 * Description :This procedure is used for calculating the output *
2390 * summary from output in INIT mode *
2391 * File Name :MTHEQOPB.PLS *
2392 * Visibility :Public *
2393 * Parameters : *
2394 * *
2395 * Modification log : *
2396 * Author Date Change *
2397 * Mandar Gijare 02-Sep-2011 Initial Creation *
2398 *******************************************************************************/
2399 PROCEDURE PROCESS_OUTPUT_SMMRY_INIT IS
2400 v_log_date DATE;
2401 v_ua_val VARCHAR2(30);
2402 v_run_log_to_date DATE;
2403 v_run_log_from_date DATE;
2404 BEGIN
2405 mth_util_pkg.log_msg('PROCESS_OUTPUT_SMMRY_INIT start', mth_util_pkg.G_DBG_PROC_FUN_START);
2406 -- Initialize default parameters
2407 v_log_date := sysdate;
2408 v_ua_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
2409
2410 -- Call mth_run_log_pre_load
2411 mth_util_pkg.mth_run_log_pre_load('MTH_EQUIP_OUTPUT_SUMMARY',v_ua_val,'INITIAL',NULL,0,v_log_date);
2412
2413 -- Call delete output summary to delete all data from MTH_EQUIP_OUTPUT_SUMMARY table as well update status summary table
2414 UPDATE MTH_EQUIP_STATUS_SUMMARY
2415 SET wo_item_count = null,
2416 required_hours = null;
2417 mth_util_pkg.log_msg('Number of rows updated in status summary - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2418
2419 DELETE FROM MTH_EQUIP_OUTPUT_SUMMARY;
2420 mth_util_pkg.log_msg('Number of rows deleted from output summary - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2421
2422 -- Get to and from date from run log
2423 mth_util_pkg.GET_RUN_LOG_DATES('MTH_EQUIP_OUTPUT_SUMMARY',NULL,NULL,NULL,v_run_log_from_date,v_run_log_to_date);
2424
2425 -- Process data from output to be inserted to output summary
2426 INSERT
2427 INTO
2428 MTH_EQUIP_OUTPUT_SUMMARY( EQUIPMENT_FK_KEY,
2429 ITEM_FK_KEY,
2430 WORKORDER_FK_KEY,
2431 SHIFT_WORKDAY_FK_KEY,
2432 HOUR_FK_KEY,
2433 QTY_COMPLETED,
2434 QTY_SCRAP,
2435 QTY_REJECTED,
2436 QTY_REWORK,
2437 QTY_GOOD,
2438 QTY_OUTPUT,
2439 SYSTEM_FK_KEY,
2440 CREATION_DATE,
2441 LAST_UPDATE_DATE,
2442 CREATION_SYSTEM_ID,
2443 LAST_UPDATE_SYSTEM_ID,
2444 LAST_UPDATE_LOGIN,
2445 LAST_UPDATED_BY,
2446 RECIPE_NUM,
2447 RECIPE_VERSION,
2448 SEGMENT_FK_KEY,
2449 STANDARD_RATE_1,
2450 ITEM_COST,
2451 RESOURCE_FK_KEY,
2452 RESOURCE_COST )
2453 ( SELECT meo.equipment_fk_key,
2454 meo.item_fk_key,
2455 Nvl(meo.workorder_fk_key,v_ua_val) workorder_fk_key,
2456 meo.shift_workday_fk_key,
2457 meo.hour_fk_key,
2458 Sum(meo.qty_completed) qty_completed,
2459 Sum(meo.qty_scrap) qty_scrap,
2460 Sum(meo.qty_rejected ) qty_rejected,
2461 Sum(meo.qty_rework) qty_rework,
2462 Sum(meo.qty_completed) qty_good,
2463 Sum(meo.qty_output) qty_output,
2464 v_ua_val,
2465 v_log_date,
2466 v_log_date,
2467 v_ua_val,
2468 v_ua_val,
2469 v_ua_val,
2470 v_ua_val,
2471 Nvl(meo.recipe_num,v_ua_val) recipe_num,
2472 Nvl(meo.recipe_version,v_ua_val) recipe_version,
2473 meo.segment_fk_key segment_fk_key,
2474 Min(srf.standard_rate_1) standard_rate_1,
2475 Min(mic.cost) item_cost,
2476 Min(med.level9_level_key) level9_level_key,
2477 Min(mrc.cost) resource_cost
2478 FROM mth_item_cost_mv mic,
2479 mth_resource_cost_mv mrc,
2480 mth_workday_shifts_d msg,
2481 mth_equipment_denorm_d med,
2482 mth_equip_standard_rates_f srf,
2483 mth_equip_output meo
2484 WHERE med.equipment_hierarchy_key = -2
2488 AND nvl(med.level9_level_key, v_ua_val) = mrc.resource_fk_key (+)
2485 AND med.equipment_fk_key is not null
2486 AND msg.from_date between med.equipment_effective_date
2487 and nvl(med.equipment_expiration_date , msg.from_date)
2489 AND med.equipment_fk_key = meo.equipment_fk_key
2490 AND meo.shift_workday_fk_key = msg.shift_workday_pk_key
2491 AND meo.item_fk_key = mic.item_fk_key (+)
2492 AND meo.equipment_fk_key = srf.equipment_fk_key(+)
2493 AND meo.item_fk_key = srf.item_fk_key(+)
2494 AND meo.shift_workday_fk_key = srf.shift_workday_fk_key(+)
2495 AND meo.last_update_date <= v_run_log_to_date
2496 GROUP BY meo.equipment_fk_key,
2497 meo.item_fk_key,
2498 meo.workorder_fk_key ,
2499 meo.shift_workday_fk_key,
2500 meo.recipe_version ,
2501 meo.recipe_num ,
2502 meo.segment_fk_key,
2503 meo.hour_fk_key );
2504
2505 -- Call the logging API to log the number of rows inserted
2506 mth_util_pkg.log_msg('Rows inserted in MTH_EQUIP_OUTPUT_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
2507
2508 UPDATE
2509 MTH_EQUIP_STATUS_SUMMARY mes
2510 SET (mes.WO_ITEM_COUNT,mes.REQUIRED_HOURS,
2511 mes.LAST_UPDATE_DATE,mes.LAST_UPDATE_SYSTEM_ID,
2512 mes.LAST_UPDATE_LOGIN,mes.LAST_UPDATED_BY)=
2513 (SELECT statrec.wo_item_count, statrec.required_time,
2514 statrec.last_update_date,statrec.last_update_system_id,
2515 statrec.last_update_login,statrec.last_updated_by
2516 FROM (SELECT meos.equipment_fk_key,
2517 meos.shift_workday_fk_key,
2518 sum(case when nvl(meos.qty_output,0) = 0 then
2519 nvl(meos.qty_completed,0) +
2520 case when nvl((meos.qty_rejected),0) = 0 then
2521 nvl(meos.qty_rework,0) + nvl(meos.qty_scrap,0)
2522 else meos.qty_rejected
2523 end
2524 else meos.qty_output
2525 end / meos.standard_rate_1) required_time,
2526 count(*) wo_item_count,
2527 meos.hour_fk_key,
2528 v_log_date last_update_date,
2529 v_ua_val last_update_system_id,
2530 v_ua_val last_update_login,
2531 v_ua_val last_updated_by
2532 FROM MTH_EQUIP_OUTPUT_SUMMARY meos
2533 WHERE meos.standard_rate_1 is NOT NULL
2534 AND meos.standard_rate_1 <> 0
2535 GROUP BY meos.equipment_fk_key,
2536 meos.shift_workday_fk_key,
2537 meos.hour_fk_key) statrec
2538 WHERE mes.EQUIPMENT_FK_KEY = statrec.EQUIPMENT_FK_KEY AND
2539 mes.SHIFT_WORKDAY_FK_KEY = statrec.SHIFT_WORKDAY_FK_KEY AND
2540 mes.HOUR_FK_KEY = statrec.HOUR_FK_KEY )
2541 WHERE EXISTS (SELECT 1
2542 FROM (SELECT meos.equipment_fk_key,
2543 meos.shift_workday_fk_key,
2544 sum(case when nvl(meos.qty_output,0) = 0 then
2545 nvl(meos.qty_completed,0) +
2546 case when nvl((meos.qty_rejected),0) = 0 then
2547 nvl(meos.qty_rework,0) + nvl(meos.qty_scrap,0)
2548 else meos.qty_rejected
2549 end
2550 else meos.qty_output
2551 end / meos.standard_rate_1) required_time,
2552 count(*) wo_item_count,
2553 meos.hour_fk_key,
2554 v_log_date last_update_date,
2555 v_ua_val last_update_system_id,
2556 v_ua_val last_update_login,
2557 v_ua_val last_updated_by
2558 FROM MTH_EQUIP_OUTPUT_SUMMARY meos
2559 WHERE meos.standard_rate_1 is NOT NULL
2560 AND meos.standard_rate_1 <> 0
2561 GROUP BY meos.equipment_fk_key,
2562 meos.shift_workday_fk_key,
2563 meos.hour_fk_key) meos
2567
2564 WHERE mes.EQUIPMENT_FK_KEY = meos.EQUIPMENT_FK_KEY
2565 AND mes.SHIFT_WORKDAY_FK_KEY = meos.SHIFT_WORKDAY_FK_KEY
2566 AND mes.HOUR_FK_KEY = meos.HOUR_FK_KEY);
2568 -- Call the logging API to log the number of rows updated
2569 mth_util_pkg.log_msg('Rows updated in MTH_EQUIP_STATUS_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
2570
2571 -- Call mth_run_log_post_load
2572 mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_OUTPUT_SUMMARY',v_ua_val);
2573
2574 mth_util_pkg.log_msg('PROCESS_OUTPUT_SMMRY_INIT end', mth_util_pkg.G_DBG_PROC_FUN_END);
2575 EXCEPTION
2576 WHEN OTHERS THEN
2577 --Call logging API and then throw exception
2578 mth_util_pkg.log_msg('Exception OTHERS in PROCESS_OUTPUT_SMMRY_INIT', mth_util_pkg.G_DBG_EXCEPTION);
2579 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2580 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2581 RAISE;
2582 END PROCESS_OUTPUT_SMMRY_INIT;
2583
2584 /*******************************************************************************
2585 * Procedure :PROCESS_OUTPUT_SMMRY_INCR *
2586 * Description :This procedure is used for calculating the output *
2587 * summary from output in INCR mode *
2588 * File Name :MTHEQOPB.PLS *
2589 * Visibility :Public *
2590 * Parameters : *
2591 * *
2592 * Modification log : *
2593 * Author Date Change *
2594 * Mandar Gijare 02-Sep-2011 Initial Creation *
2595 *******************************************************************************/
2596 PROCEDURE PROCESS_OUTPUT_SMMRY_INCR IS
2597 v_log_date DATE;
2598 v_ua_val VARCHAR2(30);
2599 v_run_log_to_date DATE;
2600 v_run_log_from_date DATE;
2601 BEGIN
2602 mth_util_pkg.log_msg('PROCESS_OUTPUT_SMMRY_INCR start', mth_util_pkg.G_DBG_PROC_FUN_START);
2603 -- Initialize default parameters
2604 v_log_date := sysdate;
2605 v_ua_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
2606
2607 -- Call mth_run_log_pre_load
2608 mth_util_pkg.mth_run_log_pre_load('MTH_EQUIP_OUTPUT_SUMMARY',v_ua_val,'INCR',NULL,0,v_log_date);
2609
2610 -- Get to and from date from run log
2611 mth_util_pkg.GET_RUN_LOG_DATES('MTH_EQUIP_OUTPUT_SUMMARY',NULL,NULL,NULL,v_run_log_from_date,v_run_log_to_date);
2612
2613 UPDATE
2614 MTH_EQUIP_STATUS_SUMMARY mes
2615 SET (mes.WO_ITEM_COUNT,mes.REQUIRED_HOURS,
2616 mes.LAST_UPDATE_DATE,mes.LAST_UPDATE_SYSTEM_ID,
2617 mes.LAST_UPDATE_LOGIN,mes.LAST_UPDATED_BY)=
2618 (SELECT (nvl(mes.WO_ITEM_COUNT,0) + nvl(statrec.wo_item_count,0)), (nvl(mes.REQUIRED_HOURS,0) + nvl(statrec.required_time,0)),
2619 statrec.last_update_date,statrec.last_update_system_id,
2620 statrec.last_update_login,statrec.last_updated_by
2621 FROM
2622 (SELECT aggr.equipment_fk_key equipment_fk_key,
2623 aggr.shift_workday_fk_key shift_workday_fk_key,
2624 aggr.hour_fk_key hour_fk_key,
2625 v_log_date last_update_date,
2626 null,
2627 v_ua_val last_update_system_id,
2628 null,
2629 v_ua_val last_update_login,
2630 v_ua_val last_updated_by,
2631 Sum((case when srf.standard_rate_1 is not null then
2632 case when nvl(aggr.qty_output,0) = 0 then
2633 nvl(aggr.qty_completed,0) +
2634 case when nvl((aggr.qty_rejected),0) = 0 then
2635 nvl(aggr.qty_rework,0) + nvl(aggr.qty_scrap,0)
2636 else aggr.qty_rejected
2637 end
2638 else aggr.qty_output
2639 end / srf.standard_rate_1
2640 end) ) required_time,
2641 Sum((CASE WHEN eos.equipment_fk_key IS NULL THEN 1 ELSE 0 END)) wo_item_count,
2642 null,
2643 null
2644 FROM mth_equip_output_summary eos,
2645 mth_equip_standard_rates_f srf,
2646 (SELECT meo.equipment_fk_key,
2647 meo.item_fk_key,
2648 Nvl(meo.workorder_fk_key,v_ua_val) workorder_fk_key,
2649 Sum(meo.qty_completed) qty_completed,
2650 Sum(meo.qty_scrap) qty_scrap,
2651 Sum(meo.qty_rejected) qty_rejected,
2652 Sum(meo.qty_rework) qty_rework,
2653 Sum(meo.qty_output) qty_output,
2654 meo.shift_workday_fk_key,
2655 Nvl(meo.recipe_version,v_ua_val) recipe_version,
2656 Nvl(meo.recipe_num,v_ua_val) recipe_num,
2657 meo.segment_fk_key segment_fk_key,
2658 meo.hour_fk_key
2659 FROM MTH_EQUIP_OUTPUT meo
2660 WHERE meo.last_update_date > v_run_log_from_date AND meo.last_update_date <= v_run_log_to_date
2661 GROUP BY meo.equipment_fk_key,
2662 meo.item_fk_key,
2663 meo.workorder_fk_key ,
2664 meo.shift_workday_fk_key,
2665 meo.recipe_version ,
2666 meo.recipe_num ,
2670 AND aggr.item_fk_key = eos.item_fk_key(+)
2667 meo.segment_fk_key,
2668 meo.hour_fk_key) aggr
2669 WHERE aggr.equipment_fk_key = eos.equipment_fk_key(+)
2671 AND aggr.workorder_fk_key = eos.workorder_fk_key(+)
2672 AND aggr.shift_workday_fk_key = eos.shift_workday_fk_key(+)
2673 AND aggr.recipe_version = eos.recipe_version(+)
2674 AND aggr.recipe_num = eos.recipe_num(+)
2675 AND aggr.segment_fk_key = eos.segment_fk_key(+)
2676 AND aggr.hour_fk_key = eos.hour_fk_key(+)
2677 AND aggr.equipment_fk_key = srf.equipment_fk_key(+)
2678 AND aggr.item_fk_key = srf.item_fk_key(+)
2679 AND aggr.shift_workday_fk_key = srf.shift_workday_fk_key(+)
2680 AND srf.standard_rate_1 IS NOT NULL
2681 AND srf.standard_rate_1 <> 0
2682 GROUP BY aggr.equipment_fk_key,
2683 aggr.shift_workday_fk_key,
2684 aggr.hour_fk_key) statrec
2685 WHERE mes.equipment_fk_key = statrec.equipment_fk_key
2686 AND mes.shift_workday_fk_key = statrec.shift_workday_fk_key
2687 AND mes.hour_fk_key = statrec.hour_fk_key )
2688 WHERE EXISTS (SELECT 1
2689 FROM (
2690 SELECT aggr.equipment_fk_key equipment_fk_key,
2691 aggr.shift_workday_fk_key shift_workday_fk_key,
2692 aggr.hour_fk_key hour_fk_key,
2693 v_log_date last_update_date,
2694 null,
2695 v_ua_val last_update_system_id,
2696 null,
2697 v_ua_val last_update_login,
2698 v_ua_val last_updated_by,
2699 Sum((case when srf.standard_rate_1 is not null then
2700 case when nvl(aggr.qty_output,0) = 0 then
2701 nvl(aggr.qty_completed,0) +
2702 case when nvl((aggr.qty_rejected),0) = 0 then
2703 nvl(aggr.qty_rework,0) + nvl(aggr.qty_scrap,0)
2704 else aggr.qty_rejected
2705 end
2706 else aggr.qty_output
2707 end / srf.standard_rate_1
2708 end) ) required_time,
2709 Sum((case when eos.equipment_fk_key is null then 1 else 0 end)) wo_item_count,
2710 null,
2711 null
2712 FROM mth_equip_output_summary eos,
2713 mth_equip_standard_rates_f srf,
2714 (SELECT meo.equipment_fk_key,
2715 meo.item_fk_key,
2716 Nvl(meo.workorder_fk_key,v_ua_val) workorder_fk_key,
2717 Sum(meo.qty_completed) qty_completed,
2718 Sum(meo.qty_scrap) qty_scrap,
2719 Sum(meo.qty_rejected) qty_rejected,
2720 Sum(meo.qty_rework) qty_rework,
2721 Sum(meo.qty_output) qty_output,
2722 meo.shift_workday_fk_key,
2723 Nvl(meo.recipe_version,v_ua_val) recipe_version,
2724 Nvl(meo.recipe_num,v_ua_val) recipe_num,
2725 meo.segment_fk_key segment_fk_key,
2726 meo.hour_fk_key
2727 FROM MTH_EQUIP_OUTPUT meo
2728 WHERE meo.last_update_date > v_run_log_from_date AND meo.last_update_date <= v_run_log_to_date
2729 GROUP BY meo.equipment_fk_key,
2730 meo.item_fk_key,
2731 meo.workorder_fk_key ,
2732 meo.shift_workday_fk_key,
2733 meo.recipe_version ,
2734 meo.recipe_num ,
2738 AND aggr.item_fk_key = eos.item_fk_key(+)
2735 meo.segment_fk_key,
2736 meo.hour_fk_key) aggr
2737 WHERE aggr.equipment_fk_key = eos.equipment_fk_key(+)
2739 AND aggr.workorder_fk_key = eos.workorder_fk_key(+)
2740 AND aggr.shift_workday_fk_key = eos.shift_workday_fk_key(+)
2741 AND aggr.recipe_version = eos.recipe_version(+)
2742 AND aggr.recipe_num = eos.recipe_num(+)
2743 AND aggr.segment_fk_key = eos.segment_fk_key(+)
2744 AND aggr.hour_fk_key = eos.hour_fk_key(+)
2745 AND aggr.equipment_fk_key = srf.equipment_fk_key(+)
2746 AND aggr.item_fk_key = srf.item_fk_key(+)
2747 AND aggr.shift_workday_fk_key = srf.shift_workday_fk_key(+)
2748 AND srf.standard_rate_1 IS NOT NULL
2749 AND srf.standard_rate_1 <> 0
2750 GROUP BY aggr.equipment_fk_key,
2751 aggr.shift_workday_fk_key,
2752 aggr.hour_fk_key) meos
2753 WHERE mes.EQUIPMENT_FK_KEY = meos.EQUIPMENT_FK_KEY
2754 AND mes.SHIFT_WORKDAY_FK_KEY = meos.SHIFT_WORKDAY_FK_KEY
2755 AND mes.HOUR_FK_KEY = meos.HOUR_FK_KEY);
2756
2757 -- Call the logging API to log the number of rows updated
2758 mth_util_pkg.log_msg('Rows updated in MTH_EQUIP_STATUS_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
2759
2760 -- Process data from output to be updated/inserted to output summary
2761 MERGE
2762 INTO
2763 MTH_EQUIP_OUTPUT_SUMMARY
2764 USING
2765 ( SELECT meo.equipment_fk_key sq_equipment_fk_key,
2766 meo.item_fk_key sq_item_fk_key,
2767 Nvl(meo.workorder_fk_key,v_ua_val) sq_workorder_fk_key,
2768 meo.shift_workday_fk_key sq_shift_workday_fk_key,
2769 meo.hour_fk_key sq_hour_fk_key,
2770 Sum(meo.qty_completed) sq_qty_completed,
2771 Sum(meo.qty_scrap) sq_qty_scrap,
2772 Sum(meo.qty_rejected ) sq_qty_rejected,
2773 Sum(meo.qty_rework) sq_qty_rework,
2774 Sum(meo.qty_completed) sq_qty_good,
2775 Sum(meo.qty_output) sq_qty_output,
2776 v_ua_val sq_system_fk_key,
2777 v_log_date sq_log_date,
2778 Nvl(meo.recipe_num,v_ua_val) sq_recipe_num,
2779 Nvl(meo.recipe_version,v_ua_val) sq_recipe_version,
2780 meo.segment_fk_key segment,
2781 Min(srf.standard_rate_1) sq_standard_rate_1,
2782 Min(mic.cost) sq_item_cost,
2783 Min(med.level9_level_key) sq_level9_level_key,
2784 Min(mrc.cost) sq_resource_cost
2785 FROM mth_item_cost_mv mic,
2786 mth_resource_cost_mv mrc,
2787 mth_workday_shifts_d msg,
2788 mth_equipment_denorm_d med,
2789 mth_equip_standard_rates_f srf,
2790 mth_equip_output meo
2791 WHERE med.equipment_hierarchy_key = -2
2792 AND med.equipment_fk_key is not null
2793 AND msg.from_date between med.equipment_effective_date
2794 and nvl(med.equipment_expiration_date , msg.from_date)
2795 AND nvl(med.level9_level_key, v_ua_val) = mrc.resource_fk_key (+)
2796 AND med.equipment_fk_key = meo.equipment_fk_key
2797 AND meo.shift_workday_fk_key = msg.shift_workday_pk_key
2798 AND meo.item_fk_key = mic.item_fk_key (+)
2799 AND meo.equipment_fk_key = srf.equipment_fk_key(+)
2800 AND meo.item_fk_key = srf.item_fk_key(+)
2801 AND meo.shift_workday_fk_key = srf.shift_workday_fk_key(+)
2802 AND meo.hour_fk_key IN ( SELECT hour_fk_key
2803 FROM mth_equip_output
2804 WHERE last_update_date > v_run_log_from_date
2805 AND last_update_date <= v_run_log_to_date )
2806 GROUP BY meo.equipment_fk_key,
2807 meo.item_fk_key,
2808 meo.workorder_fk_key ,
2809 meo.shift_workday_fk_key,
2810 meo.recipe_version ,
2811 meo.recipe_num ,
2812 meo.segment_fk_key,
2813 meo.hour_fk_key ) subquery
2814 ON
2815 (
2816 mth_equip_output_summary.equipment_fk_key = subquery.sq_equipment_fk_key
2817 AND mth_equip_output_summary.item_fk_key = subquery.sq_item_fk_key
2818 AND mth_equip_output_summary.workorder_fk_key = subquery.sq_workorder_fk_key
2819 AND mth_equip_output_summary.shift_workday_fk_key = subquery.sq_shift_workday_fk_key
2820 AND mth_equip_output_summary.hour_fk_key = subquery.sq_hour_fk_key
2821 AND mth_equip_output_summary.recipe_num = subquery.sq_recipe_num
2822 AND mth_equip_output_summary.recipe_version = subquery.sq_recipe_version
2823 AND mth_equip_output_summary.segment_fk_key = subquery.segment
2824 )
2825 WHEN MATCHED THEN
2826 UPDATE
2830 qty_rejected = nvl(subquery.sq_qty_rejected,0),
2827 SET
2828 qty_completed = nvl(subquery.sq_qty_completed,0),
2829 qty_scrap = nvl(subquery.sq_qty_scrap,0),
2831 qty_rework = nvl(subquery.sq_qty_rework,0),
2832 qty_good = nvl(subquery.sq_qty_good,0),
2833 qty_output = nvl(subquery.sq_qty_output,0),
2834 system_fk_key = subquery.sq_system_fk_key,
2835 last_update_date = subquery.sq_log_date,
2836 last_update_system_id = subquery.sq_system_fk_key,
2837 last_update_login = subquery.sq_system_fk_key,
2838 last_updated_by = subquery.sq_system_fk_key,
2839 standard_rate_1 = subquery.sq_standard_rate_1,
2840 item_cost = subquery.sq_item_cost,
2841 resource_fk_key = subquery.sq_level9_level_key,
2842 resource_cost = subquery.sq_resource_cost
2843
2844 WHEN NOT MATCHED THEN
2845 INSERT
2846 (mth_equip_output_summary.equipment_fk_key,
2847 mth_equip_output_summary.item_fk_key,
2848 mth_equip_output_summary.workorder_fk_key,
2849 mth_equip_output_summary.shift_workday_fk_key,
2850 mth_equip_output_summary.hour_fk_key,
2851 mth_equip_output_summary.qty_completed,
2852 mth_equip_output_summary.qty_scrap,
2853 mth_equip_output_summary.qty_rejected,
2854 mth_equip_output_summary.qty_rework,
2855 mth_equip_output_summary.qty_good,
2856 mth_equip_output_summary.qty_output,
2857 mth_equip_output_summary.system_fk_key,
2858 mth_equip_output_summary.creation_date,
2859 mth_equip_output_summary.last_update_date,
2860 mth_equip_output_summary.creation_system_id,
2861 mth_equip_output_summary.last_update_system_id,
2862 mth_equip_output_summary.last_update_login,
2863 mth_equip_output_summary.last_updated_by,
2864 mth_equip_output_summary.recipe_num,
2865 mth_equip_output_summary.recipe_version,
2866 mth_equip_output_summary.segment_fk_key,
2867 mth_equip_output_summary.standard_rate_1,
2868 mth_equip_output_summary.item_cost,
2869 mth_equip_output_summary.resource_fk_key,
2870 mth_equip_output_summary.resource_cost)
2871 VALUES
2872 (subquery.sq_equipment_fk_key,
2873 subquery.sq_item_fk_key,
2874 subquery.sq_workorder_fk_key,
2875 subquery.sq_shift_workday_fk_key,
2876 subquery.sq_hour_fk_key,
2877 subquery.sq_qty_completed,
2878 subquery.sq_qty_scrap,
2879 subquery.sq_qty_rejected,
2880 subquery.sq_qty_rework,
2881 subquery.sq_qty_good,
2882 subquery.sq_qty_output,
2883 subquery.sq_system_fk_key,
2884 subquery.sq_log_date,
2885 subquery.sq_log_date,
2886 subquery.sq_system_fk_key,
2887 subquery.sq_system_fk_key,
2888 subquery.sq_system_fk_key,
2889 subquery.sq_system_fk_key,
2890 subquery.sq_recipe_num,
2891 subquery.sq_recipe_version,
2892 subquery.segment,
2893 subquery.sq_standard_rate_1,
2894 subquery.sq_item_cost,
2895 subquery.sq_level9_level_key,
2896 subquery.sq_resource_cost);
2897
2898 -- Call the logging API to log the number of rows merged
2899 mth_util_pkg.log_msg('Rows merged in MTH_EQUIP_OUTPUT_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
2900
2901
2902 -- Call mth_run_log_pre_load
2903 mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_OUTPUT_SUMMARY',v_ua_val);
2904 mth_util_pkg.log_msg('PROCESS_OUTPUT_SMMRY_INCR end', mth_util_pkg.G_DBG_PROC_FUN_END);
2905
2906 EXCEPTION
2907 WHEN OTHERS THEN
2908 --Call logging API and then throw exception
2909 mth_util_pkg.log_msg('Exception OTHERS in PROCESS_OUTPUT_SMMRY_INCR', mth_util_pkg.G_DBG_EXCEPTION);
2910 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2911 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2912 RAISE;
2913 END PROCESS_OUTPUT_SMMRY_INCR;
2914
2915 /*******************************************************************************
2916 * Procedure :PROCESS_OUTPUT_SMMRY_RECAL *
2917 * Description :This procedure is used for calculating the output *
2918 * summary from output in RECAL mode *
2919 * File Name :MTHEQOPB.PLS *
2920 * Visibility :Public *
2921 * Parameters : p_recal_from_date : Recalculation from date *
2922 * p_recal_to_date : Recalculation to date *
2923 * p_equipment_pk_key : Equipment to recalculate *
2924 * *
2925 * Modification log : *
2926 * Author Date Change *
2927 * Mandar Gijare 02-Sep-2011 Initial Creation *
2928 *******************************************************************************/
2929 PROCEDURE PROCESS_OUTPUT_SMMRY_RECAL(p_recalc_from_date IN TIMESTAMP,
2930 p_recalc_to_date IN TIMESTAMP,
2931 p_recalc_equip_key IN NUMBER,
2932 p_recalc_plant_key IN NUMBER)
2933 IS
2934 v_log_date DATE;
2935 v_ua_val VARCHAR2(30);
2936 v_run_log_to_date TIMESTAMP;
2937 v_run_log_from_date TIMESTAMP;
2938 p_n_recalc_from_date TIMESTAMP;
2939 p_n_recalc_to_date TIMESTAMP;
2940 v_recalc_to_date TIMESTAMP;
2941 NULL_DATES EXCEPTION;
2942 DATE_VAL EXCEPTION;
2943
2947 FROM mth_hour_d
2944 CURSOR c_fetch_from_date
2945 IS
2946 SELECT Min(from_time)
2948 WHERE p_recalc_from_date BETWEEN from_time AND to_time;
2949
2950 CURSOR c_fetch_to_date
2951 IS
2952 SELECT Max(to_time)
2953 FROM mth_hour_d
2954 WHERE v_recalc_to_date BETWEEN from_time AND to_time;
2955
2956 CURSOR c_fetch_recalc_to_date
2957 IS
2958 SELECT Max(reading_time)
2959 FROM MTH_EQUIP_OUTPUT;
2960
2961
2962 BEGIN
2963 mth_util_pkg.log_msg('PROCESS_OUTPUT_SMMRY_RECAL start', mth_util_pkg.G_DBG_PROC_FUN_START);
2964 mth_util_pkg.log_msg('p_recalc_from_date = ' || to_char(p_recalc_from_date,'DD-MON-YYYY HH24:MI:SS') , mth_util_pkg.G_DBG_PARAM_VAL);
2965 mth_util_pkg.log_msg('p_recalc_to_date = ' || to_char(p_recalc_to_date,'DD-MON-YYYY HH24:MI:SS') , mth_util_pkg.G_DBG_PARAM_VAL);
2966 mth_util_pkg.log_msg('p_recalc_equip_key = ' || p_recalc_equip_key, mth_util_pkg.G_DBG_PARAM_VAL);
2967
2968 -- Validate the parameters passed
2969 IF (p_recalc_from_date IS NULL)
2970 THEN
2971 RAISE NULL_DATES;
2972 END IF;
2973
2974 -- Validate the parameters passed
2975 IF (p_recalc_from_date > p_recalc_to_date)
2976 THEN
2977 RAISE DATE_VAL;
2978 END IF;
2979
2980 -- Fetch from date for the p_recalc_from_date
2981 OPEN c_fetch_from_date;
2982
2983 FETCH c_fetch_from_date INTO p_n_recalc_from_date;
2984 mth_util_pkg.log_msg('Hour from which recalculation has to be done p_n_recalc_from_date : '||p_n_recalc_from_date, mth_util_pkg.G_DBG_PARAM_VAL);
2985
2986 CLOSE c_fetch_from_date;
2987
2988 IF p_recalc_to_date IS NULL
2989 THEN
2990 -- Fetch to date for the p_recalc_to_date
2991 OPEN c_fetch_recalc_to_date;
2992
2993 FETCH c_fetch_recalc_to_date INTO v_recalc_to_date;
2994 mth_util_pkg.log_msg('Max of reading time for output when the recalculation to date passed is null : '||v_recalc_to_date, mth_util_pkg.G_DBG_PARAM_VAL);
2995
2996 CLOSE c_fetch_recalc_to_date;
2997 ELSE
2998 v_recalc_to_date := p_recalc_to_date;
2999 END IF;
3000
3001 -- Fetch to date for the p_recalc_to_date
3002 OPEN c_fetch_to_date;
3003
3004 FETCH c_fetch_to_date INTO p_n_recalc_to_date;
3005 mth_util_pkg.log_msg('Hour till which recalculation has to be done p_n_recalc_to_date : '||p_n_recalc_to_date, mth_util_pkg.G_DBG_PARAM_VAL);
3006
3007 CLOSE c_fetch_to_date;
3008
3009 -- Initialize default parameters
3010 v_log_date := sysdate;
3011 v_ua_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
3012
3013 -- Call mth_run_log_pre_load
3014 mth_util_pkg.mth_run_log_pre_load('MTH_EQUIP_OUTPUT_SUMMARY',v_ua_val,'INITIAL',NULL,0,v_log_date);
3015
3016 -- Call delete output summary to delete all data from MTH_EQUIP_OUTPUT_SUMMARY table as well update status summary table
3017 -- for particular hour fk keys that contains the recalculation from and to dates
3018 IF (p_recalc_plant_key IS NULL ) THEN
3019 UPDATE MTH_EQUIP_STATUS_SUMMARY
3020 SET wo_item_count = null,
3021 required_hours = null
3022 WHERE hour_fk_key IN (SELECT hour_pk_key
3023 FROM mth_hour_d
3024 WHERE from_time >= p_n_recalc_from_date
3025 AND to_time <= p_n_recalc_to_date)
3026 AND equipment_fk_key = nvl(p_recalc_equip_key,equipment_fk_key);
3027 ELSE
3028 UPDATE MTH_EQUIP_STATUS_SUMMARY
3029 SET wo_item_count = null,
3030 required_hours = null
3031 WHERE hour_fk_key IN (SELECT hour_pk_key
3032 FROM mth_hour_d
3033 WHERE from_time >= p_n_recalc_from_date
3034 AND to_time <= p_n_recalc_to_date)
3035 AND equipment_fk_key = nvl(p_recalc_equip_key,equipment_fk_key)
3036 AND equipment_fk_key IN (SELECT equipment_pk_key
3037 FROM mth_equipments_d
3038 WHERE plant_fk_key = Nvl(p_recalc_plant_key,plant_fk_key));
3039 END IF;
3040
3041 -- Call the logging API to log the number of rows inserted
3042 mth_util_pkg.log_msg('Rows deleted from MTH_EQUIP_STATUS_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
3043 IF (p_recalc_plant_key IS NULL ) THEN
3044 DELETE
3045 FROM MTH_EQUIP_OUTPUT_SUMMARY
3046 WHERE hour_fk_key IN (SELECT hour_pk_key
3047 FROM mth_hour_d
3048 WHERE from_time >= p_n_recalc_from_date
3049 AND to_time <= p_n_recalc_to_date)
3050 AND equipment_fk_key = nvl(p_recalc_equip_key,equipment_fk_key);
3051 ELSE
3052 DELETE
3053 FROM MTH_EQUIP_OUTPUT_SUMMARY
3054 WHERE hour_fk_key IN (SELECT hour_pk_key
3055 FROM mth_hour_d
3056 WHERE from_time >= p_n_recalc_from_date
3057 AND to_time <= p_n_recalc_to_date)
3058 AND equipment_fk_key = nvl(p_recalc_equip_key,equipment_fk_key)
3059 AND equipment_fk_key IN (SELECT equipment_pk_key
3060 FROM mth_equipments_d
3061 WHERE plant_fk_key = Nvl(p_recalc_plant_key,plant_fk_key));
3062 END IF;
3063
3064 -- Call the logging API to log the number of rows inserted
3065 mth_util_pkg.log_msg('Rows deleted from MTH_EQUIP_OUTPUT_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
3066
3067 -- Process data from output to be inserted to output summary
3068 INSERT
3069 INTO
3070 MTH_EQUIP_OUTPUT_SUMMARY( EQUIPMENT_FK_KEY,
3071 ITEM_FK_KEY,
3075 QTY_COMPLETED,
3072 WORKORDER_FK_KEY,
3073 SHIFT_WORKDAY_FK_KEY,
3074 HOUR_FK_KEY,
3076 QTY_SCRAP,
3077 QTY_REJECTED,
3078 QTY_REWORK,
3079 QTY_GOOD,
3080 QTY_OUTPUT,
3081 SYSTEM_FK_KEY,
3082 CREATION_DATE,
3083 LAST_UPDATE_DATE,
3084 CREATION_SYSTEM_ID,
3085 LAST_UPDATE_SYSTEM_ID,
3086 LAST_UPDATE_LOGIN,
3087 LAST_UPDATED_BY,
3088 RECIPE_NUM,
3089 RECIPE_VERSION,
3090 SEGMENT_FK_KEY,
3091 STANDARD_RATE_1,
3092 ITEM_COST,
3093 RESOURCE_FK_KEY,
3094 RESOURCE_COST )
3095 ( SELECT meo.equipment_fk_key,
3096 meo.item_fk_key,
3097 Nvl(meo.workorder_fk_key,v_ua_val) workorder_fk_key,
3098 meo.shift_workday_fk_key,
3099 meo.hour_fk_key,
3100 Sum(meo.qty_completed) qty_completed,
3101 Sum(meo.qty_scrap) qty_scrap,
3102 Sum(meo.qty_rejected ) qty_rejected,
3103 Sum(meo.qty_rework) qty_rework,
3104 Sum(meo.qty_completed) qty_good,
3105 Sum(meo.qty_output) qty_output,
3106 v_ua_val,
3107 v_log_date,
3108 v_log_date,
3109 v_ua_val,
3110 v_ua_val,
3111 v_ua_val,
3112 v_ua_val,
3113 Nvl(meo.recipe_num,v_ua_val) recipe_num,
3114 Nvl(meo.recipe_version,v_ua_val) recipe_version,
3115 meo.segment_fk_key segment_fk_key,
3116 Min(srf.standard_rate_1) standard_rate_1,
3117 Min(mic.cost) item_cost,
3118 Min(med.level9_level_key) level9_level_key,
3119 Min(mrc.cost) resource_cost
3120 FROM mth_item_cost_mv mic,
3121 mth_resource_cost_mv mrc,
3122 mth_workday_shifts_d msg,
3123 mth_equipment_denorm_d med,
3124 mth_equip_standard_rates_f srf,
3125 mth_equip_output meo
3126 WHERE med.equipment_hierarchy_key = -2
3127 AND med.equipment_fk_key is not null
3128 AND msg.from_date between med.equipment_effective_date
3129 and nvl(med.equipment_expiration_date , msg.from_date)
3130 AND nvl(med.level9_level_key, v_ua_val) = mrc.resource_fk_key (+)
3131 AND med.equipment_fk_key = meo.equipment_fk_key
3132 AND meo.shift_workday_fk_key = msg.shift_workday_pk_key
3133 AND meo.item_fk_key = mic.item_fk_key (+)
3134 AND meo.equipment_fk_key = srf.equipment_fk_key(+)
3135 AND meo.item_fk_key = srf.item_fk_key(+)
3136 AND meo.shift_workday_fk_key = srf.shift_workday_fk_key(+)
3137 AND meo.hour_fk_key IN (SELECT hour_pk_key
3138 FROM mth_hour_d
3139 WHERE from_time >= p_n_recalc_from_date
3140 AND to_time <= p_n_recalc_to_date)
3141 AND meo.equipment_fk_key = nvl(p_recalc_equip_key,meo.equipment_fk_key)
3142 AND meo.equipment_fk_key IN (SELECT equipment_pk_key
3143 FROM mth_equipments_d
3144 WHERE plant_fk_key = Nvl(p_recalc_plant_key,plant_fk_key))
3145 GROUP BY meo.equipment_fk_key,
3146 meo.item_fk_key,
3147 meo.workorder_fk_key ,
3148 meo.shift_workday_fk_key,
3149 meo.recipe_version ,
3150 meo.recipe_num ,
3151 meo.segment_fk_key,
3152 meo.hour_fk_key );
3153
3154 -- Call the logging API to log the number of rows inserted
3155 mth_util_pkg.log_msg('Rows inserted in MTH_EQUIP_OUTPUT_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
3156
3157 UPDATE
3158 MTH_EQUIP_STATUS_SUMMARY mes
3159 SET (mes.WO_ITEM_COUNT,mes.REQUIRED_HOURS,
3160 mes.LAST_UPDATE_DATE,mes.LAST_UPDATE_SYSTEM_ID,
3161 mes.LAST_UPDATE_LOGIN,mes.LAST_UPDATED_BY)=
3162 (SELECT statrec.wo_item_count, statrec.required_time,
3163 statrec.last_update_date,statrec.last_update_system_id,
3167 sum(case when nvl(meos.qty_output,0) = 0 then
3164 statrec.last_update_login,statrec.last_updated_by
3165 FROM (SELECT meos.equipment_fk_key,
3166 meos.shift_workday_fk_key,
3168 nvl(meos.qty_completed,0) +
3169 case when nvl((meos.qty_rejected),0) = 0 then
3170 nvl(meos.qty_rework,0) + nvl(meos.qty_scrap,0)
3171 else meos.qty_rejected
3172 end
3173 else meos.qty_output
3174 end / meos.standard_rate_1) required_time,
3175 count(*) wo_item_count,
3176 meos.hour_fk_key ,
3177 v_log_date last_update_date,
3178 v_ua_val last_update_system_id,
3179 v_ua_val last_update_login,
3180 v_ua_val last_updated_by
3181 FROM MTH_EQUIP_OUTPUT_SUMMARY meos
3182 WHERE meos.standard_rate_1 is NOT NULL
3183 AND meos.standard_rate_1 <> 0
3184 AND meos.hour_fk_key IN (SELECT hour_pk_key
3185 FROM mth_hour_d
3186 WHERE from_time >= p_n_recalc_from_date
3187 AND to_time <= p_n_recalc_to_date)
3188 AND meos.equipment_fk_key = nvl(p_recalc_equip_key,meos.equipment_fk_key)
3189 AND meos.equipment_fk_key IN (SELECT equipment_pk_key
3190 FROM mth_equipments_d
3191 WHERE plant_fk_key = Nvl(p_recalc_plant_key,plant_fk_key))
3192 GROUP BY meos.equipment_fk_key,
3193 meos.shift_workday_fk_key,
3194 meos.hour_fk_key) statrec
3195 WHERE mes.EQUIPMENT_FK_KEY = statrec.EQUIPMENT_FK_KEY AND
3196 mes.SHIFT_WORKDAY_FK_KEY = statrec.SHIFT_WORKDAY_FK_KEY AND
3197 mes.HOUR_FK_KEY = statrec.HOUR_FK_KEY )
3198 WHERE EXISTS (SELECT 1
3199 FROM (SELECT meos.equipment_fk_key,
3200 meos.shift_workday_fk_key,
3201 sum(case when nvl(meos.qty_output,0) = 0 then
3202 nvl(meos.qty_completed,0) +
3203 case when nvl((meos.qty_rejected),0) = 0 then
3204 nvl(meos.qty_rework,0) + nvl(meos.qty_scrap,0)
3205 else meos.qty_rejected
3206 end
3207 else meos.qty_output
3208 end / meos.standard_rate_1) required_time,
3209 count(*) wo_item_count,
3210 meos.hour_fk_key ,
3211 v_log_date last_update_date,
3212 v_ua_val last_update_system_id,
3213 v_ua_val last_update_login,
3214 v_ua_val last_updated_by
3215 FROM MTH_EQUIP_OUTPUT_SUMMARY meos
3216 WHERE meos.standard_rate_1 is NOT NULL
3217 AND meos.standard_rate_1 <> 0
3218 AND meos.hour_fk_key IN (SELECT hour_pk_key
3219 FROM mth_hour_d
3220 WHERE from_time >= p_n_recalc_from_date
3221 AND to_time <= p_n_recalc_to_date)
3222 AND meos.equipment_fk_key = nvl(p_recalc_equip_key,meos.equipment_fk_key)
3223 AND meos.equipment_fk_key IN (SELECT equipment_pk_key
3224 FROM mth_equipments_d
3225 WHERE plant_fk_key = Nvl(p_recalc_plant_key,plant_fk_key))
3226 GROUP BY meos.equipment_fk_key,
3227 meos.shift_workday_fk_key,
3228 meos.hour_fk_key) meos
3229 WHERE mes.EQUIPMENT_FK_KEY = meos.EQUIPMENT_FK_KEY
3230 AND mes.SHIFT_WORKDAY_FK_KEY = meos.SHIFT_WORKDAY_FK_KEY
3231 AND mes.HOUR_FK_KEY = meos.HOUR_FK_KEY);
3232
3233 -- Call the logging API to log the number of rows updated
3234 mth_util_pkg.log_msg('Rows updated in MTH_EQUIP_STATUS_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
3235 -- Call mth_run_log_pre_load
3236 mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_OUTPUT_SUMMARY',v_ua_val);
3237 mth_util_pkg.log_msg('PROCESS_OUTPUT_SMMRY_RECAL end', mth_util_pkg.G_DBG_PROC_FUN_END);
3238
3239 EXCEPTION
3240 WHEN NULL_DATES THEN
3241 --Call logging API and then throw exception
3242 mth_util_pkg.log_msg('Exception NULL_DATES in PROCESS_OUTPUT_SMMRY_RECAL', mth_util_pkg.G_DBG_EXCEPTION);
3243 mth_util_pkg.log_msg('Recalculation from date passed is null. Please correct and re-run the program.', mth_util_pkg.G_DBG_EXCEPTION);
3244 mth_util_pkg.log_msg(-20001, mth_util_pkg.G_DBG_EXCEPTION);
3245 RAISE;
3246 WHEN DATE_VAL THEN
3247 --Call logging API and then throw exception
3248 mth_util_pkg.log_msg('Exception DATE_VAL in PROCESS_OUTPUT_SMMRY_RECAL', mth_util_pkg.G_DBG_EXCEPTION);
3249 mth_util_pkg.log_msg('Recalculation from date is greater than recalculation to date. Please correct and re-run the program.', mth_util_pkg.G_DBG_EXCEPTION);
3250 mth_util_pkg.log_msg(-20002, mth_util_pkg.G_DBG_EXCEPTION);
3251 RAISE;
3252 WHEN OTHERS THEN
3253 --Call logging API and then throw exception
3254 mth_util_pkg.log_msg('Exception OTHERS in PROCESS_OUTPUT_SMMRY_RECAL', mth_util_pkg.G_DBG_EXCEPTION);
3255 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
3256 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
3257 RAISE;
3258 END PROCESS_OUTPUT_SMMRY_RECAL;
3259
3260 END MTH_PROCESS_OUTPUT_PKG;