VIEW: APPS.WSH_ITM_SHIPMENT_V

Source

View Text - Preformatted

SELECT DISTINCT WND.DELIVERY_ID DELIVERY_ID, ( SELECT SUM( WFC.TOTAL_AMOUNT) FROM wsh_freight_costs wfc WHERE wfc.delivery_id = wnd.delivery_id AND wfc.line_type_code ='SUMMARY') TOTAL_COST, WTH.ACTION_TYPE ACTION_CODE, WTH.DOCUMENT_NUMBER DOCUMENT_NUMBER, 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, HCSU.LOCATION 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.FREIGHT_CODE CARRIER, WND.SERVICE_LEVEL SERVICE_LEVEL, WND.MODE_OF_TRANSPORT TRANSPORTATION_METHOD FROM WSH_NEW_DELIVERIES WND, WSH_TRANSACTIONS_HISTORY WTH, HR_LOCATIONS_ALL_TL HRL, HZ_CUST_SITE_USES_ALL HCSU, HZ_CUST_ACCT_SITES_ALL HCAS, HZ_CUST_ACCOUNTS HCA, HZ_PARTY_SITES HPS, 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 HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID AND HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID AND HCAS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID AND HCSU.SITE_USE_CODE = 'SHIP_TO' AND WND.ULTIMATE_DROPOFF_LOCATION_ID = WL2.WSH_LOCATION_ID AND WL2.LOCATION_SOURCE_CODE = 'HZ' AND WL2.SOURCE_LOCATION_ID = HPS.LOCATION_ID AND WTH.ENTITY_NUMBER = WND.NAME AND WTH.ENTITY_TYPE ='DLVY' AND OOG.ORGANIZATION_ID = WND.ORGANIZATION_ID AND (HCAS.ORG_ID IS NULL OR HCAS.ORG_ID = DECODE(WSH_UTIL_CORE.Get_OperatingUnit_Id(wnd.delivery_id), -1, HCAS.ORG_ID, WSH_UTIL_CORE.Get_OperatingUnit_Id(wnd.delivery_id))) AND NVL(HCAS.ORG_ID, -999) = NVL(HCSU.ORG_ID , -999) AND WTH.DOCUMENT_DIRECTION = 'O' AND WND.CARRIER_ID = WCAR.CARRIER_ID (+) AND NVL(WND.SHIPMENT_DIRECTION, 'O') IN ('O', 'IO') AND nvl(WND.CONSIGNEE_FLAG, 'C') = 'C' UNION SELECT DISTINCT WND.DELIVERY_ID DELIVERY_ID, ( SELECT SUM( WFC.TOTAL_AMOUNT) FROM wsh_freight_costs wfc WHERE wfc.delivery_id = wnd.delivery_id AND wfc.line_type_code ='SUMMARY') TOTAL_COST, WTH.ACTION_TYPE ACTION_CODE, WTH.DOCUMENT_NUMBER DOCUMENT_NUMBER, 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, POVS.VENDOR_SITE_CODE 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.FREIGHT_CODE CARRIER, WND.SERVICE_LEVEL SERVICE_LEVEL, WND.MODE_OF_TRANSPORT TRANSPORTATION_METHOD FROM WSH_NEW_DELIVERIES WND, WSH_TRANSACTIONS_HISTORY WTH, HR_LOCATIONS_ALL_TL HRL, PO_VENDOR_SITES_ALL POVS, HZ_PARTY_SITE_USES HPSU, HZ_PARTY_SITES HPS, 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 POVS.PARTY_SITE_ID = HPS.PARTY_SITE_ID AND HPSU.PARTY_SITE_ID = HPS.PARTY_SITE_ID AND HPSU.SITE_USE_TYPE = 'PURCHASING' AND WND.ULTIMATE_DROPOFF_LOCATION_ID = WL2.WSH_LOCATION_ID AND WL2.LOCATION_SOURCE_CODE = 'HZ' AND WL2.SOURCE_LOCATION_ID = HPS.LOCATION_ID AND WTH.ENTITY_NUMBER = WND.NAME AND WTH.ENTITY_TYPE ='DLVY' AND OOG.ORGANIZATION_ID = WND.ORGANIZATION_ID AND (POVS.ORG_ID IS NULL OR POVS.ORG_ID = DECODE(WSH_UTIL_CORE.Get_OperatingUnit_Id(wnd.delivery_id) , -1, POVS.ORG_ID, WSH_UTIL_CORE.Get_OperatingUnit_Id(wnd.delivery_id))) AND WTH.DOCUMENT_DIRECTION = 'O' AND WND.CARRIER_ID = WCAR.CARRIER_ID (+) AND NVL(WND.SHIPMENT_DIRECTION, 'O') IN ('O', 'IO') AND WND.CONSIGNEE_FLAG = 'V'

View Text - HTML Formatted

SELECT DISTINCT WND.DELIVERY_ID DELIVERY_ID
, ( SELECT SUM( WFC.TOTAL_AMOUNT)
FROM WSH_FREIGHT_COSTS WFC
WHERE WFC.DELIVERY_ID = WND.DELIVERY_ID
AND WFC.LINE_TYPE_CODE ='SUMMARY') TOTAL_COST
, WTH.ACTION_TYPE ACTION_CODE
, WTH.DOCUMENT_NUMBER DOCUMENT_NUMBER
, 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
, HCSU.LOCATION 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.FREIGHT_CODE CARRIER
, WND.SERVICE_LEVEL SERVICE_LEVEL
, WND.MODE_OF_TRANSPORT TRANSPORTATION_METHOD
FROM WSH_NEW_DELIVERIES WND
, WSH_TRANSACTIONS_HISTORY WTH
, HR_LOCATIONS_ALL_TL HRL
, HZ_CUST_SITE_USES_ALL HCSU
, HZ_CUST_ACCT_SITES_ALL HCAS
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTY_SITES HPS
, 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 HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
AND HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND HCAS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND HCSU.SITE_USE_CODE = 'SHIP_TO'
AND WND.ULTIMATE_DROPOFF_LOCATION_ID = WL2.WSH_LOCATION_ID
AND WL2.LOCATION_SOURCE_CODE = 'HZ'
AND WL2.SOURCE_LOCATION_ID = HPS.LOCATION_ID
AND WTH.ENTITY_NUMBER = WND.NAME
AND WTH.ENTITY_TYPE ='DLVY'
AND OOG.ORGANIZATION_ID = WND.ORGANIZATION_ID
AND (HCAS.ORG_ID IS NULL OR HCAS.ORG_ID = DECODE(WSH_UTIL_CORE.GET_OPERATINGUNIT_ID(WND.DELIVERY_ID)
, -1
, HCAS.ORG_ID
, WSH_UTIL_CORE.GET_OPERATINGUNIT_ID(WND.DELIVERY_ID)))
AND NVL(HCAS.ORG_ID
, -999) = NVL(HCSU.ORG_ID
, -999)
AND WTH.DOCUMENT_DIRECTION = 'O'
AND WND.CARRIER_ID = WCAR.CARRIER_ID (+)
AND NVL(WND.SHIPMENT_DIRECTION
, 'O') IN ('O'
, 'IO')
AND NVL(WND.CONSIGNEE_FLAG
, 'C') = 'C' UNION SELECT DISTINCT WND.DELIVERY_ID DELIVERY_ID
, ( SELECT SUM( WFC.TOTAL_AMOUNT)
FROM WSH_FREIGHT_COSTS WFC
WHERE WFC.DELIVERY_ID = WND.DELIVERY_ID
AND WFC.LINE_TYPE_CODE ='SUMMARY') TOTAL_COST
, WTH.ACTION_TYPE ACTION_CODE
, WTH.DOCUMENT_NUMBER DOCUMENT_NUMBER
, 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
, POVS.VENDOR_SITE_CODE 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.FREIGHT_CODE CARRIER
, WND.SERVICE_LEVEL SERVICE_LEVEL
, WND.MODE_OF_TRANSPORT TRANSPORTATION_METHOD
FROM WSH_NEW_DELIVERIES WND
, WSH_TRANSACTIONS_HISTORY WTH
, HR_LOCATIONS_ALL_TL HRL
, PO_VENDOR_SITES_ALL POVS
, HZ_PARTY_SITE_USES HPSU
, HZ_PARTY_SITES HPS
, 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 POVS.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND HPSU.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND HPSU.SITE_USE_TYPE = 'PURCHASING'
AND WND.ULTIMATE_DROPOFF_LOCATION_ID = WL2.WSH_LOCATION_ID
AND WL2.LOCATION_SOURCE_CODE = 'HZ'
AND WL2.SOURCE_LOCATION_ID = HPS.LOCATION_ID
AND WTH.ENTITY_NUMBER = WND.NAME
AND WTH.ENTITY_TYPE ='DLVY'
AND OOG.ORGANIZATION_ID = WND.ORGANIZATION_ID
AND (POVS.ORG_ID IS NULL OR POVS.ORG_ID = DECODE(WSH_UTIL_CORE.GET_OPERATINGUNIT_ID(WND.DELIVERY_ID)
, -1
, POVS.ORG_ID
, WSH_UTIL_CORE.GET_OPERATINGUNIT_ID(WND.DELIVERY_ID)))
AND WTH.DOCUMENT_DIRECTION = 'O'
AND WND.CARRIER_ID = WCAR.CARRIER_ID (+)
AND NVL(WND.SHIPMENT_DIRECTION
, 'O') IN ('O'
, 'IO')
AND WND.CONSIGNEE_FLAG = 'V'