SAD_BPV_HEG_SRC

(SQL View)
 Index Back

Search record

FS 09062004

SELECT DISTINCT A.EXT_ORG_ID , A.HEGIS_CODE , A.EFFDT , B.DESCR , B.DESCR60 , G.OPRID FROM PS_SSR_HEGISCD_NLD A , PS_HEGIS_CODE_TBL B , PS_ACAD_PROG_TBL C , PS_SCRTY_TBL_PROG G WHERE SSR_FINAL_QUAL_NLD = 'Y' AND A.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_HEGIS_CODE_TBL A_ED WHERE A.HEGIS_CODE = A_ED.HEGIS_CODE AND A_ED.EFFDT <= %CurrentDateIn) AND A.HEGIS_CODE = C.HEGIS_CODE AND C.EFFDT = ( SELECT MAX(C_ED.EFFDT) FROM PS_ACAD_PROG_TBL C_ED WHERE C.INSTITUTION = C_ED.INSTITUTION AND C.ACAD_PROG = C_ED.ACAD_PROG AND C_ED.EFFDT <= %CurrentDateIn) AND C.INSTITUTION = G.INSTITUTION AND G.ACCESS_CD = 'Y' AND C.ACAD_CAREER = G.ACAD_CAREER AND (C.ACAD_PROG = G.ACAD_PROG OR G.ACAD_PROG = 'ALL') AND NOT EXISTS ( SELECT 'X' FROM ps_scrty_tbl_prog g1 WHERE g1.access_cd = 'N' AND g1.oprid = g.oprid AND g1.institution = g.institution AND g1.acad_career = g.acad_career AND g1.acad_prog = C.ACAD_PROG) AND B.HEGIS_CODE = A.HEGIS_CODE AND B.EFFDT = A.EFFDT UNION SELECT DISTINCT NL.EXT_ORG_ID , HG.HEGIS_CODE , HG.EFFDT , HG.DESCR , HG.DESCR60 ,%OperatorId FROM PS_HEGIS_CODE_TBL HG , PS_SSR_HEGISCD_NLD NL 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: SADBPVHEGSRCLNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 SAD_KC_ID_NLD Character(11) VARCHAR2(11) NOT NULL Knowledge centre id

    Prompt Table: SAD_BPV_KC_SRCH

    2 HEGIS_CODE Character(8) VARCHAR2(8) NOT NULL HEGIS Code

    Prompt Table: HEGIS_CODE_TBL

    3 EFFDT Date(10) DATE Effective Date

    Default Value: %date

    4 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    5 DESCR60 Character(60) VARCHAR2(60) NOT NULL Description
    6 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).