HQ created duplicate suppliers at stores

Posted by Terrible Tom on January 23, 2008, 5:45 pm

Please log in for more thread options

The question of the day is this: How did this happen?

I've spent hours, days even, trying to figure out what happened.

All of a sudden, several dozen supplier records are duplicated at the store
level. HQ database seems fine. So I poke around in Admin to see shat is
going on...

On the Supplier table, the new supplier records (the ones with the higher
IDs) have the correct HQID associated with them. The original supplier
records now have an HQID of 0.

On the SupplierList table, any new items created since the duplication error
occurred are associated with the new SupplierID, while older items are still
associated with the original SupplierID.

My solution: Open both HQ Admin and Store Ops Admin.
In HQ Admin, SELECT * from Supplier, sort by SupplierName

In SO Admin, SELECT * from Supplier, sort by SupplierName

Look down the list until you find a record with HQID = 0.

Is there a duplicate?
If not, then check HQ Supplier table for correct HQID.
No record at HQ? Check SupplierList for associated items.
If no associated items, delete record.
If yes to associated items, note for later... [sad face - another less than
fun project]

If there is a duplicate, then:
UPDATE SupplierList SET SupplierID = OLD WHERE SupplierID = NEW
DELETE from Supplier WHERE ID = NEW
UPDATE Supplier SET HQID= CORRECT WHERE ID = OLD

SELECT * from SupplierList WHERE SupplierID = ID of record with HQID = 0

This is crazy. As far as I can tell, any supplier record that has been
updated in HQ and had a subsequent WS206 issued to update the info at the
stores has resulted in the existing Store Ops record being disassociated from
HQ and a new supplier record being created.

Once I have corrected this at each store (thank goodness I only have four
stores) I will reindex all store databases and the HQ database. I will then
issue a WS206 for one supplier and check the store databases for duplicates.
If a duplicate record has been created, I don't know what I will do.

Aside from an explanation of the obvious question of 'why?' I would also be
very interested to hear from anyone that has experienced anything similar.

Hoping your day has been less interesting than mine,
Tom
--
Stop fishing for e-mail

Posted by jocelynp on January 23, 2008, 11:44 pm

Please log in for more thread options

Hi Tom - Sorry to hit on your thread, I hope people do not disregard it as
having been 'dealt with'.

I have not yet seen the Suppliers go off like this, but I am having similar
issues with Cats and Depts. My issues are directly relating to the item
dynamic table not updating inv to HQ. I can see about 27 of our 112 stores
inventory at the HQ level.

I have tried everything but going after removing and updating the Cat ID to
= the HQID. I have run a 101 (I know) and chased it with the auto created 401
+ 501 and still nothing (also, all Depts/Cats were updated from HQ
initially). I have back dated ws 401's to start with info by month,
processing one at a time. However, even with closed connections to all stores
but the one I was working on, the connection still timed out on me. The ws
401's just got to massive to run over the connections. I brought the store db
over to HQ and created out of HQ Admin placing the db on the same sql server
(hoping to improve connection timeouts), and still nothing.

I am thinking I will remove the duped Cats and replace the Cat ID with the
HQID and see where that gets me. The main issue I am having with this whole
process is that there are so many stores that will need updating, each have
codes and dups that are different, so I can not issue scripts in a blanket ws
51.

Let me know how your testing/implementation goes.
--
Jocelyn

"Terrible Tom" wrote:

> The question of the day is this: How did this happen?

> I've spent hours, days even, trying to figure out what happened.

> All of a sudden, several dozen supplier records are duplicated at the store

> level. HQ database seems fine. So I poke around in Admin to see shat is

> going on...

> On the Supplier table, the new supplier records (the ones with the higher

> IDs) have the correct HQID associated with them. The original supplier

> records now have an HQID of 0.

> On the SupplierList table, any new items created since the duplication error

> occurred are associated with the new SupplierID, while older items are still

> associated with the original SupplierID.

> My solution: Open both HQ Admin and Store Ops Admin.

> In HQ Admin, SELECT * from Supplier, sort by SupplierName

