GM_VRSN_SRCH

(SQL View)
 Index Back

Protocol Version Search VW

Secured search record for Protocol Versions based on the Protocol Type.

SELECT DISTINCT D.OPRID , B.BUSINESS_UNIT , B.PCL_ID , B.PCL_VRSN_ID , A.PCL_TYPE , B.PCL_STATUS , B.DESCR FROM PS_GM_PCL_HDR A , PS_GM_PCL_VRSN B , PS_GM_PCL_PROF C , PSOPRDEFN D WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PCL_ID = B.PCL_ID AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.PCL_ID = C.PCL_ID AND B.PCL_VRSN_ID = C.PCL_VRSN_ID AND (D.EMPLID = B.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_PCL_PROF F WHERE F.EMPLID = D.EMPLID AND F.BUSINESS_UNIT = B.BUSINESS_UNIT AND F.PCL_ID = B.PCL_ID AND F.PCL_VRSN_ID = B.PCL_VRSN_ID AND F.PROF_ROLE_TYPE IN ('PI','CPI','AUTH')) 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 = '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)))

  • Related Language Record: GM_VRSN_SRCH_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_PC_NONVW

    3 PCL_ID Character(15) VARCHAR2(15) NOT NULL PCL_ID

    Prompt Table: GM_PCL_HDR

    4 PCL_VRSN_ID Character(7) VARCHAR2(7) NOT NULL PCL_VRSN_ID
    5 PCL_TYPE Character(10) VARCHAR2(10) NOT NULL Protocol Type

    Prompt Table: GM_PCL_TYPE

    6 PCL_STATUS Character(4) VARCHAR2(4) NOT NULL PCL_STATUS
    APPR=Approved
    DEND=Denied
    DRFT=Draft
    EXPD=Expired
    INAC=Inactive
    PEND=Pending
    RETD=Returned
    7 DESCR Character(30) VARCHAR2(30) NOT NULL Description