View: MTL_EAM_ASSET_REBUILDS_V

Product:	INV - Inventory
Description:	View for Asset and Rebuild information.
Implementation/DBA Data:	APPS.MTL_EAM_ASSET_REBUILDS_V

View Text

SELECT CII.ROWID ROW_ID
, CII.INVENTORY_ITEM_ID
, CII.SERIAL_NUMBER
, CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT
, CII.NETWORK_ASSET_FLAG NETWORK_ASSET_FLAG
, MP.MAINT_ORGANIZATION_ID CURRENT_ORGANIZATION_ID
, MSI.CONCATENATED_SEGMENTS
, MSI.EAM_ITEM_TYPE
, MSI.DESCRIPTION ASSET_GROUP_DESCRIPTION
, MCK.CONCATENATED_SEGMENTS CATEGORY_NAME
, CII.ASSET_CRITICALITY_CODE ASSET_CRITICALITY
, NVL(CII.MAINTAINABLE_FLAG
, 'Y') MAINTAINABLE_FLAG
, MSN_PARENT.INVENTORY_ITEM_ID PARENT_ITEM_ID
, MSN_PARENT.SERIAL_NUMBER PARENT_SERIAL_NUMBER
, MSN_PARENT.GEN_OBJECT_ID PARENT_OBJECT_ID
, MSN.GEN_OBJECT_ID
, MSN.CURRENT_STATUS
, CII.INSTANCE_NUMBER
, CII.LAST_VLD_ORGANIZATION_ID INV_ORGANIZATION_ID
, CII.INSTANCE_ID
, (SELECT COUNT(*)
FROM MTL_OBJECT_GENEALOGY MOG1
WHERE MOG1.PARENT_OBJECT_ID = MSN.GEN_OBJECT_ID
AND NVL(MOG1.START_DATE_ACTIVE
, SYSDATE-1) <= SYSDATE
AND NVL(MOG1.END_DATE_ACTIVE
, SYSDATE+1) >= SYSDATE) AS NO_OF_CHILDREN
FROM MTL_PARAMETERS MP
, CSI_ITEM_INSTANCES CII
, MTL_CATEGORIES_KFV MCK
, MTL_SYSTEM_ITEMS_B_KFV MSI
, MTL_OBJECT_GENEALOGY MOG
, MTL_SERIAL_NUMBERS MSN
, MTL_SERIAL_NUMBERS MSN_PARENT
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 MSI.SERIAL_NUMBER_CONTROL_CODE <>1
AND CII.CATEGORY_ID = MCK.CATEGORY_ID(+)
AND CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
AND MSN.GEN_OBJECT_ID = MOG.OBJECT_ID(+)
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)

Columns