SSR_IAMANONSRCH

(SQL View)
 Index Back

Activity Search Security View

This view is based on CRSE_CATLG_SCTY, a search view used to restrict course catalog data access to courses offered under Academic Organizations that an operator is authorized to access.

SELECT DISTINCT C.OPRID ,B.INSTITUTION ,B.ACAD_CAREER ,X.STRM ,D.DESCR ,X.SSR_PERIOD_ID ,' ' ,B.SUBJECT ,B.CATALOG_NBR ,A.DESCR ,I.EMPLID ,P.LAST_NAME_SRCH ,P.FIRST_NAME_SRCH ,P.NAME ,I.SSR_ACTIVITY_ID ,I.SSR_ACT_ORG_SQ_NBR ,O.SSR_ANID_SEQ_NBR ,O.SSR_ANONYMOUS_ID , CASE WHEN I.SSR_ACT_ORG_SQ_NBR = 1 THEN 'C' ELSE 'R' END ,X.SSR_AC_EXAM_ONLY ,I.SSR_ACT_ID_ROOT FROM PS_SSR_ACTIVITY V ,PS_SSR_ACMCONT_ITM W ,PS_SSR_ACT_XREF X ,PS_SSR_ACT_ROOTEXT R ,PS_CRSE_CATALOG A ,PS_CRSE_OFFER B ,PS_SCRTY_TBL_ACAD C ,PS_TERM_TBL D ,PS_SSR_IAM I LEFT OUTER JOIN PS_SSR_IAM_ANON_ID O ON I.EMPLID = O.EMPLID AND I.SSR_ACTIVITY_ID = O.SSR_ACTIVITY_ID AND I.SSR_ACT_ID_SEQ_NBR = O.SSR_ACT_ID_SEQ_NBR ,PS_PEOPLE_SRCH P WHERE W.SSR_ACTIVITY_ID = V.SSR_ACTIVITY_ID AND W.INSTITUTION = V.INSTITUTION AND W.SSR_BLIND_GRADED = 'Y' AND X.SSR_AC_EXAM_ONLY <> 'Y' AND X.CRSE_ID = B.CRSE_ID AND X.CRSE_OFFER_NBR = B.CRSE_OFFER_NBR AND V.SSR_ACTIVITY_ID = X.SSR_ACTIVITY_ID AND X.SSR_AC_CONTENT_TP = '1000' AND R.SSR_ACTIVITY_ID = V.SSR_ACTIVITY_ID AND R.INSTITUTION = V.INSTITUTION AND A.CRSE_ID = B.CRSE_ID AND A.EFFDT = B.EFFDT AND 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=B.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)) AND B.INSTITUTION = D.INSTITUTION AND B.ACAD_CAREER = D.ACAD_CAREER AND D.STRM = X.STRM AND A.EFFDT = ( SELECT MAX(E.EFFDT) FROM PS_CRSE_CATALOG E WHERE A.CRSE_ID = E.CRSE_ID AND E.EFFDT <= D.TERM_BEGIN_DT) AND I.SSR_ACT_ID_ROOT = R.SSR_ACTIVITY_ID AND (I.SSR_ACTIVITY_ID = I.SSR_ACT_ID_ROOT OR I.SSR_ACT_ORG_SQ_NBR > 1) AND P.EMPLID = I.EMPLID UNION ALL SELECT DISTINCT C.OPRID ,B.INSTITUTION ,B.ACAD_CAREER ,X.STRM ,' ' ,X.SSR_PERIOD_ID ,D.DESCR ,B.SUBJECT ,B.CATALOG_NBR ,A.DESCR ,I.EMPLID ,P.LAST_NAME_SRCH ,P.FIRST_NAME_SRCH ,P.NAME ,I.SSR_ACTIVITY_ID ,I.SSR_ACT_ORG_SQ_NBR ,O.SSR_ANID_SEQ_NBR ,O.SSR_ANONYMOUS_ID , CASE WHEN I.SSR_ACT_ORG_SQ_NBR = 1 THEN 'C' ELSE 'R' END ,X.SSR_AC_EXAM_ONLY ,I.SSR_ACT_ID_ROOT FROM PS_SSR_ACTIVITY V ,PS_SSR_ACMCONT_ITM W ,PS_SSR_ACT_XREF X ,PS_SSR_ACT_ROOTEXT R ,PS_CRSE_CATALOG A ,PS_CRSE_OFFER B ,PS_SCRTY_TBL_ACAD C ,PS_SSR_PERIOD_TBL D ,PS_SSR_IAM I LEFT OUTER JOIN PS_SSR_IAM_ANON_ID O ON I.EMPLID = O.EMPLID AND I.SSR_ACTIVITY_ID = O.SSR_ACTIVITY_ID AND I.SSR_ACT_ID_SEQ_NBR = O.SSR_ACT_ID_SEQ_NBR ,PS_PEOPLE_SRCH P WHERE W.SSR_ACTIVITY_ID = V.SSR_ACTIVITY_ID AND W.INSTITUTION = V.INSTITUTION AND W.SSR_BLIND_GRADED = 'Y' AND X.SSR_AC_EXAM_ONLY = 'Y' AND X.CRSE_ID = B.CRSE_ID AND X.CRSE_OFFER_NBR = B.CRSE_OFFER_NBR AND V.SSR_ACTIVITY_ID = X.SSR_ACTIVITY_ID AND X.SSR_AC_CONTENT_TP = '1000' AND R.SSR_ACTIVITY_ID = V.SSR_ACTIVITY_ID AND R.INSTITUTION = V.INSTITUTION AND A.CRSE_ID = B.CRSE_ID AND A.EFFDT = B.EFFDT AND 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=B.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)) AND B.INSTITUTION = D.INSTITUTION AND D.SSR_PERIOD_ID = X.SSR_PERIOD_ID AND A.EFFDT = ( SELECT MAX(E.EFFDT) FROM PS_CRSE_CATALOG E WHERE A.CRSE_ID = E.CRSE_ID AND E.EFFDT <= D.SSR_PRD_START_DT) AND I.SSR_ACT_ID_ROOT = R.SSR_ACTIVITY_ID AND (I.SSR_ACTIVITY_ID = I.SSR_ACT_ID_ROOT OR I.SSR_ACT_ORG_SQ_NBR > 1) AND P.EMPLID = I.EMPLID

# 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 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

Default Value: OPR_DEF_TBL_CS.INSTITUTION

Prompt Table: INSTITUTION_TBL

3 ACAD_CAREER Character(4) VARCHAR2(4) NOT NULL Academic Career
BAC=Bachelor (NLD)
BBL=Vocational Coaching (NLD)
BOL=Vocational Training (NLD)
BUSN=Graduate Business
CNED=Continuing Education
CRED=Semester Credit
EDU=Education (NLD)
EXED=Extended Education
GRAD=Graduate
LAW=Law
MEDS=Medical School
NONA=Non Award
PGRD=Postgraduate
RSCH=Research
TECH=Technical
UENG=Undergraduate Engineering
UGRD=Undergraduate
VAVO=Advanced General Educ. (NLD)
VETM=Veterinary Medicine
4 STRM Character(4) VARCHAR2(4) NOT NULL Term

Prompt Table: TERM_VAL_TBL

5 SSR_TERM_DESCR Character(30) VARCHAR2(30) NOT NULL This field is used transcript report tables to display the term description.
6 SSR_PERIOD_ID Character(12) VARCHAR2(12) NOT NULL Academic Period ID

Prompt Table: SSR_EXAM_PRD_VW

7 SSR_DESCR Character(30) VARCHAR2(30) NOT NULL This field is used to hold a description in student records. This field is mixed case.
8 SUBJECT Character(8) VARCHAR2(8) NOT NULL Subject Area

Default Value: OPR_DEF_TBL_CS.SUBJECT

Prompt Table: SUBJECT_TBL_VW

9 CATALOG_NBR Character(10) VARCHAR2(10) NOT NULL Catalog Nbr
10 SSR_AC_CRS_DESCR Character(30) VARCHAR2(30) NOT NULL Course Description
11 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: PEOPLE_SRCH

12 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
13 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
14 NAME Character(50) VARCHAR2(50) NOT NULL Name
15 SSR_ACTIVITY_ID Character(11) VARCHAR2(11) NOT NULL Activity ID
16 SSR_ACT_ORG_SQ_NBR Number(4,0) SMALLINT NOT NULL Attempt Number
17 SSR_ANID_SEQ_NBR Number(3,0) SMALLINT NOT NULL Anonymous ID Sequence Number
18 SSR_ANONYMOUS_ID Number(10,0) DECIMAL(10) NOT NULL Anonymous ID
19 SSR_IAM_ANONID_TYP Character(1) VARCHAR2(1) NOT NULL Anonymous ID Type
C=Course
R=Resit
20 SSR_AC_EXAM_ONLY Character(1) VARCHAR2(1) NOT NULL Exam Only Course

Y/N Table Edit

21 SSR_ACT_ID_ROOT Character(11) VARCHAR2(11) NOT NULL Course Root Activity ID