How Can I Create A Database View for Bulk Shipping From UPS or FedEx?

You can batch ship from UPS and FedEx, however there is some configuration that is required. This article goes over the steps of creating a custom database View to handle this scenario.

Some companies process hundreds of shipments a day in UPS Worldship or FedEx Shipping Workstation. The default setup for shipping workstation is to import one order or shipment into UPS or FedEx at a time. There is a custom solution to Bulk import shipments into UPS or FedEx. This solution involves the creation of a custom view in the database as well as custom mappings with UPS and FedEx.

The custom Shipping workstation mappings will require the assistance of a UPS or FedEx tech rep with the ability to run scripts that will connect our data view to theirs.

Here is an example of the custom view for Bulk Shipments.

CREATE VIEW _BulkShipments
s.GUIDShipment –Not required
, s.GUIDCustomer –Not required
, c.CustID
, s.ShipmentNumber
, s.ShipmentStatus
, s.ShipmentDate
, s.CarrierService
, s.ShipToAddress1
, s.ShipToAddress2
, s.ShipToAddress3
, s.ShipToAddress4
, s.ShipToAttn
, s.ShipToCity
, s.ShipToCountry
, s.ShipToState
, s.ShipToZip
, Replace(o.ContactPhoneNumber, ‘-‘, ”) AS ContactPhoneNumber
, p.PO
, p.PackageID
, p.Weight
, c.CompanyName as BillToCompany
, c.Name as BillToAttn
, c.Address as BillToAddress1
, c.Address2 as BillToAddress2
, c.Address3 as BillToAddress3
, c.Address4 as BillToAddress4
, c.City as BillToCity
, c.State as BillToState
, c.Zip as BillToZip
, c.Country as BillToCountry
, c.DefaultReference as BillToAccount –Could be any field not in use or a custom field on the customer record.
, c._BillingOption as BillingOption –Used to denote whether a shipment is billed to a Third Party, Shipper or Receiver. Added as custom field on customer.
, ‘CP’ as UPSPackageType –Defaulted for use by UPS, may not be necessary.
, ‘1’ as FedExPackageType — Defaulted for use by FedEx, may not be necessary.
, Convert(date, o.RequestedShipDate) as RequestedShipDate
, o.OrderDate –Not required
, o.OrderNumber –Not required
FROM ShipmentSummary s
INNER JOIN ShipmentPack p ON s.GUIDShipment = p.GUIDShipment
INNER JOIN Customer c ON s.GUIDCustomer = c.GUIDCustomer
INNER JOIN ShipmentOrder so ON s.GUIDShipment = so.GUIDShipment
INNER JOIN Orders o ON so.GUIDOrder = o.GUIDOrder
WHERE s.Carrier IN (‘UPS’, ‘UPSG’, ‘UPSA’, ‘UPSN’, ‘UPSS’) –Include all Carriers that are relevant to the Bulk Shipment i.e. FedEx or UPS
AND s.ShipmentStatus = ‘P’ –Only includes shipments that are set as Packaged.
–AND s.GUIDCustomer = ” –Not necessary unless you only want to filter shipments related to a certain customer.
AND Convert(date, o.OrderDate) <= Convert(date, GETDATE()) –Bring in Shipments that have an Order Date Before or on Today. Could also use Requested Ship Date or Promised Date.

Keep in mind that every company using Acctivate will have different needs for this view so there is no exact standard available. This article is meant to simply inform the user that there is an option for these types of situations.