Bulk processing cust/vendor payments

[Geert Janssens]

On Wednesday 24 August 2016 18:22:12 yary wrote:
> I'm converting into GnuCash my businesses books from an on-line system
> that went down mid-year. Successes so far- loading customers,
> vendors, invoices and bills all via "File->Import" and csv files.
> 
> I am also able to convert all the records I need into QIF and import
> them into GnuCash. However,the QIF import makrs none of the bills or
> invoices as "paid," even though the A/R and A/P, bank accounts are
> properly credited/debited.
> 
> 1. Is there any "bulk process payment" for bills/invoices that reads a
> file, of any format? If there is, then I'll use that, and remove the
> matching transactions from the QIF
> 
> 2. If #1 isn't possible, then maybe I should keep they payments in the
> QIF file and mark invoices as paid using SQL. Is setting
> "Lots.is_closed=1" completely sufficient for marking an invoice as
> "paid"- or do I also need to tie the payment transaction guid to
> another table?
> 
> I've browsed the ERD at
> http://wiki.gnucash.org/wiki/images/8/86/Gnucash_erd.png , and also
> paid a couple invoices through the normal interface to see how the
> tables change, but still not sure I understand all that changes when
> processing payments.
> 
> My day job involves SQL and text-file processing, so I'm comfortable
> with creating files, updating tables as needed for this task.
> 
> -y

Hi,

If you don't care too much about matching your payments exactly to the 
invoice,  but only care to get all paid invoices marked as paid, you 
could do this (to repeat once for each customer and vendor):

1. Select Business->Customer->Process Payment...
2. In the owner field select your first customer
3. In the document overview you can now highlight all invoices that are 
paid together with all payments for them. You can select multiple by 
using shift-click or ctrl-click.
4. If all is well the amount left to pay should now be 0. If not, double 
check your selection.
5. Hit ok.

Repeat for each customer and each vendor.

If you do care about exact assignments of your historical payments, you 
can use the "Assign as payment" option on each payment transaction. 
Depending on the size of your history this will take you a while.

As for going the sql route, this is slightly tricky because not all data 
involved in paying an invoice is normalized in sql tables.

Some additional info on the payment process to get a rough idea on how 
it works (from the top of my head):

1. You don't mark invoices as paid. Instead you assign payments to 
invoices and gnucash decides when sufficient amount of payment is 
assigned to an invoice to fulfill it. This can come from one payment or 
from multiple, and even credit notes can play a role here.

2. The way gnucash ties invoices and (parts of) payments together is via 
lots. You will find a lots table in sql as well.

3. Each (posted) invoice is associated with exactly one lot. That is, 
the AR split of the invoice transaction is linked with that lot. 
Payments are added to the lot by linking the AR splits of those payments 
to the same invoice lot. As soon as the amounts of all splits in the lot 
balance out, the invoice is marked as paid.

4. You can also have overpayments. In that case the AR split for the 
payment is split in two: the first split to match the amount of the 
invoice, and the remainder is parked in a spare lot for a future invoice 
payment.

5. On payment can also pay multiple invoices at once on the same 
principle: the AR split for the payment will be split up in as much 
splits as there are invoices to pay and each of these smaller splits is 
then linked to the relevant invoice lot for the invoice it pays.

All of this can be relatively easily reproduced in sql if you have the 
proper details on payments and invoices. Aside of this information there 
are a few entries in the slots table that relate to lots as used for 
payments. This is the shadowy part for which I don't know all the 
details either without careful code inspection.

One such entry will store the account used for the last payment for each 
customer vendor. This is only convenience and not mandatory.

More important is the invoice number for a given lot, or in absence of 
an invoice (for pre-payment lots), the customer the lot is related to. 
Both are also somewhere in a slot and are relevant for proper 
functioning.
A quick glance on the slots table and the code suggest you need these 
slots:
"title"
"gncInvoice", "gncInvoice/invoice-guid" (if an invoice is associated 
with the lot)
"gncOwner", "gncOwner/owner-type", "gncOwner/owner-guid" for outstanding 
payments not associated with an invoice yet.

Note the slots table is not normalized and hierarchical. For example the 
gncInvoice slot stores the guid needed to find the slot 
gncInvoice/invoice-guid. Gnucash will recursively search these to get to 
the eventual invoice-guid it needs. The same goes for the gncOwner and 
its subslots.
The obj_guid is the foreign key to link back to the lots (and other 
tables for that matter).

title is only used in the lot viewer and is usually in the format 
"Invoice <number>" or "Bill <number>".

The values to use for gncOwner/owner-type are defined in code. The 
relevant ones during import are:
2 = customer
4 = vendor
5 = employee

Hopefully this will help you sort out your payments vs invoices.

Good luck!

Geert