Microsoft Dynamics GP SOP has the ability to store the tracking numbers for shipments. Open the SOP invoice, and open the user-defined fields to enter or obtain the tracking information. If the custom links are set up correctly for your shippers, you can drill into the tracking information on the Internet from the SOP Invoice.
Yes, the trick is getting the shippers set up correctly. Each shipper has a different link that needs to be used, as they all store tracking numbers differently. Review this Microsoft Support KB article for more information: http://bit.ly/GPsopTrack.
This distribution company sells product through some websites that require that shipping to occur within a specified time period. In an attempt to retrieve tracking numbers with the invoice information, tracking numbers are stored in the GP invoice user-defined window.
When a customer calls to find out the status of their order, it takes a while to get to the tracking information. The company wanted an Excel document that shows all orders shipped within the last 30 days with links to the shippers' tracking web pages, in order to shorten the time it takes to review the status of the shipment.
We will be using an Excel table for this report as well. This will allow for quick refresh, and can be extended to non-GP users.
There are two parts to these steps. The first part is technical, containing steps that involve work in SQL Server. The second part is the actual building of the report. You may choose to obtain assistance with the technical part, especially if you do not have access to SQL Server.
Create the following view in SQL Server:
We've warned you about SQL; don't mess it up.
CREATE VIEW view_BI_SOP_TrackingNumber AS SELECT 'Posted' AS Status, CASE SH.soptype WHEN 3 THEN 'Invoice' WHEN 4 THEN 'Return' WHEN 1 THEN 'Quote' WHEN 2 THEN 'Order' WHEN 5 THEN 'Backorder' WHEN 6 THEN 'Fulfillment_Order' ELSE 'Other' END AS Doc_Type, SH.SOPNUMBE AS Doc_Number, SH.DOCDATE AS Doc_Date, SH.ReqShipDate AS Requested_Ship, SH.ACTLSHIP AS Actual_Ship, SH.CUSTNMBR AS Customer_ID, SH.CUSTNAME AS Customer, SH.CSTPONBR AS Customer_PO, SH.MSTRNUMB AS Master_Num, SH.PRSTADCD AS Ship_To_ID, SH.ShipToName AS Ship_To, SH.ADDRESS1 AS Address_1, SH.ADDRESS2 AS Address_2, SH.ADDRESS3 AS Address_3, SH.CITY AS City, SH.STATE AS STATE, SH.ZIPCODE AS Zip, SH.CNTCPRSN AS Contact, SH.PHNUMBR1 AS Phone, ST.Tracking_Number, SH.SHIPMTHD AS Shipping_Method, CL.CUSTOMLINKLBL AS GP_Link, RTRIM(REPLACE(CL.CUSTOMLINKINETADDR, '%1', RTRIM(ST.Tracking_Number))) AS URL FROM dbo.SOP30200 AS SH INNER JOIN dbo.SOP10107 AS ST ON SH.SOPNUMBE = ST.SOPNUMBE AND SH.SOPTYPE = ST.SOPTYPE INNER JOIN dbo.SY01201 AS CL ON SH.SHIPMTHD = CL.CUSTOMLINKFIELDVAL WHERE (CL.CUSTOMLINKPRMPT = 9) AND (SH.ACTLSHIP >= DATEADD(DAY, - 30, GETDATE())) Union SELECT 'Unposted' AS Status, CASE SH.soptype WHEN 3 THEN 'Invoice' WHEN 4 THEN 'Return' WHEN 1 THEN 'Quote' WHEN 2 THEN 'Order' WHEN 5 THEN 'Backorder' WHEN 6 THEN 'Fulfillment_Order' ELSE 'Other' END AS Doc_Type, SH.SOPNUMBE AS Doc_Number, SH.DOCDATE AS Doc_Date, SH.ReqShipDate AS Requested_Ship, SH.ACTLSHIP AS Actual_Ship, SH.CUSTNMBR AS Customer_ID, SH.CUSTNAME AS Customer, SH.CSTPONBR AS Customer_PO, SH.MSTRNUMB AS Master_Num, SH.PRSTADCD AS Ship_To_ID, SH.ShipToName AS Ship_To, SH.ADDRESS1 AS Address_1, SH.ADDRESS2 AS Address_2, SH.ADDRESS3 AS Address_3, SH.CITY AS City, SH.STATE AS STATE, SH.ZIPCODE AS Zip, SH.CNTCPRSN AS Contact, SH.PHNUMBR1 AS Phone, ST.Tracking_Number, SH.SHIPMTHD AS Shipping_Method, CL.CUSTOMLINKLBL AS GP_Link, RTRIM(REPLACE(CL.CUSTOMLINKINETADDR, '%1', RTRIM(ST.Tracking_Number))) AS URL FROM dbo.SOP10100 AS SH INNER JOIN dbo.SOP10107 AS ST ON SH.SOPNUMBE = ST.SOPNUMBE AND SH.SOPTYPE = ST.SOPTYPE INNER JOIN dbo.SY01201 AS CL ON SH.SHIPMTHD = CL.CUSTOMLINKFIELDVAL WHERE (CL.CUSTOMLINKPRMPT = 9) AND (SH.ACTLSHIP >= DATEADD(DAY, - 30, GETDATE())) GO GRANT SELECT ON view_BI_SOP_TrackingNumber TO DYNGRP
Let's build a report!
Know what you want. Yeah, we're kind of a know-it-all, but we know what we want:
Note that this report will only show SOP documents that have tracking numbers assigned to them.
If a shipment is missing from this list, the probable cause is that the tracking number is missing, the custom link that contains the URL has not been set up, or the shipping method does not match the custom link that contains the URL. It can also be a combination of any or all of these issues.
Let's make a connection to the GP data:
The report displays all orders with tracking numbers and actual shipping dates in the last 30 days for both posted and unposted documents. Clicking on this hyperlink we created will open the tracking information window for the appropriate shipper on the shipper's website, providing you with the tracking information for this order.
You can also filter this report by customer, customer PO number, master number, dates, and other fields. All of this information is available directly from Excel, eliminating the need to log into GP.
By using this report, the company's shipping department can handle phone calls for all shipped orders (they enter a tracking number for every order) from this spreadsheet. If the order does not display, the call is forwarded to the sales department for research. This company saves time and user licenses by using this Microsoft Excel Table.