...

ORG_TBL, PS_EXT_ORG_TBL_ADM, PS_EXT_ORG_PRI_VW

Code Block

language	sql
title	PS_EXT_TRNSFR_EQ, PS_TRNSFR_RULE, PS_TRNSFR_RSUB, PS_TRNSFR_SUBJ

PS_EXT_TRNSFR_EQ, PS_TRNSFR_RULE, PS_TRNSFR_RSUB, PS_TRNSFR_SUBJ
SELECT DISTINCT
    	A.INSTITUTION,
    	A.TRNSFR_SRC_ID,
    	A.DEF_RULE_SRC_ID
FROM
    	PS_EXT_TRNSFR_EQ A,
    		PS_TRNSFR_RULE B,
    		PS_TRNSFR_RSUB C,
    		PS_TRNSFR_SUBJ D
WHERE A...

Code Block

language

sql

title

EFFDT = (	SELECT MAX(A_ED.EFFDT)
					FROM PS_EXT_

/**
 * Where clause specifies: MAX(PS_EXT_ORG_TBL.EFFDT)
 */
SELECT
    EXT.EXT_ORG_ID,
    EXT.EFFDT,
    EXT.DESCR50,
    A.IPEDS_CD,
    A.FICE_CD,
    A.ACT_CD,
    A.ATP_CD,
    A.LS_SCHOOL_TYPE,
    A.OFFERS_COURSES,
    A.CATALOG_ORG,
    VW.CITY,
    VW.STATE,
    VW.COUNTRY 
FROM PS_EXT_ORG_TBL EXT,
    PS_EXT_ORG_TBL_ADM A,
    PS_EXT_ORG_PRI_VW VW 
WHERE ...

Code Block

language	sql
title	PS_TRNSFR_COMP

/**
 * Where clause specifies: MAX(PS_TRNSFR_COMP.EFFDT)
 */

SELECT
    COMP.INSTITUTION,
    COMP.TRNSFR_SRC_ID,
    COMP.COMP_SUBJECT_AREA,
    CAST(COMP.EFFDT AS DATE) AS EFFDT,
    COMP.TRNSFR_EQVLNCY_CMP,
    COMP.DESCR,
    COMP.TRNSFR_PRIORITY,
    COMP.EXT_TERM_TYPE,
    COMP.TRNSFR_CRSE_FL,
    COMP.CNTNGNT_CRDT_FL,
    COMP.INP_CRSE_CNT,
    COMP.XS_CRSE_FL 
FROM PS_TRNSFR_COMP COMP
WHERE ...TRNSFR_EQ A_ED
					WHERE A.INSTITUTION = A_ED.INSTITUTION
						AND A.ACAD_PROG = A_ED.ACAD_PROG
						AND A.ACAD_PLAN = A_ED.ACAD_PLAN
						AND A.TRNSFR_SRC_ID = A_ED.TRNSFR_SRC_ID
						AND A_ED.EFFDT <= SYSDATE)
	AND A.EFF_STATUS = 'A'
	AND A.INSTITUTION = :INSTITUTION 
	-- AND A.ACAD_PROG = :ACADPROG -- optional
	AND A.TRNSFR_SRC_ID = B.TRNSFR_SRC_ID
	AND B.EFFDT = (	SELECT MAX(B_ED.EFFDT)
					FROM PS_TRNSFR_RULE B_ED
					WHERE B.INSTITUTION = B_ED.INSTITUTION
						AND B.TRNSFR_SRC_ID = B_ED.TRNSFR_SRC_ID
						AND B.TRNSFR_EQVLNCY = B_ED.TRNSFR_EQVLNCY
						AND B_ED.EFFDT <= A.EFFDT)
	AND A.CRS_TRNSFR_EQVLNCY = B.TRNSFR_EQVLNCY
	AND B.INSTITUTION = C.INSTITUTION
	AND B.TRNSFR_SRC_ID = C.TRNSFR_SRC_ID
	AND B.TRNSFR_EQVLNCY = C.TRNSFR_EQVLNCY
	AND B.EFFDT = C.EFFDT
	AND C.INSTITUTION = D.INSTITUTION
	AND C.TRNSFR_SRC_ID = D.TRNSFR_SRC_ID
	AND C.COMP_SUBJECT_AREA = D.COMP_SUBJECT_AREA
	AND D.EFFDT = (	SELECT MAX(D_ED.EFFDT)
					FROM PS_TRNSFR_SUBJ D_ED
					WHERE D.INSTITUTION = D_ED.INSTITUTION
						AND D.TRNSFR_SRC_ID = D_ED.TRNSFR_SRC_ID
						AND D.COMP_SUBJECT_AREA = D_ED.COMP_SUBJECT_AREA
						AND D_ED.EFFDT <= C.EFFDT)
ORDER BY A.DEF_RULE_SRC_ID, A.TRNSFR_SRC_ID

Code Block

language	sql
title	PS_CRSE_CATALOG

SELECT
    CRSE_ID,
    EFFDT,
    COURSE_TITLE_LONG,
    DESCR
FROM PS_CRSE_CATALOG
WHERE ...

Code Block

language	sql
title	PS_TRNSFR_TO

SELECT
    INSTITUTION,
    TRNSFR_EQVLNCY_CMP,
    TRNSFR_SRC_ID,
    CRSE_ID,
    EFFDT,
    COMP_SUBJECT_AREA,
    CRSE_OFFER_NBR
FROM PS_TRNSFR_TO
WHERE ...

Code Block

language	sql
title	PS_TRNSFR_TO_XS

SELECT
    INSTITUTION,
    TRNSFR_EQVLNCY_CMP,
    TRNSFR_SRC_ID,
    CRSE_ID,
    EFFDT,
    COMP_SUBJECT_AREA,
    CRSE_OFFER_NBR
FROM PS_TRNSFR_TO_XS
WHERE ...

Code Block

language	sql
title	PS_TRNSFR_FROM

SELECT
    INSTITUTION,
    TRNSFR_CMP_SEQ,
    TRNSFR_SRC_ID,
    COMP_SUBJECT_AREA,
    EFFDT,
    TRNSFR_EQVLNCY_CMP,
    SCHOOL_SUBJECT,
    SCHOOL_CRSE_NBR,
    BEGIN_DT,
    END_DT
FROM PS_TRNSFR_FROM
WHERE ...

Code Block

language	sql
title	PS_CRSE_OFFER

SELECT
    CRSE_ID,
    EFFDT,
    SUBJECT,
    CATALOG_NBR,
    CRSE_OFFER_NBR
FROM PS_CRSE_OFFER
WHERE ...

Code Block

language	sql
title	PS_SCHOOL_CRSE_TBL

SELECT
    EXT_ORG_ID,
    EFFDT,
    SCHOOL_SUBJECT,
    SCHOOL_CRSE_NBR,
    EFF_STATUS,
    DESCR
FROM PS_SCHOOL_CRSE_TBL
WHERE ...

SELECT A.INSTITUTION, A.TST_EQVLNCY, A.EFFDT, A.DESCR AS EQV_DESCR, B.TEST_ID, B.TEST_COMPONENT, B.TRNSFR_EQVLNCY_CMP, B.DESCR AS TST_DESCR, B.MIN_SCORE, B.MAX_SCORE, C.CRSE_ID, C.CRSE_OFFER_NBR, C.UNT_TAKEN, D.DESCR AS CRSE_DESCR, E.SUBJECT, E.CATALOG_NBR FROM PS_TST_CREDIT_RULE A, PS_TST_CREDIT_COMP B, PS_TST_CREDIT_CRSE C, PS_CRSE_CATALOG D, PS_CRSE_OFFER E WHERE ...

Code Block

language	sql
title	Standardized Exam Query: PS_TST_CREDIT_RULE, PS_TST_CREDIT_COMP, PS_TST_CREDIT_CRSE, PS_CRSE_CATALOG, PS_CRSE_OFFER

EXT_ORG_TBL, PS_EXT_ORG_TBL_ADM, PS_EXT_ORG_PRI_VW

/* IDS is a list of either PS_EXT_TRNSFR_EQ.TRNSFR_SRC_ID or PS_EXT_TRNSFR_EQ.DEF_RULE_SRC_ID */
SELECT DISTINCT
        EXT.EXT_ORG_ID,
        EXT.EFFDT,
        EXT.DESCR50,
        A.IPEDS_CD,
        A.FICE_CD,
        A.ACT_CD,
        A.ATP_CD,
        A.LS_SCHOOL_TYPE,
        A.OFFERS_COURSES,
        A.CATALOG_ORG,
        VW.CITY,
        VW.STATE,
        VW.COUNTRY
FROM PS_EXT_ORG_TBL EXT,
     PS_EXT_ORG_TBL_ADM A,
     PS_EXT_ORG_PRI_VW VW
WHERE
    EXT.EXT_ORG_ID = A.EXT_ORG_ID
	AND A.EXT_ORG_ID = VW.EXT_ORG_ID
	AND A.OFFERS_COURSES = 'Y'
	AND A.LS_SCHOOL_TYPE!='UKN'
	AND A.EFF_STATUS = 'A'
	AND VW.EFF_STATUS = 'A'
	AND EXISTS(	SELECT SUBJ.TRNSFR_SRC_ID
				FROM PS_TRNSFR_SUBJ SUBJ
				WHERE SUBJ.EFF_STATUS = 'A'
					AND SUBJ.INSTITUTION = :INSTITUTION
					AND SUBJ.TRNSFR_SRC_ID = NVL(TRIM(A.CATALOG_ORG), EXT.EXT_ORG_ID)
					AND SUBJ.EFFDT = (	SELECT MAX(SUBJ_ED.EFFDT)
										FROM PS_TRNSFR_SUBJ SUBJ_ED
										WHERE SUBJ.INSTITUTION = SUBJ_ED.INSTITUTION
											AND SUBJ.TRNSFR_SRC_ID = SUBJ_ED.TRNSFR_SRC_ID
											AND SUBJ.COMP_SUBJECT_AREA = SUBJ_ED.COMP_SUBJECT_AREA
											AND SUBJ_ED.EFFDT <= SYSDATE))
	AND EXT.EFFDT = (SELECT MAX(A_ED.EFFDT)
					 FROM PS_EXT_ORG_TBL A_ED
					 WHERE EXT.EXT_ORG_ID = A_ED.EXT_ORG_ID
						AND A_ED.EFFDT <= SYSDATE)
	AND A.EFFDT = (	SELECT MAX(A_ED2.EFFDT)
					FROM PS_EXT_ORG_TBL_ADM A_ED2
					WHERE A.EXT_ORG_ID = A_ED2.EXT_ORG_ID
						AND A_ED2.EFFDT <= SYSDATE)
	AND VW.EFFDT = (SELECT MAX(VW_ED.EFFDT)
					FROM PS_EXT_ORG_PRI_VW VW_ED
					WHERE VW.EXT_ORG_ID = VW_ED.EXT_ORG_ID
						AND VW_ED.EFFDT <= SYSDATE)
	AND EXT.EXT_ORG_ID IN (:IDS)
ORDER BY EXT.EXT_ORG_ID, EXT.EFFDT

Code Block

language	sql
title	PS_TRNSFR_COMP

/* ID is either PS_EXT_TRNSFR_EQ.TRNSFR_SRC_ID or PS_EXT_TRNSFR_EQ.DEF_RULE_SRC_ID */
SELECT DISTINCT
 	COMP.INSTITUTION,
	COMP.TRNSFR_SRC_ID, 
	COMP.COMP_SUBJECT_AREA, 
	CAST(COMP.EFFDT AS DATE) AS EFFDT, 
	COMP.TRNSFR_EQVLNCY_CMP, 
	COMP.DESCR, 
	COMP.TRNSFR_PRIORITY, 
	COMP.EXT_TERM_TYPE, 
	COMP.TRNSFR_CRSE_FL, 
	COMP.CNTNGNT_CRDT_FL, 
	COMP.INP_CRSE_CNT, 
	COMP.XS_CRSE_FL 
