3 March 2022

Automate and Speed Up Monthly Close Process in SAP with UiPath

3 March 2022

Automate and Speed Up Monthly Close Process in SAP with UiPath

⬅  SEE ALL THE ARTICLES

 

Introduction

 

In this article you can identify use cases to introduce UiPath based automation to your accounting close processes. Typical accounting close process in SAP span over multiple days with repetitive processes, which sometimes are error-prone and dependent on humans. Read further about use cases in a typical month end schedule. As we progress you'll uncover tips and tricks and lessons learned throughout the development life cycle. 

 

Requirements 

 

This blog is written with SAP-ECC 6.0 processes, but most of the content should remain the same for S/4 HANA. 

Let's briefly talk about a typical accounting close schedule which could be monthly, quarterly, annually, or a specific schedule as applicable to an industry requirement. Most of the tasks generally remain the same in these schedules and vary around data, volume, frequency, level of reconciliation, etc.

 

I generally segregate the schedule into four buckets: 

 

  1. 1. (Day -n, Day 1) Preparation tasks, like opening calendars, maintaining monthly exchange rates, notification to broader team with closing schedule etc. 
  2. 2. (Day 2, Day 3) Core transactions and postings (manual or systematic), some interim reconciliation etc. 
  3. 3. (Day 4, 5, final day -1) Core transactions and postings (manual or systematic), final reconciliations etc. 
  4. 4. (Final day) Run financials (trial balance and other financial statements), close accounting period and communication. 

These tasks involve both manual and systematic activities, with some being hybrid.

 

Now that we have a definition of a close schedule, the first thing to do is identify tasks that can be truly automated. I strongly suggest having a working session with the business in charge of the close schedule (typically corporate controller or accounting manager) to go over the process and systems involved in the close cycle. Tip: I generally start with tasks where humans don't add much value and simply execute the mechanical steps in the system. Those tasks which are truly rule-based and don't change month over month.

 

 

Disclaimer

 

Ithis article, I'll share use cases from my experience automating in each of these categories. The purpose is to give you an idea on how UiPath can help to automate mundane, rule-based tasks from a monthly schedule and not limited to these use cases. The close schedule and requirements may vary from industry to industry, but the concepts discussed in this article should apply in general.

  •  
  • Use cases

  •  
  • 1. Publish close calendar 

Organizations handle the close schedule (update and publishing) in different ways based on their requirements and system landscape. Some use SAP Closing Cockpit (or similar) and some companies maintain it in Excel, PDF, and publish it. For this article, I am going to cover schedules maintained in Excel and distributed as email communication.

Most of the close schedules are repetitive except for adjustments to start and end dates based on calendar month. For example, if it is a seven day closed cycle, with a start as day two and ends on day seven of the month, day two is generally the second working day before the previous month's end date, and day seven is the 7th working day of the current month.  Here's the sample view of the close schedule (for January'2022 close). 

 

Picture1

 

To extend this schedule for February close, tasks are generally kept the same as January close but adjust the dates according to February calendar.  

As you can imagine, this is pretty rules-based task to adjust Excel file with some formula on "Date" column to get end of the month date and accordingly subtract working days to get to the first task of the schedule. And extend the formula to all the rows accordingly, before publishing it out. Once file is ready, email it to the distribution list for all the process owners involved in the close cycle.  

UiPath can easily help achieve this automation. The deployment can be attended or unattended, depending on the requirements and preferences of the business users. This is a simple UiPath Studio development with commonly used activities and pseudocode such as: 

  1. a) Calculate Dates 
  2.  
  3. Calculate the current month end date and day of the week as an example in the code below. 

Picture2

 

Picture3

 

Based on the day of the week, decide how many days to subtract to get to day two (just an example if that's the first task of the month). This can be a switch statement with a couple of conditions around the day of the week, something like in the screen shot below. 

 

Picture4

 

Calculate other dates accordingly for the close days with variables for day two, day one, day zero, and so on, as in the example below. 

 

Picture5

 

Please note to adjust days (AddDays) as per your requirement. Above is just a screen print to give you an idea. 

Please refer UiPath Documentation Portal and UiPath Academy for further insights to handling date and time.  

 

b) Once you have dates calculated, read prior month close schedule available in Excel, using Excel Application Scope and write cells back with a calculated date as applicable.  

 

