Product: | OE - Order Entry |
---|---|
Description: | Deliveries for departure planning or ship confirm |
Implementation/DBA Data: | Not implemented in this database |
SELECT DEL.ROWID ROW_ID
, DEL.DELIVERY_ID DELIVERY_ID
, DEL.NAME DELIVERY_NAME
, DEL.ORGANIZATION_ID
, DEL.CREATION_DATE
, DEL.CREATED_BY
, DEL.LAST_UPDATE_DATE
, DEL.LAST_UPDATED_BY
, DEL.LAST_UPDATE_LOGIN
, DEL.PROGRAM_APPLICATION_ID
, DEL.PROGRAM_ID
, DEL.PROGRAM_UPDATE_DATE
, DEL.REQUEST_ID
, DEL.PLANNED_DEPARTURE_ID DPW_DEPARTURE_ID
, DEP1.NAME DPW_DEPARTURE_NAME
, DEP1.PLANNED_DEPARTURE_DATE DPW_PLANNED_DEPARTURE_DATE
, DEP1.ACTUAL_DEPARTURE_DATE DPW_ACTUAL_DEPARTURE_DATE
, DEP1.FREIGHT_CARRIER_CODE DPW_FREIGHT_CARRIER_CODE
, DEP1.BILL_OF_LADING DPW_BOL
, DEP1.STATUS_CODE DPW_DEPARTURE_STATUS_CODE
, S3.MEANING DPW_DEPARTURE_STATUS
, DEL.ACTUAL_DEPARTURE_ID SC_DEPARTURE_ID
, DEP2.NAME SC_DEPARTURE_NAME
, DEP2.PLANNED_DEPARTURE_DATE SC_PLANNED_DEPARTURE_DATE
, DEP2.ACTUAL_DEPARTURE_DATE SC_ACTUAL_DEPARTURE_DATE
, DEP2.FREIGHT_CARRIER_CODE SC_FREIGHT_CARRIER_CODE
, DEP2.BILL_OF_LADING SC_BOL
, DEP2.STATUS_CODE SC_DEPARTURE_STATUS_CODE
, S2.MEANING SC_DEPARTURE_STATUS
, DEL.STATUS_CODE STATUS_CODE
, S1.MEANING STATUS
, DEL.SOURCE_CODE
, DEL.DATE_CLOSED
, DEL.SEQUENCE_NUMBER
, DEL.CUSTOMER_ID
, RC.CUSTOMER_NAME
, RC.CUSTOMER_NUMBER
, DEL.ULTIMATE_SHIP_TO_ID
, SU1.LOCATION ULTIMATE_SHIP_TO
, DEL.INTERMEDIATE_SHIP_TO_ID
, SU2.LOCATION INTERMEDIATE_SHIP_TO
, DEL.POOLED_SHIP_TO_ID
, SU3.LOCATION POOLED_SHIP_TO
, DEL.WAYBILL
, DEL.GROSS_WEIGHT
, DEL.WEIGHT_UOM_CODE
, DEL.VOLUME
, DEL.VOLUME_UOM_CODE
, DEL.PICKED_BY_ID
, SUBSTR(FU1.LAST_NAME|| DECODE( DEL.PICKED_BY_ID
, NULL
, NULL
, '
, ') ||FU1.MIDDLE_NAMES||' '||FU1.FIRST_NAME
, 1
, 50) PICKER_NAME
, DEL.PACKED_BY_ID
, SUBSTR(FU2.LAST_NAME|| DECODE( DEL.PACKED_BY_ID
, NULL
, NULL
, '
, ') ||FU2.MIDDLE_NAMES||' '||FU2.FIRST_NAME
, 1
, 50) PACKER_NAME
, DEL.EXPECTED_ARRIVAL_DATE
, DEL.REPORT_SET_ID
, SRS.NAME REPORT_SET_NAME
, DEL.ASN_DATE_SENT
, DEL.ASN_SEQ_NUMBER
, DEL.LOADING_ORDER_FLAG
, S4.MEANING LOADING_ORDER
, DEL.FREIGHT_CARRIER_CODE
, DEL.FREIGHT_TERMS_CODE
, S5.MEANING FREIGHT_TERMS
, DEL.CURRENCY_CODE
, DEL.FOB_CODE
, AL.MEANING FOB_NAME
, DEP2.VEHICLE_NUMBER
, DEL.ATTRIBUTE_CATEGORY
, DEL.ATTRIBUTE1
, DEL.ATTRIBUTE2
, DEL.ATTRIBUTE3
, DEL.ATTRIBUTE4
, DEL.ATTRIBUTE5
, DEL.ATTRIBUTE6
, DEL.ATTRIBUTE7
, DEL.ATTRIBUTE8
, DEL.ATTRIBUTE9
, DEL.ATTRIBUTE10
, DEL.ATTRIBUTE11
, DEL.ATTRIBUTE12
, DEL.ATTRIBUTE13
, DEL.ATTRIBUTE14
, DEL.ATTRIBUTE15
, DEL.GLOBAL_ATTRIBUTE_CATEGORY
, DEL.GLOBAL_ATTRIBUTE1
, DEL.GLOBAL_ATTRIBUTE2
, DEL.GLOBAL_ATTRIBUTE3
, DEL.GLOBAL_ATTRIBUTE4
, DEL.GLOBAL_ATTRIBUTE5
, DEL.GLOBAL_ATTRIBUTE6
, DEL.GLOBAL_ATTRIBUTE7
, DEL.GLOBAL_ATTRIBUTE8
, DEL.GLOBAL_ATTRIBUTE9
, DEL.GLOBAL_ATTRIBUTE10
, DEL.GLOBAL_ATTRIBUTE11
, DEL.GLOBAL_ATTRIBUTE12
, DEL.GLOBAL_ATTRIBUTE13
, DEL.GLOBAL_ATTRIBUTE14
, DEL.GLOBAL_ATTRIBUTE15
, DEL.GLOBAL_ATTRIBUTE16
, DEL.GLOBAL_ATTRIBUTE17
, DEL.GLOBAL_ATTRIBUTE18
, DEL.GLOBAL_ATTRIBUTE19
, DEL.GLOBAL_ATTRIBUTE20
, DEL.NET_WEIGHT
, DEL.GROSS_WEIGHT - DEL.NET_WEIGHT
FROM WSH_DEPARTURES DEP1
, WSH_DEPARTURES DEP2
, SO_REPORT_SETS SRS
, PER_PEOPLE_X FU1
, PER_PEOPLE_X FU2
, RA_CUSTOMERS RC
, RA_SITE_USES_ALL SU1
, RA_SITE_USES_ALL SU2
, RA_SITE_USES_ALL SU3
, AR_LOOKUPS AL
, SO_LOOKUPS S1
, SO_LOOKUPS S2
, SO_LOOKUPS S3
, SO_LOOKUPS S4
, SO_LOOKUPS S5
, WSH_DELIVERIES DEL
WHERE RC.CUSTOMER_ID = DEL.CUSTOMER_ID
AND SRS.REPORT_SET_ID(+) = DEL.REPORT_SET_ID
AND SU1.SITE_USE_ID = DEL.ULTIMATE_SHIP_TO_ID
AND SU2.SITE_USE_ID(+) = NVL(DEL.INTERMEDIATE_SHIP_TO_ID
, -99)
AND SU3.SITE_USE_ID(+) = NVL(DEL.POOLED_SHIP_TO_ID
, -99)
AND DEP1.DEPARTURE_ID(+) = NVL(DEL.PLANNED_DEPARTURE_ID
, -99)
AND DEP2.DEPARTURE_ID(+) = NVL(DEL.ACTUAL_DEPARTURE_ID
, -99)
AND FU1.PERSON_ID(+) = NVL(DEL.PICKED_BY_ID
, -99)
AND FU2.PERSON_ID(+) = NVL(DEL.PACKED_BY_ID
, -99)
AND AL.LOOKUP_TYPE (+) = 'FOB'
AND AL.LOOKUP_CODE (+) = DEL.FOB_CODE
AND S1.LOOKUP_TYPE(+) = 'SHIPPING_STATUS'
AND S1.LOOKUP_CODE(+) = DEL.STATUS_CODE
AND S2.LOOKUP_TYPE(+) = 'SHIPPING_STATUS'
AND S2.LOOKUP_CODE(+) = DEP2.STATUS_CODE
AND S3.LOOKUP_TYPE(+) = 'SHIPPING_STATUS'
AND S3.LOOKUP_CODE(+) = DEP1.STATUS_CODE
AND S4.LOOKUP_CODE(+) = DEL.LOADING_ORDER_FLAG
AND S4.LOOKUP_TYPE(+) = 'LOADING_ORDER'
AND S5.LOOKUP_CODE(+) = DEL.FREIGHT_TERMS_CODE
AND S5.LOOKUP_TYPE(+) = 'FREIGHT_TERMS'