3-Way Match

EXECUTIVE SUMMARY

Client A needs Elite to be able to electronically perform an automatic 3-way match between a purchase order, a merchandise receiver and the vendor invoice.  Elite should also be able to allow for EDI 810 Vendor Invoice transaction. 

BUSINESS REQUIREMENT

Client A requires the ability to enter vendor invoices into Elite and to match the invoice lines to the purchase order receipts and purchase order cost.  If the values do not match and are outside a tolerance level, set either at the system level or the vendor level, then the invoice does not match.  Neither Elite Payables Entry nor Invoice Reconciliation contains the full functionality that Client A requires.

When an invoice has been entered and accepted, the system should apply a set of matching rules to determine if the invoice is eligible to be paid or not.  The user should also have the ability to force the invoice through in the invoice entry screen.  

Matching rules would check the invoice line quantity versus the receipts.  If the invoice is for more than the quantity received and not yet matched, then the line does not match.  If the line quantity is correct, then the system should check the invoice price versus the purchase order price and if the invoice price is higher and it is greater than the invoice tolerance.

EDI 810 Inbound Invoice functionality must be added to Elite and the invoice validation must follow the same matching rules once the invoice is in Elite.

Functional Overview

Client A requires the:

1.        Ability to enter vendor invoices in batch and assign a reference (batch) number.   Transactions in that  batch can be released even if not all invoices in that batch are balanced. This has the advantage of allowing a matched invoice to be flagged for payment in PeopleSoft.

2.        Ability to receive an 810 EDI Vendor Invoice into the Elite system. 

3.        Ability to flag the invoice as Matched “Y” or Not Matched “N” (default to “N” at invoice entry)

4.        Ability to enter invoices before the merchandise is received (in WMS) (flag as Prepaid)

5.        Ability to set a tolerance of the price extension (in percentage or dollars) at the Global level and at the vendor level for unmatched invoices.   The global setting would be default.

6.        All values above PO values should go to variance, not just the values in excess of tolerance.

7.        Ability to run a matching process for all invoices in a batch (see point #1 regarding batches.)

8.        Ability to enter the PO# on the header screen of the Vendor Invoice Transactions screen

(Vendor # and terms code default in fields)

9.        Ability to run an exception report for matched and unmatched items on the quantity, unit price and extended price of the PO vs invoice

10.     Ability to (always) enter an invoice with reference to the PO line detail – not the (WMS) receipt line detail

11.     Ability to inquire on invoice number, vendor number, receipt number, purchase order number, total receipt amount and the detail of that receipt (for researching exceptions) (+ Journal # and System entry date)

12.     Ability to modify the quantity invoiced, unit price and  extension at each line level of the Vendor Invoice Transaction screen (based on the PO quantity)

13.     Ability to enter and match the merchandise quantity and value, duty, import freight and/or brokerage charges (separate fields); All values at header of invoice entry, and sum equals invoice amount

14.     Ability to enter duty, freight, brokerage and all other charges without merchandise amount.

15.     Ability to enter to 3 lines of  miscellaneous charges on the header screen of the Vendor Invoice Transaction screen (these will be included in the payment of invoice)

16.     If freight amount is not included in receipt value, then go back and pick up the GL value in PO Organization.

17.     Ability to flag the invoice as “prepay” before and/or after the invoice is entered (requires correct invoice number)

18.     Ability to correct (or add) or change all fields of the invoice

19.     Ability to enter a debit memo for adjustments against a specific invoice (in Elite, this is called a credit memo)

20.     Ability to match vendor invoices on vendor drop ship purchase orders (standard Elite functionality)

21.     Ability to turn off the G/L interface; the ‘APP’ source journal entries will not be sent to People Soft (this is a flag in the AP Organization Maintenance – current functionality)

22.     Ability to pay invoices before they are matched

23.     Ability to determine user permissions for invoice entry as dictated by the user group maintenance functionality (standard functionality)

24.     Ability to process at least, as many lines on the Vendor Invoice Transaction Screen as there is on the purchase order.

25.     Ability for invoice entry to display only the quantity on the PO line that is remaining

26.     Ability to change the “Remit To” Vendor at Invoice Entry.

27.     Ability to capture freight on a separate line on the header of invoice entry

28.     On line access to invoices that are in the exception queue

 

Current Process Flows

SOLUTION SUMMARY

In order to provide the functionality that Client A requires, the following solution is proposed:

Proposed Process Flow

Pricing Tolerance Value/Percentage – A/P Org

Create a system level pricing tolerance value/percentage option in A/P Organization Maintenance.

Ø       Set up to be either a percentage or a dollar value

Ø       The value entered here will become the default value.

Ø       Values in excess of  PO values (not just tolerance levels) resulting from the Invoice matching program would be assigned to the Inventory Variance G/L account.

Ø       Add details to A/P Organization List

 

Pricing Tolerance Value/Percentage – Vendor

Create a pricing tolerance value/percentage in Vendor Maintenance.

Ø       Set up to be either a percentage or a dollar value

Ø       Any value entered here will override the system default value.

Ø       Values in excess of Vendor tolerance levels resulting from the Invoice matching program would be assigned to the Inventory Variance G/L account.

Ø       Add details to Vendor List

 

Vendor Invoice Entry Program

Create a Vendor Invoice Entry program in which to enter the vendor invoice information (for merchandise or freight, duty, brokerage, misc.) to be matched to the purchase order.  

Ø       Security for accessing this function will be governed by the setup done in User Group Maintenance (UTGROPM1).

Ø       Options available would include the ability to 1) add or modify invoices, 2) add or modify adjustments,  3) delete transactions or 4) perform a query on invoices.

