Overcoming the Obstacles of ERP 3-Way Matching with a Customized Solution

A modern Enterprise Resource Planning (ERP) system covers a huge breadth of solutions under a single and flexible roof. ERP implementation creates efficiencies in business insight processes by providing the ability to access data in a common language while also helping to reduce downstream impacts. By collecting an organization's shared transactional data from multiple sources, ERP systems eliminate data duplication and provide data integrity with a single source of truth. ERP systems will also provide transparency into complete business processes by tracking all aspects of production, logistics, and financials. These integrated systems act as a business's central hub for end-to-end workflow and data, allowing access to a variety of departments.

There are many key processes in every ERP Implementation. However, three-way matching functionality is critical for all companies that utilize multiple functionalities related to supply chain processes. Three-way (3-way) matching is an accounting process that compares what was ordered (the purchase order or PO), what was delivered (the receipt), and the supplier's invoice to verify that an invoice is legitimate and ready to be paid. As part of this process, a company verifies an invoice by comparing details across three documents (purchase order, receiving goods, and supplier invoice).

While 3-way matching is generally provided with common ERP systems, companies are often challenged to match this application's out-of-the-box functionality with their needs. Typical challenges include:

• If the company's requirement involves a lot of logic and derivations to arrive at the fields needed for an invoice, it does not support the application of various deals in channel revenue management during the matching process.
• There is no way to integrate with third-party systems to consider invoice data for 3-way matching.
• For any ERP system to execute the 3-way matching process for any invoice, the Purchase Order information should reside within the ERP system; otherwise, the Invoice to Purchase Order matching criteria does not work.
• If the Invoice has deals and Freight Allowances, it does not have the functionality to accommodate these details.
• If the Invoice has additional fees, it does not have the functionality to reconcile additional fees.

Compounding these challenges, the 3-way matching out-of-the-box program is not suitable for certain business use cases. Examples of these cases include:

• Companies that do not have integrated ERP systems that have a requirement to execute the three-way matching process on their vendor invoices against Purchase Orders that reside in a different system
• Making the carrier transportation payments by matching against the Purchase Order receipts and calculating the authorized freight payment based on receipt quantities
• Brokerage invoice payments by Purchase Order matching
• Exception handling process for users through a dedicated user interface (UI) designed for Price mismatches, Quantity discrepancies, Purchase Order charges, and allowances between Purchase Order and Vendor Invoice
• Ability to integrate with EDI (Electronic Data Interchange) invoice data or OCR (image reading) data to import invoice data
• Ability to take care of deals, Freight Allowances, and additional EDI fees during the 3-way match process.

Customizing a Solution

Because the standard ERP 3-way match doesn't meet any of the above requirements, I designed and developed a custom eco-system—known as a Pre-Match custom application—that can produce all the required referential transactions to execute the Procure-to-Pay process in 3-way matching criteria. All this application requires is to supply a data file that contains the Purchase Order details such as items, costs, order quantities, Purchase Order schedules, overhead costs, and goods receipt information. Once the invoice is received from the vendor, the pre-match is invoked. This Pre-Match has built-in rules to identify the source of the invoice, any penalty based on invoice attributes, and discounts based on the information on the invoice. When the pre-match validation is complete, it does two things: create an invoice with modified lines to match the PO and create additional journal entries to record the penalties and discounts. This innovation saved the client company substantial dollars on the Procure-to-Pay process and took advantage of the discounts and penalties that needed to be levied on the vendors.

The objective of this system is to facilitate the vendor or service providers invoicing a 3-way matching process. A 3-way matching process is always expected to have purchase order information with items' order quantities and receipt quantities that will be matched against the invoicing quantity. When all of these three quantity variables are within tolerance limits, then the record gets authorized for payment via the following process:

• Scanned Invoices – Invoices received from vendors are scanned through the Scanning System in a separate batch. Metadata from those invoices is imported into staging tables and triggers a Custom AP Scan Invoices Process, which imports the invoices into Pre-Match staging tables for 3-way matching. At the same time, the scanned images are sent to DMS and stored in respective folders. Once the invoices are imported into Pre-Match tables, the image link is attached to the invoice. Users can open the invoice image from the Pre-Match application.
• EDI Invoices – EDI process gets the invoice data from various EDI vendors and loads them into the EDI staging table. Custom AP EDI Invoices Import Program picks invoices from the EDI staging table and performs validations. If the invoice gets validated successfully and has all the mandatory fields, the invoice is then loaded into the ERP staging table. The invoices are picked up by the Custom Invoice Import Program, which loads invoices into Pre-Match tables for the 3-way match program, which does validations and standard derivations. If the validations fail, it brings the invoice to pre-match tables with an INVALID status, or else it brings a NEW status. Along with it, there is a Ready for Match flag, which will be marked as "Y." This invokes the Pre-Match program once it loads the invoices. Once they are complete, the Pre-Match program picks this invoice for matching along with other invoices and processes the same (Figure 1).

• Manual Invoices – Users can also create the invoices manually in the Pre-Match application. They load the header information, and the screens automatically load the receipt lines. Users can manually balance the invoice and correct the invoices if needed. Once they are complete, the Pre-Match program picks this invoice for matching along with other invoices and processes the same (Figure 2).

• Pre-Match Process – The invoices that come to Pre-Match tables from various sources are picked up for 3-way matching with the receipts, and the Custom AP Invoice Pre-Match process picks up the invoices with different statuses and matches the invoice based on the different conditions and rules. If the process does not find any exceptions, it updates the status of the invoices to "Matched Reconciled." If the process finds quantity exceptions but not price exceptions, it waits three days for any receiving corrections to occur. After three days, the invoices are then stamped as "Matched Reconciled." If the program finds price exceptions, the respective team is supposed to clear the exceptions to process the invoice further. Once the respective team takes a decision, the invoices are stamped as "Matched Reconciled" by the Pre-Match program. All invoices with the status "Matched Reconciled" are picked by "Custom AP Interface Pre-Matched Invoices to ERP" and inserted into ERP AP interface tables. Based on the scenario updated on the invoice, the lines inserted into the interface vary. This program investigates the scenario, loads the invoices into the interface accordingly and invokes the below standard program, which is Payables Open Interface Import. All invoices that are successfully transferred to the ERP Accounts Payables System are tied back to the invoice in the Pre-Match application, which helps in pulling the invoice related details from the Pre-Match application. Invoice IDs from the ERP System are stamped on the invoices in the Pre-Match application (Figure 3).

In addition to matching functionality, the Pre-Match application provides the following enhanced functionality for Deals at Invoice Header Level Grouping, Deals at Invoice Line Level Grouping, Deals – Invoice Matching – Deals Not Available on the PO, Deals – Invoice Matching – Deals Available on the PO, Freight Allowance, and Fines during Invoice matching.

Deals at Invoice Header Level Grouping

• Invoices have deals at the header level as well as the line level
• There could be numerous deals on the invoice, both at the header and line level, so the matching program needs to segregate the deals based on the data in the custom table
• If the deal is at the header level,
  • Sum the deal amounts deal-wise
  • Identify the parent deal using the mapping in the custom table and classify them into the parent deal.
  • If there is no parent deal, put the deal as header level negative adjustment.

Deals at Invoice Line Level Grouping

If the deal is at the line level, the below logic needs to be performed:

• Sum up the deals deal-wise.
• Identify the parent deal using the mapping in the custom table and classify them into the parent deal.
• If either the child deal or parent deal in the mapping in the custom table is tagged as header level, the deal amount should be classified as a header level deal and shown in header level deals.
• If either the child deal or parent deal in the mapping in the custom table is tagged as line level and if it is marked as Off invoice, the deal amount is multiplied by the quantity and the deal/unit arrived. The same is displayed to buyers in case of buyer exception (Example: OI Allowance).
• If either the child deal or parent deal in the mapping in the custom table is tagged as line level and if it is NOT marked as Off invoice, the deal should be treated as a line level deal and is not subject to buyer exception (Example: BDF Allowance).

Once the above grouping is done, the deals at the header level and deals at the line level (not OI) need to be checked against a different mapping in the custom table and then proceed towards deal matching.

Deals – Invoice Matching – Deals Not Available on the PO

If the parent deal on the invoice is mentioned as "Miscellaneous," this means the deal is not available on the PO. In such cases:

• The deal is rolled up to the header, and the same is passed as a miscellaneous line to the ERP AP interface.
• There will be no deal matching with a PO/receipt for such deals.
• Mapping in the custom table has a GL distribution to be given to this miscellaneous line.

