SELECT
TRP.TRIP_ID TRIP_ID,
TRP.NAME TRIP,
TRP2.NAME ARRIVE_AFTER_TRIP_NAME,
(SELECT party_name FROM hz_parties WHERE party_id = TRP.CARRIER_ID ) CARRIER,
WDI.SEQUENCE_NUMBER BILL_OF_LADING,
WL2.MEANING DEL_STATUS,
WL.MEANING TRP_STATUS,
wsh_util_core.get_item_name(msi.inventory_item_id,msi.organization_id) VEHICLE_TYPE,
TRP.VEHICLE_NUMBER VEHICLE_NUMBER,
WND.GROSS_WEIGHT DEP_GR_WT,
WND.NET_WEIGHT DEP_NET_WT,
WND.WEIGHT_UOM_CODE DEL_WEIGHT_UOM,
TRP.ROUTING_INSTRUCTIONS INSTRUCTIONS,
WND.NAME DELIVERY_NAME,
WND.WAYBILL WAYBILL,
WND.LOADING_SEQUENCE DEL_SEQUENCE,
SUBSTRB(CUST.PARTY_NAME,1,50) CUSTOMER,
CUST.ACCOUNT_NUMBER CUSTOMER_NUMBER,
STP.STOP_ID PICK_UP_STOP_ID,
STP2.STOP_ID DROP_OFF_STOP_ID,
STP.STOP_SEQUENCE_NUMBER PICK_UP_SEQ,
STP.PLANNED_DEPARTURE_DATE PICK_UP_PLANNED_DEP,
STP.ACTUAL_DEPARTURE_DATE PICK_UP_ACTUAL_DEP,
WL3.MEANING PICK_UP_STOP_STATUS,
STP2.STOP_SEQUENCE_NUMBER DROP_OFF_SEQ,
STP2.PLANNED_ARRIVAL_DATE DROP_OFF_PLANNED_ARR,
STP2.ACTUAL_ARRIVAL_DATE DROP_OFF_ACTUAL_ARR,
WL3.MEANING DROP_OFF_STOP_STATUS,
LOC.ADDRESS1 PICK_ADDR1,
LOC.ADDRESS2 PICK_ADDR2,
LOC.ADDRESS3 PICK_ADDR3,
LOC.CITY || ',' || LOC.STATE || ',' || LOC.POSTAL_CODE PICK_CITY_STATE,
LOC.COUNTRY PICK_COUNTRY,
LOC1.ADDRESS1 DROP_ADDR1,
LOC1.ADDRESS2 DROP_ADDR2,
LOC1.ADDRESS3 DROP_ADDR3,
LOC1.CITY || ',' || LOC1.STATE || ',' || LOC1.POSTAL_CODE DROP_CITY_STATE,
LOC1.COUNTRY DROP_COUNTRY,
WDL.PARENT_DELIVERY_LEG_ID PARENT_DELIVERY_LEG_ID,
TO_CHAR(TRP.TRIP_ID) CHAR_TRIP_ID,
WSH_WSHRDVLS_XMLP_PKG.cf_consol_delivery_nameformula(PARENT_DELIVERY_LEG_ID) CF_consol_delivery_name
FROM
WSH_LOOKUPS WL,
WSH_LOOKUPS WL2,
WSH_LOOKUPS WL3,
WSH_LOOKUPS WL4,
MTL_SYSTEM_ITEMS MSI,
WSH_TRIPS TRP,
WSH_TRIPS TRP2,
(SELECT party.party_name,
cust_acct.cust_account_id,
'C' consignee_flag,
cust_acct.account_number account_number
FROM hz_parties party,
hz_cust_accounts cust_acct
WHERE cust_acct.party_id = party.party_id
UNION
SELECT party.party_name,
pv.vendor_id cust_account_id,
'V' consignee_flag,
pv.segment1 account_number
FROM hz_parties party,
po_vendors pv
WHERE pv.party_id = party.party_id) cust,
WSH_LOCATIONS LOC,
WSH_LOCATIONS LOC1,
WSH_TRIP_STOPS STP,
WSH_TRIP_STOPS STP2,
WSH_DOCUMENT_INSTANCES WDI,
WSH_DELIVERY_LEGS WDL,
WSH_NEW_DELIVERIES WND
WHERE TRP.ARRIVE_AFTER_TRIP_ID = TRP2.TRIP_ID (+)
AND TRP.VEHICLE_ITEM_ID = MSI.INVENTORY_ITEM_ID (+)
AND TRP.VEHICLE_ORGANIZATION_ID = MSI.ORGANIZATION_ID (+)
AND WL2.LOOKUP_CODE = WND.STATUS_CODE
AND WL2.LOOKUP_TYPE = 'DELIVERY_STATUS'
AND WL.LOOKUP_CODE = TRP.STATUS_CODE
AND WL.LOOKUP_TYPE = 'TRIP_STATUS'
AND WL3.LOOKUP_CODE = STP.STATUS_CODE
AND WL3.LOOKUP_TYPE = 'TRIP_STOP_STATUS'
AND WL4.LOOKUP_CODE = STP2.STATUS_CODE
AND WL4.LOOKUP_TYPE = 'TRIP_STOP_STATUS'
AND STP.TRIP_ID = TRP.TRIP_ID
AND STP2.TRIP_ID = TRP.TRIP_ID
AND WDL.PICK_UP_STOP_ID = STP.STOP_ID
AND WDL.DROP_OFF_STOP_ID = STP2.STOP_ID
AND WDL.DELIVERY_ID = WND.DELIVERY_ID
AND WND.CUSTOMER_ID = CUST.CUST_ACCOUNT_ID
AND NVL(WND.CONSIGNEE_FLAG,'C') = CUST.CONSIGNEE_FLAG
AND nvl(WND.shipment_direction, 'O') IN ('O', 'IO')
AND WND.DELIVERY_TYPE = 'STANDARD'
AND WDI.DOCUMENT_TYPE (+)= 'BOL'
AND WDI.ENTITY_ID (+)= WDL.DELIVERY_LEG_ID
AND STP.STOP_LOCATION_ID = LOC.WSH_LOCATION_ID
AND STP2.STOP_LOCATION_ID = LOC1.WSH_LOCATION_ID
&LP_TRIP
ORDER BY DEL_SEQUENCE |
