GM_XML_PROP_398(SQL View) |
Index Back |
|---|
| SELECT A.OPRID , A.RUN_CNTL_ID , B.BUSINESS_UNIT , A.PROPOSAL_ID , A.VERSION_ID , CASE B.PI_CHANGE_IND WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END , B.PROPOSAL_TYPE , I.XLATLONGNAME , B.CONTRACT_NUM , CASE B.INST_CHANGE_IND WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END , B.PREV_INSTID , CASE F.IS_INV_PATEN WHEN 'Y' THEN 'Y: Yes' WHEN 'N' THEN'N: No' ELSE ' ' END , CASE F.IS_PRE_RPT WHEN 'Y' THEN 'Y: Yes' WHEN 'N' THEN'N: No' ELSE ' ' END , CASE F.IS_PRO_INCOM WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END , F.IS_PRO_INCOM , ' ' FROM PS_GM_XML_PROP_TBL A , PS_GM_EC_HDR B , PS_GM_PROPSL_FORMS F , PSXLATITEM I WHERE A.OPRID = B.OPRID AND A.RUN_CNTL_ID = B.RUN_CNTL_ID AND B.BUSINESS_UNIT = F.BUSINESS_UNIT AND B.PROPOSAL_ID = F.PROPOSAL_ID AND B.VERSION_ID = F.VERSION_ID AND I.FIELDNAME = 'PROPOSAL_TYPE' AND I.FIELDVALUE = B.PROPOSAL_TYPE AND I.EFFDT = ( SELECT MAX(I2.EFFDT) FROM PSXLATITEM I2 WHERE I.FIELDNAME = I2.FIELDNAME AND I.FIELDVALUE = I2.FIELDVALUE AND I2.EFFDT <= %CurrentDateIn) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  OPRID |
Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 2 | RUN_CNTL_ID |
Character(30) | VARCHAR2(30) NOT NULL | Run Control ID |
| 3 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 4 | PROPOSAL_ID | Character(25) | VARCHAR2(25) NOT NULL | Proposal ID |
| 5 | VERSION_ID | Character(7) | VARCHAR2(7) NOT NULL | Version ID |
| 6 | Y_COLL_DESC | Character(18) | VARCHAR2(18) NOT NULL | Collaborator |
| 7 | PROPOSAL_TYPE | Character(3) | VARCHAR2(3) NOT NULL |
PROPOSAL_TYPE
AMD=Amendment CNT=Continuation CON=Competing Continuation INT=Internal MOD=Modification NEW=New NOC=No-Cost Extension NON=Non-Competing Continuation NRS=NRSA PRE=Pre-Proposal RCA=Research Career Award REN=Renewal RES=Resubmission REV=Revision SB1=SBIR I SB2=SBIR II ST1=STTR I ST2=STTR II SUP=Supplemental Default Value: NEW |
| 8 | PROPOSAL_TYPE_D | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 9 | PREV_CONTRACT_NUM | Character(25) | VARCHAR2(25) NOT NULL | Previous Project/Grant |
| 10 | Y_COPI_DESC | Character(18) | VARCHAR2(18) NOT NULL | CO-PI |
| 11 | PREV_INSTID | Character(5) | VARCHAR2(5) NOT NULL | Previous Institution |
| 12 | Y_EQU_DESC | Character(14) | VARCHAR2(14) NOT NULL | Equipment |
| 13 | Y_EX_DESC | Character(250) | VARCHAR2(250) NOT NULL | Explanation |
| 14 | Y_FAC_DESC | Character(14) | VARCHAR2(14) NOT NULL | Facility |
| 15 | IS_PRO_INCOM | Character(1) | VARCHAR2(1) NOT NULL | Program Income |
| 16 | PRE_ORG_LEG_NAME | Character(60) | VARCHAR2(60) NOT NULL | Organization Legal Name |