See sample code below

Picture6

 

Picture7

 

Picture8

 

 

The February close schedule is updated now in the screen shot below. 

 

Picture9

 

There's UiPath Academy course on Excel automation. Explore the tutorial for further details.

 

c) Send email messages with PDF attachments (standard UiPath activities based on the mail server you want to use).

 

Deployment Options 

 

Attended bot: this can be an attended bot on end user machine to run when ready to prepare the close schedule using UiPath Assistant.  

Unattended bot: this can also be deployed as an unattended bot running on a remote machine as a scheduled trigger. The trigger could be a fixed day, time of each month. 

 

  1. 2. Open and close accounting calendars 

In SAP, we maintain three accounting calendars that are relevant for month end transactions as well as daily activities that hit the general ledger.

 

  1. 1. FI (Financial Accounting) periods: these periods allow or disallow postings to a general ledger each month and are generally handled in a controlled environment with restrictions on who has access and authority to open and close periods. This is an important step in a close routine to timely, open and close accounting periods to avoid any overlap of transactions from one month to another. 

SAP provides standard transaction code (OB52) to open and close accounting periods. This setting is performed at "Posting Period Variant" level, which is generally based on legal entity. Please check with your functional analyst (or accounting in charge for this process) on how it's setup to identify potentials for automation.

I have seen posting period variants configured for each company code (like 1:1) to allow flexibility to open and close periods based on the schedule at company code level.

As you can imagine, it's time-sensitive tasks which sometimes act as "critical milestone" also to keep the calendars as accurate and timely as possible, especially in a global environment with multiple time zones. That's where it comes across as a decent use case for unattended automation.

 

There are multiple ways to automate it. It depends on the business requirement and whether accounting functions are centralized at corporate level or decentralized per legal entity. Please consult business manager before proceeding into it.  

 

SAP transaction code: OB52 (program name RFOB5200)

 

Generally, there's a variant defined for this screen with preset values for the fields that are static and then maintain month-dependent fields as applicable (for example, account type and account are static, and period 1/2 will change each month). Please consult business to identify the variant and the requirement for the period-dependent fields.

 

Tip: if there is no variant used in the current process, I recommend setting it up in working with a business and using it directly in automation. Please note down the variant name.

 

Let's talk about UiPath automation as the next step. UiPath provides standard activities to work with SAP programs and transaction codes. Please refer to my blog on "How RPA Developers Can Kick Start SAP Automation" for further details. 

As a snippet from that blog post, UiPath provides the standard package UiPath.UIAutomation. Activities with activities to help automate SAP processes. I highly recommend installing the latest version of this package in Studio and exploring the available SAP activities. To install a package, click on Manage Packages, All Packages, and type this package name in the search field. Select the package and click Install from the side panel.   

Here's the pseudocode and sample code for the automation: 

(In this sample code, I am changing the calendars on the first of the month to open a new month while still allowing closing activities in the previous month) 

  1. a) Define variables 

 

current date = datetime.Today.ToShortDateString 

prevMonthDate = convert.ToDateTime(currentDate).AddMonths(-1).ToShortDateString 

current month = Convert.ToDateTime(current date).ToString("MM") 

fromPeriod = convert.ToDateTime(prevMonthDate).ToString("MM") 

fromYear = convert.ToDateTime(prevMonthDate).ToString("yyyy") 

toPeriod = currentMonth 

toYear = Convert.ToDateTime(currentDate).ToString("yyyy") 

 

Picture10

 

b) Call transaction (SA38) and Type into program RFOB5200 and execute (hot key F8 or using Activity Select menu Item). 

 

Picture11

  1. c) Get variant defined on SAP side (this will pre-populate some of the fields) 

    In the pop-up window, use "Type Into" for the variant already saved.  

    SAP pop-up screen: 

Picture12

 

UiPath Type Into and Set menu Item activities to Enter variant and select Enter on the pop-up: 

 

Picture13

 

d) For period one and period two section, use "Type Into" Activity to Enter period and fiscal year as applicable. 

 

Picture14

 

Similarly, map the variables to Period two section as applicable.  

 

  1. e) Execute the transaction 

