TRN_SINGLES_VW (SQL View)	Index Back
Worksheet Single Item View

TRN_SINGLES_VW

(SQL View)

Worksheet Single Item View

SELECT w.trn_bu , w.trn_id , 'N' , I.business_unit , I.cust_id , I.item , I.item_line , 0 , I.entry_use_id , I.entry_type , I.entry_reason , ' ' , I.bal_amt , I.accounting_dt , I.asof_dt , I.due_dt , I.po_ref , I.document , I.document_line ,I.dst_id_ar , I.bill_of_lading , I.subcust_qual1 , I.subcust_qual2 , I.bal_currency , I.rate_mult , I.rate_div , I.rt_type , I.bal_amt_base , I.currency_cd , I.pymnt_terms_cd , I.accounting_dt , I.entry_type , I.entry_reason , 0 , b.business_unit_gl , I.disc_dt , ' ' ,0 ,0 ,0 , ' ' ,' ' ,0 ,I.bal_amt ,I.bal_amt_base ,' ' ,0 ,I.order_no , I.contract_num , I.entry_use_id , I.cr_analyst , I.sales_person , I.collector , I.collector , I.sales_person , I.cr_analyst , I.dst_id_ar , I.entry_type , I.entry_reason , i.item_status , i.process_instance , I.draft_status , I.business_unit_ca , I.contract_line_num , I.subrog_case_num ,I.convers_exists FROM ps_trn_control w , PS_ITEM I , ps_bus_unit_tbl_ar B WHERE b.business_unit = i.business_unit AND item_status='O'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 TRN_BU Character(5) VARCHAR2(5) NOT NULL Transfer Business Unit

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

3 ITEM_SELECTED Character(1) VARCHAR2(1) NOT NULL Item selected switch

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

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

6 ITEM Character(30) VARCHAR2(30) NOT NULL Uniquely identifies an invoice credit memo or debit memo. The item ID is also called the invoice number.

7 ITEM_LINE Number(6,0) INTEGER NOT NULL Item Line

8 WS_SEQ Number(6,0) INTEGER NOT NULL Worksheet Sequence Number

9 ENTRY_USE_ID Character(5) VARCHAR2(5) NOT NULL Specifies the System Function or type of processing associated with an entry type.

10 ENTRY_TYPE Character(5) VARCHAR2(5) NOT NULL Specifies the type of transaction associated with a pending item. The entry type remains with the item after it is posted and affects many areas of Receivables processing. Some examples of entry types are Invoices Credit Memo Prepayment and Deduction.

11 ENTRY_REASON Character(5) VARCHAR2(5) NOT NULL "Specifies an entry reason that further qualifies the entry type associated with a pending item. For example an entry reason for a credit memo might be ""Pricing Error"" or ""Shipping Error""."
Prompt Table: AUTO_REASN_VW

12 REQUIRE_REASON Character(1) VARCHAR2(1) NOT NULL Require Reason

13 BAL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Item Balance

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

15 ASOF_DT Date(10) DATE As of Date

16 DUE_DT Date(10) DATE Specifies the date that a transaction is due. It is a generic field used in multiple PeopleSoft applications including Receivables Payables and Purchasing. For example it represents the date that payment is due for a voucher or the date that a scheduled shipment is due to be received.

17 PO_REF Character(30) VARCHAR2(30) NOT NULL Specifies the purchase order number associated with a receivables item.

18 DOCUMENT Character(30) VARCHAR2(30) NOT NULL Specifies a document used as a reference number for a Receivables item. Document ID is used by the Receivables Update program and the Maintenance Worksheet to "match" related invoices debit memos and credit memos.

19 DOCUMENT_LINE Number(6,0) INTEGER NOT NULL Specifies the line number of a Document ID used as a reference on a Receivables item.

20 DST_ID_AR Character(10) VARCHAR2(10) NOT NULL Specifies the general ledger ChartField combination that is affected when subsequent activity such as a payment occurs for the item.

21 BILL_OF_LADING Character(30) VARCHAR2(30) NOT NULL Specifies a bill of lading number associated with a Receivables item.

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

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

24 BAL_CURRENCY Character(3) VARCHAR2(3) NOT NULL Currency Code - Bal Amount

25 RATE_MULT Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier

26 RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL Rate Divisor
Default Value: 1

