Page History

...

ACADEMIC LEVEL,,TOT_CUM,TOT_PASSED_GPA, CUM_GPA

Records	Description of fields
ACADADM_APPL_PROGDATA	ADMIT_TYPE
ADM_APPL_PROG	EMPLID, ACAD_PROG, ADMIT_TERM	EMPLID, ADMIT TERM,ACAD_CAREER, ACAD_PROG,	ACAD_PLAN	ACAD_PLAN,PLAN_SEQ,
ADM_APPL_DATAPLAN	ADMIT ACAD_ TYPE PLAN
CU_BOI_VW	LAST_NAME, FIRST_NAME, MIDDLE_NAME, PHONE, EMAIL_ADDRSERV_IND_DATA
SRVC_IND_CD for 'APS' and 'ACP' and have used for their reason codes	STDNT_CAR_TERM	UNT_TAKEN_PRGRSS
ACAD_PLAN_TBL	DESCR, DEGREE, SSR_NSC_CRD_LVL(DEG LEVEL)

CLASS_TBL_SE_VW	SUBJECT, CATALOG_NBR , CLASS_SECTION, CRSE_GRADE_OFF
STDNT_TEST_COMP	TEST_ID, TEST_COMPONENT, TEST_DT, SCORE	ACAD_SUBPLAN	ACAD_SUB_PLAN

Expressions:

Few 'Expressions' have been added to find 'Academic Level' have described the prompt as null and define the value of 'ACP' and 'APS'. Reference for Service Indicator: Service Indicators

...

ACAD_PROG_STATUS is 'AC' because the report is based on active students. Added 2 service indicator records to define 'APS' and 'ACP' with their reason code. Create join with ACAD_PLAN with ACAD_SUB_PLAN to define the major concentration.

Query Output Image:

Image RemovedImage Added

SQL Summery:

Panel

SELECT

A

DISTINCT B.EMPLID, D.LAST_NAME, D.FIRST_NAME, D.MIDDLE_NAME,

C.ADMIT_TYPE, A.ADMIT_TERM, A

B.ACAD_

CAREER, A.ACAD_

PROG,

B

C.ACAD_PLAN,

I.ACAD_SUB_PLAN, B.PLAN_SEQUENCE, G.DESCR, G.DEGREE, G.SSR_NSC_CRD_LVL, decode ( F.ACAD_LEVEL_BOT, '00', 'Not Set') ||
decode ( F.ACAD_LEVEL_BOT, '11', 'Lower Freshman') ||
decode ( F.ACAD_LEVEL_BOT, '15', 'Upper Freshman')||
decode ( F.ACAD_LEVEL_BOT, '21', 'Lower Sophmore') ||
decode ( F.ACAD_LEVEL_BOT, '25', 'Upper Sophmore')|| decode ( F.ACAD_LEVEL_BOT, '31', 'Lower Junior') ||
decode ( F.ACAD_LEVEL_BOT, '35', 'Upper Junior') || decode ( F.ACAD_LEVEL_BOT, '41', 'Lower Senior') ||
decode ( F.ACAD_LEVEL_BOT, '45', 'Upper Senior') ||
decode ( F.ACAD_LEVEL_BOT, '52', 'Second Degree') ||
decode ( F.ACAD_LEVEL_BOT, 'GR', 'Graduate'), F.

E.UNT_TAKEN_PRGRSS,

F

B.

TOT

ADMIT_

CUMULATIVE

TERM,

F

A.

TOT

ADMIT_

PASSD_GPA, F.CUM_GPA,

TYPE, D.PHONE, D.EMAIL_ADDR,

E.SRVC_IND_CD || ' ' || E.SRVC_IND_REASON, H.SRVC_IND_CD || ' ' || H.SRVC_IND_REASON, E.SRVC_IND_ACT_TERM, H.SRVC_IND_ACT_TERM,G.INSTITUTION,G.ACAD_PLAN

