A freight carrier is a commercial company that transports shipment/deliveries to and from customers, suppliers and internal organizations. Freight Carriers are setup commonly for the Order Management, Inventory and Procurement. The freight carriers are setup with their model of transport like Air, Ground, Ocean, Service levels like 1 Day, 2 day and Overnight etc., and enabled in the shipping organizations.
Here is the SQL Query to get all the active shipping methods setup in the system. This helps for the Oracle Shipping team to determine a list of active shipping methods/carriers setup in the system and setup additional freight carriers incase of missing any shipping methods.
SELECT Carrier.carrier_name Carrier,
ModeOfTransport.meaning ModeofTransport ,
ServiceLevel.meaning ServiceLevel ,
(Carrier.carrier_name||’ ‘||ModeOfTransport.meaning||’ ‘ || ServiceLevel.meaning) AS ShippingMethod ,
LISTAGG(inv.organization_code, ‘, ‘) WITHIN group (ORDER BY inv.organization_code) “Enabled_Orgs”
FROM WSH_ORG_CARRIER_SERVICES WarehouseCarrierService,
RCS_LOOKUPS ModeOfTransport ,
RCS_LOOKUPS ServiceLevel ,
WSH_CARRIERS_V Carrier,
INV_ORG_PARAMETERS Inv
WHERE (WarehouseCarrierService.CARRIER_ID = Carrier.CARRIER_ID )
AND (WarehouseCarrierService.MODE_OF_TRANSPORT = ModeOfTransport.LOOKUP_CODE)
AND (ModeOfTransport.LOOKUP_TYPE = ‘WSH_MODE_OF_TRANSPORT’)
AND (WarehouseCarrierService.SERVICE_LEVEL = ServiceLevel.LOOKUP_CODE)
AND (ServiceLevel.LOOKUP_TYPE = ‘WSH_SERVICE_LEVELS’)
AND (Carrier.CARRIER_ID = WarehouseCarrierService.CARRIER_ID )
AND NVL(Carrier.enabled_flag,’N’) = ‘Y’
AND NVL(WarehouseCarrierService.enabled_flag,’N’) = ‘Y’
AND inv.organization_id = WarehouseCarrierService.ORGANIZATION_ID
GROUP BY Carrier.carrier_name, ModeOfTransport.meaning, ServiceLevel.meaning,
(Carrier.carrier_name||’ ‘||ModeOfTransport.meaning||’ ‘ ||ServiceLevel.meaning)
Sample Output:
