PMT_ITEMDD_O_VW(SQL View) |
Index Back |
|---|---|
Payment Item Org VW |
| SELECT A.BUSINESS_UNIT ,A.COMMON_ID ,A.COMMON_ID ,A.PAYMENT_ID_NBR ,A.STRM ,A.PAYMENT_AMT ,A.ITEM_TYPE ,C.ITEM_TYPE_CD ,C.DESCR ,A.POSTED_DATE ,A.SF_REVERSAL_IND ,SUM(B.ITEM_BALANCE) FROM PS_PAYMENT_TBL A ,PS_ITEM_SF B ,PS_ITEM_TYPE_TBL C ,PS_BUS_UNIT_TBL_SF D WHERE A.BUSINESS_UNIT=B.BUSINESS_UNIT AND A.COMMON_ID = B.COMMON_ID AND A.SA_ID_TYPE = B.SA_ID_TYPE AND A.PAYMENT_ID_NBR=B.PAYMENT_ID_NBR AND A.BUSINESS_UNIT=D.BUSINESS_UNIT AND B.SA_ID_TYPE = 'O' AND C.SETID=( SELECT E.SETID FROM PS_SET_CNTRL_REC E WHERE E.SETCNTRLVALUE=D.INSTITUTION AND E.RECNAME='ITEM_TYPE_TBL') AND A.ITEM_TYPE=C.ITEM_TYPE AND C.EFFDT=( SELECT MAX(F.EFFDT) FROM PS_ITEM_TYPE_TBL F WHERE C.SETID=F.SETID AND C.ITEM_TYPE=F.ITEM_TYPE AND F.EFFDT <= %CurrentDateIn) AND C.EFF_STATUS='A' GROUP BY A.BUSINESS_UNIT ,A.COMMON_ID ,A.COMMON_ID ,A.PAYMENT_ID_NBR ,A.STRM ,A.ITEM_TYPE ,A.PAYMENT_AMT ,C.ITEM_TYPE_CD ,C.DESCR ,A.POSTED_DATE ,A.SF_REVERSAL_IND |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Default Value: OPR_DEF_TBL_CS.BUSINESS_UNIT Prompt Table: SP_BU_SF_NONVW |
| 2 | COMMON_ID | Character(11) | VARCHAR2(11) NOT NULL | Common ID to store Personal ID / Ext Org ID value |
| 3 | EXT_ORG_ID |
Character(11) | VARCHAR2(11) NOT NULL | External Org ID |
| 4 | PAYMENT_ID_NBR | Number(12,0) | DECIMAL(12) NOT NULL | Payment ID Nbr |
| 5 | STRM | Character(4) | VARCHAR2(4) NOT NULL | Term |
| 6 | PAYMENT_AMT | Number(19,3) | DECIMAL(18,3) NOT NULL | Payment Amount |
| 7 | ITEM_TYPE | Character(12) | VARCHAR2(12) NOT NULL | Item Type |
| 8 | 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 |
| 9 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 10 | POSTED_DATE | Date(10) | DATE | Date Posted |
| 11 | SF_REVERSAL_IND | Character(1) | VARCHAR2(1) NOT NULL | Reversal Indicator |
| 12 | BALANCE_AMT | Signed Number(18,2) | DECIMAL(16,2) NOT NULL | Charge Balance |