EX_SES_BASE_VW

(SQL View)
 Index Back

Expense Base View for PTSF

Expense Base View for PTSF

SELECT A.SHEET_ID , A.LINE_NBR , B.BUSINESS_UNIT_GL , B.SHEET_NAME , B.SHEET_STATUS , B.SUBMISSION_DATE , B.CREATION_DT , B.REFERENCE_ID , B.BUSINESS_PURPOSE , D.SETID , B.COMMENTS , B.POST_STATUS_EX , B.EMPLID , B.ACCOUNTING_DT , B.OPRID_ENTERED_BY , A.DESCR254 , A.DESCR60 , A.EE_DPLCAT_COMMENT , A.EE_LOCAMT_COMMENT , A.EE_NORCPT_COMMENT , A.EE_PRFMRC_COMMENT , A.EXPENSE_TYPE , D.SETID , A.MERCHANT , A.MERCHANT_CD , D.SETID , A.MONETARY_AMOUNT , A.CURRENCY_CD , A.NO_RECEIPT_FLG , A.OUT_OF_POLICY , A.PREF_MRCH_NOT_USED , A.TRANS_DT , A.RECEIPT_VERIFIED , A.TXN_LOCATION , D.SETID , CASE WHEN (A.OUT_OF_POLICY = 'Y' OR A.NO_RECEIPT_FLG = 'Y' OR A.PREF_MRCH_NOT_USED = 'Y') THEN 'Y' ELSE 'N' END , CASE WHEN A.LASTUPDDTTM > B.LASTUPDDTTM THEN A.LASTUPDDTTM ELSE B.LASTUPDDTTM END , %Substring(%Cast(B.CREATION_DT, Date, Character), 6, 2) , %Substring(%Cast(B.CREATION_DT, Date, Character), 6, 2) , ' ' , ( SELECT B.DESCR FROM %Table(EX_MERCH2_VW) B WHERE D.SETID = B.SETID AND A.MERCHANT_CD = B.MERCHANT_CD ) , CASE WHEN A.MERCHANT_CD <> ' ' THEN ( SELECT B.DESCR FROM %Table(EX_MERCH2_VW) B WHERE D.SETID = B.SETID AND A.MERCHANT_CD = B.MERCHANT_CD ) ELSE A.MERCHANT END FROM %Table(EX_SHEET_LINE) A , %Table(EX_SHEET_HDR) B , %Table(SET_CNTRL_GROUP) D WHERE A.SHEET_ID = B.SHEET_ID AND B.BUSINESS_UNIT_GL = D.SETCNTRLVALUE AND D.REC_GROUP_ID = 'EX_02'

  • Related Language Record: EX_SES_BASE_LVW

    • # 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]
    3 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit
    4 SHEET_NAME Character(30) VARCHAR2(30) NOT NULL Report Description
    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
    6 SUBMISSION_DATE Date(10) DATE Submission Date
    7 CREATION_DT Date(10) DATE Creation Date
    8 REFERENCE_ID Character(10) VARCHAR2(10) NOT NULL Reference ID
    9 BUSINESS_PURPOSE Character(5) VARCHAR2(5) NOT NULL Business Purpose
    10 SETID3 Character(5) VARCHAR2(5) NOT NULL 3rd SetId
    11 COMMENTS Long Character CLOB Comment
    12 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
    13 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    14 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
    15 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    16 DESCR254 Character(254) VARCHAR2(254) NOT NULL Description of length 254
    17 DESCR60 Character(60) VARCHAR2(60) NOT NULL Description
    18 EE_DPLCAT_COMMENT Character(80) VARCHAR2(80) NOT NULL Duplicate Comment
    19 EE_LOCAMT_COMMENT Character(60) VARCHAR2(60) NOT NULL Location Amount Comment
    20 EE_NORCPT_COMMENT Character(60) VARCHAR2(60) NOT NULL No Receipt Comment
    21 EE_PRFMRC_COMMENT Character(60) VARCHAR2(60) NOT NULL Preferred Vendor Comment
    22 EXPENSE_TYPE Character(7) VARCHAR2(7) NOT NULL Expense Type
    23 SETID4 Character(5) VARCHAR2(5) NOT NULL 4th SetId
    24 MERCHANT Character(40) VARCHAR2(40) NOT NULL Merchant
    25 MERCHANT_CD Character(10) VARCHAR2(10) NOT NULL Preferred Merchant
    26 SETID2 Character(5) VARCHAR2(5) NOT NULL 2nd SetId
    27 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.
    28 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    29 NO_RECEIPT_FLG Character(1) VARCHAR2(1) NOT NULL No Receipt
    30 OUT_OF_POLICY Character(1) VARCHAR2(1) NOT NULL Authorized Amount Exceeded
    31 PREF_MRCH_NOT_USED Character(1) VARCHAR2(1) NOT NULL Non-Preferred Merchant
    32 TRANS_DT Date(10) DATE Transaction Date
    33 RECEIPT_VERIFIED Character(1) VARCHAR2(1) NOT NULL Receipt Verified
    34 TXN_LOCATION Character(5) VARCHAR2(5) NOT NULL Expense Location
    35 SETID1 Character(5) VARCHAR2(5) NOT NULL SetID Value
    36 EXCPTN_ON_FLG Character(1) VARCHAR2(1) NOT NULL Budget Exceptions
    37 SES_LAST_DTTM DateTime(26) TIMESTAMP PTSF Last Modified DateTimestamp
    38 SES_MONTH Character(2) VARCHAR2(2) NOT NULL Month
    01=01: January
    02=02: February
    03=03: March
    04=04: April
    05=05: May
    06=06: June
    07=07: July
    08=08: August
    09=09: September
    10=10: October
    11=11: November
    12=12: December
    39 SES_MONTH2 Character(2) VARCHAR2(2) NOT NULL Month
    01=01: January
    02=02: February
    03=03: March
    04=04: April
    05=05: May
    06=06: June
    07=07: July
    08=08: August
    09=09: September
    10=10: October
    11=11: November
    12=12: December
    40 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
    41 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    42 DESCR40 Character(40) VARCHAR2(40) NOT NULL Description