|
SELECT A.EX_DOC_ID ,A.EX_DOC_TYPE ,A.LINE_NBR ,A.DISTRIB_LINE_NUM ,A.DST_ACCT_TYPE ,A.APPL_JRNL_ID ,A.UNPOST_SEQ ,A.BUSINESS_UNIT ,C.COUNTRY ,'EX' ,A.DST_ACCT_TYPE ,'N' ,A.FOREIGN_CURRENCY ,A.CURRENCY_CD ,' ' ,A.VAT_BASIS_AMT ,A.VAT_BASIS_AMT_BSE ,0 ,A.VAT_CALC_AMT ,A.VAT_CALC_AMT_BSE ,0 ,A.VAT_TRANS_AMT ,A.VAT_TRANS_AMT_BSE ,0 ,A.VAT_RCVRY_AMT ,A.VAT_RCVRY_AMT_BSE ,0 ,A.VAT_REBATE_AMT ,A.VAT_REBATE_AMT_BSE ,0 ,C.TAX_CD_VAT ,C.VAT_TXN_TYPE_CD ,C.TAX_CD_VAT_PCT ,C.VAT_TREATMENT ,C.VAT_APPLICABILITY ,C.TRANS_DT ,'N' ,' ' ,C.TRANS_DT ,0 ,0 ,C.TRANS_DT ,A.BUSINESS_UNIT_GL ,A.BUSINESS_UNIT_GL ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,A.DOC_TYPE ,A.DOC_SEQ_DATE ,A.DOC_SEQ_NBR ,C.PHYSICAL_NATURE ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' FROM PS_EX_ACCTG_LINE A ,PS_EX_SHEET_HDR B ,PS_EX_SHEET_LINE C ,PS_BUS_UNIT_TBL_EX E WHERE A.DST_ACCT_TYPE = 'VIR' AND A.EX_DOC_ID = B.SHEET_ID AND A.EX_DOC_ID = C.SHEET_ID AND A.LINE_NBR = C.LINE_NBR AND C.VAT_TREATMENT IN ('DGP','DSP') AND A.VAT_DISTRIB_STATUS = 'N' AND A.BUSINESS_UNIT_GL = E.BUSINESS_UNIT_GL AND A.APPL_JRNL_ID = E.APPL_JRNL_ID_ACCR
|
| # |
PeopleSoft Field Name |
PeopleSoft Field Type |
Database Column Type |
Description |
| 1 |
 EX_DOC_ID |
Character(10) |
VARCHAR2(10) NOT NULL |
Documentation ID
|
| 2 |
EX_DOC_TYPE |
Character(1) |
VARCHAR2(1) NOT NULL |
Identifies Expenses Document Type
A=Cash Advance
C=Time Report
J=Time Adjustment
M=Accrual - My Wallet
R=Accrual - Expense Report
S=Expense Report
T=Travel Authorization
|
| 3 |
EX_LINE_NBR |
Number(5,0) |
INTEGER NOT NULL |
Line Number
|
| 4 |
DISTRIB_LINE_NUM |
Number(5,0) |
INTEGER NOT NULL |
Distribution Line
|
| 5 |
DST_ACCT_TYPE |
Character(4) |
VARCHAR2(4) NOT NULL |
07/20/04 st: added RCN for Advance Reconciliation
02/16/98 ebn CN#CM800-2.0 : Added for MISC type for Landed Cost Enhancement
11/11/99 llr: Added ADVP for Payables Advance Payment
|
| 6 |
APPL_JRNL_ID |
Character(10) |
VARCHAR2(10) NOT NULL |
Journal Template
|
| 7 |
UNPOST_SEQ |
Number(2,0) |
SMALLINT NOT NULL |
Identifies the sequence of journal entries when a journal is "unposted". When a journal is posted the UnPost Sequence is automatically set to "0". When a journal is unposted, a new reversing entry is automatically created with an UnPost Sequence of "1".
|
| 8 |
BUSINESS_UNIT |
Character(5) |
VARCHAR2(5) NOT NULL |
Business Unit
|
| 9 |
COUNTRY_VAT_RPTG |
Character(3) |
VARCHAR2(3) NOT NULL |
VAT Reporting Country
|
| 10 |
VAT_TXN_SOURCE |
Character(6) |
VARCHAR2(6) NOT NULL |
VAT Transaction Source
|
| 11 |
VAT_DST_ACCT_TYPE |
Character(4) |
VARCHAR2(4) NOT NULL |
Specifies the accounting code associated with a VAT account type and with a VAT transaction. The accounting code determines when and how the transaction is distributed (posted) to the general ledger.
Prompt Table:
VAT_DST_VAT_VW
|
| 12 |
VAT_RPTG_CURR_FLG |
Character(1) |
VARCHAR2(1) NOT NULL |
Amounts in Reporting Currency
|
| 13 |
TXN_CURRENCY_CD |
Character(3) |
VARCHAR2(3) NOT NULL |
Transaction Currency
Prompt Table:
CURRENCY_CD_TBL
|
| 14 |
BASE_CURRENCY |
Character(3) |
VARCHAR2(3) NOT NULL |
"Specifies the primary currency for a general ledger business unit, and is sometimes referred to as the ""book"" currency. Each business unit has one base currency. which is usually, but not always, the local currency for the organization. Journal entries are posted to a business unit in its base currency. "
Prompt Table:
CURRENCY_CD_TBL
|
| 15 |
CURRENCY_RPTG |
Character(3) |
VARCHAR2(3) NOT NULL |
Reporting Currency
Prompt Table:
CURRENCY_CD_TBL
|
| 16 |
TAX_BASIS_AMT_TXN |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Tax Basis Amount - Transaction
|
| 17 |
TAX_BASIS_AMT_BSE |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Tax Basis Amount - Base
|
| 18 |
TAX_BASIS_AMT_RPTG |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Tax Basis Amount - Reporting
|
| 19 |
TAX_AMT_GROSS_TXN |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Gross Tax Amount - Transaction
|
| 20 |
TAX_AMT_GROSS_BSE |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Gross Tax Amount - Base
|
| 21 |
TAX_AMT_GROSS_RPTG |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Gross Tax Amount - Reporting
|
| 22 |
TAX_TRANS_AMT_TXN |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Tax Transaction Amt - Transact
|
| 23 |
TAX_TRANS_AMT_BSE |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Tax Transaction Amt - Base
|
| 24 |
TAX_TRANS_AMT_RPTG |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Tax Transaction Amt - Report
|
| 25 |
TAX_AMT_RCVRY_TXN |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Tax Recovery Amt - Transaction
|
| 26 |
TAX_AMT_RCVRY_BSE |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Tax Recovery Amount - Base
|
| 27 |
TAX_AMT_RCVRY_RPTG |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Tax Recovery Amt - Reporting
|
| 28 |
TAX_AMT_RBT_TXN |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Tax Rebate Amt - Transaction
|
| 29 |
TAX_AMT_RBT_BSE |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Tax Rebate Amount - Base
|
| 30 |
TAX_AMT_RBT_RPTG |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Tax Rebate Amount - Reporting
|
| 31 |
TAX_CD_VAT |
Character(8) |
VARCHAR2(8) NOT NULL |
Defines the VAT (Value Added Tax) percentage (via link to the Tax Authority) and is used to retrieve VAT accounting ChartFields. The VAT code determines how the VAT amount is calculated on a transaction and how that amount is accounted and reported for.
Prompt Table:
VAT_HEADER_VW
|
| 32 |
VAT_TXN_TYPE_CD |
Character(4) |
VARCHAR2(4) NOT NULL |
Specifies a user-defined category of business transaction that is subject to VAT accounting and reporting. The VAT code and the VAT transaction type are used in conjunction with the VAT account type to obtain the ChartFields for accounting entries. Some examples of VAT Transaction Types are Exempt Sales Exempt Purchases Triangulation EU Sales and Domestic Sales.
Prompt Table:
VAT_TXN_CD
|
| 33 |
TAX_CD_VAT_PCT |
Signed Number(9,4) |
DECIMAL(7,4) NOT NULL |
Specifies the tax percentage that corresponds to the VAT code. If more than one VAT authority is linked to a VAT code this will represent an aggregate percentage.
|
| 34 |
VAT_TREATMENT |
Character(4) |
VARCHAR2(4) NOT NULL |
VAT Treatment
Prompt Table:
VAT_TREATMENT
|
| 35 |
VAT_APPLICABILITY |
Character(1) |
VARCHAR2(1) NOT NULL |
A flag that indicates whether or not a transaction is eligible for VAT (Value Added Tax) taxation or why it is not eligible.
E=Exempt
N=Not Applicable
O=Outside of Scope of VAT
S=Suspended
T=Taxable
V=VAT Only
X=Exonerated
|
| 36 |
VAT_DCLRTN_DT |
Date(10) |
DATE |
"Specifies the date of the invoice when the VAT declaration point (VAT_DCLRTN_POINT) is ""Invoice"". The VAT declaration date is automatically set to the invoice date by the system. This field does not apply when the declaration point is ""payment"". "
|
| 37 |
VAT_DCLRTN_DT_SRC |
Character(1) |
VARCHAR2(1) NOT NULL |
Declaration Date Source
A=Delivery Date - Ext Process
D=Delivery Date - Txn Entry
I=Invoice Date
M=Manual Entry
N=Not Applicable
Default Value: N
|
| 38 |
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
|
| 39 |
CUR_EFFDT |
Date(10) |
DATE |
Specifies the effective date associated with the currency used as the base currency for the Business Unit on the Journal Entry Header.
|
| 40 |
RATE_MULT |
Signed Number(17,8) |
DECIMAL(15,8) NOT NULL |
Rate Multiplier
|
| 41 |
RATE_DIV |
Number(16,8) |
DECIMAL(15,8) NOT NULL |
Rate Divisor
|
| 42 |
VAT_RGSTR_RPT_DT |
Date(10) |
DATE |
Date to be used on Italian VAT Register Reports
|
| 43 |
SETCNTRLVALUE |
Character(20) |
VARCHAR2(20) NOT NULL |
Tree Definition User Key Value
Prompt Table:
SP_BU_FS_NONVW
|
| 44 |
BUSINESS_UNIT_GL |
Character(5) |
VARCHAR2(5) NOT NULL |
GL Business Unit
|
| 45 |
VENDOR_SETID |
Character(5) |
VARCHAR2(5) NOT NULL |
Vendor SetID
|
| 46 |
VENDOR_ID |
Character(10) |
VARCHAR2(10) NOT NULL |
Vendor Identifier
|
| 47 |
CUSTOMER_SETID |
Character(5) |
VARCHAR2(5) NOT NULL |
Customer SetID
|
| 48 |
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.
|
| 49 |
VAT_RGSTRN_TPRTNR |
Character(20) |
VARCHAR2(20) NOT NULL |
Trading Partner's VAT Registration ID
**** WARNING **** - This field has label ids of CUSTOMER, VENDOR, BANK. Do NOT delete these label ids. They are used to ensure the correct label is displayed on the common VAT subpages.
|
| 50 |
COUNTRY_VAT_TPRTNR |
Character(3) |
VARCHAR2(3) NOT NULL |
Trading Partner's VAT Registration Country.
**** WARNING **** - This field has label ids of CUSTOMER, VENDOR, BANK. Do NOT delete these label ids. They are used to ensure the correct label is displayed on the common VAT subpages.
|
| 51 |
COUNTRY_VAT_BILLFR |
Character(3) |
VARCHAR2(3) NOT NULL |
Specifies the country where an invoice is generated (for VAT processing only).
|
| 52 |
COUNTRY_VAT_BILLTO |
Character(3) |
VARCHAR2(3) NOT NULL |
Specifies the country where an invoice is sent (for VAT processing only).
|
| 53 |
COUNTRY_SHIP_TO |
Character(3) |
VARCHAR2(3) NOT NULL |
Specifies the country to which the invoice contents were shipped (for VAT processing only).
Prompt Table:
COUNTRY_TBL
|
| 54 |
COUNTRY_SHIP_FROM |
Character(3) |
VARCHAR2(3) NOT NULL |
Specifies the country from which the invoice contents were shipped (for VAT processing only).
|
| 55 |
DOC_TYPE |
Character(8) |
VARCHAR2(8) NOT NULL |
Specifies the business purpose of a financial transaction in countries that require all financial transactions to be tracked as "documents". A Document Type is associated with one and only one Journal Code.
|
| 56 |
DOC_SEQ_DATE |
Date(10) |
DATE |
Specifies the date that a document sequence number is assigned to a document or the date the document was created.
|
| 57 |
DOC_SEQ_NBR |
Character(12) |
VARCHAR2(12) NOT NULL |
Specifies the sequence number assigned to each financial transaction (a document). The sequence number may be manually entered or system-generated.
|
| 58 |
PHYSICAL_NATURE |
Character(1) |
VARCHAR2(1) NOT NULL |
Physical Nature
G=Goods
S=Services
|
| 59 |
COUNTRY_VAT_SUPPLY |
Character(3) |
VARCHAR2(3) NOT NULL |
VAT Place of Supply Country
Prompt Table:
COUNTRY_TBL
|
| 60 |
VAT_SVC_SUPPLY_FLG |
Character(1) |
VARCHAR2(1) NOT NULL |
VAT Services Place of Supply Flag
1=Supplier's Countries
2=Buyer's Countries
3=Where Physically Performed
|
| 61 |
VAT_SERVICE_TYPE |
Character(1) |
VARCHAR2(1) NOT NULL |
VAT Service Type
1=Freight Transport
2=Other
3=Electronics/Communication
|
| 62 |
COUNTRY_VAT_PERFRM |
Character(3) |
VARCHAR2(3) NOT NULL |
Country Where Service Physically Performed
Prompt Table:
COUNTRY_TBL
|
| 63 |
STATE_VAT_DEFAULT |
Character(6) |
VARCHAR2(6) NOT NULL |
VAT Defaulting State
Prompt Table:
STATE_TBL
|
| 64 |
VAT_RPT_STATUS |
Character(1) |
VARCHAR2(1) NOT NULL |
VAT Return Status
N=Not Reported
R=Reported
Default Value: N
|
| 65 |
ESL_RPT_STATUS |
Character(1) |
VARCHAR2(1) NOT NULL |
EC Sales List Status
N=Not Reported
R=Reported
Default Value: N
|
| 66 |
RCSL_RPT_STATUS |
Character(1) |
VARCHAR2(1) NOT NULL |
Rev Charge Sales List Status
N=Not Reported
R=Reported
Default Value: N
|