WTHD_PYMNT_VW3

(SQL View)
 Index Back

SELECT V.VENDOR_SETID , V.VENDOR_ID , P.PYMNT_ID , P.PYMNT_ID_REF , V.BUSINESS_UNIT , V.VOUCHER_ID , X.PYMNT_CNT , 0 , 0 , P.BANK_SETID , P.BANK_CD , P.BANK_ACCT_KEY , P.PYMNT_DT , P.SCHEDULE_ID , X.PYMNT_GROSS_AMT , V.TXN_CURRENCY_CD , X.PYMNT_TYPE , X.REMIT_SETID , X.REMIT_VENDOR , VNDR.VENDOR_NAME_SHORT FROM PS_VOUCHER V , PS_PYMNT_VCHR_XREF X , PS_PAYMENT_TBL P , PS_VENDOR VNDR WHERE V.BUSINESS_UNIT = X.BUSINESS_UNIT AND V.VOUCHER_ID = X.VOUCHER_ID AND X.PYMNT_SELCT_STATUS = 'P' AND P.PYMNT_STATUS <> 'X' AND P.BANK_SETID = X.BANK_SETID AND P.BANK_CD = X.BANK_CD AND P.BANK_ACCT_KEY = X.BANK_ACCT_KEY AND P.PYMNT_ID = X.PYMNT_ID AND VNDR.SETID = X.REMIT_SETID AND VNDR.VENDOR_ID = X.REMIT_VENDOR AND NOT EXISTS ( SELECT 'X' FROM PS_PYMNT_VCHR_WTHD W WHERE W.BUSINESS_UNIT = V.BUSINESS_UNIT AND W.VOUCHER_ID = V.VOUCHER_ID)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 APPL_VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Wthd Applicable Supplier Setid
2 APPL_VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Wthd Applicable Supplier ID
3 PYMNT_ID Character(10) VARCHAR2(10) NOT NULL Payment Number
4 PYMNT_ID_REF Character(20) VARCHAR2(20) NOT NULL Payment Reference
5 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
6 VOUCHER_ID Character(8) VARCHAR2(8) NOT NULL Voucher ID
7 PYMNT_CNT Number(5,0) INTEGER NOT NULL Payments
8 WTHD_BASIS_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Withholding Basis Amount
9 WTHD_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Withholding Amount
10 BANK_SETID Character(5) VARCHAR2(5) NOT NULL The PeopleSoft tableset ID associated with a given bank/counterparty.
11 BANK_CD Character(5) VARCHAR2(5) NOT NULL Bank Code
12 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
13 PYMNT_DT Date(10) DATE Payment Date
14 SCHEDULE_ID Character(10) VARCHAR2(10) NOT NULL Schedule ID
15 PYMNT_GROSS_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Gross Payment Amount
16 TXN_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Transaction Currency
17 PYMNT_TYPE Character(1) VARCHAR2(1) NOT NULL Document Type
E=Express Payment
M=Manual Payment
R=Regular payment
W=Withholding Payment
Z=Zero Payment Check
18 REMIT_SETID Character(5) VARCHAR2(5) NOT NULL Remit SetID
19 REMIT_VENDOR Character(10) VARCHAR2(10) NOT NULL Remit Vendor
20 VENDOR_NAME_SHORT Character(14) VARCHAR2(14) NOT NULL Short Vendor Name