As standard functionality, an SAP system does not update the KNA1 (general data in customer master) table when a user makes one-time changes to a customer ship-to address on a sales order. Instead, the system tags the sales order header with an address key that points to another table, which stores the details of the one-time address change. The same thing happens when you enter ship-to address information for one-time customers in a sales order.

This means that when you try to extract this information from the sales order, the information is not there, or the system extracts incorrect information from the KNA1 table regarding the ship-to party. In my experience, given the impact on profitability reporting of ship-to country, region, and city, incorrect or inaccurate information could present a false profitability view of the reports, leading to bad business decisions. I’ll show you a method to make that information available and then I’ll explain how to validate the configuration. I used mySAP ERP Central Component (ECC) 5.0 for the derivation, but it also works in R/3. First I’ll give you some background information about the derivation process.

Standard CO-PA Derivation

A typical Profitability Analysis (CO-PA) derivation process for extracting ship- to country, region, and city involves reading the value of the ship-to customer field KUNWE from the PAPARTNER table. The PAPARTNER table stores Sales and Distribution (SD) partners such as sold-to party, ship-to party, and bill-to party, which come from the header record in any given sales order. Once you retrieve a value for one of these partners from the PAPARTNER table, you can use it to extract additional information, such as the ship-to country, region, and city, for that customer from the KNA1 table.

If the ship-to customer address information has been changed on the sales order, the KNA1 table is not updated and a derivation step defined using the typical process described above would be inaccurate. The KNA1 table is never updated with customer address information for a one-time customer. Using the typical derivation process just described, you would not find a value in the KNA1 table for the ship-to customer at all.

For the purposes of this article, I’m assuming the following conditions exist at a customer site:

• It is fairly common to change the ship-to address on a sales order header record.
• A significant number of sales orders are created for one-time customers.
• CO-PA captures ship-to customer address information (i.e., destination country, region, city) as a dimension of profitability analysis and reporting.

Under these conditions, the process I describe in this article ensures your system extracts accurate information regarding ship-to customer address information for profitability reporting.

The System’s Role in a Typical Derivation

When you make changes to the ship-to address on a sales order, R/3 updates the VBPA (sales document partner) table by generating a special 10- character indicator and populating the ADRNR field with that generated value. ADRNR then becomes a key field to the ADRC (business address services) table in which you can store the address details, such as the ship-to country, region, and city. Only the ADRNR field value remains in the sales order. This same process occurs when you use a one-time customer and add the ship-to address at the time you create the sales order. The system does not update the KNA1 table with any new values in these cases. That is why, under these circumstances, the typical derivation process described above does not provide accurate and complete information to CO-PA.

You need values for three key fields to access records in the VBPA table. They are:

• Sales order number (field VBELN — 10 characters)
• Sales order line number (field POSNR — 6 characters)
• Partner function (field PARVW — 2 characters)

You can retrieve the sales order number from the CO-PA transaction record. Because you execute the derivation at the transaction level as posting occurs in CO-PA, this solution works without making the sales order number a segment-level characteristic.

The sales order line number in the VBPA table defaults to value 000000 for sales order header records like this one. The partner function is a shorthand code that differentiates between the sold-to party, ship-to party, bill-to party, and so on. Though the master record in the VBPA table displays SH as the ship-to partner function value for the PARVW field, the database actually stored the value as WE, which I learned through a lot of trial and error when defining CO-PA derivation rules. This display occurs because the partner functions have an external and internal format. The external format that corresponds to WE is SH (Table 1).

You can create a CO-PA characteristic for the ship-to partner function PARVW and add it to the Operating Concern, but it is unlikely that you would use this characteristic in CO-PA reporting. Since you only have a limited number of user-defined characteristics available in CO-PA, there is no need to use one of them by creating a characteristic in your Operating Concern for PARVW. The ship-to party partner function often retrieves more detailed customer information.

New Derivation Process to Ensure Accurate Information

Because of the limitations of a typical derivation, and the system’s role in the process, I use an alternate method to ensure accurate and complete information. This derivation process requires four derivation steps to be successful and assumes that you already have captured the sales order number. Doing so is standard CO-PA functionality in both R/3 and ECC. Also, you must store the derivation steps defined in transaction KEDR in the order the system should execute them so that you do not lose dependencies between derivation steps. This also ensures that the end result of all the related derivation steps is successful. In my example, I use three of the six types of derivations: move, in steps 1 and 3; table lookup, in steps 2 and 4; and clear, in step 4. For more information on derivation types, see the sidebar, “The Six Types of Derivations.”

Step 1. Use the USERTEMP1 field to store the ship- to party partner function value WE (Figure 1). Using transaction KEDR, create a derivation step by moving WE to USERTEMP1 for ADRNR lookup.

Figure 1

Move WE to USERTEMP1 for ADRNR lookup

Step 2. Create a derivation rule to read the VBPA table for the address code ADRNR field value and move that value to CO-PA global USERTEMP2. Create a derivation using the table lookup function. To do this, you need to provide values for the three key fields required to read records from the VBPA table: the SD sales order number (VBELN), the sales order line number of the SD document (POSNR), and the partner function (PARVW). In this derivation you insert values for these key fields from three corresponding fields in CO-PA. They open the door to the rest of the values stored in VBPA, in this case the ADRNR address code in particular.

The system stores the values you use to read the VBPA table in CO-PA fields KAUFN (sales order number), KDPOS (sales order line item, which I am defaulting to 000000 as it is a header record I am reading), and USERTEMP1 (the partner function for the ship-to party [WE]).

You then store the value you retrieved (ADRNR) in the global USERTEMP2 field to use in the next derivation step (Figure 2).

Figure 2

Store the value ADRNR in USERTEMP2

Note that when defining the value to use for KDPOS, you want it always to be the same constant value 000000. To do this, you need to define the details for the value to use for KDPOS. Do this by clicking on the details icon for that line of the derivation, found on the second line of the Source Fields for Table Lookup section of the derivation screen. A new pop-up screen displays (Figure 3).

Figure 3

Create constant value 000000 for KDPOS

You also must define how you want to store the value you retrieved for ADRNR in the USERTEMP2 field. Click on the details icon on the Assignment of Table Fields to Target Fields section of the derivation screen in Figure 2. A pop-up screen displays, as shown in Figure 4.

Figure 4

Store the retrieved value of ADRNR in the pop-up screen

Be sure to select Overwrite with new value even if new value is initial to store the retrieved value of ADRNR, even if it is blank, in the USERTEMP2 field. Note that if the value of ADRNR is blank, likely no last-minute changes were made to the ship-to party on the sales order and a one-time customer was not used on the sales order. When ADRNR is blank, none of the rest of the derivation steps in this derivation sequence are executed as they have no value with which ADRNR can search the ADRC.

Step 3. Use the ADRNR value to read the ship-to customer address information from the ADRC table. To access records in this table, you need values for the following key fields:

• Address number (ADDRNUMBER – 10 characters)
• Date valid from (DATE_FROM – 8 characters in date format)
• International address version ID (NATION – 1 character)

Your system defaults the value of DATE_ FROM to January 1, 0001 — stored in the database as 00010101 — and defaults the value of NATION to blank unless the user enters specific values for these fields. If you typically change the Date valid from value, you must adjust the derivation rule accordingly. However, in my experience this is rarely done. Additionally, if the language chosen on the ship-to address screen of the sales order is Arabic, Hebrew, Chinese, Greek, Hangul, International, Kanji, Chinese traditional, Katakana, Cyrillic, or Thai, you need to adjust the derivation rule to supply values for the NATION field according to Table 2.

Since the DATE_FROM field is a key field you use to open the ADRC table, and you want to use the default value of 00010101 for that value, you must store that default value in another USERTEMP field, in this case, USERTEMP3. Create another derivation using the move function, and move the value 00010101 to the USERTEMP3 field (Figure 5).

Figure 5

Move the 00010101 value to USERTEMP3

Step 4. Store a blank value to use for the International Address Version ID (NATION) in USERTEMP4. In this example, assume the NATION field is blank. This step is insurance, since you want to be sure that when you use the key fields to open the ADRC table, the NATION field does not contain a value. To accomplish the derivation strategy, this step is not strictly required. However, it is good practice to use it to ensure the system always applies correct values to the derivation steps. Use the clear function to accomplish this (Figure 6).

Figure 6

Clear values from USERTEMP4 to ensure the field is blank

Now you have the keys you need to read the ADRC table for accurate ship-to customer information. You can use the values stored in USERTEMP2 (ADRNR), USERTEMP3 (DATE_FROM – constant value of 00010101), and USERTEMP4 (NATION – cleared to be blank always), to read records in the ADRC table. You want to find ship-to country, region, and city that were used for either a one-time customer, or when changes were made to the ship-to address directly on the sales order. You need to ensure that characteristic fields already exist in CO-PA to hold these values. You created these characteristics for the Operating Concern before beginning the process of defining this derivation strategy. Define this derivation step to move the values found in ADRC to CO- PA transaction records where you can use them for profitability reporting and analysis. In this example, you can move the ADRC values to the existing CO-PA characteristics WW001 – Destination Country, WW019 – Destination Region, and WW018 – Destination City (Figure 7).

Figure 7

Look up ADRC ship-to country, region, and city, and move to CO- PA

Note that when you look at the ADRC_CITY1 field in the database, you see that your system defines it to be 40 characters. Since R/3 only allows a user-defined CO-PA characteristic to be a maximum of 18 characters, you move only the first 18 characters of this value into the CO-PA WW018 — Destination City characteristic field in this derivation step. Click on the Details button for the line containing WW018 in the Assignment of Table Fields to Target Fields section of the derivation screen. Refer to Figure 4 for an example of how you accomplish this.

Validate the Derivation Results

You can test the accuracy of the derivation strategy just defined by creating a sales order, changing the ship-to information on the sales order, and validating that the derivation strategy works. Create a sales order using transaction VA01 and change the ship-to address information (ship-to country, region, and city) before saving it. Create a delivery for the sales order using transaction VL01N (be sure to execute a post goods issue). Then create and release a billing document for the sales order using transaction VF01, for which you may need assistance from the SD group.

Enter the billing document number, in this example, 5200000317, in transaction KE4ST – Simulate billing document transfer (Figure 8). When you execute this transaction, a log display screen displays transaction results (Figure 9). Highlight the message text and click on the Characteristics button on the toolbar to see a list of characteristics populated by the simulated posting of this billing document, as displayed in Figure 10.

Figure 8

Simulate billing document transfer

Figure 9

Billing document simulation results displayed on the log display screen of transaction KE4ST

Figure 10

Display the CO-PA characteristics resulting from the billing document simulation posting

Note that the simulated billing document posting populates the Destination Country, Destination Region, and Destination City characteristic fields with the values FR, 07, and French, respectively. This is only a simulation of what would happen had you actually executed the derivation. These results do not actually populate any tables.

Click on the Display Derivat. Analysis button (Figure 10) to see the simulated results of the derivation strategy that you plan to execute once the system posts the billing document. A new screen displays all of the derivation steps involved in this derivation rule. Expand the specific derivation rules defined by clicking on the + folder icon to the left of each rule to see each result.

In Figure 11 you can see from the simulated results of the first derivation step that you were successful. Note the content of the USERTEMP1 field changed from blank to WE after executing the derivation step.

Figure 11

Review the simulated results of the first derivation step

Figure 12 shows that the second derivation step also was successful: The value of the USERTEMP2 field changed from blank to 9000000079 after the derivation step. The table in Figure 2 first retrieves value 9000000079, though the value is not displayed until the validation process. This is also true for many of the retrieved (USERTEMP2), defaulted (USERTEMP3), or cleared (USERTEMP4) values.

Figure 12

Validating step 2

Figure 13 shows that the third step (populating USERTEMP3 with 00010101) was successful, displaying the new value in the Content after section and a blank value in Content before.

Figure 13

Validating step 3

Figure 14 confirms the success of another piece of derivation in step 3, clearing out the existing values from the USERTEMP4 field. Both the Content before and Content after values for USERTEMP4 are blank.

Figure 14

Before and after values of USERTEMP4 are blank, proving that NATION has been cleared.

Finally, Figure 15 displays the result of step 4 in the derivation strategy and shows that, using the key field values supplied to read the ADRC table, the system retrieved the correct ship-to customer address information for the CO-PA transaction records.

Figure 15

Validating step 4

Note that if you have defined a derivation rule to move the ship-to customer address information from KNA1 based on the ship-to customer on the sales order header and in the PAPARTNER table, you should execute this derivation step after the derivation steps defined here. That is because all derivation steps defined in transaction KEDR are executed in the order they are listed. This ensures that when you do not make changes to the ship-to customer address on the sales order or when you do not use a one-time customer, the ship-to country, region, and city CO- PA characteristic values remain populated. They contain values from the KNA1 table instead of the ADRC table that this derivation strategy uses.

To do this, be sure to indicate that the system should not overwrite existing values by selecting Do not overwrite with new value if field already filled (Figure 16). Failing to execute this step exposes the values you have derived in this example to the possibility that the system will overwrite them, wasting your entire derivation strategy and execution.

Figure 16

Characteristic Derivation from KNA1

George Colburn

George Colburn has been a business software consultant for 22 years with experience as a systems analyst, software developer, trainer, and project manager. He has worked with SAP America for the last 10 years as a principal consultant focusing in CO – Cost Center Accounting, Profit Center Accounting, Internal Order Management, Profitability Analysis, Product Costing, and Report Writer/Painter.

You may contact the author at george.colburn@sap.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.