## PropC Web Application Documentation 

## Bureau of Reclamation, Research Office

## Date of this document: March, 2007

## Business Owner: Dan Levish 

## 303-445-3175 dlevish@do.usbr.gov

## Project Manager and System Security Officer: Siegie Potthoff 

## 303-445-2136 <spotthoff@do.usbr.gov>

## Developer: Bill Smith 

## 303-445-3385 <bsmith@do.usbr.gov>

## URL: 

## <http://www.usbr.gov/research/propcweb/login/login.cfm>

## 

## <http://intra.do.usbr.gov/research/propcweb/login/login.cfm>

## 

## The intranet site is identical to the public site, except the intranet site has the admin and Finance features.

## 

## 

## 

## 

## 

## 

## ![](media/image1.png){width="6.0in" height="4.034722222222222in"}

## Overview:

The Research and Development Office implemented the web-based Proposal
and Performance Contract Management System (PropC) during FY 2003. The
system automates the office business practices/processes for research
proposal submission, review, funding decisions, and progress tracking.
The PropC system allows for the submission and review of research
proposals online. After a proposal is submitted, it is subject to three
distinct reviews. The first review is the Relevancy Review which
evaluates the relevancy of the proposal to Reclamation's core mission
and S&T Program goals and is conducted by Reclamation subject matter
experts in Regional and Area Offices as well as the Office of Policy
Services. The next phase of the review process is the Technical Review
which evaluates proposals for technical merit. It includes review by
technical experts inside and outside Reclamation. The Research
Director's Office as well as Regional Research Coordinators from each
Reclamation Region, conduct the Program Review. The Program Review
evaluates the results of the Relevancy and Technical Reviews of the
proposed research; determines if the proposed research meets S&T program
goals, steering team priorities, administration priorities,
affordability, checks past progress and performance of the
investigators, and makes the final funding decisions. Funding decisions
are then transferred to a financial screen, which automates the
processes for establishing accounts by sending e-mails to Finance and to
the submitters, notifying them of funding data. This portion of the
system is not a financial management application, but simply automates
the Research and Development Office funding processes.

## 

## 

## PropC Users Types (currently there are 1300 users with login accounts)

## Proposal Submitter -- Create proposals, submit proposals for funding and update proposal progress reports. They can also read comments from reviews (Relevancy, Technical and Program) and see how much they have charged to their projects. For each year of a project, a separate version of the proposal is created in the database. New versions are created in the fall of each year by the Research Office. The script (proposal_entry/progress_help.cfm) duplicates all records of a proposal with new fiscal year in each record. The submitter can update the current version of a proposal, but can only view the past versions.

## Guest Proposal Submitter - Create and update proposals in the Practice database only

## Guest Admin -- Allows user to access some of the admin screens. Guest cannot get to proprietary proposals. The guest user sees only the Practice database. This user type was created for a vendor who was estimating the cost to convert PropC.

## Relevancy Reviewer -- Score and comment on proposals assigned to them by the Research Office or Relevancy Assigner. Scores and comments are viewed during the Program Review. The reviewer may elect not to score one or more proposals. 

## Technical Reviewer -- Score and comment on proposals assigned to them by the Research Office or Technical Assigner. Scores and comments are viewed during the Program Review. The reviewer may elect not to score one or more proposals.

## Relevancy Assigner -- Assigns Relevancy reviewers to proposals for the current review year.

## Technical Assigner -- Assigns Technical reviewers to proposals for the current review year.

## Relevancy Admin -- Update Peer Status (not used)

## Program Reviewer -- Make funding decisions and comment on proposals submitted. The funding decisions are yes, no or pending. A yes decision can also be conditional or partial. A conditional yes means the submitter must address the conditions before work on the project can be started. A partial yes means a lesser amount of money is given to the project and an evaluation is made later for full funding. A change was made in FY 2006 to have the Research Office make all funding decisions. Regions can no longer make funding decisions, but the logic was left in some screens so that a history could be viewed of the decisions in FY2004 and FY2005. A change was made (March 2007) to allocate the funds automatically when a proposal is funded. This allocation was done in the Finance screen in previous years. The allocation amount is what goes to the Finance Office to open the WOID.

## Research Partners -- Confirm contributions (no login required) This allows the Research Office to determine actual contributions to projects versus the submitters' projected contribution.. An email message is sent to each partner asking them to confirm what the submitter said was the actual contribution received. The email sent to the partner has a link which takes the partner to a screen which allows them to confirm their contribution. Note. The confirmation process is one year behind. eg. In the Fall of 2008, the Research Office would ask the partners to confirm the FY2007. This data would be stored in the FY2008 version of the proposals.

## Steering Team and Regional Supervisor -- Comment on proposals for input into the Program Review (funding decisions)

## Finance --Enter WOIDS/Cost Authority and Continuing Resolution amounts for new funded proposals. For ongoing proposals, the WOID and the Cost Authority are in the database.

## R&DSummary -- Access to Reclamation Manager Screen which displays funding decisions for the review year. The password to this account is given out by the Research Office to managers

## Admin (create users, view proposals, view peers , assign reviewers, turn on/off groups of users, view partner data, view funding, view reviews)

## Public users -- View limited info on funded proposals (no login required)

## Proposal Reviewer -- Allow selected people view all proposals. 

## IPA Reviewer - Verifies intellectual property content of proposals. 

## 

## PropC Modules:

## Admin -- The Admin menu allows the Research Office to see all the data in the system. There are approximately 100 screens and scripts in the Admin module. The Research Office can view and update all proposals and reviews in the system. The Research office can email various user types to let them know coming events, e.g. Deadline for proposal submission or funding decisions, the "Yes" memo to inform funded submitters their proposal was funded, partners to confirm their contributions, etc. The Research Office can assign reviewers to a proposal.

## Subject Matter Peer / User Create -- The Admin user type creates profiles and user accounts for relevancy and technical experts (known as Relevancy Reviewers or Technical Reviewers) for BOR employees and other employees from government and non-government sources. There is another feature which allows the Admin user type to create profiles and user accounts for technical expects from the names Proposal Submitters enter into Section IX of the proposal. This feature is under the "Peers" link in the Admin menu. There are other links under "Peers" to view the Subject Matter Peer data (profiles). 

## When a Subject Matter Peer is entered, a user account is automatically created. These accounts are created with a temporary password. When the user logs in, they are required to enter a password of their choice.

## The Subject Matter Peer enroll screen is also used to create Proposal Submitter user accounts also (so there would be one screen to create accounts).

## Login -- When a user logs into PropC, a menu of links are displayed (login/menu.cfm) based on what the user is validated for. 

## When a user logs in, a record is written to the table "user_login". The user's name, date and what happened (logged in, Bad Password), When a user logs out (clicks the logout link), another record is written to the user_login table indicating the user logged out.

## If a user attempts to login three times with the incorrect password, the account well be locked by setting the user type to "None". When the user calls, the admin user will reset the password and reset the user type.

## When a user logs in, the password expiration date is checked. If the date is more than 90 days old, the user will be prompted to enter a new password (must be at least eight characters long and have one upper case, one lower case and one number).

## The user is presented with a menu of links (PropC Modules) that the user is authorized to use. If a module is turned off by the PropC Administrator, the link will not appear,

## Certain users are "Guest" users (eg. Guest Proposal Submitter). When a Guest user logs in, the PropC Practice database (on dohps3) is used instead of the production database. This is done by code in the application.cfm (see below). 

## When users leave Reclamation or are no longer Reviewers, the admin user locks their account by adding the "Lock" user type to the users list of user types. There is a link on the admin menu (Inactive Users), which displays all Inactive Users. Users are never removed from the database so their user information is retained and displayed.

**Submitter** -- Allows BOR researchers (logged in as Proposal
Submitter) to create research proposals, update continuing proposals or
close out completed proposals (script: proposal_entry/main.cfm) When the
user logs in, PropC will display a list of proposals that the Proposal
Submitter owns.

When a new proposal is created
(proposal_entry/create_proposal_action.cfm), a Proposal ID is assigned a
random number between 1 and 9999. If the random number generated matches
an existing proposal ID, another random number is assigned.

## When a submitter, submits a proposal, the database column "current_status" in the "proposal_info" table is changed from "draft" to "submitted". Only proposals with a status of "submitted", go to the next step, which is the Relevancy and Technical Review. When a submitter, completes a proposal, the status is changed to "completed"

## A proposal submitter can not delete their proposals. The reason for this is the Research Office wants to retain all proposals. There is a link in the Admin screen (Clear menu) to remove all proposals that have the default title "A title has not been entered yet". These proposals were created by employees who click on the "Create Proposal" but never started on the proposal. The Research Office can delete a proposal that was created if an error was made in the system when creating new versions of a proposal.

