[Evergreen-reports] [Evergreen-general] Next Reports Interest Group Meeting - October 26 at 3 PM at the Evergreen Hack A Way!

[Blake Henderson]

Lindsay,

money.billable_xact

Is the "root". Rows in here are the definitions of a whole bill. The ID 
numbers in this table are exposed on the Evergreen interface in the 
billing section.

action.circulation shares the exact same ID numbers as 
money.billable_xact because action.circulation is a "child" table of 
money.billable_xact. You'll notice that action.circulation has many of 
the same columns as money.billable_xact. They are one and the same. 
action.circulation appends more columns that makes it a circulation.

likewise: money.grocery

money.grocery is a child table of money.billable_xact. You'll notice the 
same column names between them. With two additions: billing_location, note.

So those are the "whole bill" tables. Then we have other tables that 
account for the "billing line items" and "payment line items"

money.billing contains the raw charges for any given bill. You'll notice 
this table has a column for "xact". That column should match an ID 
number from money.billable_xact. It's a many-to-one relationship. Where 
money.billing is the "many" and money.billable_xact is the "one". "Full 
details" in the Evergreen interface will show these rows on the screen.

Payments are a complicated matter because there are so many different 
types of payments. A the root, we have money.payment.

beneath money.payment, we have a child table: money.bnm_payment

money.bnm_payment is again another parent to several child tables:

money.goods_payment
money.forgive_payment
money.bnm_desk_payment

money.bnm_desk_payment is parent to these:

money.cash_payment
money.check_payment
money.credit_card_payment
money.debit_card_payment
money.account_adjustment

But you can almost* ignore that string of relationships. Just know that 
money.payment is the parent to all* of the "payment" type tables above. 
you'll notice the column names are shared with each one, and each one 
appends one or more columns.

and again, for all of these payment tables, "xact" is a reference to 
money.billable_xact.

So, when money is paid in Evergreen, you can rely on the resulting row 
showing up in money.payment. But you'll have to look in one of the other 
tables to figure out which "type" of payment it was. This complexity is 
why we have some handy database views. This one is nice:

money.payment_view

That view will give you all of the information about the payment and* 
give you the "payment type" as a column.

I'll also point out another table that does a lot of the leg work for 
us. Each time a bill/payment is made in Evergreen, there is a special 
table: money.materialized_billable_xact_summary that receives an update. 
It keeps a running total and balance for any given xact.

xact_start will have a timestamp when the transaction begins (upon 
circulation or grocery bill).

xact_finish is updated when the bill is paid off and* (in the case of a 
circulation transaction) the item is returned.

-Blake-
Conducting Magic
Will consume any data format
MOBIUS

On 10/27/2022 8:41 AM, Lindsay Stratton via Evergreen-reports wrote:
> Hi all -
>
> Kind of a random nitpicky question about Evergreen data tables...
>
> In the database, I'm looking at money schema, specifically the various 
> billable/billing summary tables and wondering if the transactions 
> included that do not have last billing anything or total owed values 
> were returned before accruing any fines, or circ transactions that 
> would never have accrued fines because of non-fine bearing circ rules?
>
> Looking at xact_start and xact_finish dates for those records, it 
> looks like the former. But... I'm a little hesitant. Does anybody know?
>
> Lindsay
>
>
> *Lindsay Stratton*
> *Systems Librarian*
> Westchester Library System
> 570 Taxter Rd., 4th Floor
> Elmsford, NY 10523
> lstratton at wlsmail.org
>
> _______________________________________________
> Evergreen-reports mailing list
> Evergreen-reports at list.evergreen-ils.org
> http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://list.evergreen-ils.org/pipermail/evergreen-reports/attachments/20221027/7f61a8b8/attachment.htm>