ShipRush
SQL: ExamplesShipRush
SQL: ExamplesQuick Links:
The ShipRush SQL Examples can be downloaded in a zip. The zip has the examples packaged in XML files that import directly into ShipRush.
This example requires that QuickBooks be accessible via ODBC. A popular tool for this is QODBC. The read-only version of QODBC is included with QuickBooks Enterprise Edition (on the QuickBooks CDROM). To post shipping details back to QuickBooks requires the read-write version of QODBC.
The 'get' query:
SELECT
i.RefNumber as RecordID,
i.ShipAddressAddr1 as ContactName,
i.ShipAddressAddr2 as CompanyName,
i.ShipAddressAddr3 as Address1,
i.ShipAddressCity as City,
i.ShipAddressState as State,
i.ShipAddressPostalCode as ZIP,
i.ShipAddressCountry as Country,
c.Phone as Phone,
c.Email as Email,
i.RefNumber as Reference
FROM Invoice i
LEFT OUTER JOIN Customer c ON c.ListID = i.CustomerRefListID
WHERE
i.RefNumber = '%SEARCHPARAM%'
To save the shipment to the QuickBooks sales order. Note that for this examle SQL to work, there must be two custom fields added to the Invoice/Sales Order: TrackNo and ShipCharges.
UPDATE INVOICE
SET
CustomFieldShipCharges = '%SHIPPINGCHARGES%',
CustomFieldTrackNo = '%TRACKINGNUMBER%'
WHERE
RefNumber = '%RecordID%'
The 'get' data query is simple:
SELECT
o.OrderID as RecordID,
o.ShipName as ContactName,
c.CompanyName as CompanyName,
o.ShipAddress as Address1,
o.ShipCity as City,
o.ShipRegion as State,
o.ShipPostalCode as ZIP,
o.ShipCountry as Country,
c.Phone as Phone
FROM dbo.orders o
LEFT OUTER JOIN dbo.Customers c ON c.CustomerID = o.CustomerID
WHERE o.OrderID LIKE '%SEARCHPARAM%%'
That is it.
The write history query is also simple:
update dbo.Orders set Freight = %SHIPPINGCHARGES% where OrderID = %RecordID%
Retrieving the Ship To information:
SELECT
o.OrderID as RecordID,
o.ShipName as ContactName,
c.CompanyName as CompanyName,
o.ShipAddress as Address1,
o.ShipCity as City,
o.ShipRegion as State,
o.ShipPostalCode as ZIP,
o.ShipCountry as Country,
c.Phone as Phone
FROM orders o
LEFT OUTER JOIN Customers c ON c.CustomerID = o.CustomerID
WHERE o.OrderID LIKE '%SEARCHPARAM%%'
Writing the shipping charges back to the Northwind database:
update Orders set Freight = %SHIPPINGCHARGES% where OrderID = %RecordID%
Getting the ship to information based on the Sales Order number:
select
SopNumbe as RecordID,
CustName as CompanyName,
ShipToName as ContactName,
Address1 as Address1,
Address2 as Address2,
Address3 as Address3,
City as City,
State as State,
ZipCode as ZIP,
Country as Country,
substring(PhNumbr1,1,10) as Phone,
FaxNumbr as Fax
FROM dbo.SOP10100
WHERE SopNUMBE LIKE '%SEARCHPARAM%%'
Writing back the tracking number and cost:
DECLARE @SOPType int
DECLARE @CommentsExist int
DECLARE @RecID varchar(100)
DECLARE @Comments varchar(2000)
DECLARE @TrackingNumber varchar(100)
DECLARE @PtrVal binary(16)
SET @RecID = '%RecordID%'
SET @TrackingNumber = '%SHIPPINGCARRIER%:%TRACKINGNUMBER%'
SET @Comments = '%SHIPPINGSERVICE% TRK:%TRACKINGNUMBER% '
SELECT @SOPType=SOPTYPE FROM dbo.SOP10100 WHERE SOPNumbe = @RecID
IF (@SOPType > 0) BEGIN
SELECT @CommentsExist=Count(*) FROM dbo.SOP10106 WHERE SOPNumbe = @RecID
IF (@CommentsExist > 0) BEGIN
SELECT @PtrVal=TEXTPTR(CMMTTEXT) FROM dbo.SOP10106 WHERE SOPNumbe = @RecID
UPDATETEXT dbo.SOP10106.CMMTTEXT @PtrVal 0 0 @Comments
END ELSE BEGIN
INSERT INTO dbo.SOP10106 (SOPTYPE, SOPNUMBE, CMMTTEXT) VALUES ( @SOPType, @RecID, @Comments)
END
INSERT INTO dbo.SOP10107 (SOPTYPE, SOPNUMBE, Tracking_Number) VALUES ( @SOPType, @RecID, @TrackingNumber)
END
-- *** PLEASE DO NOT REMOVE THIS HEADER ***
-- ShipRush SQL Example Script – Not supported by
-- Z-Firm support.
-- Rights granted to licensed ShipRush users only
-- Modify to taste
-- For use only with ShipRush.
DECLARE @SOPType int
DECLARE @CommentsExist int
DECLARE @RecID varchar(100)
DECLARE @Comments varchar(2000)
DECLARE @TrackingNumber varchar(100)
DECLARE @PtrVal binary(16)
SET @RecID = '%RecordID%'
SET @TrackingNumber = '%SHIPPINGCARRIER%:%TRACKINGNUMBER%'
SET @Comments = '%SHIPPINGSERVICE% TRK:%TRACKINGNUMBER% '
SELECT @SOPType=SOPTYPE FROM dbo.SOP10100
WHERE SOPNumbe = @RecID
IF (@SOPType > 0) BEGIN
SELECT @CommentsExist=Count(*) FROM dbo.SOP10106
WHERE SOPNumbe = @RecID
IF (@CommentsExist > 0) BEGIN
SELECT @PtrVal=TEXTPTR(CMMTTEXT) FROM dbo.SOP10106
WHERE SOPNumbe = @RecID
UPDATETEXT dbo.SOP10106.CMMTTEXT @PtrVal 0 0 @Comments
END ELSE BEGIN
INSERT INTO dbo.SOP10106
(SOPTYPE, SOPNUMBE, CMMTTEXT) VALUES
( @SOPType, @RecID, @Comments)
END
INSERT INTO dbo.SOP10107 (SOPTYPE, SOPNUMBE, Tracking_Number)
VALUES ( @SOPType, @RecID, @TrackingNumber)
END
Reading the ship to information:
SELECT
S.OrdNbr as RecordID,
H.ShipName as CompanyName,
A.Name as ContactName,
A.Addr1 as Address1,
A.Addr2 as Address2,
A.City as City,
A.EmailAddr as EMail,
A.State as State,
A.Zip as ZIP,
A.Country as Country,
A.Phone as Phone,
A.Fax as Fax
FROM SOSched S WITH (NOLOCK)
LEFT JOIN SOHeader H WITH (NOLOCK) ON H.CpnyID = S.CpnyID AND H.OrdNbr = S.OrdNbr
LEFT OUTER JOIN SOAddress A WITH (NOLOCK) ON (A.CustId = H.CustID) and (A.ShipToId = H.ShipToID)
WHERE
(S.OrdNbr LIKE '%SEARCHPARAM%%')
Writing back the tracking number:
DECLARE @RecID varchar(100)
DECLARE @CompanyNumber varchar(100)
DECLARE @TrackingNumber varchar(100)
SET @RecID = '%RecordID%'
SET @TrackingNumber = '%SHIPPINGCARRIER%:%TRACKINGNUMBER%'
SET @CompanyNumber = '0060'
exec ADG_SOEvent_Create @CompanyNumber, @TrackingNumber, 'CSHP', @RecID, '', '', ''
Reading the ship to information from the order:
SELECT
OrderNumber as RecordID,
ShippingFirstname + ' ' + shippinglastname as ContactName,
ShippingCompany as CompanyName,
ShippingAddress1 as Address1,
ShippingAddress2 as Address2,
ShippingSuite as Address3,
ShippingCity as City,
ShippingState as State,
ShippingZip as ZIP,
ShippingCountry as Country,
ShippingPhone as Phone,
Email as Email,
'C:\Temp\1\1pound.zpk' as ShippingTemplate
FROM dbo.orders
WHERE ordernumber = %SEARCHPARAM%
Writing back the tracking number and shipping service to the Storefront database:
Update orders set ShippingTrackingNumber = '%TRACKINGNUMBER%' where ordernumber = %RecordID%
GO
Update orders set ShippingMethod = '%SHIPPINGSERVICE%' where ordernumber = %RecordID%
GO
This first set of scripts revolves around the RMS Shipping Queue. Sales must be tagged for shipping in the usual RMS way for this to work. Note that this is the only way to save tracking information for sales:
SELECT
s.TransactionNumber as RecordID,
s.[name] as ContactName,
s.Company as CompanyName,
s.Address as Address1,
s.Address2 as Address2,
s.City as City,
s.State as State,
s.ZIP as ZIP,
s.Country as Country,
s.PhoneNumber as Phone,
s.EmailAddress as EMail
FROM Shipping s
where
s.TransactionNumber LIKE '%SEARCHPARAM%%'
The above will find any order. To find only unprocessed shipments, change the 'where' clause above to:
where s.TransactionNumber LIKE '%SEARCHPARAM%%' AND DateProcessed is NULL
Writing back the tracking number and marking the transaction as Shipped:
UPDATE Shipping
Set TrackingNumber = '%TrackingNumber%',
Notes = '%SHIPPINGSERVICE% TRK:%TRACKINGNUMBER% Cost: %SHIPPINGCHARGES%',
DateProcessed = getdate()
-- Optional Section. Uncomment to enable
/*
,
carriername = '%ShippingCarrier%',
ServiceName = '%ShippingService%',
TotalWeight = %ShippingWeight%
*/
where transactionnumber = %RecordID%
This next query will work for read-access to RMS, whether or not the transaction was marked for shipping. However there is no place to write back the tracking number, so that is not present here.
DECLARE @UseShipTo INT
select @UseShipTo=t.ShipToID from [transaction] t
where t.TransactionNumber = 85 --'%SEARCHPARAM%'
IF (@UseShipTo = 0) BEGIN
-- This means that the default Customer address is used for
-- this xaction, from the Customer table
SELECT
t.TransactionNumber as RecordID,
c.firstname + ' ' + c.lastname as ContactName,
c.Company as CompanyName,
c.Address as Address1,
c.Address2 as Address2,
c.City as City,
c.State as State,
c.ZIP as ZIP,
c.Country as Country,
c.PhoneNumber as Phone,
c.EmailAddress as EMail
FROM Customer c, [transaction] t
where
t.CustomerID = c.ID
and t.TransactionNumber = 85 --'%SEARCHPARAM%%'
END
IF (@UseShipTo <> 0) BEGIN
-- Follow ShipToID
SELECT
t.TransactionNumber as RecordID,
s.[name] as ContactName,
s.Company as CompanyName,
s.Address as Address1,
s.Address2 as Address2,
s.City as City,
s.State as State,
s.ZIP as ZIP,
s.Country as Country,
s.PhoneNumber as Phone,
s.EmailAddress as EMail
FROM ShipTo s, [transaction] t
where
t.ShipToID = s.ID
and t.TransactionNumber = 85 --'%SEARCHPARAM%%'
END
Next: Programming Notes