SAD_BPV_KC_SRCH

(SQL View)
 Index Back

Search record

FS 09062004

SELECT DISTINCT A.EXT_ORG_ID , A.DESCR , A.DESCRSHORT , A.OTH_NAME_SORT_SRCH , G.OPRID FROM PS_EXT_ORG_TBL A , PS_SSR_HEGISCD_NLD B , PS_ACAD_PROG_TBL C , PS_SCRTY_TBL_PROG G WHERE A.EXT_ORG_TYPE = 'KNCT' AND A.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_EXT_ORG_TBL A_ED WHERE A.EXT_ORG_ID = A_ED.EXT_ORG_ID AND A_ED.EFFDT <= %CurrentDateIn) AND A.EFF_STATUS <> 'I' AND A.EXT_ORG_ID = B.EXT_ORG_ID AND B.SSR_FINAL_QUAL_NLD = 'Y' AND B.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PS_HEGIS_CODE_TBL B_ED WHERE B.HEGIS_CODE = B_ED.HEGIS_CODE AND B_ED.EFFDT <= %CurrentDateIn) AND B.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)

  • Related Language Record: SADBPVKCSRCHLNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EXT_ORG_ID Character(11) VARCHAR2(11) NOT NULL External Org ID
    2 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    3 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
    4 OTH_NAME_SORT_SRCH Character(50) VARCHAR2(50) NOT NULL Search Name
    5 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).