View: WSH_STOP_DELIVERABLES_V

Product:	WSH - Shipping Execution
Description:
Implementation/DBA Data:	APPS.WSH_STOP_DELIVERABLES_V

View Text

SELECT /* DELIVERABLES TO PICK UP */ TO_NUMBER(NULL)
, TS.STOP_ID
, DLVB.ROW_ID
, DLVB.TYPE
, DLVB.DELIVERY_DETAIL_ID
, DLVB.DELIVERY_LINE_ID
, DLVB.CONTAINER_INSTANCE_ID
, DLVB.CONTAINER_NAME
, DLVB.DELIVERY_ID
, DLVB.SOURCE_CODE
, DLVB.SOURCE_NAME
, DLVB.SOURCE_HEADER_ID
, DLVB.SOURCE_LINE_ID
, DLVB.SOURCE_HEADER_NUMBER
, DLVB.SOURCE_HEADER_TYPE_ID
, DLVB.SOURCE_HEADER_TYPE_NAME
, DLVB.SOURCE_LINE_NUMBER
, DLVB.SRC_REQUESTED_QUANTITY
, DLVB.SRC_REQUESTED_QUANTITY_UOM
, DLVB.CUSTOMER_ID
, DLVB.SOLD_TO_CONTACT_ID
, DLVB.INVENTORY_ITEM_ID
, DLVB.ITEM_DESCRIPTION
, DLVB.COUNTRY_OF_ORIGIN
, DLVB.CLASSIFICATION
, DLVB.SHIP_FROM_LOCATION_ID
, DLVB.SHIP_TO_LOCATION_ID
, DLVB.SHIP_TO_CONTACT_ID
, DLVB.DELIVER_TO_LOCATION_ID
, DLVB.DELIVER_TO_CONTACT_ID
, DLVB.INTMED_SHIP_TO_LOCATION_ID
, DLVB.INTMED_SHIP_TO_CONTACT_ID
, DLVB.SHIP_TOLERANCE_ABOVE
, DLVB.SHIP_TOLERANCE_BELOW
, DLVB.REQUESTED_QUANTITY
, DLVB.SHIPPED_QUANTITY
, DLVB.DELIVERED_QUANTITY
, DLVB.CANCELLED_QUANTITY
, DLVB.REQUESTED_QUANTITY_UOM
, DLVB.SUBINVENTORY
, DLVB.REVISION
, DLVB.LOT_NUMBER
, DLVB.CUSTOMER_REQUESTED_LOT_FLAG
, DLVB.SERIAL_NUMBER
, DLVB.LOCATOR_ID
, DLVB.DATE_REQUESTED
, DLVB.DATE_SCHEDULED
, DLVB.MASTER_CONTAINER_ITEM_ID
, DLVB.DETAIL_CONTAINER_ITEM_ID
, DLVB.LOAD_SEQ_NUMBER
, DLVB.SHIP_METHOD_CODE
, DLVB.CARRIER_ID
, DLVB.FREIGHT_TERMS_CODE
, DLVB.SHIPMENT_PRIORITY_CODE
, DLVB.FOB_CODE
, DLVB.CUSTOMER_ITEM_ID
, DLVB.DEP_PLAN_REQUIRED_FLAG
, DLVB.CUSTOMER_PROD_SEQ
, DLVB.CUSTOMER_DOCK_CODE
, DLVB.UNIT_WEIGHT
, DLVB.UNIT_VOLUME
, DLVB.WV_FROZEN_FLAG
, DLVB.GROSS_WEIGHT
, DLVB.TARE_WEIGHT
, DLVB.NET_WEIGHT
, DLVB.WEIGHT_UOM_CODE
, DLVB.FILLED_VOLUME
, DLVB.VOLUME
, DLVB.VOLUME_UOM_CODE
, DLVB.FILL_PERCENT
, DLVB.MAXIMUM_LOAD_WEIGHT
, DLVB.MAXIMUM_VOLUME
, DLVB.MINIMUM_FILL_PERCENT
, DLVB.MVT_STAT_STATUS
, DLVB.RELEASED_STATUS
, DLVB.RELEASED_STATUS_NAME
, DLVB.ORGANIZATION_ID
, DLVB.TRANSACTION_TEMP_ID
, DLVB.SHIP_SET_ID
, DLVB.ARRIVAL_SET_ID
, DLVB.SHIP_MODEL_COMPLETE_FLAG
, DLVB.TOP_MODEL_LINE_ID
, DLVB.HOLD_CODE
, DLVB.CUST_PO_NUMBER
, DLVB.ATO_LINE_ID
, DLVB.MOVE_ORDER_LINE_ID
, DLVB.HAZARD_CLASS_ID
, DLVB.QUALITY_CONTROL_QUANTITY
, DLVB.CYCLE_COUNT_QUANTITY
, DLVB.TRACKING_NUMBER
, DLVB.MOVEMENT_ID
, DLVB.SHIPPING_INSTRUCTIONS
, DLVB.PACKING_INSTRUCTIONS
, DLVB.PROJECT_ID
, DLVB.TASK_ID
, DLVB.ORG_ID
, DLVB.OE_INTERFACED_FLAG
, DLVB.SPLIT_FROM_DELIVERY_DETAIL_ID
, DLVB.INV_INTERFACED_FLAG
, DLVB.PARENT_CONTAINER_INSTANCE_ID
, DLVB.MASTER_SERIAL_NUMBER
, DLVB.CONTAINER_FLAG
, DLVB.CONTAINER_TYPE_CODE
, DLVB.SEAL_CODE
, DECODE(TS.STATUS_CODE
, 'OP'
, 'AP'
, 'AR'
, 'AP'
, 'CL'
, 'PU')
, DLVB.ATTRIBUTE_CATEGORY
, DLVB.ATTRIBUTE1
, DLVB.ATTRIBUTE2
, DLVB.ATTRIBUTE3
, DLVB.ATTRIBUTE4
, DLVB.ATTRIBUTE5
, DLVB.ATTRIBUTE6
, DLVB.ATTRIBUTE7
, DLVB.ATTRIBUTE8
, DLVB.ATTRIBUTE9
, DLVB.ATTRIBUTE10
, DLVB.ATTRIBUTE11
, DLVB.ATTRIBUTE12
, DLVB.ATTRIBUTE13
, DLVB.ATTRIBUTE14
, DLVB.ATTRIBUTE15
, DLVB.TP_ATTRIBUTE_CATEGORY
, DLVB.TP_ATTRIBUTE1
, DLVB.TP_ATTRIBUTE2
, DLVB.TP_ATTRIBUTE3
, DLVB.TP_ATTRIBUTE4
, DLVB.TP_ATTRIBUTE5
, DLVB.TP_ATTRIBUTE6
, DLVB.TP_ATTRIBUTE7
, DLVB.TP_ATTRIBUTE8
, DLVB.TP_ATTRIBUTE9
, DLVB.TP_ATTRIBUTE10
, DLVB.TP_ATTRIBUTE11
, DLVB.TP_ATTRIBUTE12
, DLVB.TP_ATTRIBUTE13
, DLVB.TP_ATTRIBUTE14
, DLVB.TP_ATTRIBUTE15
, DLVB.CREATION_DATE
, DLVB.CREATED_BY
, DLVB.LAST_UPDATE_DATE
, DLVB.LAST_UPDATED_BY
, DLVB.LAST_UPDATE_LOGIN
, DLVB.PROGRAM_APPLICATION_ID
, DLVB.PROGRAM_ID
, DLVB.PROGRAM_UPDATE_DATE
, DLVB.REQUEST_ID
, DLVB.PREFERRED_GRADE
, DLVB.SHIPPED_QUANTITY2
, DLVB.DELIVERED_QUANTITY2
, DLVB.CANCELLED_QUANTITY2
, DLVB.REQUESTED_QUANTITY2
, DLVB.REQUESTED_QUANTITY_UOM2
, DLVB.SRC_REQUESTED_QUANTITY2
, DLVB.SRC_REQUESTED_QUANTITY_UOM2
, DLVB.QUALITY_CONTROL_QUANTITY2
, DLVB.CYCLE_COUNT_QUANTITY2
, DLVB.UNIT_NUMBER
, DLVB.UNIT_PRICE
, DLVB.CURRENCY_CODE
, DLVB.FREIGHT_CLASS_CAT_ID
, DLVB.COMMODITY_CODE_CAT_ID
, DLVB.INSPECTION_FLAG
, DLVB.PICKABLE_FLAG
, DLVB.SHIP_TO_SITE_USE_ID
, DLVB.DELIVER_TO_SITE_USE_ID
, DLVB.TO_SERIAL_NUMBER
, DLVB.PICKED_QUANTITY
, DLVB.PICKED_QUANTITY2
, DLVB.CUSTOMER_PRODUCTION_LINE
, DLVB.CUSTOMER_JOB
, DLVB.CUST_MODEL_SERIAL_NUMBER
, DLVB.RECEIVED_QUANTITY
, DLVB.RECEIVED_QUANTITY2
, DLVB.SOURCE_LINE_SET_ID
, DLVB.BATCH_ID
, DLVB.LPN_ID
, DLVB.ORIGINAL_SUBINVENTORY
, DLVB.SERVICE_LEVEL
, DLVB.MODE_OF_TRANSPORT
, DLVB.IGNORE_FOR_PLANNING
, DLVB.LINE_DIRECTION
, DLVB.PARTY_ID
, DLVB.ROUTING_REQ_ID
, DLVB.SHIPPING_CONTROL
, DLVB.SOURCE_BLANKET_REFERENCE_ID
, DLVB.SOURCE_BLANKET_REFERENCE_NUM
, DLVB.PO_SHIPMENT_LINE_ID
, DLVB.PO_SHIPMENT_LINE_NUMBER
, DLVB.RETURNED_QUANTITY
, DLVB.RETURNED_QUANTITY2
, DLVB.SOURCE_LINE_TYPE_CODE
, DLVB.RCV_SHIPMENT_LINE_ID
, DLVB.SUPPLIER_ITEM_NUMBER
, DLVB.VENDOR_ID
, DLVB.SHIP_FROM_SITE_ID
, DLVB.EARLIEST_PICKUP_DATE
, DLVB.LATEST_PICKUP_DATE
, DLVB.EARLIEST_DROPOFF_DATE
, DLVB.LATEST_DROPOFF_DATE
, DLVB.REQUEST_DATE_TYPE_CODE
, DLVB.TP_DELIVERY_DETAIL_ID
, DLVB.SOURCE_DOCUMENT_TYPE_ID
, DLVB.PO_REVISION_NUMBER
, DLVB.RELEASE_REVISION_NUMBER
FROM WSH_DLVY_DELIVERABLES_V DLVB
, WSH_NEW_DELIVERIES D
, WSH_DELIVERY_LEGS DL
, WSH_TRIP_STOPS TS
WHERE DLVB.DELIVERY_ID = D.DELIVERY_ID AND D.DELIVERY_ID = DL.DELIVERY_ID AND TS.STOP_ID = DL.PICK_UP_STOP_ID
AND NVL(D.SHIPMENT_DIRECTION
, 'O') IN ('O'
, 'IO') /*--*/ UNION SELECT /* DELIVERABLES TO DROP OFF */ TO_NUMBER(NULL)
, TS.STOP_ID
, DLVB.ROW_ID
, DLVB.TYPE
, DLVB.DELIVERY_DETAIL_ID
, DLVB.DELIVERY_LINE_ID
, DLVB.CONTAINER_INSTANCE_ID
, DLVB.CONTAINER_NAME
, DLVB.DELIVERY_ID
, DLVB.SOURCE_CODE
, DLVB.SOURCE_NAME
, DLVB.SOURCE_HEADER_ID
, DLVB.SOURCE_LINE_ID
, DLVB.SOURCE_HEADER_NUMBER
, DLVB.SOURCE_HEADER_TYPE_ID
, DLVB.SOURCE_HEADER_TYPE_NAME
, DLVB.SOURCE_LINE_NUMBER
, DLVB.SRC_REQUESTED_QUANTITY
, DLVB.SRC_REQUESTED_QUANTITY_UOM
, DLVB.CUSTOMER_ID
, DLVB.SOLD_TO_CONTACT_ID
, DLVB.INVENTORY_ITEM_ID
, DLVB.ITEM_DESCRIPTION
, DLVB.COUNTRY_OF_ORIGIN
, DLVB.CLASSIFICATION
, DLVB.SHIP_FROM_LOCATION_ID
, DLVB.SHIP_TO_LOCATION_ID
, DLVB.SHIP_TO_CONTACT_ID
, DLVB.DELIVER_TO_LOCATION_ID
, DLVB.DELIVER_TO_CONTACT_ID
, DLVB.INTMED_SHIP_TO_LOCATION_ID
, DLVB.INTMED_SHIP_TO_CONTACT_ID
, DLVB.SHIP_TOLERANCE_ABOVE
, DLVB.SHIP_TOLERANCE_BELOW
, DLVB.REQUESTED_QUANTITY
, DLVB.SHIPPED_QUANTITY
, DLVB.DELIVERED_QUANTITY
, DLVB.CANCELLED_QUANTITY
, DLVB.REQUESTED_QUANTITY_UOM
, DLVB.SUBINVENTORY
, DLVB.REVISION
, DLVB.LOT_NUMBER
, DLVB.CUSTOMER_REQUESTED_LOT_FLAG
, DLVB.SERIAL_NUMBER
, DLVB.LOCATOR_ID
, DLVB.DATE_REQUESTED
, DLVB.DATE_SCHEDULED
, DLVB.MASTER_CONTAINER_ITEM_ID
, DLVB.DETAIL_CONTAINER_ITEM_ID
, DLVB.LOAD_SEQ_NUMBER
, DLVB.SHIP_METHOD_CODE
, DLVB.CARRIER_ID
, DLVB.FREIGHT_TERMS_CODE
, DLVB.SHIPMENT_PRIORITY_CODE
, DLVB.FOB_CODE
, DLVB.CUSTOMER_ITEM_ID
, DLVB.DEP_PLAN_REQUIRED_FLAG
, DLVB.CUSTOMER_PROD_SEQ
, DLVB.CUSTOMER_DOCK_CODE
, DLVB.UNIT_WEIGHT
, DLVB.UNIT_VOLUME
, DLVB.WV_FROZEN_FLAG
, DLVB.GROSS_WEIGHT
, DLVB.TARE_WEIGHT
, DLVB.NET_WEIGHT
, DLVB.WEIGHT_UOM_CODE
, DLVB.FILLED_VOLUME
, DLVB.VOLUME
, DLVB.VOLUME_UOM_CODE
, DLVB.FILL_PERCENT
, DLVB.MAXIMUM_LOAD_WEIGHT
, DLVB.MAXIMUM_VOLUME
, DLVB.MINIMUM_FILL_PERCENT
, DLVB.MVT_STAT_STATUS
, DLVB.RELEASED_STATUS
, DLVB.RELEASED_STATUS_NAME
, DLVB.ORGANIZATION_ID
, DLVB.TRANSACTION_TEMP_ID
, DLVB.SHIP_SET_ID
, DLVB.ARRIVAL_SET_ID
, DLVB.SHIP_MODEL_COMPLETE_FLAG
, DLVB.TOP_MODEL_LINE_ID
, DLVB.HOLD_CODE
, DLVB.CUST_PO_NUMBER
, DLVB.ATO_LINE_ID
, DLVB.MOVE_ORDER_LINE_ID
, DLVB.HAZARD_CLASS_ID
, DLVB.QUALITY_CONTROL_QUANTITY
, DLVB.CYCLE_COUNT_QUANTITY
, DLVB.TRACKING_NUMBER
, DLVB.MOVEMENT_ID
, DLVB.SHIPPING_INSTRUCTIONS
, DLVB.PACKING_INSTRUCTIONS
, DLVB.PROJECT_ID
, DLVB.TASK_ID
, DLVB.ORG_ID
, DLVB.OE_INTERFACED_FLAG
, DLVB.SPLIT_FROM_DELIVERY_DETAIL_ID
, DLVB.INV_INTERFACED_FLAG
, DLVB.PARENT_CONTAINER_INSTANCE_ID
, DLVB.MASTER_SERIAL_NUMBER
, DLVB.CONTAINER_FLAG
, DLVB.CONTAINER_TYPE_CODE
, DLVB.SEAL_CODE
, DECODE(TS.STATUS_CODE
, 'OP'
, 'AD'
, 'AR'
, 'AD'
, 'CL'
, 'DO')
, DLVB.ATTRIBUTE_CATEGORY
, DLVB.ATTRIBUTE1
, DLVB.ATTRIBUTE2
, DLVB.ATTRIBUTE3
, DLVB.ATTRIBUTE4
, DLVB.ATTRIBUTE5
, DLVB.ATTRIBUTE6
, DLVB.ATTRIBUTE7
, DLVB.ATTRIBUTE8
, DLVB.ATTRIBUTE9
, DLVB.ATTRIBUTE10
, DLVB.ATTRIBUTE11
, DLVB.ATTRIBUTE12
, DLVB.ATTRIBUTE13
, DLVB.ATTRIBUTE14
, DLVB.ATTRIBUTE15
, DLVB.TP_ATTRIBUTE_CATEGORY
, DLVB.TP_ATTRIBUTE1
, DLVB.TP_ATTRIBUTE2
, DLVB.TP_ATTRIBUTE3
, DLVB.TP_ATTRIBUTE4
, DLVB.TP_ATTRIBUTE5
, DLVB.TP_ATTRIBUTE6
, DLVB.TP_ATTRIBUTE7
, DLVB.TP_ATTRIBUTE8
, DLVB.TP_ATTRIBUTE9
, DLVB.TP_ATTRIBUTE10
, DLVB.TP_ATTRIBUTE11
, DLVB.TP_ATTRIBUTE12
, DLVB.TP_ATTRIBUTE13
, DLVB.TP_ATTRIBUTE14
, DLVB.TP_ATTRIBUTE15
, DLVB.CREATION_DATE
, DLVB.CREATED_BY
, DLVB.LAST_UPDATE_DATE
, DLVB.LAST_UPDATED_BY
, DLVB.LAST_UPDATE_LOGIN
, DLVB.PROGRAM_APPLICATION_ID
, DLVB.PROGRAM_ID
, DLVB.PROGRAM_UPDATE_DATE
, DLVB.REQUEST_ID
, DLVB.PREFERRED_GRADE
, DLVB.SHIPPED_QUANTITY2
, DLVB.DELIVERED_QUANTITY2
, DLVB.CANCELLED_QUANTITY2
, DLVB.REQUESTED_QUANTITY2
, DLVB.REQUESTED_QUANTITY_UOM2
, DLVB.SRC_REQUESTED_QUANTITY2
, DLVB.SRC_REQUESTED_QUANTITY_UOM2
, DLVB.QUALITY_CONTROL_QUANTITY2
, DLVB.CYCLE_COUNT_QUANTITY2
, DLVB.UNIT_NUMBER
, DLVB.UNIT_PRICE
, DLVB.CURRENCY_CODE
, DLVB.FREIGHT_CLASS_CAT_ID
, DLVB.COMMODITY_CODE_CAT_ID
, DLVB.INSPECTION_FLAG
, DLVB.PICKABLE_FLAG
, DLVB.SHIP_TO_SITE_USE_ID
, DLVB.DELIVER_TO_SITE_USE_ID
, DLVB.TO_SERIAL_NUMBER
, DLVB.PICKED_QUANTITY
, DLVB.PICKED_QUANTITY2
, DLVB.CUSTOMER_PRODUCTION_LINE
, DLVB.CUSTOMER_JOB
, DLVB.CUST_MODEL_SERIAL_NUMBER
, DLVB.RECEIVED_QUANTITY
, DLVB.RECEIVED_QUANTITY2
, DLVB.SOURCE_LINE_SET_ID
, DLVB.BATCH_ID
, DLVB.LPN_ID
, DLVB.ORIGINAL_SUBINVENTORY
, DLVB.SERVICE_LEVEL
, DLVB.MODE_OF_TRANSPORT
, DLVB.IGNORE_FOR_PLANNING
, DLVB.LINE_DIRECTION
, DLVB.PARTY_ID
, DLVB.ROUTING_REQ_ID
, DLVB.SHIPPING_CONTROL
, DLVB.SOURCE_BLANKET_REFERENCE_ID
, DLVB.SOURCE_BLANKET_REFERENCE_NUM
, DLVB.PO_SHIPMENT_LINE_ID
, DLVB.PO_SHIPMENT_LINE_NUMBER
, DLVB.RETURNED_QUANTITY
, DLVB.RETURNED_QUANTITY2
, DLVB.SOURCE_LINE_TYPE_CODE
, DLVB.RCV_SHIPMENT_LINE_ID
, DLVB.SUPPLIER_ITEM_NUMBER
, DLVB.VENDOR_ID
, DLVB.SHIP_FROM_SITE_ID
, DLVB.EARLIEST_PICKUP_DATE
, DLVB.LATEST_PICKUP_DATE
, DLVB.EARLIEST_DROPOFF_DATE
, DLVB.LATEST_DROPOFF_DATE
, DLVB.REQUEST_DATE_TYPE_CODE
, DLVB.TP_DELIVERY_DETAIL_ID
, DLVB.SOURCE_DOCUMENT_TYPE_ID
, DLVB.PO_REVISION_NUMBER
, DLVB.RELEASE_REVISION_NUMBER
FROM WSH_DLVY_DELIVERABLES_V DLVB
, WSH_NEW_DELIVERIES D
, WSH_DELIVERY_LEGS DL
, WSH_TRIP_STOPS TS
WHERE DLVB.DELIVERY_ID = D.DELIVERY_ID AND D.DELIVERY_ID = DL.DELIVERY_ID AND TS.STOP_ID = DL.DROP_OFF_STOP_ID
AND NVL(D.SHIPMENT_DIRECTION
, 'O') IN ('O'
, 'IO')

Columns