View: MTL_RCV_SERIAL_MWB_OH_V

Product:	INV - Inventory
Description:	MTL_RCV_SERIAL_MWB_OH_V is a new view which needs to be used for querying data when the user chooses subinventory type of Receiving and user has entered serials and grade code in the Find window, or has entered grade in the Find window and
Implementation/DBA Data:	APPS.MTL_RCV_SERIAL_MWB_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
, MSI.DESCRIPTION ITEM_DESCRIPTION
, MSI.CONCATENATED_SEGMENTS ITEM
, RS.ITEM_REVISION REVISION
, MSI.PRIMARY_UOM_CODE UOM
, RS.QUANTITY ON_HAND
, DECODE (RS.LPN_ID
, NULL
, RS.QUANTITY
, 0) UNPACKED
, DECODE (RS.LPN_ID
, NULL
, 0
, RS.QUANTITY) PACKED
, TO_NUMBER (NULL) COST_GROUP_ID
, RS.LPN_ID LPN_ID
, TO_CHAR (NULL) LPN
, TO_CHAR (NULL) LOT_NUMBER
, TO_CHAR (NULL) 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) LOT_STATUS_ID
, TO_NUMBER (NULL) SERIAL_STATUS_ID
, DECODE (RS.LPN_ID
, NULL
, TO_NUMBER (NULL)
, 1) CONTAINERIZED_FLAG
, TO_CHAR (NULL) STATUS_LEVEL
, TO_NUMBER (NULL) PLANNING_TP_TYPE
, TO_NUMBER (NULL) PLANNING_ORGANIZATION_ID
, TO_NUMBER (NULL) OWNING_TP_TYPE
, TO_NUMBER (NULL) OWNING_ORGANIZATION_ID
, MSI.LOT_CONTROL_CODE ITEM_LOT_CONTROL
, MSI.SERIAL_NUMBER_CONTROL_CODE ITEM_SERIAL_CONTROL
, TO_NUMBER (NULL) SECONDARY_ONHAND
, MSI.SECONDARY_UOM_CODE SECONDARY_UOM_CODE
, TO_CHAR (NULL) GRADE_CODE
, TO_NUMBER (NULL) SECONDARY_UNPACKED
, TO_NUMBER (NULL) SECONDARY_PACKED
FROM RCV_SUPPLY RS
, MTL_SYSTEM_ITEMS_VL MSI
, MTL_ITEM_LOCATIONS_KFV MIL
, MTL_PARAMETERS MP
WHERE MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND RS.SUPPLY_TYPE_CODE = 'RECEIVING'
AND RS.ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND RS.TO_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.LOT_CONTROL_CODE = 1
AND MIL.ORGANIZATION_ID(+) = RS.TO_ORGANIZATION_ID
AND RS.TO_LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+) UNION ALL 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
, MSI.DESCRIPTION ITEM_DESCRIPTION
, MSI.CONCATENATED_SEGMENTS ITEM
, RS.ITEM_REVISION REVISION
, MSI.PRIMARY_UOM_CODE UOM
, RS.QUANTITY ON_HAND
, DECODE (RS.LPN_ID
, NULL
, RS.QUANTITY
, 0) UNPACKED
, DECODE (RS.LPN_ID
, NULL
, 0
, RS.QUANTITY) PACKED
, TO_NUMBER (NULL) COST_GROUP_ID
, RS.LPN_ID LPN_ID
, TO_CHAR (NULL) LPN
, RLS.LOT_NUM LOT_NUMBER
, TO_CHAR (NULL) SERIAL_NUMBER
, TO_CHAR (NULL) UNIT_NUMBER
, MIL.PROJECT_ID PROJECT_ID
, MIL.TASK_ID TASK_ID
, TO_NUMBER (NULL) LOCATOR_STATUS_ID
, TO_NUMBER (NULL) LOT_STATUS_ID
, TO_NUMBER (NULL) SERIAL_STATUS_ID
, DECODE (RS.LPN_ID
, NULL
, TO_NUMBER (NULL)
, 1) CONTAINERIZED_FLAG
, TO_CHAR (NULL) STATUS_LEVEL
, TO_NUMBER (NULL) PLANNING_TP_TYPE
, TO_NUMBER (NULL) PLANNING_ORGANIZATION_ID
, TO_NUMBER (NULL) OWNING_TP_TYPE
, TO_NUMBER (NULL) OWNING_ORGANIZATION_ID
, MSI.LOT_CONTROL_CODE ITEM_LOT_CONTROL
, MSI.SERIAL_NUMBER_CONTROL_CODE ITEM_SERIAL_CONTROL
, TO_NUMBER (NULL) SECONDARY_ONHAND
, MSI.SECONDARY_UOM_CODE SECONDARY_UOM_CODE
, MLN.GRADE_CODE GRADE_CODE
, TO_NUMBER (NULL) SECONDARY_UNPACKED
, TO_NUMBER (NULL) SECONDARY_PACKED
FROM RCV_SUPPLY RS
, RCV_LOTS_SUPPLY RLS
, MTL_SYSTEM_ITEMS_VL MSI
, MTL_ITEM_LOCATIONS_KFV MIL
, MTL_PARAMETERS MP
, MTL_LOT_NUMBERS MLN
WHERE MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND RS.SUPPLY_TYPE_CODE = 'RECEIVING'
AND RS.SUPPLY_SOURCE_ID = RLS.TRANSACTION_ID(+)
AND RS.SHIPMENT_LINE_ID = RLS.SHIPMENT_LINE_ID(+)
AND RS.ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND RS.TO_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.LOT_CONTROL_CODE = 2
AND MIL.ORGANIZATION_ID(+) = RS.TO_ORGANIZATION_ID
AND RS.TO_LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)
AND MLN.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MLN.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MLN.LOT_NUMBER(+) = RLS.LOT_NUM

Columns