EX_SHEET_HDR_J

(SQL View)
Index Back

Expense Sheet Header

This is one of the core records for Expenses. It is the highest level definition for an Expense Sheet. The key is SHEET_ID, which is generated automatically by the system by using a counter that exists on INSTALLTION_EX.

SELECT A.SHEET_ID ,A.EMPLID ,A.SHEET_STATUS ,A.EX_ORIGIN ,A.APPROVAL_LEVEL ,A.CREATION_DT ,A.APPROVAL_DT ,A.AUDIT_DT ,A.AUDIT_OPRID ,A.ADVANCE_AMT ,A.ADVANCE_ID ,A.SHEET_ID_RELATED ,A.SHEET_TYPE ,A.PP_DENIAL_ADV ,A.CREDIT_ADV ,A.SUBMISSION_DATE ,A.RESUBMISSION_DT ,A.LASTUPDDTTM ,A.OPRID_LAST_UPDT ,A.RCPT_VERIFY_DT ,A.RCPT_VERIFY_OPRID ,A.RCPT_VERIF_LEVEL ,A.RECEIPTS_RECEIVED ,A.RECEIPT_VERIFIED ,A.DST_CNTRL_ID ,A.REFERENCE_ID ,A.TEMPLATE ,A.SHEET_NAME ,A.COPY_EXPENSE_SHEET ,A.SETID ,A.BUSINESS_UNIT_GL ,A.EMPL_RCD ,A.RECEIPT_ID ,A.POST_STATUS_EX ,A.ACCOUNTING_DT ,A.PROCESS_INSTANCE ,A.PROCESS_FLAG ,A.UNPOST_SEQ ,A.REVERSAL_DATE ,A.BUSINESS_PURPOSE ,A.DOC_TYPE ,A.DOC_SEQ_NBR ,A.DOC_SEQ_DATE ,A.OPRID_ENTERED_BY ,A.TRAVEL_AUTH_ID ,A.PROJ_MGR_FLAG ,A.OUT_OF_POLICY ,A.VAT_ENTRIES ,A.NO_RECEIPT_FLG ,A.VAT_RECEIPT ,A.TAX_IMPLICATIONS ,A.DUPLICATES_EXIST ,A.PREF_MRCH_NOT_USED ,A.RISK_EXISTS_FLG ,A.BUDGET_HDR_STATUS ,A.KK_TRAN_OVER_FLAG ,A.KK_TRAN_OVER_OPRID ,A.KK_TRAN_OVER_DTTM ,A.KK_AMOUNT_TYPE ,A.COUNTRY ,A.VAT_ENTITY ,A.COMMENTS ,A.TOTAL_AMT ,A.TOTAL_REIMB_AMT ,A.CURRENCY_CD FROM PS_EX_SHEET_HDR A

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SHEET_ID Character(10) VARCHAR2(10) NOT NULL Report ID

Default Value: NEXT

2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
3 SHEET_STATUS Character(3) VARCHAR2(3) NOT NULL Report Status
APY=Approved for Payment
APZ=Approved For Zero Payment
CLS=Closed
DEN=Denied
DNA=Denied by Approver
DNU=Denied by Auditor
ESC=Escheated Payment
HDA=On Hold, with Approver
HDU=On Hold, with Auditor
HLD=On Hold
MFS=Marked for Submit
OPN=Open
PAR=Approvals in Process
PD=Paid
PND=Pending
PRO=In Process
RAP=Approved
SFA=Submission in Process
STG=Staged
SUB=Submitted for Approval
XML=Submitted, Pending Validation

Default Value: PND

4 EX_ORIGIN Character(1) VARCHAR2(1) NOT NULL Origin of Entry (Expenses)
A=Asset Lifecycle Management
H=Mobile Hand Held Device
M=Mobile Time and Expense
N=NUI
P=Primavera P6
W=Web
5 APPROVAL_LEVEL Character(1) VARCHAR2(1) NOT NULL Approval Level
A=Approver Only
B=Pre- and Post-Pay Audit
E=Approver & Pre-Pay Auditor
I=Approve, Pre- & Post-Pay Audit
N=None
S=Approver & Post-Pay Auditor
T=Auditor Only - Post-Payment
U=Auditor Only - Pre-Payment
6 CREATION_DT Date(10) DATE Creation Date

Default Value: %date

7 APPROVAL_DT Date(10) DATE Date of Approval
8 AUDIT_DT Date(10) DATE Date of Audit
9 AUDIT_OPRID Character(30) VARCHAR2(30) NOT NULL The OPRID of the user who made the changes audited on an audit record.
10 ADVANCE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Advance Amount
11 ADVANCE_ID Character(10) VARCHAR2(10) NOT NULL Advance ID
12 SHEET_ID_RELATED Character(10) VARCHAR2(10) NOT NULL Journal Report ID

Prompt Table: EX_JRNL_PMPT_VW

13 SHEET_TYPE Character(1) VARCHAR2(1) NOT NULL Expense Sheet Type
J=Journal Sheet
R=Regular Sheet

Default Value: R

14 PP_DENIAL_ADV Character(10) VARCHAR2(10) NOT NULL Cash advance created from expense items denied on an expense sheet that has already been paid.
15 CREDIT_ADV Character(10) VARCHAR2(10) NOT NULL Credit Advance
16 SUBMISSION_DATE Date(10) DATE Submission Date
17 RESUBMISSION_DT Date(10) DATE Resubmission Date
18 LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.
19 OPRID_LAST_UPDT Character(30) VARCHAR2(30) NOT NULL Last User to Update
20 RCPT_VERIFY_DT Date(10) DATE Date of Verification
21 RCPT_VERIFY_OPRID Character(30) VARCHAR2(30) NOT NULL Verification UserID
22 RCPT_VERIF_LEVEL Character(1) VARCHAR2(1) NOT NULL Receipt Verification Level - Determines to what level the receipts on expense sheets need to be verified.
B=Both Received & Verified
N=Neither need Receive or Verfy
R=Receipts Received
V=Receipts Verified
23 RECEIPTS_RECEIVED Character(1) VARCHAR2(1) NOT NULL Receipts Received

Y/N Table Edit

Default Value: N

24 RECEIPT_VERIFIED Character(1) VARCHAR2(1) NOT NULL Receipt Verified

Y/N Table Edit

Default Value: N

25 DST_CNTRL_ID Character(10) VARCHAR2(10) NOT NULL Accounting Template

Default Value: BUS_UNIT_TBL_EX.DST_CNTRL_ID

Prompt Table: DST_CNTRL_TBL

26 REFERENCE_ID Character(10) VARCHAR2(10) NOT NULL Reference ID
27 TEMPLATE Character(8) VARCHAR2(8) NOT NULL Used to define a particular type of travel, i.e. company relocation, internal travel, etc. Determines what type of expenses and expenditure limits are associated with that particular type of travel. Established in the Structure Expenses system and chosen on the header panel of cash advances, expense sheets and travel authorizations.
28 SHEET_NAME Character(30) VARCHAR2(30) NOT NULL Report Description
29 COPY_EXPENSE_SHEET Character(1) VARCHAR2(1) NOT NULL Copy From Existing Report

Y/N Table Edit

Default Value: N

30 SETID Character(5) VARCHAR2(5) NOT NULL SetID
31 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit

Default Value: EX_AA_EE_ORG_VW.BUSINESS_UNIT_GL

32 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr

Default Value: EX_AA_EE_ORG_VW.EMPL_RCD

33 RECEIPT_ID Character(10) VARCHAR2(10) NOT NULL Receipt ID
34 POST_STATUS_EX Character(1) VARCHAR2(1) NOT NULL Expenses Post Status
C=Closed
M=Marked for Unpost
N=Not Applied
O=Unpost In Process
P=Posted
S=Close In Process
U=Unposted

Default Value: N

35 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
36 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
37 PROCESS_FLAG Character(1) VARCHAR2(1) NOT NULL Process Flag
A=Purge All
S=Only purge items with at least

Default Value: N

38 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".
39 REVERSAL_DATE Date(10) DATE Specifies the date for an automatic reversal journal entry. The date must be within the calendar of the target (posting) ledger. PeopleSoft General Ledger automatically creates and populates a journal entry with this date.
40 BUSINESS_PURPOSE Character(5) VARCHAR2(5) NOT NULL Business Purpose

Prompt Table: EX_PURPOSE_TBL

41 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.

Prompt Table: DOC_TYPE_A_VW

42 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.
43 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.
44 OPRID_ENTERED_BY Character(30) VARCHAR2(30) NOT NULL Entered By 07/25/2011 MRAD 12383033 :Ensured that OPRID_ENTERED_BY is set with format type of MixedCase. 03/22/2013 GL 16482301: Switched OPRID_ENTERED_BY back to MixedCase again. Please don't change it to UpperCase!!! FYI - The alternatives to use a User ID as uppercase: 1) Create your own User ID, add comments in the Field Properties, and fill out the Owner ID 2) Use %Upper meta-SQL in SQL statements 3) Use Upper function in peoplecodes
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 PROJ_MGR_FLAG Character(1) VARCHAR2(1) NOT NULL Project Manager Review

Default Value: N

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

Y/N Table Edit

Default Value: N

48 VAT_ENTRIES Character(1) VARCHAR2(1) NOT NULL VAT Items

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

54 RISK_EXISTS_FLG Character(1) VARCHAR2(1) NOT NULL Risk Exists

Y/N Table Edit

Default Value: N

55 BUDGET_HDR_STATUS Character(1) VARCHAR2(1) NOT NULL Budget Checking Header Status
E=Error in Budget Check
I=Document In Processing
N=Not Budget Checked
P=Provisionally Valid
V=Valid

Default Value: N

56 KK_TRAN_OVER_FLAG Character(1) VARCHAR2(1) NOT NULL Commitment Control source transaction override flag
N=No
Y=Yes

Y/N Table Edit

57 KK_TRAN_OVER_OPRID Character(30) VARCHAR2(30) NOT NULL Commitment Control source transaction override operator
58 KK_TRAN_OVER_DTTM DateTime(26) TIMESTAMP Commitment Control source transaction override datetime
59 KK_AMOUNT_TYPE Character(1) VARCHAR2(1) NOT NULL Commitment Control Amount Type
0=Budget
1=Actuals and Recognized
2=Encumbrance
3=Pre-Encumbrance
4=Collected Revenue
5=Planned
7=Actuals, Recognize and Collect
9=Dynamic
60 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
61 VAT_ENTITY Character(20) VARCHAR2(20) NOT NULL Specifies an organizational entity that is responsible for VAT reporting and that is registered in one or more countries that require VAT reporting. A single VAT entity is linked to one or more General Ledger Business Units depending on the reporting requirements.
62 COMMENTS Long Character CLOB Comment
63 TOTAL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Amount
64 TOTAL_REIMB_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Due Employee
65 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code