As you may have noticed, this post is a bit tardy. A personal illness, an international trip, and a serious underestimation of the effort required to write this post all conspired to delay its publishing. But without further ado, here is the conclusion to my thoughts on importing SAP sales pricing data.
Now that we have some understanding of SAP’s pricing process from the previous post, it is time to extract data so we can analyze and test to our auditor’s heart’s content. To give you some context, here are some of my objectives when I pull data from SAP. The rest of this post is going to be biased with these objectives, so you will want to keep these in mind and adjust the methodology where your objectives do not agree with mine.
- I want to be able to import pricing data quickly. Less than an hour would be good, less than a half-hour would be ideal.
- I don’t need every pricing condition record in history for every condition type. If I did, I would simply run KONH in the background, wait half of an eternity for that massive table to be scanned, and fend off nasty e-mails from BASIS administrators complaining about “excessive resource usage”.
- Because of the above point, I am OK with filtering my data by condition type, sales organization, etc.
- I always filter transactions, or records containing validity dates, by a period of time.
I like to start by obtaining the condition types used by the business I am analyzing. I can either ask the business for them, which violates my ‘do not ask the business for their data’ principle, or I can pull the data directly from SAP. Based on what we know from part 1, we can identify the condition types used by a sales organization with a query that graphically looks something like this:
The IMPORT SAP command would look like this:
IMPORT SAP PASSWORD 1 TO ConditionsBySalesOrg SAP SOURCE “SAP AGENT” <q version=”7.0″><s>0</s><d>NameOfSapInstance</d><u>MyUserId</u><c>ClientNumber</c><lg>EN</lg><cf>ConditionsBySalesOrg.fil</cf><sf> </sf><jcount> </jcount><jname> </jname><dl>37 </dl><m>0</m><ws>0</ws><jw>0</jw><r>500</r><ar>1</ar><e>500</e><ts><t><n>T683V</n><a>T00001</a><td>Pricing Procedures: Determination in Sales Docs.</td><tt>1</tt><fs><f>VKORG</f><f>VTWEG</f><f>SPART</f><f>KALVG</f><f>KALKS</f><f>KALSM</f></fs><wc><w><f>VKORG</f><o>0</o><l>A001</l><h></h></w></wc></t><t><n>T683S</n><a>T00002</a><td>Pricing Procedure: Data</td><tt>1</tt><fs><f>KALSM</f><f>STUNR</f><f>ZAEHK</f><f>KSCHL</f><f>KAUTO</f><f>KOBLI</f></fs><wc></wc></t></ts><js><jc><pt><pa>T00001</pa><pf>KALSM</pf></pt><ct><ca>T00002</ca><cf>KALSM</cf></ct></jc></js></q>
Below is some sample data to give you an idea of what the output look like:
The resulting table will provide you with a complete list of all condition types used by the specified sales organization. Keep in mind that this list may not exclusively contain only those condition types which are managed by the business. Some types of pricing conditions may transcend business organizations, such as tax percentages. This table is only the starting point to defining which condition types are in scope.
I like to use this table to identify certain subsets of conditions:
- Manual conditions (usually discounts) – Start by identifying condition types with ‘X’ in the T683S_KAUTO field.
- List Prices – These are usually one of the first condition types in each procedure. These may often be flagged as ‘required’ (an ‘X’ in the T683S_KOBLI field).
- Trade discount condition types – I look up the condition type names in SAP using table T685/T685T.
However you go about reviewing this table, and whatever you decide you want to look for, this step requires some manual exploration. Each company designs unique pricing procedures and condition types, so you will need to become familiar with your specific company’s designs.
Once you have an idea of the condition types you are most interested in, the next step is to import the actual pricing records from SAP. The script I have developed, called ‘ValidPriceConds1’ requires some parameters: a list of condition types and a set of dates which define the validity period to pull. Here is the header of the script; note that the first task is to convert the list of condition types (separated by commas) into an SAP filter:
** Imports valid pricing condition records from the ‘A’ pricing tables
** Inputs are:
** v_PriceCondList – A comma-delimited list of pricing conditions.
** v_StartDate – Optional. The start of the validitiy period. Defaults to today.
** v_EndDate – Optional. The end of the validity period. Defaults to today.
** v_SapServer – The name of the SAP Server to connect to
** v_SapClient – The SAP client to connect to
** v_UserId – The user ID to use to connect to SAP
** PASSWORD 1 – The password to use to connect to SAP
** The output is a table containing pricing condition records that were valid during the period.
v_StartDate = TODAY() IF FTYPE(‘v_StartDate’) <> ‘d’
v_EndDate = TODAY() IF FTYPE(‘v_EndDate’) <> ‘d’
COMMENT Convert the delimited price conditions to an SAP filter string
v_DelValueVar = “v_PriceCondList”
v_SapFilterField = “KSCHL”
I want to take a brief diversion to look at the DelimitedValuesToSapFilter script in more depth. The code is below:
** This script takes a list of comma-delimited values and value ranges and converts them to an SAP filter string
** The required inputs are:
** v_DelValueVar – The name of a variable containing a comma-delimited list of values to be converted to an SAP filter string
** The comma-delimited list values can only contain alpha-numeric characters and underscore.
** Use the dash symbol ‘-‘ to specify ranges
** v_SapFilterField – The SAP field to be filtered
** The output is a variable with the name of v_DelValueVar appended with ‘Sap’ which contains the SAP filter statement
** For example, if v_DelValueVar is equal to ‘v_MyList’, v_MyList has a value of ‘01,02,10-20’, and v_SapFilterField has a value of
** ‘FIELD’, the output would be a variable called v_MyListSap with a value of
v_ValueChars = ‘[^,\-<> ]’
%v_DelValueVar%Sap = EXCLUDE(%v_DelValueVar% ‘ ‘)+BLANKS(10000)
%v_DelValueVar%Sap = REGEXREPLACE(ALLTRIM(%v_DelValueVar%Sap) ‘(%v_ValueChars%+)(?:-(%v_ValueChars%+))?,?’ ‘<w><f>%v_SapFilterField%</f><o>0</o><l>$1</l><h>$2</h></w>’)
%v_DelValueVar%Sap = REGEXREPLACE(ALLTRIM(%v_DelValueVar%Sap) ‘(<w><f>%v_SapFilterField%</f><o>)0(</o><l>%v_ValueChars%+</l><h>%v_ValueChars%+</h></w>)’ ‘$1 6$2’)
%v_DelValueVar%Sap = ALLTRIM(EXCLUDE(%v_DelValueVar%Sap ‘ ‘))
DELETE v_ValueChars OK
This script relies heavily on regular expressions to parse and convert the comma-delimited values into an SAP filter string. The first line, v_ValueChars = ‘[^,\-<> ]’, simply defines which characters are allowed in a SAP filter value. In this case, filter values may have any characters except commas, dashes, and angle brackets. The next line removes spaces from the provided filter string and appends 10,000 blank spaces to the end. This is done to allow the filter expression to grow. ACL’s regular expression engine does not increase variable size if it runs out of room – instead it starts overwriting and garbling the existing string. Padding the extra spaces at the end prevents that. The next line extracts the comma-delimited list of values and deposits them into an SAP filter string. Values with a dash (-) get extracted into the Low and High value fields, whereas values without a dash get extracted only into the Low value field. This allows us to define ranges of filter values in our comma-delimited list. Finally, the next line converts the operator of range filters from 0 (=) to 6 (BETWEEN).
I use this little utility script in most of my import commands. While the script is small, the logic is somewhat complicated. Separating the logic into its own script allows me to re-use it extensively without having to worry about introducing errors. I have found utility scripts such as this to be enormous time-savers in my daily development work.
Back to the original import script – now that we have done the necessary preparations, we can import an initial data set that will identify all of the pricing tables that can contain our list of condition types.
IMPORT SAP PASSWORD 1 TO ValidPriceConds_T01 SAP SOURCE “SAP AGENT” <q version=”7.0″><s>0</s><d>%v_SapServer%</d><u>%v_UserId%</u><c>%v_SapClient%</c><lg>EN</lg><cf>ValidPriceConds_T01.fil</cf><sf></sf><jcount></jcount><jname></jname><dl>20</dl><m>0</m><ws>0</ws><jw>0</jw><r>500</r><ar>1</ar><e>500</e><ts><t><n>T685</n><a>T00001</a><td>Conditions: Types</td><tt>1</tt><fs><f>KVEWE</f><f>KAPPL</f><f>KSCHL</f><f>KOZGF</f></fs><wc><w><f>KVEWE</f><o>0</o><l>A</l><h></h></w><w><f>KAPPL</f><o>0</o><l>V</l><h></h></w>%v_PriceCondListSap%</wc></t><t><n>T682I</n><a>T00002</a><td>Conditions: Access Sequences (Generated Form)</td><tt>1</tt><fs><f>KOLNR</f><f>KOTABNR</f><f>KZEXL</f><f>KKOPF</f></fs><wc></wc></t></ts><js><jc><pt><pa>T00001</pa><pf>KVEWE</pf></pt><ct><ca>T00002</ca><cf>KVEWE</cf></ct></jc><jc><pt><pa>T00001</pa><pf>KAPPL</pf></pt><ct><ca>T00002</ca><cf>KAPPL</cf></ct></jc><jc><pt><pa>T00001</pa><pf>KOZGF</pf></pt><ct><ca>T00002</ca><cf>KOZGF</cf></ct></jc></js></q>
A graphical representation of the query is below. Note that there are filters being made on the Application (KAPPL) and Usage (KVEWE) fields. I have found this to be appropriate for my purposes – you will want to perform your own analysis.
Some sample data is below:
Now that we have a list of tables per condition type, let’s summarize and build a list of the needed pricing tables. The idea is that we will iterate and import the data from each pricing table separately. I could use the summarized table for this and use LOCATE RECORD to iterate through the tables, but I prefer working with a text variable. It feels cleaner to me to avoid the overhead of constantly opening and closing the summary table.
COMMENT Get a list tables used by the provided pricing conditions
SUMMARIZE ON T682I_KOTABNR PRESORT TO ValidPriceConds_T02 OPEN
v_PriceCondTables = BLANKS(15000)
v_PriceCondTables = ALLTRIM(ALLTRIM(v_PriceCondTables) + ‘ A’ + ALLTRIM(T682I_KOTABNR))
v_PriceCondTables = ALLTRIM(v_PriceCondTables)
COMMENT Import the pricing condition records from each applicable table
v_PriceCondCounter = 1
v_PriceCondCount = OCCURS(v_PriceCondTables ‘ ‘) + 1
v_PriceCondAppend = ”
v_PriceCondStartDate = ALLTRIM(DATE(v_StartDate ‘YYYYMMDD’))
v_PriceCondEndDate = ALLTRIM(DATE(v_EndDate ‘YYYYMMDD’))
DO ValidPriceConds2 WHILE v_PriceCondCounter <= v_PriceCondCount
The script ‘ValidPriceConds2’ is designed to import pricing condition data from a single pricing table. Each pricing table is joined to the detailed pricing data in the KONH and KONP tables. You can use this same type of query to import scale data from the KONM and KONW tables; all you need to do is redirect the join from KONP to KONM/KONW and update the imported fields. The code, in its entirety, is below, and is followed by a graphical representation of the query. Note the definition of the ‘Amount’ field. SAP has unusual behavior with the ‘%’ unit of measure. This unit of measure, as far as I know, is not defined in any configuration table. But the values stored with this unit of measure are multiplied by 10. This means a value of 10.0% is actually stored as 100.0%. The amount field definition takes care of this transformation.
v_PriceCondTable = ALLTRIM(SPLIT(v_PriceCondTables ‘ ‘ v_PriceCondCounter))
IMPORT SAP PASSWORD 1 TO ValidPriceConds_T03 SAP SOURCE “SAP AGENT” <q version=”7.0″><s>0</s><d>%v_SapServer%</d><u>%v_UserId%</u><c>%v_SapClient%</c><lg>EN</lg><cf>CurrentPriceConds_T03.fil</cf><sf></sf><jcount></jcount><jname></jname><dl>271 </dl><m>0</m><ws>0</ws><jw>0</jw><r>500</r><ar>1</ar><e>500</e><ts><t><n>%v_PriceCondTable%</n><a>T00001</a><td>Price Cond Table</td><tt>1</tt><fs><f>KAPPL</f><f>KSCHL</f><f>DATBI</f><f>DATAB</f><f>KNUMH</f></fs><wc><w><f>KAPPL</f><o>0</o><l>V</l><h></h></w><w><f>DATBI</f><o>5</o><l>%v_PriceCondStartDate%</l><h></h></w><w><f>DATAB</f><o>3</o><l>%v_PriceCondEndDate%</l><h></h></w>%v_PriceCondListSap%</wc></t><t><n>KONH</n><a>T00002</a><td>Conditions (Header)</td><tt>1</tt><fs><f>KNUMH</f><f>ERNAM</f><f>ERDAT</f><f>KVEWE</f><f>KOTABNR</f><f>KAPPL</f><f>KSCHL</f><f>VAKEY</f><f>KNUMA_PI</f><f>KNUMA_AG</f></fs><wc></wc></t><t><n>KONP</n><a>T00003</a><td>Conditions (Item)</td><tt>1</tt><fs><f>KOPOS</f><f>STFKZ</f><f>KZBZG</f><f>KSTBM</f><f>KONMS</f><f>KSTBW</f><f>KONWS</f><f>KRECH</f><f>KBETR</f><f>KONWA</f><f>KPEIN</f><f>KMEIN</f></fs><wc></wc></t></ts><js><jc><pt><pa>T00001</pa><pf>KNUMH</pf></pt><ct><ca>T00002</ca><cf>KNUMH</cf></ct></jc><jc><pt><pa>T00002</pa><pf>KNUMH</pf></pt><ct><ca>T00003</ca><cf>KNUMH</cf></ct></jc></js></q>
DEFINE FIELD Amount COMPUTED
KONP_KBETR/10 IF KONP_KONWA = ‘%’
EXTRACT FIELDS TO ValidPriceConds %v_PriceCondAppend%
KONH_VAKEY AS ‘PricingKey’
%v_PriceCondTable%_KAPPL AS ‘Application’
%v_PriceCondTable%_KSCHL AS ‘CondType’
%v_PriceCondTable%_DATBI AS ‘ValidTo’
%v_PriceCondTable%_DATAB AS ‘ValidFrom’
%v_PriceCondTable%_KNUMH AS ‘CondRecord’
KONH_ERNAM AS ‘CreatedBy’
KONH_ERDAT AS ‘CreatedOn’
KONH_KOTABNR AS ‘PricingTable’
KONH_KNUMA_PI AS ‘Promotion’
KONH_KNUMA_AG AS ‘SalesDeal’
KONP_KOPOS AS ‘PricingCondSeq’
KONP_STFKZ AS ‘ScaleType’
KONP_KZBZG AS ‘ScaleBasis’
KONP_KSTBM AS ‘ScaleQty’
KONP_KONMS AS ‘ScaleUom’
KONP_KSTBW AS ‘ScaleValue’
KONP_KONWS AS ‘ScaleCurrency’
KONP_KRECH AS ‘CalculationType’
KONP_KONWA AS ‘CondCurrency’
KONP_KPEIN AS ‘PricingUnit’
KONP_KMEIN AS ‘PricingUom’
v_PriceCondAppend = ‘APPEND’
v_PriceCondCounter = v_PriceCondCounter + 1
The remainder of the ValidPriceConds1 script cleans up the temporary tables and variables. Some sample data generated from the script follows:
Looking at our result table, you will notice the first field is the pricing key. Everything there is to know about the criteria of the pricing record is stored in this key. The assignment to sales organizations, divisions, customers, material numbers, etc is encoded in this field. This field contains all of the key values from the ‘A’ pricing tables. Unfortunately, it is not in a convenient form. We could alternatively import the individual key values from the ‘A’ pricing tables, but each table has a different key structure and it would be a nightmare to append the different structures into a single table. We need an easy way to get at this data so we can perform analyses on it. Fortunately, SAP does store this data for us – in the data dictionary. The first step is to import all of the key fields for the ‘A’ pricing tables from DD03M:
IMPORT SAP PASSWORD 1 TO PricingTableKeys_T01 SAP SOURCE “SAP AGENT” <q version=”7.0″><s>0</s><d>%v_SapServer%</d><u>%v_UserId%</u><c>%v_SapClient%</c><lg>EN</lg><cf>PricingTableKeys_T01.fil</cf><sf></sf><jcount></jcount><jname></jname><dl>92</dl><m>0</m><ws>0</ws><jw>0</jw><r>500</r><ar>1</ar><e>500</e><ts><t><n>DD03M</n><a>T00001</a><td>Table fields with data elements, text, and domains</td><tt>3</tt><fs><f>TABNAME</f><f>FIELDNAME</f><f>POSITION</f><f>LENG</f><f>INTTYPE</f><f>SCRTEXT_M</f></fs><wc><w><f>DDLANGUAGE</f><o>0</o><l>EN</l><h></h></w><w><f>KEYFLAG</f><o>0</o><l>X</l><h></h></w><w><f>TABNAME</f><o>6</o><l>A000</l><h>A999</h></w></wc></t></ts><js></js></q>
I then remove the standard key fields, because these do not show up in the pricing key from KONH:
SORT ON DD03M_TABNAME DD03M_POSITION TO PricingTableKeys_T02 OPEN IF NOT MATCH(DD03M_FIELDNAME ‘MANDT’ ‘KAPPL’ ‘KSCHL’ ‘DATBI’ ‘DATAB’ ‘KNUMH’)
Next, we need to create a table layout that will contain the key definitions. I assume my pricing keys have no more than 10 embedded values. I assume this because I verified the key counts by table and found the maximum to be 10. You will want to perform your own verification. My methodology is to create field definitions that define the name, starting position, and length of each key in each pricing table. The code below will create an EXTRACT command field listing that will do just that for 10 keys. Note that no data is extracted in the EXTRACT command – at this point I am only interested in getting a table layout defined.
COMMENT Generate the fields to extract to create our 10-key table layout
v_Keys = 10
v_ExtractFieldListCounter = 1
v_ExtractFieldList = “SUBSTRING(DD03M_TABNAME 2 3) AS ‘PricingTable'”
DO PricingTableKeys2 WHILE v_ExtractFieldListCounter <= v_Keys
DELETE v_CounterStr OK
DELETE v_ExtractFieldListCounter OK
COMMENT Extract our 10-key table layout, but do not extract any data
EXTRACT FIELDS %v_ExtractFieldList% TO PricingTableKeys_T03 IF RECNO() < 1
DELETE v_ExtractFieldList OK
The contents of PricingTableKeys2 is below. This script simply loops to append the requested number of field definitions to the EXTRACT field list. In this way, I can easily adjust the number of key fields by changing the v_Keys variable.
v_CounterStr = ALLTRIM(BINTOSTR(ZONED(v_ExtractFieldListCounter 2) ‘A’))
v_ExtractFieldList = v_ExtractFieldList + ” DD03M_SCRTEXT_M AS ‘Key%v_CounterStr%Name’ VALUE(‘1’ 0) AS ‘Key%v_CounterStr%Start’ VALUE(DD03M_LENG 0) AS ‘Key%v_CounterStr%Len'”
v_ExtractFieldListCounter = v_ExtractFieldListCounter + 1
The next section of the script is the clever bit, but I need to pause here and credit Muhammed Yousuf. He made a post on the ACL forums that made use of the following methodology, a post which was incredible for its clever simplicity, and which I found very useful while building this pricing import process.
Conceptually, ACL data files are interpreted by a table layout which defines fixed-length fields. When ACL reaches the end of the final field, it interprets then next bit of data in the data file as the beginning of the first field of the next record. This means there does not have to be a correlation between the number of rows of data we extract and the number of rows of data in the new table layout. We can use this to our advantage to easily cross-tabulate our vertical key definition data into 1 record per pricing table. The code below does that for the specified number of keys, appending blank values if the table contains less than the maximum amount of keys.
v_KeyNameLen = LEN(DD03M_SCRTEXT_M)
v_OldPricingTable = BLANKS(v_KeyNameLen)
v_KeyCounter = v_Keys + 1
v_StartPos = 1
GROUP IF v_OldPricingTable <> DD03M_TABNAME
v_Len = 0
v_StartPos = 0
LOOP WHILE v_KeyCounter <= v_Keys
EXTRACT FIELDS BLANKS(v_KeyNameLen) v_StartPos v_Len TO PricingTableKeys_T04
v_KeyCounter = v_KeyCounter + 1
v_KeyCounter = 1
v_StartPos = 1
EXTRACT FIELDS SUBSTRING(DD03M_TABNAME 2 3) TO PricingTableKeys_T04
GROUP IF v_KeyCounter <= v_Keys
v_Len = VALUE(DD03M_LENG 0)
EXTRACT FIELDS DD03M_SCRTEXT_M v_StartPos v_Len TO PricingTableKeys_T04
v_KeyCounter = v_KeyCounter + 1
v_StartPos = v_StartPos + v_Len
v_OldPricingTable = DD03M_TABNAME
COMMENT Open the n-key data with the n-key table layout and extract to the final table
OPEN PricingTableKeys_T04 FORMAT PricingTableKeys_T03
EXTRACT FIELDS ALL TO PricingTableKeys OPEN
Essentially, each key definition is appended to the end of the row containing all of the key definitions per table. To better understand, below you will find a set of diagrams to demonstrate the process. The table definition on the left is a representation of the table layout we created earlier. The table on the right is a representation of our data.
When the GROUP command first begins, it starts by extracting the table name and the first key definition. Each subsequent record containing subsequent keys is then extracted.
When a new table is reached, that table name is then extracted along with its first key definition. The process is repeated. If, in the example here, the number of keys in the table do not match the maximum, blank values are extracted. This process continues until all of the pricing tables are processed.
Now that the key definitions are extracted, we can join our table of pricing conditions to our pricing table keys and bring over the definitions. We can then create a series of computed fields to define the data in the keys using the following code:
DEFINE FIELD Key01Value COMPUTED SUBSTRING(PricingKey Key01Start Key01Len)
DEFINE FIELD Key02Value COMPUTED SUBSTRING(PricingKey Key02Start Key02Len)
DEFINE FIELD Key10Value COMPUTED SUBSTRING(PricingKey Key10Start Key10Len)
From this point, you can determine important keys to identify in a single field. Perhaps it would be useful to have the sales organization for each record. The following field definition would give that value, assuming the related pricing table has sales organizations as one of its keys:
DEFINE FIELD SalesOrg COMPUTED
Key01Value IF Key01Name = ‘Sales Org.’
Key02Value IF Key02Name = ‘Sales Org.’
Key03Value IF Key03Name = ‘Sales Org.’
Key04Value IF Key04Name = ‘Sales Org.’
Key05Value IF Key05Name = ‘Sales Org.’
Key06Value IF Key06Name = ‘Sales Org.’
Key07Value IF Key07Name = ‘Sales Org.’
Key08Value IF Key08Name = ‘Sales Org.’
Key09Value IF Key09Name = ‘Sales Org.’
Key10Value IF Key10Name = ‘Sales Org.’
Once you understand this process and data structure, you can perform whatever analysis you see fit. For example, perhaps your company defines a standard base discount of 10%. You could filter on the appropriate condition type and summarize on the value field and quickly identify records that are not equal to 10%. For another example, assume all customer-specific discounts are supposed to be limited to a single condition type. You could define a Customer field using the same pattern we used for SalesOrg. If you exclude the appropriate condition type, all of the remaining records should have a blank customer value. Any exceptions would be obvious.
The type of analyses you perform are going to be tailored to the purpose of your audit, your engagement risk assessment, and the unique processes of your company. In my experience, the challenge has been to obtain the entire population of pricing data. I have had great success analyzing pricing with the process detailed in this post, and I hope you find it useful as well. As a parting gift, here are the completed scripts that perform all of the steps we have discussed. And although they work in my environment, they are provided without warranty of any kind. Feel free to change as needed to make them work for you.