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

[Andrea Buntz Neiman]

Putting in a plug for Rogan Hamby's Emerald Elephant site, which is where I
learned that fact about money.billable_xact being the parent table (among
other fun facts about EG database things):

https://roganhamby.github.io/emeraldelephant/

On Thu, Oct 27, 2022 at 10:53 AM Lindsay Stratton via Evergreen-reports <
evergreen-reports at list.evergreen-ils.org> wrote:

> 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
>>
> _______________________________________________
> Evergreen-reports mailing list
> Evergreen-reports at list.evergreen-ils.org
> http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports
>


-- 
Andrea Buntz Neiman
Project Manager for Software Development
Equinox Open Library Initiative
abneiman at equinoxOLI.org <abneiman at equinoxoli.org>
1-877-OPEN-ILS (673-6457) x5583
*https://www.equinoxOLI.org/ <https://www.equinoxOLI.org/>*
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://list.evergreen-ils.org/pipermail/evergreen-reports/attachments/20221028/f4b03088/attachment.htm>