MPQ,Restock Level and Restock Point

Terry - the MPQ as you are finding out won't work because your selling unit unit of measure is different than your ordering unit unit of measure. The only way to get this to work is to set up a parent/child relationship. The parent must be the ordering unit (the case of 25 lbs) and the child must be the selling unit (the item you sell by the pound). It will be a little tricky, search the newsgroup and you will get a lot of valid pointers about the shortcomings of this approach, and they are all valid.

Set the SKU you sell by the pound as "may not be placed on purchase order" in the options tab, and in the special tab link it to the parent item (the case) and your child qty = 25.

The biggest problem with this setup is that (out of the box) there is no way that the system knows that the child item still has 50 lbs in the store, and that if the parent SKU has 0 it should NOT reorder because you still have 50 lbs of the child unit in stock.

Thus, you need to do some manual checks on the standard reordering process, kind of defeating the whole purpose of automation in my opinion. A possible workaround is running a SQL script on a nightly basis that "edits" your parent item based on the current stock qty of the child item. The editing could insert (or delete) a certain value (something like "time to order" on a subdescription field, or the notes field), then when you run your PO generation you set a supplier and a filter that reads that "time to order" value. I haven't developed such a script but if you are interested I could spend a little time in the playground with my friends.

My friends being these pretty keys on this pretty keyboard.

H> I deal with Bulkfood stores and I am confused on how to setup the reorder for > a PO.

I think setting up a couple of views that track parent info and child/parent info can be used to stage the data an update query can use to flag a parent item as "orderable" or "not orderable yet". Basically, if the child quantity in stock is less than the child quantity in the parent, then do not reorder. Now, different RMS users will have different criteria for ordering the parent, for example, if the child quantity in stock is less than the child quantity in the parent (in Terry's example, he's got 10 lbs in stock and since there's 25 lbs in the parent he should order the parent) BUT the parent is out of stock and it's already on order, then should the parent be flagged as orderable or not? You start getting into some grey areas but I think they can all be tweaked as they arise.

The idea behind the actual ordering process of the parents would be to create POs using a filter, where you can filter for the word "ORDER". Unfortunately there's not a lot of available fields in RMS, other than bin location, notes, and subdescriptions 1 thru 3, that can be used in the PO filter. The update queries use the NOTES field in the item table. If you are using this field already then do not use the update queries as you'll lose the information you are currently tracking in the notes field.

Disclaimer time: Queries were done on and for STORE OPERATIONS. Before using these queries on your live database, create a copy of your db for testing purposes. Only after you are comfortable with the results should you apply these to your live production database. Also, keep in mind I still like to think of myself as a newbie with sql because there is so much to learn.....

Or as Terrible Tom proclaimed so many times on this newsgroup....."proceed at your own risk."

First create your parents view. create view vparents as select i.id, i.itemlookupcode, i.description, i.quantity, vo.onorder, i.donotorder from item i left join viewonorder vo on i.id = vo.itemid where i.id in (select parentitem from item where parentitem 0)

Second create your children view CREATE VIEW VCHILDREN AS select i.itemlookupcode, i.description, i.quantity, i.parentquantity, i.parentitem, VPARENTS.QUANTITY as PARENT_INSTOCK, VPARENTS.ONORDER AS PARENT_ONORDER from item i INNER JOIN VPARENTS ON i.PARENTITEM = VPARENTS.ID where i.parentitem 0

Run select statements against the views to see their "contents" SELECT * FROM VPARENTS SELECT * FROM VCHILDREN

Here is a sample UPDATE query that will populate the NOTES field of PARENT items in the item table withthe word "ORDER" based on a) child quantity in stock is less than child quantity in the parent, and b) parent quantity in stock is less than or equal to zero (to account for those occassional negatives), and c) the parent has never been ordered or has no PO backorders (the view viewonorder reads the purchaseorderentry table) UPDATE ITEM SET NOTES = 'ORDER' FROM ITEM INNER JOIN VCHILDREN ON ITEM.ID = VCHILDREN.PARENTITEM WHERE VCHILDREN.QUANTITY < VCHILDREN.PARENTQUANTITY AND VCHILDREN.PARENT_INSTOCK 0)

-- RUN THIS QUERY ONLY AFTER PLACING PURCHASEORDERS FOR PARENT ITEMS UPDATE ITEM SET NOTES = '' FROM ITEM INNER JOIN VCHILDREN ON ITEM.ID = VCHILDREN.PARENTITEM WHERE VCHILDREN.QUANTITY > VCHILDREN.PARENTQUANTITY OR VCHILDREN.PARENT_INSTOCK > 0 OR VCHILDREN.PARENT_ONORDER > 0

-- the "notes equals" shows two SINGLE quotes, not one double quote

If anyone sees where the queries can be improved, or if I missed something that's key, please post - thanks

" snipped-for-privacy@netzero.com" wrote:

convoluted does this work for parent items with no child? what about populating all note fields with order and using a query to insert or erase note field of parent item using min max order levels. generate po using min max order levels and note equal order. is my logic right? jason

Would only work for parents WITH children (actually, I think a parent without any children is not defined as a parent anymore), as it's the child that actually defines the parent.

If the parent has no min/max and your PO generation selection is "based on reorder information" instead of "for all items in selected group" then the PO will only be created for items that have a min/max defined - it should ignore those that don't.

" snipped-for-privacy@netzero.com" wrote:

I had the good luck to meet up with the RMS marketing manager at the VARTECH show in Orlando. When I posed this question to her see promised a response. I will post there answer to this issue.