EX_SHEET_LINE_J

(SQL View)
 Index Back

Expense Report Line

Expense Report Line

SELECT A.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_USR_DFN , 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.DESCR254 , 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.CC_FEED_FLG , 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) , A.AIR_TKT_RECON_STAT ,'N' AS JOURNAL_LINE_SEL , A.TAX_AUTHORITY , A.MERCHANT_LOC_STATE , A.SHIPTO_LOC_STATE , A.TXN_LOCATION_STATE , A.MERCHANT_LOCATION , A.SHIPTO_LOCATION , A.GST_EXEMPT , A.MERCHANT_REG_NUM , A.RECEIPT_NUMBER , A.TAX_CODE , A.TAX_JURISDICTION_L , A.HSN_CD , A.SAC_CD , A.TAXABLE_AMT , A.TAXABLE_AMT_BSE , A.CGST_AMT , A.CGST_AMT_BSE , A.CGST_ENTERED_AMT , A.CGST_RATE , A.IGST_AMT , A.IGST_AMT_BSE , A.IGST_ENTERED_AMT , A.IGST_RATE , A.SGST_AMT , A.SGST_AMT_BSE , A.SGST_ENTERED_AMT , A.SGST_RATE , A.EE_GST_COMMENT , A.CGST_CALC_AMT , A.IGST_CALC_AMT , A.SGST_CALC_AMT , A.CGST_CALC_AMT_BSE , A.IGST_CALC_AMT_BSE , A.SGST_CALC_AMT_BSE FROM PS_EX_SHEET_LINE A

# 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_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
18 RATE_MULT Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier
19 RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL Rate Divisor

Default Value: 1

20 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

21 GROSS_UP_EST_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Gross Up Estimated Amount
22 TXN_LOCATION Character(5) VARCHAR2(5) NOT NULL Expense Location

Prompt Table: EX_LOCATION_VW

23 EXPEND_MTHD Character(3) VARCHAR2(3) NOT NULL Payment Type

Prompt Table: EX_EXP_MTHD_TBL
Set Control Field: BUSINESS_UNIT_GL

24 PREF_MRCHNT_FLG Character(1) VARCHAR2(1) NOT NULL Preferred Merchant
N=No
Y=Yes

Y/N Table Edit

Default Value: N

25 MERCHANT_CD Character(10) VARCHAR2(10) NOT NULL Preferred Merchant

Prompt Table: EX_MERCHANT_VW

26 MERCHANT Character(40) VARCHAR2(40) NOT NULL Merchant
27 DESCR60 Character(60) VARCHAR2(60) NOT NULL Description
28 DESCR254 Character(254) VARCHAR2(254) NOT NULL Description of length 254
29 AIRFARE_RCPT_NBR Character(16) VARCHAR2(16) NOT NULL Airfare Receipt Number
30 DISTANCE Number(8,2) DECIMAL(7,2) NOT NULL Distance Traveled
31 DISTANCE_TYPE Character(1) VARCHAR2(1) NOT NULL Distance Type
3=Metres
K=Kilometers
M=Miles
32 DISTANCE_RT Number(9,4) DECIMAL(8,4) NOT NULL Standard Distance Rate
33 NBR_NIGHTS Number(3,0) SMALLINT NOT NULL Number of Nights

Default Value: 1

34 EE_LOCAMT_COMMENT Character(60) VARCHAR2(60) NOT NULL Location Amount Comment
35 EE_NORCPT_COMMENT Character(60) VARCHAR2(60) NOT NULL No Receipt Comment
36 EE_PRFMRC_COMMENT Character(60) VARCHAR2(60) NOT NULL Preferred Vendor Comment
37 EE_DPLCAT_COMMENT Character(80) VARCHAR2(80) NOT NULL Duplicate Comment
38 RECEIPT_VERIFIED Character(1) VARCHAR2(1) NOT NULL Receipt Verified

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

40 NO_RECEIPT_FLG Character(1) VARCHAR2(1) NOT NULL No Receipt

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

44 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

