VCHRPY_DIAG_VW

(SQL View)

SELECT X.BUSINESS_UNIT , X.VOUCHER_ID , X.PYMNT_CNT , P.PYMNT_ID , P.PYMNT_ID_REF , X.PAID_AMT , P.PYMNT_DT , X.REMIT_SETID , X.REMIT_VENDOR , P.BANK_SETID , P.BANK_CD , P.BANK_ACCT_KEY , P.IN_PROCESS_FLG , P.PYMNT_STATUS , P.CANCEL_ACTION , P.CANCEL_DT , P.CANCEL_POST_STATUS , P.POST_STATUS_AP , P.RECON_STATUS , X.PYMNT_TYPE FROM PS_PYMNT_VCHR_XREF X , PS_PAYMENT_TBL P WHERE X.REMIT_SETID = P.REMIT_SETID AND X.REMIT_VENDOR = P.REMIT_VENDOR AND X.BANK_SETID = P.BANK_SETID AND X.BANK_CD = P.BANK_CD AND X.BANK_ACCT_KEY = P.BANK_ACCT_KEY AND X.PYMNT_ID = P.PYMNT_ID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
Default Value: OPR_DEF_TBL_FS.BUSINESS_UNIT

2 VOUCHER_ID Character(8) VARCHAR2(8) NOT NULL Voucher ID
Default Value: NEXT

3 PYMNT_CNT Number(5,0) INTEGER NOT NULL Payments
Default Value: 1

4 PYMNT_ID Character(10) VARCHAR2(10) NOT NULL Payment Number

5 PYMNT_ID_REF Character(20) VARCHAR2(20) NOT NULL Payment Reference

6 PAID_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Paid Amount

7 PYMNT_DT Date(10) DATE Payment Date
Default Value: %date

8 REMIT_SETID Character(5) VARCHAR2(5) NOT NULL Remit SetID
Prompt Table: REMIT_SETID_VW

9 REMIT_VENDOR Character(10) VARCHAR2(10) NOT NULL Remit Vendor
Prompt Table: VNDR_RMT_VW

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 IN_PROCESS_FLG Character(1) VARCHAR2(1) NOT NULL In process flag

14 PYMNT_STATUS Character(1) VARCHAR2(1) NOT NULL Payment Status
A=Alignment
E=ACH/EFT Prenote
L=Stale Dated Payment
N=Reprinted with same ref number
O=Overflow
P=Paid
R=Replaced
S=Stopped
T=Undo Escheatment
U=Undo Cancel
V=Void
W=Escheated Check
X=Deleted

15 CANCEL_ACTION Character(1) VARCHAR2(1) NOT NULL Cancel Action
C=Do Not Reissue/Close Liability
H=Re-Open Voucher(s)/Put on Hold
N=No Cancel Action
P=Processed
R=Re-Open Voucher(s)/Re-Issue
S=Stale Dated Check
W=Escheated

16 CANCEL_DT Date(10) DATE Date Cancelled

17 CANCEL_POST_STATUS Character(1) VARCHAR2(1) NOT NULL Used to determine the post status of cancelled payment. If posting has not been run it will be set to W Pending. If the payment action is not V Void, S Stopped, W Escheated Check then the value is N. If the payment has not be posted but has been cancel the value = W. After the cancellation has been posted it the value is P posted.
N=Not Applicable
P=Posted
W=Pending

18 POST_STATUS_AP Character(1) VARCHAR2(1) NOT NULL Post Status
N=Payment Not Applied
P=Posted
U=Unposted

19 RECON_STATUS Character(3) VARCHAR2(3) NOT NULL Reconciliation Status
ALG=Alignment Check
AMB=Ambiguous Match
AMT=Amounts Not Equal
CUR=Currency not Defined
DAT=Tran Date <> Value Date
DPI=Duplicate Input Transactions
DUP=Duplicate Transaction
MSC=Miscellaneous Transaction
NAM=Payee Names Not Equal
NTB=Not Found in Statement
NTF=Not Found In System
NTP=Not Found in POS Data
NUL=Null Transaction ID Ref
OVR=Overflow
PND=Pending Reconciliation
REC=Reconciled
RPR=Reprinted Check
RR=Requires Review - BSAC
STP=Stop Payment
TYP=Wrong Transaction Type
UNR=Unreconciled
VOI=Voided Check

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

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit Default Value: OPR_DEF_TBL_FS.BUSINESS_UNIT
2	VOUCHER_ID	Character(8)	VARCHAR2(8) NOT NULL	Voucher ID Default Value: NEXT
3	PYMNT_CNT	Number(5,0)	INTEGER NOT NULL	Payments Default Value: 1
4	PYMNT_ID	Character(10)	VARCHAR2(10) NOT NULL	Payment Number
5	PYMNT_ID_REF	Character(20)	VARCHAR2(20) NOT NULL	Payment Reference
6	PAID_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Paid Amount
7	PYMNT_DT	Date(10)	DATE	Payment Date Default Value: %date
8	REMIT_SETID	Character(5)	VARCHAR2(5) NOT NULL	Remit SetID Prompt Table: REMIT_SETID_VW
9	REMIT_VENDOR	Character(10)	VARCHAR2(10) NOT NULL	Remit Vendor Prompt Table: VNDR_RMT_VW
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	IN_PROCESS_FLG	Character(1)	VARCHAR2(1) NOT NULL	In process flag
14	PYMNT_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Payment Status A=Alignment E=ACH/EFT Prenote L=Stale Dated Payment N=Reprinted with same ref number O=Overflow P=Paid R=Replaced S=Stopped T=Undo Escheatment U=Undo Cancel V=Void W=Escheated Check X=Deleted
15	CANCEL_ACTION	Character(1)	VARCHAR2(1) NOT NULL	Cancel Action C=Do Not Reissue/Close Liability H=Re-Open Voucher(s)/Put on Hold N=No Cancel Action P=Processed R=Re-Open Voucher(s)/Re-Issue S=Stale Dated Check W=Escheated
16	CANCEL_DT	Date(10)	DATE	Date Cancelled
17	CANCEL_POST_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Used to determine the post status of cancelled payment. If posting has not been run it will be set to W Pending. If the payment action is not V Void, S Stopped, W Escheated Check then the value is N. If the payment has not be posted but has been cancel the value = W. After the cancellation has been posted it the value is P posted. N=Not Applicable P=Posted W=Pending
18	POST_STATUS_AP	Character(1)	VARCHAR2(1) NOT NULL	Post Status N=Payment Not Applied P=Posted U=Unposted
19	RECON_STATUS	Character(3)	VARCHAR2(3) NOT NULL	Reconciliation Status ALG=Alignment Check AMB=Ambiguous Match AMT=Amounts Not Equal CUR=Currency not Defined DAT=Tran Date <> Value Date DPI=Duplicate Input Transactions DUP=Duplicate Transaction MSC=Miscellaneous Transaction NAM=Payee Names Not Equal NTB=Not Found in Statement NTF=Not Found In System NTP=Not Found in POS Data NUL=Null Transaction ID Ref OVR=Overflow PND=Pending Reconciliation REC=Reconciled RPR=Reprinted Check RR=Requires Review - BSAC STP=Stop Payment TYP=Wrong Transaction Type UNR=Unreconciled VOI=Voided Check
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