Oracle EBS to GCP : Accounts Payable

Architecture

Figure 1: Reference Architecture

1. Data from the source system (Oracle EBS Migration) is ingested into ODS Stage schema through cloud composer application, the oracle EBS Migration data is first landed into GCS bucket in csv format and then inserted into the ODS stage schema. After the initial load, the data from oracle EBS Migration is pulled incrementally into the GCS bucket.
2.  Then the data is ingested into the ODS Schema. This merging of data from ODS Stage to ODS Schema of BigQuery is implemented in the cloud composer itself. 
3. Once the data is merged from ODS stage schema to ODS schema, the oracle EBS data file that was created in the GCS bucket is moved to an archived location within the GCS bucket.
4. From ODS layer to Edw the data mart is cleansed and transformed according to the business requirement through the Dataform on top of the Google Cloud Platform.
5. The Dataform Scripts are executed on a daily basis to get the most recent data from the source.
6. This execution of the Dataform jobs is automated using the Cloud Composer. To do this each dataform form is given some tag name and the cloud composer is able to execute those jobs via these tag names.
7. In the transformation the data is denormalized as per the data model defined below.
8. All the needed naming standards and conventions are followed while creating data models.
9. Looker is connected to the Edw layer for all the accounts payable reporting needs.

Data Flow Diagram

Figure 2: Data Flow Diagram

1. Data flows from EBS and Hyperion to the GCP ecosystem.
2. Source data is first dropped into a GCS bucket in csv file format using the cloud composer.
3. Various one to one target tables are created that hold the EBS table data in bigquery tables. This stage of data flow is called data replication. All the target tables related to AP are listed above in the Data flow diagram.
4. Once the data replication is done in the ODS layer the final Edw tables are created by performing the desired transformations. This is done using the dataform. 
5. Finally this transformed data is consumed by Looker to produce the desired reports and actionable insights.

Data Models

Figure 3: Data Model Summary                   

1. In the AP module we have created Four data models based on the business requirement. The final looker reports generated are based on these Four models. 
2. All the dimensions are shared across these models.
3. The fact tables include: 
   Invoice information at distribution level for Invoice Model
   Payment schedule and actual payment information for Payments
   Invoice aging information group into various buckets by invoice date, invoice creation date and invoice received date.
   Combined aggregate which captures all the activities that happen in AP lifecycle in terms of invoicing and payments.
4. The dimension tables will include the accounting information, Ledger information , Period Information and the Supplier information.
5. These dimensions are common across all the models and are joined with the fact tables to get the desired information.
6. Among all these models, the combined model is unique because it combines invoice, payment, purchase order & purchase requisitions information together in this model.
7. The invoice aging model is also of utmost importance as it helps in tracking the invoices that are yet to be paid and for how long they have been open. Model is capable of bucketing these invoices based on its invoicing date or its creation date in oracle or its receiving date. This model can be extremely helpful in analyzing and minimizing the accruals in terms of supplier payments. 
8. Invoices are separated into individual data models like distribution and payment schedules in order to facilitate detailed reporting at granular level, centered around invoice distribution.
9. All the models are integrated with the hierarchies that are defined in hyperion to get the hierarchies related to account, department, company and other segments.
10.  Additionally in all the data model currency conversion mechanism is applied to convert the transaction recorded in local currency to global currency like USD.

Combined Aggregate Model

1. The Combined Aggregate presents the summarized view of the invoice lifecycle in terms of its distribution, payments, purchase orders and requisitions.
2. Accounting-APInvoiceDistributions and Accounting-APPaymentSchedules are the Primary tables for the combined data model along with other supporting tables.
3. For the combined aggregate the granularity of the data is kept at a combined level of vendor, ledger, operating unit, source, payment terms and periods.
4. The accounting related details are obtained by joining the combined table with the CodeCombination table.
5. Time-series KPIs such as year-to-date, quarter-to-date are calculated based on two important dates in the AP lifecycle. These dates are invoice date and invoice creation date in oracle.
6. Pre-calculated KPIs are added in the model to track the number of early, on-time and late payments.
7. Pre-calculated KPIs related to AP balance amount, overdue amount, overdue invoice counts are also added to the model.
8. The audit columns are added at the denormalized level and the naming standards are followed.
9. As an outcome of this model it becomes easy to track the key AP KPIs through one model only. Some of these KPIs include invoice amount, number of invoices on hold, number of canceled invoices, payment amount, overdue amount, total number of early, on time and late payments, number of purchase orders and requisitions associated with invoices.

Figure 4 : Combined Aggregate Data Integration

Figure 5 : Combined Aggregate Data Model

Invoice Aging Model

