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_MTL_RET_DISPOSITIONS_V

Source

View Text - Preformatted


SELECT DISP.DISPOSITION_ID, DISP.WORKORDER_ID, WO.WORKORDER_NAME, NVL(DISP.WO_OPERATION_ID, PC.WORKORDER_OPERATION_ID) WORKORDER_OPERATION_ID, DISP.INSTANCE_ID, DISP.INVENTORY_ITEM_ID, DISP.ORGANIZATION_ID, DISP.ITEM_REVISION, MTL.CONCATENATED_SEGMENTS ITEM_NUMBER, MTL.DESCRIPTION ITEM_DESC, DISP.SERIAL_NUMBER, DISP.LOT_NUMBER, DISP.QUANTITY, DISP.UOM, DISP.IMMEDIATE_DISPOSITION_CODE, FND1.MEANING IMMEDIATE_TYPE, DISP.SECONDARY_DISPOSITION_CODE, FND2.MEANING SECONDARY_TYPE, DISP.CONDITION_ID, COND.STATUS_CODE CONDITION_CODE, DISP.STATUS_CODE, FND3.MEANING STATUS, DISP.COLLECTION_ID, CSI.QUANTITY - AHL_PRD_DISP_MTL_TXN_PVT.CALCULATE_TXNED_QTY(DISP.DISPOSITION_ID) NET_QUANTITY FROM AHL_PRD_DISPOSITIONS_B DISP, MTL_SYSTEM_ITEMS_KFV MTL, (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_IMMED_DISP_TYPE' AND LANGUAGE= USERENV('LANG')) FND1, (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_SECND_DISP_TYPE' AND LANGUAGE= USERENV('LANG')) FND2, (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_DISP_STATUS' AND LANGUAGE= USERENV('LANG')) FND3, (SELECT STATUS_ID, STATUS_CODE FROM MTL_MATERIAL_STATUSES_TL WHERE LANGUAGE= USERENV('LANG')) COND, AHL_WORKORDERS WO, CSI_ITEM_INSTANCES CSI, AHL_PART_CHANGES PC WHERE (DISP.INSTANCE_ID IS NOT NULL OR DISP.PATH_POSITION_ID IS NOT NULL) AND NVL(DISP.STATUS_CODE, 'X') NOT IN ('COMPLETE','TERMINATED') AND NVL(DISP.IMMEDIATE_DISPOSITION_CODE, 'X') <> 'NOT_RECEIVED' AND DISP.WORKORDER_ID = WO.WORKORDER_ID AND COND.STATUS_ID (+) = DISP.CONDITION_ID AND FND1.LOOKUP_CODE (+) = DISP.IMMEDIATE_DISPOSITION_CODE AND FND2.LOOKUP_CODE (+) = DISP.SECONDARY_DISPOSITION_CODE AND FND3.LOOKUP_CODE (+) = DISP.STATUS_CODE AND DISP.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID (+) AND DISP.ORGANIZATION_ID = MTL.ORGANIZATION_ID (+) AND WO.WIP_ENTITY_ID = CSI.WIP_JOB_ID AND NVL(CSI.LOCATION_TYPE_CODE, 'X') NOT IN ('PO','IN-TRANSIT','PROJECT','INVENTORY') AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(CSI.ACTIVE_START_DATE, SYSDATE)) AND TRUNC(NVL(CSI.ACTIVE_END_DATE, SYSDATE)) AND CSI.INSTANCE_ID = DISP.INSTANCE_ID AND PC.PART_CHANGE_ID (+) = DISP.PART_CHANGE_ID AND CSI.QUANTITY > AHL_PRD_DISP_MTL_TXN_PVT.CALCULATE_TXNED_QTY(DISP.DISPOSITION_ID) UNION ALL SELECT DISP.DISPOSITION_ID, DISP.WORKORDER_ID, WO.WORKORDER_NAME, DISP.WO_OPERATION_ID WORKORDER_OPERATION_ID, DISP.INSTANCE_ID, DISP.INVENTORY_ITEM_ID, DISP.ORGANIZATION_ID, DISP.ITEM_REVISION, MTL.CONCATENATED_SEGMENTS ITEM_NUMBER, MTL.DESCRIPTION ITEM_DESC, DISP.SERIAL_NUMBER, DISP.LOT_NUMBER, DISP.QUANTITY, DISP.UOM, DISP.IMMEDIATE_DISPOSITION_CODE, FND1.MEANING IMMEDIATE_TYPE, DISP.SECONDARY_DISPOSITION_CODE, FND2.MEANING SECONDARY_TYPE, DISP.CONDITION_ID, COND.STATUS_CODE CONDITION_CODE, DISP.STATUS_CODE, FND3.MEANING STATUS, DISP.COLLECTION_ID, DISP.QUANTITY - AHL_PRD_DISP_MTL_TXN_PVT.CALCULATE_TXNED_QTY(DISP.DISPOSITION_ID) NET_QUANTITY FROM AHL_PRD_DISPOSITIONS_B DISP, MTL_SYSTEM_ITEMS_KFV MTL, (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_IMMED_DISP_TYPE' AND LANGUAGE= USERENV('LANG')) FND1, (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_SECND_DISP_TYPE' AND LANGUAGE= USERENV('LANG')) FND2, (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_DISP_STATUS' AND LANGUAGE= USERENV('LANG')) FND3, (SELECT STATUS_ID, STATUS_CODE FROM MTL_MATERIAL_STATUSES_TL WHERE LANGUAGE= USERENV('LANG')) COND, AHL_WORKORDERS WO WHERE (DISP.INSTANCE_ID IS NULL AND DISP.PATH_POSITION_ID IS NULL) AND NVL(DISP.STATUS_CODE, 'X') NOT IN ('COMPLETE','TERMINATED') AND NVL(DISP.IMMEDIATE_DISPOSITION_CODE, 'X') <> 'NOT_RECEIVED' AND DISP.WORKORDER_ID = WO.WORKORDER_ID AND COND.STATUS_ID (+) = DISP.CONDITION_ID AND FND1.LOOKUP_CODE (+) = DISP.IMMEDIATE_DISPOSITION_CODE AND FND2.LOOKUP_CODE (+) = DISP.SECONDARY_DISPOSITION_CODE AND FND3.LOOKUP_CODE (+) = DISP.STATUS_CODE AND DISP.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID (+) AND DISP.ORGANIZATION_ID = MTL.ORGANIZATION_ID (+) AND DISP.QUANTITY > AHL_PRD_DISP_MTL_TXN_PVT.CALCULATE_TXNED_QTY(DISP.DISPOSITION_ID)

