GM_BUD_PD_SRH_L

(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 , BP.BUDGET_ID , BP.BUDGET_PERIOD_GM , LP.LANGUAGE_CD , LB.DESCR , LP.TITLE56 FROM PS_GM_PROPOSAL A , PS_GM_PROP_PROJ B , PSOPRDEFN D , PS_GM_BUD_HDR BH , PS_GM_BUD_PERIOD BP , PS_GM_PROP_PRJ_LNG LP , PS_GM_BUD_HDR_LNG LB 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 LP.BUSINESS_UNIT = B.BUSINESS_UNIT AND LP.PROPOSAL_ID = B.PROPOSAL_ID AND LP.VERSION_ID = B.VERSION_ID AND LP.SUB_PROP_NBR = B.SUB_PROP_NBR AND LB.BUSINESS_UNIT = B.BUSINESS_UNIT AND LB.PROPOSAL_ID = B.PROPOSAL_ID AND LB.VERSION_ID = B.VERSION_ID AND LB.SUB_PROP_NBR = B.SUB_PROP_NBR AND LB.BUDGET_ID = BP.BUDGET_ID AND LB.LANGUAGE_CD = LP.LANGUAGE_CD 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 for GM_BUD_PD_SRCH

    • # 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 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    9 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    10 TITLE56 Character(56) VARCHAR2(56) NOT NULL TITLE56