GM_PROP_OA_2_VW(SQL View) |
Index Back |
|---|---|
Proposal Prof Other Support VWThis View contains the proposal specific details of a professionals Active support (in existing Awards). |
| SELECT D.BUSINESS_UNIT , D.PROPOSAL_ID , D.VERSION_ID , D.SUB_PROP_NBR , D.ROW_NUMBER , T.BUSINESS_UNIT , A.CONTRACT_NUM , T.PROJECT_ID , A.AWARD_TYPE , A.AWARD_STATUS , A.TITLE56 , A.BEGIN_DT , A.END_DT , C.BILL_TO_CUST_ID , C.SHIP_TO_CUST_ID , T.TEAM_MEMBER , N.NAME , T.PERCENTAGE FROM PS_GM_PROP_PROF D , PS_PROJECT_TEAM T , PS_PROJECT P , PS_GM_AWARD A , PS_GM_CA_DTL_PJ_VW B , PS_CA_CONTR_HDR C , PS_PERS_DATA_EFFDT N , PS_GM_PROPOSAL H WHERE (D.EMPLID<>' ' AND D.EMPLID=T.TEAM_MEMBER) AND T.TEAM_MEMBER = N.EMPLID AND N.EFFDT=( SELECT MAX(N1.EFFDT) FROM PS_PERS_DATA_EFFDT N1 WHERE N1.EMPLID = N.EMPLID AND N1.EFFDT <= %CurrentDateIn) AND T.BUSINESS_UNIT = P.BUSINESS_UNIT AND T.PROJECT_ID = P.PROJECT_ID AND P.GRANT_FLG = 'Y' AND P.BUSINESS_UNIT=B.BUSINESS_UNIT_PC AND P.PROJECT_ID=B.PROJECT_ID AND A.BUSINESS_UNIT = B.BUSINESS_UNIT_PC AND A.CONTRACT_NUM = B.CONTRACT_NUM AND A.AWARD_STATUS IN ('ACP', 'PEN', 'PRE', 'TRA') AND C.BUSINESS_UNIT = A.BUSINESS_UNIT AND C.CONTRACT_NUM = A.CONTRACT_NUM AND D.BUSINESS_UNIT = H.BUSINESS_UNIT AND D.PROPOSAL_ID=H.PROPOSAL_ID AND D.VERSION_ID=H.VERSION_ID AND A.CONTRACT_NUM <>H.CONTRACT_NUM |
| # | 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 | SUB_PROP_NBR |
Character(15) | VARCHAR2(15) NOT NULL | SUB_PROP_NBR |
| 5 | ROW_NUMBER |
Number(3,0) | SMALLINT NOT NULL | Row_Number |
| 6 | BUSINESS_UNIT_PROP |
Character(5) | VARCHAR2(5) NOT NULL | Proposal Business Unit |
| 7 | CONTRACT_NUM | Character(25) | VARCHAR2(25) NOT NULL | Contract |
| 8 | PROJECT_ID | Character(15) | VARCHAR2(15) NOT NULL | Project Id ChartField |
| 9 | AWARD_TYPE | Character(3) | VARCHAR2(3) NOT NULL |
Award Type
BOA=Basic Ordering Agreement CLI=Clinical Trial CON=Contract CP=Cooperative Agreement FDP=FDP FIX=Fixed GR=Grant IPA=IPA LIC=License LN=Loan MOU=MOU MTA=MTA NDA=NDA OTH=Other PA=Patent Agreement PO=Purchase Order SB=SubContract TO=Task Order |
| 10 | AWARD_STATUS | Character(3) | VARCHAR2(3) NOT NULL |
Award Status
ACP=Accepted CLS=Closed HOL=Holding NEG=Under Negotiation PEN=Pending PRE=Pre-Award REF=Refused TER=Terminated TRA=Transfer WTH=Withdrawn |
| 11 | TITLE56 | Character(56) | VARCHAR2(56) NOT NULL | TITLE56 |
| 12 | BEGIN_DT | Date(10) | DATE | Begin Date |
| 13 | END_DT | Date(10) | DATE | End Date |
| 14 | BILL_TO_CUST_ID | Character(15) | VARCHAR2(15) NOT NULL | Customer |
| 15 | SHIP_TO_CUST_ID | Character(15) | VARCHAR2(15) NOT NULL | Ship To Customer |
| 16 | TEAM_MEMBER | Character(30) | VARCHAR2(30) NOT NULL | Team Member |
| 17 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 18 | PERCENTAGE | Signed Number(7,2) | DECIMAL(5,2) NOT NULL | Percentage |