1. The invoice aging model comprises all the invoices that are yet to be paid.
2. Accounting-APInvoiceDistributions is the primary table for the aging data model along with other supporting tables.
3. Monthly snapshot of the data is taken to track the number of invoices that open as of a given month.
4. For the invoice aging fact, the granularity of the data set at the invoice level.
5. The accounting related details are obtained by joining with the CodeCombination table.
6. The audit columns are added at the denormalized level and the naming standards are followed.
7. As an outcome of this model it becomes easy to track the total number of open invoices as of a given month. This model is robust enough to bucket the open invoices in terms of the number of days they have been open based on their invoice date, invoice creation data and invoice received date.
8. The model captures the hold information as well to understand if the invoice is open because of one or more hold reasons.
9. This model can be extremely helpful in analyzing the outstanding amount as of a given month.

Figure 6: Invoice Aging Data Integration

Figure 7: Invoice Aging Data Model

Invoice Distribution Model

1. The invoice distribution is the most detailed model that comprises all the data related to invoice lifecycle in terms of its distribution, payments, holds, cancellation, purchase orders and requisitions.
2. EBS tables related to invoice distributions, holds, purchase orders, requisition and payments are the primary tables for this model along with other supporting tables.
3. For this model the granularity of the data is kept at invoice distribution level.
4. The accounting related details are obtained by joining the combined table with the CodeCombination table.
5. Time model supports reporting at various date levels such as invoice date, invoice creation date, accounting date etc.
6. Derived attributes are added to the model to track the invoice where the invoice amount is not equal to invoice distribution amount or the invoice that is on hold.
7. The audit columns are added at the denormalized level and the naming standards are followed.

Figure 8 : Invoice Distribution Data Integration

Figure 9 : Invoice Distribution Data Model

Invoice Payment Schedule Model

1. The invoice payment schedule is the detailed model that comprises all the data related to invoice payment lifecycle in terms of payment schedules, total payment done and the date on which the last payment was made.
2. EBS tables related to invoice accounting, payment schedule and payments are the primary tables for this model along with other supporting tables.
3. For this model the granularity of the data is kept at invoice payment schedule level.
4. The model supports reporting at various date levels such as schedule or due date, invoice date, invoice creation date and payment date.
5. Pre-calculated KPIs are added in the model to track the number of early, on-time and late payments.
6. Pre-calculated KPIs related to overdue amount and overdue invoice counts are also added to the model.
7. The audit columns are added at the denormalized level and the naming standards are followed.

Figure 10 : Invoice Payment Schedule Data Integration

Figure 11 : Invoice Payment Schedule Data Model

Incremental Update using Cloud Composer

        Figure 12 : Cloud Composer Solution Overview 

1. As highlighted in the architecture diagram the data is ingested into bigquery tables using the cloud composer (airflow).
2. The above diagram shows the other details related to this process in the cloud composer.
3. As shown above, the flow of data is from oracle EBS to GCS bucket and from GCS bucket to Ods stage followed by Ods.
4. It is when data moves from oracle EBS to GCS bucket the change data capture (CDC) logic is applied to get incremental data.
5. This is done by using a system variable that maintains that last successful execution start date of cloud composer code.
6. Only those records are processed from EBS that have last_update_date as greater than or equal to this date.
7. Additionally cloud composer is empowered with an option to pass prune days as well which is leveraged here to go one day back further from last successful execution start date of cloud composer code to get the recently modified data in source.
8. This ensures that there is an extra cushion to ensure that the code is missing any of the records that are modified in source.
9. The config file shown in the diagram maintains the information such as project id, region, gcs bucket information, initial extract date, repository etc.

Figure 13 : Sample Cloud Composer Data Flow

Key Looker Dashboards and Visualizations

AP Overview Dashboard

The AP overview dashboard offers a holistic view of the end-to-end lifecycle of invoices, providing crucial insights into the efficiency and performance of the Accounts Payable systems.

 Key highlights include:

1. Invoice Metrics: 

• Breakdown of invoice amounts, providing a comprehensive overview of financial transactions.

2. Invoice Payment Lifecycle:

• Breakdown of invoices based on payment status (paid, unpaid).
• Insights into the distribution of payments made early, on time, and late.

3. Payment Metrics:

• Overview of paid amounts, showcasing the total amount paid within the specified timeframe.
• Analysis of balances or unpaid amounts, aiding in understanding outstanding financial obligations.

4. Overdue Payments:

• Identification and quantification of overdue payments, offering a clear understanding of the financial impact of delays.

5. Invoice Counts:

• Count of invoices paid early, on time, and those facing delays, providing a comprehensive picture of payment timeliness.

6. Hold and Canceled Invoices:

• Tracking the count of invoices currently on hold, allowing for proactive management of potential issues.

• Monitoring the count of canceled invoices, helping identify and address any anomalies.

