ITEM_ACT_CSS_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 , A.ITEM , A.CUST_ID , C.OPRID , D.SETID , A.BUSINESS_UNIT_BI , MAX(A.ITEM_STATUS) , A.CURRENCY_CD , SUM(A.BAL_AMT) , B.ENTRY_TYPE , MAX(B.ACCOUNTING_DT) , SUM(B.ENTRY_AMT) , B.ENTRY_CURRENCY FROM PS_ITEM A , PS_ITEM_ACTIVITY B , PS_BILL_TO_SEC_VW C , PS_SET_CNTRL_REC D , PS_BI_HDR H 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 AND A.CONSOL_BUS_UNIT = H.BUSINESS_UNIT AND A.CONSOL_INVOICE = H.INVOICE AND H.BILL_TO_CUST_ID = C.CUST_ID AND D.RECNAME = 'CUSTOMER' AND C.CUSTOMER_SETID = D.SETID AND SETCNTRLVALUE = H.BUSINESS_UNIT GROUP BY C.OPRID, D.SETID, A.BUSINESS_UNIT, A.BUSINESS_UNIT_BI, A.CUST_ID, A.ITEM, A.CURRENCY_CD, 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 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
5 SETID Character(5) VARCHAR2(5) NOT NULL SetID
6 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
7 ITEM_STATUS Character(1) VARCHAR2(1) NOT NULL Item Status
C=Closed
O=Open
8 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
9 BAL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Item Balance
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.

Prompt Table: ENTRY_TYPE_TBL

11 ACCOUNTING_DT Date(10) DATE NOT NULL The accounting entry construction date for a given transaction (a generic field that crosses multiple
12 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.
13 ENTRY_CURRENCY Character(3) VARCHAR2(3) NOT NULL Specifies the currency associated with the monetary amount of a pending item.