SSF_ISET_ITD_VW

(SQL View)
Index Back

Item SF View

This view is used to identify charges that can be paid given an Institution Set.

SELECT DISTINCT Z.COMMON_ID , Z.BUSINESS_UNIT , D.CHARGE_PRIORITY , Z.ITEM_NBR , Y.DUE_AMT , Z.ITEM_TYPE , Z.APPLIED_AMT , Z.ITEM_TERM , Z.ACCOUNT_TERM , Z.ACCOUNT_NBR , Z.ITEM_BALANCE , Z.CURRENCY_CD , Y.DUE_AMT - Y.APPLIED_AMT , Y.DUE_DT FROM PS_ITEM_SF Z ,PSTREEDEFN A ,PSTREENODE B ,PSTREELEAF C ,PS_PMT_CHRG_TBL D ,PS_PMT_CHRG_PRIOR E ,PS_BUS_UNIT_TBL_SF F ,PS_ITEM_DUE_SF Y WHERE Z.BUSINESS_UNIT = F.BUSINESS_UNIT AND Z.BUSINESS_UNIT = Y.BUSINESS_UNIT AND Z.COMMON_ID = Y.COMMON_ID AND Z.SA_ID_TYPE = Y.SA_ID_TYPE AND Z.ITEM_NBR = Y.ITEM_NBR AND Y.DUE_AMT > 0 AND F.SF_ENABLE_WEB_PAY = 'Y' AND A.SETID = B.SETID AND A.SETID = C.SETID AND A.SETID = D.SETID AND D.CHARGE_PRIORITY = E.CHARGE_PRIORITY AND D.SETID = E.SETID AND D.SETID = ( SELECT D1.SETID FROM PS_SET_CNTRL_REC D1 WHERE D1.SETCNTRLVALUE = F.INSTITUTION AND D1.RECNAME = 'PMT_CHRG_TBL') AND D.EFFDT = E.EFFDT AND D.EFF_STATUS = 'A' AND D.EFFDT = ( SELECT MAX(D2.EFFDT) FROM PS_PMT_CHRG_TBL D2 WHERE D.SETID = D2.SETID AND D.CHARGE_PRIORITY = D2.CHARGE_PRIORITY AND D2.EFFDT <= %CurrentDateIn) AND B.TREE_NODE = E.TREE_NODE AND A.VALID_TREE = 'Y' AND A.TREE_NAME = B.TREE_NAME AND A.TREE_NAME = C.TREE_NAME AND A.EFFDT = B.EFFDT AND A.EFFDT = C.EFFDT AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PSTREEDEFN A1 WHERE A1.SETID = A.SETID AND A1.TREE_NAME = A.TREE_NAME AND A1.EFFDT <= %CurrentDateIn) AND C.TREE_NODE_NUM = B.TREE_NODE_NUM AND Z.ITEM_TYPE >= C.RANGE_FROM AND Z.ITEM_TYPE <= C.RANGE_TO AND (Z.ITEM_AMT >= 0) AND Z.ITEM_AMT - Z.APPLIED_AMT > 0 AND Z.ITEM_AMT > 0 AND (Z.ITEM_STATUS = 'A' OR Z.ITEM_STATUS = ' ')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 COMMON_ID Character(11) VARCHAR2(11) NOT NULL Common ID to store Personal ID / Ext Org ID value
2 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
3 CHARGE_PRIORITY Character(8) VARCHAR2(8) NOT NULL Charge Priority List
4 ITEM_NBR Character(15) VARCHAR2(15) NOT NULL Item Nbr
5 ITEM_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Item Amount
6 ITEM_TYPE Character(12) VARCHAR2(12) NOT NULL Item Type
7 APPLIED_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Applied Amount
8 ITEM_TERM Character(4) VARCHAR2(4) NOT NULL Item Term
9 ACCOUNT_TERM Character(4) VARCHAR2(4) NOT NULL Account Term
10 ACCOUNT_NBR Character(10) VARCHAR2(10) NOT NULL Account Nbr
11 ITEM_BALANCE Signed Number(18,2) DECIMAL(16,2) NOT NULL Item Balance
12 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
13 BALANCE_REMAIN Signed Number(18,2) DECIMAL(16,2) NOT NULL Remaining Balance
14 DUE_DT Date(10) DATE Expected Birth Date