F.SUBJECT, F.CATALOG_NBR, F.CLASS_SECTION, F.CRSE_GRADE_OFF, G.TEST_ID, G.TEST_COMPONENT, TO_CHAR(G.

EFFDT

TEST_DT,'YYYY-MM-DD'), G.SCORE
FROM PS_ADM_

ACAD

APPL_

PROG

DATA A, PS_

ACAD_PLAN B

ADM_APPL_SCTY A1, (SELECT

TC.ADMIT_TYPE, TC.EMPLID, TC

TB.EMPLID, TB.ACAD_PROG, TB.ADMIT_TERM, TB.ACAD_CAREER,

TC

TB.STDNT_CAR_NBR,

TC

TB.ADM_APPL_NBR, TB.EFFDT, TB.PROG_ACTION, TB.APPL_PROG_NBR, TB.EFFSEQ,

TC

TB.INSTITUTION
FROM PS_ADM_APPL_

DATA TC

PROG TB,PS_ADM_

APPL

MAINT_SCTY

C1

B1 WHERE

TC

TB.EMPLID =

C1

B1.EMPLID AND

TC

TB.ACAD_CAREER =

C1

B1.ACAD_CAREER AND

TC

TB.STDNT_CAR_NBR = B1.STDNT_CAR_NBR AND TB.ADM_APPL_NBR =

C1

B1.ADM_APPL_NBR AND

C1

TB.APPL_PROG_NBR = B1.APPL_PROG_NBR AND B1.OPRCLASS = 'CUHCPPBAR01'

AND C1

AND B1.OPRID = '11002650' ) B, PS_ADM_APPL_PLAN C, PS_CU_BIO_VW D, PS_STDNT_CAR_TERM

F

E, PS_

ACAD_PLAN

CLASS_TBL

G, PS

_

SRVC_IND_DATA E

SE_VW F, PS_

SRVC

STDNT_

IND_DATA H, PS_ACAD_SUBPLAN I

TEST_COMP G
WHERE ( A.

EFFDT

EMPLID =

(SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
WHERE A.EMPLID = A_ED.EMPLID

A1.EMPLID
    AND A.ACAD_CAREER = A1.ACAD_CAREER
    AND A.ADM_APPL_NBR = A1.ADM_APPL_NBR
    AND A1.OPRCLASS = 'CUHCPPBAR01'
    AND A1.OPRID = '11002650'
    AND ( A.INSTITUTION = :1
     AND A.ACAD_CAREER = 'UGRD'
     AND A.EMPLID = B.EMPLID
     AND A.ACAD_CAREER =

A_ED

B.ACAD_CAREER
AND A.STDNT_CAR_NBR =

A_ED

B.STDNT_CAR_NBR
AND A

_ED.EFFDT <= SYSDATE)
AND A.EFFSEQ =

.ADM_APPL_NBR = B.ADM_APPL_NBR
AND B.EFFDT =
(SELECT MAX(

A

B_

ES

ED.

EFFSEQ

EFFDT) FROM PS_

ACAD

ADM_APPL_PROG

A

B_

ES

ED
WHERE

A

B.EMPLID =

A

B_

ES

ED.EMPLID
AND

A

B.ACAD_CAREER =

A

B_

ES

ED.ACAD_CAREER
AND

A

B.STDNT_CAR_NBR =

A

B_

ES

ED.STDNT_CAR_NBR

AND A.EFFDT = A_ES.EFFDT)
AND A

          AND B.ADM_APPL_NBR = B_ED.ADM_APPL_NBR
          AND B.APPL_PROG_NBR = B_ED.APPL_PROG_NBR
          AND B_ED.EFFDT <= SYSDATE)
    AND B.EFFSEQ =
        (SELECT MAX(B_ES.EFFSEQ) FROM PS_ADM_APPL_PROG B_ES
        WHERE B.EMPLID = B_ES.EMPLID
          AND

A

B.ACAD_CAREER = B_ES.ACAD_CAREER
AND

A

B.STDNT_CAR_NBR = B_ES.STDNT_CAR_NBR

AND A.EFFSEQ = B.EFFSEQ
AND A

          AND B.ADM_APPL_NBR = B_ES.ADM_APPL_NBR
          AND B.APPL_PROG_NBR = B_ES.APPL_PROG_NBR
          AND B.EFFDT = B_ES.EFFDT)
     AND

A.INSTITUTION = :1
AND A

B.ADMIT_TERM BETWEEN :2 AND :3
AND ( B.PROG_

STATUS

ACTION = '

AC

MATR')
AND

A

B.EMPLID = C.EMPLID

(+)

AND

A

B.ACAD_CAREER = C.ACAD_CAREER

(+)

AND

A

B.STDNT_CAR_NBR = C.STDNT_CAR_NBR

(+)

AND

A

B.ADM_APPL_NBR = C.ADM_APPL_NBR

(+)
AND A.INSTITUTION = C.INSTITUTION (+)

     AND B.APPL_PROG_NBR = C.APPL_PROG_NBR
     AND B.EFFSEQ = C.EFFSEQ
     AND B.EFFDT = C.EFFDT
     AND A.EMPLID = D.EMPLID
     AND

A

B.EMPLID =

F

E.EMPLID
AND

A

B.ACAD_CAREER =

F

E.ACAD_CAREER
AND

A

B.STDNT_CAR_NBR =

F

E.STDNT_CAR_NBR
AND

A

E.INSTITUTION =

F.INSTITUTION
AND

B

.ACAD_PLAN = G.ACAD_PLAN
AND G.EFFDT =
(SELECT MAX(G_ED.EFFDT) FROM PS_ACAD_PLAN_TBL G_ED
WHERE G

.INSTITUTION

= G_ED.INSTITUTION

AND

G.ACAD_PLAN = G_ED.ACAD_PLAN
AND G_ED.EFFDT <= B.EFFDT)
AND A.INSTITUTION = G.INSTITUTION
AND A.ACAD_CAREER = :2
AND F.STRM = :3
AND F.

E.UNT_TAKEN_PRGRSS > 0
AND

( G

E.

DEGREE

STRM = :

4
OR :4 IS NULL)
AND (

3
AND A.

ACAD

ADMIT_

PROG = :5
OR :5 IS NULL)
AND ( B.ACAD_PLAN = :6
OR :6 IS NULL)
AND A.EMPLID = E.EMPLID (+)
AND 'APS' = E.SRVC_IND_CD (+)
AND A.INSTITUTION = E.INSTITUTION (+)
AND A.EMPLID = H.EMPLID (+)
AND A.INSTITUTION = H.INSTITUTION (+)
AND 'ACP' = H.SRVC_IND_CD (+)
AND B.EMPLID = I

TYPE = '2'
     AND B.EMPLID = F.EMPLID
     AND B.ACAD_CAREER = F.ACAD_CAREER
     AND F.INSTITUTION = B.INSTITUTION
     AND F.CRSE_ID IN ('090828','093130','093135','093157','093160')
     AND B.EMPLID = G.EMPLID (+)
     AND

B

( G.

ACAD_CAREER = I.ACAD_CAREER

TEST_ID (+)

AND B.EFFSEQ = I.EFFSEQ (+)
AND B.ACAD_PLAN = I.ACAD_PLAN (+)
AND B.STDNT_CAR_NBR = I.STDNT_CAR_NBR (+)
AND B.EFFDT = I.EFFDT (+) )
ORDER BY

= 'RGNTS'
AND G.TEST_COMPONENT (+) IN ('SMA3','MATHB')) ))
ORDER BY 6, 2, 3, 4

, 11

Page tree

Versions Compared

Old Version 2

New Version 3

Key

Expressions:

Query Output Image:

SQL Summery: