SSR_APTSY_TXN_V

(SQL View)
 Index Back

APT Sync View 2

When data is in tracking but no longer in student enroll - this will be a drop action

SELECT e2.emplid , e2.institution , e2.acad_career , e2.strm , e2.ssr_apt_instance , e2.class_nbr , e2.model_nbr , e2.trnsfr_eqvlncy_grp , e2.trnsfr_eqvlncy_seq , e2.crse_id , e2.crse_offer_nbr , e2.crs_topic_id , e2.session_code , e2.acad_prog , 'E' AS ssr_aptsync_source , e2.ssr_aptsyn_actn_x , e2.ssr_aptsync_action , %NumToChar(e2.class_nbr) AS ssr_aptsy_cls_nbr , %NumToChar(e2.model_nbr) AS ssr_aptsy_mdl_nbr , %NumToChar(e2.trnsfr_eqvlncy_grp) AS ssr_aptsy_trn_grp , %NumToChar(e2.trnsfr_eqvlncy_seq) AS ssr_aptsy_trn_seq FROM ps_ssr_aptsy_en2_v e2 UNION SELECT e3.emplid , e3.institution , e3.acad_career , e3.strm , e3.ssr_apt_instance , e3.class_nbr , e3.model_nbr , e3.trnsfr_eqvlncy_grp , e3.trnsfr_eqvlncy_seq , e3.crse_id , e3.crse_offer_nbr , e3.crs_topic_id , e3.session_code , e3.acad_prog , 'E' AS ssr_aptsync_source , e3.ssr_aptsyn_actn_x , e3.ssr_aptsync_action , %NumToChar(e3.class_nbr) AS ssr_aptsy_cls_nbr , %NumToChar(e3.model_nbr) AS ssr_aptsy_mdl_nbr , %NumToChar(e3.trnsfr_eqvlncy_grp) AS ssr_aptsy_trn_grp , %NumToChar(e3.trnsfr_eqvlncy_seq) AS ssr_aptsy_trn_seq FROM PS_SSR_APTSY_EN3_V e3 UNION SELECT e4.emplid , e4.institution , e4.acad_career , e4.strm , e4.ssr_apt_instance , e4.class_nbr , e4.model_nbr , e4.trnsfr_eqvlncy_grp , e4.trnsfr_eqvlncy_seq , e4.crse_id , e4.crse_offer_nbr , e4.crs_topic_id , e4.session_code , e4.acad_prog , 'E' AS ssr_aptsync_source , e4.ssr_aptsyn_actn_x , e4.ssr_aptsync_action , %NumToChar(e4.class_nbr) AS ssr_aptsy_cls_nbr , %NumToChar(e4.model_nbr) AS ssr_aptsy_mdl_nbr , %NumToChar(e4.trnsfr_eqvlncy_grp) AS ssr_aptsy_trn_grp , %NumToChar(e4.trnsfr_eqvlncy_seq) AS ssr_aptsy_trn_seq FROM ps_ssr_aptsy_en4_v e4 UNION SELECT c2.emplid , c2.institution , c2.acad_career , c2.strm , c2.ssr_apt_instance , c2.class_nbr , c2.model_nbr , c2.trnsfr_eqvlncy_grp , c2.trnsfr_eqvlncy_seq , c2.crse_id , c2.crse_offer_nbr , c2.crs_topic_id , c2.session_code , c2.acad_prog , 'C' AS ssr_aptsync_source , c2.ssr_aptsyn_actn_x , c2.ssr_aptsync_action , %NumToChar(c2.class_nbr) AS ssr_aptsy_cls_nbr , %NumToChar(c2.model_nbr) AS ssr_aptsy_mdl_nbr , %NumToChar(c2.trnsfr_eqvlncy_grp) AS ssr_aptsy_trn_grp , %NumToChar(c2.trnsfr_eqvlncy_seq) AS ssr_aptsy_trn_seq FROM ps_ssr_aptsy_ct2_v c2 UNION SELECT c3.emplid , c3.institution , c3.acad_career , c3.strm , c3.ssr_apt_instance , c3.class_nbr , c3.model_nbr , c3.trnsfr_eqvlncy_grp , c3.trnsfr_eqvlncy_seq , c3.crse_id , c3.crse_offer_nbr , c3.crs_topic_id , c3.session_code , c3.acad_prog , 'C' AS ssr_aptsync_source , c3.ssr_aptsyn_actn_x , c3.ssr_aptsync_action , %NumToChar(c3.class_nbr) AS ssr_aptsy_cls_nbr , %NumToChar(c3.model_nbr) AS ssr_aptsy_mdl_nbr , %NumToChar(c3.trnsfr_eqvlncy_grp) AS ssr_aptsy_trn_grp , %NumToChar(c3.trnsfr_eqvlncy_seq) AS ssr_aptsy_trn_seq FROM ps_ssr_aptsy_ct3_v c3 UNION SELECT c4.emplid , c4.institution , c4.acad_career , c4.strm , c4.ssr_apt_instance , c4.class_nbr , c4.model_nbr , c4.trnsfr_eqvlncy_grp , c4.trnsfr_eqvlncy_seq , c4.crse_id , c4.crse_offer_nbr , c4.crs_topic_id , c4.session_code , c4.acad_prog , 'C' AS ssr_aptsync_source , c4.ssr_aptsyn_actn_x , c4.ssr_aptsync_action , %NumToChar(c4.class_nbr) AS ssr_aptsy_cls_nbr , %NumToChar(c4.model_nbr) AS ssr_aptsy_mdl_nbr , %NumToChar(c4.trnsfr_eqvlncy_grp) AS ssr_aptsy_trn_grp , %NumToChar(c4.trnsfr_eqvlncy_seq) AS ssr_aptsy_trn_seq FROM ps_ssr_aptsy_ct4_v c4 UNION SELECT t2.emplid , t2.institution , t2.acad_career , t2.strm , t2.ssr_apt_instance , t2.class_nbr , t2.model_nbr , t2.trnsfr_eqvlncy_grp , t2.trnsfr_eqvlncy_seq , t2.crse_id , t2.crse_offer_nbr , t2.crs_topic_id , t2.session_code , t2.acad_prog , 'T' AS ssr_aptsync_source , t2.ssr_aptsyn_actn_x , t2.ssr_aptsync_action , %NumToChar(t2.class_nbr) AS ssr_aptsy_cls_nbr , %NumToChar(t2.model_nbr) AS ssr_aptsy_mdl_nbr , %NumToChar(t2.trnsfr_eqvlncy_grp) AS ssr_aptsy_trn_grp , %NumToChar(t2.trnsfr_eqvlncy_seq) AS ssr_aptsy_trn_seq FROM ps_ssr_aptsy_tt2_v t2 UNION SELECT t3.emplid , t3.institution , t3.acad_career , t3.strm , t3.ssr_apt_instance , t3.class_nbr , t3.model_nbr , t3.trnsfr_eqvlncy_grp , t3.trnsfr_eqvlncy_seq , t3.crse_id , t3.crse_offer_nbr , t3.crs_topic_id , t3.session_code , t3.acad_prog , 'T' AS ssr_aptsync_source , t3.ssr_aptsyn_actn_x , t3.ssr_aptsync_action , %NumToChar(t3.class_nbr) AS ssr_aptsy_cls_nbr , %NumToChar(t3.model_nbr) AS ssr_aptsy_mdl_nbr , %NumToChar(t3.trnsfr_eqvlncy_grp) AS ssr_aptsy_trn_grp , %NumToChar(t3.trnsfr_eqvlncy_seq) AS ssr_aptsy_trn_seq FROM ps_ssr_aptsy_tt3_v t3 UNION SELECT t4.emplid , t4.institution , t4.acad_career , t4.strm , t4.ssr_apt_instance , t4.class_nbr , t4.model_nbr , t4.trnsfr_eqvlncy_grp , t4.trnsfr_eqvlncy_seq , t4.crse_id , t4.crse_offer_nbr , t4.crs_topic_id , t4.session_code , t4.acad_prog , 'T' AS ssr_aptsync_source , t4.ssr_aptsyn_actn_x , t4.ssr_aptsync_action , %NumToChar(t4.class_nbr) AS ssr_aptsy_cls_nbr , %NumToChar(t4.model_nbr) AS ssr_aptsy_mdl_nbr , %NumToChar(t4.trnsfr_eqvlncy_grp) AS ssr_aptsy_trn_grp , %NumToChar(t4.trnsfr_eqvlncy_seq) AS ssr_aptsy_trn_seq FROM ps_ssr_aptsy_tt4_v t4 UNION SELECT o2.emplid , o2.institution , o2.acad_career , o2.strm , o2.ssr_apt_instance , o2.class_nbr , o2.model_nbr , o2.trnsfr_eqvlncy_grp , o2.trnsfr_eqvlncy_seq , o2.crse_id , o2.crse_offer_nbr , o2.crs_topic_id , o2.session_code , o2.acad_prog , 'O' AS ssr_aptsync_source , o2.ssr_aptsyn_actn_x , o2.ssr_aptsync_action , %NumToChar(o2.class_nbr) AS ssr_aptsy_cls_nbr , %NumToChar(o2.model_nbr) AS ssr_aptsy_mdl_nbr , %NumToChar(o2.trnsfr_eqvlncy_grp) AS ssr_aptsy_trn_grp , %NumToChar(o2.trnsfr_eqvlncy_seq) AS ssr_aptsy_trn_seq FROM ps_ssr_aptsy_ot2_v o2 UNION SELECT o3.emplid , o3.institution , o3.acad_career , o3.strm , o3.ssr_apt_instance , o3.class_nbr , o3.model_nbr , o3.trnsfr_eqvlncy_grp , o3.trnsfr_eqvlncy_seq , o3.crse_id , o3.crse_offer_nbr , o3.crs_topic_id , o3.session_code , o3.acad_prog , 'O' AS ssr_aptsync_source , o3.ssr_aptsyn_actn_x , o3.ssr_aptsync_action , %NumToChar(o3.class_nbr) AS ssr_aptsy_cls_nbr , %NumToChar(o3.model_nbr) AS ssr_aptsy_mdl_nbr , %NumToChar(o3.trnsfr_eqvlncy_grp) AS ssr_aptsy_trn_grp , %NumToChar(o3.trnsfr_eqvlncy_seq) AS ssr_aptsy_trn_seq FROM ps_ssr_aptsy_ot3_v o3 UNION SELECT o4.emplid , o4.institution , o4.acad_career , o4.strm , o4.ssr_apt_instance , o4.class_nbr , o4.model_nbr , o4.trnsfr_eqvlncy_grp , o4.trnsfr_eqvlncy_seq , o4.crse_id , o4.crse_offer_nbr , o4.crs_topic_id , o4.session_code , o4.acad_prog , 'O' AS ssr_aptsync_source , o4.ssr_aptsyn_actn_x , o4.ssr_aptsync_action , %NumToChar(o4.class_nbr) AS ssr_aptsy_cls_nbr , %NumToChar(o4.model_nbr) AS ssr_aptsy_mdl_nbr , %NumToChar(o4.trnsfr_eqvlncy_grp) AS ssr_aptsy_trn_grp , %NumToChar(o4.trnsfr_eqvlncy_seq) AS ssr_aptsy_trn_seq FROM ps_ssr_aptsy_ot4_v o4

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution
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
5 SSR_APT_INSTANCE Number(3,0) SMALLINT NOT NULL Academic Progress Tracker Instance Number
6 CLASS_NBR Number(5,0) INTEGER NOT NULL Class Nbr
7 MODEL_NBR Number(3,0) SMALLINT NOT NULL Transfer Model Nbr
8 TRNSFR_EQVLNCY_GRP Number(3,0) SMALLINT NOT NULL Transfer Equivalency Group
9 TRNSFR_EQVLNCY_SEQ Number(3,0) SMALLINT NOT NULL Transfer Equivalency Seq
10 CRSE_ID Character(6) VARCHAR2(6) NOT NULL Course ID
11 CRSE_OFFER_NBR Number(2,0) SMALLINT NOT NULL Course Offering Nbr
12 CRS_TOPIC_ID Number(3,0) SMALLINT NOT NULL Course Topic ID
13 SESSION_CODE Character(3) VARCHAR2(3) NOT NULL Session
1=Regular Academic Session
10W=10 Week
12W=Twelve Week
4W1=Four Week - First
4W2=Four Week - Second
4W3=Four Week - Third
4W4=Four Week - Fourth
6W1=Six Week - First
6W2=Six Week - Second
8W1=Eight Week - First
8W2=Eight Week - Second
A11=Medical Period 1, 4 Weeks
A12=Medical Period 1, 8 Weeks
A13=Medical Pd 1, 12 Weeks
A21=Medical Period 2, 4 Weeks
A22=Medical Period 2, 8 Weeks
A23=Medical Period 2, 12 Week
FYR=Full Year
MIN=Mini Session
OEE=Open Entry/Open Exit
SM1=Semester 1
SM2=Semester 2
14 ACAD_PROG Character(5) VARCHAR2(5) NOT NULL Academic Program
15 SSR_APTSYNC_SOURCE Character(1) VARCHAR2(1) NOT NULL Source Type
C=Course Transfer Credit
E=Enrollment
O=Other Transfer Credit
T=Test Transfer Credit
16 SSR_APTSYN_ACTN_X Character(1) VARCHAR2(1) NOT NULL Include Previously Unmatched
17 SSR_APTSYNC_ACTION Character(2) VARCHAR2(2) NOT NULL APT Sync Request Action
CD=Course Credit Delete
CP=Course Credit Post
CU=Course Credit Unpost
D=Drop
DD=Drop Delete
E=Enroll
OD=Other Credit Delete
OP=Other Credit Post
OU=Other Credit Unpost
TD=Test Credit Delete
TP=Test Credit Post
TU=Test Credit Unpost
18 SSR_APTSY_CLS_NBR Character(5) VARCHAR2(5) NOT NULL Class Nbr
19 SSR_APTSY_MDL_NBR Character(3) VARCHAR2(3) NOT NULL Transfer Model Nbr
20 SSR_APTSY_TRN_GRP Character(3) VARCHAR2(3) NOT NULL Transfer Equivalency Group
21 SSR_APTSY_TRN_SEQ Character(3) VARCHAR2(3) NOT NULL Transfer Equivalency Seq