[Home] [Help]
PACKAGE BODY: APPS.MTH_PROCESS_REASON_PKG
Source
1 PACKAGE BODY MTH_PROCESS_REASON_PKG AS
2 /*$Header: mthtrdgb.pls 120.1.12020000.1 2012/07/24 16:13:33 sasuren noship $*/
3
4 PROCEDURE INIT_REASONS_FROM_READING;
5 PROCEDURE INCR_REASONS_FROM_READING;
6
7 /*******************************************************************************
8 * Procedure :PROCESS_REASONS *
9 * Description :This procedure is the main procedure for reason *
10 * readings. *
11 * File Name :MTHTRDGB.PLS *
12 * Visibility :Public *
13 * Parameters : p_mode : INIT,INCR,RECAL *
14 * p_source : TAG *
15 * p_recal_from_date : Recalculation from date *
16 * p_recal_to_date : Recalculation to date *
17 * p_equipment_pk_key : Equipment to recalculate *
18 * Modification log : *
19 * Author Date Change *
20 * Mandar Gijare 06-Dec-2011 Initial Creation *
21 *******************************************************************************/
22
23 PROCEDURE PROCESS_REASONS(p_mode IN VARCHAR2, --INIT, INCR
24 p_source IN VARCHAR2, --TAG
25 p_recal_from_date IN DATE DEFAULT NULL, --Recalculation from date
26 p_recal_to_date IN DATE DEFAULT NULL, --Recalculation to date
27 p_equipment_pk_key IN NUMBER DEFAULT NULL, --Equipment to recalculate
28 p_plant_pk_key IN NUMBER DEFAULT NULL, --Site for which recalcution to be done
29 p_ret_code OUT NOCOPY NUMBER
30 )
31 IS
32 l_ret_code NUMBER;
33 BEGIN
34 l_ret_code := 0;
35 mth_util_pkg.log_msg('PROCESS_REASONS start', mth_util_pkg.G_DBG_PROC_FUN_START);
36 mth_util_pkg.log_msg('p_mode = ' || p_mode , mth_util_pkg.G_DBG_PARAM_VAL);
37 mth_util_pkg.log_msg('p_source = ' || p_source , mth_util_pkg.G_DBG_PARAM_VAL);
38 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);
39 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);
40 mth_util_pkg.log_msg('p_equipment_pk_key = ' || p_equipment_pk_key, mth_util_pkg.G_DBG_PARAM_VAL);
41 mth_util_pkg.log_msg('p_plant_pk_key = ' || p_plant_pk_key, mth_util_pkg.G_DBG_PARAM_VAL);
42
43 IF p_mode = 'INIT' THEN
44 INIT_REASONS_FROM_READING();
45 ELSIF p_mode = 'INCR' THEN
46 INCR_REASONS_FROM_READING();
47 END IF;
48 p_ret_code := l_ret_code;
49 mth_util_pkg.log_msg('PROCESS_REASONS end', mth_util_pkg.G_DBG_PROC_FUN_END);
50
51 EXCEPTION
52 WHEN OTHERS THEN
53 l_ret_code := 2;
54 p_ret_code := l_ret_code;
55 mth_util_pkg.log_msg('Exception OTHERS in PROCESS_REASONS', mth_util_pkg.G_DBG_EXCEPTION);
56 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
57 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
58 RAISE;
59
60 END PROCESS_REASONS;
61
62 /*******************************************************************************
63 * Procedure :INIT_REASONS_FROM_READING *
64 * Description :This procedure is used for calculating the reason *
65 * in INIT mode *
66 * File Name :MTHTRDGB.PLS *
67 * Visibility :Private *
68 * Parameters : *
69 *******************************************************************************/
70 PROCEDURE INIT_REASONS_FROM_READING IS
71 v_log_date DATE;
72 v_unassigned_val VARCHAR2(30);
73 BEGIN
74 mth_util_pkg.log_msg('INIT_REASONS_FROM_READING start', mth_util_pkg.G_DBG_PROC_FUN_START);
75
76 -- Initialize default parameters
77 v_log_date := sysdate;
78 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
79
80 -- Call mth_run_log_pre_load
81 mth_util_pkg.mth_run_log_pre_load('MTH_TAG_REASON_READINGS',v_unassigned_val,'INITIAL',NULL,0,v_log_date);
82
83 --delete status table
84 DELETE FROM MTH_TAG_REASON_READINGS;
85 mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_REASON_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
86
87 INSERT INTO mth_tag_reason_readings(reason_type,
88 equipment_fk_key,
89 from_date,
90 To_Date,
91 reason_code,
92 creation_date,
93 last_update_date,
94 reading_time,
95 hour_fk_key)
96 (SELECT Decode(mtr.mth_entity,8,1,17,3) reason_type,
97 mtr.equipment_fk_key,
98 mes.from_date,
99 mes.To_Date,
100 mtr.tag_data reason_code,
101 SYSDATE,
102 SYSDATE,
103 mtr.reading_time,
104 mes.hour_fk_key
105 FROM (SELECT mth_entity,
106 equipment_fk_key,
107 tag_data,
108 reading_time
109 FROM mth_tag_readings mtr
110 WHERE processed_flag = 0
111 UNION
112 SELECT mth_entity,
113 equipment_fk_key,
114 tag_data,
115 reading_time
116 FROM mth_tag_readings_err mte
117 WHERE mte.processed_flag = 0) mtr,
118 mth_equip_statuses mes
119 WHERE mtr.reading_time = mes.reading_time
120 AND mtr.equipment_fk_key = mes.equipment_fk_key
121 AND mes.last_update_date <= v_log_date
122 AND mtr.mth_entity IN (8,17)
123 UNION
124 SELECT Decode(mtr.mth_entity,13,2) reason_type,
125 mtr.equipment_fk_key,
126 meo.reading_time from_date,
127 meo.reading_time To_Date,
128 mtr.tag_data reason_code,
129 SYSDATE,
130 SYSDATE,
131 mtr.reading_time,
132 meo.hour_fk_key
133 FROM (SELECT mth_entity,
134 equipment_fk_key,
135 tag_data,
136 reading_time
137 FROM mth_tag_readings mtr
138 WHERE processed_flag = 0
139 UNION
140 SELECT mth_entity,
141 equipment_fk_key,
142 tag_data,
143 reading_time
144 FROM mth_tag_readings_err mte
145 WHERE mte.processed_flag = 0) mtr,
146 mth_equip_output meo
147 WHERE mtr.reading_time = meo.reading_time
148 AND mtr.equipment_fk_key = meo.equipment_fk_key
149 AND meo.last_update_date <= v_log_date
150 AND mtr.mth_entity = 13);
151 mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_REASON_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
152
153 UPDATE mth_tag_readings mtr
154 SET mtr.last_update_date = SYSDATE,
155 mtr.processed_flag = 1
156 WHERE EXISTS
157 ( SELECT 1
158 FROM mth_tag_reason_readings mtrr
159 WHERE mtrr.reading_time = mtr.reading_time
160 AND Decode(mtrr.reason_type,1,8,2,13,3,17) = mtr.mth_entity
161 AND mtrr.equipment_fk_key = mtr.equipment_fk_key
162 AND mtr.processed_flag = 0 ) ;
163
164 UPDATE mth_tag_readings_err mtr
165 SET mtr.processed_flag = 1
166 WHERE EXISTS
167 ( SELECT 1
168 FROM mth_tag_reason_readings mtrr
169 WHERE mtrr.reading_time = mtr.reading_time
170 AND Decode(mtrr.reason_type,1,8,2,13,3,17) = mtr.mth_entity
171 AND mtrr.equipment_fk_key = mtr.equipment_fk_key
172 AND mtr.processed_flag = 0 ) ;
173
174 mth_util_pkg.log_msg('No. of rows updated in MTH_TAG_READINGS : '||SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
175
176 ----Call mth_run_log_post_load
177 mth_util_pkg.mth_run_log_post_load('MTH_TAG_REASON_READINGS',v_unassigned_val);
178
179
180 mth_util_pkg.log_msg('INIT_REASONS_FROM_READING end', mth_util_pkg.G_DBG_PROC_FUN_END);
181 EXCEPTION
182 WHEN OTHERS THEN
183 mth_util_pkg.log_msg('Exception OTHERS in INIT_REASONS_FROM_READING', mth_util_pkg.G_DBG_EXCEPTION);
184 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
185 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
186 RAISE;
187 END INIT_REASONS_FROM_READING;
188
189 /*******************************************************************************
190 * Procedure :INCR_REASONS_FROM_READING *
191 * Description :This procedure is used for calculating the reason *
192 * in INCR mode *
193 * File Name :MTHTRDGB.PLS *
194 * Visibility :Private *
195 * Parameters : *
196 *******************************************************************************/
197 PROCEDURE INCR_REASONS_FROM_READING IS
198 v_run_log_from_date DATE;
199 v_log_date DATE;
200 v_run_log_to_date DATE;
201 v_unassigned_val VARCHAR2(30);
202 BEGIN
203 mth_util_pkg.log_msg('INCR_REASONS_FROM_READING start', mth_util_pkg.G_DBG_PROC_FUN_START);
204
205 -- Initialize default parameters
206 v_log_date := sysdate;
207 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
208
209 -- Call mth_run_log_pre_load
210 mth_util_pkg.mth_run_log_pre_load('MTH_TAG_REASON_READINGS',v_unassigned_val,'INCR',NULL,0,v_log_date);
211
212 -- Get to and from date from run log
213 mth_util_pkg.GET_RUN_LOG_DATES('MTH_TAG_REASON_READINGS',NULL,NULL,NULL,v_run_log_from_date,v_run_log_to_date);
214
215 MERGE INTO MTH_TAG_REASON_READINGS mtrr USING
216 (
217 SELECT Decode(mtr.mth_entity,8,1,17,3) reason_type,
218 mtr.equipment_fk_key,
219 mes.from_date,
220 mes.To_Date,
221 mtr.tag_data reason_code,
222 SYSDATE creation_date,
223 SYSDATE last_update_date,
224 mtr.reading_time,
225 mes.hour_fk_key
226 FROM (SELECT mth_entity,
227 equipment_fk_key,
228 tag_data,
229 reading_time
230 FROM mth_tag_readings
231 WHERE PROCESSED_FLAG = 0
232 AND mth_entity IN (8,17)
233 UNION
234 SELECT Decode(reason_type,1,8),
235 equipment_fk_key,
236 reason_code,
237 reading_time
241 SELECT mth_entity,
238 FROM mth_tag_reason_readings
239 WHERE To_Date IS NULL
240 UNION
242 equipment_fk_key,
243 tag_data,
244 reading_time
245 FROM mth_tag_readings_err
246 WHERE processed_flag = 0
247 AND mth_entity IN (8,17)) mtr,
248 mth_equip_statuses mes
249 WHERE mtr.reading_time = mes.reading_time
250 AND mtr.equipment_fk_key = mes.equipment_fk_key
251 AND mes.last_update_date BETWEEN v_run_log_from_date AND v_run_log_to_date
252 AND mtr.mth_entity IN (8,17)
253 UNION
254 SELECT Decode(mtr.mth_entity,13,2) reason_type,
255 mtr.equipment_fk_key,
256 meo.reading_time from_date,
257 meo.reading_time To_Date,
258 mtr.tag_data reason_code,
259 SYSDATE creation_date,
260 SYSDATE last_update_date,
261 mtr.reading_time,
262 meo.hour_fk_key
263 FROM (SELECT mth_entity,
264 equipment_fk_key,
265 tag_data,
266 reading_time
267 FROM mth_tag_readings mtr
268 WHERE PROCESSED_FLAG = 0
269 UNION
270 SELECT mth_entity,
271 equipment_fk_key,
272 tag_data,
273 reading_time
274 FROM mth_tag_readings_err mte
275 WHERE mte.processed_flag = 0
276 ) mtr,
277 mth_equip_output meo
278 WHERE mtr.reading_time = meo.reading_time
279 AND mtr.equipment_fk_key = meo.equipment_fk_key
280 AND meo.last_update_date BETWEEN v_run_log_from_date AND v_run_log_to_date
281 AND mtr.mth_entity = 13)mtr
282 ON ( mtrr.equipment_fk_key = mtr.equipment_fk_key
283 AND mtrr.from_date = mtr.from_date
284 AND mtrr.reading_time = mtr.reading_time
285 AND mtrr.reason_type = mtr.reason_type )
286 WHEN MATCHED THEN
287 UPDATE SET
288 mtrr.To_Date = mtr.To_Date,
289 mtrr.last_update_date = mtr.last_update_date
290 WHEN NOT MATCHED THEN
291 INSERT
292 (mtrr.reason_type,
293 mtrr.equipment_fk_key,
294 mtrr.from_date,
295 mtrr.To_Date,
296 mtrr.reason_code,
297 mtrr.creation_date,
298 mtrr.last_update_date,
299 mtrr.reading_time,
300 mtrr.hour_fk_key)
301 VALUES
302 (mtr.reason_type,
303 mtr.equipment_fk_key,
304 mtr.from_date,
305 mtr.To_Date,
306 mtr.reason_code,
307 mtr.creation_date,
308 mtr.last_update_date,
309 mtr.reading_time,
310 mtr.hour_fk_key
311 );
312
313 mth_util_pkg.log_msg('No. of rows merged in mth_tag_reason_readings : '||SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
314
315 UPDATE mth_tag_readings mtr
316 SET mtr.last_update_date = SYSDATE,
317 mtr.processed_flag = 1
318 WHERE EXISTS
319 ( SELECT 1
320 FROM mth_tag_reason_readings mtrr
321 WHERE mtrr.reading_time = mtr.reading_time
322 AND Decode(mtrr.reason_type,1,8,2,13,3,17) = mtr.mth_entity
323 AND mtrr.equipment_fk_key = mtr.equipment_fk_key
324 AND mtr.processed_flag = 0 ) ;
325
326 UPDATE mth_tag_readings_err mtr
327 SET mtr.processed_flag = 1
328 WHERE EXISTS
329 ( SELECT 1
330 FROM mth_tag_reason_readings mtrr
331 WHERE mtrr.reading_time = mtr.reading_time
332 AND Decode(mtrr.reason_type,1,8,2,13,3,17) = mtr.mth_entity
333 AND mtrr.equipment_fk_key = mtr.equipment_fk_key
334 AND mtr.processed_flag = 0 ) ;
335
336 mth_util_pkg.log_msg('No. of rows updated in mth_tag_readings : '||SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
337
338 -- Call mth_run_log_post_load
339 mth_util_pkg.mth_run_log_post_load('MTH_TAG_REASON_READINGS',v_unassigned_val);
340
341 mth_util_pkg.log_msg('INCR_REASONS_FROM_READING end', mth_util_pkg.G_DBG_PROC_FUN_END);
342
343 EXCEPTION
344 WHEN OTHERS THEN
345 --Call logging API and then throw exception
346 mth_util_pkg.log_msg('Exception OTHERS in INCR_REASONS_FROM_READING', mth_util_pkg.G_DBG_EXCEPTION);
347 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
348 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
349 RAISE;
350
351 END INCR_REASONS_FROM_READING;
352
353 END MTH_PROCESS_REASON_PKG;