WSH Vehicle Load Sheet Summary

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