PO_SSPYMADV_VW

(SQL View)
 Index Back

SELECT DISTINCT B.BUSINESS_UNIT , B.VOUCHER_ID , A.BANK_SETID , A.BANK_CD , A.BANK_ACCT_KEY , A.PYMNT_ID , A.PAY_CYCLE_SEQ_NUM , A.PYMNT_METHOD , D.PYMNT_SEPARATE , D.PAY_DOC_SEQ , A.PYMNT_ID_REF , D.PYMNT_OVERFLOW_ID , D.SOURCE_TXN , D.PAY_DOC_ID , D.REMIT_LINE_TYPE , D.INVOICE_ID , D.PAID_AMT_GROSS , D.PAID_AMT_DSCNT , D.DISCOUNT_TAKEN , D.PAID_AMT , D.PYMNT_MESSAGE , D.CURRENCY_PYMNT FROM PS_PAYMENT_TBL A , PS_VCHR_FS B , PS_PYMNT_VCHR_XREF C , PS_PYMNT_ADVICE D WHERE A.BANK_SETID = C.BANK_SETID AND A.BANK_CD = C.BANK_CD AND A.BANK_ACCT_KEY = C.BANK_ACCT_KEY AND A.PYMNT_ID = C.PYMNT_ID AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.VOUCHER_ID = C.VOUCHER_ID AND A.PAY_CYCLE_SEQ_NUM = D.PAY_CYCLE_SEQ_NUM AND A.BANK_SETID = D.BANK_SETID AND A.BANK_CD = D.BANK_CD AND A.BANK_ACCT_KEY = D.BANK_ACCT_KEY AND A.PYMNT_METHOD = D.PYMNT_METHOD AND A.PYMNT_ID_REF = D.PYMNT_ID_REF AND B.BUSINESS_UNIT = D.BUSINESS_UNIT AND A.PYMNT_STATUS <> 'X' AND A.POST_STATUS_AP IN ('P','U') AND B.ENTRY_STATUS = 'P'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 VOUCHER_ID Character(8) VARCHAR2(8) NOT NULL Voucher ID
3 BANK_SETID Character(5) VARCHAR2(5) NOT NULL The PeopleSoft tableset ID associated with a given bank/counterparty.
4 BANK_CD Character(5) VARCHAR2(5) NOT NULL Bank Code
5 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
6 PYMNT_ID Character(10) VARCHAR2(10) NOT NULL Payment Number
7 PAY_CYCLE_SEQ_NUM Number(6,0) INTEGER NOT NULL Pay Cycle Sequence Number
8 PYMNT_METHOD Character(3) VARCHAR2(3) NOT NULL Payment Method
ACH=Automated Clearing House
BEF=Draft - Customer EFT
BOO=Draft - Customer Initiated
CHK=System Check
D=Deposit
DD=Direct Debit
DFT=Draft - Supplier Initiated
DRA=Draft
EFT=Electronic Funds Transfer
GE=Giro - EFT
GM=Giro - Manual
LC=Letter of Credit
MAN=Manual Check
TRW=Treasury Wire
WIR=Wire Transfer
9 PYMNT_SEPARATE Character(1) VARCHAR2(1) NOT NULL Separate Payment
10 PAY_DOC_SEQ Number(5,0) INTEGER NOT NULL Advice Sequence
11 PYMNT_ID_REF Character(20) VARCHAR2(20) NOT NULL Payment Reference
12 PYMNT_OVERFLOW_ID Character(20) VARCHAR2(20) NOT NULL Payment Overflow ID
13 SOURCE_TXN Character(4) VARCHAR2(4) NOT NULL Source Transaction
14 PAY_DOC_ID Character(20) VARCHAR2(20) NOT NULL Advice ID
15 REMIT_LINE_TYPE Character(1) VARCHAR2(1) NOT NULL Remit Line Type
A=Remittance Advice Line
M=Remittance Message Line
16 INVOICE_ID Character(30) VARCHAR2(30) NOT NULL Invoice Number
17 PAID_AMT_GROSS Signed Number(28,3) DECIMAL(26,3) NOT NULL Gross Paid Amount
18 PAID_AMT_DSCNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Discount on Paid Amount
19 DISCOUNT_TAKEN Signed Number(28,3) DECIMAL(26,3) NOT NULL Discount Taken
20 PAID_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Paid Amount
21 PYMNT_MESSAGE Character(70) VARCHAR2(70) NOT NULL Payment Message
22 CURRENCY_PYMNT Character(3) VARCHAR2(3) NOT NULL Payment Currency