insert into WK_ACCT_WSTORE
(ACCT_TDLINX, RELATED_TDLINX, ACCT_TYPE_CD, LVL,
 MKT_GRP_TDLINX, TRADE_CHANNEL_CD, SUB_CHANNEL_CD,
 MKT_GRP_COUNT, TRADE_CHANNEL_COUNT, SUB_CHANNEL_COUNT)
select acct_tdlinx, related_tdlinx, acct_type_cd, lvl,
mkt_grp_tdlinx, trade_channel_cd, sub_channel_cd,
mkt_grp_count, trade_channel_count, sub_channel_count
from
(
    select connect_by_root acct_tdlinx_cd as acct_tdlinx, irt_tdlinx_cd as related_tdlinx,
           connect_by_isleaf isleaf, (LEVEL) as lvl
    from dlvry_acct_dim mad
    where mad.orig_status_cd in ('FO', 'OP')
        and mad.end_dt = '31 dec 2500'
    start with mad.orig_status_cd in ('FO', 'OP')
        and mad.end_dt = '31 dec 2500'
        and mad.acct_tdlinx_cd <> mad.irt_tdlinx_cd
    connect by NOCYCLE prior mad.irt_tdlinx_cd = mad.acct_tdlinx_cd
    union all
    select acct_tdlinx_cd, acct_tdlinx_cd, 0, 0
    from dlvry_acct_dim mad
    where mad.orig_status_cd in ('FO', 'OP')
        and mad.end_dt = '31 dec 2500'
    union all
    select connect_by_root irt_tdlinx_cd, acct_tdlinx_cd,
           connect_by_isleaf isleaf, (0 - LEVEL) as lvl
    from dlvry_acct_dim mad
    where mad.orig_status_cd in ('FO', 'OP')
        and mad.end_dt = '31 dec 2500'
    start with mad.orig_status_cd in ('FO', 'OP')
        and mad.end_dt = '31 dec 2500'
        and mad.acct_tdlinx_cd <> mad.irt_tdlinx_cd
    connect by NOCYCLE prior mad.acct_tdlinx_cd = mad.irt_tdlinx_cd
) hier,
(
    select distinct acct_tdlinx_cd, acct_type_cd,
                    mkt_grp_tdlinx, trade_channel_cd, sub_channel_cd,
    count(store_tdlinx_cd) over
         (partition by acct_tdlinx_cd, mkt_grp_tdlinx) mkt_grp_count,
    count(store_tdlinx_cd) over
         (partition by acct_tdlinx_cd, trade_channel_cd) trade_channel_count,
    count(store_tdlinx_cd) over
         (partition by acct_tdlinx_cd, trade_channel_cd, sub_channel_cd) sub_channel_count
    from
    (
        select mad.acct_tdlinx_cd, mad.acct_type_cd, msd.store_tdlinx_cd,
               msd.marketing_grp_tdlinx_cd as mkt_grp_tdlinx,
               msd.trade_channel_cd, msd.SUB_CHANNEL_CD
        from dlvry_acct_dim mad, mstr_store_dim msd
        where mad.end_dt = '31 dec 2500'
        and msd.end_dt = '31 dec 2500'
        and msd.orig_status_cd in ('OP','FO')
        and mad.acct_type_cd in ('B', 'O')
        and mad.acct_tdlinx_cd = msd.owner_tdlinx_cd
        and mad.acct_trade_channel_cd = msd.trade_channel_cd
        union
        select mad.acct_tdlinx_cd, mad.acct_type_cd, msd.store_tdlinx_cd,
               msd.marketing_grp_tdlinx_cd, msd.trade_channel_cd, msd.SUB_CHANNEL_CD
        from dlvry_acct_dim mad, mstr_store_dim msd
        where mad.end_dt = '31 dec 2500'
        and msd.end_dt = '31 dec 2500'
        and msd.orig_status_cd in ('OP','FO')
        and mad.acct_type_cd = 'S'
        and mad.acct_tdlinx_cd = msd.pri_sup_tdlinx_cd
        and (mad.acct_trade_channel_cd = msd.trade_channel_cd or
             mad.acct_trade_channel_cd = '12')
        union
        select mad.acct_tdlinx_cd, mad.acct_type_cd, msd.store_tdlinx_cd,
               msd.marketing_grp_tdlinx_cd, msd.trade_channel_cd, msd.SUB_CHANNEL_CD
        from dlvry_acct_dim mad, mstr_store_dim msd
        where mad.end_dt = '31 dec 2500'
        and msd.end_dt = '31 dec 2500'
        and msd.orig_status_cd in ('OP','FO')
        and mad.acct_type_cd = 'S'
        and mad.acct_tdlinx_cd = grocery_acct_tdlinx_cd
        and (mad.acct_trade_channel_cd = msd.trade_channel_cd or
             mad.acct_trade_channel_cd = '12')
        union
        select mad.acct_tdlinx_cd, mad.acct_type_cd, msd.store_tdlinx_cd,
               msd.marketing_grp_tdlinx_cd, msd.trade_channel_cd, msd.SUB_CHANNEL_CD
        from dlvry_acct_dim mad, mstr_store_dim msd
        where mad.end_dt = '31 dec 2500'
        and msd.end_dt = '31 dec 2500'
        and msd.orig_status_cd in ('OP','FO')
        and mad.acct_type_cd = 'S'
        and mad.acct_tdlinx_cd = hbc_acct_tdlinx_cd
        and (mad.acct_trade_channel_cd = msd.trade_channel_cd or
             mad.acct_trade_channel_cd = '12')
        union
        select mad.acct_tdlinx_cd, mad.acct_type_cd, msd.store_tdlinx_cd,
               msd.marketing_grp_tdlinx_cd, msd.trade_channel_cd, msd.SUB_CHANNEL_CD
        from dlvry_acct_dim mad, mstr_store_dim msd
        where mad.end_dt = '31 dec 2500'
        and msd.end_dt = '31 dec 2500'
        and msd.orig_status_cd in ('OP','FO')
        and mad.acct_type_cd = 'S'
        and mad.acct_tdlinx_cd = gm_acct_tdlinx_cd
        and (mad.acct_trade_channel_cd = msd.trade_channel_cd or
             mad.acct_trade_channel_cd = '12')
        union
        select mad.acct_tdlinx_cd, mad.acct_type_cd, msd.store_tdlinx_cd,
               msd.marketing_grp_tdlinx_cd, msd.trade_channel_cd, msd.SUB_CHANNEL_CD
        from dlvry_acct_dim mad, mstr_store_dim msd
        where mad.end_dt = '31 dec 2500'
        and msd.end_dt = '31 dec 2500'
        and msd.orig_status_cd in ('OP','FO')
        and mad.acct_type_cd = 'S'
        and mad.acct_tdlinx_cd = fr_acct_tdlinx_cd
        and (mad.acct_trade_channel_cd = msd.trade_channel_cd or
             mad.acct_trade_channel_cd = '12')
        union
        select mad.acct_tdlinx_cd, mad.acct_type_cd, msd.store_tdlinx_cd,
               msd.marketing_grp_tdlinx_cd, msd.trade_channel_cd, msd.SUB_CHANNEL_CD
        from dlvry_acct_dim mad, mstr_store_dim msd
        where mad.end_dt = '31 dec 2500'
        and msd.end_dt = '31 dec 2500'
        and msd.orig_status_cd in ('OP','FO')
        and mad.acct_type_cd = 'S'
        and mad.acct_tdlinx_cd = conf_acct_tdlinx_cd
        and (mad.acct_trade_channel_cd = msd.trade_channel_cd or
             mad.acct_trade_channel_cd = '12')
) ) cnt
where hier.related_tdlinx = cnt.acct_tdlinx_cd
order by acct_tdlinx, lvl desc, related_tdlinx ;