PAYMENT_TBL_VW5

(SQL View)
 Index Back

Manual Check Deletion


select a.BANK_SETID ,a.BANK_CD ,a.BANK_ACCT_KEY ,a.PYMNT_ID ,a.PYMNT_ID_REF ,a.BANK_ACCOUNT_NUM ,a.BRANCH_ID ,a.CHECK_DIGIT ,a.REMIT_SETID ,a.REMIT_VENDOR ,a.vndr_loc ,a.EMPLID ,a.NAME1 ,a.NAME2, %subrec(ADDRess_SBR,a) ,a.PYMNT_DT ,a.CREATION_DT ,a.OPRID ,a.PYMNT_AMT ,a.CURRENCY_PYMNT ,a.PYMNT_METHOD ,a.PYMNT_HANDLING_CD ,a.PYMNT_STATUS ,a.PYMNT_RECONCILE_DT ,a.CANCEL_ACTION ,a.CANCEL_DT ,a.ACCOUNTING_DT ,a.POST_STATUS_AP ,a.PROCESS_INSTANCE ,a.PAY_CYCLE ,a.PAY_CYCLE_SEQ_NUM ,a.RECON_TYPE ,a.RECON_CYCLE_NBR ,a.RECONCILE_OPRID ,a.BNK_ID_NBR ,a.RECON_STATUS ,a.DFI_ID_NUM ,a.DFI_ID_QUAL ,a.EFT_PYMNT_FMT_CD ,a.STTLMNT_DT_EST ,a.STTLMNT_DT_ACTUAL ,a.EFT_TRANS_HANDLING ,a.REMIT_BANK_ACCOUNT ,a.REMIT_BRANCH_ID ,a.REMIT_CHECK_DIGIT ,a.REP_RTNG_NUM ,a.FORM_NBR_CONFIRMED ,a.IN_PROCESS_FLG ,a.POSITIVE_PAY_DT ,a.POSITIVE_PAY_STAT ,a.MICR_LINE ,a.CASH_CLEARED_FLG ,a.CASH_CLEARED_DT ,a.REPLACED_DT ,a.PYMNT_TYPE ,a.doc_type ,a.doc_seq_nbr ,a.doc_seq_date ,a.doc_seq_status ,a.preferred_language ,b.business_unit_gl from ps_payment_tbl a, PS_BANK_ACCT_TBL b where a.bank_setid = b.setid and a.bank_cd = b.bank_cd and a.bank_acct_key = b.bank_acct_key

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BANK_SETID Character(5) VARCHAR2(5) NOT NULL The PeopleSoft tableset ID associated with a given bank/counterparty.

Default Value: OPR_BANK_VW_AP.BANK_SETID

Prompt Table: SP_SETBNK_NONVW

2 BANK_CD Character(5) VARCHAR2(5) NOT NULL Bank Code

Prompt Table: BANK_CD_VW2

3 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

Prompt Table: BANK_ACCT_VW

4 PYMNT_ID Character(10) VARCHAR2(10) NOT NULL Payment Number
5 PYMNT_ID_REF Character(20) VARCHAR2(20) NOT NULL Payment Reference
6 BANK_ACCOUNT_NUM Character(35) VARCHAR2(35) NOT NULL The literal unique identifier associated with a given bank/counterparty account.
7 BRANCH_ID Character(10) VARCHAR2(10) NOT NULL The local branch office identifier associated with a given bank/counterparty.
8 CHECK_DIGIT Character(2) VARCHAR2(2) NOT NULL Check Digit
9 REMIT_SETID Character(5) VARCHAR2(5) NOT NULL Remit SetID
10 REMIT_VENDOR Character(10) VARCHAR2(10) NOT NULL Remit Vendor
11 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
12 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
13 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1

Default Value: VENDOR_ADDR.NAME1

14 NAME2 Character(40) VARCHAR2(40) NOT NULL Name 2
15 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

Prompt Table: COUNTRY_TBL

16 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
17 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
18 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
19 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
20 CITY Character(30) VARCHAR2(30) NOT NULL City
21 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
22 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
23 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
AB=House Boat
WW=Trailer
24 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
25 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
26 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
27 COUNTY Character(30) VARCHAR2(30) NOT NULL County
28 STATE Character(6) VARCHAR2(6) NOT NULL State

Prompt Table: %EDIT_STATE

29 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
30 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
31 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

Y/N Table Edit

32 PYMNT_DT Date(10) DATE Payment Date

Default Value: %date

33 CREATION_DT Date(10) DATE Creation Date
34 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
35 PYMNT_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Payment Amount
36 CURRENCY_PYMNT Character(3) VARCHAR2(3) NOT NULL Payment Currency

Prompt Table: CURR_PYMNT_VW

37 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
38 PYMNT_HANDLING_CD Character(2) VARCHAR2(2) NOT NULL Payment Handling
39 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
40 PYMNT_RECONCILE_DT Date(10) DATE Date Reconciled
41 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
42 CANCEL_DT Date(10) DATE Date Cancelled
43 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
44 POST_STATUS_AP Character(1) VARCHAR2(1) NOT NULL Post Status
N=Payment Not Applied
P=Posted
U=Unposted

Default Value: U

45 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
46 PAY_CYCLE Character(6) VARCHAR2(6) NOT NULL Pay Cycle
47 PAY_CYCLE_SEQ_NUM Number(6,0) INTEGER NOT NULL Pay Cycle Sequence Number
48 RECON_TYPE Character(1) VARCHAR2(1) NOT NULL Represents a series of options as to how a treasury wishes to perform reconciliation for a given bank
A=Automatically Reconciled
B=Balance System(s) to BankTran
F=Forced to Reconcile
M=Manually Reconciled
S=Semi Manual
U=Unreconciled

