Proposal Development (PD)

Update PD Proposal Status

Request: "Please update proposal number 1234 to the 'Approved & Submitted' status."

Example Ticket: INC0712970

1. View proposal status values

select * from proposal_state;

2. View status of selected proposal

select proposal_number, status_code from eps_proposal where proposal_number='1234';

3. Update status of selected proposal

update eps_proposal set status_code='6' where proposal_number='1234';

Remove Previous Grants.gov Tracking ID from PD

Request: Cannot remove previous Grants.gov tracking ID from proposal

Example Ticket: INC0712387

1. View Grants.gov info for selected proposal

select * from s2s_app_submission where proposal_number='1234';

2. Remove value for GG Tracking ID from selected proposal

update s2s_app_submission set GG_TRACKING_ID=null where proposal_number='1234';

Fix Missing/Invalid Fiscal Year on PD Unrecovered F&A

Request: Proposal encounters an error during routing referencing an invalid fiscal year. ("Fiscal year must be between X and Y")

Example Ticket: INC0729118

1. Find budget ID for selected proposal

select proposal_number, budget_id, final_version_flag, status_code from eps_proposal_budget_ext where proposal_number='1234'; -- Status code of 1 = Complete, 2 = Incomplete

2. View Unrecovered F&A lines for selected budget ID

select * from eps_prop_idc_rate where budget_id='2345';

3. Update Fiscal Year value for selected UF&A line

 update eps_prop_idc_rate set fiscal_year='2020' where budget_id='2345' and unrecovered_fna_id='3';

Institutional Proposal (IP)

Update IP Status

Request: "Please update the status of IP ####### to 'Not Funded'."

1. View IP status values

select * from proposal_status;

2. View status of selected proposal

select proposal_number, proposal_id, proposal_sequence_status, status_code from proposal where proposal_sequence_status='ACTIVE' and proposal_number='00173838';

3. Update status of selected proposal

update proposal set status_code='5' where proposal_sequence_status='ACTIVE' and proposal_number='00173838';

Award

Fix Award Stuck in 'Pending' State (Edit button doesn't appear)

Request: "Edit button does not appear for award 100000-00001."

Example Ticket: INC0728472

1. View versions and statuses for selected award

select award_number, sequence_number, award_id, award_sequence_status from award where award_number='100000-00001' order by sequence_number;

2. If no ACTIVE version, set the PENDING version to ACTIVE

update award set award_sequence_status='ACTIVE' where award_sequence_status='PENDING' and award_id='111111';

Maintenance Tables

Add New State to State Table

Request: "Please add the states of (country) to the RAS state table."

Example Ticket: INC0718617

1. Insert new row for state (repeat for each state/province)

insert into krlc_st_t values ('SS', 'CC', sys_guid(), 1, 'State Name', 'Y'); 
-- Replace SS with two-digit state/province abbreviation; these are used in S2S submissions so they should match the official designations.
-- Replace CC with the two-digit country abbreviation (found in the Country maintenance table)
-- Replace State Name with the name of the state/province; this is what will display in the drop-down. Be careful about copy-pasting special characters as they may not show up correctly.

Remove Inactive Departments from User

Request: Person/User record cannot be edited because of inactive departments in their permissions.

1. Run stored procedure on user to clean up permissions for inactive departments

exec remove_inactive_unit_perms('efitzp01'); -- Replace with user's UTLN

Grant Admin permissions to a user (full access)

1. Assign admin permission to the given UTLN

exec give_user_admin_permissions('efitzp01'); -- Replace with user's UTLN

PeopleSoft Integration

Add New PeopleSoft Grant Prefix

1. View current grant prefixes

select * from ps_grant_prefix order by grant_prefix;

2. Insert a new grant prefix

insert into ps_grant_prefix values ('ABC', sysdate, 'admin', 1, sys_guid());

Add/Update PeopleSoft Accountant Code assignments

1. View current accountant code assignments

select accountant_code, user_id from ps_accountant_codes order by to_number(accountant_code);

2a. Assign a new user to an existing accountant code

update ps_accountant_codes set user_id='efitzp01' where accountant_code='99';

2b. Create a new accountant code for the given user

insert into ps_accountant_codes values ('99', 'efitzp01', sysdate, 'admin', 1, sys_guid());

2c. Remove an existing accountant code

delete from ps_accountant_codes where accountant_code='99';

Add Federal Sponsor Mapping Info

1. Insert new mapping row with the given data

insert into ps_sponsor_mappings(sponsor_code, fed_agency, reporting_agency, display_acronym, mapping_level, update_timestamp, update_user, ver_nbr, obj_id) values
	('000000', 'DOD', 'DOD', 'DOD>USMA>', 1, sysdate, 'admin', 1, sys_guid());
	-- First parameter is the six-digit sponsor code
    -- Second parameter is the Federal Agency code
    -- Third parameter is the Reporting Agency code
    -- Fourth parameter is the Display Acronym
    -- Other parameters can be left with the provided values

RAS Database Updates