View: WSH_PURGE_SET_V

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

View Text

SELECT WT.TRIP_ID TRIP_ID
, WT.NAME TRIP_NAME
, WT.STATUS_CODE TRIP_STATUS
, WT.CREATION_DATE TRIP_CREATION_DATE
, WT.SHIPMENTS_TYPE_FLAG TRIP_SHIPMENTS_TYPE
, WTS.STOP_ID PICKUP_STOP_ID
, WTS.SHIPMENTS_TYPE_FLAG PICKUP_STOP_SHIPMENTS_TYPE
, WTS.DEPARTURE_GROSS_WEIGHT PICKUP_STOP_GROSS_WEIGHT
, WTS.WEIGHT_UOM_CODE PICKUP_STOP_WEIGHT_UOM
, WGT_LKUP.UNIT_OF_MEASURE_TL PICKUP_STOP_WEIGHT_UOM_MEANING
, WTS.ACTUAL_DEPARTURE_DATE PICKUP_STOP_SHIP_DATE
, WDL.DELIVERY_LEG_ID LEG_ID
, WDL.GROSS_WEIGHT LEG_GROSS_WEIGHT
, WDL.WEIGHT_UOM_CODE LEG_WEIGHT_UOM
, WND.DELIVERY_ID DEL_ID
, WND.NAME DEL_NAME
, WND.ORGANIZATION_ID DEL_SHIP_FROM_ORG
, MP.ORGANIZATION_CODE DEL_SHIP_FROM_ORG_CODE
, WND.INITIAL_PICKUP_DATE DEL_PICKUP_DATE
, WND.CREATION_DATE DEL_CREATION_DATE
, WND.SHIPPING_CONTROL DEL_SHIPPING_CONTROL
, WND.SHIPMENT_DIRECTION DEL_SHIPMENT_DIRECTION
, WND.WAYBILL DEL_WAYBILL
, WND.GROSS_WEIGHT DEL_GROSS_WEIGHT
, WND.WEIGHT_UOM_CODE DEL_WEIGHT_UOM
, WND.CUSTOMER_ID DEL_CUSTOMER_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) DEL_CUSTOMER_NAME
, WND.ULTIMATE_DROPOFF_LOCATION_ID DEL_ULT_DO_LOCATION_ID
, WL.UI_LOCATION_CODE DEL_UI_LOCATION_CODE
, WND.ULTIMATE_DROPOFF_DATE DEL_DROPOFF_DATE
, WDD.DELIVERY_DETAIL_ID DD_ID
, WDD.LINE_DIRECTION DD_LINE_DIRECTION
, WDD.SHIPPING_CONTROL DD_SHIPPING_CONTROL
, WDD.SOURCE_CODE DD_SOURCE_CODE
, SRC_LKUP.MEANING DD_SOURCE_MEANING
, WDD.SOURCE_HEADER_ID DD_SOURCE_HEADER_ID
, WDD.SOURCE_HEADER_NUMBER DD_SOURCE_HEADER_NUMBER
, WDD.SOURCE_HEADER_TYPE_ID DD_SOURCE_HEADER_TYPE_ID
, WDD.SOURCE_HEADER_TYPE_NAME DD_SOURCE_HEADER_TYPE_NAME
, WDI.SEQUENCE_NUMBER BOL
, WND.INITIAL_PICKUP_DATE DEL_SHIP_DATE
, WDD.PO_SHIPMENT_LINE_ID DD_PO_SHIPMENT_LINE_ID
FROM WSH_TRIPS WT
, WSH_TRIP_STOPS WTS
, WSH_DELIVERY_LEGS WDL
, WSH_NEW_DELIVERIES WND
, WSH_DELIVERY_ASSIGNMENTS_V WDA
, WSH_DELIVERY_DETAILS WDD
, WSH_DOCUMENT_INSTANCES WDI
, MTL_UNITS_OF_MEASURE_VL WGT_LKUP
, MTL_PARAMETERS MP
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
, WSH_LOCATIONS WL
, WSH_LOOKUPS SRC_LKUP
WHERE WT.TRIP_ID = WTS.TRIP_ID
AND WTS.STOP_ID = WDL.PICK_UP_STOP_ID
AND WDL.DELIVERY_ID = WND.DELIVERY_ID
AND WDA.DELIVERY_ID = WND.DELIVERY_ID
AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
AND WDD.CONTAINER_FLAG ='N'
AND WDI.ENTITY_NAME(+) = 'WSH_DELIVERY_LEGS'
AND WDI.ENTITY_ID(+) = WDL.DELIVERY_LEG_ID
AND CUST_ACCT.CUST_ACCOUNT_ID(+) = WND.CUSTOMER_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID(+)
AND MP.ORGANIZATION_ID = WND.ORGANIZATION_ID
AND WGT_LKUP.UOM_CODE(+) = WTS.WEIGHT_UOM_CODE
AND NVL(WND.CONSIGNEE_FLAG
, 'C') = 'C'
AND WL.WSH_LOCATION_ID = WND.ULTIMATE_DROPOFF_LOCATION_ID
AND SRC_LKUP.LOOKUP_TYPE = 'SOURCE_SYSTEM'
AND SRC_LKUP.LOOKUP_CODE = WDD.SOURCE_CODE UNION SELECT WT.TRIP_ID TRIP_ID
, WT.NAME TRIP_NAME
, WT.STATUS_CODE TRIP_STATUS
, WT.CREATION_DATE TRIP_CREATION_DATE
, WT.SHIPMENTS_TYPE_FLAG TRIP_SHIPMENTS_TYPE
, WTS.STOP_ID PICKUP_STOP_ID
, WTS.SHIPMENTS_TYPE_FLAG PICKUP_STOP_SHIPMENTS_TYPE
, WTS.DEPARTURE_GROSS_WEIGHT PICKUP_STOP_GROSS_WEIGHT
, WTS.WEIGHT_UOM_CODE PICKUP_STOP_WEIGHT_UOM
, WGT_LKUP.UNIT_OF_MEASURE_TL PICKUP_STOP_WEIGHT_UOM_MEANING
, WTS.ACTUAL_DEPARTURE_DATE PICKUP_STOP_SHIP_DATE
, WDL.DELIVERY_LEG_ID LEG_ID
, WDL.GROSS_WEIGHT LEG_GROSS_WEIGHT
, WDL.WEIGHT_UOM_CODE LEG_WEIGHT_UOM
, WND.DELIVERY_ID DEL_ID
, WND.NAME DEL_NAME
, WND.ORGANIZATION_ID DEL_SHIP_FROM_ORG
, MP.ORGANIZATION_CODE DEL_SHIP_FROM_ORG_CODE
, WND.INITIAL_PICKUP_DATE DEL_PICKUP_DATE
, WND.CREATION_DATE DEL_CREATION_DATE
, WND.SHIPPING_CONTROL DEL_SHIPPING_CONTROL
, WND.SHIPMENT_DIRECTION DEL_SHIPMENT_DIRECTION
, WND.WAYBILL DEL_WAYBILL
, WND.GROSS_WEIGHT DEL_GROSS_WEIGHT
, WND.WEIGHT_UOM_CODE DEL_WEIGHT_UOM
, WND.CUSTOMER_ID DEL_CUSTOMER_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) DEL_CUSTOMER_NAME
, WND.ULTIMATE_DROPOFF_LOCATION_ID DEL_ULT_DO_LOCATION_ID
, WL.UI_LOCATION_CODE DEL_UI_LOCATION_CODE
, WND.ULTIMATE_DROPOFF_DATE DEL_DROPOFF_DATE
, WDD.DELIVERY_DETAIL_ID DD_ID
, WDD.LINE_DIRECTION DD_LINE_DIRECTION
, WDD.SHIPPING_CONTROL DD_SHIPPING_CONTROL
, WDD.SOURCE_CODE DD_SOURCE_CODE
, SRC_LKUP.MEANING DD_SOURCE_MEANING
, WDD.SOURCE_HEADER_ID DD_SOURCE_HEADER_ID
, WDD.SOURCE_HEADER_NUMBER DD_SOURCE_HEADER_NUMBER
, WDD.SOURCE_HEADER_TYPE_ID DD_SOURCE_HEADER_TYPE_ID
, WDD.SOURCE_HEADER_TYPE_NAME DD_SOURCE_HEADER_TYPE_NAME
, WDI.SEQUENCE_NUMBER BOL
, WND.INITIAL_PICKUP_DATE DEL_SHIP_DATE
, WDD.PO_SHIPMENT_LINE_ID DD_PO_SHIPMENT_LINE_ID
FROM WSH_TRIPS WT
, WSH_TRIP_STOPS WTS
, WSH_DELIVERY_LEGS WDL
, WSH_NEW_DELIVERIES WND
, WSH_DELIVERY_ASSIGNMENTS_V WDA
, WSH_DELIVERY_DETAILS WDD
, WSH_DOCUMENT_INSTANCES WDI
, MTL_UNITS_OF_MEASURE_VL WGT_LKUP
, MTL_PARAMETERS MP
, HZ_PARTIES PARTY
, PO_VENDORS POV
, WSH_LOCATIONS WL
, WSH_LOOKUPS SRC_LKUP
WHERE WT.TRIP_ID = WTS.TRIP_ID
AND WTS.STOP_ID = WDL.PICK_UP_STOP_ID
AND WDL.DELIVERY_ID = WND.DELIVERY_ID
AND WDA.DELIVERY_ID = WND.DELIVERY_ID
AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
AND WDD.CONTAINER_FLAG = 'N'
AND WDI.ENTITY_NAME(+) = 'WSH_DELIVERY_LEGS'
AND WDI.ENTITY_ID(+) = WDL.DELIVERY_LEG_ID
AND POV.VENDOR_ID(+) = WND.CUSTOMER_ID
AND NVL(WND.CONSIGNEE_FLAG
, 'C') = 'V'
AND POV.PARTY_ID = PARTY.PARTY_ID(+)
AND MP.ORGANIZATION_ID = WND.ORGANIZATION_ID
AND WGT_LKUP.UOM_CODE(+) = WTS.WEIGHT_UOM_CODE
AND WL.WSH_LOCATION_ID = WND.ULTIMATE_DROPOFF_LOCATION_ID
AND SRC_LKUP.LOOKUP_TYPE = 'SOURCE_SYSTEM'
AND SRC_LKUP.LOOKUP_CODE = WDD.SOURCE_CODE ORDER BY 1 ASC

Columns