Skip to main content

Notifications

Microsoft Dynamics RMS forum

Lost Inter store Transfer in limbo

Posted on by Microsoft Employee
I have a situation where every once in a while a store will send transfer to another store and the receiving store will never get it. You can see it is in HQ status closed but not at the receiving store. Sending store inventory has been adjusted correctly. Transfer is in limbo. We are currently sending ws 307 to the receiving store to fix inventory. Any Ideas how to fix or work around these situations?

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Lost Inter store Transfer in limbo

    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

  • Jayvincom876 Profile Picture
    Jayvincom876 50 on at
    RE: Lost Inter store Transfer in limbo

    my transfer ae stuck on partial status can you help?

  • Jayvincom876 Profile Picture
    Jayvincom876 50 on at
    RE: Lost Inter store Transfer in limbo

    Can you send me the exact query you did...my transfers are stuck on partial

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Lost Inter store Transfer in limbo

    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

  • Spencer McCandless Profile Picture
    Spencer McCandless 2,085 on at
    Re: Lost Inter store Transfer in limbo

    Is there anywhere where the database definitions for the HQmessage table are listed? What the numbers in the style and status column correspond to?

  • Suggested answer
    Fordy Profile Picture
    Fordy 70 on at
    Re: Lost Inter store Transfer in limbo

    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.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Lost Inter store Transfer in limbo

    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.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Lost Inter store Transfer in limbo

    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!

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Re: Re: Re: Re: Re: Re: Lost Inter store Transfer in limbo

    @Matt Hurst

    Yes! your steps worked like a charm for me too... :o)

    Thanks!

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Re: Re: Re: Re: Re: Re: Lost Inter store Transfer in limbo
    Ok great. It worked like a charm. This makes my job so much easier adding it as daily procedure from now on. Thanks so much again!!

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,867 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,173 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans