RD_USR_PLN_VW (SQL View)	Index Back
Academic Plan Security View This view is a clone of ACAD_PLAN_SVW. By using this view as a prompt table you will be able to selects all plans that are associated with an Academic Program or a career within an Institution and plan that are valid for the Admit Term and the Admit Term is either less than or equal to the Last Admit Term or the Last Admit Term is blank.

RD_USR_PLN_VW

(SQL View)

Academic Plan Security View

This view is a clone of ACAD_PLAN_SVW. By using this view as a prompt table you will be able to selects all plans that are associated with an Academic Program or a career within an Institution and plan that are valid for the Admit Term and the Admit Term is either less than or equal to the Last Admit Term or the Last Admit Term is blank.

SELECT SP.OPRID , PLN.INSTITUTION , PRG.ACAD_PROG , PLN.ACAD_PLAN , SP.ACCESS_CD , PLN.FIRST_TERM_VALID , PLN.SSR_LAST_ADM_TERM , PLN.SSR_LAST_PRS_DT , PLN.DESCR FROM PS_ACAD_PLAN_TBL PLN , PS_ACAD_PROG_TBL PRG , PS_SCRTY_TBL_PLAN SP WHERE PLN.INSTITUTION = PRG.INSTITUTION AND PLN.ACAD_PROG = PRG.ACAD_PROG AND (%CurrentDateIn <= PLN.SSR_LAST_PRS_DT OR PLN.SSR_LAST_PRS_DT IS NULL) AND %EffdtCheck(ACAD_PLAN_TBL, PLN, %CurrentDateIn) AND PLN.EFF_STATUS <> 'I' AND PLN.INSTITUTION = SP.INSTITUTION AND (PLN.ACAD_PLAN = SP.ACAD_PLAN OR SP.ACAD_PLAN = 'ALL') AND NOT EXISTS ( SELECT 1 FROM PS_SCRTY_TBL_PLAN SP2 WHERE SP2.ACCESS_CD = 'N' AND SP2.OPRID = SP.OPRID AND SP2.INSTITUTION = SP.INSTITUTION AND SP2.ACAD_PLAN = SP.ACAD_PLAN) UNION SELECT SP.OPRID , PLN.INSTITUTION , PRG.ACAD_PROG , PLN.ACAD_PLAN , SP.ACCESS_CD , PLN.FIRST_TERM_VALID , PLN.SSR_LAST_ADM_TERM , PLN.SSR_LAST_PRS_DT , PLN.DESCR FROM PS_ACAD_PLAN_TBL PLN , PS_ACAD_PROG_TBL PRG , PS_SCRTY_TBL_PLAN SP WHERE PLN.INSTITUTION = PRG.INSTITUTION AND PLN.ACAD_PROG = ' ' AND PLN.ACAD_CAREER = PRG.ACAD_CAREER AND (%CurrentDateIn <= PLN.SSR_LAST_PRS_DT OR PLN.SSR_LAST_PRS_DT IS NULL) AND %EffdtCheck(ACAD_PLAN_TBL, PLN, %CurrentDateIn) AND PLN.EFF_STATUS <> 'I' AND PLN.INSTITUTION = SP.INSTITUTION AND (PLN.ACAD_PLAN = SP.ACAD_PLAN OR SP.ACAD_PLAN = 'ALL') AND NOT EXISTS ( SELECT 1 FROM PS_SCRTY_TBL_PLAN SP2 WHERE SP2.ACCESS_CD = 'N' AND SP2.OPRID = SP.OPRID AND SP2.INSTITUTION = SP.INSTITUTION AND SP2.ACAD_PLAN = SP.ACAD_PLAN)

Related Language Record: RD_USR_PLN_LVW

# 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 Institution field to store institution information in campus solution system.
Prompt Table: INSTITUTION_TBL

3 ACAD_PROG Character(5) VARCHAR2(5) NOT NULL Academic program field coming from campus solution system.
Prompt Table: ACAD_PROG_TBL

4 ACAD_PLAN Character(10) VARCHAR2(10) NOT NULL Academic Plan field coming from campus solution system
Prompt Table: ACAD_PLAN_TBL

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

6 FIRST_TERM_VALID Character(4) VARCHAR2(4) NOT NULL This field is used to indicate first valid term.

7 SSR_LAST_ADM_TERM Character(4) VARCHAR2(4) NOT NULL This field stores the Last Admitted Term.

8 SSR_LAST_PRS_DT Date(10) DATE Field used to store the Last Prospect Date values.

9 DESCR Character(30) VARCHAR2(30) NOT NULL Description

#	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	Institution field to store institution information in campus solution system. Prompt Table: INSTITUTION_TBL
3	ACAD_PROG	Character(5)	VARCHAR2(5) NOT NULL	Academic program field coming from campus solution system. Prompt Table: ACAD_PROG_TBL
4	ACAD_PLAN	Character(10)	VARCHAR2(10) NOT NULL	Academic Plan field coming from campus solution system Prompt Table: ACAD_PLAN_TBL
5	ACCESS_CD	Character(1)	VARCHAR2(1) NOT NULL	Access Code N=No Access R=Read Only Access Y=Read/Write Access
6	FIRST_TERM_VALID	Character(4)	VARCHAR2(4) NOT NULL	This field is used to indicate first valid term.
7	SSR_LAST_ADM_TERM	Character(4)	VARCHAR2(4) NOT NULL	This field stores the Last Admitted Term.
8	SSR_LAST_PRS_DT	Date(10)	DATE	Field used to store the Last Prospect Date values.
9	DESCR	Character(30)	VARCHAR2(30) NOT NULL	Description