GM_PROP_SRCH_L

(SQL View)
 Index Back

Proposal Search Record

Used as secured search record for Proposal.

SELECT DISTINCT D.OPRID , A.BUSINESS_UNIT , A.PROPOSAL_ID , A.VERSION_ID , C.LANGUAGE_CD , C.DESCR20A , C.TITLE56 FROM PS_GM_PROPOSAL A , PS_GM_PROP_PROJ B , PSOPRDEFN D , PS_GM_PROPOSAL_LNG C WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PROPOSAL_ID = B.PROPOSAL_ID AND A.VERSION_ID = B.VERSION_ID AND A.PROPOSAL_FLG = 'G' AND C.BUSINESS_UNIT = B.BUSINESS_UNIT AND C.PROPOSAL_ID = B.PROPOSAL_ID AND C.VERSION_ID = B.VERSION_ID 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 'X1' 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 'X1' 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 'X2' 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 'X1' 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 'X2' 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_PROP_DR_SRCH GM_PROP_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_CA.BUSINESS_UNIT

    Prompt Table: SP_BU_CA_NONVW

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

    Default Value: NEXT

    Prompt Table: GM_PROP_ID_SRCH

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

    Prompt Table: GM_PROPOSAL

    5 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    6 DESCR20A Character(20) VARCHAR2(20) NOT NULL Short Description
    7 TITLE56 Character(56) VARCHAR2(56) NOT NULL TITLE56