RAS Database Updates

Update PD Proposal Status

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

Fix Fiscal Year on PD Unrecovered F&A

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

Remove Previous Grants.gov Tracking ID from PD

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 Award Stuck in 'Pending' State (Edit button doesn't appear)

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

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());