SSF_CHRG_ITEMS

(SQL View)
 Index Back

View of ITEM_DUE_SF

This view gives the open items in ITEM_SF

SELECT C.INSTITUTION_SET , A.BUSINESS_UNIT ,A.COMMON_ID ,A.ITEM_NBR ,A.ITEM_TERM ,B.DUE_DT ,D.DESCR ,B.DUE_AMT ,b.due_amt - B.APPLIED_AMT ,' ' ,A.ACAD_CAREER ,A.STDNT_CAR_NBR ,A.ACAD_YEAR ,A.SESSION_CODE ,A.CLASS_NBR ,A.SEL_GROUP ,A.FEE_CD ,A.COURSE_LIST ,A.CRSE_ID ,A.ADM_APPL_NBR ,A.BILLING_CAREER ,A.ITEM_TYPE ,A.BILLING_DT ,A.REF1_DESCR ,A.ITEM_TYPE_CD ,A.CURRENCY_CD FROM PS_ITEM_SF A , PS_ITEM_DUE_SF B , PS_ISET_BU_DTL C , PS_ITEM_TYPE_BU_VW D WHERE A.SA_ID_TYPE = 'P' AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.COMMON_ID = B.COMMON_ID AND A.SA_ID_TYPE = B.SA_ID_TYPE AND A.ITEM_NBR = B.ITEM_NBR AND D.BUSINESS_UNIT = A.BUSINESS_UNIT AND D.ITEM_TYPE = A.ITEM_TYPE UNION ALL SELECT B.INSTITUTION_SET , B.BUSINESS_UNIT ,A.EMPLID ,' ' , A.ADMIT_TERM , F.DUE_DT , P.DESCR , F.DUE_AMT , F.DUE_AMT - F.RECEIVED_AMT - F.WAIVED_AMT , A.ACAD_PROG , A.ACAD_CAREER , 0 , ' ' , ' ' , 0 ,' ' ,' ' ,' ' ,' ' , A.ADM_APPL_NBR , A.ACAD_CAREER , ' ' ,%DateNull ,' ' ,'S' , F.CURRENCY_CD FROM PS_ADM_APPL_PROG A , PS_ISET_BU_DTL B , PS_BUS_UNIT_TBL_SF C , PS_ADM_APPL_DEP_PR D , PS_HOME_CAMPUS_TBL E , PS_ADM_APPL_DEP F , PS_ACAD_PROG_TBL P WHERE D.EMPLID = F.EMPLID AND D.ADM_APPL_NBR = F.ADM_APPL_NBR AND D.APPL_DEP_NBR = F.APPL_DEP_NBR AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.INSTITUTION = C.INSTITUTION AND A.EFFDT = ( SELECT MAX(A1.EFFDT ) FROM PS_ADM_APPL_PROG A1 WHERE A.EMPLID = A1.EMPLID AND A.ACAD_CAREER = A1.ACAD_CAREER AND A.STDNT_CAR_NBR = A1.STDNT_CAR_NBR AND A.ADM_APPL_NBR = A1.ADM_APPL_NBR AND A.APPL_PROG_NBR = A1.APPL_PROG_NBR) AND A.EFFSEQ = ( SELECT MAX(A2.EFFSEQ ) FROM PS_ADM_APPL_PROG A2 WHERE A.EMPLID = A2.EMPLID AND A.ACAD_CAREER = A2.ACAD_CAREER AND A.STDNT_CAR_NBR = A2.STDNT_CAR_NBR AND A.ADM_APPL_NBR = A2.ADM_APPL_NBR AND A.APPL_PROG_NBR = A2.APPL_PROG_NBR AND A.EFFDT = A2.EFFDT) AND (A.PROG_STATUS = 'PM' OR A.PROG_STATUS = 'AD' OR A.PROG_STATUS = 'AC') AND A.EMPLID = D.EMPLID AND A.ADM_APPL_NBR = D.ADM_APPL_NBR AND A.ACAD_PROG = D.ACAD_PROG AND A.INSTITUTION = E.INSTITUTION AND A.ACAD_PROG = E.ACAD_PROG AND E.EFFDT = ( SELECT MAX(E1.EFFDT) FROM PS_HOME_CAMPUS_TBL E1 WHERE E.INSTITUTION = E1.INSTITUTION AND E.ACAD_PROG = E1.ACAD_PROG AND E1.EFFDT <= %CurrentDateIn) AND A.CAMPUS = E.CAMPUS AND B.BUSINESS_UNIT = E.BUSINESS_UNIT AND A.Institution = P.Institution AND A.Acad_Prog = P.Acad_Prog AND P.Effdt = ( SELECT MAX(P1.Effdt) FROM PS_ACAD_PROG_TBL P1 WHERE P1.Institution = P.Institution AND P1.Acad_Prog = P.Acad_Prog AND P1.Effdt <= %CurrentDateIn) AND P.Eff_Status = 'A'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 INSTITUTION_SET Character(5) VARCHAR2(5) NOT NULL Institution Set
2 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: BUS_UNIT_TBL_SF

3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
4 ITEM_NBR Character(15) VARCHAR2(15) NOT NULL Item Nbr
5 STRM Character(4) VARCHAR2(4) NOT NULL Term
6 DUE_DT Date(10) DATE Due Date
7 DESCR Character(30) VARCHAR2(30) NOT NULL Description
8 SSF_AMOUNT Signed Number(19,2) DECIMAL(17,2) NOT NULL Amount
9 SSF_BALANCE Signed Number(19,2) DECIMAL(17,2) NOT NULL Balance
10 ACAD_PROG Character(5) VARCHAR2(5) NOT NULL Academic Program
11 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
12 STDNT_CAR_NBR Number(3,0) SMALLINT NOT NULL Student Career Nbr
13 ACAD_YEAR Character(4) VARCHAR2(4) NOT NULL Academic Year
14 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
15 CLASS_NBR Number(5,0) INTEGER NOT NULL Class Nbr
16 SEL_GROUP Character(10) VARCHAR2(10) NOT NULL Tuition Group
17 FEE_CD Character(6) VARCHAR2(6) NOT NULL Fee Code
18 COURSE_LIST Character(9) VARCHAR2(9) NOT NULL Course List
19 CRSE_ID Character(6) VARCHAR2(6) NOT NULL Course ID
20 ADM_APPL_NBR Character(8) VARCHAR2(8) NOT NULL Application Nbr
21 BILLING_CAREER Character(4) VARCHAR2(4) NOT NULL Billing Career
22 ITEM_TYPE Character(12) VARCHAR2(12) NOT NULL Item Type

Prompt Table: ITEM_TYPE_TBL

23 BILLING_DT Date(10) DATE Billing Date
24 REF1_DESCR Character(30) VARCHAR2(30) NOT NULL Reference Nbr
25 SSF_TXN_DTL_TYPE Character(1) VARCHAR2(1) NOT NULL Transaction Detail Type
A=Application Fees
C=Charges
S=Tuition Deposit Due
26 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code