BNK_RCN_DRAFR (SQL View)	Index Back
Revaluation: AR Drafts View that revalues AR Drafts into bank account's default currency

BNK_RCN_DRAFR

(SQL View)

Revaluation: AR Drafts

View that revalues AR Drafts into bank account's default currency

SELECT A.DRAFT_BU , A.DRAFT_ID , A.DRAFT_STATUS , A.CUST_ID , A.DRAFT_AMT , A.DRAFT_CNT , A.DRAFT_DUE_DT , A.DRAFT_DUE_DATE2 , A.CREATE_DATE , A.PROCESS_INSTANCE , A.DRAFT_APPROVAL , A.DRAFT_CURRENCY , A.DRAFT_DOC , A.MICR_ID , A.GROUP_ID , A.HOLD_SW , A.DISCOUNT_DRAFT , A.RECOURSE_ALLOWED , B.BASE_CURRENCY_BNK , A.DRAFT_AMT_BASE , A.BANK_SETID , A.BANK_CD , A.BANK_ACCT_KEY , A.BNK_ID_NBR , A.BANK_ACCOUNT_NUM , A.RECON_TYPE , A.RECONCILE_DT , A.RECON_STATUS , A.RECON_CYCLE_NBR , A.RECORD_SEQ_NUMBER , A.RECONCILE_OPRID , A.STTLMNT_DT_EST , A.STTLMNT_DT_ACTUAL , A.DISCOUNT_PROCESSED , A.DOC_TYPE , A.DOC_SEQ_NBR , A.DOC_SEQ_DATE , A.DOC_SEQ_STATUS , A.ACCOUNTING_DT , A.BUSINESS_UNIT_GL , A.DRAFT_TYPE , A.DRAFT_DOC_REF , A.DRAFT_ACCEPT_OPRID , A.DRAFT_PAYER_NAME , A.DESCR60 , A.SEL_REF_RANGE , A.WS_REF_MATCH_TYPE , A.EFT_INSTANCE , A.RECON_PROCESSED , A.DRAFT_BUSN_EVENT , A.DRAFT_POST_ACTION , A.DTTM_CREATED , A.DRAFT_STATUS_NEW , A.ENTRY_TYPE , A.ENTRY_USE_ID , A.ITM_MULTIPLIER , %Round(%DecMult(%DecDiv(A.DRAFT_AMT , B.RATE_DIV) , B.RATE_MULT) , B.DECIMAL_POSITIONS) , A.RECON_RUN_ID FROM PS_BNK_RCN_DRAFT A , PS_BNK_CURR_RVL_VW B WHERE B.SETID = A.BANK_SETID AND B.BANK_CD = A.BANK_CD AND B.BANK_ACCT_KEY = A.BANK_ACCT_KEY AND B.BNK_ID_NBR = A.BNK_ID_NBR AND B.BANK_ACCOUNT_NUM = A.BANK_ACCOUNT_NUM AND B.TXN_CURRENCY_CD = A.DRAFT_CURRENCY AND B.EFFDT = ( SELECT MAX(EFFDT) FROM PS_BNK_CURR_RVL_V2 WHERE SETID = B.SETID AND BANK_CD = B.BANK_CD AND BANK_ACCT_KEY = B.BANK_ACCT_KEY AND BASE_CURRENCY_BNK = B.BASE_CURRENCY_BNK AND TXN_CURRENCY_CD = B.TXN_CURRENCY_CD AND RATE_INDEX = B.RATE_INDEX AND CUR_RT_TYPE = B.CUR_RT_TYPE AND EFFDT <= A.STTLMNT_DT_EST)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 DRAFT_BU Character(5) VARCHAR2(5) NOT NULL Identifies the Receivables business unit associated with a draft. This field is maintained by the Draft Creation process.
Default Value: OPR_DEF_TBL_AR.DEPOSIT_BU
Prompt Table: SP_BUARDS_NONVW

2 DRAFT_ID Character(15) VARCHAR2(15) NOT NULL Uniquely identifies a payment draft. The Draft ID is system-generated during the Draft Creation process or manually entered.
Default Value: NEXT
Prompt Table: DRAFT_CONTROL

3 DRAFT_STATUS Character(1) VARCHAR2(1) NOT NULL Draft Status
A=Accepted
C=Complete
D=Dishonored
E=Void
I=Identified
L=Collateral
M=Discounted
N=No Action
P=Pending
R=Remitted
T=Endorsed
Default Value: I

4 CUST_ID Character(15) VARCHAR2(15) NOT NULL Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations.
Prompt Table: CUST_SRCH_VW2

5 DRAFT_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Specifies the amount of a draft payment. This field is maintained by the Draft Creation process.

6 DRAFT_CNT Number(5,0) INTEGER NOT NULL Draft Count

7 DRAFT_DUE_DT Date(10) DATE Draft Due Date

8 DRAFT_DUE_DATE2 Date(10) DATE Draft Due Date

9 CREATE_DATE Date(10) DATE Creation Date

10 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance

11 DRAFT_APPROVAL Character(1) VARCHAR2(1) NOT NULL "Indicates whether or not a draft is pre-approved for payment by a bill-to customer. If the draft is pre-approved then it can be submitted to the customer's bank without the customer's sign-off. Otherwise the draft must be reviewed and approved by the customer before the enterprise can submit it to the bank for payment. This field is used only if the preferred payment method is ""Draft""."
P=Preapproved
R=Approval Required

12 DRAFT_CURRENCY Character(3) VARCHAR2(3) NOT NULL Specifies the currency of a draft payment. The draft currency may or may not be the same as the base currency of the business unit handling the draft.
Prompt Table: CURRENCY_CD_TBL

13 DRAFT_DOC Character(1) VARCHAR2(1) NOT NULL "A flag that indicates whether or not (C or N) a draft document should be created for a Receivables item when the payment method is ""draft"". If the value is N then the system prints only a draft statement. If the value is C then the system creates a draft document. "
C=Create Document
N=Do Not Create Document

14 MICR_ID Character(30) VARCHAR2(30) NOT NULL MICR ID
Prompt Table: DRAFT_MICR_VW

15 GROUP_ID Character(15) VARCHAR2(15) NOT NULL Uniquely identifies a collection of pending items that are to be posted to accounts receivable. The identifier is manually entered or automatically generated by the system.

16 HOLD_SW Character(1) VARCHAR2(1) NOT NULL Hold Switch
N=No Hold
Y=On Hold
Default Value: N

17 DISCOUNT_DRAFT Character(1) VARCHAR2(1) NOT NULL Discount Draft
Y/N Table Edit

18 RECOURSE_ALLOWED Character(1) VARCHAR2(1) NOT NULL Allow Recourse

19 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

20 DRAFT_AMT_BASE Signed Number(28,3) DECIMAL(26,3) NOT NULL Specifies the amount of a draft payment in the business unit's base currency. This field is maintained by the Draft Creation process.

21 BANK_SETID Character(5) VARCHAR2(5) NOT NULL The PeopleSoft tableset ID associated with a given bank/counterparty.

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

23 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

24 BNK_ID_NBR Character(20) VARCHAR2(20) NOT NULL A literal alphanumeric attribute that uniquely identifies a given bank or counterparty.

25 BANK_ACCOUNT_NUM Character(35) VARCHAR2(35) NOT NULL The literal unique identifier associated with a given bank/counterparty account.

26 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

27 RECONCILE_DT Date(10) DATE The transaction reconciliation date.

28 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
Default Value: UNR

29 RECON_CYCLE_NBR Number(10,0) DECIMAL(10) NOT NULL The bank statement ID.

30 RECORD_SEQ_NUMBER Number(10,0) DECIMAL(10) NOT NULL An internal work field utilized to enumerate database records in bank statement processing.

31 RECONCILE_OPRID Character(30) VARCHAR2(30) NOT NULL The user ID that performed the reconciliation for a given transaction.

32 STTLMNT_DT_EST Date(10) DATE Date in which a deal transaction is estimated to be settled with a counterparty.

33 STTLMNT_DT_ACTUAL Date(10) DATE Date in which a deal transaction is actually settled with a counterparty.

34 DISCOUNT_PROCESSED Character(1) VARCHAR2(1) NOT NULL Discount Processed Switch
Y/N Table Edit
Default Value: N

35 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.

36 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.

37 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.

38 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

39 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
Default Value: %date

40 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit

41 DRAFT_TYPE Character(10) VARCHAR2(10) NOT NULL A flag that indicates whether a draft is initiated by the vendor (1) or by the customer (2).
Prompt Table: DR_TYPE_C_VW

42 DRAFT_DOC_REF Character(20) VARCHAR2(20) NOT NULL Draft Reference

43 DRAFT_ACCEPT_OPRID Character(30) VARCHAR2(30) NOT NULL Accepted by

44 DRAFT_PAYER_NAME Character(50) VARCHAR2(50) NOT NULL Payer Name

45 DESCR60 Character(60) VARCHAR2(60) NOT NULL Description

46 SEL_REF_RANGE Character(1) VARCHAR2(1) NOT NULL Range of References
Default Value: N

47 WS_REF_MATCH_TYPE Character(1) VARCHAR2(1) NOT NULL Worksheet Reference Match Type
E=Exact Match
L=Like Match
Default Value: E

48 EFT_INSTANCE Number(10,0) DECIMAL(10) NOT NULL EFT Process Instance

49 RECON_PROCESSED Character(1) VARCHAR2(1) NOT NULL Reconciliation Processed
Y/N Table Edit
Default Value: N

50 DRAFT_BUSN_EVENT Character(2) VARCHAR2(2) NOT NULL Defines the Draft life cycle event. It represents one of the actions that the PeopleSoft system has provided for Draft processing. Examples are: Approving a Draft Remitting a Draft to the Bank Voiding the Draft.

51 DRAFT_POST_ACTION Character(1) VARCHAR2(1) NOT NULL Draft Post Action
A=Actual
D=Dishonor

52 DTTM_CREATED DateTime(26) TIMESTAMP Datetime Created

53 DRAFT_STATUS_NEW Character(1) VARCHAR2(1) NOT NULL New Draft Status

54 ENTRY_TYPE Character(5) VARCHAR2(5) NOT NULL Specifies the type of transaction associated with a pending item. The entry type remains with the item after it is posted and affects many areas of Receivables processing. Some examples of entry types are Invoices Credit Memo Prepayment and Deduction.

55 ENTRY_USE_ID Character(5) VARCHAR2(5) NOT NULL Specifies the System Function or type of processing associated with an entry type.

56 ITM_MULTIPLIER Signed Number(2,0) DECIMAL(1) NOT NULL Posting Item Multiplier

57 BANK_ACCT_RVL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Field used to store the amount on a transaction revalued in the base currency of the transactions bank account.

58 RECON_RUN_ID Character(15) VARCHAR2(15) NOT NULL Recon Run ID

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	DRAFT_BU	Character(5)	VARCHAR2(5) NOT NULL	Identifies the Receivables business unit associated with a draft. This field is maintained by the Draft Creation process. Default Value: OPR_DEF_TBL_AR.DEPOSIT_BU Prompt Table: SP_BUARDS_NONVW
2	DRAFT_ID	Character(15)	VARCHAR2(15) NOT NULL	Uniquely identifies a payment draft. The Draft ID is system-generated during the Draft Creation process or manually entered. Default Value: NEXT Prompt Table: DRAFT_CONTROL
3	DRAFT_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Draft Status A=Accepted C=Complete D=Dishonored E=Void I=Identified L=Collateral M=Discounted N=No Action P=Pending R=Remitted T=Endorsed Default Value: I
4	CUST_ID	Character(15)	VARCHAR2(15) NOT NULL	Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations. Prompt Table: CUST_SRCH_VW2
5	DRAFT_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Specifies the amount of a draft payment. This field is maintained by the Draft Creation process.
6	DRAFT_CNT	Number(5,0)	INTEGER NOT NULL	Draft Count
7	DRAFT_DUE_DT	Date(10)	DATE	Draft Due Date
8	DRAFT_DUE_DATE2	Date(10)	DATE	Draft Due Date
9	CREATE_DATE	Date(10)	DATE	Creation Date
10	PROCESS_INSTANCE	Number(10,0)	DECIMAL(10) NOT NULL	Process Instance
11	DRAFT_APPROVAL	Character(1)	VARCHAR2(1) NOT NULL	"Indicates whether or not a draft is pre-approved for payment by a bill-to customer. If the draft is pre-approved then it can be submitted to the customer's bank without the customer's sign-off. Otherwise the draft must be reviewed and approved by the customer before the enterprise can submit it to the bank for payment. This field is used only if the preferred payment method is ""Draft""." P=Preapproved R=Approval Required
12	DRAFT_CURRENCY	Character(3)	VARCHAR2(3) NOT NULL	Specifies the currency of a draft payment. The draft currency may or may not be the same as the base currency of the business unit handling the draft. Prompt Table: CURRENCY_CD_TBL
13	DRAFT_DOC	Character(1)	VARCHAR2(1) NOT NULL	"A flag that indicates whether or not (C or N) a draft document should be created for a Receivables item when the payment method is ""draft"". If the value is N then the system prints only a draft statement. If the value is C then the system creates a draft document. " C=Create Document N=Do Not Create Document
14	MICR_ID	Character(30)	VARCHAR2(30) NOT NULL	MICR ID Prompt Table: DRAFT_MICR_VW
15	GROUP_ID	Character(15)	VARCHAR2(15) NOT NULL	Uniquely identifies a collection of pending items that are to be posted to accounts receivable. The identifier is manually entered or automatically generated by the system.
16	HOLD_SW	Character(1)	VARCHAR2(1) NOT NULL	Hold Switch N=No Hold Y=On Hold Default Value: N
17	DISCOUNT_DRAFT	Character(1)	VARCHAR2(1) NOT NULL	Discount Draft Y/N Table Edit
18	RECOURSE_ALLOWED	Character(1)	VARCHAR2(1) NOT NULL	Allow Recourse
19	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
20	DRAFT_AMT_BASE	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Specifies the amount of a draft payment in the business unit's base currency. This field is maintained by the Draft Creation process.
21	BANK_SETID	Character(5)	VARCHAR2(5) NOT NULL	The PeopleSoft tableset ID associated with a given bank/counterparty.
22	BANK_CD	Character(5)	VARCHAR2(5) NOT NULL	Bank Code
23	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
24	BNK_ID_NBR	Character(20)	VARCHAR2(20) NOT NULL	A literal alphanumeric attribute that uniquely identifies a given bank or counterparty.
25	BANK_ACCOUNT_NUM	Character(35)	VARCHAR2(35) NOT NULL	The literal unique identifier associated with a given bank/counterparty account.
26	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
27	RECONCILE_DT	Date(10)	DATE	The transaction reconciliation date.
28	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 Default Value: UNR
29	RECON_CYCLE_NBR	Number(10,0)	DECIMAL(10) NOT NULL	The bank statement ID.
30	RECORD_SEQ_NUMBER	Number(10,0)	DECIMAL(10) NOT NULL	An internal work field utilized to enumerate database records in bank statement processing.
31	RECONCILE_OPRID	Character(30)	VARCHAR2(30) NOT NULL	The user ID that performed the reconciliation for a given transaction.
32	STTLMNT_DT_EST	Date(10)	DATE	Date in which a deal transaction is estimated to be settled with a counterparty.
33	STTLMNT_DT_ACTUAL	Date(10)	DATE	Date in which a deal transaction is actually settled with a counterparty.
34	DISCOUNT_PROCESSED	Character(1)	VARCHAR2(1) NOT NULL	Discount Processed Switch Y/N Table Edit Default Value: N
35	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.
36	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.
37	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.
38	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
39	ACCOUNTING_DT	Date(10)	DATE	The accounting entry construction date for a given transaction (a generic field that crosses multiple Default Value: %date
40	BUSINESS_UNIT_GL	Character(5)	VARCHAR2(5) NOT NULL	GL Business Unit
41	DRAFT_TYPE	Character(10)	VARCHAR2(10) NOT NULL	A flag that indicates whether a draft is initiated by the vendor (1) or by the customer (2). Prompt Table: DR_TYPE_C_VW
42	DRAFT_DOC_REF	Character(20)	VARCHAR2(20) NOT NULL	Draft Reference
43	DRAFT_ACCEPT_OPRID	Character(30)	VARCHAR2(30) NOT NULL	Accepted by
44	DRAFT_PAYER_NAME	Character(50)	VARCHAR2(50) NOT NULL	Payer Name
45	DESCR60	Character(60)	VARCHAR2(60) NOT NULL	Description
46	SEL_REF_RANGE	Character(1)	VARCHAR2(1) NOT NULL	Range of References Default Value: N
47	WS_REF_MATCH_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Worksheet Reference Match Type E=Exact Match L=Like Match Default Value: E
48	EFT_INSTANCE	Number(10,0)	DECIMAL(10) NOT NULL	EFT Process Instance
49	RECON_PROCESSED	Character(1)	VARCHAR2(1) NOT NULL	Reconciliation Processed Y/N Table Edit Default Value: N
50	DRAFT_BUSN_EVENT	Character(2)	VARCHAR2(2) NOT NULL	Defines the Draft life cycle event. It represents one of the actions that the PeopleSoft system has provided for Draft processing. Examples are: Approving a Draft Remitting a Draft to the Bank Voiding the Draft.
51	DRAFT_POST_ACTION	Character(1)	VARCHAR2(1) NOT NULL	Draft Post Action A=Actual D=Dishonor
52	DTTM_CREATED	DateTime(26)	TIMESTAMP	Datetime Created
53	DRAFT_STATUS_NEW	Character(1)	VARCHAR2(1) NOT NULL	New Draft Status
54	ENTRY_TYPE	Character(5)	VARCHAR2(5) NOT NULL	Specifies the type of transaction associated with a pending item. The entry type remains with the item after it is posted and affects many areas of Receivables processing. Some examples of entry types are Invoices Credit Memo Prepayment and Deduction.
55	ENTRY_USE_ID	Character(5)	VARCHAR2(5) NOT NULL	Specifies the System Function or type of processing associated with an entry type.
56	ITM_MULTIPLIER	Signed Number(2,0)	DECIMAL(1) NOT NULL	Posting Item Multiplier
57	BANK_ACCT_RVL_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Field used to store the amount on a transaction revalued in the base currency of the transactions bank account.
58	RECON_RUN_ID	Character(15)	VARCHAR2(15) NOT NULL	Recon Run ID