|
SELECT w.ws_bu , w.ws_id , 'N' , I.business_unit , I.cust_id , I.item , I.item_line , 0 , ' ' , ' ' , ' ' , I.entry_event , I.ref_reason , ' ' , I.bal_amt , I.accounting_dt , I.asof_dt , I.due_dt , I.po_ref , I.document , I.document_line , I.dst_id_ar , I.bill_of_lading , I.subcust_qual1 , I.subcust_qual2 , I.bal_currency , I.rate_mult , I.rate_div , I.rt_type , I.bal_amt_base , I.currency_cd , I.vat_dclrtn_point , I.vat_recalc_flg , I.country_vat_billfr , I.country_vat_billto , I.country_ship_to , I.country_ship_from , I.vat_calc_gross_net , I.vat_entity , I.vat_excptn_type , I.vat_excptn_certif , I.vat_rgstrn_buyer , I. vat_round_rule , I.pymnt_terms_cd , I.accounting_dt , I.entry_type , I.entry_reason , 0 , b.business_unit_gl , I.disc_dt , ' ' ,0 ,0 ,0 , ' ' ,' ' ,0 , I.bal_amt , I.bal_amt_base ,' ' ,0 , I.order_no , I.contract_num , I.entry_use_id , I.item_status , I.process_instance , I.draft_bu , I.draft_id , I.draft_approval , I.draft_doc , I.draft_currency , I.draft_format , I.draft_status , I.payment_method , I.bank_setid , I.bank_cd , I.bank_acct_key , I.dd_bu , I.dd_id , I.dd_currency , I.dd_status , I.dd_profile_id , I.draft_type , I.doubtful , I.draft_busn_event , I.draft_sub_event , I.sales_person , I.sales_person2 , I.region_cd , I.revalue_flag , I.packslip_no , I.sbi_num , I.lc_id , I.ag_ref_nbr , I.convers_exists , I.business_unit_ca , I.contract_line_num FROM ps_ws_control w , PS_ITEM I , ps_bus_unit_tbl_ar B WHERE b.business_unit = i.business_unit
|
| # |
PeopleSoft Field Name |
PeopleSoft Field Type |
Database Column Type |
Description |
| 1 |
 WS_BU |
