ITEM_ACT_SS_VW

(SQL View)
 Index Back

Item Activiy for Self Service

SELECT A.BUSINESS_UNIT, A.CUST_ID, A.ITEM, A.ITEM_STATUS, A.BAL_AMT, A.CURRENCY_CD, B.ENTRY_TYPE, (CONVERT(CHAR(10),B.ACCOUNTING_DT,121)), B.ENTRY_AMT, B.ENTRY_CURRENCY, B.PAYMENT_AMT, B.PAYMENT_CURRENCY FROM PS_ITEM A, PS_ITEM_ACTIVITY B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.CUST_ID = B.CUST_ID AND A.ITEM = B.ITEM AND A.ITEM_LINE = B.ITEM_LINE

SELECT DISTINCT A.BUSINESS_UNIT , %Substring (A.ITEM ,1 ,22) , A.CUST_ID , A.BUSINESS_UNIT_BI , MAX(A.ITEM_STATUS) , A.CURRENCY_CD , SUM(A.BAL_AMT) , A.BAL_CURRENCY , B.ENTRY_TYPE , MAX(B.ACCOUNTING_DT) , SUM(B.ENTRY_AMT) , B.ENTRY_CURRENCY FROM PS_ITEM A , PS_ITEM_ACTIVITY B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.CUST_ID = B.CUST_ID AND A.ITEM = B.ITEM AND A.ITEM_LINE = B.ITEM_LINE AND B.ENTRY_AMT <> 0 GROUP BY A.BUSINESS_UNIT, A.BUSINESS_UNIT_BI, A.CUST_ID, A.ITEM, A.CURRENCY_CD, A.BAL_CURRENCY,B.ENTRY_TYPE, B.ENTRY_CURRENCY

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT_AR Character(5) VARCHAR2(5) NOT NULL AR Business Unit

Prompt Table: SP_BUARED_NONVW

2 INVOICE Character(22) VARCHAR2(22) NOT NULL Invoice
3 BILL_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Customer
4 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
5 ITEM_STATUS Character(1) VARCHAR2(1) NOT NULL Item Status
C=Closed
O=Open
6 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
7 BAL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Item Balance
8 BAL_CURRENCY Character(3) VARCHAR2(3) NOT NULL Currency Code - Bal Amount
9 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.

Prompt Table: ENTRY_TYPE_TBL

10 ACCOUNTING_DT Date(10) DATE NOT NULL The accounting entry construction date for a given transaction (a generic field that crosses multiple
11 ENTRY_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Represents the monetary amount of a pending item transaction such as invoice or credit memo.
12 ENTRY_CURRENCY Character(3) VARCHAR2(3) NOT NULL Specifies the currency associated with the monetary amount of a pending item.