|
SELECT DISTINCT C.SETID , I.BUSINESS_UNIT , I.CUST_ID , 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.DEDUCTION_STATUS FROM PS_ITEM I , PS_CUSTOMER C , PS_SET_CNTRL_REC S , PS_AR_EBP_PPEP_VW Q 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 = Q.business_unit AND I.cust_id = Q.cust_id AND I.item = Q.item AND I.item_line = Q.item_line AND NOT EXISTS ( SELECT 'x' FROM PS_AR_EBP_ITEMP_VW P WHERE I.business_unit = P.business_unit AND I.cust_id = P.cust_id AND I.item = P.item AND I.item_line = P.item_line)
|
| # |
PeopleSoft Field Name |
PeopleSoft Field Type |
Database Column Type |
Description |
| 1 |
 SETID |
Character(5) |
VARCHAR2(5) NOT NULL |
SetID
|
| 2 |
BUSINESS_UNIT |
Character(5) |
VARCHAR2(5) NOT NULL |
Business Unit
Default Value: OPR_DEF_TBL_FS.BUSINESS_UNIT
Prompt Table:
SP_BUARDS_NONVW
|
| 3 |
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.
Prompt Table:
CUST_SRCH_VW
Set Control Field: BUSINESS_UNIT
|
| 4 |
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.
|
| 5 |
ITEM_LINE |
Number(6,0) |
INTEGER NOT NULL |
Item Line
|
| 6 |
ITEM_STATUS |
Character(1) |
VARCHAR2(1) NOT NULL |
Item Status
C=Closed
O=Open
Default Value: O
|
| 7 |
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.
Prompt Table:
ENTRY_TYPE_TBL
|
| 8 |
BAL_AMT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Item Balance
|
| 9 |
ACCOUNTING_DT |
Date(10) |
DATE NOT NULL |
The accounting entry construction date for a given transaction (a generic field that crosses multiple
|
| 10 |
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.
Default Value: ITEM.ACCOUNTING_DT
|
| 11 |
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.
Prompt Table:
DISPUTE_TBL
|
| 12 |
DISPUTE_DT |
Date(10) |
DATE |
Specifies the date that an item was placed in dispute status.
|
| 13 |
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.
|
| 14 |
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.
Prompt Table:
AR_TRMS_VW
|
| 15 |
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.
Prompt Table:
COLLECTION_TBL
|
| 16 |
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.
|
| 17 |
BAL_CURRENCY |
Character(3) |
VARCHAR2(3) NOT NULL |
Currency Code - Bal Amount
|
| 18 |
TRAN_FROM_BU |
Character(5) |
VARCHAR2(5) NOT NULL |
Transfer from Business Unit
|
| 19 |
TRAN_FROM_CUSTID |
Character(15) |
VARCHAR2(15) NOT NULL |
Transfer from customer
|
| 20 |
CONSOL_BUS_UNIT |
Character(5) |
VARCHAR2(5) NOT NULL |
Specifies the Billing business unit associated with a consolidated bill.
|
| 21 |
CONSOL_INVOICE |
Character(22) |
VARCHAR2(22) NOT NULL |
Specifies the invoice identifier associated with a consolidated bill.
|
| 22 |
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
|
| 23 |
DEDUCTION_STATUS |
Character(5) |
VARCHAR2(5) NOT NULL |
Deduction Status
C=Closed
I=In Process
O=Created/Opened
W=On Worksheet
|