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';

Update Opportunity Title

Request: The opportunity title on an application contains an unsupported character, resulting in an error which states "Must be an alphanumeric character or punctuation".

1. Update the proposal AND s2s tables with the new title, replacing the invalid character with the correct one

update eps_proposal set program_announcement_title='DoD Peer Reviewed Alzheimer''s, Research Partnership Award' where proposal_number='5997';
update s2s_opportunity set opportunity_title='DoD Peer Reviewed Alzheimer''s, Research Partnership Award' where proposal_number='5997';

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';

Add New Delegate Central Admin

Request: Add user with UTLN 'mgoldb10' as a Delegate Central Administrator for all departments.

1. Run procedure to create unit_administrator rows for all units using the given UTLN

exec add_delegate_central_admin('mgoldb10');

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