Character(5) |
VARCHAR2(5) NOT NULL |
Worksheet Business Unit
|
| 2 |
WS_ID |
Character(15) |
VARCHAR2(15) NOT NULL |
Worksheet ID
|
| 3 |
ITEM_SELECTED |
Character(1) |
VARCHAR2(1) NOT NULL |
Item selected switch
|
| 4 |
BUSINESS_UNIT |
Character(5) |
VARCHAR2(5) NOT NULL |
Business Unit
|
| 5 |
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.
|
| 6 |
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.
|
| 7 |
ITEM_LINE |
Number(6,0) |
INTEGER NOT NULL |
Item Line
|
| 8 |
WS_SEQ |
Number(6,0) |
INTEGER NOT NULL |
Worksheet Sequence Number
|
| 9 |
ENTRY_USE_ID |
Character(5) |
VARCHAR2(5) NOT NULL |
Specifies the System Function or type of processing associated with an entry type.
|
| 10 |
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.
|
| 11 |
ENTRY_REASON |
Character(5) |
VARCHAR2(5) NOT NULL |
"Specifies an entry reason that further qualifies the entry type associated with a pending item. For example an entry reason for a credit memo might be ""Pricing Error"" or ""Shipping Error""."
Prompt Table:
AUTO_REASN_VW
|
| 12 |
ENTRY_EVENT |
Character(10) |
VARCHAR2(10) NOT NULL |
Entry Event
|
| 13 |
REF_REASON |
Character(5) |
VARCHAR2(5) NOT NULL |
Reference Reason
|
| 14 |
REQUIRE_REASON |
Character(1) |
VARCHAR2(1) NOT NULL |
Require Reason
|
| 15 |
BAL_AMT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Item Balance
|
| 16 |
ACCOUNTING_DT |
Date(10) |
DATE |
The accounting entry construction date for a given transaction (a generic field that crosses multiple
|
| 17 |
ASOF_DT |
Date(10) |
DATE |
As of Date
|
| 18 |
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.
|
| 19 |
PO_REF |
Character(30) |
VARCHAR2(30) NOT NULL |
Specifies the purchase order number associated with a receivables item.
|
| 20 |
DOCUMENT |
Character(30) |
VARCHAR2(30) NOT NULL |
Specifies a document used as a reference number for a Receivables item. Document ID is used by the Receivables Update program and the Maintenance Worksheet to "match" related invoices debit memos and credit memos.
|
| 21 |
DOCUMENT_LINE |
Number(6,0) |
INTEGER NOT NULL |
Specifies the line number of a Document ID used as a reference on a Receivables item.
|
| 22 |
DST_ID_AR |
Character(10) |
VARCHAR2(10) NOT NULL |
Specifies the general ledger ChartField combination that is affected when subsequent activity such as a payment occurs for the item.
|
| 23 |
BILL_OF_LADING |
Character(30) |
VARCHAR2(30) NOT NULL |
Specifies a bill of lading number associated with a Receivables item.
|
| 24 |
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.
|
| 25 |
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.
|
| 26 |
BAL_CURRENCY |
Character(3) |
VARCHAR2(3) NOT NULL |
Currency Code - Bal Amount
|
| 27 |
RATE_MULT |
Signed Number(17,8) |
DECIMAL(15,8) NOT NULL |
Rate Multiplier
|
| 28 |
RATE_DIV |
Number(16,8) |
DECIMAL(15,8) NOT NULL |
Rate Divisor
Default Value: 1
|
| 29 |
RT_TYPE |
Character(5) |
VARCHAR2(5) NOT NULL |
Defines a category of market rates for currency conversion. Some examples of rate types are commercial, average, floating, and historical.
|
| 30 |
BAL_AMT_BASE |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Balance - Base Currency
|
| 31 |
CURRENCY_CD |
Character(3) |
VARCHAR2(3) NOT NULL |
Currency Code
|
| 32 |
VAT_DCLRTN_POINT |
Character(1) |
VARCHAR2(1) NOT NULL |
A flag that specifies when VAT information for sales or purchase transaction is recognized for reporting to the appropriate VAT authority. VAT is declared at invoice time, delivery time, accounting date or the time of payment.
A=At Accounting Date
D=At Delivery Time
I=At Invoice Time
P=At Payment Time
|
| 33 |
VAT_RECALC_FLG |
Character(1) |
VARCHAR2(1) NOT NULL |
A flag that indicates whether or not the VAT discount amount is recalculated at payment time. This field is only used if the VAT Calculation Type (VAT_CAL_GROSS_NET) is gross.
|
| 34 |
COUNTRY_VAT_BILLFR |
Character(3) |
VARCHAR2(3) NOT NULL |
Specifies the country where an invoice is generated (for VAT processing only).
|
| 35 |
COUNTRY_VAT_BILLTO |
Character(3) |
VARCHAR2(3) NOT NULL |
Specifies the country where an invoice is sent (for VAT processing only).
|
| 36 |
COUNTRY_SHIP_TO |
Character(3) |
VARCHAR2(3) NOT NULL |
Specifies the country to which the invoice contents were shipped (for VAT processing only).
|
| 37 |
COUNTRY_SHIP_FROM |
Character(3) |
VARCHAR2(3) NOT NULL |
Specifies the country from which the invoice contents were shipped (for VAT processing only).
|
| 38 |
VAT_CALC_GROSS_NET |
Character(1) |
VARCHAR2(1) NOT NULL |
A flag that indicates whether VAT calculation should be done on the Gross (G) amount or on the Net (N) amount of sales or purchase transaction. When calculating VAT at net the early payment discount is applied to the goods amount before calculating the VAT. When calculating VAT at gross the VAT is initially calculated based on the gross transaction amount.
G=Calculate at Gross
N=Calculate at Net
|
| 39 |
VAT_ENTITY |
Character(20) |
VARCHAR2(20) NOT NULL |
Specifies an organizational entity that is responsible for VAT reporting and that is registered in one or more countries that require VAT reporting. A single VAT entity is linked to one or more General Ledger Business Units depending on the reporting requirements.
|
| 40 |
VAT_EXCPTN_TYPE |
Character(1) |
VARCHAR2(1) NOT NULL |
A flag that specifies the reason for a VAT exemption for a customer. Suspension (S) and Exoneration (X) are examples of Exception Type.
N=None
S=Suspended
X=Exonerated
|
| 41 |
VAT_EXCPTN_CERTIF |
Character(20) |
VARCHAR2(20) NOT NULL |
Specifies the VAT exception certificate number. This identifier serves as proof of customer's exemption from VAT.
|
| 42 |
VAT_RGSTRN_BUYER |
Character(20) |
VARCHAR2(20) NOT NULL |
Specifies a customers VAT Registration ID for a particular country.
|
| 43 |
VAT_ROUND_RULE |
Character(1) |
VARCHAR2(1) NOT NULL |
" Rounding rule to be applied to VAT calculations. Options are 'Natural'
D=Round Down
N=Natural Round
U=Round Up
|
| 44 |
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.
|
| 45 |
ITEM_ACCTG_DT |
Date(10) |
DATE |
Specifies the accounting date of the original item and appears on subsequent entries. This field is used in multicurrency processing.
|
| 46 |
ITEM_ENTRY_TYPE |
Character(5) |
VARCHAR2(5) NOT NULL |
Original Item Entry Type
|
| 47 |
ITEM_ENTRY_REASON |
Character(5) |
VARCHAR2(5) NOT NULL |
Original Item Entry Reason
|
| 48 |
AMT_REM |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Amount Remaining
|
| 49 |
BUSINESS_UNIT_GL |
Character(5) |
VARCHAR2(5) NOT NULL |
GL Business Unit
|
| 50 |
DISC_DT |
Date(10) |
DATE |
Specifies the date before which payment must be made on an invoice in order to qualify for a discount. This field applies to the first of two possible discounts and is associated with the discount amount specified in DISC_AMT.
|
| 51 |
MATCH_SEQ |
Character(1) |
VARCHAR2(1) NOT NULL |
Match Sequence
|
| 52 |
REAL_GAIN_LOSS |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Represents the monetary amount of realized gain or loss calculated by the system during payment application or maintenance worksheet (Receivables).
|
| 53 |
RATE_MULT_ORIG |
Number(16,8) |
DECIMAL(15,8) NOT NULL |
Original Rate Multiplier
|
| 54 |
RATE_DIV_ORIG |
Number(16,8) |
DECIMAL(15,8) NOT NULL |
Original Rate Divisor
Default Value: 1
|
| 55 |
DST_ID_REAL |
Character(10) |
VARCHAR2(10) NOT NULL |
Specifies the general ledger ChartField combination for posting realized gain or loss calculated by the system during payment application or maintenance worksheet (Receivables).
|
| 56 |
SETID |
Character(5) |
VARCHAR2(5) NOT NULL |
SetID
|
| 57 |
AMT_SEL |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Amount selected
|
| 58 |
ITEM_AMT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Amount
|
| 59 |
ITEM_AMT_BASE |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Item Amount Base
|
| 60 |
ITEM_MTCH |
Character(33) |
VARCHAR2(33) NOT NULL |
Matched Item
|
| 61 |
ITEM_LINE_MTCH |
Number(6,0) |
INTEGER NOT NULL |
Matched Item Line
|
| 62 |
ORDER_NO |
Character(10) |
VARCHAR2(10) NOT NULL |
Identifies a customer order number that appears as a reference on a receivables pending item.
Date Initials Issue Description
022701 mv SP2 (F-CJORGENS-3) CSR Desktop
|
| 63 |
CONTRACT_NUM |
Character(25) |
VARCHAR2(25) NOT NULL |
Contract
|
| 64 |
ITEM_ENTRY_USE |
Character(5) |
VARCHAR2(5) NOT NULL |
Item Entry Use ID
|
| 65 |
ITEM_STATUS |
Character(1) |
VARCHAR2(1) NOT NULL |
Item Status
C=Closed
O=Open
|
| 66 |
PROCESS_INSTANCE |
Number(10,0) |
DECIMAL(10) NOT NULL |
Process Instance
|
| 67 |
DRAFT_BU |
Character(5) |
VARCHAR2(5) NOT NULL |
Identifies the Receivables business unit associated with a draft. This field is maintained by the Draft Creation process.
|
| 68 |
DRAFT_ID |
Character(15) |
VARCHAR2(15) NOT NULL |
Uniquely identifies a payment draft. The Draft ID is system-generated during the Draft Creation process or manually entered.
|
| 69 |
DRAFT_APPROVAL |
Character(1) |
VARCHAR2(1) NOT NULL |
"Indicates whether or not a draft is pre-approved for payment by a bill-to customer. If the draft is pre-approved then it can be submitted to the customer's bank without the customer's sign-off. Otherwise the draft must be reviewed and approved by the customer before the enterprise can submit it to the bank for payment. This field is used only if the preferred payment method is ""Draft""."
P=Preapproved
R=Approval Required
|
| 70 |
DRAFT_DOC |
Character(1) |
VARCHAR2(1) NOT NULL |
"A flag that indicates whether or not (C or N) a draft document should be created for a Receivables item when the payment method is ""draft"". If the value is N then the system prints only a draft statement. If the value is C then the system creates a draft document. "
C=Create Document
N=Do Not Create Document
|
| 71 |
DRAFT_CURRENCY |
Character(3) |
VARCHAR2(3) NOT NULL |
Specifies the currency of a draft payment. The draft currency may or may not be the same as the base currency of the business unit handling the draft.
|
| 72 |
DRAFT_FORMAT |
Character(1) |
VARCHAR2(1) NOT NULL |
"A flag that indicates whether a draft will be associated with a single item or with multiple items. This field is used only when the payment method is ""draft""."
M=Multiple Items Per Draft
S=Single Item Per Draft
|
| 73 |
DRAFT_STATUS |
Character(1) |
VARCHAR2(1) NOT NULL |
Draft Status
A=Accepted
C=Complete
D=Dishonored
E=Void
I=Identified
L=Collateral
M=Discounted
N=No Action
P=Pending
R=Remitted
T=Endorsed
|
| 74 |
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
|
| 75 |
BANK_SETID |
Character(5) |
VARCHAR2(5) NOT NULL |
The PeopleSoft tableset ID associated with a given bank/counterparty.
|
| 76 |
BANK_CD |
Character(5) |
VARCHAR2(5) NOT NULL |
Bank Code
|
| 77 |
BANK_ACCT_KEY |
Character(4) |
VARCHAR2(4) NOT NULL |
A user defined unique identifier that facilitates the identification of a given account with a given bank
|
| 78 |
DD_BU |
Character(5) |
VARCHAR2(5) NOT NULL |
Specifies the business unit to which a direct debit is posted. This field is maintained by the Direct debit generation process.
|
| 79 |
DD_ID |
Character(15) |
VARCHAR2(15) NOT NULL |
Specifies a sequence number assigned to a Direct Debit by the Direct Debit generation process.
|
| 80 |
DD_CURRENCY |
Character(3) |
VARCHAR2(3) NOT NULL |
Specifies the currency in which a direct debit is created. This field is maintained by the Direct Debit generation process.
|
| 81 |
DD_STATUS |
Character(1) |
VARCHAR2(1) NOT NULL |
A flag that indicates the stage of processing of a direct debit payment in receivables. This field is maintained by the Direct Debit generation process.
A=Accepted
C=Complete
E=Rejected
N=No Action
P=Pending
R=Remitted
Z=All
|
| 82 |
DD_PROFILE_ID |
Character(5) |
VARCHAR2(5) NOT NULL |
Specifies the Profile ID for a Direct Debit
|
| 83 |
DRAFT_TYPE |
Character(10) |
VARCHAR2(10) NOT NULL |
A flag that indicates whether a draft is initiated by the vendor (1) or by the customer (2).
|
| 84 |
DOUBTFUL |
Character(1) |
VARCHAR2(1) NOT NULL |
Identifies items ( or invoices ) which probably will not be paid by the customer. This status is used for items whose balances have been moved from the receivables account to the doubtful account using the transfer worksheet.
|
| 85 |
DRAFT_BUSN_EVENT |
Character(2) |
VARCHAR2(2) NOT NULL |
Defines the Draft life cycle event. It represents one of the actions that the PeopleSoft system has provided for Draft processing. Examples are: Approving a Draft Remitting a Draft to the Bank Voiding the Draft.
|
| 86 |
DRAFT_SUB_EVENT |
Character(2) |
VARCHAR2(2) NOT NULL |
Defines specific types of activity that can be performed at the Draft Business Event. For example in the process of approving a Draft you can specify that you are paying an item putting an amount on acccount etc.
|
| 87 |
SALES_PERSON |
Character(8) |
VARCHAR2(8) NOT NULL |
Specifies the personnel code for the person who works with a customer as a sales representative. This field is required for a customer that functions as a bill-to customer.
|
| 88 |
SALES_PERSON2 |
Character(8) |
VARCHAR2(8) NOT NULL |
Identifies a second Sales Person. Populated on the Item Table to identify an additional Sales Person the Item is applied to.
|
| 89 |
REGION_CD |
Character(10) |
VARCHAR2(10) NOT NULL |
Identifies the Sales Region. Populated on the Item Table to identify the Sales Region the Item is applied to.
|
| 90 |
REVALUE_FLAG |
Character(1) |
VARCHAR2(1) NOT NULL |
Can be either 'Y' or 'N'. This field is located on the ITEM Table and signifies whether or not an item will be considered by the Revaluation Process to be revaluated.
|
| 91 |
PACKSLIP_NO |
Character(22) |
VARCHAR2(22) NOT NULL |
Packing Slip Number
|
| 92 |
SBI_NUM |
Character(40) |
VARCHAR2(40) NOT NULL |
SBI_num
|
| 93 |
LC_ID |
Character(12) |
VARCHAR2(12) NOT NULL |
Treasury Letter of Credit Number Assigned by the System
|
| 94 |
AG_REF_NBR |
Number(16,0) |
DECIMAL(16) NOT NULL |
Payment Reference Number
|
| 95 |
CONVERS_EXISTS |
Character(1) |
VARCHAR2(1) NOT NULL |
"A flag that indicates whether a conversation is associated with a single item or with multiple items ."
|
| 96 |
BUSINESS_UNIT_CA |
Character(5) |
VARCHAR2(5) NOT NULL |
Contracts Business Unit
|
| 97 |
CONTRACT_LINE_NUM |
Number(3,0) |
SMALLINT NOT NULL |
Contract Line Num
|