VIEW: APPS.EAM_FAILUREINFO_V

Source

View Text - Preformatted

SELECT EWD.WIP_ENTITY_ID AS WIP_ENTITY_ID, EAF.FAILURE_ID AS FAILURE_ID, EAFC.FAILURE_ENTRY_ID AS FAILURE_ENTRY_ID, EAFC.FAILURE_CODE AS FAILURE_CODE, NVL(EFC.DESCRIPTION, EFC.FAILURE_CODE) AS FAILURE_DESCRIPTION, EAFC.CAUSE_CODE AS CAUSE_CODE, NVL(ECC.DESCRIPTION, ECC.CAUSE_CODE) AS CAUSE_DESCRIPTION, EAFC.RESOLUTION_CODE AS RESOLUTION_CODE, NVL(ERC.DESCRIPTION, ERC.RESOLUTION_CODE) AS RESOLUTION_DESCRIPTION, EWD.FAILURE_CODE_REQUIRED AS FAILURE_CODE_REQUIRED, EAF.FAILURE_DATE AS FAILURE_DATE, EAFC.COMMENTS AS COMMENTS, NVL(WDJ.REBUILD_ITEM_ID, WDJ.ASSET_GROUP_ID) AS INVENTORY_ITEM_ID, ( SELECT SET_ID FROM EAM_FAILURE_SET_ASSOCIATIONS EFSA WHERE EFSA.INVENTORY_ITEM_ID = NVL( WDJ.REBUILD_ITEM_ID,WDJ.ASSET_GROUP_ID) AND EFSA.EFFECTIVE_END_DATE IS NULL AND SYSDATE <= ( select min(nvl(EFS.EFFECTIVE_END_DATE, SYSDATE)) from EAM_FAILURE_SETS EFS where nvl(EFS.EFFECTIVE_END_DATE, SYSDATE) >= sysdate and EFS.SET_ID = EFSA.SET_ID ) ) AS SET_ID, ( SELECT SET_NAME FROM EAM_FAILURE_SETS EFS2 WHERE SET_ID = ( SELECT SET_ID FROM EAM_FAILURE_SET_ASSOCIATIONS EFSA WHERE EFSA.INVENTORY_ITEM_ID = NVL( WDJ.REBUILD_ITEM_ID,WDJ.ASSET_GROUP_ID) AND EFSA.EFFECTIVE_END_DATE IS NULL AND SYSDATE <= ( select min(nvl(EFS.EFFECTIVE_END_DATE, SYSDATE)) from EAM_FAILURE_SETS EFS where nvl(EFS.EFFECTIVE_END_DATE, SYSDATE) >= sysdate and EFS.SET_ID = EFSA.SET_ID ) ) ) AS SET_NAME, WDJ.STATUS_TYPE, WDJ.CREATION_DATE from EAM_ASSET_FAILURES EAF, EAM_ASSET_FAILURE_CODES EAFC, WIP_DISCRETE_JOBS WDJ, EAM_WORK_ORDER_DETAILS EWD, EAM_FAILURE_CODES EFC, EAM_CAUSE_CODES ECC, EAM_RESOLUTION_CODES ERC WHERE WDJ.WIP_ENTITY_ID = EWD.WIP_ENTITY_ID and EWD.WIP_ENTITY_ID = EAF.SOURCE_ID(+) and EAF.FAILURE_ID = EAFC.FAILURE_ID(+) and EAFC.FAILURE_CODE = EFC.FAILURE_CODE (+) and EAFC.CAUSE_CODE = ECC.CAUSE_CODE (+) and EAFC.RESOLUTION_CODE = ERC.RESOLUTION_CODE (+)

View Text - HTML Formatted

SELECT EWD.WIP_ENTITY_ID AS WIP_ENTITY_ID
, EAF.FAILURE_ID AS FAILURE_ID
, EAFC.FAILURE_ENTRY_ID AS FAILURE_ENTRY_ID
, EAFC.FAILURE_CODE AS FAILURE_CODE
, NVL(EFC.DESCRIPTION
, EFC.FAILURE_CODE) AS FAILURE_DESCRIPTION
, EAFC.CAUSE_CODE AS CAUSE_CODE
, NVL(ECC.DESCRIPTION
, ECC.CAUSE_CODE) AS CAUSE_DESCRIPTION
, EAFC.RESOLUTION_CODE AS RESOLUTION_CODE
, NVL(ERC.DESCRIPTION
, ERC.RESOLUTION_CODE) AS RESOLUTION_DESCRIPTION
, EWD.FAILURE_CODE_REQUIRED AS FAILURE_CODE_REQUIRED
, EAF.FAILURE_DATE AS FAILURE_DATE
, EAFC.COMMENTS AS COMMENTS
, NVL(WDJ.REBUILD_ITEM_ID
, WDJ.ASSET_GROUP_ID) AS INVENTORY_ITEM_ID
, ( SELECT SET_ID
FROM EAM_FAILURE_SET_ASSOCIATIONS EFSA
WHERE EFSA.INVENTORY_ITEM_ID = NVL( WDJ.REBUILD_ITEM_ID
, WDJ.ASSET_GROUP_ID)
AND EFSA.EFFECTIVE_END_DATE IS NULL
AND SYSDATE <= ( SELECT MIN(NVL(EFS.EFFECTIVE_END_DATE
, SYSDATE))
FROM EAM_FAILURE_SETS EFS
WHERE NVL(EFS.EFFECTIVE_END_DATE
, SYSDATE) >= SYSDATE
AND EFS.SET_ID = EFSA.SET_ID ) ) AS SET_ID
, ( SELECT SET_NAME
FROM EAM_FAILURE_SETS EFS2
WHERE SET_ID = ( SELECT SET_ID
FROM EAM_FAILURE_SET_ASSOCIATIONS EFSA
WHERE EFSA.INVENTORY_ITEM_ID = NVL( WDJ.REBUILD_ITEM_ID
, WDJ.ASSET_GROUP_ID)
AND EFSA.EFFECTIVE_END_DATE IS NULL
AND SYSDATE <= ( SELECT MIN(NVL(EFS.EFFECTIVE_END_DATE
, SYSDATE))
FROM EAM_FAILURE_SETS EFS
WHERE NVL(EFS.EFFECTIVE_END_DATE
, SYSDATE) >= SYSDATE
AND EFS.SET_ID = EFSA.SET_ID ) ) ) AS SET_NAME
, WDJ.STATUS_TYPE
, WDJ.CREATION_DATE
FROM EAM_ASSET_FAILURES EAF
, EAM_ASSET_FAILURE_CODES EAFC
, WIP_DISCRETE_JOBS WDJ
, EAM_WORK_ORDER_DETAILS EWD
, EAM_FAILURE_CODES EFC
, EAM_CAUSE_CODES ECC
, EAM_RESOLUTION_CODES ERC
WHERE WDJ.WIP_ENTITY_ID = EWD.WIP_ENTITY_ID
AND EWD.WIP_ENTITY_ID = EAF.SOURCE_ID(+)
AND EAF.FAILURE_ID = EAFC.FAILURE_ID(+)
AND EAFC.FAILURE_CODE = EFC.FAILURE_CODE (+)
AND EAFC.CAUSE_CODE = ECC.CAUSE_CODE (+)
AND EAFC.RESOLUTION_CODE = ERC.RESOLUTION_CODE (+)