In product cost controlling, SAP offers different OTS functionalities to update the standard price of a material. Based on the type of business requirements associated with the update of the standard price and complexity involved in the process, you need to select and implement the relevant SAP solution. My tip “Overview of Standard SAP OTS Functionalities” explains the functionality of each of these standard transactions, and the pros and cons of each.

As an alternative to the standard transactions, you can also design a custom Microsoft Excel upload functionality using a standard BAPI. The custom Excel-based upload tool can help overcome limitations involved in standard transactions and facilitate the price update process. Some of the benefits of the tool include:

• End users can enter the details of standard price for multiple materials in an Excel sheet. You can design the custom program in a way that allows users to easily update the standard price for multiple materials. In many standard transactions, you can only update a limited number of materials in a single view, which requires a lot of manual effort from users. It is also possible that the custom Excel upload program can update the standard price for a material either in a specific plant or across all plants where the material has been created or extended.
• The program can automatically carry out necessary currency conversion for a standard price update and help avoid user data entry issues. Errors in currency translation or in entering the correct currency value in the relevant currency tabs in standard OTS transactions often lead to discrepancies in inventory valuation.
• The program provides a simulation view first to business users giving a display of stock revaluation impact, current price, new price, and stock quantity at the material or plant level before the user carries out the actual price update.
• In the material master, the standard price is updated with reference to a price unit (value of price unit in the material master can be any numeric value, say 1, 10, or 100). It is important to enter the value of the price of a material with reference to the price unit. In standard SAP OTS transactions, sometimes users do not notice the associated price unit for a material and enter the price value incorrectly, leading to incorrect valuation. The Excel upload program helps overcome this limitation because it carries out the price unit conversion automatically based on a user-entered price unit in a template with the price unit maintained in the material master.

I’ll give you the details so you can incorporate each of the above features into the design of the custom Excel upload tool. See “Use of the Excel Upload Tool in a Material Ledger Scenario” for more about a material ledger/actual costing functionality scenario.

Design of the Excel Upload Tool

To upload standard prices for materials in an SAP system using an Excel template, use BAPI_MATVAL_PRICE_CHANGE. To design the tool, create a template format, design a selection screen for the program, and specify the validation and checks that you want to carry out before updating the standard price. Further, map the BAPI import parameter values for carrying out standard price update of a material and also design necessary security authorization controls.

Upload the Template Format

Business users enter the price change details in the Excel template format proposed in Figure 1. They then save the file in a text tab delimited file format.  

• Material number: Valid SAP part number for which the price update has to be carried out. When the file is uploaded to the program, the system validates the entered part number. The validation logic is explained in the next section.
• Plant: Depending on the business requirement, you can either enter the plant value in the template or leave the value blank. If the requirement is to update the standard price for a material in a specific plant, then you need to enter the value. If a uniform price has to be updated across all plants where the material has been created or extended, then you can leave it blank. You can design the BAPI BAPI_MATVAL_PRICE_CHANGE with logic to identify all the plants where the material has been created or extended. I’ll explain the logic of how the program identifies the plants automatically in the next section.
• Price: Proposed new standard price value with decimal places as necessary
• Currency: Currency to which the new price is associated
• Price unit: A numeric value entered by the user such as 1, 10, or 100

Selection Screen

Figure 2 shows the selection parameters in the input screen of the custom program. The custom program has the following selection parameters included:

• Input File: Text tab delimited file format uploaded from a desktop/local hard drive of user
• Simulation Output check box: If you check this check box, the program does not perform a price update but just provides a simulation view of the revaluation impact. If you do not check it, the program displays the output and also carries out the update process.
• Download Template button: You have the option of downloading the file upload template format. SAP function module GUI_DOWNLOAD enables the end user to download the upload template from the custom program transaction selection screen. This enables the user to download the template file to their local directory.

 

Input Validations and System Checks before Update

The program carries out basic validations and detailed system checks for every record. It does this before the user carries out the actual price update or computes the revaluation impact. Among the validations you need to implement for the material value entered in the template are the following:

• The material has to be a valid part number in table MARA, field MARA-MATNR. If not, then set the record with an error state and return an error message as “Material not a valid part number in SAP.”
• Do not set the material for Marked for deletion at a client level. If field MARA-LVOMA in table MARA is set, then return the record with an error state and return an error message as "Material marked for deletion."
• Check whether the material has accounting views created in the plant by determining whether the material exists in table MBEW. If the material is not in MBEW for the plant, then return an error message as "Material does not have valid accounting/costing view in plant."
• Verify whether the material is set for deletion at plant level in table MBEW. If MBEW-LVORM is set for the material in any plant, then return a message as "Material set for deletion in plant 0001," for example.
• Confirm whether the material has the Do not Cost indicator set in the plant by verifying field MARC- NCOST in table MARC. If the material is set with the Do not cost indicator, then return a message as "Material set with do not cost indicator in plant 0001," for example.

You can also perform actions related to other fields in the template:

• Validation for plant: Make sure the plant entered in the template is valid. If a plant is not entered in the template, then identify all the plants where the material is created or extended from the MBEW table. The process of updating the same price across all plants can vary from one enterprise to another, so you would design the logic based on your company’s business requirements.
• Validation for price and currency: In the template record, ensure the price and currency values are not blank.
• Validation for the price unit: The user-entered value in the template should not be blank or have decimals. If so, the entire record errors out.

If the price unit is valid, then you can carry out the price unit conversion and align the price entered in the template to the price unit in the material master. Use this formula: (Price in template / Price unit in template) * Price unit in material master

Currency Conversion Logic

As mentioned earlier, you can design the upload tool to do automatic currency conversion as a part of the standard price update process. There are two possible scenarios for currency conversion that the program may need to handle as a part of design logic:

1. Currency conversion of the new standard price entered in a template to local currency (if the material ledger is not implemented) or local currency and other parallel currencies (if the material ledger is active)
2. Currency conversion of existing standard price to a different currency to report revaluation impact

Conversion Scenario 1

In the first conversion scenario, you change the template input price to a new standard price in local currency:

• For the plant in which the standard price has to be updated, derive the company code value from table T001K and the company code currency from table T001
• To convert from template currency to local currency, use the standard SAP exchange rate conversion function module CONVERT_TO_LOCAL_CURRENCY
• The template price amount is calculated after price unit conversion and provided as an input to the exchange rate conversion function module
• The function module automatically computes the new standard price in the local currency for an update in the material master. It takes into account the company code currency, the current exchange rate between template currency and company code currency, and the translation ratio between currency pairs and decimal settings.

Use similar logic and the same function module to convert from the template currency to other parallel currencies (e.g., group currency), providing the correct exchange rate combination for the scenario when the material ledger is active for the plant.

Conversion Scenario 2

In the second scenario, you change the current standard price in local currency to group currency. Table MBEW (material valuation table) stores the standard price only as the local currency in field MBEW-STPRS. If the revaluation impact has to be determined in a currency other than the local currency (e.g., group currency or template input currency), use the currency conversion function module to convert the current standard price in local currency to group currency following a similar process as described above.

Revaluation Impact in Simulation Mode

As mentioned earlier, end users have the option of viewing simulation results in test run mode before actually carrying out an update run. If you select simulation mode for the material/plant combination where the standard price change is to be posted, the program provides details of the revaluation impact before carrying out a price update. You need to provide the revaluation impact as an output at the material and plant level. Use these formulas to determine the revaluation impact: Revaluation impact in local currency = current stock quantity * (current standard price in the material master – new standard price calculated after currency conversion) / price unit in material master Revaluation impact in group currency (if applicable) = current stock quantity * (current standard price in group currency after currency conversion – new standard price in group currency) / price unit in the material master Determine the current stock quantity of the material in a plant from table MBEW (MBEW-LBKUM). Calculate the revaluation impact and display all the values in the program output. Then, output the revaluation impact by material, plant, stock quantity, current standard price, new standard price, and revaluation impact value.

Posting Price Update Using BAPI

In this section, I’ll show you how to carry out a price update using BAPI BAPI_MATVAL_PRICE_CHANGE. I’ll also go over the prerequisites to use the BAPI, the mapping of BAPI import parameters, and the output you get from the BAPI.

Prerequisites for BAPI Update Functionality

To use the BAPI to carry out a price update, follow IMG menu path Controlling > Product Cost Controlling > Actual Costing / Material Ledger > Set Up Material Price Dispatch. In this configuration, set the warning and error tolerances (e.g., 25.00 and 999.99) for a price change at a valuation area level (Figure 3).   In this example, 25% has been set as the tolerance level to issue a warning. If a price change exceeds 25% up or down, the BAPI issues a warning message. Error tolerance levels are set at 999.99%.

Import Parameter Values Mapping to BAPI

You can input date and fiscal period parameter values to the BAPI as follows:

• Price change date: Current user date based on time zone of user
• The program should automatically determine the other related parameters required for the BAPI as shown in Table 1.

  The custom program passes on the BAPI parameter values shown in Table 2, which include both default values and some derived and calculated within the program. If the material ledger is activated, you can change the prices in all currencies and valuations maintained in the material ledger. Pass the value of the material, valuation area (plant), price change date, and valuation type as a parameter to the BAPI BAPI_MATVAL_PRICE_CHANGE. The valuation type is set to blank. If you get an error message, read the error message details or commit the changes after the BAPI execution.

Export Values from BAPI

Depending on whether the standard price update was successful, the BAPI exports the output values to the program as shown in Table 3.

• Price change document: The document number for the price change document if the BAPI execution is successful for every line in Excel as output to the user

 

• Return error messages for each line entered in Excel as output to the user. The BAPI exports the error message parameters to the program as shown in Table 4.

  After carrying out the price update using BAPI, the program displays the results of the update process and the errors encountered in the update process for each material/plant combination as shown in Figure 4. You can export all the output values to Excel in SAP List Viewer (ALV) format.   If the update is successful, then the report output should have all the details similar to the simulation run in the revaluation report output format:

• Material
• Plant
• Valuation class
• Price unit from the material master
• Current stock quantity from the material master
• Current standard price (local currency)
• Current standard price (group currency), if applicable
• New standard price (local currency)
• New standard price (group currency), if applicable
• Revaluation impact (local currency)
• Revaluation impact (group currency), if applicable
• Price change document number
• Price change document year

For records for which the update is not successful, consolidate all error messages by record. In the report output, include an exception list with these details:

• Record number (line number in an Excel template)
• Material
• Plant
• Error message

Security Authorization Control Check in Price Updates

In addition to the basic security authorization check, which determines whether an end user has access to this transaction, you can implement authorization control to make sure an end user has access to update standard prices for a specific plant/valuation area. This additional authorization control is desirable for better Sarbanes-Oxley controls and ensures the right authorized group has access to update prices only for specific plants. Carry out this additional security authorization check using the AUTHORIZATION-CHECK ABAP command within the ABAP program. It checks whether the user has the value of authorization object Valuation area in the security profile for the transaction. Only if the user has authorization for a particular plant does the program continue with other validation, derivation, or price change posting steps. If there is an authorization failure, the data is not processed for the specific plant/material combination. This ensures that users update prices only for the plants/valuation areas for which they are authorized in the security profile. Figure 5 shows details of the authorization object to be called out in the security role/profile.