EX_ER_LN_AW_VW2(SQL View) |
Index Back |
|---|---|
Expense Report LineExpense Report Line additional information. |
| SELECT A.SHEET_ID , A.LINE_NBR , B.DESCR , C.DESCR , CASE WHEN A.TXN_LOCATION <> ' ' THEN D.DESCR ELSE ' ' END , A.BILLING_ACTION , A.DESCR254 , CASE WHEN A.MERCHANT_CD <> ' ' THEN M.DESCR WHEN A.MERCHANT <> ' ' THEN A.MERCHANT ELSE ' 'END , A.TRANS_DT , A.PERSONAL_EXPENSE , A.RECEIPT_VERIFIED , A.RECEIPT_REQ_EX , CASE WHEN A.NO_RECEIPT_FLG = 'Y' THEN 'N' ELSE 'Y' END , A.MONETARY_AMOUNT , A.CURRENCY_CD , A.TXN_AMOUNT , A.TXN_CURRENCY_CD , A.EE_NORCPT_COMMENT , A.EX_LINE_STATUS FROM PS_EX_SHEET_HLN_VW A LEFT OUTER JOIN PS_EX_CUR_LOC_VW D ON A.SETID = D.SETID AND A.TXN_LOCATION = D.TXN_LOCATION LEFT OUTER JOIN PS_EX_MERCH2_VW M ON A.SETID = M.SETID AND A.MERCHANT_CD = M.MERCHANT_CD , PS_EX_TYPES_TBL B , PS_EX_EXP_MTHD_TBL C WHERE A.SETID = B.SETID AND B.EXPENSE_TYPE = A.EXPENSE_TYPE AND B.EFF_STATUS = 'A' AND B.EFFDT = ( SELECT MAX(X.EFFDT) FROM PS_EX_TYPES_TBL X WHERE X.SETID = A.SETID AND X.EXPENSE_TYPE = A.EXPENSE_TYPE AND X.EFF_STATUS = 'A' AND X.EFFDT <= %CurrentDateIn) AND A.SETID = C.SETID AND C.EXPEND_MTHD = A.EXPEND_MTHD AND C.EFF_STATUS = 'A' AND C.EFFDT = ( SELECT MAX(Y.EFFDT) FROM PS_EX_EXP_MTHD_TBL Y WHERE Y.SETID = A.SETID AND Y.EXPEND_MTHD = A.EXPEND_MTHD AND Y.EFF_STATUS = 'A' AND Y.EFFDT <= %CurrentDateIn) |
| # | 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 | EXP_TYPE_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Expense Type |
| 4 | EXPEND_MTHD_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Payment Method |
| 5 | LOCATION_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Location |
| 6 | BILLING_ACTION | Character(1) | VARCHAR2(1) NOT NULL |
Billing Action
B=Billable I=Internal P=Personal U=Nonbillable |
| 7 | DESCR254 | Character(254) | VARCHAR2(254) NOT NULL | Description of length 254 |
| 8 | MERCHANT | Character(40) | VARCHAR2(40) NOT NULL | Merchant |
| 9 | TRANS_DT | Date(10) | DATE | Transaction Date |
| 10 | PERSONAL_EXPENSE | Character(1) | VARCHAR2(1) NOT NULL |
Personal Expense
N=No Y=Yes |
| 11 | RECEIPT_VERIFIED | Character(1) | VARCHAR2(1) NOT NULL | Receipt Verified |
| 12 | RECEIPT_REQ_EX | Character(1) | VARCHAR2(1) NOT NULL | Receipt Required |
| 13 | NO_RECEIPT_FLG | Character(1) | VARCHAR2(1) NOT NULL |
No Receipt
Y/N Table Edit |
| 14 | 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. |
| 15 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 16 | TXN_AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Transaction Amount |
| 17 | TXN_CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Transaction Currency |
| 18 | EE_NORCPT_COMMENT | Character(60) | VARCHAR2(60) NOT NULL | No Receipt Comment |
| 19 | 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 |