Deals – Invoice Matching – Deals Available on the PO

If the parent deal on the invoice is NOT mentioned as "Miscellaneous," this means the deal is available on the Purchase Order. In such cases:

• The deal amount should be rolled at the header level and matched against the PO/Receipt.
• The parent deal should be checked in the mapping in the custom table and go to the respective PO table to check the deal on this PO.
• The objective is to arrive at the following three amounts:
  • Deal on the Invoice
  • Deal on the Purchase Order
  • Deal on the Receipt (Accrued)
• Decide which deal to be taken to invoice:
  • If the Invoice deal amount is greater than the PO Deal, the Invoice deal is taken
  • If the Invoice deal amount is less than the PO Deal, the PO deal is taken
• Once the deal amount to be taken is arrived upon:
  • If the Amount to the extent of the deal is on the receipt, it will hit the Deals Receivable A/C as mentioned in the mapping in the custom table for that respective deal
  • Taken Deal-Receipt deal: the rest of the amount goes to Deals HI Account/Deals Income Act mentioned in the mapping in the custom table for that respective deal based on tolerances.

Freight Allowance

• Freight Allowance is captured at the Purchase Order header level.
• Vendor invoice also gets the freight allowance either at the Purchase Order header level or at the Purchase Order line level on deal lines only.
• These lines roll up to Freight Allowance at the Purchase Order header level.
• Compute the difference between the Purchase Order freight and Vendor Invoice freight, and if the difference is within the tolerance, it gets processed, and if the difference is outside the tolerance, it goes into price exception.
• The status of the invoice will be "MATCHED-PRICE EXCEPTION" and flagged as an exception.

Fines During Invoice Matching

• Invoice EDI Compliance Fee: If the Invoice EDI Compliance Fee is greater than zero and the EDI Compliance Fee is not received via EDI, the EDI Compliance Fee is stored in the Invoice EDI Compliance Fee field for each Invoice received
• Manual PO Fee: If the Manual Purchase Order fee is greater than zero and the Manual Purchase Order fee is not sent via EDI, the Manual Purchase Order fee amount is stored in the "Manual PO fee" field for each Invoice received
• Invoice Pricing Discrepancy Fine: Invoice Pricing Discrepancy Fine is greater than zero, and it gets stuck with Buyer Exception, and if the Buyer's Adjusted Price is lower than Invoice Price then this fine is considered. The fine amount is stored in the "Invoice Pricing Discrepancy Fine" field for each Invoice received
• Lost Potential Income fine: If the Lost Potential Income Charge is set to "X," then any item with an Off Invoice amount that is received for a quantity less than ordered generates a deduction for that Off Invoice Amount times the Off Invoice amount.

This customized solution to the 3-way match problem was designed using Oracle Database, Java for the backend, React JS for the front end, and Oracle PLSQL.

Besides optimizing financial outcomes on the Procure-to-Pay process and improving profitability by capturing discounts and penalties levied on vendors, this solution saved significant employee hours in manual labor. As an added benefit, reducing the tedium associated with manual updates is of great value to businesses of any size.

Among the many advantages this solution provides, it takes care of the 3-way matching process seamlessly in any situation and allows for all the business use cases. Additionally, in the case of this client company, more than 25 employee users were working on deals, allowances, and EDI fees prior to the Pre-Match application implementation. Following implementation, completing deals, allowances, and EDI fees requires only a couple of users to get the job done in far less time and with far less effort. In a short time, the cost of developing and implementing this solution more than paid for itself.

About the Author

Malyadri Kapugorla is an Oracle Solution Architect at SPL Consulting, Inc. He brings 20 years of highly specialized experience in Oracle E-Business Suite (EBS) Enterprise Resource Planning (ERP) Applications and Oracle Fusion Cloud Enterprise Resource Planning applications to engineering transformative process improvements for businesses across diverse sectors, including air transportation, wholesale grocery, sales and distribution, manufacturing, real estate construction, technology, and software development. With a bachelor's degree in Commerce and Computer Applications and a master's degree in Financial Management, Malyadri is skilled in business and system analysis, configuration, testing, conversions, and post-implementation support. He drives operational efficiencies by developing and implementing innovative solutions that streamline and automate financial and business processes, revenue management cloud services, and project portfolio management.