EX_SHEETHDR_VW2

(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 , C.FIRST_NAME , C.LAST_NAME , A.SHEET_STATUS , B.XLATLONGNAME , 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 , %DatePart(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.SHEET_NAME , A.TXN_LOCATION , 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 , D.DESCR , A.APPL_JRNL_ID , A.DOC_TYPE , A.DOC_SEQ_NBR , A.DOC_SEQ_DATE , A.OPRID_ENTERED_BY , A.TRAVEL_AUTH_ID , A.ASSOC_FLG , A.PROJ_MGR_FLAG , A.OUT_OF_POLICY , A.VAT_ENTRIES , A.NO_RECEIPT_FLG , A.VAT_RECEIPT , A.BU_VAT_ENABLED , A.TAX_IMPLICATIONS , A.DUPLICATES_EXIST , A.PREF_MRCH_NOT_USED , A.RISK_EXISTS_FLG , A.CC_FEED_FLG , A.MULT_PER_DIEM , 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.RCPT_RECEIVED_DT , A.PRE_MAX_LIMIT_FLG , A.POST_MAX_LIMIT_FLG , A.TRANS_ID , A.LASTUPDDTTM , A.TOTAL_AMT , A.TOTAL_REIMB_AMT , A.CURRENCY_CD , A.APPR_PROCESS_FLAG , A.DATE_TIME_HM , A.OPRID_LAST_ACTION , A.ACTION_NAME , A.TOTAL_CMPNY_PD_AMT , A.TOTAL_CREDITS_VND FROM PS_EX_SHEET_HDR A LEFT OUTER JOIN PS_EX_PURPOSE_VW D ON D.SETID = A.SETID AND D.BUSINESS_PURPOSE = A.BUSINESS_PURPOSE , XLATTABLE_VW B , PS_PERSONAL_DATA C WHERE B.FIELDNAME = 'SHEET_STATUS' AND B.FIELDVALUE = A.SHEET_STATUS AND A.EMPLID=C.EMPLID

  • Related Language Record: EX_SHEETHDR_VWL

    • # 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 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
    4 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
    5 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

    6 SHEET_STATUS_DESCR Character(30) VARCHAR2(30) NOT NULL Sheet Status Description
    7 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

    Default Value: W

    8 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
    9 CREATION_DT Date(10) DATE Creation Date

    Default Value: %date

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

    Prompt Table: EX_JRNL_VW

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

    Default Value: R

    17 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.
    18 CREDIT_ADV Character(10) VARCHAR2(10) NOT NULL Credit Advance
    19 SUBMISSION_DATE Date(10) DATE Submission Date
    20 RESUBMISSION_DT Date(10) DATE Resubmission Date
    21 LAST_UPDATE_DT Date(10) DATE Last Updated
    22 OPRID_LAST_UPDT Character(30) VARCHAR2(30) NOT NULL Last User to Update
    23 RCPT_VERIFY_DT Date(10) DATE Date of Verification
    24 RCPT_VERIFY_OPRID Character(30) VARCHAR2(30) NOT NULL Verification UserID
    25 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
    26 RECEIPTS_RECEIVED Character(1) VARCHAR2(1) NOT NULL Receipts Received

    Y/N Table Edit

    Default Value: N

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

    Y/N Table Edit

    Default Value: N

    28 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

    29 REFERENCE_ID Character(10) VARCHAR2(10) NOT NULL Reference ID
    30 SHEET_NAME Character(30) VARCHAR2(30) NOT NULL Report Description
    31 TXN_LOCATION Character(5) VARCHAR2(5) NOT NULL Expense Location

    Prompt Table: EX_LOCATION_VW2

    32 COPY_EXPENSE_SHEET Character(1) VARCHAR2(1) NOT NULL Copy From Existing Report

    Y/N Table Edit

    Default Value: N

    33 SETID Character(5) VARCHAR2(5) NOT NULL SetID
    34 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit

    Default Value: EX_AA_EE_ORG_VW.BUSINESS_UNIT_GL

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

    Default Value: EX_AA_EE_ORG_VW.EMPL_RCD

    36 RECEIPT_ID Character(10) VARCHAR2(10) NOT NULL Receipt ID
    37 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

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

    Default Value: N

    41 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".
    42 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.
    43 BUSINESS_PURPOSE Character(5) VARCHAR2(5) NOT NULL Business Purpose

    Prompt Table: EX_PURPOSE_TBL

    44 PURPOSE_DESCR Character(30) VARCHAR2(30) NOT NULL Business Purpose
    45 APPL_JRNL_ID Character(10) VARCHAR2(10) NOT NULL Journal Template
    46 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

    47 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.
    48 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.
    49 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
    50 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.
    51 ASSOC_FLG Character(1) VARCHAR2(1) NOT NULL used for travel reservation association or transactions

    Default Value: N

    52 PROJ_MGR_FLAG Character(1) VARCHAR2(1) NOT NULL Project Manager Review

    Default Value: N

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

    Y/N Table Edit

    Default Value: N

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

    Y/N Table Edit

    Default Value: N

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

    Y/N Table Edit

    Default Value: N

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

    Y/N Table Edit

    Default Value: N

    57 BU_VAT_ENABLED Character(1) VARCHAR2(1) NOT NULL Business Unit is Vat Enabled

    Y/N Table Edit

    Default Value: N

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

    Y/N Table Edit

    Default Value: N

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

    Y/N Table Edit

    Default Value: N

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

    Y/N Table Edit

    Default Value: N

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

    Y/N Table Edit

    Default Value: N

    62 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

    63 MULT_PER_DIEM Character(1) VARCHAR2(1) NOT NULL Multip Per Diem Exist Per Day

    Y/N Table Edit

    Default Value: N

    64 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: V

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

    Y/N Table Edit

    66 KK_TRAN_OVER_OPRID Character(30) VARCHAR2(30) NOT NULL Commitment Control source transaction override operator
    67 KK_TRAN_OVER_DTTM DateTime(26) TIMESTAMP Commitment Control source transaction override datetime
    68 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
    69 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
    70 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.
    71 RCPT_RECEIVED_DT DateTime(26) TIMESTAMP Receipts Received Date
    72 PRE_MAX_LIMIT_FLG Character(1) VARCHAR2(1) NOT NULL Pre-Audit Max Limit Flag

    Default Value: N

    73 POST_MAX_LIMIT_FLG Character(1) VARCHAR2(1) NOT NULL Post Audit Max Limit Flag

    Default Value: N

    74 TRANS_ID Character(50) VARCHAR2(50) NOT NULL Transaction ID
    75 SYNCDTTM DateTime(26) TIMESTAMP Synchronization Last Update Date Time
    76 TOTAL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Amount
    77 TOTAL_REIMB_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Due Employee
    78 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    79 APPR_PROCESS_FLAG Character(1) VARCHAR2(1) NOT NULL Approval in Process Flag

    Y/N Table Edit

    Default Value: N

    80 DATE_TIME_HM DateTime(26) TIMESTAMP Date Timestamp
    81 OPRID_LAST_ACTION Character(30) VARCHAR2(30) NOT NULL Last User to Take Action
    82 ACTION_NAME Character(3) VARCHAR2(3) NOT NULL Action taken on a transaction
    APR=Approved
    AUT=Auto Approved
    BUD=Budget Checked
    DEL=Deleted
    DNY=Denied
    HLD=On Hold
    PAS=Post Approval Submit
    REC=Receipt Verified
    RES=Resubmitted
    REV=Reviewed
    RSN=Reassigned
    SBR=Sent Back For Revision
    SUB=Submitted
    WDR=Withdrawn
    83 TOTAL_CMPNY_PD_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Due Vendor
    84 TOTAL_CREDITS_VND Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Vendor Credits