GM_XML_OTHER

(SQL View)
 Index Back

SELECT A.BUSINESS_UNIT ,A.PROPOSAL_ID ,A.VERSION_ID ,CASE A.IS_SUB_TO_OTH WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.IS_SUB_TO_OTH WHEN 'Y' THEN A.OTHER_AGENCY ELSE ' ' END ,CASE A.RRCOVERPAGE WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.SUPPLEINFO WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.RRSENKEY WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.BIOSKETCH WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.SUPPORTFILE WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.ADDSENKEY WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.ADDBIO WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.ADDSUPPOR WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.OTHPRJINFO WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.PRJ_SUM_ABS WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.PRJNAFL WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.BIO_REFERE WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.OTH_ATTCH WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.BUDGET_FORM WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.BUDGET_1 WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.BUDGET_2 WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.BUDGET_3 WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.BUDGET_4 WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.BUDGET_5 WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.ADD_KEY_ATT WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.ADD_EQU_ATT WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.BUD_JUST WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.PERS_FORM WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.REV_PRJ_OBJ WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,A.NRI_PROP_TYPE ,A.RES_PRP_PRJ_GRP ,CASE A.POST_DOC_FEL WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.NEW_INV WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.STR_TH WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.STR_TH_GRP WHEN ' ' THEN ' ' ELSE F.XLATLONGNAME END ,A.PROG_NAME ,A.PROG_CODE ,CASE A.FED_ACT_TYPE WHEN ' ' THEN ' ' ELSE D.XLATLONGNAME END ,CASE A.FED_ACT_STATUS WHEN ' ' THEN ' ' ELSE E.XLATLONGNAME END ,A.CHANGE_YEAR ,A.CHANGE_QT ,A.LAST_REPORT , CASE A.CHECKSIGNDATE1 WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END , CASE A.CHECKSIGNDATE2 WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END , CASE A.CHECKSIGNDATE3 WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END , A.CIVILRIGHTASS , CASE A.CIVILRIGHTASS WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,A.CIVIL_ASS_DT , A.HANDI_ASS ,CASE A.HANDI_ASS WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,A.HANDI_ASS_DT , A.SEX_ASS , CASE A.SEX_ASS WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,A.SEX_ASS_DT , A.AGE_ASS , CASE A.AGE_ASS WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,A.AGE_ASS_DT , CASE A.HUM_SUB_CERT WHEN 'Y' THEN 'Y: Yes' WHEN 'N' THEN 'N: No' ELSE 'NA: Not Applicable' END , CASE A.PHS_IMPACT WHEN 'Y' THEN 'Y: Yes' WHEN 'N' THEN 'N: No' ELSE 'NA: Not Applicable' END , CASE A.INTER_GOV_REV WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END , CASE A.PROJ_IDENTIFY WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END , CASE A.BIO_SKETCH WHEN 'Y' THEN 'Y: Yes' WHEN 'N' THEN 'N: No' ELSE 'NA: Not Applicable' END , CASE A.SF424AC WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END , CASE A.MONTH_12_DTL WHEN 'Y' THEN 'Y: Yes' WHEN 'N' THEN 'N: No' ELSE 'NA: Not Applicable' END , CASE A.PROJ_DTL WHEN 'Y' THEN 'Y: Yes' WHEN 'N' THEN 'N: No' ELSE 'NA: Not Applicable' END , CASE A.ONLY_ADD_FIN WHEN 'Y' THEN 'Y: Yes' WHEN 'N' THEN 'N: No' ELSE 'NA: Not Applicable' END , CASE A.PROGRESS_REP WHEN 'Y' THEN 'Y: Yes' WHEN 'N' THEN 'N: No' ELSE 'NA: Not Applicable' END ,CASE A.CHECK_RRSITE WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END , CASE A.CHECK_RROTHER WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.CHECK_PROJ_SUM WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.CHECK_BIB WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.CHECK_FACILITY WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END , CASE A.CHECK_RR_PERS WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,A.INCLUE_FIRST_PI ,A.INCLUE_LOBB_ACT ,A.INCLUE_EXPLOR_RSH ,A.INCLUE_HIS_PLCE ,A.INCLUE_ACC_RENV ,A.INCLUE_HIG_RESLU ,A.DEVIATION_AUTH ,A.IS_HUM_STM_CEL , CASE A.IS_HUM_STM_CEL WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,A.STEM_CELL_IND , CASE A.STEM_CELL_IND WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,CASE A.NASA_CIVIL_SEV WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END ,A.NASA_FY1 ,A.NASA_FY2 ,A.NASA_FY3 ,A.NASA_FY4 ,A.NASA_FY5 ,A.NASA_FY6 ,A.NASA_FTE1 ,A.NASA_FTE2 ,A.NASA_FTE3 ,A.NASA_FTE4 ,A.NASA_FTE5 ,A.NASA_FTE6 ,A.NSF_DIV_CODE , A.NSF_DIV_NAME , A.GM_PROG_NAME , A.GM_PROG_CODE , CASE A.REP_TYPE_LNG WHEN ' ' THEN ' ' ELSE G.XLATLONGNAME END , A.PRIMARY_REP_ENTY , CASE A.APP_CERT WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END , H.REV_DEC_AWD , H.REV_DEC_DUR , H.REV_INC_AWD , H.REV_INC_DUR , H.REV_OTHER , H.REV_OTHER_SPC , H.PROPOSAL_TYPE , I.XLATLONGNAME , ' ' FROM PS_GM_PROPSL_FORMS A , PS_GM_ACT_TYP_VW D , PS_GM_FED_STATS_VW E , PS_GM_STR_GPVAL_VW F , PS_GM_REP_TYP_VW G , PS_GM_PROPOSAL H , PSXLATITEM I WHERE A.BUSINESS_UNIT = H.BUSINESS_UNIT AND A.PROPOSAL_ID = H.PROPOSAL_ID AND A.VERSION_ID = H.VERSION_ID AND (D.FIELDVALUE = A.FED_ACT_TYPE OR A.FED_ACT_TYPE = ' ') AND (E.FIELDVALUE = A.FED_ACT_STATUS OR A.FED_ACT_STATUS = ' ') AND (F.FIELDVALUE = A.STR_TH_GRP OR A.STR_TH_GRP = ' ') AND (G.FIELDVALUE = A.REP_TYPE_LNG OR A.REP_TYPE_LNG = ' ') AND I.FIELDNAME = 'PROPOSAL_TYPE' AND I.FIELDVALUE = H.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 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 PROPOSAL_ID Character(25) VARCHAR2(25) NOT NULL Proposal ID
3 VERSION_ID Character(7) VARCHAR2(7) NOT NULL Version ID
4 IS_SUB_TO_OTH_D Character(14) VARCHAR2(14) NOT NULL Submit to Other Agency
5 OTHER_AGENCY Character(20) VARCHAR2(20) NOT NULL Other Agency name
6 RRCOVERPAGE_D Character(14) VARCHAR2(14) NOT NULL R&R Cover Page Form
7 SUPPLEINFO_D Character(14) VARCHAR2(14) NOT NULL Supplemental Information Form
8 RRSENKEY_D Character(14) VARCHAR2(14) NOT NULL R&R Senior Key Person Form
9 BIOSKETCH_D Character(14) VARCHAR2(14) NOT NULL Biographical Sketch File
10 SUPPORTFILE_D Character(14) VARCHAR2(14) NOT NULL Current & Pending Support File
11 ADDSENKEY_D Character(14) VARCHAR2(14) NOT NULL Additional Senior Key Person
12 ADDBIO_D Character(14) VARCHAR2(14) NOT NULL Additional Bio. Sketches
13 ADDSUPPOR_D Character(14) VARCHAR2(14) NOT NULL Additional Current &Pend Sup
14 OTHPRJINFO_D Character(14) VARCHAR2(14) NOT NULL RR Other Project Inform File
15 PRJ_SUM_ABS_D Character(14) VARCHAR2(14) NOT NULL Project Summary Abstract File
16 PRJNAFL_D Character(14) VARCHAR2(14) NOT NULL Project Narrative File
17 BIO_REFERE_D Character(14) VARCHAR2(14) NOT NULL Bibliography References File
18 OTH_ATTCH_D Character(14) VARCHAR2(14) NOT NULL Other Attachments
19 BUDGET_FORM_D Character(14) VARCHAR2(14) NOT NULL R&R Budget Form
20 BUDGET_1_D Character(14) VARCHAR2(14) NOT NULL Budget Year 1
21 BUDGET_2_D Character(14) VARCHAR2(14) NOT NULL Budget Year 2
22 BUDGET_3_D Character(14) VARCHAR2(14) NOT NULL Budget Year 2
23 BUDGET_4_D Character(14) VARCHAR2(14) NOT NULL Budget Year 2
24 BUDGET_5_D Character(14) VARCHAR2(14) NOT NULL Budget Year 2
25 ADD_KEY_ATT_D Character(14) VARCHAR2(14) NOT NULL Additional Senior Key Persons
26 ADD_EQU_ATT_D Character(14) VARCHAR2(14) NOT NULL Additional Equipment Attach
27 BUD_JUST_D Character(14) VARCHAR2(14) NOT NULL Budget Justification Form
28 PERS_FORM_D Character(14) VARCHAR2(14) NOT NULL Personal Data Form
29 REV_PRJ_OBJ_D Character(14) VARCHAR2(14) NOT NULL Revised Project Objectives
30 NRI_PROP_TYPE Character(30) VARCHAR2(30) NOT NULL NRI Proposal Type
31 RES_PRP_PRJ_GRP Character(50) VARCHAR2(50) NOT NULL Project Proposal Group
32 POST_DOC_FEL_D Character(14) VARCHAR2(14) NOT NULL Post doctoral Fellowship
33 NEW_INV_D Character(14) VARCHAR2(14) NOT NULL New Investigator
34 STR_TH_D Character(14) VARCHAR2(14) NOT NULL Strengthening
35 STR_GP_VALUE Character(30) VARCHAR2(30) NOT NULL Strengthening Group
36 PROG_NAME Character(60) VARCHAR2(60) NOT NULL *Program Name
37 PROG_CODE Character(5) VARCHAR2(5) NOT NULL Program Code
38 FED_ACT_TYPE_LNG Character(30) VARCHAR2(30) NOT NULL FED LONG DESCR
39 FED_ACT_STAT_LNG Character(30) VARCHAR2(30) NOT NULL FED ACT STATUS
40 CHANGE_YEAR Number(4,0) SMALLINT NOT NULL Year
41 CHANGE_QT Number(1,0) SMALLINT NOT NULL Quarter
42 LAST_REPORT Date(10) DATE Last Report
43 CHECKSIGNDATE1_D Character(14) VARCHAR2(14) NOT NULL Sign Date SF424
44 CHECKSIGNDATE2_D Character(14) VARCHAR2(14) NOT NULL Sign Date PHS5161-1
45 CHECKSIGNDATE3_D Character(14) VARCHAR2(14) NOT NULL Sign Date SF424BD
46 CIVILRIGHTASS Character(1) VARCHAR2(1) NOT NULL Civil Right Assurance
47 CIVILRIGHTASS_D Character(14) VARCHAR2(14) NOT NULL Civil Right Assurance
48 CIVIL_ASS_DT Date(10) DATE Civil Right Assurance Sign
49 HANDI_ASS Character(1) VARCHAR2(1) NOT NULL Handicapped Assurance
50 HANDI_ASS_D Character(14) VARCHAR2(14) NOT NULL Handicapped Assurance
51 HANDI_ASS_DT Date(10) DATE Handicapped Assurance Sign
52 SEX_ASS Character(1) VARCHAR2(1) NOT NULL Sex Assurance
53 SEX_ASS_D Character(14) VARCHAR2(14) NOT NULL Sex Assurance
54 SEX_ASS_DT Date(10) DATE Sex Assurance Sign Date
55 AGE_ASS Character(1) VARCHAR2(1) NOT NULL Age Assurance
56 AGE_ASS_D Character(14) VARCHAR2(14) NOT NULL Age Assurance
57 AGE_ASS_DT Date(10) DATE Age Assurance Sign Date
58 HUM_SUB_CERT_D Character(18) VARCHAR2(18) NOT NULL Human Subject Certification
A=NOT APPLICABLE
N=NO
Y=YES
59 PHS_IMPACT_D Character(18) VARCHAR2(18) NOT NULL PHS Impact Statement
A=NOT APPLICABLE
N=NO
Y=YES
60 INTER_GOV_REV_D Character(14) VARCHAR2(14) NOT NULL Intergrovern mental Review
61 PROJ_IDENTIFY_D Character(14) VARCHAR2(14) NOT NULL Project Identify
62 BIO_SKETCH_D Character(18) VARCHAR2(18) NOT NULL Biographical Sketch
A=NOT APPLICABLE
N=NO
Y=YES
63 SF424AC_D Character(14) VARCHAR2(14) NOT NULL SF424AC
64 MONTH_12_DTL_D Character(18) VARCHAR2(18) NOT NULL 12 Month Detailed Budget
A=NOT APPLICABLE
N=NO
Y=YES
65 PROJ_DTL_D Character(18) VARCHAR2(18) NOT NULL Has Detail Project Budget
A=NOT APPLICABLE
N=NO
Y=YES
66 ONLY_ADD_FIN_D Character(18) VARCHAR2(18) NOT NULL Address Only Additional Funds
A=NOT APPLICABLE
N=NO
Y=YES
67 PROGRESS_REP_D Character(18) VARCHAR2(18) NOT NULL Progress Report
A=NOT APPLICABLE
N=NO
Y=YES
68 CHECK_RRSITE_D Character(14) VARCHAR2(14) NOT NULL Check RR Site
69 CHECK_RROTHER_D Character(14) VARCHAR2(14) NOT NULL Check RR Other Info
70 CHECK_PROJ_SUM_D Character(14) VARCHAR2(14) NOT NULL Check Project Summary
71 CHECK_BIB_D Character(14) VARCHAR2(14) NOT NULL Check Biblio
72 CHECK_FACILITY_D Character(14) VARCHAR2(14) NOT NULL Check Facilities
73 CHECK_RR_PERS_D Character(18) VARCHAR2(18) NOT NULL Check RR Personal Data
74 INCLUE_FIRST_PI Character(1) VARCHAR2(1) NOT NULL Beginning Investigator
75 INCLUE_LOBB_ACT Character(1) VARCHAR2(1) NOT NULL Disclosure of Lobbying
76 INCLUE_EXPLOR_RSH Character(1) VARCHAR2(1) NOT NULL Is Exploratory Research
77 INCLUE_HIS_PLCE Character(1) VARCHAR2(1) NOT NULL Is Historic Places
78 INCLUE_ACC_RENV Character(1) VARCHAR2(1) NOT NULL Is Accomplishment Renewal
79 INCLUE_HIG_RESLU Character(1) VARCHAR2(1) NOT NULL Is High Resolution Graphics
80 DEVIATION_AUTH Long Character(4000) CLOB Deviation Authorization
81 IS_HUM_STM_CEL Character(1) VARCHAR2(1) NOT NULL Human Stem Cells
82 IS_HUM_STM_CEL_D Character(14) VARCHAR2(14) NOT NULL Human Stem Cells
83 STEM_CELL_IND Character(1) VARCHAR2(1) NOT NULL Stem Cell
84 STEM_CELL_IND_D Character(14) VARCHAR2(14) NOT NULL Stem Cell
85 INTER_PART_DESC Character(14) VARCHAR2(14) NOT NULL International Participation
86 NASA_FY1 Number(4,0) SMALLINT NOT NULL Fiscal Year1
87 NASA_FY2 Number(4,0) SMALLINT NOT NULL Fiscal Year2
88 NASA_FY3 Number(4,0) SMALLINT NOT NULL Fiscal Year3
89 NASA_FY4 Number(4,0) SMALLINT NOT NULL Fiscal Year4
90 NASA_FY5 Number(4,0) SMALLINT NOT NULL Fiscal Year5
91 NASA_FY6 Number(4,0) SMALLINT NOT NULL Fiscal Year6
92 NASA_FTE1 Number(8,2) DECIMAL(7,2) NOT NULL Year 1
93 NASA_FTE2 Number(8,2) DECIMAL(7,2) NOT NULL Year 2
94 NASA_FTE3 Number(8,2) DECIMAL(7,2) NOT NULL Year 3
95 NASA_FTE4 Number(8,2) DECIMAL(7,2) NOT NULL Year 4
96 NASA_FTE5 Number(8,2) DECIMAL(7,2) NOT NULL Year 5
97 NASA_FTE6 Number(8,2) DECIMAL(7,2) NOT NULL Year 6
98 NSF_DIV_CODE Character(8) VARCHAR2(8) NOT NULL NSF Division Code
99 NSF_DIV_NAME Character(30) VARCHAR2(30) NOT NULL NSF Division Name
100 GM_PROG_NAME Character(30) VARCHAR2(30) NOT NULL Program Name
101 GM_PROG_CODE Character(4) VARCHAR2(4) NOT NULL Program Code ChartField
102 REP_TYP_LNG_DESCR Character(30) VARCHAR2(30) NOT NULL REP TYPE DESCR
103 PRIMARY_FLAG Character(1) VARCHAR2(1) NOT NULL Primary
N=No
Y=Yes
104 APP_CERT_D Character(14) VARCHAR2(14) NOT NULL Applicant Certifies
105 REV_DEC_AWD Character(1) VARCHAR2(1) NOT NULL Decrease Award
106 REV_DEC_DUR Character(1) VARCHAR2(1) NOT NULL Decrease Duration
107 REV_INC_AWD Character(1) VARCHAR2(1) NOT NULL Increase Award
108 REV_INC_DUR Character(1) VARCHAR2(1) NOT NULL Increase Duration
109 REV_OTHER Character(1) VARCHAR2(1) NOT NULL Other(Specify)
110 REV_OTHER_SPC Character(45) VARCHAR2(45) NOT NULL Other(Specify)
111 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
112 PROPOSAL_TYPE_D Character(30) VARCHAR2(30) NOT NULL Description
113 REV_VALUE Character(2) VARCHAR2(2) NOT NULL Revision