EX_SHEET_LN_VW

(SQL View)
 Index Back

Expense Sheet Line View

Expense Sheet Line Information

SELECT A.EMPLID , B.SHEET_ID , B.LINE_NBR , B.EX_LINE_STATUS , B.SHEET_RCPT_NUM , B.SHEET_RCPT_TOTAL , B.ARRIVAL_DATE , B.DEPARTURE_DATE , B.EXPENSE_TYPE , B.TRANS_DT , B.REIMBURSEMENT_CD , B.PERSONAL_EXPENSE , B.REIMB_ACTION , B.MONETARY_AMOUNT , B.CURRENCY_CD , B.TXN_AMOUNT , B.TXN_CURRENCY_CD , B.RATE_USR_DFN , B.RATE_MULT , B.RATE_DIV , B.RT_TYPE , B.GROSS_UP_EST_AMT , B.TXN_LOCATION , B.EXPEND_MTHD , B.PREF_MRCHNT_FLG , B.MERCHANT_CD , B.MERCHANT , B.DESCR60 , B.DESCR254 , B.AIRFARE_RCPT_NBR , B.DISTANCE , B.DISTANCE_TYPE , B.DISTANCE_RT , B.NBR_NIGHTS , B.EE_LOCAMT_COMMENT , B.EE_NORCPT_COMMENT , B.EE_PRFMRC_COMMENT , B.EE_DPLCAT_COMMENT , B.EE_OLDTXN_COMMENT , B.RECEIPT_VERIFIED , B.OUT_OF_POLICY , B.OUT_OF_POLICY_TYPE , B.NO_RECEIPT_FLG , B.PREF_MRCH_NOT_USED , B.TAX_IMPLICATIONS , B.DUPLICATES_EXIST , B.OLDER_TRANSACTION , B.CC_FEED_FLG , B.BILL_CODE_EX , B.BILLING_ACTION , B.OVRRIDE_SPLT_SW , B.TRAVEL_AUTH_ID , B.GROSSUP , B.SEPCHK , B.EX_CREDIT_REF , B.TRANS_DT_JULIAN , B.STARTTM_PERDIEM , B.ENDTM_PERDIEM , B.CRDMEM_ACCT_NBR , B.BREAKFAST_FLG , B.LUNCH_FLG , B.DINNER_FLG , %subrec(EX_VAT_LINE, B) , B.AIR_TKT_RECON_STAT , B.TRANSPORT_ID , B.TRAVEL_FROM , B.NBR_PASSENGERS , B.VAT_DFLT_DONE_FLG , B.RANGE_SEQ , B.RECEIPT_REQ_EX , A.BUSINESS_UNIT_GL , B.TAX_AUTHORITY , B.SHIPTO_LOC_STATE , B.MERCHANT_LOC_STATE , B.TXN_LOCATION_STATE , B.GST_EXEMPT , B.SHIPTO_LOCATION , B.MERCHANT_REG_NUM , B.MERCHANT_LOCATION , B.RECEIPT_NUMBER , B.TAX_CODE , B.TAX_JURISDICTION_L , B.HSN_CD , B.SAC_CD , B.TAXABLE_AMT , B.CGST_AMT , B.SGST_AMT , B.IGST_AMT , B.IGST_RATE , B.SGST_RATE , B.CGST_RATE , B.TAXABLE_AMT_BSE , B.CGST_AMT_BSE , B.IGST_AMT_BSE , B.SGST_AMT_BSE , B.CGST_ENTERED_AMT , B.IGST_ENTERED_AMT , B.SGST_ENTERED_AMT , B.EE_GST_COMMENT , B.CGST_CALC_AMT , B.IGST_CALC_AMT , B.SGST_CALC_AMT , B.CGST_CALC_AMT_BSE , B.IGST_CALC_AMT_BSE , B.SGST_CALC_AMT_BSE ,B.EE_GPS_AMT_COMMENT, %subrec(EX_MILEAGE_SBR, B) FROM PS_EX_SHEET_HDR A , PS_EX_SHEET_LINE B WHERE B.SHEET_ID = A.SHEET_ID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 SHEET_ID Character(10) VARCHAR2(10) NOT NULL Report ID
3 LINE_NBR Number(5,0) INTEGER NOT NULL Line Number: 11/24/08 - Added TARGET label [PC product]

Default Value: 1

4 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
5 SHEET_RCPT_NUM Number(3,0) SMALLINT NOT NULL Receipt Number
6 SHEET_RCPT_TOTAL Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Local Amount
7 ARRIVAL_DATE Date(10) DATE Arrival Date
8 DEPARTURE_DATE Date(10) DATE Departure Date
9 EXPENSE_TYPE Character(7) VARCHAR2(7) NOT NULL Expense Type

