Formula Calculation
Overview of Features
The planning function of type Formula Calculation offers you a simple programming language for manipulating transaction data. It includes elements that can be found in many macro languages for business applications:
- Statements that change transaction data using operators and functions for example: REVENUE = SLSQTY * PRICE.
- A form of conditional statement for example: IF expression. statement. ENDIF.
- General loop construct for example: DO. statement. IF expression. EXIT. ENDIF. ENDDO.
- A special loop construct FOREACH that iterates over all characteristic values of one or several characteristics in the transaction data. The exact explanation of the construct can be found further below in a special section.
- A DATA statement for matching auxiliary variables.
- A comment option. Comments begin with '*' at the start of the row.
Internal Data Objects and Address
The internal data objects that are edited are subsets of the transaction data contained in the planning package. The user executes a planning function by selecting a package and a parameter group. This package is divided into smaller data objects made up of transaction data. If a formula requires reference data, these data objects are also created from this reference data. Note: If it is not possible to create data objects, a formula is not executed. The data objects are only different in terms of the characteristic values of the characteristics to be changed. The values of the other characteristics are the same. If no characteristics are selected to be changed, the formula is executed for each transaction data record. Therefore, as far as the system is concerned, the planning function is not called once for each package, but once for each data object. Each key figure value in the data object can now be addressed uniquely and changed by the functions due to the exact specification of the characteristic value of the characteristic to be changed, and the specification of the key figure name. In order to understand how a formula works, it may be useful to define a layout in which all the characteristics to be changed are placed in the lead columns and the remaining characteristics are placed in the header. The formula is then processed for all combinations. Of course the reference data that is referred to in the formulas is not available in the current package. For this reason, you may have to extend the layout to include columns with reference data. Restrict the values in the package as far possible to the characteristics that are not to be changed in the formula so that less data objects are generated.
In practice, some functions are always executed for all or a large number of the key figures. With a very large number of key figures, it can be awkward to have to write the same equation for each key figure. Therefore, there are two possible ways of expressing the key figure reference for a formula:
- You make no entry for key figures. In this case, the system executes the calculation described by the formula for all key figures of the planning level. For example, copying version 001 to version 002 can be written as a formula as follows: { 002 } = { 001 }.
- You explicitly enter the key figure for which the calculation should be executed. For this you include the generic entry "key figure name" in the list of fields to be changed, and enter the technical name of the key figure that is to be calculated in the formula operators. To execute the example shown (copying between versions) for the REVENUE key figure only, for example, write { REVENUE, 002 } = { REVENUE, 001 }.
There are two options if you want to access reference data in the formulas. You take the field that makes the reference data different from the data of the active planning package as the field to be changed (for example: Version technical name 0VERSION). You then write the operand like this { REVENUE, 002 } = { REVENUE, 001 }. The other option is to address the reference data explicitly. To do this, you enter the name of the characteristic and the value in the operand: { REVENUE = { REVENUE | 0VERSION = 001 }. The data of version 002 is then in the active planning package. The formula is run for each data record of the active planning package. The difference between the two variants is not only syntactic. The creation of data objects is controlled and this defines how often the formula is executed. For { REVENUE, 002 } = { REVENUE, 001 } the procedure is as follows: If there is no data in version 002, the internal data objects are created using the data from version 001. When the formula is executed, the data is created in version 002. { REVENUE, 002 } = { REVENUE, 001 } is suitable for formulas that create new data from reference data. In the case of { REVENUE} = { REVENUE | 0VERSION = 001 }, the formula is only run for all records in version 002 because it cannot be determined from the formula how the records from version 001 should be transformed to records from version 001. If there are no records, the formula is not run. No records are created. This format is suitable for formulas that require reference data to evaluate existing data.
Example: The following transaction data is available:
| 0VERSION | 0COSTCENTER | REVENUE |
| 001 | 4711 | 3 |
| 001 | 0815 | 2 |
| 002 | 4711 | 9 |
Result from: { REVENUE, 002 } = { REVENUE 001 }.
| 0VERSION | 0COSTCENTER | REVENUE |
| 001 | 4711 | 3 |
| 001 | 0815 | 2 |
| 002 | 4711 | 3 |
| 001 | 0815 | 2 |
Two data objects are formed.
Result from: { REVENUE } = { REVENUE | 0VERSION = 001 }.
| 0VERSION | 0COSTCENTER | REVENUE |
| 001 | 4711 | 3 |
| 001 | 0815 | 2 |
| 002 | 4711 | 3 |
Data objects
| 0VERSION | 0COSTCENTER |
| 002 | 4711 |
Before executing the parameter group, the system decides which reference data is required based on a formula. The original selection is enhanced for the reference data selection. Assume that only data of version 002 is contained in the planning package. Based on the formula row { ERLOES, 002 } = { ERLOES, 001 }, the selection is enhanced with the value Version 001. Unfortunately it is not always possible to decide which reference data is required before starting a planning function of type Formula. If this occurs, the selection is deleted.
The cases in which this can occur are:
- Use of function TMVL to change the value of a time characteristic
- Use of function ATRV or ATRVT to read attribute values
- Call of function modules
The selection is only deleted, however, if the results of the functions for addressing reference data are used.
DATA FISCPER TYPE 0FISCPER.
DATA ACTPER TYPE 0FISCPER.
ACT = VARV( AKTPERIO ).
FISCPER = ACTPER.
FISCPER = TMVL ( FISCPER , -12 ).
{ ERLOES, ACTPER } = { ERLOES, FISCPER }.
In the example, the formula variable ACTPER is first filled from the global variable AKTPERIO. This value is then assigned to variable FISCPER. The value of ACTPER is then decreased by 12 using the function TMVL. Finally, the value of key figure ERLOES in period FISCPER is assigned to the key figure ERLOES in period ACTPER.
Conditions
Characteristics, which are not marked as to be changed, can be selected as characteristics for conditions. Selection conditions can be specified for these characteristics. A formula is only calculated if the selection condition is fulfilled for the internal data object. The "key figure name" can also be selected here like the other characteristics of the level for conditions are selected. If conditions are set for the field "key figure name", then the formulas are only calculated for those key figures that satisfy the selection conditions.
Formula or Exit: Which function type is the right one?
In addition to the formula calculation there is also the option of creating planning functions of type Exit in order to manipulate transaction data using a programming language. Weigh up the following points when deciding which function type to use:
- Planning functions of type Formula are easy to learn and only require a small amount of training. This function is ideally intended for a user in Controlling, who has already mastered an algorithmic programming language or a macro language, and can solve most of the problems with the formula language.
- Planning functions of type Exit must always be written if you require features that are yet not available by any other means. Example: When calculating costs, customer-defined tables have to be accessed. So far, no feature is available to access formulas in any tables.
- You can call function modules in formulas. In this way it is possible for part of the logic to come from the function modules and part from the formula.
- Generally, Exit function modules achieve a higher level of performance than formula calculations. The reason for this is mainly that each operand and each result from complex formulas are read separately from an internal table. In a self-written program, you would optimize this. This emphasis on performance is a decisive criterion with larger sets of data.
In addition, you can also find information on formula calculation in the help on the application.
Data Declarations
Auxiliary variables can be defined as follows:
DATA HELPVAR TYPE I.
DATA KEYRA TYPE KEYFIGURE_NAME.
Data declarations are introduced with the word 'DATA', followed by the name of the auxiliary variable. The name must start with a letter and can contain digits and letters, however no special characters. Variable names must differ from keywords such as DATA, ELSEIF or SIN. If the formula syntax is later enhanced by new keywords, existing variables with the same name will become invalid.
To avoid errors, do not name the variables like characteristic values, or enclose the characteristic values in single quotes.
There are predefined types I (integers) for index operations and F (floating points) to calculate. You can also define variables of the type of characteristics to be changed. The technical name of a characteristic serves as the type description. If you have selected "key figure name", variables of the type KEYFIGURE_NAME can be defined. You can display the valid data types, when you press the pushbutton 'data types'.
You can also create data types for attributes. If the attributes are of the type key figure, auxiliary variables must be created of the type F or I. This is then displayed, where in the field for the type, the string attribute name = 'F' or attribute name = 'I' is displayed. You require the technical name of the attribute, when you want to read attribute values with the function ATRV.
Expressions
You can use the following elements in expressions:
1. Constants, which consist of digits and a decimal point.
2. Variables, which you have declared with the data statement.
3. Operands, that you can select using F4 Help. If the operands are not only addressed using key figure names, then the operands are contained in curly brackets '{' and '}'. If, on the other hand, "Key figure name" is the only characteristic to be changed, then you can write down the key figure name directly as the operand. The valid syntax and correct sequence of characteristics is displayed in the row above the Editor. You can use the F4 help to compile valid operands. If the characteristic values contain blank characters or symbols such as '+', '-', '*' in the operands, then the characteristic values have to be contained in quotation marks.
There is always an external and an internal format for a characteristic value. For example, date values are presented externally as 3.12.1963 and internally as 19631203. Always use the internal format in formaulas. In operands you can also use variables instead of characteristic values.
Note: If "key figure name" is the only characteristic to be changed, and you have defined a variable of the type KEYFIGURE_NAME and want to change key figures with it, then you must also enclose this in curly brackets. The following statement sets all key figures to the value 1. The FOREACH construct fills the variable KEYFIG with all key figure names of key figures, which are available in the level.
DATA KEYFIG TYPE KEYFIGURE_NAME.
FOREACH KEYFIG.
{ KEYFIG } = 1. ENDFOR.
4. Operators
| Operator | Meaning |
| + | Sign |
| + | Addition |
| - | Sign |
| - | Minus |
| * | Multiplication |
| / | Division |
| DIV | Division of integers |
| MOD | Modulo operation |
| ** | Exponentiation |
5. Functions with one operand
| Function | Meaning |
| CEIL | smallest integer value, which |
| is not smaller than x |
| FLOOR | largest integer value, which |
| is not larger than x |
| TRUNC | integer part of x |
| FRAC | fraction of x |
| ABS | amount (absolute values) |x| of x |
| SIGN | Signum (sign) of x |
| ACOS | Arccosine(x) in the range [-pi/2, pi/2], |
| x from [-1, 1] |
| ASIN | Arcsine(x) in the range [0, pi], |
| x from [-1, 1] |
| ATAN | Arctangent(x) in the range [-pi/2, pi/2] |
| (pi = 3.1415926535897932) |
| COS | Cosine of an angle |
| given by the radius |
| SIN | Sine of an angle |
| given by the radius |
| TAN | Tangent of an angle |
| given by the radius |
| COSH | Hyperbolic cosine |
| SINH | Hyperbolic sine |
| TANH | Hyperbolic tangent |
| EXP | Exponential function to the base |
| e = 2.7182818284590452 |
| LOG | natural logarithm |
| (this means to the base e) |
| LOG10 | logarithm of x to the base 10, x > 0 |
| SQRT | square root of a non-negative number |
| STRLEN | length of character string |
| VARV | variable value (argument = variable name) |
| VARC | number of values of a variable |
| (argument = variable name) |
6. Functions with two operands
| Function | Meaning | Operand1 | Operand2 |
| MAX | Maximum | | |
| MIN | Minimum | | |
| ATRV | Read attribute | Attribute type | Variable |
| TMVL | Read time chara. | Value | Offset |
| PERP | Perpetual annuity | Key figure | Interest rate |
| C2DATE | Determine date | Time characteristic | Start/end value |
| CONCAT | Concatenate operands | Operand1 | Operand2 |
| VARI | i-ter variable value | variable | index |
| ROUND | Rounding | key figure | decimal places |
7. Functions with three operands
| Function | Meaning | Operand1,Operand2,Operand3 |
| DISC | Discounting | Key figure, Interest rate, Years |
| DECD | Declining balance | Start value, Percentage, Years |
| depreciation | |
| ATRVT | Read Time-Dependent | Attribute type, Variable, |
| Attribute | Date |
| ATRVT | Read partial string | Variable, Offset, Length |
| SUBSTR | Read substring | Variable, Offset, Length |
| REPLACE | Replace Character | Source String, Pattern, Replacement |
8. Function with four operands
| Function | Meaning | Operand1,..,Operand4 |
| DECL | Straight-line dep. | Start value, Residual value, |
| | Percentage, Years |
9. Functions with five operands
| Function | Meaning | Operand1,...,Operand5 |
| CURC | Currency | Amount, Date, Exchange Rate Type |
| Translation | Source Currency, Target Currency |
10. Functions without operands
| Function | Meaning |
| OBJV | Read characteristic value |
If no special operands are specified, then you can specify a constant as well as the valid operands.
The operand interest rate and percentage are given as a percentage, that is 10% as 10 and not as 0.1.
With function C2DATE, E has to be specified as the end value of a period and S as the start value of a period.
Function CURC calculates the amount in another target currency. Note that small rounding differences may occur with floating-point key figures, as internally calculations are performed to nine decimal places.
The functions have a value as a result, and not a time series. In the case of depreciation it would be ideal to have a time series with the depreciation amounts. To enable this, you must complete a formula for each point in time.
The PERP function calculates the perpetual annuity from a capital according to the following formula:
Result = Key figure value * ( interest rate / 100 )
The DISC function discounts an amount according to the following formula:
Result = Key figure value / ( ( 1 + interest rate / 100 ) ** years )
The DECD function calculates a declining-balance depreciation according to the following algorithm:
1. Step: Carry out years.
Intermediate value = intermediate value + ( start value -
intermediate value ) * percentage / 100.
2. Step: Result = start value - intermediate value
The DECL function calculates a straight-line depreciation according to the following formula:
Result = start value -
( ( start value - residual value ) * percentage / 100 *
years )
Conditional statements can be realized using the IF statement. After an IF statement, any further conditional statements with ELSEIF can follow. The ELSE statement is for dealing with the remaining cases within a chain of conditional statements. The schema appears as in the following example.IF expression1. statement1. ELSEIF expression2. statement2. ELSE. statement3. ENDIF.
Expressions within the IF statement consist of operands, relational operators and logical operators. The valid operands are the same as those discussed in the section 'Expressions' plus additional variables and constants. Note that constants can only be on the right side of a relational operator. '=', '', '=', '' are relational operators. To check the special case, whether an operand oper1 has the initial value, you can write: IF oper1 IS INITIAL. or IF oper1 = '#'. In addition, there are the logical operators NOT, OR and AND for constructions such as: IF NOT oper1 oper2.
The operators CP, CO, CA, CS also exist for working with character-type operands. Operators in the expression c1 op c2 have the following meanings:
- CP (Contains Pattern):
The whole character string c1 corresponds to c2 (c1 "matches" c2). There
can be normal characters and wildcard characters in pattern c2. '*'
masks a particular character string and '+' a particular character.
- CO (Contains Only):
c1 only contains characters from the c2 character set.
If c1 or c2 is of internal type C, the full length of the field is entered in the comparison. This means that blank characters at the end are also included.
If c1 is of type STRING and is empty, the outcome of the comparison is always positive.
If c2 is of type STRING and is empty, the outcome of the comparison is always negative, unless c1 is also an empty string.
- CA (Contains Any):
c1 contains at least one character from the c2 character set.
If c1 or c2 is of type C, the full length of the field is entered in the comparison. This means that blank characters at the end are also included.
If either c1 or c2 is of type STRING and is empty, the outcome of the comparison is always negative.
- CS (Contains String):
c1 contains the character string c2. Concluding blank characters in c1 and c2 are ignored if the corresponding field is of type C.
An empty character string c2 (blank characters with type C or an empty string with type STRING) is contained in every character string c1. This includes the empty character string itself. However, no non-empty character string is contained in an empty character string c1.
Loop Constructs
DO Loops
Statements, which appear between
DO and
ENDDO are repeated as many times as required. However, the loop is interrupted by the
EXIT statement. The number of loop runs can also be controlled
DO N TIMES by a variable of type I.
N can also be replaced with an integer.
FOREACH Loops
With the
FOREACH variable statement, all values of a variable are iterated. This affects the characteristic values that exist in the transaction data of the current data object (not necessarily all values that are maintained for the characteristic). This loop construct is closed with the statement
ENDFOR. The characteristic values are processed in ascending order. If you require combinations of characteristic values, you can use the statement in the form
FOREACH Variable1, Variable2. This construct delivers the available combination of characteristic values of the current data objects.
Comments on Runtime Behavior:
Before making an entry in the
FOREACH loop, all characteristic values available in the data object are collected and sorted. Then these characteristic values are delivered into the
FOREACH loop one after the other. If a new characteristic value is generated in the loop and written to the transaction data, it is only taken into account with the next
FOREACH loop. However, the
FOREACH construct takes a lot of processor time and should be used as infrequently as possible. Always consider carefully whether your only option is to use a construct of type
FOREACH Variable1, Variable2. and not two loops as runtime behavior is normally improved in this case.
The following example illustrates a bad program:
DATA COUNTRY TYPE 0BPS_CNTRY.
DATA PRODUCT TYPE 0BPS_PRODU.
DATA FISCPER TYPE 0FISCPER.
FOREACH COUNTRY.
FOREACH PRODUCT.
FOREACH FISCPER.
{COUNTRY, PRODUCT, FISCPER} = {COUNTRY, PRODUCT, FISCPER} * 2.
ENDFOR.
ENDFOR.
ENDFOR.
The following example illustrates a better program:
DATA COUNTRY TYPE 0BPS_CNTRY.
DATA PRODUCT TYPE 0BPS_PRODU.
DATA FISCPER TYPE 0FISCPER.
FOREACH COUNTRY.
FOREACH PRODUCT.
FOREACH FISCPER.
IF {COUNTRY, PRODUCT, FISCPER} > 0.
{COUNTRY, PRODUCT, FISCPER} = {COUNTRY, PRODUCT, FISCPER} * 2.
ENDIF.
ENDFOR.
ENDFOR.
ENDFOR.
In this program example, new values are only generated if they are not equal to zero. Because the amount of values that can occur in variables PRODUCT and FISCPER does not have to be refreshed, this saves a lot of time in terms of the calculation. An update is only performed when both loops
FOREACH PRODUCT. and
FOREACH FISCPER. are executed again. If a formula generates zero values, the system does not write these back to the internal buffer. These zero values are not visible in subsequent planning functions or in manual planning. This also means that the value 0 is not assigned to existing data. Zero values do not have to be generated explicitly.
The following example illustrates the recommended programming:
DATA COUNTRY TYPE 0BPS_CNTRY.
DATA PRODUCT TYPE 0BPS_PRODU.
DATA FISCPER TYPE 0FISCPER.
FOREACH COUNTRY, PRODUCT, FISCPER.
IF NOT {COUNTRY, PRODUCT, FISCPER} IS INITIAL.
{COUNTRY, PRODUCT, FISCPER} = {COUNTRY, PRODUCT, FISCPER} * 2.
ENDIF.
ENDFOR.
Additional variants of FOREACH loops
With the
FOREACH Variable IN REFDATA you can perform iteratation for all characteristic values that are available in the reference data.
With the
FOREACH Variable IN SELECTION you can perform iteration for all characteristic values in the current planning package.
With the
FOREACH Variable IN VARIABLE Var you can perform iteration for all characteristic values for a globale variable
Var.
Issuing Messages
Messages can be issued with
MESSAGE Tnnn(class) or with
MESSAGE Tnnn(class) WITH operand1 ... operand4.
T is the type of message. 'E' = error, 'I' = information.
nnn is a three-figure number.
class is the message class. Optionally, a maximum of 4 operands can be entered additionally. Operands can either be variables or strings enclosed in quotation marks. If you trigger a type 'E' error, then the planning function results are not transferred into the internal transaction data buffer.
The messages are gathered and displayed after executing the function. If you edit plan data, the messages are only displayed, if at least one type 'E' error is triggered. Otherwise the display does not occur.
It is best if you create your own message class for error messages, and do not use any of the messages that are delivered by SAP. Note that you must transport your own message class from the test system into the production system.
Calling Function Modules
You use the CALL FUNCTION statement to call function modules. The names of the function modules have to be entered in table RSPLF_FDIR. You can do this in transaction SM30. In the following example, function module UPF_DISTR_RATE_GET is called.
DATA FISCPER TYPE 0FISCPER.
DATA FISCYEAR TYPE 0FISCYEAR.
DATA RATE TYPE F.
DATA KYF TYPE KEYFIGURE_NAME.
FOREACH FISCYEAR, KYF.
FISCPER = OBJV( ).
CALL FUNCTION UPF_DISTR_RATE_GET
EXPORTING I_FISCPER = FISCPER
I_VERSION = 'OPTIMISTIC'
IMPORTINGE_RATE = RATE.
{KYF,FISCYEAR} = { KYF, FISCYEAR } * RATE.
ENDFOR.
EXPORTING, IMPORTING, and CHANGING parameters can be transferred to function modules. The parameters have to have simple types (F, I, D, STRING and types of characteristics and attributes). Class references, structures and some table parameters are not permitted. All non-optional IMPORTING parameters for a function module have to be filled. If the function module raises an exception, you have to work with construct MESSAGE .... RAISING. THis is shown in the following example. The messages are transferred to the log. Class-based exceptions are also not permitted.
FUNCTION UPF_DISTR_RATE_GET.
*"--------------------------------------------------------------------
*"*"Local Interfaces:
*" IMPORTING
*" REFERENCE(I_FISCPER) TYPE /BI0/OIFISCPER
*" REFERENCE(I_VERSION) TYPE STRING DEFAULT 'OPTIMISTIC'
*" EXPORTING
*" REFERENCE(E_RATE) TYPE F
*" REFERENCE(E_FISCYEAR) TYPE /BI0/OIFISCYEAR
*" EXCEPTIONS
*" ERROR
*"--------------------------------------------------------------------
DATA: l_fiscper_3 TYPE n length 3.
l_fiscper_3 = i_fiscper+4(3).
IF i_version = 'OPTIMISTIC'.
e_rate = l_fiscper_3 / 5.
ELSE.
e_rate = l_fiscper_3 / 7.
ENDIF.
e_fiscyear = i_fiscper(4).
if l_fiscper_3 is initial or e_Fiscyear is initial.
message e001(upf) with 'Initial Values'(TIV) raising error.
endif.
ENDFUNCTION.
Examples
Price Planning
In this example the prices are stored in the transaction data. Characteristics to be changed are key figure name, version (0VERSION ) fiscal year (0FYEAR ) and customer (0CUSTOMER ). It is planned in version 1. Prices are stored in version 2. The records have the special feature that the characteristics Customer and Fiscal Year have the value "non assigned", and must therefore be included in the quantity of characteristics to be changed. Otherwise, for every object to be planned in version 1, there has to be an object in version 2 that can be used to determine the price. A message is issued if no price is planned for an article. The calculation is only executed if the planned combination {QUANTITY,1,FYEAR,CUSTOMER} is greater than 0.
DATA CUSTOMER TYPE 0CUSTOMER.
DATA FYEAR TYPE 0FYEAR.
DATA ARTICLE TYPE 0ARTICLE.
IF {PRICE,2,#,#} = 0.
ARTICLE = OBJV( ).
MESSAGE I001(/SEM/003) WITH ARTICLE.
ELSE.
FOREACH FYEAR, CUSTOMER
IF {QUANTITY,1,FYEAR,CUSTOMER} > 0.
{REVENUE,1,FYEAR,CUSTOMER} = {QUANTITY,1,FYEAR,CUSTOMER} * {PRICE,2,#,#}.
ENDIF.
ENDFOR.
ENDIF.
You may expect the characteristic Article to appear in the list of characteristics to be changed, even though the prices used for the calculation refer to Article. Using the Article characteristic as an example, the following is true:
DATA CUSTOMER TYPE 0CUSTOMER.
DATA FYEAR TYPE 0FYEAR.
DATA ARTICLE TYPE 0ARTICLE.
FOREACH ARTICLE, FYEAR, CUSTOMER.
IF {PRICE,2,#,#,ARTICLE} = 0.
MESSAGE I001(/SEM/003) WITH ARTICLE.
ELSE.
IF {QUANTITY,1,FYEAR,CUSTOMER,ARTICLE} > 0.
{REVENUE,1,FYEAR,CUSTOMER,ARTICLE} = {QUANTITY,1,FYEAR,CUSTOMER,ARTICLE} * {PRICE,2,#,#,ARTICLE}.
ENDIF.
ENDIF.
ENDFOR.
The characteristic is superfluous to the formula as the values of the characteristic are not changed. When calculating the planned revenue, the same article is referred to on both sides of the relational operator. Under these circumstances SAP advises
against including the characteristic in the quantity of characteristics to be changed, as it is not required for the calculation, but negatively affects performance when the values are read. Another way to accelerate the formula is to save the value of the element {PRICE,2,#,#} in an auxiliary variable and work with this. If the value of a characteristic is required to issue error messages or determine attribute values, the value can be read by function OBJV().
You can make the formula even shorter by accessing the reference data
explicitly. In this case there are no characteristics to be changed except for the key figure name. The formula is run through the data record. A FOREACH loop over the characteristics to be changed is not necessary.
DATA ARTICLE TYPE 0ARTICLE.
IF {PREIS | 0VERSION = 2, 0GJAHR = #, 0CUSTOMER = #} = 0.
ARTICLE = OBJV( ).
MESSAGE I001(/SEM/003) WITH ARTICLE.
ELSE.
ERLOS = MENGE * { PREIS | 0VERSION = 2,0GJAHR = #,0CUSTOMER = #}.
ENDIF.
Price Planning with Prices in Master Data
How does price planning work when the price is stored in the master data? You use function OBJV to get the value of the article. The value of attribute 0PRICE is read using function ATRV. The ATRV function includes 2 arguments, in its simplest form. The first argument has to be the name of a master data attribute; the second argument has to be a variable. The value of the attribute is read using the variable value in the master data table. Two cases have to be distinguished with
Formula compound characteristics:
- If the higher-level characteristics are fields to be changed, the values of these characteristics have to be specified for the function in the form of variables. In this case, the number of arguments for the function complies with the number of field to be specified.
- If the higher-level characteristics are not contained in the fields to be changed, values are filled automatically from the values for the current package. In the following example, only the key figure name is a characteristic to be changed.
DATA ARTICLE TYPE 0ARTICLE.
ARTICLE = OBJV().
REVENUE = ATRV( '0PRICE', ARTICLE ) * QUANTITY.
Proportional Planning
Characteristics to be changed are key figure name, version (0VERSION) and (0CUSTOMER).
DATA TOTREVEN TYPE F.
DATA TOTQUANT TYPE F.
DATA CUSTOMER TYPE 0CUSTOMER.
FOREACH CUSTOMER.
TOTREVEN = TOTREVEN + {REVENUE,2,CUSTOMER}.
TOTQUANT = TOTQUANT + {QUANTITY,2,CUSTOMER}.
ENDFOR.
FOREACH CUSTOMER.
{REVENUE,1,CUSTOMER} = {QUANTITY,1,CUSTOMER} * TOTREVEN /TOTQUANT.
ENDFOR.
Copying with Condition to the Value of a Key Figure
The characteristic to be changed is the version. The key figure name is selected as the characteristic for conditions. REVENUE is selected as the value for key figure names. The following formula copies the revenue from version 1 to version 2, if the revenue in version 1 is greater than 500. In this example, we cannot use the copy planning function as no conditions can be formulated for key figure values. Normally it is more beneficial to use special planning functions instead of formulas, as these are implemented more efficiently.
IF {1} > 500.
{2} = {1}.
ENDIF.
Copying with Condition and Iteration Using Key Figure Name
Characteristics to be changed are key figure name and version. In contrast to the example above, all key figures are now copied to version 2 if the revenue is greater than 500. So that you do not have to write the assignment for all key figures, we define a RATIO variable of type KEYFIGURE_NAME, and iterate it for all key figures in the planning level using the
FOREACH construct.
DATA RATIO TYPE KEYFIGURE_NAME.
IF { REVENUE, 1 } > 500.
FOREACH RATIO.
{ RATIO, 2 } = { RATIO, 1 }.
ENDFOR.
ENDIF.
Checking Data for Plausibility
Characteristics to be changed are key figure name, version and article. We check whether the planned revenue achieves at least 90 percent of the revenue of version 1. If the revenue falls below the boundary, a warning is issued.
DATA ARTICLE TYPE 0ARTICLE.
DATA MINERLOS TYPE F.
FOREACH ARTICLE.
MINREVENUE = { ERLOS, 1, ARTICLE } * 0.9.
IF { REVENUE, 2, ARTICLE } < MINREVENUE.
* The planned revenue for article &1 fell below the predefined minimum revenue
MESSAGE I034(ZSEM) WITH ARTICLE.
ENDIF.
ENDFOR.
Rolling Planning
Characterisics to be changed are version and fiscal year/period. The actual data of the current period is copied into the plan version. The difference is distributed over the remaining periods. The current period is determined from a variable.
DATA CURRPER TYPE FISCPER.
DATA FISCPER TYPE FISCPER.
DATA SUM TYPE F.
DATA DELTA TYPE F.
* Read periods from the variables with technical name PERIOD
CURRPER = VARV( 'PERIOD' ).
* Get total on weighting
FOREACH FISCPER.
IF FISCPER > CURRPER.
SUM = SUM + { 1, FISCPER }.
ENDIF.
ENDFOR.
* Determine delta between plan value and actual value
DELTA = {1, CURRPER} - {0,CURRPER}.
* Set plan value to actual value
{1,ACTPER} = {0, ACTPER}.
* Distribute delta weighted
FOREACH FISCPER.
IF FISCPER > CURRPER.
{1,FISCPER} = {1,FISCPER} + DELTA * {1,FISCPER} / SUM.
ENDIF.
ENDFOR.
Depreciation
Characteristics to be changed are key figure name and fiscal year. The value of key figure 0AMOUNT is calculated for 5 years. The cost price is 1000. Residual value is 100. Depreciation percentage rate is 20 percent. Straight-line depreciation is carried out. In this example the
depreciation function is not the focus; the TMVL funtion is. As its first argument, the function has the value of a time characteristic. As a second argument it has an offset. The offset specifies which next larger value the function is to deliver. You can also transfer negative offsets. Then the corresponding smaller values are delivered. You cannot perform the same function with a
FOREACH loop, as all years do not have to be available in the transaction data.
DATA YEARS TYPE I.
DATA FYEAR TYPE 0FISCYEAR.
FYEAR = VARV( 'CURRYEAR' ).
DO.
YEARS = YEARS + 1.
FYEAR = TMVL( FYEAR, 1 ).
{0AMOUNT, FYEAR} = DECL( 1000, 100, 20, YEARS).
IF YEARS = 5.
EXIT.
ENDIF.
ENDDO.
Calculating with Data Type 'D'
You can also perform calculations with data type 'D'. A short example is now given. In the
FOREACH loop, the the date of the first day in FISCPER is calculated in D1, and the last day in the FISCPER is calculated in D2. The difference between D1 and D2 is formed in I1 and two days are subtracted. I1 has type I. If the data specified in D1 and D2 are invalid, the result is 0. When you perform calculations with type D fields, the constant operands are checked to make sure that the dates specified are valid. Therefore, you can write
I1 = D2 - D1 - 2., but an auxilliary variable I2 has to be used.
DATA D1 TYPE D.
DATA D2 TYPE D.
DATA I1 TYPE I.
DATA I2 TYPE I.
DATA FISCPER TYPE 0FISCPER.
FOREACH FISCPER.
* Calculate 1st day of FISCPER
D1 = C2DATE( FISCPER, S ).
* Calculate last day of FISCPER
D2 = C2DATE( FISCPER, E ).
* Calculate the difference between last and 1st day minus two days
I2 = 2.
I1 = D2 - D1 - I2.
MESSAGE I001(UPF) WITH 'DIFFERENCE' I1.
ENDFOR.
Working with Character Strings
In the following example. the system checks whether the characteristics fiscal year/period (0FISCPER), fiscal year (0FISCYEAR) and the three- figure posting periods (0FISCPER3) are filled consistently. The fiscal year/period characteristic is seven characters long. The first four characters contain the fiscal year. The posting period is stored in characters five to seven. Using function SUBSTR (characteristic value, offset, length), the appropriate values are read from the value for fiscal year/period. If the characteristic value for fiscal year/period is initial, it is filled with the concatenated value of fiscal year and posting period. The
CONCAT function is used.
DATA FISCPER TYPE 0FISCPER.
DATA FISCPER_CMP TYPE 0FISCPER.
DATA YEAR TYPE 0FISCYEAR.
DATA PER TYPE 0FISCPER3.
DATA PER_CMP TYPE 0FISCPER3.
DATA YEAR_CMP TYPE 0FISCYEAR.
DATA KYFNM TYPE KEYFIGURE_NAME.
YEAR = OBJV( ).
PER = OBJV( ).
FISCPER_CMP = CONCAT( YEAR, PER3 ).
FOREACH KYFNM, FISCPER.
IF FISCPER IS INITIAL.
{KYFNM,FISCPER_CMP} = {KYFNM,FISCPER}.
ELSE.
PER_CMP = SUBSTR( FISCPER, 4, 3 ).YEAR_CMP = SUBSTR( FISCPER, 0, 4 ).
IF YEAR YEAR_CMP OR PER PER_CMP.
* Error message
MESSAGE E021(/SEM/003) WITH YEAR YEAR_CMP PER PER_CMP.
ENDIF.
ENDIF.
ENDFOR.
Testing Planning Functions
In this section, you find notes on how you test planning functions systematically and understand how they work.
1. You create packages at the level of target data (data, which was generated or changed), reference data (if the function requires reference data) and a package, which contains target and reference data.2. Restrict the packages so that only one or two records are in the target and reference data. It is difficult to analyze a function, which changes 50,000 data records.3. Create layouts for every package. The layouts have the characteristics to be changed in the lead columns, the key figures in the data columns, and the remaining characteristics in the header area. In this way, you can check whether reference data exists for every object in the target data. It is often overlooked that the values of the unit fields in the reference data do not match with the units in the target data.4. You can issue runtime information using the MESSAGE statement for planning functions of the type formula. If you want to get to the debugger, you can write the BREAK-POINT statement into the formula. When executing the parameter group, you get to the debugger and see the generated ABAP/V coding.5. For the messages in the log, you have the possibility to display details on a message. Press the detail button, then the system displays a dialog field in which you see the selection conditions for the records where the message was triggered. It concerns the quantity of records whose characteristic values are the same regarding the characteristics, which are NOT selected as characteristics to be changed.6. If you are executing the planning function in the trace, you receive the following information:
a) With which data object a function was executed. You can navigate to a detail screen from the data object display.
b) On the detail screen you can see records before and after the planning function was executed.
c) If reference data was required, you also see the reference data that was read. Reference data then has to be read in if it does not exist in the active planning package. Some functions such as 'delete data', do not require reference data.
d) Alternatively you can branch to the display with messages that belongs to the data object.
http://help.sap.com/saphelp_nw70/helpdata/EN/43/1dfeff41130bd5e10000000a422035/content.htm
