<div dir="ltr">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):<div><br></div><div><a href="https://roganhamby.github.io/emeraldelephant/">https://roganhamby.github.io/emeraldelephant/</a><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, Oct 27, 2022 at 10:53 AM Lindsay Stratton via Evergreen-reports <<a href="mailto:evergreen-reports@list.evergreen-ils.org">evergreen-reports@list.evergreen-ils.org</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">Hi Blake, <div><br></div><div>Thanks for the detailed response!</div><div><br></div><div>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!</div><div><br></div><div>Lindsay</div><div><br clear="all"><div><div dir="ltr"><div dir="ltr"><div><div dir="ltr"><b>Lindsay Stratton</b><div><b>Systems Librarian</b></div><div>Westchester Library System</div><div>570 Taxter Rd., 4th Floor</div><div>Elmsford, NY 10523</div><div><a href="mailto:lstratton@wlsmail.org" target="_blank">lstratton@wlsmail.org</a></div></div></div></div></div></div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, Oct 27, 2022 at 10:36 AM Blake Henderson via Evergreen-reports <<a href="mailto:evergreen-reports@list.evergreen-ils.org" target="_blank">evergreen-reports@list.evergreen-ils.org</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div>
Lindsay,<br>
<br>
money.billable_xact<br>
<br>
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.<br>
<br>
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.<br>
<br>
likewise: money.grocery<br>
<br>
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.<br>
<br>
So those are the "whole bill" tables. Then we have other tables that
account for the "billing line items" and "payment line items"<br>
<br>
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.<br>
<br>
Payments are a complicated matter because there are so many
different types of payments. A the root, we have money.payment.<br>
<br>
beneath money.payment, we have a child table: money.bnm_payment<br>
<br>
money.bnm_payment is again another parent to several child tables:<br>
<br>
money.goods_payment<br>
money.forgive_payment<br>
money.bnm_desk_payment<br>
<br>
money.bnm_desk_payment is parent to these:<br>
<br>
money.cash_payment<br>
money.check_payment<br>
money.credit_card_payment<br>
money.debit_card_payment<br>
money.account_adjustment<br>
<br>
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.<br>
<br>
and again, for all of these payment tables, "xact" is a reference to
money.billable_xact.<br>
<br>
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:<br>
<br>
money.payment_view<br>
<br>
That view will give you all of the information about the payment
and* give you the "payment type" as a column.<br>
<br>
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.<br>
<br>
xact_start will have a timestamp when the transaction begins (upon
circulation or grocery bill).<br>
<br>
xact_finish is updated when the bill is paid off and* (in the case
of a circulation transaction) the item is returned.<br>
<br>
<pre cols="72">-Blake-
Conducting Magic
Will consume any data format
MOBIUS
</pre>
<div>On 10/27/2022 8:41 AM, Lindsay Stratton
via Evergreen-reports wrote:<br>
</div>
<blockquote type="cite">
<div dir="ltr">
<div>Hi all - </div>
<div><br>
</div>
<div>Kind of a random nitpicky question about Evergreen data
tables...</div>
<div><br>
</div>
<div>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?</div>
<div><br>
</div>
<div>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?</div>
<div><br>
</div>
<div>Lindsay</div>
<div dir="ltr"><br>
</div>
<div dir="ltr"><br clear="all">
<div>
<div dir="ltr">
<div dir="ltr">
<div>
<div dir="ltr"><b>Lindsay Stratton</b>
<div><b>Systems Librarian</b></div>
<div>Westchester Library System</div>
<div>570 Taxter Rd., 4th Floor</div>
<div>Elmsford, NY 10523</div>
<div><a href="mailto:lstratton@wlsmail.org" target="_blank">lstratton@wlsmail.org</a></div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<br>
<fieldset></fieldset>
<pre>_______________________________________________
Evergreen-reports mailing list
<a href="mailto:Evergreen-reports@list.evergreen-ils.org" target="_blank">Evergreen-reports@list.evergreen-ils.org</a>
<a href="http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports" target="_blank">http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports</a>
</pre>
</blockquote>
<br>
</div>
_______________________________________________<br>
Evergreen-reports mailing list<br>
<a href="mailto:Evergreen-reports@list.evergreen-ils.org" target="_blank">Evergreen-reports@list.evergreen-ils.org</a><br>
<a href="http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports" rel="noreferrer" target="_blank">http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports</a><br>
</blockquote></div>
_______________________________________________<br>
Evergreen-reports mailing list<br>
<a href="mailto:Evergreen-reports@list.evergreen-ils.org" target="_blank">Evergreen-reports@list.evergreen-ils.org</a><br>
<a href="http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports" rel="noreferrer" target="_blank">http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports</a><br>
</blockquote></div><br clear="all"><div><br></div>-- <br><div dir="ltr" class="gmail_signature"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div style="color:rgb(136,136,136);font-size:12.8px"><span style="font-size:12.8px">Andrea Buntz Neiman</span><br></div><div style="color:rgb(136,136,136);font-size:12.8px">Project Manager for Software Development</div><div style="color:rgb(136,136,136);font-size:12.8px">Equinox Open Library Initiative</div><div style="color:rgb(136,136,136);font-size:12.8px"><a href="mailto:abneiman@equinoxoli.org" target="_blank">abneiman@equinoxOLI.org</a></div><div style="color:rgb(136,136,136);font-size:12.8px"><span style="font-size:12.8px">1-877-OPEN-ILS </span><span style="font-size:12.8px">(673-6457) x5583</span><br></div><div><font color="#0000ee"><u><a href="https://www.equinoxOLI.org/" target="_blank">https://www.equinoxOLI.org/</a></u></font><br></div></div></div></div></div></div>