Prompt Table: EX_TYPES_TBL

10 TRANS_DT Date(10) DATE Transaction Date
11 REIMBURSEMENT_CD Character(1) VARCHAR2(1) NOT NULL Deny
N=Nonreimburseable
P=Prepaid
R=Reimburseable

Default Value: R

12 PERSONAL_EXPENSE Character(1) VARCHAR2(1) NOT NULL Personal Expense
N=No
Y=Yes

Y/N Table Edit

13 REIMB_ACTION Character(5) VARCHAR2(5) NOT NULL Reason

Prompt Table: EX_ACTION_TBL

14 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.
15 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

Default Value: BUS_UNIT_TBL_EX.BASE_CURRENCY

Prompt Table: CURRENCY_CD_TBL

16 TXN_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Transaction Amount
17 TXN_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Transaction Currency
18 RATE_USR_DFN Character(1) VARCHAR2(1) NOT NULL The field indicates the type of Exchange rate to be used. User defined or system default
D=Custom - Direct
I=Custom - Indirect
S=System Default
19 RATE_MULT Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier
20 RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL Rate Divisor
21 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.
22 GROSS_UP_EST_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Gross Up Estimated Amount
23 TXN_LOCATION Character(5) VARCHAR2(5) NOT NULL Expense Location

Prompt Table: EX_LOCATION_TBL

24 EXPEND_MTHD Character(3) VARCHAR2(3) NOT NULL Payment Type
25 PREF_MRCHNT_FLG Character(1) VARCHAR2(1) NOT NULL Preferred Merchant
N=No
Y=Yes
26 MERCHANT_CD Character(10) VARCHAR2(10) NOT NULL Preferred Merchant
27 MERCHANT Character(40) VARCHAR2(40) NOT NULL Merchant
28 DESCR60 Character(60) VARCHAR2(60) NOT NULL Description
29 DESCR254 Character(254) VARCHAR2(254) NOT NULL Description of length 254
30 AIRFARE_RCPT_NBR Character(16) VARCHAR2(16) NOT NULL Airfare Receipt Number
31 DISTANCE Number(8,2) DECIMAL(7,2) NOT NULL Distance Traveled
32 DISTANCE_TYPE Character(1) VARCHAR2(1) NOT NULL Distance Type
3=Metres
K=Kilometers
M=Miles
33 DISTANCE_RT Number(9,4) DECIMAL(8,4) NOT NULL Standard Distance Rate
34 NBR_NIGHTS Number(3,0) SMALLINT NOT NULL Number of Nights
35 EE_LOCAMT_COMMENT Character(60) VARCHAR2(60) NOT NULL Location Amount Comment
36 EE_NORCPT_COMMENT Character(60) VARCHAR2(60) NOT NULL No Receipt Comment
37 EE_PRFMRC_COMMENT Character(60) VARCHAR2(60) NOT NULL Preferred Vendor Comment
38 EE_DPLCAT_COMMENT Character(80) VARCHAR2(80) NOT NULL Duplicate Comment
39 EE_OLDTXN_COMMENT Character(60) VARCHAR2(60) NOT NULL Older Transactions Comment
40 RECEIPT_VERIFIED Character(1) VARCHAR2(1) NOT NULL Receipt Verified

Y/N Table Edit

Default Value: N

41 OUT_OF_POLICY Character(1) VARCHAR2(1) NOT NULL Authorized Amount Exceeded

Y/N Table Edit

Default Value: N

42 OUT_OF_POLICY_TYPE Character(4) VARCHAR2(4) NOT NULL Type of Out of Poclicy
BOTH=Both
GRP=Group
INDV=Individual
43 NO_RECEIPT_FLG Character(1) VARCHAR2(1) NOT NULL No Receipt

Y/N Table Edit

44 PREF_MRCH_NOT_USED Character(1) VARCHAR2(1) NOT NULL Non-Preferred Merchant

Y/N Table Edit

Default Value: N

45 TAX_IMPLICATIONS Character(1) VARCHAR2(1) NOT NULL Items with Tax Implications

Y/N Table Edit

Default Value: N

46 DUPLICATES_EXIST Character(1) VARCHAR2(1) NOT NULL Duplicates Exist

Y/N Table Edit

Default Value: N

47 OLDER_TRANSACTION Character(1) VARCHAR2(1) NOT NULL Older Transactions
48 CC_FEED_FLG Character(1) VARCHAR2(1) NOT NULL Field used to indicate whether a credit card feed was used to populate an expense sheet or not.

Y/N Table Edit

Default Value: N

49 BILL_CODE_EX Character(3) VARCHAR2(3) NOT NULL Expense Billing Code
50 BILLING_ACTION Character(1) VARCHAR2(1) NOT NULL Billing Action
B=Billable
I=Internal
P=Personal
U=Nonbillable
51 OVRRIDE_SPLT_SW Character(1) VARCHAR2(1) NOT NULL Override Accounting Split

Y/N Table Edit

52 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.
53 GROSSUP Character(1) VARCHAR2(1) NOT NULL Gross Up Indicator

Y/N Table Edit

Default Value: N

54 SEPCHK Number(1,0) SMALLINT NOT NULL Separate Check Indicator
55 EX_CREDIT_REF Character(60) VARCHAR2(60) NOT NULL Informational field used for reference when a credit line is entered in an expense sheet.
56 TRANS_DT_JULIAN Number(3,0) SMALLINT NOT NULL Julian Transaction Date
57 STARTTM_PERDIEM Time(15) TIMESTAMP Start Time (HH:MI)
58 ENDTM_PERDIEM Time(15) TIMESTAMP End Time (HH:MI)
59 CRDMEM_ACCT_NBR Character(44) VARCHAR2(44) NOT NULL Cardmember Number

Prompt Table: EX_EMP_CARD_VW

60 BREAKFAST_FLG Character(1) VARCHAR2(1) NOT NULL Breakfast Provided

Y/N Table Edit

Default Value: N

61 LUNCH_FLG Character(1) VARCHAR2(1) NOT NULL Lunch Provided

Y/N Table Edit

Default Value: N

62 DINNER_FLG Character(1) VARCHAR2(1) NOT NULL Dinner Provided

Y/N Table Edit

Default Value: N

63 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

Prompt Table: COUNTRY_TBL

64 STATE Character(6) VARCHAR2(6) NOT NULL State

Prompt Table: STATE_TBL

65 VAT_RECEIPT Character(1) VARCHAR2(1) NOT NULL No VAT Receipt

Y/N Table Edit

Default Value: N

66 VAT_ENTRD_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Entered VAT Amount
67 VAT_NRCVR_PRO_FLG Character(1) VARCHAR2(1) NOT NULL Prorate Non-Recoverable VAT

Y/N Table Edit

68 VAT_NRCVR_ALL_FLG Character(1) VARCHAR2(1) NOT NULL Allocate Non-Recoverable VAT

Y/N Table Edit

69 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

70 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.
71 VAT_TREATMENT Character(4) VARCHAR2(4) NOT NULL VAT Treatment
72 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

73 VAT_RECOVERY_PCT Signed Number(7,2) DECIMAL(5,2) NOT NULL VAT Recovery Percent
74 VAT_REBATE_PCT Signed Number(7,2) DECIMAL(5,2) NOT NULL VAT Rebate Percent
75 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
76 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
77 VAT_USE_ID Character(6) VARCHAR2(6) NOT NULL VAT Use Type

Prompt Table: VAT_USE_TBL

78 VAT_RECLAIM_PCT Signed Number(7,2) DECIMAL(5,2) NOT NULL VAT Reclaim Percent
79 VAT_RCLM_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL VAT Reclaim Amount
80 VAT_RCLM_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Base VAT Reclaim Amount
81 VAT_NONTX_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Non-Taxable Amount
82 VAT_NONTX_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Base Non-Taxable Amount
83 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.
84 VAT_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL VAT Amount in Base Curr
85 VAT_AUTO_CALC_FLG Character(1) VARCHAR2(1) NOT NULL Automatically Calculate VAT
N=No
Y=Yes

Y/N Table Edit

86 TXN_CURRENCY_CD1 Character(3) VARCHAR2(3) NOT NULL Transaction Currency Display
87 CURRENCY_CD1 Character(3) VARCHAR2(3) NOT NULL Currency Code
88 AIR_TKT_RECON_STAT Character(1) VARCHAR2(1) NOT NULL Airline Ticket Reconciliation Status
C=Cancelled
N=Not Reconciled
R=Reconciled

Default Value: N