> In SO Admin, SELECT * from Supplier, sort by SupplierName

> Look down the list until you find a record with HQID = 0.

> Is there a duplicate?

> If not, then check HQ Supplier table for correct HQID.

> No record at HQ? Check SupplierList for associated items.

> If no associated items, delete record.

> If yes to associated items, note for later... [sad face - another less than

> fun project]

> If there is a duplicate, then:

> UPDATE SupplierList SET SupplierID = OLD WHERE SupplierID = NEW

> DELETE from Supplier WHERE ID = NEW

> UPDATE Supplier SET HQID= CORRECT WHERE ID = OLD

> SELECT * from SupplierList WHERE SupplierID = ID of record with HQID = 0

> This is crazy. As far as I can tell, any supplier record that has been

> updated in HQ and had a subsequent WS206 issued to update the info at the

> stores has resulted in the existing Store Ops record being disassociated from

> HQ and a new supplier record being created.

> Once I have corrected this at each store (thank goodness I only have four

> stores) I will reindex all store databases and the HQ database. I will then

> issue a WS206 for one supplier and check the store databases for duplicates.

> If a duplicate record has been created, I don't know what I will do.

> Aside from an explanation of the obvious question of 'why?' I would also be

> very interested to hear from anyone that has experienced anything similar.

> Hoping your day has been less interesting than mine,

> Tom

> --

> Stop fishing for e-mail

Posted by Terrible Tom on January 25, 2008, 1:31 pm

Please log in for more thread options

My 'solution' seems to have worked. It didn't take as long as I expected,
because during the work on the second store I started saving a separate query
for each supplier. In the end, I had 17 queries for duplicated suppliers and
another query that fixed the HQ:SO associations for about a dozen others that
simply had HQID = 0 for some reason. I was done in less than four hours.

112 stores! I almost hate to ask, but how do you feel about RMS? I have a
measly four stores and HQ is a disaster for me. Knowing what I know now, I'd
prefer seppuku to a 12-store HQ scenario. The prospect of 112 stores seems
almost laughable (no offense intended).

If RMS Store Ops were multi-store capable right out of the box, it'd be
awesome. In a perfect world, you'd run it as a server-client application
with thin client registers that ran nothing but RMS:POS and RMS:Admin. The
server would do ALL of the work, and the multi-store inventory would be
handled within one database. Store Ops works great. If you had just one
store, the product would be almost perfect. Statements could use some work,
but for the most part all of RMS' problems are HQ-related.

Tom
--
Stop fishing for e-mail

"jocelynp" wrote:

> Hi Tom - Sorry to hit on your thread, I hope people do not disregard it as

> having been 'dealt with'.

> I have not yet seen the Suppliers go off like this, but I am having similar

> issues with Cats and Depts. My issues are directly relating to the item

> dynamic table not updating inv to HQ. I can see about 27 of our 112 stores

> inventory at the HQ level.

> I have tried everything but going after removing and updating the Cat ID to

> = the HQID. I have run a 101 (I know) and chased it with the auto created 401

> + 501 and still nothing (also, all Depts/Cats were updated from HQ

> initially). I have back dated ws 401's to start with info by month,

> processing one at a time. However, even with closed connections to all stores

> but the one I was working on, the connection still timed out on me. The ws

> 401's just got to massive to run over the connections. I brought the store db

> over to HQ and created out of HQ Admin placing the db on the same sql server

> (hoping to improve connection timeouts), and still nothing.

> I am thinking I will remove the duped Cats and replace the Cat ID with the

> HQID and see where that gets me. The main issue I am having with this whole

> process is that there are so many stores that will need updating, each have

> codes and dups that are different, so I can not issue scripts in a blanket ws

> 51.

> Let me know how your testing/implementation goes.

> --

> Jocelyn

> "Terrible Tom" wrote:

> > The question of the day is this: How did this happen?

> >

> > I've spent hours, days even, trying to figure out what happened.

> >

> > All of a sudden, several dozen supplier records are duplicated at the store