Ø       A reference number will be assigned. The reference number will be the next in the batch number sequence generated by Payables Entry.

Ø       The vendor_num, trans_type, and trans_num should be used as a means of tracking the transaction

Ø        Selecting ‘Add/Modify’ would bring up a screen where the user can enter the Organization, Division, and the Entry Date (system date by default). 

  • F1 accepts entries here and then brings up the Vendor Invoice Entry screen.  This option would allow the user to enter a Vendor Invoice (VI) or LC=Landed Cost (for Freight, Duty, Brokerage, Misc. 1, Misc. 2, and Misc. 3).
  • Choosing LC would bring up only the header screen.

A sample Invoice Entry screen would be:

HEADER

12345678901234567890123456789012345678901234567890123456789012345678901234567890

1Vendor Invoice Reconciliation

2
3
4
5
6
7
8
9
0
1
2
3
4
5
6
7
8
9
0
1
2
3
4

 Menu:   Add/Modify  Delete  Adjust  Match  Query  Reconciliation  Exit  Help

Add or modify an invoice.
 Organization: __                                  Ref.#      : ______
                                                   Entry Date : ___________
 Trans. Type : __
  PP/O Num    : _______   
 Vendor      : ___________ _____________________________
 Invoice #   : __________      
 Invoice Date: ___________            Remit to  : ___________          
 Invoice Amt : ___________            Prepaid   : _(default = N)        
 Terms       : __ [                    ]
 Discount    : ______               
                           Applied
 Freight     : ___________   [ ]      Misc. 1   : ___________           
 Brokerage   : ___________   [ ]      Misc. 2   : ___________          
 Duty        : ___________   [ ]      Misc. 3   : ___________           
 Gross Amt: ___________ Total Entered: ___________ Difference: ___________

LINES

12345678901234567890123456789012345678901234567890123456789012345678901234567890
1Vendor Invoice Reconciliation
2
3
4
5
6
7
8
9
0
1
2
3
4
5
6
7
8
9
0
1
2
3
4

Menu:   Lines  Accept  Main  Cancel  Help                                   
Add, modify or delete lines.                                               
                                                   Ref.#      : ______
