Reconciling invoices against orders

[pncbiz]

This is a high-level question. I'm building an ITFM database and am getting close to a 1.0 version. I've built modules for budgeting, contracts, and requisitions. Now I'm building the invoice module. The purpose of this module is to reconcile the invoices we pay against the orders we have placed (contracts and requisition).

Invoices USUALLY match a contract or requisition line for line, but not always. Occasionally, we'll have partial fulfillment of a requisition or a contract will be billed for different services at different times.

Bringing it down to the specific use case, I have a one to many relationship between contracts/requisitions (Order Table) and products (OrderProduct Table), so one contract or requisition can include multiple products.
I have an Invoice table to record the details of Invoices and a many to many join table between Orders and Invoices. That gives me the most flexibility between Orders and Invoices. Currently, the form for invoice entry has a search box on it for the order (requisition or contract). Once the user clicks on the order, it brings up a blank invoice form for the user to populate and lists the products on the contract.

However, that edge case where an invoice only covers partial fulfillment is problematic. I need to be able to allow for partial fulfillment scenarios by allowing Invoices reconcile directly against the product (vice the entire contract/requisition), but I also don't want to force the user to have to tick off every product if the invoice is a complete fulfillment of the order.

Would love to hear your thoughts about the best way to do this from a design perspective. My initial thoughts are:
1) User indicates if they have a partial or complete fulfillment invoice, then present either contract/requisition search OR product search. Problem there is the user may not know if it's partial/complete until they compare what they have against what should be there.
2) User searches for the order, clicks, and in the product list can tick off which ones are covered in the invoice, as well as enter the other details.
Any others?


Thanks,
Phil

[CJ_London]

suggest in your second idea, you might want to include a 'select/deselect all' option.

Not sure you have thought this through sufficiently - is the user matching against a quantity or a value or both?

lets say you have ordered an item for £10, the invoice comes in at £11 - what do you do then?

Or you have ordered 9 items and get invoiced for 8 (or 10)

or you ordered 10 at £10 each and get invoiced for 9 at £11 each

and the question is what do you actually do? what is your business process/rules? reject the whole invoice? only accept those lines that are an exact match? pay it and argue about it later? Do you have evidence that the price on your order is correct i.e. it is one the supplier has quoted you for and not a whim or an old price?

Another process you need to consider is if a user reconciles an invoice and subsequently (perhaps days later) realises they have assigned the invoice to the wrong contract or the wrong part of the contract. What is the process/rule to undo the original reconciliation and correct it?

Without knowing this, not really feasible to make suggestions.

[pncbiz]

Ajax,
Thanks for the input. Actually I started forging ahead with #2, and I think I've figured out a path forward from a high level (technical issues plaguing me...but I'll post those in the appropriate forum).
I am considering precisely the issues you raised.
From a business process perspective, if we are invoiced differently to what we ordered (quantity or price) we don't pay until the issue is resolved, but the resolution may mean we accept the different terms (supplier may have limited stock, for instance, or a rebate may have expired. All have happened recently). Therefore the database needs to be flexible enough to accommodate that potential.
So what I did was build a subform based on a query that pulls all the products associated with the order selected in the first subform. I then added fields to the OrderProduct table (QuantityInvoiced, UnitCostInvoiced, Paid Amount) to allow for a delta between what was ordered and what was invoiced. The subform populates those fields with the original order Quantity and UnitCost so as to make data entry less intensive, and the Paid Amount column is prepopulated with QuantityInvoicedXUnitCostInvoiced. If the invoice is different, the user just makes the change in those controls, preserving the original order data.
As for mistaken association - I'll have to think on that for a while. For the moment, the best I can think of is to put in a delete button to clear the three fields I just mentioned, clearing the reconciliation. I don't think this is a highly probably scenario, however, because one would have to not only match the wrong contract, but also the wrong products. Seems at some point they'd see it. That's why I don't think I need to invest in a sophisticated reassignment module - hit that delete button enough and the user will learn to do a better job of reconciling....
Thanks,
Phil

[CJ_London]

however, because one would have to not only match the wrong contract, but also the wrong products

I don't know your business but I've certainly come across the situation where there are multiple contracts which require same quantity and item from same supplier

good luck with your project