COEUS is composed of three main modules. Users build proposals in the Proposal Development Module. Once submitted to potential outside sponsors, these works become Institute Proposals and are moved to the Proposal Module. Once funded, the information in the Proposal Module forms the basis for the actual award record in the Award Module.

Is Coeus a financial system?

No, the Coeus program is a proposal and award management tool. It is not a financial system. Sponsored Program administrators and investigators can use Coeus to prepare proposals from the desktop, route them to obtain proper approvals, and submit them to sponsors. Coeus maintains very detailed information on awards. The system maintains a complete history of every change made to a record. So, for example, when you look at the "Money and End Date" screen, you are able to "walk" through the complete history of the award. You can see the notice of award and every modification made to the award. In addition, the Coeus program maintains all agency contacts (in the electronic Rolodex), all reports (financial, technical, property, patents), all terms and conditions of the award, and all required approvals for the award (equipment, foreign travel, and subcontracts).

What is provided with a Coeus site license?

SQL Scripts to create database and index files, foreign and primary keys.

Zip file to create the PowerBuilder source libraries.

Zip file for the application, including the PowerBuilder runtime files.

As you are doing additional development, what kinds of plans do you have for establishing release levels and distributing updates? Is there any kind of maintenance fee? If there is another release, will we have to repurchase the package?

The license fee is $500. For minor releases at MIT, we take the necessary files, zip them up, and put them on the web. There is some version control in the software so that if it is a major release and you want to force the users software to break from it so they can’t use the old client, they will just get a message that says you need to go to the web page to get the new version of the software. Minor fixes are also put on the web for people to download. It would be a different process for other universities, especially if you make modifications to the source code. MIT would need to notify licensees of the modification, and what files were affected. If a licensee modifies source code, MIT does not know. Theoretically, if the changes are for files that have not been modified by the licensee, then the new files could just be copied and used.

Did you track the changes that you made to each file?

We can give an explanation for each of the releases that are given on an object basis. We can tell you on what object, even what script we have made the changes. Any new release would include a document that includes the changes that were made and in what script. The changes could be applied to the source code.

If we purchase this system, will we get a quick training session?

We provide at a minimum one day of consulting from various people. Then depending on your needs, we would provide an additional two days. We also would provide training to your IS folks from our database and/or PowerBuilder folks. Again, this would be at an additional charge. This is why we offer the system for $500. This way you can have it "as is" or get a little more personalized attention if you desire. Some schools prefer to send people to Boston, learn the system and then plan on doing their own in-house training.

System requirements

You will be shipping us Powerbuilder source code. Will we as a result require Powerbuilder development tools to modify or compile that source code?

Yes. Also MIT does ship the actual Powerbuilder run time module, since this can be freely distributed. We will ship an install file for the client that will install the Powerbuilder run time and the application. You can view the application, but obviously to make your own compiles, you would need to purchase the developer’s kit.

What version of Powerbuilder are you using?

We have developed Coeus using Powerbuilder version 5.0.04. We have not moved to Powerbuilder 7 because of lack of support for the Mac platform. Our last major release in Powerbuilder is Coeus3.8. Future development strategy is centered on a Java, but the exact architecture is not yet been finalized.

Do I need to buy a copy of PowerBuilder to run the Coeus program?

You won't need PowerBuilder - unless you are going to become a programmer. We give a runtime with the Coeus program. If you want a report writer, then you'll need something like InfoMaker. Powerbuilder is expensive and is really a development tool for creating Windows applications. Even with the InfoMaker, you can make front-end applications that access the database.

Does Coeus program use a native driver or ODBC driver to access the Oracle database?

To run Coeus program executables, you don't need a PowerBuilder development environment at all. All of the PowerBuilder runtimes are included in the Coeus program installation set. This includes the native drivers for Oracle for runtime (these cannot be used for development) as well as the ODBC driver.

Oracle

What version of Oracle are you using?

Currently we are using Oracle 8.1.7.4.

Can I run Coeus with Oracle 9.x?

Yes, as long as you are using the latest version of Coeus 3.8. We don't anticipate any problems with 9i at this point if you are using the latest Coeus deployment kit. The problems that we had with Oracle 8.1.7.3 (see below) have been fixed and no one has sent us any other issues with Oracle 9x.

What are the problems you experienced with Oracle 8.1.7.3 and how were they fixed?

Manifestation of Problem:

In some places in the Coeus application, when trying to update you will get an error message saying that the inserted value is too large for the column. This problem appeared in Oracle 8.1.7.3 and disappeared in 8.1.7.4, but may reappear in Oracle 9.x (unless you use the updated stored procedures).

Reason:

Using Oracle 8.1.7.3, when a stored procedure is executed from Powerbuilder script, if the OUT argument is defined as a CHAR(1), Oracle returns a 2000 character string. When the application subsequently tries to update the 1 character column using a 2000 character string, you get the error. (This does not happen if you execute the procedure from SQL*Plus.)

There are a number of tables in the database that have columns that are

defined as char(1). Several stored procedures have OUT parameters that are

based on these char(1) column types. These are the stored procedures that

will result in errors.

Solution:

The problem we saw with 8.1.7.3 was only with stored procedures which

are executed from powerscript to retrieve data (not the ones used as

datawindow data sources or the ones used in updates). In Coeus 3.8 (in Nov 2002), we changed the following stored procedure declarations:

create or replace procedure get_budget_category_type

( as_category_code in OSP$BUDGET_CATEGORY.BUDGET_CATEGORY_CODE%TYPE,

as_category_type out VARCHAR2)

create or replace procedure get_cost_element_details

( as_ce_code in OSP$COST_ELEMENT.COST_ELEMENT%TYPE,

as_description out OSP$COST_ELEMENT.DESCRIPTION%TYPE,

ai_category out OSP$COST_ELEMENT.BUDGET_CATEGORY_CODE%TYPE,

as_campus_flag out VARCHAR2 )

create or replace procedure get_person_info

( as_personid in osp$person.person_id%type,

as_full_name out osp$person.full_name%type,

as_directory_title out osp$person.directory_title%type,

as_faculty_flag out varchar2,

as_home_unit out osp$person.home_unit%type) is

create or replace procedure get_person_info_name

( as_personname in osp$person.full_name%type,

as_person_id out osp$person.person_id%type,

as_directory_title out osp$person.directory_title%type,

as_faculty_flag out varchar2,

as_home_unit out osp$person.home_unit%type)

create or replace procedure get_valid_sp_rev_approval_info

(as_sp_rev_code in osp$valid_sp_rev_approval.special_review_code%type,

as_approval_type_code in osp$valid_sp_rev_approval.approval_type_code%type,

as_protocol_number_flag out varchar2,

as_approval_date_flag out varchar2,

as_application_date_flag out varchar2 )

OUT parameters that were based on the CHAR(1) columns were changed to VARCHAR2.

What is MIT's recommended system requirements for Coeus on a Sun Server?
We are expecting no more than 300 total users and not more than 50
concurrent connections.

The drain imposed by Coeus is very minimum. Your server should be sized according to Oracle's recommendations.
We run Coeus on a relatively small server

model:                 COMPAQ AlphaServer - Model DS10
cpu:                     1 CPU - model 6/466 MHz
memory:              512 MB
disk space:          38 GB total is configured, 6.1 GB is actually used space
OS:                     COMPAQ Tru64 version 4.0g
filesystems:          the Oracle data is spread over 6 logical file systems,
                           plus separate file system components for OS and swap area

Is it necessary to use the application with Oracle or would it be possible to use another relational database such as Sybase or IBM's UDB? Does COEUS contain Oracle-specific code or features which would require significant rework on our part if we would adapt it to another database?

We have, at last count, 750 stored procedures. Most of these would have to be tweaked to make the change from Oracle to SQL Server or some other back end database. I'm not sure how much tweaking would be necessary - most of the sql is standard, but I don't know how/if Sybase or UDB uses reference cursors, which we use in Oracle. In addition, even if you changed the stored procedures, you would need to make changes in the Coeus Powerbuilder client to accommodate the stored procedure changes - (mostly because of the use of reference cursors) - so you would need a Powerbuilder developer.

Can you explain why the query screens have custom SQL in the Powerbuilder code, and why there are public selects on some tables?

In prior releases of Coeus, all of the search windows generated custom SQL

commands. In order for this to work in Oracle 7, we had to grant public

select on the tables that were referenced in query screens. As you know,

this opens those tables up to uses coming in from outside the application.

In Oracle 8, we are able to generate the custom SQL via stored procedures

and can thereby remove the public select on the tables. It is our

intention to begin migrating all of the query screens to stored procedures

and remove the custom SQL from the Powerbuilder application.

The custom SQL will still be constructed on the PB Client (pfc_apply event in most cases). Currently the datawindows that do the retrieve are plain SQL data source and in the pfc_apply event we set DataWindow.Table.Select attribute with the new SQL which was constructed. In the new approach for Oracle 8, the datawindow which does the retrieve will have a stored procedure as the data source. This stored procedure takes one argument which is the complete SQL statement. Instead of setting Table.Select and then doing a retrieve, we will now call Retrieve of the datawindow directly with the SQL statement as its argument.

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

Here is a sample stored procedure which will do the retrieve.

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

create or replace procedure dw_get_850_txn_list

( as_Query IN VARCHAR2,

cur_list IN OUT result_sets.cur_generic) is

sql_stmt VARCHAR2(2000);

insufficient_priv exception;

begin

sql_stmt := REPLACE(as_Query, '''''', '''');

open cur_list for sql_stmt ;

EXCEPTION

WHEN insufficient_priv THEN

raise_application_error(-20100, 'Insufficient Privileges to query EDI transaction list');

end;

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

Here is the relevant part of a new sample pfc_apply event

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

ls_WhereClause = f_get_whereclause(dw_query)

wf_to_upper(ls_WhereClause)

ls_SelectString = "select TXN.TRANSACTION_NUMBER ,

TXN.TRANSLATOR_TRANS_NUM, " + &

"TXN.TRANSCATION_ID, TXN.SPONSOR_NAME,

TXN.PI_NAME, " + &

"TXN.TXN_RECEIVE_DATE, TXN.INPUT_FILE_NAME,

TXN.TXN_LOAD_STATUS, " + &

"TXN.MIT_AWARD_NUMBER, TXN.SEQUENCE_NUMBER,

TXN.AWARD_CREATED_BY, " + &

" TXN.AWARD_CREATION_DATE " + &

" from OSP$INCOMING_EDI_TXN TXN "

ls_Query = ls_SelectString + " where " + ls_WhereClause + " order by transaction_number desc "

idw_source.Retrieve(ls_Query)

iw_list.is_PrevQuery = dw_query.Object.DataWindow.Data

close(this)

MAC-specific questions

What are the ShowPages and fixCoeusini applications that get installed with the Mac client?

We are using shareware called ShowPages for printing. ShowPages is a small utility for downloading PostScript code and printing ASCII text files on your PS printer from a Mac. This is a freeware and can be downloaded from www.macdownload.com. The current version is 1.4.1. The Coeus installer will put ShowPages in the same folder as Coeus. In the coeus.ini file there is an entry named “MacPrinterApp= “which should contain the complete path to ShowPages. Coeus invokes ShowPages through an apple script and tells it to print the file. The Apple script looks something like this.

tell application <Full path to showpages>:ShowPages

print file <Full path to ps file>

end tell

FixCoeusini is a perl program that runs during installation of Coeus. It sets the path to Showpages, and also sets the path for the temp directory.

If a user moves the location of Coeus to a different folder on their machine after installation, fixcoeusini should be run again.

Installation

When running installation scripts, I keep getting prompts from sqlplus for a substitution variable. What’s going on?

This is because ‘&’ is a special character for SQLplus that causes it to prompt the user for input. Go to options>environment and for the define option, set the value to OFF.

Which tables are the basic ones we must have in place first in order to build/test the other data? Is there a specific sequence that we need to consider when populating the Cost Elements, Rates, and other Administrative tables Do we have to assign the rules first, or what?

See the Coeus 12 step document (http://coeus.mit.edu/)

I'm having a problem with coeus3.6. I'm having problems with the unit_number in the osp$user_roles. Is the unit number hard coded to '000001' in the software or am I missing something in one of the tables?

The 000001 must be the top level in the unit hierarchy. For units lower in the hierarchy, you can use more/less digits, but the top level unit in the hierarchy must remain 000001. It is hard coded in the application so that Coeus "knows" it is at the top level of the unit hierarchy.

I’m getting errors when I try to insert rows into tables. Why?

The maximum length of the user_id in Coeus is 8 characters. Oracle allows you to create a user with a longer length, but then you will run into problems when updating the update_user column in tables.

What is the prfile32 application that is installed with Coeus?

PrintFile (prfile32.exe) is a freeware MS Windows utility program that will enable you to print files fast and easily. The program recognizes plain text, PostScript, Encapsulated PostScript (EPS) and binary formats. PrintFile has to be in the same folder as Coeus. Coeus invokes PrintFile as an external application using PB Run() command and passes the postscript filename as the argument. PrintFile uses an INI file Prfile.ini. This file should be in Windows directory. The settings for this INI file should be as follows

[General]

Version=2.1

[Settings List]

Default settings=1

[Default settings]

ShowPrintDlg=0

QuitFromComLine=1

ProgrBar=1

PSEOFChar=0

UseRange=0

The above settings will send the file to your default printer and PrintFile will exit after printing the file.

Details about PrintFile can be found at http://hem.passagen.se/ptlerup/

Can you explain all the entries in the coeus.ini file?

Section	description
[Database]	Contains information necessary to connect to your database. The only change you should have to make is the ServerName. If you are using Oracle 8, you can still use the Oracle 7 driver (DBMS entry).
[School]	Value for Name is used in reports (Award Notice, proposal Notice..) where a school name is printed. Acronym is used where MIT is used.
[Narrative]	Contains information for saving the proposal narrative. Activex = 0 is the normal setting. ‘1’ indicates that Acrobat reader will open as an Active X control inside Powerbuilder. ReaderPath – defines the path for the location of Acrobat Reader on client machine TEMP - PDF files are stored in the database as Binary Large Objects (BLOBS). When you try to view a PDF file, Coeus will retrieve the Blob and write it to the directory specified in this entry. Coeus then calls Adobe Reader, passing it the path and file name. When you close Adobe Reader, the file in the temp directory will be deleted. If you don’t have anything in this entry initially when you log into Coeus, Coeus will update this entry in the ini file. NOTE: The Coeus installer will create a temp directory as a subdirectory of the install directory.
{Feed}	Defines the directories on the server where Coeus will write files used for its external system feeds. This is only used by MIT.
[Application]	Used to control the viewing of the initial information message. The value of ‘message_seen’ in this section should initially be set to 0. MacPrinterApp = path where the ShowPages application resides.
[EDI]	Defines the directories on the server where Coeus will write EDI files.

Can you explain all the entries in the parameter table?

Parameter	Description
AWARD_RETRIEVE_LIMIT	Limits the maximum number of rows retrieved in award searches
CFDA_CODE	This is used in EDI. The value of this parameter should be the Notice of Opportunity code for CFDA
CLIENT_DOWNLOAD_LOCATION	URL of latest version of Coeus Download. This URL will be displayed in the message if the user is using an outdated version of Coeus
COEUS_CURRENT_VERSION	Current Version of Coeus Database. Users will see a warning message each time they login to the application if their client version is prior to this value.
COEUS_MODULE_AWARD	Module code for Award. Code from osp$coeus_modules table
COEUS_MODULE_DEV_PROPOSAL	Module code for Development Proposal. Code from osp$coeus_modules table
COEUS_MODULE_NEGOTIATION	Module code for Negotiation. Code from osp$coeus_modules table
COEUS_MODULE_PERSON	Module code for Person Code from osp$coeus_modules table
COEUS_MODULE_PROPOSAL	Module code for Institute Proposal. Code from osp$coeus_modules table
COEUS_MODULE_SUBCONTRACT	Module code for Subcontract. Code from osp$coeus_modules table
COST_SHARING_COMMENT_CODE	Code from osp$comments table (Comment type lookup table) for Cost Sharing Comments
CURRENT_FISCAL_YEAR	MIT SPECIFIC – used in SAP feed
DEFAULT_BUDGET_COST_ELEMENT	Used in award budget – make sure there is a cost element set up in OSP$COST_ELEMENTS
DEFAULT_TABLESPACE	Database default tablespace
DISCLOSURE_RETRIEVE_LIMIT	Limits the maximum number of rows retrieved in disclusure searches.
EB_ON_LA_RATE_TYPE_CODE	MIT SPECIFIC
EB_RATE_CLASS_CODE	Code for rate class of type 'EB'. Code from osp$rate_class table WHY DO WE USE THIS
EOM_PROCESS_DATE	Date of previous EOM process. EOM process osp$eom_amount_info_table. This process calculates the change in obligated and anticipated amounts in awards since the last run of EOM process. For every award that was modified or created it inserts a row in osp$eom_amount_info table with the changed amounts
EOM_PROCESS_USER	User who ran the EOM process last time
FELLOWSHIP_OSP_ADMIN	The value for this parameter should be the name of the person who should receive award notice for Fellowships. i.e If award type is Fellowship, this name will be printed in award notice.
FISCAL_CLASS_CODE	Code for report class "Fiscal"
GENERIC_SPONSOR_CODE	Used for printing generic sponsor forms – make sure this sponsor exists in OSP$SPONSOR. We use MIT as the generic sponsor. This is the sponsor you will use to load generic forms.
INDIRECT_COST_COMMENT_CODE	Comment type code for Indirect cost Comments
INTELLECTUAL_PROPERTY_CLASS_CODE	Report class code for Intellectual property reports
INVOICE_INSTRUCTION_COMMENT_CODE	Comment type code for Invoice instructions
JOBCODE_VALIDATION_ENABLED	Used for validation of job code : cost element mapping in proposal budget.
LA_RATE_CLASS_CODE	MIT SPECIFIC
MIT_IDC_VALIDATION_ENABLED	Used for validation of rates in MIT accounting system.. Value of ‘1’ enables rate pair validation. If this is set, indirect cost rates must be input in pairs (on and off campus rates) that are validated against values in osp$valid_rates.
NEGOTIATION_RETRIEVE_LIMIT	Limits the maximum number of rows retrieved in negotiation searches
OLDEST_SUPPORTED_VERSION	Oldest version of the client that can be supported by this version of the database. Older version of client applications will not be allowed to connect to the database
PERSON_RETRIEVE_LIMIT	Limits the maximum number of rows retrieved in person searches
PROCUREMENT_CLASS_CODE	Report class code for Procurement reports
PROPERTY_CLASS_CODE	Report class code for Property reports
PROPOSAL_COMMENT_CODE	Comment type code for general comments in institute proposals
PROPOSAL_IP_REVIEW_COMMENT_CODE	Comment type code for IP review comments
PROPOSAL_IP_REVIEWER_COMMENT_CODE	Comment type code for IP Reviewer Comments
PROPOSAL_LOG_RETRIEVE_LIMIT	Limits the maximum number of rows retrieved in proposal log searches
PROPOSAL_RETRIEVE_LIMIT	Limits the maximum number of rows retrieved in proposal searches
PROPOSAL_SUMMARY_COMMENT_CODE
PUBLIC_MESSAGE_ID	Controls the ‘message of the day’ along with the value of message_seen in the coeus.ini file. The parameter value should be set to the message_id of the message you want displayed. If the value of ‘message_seen’ in coeus.ini is not the same as the value in PUBLIC_MESSAGE_ID, the message will be displayed. Messages are stored in OSP$MESSAGE table. When the user clicks the ‘do not show me this anymore’ checkbox, the ini file’s ‘message_seen’ value will be set to the value of the public_message_id. The value of ‘message_seen’ in the Application section of Coeus.ini should initially be set to 0. To display a new message, insert a new message into osp$message and update the value of ‘public_message_id’ in OSP$PARAMETER with the new message_id.
REPORTING_RETRIEVE_LIMIT	Limits the maximum number of rows retrieved in award reporting searches.
ROLODEX_FEED_DATE	Timestamp of last Rolodex Feed.
ROLODEX_FEED_USER	USER who ran the last Rolodex feed
ROLODEX_RETRIEVE_LIMIT	Limits the maximum number of rows retrieved in rolodex searches
SAP_FEED_ENABLED	MIT SPECIFIC
SCIENCE_CODE_RETRIEVE_LIMIT	Limits the maximum number of rows retrieved in science code searches
SPECIAL_RATE_COMMENT_CODE	Comment type code for Special rate comments in awards
SPECIAL_REVIEW_COMMENT_CODE	Comment type code for Special review comments
SPONSOR_FEED_DATE	Timestamp of last SAP sponsor Feed.
SPONSOR_FEED_USER	USER who ran the last SAP sponsor feed
SPONSOR_RETRIEVE_LIMIT	Limits the maximum number of rows retrieved in sponsor searches
SUBCONTRACT_RETRIEVE_LIMIT	Limits the maximum number of rows retrieved in subcontract searches
TECHNICAL_MANAGEMENT_CLASS_CODE	Report class code for Technical/Management reports
TEMPORARY_TABLESPACE	Database temporary tablespace
UNIT_RETRIEVE_LIMIT	Limits the maximum number of rows retrieved in unit searches
USER_RETRIEVE_LIMIT	Limits the maximum number of rows retrieved in user searches
VACATION_ON_LA_RATE_TYPE_CODE	MIT SPECIFIC
VACATION_RATE_CLASS_CODE	Code for rate class of type 'Vacation'. Code from osp$rate_class table

User interface

Query screens - How can I find proposals with titles that contain certain words?

You would use the “like %xx%” construct. So, if you wanted to
find all awards with the word "fusion" in the title, just go to the title
field, type like %fusion% . The “%” on either side of the argument ensures that I got any award where the letters fusion are anywhere in the title. Using the percent sign only at the beginning will give you all
titles that end with “fusion”. Using the percent sign only at the end will give you all titles that begin with fusion. For example,

like %fusion% would find titles "Jimmy's Fusion Experiment", "Fusion for
Dummies", and "How to Find Fusion"
like %fusion would ONLY find "How to Find Fusion"
like fusion% would ONLY find "Fusion for Dummies"

Security and Roles

What kind of security system does the system have and how is the security handled?

Users are authenticated using Oracle userid/password.

We use stored procedures to read and update the database. Individual users are not given explicit permissions on any tables (with some exceptions – users never have update, insert, or delete on any tables, but have select on a few). Within the Coeus, application defined user roles restrict or provide access to Coeus modules and functions based upon the individual and the associated rights for that user.

Connections between the client and the database are encrypted via Oracle’s ANO product.

What if a user forgets a password? how do you reset it?

If a user forgets the password, you can use Oracle to reset it. You need to use Oracle directly to remove a row from the first_time_logon table to force the user to change their password

How are roles assigned across multiple units? For example, if we have a Business Manager that manages more than one department, how do we set up this person within Coeus to have access to more than one unit?

There are two basic points that can clarify this issue. The first is that in Coeus, a user has only one home unit, although a user can have roles in multiple units. The second point is related to the user interface of the User Maintenance window. The “Add new User” icon will add a new Coeus user. This should be used only if the the person has not already been established as a Coeus user. The “Search for Users” icon is used to find users who already exist in Coeus.

So if you have created a user in unit A and you want to give that user roles in unit B, then you should find unit B in the unit hierarchy, open the user maintenance window, and use the "Search for user" icon. Now find the Coeus user ID and click ok. You will see the person listed in the list with their "home" unit. You can now assign roles for that user in the new unit.

What are the meanings of the colored roles (red, yellow, blue)?

Blue roles - Proposal level roles - you can assign users to default proposal roles at the unit level. Then all proposals created in the unit will inherit all proposal level roles and the users assigned to these roles. These default assignments can be changed at the proposal level. If required, the Aggregator of the proposal can modify the default assignments for individual proposals, thereby granting additional access or restricting access as appropriate.

Yellow roles - Department level roles - A user having a role at any unit in the hierarchy can exercise his/her privilege only on that unit. Optionally, the descend flag may be set on a particular role assigned to a user which allows the user to perform the action to subordinate units within the hierarchy.

Red roles - Institute level roles - roles which have a system wide impact. These are OSP office roles.

What is the difference between the proposal creator and aggregator roles?

You must carry the Proposal Creator role in a unit before you can create a new proposal for that unit. The Aggregator Role, which carries all proposal level privileges, is granted by the system when a new proposal is created.The role is also granted to anyone who carries the default aggregator role for that unit (as defined in the user maintenance for the unit). In the proposal roles window you can remove the aggregator role from people if you want.

When you say “anyone who carries the default aggregator role for that unit", which unit are we referring to here--the home_unit in the osp$person table or the unit_number in the osp$user table?

Actually neither.. The unit involved is the unit in the osp$user_roles table...When you assign users to any yellow or blue roles, you are assigning them those roles at the unit that the user maintenance screen is opened for. So let's say you have the unit hierarchy window open and you select the Chemistry Dept. Then you open the User Maintenance window for Chemistry. Add some roles for the person...The roles are for Chemistry, which may or may not be the user's home unit -

How do roles descend? (not necessarily into other departments. For example, if you are a System Administrator (DEPT role), are all the roles listed below the sys. admin. role automatic or does each role have to be specified for any given person?)

The Descend flag is used to indicate that you can carry that role in any unit lower in the hierarchy. So, for example, if you are the System Administrator in the Dean's office, you can turn on the descend flag so you can perform system admin functions in all of the departments that report to that dean. This way , you don't have to explicitly grant a person the role in every department.

Most roles will descend except blue (proposal) roles and the yellow (departmental) Proposal Creator role.

How do I know what rights are associated with a role?

To see the rights associated with each role, highlight the role and on the menu, select View --> Rights. This will show you the rights associated with the Role.

What does a system administrator do at institute level verses department level?

In general, the System Administrator for the unit will carry all of the rights associated with the other yellow maintenance roles associated for the unit. The System Administrator at the Institute (red-role) level can grant other red-level roles. The system administrator at the yellow-role level can only grant other

yellow roles.

User maintenance

Can users be deleted?

We never delete a user since it is possible that a user was included in a routing rule at one point in time. To remove someone's access to the system, change the user status to "inactive" This will prevent the user from logging into the system. It will not, however, delete the Oracle user which you should do from a SQL prompt or develop some other alternative that works for your particular institution.

When I try to create a new user for unit 000001 by finding him/her in the person table, I get the following message: "The selected MIT personbelongs to Unit XXXXXX. Do you want to create a user record with this person assigned to unit 000001" I have to
say Yes if I want to create the user, but what are the implications of saying Yes? I note that this does not change the user's home unit in the person table. I also note that once you create a user you can move him/her to another unit. Why isn't the unit number in the user table the same as the unit number in the person table?

The “home unit” for a person is the unit that is in the OSP$PERSON table, which is from our warehouse. This may be different from the unit where the PI conducts research. For example, Prof. Dowdy's home unit might be Physics, but he does most of his research out of the Bionic Rodent lab. So when you add him as a Coeus user, you would add him in the Bionic Rodent lab unit, where he will have the Create Proposal role. Business rules that are applied in building the proposal routing maps are dependent on the proposal lead unit, which would be the Bionic Rodent lab unit. We use the home unit (in osp$person) more for reporting purposes so the Physics department can gather data on where all their faculty are doing research. That's why that message comes up, and there aren't really any implications in saying 'yes'

While looking through the ERD and data dictionary, it appears to me that the Home_Unit for OSP$Person is independent of Unit_Number in OSP$Unit. As an illustration of potential implications, I could get different results from the following two queries:

-I might run a query of all active awards by lead unit or

-I might run a query of awards by PD and then segment into home units of PDs (which

by definition at EDC is the lead unit for an award.

You are right - your queries may get different results. The home unit in osp$person is really not too important to us - it is not a foreign key because we may have cases where our human resources hierarchy is different from the one in Coeus - so in the feed of osp$person, the home_unit may in fact not exist in our osp$unit table.

The lead unit of the proposal/award is really the important one. Remember, when you create a proposal, the lead unit is by definition the unit for which the PI has the PROPOSAL CREATOR role. So if you set up your environment so that PIs always create proposals in their home units, then your queries would get the same result. But for us, PIs often do research in units other than their "home unit", as defined by personnel. So the lead unit will be different from the home unit.

Sponsors, Organization, Subcontracts and Rolodex

What is the relationship between OSP$SPONSOR, OSP$ORGANIZATION, and OSP$ROLODEX?

OSP$SPONSORS contains entities that award funding for projects. These could be agencies or other schools (if Harvard subcontracts out to us, Harvard is the prime sponsor, and needs to be in OSP$SPONSOR) When you create a new sponsor in OSP$SPONSOR, the system inserts a row into OSP$ROLODEX with the sponsor’s base address. You can add multiple rolodex entries as contacts for that sponsor, but there is only one ‘base address’ rolodex entry for a sponsor. It cannot be modified.

OSP$ORGANIZATION contains entities that are subcontractors, or performing organizations. An organization has a contact address, which is a rolodex entry.

(we subcontract out to Harvard, Harvard needs to be in OSP$Organization)

OSP$ROLODEX contains entities that are base addresses of sponsors, people who are sponsor contacts, organization contacts, and other people at your institution who are not in OSP$PERSON.

It appears that within subcontracts, the organization ID is independent of the rolodex contact ID so I can have a contact for a subcontract outside of the institution officially accepting the contract.

Subcontractors are added to OSP$ORGANIZATION . The CONTACT_ADDRESS_ID is a rolodex entry for the organization contact. When you add the contact (from the organization window), you should already have added the rolodex id.

The confusion I think is that when you add the rolodex entry, you can add a sponsor that the rolodex entry is linked to. (You will be forced to do this in 3.8 in fact). The sponsor really does not have anything to do with the Organization. We will be using a dummy sponsor to link rolodex ids to if they are just people who are organization contacts.

To put it another way, OSP$ROLODEX contains entities that are base addresses of sponsors, people who are sponsor contacts, organization contacts, and other people at your institution who are not in OSP$PERSON. So when you pick an organization contact from the rolodex, you can pick anyone in the rolodex.

Should every user of Coeus at my institution be in the Rolodex as well?

No, your users are picked up from OSP$PERSON table, if you choose to

populate the person table first and link users to entries in OSP$PERSON.

This is highly recommended. You should have a rolodex entry for the

contact person for your organization in the roldex table though. This is

the person referenced as the contact person from ORGANIZATION table.

I noticed in the OSP$SPONSOR Table that the field COUNTRY_CODE is set

for three characters. However, when I do a search on Sponsors, Coeus returns

the full country name. Is there somewhere in Coeus where I can find the

three character codes for the countries or should I just add the country

name to the OSP$SPOSNOR Table? I was wondering whether Oracle would reject

adding a full name if the code is supposed to be only three characters.

All country codes are in osp$country_code. You can do code table

maintenance in Coeus and add country codes.

When going to add a rolodex entry I noticed Rolodex References...What is this?

This shows where this rolodex entry is being used. In the event we find duplicate rolodex entries, we must find the entries that we want to remove before we would be able to delete the rolodex. From this icon, you can see where this rolodex is being used in the system.

Proposal development

How do I get the lead unit of a proposal set to what I want?

The lead unit of the proposal MUST be in the unit where you are creating the proposal. If you want to create a proposal in different unit, then you must be a Proposal Creator in that unit. Once you have the Proposal Creator role in more than one unit, a window will appear and ask you which unit you wish to work in. Select the appropriate unit to begin your proposal.

Rules

The validation rules don't work - at least not the way we think they should. For example, if cost element 8230 (grad staff) is present, then cost element 8270 (grad fee remits) must also be present. We only want notice if it is missing.

This rule will work if you have set it up correctly. However, if you put 8230 in period one and put 8270 in period two, the validation rule will pass. That is, the system looks at all periods when looking at cost elements. It doesn't validate year by year.

Proposal budget

We only want to use summary budget information. Can we just enter summary information?

You could use the summary tab for a high-level budget for routing purposes.

But if you look at the data required on the forms, you will see that there is no way there is sufficient level of detail on the summary tab to print agency forms. There are no calculations performed on the summary tab. All calculated amounts are driven from the period tabs and/or the person detail windows. So, you will get no overhead, EB, inflation, etc if you only use the summary tab. There simply isn't enough information on the summary tab to accurately (or even come close to) produce the calculated/secondary costs.

Can you explain the EB rate methodology? Do all employees involved with a proposal get the same rate?

Employee Benefit rates are tied to the activity type of the proposal, but not all employees get the same rate. On the rate screen, the top portion shows the type of rate, like Employee Benefits... The bottom portion of the screen should have as many entries as you have different rates. Therefore you must be able to map your cost elements to the EB rate types that you establish.. So, if you have 19 faculty benefit rates, then you will need to have at least 19 different cost elements to map those rates to. The rates are applied at the cost element level, not the employee level. So, you can have many employee benefit rates in the same proposal as long as the persons are mapped to the correct cost element. If you don't do this in your financial/payroll system, you should create multiple cost elements within Coeus to map the benefit rates. Then on the second tab of the rate screen, you will use the screen to create as many rates as required.

How are EB and vacation calculated?

An example is probably the best way to explain this.

Let's say you have the following rates:

EB rate for FY 2001 is 21%

EB rate for FY 2002 is 22%.

Inflation rate for FY2002 is 3%.

Budget period is 1/1/01 - 12/31/01

Steve Dowdy is at 100% for the entire period (for simplicity).

Steve's base salary is 100,000 effective 1/1/01

Steve's salary for the period 1/1 - 6/30 is 50,000.

His base salary for the period starting 7/1/ is 103000. (3% inflation),

so his salary for 7/1 - 12/31 is 51500.

So his total salary for the entire year is 50,000 + 51,500 = 101500.

Now EB is calculated.

First for FY 2001, 21% of 50,000 = 10,500.

Then for FY 2002, 22% of 51500 is 11,330.

So total EB is 10,500 + 11,330 = 21,830.

Vacation is calculated essentially the same way as EB.

My understanding is that the proposal module needs employee salary review dates to be standardized. Can you advise if there is a specific way that these need to be standardized? For example, does Coeus care if we select the first of each month, the 15th, or the last day of a month?

Coeus applies inflation at the anticipated raise date for each class of employee. Coeus does not care what the raise date is.

To make this work, you must first set up your inflation rates in Coeus. Go to the Rates Maintenance screen (Admin -> Rates). For the Inflation rate class (on the top part of the screen), you can set up multiple "rate types" (on the bottom part of the screen). You would create different rate types for different classes of employees and other inflation factors. So for example, you might have rate types for Faculty salaries, Administrative Salaries, Support Staff Salaries, and Materials and Services.

Then you set the rates and effective dates for all the Inflation rate types by going to the Institute Rate tab on the screen. For each Inflation rate type you set the rate and the start date (this is your salary review date).

Then you must make sure you have mapped your cost elements to get the proper rate. (Admin -> Cost Elements -> Valid Rate Types).

Now, in the budget, for any cost element that is a Personnel category element, the inflation will be applied on the start date for the inflation rate type that the cost element is mapped to. So if the project period is Jan 1 to Dec 31, but Faculty Salaries increase on July 1, Coeus will apply inflation to Faculty line items on July 1.

For all non-personnel cost elements, the inflation will be applied at the project year cycle (i.e., standard type of 12-month inflation).

I put foremen in as a cost element and noticed that inflation was not applied. I realized that the cost element for formen was no tmapped to receive inflation. So I went to the cost element screen and gave it inflation. It still didn’t get inflation in my budget.

[This question is something that came up in testing. If your system is in production, presumably all your cost elements are mapped correctly.] In order for inflation to get applied to later periods in the budget, you have to remove the cost element from your budget, save the budget, and then go back in and then apply inflation to later periods.

What is the use of the Academic/Cycle/Calendar/Summer period column in thePersonnel Details Budget screen?

This data is used only for EDI transactions.

I have a line item for Graduate Students in my budget with a $5000 cost. This amount is not displayed in the Salary window for the budget.

The person salary screen in the budget displays salaries for PERSONS - so if you have a personnel cost element without a person attached, it won’t show in the salary window – but it will show up on the summary tab.

The travel detail on adding the budget seems quite limited. I usually

Break down the airfare, per diem and ground transportation and I couldn't

add it all in in the description area.

For the travel detail, you could put multiple travel cost elements in the budget, and put airfare, ground transportation etc as the line item description. But you might want to put it all together in one travel line item, and use budget justification to specify the breakdown.

How does budget justification work?

You can add justification at the line item level, or at the level of the entire budget. To enter line item justifications, select a line item and double click to open the line item detail. Click on Justify. Then you can a budget justification for that line item. When you go back to the budget period screen, you will see the little icon to the right of the line item no longer has a red X through it - indicating that it has a justification. Then you can click on that icon to see the line item justification.

To enter justification at the budget level, you choose the Edit -> Budget Justification menu item. You can then enter text for the budget justification. You can also click the ‘Consolidate’ button to bring in any line item justifications you had previously entered.

When I award a proposal do I have to re-enter the budget through the Money and End Dates tab or is the proposal budget transferred to the award and available for edit?

Budgets do not come forward... too often the amounts are different and it takes longer for our data entry operators to look at the number and decide whether it needs to be updated than to simply enter the data. Only a shell of information

comes forward from the proposal to the award (sponsor, title, PI, cost sharing, special reviews, indirect cost).

How do I add TBAs to the budget?

When you add a TBA in a budget, you should add the appropriate TBA for the budget category (e.g. for Graduate student, add a TBA-Graduate).

You have to enter a job code. If you pick TBA-Graduate, the job code should already be filled in. The specific job code really doesn't matter though. It won't affect the calculation. If for some reason, you do enter a job code, the only things you should NOT enter are 'LA' or all 0's or all 9s.

You should not enter TBAs for Senior Personnel. These will not be accepted in EDI transmissions to NSF.

Proposal Logs

Under what circumstances would we create a "Temporary Proposal Log"?

We added the temporary proposal log when we added the Negotiations module. If we start a negotiation on a proposal before it even walks in the door to OSP, then we create the temporary log. This is because negotiations are tied to proposals (or proposal logs), so to start a negotiation we need a proposal log. When the actual proposal walks into OSP (often carried by the swamp reptile himself), then we create the proposal log... when the proposal log is being created, Coeus searches the temporary logs and if there are any temporary logs with the same PI, it opens a window asking if you want to convert the temporary log to a permanent one (bringing over the negotiation stuff too)...

Institute Proposals

What is the percentage column for on the cost sharing tab?

The percentage column indicates the percentage of the total budget that the cost sharing represents. We usually don’t use this column.

Awards

When trying to update the indirect cost screen in the award module, I get an error telling me that the On and Off rates is not a valid pair. What’s going on?

What does the MIT_IDC_VALIDATION_ENABLED parameter do?

To eliminate this error, disable the rate pair validation by setting the MIT_IDC_VALIDATION_ENABLED parameter to a 0. (Central Admin -> Parameter Maintenance).

At MIT, any time a user enters a rate for the indirect cost screen, the

rates must be input in pairs - the on and off campus rate. These valid

pairs are placed in the table: osp$valid_rates. This table validates the combination of the rates to ensure our accounting system gets the correct information. This table isn't linked to the other rate tables. There is no maintenance screen for this (at this time). When you enter a rate pair, you must give the start date of the rate pair. This functionality is to assist with the "fixed for the life of the award" problem while trying to fix your indirect cost rates for federal awards. The start date, therefore, may or may not be the first day of the fiscal year since it depends on the start date of the competing segments.

If you enable the validation, then you must have every possible rate pair and the start date will become required.

Can you delete an award?

You can't delete an award. We set the award to “Inactive”.

Can you clarify the different award statuses?

Active - normal award that has been received. Billable.

Inactive - woops, we made a mistake and this should have never been created. When we set the status to "Inactive", we go to the money and end date screen and make sure all the money is zeroed out. We set the beginning and ending date to be the same. We have a sponsor code called "Account Canceled" and we change the sponsor to that code. We update the title of the project to "Account Canceled"

Pending - not yet billable. This can be done on the front end of the award where we are exercising our 90-day pre-award costs under the expanded authorities. Or, we will do this on the back-end of a contract where we have not received our date extension, therefore we can no longer bill the award, but are continuing to allow the department to spend.

Hold - award should not be presented to the accounting system. For some reason, the administrator doesn’t want the financial system updated at this time.

Terminated - award period has expired. We have reached the final expiration date and anticipate no more date extensions. Also, we may need to shut the account down for some reason and not allow expenditure to post, but the award is still active.

Closed - everything is done. Period is over, we got all our money, award has been closed out. We don't mark the account closed until we got all our money. This may take weeks, if not months, after the final expiration date. So, our account may terminate on 10/31/2001. On 10/31/2001, the account will be placed in "Terminated" status. This is the key for the financial office to begin the close-out process. Once the accounting office notifies us that the account is financially closed (final report sent, all money received, all unallowable expenses have been removed, indirect cost calculations have been confirmed), they notify us to place the account in "Closed" status.

When entering an award, I found that my selection of "Award Type" would

determine the choice I had for Basis of Payment and Method of Payment. What are the options allowed for Basis of Payment for a given Award Type?

The Award Rules can be set in the Admin -> Award Rules menu. You can set the valid Basis of Payments for each Award type, the report type and frequencies for different report classes, and the frequency basis for each report frequency.

Medusa

How can I see what proposals fund a given award?

The Medusa module in Coeus provides an interface to display and navigate between all the related entities in Coeus. It allows you to display all proposals funding a given award, all awards being funded by a given proposal, the development proposal that created a given Institute proposal, subcontracts associated with an award, and negotiations associated with a proposal. Medusa provides a consistent view of the relationship between all these entities irrespective of where you invoked Medusa from.

Medusa can be invoked from a list window or a detail sheet window for Development Proposal, Institute Proposal, Awards, Subcontracts and Proposal Negotiations. It can be opened either by clicking the menu item "Medusa" under Edit menu in the corresponding window or by using a hot key combination Ctrl+M.

The Medusa window has two sections, a hierarchical tree view on the left showing all the related items, and a summary section on the right. The tree view will display all the related entities. When the Medusa window opens, the item which triggered Medusa will be highlighted. The tree has two View Options, Proposal à Award, and Award à Proposal.

Proposal à Award View

In this View, all Institute Proposals in the tree will be at level 1, and each of the proposals will have all the awards they are funding as their children. If a proposal is funding multiple awards, all of them will be listed as children of the proposal. If an award is funded by multiple proposals, that award will be listed as a child under all those proposals which are funding the award.

Award à Proposal

In this view, all Awards in the tree will be at level 1. All institute proposals funding an award will be listed as children of that award. If an institute proposal is funding two awards, it will be listed as child of both awards.

In both views, a development proposal will be listed as a child of the institute proposal it created. .If an award has any subcontracts associated with it, they will be listed as children of the award.

If an institute proposal listed in the tree has Negotiations associated with it, the proposal item in the tree view will have a Negotiation icon next to it, in addition to the proposal icon.

The user can select any item in the tree and select the Details option under the View menu to open up the sheet window for that item in display mode. The details section of the Medusa window can display either the summary for the selected item or the award hierarchy for the selected award. The user can toggle between the summary and hierarchy or can turn off the summary section. If the summary or hierarchy is turned on, performance will be a bit slow when you scroll through the tree view items.

If the hierarchy option is set and if you change selection from an award to anything else, the summary section will be blank.

How are the links between development proposals, Institute proposals, and Awards maintained in the database?

The link between Development proposals and Institute Proposals is maintained in the osp$proposal_admin_details table. An Institute proposal is created by the system at the time a development proposal is submitted to the sponsor. Generally a new Institute Proposal number will be generated for each Development proposal. However, if the proposal is a revision, a new sequence number will be created for the existing Institute proposal.

The link between awards and Institute proposals is maintained in the osp$award_funding_proposals table. When a new award is created, the user will be prompted to select one pending proposal. If the user did not select a proposal at the time of creating a new award or wants to link more proposals to the award, the Funding Proposals window can be used. This window will display all the rows that exist for the award, i.e rows for all sequence numbers. Rows will be grouped by sequence number of the award. This will give the ability to see all the proposals that ever funded the award. (To remove a proposal's association with an award, the user will have to use the Unlock Proposal functionality available from proposal window.)

We ran into a situation where we had an old award listed in the Award Number field in a proposal. We awarded this proposal, and the two are linked, but there is (and never was) any sign of it on the Medusa screen. See below:

Award 000365-01 shows up here

But no sign of it here!!!

(AnswerJ)

There is a table in Coeus that keeps the funded proposals associated with

an award. Medusa uses this table to draw the relationship between and

award and proposals. However, when you log in a proposal (such as a

revision), you can indicate the MIT Award Number (as in your example) where

you think the revision belongs. This would be true for supplements and

non-competing applications as well.

When the Medusa tree is drawn from the AWARD side of the house, the system

will use the funded proposals for that award and then will recursively walk

through the database for any proposal that is still in PENDING status and

not yet linked to the award from the funded proposal table.

The example you give shows that the proposal is funded. Therefore, the MIT

Award number listed on the screen has no relationship any longer. At the

time you entered the proposal, you indicated that when the award was

received, you would place the funds in MIT award number

000365-001. However, as you can see from the Medusa tree, when you entered

this award into the system, you used MIT award number 002999-001 and did

not place the funds into MIT award number 000365-001.

Medusa will not look at the MIT award number on the proposal screen once

the proposal is funded. The relationship must come from the funded

proposal tables that is linked to the award. This field is only used to

help associate a pending proposal to the award where you anticipate placing

the funds.

Subcontracts

What is meant by “amount obligated” and “ amount released” on the subcontract screens?

‘Obligated amount’ is the amount to be awarded to the subcontract this year.

‘Anticipated amount’ is the total amount that will be awarded to the subcontract.

‘Amount released’ means the amount that has been billed from the subcontractor.

Person maintenance

Is it possible to do person maintenance from the client? I gave myself the "person maintainer" role and I can only display person records, even for persons within my department . I can change the data via SQL or ODBC. Is the point that the person data is coming from a feed so there's no reason to edit it via the client?

Yes, you're right - the osp$person table is fed from our HR system and you can not edit it from the application. If the personnel information is incorrect, we tell HR to fix it on their end.

However, you CAN change person information at the proposal level. (osp$eps_prop_person is the table involved). To do this, first you must specify what columns can be changed. You do this in the Code Table Maintenance window by adding columns in the 'Person Table Editable Columns' table. In order to do this you must have the ' Application Administrator' role. (a red role). So basically only OSP people can do this. Once the editable columns are chosen, the aggregator can change the person details in the proposal.

This way, OSP can control what columns we allow to be edited for a person (at the proposal level).

The 'Person Maintainer' role allows you to change information about a person concerning their bio and educational info. You can always do this for yourself even if you don't have the role.

How is the full_name columnin OSP$PERSON to be formatted?

You can format the full name any way you want. The only thing…. On query screens where you enter the full name, it will need to be entered the same way this field is formatted. This field is simply used to give us quicker access to the full name without joining back to the person table. The database, therefore, is de-normalized in this area. This is intentional on our part. When we refresh our person table from our HR table, a second process is run to ensure the de-normalized data is synchronized back to the person table.

Why are the gender and race columns in OSP$PERSON 30 positions in length?

This field comes from our data warehouse. We don’t store codes in the warehouse but rather we store the translation of the code. We take their values and their lengths.

Why is the length of the degree column in OSP$PERSON different in osp$person and osp$person_degree?

The 11 digit degree is in the osp$person table is data that is fed from our warehouse. The 80 character degree column is in the osp$person_degree table and is a free-form description to describe the degree.

How can we manually modify a person after we add them?

If you are using Coeus 3.7 or earlier, you have to use the Administrative Module (posted on our download site) to do this. If you are using Coeus 3.8, you can add a person through Person Maintenance.

Can someone tell me if there is a way for People to change to what comes

in a feed to a Preferred Name? I did see in the documentation that

there are supposed to be 2 tables: OSP$PERSON_PREFERENCES and

OSP$PERSON_PREF_VARIABLES.

Are these tables related to people being able to change items in their records? We could not find these 2 tables in Coeus 3.8 or 3.7. Do they exist?

This was an enhancement we once did and then later dropped. You will not find these tables in Coeus 3.8. The user preferences functionality became redundant when we implemented custom data for person.

Could MIT provide us with a copy of their HR feed format?

The feed format is the same as osp$person. The feed comes in and we store it in a temporary table. Then we compare by person_id, update columns where we can match ID numbers, insert new rows when we encounter a new ID.

Appointments table

Are the valid values listed in the Coeus data dictionary for appointment_type fixed? Can we change them?

The values in the data dictionary are a complete list of valid values. These values are used in budget calculations to figure out monthly salary of a person. If you modify these values or add new values you need to modify the Budget calculations also. If the budget calculation finds an appointment type which is not in the list of values specified in the data dictionary, the appointment type is considered as a 12 month appointment

What is the definition of the job_code column? Is it possible for multiple appointments for a given individual to have the same job code?

At MIT, the jobcode uniquely identifies a job title, and it is not unique for an appointment. It is possible for an individual to have multiple appointments with the same job code. A professor could have two appointments in two different departments, for example. In both these cases the job code is the same (CB015 for Professor in our case). Or a person can have the same job code in the same department, but have different primary_secondary_indicator values.

How can we manually enter the salary and job code for a person?

You will have to enter this at the proposal stage. You can’t enter it in the Appointments table using the application. . This data is meant to come in as a feed and that is why there is no maintenance screen for it.

Forms and printing

Are you creating all your sponsor forms in Draw? Some Sponsors are very particular in the forms they receive (formatting, logos, etc.). Are these forms being recreated in Draw as well?

We use forms generated by the Draw program as well as forms constructed

using Powerbuilder datawindows. We use the datawindow syntax only if the form is simple and easy to create as a Powerbuilder datawindow and the data in the form can be retrieved from the database with a single retrieve.

A big advantage of the Powerbuilder forms is that we have more control over the formatting (e.g. we can use the autosize height attribute to dynamically enlarge the “variable area”, which we can’t do using the Draw forms).

Are there any "universal" forms that can be used for all sponsors?

At this point we have only two “generic forms” created. We took the NSF cover sheet and budget form and removed all references to NSF. We load the forms under a “generic” sponsor code. The generic sponsor code in the OSP$PARAMETER table must be set to the generic sponsor. If this is done, then the generic form packages will be available for all proposals – regardless of sponsor.

When loading a bitmap file into the Draw program and then trying to place a variable on the form, I get an error saying "Unable to load database variable names. Cannot find file <db_vars.txt>". However, when I am not working with a bitmap file (just creating a form from scratch), things work okay. Why?

This is a problem with the Draw program. When you load the bitmap file, the directory where the bitmap file resides becomes the current directory, and the Draw program can't find the db_vars.txt file. The workaround is to put a copy of the db_vars.txt file in the same directory as the bitmap file, or to put the bitmap file in the Draw directory.

How much budget information do I have to enter in order to produce agency forms?

You need to enter information on the period tabs. Looking at the agency's form, you will see categories for travel, equipment, consultants, etc. This level of detail is captured at the cost element level and, therefore, can be printed on the agency's form. However, when we deal with people, there isn't always enough level of detail to produce the form. At times, there is. For example, many forms have a budget category for GRADUATE STUDENTS. They do not ask for the individual's name. They simply want the number of graduate students and the amount requested. In this case, using the period tabs may be sufficient. On the other hand, for senior personnel, they often require the name of the individual. In these cases, the period tab will *not* have a sufficient level of detail to produce the agency's form – you would need to add a budget person and assign that person to that cost element.

Can you use Draw to create an input form?

You can use DRAW to create forms. The form you create, however, is not used to populate the database. It is the other way around. The database will populate the form for you.

What are the steps you go through to create a new form?

Obtain a hard copy of the form.

Scan form and create a bmp file

Identify the variables needed on the form and add them to the vars.txt file

Using the Draw program, load the bmp file and create the drw file.

Generate a postscript file from the draw program.

Write Oracle functions that will get the values for the new form variables.

Update the OSP$PRINT_VARIABLES table (manually) to include the new variables and functions to be called.

Using the Coeus application, create a new package and load the postscript file.

EDI

Are you sending your grant proposals to NSF via EDI? How about other sponsors?

Yes, we are sending production proposals to NSF using EDI. No other sponsors are receiving EDI transactions from us at the moment, though we are starting to test with ONR.

What basic steps did you take to get this set up? We realize we need to map to the 194 Transaction Set. Can you provide any suggestions o rhelpful hints? Do you recall any specific hurdles you had to overcome?

First you have to map your data to the 194 transaction set. Our master data was

designed with EDI in mind, so we didn’t have much difficulty mapping. But we still had to do a fair amount of work mapping all the budget elements because our application was not storing the budget details in the same breakdown as NSF required. We also had to pick translation software. We are using TLE from Harbinger, which carries with it a significant learning curve.

Do your proposals include rich text (ie. Greek characters, bold, italics,...) in the Summary of Work, Publications, and Other Support? If so, how do you transfer this data?

Yes. The Proposal Summary, bio sketches, etc are stored as PDF files in our application. Currently, NSF still requires these to be uploaded as PDF files. In the future, they will be sent using the 102 transaction set.

We haven't started yet and there is a lot of emphasis in our organization to implement systems using XML. Do you have any comments regarding the use of XML to transfer documents to NSF?

Our recommendation is that if you haven’t been using EDI until now, don’t even start. Wait until E-Grants which is based on XML.

Miscellaneous

How do I set my institution's F and A (Indirect Cost) rate?

Under the menu "Admin".... select "Rate". When the rate screen opens, you

will notice it has two tabs. Click on the tab "Institute Rates" and you

can adjust. You will need to create rates for every "Activity Type". You

can see activity types and adjust them from the code table maintenance.

How does Coeus handle current and pending support?

Coeus lets you produce the current list of pending proposals and active awards for each investigator. Current and Pending support is different for every proposal, so this menu item is available at the proposal level, not at the investigator level. Overlap, for example, will be different for each proposal. One NSF proposal's overlap with another sponsor will be different for a different proposal to NSF. That is, the nature of the proposed projects will dictate the overlap from one proposal to another. Therefore, current and pending support is a "per proposal" type of data gathering. That is why we don't store this at the investigator level.

The tab pages that produce the current and pending support pages have a download function. Coeus allows you to generate the raw data for current and pending. Then, the user can download the information into an Excel spreadsheet. In Excel, the user can add or delete information. Then, if there are specific forms required, the user can merge this information into a template in MS Word and produce the form. In case you are unaware, for many agencies, the form they use is not mandatory. You can use alternate formats as long as the required information is supplied to the agency.

There are two ways to get a list of current and pending support.

On the Departmental menu, select Personnel

Enter the investigator’s last name and other search criteria as needed

Highlight the desired investigator’s name on the results window

On the Edit menu, select Current and Pending Report

On the Maintain Menu, select Proposals

Enter search criteria for the proposal

When the Institutional Proposal list is retrieved, highlight a proposal and display proposal

On the File menu select Current and Pending Support

To download the current and pending reports to the local machine, select Save As from the File menu.

“Excel with headers” is a good way to download the report so that you can customize it - you may add and move around your columns to get the results required for your own needs as well as any agency specific requirements.

How does Coeus handle revisions, continuations and supplements?

An Institute Proposal is created by the system at the time a development proposal is submitted to the sponsor. Generally a new Institute Proposal number will be generated for a new development proposal (in the form YYMMSSSS, where YY = fiscal year, MM = fiscal month, SSSS = sequential number). However, if the proposal is a revision, and the user supplies an original Institute Proposal number, a new sequence number will be created for the existing Institute proposal. (Note: A submitted proposal must always be linked to an Institute roposal. In the case of revisions, if no existing Institute proposal is Provided, a new Institute proposal number will be created.)

We only use sequence numbers for revisions. To us, a supplement must be given a new number since it is a request for additional funds. That is, there is nothing in the original proposal to indicate that a supplement may be forthcoming. Basically, supplements are NEW proposals asking for additional money and, to us, thereby need a new number.

This is true for competing applications as well since the sponsor may

decline to fund a competing proposal

How do the departments feel about using Coeus for development proposals knowing that some agency online systems are not EDI "friendly" (like STScI or Dept of Ed)? This would there likely be the possibility of data double-entry -- once into an agency's online system and once into Coeus?

We too know that there can be a problem with the data in Coeus not matching the data in the agency's system since there will be double keying. Our departments that we are piloting with still like Coeus since it does the budget calculations for them and gives them a central place for biosketch storage and to have the text of the proposal on-line within the database. Basically, our departments have shadow systems now for calculating the budget. Even in Fastlane, they do work in a spreadsheet before adding the data to Fastlane. So, for them, there isn't a lot of re-keying. It's just which system they use originally, their shadow system or Coeus.

We are looking at the business process flow of institutional review/signature. If we "approve" an EPS proposal – whose responsibility is it to create the hard copy proposal? We don’t want to get into the business of creating and duplicating – and even though the proposal info in EPS would be locked once it is approved, there is a worry about what would prevent changes to a proposal once a hard copy version is created?

When our office finishes with a proposal, we sign the proposal and return it to the department. We have no way of knowing if they change the proposal after it leaves our office with our signature. The only thing we have is the copy of the proposal in our paper files. This is true for Coeus. Coeus will contain the institute approved proposal.

Even now in Fastlane EDI submission, the PI and/or department can log into Fastlane after we upload the EDI file and change it. We will be working with NSF over the next several months to close that hole. For now, we consider this a safety net until all the bugs are worked out.

So, in some ways, Coeus is business as usual. What we have in our paper file may or may not be what is in the agency's database. We would consider our file the official institute record and would have to work with the agency to reconcile any discrepancies at the time the award was received.

Why can't Coeus automatically open pdf files if Acrobat reader is already installed on my computer? When trying to open a PDF file through Coeus, why do I get this message:

"In order to view PDF files in Coeus Database, Acrobat Reader should be installed on your machine and full path to the reader application should be specified in Coeus.ini file. The current value in Coeus.ini for Reader Path is invalid. Do you want to select the PDF Reader application now? If you choose yes, Coeus.ini will be updated with the path of the file you select. If you chose No, Coeus will not be able to open pdf. files in the data base."

This is due to the fact that the original place where the Reader was located has probably been moved (or isn't where Coeus expects it to be). You need to use the browse feature and find the Adobe Reader again. The way we launch the Reader is actually a bit different than the way a web page downloads a pdf file and launches the Reader. So, we need to open the Reader first, before Windows knows it is dealing with a pdf file (which it would then open the reader by itself ).

Any way, hunt and peck and find the Adobe Reader again on your computer. There is another way, but it can cause a problem. If you remember, ask me at the next training and I'll show you the other option and give the folks a demo of the differences.

I'm working on altering the yes/no questions to fit our needs and need to add a few questions. Is there a significance to the alpha character of the question ids? I know that the question_id is the table key, but the Gx, Hx, Zx, etc. have me wondering.

There is not a naming convention as such, so when you add new questions, you can really use any question id you want. However, you should not change the existing question ids. ( If you would like to change the description of existing questions to reword them, you can.) But the question ids are hard coded at some places in the application - specifically in the EDI package. The 194 implementation guide requires certain question ids, so you shouldn't change them.

Interface with Financial System (SAP)

Can you provide a summary of the interface? Tables used, data provided to SAP, etc.

Coeus can track every account that is changed. This tracking is enabled by a flag in the osp$parameter table (SAP_FEED_ENABLED). When a new award is created, or when anything is inserted or changed in an award, including money changes in any of the child awards, a row is inserted into the OSP$SAP_FEED_DETAILS table with the award number and sequence_number.

Before writing to OSP$SAP_FEED_DETAILS, we first check to see if that award and sequence number is already in the table with a status of Pending. If so, we don’t write it again. Note that if it is a New Entry, it will be written (different sequence number).

SAP does not require all the data that Coeus maintains for an award, so it is possible for two feed records to look exactly the same. Let’s say we create an award today. This results in a new row in OSP$SAP_FEED_DETAILS. Later on the same day we create a new entry for the award and change the terms and conditions. This creates another row in OSP$SAP_FEED_DETAILS. The only difference in the award is the terms and conditions. When we create the feed, since there are two rows for this award in OSP$SAP_FEED_DETAILS, we will create two feed records. But since Terms and conditions is not part of our feed to SAP , we will have two rows in the feed for this award today that are identical. (This is not a problem for SAP).

When the feed is generated, details from the awards that are pending in OSP$SAP_FEED_DETAILS are retrieved, some data is derived in the feed package, and then rows are inserted into OSP$SAP_FEED. Data from OSP$SAP_FEED is then spooled to a file, which is fed to SAP.

Can other schools use your interface?

Yes, with some redesign, and assuming your financial system can accept batch input. Coeus will flag the awards that need to be fed, inserting them into osp$sap_feed_details. You would need to redesign the osp$sap_feed table to match your flat file output that will be fed to your financial system. Also you would need to write your own feed logic in your stored procedures.

Coeus web

What is the CoeusWeb architecture (prior to the Java conversion)?

The old CoeusWeb piece used Perl scripts to generate the html code and used the DBI component for Perl to access the Oracle database.

We use certificates for security. MIT is its own Certificate Authority (CA). When the user needs to get access to CoeusWeb, they need to use an MIT provided certificate for authentication. To do so, they use their Kerberos user ID, password and another unique identifier and the certificate server will issue them an X.509 certificate. The certificate carries the e-mail user ID of the person. So, we now know the user ID of the person accessing the Coeus web system since we can parse the user ID our of the X.509 certificate since our e-mail ID = Kerberos ID. Coeus does not use Kerberos, but we require MIT users of Coeus to use their Kerberos principal as their username.

CoeusWeb uses the same stored procedures as the native PowerBuilder application since we get the user ID from the certificate.

It is not necessary for you to use certificates to get the authorization component to work. You may, however, have to develop a login type of system if you have no infrastructure in place to obtain the user ID of the person requesting access to the Coeus data/system.

What is the new CoeusWeb architecture ?

Work is underway to convert the CoeusWeb module to a web application using JSPs. This will be released to the community in the near future.

What is Coeus?

What are the main components of Coeus?