VIEW: APPS.WSH_PURGE_SET_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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