EM_PYMT_CRTD_VW

(SQL View)
 Index Back

MarketPay Created Payments


SELECT DISTINCT B.BUSINESS_UNIT , B.VOUCHER_ID , A.BANK_SETID , A.BANK_CD , A.BANK_ACCT_KEY , A.PYMNT_ID , C.PYMNT_CNT , C.PAY_CYCLE , C.PAY_CYCLE_SEQ_NUM , A.PYMNT_METHOD , A.PYMNT_ID_REF , C.PYMNT_GROSS_AMT , C.DSCNT_PAY_AMT , C.PAID_AMT , C.CURRENCY_PYMNT , SEC.OPRID , A.PYMNT_DT , A.PYMNT_AMT , C.SCHEDULED_PAY_DT , C.PYMNT_TYPE FROM PS_PAYMENT_TBL A , PS_VCHR_FS B , PS_PYMNT_VCHR_XREF C , PS_SEC_BU_OPR SEC 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 B.BUSINESS_UNIT = SEC.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 PYMNT_CNT Number(5,0) INTEGER NOT NULL Payments

Default Value: 1

8 PAY_CYCLE Character(6) VARCHAR2(6) NOT NULL Pay Cycle
9 PAY_CYCLE_SEQ_NUM Number(6,0) INTEGER NOT NULL Pay Cycle Sequence Number
10 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
11 PYMNT_ID_REF Character(20) VARCHAR2(20) NOT NULL Payment Reference
12 PAID_AMT_GROSS Signed Number(28,3) DECIMAL(26,3) NOT NULL Gross Paid Amount
13 PAID_AMT_DSCNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Discount on Paid Amount
14 PAID_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Paid Amount
15 CURRENCY_PYMNT Character(3) VARCHAR2(3) NOT NULL Payment Currency
16 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
17 PYMNT_DT Date(10) DATE Payment Date
18 PYMNT_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Payment Amount
19 SCHEDULED_PAY_DT Date(10) DATE Scheduled to Pay
20 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