EHS Authorized Projects - phase 1 specifications ================================================ (last modified by Jim Repa, 1/8/2004) Summary ------- Within the Radiation Protection Office, data is currently kept on "authorized projects". An Authorized Project is identified by an Authorization Number (also called Authorized Project Code in this document). It has a primary PI and possibly one or more secondary PIs, it is associated with a DLC, and it has a list of people known as authorized workers. It also is associated with a list of rooms, in each of which there may be one or more nuclides. We will generalize these nuclides and call them "substances" in this document, so that the system could be generalized to handle biological or chemical substances as well. For each substance for a project, there is an authorized amount (i.e., the largest amount permissible at one time that has been approved), and a current amount. For each type of substance in general, there is a "trigger amount" of the substance -- if an amount of the substance greater than the "trigger amount" is ordered or requested, a review process is triggered. It is important that the training system be able to handle reporting organized around the list of authorized workers in a given authorized project. In order to support this, the minimal amount of data that needs to be gathered and maintained for authorized projects includes (1) the Authorized Project Code, (2) the primary PI, (3) the DLC, and (4) the list of authorized workers. The rooms and substances are not considered essential for phase 1 of the implementation of an Authorized Projects component. The plan is to enhance the EHSWEB system to support the minimal data about authorized projects required for training reporting purposes. Tables, stored procedures, and a web-based user interface will be built. There will be a user interface for specified individuals within the EHS Office (specified with a special authorization in the Roles DB) that allows them to insert and maintain authorized project information, including the list of authorized workers. There will also be a reporting interface, with authorizations controlled by the Roles Database, that will allow authorized individuals to report on authorized projects and their workers based within a DLC. Additional data will be uploaded to the Data Warehouse to support training reports that include the Authorized Project Code. Enhancements to the web-based training reports may be made as well to support this. Summary of phase 1 technical implementation ------------------------------------------- We're going to build an extension to the tables, stored procedures, and web interface on EHSWEB to support "authorized projects". An "authorized project" is a research or other project that has the following attributes: - An "authorized project number" (formerly known to EHS people as an "authorization number") (This will be stored in the column AUTH_PROJ_CODE) - An authorized project name - A DLC - A primary PI - Optionally, one or more secondary PIs - An optional description - A list of authorized workers (identified by the Kerberos usernames) There are more attributes that we will *not* include in phase 1: - A room set - A set of radionuclides or other controlled substances, each of which has * A code and/or name * An authorized amount * A currently-reported amount in the room set * "has-protocol", i.e., a flag indicating whether or not this particular substance has a "protocol" on file for this authorized project. (A "protocol" is a written document describing the proposed use, amount, etc., for this particular substance within this project) Note that the items in the second set of attributes, those that will not be supported in phase 1, are left out of phase 1 because we do not yet have enough information on how to implement them within EHSWEB and we do not yet know how they will fit together with plans for reimplementing the PI Space Registration component. In fact, it is possible that the work done on Phase 1 will be subsumed by a redesigned version of the PI Space Registration some time in the future. (Nevertheless, the "authorized project" features are an immediate requirement, so we need to build phase 1 now.) There are other entities related to Authorized Projects that we will not support in phase 1. We do not need to implement these items yet, and will decide how and where to implement the features in the future. - Trigger amounts for radionuclides or other substances Independent of individual authorized projects, each radionuclide will have a "trigger amount", above which special reviews or approvals are triggered. - Requests/orders for radionuclides or other substances Each request will have * an authorized project number * a substance code * a requested amount * a room number where it is to be delivered. * a person ordering the substance Tables ------ EHS_AUTHORIZED_PROJECT auth_proj_id number(10) NOT NULL (Generated by a sequence) auth_proj_code varchar2(15) NOT NULL auth_proj_name varchar2(30) NOT NULL dept_id number(12) NOT NULL pi_kerbname varchar2(8) NOT NULL auth_proj_desc varchar2(2000) modified_by varchar2(8) (kerberos name; no constraints) modified_date date Constraints and notes: - auth_proj_id is the unique primary key - auth_proj_number must be unique as well - dept_id must match a dept_id in the DEPT table - pi_kerbname must match a kerberos_name in the PERSON2 table. EHS_AUTH_PROJ_SECONDARY_PI auth_proj_id number(10) NOT NULL kerberos_name varchar2(8) NOT NULL Constraints and notes: - unique primary key is (AUTH_PROJ_ID, KERBEROS_NAME) - auth_proj_id must match an auth_proj_id in EHS_AUTHORIZED_PROJECT - kerberos_name must match a kerberos_name in the PERSON2 table EHS_AUTH_PROJ_WORKER auth_proj_id number(10) NOT NULL kerberos_name varchar2(8) NOT NULL Constraints and notes: - unique primary key is (AUTH_PROJ_ID, KERBEROS_NAME) - auth_proj_id must match an auth_proj_id in EHS_AUTHORIZED_PROJECT - kerberos_name must match a kerberos_name in the PERSON2 table For authorization control: functions in the Roles DB and EHS ------------------------------------------------------------- Function name Qualifier type Roles->EHS In EHS only ------------------------------ -------------- ---------- ----------- VIEW AUTHORIZED PROJECT DEPT Yes No MAINTAIN AUTHORIZED PROJECT DEPT Yes No PROXY FOR EHS AUTH PROJECT NULL No Yes Stored procedures and stored functions -------------------------------------- EHS_SF_CAN_MAINT_AP (ai_user IN STRING, ai_dept_code IN STRING) Returns 'Y' if the user is allowed to maintain authorized projects under the given dept_code, 'N' if not EHS_SF_CAN_VIEW_AP (ai_user IN STRING, ai_dept_code IN STRING) Returns 'Y' if the user is allowed to update authorized projects under the given dept_code, 'N' if not EHS_SP_ADD_PROJECT (ai_for_user, ai_proj_code, ai_proj_name, ai_dept_code, ai_pi_kerbname, ai_description, ao_auth_proj_id, ao_message) Checks the calling user's authority to act as a proxy for others for EHS projects. Checks for-user's authority to maintain auth projects within the given dept_code. Raises AI_PROJ_CODE, AI_DEPT_CODE, and AI_PI_KERBNAME to upper case. Checks possible errors in the various parameters and returns a hand-coded user-friendly error message for each. Inserts the new record into EHS_AUTHORIZED_PROJECT (setting MODIFIED_BY to upper(AI_FOR_USER) and MODIFIED_DATE to SYSDATE). Sets the return variable AO_AUTH_PROJ_ID to TO_CHAR(the new auth_proj_id) Sets ao_message to a message ("Project was successfully added") EHS_SP_DELETE_PROJECT (ai_for_user, ai_proj_code, ao_message) Checks the calling user's authority to act as a proxy for others for EHS projects. Checks for-user's authority to maintain auth projects within the given dept_code. Looks up record for the given proj_code in EHS_AUTHORIZED_PROJECT to check for existence and to get the auth_proj_id. Raises AI_PROJ_CODE to upper case. For possible errors in the various parameters, returns a hand-coded user-friendly error message for each. Deletes related records in EHS_AUTH_PROJ_SECONDARY_PI and EHS_AUTH_PROJ_WORKER with a matching auth_proj_id number. (Do this with two explicit delete statements; do not use CASCADING CONSTRAINTS which can be dangerous if we change the schema in the future.) Deletes the record Sets ao_message to a message ("Project has been deleted") EHS_SP_UPDATE_PROJECT (ai_for_user, ai_old_proj_code, ai_new_proj_code, ai_proj_name, ai_dept_code, ai_pi_kerbname, ai_description, ao_message) Similar to EHS_SP_ADD_PROJECT, except that it updates an existing record. If there are no changes, then return an error message and do not do the SQL update statement. If there are changes, remember to update MODIFIED_BY and MODIFIED_DATE as well. EHS_SP_ADD_AP_WORKER (ai_for_user, ai_proj_code, ai_worker_kerbname, ao_message) Checks the calling user's authority to act as a proxy for others for EHS projects. Checks for-user's authority to maintain auth projects within the given dept_code. Raises AI_PROJ_CODE and AI_WORKER_KERBNAME to upper case. Looks up record for the given proj_code in EHS_AUTHORIZED_PROJECT to check for existence and to get the auth_proj_id. Looks up ai_worker_kerbname in person2 (checks for existence). For possible errors in the various parameters, returns a hand-coded user-friendly error message for each. Inserts a record in EHS_AUTH_PROJ_WORKER table. Updates the record for this AUTH_PROJ_ID in EHS_AUTHORIZED_PROJECT, setting MODIFIED_BY to upper(AI_FOR_USER) and MODIFIED_DATE to SYSDATE. Returns message in AO_MESSAGE ( added as a worker under project ) EHS_SP_DELETE_AP_WORKER (ai_for_user, ai_proj_code, ai_worker_kerbname, ao_message) Checks the calling user's authority to act as a proxy for others for EHS projects. Checks for-user's authority to maintain auth projects within the given dept_code. Raises AI_PROJ_CODE and AI_WORKER_KERBNAME to upper case. Looks up record for the given proj_code in EHS_AUTHORIZED_PROJECT to check for existence and to get the auth_proj_id. Looks to see if the record exists in EHS_AUTH_PROJ_WORKER. For possible errors in the various parameters, returns a hand-coded user-friendly error message for each. Deletes record from EHS_AUTH_PROJ_WORKER table. Updates the record for this AUTH_PROJ_ID in EHS_AUTHORIZED_PROJECT, setting MODIFIED_BY to upper(AI_FOR_USER) and MODIFIED_DATE to SYSDATE. Returns message in AO_MESSAGE ( deleted as a worker under project ) EHS_SP_ADD_AP_2NDARY_PI (ai_for_user, ai_proj_code, ai_pi_kerbname, ao_message) Checks the calling user's authority to act as a proxy for others for EHS projects. Checks for-user's authority to maintain auth projects within the given dept_code. Raises AI_PROJ_CODE and AI_PI_KERBNAME to upper case. Looks up record for the given proj_code in EHS_AUTHORIZED_PROJECT to check for existence and to get the auth_proj_id. Looks up ai_pi_kerbname in person2 (checks for existence). For possible errors in the various parameters, returns a hand-coded user-friendly error message for each. Inserts a record into EHS_AUTH_PROJ_SECONDARY_PI table. Updates the record for this AUTH_PROJ_ID in EHS_AUTHORIZED_PROJECT, setting MODIFIED_BY to upper(AI_FOR_USER) and MODIFIED_DATE to SYSDATE. Returns message in AO_MESSAGE ( deleted as 2ndary PI under project ) EHS_SP_DELETE_AP_2NDARY_PI (ai_for_user, ai_proj_code, ai_pi_kerbname, ao_message) Checks the calling user's authority to act as a proxy for others for EHS projects. Checks for-user's authority to maintain auth projects within the given dept_code. Raises AI_PROJ_CODE and AI_PI_KERBNAME to upper case. Looks up record for the given proj_code in EHS_AUTHORIZED_PROJECT to check for existence and to get the auth_proj_id. Looks to see if the record exists in EHS_AUTH_PROJ_SECONDARY_PI. For possible errors in the various parameters, returns a hand-coded user-friendly error message for each. Deletes record from EHS_AUTH_PROJ_SECONDARY_PI table. Updates the record for this AUTH_PROJ_ID in EHS_AUTHORIZED_PROJECT, setting MODIFIED_BY to upper(AI_FOR_USER) and MODIFIED_DATE to SYSDATE. Returns message in AO_MESSAGE ( deleted as a worker under project ) Web-based user interface ------------------------ There needs to be a simple web interface for maintaining authorized projects. The web UI will use the above stored procedures for doing inserts, updates and deletes. Screens: 1. List of Authorized Projects Show a table of authorized projects with the following columns Auth. Authorized Most Project Project # of recent Code Name PI DLC workers update ------- --------------- --------------- ---------- ------- ------ (a) (b) (c) (d) (e) (f) a. The auth_proj_code. Make this a clickable link to the "Details for Authorized Project xxxxx" page. b. auth_proj_name c. Full name of the PI (last, first middle). Join PI_KERBNAME with KERBEROS_NAME from the PERSON2 table. If there is one or more secondary PIs, show them as well, below the Primary PI. d. The DEPT_CODE from the DEPT table (for matching DEPT_ID) e. The number of records in EHS_AUTH_PROJ_WORKER for this AUTH_PROJ_ID. f. modified_date Include a button "Add a new project" 2. Details for Authorized Project xxxxx Show details for the given authorized project. Include Auth Project Code | Auth Project Name | PI (kerberos_name and full name) | Button: [Edit...] | DLC (dept_code and dept_name) | Notes | Last updated by (Kerberos name) | Last updated date | Authorized workers: Kerberos name and full name of each person from EHS_AUTH_PROJ_WORKER A single "Edit..." button for the top section takes you to another screen for editing the first set of fields (except for last_updated_by and last_updated_date which are not editable by the user, of course). Have a single "Delete" button between the top section and the Authorized Workers section. When you click the "Delete" button, display a warning ("Do you really want to delete this Authorized Project and its list of workers?"). Under the Authorized workers section, build an interface that allows the user to delete one worker at a time, or add one at a time. (For an example of add/delete one-at-a-time, see the Rooms section in the Room Set interface.) Show a "Delete" button next to each authorized worker, and show a typable field for an additional Kerberos name; have a pop-up screen that allows you to search for a person (similar to other parts of the Room Set interface where you can search for a person) using JavaScript to drop a picked Kerberos name into the Kerberos name field on the main screen. Have an "Add Project Worker" button to add the Kerberos name in the field. 3. Authorized project add/update screen Follow a convention similar to that used for Room Sets. The screen will include the fields "Auth Project Code" through "Notes" of the Authorized Project Detail screen, permitting them to be edited (for existing Authorized Projects) or inserted (for new Authorized Projects). The Authorized Project Code will default to to_char of max(auth_proj_id) + 1. Other tasks ----------- - Add AUTH_PROJ_CODEs as a new kind of trigger in training rules component - Change config file for Roles -> EHS feed to add 2 new functions - Determine if changes need to be made in EHS -> Warehouse feeds to accommodate the new data - Determine if changes need to be made in EHS web-based training reports to do reporting by Authorized Project Code.