The Submitter screen displays a summary of all versions (fiscal years)
of proposals that belong to the user logged it. The summaries provide
links to display reviews (Relevancy, Technical and Program) and
conditional / partial funding comments. The current version of a
proposal displays additional links to: edit the proposal, submit for
review and funding.

The Research Office increments the Submitter Fiscal Year just prior to
award and then creates the next year version of all proposals.

## Submitter Path

## login/login.cfm

## login/menu.cfm (display modules the user has access to -- chooses Submitter) 

## proposal_entry/main.cfm (display proposals created by submitter)

## proposal_entry/main1.cfm (display info about proposal selected above)

## proposal_entry/proposal_entry.cfm (display proposal selected)

## ... files used to input proposal data are not shown

## proposal_evtry/view.cfm (check proposal for required section before submit for funding)

## proposal_entry/submit.cfm ( PropC Funding Agreement)

## proposal_entry/submit_action.cfm (submit for review)

**Proposal Entry -** The proposal screen is divided into sections. Each
section has a corresponding database table as well as a set of
ColdFusion scripts (in the proposal_entry folder) which add's, updates
or deletes records from these database tables.

The Proposal form has a few large text fields. The data in these fields
is entered with a HTLM Text Box with a character counter so the proposal
submitter can know when the upper limit character count of the field is
reached. These large text fields are written to a separate database
table to make the overall performance of the application better.

## This screen provides links to view past versions of a proposal. 

## The Research Office (logged in as Admin) can indicate if they have received Products due using this screen..

## 

## Assign Reviewers -- Allows Relevancy and Technical Assigners to assign Subject Matters Peers Reviewers (Relevancy and Technical) as reviewers to proposals. When a reviewer is assigned to a proposal, a check is done to see if that reviewer has already been assigned to the maximum number of proposals the Research Office indicated when the user account was created.. 

## Admin users can also assign reviewers by clicking on "Submitted Proposals" under the Admin menu (Proposals for ...). For each proposal, Admin can assign Relevancy or Technical reviewers. The "no name" feature displays the Technical reviewer's ID, rather than their name, so when the Research Office is working with the Tributary Leads to assign reviewers, the Tributary Lead will not see the reviewer names. 

## Assigner Path

## login/login.cfm

## login/menu.cfm (display modules the user has access to -- chooses Relevancy Assigner)

## login/assigner/view_submitted.cfm 

## 

## login/login.cfm

## login/menu.cfm (display modules the user has access to -- chooses Technical Assigner)

## login/assigner/view_submitted_tech.cfm 

## 

## Relevancy and Technical Review -- Allows relevancy and technical reviewers (logged in as Relevancy Reviewers or Technical Reviewers) to score and comment on proposals submitted the reviewer was assigned. For proposals to show up on this screen, the assigners or the Research Office must assign a proposal to the reviewer.

## 

## Reviewer Path

## login/login.cfm

## login/menu.cfm (display modules the user has access to -- chooses Relevancy Reviewer)

## review_main.cfm 

## 

## login/login.cfm

## login/menu.cfm (display modules the user has access to -- chooses Technical Reviewer)

## review_main.cfm 

## 

## Program Review -- Allows the Research Office (logged in as Program Reviewers) to look at the scores and comments by Relevancy and Technical reviewers and decide which proposals to fund. This screen also will help the Research Office balance funded amounts to Output Area and regional targets. To do this balancing, funded amounts are written to temporary database tables and then joined to the main query in the Program Funding screen to display these totals. (program_review/decision_main.cfm). There is a link on this screen which allows the Program reviewer to compare a proposal to last year's version. Differences are highlighted in yellow.

## 

## Program Reviewer Path

## login/login.cfm

## login/menu.cfm (display modules the user has access to -- chooses Program Reviewer)

## program_review/program_decision_main.cfm

## ... other files not shown

## 

## 

## 

##  

## Steering Team and Regional Supervisor -- Allows these users to enter comments on proposals. These comments are read by the Program Reviewer. 

## Steering Team and Regional Supervisor Path

## login/login.cfm

## login/menu.cfm (display modules the user has access to -- chooses either option)

## program_review/regional_funding_main.cfm

## ... other files not shown

## 

## 

## Finance -- Allows the Research Office to enter Cost Authority Numbers for funded proposals (finance/program_finance.cfm). 

## Finance Path

## login/login.cfm

## login/menu.cfm (display modules the user has access to -- chooses Finance)

## finance/program_finance.cfm 

## ... other files not shown

## Research Report Inventory --Allows the Research Office to "tie" proposals to research reports. The reports (files) are kept in a folder called Research_Reports on the web server. As these file are received by the Research Office, they are copied to this folder.

## Reclamation Manager (Executive) -- Managers (logged in as "R&DSummary") may see the funding decisions and scores and comments by Relevancy and Technical reviewers. 

## Public -- The public may see limited information on funded proposals. 

## Relevancy Admin (not used) -- Allow Relevancy Admin's to indicate which employees may participate in Relevancy Reviews. (admin/regional_submitters.cfm).

## Partner Confirmation -- Provides a way to have research partner confirm their contributions. This data is then reported to OMB. The confirmation occurs one year latter. For example, the confirmation for the FY2005 proposals (projects) occurred in March 2006. The actual data for these proposals is in the 2007 version for ongoing proposals and the 2006 version for completed proposals. The links under the admin menu have been developed to allow this to work every year. When the email is sent to partners, two sets of email are sent (email for ongoing and completed projects). This email contains a link which allows the partner to confirm their contribution. Reports are under admin, to see the data.

## Transfer -- Allows a proposal to be assigned to a different submitter. The new submitter name is assigned to proposal being transferred and a copy of the original is created under the original submitter so that the original submitter has it on file (this copy may not be edited. ) 

## 

## 

## 

**PropC Database Tables**

## Advocates\* - Reclamation Managers and Other Stakeholders consulted for proposals.

  -------------- ------ -------------- -------------- -------------- -----------
  Field name     Type   Description    Function       Example        Notes

  ID             Num    Sequence       Makes record                  Assigned in
                                       unique                        sequence

  Proposalid     Num    Proposal ID    Relates record 4565           
                                       to a proposal                 

  First_name     Char   First name of                                
                        advocate                                     

  Last_Name      Char   Last name of                                 
                        advocate                                     

  Title          Char   Title of                                     
                        advocate                                     

  Organization   Char   Organization                                 
                        of advocate                                  

  Email          Char   Email address                                
                        of advocate                                  

  Phone          Char   Phone of                                     
                        advocate                                     

  FY             Num    Fiscal year    Provides the   2004           
                        record was     way to version                
                        created in     a proposal                    
  -------------- ------ -------------- -------------- -------------- -----------

##  

## Beneficary2\* - **Primary Research Beneficiary Region(s) for proposals**

  --------------- ------ -------------- -------------- -------------- -----------
  Field name      Type   Description    Function       Example        Notes

  ID              Num    Sequence       Makes record                  Assigned in
                                        unique                        sequence

  Proposalid      Num    Proposal ID    Relates record 4565           
                                        to a proposal                 

  Area_Office     Char   Area Office of                               
                         the beneficary                               

  Based           Char   When project                  Field or       
                         is based                      Office         

  Firstname       Char   First name of                                
                         beneficary                                   

  LastName        Char   Last name of                                 
                         beneficary                                   

  Mail Code       Char   Mail Code of                                 
                         beneficary                                   

  Email_Address   Char   Email address                                
                         of advocate                                  

  FY              Num    Fiscal year    Provides the   2004           
                         record was     way to version                
                         created in     a proposal                    
  --------------- ------ -------------- -------------- -------------- -----------

## 

## 

**Beneficary3**\* - **NEPA Compliance Contact Information**

  -------------------------- ------ -------------- -------------- -------------- -----------
  Field name                 Type   Description    Function       Example        Notes

  ID                         Num    sequence       Makes record                  Assigned in
                                                   unique                        sequence

  Proposalid                 Num    Proposal ID    Relates record 4565           
                                                   to a proposal                 

  Firstname                  Char   First name of                                
                                    NEPA contact                                 

  Lastname                   Char   Last name of                                 
                                    NEPA contact                                 

  Locatiomn_NEPA_Doicument   Char   Location of                                  
                                    NEPA document                                

  Organization               Char   Organization                                 
                                    of advocate                                  

  Email_Address              Char   Email address                                
                                    of NEPA                                      
                                    contact                                      

  FY                         Num    Fiscal year    Provides the   2004           
                                    record was     way to version                
                                    created in     a proposal                    
  -------------------------- ------ -------------- -------------- -------------- -----------

**Bulletin** \*\*\*\* -- Contains data about research bulletins. Not
implemented yet.

**Bulletin_rating** \*\*\*\* - Not implemented yet.

**Bulletin_Subscriber** \*\*\*\* Not implemented yet.

**CDW** \*\*\*\* - Data from the Corporate Data Warehouse by 18 digit
number (charges against the 18 digit). Currently, the data is loaded
from an MS Access database, supplied by Mike Scott.

  -------------------------- ------ -------------- -------------- -------------- -----------
  Field name                 Type   Description    Function       Example        Notes

  Costnumber                 Char   18 digit                                     
                                    number                                       

  PY_Undelivered_Orders      Num    Prior Year                                   
                                    Undelivered                                  
                                    Orders                                       

  Total_Undelivered_Orders   Num    Total                                        
                                    Undelivered                                  
                                    Orders                                       

  Expended                   Num    Spent                                        

  Obligations                Num    Obligated                                    
  -------------------------- ------ -------------- -------------- -------------- -----------

##  

**Expenditures** \*\*\*\* - Data from the Corporate Data Warehouse by 18
digit number summarized by month (charges against the 18 digit). This
table will be phased out.

**Focus** ... \*\*\*\* - These tables are not being used. They were an
attempt to track who were Research Champions in the regional offices.

**Funding_Request**\* - Requested Funding for proposals.

  ------------ ------ -------------- -------------- -------------- -----------
  Field name   Type   Description    Function       Example        Notes

  ID           Num    sequence       Makes record                  Assigned in
                                     unique                        sequence

  Proposalid   Num    Proposal ID    Relates record 4565           
                                     to a proposal                 

  Funding      Num    Funding                                      
                      requested for                                
                      specific year                                

  Year         Num    Year requested                               

  Title        Char   Title of                                     
                      advocate                                     

  FY           Num    Fiscal year    Provides the   2004           
                      record was     way to version                
                      created in     a proposal                    
  ------------ ------ -------------- -------------- -------------- -----------

##  

**Last_years_totals**\*\* - This table is generated by the Program
Review process.

**Need_Hopper** \*\*\*\* - This table contain research needs identified
by Reviewers.

**Other_Totals** \*\*

**Outputs** \* - Outputs for proposals. This table was a section on the
proposal form in FY2004, but is no longer on the form. It should not be
deleted because the FY2004 proposals will access it.

**Output_Area_Totals** \*\*

**Partner1** \* - Contain Partner names and contact information for
proposals.

  -------------- ------ -------------- -------------- -------------- -----------
  Field name     Type   Description    Function       Example        Notes

  ID             Num    sequence       Makes record                  Assigned in
                                       unique                        sequence

  Proposalid     Num    Proposal ID    Relates record 4565           
                                       to a proposal                 

  First_name     Char   First name of                                
                        advocate                                     

  Last_Name      Char   Last name of                                 
                        advocate                                     

  Partner_ID     Num    Partner ID     To relate                     Assigned in
                        sequence       record to the                 sequence
                                       Partners2                     
                                       table                         

  Organization   Char   Organization                                 
                        of partner                                   

  Email          Char   Email address                                
                        of partner                                   

  Phone          Char   Phone of                                     
                        partner                                      

  FY             Num    Fiscal year    Provides the   2004           
                        record was     way to version                
                        created in     a proposal                    
  -------------- ------ -------------- -------------- -------------- -----------

**Partners2** \* - Contains partner projected and actual contributions
for proposals. Actuals are entered in the following year after award.

  -------------------------- ------ -------------- -------------- -------------- -----------
  Field name                 Type   Description    Function       Example        Notes

  ID                         Num    sequence       Makes record                  Assigned in
                                                   unique                        sequence

  Proposalid                 Num    Proposal ID    Relates record 4565           
                                                   to a proposal                 

  Partner_ID                 Num    Partner ID     To relate                     
                                                   record to the                 
                                                   Partner1 table                

  Description_Contribution   Char   Description of                               
                                    Contribution                                 

  Cask_IKS                   Char   Cash or In                                   
                                    Kind Services                                

  Contribution               Num    Projected                                    
                                    amount of                                    
                                    partner                                      
                                    contribution                                 
                                    by the                                       
                                    proposal                                     
                                    submitter                                    

  Actual_Contribution        Num    Actual                                       
                                    contribution                                 
                                    received                                     
                                    (entered by                                  
                                    the proposal                                 
                                    submitter)                                   

  Year                       Num    Year of                                      
                                    partner                                      
                                    contribution                                 

  Decription_Received        Num    Description of                               
                                    actual                                       
                                    contribution                                 
                                    received                                     
                                    (entered by                                  
                                    the proposal                                 
                                    submitter)                                   

  Partner_Comments           num    Description of                               
                                    actual                                       
                                    contribution                                 
                                    received                                     
                                    (entered by                                  
                                    the partner                                  
                                    when                                         
                                    contribution                                 
                                    was confirmed)                               

  Partner_Cash_IKS           Char   Cash or IKS                                  
                                    indicated by                                 
                                    the partner                                  

  Partner_Contribution       Num    Actual                                       
                                    contribution                                 
                                    received                                     
                                    (confirmed by                                
                                    the partner)                                 

  Confirmation_Code          Char   Confirmation                  Confirm,       
                                    Code (entered                 edited, not    
                                    by the                        aware          
                                    partner)                                     

  FY                         Num    Fiscal year    Provides the   2004           
                                    record was     way to version                
                                    created in     a proposal                    
  -------------------------- ------ -------------- -------------- -------------- -----------

**Partner_Temp** \*\*

**Peers** \*\*\*\* - Data about reviewers. When a user is entered into
PropC, a record is inserted to this table and the User Account table.

  --------------------- ------ -------------- -------------- -------------- -------------
  Field name            Type   Description    Function       Example        Notes

  ID                    Num    sequence       Makes record                  Assigned in
                                              unique                        sequence

  First_name            Char   First name of                                
                               peer                                         

  Last_Name             Char   Last name of                                 
                               peer                                         

  Title                 Char   Titlle of peer                               

  Organization          Char   Organization                                 
                               of peer                                      

  Email                 Char   Email address                                

  Phone                 Char   Phone                                        

  Mail_Code             Char   BOR Mail Code                                

  Affiliation           Char   Affiliation                   Federal,       
                                                             State,         
                                                             University,    
                                                             etc.           

  Dept                  Char   Department                    Agriculture,   
                                                             Interior, etc. 

  Other_Dept            Char   Other                         Federal Energy 
                               Department                    Regulatory     
                                                             Commission     

  Output_Area           Char   Output Area                   DS2-Improve    
                                                             Water Supply   
                                                             Forecast       

  Peer_category         Char   Peer Category  Type of        Technical,     
                                              reviewer       Resource       
                                                             (Relevancy)    

  Expertise             Char   Description of                               
                               expertise                                    

  SubmitterFirst_Name   Char   First name of                                
                               the person who                               
                               entered this                                 
                               peer                                         

  SubmitterLast_Name    Char   Last name of                                 
                               the person who                               
                               entered this                                 
                               peer                                         

  SubmitterEmail        Char   Email address                                
                               of the person                                
                               who entered                                  
                               this peer                                    

  submitterPhone        char   Phone number                                 
                               of the person                                
                               who entered                                  
                               this peer                                    

  Peer_Preference       Char   How many                                     
                               proposals a                                  
                               peer is                                      
                               willing to                                   
                               review                                       

  FY2005_Status         Char   Whether or not                Yes or No      This was
                               the region                                   never
                               wants this                                   implemented
                               peer to be a                                 
                               reviewer in                                  
                               FY2005                                       

  Peer_Status           Char   Whether not                   next year      This column
                               the peer wants                               was never
                               to review                                    accessed in
                               proposals next                               the
                               year                                         assignment
                                                                            process to
                                                                            eliminate
                                                                            peers which
                                                                            do not want
                                                                            to review.

  Username              Char   User Name                                    This is
                                                                            generated by
                                                                            application
                                                                            (first
                                                                            initial and
                                                                            last name)
  --------------------- ------ -------------- -------------- -------------- -------------

##  

**Peer_Relevancy_Temp** \*\*

**Peer_Technical_Temp** \*\*

**Product** \* - Deliverables associated for proposals.

  ------------------ ------ -------------- -------------- -------------- -----------
  Field name         Type   Description    Function       Example        Notes

  ID                 Num    Sequence       Makes record                  Assigned in
                                           unique                        sequence

  Proposalid         Num    Proposal ID    Relates record 4565           
                                           to a proposal                 

  Product_Name       Char   Product Name                  River          
                                                          restoration    
                                                          tools          

  Other_Product      Char   Addition info                                

  Due_Date           Char   Project date                                 
                            which proposal                               
                            submitter will                               
                            finish the                                   
                            product                                      

  Documented         Char   How documented                Manual,        
                                                          Guidelines,    
                                                          Reporrt        

  Other_document     Char   Additional                                   
                            info                                         

  Actual_date               Actual date                                  
                            proposal                                     
                            submitter                                    
                            finished the                                 
                            product                                      

  Comments           Char   Proposal                                     
                            submitted                                    
                            comments                                     

  Product_Received   char   Indicates if                  yes or no      This is
                            the product                                  entered by
                            was received                                 the
                            by the                                       Research
                            Research                                     Office
                            Office                                       logged in
                                                                         as Admin

  FY                 Num    Fiscal year    Provides the   2004           
                            record was     way to version                
                            created in     a proposal                    
  ------------------ ------ -------------- -------------- -------------- -----------

##  

**Program_Comments_Temp** \*\*

**Proposal** \* -- Contain the funded amounts and Cost Account Number
for proposals.

+---------+----+-------------+-------------+-------------+-----------+
| Field   | Ty | Description | Function    | Example     | Notes     |
| name    | pe |             |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Pro     | N  | Proposal ID | Relates     | 4565        |           |
| posalid | um |             | record to a |             |           |
|         |    |             | proposal    |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Fu      | N  | Funding     |             |             | Entered   |
| nding_B | um | amount for  |             |             | on the    |
| OR_WIDE |    | project     |             |             | Program   |
|         |    | from the    |             |             | Review    |
|         |    | Research    |             |             | screen    |
|         |    | Office      |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Fun     | N  | Funding     |             |             | Funding   |
| ding_PN | um | amount for  |             |             | no longer |
|         |    | project     |             |             | comes     |
|         |    | from the PN |             |             | from a    |
|         |    | region      |             |             | region.   |
|         |    |             |             |             | This      |
|         |    |             |             |             | column    |
|         |    |             |             |             | and the   |
|         |    |             |             |             | next four |
|         |    |             |             |             | were last |
|         |    |             |             |             | used in   |
|         |    |             |             |             | FY2005.   |
|         |    |             |             |             | They need |
|         |    |             |             |             | to remain |
|         |    |             |             |             | until     |
|         |    |             |             |             | FY2005    |
|         |    |             |             |             | data is   |
|         |    |             |             |             | purged    |
+---------+----+-------------+-------------+-------------+-----------+
| Fun     | N  | Funding     |             |             |           |
| ding_MP | um | amount for  |             |             |           |
|         |    | project     |             |             |           |
|         |    | from the MP |             |             |           |
|         |    | region      |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Fun     | Ch | Funding     |             | Manual,     |           |
| ding_LC | ar | amount for  |             | Guidelines, |           |
|         |    | project     |             | Reporrt     |           |
|         |    | from the LC |             |             |           |
|         |    | region      |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Fun     | N  | Funding     |             |             |           |
| ding_GP | um | amount for  |             |             |           |
|         |    | project     |             |             |           |
|         |    | from the GP |             |             |           |
|         |    | region      |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Fun     | N  | Funding     |             |             |           |
| ding_UC | um | amount for  |             |             |           |
|         |    | project     |             |             |           |
|         |    | from the PN |             |             |           |
|         |    | region      |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Obj     | N  | This column |             |             |           |
| 1_Relev | um | was use in  |             |             |           |
| ancy_av |    | FY2004 to   |             |             |           |
| g_score |    | average one |             |             |           |
|         |    | component   |             |             |           |
|         |    | (O          |             |             |           |
|         |    | bjective 1) |             |             |           |
|         |    | of          |             |             |           |
|         |    | Relevancy   |             |             |           |
|         |    | Reviews.    |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Obj     | N  | This column |             |             |           |
| 1_Techn | um | was use in  |             |             |           |
| ical_av |    | FY2004 to   |             |             |           |
| g_score |    | average one |             |             |           |
|         |    | component   |             |             |           |
|         |    | (O          |             |             |           |
|         |    | bjective 1) |             |             |           |
|         |    | of          |             |             |           |
|         |    | Technical   |             |             |           |
|         |    | Reviews.    |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Fund_RO | N  | Funding     |             | 1 = no      |           |
|         | um | decision of |             |             |           |
|         |    | the         |             | 3 = Fund    |           |
|         |    | Research    |             |             |           |
|         |    | Office      |             | 4 = Pending |           |
+---------+----+-------------+-------------+-------------+-----------+
| Fund_PN | N  | Funding     |             | 1 = no      | Funding   |
|         | um | decision of |             |             | no longer |
|         |    | PN          |             | 3 = Fund    | comes     |
|         |    |             |             |             | from a    |
|         |    |             |             | 4 = Pending | region.   |
|         |    |             |             |             | This      |
|         |    |             |             |             | column    |
|         |    |             |             |             | and the   |
|         |    |             |             |             | next four |
|         |    |             |             |             | were last |
|         |    |             |             |             | used in   |
|         |    |             |             |             | FY2005.   |
|         |    |             |             |             | They need |
|         |    |             |             |             | to remain |
|         |    |             |             |             | until     |
|         |    |             |             |             | FY2005    |
|         |    |             |             |             | data is   |
|         |    |             |             |             | purged    |
+---------+----+-------------+-------------+-------------+-----------+
| Fund_MP | N  | Funding     |             |             |           |
|         | um | decision of |             |             |           |
|         |    | MP          |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Fund_LC | N  | Funding     |             |             |           |
|         | um | decision of |             |             |           |
|         |    | LC          |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Fund_GP | N  | Funding     |             |             |           |
|         | um | decision of |             |             |           |
|         |    | GP          |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Fund_UC | N  | Funding     |             |             |           |
|         | um | decision of |             |             |           |
|         |    | UC          |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Fund    | Ch | Indicates   |             | conditions, |           |
| _Condit | ar | conditional |             | partial     |           |
| ions_RO |    | or partial  |             | funding     |           |
|         |    | funding     |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Fund    | Ch | Indicates   |             | conditions, | Funding   |
| _Condit | ar | conditional |             | partial     | no longer |
| ions_PN |    | or partial  |             | funding     | comes     |
|         |    | funding by  |             |             | from a    |
|         |    | PN region   |             |             | region.   |
|         |    |             |             |             | This      |
|         |    |             |             |             | column    |
|         |    |             |             |             | and the   |
|         |    |             |             |             | next four |
|         |    |             |             |             | were last |
|         |    |             |             |             | used in   |
|         |    |             |             |             | FY2005.   |
|         |    |             |             |             | They need |
|         |    |             |             |             | to remain |
|         |    |             |             |             | until     |
|         |    |             |             |             | FY2005    |
|         |    |             |             |             | data is   |
|         |    |             |             |             | purged    |
+---------+----+-------------+-------------+-------------+-----------+
| Fund    | Ch | Indicates   |             |             |           |
| _Condit | ar | conditional |             |             |           |
| ions_MP |    | or partial  |             |             |           |
|         |    | funding by  |             |             |           |
|         |    | MP region   |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Fund    | Ch | Indicates   |             |             |           |
| _Condit | ar | conditional |             |             |           |
| ions_LC |    | or partial  |             |             |           |
|         |    | funding by  |             |             |           |
|         |    | LC region   |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Fund    | Ch | Indicates   |             |             |           |
| _Condit | ar | conditional |             |             |           |
| ions_GP |    | or partial  |             |             |           |
|         |    | funding by  |             |             |           |
|         |    | GP region   |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Fund    | Ch | Indicates   |             |             |           |
| _Condit | ar | conditional |             |             |           |
| ions_UC |    | or partial  |             |             |           |
|         |    | funding by  |             |             |           |
|         |    | UC region   |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| R       | Ch | First three |             | A10         | This and  |
| el_Fund | ar | characters  |             |             | the next  |
|         |    | of thel 18  |             |             | 10        |
|         |    | digit Cost  |             |             | columns   |
|         |    | Authority.  |             |             | were not  |
|         |    | REL was for |             |             | used      |
|         |    | realigned   |             |             | after     |
|         |    |             |             |             | FY2005    |
+---------+----+-------------+-------------+-------------+-----------+
| Rel_    | Ch | Next four   |             |             |           |
| Project | ar | characters  |             |             |           |
|         |    | of the 18   |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Re      | Ch | Next four   |             |             |           |
| l_Third | ar | characters  |             |             |           |
|         |    | of the 18   |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Re      | Ch | Next three  |             |             |           |
| l_Forth | ar | characters  |             |             |           |
|         |    | of the 18   |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Re      | Ch | Next two    |             |             |           |
| l_Firth | ar | characters  |             |             |           |
|         |    | of the 18   |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Re      | Ch | Next        |             |             |           |
| l_Sixth | ar | character   |             |             |           |
|         |    | of the 18   |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Rel_    | Ch | Next        |             |             |           |
| Seventh | ar | character   |             |             |           |
|         |    | of the 18   |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Re      | C  | The 19^th^  |             |             |           |
| l_Eight | ha | digit       |             |             |           |
|         | 0r |             |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Rel     | N  | Dollars     |             |             |           |
| _Funded | um | assigned    |             |             |           |
|         |    | for this    |             |             |           |
|         |    | category    |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Re      | Ch | Project     |             |             |           |
| l_Proje | ar | Name        |             |             |           |
| ct_Name |    |             |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| R       | Ch | Work Order  |             |             |           |
| el_WOID | ar | ID          |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| R       | Ch | Description |             |             |           |
| el_Desc | ar | that goes   |             |             |           |
| ription |    | to finance  |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| AS      | Ch | First three |             | A10         | This and  |
| TD_Fund | ar | characters  |             |             | the next  |
|         |    | of thel 18  |             |             | nine      |
|         |    | digit Cost  |             |             | columns   |
|         |    | Authority.  |             |             | arre      |
|         |    | ASTD stood  |             |             | entered   |
|         |    | for Applied |             |             | on the    |
|         |    | Science and |             |             | Finance   |
|         |    | Technical   |             |             | screen    |
|         |    |             |             |             | after     |
|         |    |             |             |             | clicking  |
|         |    |             |             |             | "S&T"     |
+---------+----+-------------+-------------+-------------+-----------+
| ASTD_   | Ch | Next four   |             |             |           |
| Project | ar | characters  |             |             |           |
|         |    | of the 18   |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| AST     | Ch | Next four   |             |             |           |
| H_Third | ar | characters  |             |             |           |
|         |    | of the 18   |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| AST     | Ch | Next three  |             |             |           |
| D_Forth | ar | characters  |             |             |           |
|         |    | of the 18   |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| AST     | Ch | Next two    |             |             |           |
| D_Firth | ar | characters  |             |             |           |
|         |    | of the 18   |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| AST     | Ch | Next        |             |             |           |
| D_Sixth | ar | character   |             |             |           |
|         |    | of the 18   |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| ASTD_   | Ch | Next        |             |             |           |
| Seventh | ar | character   |             |             |           |
|         |    | of the 18   |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| ASTD    | N  | Dollars     |             |             |           |
| _Funded | um | assigned    |             |             |           |
|         |    | for this    |             |             |           |
|         |    | category    |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| AS      | Ch | Work Order  |             |             |           |
| TD_WOID | ar | ID          |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| AS      | Ch | Description |             |             |           |
| TD_Desc | ar | that goes   |             |             |           |
| ription |    | to Finance  |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Oth     | Ch | First three |             | A10         | This and  |
| er_Fund | ar | characters  |             |             | the next  |
|         |    | of thel 18  |             |             | nine      |
|         |    | digit Cost  |             |             | columns   |
|         |    | Authority   |             |             | were not  |
|         |    |             |             |             | used      |
|         |    |             |             |             | after     |
|         |    |             |             |             | FY2005    |
+---------+----+-------------+-------------+-------------+-----------+
| Other_  | Ch | Next four   |             |             |           |
| Project | ar | characters  |             |             |           |
|         |    | of the 18   |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Othe    | Ch | Next four   |             |             |           |
| r_Third | ar | characters  |             |             |           |
|         |    | of the 18   |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Othe    | Ch | Next three  |             |             |           |
| r_Forth | ar | characters  |             |             |           |
|         |    | of the 18   |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Othe    | Ch | Next two    |             |             |           |
| r_Firth | ar | characters  |             |             |           |
|         |    | of the 18   |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Othe    | Ch | Next        |             |             |           |
| r_Sixth | ar | character   |             |             |           |
|         |    | of the 18   |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Other_  | Ch | Next        |             |             |           |
| Seventh | ar | character   |             |             |           |
|         |    | of the 18   |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Other   | N  | Dollars     |             |             |           |
| _Funded | um | assigned    |             |             |           |
|         |    | for this    |             |             |           |
|         |    | category    |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Oth     | Ch | Work Order  |             |             |           |
| er_WOID | ar | ID          |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Oth     | Ch | Description |             |             |           |
| er_Desc | ar | that goes   |             |             |           |
| ription |    | to Finance  |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| A       | Ch | For Finance |             |             |           |
| BC_Code | ar |             |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| FY      | N  | Fiscal year | Provides    | 2004        |           |
|         | um | record was  | the way to  |             |           |
|         |    | created in  | version a   |             |           |
|         |    |             | proposal    |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| Te      | N  | Average     |             |             | R         |
| chnical | um | Technical   |             |             | ecomputed |
| _Averag |    | Score       |             |             | every     |
| e_Score |    |             |             |             | time a    |
|         |    |             |             |             | Technical |
|         |    |             |             |             | review is |
|         |    |             |             |             | finalized |
+---------+----+-------------+-------------+-------------+-----------+
| Re      | N  | Average     |             |             | R         |
| levancy | um | Relevancy   |             |             | ecomputed |
| _averag |    | Score       |             |             | every     |
| e_score |    |             |             |             | time a    |
|         |    |             |             |             | Relvancy  |
|         |    |             |             |             | review is |
|         |    |             |             |             | finalized |
+---------+----+-------------+-------------+-------------+-----------+
| P       | N  | Partial     |             |             | Entered   |
| artial_ | um | Funding     |             |             | during    |
| Funding |    | Amount      |             |             | the       |
| _Amount |    |             |             |             | Program   |
|         |    |             |             |             | Review    |
+---------+----+-------------+-------------+-------------+-----------+
| CRIF    | N  | Funding     |             |             | Entered   |
| _Amount | um | during a    |             |             | on the    |
|         |    | Continuing  |             |             | Finance   |
|         |    | Resolution  |             |             | screen    |
|         |    | Interium    |             |             |           |
|         |    | Funding     |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| CR      | Ch | WOID to be  |             |             | Entered   |
| IF_WOID | ar | used during |             |             | on the    |
|         |    | a           |             |             | Finance   |
|         |    | Continuing  |             |             | screen    |
|         |    | Resolution  |             |             |           |
+---------+----+-------------+-------------+-------------+-----------+
| CRI     | Ch | Continuing  |             |             | Entered   |
| F_Notes | ar | Resolution  |             |             | on the    |
|         |    | Notes       |             |             | Finance   |
|         |    |             |             |             | screen    |
+---------+----+-------------+-------------+-------------+-----------+
| Cos     | Ch | 18 digit    |             |             | Concat.   |
| tnumber | ar | number      |             |             | When CDW  |
|         |    |             |             |             | data is   |
|         |    |             |             |             | loaded    |
+---------+----+-------------+-------------+-------------+-----------+

##  

**Proposal_Info** \* - Contains the information in Section I for
proposals (Title, Output Area, Start Date, Completion Date) It also
contains information entered by the Tributary Leads and generated data
if a proposal is transferred to another proposal submitter.

  --------------------------- ------ --------------- -------------- -------------- -------------
  Field name                  Type   Description     Function       Example        Notes

  Proposalid                  Num    Proposal ID     Relates record 4565           
                                                     to a proposal                 

  Title                       Char   Proposal Title                                

  Output_Area                 Char   Assigned Output                               
                                     Area                                          

  Purpose                     Char   Type of                        Conducting,    
                                     proposal                       scoping        

  Current_Status              char   Status          Determines the draft,         A transferred
                                                     state of the   submitted,     proposal is
                                                     proposal       submitted      where the
                                                                    complete,      proposal is
                                                                    transferred    transferred
                                                                                   to a
                                                                                   different
                                                                                   proposal
                                                                                   submitter.
                                                                                   The proposal
                                                                                   marked
                                                                                   transferred
                                                                                   is a copy of
                                                                                   the orginal.

  Proprietary                 Char   Indicates if a                 yes, no        
                                     proposal                                      
                                     contains                                      
                                     proprietary                                   
                                     information                                   
                                     (patientable)                                 

  New_Start_Year              Char   The fiscal year                               
                                     a proposal will                               
                                     start                                         

  Completion_year             Char   The fiscal year                               
                                     a proposal will                               
                                     end                                           

  Username                    Char   The login name                                
                                     of the proposal                               
                                     submitter                                     

  Technical_Review_Year       Num    The fiscal year                               
                                     the Technical                                 
                                     Review was done                               

  Resubmitted                 Char   Indicates if a                                
                                     proposal was                                  
                                     resubmitted the                               
                                     following year                                

  Region1                     Char   Benefiting                     LC, MP, PN,    
                                     region                         UC, GP,        
                                                                    BOR-WIDE       

  Region2                     Char   Second                                        
                                     benefiting                                    
                                     region                                        

  Region3                     Char   Third                                         
                                     benefiting                                    
                                     region                                        

  Relevancy_Review_count      Num    Number of                                     Recomputed
                                     Relevancy                                     every time a
                                     Reviews                                       Relvancy
                                     assigned to the                               review is
                                     proposal                                      assigned

  Technical_Review_Count      Num    Number of                                     Recomputed
                                     Technical                                     every time a
                                     Reviews                                       Technical
                                     assigned to the                               review is
                                     proposal                                      assigned

  Orginal_Proposalid          Num    Orginal                                       
                                     Proposal ID (if                               
                                     this is a copy                                
                                     of the proposal                               
                                     which was                                     
                                     transferred)                                  

  Copied_To_proposal          Num    The proposalid                                
                                     this was copied                               
                                     to when a                                     
                                     proposal was                                  
                                     transferred                                   

  Technical_Review_Required   Num    Indicates if a                 yes, no        In FY2005,
                                     technical                                     the Tributary
                                     review is                                     Leads would
                                     required                                      determine

  Tributary_Lead_Notes        Char   Notes entered                                 
                                     by the                                        
                                     Tributary Lead                                

  After_Review_Comments       Char   Notes entered                                 
                                     by the                                        
                                     Tributary Lead                                
                                     after the                                     
                                     Program Review                                

  Added_First_Name            Char   The first name                                
                                     of a reviewed                                 
                                     suggested by                                  
                                     the Tributary                                 
                                     Lead                                          

  Added_Last_Name             Char   The last name                                 
                                     of a reviewed                                 
                                     suggested by                                  
                                     the Tributary                                 
                                     Lead                                          

  Added_Mail_Code             char   The mail of a                                 
                                     reviewed                                      
                                     suggested by                                  
                                     the Tributary                                 
                                     Lead                                          

                                                                                   

  FY                          Num    Fiscal year     Provides the   2004           
                                     record was      way to version                
                                     created in      a proposal                    
  --------------------------- ------ --------------- -------------- -------------- -------------

##  

**Proposal_Root** \* -- Contains the proposal number of all proposals in
the system. This was done so that a proposal and it's data can be
deleted easily.

**Proposal_Scoring** \*\*\* - Contains the proposal number of all
proposals that have reviews associated with them.

**Proposal_Text** \* - Contains the large text fields for proposals from
the Poposal Entry screen.

  ------------------------- ------ ----------------- -------------- -------------- -----------
  Field name                Type   Description       Function       Example        Notes

  Proposalid                Num    Proposal ID       Relates record 4565           
                                                     to a proposal                 

  Research_Question         Char   The question the                                
                                   research would                                  
                                   answer                                          

  Problem                   Char   **Describe                                      
                                   existing                                        
                                   capabilities                                    
                                   available to                                    
                                   Reclamation from                                
                                   both internal and                               
                                   external sources.                               
                                   Explain why they                                
                                   are insufficient                                
                                   to adequately                                   
                                   serve                                           
                                   Reclamation's                                   
                                   needs**                                         

  Study_Approach            Char   **Why is this the                               
                                   responsibility of                               
                                   Reclamation and                                 
                                   not another                                     
                                   government agency                               
                                   or the private                                  
                                   sector.**                                       

  Technical_Transfer_Plan   char   **The methods and                               
                                   approaches you                                  
                                   will use to                                     
                                   answer your                                     
                                   research                                        
                                   question**                                      

  Qualifications            Char   Team                                            
                                   Qualificatiions                                 

  Comments                  Char   Comments and                                    
                                   additional                                      
                                   information                                     

  Accomplishments           Char   The previous                                    
                                   fiscal year                                     
                                   accomplishments                                 

  Next_Years_Plans          Char   Next years plans                                

  Lessons                   Char   Lessons learned                                 

  Sharing                   Char   The effective and                               
                                   ineffective ways                                
                                   you found to                                    
                                   engage users of                                 
                                   your end                                        
                                   products.                                       

  Partnerships              Char   How you developed                               
                                   successful                                      
                                   partnerships for                                
                                   this R&D.                                       

  Nextsteps                 Char   State where you                                 
                                   believe research                                
                                   in this area                                    
                                   should go next                                  

  Changes                   Char   Summarize the                                   
                                   changes you made                                
                                   to this R&D                                     
                                   proposal to                                     
                                   reflect the                                     
                                   current status of                               
                                   this project.                                   

  FY                        Num    Fiscal year       Provides the   2004           
                                   record was        way to version                
                                   created in        a proposal                    
  ------------------------- ------ ----------------- -------------- -------------- -----------

**Realigned_Totals** \*\*

**Relevancy_Scoring** \*\*\* - Comments associated with Relevancy
Reviews.

  ------------------------ ------ -------------- -------------- -------------- ---------------
  Field name               Type   Description    Function       Example        Notes

  Proposalid               Num    Proposal ID    Relates record 4565           
                                                 to a proposal                 

  Username                 Char   Login username                               
                                  of reviewer                                  

  Like_Most                Char   What do you                                  
                                  like most                                    
                                  about the                                    
                                  relevancy of                                 
                                  this proposal                                

  Concerns                 Char   What concerns                                
                                  do you have                                  
                                  about the                                    
                                  relevancy of                                 
                                  this proposal                                

  Make_Better              Char   What would                                   
                                  make this                                    
                                  proposal more                                
                                  relevant                                     

  Recommendation           Char   Other comments                               
                                  and concerns                                 

  Status                   Char   Status of                     draft, final   
                                  review (draft                                
                                  or final)                                    

  Objective1               Num    Scrore for                                   This and the
                                  Objective 1                                  next seven
                                                                               columns were
                                                                               used in FY2004

  Objective2               Num    Scrore for                                   
                                  Objective 2                                  

  Objective3               Num    Scrore for                                   
                                  Objective 3                                  

  Objective4               Num    Scrore for                                   
                                  Objective 4                                  

  Rationale_Obj1           Char   Rationale for                                
                                  rating                                       
                                  Objective 1                                  

  Rationale_Obj2           Char   Rationale for                                
                                  rating                                       
                                  Objective 2                                  

  Rationale_Obj3           Char   Rationale for                                
                                  rating                                       
                                  Objective 3                                  

  Rationale_Obj4           Char   Rationale for                                
                                  rating                                       
                                  Objective 4                                  

  Total_Score              Num    Overall rating                               In FY2004, this
                                                                               column was
                                                                               computed (
                                                                               total of
                                                                               Objective1-4)

  Submit_User              Char   Login user                                   To avoid a join
                                  name of the                                  with the
                                  proposal                                     proposal_info
                                  submitter                                    table

  Output_Area              Char   Output Area of                               
                                  the proposal                                 

  Overal_Rating            Char   Overall Rating                               
                                  (text)                                       

  Additional_Association   Char   Conflicts of                                 
                                  interest of                                  
                                  the reviewer                                 

  FY                       Num    Fiscal year    Provides the   2004           
                                  record was     way to version                
                                  created in     a proposal                    
  ------------------------ ------ -------------- -------------- -------------- ---------------

**Relevancy_temp** \*\*

**Rel_Review_Totals** \*\*

**Review_Questions** \*\*\*\* - Contains FY2004 text for column heading
for Review questions. This was done so that if questions change as in
FY2005, the original question heading can be pulled from the database.

**Settings** \*\*\*\* -- Contains the settings for the system (Fiscal
Year, User settings)

  --------------------- ------ -------------- -------------- -------------- -----------
  Field name            Type   Description    Function       Example        Notes

  FY                    char   Fiscal year                                  This year
                               for the                                      is changed
                               reviewer                                     just prior
                                                                            to the
                                                                            Relevancy /
                                                                            Technical
                                                                            Reviews.

  Submitter_FY          Char   Fiscal Year                                  This year
                               for the                                      is changed
                               Proposal                                     just prior
                               Submitter                                    to award
                                                                            (usually in
                                                                            Oct. --
                                                                            Nov)

  Proposal_Submitters   Char   Indicates if                  on, off        
                               Proposal                                     
                               Submitter may                                
                               use PropC                                    

  Relevancy_Reviewer    Char   Indicates if                  on, off        
                               Relevancy                                    
                               Reviewers may                                
                               use PropC                                    

  Technical_Review      Char   Indicates if                  on, off        
                               Technical                                    
                               Reviewers may                                
                               use PropC                                    

  Reviewer              char   Indicates if                  on, off        This was
                               Relevancy or                                 not tuned
                               Technical                                    on for the
                               Reviewers may                                FY2006
                               log in to view                               year. This
                               all comments                                 was usually
                               of other                                     turned on
                               reviewers                                    in Dec or
                                                                            Jan after
                                                                            award.
  --------------------- ------ -------------- -------------- -------------- -----------

**Suggestions** \*\*\*\* - Contains suggestions from users on how to
improve the system.

**Tasks** \* - Steps to complete for proposals.

  ------------------ ------ -------------- -------------- -------------- -----------
  Field name         Type   Description    Function       Example        Notes

  ID                 num    Sequence       Makes record                  Assigned in
                                           unique                        sequence

  Proposalid         num    Proposal ID    Relates record 4565           
                                           to a proposal                 

  Product_Name       char   Product Name                  River          
                                                          restoration    
                                                          tools          

  Other_Product      char   Addition info                                

  Due_Date           char   Project date                                 
                            which proposal                               
                            submitter will                               
                            finish the                                   
                            product                                      

  Documented         char   How documented                Manual,        
                                                          Guidelines,    
                                                          Reporrt        

  Other_document     char   Additional                                   
                            info                                         

  Actual_date               Actual date                                  
                            proposal                                     
                            submitter                                    
                            finished the                                 
                            product                                      

  Comments           char   Proposal                                     
                            submitted                                    
                            comments                                     

  Product_Received   char   Indicates if                  yes or no      
                            the product                                  
                            was received                                 
                            by the                                       
                            Research                                     
                            Office                                       

  FY                 num    Fiscal year    Provides the   2004           
                            record was     way to version                
                            created in     a proposal                    
  ------------------ ------ -------------- -------------- -------------- -----------

**Teammembers** \* - Employees which are Team Members for proposals.

  ------------------------ ------ -------------- -------------- -------------- -----------
  Field name               Type   Description    Function       Example        Notes

  ID                       num    Sequence       Makes record                  Assigned in
                                                 unique                        sequence

  Proposalid               num    Proposal ID    Relates record 4565           
                                                 to a proposal                 

  FirstName                char   First name of                                
                                  team member                                  

  LastName                 char   Last name of                                 
                                  team member                                  

  Discipline               char   Discipline of                 Fisheries      
                                  team member                   Biologist      

  Organization             char   Organization                  US Fish and    
                                  of team member                Wildlife       
                                                                Service        

  Phone                    char   Phone number                                 
                                  of team member                               

  Email_Address            char   Email address                                
                                  of team member                               

  Principle_Investigator   char   Indicates if                  yes, no        
                                  the team                                     
                                  member is a                                  
                                  principle                                    
                                  investigator                                 

  FY                       num    Fiscal year    Provides the   2004           
                                  record was     way to version                
                                  created in     a proposal                    
  ------------------------ ------ -------------- -------------- -------------- -----------

**Technical_Reviewers** \* A list of Technical Reviewers recommended by
proposal submitters for proposals.

  ------------------------ ------ -------------- -------------- -------------- -----------
  Field name               Type   Description    Function       Example        Notes

  ID                       num    Sequence       Makes record                  Assigned in
                                                 unique                        sequence

  Proposalid               num    Proposal ID    Relates record 4565           
                                                 to a proposal                 

  FirstName                char   First name of                                
                                  technical                                    
                                  reviewer                                     

  LastName                 char   Last name of                                 
                                  technical                                    
                                  reviewer                                     

  Discipline               char   Discipline of                 Fisheries      
                                  technical                     Biologist      
                                  reviewer                                     

  Affiliation              char   Affiliation of                US Fish and    
                                  the technical                 Wildlife       
                                  reviewer                      Service        

  Phone                    char   Phone number                                 
                                  of technical                                 
                                  reviewer                                     

  Email_Address            char   Email address                                
                                  of technical                                 
                                  reviewer                                     

  Keywords                 char   Keyword                                      
                                  associated                                   
                                  with this                                    
                                  reviewer (from                               
                                  a table by                                   
                                  Output Area)                                 

  Other Keyword            char   Other key                                    
                                  words not in                                 
                                  key word table                               

  Principle_Investigator   char   Indicates if                  yes, no        This should
                                  the team                                     not be in
                                  member is a                                  this table
                                  principle                                    
                                  investigator                                 

  FY                       num    Fiscal year    Provides the   2004           
                                  record was     way to version                
                                  created in     a proposal                    
  ------------------------ ------ -------------- -------------- -------------- -----------

**Technical_Scoring** \*\*\* - Comments and scores associated with
Technical Reviews.

  ------------------------ ------ -------------- -------------- -------------- ---------------
  Field name               Type   Description    Function       Example        Notes

  Proposalid               num    Proposal ID    Relates record 4565           
                                                 to a proposal                 

  Username                 char   Login username                               
                                  of reviewer                                  

  Like_Most                char   What do you                                  
                                  like most                                    
                                  about this                                   
                                  proposal                                     

  Concerns                 char   What concerns                                
                                  do you have                                  
                                  about the                                    
                                  relevancy of                                 
                                  this proposal                                

  Make_Better              char   What would                                   
                                  make this                                    
                                  proposal                                     
                                  better                                       

  Recommendation           char   Other comments                               
                                  and concerns                                 

  Status                   char   Status of                     draft, final   
                                  review (draft                                
                                  or final)                                    

  Objective1               num    Scrore for                                   This and the
                                  Objective 1                                  next nine
                                                                               columns were
                                                                               used in FY2004.
                                                                               This columns
                                                                               must be kept
                                                                               until the
                                                                               FY2004 data is
                                                                               purged

  Objective2               num    Score for                                    
                                  Objective 2                                  

  Objective3               num    Score for                                    
                                  Objective 3                                  

  Objective4               num    Score for                                    
                                  Objective 4                                  

  Objective5               num    Score for                                    
                                  Objective 5                                  

  Rationale_Obj1           char   Rationale for                                
                                  rating                                       
                                  Objective 1                                  

  Rationale_Obj2           char   Rationale for                                
                                  rating                                       
                                  Objective 2                                  

  Rationale_Obj3           char   Rationale for                                
                                  rating                                       
                                  Objective 3                                  

  Rationale_Obj4           char   Rationale for                                
                                  rating                                       
                                  Objective 4                                  

  Rationale_Obj5           char   Rationale for                                
                                  rating                                       
                                  Objective 5                                  

  Total_Score              num    Overall rating                               In FY2004, this
                                                                               column was a
                                                                               computed (total
                                                                               of
                                                                               Objective1-4)

  Submit_User              char   Login user                                   
                                  name of the                                  
                                  proposal                                     
                                  submitter                                    

  Output_Area              char   Output Area of                               
                                  the proposal                                 

  Overal_Rating            char   Overall Rating                               
                                  (text)                                       

  Additional_Association   char   Conflicts of                                 
                                  interest of                                  
                                  the reviewer                                 

  FY                       num    Fiscal year    Provides the   2004           
                                  record was     way to version                
                                  created in     a proposal                    
  ------------------------ ------ -------------- -------------- -------------- ---------------

**Tec_Review_Totals** \*\*

**User_Account** \*\*\*\* -- User information (User Name, Password,
Rights).

  ------------------- ------ -------------- -------------- -------------- -------------
  Field name          Type   Description    Function       Example        Notes

  Userid              num    User ID                                      
                             sequence                                     

  Username            char   Login user                                   
                             name                                         

  Password            char   Login password                               

  First_Name          char   First name of                                
                             user                                         

  Last Name           char   Last name of                                 
                             user                                         

  Phone               char   Phone number                                 
                             of user                                      

  Organization        char   Organization                                 
                             of user                                      

  Mail_Code           char   BOR Mail Code                                Blank if not
                             of BOR user                                  a BOR user

  Email               char   Email address                                
                             of user                                      

  User_Type           char   User type(S)                  Proposal       The User_Type
                             assigned to                   submitter,     "Locked" is
                             user                          Relevancy      to lock out
                                                           Reviewer,      certain
                                                           Technical      users, i.e.
                                                           Reviewer,      employees
                                                           Locked, etc.   that are
                                                                          retired or
                                                                          left BOR

  Region              char   BOR Region                    LC, MP, PN,    Blank if not
                                                           UC, GP         a BOR user

  Signed_NDA          char   Indicates if                  yes, no        This is reset
                             this user has                                to no at the
                             signed a non                                 beginning of
                             disclosure                                   a review year
                             statement                                    

  BOR_Employee        char   Indicates if                  yes, no        
                             this is a BOR                                
                             employee                                     

  Password_Expire     date   The date the                                 This date is
                             user's                                       set in the
                             password will                                password
                             expire                                       change screen

  Tributary_Lead      char   If user is a                  DW, WS, etc    
                             Tributary                                    
                             Lead, this                                   
                             column                                       
                             contains the                                 
                             first two                                    
                             characters of                                
                             the Output                                   
                             Area this user                               
                             reviews.                                     

  Submitter_on        char   Indicates if   There are      on, off        This does not
                             the user can   situations                    apply to
                             log in as a    when the                      other user
                             Proposal       Research                      typers, just
                             Submitter      Office has                    proposal
                                            turned off                    submitters
                                            submitters,                   
                                            but they want                 
                                            to allow one                  
                                            or more                       
                                            submitters to                 
                                            login                         

  Userlocked          char                                                Not used.
                                                                          Users are
                                                                          locked out by
                                                                          a 'Locked'
                                                                          User_Type

  Acknowledge_Rules   char   Indicates if                  yes, no        The User must
                             the user has                                 chick a link
                             read the rules                               that they
                                                                          have read the
                                                                          rules before
                                                                          they can use
                                                                          the
                                                                          application
  ------------------- ------ -------------- -------------- -------------- -------------

##  

**User_Logins** \*\*\*\* - Contains information about who has logged
into PropC (User Name, date of login, IP Address of user, etc.)

**User_Password_History** \*\*\*\* -- Contains previous users passwords
so that users can not reuse user passwords.

\* Tables that are associated with proposals. These tables retain
multiple versions of proposals (FY2004, FY2005, FY2006, FY2007).
Proposal Submitters and Reviewers can view past versions at any time.
After awards (funding proposals) usually in November of each year,
Proposal Submitters can automatically create a new version of their
proposal(s) by clicking a link on the Proposal Submitter home page. This
process duplicates the entire proposal and opens the new version which
allows the Proposal Submitter to update progress and apply for funding
for next year. At this time there is no process to purge old versions.

\*\* Tables that are generated by scripts for the purpose of joining
data in Select statements.

\*\*\* Tables that are associated with the review process.

\*\*\*\* Tables not associated with proposals or reviews.

## 

## 

## 

##  

Major Event List

  ----------------------- ----------------------- -----------------------
  **Event**               **Type of user done     **When**
                          by**                    

  1\. New proposals       Proposal Submitter      Research Office Call
  entered or funded                               For Proposals (usually
  proposals updated or                            in March / April)
  completed proposals                             
  closed out.                                     

  2\. Proposals submitted Proposal Submitter      
  for review                                      

  3\. Relevancy &         Research Office         April/May
  Technical Reviewers                             
  identified and user                             
  accounts established                            
  and assigned to review                          
  5 -- 10 proposals                               

  4\. Tributary Lead                              
  Review (no longer used)                         

  5\. Relevancy Review    Relevancy Reviewers     July/Aug

  6\. Technical Review    Technical Reviewers     July/Aug

  7\. Program Review and  Regional Coordinators   Sept
  Funding Decision                                

  8\. Funding             Finance                 Oct.

  9\. Partner             Partners                Oct.
  Confirmation for the                            
  previous year                                   
  ----------------------- ----------------------- -----------------------

Below is a detailed list of events.

  ----------------------- ----------------------- -----------------------
  **Event**               Who                     How

  Just prior to the Call  Admin                   Settings Screen
  for Proposals, Set the                          
  Submitter FY to the                             
  coming review year.                             

  Send email to           Admin.                  Setting Screen
  submitters that new                             
  proposals can be                                
  submitted                                       

  Send email to           Admin.                  
  submitters telling them                         
  of the deadline date to                         
  submit proposals                                

                                                  

                                                  

  Assign reviews to       Admin                   Admin Screen- Submitted
  proposals                                       Proposals link

  Clear Non Discloser     Admin                   Admin Screen link
  Agreement Indicator                             
  (this will force a NDA                          
  to be generated when a                          
  reviewer logs on if                             
  they are assigned at                            
  least one proposal                              
  which contains                                  
  proprietary                                     
  information)                                    

  Turn on Relevancy       Admin                   Admin Screen link
  Reviewers                                       

  **Relevancy Review**    Relevancy Reviewers     

  Check percentage of     Admin                   Admin Screen link
  Relevancy review                                
  completion                                      

  Turn off Relevancy      Admin                   Admin Screen link
  Reviewers                                       

  Turn on Technical       Admin                   Admin Screen link
  Reviewers                                       

  **Technical Review**    Technical Reviewers     

  Check percentage of     Admin                   Admin Screen link
  Technical review                                
  completion                                      

  Turn off Technical      Admin                   Admin Screen link
  reviewers                                       

  **Program Review and    Program Reviewers       
  Funding Decisions**                             

  **Steering Team and                             
  Regional Supervisor**                           

  **Finance**                                     

  Turn on Proposal        Admin                   Admin Screen link
  Submitters                                      

  Email Proposal          Admin                   
  Submitters to notify of                         
  funding                                         

  Email Reviewers to let  Admin                   
  them review funding                             
  decision                                        

                                                  

                                                  
  ----------------------- ----------------------- -----------------------

## 

## 

## 

## application.cfm under login folder:

## 

## \<cfapplication name=\"research\" sessionmanagement=\"Yes\" setclientcookies=\"no\" sessiontimeout=\"#createtimespan(1,0,60,0)#\"\>

## \<cfinclude template = \"../ssl.cfm\"\>

## \<cfset DSN = \"ST_fy2006\"\>

## \<!\-\-- CF will not set the client cookies automatically, so

##  set them manually as per-session cookies \-\--\>

##  \<cfif not IsDefined(\"Cookie.CFID\")\>

##  \<CFLOCK SCOPE=\"SESSION\" TYPE=\"READONLY\" TIMEOUT=\"50\"\>

##  \<CFCOOKIE NAME=\"CFID\" VALUE=\"#SESSION.CFID#\"\>

##  \<CFCOOKIE NAME=\"CFTOKEN\" VALUE=\"#SESSION.CFTOKEN#\"\>

##  \</CFLOCK\>

##  \</cfif\>

## 

## The above creates a temporary cookie when a user logs in (as opposed to a persistent cookie). Care must be used if this script has to be modified because if no cookie is created, users may get same rights of the first user to login. 

## 

## 

## 

## 

## 

## 

## application.cfm under other folders:

## 

## \<cfapplication name=\"research\" sessionmanagement=\"no\" setclientcookies=\"yes\" sessiontimeout=\"#createtimespan(0,0,60,0)#\"\>

## \<cfinclude template = \"../ssl.cfm\"\>

## 

## \<cfif NOT isdefined(\"session.loggedin\")\>

##  \<cflocation url=\"../login/login.cfm\"\>

## \</cfif\>

## \<cfif session.loggedin is \"0\"\>

##  \<cflocation url=\"../login/login.cfm\"\>

##  \</cfif\>

## 

##  \<cfif session.demo is \'practice\'\>

##  \<cfset DSN = \"st_play\"\>

##  \</cfif\>

##  \<cfif session.demo is \'actual\'\>

##  \<cfset DSN = \"st_fy2006\"\>

##  \</cfif\>

## \<cfif session.user_type is not \"4\"\>

##  \<cflocation url=\"../login/login.cfm\"\>

##  \</cfif\>

## 

## PROBLEM ISSUES:

1\. There are 4 files which must be changed when the Proposal form is
updated: proposal_entry/proposal_entry.cfm, print.cfm, compare_proposal
and print_history.cfm (used to print past versions of a proposal) and
files starting with "section" under the proposal_entry folder. In
addition, if sections or columns are added or removed from the proposal
form, there are 2 utilities which must be changed in addition to the
add, update and delete scripts: proposal_entry/progress_help.cfm (used
to create a new version of a proposal when a submitter applies for
continued funding) and admin/transfer_proposal_records.cfm (used to
transfer a proposal to another user). Both scripts are similar. They
read each table that contains proposal data and writes the same
information back out to the database under the new fiscal year. This is
what creates a new version of a proposal.

## 2. Partner Confirmation . The 2005 partner confirmation is stored in the 2006 and 2007 versions. This is because some proposals do not have 2007 versions because they were completed in 2006. Most of this data is in the 2007 version because proposal submitters had to update partner data and this update occurred in the 2007 version. The 2004 partner confirmation data is in the 2005 version. Reports are under Admin menu item "Reports for 2006".

## 

## 

## 

## 

## 

## 

## 

## 

## 

## 

##