45 BILL_CODE_EX Character(3) VARCHAR2(3) NOT NULL Expense Billing Code

Prompt Table: EX_BILL_CD_TBL

46 BILLING_ACTION Character(1) VARCHAR2(1) NOT NULL Billing Action
B=Billable
I=Internal
P=Personal
U=Nonbillable
47 OVRRIDE_SPLT_SW Character(1) VARCHAR2(1) NOT NULL Override Accounting Split

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

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

Prompt Table: COUNTRY_TBL

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

Prompt Table: STATE_TBL

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

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

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

Y/N Table Edit

65 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

66 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.
67 VAT_TREATMENT Character(4) VARCHAR2(4) NOT NULL VAT Treatment
68 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

69 VAT_RECOVERY_PCT Signed Number(7,2) DECIMAL(5,2) NOT NULL VAT Recovery Percent
70 VAT_REBATE_PCT Signed Number(7,2) DECIMAL(5,2) NOT NULL VAT Rebate Percent
71 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
72 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
73 VAT_USE_ID Character(6) VARCHAR2(6) NOT NULL VAT Use Type

Prompt Table: VAT_USE_TBL

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

Y/N Table Edit

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

Default Value: N

85 JOURNAL_LINE_SEL Character(1) VARCHAR2(1) NOT NULL Line Selected

Default Value: N

86 TAX_AUTHORITY Character(8) VARCHAR2(8) NOT NULL Tax Authority
87 MERCHANT_LOC_STATE Character(6) VARCHAR2(6) NOT NULL Merchant location state.
88 SHIPTO_LOC_STATE Character(6) VARCHAR2(6) NOT NULL Ship-to location state.
89 TXN_LOCATION_STATE Character(6) VARCHAR2(6) NOT NULL Transaction location state.
90 MERCHANT_LOCATION Character(5) VARCHAR2(5) NOT NULL Merchant Location
91 SHIPTO_LOCATION Character(5) VARCHAR2(5) NOT NULL Ship-To Location
92 GST_EXEMPT Character(1) VARCHAR2(1) NOT NULL Tax Exempt
93 MERCHANT_REG_NUM Character(30) VARCHAR2(30) NOT NULL Merchant Registration Number
94 RECEIPT_NUMBER Character(30) VARCHAR2(30) NOT NULL Receipt Number
95 TAX_CODE Character(10) VARCHAR2(10) NOT NULL Tax code
96 TAX_JURISDICTION_L Character(10) VARCHAR2(10) NOT NULL Tax Jurisdiction
97 HSN_CD Character(10) VARCHAR2(10) NOT NULL HSN Code
98 SAC_CD Character(10) VARCHAR2(10) NOT NULL SAC Code
99 TAXABLE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Cost of Good(s) or Service(s)
100 TAXABLE_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Cost of Good(s) or Service(s)
101 CGST_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL CGST Tax Amount
102 CGST_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL CGST Tax Amount
103 CGST_ENTERED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Entered CGST Tax Amount
104 CGST_RATE Number(6,2) DECIMAL(5,2) NOT NULL CGST Tax Rate
105 IGST_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL IGST Tax Amount
106 IGST_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL IGST Tax Amount
107 IGST_ENTERED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Entered IGST Tax Amount
108 IGST_RATE Number(6,2) DECIMAL(5,2) NOT NULL IGST Tax Rate
109 SGST_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL SGST Tax Amount
110 SGST_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL SGST Tax Amount
111 SGST_ENTERED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Entered SGST Tax Amount
112 SGST_RATE Number(6,2) DECIMAL(5,2) NOT NULL SGST Tax Rate
113 EE_GST_COMMENT Character(80) VARCHAR2(80) NOT NULL Override GST Amounts Comments
114 CGST_CALC_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Calculated CGST Tax Amount
115 IGST_CALC_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Calculated IGST Tax Amount
116 SGST_CALC_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Calculated SGST Tax Amount
117 CGST_CALC_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Calculated CGST Tax Amount
118 IGST_CALC_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Calculated IGST Tax Amount
119 SGST_CALC_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Calculated SGST Tax Amount