EX_PMT_VND_VW(SQL View) |
Index Back |
|---|---|
Supplier Payment Inq Vw |
| SELECT A.BANK_CD , A.PYMNT_ID , A.BANK_SETID , A.BANK_ACCT_KEY , A.SHEET_ID , B.SHEET_STATUS , B.SHEET_NAME , B.CREATION_DT , SUM(C.MONETARY_AMOUNT) , C.CURRENCY_CD FROM PS_EX_SHEET_PYMNT A , PS_EX_SHEET_HDR B , PS_EX_SHEET_LINE C , PS_SET_CNTRL_REC D , PS_EX_EXP_MTHD_TBL E WHERE A.EX_PYMNT_TYPE = 'V' AND A.SHEET_ID = B.SHEET_ID AND B.SHEET_ID = C.SHEET_ID AND A.LINE_NBR = C.LINE_NBR AND C.REIMBURSEMENT_CD='R' AND D.SETCNTRLVALUE=B.BUSINESS_UNIT_GL AND D.RECNAME='EX_EXP_MTHD_TBL' AND D.SETID = E.SETID AND C.EXPEND_MTHD = E.EXPEND_MTHD AND E.EFFDT=( SELECT MAX(X.EFFDT) FROM PS_EX_EXP_MTHD_TBL X WHERE X.SETID = E.SETID AND X.EXPEND_MTHD = E.EXPEND_MTHD AND X.EFFDT <= %CurrentDateIn) AND E.EFF_STATUS = 'A' AND E.EXPEND_MTHD_EDIT = 'VND' AND A.SEQ_NBR = ( SELECT MAX(A1.SEQ_NBR) FROM PS_EX_SHEET_PYMNT A1 WHERE A1.SHEET_ID = A.SHEET_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.PYMNT_ID, A.SHEET_ID, B.SHEET_STATUS, B.SHEET_NAME, B.CREATION_DT, C.CURRENCY_CD |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  BANK_CD |
Character(5) | VARCHAR2(5) NOT NULL | Bank Code |
| 2 | PYMNT_ID |
Character(10) | VARCHAR2(10) NOT NULL | Payment Number |
| 3 | SETID | Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 4 | 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 |
| 5 | SHEET_ID | Character(10) | VARCHAR2(10) NOT NULL | Report ID |
| 6 | SHEET_STATUS | Character(3) | VARCHAR2(3) NOT NULL |
Report Status
APY=Approved for Payment APZ=Approved For Zero Payment CLS=Closed DEN=Denied DNA=Denied by Approver DNU=Denied by Auditor ESC=Escheated Payment HDA=On Hold, with Approver HDU=On Hold, with Auditor HLD=On Hold MFS=Marked for Submit OPN=Open PAR=Approvals in Process PD=Paid PND=Pending PRO=In Process RAP=Approved SFA=Submission in Process STG=Staged SUB=Submitted for Approval XML=Submitted, Pending Validation |
| 7 | SHEET_NAME | Character(30) | VARCHAR2(30) NOT NULL | Report 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 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |