GM_AWD_PG_VW(SQL View) |
Index Back |
---|---|
Award Based Pivot Grid view |
SELECT C.BUSINESS_UNIT , C.CONTRACT_NUM , SUM(A.FOREIGN_AMOUNT) , A.FOREIGN_CURRENCY , C.PURPOSE , E.CONTRACT_TYPE , C.AWARD_TYPE , C.EMPLID , F.NAME , C.BEGIN_DT , C.AWARD_STATUS , P.DESCR FROM PS_PC_BUD_DETAIL A , PS_PC_BUD_PLAN B , PS_GM_AWARD C LEFT OUTER JOIN PS_SET_CNTRL_REC S ON S.SETCNTRLVALUE = C.BUSINESS_UNIT AND S.RECNAME = 'GM_PURPOSE' LEFT OUTER JOIN PS_GM_PURPOSE_VW P ON P.SETID = S.SETID AND P.PURPOSE = C.PURPOSE , PS_GM_AWD_PROJT_VW D , PS_CA_CONTR_HDR E , PS_GM_PERSONAL5_VW F WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PROJECT_ID = B.PROJECT_ID AND A.PC_BUDGET_ID = B.PC_BUDGET_ID AND B.PC_BUD_TYPE = 'C' AND A.GM_CS_SEQ_FLG <> '2' AND C.CONTRACT_NUM = D.CONTRACT_NUM AND C.CONTRACT_NUM = E.CONTRACT_NUM AND C.BUSINESS_UNIT = E.BUSINESS_UNIT AND A.BUSINESS_UNIT = D.BUSINESS_UNIT_PC AND A.PROJECT_ID = D.PROJECT_ID AND C.EMPLID = F.EMPLID GROUP BY C.BUSINESS_UNIT, C.CONTRACT_NUM, A.FOREIGN_CURRENCY, C.PURPOSE, E.CONTRACT_TYPE, C.AWARD_TYPE, C.EMPLID ,F.NAME, C.BEGIN_DT , C. AWARD_STATUS, P.DESCR |
# | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
---|---|---|---|---|
1 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
2 | CONTRACT_NUM | Character(25) | VARCHAR2(25) NOT NULL | Contract |
3 | FOREIGN_AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Specifies the monetary amount of a debit or credit (accounting entry line) in the entry currency of the item. The sum of FOREIGN_AMOUNT values on AR lines should equal the ENTRY_AMT on the pending item. |
4 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
5 | PURPOSE | Character(5) | VARCHAR2(5) NOT NULL | Purpose |
6 | CONTRACT_TYPE | Character(15) | VARCHAR2(15) NOT NULL | Contract Type |
7 | 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 |
8 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
9 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
10 | BEGIN_DT | Date(10) | DATE | Begin Date |
11 | 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 |
12 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |