Changes to accounting_summary_source.sql,stanford

John Shott shott at snf.stanford.edu
Wed Sep 18 08:44:19 PDT 2002


Bill:

I've made some changes (not checked in, yet) to
labnet/accounting/billing/site-specific/accounting_summary_source.sql,stanford

These changes don't in any way (I'm pretty certain) affect anything that
happens to acct_sum ... but it adds some capability (and another table) that I
think may be useful.

This was originally motivated by questions such as "How much do we 'earn' in
equipment fees for the micronic ... or any other piece of equipment".  The
simplistic model is that we earn $72 per hour of use for most pieces of
equipment until they reach the cap ... then it is free after that.  However,
that analysis breaks down if it is applied to all pieces of equipment.

A more precise analysis, I think, would say that if someone used twice the
capped amount of equipment time in a month that they effectively paid $36 per
hour for each hour of equipment use ... and that none of it is actaully free,
but all of it generated less than $72 per hour of use.

I believe that I have a new table called item_acct_sum that is similar in
structure to acct_sum except that it has an item field ... for eq_activities,
item is, of course, the equipment.  Then what I have stored in the tally,
amount, and cost fields is the number of times they used that equipment, the
total number of minutes that they used the equipment, and the "scaled" cost
for using that equipment.  Originally in acct_sum_raw, we have the cost before
any caps are applied.  Later, in acct_sum_working, we apply equipment-specific
caps, and later, in acct_sum we apply global caps.  Basically, what I have
done is taken any place where a cap has been applied and used the ratio of the
capped charge to what the uncapped charge would be to go back and scale the
"cost" field in item_acct_sum to reflect the reduced average hourly rate of
equipment use.  I'm probably not explaining this well, but the calculation
isn't as difficult as I'm making it sound ... Let me see if I can do a better
job with an example.  Suppose someone used 2 pieces of equipment in a month:
amtetcher for 1000 minutes and tylannitride for 2500 minutes.  The raw charge
for their amtetecher use would be $1200 and $3000 for tylannitride (at $1.20
per minute with no cap applied).  This would have given them a raw total cost
of $4200 ... which is of course reduced to $1400 by imposition of the cap. 
Effectively, by my reasoning, what they actually paid for their amtetcher use
was $1200 * ($1400/$4200) = $400 and what they actually paid for their tylan
nitride use would have been $3000 * ($1400/$4200) = $1000.

So, in the item_acct_sum table we have:
member, project, ...., item, cost, amount ....
X, Y,..................,amtetcher,$400,1000, ....
X, Y,..................,tylannitride,$1000,2500, ....

I think that there are two benefits to this table:

1. It allows us to make a better determination of how much income each piece
of equipment actaully generates that may be of use in determining when
something should be retired, etc.

2. It gives us a better level for providing "accounting backup" to users when
they question their bills.  At the moment we have the total dollar amount of
equipment usage from acct_sum or we can provide the gory details of equipment
use from activity (but which has no costs associated with them) ... With this
table, we can now provide an intermediate level of detail that includes for
each piece of equipment the number of times they used the equipment, the total
number of minutes, and the "effective" cost of using that equipment ... and
the sum of all of the effective costs will equal the "EquipUse" item on their
invoice even if they reached the cap.

As I say, I've alread made some changes to my version of
accounting_summary_source.sql,stanford that you can find in
~shott/labnet/accounting/billing/site-specific.  In this file, there are two
sections that I've modified that are each bounded by a pair of comments that
are something like:
/* Start of J. Shott additions ... */
/* End of J. Shott additions ... */

Also, the details of the structure of the item_acct_sum table are included in
~shott/labnet/sql/accounting.sql (that is also not yet checked in).

While I didn't want to pull you away from your other activities, I'd
appreciate it if you could find the time to look at this and make sure that I
haven't done anything stupid ...

Thanks,

John



More information about the coral mailing list