sql query to link invoice payments to invoice id?

[Derek Atkins]

Hi,

On Sun, January 9, 2011 7:40 pm, Jannick Asmus wrote:
> Hi,
>
> I am trying to identify the invoice payments TOGETHER with the
> associated invoice in the sqlite file of my GnuCash file. Since there is
> a window to process the payment, I suspect there should be a link
> between the payment and the invoice guid.
>
> I am aware that the payment amount need not be identical to the invoice
> amount. As there might be slight differences between these two amounts
> in reality anyway, I prefer not to have a query using the invoice
> amount.
>
> Any ideas on this highly appreciated!

The way they are mapped is via the LOT in the A/R account.  When you post
an invoice is "creates" a lot, and then payments are applied to that lot
until the balance reaches 0 (at which point any remaining proceeds get
applied to the next lot owned by the same customer).

So, the mapping would be via the lot's in the splits in the A/R account
for the payment transaction.  Note that you could have multiple splits in
the A/R account in the case of a payment being applied to multiple
invoices.

I cannot tell you how to do this in SQL.  But you can do it in the GnuCash
API using gncInvoiceGetPostedLot() and then iterating the transactions in
the lot.  (There are other ways as well).

> /J.

> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.

-derek