Tip: The SQL Webstore Examples can be downloaded in a zip here. For Microsoft RMS, a special kit can be downloaded here.

QuickBooks

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, and can be downloaded here. 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: For this example 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%'

Northwind Example Database: SQL Server

'Get' Data Query:

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%%'

History Write Query

update dbo.Orders set Freight = %SHIPPINGCHARGES% where OrderID = %RecordID%

Northwind Example Database: Access

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%

Microsoft Dynamics GP (aka Great Plains)

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

Descartes ShipRush SQL Script for Write-Back to Microsoft GP v8

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

Microsoft Dynamics SL (aka Solomon) Example

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, '', '', ''

ASPDotNetStorefront

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:Temp11pound.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

Microsoft Retail Management System (aka RMS or MSRMS)

For Microsoft RMS, a special kit can be downloaded with SQL and sample ZPK templates.

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: SQL Webstore - Programming Notes

SQL Webstore - Example Scripts