select t.cate_1_id,
       max(t.supplier_count),
       max(t.supplier_t_count),
       max(t.supplier_a_count),
       max(t_rate),
       max(a_rate),
       max(t.vcode_count),
       max(vcode_rate),
       max(t.supplier_l_count),
       max(l_rate),
       max(t.supplier_b_count),
       max(b_rate),
       max(t.examiner),
       (select asset.name
          from assetcategory asset
         where asset.categoryid = t.cate_1_id),
       max(t.category_name) as category_name,
       max(t.parentcategoryid) as parentCode
  from (with supplier_cnt as 
  (select count(cate_1_id) as cnt, cate_1_id
                                from (select distinct decode(get_latest_parent_bysubid(sp.prodtype_id),
                                                             null,
                                                             sp.prodtype_id,
                                                             get_latest_parent_bysubid(sp.prodtype_id)) cate_1_id,
                                                      sp.supplier_id
                                        from t_rsp_supplier_basis sb
                                        left join t_rsp_supplier_prodtype sp
                                          on sb.supplier_id = sp.supplier_id
                                        left join user_ u
                                          on sb.user_id = u.userid
                                       where 1 = 1) t
                               group by t.cate_1_id), supplier_status_cnt as (select count(cate_1_id) as cnt,
                                                                                     cate_1_id as prodtype_id,
                                                                                     manage_status
                                                                                from (select distinct decode(get_latest_parent_bysubid(sp.prodtype_id),
                                                                                                             null,
                                                                                                             sp.prodtype_id,
                                                                                                             get_latest_parent_bysubid(sp.prodtype_id)) cate_1_id,
                                                                                                      sp.supplier_id,
                                                                                                      sb.manage_status
                                                                                        from t_rsp_supplier_basis sb
                                                                                        left join t_rsp_supplier_prodtype sp
                                                                                          on sb.supplier_id =
                                                                                             sp.supplier_id
                                                                                        left join user_ u
                                                                                          on sb.user_id =
                                                                                             u.userid
                                                                                       where 1 = 1) t
                                                                               group by t.cate_1_id,
                                                                                        t.manage_status), vcode_cnt as (select count(cate_1_id) as cnt,
                                                                                                                               cate_1_id as prodtype_id
                                                                                                                          from (select distinct decode(get_latest_parent_bysubid(sp.prodtype_id),
                                                                                                                                                       null,
                                                                                                                                                       sp.prodtype_id,
                                                                                                                                                       get_latest_parent_bysubid(sp.prodtype_id)) cate_1_id,
                                                                                                                                                sp.supplier_id
                                                                                                                                  from t_rsp_supplier_basis sb
                                                                                                                                  left join t_rsp_supplier_prodtype sp
                                                                                                                                    on sb.supplier_id =
                                                                                                                                       sp.supplier_id
                                                                                                                                  left join user_ u
                                                                                                                                    on sb.user_id =
                                                                                                                                       u.userid
                                                                                                                                 where 1 = 1
                                                                                                                                   and sb.vcode is not null
                                                                                                                                   and sp.prodtype_id is not null) t
                                                                                                                         group by t.cate_1_id)
         select decode(get_latest_parent_bysubid(ac.categoryid),
                       null,
                       ac.categoryid,
                       get_latest_parent_bysubid(ac.categoryid)) as category_2nd_code,
                max(sp_cnt.cnt) as supplier_count,
                max(sps_cnt_t.cnt) as supplier_t_count,
                max(sps_cnt_a.cnt) as supplier_a_count,
                decode(max(sp_cnt.cnt),
                       0,
                       '',
                       round((max(sps_cnt_t.cnt) / max(sp_cnt.cnt)) * 100, 2) || '%') as t_rate,
                decode(max(sp_cnt.cnt),
                       0,
                       '',
                       (round((max(sps_cnt_a.cnt) / max(sp_cnt.cnt)) * 100, 2)) || '%') as a_rate,
                max(v_cnt.cnt) as vcode_count,
                decode(max(sp_cnt.cnt),
                       0,
                       '',
                       (round((max(v_cnt.cnt) / max(sp_cnt.cnt)) * 100, 2)) || '%') as vcode_rate,
                max(sps_cnt_l.cnt) as supplier_l_count,
                decode(max(sp_cnt.cnt),
                       0,
                       '',
                       round((max(sps_cnt_l.cnt) / max(sp_cnt.cnt)) * 100, 2) || '%') as l_rate,
                max(sps_cnt_b.cnt) as supplier_b_count,
                decode(max(sp_cnt.cnt),
                       0,
                       '',
                       round((max(sps_cnt_b.cnt) / max(sp_cnt.cnt)) * 100, 2) || '%') as b_rate,
                get_examiner_by_prodtype(ac.categoryid, 11419) as examiner,
                max(ac.name),
                getassetcategory2(ac.categoryid) as category_name,
                ac.categoryid,
                max(ac.parentcategoryid) as parentcategoryid,
                sp_cnt.cate_1_id
           from ASSETCATEGORY ac
           left join T_RSP_SUPPLIER_PRODTYPE rsp
             on ac.categoryid = rsp.prodtype_id
           left join T_RSP_SUPPLIER_BASIS sb
             on rsp.supplier_id = sb.supplier_id
           left join user_ u
             on sb.user_id = u.userid
           left join supplier_cnt sp_cnt
             on sp_cnt.cate_1_id =
                decode(get_latest_parent_bysubid(ac.categoryid),
                       null,
                       ac.categoryid,
                       get_latest_parent_bysubid(ac.categoryid))
           left join supplier_status_cnt sps_cnt_t
             on (sps_cnt_t.prodtype_id =
                decode(get_latest_parent_bysubid(ac.categoryid),
                        null,
                        ac.categoryid,
                        get_latest_parent_bysubid(ac.categoryid)) and
                sps_cnt_t.manage_status = 'T')
           left join supplier_status_cnt sps_cnt_a
             on (sps_cnt_a.prodtype_id =
                decode(get_latest_parent_bysubid(ac.categoryid),
                        null,
                        ac.categoryid,
                        get_latest_parent_bysubid(ac.categoryid)) and
                sps_cnt_a.manage_status = 'A')
           left join supplier_status_cnt sps_cnt_l
             on (sps_cnt_l.prodtype_id =
                decode(get_latest_parent_bysubid(ac.categoryid),
                        null,
                        ac.categoryid,
                        get_latest_parent_bysubid(ac.categoryid)) and
                sps_cnt_l.manage_status = 'L')
           left join supplier_status_cnt sps_cnt_b
             on (sps_cnt_b.prodtype_id =
                decode(get_latest_parent_bysubid(ac.categoryid),
                        null,
                        ac.categoryid,
                        get_latest_parent_bysubid(ac.categoryid)) and
                sps_cnt_b.manage_status = 'B')
           left join vcode_cnt v_cnt
             on v_cnt.prodtype_id =
                decode(get_latest_parent_bysubid(ac.categoryid),
                       null,
                       ac.categoryid,
                       get_latest_parent_bysubid(ac.categoryid))
          where ac.categoryid not in
                (select a.parentcategoryid from ASSETCATEGORY a)
          group by sp_cnt.cate_1_id,
                   ac.categoryid) t
          group by t.cate_1_id