ISTech Support Forum - Modifying 1200 inventory records

Welcome, Guest. Please Login.

	05/14/24 at 20:18:05 News: \| IS Tech Support \| Evo~ERP \| Evo Wiki \| Forum \|

ISTech Support Forum › Evo-ERP and DBA Classic › Items

‹ Previous topic | Next topic ›

Pages: 1

Modifying 1200 inventory records (Read 932 times)

GasGiant Administrator How can I help? Posts: 1523 Gender:	Modifying 1200 inventory records 04/27/06 at 08:02:50 I need to change MTIC.PROD.LOT and MTIC.PROD.TYPE for 1200 assemblies in my inventory system. After looking at the DE-A and DE-B process, I'm looking for a better way. Any hints would be appreciated.
Back to top	IP Logged

rmh148

Member

Posts: 84

Re: Modifying 1200 inventory records
Reply #1 - 04/27/06 at 08:53:18

I have used Pervasive SQL to fix lot size in MTICMSTR and ROUTING. Here is a sample of the SQL

For MTICMSTR:

UPDATE "MTICMSTR" SET "MTICMSTR"."MTIC_PROD_LOTSZ"=20 WHERE "MTICMSTR"."MTIC_PROD_CODE"= '000314-01/-'#
UPDATE "MTICMSTR" SET "MTICMSTR"."MTIC_PROD_LOTSZ"=250 WHERE "MTICMSTR"."MTIC_PROD_CODE"= '00083101/-'#

for ROUTING:

UPDATE "ROUTING" SET "ROUTING"."MTRO_LOTSIZE"=20 WHERE "ROUTING"."MTRO_CODE"= '000314-01/-'#
UPDATE "ROUTING" SET "ROUTING"."MTRO_LOTSIZE"=250 WHERE "ROUTING"."MTRO_CODE"= '00083101/-'#

IP Logged

shadowcaster Active Member I'm not here because I know what to do. Posts: 678 Gender:	Re: Modifying 1200 inventory records Reply #2 - 05/01/06 at 14:30:50 That looks very handy where would you recomend one would go to learn about SQL and DBA JW
Back to top	IP Logged

GasGiant

Administrator

How can I help?

Posts: 1523
Gender: male

Re: Modifying 1200 inventory records
Reply #3 - 05/02/06 at 03:09:38

Okay, SQL I can do. I guess I didn't realize that Pervasive had such a standard SQL interface. I always think of it as arcane. Setting up the ODBC was interesting, but I can now get a standard SELECT query to work just fine. I'm hoping that an UPDATE that hits multiple fields will work the way I expect, as well. 8)

Here is what I'm going to try once I get all of my approvals signed off...

UPDATE "MTICMSTR"
SET "MTIC_PROD_CLASS" = 'N' , "MTIC_PROD_TYPE" = 'B', "MTIC_PROD_LOT" = 'N'
WHERE "MTIC_PROD_CODE" > 'UEPL'
AND "MTIC_PROD_CODE" < 'UEXX'
AND "MTIC_PROD_TYPE" = 'A'
#

Anyone see any glaring errors? All I'm doing is modifying these ~1200 records to make the assemblies into phantoms, remove lot numbering from them, and standardizing them as class N, which we use for all non-finished goods.

« Last Edit: 05/23/06 at 04:08:52 by GasGiant »

IP Logged

GasGiant Administrator How can I help? Posts: 1523 Gender:	Re: Modifying 1200 inventory records Reply #4 - 05/02/06 at 03:20:36 Quote from shadowcaster on 05/01/06 at 14:30:50: That looks very handy where would you recomend one would go to learn about SQL and DBA There are tons of SQL tutorials on the web (free) and the Pervasive help is where I looked to find the little quirks of 2000i compared with MySQL or P/L SQL, etc.
Back to top	IP Logged

GasGiant

Administrator

How can I help?

Posts: 1523
Gender: male

Re: Modifying 1200 inventory records
Reply #5 - 05/23/06 at 04:16:55

Just to close out this thread, the process worked great, it took 7 seconds to update 1210 records. The only thing I had to change from what I wrote above is that I had to update a corresponding field in the BKICMSTR file to make everything match.

UPDATE "BKICMSTR"
SET "BKIC_PROD_TYPE" = 'B'
WHERE "BKIC_PROD_CODE" > 'UEPL'
AND "BKIC_PROD_CODE" < 'UEXX'
AND "BKIC_PROD_TYPE" = 'A'
#

One nice bonus is that all of the open work orders for the assemblies that I changed into phantoms can still be processed normally. Also, all of the open work orders with those assemblies on their BOM process properly. All I have left to do is to purge the remaining inventory of assemblies during our annual inventory on June 30th. Now my inventory chore is greatly reduced by not having to count 1210 lotted assemblies. That took almost a full man-week last year!

IP Logged

rmh148

Member

Posts: 84

Re: Modifying 1200 inventory records
Reply #6 - 05/23/06 at 13:26:36

I told you it was easy! But, be careful. I have dba set up as stand alone (C drive) and the real on the server. I point it a my TestData to ensure that it works, then point it at the real ODBC data too save many hours. I have used it to clean up lead times, lot sizes and many other mass cleanup areas. I create a guide file with visual basic, vbscript or even in an text editor to stack command then execute. Like, this one I used to fix a bunch of GL COA descriptions:

UPDATE "BKGLCOA" SET "BKGLCOA"."BKGL_ACCTD"= 'COD COLLECTED NOT RCVD' WHERE ("BKGLCOA"."BKGL_ACCT"= '12050')#
UPDATE "BKGLCOA" SET "BKGLCOA"."BKGL_ACCTD"= 'ALLOWANCE FOR BAD DEBTS' WHERE ("BKGLCOA"."BKGL_ACCT"= '12100')#
UPDATE "BKGLCOA" SET "BKGLCOA"."BKGL_ACCTD"= 'INVENTORY- FINISHED GOODS' WHERE ("BKGLCOA"."BKGL_ACCT"= '12500')#
UPDATE "BKGLCOA" SET "BKGLCOA"."BKGL_ACCTD"= 'INVENTORY- WIP' WHERE ("BKGLCOA"."BKGL_ACCT"= '12550')#
UPDATE "BKGLCOA" SET "BKGLCOA"."BKGL_ACCTD"= 'INVENTORY- RAW GOODS' WHERE ("BKGLCOA"."BKGL_ACCT"= '12600')#
UPDATE "BKGLCOA" SET "BKGLCOA"."BKGL_ACCTD"= 'INVENTORY-OBSOLETE' WHERE ("BKGLCOA"."BKGL_ACCT"= '12650')#
UPDATE "BKGLCOA" SET "BKGLCOA"."BKGL_ACCTD"= 'OBSOLESCENCE - ALLOWANCE' WHERE ("BKGLCOA"."BKGL_ACCT"= '12651')#
UPDATE "BKGLCOA" SET "BKGLCOA"."BKGL_ACCTD"= 'PREPAID INSURANCE' WHERE ("BKGLCOA"."BKGL_ACCT"= '13000')#
UPDATE "BKGLCOA" SET "BKGLCOA"."BKGL_ACCTD"= 'PREPAID EXPENSE' WHERE ("BKGLCOA"."BKGL_ACCT"= '13100')#
UPDATE "BKGLCOA" SET "BKGLCOA"."BKGL_ACCTD"= 'DEPOSITS' WHERE ("BKGLCOA"."BKGL_ACCT"= '14000')#

IP Logged

Pages: 1

ISTech Support Forum › Evo-ERP and DBA Classic › Items

‹ Previous topic | Next topic ›