Foreword:
This blog post details my recent experience relating to when an end user ran a transaction called QA08: Mass Change of QM Inspection Setup Data in Materials, for every material in our plant and deactivated three inspection types 03, 06 & 89. I will take you through the steps I performed to understand how large the problem was, how I fixed the problem and how I dealt with the fallout.
Introduction:
It was coming to the end of my working day on a Wednesday. The week had been going well - I had dealt with a handful of incidents that week regarding goods receipts not creating certificate records. During my investigations of these incidents I had noted some changes to the inspection types on the QM tab by a user in QA08 - We will call him John Smith (SAP User JSMITH). I didn't think much of it as the problem appeared to be un-related. I then get an email from a colleague with the following phrase
" We might have a bit of a problem on our hands…. Shall we have a call to see how we can tackle it?"
Below this email are a series of exchanges between a group of people who have also noticed that user JSMITH has removed a lot of inspection types. When I looked at the change records for that user and that transaction I see this:
Count of Changes in CDHDR Using QA08
It would appear that over the course of two days user JSMITH has used QA08 incorrectly and caused some serious damage. I started thinking about this issue and what can be done, referring to the Cynefin framework I deemed us to be in chaos. This meant the approach to take was...
Act -> Sense -> Respond and fix the issue with a novel practice.
In other words, fix the problem first, This does not happen often so I will need to invent a fix as I go. Then investigate the damage caused and get a sense of the fallout, lastly respond to the business and the user.
Fixing the Data:
By this point I had an idea of how many records he changed but I had no idea what he actually did. I looked in table CDHDR at a handful of material numbers, then I copied the corresponding document numbers into CDPOS to see what had actually been changed.
CDPOS Material Investigation
As you can see from the image above, Inspection types 03, 06 & 89 had an old value of "X" and a new Value of " " for each material number. I performed this check a couple of more times and by then I was certain. The user had not entered a material number when running the transaction. The problem I now faced is that I cannot just simply run QA08 and turn those inspection types on as they may have been turned off for a legitimate reason. The only approach I could take was to download and digest the change records, then try to reverse the changes that he had made.
As I couldn't use SAP Username or the Date as an entry on CDPOS I thought the best approach was to use the document numbers from CDHDR as a range. I ran CDHDR similar to the photo above without a maximum number of hits limit. This took a while to run but eventually I got an ALV grid of all the document numbers across both days. I then sorted the list based on the document numbers and extracted some key bits of information.
Date: 25/08/2021
- Amount of Records: 580,898
- Start Time: 17:53:17
- Minimum Document Number = 125777374
- End Time: 18:28:50
- Maximum Document Number =126359057
Date: 26/08/2021
- Amount of Records: 1,482,394
- Start Time: 15:30:39
- Minimum Document Number = 126398794
- End Time: 17:06:03
- Maximum Document Number =127882976
As these changes were performed over two days I do have a gap in the document numbers. However, this didn't affect me as I could filter CDPOS to look at the specific table and fields etc.
I knew that I needed to extract the data into an excel format in order to use it as an input to an LSMW as a text file. I decided that my best approach would be to do the following:
- Run SE16 looking at CDPOS in the background with as much detail on the selection screen as possible.
- Extract the spool file to a text file
- Add the text files into Excel and perform some manipulation
I started with the first days changes. I decided to try and get them all into one large spool file, failing this I would create smaller batches. I ran SE16 with the following selection screen.
Selection Screen for CDPOS
ENSURE YOU BLANK OUT THE "WIDTH OF OUTPUT LIST" & "MAXIMUM NUMBER OF HITS" FIELDS, I LEARNED THIS THE HARD WAY.
The first batch finished running and I had a spool file containing 9,682 pages. I then went to transaction SP02, selected the spool and exported it as text. This process was the lengthiest. This spool file took nearly an hour and a half to generate the text document. However it did work. I imported the text file into Excel, did some formatting and I then had a list of the changes that occurred on the 25th.
Due to the length of time that the text file took to generate I thought it would be smart to break the changes from the 26th down into smaller chunks, so that I would create similar sized spools to the changes on the 25th. I took the difference in records and divided this into thirds to create my batches.
Date: 26/08/2021
- Amount of Records: 1,482,394
- Minimum Document Number = 126398794
- Maximum Document Number =127882976
Difference between Minimum Document Number and Maximum = 1,484,182
1,484,182 / 3 = 494,727
Batch 1 = 126,398,794 to 126,893,521
Batch 2 = 126,893,522 to 127,388,249
Batch 3 = 127,388,250 to 127,882,976
I repeated the steps above and ran SE16 in the background looking at CDPOS with the same selection screen but with a different range of document numbers.This process gave me three separate spool files with roughly 8,235 pages each, which was less then the initial file so I knew that I could extract these. I followed the same process as above and turned each one into a text file.
CDPOS Text Files Saved on my Desktop
This process was very laborious and took a long time however once done, I created an excel sheet with four tabs - one for each batch. The next step was to create an LSMW.
CDPOS Excel Sheet Final
I won't take you through the exact intricacies of how I wrote my LSMW as there are plenty of resources here for that. To cut to the chase, I created a recording which went to transaction QA08 and entered the following pieces of information:
- Plant
- Material Number
- Inspection Type
- Checkbox to Not Display Material List
- Checkbox to Activate Despite Inspection Stock
- Checkbox to Create Change Records
It was not my prettiest LSMW to date however it got the job done. I performed various checks in the DEV system and then promoted it to the production system. My initial intension was to group each material together and then perform the updates. This was to avoid having material masters half finished in the production system. The LSMW would run for 1 material three times and now that material would be correct. However this proved to be more hassle then it was worth as it was very time consuming to try and collate the 4 different tabs in my Excel sheet. So I scrapped this idea.
I then needed to run my LSMW in the production system. I decided against just having one huge batch as I was working from home at the time and if my VPN dropped out I may be in trouble. So I put all of my records into batches of 58,000.
LSMW Batch Input
I ran the first 100 records or so in the foreground just to monitor what happened and build some confidence. I then set all the batches running at the same time. Once they had all finish I had a handful of errors from each batch to look at - the error rate was approx. 0.4% , Which I was very happy with.
Once I started looking into the errors they were all caused by the relevant inspection type being made active already. I did't look through them all as error management in LSMW is a pain. but I looked at enough to give myself confidence that was the case.
The final result was that now when I look at table QMAT I have 2,072,465 entries with the correct active inspection types.
QMAT Inspection Type Count
At this juncture I considered the actual data changes solved, every change that user JSMITH had made on the 25th & 26th had been reversed. However, these changes occurred about two weeks before this point in time. So I now need to investigate the problems this had caused.
Understanding and Fixing the Fallout:
So I now needed to try and get an understanding of the fallout. What has happened in the last two weeks due to this data change, or more specifically, as I are dealing with inspection types - what hasn't happened.
In order too look at the problems caused by this you need to have a basic understanding of how inspection types work. I will try and create a concise view below.
Inspection types can be considered as trap. An opportunity to catch a material and inspect it once a certain set of circumstances have occurred. These circumstances are grouped by something called an inspection lot origin. see some examples below:
- Inspection Lot Origin 01 = Goods Receipt
- This may trigger Inspection Type 01 (Goods receipt inspection for a purchase order)
- Inspection Lot Origin 03 = Production
- This may trigger inspection type 03 (In-process inspection for a production order)
- Inspection Lot Origin 04 = Goods Receipt from Production
- This may trigger inspection type 04 (
Goods receipt inspection from production)
- This may trigger inspection type 04 (
The reason behind this is that the origin may be a goods receipt, but it could be a goods receipt from a Purchase Order or Materials from External Processing etc. The source transaction will always be MIGO but the circumstances of the goods receipt can change.
You can find more information about this here:
In our case inspection type 03, 06 and 89 were deactivated. I know that an 03 inspection type is triggered by the release of a production order however, I will fix these values further down. For now I will look at repairing the damage relating to inspection type 06 and 89.
When ever a goods movement happens in SAP it has a movement type, within configuration you can set the inspection lot origin. See below an image from OMJJ. Looking at movement type 101.
OMJJ Configuration - Movement Type 101
So to summarise. A Goods Movement can occur which links to an Inspection Lot Origin, This origin has a relationship to one or more Inspection Types which in turn could create an Inspection Lot
Goods Movement -> Inspection Lot Origin -> Inspection Type -> Inspection Lot
As I knew the Inspection Types that had been made inactive. I could reverse engineer the process above and find which inspection type belongs to which inspection lot origin. I could then look at what movement types would usually trigger these inspection origins. Then I can look at all movements over the two week period.
After Looking in our system I could confirm the following about the deactivated inspection types:
Inspection Type 06 has an Inspection Lot Origin of 06
Inspection Type 89 has an Inspection Lot Origin of 89
I looked at table T156S, filtering on the above Inspection lot Origins.
Table T156S filtering on origin 03, 06, 89
No movement types are configured to trigger Origin 89. However movement types 451 and 651 are linked to inspection lot origin 06. I then looked in transaction MB51 at all 651 & 451 movements between our date range and this gave me a list of Materials which should have had an inspection lot created. I gave these two examples to our Logistics team and they dealt with them.
651/451 Material Documents List
I then looked at the second problem. If inspection type 03 has been added to the material master. When a Production Order is released it will create an inspection lot to handle the in-process inspection. As inspection type 03 was deactivated for a couple of weeks there was Production Orders floating around with no inspection lots assigned. I believed that there were two scenarios and have detailed my steps below:
- Production Orders that have been released during our date range but not TECO'D
- I ran transaction COOIS looking at Production Orders with an actual release date within our date range
- I excluded anything with a status of TECO (Technically Complete), ILAS (Inspection Lot Assigned) or CLSD (Closed)
- I exported these values to Excel and then used the Material Numbers as an input into table QMAT to see if the materials have inspection type 03 assigned and active
- I removed any Production Orders from the list which didn't have inspection type 03 active
- With this final list of parts (Approx 950) I then built a simple LSMW with a colleague to go into the production orders via CO02 and click the following: Functions-> Inspection Lot -> Create -> Save
- Production Orders that have been released during our date range and TECO'D
- I ran transaction COOIS looking at Production Orders with an actual release date of within our date range and an actual finish date before or on the day I fixed the inspection types.
- I excluded anything with a status of ILAS (Inspection Lot Assigned) or CLSD (Closed)
- I exported these values to Excel and then used the Material Numbers as in input into table QMAT to see if the materials have inspection type 03 assigned and active
- I removed any Production Orders from the list which didn't have inspection type 03 active
- This final list of Production Orders (Approx 75) was given back to the business to manage manually
After all of this work the business continued to run optimally with minimal disruption. We are now taking steps to prevent this from happening in the future. Our saving grace was that QA08 created change documents - as without these we would have been looking at restoring a backup.
I hope you enjoyed reading about this experience and if you have any questions or comments feel free to mention them below.
- SAP Managed Tags:
