GM_PROP_OA_6_VW

(SQL View)
 Index Back

Proposal Prof Other Support VW

This 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 , T.TEAM_MEMBER , T.PLAN_PROJROLE , A.AWARD_TYPE , A.AWARD_STATUS , A.TITLE56 , A.BEGIN_DT , A.END_DT , C.BILL_TO_CUST_ID , C.SHIP_TO_CUST_ID , 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 TEAM_MEMBER Character(30) VARCHAR2(30) NOT NULL Team Member
10 PROJ_ROLE Character(15) VARCHAR2(15) NOT NULL Project Role

Prompt Table: PROJ_ROLE

11 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
12 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
13 TITLE56 Character(56) VARCHAR2(56) NOT NULL TITLE56
14 BEGIN_DT Date(10) DATE Begin Date
15 END_DT Date(10) DATE End Date
16 BILL_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Customer
17 SHIP_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Ship To Customer
18 NAME Character(50) VARCHAR2(50) NOT NULL Name
19 PERCENTAGE Signed Number(7,2) DECIMAL(5,2) NOT NULL Percentage