|
SELECT SHEET_ID , A.LINE_NBR , A.EX_LINE_STATUS , A.SHEET_RCPT_NUM , A.SHEET_RCPT_TOTAL , A.ARRIVAL_DATE , A.DEPARTURE_DATE , A.EXPENSE_TYPE , A.TRANS_DT , A.REIMBURSEMENT_CD , A.PERSONAL_EXPENSE , A.REIMB_ACTION , A.MONETARY_AMOUNT , A.CURRENCY_CD , A.TXN_AMOUNT , A.TXN_CURRENCY_CD , A.RATE_MULT , A.RATE_DIV , A.RT_TYPE , A.GROSS_UP_EST_AMT , A.TXN_LOCATION , A.EXPEND_MTHD , A.PREF_MRCHNT_FLG , A.MERCHANT_CD , A.MERCHANT , A.DESCR60 , A.AIRFARE_RCPT_NBR , A.DISTANCE , A.DISTANCE_TYPE , A.DISTANCE_RT , A.NBR_NIGHTS , A.EE_LOCAMT_COMMENT , A.EE_NORCPT_COMMENT , A.EE_PRFMRC_COMMENT , A.EE_DPLCAT_COMMENT , A.RECEIPT_VERIFIED , A.OUT_OF_POLICY , A.NO_RECEIPT_FLG , A.PREF_MRCH_NOT_USED , A.TAX_IMPLICATIONS , A.DUPLICATES_EXIST , A.BILL_CODE_EX , A.BILLING_ACTION , A.OVRRIDE_SPLT_SW , A.TRAVEL_AUTH_ID , A.GROSSUP , A.SEPCHK , A.EX_CREDIT_REF , A.TRANS_DT_JULIAN , A.STARTTM_PERDIEM , A.ENDTM_PERDIEM , A.CRDMEM_ACCT_NBR , A.BREAKFAST_FLG , A.LUNCH_FLG , A.DINNER_FLG ,%subrec(EX_VAT_LINE, A) FROM PS_EX_SHEET_LINE A WHERE SHEET_RCPT_NUM<>0
|
| # |
PeopleSoft Field Name |
PeopleSoft Field Type |
Database Column Type |
Description |
| 1 |
 SHEET_ID |
