Is it possible to remove no longer used Item Master Data instead of just inactivate them?

[leon_laikan]

Hello, everybody

We intend using an Inventory System which generates an enormous number of Item Codes (actually 1 Item Code for each item ordered by a client on his Sales Order).

This is a very strange method of working, but it fits our business well.

Unfortunately, this system generates an enormous number of codes, which grows over the years.

This will eventually slow down SAP.

Our system can only work if we can periodically delete (i.e. remove completely) all the older codes which we are no longer using.

----

We intend performing Data Archiving every few years, and then immediately do a Data Master Cleanup of the Item Masters.

------

The problem is that I am not sure if this can be done!

(a) In the SAP document: How to Prepare for and Perform Master Data Cleanup, it seems (???) this can be done (???)

(b) Some experts on the net say this can be done.

(b) However, some experts on the net say that it is impossible to delete Item Masters once transactions have been passed using the Item Masters.

But, suppose we have archived (using Data Archive Wizard), all our old data (using some of our item codes). This means that there are no longer any old transactions using old Item Masters in our live database.

Then, I suppose we could use Data Archive Wizard to actually remove (and not just inactivate) the unused item masters. But some experts say that once transactions have been passed, it is impossible to remove the item masters. We can only inactivate them.

(c) I have also watched a video on YouTube demonstrating how to remove thousands of master files. But these master files were created by error, and no transactions were passed on them. I have not yet seen a video removing master files which once had transactions.

---------

My Question:

If we archive our database first, (using the Archive Wizard), is it then possible to use Master Data Cleanup Wizard to actually remove (and not simply inactivate) the old item masters that we no longer use?

If anyone has successfully removed old item masters, I'll be glad to hear from him.

If not possible (using Master Data Cleanup Wizard), is there any other way? SDK?

Thanks

Leon

[msundararaja_perumal · Accepted solution]

Hello,

As of now there is no other option apart from make them inactive or use data archive.

Thanks.

[leon_laikan · Accepted solution]

Hello, Sundararaja

Thanks for reply.

>> As of now there is no other option apart ... use data archive.

This is what I have never understood, and it's still not clear in my mind.

I cannot test on my live database as I risk damaging it.

I do understand that it's not possible to remove Item Masters in the live database (because there are transactions).

But what if you have archived your database and there are no longer any transactions (using the old item masters)? The Archive Wizard is supposed to remove the old transactions, isn't it?

Then the old unused item masters will remain in our live database, but all the related old transactions will have been removed by the Data Archive Wizard. Am I right?

In such a situation, is it possible now to REMOVE the old unused item masters? Either manually or by using the Master Cleanup Wizard, or by SDK, or any other means?

This is my question, in essence:

Thanks

Leon

[msundararaja_perumal · Accepted solution]

leon.laikan ,

Archive will remove the old transactions, so it will reduce the DB size, but it won't allow you to remove the item master.

In other words, the old transactions removed will be cut off similar to that of opening balances.

Hope you get the clear picture now.

[leon_laikan · Accepted solution]

Sandararaja,

You mention one point that I never thought before... and which may shed some light on my confused mind.

"... similar to that of opening balances."

Yes, I understand that the Archiving Wizard deletes a lot of transactions and replaces them with an OPENING BALANCE.

If there is a (non-zero) opening balance in my item masters, I understand your point ... that it is impossible to remove the item master data.

----

But in my situation, I use item masters only once: once the client has gone, the item code is no longer used.

As I said, this is a strange system. I use item masters only once.

The items will show zero balances when the client leaves.

So, a few years later, when I archive the data, ALL old data will be removed. Only the item masters will remain. But opening balance will be zero.

What I am not sure is this:

#1:WILL THE ARCHIVE WIZARD CREATE OPENING BALANCES OF ZERO FOR ALL ITEM MASTERS?,

OR

#2: WILL THE ARCHIVE WIZARD NOT CREATE OPENING BALANCES FOR THESE ZERO ITEMS?

If #1 is true, I can understand why it is IMPOSSIBLE to delete the old item masters.( though I don't think SAP would choose to create opening balances of zero...)

But if #2 is true, surely it must be possible do delete the old item masters? Am I right?

Thanks

Leon

[msundararaja_perumal · Accepted solution]

leon.laikan , System will consider zero balance items as opening balance, therefore you cannot remove them.

[leon_laikan · Accepted solution]

Hi, Sundararaja

>> System will consider zero balance items as opening balance, therefore you cannot remove them.

Ooops! What a shock to me!

If what you say is correct, there is absolutely no way I could delete those old item masters.

Thanks for avoiding me to continue in a blind allay.

I may consider later to lodge a ticket with SAP to correct this "anomaly".

Closing

Leon

[msundararaja_perumal · Accepted solution]

leon.laikan , Its not considered as anomaly, the reason for this behaviour is to get the audit trail, which requires for SOX compliance.

[patrice_vigier · Accepted solution]

It is unfortunate that it is not possible, because when you have 10.000 items in the database that are disable, it is not easy to find the active ones in this mess.

[Johan_H]

Hi Leon,

Why can you not recycle items? In other words, why not use the same item codes, and update the item names, and any other settings necessary, just before you create each order?

Regards,

Johan

[leon_laikan]

Hi Johan,

Long time not met!

You may already know that I have been struggling for years to find a workable solution for my company.

My company is engaged in shipchandling: i.e. it sells everything that the ship wants.

We stock only high-selling goods. Non-store items have to be bought on demand.

Our big problem:

Ships can order practically anything you can imagine! It would be impossible to give fixed item codes to all items!

----

I have already considered your solution, but did not implement it for the foll. reason:

If we are changing the item names all the time, our stock reports will also be changing

all the time, depending on when we print them.

Eg, if we want a stock listing @ 30/006/19, we get different lists depending on what

date we run the report

---

Another method I imagined is to give each item ordered by the client a one-time unique code.

This can work, but it generates an enormous number of item codes! This is why I posted my question on this thread.

The method will only work if I archive my data every 3-4 years, PLUS I then remove the unwanted item masters.

-----

Still another method I imagined is to use high-level codes. Instead of coding each item precisely, I could group similar items under one code, e.g. whisky, tools, computers, etc.

But if a client orders several types of each, we have to create multiple instances of each high-level codes like: whisky_1, whisky_2, whisky_3 etc. And this also creates a large number of codes!

For the moment, this option is my preferred one.

-----

But, coming back to my question:

If I successfully archive my old transactions, can I then use the Master Data Cleaning Wizard to remove the old item masters from the live database? or is this impossible as some people say?

Best Regards,

Leon

[Johan_H]

Hi Leon!

Now I remember we spoke about your challenging scenario before.

About your question, as far as I know, the master data cleaning wizard, can remove master data after all related transactions have been archived first. However, I strongly suggest you check the documentation and/or ask your SAP Partner to confirm.

About the stock listing report, you could consider building your own report, based on item names in documents, instead of master data. Then you could recycle item codes. But that is just off the top of my head. Another solution you could think about, is to setup a database in MS SQL Server Management Studio (not a B1 company database) with a single table, which could hold your item codes, item descriptions, a date and stock for that description and date. You would then add records to this table every day with the (recycled) item codes, the relevant descriptions, and stock at end of business. You could then base your report on this table, and it would not slow B1 down.

Having high-level codes does sound better than setting up a new item each time.

Regards,

Johan

[leon_laikan]

Johan,

1. Ask your SAP partner.

The problem is that my SAP partner says we cannot remove item masters even if I have removed all transactions.

I asked him: Why should SAP develop a cleaning Wizard if it cannot remove item masters from the database?

He did not answer me.

2. It's on top of my head.

Me too!

3. Non-B1 database

I have never thought about this possibility. Every idea is worth exploring!

But I don't quite grasp your idea.

How do you "add records to this table everyday ..." ?

Would you mind elaborating a bit?

Leon

[Johan_H]

Hi Leon,

You said that the problem with recycling item codes, is that you would not be able to have a stock report in the past (for example, what was the stock this day, last year).

If you enter this data in a table every day, you can then later have your stock report for a time in the past. With a query, you can probably even enter data that already exists. So in short:

1. Set up a database in MS SQL Management Studio
2. Set up a table in this database, with fields ItemCode, ItemName (the description it had on that day, taken from the sales order or delivery), OnHandDate (DATETIME), OnHand (NUMERIC(19, 6))
3. Set up a job in MS SQL Management Studio, that runs a query against your company database, every day at end of business, and inserts the data into the table that you setup in step 2
4. Optionally, create a query that gets item descriptions from your documents, grouped by day, and stock for that item for that day. Insert this data into the table, to be able to use it right away.
5. In B1 setup a report / query that gets (part of) its data from the table you setup in step 2

With this setup, you can recycle items, and still get a stock report.

Regards,

Johan

[leon_laikan]

Hi, Johan

Thanks a lot for your latest comment.

Yes, probably it could work for me.

I will have to study all its implications.

Regards,

Leon

[kothandaraman_nagarajan]

Hi,

Check this link to basic information,

http://www.pioneerb1.com/wp-content/uploads/2012/06/How-to-Prepare-for-and-Perform-Master-Data-Clean...

Regards,

Nagraajan

[leon_laikan]

Hi, Nagarajan

Thanks for your reply.

I already have studied this document, and as I mentioned, it seems (???) to indicate that once we archive the data, we can delete the item masters. But I am not sure if this is what it says! Some experts say that once you have input transactions, you cannot remove the item masters, EVEN if you archive the data first.

Regards,

Leon

[inkosi]

If there is any transaction linked to them then there is no posibility to delete

[leon_laikan]

Hello, Macpherson Mchesi

>> If there is any transaction linked to them then there is no possibility to delete

So, if there are no transactions linked to them, we can delete?

So, if I have archived all my transactions (assume it has been successful), then in the live database, there will be no transactions that are linked to the old item masters (since all the old transactions have been removed by the archive wizard) . So, the item masters should be removable by the removal wizard? True? False?

Some people have told me that even if I have removed the old transactions (by data archiving wizard), I can't remove the item masters (by master data cleaning wizard). If this is true, what's the use of the cleaning wizard then?

Really, I don't know what to believe.

How can I check if there are any transactions linked to the item masters?

Thanks

Leon