AR_CA_BAL_VW(SQL View) |
Index Back |
|---|---|
AR Contract Item Balance ViewAR Contract Item Balance View |
| SELECT I.BUSINESS_UNIT , I.CUST_ID , I.SUBCUST_QUAL1 , I.SUBCUST_QUAL2 , S.SETID , I.CONTRACT_NUM , I.CONTRACT_LINE_NUM , I.BAL_CURRENCY , SUM(I.BAL_AMT) , COUNT(*) , SUM(CASE WHEN I.DUE_DT < %CurrentDateIn THEN I.BAL_AMT ELSE 0 END) , SUM(CASE WHEN I.DUE_DT < %CurrentDateIn THEN 1 ELSE 0 END) FROM PS_ITEM I , PS_SET_CNTRL_REC S WHERE I.CONTRACT_NUM <> ' ' AND I.ITEM_STATUS <> 'C' AND S.RECNAME = 'CUSTOMER' AND S.SETCNTRLVALUE = I.BUSINESS_UNIT GROUP BY I.BUSINESS_UNIT , I.CUST_ID , I.SUBCUST_QUAL1 , I.SUBCUST_QUAL2 , S.SETID , I.CONTRACT_NUM , I.CONTRACT_LINE_NUM , I.BAL_CURRENCY |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 2 | CUST_ID | Character(15) | VARCHAR2(15) NOT NULL | Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations. |
| 3 | SUBCUST_QUAL1 | Character(15) | VARCHAR2(15) NOT NULL | Specifies a user-defined grouping of customer receivables activity. A subcustomer qualifier provides the ability to record and report receivables activity across multiple business units and/or multiple customers. For example a subcustomer qualifier could represent a sales region or a geographic area. There are two possible subcustomer qualifiers: SUBCUST_QUAL1 and SUBCUST_QUAL2. |
| 4 | SUBCUST_QUAL2 | Character(15) | VARCHAR2(15) NOT NULL | Specifies a user-defined grouping of customer receivables activity. A subcustomer qualifier provides the ability to record and report receivables activity across multiple business units and/or multiple customers. For example a subcustomer qualifier could represent a sales region or a geographic area. There are two possible subcustomer qualifiers: SUBCUST_QUAL1 and SUBCUST_QUAL2. |
| 5 | SETID_CUST | Character(5) | VARCHAR2(5) NOT NULL | Customer SetID |
| 6 | CONTRACT_NUM | Character(25) | VARCHAR2(25) NOT NULL | Contract |
| 7 | CONTRACT_LINE_NUM | Number(3,0) | SMALLINT NOT NULL | Contract Line Num |
| 8 | BAL_CURRENCY | Character(3) | VARCHAR2(3) NOT NULL | Currency Code - Bal Amount |
| 9 | BAL_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Item Balance |
| 10 | BALANCE_CNT | Number(5,0) | INTEGER NOT NULL | Balance Count |
| 11 | PASTDUE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Past Due |
| 12 | PASTDUE_CNT | Number(5,0) | INTEGER NOT NULL | Past Due Count |