VIEW: APPS.MTL_SERIAL_NUMBERS_ALL_V

Source

View Text - Preformatted

SELECT SERIAL.ROWID ROW_ID , SERIAL.INVENTORY_ITEM_ID , MSI.DESCRIPTION , SERIAL.SERIAL_NUMBER , SERIAL.LAST_UPDATE_DATE , SERIAL.LAST_UPDATED_BY , SERIAL.CREATION_DATE , SERIAL.CREATED_BY , SERIAL.LAST_UPDATE_LOGIN , SERIAL.REQUEST_ID , SERIAL.PROGRAM_APPLICATION_ID , SERIAL.PROGRAM_ID , SERIAL.PROGRAM_UPDATE_DATE , SERIAL.INITIALIZATION_DATE , SERIAL.COMPLETION_DATE , SERIAL.SHIP_DATE , SERIAL.CURRENT_STATUS , MFG.MEANING CURRENT_STATUS_NAME , SERIAL.REVISION , SERIAL.LOT_NUMBER , SERIAL.FIXED_ASSET_TAG , SERIAL.RESERVED_ORDER_ID , SERIAL.PARENT_ITEM_ID , SERIAL.PARENT_SERIAL_NUMBER , SERIAL.ORIGINAL_WIP_ENTITY_ID , SERIAL.ORIGINAL_UNIT_VENDOR_ID , NULL VENDOR_NAME , SERIAL.VENDOR_SERIAL_NUMBER , SERIAL.VENDOR_LOT_NUMBER , SERIAL.LAST_TXN_SOURCE_TYPE_ID , SERIAL.LAST_TRANSACTION_ID , SERIAL.LAST_RECEIPT_ISSUE_TYPE , SERIAL.LAST_TXN_SOURCE_NAME , SERIAL.LAST_TXN_SOURCE_ID , SERIAL.DESCRIPTIVE_TEXT , SERIAL.CURRENT_SUBINVENTORY_CODE , SERIAL.CURRENT_LOCATOR_ID , SERIAL.CURRENT_ORGANIZATION_ID , SERIAL.END_ITEM_UNIT_NUMBER , SERIAL.ATTRIBUTE_CATEGORY , SERIAL.ATTRIBUTE1 , SERIAL.ATTRIBUTE2 , SERIAL.ATTRIBUTE3 , SERIAL.ATTRIBUTE4 , SERIAL.ATTRIBUTE5 , SERIAL.ATTRIBUTE6 , SERIAL.ATTRIBUTE7 , SERIAL.ATTRIBUTE8 , SERIAL.ATTRIBUTE9 , SERIAL.ATTRIBUTE10 , SERIAL.ATTRIBUTE11 , SERIAL.ATTRIBUTE12 , SERIAL.ATTRIBUTE13 , SERIAL.ATTRIBUTE14 , SERIAL.ATTRIBUTE15 ,SERIAL.SERIAL_ATTRIBUTE_CATEGORY ,SERIAL.TERRITORY_CODE ,SERIAL.ORIGINATION_DATE ,SERIAL.C_ATTRIBUTE1 ,SERIAL.C_ATTRIBUTE2 ,SERIAL.C_ATTRIBUTE3 ,SERIAL.C_ATTRIBUTE4 ,SERIAL.C_ATTRIBUTE5 ,SERIAL.C_ATTRIBUTE6 ,SERIAL.C_ATTRIBUTE7 ,SERIAL.C_ATTRIBUTE8 ,SERIAL.C_ATTRIBUTE9 ,SERIAL.C_ATTRIBUTE10 ,SERIAL.C_ATTRIBUTE11 ,SERIAL.C_ATTRIBUTE12 ,SERIAL.C_ATTRIBUTE13 ,SERIAL.C_ATTRIBUTE14 ,SERIAL.C_ATTRIBUTE15 ,SERIAL.C_ATTRIBUTE16 ,SERIAL.C_ATTRIBUTE17 ,SERIAL.C_ATTRIBUTE18 ,SERIAL.C_ATTRIBUTE19 ,SERIAL.C_ATTRIBUTE20 ,SERIAL.D_ATTRIBUTE1 ,SERIAL.D_ATTRIBUTE2 ,SERIAL.D_ATTRIBUTE3 ,SERIAL.D_ATTRIBUTE4 ,SERIAL.D_ATTRIBUTE5 ,SERIAL.D_ATTRIBUTE6 ,SERIAL.D_ATTRIBUTE7 ,SERIAL.D_ATTRIBUTE8 ,SERIAL.D_ATTRIBUTE9 ,SERIAL.D_ATTRIBUTE10 ,SERIAL.N_ATTRIBUTE1 ,SERIAL.N_ATTRIBUTE2 ,SERIAL.N_ATTRIBUTE3 ,SERIAL.N_ATTRIBUTE4 ,SERIAL.N_ATTRIBUTE5 ,SERIAL.N_ATTRIBUTE6 ,SERIAL.N_ATTRIBUTE7 ,SERIAL.N_ATTRIBUTE8 ,SERIAL.N_ATTRIBUTE9 ,SERIAL.N_ATTRIBUTE10 ,SERIAL.STATUS_ID , MSTL.STATUS_CODE , SERIAL.GEN_OBJECT_ID , SERIAL.TIME_SINCE_NEW , SERIAL.CYCLES_SINCE_NEW , SERIAL.TIME_SINCE_OVERHAUL , SERIAL.CYCLES_SINCE_OVERHAUL , SERIAL.TIME_SINCE_REPAIR , SERIAL.CYCLES_SINCE_REPAIR , SERIAL.TIME_SINCE_VISIT , SERIAL.CYCLES_SINCE_VISIT ,SERIAL.TIME_SINCE_MARK ,SERIAL.CYCLES_SINCE_MARK ,SERIAL.NUMBER_OF_REPAIRS ,SERIAL.LPN_ID ,SERIAL.WIP_ENTITY_ID ,SERIAL.OPERATION_SEQ_NUM ,SERIAL.INTRAOPERATION_STEP_TYPE ,MP.ORGANIZATION_CODE FROM MTL_SERIAL_NUMBERS SERIAL, MTL_SYSTEM_ITEMS_TL MSI, MFG_LOOKUPS MFG, MTL_MATERIAL_STATUSES_TL MSTL, MTL_PARAMETERS MP WHERE SERIAL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND SERIAL.CURRENT_ORGANIZATION_ID = MSI.ORGANIZATION_ID AND MSI.LANGUAGE = USERENV('LANG') AND MP.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND MFG.LOOKUP_TYPE = 'SERIAL_NUM_STATUS' AND MFG.LOOKUP_CODE = SERIAL.CURRENT_STATUS AND SERIAL.STATUS_ID= MSTL.STATUS_ID(+) AND MSTL.LANGUAGE(+) =userenv('LANG')

View Text - HTML Formatted

SELECT SERIAL.ROWID ROW_ID
, SERIAL.INVENTORY_ITEM_ID
, MSI.DESCRIPTION
, SERIAL.SERIAL_NUMBER
, SERIAL.LAST_UPDATE_DATE
, SERIAL.LAST_UPDATED_BY
, SERIAL.CREATION_DATE
, SERIAL.CREATED_BY
, SERIAL.LAST_UPDATE_LOGIN
, SERIAL.REQUEST_ID
, SERIAL.PROGRAM_APPLICATION_ID
, SERIAL.PROGRAM_ID
, SERIAL.PROGRAM_UPDATE_DATE
, SERIAL.INITIALIZATION_DATE
, SERIAL.COMPLETION_DATE
, SERIAL.SHIP_DATE
, SERIAL.CURRENT_STATUS
, MFG.MEANING CURRENT_STATUS_NAME
, SERIAL.REVISION
, SERIAL.LOT_NUMBER
, SERIAL.FIXED_ASSET_TAG
, SERIAL.RESERVED_ORDER_ID
, SERIAL.PARENT_ITEM_ID
, SERIAL.PARENT_SERIAL_NUMBER
, SERIAL.ORIGINAL_WIP_ENTITY_ID
, SERIAL.ORIGINAL_UNIT_VENDOR_ID
, NULL VENDOR_NAME
, SERIAL.VENDOR_SERIAL_NUMBER
, SERIAL.VENDOR_LOT_NUMBER
, SERIAL.LAST_TXN_SOURCE_TYPE_ID
, SERIAL.LAST_TRANSACTION_ID
, SERIAL.LAST_RECEIPT_ISSUE_TYPE
, SERIAL.LAST_TXN_SOURCE_NAME
, SERIAL.LAST_TXN_SOURCE_ID
, SERIAL.DESCRIPTIVE_TEXT
, SERIAL.CURRENT_SUBINVENTORY_CODE
, SERIAL.CURRENT_LOCATOR_ID
, SERIAL.CURRENT_ORGANIZATION_ID
, SERIAL.END_ITEM_UNIT_NUMBER
, SERIAL.ATTRIBUTE_CATEGORY
, SERIAL.ATTRIBUTE1
, SERIAL.ATTRIBUTE2
, SERIAL.ATTRIBUTE3
, SERIAL.ATTRIBUTE4
, SERIAL.ATTRIBUTE5
, SERIAL.ATTRIBUTE6
, SERIAL.ATTRIBUTE7
, SERIAL.ATTRIBUTE8
, SERIAL.ATTRIBUTE9
, SERIAL.ATTRIBUTE10
, SERIAL.ATTRIBUTE11
, SERIAL.ATTRIBUTE12
, SERIAL.ATTRIBUTE13
, SERIAL.ATTRIBUTE14
, SERIAL.ATTRIBUTE15
, SERIAL.SERIAL_ATTRIBUTE_CATEGORY
, SERIAL.TERRITORY_CODE
, SERIAL.ORIGINATION_DATE
, SERIAL.C_ATTRIBUTE1
, SERIAL.C_ATTRIBUTE2
, SERIAL.C_ATTRIBUTE3
, SERIAL.C_ATTRIBUTE4
, SERIAL.C_ATTRIBUTE5
, SERIAL.C_ATTRIBUTE6
, SERIAL.C_ATTRIBUTE7
, SERIAL.C_ATTRIBUTE8
, SERIAL.C_ATTRIBUTE9
, SERIAL.C_ATTRIBUTE10
, SERIAL.C_ATTRIBUTE11
, SERIAL.C_ATTRIBUTE12
, SERIAL.C_ATTRIBUTE13
, SERIAL.C_ATTRIBUTE14
, SERIAL.C_ATTRIBUTE15
, SERIAL.C_ATTRIBUTE16
, SERIAL.C_ATTRIBUTE17
, SERIAL.C_ATTRIBUTE18
, SERIAL.C_ATTRIBUTE19
, SERIAL.C_ATTRIBUTE20
, SERIAL.D_ATTRIBUTE1
, SERIAL.D_ATTRIBUTE2
, SERIAL.D_ATTRIBUTE3
, SERIAL.D_ATTRIBUTE4
, SERIAL.D_ATTRIBUTE5
, SERIAL.D_ATTRIBUTE6
, SERIAL.D_ATTRIBUTE7
, SERIAL.D_ATTRIBUTE8
, SERIAL.D_ATTRIBUTE9
, SERIAL.D_ATTRIBUTE10
, SERIAL.N_ATTRIBUTE1
, SERIAL.N_ATTRIBUTE2
, SERIAL.N_ATTRIBUTE3
, SERIAL.N_ATTRIBUTE4
, SERIAL.N_ATTRIBUTE5
, SERIAL.N_ATTRIBUTE6
, SERIAL.N_ATTRIBUTE7
, SERIAL.N_ATTRIBUTE8
, SERIAL.N_ATTRIBUTE9
, SERIAL.N_ATTRIBUTE10
, SERIAL.STATUS_ID
, MSTL.STATUS_CODE
, SERIAL.GEN_OBJECT_ID
, SERIAL.TIME_SINCE_NEW
, SERIAL.CYCLES_SINCE_NEW
, SERIAL.TIME_SINCE_OVERHAUL
, SERIAL.CYCLES_SINCE_OVERHAUL
, SERIAL.TIME_SINCE_REPAIR
, SERIAL.CYCLES_SINCE_REPAIR
, SERIAL.TIME_SINCE_VISIT
, SERIAL.CYCLES_SINCE_VISIT
, SERIAL.TIME_SINCE_MARK
, SERIAL.CYCLES_SINCE_MARK
, SERIAL.NUMBER_OF_REPAIRS
, SERIAL.LPN_ID
, SERIAL.WIP_ENTITY_ID
, SERIAL.OPERATION_SEQ_NUM
, SERIAL.INTRAOPERATION_STEP_TYPE
, MP.ORGANIZATION_CODE
FROM MTL_SERIAL_NUMBERS SERIAL
, MTL_SYSTEM_ITEMS_TL MSI
, MFG_LOOKUPS MFG
, MTL_MATERIAL_STATUSES_TL MSTL
, MTL_PARAMETERS MP
WHERE SERIAL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND SERIAL.CURRENT_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.LANGUAGE = USERENV('LANG')
AND MP.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MFG.LOOKUP_TYPE = 'SERIAL_NUM_STATUS'
AND MFG.LOOKUP_CODE = SERIAL.CURRENT_STATUS
AND SERIAL.STATUS_ID= MSTL.STATUS_ID(+)
AND MSTL.LANGUAGE(+) =USERENV('LANG')