Inter-Store Transfer Lost

We have had an ongoing issue with this problem and determined that it happens when there is a polling error on the sending location. (401) The transfer gets closed at HQ and will not sent to recieving location. I usually run a SQL 51 worksheet telling the sending store to reopen and send the transfer again then it will pass to the recieving store. The polling error is usually a deadlock between two polling locations. We poll about 300 locations every night through 4 comm servers.

Paul / Eastern Nati> I am having a problem with Inter-store transfers. They are committed at

"Paul Houst> We have had an ongoing issue with this problem and determined that it

Sorry about that

You will need to f> Hey Paul,

PurchaseOrderEntry tables,

transfers gets into

I have found a few solutions that have helped since I first tackled this problem.

1. run a report every day to find these problems before the stores do. select po1.StoreID,po1.POTitle,sum(e.quantityordered) as Qty, sum(e.quantityordered*e.price) as Total, Department.Name,po1.DateCreated,po1.LastUpdated,po1.PONumber,po1.[shipTo] from purchaseorder as po1 WITH(NOLOCK) left join purchaseorder as po2 WITH(NOLOCK) on po2.otherpoid = po1.id and po2.otherstoreid = po1.storeid Inner JOIN PurchaseOrderEntry e WITH(NOLOCK) ON po1.ID=e.PurchaseOrderID AND po1.StoreID = e.StoreID LEFT JOIN Item WITH(NOLOCK) on e.ItemID=Item.ID LEFT JOIN Department with(NOLOCK) on Item.DepartmentIDÞpartment.ID where po2.id Is Null and po1.otherstoreid > 0 and po1.status=2 and po1.otherpoid = 0 group by po1.StoreID,po1.POTitle,Department.Name,po1.DateCreated,po1.LastUpdated,po1.PONumber,po1.[shipTo] Order by po1.LastUpdated

1. transfers that were missed within the last day can often be fixed by backdating a 401 worksheet 1 day to the receiving store

be careful of the next steps because they can cause double transfers to the recieving stores if the problem is just that the recieving store isn't connecting to hq

1. next step if that doesn't work is to try refreshing the hq entry to forward the transfer. Use the ponumber and storeid from the store that created the transfer update h set h.status=2 from purchaseorder p join hqmessage h on fromstoreid=storeid and attachmentid=p.id where style=2 and h.status=3 and ponumber ='0333505' and storeidr

1. if there isn't an entry in hqmessage for that transfer, you may need to create it: insert into hqmessage (status, style, fromstoreid, tostoreid, attachmentid, datesent) select 2,2,storeid,otherstoreid,id,datecreated from purchaseorder where ponumber ='0333505' and storeidr

2. rarely I have found some stubborn transfers that just w> Is there any way of creating a query for transfers that were never received

PurchaseOrderEntry tables,

transfers gets into