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.
