VIEW: APPS.WSH_SHIPPING_DETAILS_V

Source

View Text - Preformatted

SELECT ol.header_id order_header_id, ol.line_id order_line_id, wdd.delivery_detail_id picking_line_detail_id, wdd.shipped_quantity shipped_quantity, wdd.requested_quantity requested_quantity, to_number(NULL) pick_slip_number,hrloc.inventory_organization_id warehouse_id, wdd.mvt_stat_status mvt_stat_status, wdd.movement_id movement_id, wdd.released_status released_status, wdd.load_seq_number load_seq_number, wdd.customer_item_id customer_item_id, wdd.requested_quantity_uom requested_quantity_uom, wdd.requested_quantity_uom shipped_quantity_uom, ol.ship_to_org_id ship_to_site_use_id, wdd.ship_to_contact_id ship_to_contact_id, wdd.inventory_item_id inventory_item_id, 0 cancelled_quantity, to_number(NULL) picking_header_id, to_number(NULL) picking_line_id, to_number(NULL) sequence_number, ol.component_code component_code, to_number(NULL) component_ratio, decode(ol.item_type_code,'INCLUDED','Y','N') included_item_flag, wdd.date_requested date_requested, wdd.requested_quantity original_requested_quantity, wdd.shipment_priority_code shipment_priority_code, wnd.confirm_date confirm_date, to_char(NULL) ra_interface_status, to_number(NULL) invoiced_quantity, decode(wts.status_code,'CLOSED','INTERFACED',NULL) inventory_status, wdd.date_scheduled schedule_date, ol.demand_class_code demand_class_code, ol.component_sequence_id component_sequence_id, decode(ol.item_type_code,'CONFIG','Y','N') configuration_item_flag, ol.latest_acceptable_date latest_acceptable_date, wdd.attribute_category context, wdd.attribute1 attribute1, wdd.attribute2 attribute2, wdd.attribute3 attribute3, wdd.attribute4 attribute4, wdd.attribute5 attribute5, wdd.attribute6 attribute6, wdd.attribute7 attribute7, wdd.attribute8 attribute8, wdd.attribute9 attribute9, wdd.attribute10 attribute10, wdd.attribute11 attribute11, wdd.attribute12 attribute12, wdd.attribute13 attribute13, wdd.attribute14 attribute14, wdd.attribute15 attribute15, wdd.creation_date creation_date, wdd.created_by created_by, wdd.last_update_date last_update_date, wdd.last_updated_by last_updated_by, wdd.last_update_login last_update_login, wdd.program_application_id program_application_id, wdd.program_id program_id, wdd.program_update_date program_update_date, wdd.request_id request_id, wts.planned_departure_date planned_departure_date, wts.actual_departure_date actual_departure_date, wts.status_code departure_status_code, wnd.confirm_date date_closed, ol.ship_to_org_id ultimate_ship_to_id, wnd.name delivery_name, wnd.delivery_id delivery_id, wnd.status_code delivery_status_code, wdd.fob_code fob_code, wdd.ship_method_code freight_carrier_code, wts.status_code picking_hdr_status_code, decode(wnd.ultimate_dropoff_date,NULL,2,1) arrived_flag, wdd.ship_method_code ship_method_code, wnd.waybill waybill, ol.org_id org_id FROM wsh_lookups fl, oe_order_lines_all ol, oe_order_headers_all oh, wsh_delivery_details wdd, wsh_new_deliveries wnd, wsh_delivery_legs wlg, wsh_trip_stops wts, wsh_delivery_assignments_v wda, wsh_locations wsh_loc, hr_locations_all hrloc WHERE wnd.delivery_id = wda.delivery_id AND wda.delivery_detail_id = wdd.delivery_detail_id AND wdd.source_line_id = ol.line_id AND ol.header_id = oh.header_id AND wdd.source_code = fl.lookup_code AND fl.lookup_type = 'SOURCE_SYSTEM' AND fl.lookup_code = 'OE' AND wdd.ship_from_location_id = wsh_loc.wsh_location_id AND wsh_loc.location_source_code = 'HR' AND wsh_loc.source_location_id = hrloc.location_id AND wts.stop_id(+) = wlg.pick_up_stop_id AND wlg.delivery_id(+) = wnd.delivery_id AND NVL(wlg.sequence_number,-1) = (SELECT NVL(min(g.sequence_number),-1) FROM wsh_delivery_legs g WHERE g.delivery_id(+) = wnd.delivery_id ) AND OE_INSTALL.Get_Active_Product = 'ONT' AND wda.delivery_id is not null AND wnd.delivery_type = 'STANDARD'

View Text - HTML Formatted

SELECT OL.HEADER_ID ORDER_HEADER_ID
, OL.LINE_ID ORDER_LINE_ID
, WDD.DELIVERY_DETAIL_ID PICKING_LINE_DETAIL_ID
, WDD.SHIPPED_QUANTITY SHIPPED_QUANTITY
, WDD.REQUESTED_QUANTITY REQUESTED_QUANTITY
, TO_NUMBER(NULL) PICK_SLIP_NUMBER
, HRLOC.INVENTORY_ORGANIZATION_ID WAREHOUSE_ID
, WDD.MVT_STAT_STATUS MVT_STAT_STATUS
, WDD.MOVEMENT_ID MOVEMENT_ID
, WDD.RELEASED_STATUS RELEASED_STATUS
, WDD.LOAD_SEQ_NUMBER LOAD_SEQ_NUMBER
, WDD.CUSTOMER_ITEM_ID CUSTOMER_ITEM_ID
, WDD.REQUESTED_QUANTITY_UOM REQUESTED_QUANTITY_UOM
, WDD.REQUESTED_QUANTITY_UOM SHIPPED_QUANTITY_UOM
, OL.SHIP_TO_ORG_ID SHIP_TO_SITE_USE_ID
, WDD.SHIP_TO_CONTACT_ID SHIP_TO_CONTACT_ID
, WDD.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, 0 CANCELLED_QUANTITY
, TO_NUMBER(NULL) PICKING_HEADER_ID
, TO_NUMBER(NULL) PICKING_LINE_ID
, TO_NUMBER(NULL) SEQUENCE_NUMBER
, OL.COMPONENT_CODE COMPONENT_CODE
, TO_NUMBER(NULL) COMPONENT_RATIO
, DECODE(OL.ITEM_TYPE_CODE
, 'INCLUDED'
, 'Y'
, 'N') INCLUDED_ITEM_FLAG
, WDD.DATE_REQUESTED DATE_REQUESTED
, WDD.REQUESTED_QUANTITY ORIGINAL_REQUESTED_QUANTITY
, WDD.SHIPMENT_PRIORITY_CODE SHIPMENT_PRIORITY_CODE
, WND.CONFIRM_DATE CONFIRM_DATE
, TO_CHAR(NULL) RA_INTERFACE_STATUS
, TO_NUMBER(NULL) INVOICED_QUANTITY
, DECODE(WTS.STATUS_CODE
, 'CLOSED'
, 'INTERFACED'
, NULL) INVENTORY_STATUS
, WDD.DATE_SCHEDULED SCHEDULE_DATE
, OL.DEMAND_CLASS_CODE DEMAND_CLASS_CODE
, OL.COMPONENT_SEQUENCE_ID COMPONENT_SEQUENCE_ID
, DECODE(OL.ITEM_TYPE_CODE
, 'CONFIG'
, 'Y'
, 'N') CONFIGURATION_ITEM_FLAG
, OL.LATEST_ACCEPTABLE_DATE LATEST_ACCEPTABLE_DATE
, WDD.ATTRIBUTE_CATEGORY CONTEXT
, WDD.ATTRIBUTE1 ATTRIBUTE1
, WDD.ATTRIBUTE2 ATTRIBUTE2
, WDD.ATTRIBUTE3 ATTRIBUTE3
, WDD.ATTRIBUTE4 ATTRIBUTE4
, WDD.ATTRIBUTE5 ATTRIBUTE5
, WDD.ATTRIBUTE6 ATTRIBUTE6
, WDD.ATTRIBUTE7 ATTRIBUTE7
, WDD.ATTRIBUTE8 ATTRIBUTE8
, WDD.ATTRIBUTE9 ATTRIBUTE9
, WDD.ATTRIBUTE10 ATTRIBUTE10
, WDD.ATTRIBUTE11 ATTRIBUTE11
, WDD.ATTRIBUTE12 ATTRIBUTE12
, WDD.ATTRIBUTE13 ATTRIBUTE13
, WDD.ATTRIBUTE14 ATTRIBUTE14
, WDD.ATTRIBUTE15 ATTRIBUTE15
, WDD.CREATION_DATE CREATION_DATE
, WDD.CREATED_BY CREATED_BY
, WDD.LAST_UPDATE_DATE LAST_UPDATE_DATE
, WDD.LAST_UPDATED_BY LAST_UPDATED_BY
, WDD.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, WDD.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, WDD.PROGRAM_ID PROGRAM_ID
, WDD.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, WDD.REQUEST_ID REQUEST_ID
, WTS.PLANNED_DEPARTURE_DATE PLANNED_DEPARTURE_DATE
, WTS.ACTUAL_DEPARTURE_DATE ACTUAL_DEPARTURE_DATE
, WTS.STATUS_CODE DEPARTURE_STATUS_CODE
, WND.CONFIRM_DATE DATE_CLOSED
, OL.SHIP_TO_ORG_ID ULTIMATE_SHIP_TO_ID
, WND.NAME DELIVERY_NAME
, WND.DELIVERY_ID DELIVERY_ID
, WND.STATUS_CODE DELIVERY_STATUS_CODE
, WDD.FOB_CODE FOB_CODE
, WDD.SHIP_METHOD_CODE FREIGHT_CARRIER_CODE
, WTS.STATUS_CODE PICKING_HDR_STATUS_CODE
, DECODE(WND.ULTIMATE_DROPOFF_DATE
, NULL
, 2
, 1) ARRIVED_FLAG
, WDD.SHIP_METHOD_CODE SHIP_METHOD_CODE
, WND.WAYBILL WAYBILL
, OL.ORG_ID ORG_ID
FROM WSH_LOOKUPS FL
, OE_ORDER_LINES_ALL OL
, OE_ORDER_HEADERS_ALL OH
, WSH_DELIVERY_DETAILS WDD
, WSH_NEW_DELIVERIES WND
, WSH_DELIVERY_LEGS WLG
, WSH_TRIP_STOPS WTS
, WSH_DELIVERY_ASSIGNMENTS_V WDA
, WSH_LOCATIONS WSH_LOC
, HR_LOCATIONS_ALL HRLOC
WHERE WND.DELIVERY_ID = WDA.DELIVERY_ID
AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
AND WDD.SOURCE_LINE_ID = OL.LINE_ID
AND OL.HEADER_ID = OH.HEADER_ID
AND WDD.SOURCE_CODE = FL.LOOKUP_CODE
AND FL.LOOKUP_TYPE = 'SOURCE_SYSTEM'
AND FL.LOOKUP_CODE = 'OE'
AND WDD.SHIP_FROM_LOCATION_ID = WSH_LOC.WSH_LOCATION_ID
AND WSH_LOC.LOCATION_SOURCE_CODE = 'HR'
AND WSH_LOC.SOURCE_LOCATION_ID = HRLOC.LOCATION_ID
AND WTS.STOP_ID(+) = WLG.PICK_UP_STOP_ID
AND WLG.DELIVERY_ID(+) = WND.DELIVERY_ID
AND NVL(WLG.SEQUENCE_NUMBER
, -1) = (SELECT NVL(MIN(G.SEQUENCE_NUMBER)
, -1)
FROM WSH_DELIVERY_LEGS G
WHERE G.DELIVERY_ID(+) = WND.DELIVERY_ID )
AND OE_INSTALL.GET_ACTIVE_PRODUCT = 'ONT'
AND WDA.DELIVERY_ID IS NOT NULL
AND WND.DELIVERY_TYPE = 'STANDARD'