GM_BUD_PD_SRCH

(SQL View)
 Index Back

Proposal Budget Prd Sec Search

Used for Proposal Projects Budget Period 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 , BP.BUDGET_PERIOD_GM , BH.DESCR , A.EMPLID , A.CUST_ID , A.PROPOSAL_TYPE , A.PROPOSAL_STATUS_GR , B.DEPTID , B.TITLE56 FROM PS_GM_PROPOSAL A , PS_GM_PROP_PROJ B , PSOPRDEFN D , PS_GM_BUD_HDR BH , PS_GM_BUD_PERIOD BP 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 BP.BUSINESS_UNIT = BH.BUSINESS_UNIT AND BP.PROPOSAL_ID = BH.PROPOSAL_ID AND BP.VERSION_ID = BH.VERSION_ID AND BP.SUB_PROP_NBR = BH.SUB_PROP_NBR AND BP.BUDGET_ID = BH.BUDGET_ID AND A.PROPOSAL_FLG = 'G' AND (D.EMPLID = A.EMPLID OR EXISTS ( SELECT 'RU1' FROM PSROLEUSER H WHERE D.OPRID = H.ROLEUSER AND H.ROLENAME = 'Grants Super User') 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', 'PAA')) 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 IN ('R', '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)) AND NOT EXISTS ( SELECT 'X' FROM PS_GM_SEC_DEPT_OPR OPR , PSTREENODE TN , PSTREELEAF TL , PS_GM_PROP_PROJ PRJ WHERE OPR.TREE_SETID = TN.SETID AND OPR.TREE_NAME = TN.TREE_NAME AND OPR.TREE_EFFDT = TN.EFFDT AND OPR.TREE_NODE_NUM = TN.TREE_NODE_NUM AND TN.SETID = TL.SETID AND TN.TREE_NAME = TL.TREE_NAME AND TL.TREE_NODE_NUM BETWEEN TN.TREE_NODE_NUM AND TN.TREE_NODE_NUM_END AND OPR.ACCESS_CD = 'N' AND OPR.OPRID = D.OPRID AND PRJ.BUSINESS_UNIT = A.BUSINESS_UNIT AND PRJ.PROPOSAL_ID = A.PROPOSAL_ID AND PRJ.VERSION_ID = A.VERSION_ID AND PRJ.DEPTID BETWEEN TL.RANGE_FROM AND TL.RANGE_TO)))

  • Related Language Record: GM_BUD_PD_SRH_L

    • # 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: SP_BU_CA_NONVW

    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

    Prompt Table: GM_BUD_HDR_SRCH

    7 BUDGET_PERIOD_GM Number(3,0) SMALLINT NOT NULL BUDGET_PERIOD_GM
    8 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    9 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

    Prompt Table: GM_ELIG_PI_VW

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

    Prompt Table: GM_CUSTOMER_VW

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

    Prompt Table: GM_DEPT_TBL_VW

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