|
SELECT %subrec(EX_TRANS_WC_SBR, A) ,T.DESCR FROM PS_EX_TRANS A , PS_EX_EXP_MTHD_TBL C , PS_EX_TYPES_TBL T WHERE A.PERSONAL_EXPENSE = 'Y' AND (EXISTS ( SELECT B.SHEET_ID FROM PS_EX_SHEET_HDR B WHERE B.SHEET_ID = A.SHEET_ID AND B.SHEET_STATUS NOT IN ('PAR','RAP','APY','APZ','CLS','ESC','PD','STG')) OR A.SHEET_ID = ' ') AND C.EXPEND_MTHD = A.EXPEND_MTHD AND C.EXPEND_MTHD_EDIT IN ('PPD','PPA', 'PPH') AND C.SETID IN ( SELECT G.SETID FROM PS_SET_CNTRL_REC G WHERE G.SETCNTRLVALUE IN ( SELECT J.BUSINESS_UNIT FROM PS_JOB J WHERE J.EMPLID = A.EMPLID AND J.EFFDT = ( SELECT MAX(JOB.EFFDT) FROM PS_JOB JOB WHERE JOB.EMPLID=J.EMPLID AND JOB.EMPL_RCD=( SELECT MAX(J2.EMPL_RCD) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EFFDT = J.EFFDT ) AND JOB.EFFSEQ=( SELECT MAX(J3.EFFSEQ) FROM PS_JOB J3 WHERE J3.EMPLID = J.EMPLID AND J3.EMPL_RCD = J.EMPL_RCD) AND JOB.EFFDT<=A.TRANS_DT)) AND G.REC_GROUP_ID = 'EX_02' AND G.RECNAME = 'EX_EXP_MTHD_TBL') AND %EffdtCheck(EX_EXP_MTHD_TBL, C, A.TRANS_DT) AND C.EFF_STATUS = 'A' AND T.EXPENSE_TYPE = A.EXPENSE_TYPE AND T.EFF_STATUS = 'A' AND T.SETID = C.SETID AND %EffdtCheck(EX_TYPES_TBL, T, A.TRANS_DT)
|
| # |
PeopleSoft Field Name |
PeopleSoft Field Type |
Database Column Type |
Description |
| 1 |
 EMPLID |
Character(11) |
VARCHAR2(11) NOT NULL |
Employee ID
Prompt Table:
PERSONAL_DATA
|
| 2 |
DATA_SOURCE_EX |
Character(3) |
VARCHAR2(3) NOT NULL |
External Data Sources for PS Expenses
AIR=Airline Ticket
AMA=American Express
AMG=American Express
AMH=American Express-Hotel Folio
AMM=American Express - Member List
AMX=American Express
CNS=CONUS
DC=Diners Club
IPH=iReceipts
MC=MasterCard CDFv2
MCX=MasterCard CDFv3
MSG=Text Message
NAP=PS NA Payroll
PDA=PDA
RNZ=Runzheimer
SAB=Sabre
UBT=User Chatbot
USB=US Bank
USR=User Input
VI4=Visa
VIS=Visa
Default Value: USR
|
| 3 |
TRANS_NBR |
Character(24) |
VARCHAR2(24) NOT NULL |
Transaction Number
Default Value: 1
|
| 4 |
SEQ_NBR |
Number(15,0) |
DECIMAL(15) NOT NULL |
Sequence Number
Default Value: 1
|
| 5 |
ITEM_SEQ_NBR |
Number(5,0) |
INTEGER NOT NULL |
Item Sequence Number
Default Value: 0
|
| 6 |
AIRFARE_RCPT_NBR |
Character(16) |
VARCHAR2(16) NOT NULL |
Airfare Receipt Number
|
| 7 |
MOBILE_POPWKEY |
Character(50) |
VARCHAR2(50) NOT NULL |
Mobile Id
|
| 8 |
EX_TRANS_TYPE |
Character(4) |
VARCHAR2(4) NOT NULL |
Expenses Transaction Type, used to categorize external data
that is loaded into the expense product, for example corporate
card expenses/credits, ATM advances and travel bookings.
ALL=All Transaction Types
ATMA=ATM Advance
CCCR=Credit
CCEX=Expense
TRBK=Travel Reservation
|
| 9 |
CCTRANS_TYPE_CD |
Character(2) |
VARCHAR2(2) NOT NULL |
Credit Card Transaction Code
|
| 10 |
EX_DOC_TYPE |
Character(1) |
VARCHAR2(1) NOT NULL |
Identifies Expenses Document Type
A=Cash Advance
C=Time Report
J=Time Adjustment
M=Accrual - My Wallet
R=Accrual - Expense Report
S=Expense Report
T=Travel Authorization
|
| 11 |
ADVANCE_ID |
Character(10) |
VARCHAR2(10) NOT NULL |
Advance ID
Prompt Table:
EX_ADV_HDR
|
| 12 |
ADVANCE_NAME |
Character(30) |
VARCHAR2(30) NOT NULL |
Advance Description
|
| 13 |
SHEET_ID |
Character(10) |
VARCHAR2(10) NOT NULL |
Report ID
Prompt Table:
EX_SHEET_HDR
|
| 14 |
SHEET_NAME |
Character(30) |
VARCHAR2(30) NOT NULL |
Report Description
|
| 15 |
LINE_NBR |
Number(5,0) |
INTEGER NOT NULL |
Line Number:
11/24/08 - Added TARGET label [PC product]
|
| 16 |
EXPENSE_TYPE |
Character(7) |
VARCHAR2(7) NOT NULL |
Expense Type
Prompt Table:
EX_TYPES_TBL
|
| 17 |
RECEIPT_VERIFIED |
Character(1) |
VARCHAR2(1) NOT NULL |
Receipt Verified
Y/N Table Edit
Default Value: N
|
| 18 |
TRANS_DT |
Date(10) |
DATE |
Transaction Date
|
| 19 |
TXN_AMOUNT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Transaction Amount
|
| 20 |
TXN_CURRENCY_CD |
Character(3) |
VARCHAR2(3) NOT NULL |
Transaction Currency
Default Value: BUS_UNIT_TBL_EX.BASE_CURRENCY
Prompt Table:
CURRENCY_CD_TBL
|
| 21 |
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.
|
| 22 |
CURRENCY_CD |
Character(3) |
VARCHAR2(3) NOT NULL |
Currency Code
Default Value: BUS_UNIT_TBL_EX.BASE_CURRENCY
Prompt Table:
CURRENCY_CD_TBL
|
| 23 |
CUR_EXCHNG_RT |
Number(16,8) |
DECIMAL(15,8) NOT NULL |
Currency Exchange Rate
Default Value: 1
|
| 24 |
RATE_USR_DFN |
Character(1) |
VARCHAR2(1) NOT NULL |
The field indicates the type of Exchange rate to be used. User defined or system default
D=Custom - Direct
I=Custom - Indirect
S=System Default
Default Value: S
|
| 25 |
RATE_MULT |
Signed Number(17,8) |
DECIMAL(15,8) NOT NULL |
Rate Multiplier
|
| 26 |
RATE_DIV |
Number(16,8) |
DECIMAL(15,8) NOT NULL |
Rate Divisor
Default Value: 1
|
| 27 |
CITY |
Character(30) |
VARCHAR2(30) NOT NULL |
City
|
| 28 |
COUNTRY |
Character(3) |
VARCHAR2(3) NOT NULL |
Country
Prompt Table:
COUNTRY_TBL
|
| 29 |
TXN_LOCATION |
Character(5) |
VARCHAR2(5) NOT NULL |
Expense Location
Prompt Table:
EX_LOCATION_VW
|
| 30 |
EXPEND_MTHD |
Character(3) |
VARCHAR2(3) NOT NULL |
Payment Type
Prompt Table:
EX_EXP_MTHD_TBL
|
| 31 |
PREF_MRCHNT_FLG |
Character(1) |
VARCHAR2(1) NOT NULL |
Preferred Merchant
N=No
Y=Yes
Y/N Table Edit
Default Value: N
|
| 32 |
MERCHANT_CD |
Character(10) |
VARCHAR2(10) NOT NULL |
Preferred Merchant
Prompt Table:
EX_MERCHANT_VW
|
| 33 |
MERCHANT |
Character(40) |
VARCHAR2(40) NOT NULL |
Merchant
|
| 34 |
NBR_NIGHTS |
Number(3,0) |
SMALLINT NOT NULL |
Number of Nights
Default Value: 0
|
| 35 |
AIR_TKT_RECON_STAT |
Character(1) |
VARCHAR2(1) NOT NULL |
Airline Ticket Reconciliation Status
C=Cancelled
N=Not Reconciled
R=Reconciled
Default Value: N
|
| 36 |
DAYS_OUTSTANDING |
Number(4,0) |
SMALLINT NOT NULL |
Days Outstanding
|
| 37 |
DESCR254 |
Character(254) |
VARCHAR2(254) NOT NULL |
Description of length 254
|
| 38 |
CORP_ACCT_NBR |
Character(20) |
VARCHAR2(20) NOT NULL |
Corporate Number
|
| 39 |
CRDMEM_ACCT_NBR |
Character(44) |
VARCHAR2(44) NOT NULL |
Cardmember Number
Prompt Table:
EX_EMP_CARD_VW2
|
| 40 |
SUPPLIER_NBR |
Character(11) |
VARCHAR2(11) NOT NULL |
Supplier Reference Nbr
|
| 41 |
ADVANCE_SOURCE |
Character(4) |
VARCHAR2(4) NOT NULL |
Advance Source
Prompt Table:
EX_ADV_SRC_TBL
|
| 42 |
TXN_STATUS |
Character(1) |
VARCHAR2(1) NOT NULL |
Transaction Status
A=Assigned
D=Marked for Delete
R=Rejected
T=All Transaction Statuses
U=Unassigned
Default Value: U
|
| 43 |
TXN_LOAD_DATE |
Date(10) |
DATE |
Transaction Load Date
|
| 44 |
DESCR_LN1 |
Character(50) |
VARCHAR2(50) NOT NULL |
Description Line 1
|
| 45 |
DESCR_LN2 |
Character(50) |
VARCHAR2(50) NOT NULL |
Description Line 2
|
| 46 |
DESCR_LN3 |
Character(50) |
VARCHAR2(50) NOT NULL |
Description Line 3
|
| 47 |
DESCR_LN4 |
Character(50) |
VARCHAR2(50) NOT NULL |
Description Line 4
|
| 48 |
EMP_NOTIFY_SW |
Character(1) |
VARCHAR2(1) NOT NULL |
Employee Notified
Y/N Table Edit
Default Value: N
|
| 49 |
MOBILE_DWNLD_FLG |
Character(1) |
VARCHAR2(1) NOT NULL |
Picked Up By Mobile
N=No
Y=Yes
Default Value: N
|
| 50 |
DTTM_STAMP |
DateTime(26) |
TIMESTAMP |
Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.
|
| 51 |
DISTANCE |
Number(8,2) |
DECIMAL(7,2) NOT NULL |
Distance Traveled
|
| 52 |
DISTANCE_TYPE |
Character(1) |
VARCHAR2(1) NOT NULL |
Distance Type
3=Metres
K=Kilometers
M=Miles
|
| 53 |
MOBILE_SYNC_STATUS |
Character(1) |
VARCHAR2(1) NOT NULL |
Marks the synchronization status of a mobile transaction
A=Assigned
N=New
R=Repeat
S=Synchronized
|
| 54 |
EX_DOC_ID |
Character(10) |
VARCHAR2(10) NOT NULL |
Documentation ID
|
| 55 |
PROCESS_FLG |
Character(1) |
VARCHAR2(1) NOT NULL |
Processing Flag
Default Value: N
|
| 56 |
PROCESS_INSTANCE |
Number(10,0) |
DECIMAL(10) NOT NULL |
Process Instance
Default Value: 0
|
| 57 |
PERSONAL_EXPENSE |
Character(1) |
VARCHAR2(1) NOT NULL |
Personal Expense
N=No
Y=Yes
Y/N Table Edit
Default Value: N
|
| 58 |
EX_TYPE_FLAG |
Character(1) |
VARCHAR2(1) NOT NULL |
Expense Type Flag
Y/N Table Edit
Default Value: N
|
| 59 |
ENHC_DATA_LOADED |
Character(1) |
VARCHAR2(1) NOT NULL |
Enhanced Data Loaded
Y/N Table Edit
Default Value: N
|
| 60 |
CC_POSTING_DT |
Date(10) |
DATE |
Credit Card Posting Date
|
| 61 |
EX_AMX_TRANSNBR |
Character(50) |
VARCHAR2(50) NOT NULL |
Amex Global Transaction Nbr
|
| 62 |
DESCR |
Character(30) |
VARCHAR2(30) NOT NULL |
Description
|