[Home] [Help]
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:
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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
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)
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: ) ;
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,
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,
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
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)
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: ) ;
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,
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
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
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,
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
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
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,
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,
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
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)
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: ) ;
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,
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,
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
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)
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: ) ;
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,
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
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
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
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,
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
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
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,
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
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)
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,
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: )));
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,
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
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)
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,
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: )));
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,
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
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,
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
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,
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,
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
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)
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: ) ;
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,
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,
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
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)
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: ) ;
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,
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,
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
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,
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,
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
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,
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,
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
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,
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
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:
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,
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
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,
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