User Exit Macro for Demand Planning

October 4, 2005 by admin

Users face numerous complex situations in the planning process, such as adjusting a key figure value in the planning book or working out one key figure value to calculate another. They can easily use the standard macro functionality to achieve this. But there might be more complex scenarios where use of standard macros may not achieve results. One example is finding out the order number for a cell value from live cache and using it in alerts or adjusting the forecast figures by using a complex formula which cannot be accommodated in standard macros. Writing a user exit macro is one of the solutions to overcome this. The grid structure of the planning book is transferred in the user exit macro. The cell value of a key figure can therefore be read from the grid structure and used in various ways to complete the gaps.

Steps to create a macro

The following example explains how to develop a user exit macro based on a given set of circumstances. Imagine an automotive plant where a buyer has issued a large number of purchase orders for a component and uses SAP Advanced Planning and Optimization for planning. Standard SAP APO alerts are generated if there is any delay by the vendor. But the buyer does not know which purchase order (PO) is due. He needs to get the number of the PO which is due in the alert. To do this a user exit macro can be used. In this example the production plant code is 1200, the destination area warehouse codes are 1300 and 1400 and the product codes are 100-500 and 100-501.
To develop the user exit macro a whole series of steps have to be completed. It is assumed here that mySAP SCM version 4.0 and above is being used. The process starts at the macro workbench by using the path Demand Planning – Environment – Current settings – Macro Workbench. Then the planning book is selected for which the user exit macro needs to be developed. Once the macro workbench is entered (by selecting the macro workbench button), the user exit macro button should be dragged and dropped from the top left hand side window into the working area (bottom section of the middle window).The macro should be given an appropriate name. If a large number of user exit macros are to be developed, the name needs to be carefully selected as this drives the execution of code as explained in the next section. The macro should be saved. This occurs when exiting the workbench and the user exit project activated by using enhancement component ‘APODM005’. This calls up a user exit function module “EXIT_/SAPAPO/SAPMMCP6_005”.

Parameters

I_MACRO_NAME: This will convey the name of the macro and help control which part of the code is used. If a large number of user exit macros have been developed for different planning books, verify the execution of relevant code in the exit function module by checking the name of the user exit macro.
E.g. if I_MACRO_NAME = ‘XYZ’.
Code….
Elseif I_MACRO_NAME = ‘ABC’.
Code….
Endif.

Tables

Internal table details in debugging mode

Internal table details in debugging mode

T_I_PLOB_VALUES: This table contains the values of the data selection. Typical values may be seen for a drilled down book in debugging mode. Double click in the column ‘CHAR_VALUES’ TMP00000….1. The source location values will appear (if the corresponding characteristics have been selected). In the example explained above, the character 9ALOCNO has been selected as a source location and its value is 1200.
Now double click on ‘Selection’. It will display the characteristics and corresponding values as far as they have been drilled down. The following values will be shown:
9AVERSION: 000, 9AMATNR: 100-500, 9AMATNR: 100-501, 9ALOCNO: 1200.

T_I_LINES

Values for different tags

Values for different tags

The row coordinates of a particular cell can be obtained from the T_I_LINES table. The important fields are LINE, KRIT1, and FELDH. In row one the tag TMP00000000000001 for key figure 9AADDKF1 is shown in line 1, which means row 1 represents aggregated values for location 1200. Similarly, line 42 represents tag TMP00000000000002 for key figure 9AMANUP. This means row 42 has aggregated values for location 1200 and destination location 1300. The row coordinates for the cells can therefore be derived.

T_I_COLS

This table represents the column coordinates of a particular cell. In the example selected, if the value of column ‘COLUMN’ is 156 and its corresponding column ‘PERDY’ shows the value ‘W 21.2005’, this means that week 21 of 2005 is in column 156. Likewise, the column number for week 33 of 2005 is 168. Thus the column coordinate for the cell is known.

T_C_TAB

The T_I_LINES & T_I_COLS tables represent the row and column coordinates for the cell. The cell value in the table T_C_TAB can be derived for given row and column numbers of the cell. Column Z represents the row number, C represents the column number and V represents the value of the cell.
The next example is from the T_C_TAB table and will be tracked to the planning book. Take the first entry from the above table.
The values for the different fields are: Z = 1, C = 127 and V = 340. The week for column 127 has to be found from the T_I_COLS table.

Typical planning book view

Typical planning book view

For column 127 the week value is W 45.2004. In the T_I_LINES table, line one represents key figure 9AADDKF1 (‘Final forecast after manual adjustment’) for TMP00000000000001. From figure no. 1 part 2 it is clear that TMP00000000000001 represents the aggregate value for location 1200. The planning book is consulted and the result is verified. From figure 2 it can be seen that the planning book value is 340 for week 45 of 2004 and the aggregated value 1200. The value can therefore be freely manipulated. If the value of a cell for week 50 of 2004 and for product 100-501 needs to be modified, the T_I_PLOB_VALUES table can be searched to find out the value of the “TMP” table for disaggregation and then a search can be performed in the T_I_LINES & T_I_COLS tables.
The above discussion shows that key figure values can be read from row and column coordinates. Once the key figure value is derived, further calculations can be performed or further details can be ascertained as desired. In the example of the automotive plant, the buyer can find out PO numbers by using the appropriate function modules to extract the data from live cache. He can then use this data in alerts or in any other way he wishes.

Vinayak Oak

Vinayak Oak

Tags: ,

1 comment

  1. Udit Agarwal

    Sir,
    I have a requirment, in which i need to capturein my macro-exit the cell coordinateds (row*column) for the cell which user is selecting, along with the location and prodict and the bucket.
    Note:- The data is driiled down at the location and product level (thus we have multiple rows for a single key figure).
    Kindly guide how to capture these values.

    Thanks

Leave a Reply