Order not yet placed

Posted by Rick@ASP on September 23, 2009, 4:39 pm

Please log in for more thread options

My stores perform inter-store transfers all the time. Often, the sending
store does not click on "Order has been placed." Most times, once they issue
the items, the transfer shows up in the receiving store, but not always.

I have several closed inter-store transfers where inventory has been
relieved from the sending store, but the transfer never appears for the
receiving store. (And the data does not appear in the receiving stores'
PurchaseOrder table.)

Can anyone help?

Posted by Paul Houston / on September 24, 2009, 8:04 am

Please log in for more thread options

Was there and error on the 401 worksheet at the time the transfer was
comitted? We have had this happen on many occassion when the sending
store had an error polling the 401 worksheet. Usually the error is a deadlock
on resources type error and the Transfer is closed on the sending side so
it never gets sent to the recieving store. This has been occuring since
day one and has never been fixed. We have gone from v1.1 to V2.0 sp2
and still have the problem. We normally sent a Sql statement to the
sending store to change the status and resend the transfer..

Paul H

"Rick@ASP" wrote:

> My stores perform inter-store transfers all the time. Often, the sending

> store does not click on "Order has been placed." Most times, once they issue

> the items, the transfer shows up in the receiving store, but not always.

> I have several closed inter-store transfers where inventory has been

> relieved from the sending store, but the transfer never appears for the

> receiving store. (And the data does not appear in the receiving stores'

> PurchaseOrder table.)

> Can anyone help?

Posted by Matt Hurst on September 25, 2009, 12:04 pm

Please log in for more thread options

There is a daily proceedure that I do to find and fix this problem:
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
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

"Rick@ASP" wrote:

> My stores perform inter-store transfers all the time. Often, the sending

> store does not click on "Order has been placed." Most times, once they issue

> the items, the transfer shows up in the receiving store, but not always.

> I have several closed inter-store transfers where inventory has been

> relieved from the sending store, but the transfer never appears for the

> receiving store. (And the data does not appear in the receiving stores'

> PurchaseOrder table.)

> Can anyone help?