VIEW: APPS.WSH_TRIP_DELIVERIES_V

Source

View Text - Preformatted

SELECT t.trip_id, to_number(null), to_number(null), d.row_id, d.delivery_id, d.name, d.planned_flag, d.status_code, d.status_name, d.initial_pickup_date, d.initial_pickup_location_id, d.ultimate_dropoff_location_id, d.ultimate_dropoff_date, d.customer_id, d.intmed_ship_to_location_id, d.pooled_ship_to_location_id, d.freight_terms_code, d.fob_code, d.fob_location_id, d.waybill, d.acceptance_flag, d.accepted_by, d.accepted_date, d.acknowledged_by, d.confirmed_by, d.asn_date_sent, d.asn_status_code, d.asn_seq_number, d.wv_frozen_flag, d.gross_weight, d.tare_weight, d.net_weight, d.weight_uom_code, d.volume, d.volume_uom_code, d.additional_shipment_info, d.confirm_date, d.ship_method_code, d.dock_code, d.delivery_type, d.carrier_id, d.currency_code, d.organization_id, d.loading_sequence, d.loading_order_flag, d.consolidation_flag, decode(pickup_stop.status_code, 'OP', 'RE', 'AR', 'LO', 'CL', decode(dropoff_stop.status_code, 'OP', 'OB', 'AR', 'UL', 'CL', 'CO')), d.attribute_category, d.attribute1, d.attribute2, d.attribute3, d.attribute4, d.attribute5, d.attribute6, d.attribute7, d.attribute8, d.attribute9, d.attribute10, d.attribute11, d.attribute12, d.attribute13, d.attribute14, d.attribute15, d.tp_attribute_category, d.tp_attribute1, d.tp_attribute2, d.tp_attribute3, d.tp_attribute4, d.tp_attribute5, d.tp_attribute6, d.tp_attribute7, d.tp_attribute8, d.tp_attribute9, d.tp_attribute10, d.tp_attribute11, d.tp_attribute12, d.tp_attribute13, d.tp_attribute14, d.tp_attribute15, d.global_attribute_category, d.global_attribute1, d.global_attribute2, d.global_attribute3, d.global_attribute4, d.global_attribute5, d.global_attribute6, d.global_attribute7, d.global_attribute8, d.global_attribute9, d.global_attribute10, d.global_attribute11, d.global_attribute12, d.global_attribute13, d.global_attribute14, d.global_attribute15, d.global_attribute16, d.global_attribute17, d.global_attribute18, d.global_attribute19, d.global_attribute20, d.creation_date, d.created_by, d.last_update_date, d.last_updated_by, d.last_update_login, d.program_application_id, d.program_id, d.program_update_date, d.request_id, d.number_of_lpn, d.cod_amount, d.cod_currency_code, d.cod_remit_to, d.cod_charge_paid_by, d.problem_contact_reference, d.port_of_loading, d.port_of_discharge, d.ftz_number, d.routed_export_txn, d.entry_number, d.routing_instructions, d.in_bond_code, d.shipping_marks, d.service_level, d.mode_of_transport, d.assigned_to_fte_trips, d.batch_id, d.ap_batch_id, d.auto_sc_exclude_flag, d.auto_ap_exclude_flag, d.tp_delivery_number, d.tp_plan_name, d.ignore_for_planning, d.party_id, d.routing_response_id, d.rcv_shipment_header_id, d.asn_shipment_header_id, d.shipment_direction, d.shipping_control, d.vendor_id, d.earliest_pickup_date, d.latest_pickup_date, d.earliest_dropoff_date, d.latest_dropoff_date, d.reason_of_transport, d.description, d.delivered_date, d.delivery_wf_process, d.delivery_scpod_wf_process, d.tms_version_number, d.tms_interface_flag, d.pending_advice_flag, d.client_id, d.consignee_flag from wsh_new_deliveries_v d, wsh_delivery_legs dl, wsh_trip_stops pickup_stop, wsh_trip_stops dropoff_stop, wsh_trips t WHERE dl.delivery_id = d.delivery_id and pickup_stop.stop_id = dl.pick_up_stop_id and dropoff_stop.stop_id = dl.drop_off_stop_id and pickup_stop.trip_id = t.trip_id and d.delivery_type = 'STANDARD'

View Text - HTML Formatted

SELECT T.TRIP_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, D.ROW_ID
, D.DELIVERY_ID
, D.NAME
, D.PLANNED_FLAG
, D.STATUS_CODE
, D.STATUS_NAME
, D.INITIAL_PICKUP_DATE
, D.INITIAL_PICKUP_LOCATION_ID
, D.ULTIMATE_DROPOFF_LOCATION_ID
, D.ULTIMATE_DROPOFF_DATE
, D.CUSTOMER_ID
, D.INTMED_SHIP_TO_LOCATION_ID
, D.POOLED_SHIP_TO_LOCATION_ID
, D.FREIGHT_TERMS_CODE
, D.FOB_CODE
, D.FOB_LOCATION_ID
, D.WAYBILL
, D.ACCEPTANCE_FLAG
, D.ACCEPTED_BY
, D.ACCEPTED_DATE
, D.ACKNOWLEDGED_BY
, D.CONFIRMED_BY
, D.ASN_DATE_SENT
, D.ASN_STATUS_CODE
, D.ASN_SEQ_NUMBER
, D.WV_FROZEN_FLAG
, D.GROSS_WEIGHT
, D.TARE_WEIGHT
, D.NET_WEIGHT
, D.WEIGHT_UOM_CODE
, D.VOLUME
, D.VOLUME_UOM_CODE
, D.ADDITIONAL_SHIPMENT_INFO
, D.CONFIRM_DATE
, D.SHIP_METHOD_CODE
, D.DOCK_CODE
, D.DELIVERY_TYPE
, D.CARRIER_ID
, D.CURRENCY_CODE
, D.ORGANIZATION_ID
, D.LOADING_SEQUENCE
, D.LOADING_ORDER_FLAG
, D.CONSOLIDATION_FLAG
, DECODE(PICKUP_STOP.STATUS_CODE
, 'OP'
, 'RE'
, 'AR'
, 'LO'
, 'CL'
, DECODE(DROPOFF_STOP.STATUS_CODE
, 'OP'
, 'OB'
, 'AR'
, 'UL'
, 'CL'
, 'CO'))
, D.ATTRIBUTE_CATEGORY
, D.ATTRIBUTE1
, D.ATTRIBUTE2
, D.ATTRIBUTE3
, D.ATTRIBUTE4
, D.ATTRIBUTE5
, D.ATTRIBUTE6
, D.ATTRIBUTE7
, D.ATTRIBUTE8
, D.ATTRIBUTE9
, D.ATTRIBUTE10
, D.ATTRIBUTE11
, D.ATTRIBUTE12
, D.ATTRIBUTE13
, D.ATTRIBUTE14
, D.ATTRIBUTE15
, D.TP_ATTRIBUTE_CATEGORY
, D.TP_ATTRIBUTE1
, D.TP_ATTRIBUTE2
, D.TP_ATTRIBUTE3
, D.TP_ATTRIBUTE4
, D.TP_ATTRIBUTE5
, D.TP_ATTRIBUTE6
, D.TP_ATTRIBUTE7
, D.TP_ATTRIBUTE8
, D.TP_ATTRIBUTE9
, D.TP_ATTRIBUTE10
, D.TP_ATTRIBUTE11
, D.TP_ATTRIBUTE12
, D.TP_ATTRIBUTE13
, D.TP_ATTRIBUTE14
, D.TP_ATTRIBUTE15
, D.GLOBAL_ATTRIBUTE_CATEGORY
, D.GLOBAL_ATTRIBUTE1
, D.GLOBAL_ATTRIBUTE2
, D.GLOBAL_ATTRIBUTE3
, D.GLOBAL_ATTRIBUTE4
, D.GLOBAL_ATTRIBUTE5
, D.GLOBAL_ATTRIBUTE6
, D.GLOBAL_ATTRIBUTE7
, D.GLOBAL_ATTRIBUTE8
, D.GLOBAL_ATTRIBUTE9
, D.GLOBAL_ATTRIBUTE10
, D.GLOBAL_ATTRIBUTE11
, D.GLOBAL_ATTRIBUTE12
, D.GLOBAL_ATTRIBUTE13
, D.GLOBAL_ATTRIBUTE14
, D.GLOBAL_ATTRIBUTE15
, D.GLOBAL_ATTRIBUTE16
, D.GLOBAL_ATTRIBUTE17
, D.GLOBAL_ATTRIBUTE18
, D.GLOBAL_ATTRIBUTE19
, D.GLOBAL_ATTRIBUTE20
, D.CREATION_DATE
, D.CREATED_BY
, D.LAST_UPDATE_DATE
, D.LAST_UPDATED_BY
, D.LAST_UPDATE_LOGIN
, D.PROGRAM_APPLICATION_ID
, D.PROGRAM_ID
, D.PROGRAM_UPDATE_DATE
, D.REQUEST_ID
, D.NUMBER_OF_LPN
, D.COD_AMOUNT
, D.COD_CURRENCY_CODE
, D.COD_REMIT_TO
, D.COD_CHARGE_PAID_BY
, D.PROBLEM_CONTACT_REFERENCE
, D.PORT_OF_LOADING
, D.PORT_OF_DISCHARGE
, D.FTZ_NUMBER
, D.ROUTED_EXPORT_TXN
, D.ENTRY_NUMBER
, D.ROUTING_INSTRUCTIONS
, D.IN_BOND_CODE
, D.SHIPPING_MARKS
, D.SERVICE_LEVEL
, D.MODE_OF_TRANSPORT
, D.ASSIGNED_TO_FTE_TRIPS
, D.BATCH_ID
, D.AP_BATCH_ID
, D.AUTO_SC_EXCLUDE_FLAG
, D.AUTO_AP_EXCLUDE_FLAG
, D.TP_DELIVERY_NUMBER
, D.TP_PLAN_NAME
, D.IGNORE_FOR_PLANNING
, D.PARTY_ID
, D.ROUTING_RESPONSE_ID
, D.RCV_SHIPMENT_HEADER_ID
, D.ASN_SHIPMENT_HEADER_ID
, D.SHIPMENT_DIRECTION
, D.SHIPPING_CONTROL
, D.VENDOR_ID
, D.EARLIEST_PICKUP_DATE
, D.LATEST_PICKUP_DATE
, D.EARLIEST_DROPOFF_DATE
, D.LATEST_DROPOFF_DATE
, D.REASON_OF_TRANSPORT
, D.DESCRIPTION
, D.DELIVERED_DATE
, D.DELIVERY_WF_PROCESS
, D.DELIVERY_SCPOD_WF_PROCESS
, D.TMS_VERSION_NUMBER
, D.TMS_INTERFACE_FLAG
, D.PENDING_ADVICE_FLAG
, D.CLIENT_ID
, D.CONSIGNEE_FLAG
FROM WSH_NEW_DELIVERIES_V D
, WSH_DELIVERY_LEGS DL
, WSH_TRIP_STOPS PICKUP_STOP
, WSH_TRIP_STOPS DROPOFF_STOP
, WSH_TRIPS T
WHERE DL.DELIVERY_ID = D.DELIVERY_ID
AND PICKUP_STOP.STOP_ID = DL.PICK_UP_STOP_ID
AND DROPOFF_STOP.STOP_ID = DL.DROP_OFF_STOP_ID
AND PICKUP_STOP.TRIP_ID = T.TRIP_ID
AND D.DELIVERY_TYPE = 'STANDARD'