AR_INCR_FLTR_VW (SQL View)	Index Back
AR WorkCenter Filter

AR_INCR_FLTR_VW

(SQL View)

AR WorkCenter Filter

SELECT a.deposit_bu , a.deposit_id , a.payment_seq_num , a.payment_id , b.pay_ws_type , b.oprid , b.assn_oprid , a.payment_status , a.accounting_dt , a.entry_dt , a.pp_sw , a.payment_amt , a.payment_currency , a.amt_sel , a.amt_adj , a.amt_rem , a.disc_taken , a.misc_payment , a.create_pend_items , ' ' , ' ' ,a.REMIT_FROM_SETID ,a.REMIT_FROM_CUST_ID ,a.PYMT_RT_TYPE ,a.PYMT_RATE_MULT ,a.PYMT_RATE_DIV ,a.CURRENCY_CD ,a.PP_METHOD ,a.ALGORITHM_GROUP ,a.ALGORITHM ,a.BANK_ACCOUNT_NUM ,a.BNK_ID_NBR ,b.BANK_CD ,b.BANK_ACCT_KEY ,a.UNPOST_REASON ,c.CR_CARD_AUTH_STAT ,c.CR_CARD_AUTH_DT ,a.BILL_TO_CUST_ID ,d.SETID FROM PS_PAYMENT a , PS_DEPOSIT_CONTROL b LEFT OUTER JOIN PS_AR_CRCARD_I_VW c ON b.DEPOSIT_BU= c.DEPOSIT_BU AND b.DEPOSIT_ID= c.DEPOSIT_ID , PS_SET_CNTRL_REC d WHERE a.deposit_bu=b.deposit_bu AND a.deposit_id=b.deposit_id AND b.PAY_WS_TYPE='C' AND d.SETCNTRLVALUE = a.DEPOSIT_BU AND d.RECNAME = 'CUSTOMER' AND ((NOT EXISTS ( SELECT 'x' FROM ps_group_control WHERE group_bu = a.deposit_bu AND group_id = a.group_id) AND a.misc_payment = 'N') OR (a.payment_status <> 'J' AND a.misc_payment = 'Y'))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 DEPOSIT_BU Character(5) VARCHAR2(5) NOT NULL Identifies the business unit to which a deposit is posted.
Prompt Table: SP_BUARDS_NONVW

2 DEPOSIT_ID Character(15) VARCHAR2(15) NOT NULL Uniquely identifies a deposit of customer payments. The deposit ID can be manually assigned or system-generated.
Prompt Table: DEPOSIT_CONTROL

3 PAYMENT_SEQ_NUM Number(6,0) INTEGER NOT NULL Specifies the system-generated sequence number assigned to each payment as it is entered in a deposit.

4 PAYMENT_ID Character(15) VARCHAR2(15) NOT NULL Identifies a customer payment. This is usually a check number but may be some other identifying number.

5 PAY_WS_TYPE Character(1) VARCHAR2(1) NOT NULL Payment Worksheet Type
C=Credit Card Worksheet
E=ePayment Worksheet
P=Payment Worksheet

6 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).

7 ASSN_OPRID Character(30) VARCHAR2(30) NOT NULL Specifies the operator ID of the person who will enter items in a group. This is used when one person enters the control totals and another person enters the actual transactions. The assigned operator is also the person who processes the group and the person who is shown on group inquiry and processing panels.

8 PAYMENT_STATUS Character(1) VARCHAR2(1) NOT NULL Payment Status
A=Applied
B=Bounced
C=Complete
E=Identified - Express
I=Identified
J=Directly Journalled
R=Unposted Direct Journal
U=Unidentified
W=Worksheet

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

10 ENTRY_DT Date(10) DATE Specifies the date that a group of pending items was entered into the system. This date may be later or the same as the date that the items were received by the organization (See RECEIVED_DT).

11 PP_SW Character(1) VARCHAR2(1) NOT NULL Payment Predictor

12 PAYMENT_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Specifies the amount of a customer payment.

13 PAYMENT_CURRENCY Character(3) VARCHAR2(3) NOT NULL Identifies the currency of a customer payment. This is generally the same as the currency specified for the deposit but it can be different.

14 AMT_SEL Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount selected

15 AMT_ADJ Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount Adjusted

16 AMT_REM Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount Remaining

17 DISC_TAKEN Signed Number(28,3) DECIMAL(26,3) NOT NULL Discount Taken

18 MISC_PAYMENT Character(1) VARCHAR2(1) NOT NULL Journal Directly

19 CREATE_PEND_ITEMS Character(1) VARCHAR2(1) NOT NULL Create Pending Items
A=Do Not Post
L=Batch Standard
N=Batch Priority

20 WC_POST_ACTION Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not a group of pending items is ready to be processed by the Receivable Update program. The "action" requested can be no action, priority scheduled or standard scheduled.
A=Do Not Post
G=Post Now To GL
L=Batch Standard
N=Batch Priority
P=Post Now

21 WC_POST_STATUS Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not a control group of pending items has been fully posted to customer accounts. The posting status also indicates whether or not there were errors encountered during posting. This field is automatically populated by the Receivables Update program.
C=Complete
E=Errors
N=Not Posted

22 REMIT_FROM_SETID Character(5) VARCHAR2(5) NOT NULL SetID

23 REMIT_FROM_CUST_ID Character(15) VARCHAR2(15) NOT NULL Remit From Customer

24 PYMT_RT_TYPE Character(5) VARCHAR2(5) NOT NULL Specifies the type of exchange rate to be used when the currency of a customer payment is converted to the base currency of a business unit. The exchange rate type is also called the market rate type. Examples are spot rate current rate and official rate.
Prompt Table: PYMT_RT_TYPE_VW

25 PYMT_RATE_MULT Number(16,8) DECIMAL(15,8) NOT NULL Specifies the number (the multiplier) required to convert a payment amount from its entry currency to the business unit base currency. The number is based on the exchange rate specified for the transaction. It is used in conjunction with PYMT_ RATE_DIV. Refer to documentation on the Triangulation Process for the exact calculation.

26 PYMT_RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL Specifies the number (the divisor) required to convert a payment amount from its entry currency to the business unit base currency. The number is based on the exchange rate specified for the transaction. It is used in conjunction with PYMT_RATE_MULT. Refer to documentation on the Triangulation Process for the exact calculation.
Default Value: 1

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

28 PP_METHOD Character(15) VARCHAR2(15) NOT NULL Payment Predictor Method

29 ALGORITHM_GROUP Character(15) VARCHAR2(15) NOT NULL Algorithm Group for Payment Predictor

30 ALGORITHM Character(7) VARCHAR2(7) NOT NULL Algorithm Identifier

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

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

33 BANK_CD Character(5) VARCHAR2(5) NOT NULL Bank Code
Prompt Table: BANK_AR_BD_VW

34 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_AR_D_VW

35 UNPOST_REASON Character(10) VARCHAR2(10) NOT NULL Reason code for Unposted groups. For informational purposes.

36 WC_CR_CARD_AUTH_ST Character(1) VARCHAR2(1) NOT NULL Credit Card Auth Status
D=Denied
E=Validation Errors
K=No Action
M=Manually Approved/Settled
P=Authorized and Billed
R=Processing
U=Unprocessed/Retry
V=Pending Approval
W=Deleted after Settlement
Z=Zero Payment for Maintenance

37 CR_CARD_AUTH_DT Date(10) DATE Credit Card Authorization Date

38 BILL_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Customer

39 SETID Character(5) VARCHAR2(5) NOT NULL SetID

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	DEPOSIT_BU	Character(5)	VARCHAR2(5) NOT NULL	Identifies the business unit to which a deposit is posted. Prompt Table: SP_BUARDS_NONVW
2	DEPOSIT_ID	Character(15)	VARCHAR2(15) NOT NULL	Uniquely identifies a deposit of customer payments. The deposit ID can be manually assigned or system-generated. Prompt Table: DEPOSIT_CONTROL
3	PAYMENT_SEQ_NUM	Number(6,0)	INTEGER NOT NULL	Specifies the system-generated sequence number assigned to each payment as it is entered in a deposit.
4	PAYMENT_ID	Character(15)	VARCHAR2(15) NOT NULL	Identifies a customer payment. This is usually a check number but may be some other identifying number.
5	PAY_WS_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Payment Worksheet Type C=Credit Card Worksheet E=ePayment Worksheet P=Payment Worksheet
6	OPRID	Character(30)	VARCHAR2(30) NOT NULL	A user's ID (see PSOPRDEFN).
7	ASSN_OPRID	Character(30)	VARCHAR2(30) NOT NULL	Specifies the operator ID of the person who will enter items in a group. This is used when one person enters the control totals and another person enters the actual transactions. The assigned operator is also the person who processes the group and the person who is shown on group inquiry and processing panels.
8	PAYMENT_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Payment Status A=Applied B=Bounced C=Complete E=Identified - Express I=Identified J=Directly Journalled R=Unposted Direct Journal U=Unidentified W=Worksheet
9	ACCOUNTING_DT	Date(10)	DATE	The accounting entry construction date for a given transaction (a generic field that crosses multiple
10	ENTRY_DT	Date(10)	DATE	Specifies the date that a group of pending items was entered into the system. This date may be later or the same as the date that the items were received by the organization (See RECEIVED_DT).
11	PP_SW	Character(1)	VARCHAR2(1) NOT NULL	Payment Predictor
12	PAYMENT_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Specifies the amount of a customer payment.
13	PAYMENT_CURRENCY	Character(3)	VARCHAR2(3) NOT NULL	Identifies the currency of a customer payment. This is generally the same as the currency specified for the deposit but it can be different.
14	AMT_SEL	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Amount selected
15	AMT_ADJ	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Amount Adjusted
16	AMT_REM	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Amount Remaining
17	DISC_TAKEN	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Discount Taken
18	MISC_PAYMENT	Character(1)	VARCHAR2(1) NOT NULL	Journal Directly
19	CREATE_PEND_ITEMS	Character(1)	VARCHAR2(1) NOT NULL	Create Pending Items A=Do Not Post L=Batch Standard N=Batch Priority
20	WC_POST_ACTION	Character(1)	VARCHAR2(1) NOT NULL	A flag that indicates whether or not a group of pending items is ready to be processed by the Receivable Update program. The "action" requested can be no action, priority scheduled or standard scheduled. A=Do Not Post G=Post Now To GL L=Batch Standard N=Batch Priority P=Post Now
21	WC_POST_STATUS	Character(1)	VARCHAR2(1) NOT NULL	A flag that indicates whether or not a control group of pending items has been fully posted to customer accounts. The posting status also indicates whether or not there were errors encountered during posting. This field is automatically populated by the Receivables Update program. C=Complete E=Errors N=Not Posted
22	REMIT_FROM_SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID
23	REMIT_FROM_CUST_ID	Character(15)	VARCHAR2(15) NOT NULL	Remit From Customer
24	PYMT_RT_TYPE	Character(5)	VARCHAR2(5) NOT NULL	Specifies the type of exchange rate to be used when the currency of a customer payment is converted to the base currency of a business unit. The exchange rate type is also called the market rate type. Examples are spot rate current rate and official rate. Prompt Table: PYMT_RT_TYPE_VW
25	PYMT_RATE_MULT	Number(16,8)	DECIMAL(15,8) NOT NULL	Specifies the number (the multiplier) required to convert a payment amount from its entry currency to the business unit base currency. The number is based on the exchange rate specified for the transaction. It is used in conjunction with PYMT_ RATE_DIV. Refer to documentation on the Triangulation Process for the exact calculation.
26	PYMT_RATE_DIV	Number(16,8)	DECIMAL(15,8) NOT NULL	Specifies the number (the divisor) required to convert a payment amount from its entry currency to the business unit base currency. The number is based on the exchange rate specified for the transaction. It is used in conjunction with PYMT_RATE_MULT. Refer to documentation on the Triangulation Process for the exact calculation. Default Value: 1
27	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
28	PP_METHOD	Character(15)	VARCHAR2(15) NOT NULL	Payment Predictor Method
29	ALGORITHM_GROUP	Character(15)	VARCHAR2(15) NOT NULL	Algorithm Group for Payment Predictor
30	ALGORITHM	Character(7)	VARCHAR2(7) NOT NULL	Algorithm Identifier
31	BANK_ACCOUNT_NUM	Character(35)	VARCHAR2(35) NOT NULL	The literal unique identifier associated with a given bank/counterparty account.
32	BNK_ID_NBR	Character(20)	VARCHAR2(20) NOT NULL	A literal alphanumeric attribute that uniquely identifies a given bank or counterparty.
33	BANK_CD	Character(5)	VARCHAR2(5) NOT NULL	Bank Code Prompt Table: BANK_AR_BD_VW
34	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_AR_D_VW
35	UNPOST_REASON	Character(10)	VARCHAR2(10) NOT NULL	Reason code for Unposted groups. For informational purposes.
36	WC_CR_CARD_AUTH_ST	Character(1)	VARCHAR2(1) NOT NULL	Credit Card Auth Status D=Denied E=Validation Errors K=No Action M=Manually Approved/Settled P=Authorized and Billed R=Processing U=Unprocessed/Retry V=Pending Approval W=Deleted after Settlement Z=Zero Payment for Maintenance
37	CR_CARD_AUTH_DT	Date(10)	DATE	Credit Card Authorization Date
38	BILL_TO_CUST_ID	Character(15)	VARCHAR2(15) NOT NULL	Customer
39	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID