VIEW: APPS.MTL_ONHAND_STATUS_HIST_ERV

Source

View Text - Preformatted

SELECT msh.organization_id , msh.inventory_item_id , msi.concatenated_segments , msh.lot_number , mp.organization_code , h.name , msh.zone_code , msh. locator_id , DECODE (msh.locator_id ,NULL,NULL, ( SELECT mil.concatenated_segments FROM mtl_item_locations_kfv mil WHERE mil.inventory_location_id =msh.locator_id)) , msh.lpn_id , inv_status_pkg.get_onhand_from_status ( msh.organization_id , msh.inventory_item_id , msh.zone_code , msh.locator_id , msh.lot_number , msh.lpn_id) , mms.status_code , mms.description , msh.status_id , msi.primary_unit_of_measure , msi.secondary_uom_code , mr.reason_name , msh.creation_date , DECODE (msh.lpn_id,NULL,NULL, (SELECT license_plate_number FROM wms_license_plate_numbers WHERE lpn_id = msh.lpn_id)) , msh.group_id , SUM(moqd.primary_transaction_quantity) , SUM(moqd.secondary_transaction_quantity) FROM mtl_material_status_history msh , hr_all_organization_units h , mtl_system_items_kfv msi , mtl_parameters mp , mtl_transaction_reasons mr , mtl_material_statuses mms , mtl_onhand_quantities_detail moqd WHERE msh.organization_id = h.organization_id AND h.organization_id = mp.organization_id AND msh.update_reason_id = mr.reason_id(+) AND msh.status_id = mms .status_id AND msh.serial_number IS NULL AND msh.organization_id = msi.organization_id AND msh.inventory_item_id = msi.inventory_item_id AND EXISTS ( SELECT 'y' FROM mtl_parameters mp WHERE mp.organization_id = msh.organization_id AND default_status_id IS NOT NULL) AND msh.organization_id IS NOT NULL AND msh.inventory_item_id IS NOT NULL AND msh.organization_id = moqd.organization_id AND msh.inventory_item_id = moqd.inventory_item_id AND msh.zone_code = moqd.subinventory_code AND DECODE(msh.lpn_id,NULL,-999,msh.lpn_id) = NVL(moqd.lpn_id,-999) AND DECODE(msh.lot_number,NULL,'@@@',msh.lot_number) = NVL(moqd.lot_number,'@@@') AND DECODE(msh.locator_id,NULL,-999, msh.locator_id) = NVL(moqd.locator_id,-999) GROUP BY msh.organization_id , msh.inventory_item_id , msi.concatenated_segments , msh.lot_number , mp.organization_code , h.name , msh.zone_code , msh.locator_id , 8 , msh.lpn_id , inv_status_pkg.get_onhand_from_status ( msh.organization_id , msh.inventory_item_id , msh.zone_code , msh.locator_id , msh.lot_number ,msh.lpn_id) , mms .status_code , mms.description , msh.status_id , msi.primary_unit_of_measure , msi.secondary_uom_code , mr.reason_name , msh.creation_date , 17 , msh.group_id

View Text - HTML Formatted

SELECT MSH.ORGANIZATION_ID
, MSH.INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS
, MSH.LOT_NUMBER
, MP.ORGANIZATION_CODE
, H.NAME
, MSH.ZONE_CODE
, MSH. LOCATOR_ID
, DECODE (MSH.LOCATOR_ID
, NULL
, NULL
, ( SELECT MIL.CONCATENATED_SEGMENTS
FROM MTL_ITEM_LOCATIONS_KFV MIL
WHERE MIL.INVENTORY_LOCATION_ID =MSH.LOCATOR_ID))
, MSH.LPN_ID
, INV_STATUS_PKG.GET_ONHAND_FROM_STATUS ( MSH.ORGANIZATION_ID
, MSH.INVENTORY_ITEM_ID
, MSH.ZONE_CODE
, MSH.LOCATOR_ID
, MSH.LOT_NUMBER
, MSH.LPN_ID)
, MMS.STATUS_CODE
, MMS.DESCRIPTION
, MSH.STATUS_ID
, MSI.PRIMARY_UNIT_OF_MEASURE
, MSI.SECONDARY_UOM_CODE
, MR.REASON_NAME
, MSH.CREATION_DATE
, DECODE (MSH.LPN_ID
, NULL
, NULL
, (SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = MSH.LPN_ID))
, MSH.GROUP_ID
, SUM(MOQD.PRIMARY_TRANSACTION_QUANTITY)
, SUM(MOQD.SECONDARY_TRANSACTION_QUANTITY)
FROM MTL_MATERIAL_STATUS_HISTORY MSH
, HR_ALL_ORGANIZATION_UNITS H
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_PARAMETERS MP
, MTL_TRANSACTION_REASONS MR
, MTL_MATERIAL_STATUSES MMS
, MTL_ONHAND_QUANTITIES_DETAIL MOQD
WHERE MSH.ORGANIZATION_ID = H.ORGANIZATION_ID
AND H.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSH.UPDATE_REASON_ID = MR.REASON_ID(+)
AND MSH.STATUS_ID = MMS .STATUS_ID
AND MSH.SERIAL_NUMBER IS NULL
AND MSH.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSH.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND EXISTS ( SELECT 'Y'
FROM MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_ID = MSH.ORGANIZATION_ID
AND DEFAULT_STATUS_ID IS NOT NULL)
AND MSH.ORGANIZATION_ID IS NOT NULL
AND MSH.INVENTORY_ITEM_ID IS NOT NULL
AND MSH.ORGANIZATION_ID = MOQD.ORGANIZATION_ID
AND MSH.INVENTORY_ITEM_ID = MOQD.INVENTORY_ITEM_ID
AND MSH.ZONE_CODE = MOQD.SUBINVENTORY_CODE
AND DECODE(MSH.LPN_ID
, NULL
, -999
, MSH.LPN_ID) = NVL(MOQD.LPN_ID
, -999)
AND DECODE(MSH.LOT_NUMBER
, NULL
, '@@@'
, MSH.LOT_NUMBER) = NVL(MOQD.LOT_NUMBER
, '@@@')
AND DECODE(MSH.LOCATOR_ID
, NULL
, -999
, MSH.LOCATOR_ID) = NVL(MOQD.LOCATOR_ID
, -999) GROUP BY MSH.ORGANIZATION_ID
, MSH.INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS
, MSH.LOT_NUMBER
, MP.ORGANIZATION_CODE
, H.NAME
, MSH.ZONE_CODE
, MSH.LOCATOR_ID
, 8
, MSH.LPN_ID
, INV_STATUS_PKG.GET_ONHAND_FROM_STATUS ( MSH.ORGANIZATION_ID
, MSH.INVENTORY_ITEM_ID
, MSH.ZONE_CODE
, MSH.LOCATOR_ID
, MSH.LOT_NUMBER
, MSH.LPN_ID)
, MMS .STATUS_CODE
, MMS.DESCRIPTION
, MSH.STATUS_ID
, MSI.PRIMARY_UNIT_OF_MEASURE
, MSI.SECONDARY_UOM_CODE
, MR.REASON_NAME
, MSH.CREATION_DATE
, 17
, MSH.GROUP_ID