Resource client "works_on" and "charges_to" functionality ...

John Shott shott at
Fri Jun 8 11:43:33 PDT 2001

Bill and Mike:

Now that we have the 3-panel template for the resource client (I know that
there are things that still need to be added to that portion, but ...), I
thought I'd give you my  thoughts related to what additional functionality we
need to be able to release this.

At the moment, for member, project and account, we have "Add", "Edit", and
"Delete" in each of their menus.  (Note: we still need to decide what we mean
when we say "Delete", but ...).

The next most important thing, from my perspective, that is the functionality
that will let us deal with the "works_on" relationships between member and
project and the "charges_to" relationships between project and account.  I
think that this functionality is a higher priority that searching ....

Following is a first pass at defining the menus that I think would be useful
for member, project, and account .... 

For the member menu, I would suggest (using --- as a menu separator, and
indentation of some of the "For selected menu ..." sub entries.

Suggested Member menu:

For selected member ...
	Set default project
	Add project to list
	Remove project from list
	Replace project with ...
	Show active projects
	Show all projects

Suggested Project menu:

For selected project ...
	Set default account
	Add account to list
	Remove account from list
	Replace account with ...
	Show active accounts
	Show all accounts
	Show active members
	Show all members

Suggested Account menu:

For selected account ...
	Show active projects
	Show all projects

Here is my description of what I think that each of these menu items should

The "Set default xxx" would prompt for a project or account, as appropriate,
and change the existing project or account field in member or project,
respectively, to that value.  It would also make sure that the new default was
in the charges_to or works_on table and add it if need be.  Of course, it
would give an error if that project or account didn't exist or wasn't active.
(Presumably, clicking on the appropriate project or account would also enter
that value).

The "Add xxx to list" would prompt for a project or account, as appropriate,
and add the appropriate entry to the works_on or charges_to table (but not
change the default).

The "Remove xxx from list" would prompt for a project or account, as
appropriate, and "remove" the appropriate entry from the works_on or
charges_to table. Actually, be remove I mean, set active = 0, set edate =
SYSDATE, and set revoker = 'whomever is running the application'  ... in other
works, it makes an entry in either works_on or charges_to inactive. Note: this
would also need to give an error if the entry being removed as either the
default project or account.

The "Replace project with ..." or "Replace account with ..." is probably one
of the most important, but also one of the trickest things to do.  Part of the
reason that this is tricky is that it is often done retroactively to the start
of the month (or even the start of last month) ... and appropriate database
changes need to be made.
Basically, what I view as happening is a popup window with the Old Project,
the New Project, and the Effective Date. (I'll do this for changing project
... but similar things should happen for changing account).
When they click "OK", here is what I think should happen.
1. Check to see that "Old Project" and "New Project" already exist in the
project table. Abort if Old Project doesnt' exist. Abort or maybe prompt for
new project if it doesn't exist.
2. Get member/old project entry from works_on: set revoker, edate = 'effective
date', active = 0.
4. Get member/new project entry from works_on (or create it if it doesn't
exist), set approver, bdate = 'effective date', active = 1.
5. Make appropriate changes in eq_activity, staff_activity, training,
inven_activity, and reservation:
Change old project to new project, old project's default account to new
project's default account where member = 'member' and project = 'old_project'
and account = 'old_projects default account' and edate >= 'effective_date'

The "Show active xxx" entries should, more or less, show the results of the
following queries:
	select * from (works_on or charges_to)
	where master = 'this_member or this_project'
	and active = 1;
	select * from (works_on or charges_to)
	where slave = 'this_project or this_account'
	and active = 1;
Note: whether the query involves "master" or "slave" depends on whether we are
searching "downstream" (from member to project to account) or "upstream" (from
account to project to member).

Of course, the "Show all xxx" would be the same thing ... but without the
active = 1 clause.

In either case, I envision a popup window with a table with columns that are
exactly those in the works_on or charges_to tables: master, slave, approver,
revoker, bdate, edate, active.  I'm not sure that I've got a strong opinion
yet of the degree to which this table should be editable ... I need to think
more about that.

Sorry about this long winded message ... but I thought that I should try to
write down the things that I feel are most important to allow the resource
client to be fully functional.  In particular, it seems as if juggling entries
in the works_on and charges_to tables is a fact of life ... particularly the
"oops, this account/project (and all activities) need to be changed effective
some date in the past".

I'm sure that this will generate questions, discussion, and differences of
opinion, but I thought I'd take a stab at writing down my initial thoughts.



More information about the coral mailing list