Authorized Projects component ============================= Jim Repa - last modified 10/28/2003 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. --------------------------- Technical Notes --------------------------- Tables ------ Table name Column --------------------------- --------------------------------- Authorized_project Auth_proj_id (from a sequence) Auth_proj_code (manually entered) Auth_proj_name PI_kerbname dept_id notes modified_by (kerberos_name) modified_date AP_WORKER Auth_proj_id Kerberos_name AP_ROOM_SET (future) Auth_proj_id room_set_id (lab_id) Substance (future) Substance_id (from a sequence) Substance_code (manually entered) Substance_name Amount_unit Trigger_amount (number) hazard_id (join to hazard_type table) AP_SUBSTANCE (future) Auth_proj_id Building_room Substance_id Authorized_amount (number) Amount_in_lab (number) Has_protocol ('Y' or 'N') Stored procedures (for 1st pass) -------------------------------- EHS_SP_ADD_AUTH_PROJECT (AI_FOR_USER, AI_AUTH_PROJ_CODE, AI_PI_KERBNAME, AI_DEPT_CODE, AI_NOTES, AO_MESSAGE, AO_AUTH_PROJ_ID) - checks authority of USER to act as a proxy for Authorized Project updates - checks authority of FOR_USER to do Authorized Project updates - checks to make sure PI_KERBNAME matches a KERBEROS_NAME in PERSON2 table - checks to make sure Auth_project_code is unique - checks to make sure DEPT_CODE matches a record in DEPT table (gets the matching DEPT_ID for the DEPT_CODE, which will be inserted into the table) - inserts a record into the AUTHORIZED_PROJECT table, setting modified_by to upper(AI_FOR_USER) and modified_date to SYSDATE - returns new auth_proj_id and a message. EHS_SP_DELETE_AUTH_PROJECT (AI_FOR_USER, AI_AUTH_PROJ_CODE, AO_MESSAGE) - checks authority of USER to act as a proxy for Authorized Project updates - checks authority of FOR_USER to do Authorized Project updates - checks to make sure AUTH_PROJ_CODE exists in AUTHORIZED_PROJECT table (finds matching AUTH_PROJ_ID) - deletes all records from AP_WORKER matching this AUTH_PROJ_ID - deletes record from AUTHORIZED_PROJECT matching this AUTH_PROJ_ID - returns a message EHS_SP_UPDATE_AUTH_PROJ (AI_FOR_USER, AI_AUTH_PROJ_CODE, AI_NEW_AP_CODE, AI_PI_KERBNAME, AI_DEPT_CODE, AI_NOTES, AO_MESSAGE) - checks authority of USER to act as a proxy for Authorized Project updates - checks authority of FOR_USER to do Authorized Project updates - checks to make sure new PI_KERBNAME matches a KERBEROS_NAME in PERSON2 table - checks to make sure AUTH_PROJ_CODE exists in AUTHORIZED_PROJECT table (finds matching AUTH_PROJ_ID) - if new auth_proj_code is different than the old one, checks to make sure new Auth_proj_code is unique - checks to make sure DEPT_CODE matches a record in DEPT table (gets the matching DEPT_ID for the DEPT_CODE, which will be updated in the table) - finds out if any fields have changed; if not, set AO_MESSAGE = 'Authorized project not updated - nothing to change.', and stop. - update any column that has changed. Update modified_by (set it to upper(AI_FOR_USER) ) and modified_date (set it to SYSDATE) - returns a message. EHS_SP_ADD_AP_WORKER (AI_FOR_USER, AI_AUTH_PROJ_CODE, AI_KERBNAME, AO_MESSAGE) - checks authority of USER to act as a proxy for Authorized Project updates - checks authority of FOR_USER to do Authorized Project updates - checks to make sure AUTH_PROJ_CODE exists in AUTHORIZED_PROJECT table (finds matching AUTH_PROJ_ID) - checks to make sure new KERBNAME matches a KERBEROS_NAME in PERSON2 table - checks to make sure new KERBNAME does not match an existing KERBEROS_NAME for this auth_proj_id in the AP_WORKER table - inserts a record into AP_WORKER - in AUTHORIZED_PROJECT table, updates the record for this AUTH_PROJ_ID, setting modified_by to upper(AI_FOR_USER) and modified_date to SYSDATE - returns a message. EHS_SP_DELETE_AP_WORKER (AI_FOR_USER, AI_AUTH_PROJ_CODE, AI_KERBNAME, AO_MESSAGE) - checks authority of USER to act as a proxy for Authorized Project updates - checks authority of FOR_USER to do Authorized Project updates - checks to make sure AUTH_PROJ_CODE exists in AUTHORIZED_PROJECT table (finds matching AUTH_PROJ_ID) - checks to make sure new KERBNAME matches an existing KERBEROS_NAME for this auth_proj_id in the AP_WORKER table - deletes a record from AP_WORKER - in AUTHORIZED_PROJECT table, updates the record for this AUTH_PROJ_ID, setting modified_by to upper(AI_FOR_USER) and modified_date to SYSDATE - returns a message. Functions used in authorizations -------------------------------- - Update authorized project (allows the user to insert, delete, update, or view any authorized project). NULL qualifier. - View authorized project. Qualifier type DEPT. Allows the user to view authorized projects under the given DEPT. (It needs to follow existing conventions for supporting the hierarchy of departments, so that we can grant authorizations at the school or all-of-MIT) Screens ------- 1. List of Authorized Projects Show a table of authorized projects with the following columns Auth Auth Most Project Proj # 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. d. The DEPT_CODE from the DEPT table (for matching DEPT_ID) e. The number of records in AP_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 AP_WORKER A single "Edit..." button for the top section takes you to another screen for editing the first 5 fields. 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 1st 5 fields 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.