GM_BUD_HDR_SRCH

(SQL View)
 Index Back

Proposal Budget Hdr Sec Search

Used for Proposal Projects Budget Header search record with PI and Department Level Security by Operator ID. Essentially, if an operator can access the Proposal Project then, all Budget data for the Proposal Project is accessable too.

SELECT DISTINCT D.OPRID , A.BUSINESS_UNIT , A.PROPOSAL_ID , A.VERSION_ID , B.SUB_PROP_NBR , BH.BUDGET_ID , BH.DESCR , A.EMPLID , A.CUST_ID , B.DEPTID , B.TITLE56 , A.PROPOSAL_TYPE , A.PROPOSAL_STATUS_GR FROM PS_GM_PROPOSAL A , PS_GM_PROP_PROJ B , PSOPRDEFN D , PS_GM_BUD_HDR BH WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PROPOSAL_ID = B.PROPOSAL_ID AND A.VERSION_ID = B.VERSION_ID AND BH.BUSINESS_UNIT = B.BUSINESS_UNIT AND BH.PROPOSAL_ID = B.PROPOSAL_ID AND BH.VERSION_ID = B.VERSION_ID AND BH.SUB_PROP_NBR = B.SUB_PROP_NBR AND A.PROPOSAL_FLG = 'G' AND (D.EMPLID = A.EMPLID OR EXISTS ( SELECT 'x' FROM PS_GM_PROP_PROF F WHERE F.EMPLID = D.EMPLID AND F.BUSINESS_UNIT = B.BUSINESS_UNIT AND F.PROPOSAL_ID = B.PROPOSAL_ID AND F.VERSION_ID = B.VERSION_ID AND F.SUB_PROP_NBR = B.SUB_PROP_NBR AND F.PROF_ROLE_TYPE IN ('PI','CPI','AUTH')) 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 = B.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 B.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_PC.BUSINESS_UNIT

Prompt Table: GM_PROP_BU_SRCH

3 PROPOSAL_ID Character(25) VARCHAR2(25) NOT NULL Proposal ID

Prompt Table: GM_PROP_ID_SRCH

4 VERSION_ID Character(7) VARCHAR2(7) NOT NULL Version ID

Prompt Table: GM_PROP_VN_SRCH

5 SUB_PROP_NBR Character(15) VARCHAR2(15) NOT NULL SUB_PROP_NBR

Prompt Table: GM_PROP_J_SRCH

6 BUDGET_ID Character(15) VARCHAR2(15) NOT NULL BUDGET_ID
7 DESCR Character(30) VARCHAR2(30) NOT NULL Description
8 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: GM_ELIG_PI_VW

9 SPONSOR_ID Character(15) VARCHAR2(15) NOT NULL SPONSOR_ID

Prompt Table: GM_CUSTOMER_VW

10 DEPTID2 Character(10) VARCHAR2(10) NOT NULL DEPTID2

Prompt Table: GM_DEPT_TBL_VW

11 TITLE56 Character(56) VARCHAR2(56) NOT NULL TITLE56
12 PROPOSAL_TYPE Character(3) VARCHAR2(3) NOT NULL PROPOSAL_TYPE
AMD=Amendment
CNT=Continuation
CON=Competing Continuation
INT=Internal
MOD=Modification
NEW=New
NOC=No-Cost Extension
NON=Non-Competing Continuation
NRS=NRSA
PRE=Pre-Proposal
RCA=Research Career Award
REN=Renewal
RES=Resubmission
REV=Revision
SB1=SBIR I
SB2=SBIR II
ST1=STTR I
ST2=STTR II
SUP=Supplemental
13 PROPOSAL_STATUS_GR Character(4) VARCHAR2(4) NOT NULL PROPOSAL_STATUS_GR
ACP=Accepted Award
APR=Institution Approved
AWD=Awarded
AWP=Award Pending
DEC=Declined by Sponsor
DIS=Discontinued
DRF=Draft
NOT=Not Funded
PEF=Pending Funding
PEN=Pending Approval
REF=Refused by Institution
REJ=Rejected
SUBM=Submitted
WIT=Withdrawn