VIEW: APPS.CSP_EXCESS_LISTS_V

Source

View Text - Preformatted

SELECT EXCESS_LINE_ID ,CSP.CREATED_BY ,CSP.CREATION_DATE ,CSP.LAST_UPDATED_BY ,CSP.LAST_UPDATE_DATE ,CSP.LAST_UPDATE_LOGIN ,CSP.ORGANIZATION_ID ,OOD.ORGANIZATION_NAME ,CSP.EXCESS_STATUS ,FL.MEANING ,CSP.SUBINVENTORY_CODE ,CSP.CONDITION_CODE ,CSP.INVENTORY_ITEM_ID ,MSI.CONCATENATED_SEGMENTS ITEM_NUMBER ,MSI.DESCRIPTION ITEM_DESCRIPTION ,PRLA.ITEM_REVISION ,MSI.PRIMARY_UNIT_OF_MEASURE ,MSI.PRIMARY_UOM_CODE ,CSP.EXCESS_QUANTITY ,PRHA.REQUISITION_HEADER_ID ,PRHA.SEGMENT1 REQUISITION_NUMBER ,CSP.REQUISITION_LINE_ID ,NVL(CSP.RETURNED_QUANTITY,0) ,NVL(CSP.CURRENT_RETURN_QTY,0) ,(CSP.EXCESS_QUANTITY - NVL(CSP.RETURNED_QUANTITY,0)) CURRENT_EXCESS_QUANTITY ,DECODE(NVL(CSP.SUBINVENTORY_CODE,'X'),'X',NVL(MSI.MAX_MINMAX_QUANTITY,0),NVL(MSUB.MAX_MINMAX_QUANTITY,0)) MAX_QUANTITY ,PRLA.NEED_BY_DATE ,PRLA.DELIVER_TO_LOCATION_ID ,CSI.OWNER_RESOURCE_TYPE ,CSI.OWNER_RESOURCE_ID ,CSI.RETURN_ORGANIZATION_ID ,CSI.RETURN_SUBINVENTORY_NAME ,L.MEANING ,MIP.INTRANSIT_TYPE ,CSP.ATTRIBUTE_CATEGORY ,CSP.ATTRIBUTE1 ,CSP.ATTRIBUTE2 ,CSP.ATTRIBUTE3 ,CSP.ATTRIBUTE4 ,CSP.ATTRIBUTE5 ,CSP.ATTRIBUTE6 ,CSP.ATTRIBUTE7 ,CSP.ATTRIBUTE8 ,CSP.ATTRIBUTE9 ,CSP.ATTRIBUTE10 ,CSP.ATTRIBUTE11 ,CSP.ATTRIBUTE12 ,CSP.ATTRIBUTE13 ,CSP.ATTRIBUTE14 ,CSP.ATTRIBUTE15 FROM MTL_SYSTEM_ITEMS_B_KFV MSI, ORG_ORGANIZATION_DEFINITIONS OOD, MTL_ITEM_SUB_INVENTORIES MSUB, CSP_SEC_INVENTORIES CSI, MTL_INTERORG_PARAMETERS MIP, FND_LOOKUPS L, FND_LOOKUPS FL, CSP_EXCESS_LISTS CSP, PO_REQUISITION_HEADERS_ALL PRHA, PO_REQUISITION_LINES_ALL PRLA WHERE MSI.INVENTORY_ITEM_ID = CSP.INVENTORY_ITEM_ID AND MSI.ORGANIZATION_ID = CSP.ORGANIZATION_ID AND MSI.ORGANIZATION_ID = OOD.ORGANIZATION_ID AND MSUB.INVENTORY_ITEM_ID(+) = CSP.INVENTORY_ITEM_ID AND MSUB.ORGANIZATION_ID(+) = CSP.ORGANIZATION_ID AND MSUB.SECONDARY_INVENTORY(+) = CSP.SUBINVENTORY_CODE AND CSI.SECONDARY_INVENTORY_NAME(+) = CSP.SUBINVENTORY_CODE AND CSI.ORGANIZATION_ID(+) = CSP.ORGANIZATION_ID AND L.LOOKUP_CODE = CSP.CONDITION_CODE AND L.LOOKUP_TYPE = 'CSP_CONDITION_TYPE' AND L.ENABLED_FLAG = 'Y' AND FL.LOOKUP_CODE = CSP.EXCESS_STATUS AND FL.LOOKUP_TYPE = 'CSP_EXCESS_STATUS' AND FL.ENABLED_FLAG = 'Y' AND MIP.FROM_ORGANIZATION_ID(+) = CSI.ORGANIZATION_ID AND MIP.TO_ORGANIZATION_ID(+) = CSI.RETURN_ORGANIZATION_ID AND CSP.REQUISITION_LINE_ID = PRLA.REQUISITION_LINE_ID (+) AND PRLA.REQUISITION_HEADER_ID = PRHA.REQUISITION_HEADER_ID (+)

