SAD_BPV_HEG_SCT

(SQL View)
 Index Back

View Security HEGIS code

FS 03052004

SELECT DISTINCT a.hegis_code ,b.oprid ,c.descr ,D.SCC_HEGIS_TYPE_NLD ,b.access_cd FROM ps_acad_prog_tbl a ,ps_scrty_tbl_prog b ,PS_HEGIS_CODE_TBL C ,PS_SSR_HEGISCD_NLD D WHERE b.access_cd = 'Y' AND a.institution = b.institution AND a.acad_career = b.acad_career AND A.HEGIS_CODE = C.HEGIS_CODE AND A.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG_TBL A_ED WHERE A.INSTITUTION = A_ED.INSTITUTION AND A.ACAD_PROG = A_ED.ACAD_PROG AND A_ED.EFFDT <= %CurrentDateIn) AND C.EFFDT = ( SELECT MAX(C_ED.EFFDT) FROM PS_HEGIS_CODE_TBL C_ED WHERE C_ED.HEGIS_CODE = A.HEGIS_CODE AND C_ED.EFFDT <= A.EFFDT) AND (a.acad_prog = b.acad_prog OR b.acad_prog = 'ALL') AND D.HEGIS_CODE = C.HEGIS_CODE AND D.EFFDT = C.EFFDT AND NOT EXISTS ( SELECT 'X' FROM ps_scrty_tbl_prog c WHERE c.access_cd = 'N' AND c.oprid = b.oprid AND c.institution = b.institution AND c.acad_career = b.acad_career AND c.acad_prog = a.acad_prog) UNION SELECT DISTINCT HG.HEGIS_CODE , OD.OPRID , HG.DESCR ,NL.SCC_HEGIS_TYPE_NLD ,'Y' FROM PS_HEGIS_CODE_TBL HG , PS_SSR_HEGISCD_NLD NL , PSOPRDEFN OD WHERE HG.HEGIS_CODE = NL.HEGIS_CODE AND NL.SCC_HEGIS_TYPE_NLD ='K' AND HG.EFFDT = NL.EFFDT AND HG.EFFDT = ( SELECT MAX(C_ED1.EFFDT) FROM PS_HEGIS_CODE_TBL C_ED1 WHERE C_ED1.HEGIS_CODE = HG.HEGIS_CODE AND C_ED1.EFFDT <= %CurrentDateIn)

  • Related Language Record: SADBPVHEGSCTLNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 HEGIS_CODE Character(8) VARCHAR2(8) NOT NULL HEGIS Code
    2 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    3 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    4 SCC_HEGIS_TYPE_NLD Character(1) VARCHAR2(1) NOT NULL Hegis Code Type
    C=Course
    E=Elective
    K=Elective
    P=Program
    T=Testimony
    5 ACCESS_CD Character(1) VARCHAR2(1) NOT NULL Access Code
    N=No Access
    Y=Read/Write Access