View: ECE_DSNO_DELIVERIES_V

View Text

SELECT ETD.DOCUMENT_ID TRANSACTION_TYPE
, 'EDI' COMMUNICATION_METHOD
, SYSDATE TRANSACTION_DATE
, ETH.TP_HEADER_ID TP_HEADER_ID
, ETD.TP_DETAIL_ID TP_DETAIL_ID
, DLV.NAME DOCUMENT_CODE
, ETD.TEST_FLAG TP_TEST_FLAG
, ETD.DOCUMENT_TYPE TP_DOCUMENT_TYPE
, ETH.TP_CODE TP_CODE
, ERC3.TP_LOCATION_CODE_EXT TP_LOCATION_CODE_EXT
, ETH.TP_DESCRIPTION TP_DESCRIPTION
, ETH.TP_REFERENCE_EXT1 TP_REFERENCE_EXT1
, ETH.TP_REFERENCE_EXT2 TP_REFERENCE_EXT2
, ETD.TRANSLATOR_CODE TP_TRANSLATOR_CODE
, ETD.EDI_FLAG TP_EDI_FLAG
, ETD.PRINT_FLAG TP_PRINT_FLAG
, ETH.TP_CONTACT_NAME TP_CONTACT_NAME
, ETH.TP_CONTACT_JOB_TITLE TP_CONTACT_JOB_TITLE
, ETH.ADDRESS1 TP_ADDRESS1
, ETH.ADDRESS2 TP_ADDRESS2
, ETH.ADDRESS3 TP_ADDRESS3
, ETH.ADDRESS4 TP_ADDRESS4
, ETH.CITY TP_CITY
, ETH.STATE TP_STATE
, ETH.PROVINCE TP_PROVINCE
, ETH.COUNTRY TP_COUNTRY
, ETH.POSTAL_CODE TP_POSTAL_CODE
, ETH.PHONE TP_PHONE
, ETH.FAX TP_FAX
, ETH.EMAIL TP_EMAIL
, ETD.DOCUMENT_STANDARD DOCUMENT_STANDARD
, DPT.ORGANIZATION_ID ORGANIZATION_ID
, DLV.ACTUAL_DEPARTURE_ID DEPARTURE_ID
, DPT.NAME DEPARTURE_NAME
, DPT.ACTUAL_DEPARTURE_DATE DEPARTURE_DATE
, DLV.DELIVERY_ID DELIVERY_ID
, DLV.NAME DELIVERY_NAME
, DPT.BILL_OF_LADING MASTER_BILL_OF_LADING
, MSI.ITEM_TYPE EQUIPMENT_CODE_INT
, RTRIM(DPT.VEHICLE_NUMBER
, '0123456789') EQUIPMENT_PREFIX
, SUBSTR(DPT.VEHICLE_NUMBER
, NVL(LENGTH(RTRIM(DPT.VEHICLE_NUMBER
, '0123456789'))
, 0) + 1) EQUIPMENT_NUMBER
, DPT.SEAL_CODE EQUIPMENT_SEAL
, DPT.FREIGHT_CARRIER_CODE CARRIER_NAME_INT
, DPT.GROSS_WEIGHT DEPARTURE_GROSS_WEIGHT
, DPT.WEIGHT_UOM_CODE DEPARTURE_GROSS_WEIGHT_UOM_INT
, DPT.NET_WEIGHT DEPARTURE_NET_WEIGHT
, DPT.WEIGHT_UOM_CODE DEPARTURE_NET_WEIGHT_UOM_INT
, DPT.GROSS_WEIGHT - DPT.NET_WEIGHT DEPARTURE_TARE_WEIGHT
, DPT.WEIGHT_UOM_CODE DEPARTURE_TARE_WEIGHT_UOM_INT
, DPT.VOLUME DEPARTURE_VOLUME
, DPT.VOLUME_UOM_CODE DEPARTURE_VOLUME_UOM_INT
, DPT.ROUTING_INSTRUCTIONS ROUTING_INSTRUCTIONS
, HRL.LOCATION_ID WAREHOUSE_LOCATION_ID
, MTP.ORGANIZATION_CODE WAREHOUSE_CODE_INT
, HOU.NAME WAREHOUSE_NAME
, HRL.ADDRESS_LINE_1 WAREHOUSE_ADDRESS1
, HRL.ADDRESS_LINE_2 WAREHOUSE_ADDRESS2
, HRL.ADDRESS_LINE_3 WAREHOUSE_ADDRESS3
, HRL.TOWN_OR_CITY WAREHOUSE_CITY
, HRL.POSTAL_CODE WAREHOUSE_POSTAL_CODE
, HRL.COUNTRY WAREHOUSE_COUNTRY_INT
, HRL.REGION_1 WAREHOUSE_REGION1_INT
, HRL.REGION_2 WAREHOUSE_REGION2_INT
, HRL.REGION_3 WAREHOUSE_REGION3_INT
, HRL.TELEPHONE_NUMBER_1 WAREHOUSE_TELEPHONE_1
, HRL.TELEPHONE_NUMBER_2 WAREHOUSE_TELEPHONE_2
, HRL.TELEPHONE_NUMBER_3 WAREHOUSE_TELEPHONE_3
, ERN1.ADDRESS_ID POOLED_ADDRESS_ID
, ERN1.ORIG_SYSTEM_REFERENCE POOLED_CODE_INT
, ERN1.TP_LOCATION_CODE_EXT POOLED_CODE_EXT1
, ERN1.TP_REFERENCE_EXT1 POOLED_CODE_EXT2
, ERN1.TP_REFERENCE_EXT2 POOLED_CODE_EXT3
, ERN1.CUSTOMER_NAME POOLED_NAME
, ERN1.ADDRESS1 POOLED_ADDRESS1
, ERN1.ADDRESS2 POOLED_ADDRESS2
, ERN1.ADDRESS3 POOLED_ADDRESS3
, ERN1.ADDRESS4 POOLED_ADDRESS4
, ERN1.CITY POOLED_CITY
, ERN1.POSTAL_CODE POOLED_POSTAL_CODE
, ERN1.COUNTRY POOLED_COUNTRY_INT
, ERN1.STATE POOLED_STATE_INT
, ERN1.PROVINCE POOLED_PROVINCE_INT
, ERN1.COUNTY POOLED_COUNTY
, ERN1.AREA_CODE POOLED_AREA_CODE
, ERN1.PHONE_NUMBER POOLED_TELEPHONE
, ERN2.ADDRESS_ID CONSIGNEE_ADDRESS_ID
, ERN2.ORIG_SYSTEM_REFERENCE CONSIGNEE_CODE_INT
, ERN2.TP_LOCATION_CODE_EXT CONSIGNEE_CODE_EXT1
, ERN2.TP_REFERENCE_EXT1 CONSIGNEE_CODE_EXT2
, ERN2.TP_REFERENCE_EXT2 CONSIGNEE_CODE_EXT3
, ERN2.CUSTOMER_NAME CONSIGNEE_NAME
, ERN2.ADDRESS1 CONSIGNEE_ADDRESS1
, ERN2.ADDRESS2 CONSIGNEE_ADDRESS2
, ERN2.ADDRESS3 CONSIGNEE_ADDRESS3
, ERN2.ADDRESS4 CONSIGNEE_ADDRESS4
, ERN2.CITY CONSIGNEE_CITY
, ERN2.POSTAL_CODE CONSIGNEE_POSTAL_CODE
, ERN2.COUNTRY CONSIGNEE_COUNTRY_INT
, ERN2.STATE CONSIGNEE_STATE_INT
, ERN2.PROVINCE CONSIGNEE_PROVINCE_INT
, ERN2.COUNTY CONSIGNEE_COUNTY
, ERN2.AREA_CODE CONSIGNEE_AREA_CODE
, ERN2.PHONE_NUMBER CONSIGNEE_TELEPHONE
, ERC3.ADDRESS_ID DESTINATION_ADDRESS_ID
, ERC3.CUSTOMER_ID DESTINATION_CUSTOMER_ID
, ERC3.SITE_USE_ID DESTINATION_SITE_USE_ID
, ERC3.ORIG_SYSTEM_REFERENCE DESTINATION_CODE_INT
, ERC3.TP_LOCATION_CODE_EXT DESTINATION_CODE_EXT1
, ERC3.TP_REFERENCE_EXT1 DESTINATION_CODE_EXT2
, ERC3.TP_REFERENCE_EXT2 DESTINATION_CODE_EXT3
, ERC3.CUSTOMER_NAME DESTINATION_NAME
, ERC3.ADDRESS1 DESTINATION_ADDRESS1
, ERC3.ADDRESS2 DESTINATION_ADDRESS2
, ERC3.ADDRESS3 DESTINATION_ADDRESS3
, ERC3.ADDRESS4 DESTINATION_ADDRESS4
, ERC3.CITY DESTINATION_CITY
, ERC3.POSTAL_CODE DESTINATION_POSTAL_CODE
, ERC3.COUNTRY DESTINATION_COUNTRY_INT
, ERC3.STATE DESTINATION_STATE_INT
, ERC3.PROVINCE DESTINATION_PROVINCE_INT
, ERC3.COUNTY DESTINATION_COUNTY
, ERC3.LAST_NAME DESTINATION_CONTACT_LAST_NAME
, ERC3.FIRST_NAME DESTINATION_CONTACT_FIRST_NAME
, ERC3.JOB_TITLE DESTINATION_CONTACT_JOB_TITLE
, ERC3.AREA_CODE DESTINATION_AREA_CODE
, ERC3.PHONE_NUMBER DESTINATION_TELEPHONE
, DLV.DELIVERY_ID SHIPPER_NUMBER
, DLV.WAYBILL WAYBILL_NUMBER
, DLV.DELIVERY_ID BILL_OF_LADING
, DLV.DELIVERY_ID INVOICE_NUMBER
, DLV.DELIVERY_ID PACKING_SLIP_NUMBER
, DPT.ACTUAL_DEPARTURE_DATE ACTUAL_SHIP_DATE
, DLV.EXPECTED_ARRIVAL_DATE EXPECTED_ARRIVAL_DATE
, FND1.USER_NAME PICKED_BY_NAME
, FND2.USER_NAME PACKED_BY_NAME
, DLV.GROSS_WEIGHT DELIVERY_GROSS_WEIGHT
, DLV.WEIGHT_UOM_CODE DELIVERY_GROSS_WEIGHT_UOM_INT
, DLV.VOLUME DELIVERY_VOLUME
, DLV.VOLUME_UOM_CODE DELIVERY_VOLUME_UOM_INT
, ETD.MAP_ID MAP_ID
, ETH.ATTRIBUTE_CATEGORY TP_HEADER_CATEGORY
, ETH.ATTRIBUTE1 TP_HEADER_ATTRIBUTE1
, ETH.ATTRIBUTE2 TP_HEADER_ATTRIBUTE2
, ETH.ATTRIBUTE3 TP_HEADER_ATTRIBUTE3
, ETH.ATTRIBUTE4 TP_HEADER_ATTRIBUTE4
, ETH.ATTRIBUTE5 TP_HEADER_ATTRIBUTE5
, ETH.ATTRIBUTE6 TP_HEADER_ATTRIBUTE6
, ETH.ATTRIBUTE7 TP_HEADER_ATTRIBUTE7
, ETH.ATTRIBUTE8 TP_HEADER_ATTRIBUTE8
, ETH.ATTRIBUTE9 TP_HEADER_ATTRIBUTE9
, ETH.ATTRIBUTE10 TP_HEADER_ATTRIBUTE10
, ETH.ATTRIBUTE11 TP_HEADER_ATTRIBUTE11
, ETH.ATTRIBUTE12 TP_HEADER_ATTRIBUTE12
, ETH.ATTRIBUTE13 TP_HEADER_ATTRIBUTE13
, ETH.ATTRIBUTE14 TP_HEADER_ATTRIBUTE14
, ETH.ATTRIBUTE15 TP_HEADER_ATTRIBUTE15
, ETD.ATTRIBUTE_CATEGORY TP_DETAIL_CATEGORY
, ETD.ATTRIBUTE1 TP_DETAIL_ATTRIBUTE1
, ETD.ATTRIBUTE2 TP_DETAIL_ATTRIBUTE2
, ETD.ATTRIBUTE3 TP_DETAIL_ATTRIBUTE3
, ETD.ATTRIBUTE4 TP_DETAIL_ATTRIBUTE4
, ETD.ATTRIBUTE5 TP_DETAIL_ATTRIBUTE5
, DLV.NET_WEIGHT DELIVERY_NET_WEIGHT
FROM ECE_RSU_NOCONTACT ERN1
, ECE_RSU_NOCONTACT ERN2
, ECE_RSU_CONTACT ERC3
, HR_LOCATIONS HRL
, HR_ORGANIZATION_UNITS HOU
, FND_USER FND1
, FND_USER FND2
, MTL_PARAMETERS MTP
, MTL_SYSTEM_ITEMS MSI
, ECE_TP_DETAILS ETD
, ECE_TP_HEADERS ETH
, WSH_DEPARTURES DPT
, WSH_DELIVERIES DLV
WHERE DLV.PICKED_BY_ID = FND1.USER_ID (+) AND DLV.PACKED_BY_ID = FND2.USER_ID (+) AND HOU.LOCATION_ID = HRL.LOCATION_ID (+) AND DLV.ORGANIZATION_ID = MTP.ORGANIZATION_ID AND DLV.ORGANIZATION_ID = HOU.ORGANIZATION_ID AND DLV.POOLED_SHIP_TO_ID = ERN1.SITE_USE_ID (+) AND DLV.INTERMEDIATE_SHIP_TO_ID = ERN2.SITE_USE_ID (+) AND ETD.EDI_FLAG = 'Y' AND ETD.DOCUMENT_ID = 'DSNO' AND ERC3.TP_HEADER_ID = ETD.TP_HEADER_ID AND ERC3.TP_HEADER_ID = ETH.TP_HEADER_ID AND DLV.ULTIMATE_SHIP_TO_ID = ERC3.SITE_USE_ID AND DPT.ORGANIZATION_ID = MSI.ORGANIZATION_ID(+) AND DPT.VEHICLE_ITEM_ID = MSI.INVENTORY_ITEM_ID(+) AND DPT.STATUS_CODE = 'CL' AND DLV.ACTUAL_DEPARTURE_ID = DPT.DEPARTURE_ID AND EXISTS (SELECT DELIVERY_ID
FROM SO_PICKING_LINE_DETAILS PLD
WHERE DLV.DELIVERY_ID = PLD.DELIVERY_ID
AND NVL(PLD.SHIPPED_QUANTITY
, 0) > 0)

Columns