You are viewing an old version of this page. View the current version.
Compare with Current
View Page History
« Previous
Version 56
Next »
Home page for the PeopleSoft - Transfer Rule extractor aka PS T-Rex.
PS T-Rex is a java client program, you will install on a server of your choice. PS T-Rex is NOT installed into PeopleSoft. It is provided for download in zip and tar.gz file formats which contain the following:
- java libraries
- A Linux shell script and Windows batch file to call the java program
- A trex.properties file for school specific configuration
- Install instructions
PeopleSoft Extractor Install and Configure
To download PS T-Rex, you must log in with a CollegeSource support center account:
PeopleSoft Extractor Downloads
If you don't have a CollegeSource support center account, please use the following link to request one:
https://clients.collegesource.com/myprofile/RequestAccountServlet
PeopleSoft Table Access
SQL Select Statements
The selected equivalencies must have child rows in the PS_TRNSFR_COMP and PS_TRNSFR_SUBJ tables.
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.EFFDT = ( SELECT MAX(A_ED.EFFDT)
FROM PS_EXT_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
/* 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
/* 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)
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))
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)
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)
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)
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) )
SELECT DISTINCT
EXT_ORG_ID,
EFFDT,
SCHOOL_SUBJECT,
SCHOOL_CRSE_NBR,
EFF_STATUS,
DESCR
FROM PS_SCHOOL_CRSE_TBL
WHERE EXT_ORG_ID = :TRNSFRSRCID
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
When creating Transferology Transfer Rules from PeopleSoft Transfer Rules, these assumptions are made.
A "Does Not Transfer" Course is created as the Target (Internal Equivalent) Course when:
- The Transfer Component Transfer Course Flag is not Checked (PS_TRNSFR_COMP.TRNSFR_CRSE_FL = 'N').
- No PS_TRNSFR_TO (Internal Equivalent) Record exists in PeopleSoft for the Rule being processed.
- When the Transfer Course Flag is not checked, no Internal Equivalents can be created, but this is to handle bad data.
A PeopleSoft Transfer Rule is not created in Transferology, when:
- The Transfer Component Transfer Course Flag is Checked, PS_TRNSFR_TO (Internal Equivalent) Records exist, but the Course ID on the Internal Equivalent is blank (PS_TRNSFR_TO.CRSE_ID)
- The PS_TRNSFR_TO (Internal Equivalent) Record does not reference a valid PS_CRSE_OFFER Record, based on CRSE_OFFER_NBR and CRSE_ID:
- PS_TRNSFR_TO.CRSE_OFFER_NBR = PS_CRSE_OFFER.CRSE_OFFER_NBR AND PS_TRNSFR_TO.CRSE_ID = PS_CRSE_OFFER.CRSE_ID