VIEW: APPS.WSH_STOP_DELIVERABLES_V

Source

View Text - Preformatted

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, dlvb.replenishment_status 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, dlvb.replenishment_status 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')

View Text - HTML Formatted

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
, DLVB.REPLENISHMENT_STATUS
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
, DLVB.REPLENISHMENT_STATUS
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')