SFA_ITEM_SFG_VW

(SQL View)
 Index Back

Financial Aid Item SF Grp View

Selects Financial Aid Item Type entries from the ITEM_SF table based on Item Type Group.

SELECT A.EMPLID , B.INSTITUTION , A.AID_YEAR , C.ITEM_TYPE_GROUP , A.ITEM_TYPE , A.ITEM_AMT , A.CURRENCY_CD FROM PS_ITEM_SF A ,PS_BUS_UNIT_TBL_SF B ,PS_ITEM_GROUP_TBL C ,PS_ITEM_GROUP_DTL D ,PSTREEDEFN E ,PSTREENODE F ,PSTREELEAF G WHERE A.SA_ID_TYPE = 'P' AND A.ITEM_TYPE_CD = 'F' AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND C.SETID = ( SELECT H.SETID FROM PS_SET_CNTRL_REC H WHERE H.SETCNTRLVALUE = B.INSTITUTION AND H.RECNAME = 'ITEM_GROUP_TBL') AND C.EFFDT = ( SELECT MAX (CC.EFFDT) FROM PS_ITEM_GROUP_TBL CC WHERE CC.SETID = C.SETID AND CC.ITEM_TYPE_GROUP = C.ITEM_TYPE_GROUP AND CC.EFFDT <= %CurrentDateIn) AND C.EFF_STATUS = 'A' AND D.SETID = C.SETID AND D.ITEM_TYPE_GROUP = C.ITEM_TYPE_GROUP AND D.EFFDT = C.EFFDT AND E.TREE_NAME = C.TREE_NAME AND E.EFFDT = ( SELECT MAX (EE.EFFDT) FROM PSTREEDEFN EE WHERE EE.SETID = E.SETID AND EE.TREE_NAME = E.TREE_NAME AND EE.EFFDT <= %CurrentDateIn) AND E.VALID_TREE = 'Y' AND F.SETID = E.SETID AND F.TREE_NAME = E.TREE_NAME AND F.EFFDT = E.EFFDT AND G.SETID = E.SETID AND G.TREE_NAME = E.TREE_NAME AND G.EFFDT = E.EFFDT AND E.SETID = C.TREE_NAME_SETID AND F.TREE_NODE = D.TREE_NODE AND G.TREE_NODE_NUM = F.TREE_NODE_NUM AND A.ITEM_TYPE >= G.RANGE_FROM AND A.ITEM_TYPE <= G.RANGE_TO

# 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 AID_YEAR Character(4) VARCHAR2(4) NOT NULL Aid Year
4 ITEM_TYPE_GROUP Character(10) VARCHAR2(10) NOT NULL Item Type Group
5 ITEM_TYPE Character(12) VARCHAR2(12) NOT NULL Item Type
6 ITEM_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Item Amount
7 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code