PAY_ID_ITEMA_VW(SQL View) |
Index Back |
---|---|
Payment Item ID - One Ref |
SELECT A.DEPOSIT_BU , A.DEPOSIT_ID , B.PAYMENT_ID , A.PAYMENT_SEQ_NUM , MAX(A.REF_QUALIFIER_CODE) , MAX(A.REF_VALUE) ,MAX(B.PAYMENT_AMT) ,MAX(B.PAYMENT_CURRENCY) FROM PS_PAYMENT_ID_ITEM A , PS_PAYMENT B WHERE A.DEPOSIT_BU = B.DEPOSIT_BU AND A.DEPOSIT_ID = B.DEPOSIT_ID AND A.PAYMENT_SEQ_NUM = B.PAYMENT_SEQ_NUM AND A.REF_QUALIFIER_CODE>' ' AND A.REF_VALUE<>' ' AND B.PP_SW='Y' AND B.PAYMENT_STATUS IN ('I', 'U', 'E') GROUP BY A.DEPOSIT_BU, A.DEPOSIT_ID, B.PAYMENT_ID, A.PAYMENT_SEQ_NUM,B.PAYMENT_ID HAVING COUNT(A.REF_QUALIFIER_CODE)=1 |
# | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
---|---|---|---|---|
1 | DEPOSIT_BU | Character(5) | VARCHAR2(5) NOT NULL | Identifies the business unit to which a deposit is posted. |
2 | DEPOSIT_ID | Character(15) | VARCHAR2(15) NOT NULL | Uniquely identifies a deposit of customer payments. The deposit ID can be manually assigned or system-generated. |
3 | PAYMENT_ID | Character(15) | VARCHAR2(15) NOT NULL | Identifies a customer payment. This is usually a check number but may be some other identifying number. |
4 | PAYMENT_SEQ_NUM | Number(6,0) | INTEGER NOT NULL | Specifies the system-generated sequence number assigned to each payment as it is entered in a deposit. |
5 | REF_QUALIFIER_CODE | Character(2) | VARCHAR2(2) NOT NULL |
Reference Qualifier Code
Prompt Table: AR_FLD_REF_VW |
6 | REF_VALUE | Character(30) | VARCHAR2(30) NOT NULL | Reference |
7 | PAYMENT_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Specifies the amount of a customer payment. |
8 | PAYMENT_CURRENCY | Character(3) | VARCHAR2(3) NOT NULL | Identifies the currency of a customer payment. This is generally the same as the currency specified for the deposit but it can be different. |