27 RT_TYPE Character(5) VARCHAR2(5) NOT NULL Defines a category of market rates for currency conversion. Some examples of rate types are commercial, average, floating, and historical.

28 BAL_AMT_BASE Signed Number(28,3) DECIMAL(26,3) NOT NULL Balance - Base Currency

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

30 PYMNT_TERMS_CD Character(5) VARCHAR2(5) NOT NULL Specifies how the payment due date and discount due date are calculated. A payment terms code is associated with various business units bill-to customers vendors as well as sales orders purchase orders and vouchers.

31 ITEM_ACCTG_DT Date(10) DATE Specifies the accounting date of the original item and appears on subsequent entries. This field is used in multicurrency processing.

32 ITEM_ENTRY_TYPE Character(5) VARCHAR2(5) NOT NULL Original Item Entry Type

33 ITEM_ENTRY_REASON Character(5) VARCHAR2(5) NOT NULL Original Item Entry Reason

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

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

36 DISC_DT Date(10) DATE Specifies the date before which payment must be made on an invoice in order to qualify for a discount. This field applies to the first of two possible discounts and is associated with the discount amount specified in DISC_AMT.

37 MATCH_SEQ Character(1) VARCHAR2(1) NOT NULL Match Sequence

38 REAL_GAIN_LOSS Signed Number(28,3) DECIMAL(26,3) NOT NULL Represents the monetary amount of realized gain or loss calculated by the system during payment application or maintenance worksheet (Receivables).

39 RATE_MULT_ORIG Number(16,8) DECIMAL(15,8) NOT NULL Original Rate Multiplier

40 RATE_DIV_ORIG Number(16,8) DECIMAL(15,8) NOT NULL Original Rate Divisor
Default Value: 1

41 DST_ID_REAL Character(10) VARCHAR2(10) NOT NULL Specifies the general ledger ChartField combination for posting realized gain or loss calculated by the system during payment application or maintenance worksheet (Receivables).

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

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

44 ITEM_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount

45 ITEM_AMT_BASE Signed Number(28,3) DECIMAL(26,3) NOT NULL Item Amount Base

46 ITEM_MTCH Character(33) VARCHAR2(33) NOT NULL Matched Item

47 ITEM_LINE_MTCH Number(6,0) INTEGER NOT NULL Matched Item Line

48 ORDER_NO Character(10) VARCHAR2(10) NOT NULL Identifies a customer order number that appears as a reference on a receivables pending item. Date Initials Issue Description 022701 mv SP2 (F-CJORGENS-3) CSR Desktop

49 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract

50 ITEM_ENTRY_USE Character(5) VARCHAR2(5) NOT NULL Item Entry Use ID

51 CR_ANALYST Character(8) VARCHAR2(8) NOT NULL Specifies the personnel code for the person who works with a customer to establish credit limits and payment terms. This field is required for a bill-to customer.

52 SALES_PERSON Character(8) VARCHAR2(8) NOT NULL Specifies the personnel code for the person who works with a customer as a sales representative. This field is required for a customer that functions as a bill-to customer.

53 COLLECTOR Character(8) VARCHAR2(8) NOT NULL Specifies the personnel code for the person who works with a customer to collect overdue payments.

54 TO_COLLECTOR Character(8) VARCHAR2(8) NOT NULL Transfer To Collector

55 TO_SALES_PERSON Character(8) VARCHAR2(8) NOT NULL Transfer To Sales Person

56 TO_CR_ANALYST Character(8) VARCHAR2(8) NOT NULL Transfer To Credit Analyst

57 TO_DST_ID_AR Character(10) VARCHAR2(10) NOT NULL Transfer To Dist ID AR

58 TO_ENTRY_TYPE Character(5) VARCHAR2(5) NOT NULL Transfer To Entry Type

59 TO_ENTRY_REASON Character(5) VARCHAR2(5) NOT NULL Transfer To Entry Reason

60 ITEM_STATUS Character(1) VARCHAR2(1) NOT NULL Item Status
C=Closed
O=Open

61 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance

62 DRAFT_STATUS Character(1) VARCHAR2(1) NOT NULL Draft Status
A=Accepted
C=Complete
D=Dishonored
E=Void
I=Identified
L=Collateral
M=Discounted
N=No Action
P=Pending
R=Remitted
T=Endorsed

63 BUSINESS_UNIT_CA Character(5) VARCHAR2(5) NOT NULL Contracts Business Unit

