View: MTL_EAM_ASSET_NUMBERS_ALL_V

Product:	INV - Inventory
Description:	View for Asset Number details
Implementation/DBA Data:	APPS.MTL_EAM_ASSET_NUMBERS_ALL_V

View Text

SELECT CII.ROWID ROW_ID
, MSN.INVENTORY_ITEM_ID
, MSN.SERIAL_NUMBER
, NVL(CII.INSTANCE_DESCRIPTION
, MSN.DESCRIPTIVE_TEXT) AS DESCRIPTIVE_TEXT
, CII.INV_SUBINVENTORY_NAME CURRENT_SUBINVENTORY_CODE
, CII.INV_LOCATOR_ID AS CURRENT_LOCATOR_ID
, CII.NETWORK_ASSET_FLAG NETWORK_ASSET_FLAG
, CII.CONTEXT
, CII.ATTRIBUTE1
, CII.ATTRIBUTE2
, CII.ATTRIBUTE3
, CII.ATTRIBUTE4
, CII.ATTRIBUTE5
, CII.ATTRIBUTE6
, CII.ATTRIBUTE7
, CII.ATTRIBUTE8
, CII.ATTRIBUTE9
, CII.ATTRIBUTE10
, CII.ATTRIBUTE11
, CII.ATTRIBUTE12
, CII.ATTRIBUTE13
, CII.ATTRIBUTE14
, CII.ATTRIBUTE15
, CII.ATTRIBUTE16
, CII.ATTRIBUTE17
, CII.ATTRIBUTE18
, CII.ATTRIBUTE19
, CII.ATTRIBUTE20
, CII.ATTRIBUTE21
, CII.ATTRIBUTE22
, CII.ATTRIBUTE23
, CII.ATTRIBUTE24
, CII.ATTRIBUTE25
, CII.ATTRIBUTE26
, CII.ATTRIBUTE27
, CII.ATTRIBUTE28
, CII.ATTRIBUTE29
, CII.ATTRIBUTE30
, MP.MAINT_ORGANIZATION_ID CURRENT_ORGANIZATION_ID
, MP.ORGANIZATION_CODE INV_ORGANIZATION_CODE
, MSI.CONCATENATED_SEGMENTS
, MSI.EAM_ITEM_TYPE
, MSI.DESCRIPTION ASSET_GROUP_DESCRIPTION
, CII.CATEGORY_ID CATEGORY_ID
, MCK.CONCATENATED_SEGMENTS CATEGORY_NAME
, MCT.DESCRIPTION CATEGORY_DESCRIPTION
, CII.PN_LOCATION_ID PN_LOCATION_ID
, PL.BUILDING||PL.FLOOR||PL.OFFICE PN_LOCATION_NAME
, PL.LOCATION_CODE PN_LOCATION_CODE
, CII.ASSET_CRITICALITY_CODE ASSET_CRITICALITY_CODE
, ML1.MEANING ASSET_CRITICALITY
, EOMD.ACCOUNTING_CLASS_CODE WIP_ACCOUNTING_CLASS_CODE
, NVL(CII.MAINTAINABLE_FLAG
, 'Y') MAINTAINABLE_FLAG
, EOMD.AREA_ID
, EL.LOCATION_CODES AREA
, EOMD.OWNING_DEPARTMENT_ID
, BD.DEPARTMENT_CODE OWNING_DEPARTMENT
, MP_PROD.ORGANIZATION_CODE PROD_ORGANIZATION_CODE
, MSN_PROD.CURRENT_ORGANIZATION_ID PROD_ORGANIZATION_ID
, MSI_PROD.INVENTORY_ITEM_ID EQUIPMENT_ITEM_ID
, DECODE(MSI_PROD.EQUIPMENT_TYPE
, NULL
, NULL
, 1
, MSN_PROD.SERIAL_NUMBER
, NULL) EQP_SERIAL_NUMBER
, MSN_PROD.GEN_OBJECT_ID EQUIPMENT_GEN_OBJECT_ID
, CIA.FA_ASSET_ID
, FA.ASSET_CATEGORY_ID
, FA.ASSET_NUMBER FA_ASSET_NUMBER
, MSN_PARENT.INVENTORY_ITEM_ID PARENT_ITEM_ID
, MSN_PARENT.SERIAL_NUMBER PARENT_SERIAL_NUMBER
, MSN_PARENT.GEN_OBJECT_ID PARENT_GEN_OBJECT_ID
, CII2.INSTANCE_NUMBER PARENT_INSTANCE_NUMBER
, CII.SERIAL_NUMBER MAINTAINED_UNIT
, CII.INSTANCE_NUMBER
, NVL(CII.LAST_VLD_ORGANIZATION_ID
, MSN.CURRENT_ORGANIZATION_ID) INV_ORGANIZATION_ID
, 3 MAINTENANCE_OBJECT_TYPE
, CII.INSTANCE_ID MAINTENANCE_OBJECT_ID
, MSN.GEN_OBJECT_ID
, MSN.CURRENT_STATUS
, CII.LOCATION_TYPE_CODE
, CII.LOCATION_ID
, DECODE(CII.LOCATION_TYPE_CODE
, 'HZ_PARTY_SITES'
, (SELECT HZ.ADDRESS1||'
, '||HZ.ADDRESS2||'
, '||HZ.ADDRESS3||'
, '||HZ.ADDRESS4||'
, '||HZ.CITY||'
, '||HZ.STATE||'
, '||HZ.POSTAL_CODE||'
, '||HZ.COUNTRY
FROM HZ_LOCATIONS HZ
, HZ_PARTY_SITES HPS
WHERE HZ.LOCATION_ID = HPS.LOCATION_ID
AND HPS.PARTY_SITE_ID = CII.LOCATION_ID)
, 'HZ_LOCATIONS'
, (SELECT HZ.ADDRESS1||'
, '||HZ.ADDRESS2||'
, '||HZ.ADDRESS3||'
, '||HZ.ADDRESS4||'
, '||HZ.CITY||'
, '||HZ.STATE||'
, '||HZ.POSTAL_CODE||'
, '||HZ.COUNTRY
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID = CII.LOCATION_ID)
, 'VENDOR_SITE'
, (SELECT PVS.ADDRESS_LINE1||'
, '||PVS.ADDRESS_LINE2||'
, '||PVS.ADDRESS_LINE3||'
, '||PVS.CITY||'
, '||PVS.STATE||'
, '||PVS.ZIP||'
, '||PVS.COUNTRY
FROM PO_VENDOR_SITES_ALL PVS
WHERE PVS.VENDOR_SITE_ID = CII.LOCATION_ID)
, 'INVENTORY'
, (SELECT HRL.ADDRESS_LINE_1||'
, '||HRL.ADDRESS_LINE_2||'
, '||HRL.ADDRESS_LINE_3||'
, '||HRL.REGION_1||'
, '||HRL.POSTAL_CODE||'
, '||HRL.COUNTRY
FROM HR_LOCATIONS_ALL HRL
WHERE HRL.LOCATION_ID = CII.LOCATION_ID)
, 'INTERNAL_SITE'
, (SELECT HRL.ADDRESS_LINE_1||'
, '||HRL.ADDRESS_LINE_2||'
, '||HRL.ADDRESS_LINE_3||'
, '||HRL.REGION_1||'
, '||HRL.POSTAL_CODE||'
, '||HRL.COUNTRY
FROM HR_LOCATIONS_ALL HRL
WHERE HRL.LOCATION_ID = CII.LOCATION_ID)
, 'WIP'
, (SELECT HZ.ADDRESS1||'
, '||HZ.ADDRESS2||'
, '||HZ.ADDRESS3||'
, '||HZ.ADDRESS4||'
, '||HZ.CITY||'
, '||HZ.STATE||'
, '||HZ.POSTAL_CODE||'
, '||HZ.COUNTRY
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID = CII.LOCATION_ID)
, 'PROJECT'
, (SELECT HZ.ADDRESS1||'
, '||HZ.ADDRESS2||'
, '||HZ.ADDRESS3||'
, '||HZ.ADDRESS4||'
, '||HZ.CITY||'
, '||HZ.STATE||'
, '||HZ.POSTAL_CODE||'
, '||HZ.COUNTRY
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID = CII.LOCATION_ID)
, 'IN_TRANSIT'
, (SELECT HZ.ADDRESS1||'
, '||HZ.ADDRESS2||'
, '||HZ.ADDRESS3||'
, '||HZ.ADDRESS4||'
, '||HZ.CITY||'
, '||HZ.STATE||'
, '||HZ.POSTAL_CODE||'
, '||HZ.COUNTRY
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID = CII.LOCATION_ID)
, 'PO'
, (SELECT HZ.ADDRESS1||'
, '||HZ.ADDRESS2||'
, '||HZ.ADDRESS3||'
, '||HZ.ADDRESS4||'
, '||HZ.CITY||'
, '||HZ.STATE||'
, '||HZ.POSTAL_CODE||'
, '||HZ.COUNTRY
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID = CII.LOCATION_ID)
, NULL) CURRENT_LOCATION
, CII.CREATED_BY
, CII.CREATION_DATE
, CII.LAST_UPDATED_BY
, CII.LAST_UPDATE_DATE
, CII.LAST_UPDATE_LOGIN
, CII.SUPPLIER_WARRANTY_EXP_DATE
, CII.CHECKIN_STATUS
, CII.OPERATIONAL_LOG_FLAG
, ML2.MEANING CURRENT_STATUS_MEANING
, CII.ACTIVE_START_DATE
, CII.ACTIVE_END_DATE
, CIA.FA_SYNC_FLAG
, MSN_PARENT.CURRENT_ORGANIZATION_ID PARENT_ORGANIZATION_ID
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_DEGREES_FROM_DD(GEO.INST_LATITUDE) LAT_DEGREES
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_MINUTES_FROM_DD(GEO.INST_LATITUDE) LAT_MINUTES
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_SECONDS_FROM_DD(GEO.INST_LATITUDE) LAT_SECONDS
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_DIRECTION_FROM_DD('LAT'
, GEO.INST_LATITUDE) LAT_DIRECTION
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_DEGREES_FROM_DD(GEO.INST_LONGITUDE) LONG_DEGREES
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_MINUTES_FROM_DD(GEO.INST_LONGITUDE) LONG_MINUTES
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_SECONDS_FROM_DD(GEO.INST_LONGITUDE) LONG_SECONDS
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_DIRECTION_FROM_DD('LON'
, GEO.INST_LONGITUDE) LONG_DIRECTION
, SAF.EAM_SAFETY_TYPE SAFETY_TYPE
, SAF.OPERATING_POSITION OPERATING_POSITION
, (SELECT MEANING
FROM MFG_LOOKUPS
WHERE LOOKUP_CODE=SAF.OPERATING_POSITION
AND LOOKUP_TYPE='EAM_TAG_POSITIONS') OPERATING_POSITION_DISP
, SAF.OPERATING_TAG OPERATING_TAG
, (SELECT MEANING
FROM MFG_LOOKUPS
WHERE LOOKUP_CODE=SAF.OPERATING_TAG
AND LOOKUP_TYPE='EAM_SAFETY_TAGS') OPERATING_TAG_DISP
, SAF.SHUTDOWN_POSITION SHUTDOWN_POSITION
, (SELECT MEANING
FROM MFG_LOOKUPS
WHERE LOOKUP_CODE=SAF.SHUTDOWN_POSITION
AND LOOKUP_TYPE='EAM_TAG_POSITIONS') SHUTDOWN_POSITION_DISP
, SAF.SHUTDOWN_TAG SHUTDOWN_TAG
, (SELECT MEANING
FROM MFG_LOOKUPS
WHERE LOOKUP_CODE=SAF.SHUTDOWN_TAG
AND LOOKUP_TYPE='EAM_SAFETY_TAGS') SHUTDOWN_TAG_DISP
, SAF.POS_LOCKOUT_DEVICE POS_LOCKOUT_DEVICE
, (SELECT MEANING
FROM MFG_LOOKUPS
WHERE LOOKUP_CODE=SAF.POS_LOCKOUT_DEVICE
AND LOOKUP_TYPE='EAM_LOCKOUT_DEVICE') POS_LOCKOUT_DEVICE_DISP
, SAF.LOCKOUT_DEVICE LOCKOUT_DEVICE
, (SELECT MEANING
FROM MFG_LOOKUPS
WHERE LOOKUP_CODE=SAF.LOCKOUT_DEVICE
AND LOOKUP_TYPE='EAM_LOCKOUT_DEVICE') LOCKOUT_DEVICE_DISP
FROM MTL_PARAMETERS MP
, CSI_ITEM_INSTANCES CII
, EAM_ORG_MAINT_DEFAULTS EOMD
, CSI_I_ASSETS CIA
, MFG_LOOKUPS ML1
, BOM_DEPARTMENTS BD
, PN_LOCATIONS_ALL PL
, MTL_EAM_LOCATIONS EL
, MTL_CATEGORIES_KFV MCK
, MTL_CATEGORIES_TL MCT
, MTL_SYSTEM_ITEMS_B_KFV MSI
, MTL_SYSTEM_ITEMS MSI_PROD
, MTL_SERIAL_NUMBERS MSN_PROD
, MTL_PARAMETERS MP_PROD
, FA_ADDITIONS_B FA
, MTL_OBJECT_GENEALOGY MOG
, MTL_SERIAL_NUMBERS MSN
, MTL_SERIAL_NUMBERS MSN_PARENT
, CSI_ITEM_INSTANCES CII2
, MFG_LOOKUPS ML2
, CSI_II_GEOLOCATIONS GEO
, EAM_ASSET_SAFETY_TYPES SAF
WHERE MP.ORGANIZATION_ID = MSN.CURRENT_ORGANIZATION_ID
AND MSN.CURRENT_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND MSI.EAM_ITEM_TYPE IN (1
, 3)
AND MSI.SERIAL_NUMBER_CONTROL_CODE <> 1
AND MSN.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID(+)
AND MSN.SERIAL_NUMBER = CII.SERIAL_NUMBER(+)
AND MSN.GEN_OBJECT_ID = MOG.OBJECT_ID(+)
AND CII.INSTANCE_ID = EOMD.OBJECT_ID (+)
AND EOMD.OBJECT_TYPE = 50
AND (EOMD.ORGANIZATION_ID = MP.MAINT_ORGANIZATION_ID)
AND CII.ASSET_CRITICALITY_CODE = ML1.LOOKUP_CODE(+)
AND ML1.LOOKUP_TYPE(+) = 'MTL_EAM_ASSET_CRITICALITY'
AND EOMD.OWNING_DEPARTMENT_ID = BD.DEPARTMENT_ID (+)
AND CII.PN_LOCATION_ID = PL.LOCATION_ID(+)
AND SYSDATE >= NVL(PL.ACTIVE_START_DATE(+)
, SYSDATE)
AND SYSDATE <= NVL(PL.ACTIVE_END_DATE(+)
, SYSDATE)
AND EOMD.AREA_ID = EL.LOCATION_ID(+)
AND CII.CATEGORY_ID = MCK.CATEGORY_ID(+)
AND CII.EQUIPMENT_GEN_OBJECT_ID = MSN_PROD.GEN_OBJECT_ID(+)
AND MSN_PROD.CURRENT_ORGANIZATION_ID = MSI_PROD.ORGANIZATION_ID(+)
AND MSN_PROD.INVENTORY_ITEM_ID = MSI_PROD.INVENTORY_ITEM_ID(+)
AND MSI_PROD.ORGANIZATION_ID = MP_PROD.ORGANIZATION_ID(+)
AND MSI_PROD.EQUIPMENT_TYPE(+) = 1
AND CII.INSTANCE_ID = CIA.INSTANCE_ID(+)
AND CIA.FA_ASSET_ID = FA.ASSET_ID(+)
AND CII.INSTANCE_ID = GEO.INSTANCE_ID(+)
AND GEO.VALID_FLAG(+) ='Y'
AND CII.INSTANCE_ID = SAF.INSTANCE_ID(+)
AND SYSDATE >= NVL(CIA.ACTIVE_START_DATE(+)
, SYSDATE)
AND SYSDATE <= NVL(CIA.ACTIVE_END_DATE(+)
, SYSDATE)
AND MOG.PARENT_OBJECT_ID = MSN_PARENT.GEN_OBJECT_ID(+)
AND MOG.GENEALOGY_TYPE(+) = 5
AND SYSDATE >= NVL(MOG.START_DATE_ACTIVE(+)
, SYSDATE)
AND SYSDATE <= NVL(MOG.END_DATE_ACTIVE(+)
, SYSDATE)
AND MSN_PARENT.INVENTORY_ITEM_ID = CII2.INVENTORY_ITEM_ID (+)
AND MSN_PARENT.SERIAL_NUMBER = CII2.SERIAL_NUMBER (+)
AND ML2.LOOKUP_TYPE(+) = 'SERIAL_NUM_STATUS'
AND ML2.LOOKUP_CODE(+) = MSN.CURRENT_STATUS
AND MCK.CATEGORY_ID = MCT.CATEGORY_ID(+)
AND MCT.LANGUAGE(+) = USERENV('LANG') UNION ALL SELECT CII.ROWID ROW_ID
, MSN.INVENTORY_ITEM_ID
, MSN.SERIAL_NUMBER
, NVL(CII.INSTANCE_DESCRIPTION
, MSN.DESCRIPTIVE_TEXT) AS DESCRIPTIVE_TEXT
, CII.INV_SUBINVENTORY_NAME CURRENT_SUBINVENTORY_CODE
, CII.INV_LOCATOR_ID AS CURRENT_LOCATOR_ID
, CII.NETWORK_ASSET_FLAG NETWORK_ASSET_FLAG
, CII.CONTEXT
, CII.ATTRIBUTE1
, CII.ATTRIBUTE2
, CII.ATTRIBUTE3
, CII.ATTRIBUTE4
, CII.ATTRIBUTE5
, CII.ATTRIBUTE6
, CII.ATTRIBUTE7
, CII.ATTRIBUTE8
, CII.ATTRIBUTE9
, CII.ATTRIBUTE10
, CII.ATTRIBUTE11
, CII.ATTRIBUTE12
, CII.ATTRIBUTE13
, CII.ATTRIBUTE14
, CII.ATTRIBUTE15
, CII.ATTRIBUTE16
, CII.ATTRIBUTE17
, CII.ATTRIBUTE18
, CII.ATTRIBUTE19
, CII.ATTRIBUTE20
, CII.ATTRIBUTE21
, CII.ATTRIBUTE22
, CII.ATTRIBUTE23
, CII.ATTRIBUTE24
, CII.ATTRIBUTE25
, CII.ATTRIBUTE26
, CII.ATTRIBUTE27
, CII.ATTRIBUTE28
, CII.ATTRIBUTE29
, CII.ATTRIBUTE30
, MP.MAINT_ORGANIZATION_ID CURRENT_ORGANIZATION_ID
, MP.ORGANIZATION_CODE INV_ORGANIZATION_CODE
, MSI.CONCATENATED_SEGMENTS
, MSI.EAM_ITEM_TYPE
, MSI.DESCRIPTION ASSET_GROUP_DESCRIPTION
, CII.CATEGORY_ID CATEGORY_ID
, MCK.CONCATENATED_SEGMENTS CATEGORY_NAME
, MCT.DESCRIPTION CATEGORY_DESCRIPTION
, CII.PN_LOCATION_ID PN_LOCATION_ID
, PL.BUILDING||PL.FLOOR||PL.OFFICE PN_LOCATION_NAME
, PL.LOCATION_CODE PN_LOCATION_CODE
, CII.ASSET_CRITICALITY_CODE ASSET_CRITICALITY_CODE
, ML1.MEANING ASSET_CRITICALITY
, NULL WIP_ACCOUNTING_CLASS_CODE
, NVL(CII.MAINTAINABLE_FLAG
, 'Y') MAINTAINABLE_FLAG
, NULL
, NULL AREA
, NULL
, NULL OWNING_DEPARTMENT
, MP_PROD.ORGANIZATION_CODE PROD_ORGANIZATION_CODE
, MSN_PROD.CURRENT_ORGANIZATION_ID PROD_ORGANIZATION_ID
, MSI_PROD.INVENTORY_ITEM_ID EQUIPMENT_ITEM_ID
, DECODE(MSI_PROD.EQUIPMENT_TYPE
, NULL
, NULL
, 1
, MSN_PROD.SERIAL_NUMBER
, NULL) EQP_SERIAL_NUMBER
, MSN_PROD.GEN_OBJECT_ID EQUIPMENT_GEN_OBJECT_ID
, CIA.FA_ASSET_ID
, FA.ASSET_CATEGORY_ID
, FA.ASSET_NUMBER FA_ASSET_NUMBER
, MSN_PARENT.INVENTORY_ITEM_ID PARENT_ITEM_ID
, MSN_PARENT.SERIAL_NUMBER PARENT_SERIAL_NUMBER
, MSN_PARENT.GEN_OBJECT_ID PARENT_GEN_OBJECT_ID
, CII2.INSTANCE_NUMBER PARENT_INSTANCE_NUMBER
, CII.SERIAL_NUMBER MAINTAINED_UNIT
, CII.INSTANCE_NUMBER
, NVL(CII.LAST_VLD_ORGANIZATION_ID
, MSN.CURRENT_ORGANIZATION_ID) INV_ORGANIZATION_ID
, 3 MAINTENANCE_OBJECT_TYPE
, CII.INSTANCE_ID MAINTENANCE_OBJECT_ID
, MSN.GEN_OBJECT_ID
, MSN.CURRENT_STATUS
, CII.LOCATION_TYPE_CODE
, CII.LOCATION_ID
, DECODE(CII.LOCATION_TYPE_CODE
, 'HZ_PARTY_SITES'
, (SELECT HZ.ADDRESS1||'
, '||HZ.ADDRESS2||'
, '||HZ.ADDRESS3||'
, '||HZ.ADDRESS4||'
, '||HZ.CITY||'
, '||HZ.STATE||'
, '||HZ.POSTAL_CODE||'
, '||HZ.COUNTRY
FROM HZ_LOCATIONS HZ
, HZ_PARTY_SITES HPS
WHERE HZ.LOCATION_ID = HPS.LOCATION_ID
AND HPS.PARTY_SITE_ID = CII.LOCATION_ID)
, 'HZ_LOCATIONS'
, (SELECT HZ.ADDRESS1||'
, '||HZ.ADDRESS2||'
, '||HZ.ADDRESS3||'
, '||HZ.ADDRESS4||'
, '||HZ.CITY||'
, '||HZ.STATE||'
, '||HZ.POSTAL_CODE||'
, '||HZ.COUNTRY
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID = CII.LOCATION_ID)
, 'VENDOR_SITE'
, (SELECT PVS.ADDRESS_LINE1||'
, '||PVS.ADDRESS_LINE2||'
, '||PVS.ADDRESS_LINE3||'
, '||PVS.CITY||'
, '||PVS.STATE||'
, '||PVS.ZIP||'
, '||PVS.COUNTRY
FROM PO_VENDOR_SITES_ALL PVS
WHERE PVS.VENDOR_SITE_ID = CII.LOCATION_ID)
, 'INVENTORY'
, (SELECT HRL.ADDRESS_LINE_1||'
, '||HRL.ADDRESS_LINE_2||'
, '||HRL.ADDRESS_LINE_3||'
, '||HRL.REGION_1||'
, '||HRL.POSTAL_CODE||'
, '||HRL.COUNTRY
FROM HR_LOCATIONS_ALL HRL
WHERE HRL.LOCATION_ID = CII.LOCATION_ID)
, 'INTERNAL_SITE'
, (SELECT HRL.ADDRESS_LINE_1||'
, '||HRL.ADDRESS_LINE_2||'
, '||HRL.ADDRESS_LINE_3||'
, '||HRL.REGION_1||'
, '||HRL.POSTAL_CODE||'
, '||HRL.COUNTRY
FROM HR_LOCATIONS_ALL HRL
WHERE HRL.LOCATION_ID = CII.LOCATION_ID)
, 'WIP'
, (SELECT HZ.ADDRESS1||'
, '||HZ.ADDRESS2||'
, '||HZ.ADDRESS3||'
, '||HZ.ADDRESS4||'
, '||HZ.CITY||'
, '||HZ.STATE||'
, '||HZ.POSTAL_CODE||'
, '||HZ.COUNTRY
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID = CII.LOCATION_ID)
, 'PROJECT'
, (SELECT HZ.ADDRESS1||'
, '||HZ.ADDRESS2||'
, '||HZ.ADDRESS3||'
, '||HZ.ADDRESS4||'
, '||HZ.CITY||'
, '||HZ.STATE||'
, '||HZ.POSTAL_CODE||'
, '||HZ.COUNTRY
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID = CII.LOCATION_ID)
, 'IN_TRANSIT'
, (SELECT HZ.ADDRESS1||'
, '||HZ.ADDRESS2||'
, '||HZ.ADDRESS3||'
, '||HZ.ADDRESS4||'
, '||HZ.CITY||'
, '||HZ.STATE||'
, '||HZ.POSTAL_CODE||'
, '||HZ.COUNTRY
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID = CII.LOCATION_ID)
, 'PO'
, (SELECT HZ.ADDRESS1||'
, '||HZ.ADDRESS2||'
, '||HZ.ADDRESS3||'
, '||HZ.ADDRESS4||'
, '||HZ.CITY||'
, '||HZ.STATE||'
, '||HZ.POSTAL_CODE||'
, '||HZ.COUNTRY
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID = CII.LOCATION_ID)
, NULL) CURRENT_LOCATION
, CII.CREATED_BY
, CII.CREATION_DATE
, CII.LAST_UPDATED_BY
, CII.LAST_UPDATE_DATE
, CII.LAST_UPDATE_LOGIN
, CII.SUPPLIER_WARRANTY_EXP_DATE
, CII.CHECKIN_STATUS
, CII.OPERATIONAL_LOG_FLAG
, ML2.MEANING CURRENT_STATUS_MEANING
, CII.ACTIVE_START_DATE
, CII.ACTIVE_END_DATE
, CIA.FA_SYNC_FLAG
, MSN_PARENT.CURRENT_ORGANIZATION_ID PARENT_ORGANIZATION_ID
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_DEGREES_FROM_DD(GEO.INST_LATITUDE) LAT_DEGREES
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_MINUTES_FROM_DD(GEO.INST_LATITUDE) LAT_MINUTES
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_SECONDS_FROM_DD(GEO.INST_LATITUDE) LAT_SECONDS
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_DIRECTION_FROM_DD('LAT'
, GEO.INST_LATITUDE) LAT_DIRECTION
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_DEGREES_FROM_DD(GEO.INST_LONGITUDE) LONG_DEGREES
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_MINUTES_FROM_DD(GEO.INST_LONGITUDE) LONG_MINUTES
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_SECONDS_FROM_DD(GEO.INST_LONGITUDE) LONG_SECONDS
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_DIRECTION_FROM_DD('LON'
, GEO.INST_LONGITUDE) LONG_DIRECTION
, SAF.EAM_SAFETY_TYPE SAFETY_TYPE
, SAF.OPERATING_POSITION OPERATING_POSITION
, (SELECT MEANING
FROM MFG_LOOKUPS
WHERE LOOKUP_CODE=SAF.OPERATING_POSITION
AND LOOKUP_TYPE='EAM_TAG_POSITIONS') OPERATING_POSITION_DISP
, SAF.OPERATING_TAG OPERATING_TAG
, (SELECT MEANING
FROM MFG_LOOKUPS
WHERE LOOKUP_CODE=SAF.OPERATING_TAG
AND LOOKUP_TYPE='EAM_SAFETY_TAGS') OPERATING_TAG_DISP
, SAF.SHUTDOWN_POSITION SHUTDOWN_POSITION
, (SELECT MEANING
FROM MFG_LOOKUPS
WHERE LOOKUP_CODE=SAF.SHUTDOWN_POSITION
AND LOOKUP_TYPE='EAM_TAG_POSITIONS') SHUTDOWN_POSITION_DISP
, SAF.SHUTDOWN_TAG SHUTDOWN_TAG
, (SELECT MEANING
FROM MFG_LOOKUPS
WHERE LOOKUP_CODE=SAF.SHUTDOWN_TAG
AND LOOKUP_TYPE='EAM_SAFETY_TAGS') SHUTDOWN_TAG_DISP
, SAF.POS_LOCKOUT_DEVICE POS_LOCKOUT_DEVICE
, (SELECT MEANING
FROM MFG_LOOKUPS
WHERE LOOKUP_CODE=SAF.POS_LOCKOUT_DEVICE
AND LOOKUP_TYPE='EAM_LOCKOUT_DEVICE') POS_LOCKOUT_DEVICE_DISP
, SAF.LOCKOUT_DEVICE LOCKOUT_DEVICE
, (SELECT MEANING
FROM MFG_LOOKUPS
WHERE LOOKUP_CODE=SAF.LOCKOUT_DEVICE
AND LOOKUP_TYPE='EAM_LOCKOUT_DEVICE') LOCKOUT_DEVICE_DISP FROM MTL_PARAMETERS MP
, CSI_ITEM_INSTANCES CII
, CSI_I_ASSETS CIA
, MFG_LOOKUPS ML1
, PN_LOCATIONS_ALL PL
, MTL_CATEGORIES_KFV MCK
, MTL_CATEGORIES_TL MCT
, MTL_SYSTEM_ITEMS_B_KFV MSI
, MTL_SERIAL_NUMBERS MSN_PROD
, MTL_SYSTEM_ITEMS MSI_PROD
, MTL_PARAMETERS MP_PROD
, FA_ADDITIONS_B FA
, MTL_OBJECT_GENEALOGY MOG
, MTL_SERIAL_NUMBERS MSN
, MTL_SERIAL_NUMBERS MSN_PARENT
, CSI_ITEM_INSTANCES CII2
, MFG_LOOKUPS ML2
, CSI_II_GEOLOCATIONS GEO
, EAM_ASSET_SAFETY_TYPES SAF WHERE MP.ORGANIZATION_ID = MSN.CURRENT_ORGANIZATION_ID
AND MSN.CURRENT_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND MSI.EAM_ITEM_TYPE IN (1
, 3)
AND MSI.SERIAL_NUMBER_CONTROL_CODE <>1
AND MSN.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID(+)
AND MSN.SERIAL_NUMBER = CII.SERIAL_NUMBER(+)
AND MSN.GEN_OBJECT_ID = MOG.OBJECT_ID(+)
AND CII.ASSET_CRITICALITY_CODE = ML1.LOOKUP_CODE(+)
AND ML1.LOOKUP_TYPE(+) = 'MTL_EAM_ASSET_CRITICALITY'
AND CII.PN_LOCATION_ID = PL.LOCATION_ID(+)
AND CII.CATEGORY_ID = MCK.CATEGORY_ID(+)
AND CII.EQUIPMENT_GEN_OBJECT_ID = MSN_PROD.GEN_OBJECT_ID(+)
AND MSN_PROD.CURRENT_ORGANIZATION_ID = MSI_PROD.ORGANIZATION_ID(+)
AND MSN_PROD.INVENTORY_ITEM_ID = MSI_PROD.INVENTORY_ITEM_ID(+)
AND MSI_PROD.ORGANIZATION_ID = MP_PROD.ORGANIZATION_ID(+)
AND MSI_PROD.EQUIPMENT_TYPE(+) = 1
AND CII.INSTANCE_ID = CIA.INSTANCE_ID(+)
AND CIA.FA_ASSET_ID = FA.ASSET_ID(+)
AND CII.INSTANCE_ID = GEO.INSTANCE_ID(+)
AND GEO.VALID_FLAG(+) ='Y'
AND CII.INSTANCE_ID = SAF.INSTANCE_ID(+)
AND SYSDATE >= NVL(CIA.ACTIVE_START_DATE(+)
, SYSDATE)
AND SYSDATE <= NVL(CIA.ACTIVE_END_DATE(+)
, SYSDATE)
AND MOG.PARENT_OBJECT_ID = MSN_PARENT.GEN_OBJECT_ID(+)
AND MOG.GENEALOGY_TYPE(+) = 5
AND SYSDATE >= NVL(MOG.START_DATE_ACTIVE(+)
, SYSDATE)
AND SYSDATE <= NVL(MOG.END_DATE_ACTIVE(+)
, SYSDATE)
AND MSN_PARENT.INVENTORY_ITEM_ID = CII2.INVENTORY_ITEM_ID (+)
AND MSN_PARENT.SERIAL_NUMBER = CII2.SERIAL_NUMBER (+)
AND NOT EXISTS (SELECT 1
FROM EAM_ORG_MAINT_DEFAULTS EOMD
WHERE EOMD.OBJECT_ID = CII.INSTANCE_ID
AND EOMD.ORGANIZATION_ID = MP.MAINT_ORGANIZATION_ID
AND EOMD.OBJECT_TYPE = 50)
AND ML2.LOOKUP_TYPE(+) = 'SERIAL_NUM_STATUS'
AND ML2.LOOKUP_CODE(+) = MSN.CURRENT_STATUS
AND MCK.CATEGORY_ID = MCT.CATEGORY_ID(+)
AND MCT.LANGUAGE(+) = USERENV('LANG')

Columns