Sage 500 TranKeys Solution

Problem:

One of our clients uses Sage 500 as its ERP. One shortcoming we encountered when dealing directly with the data in the tables is that when tying sales orders to invoices, sales orders to shipments, invoices to shipments, etc using the trankeys, it requires multiple table joins to get there.

For example: To tie the sales order to the invoice, you tie the tsoSalesOrder table to tsoSOLine, then tie tsoSOLine to tsoSOLineDist, then tie tsoSOLineDist to tarInvoiceListDist, then tie tarInvoiceListDist to tarInvoiceDetl, then tie tarInvoiceDetl to tarInvoice and then you have tied the sales order to the invoice. That is 5 joins to connect 2 transactions, simply inefficient.

Seems rather like a long way to get to that data. We came up with an idea that seems to work for those of you struggling with a similar dislike of the long path to get two tables for related transactions to tie together. We created a trankeys table that pulls the trankeys from sales orders, invoices, shipments and RMAs into a single table. Then we can tie sales orders, invoices, shipments and RMAs together with one join.

For those that need it, here's the table structure that we used and the way we're populating it.

SQL Script Solution:

CREATE TABLE #tmp
(
SOKey INT NULL
,SOLineKey INT NULL
,SOLineDistKey INT NULL
,InvcKey INT NULL
,InvoiceLineKey INT NULL
,InvoiceLineDistKey INT NULL
,RMAKey INT NULL
,RMALineKey INT NULL
,ShipKey INT NULL
,ShipLineKey INT NULL
,ItemKey INT NULL
,SOTranID VARCHAR(13) NULL
,InvTranID VARCHAR(13) NULL
,RMATranID VARCHAR(13) NULL
,ShipTranID VARCHAR(13) NULL
,ItemID VARCHAR(50) NULL
,CompanyID VARCHAR(3) NOT NULL
,PickListKey INT NULL
,PickListNo VARCHAR(10) NULL
);

-- Create base data
SELECT so.SOKey
,sol.SOLineKey
,sold.SOLineDistKey
,sol.ItemKey
,so.TranID
,i.ItemID
,so.CompanyID
INTO #tmpSO
FROM tsoSalesOrder so WITH ( NOLOCK )
LEFT JOIN tsoSOLine sol WITH ( NOLOCK ) ON so.SOKey = sol.SOKey
LEFT JOIN tsoSOLineDist sold WITH (NOLOCK) ON sol.SOLineKey = sold.SOLineKey
LEFT JOIN timItem i WITH ( NOLOCK ) ON sol.ItemKey = i.ItemKey
WHERE so.CompanyID IN (YOUR LIST OF COMPANY IDs HERE);

SELECT i.InvcKey
,d.InvoiceLineKey
,l.InvoiceLineDistKey
,l.SOLineDistKey
,d.SOLineKey
,d.ItemKey
,d.ShipLineKey
,i.TranID
,it.ItemID
,i.CompanyID
INTO #tmpInv
FROM tarInvoice i WITH (NOLOCK)
LEFT JOIN tarInvoiceDetl d WITH (NOLOCK) ON i.InvcKey = d.InvcKey
LEFT JOIN tarInvoiceLineDist l WITH (NOLOCK) ON d.InvoiceLineKey = l.InvoiceLineKey
LEFT JOIN timItem it WITH (NOLOCK) ON d.ItemKey = it.ItemKey
WHERE i.CompanyID IN (YOUR LIST OF COMPANY IDs HERE);

SELECT r.RMAKey
,l.RMALineKey
,l.SOLineDistKey
,l.ShipLineKey
,it.ItemKey
,r.TranID
,it.ItemID
,r.CompanyID
,ship.InvcKey
INTO #tmpRMA
FROM tsoRMA r WITH (NOLOCK)
LEFT JOIN tsoRMALine l WITH (NOLOCK) ON r.RMAKey = l.RMAKey
LEFT JOIN timItem it WITH (NOLOCK) ON l.ItemKey = it.ItemKey
LEFT JOIN tsoShipLine sl WITH (NOLOCK) ON l.ShipLineKey = sl.ShipLineKey
LEFT JOIN tsoShipment s WITH (NOLOCK) ON sl.ShipKey = s.ShipKey
LEFT JOIN tsoInvoiceShipment ship WITH (NOLOCK) ON s.ShipKey = ship.ShipKey
WHERE r.CompanyID IN (YOUR LIST OF COMPANY IDs HERE);

SELECT s.ShipKey
,ld.SOLineDistKey
,l.ShipLineKey
,l.SOLineKey
,l.ItemKey
,s.TranID
,i.ItemID
,s.CompanyID
,invship.InvcKey
,l.PickListKey
,pl.PickListNo
INTO #tmpShip
FROM tsoShipment s WITH (NOLOCK)
LEFT JOIN tsoShipLine l WITH (NOLOCK) ON s.ShipKey = l.ShipKey
LEFT JOIN tsoShipLineDist ld WITH (NOLOCK) ON l.ShipLineKey = ld.ShipLineKey
LEFT JOIN timItem i WITH (NOLOCK) ON l.ItemKey = i.ItemKey
LEFT JOIN tsoInvoiceShipment invship WITH (NOLOCK) ON s.ShipKey = invship.ShipKey
LEFT JOIN tsoPickList pl WITH (NOLOCK) ON l.PickListKey = pl.PickListKey
WHERE s.CompanyID IN (YOUR LIST OF COMPANY IDs HERE);

INSERT
INTO #tmp
(
SOKey
,SOLineKey
,SOLineDistKey
,InvcKey
,InvoiceLineKey
,InvoiceLineDistKey
,RMAKey
,RMALineKey
,ShipKey
,ShipLineKey
,ItemKey
,SOTranID
,InvTranID
,RMATranID
,ShipTranID
,ItemID
,CompanyID
,PickListKey
,PickListNo
)
SELECT so.SOKey
,so.SOLineKey
,so.SOLineDistKey
,inv.InvcKey
,inv.InvoiceLineKey
,inv.InvoiceLineDistKey
,rma.RMAKey
,rma.RMALineKey
,ship.ShipKey
,ship.ShipLineKey
,COALESCE(so.ItemKey, inv.ItemKey, rma.ItemKey, ship.ItemKey) ItemKey
,so.TranID SOTranID
,inv.TranID InvTranID
,rma.TranID RMATranID
,ship.TranID ShipTranID
,COALESCE(so.ItemID, inv.ItemID, rma.ItemID, ship.ItemID) ItemID
,COALESCE(so.CompanyID, inv.CompanyID, rma.CompanyID, ship.CompanyID) CompanyID
,ship.PickListKey
,ship.PickListNo
FROM #tmpSO so
FULL JOIN #tmpInv inv ON so.SOLineKey = inv.SOLineKey
AND so.SOLineDistKey = inv.SOLineDistKey
FULL JOIN #tmpShip ship ON so.SOLineDistKey = ship.SOLineDistKey
AND inv.InvcKey = ship.InvcKey
FULL JOIN #tmpRMA rma ON so.SOLineDistKey = rma.SOLineDistKey
AND inv.InvcKey = rma.InvcKey

/*
Backfill here
This corrects issues with things that are added to invoices directly, thereby adding ties to things like SO that don't exist
*/

UPDATE t
SET SOKey = t2.SOKey
FROM #tmp t
JOIN #tmp t2 ON t.InvcKey = t2.InvcKey
WHERE t.InvcKey IS NOT NULL
AND t.SOKey IS NULL
AND t2.SOKey IS NOT NULL;

UPDATE t
SET ShipKey = t2.ShipKey
FROM #tmp t
JOIN #tmp t2 ON t.InvcKey = t2.InvcKey
WHERE t.InvcKey IS NOT NULL
AND t.ShipKey IS NULL
AND t2.ShipKey IS NOT NULL;

UPDATE t
SET SOTranID = t2.SOTranID
FROM #tmp t
JOIN #tmp t2 ON t.InvcKey = t2.InvcKey
WHERE t.InvcKey IS NOT NULL
AND t.SOTranID IS NULL
AND t2.SOTranID IS NOT NULL;

UPDATE t
SET ShipTranID = t2.ShipTranID
FROM #tmp t
JOIN #tmp t2 ON t.InvcKey = t2.InvcKey
WHERE t.InvcKey IS NOT NULL
AND t.ShipTranID IS NULL
AND t2.ShipTranID IS NOT NULL;

DROP TABLE #tmpInv;
DROP TABLE #tmpRMA;
DROP TABLE #tmpShip;
DROP TABLE #tmpSO;

/* From this point you have the data loaded into your #tmp table so you can push itto your live table however you want
We chose to just dump the data in the table and then reload it. It can be handled multiple ways but our process
runs once per night so no need to keep it updated in real time
*/

BEGIN TRANSACTION myTran;

TRUNCATE TABLE tblTranKeys;

INSERT
INTO tblTranKeys
( SOKey, SOLineKey, SOLineDistKey, InvcKey, InvoiceLineKey,InvoiceLineDistKey, RMAKey, RMALineKey, ShipKey, ShipLineKey, ItemKey, SOTranID, InvTranID, RMATranID, ShipTranID, ItemID, CompanyID, PickListKey, PickListNo )
SELECT SOKey, SOLineKey, SOLineDistKey, InvcKey, InvoiceLineKey,InvoiceLineDistKey, RMAKey, RMALineKey, ShipKey, ShipLineKey, ItemKey, SOTranID, InvTranID, RMATranID, ShipTranID, ItemID, CompanyID, PickListKey, PickListNo
FROM #tmp;

IF @@ERROR <> 0
ROLLBACK TRANSACTION myTran;
ELSE
COMMIT TRANSACTION myTran;

DROP TABLE #tmp;

Conclusion:

Feel free to use this script however you like, however, Crackerjack-IT, Inc. is not responsible for the data accuracy in your system as this is adapted to one of our clients' systems, it is not guaranteed to work for your needs. If you need assistance with it, feel free to reach out to us.

Crackerjack-IT Blog

Problem:

SQL Script Solution:

Conclusion: