ACAD_ORG_SRCH

(SQL View)
 Index Back

Academic Organization Search

ACAD_ORG_SRCH is a view which uses academic organization security to grant access to the academic organizations themselves, or other records keyed by ACAD_ORG. Users will only see ACAD_ORGs which they have security clearance to access. It is used as a prompt table for ACAD_ORG in Course Offer and Class Table. Use this view as the search record for the Academic Organization Table to restrict access to the table by security clearance.

SELECT A.INSTITUTION ,A.ACAD_ORG ,C.OPRID ,A.EFFDT ,A.EFF_STATUS ,A.DESCR ,A.CAMPUS ,C.ACCESS_CD FROM PS_ACAD_ORG_TBL A ,PS_SCRTY_TBL_ACAD C WHERE C.ACCESS_CD='Y' AND EXISTS (SELECT 'X' FROM PSTREENODE E WHERE E.SETID=' ' AND E.TREE_NAME='ACAD_ORGANIZATION' AND E.EFFDT=C.TREE_EFFDT AND E.TREE_NODE=A.ACAD_ORG AND E.TREE_NODE_NUM BETWEEN C.TREE_NODE_NUM AND C.TREE_NODE_NUM_END AND NOT EXISTS (SELECT 'X' FROM PS_SCRTY_TBL_ACAD G WHERE C.OPRID=G.OPRID AND C.TREE_NODE_NUM<>G.TREE_NODE_NUM AND E.TREE_NODE_NUM BETWEEN G.TREE_NODE_NUM AND G.TREE_NODE_NUM_END AND G.TREE_NODE_NUM BETWEEN C.TREE_NODE_NUM AND C.TREE_NODE_NUM_END))

  • Related Language Record: ACAD_ORG_SR_LVW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

    Prompt Table: INSTITUTION_TBL

    2 ACAD_ORG Character(10) VARCHAR2(10) NOT NULL Academic Organization
    3 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    4 EFFDT Date(10) DATE NOT NULL Effective Date

    Default Value: %date

    5 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
    A=Active
    I=Inactive
    6 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    7 CAMPUS Character(5) VARCHAR2(5) NOT NULL Campus

    Prompt Table: CAMPUS_TBL

    8 ACCESS_CD Character(1) VARCHAR2(1) NOT NULL Access Code
    N=No Access
    Y=Read/Write Access