[Home] [Help]
239: l_first_tbf_calc_clause := 'ASSET_CREATION_DATE';
240: ELSIF (p_view_by = 2) THEN
241: l_partition_by := 'MAINTAINED_GROUP_ID';
242: l_first_tbf_calc_clause := '(SELECT MIN(CII1.CREATION_DATE)
243: FROM CSI_ITEM_INSTANCES CII1
244: WHERE CII1.INVENTORY_ITEM_ID = MAINTAINED_GROUP_ID
245: AND CII1.LAST_VLD_ORGANIZATION_ID = ASSET_ORGANIZATION_ID)';
246: END IF;
247:
316: METER.METER_TYPE,
317: CII.CREATION_DATE ASSET_CREATION_DATE
318: FROM WIP_DISCRETE_JOBS WDJ,
319: WIP_ENTITIES WE,
320: CSI_ITEM_INSTANCES CII,
321: MTL_CATEGORIES_KFV MCKFV,
322: MTL_SYSTEM_ITEMS_KFV MSIKFV,
323: MTL_EAM_LOCATIONS MEL,
324: BOM_DEPARTMENTS BD,
469: FAILURE_DATE,
470: COMMENTS,
471: DECODE( LAG(FAILURE_DATE,1,NULL) OVER (PARTITION BY '||l_partition_by||' ORDER BY FAILURE_DATE),
472: NULL, (FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
473: FROM CSI_ITEM_INSTANCES CII1
474: WHERE CII1.INVENTORY_ITEM_ID = MAINTAINED_GROUP_ID
475: AND CII1.CURRENT_ORGANIZATION_ID = ASSET_ORGANIZATION_ID)),
476: (FAILURE_DATE - ( LAG(FAILURE_DATE,1,NULL) OVER (PARTITION BY '||l_partition_by||' ORDER BY FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
477: (DATE_COMPLETED - FAILURE_DATE ) * 24 TIME_TO_REPAIR,
508: EAFC.COMMENTS,
509: CII.LAST_VLD_ORGANIZATION_ID ASSET_ORGANIZATION_ID
510: FROM WIP_DISCRETE_JOBS WDJ,
511: WIP_ENTITIES WE,
512: CSI_ITEM_INSTANCES CII,
513: MTL_CATEGORIES_KFV MCKFV,
514: MTL_SYSTEM_ITEMS_KFV MSIKFV,
515: MTL_EAM_LOCATIONS MEL,
516: BOM_DEPARTMENTS BD,
667: METER.PRIMARY_FAILURE_METER,
668: METER.METER_ID
669: FROM WIP_DISCRETE_JOBS WDJ,
670: WIP_ENTITIES WE,
671: CSI_ITEM_INSTANCES CII,
672: MTL_CATEGORIES_KFV MCKFV,
673: MTL_SYSTEM_ITEMS_KFV MSIKFV,
674: MTL_EAM_LOCATIONS MEL,
675: BOM_DEPARTMENTS BD,
896: EAFC.COMMENTS,
897: DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY CII.INSTANCE_ID, CII.CATEGORY_ID ORDER BY EAF.FAILURE_DATE),
898: --NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
899: NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII.CREATION_DATE)
900: FROM CSI_ITEM_INSTANCES CII1
901: WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
902: AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
903: (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
904: ( PARTITION BY CII.INSTANCE_ID, CII.CATEGORY_ID ORDER BY EAF.FAILURE_DATE)) )) DAYS_BETWEEN_FAILURES,
910: 'Y' INCLUDE_FOR_COST_AGGR,
911: NULL METER_ID
912: FROM WIP_DISCRETE_JOBS WDJ,
913: WIP_ENTITIES WE,
914: CSI_ITEM_INSTANCES CII,
915: MTL_CATEGORIES_KFV MCKFV,
916: MTL_SYSTEM_ITEMS_KFV MSIKFV,
917: MTL_EAM_LOCATIONS MEL,
918: BOM_DEPARTMENTS BD,
997: EAFC.COMMENTS,
998: DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY CII.INSTANCE_ID, EAFC.FAILURE_CODE ORDER BY EAF.FAILURE_DATE),
999: --NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
1000: NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
1001: FROM CSI_ITEM_INSTANCES CII1
1002: WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
1003: AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
1004: (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
1005: ( PARTITION BY CII.INSTANCE_ID, EAFC.FAILURE_CODE ORDER BY EAF.FAILURE_DATE)) )) DAYS_BETWEEN_FAILURES,
1011: 'Y' INCLUDE_FOR_COST_AGGR,
1012: NULL METER_ID
1013: FROM WIP_DISCRETE_JOBS WDJ,
1014: WIP_ENTITIES WE,
1015: CSI_ITEM_INSTANCES CII,
1016: MTL_CATEGORIES_KFV MCKFV,
1017: MTL_SYSTEM_ITEMS_KFV MSIKFV,
1018: MTL_EAM_LOCATIONS MEL,
1019: BOM_DEPARTMENTS BD,
1113: METER.METER_ID,
1114: METER.PRIMARY_FAILURE_METER
1115: FROM WIP_DISCRETE_JOBS WDJ,
1116: WIP_ENTITIES WE,
1117: CSI_ITEM_INSTANCES CII,
1118: MTL_CATEGORIES_KFV MCKFV,
1119: MTL_SYSTEM_ITEMS_KFV MSIKFV,
1120: MTL_EAM_LOCATIONS MEL,
1121: BOM_DEPARTMENTS BD,
1237: EAFC.COMMENTS,
1238: DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY CII.INSTANCE_ID, CII.INVENTORY_ITEM_ID ORDER BY EAF.FAILURE_DATE ),
1239: --NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
1240: NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
1241: FROM CSI_ITEM_INSTANCES CII1
1242: WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
1243: AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
1244: (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
1245: ( PARTITION BY CII.INSTANCE_ID, CII.INVENTORY_ITEM_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
1256: METER.METER_ID,
1257: METER.PRIMARY_FAILURE_METER
1258: FROM WIP_DISCRETE_JOBS WDJ,
1259: WIP_ENTITIES WE,
1260: CSI_ITEM_INSTANCES CII,
1261: MTL_CATEGORIES_KFV MCKFV,
1262: MTL_SYSTEM_ITEMS_KFV MSIKFV,
1263: MTL_EAM_LOCATIONS MEL,
1264: BOM_DEPARTMENTS BD,
1772: EAFC.COMMENTS,
1773: DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY CII.CATEGORY_ID ORDER BY EAF.FAILURE_DATE ),
1774: --NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
1775: NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
1776: FROM CSI_ITEM_INSTANCES CII1
1777: WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
1778: AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
1779: (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
1780: ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
1786: 'Y' INCLUDE_FOR_READING_AGGR,
1787: 'Y' INCLUDE_FOR_COST_AGGR
1788: FROM WIP_DISCRETE_JOBS WDJ,
1789: WIP_ENTITIES WE,
1790: CSI_ITEM_INSTANCES CII,
1791: MTL_CATEGORIES_KFV MCKFV,
1792: MTL_SYSTEM_ITEMS_KFV MSIKFV,
1793: MTL_EAM_LOCATIONS MEL,
1794: BOM_DEPARTMENTS BD,
1810: AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
1811: AND MEL.LOCATION_ID (+) = EAF.AREA_ID
1812: AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
1813: AND EAF.OBJECT_ID IN (SELECT CII.INSTANCE_ID
1814: FROM CSI_ITEM_INSTANCES CII,
1815: MTL_SERIAL_NUMBERS MSN
1816: WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
1817: AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
1818: AND MSN.gen_object_id IN
1822: START WITH PARENT_OBJECT_ID IN
1823: (
1824: SELECT MSN.GEN_OBJECT_ID PARENT_OBJECT_ID
1825: FROM MTL_SERIAL_NUMBERS MSN,
1826: CSI_ITEM_INSTANCES CII
1827: WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
1828: AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
1829: AND (l_gen_object_id IS NULL OR CII.INSTANCE_ID = l_gen_object_id)
1830: AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
1833: CONNECT BY NOCYCLE PRIOR OBJECT_ID = PARENT_OBJECT_ID
1834: )
1835: UNION
1836: SELECT CII.INSTANCE_ID OBJECT_ID --This part will select the parent assets
1837: FROM CSI_ITEM_INSTANCES CII
1838: WHERE (l_gen_object_id IS NULL OR CII.INSTANCE_ID = l_gen_object_id)
1839: AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
1840: AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
1841: ) ;
1863: EAFC.COMMENTS,
1864: DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY CII.CATEGORY_ID ORDER BY EAF.FAILURE_DATE ),
1865: --NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
1866: NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
1867: FROM CSI_ITEM_INSTANCES CII1
1868: WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
1869: AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
1870: (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
1871: ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
1883: MTL_EAM_LOCATIONS MEL,
1884: BOM_DEPARTMENTS BD,
1885: EAM_ASSET_FAILURE_CODES EAFC,
1886: EAM_ASSET_FAILURES EAF,
1887: CSI_ITEM_INSTANCES CII,
1888: ORG_ORGANIZATION_DEFINITIONS OOD
1889: WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
1890: AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
1891: AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
1903: AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
1904: AND (p_to_date IS NULL OR FAILURE_DATE <= p_to_date)
1905: AND (p_from_date IS NULL OR FAILURE_DATE >= p_from_date)
1906: AND EAF.OBJECT_ID IN (SELECT CII.INSTANCE_ID
1907: FROM CSI_ITEM_INSTANCES CII,
1908: MTL_SERIAL_NUMBERS MSN
1909: WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
1910: AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
1911: AND MSN.gen_object_id IN (
1912: SELECT OBJECT_ID --This part will select all children assets
1913: FROM MTL_OBJECT_GENEALOGY
1914: START WITH PARENT_OBJECT_ID IN
1915: ( SELECT MSN.GEN_OBJECT_ID PARENT_OBJECT_ID
1916: FROM CSI_ITEM_INSTANCES CII,
1917: EAM_ASSET_FAILURE_CODES EAFC,
1918: EAM_ASSET_FAILURES EAF,
1919: WIP_DISCRETE_JOBS WDJ,
1920: MTL_SERIAL_NUMBERS MSN
1962: EAFC.COMMENTS,
1963: DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY CII.CATEGORY_ID ORDER BY EAF.FAILURE_DATE ),
1964: --NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
1965: NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
1966: FROM CSI_ITEM_INSTANCES CII1
1967: WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
1968: AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
1969: (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
1970: ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
1983: BOM_DEPARTMENTS BD,
1984: EAM_ASSET_FAILURE_CODES EAFC,
1985: EAM_ASSET_FAILURES EAF,
1986: ORG_ORGANIZATION_DEFINITIONS OOD,
1987: CSI_ITEM_INSTANCES CII
1988: WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
1989: AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
1990: AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
1991: AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
2003: AND EAF.OBJECT_ID IN (SELECT EAF.OBJECT_ID OBJECT_ID --This part will select parent assets
2004: FROM EAM_ASSET_FAILURE_CODES EAFC,
2005: EAM_ASSET_FAILURES EAF,
2006: WIP_DISCRETE_JOBS WDJ,
2007: CSI_ITEM_INSTANCES CII
2008: WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
2009: AND WDJ.STATUS_TYPE IN (4,5,12)
2010: AND EAF.SOURCE_TYPE = 1
2011: AND EAF.OBJECT_TYPE = 3
2045: EAFC.COMMENTS,
2046: DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY EAFC.FAILURE_CODE ORDER BY EAF.FAILURE_DATE ),
2047: --NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
2048: NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
2049: FROM CSI_ITEM_INSTANCES CII1
2050: WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
2051: AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
2052: (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
2053: ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
2059: 'Y' INCLUDE_FOR_READING_AGGR,
2060: 'Y' INCLUDE_FOR_COST_AGGR
2061: FROM WIP_DISCRETE_JOBS WDJ,
2062: WIP_ENTITIES WE,
2063: CSI_ITEM_INSTANCES CII,
2064: MTL_CATEGORIES_KFV MCKFV,
2065: MTL_SYSTEM_ITEMS_KFV MSIKFV,
2066: MTL_EAM_LOCATIONS MEL,
2067: BOM_DEPARTMENTS BD,
2083: AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
2084: AND MEL.LOCATION_ID (+) = EAF.AREA_ID
2085: AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
2086: AND EAF.OBJECT_ID IN (SELECT CII.INSTANCE_ID
2087: FROM CSI_ITEM_INSTANCES CII,
2088: MTL_SERIAL_NUMBERS MSN
2089: WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
2090: AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
2091: AND MSN.gen_object_id IN
2093: FROM MTL_OBJECT_GENEALOGY
2094: START WITH PARENT_OBJECT_ID IN
2095: ( SELECT MSN.GEN_OBJECT_ID PARENT_OBJECT_ID
2096: FROM MTL_SERIAL_NUMBERS MSN,
2097: CSI_ITEM_INSTANCES CII
2098: WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
2099: AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
2100: AND (l_gen_object_id IS NULL OR CII.INSTANCE_ID = l_gen_object_id)
2101: AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
2104: CONNECT BY NOCYCLE PRIOR OBJECT_ID = PARENT_OBJECT_ID
2105: )
2106: UNION
2107: SELECT CII.INSTANCE_ID OBJECT_ID --This part will select all parent assets
2108: FROM CSI_ITEM_INSTANCES CII
2109: WHERE (l_gen_object_id IS NULL OR CII.INSTANCE_ID = l_gen_object_id)
2110: AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
2111: AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
2112: ) ;
2134: EAFC.COMMENTS,
2135: DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY EAFC.FAILURE_CODE ORDER BY EAF.FAILURE_DATE ),
2136: --NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
2137: NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
2138: FROM CSI_ITEM_INSTANCES CII1
2139: WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
2140: AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
2141: (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
2142: ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
2154: MTL_EAM_LOCATIONS MEL,
2155: BOM_DEPARTMENTS BD,
2156: EAM_ASSET_FAILURE_CODES EAFC,
2157: EAM_ASSET_FAILURES EAF,
2158: CSI_ITEM_INSTANCES CII,
2159: ORG_ORGANIZATION_DEFINITIONS OOD
2160: WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
2161: AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
2162: AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
2174: AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
2175: AND (p_to_date IS NULL OR FAILURE_DATE <= p_to_date)
2176: AND (p_from_date IS NULL OR FAILURE_DATE >= p_from_date)
2177: AND EAF.OBJECT_ID IN (SELECT CII.INSTANCE_ID
2178: FROM CSI_ITEM_INSTANCES CII,
2179: MTL_SERIAL_NUMBERS MSN
2180: WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
2181: AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
2182: AND MSN.gen_object_id IN
2187: FROM EAM_ASSET_FAILURE_CODES EAFC,
2188: EAM_ASSET_FAILURES EAF,
2189: WIP_DISCRETE_JOBS WDJ,
2190: MTL_SERIAL_NUMBERS MSN,
2191: CSI_ITEM_INSTANCES CII
2192: WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
2193: AND WDJ.STATUS_TYPE IN (4,5,12)
2194: AND EAF.SOURCE_TYPE = 1
2195: AND EAF.OBJECT_TYPE = 3
2229: EAFC.COMMENTS,
2230: DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY EAFC.FAILURE_CODE ORDER BY EAF.FAILURE_DATE ),
2231: --NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
2232: NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
2233: FROM CSI_ITEM_INSTANCES CII1
2234: WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
2235: AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
2236: (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
2237: ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
2249: MTL_EAM_LOCATIONS MEL,
2250: BOM_DEPARTMENTS BD,
2251: EAM_ASSET_FAILURE_CODES EAFC,
2252: EAM_ASSET_FAILURES EAF,
2253: CSI_ITEM_INSTANCES CII,
2254: ORG_ORGANIZATION_DEFINITIONS OOD
2255: WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
2256: AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
2257: AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
2270: AND EAF.OBJECT_ID IN ( SELECT EAF.OBJECT_ID OBJECT_ID --This part will select all parent assets
2271: FROM EAM_ASSET_FAILURE_CODES EAFC,
2272: EAM_ASSET_FAILURES EAF,
2273: WIP_DISCRETE_JOBS WDJ,
2274: CSI_ITEM_INSTANCES
2275: WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
2276: AND WDJ.STATUS_TYPE IN (4,5,12)
2277: AND EAF.SOURCE_TYPE = 1
2278: AND EAF.OBJECT_TYPE = 3
2326: 'Y' INCLUDE_FOR_READING_AGGR,
2327: 'Y' INCLUDE_FOR_COST_AGGR
2328: FROM WIP_DISCRETE_JOBS WDJ,
2329: WIP_ENTITIES WE,
2330: CSI_ITEM_INSTANCES CII,
2331: MTL_CATEGORIES_KFV MCKFV,
2332: MTL_SYSTEM_ITEMS_KFV MSIKFV,
2333: MTL_EAM_LOCATIONS MEL,
2334: BOM_DEPARTMENTS BD,
2386: AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
2387: AND EAF.OBJECT_ID = METER.MAINTENANCE_OBJECT_ID (+)
2388: AND METER.WIP_ENTITY_ID (+) = EAF.SOURCE_ID
2389: AND EAF.OBJECT_ID IN (SELECT CII.INSTANCE_ID
2390: FROM CSI_ITEM_INSTANCES CII,
2391: MTL_SERIAL_NUMBERS MSN
2392: WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
2393: AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
2394: AND MSN.gen_object_id IN
2396: FROM MTL_OBJECT_GENEALOGY
2397: START WITH PARENT_OBJECT_ID IN
2398: ( SELECT MSN.GEN_OBJECT_ID PARENT_OBJECT_ID
2399: FROM MTL_SERIAL_NUMBERS MSN,
2400: CSI_ITEM_INSTANCES CII
2401: WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
2402: AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
2403: AND (l_gen_object_id IS NULL OR CII.INSTANCE_ID = l_gen_object_id)
2404: AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
2450: MTL_EAM_LOCATIONS MEL,
2451: BOM_DEPARTMENTS BD,
2452: EAM_ASSET_FAILURE_CODES EAFC,
2453: EAM_ASSET_FAILURES EAF,
2454: CSI_ITEM_INSTANCES CII,
2455: ORG_ORGANIZATION_DEFINITIONS OOD,
2456: (SELECT EM.METER_ID,
2457: EM.METER_NAME,
2458: EM.METER_UOM,
2493: AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
2494: AND EAF.OBJECT_ID = METER.MAINTENANCE_OBJECT_ID (+)
2495: AND METER.WIP_ENTITY_ID (+) = EAF.SOURCE_ID
2496: AND EAF.OBJECT_ID IN ( SELECT CII.INSTANCE_ID OBJECT_ID --This part will select all parent assets
2497: FROM CSI_ITEM_INSTANCES CII
2498: WHERE(l_gen_object_id IS NULL OR CII.INSTANCE_ID = l_gen_object_id)
2499: AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
2500: AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
2501: )));
2544: BOM_DEPARTMENTS BD,
2545: EAM_ASSET_FAILURE_CODES EAFC,
2546: EAM_ASSET_FAILURES EAF,
2547: ORG_ORGANIZATION_DEFINITIONS OOD,
2548: CSI_ITEM_INSTANCES CII,
2549: (SELECT EM.METER_ID,
2550: EM.METER_NAME,
2551: EM.METER_UOM,
2552: EAM.MAINTENANCE_OBJECT_ID,
2591: AND METER.WIP_ENTITY_ID (+) = EAF.SOURCE_ID
2592: AND (p_to_date IS NULL OR FAILURE_DATE <= p_to_date)
2593: AND (p_from_date IS NULL OR FAILURE_DATE >= p_from_date)
2594: AND EAF.OBJECT_ID IN (SELECT CII.INSTANCE_ID
2595: FROM CSI_ITEM_INSTANCES CII,
2596: MTL_SERIAL_NUMBERS MSN
2597: WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
2598: AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
2599: AND MSN.gen_object_id IN
2600: (SELECT OBJECT_ID --This part will select all children assets
2601: FROM MTL_OBJECT_GENEALOGY
2602: START WITH PARENT_OBJECT_ID IN
2603: ( SELECT MSN.GEN_OBJECT_ID PARENT_OBJECT_ID
2604: FROM CSI_ITEM_INSTANCES CII,
2605: EAM_ASSET_FAILURE_CODES EAFC,
2606: EAM_ASSET_FAILURES EAF,
2607: WIP_DISCRETE_JOBS WDJ,
2608: MTL_SERIAL_NUMBERS MSN
2667: BOM_DEPARTMENTS BD,
2668: EAM_ASSET_FAILURE_CODES EAFC,
2669: EAM_ASSET_FAILURES EAF,
2670: ORG_ORGANIZATION_DEFINITIONS OOD,
2671: CSI_ITEM_INSTANCES CII,
2672: (SELECT EM.METER_ID,
2673: EM.METER_NAME,
2674: EM.METER_UOM,
2675: EAM.MAINTENANCE_OBJECT_ID,
2710: AND EAF.OBJECT_ID IN ( SELECT EAF.OBJECT_ID OBJECT_ID --This part will select all parent assets
2711: FROM EAM_ASSET_FAILURE_CODES EAFC,
2712: EAM_ASSET_FAILURES EAF,
2713: WIP_DISCRETE_JOBS WDJ,
2714: CSI_ITEM_INSTANCES CII
2715: WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
2716: AND WDJ.STATUS_TYPE IN (4,5,12)
2717: AND EAF.SOURCE_TYPE = 1
2718: AND EAF.OBJECT_TYPE = 3
2751: EAFC.COMMENTS,
2752: DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY CII.INVENTORY_ITEM_ID ORDER BY EAF.FAILURE_DATE ),
2753: --NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
2754: NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
2755: FROM CSI_ITEM_INSTANCES CII1
2756: WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
2757: AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
2758: (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
2759: ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
2769: 'Y' INCLUDE_FOR_READING_AGGR,
2770: 'Y' INCLUDE_FOR_COST_AGGR
2771: FROM WIP_DISCRETE_JOBS WDJ,
2772: WIP_ENTITIES WE,
2773: CSI_ITEM_INSTANCES CII,
2774: MTL_CATEGORIES_KFV MCKFV,
2775: MTL_SYSTEM_ITEMS_KFV MSIKFV,
2776: MTL_EAM_LOCATIONS MEL,
2777: BOM_DEPARTMENTS BD,
2829: AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
2830: AND EAF.OBJECT_ID = METER.MAINTENANCE_OBJECT_ID (+)
2831: AND METER.WIP_ENTITY_ID (+) = EAF.SOURCE_ID
2832: AND EAF.OBJECT_ID IN (SELECT CII.INSTANCE_ID
2833: FROM CSI_ITEM_INSTANCES CII,
2834: MTL_SERIAL_NUMBERS MSN
2835: WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
2836: AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
2837: AND MSN.gen_object_id IN
2839: FROM MTL_OBJECT_GENEALOGY
2840: START WITH PARENT_OBJECT_ID IN
2841: ( SELECT MSN.GEN_OBJECT_ID PARENT_OBJECT_ID
2842: FROM MTL_SERIAL_NUMBERS MSN,
2843: CSI_ITEM_INSTANCES CII
2844: WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
2845: AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
2846: AND (l_gen_object_id IS NULL OR CII.INSTANCE_ID = l_gen_object_id)
2847: AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
2850: CONNECT BY NOCYCLE PRIOR OBJECT_ID = PARENT_OBJECT_ID
2851: )
2852: UNION
2853: SELECT CII.INSTANCE_ID OBJECT_ID --This part will select all parent assets
2854: FROM CSI_ITEM_INSTANCES CII
2855: WHERE (l_gen_object_id IS NULL OR CII.INSTANCE_ID = l_gen_object_id)
2856: AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
2857: AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
2858: ) ;
2880: EAFC.COMMENTS,
2881: DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY CII.INVENTORY_ITEM_ID ORDER BY EAF.FAILURE_DATE ),
2882: --NULL, (EAF.FAILURE_DATE - MSN.CREATION_DATE),
2883: NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
2884: FROM CSI_ITEM_INSTANCES CII1
2885: WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
2886: AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
2887: (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
2888: ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
2904: MTL_EAM_LOCATIONS MEL,
2905: BOM_DEPARTMENTS BD,
2906: EAM_ASSET_FAILURE_CODES EAFC,
2907: EAM_ASSET_FAILURES EAF,
2908: CSI_ITEM_INSTANCES CII,
2909: ORG_ORGANIZATION_DEFINITIONS OOD,
2910: (SELECT EM.METER_ID,
2911: EM.METER_NAME,
2912: EM.METER_UOM,
2947: AND METER.WIP_ENTITY_ID (+) = EAF.SOURCE_ID
2948: AND (p_to_date IS NULL OR FAILURE_DATE <= p_to_date)
2949: AND (p_from_date IS NULL OR FAILURE_DATE >= p_from_date)
2950: AND EAF.OBJECT_ID IN (SELECT CII.INSTANCE_ID
2951: FROM CSI_ITEM_INSTANCES CII,
2952: MTL_SERIAL_NUMBERS MSN
2953: WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
2954: AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
2955: AND MSN.GEN_OBJECT_ID IN
2956: (SELECT OBJECT_ID --This part will select all children assets
2957: FROM MTL_OBJECT_GENEALOGY
2958: START WITH PARENT_OBJECT_ID IN
2959: ( SELECT MSN.GEN_OBJECT_ID PARENT_OBJECT_ID
2960: FROM CSI_ITEM_INSTANCES CII,
2961: EAM_ASSET_FAILURE_CODES EAFC,
2962: EAM_ASSET_FAILURES EAF,
2963: WIP_DISCRETE_JOBS WDJ,
2964: MTL_SERIAL_NUMBERS MSN
3002: EAFC.COMMENTS,
3003: DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY CII.INVENTORY_ITEM_ID ORDER BY EAF.FAILURE_DATE ),
3004: --NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
3005: NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
3006: FROM CSI_ITEM_INSTANCES CII1
3007: WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
3008: AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
3009: (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
3010: ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
3026: MTL_EAM_LOCATIONS MEL,
3027: BOM_DEPARTMENTS BD,
3028: EAM_ASSET_FAILURE_CODES EAFC,
3029: EAM_ASSET_FAILURES EAF,
3030: CSI_ITEM_INSTANCES CII,
3031: ORG_ORGANIZATION_DEFINITIONS OOD,
3032: (SELECT EM.METER_ID,
3033: EM.METER_NAME,
3034: EM.METER_UOM,
3070: AND EAF.OBJECT_ID IN ( SELECT EAF.OBJECT_ID OBJECT_ID --This part will select all parent assets
3071: FROM EAM_ASSET_FAILURE_CODES EAFC,
3072: EAM_ASSET_FAILURES EAF,
3073: WIP_DISCRETE_JOBS WDJ,
3074: CSI_ITEM_INSTANCES CII
3075: WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
3076: AND WDJ.STATUS_TYPE IN (4,5,12)
3077: AND EAF.SOURCE_TYPE = 1
3078: AND EAF.OBJECT_TYPE = 3
3539: EAF.FAILURE_DATE,
3540: EAFC.COMMENTS,
3541: DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY '||l_partition_by||' ORDER BY EAF.FAILURE_DATE ),
3542: NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
3543: FROM CSI_ITEM_INSTANCES CII1
3544: WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
3545: AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
3546: (EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
3547: ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
3554: '||'''Y'''||' INCLUDE_FOR_COST_AGGR ';
3555:
3556: l_sql_stmt2 := ' FROM WIP_DISCRETE_JOBS WDJ,
3557: WIP_ENTITIES WE,
3558: CSI_ITEM_INSTANCES CII,
3559: MTL_CATEGORIES_KFV MCKFV,
3560: MTL_SYSTEM_ITEMS_KFV MSIKFV,
3561: MTL_EAM_LOCATIONS MEL,
3562: BOM_DEPARTMENTS BD,
3580: AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
3581: AND EAF.OBJECT_ID IN';
3582:
3583: l_sql_stmt3 := '(SELECT CII.INSTANCE_ID
3584: FROM CSI_ITEM_INSTANCES CII,
3585: MTL_SERIAL_NUMBERS MSN
3586: WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
3587: AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
3588: AND MSN.GEN_OBJECT_ID IN
3609: ERC.DESCRIPTION RESOLUTION_DESC,
3610: EAF.FAILURE_DATE,
3611: EAFC.COMMENTS
3612: FROM WIP_DISCRETE_JOBS WDJ,
3613: CSI_ITEM_INSTANCES CII,
3614: MTL_CATEGORIES_KFV MCKFV,
3615: MTL_SYSTEM_ITEMS_KFV MSIKFV,
3616: MTL_EAM_LOCATIONS MEL,
3617: BOM_DEPARTMENTS BD,
3657: MTL_CATEGORIES_KFV MCKFV,
3658: MTL_SYSTEM_ITEMS_KFV MSIKFV,
3659: MTL_EAM_LOCATIONS MEL,
3660: BOM_DEPARTMENTS BD,
3661: CSI_ITEM_INSTANCES CII,
3662: (SELECT * FROM EAM_ORG_MAINT_DEFAULTS WHERE ORGANIZATION_ID= '||l_org_id|| ' ) EOMD1 ' ||
3663: ' WHERE CII.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID
3664: AND CII.LAST_VLD_ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID
3665: AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
3742: l_first_tbf_calc_clause := 'CII.CREATION_DATE';
3743: ELSIF p_view_by = 2 THEN
3744: l_partition_by := 'CII.INVENTORY_ITEM_ID';
3745: l_first_tbf_calc_clause := '(SELECT MIN(CII1.CREATION_DATE)
3746: FROM CSI_ITEM_INSTANCES CII1
3747: WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
3748: AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID)';
3749: END IF;
3750:
3788: '||'''Y'''||' INCLUDE_FOR_READING_AGGR,
3789: '||'''Y'''||' INCLUDE_FOR_COST_AGGR
3790: FROM WIP_DISCRETE_JOBS WDJ,
3791: WIP_ENTITIES WE,
3792: CSI_ITEM_INSTANCES CII,
3793: MTL_CATEGORIES_KFV MCKFV,
3794: MTL_SYSTEM_ITEMS_KFV MSIKFV,
3795: MTL_EAM_LOCATIONS MEL,
3796: BOM_DEPARTMENTS BD,
3848: AND EAF.OBJECT_ID = METER.MAINTENANCE_OBJECT_ID (+)
3849: AND METER.WIP_ENTITY_ID (+) = EAF.SOURCE_ID
3850: AND EAF.OBJECT_ID IN ';
3851: l_sql_stmt3 := '( SELECT CII.INSTANCE_ID
3852: FROM CSI_ITEM_INSTANCES CII,
3853: MTL_SERIAL_NUMBERS MSN
3854: WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
3855: AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
3856: AND MSN.gen_object_id IN
3878: EAF.FAILURE_DATE,
3879: EAFC.COMMENTS
3880:
3881: FROM WIP_DISCRETE_JOBS WDJ,
3882: CSI_ITEM_INSTANCES CII,
3883: MTL_CATEGORIES_KFV MCKFV,
3884: MTL_SYSTEM_ITEMS_KFV MSIKFV,
3885: MTL_EAM_LOCATIONS MEL,
3886: BOM_DEPARTMENTS BD,
3927: MTL_CATEGORIES_KFV MCKFV,
3928: MTL_SYSTEM_ITEMS_KFV MSIKFV,
3929: MTL_EAM_LOCATIONS MEL,
3930: BOM_DEPARTMENTS BD,
3931: CSI_ITEM_INSTANCES CII,
3932: (SELECT * FROM EAM_ORG_MAINT_DEFAULTS WHERE ORGANIZATION_ID= '||l_org_id|| ' ) EOMD1 ' ||
3933: ' WHERE CII.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID
3934: AND CII.LAST_VLD_ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID
3935: AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID