Configuration in Queries – An example with planned orders
Variant Configuration has poor visibility in ECC. This article explains how to incorporate configuration values into a simple planned order query. The concepts can be used to display configuration information for other objects in more complex situations.
There are two reasons why variant configuration visibility is poor in general.
The main one is that the SAP system doesn’t know how many characteristics you are going to use in your configuration, you can use one or you can use 100. In order to incorporate the values into a standard report (like COOIS) it would require a flexible definition tool to allow you to decide what characteristics you want to see. This gets even more complicated if you use different configuration classes with different characteristics for different materials. I assume this complexity is the reason why SAP doesn’t offer the functionality.
The other reason is that the configuration details are not stored in tables so extracting the data directly from the table – or with a simple query – is not an option.
If you use configuration to plan production in a make-to-order environment you will need to drilldown a couple of times to get the planned order configuration.
This is of no use if you want to list, filter and process orders based on their configuration values.
The Query
You can use a query with some embedded code to solve this visibility limitation. The first step is to decide which characteristic values you want to see in your report.
Let’s assume you have width and length as your configuration characteristics. Create the infoset based on the planned order table (PLAF) and add additional fields for the values that you want to see.
Add the following definition in the DATA section of the query code.
* Config data definition
DATA:
INSTANCE LIKE PLAF-CUOBJ,
CONFIGURATION LIKE CONF_OUT OCCURS 100 WITH HEADER LINE.
These are the variables needed to call the configuration value function. In the “Record Processing” section of the query code add the following function call.
* Clear variables
CLEAR: CONFIGURATION, WIDTH, LENGTH.
REFRESH CONFIGURATION.
* Call the function for the planned order instance
INSTANCE = PLAF-CUOBJ.
CALL FUNCTION ‘VC_I_GET_CONFIGURATION’
EXPORTING
INSTANCE = INSTANCE
TABLES
CONFIGURATION = CONFIGURATION
EXCEPTIONS
INSTANCE_NOT_FOUND = 0.
* Loop the configuration table returned looking
* for the characteristic name and transfer the
* configured value to the additional fieldloop at CONFIGURATION.
case CONFIGURATION-ATNAM.
when ‘WIDTH’.
WIDTH = CONFIGURATION-ATWRT.
when ‘LENGTH’.
LENGTH = CONFIGURATION-ATWRT.
endcase.
endloop.
Finish your query by including the new fields with any regular field you require. The result is a quick and efficient query showing the order configuration in nice tabular form. It can then be used to filter, drill down to a transaction, export to Excel, copy paste the order list into COOIS; among other things.
Enjoy
Joaquin, thanks for sharing. I'm a big fan of queries too. 🙂 The only thing I should point out - adding this code to a query could cause a performance issue very quickly. You might want to put some limitations on how much data the users could pull in this query. In the worst case, of course, the timeout will take care of that, but it's definitely something to keep in mind.
As you correctly pointed out, VC is very difficult to use in the reports. We've actually added custom fields to VBAP table to copy several important characteristics there for reporting purposes. In another system where the reports need to drill down into VC for each item we clearly see much worse performance.
I would also be curious how other SCN members deal with these VC challenges.
P.S. Not sure if moderators would approve of the links to external sites in a blog, but you could just add those to your profile.
Thanks again!
Dear Jelena,
Thanks for your comment. I agree that the function call has a performance issue and moving the configuration values to a table (VBAP in your case) will certainly speed things up. Limiting the query data volume with the selection should be a good trade-off specially if you want an ad-hoc report of fields that you have not previously moved out to the table. I think this particular FM is very lean but that is always relative.
APO uses profiles to show configuration values in standard reports and VC visibility in general is better there. However, I don't see SAP retrofitting that approach into ECC any time soon.
One point that I want to make is that queries can be very efficient even with code. I know that some companies (I'm sure is not your case) see queries as inherently inefficient and even block their use. Queries done wrong, with improper joins or bad coding, can be very inefficient indeed but if you know what you are doing they can become an indispensable tool.
P.S. I took out the part with the link. I did it because I wanted to share other ideas in the PP area (I usually post in the APO one). In particularly I wanted to share a post on the difference between ECC and APO calculation that I though would be interesting for the PP crowd as well. But the moderator told me not to duplicate post in different areas.
Anyway, my profile list all my SCN content and also have the links to my blog. The invitation to check out the other ideas stands.
Joaquin, a somewhat late response but as a big fan of InfoSets queries , i admire the way people like transform this magnificent workspace into fully functional programs .
Could you please help me with a this issue i'm experiencing ( and it involves the End-Of-Selection event ) with changing a query's header (column) dynamically at runtime?
What i'm basically trying to do is to assign a dynamic name to an additional field of the query before the list is printed ( e.g field S464-spmon that holds a specific period that i would like to print out eventually ...)
Any help will be greatly appreciated
Thanks!
Moshe
Hi Moshe,
Sorry for the delay, I has been away from the community. If I got a notification of your comment, I didn't see it.
Queries are great but they do have some limitations. One may be doing what you want to do.
The change should be simple enough. The header definition is available during the End-Of-Selection code block in table %LDESC[]. If you debug at that point you will see that the table have the header text of all fields. If you change one title value at that point it will be reflected in the query report.
The problem is that %LDESC[] is generated during the query compilation. As a result, if you try to change its content in the query code you will get an error saying that %LDESC[] has not been declared yet. If you DO declare it in your code there will be a double declaration error during the activation of the final query code.
A catch 22 situation.
There may be a way to dynamically access the table without having to declare it in your code using field-symbols, a way to bypass the query checking of the extra code or a way to bypass the double declaration error when the query is activated. I did a quick check but couldn't find any easy way to do it. An experienced ABAPer may know how.
I hope that at least I'm sending you in the right direction.
One easy way around is to made a copy of the query final program into a customized program and modify it directly with the update of %LDESC[] where the End-Of-Selection code block goes.
Regards,
Joaquin