EX_SHEET_LINE_T

(SQL View)
 Index Back

Sheet/Travel Auth Line Info

View displaying link between expense sheet and travel auth.

SELECT A.SHEET_ID ,A.LINE_NBR ,A.EX_LINE_STATUS ,A.SHEET_RCPT_NUM ,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.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 ,%subrec(EX_VAT_LINE,A) ,A.GROSSUP ,A.SEPCHK ,A.EX_CREDIT_REF ,A.TRANS_DT_JULIAN ,A.STARTTM_PERDIEM ,A.ENDTM_PERDIEM ,'N' AS JOURNAL_LINE_SEL ,A.AIR_TKT_RECON_STAT , 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 EXPENSE_TYPE Character(7) VARCHAR2(7) NOT NULL Expense Type

Prompt Table: EX_TYPES_TBL

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

Default Value: R

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

Y/N Table Edit

Default Value: N

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

Prompt Table: EX_ACTION_TBL

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

Default Value: BUS_UNIT_TBL_EX.BASE_CURRENCY

Prompt Table: CURRENCY_CD_TBL

12 TXN_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Transaction Amount
13 TXN_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Transaction Currency

Default Value: BUS_UNIT_TBL_EX.BASE_CURRENCY

14 RATE_MULT Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier
15 RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL Rate Divisor

Default Value: 1

16 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

17 TXN_LOCATION Character(5) VARCHAR2(5) NOT NULL Expense Location

Prompt Table: EX_LOCATION_TBL

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

Prompt Table: EX_EXP_MTHD_TBL
Set Control Field: BUSINESS_UNIT_GL

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

Y/N Table Edit

Default Value: N

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

Prompt Table: EX_MERCHANT_VW

21 MERCHANT Character(40) VARCHAR2(40) NOT NULL Merchant
22 DESCR60 Character(60) VARCHAR2(60) NOT NULL Description
23 AIRFARE_RCPT_NBR Character(16) VARCHAR2(16) NOT NULL Airfare Receipt Number
24 DISTANCE Number(8,2) DECIMAL(7,2) NOT NULL Distance Traveled
25 DISTANCE_TYPE Character(1) VARCHAR2(1) NOT NULL Distance Type
3=Metres
K=Kilometers
M=Miles
26 DISTANCE_RT Number(9,4) DECIMAL(8,4) NOT NULL Standard Distance Rate
27 NBR_NIGHTS Number(3,0) SMALLINT NOT NULL Number of Nights

Default Value: 1

28 EE_LOCAMT_COMMENT Character(60) VARCHAR2(60) NOT NULL Location Amount Comment
29 EE_NORCPT_COMMENT Character(60) VARCHAR2(60) NOT NULL No Receipt Comment
30 EE_PRFMRC_COMMENT Character(60) VARCHAR2(60) NOT NULL Preferred Vendor Comment
31 EE_DPLCAT_COMMENT Character(80) VARCHAR2(80) NOT NULL Duplicate Comment
32 RECEIPT_VERIFIED Character(1) VARCHAR2(1) NOT NULL Receipt Verified

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

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

Default Value: EX_BILL_CD_VW.BILL_CODE_EX

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

Y/N Table Edit

Default Value: N

41 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.
42 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

Prompt Table: COUNTRY_TBL

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

Prompt Table: STATE_TBL

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

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

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

Y/N Table Edit

48 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

49 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.
50 VAT_TREATMENT Character(4) VARCHAR2(4) NOT NULL VAT Treatment
51 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

52 VAT_RECOVERY_PCT Signed Number(7,2) DECIMAL(5,2) NOT NULL VAT Recovery Percent
53 VAT_REBATE_PCT Signed Number(7,2) DECIMAL(5,2) NOT NULL VAT Rebate Percent
54 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
55 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
56 VAT_USE_ID Character(6) VARCHAR2(6) NOT NULL VAT Use Type

Prompt Table: VAT_USE_TBL

57 VAT_RECLAIM_PCT Signed Number(7,2) DECIMAL(5,2) NOT NULL VAT Reclaim Percent
58 VAT_RCLM_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL VAT Reclaim Amount
59 VAT_RCLM_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Base VAT Reclaim Amount
60 VAT_NONTX_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Non-Taxable Amount
61 VAT_NONTX_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Base Non-Taxable Amount
62 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.
63 VAT_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL VAT Amount in Base Curr
64 VAT_AUTO_CALC_FLG Character(1) VARCHAR2(1) NOT NULL Automatically Calculate VAT
N=No
Y=Yes