Now that all the required fields are filled in, execute the program using "Select menu Item" Activity.

 

Picture15

 

f) Validate the results in table T001B for successful change in periods

Once it is executed, I generally validate the results in table T001B just to confirm that periods are changed successfully. To do that, we can add another set of activities in the same workflow to call transaction 'SE16n', type into table T001B, Enter FRPE1 (from period), FRYE1 (from year) and any other field that's relevant for selection (based on the fields entered above), execute it (hot key F8 or Activity Select menu Item). If an entry is found (one way to check is data scraping), that means calendars are changed successfully. 

 

  1. g) Send email notification  

Based on the confirmation, workflow email can be configured to send notification to business users announcing changes in FI calendars.

  1. 2. CO (Controlling) Periods: Controlling (CO) periods also follow in conjunction with FI periods to lock/unlock plan and actual business transactions for a combination of controlling area, version, fiscal year, and period. Concept and intent remain the same as FI periods, to prevent further postings in locked and reconciled periods.  

SAP transaction code OKP1 (program Name: RKCOOKP1) 

I recommend using a direct program for this automation as it allows to set period locks in batches. Please note that controlling periods can be lock/unlock for a specific business transaction. The design and automation approach may vary based on the requirements. On the selection screen of this program (RKCOOKP1), there are two processing options – Set Lock (to close a period) and Delete Lock (to unlock a period). There may be a variant for each processing option. (Please consult business user before proceeding with the development. 

Here's the pseudocode and sample code for the automation: 

(In this sample code, I am assuming that all future periods have a lock on them. Automation will set lock on previous month period and unlock current month period) 

  1. a) Define variables—please refer to the "Define variables" section discussed under FI Periods. The variables used for this workflow can be the same as in the FI Periods workflow.  

b) Call transaction (SA38) and Type into program RKCOOKP1 and execute (hot key F8 or using Activity Select menu item) – please refer to call transaction steps discussed under FI Periods. It remains the same, except for changing the program name.  

  1. c) Get variant defined on SAP side (this will pre-populate some of the fields) 
  2.  
  3. To close a period, get the Set Lock variant. 
  4. To open a period, get the Delete Lock variant. 

 

Controlling Area, and Version can be present in the variant, if yes, no need to Enter again 

UiPath Type Into and Set menu Item activities to Enter variant and select Enter on the pop-up. 

In the below screen prints, I am locking a period first followed by unlocking.  

Variant: LOCKPERIOD 

 

Picture16

 

Now comes the main selection screen to Enter Fiscal Year, From Period and To Period using variables defined above. Assuming that all other fields are set in the variant. It is a simple "Type Into" Activity to map the variables accordingly.

 

Picture17

 

Variant: OPENPERIOD 

 

Picture18

 

Now comes the main selection screen to Enter Fiscal Year, From Period and To Period using variables defined above. Assuming that all other fields are set in the variant.  

It is a simple "Type Into" Activity to map the variables accordingly.  

  1. d) Validation: once it is executed, I generally validate the results in table KAPS just to confirm that periods are changed successfully. To do that, we can add another set of activities in the same workflow to call transaction 'SE16n', type into table KAPS, Enter KOKRS (Controlling Area), GKAHR (fiscal year), PSP001 (period lock) and any other field that's relevant for selection (based on the fields entered above), execute it (hot key F8 or Activity Select menu Item). If an entry is found (one way to check is data scraping), that means the period is locked successfully.  

 

  1. Based on the confirmation, workflow email can be configured to send notification to business users announcing changes in CO calendars. 

 

  1. 3. MM (Materials Management) Periods: :  similar concept would allow to open and close MM periods. 

SAP transaction code MMPV (program Name: RMMMPERI) 

Please refer steps discussed in above sections to replicate the steps for MM periods.  

 

  1. 3. Maintain monthly exchange rates
  2.  
  3. The definition of an exchange rate is self-defined and doesn't need much explanation. Every financial transaction in SAP goes through some form of currency conversion depending upon transaction currency and the local/group currency of an entity. Every company has its own requirements and frequency to maintain exchange rates.  

 

