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_ONHAND_SYNC_V

Source

View Text - Preformatted


SELECT mck.concatenated_segments Category, onh.organization_id Warehouse_id, mp.organization_code Warehouse, onh.inventory_item_id Item_id, msibk.concatenated_segments Item, msibk.description Item_Description, onh.revision Revision, onh.subinventory_code Subinventory, milk.concatenated_segments Locator, onh.lot_number Lot, wlpn1.license_plate_number LPN, wlpn2.license_plate_number Parent_LPN, onh.serial_number Serial, Decode(onh.lpn_id,NULL,'No','Yes') Containerized_Flag, onh.primary_quantity Primary_Quantity, muom1.unit_of_measure_tl PRIMARY_UOM, Decode(msibk.tracking_quantity_ind,'PS',onh.secondary_quantity,NULL) Secondary_Quantity, Decode(msibk.tracking_quantity_ind,'PS',muom2.unit_of_measure_tl,null) Secondary_UOM, Sysdate Snapshot_Date, mms.status_code Onhand_Status from (SELECT mic.category_id category_id , moq.organization_id organization_id , moq.inventory_item_id inventory_item_id , moq.revision revision , moq.subinventory_code subinventory_code , moq.locator_id locator_id , moq.lot_number lot_number , moq.lpn_id lpn_id , NULL serial_number , Decode(moq.status_id, null, inv_material_status_grp.get_default_status_conc(moq.organization_id, moq.inventory_item_id, moq.subinventory_code, moq.locator_id, moq.lot_number, moq.lpn_id), moq.status_id) status_id , sum(moq.primary_transaction_quantity) primary_quantity , sum(moq.secondary_transaction_quantity) secondary_quantity FROM mtl_onhand_quantities_detail moq, mtl_system_items_b msi, mtl_item_categories mic WHERE msi.inventory_item_id = moq.inventory_item_id AND msi.organization_id = moq.organization_id AND msi.serial_number_control_code in (1,6) AND mic.inventory_item_id = moq.inventory_item_id AND mic.organization_id = moq.organization_id GROUP BY mic.category_id, moq.organization_id,moq.inventory_item_id,moq.revision, moq.subinventory_code,moq.locator_id,moq.lot_number,moq.lpn_id,moq.status_id UNION all SELECT mic.category_id category_id , msn.current_organization_id organization_id , msn.inventory_item_id inventory_item_id , msn.revision revision , msn.current_subinventory_code subinventory_code , msn.current_locator_id locator_id , msn.lot_number lot_number , msn.lpn_id lpn_id , msn.serial_number serial_number , msn.status_id status_id , 1 primary_quantity , NULL secondary_quantity FROM mtl_serial_numbers msn, mtl_system_items_b msi, mtl_item_categories mic WHERE msn.current_status = 3 AND msi.inventory_item_id = msn.inventory_item_id AND msi.organization_id = msn.current_organization_id AND msi.serial_number_control_code not in (1,6) AND mic.inventory_item_id = msn.inventory_item_id AND mic.organization_id = msn.current_organization_id ) onh, mtl_categories_kfv mck, mtl_parameters mp, mtl_system_items_b_kfv msibk, mtl_item_locations_kfv milk, wms_license_plate_numbers wlpn1, wms_license_plate_numbers wlpn2, mtl_units_of_measure muom1, mtl_units_of_measure muom2, mtl_material_statuses mms WHERE mck.category_id = onh.category_id AND mp.organization_id = onh.organization_id AND msibk.organization_id = onh.organization_id AND msibk.inventory_item_id = onh.inventory_item_id AND milk.organization_id (+) = onh.organization_id AND milk.inventory_location_id (+) = onh.locator_id AND mms.status_id (+) = onh.status_id AND wlpn1.lpn_id (+) = onh.lpn_id AND wlpn2.lpn_id (+) = wlpn1.parent_lpn_id AND muom1.uom_code = msibk.primary_uom_code AND muom2.uom_code (+) = msibk.secondary_uom_code

View Text - HTML Formatted

