eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.MTL_RCV_SERIAL_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 ,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

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
, 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