ITEM_GRP_CH_VW(SQL View) |
Index Back |
|---|---|
Item Group Charge View |
| SELECT DISTINCT Z.BUSINESS_UNIT ,Z.COMMON_ID ,Z.COMMON_ID ,Z.ACCOUNT_NBR ,Z.ACCOUNT_TERM ,Z.ITEM_NBR ,Z.ITEM_TERM ,Z.ITEM_AMT ,Z.ITEM_TYPE ,Z.ITEM_TYPE_CD ,Z.ACCOUNT_TYPE_SF ,D.ITEM_TYPE_GROUP 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 WHERE Z.BUSINESS_UNIT = F.BUSINESS_UNIT AND Z.SA_ID_TYPE = 'P' 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 Z.ITEM_AMT - Z.APPLIED_AMT >= 0 |
| # | 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 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 4 | ACCOUNT_NBR |
Character(10) | VARCHAR2(10) NOT NULL | Account Nbr |
| 5 | ACCOUNT_TERM |
Character(4) | VARCHAR2(4) NOT NULL | Account Term |
| 6 | ITEM_NBR |
Character(15) | VARCHAR2(15) NOT NULL | Item Nbr |
| 7 | ITEM_TERM | Character(4) | VARCHAR2(4) NOT NULL | Item Term |
| 8 | ITEM_AMT | Signed Number(18,2) | DECIMAL(16,2) NOT NULL | Item Amount |
| 9 | ITEM_TYPE | Character(12) | VARCHAR2(12) NOT NULL | Item Type |
| 10 | 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 |
| 11 | ACCOUNT_TYPE_SF | Character(3) | VARCHAR2(3) NOT NULL | Account Type |
| 12 | ITEM_TYPE_GROUP | Character(10) | VARCHAR2(10) NOT NULL | Item Type Group |