SELECT MCK.CONCATENATED_SEGMENTS CATEGORY
, ONH.ORGANIZATION_ID WAREHOUSE_ID
, MP.ORGANIZATION_CODE WAREHOUSE
, ONH.INVENTORY_ITEM_ID ITEM_ID
, MSIBK.CONCATENATED_SEGMENTS ITEM
, MSIBK.DESCRIPTION ITEM_DESCRIPTION
, ONH.REVISION REVISION
, ONH.SUBINVENTORY_CODE SUBINVENTORY
, MILK.CONCATENATED_SEGMENTS LOCATOR
, ONH.LOT_NUMBER LOT
, WLPN1.LICENSE_PLATE_NUMBER LPN
, WLPN2.LICENSE_PLATE_NUMBER PARENT_LPN
, ONH.SERIAL_NUMBER SERIAL
, DECODE(ONH.LPN_ID
, NULL
, 'NO'
, 'YES') CONTAINERIZED_FLAG
, ONH.PRIMARY_QUANTITY PRIMARY_QUANTITY
, MUOM1.UNIT_OF_MEASURE_TL PRIMARY_UOM
, DECODE(MSIBK.TRACKING_QUANTITY_IND
, 'PS'
, ONH.SECONDARY_QUANTITY
, NULL) SECONDARY_QUANTITY
, DECODE(MSIBK.TRACKING_QUANTITY_IND
, 'PS'
, MUOM2.UNIT_OF_MEASURE_TL
, NULL) SECONDARY_UOM
, SYSDATE SNAPSHOT_DATE
, MMS.STATUS_CODE ONHAND_STATUS
FROM (SELECT MIC.CATEGORY_ID CATEGORY_ID
, MOQ.ORGANIZATION_ID ORGANIZATION_ID
, MOQ.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MOQ.REVISION REVISION
, MOQ.SUBINVENTORY_CODE SUBINVENTORY_CODE
, MOQ.LOCATOR_ID LOCATOR_ID
, MOQ.LOT_NUMBER LOT_NUMBER
, MOQ.LPN_ID LPN_ID
, NULL SERIAL_NUMBER
, DECODE(MOQ.STATUS_ID
, NULL
, INV_MATERIAL_STATUS_GRP.GET_DEFAULT_STATUS_CONC(MOQ.ORGANIZATION_ID
, MOQ.INVENTORY_ITEM_ID
, MOQ.SUBINVENTORY_CODE
, MOQ.LOCATOR_ID
, MOQ.LOT_NUMBER
, MOQ.LPN_ID)
, MOQ.STATUS_ID) STATUS_ID
, SUM(MOQ.PRIMARY_TRANSACTION_QUANTITY) PRIMARY_QUANTITY
, SUM(MOQ.SECONDARY_TRANSACTION_QUANTITY) SECONDARY_QUANTITY
FROM MTL_ONHAND_QUANTITIES_DETAIL MOQ
, MTL_SYSTEM_ITEMS_B MSI
, MTL_ITEM_CATEGORIES MIC
WHERE MSI.INVENTORY_ITEM_ID = MOQ.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MOQ.ORGANIZATION_ID
AND MSI.SERIAL_NUMBER_CONTROL_CODE IN (1
, 6)
AND MIC.INVENTORY_ITEM_ID = MOQ.INVENTORY_ITEM_ID
AND MIC.ORGANIZATION_ID = MOQ.ORGANIZATION_ID GROUP BY MIC.CATEGORY_ID
, MOQ.ORGANIZATION_ID
, MOQ.INVENTORY_ITEM_ID
, MOQ.REVISION
, MOQ.SUBINVENTORY_CODE
, MOQ.LOCATOR_ID
, MOQ.LOT_NUMBER
, MOQ.LPN_ID
, MOQ.STATUS_ID UNION ALL SELECT MIC.CATEGORY_ID CATEGORY_ID
, MSN.CURRENT_ORGANIZATION_ID ORGANIZATION_ID
, MSN.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MSN.REVISION REVISION
, MSN.CURRENT_SUBINVENTORY_CODE SUBINVENTORY_CODE
, MSN.CURRENT_LOCATOR_ID LOCATOR_ID
, MSN.LOT_NUMBER LOT_NUMBER
, MSN.LPN_ID LPN_ID
, MSN.SERIAL_NUMBER SERIAL_NUMBER
, MSN.STATUS_ID STATUS_ID
, 1 PRIMARY_QUANTITY
, NULL SECONDARY_QUANTITY
FROM MTL_SERIAL_NUMBERS MSN
, MTL_SYSTEM_ITEMS_B MSI
, MTL_ITEM_CATEGORIES MIC
WHERE MSN.CURRENT_STATUS = 3
AND MSI.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MSN.CURRENT_ORGANIZATION_ID
AND MSI.SERIAL_NUMBER_CONTROL_CODE NOT IN (1
, 6)
AND MIC.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND MIC.ORGANIZATION_ID = MSN.CURRENT_ORGANIZATION_ID ) ONH
, MTL_CATEGORIES_KFV MCK
, MTL_PARAMETERS MP
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, MTL_ITEM_LOCATIONS_KFV MILK
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
, MTL_UNITS_OF_MEASURE MUOM1
, MTL_UNITS_OF_MEASURE MUOM2
, MTL_MATERIAL_STATUSES MMS
WHERE MCK.CATEGORY_ID = ONH.CATEGORY_ID
AND MP.ORGANIZATION_ID = ONH.ORGANIZATION_ID
AND MSIBK.ORGANIZATION_ID = ONH.ORGANIZATION_ID
AND MSIBK.INVENTORY_ITEM_ID = ONH.INVENTORY_ITEM_ID
AND MILK.ORGANIZATION_ID (+) = ONH.ORGANIZATION_ID
AND MILK.INVENTORY_LOCATION_ID (+) = ONH.LOCATOR_ID
AND MMS.STATUS_ID (+) = ONH.STATUS_ID
AND WLPN1.LPN_ID (+) = ONH.LPN_ID
AND WLPN2.LPN_ID (+) = WLPN1.PARENT_LPN_ID
AND MUOM1.UOM_CODE = MSIBK.PRIMARY_UOM_CODE
AND MUOM2.UOM_CODE (+) = MSIBK.SECONDARY_UOM_CODE