Query to Derive Freight Carrier Information for a Delivery

Query to Derive Freight Carrier Information for a Delivery

SELECT wlok.meaning status,
     to_char(NULL) name,
         WSH_UTIL_CORE.get_location_description(wnd.initial_pickup_location_id,'CSZ') pickup_loc,
     wnd.ship_method_code,
         hp.party_name carrier_name,
     to_char(NULL) bill_of_lading
  FROM     wsh_lookups wlok,
     wsh_new_deliveries wnd,
         hz_parties hp, hz_party_usg_assignments hpu
  WHERE     wnd.name = :v_delivery_name
  AND    hp.party_id(+) = wnd.carrier_id
  AND    hp.party_id = hpu.party_id(+)
  AND    hpu.party_usage_code(+) = 'TRANSPORTATION_PROVIDER'
  AND     wlok.lookup_type = 'DELIVERY_STATUS'
  AND     wlok.lookup_code = wnd.status_code
  AND    nvl(wnd.SHIPMENT_DIRECTION , 'O') IN ('O', 'IO')
  AND     wnd.status_code not in ('CL','IT');


Note - Please Note that Freight Carriers are Defined as Party in TCA.With Party Type = Organization.