help with SQL query for HQ

[ZCSF]

Hello!
Need two queries.
1. to clear the bin location field for all items
2. to assign a numberical value for the bin location field for each of our
stores. ex. store 1 bin location field to read 1, store store bin location
field to read 2
any help would as always be greatly appreciated...
thank you

 

[Akber Alwani]

hi zcsf,
1. To Clear bin location Run this
UPDATE ITEM SET BINLOCATION=''
2. To Update the bin location with store id
UPDATE ITEM SET BINLOCATION=storeid
from Configuration

Rate please.

 

[Terrible Tom]

Query #1 will work. This will clear the BinLocation field.

Query #2 will only work at the store database level, not at HQ. There is no
way to assign store-specific bin locations. What bin location do you use for
items that exist at multiple stores? There is no StoreID with which to
populate the BinLocation field in the HQ DB.

Setting a bin location of 1 at store 1 is pointless. Store 1 has it on
hand, therefore it's at store 1.

At HQ - Item Properties | Store Quantity tab

At Store Operations - Item Lookup | Check Stores button.


Tom

 

[convoluted]

You still need to the worksheet 51 to update the store databases, right?
Seems like in the hq database the binlocation is tracked in the item table
instead of the itemdynamic table....

 

[Terrible Tom]

One or both of us is/are confused.

PROBLEM: You want to set bin locations at the HQ level that are
store-specific.

WHY YOU CAN'T DO THIS: As you have already noticed, the BinLocation field
is part of the Item table (global information) and not the ItemDynamic (store
specific) table. If Item GADGET-A has a bin location of 1 and GADGET-B has a
bin location of 2, this information will be identical AT ALL STORES.

Changing the BinLocation field in this manner at the store level is
pointless. If you have the item on hand at the store level, then you have it
on hand. Using bin location = this store doesn't help anyone. WS51 could
accomplish this, but to what end?

EVEN WORSE: If you issue a WS250, you will be changing the bin location
value for all of the items on said worksheet at all of the stores that
process the worksheet.

Are you trying to accomplish something other than what the HQ Item
Properties Store Quantity tab and Store Operations 'Check Stores' features
already provide?

There may be a way to set the BinLocation field like this:
4 stores (1,2,3,4)
GADGET-A Bin Location = 134 (none at store 2)
GADGET-B Bin Location = 3 (only available at store 3)
GADGET-C Bin Location = 1234 (in stock at all stores)

My SQL-fu is not strong, maybe somebody else can help with this part...

For each ItemID you would want to SELECT from ItemDynamic the StoreIDs where
Quantity > 0 (or perhaps Quantity > QuantityCommitted), concatenate the
selected StoreID(s) and UPDATE Item.BinLocation for each Item.ID with the
concatenated string.

You would then issue a WS250 for all items. This would be a 'snapshot' kind
of thing, and you would want to perform this task on some sort of schedule.


Tom

 

[ZCSF]

When we print a tag we are using the bin location as a store originator
locater. We have two very small stores that do not have a pos system but we
are able to track movement of inventory due to the bin location per the
inventory labels.
The query I wanted was to reassign the bin location for these stores as per
our latest physical inventory. It would be strictly for HQ purposes only. Ex.
Store 6 has 2200 items that I wanted to ensure the proper bin location. I
assumed I could clear the bin location and then run a query to assign it the
proper code. Store 6 = bin location "6"
I am thinking I asked something too complicated for us right now. I
appreciate all the responses.
Thanks!

 

[Jason]

Uh oh. This is really going to mess up some plans I had. I was just getting
ready to start using Bin locations in my enterprise.

Are you telling me that I can't set Bin locations at the store level and
that if a 250 is done it will wipe out the bin locations I set at the store?
That makes the Bin field completely useless in an HQ environment.

At Store X I keep apples in Bin 1 and at Store Y apples are kept in Bin 4.
Are you telling me that I need to keep apples in the same bin number at all
locations for RMS to work for me? Talk about letting your POS system run
your business. This needs to change!

 

[Terrible Tom]

I never really thought about it much--bin locations aren't critical when
you're dealing with recliners and refrigerators--but I guess you are correct.
Bin locations are useless with HQ unless all stores are identical.

"You are coming to a sad realization. Cancel or allow?"

Tom

 

[convoluted]

This is why I was surprised to find binlocation in the item table in HQ
instead of the itemdynamic table...it doesn't make sense. Suggestion for
improvement?

 

[Jason]

In reality I just want to use Bin locations at one of the stores (which
functions as a warehouse) so I could make it work as long as I update the
bin via HQ, but then I have to remember to do a 250 to send it down (also
time consuming). The real problem is that I can't administer the bin
location at the store without it potentially being overwritten with a 250.

Yuck!

I can't imagine any situation where the bin field would be useful for it's
intended purpose unless your enterprise had a bunch of 100% homogeneous
locations. Seems like a significant miss during HQ's development... This
could be fixed without much issue by changing a 250 to not update the Bin
field. Or... create a "249" as an option...

Come to think of it, wouldn't it be great to be able to make a custom
worksheet that updates selected item fields only?