GM_CONT_SRCH

(SQL View)
 Index Back

Grants Continuation Search Rec


SELECT DISTINCT D.OPRID , W.BUSINESS_UNIT , W.CONTRACT_NUM , W.PROPOSAL_ID , W.VERSION_ID ,B.BUDGET_PERIOD_GM , W.TITLE56 , W.EMPLID , W.CUST_ID , W.AWARD_TYPE , W.AWARD_STATUS , T.DEPTID FROM PS_GM_AWARD W , PS_CA_DETAIL_PROJ P , PS_GM_PRJ_DEPT T ,PS_GM_PROP_PROJ J ,PS_GM_BUD_PERIOD B , PSOPRDEFN D WHERE W.CONTRACT_NUM = P.CONTRACT_NUM AND W.AWARD_STATUS IN ('ACP', 'CLS') AND T.BUSINESS_UNIT = P.BUSINESS_UNIT_PC AND T.PROJECT_ID = P.PROJECT_ID AND J.BUSINESS_UNIT = W.BUSINESS_UNIT AND J.PROJECT_ID = P.PROJECT_ID AND J.PROPOSAL_ID = W.PROPOSAL_ID AND J.VERSION_ID = W.VERSION_ID AND B.BUSINESS_UNIT = W.BUSINESS_UNIT AND B.PROPOSAL_ID = J.PROPOSAL_ID AND B.VERSION_ID = J.VERSION_ID AND B.SUB_PROP_NBR = J.SUB_PROP_NBR AND (D.EMPLID = W.EMPLID OR EXISTS ( SELECT 'X' FROM PSTREENODE E , PS_GM_SEC_DEPT_OPR S WHERE E.EFFDT = S.TREE_EFFDT AND D.OPRID = S.OPRID AND E.TREE_NAME = S.TREE_NAME AND E.TREE_NODE = T.DEPTID AND E.SETID = S.TREE_SETID AND E.TREE_NODE_NUM BETWEEN S.TREE_NODE_NUM AND S.TREE_NODE_NUM_END AND S.ACCESS_CD = 'Y' AND NOT EXISTS ( SELECT 'X' FROM PS_GM_SEC_DEPT_OPR S1 WHERE S1.OPRID = S.OPRID AND S1.ACCESS_CD = 'N' AND S1.TREE_SETID = S.TREE_SETID AND S1.TREE_NAME = S.TREE_NAME AND S1.TREE_EFFDT = S.TREE_EFFDT AND S1.TREE_NODE_NUM <> S.TREE_NODE_NUM AND E.TREE_NODE_NUM BETWEEN S1.TREE_NODE_NUM AND S1.TREE_NODE_NUM_END AND S1.TREE_NODE_NUM BETWEEN S.TREE_NODE_NUM AND S.TREE_NODE_NUM_END)) OR EXISTS ( SELECT 'X' FROM PSTREELEAF F , PS_GM_SEC_DEPT_OPR S WHERE F.EFFDT = S.TREE_EFFDT AND F.TREE_NAME = S.TREE_NAME AND D.OPRID = S.OPRID AND T.DEPTID BETWEEN F.RANGE_FROM AND F.RANGE_TO AND F.SETID = S.TREE_SETID AND F.TREE_NODE_NUM BETWEEN S.TREE_NODE_NUM AND S.TREE_NODE_NUM_END AND S.ACCESS_CD = 'Y' AND NOT EXISTS ( SELECT 'X' FROM PS_GM_SEC_DEPT_OPR S1 WHERE S1.OPRID = S.OPRID AND S1.ACCESS_CD = 'N' AND S1.TREE_SETID = S.TREE_SETID AND S1.TREE_NAME = S.TREE_NAME AND S1.TREE_EFFDT = S.TREE_EFFDT AND S1.TREE_NODE_NUM <> S.TREE_NODE_NUM AND F.TREE_NODE_NUM BETWEEN S1.TREE_NODE_NUM AND S1.TREE_NODE_NUM_END AND S1.TREE_NODE_NUM BETWEEN S.TREE_NODE_NUM AND S.TREE_NODE_NUM_END)))

# 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

Default Value: OPR_DEF_TBL_CA.BUSINESS_UNIT

Prompt Table: SP_BU_CA_NONVW

3 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Award ID
4 PROPOSAL_ID Character(25) VARCHAR2(25) NOT NULL Proposal ID
5 VERSION_ID Character(7) VARCHAR2(7) NOT NULL Version ID
6 BUDGET_PERIOD_GM Number(3,0) SMALLINT NOT NULL BUDGET_PERIOD_GM
7 TITLE56 Character(56) VARCHAR2(56) NOT NULL TITLE56
8 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: GM_PERSONAL1_VW

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

Prompt Table: GM_CUSTOMER_VW

10 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
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 DEPTID2 Character(10) VARCHAR2(10) NOT NULL DEPTID2

Prompt Table: GM_DEPT_TBL_VW