Trans. Type : __                                  Entry Date : ___________
PP/O Num     : 9999999    Vendor : ZZZZZZZZZZZ zzzzzzzzzzzzzzzzzzzzzzzzzzzzz
Invoice #   : __________   Invoice Date: ___________
Invoice Amt : ___________      Prepaid   : _(default = N)      
Gross Amt: ___________   Total Entered: ___________   Difference: ___________
--------------------------------------------------------------------------------
F1=Accept F4=Abort F9=Insert F10=Delete F11=Page Up F12=Page Down.

P/O Item                       Qty Rcvd Qty Invcd    Invc. Cost      Extended 
ZZZZZZZZZZZZZZZ                99999999 999999999 9999999.99999 9999999.99999      ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ 
ZZZZZZZZZZZZZZZ                99999999 999999999 9999999.99999 9999999.99999
 ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ 
ZZZZZZZZZZZZZZZ                99999999 999999999 9999999.99999 9999999.99999
 ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ 
ZZZZZZZZZZZZZZZ                99999999 999999999 9999999.99999 9999999.99999
 ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ 
ZZZZZZZZZZZZZZZ                99999999 999999999 9999999.99999 9999999.99999
 ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
                                                     Subtotal   9999999.99999 
Enter the item number F3=Q.R. F5=Sort Query F7=Select Items.

Ø       The scope of the transaction (begin work) will begin after the entry of the “P/O Number” and will end at the acceptance of the invoice (commit work). 

Ø       At the header,

  • Selecting ‘Add/Modify’ would allow entry or modification of a vendor invoice (VI) or landed cost  invoice (LC) within the Invoice Entry screen.  This screen could be used to search for, view, and modify unmatched vendor invoices. 
  • Selecting ‘Delete’ allows the user to delete any unmatched invoice.
  • Adjustments occur when the user selects ‘Adjust’ in the menu.  An adjustment transaction type is a reduction or increase in one or more of Freight, Duty, Brokerage, Miscellaneous, Invoiced Quantity, or Invoiced Cost.
    • Within this screen, the user will enter the P/O number, the vendor number and the invoice number of the transaction to be adjusted.
    • Adjustments can be created, whether or not the original document (the invoice) is matched or not.
    • Adjustment screen will show adjusted quantity and adjusted cost, both defaulted to 0.
    • The user enters either a + or – to indicate adjustment direction.  Columns visible include item number, description, and extension. 
    • Adjustments for quantity and cost can be made within the same transaction.
    • The resulting transaction will produce a
      • DM=Debit memo (increases* the invoice amount) and/or
      • CM=Credit memo (decreases the invoice amount)
      * The DM and CM roles could be switched where a DM decreases the invoice amount and the CM increases the invoice amount.

Ø       Selecting ‘Match’ from the menu allows the user to force the matched status for an invoice.

  •  Security for accessing this function will be governed by the setup done in User Group Maintenance (UTGROPM1).
  • This function is the Invoice Match Override Program (see section 1.3.4)
  • The screen will ask for the vendor number, invoice number, PO Number (displayed) and whether or not you want to force the match (Y/N, default to N).  
    When the invoice is matched through this process, it should match to the PO, based on the reconciled quantities, and all accounting entries should be made. 
    If the invoice is for more than the total of the receipts, then the remaining unreconciled quantity would equal 0, and the difference would go to the variance account.
     If a line that was not received is on the invoice, and the match flag is changed, then the value of that line should be posted to variance.

Ø        Selecting ‘Query’ would provide the ability to lookup an invoice, whether matched or not.

Ø        If an invoice is not matched, then modification is allowed

Ø       Selecting ‘Reconciliation’ will call a program (new) allows the user who has the proper authority to force the reconciliation of P/O receipts.  

This function would be used when, for example, a vendor ships 1050 of an item for which only 1000 was ordered on a P/O.  The additional quantity was not shipped in error, but rather as a precaution by the vendor to account for possible damage of goods in the shipment.  The additional quantity will be accounted for (reconciled) using this function.

Example 1

Receipt:

Accrued Payables                    Inventory
----------------------         --------------------
         |   1050              1050     | 
         |                              |

Invoice Entry:

Accrued Payables                  Accounts Payable               
----------------------          --------------------
       1050 |                             |  1000                    
            |                             |

            