Key Takeaways:

• Efficiently track and manage the payment lifecycle of invoices.
• Pinpoint areas of improvement for on-time payments.
• Monitor and mitigate the impact of overdue payments on financial operations.
• Proactively address issues related to holds and canceled invoices.

This dashboard empowers stakeholders with valuable insights, enabling informed decision-making and optimizing the overall efficiency of the Accounts Payable automation process.

AP Aging Dashboard

The AP Aging Dashboard is designed to provide a comprehensive view of unpaid invoices, categorized into age buckets. This enables quick identification of outstanding payment trends and helps in assessing the financial health of the accounts payable analytics.

Key Highlights Include:

1. Age Buckets: Invoices are categorized into age buckets such as 0-30 days, 30-60 days, etc., allowing for a clear understanding of the aging of unpaid invoices.
2. Date Criteria: Age buckets are created based on different dates, including invoice date, invoice creation date, invoice received date, and due date. This flexibility provides insights into payment delays from various perspectives.
3. Trend Analysis: The dashboard facilitates trend analysis by allowing users to observe the month-on-month changes in the number and value of unpaid invoices. This helps in identifying patterns and addressing potential issues promptly.

Benefits:

1. Timely Payment Monitoring: Enables real-time tracking of unpaid invoices, allowing for proactive management and timely resolution of payment issues.
2. Customizable Views: Users can customize the dashboard to focus on specific date criteria or age buckets, tailoring the analysis to their specific needs.
3. Visual Representation: Visualizations, such as graphs and charts, enhance data interpretation, making it easier for stakeholders to grasp trends and patterns quickly.
4. Performance Insights: Provides insights into the effectiveness of accounts payable processes and aids in optimizing cash flow management.

Recommendations:

1. Regularly monitor the trends in age buckets to identify any increasing trends in overdue invoices.
2. Investigate and address any significant changes in the aging of invoices promptly.
3. Utilize the flexibility of date criteria to gain insights into specific aspects of the accounts payable process.

AP Invoices on Hold Dashboard

The AP Invoice Hold Dashboard is designed to provide insights into invoices that are placed on hold. It focuses on showcasing the top reasons for holds and identifies the key suppliers whose invoices are frequently held. The main key performance indicators (KPIs) tracked are the hold amount and the count of invoices on hold.

Key Highlights Include:

1. Hold Reasons Analysis: The dashboard highlights the primary reasons why invoices are put on hold, offering a detailed breakdown of the top factors contributing to delayed payments.
2. Top Suppliers on Hold: Identifies and ranks the top suppliers whose invoices are most frequently placed on hold. This insight helps in managing relationships and addressing potential issues with specific suppliers.
3. Hold Amount and Count: Key KPIs include the total amount and count of invoices currently on hold. These metrics provide an overview of the financial impact and the volume of invoices affected by holds.

Benefits:

1. Issue Identification: Quickly identifies and prioritizes the reasons causing invoices to be on hold, facilitating prompt issue resolution.
2. Supplier Relationship Management: Pinpoints suppliers with frequent holds, allowing for proactive communication and collaboration to address recurring problems.
3. Financial Impact Assessment: Provides a clear understanding of the financial impact of held invoices, enabling better cash flow management and forecasting.

Recommendations:

1. Regularly review the top reasons for invoice holds and take corrective actions to minimize delays.
2. Engage with high-frequency suppliers on hold to improve communication and address recurring issues.
3. Monitor hold amount and count trends over time to gauge the effectiveness of process improvements.

Conclusion

In this article, we have delved into the implementation of Oracle E-Business Suite (EBS) Accounts Payable (AP) module within the Google Cloud Platform (GCP) ecosystem. Our solution not only streamlines the end-to-end lifecycle of AP operations but also unlocks powerful insights and efficiencies for organizations.

From invoice management to payment processing, our implementation covers every aspect of the AP process. We have meticulously designed models to handle invoice information, schedule and payment details, and hold management, ensuring seamless transactional flow and compliance.

The integration of the aging model not only captures unpaid amounts but also determines the aging of invoices, providing crucial insights into financial health and liabilities. This feature empowers organizations to make informed decisions and proactively manage their cash flow.

Furthermore, our implementation offers valuable analytics capabilities and by leveraging the data within Oracle EBS Migration on GCP, organizations can optimize vendor relationships, negotiate better terms, and drive operational excellence.

In conclusion, by harnessing the power of cloud computing and robust ERP solutions, organizations can achieve greater efficiency, accuracy, and strategic insight in their AP processes, paving the way for sustained growth and success in the digital era.

Curious And Would Like To Hear More About This Article?

Contact us at [email protected] or Book time with me to organize a 100%-free, no-obligation call