> > level. HQ database seems fine. So I poke around in Admin to see shat is

> > going on...

> >

> > On the Supplier table, the new supplier records (the ones with the higher

> > IDs) have the correct HQID associated with them. The original supplier

> > records now have an HQID of 0.

> >

> > On the SupplierList table, any new items created since the duplication error

> > occurred are associated with the new SupplierID, while older items are still

> > associated with the original SupplierID.

> >

> > My solution: Open both HQ Admin and Store Ops Admin.

> > In HQ Admin, SELECT * from Supplier, sort by SupplierName

> >

> > In SO Admin, SELECT * from Supplier, sort by SupplierName

> >

> > Look down the list until you find a record with HQID = 0.

> >

> > Is there a duplicate?

> > If not, then check HQ Supplier table for correct HQID.

> > No record at HQ? Check SupplierList for associated items.

> > If no associated items, delete record.

> > If yes to associated items, note for later... [sad face - another less than

> > fun project]

> >

> > If there is a duplicate, then:

> > UPDATE SupplierList SET SupplierID = OLD WHERE SupplierID = NEW

> > DELETE from Supplier WHERE ID = NEW

> > UPDATE Supplier SET HQID= CORRECT WHERE ID = OLD

> >

> > SELECT * from SupplierList WHERE SupplierID = ID of record with HQID = 0

> >

> > This is crazy. As far as I can tell, any supplier record that has been

> > updated in HQ and had a subsequent WS206 issued to update the info at the

> > stores has resulted in the existing Store Ops record being disassociated

from

> > HQ and a new supplier record being created.

> >

> > Once I have corrected this at each store (thank goodness I only have four

> > stores) I will reindex all store databases and the HQ database. I will then

> > issue a WS206 for one supplier and check the store databases for duplicates.

> > If a duplicate record has been created, I don't know what I will do.

> >

> > Aside from an explanation of the obvious question of 'why?' I would also be

> > very interested to hear from anyone that has experienced anything similar.

> >

> > Hoping your day has been less interesting than mine,

> > Tom

> > --

> > Stop fishing for e-mail

Posted by jocelynp on January 25, 2008, 2:28 pm

Please log in for more thread options

I love thin clients.

Actually most of my issues are not on the HQ side as much as you would
expect. Well excluding the cost of HQ licenses, etc. The main HQ issues we
have had are that data was not centralized - that is when I came on board. We
are slowly creeping out centralizing, or as it is also known 'taking away all
free rights' in the user's eyes.

My most major issue is that the store systems are not the most secured. I
have each store running different config settings, and that is the hardest
with support. Then there is always the communication issues when they get an
outside guy in there messing with the machines (Norton) - and before you know
it they have one from 3 lane to 1. Argh!

I would love to pool my scripts I am planning on using for the Cats, but I
can not b/c this all dates back to the stores not having centralized data
(add own Dept/Cat/Items) and then converting. Not all stores have the same
Store CatID. This is just going to be one of those bear down and get it done
things. - Well all after I validate what I am thinking about doing. Haven’t
even gotten there b/c I've been eyes deep an AR issue.
--
Jocelyn

"Terrible Tom" wrote:

> My 'solution' seems to have worked. It didn't take as long as I expected,

> because during the work on the second store I started saving a separate query

> for each supplier. In the end, I had 17 queries for duplicated suppliers and

> another query that fixed the HQ:SO associations for about a dozen others that

> simply had HQID = 0 for some reason. I was done in less than four hours.

> 112 stores! I almost hate to ask, but how do you feel about RMS? I have a

> measly four stores and HQ is a disaster for me. Knowing what I know now, I'd

> prefer seppuku to a 12-store HQ scenario. The prospect of 112 stores seems

> almost laughable (no offense intended).

> If RMS Store Ops were multi-store capable right out of the box, it'd be

> awesome. In a perfect world, you'd run it as a server-client application

> with thin client registers that ran nothing but RMS:POS and RMS:Admin. The

> server would do ALL of the work, and the multi-store inventory would be

