Hi there.
Occasionally, when a store transfers inventory to another store, the receiving store will never get it. I tried running a WS 401 and backdated to the transfer date but it still doesn't work. The only method which works is to run a query script to insert the hqmessage for the transfer that didn't go through.
What I did was:
1. run a report MTD through yesterday to find lost transfers.
2. backdate a worksheet 401 to receiving store from the lost transfer's
creation date
3. process 401 worksheet and check report again to see if the transfer went
4. if still won't go, update the hqmessage entry to flag transfer to
transmit again <-- most effective solution
5. if hqmessage for that transfer doesn't exist, create one to force the queue
6. process another 401 worksheet and check again to see if that went
7. extremely rarely do previous steps not work, but sometime have to put the
inventory back in sending store, remove all trace of previous transfer, and
recreate
here are the queries that accomplish this
1. run a report MTD through yesterday to find lost transfers.
Select
Store.ID, po1.POTitle, Department.Name, po1.DateCreated, po1.LastUpdated,
po1.Status, po1.PONumber, Store.Name, po1.[To], po1.autoid
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 WITH(NOLOCK) ON
po1.ID=PurchaseOrderEntry.PurchaseOrderID AND po1.StoreID =
PurchaseOrderEntry.StoreID
LEFT JOIN Item WITH(NOLOCK) on PurchaseOrderEntry.ItemID=Item.ID
LEFT JOIN Department with(NOLOCK) on Item.DepartmentID=Department.ID
LEFT JOIN Category WITH(NOLOCK) ON Item.CategoryID=Category.ID
LEFT JOIN Supplier WITH(NOLOCK) ON Item.SupplierID=Supplier.ID
LEFT JOIN Store ON po1.OtherStoreID = Store.ID
WHERE
po2.id Is Null and po1.otherstoreid > 0 and po1.status=2 and
po1.otherpoid = 0
and
po1.lastupdated<convert(nvarchar,month(getdate()))+'/'+convert(nvarchar,day(getdate()))+'/'+convert(nvarchar,year(getdate()))
and
po1.lastupdated>convert(nvarchar,month(getdate()))+'/1/'+convert(nvarchar,year(getdate()))
Group By
Store.ID, po1.POTitle, Department.Name, po1.DateCreated, po1.LastUpdated,
po1.Status, po1.PONumber, Store.Name, po1.[To], po1.autoid
Order By po1.PONumber
4. if still won't go, update the hqmessage entry to flag transfer to
transmit again
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 p.autoid=122390
5. if hqmessage for that transfer doesn't exist, create one to force the queue
insert into hqmessage (status, style, fromstoreid, tostoreid, attachmentid,
datesent)
select 2,2,storeid,otherstoreid,id,datecreated from purchaseorder where
autoid=122390
I referred to this thread: https://community.dynamics.com/rms/f/106/t/40321
Matt Hurst's reply on 8 Sep 2010 11:20 AM which solve this problem. But it is extremely manual and tedious. The process should be automatic if it is working as it should.
This seems to be a bug which affect not only me, but other users as well. I wondered if anyone has any permanent solution so far?
Thank you.
Best regards,
Nelson Quek