I've seen companies maintain exchange rates daily (type 'M') for operational transactions and then monthly rates for FX translation and revaluation purposes. These rates can be sourced from commercial websites (systematically or downloaded manually) or retrieved from another entity within an organization. For this discussion, I'll assume that rates are somehow available to the group responsible for maintaining the exchange rates in SAP.  

 

In this article, I'll talk about how to automate exchange rates maintenance in SAP (whatever frequency it could be) using UiPath.  

(Please note that requirements and procedures may vary from company to company. The purpose of this article is to share general concepts on using UiPath automation if the rates are being maintained manually). 

 

SAP provides two transaction codes to maintain exchange rates: 

 

  1. a) Transaction code: OB08 (or S_BCE_68000174) to "Enter Translation Rates" 

 

This transaction is used to manually insert exchange rate records. We either maintain indirect quotes or direct quotes for a combination of from/to currency codes. Please check with business on the requirement before proceeding with automation. This is what a standard SAP screen looks like: 

 

Picture19

 

There are couple of ways to automate it. The approach we are going to discuss is Excel uploading to insert records one by one.  

UiPath provides standard activities to achieve this automation.  

 

Sample pseudocode looks like this: 

(Assuming rates are available in Excel and file is saved locally on PC/Server)  

 

Sample Excel file

 

Picture20

 

Step 1: Open Excel file using “Excel Application Scope” and read records in Data table using “Read Range” Activity.  

 

Picture21

 

Step 2: Use SAP activity “Select Manu Item” to click New Entries on the screen to allow inserting records. 

 

Picture22

 

Step 3: Once the records are in data table, next step is to process in loop and map row/column to SAP screen fields. Use activity “For each row” to loop through data table and use “Type Into” activity to map row item (and column positioning) to screen field as applicable. 

 

Picture23

 

 

Picture24

Step 4: Once all the records are processed, use SAP activity “Select Menu Item” to save the records and exit the screen.

 

Picture25 

 

Records are inserted successfully.  

Validation: once it is executed, I generally validate the results in table TCURR just to confirm that rates are inserted successfully. To do that, we can add another set of activities in the same workflow to call transaction 'SE16n', type into table TCURR, Enter KURST (Exch. Rate Type), FCURR (From Currency), TCURR (To-Currency) and any other field that's relevant for selection (based on the fields entered above), execute it (hot key, F8 or Activity Select menu item). Check number of records and compare against the count in Excel file. If matching, automation worked successfully. 

 

  1. b) Transaction code: TBDM (program Name: RFTBFF00) 

This transaction allows uploading text (.txt) file directly to maintain exchange rates in SAP. Please check with business to understand and define the variant for this transaction. This is a simple automation to execute SAP program using pre-defined variants. Variant can also contain the file name/path to upload the exchange rates.  

Pseudocode and sample steps are as follows: 

Step 1: Use Activity "Call transaction" for transaction code SA38 to execute a program 

Step 2: Type into program "RFTBFF00" 

Step 3: Select menu Item for Execute with Variant 

 

Picture26

 

Step 4: Type into Variant Name and Execute using Select Menu Item 

 

Step 5: Select Menu Item to Execute the program

 

Picture27

 

Validation: once it is executed, I generally validate the results in table TCURR just to confirm that rates are inserted successfully. To do that, we can add another set of activities in the same workflow to call transaction 'SE16n', type into table TCURR, Enter KURST (Exch. Rate Type), FCURR (From Currency), TCURR (To-Currency) and any other field that's relevant for selection (based on the fields entered above), execute it (hot key, F8 or Activity Select menu item). Check number of records and compare against the count in Excel file. If matching, automation worked successfully. 

 

Conclusion

 

The use cases covered in this article are just the tip of the iceberg, with a lot of potential to supplement SAP-native automation using UiPath. In the next article I'll share tips and tricks on how to use UiPath automation to speed up periodic reconciliation activities involving SAP data extraction and our friend "Excel". Stay tuned for more to come. 

 

Rahul Goyal is a Senior Director of ERP Systems at Ellucian

 

Review UiPath and earn gift cards. Join us and help other professionals learn from your experience with UiPath. Your review can be anonymous.


by Rahul Goyal

TOPICS: SAP Automation, UiPath Studio

Show sidebar