SAD_CRM_FLT_DAT (SQL View)	Index Back
Adm Appl Data View SA/CRM Integration This view is used to filter the applicant data that will be published to CRM.

SAD_CRM_FLT_DAT

(SQL View)

Adm Appl Data View

SA/CRM Integration This view is used to filter the applicant data that will be published to CRM.

SELECT EMPLID ,ACAD_CAREER ,STDNT_CAR_NBR ,ADM_APPL_NBR ,INSTITUTION ,ADM_APPL_CTR ,ADMIT_TYPE ,FIN_AID_INTEREST ,HOUSING_INTEREST ,APPL_FEE_STATUS ,APPL_FEE_DT ,NOTIFICATION_PLAN ,REGION ,REGION_FROM ,RECRUITER_ID ,LAST_SCH_ATTEND ,ADM_CREATION_DT ,ADM_CREATION_BY ,ADM_UPDATED_DT ,ADM_UPDATED_BY ,ADM_APPL_COMPLETE ,ADM_APPL_DT ,ADM_APPL_CMPLT_DT ,GRADUATION_DT ,PRIOR_APPL ,APPL_FEE_TYPE ,ADM_APPL_METHOD ,APPL_FEE_AMT ,APPL_FEE_PAID ,CURRENCY_CD ,TENDER_CATEGORY ,ACADEMIC_LEVEL ,OVERRIDE_DEPOSIT ,EXT_ADM_APPL_NBR ,APP_FEE_STATUS ,APP_FEE_CALC_DTTM ,CUR_RT_TYPE ,RATE_MULT ,RATE_DIV ,ORIGNL_APPL_FEE_PD ,ORIGNL_CURRENCY_CD ,WAIVE_AMT FROM PS_ADM_APPL_DATA WHERE EMPLID IN ( SELECT EMPLID FROM PS_SAD_CRM_EMPLIDS)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
Prompt Table: PERSONAL_DATA

2 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
Prompt Table: STDNT_CAREER

3 STDNT_CAR_NBR Number(3,0) SMALLINT NOT NULL Student Career Nbr

4 ADM_APPL_NBR Character(8) VARCHAR2(8) NOT NULL Application Nbr
Default Value: 0

5 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution
Prompt Table: INST_CAR_SCTY

6 ADM_APPL_CTR Character(4) VARCHAR2(4) NOT NULL Application Center
Default Value: OPR_DEF_TBL_CS.ADM_APPL_CTR
Prompt Table: ADM_APPLCTR_SVW

7 ADMIT_TYPE Character(3) VARCHAR2(3) NOT NULL Admit Type
Default Value: OPR_DEF_TBL_CS.ADMIT_TYPE
Prompt Table: ADMIT_TYPE_VW

8 FIN_AID_INTEREST Character(1) VARCHAR2(1) NOT NULL Financial Aid Interest
Y/N Table Edit
Default Value: OPR_DEF_TBL_CS.FIN_AID_INTEREST

9 HOUSING_INTEREST Character(1) VARCHAR2(1) NOT NULL Housing Interest
C=Commuter
F=Off Campus Housing
H=On Campus Housing

10 APPL_FEE_STATUS Character(3) VARCHAR2(3) NOT NULL Application Fee Status
DEF=Deferred Payment
PEN=Pending
REC=Received
WRQ=Waiver Request
WVD=Waived
Default Value: PEN

11 APPL_FEE_DT Date(10) DATE Application Fee Date
Default Value: %date

12 NOTIFICATION_PLAN Character(4) VARCHAR2(4) NOT NULL Notification Plan
ERLY=Early Admission Decision
REG=Regular Admission Decision
ROLL=Rolling Admission
Default Value: REG

13 REGION Character(10) VARCHAR2(10) NOT NULL Region
Prompt Table: REGN_BASE_SRCH

14 REGION_FROM Character(1) VARCHAR2(1) NOT NULL Region from
A=Address
O=School
R=Region Tree

15 RECRUITER_ID Character(11) VARCHAR2(11) NOT NULL Recruiter ID
Prompt Table: RECRUITERS

16 LAST_SCH_ATTEND Character(11) VARCHAR2(11) NOT NULL Last School Attended
Prompt Table: EXT_ORGSCHL_VW

17 ADM_CREATION_DT Date(10) DATE NOT NULL Admission Data Creation Date
Default Value: %date

18 ADM_CREATION_BY Character(30) VARCHAR2(30) NOT NULL Admission Data Created By

19 ADM_UPDATED_DT Date(10) DATE Last Updated On

20 ADM_UPDATED_BY Character(30) VARCHAR2(30) NOT NULL Last Updated By

21 ADM_APPL_COMPLETE Character(1) VARCHAR2(1) NOT NULL Application Complete
Y/N Table Edit
Default Value: N

22 ADM_APPL_DT Date(10) DATE NOT NULL Application Date
Default Value: %date

23 ADM_APPL_CMPLT_DT Date(10) DATE Completed Date

24 GRADUATION_DT Date(10) DATE Graduation Date

