eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.MTL_EAM_ASSET_NUMBERS_V

Source

View Text - Preformatted


SELECT cii.rowid row_id, cii.inventory_item_id, cii.serial_number, cii.instance_description 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, msi.concatenated_segments, msi.eam_item_type, msi.description asset_group_description, cii.category_id category_id, mck.concatenated_segments category_name, cii.pn_location_id pn_location_id, cii.asset_criticality_code asset_criticality_code, ml.meaning asset_criticality, eomd.accounting_class_code wip_accounting_class_code, nvl(cii.maintainable_flag, 'Y') maintainable_flag, eomd.area_id, el.location_codes eam_location, eomd.owning_department_id owning_department_id, bd.department_code owning_department, 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, cia.fa_asset_id, cii.serial_number maintained_unit, cii.instance_number, cii.last_vld_organization_id inv_organization_id, 3 maintenance_object_type, cii.instance_id maintenance_object_id, cii.checkin_status, cii.CREATED_BY, cii.CREATION_DATE, cii.LAST_UPDATED_BY, cii.LAST_UPDATE_DATE, cii.LAST_UPDATE_LOGIN FROM mtl_parameters mp, csi_item_instances cii, eam_org_maint_defaults eomd, csi_i_assets cia, mfg_lookups ml, bom_departments bd, mtl_eam_locations el, mtl_categories_kfv mck, mtl_system_items_b_kfv msi, mtl_serial_numbers msn_prod, mtl_system_items msi_prod WHERE mp.organization_id = cii.last_vld_organization_id AND cii.last_vld_organization_id = msi.organization_id AND msi.inventory_item_id = cii.inventory_item_id AND msi.eam_item_type in (1,3) AND nvl(cii.active_start_date, sysdate-1) <= sysdate AND nvl(cii.active_end_date, sysdate+1) >= sysdate AND msi.serial_number_control_code <> 1 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 = ml.lookup_code(+) AND ml.lookup_type(+) = 'MTL_EAM_ASSET_CRITICALITY' AND bd.department_id(+) = eomd.owning_department_id 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.equipment_type(+) = 1 AND cii.instance_id = cia.instance_id(+) AND sysdate >= nvl(cia.active_start_date(+),sysdate) AND sysdate <= nvl(cia.active_end_date(+),sysdate) UNION ALL SELECT cii.rowid row_id, cii.inventory_item_id, cii.serial_number, cii.instance_description 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, msi.concatenated_segments, msi.eam_item_type, msi.description asset_group_description, cii.category_id category_id, mck.concatenated_segments category_name, cii.pn_location_id pn_location_id, cii.asset_criticality_code asset_criticality_code, ml.meaning asset_criticality, null wip_accounting_class_code, nvl(cii.maintainable_flag, 'Y') maintainable_flag, null, null eam_location, null owning_department_id, null owning_department, 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, cia.fa_asset_id, cii.serial_number maintained_unit, cii.instance_number, cii.last_vld_organization_id inv_organization_id, 3 maintenance_object_type, cii.instance_id maintenance_object_id, cii.checkin_status, cii.CREATED_BY, cii.CREATION_DATE, cii.LAST_UPDATED_BY, cii.LAST_UPDATE_DATE, cii.LAST_UPDATE_LOGIN FROM mtl_parameters mp, csi_item_instances cii, csi_i_assets cia, mfg_lookups ml, mtl_categories_kfv mck, mtl_system_items_b_kfv msi, mtl_serial_numbers msn_prod, mtl_system_items msi_prod WHERE mp.organization_id = cii.last_vld_organization_id AND cii.last_vld_organization_id = msi.organization_id AND msi.inventory_item_id = cii.inventory_item_id AND msi.eam_item_type in (1,3) AND nvl(cii.active_start_date, sysdate-1) <= sysdate AND nvl(cii.active_end_date, sysdate+1) >= sysdate AND msi.serial_number_control_code <> 1 AND cii.asset_criticality_code = ml.lookup_code(+) AND ml.lookup_type(+) = 'MTL_EAM_ASSET_CRITICALITY' 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.equipment_type(+) = 1 AND cii.instance_id = cia.instance_id(+) AND sysdate >= nvl(cia.active_start_date(+),sysdate) AND sysdate <= nvl(cia.active_end_date(+),sysdate) AND NOT EXISTS (SELECT 1 FROM eam_org_maint_defaults eomd WHERE eomd.organization_id = mp.maint_organization_id AND eomd.object_id = cii.instance_id AND eomd.object_type = 50)

View Text - HTML Formatted

