GM_PT_EFF_AWD_V

(SQL View)

SELECT DISTINCT DET.BUSINESS_UNIT_PC , DET.PROJECT_ID , AWD.BUSINESS_UNIT , AWD.CONTRACT_NUM , AWD.CUST_ID , CUST.NAME1 , CUST.CUSTOMER_TYPE , AWD.AWARD_STATUS , AWD.AWARD_TYPE , AWD.TITLE56 , AWD.GM_ADMIN_CNTCT FROM PS_GM_AWARD AWD , PS_CUSTOMER CUST , PS_CA_DETAIL_PROJ DET WHERE DET.CONTRACT_NUM = AWD.CONTRACT_NUM AND CUST.CUST_ID = AWD.CUST_ID AND CUST.SETID = ( SELECT CTL.SETID FROM PS_SET_CNTRL_REC CTL WHERE CTL.SETCNTRLVALUE = AWD.BUSINESS_UNIT AND CTL.RECNAME = 'CUSTOMER')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

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

3 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

4 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract

5 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.

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

7 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

8 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

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 TITLE56 Character(56) VARCHAR2(56) NOT NULL TITLE56

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

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BUSINESS_UNIT_PC	Character(5)	VARCHAR2(5) NOT NULL	PC Business Unit
2	PROJECT_ID	Character(15)	VARCHAR2(15) NOT NULL	Project Id ChartField
3	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
4	CONTRACT_NUM	Character(25)	VARCHAR2(25) NOT NULL	Contract
5	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.
6	CUST_NAME	Character(40)	VARCHAR2(40) NOT NULL	Name
7	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
8	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
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	TITLE56	Character(56)	VARCHAR2(56) NOT NULL	TITLE56
11	GM_ADMIN_CNTCT	Character(11)	VARCHAR2(11) NOT NULL	Grant Administrator