View Text - HTML Formatted

SELECT DISP.DISPOSITION_ID
, DISP.WORKORDER_ID
, WO.WORKORDER_NAME
, NVL(DISP.WO_OPERATION_ID
, PC.WORKORDER_OPERATION_ID) WORKORDER_OPERATION_ID
, DISP.INSTANCE_ID
, DISP.INVENTORY_ITEM_ID
, DISP.ORGANIZATION_ID
, DISP.ITEM_REVISION
, MTL.CONCATENATED_SEGMENTS ITEM_NUMBER
, MTL.DESCRIPTION ITEM_DESC
, DISP.SERIAL_NUMBER
, DISP.LOT_NUMBER
, DISP.QUANTITY
, DISP.UOM
, DISP.IMMEDIATE_DISPOSITION_CODE
, FND1.MEANING IMMEDIATE_TYPE
, DISP.SECONDARY_DISPOSITION_CODE
, FND2.MEANING SECONDARY_TYPE
, DISP.CONDITION_ID
, COND.STATUS_CODE CONDITION_CODE
, DISP.STATUS_CODE
, FND3.MEANING STATUS
, DISP.COLLECTION_ID
, CSI.QUANTITY - AHL_PRD_DISP_MTL_TXN_PVT.CALCULATE_TXNED_QTY(DISP.DISPOSITION_ID) NET_QUANTITY
FROM AHL_PRD_DISPOSITIONS_B DISP
, MTL_SYSTEM_ITEMS_KFV MTL
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_IMMED_DISP_TYPE'
AND LANGUAGE= USERENV('LANG')) FND1
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_SECND_DISP_TYPE'
AND LANGUAGE= USERENV('LANG')) FND2
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_DISP_STATUS'
AND LANGUAGE= USERENV('LANG')) FND3
, (SELECT STATUS_ID
, STATUS_CODE
FROM MTL_MATERIAL_STATUSES_TL
WHERE LANGUAGE= USERENV('LANG')) COND
, AHL_WORKORDERS WO
, CSI_ITEM_INSTANCES CSI
, AHL_PART_CHANGES PC
WHERE (DISP.INSTANCE_ID IS NOT NULL OR DISP.PATH_POSITION_ID IS NOT NULL)
AND NVL(DISP.STATUS_CODE
, 'X') NOT IN ('COMPLETE'
, 'TERMINATED')
AND NVL(DISP.IMMEDIATE_DISPOSITION_CODE
, 'X') <> 'NOT_RECEIVED'
AND DISP.WORKORDER_ID = WO.WORKORDER_ID
AND COND.STATUS_ID (+) = DISP.CONDITION_ID
AND FND1.LOOKUP_CODE (+) = DISP.IMMEDIATE_DISPOSITION_CODE
AND FND2.LOOKUP_CODE (+) = DISP.SECONDARY_DISPOSITION_CODE
AND FND3.LOOKUP_CODE (+) = DISP.STATUS_CODE
AND DISP.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID (+)
AND DISP.ORGANIZATION_ID = MTL.ORGANIZATION_ID (+)
AND WO.WIP_ENTITY_ID = CSI.WIP_JOB_ID
AND NVL(CSI.LOCATION_TYPE_CODE
, 'X') NOT IN ('PO'
, 'IN-TRANSIT'
, 'PROJECT'
, 'INVENTORY')
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(CSI.ACTIVE_START_DATE
, SYSDATE))
AND TRUNC(NVL(CSI.ACTIVE_END_DATE
, SYSDATE))
AND CSI.INSTANCE_ID = DISP.INSTANCE_ID
AND PC.PART_CHANGE_ID (+) = DISP.PART_CHANGE_ID
AND CSI.QUANTITY > AHL_PRD_DISP_MTL_TXN_PVT.CALCULATE_TXNED_QTY(DISP.DISPOSITION_ID) UNION ALL SELECT DISP.DISPOSITION_ID
, DISP.WORKORDER_ID
, WO.WORKORDER_NAME
, DISP.WO_OPERATION_ID WORKORDER_OPERATION_ID
, DISP.INSTANCE_ID
, DISP.INVENTORY_ITEM_ID
, DISP.ORGANIZATION_ID
, DISP.ITEM_REVISION
, MTL.CONCATENATED_SEGMENTS ITEM_NUMBER
, MTL.DESCRIPTION ITEM_DESC
, DISP.SERIAL_NUMBER
, DISP.LOT_NUMBER
, DISP.QUANTITY
, DISP.UOM
, DISP.IMMEDIATE_DISPOSITION_CODE
, FND1.MEANING IMMEDIATE_TYPE
, DISP.SECONDARY_DISPOSITION_CODE
, FND2.MEANING SECONDARY_TYPE
, DISP.CONDITION_ID
, COND.STATUS_CODE CONDITION_CODE
, DISP.STATUS_CODE
, FND3.MEANING STATUS
, DISP.COLLECTION_ID
, DISP.QUANTITY - AHL_PRD_DISP_MTL_TXN_PVT.CALCULATE_TXNED_QTY(DISP.DISPOSITION_ID) NET_QUANTITY
FROM AHL_PRD_DISPOSITIONS_B DISP
, MTL_SYSTEM_ITEMS_KFV MTL
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_IMMED_DISP_TYPE'
AND LANGUAGE= USERENV('LANG')) FND1
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_SECND_DISP_TYPE'
AND LANGUAGE= USERENV('LANG')) FND2
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_DISP_STATUS'
AND LANGUAGE= USERENV('LANG')) FND3
, (SELECT STATUS_ID
, STATUS_CODE
FROM MTL_MATERIAL_STATUSES_TL
WHERE LANGUAGE= USERENV('LANG')) COND
, AHL_WORKORDERS WO
WHERE (DISP.INSTANCE_ID IS NULL
AND DISP.PATH_POSITION_ID IS NULL)
AND NVL(DISP.STATUS_CODE
, 'X') NOT IN ('COMPLETE'
, 'TERMINATED')
AND NVL(DISP.IMMEDIATE_DISPOSITION_CODE
, 'X') <> 'NOT_RECEIVED'
AND DISP.WORKORDER_ID = WO.WORKORDER_ID
AND COND.STATUS_ID (+) = DISP.CONDITION_ID
AND FND1.LOOKUP_CODE (+) = DISP.IMMEDIATE_DISPOSITION_CODE
AND FND2.LOOKUP_CODE (+) = DISP.SECONDARY_DISPOSITION_CODE
AND FND3.LOOKUP_CODE (+) = DISP.STATUS_CODE
AND DISP.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID (+)
AND DISP.ORGANIZATION_ID = MTL.ORGANIZATION_ID (+)
AND DISP.QUANTITY > AHL_PRD_DISP_MTL_TXN_PVT.CALCULATE_TXNED_QTY(DISP.DISPOSITION_ID)