STMT_EXTRCT_VW(SQL View) |
Index Back |
|---|
| SELECT C.SETID , I.CUST_ID , D.ST_ID_NUM , I.BUSINESS_UNIT , I.ITEM , I.ITEM_LINE , I.ITEM_STATUS , I.ENTRY_TYPE , I.BAL_AMT , I.ACCOUNTING_DT , I.DUE_DT , I.DISPUTE_STATUS , I.DISPUTE_DT , I.DISPUTE_AMOUNT , I.PYMNT_TERMS_CD , I.COLLECTION_STATUS , I.COLLECTION_DT , I.BAL_CURRENCY , I.TRAN_FROM_BU , I.TRAN_FROM_CUSTID , I.CONSOL_BUS_UNIT , I.CONSOL_INVOICE , I.PAYMENT_METHOD , I.DEDUCTION_STATUS , I.DEDUCTION_DT FROM PS_ITEM I , PS_CUSTOMER C , PS_SET_CNTRL_REC S , PS_STMT_CUST_DTL D WHERE C.CUST_ID = I.CUST_ID AND C.SETID = S.SETID AND S.SETCNTRLVALUE = I.BUSINESS_UNIT AND S.RECNAME = 'CUSTOMER' AND I.business_unit = D.business_unit AND I.cust_id = D.cust_id AND I.item = D.item AND I.item_line = D.item_line AND I.item_status = 'O' |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  SETID |
Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 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 | ST_ID_NUM |
Number(8,0) | INTEGER NOT NULL | Statement Number |
| 4 | BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 5 | ITEM |
Character(30) | VARCHAR2(30) NOT NULL | Uniquely identifies an invoice credit memo or debit memo. The item ID is also called the invoice number. |
| 6 | ITEM_LINE |
Number(6,0) | INTEGER NOT NULL | Item Line |
| 7 | ITEM_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Item Status
C=Closed O=Open |
| 8 | ENTRY_TYPE | Character(5) | VARCHAR2(5) NOT NULL | Specifies the type of transaction associated with a pending item. The entry type remains with the item after it is posted and affects many areas of Receivables processing. Some examples of entry types are Invoices Credit Memo Prepayment and Deduction. |
| 9 | BAL_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Item Balance |
| 10 | ACCOUNTING_DT | Date(10) | DATE | The accounting entry construction date for a given transaction (a generic field that crosses multiple |
| 11 | DUE_DT | Date(10) | DATE | Specifies the date that a transaction is due. It is a generic field used in multiple PeopleSoft applications including Receivables Payables and Purchasing. For example it represents the date that payment is due for a voucher or the date that a scheduled shipment is due to be received. |
| 12 | DISPUTE_STATUS | Character(3) | VARCHAR2(3) NOT NULL | Specifies the reason that an item or customer is in dispute. For example the dispute status code might represent pricing error. |
| 13 | DISPUTE_DT | Date(10) | DATE | Specifies the date that an item was placed in dispute status. |
| 14 | DISPUTE_AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Specifies the amount of an item in dispute. This may represent only a portion of the full amount of an item. |
| 15 | PYMNT_TERMS_CD | Character(5) | VARCHAR2(5) NOT NULL | Specifies how the payment due date and discount due date are calculated. A payment terms code is associated with various business units bill-to customers vendors as well as sales orders purchase orders and vouchers. |
| 16 | COLLECTION_STATUS | Character(3) | VARCHAR2(3) NOT NULL | Specifies a collection agency or the reason for placing a customer in collection. Collection status indicates that the customer is delinquent in making payments or the invoice is more than 120 days past due. |
| 17 | COLLECTION_DT | Date(10) | DATE | Specifies the date that a customer or invoice was put in collection status. Collection status indicates that the customer is delinquent in making payments or the invoice is more than 120 days past due. |
| 18 | BAL_CURRENCY | Character(3) | VARCHAR2(3) NOT NULL | Currency Code - Bal Amount |
| 19 | TRAN_FROM_BU | Character(5) | VARCHAR2(5) NOT NULL | Transfer from Business Unit |
| 20 | TRAN_FROM_CUSTID | Character(15) | VARCHAR2(15) NOT NULL | Transfer from customer |
| 21 | CONSOL_BUS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Specifies the Billing business unit associated with a consolidated bill. |
| 22 | CONSOL_INVOICE | Character(22) | VARCHAR2(22) NOT NULL | Specifies the invoice identifier associated with a consolidated bill. |
| 23 | PAYMENT_METHOD | Character(3) | VARCHAR2(3) NOT NULL |
Specifies the preferred method of payment by a customer. Examples of payment method are check direct debit and draft.
10/28/2003 edw 655070000 - Removed LTC and CSH xlats.
ACH=ACH CC=Credit Card CHK=Check CSH=Cash DD=Direct Debit DR=Draft EC=Electronic Check EFT=Electronic Fund Transfer GE=Giro - EFT PL=PayPal WIR=Wire Report |
| 24 | DEDUCTION_STATUS | Character(5) | VARCHAR2(5) NOT NULL |
Deduction Status
C=Closed I=In Process O=Created/Opened W=On Worksheet |
| 25 | DEDUCTION_DT | Date(10) | DATE | Deduction Date |