View: MTL_RCV_SERIAL_OH_V

Product:	INV - Inventory
Description:	MTL_RCV_SERIAL_OH_V is used in the material workbench to query the on-hand quantities of materials that are in receiving and are serial controlled.
Implementation/DBA Data:	APPS.MTL_RCV_SERIAL_OH_V

View Text

SELECT RS.TO_ORGANIZATION_ID ORGANIZATION_ID
, MP.ORGANIZATION_CODE ORGANIZATION_CODE
, RS.TO_SUBINVENTORY SUBINVENTORY_CODE
, RS.TO_LOCATOR_ID LOCATOR_ID
, MIL.CONCATENATED_SEGMENTS LOCATOR
, RS.ITEM_ID INVENTORY_ITEM_ID
, MSIV.DESCRIPTION ITEM_DESCRIPTION
, MSIV.CONCATENATED_SEGMENTS ITEM
, RS.ITEM_REVISION REVISION
, MSIV.PRIMARY_UOM_CODE UOM
, DECODE (RSS.SERIAL_NUM
, NULL
, RS.QUANTITY
, 1) ON_HAND
, DECODE (RS.LPN_ID
, NULL
, DECODE (RSS.SERIAL_NUM
, NULL
, RS.QUANTITY
, 1)
, 0) UNPACKED
, DECODE (RS.LPN_ID
, NULL
, 0
, DECODE (RSS.SERIAL_NUM
, NULL
, RS.QUANTITY
, 1)) PACKED
, TO_NUMBER (NULL) COST_GROUP_ID
, RS.LPN_ID LPN_ID
, TO_CHAR (NULL) LPN
, RSS.LOT_NUM LOT_NUMBER
, (SELECT EXPIRATION_DATE
FROM MTL_LOT_NUMBERS MLN
WHERE MLN.ORGANIZATION_ID = MSIV.ORGANIZATION_ID
AND MLN.INVENTORY_ITEM_ID = MSIV.INVENTORY_ITEM_ID
AND MLN.LOT_NUMBER = RSS.LOT_NUM) EXPIRATION_DATE
, RSS.SERIAL_NUM SERIAL_NUMBER
, TO_CHAR (NULL) UNIT_NUMBER
, MIL.PROJECT_ID PROJECT_ID
, MIL.TASK_ID TASK_ID
, MIL.STATUS_ID LOCATOR_STATUS_ID
, TO_NUMBER (NULL) SERIAL_STATUS_ID
, DECODE (NVL (RS.LPN_ID
, -1)
, -1
, TO_NUMBER (NULL)
, 1) CONTAINERIZED_FLAG
, TO_NUMBER (NULL) PLANNING_TP_TYPE
, TO_NUMBER (NULL) PLANNING_ORGANIZATION_ID
, TO_NUMBER (NULL) OWNING_TP_TYPE
, TO_NUMBER (NULL) OWNING_ORGANIZATION_ID
, MSIV.LOT_CONTROL_CODE ITEM_LOT_CONTROL
, MSIV.SERIAL_NUMBER_CONTROL_CODE ITEM_SERIAL_CONTROL /* INVCONV START RNRAO */
, TO_NUMBER (NULL) SECONDARY_ONHAND
, MSIV.SECONDARY_UOM_CODE SECONDARY_UOM_CODE
, TO_CHAR (NULL) GRADE_CODE
, TO_NUMBER (NULL) SECONDARY_UNPACKED
, TO_NUMBER (NULL) SECONDARY_PACKED /* INVCONV END RNRAO */
FROM MTL_SYSTEM_ITEMS_VL MSIV
, RCV_SERIALS_SUPPLY RSS
, RCV_SUPPLY RS
, MTL_ITEM_LOCATIONS_KFV MIL
, MTL_PARAMETERS MP
WHERE MSIV.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND RS.SUPPLY_SOURCE_ID = RSS.TRANSACTION_ID(+)
AND RS.SHIPMENT_LINE_ID = RSS.SHIPMENT_LINE_ID(+)
AND RS.TO_ORGANIZATION_ID = MSIV.ORGANIZATION_ID
AND RS.ITEM_ID = MSIV.INVENTORY_ITEM_ID
AND RS.TO_LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)
AND MSIV.SERIAL_NUMBER_CONTROL_CODE > 1

Columns