DBTIT_OPSMY_CVW

(SQL View)
 Index Back

Doubtful Item Summary with Opr

Doubtful Item Summary with Opr

SELECT I.OPRID , I.BUSINESS_UNIT , E.SETID , I.CUST_ID , I.SETID , I.RESP_PART , I.TRANS_CURRENCY , SUM(I.BAL_AMT) , SUM(I.ITEM_CNT) , SUM(I.DISPLAY_BAL_AMT) , I.DISPLAY_CURRENCY FROM PS_DBTIT_OPBAL_VW I , PS_ARFO_PRF_DBTIT S , PS_AR_BUSET_MVW E WHERE S.OPRID = I.OPRID AND ( S.BU_SELECT_FLG = 'A' OR EXISTS( SELECT 'X' FROM PS_ARFO_PRF_DBT_BU BU WHERE BU.OPRID = I.OPRID AND BU.BUSINESS_UNIT = I.BUSINESS_UNIT) ) AND ( S.RESP_ALL_FLG = 'A' OR EXISTS( SELECT 'X' FROM PS_ARFO_PRFDBT_RSP R WHERE R.OPRID = I.OPRID AND R.RESP_PART = I.RESP_PART) ) AND E.SETCNTRLVALUE = I.BUSINESS_UNIT AND E.RECNAME = 'CUSTOMER' AND (S.CUST_SELECT_FLG = 'A' OR (S.CUST_SELECT_FLG = 'S' AND EXISTS ( SELECT CUST.CUST_ID FROM PS_ARFO_PRFDBT_CUS CUST WHERE CUST.OPRID = I.OPRID AND CUST.CUSTOMER_SETID = E.SETID AND CUST.CUST_ID = I.CUST_ID ))) GROUP BY I.OPRID , I.BUSINESS_UNIT , E.SETID, I.CUST_ID , I.SETID , I.RESP_PART , I.TRANS_CURRENCY , I.DISPLAY_CURRENCY

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
2 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: SP_BUARDS_NONVW

3 CUSTOMER_SETID Character(5) VARCHAR2(5) NOT NULL Customer SetID
4 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.
5 SETID Character(5) VARCHAR2(5) NOT NULL SetID
6 RESP_PART Character(15) VARCHAR2(15) NOT NULL Responsible Party
7 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
8 BAL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Item Balance
9 ITEM_CNT Number(15,0) DECIMAL(15) NOT NULL Item Count
10 DISPLAY_BAL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Display Balance
11 DISPLAY_CURRENCY Character(3) VARCHAR2(3) NOT NULL Display Currency