Default Value: U

49 RECON_CYCLE_NBR Number(10,0) DECIMAL(10) NOT NULL The bank statement ID.
50 RECONCILE_OPRID Character(30) VARCHAR2(30) NOT NULL The user ID that performed the reconciliation for a given transaction.
51 BNK_ID_NBR Character(20) VARCHAR2(20) NOT NULL A literal alphanumeric attribute that uniquely identifies a given bank or counterparty.
52 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
53 DFI_ID_NUM Character(12) VARCHAR2(12) NOT NULL A banking industry convention identifier utilized to drive electronic settlements processing for a given bank/coun
54 DFI_ID_QUAL Character(2) VARCHAR2(2) NOT NULL A banking industry convention identifier utilized to drive data validation for a given bank/counterparty.
01=Transit Number
02=Swift ID/BIC
03=CHIPS Participant ID
04=Canadian Bank Branch/Institute
05=CHIPS Universal ID
ZZ=Mutually Defined
55 EFT_PYMNT_FMT_CD Character(3) VARCHAR2(3) NOT NULL Payment Format
00=Standard SWIFT transfer
10=Urgent SWIFT
15=Euro-Transfer
21=Check, w/ receipt confirmation
22=Check, Urgent
23=Check, urgent w/ receipt
40=Postal Order
CTX=Corp Trade Exchange
PBC=Check, standard
PPD=Prearranged Pay & Deposit
WRT=Wire Transfer

Prompt Table: PYMNT_FMT_TBL

56 STTLMNT_DT_EST Date(10) DATE Date in which a deal transaction is estimated to be settled with a counterparty.
57 STTLMNT_DT_ACTUAL Date(10) DATE Date in which a deal transaction is actually settled with a counterparty.
58 EFT_TRANS_HANDLING Character(1) VARCHAR2(1) NOT NULL Transaction Handling
D=Payment Only
P=Prenotification
X=Payment and Advice
59 REMIT_BANK_ACCOUNT Character(35) VARCHAR2(35) NOT NULL Bank Account Number
60 REMIT_BRANCH_ID Character(10) VARCHAR2(10) NOT NULL Branch ID
61 REMIT_CHECK_DIGIT Character(2) VARCHAR2(2) NOT NULL Check Digit
62 REP_RTNG_NUM Character(10) VARCHAR2(10) NOT NULL Routing Code
63 FORM_NBR_CONFIRMED Character(1) VARCHAR2(1) NOT NULL Check Number Confirmed

Y/N Table Edit

Default Value: Y

64 IN_PROCESS_FLG Character(1) VARCHAR2(1) NOT NULL In process flag

Y/N Table Edit

Default Value: N

65 POSITIVE_PAY_DT Date(10) DATE Positive Payment Date
66 POSITIVE_PAY_STAT Character(1) VARCHAR2(1) NOT NULL Positive Payment Status
C=Sent to bank
N=Not Sent to bank
S=Selected for transmission
Z=Zero Payment

Default Value: N

67 MICR_LINE Character(50) VARCHAR2(50) NOT NULL MICR Line
68 CASH_CLEARED_FLG Character(1) VARCHAR2(1) NOT NULL Cash Cleared Flag

Y/N Table Edit

Default Value: N

69 CASH_CLEARED_DT Date(10) DATE Cash Cleared Date
70 REPLACED_DT Date(10) DATE Replacement Date
71 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
72 DOC_TYPE Character(8) VARCHAR2(8) NOT NULL Specifies the business purpose of a financial transaction in countries that require all financial transactions to be tracked as "documents". A Document Type is associated with one and only one Journal Code.
73 DOC_SEQ_NBR Character(12) VARCHAR2(12) NOT NULL Specifies the sequence number assigned to each financial transaction (a document). The sequence number may be manually entered or system-generated.
74 DOC_SEQ_DATE Date(10) DATE Specifies the date that a document sequence number is assigned to a document or the date the document was created.
75 DOC_SEQ_STATUS Character(1) VARCHAR2(1) NOT NULL A flag that indicates a document sequencing error. If there is no error the field is blank and document is considered valid for posting.
1=Document Sequencing Disabled
A=GL BU Undefined
B=GL BU Blank
C=Accounting Date blank
D=Doc Type Blank
E=Jrnl Type, Code, Doc Undefine
F=Sequence Range Undefine
G=Max. Sequence Number Reached
H=Manual Nbr for Auto Doc Type
I=No Manual Number Entered
J=Duplicate Manual Number
K=Invalid Ledger
L=Invalid Ledger Group
N=No Status
76 PREFERRED_LANGUAGE Character(3) VARCHAR2(3) NOT NULL Preferred Language
ARA=Arabic
CFR=Canadian French
CZE=Czech
DAN=Danish
DUT=Dutch
E=English
ENG=English
ESP=Spanish
F=French
FIN=Finnish
FRA=French
GER=German
HUN=Hungarian
INE=International English
ITA=Italian
JPN=Japanese
KOR=Korean
NOR=Norwegian
POL=Polish
POR=Portuguese
RUS=Russian
SVE=Swedish
THA=Thai
UKE=UK English
ZHS=Simplified Chinese
ZHT=Traditional Chinese
77 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit