AR_TRNW_FLTR_VW (SQL View)	Index Back
AR WorkCenter Filter

AR_TRNW_FLTR_VW

(SQL View)

AR WorkCenter Filter

SELECT DISTINCT A.TRN_BU , A.TRN_ID , A.OPRID , A.ASSN_OPRID , A.BUSINESS_UNIT , A.GROUP_TYPE , A.BAL_STATUS , A.POST_STATUS , A.POST_ACTION , A.ENTRY_DT , A.ACCOUNTING_DT , A.ORIGIN_ID , B.CUST_ID , B.SUBCUST_QUAL1 , B.SUBCUST_QUAL2 , C.REF_QUALIFIER_CODE , C.REF_VALUE , C.REF_VALUE_TO , D.TO_BU , D.TO_CUST_ID , D.TO_SUBCUST_QUAL1 , D.TO_SUBCUST_QUAL2 , A.EXCLUDE_COLLECTION , A.EXCLUDE_DEDUCTIONS , A.EXCLUDE_DISPUTES , A.DOUBTFUL , A.DST_ID_TRAN_CTL ,A.ENTERED_AMT ,A.CURRENCY_CD FROM PS_TRN_CONTROL A , PS_TRN_ID_CUST B , PS_TRN_ID_ITEM C , PS_TRN_ITEM D WHERE A.TRN_BU= B.TRN_BU AND A.TRN_ID=B.TRN_ID AND B.TRN_BU=C.TRN_BU AND B.TRN_ID=C.TRN_ID AND A.TRN_BU=D.TRN_BU AND A.TRN_ID=D.TRN_ID AND A.BUSINESS_UNIT= D.BUSINESS_UNIT AND B.CUST_ID= D.CUST_ID AND A.POST_STATUS <>'C'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 TRN_BU Character(5) VARCHAR2(5) NOT NULL Transfer Business Unit
Prompt Table: SP_BUARTR_NONVW

2 TRN_ID Character(15) VARCHAR2(15) NOT NULL Transfer Worksheet ID

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

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: OPRID_VW

5 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

6 GROUP_TYPE Character(1) VARCHAR2(1) NOT NULL Specifies a category of transactions included in a group of receivables pending items. The group type affects processing of the transactions and is useful for as a sort/selection option for reports and inquiries. Examples of group type are Billing (for invoices) Payments and Transfers.

7 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

8 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

9 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

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 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple

12 ORIGIN_ID Character(5) VARCHAR2(5) NOT NULL Specifies the source or method of entry for a pending item group.

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

14 SUBCUST_QUAL1 Character(15) VARCHAR2(15) NOT NULL Specifies a user-defined grouping of customer receivables activity. A subcustomer qualifier provides the ability to record and report receivables activity across multiple business units and/or multiple customers. For example a subcustomer qualifier could represent a sales region or a geographic area. There are two possible subcustomer qualifiers: SUBCUST_QUAL1 and SUBCUST_QUAL2.

15 SUBCUST_QUAL2 Character(15) VARCHAR2(15) NOT NULL Specifies a user-defined grouping of customer receivables activity. A subcustomer qualifier provides the ability to record and report receivables activity across multiple business units and/or multiple customers. For example a subcustomer qualifier could represent a sales region or a geographic area. There are two possible subcustomer qualifiers: SUBCUST_QUAL1 and SUBCUST_QUAL2.

16 REF_QUALIFIER_CODE Character(2) VARCHAR2(2) NOT NULL Reference Qualifier Code

17 REF_VALUE Character(30) VARCHAR2(30) NOT NULL Reference

18 REF_VALUE_TO Character(30) VARCHAR2(30) NOT NULL Range for Reference Value

19 TO_BU Character(5) VARCHAR2(5) NOT NULL To Business Unit

20 TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL To Customer ID

21 TO_SUBCUST_QUAL1 Character(15) VARCHAR2(15) NOT NULL To Subcustomer Qualifier 1

22 TO_SUBCUST_QUAL2 Character(15) VARCHAR2(15) NOT NULL To Subcustomer Qualifier 2

23 EXCLUDE_COLLECTION Character(1) VARCHAR2(1) NOT NULL Exclude Collection Items
Y/N Table Edit

24 EXCLUDE_DEDUCTIONS Character(1) VARCHAR2(1) NOT NULL Exclude Deduction Items
Y/N Table Edit

25 EXCLUDE_DISPUTES Character(1) VARCHAR2(1) NOT NULL Exclude Dispute Items
Y/N Table Edit

26 DOUBTFUL Character(1) VARCHAR2(1) NOT NULL Identifies items ( or invoices ) which probably will not be paid by the customer. This status is used for items whose balances have been moved from the receivables account to the doubtful account using the transfer worksheet.
Y/N Table Edit

27 DST_ID_TRAN_CTL Character(10) VARCHAR2(10) NOT NULL 10 character value used to identify the transfer worksheet control distribution code.
Prompt Table: DST_CODE_TRN_VW

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

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

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	TRN_BU	Character(5)	VARCHAR2(5) NOT NULL	Transfer Business Unit Prompt Table: SP_BUARTR_NONVW
2	TRN_ID	Character(15)	VARCHAR2(15) NOT NULL	Transfer Worksheet ID
3	OPRID	Character(30)	VARCHAR2(30) NOT NULL	A user's ID (see PSOPRDEFN). Prompt Table: OPRID_VW
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: OPRID_VW
5	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
6	GROUP_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Specifies a category of transactions included in a group of receivables pending items. The group type affects processing of the transactions and is useful for as a sort/selection option for reports and inquiries. Examples of group type are Billing (for invoices) Payments and Transfers.
7	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
8	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
9	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
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	ACCOUNTING_DT	Date(10)	DATE	The accounting entry construction date for a given transaction (a generic field that crosses multiple
12	ORIGIN_ID	Character(5)	VARCHAR2(5) NOT NULL	Specifies the source or method of entry for a pending item group.
13	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.
14	SUBCUST_QUAL1	Character(15)	VARCHAR2(15) NOT NULL	Specifies a user-defined grouping of customer receivables activity. A subcustomer qualifier provides the ability to record and report receivables activity across multiple business units and/or multiple customers. For example a subcustomer qualifier could represent a sales region or a geographic area. There are two possible subcustomer qualifiers: SUBCUST_QUAL1 and SUBCUST_QUAL2.
15	SUBCUST_QUAL2	Character(15)	VARCHAR2(15) NOT NULL	Specifies a user-defined grouping of customer receivables activity. A subcustomer qualifier provides the ability to record and report receivables activity across multiple business units and/or multiple customers. For example a subcustomer qualifier could represent a sales region or a geographic area. There are two possible subcustomer qualifiers: SUBCUST_QUAL1 and SUBCUST_QUAL2.
16	REF_QUALIFIER_CODE	Character(2)	VARCHAR2(2) NOT NULL	Reference Qualifier Code
17	REF_VALUE	Character(30)	VARCHAR2(30) NOT NULL	Reference
18	REF_VALUE_TO	Character(30)	VARCHAR2(30) NOT NULL	Range for Reference Value
19	TO_BU	Character(5)	VARCHAR2(5) NOT NULL	To Business Unit
20	TO_CUST_ID	Character(15)	VARCHAR2(15) NOT NULL	To Customer ID
21	TO_SUBCUST_QUAL1	Character(15)	VARCHAR2(15) NOT NULL	To Subcustomer Qualifier 1
22	TO_SUBCUST_QUAL2	Character(15)	VARCHAR2(15) NOT NULL	To Subcustomer Qualifier 2
23	EXCLUDE_COLLECTION	Character(1)	VARCHAR2(1) NOT NULL	Exclude Collection Items Y/N Table Edit
24	EXCLUDE_DEDUCTIONS	Character(1)	VARCHAR2(1) NOT NULL	Exclude Deduction Items Y/N Table Edit
25	EXCLUDE_DISPUTES	Character(1)	VARCHAR2(1) NOT NULL	Exclude Dispute Items Y/N Table Edit
26	DOUBTFUL	Character(1)	VARCHAR2(1) NOT NULL	Identifies items ( or invoices ) which probably will not be paid by the customer. This status is used for items whose balances have been moved from the receivables account to the doubtful account using the transfer worksheet. Y/N Table Edit
27	DST_ID_TRAN_CTL	Character(10)	VARCHAR2(10) NOT NULL	10 character value used to identify the transfer worksheet control distribution code. Prompt Table: DST_CODE_TRN_VW
28	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.
29	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code