ITEM_LINE_VW3(SQL View) |
Index Back |
|---|
| SELECT A.BUSINESS_UNIT ,A.COMMON_ID ,A.SA_ID_TYPE ,A.EMPLID ,A.EXT_ORG_ID ,A.ITEM_TERM ,D.ITEM_TYPE ,D.DESCR ,B.ITEM_TYPE_CD ,A.POSTED_DATE ,A.ITEM_EFFECTIVE_DT ,B.ITEM_AMT ,B.APPLIED_AMT ,B.ITEM_NBR ,B.ITEM_BALANCE ,SUM(A.LINE_AMT) FROM PS_ITEM_LINE_SF A , PS_ITEM_SF B , PS_BUS_UNIT_TBL_SF C , PS_ITEM_TYPE_TBL D WHERE B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.COMMON_ID = A.COMMON_ID AND B.SA_ID_TYPE = A.SA_ID_TYPE AND B.ITEM_NBR = A.ITEM_NBR AND B.ITEM_TYPE = D.ITEM_TYPE AND D.SETID=( SELECT E.SETID FROM PS_SET_CNTRL_REC E WHERE E.RECNAME='ITEM_TYPE_TBL' AND E.SETCNTRLVALUE=C.INSTITUTION) AND D.EFFDT=( SELECT MAX(Y.EFFDT) FROM PS_ITEM_TYPE_TBL Y WHERE Y.SETID = D.SETID AND Y.ITEM_TYPE = D.ITEM_TYPE AND Y.EFFDT <= %CurrentDateIn) AND D.EFF_STATUS = 'A' GROUP BY A.BUSINESS_UNIT, A.COMMON_ID, A.SA_ID_TYPE, A.EMPLID ,A.EXT_ORG_ID ,A.ITEM_TERM, D.ITEM_TYPE, D.DESCR ,B.ITEM_TYPE_CD ,A.POSTED_DATE ,A.ITEM_EFFECTIVE_DT ,B.ITEM_AMT , B.APPLIED_AMT, B.ITEM_NBR, B.ITEM_BALANCE |
| # | 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 | SA_ID_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
ID Type
O=Organization P=Person |
| 4 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 5 | EXT_ORG_ID |
Character(11) | VARCHAR2(11) NOT NULL | External Org ID |
| 6 | ITEM_TERM |
Character(4) | VARCHAR2(4) NOT NULL | Item Term |
| 7 | ITEM_TYPE |
Character(12) | VARCHAR2(12) NOT NULL | Item Type |
| 8 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 9 | 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 |
| 10 | POSTED_DATE | Date(10) | DATE | Date Posted |
| 11 | ITEM_EFFECTIVE_DT | Date(10) | DATE | Item Effective Date |
| 12 | ITEM_AMT | Signed Number(18,2) | DECIMAL(16,2) NOT NULL | Item Amount |
| 13 | APPLIED_AMT | Signed Number(18,2) | DECIMAL(16,2) NOT NULL | Applied Amount |
| 14 | ITEM_NBR | Character(15) | VARCHAR2(15) NOT NULL | Item Nbr |
| 15 | ITEM_BALANCE | Signed Number(18,2) | DECIMAL(16,2) NOT NULL | Item Balance |
| 16 | LINE_TOT_AMT | Signed Number(18,2) | DECIMAL(16,2) NOT NULL | Line Total Amount |