*This post is locked for comments
If you're stating the status itself shows partial this is more than likely the result of the transfer being partially filled if memory serves me correctly(part ABC123 was on order for 100 pieces and when the transfer was received in the receiver didn't receive in all 100...they may have keyed in a lesser number like 80 thus the transfer is partially filled as 20 additional should have been received in to make it fully filled at 100 pieces.)
The queries stated in the earlier response by Matt Hurst responded on 8 Sep 2010 12:20 PM will help you if your transfers are in fact closed in the sending store and going to the HQ server but not downloading to the receiving store(seemingly 'in limbo' as the title of this thread suggests.) These queries have to be run on the HQ server(remember always backup your DB beforehand just to be safe.
As for the cause of the transfers going into this 'limbo' state this is caused by a timing issue(at least it appears to be in my case...I have 13 stores and 2 warehouses(also set up as stores in RMS.) I have my main warehouse set up to poll HQ about 10 past the hour and then my secondary warehouse about 3 minutes past that as 99% of my transfers come from these 2 locations and go to the other 13. The other stores poll one at a time after the secondary warehouse does at about 3 minute intervals. All locations are polling on this schedule once an hour every hour except for between 11pm and 7am as I don't have stores polling during that time so I can use it as a maintenance window for the SQL servers etc.) This timing works for 99.9% of my transfers as I have found to prevent the issue the sending store must poll HQ before the receiving store and the receiving store has to poll HQ within the same hour or the transfer appears to go into this limbo state. I generally only see this happening in my setup if one of my stores (which polls after the warehouses) is sending something back to the warehouses. I then run the first SQL script on the HQ database to find the missing transfer and then I run the other SQL script to update the HQmessage table for the missing transfer. Then I'll initiate a 401 worksheet from HQ for the receiving store and manually open the HQ client on the receiving store and click connect so that I can see it poll...if you watch and everything was done correctly you'll see something like this in the HQ client window as it connects to HQ and downloads the transfer...
Created 8 inter-store inventory transfer orders
updated 132 inventory transfer entry records to reflect new costs from issuing store.
downloaded and processed 16 records from table HQMessage.
Sorry for being a bit long winded but I hope this helps answer your question and clarify what's actually going on. If not please reply and I'll do my best to help if I can. It's tough with this product
seeing it's end of life but there are still a few people running it that may be of help to you.
Sincerely,
Chris
my transfer ae stuck on partial status can you help?
Can you send me the exact query you did...my transfers are stuck on partial
Anyone know what is the cause of this problem? It is counter-productive to check it manually every single day. Is there any fixes for this?
Thanks.
Nelson
Is there anywhere where the database definitions for the HQmessage table are listed? What the numbers in the style and status column correspond to?
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=Department.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
2. 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
3. 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 storeid=72
4. 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 storeid=72
5. rarely I have found some stubborn transfers that just won't send. In
those cases I have had to put the items back in the sending store's
inventory, remove the po from hq and sending store: Purchaseorder,
Purchaseorderentry, Inventorytransferlog, hqmessage; and recreate the
transfer.
I think the problem has something to do with stores not connecting with HQ on the right schedule, and when a transfer is submitted or uploaded to HQ late and the receiving store has already synced for that time period, the connection gets missed.
Matt,
Thanks for sharing your knowledge with us. I too have this issue. It's disturbing that this is happening but it doesn't appear that MS has addressed this. Any ideas as to the root cause of this problem? I have noticed that when this occurs it is usually on days that have heavier transfers(today transfers from our main warehouse which is set up as a store to 7 different shops...about 30 total transfer orders.) On the first occurrence the numbers were similar.
Also on my end both times I've had to run the insert scripts to place the entries into the hqmessage table as the entries didn't exist with just a status flagged incorrectly. I did notice that upon running the insert the first field 'ID' in HQMESSAGE is populating with a zero instead of autoincrementing...is this acceptable or is there something else that would need to be done to correct this? Please let me know.
Thanks again!
@Matt Hurst
Yes! your steps worked like a charm for me too... :o)
Thanks!
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,867 Super User 2024 Season 2
Martin Dráb 229,173 Most Valuable Professional
nmaenpaa 101,156