Y/N Table Edit

65 TXN_CURRENCY_CD1 Character(3) VARCHAR2(3) NOT NULL Transaction Currency Display
66 CURRENCY_CD1 Character(3) VARCHAR2(3) NOT NULL Currency Code
67 GROSSUP Character(1) VARCHAR2(1) NOT NULL Gross Up Indicator

Y/N Table Edit

Default Value: N

68 SEPCHK Number(1,0) SMALLINT NOT NULL Separate Check Indicator
69 EX_CREDIT_REF Character(60) VARCHAR2(60) NOT NULL Informational field used for reference when a credit line is entered in an expense sheet.
70 TRANS_DT_JULIAN Number(3,0) SMALLINT NOT NULL Julian Transaction Date
71 STARTTM_PERDIEM Time(15) TIMESTAMP Start Time (HH:MI)

Default Value: 00:00

72 ENDTM_PERDIEM Time(15) TIMESTAMP End Time (HH:MI)

Default Value: 23:59

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

Default Value: N

74 AIR_TKT_RECON_STAT Character(1) VARCHAR2(1) NOT NULL Airline Ticket Reconciliation Status
C=Cancelled
N=Not Reconciled
R=Reconciled
75 TAX_AUTHORITY Character(8) VARCHAR2(8) NOT NULL Tax Authority
76 MERCHANT_LOC_STATE Character(6) VARCHAR2(6) NOT NULL Merchant location state.
77 SHIPTO_LOC_STATE Character(6) VARCHAR2(6) NOT NULL Ship-to location state.
78 TXN_LOCATION_STATE Character(6) VARCHAR2(6) NOT NULL Transaction location state.
79 MERCHANT_LOCATION Character(5) VARCHAR2(5) NOT NULL Merchant Location
80 SHIPTO_LOCATION Character(5) VARCHAR2(5) NOT NULL Ship-To Location
81 GST_EXEMPT Character(1) VARCHAR2(1) NOT NULL Tax Exempt
82 MERCHANT_REG_NUM Character(30) VARCHAR2(30) NOT NULL Merchant Registration Number
83 RECEIPT_NUMBER Character(30) VARCHAR2(30) NOT NULL Receipt Number
84 TAX_CODE Character(10) VARCHAR2(10) NOT NULL Tax code
85 TAX_JURISDICTION_L Character(10) VARCHAR2(10) NOT NULL Tax Jurisdiction
86 HSN_CD Character(10) VARCHAR2(10) NOT NULL HSN Code
87 SAC_CD Character(10) VARCHAR2(10) NOT NULL SAC Code
88 TAXABLE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Cost of Good(s) or Service(s)
89 TAXABLE_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Cost of Good(s) or Service(s)
90 CGST_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL CGST Tax Amount
91 CGST_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL CGST Tax Amount
92 CGST_ENTERED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Entered CGST Tax Amount
93 CGST_RATE Number(6,2) DECIMAL(5,2) NOT NULL CGST Tax Rate
94 IGST_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL IGST Tax Amount
95 IGST_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL IGST Tax Amount
96 IGST_ENTERED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Entered IGST Tax Amount
97 IGST_RATE Number(6,2) DECIMAL(5,2) NOT NULL IGST Tax Rate
98 SGST_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL SGST Tax Amount
99 SGST_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL SGST Tax Amount
100 SGST_ENTERED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Entered SGST Tax Amount
101 SGST_RATE Number(6,2) DECIMAL(5,2) NOT NULL SGST Tax Rate
102 EE_GST_COMMENT Character(80) VARCHAR2(80) NOT NULL Override GST Amounts Comments
103 CGST_CALC_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Calculated CGST Tax Amount
104 IGST_CALC_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Calculated IGST Tax Amount
105 SGST_CALC_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Calculated SGST Tax Amount
106 CGST_CALC_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Calculated CGST Tax Amount
107 IGST_CALC_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Calculated IGST Tax Amount
108 SGST_CALC_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Calculated SGST Tax Amount