SELECT CII.ROWID ROW_ID
, CII.INVENTORY_ITEM_ID
, CII.SERIAL_NUMBER
, CII.INSTANCE_DESCRIPTION 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
, MSI.CONCATENATED_SEGMENTS
, MSI.EAM_ITEM_TYPE
, MSI.DESCRIPTION ASSET_GROUP_DESCRIPTION
, CII.CATEGORY_ID CATEGORY_ID
, MCK.CONCATENATED_SEGMENTS CATEGORY_NAME
, CII.PN_LOCATION_ID PN_LOCATION_ID
, CII.ASSET_CRITICALITY_CODE ASSET_CRITICALITY_CODE
, ML.MEANING ASSET_CRITICALITY
, EOMD.ACCOUNTING_CLASS_CODE WIP_ACCOUNTING_CLASS_CODE
, NVL(CII.MAINTAINABLE_FLAG
, 'Y') MAINTAINABLE_FLAG
, EOMD.AREA_ID
, EL.LOCATION_CODES EAM_LOCATION
, EOMD.OWNING_DEPARTMENT_ID OWNING_DEPARTMENT_ID
, BD.DEPARTMENT_CODE OWNING_DEPARTMENT
, 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
, CIA.FA_ASSET_ID
, CII.SERIAL_NUMBER MAINTAINED_UNIT
, CII.INSTANCE_NUMBER
, CII.LAST_VLD_ORGANIZATION_ID INV_ORGANIZATION_ID
, 3 MAINTENANCE_OBJECT_TYPE
, CII.INSTANCE_ID MAINTENANCE_OBJECT_ID
, CII.CHECKIN_STATUS
, CII.CREATED_BY
, CII.CREATION_DATE
, CII.LAST_UPDATED_BY
, CII.LAST_UPDATE_DATE
, CII.LAST_UPDATE_LOGIN
FROM MTL_PARAMETERS MP
, CSI_ITEM_INSTANCES CII
, EAM_ORG_MAINT_DEFAULTS EOMD
, CSI_I_ASSETS CIA
, MFG_LOOKUPS ML
, BOM_DEPARTMENTS BD
, MTL_EAM_LOCATIONS EL
, MTL_CATEGORIES_KFV MCK
, MTL_SYSTEM_ITEMS_B_KFV MSI
, MTL_SERIAL_NUMBERS MSN_PROD
, MTL_SYSTEM_ITEMS MSI_PROD
WHERE MP.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND CII.LAST_VLD_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSI.EAM_ITEM_TYPE IN (1
, 3)
AND NVL(CII.ACTIVE_START_DATE
, SYSDATE-1) <= SYSDATE
AND NVL(CII.ACTIVE_END_DATE
, SYSDATE+1) >= SYSDATE
AND MSI.SERIAL_NUMBER_CONTROL_CODE <> 1
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 = ML.LOOKUP_CODE(+)
AND ML.LOOKUP_TYPE(+) = 'MTL_EAM_ASSET_CRITICALITY'
AND BD.DEPARTMENT_ID(+) = EOMD.OWNING_DEPARTMENT_ID
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.EQUIPMENT_TYPE(+) = 1
AND CII.INSTANCE_ID = CIA.INSTANCE_ID(+)
AND SYSDATE >= NVL(CIA.ACTIVE_START_DATE(+)
, SYSDATE)
AND SYSDATE <= NVL(CIA.ACTIVE_END_DATE(+)
, SYSDATE) UNION ALL SELECT CII.ROWID ROW_ID
, CII.INVENTORY_ITEM_ID
, CII.SERIAL_NUMBER
, CII.INSTANCE_DESCRIPTION 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
, MSI.CONCATENATED_SEGMENTS
, MSI.EAM_ITEM_TYPE
, MSI.DESCRIPTION ASSET_GROUP_DESCRIPTION
, CII.CATEGORY_ID CATEGORY_ID
, MCK.CONCATENATED_SEGMENTS CATEGORY_NAME
, CII.PN_LOCATION_ID PN_LOCATION_ID
, CII.ASSET_CRITICALITY_CODE ASSET_CRITICALITY_CODE
, ML.MEANING ASSET_CRITICALITY
, NULL WIP_ACCOUNTING_CLASS_CODE
, NVL(CII.MAINTAINABLE_FLAG
, 'Y') MAINTAINABLE_FLAG
, NULL
, NULL EAM_LOCATION
, NULL OWNING_DEPARTMENT_ID
, NULL OWNING_DEPARTMENT
, 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
, CIA.FA_ASSET_ID
, CII.SERIAL_NUMBER MAINTAINED_UNIT
, CII.INSTANCE_NUMBER
, CII.LAST_VLD_ORGANIZATION_ID INV_ORGANIZATION_ID
, 3 MAINTENANCE_OBJECT_TYPE
, CII.INSTANCE_ID MAINTENANCE_OBJECT_ID
, CII.CHECKIN_STATUS
, CII.CREATED_BY
, CII.CREATION_DATE
, CII.LAST_UPDATED_BY
, CII.LAST_UPDATE_DATE
, CII.LAST_UPDATE_LOGIN
FROM MTL_PARAMETERS MP
, CSI_ITEM_INSTANCES CII
, CSI_I_ASSETS CIA
, MFG_LOOKUPS ML
, MTL_CATEGORIES_KFV MCK
, MTL_SYSTEM_ITEMS_B_KFV MSI
, MTL_SERIAL_NUMBERS MSN_PROD
, MTL_SYSTEM_ITEMS MSI_PROD
WHERE MP.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND CII.LAST_VLD_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSI.EAM_ITEM_TYPE IN (1
, 3)
AND NVL(CII.ACTIVE_START_DATE
, SYSDATE-1) <= SYSDATE
AND NVL(CII.ACTIVE_END_DATE
, SYSDATE+1) >= SYSDATE
AND MSI.SERIAL_NUMBER_CONTROL_CODE <> 1
AND CII.ASSET_CRITICALITY_CODE = ML.LOOKUP_CODE(+)
AND ML.LOOKUP_TYPE(+) = 'MTL_EAM_ASSET_CRITICALITY'
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.EQUIPMENT_TYPE(+) = 1
AND CII.INSTANCE_ID = CIA.INSTANCE_ID(+)
AND SYSDATE >= NVL(CIA.ACTIVE_START_DATE(+)
, SYSDATE)
AND SYSDATE <= NVL(CIA.ACTIVE_END_DATE(+)
, SYSDATE)
AND NOT EXISTS (SELECT 1
FROM EAM_ORG_MAINT_DEFAULTS EOMD
WHERE EOMD.ORGANIZATION_ID = MP.MAINT_ORGANIZATION_ID
AND EOMD.OBJECT_ID = CII.INSTANCE_ID
AND EOMD.OBJECT_TYPE = 50)