> handled within one database. Store Ops works great. If you had just one

> store, the product would be almost perfect. Statements could use some work,

> but for the most part all of RMS' problems are HQ-related.

> Tom

> --

> Stop fishing for e-mail

> "jocelynp" wrote:

> > Hi Tom - Sorry to hit on your thread, I hope people do not disregard it as

> > having been 'dealt with'.

> >

> > I have not yet seen the Suppliers go off like this, but I am having similar

> > issues with Cats and Depts. My issues are directly relating to the item

> > dynamic table not updating inv to HQ. I can see about 27 of our 112 stores

> > inventory at the HQ level.

> >

> > I have tried everything but going after removing and updating the Cat ID to

> > = the HQID. I have run a 101 (I know) and chased it with the auto created

401

> > + 501 and still nothing (also, all Depts/Cats were updated from HQ

> > initially). I have back dated ws 401's to start with info by month,

> > processing one at a time. However, even with closed connections to all

stores

> > but the one I was working on, the connection still timed out on me. The ws

> > 401's just got to massive to run over the connections. I brought the store

> > over to HQ and created out of HQ Admin placing the db on the same sql server

> > (hoping to improve connection timeouts), and still nothing.

> >

> > I am thinking I will remove the duped Cats and replace the Cat ID with the

> > HQID and see where that gets me. The main issue I am having with this whole

> > process is that there are so many stores that will need updating, each have

> > codes and dups that are different, so I can not issue scripts in a blanket

> > 51.

> >

> > Let me know how your testing/implementation goes.

> > --

> > Jocelyn

> >

> > "Terrible Tom" wrote:

> >

> > > The question of the day is this: How did this happen?

> > >

> > > I've spent hours, days even, trying to figure out what happened.

> > >

> > > All of a sudden, several dozen supplier records are duplicated at the

store

> > > level. HQ database seems fine. So I poke around in Admin to see shat is

> > > going on...

> > >

> > > On the Supplier table, the new supplier records (the ones with the higher

> > > IDs) have the correct HQID associated with them. The original supplier

> > > records now have an HQID of 0.

> > >

> > > On the SupplierList table, any new items created since the duplication

error

> > > occurred are associated with the new SupplierID, while older items are

still

> > > associated with the original SupplierID.

> > >

> > > My solution: Open both HQ Admin and Store Ops Admin.

> > > In HQ Admin, SELECT * from Supplier, sort by SupplierName

> > >

> > > In SO Admin, SELECT * from Supplier, sort by SupplierName

> > >

> > > Look down the list until you find a record with HQID = 0.

> > >

> > > Is there a duplicate?

> > > If not, then check HQ Supplier table for correct HQID.

> > > No record at HQ? Check SupplierList for associated items.

> > > If no associated items, delete record.

> > > If yes to associated items, note for later... [sad face - another less

than

> > > fun project]

> > >

> > > If there is a duplicate, then:

> > > UPDATE SupplierList SET SupplierID = OLD WHERE SupplierID = NEW

> > > DELETE from Supplier WHERE ID = NEW

> > > UPDATE Supplier SET HQID= CORRECT WHERE ID = OLD

> > >

> > > SELECT * from SupplierList WHERE SupplierID = ID of record with HQID = 0

> > >

> > > This is crazy. As far as I can tell, any supplier record that has been

> > > updated in HQ and had a subsequent WS206 issued to update the info at the

> > > stores has resulted in the existing Store Ops record being disassociated

from

> > > HQ and a new supplier record being created.

> > >

> > > Once I have corrected this at each store (thank goodness I only have four

> > > stores) I will reindex all store databases and the HQ database. I will

then

> > > issue a WS206 for one supplier and check the store databases for

duplicates.

> > > If a duplicate record has been created, I don't know what I will do.

> > >

> > > Aside from an explanation of the obvious question of 'why?' I would also

> > > very interested to hear from anyone that has experienced anything similar.

> > >

> > > Hoping your day has been less interesting than mine,

> > > Tom

> > > --

> > > Stop fishing for e-mail