VIEW: APPS.WSH_SS_SHIPMENT_V

Source

View Text - Preformatted

SELECT DISTINCT WND.DELIVERY_ID DELIVERY_ID , WTH.ACTION_TYPE ACTION_CODE , WTH.DOCUMENT_NUMBER DOCUMENT_NUMBER , WTH.ENTITY_TYPE ENTITY_TYPE , TO_CHAR(NULL) CURRENCY_CODE , HRL.LOCATION_CODE SHIP_FROM_LOCATION , WND.SHIP_METHOD_CODE SHIP_METHOD , WL1.COUNTRY COUNTRY , WND.CREATION_DATE CREATION_DATE , WND.ULTIMATE_DROPOFF_DATE ULTIMATE_DROPOFF_DATE , WND.INITIAL_PICKUP_DATE INITIAL_PICKUP_DATE , WND.DESCRIPTION DESCRIPTION , WTH.DOCUMENT_TYPE DOCUMENT_TYPE , WND.FREIGHT_TERMS_CODE FREIGHT_TERMS , WND.GROSS_WEIGHT GROSS_WEIGHT , WND.NET_WEIGHT NET_WEIGHT , WND.WEIGHT_UOM_CODE WEIGHT_UOM_CODE , WND.NUMBER_OF_LPN NUMBER_OF_LPN , WND.VOLUME VOLUME , WND.VOLUME_UOM_CODE VOLUME_UOM_CODE , WND.SHIPPING_MARKS SHIPPING_MARKS , TO_CHAR(NULL) SHIP_TO_LOCATION , WND.FOB_CODE FOB_CODE , OOG.ORGANIZATION_CODE ORGANIZATION_CODE , WND.LOADING_SEQUENCE LOADING_SEQUENCE , WND.ATTRIBUTE_CATEGORY SHIPMENT_ATTRIBUTE_CATEGORY, WND.ATTRIBUTE1 SHIPMENT_ATTRIBUTE1 , WND.ATTRIBUTE2 SHIPMENT_ATTRIBUTE2 , WND.ATTRIBUTE3 SHIPMENT_ATTRIBUTE3 , WND.ATTRIBUTE4 SHIPMENT_ATTRIBUTE4 , WND.ATTRIBUTE5 SHIPMENT_ATTRIBUTE5 , WND.ATTRIBUTE6 SHIPMENT_ATTRIBUTE6 , WND.ATTRIBUTE7 SHIPMENT_ATTRIBUTE7 , WND.ATTRIBUTE8 SHIPMENT_ATTRIBUTE8 , WND.ATTRIBUTE9 SHIPMENT_ATTRIBUTE9 , WND.ATTRIBUTE10 SHIPMENT_ATTRIBUTE10 , WND.ATTRIBUTE11 SHIPMENT_ATTRIBUTE11 , WND.ATTRIBUTE12 SHIPMENT_ATTRIBUTE12 , WND.ATTRIBUTE13 SHIPMENT_ATTRIBUTE13 , WND.ATTRIBUTE14 SHIPMENT_ATTRIBUTE14 , WND.ATTRIBUTE15 SHIPMENT_ATTRIBUTE15 , WTH.ORIG_DOCUMENT_NUMBER ORIG_DOCUMENT_NUMBER , DECODE(WTH.DOCUMENT_TYPE, 'SA', WND.WAYBILL, NULL) WAYBILL , WCAR.CARRIER_NAME CARRIER , WND.SERVICE_LEVEL SERVICE_LEVEL , WND.MODE_OF_TRANSPORT TRANSPORTATION_METHOD , NVL(WND.WV_FROZEN_FLAG,'Y') MANUAL_WV , NVL(WND.SHIPMENT_DIRECTION,'O') SHIPMENT_DIRECTION , WND.DELIVERED_DATE DELIVERED_DATE FROM WSH_NEW_DELIVERIES WND , WSH_TRANSACTIONS_HISTORY WTH , HR_LOCATIONS_ALL_TL HRL , ORG_ORGANIZATION_DEFINITIONS OOG, WSH_CARRIERS_V WCAR , WSH_LOCATIONS WL1 , WSH_LOCATIONS WL2 WHERE WND.INITIAL_PICKUP_LOCATION_ID = WL1.WSH_LOCATION_ID AND WL1.LOCATION_SOURCE_CODE = 'HR' AND WL1.SOURCE_LOCATION_ID = HRL.LOCATION_ID AND HRL.LANGUAGE = USERENV('LANG') AND WND.ULTIMATE_DROPOFF_LOCATION_ID = WL2.WSH_LOCATION_ID AND WL2.LOCATION_SOURCE_CODE = 'HZ' AND WTH.ENTITY_NUMBER = WND.NAME AND WTH.ENTITY_TYPE ='DLVY' AND OOG.ORGANIZATION_ID = WND.ORGANIZATION_ID AND WTH.DOCUMENT_DIRECTION = 'O' AND WND.CARRIER_ID = WCAR.CARRIER_ID (+) AND NVL(WND.SHIPMENT_DIRECTION, 'O') IN ('O','IO') AND WND.DELIVERY_TYPE = 'STANDARD' UNION SELECT DISTINCT WSB.BATCH_ID DELIVERY_ID , WTH.ACTION_TYPE ACTION_CODE , WTH.DOCUMENT_NUMBER DOCUMENT_NUMBER , WTH.ENTITY_TYPE ENTITY_TYPE , WSB.CURRENCY_CODE CURRENCY_CODE , HRL.LOCATION_CODE SHIP_FROM_LOCATION , WSB.SHIP_METHOD_CODE SHIP_METHOD , WL1.COUNTRY COUNTRY , WSB.CREATION_DATE CREATION_DATE , NULL ULTIMATE_DROPOFF_DATE , NULL INITIAL_PICKUP_DATE , NULL DESCRIPTION , WTH.DOCUMENT_TYPE DOCUMENT_TYPE , WSB.FREIGHT_TERMS_CODE FREIGHT_TERMS , NULL GROSS_WEIGHT , NULL NET_WEIGHT , NULL WEIGHT_UOM_CODE , NULL NUMBER_OF_LPN , NULL VOLUME , NULL VOLUME_UOM_CODE , NULL SHIPPING_MARKS , TO_CHAR(NULL) SHIP_TO_LOCATION , WSB.FOB_CODE FOB_CODE , MP.ORGANIZATION_CODE ORGANIZATION_CODE , NULL LOADING_SEQUENCE , NULL SHIPMENT_ATTRIBUTE_CATEGORY, NULL SHIPMENT_ATTRIBUTE1 , NULL SHIPMENT_ATTRIBUTE2 , NULL SHIPMENT_ATTRIBUTE3 , NULL SHIPMENT_ATTRIBUTE4 , NULL SHIPMENT_ATTRIBUTE5 , NULL SHIPMENT_ATTRIBUTE6 , NULL SHIPMENT_ATTRIBUTE7 , NULL SHIPMENT_ATTRIBUTE8 , NULL SHIPMENT_ATTRIBUTE9 , NULL SHIPMENT_ATTRIBUTE10 , NULL SHIPMENT_ATTRIBUTE11 , NULL SHIPMENT_ATTRIBUTE12 , NULL SHIPMENT_ATTRIBUTE13 , NULL SHIPMENT_ATTRIBUTE14 , NULL SHIPMENT_ATTRIBUTE15 , WTH.ORIG_DOCUMENT_NUMBER ORIG_DOCUMENT_NUMBER , NULL WAYBILL , WCAR.CARRIER_NAME CARRIER , WCS.SERVICE_LEVEL SERVICE_LEVEL , WCS.MODE_OF_TRANSPORT TRANSPORTATION_METHOD , 'N' MANUAL_WV , 'O' SHIPMENT_DIRECTION , NULL DELIVERED_DATE FROM WSH_SHIPMENT_BATCHES WSB , WSH_TRANSACTIONS_HISTORY WTH , HR_LOCATIONS_ALL_TL HRL , HZ_PARTY_SITES PS , HZ_CUST_ACCT_SITES_ALL CA , HZ_CUST_SITE_USES_ALL SU , MTL_PARAMETERS MP , WSH_CARRIERS_V WCAR , WSH_CARRIER_SERVICES WCS , WSH_LOCATIONS WL1 , WSH_LOCATIONS WL2 WHERE WSB.SHIP_FROM_LOCATION_ID = WL1.WSH_LOCATION_ID AND WL1.LOCATION_SOURCE_CODE = 'HR' AND WL1.SOURCE_LOCATION_ID = HRL.LOCATION_ID AND HRL.LANGUAGE = USERENV('LANG') AND PS.LOCATION_ID = WL2.WSH_LOCATION_ID AND SU.SITE_USE_ID = WSB.SHIP_TO_SITE_USE_ID AND SU.CUST_ACCT_SITE_ID = CA.CUST_ACCT_SITE_ID AND CA.PARTY_SITE_ID = PS.PARTY_SITE_ID AND WL2.LOCATION_SOURCE_CODE = 'HZ' AND WTH.ENTITY_NUMBER = WSB.NAME AND WTH.ENTITY_TYPE = 'BATCH' AND MP.ORGANIZATION_ID = WSB.ORGANIZATION_ID AND WTH.DOCUMENT_DIRECTION = 'O' AND WCS.SHIP_METHOD_CODE (+) = WSB.SHIP_METHOD_CODE AND WCS.CARRIER_ID = WCAR.CARRIER_ID (+) AND nvl(WSB.PENDING_REQUEST_FLAG, 'N') = 'Y' AND Nvl(MP.DISTRIBUTED_ORGANIZATION_FLAG, 'N') = 'Y' AND WTH.DOCUMENT_TYPE = 'SR'

View Text - HTML Formatted

SELECT DISTINCT WND.DELIVERY_ID DELIVERY_ID
, WTH.ACTION_TYPE ACTION_CODE
, WTH.DOCUMENT_NUMBER DOCUMENT_NUMBER
, WTH.ENTITY_TYPE ENTITY_TYPE
, TO_CHAR(NULL) CURRENCY_CODE
, HRL.LOCATION_CODE SHIP_FROM_LOCATION
, WND.SHIP_METHOD_CODE SHIP_METHOD
, WL1.COUNTRY COUNTRY
, WND.CREATION_DATE CREATION_DATE
, WND.ULTIMATE_DROPOFF_DATE ULTIMATE_DROPOFF_DATE
, WND.INITIAL_PICKUP_DATE INITIAL_PICKUP_DATE
, WND.DESCRIPTION DESCRIPTION
, WTH.DOCUMENT_TYPE DOCUMENT_TYPE
, WND.FREIGHT_TERMS_CODE FREIGHT_TERMS
, WND.GROSS_WEIGHT GROSS_WEIGHT
, WND.NET_WEIGHT NET_WEIGHT
, WND.WEIGHT_UOM_CODE WEIGHT_UOM_CODE
, WND.NUMBER_OF_LPN NUMBER_OF_LPN
, WND.VOLUME VOLUME
, WND.VOLUME_UOM_CODE VOLUME_UOM_CODE
, WND.SHIPPING_MARKS SHIPPING_MARKS
, TO_CHAR(NULL) SHIP_TO_LOCATION
, WND.FOB_CODE FOB_CODE
, OOG.ORGANIZATION_CODE ORGANIZATION_CODE
, WND.LOADING_SEQUENCE LOADING_SEQUENCE
, WND.ATTRIBUTE_CATEGORY SHIPMENT_ATTRIBUTE_CATEGORY
, WND.ATTRIBUTE1 SHIPMENT_ATTRIBUTE1
, WND.ATTRIBUTE2 SHIPMENT_ATTRIBUTE2
, WND.ATTRIBUTE3 SHIPMENT_ATTRIBUTE3
, WND.ATTRIBUTE4 SHIPMENT_ATTRIBUTE4
, WND.ATTRIBUTE5 SHIPMENT_ATTRIBUTE5
, WND.ATTRIBUTE6 SHIPMENT_ATTRIBUTE6
, WND.ATTRIBUTE7 SHIPMENT_ATTRIBUTE7
, WND.ATTRIBUTE8 SHIPMENT_ATTRIBUTE8
, WND.ATTRIBUTE9 SHIPMENT_ATTRIBUTE9
, WND.ATTRIBUTE10 SHIPMENT_ATTRIBUTE10
, WND.ATTRIBUTE11 SHIPMENT_ATTRIBUTE11
, WND.ATTRIBUTE12 SHIPMENT_ATTRIBUTE12
, WND.ATTRIBUTE13 SHIPMENT_ATTRIBUTE13
, WND.ATTRIBUTE14 SHIPMENT_ATTRIBUTE14
, WND.ATTRIBUTE15 SHIPMENT_ATTRIBUTE15
, WTH.ORIG_DOCUMENT_NUMBER ORIG_DOCUMENT_NUMBER
, DECODE(WTH.DOCUMENT_TYPE
, 'SA'
, WND.WAYBILL
, NULL) WAYBILL
, WCAR.CARRIER_NAME CARRIER
, WND.SERVICE_LEVEL SERVICE_LEVEL
, WND.MODE_OF_TRANSPORT TRANSPORTATION_METHOD
, NVL(WND.WV_FROZEN_FLAG
, 'Y') MANUAL_WV
, NVL(WND.SHIPMENT_DIRECTION
, 'O') SHIPMENT_DIRECTION
, WND.DELIVERED_DATE DELIVERED_DATE
FROM WSH_NEW_DELIVERIES WND
, WSH_TRANSACTIONS_HISTORY WTH
, HR_LOCATIONS_ALL_TL HRL
, ORG_ORGANIZATION_DEFINITIONS OOG
, WSH_CARRIERS_V WCAR
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
WHERE WND.INITIAL_PICKUP_LOCATION_ID = WL1.WSH_LOCATION_ID
AND WL1.LOCATION_SOURCE_CODE = 'HR'
AND WL1.SOURCE_LOCATION_ID = HRL.LOCATION_ID
AND HRL.LANGUAGE = USERENV('LANG')
AND WND.ULTIMATE_DROPOFF_LOCATION_ID = WL2.WSH_LOCATION_ID
AND WL2.LOCATION_SOURCE_CODE = 'HZ'
AND WTH.ENTITY_NUMBER = WND.NAME
AND WTH.ENTITY_TYPE ='DLVY'
AND OOG.ORGANIZATION_ID = WND.ORGANIZATION_ID
AND WTH.DOCUMENT_DIRECTION = 'O'
AND WND.CARRIER_ID = WCAR.CARRIER_ID (+)
AND NVL(WND.SHIPMENT_DIRECTION
, 'O') IN ('O'
, 'IO')
AND WND.DELIVERY_TYPE = 'STANDARD' UNION SELECT DISTINCT WSB.BATCH_ID DELIVERY_ID
, WTH.ACTION_TYPE ACTION_CODE
, WTH.DOCUMENT_NUMBER DOCUMENT_NUMBER
, WTH.ENTITY_TYPE ENTITY_TYPE
, WSB.CURRENCY_CODE CURRENCY_CODE
, HRL.LOCATION_CODE SHIP_FROM_LOCATION
, WSB.SHIP_METHOD_CODE SHIP_METHOD
, WL1.COUNTRY COUNTRY
, WSB.CREATION_DATE CREATION_DATE
, NULL ULTIMATE_DROPOFF_DATE
, NULL INITIAL_PICKUP_DATE
, NULL DESCRIPTION
, WTH.DOCUMENT_TYPE DOCUMENT_TYPE
, WSB.FREIGHT_TERMS_CODE FREIGHT_TERMS
, NULL GROSS_WEIGHT
, NULL NET_WEIGHT
, NULL WEIGHT_UOM_CODE
, NULL NUMBER_OF_LPN
, NULL VOLUME
, NULL VOLUME_UOM_CODE
, NULL SHIPPING_MARKS
, TO_CHAR(NULL) SHIP_TO_LOCATION
, WSB.FOB_CODE FOB_CODE
, MP.ORGANIZATION_CODE ORGANIZATION_CODE
, NULL LOADING_SEQUENCE
, NULL SHIPMENT_ATTRIBUTE_CATEGORY
, NULL SHIPMENT_ATTRIBUTE1
, NULL SHIPMENT_ATTRIBUTE2
, NULL SHIPMENT_ATTRIBUTE3
, NULL SHIPMENT_ATTRIBUTE4
, NULL SHIPMENT_ATTRIBUTE5
, NULL SHIPMENT_ATTRIBUTE6
, NULL SHIPMENT_ATTRIBUTE7
, NULL SHIPMENT_ATTRIBUTE8
, NULL SHIPMENT_ATTRIBUTE9
, NULL SHIPMENT_ATTRIBUTE10
, NULL SHIPMENT_ATTRIBUTE11
, NULL SHIPMENT_ATTRIBUTE12
, NULL SHIPMENT_ATTRIBUTE13
, NULL SHIPMENT_ATTRIBUTE14
, NULL SHIPMENT_ATTRIBUTE15
, WTH.ORIG_DOCUMENT_NUMBER ORIG_DOCUMENT_NUMBER
, NULL WAYBILL
, WCAR.CARRIER_NAME CARRIER
, WCS.SERVICE_LEVEL SERVICE_LEVEL
, WCS.MODE_OF_TRANSPORT TRANSPORTATION_METHOD
, 'N' MANUAL_WV
, 'O' SHIPMENT_DIRECTION
, NULL DELIVERED_DATE
FROM WSH_SHIPMENT_BATCHES WSB
, WSH_TRANSACTIONS_HISTORY WTH
, HR_LOCATIONS_ALL_TL HRL
, HZ_PARTY_SITES PS
, HZ_CUST_ACCT_SITES_ALL CA
, HZ_CUST_SITE_USES_ALL SU
, MTL_PARAMETERS MP
, WSH_CARRIERS_V WCAR
, WSH_CARRIER_SERVICES WCS
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
WHERE WSB.SHIP_FROM_LOCATION_ID = WL1.WSH_LOCATION_ID
AND WL1.LOCATION_SOURCE_CODE = 'HR'
AND WL1.SOURCE_LOCATION_ID = HRL.LOCATION_ID
AND HRL.LANGUAGE = USERENV('LANG')
AND PS.LOCATION_ID = WL2.WSH_LOCATION_ID
AND SU.SITE_USE_ID = WSB.SHIP_TO_SITE_USE_ID
AND SU.CUST_ACCT_SITE_ID = CA.CUST_ACCT_SITE_ID
AND CA.PARTY_SITE_ID = PS.PARTY_SITE_ID
AND WL2.LOCATION_SOURCE_CODE = 'HZ'
AND WTH.ENTITY_NUMBER = WSB.NAME
AND WTH.ENTITY_TYPE = 'BATCH'
AND MP.ORGANIZATION_ID = WSB.ORGANIZATION_ID
AND WTH.DOCUMENT_DIRECTION = 'O'
AND WCS.SHIP_METHOD_CODE (+) = WSB.SHIP_METHOD_CODE
AND WCS.CARRIER_ID = WCAR.CARRIER_ID (+)
AND NVL(WSB.PENDING_REQUEST_FLAG
, 'N') = 'Y'
AND NVL(MP.DISTRIBUTED_ORGANIZATION_FLAG
, 'N') = 'Y'
AND WTH.DOCUMENT_TYPE = 'SR'