Reconciliation:

Accrued Payables                        Variance
----------------------          -----------------------
       50 |                                |    50
          |                                |

·          The screen allows the entry of the PO number, and displays all lines that have not been reconciled.  Flags at the header allow the override of the reconciliation flag for duty, freight and brokerage (from Y to N or vice versa).           

The unreconciled amount is displayed, so the user knows what amount they are overriding. 

        Flags at the line level will allow the reconciliation of each PO line.  The line will show unreconciled quantities.

    Once in the invoice entry screen, the user would enter the transaction type, VI=Vendor Invoice (the default value in the field), or LC=Landed Cost.  Subsequent transactions entered will be of the same type until the transaction type is changed.  The user will enter a P/O number.

Ø       If more than one purchase order matches the number entered, then the user will be prompted to select from a list of matching purchase orders once the cursor moves to the next entry field.  Information related to that P/O number would populate other fields such as vendor name, terms, remit-to, discount, and line details from the P/O. 

Ø       If the P/O number is for a Drop Ship P/O, then an error message will warn the user to enter that invoice in the Drop Ship Confirmation screen.

Ø       The Qty invoiced should default to the total quantity that has been received , but that has not been reconciled, and the invoice cost should default to the PO Cost.

·         A flag for the freight, duty, and brokerage indicates whether or not freight, duty or brokerage has been applied to any receipts for this PO that have not been reconciled.  This flag will be used in the accounting distribution.  If an amount is entered, the user should not be able to change the flag.  If the flag is defaulted to N, then the flag should not be able to be changed.

·         For freight, duty or brokerage, if the flag defaults to Y (meaning amounts were applied to Accrued Payables at receipt, that have not already been reconciled) and no value is entered, then leave the value applied at receipt in Accrued Payables.

·         If the flag defaults to Y, and an amount is entered, the difference between the unreconciled amounts of receipts being processed and the entered amount will go to the Inventory Variance Account.

·         If the flag is changed to N, meaning that freight, duty or brokerage was applied, but will not be accounted for in an invoice at reconciliation, then debit Accrued Payables for the amount on the receipt, and credit the freight, duty, or brokerage account from the PO Organization.  These PO receipts will also need to be flagged as reconciled.

·         If the flag is defaulted to N, but an amount is entered, then post the value to the freight, duty, or brokerage account on the PO Organization.

·         The additional charges from the container should be posted to the Inventory Variance account when processing the VI.

·         If miscellaneous charges are applied to the invoice, then they should be applied to Inventory Variance at the time identified.

Ø       The user can specify whether or not an invoice is prepaid.  This flag will be used to reconcile the transaction with PeopleSoft.  If the invoice has been flagged as matched, then this change would be disallowed.

Ø       The user can override the values in the header fields.   The Remit To information is taken from the Head Office information in Vendor Maintenance (APVENDM1).  To change the Remit To, press F3 and enter the name of the new Remit To vendor (must be a valid vendor) or press F1 to see a list of vendor from which to choose.

Ø       All transactions require an invoice number.  If the P/O number and invoice number entered correspond to a matched invoice, then the transaction is disallowed.  If the transaction type is other than VI, then header amounts (Freight, Brokerage, Duty and Misc. 1 to 3) would be blank for the user to enter new amounts.

Ø       A running total will be kept and compared to the gross amount of the invoice.  The difference will be shown so that the user will know that some details have not yet been entered.

Ø       Additional lines, over and above P/O lines, can be added to the invoice.  The result is an unmatched invoice.

Ø       G/L distribution will be generated in the background.  If the user wishes to view and modify the G/L distribution, they can press F9 and the G/L distribution screen will appear.  Pressing F1 in this screen would mean accepting changes.

 