89 TRANSPORT_ID Character(15) VARCHAR2(15) NOT NULL Transportation ID
90 TRAVEL_FROM Character(5) VARCHAR2(5) NOT NULL Used to indicate the starting point of travel for a travel authorization for airline expenditures. Defined on the EX_TAUTH_LINE record and used on various travel auth line panels. Used in conjunction with the TRAVEL_TO field to track the cost of flights to and from various locals.
91 NBR_PASSENGERS Number(2,0) SMALLINT NOT NULL Number of Passengers
92 VAT_DFLT_DONE_FLG Character(1) VARCHAR2(1) NOT NULL VAT Defaulting Done
N=Awaiting Defaulting
X=Do not default
Y=Defaulting Done
93 RANGE_SEQ Number(3,0) SMALLINT NOT NULL Per Diem Range
94 RECEIPT_REQ_EX Character(1) VARCHAR2(1) NOT NULL Receipt Required
95 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit
96 TAX_AUTHORITY Character(8) VARCHAR2(8) NOT NULL Tax Authority
97 SHIPTO_LOC_STATE Character(6) VARCHAR2(6) NOT NULL Ship-to location state.
98 MERCHANT_LOC_STATE Character(6) VARCHAR2(6) NOT NULL Merchant location state.
99 TXN_LOCATION_STATE Character(6) VARCHAR2(6) NOT NULL Transaction location state.
100 GST_EXEMPT Character(1) VARCHAR2(1) NOT NULL Tax Exempt
101 SHIPTO_LOCATION Character(5) VARCHAR2(5) NOT NULL Ship-To Location
102 MERCHANT_REG_NUM Character(30) VARCHAR2(30) NOT NULL Merchant Registration Number
103 MERCHANT_LOCATION Character(5) VARCHAR2(5) NOT NULL Merchant Location
104 RECEIPT_NUMBER Character(30) VARCHAR2(30) NOT NULL Receipt Number
105 TAX_CODE Character(10) VARCHAR2(10) NOT NULL Tax code
106 TAX_JURISDICTION_L Character(10) VARCHAR2(10) NOT NULL Tax Jurisdiction
107 HSN_CD Character(10) VARCHAR2(10) NOT NULL HSN Code
108 SAC_CD Character(10) VARCHAR2(10) NOT NULL SAC Code
109 TAXABLE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Cost of Good(s) or Service(s)
110 CGST_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL CGST Tax Amount
111 SGST_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL SGST Tax Amount
112 IGST_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL IGST Tax Amount
113 IGST_RATE Number(6,2) DECIMAL(5,2) NOT NULL IGST Tax Rate
114 SGST_RATE Number(6,2) DECIMAL(5,2) NOT NULL SGST Tax Rate
115 CGST_RATE Number(6,2) DECIMAL(5,2) NOT NULL CGST Tax Rate
116 TAXABLE_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Cost of Good(s) or Service(s)
117 CGST_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL CGST Tax Amount
118 IGST_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL IGST Tax Amount
119 SGST_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL SGST Tax Amount
120 CGST_ENTERED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Entered CGST Tax Amount
121 IGST_ENTERED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Entered IGST Tax Amount
122 SGST_ENTERED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Entered SGST Tax Amount
123 EE_GST_COMMENT Character(80) VARCHAR2(80) NOT NULL Override GST Amounts Comments
124 CGST_CALC_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Calculated CGST Tax Amount
125 IGST_CALC_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Calculated IGST Tax Amount
126 SGST_CALC_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Calculated SGST Tax Amount
127 CGST_CALC_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Calculated CGST Tax Amount
128 IGST_CALC_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Calculated IGST Tax Amount
129 SGST_CALC_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Calculated SGST Tax Amount
130 EE_GPS_AMT_COMMENT Character(60) VARCHAR2(60) NOT NULL GPS amount override exception comment
131 DISTANCE_CALC Number(8,2) DECIMAL(7,2) NOT NULL Calculated Distance
132 STARTING_POINT Character(254) VARCHAR2(254) NOT NULL Starting Point
133 ENDING_POINT Character(254) VARCHAR2(254) NOT NULL Ending Point
134 LATITUDE1 Signed Number(12,7) DECIMAL(10,7) NOT NULL Latitude
135 LONGITUDE1 Signed Number(12,7) DECIMAL(10,7) NOT NULL Longitude
136 START_POINT_SELECT Character(254) VARCHAR2(254) NOT NULL Selected Start Point
137 LATITUDE2 Signed Number(12,7) DECIMAL(10,7) NOT NULL Latitude
138 LONGITUDE2 Signed Number(12,7) DECIMAL(10,7) NOT NULL Longitude
139 END_POINT_SELECT Character(254) VARCHAR2(254) NOT NULL Selected End Point
140 DISTANCE_SELECT Number(8,2) DECIMAL(7,2) NOT NULL Distance