Sponsors,
Organization, Subcontracts and Rolodex
Interface with
Financial System (SAP)
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.
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).
· 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.
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.
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.
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
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.
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.
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.
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.
Currently we are using Oracle 8.1.7.4.
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.
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.
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
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.
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)
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.
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.
See the Coeus 12 step document (http://coeus.mit.edu/)
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.
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.
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/
|
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. |