EX_SHEETLN_VW

(SQL View)
 Index Back

Line View for MAP


SELECT A.SHEET_ID , B.LINE_NBR , A.EMPLID , A.EMPL_RCD , A.SHEET_STATUS , B.EX_LINE_STATUS , A.BUSINESS_UNIT_GL , A.CREATION_DT , A.ADVANCE_ID , A.APPROVAL_DT , A.ACCOUNTING_DT , A.ADVANCE_AMT , A.POST_STATUS_EX , B.EXPEND_MTHD , C.EXPEND_MTHD_EDIT , C.VENDOR_SETID , C.VENDOR_ID , B.REIMBURSEMENT_CD , B.MONETARY_AMOUNT , B.CURRENCY_CD , A.COUNTRY , B.TRANS_DT , B.EXPENSE_TYPE , B.TXN_AMOUNT , B.TXN_CURRENCY_CD , B.RATE_MULT , B.RATE_DIV , B.RT_TYPE , B.GROSSUP , B.DESCR254 , B.TXN_LOCATION , B.BILL_CODE_EX , A.PROCESS_INSTANCE , A.PROCESS_FLAG , A.RECEIPT_ID , A.REFERENCE_ID , B.AIRFARE_RCPT_NBR , B.CRDMEM_ACCT_NBR , B.APPROVE_FLAG , B.OUT_OF_POLICY , B.OUT_OF_POLICY_TYPE , B.EE_LOCAMT_COMMENT , B.EE_LOCAMT_GRP_CMMT , B.NO_RECEIPT_FLG , B.EE_NORCPT_COMMENT , B.PREF_MRCH_NOT_USED , B.EE_PRFMRC_COMMENT , B.OLDER_TRANSACTION , B.EE_OLDTXN_COMMENT , B.DUPLICATES_EXIST FROM PS_EX_SHEET_HDR A , PS_EX_SHEET_LINE B , PS_EX_EXP_MTHD_TBL C WHERE B.EXPEND_MTHD = C.EXPEND_MTHD AND A.SETID = C.SETID AND C.EFFDT=( SELECT MAX(X.EFFDT) FROM PS_EX_EXP_MTHD_TBL X WHERE X.SETID = C.SETID AND X.EXPEND_MTHD = C.EXPEND_MTHD AND X.EFFDT <= %CurrentDateIn) AND C.EFF_STATUS = 'A' AND B.SHEET_ID = A.SHEET_ID

# 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 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
4 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
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 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
7 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit
8 CREATION_DT Date(10) DATE Creation Date
9 ADVANCE_ID Character(10) VARCHAR2(10) NOT NULL Advance ID
10 APPROVAL_DT Date(10) DATE Date of Approval
11 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
12 ADVANCE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Advance Amount
13 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
14 EXPEND_MTHD Character(3) VARCHAR2(3) NOT NULL Payment Type
15 EXPEND_MTHD_EDIT Character(3) VARCHAR2(3) NOT NULL Payment Type Edit
EMP=Employee
PPA=Prepaid Airfare
PPD=Prepaid
PPH=Prepaid Hotel
VND=Supplier
16 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
17 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
18 REIMBURSEMENT_CD Character(1) VARCHAR2(1) NOT NULL Deny
N=Nonreimburseable
P=Prepaid
R=Reimburseable
19 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.
20 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
21 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
22 TRANS_DT Date(10) DATE Transaction Date
23 EXPENSE_TYPE Character(7) VARCHAR2(7) NOT NULL Expense Type
24 TXN_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Transaction Amount
25 TXN_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Transaction Currency
26 RATE_MULT Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier
27 RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL Rate Divisor
28 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.
29 GROSSUP Character(1) VARCHAR2(1) NOT NULL Gross Up Indicator
30 DESCR254 Character(254) VARCHAR2(254) NOT NULL Description of length 254
31 TXN_LOCATION Character(5) VARCHAR2(5) NOT NULL Expense Location
32 BILL_CODE_EX Character(3) VARCHAR2(3) NOT NULL Expense Billing Code
33 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
34 PROCESS_FLAG Character(1) VARCHAR2(1) NOT NULL Process Flag
A=Purge All
S=Only purge items with at least
35 RECEIPT_ID Character(10) VARCHAR2(10) NOT NULL Receipt ID
36 REFERENCE_ID Character(10) VARCHAR2(10) NOT NULL Reference ID
37 AIRFARE_RCPT_NBR Character(16) VARCHAR2(16) NOT NULL Airfare Receipt Number
38 CRDMEM_ACCT_NBR Character(44) VARCHAR2(44) NOT NULL Cardmember Number

Prompt Table: EX_EMP_CARD_VW

39 APPROVE_FLAG Character(1) VARCHAR2(1) NOT NULL Approve Flag
40 OUT_OF_POLICY Character(1) VARCHAR2(1) NOT NULL Authorized Amount Exceeded
41 OUT_OF_POLICY_TYPE Character(4) VARCHAR2(4) NOT NULL Type of Out of Poclicy
BOTH=Both
GRP=Group
INDV=Individual
42 EE_LOCAMT_COMMENT Character(60) VARCHAR2(60) NOT NULL Location Amount Comment
43 EE_LOCAMT_GRP_CMMT Character(60) VARCHAR2(60) NOT NULL Location Amount Comment
44 NO_RECEIPT_FLG Character(1) VARCHAR2(1) NOT NULL No Receipt
45 EE_NORCPT_COMMENT Character(60) VARCHAR2(60) NOT NULL No Receipt Comment
46 PREF_MRCH_NOT_USED Character(1) VARCHAR2(1) NOT NULL Non-Preferred Merchant
47 EE_PRFMRC_COMMENT Character(60) VARCHAR2(60) NOT NULL Preferred Vendor Comment
48 OLDER_TRANSACTION Character(1) VARCHAR2(1) NOT NULL Older Transactions
49 EE_OLDTXN_COMMENT Character(60) VARCHAR2(60) NOT NULL Older Transactions Comment
50 DUPLICATES_EXIST Character(1) VARCHAR2(1) NOT NULL Duplicates Exist