Automating ERP Data to Update Profiles and Balances in ARCS
By: CloudADDIE
Published: August 19, 2020
OUR CLIENT
Our Client is a Fortune 500 Leader in the Entertainment Industry; with a global market share, and billions of dollars in annual revenue.
| Industry | Market | Revenue |
|---|---|---|
| Entertainment | Global | $12 Billion |
Our client hired CloudADDIE to help modernize their Finance Operations on a global scale. Part of the Finance modernization initiative aimed to efficiently manage and improve global account reconciliation by automating and addressing security and risk typically associated with the reconciliation process.
They chose CloudADDIE because of our reputation of delivering custom Enterprise solutions that the Big 4 Consulting firms cannot deliver.
CHALLENGE
Our client wished to streamline the process required to create reconciliations by importing profiles as soon as new accounts were created in the ERP Systems. Typically, importing balances for Profiles that do not exist in ARCS will yield invalid alerts that must be addressed by the ARCS Admin.
ARCS PROFILES
Profiles are one of the most important objects within Account Reconciliation since profiles are the pre-cursors to reconciliations. Profiles is the term used for the collection, or group, of accounts that are reconciled.
Each profile represents a distinct reconciliation and may contain many different low level accounts rolling into it.
Profiles can be created manually or imported from a spreadsheet.
| Scope | Maintenance | Formats |
|---|---|---|
| 100,000 Profiles | Weekly | 7 (VA,AA,BC) |
Our client created Profiles manually for seven (7) different formats and three (3) methods: Variance Analysis, Account Analysis and Balance Comparison.
IMPORTING BALANCES
Our client's reconciliation requirements involved importing balances from a Cloud General Ledger ERP system. Source and Sub Systems were set up based on the reconciliation Methods.
| Data Loads | Integrated Systems |
|---|---|
| 14 Locations | Oracle Fusion Cloud Financials & ARCS |
Balances needed to be imported to 14 locations with minimal data management mapping.
The Challenge was to create new Account Reconciliation IDs from Oracle Fusion Cloud Financials, export the new Profiles from ERP, export balances for the 7 formats, import the new profiles into ARCS, and load the balances into ARCS for the 14 locations.
SOLUTION
To minimize the data mapping requirements needed to import balances to 14 locations, CloudADDIE created pre-mapped balances. By leveraging SQL queries to extract Reports from the ERP System, CloudADDIE was able to simplify the mapping design. New Profiles were dervived by comparing the latest Profile extracts to the Profiles in ARCS, and using Powershell to keep unique rows, insert headers and autonomously rename and prepare Profiles for EPM Automate to continue the automated processes.
EPM Automate
EPM Automate is a powerful automation tool for EPM Cloud services. We supplemented EPM Automate commands with shell scripting and a superve error handling design to successfully manage ARCS processes such as: importing profiles, importing balances, creating reconciliations and sharing reports.
OTBI
We designed data models to create Reports for both premapped balances and for profiles using SQL. By using premapped balances, we were able to circumvent Data Management and avoid any mapping errors due to import formats or mapping issues. For Profiles, we were able to compare the current OTBI Reports to the prior OTBI reports, and keep only the difference. By doing so, we only needed to import the new profiles, instead of 100,000 profiles.
PowerShell
PowerShell served two main roles for this project. The first role provided custom email alerts to the ARCS Admin in case an error in any of the systems was encountered. The second role was to leverage the power of Bash for ETL. We used Bash to convert OTBI Report Exports from Excel to CSV, remove duplicate profiles, insert headers and format files for import into ARCS.
RESULTS
New Profiles were created and imported, Period Balances were loaded for 14 Locations and 100,000 Profiles, Reconciliations were created, and reports were sent to the appropriate personel. All of these processes are now done autonomously. The only intervention needed happens when the Autonoumous System detects a critical error, and a custom alert is sent to the ARCS administrator with a detailed description of what caused the critical error. Admins can troubleshoot errors using human-readable details, unlike Oracle's Default FDMEE error logs.
Feel free to contact us for more information.
FREE CONSULTATION
Schedule your Free Consultation, to see it in action!
Contact Us!
We offer Autonomous Systems for EPM Cloud Applications such as Oracle Planning & Budgeting Cloud Service (PBCS/EPBCS), Financial Close & Consolidations Cloud Service (FCCS), Enterprise Data Management Cloud Services (EDMCS), Account Reconciliation Cloud Service (ARCS), Profitability & Cost Management (PCM) and more.