# Presentation: 745443

## IRDB “Redesign”

- Materialized Views for TA and PA
- March 1, 2002

## Agenda

- Review redesign goals
- Present proposed new views
- PV_TRAINEE_ADMIN_MV
- PV_PROGRAM_ADMIN_MV
- Discuss outstanding issues

## IRDB Redesign Goal

- Make the IRDB the eRA Decision Support System (DSS)
- Give people the information they need
- Make the information useful
- Improve performance
- Minimize the impact of change on the IC

## IRDB Redesign

- Proposed TA Materialized View

## PV_TRAINEE_ADMIN_MV

- Includes all type “T” and “F” records
- NRSA applications and appointments
- Fellowship awards
- Designed primarily for GM and PA staff

## PV_TRAINEE_ADMIN_MV cont’d.

- Key indicators
- Appt. status code and descrip. (6)
- Appt. type code (6)
- DSF code (6)
- FOT code and descrip. (7)
- Payback status code (7)
- Termination status code (9)
- WIP status code (10)

- Key dates
- Activation date, type 1 and 2 “Fs” (6)
- Project start and end dates (8)
- Waiver approval date (9)
- TA start and end dates (9)
- Termination date (9)

## PV_TRAINEE_ADMIN_MV cont’d.

- Funding data
- Stipend level (9)
- Stipend amount (9)
- Total amount all trainees (9)
- Stipend amount (9)
- Travel amount (10)
- Tuition amount  (10)

- Person Data
- Full name
- Person ID (8)
- Person profile ID (8)
- Person invol. ID (8)
- Person type code (8)
- Role type code (8)
- Stipend level degree code (8)

## PV_TRAINEE_ADMIN_MV cont’d.

- Admin data
- Appl. Type code (6)
- Activity code (6)
- Admin. IC (6)
- Serial Number (8)
- Suffix code (9)
- Source_code_dc (6)
- External org ID (6)
- FY (7)

- Admin data cont’d.
- Major activity code (7)
- Org Name (7)
- Project title (8)
- Support waiver limit code (9)

## IRDB Redesign

- Proposed PA Materialized View

## PV_PROGRAM_ADMIN_MV

- Designed primarily for Program Directors
- Combines elements of
- PV_GRANT_PI_MV
- PV_BUDGET_MV
- PV_INSTITUTION_MV
- PV_ICD_ASSIGNMENTS_MV

## PV_PROGRAM_ADMIN_MV cont’d.

- Key code indicators
- Abstract exists (10)
- Activated fellowship (10)
- Active grant (10)
- AIDS related (10)
- Animal subjects (10)
- Appl. Status (11)
- Child clinical study (12)
- Co-funding indicator (12)
- DSF code (12)
- Expedited review (12)

- Key code indicators
- Foreign application (13)
- FSR accepted (13)
- Gender clinical study (13)
- Human subject (14)
- ICD Disposition (14)
- Initiative type (15)
- IRG code (15)
- IRG flex (15)
- IRG_COMB_DC (15)
- SEP reason (15)

## PV_PROGRAM_ADMIN_MV cont’d.

- Key code indicators
- IRG Recom. (15)
- IRG Round (16)
- Minority clinical study (16)
- New PI (16)
- NGA Exists (16)
- Phase 3 clinical trial (16)
- Priority code (17)
- PCC (17)
- Sci. Classification (18)

- Key code indicators
- SNAP indicator (18)
- Special consideration (18)
- Special disposition (18)
- SRA designator (18)
- SRA Flex (18)
- Smry. stmt. status (19)
- Tracking exception (19)
- Stem cells used (19)

## PV_PROGRAM_ADMIN_MV cont’d.

- Key Flags
- SS Admin note (19)
- SS Biohazard (19)
- SS Foreign (19)
- Full name, email, work phone w/ ext for:
- PI (w/ addr. and fax)
- PO
- GS
- GMO
- SRA

- Key Dates
- NGA Issued (12)
- Budget start/end (12)
- Council meeting (12)
- Fellowship activation notice recorded (12)
- IRG meeting (15)
- Project start/end (17)
- Smry. stmt. released (19)

## PV_PROGRAM_ADMIN_MV cont’d.

- Admin data
- Full grant number (13)
- Former number (13)
- Appl. Type code (12)
- Activity code (10)
- Admin. IC (10)
- Serial Number (18)
- Suffix code (19)
- Source_code_dc (10)
- External org ID

- Admin data cont’d.
- FY (13)
- Org Name (16)
- Business Official name and phone (16)
- Project title (17)
- Priority score (17)
- RFA/PA num (17)

## PV_PROGRAM_ADMIN_MV cont’d.

- Funding data
- Council recommended dollars – current and all future years (12)
- Committed future year amounts (13)
- Direct cost (12)
- Indirect cost (12)
- Total awarded amt. (19)
- Total obligated amt. (19)
- Un-obligated balance (19)

- Other information
- IRG meeting address (15)
- Number of out years (16)
- % applied research (12)
- % basic research (12)
- % develop. research (12)

## PV_PROGRAM_ADMIN_MV cont’d.

- appl_workflow_state (12)
- Pre Submission
- Pending SRG
- Pending Council
- Post Council
- Post Award

- appl_workflow_state_code
- Pre Submission
- Pending SRG
- Pending Council
- Post Council
- Post Award

## Outstanding Issues

- See pages 19 and 20