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.AHL_OSP_INV_ITEMS_V

Source

View Text - Preformatted


SELECT mtl.organization_name ORG_NAME, mtl.inventory_org_id ORG_ID, mtls.CURRENT_SUBINVENTORY_CODE SUBINVENTORY, mtls.serial_number SERIAL_NUMBER, mtl.concatenated_segments ITEM_NUMBER, mtls.inventory_item_id ITEM_ID, mtl.primary_uom_code, mtls.lot_number LOT_NUMBER, csi.instance_id INSTANCE_ID, mtl.description ITEM_DESCRIPTION, 1 ONHAND_QUANTITY, 'SERIAL' CONTROL_FLAG FROM mtl_serial_numbers mtls, ahl_mtl_items_ou_v mtl, CSI_ITEM_INSTANCES csi WHERE mtls.inventory_item_id = mtl.inventory_item_id AND mtls.current_organization_id = mtl.inventory_org_id AND mtls.current_status = 3 AND mtls.inventory_item_id = csi.inventory_item_id(+) AND mtls.current_organization_id = csi.last_vld_organization_id(+) AND mtls.serial_number = csi.serial_number(+) UNION SELECT mtl.organization_name org_name, mtl.inventory_org_id org_id, mtlq.subinventory_code subinventory, NULL serial_number, mtl.concatenated_segments item_number, mtl.inventory_item_id item_id, mtl.primary_uom_code, mtlq.lot_number, NULL instance_id, mtl.description item_description, ahl_osp_queries_pvt.get_onhand_quantity (mtl.inventory_org_id, mtlq.subinventory_code, mtl.inventory_item_id, mtlq.lot_number ) onhand_quantity, 'NOT' control_flag FROM ahl_mtl_items_ou_v mtl, (SELECT organization_id, subinventory_code, inventory_item_id, lot_number FROM mtl_onhand_quantities GROUP BY organization_id, subinventory_code, inventory_item_id, lot_number) mtlq WHERE mtl.serial_nbr_cntrl_code NOT IN (2, 5, 6) AND mtl.inventory_item_id = mtlq.inventory_item_id AND mtl.inventory_org_id = mtlq.organization_id

View Text - HTML Formatted

SELECT MTL.ORGANIZATION_NAME ORG_NAME
, MTL.INVENTORY_ORG_ID ORG_ID
, MTLS.CURRENT_SUBINVENTORY_CODE SUBINVENTORY
, MTLS.SERIAL_NUMBER SERIAL_NUMBER
, MTL.CONCATENATED_SEGMENTS ITEM_NUMBER
, MTLS.INVENTORY_ITEM_ID ITEM_ID
, MTL.PRIMARY_UOM_CODE
, MTLS.LOT_NUMBER LOT_NUMBER
, CSI.INSTANCE_ID INSTANCE_ID
, MTL.DESCRIPTION ITEM_DESCRIPTION
, 1 ONHAND_QUANTITY
, 'SERIAL' CONTROL_FLAG
FROM MTL_SERIAL_NUMBERS MTLS
, AHL_MTL_ITEMS_OU_V MTL
, CSI_ITEM_INSTANCES CSI
WHERE MTLS.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID
AND MTLS.CURRENT_ORGANIZATION_ID = MTL.INVENTORY_ORG_ID
AND MTLS.CURRENT_STATUS = 3
AND MTLS.INVENTORY_ITEM_ID = CSI.INVENTORY_ITEM_ID(+)
AND MTLS.CURRENT_ORGANIZATION_ID = CSI.LAST_VLD_ORGANIZATION_ID(+)
AND MTLS.SERIAL_NUMBER = CSI.SERIAL_NUMBER(+) UNION SELECT MTL.ORGANIZATION_NAME ORG_NAME
, MTL.INVENTORY_ORG_ID ORG_ID
, MTLQ.SUBINVENTORY_CODE SUBINVENTORY
, NULL SERIAL_NUMBER
, MTL.CONCATENATED_SEGMENTS ITEM_NUMBER
, MTL.INVENTORY_ITEM_ID ITEM_ID
, MTL.PRIMARY_UOM_CODE
, MTLQ.LOT_NUMBER
, NULL INSTANCE_ID
, MTL.DESCRIPTION ITEM_DESCRIPTION
, AHL_OSP_QUERIES_PVT.GET_ONHAND_QUANTITY (MTL.INVENTORY_ORG_ID
, MTLQ.SUBINVENTORY_CODE
, MTL.INVENTORY_ITEM_ID
, MTLQ.LOT_NUMBER ) ONHAND_QUANTITY
, 'NOT' CONTROL_FLAG
FROM AHL_MTL_ITEMS_OU_V MTL
, (SELECT ORGANIZATION_ID
, SUBINVENTORY_CODE
, INVENTORY_ITEM_ID
, LOT_NUMBER
FROM MTL_ONHAND_QUANTITIES GROUP BY ORGANIZATION_ID
, SUBINVENTORY_CODE
, INVENTORY_ITEM_ID
, LOT_NUMBER) MTLQ
WHERE MTL.SERIAL_NBR_CNTRL_CODE NOT IN (2
, 5
, 6)
AND MTL.INVENTORY_ITEM_ID = MTLQ.INVENTORY_ITEM_ID
AND MTL.INVENTORY_ORG_ID = MTLQ.ORGANIZATION_ID