Invoice Match Override Program

  • Security for accessing this function will be governed by the setup done in User Group Maintenance (UTGROPM1).
  • The screen will ask for the vendor number, invoice number, PO Number (displayed) and whether or not you want to force the match (Y/N, default to N). 
     
    When the invoice is matched through this process, it should match to the PO, based on the reconciled quantities, and all accounting entries should be made. 
    If the invoice is for more than the total of the receipts, then the remaining unreconciled quantity would equal 0, and the difference would go to the variance account.
     If a line that was not received is on the invoice, and the match flag is changed, then the value of that line should be posted to variance.

Invoice-Matching Program

Create an invoice-matching program to match the invoice information entered in Vendor Invoice Entry and EDI 810 Inbound Invoice to receipts, purchase orders, and invoiced-to-date and tolerance values.  This program will be run as a batch function

  • Once the Vendor Invoice Entry program is exited, the user will be asked if they wish to run the invoice matching program or not.  If they answer ‘Y’ for Yes, then a parameter screen will present the option to process the current batch or all invoices.

Parameters are:

  • Organization (default to terminal)
  • From/To Reference Number
  • User Code
  • From/To Vendor
  • From/To Invoice Number
  • From/To Invoice Date
  • From/To Entry Date
  • From/To PO Number
  • Print 1 = All transactions (matched and unmatched)
  •          2 = Exceptions (only those not matched. default

Ø       The user will be prompted to run this program immediately upon exiting Invoice Reconciliation .  If the user accepts to run the Invoice Matching program, then the From/To Reference Number will contain the current reference number.

Ø       All invoices and adjustments entered in the session will be run through the matching program.   Optionally, all unmatched invoices (created in earlier invoice entry sessions or from EDI) will be run through the matching program. 

Matching Rules:

Ø       Matching rules verify the following:

  •           Does the invoice quantity match the received quantity?
  •           If the invoice quantity is greater than the received quantity, a matching error is flagged. 
    NOTE that received quantity should be net of invoiced (matched) quantities.  For example, if a P/O line is for quantity 100 of which 75 have been received and 50 already invoiced, then the received quantity should be 25 since that quantity has not yet been matched.
  •           Does the invoice price match the P/O price?
  •           If the invoice price is greater than the P/O price, does the difference between the extended amounts on the invoice line less the invoice quantity multiplied by the P/O price fall within the tolerance?
  •           If the difference is outside (greater than) the tolerance range (in dollars or percentage), a matching error is flagged.

Ø       The first error will cause the matching program to flag the invoice as an exception.  Matching rules will be applied the entire invoice and all subsequent errors will be noted in the exception log.

Ø        Matched invoices will have the “Matched” flag set to “Y”.   These invoices will be available for interfacing to PeopleSoft.

Ø        Once the invoice is accepted and processed by the matching program, then no further changes will be allowed.

Vendor Invoice Matching Exception Report

Create Vendor Invoice Matching Exception report.

Parameters are:

  • Organization (default to terminal)
  • From/To Reference Number
  • From/To Vendor
  • From/To Invoice Number
  • From/To Invoice Date
  • From/To Entry Date
  • From/To PO Number
  • Print 1 = Errors
  •          2 = Open (Unmatched)
  •          3 = Closed (Matched)
  •          4 = All transactions (matched and unmatched)
  • Details : Y or N

Ø       This report will list all invoices that have failed the matching process.  All invoices that have one or more matching errors will be listed in this report. 

Ø       An invoice has a “Matched: Y/N” flag that is set to “N” by default.  Once the invoiced is matched, either by the invoice matching program or by manually setting the “Matched: Y/N” flag to “Y”, the invoice will no longer appear on the Matching Exception report unless the printing option includes the closed invoices.

Ø       The report will list the invoices in error and will indicate what the error was and where it occurred, whether at the header and/or at the line.

Vendor Invoice Inquiry

Create Vendor Invoice Inquiry function.

Ø       This inquiry function will allow the user to call up any vendor invoice and any transactions related to that invoice.  The invoice can be viewed along with additional information that does not appear in the Vendor Invoice Entry screen such as P/O cost. 

Ø       The Invoice Inquiry screen could look like the following sample:

HEADER

12345678901234567890123456789012345678901234567890123456789012345678901234567890
1Vendor Invoice Inquiry       
2
3
4
5
6
7
8
9
0
1
2
3
4
5
6
7
8
9
0
1
2
3
4		 Menu:   Lines  Next  Previous  First  Last  Exit Help 


Organization: __                                  Ref.#      : ______                                                   Entry Date : ___________  
Trans. Type : __
PP/O Num    : _______     
Vendor      : ___________ _____________________________ 
Invoice #   : __________             Matched   : _  
Invoice Date: ___________            Remit to  : ___________            
Invoice Amt : ___________            Prepaid   : _                       
Terms       : __ [                    ]  
Discount    : ______                

                           Applied  
Freight     : ___________   [ ]      Misc. 1   : ___________             
Brokerage   : ___________   [ ]      Misc. 2   : ___________            
Duty        : ___________   [ ]      Misc. 3   : ___________             

LINES

12345678901234567890123456789012345678901234567890123456789012345678901234567890
1Vendor Invoice Inquiry       
2
3
4
5
6
7
8
9
0
1
2
3
4
5
6
7
8
9
0
1
2
3
4		  
Ref.#      : ______ 
Trans. Type : __                                  Entry Date : ___________
PP/O Num    : 9999999    Vendor : ZZZZZZZZZZZ zzzzzzzzzzzzzzzzzzzzzzzzzzzzz 
Invoice #   : __________   Invoice Date: ___________  
Invoice Amt : ___________      Prepaid   : _                    
--------------------------------------------------------------------------------
F4=Abort F11=Page Up F12=Page Down. 
P/O Item                       Qty Rcvd Qty Invcd    Invc. Cost      Extended  ZZZZZZZZZZZZZZZ                99999999 999999999 9999999.99999 9999999.99999
 ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ 
ZZZZZZZZZZZZZZZ                99999999 999999999 9999999.99999 9999999.99999 
 ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ 
ZZZZZZZZZZZZZZZ                99999999 999999999 9999999.99999 9999999.99999
 ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ 
ZZZZZZZZZZZZZZZ                99999999 999999999 9999999.99999 9999999.99999 
 ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ 
ZZZZZZZZZZZZZZZ                99999999 999999999 9999999.99999 9999999.99999 
 ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ 
ZZZZZZZZZZZZZZZ                99999999 999999999 9999999.99999 9999999.99999 
 ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
                                                     Subtotal   9999999.99999 

EDI 810 Inbound Invoice

Create an EDI 810 Inbound Invoice module to handle Vendor Invoices only (This BR has yet to be approved).

Ø       This new module will include:           

  •           Batch process validation
  •           Report
  •           Maintenance
  •           Purge

Ø       The following abilities must be part of the module:

  •           Ability to validate inbound data to ensure data integrity within the EliteSeries database.
  •           Ability to report on invalid data and identify problem areas.
  •           Ability to report on how data was distributed, whether to vend_inv tables or to ds_b* tables (for Drop Ship invoices).
  •           Ability to perform maintenance on inbound data.
  •           Ability to archive inbound data to provide an audit trail.
  •           Ability to seamlessly integrate the inbound data into the Elite database.
  •           Ability to process vendor invoices with the same functionality as the new Vendor Invoice Entry process.
  •           Ability to support invoices that are matched to P/O’s.

Ø       Additional functionality required includes the ability to process Drop Ship invoices:

  • It is assumed that if the first line for the invoice is Drop Ship, then the rest of the invoice is for Drop Ship.
  • If the EDI invoice is for a Drop Ship purchase order (po_h.dropship = “Y”), then the Drop Ship (ds_b*) tables will be populated with the invoice information. 
  • For Drop Ship Invoices, the Drop Ship Confirmation Listing and Update functions must be run separately.

PROJECT CONDITIONS/LIMITATIONS

Ø       The interface to PeopleSoft is not covered in this analysis. 

Ø       Invoice matching for EDI 810 will apply to Inventory Vendor Invoices only.

Ø       EDI Inbound Invoice functionality is based on a similar Inbound EDI Invoice 810 modification, but with modifications specific to Client A.