AR_SSPAYPEP_VW1 (SQL View)	Index Back
Pend EC Payments Pending EC payments This view dispaly EC payment already processed by EC Process, but Receivables Update have not been run for them yet.

AR_SSPAYPEP_VW1

(SQL View)

Pend EC Payments

Pending EC payments This view dispaly EC payment already processed by EC Process, but Receivables Update have not been run for them yet.

SELECT DISTINCT C.SETID , P.BILL_TO_CUST_ID , P.DEPOSIT_BU , P.DEPOSIT_ID , P.PAYMENT_SEQ_NUM , P.PAYMENT_ID , P.PAYMENT_STATUS , 'R' , D.RECEIVED_DT , P.ACCOUNTING_DT , P.POST_DT , P.PAYMENT_AMT , P.PAYMENT_CURRENCY , P.PAYMENT_METHOD FROM PS_DEPOSIT_CONTROL D , PS_PAYMENT P , PS_PAYMENT_ITEM PI , PS_SET_CNTRL_REC C WHERE D.DEPOSIT_BU = P.DEPOSIT_BU AND D.DEPOSIT_ID = P.DEPOSIT_ID AND P.DEPOSIT_BU = PI.DEPOSIT_BU AND P.DEPOSIT_ID = PI.DEPOSIT_ID AND P.PAYMENT_SEQ_NUM = PI.PAYMENT_SEQ_NUM AND P.PAYMENT_STATUS NOT IN ('C' ,'W') AND P.PAYMENT_METHOD IN ('EC', 'PL') AND P.MISC_PAYMENT = 'N' AND P.BILL_TO_CUST_ID <> ' ' AND PI.ITEM_SELECTED = 'Y' AND C.SETCNTRLVALUE= PI.BUSINESS_UNIT AND C.RECNAME = 'CUSTOMER' AND NOT EXISTS ( SELECT 'X' FROM PS_PAYMENT_ID_CUST IC WHERE IC.DEPOSIT_BU = P.DEPOSIT_BU AND IC.DEPOSIT_ID = P.DEPOSIT_ID AND IC.PAYMENT_SEQ_NUM = P.PAYMENT_SEQ_NUM)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

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

3 DEPOSIT_BU Character(5) VARCHAR2(5) NOT NULL Identifies the business unit to which a deposit is posted.

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

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

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

7 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

8 PAYMENT_STATUS_SS Character(1) VARCHAR2(1) NOT NULL Payment Applied
C=Yes
R=No

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

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

11 POST_DT Date(10) DATE The Date a transaction was Posted.

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 PAYMENT_METHOD Character(3) VARCHAR2(3) NOT NULL Specifies the preferred method of payment by a customer. Examples of payment method are check direct debit and draft. 10/28/2003 edw 655070000 - Removed LTC and CSH xlats.
ACH=ACH
CC=Credit Card
CHK=Check
CSH=Cash
DD=Direct Debit
DR=Draft
EC=Electronic Check
EFT=Electronic Fund Transfer
GE=Giro - EFT
PL=PayPal
WIR=Wire Report

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID
2	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.
3	DEPOSIT_BU	Character(5)	VARCHAR2(5) NOT NULL	Identifies the business unit to which a deposit is posted.
4	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.
5	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.
6	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.
7	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
8	PAYMENT_STATUS_SS	Character(1)	VARCHAR2(1) NOT NULL	Payment Applied C=Yes R=No
9	RECEIVED_DT	Date(10)	DATE	Specifies the date that a group of pending items was received by the organization. This date may be earlier or the same as the date that the items were actually entered into the system (ENTRY_DT).
10	ACCOUNTING_DT	Date(10)	DATE	The accounting entry construction date for a given transaction (a generic field that crosses multiple
11	POST_DT	Date(10)	DATE	The Date a transaction was Posted.
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	PAYMENT_METHOD	Character(3)	VARCHAR2(3) NOT NULL	Specifies the preferred method of payment by a customer. Examples of payment method are check direct debit and draft. 10/28/2003 edw 655070000 - Removed LTC and CSH xlats. ACH=ACH CC=Credit Card CHK=Check CSH=Cash DD=Direct Debit DR=Draft EC=Electronic Check EFT=Electronic Fund Transfer GE=Giro - EFT PL=PayPal WIR=Wire Report