Character(10) |
VARCHAR2(10) NOT NULL |
Report ID
|
| 2 |
LINE_NBR |
Number(5,0) |
INTEGER NOT NULL |
Line Number:
11/24/08 - Added TARGET label [PC product]
Default Value: 1
|
| 3 |
EX_LINE_STATUS |
Character(3) |
VARCHAR2(3) NOT NULL |
The Line Status of an Expenses transaction (i.e. Expense Report, Cash Advance, Travel Authorization, Time Report)
ADJ=Adjusted
APR=Approved
APY=Approved for Payment
CLS=Closed
DEN=Denied
DNA=Denied by Approver
DNC=Denied
DNU=Denied by Auditor
ESC=Escheated Payment
HDA=Hold by Approver
HDU=Hold by Auditor
HLD=Hold
MFS=Marked For Submit
OPN=Open
PAR=Approvals in Process
PD=Paid
PND=Pending
PRO=In Process
RAP=Approved
STG=Staged
SUB=Submitted
XML=Submitted, Pending Validation
Default Value: PND
|
| 4 |
SHEET_RCPT_NUM |
Number(3,0) |
SMALLINT NOT NULL |
Receipt Number
|
| 5 |
SHEET_RCPT_TOTAL |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Total Local Amount
|
| 6 |
ARRIVAL_DATE |
Date(10) |
DATE |
Arrival Date
|
| 7 |
DEPARTURE_DATE |
Date(10) |
DATE |
Departure Date
|
| 8 |
EXPENSE_TYPE |
Character(7) |
VARCHAR2(7) NOT NULL |
Expense Type
Prompt Table:
EX_TYPES_TBL
|
| 9 |
TRANS_DT |
Date(10) |
DATE |
Transaction Date
|
| 10 |
REIMBURSEMENT_CD |
Character(1) |
VARCHAR2(1) NOT NULL |
Deny
N=Nonreimburseable
P=Prepaid
R=Reimburseable
Default Value: R
|
| 11 |
PERSONAL_EXPENSE |
Character(1) |
VARCHAR2(1) NOT NULL |
Personal Expense
N=No
Y=Yes
Y/N Table Edit
Default Value: N
|
| 12 |
REIMB_ACTION |
Character(5) |
VARCHAR2(5) NOT NULL |
Reason
Prompt Table:
EX_ACTION_TBL
|
| 13 |
MONETARY_AMOUNT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Specifies the monetary amount of a debit or credit in the business unit base currency. Debit entries are positive and credit entries are negative. This amount is only zero if associated with a statistical account.
|
| 14 |
CURRENCY_CD |
Character(3) |
VARCHAR2(3) NOT NULL |
Currency Code
Default Value: BUS_UNIT_TBL_EX.BASE_CURRENCY
Prompt Table:
CURRENCY_CD_TBL
|
| 15 |
TXN_AMOUNT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Transaction Amount
|
| 16 |
TXN_CURRENCY_CD |
Character(3) |
VARCHAR2(3) NOT NULL |
Transaction Currency
Default Value: BUS_UNIT_TBL_EX.BASE_CURRENCY
Prompt Table:
CURRENCY_CD_TBL
|
| 17 |
RATE_MULT |
Signed Number(17,8) |
DECIMAL(15,8) NOT NULL |
Rate Multiplier
|
| 18 |
RATE_DIV |
Number(16,8) |
DECIMAL(15,8) NOT NULL |
Rate Divisor
Default Value: 1
|
| 19 |
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.
Default Value: BUS_UNIT_OPT_EX.RT_TYPE
Prompt Table:
RT_TYPE_TBL
|
| 20 |
GROSS_UP_EST_AMT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Gross Up Estimated Amount
|
| 21 |
TXN_LOCATION |
Character(5) |
VARCHAR2(5) NOT NULL |
Expense Location
Prompt Table:
EX_LOCATION_TBL
|
| 22 |
EXPEND_MTHD |
Character(3) |
VARCHAR2(3) NOT NULL |
Payment Type
Prompt Table:
EX_EXP_MTHD_TBL
Set Control Field: BUSINESS_UNIT_GL
|
| 23 |
PREF_MRCHNT_FLG |
Character(1) |
VARCHAR2(1) NOT NULL |
Preferred Merchant
N=No
Y=Yes
Y/N Table Edit
Default Value: N
|
| 24 |
MERCHANT_CD |
Character(10) |
VARCHAR2(10) NOT NULL |
Preferred Merchant
Prompt Table:
EX_MERCHANT_VW
|
| 25 |
MERCHANT |
Character(40) |
VARCHAR2(40) NOT NULL |
Merchant
|
| 26 |
DESCR60 |
Character(60) |
VARCHAR2(60) NOT NULL |
Description
|
| 27 |
AIRFARE_RCPT_NBR |
Character(16) |
VARCHAR2(16) NOT NULL |
Airfare Receipt Number
|
| 28 |
DISTANCE |
Number(8,2) |
DECIMAL(7,2) NOT NULL |
Distance Traveled
|
| 29 |
DISTANCE_TYPE |
Character(1) |
VARCHAR2(1) NOT NULL |
Distance Type
3=Metres
K=Kilometers
M=Miles
|
| 30 |
DISTANCE_RT |
Number(9,4) |
DECIMAL(8,4) NOT NULL |
Standard Distance Rate
|
| 31 |
NBR_NIGHTS |
Number(3,0) |
SMALLINT NOT NULL |
Number of Nights
Default Value: 1
|
| 32 |
EE_LOCAMT_COMMENT |
Character(60) |
VARCHAR2(60) NOT NULL |
Location Amount Comment
|
| 33 |
EE_NORCPT_COMMENT |
Character(60) |
VARCHAR2(60) NOT NULL |
No Receipt Comment
|
| 34 |
EE_PRFMRC_COMMENT |
Character(60) |
VARCHAR2(60) NOT NULL |
Preferred Vendor Comment
|
| 35 |
EE_DPLCAT_COMMENT |
Character(80) |
VARCHAR2(80) NOT NULL |
Duplicate Comment
|
| 36 |
RECEIPT_VERIFIED |
Character(1) |
VARCHAR2(1) NOT NULL |
Receipt Verified
Y/N Table Edit
Default Value: N
|
| 37 |
OUT_OF_POLICY |
Character(1) |
VARCHAR2(1) NOT NULL |
Authorized Amount Exceeded
Y/N Table Edit
Default Value: N
|
| 38 |
NO_RECEIPT_FLG |
Character(1) |
VARCHAR2(1) NOT NULL |
No Receipt
Y/N Table Edit
Default Value: N
|
| 39 |
PREF_MRCH_NOT_USED |
Character(1) |
VARCHAR2(1) NOT NULL |
Non-Preferred Merchant
Y/N Table Edit
Default Value: N
|
| 40 |
TAX_IMPLICATIONS |
Character(1) |
VARCHAR2(1) NOT NULL |
Items with Tax Implications
Y/N Table Edit
Default Value: N
|
| 41 |
DUPLICATES_EXIST |
Character(1) |
VARCHAR2(1) NOT NULL |
Duplicates Exist
Y/N Table Edit
Default Value: N
|
| 42 |
BILL_CODE_EX |
Character(3) |
VARCHAR2(3) NOT NULL |
Expense Billing Code
Prompt Table:
EX_BILL_CD_TBL
|
| 43 |
BILLING_ACTION |
Character(1) |
VARCHAR2(1) NOT NULL |
Billing Action
B=Billable
I=Internal
P=Personal
U=Nonbillable
|
| 44 |
OVRRIDE_SPLT_SW |
Character(1) |
VARCHAR2(1) NOT NULL |
Override Accounting Split
Y/N Table Edit
Default Value: N
|
| 45 |
TRAVEL_AUTH_ID |
Character(10) |
VARCHAR2(10) NOT NULL |
Travel Authorization Identification. Number used to define travel authorizations. Key field on most travel authorization records and exists at level 0 for travel authorization panels.
|
| 46 |
GROSSUP |
Character(1) |
VARCHAR2(1) NOT NULL |
Gross Up Indicator
Y/N Table Edit
Default Value: N
|
| 47 |
SEPCHK |
Number(1,0) |
SMALLINT NOT NULL |
Separate Check Indicator
|
| 48 |
EX_CREDIT_REF |
Character(60) |
VARCHAR2(60) NOT NULL |
Informational field used for reference when a credit line
is entered in an expense sheet.
|
| 49 |
TRANS_DT_JULIAN |
Number(3,0) |
SMALLINT NOT NULL |
Julian Transaction Date
|
| 50 |
STARTTM_PERDIEM |
Time(15) |
TIMESTAMP |
Start Time (HH:MI)
Default Value: 00:00
|
| 51 |
ENDTM_PERDIEM |
Time(15) |
TIMESTAMP |
End Time (HH:MI)
Default Value: 23:59
|
| 52 |
CRDMEM_ACCT_NBR |
Character(44) |
VARCHAR2(44) NOT NULL |
Cardmember Number
|
| 53 |
BREAKFAST_FLG |
Character(1) |
VARCHAR2(1) NOT NULL |
Breakfast Provided
Y/N Table Edit
Default Value: N
|
| 54 |
LUNCH_FLG |
Character(1) |
VARCHAR2(1) NOT NULL |
Lunch Provided
Y/N Table Edit
Default Value: N
|
| 55 |
DINNER_FLG |
Character(1) |
VARCHAR2(1) NOT NULL |
Dinner Provided
Y/N Table Edit
Default Value: N
|
| 56 |
COUNTRY |
Character(3) |
VARCHAR2(3) NOT NULL |
Country
Prompt Table:
COUNTRY_TBL
|
| 57 |
STATE |
Character(6) |
VARCHAR2(6) NOT NULL |
State
Prompt Table:
STATE_TBL
|
| 58 |
VAT_RECEIPT |
Character(1) |
VARCHAR2(1) NOT NULL |
No VAT Receipt
Y/N Table Edit
Default Value: N
|
| 59 |
VAT_ENTRD_AMT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Entered VAT Amount
|
| 60 |
VAT_NRCVR_PRO_FLG |
Character(1) |
VARCHAR2(1) NOT NULL |
Prorate Non-Recoverable VAT
Y/N Table Edit
|
| 61 |
VAT_NRCVR_ALL_FLG |
Character(1) |
VARCHAR2(1) NOT NULL |
Allocate Non-Recoverable VAT
Y/N Table Edit
|
| 62 |
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
|
| 63 |
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.
|
| 64 |
VAT_TREATMENT |
Character(4) |
VARCHAR2(4) NOT NULL |
VAT Treatment
|
| 65 |
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
|
| 66 |
VAT_RECOVERY_PCT |
Signed Number(7,2) |
DECIMAL(5,2) NOT NULL |
VAT Recovery Percent
|
| 67 |
VAT_REBATE_PCT |
Signed Number(7,2) |
DECIMAL(5,2) NOT NULL |
VAT Rebate Percent
|
| 68 |
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
|
| 69 |
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
|
| 70 |
VAT_USE_ID |
Character(6) |
VARCHAR2(6) NOT NULL |
VAT Use Type
Prompt Table:
VAT_USE_TBL
|
| 71 |
VAT_RECLAIM_PCT |
Signed Number(7,2) |
DECIMAL(5,2) NOT NULL |
VAT Reclaim Percent
|
| 72 |
VAT_RCLM_AMT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
VAT Reclaim Amount
|
| 73 |
VAT_RCLM_AMT_BSE |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Base VAT Reclaim Amount
|
| 74 |
VAT_NONTX_AMT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Non-Taxable Amount
|
| 75 |
VAT_NONTX_AMT_BSE |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Base Non-Taxable Amount
|
| 76 |
VAT_AMT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Specifies the amount of VAT associated with a transaction such as an invoice or a purchase order. In PeopleSoft Receivables this field is populated when the VAT declaration point is set to Invoice.
|
| 77 |
VAT_AMT_BSE |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
VAT Amount in Base Curr
|
| 78 |
VAT_AUTO_CALC_FLG |
Character(1) |
VARCHAR2(1) NOT NULL |
Automatically Calculate VAT
N=No
Y=Yes
Y/N Table Edit
|
| 79 |
TXN_CURRENCY_CD1 |
Character(3) |
VARCHAR2(3) NOT NULL |
Transaction Currency Display
|
| 80 |
CURRENCY_CD1 |
Character(3) |
VARCHAR2(3) NOT NULL |
Currency Code
|