VIEW: APPS.BIV_MAINT_ASSET_NUMBER_LVL_V

Source

View Text - Preformatted

SELECT CII.INSTANCE_ID ID, CII.INSTANCE_NUMBER VALUE, CII.SERIAL_NUMBER, CII.LAST_VLD_ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, MSI.CONCATENATED_SEGMENTS ASSET_GROUP,MP.MAINT_ORGANIZATION_ID FROM MTL_SYSTEM_ITEMS_KFV MSI, CSI_ITEM_INSTANCES CII, MTL_PARAMETERS MP WHERE MSI.EAM_ITEM_TYPE in (1,3) /* Asset Groups and rebuildable items */ And serial_number_control_code <> 1 /* This gets rid of the non-serialized rebuildable items */ And MSI.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID And MSI.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID union all select -1 id, fnd_message.get_string('BIS','BIS_UNASSIGNED') value, null, null, null, null, null from dual

View Text - HTML Formatted

SELECT CII.INSTANCE_ID ID
, CII.INSTANCE_NUMBER VALUE
, CII.SERIAL_NUMBER
, CII.LAST_VLD_ORGANIZATION_ID
, MSI.INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS ASSET_GROUP
, MP.MAINT_ORGANIZATION_ID
FROM MTL_SYSTEM_ITEMS_KFV MSI
, CSI_ITEM_INSTANCES CII
, MTL_PARAMETERS MP
WHERE MSI.EAM_ITEM_TYPE IN (1
, 3) /* ASSET GROUPS
AND REBUILDABLE ITEMS */
AND SERIAL_NUMBER_CONTROL_CODE <> 1 /* THIS GETS RID OF THE NON-SERIALIZED REBUILDABLE ITEMS */
AND MSI.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID UNION ALL SELECT -1 ID
, FND_MESSAGE.GET_STRING('BIS'
, 'BIS_UNASSIGNED') VALUE
, NULL
, NULL
, NULL
, NULL
, NULL
FROM DUAL