64 CONTRACT_LINE_NUM Number(3,0) SMALLINT NOT NULL Contract Line Num

65 SUBROG_CASE_NUM Character(30) VARCHAR2(30) NOT NULL Subrogation case number used for Insurance

66 CONVERS_EXISTS Character(1) VARCHAR2(1) NOT NULL "A flag that indicates whether a conversation is associated with a single item or with multiple items ."

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	TRN_BU	Character(5)	VARCHAR2(5) NOT NULL	Transfer Business Unit
2	TRN_ID	Character(15)	VARCHAR2(15) NOT NULL	Transfer Worksheet ID
3	ITEM_SELECTED	Character(1)	VARCHAR2(1) NOT NULL	Item selected switch
4	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
5	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.
6	ITEM	Character(30)	VARCHAR2(30) NOT NULL	Uniquely identifies an invoice credit memo or debit memo. The item ID is also called the invoice number.
7	ITEM_LINE	Number(6,0)	INTEGER NOT NULL	Item Line
8	WS_SEQ	Number(6,0)	INTEGER NOT NULL	Worksheet Sequence Number
9	ENTRY_USE_ID	Character(5)	VARCHAR2(5) NOT NULL	Specifies the System Function or type of processing associated with an entry type.
10	ENTRY_TYPE	Character(5)	VARCHAR2(5) NOT NULL	Specifies the type of transaction associated with a pending item. The entry type remains with the item after it is posted and affects many areas of Receivables processing. Some examples of entry types are Invoices Credit Memo Prepayment and Deduction.
11	ENTRY_REASON	Character(5)	VARCHAR2(5) NOT NULL	"Specifies an entry reason that further qualifies the entry type associated with a pending item. For example an entry reason for a credit memo might be ""Pricing Error"" or ""Shipping Error""." Prompt Table: AUTO_REASN_VW
12	REQUIRE_REASON	Character(1)	VARCHAR2(1) NOT NULL	Require Reason
13	BAL_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Item Balance
14	ACCOUNTING_DT	Date(10)	DATE	The accounting entry construction date for a given transaction (a generic field that crosses multiple
15	ASOF_DT	Date(10)	DATE	As of Date
16	DUE_DT	Date(10)	DATE	Specifies the date that a transaction is due. It is a generic field used in multiple PeopleSoft applications including Receivables Payables and Purchasing. For example it represents the date that payment is due for a voucher or the date that a scheduled shipment is due to be received.
17	PO_REF	Character(30)	VARCHAR2(30) NOT NULL	Specifies the purchase order number associated with a receivables item.
18	DOCUMENT	Character(30)	VARCHAR2(30) NOT NULL	Specifies a document used as a reference number for a Receivables item. Document ID is used by the Receivables Update program and the Maintenance Worksheet to "match" related invoices debit memos and credit memos.
19	DOCUMENT_LINE	Number(6,0)	INTEGER NOT NULL	Specifies the line number of a Document ID used as a reference on a Receivables item.
20	DST_ID_AR	Character(10)	VARCHAR2(10) NOT NULL	Specifies the general ledger ChartField combination that is affected when subsequent activity such as a payment occurs for the item.
21	BILL_OF_LADING	Character(30)	VARCHAR2(30) NOT NULL	Specifies a bill of lading number associated with a Receivables item.
22	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.
23	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.
24	BAL_CURRENCY	Character(3)	VARCHAR2(3) NOT NULL	Currency Code - Bal Amount
25	RATE_MULT	Signed Number(17,8)	DECIMAL(15,8) NOT NULL	Rate Multiplier
26	RATE_DIV	Number(16,8)	DECIMAL(15,8) NOT NULL	Rate Divisor Default Value: 1
27	RT_TYPE	Character(5)	VARCHAR2(5) NOT NULL	Defines a category of market rates for currency conversion. Some examples of rate types are commercial, average, floating, and historical.
28	BAL_AMT_BASE	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Balance - Base Currency
29	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
30	PYMNT_TERMS_CD	Character(5)	VARCHAR2(5) NOT NULL	Specifies how the payment due date and discount due date are calculated. A payment terms code is associated with various business units bill-to customers vendors as well as sales orders purchase orders and vouchers.
31	ITEM_ACCTG_DT	Date(10)	DATE	Specifies the accounting date of the original item and appears on subsequent entries. This field is used in multicurrency processing.
32	ITEM_ENTRY_TYPE	Character(5)	VARCHAR2(5) NOT NULL	Original Item Entry Type
33	ITEM_ENTRY_REASON	Character(5)	VARCHAR2(5) NOT NULL	Original Item Entry Reason
34	AMT_REM	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Amount Remaining
35	BUSINESS_UNIT_GL	Character(5)	VARCHAR2(5) NOT NULL	GL Business Unit
36	DISC_DT	Date(10)	DATE	Specifies the date before which payment must be made on an invoice in order to qualify for a discount. This field applies to the first of two possible discounts and is associated with the discount amount specified in DISC_AMT.
37	MATCH_SEQ	Character(1)	VARCHAR2(1) NOT NULL	Match Sequence
38	REAL_GAIN_LOSS	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Represents the monetary amount of realized gain or loss calculated by the system during payment application or maintenance worksheet (Receivables).
39	RATE_MULT_ORIG	Number(16,8)	DECIMAL(15,8) NOT NULL	Original Rate Multiplier
40	RATE_DIV_ORIG	Number(16,8)	DECIMAL(15,8) NOT NULL	Original Rate Divisor Default Value: 1
41	DST_ID_REAL	Character(10)	VARCHAR2(10) NOT NULL	Specifies the general ledger ChartField combination for posting realized gain or loss calculated by the system during payment application or maintenance worksheet (Receivables).
42	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID
43	AMT_SEL	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Amount selected
44	ITEM_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Amount
45	ITEM_AMT_BASE	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Item Amount Base
46	ITEM_MTCH	Character(33)	VARCHAR2(33) NOT NULL	Matched Item
47	ITEM_LINE_MTCH	Number(6,0)	INTEGER NOT NULL	Matched Item Line
48	ORDER_NO	Character(10)	VARCHAR2(10) NOT NULL	Identifies a customer order number that appears as a reference on a receivables pending item. Date Initials Issue Description 022701 mv SP2 (F-CJORGENS-3) CSR Desktop
49	CONTRACT_NUM	Character(25)	VARCHAR2(25) NOT NULL	Contract
50	ITEM_ENTRY_USE	Character(5)	VARCHAR2(5) NOT NULL	Item Entry Use ID
51	CR_ANALYST	Character(8)	VARCHAR2(8) NOT NULL	Specifies the personnel code for the person who works with a customer to establish credit limits and payment terms. This field is required for a bill-to customer.
52	SALES_PERSON	Character(8)	VARCHAR2(8) NOT NULL	Specifies the personnel code for the person who works with a customer as a sales representative. This field is required for a customer that functions as a bill-to customer.
53	COLLECTOR	Character(8)	VARCHAR2(8) NOT NULL	Specifies the personnel code for the person who works with a customer to collect overdue payments.
54	TO_COLLECTOR	Character(8)	VARCHAR2(8) NOT NULL	Transfer To Collector
55	TO_SALES_PERSON	Character(8)	VARCHAR2(8) NOT NULL	Transfer To Sales Person
56	TO_CR_ANALYST	Character(8)	VARCHAR2(8) NOT NULL	Transfer To Credit Analyst
57	TO_DST_ID_AR	Character(10)	VARCHAR2(10) NOT NULL	Transfer To Dist ID AR
58	TO_ENTRY_TYPE	Character(5)	VARCHAR2(5) NOT NULL	Transfer To Entry Type
59	TO_ENTRY_REASON	Character(5)	VARCHAR2(5) NOT NULL	Transfer To Entry Reason
60	ITEM_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Item Status C=Closed O=Open
61	PROCESS_INSTANCE	Number(10,0)	DECIMAL(10) NOT NULL	Process Instance
62	DRAFT_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Draft Status A=Accepted C=Complete D=Dishonored E=Void I=Identified L=Collateral M=Discounted N=No Action P=Pending R=Remitted T=Endorsed
63	BUSINESS_UNIT_CA	Character(5)	VARCHAR2(5) NOT NULL	Contracts Business Unit
64	CONTRACT_LINE_NUM	Number(3,0)	SMALLINT NOT NULL	Contract Line Num
65	SUBROG_CASE_NUM	Character(30)	VARCHAR2(30) NOT NULL	Subrogation case number used for Insurance
66	CONVERS_EXISTS	Character(1)	VARCHAR2(1) NOT NULL	"A flag that indicates whether a conversation is associated with a single item or with multiple items ."