VIEW: APPS.MTL_ONHAND_SERIAL_V

Source

View Text - Preformatted

SELECT msn.current_organization_id organization_id ,mp.organization_code organization_code ,msn.current_subinventory_code subinventory_code ,msn.current_locator_id locator_id ,mil.concatenated_segments LOCATOR ,msn.inventory_item_id inventory_item_id ,msiv.description item_description ,msiv.concatenated_segments item ,msn.revision revision ,msiv.primary_uom_code uom ,1 on_hand ,DECODE (NVL (msn.lpn_id, -1), -1, 1, 0) unpacked ,DECODE (NVL (msn.lpn_id, -1), -1, 0, 1) packed ,msn.cost_group_id cost_group_id ,msn.lpn_id lpn_id ,TO_CHAR (NULL) lpn ,msn.lot_number lot_number ,mln.expiration_date expiration_date ,msn.serial_number serial_number ,msn.end_item_unit_number unit_number ,mil.project_id project_id ,mil.task_id task_id ,msi.status_id subinventory_status_id ,mil.status_id locator_status_id ,mln.status_id lot_status_id ,msn.status_id serial_status_id ,DECODE (NVL (msn.lpn_id, -1), -1, TO_NUMBER (NULL), 1) containerized_flag ,msn.planning_tp_type planning_tp_type ,msn.planning_organization_id planning_organization_id ,msn.owning_tp_type owning_tp_type ,msn.owning_organization_id 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 ,TO_CHAR (NULL) secondary_uom_code ,mln.grade_code grade_code ,TO_NUMBER (NULL) secondary_packed ,TO_NUMBER (NULL) secondary_unpacked /* INVCONV END rnrao */ ,msn.last_transaction_id last_transaction_id FROM mtl_parameters mp ,mtl_system_items_vl msiv ,mtl_item_locations_kfv mil ,mtl_lot_numbers mln ,mtl_secondary_inventories msi ,mtl_serial_numbers msn WHERE msn.current_organization_id = mp.organization_id AND msn.current_organization_id = msiv.organization_id AND msn.inventory_item_id = msiv.inventory_item_id AND msn.current_organization_id = mil.organization_id(+) AND msn.current_locator_id = mil.inventory_location_id(+) AND msn.current_organization_id = mln.organization_id(+) AND msn.inventory_item_id = mln.inventory_item_id(+) AND msn.lot_number = mln.lot_number(+) AND msn.current_organization_id = msi.organization_id AND msn.current_subinventory_code = msi.secondary_inventory_name AND msn.current_status = 3 AND NVL (msn.organization_type, 2) = 2 AND (msi.subinventory_type = 1 OR msi.subinventory_type IS NULL)

View Text - HTML Formatted

SELECT MSN.CURRENT_ORGANIZATION_ID ORGANIZATION_ID
, MP.ORGANIZATION_CODE ORGANIZATION_CODE
, MSN.CURRENT_SUBINVENTORY_CODE SUBINVENTORY_CODE
, MSN.CURRENT_LOCATOR_ID LOCATOR_ID
, MIL.CONCATENATED_SEGMENTS LOCATOR
, MSN.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MSIV.DESCRIPTION ITEM_DESCRIPTION
, MSIV.CONCATENATED_SEGMENTS ITEM
, MSN.REVISION REVISION
, MSIV.PRIMARY_UOM_CODE UOM
, 1 ON_HAND
, DECODE (NVL (MSN.LPN_ID
, -1)
, -1
, 1
, 0) UNPACKED
, DECODE (NVL (MSN.LPN_ID
, -1)
, -1
, 0
, 1) PACKED
, MSN.COST_GROUP_ID COST_GROUP_ID
, MSN.LPN_ID LPN_ID
, TO_CHAR (NULL) LPN
, MSN.LOT_NUMBER LOT_NUMBER
, MLN.EXPIRATION_DATE EXPIRATION_DATE
, MSN.SERIAL_NUMBER SERIAL_NUMBER
, MSN.END_ITEM_UNIT_NUMBER UNIT_NUMBER
, MIL.PROJECT_ID PROJECT_ID
, MIL.TASK_ID TASK_ID
, MSI.STATUS_ID SUBINVENTORY_STATUS_ID
, MIL.STATUS_ID LOCATOR_STATUS_ID
, MLN.STATUS_ID LOT_STATUS_ID
, MSN.STATUS_ID SERIAL_STATUS_ID
, DECODE (NVL (MSN.LPN_ID
, -1)
, -1
, TO_NUMBER (NULL)
, 1) CONTAINERIZED_FLAG
, MSN.PLANNING_TP_TYPE PLANNING_TP_TYPE
, MSN.PLANNING_ORGANIZATION_ID PLANNING_ORGANIZATION_ID
, MSN.OWNING_TP_TYPE OWNING_TP_TYPE
, MSN.OWNING_ORGANIZATION_ID 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
, TO_CHAR (NULL) SECONDARY_UOM_CODE
, MLN.GRADE_CODE GRADE_CODE
, TO_NUMBER (NULL) SECONDARY_PACKED
, TO_NUMBER (NULL) SECONDARY_UNPACKED /* INVCONV END RNRAO */
, MSN.LAST_TRANSACTION_ID LAST_TRANSACTION_ID
FROM MTL_PARAMETERS MP
, MTL_SYSTEM_ITEMS_VL MSIV
, MTL_ITEM_LOCATIONS_KFV MIL
, MTL_LOT_NUMBERS MLN
, MTL_SECONDARY_INVENTORIES MSI
, MTL_SERIAL_NUMBERS MSN
WHERE MSN.CURRENT_ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSN.CURRENT_ORGANIZATION_ID = MSIV.ORGANIZATION_ID
AND MSN.INVENTORY_ITEM_ID = MSIV.INVENTORY_ITEM_ID
AND MSN.CURRENT_ORGANIZATION_ID = MIL.ORGANIZATION_ID(+)
AND MSN.CURRENT_LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)
AND MSN.CURRENT_ORGANIZATION_ID = MLN.ORGANIZATION_ID(+)
AND MSN.INVENTORY_ITEM_ID = MLN.INVENTORY_ITEM_ID(+)
AND MSN.LOT_NUMBER = MLN.LOT_NUMBER(+)
AND MSN.CURRENT_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSN.CURRENT_SUBINVENTORY_CODE = MSI.SECONDARY_INVENTORY_NAME
AND MSN.CURRENT_STATUS = 3
AND NVL (MSN.ORGANIZATION_TYPE
, 2) = 2
AND (MSI.SUBINVENTORY_TYPE = 1 OR MSI.SUBINVENTORY_TYPE IS NULL)