GM_XML_PROPOSAL

(SQL View)
 Index Back

xml file proposal header infor


SELECT A.OPRID , A.RUN_CNTL_ID , A.PROPOSAL_ID , A.VERSION_ID , A.GM_IS_RFP , A.ADDRESS_CC , A.HUMAN_SUBJ_FLAG , CASE A.HUMAN_SUBJ_FLAG WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END , C.ASSURANCE_NBR , C.EXEMPTION_NBR , A.EXEMPT_FLAG , A.NIH_PHASE_III_FLAG , CASE A.NIH_PHASE_III_FLAG WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END , A.VERTEBRATES_FLAG , C.ASSURANCE_NBR2 , A.IACUC_APPROVAL_DT , B.TITLE56 , B.PROPOSAL_RFP_REF , B.SOLICITATION_TITLE , B.BEGIN_DT , B.END_DT , C.TOTAL_DIRECT_COST , C.TOTAL_BUDGET_AMT , C.TOTAL_DIRECT_COST2 , C.TOTAL_BUDGET_AMT2 , D.DESCR120 , D.NIH_IPF_NBR , A.FEDERAL_ORG , A.STATE_ORG , A.LOCAL_ORG , A.PRIVATE_NONPRO_ORG , A.GEN_PROFIT_ORG , A.SMALL_BUSINESS_ORG , A.WOMAN_OWNED_ORG , A.SOC_ECON_DISAD_ORG , %Substring(A.CONGRESSIONAL_DIST, 1, 6) , A.DUNS_NBR , D.TAXPAYER_ID , A.SUB_PROP_NBR , B.DISCLOSURE_FLAG , C.FULL_REVIEW , C.EXPEDITED_REVIEW , C.APPROVAL_DATE , B.SBIR_FLAG , B.STTR_FLAG , B.PI_CHANGE_IND , B.FOREIGN_APP_COMP , B.PROPOSAL_TYPE , E.FA_AGREEMENT_DT , B.REF_AWD_NUMBER , B.PROPOSAL_ID_SPNSR , CASE WHEN B.SUBMIT_DTTM IS NULL THEN %CurrentDateTimeIn ELSE B.SUBMIT_DTTM END , CASE WHEN B.SUBMIT_DTTM IS NULL THEN %CurrentDateTimeIn ELSE B.SUBMIT_DTTM END , B.CONTRACT_NUM , B.CFDA , 'Y' , C.REV_PND_IACUC , CASE F.IS_PROPRIE_INC WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END , CASE F.ENV_IMPACT WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END , %Substring(F.ENV_IMPACT_EXPL, 1, 55) , CASE F.ENV_IMP_EXEMT WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END , %Substring(F.ENV_IMP_EXEMT_EXPL, 1, 55) , CASE F.OUTSIDE_US_IND WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END , F.OUTSIDE_US_EXP , F.OUTSIDE_US_OPT_EXP , B.OPPORTUNITY_NUM , B.INST_CHANGE_IND , B.PREV_INSTID , B.SBIRSTTRT_PHASE , B.DESCR254 , B.PRE_TRACK_NUM , F.IS_INV_PATEN , F.IS_PRE_RPT , F.IS_PRO_INCOM , F.REV_12372 , F.REV_12372_DT , B.BUSINESS_UNIT , %Substring(G.DESCR254, 1, 200) , H.NAME1 , ' ' , 'Y: Yes' , D.FEDERAL_EIN_GM , I.APP_IDNT , I.ST_APP_IDNT , I.SUBMISSION_TYPE , I.ST_REC_DT , I.CONTRACT_NUM , H.CONT_ALLOW_PCT , H.CONT_NO_ALLW_PCT , H.FED_SHARE_PCT FROM PS_GM_XML_PROP_TBL A , PS_GM_EC_HDR B , PS_GM_EC_MISCEL C , PS_GM_EC_INST D , PS_GM_EC_FA E , PS_GM_PROPSL_FORMS F , PS_GM_PROP_PROJ G , PS_CUSTOMER H , PS_GM_PROPOSAL I WHERE A.OPRID = B.OPRID AND A.RUN_CNTL_ID = B.RUN_CNTL_ID AND A.OPRID = C.OPRID AND A.RUN_CNTL_ID = C.RUN_CNTL_ID AND B.ECTRANSID = D.ECTRANSID AND B.ECQUEUEINSTANCE = D.ECQUEUEINSTANCE AND B.ECTRANSINOUTSW = D.ECTRANSINOUTSW AND B.OPRID = D.OPRID AND B.RUN_CNTL_ID = D.RUN_CNTL_ID AND B.BUSINESS_UNIT = D.BUSINESS_UNIT AND B.PROPOSAL_ID = D.PROPOSAL_ID AND A.OPRID = E.OPRID AND A.RUN_CNTL_ID = E.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 B.BUSINESS_UNIT = G.BUSINESS_UNIT AND B.PROPOSAL_ID = G.PROPOSAL_ID AND B.VERSION_ID = G.VERSION_ID AND G.PRIMARY_FLAG = 'Y' AND D.SETID = H.SETID AND B.CUST_ID = H.CUST_ID AND B.BUSINESS_UNIT = I.BUSINESS_UNIT AND B.PROPOSAL_ID = I.PROPOSAL_ID AND B.VERSION_ID = I.VERSION_ID

# 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 PROPOSAL_ID Character(25) VARCHAR2(25) NOT NULL Proposal ID
4 VERSION_ID Character(7) VARCHAR2(7) NOT NULL Version ID
5 GM_IS_RFP Character(3) VARCHAR2(3) NOT NULL
6 ADDRESS_CC Character(110) VARCHAR2(110) NOT NULL Field to Store CC Address
7 HUMAN_SUBJ_FLAG Character(1) VARCHAR2(1) NOT NULL Human Subjects

Y/N Table Edit

8 HUMAN_SUBJ_DESC Character(14) VARCHAR2(14) NOT NULL Human Subjects
9 ASSURANCE_NBR Character(20) VARCHAR2(20) NOT NULL Assurance Number
10 EXEMPTION_NBR Character(20) VARCHAR2(20) NOT NULL Exemption Number
11 EXEMPT_FLAG Character(1) VARCHAR2(1) NOT NULL Research Exempt
12 NIH_PHASE_III_FLAG Character(1) VARCHAR2(1) NOT NULL Phase III
13 NIH_PHASE_III_DESC Character(14) VARCHAR2(14) NOT NULL Phase III
14 VERTEBRATES_FLAG Character(1) VARCHAR2(1) NOT NULL Vertebrate Animals
15 ASSURANCE_NBR2 Character(20) VARCHAR2(20) NOT NULL Assurance Number
16 IACUC_APPROVAL_DT Date(10) DATE IACUC Approval Date
17 TITLE56 Character(56) VARCHAR2(56) NOT NULL TITLE56
18 PROPOSAL_RFP_REF Character(30) VARCHAR2(30) NOT NULL Solicitation/RFP Number
19 SOLICITATION_TITLE Character(56) VARCHAR2(56) NOT NULL Solicitation Title
20 BEGIN_DT Date(10) DATE Begin Date
21 END_DT Date(10) DATE End Date
22 TOTAL_DIRECT_COST Signed Number(28,3) DECIMAL(26,3) NOT NULL TOTAL_DIRECT_COST
23 TOTAL_BUDGET_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL TOTAL_BUDGET_AMT
24 TOTAL_DIRECT_COST2 Signed Number(12,2) DECIMAL(10,2) NOT NULL Total Direct Budget
25 TOTAL_BUDGET_AMT2 Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Budget
26 DESCR120 Character(120) VARCHAR2(120) NOT NULL Description
27 NIH_IPF_NBR Character(14) VARCHAR2(14) NOT NULL NIH IPF Number
28 FEDERAL_ORG Character(1) VARCHAR2(1) NOT NULL FEDERAL ORGANIZATION
29 STATE_ORG Character(1) VARCHAR2(1) NOT NULL FEDERAL ORGANIZATION
30 LOCAL_ORG Character(1) VARCHAR2(1) NOT NULL FEDERAL ORGANIZATION
31 PRIVATE_NONPRO_ORG Character(1) VARCHAR2(1) NOT NULL FEDERAL ORGANIZATION
32 GEN_PROFIT_ORG Character(1) VARCHAR2(1) NOT NULL FEDERAL ORGANIZATION
33 SMALL_BUSINESS_ORG Character(1) VARCHAR2(1) NOT NULL FEDERAL ORGANIZATION
34 WOMAN_OWNED_ORG Character(1) VARCHAR2(1) NOT NULL FEDERAL ORGANIZATION
35 SOC_ECON_DISAD_ORG Character(1) VARCHAR2(1) NOT NULL FEDERAL ORGANIZATION
36 CONGRESSIONAL_DIST Character(40) VARCHAR2(40) NOT NULL Congressional District
37 DUNS_NBR Character(40) VARCHAR2(40) NOT NULL DUNS
38 TAXPAYER_ID Character(14) VARCHAR2(14) NOT NULL Taxpayer ID
39 SUB_PROP_NBR Character(15) VARCHAR2(15) NOT NULL SUB_PROP_NBR
40 DISCLOSURE_FLAG Character(1) VARCHAR2(1) NOT NULL Disclosure Permission Statemen

Y/N Table Edit

41 FULL_REVIEW Character(1) VARCHAR2(1) NOT NULL Full Review
42 EXPEDITED_REVIEW Character(1) VARCHAR2(1) NOT NULL Expedited Review
43 APPROVAL_DATE Date(10) DATE Approval Date
44 SBIR_FLAG Character(1) VARCHAR2(1) NOT NULL SBIR Fast Track

Y/N Table Edit

45 STTR_FLAG Character(1) VARCHAR2(1) NOT NULL STTR Fast Track

Y/N Table Edit

46 PI_CHANGE_IND Character(1) VARCHAR2(1) NOT NULL PI Change Indicator
47 FOREIGN_APP_COMP Character(1) VARCHAR2(1) NOT NULL Foreign Application/Component
48 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

49 FA_AGREEMENT_DT Date(10) DATE Agreement Date
50 REF_AWD_NUMBER Character(25) VARCHAR2(25) NOT NULL Reference Award Number
51 PROPOSAL_ID_SPNSR Character(30) VARCHAR2(30) NOT NULL Sponsor Proposal ID
52 SUBMIT_DTTM DateTime(26) TIMESTAMP Report Submitted On - DateTime
53 SIGN_DTTM DateTime(26) TIMESTAMP Report Submitted On - DateTime
54 PREV_CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Previous Project/Grant
55 CFDA Character(15) VARCHAR2(15) NOT NULL CFDA
56 REV_PND_IRB Character(1) VARCHAR2(1) NOT NULL IRB Review
57 REV_PND_IACUC Character(1) VARCHAR2(1) NOT NULL IACUCA Review
58 IS_PROPRIE_INC_D Character(14) VARCHAR2(14) NOT NULL Proprietary/Privileged
59 ENV_IMPACT_D Character(14) VARCHAR2(14) NOT NULL Env Impact
60 ENV_IMPACT_EXPL Character(55) VARCHAR2(55) NOT NULL Env Impact Explanation
61 ENV_IMP_EXEMT_D Character(14) VARCHAR2(14) NOT NULL Exempt
62 ENV_IMP_EXEMT_EXPL Character(55) VARCHAR2(55) NOT NULL Exempt
63 OUTSIDE_US_IND_D Character(14) VARCHAR2(14) NOT NULL Outside US Indicator
64 OUTSIDE_US_EXP Character(55) VARCHAR2(55) NOT NULL Outside US Explanation
65 OUTSIDE_US_OPT_EXP Character(55) VARCHAR2(55) NOT NULL Outside US Explanation
66 OPPORTUNITY_NUM Character(100) VARCHAR2(100) NOT NULL Opportunity Number
67 INST_CHANGE_IND Character(1) VARCHAR2(1) NOT NULL Is Change Of Institution
68 PREV_INSTID Character(5) VARCHAR2(5) NOT NULL Previous Institution
69 SBIRSTTRT_PHASE Character(1) VARCHAR2(1) NOT NULL SBIR STTR Type
1=Phase 1
2=Phase 2
F=Fast Track
70 DESCR254 Character(254) VARCHAR2(254) NOT NULL Description of length 254
71 PRE_TRACK_NUM Character(240) VARCHAR2(240) NOT NULL Tracking Number
72 IS_INV_PATEN Character(1) VARCHAR2(1) NOT NULL Is Inventions And Patents
73 IS_PRE_RPT Character(1) VARCHAR2(1) NOT NULL Is Previously Reported
74 IS_PRO_INCOM Character(1) VARCHAR2(1) NOT NULL Program Income
75 REV_12372 Character(1) VARCHAR2(1) NOT NULL Review
C=Not Covered
S=Not Selected
Y=Yes
76 REV_12372_DT Date(10) DATE Review Date
77 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
78 GM_PROJ_DESC Character(254) VARCHAR2(254) NOT NULL Project Description
79 GM_AGENCY_NAME Character(60) VARCHAR2(60) NOT NULL Agency Name
80 EFF_LOC Character(45) VARCHAR2(45) NOT NULL Location
81 GM_TRUSTAGREE Character(14) VARCHAR2(14) NOT NULL Trust Agree
82 FEDERAL_EIN_GM Character(30) VARCHAR2(30) NOT NULL Federal EIN
83 APP_IDNT Character(30) VARCHAR2(30) NOT NULL Applicant Identifier
84 ST_APP_IDNT Character(30) VARCHAR2(30) NOT NULL State Application Idetifier
85 SUBMISSION_TYPE Character(35) VARCHAR2(35) NOT NULL There will be 3 valid value for this field: Pre-Application, Application, Changed/Corrected Application
86 ST_REC_DT Date(10) DATE State Received Date
87 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract
88 CONT_ALLOW_PCT Signed Number(7,2) DECIMAL(5,2) NOT NULL Allowable Construction Pct
89 CONT_NO_ALLW_PCT Signed Number(7,2) DECIMAL(5,2) NOT NULL Not allowable Construction Pct
90 FED_SHARE_PCT Signed Number(7,2) DECIMAL(5,2) NOT NULL Federal Share Percentage