EX_TA_LN_AW_VW2(SQL View) |
Index Back |
|---|---|
Travel Authorizairton LineTravel Authorizairton Line additional information. |
| SELECT A.TRAVEL_AUTH_ID , A.LINE_NBR , B.DESCR , C.DESCR , CASE WHEN A.TRAVEL_FROM <> ' ' THEN D1.DESCR ELSE ' ' END , CASE WHEN A.TRAVEL_TO <> ' ' THEN D.DESCR ELSE ' ' END , 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.MONETARY_AMOUNT , A.CURRENCY_CD , A.TXN_AMOUNT , A.TXN_CURRENCY_CD , CASE WHEN A.REIMBURSEMENT_CD <> 'N' THEN 'Y' ELSE 'N' END , A.EX_LINE_STATUS FROM PS_EX_TAUTH_HLN_VW A LEFT OUTER JOIN PS_EX_CUR_LOC_VW D1 ON A.SETID = D1.SETID AND A.TRAVEL_FROM = D1.TXN_LOCATION 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 |  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. |
| 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 | TRAVEL_FROM_LOC | Character(30) | VARCHAR2(30) NOT NULL | Orignating Location |
| 6 | TXN_LOC | Character(30) | VARCHAR2(30) NOT NULL | Destination Location |
| 7 | LOCATION_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Location |
| 8 | BILLING_ACTION | Character(1) | VARCHAR2(1) NOT NULL |
Billing Action
B=Billable I=Internal P=Personal U=Nonbillable |
| 9 | DESCR254 | Character(254) | VARCHAR2(254) NOT NULL | Description of length 254 |
| 10 | MERCHANT | Character(40) | VARCHAR2(40) NOT NULL | Merchant |
| 11 | TRANS_DT | Date(10) | DATE | Transaction Date |
| 12 | 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. |
| 13 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 14 | TXN_AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Transaction Amount |
| 15 | TXN_CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Transaction Currency |
| 16 | REIMBURSE_IND | Character(1) | VARCHAR2(1) NOT NULL |
Deny
Y/N Table Edit |
| 17 | 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 |