Page Comparison

Hosted by 3rd Party: Marcel Digital

www.marceldigital.com

Jeff Belasco Olivia / Project Manager / opugliese@marceldigital.com (
Jeff Belasco jbelasco@marceldigital.com was told no longer on the project as of 4.5.23

Casey Jordan/ Website Developer /  cjordan@marceldigital.com

Developer contact: alex@marceldigital.com

SEKO API Call

This is the endpoint that we are currently using: https://mysekoapi.sekologistics.io/tracking-service/track/

Here is an example with the tracking number: https://mysekoapi.sekologistics.io/tracking-service/track/NR1865881

Try to find the Shipment Data

Alternate API: https://seko-track-api.azurewebsites.net/track/21720895

Source Code Repository: https://bitbucket.org/sekologistics/myseko-track-api/src/develop/src/MySekoTrackApi/

CI/CD Pipelines (Azure DevOps): https://dev.azure.com/seko-na/Track API

Source SQL Query (Subject to change. See Source Code for current query details)

USE HarmonyStore
DECLARE @TypeOrderIdDomestic uniqueidentifier = 'fe2ba03d-1c5e-4475-9280-cf89b282baf3'
DECLARE @TypeOrderIdInternational uniqueidentifier = '60b8d5a0-b1fb-4817-ae38-c5b254c6b26f'

SELECT TOP 1 * FROM dbo.OrdOrder WHERE Number = @trackingNumber AND TypeOrderID IN (@TypeOrderIdDomestic, @TypeOrderIdInternational)

Per the Vendor, Here is the logic to categorize the 5 buckets at the top:

Use: "shipmentStatus" values that we look for and what step they are matched to:

Step 1: "Shipment Data Received", "Shipment Being Processed for Pickup"

Step 2: "At Pickup Terminal", "Temporary Origin Hold",

Step 3: "In Transit To Delivery Terminal", "In Transit", “In Transit- Confirmed out of Origin”

Step 4: "At Delivery Terminal", "At Delivery Terminal Delivery Appt Set", "Temporary Destination Hold", "Delivered To Warehouse", "Shipment is Out for Delivery", "On Hand DEST Delv Appt Set",

“Out for Delivery”

Step 5: "Shipment Delivered", "Picked Up From Customer", "Return Data Received", "Delivered"

Image Added

to get comprehensive list of Shipment Status/Milestones from REDNA & Cargowise

Harmony Store

SELECT * FROM sekona.sekona.dbo.tblStatusMapping
SELECT * FROM odyssey.ODYSSEY_UserRepository.dbo.Client_StatusMapping
SELECT * FROM odyssey.ODYSSEY_UserRepository.dbo.Client_StatusCategory

When in doubt, look to mySEKO tracking results: https://harmony.myseko.com/Track

International Shipment:

...

Domestic Shipment:

...

API query

exec sp_executesql N'SELECT TOP 1 * FROM dbo.OrdOrder WHERE Number = @trackingNumber',N'@trackingNumber nvarchar(8)',@trackingNumber=N'21720895-- TrackingInfoDetailed
SELECT TOP 1
@trackingNumber as [TrackingNumber],
ShipmentDate as [ShipmentDate],
ServiceLevel as [ServiceLevel],
ShipmentStatus as [ShipmentStatus],
DueDate as [DueDate],

                NULLIF(ShipReference, '''')   as [ShipperReference],
                ShipCity        as [ShipperCity],
                ShipState       as [ShipperStateCode],
                ShipPostal      as [ShipperPostalCode],
                ShipCountryCode as [ShipperCountryCode],

                NULLIF(ConsReference, '''')   as [ConsigneeReference],
                ConsCity        as [ConsigneeCity],
                ConsState       as [ConsigneeStateCode],
                ConsPostal      as [ConsigneePostalCode],
                ConsCountryCode as [ConsigneeCountryCode],

                PODDate         as [PodDate],
                NULLIF(PODReceivedBy, '''')   as [PodReceivedBy],
                Pieces          as [PieceCount],
                [Weight]        as [TotalWeight],
                UnitOfWeight    as [UnitOfWeight]
            FROM vwOrderDetail_GetOdysseyShipmentHeader
            WHERE ShipmentID = @ordOrderID

            -- MilestonEvent
            exec [dbo].[spOrderDetail_IntlShipmentRemarks] @HawbID=@ordOrderID',N'@ordOrderID uniqueidentifier,@trackingNumber nvarchar(13)',@ordOrderID='39CB3F55-97B8-429A-B075-058EBD61475A',@trackingNumber=N'WWT1590094600'