[Home] [Help]
PACKAGE BODY: APPS.EAM_FAILURE_ANALYSIS_PVT
Source
1 PACKAGE BODY EAM_FAILURE_ANALYSIS_PVT AS
2 /* $Header: EAMVFALB.pls 120.6 2006/10/05 07:34:28 amourya noship $ */
3
4 g_module_name VARCHAR2(30);
5
6 Procedure GET_HISTORY_RECORDS_ADV
7 ( P_WHERE_CLAUSE IN VARCHAR2,
8 P_FROM_DATE_CLAUSE IN VARCHAR2,
9 P_SELECTED_METER IN NUMBER,
10 P_CURRENT_ORG_ID IN NUMBER,
11 X_GROUP_ID OUT NOCOPY NUMBER,
12 x_return_status OUT NOCOPY VARCHAR2,
13 x_msg_count OUT NOCOPY NUMBER,
14 x_msg_data OUT NOCOPY VARCHAR2,
15 x_unmatched_uom_class OUT NOCOPY VARCHAR2,
16 x_unmatched_currency OUT NOCOPY VARCHAR2) IS
17
18 sql_stmt VARCHAR2(4000);
19 l_lookup_meaning VARCHAR2(30);
20 c_ref_failures SYS_REFCURSOR;
21 L_asset_failure_tbl EAM_FAILURE_ANALYSIS_PVT.eam_asset_failure_tbl_type;
22 l_group_id NUMBER;
23 l_ref_failures SYS_REFCURSOR;
24 l_unmatched_uom_class VARCHAR2(1) := 'N';
25 l_unmatched_currency VARCHAR2(1) := 'N';
26
27 BEGIN
28 g_module_name := 'GET_HISTORY_RECORDS_ADV';
29
30 /*This is to rollback all transactions done w.r.t. EAM_FAILURE_HISTORY_TEMP in the current session */
31 ROLLBACK;
32
33 GET_FAILURE_METER_RECS_CURSOR(p_where_clause => p_where_clause,
34 p_selected_meter => p_selected_meter,
35 p_view_by => 1,
36 p_from_date_clause => p_from_date_clause,
37 x_ref_failures => l_ref_failures);
38
39 LOOP
40 FETCH l_REF_FAILURES BULK COLLECT INTO l_asset_failure_tbl LIMIT 500;
41
42 IF ( l_asset_failure_tbl.Count > 0 ) THEN
43
44 --Currency Validations and UOM Validations are not needed in Failure History Page.
45 /* VALIDATE_RECORDS(p_asset_failure_tbl => l_asset_failure_tbl,
46 p_validate_meters => 'N',
47 p_validate_currency => 'Y',
48 p_current_org_id => p_current_org_id,
49 x_unmatched_uom_class => l_unmatched_uom_class,
50 x_unmatched_currency => l_unmatched_currency); */
51
52 SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_GROUP_ID FROM DUAL;
53
54 INSERT_INTO_TEMP_TABLE(p_group_id => l_group_id,
55 p_asset_failure_tbl => l_asset_failure_tbl);
56
57 --No need to calculate Repair Costs in Failure History Page.
58 --COMPUTE_REPAIR_COSTS(l_group_id);
59
60 END IF;
61 EXIT WHEN l_REF_FAILURES%NOTFOUND;
62 END LOOP;
63
64 CLOSE l_REF_FAILURES;
65 x_group_id := l_group_id;
66 x_return_status := 'S';
67 x_unmatched_uom_class := l_unmatched_uom_class;
68 x_unmatched_currency := l_unmatched_currency;
69 EXCEPTION
70 WHEN OTHERS THEN
71 x_return_status := 'E';
72 x_msg_data := 'Error in '||g_module_name||':'||SQLERRM;
73 END GET_HISTORY_RECORDS_ADV;
74
75
76 Procedure GET_HISTORY_RECORDS_FA_ADV
77 ( P_WHERE_CLAUSE IN VARCHAR2,
78 P_WHERE_CLAUSE_1 IN VARCHAR2,
79 P_FROM_DATE_CLAUSE IN VARCHAR2,
80 P_SELECTED_METER IN NUMBER,
81 P_INCLUDE_CHILDREN IN VARCHAR2,
82 P_VIEW_BY IN VARCHAR2,
83 P_COMPUTE_REPAIR_COSTS IN VARCHAR2,
84 P_CURRENT_ORG_ID IN VARCHAR2,
85 X_GROUP_ID OUT NOCOPY NUMBER,
86 x_return_status OUT NOCOPY VARCHAR2,
87 x_msg_count OUT NOCOPY NUMBER,
88 x_msg_data OUT NOCOPY VARCHAR2,
89 x_unmatched_uom_class OUT NOCOPY VARCHAR2,
90 x_unmatched_currency OUT NOCOPY VARCHAR2) IS
91
92 c_ref_failures SYS_REFCURSOR;
93 l_asset_failure_tbl eam_asset_failure_tbl_type;
94 l_group_id NUMBER;
95 l_current_org_id NUMBER;
96 l_org2 NUMBER;
97 l_same_currency NUMBER;
98 l_meter_uom1 VARCHAR2(3);
99 l_meter_uom2 VARCHAR2(3);
100 l_uom1_conv_rate NUMBER;
101 l_uom2_conv_rate NUMBER;
102 l_validate_meters VARCHAR2(1);
103 l_validate_currency VARCHAR2(1);
104 x_ref_failures SYS_REFCURSOR;
105 l_unmatched_uom_class VARCHAR2(1) := 'N';
106 l_unmatched_currency VARCHAR2(1) := 'N';
107 l_return_status VARCHAR2(1);
108 l_msg_data VARCHAR2(4000);
109 l_where_clause_parent VARCHAR2(8000);
110
111 BEGIN
112
113 g_module_name := 'GET_HISTORY_RECORDS_FA_ADV';
114 l_return_status := 'S';
115 /*This is to rollback all transactions (uncommited) done w.r.t. EAM_FAILURE_HISTORY_TEMP in the current session */
116 ROLLBACK;
117
118 l_current_org_id := p_current_org_id;
119
120 IF (p_where_clause <> 'NULL' AND p_where_clause IS NOT NULL) then
121 l_where_clause_parent := p_where_clause;
122 ELSIF (p_where_clause_1 <> 'NULL' AND p_where_clause_1 IS NOT NULL) then
123 l_where_clause_parent := p_where_clause_1;
124 ELSE
125 l_where_clause_parent := NULL;
126 END IF;
127
128 IF ( p_include_children = 'N' OR p_include_children IS NULL ) THEN
129
130 If p_View_By = 2 /* 'ASSET_GROUP' */ OR p_View_By = 1 /* 'ASSET_NUMBER' */ THEN
131 GET_FAILURE_METER_RECS_CURSOR(l_where_clause_parent, p_selected_meter, p_from_date_clause, p_View_By, x_ref_failures);
132 Else
133 GET_FAILURE_RECS_CURSOR(l_where_clause_parent, p_from_date_clause, p_View_By, x_ref_failures);
134 End if;
135
136 IF p_view_by IN (1,3,4) THEN
137 l_validate_meters := 'N';
138 l_validate_currency := 'Y';
139 ELSIF p_view_by = 2 THEN
140 l_validate_meters := 'Y';
141 l_validate_currency := 'Y';
142 END IF;
143
144 l_validate_currency := p_compute_repair_costs;
145
146 LOOP
147 FETCH x_ref_failures BULK COLLECT INTO l_asset_failure_tbl LIMIT 500;
148 IF ( l_asset_failure_tbl.Count > 0 ) THEN
149
150 VALIDATE_RECORDS( p_asset_failure_tbl => l_asset_failure_tbl,
151 p_validate_meters => l_validate_meters,
152 p_validate_currency => l_validate_currency,
153 p_current_org_id => l_current_org_id,
154 x_unmatched_uom_class => l_unmatched_uom_class,
155 x_unmatched_currency => l_unmatched_currency);
156
157 SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_group_id FROM DUAL;
158
159 INSERT_INTO_TEMP_TABLE(p_group_id => l_group_id,
160 p_asset_failure_tbl => l_asset_failure_tbl);
161
162 IF p_compute_repair_costs = 'Y' THEN
163 COMPUTE_REPAIR_COSTS(l_group_id);
164 END if;
165 END IF;
166
167 EXIT WHEN x_ref_failures%NOTFOUND;
168 END LOOP;
169
170 CLOSE x_ref_failures;
171 x_group_id := l_group_id;
172
173 ELSIF p_include_children = 'Y' THEN
174
175 GET_CHILD_RECORDS_FA_ADV
176 ( P_WHERE_CLAUSE =>p_where_clause,
177 P_WHERE_CLAUSE_1 =>p_where_clause_1,
178 P_FROM_DATE_CLAUSE =>p_from_date_clause,
179 P_VIEW_BY =>p_view_by,
180 P_COMPUTE_REPAIR_COSTS =>p_compute_repair_costs,
181 P_CURRENT_ORG_ID =>l_current_org_id,
182 x_group_id =>l_group_id,
183 x_return_status =>l_return_status,
184 x_msg_data =>l_msg_data,
185 x_unmatched_uom_class =>l_unmatched_uom_class,
186 x_unmatched_currency =>l_unmatched_currency );
187
188 END IF;
189 x_return_status := l_return_status;
190 x_group_id := l_group_id;
191 x_unmatched_uom_class := l_unmatched_uom_class;
192 x_unmatched_currency := l_unmatched_currency;
193
194 EXCEPTION
195 WHEN OTHERS THEN
196 x_return_status := 'E';
197 x_msg_data := 'Error in '||g_module_name||':'||SQLERRM;
198 END GET_HISTORY_RECORDS_FA_ADV;
199
200
201 PROCEDURE GET_FAILURE_METER_RECS_CURSOR
202 ( P_WHERE_CLAUSE IN VARCHAR2,
203 P_SELECTED_METER IN NUMBER,
204 P_FROM_DATE_CLAUSE IN VARCHAR2,
205 P_VIEW_BY IN NUMBER,
206 X_REF_FAILURES OUT NOCOPY SYS_REFCURSOR) IS
207
208 sql_stmt VARCHAR2(8000);
209
210 l_selected_meter NUMBER;
211 l_from_date_clause VARCHAR2(8000);
212 l_where_clause VARCHAR2(8000);
213 l_partition_by VARCHAR2(50);
214 l_first_tbf_calc_clause VARCHAR2(500);
215 BEGIN
216
217 g_module_name := 'GET_FAILURE_METER_RECS_CURSOR';
218
219 IF P_SELECTED_METER IS NULL THEN
220 l_selected_meter := 0;
221 ELSE
222 l_selected_meter := P_SELECTED_METER;
223 END IF;
224
225 IF (p_where_clause <> 'NULL' AND p_where_clause IS NOT NULL) THEN
226 l_where_clause := p_where_clause;
227 ELSE
228 l_where_clause := NULL;
229 END IF;
230
231 IF (p_from_date_clause <> 'NULL' AND p_from_date_clause IS NOT NULL) THEN
232 l_from_date_clause := p_from_date_clause;
233 ELSE
234 l_from_date_clause := NULL ;
235 END IF;
236
237 IF (p_view_by = 1) THEN
238 l_partition_by := 'MAINTENANCE_OBJECT_ID';
239 l_first_tbf_calc_clause := 'ASSET_CREATION_DATE';
240 ELSIF (p_view_by = 2) THEN
241 l_partition_by := 'MAINTAINED_GROUP_ID';
242 l_first_tbf_calc_clause := '(SELECT MIN(CII1.CREATION_DATE)
243 FROM CSI_ITEM_INSTANCES CII1
244 WHERE CII1.INVENTORY_ITEM_ID = MAINTAINED_GROUP_ID
245 AND CII1.LAST_VLD_ORGANIZATION_ID = ASSET_ORGANIZATION_ID)';
246 END IF;
247
248 sql_stmt := ' SELECT ASSET_TYPE,
249 MAINTENANCE_OBJECT_ID,
250 MAINTAINED_NUMBER,
251 DESCRIPTIVE_TEXT,
252 MAINTAINED_GROUP,
253 MAINTAINED_GROUP_ID,
254 WIP_ENTITY_ID,
255 WIP_ENTITY_NAME,
256 ORGANIZATION_ID,
257 ORGANIZATION_CODE,
258 ASSET_CATEGORY_ID,
259 ASSET_CATEGORY,
260 ASSET_LOCATION_ID,
261 ASSET_LOCATION,
262 OWNING_DEPARTMENT_ID,
263 OWNING_DEPARTMENT,
264 FAILURE_CODE,
265 FAILURE_DESC,
266 CAUSE_CODE,
267 CAUSE_DESC,
268 RESOLUTION_CODE,
269 RESOLUTION_DESC,
270 FAILURE_DATE,
271 COMMENTS,
272 DECODE( LAG(FAILURE_DATE,1,NULL) OVER ( PARTITION BY '||l_partition_by||' ORDER BY FAILURE_DATE),
273 NULL, (FAILURE_DATE - '||l_first_tbf_calc_clause||' ),
274 (FAILURE_DATE - ( LAG(FAILURE_DATE,1,NULL) OVER ( PARTITION BY '||l_partition_by||' ORDER BY FAILURE_DATE)) )) DAYS_BETWEEN_FAILURES,
275 (DATE_COMPLETED - FAILURE_DATE )*24 TIME_TO_REPAIR,
276 METER_ID,
277 METER_NAME,
278 METER_UOM,
279 DECODE(METER_TYPE,2,CURRENT_READING,1,
280 DECODE( LAG(CURRENT_READING,1,NULL) OVER ( PARTITION BY '||l_partition_by||' ORDER BY DATE_COMPLETED),
281 NULL, CURRENT_READING,
282 (CURRENT_READING - ( LAG(CURRENT_READING,1,NULL) OVER ( PARTITION BY '||l_partition_by||' ORDER BY DATE_COMPLETED))) )) READING_BETWEEN_FAILURES,
283 '||'''Y'''||' INCLUDE_FOR_READING_AGGR,
284 '||'''Y'''||' INCLUDE_FOR_COST_AGGR
285 FROM (SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
286 EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
287 CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
288 CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
289 NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID)MAINTAINED_GROUP_ID,
290 MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
291 WDJ.WIP_ENTITY_ID,
292 WE.WIP_ENTITY_NAME,
293 WDJ.ORGANIZATION_ID,
294 OOD.ORGANIZATION_CODE,
295 CII.CATEGORY_ID ASSET_CATEGORY_ID,
296 MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
297 EAF.AREA_ID ASSET_LOCATION_ID,
298 MEL.LOCATION_CODES ASSET_LOCATION,
299 WDJ.OWNING_DEPARTMENT OWNING_DEPARTMENT_ID,
300 BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
301 WDJ.DATE_COMPLETED,
302 EAFC.FAILURE_CODE,
303 EFC.DESCRIPTION FAILURE_DESC,
304 EAFC.CAUSE_CODE,
305 ECC.DESCRIPTION CAUSE_DESC,
306 EAFC.RESOLUTION_CODE,
307 ERC.DESCRIPTION RESOLUTION_DESC,
308 EAF.FAILURE_DATE,
309 EAFC.COMMENTS,
310 METER.METER_ID,
311 METER.METER_NAME METER_NAME,
312 METER.METER_UOM METER_UOM,
313 METER.CURRENT_READING CURRENT_READING,
314 METER.CURRENT_READING_DATE CURRENT_READING_DATE,
315 CII.LAST_VLD_ORGANIZATION_ID ASSET_ORGANIZATION_ID,
316 METER.METER_TYPE,
317 CII.CREATION_DATE ASSET_CREATION_DATE
318 FROM WIP_DISCRETE_JOBS WDJ,
319 WIP_ENTITIES WE,
320 CSI_ITEM_INSTANCES CII,
321 MTL_CATEGORIES_KFV MCKFV,
322 MTL_SYSTEM_ITEMS_KFV MSIKFV,
323 MTL_EAM_LOCATIONS MEL,
324 BOM_DEPARTMENTS BD,
325 EAM_ASSET_FAILURE_CODES EAFC,
326 EAM_ASSET_FAILURES EAF,
327 ORG_ORGANIZATION_DEFINITIONS OOD,
328 EAM_FAILURE_CODES EFC,
329 EAM_CAUSE_CODES ECC,
330 EAM_RESOLUTION_CODES ERC,
331 (SELECT
332 ccb.counter_id METER_ID,
333 cctl.name METER_NAME,
334 ccb.uom_code METER_UOM,
335 CCA.SOURCE_OBJECT_ID MAINTENANCE_OBJECT_ID,
336 CCR.COUNTER_READING CURRENT_READING,
337 CCR.VALUE_TIMESTAMP CURRENT_READING_DATE,
338 CCA.PRIMARY_FAILURE_FLAG,
339 decode(ct.transaction_type_id,92,ct.source_header_ref_id,to_number(null)) WIP_ENTITY_ID,
340 CCB.reading_type METER_TYPE
341 FROM csi_counters_b CCB,csi_counters_tl cctl, csi_counter_readings CCR, csi_counter_associations CCA, csi_transactions CT WHERE ccb.counter_id = cctl.counter_id
342 and SYSDATE BETWEEN nvl(ccb.start_date_active, SYSDATE-1) AND nvl(ccb.end_date_active, SYSDATE+1)
343 and cctl.language = userenv('|| '''LANG'''|| ') and ccb.counter_type = '||'''REGULAR'''||'
344 AND CCB.COUNTER_ID = CCA.COUNTER_ID
345 AND CCR.COUNTER_ID(+) = CCB.COUNTER_ID
346 AND CCR.transaction_id = CT.transaction_id(+)
347 and SYSDATE BETWEEN nvl(cca.start_date_active, SYSDATE-1) AND nvl(cca.end_date_active, SYSDATE+1)
348 AND CCA.PRIMARY_FAILURE_FLAG = '||'''Y'''||'
349 AND CCB.EAM_REQUIRED_FLAG = '||'''Y'''||'
350 AND CCR.COUNTER_VALUE_ID IN
351 (
352 SELECT
353 METER_READING_ID
354 FROM
355 (
356 SELECT
357 Max(EMR1.METER_READING_ID) METER_READING_ID
358 FROM EAM_METER_READINGS_V EMR1
359 GROUP BY EMR1.WIP_ENTITY_ID,
360 EMR1.METER_ID
361 )
362 )) METER
363 WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
364 AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
365 AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
366 AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
367 AND WDJ.STATUS_TYPE IN (4,5,12)
368 AND EAF.SOURCE_TYPE = 1
369 AND EAF.OBJECT_TYPE = 3
370 AND EAF.OBJECT_ID = CII.INSTANCE_ID
371 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
372 AND EAFC.FAILURE_CODE = EFC.FAILURE_CODE
373 AND EAFC.CAUSE_CODE = ECC.CAUSE_CODE
374 AND EAFC.RESOLUTION_CODE = ERC.RESOLUTION_CODE
375 AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
376 AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
377 AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
378 AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
379 AND MEL.LOCATION_ID (+) = EAF.AREA_ID
380 AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
381 AND EAF.OBJECT_ID = METER.MAINTENANCE_OBJECT_ID (+)
382 AND EAF.SOURCE_ID = METER.WIP_ENTITY_ID (+) )';
383
384 OPEN X_REF_FAILURES FOR 'SELECT ASSET_TYPE,
385 MAINTENANCE_OBJECT_ID,
386 MAINTAINED_NUMBER,
387 DESCRIPTIVE_TEXT,
388 MAINTAINED_GROUP,
389 MAINTAINED_GROUP_ID,
390 WIP_ENTITY_ID,
391 WIP_ENTITY_NAME,
392 ORGANIZATION_ID,
393 ORGANIZATION_CODE,
394 ASSET_CATEGORY,
395 ASSET_CATEGORY_ID,
396 ASSET_LOCATION,
397 OWNING_DEPARTMENT,
398 FAILURE_CODE,
399 CAUSE_CODE,
400 RESOLUTION_CODE,
401 FAILURE_DATE,
402 COMMENTS,
403 DAYS_BETWEEN_FAILURES,
404 TIME_TO_REPAIR,
405 METER_ID,
406 METER_NAME,
407 METER_UOM,
408 READING_BETWEEN_FAILURES,
409 INCLUDE_FOR_READING_AGGR,
410 INCLUDE_FOR_COST_AGGR FROM ('||sql_stmt||l_where_clause||')'||l_from_date_clause||' ORDER BY MAINTAINED_GROUP_ID';
411
412 END GET_FAILURE_METER_RECS_CURSOR;
413
414
415 Procedure GET_FAILURE_RECS_CURSOR
416 ( P_WHERE_CLAUSE IN VARCHAR2,
417 P_FROM_DATE_CLAUSE IN VARCHAR2,
418 p_VIEW_BY IN NUMBER,
419 X_REF_FAILURES OUT NOCOPY SYS_REFCURSOR) IS
420
421 sql_stmt VARCHAR2(4000);
422 l_from_date_clause VARCHAR2(8000);
423 l_where_clause VARCHAR2(8000);
424 l_partition_by VARCHAR2(50);
425 BEGIN
426
427 g_module_name := 'GET_FAILURE_RECS_CURSOR';
428
429 IF (p_where_clause <> 'NULL' AND p_where_clause IS NOT NULL) THEN
430 l_where_clause := p_where_clause;
431 ELSE
432 l_where_clause := NULL;
433 END IF;
434
435 IF (p_from_date_clause <> 'NULL' AND p_from_date_clause IS NOT NULL) THEN
436 l_from_date_clause := p_from_date_clause;
437 ELSE
438 l_from_date_clause := NULL;
439 END IF;
440
441 IF (p_view_by = 3) THEN
442 l_partition_by := 'ASSET_CATEGORY_ID';
443 ELSIF (p_view_by = 4) THEN
444 l_partition_by := 'FAILURE_CODE';
445 END IF;
446
447 sql_stmt := 'SELECT ASSET_TYPE,
448 MAINTENANCE_OBJECT_ID,
449 MAINTAINED_NUMBER,
450 DESCRIPTIVE_TEXT,
451 MAINTAINED_GROUP,
452 MAINTAINED_GROUP_ID,
453 WIP_ENTITY_ID,
454 WIP_ENTITY_NAME,
455 ORGANIZATION_ID,
456 ORGANIZATION_CODE,
457 ASSET_CATEGORY_ID,
458 ASSET_CATEGORY,
459 ASSET_LOCATION_ID,
460 ASSET_LOCATION,
461 OWNING_DEPARTMENT_ID,
462 OWNING_DEPARTMENT,
463 FAILURE_CODE,
464 FAILURE_DESC,
465 CAUSE_CODE,
466 CAUSE_DESC,
467 RESOLUTION_CODE,
468 RESOLUTION_DESC,
469 FAILURE_DATE,
470 COMMENTS,
471 DECODE( LAG(FAILURE_DATE,1,NULL) OVER (PARTITION BY '||l_partition_by||' ORDER BY FAILURE_DATE),
472 NULL, (FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
473 FROM CSI_ITEM_INSTANCES CII1
474 WHERE CII1.INVENTORY_ITEM_ID = MAINTAINED_GROUP_ID
475 AND CII1.CURRENT_ORGANIZATION_ID = ASSET_ORGANIZATION_ID)),
476 (FAILURE_DATE - ( LAG(FAILURE_DATE,1,NULL) OVER (PARTITION BY '||l_partition_by||' ORDER BY FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
477 (DATE_COMPLETED - FAILURE_DATE ) * 24 TIME_TO_REPAIR,
478 NULL METER_ID,
479 NULL METER_NAME,
480 NULL METER_UOM,
481 to_number(NULL) READING_BETWEEN_FAILURES,
482 '||'''Y'''||' INCLUDE_FOR_READING_AGGR,
483 '||'''Y'''||' INCLUDE_FOR_COST_AGGR
484 FROM (SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
485 EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
486 CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
487 CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
488 NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
489 MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
490 WDJ.WIP_ENTITY_ID,
491 WE.WIP_ENTITY_NAME,
492 WDJ.ORGANIZATION_ID,
493 OOD.ORGANIZATION_CODE,
494 CII.CATEGORY_ID ASSET_CATEGORY_ID,
495 MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
496 EAF.AREA_ID ASSET_LOCATION_ID,
497 MEL.LOCATION_CODES ASSET_LOCATION,
498 EAF.DEPARTMENT_ID OWNING_DEPARTMENT_ID,
499 BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
500 WDJ.DATE_COMPLETED,
501 EAFC.FAILURE_CODE,
502 EFC.DESCRIPTION FAILURE_DESC,
503 EAFC.CAUSE_CODE,
504 ECC.DESCRIPTION CAUSE_DESC,
505 EAFC.RESOLUTION_CODE,
506 ERC.DESCRIPTION RESOLUTION_DESC,
507 EAF.FAILURE_DATE,
508 EAFC.COMMENTS,
509 CII.LAST_VLD_ORGANIZATION_ID ASSET_ORGANIZATION_ID
510 FROM WIP_DISCRETE_JOBS WDJ,
511 WIP_ENTITIES WE,
512 CSI_ITEM_INSTANCES CII,
513 MTL_CATEGORIES_KFV MCKFV,
514 MTL_SYSTEM_ITEMS_KFV MSIKFV,
515 MTL_EAM_LOCATIONS MEL,
516 BOM_DEPARTMENTS BD,
517 EAM_ASSET_FAILURE_CODES EAFC,
518 EAM_ASSET_FAILURES EAF,
519 ORG_ORGANIZATION_DEFINITIONS OOD,
520 EAM_FAILURE_CODES EFC,
521 EAM_CAUSE_CODES ECC,
522 EAM_RESOLUTION_CODES ERC
523 WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
524 AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
525 AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
526 AND WDJ.STATUS_TYPE IN (4,5,12)
527 AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
528 AND EAF.SOURCE_TYPE = 1
529 AND EAF.OBJECT_TYPE = 3
530 AND EAF.OBJECT_ID = CII.INSTANCE_ID
531 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
532 AND EAFC.FAILURE_CODE = EFC.FAILURE_CODE
533 AND EAFC.CAUSE_CODE = ECC.CAUSE_CODE
534 AND EAFC.RESOLUTION_CODE = ERC.RESOLUTION_CODE
535 AND (EFC.EFFECTIVE_END_DATE IS NULL OR EFC.EFFECTIVE_END_DATE >= SYSDATE)
536 AND (ECC.EFFECTIVE_END_DATE IS NULL OR ECC.EFFECTIVE_END_DATE >= SYSDATE)
537 AND (ERC.EFFECTIVE_END_DATE IS NULL OR ERC.EFFECTIVE_END_DATE >= SYSDATE)
538 AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
539 AND EAF.MAINT_ORGANIZATION_ID = WDJ.ORGANIZATION_ID
540 AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
541 AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
542 AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
543 AND MEL.LOCATION_ID (+) = EAF.AREA_ID
544 AND BD.DEPARTMENT_ID (+)= EAF.DEPARTMENT_ID )';
545
546
547 OPEN X_REF_FAILURES FOR 'SELECT ASSET_TYPE,
548 MAINTENANCE_OBJECT_ID,
549 MAINTAINED_NUMBER,
550 DESCRIPTIVE_TEXT,
551 MAINTAINED_GROUP,
552 MAINTAINED_GROUP_ID,
553 WIP_ENTITY_ID,
554 WIP_ENTITY_NAME,
555 ORGANIZATION_ID,
556 ORGANIZATION_CODE,
557 ASSET_CATEGORY,
558 ASSET_CATEGORY_ID,
559 ASSET_LOCATION,
560 OWNING_DEPARTMENT,
561 FAILURE_CODE,
562 CAUSE_CODE,
563 RESOLUTION_CODE,
564 FAILURE_DATE,
565 COMMENTS,
566 DAYS_BETWEEN_FAILURES,
567 TIME_TO_REPAIR,
568 METER_ID,
569 METER_NAME,
570 METER_UOM,
571 READING_BETWEEN_FAILURES,
572 INCLUDE_FOR_READING_AGGR,
573 INCLUDE_FOR_COST_AGGR FROM ('||SQL_STMT||l_where_clause||' ) '||l_from_date_clause;
574
575 END GET_FAILURE_RECS_CURSOR;
576
577
578 Procedure GET_HISTORY_RECORDS_SIMPLE
579 ( P_GEN_OBJECT_ID IN NUMBER,
580 P_FROM_DATE IN DATE,
581 P_TO_DATE IN DATE,
582 P_SELECTED_METER IN NUMBER,
583 P_CURRENT_ORG_ID IN NUMBER,
584 X_GROUP_ID OUT NOCOPY NUMBER,
585 x_return_status OUT NOCOPY VARCHAR2,
586 x_msg_count OUT NOCOPY NUMBER,
587 x_msg_data OUT NOCOPY VARCHAR2,
588 x_unmatched_uom_class OUT NOCOPY VARCHAR2,
589 x_unmatched_currency OUT NOCOPY VARCHAR2) IS
590
591 l_asset_failure_tbl EAM_FAILURE_ANALYSIS_PVT.eam_asset_failure_tbl_type;
592 l_group_id NUMBER := 0;
593 l_gen_object_id NUMBER;
594 l_selected_meter NUMBER;
595 l_validate_meters VARCHAR2(1);
596 l_validate_currency VARCHAR2(1);
597 l_current_org_id NUMBER;
598 l_unmatched_uom_class VARCHAR2(1) := 'N';
599 l_unmatched_currency VARCHAR2(1) := 'N';
600 l_from_date DATE;
601 l_to_date DATE;
602
603 CURSOR c_history_records_simple IS
604 SELECT ASSET_TYPE,
605 MAINTENANCE_OBJECT_ID,
606 MAINTAINED_NUMBER,
607 DESCRIPTIVE_TEXT,
608 MAINTAINED_GROUP,
609 MAINTAINED_GROUP_ID,
610 WIP_ENTITY_ID,
611 WIP_ENTITY_NAME,
612 ORGANIZATION_ID,
613 ORGANIZATION_CODE,
614 ASSET_CATEGORY,
615 ASSET_CATEGORY_ID,
616 ASSET_LOCATION,
617 OWNING_DEPARTMENT,
618 FAILURE_CODE,
619 CAUSE_CODE,
620 RESOLUTION_CODE,
621 FAILURE_DATE,
622 COMMENTS,
623 DAYS_BETWEEN_FAILURES,
624 TIME_TO_REPAIR,
625 METER_ID,
626 METER_NAME,
627 METER_UOM,
628 READING_BETWEEN_FAILURES,
629 INCLUDE_FOR_READING_AGGR,
630 INCLUDE_FOR_COST_AGGR
631 from
632 (SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
633 EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
634 CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
635 CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
636 MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
637 NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
638 WDJ.WIP_ENTITY_ID,
639 WE.WIP_ENTITY_NAME,
640 WDJ.ORGANIZATION_ID,
641 OOD.ORGANIZATION_CODE,
642 CII.CATEGORY_ID ASSET_CATEGORY_ID,
643 MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
644 EAF.AREA_ID ASSET_LOCATION_ID,
645 MEL.LOCATION_CODES ASSET_LOCATION,
646 EAF.DEPARTMENT_ID OWNING_DEPARTMENT_ID,
647 BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
648 EAFC.FAILURE_CODE,
649 EAFC.CAUSE_CODE,
650 EAFC.RESOLUTION_CODE,
651 EAF.FAILURE_DATE,
652 EAFC.COMMENTS,
653 DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE ),
654 NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
655 (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
656 ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
657 (WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
658 METER.METER_NAME METER_NAME,
659 METER.METER_UOM METER_UOM,
660 DECODE(METER.METER_TYPE,2,METER.CURRENT_READING,1,
661 DECODE( LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ),
662 NULL, METER.CURRENT_READING,
663 (METER.CURRENT_READING -
664 (LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ))) )) READING_BETWEEN_FAILURES,
665 'Y' INCLUDE_FOR_READING_AGGR,
666 'Y' INCLUDE_FOR_COST_AGGR,
667 METER.PRIMARY_FAILURE_METER,
668 METER.METER_ID
669 FROM WIP_DISCRETE_JOBS WDJ,
670 WIP_ENTITIES WE,
671 CSI_ITEM_INSTANCES CII,
672 MTL_CATEGORIES_KFV MCKFV,
673 MTL_SYSTEM_ITEMS_KFV MSIKFV,
674 MTL_EAM_LOCATIONS MEL,
675 BOM_DEPARTMENTS BD,
676 EAM_ASSET_FAILURE_CODES EAFC,
677 EAM_ASSET_FAILURES EAF,
678 ORG_ORGANIZATION_DEFINITIONS OOD,
679 (SELECT
680 ccb.counter_id METER_ID,
681 cctl.name METER_NAME,
682 ccb.uom_code METER_UOM,
683 CCA.SOURCE_OBJECT_ID MAINTENANCE_OBJECT_ID,
684 CCR.COUNTER_READING CURRENT_READING,
685 CCR.VALUE_TIMESTAMP CURRENT_READING_DATE,
686 CCA.PRIMARY_FAILURE_FLAG PRIMARY_FAILURE_METER,
687 decode(ct.transaction_type_id,92,ct.source_header_ref_id,to_number(null)) WIP_ENTITY_ID,
688 CCB.reading_type METER_TYPE
689 FROM csi_counters_b CCB,csi_counters_tl cctl, csi_counter_readings CCR, csi_counter_associations CCA, csi_transactions CT
690 WHERE
691 ccb.counter_id = cctl.counter_id
692 and SYSDATE BETWEEN nvl(ccb.start_date_active, SYSDATE-1) AND nvl(ccb.end_date_active, SYSDATE+1)
693 and cctl.language = userenv('LANG') and ccb.counter_type = 'REGULAR'
694 AND CCB.COUNTER_ID = CCA.COUNTER_ID
695 AND CCR.COUNTER_ID(+) = CCB.COUNTER_ID
696 AND CCR.transaction_id = CT.transaction_id(+)
697 and SYSDATE BETWEEN nvl(cca.start_date_active, SYSDATE-1) AND nvl(cca.end_date_active, SYSDATE+1)
698 AND ( (l_selected_meter IS NULL AND CCA.PRIMARY_FAILURE_FLAG = 'Y') OR
699 (l_selected_meter IS NOT NULL AND CCB.COUNTER_ID = l_selected_meter))
700 AND CCB.EAM_REQUIRED_FLAG = 'Y'
701 AND CCR.COUNTER_VALUE_ID IN
702 (
703 SELECT
704 METER_READING_ID
705 FROM
706 (
707 SELECT
708 Max(EMR1.METER_READING_ID) METER_READING_ID
709 FROM EAM_METER_READINGS_V EMR1
710 GROUP BY EMR1.WIP_ENTITY_ID,
711 EMR1.METER_ID
712 )
713 )) METER
714 WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
715 AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
716 AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
717 AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
718 AND WDJ.STATUS_TYPE IN (4,5,12)
719 AND EAF.SOURCE_TYPE = 1
720 AND EAF.OBJECT_TYPE = 3
721 AND EAF.OBJECT_ID = CII.INSTANCE_ID
722 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
723 AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
724 AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
725 AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
726 AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
727 AND MEL.LOCATION_ID (+) = EAF.AREA_ID
728 AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
729 AND EAF.OBJECT_ID = METER.MAINTENANCE_OBJECT_ID (+)
730 AND EAF.SOURCE_ID = METER.WIP_ENTITY_ID (+)
731 AND EAFC.FAILURE_CODE IS NOT NULL
732 AND EAFC.CAUSE_CODE IS NOT NULL
733 AND EAFC.RESOLUTION_CODE IS NOT NULL
734 AND EAF.FAILURE_DATE IS NOT NULL
735 AND EAF.OBJECT_ID = l_gen_object_id
736 AND (l_to_date IS NULL OR EAF.FAILURE_DATE <= l_to_date))
737 WHERE (l_from_date IS NULL OR FAILURE_DATE >= l_from_date);
738
739 BEGIN
740 g_module_name := 'GET_HISTORY_RECORDS_SIMPLE';
741
742 /*This is to rollback all transactions (uncommited) done w.r.t. EAM_FAILURE_HISTORY_TEMP in the current session */
743 ROLLBACK;
744
745 IF p_gen_object_id = 0 THEN
746 l_gen_object_id := NULL;
747 ELSE
748 l_gen_object_id := p_gen_object_id;
749 END IF;
750
751 IF p_selected_meter = 0 THEN
752 l_selected_meter := NULL;
753 ELSE
754 l_selected_meter := p_selected_meter;
755 END IF;
756
757 IF p_to_date = '' THEN
758 l_to_date := NULL;
759 ELSE
760 l_to_date := p_to_date;
761 END IF;
762
763 IF p_from_date = '' THEN
764 l_from_date := NULL;
765 ELSE
766 l_from_date := p_from_date;
767 END IF;
768
769
770 l_validate_meters := 'N';
771 l_validate_currency := 'Y';
772 l_current_org_id := p_current_org_id;
773 OPEN c_history_records_simple;
774 LOOP
775 FETCH c_history_records_simple BULK COLLECT INTO l_asset_failure_tbl LIMIT 500;
776
777 IF ( l_asset_failure_tbl.Count > 0 ) THEN
778
779 --Currency Validations and UOM Validations are not needed in Failure History Page.
780 /* VALIDATE_RECORDS( p_asset_failure_tbl => l_asset_failure_tbl,
781 p_validate_meters => l_validate_meters,
782 p_validate_currency => l_validate_currency,
783 p_current_org_id => l_current_org_id,
784 x_unmatched_uom_class => l_unmatched_uom_class,
785 x_unmatched_currency => l_unmatched_currency); */
786
787 SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_group_id FROM DUAL;
788
789 INSERT_INTO_TEMP_TABLE(l_group_id, l_asset_failure_tbl);
790
791 --No need to calculate Repair Costs in Failure History Page.
792 --COMPUTE_REPAIR_COSTS(l_group_id);
793 END IF;
794
795 EXIT WHEN c_history_records_simple%NOTFOUND;
796 END LOOP;
797
798 CLOSE c_history_records_simple;
799
800 x_group_id := l_group_id;
801 x_return_status := 'S';
802 x_unmatched_uom_class := l_unmatched_uom_class;
803 x_unmatched_currency := l_unmatched_currency;
804
805 EXCEPTION
806 WHEN OTHERS THEN
807 x_return_status := 'E';
808 x_msg_data := 'Error in '||g_module_name||':'||SQLERRM;
809 END GET_HISTORY_RECORDS_SIMPLE;
810
811
812 Procedure GET_HISTORY_RECORDS_FA_SIMPLE
813 ( P_GEN_OBJECT_ID IN NUMBER,
814 P_MAINT_GROUP_ID IN NUMBER,
815 P_CATEGORY_ID IN NUMBER,
816 P_FAILURE_CODE IN VARCHAR2,
817 P_FROM_DATE IN DATE,
818 P_TO_DATE IN DATE,
819 P_INCLUDE_CHILDREN IN VARCHAR2,
820 P_VIEW_BY IN VARCHAR2,
821 P_COMPUTE_REPAIR_COSTS IN VARCHAR2,
822 P_SELECTED_METER IN NUMBER,
823 P_CURRENT_ORG_ID IN NUMBER,
824 X_GROUP_ID OUT NOCOPY NUMBER,
825 x_return_status OUT NOCOPY VARCHAR2,
826 x_msg_count OUT NOCOPY NUMBER,
827 x_msg_data OUT NOCOPY VARCHAR2,
828 x_unmatched_uom_class OUT NOCOPY VARCHAR2,
829 x_unmatched_currency OUT NOCOPY VARCHAR2) IS
830
831 l_asset_failure_tbl EAM_FAILURE_ANALYSIS_PVT.eam_asset_failure_tbl_type;
832 l_group_id NUMBER;
833
834 l_gen_object_id number;
835 l_maint_group_id number;
836 l_category_id number;
837 l_selected_meter number;
838
839 l_unmatched_uom_class VARCHAR2(1) := 'N';
840 l_unmatched_currency VARCHAR2(1) := 'N';
841 l_validate_meters VARCHAR2(1);
842 l_validate_currency VARCHAR2(1);
843
844 l_msg_data VARCHAR2(500);
845 l_return_status VARCHAR2(1) := 'S';
846
847 CURSOR c_hist_recs_ac_simple IS
848 SELECT ASSET_TYPE,
849 MAINTENANCE_OBJECT_ID,
850 MAINTAINED_NUMBER,
851 DESCRIPTIVE_TEXT,
852 MAINTAINED_GROUP,
853 MAINTAINED_GROUP_ID,
854 WIP_ENTITY_ID,
855 WIP_ENTITY_NAME,
856 ORGANIZATION_ID,
857 ORGANIZATION_CODE,
858 ASSET_CATEGORY,
859 ASSET_CATEGORY_ID,
860 ASSET_LOCATION,
861 OWNING_DEPARTMENT,
862 FAILURE_CODE,
863 CAUSE_CODE,
864 RESOLUTION_CODE,
865 FAILURE_DATE,
866 COMMENTS,
867 DAYS_BETWEEN_FAILURES,
868 TIME_TO_REPAIR,
869 METER_ID,
870 METER_NAME,
871 METER_UOM,
872 READING_BETWEEN_FAILURES,
873 INCLUDE_FOR_READING_AGGR,
874 INCLUDE_FOR_COST_AGGR
875 from
876 (SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
877 EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
878 CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
879 CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
880 MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
881 NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
882 WDJ.WIP_ENTITY_ID,
883 WE.WIP_ENTITY_NAME,
884 WDJ.ORGANIZATION_ID,
885 OOD.ORGANIZATION_CODE,
886 CII.CATEGORY_ID ASSET_CATEGORY_ID,
887 MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
888 EAF.AREA_ID ASSET_LOCATION_ID,
889 MEL.LOCATION_CODES ASSET_LOCATION,
890 EAF.DEPARTMENT_ID OWNING_DEPARTMENT_ID,
891 BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
892 EAFC.FAILURE_CODE,
893 EAFC.CAUSE_CODE,
894 EAFC.RESOLUTION_CODE,
895 EAF.FAILURE_DATE,
896 EAFC.COMMENTS,
897 DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY CII.INSTANCE_ID, CII.CATEGORY_ID ORDER BY EAF.FAILURE_DATE),
898 --NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
899 NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII.CREATION_DATE)
900 FROM CSI_ITEM_INSTANCES CII1
901 WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
902 AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
903 (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
904 ( PARTITION BY CII.INSTANCE_ID, CII.CATEGORY_ID ORDER BY EAF.FAILURE_DATE)) )) DAYS_BETWEEN_FAILURES,
905 (WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
906 NULL METER_NAME,
907 NULL METER_UOM,
908 NULL READING_BETWEEN_FAILURES,
909 'Y' INCLUDE_FOR_READING_AGGR,
910 'Y' INCLUDE_FOR_COST_AGGR,
911 NULL METER_ID
912 FROM WIP_DISCRETE_JOBS WDJ,
913 WIP_ENTITIES WE,
914 CSI_ITEM_INSTANCES CII,
915 MTL_CATEGORIES_KFV MCKFV,
916 MTL_SYSTEM_ITEMS_KFV MSIKFV,
917 MTL_EAM_LOCATIONS MEL,
918 BOM_DEPARTMENTS BD,
919 EAM_ASSET_FAILURE_CODES EAFC,
920 EAM_ASSET_FAILURES EAF,
921 ORG_ORGANIZATION_DEFINITIONS OOD
922 WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
923 AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
924 AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
925 AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
926 AND WDJ.STATUS_TYPE IN (4,5,12)
927 AND EAF.SOURCE_TYPE = 1
928 AND EAF.OBJECT_TYPE = 3
929 AND EAF.OBJECT_ID = CII.INSTANCE_ID
930 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
931 AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
932 AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
933 AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
934 AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
935 AND MEL.LOCATION_ID (+) = EAF.AREA_ID
936 AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
937 AND EAFC.FAILURE_CODE IS NOT NULL
938 AND EAFC.CAUSE_CODE IS NOT NULL
939 AND EAFC.RESOLUTION_CODE IS NOT NULL
940 AND EAF.FAILURE_DATE IS NOT NULL
941 AND (l_gen_object_id IS NULL OR EAF.OBJECT_ID = l_gen_object_id)
942 AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
943 AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
944 AND (p_failure_code IS NULL OR EAFC.FAILURE_CODE = p_failure_code)
945 AND (p_to_date IS NULL OR EAF.FAILURE_DATE <= p_to_date))
946 WHERE (p_from_date IS NULL OR FAILURE_DATE >= p_from_date) ;
947
948 CURSOR c_hist_recs_fc_simple IS
949 SELECT ASSET_TYPE,
950 MAINTENANCE_OBJECT_ID,
951 MAINTAINED_NUMBER,
952 DESCRIPTIVE_TEXT,
953 MAINTAINED_GROUP,
954 MAINTAINED_GROUP_ID,
955 WIP_ENTITY_ID,
956 WIP_ENTITY_NAME,
957 ORGANIZATION_ID,
958 ORGANIZATION_CODE,
959 ASSET_CATEGORY,
960 ASSET_CATEGORY_ID,
961 ASSET_LOCATION,
962 OWNING_DEPARTMENT,
963 FAILURE_CODE,
964 CAUSE_CODE,
965 RESOLUTION_CODE,
966 FAILURE_DATE,
967 COMMENTS,
968 DAYS_BETWEEN_FAILURES,
969 TIME_TO_REPAIR,
970 METER_ID,
971 METER_NAME,
972 METER_UOM,
973 READING_BETWEEN_FAILURES,
974 INCLUDE_FOR_READING_AGGR,
975 INCLUDE_FOR_COST_AGGR
976 from
977 (SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
978 EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
979 CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
980 CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
981 MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
982 NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
983 WDJ.WIP_ENTITY_ID,
984 WE.WIP_ENTITY_NAME,
985 WDJ.ORGANIZATION_ID,
986 OOD.ORGANIZATION_CODE,
987 CII.CATEGORY_ID ASSET_CATEGORY_ID,
988 MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
989 EAF.AREA_ID ASSET_LOCATION_ID,
990 MEL.LOCATION_CODES ASSET_LOCATION,
991 EAF.DEPARTMENT_ID OWNING_DEPARTMENT_ID,
992 BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
993 EAFC.FAILURE_CODE,
994 EAFC.CAUSE_CODE,
995 EAFC.RESOLUTION_CODE,
996 EAF.FAILURE_DATE,
997 EAFC.COMMENTS,
998 DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY CII.INSTANCE_ID, EAFC.FAILURE_CODE ORDER BY EAF.FAILURE_DATE),
999 --NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
1000 NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
1001 FROM CSI_ITEM_INSTANCES CII1
1002 WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
1003 AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
1004 (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
1005 ( PARTITION BY CII.INSTANCE_ID, EAFC.FAILURE_CODE ORDER BY EAF.FAILURE_DATE)) )) DAYS_BETWEEN_FAILURES,
1006 (WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
1007 NULL METER_NAME,
1008 NULL METER_UOM,
1009 NULL READING_BETWEEN_FAILURES,
1010 'Y' INCLUDE_FOR_READING_AGGR,
1011 'Y' INCLUDE_FOR_COST_AGGR,
1012 NULL METER_ID
1013 FROM WIP_DISCRETE_JOBS WDJ,
1014 WIP_ENTITIES WE,
1015 CSI_ITEM_INSTANCES CII,
1016 MTL_CATEGORIES_KFV MCKFV,
1017 MTL_SYSTEM_ITEMS_KFV MSIKFV,
1018 MTL_EAM_LOCATIONS MEL,
1019 BOM_DEPARTMENTS BD,
1020 EAM_ASSET_FAILURE_CODES EAFC,
1021 EAM_ASSET_FAILURES EAF,
1022 ORG_ORGANIZATION_DEFINITIONS OOD
1023 WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
1024 AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
1025 AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
1026 AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
1027 AND WDJ.STATUS_TYPE IN (4,5,12)
1028 AND EAF.SOURCE_TYPE = 1
1029 AND EAF.OBJECT_TYPE = 3
1030 AND EAF.OBJECT_ID = CII.INSTANCE_ID
1031 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
1032 AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
1033 AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
1034 AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
1035 AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
1036 AND MEL.LOCATION_ID (+) = EAF.AREA_ID
1037 AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
1038 AND EAFC.FAILURE_CODE IS NOT NULL
1039 AND EAFC.CAUSE_CODE IS NOT NULL
1040 AND EAFC.RESOLUTION_CODE IS NOT NULL
1041 AND EAF.FAILURE_DATE IS NOT NULL
1042 AND (l_gen_object_id IS NULL OR EAF.OBJECT_ID = l_gen_object_id)
1043 AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
1044 AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
1045 AND (p_failure_code IS NULL OR EAFC.FAILURE_CODE = p_failure_code)
1046 AND (p_to_date IS NULL OR EAF.FAILURE_DATE <= p_to_date))
1047 WHERE (p_from_date IS NULL OR FAILURE_DATE >= p_from_date) ;
1048
1049 CURSOR c_hist_meter_recs_an_simple IS
1050 SELECT ASSET_TYPE,
1051 MAINTENANCE_OBJECT_ID,
1052 MAINTAINED_NUMBER,
1053 DESCRIPTIVE_TEXT,
1054 MAINTAINED_GROUP,
1055 MAINTAINED_GROUP_ID,
1056 WIP_ENTITY_ID,
1057 WIP_ENTITY_NAME,
1058 ORGANIZATION_ID,
1059 ORGANIZATION_CODE,
1060 ASSET_CATEGORY,
1061 ASSET_CATEGORY_ID,
1062 ASSET_LOCATION,
1063 OWNING_DEPARTMENT,
1064 FAILURE_CODE,
1065 CAUSE_CODE,
1066 RESOLUTION_CODE,
1067 FAILURE_DATE,
1068 COMMENTS,
1069 DAYS_BETWEEN_FAILURES,
1070 TIME_TO_REPAIR,
1071 METER_ID,
1072 METER_NAME,
1073 METER_UOM,
1074 READING_BETWEEN_FAILURES,
1075 INCLUDE_FOR_READING_AGGR,
1076 INCLUDE_FOR_COST_AGGR
1077 from
1078 (SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
1079 EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
1080 CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
1081 CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
1082 MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
1083 NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
1084 WDJ.WIP_ENTITY_ID,
1085 WE.WIP_ENTITY_NAME,
1086 WDJ.ORGANIZATION_ID,
1087 OOD.ORGANIZATION_CODE,
1088 CII.CATEGORY_ID ASSET_CATEGORY_ID,
1089 MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
1090 EAF.AREA_ID ASSET_LOCATION_ID,
1091 MEL.LOCATION_CODES ASSET_LOCATION,
1092 EAF.DEPARTMENT_ID OWNING_DEPARTMENT_ID,
1093 BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
1094 EAFC.FAILURE_CODE,
1095 EAFC.CAUSE_CODE,
1096 EAFC.RESOLUTION_CODE,
1097 EAF.FAILURE_DATE,
1098 EAFC.COMMENTS,
1099 DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE ),
1100 NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
1101 (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
1102 ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
1103 (WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
1104 METER.METER_NAME METER_NAME,
1105 METER.METER_UOM METER_UOM,
1106 DECODE(METER.METER_TYPE,2,METER.CURRENT_READING,1,
1107 DECODE( LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ),
1108 NULL, METER.CURRENT_READING,
1109 (METER.CURRENT_READING -
1110 (LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ))) )) READING_BETWEEN_FAILURES,
1111 'Y' INCLUDE_FOR_READING_AGGR,
1112 'Y' INCLUDE_FOR_COST_AGGR,
1113 METER.METER_ID,
1114 METER.PRIMARY_FAILURE_METER
1115 FROM WIP_DISCRETE_JOBS WDJ,
1116 WIP_ENTITIES WE,
1117 CSI_ITEM_INSTANCES CII,
1118 MTL_CATEGORIES_KFV MCKFV,
1119 MTL_SYSTEM_ITEMS_KFV MSIKFV,
1120 MTL_EAM_LOCATIONS MEL,
1121 BOM_DEPARTMENTS BD,
1122 EAM_ASSET_FAILURE_CODES EAFC,
1123 EAM_ASSET_FAILURES EAF,
1124 ORG_ORGANIZATION_DEFINITIONS OOD,
1125 (SELECT
1126 ccb.counter_id METER_ID,
1127 cctl.name METER_NAME,
1128 ccb.uom_code METER_UOM,
1129 CCA.SOURCE_OBJECT_ID MAINTENANCE_OBJECT_ID,
1130 CCR.COUNTER_READING CURRENT_READING,
1131 CCR.VALUE_TIMESTAMP CURRENT_READING_DATE,
1132 CCA.PRIMARY_FAILURE_FLAG PRIMARY_FAILURE_METER,
1133 decode(ct.transaction_type_id,92,ct.source_header_ref_id,to_number(null)) WIP_ENTITY_ID,
1134 CCB.reading_type METER_TYPE
1135 FROM csi_counters_b CCB,csi_counters_tl cctl, csi_counter_readings CCR, csi_counter_associations CCA, csi_transactions CT
1136 WHERE
1137 ccb.counter_id = cctl.counter_id
1138 and SYSDATE BETWEEN nvl(ccb.start_date_active, SYSDATE-1) AND nvl(ccb.end_date_active, SYSDATE+1)
1139 and cctl.language = userenv('LANG') and ccb.counter_type = 'REGULAR'
1140 AND CCB.COUNTER_ID = CCA.COUNTER_ID
1141 AND CCR.COUNTER_ID(+) = CCB.COUNTER_ID
1142 AND CCR.transaction_id = CT.transaction_id(+)
1143 and SYSDATE BETWEEN nvl(cca.start_date_active, SYSDATE-1) AND nvl(cca.end_date_active, SYSDATE+1)
1144 AND ( (l_selected_meter IS NULL AND CCA.PRIMARY_FAILURE_FLAG = 'Y') OR
1145 (l_selected_meter IS NOT NULL AND CCB.COUNTER_ID = l_selected_meter))
1146 AND CCB.EAM_REQUIRED_FLAG = 'Y'
1147 AND CCR.COUNTER_VALUE_ID IN
1148 (
1149 SELECT
1150 METER_READING_ID
1151 FROM
1152 (
1153 SELECT
1154 Max(EMR1.METER_READING_ID) METER_READING_ID
1155 FROM EAM_METER_READINGS_V EMR1
1156 GROUP BY EMR1.WIP_ENTITY_ID,
1157 EMR1.METER_ID
1158 )
1159 )) METER
1160 WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
1161 AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
1162 AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
1163 AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
1164 AND WDJ.STATUS_TYPE IN (4,5,12)
1165 AND EAF.SOURCE_TYPE = 1
1166 AND EAF.OBJECT_TYPE = 3
1167 AND EAF.OBJECT_ID = CII.INSTANCE_ID
1168 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
1169 AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
1170 AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
1171 AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
1172 AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
1173 AND MEL.LOCATION_ID (+) = EAF.AREA_ID
1174 AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
1175 AND EAF.OBJECT_ID = METER.MAINTENANCE_OBJECT_ID (+)
1176 AND METER.WIP_ENTITY_ID (+) = EAF.SOURCE_ID
1177 AND EAFC.FAILURE_CODE IS NOT NULL
1178 AND EAFC.CAUSE_CODE IS NOT NULL
1179 AND EAFC.RESOLUTION_CODE IS NOT NULL
1180 AND EAF.FAILURE_DATE IS NOT NULL
1181 AND (l_gen_object_id IS NULL OR EAF.OBJECT_ID = l_gen_object_id)
1182 AND (l_maint_group_id IS NULL OR MSIKFV.INVENTORY_ITEM_ID = l_maint_group_id)
1183 AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
1184 AND (p_failure_code IS NULL OR EAFC.FAILURE_CODE = p_failure_code)
1185 AND (p_to_date IS NULL OR EAF.FAILURE_DATE <= p_to_date))
1186 WHERE (p_from_date IS NULL OR FAILURE_DATE >= p_from_date);
1187
1188 CURSOR c_hist_meter_recs_ag_simple IS
1189 SELECT ASSET_TYPE,
1190 MAINTENANCE_OBJECT_ID,
1191 MAINTAINED_NUMBER,
1192 DESCRIPTIVE_TEXT,
1193 MAINTAINED_GROUP,
1194 MAINTAINED_GROUP_ID,
1195 WIP_ENTITY_ID,
1196 WIP_ENTITY_NAME,
1197 ORGANIZATION_ID,
1198 ORGANIZATION_CODE,
1199 ASSET_CATEGORY,
1200 ASSET_CATEGORY_ID,
1201 ASSET_LOCATION,
1202 OWNING_DEPARTMENT,
1203 FAILURE_CODE,
1204 CAUSE_CODE,
1205 RESOLUTION_CODE,
1206 FAILURE_DATE,
1207 COMMENTS,
1208 DAYS_BETWEEN_FAILURES,
1209 TIME_TO_REPAIR,
1210 METER_ID,
1211 METER_NAME,
1212 METER_UOM,
1213 READING_BETWEEN_FAILURES,
1214 INCLUDE_FOR_READING_AGGR,
1215 INCLUDE_FOR_COST_AGGR
1216 from
1217 (SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
1218 EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
1219 CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
1220 CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
1221 MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
1222 NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
1223 WDJ.WIP_ENTITY_ID,
1224 WE.WIP_ENTITY_NAME,
1225 WDJ.ORGANIZATION_ID,
1226 OOD.ORGANIZATION_CODE,
1227 CII.CATEGORY_ID ASSET_CATEGORY_ID,
1228 MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
1229 EAF.AREA_ID ASSET_LOCATION_ID,
1230 MEL.LOCATION_CODES ASSET_LOCATION,
1231 EAF.DEPARTMENT_ID OWNING_DEPARTMENT_ID,
1232 BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
1233 EAFC.FAILURE_CODE,
1234 EAFC.CAUSE_CODE,
1235 EAFC.RESOLUTION_CODE,
1236 EAF.FAILURE_DATE,
1237 EAFC.COMMENTS,
1238 DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY CII.INSTANCE_ID, CII.INVENTORY_ITEM_ID ORDER BY EAF.FAILURE_DATE ),
1239 --NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
1240 NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
1241 FROM CSI_ITEM_INSTANCES CII1
1242 WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
1243 AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
1244 (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
1245 ( PARTITION BY CII.INSTANCE_ID, CII.INVENTORY_ITEM_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
1246 (WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
1247 METER.METER_NAME METER_NAME,
1248 METER.METER_UOM METER_UOM,
1249 DECODE(METER.METER_TYPE,2,METER.CURRENT_READING,1,
1250 DECODE( LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY CII.INVENTORY_ITEM_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ),
1251 NULL, METER.CURRENT_READING,
1252 (METER.CURRENT_READING -
1253 (LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY CII.INVENTORY_ITEM_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ))) )) READING_BETWEEN_FAILURES,
1254 'Y' INCLUDE_FOR_READING_AGGR,
1255 'Y' INCLUDE_FOR_COST_AGGR,
1256 METER.METER_ID,
1257 METER.PRIMARY_FAILURE_METER
1258 FROM WIP_DISCRETE_JOBS WDJ,
1259 WIP_ENTITIES WE,
1260 CSI_ITEM_INSTANCES CII,
1261 MTL_CATEGORIES_KFV MCKFV,
1262 MTL_SYSTEM_ITEMS_KFV MSIKFV,
1263 MTL_EAM_LOCATIONS MEL,
1264 BOM_DEPARTMENTS BD,
1265 EAM_ASSET_FAILURE_CODES EAFC,
1266 EAM_ASSET_FAILURES EAF,
1267 ORG_ORGANIZATION_DEFINITIONS OOD,
1268 (SELECT
1269 ccb.counter_id METER_ID,
1270 cctl.name METER_NAME,
1271 ccb.uom_code METER_UOM,
1272 CCA.SOURCE_OBJECT_ID MAINTENANCE_OBJECT_ID,
1273 CCR.COUNTER_READING CURRENT_READING,
1274 CCR.VALUE_TIMESTAMP CURRENT_READING_DATE,
1275 CCA.PRIMARY_FAILURE_FLAG PRIMARY_FAILURE_METER,
1276 decode(ct.transaction_type_id,92,ct.source_header_ref_id,to_number(null)) WIP_ENTITY_ID,
1277 CCB.reading_type METER_TYPE
1278 FROM csi_counters_b CCB,csi_counters_tl cctl, csi_counter_readings CCR, csi_counter_associations CCA, csi_transactions CT
1279 WHERE
1280 ccb.counter_id = cctl.counter_id
1281 and SYSDATE BETWEEN nvl(ccb.start_date_active, SYSDATE-1) AND nvl(ccb.end_date_active, SYSDATE+1)
1282 and cctl.language = userenv('LANG') and ccb.counter_type = 'REGULAR'
1283 AND CCB.COUNTER_ID = CCA.COUNTER_ID
1284 AND CCR.COUNTER_ID(+) = CCB.COUNTER_ID
1285 AND CCR.transaction_id = CT.transaction_id(+)
1286 and SYSDATE BETWEEN nvl(cca.start_date_active, SYSDATE-1) AND nvl(cca.end_date_active, SYSDATE+1)
1287 AND ( (l_selected_meter IS NULL AND CCA.PRIMARY_FAILURE_FLAG = 'Y') OR
1288 (l_selected_meter IS NOT NULL AND CCB.COUNTER_ID = l_selected_meter))
1289 AND CCB.EAM_REQUIRED_FLAG = 'Y'
1290 AND CCR.COUNTER_VALUE_ID IN
1291 (
1292 SELECT
1293 METER_READING_ID
1294 FROM
1295 (
1296 SELECT
1297 Max(EMR1.METER_READING_ID) METER_READING_ID
1298 FROM EAM_METER_READINGS_V EMR1
1299 GROUP BY EMR1.WIP_ENTITY_ID,
1300 EMR1.METER_ID
1301 )
1302 )) METER
1303 WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
1304 AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
1305 AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
1306 AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
1307 AND WDJ.STATUS_TYPE IN (4,5,12)
1308 AND EAF.SOURCE_TYPE = 1
1309 AND EAF.OBJECT_TYPE = 3
1310 AND EAF.OBJECT_ID = CII.INSTANCE_ID
1311 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
1312 AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
1313 AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
1314 AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
1315 AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
1316 AND MEL.LOCATION_ID (+) = EAF.AREA_ID
1317 AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
1318 AND EAF.OBJECT_ID = METER.MAINTENANCE_OBJECT_ID (+)
1319 AND METER.WIP_ENTITY_ID (+) = EAF.SOURCE_ID
1320 AND EAFC.FAILURE_CODE IS NOT NULL
1321 AND EAFC.CAUSE_CODE IS NOT NULL
1322 AND EAFC.RESOLUTION_CODE IS NOT NULL
1323 AND EAF.FAILURE_DATE IS NOT NULL
1324 AND (l_gen_object_id IS NULL OR EAF.OBJECT_ID = l_gen_object_id)
1325 AND (l_maint_group_id IS NULL OR MSIKFV.INVENTORY_ITEM_ID = l_maint_group_id)
1326 AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
1327 AND (p_failure_code IS NULL OR EAFC.FAILURE_CODE = p_failure_code)
1328 AND (p_to_date IS NULL OR EAF.FAILURE_DATE <= p_to_date))
1329 WHERE (p_from_date IS NULL OR FAILURE_DATE >= p_from_date)
1330 ORDER BY MAINTAINED_GROUP_ID;
1331
1332
1333 BEGIN
1334 g_module_name := 'GET_HISTORY_RECORDS_FA_SIMPLE';
1335 /*This is to rollback all transactions (uncommited) done w.r.t. EAM_FAILURE_HISTORY_TEMP in the current session */
1336 ROLLBACK;
1337 IF p_gen_object_id = 0 THEN
1338 l_gen_object_id := NULL;
1339 ELSE
1340 l_gen_object_id := p_gen_object_id;
1341 END IF;
1342
1343 IF p_maint_group_id = 0 THEN
1344 l_maint_group_id := NULL;
1345 ELSE
1346 l_maint_group_id := p_maint_group_id;
1347 END IF;
1348
1349 IF p_category_id = 0 THEN
1350 l_category_id := NULL;
1351 ELSE
1352 l_category_id := p_category_id;
1353 END IF;
1354
1355 IF p_selected_meter = 0 THEN
1356 l_selected_meter := NULL;
1357 ELSE
1358 l_selected_meter := p_selected_meter;
1359 END IF;
1360
1361 IF (p_include_children = 'N' OR p_include_children IS NULL) THEN
1362 l_validate_currency := p_compute_repair_costs;
1363 IF p_view_by IN (1,3,4) THEN
1364 l_validate_meters := 'N';
1365 -- l_validate_currency := 'Y';
1366 ELSIF p_view_by = 2 THEN
1367 l_validate_meters := 'Y';
1368 -- l_validate_currency := 'Y';
1369 END IF;
1370 l_asset_failure_tbl.DELETE();
1371
1372 IF p_view_by = 1 /* 'ASSET_NUMBER' */ THEN
1373 OPEN c_hist_meter_recs_an_simple;
1374 LOOP
1375 FETCH c_hist_meter_recs_an_simple BULK COLLECT INTO l_asset_failure_tbl LIMIT 500;
1376 IF ( l_asset_failure_tbl.Count > 0 ) THEN
1377 VALIDATE_RECORDS( p_asset_failure_tbl => l_asset_failure_tbl,
1378 p_validate_meters => l_validate_meters,
1379 p_validate_currency => l_validate_currency,
1380 p_current_org_id => p_current_org_id,
1381 x_unmatched_uom_class => l_unmatched_uom_class,
1382 x_unmatched_currency => l_unmatched_currency);
1383
1384 SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_group_id FROM DUAL;
1385
1386 INSERT_INTO_TEMP_TABLE(p_group_id => l_group_id,
1387 p_asset_failure_tbl => l_asset_failure_tbl);
1388
1389 IF p_compute_repair_costs = 'Y' THEN
1390 COMPUTE_REPAIR_COSTS(l_group_id);
1391 END if;
1392 END IF;
1393
1394 EXIT WHEN c_hist_meter_recs_an_simple%NOTFOUND;
1395 END LOOP;
1396
1397 CLOSE c_hist_meter_recs_an_simple;
1398
1399 ELSIF p_view_by = 2 /* 'ASSET_GROUP' */ THEN
1400 OPEN c_hist_meter_recs_ag_simple;
1401 LOOP
1402 FETCH c_hist_meter_recs_ag_simple BULK COLLECT INTO l_asset_failure_tbl LIMIT 500;
1403 IF ( l_asset_failure_tbl.Count > 0 ) THEN
1404 VALIDATE_RECORDS( p_asset_failure_tbl => l_asset_failure_tbl,
1405 p_validate_meters => l_validate_meters,
1406 p_validate_currency => l_validate_currency,
1407 p_current_org_id => p_current_org_id,
1408 x_unmatched_uom_class => l_unmatched_uom_class,
1409 x_unmatched_currency => l_unmatched_currency);
1410
1411 SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_group_id FROM DUAL;
1412
1413 INSERT_INTO_TEMP_TABLE(p_group_id => l_group_id,
1414 p_asset_failure_tbl => l_asset_failure_tbl);
1415
1416 IF p_compute_repair_costs = 'Y' THEN
1417 COMPUTE_REPAIR_COSTS(l_group_id);
1418 END if;
1419 END IF;
1420
1421 EXIT WHEN c_hist_meter_recs_ag_simple%NOTFOUND;
1422 END LOOP;
1423
1424 CLOSE c_hist_meter_recs_ag_simple;
1425
1426 ELSIF p_view_by = 3 /* 'ASSET_CATEGORY' */ THEN
1427 OPEN c_hist_recs_ac_simple;
1428 LOOP
1429 FETCH c_hist_recs_ac_simple BULK COLLECT INTO l_asset_failure_tbl LIMIT 500;
1430 IF ( l_asset_failure_tbl.Count > 0 ) THEN
1431 VALIDATE_RECORDS( p_asset_failure_tbl => l_asset_failure_tbl,
1432 p_validate_meters => l_validate_meters,
1433 p_validate_currency => l_validate_currency,
1434 p_current_org_id => p_current_org_id,
1435 x_unmatched_uom_class => l_unmatched_uom_class,
1436 x_unmatched_currency => l_unmatched_currency);
1437
1438 SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_group_id FROM DUAL;
1439
1440 INSERT_INTO_TEMP_TABLE(p_group_id => l_group_id,
1441 p_asset_failure_tbl => l_asset_failure_tbl);
1442
1443 IF p_compute_repair_costs = 'Y' THEN
1444 COMPUTE_REPAIR_COSTS(l_group_id);
1445 END if;
1446 END IF;
1447
1448 EXIT WHEN c_hist_recs_ac_simple%NOTFOUND;
1449 END LOOP;
1450
1451 CLOSE c_hist_recs_ac_simple;
1452
1453 ELSIF p_view_by = 4 /* 'FAILURE_CODE' */ THEN
1454 OPEN c_hist_recs_fc_simple;
1455 LOOP
1456 FETCH c_hist_recs_fc_simple BULK COLLECT INTO l_asset_failure_tbl LIMIT 500;
1457 IF ( l_asset_failure_tbl.Count > 0 ) THEN
1458 VALIDATE_RECORDS( p_asset_failure_tbl => l_asset_failure_tbl,
1459 p_validate_meters => l_validate_meters,
1460 p_validate_currency => l_validate_currency,
1461 p_current_org_id => p_current_org_id,
1462 x_unmatched_uom_class => l_unmatched_uom_class,
1463 x_unmatched_currency => l_unmatched_currency);
1464
1465 SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_group_id FROM DUAL;
1466
1467 INSERT_INTO_TEMP_TABLE(p_group_id => l_group_id,
1468 p_asset_failure_tbl => l_asset_failure_tbl);
1469
1470 IF p_compute_repair_costs = 'Y' THEN
1471 COMPUTE_REPAIR_COSTS(l_group_id);
1472 END if;
1473 END IF;
1474
1475 EXIT WHEN c_hist_recs_fc_simple%NOTFOUND;
1476 END LOOP;
1477
1478 CLOSE c_hist_recs_fc_simple;
1479 END IF;
1480
1481 --Processing so far successful
1482 x_return_status := 'S';
1483
1484 ElSIF p_include_children = 'Y' THEN
1485 GET_CHILD_RECORDS_FA_SIMPLE
1486 ( P_GEN_OBJECT_ID =>l_gen_object_id,
1487 P_MAINT_GROUP_ID =>l_maint_group_id,
1488 P_CATEGORY_ID =>l_category_id,
1489 P_FAILURE_CODE =>p_failure_code,
1490 P_FROM_DATE =>p_from_date,
1491 P_TO_DATE =>p_to_date,
1492 P_VIEW_BY =>p_view_by,
1493 P_COMPUTE_REPAIR_COSTS =>p_compute_repair_costs,
1494 P_CURRENT_ORG_ID =>p_current_org_id,
1495 x_GROUP_ID =>l_group_id,
1496 x_return_status =>l_return_status,
1497 x_msg_data =>l_msg_data,
1498 x_unmatched_uom_class =>l_unmatched_uom_class,
1499 x_unmatched_currency =>l_unmatched_currency );
1500 END IF;
1501 x_return_status := l_return_status;
1502
1503 x_msg_data := l_msg_data;
1504 x_group_id := l_group_id;
1505
1506 x_unmatched_uom_class := l_unmatched_uom_class;
1507 x_unmatched_currency := l_unmatched_currency;
1508 EXCEPTION
1509 WHEN OTHERS THEN
1510 x_return_status := 'E';
1511 x_msg_data := 'Error in '||g_module_name||':'||SQLERRM;
1512 END GET_HISTORY_RECORDS_FA_SIMPLE;
1513
1514
1515 Procedure INSERT_INTO_TEMP_TABLE
1516 ( p_group_id IN NUMBER,
1517 P_ASSET_FAILURE_TBL IN eam_asset_failure_tbl_type) IS
1518 l_asset_failure_tbl eam_asset_failure_tbl_type;
1519 BEGIN
1520 g_module_name := 'INSERT_INTO_TEMP_TABLE';
1521 l_asset_failure_tbl := p_asset_failure_tbl;
1522
1523 FOR i in l_asset_failure_tbl.first .. l_asset_failure_tbl.last
1524 LOOP
1525
1526 INSERT INTO EAM_FAILURE_HISTORY_TEMP
1527 (GROUP_ID,
1528 ASSET_TYPE,
1529 MAINTENANCE_OBJECT_ID,
1530 MAINTAINED_NUMBER,
1531 DESCRIPTIVE_TEXT,
1532 MAINTAINED_GROUP,
1533 MAINTAINED_GROUP_ID ,
1534 WIP_ENTITY_ID,
1535 WIP_ENTITY_NAME,
1536 MAINT_ORGANIZATION_ID,
1537 ORGANIZATION_CODE,
1538 ASSET_CATEGORY,
1539 ASSET_CATEGORY_ID,
1540 ASSET_LOCATION,
1541 OWNING_DEPARTMENT,
1542 FAILURE_CODE,
1543 CAUSE_CODE,
1544 RESOLUTION_CODE,
1545 FAILURE_DATE,
1546 DAYS_BETWEEN_FAILURES,
1547 TIME_TO_REPAIR,
1548 COMMENTS,
1549 METER_NAME,
1550 METER_UOM,
1551 READING_BETWEEN_FAILURES,
1552 INCLUDE_FOR_READING_AGGR,
1553 INCLUDE_FOR_COST_AGGR)
1554 values
1555 (p_group_id,
1556 l_asset_failure_tbl(i).ASSET_TYPE,
1557 l_asset_failure_tbl(i).MAINTENANCE_OBJECT_ID,
1558 l_asset_failure_tbl(i).MAINTAINED_NUMBER,
1559 l_asset_failure_tbl(i).DESCRIPTIVE_TEXT,
1560 l_asset_failure_tbl(i).MAINTAINED_GROUP,
1561 l_asset_failure_tbl(i).MAINTAINED_GROUP_ID,
1562 l_asset_failure_tbl(i).WIP_ENTITY_ID,
1563 l_asset_failure_tbl(i).WIP_ENTITY_NAME,
1564 l_asset_failure_tbl(i).ORGANIZATION_ID,
1565 l_asset_failure_tbl(i).ORGANIZATION_CODE,
1566 l_asset_failure_tbl(i).ASSET_CATEGORY,
1567 l_asset_failure_tbl(i).ASSET_CATEGORY_ID,
1568 l_asset_failure_tbl(i).ASSET_LOCATION,
1569 l_asset_failure_tbl(i).OWNING_DEPARTMENT,
1570 l_asset_failure_tbl(i).FAILURE_CODE,
1571 l_asset_failure_tbl(i).CAUSE_CODE,
1572 l_asset_failure_tbl(i).RESOLUTION_CODE,
1573 l_asset_failure_tbl(i).FAILURE_DATE,
1574 l_asset_failure_tbl(i).DAYS_BETWEEN_FAILURES,
1575 l_asset_failure_tbl(i).TIME_TO_REPAIR,
1576 l_asset_failure_tbl(i).COMMENTS,
1577 l_asset_failure_tbl(i).METER_NAME,
1578 l_asset_failure_tbl(i).METER_UOM,
1579 l_asset_failure_tbl(i).READING_BETWEEN_FAILURES,
1580 l_asset_failure_tbl(i).INCLUDE_FOR_READING_AGGR,
1581 l_asset_failure_tbl(i).INCLUDE_FOR_COST_AGGR);
1582
1583 END LOOP;
1584 END INSERT_INTO_TEMP_TABLE;
1585
1586 Procedure COMPUTE_REPAIR_COSTS
1587 ( P_GROUP_ID IN NUMBER) IS
1588
1589 TYPE repair_cost_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1590 TYPE wip_entity_id_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1591
1592 p_repair_cost_tbl repair_cost_tbl_type;
1593 p_wip_entity_id_tbl wip_entity_id_tbl_type;
1594
1595 CURSOR c_cost_calc is
1596 SELECT SUM(WEPB.ACTUAL_MAT_COST+WEPB.ACTUAL_LAB_COST+WEPB.ACTUAL_EQP_COST) COST,
1597 WEPB.WIP_ENTITY_ID
1598 FROM WIP_EAM_PERIOD_BALANCES WEPB, EAM_FAILURE_HISTORY_TEMP EFHT
1599 WHERE EFHT.GROUP_ID = p_group_id
1600 AND EFHT.MAINT_ORGANIZATION_ID = WEPB.ORGANIZATION_ID
1601 AND EFHT.WIP_ENTITY_ID = WEPB.WIP_ENTITY_ID
1602 AND EFHT.INCLUDE_FOR_COST_AGGR = 'Y'
1603 GROUP BY WEPB.WIP_ENTITY_ID;
1604
1605
1606 BEGIN
1607 g_module_name := 'COMPUTE_REPAIR_COSTS';
1608
1609 OPEN c_cost_calc;
1610 LOOP
1611 FETCH c_cost_calc BULK COLLECT INTO p_repair_cost_tbl, p_wip_entity_id_tbl LIMIT 500;
1612
1613 IF ( p_wip_entity_id_tbl.Count > 0 ) THEN
1614 FORALL i IN p_wip_entity_id_tbl.first .. p_wip_entity_id_tbl.last
1615 UPDATE EAM_FAILURE_HISTORY_TEMP
1616 SET COST_TO_REPAIR = Nvl(P_repair_cost_tbl(i),0)
1617 WHERE WIP_ENTITY_ID = P_wip_entity_id_tbl(i)
1618 AND GROUP_ID = p_group_id
1619 AND INCLUDE_FOR_COST_AGGR = 'Y';
1620 END IF;
1621 EXIT WHEN c_cost_calc%NOTFOUND;
1622 END LOOP;
1623 CLOSE c_cost_calc;
1624 END COMPUTE_REPAIR_COSTS;
1625
1626
1627
1628 PROCEDURE VALIDATE_RECORDS(P_ASSET_FAILURE_TBL IN OUT NOCOPY EAM_ASSET_FAILURE_TBL_TYPE,
1629 P_VALIDATE_METERS VARCHAR2,
1630 P_VALIDATE_CURRENCY VARCHAR2,
1631 P_CURRENT_ORG_ID NUMBER,
1632 x_unmatched_uom_class OUT NOCOPY VARCHAR2,
1633 x_unmatched_currency OUT NOCOPY VARCHAR2) IS
1634
1635 l_asset_failure_tbl EAM_ASSET_FAILURE_TBL_TYPE;
1636 l_same_prim_curr NUMBER;
1637 l_uom1_conv_rate NUMBER;
1638 l_uom2_conv_rate NUMBER;
1639 l_meter_uom1 VARCHAR2(3);
1640 l_meter_uom2 VARCHAR2(3);
1641 l_primary_uom VARCHAR2(3);
1642 l_org2 NUMBER;
1643 BEGIN
1644 g_module_name := 'VALIDATE_RECORDS';
1645 l_asset_failure_tbl := P_asset_failure_tbl;
1646 FOR i in l_asset_failure_tbl.first .. l_asset_failure_tbl.last
1647 loop
1648 if ( p_validate_currency = 'Y') then
1649 l_org2 := l_asset_failure_tbl(i).organization_id;
1650
1651 if l_org2 <> p_current_org_id then
1652
1653 BEGIN
1654 l_asset_failure_tbl(i).include_for_cost_aggr := 'Y';
1655
1656 select 1 into l_same_prim_curr
1657 from cst_organization_definitions cod1, cst_organization_definitions cod2,
1658 gl_sets_of_books gsob1, gl_sets_of_books gsob2
1659 where cod1.organization_id = p_current_org_id
1660 and cod2.organization_id = l_org2
1661 and cod1.set_of_books_id = gsob1.set_of_books_id
1662 and cod2.set_of_books_id = gsob2.set_of_books_id
1663 and gsob1.currency_code = gsob2.currency_code;
1664
1665 EXCEPTION
1666 WHEN No_Data_Found THEN
1667 --warning:there are repair costs that are not included because they are not in the current maintenance organization's currency.
1668 x_unmatched_currency := 'Y';
1669 l_asset_failure_tbl(i).include_for_cost_aggr := 'N';
1670 END;
1671 end if; --(l_org1 <> l_org2)
1672 end if; --p_compute_repair_costs = 'Y'
1673
1674 If p_validate_meters = 'Y' then
1675 --If i = l_asset_failure_tbl.first then
1676 IF ( ( i = l_asset_failure_tbl.FIRST) OR
1677 ( l_asset_failure_tbl(i).maintained_group_id <> l_asset_failure_tbl(l_asset_failure_tbl.PRIOR(i)).maintained_group_id) ) then
1678 l_meter_uom1 := l_asset_failure_tbl(i).meter_uom;
1679 else
1680 l_meter_uom2 := l_asset_failure_tbl(i).meter_uom;
1681
1682 if l_meter_uom1 <> l_meter_uom2 then
1683 begin
1684 l_asset_failure_tbl(i).include_for_reading_aggr := 'Y';
1685
1686 select nvl(MUC1.CONVERSION_RATE,0) , nvl(MUC2.CONVERSION_RATE,0), MUOFVL.UOM_CODE
1687 into l_uom1_conv_rate, l_uom2_conv_rate, l_primary_uom
1688 from MTL_UOM_CONVERSIONS MUC1, MTL_UOM_CONVERSIONS MUC2, MTL_UNITS_OF_MEASURE_VL MUOFVL
1689 where MUC1.UOM_CODE = l_meter_uom1
1690 and MUC2.UOM_CODE = l_meter_uom2
1691 AND NVL(MUC1.DISABLE_DATE, SYSDATE + 1) > SYSDATE
1692 AND NVL(MUC2.DISABLE_DATE, SYSDATE + 1) > SYSDATE
1693 AND MUC1.INVENTORY_ITEM_ID = 0
1694 AND MUC2.INVENTORY_ITEM_ID = 0
1695 AND MUC1.UOM_CLASS = MUC2.UOM_CLASS
1696 AND MUOFVL.UOM_CLASS = MUC1.UOM_CLASS
1697 AND MUOFVL.BASE_UOM_FLAG = 'Y'
1698 AND NVL(MUOFVL.DISABLE_DATE, SYSDATE + 1) > SYSDATE;
1699
1700
1701 l_asset_failure_tbl(i).meter_uom := l_primary_uom;
1702 l_asset_failure_tbl(i).reading_between_failures :=
1703 l_asset_failure_tbl(i).reading_between_failures * l_uom2_conv_rate ;
1704
1705 l_asset_failure_tbl(l_asset_failure_tbl.PRIOR(i)).meter_uom := l_primary_uom;
1706 l_asset_failure_tbl(l_asset_failure_tbl.PRIOR(i)).reading_between_failures :=
1707 l_asset_failure_tbl(l_asset_failure_tbl.PRIOR(i)).reading_between_failures * l_uom1_conv_rate ;
1708
1709 exception
1710 when no_data_found then
1711 --warning:there are meters which are not included because they are not in the same uom class.
1712 x_unmatched_uom_class := 'Y';
1713 l_asset_failure_tbl(i).include_for_reading_aggr := 'N';
1714 end;
1715 end if; -- (l_meter_uom1 <> l_meter_uom2)
1716 end if; -- (i = l_asset_failure_tbl.first)
1717 end if; -- (p_validate_meters = 'Y')
1718 End loop;
1719 p_asset_failure_tbl := l_asset_failure_tbl;
1720 END VALIDATE_RECORDS;
1721
1722 Procedure GET_CHILD_RECORDS_FA_SIMPLE
1723 ( P_GEN_OBJECT_ID IN NUMBER,
1724 P_MAINT_GROUP_ID IN NUMBER,
1725 P_CATEGORY_ID IN NUMBER,
1726 P_FAILURE_CODE IN VARCHAR2,
1727 P_FROM_DATE IN DATE,
1728 P_TO_DATE IN DATE,
1729 P_VIEW_BY IN VARCHAR2,
1730 P_COMPUTE_REPAIR_COSTS IN VARCHAR2,
1731 P_CURRENT_ORG_ID IN NUMBER,
1732 x_group_id IN OUT NOCOPY NUMBER,
1733 x_return_status OUT NOCOPY VARCHAR2,
1734 x_msg_data OUT NOCOPY VARCHAR2,
1735 x_unmatched_uom_class OUT NOCOPY VARCHAR2,
1736 x_unmatched_currency OUT NOCOPY VARCHAR2) IS
1737
1738 l_asset_failure_tbl EAM_FAILURE_ANALYSIS_PVT.eam_asset_failure_tbl_type;
1739 l_group_id NUMBER;
1740
1741 l_gen_object_id number;
1742 l_maint_group_id number;
1743 l_category_id number;
1744
1745 l_unmatched_uom_class VARCHAR2(1) := 'N';
1746 l_unmatched_currency VARCHAR2(1) := 'N';
1747 l_validate_meters VARCHAR2(1);
1748 l_validate_currency VARCHAR2(1);
1749
1750 l_msg_data VARCHAR2(500);
1751 l_return_status VARCHAR2(1) := 'S';
1752
1753 CURSOR c_recs_ac_simple IS
1754 SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
1755 EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
1756 CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
1757 CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
1758 MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
1759 NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
1760 WDJ.WIP_ENTITY_ID,
1761 WE.WIP_ENTITY_NAME,
1762 WDJ.ORGANIZATION_ID,
1763 OOD.ORGANIZATION_CODE,
1764 MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
1765 CII.CATEGORY_ID ASSET_CATEGORY_ID,
1766 MEL.LOCATION_CODES ASSET_LOCATION,
1767 BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
1768 EAFC.FAILURE_CODE,
1769 EAFC.CAUSE_CODE,
1770 EAFC.RESOLUTION_CODE,
1771 EAF.FAILURE_DATE,
1772 EAFC.COMMENTS,
1773 DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY CII.CATEGORY_ID ORDER BY EAF.FAILURE_DATE ),
1774 --NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
1775 NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
1776 FROM CSI_ITEM_INSTANCES CII1
1777 WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
1778 AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
1779 (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
1780 ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
1781 (WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
1782 NULL METER_ID,
1783 NULL METER_NAME,
1784 NULL METER_UOM,
1785 NULL READING_BETWEEN_FAILURES,
1786 'Y' INCLUDE_FOR_READING_AGGR,
1787 'Y' INCLUDE_FOR_COST_AGGR
1788 FROM WIP_DISCRETE_JOBS WDJ,
1789 WIP_ENTITIES WE,
1790 CSI_ITEM_INSTANCES CII,
1791 MTL_CATEGORIES_KFV MCKFV,
1792 MTL_SYSTEM_ITEMS_KFV MSIKFV,
1793 MTL_EAM_LOCATIONS MEL,
1794 BOM_DEPARTMENTS BD,
1795 EAM_ASSET_FAILURE_CODES EAFC,
1796 EAM_ASSET_FAILURES EAF,
1797 ORG_ORGANIZATION_DEFINITIONS OOD
1798 WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
1799 AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
1800 AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
1801 AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
1802 AND WDJ.STATUS_TYPE IN (4,5,12)
1803 AND EAF.SOURCE_TYPE = 1
1804 AND EAF.OBJECT_TYPE = 3
1805 AND EAF.OBJECT_ID = CII.INSTANCE_ID
1806 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
1807 AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
1808 AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
1809 AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
1810 AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
1811 AND MEL.LOCATION_ID (+) = EAF.AREA_ID
1812 AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
1813 AND EAF.OBJECT_ID IN (SELECT CII.INSTANCE_ID
1814 FROM CSI_ITEM_INSTANCES CII,
1815 MTL_SERIAL_NUMBERS MSN
1816 WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
1817 AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
1818 AND MSN.gen_object_id IN
1819 (
1820 SELECT OBJECT_ID --This part will select all children assets
1821 FROM MTL_OBJECT_GENEALOGY
1822 START WITH PARENT_OBJECT_ID IN
1823 (
1824 SELECT MSN.GEN_OBJECT_ID PARENT_OBJECT_ID
1825 FROM MTL_SERIAL_NUMBERS MSN,
1826 CSI_ITEM_INSTANCES CII
1827 WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
1828 AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
1829 AND (l_gen_object_id IS NULL OR CII.INSTANCE_ID = l_gen_object_id)
1830 AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
1831 AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
1832 )
1833 CONNECT BY NOCYCLE PRIOR OBJECT_ID = PARENT_OBJECT_ID
1834 )
1835 UNION
1836 SELECT CII.INSTANCE_ID OBJECT_ID --This part will select the parent assets
1837 FROM CSI_ITEM_INSTANCES CII
1838 WHERE (l_gen_object_id IS NULL OR CII.INSTANCE_ID = l_gen_object_id)
1839 AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
1840 AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
1841 ) ;
1842
1843 /* [Parent + Child Failure WOs][When ANY of failure code/from date/to date have been entered as search criteria] - View By Asset Category */
1844 CURSOR c_recs_wo_ac_simple IS
1845 SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
1846 EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
1847 NVL(WDJ.ASSET_NUMBER, WDJ.REBUILD_SERIAL_NUMBER) MAINTAINED_NUMBER,
1848 CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
1849 MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
1850 NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
1851 WDJ.WIP_ENTITY_ID,
1852 WE.WIP_ENTITY_NAME,
1853 WDJ.ORGANIZATION_ID,
1854 OOD.ORGANIZATION_CODE,
1855 MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
1856 CII.CATEGORY_ID ASSET_CATEGORY_ID,
1857 MEL.LOCATION_CODES ASSET_LOCATION,
1858 BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
1859 EAFC.FAILURE_CODE,
1860 EAFC.CAUSE_CODE,
1861 EAFC.RESOLUTION_CODE,
1862 EAF.FAILURE_DATE,
1863 EAFC.COMMENTS,
1864 DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY CII.CATEGORY_ID ORDER BY EAF.FAILURE_DATE ),
1865 --NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
1866 NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
1867 FROM CSI_ITEM_INSTANCES CII1
1868 WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
1869 AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
1870 (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
1871 ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
1872 (WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
1873 NULL METER_ID,
1874 NULL METER_NAME,
1875 NULL METER_UOM,
1876 NULL READING_BETWEEN_FAILURES,
1877 'Y' INCLUDE_FOR_READING_AGGR,
1878 'Y' INCLUDE_FOR_COST_AGGR
1879 FROM WIP_DISCRETE_JOBS WDJ,
1880 WIP_ENTITIES WE,
1881 MTL_CATEGORIES_KFV MCKFV,
1882 MTL_SYSTEM_ITEMS_KFV MSIKFV,
1883 MTL_EAM_LOCATIONS MEL,
1884 BOM_DEPARTMENTS BD,
1885 EAM_ASSET_FAILURE_CODES EAFC,
1886 EAM_ASSET_FAILURES EAF,
1887 CSI_ITEM_INSTANCES CII,
1888 ORG_ORGANIZATION_DEFINITIONS OOD
1889 WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
1890 AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
1891 AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
1892 AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
1893 AND WDJ.STATUS_TYPE IN (4,5,12)
1894 AND EAF.SOURCE_TYPE = 1
1895 AND EAF.OBJECT_TYPE = 3
1896 AND EAF.OBJECT_ID = CII.INSTANCE_ID
1897 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
1898 AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
1899 AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
1900 AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
1901 AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
1902 AND MEL.LOCATION_ID (+) = EAF.AREA_ID
1903 AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
1904 AND (p_to_date IS NULL OR FAILURE_DATE <= p_to_date)
1905 AND (p_from_date IS NULL OR FAILURE_DATE >= p_from_date)
1906 AND EAF.OBJECT_ID IN (SELECT CII.INSTANCE_ID
1907 FROM CSI_ITEM_INSTANCES CII,
1908 MTL_SERIAL_NUMBERS MSN
1909 WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
1910 AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
1911 AND MSN.gen_object_id IN (
1912 SELECT OBJECT_ID --This part will select all children assets
1913 FROM MTL_OBJECT_GENEALOGY
1914 START WITH PARENT_OBJECT_ID IN
1915 ( SELECT MSN.GEN_OBJECT_ID PARENT_OBJECT_ID
1916 FROM CSI_ITEM_INSTANCES CII,
1917 EAM_ASSET_FAILURE_CODES EAFC,
1918 EAM_ASSET_FAILURES EAF,
1919 WIP_DISCRETE_JOBS WDJ,
1920 MTL_SERIAL_NUMBERS MSN
1921 WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
1922 AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
1923 AND WDJ.STATUS_TYPE IN (4,5,12)
1924 AND EAF.SOURCE_TYPE = 1
1925 AND EAF.OBJECT_TYPE = 3
1926 AND EAF.OBJECT_ID = CII.INSTANCE_ID
1927 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
1928 AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
1929 AND CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
1930 AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
1931 AND (l_gen_object_id IS NULL OR EAF.OBJECT_ID = l_gen_object_id)
1932 AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
1933 AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
1934 AND (p_failure_code IS NULL OR EAFC.FAILURE_CODE = p_failure_code)
1935 AND (p_to_date IS NULL OR FAILURE_DATE <= p_to_date)
1936 AND (p_from_date IS NULL OR FAILURE_DATE >= p_from_date)
1937 )
1938 CONNECT BY NOCYCLE PRIOR OBJECT_ID = PARENT_OBJECT_ID
1939 )
1940
1941 UNION
1942 SELECT MAINTENANCE_OBJECT_ID FROM(
1943 SELECT MAINTENANCE_OBJECT_ID FROM (
1944 SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
1945 EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
1946 NVL(WDJ.ASSET_NUMBER, WDJ.REBUILD_SERIAL_NUMBER) MAINTAINED_NUMBER,
1947 CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
1948 MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
1949 NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
1950 WDJ.WIP_ENTITY_ID,
1951 WE.WIP_ENTITY_NAME,
1952 WDJ.ORGANIZATION_ID,
1953 OOD.ORGANIZATION_CODE,
1954 MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
1955 CII.CATEGORY_ID ASSET_CATEGORY_ID,
1956 MEL.LOCATION_CODES ASSET_LOCATION,
1957 BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
1958 EAFC.FAILURE_CODE,
1959 EAFC.CAUSE_CODE,
1960 EAFC.RESOLUTION_CODE,
1961 EAF.FAILURE_DATE,
1962 EAFC.COMMENTS,
1963 DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY CII.CATEGORY_ID ORDER BY EAF.FAILURE_DATE ),
1964 --NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
1965 NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
1966 FROM CSI_ITEM_INSTANCES CII1
1967 WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
1968 AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
1969 (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
1970 ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
1971 (WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
1972 NULL METER_ID,
1973 NULL METER_NAME,
1974 NULL METER_UOM,
1975 NULL READING_BETWEEN_FAILURES,
1976 'Y' INCLUDE_FOR_READING_AGGR,
1977 'Y' INCLUDE_FOR_COST_AGGR
1978 FROM WIP_DISCRETE_JOBS WDJ,
1979 WIP_ENTITIES WE,
1980 MTL_CATEGORIES_KFV MCKFV,
1981 MTL_SYSTEM_ITEMS_KFV MSIKFV,
1982 MTL_EAM_LOCATIONS MEL,
1983 BOM_DEPARTMENTS BD,
1984 EAM_ASSET_FAILURE_CODES EAFC,
1985 EAM_ASSET_FAILURES EAF,
1986 ORG_ORGANIZATION_DEFINITIONS OOD,
1987 CSI_ITEM_INSTANCES CII
1988 WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
1989 AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
1990 AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
1991 AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
1992 AND WDJ.STATUS_TYPE IN (4,5,12)
1993 AND EAF.SOURCE_TYPE = 1
1994 AND EAF.OBJECT_TYPE = 3
1995 AND EAF.OBJECT_ID = CII.INSTANCE_ID
1996 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
1997 AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
1998 AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
1999 AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
2000 AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
2001 AND MEL.LOCATION_ID (+) = EAF.AREA_ID
2002 AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
2003 AND EAF.OBJECT_ID IN (SELECT EAF.OBJECT_ID OBJECT_ID --This part will select parent assets
2004 FROM EAM_ASSET_FAILURE_CODES EAFC,
2005 EAM_ASSET_FAILURES EAF,
2006 WIP_DISCRETE_JOBS WDJ,
2007 CSI_ITEM_INSTANCES CII
2008 WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
2009 AND WDJ.STATUS_TYPE IN (4,5,12)
2010 AND EAF.SOURCE_TYPE = 1
2011 AND EAF.OBJECT_TYPE = 3
2012 AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
2013 AND EAF.OBJECT_ID = CII.INSTANCE_ID
2014 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
2015 AND (l_gen_object_id IS NULL OR EAF.OBJECT_ID = l_gen_object_id)
2016 AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
2017 AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
2018 AND (p_failure_code IS NULL OR EAFC.FAILURE_CODE = p_failure_code)
2019 )
2020 )
2021 WHERE (p_to_date IS NULL OR FAILURE_DATE <= p_to_date)
2022 AND (p_from_date IS NULL OR FAILURE_DATE >= p_from_date) ) );
2023
2024 /* [Parent + Child Failure WOs][When NONE of failure code/from date/to date have been entered as search criteria] - View By Failure Code */
2025
2026 CURSOR c_recs_fc_simple IS
2027 SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
2028 EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
2029 CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
2030 CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
2031 MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
2032 NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
2033 WDJ.WIP_ENTITY_ID,
2034 WE.WIP_ENTITY_NAME,
2035 WDJ.ORGANIZATION_ID,
2036 OOD.ORGANIZATION_CODE,
2037 MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
2038 CII.CATEGORY_ID ASSET_CATEGORY_ID,
2039 MEL.LOCATION_CODES ASSET_LOCATION,
2040 BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
2041 EAFC.FAILURE_CODE,
2042 EAFC.CAUSE_CODE,
2043 EAFC.RESOLUTION_CODE,
2044 EAF.FAILURE_DATE,
2045 EAFC.COMMENTS,
2046 DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY EAFC.FAILURE_CODE ORDER BY EAF.FAILURE_DATE ),
2047 --NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
2048 NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
2049 FROM CSI_ITEM_INSTANCES CII1
2050 WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
2051 AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
2052 (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
2053 ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
2054 (WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
2055 NULL METER_ID,
2056 NULL METER_NAME,
2057 NULL METER_UOM,
2058 NULL READING_BETWEEN_FAILURES,
2059 'Y' INCLUDE_FOR_READING_AGGR,
2060 'Y' INCLUDE_FOR_COST_AGGR
2061 FROM WIP_DISCRETE_JOBS WDJ,
2062 WIP_ENTITIES WE,
2063 CSI_ITEM_INSTANCES CII,
2064 MTL_CATEGORIES_KFV MCKFV,
2065 MTL_SYSTEM_ITEMS_KFV MSIKFV,
2066 MTL_EAM_LOCATIONS MEL,
2067 BOM_DEPARTMENTS BD,
2068 EAM_ASSET_FAILURE_CODES EAFC,
2069 EAM_ASSET_FAILURES EAF,
2070 ORG_ORGANIZATION_DEFINITIONS OOD
2071 WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
2072 AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
2073 AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
2074 AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
2075 AND WDJ.STATUS_TYPE IN (4,5,12)
2076 AND EAF.SOURCE_TYPE = 1
2077 AND EAF.OBJECT_TYPE = 3
2078 AND EAF.OBJECT_ID = CII.INSTANCE_ID
2079 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
2080 AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
2081 AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
2082 AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
2083 AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
2084 AND MEL.LOCATION_ID (+) = EAF.AREA_ID
2085 AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
2086 AND EAF.OBJECT_ID IN (SELECT CII.INSTANCE_ID
2087 FROM CSI_ITEM_INSTANCES CII,
2088 MTL_SERIAL_NUMBERS MSN
2089 WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
2090 AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
2091 AND MSN.gen_object_id IN
2092 (SELECT OBJECT_ID --This part will select all children assets
2093 FROM MTL_OBJECT_GENEALOGY
2094 START WITH PARENT_OBJECT_ID IN
2095 ( SELECT MSN.GEN_OBJECT_ID PARENT_OBJECT_ID
2096 FROM MTL_SERIAL_NUMBERS MSN,
2097 CSI_ITEM_INSTANCES CII
2098 WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
2099 AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
2100 AND (l_gen_object_id IS NULL OR CII.INSTANCE_ID = l_gen_object_id)
2101 AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
2102 AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
2103 )
2104 CONNECT BY NOCYCLE PRIOR OBJECT_ID = PARENT_OBJECT_ID
2105 )
2106 UNION
2107 SELECT CII.INSTANCE_ID OBJECT_ID --This part will select all parent assets
2108 FROM CSI_ITEM_INSTANCES CII
2109 WHERE (l_gen_object_id IS NULL OR CII.INSTANCE_ID = l_gen_object_id)
2110 AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
2111 AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
2112 ) ;
2113
2114 /* [Parent + Child Failure WOs][When ANY of failure code/from date/to date have been entered as search criteria] - View By Failure Code */
2115 CURSOR c_recs_wo_fc_simple IS
2116 SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
2117 EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
2118 NVL(WDJ.ASSET_NUMBER, WDJ.REBUILD_SERIAL_NUMBER) MAINTAINED_NUMBER,
2119 CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
2120 MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
2121 NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
2122 WDJ.WIP_ENTITY_ID,
2123 WE.WIP_ENTITY_NAME,
2124 WDJ.ORGANIZATION_ID,
2125 OOD.ORGANIZATION_CODE,
2126 MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
2127 CII.CATEGORY_ID ASSET_CATEGORY_ID,
2128 MEL.LOCATION_CODES ASSET_LOCATION,
2129 BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
2130 EAFC.FAILURE_CODE,
2131 EAFC.CAUSE_CODE,
2132 EAFC.RESOLUTION_CODE,
2133 EAF.FAILURE_DATE,
2134 EAFC.COMMENTS,
2135 DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY EAFC.FAILURE_CODE ORDER BY EAF.FAILURE_DATE ),
2136 --NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
2137 NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
2138 FROM CSI_ITEM_INSTANCES CII1
2139 WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
2140 AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
2141 (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
2142 ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
2143 (WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
2144 NULL METER_ID,
2145 NULL METER_NAME,
2146 NULL METER_UOM,
2147 NULL READING_BETWEEN_FAILURES,
2148 'Y' INCLUDE_FOR_READING_AGGR,
2149 'Y' INCLUDE_FOR_COST_AGGR
2150 FROM WIP_DISCRETE_JOBS WDJ,
2151 WIP_ENTITIES WE,
2152 MTL_CATEGORIES_KFV MCKFV,
2153 MTL_SYSTEM_ITEMS_KFV MSIKFV,
2154 MTL_EAM_LOCATIONS MEL,
2155 BOM_DEPARTMENTS BD,
2156 EAM_ASSET_FAILURE_CODES EAFC,
2157 EAM_ASSET_FAILURES EAF,
2158 CSI_ITEM_INSTANCES CII,
2159 ORG_ORGANIZATION_DEFINITIONS OOD
2160 WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
2161 AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
2162 AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
2163 AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
2164 AND WDJ.STATUS_TYPE IN (4,5,12)
2165 AND EAF.SOURCE_TYPE = 1
2166 AND EAF.OBJECT_TYPE = 3
2167 AND EAF.OBJECT_ID = CII.INSTANCE_ID
2168 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
2169 AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
2170 AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
2171 AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
2172 AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
2173 AND MEL.LOCATION_ID (+) = EAF.AREA_ID
2174 AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
2175 AND (p_to_date IS NULL OR FAILURE_DATE <= p_to_date)
2176 AND (p_from_date IS NULL OR FAILURE_DATE >= p_from_date)
2177 AND EAF.OBJECT_ID IN (SELECT CII.INSTANCE_ID
2178 FROM CSI_ITEM_INSTANCES CII,
2179 MTL_SERIAL_NUMBERS MSN
2180 WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
2181 AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
2182 AND MSN.gen_object_id IN
2183 (SELECT OBJECT_ID --This part will select all children assets
2184 FROM MTL_OBJECT_GENEALOGY
2185 START WITH PARENT_OBJECT_ID IN
2186 ( SELECT MSN.GEN_OBJECT_ID PARENT_OBJECT_ID
2187 FROM EAM_ASSET_FAILURE_CODES EAFC,
2188 EAM_ASSET_FAILURES EAF,
2189 WIP_DISCRETE_JOBS WDJ,
2190 MTL_SERIAL_NUMBERS MSN,
2191 CSI_ITEM_INSTANCES CII
2192 WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
2193 AND WDJ.STATUS_TYPE IN (4,5,12)
2194 AND EAF.SOURCE_TYPE = 1
2195 AND EAF.OBJECT_TYPE = 3
2196 AND EAF.OBJECT_ID = CII.INSTANCE_ID
2197 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
2198 AND CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
2199 AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
2200 AND (l_gen_object_id IS NULL OR EAF.OBJECT_ID = l_gen_object_id)
2201 AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
2202 AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
2203 AND (p_failure_code IS NULL OR EAFC.FAILURE_CODE = p_failure_code)
2204 AND (p_to_date IS NULL OR FAILURE_DATE <= p_to_date)
2205 AND (p_from_date IS NULL OR FAILURE_DATE >= p_from_date)
2206 )
2207 CONNECT BY NOCYCLE PRIOR OBJECT_ID = PARENT_OBJECT_ID)
2208
2209 UNION
2210 SELECT MAINTENANCE_OBJECT_ID FROM (
2211 SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
2212 EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
2213 NVL(WDJ.ASSET_NUMBER, WDJ.REBUILD_SERIAL_NUMBER) MAINTAINED_NUMBER,
2214 CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
2215 MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
2216 NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
2217 WDJ.WIP_ENTITY_ID,
2218 WE.WIP_ENTITY_NAME,
2219 WDJ.ORGANIZATION_ID,
2220 OOD.ORGANIZATION_CODE,
2221 MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
2222 CII.CATEGORY_ID ASSET_CATEGORY_ID,
2223 MEL.LOCATION_CODES ASSET_LOCATION,
2224 BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
2225 EAFC.FAILURE_CODE,
2226 EAFC.CAUSE_CODE,
2227 EAFC.RESOLUTION_CODE,
2228 EAF.FAILURE_DATE,
2229 EAFC.COMMENTS,
2230 DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY EAFC.FAILURE_CODE ORDER BY EAF.FAILURE_DATE ),
2231 --NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
2232 NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
2233 FROM CSI_ITEM_INSTANCES CII1
2234 WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
2235 AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
2236 (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
2237 ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
2238 (WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
2239 NULL METER_ID,
2240 NULL METER_NAME,
2241 NULL METER_UOM,
2242 NULL READING_BETWEEN_FAILURES,
2243 'Y' INCLUDE_FOR_READING_AGGR,
2244 'Y' INCLUDE_FOR_COST_AGGR
2245 FROM WIP_DISCRETE_JOBS WDJ,
2246 WIP_ENTITIES WE,
2247 MTL_CATEGORIES_KFV MCKFV,
2248 MTL_SYSTEM_ITEMS_KFV MSIKFV,
2249 MTL_EAM_LOCATIONS MEL,
2250 BOM_DEPARTMENTS BD,
2251 EAM_ASSET_FAILURE_CODES EAFC,
2252 EAM_ASSET_FAILURES EAF,
2253 CSI_ITEM_INSTANCES CII,
2254 ORG_ORGANIZATION_DEFINITIONS OOD
2255 WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
2256 AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
2257 AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
2258 AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
2259 AND WDJ.STATUS_TYPE IN (4,5,12)
2260 AND EAF.SOURCE_TYPE = 1
2261 AND EAF.OBJECT_TYPE = 3
2262 AND EAF.OBJECT_ID = CII.INSTANCE_ID
2263 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
2264 AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
2265 AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
2266 AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
2267 AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
2268 AND MEL.LOCATION_ID (+) = EAF.AREA_ID
2269 AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
2270 AND EAF.OBJECT_ID IN ( SELECT EAF.OBJECT_ID OBJECT_ID --This part will select all parent assets
2271 FROM EAM_ASSET_FAILURE_CODES EAFC,
2272 EAM_ASSET_FAILURES EAF,
2273 WIP_DISCRETE_JOBS WDJ,
2274 CSI_ITEM_INSTANCES
2275 WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
2276 AND WDJ.STATUS_TYPE IN (4,5,12)
2277 AND EAF.SOURCE_TYPE = 1
2278 AND EAF.OBJECT_TYPE = 3
2279 AND EAF.OBJECT_ID = CII.INSTANCE_ID
2280 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
2281 AND (l_gen_object_id IS NULL OR EAF.OBJECT_ID = l_gen_object_id)
2282 AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
2283 AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
2284 AND (p_failure_code IS NULL OR EAFC.FAILURE_CODE = p_failure_code)
2285 )
2286 )
2287 WHERE (p_to_date IS NULL OR FAILURE_DATE <= p_to_date)
2288 AND (p_from_date IS NULL OR FAILURE_DATE >= p_from_date) );
2289
2290
2291 /* [Parent + Child Failure WOs][When NONE of failure code/from date/to date have been entered as search criteria] - View By Asset Number */
2292
2293 CURSOR c_meter_recs_an_simple IS
2294 SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
2295 EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
2296 CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
2297 CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
2298 MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
2299 NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
2300 WDJ.WIP_ENTITY_ID,
2301 WE.WIP_ENTITY_NAME,
2302 WDJ.ORGANIZATION_ID,
2303 OOD.ORGANIZATION_CODE,
2304 MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
2305 CII.CATEGORY_ID ASSET_CATEGORY_ID,
2306 MEL.LOCATION_CODES ASSET_LOCATION,
2307 BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
2308 EAFC.FAILURE_CODE,
2309 EAFC.CAUSE_CODE,
2310 EAFC.RESOLUTION_CODE,
2311 EAF.FAILURE_DATE,
2312 EAFC.COMMENTS,
2313 DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE ),
2314 NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
2315 (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
2316 ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
2317 (WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
2318 METER.METER_ID,
2319 METER.METER_NAME METER_NAME,
2320 METER.METER_UOM METER_UOM,
2321 DECODE(METER.METER_TYPE,2,METER.CURRENT_READING,1,
2322 DECODE( LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ),
2323 NULL, METER.CURRENT_READING,
2324 (METER.CURRENT_READING -
2325 (LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ))) )) READING_BETWEEN_FAILURES,
2326 'Y' INCLUDE_FOR_READING_AGGR,
2327 'Y' INCLUDE_FOR_COST_AGGR
2328 FROM WIP_DISCRETE_JOBS WDJ,
2329 WIP_ENTITIES WE,
2330 CSI_ITEM_INSTANCES CII,
2331 MTL_CATEGORIES_KFV MCKFV,
2332 MTL_SYSTEM_ITEMS_KFV MSIKFV,
2333 MTL_EAM_LOCATIONS MEL,
2334 BOM_DEPARTMENTS BD,
2335 EAM_ASSET_FAILURE_CODES EAFC,
2336 EAM_ASSET_FAILURES EAF,
2337 ORG_ORGANIZATION_DEFINITIONS OOD,
2338 (SELECT
2339 ccb.counter_id METER_ID,
2340 cctl.name METER_NAME,
2341 ccb.uom_code METER_UOM,
2342 CCA.SOURCE_OBJECT_ID MAINTENANCE_OBJECT_ID,
2343 CCR.COUNTER_READING CURRENT_READING,
2344 CCR.VALUE_TIMESTAMP CURRENT_READING_DATE,
2345 CCA.PRIMARY_FAILURE_FLAG PRIMARY_FAILURE_METER,
2346 decode(ct.transaction_type_id,92,ct.source_header_ref_id,to_number(null)) WIP_ENTITY_ID,
2347 CCB.reading_type METER_TYPE
2348 FROM csi_counters_b CCB,csi_counters_tl cctl, csi_counter_readings CCR, csi_counter_associations CCA, csi_transactions CT
2349 WHERE
2350 ccb.counter_id = cctl.counter_id
2351 and SYSDATE BETWEEN nvl(ccb.start_date_active, SYSDATE-1) AND nvl(ccb.end_date_active, SYSDATE+1)
2352 and cctl.language = userenv('LANG') and ccb.counter_type = 'REGULAR'
2353 AND CCB.COUNTER_ID = CCA.COUNTER_ID
2354 AND CCR.COUNTER_ID(+) = CCB.COUNTER_ID
2355 AND CCR.transaction_id = CT.transaction_id(+)
2356 and SYSDATE BETWEEN nvl(cca.start_date_active, SYSDATE-1) AND nvl(cca.end_date_active, SYSDATE+1)
2357 AND CCA.PRIMARY_FAILURE_FLAG = 'Y'
2358 AND CCB.EAM_REQUIRED_FLAG = 'Y'
2359 AND CCR.COUNTER_VALUE_ID IN
2360 (
2361 SELECT
2362 METER_READING_ID
2363 FROM
2364 (
2365 SELECT
2366 Max(EMR1.METER_READING_ID) METER_READING_ID
2367 FROM EAM_METER_READINGS_V EMR1
2368 GROUP BY EMR1.WIP_ENTITY_ID,
2369 EMR1.METER_ID
2370 )
2371 )) METER
2372 WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
2373 AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
2374 AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
2375 AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
2376 AND WDJ.STATUS_TYPE IN (4,5,12)
2377 AND EAF.SOURCE_TYPE = 1
2378 AND EAF.OBJECT_TYPE = 3
2379 AND EAF.OBJECT_ID = CII.INSTANCE_ID
2380 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
2381 AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
2382 AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
2383 AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
2384 AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
2385 AND MEL.LOCATION_ID (+) = EAF.AREA_ID
2386 AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
2387 AND EAF.OBJECT_ID = METER.MAINTENANCE_OBJECT_ID (+)
2388 AND METER.WIP_ENTITY_ID (+) = EAF.SOURCE_ID
2389 AND EAF.OBJECT_ID IN (SELECT CII.INSTANCE_ID
2390 FROM CSI_ITEM_INSTANCES CII,
2391 MTL_SERIAL_NUMBERS MSN
2392 WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
2393 AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
2394 AND MSN.gen_object_id IN
2395 (SELECT OBJECT_ID --This part will select all children assets
2396 FROM MTL_OBJECT_GENEALOGY
2397 START WITH PARENT_OBJECT_ID IN
2398 ( SELECT MSN.GEN_OBJECT_ID PARENT_OBJECT_ID
2399 FROM MTL_SERIAL_NUMBERS MSN,
2400 CSI_ITEM_INSTANCES CII
2401 WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
2402 AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
2403 AND (l_gen_object_id IS NULL OR CII.INSTANCE_ID = l_gen_object_id)
2404 AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
2405 AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
2406 )
2407 CONNECT BY NOCYCLE PRIOR OBJECT_ID = PARENT_OBJECT_ID
2408 )
2409 UNION
2410 SELECT MAINTENANCE_OBJECT_ID FROM
2411 (
2412 SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
2413 EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
2414 NVL(WDJ.ASSET_NUMBER, WDJ.REBUILD_SERIAL_NUMBER) MAINTAINED_NUMBER,
2415 CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
2416 MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
2417 NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
2418 WDJ.WIP_ENTITY_ID,
2419 WE.WIP_ENTITY_NAME,
2420 WDJ.ORGANIZATION_ID,
2421 OOD.ORGANIZATION_CODE,
2422 MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
2423 CII.CATEGORY_ID ASSET_CATEGORY_ID,
2424 MEL.LOCATION_CODES ASSET_LOCATION,
2425 BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
2426 EAFC.FAILURE_CODE,
2427 EAFC.CAUSE_CODE,
2428 EAFC.RESOLUTION_CODE,
2429 EAF.FAILURE_DATE,
2430 EAFC.COMMENTS,
2431 DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE ),
2432 NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
2433 (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
2434 ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
2435 (WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
2436 METER.METER_ID,
2437 METER.METER_NAME METER_NAME,
2438 METER.METER_UOM METER_UOM,
2439 DECODE(METER.METER_TYPE,2,METER.CURRENT_READING,1,
2440 DECODE( LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ),
2441 NULL, METER.CURRENT_READING,
2442 (METER.CURRENT_READING -
2443 (LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ))) )) READING_BETWEEN_FAILURES,
2444 'Y' INCLUDE_FOR_READING_AGGR,
2445 'Y' INCLUDE_FOR_COST_AGGR
2446 FROM WIP_DISCRETE_JOBS WDJ,
2447 WIP_ENTITIES WE,
2448 MTL_CATEGORIES_KFV MCKFV,
2449 MTL_SYSTEM_ITEMS_KFV MSIKFV,
2450 MTL_EAM_LOCATIONS MEL,
2451 BOM_DEPARTMENTS BD,
2452 EAM_ASSET_FAILURE_CODES EAFC,
2453 EAM_ASSET_FAILURES EAF,
2454 CSI_ITEM_INSTANCES CII,
2455 ORG_ORGANIZATION_DEFINITIONS OOD,
2456 (SELECT EM.METER_ID,
2457 EM.METER_NAME,
2458 EM.METER_UOM,
2459 EAM.MAINTENANCE_OBJECT_ID,
2460 EMR.CURRENT_READING,
2461 EMR.CURRENT_READING_DATE,
2462 EAM.PRIMARY_FAILURE_METER,
2463 EMR.WIP_ENTITY_ID,
2464 EM.METER_TYPE
2465 FROM EAM_METERS EM,
2466 EAM_ASSET_METERS EAM,
2467 EAM_METER_READINGS EMR
2468 WHERE EM.METER_ID = EAM.METER_ID
2469 AND EMR.METER_ID = EAM.METER_ID
2470 AND EM.REQUIRED_FLAG = 'Y'
2471 --AND EAM.PRIMARY_FAILURE_METER = 'Y'
2472 -- AND ( (l_selected_meter IS NULL AND EAM.PRIMARY_FAILURE_METER = 'Y') OR
2473 -- (l_selected_meter IS NOT NULL AND EM.METER_ID = l_selected_meter))
2474 AND EMR.METER_READING_ID IN
2475 (SELECT METER_READING_ID FROM
2476 (SELECT Max(EMR1.METER_READING_ID) METER_READING_ID,EMR1.WIP_ENTITY_ID,EMR1.METER_ID
2477 FROM EAM_METER_READINGS EMR1
2478 GROUP BY EMR1.WIP_ENTITY_ID, EMR1.METER_ID ))) METER
2479 WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
2480 AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
2481 AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
2482 AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
2483 AND WDJ.STATUS_TYPE IN (4,5,12)
2484 AND EAF.SOURCE_TYPE = 1
2485 AND EAF.OBJECT_TYPE = 3
2486 AND EAF.OBJECT_ID = CII.INSTANCE_ID
2487 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
2488 AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
2489 AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
2490 AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
2491 AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
2492 AND MEL.LOCATION_ID (+) = EAF.AREA_ID
2493 AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
2494 AND EAF.OBJECT_ID = METER.MAINTENANCE_OBJECT_ID (+)
2495 AND METER.WIP_ENTITY_ID (+) = EAF.SOURCE_ID
2496 AND EAF.OBJECT_ID IN ( SELECT CII.INSTANCE_ID OBJECT_ID --This part will select all parent assets
2497 FROM CSI_ITEM_INSTANCES CII
2498 WHERE(l_gen_object_id IS NULL OR CII.INSTANCE_ID = l_gen_object_id)
2499 AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
2500 AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
2501 )));
2502
2503 /* [Parent + Child Failure WOs][When ANY of failure code/from date/to date have been entered as search criteria] - View By Asset Number */
2504 CURSOR c_meter_recs_wo_an_simple IS
2505 SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
2506 EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
2507 CII.INSTANCE_NUMBER,
2508 CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
2509 MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
2510 NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
2511 WDJ.WIP_ENTITY_ID,
2512 WE.WIP_ENTITY_NAME,
2513 WDJ.ORGANIZATION_ID,
2514 OOD.ORGANIZATION_CODE,
2515 MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
2516 CII.CATEGORY_ID ASSET_CATEGORY_ID,
2517 MEL.LOCATION_CODES ASSET_LOCATION,
2518 BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
2519 EAFC.FAILURE_CODE,
2520 EAFC.CAUSE_CODE,
2521 EAFC.RESOLUTION_CODE,
2522 EAF.FAILURE_DATE,
2523 EAFC.COMMENTS,
2524 DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE ),
2525 NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
2526 (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
2527 ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
2528 (WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
2529 METER.METER_ID,
2530 METER.METER_NAME METER_NAME,
2531 METER.METER_UOM METER_UOM,
2532 DECODE(METER.METER_TYPE,2,METER.CURRENT_READING,1,
2533 DECODE( LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ),
2534 NULL, METER.CURRENT_READING,
2535 (METER.CURRENT_READING -
2536 (LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ))) )) READING_BETWEEN_FAILURES,
2537 'Y' INCLUDE_FOR_READING_AGGR,
2538 'Y' INCLUDE_FOR_COST_AGGR
2539 FROM WIP_DISCRETE_JOBS WDJ,
2540 WIP_ENTITIES WE,
2541 MTL_CATEGORIES_KFV MCKFV,
2542 MTL_SYSTEM_ITEMS_KFV MSIKFV,
2543 MTL_EAM_LOCATIONS MEL,
2544 BOM_DEPARTMENTS BD,
2545 EAM_ASSET_FAILURE_CODES EAFC,
2546 EAM_ASSET_FAILURES EAF,
2547 ORG_ORGANIZATION_DEFINITIONS OOD,
2548 CSI_ITEM_INSTANCES CII,
2549 (SELECT EM.METER_ID,
2550 EM.METER_NAME,
2551 EM.METER_UOM,
2552 EAM.MAINTENANCE_OBJECT_ID,
2553 EMR.CURRENT_READING,
2554 EMR.CURRENT_READING_DATE,
2555 EAM.PRIMARY_FAILURE_METER,
2556 EMR.WIP_ENTITY_ID,
2557 EM.METER_TYPE
2558
2559 FROM EAM_METERS EM,
2560 EAM_ASSET_METERS EAM,
2561 EAM_METER_READINGS EMR
2562
2563 WHERE EM.METER_ID = EAM.METER_ID
2564 AND EMR.METER_ID = EAM.METER_ID
2565 AND EM.REQUIRED_FLAG = 'Y'
2566 --AND EAM.PRIMARY_FAILURE_METER = 'Y'
2567 -- AND ( (l_selected_meter IS NULL AND EAM.PRIMARY_FAILURE_METER = 'Y') OR
2568 -- (l_selected_meter IS NOT NULL AND EM.METER_ID = l_selected_meter))
2569 AND EMR.METER_READING_ID IN
2570 (SELECT METER_READING_ID FROM
2571 (SELECT Max(EMR1.METER_READING_ID) METER_READING_ID,EMR1.WIP_ENTITY_ID,EMR1.METER_ID
2572 FROM EAM_METER_READINGS EMR1
2573 GROUP BY EMR1.WIP_ENTITY_ID, EMR1.METER_ID ))) METER
2574 WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
2575 AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
2576 AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
2577 AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
2578 AND WDJ.STATUS_TYPE IN (4,5,12)
2579 AND EAF.SOURCE_TYPE = 1
2580 AND EAF.OBJECT_TYPE = 3
2581 AND EAF.OBJECT_ID = CII.INSTANCE_ID
2582 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
2583 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
2584 -- AND MSN.GEN_OBJECT_ID = WDJ.MAINTENANCE_OBJECT_ID
2585 AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
2586 AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
2587 AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
2588 AND MEL.LOCATION_ID (+) = EAF.AREA_ID
2589 AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
2590 AND EAF.OBJECT_ID = METER.MAINTENANCE_OBJECT_ID (+)
2591 AND METER.WIP_ENTITY_ID (+) = EAF.SOURCE_ID
2592 AND (p_to_date IS NULL OR FAILURE_DATE <= p_to_date)
2593 AND (p_from_date IS NULL OR FAILURE_DATE >= p_from_date)
2594 AND EAF.OBJECT_ID IN (SELECT CII.INSTANCE_ID
2595 FROM CSI_ITEM_INSTANCES CII,
2596 MTL_SERIAL_NUMBERS MSN
2597 WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
2598 AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
2599 AND MSN.gen_object_id IN
2600 (SELECT OBJECT_ID --This part will select all children assets
2601 FROM MTL_OBJECT_GENEALOGY
2602 START WITH PARENT_OBJECT_ID IN
2603 ( SELECT MSN.GEN_OBJECT_ID PARENT_OBJECT_ID
2604 FROM CSI_ITEM_INSTANCES CII,
2605 EAM_ASSET_FAILURE_CODES EAFC,
2606 EAM_ASSET_FAILURES EAF,
2607 WIP_DISCRETE_JOBS WDJ,
2608 MTL_SERIAL_NUMBERS MSN
2609 WHERE NVL(WDJ.STATUS_TYPE,4) IN (4,5,12)
2610 AND EAF.SOURCE_TYPE(+) = 1
2611 AND EAF.OBJECT_TYPE(+) = 3
2612 AND EAF.OBJECT_ID(+) = CII.INSTANCE_ID
2613 AND EAF.FAILURE_ID = EAFC.FAILURE_ID(+)
2614 AND CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
2615 AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
2616 AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
2617 AND (l_gen_object_id IS NULL OR EAF.OBJECT_ID = l_gen_object_id)
2618 AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
2619 AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
2620 AND (p_failure_code IS NULL OR EAFC.FAILURE_CODE = p_failure_code)
2621 AND (p_to_date IS NULL OR FAILURE_DATE <= p_to_date)
2622 AND (p_from_date IS NULL OR FAILURE_DATE >= p_from_date)
2623 )
2624 CONNECT BY NOCYCLE PRIOR OBJECT_ID = PARENT_OBJECT_ID)
2625
2626 UNION
2627 SELECT MAINTENANCE_OBJECT_ID FROM (
2628 SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
2629 EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
2630 NVL(WDJ.ASSET_NUMBER, WDJ.REBUILD_SERIAL_NUMBER) MAINTAINED_NUMBER,
2631 CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
2632 MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
2633 NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
2634 WDJ.WIP_ENTITY_ID,
2635 WE.WIP_ENTITY_NAME,
2636 WDJ.ORGANIZATION_ID,
2637 OOD.ORGANIZATION_CODE,
2638 MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
2639 CII.CATEGORY_ID ASSET_CATEGORY_ID,
2640 MEL.LOCATION_CODES ASSET_LOCATION,
2641 BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
2642 EAFC.FAILURE_CODE,
2643 EAFC.CAUSE_CODE,
2644 EAFC.RESOLUTION_CODE,
2645 EAF.FAILURE_DATE,
2646 EAFC.COMMENTS,
2647 DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE ),
2648 NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
2649 (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
2650 ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
2651 (WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
2652 METER.METER_ID,
2653 METER.METER_NAME METER_NAME,
2654 METER.METER_UOM METER_UOM,
2655 DECODE(METER.METER_TYPE,2,METER.CURRENT_READING,1,
2656 DECODE( LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ),
2657 NULL, METER.CURRENT_READING,
2658 (METER.CURRENT_READING -
2659 (LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ))) )) READING_BETWEEN_FAILURES,
2660 'Y' INCLUDE_FOR_READING_AGGR,
2661 'Y' INCLUDE_FOR_COST_AGGR
2662 FROM WIP_DISCRETE_JOBS WDJ,
2663 WIP_ENTITIES WE,
2664 MTL_CATEGORIES_KFV MCKFV,
2665 MTL_SYSTEM_ITEMS_KFV MSIKFV,
2666 MTL_EAM_LOCATIONS MEL,
2667 BOM_DEPARTMENTS BD,
2668 EAM_ASSET_FAILURE_CODES EAFC,
2669 EAM_ASSET_FAILURES EAF,
2670 ORG_ORGANIZATION_DEFINITIONS OOD,
2671 CSI_ITEM_INSTANCES CII,
2672 (SELECT EM.METER_ID,
2673 EM.METER_NAME,
2674 EM.METER_UOM,
2675 EAM.MAINTENANCE_OBJECT_ID,
2676 EMR.CURRENT_READING,
2677 EMR.CURRENT_READING_DATE,
2678 EAM.PRIMARY_FAILURE_METER,
2679 EMR.WIP_ENTITY_ID,
2680 EM.METER_TYPE
2681 FROM EAM_METERS EM,
2682 EAM_ASSET_METERS EAM,
2683 EAM_METER_READINGS EMR
2684 WHERE EM.METER_ID = EAM.METER_ID
2685 AND EMR.METER_ID = EAM.METER_ID
2686 AND EM.REQUIRED_FLAG = 'Y'
2687 AND EAM.PRIMARY_FAILURE_METER = 'Y'
2688 AND EMR.METER_READING_ID IN
2689 (SELECT METER_READING_ID FROM
2690 (SELECT Max(EMR1.METER_READING_ID) METER_READING_ID,EMR1.WIP_ENTITY_ID,EMR1.METER_ID
2691 FROM EAM_METER_READINGS EMR1
2692 GROUP BY EMR1.WIP_ENTITY_ID, EMR1.METER_ID ))) METER
2693 WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
2694 AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
2695 AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
2696 AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
2697 AND WDJ.STATUS_TYPE IN (4,5,12)
2698 AND EAF.SOURCE_TYPE = 1
2699 AND EAF.OBJECT_TYPE = 3
2700 AND EAF.OBJECT_ID = CII.INSTANCE_ID
2701 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
2702 --AND MSN.GEN_OBJECT_ID = WDJ.MAINTENANCE_OBJECT_ID
2703 AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
2704 AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
2705 AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
2706 AND MEL.LOCATION_ID (+) = EAF.AREA_ID
2707 AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
2708 AND EAF.OBJECT_ID = METER.MAINTENANCE_OBJECT_ID (+)
2709 AND METER.WIP_ENTITY_ID (+) = EAF.SOURCE_ID
2710 AND EAF.OBJECT_ID IN ( SELECT EAF.OBJECT_ID OBJECT_ID --This part will select all parent assets
2711 FROM EAM_ASSET_FAILURE_CODES EAFC,
2712 EAM_ASSET_FAILURES EAF,
2713 WIP_DISCRETE_JOBS WDJ,
2714 CSI_ITEM_INSTANCES CII
2715 WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
2716 AND WDJ.STATUS_TYPE IN (4,5,12)
2717 AND EAF.SOURCE_TYPE = 1
2718 AND EAF.OBJECT_TYPE = 3
2719 AND EAF.OBJECT_ID = CII.INSTANCE_ID
2720 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
2721 AND (l_gen_object_id IS NULL OR EAF.OBJECT_ID = l_gen_object_id)
2722 AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
2723 AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
2724 AND (p_failure_code IS NULL OR EAFC.FAILURE_CODE = p_failure_code)
2725 )
2726 )
2727 WHERE (p_to_date IS NULL OR FAILURE_DATE <= p_to_date)
2728 AND (p_from_date IS NULL OR FAILURE_DATE >= p_from_date) );
2729
2730 /* [Parent + Child Failure WOs][When NONE of failure code/from date/to date have been entered as search criteria] - View By Asset Group */
2731
2732 CURSOR c_meter_recs_ag_simple IS
2733 SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
2734 EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
2735 CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
2736 CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
2737 MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
2738 NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
2739 WDJ.WIP_ENTITY_ID,
2740 WE.WIP_ENTITY_NAME,
2741 WDJ.ORGANIZATION_ID,
2742 OOD.ORGANIZATION_CODE,
2743 MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
2744 CII.CATEGORY_ID ASSET_CATEGORY_ID,
2745 MEL.LOCATION_CODES ASSET_LOCATION,
2746 BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
2747 EAFC.FAILURE_CODE,
2748 EAFC.CAUSE_CODE,
2749 EAFC.RESOLUTION_CODE,
2750 EAF.FAILURE_DATE,
2751 EAFC.COMMENTS,
2752 DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY CII.INVENTORY_ITEM_ID ORDER BY EAF.FAILURE_DATE ),
2753 --NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
2754 NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
2755 FROM CSI_ITEM_INSTANCES CII1
2756 WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
2757 AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
2758 (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
2759 ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
2760 (WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
2761 METER.METER_ID,
2762 METER.METER_NAME METER_NAME,
2763 METER.METER_UOM METER_UOM,
2764 DECODE(METER.METER_TYPE,2,METER.CURRENT_READING,1,
2765 DECODE( LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ),
2766 NULL, METER.CURRENT_READING,
2767 (METER.CURRENT_READING -
2768 (LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ))) )) READING_BETWEEN_FAILURES,
2769 'Y' INCLUDE_FOR_READING_AGGR,
2770 'Y' INCLUDE_FOR_COST_AGGR
2771 FROM WIP_DISCRETE_JOBS WDJ,
2772 WIP_ENTITIES WE,
2773 CSI_ITEM_INSTANCES CII,
2774 MTL_CATEGORIES_KFV MCKFV,
2775 MTL_SYSTEM_ITEMS_KFV MSIKFV,
2776 MTL_EAM_LOCATIONS MEL,
2777 BOM_DEPARTMENTS BD,
2778 EAM_ASSET_FAILURE_CODES EAFC,
2779 EAM_ASSET_FAILURES EAF,
2780 ORG_ORGANIZATION_DEFINITIONS OOD,
2781 (SELECT
2782 ccb.counter_id METER_ID,
2783 cctl.name METER_NAME,
2784 ccb.uom_code METER_UOM,
2785 CCA.SOURCE_OBJECT_ID MAINTENANCE_OBJECT_ID,
2786 CCR.COUNTER_READING CURRENT_READING,
2787 CCR.VALUE_TIMESTAMP CURRENT_READING_DATE,
2788 CCA.PRIMARY_FAILURE_FLAG,
2789 decode(ct.transaction_type_id,92,ct.source_header_ref_id,to_number(null)) WIP_ENTITY_ID,
2790 CCB.reading_type METER_TYPE
2791 FROM csi_counters_b CCB,csi_counters_tl cctl, csi_counter_readings CCR, csi_counter_associations CCA, csi_transactions CT
2792 WHERE
2793 ccb.counter_id = cctl.counter_id
2794 and SYSDATE BETWEEN nvl(ccb.start_date_active, SYSDATE-1) AND nvl(ccb.end_date_active, SYSDATE+1)
2795 and cctl.language = userenv('LANG') and ccb.counter_type = 'REGULAR'
2796 AND CCB.COUNTER_ID = CCA.COUNTER_ID
2797 AND CCR.COUNTER_ID(+) = CCB.COUNTER_ID
2798 AND CCR.transaction_id = CT.transaction_id(+)
2799 and SYSDATE BETWEEN nvl(cca.start_date_active, SYSDATE-1) AND nvl(cca.end_date_active, SYSDATE+1)
2800 AND CCA.PRIMARY_FAILURE_FLAG = 'Y'
2801 AND CCB.EAM_REQUIRED_FLAG = 'Y'
2802 AND CCR.COUNTER_VALUE_ID IN
2803 (
2804 SELECT
2805 METER_READING_ID
2806 FROM
2807 (
2808 SELECT
2809 Max(EMR1.METER_READING_ID) METER_READING_ID
2810 FROM EAM_METER_READINGS_V EMR1
2811 GROUP BY EMR1.WIP_ENTITY_ID,
2812 EMR1.METER_ID
2813 )
2814 )) METER
2815 WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
2816 AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
2817 AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
2818 AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
2819 AND WDJ.STATUS_TYPE IN (4,5,12)
2820 AND EAF.SOURCE_TYPE = 1
2821 AND EAF.OBJECT_TYPE = 3
2822 AND EAF.OBJECT_ID = CII.INSTANCE_ID
2823 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
2824 AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
2825 AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
2826 AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
2827 AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
2828 AND MEL.LOCATION_ID (+) = EAF.AREA_ID
2829 AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
2830 AND EAF.OBJECT_ID = METER.MAINTENANCE_OBJECT_ID (+)
2831 AND METER.WIP_ENTITY_ID (+) = EAF.SOURCE_ID
2832 AND EAF.OBJECT_ID IN (SELECT CII.INSTANCE_ID
2833 FROM CSI_ITEM_INSTANCES CII,
2834 MTL_SERIAL_NUMBERS MSN
2835 WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
2836 AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
2837 AND MSN.gen_object_id IN
2838 (SELECT OBJECT_ID --This part will select all children assets
2839 FROM MTL_OBJECT_GENEALOGY
2840 START WITH PARENT_OBJECT_ID IN
2841 ( SELECT MSN.GEN_OBJECT_ID PARENT_OBJECT_ID
2842 FROM MTL_SERIAL_NUMBERS MSN,
2843 CSI_ITEM_INSTANCES CII
2844 WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
2845 AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
2846 AND (l_gen_object_id IS NULL OR CII.INSTANCE_ID = l_gen_object_id)
2847 AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
2848 AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
2849 )
2850 CONNECT BY NOCYCLE PRIOR OBJECT_ID = PARENT_OBJECT_ID
2851 )
2852 UNION
2853 SELECT CII.INSTANCE_ID OBJECT_ID --This part will select all parent assets
2854 FROM CSI_ITEM_INSTANCES CII
2855 WHERE (l_gen_object_id IS NULL OR CII.INSTANCE_ID = l_gen_object_id)
2856 AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
2857 AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
2858 ) ;
2859
2860 /* [Parent + Child Failure WOs][When ANY of failure code/from date/to date have been entered as search criteria] - View By Asset Group */
2861 CURSOR c_meter_recs_wo_ag_simple IS
2862 SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
2863 EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
2864 NVL(WDJ.ASSET_NUMBER, WDJ.REBUILD_SERIAL_NUMBER) MAINTAINED_NUMBER,
2865 CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
2866 MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
2867 NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
2868 WDJ.WIP_ENTITY_ID,
2869 WE.WIP_ENTITY_NAME,
2870 WDJ.ORGANIZATION_ID,
2871 OOD.ORGANIZATION_CODE,
2872 MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
2873 CII.CATEGORY_ID ASSET_CATEGORY_ID,
2874 MEL.LOCATION_CODES ASSET_LOCATION,
2875 BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
2876 EAFC.FAILURE_CODE,
2877 EAFC.CAUSE_CODE,
2878 EAFC.RESOLUTION_CODE,
2879 EAF.FAILURE_DATE,
2880 EAFC.COMMENTS,
2881 DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY CII.INVENTORY_ITEM_ID ORDER BY EAF.FAILURE_DATE ),
2882 --NULL, (EAF.FAILURE_DATE - MSN.CREATION_DATE),
2883 NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
2884 FROM CSI_ITEM_INSTANCES CII1
2885 WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
2886 AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
2887 (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
2888 ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
2889 (WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
2890 METER.METER_ID,
2891 METER.METER_NAME METER_NAME,
2892 METER.METER_UOM METER_UOM,
2893 DECODE(METER.METER_TYPE,2,METER.CURRENT_READING,1,
2894 DECODE( LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ),
2895 NULL, METER.CURRENT_READING,
2896 (METER.CURRENT_READING -
2897 (LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ))) )) READING_BETWEEN_FAILURES,
2898 'Y' INCLUDE_FOR_READING_AGGR,
2899 'Y' INCLUDE_FOR_COST_AGGR
2900 FROM WIP_DISCRETE_JOBS WDJ,
2901 WIP_ENTITIES WE,
2902 MTL_CATEGORIES_KFV MCKFV,
2903 MTL_SYSTEM_ITEMS_KFV MSIKFV,
2904 MTL_EAM_LOCATIONS MEL,
2905 BOM_DEPARTMENTS BD,
2906 EAM_ASSET_FAILURE_CODES EAFC,
2907 EAM_ASSET_FAILURES EAF,
2908 CSI_ITEM_INSTANCES CII,
2909 ORG_ORGANIZATION_DEFINITIONS OOD,
2910 (SELECT EM.METER_ID,
2911 EM.METER_NAME,
2912 EM.METER_UOM,
2913 EAM.MAINTENANCE_OBJECT_ID,
2914 EMR.CURRENT_READING,
2915 EMR.CURRENT_READING_DATE,
2916 EAM.PRIMARY_FAILURE_METER,
2917 EMR.WIP_ENTITY_ID,
2918 EM.METER_TYPE
2919 FROM EAM_METERS EM,
2920 EAM_ASSET_METERS EAM,
2921 EAM_METER_READINGS EMR
2922 WHERE EM.METER_ID = EAM.METER_ID
2923 AND EMR.METER_ID = EAM.METER_ID
2924 AND EM.REQUIRED_FLAG = 'Y'
2925 AND EAM.PRIMARY_FAILURE_METER = 'Y'
2926 AND EMR.METER_READING_ID IN
2927 (SELECT METER_READING_ID FROM
2928 (SELECT Max(EMR1.METER_READING_ID) METER_READING_ID,EMR1.WIP_ENTITY_ID,EMR1.METER_ID
2929 FROM EAM_METER_READINGS EMR1
2930 GROUP BY EMR1.WIP_ENTITY_ID, EMR1.METER_ID ))) METER
2931 WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
2932 AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
2933 AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
2934 AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
2935 AND WDJ.STATUS_TYPE IN (4,5,12)
2936 AND EAF.SOURCE_TYPE = 1
2937 AND EAF.OBJECT_TYPE = 3
2938 AND EAF.OBJECT_ID = CII.INSTANCE_ID
2939 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
2940 AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
2941 AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
2942 AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
2943 AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
2944 AND MEL.LOCATION_ID (+) = EAF.AREA_ID
2945 AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
2946 AND EAF.OBJECT_ID = METER.MAINTENANCE_OBJECT_ID (+)
2947 AND METER.WIP_ENTITY_ID (+) = EAF.SOURCE_ID
2948 AND (p_to_date IS NULL OR FAILURE_DATE <= p_to_date)
2949 AND (p_from_date IS NULL OR FAILURE_DATE >= p_from_date)
2950 AND EAF.OBJECT_ID IN (SELECT CII.INSTANCE_ID
2951 FROM CSI_ITEM_INSTANCES CII,
2952 MTL_SERIAL_NUMBERS MSN
2953 WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
2954 AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
2955 AND MSN.GEN_OBJECT_ID IN
2956 (SELECT OBJECT_ID --This part will select all children assets
2957 FROM MTL_OBJECT_GENEALOGY
2958 START WITH PARENT_OBJECT_ID IN
2959 ( SELECT MSN.GEN_OBJECT_ID PARENT_OBJECT_ID
2960 FROM CSI_ITEM_INSTANCES CII,
2961 EAM_ASSET_FAILURE_CODES EAFC,
2962 EAM_ASSET_FAILURES EAF,
2963 WIP_DISCRETE_JOBS WDJ,
2964 MTL_SERIAL_NUMBERS MSN
2965 WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
2966 AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
2967 AND WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
2968 AND WDJ.STATUS_TYPE IN (4,5,12)
2969 AND EAF.SOURCE_TYPE = 1
2970 AND EAF.OBJECT_TYPE = 3
2971 AND EAF.OBJECT_ID = CII.INSTANCE_ID
2972 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
2973 AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
2974 AND (l_gen_object_id IS NULL OR EAF.OBJECT_ID = l_gen_object_id)
2975 AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
2976 AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
2977 AND (p_failure_code IS NULL OR EAFC.FAILURE_CODE = p_failure_code)
2978 AND (p_to_date IS NULL OR FAILURE_DATE <= p_to_date)
2979 AND (p_from_date IS NULL OR FAILURE_DATE >= p_from_date)
2980 )
2981 CONNECT BY NOCYCLE PRIOR OBJECT_ID = PARENT_OBJECT_ID)
2982 UNION
2983 SELECT MAINTENANCE_OBJECT_ID FROM (
2984 SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
2985 EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
2986 NVL(WDJ.ASSET_NUMBER, WDJ.REBUILD_SERIAL_NUMBER) MAINTAINED_NUMBER,
2987 CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
2988 MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
2989 NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
2990 WDJ.WIP_ENTITY_ID,
2991 WE.WIP_ENTITY_NAME,
2992 WDJ.ORGANIZATION_ID,
2993 OOD.ORGANIZATION_CODE,
2994 MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
2995 CII.CATEGORY_ID ASSET_CATEGORY_ID,
2996 MEL.LOCATION_CODES ASSET_LOCATION,
2997 BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
2998 EAFC.FAILURE_CODE,
2999 EAFC.CAUSE_CODE,
3000 EAFC.RESOLUTION_CODE,
3001 EAF.FAILURE_DATE,
3002 EAFC.COMMENTS,
3003 DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY CII.INVENTORY_ITEM_ID ORDER BY EAF.FAILURE_DATE ),
3004 --NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
3005 NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
3006 FROM CSI_ITEM_INSTANCES CII1
3007 WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
3008 AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
3009 (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
3010 ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
3011 (WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
3012 METER.METER_ID,
3013 METER.METER_NAME METER_NAME,
3014 METER.METER_UOM METER_UOM,
3015 DECODE(METER.METER_TYPE,2,METER.CURRENT_READING,1,
3016 DECODE( LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ),
3017 NULL, METER.CURRENT_READING,
3018 (METER.CURRENT_READING -
3019 (LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ))) )) READING_BETWEEN_FAILURES,
3020 'Y' INCLUDE_FOR_READING_AGGR,
3021 'Y' INCLUDE_FOR_COST_AGGR
3022 FROM WIP_DISCRETE_JOBS WDJ,
3023 WIP_ENTITIES WE,
3024 MTL_CATEGORIES_KFV MCKFV,
3025 MTL_SYSTEM_ITEMS_KFV MSIKFV,
3026 MTL_EAM_LOCATIONS MEL,
3027 BOM_DEPARTMENTS BD,
3028 EAM_ASSET_FAILURE_CODES EAFC,
3029 EAM_ASSET_FAILURES EAF,
3030 CSI_ITEM_INSTANCES CII,
3031 ORG_ORGANIZATION_DEFINITIONS OOD,
3032 (SELECT EM.METER_ID,
3033 EM.METER_NAME,
3034 EM.METER_UOM,
3035 EAM.MAINTENANCE_OBJECT_ID,
3036 EMR.CURRENT_READING,
3037 EMR.CURRENT_READING_DATE,
3038 EAM.PRIMARY_FAILURE_METER,
3039 EMR.WIP_ENTITY_ID,
3040 EM.METER_TYPE
3041 FROM EAM_METERS EM,
3042 EAM_ASSET_METERS EAM,
3043 EAM_METER_READINGS EMR
3044 WHERE EM.METER_ID = EAM.METER_ID
3045 AND EMR.METER_ID = EAM.METER_ID
3046 AND EM.REQUIRED_FLAG = 'Y'
3047 AND EAM.PRIMARY_FAILURE_METER = 'Y'
3048 AND EMR.METER_READING_ID IN
3049 (SELECT METER_READING_ID FROM
3050 (SELECT Max(EMR1.METER_READING_ID) METER_READING_ID,EMR1.WIP_ENTITY_ID,EMR1.METER_ID
3051 FROM EAM_METER_READINGS EMR1
3052 GROUP BY EMR1.WIP_ENTITY_ID, EMR1.METER_ID ))) METER
3053 WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
3054 AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
3055 AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
3056 AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
3057 AND WDJ.STATUS_TYPE IN (4,5,12)
3058 AND EAF.SOURCE_TYPE = 1
3059 AND EAF.OBJECT_TYPE = 3
3060 AND EAF.OBJECT_ID = CII.INSTANCE_ID
3061 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
3062 AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
3063 AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
3064 AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
3065 AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
3066 AND MEL.LOCATION_ID (+) = EAF.AREA_ID
3067 AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
3068 AND EAF.OBJECT_ID = METER.MAINTENANCE_OBJECT_ID (+)
3069 AND METER.WIP_ENTITY_ID (+) = EAF.SOURCE_ID
3070 AND EAF.OBJECT_ID IN ( SELECT EAF.OBJECT_ID OBJECT_ID --This part will select all parent assets
3071 FROM EAM_ASSET_FAILURE_CODES EAFC,
3072 EAM_ASSET_FAILURES EAF,
3073 WIP_DISCRETE_JOBS WDJ,
3074 CSI_ITEM_INSTANCES CII
3075 WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
3076 AND WDJ.STATUS_TYPE IN (4,5,12)
3077 AND EAF.SOURCE_TYPE = 1
3078 AND EAF.OBJECT_TYPE = 3
3079 AND EAF.OBJECT_ID = CII.INSTANCE_ID
3080 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
3081 AND (l_gen_object_id IS NULL OR EAF.OBJECT_ID = l_gen_object_id)
3082 AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
3083 AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
3084 AND (p_failure_code IS NULL OR EAFC.FAILURE_CODE = p_failure_code)
3085 )
3086 )
3087 WHERE (p_to_date IS NULL OR FAILURE_DATE <= p_to_date)
3088 AND (p_from_date IS NULL OR FAILURE_DATE >= p_from_date) );
3089
3090
3091
3092 BEGIN
3093 g_module_name := 'GET_CHILD_RECORDS_FA_SIMPLE';
3094 /* Should not rollback, since the data of the parent asset numbers has to be retained */
3095 --ROLLBACK;
3096
3097 IF p_gen_object_id = 0 THEN
3098 l_gen_object_id := NULL;
3099 ELSE
3100 l_gen_object_id := p_gen_object_id;
3101 END IF;
3102
3103 IF p_maint_group_id = 0 THEN
3104 l_maint_group_id := NULL;
3105 ELSE
3106 l_maint_group_id := p_maint_group_id;
3107 END IF;
3108
3109 IF p_category_id = 0 THEN
3110 l_category_id := NULL;
3111 ELSE
3112 l_category_id := p_category_id;
3113 END IF;
3114
3115 IF p_view_by IN (1,3,4) THEN
3116 l_validate_meters := 'N';
3117 ELSIF p_view_by = 2 THEN
3118 l_validate_meters := 'Y';
3119 END IF;
3120
3121 l_validate_currency := p_compute_repair_costs;
3122 l_group_id := x_group_id;
3123 l_asset_failure_tbl.DELETE;
3124 IF ( p_failure_code IS NULL AND p_to_date IS NULL AND p_from_date IS NULL) then
3125 IF p_view_by = 1 /* 'ASSET_NUMBER' */ THEN
3126 OPEN c_meter_recs_an_simple;
3127 LOOP
3128 FETCH c_meter_recs_an_simple BULK COLLECT INTO l_asset_failure_tbl LIMIT 500;
3129 IF ( l_asset_failure_tbl.Count > 0 ) THEN
3130 VALIDATE_RECORDS( p_asset_failure_tbl => l_asset_failure_tbl,
3131 p_validate_meters => l_validate_meters,
3132 p_validate_currency => l_validate_currency,
3133 p_current_org_id => p_current_org_id,
3134 x_unmatched_uom_class => l_unmatched_uom_class,
3135 x_unmatched_currency => l_unmatched_currency);
3136
3137 IF ( l_group_id IS NULL OR l_group_id = 0) then
3138 SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_group_id FROM DUAL;
3139 END IF;
3140
3141 INSERT_INTO_TEMP_TABLE(p_group_id => l_group_id,
3142 p_asset_failure_tbl => l_asset_failure_tbl);
3143
3144 IF p_compute_repair_costs = 'Y' THEN
3145 COMPUTE_REPAIR_COSTS(l_group_id);
3146 END if;
3147 END IF;
3148
3149 EXIT WHEN c_meter_recs_an_simple%NOTFOUND;
3150 END LOOP;
3151 CLOSE c_meter_recs_an_simple;
3152
3153 ELSIF p_view_by = 2 /* 'ASSET_GROUP' */ THEN
3154 OPEN c_meter_recs_ag_simple;
3155 LOOP
3156 FETCH c_meter_recs_ag_simple BULK COLLECT INTO l_asset_failure_tbl LIMIT 500;
3157 IF ( l_asset_failure_tbl.Count > 0 ) THEN
3158 VALIDATE_RECORDS( p_asset_failure_tbl => l_asset_failure_tbl,
3159 p_validate_meters => l_validate_meters,
3160 p_validate_currency => l_validate_currency,
3161 p_current_org_id => p_current_org_id,
3162 x_unmatched_uom_class => l_unmatched_uom_class,
3163 x_unmatched_currency => l_unmatched_currency);
3164
3165 IF ( l_group_id IS NULL OR l_group_id = 0) then
3166 SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_group_id FROM DUAL;
3167 END IF;
3168
3169 INSERT_INTO_TEMP_TABLE(p_group_id => l_group_id,
3170 p_asset_failure_tbl => l_asset_failure_tbl);
3171
3172 IF p_compute_repair_costs = 'Y' THEN
3173 COMPUTE_REPAIR_COSTS(l_group_id);
3174 END if;
3175 END IF;
3176
3177 EXIT WHEN c_meter_recs_ag_simple%NOTFOUND;
3178 END LOOP;
3179
3180 CLOSE c_meter_recs_ag_simple;
3181
3182 ELSIF p_view_by = 3 /* Asset Category */ THEN
3183 OPEN c_recs_ac_simple;
3184 LOOP
3185 FETCH c_recs_ac_simple BULK COLLECT INTO l_asset_failure_tbl LIMIT 500;
3186 IF ( l_asset_failure_tbl.Count > 0 ) THEN
3187 VALIDATE_RECORDS( p_asset_failure_tbl => l_asset_failure_tbl,
3188 p_validate_meters => 'N',
3189 p_validate_currency => l_validate_currency,
3190 p_current_org_id => p_current_org_id,
3191 x_unmatched_uom_class => l_unmatched_uom_class,
3192 x_unmatched_currency => l_unmatched_currency);
3193 IF ( l_group_id IS NULL OR l_group_id = 0) then
3194 SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_group_id FROM DUAL;
3195 END IF;
3196
3197 INSERT_INTO_TEMP_TABLE(p_group_id => l_group_id,
3198 p_asset_failure_tbl => l_asset_failure_tbl);
3199
3200 IF p_compute_repair_costs = 'Y' THEN
3201 COMPUTE_REPAIR_COSTS(l_group_id);
3202 END if;
3203 END IF;
3204
3205 EXIT WHEN c_recs_ac_simple%NOTFOUND;
3206 END LOOP;
3207 CLOSE c_recs_ac_simple;
3208
3209 ELSIF p_view_by = 4 /* Failure Code */ THEN
3210 OPEN c_recs_fc_simple;
3211 LOOP
3212 FETCH c_recs_fc_simple BULK COLLECT INTO l_asset_failure_tbl LIMIT 500;
3213 IF ( l_asset_failure_tbl.Count > 0 ) THEN
3214 VALIDATE_RECORDS( p_asset_failure_tbl => l_asset_failure_tbl,
3215 p_validate_meters => 'N',
3216 p_validate_currency => l_validate_currency,
3217 p_current_org_id => p_current_org_id,
3218 x_unmatched_uom_class => l_unmatched_uom_class,
3219 x_unmatched_currency => l_unmatched_currency);
3220 IF ( l_group_id IS NULL OR l_group_id = 0) then
3221 SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_group_id FROM DUAL;
3222 END IF;
3223
3224 INSERT_INTO_TEMP_TABLE(p_group_id => l_group_id,
3225 p_asset_failure_tbl => l_asset_failure_tbl);
3226
3227 IF p_compute_repair_costs = 'Y' THEN
3228 COMPUTE_REPAIR_COSTS(l_group_id);
3229 END if;
3230 END IF;
3231
3232 EXIT WHEN c_recs_fc_simple%NOTFOUND;
3233 END LOOP;
3234
3235 CLOSE c_recs_fc_simple;
3236 END IF; --p_view_by
3237 ---
3238 ELSE /*( p_failure_code IS NOT NULL OR p_to_date IS NOT NULL OR p_from_date IS NOT NULL)*/
3239 IF p_view_by = 1 /* 'ASSET_NUMBER' */ THEN
3240 OPEN c_meter_recs_wo_an_simple;
3241 LOOP
3242 FETCH c_meter_recs_wo_an_simple BULK COLLECT INTO l_asset_failure_tbl LIMIT 500;
3243 IF ( l_asset_failure_tbl.Count > 0 ) THEN
3244 VALIDATE_RECORDS( p_asset_failure_tbl => l_asset_failure_tbl,
3245 p_validate_meters => l_validate_meters,
3246 p_validate_currency => l_validate_currency,
3247 p_current_org_id => p_current_org_id,
3248 x_unmatched_uom_class => l_unmatched_uom_class,
3249 x_unmatched_currency => l_unmatched_currency);
3250
3251 IF ( l_group_id IS NULL OR l_group_id = 0) then
3252 SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_group_id FROM DUAL;
3253 END IF;
3254
3255 INSERT_INTO_TEMP_TABLE(p_group_id => l_group_id,
3256 p_asset_failure_tbl => l_asset_failure_tbl);
3257
3258 IF p_compute_repair_costs = 'Y' THEN
3259 COMPUTE_REPAIR_COSTS(l_group_id);
3260 END if;
3261 END IF;
3262
3263 EXIT WHEN c_meter_recs_wo_an_simple%NOTFOUND;
3264 END LOOP;
3265 CLOSE c_meter_recs_wo_an_simple;
3266
3267 ELSIF p_view_by = 2 /* 'ASSET_GROUP' */ THEN
3268 OPEN c_meter_recs_ag_simple;
3269 LOOP
3270 FETCH c_meter_recs_ag_simple BULK COLLECT INTO l_asset_failure_tbl LIMIT 500;
3271 IF ( l_asset_failure_tbl.Count > 0 ) THEN
3272 VALIDATE_RECORDS( p_asset_failure_tbl => l_asset_failure_tbl,
3273 p_validate_meters => l_validate_meters,
3274 p_validate_currency => l_validate_currency,
3275 p_current_org_id => p_current_org_id,
3276 x_unmatched_uom_class => l_unmatched_uom_class,
3277 x_unmatched_currency => l_unmatched_currency);
3278
3279 IF ( l_group_id IS NULL OR l_group_id = 0) then
3280 SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_group_id FROM DUAL;
3281 END IF;
3282
3283 INSERT_INTO_TEMP_TABLE(p_group_id => l_group_id,
3284 p_asset_failure_tbl => l_asset_failure_tbl);
3285
3286 IF p_compute_repair_costs = 'Y' THEN
3287 COMPUTE_REPAIR_COSTS(l_group_id);
3288 END if;
3289 END IF;
3290
3291 EXIT WHEN c_meter_recs_ag_simple%NOTFOUND;
3292 END LOOP;
3293
3294 CLOSE c_meter_recs_ag_simple;
3295
3296 ELSIF p_view_by = 3 /* Asset Category */ THEN
3297 OPEN c_recs_ac_simple;
3298 LOOP
3299 FETCH c_recs_ac_simple BULK COLLECT INTO l_asset_failure_tbl LIMIT 500;
3300 IF ( l_asset_failure_tbl.Count > 0 ) THEN
3301 VALIDATE_RECORDS( p_asset_failure_tbl => l_asset_failure_tbl,
3302 p_validate_meters => 'N',
3303 p_validate_currency => l_validate_currency,
3304 p_current_org_id => p_current_org_id,
3305 x_unmatched_uom_class => l_unmatched_uom_class,
3306 x_unmatched_currency => l_unmatched_currency);
3307 IF ( l_group_id IS NULL OR l_group_id = 0) then
3308 SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_group_id FROM DUAL;
3309 END IF;
3310
3311 INSERT_INTO_TEMP_TABLE(p_group_id => l_group_id,
3312 p_asset_failure_tbl => l_asset_failure_tbl);
3313
3314 IF p_compute_repair_costs = 'Y' THEN
3315 COMPUTE_REPAIR_COSTS(l_group_id);
3316 END if;
3317 END IF;
3318
3319 EXIT WHEN c_recs_ac_simple%NOTFOUND;
3320 END LOOP;
3321 CLOSE c_recs_ac_simple;
3322
3323 ELSIF p_view_by = 4 /* Failure Code */ THEN
3324 OPEN c_recs_fc_simple;
3325 LOOP
3326 FETCH c_recs_fc_simple BULK COLLECT INTO l_asset_failure_tbl LIMIT 500;
3327 IF ( l_asset_failure_tbl.Count > 0 ) THEN
3328 VALIDATE_RECORDS( p_asset_failure_tbl => l_asset_failure_tbl,
3329 p_validate_meters => 'N',
3330 p_validate_currency => l_validate_currency,
3331 p_current_org_id => p_current_org_id,
3332 x_unmatched_uom_class => l_unmatched_uom_class,
3333 x_unmatched_currency => l_unmatched_currency);
3334 IF ( l_group_id IS NULL OR l_group_id = 0) then
3335 SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_group_id FROM DUAL;
3336 END IF;
3337
3338 INSERT_INTO_TEMP_TABLE(p_group_id => l_group_id,
3339 p_asset_failure_tbl => l_asset_failure_tbl);
3340
3341 IF p_compute_repair_costs = 'Y' THEN
3342 COMPUTE_REPAIR_COSTS(l_group_id);
3343 END if;
3344 END IF;
3345
3346 EXIT WHEN c_recs_fc_simple%NOTFOUND;
3347 END LOOP;
3348
3349 CLOSE c_recs_fc_simple;
3350 END IF; --p_view_by
3351
3352 END IF; /*( p_failure_code IS NULL AND p_to_date IS NULL AND p_from_date IS NULL)*/
3353
3354
3355
3356 x_return_status := 'S';
3357
3358 x_msg_data := l_msg_data;
3359 x_group_id := l_group_id;
3360
3361 x_unmatched_uom_class := l_unmatched_uom_class;
3362 x_unmatched_currency := l_unmatched_currency;
3363 EXCEPTION
3364 WHEN OTHERS THEN
3365 x_return_status := 'E';
3366 x_msg_data := 'Error in '||g_module_name||':'||SQLERRM;
3367 END GET_CHILD_RECORDS_FA_SIMPLE;
3368
3369 Procedure GET_CHILD_RECORDS_FA_ADV
3370 ( P_WHERE_CLAUSE IN VARCHAR2,
3371 p_where_clause_1 IN VARCHAR2,
3372 P_FROM_DATE_CLAUSE IN VARCHAR2,
3373 P_VIEW_BY IN VARCHAR2,
3374 P_COMPUTE_REPAIR_COSTS IN VARCHAR2,
3375 P_CURRENT_ORG_ID IN NUMBER,
3376 x_group_id IN OUT NOCOPY NUMBER,
3377 x_return_status OUT NOCOPY VARCHAR2,
3378 x_msg_data OUT NOCOPY VARCHAR2,
3379 x_unmatched_uom_class OUT NOCOPY VARCHAR2,
3380 x_unmatched_currency OUT NOCOPY VARCHAR2) IS
3381
3382 c_ref_failures SYS_REFCURSOR;
3383 l_asset_failure_tbl eam_asset_failure_tbl_type;
3384 l_group_id NUMBER;
3385 l_current_org_id NUMBER;
3386 l_org2 NUMBER;
3387 l_same_currency NUMBER;
3388 l_meter_uom1 VARCHAR2(3);
3389 l_meter_uom2 VARCHAR2(3);
3390 l_uom1_conv_rate NUMBER;
3391 l_uom2_conv_rate NUMBER;
3392 l_validate_meters VARCHAR2(1);
3393 l_validate_currency VARCHAR2(1);
3394 x_ref_failures SYS_REFCURSOR;
3395 l_unmatched_uom_class VARCHAR2(1) := 'N';
3396 l_unmatched_currency VARCHAR2(1) := 'N';
3397
3398
3399 BEGIN
3400
3401 g_module_name := 'GET_CHILD_RECORDS_FA_ADV';
3402
3403
3404 /* Should not rollback, since the data of the parent asset numbers has to be retained */
3405 ROLLBACK;
3406
3407
3408 l_current_org_id := p_current_org_id;
3409
3410 If p_View_By = 2 /* 'ASSET_GROUP' */ OR p_View_By = 1 /* 'ASSET_NUMBER' */ THEN
3411 GET_CHILD_METER_RECS_CURSOR(p_where_clause,p_where_clause_1, p_from_date_clause, p_view_by, p_current_org_id, x_ref_failures);
3412 Else
3413 GET_CHILD_RECS_CURSOR(p_where_clause, p_where_clause_1,p_from_date_clause, p_view_by, p_current_org_id,x_ref_failures);
3414 End if;
3415
3416 IF p_view_by IN (1,3,4) THEN
3417 l_validate_meters := 'N';
3418 ELSIF p_view_by = 2 THEN
3419 l_validate_meters := 'Y';
3420 END IF;
3421
3422 l_group_id := x_group_id;
3423 l_validate_currency := p_compute_repair_costs;
3424
3425
3426 LOOP
3427 FETCH x_ref_failures BULK COLLECT INTO l_asset_failure_tbl LIMIT 500;
3428 IF ( l_asset_failure_tbl.Count > 0 ) THEN
3429
3430
3431 VALIDATE_RECORDS( p_asset_failure_tbl => l_asset_failure_tbl,
3432 p_validate_meters => l_validate_meters,
3433 p_validate_currency => l_validate_currency,
3434 p_current_org_id => l_current_org_id,
3435 x_unmatched_uom_class => l_unmatched_uom_class,
3436 x_unmatched_currency => l_unmatched_currency);
3437
3438 IF ( l_group_id IS NULL OR l_group_id = 0) then
3439 SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_group_id FROM DUAL;
3440 END IF;
3441
3442 INSERT_INTO_TEMP_TABLE(p_group_id => l_group_id,
3443 p_asset_failure_tbl => l_asset_failure_tbl);
3444
3445 IF p_compute_repair_costs = 'Y' THEN
3446 COMPUTE_REPAIR_COSTS(l_group_id);
3447 END if;
3448 END IF;
3449
3450 EXIT WHEN x_ref_failures%NOTFOUND;
3451 END LOOP;
3452
3453 CLOSE x_ref_failures;
3454
3455 x_group_id := l_group_id;
3456 x_return_status := 'S';
3457 x_unmatched_uom_class := l_unmatched_uom_class;
3458 x_unmatched_currency := l_unmatched_currency;
3459
3460 EXCEPTION
3461 WHEN OTHERS THEN
3462 x_return_status := 'E';
3463 x_msg_data := 'Error in '||g_module_name||':'||SQLERRM;
3464 END GET_CHILD_RECORDS_FA_ADV;
3465
3466 PROCEDURE GET_CHILD_RECS_CURSOR
3467 ( p_where_clause IN VARCHAR2,
3468 p_where_clause_1 IN VARCHAR2,
3469 p_from_date_clause IN VARCHAR2,
3470 p_view_by IN VARCHAR2,
3471 p_org_id IN VARCHAR2,
3472 x_ref_failures OUT NOCOPY SYS_REFCURSOR) IS
3473
3474 l_sql_stmt1 VARCHAR2(8000);
3475 l_from_date_clause VARCHAR2(8000);
3476 l_where_clause VARCHAR2(8000);
3477 l_where_clause_1 VARCHAR2(8000);
3478 l_org_id VARCHAR2(50);
3479 l_partition_by VARCHAR2(50);
3480 l_first_tbf_calc_clause VARCHAR2(500);
3481 l_sql_stmt2 VARCHAR2(8000);
3482 l_sql_stmt3 VARCHAR2(8000);
3483 l_sql_stmt4 VARCHAR2(8000);
3484 l_sql_stmt5 VARCHAR2(8000);
3485
3486
3487
3488 BEGIN
3489
3490 g_module_name := 'GET_CHILD_RECS_CURSOR';
3491
3492 IF (p_where_clause <> 'NULL' AND p_where_clause IS NOT NULL) THEN
3493 l_where_clause := p_where_clause;
3494 ELSE
3495 l_where_clause := NULL;
3496 END IF;
3497
3498 IF (p_where_clause_1 <> 'NULL' AND p_where_clause_1 IS NOT NULL) THEN
3499 l_where_clause_1 := p_where_clause_1;
3500 ELSE
3501 l_where_clause_1 := NULL;
3502 END IF;
3503
3504 IF (p_from_date_clause <> 'NULL' AND p_from_date_clause IS NOT NULL) THEN
3505 l_from_date_clause := p_from_date_clause;
3506 ELSE
3507 l_from_date_clause := NULL;
3508 END IF;
3509
3510 IF p_view_by = 3 THEN
3511 l_partition_by := 'CII.CATEGORY_ID';
3512 ELSIF p_view_by = 4 THEN
3513 l_partition_by := 'EAFC.FAILURE_CODE';
3514 END IF;
3515 IF (p_org_id <> 'NULL' AND p_org_id IS NOT NULL) THEN
3516 l_org_id := p_org_id;
3517 ELSE
3518 l_org_id := NULL;
3519 END IF;
3520
3521 l_sql_stmt1 :=
3522 'SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
3523 EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
3524 CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
3525 CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
3526 MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
3527 NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
3528 WDJ.WIP_ENTITY_ID,
3529 WE.WIP_ENTITY_NAME,
3530 WDJ.ORGANIZATION_ID,
3531 OOD.ORGANIZATION_CODE,
3532 MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
3533 CII.CATEGORY_ID ASSET_CATEGORY_ID,
3534 MEL.LOCATION_CODES ASSET_LOCATION,
3535 BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
3536 EAFC.FAILURE_CODE,
3537 EAFC.CAUSE_CODE,
3538 EAFC.RESOLUTION_CODE,
3539 EAF.FAILURE_DATE,
3540 EAFC.COMMENTS,
3541 DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY '||l_partition_by||' ORDER BY EAF.FAILURE_DATE ),
3542 NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
3543 FROM CSI_ITEM_INSTANCES CII1
3544 WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
3545 AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
3546 (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
3547 ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
3548 (WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
3549 NULL METER_ID,
3550 NULL METER_NAME,
3551 NULL METER_UOM,
3552 NULL READING_BETWEEN_FAILURES,
3553 '||'''Y'''||' INCLUDE_FOR_READING_AGGR,
3554 '||'''Y'''||' INCLUDE_FOR_COST_AGGR ';
3555
3556 l_sql_stmt2 := ' FROM WIP_DISCRETE_JOBS WDJ,
3557 WIP_ENTITIES WE,
3558 CSI_ITEM_INSTANCES CII,
3559 MTL_CATEGORIES_KFV MCKFV,
3560 MTL_SYSTEM_ITEMS_KFV MSIKFV,
3561 MTL_EAM_LOCATIONS MEL,
3562 BOM_DEPARTMENTS BD,
3563 EAM_ASSET_FAILURE_CODES EAFC,
3564 EAM_ASSET_FAILURES EAF,
3565 ORG_ORGANIZATION_DEFINITIONS OOD
3566 WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
3567 AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
3568 AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
3569 AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
3570 AND WDJ.STATUS_TYPE IN (4,5,12)
3571 AND EAF.SOURCE_TYPE = 1
3572 AND EAF.OBJECT_TYPE = 3
3573 AND EAF.OBJECT_ID = CII.INSTANCE_ID
3574 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
3575 AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
3576 AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
3577 AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
3578 AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
3579 AND MEL.LOCATION_ID (+) = EAF.AREA_ID
3580 AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
3581 AND EAF.OBJECT_ID IN';
3582
3583 l_sql_stmt3 := '(SELECT CII.INSTANCE_ID
3584 FROM CSI_ITEM_INSTANCES CII,
3585 MTL_SERIAL_NUMBERS MSN
3586 WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
3587 AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
3588 AND MSN.GEN_OBJECT_ID IN
3589 ( SELECT OBJECT_ID
3590 FROM MTL_OBJECT_GENEALOGY
3591 START WITH PARENT_OBJECT_ID IN
3592 (SELECT GEN_OBJECT_ID PARENT_OBJECT_ID FROM ';
3593 --when it IS required to have a parent failure wo, in order to select the children assets.
3594
3595 l_sql_stmt4 := '(SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
3596 CII.INSTANCE_ID MAINTENANCE_OBJECT_ID,
3597 MSN.GEN_OBJECT_ID GEN_OBJECT_ID,
3598 CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
3599 CII.INSTANCE_DESCRIPTION,
3600 MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
3601 MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
3602 MEL.LOCATION_CODES ASSET_LOCATION,
3603 BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
3604 EAFC.FAILURE_CODE,
3605 EFC.DESCRIPTION FAILURE_DESC,
3606 EAFC.CAUSE_CODE,
3607 ECC.DESCRIPTION CAUSE_DESC,
3608 EAFC.RESOLUTION_CODE,
3609 ERC.DESCRIPTION RESOLUTION_DESC,
3610 EAF.FAILURE_DATE,
3611 EAFC.COMMENTS
3612 FROM WIP_DISCRETE_JOBS WDJ,
3613 CSI_ITEM_INSTANCES CII,
3614 MTL_CATEGORIES_KFV MCKFV,
3615 MTL_SYSTEM_ITEMS_KFV MSIKFV,
3616 MTL_EAM_LOCATIONS MEL,
3617 BOM_DEPARTMENTS BD,
3618 EAM_ASSET_FAILURE_CODES EAFC,
3619 EAM_ASSET_FAILURES EAF,
3620 EAM_FAILURE_CODES EFC,
3621 EAM_CAUSE_CODES ECC,
3622 MTL_SERIAL_NUMBERS MSN,
3623 EAM_RESOLUTION_CODES ERC
3624 WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
3625 AND WDJ.STATUS_TYPE IN (4,5,12)
3626 AND EAF.SOURCE_TYPE = 1
3627 AND EAF.OBJECT_TYPE = 3
3628 AND EAF.OBJECT_ID = CII.INSTANCE_ID
3629 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
3630 AND EAFC.FAILURE_CODE = EFC.FAILURE_CODE
3631 AND EAFC.CAUSE_CODE = ECC.CAUSE_CODE
3632 AND EAFC.RESOLUTION_CODE = ERC.RESOLUTION_CODE
3633 AND (EFC.EFFECTIVE_END_DATE IS NULL OR EFC.EFFECTIVE_END_DATE >= SYSDATE)
3634 AND (ECC.EFFECTIVE_END_DATE IS NULL OR ECC.EFFECTIVE_END_DATE >= SYSDATE)
3635 AND (ERC.EFFECTIVE_END_DATE IS NULL OR ERC.EFFECTIVE_END_DATE >= SYSDATE)
3636 AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
3637 AND EAF.MAINT_ORGANIZATION_ID = WDJ.ORGANIZATION_ID
3638 AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
3639 AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
3640 AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
3641 AND MEL.LOCATION_ID (+) = EAF.AREA_ID
3642 AND BD.DEPARTMENT_ID (+)= EAF.DEPARTMENT_ID
3643 AND CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
3644 AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER )';
3645
3646 --when it's NOT required to have a parent failure wo, in order to select the children assets.
3647 l_sql_stmt5 := ' (SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
3648 MSN.GEN_OBJECT_ID GEN_OBJECT_ID,
3649 CII.INSTANCE_ID MAINTENANCE_OBJECT_ID ,
3650 CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
3651 CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
3652 MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
3653 MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
3654 MEL.LOCATION_CODES ASSET_LOCATION,
3655 BD.DEPARTMENT_CODE OWNING_DEPARTMENT
3656 FROM MTL_SERIAL_NUMBERS MSN,
3657 MTL_CATEGORIES_KFV MCKFV,
3658 MTL_SYSTEM_ITEMS_KFV MSIKFV,
3659 MTL_EAM_LOCATIONS MEL,
3660 BOM_DEPARTMENTS BD,
3661 CSI_ITEM_INSTANCES CII,
3662 (SELECT * FROM EAM_ORG_MAINT_DEFAULTS WHERE ORGANIZATION_ID= '||l_org_id|| ' ) EOMD1 ' ||
3663 ' WHERE CII.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID
3664 AND CII.LAST_VLD_ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID
3665 AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
3666 AND MEL.LOCATION_ID (+) = EOMD1.AREA_ID
3667 AND BD.DEPARTMENT_ID (+)= EOMD1.OWNING_DEPARTMENT_ID
3668 AND MSIKFV.EAM_ITEM_TYPE IN (1,3)
3669 AND CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
3670 AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
3671 AND EOMD1.OBJECT_ID(+) = CII.INSTANCE_ID
3672 AND EOMD1.OBJECT_TYPE(+) = 50)';
3673
3674
3675
3676 IF (l_where_clause IS NULL) THEN --parents need NOT have a failure work order, to get children selected
3677 OPEN x_ref_failures FOR
3678 l_sql_stmt1||' '||l_sql_stmt2||' '||l_sql_stmt3||' '||l_sql_stmt5||' '||l_where_clause_1||' '||l_from_date_clause||') CONNECT BY NOCYCLE PRIOR OBJECT_ID = PARENT_OBJECT_ID )'||
3679 ' UNION '||
3680 ' SELECT MAINTENANCE_OBJECT_ID FROM ( '||l_sql_stmt1||' '||l_sql_stmt2||' (SELECT MAINTENANCE_OBJECT_ID PARENT_OBJECT_ID FROM '||l_sql_stmt5||' '||l_where_clause_1||' )) '||l_from_date_clause ||' )';
3681
3682 ELSIF (l_where_clause_1 IS NULL) THEN --parents need TO have atleast one failure work order, to get children selected
3683 OPEN x_ref_failures FOR
3684 l_sql_stmt1||' '||l_sql_stmt2||' '||l_sql_stmt3||' '||l_sql_stmt4||' '||l_where_clause||' '||l_from_date_clause||') CONNECT BY NOCYCLE PRIOR OBJECT_ID = PARENT_OBJECT_ID )'||
3685 ' UNION '||
3686 ' SELECT MAINTENANCE_OBJECT_ID FROM ( '||l_sql_stmt1||' '||l_sql_stmt2||' (SELECT MAINTENANCE_OBJECT_ID PARENT_OBJECT_ID FROM '||l_sql_stmt4||' '||l_where_clause||' )) '||l_from_date_clause ||' )';
3687
3688 END IF;
3689
3690 /* OPEN x_ref_failures FOR l_sql_stmt||' '||l_sql_stmt2||' '||l_sql_stmt3||' '||l_where_clause||' '||l_from_date_clause||') CONNECT BY PRIOR OBJECT_ID = PARENT_OBJECT_ID )';*/
3691
3692 END GET_CHILD_RECS_CURSOR;
3693
3694 PROCEDURE GET_CHILD_METER_RECS_CURSOR
3695 ( P_WHERE_CLAUSE IN VARCHAR2,
3696 P_WHERE_CLAUSE_1 IN VARCHAR2,
3697 P_FROM_DATE_CLAUSE IN VARCHAR2,
3698 P_VIEW_BY IN VARCHAR2,
3699 P_ORG_ID IN VARCHAR2,
3700
3701 X_REF_FAILURES OUT NOCOPY SYS_REFCURSOR) IS
3702
3703 l_sql_stmt1 VARCHAR2(8000);
3704 l_where_clause_1 VARCHAR2(8000);
3705 l_from_date_clause VARCHAR2(8000);
3706 l_where_clause VARCHAR2(8000);
3707 l_final_where_clause VARCHAR2(8000);
3708 l_org_id VARCHAR2(50);
3709 l_partition_by VARCHAR2(50);
3710 l_first_tbf_calc_clause VARCHAR2(500);
3711 l_sql_stmt2 VARCHAR2(8000);
3712 l_sql_stmt3 VARCHAR2(8000);
3713 l_sql_stmt4 VARCHAR2(8000);
3714 l_sql_stmt5 VARCHAR2(8000);
3715 l_final_sql_stmt VARCHAR2(8000);
3716
3717
3718 BEGIN
3719
3720 g_module_name := 'GET_CHILD_METER_RECS_CURSOR';
3721
3722 IF (p_where_clause <> 'NULL' AND p_where_clause IS NOT NULL) THEN
3723 l_where_clause := p_where_clause;
3724 ELSE
3725 l_where_clause := NULL;
3726 END IF;
3727
3728 IF (p_where_clause_1 <> 'NULL' AND p_where_clause_1 IS NOT NULL) THEN
3729 l_where_clause_1 := p_where_clause_1;
3730 ELSE
3731 l_where_clause_1 := NULL;
3732 END IF;
3733
3734 IF (p_from_date_clause <> 'NULL' AND p_from_date_clause IS NOT NULL) THEN
3735 l_from_date_clause := p_from_date_clause;
3736 ELSE
3737 l_from_date_clause := NULL;
3738 END IF;
3739
3740 IF p_view_by = 1 THEN
3741 l_partition_by := 'EAF.OBJECT_ID';
3742 l_first_tbf_calc_clause := 'CII.CREATION_DATE';
3743 ELSIF p_view_by = 2 THEN
3744 l_partition_by := 'CII.INVENTORY_ITEM_ID';
3745 l_first_tbf_calc_clause := '(SELECT MIN(CII1.CREATION_DATE)
3746 FROM CSI_ITEM_INSTANCES CII1
3747 WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
3748 AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID)';
3749 END IF;
3750
3751 IF (p_org_id <> 'NULL' AND p_org_id IS NOT NULL) THEN
3752 l_org_id := p_org_id;
3753 ELSE
3754 l_org_id := NULL;
3755 END IF;
3756 l_sql_stmt1 := 'SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
3757 EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
3758 CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
3759 CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
3760 MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
3761 NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
3762 WDJ.WIP_ENTITY_ID,
3763 WE.WIP_ENTITY_NAME,
3764 WDJ.ORGANIZATION_ID,
3765 OOD.ORGANIZATION_CODE,
3766 MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
3767 CII.CATEGORY_ID ASSET_CATEGORY_ID,
3768 MEL.LOCATION_CODES ASSET_LOCATION,
3769 BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
3770 EAFC.FAILURE_CODE,
3771 EAFC.CAUSE_CODE,
3772 EAFC.RESOLUTION_CODE,
3773 EAF.FAILURE_DATE,
3774 EAFC.COMMENTS,
3775 DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY '||l_partition_by||' ORDER BY EAF.FAILURE_DATE ),
3776 NULL, (EAF.FAILURE_DATE - '||l_first_tbf_calc_clause||'),
3777 (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
3778 ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
3779 (WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
3780 METER.METER_ID,
3781 METER.METER_NAME METER_NAME,
3782 METER.METER_UOM METER_UOM,
3783 DECODE(METER.METER_TYPE,2,METER.CURRENT_READING,1,
3784 DECODE( LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ),
3785 NULL, METER.CURRENT_READING,
3786 (METER.CURRENT_READING -
3787 (LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ))) )) READING_BETWEEN_FAILURES,
3788 '||'''Y'''||' INCLUDE_FOR_READING_AGGR,
3789 '||'''Y'''||' INCLUDE_FOR_COST_AGGR
3790 FROM WIP_DISCRETE_JOBS WDJ,
3791 WIP_ENTITIES WE,
3792 CSI_ITEM_INSTANCES CII,
3793 MTL_CATEGORIES_KFV MCKFV,
3794 MTL_SYSTEM_ITEMS_KFV MSIKFV,
3795 MTL_EAM_LOCATIONS MEL,
3796 BOM_DEPARTMENTS BD,
3797 EAM_ASSET_FAILURE_CODES EAFC,
3798 EAM_ASSET_FAILURES EAF,
3799 ORG_ORGANIZATION_DEFINITIONS OOD,
3800 (SELECT
3801 ccb.counter_id METER_ID,
3802 cctl.name METER_NAME,
3803 ccb.uom_code METER_UOM,
3804 CCA.SOURCE_OBJECT_ID MAINTENANCE_OBJECT_ID,
3805 CCR.COUNTER_READING CURRENT_READING,
3806 CCR.VALUE_TIMESTAMP CURRENT_READING_DATE,
3807 CCA.PRIMARY_FAILURE_FLAG,
3808 decode(ct.transaction_type_id,92,ct.source_header_ref_id,to_number(null)) WIP_ENTITY_ID,
3809 CCB.reading_type METER_TYPE
3810 FROM csi_counters_b CCB,csi_counters_tl cctl, csi_counter_readings CCR, csi_counter_associations CCA, csi_transactions CT ';
3811 l_sql_stmt2 := 'WHERE ccb.counter_id = cctl.counter_id
3812 and SYSDATE BETWEEN nvl(ccb.start_date_active, SYSDATE-1) AND nvl(ccb.end_date_active, SYSDATE+1)
3813 and cctl.language = userenv('|| '''LANG'''|| ') and ccb.counter_type = '||'''REGULAR'''||'
3814 AND CCB.COUNTER_ID = CCA.COUNTER_ID
3815 AND CCR.COUNTER_ID(+) = CCB.COUNTER_ID
3816 AND CCR.transaction_id = CT.transaction_id(+)
3817 and SYSDATE BETWEEN nvl(cca.start_date_active, SYSDATE-1) AND nvl(cca.end_date_active, SYSDATE+1)
3818 AND CCA.PRIMARY_FAILURE_FLAG = '||'''Y'''||'
3819 AND CCB.EAM_REQUIRED_FLAG = '||'''Y'''||'
3820 AND CCR.COUNTER_VALUE_ID IN
3821 (
3822 SELECT
3823 METER_READING_ID
3824 FROM
3825 (
3826 SELECT
3827 Max(EMR1.METER_READING_ID) METER_READING_ID
3828 FROM EAM_METER_READINGS_V EMR1
3829 GROUP BY EMR1.WIP_ENTITY_ID,
3830 EMR1.METER_ID
3831 )
3832 )) METER
3833 WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
3834 AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
3835 AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
3836 AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
3837 AND WDJ.STATUS_TYPE IN (4,5,12)
3838 AND EAF.SOURCE_TYPE = 1
3839 AND EAF.OBJECT_TYPE = 3
3840 AND EAF.OBJECT_ID = CII.INSTANCE_ID
3841 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
3842 AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
3843 AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
3844 AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
3845 AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
3846 AND MEL.LOCATION_ID (+) = EAF.AREA_ID
3847 AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
3848 AND EAF.OBJECT_ID = METER.MAINTENANCE_OBJECT_ID (+)
3849 AND METER.WIP_ENTITY_ID (+) = EAF.SOURCE_ID
3850 AND EAF.OBJECT_ID IN ';
3851 l_sql_stmt3 := '( SELECT CII.INSTANCE_ID
3852 FROM CSI_ITEM_INSTANCES CII,
3853 MTL_SERIAL_NUMBERS MSN
3854 WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
3855 AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
3856 AND MSN.gen_object_id IN
3857 (SELECT OBJECT_ID
3858 FROM MTL_OBJECT_GENEALOGY
3859 START WITH PARENT_OBJECT_ID IN
3860 (SELECT GEN_OBJECT_ID PARENT_OBJECT_ID FROM';
3861 --when it IS required to have a parent failure wo, in order to select the children assets.
3862
3863 l_sql_stmt4 := '(SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
3864 EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
3865 MSN.GEN_OBJECT_ID GEN_OBJECT_ID,
3866 CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
3867 CII.INSTANCE_DESCRIPTION,
3868 MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
3869 MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
3870 MEL.LOCATION_CODES ASSET_LOCATION,
3871 BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
3872 EAFC.FAILURE_CODE,
3873 EFC.DESCRIPTION FAILURE_DESC,
3874 EAFC.CAUSE_CODE,
3875 ECC.DESCRIPTION CAUSE_DESC,
3876 EAFC.RESOLUTION_CODE,
3877 ERC.DESCRIPTION RESOLUTION_DESC,
3878 EAF.FAILURE_DATE,
3879 EAFC.COMMENTS
3880
3881 FROM WIP_DISCRETE_JOBS WDJ,
3882 CSI_ITEM_INSTANCES CII,
3883 MTL_CATEGORIES_KFV MCKFV,
3884 MTL_SYSTEM_ITEMS_KFV MSIKFV,
3885 MTL_EAM_LOCATIONS MEL,
3886 BOM_DEPARTMENTS BD,
3887 EAM_ASSET_FAILURE_CODES EAFC,
3888 EAM_ASSET_FAILURES EAF,
3889 EAM_FAILURE_CODES EFC,
3890 EAM_CAUSE_CODES ECC,
3891 EAM_RESOLUTION_CODES ERC,
3892 MTL_SERIAL_NUMBERS MSN
3893
3894 WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
3895 AND WDJ.STATUS_TYPE IN (4,5,12)
3896 AND EAF.SOURCE_TYPE = 1
3897 AND EAF.OBJECT_TYPE = 3
3898 AND EAF.OBJECT_ID = CII.INSTANCE_ID
3899 AND EAF.FAILURE_ID = EAFC.FAILURE_ID
3900 AND EAFC.FAILURE_CODE = EFC.FAILURE_CODE
3901 AND EAFC.CAUSE_CODE = ECC.CAUSE_CODE
3902 AND EAFC.RESOLUTION_CODE = ERC.RESOLUTION_CODE
3903 AND (EFC.EFFECTIVE_END_DATE IS NULL OR EFC.EFFECTIVE_END_DATE >= SYSDATE)
3904 AND (ECC.EFFECTIVE_END_DATE IS NULL OR ECC.EFFECTIVE_END_DATE >= SYSDATE)
3905 AND (ERC.EFFECTIVE_END_DATE IS NULL OR ERC.EFFECTIVE_END_DATE >= SYSDATE)
3906 AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
3907 AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
3908 AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
3909 AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
3910 AND MEL.LOCATION_ID (+) = EAF.AREA_ID
3911 AND BD.DEPARTMENT_ID (+)= EAF.DEPARTMENT_ID
3912 AND CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
3913 AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER )';
3914
3915 --when it's NOT required to have a parent failure wo, in order to select the children assets.
3916 l_sql_stmt5 :=
3917 '(SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
3918 MSN.GEN_OBJECT_ID GEN_OBJECT_ID,
3919 CII.INSTANCE_ID MAINTENANCE_OBJECT_ID ,
3920 CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
3921 CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
3922 MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
3923 MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
3924 MEL.LOCATION_CODES ASSET_LOCATION,
3925 BD.DEPARTMENT_CODE OWNING_DEPARTMENT
3926 FROM MTL_SERIAL_NUMBERS MSN,
3927 MTL_CATEGORIES_KFV MCKFV,
3928 MTL_SYSTEM_ITEMS_KFV MSIKFV,
3929 MTL_EAM_LOCATIONS MEL,
3930 BOM_DEPARTMENTS BD,
3931 CSI_ITEM_INSTANCES CII,
3932 (SELECT * FROM EAM_ORG_MAINT_DEFAULTS WHERE ORGANIZATION_ID= '||l_org_id|| ' ) EOMD1 ' ||
3933 ' WHERE CII.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID
3934 AND CII.LAST_VLD_ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID
3935 AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
3936 AND MEL.LOCATION_ID (+) = EOMD1.AREA_ID
3937 AND BD.DEPARTMENT_ID (+)= EOMD1.OWNING_DEPARTMENT_ID
3938 AND MSIKFV.EAM_ITEM_TYPE IN (1,3)
3939 AND CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
3940 AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
3941 AND EOMD1.OBJECT_ID(+) = CII.INSTANCE_ID
3942 AND EOMD1.OBJECT_TYPE(+) = 50)';
3943
3944
3945 IF (l_where_clause IS NULL) THEN --parents need NOT have a failure work order, to get children selected
3946 OPEN x_ref_failures FOR
3947 l_sql_stmt1||' '||l_sql_stmt2||' '||l_sql_stmt3||' '||l_sql_stmt5||' '||l_where_clause_1||' '||l_from_date_clause||') CONNECT BY NOCYCLE PRIOR OBJECT_ID = PARENT_OBJECT_ID )'||
3948 ' UNION '||
3949 ' SELECT MAINTENANCE_OBJECT_ID FROM ( '||l_sql_stmt1||' '||l_sql_stmt2||' (SELECT MAINTENANCE_OBJECT_ID PARENT_OBJECT_ID FROM '||l_sql_stmt5||' '||l_where_clause_1||' )) '||l_from_date_clause||' )';
3950
3951 ELSIF (l_where_clause_1 IS NULL) THEN --parents need TO have atleast one failure work order, to get children selected
3952 OPEN x_ref_failures FOR
3953
3954 l_sql_stmt1||' '||l_sql_stmt2||' '||l_sql_stmt3||' '||l_sql_stmt4||' '||l_where_clause||' '||l_from_date_clause||') CONNECT BY NOCYCLE PRIOR OBJECT_ID = PARENT_OBJECT_ID )'||
3955 ' UNION '||
3956 ' SELECT MAINTENANCE_OBJECT_ID FROM ( '||l_sql_stmt1||' '||l_sql_stmt2||' (SELECT MAINTENANCE_OBJECT_ID PARENT_OBJECT_ID FROM '||l_sql_stmt4||' '||l_where_clause||' )) '||l_from_date_clause||' )';
3957
3958 END IF;
3959
3960 --OPEN x_ref_failures FOR l_sql_stmt||' '||l_sql_stmt2||' '||l_sql_stmt3||' '||l_where_clause||' '||l_from_date_clause||') CONNECT BY PRIOR OBJECT_ID = PARENT_OBJECT_ID)';
3961
3962 END GET_CHILD_METER_RECS_CURSOR;
3963
3964 END EAM_FAILURE_ANALYSIS_PVT;
3965
3966
3967