C360_ITACTV_VW (SQL View)	Index Back
Customer Item Activity

C360_ITACTV_VW

(SQL View)

Customer Item Activity

SELECT C.SETID , I.BUSINESS_UNIT , I.CUST_ID ,I.POST_DT ,I.CR_ANALYST ,I.COLLECTOR ,I.SALES_PERSON ,I.ENTRY_TYPE ,I.ENTRY_REASON , I.ENTRY_CURRENCY , SUM(CASE WHEN I.ENTRY_USE_ID = 'FC-01' THEN I.ENTRY_AMT ELSE 0 END) AS OVERDUE_AMT , SUM(CASE I.ENTRY_USE_ID WHEN 'WS-09' THEN I.ENTRY_AMT WHEN 'MT-03' THEN I.ENTRY_AMT WHEN 'MT-02' THEN I.ENTRY_AMT ELSE 0 END) AS ACV_WRITEOFF , %Sql(EOEW_FUNCLIB_DATE_MONTH_UPD, I.post_dt) , %Sql(EOEW_FUNCLIB_DATE_YEAR_UPD, I.post_dt) ,A.AR_SPECIALIST ,A.DUE_DT ,I.SUBCUST_QUAL1 ,I.SUBCUST_QUAL2 ,I.ACCOUNTING_DT , %Sql(C360_FUNCLIB_DATE_YYYYMM, I.post_dt) FROM %Table(SET_CNTRL_REC) C , %Table(ITEM) A , %Table(ITEM_ACTIVITY) I WHERE A.BUSINESS_UNIT= I.BUSINESS_UNIT AND A.CUST_ID=I.CUST_ID AND A.ITEM= I.ITEM AND A.ITEM_LINE= I.ITEM_LINE AND C.RECNAME = 'CUSTOMER' AND C.SETCNTRLVALUE = I.BUSINESS_UNIT AND (I.ENTRY_USE_ID = 'FC-01' OR I.ENTRY_USE_ID = 'MT-02' OR I.ENTRY_USE_ID = 'MT-03' OR I.ENTRY_USE_ID = 'WS-09') GROUP BY C.SETID , I.BUSINESS_UNIT , I.CUST_ID , I.POST_DT ,I.CR_ANALYST ,I.COLLECTOR ,I.SALES_PERSON ,I.ENTRY_TYPE ,I.ENTRY_REASON,I.ENTRY_CURRENCY,A.AR_SPECIALIST ,A.DUE_DT ,I.SUBCUST_QUAL1 ,I.SUBCUST_QUAL2 ,I.ACCOUNTING_DT

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
Prompt Table: SP_SETID_NONVW

2 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
Prompt Table: SP_BUARDS_NONVW

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

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

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

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

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

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

9 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""."

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

11 OVERDUE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Overdue Amount

12 ACV_WRITEOFF Signed Number(28,3) DECIMAL(26,3) NOT NULL Write Off

13 C360_POS_MTH Character(2) VARCHAR2(2) NOT NULL Posted Month
01=01-January
02=02-February
03=03-March
04=04-April
05=05-May
06=06-June
07=07-July
08=08-August
09=09-September
10=10-October
11=11-November
12=12-December

14 C360_POS_YEAR Character(4) VARCHAR2(4) NOT NULL Posted Year

15 AR_SPECIALIST Character(8) VARCHAR2(8) NOT NULL Accounts Recievable Specialist - person responsible for an action on an item.

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

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

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

20 C360_POS_PERIOD Character(8) VARCHAR2(8) NOT NULL Period

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID Prompt Table: SP_SETID_NONVW
2	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit Prompt Table: SP_BUARDS_NONVW
3	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.
4	POST_DT	Date(10)	DATE	The Date a transaction was Posted.
5	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.
6	COLLECTOR	Character(8)	VARCHAR2(8) NOT NULL	Specifies the personnel code for the person who works with a customer to collect overdue payments.
7	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.
8	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.
9	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""."
10	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
11	OVERDUE_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Overdue Amount
12	ACV_WRITEOFF	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Write Off
13	C360_POS_MTH	Character(2)	VARCHAR2(2) NOT NULL	Posted Month 01=01-January 02=02-February 03=03-March 04=04-April 05=05-May 06=06-June 07=07-July 08=08-August 09=09-September 10=10-October 11=11-November 12=12-December
14	C360_POS_YEAR	Character(4)	VARCHAR2(4) NOT NULL	Posted Year
15	AR_SPECIALIST	Character(8)	VARCHAR2(8) NOT NULL	Accounts Recievable Specialist - person responsible for an action on an item.
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	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.
18	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.
19	ACCOUNTING_DT	Date(10)	DATE	The accounting entry construction date for a given transaction (a generic field that crosses multiple
20	C360_POS_PERIOD	Character(8)	VARCHAR2(8) NOT NULL	Period