FROM PS_TRNSFR_COMP COMP 
WHERE COMP.TRNSFR_SRC_ID = :ID
	AND COMP.EFFDT <= SYSDATE 
	AND COMP.INSTITUTION = :INSTITUTION
	AND COMP.EFFDT = (	SELECT MAX(EFFDT) 
						FROM PS_TRNSFR_COMP MAXCOMP 
						WHERE MAXCOMP.EFFDT <= SYSDATE 
							AND MAXCOMP.TRNSFR_SRC_ID = :ID
							AND MAXCOMP.COMP_SUBJECT_AREA = COMP.COMP_SUBJECT_AREA 
							AND MAXCOMP.INSTITUTION = COMP.INSTITUTION) 
	AND EXISTS (SELECT * 
				FROM PS_TRNSFR_SUBJ SUBJ 
				WHERE SUBJ.EFF_STATUS = 'A' 
					AND SUBJ.INSTITUTION = COMP.INSTITUTION 
					AND SUBJ.TRNSFR_SRC_ID = COMP.TRNSFR_SRC_ID 
					AND SUBJ.COMP_SUBJECT_AREA = COMP.COMP_SUBJECT_AREA 
					AND SUBJ.EFFDT = COMP.EFFDT)

Code Block

language	sql
title	PS_CRSE_CATALOG

SELECT DISTINCT
 	CRSE_ID,
 	EFFDT,
 	COURSE_TITLE_LONG,
 	DESCR
FROM PS_CRSE_CATALOG
WHERE EFF_STATUS = 'A'
AND CRSE_ID IN (SELECT T.CRSE_ID FROM PS_TRNSFR_TO T
                 	WHERE T.TRNSFR_SRC_ID = :TRNSFRSRCID
					AND T.EFFDT = (	SELECT MAX(MAXCOMP.EFFDT)
										FROM PS_TRNSFR_COMP MAXCOMP
										WHERE MAXCOMP.EFFDT <= SYSDATE
											AND MAXCOMP.TRNSFR_SRC_ID = :TRNSFRSRCID
											AND MAXCOMP.COMP_SUBJECT_AREA = T.COMP_SUBJECT_AREA
											AND MAXCOMP.INSTITUTION = T.INSTITUTION)
				UNION SELECT XS.CRSE_ID FROM PS_TRNSFR_TO_XS XS
						WHERE XS.TRNSFR_SRC_ID = :TRNSFRSRCID
						AND XS.EFFDT = (SELECT MAX(MAXCOMP.EFFDT)
											FROM PS_TRNSFR_COMP MAXCOMP
											WHERE MAXCOMP.EFFDT <= SYSDATE
												AND MAXCOMP.TRNSFR_SRC_ID = :TRNSFRSRCID
												AND MAXCOMP.COMP_SUBJECT_AREA = XS.COMP_SUBJECT_AREA
												AND MAXCOMP.INSTITUTION = XS.INSTITUTION))

Code Block

language	sql
title	PS_TRNSFR_TO

SELECT DISTINCT
 	T.INSTITUTION,
	T.TRNSFR_EQVLNCY_CMP,
	T.TRNSFR_SRC_ID,
	T.CRSE_ID,
	T.EFFDT,
	T.COMP_SUBJECT_AREA,
	T.CRSE_OFFER_NBR,
	T.UNT_TAKEN
FROM PS_TRNSFR_TO T
 WHERE T.TRNSFR_SRC_ID = :TRNSFRSRCID
	AND T.EFFDT = (	SELECT MAX(MAXCOMP.EFFDT)
						FROM PS_TRNSFR_COMP MAXCOMP
						WHERE MAXCOMP.EFFDT <= SYSDATE
							AND MAXCOMP.TRNSFR_SRC_ID = :TRNSFRSRCID
							AND MAXCOMP.COMP_SUBJECT_AREA = T.COMP_SUBJECT_AREA
							AND MAXCOMP.INSTITUTION = T.INSTITUTION)

Code Block

language	sql
title	PS_TRNSFR_TO_XS

SELECT DISTINCT
 	XS.INSTITUTION,
	XS.TRNSFR_EQVLNCY_CMP,
	XS.TRNSFR_SRC_ID,
	XS.CRSE_ID,
	XS.EFFDT,
	XS.COMP_SUBJECT_AREA,
	XS.CRSE_OFFER_NBR,
	XS.UNT_TAKEN
FROM PS_TRNSFR_TO_XS XS
WHERE XS.TRNSFR_SRC_ID = :TRNSFRSRCID
AND XS.EFFDT = (SELECT MAX(MAXCOMP.EFFDT)
				FROM PS_TRNSFR_COMP MAXCOMP
				WHERE MAXCOMP.EFFDT <= SYSDATE
					AND MAXCOMP.TRNSFR_SRC_ID = :TRNSFRSRCID
					AND MAXCOMP.COMP_SUBJECT_AREA = XS.COMP_SUBJECT_AREA
					AND MAXCOMP.INSTITUTION = XS.INSTITUTION)

Code Block

language	sql
title	PS_TRNSFR_FROM

SELECT DISTINCT
 	F.INSTITUTION,
	F.TRNSFR_CMP_SEQ,
	F.TRNSFR_SRC_ID,
	F.COMP_SUBJECT_AREA,
	F.EFFDT,
	F.TRNSFR_EQVLNCY_CMP,
	F.SCHOOL_SUBJECT,
	F.SCHOOL_CRSE_NBR,
	F.WILDCARD_IND,
	F.BEGIN_DT,
	F.END_DT
FROM PS_TRNSFR_From F
WHERE F.TRNSFR_SRC_ID = :TRNSFRSRCID
	AND F.EFFDT = (	SELECT MAX(MAXCOMP.EFFDT)
						FROM PS_TRNSFR_COMP MAXCOMP
						WHERE MAXCOMP.EFFDT <= SYSDATE
							AND MAXCOMP.TRNSFR_SRC_ID = :TRNSFRSRCID
							AND MAXCOMP.COMP_SUBJECT_AREA = F.COMP_SUBJECT_AREA
							AND MAXCOMP.INSTITUTION = F.INSTITUTION)

Code Block

language	sql
title	PS_CRSE_OFFER

SELECT DISTINCT
 	A.CRSE_ID,
	A.EFFDT,
	A.SUBJECT,
	A.CATALOG_NBR,
	A.CRSE_OFFER_NBR,
	B.DESCR,
	B.DESCRSHORT,
	B.DESCRFORMAL 
FROM PS_CRSE_OFFER A,
 	 PS_SUBJECT_TBL B 
WHERE A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_CRSE_OFFER A_ED 
					WHERE A.CRSE_ID = A_ED.CRSE_ID 
					AND A_ED.EFFDT <= SYSDATE) 
    AND B.INSTITUTION = A.INSTITUTION 
    AND B.SUBJECT = A.SUBJECT 
    AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PS_SUBJECT_TBL B_ED 
					WHERE B.INSTITUTION = B_ED.INSTITUTION 
					AND B.SUBJECT = B_ED.SUBJECT 
					AND B_ED.EFFDT <= A.EFFDT) 
    AND A.CRSE_ID IN (SELECT T.CRSE_ID 
						FROM PS_TRNSFR_TO T 
						WHERE T.TRNSFR_SRC_ID = :TRNSFRSRCID 
								AND T.EFFDT = (SELECT MAX(MAXCOMP.EFFDT) 
												FROM PS_TRNSFR_COMP MAXCOMP 
												WHERE MAXCOMP.EFFDT <= SYSDATE 
													AND MAXCOMP.TRNSFR_SRC_ID = :TRNSFRSRCID 
													AND MAXCOMP.COMP_SUBJECT_AREA = T.COMP_SUBJECT_AREA 
													AND MAXCOMP.INSTITUTION = T.INSTITUTION) 
						UNION   SELECT XS.CRSE_ID 
								FROM PS_TRNSFR_TO_XS XS 
								WHERE XS.TRNSFR_SRC_ID = :TRNSFRSRCID 
									AND XS.EFFDT = (SELECT MAX(MAXCOMP.EFFDT) 
												FROM PS_TRNSFR_COMP MAXCOMP 
												WHERE MAXCOMP.EFFDT <= SYSDATE 
												AND MAXCOMP.TRNSFR_SRC_ID = :TRNSFRSRCID 
												AND MAXCOMP.COMP_SUBJECT_AREA = XS.COMP_SUBJECT_AREA 
												AND MAXCOMP.INSTITUTION = XS.INSTITUTION) )

Code Block

language	sql
title	PS_SCHOOL_CRSE_TBL

SELECT DISTINCT
 	EXT_ORG_ID,
	EFFDT,
	SCHOOL_SUBJECT,
	SCHOOL_CRSE_NBR,
	EFF_STATUS,
	DESCR
FROM PS_SCHOOL_CRSE_TBL
WHERE EXT_ORG_ID = :TRNSFRSRCID

Code Block

language	sql
title	Standardized Exam Query: PS_TST_CREDIT_RULE, PS_TST_CREDIT_COMP, PS_TST_CREDIT_CRSE, PS_CRSE_CATALOG, PS_CRSE_OFFER

SELECT DISTINCT
 	A.INSTITUTION, A.TST_EQVLNCY, A.EFFDT, A.DESCR AS EQV_DESCR,
	B.TEST_ID, B.TEST_COMPONENT, B.TRNSFR_EQVLNCY_CMP, B.DESCR AS TST_DESCR, B.MIN_SCORE, B.MAX_SCORE,
	C.CRSE_ID, C.UNT_TAKEN,
	D.DESCR AS CRSE_DESCR,
	E.SUBJECT, E.CATALOG_NBR, E.CRSE_OFFER_NBR
FROM PS_TST_CREDIT_RULE A,
 	 PS_TST_CREDIT_COMP B,
 	 PS_TST_CREDIT_CRSE C,
 	 PS_CRSE_CATALOG D,
 	 PS_CRSE_OFFER E
WHERE A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_TST_CREDIT_RULE A_ED
                 	WHERE A.INSTITUTION = A_ED.INSTITUTION
                	 AND A.TST_EQVLNCY = A_ED.TST_EQVLNCY
                 	 AND A_ED.EFFDT <= SYSDATE)
	AND A.INSTITUTION = B.INSTITUTION
	AND A.TST_EQVLNCY = B.TST_EQVLNCY
	AND A.EFFDT = B.EFFDT
	AND A.EFF_STATUS = 'A'
	AND B.INSTITUTION = C.INSTITUTION
	AND B.TST_EQVLNCY = C.TST_EQVLNCY
	AND B.TEST_ID = C.TEST_ID
	AND B.TEST_COMPONENT = C.TEST_COMPONENT
	AND B.TRNSFR_EQVLNCY_CMP = C.TRNSFR_EQVLNCY_CMP
	AND B.EFFDT = C.EFFDT
	AND C.CRSE_ID = D.CRSE_ID
	AND D.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_CRSE_CATALOG A_ED
                 	WHERE D.CRSE_ID = A_ED.CRSE_ID
                	AND A_ED.EFFDT <= SYSDATE)
	AND D.CRSE_ID = E.CRSE_ID
	AND D.EFFDT = E.EFFDT
	AND D.EFF_STATUS = 'A'

Assumptions about PeopleSoft Transfer Rules

...

QUICK LINKS

Page tree

Versions Compared

Old Version 54

New Version 55

Key

Assumptions about PeopleSoft Transfer Rules

QUICK LINKS

Page tree

Page History

Versions Compared

Old Version 54

New Version 55

Key

Assumptions about PeopleSoft Transfer Rules