25 PRIOR_APPL Character(1) VARCHAR2(1) NOT NULL Prior Application
Y/N Table Edit
Default Value: N

26 APPL_FEE_TYPE Character(3) VARCHAR2(3) NOT NULL Application Fee Type
INT=International
STN=Standard
Default Value: stn

27 ADM_APPL_METHOD Character(3) VARCHAR2(3) NOT NULL Application Method
DSK=Diskette
EDI=EDI
HRD=Hard Copy
OUA=Ontario University Application
SRV=Application Service
WWW=Web Application
Default Value: OPR_DEF_TBL_CS.ADM_APPL_METHOD

28 APPL_FEE_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Application Fee Amount

29 APPL_FEE_PAID Signed Number(18,2) DECIMAL(16,2) NOT NULL Application Fee Paid

30 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
Default Value: INSTALLATION.EXCHNG_TO_CURRENCY
Prompt Table: CURRENCY_CD_TBL

31 TENDER_CATEGORY Character(3) VARCHAR2(3) NOT NULL Tender Category
CC=Credit Card
CCI=Credit Card In
CCO=Credit Card Out
CK=Check
CKI=Check In
CKO=Check Out
CS=Cash
CSI=Cash In
CSO=Cash Out
DB=Debit Card
DBI=Debit Card In
DBO=Debit Card Out
DD=Direct Deposit
DDI=Direct Debit
DDO=Direct Deposit
EC=Electronic Check
TP=Third Party Check
TPI=Third Party Check In
WIR=Wire Transfer

32 ACADEMIC_LEVEL Character(3) VARCHAR2(3) NOT NULL Academic Level
00=Not Set
01=First Year
02=Second Year
03=Third Year
04=Fourth Year
05=Fifth Year
06=Sixth Year
10=Freshman
20=Sophomore
30=Junior
40=Senior
50=Post-Bacc Undergraduate
GR=Graduate
GRA=Graduate AUS
HON=Honours
IE=Industrial Experience
MAS=Masters
P1=Professional Year 1
P2=Professional Year 2
P3=Professional Year 3
P4=Professional Year 4
PHD=PhD
Default Value: OPR_DEF_TBL_CS.ACADEMIC_LEVEL

33 OVERRIDE_DEPOSIT Character(1) VARCHAR2(1) NOT NULL Override Deposit
Y/N Table Edit
Default Value: y

34 EXT_ADM_APPL_NBR Character(15) VARCHAR2(15) NOT NULL External Application Nbr

35 APP_FEE_STATUS Character(1) VARCHAR2(1) NOT NULL Application Fee Status
1=Pending
2=Calculated
3=Need Recalculation

36 APP_FEE_CALC_DTTM DateTime(26) TIMESTAMP Application Fee Calc DateTime

37 CUR_RT_TYPE Character(5) VARCHAR2(5) NOT NULL Currency Rate Type

38 RATE_MULT Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier
Default Value: 1

39 RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL Rate Divisor
Default Value: 1

40 ORIGNL_APPL_FEE_PD Signed Number(18,2) DECIMAL(16,2) NOT NULL Application Fee Paid

41 ORIGNL_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
Default Value: INSTALLATION.EXCHNG_TO_CURRENCY
Prompt Table: CURRENCY_CD_TBL

42 WAIVE_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Waive Amount

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID Prompt Table: PERSONAL_DATA
2	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 Prompt Table: STDNT_CAREER
3	STDNT_CAR_NBR	Number(3,0)	SMALLINT NOT NULL	Student Career Nbr
4	ADM_APPL_NBR	Character(8)	VARCHAR2(8) NOT NULL	Application Nbr Default Value: 0
5	INSTITUTION	Character(5)	VARCHAR2(5) NOT NULL	Academic Institution Prompt Table: INST_CAR_SCTY
6	ADM_APPL_CTR	Character(4)	VARCHAR2(4) NOT NULL	Application Center Default Value: OPR_DEF_TBL_CS.ADM_APPL_CTR Prompt Table: ADM_APPLCTR_SVW
7	ADMIT_TYPE	Character(3)	VARCHAR2(3) NOT NULL	Admit Type Default Value: OPR_DEF_TBL_CS.ADMIT_TYPE Prompt Table: ADMIT_TYPE_VW
8	FIN_AID_INTEREST	Character(1)	VARCHAR2(1) NOT NULL	Financial Aid Interest Y/N Table Edit Default Value: OPR_DEF_TBL_CS.FIN_AID_INTEREST
9	HOUSING_INTEREST	Character(1)	VARCHAR2(1) NOT NULL	Housing Interest C=Commuter F=Off Campus Housing H=On Campus Housing
10	APPL_FEE_STATUS	Character(3)	VARCHAR2(3) NOT NULL	Application Fee Status DEF=Deferred Payment PEN=Pending REC=Received WRQ=Waiver Request WVD=Waived Default Value: PEN
11	APPL_FEE_DT	Date(10)	DATE	Application Fee Date Default Value: %date
12	NOTIFICATION_PLAN	Character(4)	VARCHAR2(4) NOT NULL	Notification Plan ERLY=Early Admission Decision REG=Regular Admission Decision ROLL=Rolling Admission Default Value: REG
13	REGION	Character(10)	VARCHAR2(10) NOT NULL	Region Prompt Table: REGN_BASE_SRCH
14	REGION_FROM	Character(1)	VARCHAR2(1) NOT NULL	Region from A=Address O=School R=Region Tree
15	RECRUITER_ID	Character(11)	VARCHAR2(11) NOT NULL	Recruiter ID Prompt Table: RECRUITERS
16	LAST_SCH_ATTEND	Character(11)	VARCHAR2(11) NOT NULL	Last School Attended Prompt Table: EXT_ORGSCHL_VW
17	ADM_CREATION_DT	Date(10)	DATE NOT NULL	Admission Data Creation Date Default Value: %date
18	ADM_CREATION_BY	Character(30)	VARCHAR2(30) NOT NULL	Admission Data Created By
19	ADM_UPDATED_DT	Date(10)	DATE	Last Updated On
20	ADM_UPDATED_BY	Character(30)	VARCHAR2(30) NOT NULL	Last Updated By
21	ADM_APPL_COMPLETE	Character(1)	VARCHAR2(1) NOT NULL	Application Complete Y/N Table Edit Default Value: N
22	ADM_APPL_DT	Date(10)	DATE NOT NULL	Application Date Default Value: %date
23	ADM_APPL_CMPLT_DT	Date(10)	DATE	Completed Date
24	GRADUATION_DT	Date(10)	DATE	Graduation Date
25	PRIOR_APPL	Character(1)	VARCHAR2(1) NOT NULL	Prior Application Y/N Table Edit Default Value: N
26	APPL_FEE_TYPE	Character(3)	VARCHAR2(3) NOT NULL	Application Fee Type INT=International STN=Standard Default Value: stn
27	ADM_APPL_METHOD	Character(3)	VARCHAR2(3) NOT NULL	Application Method DSK=Diskette EDI=EDI HRD=Hard Copy OUA=Ontario University Application SRV=Application Service WWW=Web Application Default Value: OPR_DEF_TBL_CS.ADM_APPL_METHOD
28	APPL_FEE_AMT	Signed Number(18,2)	DECIMAL(16,2) NOT NULL	Application Fee Amount
29	APPL_FEE_PAID	Signed Number(18,2)	DECIMAL(16,2) NOT NULL	Application Fee Paid
30	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code Default Value: INSTALLATION.EXCHNG_TO_CURRENCY Prompt Table: CURRENCY_CD_TBL
31	TENDER_CATEGORY	Character(3)	VARCHAR2(3) NOT NULL	Tender Category CC=Credit Card CCI=Credit Card In CCO=Credit Card Out CK=Check CKI=Check In CKO=Check Out CS=Cash CSI=Cash In CSO=Cash Out DB=Debit Card DBI=Debit Card In DBO=Debit Card Out DD=Direct Deposit DDI=Direct Debit DDO=Direct Deposit EC=Electronic Check TP=Third Party Check TPI=Third Party Check In WIR=Wire Transfer
32	ACADEMIC_LEVEL	Character(3)	VARCHAR2(3) NOT NULL	Academic Level 00=Not Set 01=First Year 02=Second Year 03=Third Year 04=Fourth Year 05=Fifth Year 06=Sixth Year 10=Freshman 20=Sophomore 30=Junior 40=Senior 50=Post-Bacc Undergraduate GR=Graduate GRA=Graduate AUS HON=Honours IE=Industrial Experience MAS=Masters P1=Professional Year 1 P2=Professional Year 2 P3=Professional Year 3 P4=Professional Year 4 PHD=PhD Default Value: OPR_DEF_TBL_CS.ACADEMIC_LEVEL
33	OVERRIDE_DEPOSIT	Character(1)	VARCHAR2(1) NOT NULL	Override Deposit Y/N Table Edit Default Value: y
34	EXT_ADM_APPL_NBR	Character(15)	VARCHAR2(15) NOT NULL	External Application Nbr
35	APP_FEE_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Application Fee Status 1=Pending 2=Calculated 3=Need Recalculation
36	APP_FEE_CALC_DTTM	DateTime(26)	TIMESTAMP	Application Fee Calc DateTime
37	CUR_RT_TYPE	Character(5)	VARCHAR2(5) NOT NULL	Currency Rate Type
38	RATE_MULT	Signed Number(17,8)	DECIMAL(15,8) NOT NULL	Rate Multiplier Default Value: 1
39	RATE_DIV	Number(16,8)	DECIMAL(15,8) NOT NULL	Rate Divisor Default Value: 1
40	ORIGNL_APPL_FEE_PD	Signed Number(18,2)	DECIMAL(16,2) NOT NULL	Application Fee Paid
41	ORIGNL_CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code Default Value: INSTALLATION.EXCHNG_TO_CURRENCY Prompt Table: CURRENCY_CD_TBL
42	WAIVE_AMT	Signed Number(18,2)	DECIMAL(16,2) NOT NULL	Waive Amount