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

[Lindsay Stratton]

Hi Blake,

Thanks for the detailed response!

What I had not realized is that action.circulation is the child of
money.billanble_xact - I thought it was the other way around, and this info
changes evvvvrything!

Lindsay

*Lindsay Stratton*
*Systems Librarian*
Westchester Library System
570 Taxter Rd., 4th Floor
Elmsford, NY 10523
lstratton at wlsmail.org


On Thu, Oct 27, 2022 at 10:36 AM Blake Henderson via Evergreen-reports <
evergreen-reports at list.evergreen-ils.org> wrote:

> 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 listEvergreen-reports at list.evergreen-ils.orghttp://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports
>
>
> _______________________________________________
> 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/4ea986f6/attachment-0001.htm>