View Text - HTML Formatted

SELECT EXCESS_LINE_ID
, CSP.CREATED_BY
, CSP.CREATION_DATE
, CSP.LAST_UPDATED_BY
, CSP.LAST_UPDATE_DATE
, CSP.LAST_UPDATE_LOGIN
, CSP.ORGANIZATION_ID
, OOD.ORGANIZATION_NAME
, CSP.EXCESS_STATUS
, FL.MEANING
, CSP.SUBINVENTORY_CODE
, CSP.CONDITION_CODE
, CSP.INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSI.DESCRIPTION ITEM_DESCRIPTION
, PRLA.ITEM_REVISION
, MSI.PRIMARY_UNIT_OF_MEASURE
, MSI.PRIMARY_UOM_CODE
, CSP.EXCESS_QUANTITY
, PRHA.REQUISITION_HEADER_ID
, PRHA.SEGMENT1 REQUISITION_NUMBER
, CSP.REQUISITION_LINE_ID
, NVL(CSP.RETURNED_QUANTITY
, 0)
, NVL(CSP.CURRENT_RETURN_QTY
, 0)
, (CSP.EXCESS_QUANTITY - NVL(CSP.RETURNED_QUANTITY
, 0)) CURRENT_EXCESS_QUANTITY
, DECODE(NVL(CSP.SUBINVENTORY_CODE
, 'X')
, 'X'
, NVL(MSI.MAX_MINMAX_QUANTITY
, 0)
, NVL(MSUB.MAX_MINMAX_QUANTITY
, 0)) MAX_QUANTITY
, PRLA.NEED_BY_DATE
, PRLA.DELIVER_TO_LOCATION_ID
, CSI.OWNER_RESOURCE_TYPE
, CSI.OWNER_RESOURCE_ID
, CSI.RETURN_ORGANIZATION_ID
, CSI.RETURN_SUBINVENTORY_NAME
, L.MEANING
, MIP.INTRANSIT_TYPE
, CSP.ATTRIBUTE_CATEGORY
, CSP.ATTRIBUTE1
, CSP.ATTRIBUTE2
, CSP.ATTRIBUTE3
, CSP.ATTRIBUTE4
, CSP.ATTRIBUTE5
, CSP.ATTRIBUTE6
, CSP.ATTRIBUTE7
, CSP.ATTRIBUTE8
, CSP.ATTRIBUTE9
, CSP.ATTRIBUTE10
, CSP.ATTRIBUTE11
, CSP.ATTRIBUTE12
, CSP.ATTRIBUTE13
, CSP.ATTRIBUTE14
, CSP.ATTRIBUTE15
FROM MTL_SYSTEM_ITEMS_B_KFV MSI
, ORG_ORGANIZATION_DEFINITIONS OOD
, MTL_ITEM_SUB_INVENTORIES MSUB
, CSP_SEC_INVENTORIES CSI
, MTL_INTERORG_PARAMETERS MIP
, FND_LOOKUPS L
, FND_LOOKUPS FL
, CSP_EXCESS_LISTS CSP
, PO_REQUISITION_HEADERS_ALL PRHA
, PO_REQUISITION_LINES_ALL PRLA
WHERE MSI.INVENTORY_ITEM_ID = CSP.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = CSP.ORGANIZATION_ID
AND MSI.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND MSUB.INVENTORY_ITEM_ID(+) = CSP.INVENTORY_ITEM_ID
AND MSUB.ORGANIZATION_ID(+) = CSP.ORGANIZATION_ID
AND MSUB.SECONDARY_INVENTORY(+) = CSP.SUBINVENTORY_CODE
AND CSI.SECONDARY_INVENTORY_NAME(+) = CSP.SUBINVENTORY_CODE
AND CSI.ORGANIZATION_ID(+) = CSP.ORGANIZATION_ID
AND L.LOOKUP_CODE = CSP.CONDITION_CODE
AND L.LOOKUP_TYPE = 'CSP_CONDITION_TYPE'
AND L.ENABLED_FLAG = 'Y'
AND FL.LOOKUP_CODE = CSP.EXCESS_STATUS
AND FL.LOOKUP_TYPE = 'CSP_EXCESS_STATUS'
AND FL.ENABLED_FLAG = 'Y'
AND MIP.FROM_ORGANIZATION_ID(+) = CSI.ORGANIZATION_ID
AND MIP.TO_ORGANIZATION_ID(+) = CSI.RETURN_ORGANIZATION_ID
AND CSP.REQUISITION_LINE_ID = PRLA.REQUISITION_LINE_ID (+)
AND PRLA.REQUISITION_HEADER_ID = PRHA.REQUISITION_HEADER_ID (+)