DBTIT_OPSMY_GVW

(SQL View)
 Index Back

Doubtful Item Summary with Opr

Doubtful Item Summary with Opr - Customer Group

SELECT I.OPRID , I.BUSINESS_UNIT , 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 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 (S.CUST_SELECT_FLG = 'A' OR (S.CUST_SELECT_FLG = 'G' AND ( (S.BLK_CUST_GRP = 'Y' AND NOT EXISTS ( SELECT 'X' FROM PS_CUST_CGRP_LNK G , PS_AR_BUSET_MVW E WHERE E.SETCNTRLVALUE = I.BUSINESS_UNIT AND E.RECNAME = 'CUSTOMER' AND E.SETID = G.SETID AND I.CUST_ID = G.CUST_ID ) ) OR EXISTS ( SELECT 'X' FROM PS_ARFO_PRFDBT_CUG CUST , PS_CUST_CGRP_LNK G , PS_AR_BUSET_MVW E WHERE E.SETCNTRLVALUE = I.BUSINESS_UNIT AND E.RECNAME = 'CUSTOMER' AND I.OPRID = CUST.OPRID AND E.SETID = G.SETID AND I.CUST_ID = G.CUST_ID AND G.SETID = CUST.SETID AND G.CUST_GRP_TYPE = CUST.CUST_GRP_TYPE AND G.CUSTOMER_GROUP = CUST.CUSTOMER_GROUP ) ) ) ) GROUP BY I.OPRID , I.BUSINESS_UNIT , 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 SETID Character(5) VARCHAR2(5) NOT NULL SetID
4 RESP_PART Character(15) VARCHAR2(15) NOT NULL Responsible Party
5 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
6 BAL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Item Balance
7 ITEM_CNT Number(15,0) DECIMAL(15) NOT NULL Item Count
8 DISPLAY_BAL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Display Balance
9 DISPLAY_CURRENCY Character(3) VARCHAR2(3) NOT NULL Display Currency