Market Rate Upload Utility
Authors of this document
Piyush Agarwal, firstname.lastname@example.orgemail@example.com (SAP HCM Functional Consultant)
Carol D’Sa, firstname.lastname@example.orgemail@example.com (SAP HCM Technical Consultant)
IBM India Pvt. Ltd., India.
Purpose and Contents of this Whitepaper
The basis for this whitepaper is a Proof-of-Concept (PoC) in the SAP HCM – Job Pricing Model. It was done for a customer who manages through the SAP system, his worldwide Market Rate updates for each year as part of year-end salary planning.
This whitepaper shows how to upload the Market Rate updates of an entire country through a single file through an automated process, negating the need for single entry manual validations and manual data upload.
This whitepaper consists of the following sections:
Table of Contents
- Introduction. 3
- How Standard SAP Job Pricing Model works. 3
- 2.1. Market Rate update process. 3
- 2.1.1. Creation of Salary Adjustment Version. 3
- 2.1.2. Upload of new market rate. 5
- 2.2. Verification of upload at table level 7
- Demonstration of Upload Utility for Year End Market Rate updates. 7
- 3.1. Background to develop utility. 7
- 3.2. Adjustment Reason. 8
- 3.3. Limitation of BSP page. 8
- 3.4. Other Challenges. 8
- 3.5. Develop Upload Utility. 8
- 3.6. File processing and Validations. 10
- 3.7. Execute Standard Report and update T710 master table. 11
- References. 12
The underlying PoC was done for a customer who manages his HR worldwide through a single system. Every year, as a year-end activity, Market Rate updates files are prepared by the business in excel sheets and forwarded to the IBM AMS Team for update to SAP since these are customization changes.
IBM Team validates these files manually and enters the market rate updates values into the T710 master table through manual uploads, using cluster table V_T710CL.
This manual activity of file validation and data entry takes up at least 200 hours of effort every year.
Our main goal was to reduce the manual work and automate the process.
2. How Standard SAP Job Pricing Model works
Market Rate-2015 update by SAP Job Pricing model
Test Case – Testing system – HRA
Country Grouping – India ,Pay Grade type- 01(salaried), Pay Grade area –Q1(Quality), Pay Grade -6 and level -10
Market rate before update
2.1. Market Rate update process
2.1.1. Creation of Salary Adjustment Version
Path: SAP Menu-HR-PM-CM-ECM-JP, or with Tcode: PECM_START_JPR_BSP
Now Planning for New salary Structure
Clicking Planned Salary Structure and click on create market rate button
Enter percentage value to be increased for Min, Mid and Max and pressing Re-calculate button (For testing we are taking percentage as 10%)
Selecting the line and clicking recalculate button for computation of new market rate.
New market rate increased by 10%.
For e.g. Previous min value =520,338.00 after 10% incr. new min value = 572,371.80
Selecting the multi-line and using button ‘Save’ and providing Adjustment version name and start date.
Once above values are submitted, these are stored in SAP Temporary table T71JPR25.
The above adj. version can be used at country grouping, Pay scale type and payscale area level and this is a front-end activity which as per Standard process should be taken care of by Compensation specialist (For testing – we have used Country grouping)
2.1.2. Upload of new market rate
Once the data is updated to the above table through BSP page, the below standard SAP program is used for updating the market rate to the cluster table V_T710CL.
SPRO->Personnel Management->Enterprise Compensation Management->Job Pricing->Pay Grades and Levels->Update Pay Grade Amounts from Market Data (TCODE: PECM_ADJ_SAL_STRU)
This report will give a comparison of existing values in table T710 and the temp table T71JPR25.
Data updated successfully
2.2. Verification of upload at table level
Update is successful
A configuration in the cluster table should already be available for this pay grade structure. A new pay grade structure configuration cannot be created through this standard program, and manual configuration should be done.
3. Demonstration of Upload Utility for Year End Market Rate updates
3.1. Background to develop utility
Development of Utility which would update the market data from XLS sheet to the SAP temporary table -T71JPR25 (table to store the new market) along with T71JPR27 and T71JPR27T (table to store the adjustment reason master data).
Analysis of the ST05 trace on the standard processing of Job Pricing BSP page and Standard report PECM_ADJ_SAL_STRU, identifies 3 Sap temporary tables T71JPR25, T71JPR27 and T71JPR27T, which are updated by the BSP page. These tables are created for use only in this BSP-Job Pricing feature of SAP.
The Custom utility is designed to utilize these tables so that the standard report-tcode PECM_ADJ_SAL_STRU can read the tables T71JPR25, T71JPR27 and T71JPR27T, and proceed with T710 upload.
3.2. Adjustment Reason
The unique system identifier generated identify upload combination.
This Step of utility report is similar to step performed BSP page as demonstrated in the Standard Process document with following limitation –
BSP page update is based on combination of Country grouping, pay grade type, pay grade area, pay grade band. For E.g. In below screenshot:
In the BSP page we need to create 5 unique adjustment reason for row nos. 2, 3-4, 5-9, 10-12, 13.
3.3. Limitation of BSP page
Since above combination through file entry is in bulk, it is not feasible to create separate adjustment reasons for each entry.
3.4. Other Challenges
The Data Sheet received from Business for upload contains following issues-
- Duplicity for rows containing same market salary / pay grade structure – The pay grade structure is same but market salary may be same or different in repeated rows.
- Mismatch of currency as already available in system tables (T710).
- New configuration of pay grade structure
3.5. Develop Upload Utility
To overcome the limitation of BSP page and other challenges – an upload utility is designed with below listed validations, a 2 step process is designed where the utility should first perform check on duplicity or mismatch of input file and in second step validation and upload to table. Until the input file is error-free, it would not be updated/inserted into the above mentioned 3 tables.
Report Name – ZHPY_IINT_SAL_STRUC_ADJ_REPT
Input parameter –
- 1. Input File
- 2. File validation option
- 3. Country Grouping
- 4. Salary Adjustment Version
Test data load file (template for data load):
3.6. File processing and Validations
When the utility is executed after providing the input file and country grouping, following validations are performed and error is displayed along with error message.
- 1. Country Grouping provided on screen must match with one in input file.
- 2. Currency Key Validation in excel sheet as available in TCURC table.
- 3. Read table T710A to validate availability of following Key fields – country grouping, payscale type, payscale area, band, level, payment frequency and currency.
- 4. Checking for duplicate entries with following fields together as key fields- country grouping, payscale type, payscale area, band, level as there might exist duplicate in T71JPR25 table.
- 5. Amount should be input in 111,111.11 format or 111111.11 i.e. decimal separator as ‘.’; and thousand separator ‘,’. (For countries with no decimal values e.g.: JPY input format would be 111,111 or 111111). However the decimal separator if input should always be ‘.’ this is necessary as the amount has to be converted to internal sap format and the input format should be fixed.
In case of any of these errors, error message is generated else the file proceed for upload in T71JPR25, T71JPR27 and T71JPR27T.
3.7. Execute Standard Report and update T710 master table
Once the Data is updated in T71JPR25, T71JPR27 and T71JPR27T, the standard utility at tcode PECM_ADJ_SAL_STRU is used to update T710 table.
Job Pricing Model in SAP