EX_ADVLINE_VW

(SQL View)
 Index Back

Line View


SELECT A.ADVANCE_ID , B.LINE_NBR , A.EMPLID , A.EMPL_RCD , A.ADV_STATUS , B.EX_LINE_STATUS , A.BUSINESS_UNIT_GL , A.CREATION_DT , A.APPROVAL_DT , A.ACCOUNTING_DT , A.REVERSAL_DATE , A.POST_STATUS_EX , C.GENERATE_PYMNT , C.EXPEND_MTHD_EDIT , C.VENDOR_SETID , C.VENDOR_ID , B.MONETARY_AMOUNT , B.CURRENCY_CD , B.DESCR , B.GROSSUP , B.GROSS_UP_EST_AMT , A.PROCESS_INSTANCE , A.PROCESS_FLAG FROM PS_EX_ADV_HDR A , PS_EX_ADV_LINE B , PS_EX_ADV_SRC_TBL C WHERE B.ADVANCE_ID = A.ADVANCE_ID AND C.SETID = ( SELECT D.SETID FROM PS_SET_CNTRL_REC D WHERE D.RECNAME = 'EX_ADV_SRC_TBL' AND D.SETCNTRLVALUE = A.BUSINESS_UNIT_GL) AND B.ADVANCE_SOURCE = C.ADVANCE_SOURCE AND C.EFF_STATUS = 'A' AND C.EFFDT=( SELECT MAX(X.EFFDT) FROM PS_EX_ADV_SRC_TBL X WHERE X.SETID = C.SETID AND X.ADVANCE_SOURCE = B.ADVANCE_SOURCE AND X.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 ADVANCE_ID Character(10) VARCHAR2(10) NOT NULL Advance 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 ADV_STATUS Character(3) VARCHAR2(3) NOT NULL Advance Status
APY=Approved for Payment
CLS=Closed
DEN=Denied
DNA=Denied by Approver
DNU=Denied by Auditor
HDA=On Hold, with Approver
HDU=On Hold, with Auditor
HLD=On Hold
PAR=Approvals in Process
PD=Paid
PND=Pending
PRO=In Process
PST=Past Due Balance
RAP=Approved
RCN=Reconciled
RRT=Reroute Transaction
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 APPROVAL_DT Date(10) DATE Date of Approval
10 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
11 REVERSAL_DATE Date(10) DATE Specifies the date for an automatic reversal journal entry. The date must be within the calendar of the target (posting) ledger. PeopleSoft General Ledger automatically creates and populates a journal entry with this date.
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 GENERATE_PYMNT Character(1) VARCHAR2(1) NOT NULL Generate Payment
14 EXPEND_MTHD_EDIT Character(3) VARCHAR2(3) NOT NULL Payment Type Edit
EMP=Employee
PPA=Prepaid Airfare
PPD=Prepaid
PPH=Prepaid Hotel
VND=Supplier
15 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
16 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
17 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.
18 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
19 DESCR Character(30) VARCHAR2(30) NOT NULL Description
20 GROSSUP Character(1) VARCHAR2(1) NOT NULL Gross Up Indicator
21 GROSS_UP_EST_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Gross Up Estimated Amount
22 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
23 PROCESS_FLAG Character(1) VARCHAR2(1) NOT NULL Process Flag
A=Purge All
S=Only purge items with at least