Reattaching a Proposal to an Award

"I cannot edit award nnn because it is missing the institutional proposal. Can you reattach it?"

Looking up the IDs:

select award_id, award_number, award_sequence_status from award where award_number='xxx';

select proposal_id, proposal_number, proposal_sequence_status from proposal where proposal_number='xxx';

Inserting the proposal link:

insert into award_funding_proposals values (SEQUENCE_AWARD_ID.nextval, <awardId>, <proposalId>, sysdate, 'admin', 1, 'Y', sys_guid());

Un-attach an Existing Proposal from an Award and Attach a New One

Find the award id:

    select title, award_id, award_number, award_sequence_status, sequence_number from award where award_number = 'xxx'

Find prop id and sequence:

    select status_code, proposal_sequence_status, proposal_id, proposal_number, sequence_number, title, update_timestamp, update_user from proposal where proposal_number = 'xxx' and sequence_number = xxx

Find the relevant row(s) in the award_funding_proposals table:

    select * from award_funding_proposals where award_id = 'xxx'

... make sure prop ids look good

Delete these rows:

    delete from award_funding_proposals where award_id = 'xxx'

Reset the proposal you're un-linking back to pending. Use sequence_number found above:

    update PROPOSAL set status_code = 1 where proposal_number = 'xxx' and sequence_number = xxx

For the proposal you're linking, set it's status to funded:

    update PROPOSAL set status_code = 2 where proposal_number = 'xxx' and sequence_number = xxx

Finally, run the following insert to attach the new proposal into the award_funding_proposals table. You'll need to do this or else the user will get an error when trying to edit the award:

insert into award_funding_proposals values (SEQUENCE_AWARD_ID.nextval, <awardId>, <proposalId>, sysdate, 'admin', 1, 'Y', sys_guid());

Adding new departments to KC

If a new department is detected in the LDAP person feed, an email will be sent that looks like the following:

A new unit number C156001 was detected in the LDAP person feed. This unit should be investigated and added to KC's unit list if necessary.

To add the new department to KC, you will need its department ID (specified in the email as the unit number), the department name, and the department's school and campus.

Finding the department name

Log into SQLPlus on the KC database server and execute the following query, substituting the new unit number for the 'C156001' value:

    SQL> select user_id from kc_ldap_master_temp where home_unit='C156001';

    USER_ID
    --------------------------------------------------
    nsheha01

Copy the user ID that's return from the query (any one will do if there are multiple. Then go to http://whitepages.tufts.edu, paste the user ID into the search box and click go. This should return the details for the person who resides in the new department. The department name will be listed here.

Finding the school and campus information

Determining the school and campus that a department resides in is not an exact science. There are four sources of information that can be used to determine this information:

The Tufts White Pages at http://whitepages.tufts.edu
- Clicking the 'by Department' link under 'Faculty/Staff' will display a list of the Tufts schools, which can be browsed to find the department in question. This will tell you what school the department is in, but not necessarily what campus it is on; most schools are associated with a single campus (e.g. the School of Engineering is on the Medford campus) but some, such as Central Administration, are split across multiple campuses.
The Tufts web site and/or Google
- Searching on www.tufts.edu or on Google for the department name should turn up a home page for the department. This will often list the school/campus info, or at least have a mailing address that may be helpful.
The KC Unit Hierarchy screen
- This can be found on the KC Maintenance page under 'Miscellaneous'. It lists all of the departments already in KC in a tree structure, organized by campus.
- The first letter of the department ID usually denotes where in the tree the new department should go. (E.g., if the ID starts with an 'A' it is probably associated with the School of Arts and Sciences (A000000) or the School of Engineering (A400001).)
The Peoplesoft Department ID report at \\titan\report$\COA_REPORTS\DEPTID.xls
- This is a list of all of the department IDs in use by the Peoplesoft financial system. Comparing the department in question with other similar departments may be useful in determining where to place it.
- Keep in mind that there are many departments in the Peoplesoft list that are not present in KC.

Determine where to place the new department in the KC unit hierarchy

Once you've determined the campus and school for the new department, examine the KC unit hierarchy (linked on the Maintenance page) and find the location where it should be placed. In the C156001 example above, the department turned out to be part of Central Administration on the Medford campus, so it should be placed under the 'Central - Medford' node.

Make a note of the department ID for the 'parent' of the new department.

Insert the new department into the database

Construct a SQL statement that looks like the following one, substituting the new department ID for 'C156001', the new department name for 'Fares Ctr...' and the parent department's ID for 'C000000'.

insert into UNIT (unit_number, unit_name, parent_unit_number, active_flag, organization_id, update_timestamp, update_user, ver_nbr, obj_id) values ('C156001', 'Fares Ctr for East Med Studies', 'C000000', 'N', null, sysdate, 'admin', 1, sys_guid());

The fourth value (called 'active_flag', with a value of 'N' in the example above) determines whether or not the department will show up by default in a department search. If you know that the new department will be receiving grants, change the 'N' value to 'Y', otherwise leave it as-is for now.

Run the SQL statement in SQLPlus on the database server and verify that one new row was added. After committing the changes, you can use the Unit Hierarchy screen in KC to verify that the new department is displayed correctly.

Department updates

If a person changes departments, this will be updated in LDAP. Our person data comes straight from LDAP, so once the department is updated there it will automatically propagate to KC during the next nightly update. We don’t have the ability to change the person info directly in KC. (Or rather, we could, but it would just get overwritten by the LDAP data.)

Error: “Found duplicate connection descriptor using PBKey org.apache.ojb.broker.PBKey”

This error may happen when restarting the server. Fix this by deleting the session.ser file that lives tomcat/work/Catalina/localhost/kc-test directory (or just delete the entire directory and restart). Here are the details: https://jira.kuali.org/browse/KULRICE-3897

Updating the project start date

"Can you change the project start and end date on award #nnnn to 12/15/14 - 12/14/19? I cannot edit the awards side without finalizing Time and Money and I cannot finalize Time and Money without fixing the project start date. So needless to say it is a never ending cycle."

The project start date is stored in the award_effective_date in award:

select award_effective_date from award where award_number = 'nnn'
update award set award_effective_date = to_date('12/15/2014','mm/dd/yyyy') where award_number = 'nnn'

Updating a sponsor name

Update the following tables if a sponsor name must be changed:

update sponsor set sponsor_name = 'DePuy Synthes' where sponsor_name like '%Depuy%';
update rolodex set organization = 'DePuy Synthes' where organization like '%Depuy%';
update organization set organization_name = 'DePuy Synthes' where organization_name like '%Depuy%';
update sponsor_hierarchy set level3 = 'DePuy Synthes' where level3 like '%Depuy%';

Unit Already Exists for person.

A user is trying to edit an award, but gets the error: "Unit Nutrition & Cancer Lab-HNRCA (H111001) already exists for [Person First Last Name]"

When I looked at the contact tab on the award it listed the unit twice and the AWARD_PERSON_UNITS table showed the unit listed twice for the person.

Steps to resolve:

Find the award ID:

select title, award_id, award_number, award_sequence_status, sequence_number from award where award_number = 'xxx';

Find the award person ID (using the award ID):

select award_number, award_id, award_person_id, full_name from award_persons where award_id = xxx;

Find the person in the award_person_units table (using the award person ID):

select * from award_person_units where award_person_id = xxx;

You'll probably see two rows. Delete one. I've been deleting the row with N for the LEAD_UNIT_FLAG:

delete from award_person_units where award_person_id = xxx and lead_unit_flag = 'N';

Error trying to edit an award: there is a non-final T&M document.

Tried having user finalize the T&M before editing the award, but she got stack trace "no blanket approval permission". This is because it was last edited by another user, however, when I login as this other user, it says it is now locked by user 1.

Go to System Admin->Pessimistic Lock-> clear lock held by user 1.

Then go to Inbox of user 2 and finalize the T&M document.

User should now be able to edit.

User tries to view Time & Money and the award goes into edit mode.

Check to see if there is a saved (but not finalized) version of the Time and Money document in the system. Find who initiated the edit and have that user finalize it.

Need to update a submitted proposal (in PD) to change its lead unit.

Run the following SQL statements to update the proposal, substituting the proposal number and lead units as appropriate. In this example, 'M333001' is the new lead unit, and 'M530001' is the old lead unit.

update eps_proposal set owned_by_unit='M333001' where proposal_number='105';
update eps_prop_person_units set unit_number='M333001' where proposal_number='105' and unit_number='M530001';
update eps_prop_unit_credit_split set unit_number='M333001' where proposal_number='105' and unit_number='M530001';

If the proposal has been submitted, it likely has a corresponding Institutional Proposal that may also need to be updated, though the simplest way to do this is by updating it manually in KC.

Cannot create a new Time & Money document at this time as a pending award exists in this award hierarchy.

This error indicates that the system thinks one of the awards in the hierarchy is in PENDING status. Check each award to determine which one is in pending status, and either finalize it or ask the user who initiated the edit to finalize (or cancel) their changes.

It is also possible that this error appears when all awards in the hierarchy appear to be in a 'Final' state. This can happen because an older version of the award got stuck in 'PENDING' status. To locate these, run the following SQL query:

SQL> select award_number, award_id, award_sequence_status, update_timestamp from award where award_number like '100722%';
AWARD_NUMBER   AWARD_ID AWARD_SEQU UPDATE_TI
------------ ---------- ---------- ---------
100722-00001     112173 ARCHIVED   01-JUL-15
100722-00001     130502 ACTIVE     16-FEB-16
100722-00001     111797 PENDING    27-JUN-15
100722-00002     138925 ACTIVE     12-MAY-16

In the example above, there is a 'PENDING' award version that is *older* than the most recent ACTIVE version, judging by both the update timestamps and the award IDs. This can be fixed by running the following update statement to manually move the old version into ARCHIVED status:

update award set award_sequence_status='ARCHIVED' where award_id=111797;

Once this is done, KC will no longer see a PENDING award version and should allow the T&M edit to proceed.

Update Signing Official/Applicant Organization on existing records

Typically in Proposal Development, the 'Applicant Organization' entry (which contains the signing official's information) is only updated once when the proposal is initially created. If there is a change in signing official info that requires updating in-progress proposals, this can be done with the following SQL statements:

select proposal_number, location_name, organization_id from eps_prop_sites where location_type_code=1 and proposal_number in (149,142,152);

update eps_prop_sites set organization_id='000011' where location_type_code=1 and proposal_number in (149,142,152);

The select statement will show the organization IDs for the applicant orgs on the selected proposals, and can be run before and after the update to verify the change. The update statement sets the new applicant organization ID on the specified proposals.

The change will take effect immediately, although it may require closing and re-opening a proposals to see the new value. Also, if a proposal that was already open when the applicant org was updated is then saved, it may overwrite the changed org data with the original and require a second update.

Browser not supported