WITH REPS
       AS (SELECT   DISTINCT REP_SET.FILTER_TOKEN
             FROM   (SELECT          /*+ INDEX (wdsd WEBDATASETDTL_PK_TEAM) */
                           DISTINCT
                              WDSD.DATA_SETTING_ID, WDSD.FILTER_TOKEN
                       FROM   WEB_DATA_SETTING_DETAIL WDSD,
                              (SELECT   INST_QUERY_ID,
                                        NVL (Y.DATA_SETTING_ID,
                                             X.DATA_SETTING_ID)
                                           DATA_SETTING_ID
                                 FROM   WEB_INSTANTIABLE_QUERY Y,
                                        (SELECT   DATA_SETTING_ID
                                           FROM   WEB_DATA_SETTING
                                          WHERE   BROKER_ID = :B1
                                                  AND DEFAULT_YN = 'Y') X
                                WHERE   INST_QUERY_ID = :B2) WIQ
                      WHERE   WIQ.DATA_SETTING_ID = WDSD.DATA_SETTING_ID)
                    REP_SET),
    ENTIT AS (SELECT   WTXA.ACCOUNT_ID
                FROM   WEB_TEAM_X_ACCOUNT_BM WTXA, WEB_BROKER_X_TEAM WBXT
               WHERE   WBXT.BROKER_ID = :B1 AND WTXA.TEAM_ID = WBXT.TEAM_ID),
    WDT
       AS (SELECT   DATABASE_TYPE AS PROD_TYPE_ID, DESCR
             FROM   WEB_DATABASE_TYPE
            WHERE   DATABASE_TYPE IN
                          (SELECT   DATABASE_TYPE
                             FROM   WEB_DATABASE_TYPE_MAP
                            WHERE   DATABASE_TYPE_SET_ID IN
                                          (SELECT   WGS.VALUE
                                             FROM   WEB_GROUP_SETTING WGS
                                            WHERE   WGS.SETTING_ID = 654
                                                    AND WGS.GROUP_ID IN
                                                             (SELECT   GROUP_ID
                                                                FROM   WEB_GROUP
                                                               WHERE   INSTITUTION_ID =
                                                                          :B3))))
  SELECT   *
    FROM   (  SELECT   WDT.DESCR AS PROD_TYPE,
                       WDT.PROD_TYPE_ID,
                       COUNT (DISTINCT RSSUM.SEC_NO) AS NO_SEC,
                       SUM (RSSUM.MARKET_VALUE) TOTAL_ASSETS,
                       SUM (
                          NVL (RSSUM.MTD_COMMISSION, 0) + NVL (RSSUM.MTD_FEE, 0)
                       )
                          MTD_COMM_FEE,
                       SUM (
                          NVL (RSSUM.YTD_COMMISSION, 0) + NVL (RSSUM.YTD_FEE, 0)
                       )
                          YTD_COMM_FEE
                FROM   (SELECT    /*+ index (sps SEC_POS_SUMMARY_I_ACT_ID ) */
                              SPS.SEC_ID
                                    SEC_NO,
                                 SPS.MARKET_VALUE,
                                 SPS.MTD_COMMISSION,
                                 SPS.YTD_COMMISSION,
                                 SPS.MTD_FEE,
                                 SPS.YTD_FEE,
                                 SPS.PROD_TYPE_ID PROD_TYPE_ID
                          FROM   SECURITY_POSITION_SUMMARY SPS
                         WHERE   SPS.ACT_ID IN
                                       (SELECT   SM.ACCOUNT_ID
                                          FROM   REPS, WEB_ACCOUNT_QRY_FILTER SM
                                         WHERE   REPS.FILTER_TOKEN =
                                                    SM.FILTER_TOKEN)
                                 AND EXISTS (SELECT   '1'
                                               FROM   ENTIT
                                              WHERE   ACCOUNT_ID = SPS.ACT_ID))
                       RSSUM,
                       WDT
               WHERE   RSSUM.PROD_TYPE_ID(+) = WDT.PROD_TYPE_ID
            GROUP BY   WDT.DESCR, WDT.PROD_TYPE_ID)
ORDER BY   PROD_TYPE ASC