SSF_SL_ITEM_VW

(SQL View)
 Index Back

StudyLink Item View

A view to extract all charges payable by the StudyLink item type.

SELECT DISTINCT Z.BUSINESS_UNIT , Z.COMMON_ID , Z.ITEM_NBR , Z.ITEM_TYPE , Z.ITEM_TERM , Z.ITEM_AMT , Z.APPLIED_AMT , Z.CLASS_NBR , Z.SESSION_CODE , Z.ACCOUNT_TYPE_SF , Z.FEE_CD , Z.CRSE_ID , M.CHARGE_PRIORITY FROM PSTREEDEFN A , PSTREENODE B , PSTREELEAF C , PS_ITEM_SF Z , PS_PMT_CHRG_PRIOR Y , PS_PMT_CHRG_TBL M , PS_BUS_UNIT_TBL_SF BU WHERE Z.SA_ID_TYPE = 'P' AND Z.BUSINESS_UNIT = BU.BUSINESS_UNIT AND M.SETID = ( SELECT D.SETID FROM PS_SET_CNTRL_REC D WHERE D.SETCNTRLVALUE = Z.BUSINESS_UNIT AND D.RECNAME = 'PMT_CHRG_TBL') AND M.EFFDT = ( SELECT MAX(T.EFFDT) FROM PS_PMT_CHRG_TBL T WHERE T.SETID = M.SETID AND T.CHARGE_PRIORITY = M.CHARGE_PRIORITY AND T.EFFDT <= %CurrentDateIn ) AND M.EFF_STATUS = 'A' AND A.TREE_NAME = M.TREE_NAME AND A.SETID = ( SELECT D1.SETID FROM PS_SET_CNTRL_TREE D1 WHERE D1.SETCNTRLVALUE = Z.BUSINESS_UNIT AND D1.TREE_NAME = M.TREE_NAME) AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PSTREEDEFN A1 WHERE A.SETID = A1.SETID AND A.TREE_NAME = A1.TREE_NAME AND A1.EFFDT <= %CurrentDateIn) AND A.VALID_TREE = 'Y' AND B.SETID = A.SETID AND B.TREE_NAME = A.TREE_NAME AND B.EFFDT = A.EFFDT AND C.SETID = B.SETID AND C.EFFDT = B.EFFDT AND C.TREE_NAME = B.TREE_NAME AND C.TREE_BRANCH = B.TREE_BRANCH AND C.TREE_NODE_NUM >= B.TREE_NODE_NUM AND C.TREE_NODE_NUM <= B.TREE_NODE_NUM_END AND Z.ITEM_TYPE >= C.RANGE_FROM AND Z.ITEM_TYPE <= C.RANGE_TO AND Z.ITEM_TYPE_CD = 'C' AND Z.ITEM_AMT > 0 AND Y.SETID = M.SETID AND Y.CHARGE_PRIORITY = M.CHARGE_PRIORITY AND Y.EFFDT = M.EFFDT AND Y.TREE_NODE = B.TREE_NODE

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 COMMON_ID Character(11) VARCHAR2(11) NOT NULL Common ID to store Personal ID / Ext Org ID value
3 ITEM_NBR Character(15) VARCHAR2(15) NOT NULL Item Nbr
4 ITEM_TYPE Character(12) VARCHAR2(12) NOT NULL Item Type
5 ITEM_TERM Character(4) VARCHAR2(4) NOT NULL Item Term
6 ITEM_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Item Amount
7 APPLIED_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Applied Amount
8 CLASS_NBR Number(5,0) INTEGER NOT NULL Class Nbr
9 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
10 ACCOUNT_TYPE_SF Character(3) VARCHAR2(3) NOT NULL Account Type
11 FEE_CD Character(6) VARCHAR2(6) NOT NULL Fee Code
12 CRSE_ID Character(6) VARCHAR2(6) NOT NULL Course ID
13 CHARGE_PRIORITY Character(8) VARCHAR2(8) NOT NULL Charge Priority List