Inventory Control Setup - changing inventory adjustment numbers historically via SQL or 3rd party product

(0) Share

Report

Posted on by Community Member

Hi everyone,

In the inventory control setup UI the numbering schema for inventory adjustment numbers can be changed affecting the numbers for transaction going forward.

I've been given the task of updating all adjustment document numbers for all past transactions, across different modules, to follow the new inventory adjustment document number schema. For example adjustment ADJ-000069596 becomes something like ADJ-INV-000069596.

Where to begin? I know of a few tables where I've seen the adjustment numbers and have considerable experience as a DBA and could attempt a SQL script to do this. So here's what I'm looking for:

-- what tables are storing inventory adjustment document numbers in inventory, manufacturing (if applicable), Purchasing, Sales, and GL modules.

-- has anyone ever done this? , know of available scripts, 3rd party products (probably safer approach)

-- gotchas?? referential integrity problems from changing the doc number across different tables.

Thanks for any help.

-- Jim

*This post is locked for comments

I have the same question (0)

All responses (11)

Answers (0)

Suggested answer

Tristan Clores 2,812 on at

Like (0)

Report

Hi Jim,

I recommend reading through David Musgraves' blog below:

blogs.msdn.com/.../spsearchonalldb-sql-stored-procedure-to-search-an-entire-database.aspx

You can use this to determine what tables are affected by a particular transaction. From there you can determine what tables/table columns you need to modify the values to.

Was this reply helpful? Yes No
Suggested answer

soma 24,410 on at

Like (0)

Report

I will not recommend for SQL update. If your update any fields or table with wrong values, then it will affect GP. Better you can use your new Adjustment entry number for features transactions only. Don't update to historical/earlier transactions.

Was this reply helpful? Yes No
Suggested answer

Mahmoud Saadi 32,738 on at

Like (0)

Report
I would never recommend such a solution which includes updating transaction numbers on the SQL level for adjustment transactions, that requires a high level of understanding the inventory tables behaviors and meta data. I have been through such a task before for almost 1000+ transactions, it took almost 3 hours to complete the tasks with zero errors. Here's my suggestions;

List of posted Adjustments numbers, categorized by adjustment type (In, Out). The details below are to be considered;

Using the usual adjustment number criteria, reverse the transaction recorded above. Adjustment in (should be withdrawn) while adjust out (should be increased).

Using the new coding criteria, re-enter the above transactions considering the original master table.

Furthermore, I would like to shed a light that primary inventory tables for an inventory adjustment is illustration below;

IV30300 | Transactions History

IV10200 | Purchase Receipt Layers Word

IV10201 | Purchase Receipt Layers Details

SEE30303 | Historical Inventory Trial Balance

IV30200

IV30100

Related useful articles on the inventory transaction flow could be retrieved on the link below;

Inventory Transactions Flow

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks for your reply but I think it may be simpler than what you suggest (which is a very sound approach). I'm not updating the number itself just the prefix e.g. ADJ- becomes ADJINV-. The number would be retained. I wouldn't dream of updating the number. Thanks for the article on Inventory Transactions Flow.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

That was my thinking exactly and what I suggested. I'll pass on your comments. Thanks for your reply.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

A solution I like, simple and affective. I'm concerned about any referential integrity problems that may occur. I'm concerned about it being much more than a simple update to a document number prefix.

Was this reply helpful? Yes No
Mahmoud Saadi 32,738 on at

Like (0)

Report

So you will go on with the update approach ?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Yes, very carefully of course, but yes. I'll be testing the approach extensively. Like I said it's not updating the number portion of the document number, just the prefix. I'm going to wait a while to see if more suggestions to come in before I go ahead with this. Your approach gave me an idea for another project I have coming up so thanks again for your reply Mahmoud.

Was this reply helpful? Yes No
Suggested answer

soma 24,410 on at

Like (0)

Report

First back up the live database and restore it in test company then update the document number prefix and test this on same server once everything is working without any issues, then do the same in live company.

Hope this helps!!!

Was this reply helpful? Yes No
soma 24,410 on at

Like (0)

Report

All the best.

Was this reply helpful? Yes No