AR_WC_EXPDEP_VW (SQL View)	Index Back
Deposits - Not Posted

AR_WC_EXPDEP_VW

(SQL View)

Deposits - Not Posted

SELECT DISTINCT A.DEPOSIT_BU , A.DEPOSIT_ID , A.OPRID , A.ASSN_OPRID , A.BANK_CD , A.BANK_ACCT_KEY , A.BANK_ACCOUNT_NUM , A.BNK_ID_NBR , A.DEP_POST_STATUS ,A.DEP_POST_STATUS , B.POST_ACTION , A.BAL_STATUS , A.ENTRY_DT , A.ACCOUNTING_DT ,A.ENTERED_AMT ,A.CONTROL_AMT ,A.DEPOSIT_TYPE ,A.FORMAT_CURRENCY FROM PS_DEPOSIT_CONTROL A LEFT OUTER JOIN PS_GROUP_CONTROL B ON A.DEPOSIT_BU= B.DEPOSIT_BU AND B.POST_STATUS<>'C' AND A.DEPOSIT_ID= B.DEPOSIT_ID WHERE A.DEP_POST_STATUS <> 'C' AND A.PAY_WS_TYPE = 'P' AND NOT EXISTS( SELECT 'X' FROM PS_PAYMENT P WHERE P.DEPOSIT_BU=A.DEPOSIT_BU AND P.DEPOSIT_ID=A.DEPOSIT_ID AND P.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_BUARDE_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.

3 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
Prompt Table: OPR_DEF_VW_AR

4 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.
Prompt Table: OPR_DEF_VW_AR

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

6 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

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

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

9 DEP_POST_STATUS Character(1) VARCHAR2(1) NOT NULL Posting Status
A=All
C=Complete
N=Not Posted
P=Partial

10 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

11 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

12 BAL_STATUS Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not the control totals for a deposit match the entered totals. A deposit is considered "balanced" when the two sets of totals are equal.
I=Balanced
O=Not Balanced

13 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).

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

15 ENTERED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Represents the total currency amount of all pending items actually entered in a group and is compared with the control total amount. The Receivables Update program automatically calculates this total.

16 CONTROL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Specifies the total monetary amount of all transactions within a group of pending items using the entry currency of each item. For mixed currencies this becomes a hash total. Receivables compares the control amount to the actual amount entered and calculates the difference in order to help verify that all entries have been made correctly.

17 DEPOSIT_TYPE Character(1) VARCHAR2(1) NOT NULL Deposit Type
A=Amount
B=Balance of Net Pay
P=Percent
Prompt Table: DEPOST_TYPE_TBL

18 FORMAT_CURRENCY Character(3) VARCHAR2(3) NOT NULL Specifies the currency that controls the precision of the amount totals for a group of pending items. If CONTROL_CURRENCY is entered then the same currency code should be used here.

#	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_BUARDE_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.
3	OPRID	Character(30)	VARCHAR2(30) NOT NULL	A user's ID (see PSOPRDEFN). Prompt Table: OPR_DEF_VW_AR
4	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. Prompt Table: OPR_DEF_VW_AR
5	BANK_CD	Character(5)	VARCHAR2(5) NOT NULL	Bank Code
6	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
7	BANK_ACCOUNT_NUM	Character(35)	VARCHAR2(35) NOT NULL	The literal unique identifier associated with a given bank/counterparty account.
8	BNK_ID_NBR	Character(20)	VARCHAR2(20) NOT NULL	A literal alphanumeric attribute that uniquely identifies a given bank or counterparty.
9	DEP_POST_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Posting Status A=All C=Complete N=Not Posted P=Partial
10	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
11	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
12	BAL_STATUS	Character(1)	VARCHAR2(1) NOT NULL	A flag that indicates whether or not the control totals for a deposit match the entered totals. A deposit is considered "balanced" when the two sets of totals are equal. I=Balanced O=Not Balanced
13	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).
14	ACCOUNTING_DT	Date(10)	DATE NOT NULL	The accounting entry construction date for a given transaction (a generic field that crosses multiple Default Value: %date
15	ENTERED_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Represents the total currency amount of all pending items actually entered in a group and is compared with the control total amount. The Receivables Update program automatically calculates this total.
16	CONTROL_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Specifies the total monetary amount of all transactions within a group of pending items using the entry currency of each item. For mixed currencies this becomes a hash total. Receivables compares the control amount to the actual amount entered and calculates the difference in order to help verify that all entries have been made correctly.
17	DEPOSIT_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Deposit Type A=Amount B=Balance of Net Pay P=Percent Prompt Table: DEPOST_TYPE_TBL
18	FORMAT_CURRENCY	Character(3)	VARCHAR2(3) NOT NULL	Specifies the currency that controls the precision of the amount totals for a group of pending items. If CONTROL_CURRENCY is entered then the same currency code should be used here.