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