SSF_ITM_OFF_VW(SQL View) |
Index Back |
|---|---|
Item Offset ViewThis view returns all the offsets (credits) available for a student for a particular payment plan. |
| SELECT DISTINCT Z.BUSINESS_UNIT ,Z.COMMON_ID ,X.CONTRACT_NUM ,Z.ACCOUNT_NBR ,Z.ACCOUNT_TERM ,Z.ACCOUNT_TYPE_SF ,Z.ITEM_NBR ,Z.ITEM_TERM ,Z.ITEM_AMT ,Z.item_balance ,Z.ITEM_TYPE ,Z.ITEM_TYPE_CD FROM PS_ITEM_SF Z ,PSTREEDEFN A ,PSTREENODE B ,PSTREELEAF C ,PS_ITEM_GROUP_TBL D ,PS_ITEM_GROUP_DTL E ,PS_BUS_UNIT_TBL_SF F ,PS_ACCT_TYP_TBL_SF G ,PS_TP_CONTRACT X WHERE Z.BUSINESS_UNIT = X.BUSINESS_UNIT AND Z.SA_ID_TYPE = 'P' AND Z.ITEM_TERM >= X.TERM_FROM_OFF AND Z.ITEM_TERM <= X.TERM_TO_OFF AND Z.ITEM_EFFECTIVE_DT >= x.add_off_dt AND Z.ITEM_EFFECTIVE_DT <= x.adj_off_dt AND D.ITEM_TYPE_GROUP = X.ITEM_TYPE_GRP_OFF AND Z.BUSINESS_UNIT = F.BUSINESS_UNIT AND Z.ACCOUNT_TYPE_SF = G.ACCOUNT_TYPE_SF AND A.SETID=B.SETID AND A.SETID=C.SETID AND A.SETID = D.TREE_NAME_SETID AND D.ITEM_TYPE_GROUP =E.ITEM_TYPE_GROUP 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 = 'ITEM_GROUP_TBL') AND D.EFFDT = E.EFFDT AND D.EFF_STATUS='A' AND D.EFFDT = ( SELECT MAX(D2.EFFDT) FROM PS_ITEM_GROUP_TBL D2 WHERE D.SETID = D2.SETID AND D.ITEM_TYPE_GROUP = D2.ITEM_TYPE_GROUP 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 G.SETID = ( SELECT G1.SETID FROM PS_SET_CNTRL_REC G1 WHERE G1.SETCNTRLVALUE = F.BUSINESS_UNIT AND G1.RECNAME = 'ACCT_TYP_TBL_SF') AND ( Z.ITEM_TYPE_CD = 'P' OR Z.ITEM_TYPE_CD = 'F') |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 2 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 3 | CONTRACT_NUM |
Character(25) | VARCHAR2(25) NOT NULL | Contract Number |
| 4 | ACCOUNT_NBR | Character(10) | VARCHAR2(10) NOT NULL | Account Nbr |
| 5 | ACCOUNT_TERM | Character(4) | VARCHAR2(4) NOT NULL | Account Term |
| 6 | ACCOUNT_TYPE_SF | Character(3) | VARCHAR2(3) NOT NULL | Account Type |
| 7 | ITEM_NBR | Character(15) | VARCHAR2(15) NOT NULL | Item Nbr |
| 8 | ITEM_TERM | Character(4) | VARCHAR2(4) NOT NULL | Item Term |
| 9 | ITEM_AMT | Signed Number(18,2) | DECIMAL(16,2) NOT NULL | Item Amount |
| 10 | APPLIED_AMT | Signed Number(18,2) | DECIMAL(16,2) NOT NULL | Applied Amount |
| 11 | ITEM_TYPE | Character(12) | VARCHAR2(12) NOT NULL | Item Type |
| 12 | ITEM_TYPE_CD | Character(1) | VARCHAR2(1) NOT NULL |
Item Type Code
A=Application Fee B=Billing Only C=Charge D=Deposit F=Financial Aid G=GL Interface Only H=Withholding I=Interest L=Pre-Paid Tuition P=Payment R=Refund T=Transfers V=Contributor Relations W=Waiver X=Write-off Z=Pay Plan Credit |