POS_AR_DIRDEB_V (SQL View)	Index Back
View over Direct Debits View over Direct Debit Control for Actual v Projected

POS_AR_DIRDEB_V

(SQL View)

View over Direct Debits

View over Direct Debit Control for Actual v Projected

select DD_BU ,DD_ID ,DD_STATUS ,CUST_ID ,DD_AMT ,DD_CNT ,DD_DUE_DT ,CREATE_DATE ,DD_AS_OF_DT ,DD_REMIT_GROUP_ID ,CASH_POST_DT ,DD_CANCEL_POST_DT ,CURRENCY_CD ,DD_AMT_BASE ,BANK_SETID ,BANK_CD ,BANK_ACCT_KEY ,BNK_ID_NBR ,DEPOSIT_ACCT_NUM ,DEPOSIT_CURRENCY ,RECON_TYPE ,RECONCILE_DT ,RECON_STATUS ,RECON_CYCLE_NBR ,RECONCILE_OPRID ,STTLMNT_DT_ACTUAL ,STTLMNT_DT_EST ,DOC_SEQ_DATE ,ACCOUNTING_DT ,BUSINESS_UNIT_GL ,D.CALENDAR_DATE ,- DD_AMT from PS_DD_CONTROL, PS_POS_DAYS_TBL D where DD_STATUS in ('R','C') and DD_REMIT_POST_DT IS NOT NULL and D.CALENDAR_DATE = %CurrentDateIn

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 DD_BU Character(5) VARCHAR2(5) NOT NULL Specifies the business unit to which a direct debit is posted. This field is maintained by the Direct debit generation process.
Default Value: OPR_DEF_TBL_AR.DEPOSIT_BU
Prompt Table: SP_BUARDS_NONVW

2 DD_ID Character(15) VARCHAR2(15) NOT NULL Specifies a sequence number assigned to a Direct Debit by the Direct Debit generation process.
Prompt Table: DD_CONTROL

3 DD_STATUS Character(1) VARCHAR2(1) NOT NULL A flag that indicates the stage of processing of a direct debit payment in receivables. This field is maintained by the Direct Debit generation process.
A=Accepted
C=Complete
E=Rejected
N=No Action
P=Pending
R=Remitted
Z=All

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.

5 DD_CUST_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Direct Debit Cust Amt

6 DD_CNT Number(5,0) INTEGER NOT NULL Total Number of Direct Debits

7 DD_DUE_DT Date(10) DATE Direct Debit Due Date

8 CREATE_DATE Date(10) DATE Creation Date

9 DD_AS_OF_DT Date(10) DATE Pending Item As of Date

10 DD_REMIT_GROUP_ID Character(15) VARCHAR2(15) NOT NULL Remittance Group Id

11 CASH_POST_DT Date(10) DATE Cash Posted Date

12 DD_CANCEL_POST_DT Date(10) DATE Cancellation Post Date

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

14 DD_AMT_BASE Signed Number(28,3) DECIMAL(26,3) NOT NULL Specifies the amount of a direct debit in the base currency of the business unit to which it is posted. This field is maintained by the Direct debit generation process.

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

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

17 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

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

19 DEPOSIT_ACCT_NUM Character(35) VARCHAR2(35) NOT NULL Deposit Bank Account #

20 DEPOSIT_CURRENCY Character(3) VARCHAR2(3) NOT NULL Deposit Currency

21 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

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

23 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

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

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

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

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

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

29 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple

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

31 FORECAST_DATE Date(10) DATE Forecast Date

32 INVERSE_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Inverse amount used for Actual v Projected.

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	DD_BU	Character(5)	VARCHAR2(5) NOT NULL	Specifies the business unit to which a direct debit is posted. This field is maintained by the Direct debit generation process. Default Value: OPR_DEF_TBL_AR.DEPOSIT_BU Prompt Table: SP_BUARDS_NONVW
2	DD_ID	Character(15)	VARCHAR2(15) NOT NULL	Specifies a sequence number assigned to a Direct Debit by the Direct Debit generation process. Prompt Table: DD_CONTROL
3	DD_STATUS	Character(1)	VARCHAR2(1) NOT NULL	A flag that indicates the stage of processing of a direct debit payment in receivables. This field is maintained by the Direct Debit generation process. A=Accepted C=Complete E=Rejected N=No Action P=Pending R=Remitted Z=All
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.
5	DD_CUST_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Direct Debit Cust Amt
6	DD_CNT	Number(5,0)	INTEGER NOT NULL	Total Number of Direct Debits
7	DD_DUE_DT	Date(10)	DATE	Direct Debit Due Date
8	CREATE_DATE	Date(10)	DATE	Creation Date
9	DD_AS_OF_DT	Date(10)	DATE	Pending Item As of Date
10	DD_REMIT_GROUP_ID	Character(15)	VARCHAR2(15) NOT NULL	Remittance Group Id
11	CASH_POST_DT	Date(10)	DATE	Cash Posted Date
12	DD_CANCEL_POST_DT	Date(10)	DATE	Cancellation Post Date
13	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
14	DD_AMT_BASE	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Specifies the amount of a direct debit in the base currency of the business unit to which it is posted. This field is maintained by the Direct debit generation process.
15	BANK_SETID	Character(5)	VARCHAR2(5) NOT NULL	The PeopleSoft tableset ID associated with a given bank/counterparty.
16	BANK_CD	Character(5)	VARCHAR2(5) NOT NULL	Bank Code
17	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
18	BNK_ID_NBR	Character(20)	VARCHAR2(20) NOT NULL	A literal alphanumeric attribute that uniquely identifies a given bank or counterparty.
19	DEPOSIT_ACCT_NUM	Character(35)	VARCHAR2(35) NOT NULL	Deposit Bank Account #
20	DEPOSIT_CURRENCY	Character(3)	VARCHAR2(3) NOT NULL	Deposit Currency
21	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
22	RECONCILE_DT	Date(10)	DATE	The transaction reconciliation date.
23	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
24	RECON_CYCLE_NBR	Number(10,0)	DECIMAL(10) NOT NULL	The bank statement ID.
25	RECONCILE_OPRID	Character(30)	VARCHAR2(30) NOT NULL	The user ID that performed the reconciliation for a given transaction.
26	STTLMNT_DT_ACTUAL	Date(10)	DATE	Date in which a deal transaction is actually settled with a counterparty.
27	STTLMNT_DT_EST	Date(10)	DATE	Date in which a deal transaction is estimated to be settled with a counterparty.
28	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.
29	ACCOUNTING_DT	Date(10)	DATE	The accounting entry construction date for a given transaction (a generic field that crosses multiple
30	BUSINESS_UNIT_GL	Character(5)	VARCHAR2(5) NOT NULL	GL Business Unit
31	FORECAST_DATE	Date(10)	DATE	Forecast Date
32	INVERSE_AMOUNT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Inverse amount used for Actual v Projected.