VIEW: APPS.MTL_RCV_MWB_NEW_OH_V

Source

View Text - Preformatted

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 /* INVCONV START rnrao */ , 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 /* INVCONV END rnrao */ 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 /* INVCONV START rnrao */ , 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 /* INVCONV END rnrao */ 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

View Text - HTML Formatted

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 /* INVCONV START RNRAO */
, 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 /* INVCONV END RNRAO */
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 /* INVCONV START RNRAO */
, 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 /* INVCONV END RNRAO */
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