GM_PT_AWARD_VW

(SQL View)

SELECT C.OPRID , A.BUSINESS_UNIT , A.CONTRACT_NUM , A.PROCESS_DTTM , A.TITLE56 , A.BUSINESS_UNIT_PC , A.PROJECT_ID , A.PROJECTDESCR , A.AWARD_STATUS , A.BEGIN_DT , A.END_DT , A.CUST_ID , A.CUST_NAME , A.CUSTOMER_TYPE , A.REF_AWD_NUMBER , D.FED_AWD_ID_NUMBER , A.AWARD_TYPE , A.PURPOSE , A.EMPLID , A.NAME , A.DEPTID , A.DEPT_DESCR , A.GM_ADMIN_CNTCT , A.PCL_ANIM_FLG , A.PCL_HUM_FLG , A.BILLING_TERMS , A.PREV_CONTRACT_NUM , A.PO_REF , A.FLOW_THRU_FLG , A.COST_SHARE_FLG , A.FUNDED_AMT , A.BUDGETED_AMT , A.EXPENDED_AMT , A.ENCUMBERED_AMOUNT , A.PRE_ENCUMBERED_AMT , A.REMAINING_AMOUNT , A.INVOICED_AMT , A.RECEIVED_AMT , A.UNPAID_AMT , A.BURN_RATE , A.BURN_RATE_INDC , A.CURRENCY_CD FROM PS_GM_PT_AWARD A , PS_GM_PT_ACC_LVL2 B , PSOPRDEFN C , PS_GM_AWARD D WHERE B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.CONTRACT_NUM = A.CONTRACT_NUM AND C.EMPLID = B.EMPLID AND D.BUSINESS_UNIT = B.BUSINESS_UNIT AND D.CONTRACT_NUM = B.CONTRACT_NUM AND B.AWARD_FLAG = 'Y' AND (B.END_DT >= %CurrentDateIn OR B.END_DT IS NULL)

# 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 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

3 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Award ID

4 PROCESS_DTTM DateTime(26) TIMESTAMP Process Date Time

5 TITLE56 Character(56) VARCHAR2(56) NOT NULL TITLE56

6 BUSINESS_UNIT_PC Character(5) VARCHAR2(5) NOT NULL PC Business Unit

7 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField

8 PROJECTDESCR Character(30) VARCHAR2(30) NOT NULL Project Title

9 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

10 BEGIN_DT Date(10) DATE Begin Date

11 END_DT Date(10) DATE End Date

12 CUST_ID Character(15) VARCHAR2(15) NOT NULL Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations.

13 CUST_NAME Character(40) VARCHAR2(40) NOT NULL Name

14 CUSTOMER_TYPE Character(1) VARCHAR2(1) NOT NULL Customer Type
1=User 1
2=User 2
3=User 3
4=User 4
F=Foreign
M=Commercial
N=Consumer
S=State and Local
X=Excluded Foreign Customer

15 REF_AWD_NUMBER Character(25) VARCHAR2(25) NOT NULL Reference Award Number

16 FED_AWD_ID_NUMBER Character(50) VARCHAR2(50) NOT NULL Federal Award Identification Number for Grants use. Added in enhancement bug number 18492701.

17 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

18 PURPOSE Character(5) VARCHAR2(5) NOT NULL Purpose

19 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

20 NAME Character(50) VARCHAR2(50) NOT NULL Name

21 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

22 DEPTNAME Character(30) VARCHAR2(30) NOT NULL Department

23 GM_ADMIN_CNTCT Character(11) VARCHAR2(11) NOT NULL Grant Administrator

24 PCL_ANIM_FLG Character(1) VARCHAR2(1) NOT NULL Animal Protocol
Y/N Table Edit

25 PCL_HUM_FLG Character(1) VARCHAR2(1) NOT NULL Human Protocol
Y/N Table Edit

26 BILLING_TERMS Character(3) VARCHAR2(3) NOT NULL Billing Terms
CST=Cost Reimbursable
LOC=Letter of Credit
OTH=Other

27 PREV_CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Previous Project/Grant

28 PO_REF Character(30) VARCHAR2(30) NOT NULL Specifies the purchase order number associated with a receivables item.

29 FLOW_THRU_FLG Character(1) VARCHAR2(1) NOT NULL Flow Through
Y/N Table Edit
Default Value: Y

30 COST_SHARE_FLG Character(1) VARCHAR2(1) NOT NULL Cost Sharing Flag
Y/N Table Edit
Default Value: Y

31 FUNDED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Funded Amount

32 BUDGETED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Budgeted Assets

33 EXPENDED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Expended Amount

34 ENCUMBERED_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Encumbered Amount

35 PRE_ENCUMBERED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Pre Encumbered Amount

36 REMAINING_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Remaining Amount

37 INVOICED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Amt Invoiced

38 RECEIVED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Received Assets

39 UNPAID_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Unpaid Amount

40 BURN_RATE Signed Number(7,2) DECIMAL(5,2) NOT NULL Percentage

41 BURN_RATE_INDC Character(1) VARCHAR2(1) NOT NULL Burn Rate Indicator
H=High
L=Low
M=Medium

42 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

#	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	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
3	CONTRACT_NUM	Character(25)	VARCHAR2(25) NOT NULL	Award ID
4	PROCESS_DTTM	DateTime(26)	TIMESTAMP	Process Date Time
5	TITLE56	Character(56)	VARCHAR2(56) NOT NULL	TITLE56
6	BUSINESS_UNIT_PC	Character(5)	VARCHAR2(5) NOT NULL	PC Business Unit
7	PROJECT_ID	Character(15)	VARCHAR2(15) NOT NULL	Project Id ChartField
8	PROJECTDESCR	Character(30)	VARCHAR2(30) NOT NULL	Project Title
9	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
10	BEGIN_DT	Date(10)	DATE	Begin Date
11	END_DT	Date(10)	DATE	End Date
12	CUST_ID	Character(15)	VARCHAR2(15) NOT NULL	Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations.
13	CUST_NAME	Character(40)	VARCHAR2(40) NOT NULL	Name
14	CUSTOMER_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Customer Type 1=User 1 2=User 2 3=User 3 4=User 4 F=Foreign M=Commercial N=Consumer S=State and Local X=Excluded Foreign Customer
15	REF_AWD_NUMBER	Character(25)	VARCHAR2(25) NOT NULL	Reference Award Number
16	FED_AWD_ID_NUMBER	Character(50)	VARCHAR2(50) NOT NULL	Federal Award Identification Number for Grants use. Added in enhancement bug number 18492701.
17	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
18	PURPOSE	Character(5)	VARCHAR2(5) NOT NULL	Purpose
19	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
20	NAME	Character(50)	VARCHAR2(50) NOT NULL	Name
21	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department
22	DEPTNAME	Character(30)	VARCHAR2(30) NOT NULL	Department
23	GM_ADMIN_CNTCT	Character(11)	VARCHAR2(11) NOT NULL	Grant Administrator
24	PCL_ANIM_FLG	Character(1)	VARCHAR2(1) NOT NULL	Animal Protocol Y/N Table Edit
25	PCL_HUM_FLG	Character(1)	VARCHAR2(1) NOT NULL	Human Protocol Y/N Table Edit
26	BILLING_TERMS	Character(3)	VARCHAR2(3) NOT NULL	Billing Terms CST=Cost Reimbursable LOC=Letter of Credit OTH=Other
27	PREV_CONTRACT_NUM	Character(25)	VARCHAR2(25) NOT NULL	Previous Project/Grant
28	PO_REF	Character(30)	VARCHAR2(30) NOT NULL	Specifies the purchase order number associated with a receivables item.
29	FLOW_THRU_FLG	Character(1)	VARCHAR2(1) NOT NULL	Flow Through Y/N Table Edit Default Value: Y
30	COST_SHARE_FLG	Character(1)	VARCHAR2(1) NOT NULL	Cost Sharing Flag Y/N Table Edit Default Value: Y
31	FUNDED_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Funded Amount
32	BUDGETED_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Budgeted Assets
33	EXPENDED_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Expended Amount
34	ENCUMBERED_AMOUNT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Encumbered Amount
35	PRE_ENCUMBERED_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Pre Encumbered Amount
36	REMAINING_AMOUNT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Remaining Amount
37	INVOICED_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Amt Invoiced
38	RECEIVED_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Received Assets
39	UNPAID_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Unpaid Amount
40	BURN_RATE	Signed Number(7,2)	DECIMAL(5,2) NOT NULL	Percentage
41	BURN_RATE_INDC	Character(1)	VARCHAR2(1) NOT NULL	Burn Rate Indicator H=High L=Low M=Medium
42	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code