EX_PYRL_ADV_VW

(SQL View)
 Index Back

Employee Payroll Csh Adv View


SELECT A.BANK_SETID , A.BANK_CD , A.BANK_ACCT_KEY , A.PAYCHECK_NBR , A.ADVANCE_ID , B.ADV_STATUS , B.ADVANCE_NAME , B.CREATION_DT , SUM(C.MONETARY_AMOUNT) , SUM(A.PAID_AMT_BSE) , C.CURRENCY_CD , 'A' FROM PS_EX_ADVNCE_PYMNT A , PS_EX_ADV_HDR B , PS_EX_ADV_LINE C , PS_SET_CNTRL_REC D , PS_EX_ADV_SRC_TBL E WHERE A.ADVANCE_ID = B.ADVANCE_ID AND B.ADVANCE_ID = C.ADVANCE_ID AND A.LINE_NBR = C.LINE_NBR AND D.SETID = E.SETID AND D.SETCNTRLVALUE= B.BUSINESS_UNIT_GL AND D.RECNAME='EX_ADV_SRC_TBL' AND C.ADVANCE_SOURCE = E.ADVANCE_SOURCE AND E.EFF_STATUS = 'A' AND E.EFFDT=( SELECT MAX(X.EFFDT) FROM PS_EX_ADV_SRC_TBL X WHERE X.SETID = E.SETID AND X.ADVANCE_SOURCE = C.ADVANCE_SOURCE AND X.EFFDT <= %CurrentDateIn) AND A.SEQ_NBR = ( SELECT MAX(A1.SEQ_NBR) FROM PS_EX_ADVNCE_PYMNT A1 WHERE A1.ADVANCE_ID = A.ADVANCE_ID AND A1.LINE_NBR = A.LINE_NBR AND A1.PYMNT_ID = A.PYMNT_ID) GROUP BY A.BANK_SETID, A.BANK_CD, A.BANK_ACCT_KEY, A.PAYCHECK_NBR, A.ADVANCE_ID, B.ADV_STATUS, B.ADVANCE_NAME, B.CREATION_DT, C.CURRENCY_CD

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
2 BANK_CD Character(5) VARCHAR2(5) NOT NULL Bank Code
3 BANK_ACCT_KEY Character(4) VARCHAR2(4) NOT NULL A user defined unique identifier that facilitates the identification of a given account with a given bank
4 PAYCHECK_NBR Number(15,0) DECIMAL(15) NOT NULL Paycheck Number
5 ADVANCE_ID Character(10) VARCHAR2(10) NOT NULL Advance ID
6 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
7 ADVANCE_NAME Character(30) VARCHAR2(30) NOT NULL Advance Description
8 CREATION_DT Date(10) DATE Creation Date
9 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.
10 PYMNT_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Payment Amount
11 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
12 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