style24.doc/산출물/4.구축/데이터처리/10.카테고리필터생성(최종)_배치적용해야함.sql

TRUNCATE TABLE TB_CATE_FILTER_TMP;

-- 브랜드별 / 카테고리번호 0 / 정상이월 구분 o 
-- 사이즈 필터 생성
INSERT INTO TB_CATE_FILTER_TMP (
       SITE_CD
     , CATE_GB
     , FORMAL_GB
     , BRAND_GROUP_NO
     , CATE_NO
     , FILTER_GB
     , FILTER_CD
     , FILTER_NM
     , DISP_ORD
)
WITH TAB_FILTER AS (
    SELECT C4.SITE_CD
         , C4.CATE_GB
         , G.FORMAL_GB
         , B.BRAND_GROUP_NO
         , C4.CATE1_NO
         , C4.CATE2_NO
         , C4.CATE3_NO
         , C4.CATE4_NO
         , C4.CATE5_NO
         , CONCAT(I.SIZE_GB,'|',S.OPT_CD2) AS FILTER_CD
         , S.OPT_CD2                       AS FILTER_NM
    FROM   TB_CATE_4SRCH C4
         , TB_CATE_GOODS CG
         , TB_GOODS G
         , TB_ITEMKIND I
         , VW_STOCK S
         , TB_BRAND B
         , TB_SITE_BRAND SB
    WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
--     AND    CG.BRAND_GROUP_NO = 0
--     and b.BRAND_GROUP_NO = 24555
    AND    CG.GOODS_CD = G.GOODS_CD
    AND    G.ITEMKIND_CD = I.ITEMKIND_CD
    AND    G.GOODS_CD = S.GOODS_CD
    AND    G.BRAND_CD = B.BRAND_CD
    AND    B.BRAND_CD = SB.BRAND_CD
    AND    C4.SITE_CD = 'G000_10'
    AND    C4.CATE_GB = 'G032_101'
    AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
    AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
    AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
    AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*판매기간*/
    AND    I.SIZE_GB IS NOT NULL
    AND    S.CURR_STOCK_QTY - S.BASE_STOCK_QTY > 0 /*재고있는옵션*/
    AND    S.SOLDOUT_YN = 'N' /*품절이 아닌 옵션*/
    AND    S.DISP_YN = 'Y' /*노출하는 옵션*/
    AND    LENGTH(S.OPT_CD2) > 0
    AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
    AND    SB.SITE_CD = 'G000_10'
    AND    SB.USE_YN = 'Y' /*사이트에서 사용하는 브랜드*/
)
SELECT distinct SITE_CD
     , CATE_GB
     , FORMAL_GB
     , BRAND_GROUP_NO
     , CATE_NO
     , 'SIZE'        AS FILTER_GB
     , FILTER_CD
     , FILTER_NM
     , RANK() OVER(PARTITION BY SITE_CD, CATE_GB, FORMAL_GB, BRAND_GROUP_NO, CATE_NO
                   ORDER BY CASE WHEN FILTER_CD LIKE 'T%' THEN 1
                                 WHEN FILTER_CD LIKE 'B%' THEN 2
                                 WHEN FILTER_CD LIKE 'S%' THEN 3
                                 ELSE 4
                            END, FILTER_NM
                  )   AS DISP_ORD
FROM   (
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE_NO
             , FILTER_CD
             , FILTER_NM
        FROM   (
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , 0       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE5_NO IS NOT NULL
                AND FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , 0       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE4_NO IS NOT NULL
                AND FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , 0       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE3_NO IS NOT NULL
                AND FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , 0       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE2_NO IS NOT NULL
                AND FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , 0       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE1_NO IS NOT NULL
                AND FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , 0       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE5_NO IS NOT NULL
                AND FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , 0       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE4_NO IS NOT NULL
                AND FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , 0       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE3_NO IS NOT NULL
                AND FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , 0       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE2_NO IS NOT NULL
                AND FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , 0       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE1_NO IS NOT NULL
                AND FORMAL_GB = 'G009_20'
               ) Z
        WHERE  CATE_NO IS NOT NULL
    ) Z
    ;

-- 가격 필터 생성(브랜드별 전체)
INSERT INTO TB_CATE_FILTER_TMP (
       SITE_CD
     , CATE_GB
     , FORMAL_GB
     , BRAND_GROUP_NO
     , CATE_NO
     , FILTER_GB
     , FILTER_CD
     , FILTER_NM
     , DISP_ORD
)
WITH TAB_FILTER AS (
    SELECT C4.SITE_CD
         , C4.CATE_GB
         , G.FORMAL_GB
         , B.BRAND_GROUP_NO
         , C4.CATE1_NO
         , C4.CATE2_NO
         , C4.CATE3_NO
         , C4.CATE4_NO
         , C4.CATE5_NO
         , FN_GET_BENEFIT_PRICE('P',G.GOODS_CD,G.CURR_PRICE,'G100_10') AS CURR_PRICE
    FROM   TB_CATE_4SRCH C4
         , TB_CATE_GOODS CG
         , TB_GOODS G
         , TB_GOODS_STOCK GS
         , TB_BRAND B
         , TB_SITE_BRAND SB
    WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
--     and b.brand_group_no = 19706
    AND    CG.GOODS_CD = G.GOODS_CD
    AND    CG.GOODS_CD = GS.GOODS_CD
    AND    G.BRAND_CD = B.BRAND_CD
    AND    B.BRAND_CD = SB.BRAND_CD
    AND    C4.SITE_CD = 'G000_10'
    AND    C4.CATE_GB = 'G032_101'
    AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
    AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
    AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
    AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*판매기간*/
    AND    GS.STOCK_QTY > 0 /*재고있는옵션*/
    AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
    AND    SB.SITE_CD = 'G000_10'
    AND    SB.USE_YN = 'Y' /*사이트에서 사용하는 브랜드*/
)
, TAB_PRICE AS (
    SELECT SITE_CD
         , CATE_GB
         , 'G009_10' AS  FORMAL_GB
         , BRAND_GROUP_NO
         , CATE_NO
         , FLOOR(MIN_CURR_PRICE / 1000) * 1000                   AS PRICE1
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 1) / 1000) * 1000 AS PRICE2
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 2) / 1000) * 1000 AS PRICE3
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 3) / 1000) * 1000 AS PRICE4
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 4) / 1000) * 1000 AS PRICE5
         , FLOOR(MAX_CURR_PRICE / 1000) * 1000                   AS PRICE6
    FROM   (
            SELECT SITE_CD
                 , CATE_GB
                 , BRAND_GROUP_NO
                 , 0        AS CATE_NO
                 , MIN(CURR_PRICE) AS MIN_CURR_PRICE                    /*최소현재판매가*/
                 , MAX(CURR_PRICE) AS MAX_CURR_PRICE                    /*최대현재판매가*/
                 , (MAX(CURR_PRICE) - MIN(CURR_PRICE)) / 5 AS SLICE_VAL /*분할값*/
            FROM   TAB_FILTER
            WHERE  FORMAL_GB IN ('G009_10','G009_20')
            GROUP  BY SITE_CD, CATE_GB, BRAND_GROUP_NO
           ) Z
	UNION ALL 
	SELECT SITE_CD
         , CATE_GB
         , 'G009_20' AS  FORMAL_GB
         , BRAND_GROUP_NO
         , CATE_NO
         , FLOOR(MIN_CURR_PRICE / 1000) * 1000                   AS PRICE1
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 1) / 1000) * 1000 AS PRICE2
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 2) / 1000) * 1000 AS PRICE3
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 3) / 1000) * 1000 AS PRICE4
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 4) / 1000) * 1000 AS PRICE5
         , FLOOR(MAX_CURR_PRICE / 1000) * 1000                   AS PRICE6
    FROM   (
            SELECT SITE_CD
                 , CATE_GB
                 , BRAND_GROUP_NO
                 , 0        AS CATE_NO
                 , MIN(CURR_PRICE) AS MIN_CURR_PRICE                    /*최소현재판매가*/
                 , MAX(CURR_PRICE) AS MAX_CURR_PRICE                    /*최대현재판매가*/
                 , (MAX(CURR_PRICE) - MIN(CURR_PRICE)) / 5 AS SLICE_VAL /*분할값*/
            FROM   TAB_FILTER
            WHERE  FORMAL_GB IN ('G009_20')
            GROUP  BY SITE_CD, CATE_GB, BRAND_GROUP_NO
           ) Z
)
SELECT DISTINCT
       SITE_CD
     , CATE_GB
     , FORMAL_GB
     , BRAND_GROUP_NO
     , CATE_NO
     , 'PRICE'        AS FILTER_GB
     , FILTER_CD
     , FILTER_NM
     , RANK() OVER(PARTITION BY SITE_CD, CATE_GB, FORMAL_GB, BRAND_GROUP_NO, CATE_NO
                   ORDER BY FILTER_CD
                   )  AS DISP_ORD
FROM   (
        SELECT SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE_NO
             , PRICE1         AS FILTER_CD
             , PRICE1         AS FILTER_NM
        FROM   TAB_PRICE
        WHERE PRICE1 > 0
        UNION ALL
        SELECT SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE_NO
             , PRICE2         AS FILTER_CD
             , PRICE2         AS FILTER_NM
        FROM   TAB_PRICE
        WHERE PRICE2 > 0
        UNION ALL
        SELECT SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE_NO
             , PRICE3         AS FILTER_CD
             , PRICE3         AS FILTER_NM
        FROM   TAB_PRICE
        WHERE PRICE3 > 0
        UNION ALL
        SELECT SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE_NO
             , PRICE4         AS FILTER_CD
             , PRICE4         AS FILTER_NM
        FROM   TAB_PRICE
        WHERE PRICE4 > 0
        UNION ALL
        SELECT SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE_NO
             , PRICE5         AS FILTER_CD
             , PRICE5         AS FILTER_NM
        FROM   TAB_PRICE
        WHERE PRICE5 > 0
        UNION ALL
        SELECT SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE_NO
             , PRICE6         AS FILTER_CD
             , PRICE6         AS FILTER_NM
        FROM   TAB_PRICE
        WHERE PRICE6 > 0
       ) Z
;


-- 연령대 필터 생성
INSERT INTO TB_CATE_FILTER_TMP (
       SITE_CD
     , CATE_GB
     , FORMAL_GB
     , BRAND_GROUP_NO
     , CATE_NO
     , FILTER_GB
     , FILTER_CD
     , FILTER_NM
     , DISP_ORD
)
WITH TAB_FILTER AS (
    SELECT C4.SITE_CD
         , C4.CATE_GB
         , G.FORMAL_GB
         , B.BRAND_GROUP_NO
         , C4.CATE1_NO
         , C4.CATE2_NO
         , C4.CATE3_NO
         , C4.CATE4_NO
         , C4.CATE5_NO
         , G.AGE_GRP_CD      AS FILTER_CD
    FROM   TB_CATE_4SRCH C4
         , TB_CATE_GOODS CG
         , TB_GOODS G
         , TB_GOODS_STOCK GS
         , TB_BRAND B
         , TB_SITE_BRAND SB
    WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
--     AND    CG.BRAND_GROUP_NO = 0
--     and B.BRAND_GROUP_NO = 19706
    AND    CG.GOODS_CD = G.GOODS_CD
    AND    CG.GOODS_CD = GS.GOODS_CD
    AND    G.BRAND_CD = B.BRAND_CD
    AND    B.BRAND_CD = SB.BRAND_CD
    AND    C4.SITE_CD = 'G000_10'
    AND    C4.CATE_GB = 'G032_101'
    AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
    AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
    AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
    AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*판매기간*/
    AND    G.AGE_GRP_CD IS NOT NULL
    AND    GS.STOCK_QTY > 0 /*재고있는상품*/
    AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
    AND    SB.SITE_CD = 'G000_10'
    AND    SB.USE_YN = 'Y' /*사이트에서 사용하는 브랜드*/
)
SELECT A.SITE_CD
     , A.CATE_GB
     , A.FORMAL_GB
     , A.BRAND_GROUP_NO
     , A.CATE_NO
     , 'AGE'          AS FILTER_GB
     , A.FILTER_CD
     , CC.CD_NM       AS FILTER_NM
     , RANK() OVER(PARTITION BY A.SITE_CD, A.CATE_GB, A.FORMAL_GB, A.BRAND_GROUP_NO, A.CATE_NO
                   ORDER BY CC.DISP_ORD
                  )   AS DISP_ORD
FROM   (
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , 0 AS CATE_NO
             , FILTER_CD
        FROM   (
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE5_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE5_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE4_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE4_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE3_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE3_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE2_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE2_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE1_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE1_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL 
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE5_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE5_NO IS NOT NULL
                AND  FORMAL_GB  = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE4_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE4_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE3_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE3_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE2_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE2_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE1_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE1_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
               ) Z
        WHERE  CATE_NO IS NOT NULL
       ) A
     , TB_COMMON_CODE CC
WHERE  A.FILTER_CD = CC.CD
AND    CC.CD_GB = 'G023'
AND    CC.USE_YN = 'Y'
;

-- 시즌 필터 생성
INSERT INTO TB_CATE_FILTER_TMP (
       SITE_CD
     , CATE_GB
     , FORMAL_GB
     , BRAND_GROUP_NO
     , CATE_NO
     , FILTER_GB
     , FILTER_CD
     , FILTER_NM
     , DISP_ORD
)
WITH TAB_FILTER AS (
    SELECT C4.SITE_CD
         , C4.CATE_GB
         , G.FORMAL_GB
         , B.BRAND_GROUP_NO
         , C4.CATE1_NO
         , C4.CATE2_NO
         , C4.CATE3_NO
         , C4.CATE4_NO
         , C4.CATE5_NO
         , G.SEASON_CD       AS FILTER_CD
    FROM   TB_CATE_4SRCH C4
         , TB_CATE_GOODS CG
         , TB_GOODS G
         , TB_GOODS_STOCK GS
         , TB_BRAND B
         , TB_SITE_BRAND SB
    WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
--     AND    CG.BRAND_GROUP_NO = 0
--     and B.BRAND_GROUP_NO = 19706
    AND    CG.GOODS_CD = G.GOODS_CD
    AND    CG.GOODS_CD = GS.GOODS_CD
    AND    G.BRAND_CD = B.BRAND_CD
    AND    B.BRAND_CD = SB.BRAND_CD
    AND    C4.SITE_CD = 'G000_10'
    AND    C4.CATE_GB = 'G032_101'
    AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
    AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
    AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
    AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*판매기간*/
    AND    GS.STOCK_QTY > 0 /*재고있는상품*/
    AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
    AND    SB.SITE_CD = 'G000_10'
    AND    SB.USE_YN = 'Y' /*사이트에서 사용하는 브랜드*/
)
SELECT A.SITE_CD
     , A.CATE_GB
     , A.FORMAL_GB
     , A.BRAND_GROUP_NO
     , A.CATE_NO
     , 'SEASON'         AS FILTER_GB
     , A.FILTER_CD
     , CC.CD_NM         AS FILTER_NM
     , RANK() OVER(PARTITION BY A.SITE_CD, A.CATE_GB, A.FORMAL_GB, A.BRAND_GROUP_NO, A.CATE_NO
                   ORDER BY CC.DISP_ORD
                  )   AS DISP_ORD
FROM   (
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , 0 AS CATE_NO
             , FILTER_CD
        FROM   (
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE5_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE5_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE4_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE4_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE3_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE3_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE2_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE2_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE1_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE1_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL 
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE5_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE5_NO IS NOT NULL
                AND  FORMAL_GB  = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE4_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE4_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE3_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE3_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE2_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE2_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE1_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE1_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
               ) Z
        WHERE  CATE_NO IS NOT NULL
       ) A
     , TB_COMMON_CODE CC
WHERE  A.FILTER_CD = CC.CD
AND    CC.CD_GB = 'G006'
AND    CC.USE_YN = 'Y'
;

-- 색상 필터 생성
INSERT INTO TB_CATE_FILTER_TMP (
       SITE_CD
     , CATE_GB
     , FORMAL_GB
     , BRAND_GROUP_NO
     , CATE_NO
     , FILTER_GB
     , FILTER_CD
     , FILTER_NM
     , DISP_ORD
)
WITH TAB_FILTER AS (
    SELECT C4.SITE_CD
         , C4.CATE_GB
         , G.FORMAL_GB
         , B.BRAND_GROUP_NO
         , C4.CATE1_NO
         , C4.CATE2_NO
         , C4.CATE3_NO
         , C4.CATE4_NO
         , C4.CATE5_NO
         , C.COLOR_GRP_CD    AS FILTER_CD
    FROM   TB_CATE_4SRCH C4
         , TB_CATE_GOODS CG
         , TB_GOODS G
         , TB_GOODS_STOCK GS
         , TB_OPTION O
         , TB_COLOR C
         , TB_BRAND B
         , TB_SITE_BRAND SB
    WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
--     AND    CG.BRAND_GROUP_NO = 0
--     and B.BRAND_GROUP_NO = 19706
    AND    CG.GOODS_CD = G.GOODS_CD
    AND    CG.GOODS_CD = GS.GOODS_CD
    AND    CG.GOODS_CD = O.GOODS_CD
    AND    O.OPT_CD1 = C.COLOR_CD
    AND    G.BRAND_CD = B.BRAND_CD
    AND    B.BRAND_CD = SB.BRAND_CD
    AND    C4.SITE_CD = 'G000_10'
    AND    C4.CATE_GB = 'G032_101'
    AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
    AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
    AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
    AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*판매기간*/
    AND    GS.STOCK_QTY > 0 /*재고있는상품*/
    AND    O.SOLDOUT_YN = 'N' /*품절이 아닌 옵션*/
    AND    O.DISP_YN = 'Y' /*노출하는 옵션*/
    AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
    AND    SB.SITE_CD = 'G000_10'
    AND    SB.USE_YN = 'Y' /*사이트에서 사용하는 브랜드*/
)
SELECT A.SITE_CD
     , A.CATE_GB
     , A.FORMAL_GB
     , A.BRAND_GROUP_NO
     , A.CATE_NO
     , 'COLOR'          AS FILTER_GB
     , A.FILTER_CD
     , CC.CD_DESC       AS FILTER_NM
     , RANK() OVER(PARTITION BY A.SITE_CD, A.CATE_GB, A.FORMAL_GB, A.BRAND_GROUP_NO, A.CATE_NO
                   ORDER BY CC.DISP_ORD
                  )   AS DISP_ORD
FROM   (
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , 0 as CATE_NO
             , FILTER_CD
        FROM   (
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE5_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE5_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE4_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE4_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE3_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE3_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE2_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE2_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE1_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE1_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL 
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE5_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE5_NO IS NOT NULL
                AND  FORMAL_GB  = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE4_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE4_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE3_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE3_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE2_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE2_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE1_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE1_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
               ) Z
        WHERE  CATE_NO IS NOT NULL
       ) A
     , TB_COMMON_CODE CC
WHERE  A.FILTER_CD = CC.CD
AND    CC.CD_GB = 'G072'
AND    CC.USE_YN = 'Y'
;

-- 혜택 필터 생성
INSERT INTO TB_CATE_FILTER_TMP (
       SITE_CD
     , CATE_GB
     , FORMAL_GB
     , BRAND_GROUP_NO
     , CATE_NO
     , FILTER_GB
     , FILTER_CD
     , FILTER_NM
     , DISP_ORD
)
WITH TAB_FILTER AS (
    SELECT C4.SITE_CD
         , C4.CATE_GB
         , G.FORMAL_GB
         , B.BRAND_GROUP_NO
         , C4.CATE1_NO
         , C4.CATE2_NO
         , C4.CATE3_NO
         , C4.CATE4_NO
         , C4.CATE5_NO
         , G.GOODS_CD
         , GB.BENEFIT_GB
    FROM   TB_CATE_4SRCH C4
         , TB_CATE_GOODS CG
         , TB_GOODS G
         , TB_GOODS_STOCK GS
         , TB_GOODS_BENEFIT GB
         , TB_BRAND B
         , TB_SITE_BRAND SB
    WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
--     AND    CG.BRAND_GROUP_NO = 0
--     and B.BRAND_GROUP_NO = 19706
    AND    CG.GOODS_CD = G.GOODS_CD
    AND    CG.GOODS_CD = GS.GOODS_CD
    AND    CG.GOODS_CD = GB.GOODS_CD
    AND    G.BRAND_CD = B.BRAND_CD
    AND    B.BRAND_CD = SB.BRAND_CD
    AND    C4.SITE_CD = 'G000_10'
    AND    C4.CATE_GB = 'G032_101'
    AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
    AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
    AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
    AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*판매기간*/
    AND    GS.STOCK_QTY > 0 /*재고있는상품*/
    AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
    AND    SB.SITE_CD = 'G000_10'
    AND    SB.USE_YN = 'Y' /*사이트에서 사용하는 브랜드*/
)
SELECT distinct SITE_CD
     , CATE_GB
     , FORMAL_GB
     , BRAND_GROUP_NO
     , 0 as CATE_NO
     , 'BENEFIT'        AS FILTER_GB
     , FILTER_CD
     , CASE WHEN FILTER_CD = '10' THEN '쿠폰할인'
            WHEN FILTER_CD = '20' THEN '무료배송'
            WHEN FILTER_CD = '30' THEN '사은품'
            WHEN FILTER_CD = '40' THEN '신상'
			WHEN FILTER_CD = '50' THEN '총알배송'
       END              AS FILTER_NM
     , RANK() OVER(PARTITION BY SITE_CD, CATE_GB, FORMAL_GB, BRAND_GROUP_NO, CATE_NO
                   ORDER BY FILTER_CD
                  )   AS DISP_ORD
FROM   (
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , 'G009_10' AS FORMAL_GB
             , BRAND_GROUP_NO
             , 0       AS CATE_NO
             , BENEFIT_GB     AS FILTER_CD
        FROM   TAB_FILTER
        WHERE  CATE5_NO IS NOT NULL
        AND  FORMAL_GB IN ('G009_10','G009_20')
        UNION ALL
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , 'G009_10' AS FORMAL_GB
             , BRAND_GROUP_NO
             , 0       AS CATE_NO
             , BENEFIT_GB     AS FILTER_CD
        FROM   TAB_FILTER
        WHERE  CATE4_NO IS NOT NULL
        AND  FORMAL_GB IN ('G009_10','G009_20')
        UNION ALL
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , 'G009_10' AS FORMAL_GB
             , BRAND_GROUP_NO
             , 0       AS CATE_NO
             , BENEFIT_GB     AS FILTER_CD
        FROM   TAB_FILTER
        WHERE  CATE3_NO IS NOT NULL
        AND  FORMAL_GB IN ('G009_10','G009_20')
        UNION ALL
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , 'G009_10' AS FORMAL_GB
             , BRAND_GROUP_NO
             , 0       AS CATE_NO
             , BENEFIT_GB     AS FILTER_CD
        FROM   TAB_FILTER
        WHERE  CATE2_NO IS NOT NULL
        AND  FORMAL_GB IN ('G009_10','G009_20')
        UNION ALL
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , 'G009_10' AS FORMAL_GB
             , BRAND_GROUP_NO
             , 0       AS CATE_NO
             , BENEFIT_GB     AS FILTER_CD
        FROM   TAB_FILTER
        WHERE  CATE1_NO IS NOT NULL
        AND  FORMAL_GB IN ('G009_10','G009_20')
        UNION ALL 
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , 0       AS CATE_NO
             , BENEFIT_GB     AS FILTER_CD
        FROM   TAB_FILTER
        WHERE  CATE5_NO IS NOT NULL
        AND  FORMAL_GB = 'G009_20'
        UNION ALL
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , 0       AS CATE_NO
             , BENEFIT_GB     AS FILTER_CD
        FROM   TAB_FILTER
        WHERE  CATE4_NO IS NOT NULL
        AND  FORMAL_GB = 'G009_20'
        UNION ALL
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , 0       AS CATE_NO
             , BENEFIT_GB     AS FILTER_CD
        FROM   TAB_FILTER
        WHERE  CATE3_NO IS NOT NULL
        AND  FORMAL_GB = 'G009_20'
        UNION ALL
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , 0       AS CATE_NO
             , BENEFIT_GB     AS FILTER_CD
        FROM   TAB_FILTER
        WHERE  CATE2_NO IS NOT NULL
        AND  FORMAL_GB = 'G009_20'
        UNION ALL
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , 0       AS CATE_NO
             , BENEFIT_GB     AS FILTER_CD
        FROM   TAB_FILTER
        WHERE  CATE1_NO IS NOT NULL
        AND  FORMAL_GB = 'G009_20'
       ) Z
;

INSERT INTO TB_CATE_FILTER
SELECT DISTINCT SITE_CD
    , CATE_GB
    , FORMAL_GB
    , BRAND_GROUP_NO
    , CATE_NO
    , FILTER_GB
    , FILTER_CD
    , FILTER_NM
    , DISP_ORD
    , 12 AS REG_NO
    , NOW() AS REG_DT
FROM   TB_CATE_FILTER_TMP
;


-- 브랜드그룹번호 0 / 카테고리별 / 정상이월 구분 o 
-- 카테고리별 브랜드 필터 생성
INSERT INTO TB_CATE_FILTER_TMP (
       SITE_CD
     , CATE_GB
     , FORMAL_GB
     , BRAND_GROUP_NO
     , CATE_NO
     , FILTER_GB
     , FILTER_CD
     , FILTER_NM
     , DISP_ORD
)
WITH TAB_FILTER AS (
    SELECT C4.SITE_CD
         , C4.CATE_GB
         , G.FORMAL_GB
         , CG.BRAND_GROUP_NO
         , C4.CATE1_NO
         , C4.CATE2_NO
         , C4.CATE3_NO
         , C4.CATE4_NO
         , C4.CATE5_NO
         , B.BRAND_GROUP_NO  AS FILTER_CD
         , CASE WHEN BG.DISP_NM_LANG = 'EN' THEN
                BG.BRAND_GROUP_ENM
           ELSE
                BG.BRAND_GROUP_KNM
           END               AS FILTER_NM
    FROM   TB_CATE_4SRCH C4
         , TB_CATE_GOODS CG
         , TB_GOODS G
         , TB_GOODS_STOCK GS
         , TB_BRAND B
         , TB_SITE_BRAND SB
         , TB_BRAND_GROUP BG
    WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
    AND    CG.BRAND_GROUP_NO = 0
    AND    CG.GOODS_CD = G.GOODS_CD
    AND    G.GOODS_CD = GS.GOODS_CD
    AND    G.BRAND_CD = B.BRAND_CD
    AND    B.BRAND_CD = SB.BRAND_CD
    AND    B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
    AND    C4.SITE_CD = 'G000_10'
    AND    C4.CATE_GB = 'G032_101'
    AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
    AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
    AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
    AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*판매기간*/
    AND    GS.STOCK_QTY > 0 /*재고있는상품*/
    AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
    AND    SB.SITE_CD = 'G000_10'
    AND    SB.USE_YN = 'Y' /*사이트에서 사용하는 브랜드*/
    AND    BG.USE_YN = 'Y' /*사용하는 브랜드그룹*/
)
SELECT SITE_CD
     , CATE_GB
     , FORMAL_GB
     , BRAND_GROUP_NO
     , CATE_NO
     , 'BRAND'        AS FILTER_GB
     , FILTER_CD
     , FILTER_NM
     , RANK() OVER(PARTITION BY SITE_CD, CATE_GB, FORMAL_GB, BRAND_GROUP_NO, CATE_NO
                   ORDER BY FILTER_NM
                  )   AS DISP_ORD
FROM   (
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE_NO
             , FILTER_CD
             , FILTER_NM
        FROM   (
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE5_NO       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE5_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE4_NO       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE4_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE3_NO       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE3_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE2_NO       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE2_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE1_NO       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE1_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL 
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE5_NO       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE5_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE4_NO       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE4_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE3_NO       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE3_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE2_NO       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE2_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE1_NO       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE1_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
               ) Z
        WHERE  CATE_NO IS NOT NULL
    ) Z
;

-- 사이즈 필터 생성
INSERT INTO TB_CATE_FILTER_TMP (
       SITE_CD
     , CATE_GB
     , FORMAL_GB
     , BRAND_GROUP_NO
     , CATE_NO
     , FILTER_GB
     , FILTER_CD
     , FILTER_NM
     , DISP_ORD
)
WITH TAB_FILTER AS (
    SELECT C4.SITE_CD
         , C4.CATE_GB
         , G.FORMAL_GB
         , CG.BRAND_GROUP_NO
         , C4.CATE1_NO
         , C4.CATE2_NO
         , C4.CATE3_NO
         , C4.CATE4_NO
         , C4.CATE5_NO
         , CONCAT(I.SIZE_GB,'|',S.OPT_CD2) AS FILTER_CD
         , S.OPT_CD2                       AS FILTER_NM
    FROM   TB_CATE_4SRCH C4
         , TB_CATE_GOODS CG
         , TB_GOODS G
         , TB_ITEMKIND I
         , VW_STOCK S
         , TB_BRAND B
         , TB_SITE_BRAND SB
    WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
    AND    CG.BRAND_GROUP_NO = 0
    AND    CG.GOODS_CD = G.GOODS_CD
    AND    G.ITEMKIND_CD = I.ITEMKIND_CD
    AND    G.GOODS_CD = S.GOODS_CD
    AND    G.BRAND_CD = B.BRAND_CD
    AND    B.BRAND_CD = SB.BRAND_CD
    AND    C4.SITE_CD = 'G000_10'
    AND    C4.CATE_GB = 'G032_101'
    AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
    AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
    AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
    AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*판매기간*/
    AND    I.SIZE_GB IS NOT NULL
    AND    S.CURR_STOCK_QTY - S.BASE_STOCK_QTY > 0 /*재고있는옵션*/
    AND    S.SOLDOUT_YN = 'N' /*품절이 아닌 옵션*/
    AND    S.DISP_YN = 'Y' /*노출하는 옵션*/
    AND    LENGTH(S.OPT_CD2) > 0
    AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
    AND    SB.SITE_CD = 'G000_10'
    AND    SB.USE_YN = 'Y' /*사이트에서 사용하는 브랜드*/
)
SELECT SITE_CD
     , CATE_GB
     , FORMAL_GB
     , BRAND_GROUP_NO
     , CATE_NO
     , 'SIZE'        AS FILTER_GB
     , FILTER_CD
     , FILTER_NM
     , RANK() OVER(PARTITION BY SITE_CD, CATE_GB, FORMAL_GB, BRAND_GROUP_NO, CATE_NO
                   ORDER BY CASE WHEN FILTER_CD LIKE 'T%' THEN 1
                                 WHEN FILTER_CD LIKE 'B%' THEN 2
                                 WHEN FILTER_CD LIKE 'S%' THEN 3
                                 ELSE 4
                            END, FILTER_NM
                  )   AS DISP_ORD
FROM   (
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE_NO
             , FILTER_CD
             , FILTER_NM
        FROM   (
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE5_NO       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE5_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE4_NO       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE4_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE3_NO       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE3_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE2_NO       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE2_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE1_NO       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE1_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL 
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE5_NO       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE5_NO IS NOT NULL
                AND  FORMAL_GB  = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE4_NO       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE4_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE3_NO       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE3_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE2_NO       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE2_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE1_NO       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE1_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
               ) Z
        WHERE  CATE_NO IS NOT NULL
    ) Z
;

-- 가격 필터 생성
INSERT INTO TB_CATE_FILTER_TMP (
       SITE_CD
     , CATE_GB
     , FORMAL_GB
     , BRAND_GROUP_NO
     , CATE_NO
     , FILTER_GB
     , FILTER_CD
     , FILTER_NM
     , DISP_ORD
)
WITH TAB_FILTER AS (
    SELECT C4.SITE_CD
         , C4.CATE_GB
         , G.FORMAL_GB
         , CG.BRAND_GROUP_NO
         , C4.CATE1_NO
         , C4.CATE2_NO
         , C4.CATE3_NO
         , C4.CATE4_NO
         , C4.CATE5_NO
         , FN_GET_BENEFIT_PRICE('P',G.GOODS_CD,G.CURR_PRICE,'G100_10') AS CURR_PRICE
    FROM   TB_CATE_4SRCH C4
         , TB_CATE_GOODS CG
         , TB_GOODS G
         , TB_GOODS_STOCK GS
         , TB_BRAND B
         , TB_SITE_BRAND SB
    WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
    AND    CG.BRAND_GROUP_NO = 0
    AND    CG.GOODS_CD = G.GOODS_CD
    AND    CG.GOODS_CD = GS.GOODS_CD
    AND    G.BRAND_CD = B.BRAND_CD
    AND    B.BRAND_CD = SB.BRAND_CD
    AND    C4.SITE_CD = 'G000_10'
    AND    C4.CATE_GB = 'G032_101'
    AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
    AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
    AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
    AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*판매기간*/
    AND    GS.STOCK_QTY > 0 /*재고있는옵션*/
    AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
    AND    SB.SITE_CD = 'G000_10'
    AND    SB.USE_YN = 'Y' /*사이트에서 사용하는 브랜드*/
)
, TAB_PRICE AS (
    SELECT SITE_CD
         , CATE_GB
         , 'G009_10' AS FORMAL_GB
         , BRAND_GROUP_NO
         , CATE_NO
         , FLOOR(MIN_CURR_PRICE / 1000) * 1000                   AS PRICE1
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 1) / 1000) * 1000 AS PRICE2
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 2) / 1000) * 1000 AS PRICE3
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 3) / 1000) * 1000 AS PRICE4
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 4) / 1000) * 1000 AS PRICE5
         , FLOOR(MAX_CURR_PRICE / 1000) * 1000                   AS PRICE6
    FROM   (
            SELECT SITE_CD
                 , CATE_GB
                 , BRAND_GROUP_NO
                 , CATE1_NO        AS CATE_NO
                 , MIN(CURR_PRICE) AS MIN_CURR_PRICE                    /*최소현재판매가*/
                 , MAX(CURR_PRICE) AS MAX_CURR_PRICE                    /*최대현재판매가*/
                 , (MAX(CURR_PRICE) - MIN(CURR_PRICE)) / 5 AS SLICE_VAL /*분할값*/
            FROM   TAB_FILTER
            WHERE  CATE1_NO IS NOT NULL
            AND  FORMAL_GB IN ('G009_10','G009_20')
            GROUP  BY SITE_CD, CATE_GB, BRAND_GROUP_NO, CATE1_NO
           ) Z
    UNION ALL
    SELECT SITE_CD
         , CATE_GB
         , 'G009_10' AS FORMAL_GB
         , BRAND_GROUP_NO
         , CATE_NO
         , FLOOR(MIN_CURR_PRICE / 1000) * 1000                   AS PRICE1
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 1) / 1000) * 1000 AS PRICE2
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 2) / 1000) * 1000 AS PRICE3
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 3) / 1000) * 1000 AS PRICE4
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 4) / 1000) * 1000 AS PRICE5
         , FLOOR(MAX_CURR_PRICE / 1000) * 1000                   AS PRICE6
    FROM   (
            SELECT SITE_CD
                 , CATE_GB
                 , BRAND_GROUP_NO
                 , CATE2_NO        AS CATE_NO
                 , MIN(CURR_PRICE) AS MIN_CURR_PRICE                    /*최소현재판매가*/
                 , MAX(CURR_PRICE) AS MAX_CURR_PRICE                    /*최대현재판매가*/
                 , (MAX(CURR_PRICE) - MIN(CURR_PRICE)) / 5 AS SLICE_VAL /*분할값*/
            FROM   TAB_FILTER
            WHERE  CATE2_NO IS NOT NULL
            AND  FORMAL_GB IN ('G009_10','G009_20')
            GROUP  BY SITE_CD, CATE_GB, BRAND_GROUP_NO, CATE2_NO
           ) Z
    UNION ALL
    SELECT SITE_CD
         , CATE_GB
         , 'G009_10' AS FORMAL_GB
         , BRAND_GROUP_NO
         , CATE_NO
         , FLOOR(MIN_CURR_PRICE / 1000) * 1000                   AS PRICE1
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 1) / 1000) * 1000 AS PRICE2
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 2) / 1000) * 1000 AS PRICE3
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 3) / 1000) * 1000 AS PRICE4
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 4) / 1000) * 1000 AS PRICE5
         , FLOOR(MAX_CURR_PRICE / 1000) * 1000                   AS PRICE6
    FROM   (
            SELECT SITE_CD
                 , CATE_GB
                 , BRAND_GROUP_NO
                 , CATE3_NO        AS CATE_NO
                 , MIN(CURR_PRICE) AS MIN_CURR_PRICE                    /*최소현재판매가*/
                 , MAX(CURR_PRICE) AS MAX_CURR_PRICE                    /*최대현재판매가*/
                 , (MAX(CURR_PRICE) - MIN(CURR_PRICE)) / 5 AS SLICE_VAL /*분할값*/
            FROM   TAB_FILTER
            WHERE  CATE3_NO IS NOT NULL
            AND  FORMAL_GB IN ('G009_10','G009_20')
            GROUP  BY SITE_CD, CATE_GB, BRAND_GROUP_NO, CATE3_NO
           ) Z
    UNION ALL
    SELECT SITE_CD
         , CATE_GB
         , 'G009_10' AS FORMAL_GB
         , BRAND_GROUP_NO
         , CATE_NO
         , FLOOR(MIN_CURR_PRICE / 1000) * 1000                   AS PRICE1
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 1) / 1000) * 1000 AS PRICE2
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 2) / 1000) * 1000 AS PRICE3
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 3) / 1000) * 1000 AS PRICE4
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 4) / 1000) * 1000 AS PRICE5
         , FLOOR(MAX_CURR_PRICE / 1000) * 1000                   AS PRICE6
    FROM   (
            SELECT SITE_CD
                 , CATE_GB
                 , BRAND_GROUP_NO
                 , CATE4_NO        AS CATE_NO
                 , MIN(CURR_PRICE) AS MIN_CURR_PRICE                    /*최소현재판매가*/
                 , MAX(CURR_PRICE) AS MAX_CURR_PRICE                    /*최대현재판매가*/
                 , (MAX(CURR_PRICE) - MIN(CURR_PRICE)) / 5 AS SLICE_VAL /*분할값*/
            FROM   TAB_FILTER
            WHERE  CATE4_NO IS NOT NULL
            AND  FORMAL_GB IN ('G009_10','G009_20')
            GROUP  BY SITE_CD, CATE_GB, BRAND_GROUP_NO, CATE4_NO
           ) Z
    UNION ALL
    SELECT SITE_CD
         , CATE_GB
         , 'G009_10' AS FORMAL_GB
         , BRAND_GROUP_NO
         , CATE_NO
         , FLOOR(MIN_CURR_PRICE / 1000) * 1000                   AS PRICE1
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 1) / 1000) * 1000 AS PRICE2
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 2) / 1000) * 1000 AS PRICE3
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 3) / 1000) * 1000 AS PRICE4
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 4) / 1000) * 1000 AS PRICE5
         , FLOOR(MAX_CURR_PRICE / 1000) * 1000                   AS PRICE6
    FROM   (
            SELECT SITE_CD
                 , CATE_GB
                 , BRAND_GROUP_NO
                 , CATE5_NO        AS CATE_NO
                 , MIN(CURR_PRICE) AS MIN_CURR_PRICE                    /*최소현재판매가*/
                 , MAX(CURR_PRICE) AS MAX_CURR_PRICE                    /*최대현재판매가*/
                 , (MAX(CURR_PRICE) - MIN(CURR_PRICE)) / 5 AS SLICE_VAL /*분할값*/
            FROM   TAB_FILTER
            WHERE  CATE5_NO IS NOT NULL
            AND  FORMAL_GB IN ('G009_10','G009_20')
            GROUP  BY SITE_CD, CATE_GB, BRAND_GROUP_NO, CATE5_NO
           ) Z
	UNION ALL 
	SELECT SITE_CD
         , CATE_GB
         , 'G009_20' AS FORMAL_GB
         , BRAND_GROUP_NO
         , CATE_NO
         , FLOOR(MIN_CURR_PRICE / 1000) * 1000                   AS PRICE1
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 1) / 1000) * 1000 AS PRICE2
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 2) / 1000) * 1000 AS PRICE3
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 3) / 1000) * 1000 AS PRICE4
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 4) / 1000) * 1000 AS PRICE5
         , FLOOR(MAX_CURR_PRICE / 1000) * 1000                   AS PRICE6
    FROM   (
            SELECT SITE_CD
                 , CATE_GB
                 , BRAND_GROUP_NO
                 , CATE1_NO        AS CATE_NO
                 , MIN(CURR_PRICE) AS MIN_CURR_PRICE                    /*최소현재판매가*/
                 , MAX(CURR_PRICE) AS MAX_CURR_PRICE                    /*최대현재판매가*/
                 , (MAX(CURR_PRICE) - MIN(CURR_PRICE)) / 5 AS SLICE_VAL /*분할값*/
            FROM   TAB_FILTER
            WHERE  CATE1_NO IS NOT NULL
            AND  FORMAL_GB = 'G009_20'
            GROUP  BY SITE_CD, CATE_GB, BRAND_GROUP_NO, CATE1_NO
           ) Z
    UNION ALL
    SELECT SITE_CD
         , CATE_GB
         , 'G009_20' AS FORMAL_GB
         , BRAND_GROUP_NO
         , CATE_NO
         , FLOOR(MIN_CURR_PRICE / 1000) * 1000                   AS PRICE1
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 1) / 1000) * 1000 AS PRICE2
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 2) / 1000) * 1000 AS PRICE3
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 3) / 1000) * 1000 AS PRICE4
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 4) / 1000) * 1000 AS PRICE5
         , FLOOR(MAX_CURR_PRICE / 1000) * 1000                   AS PRICE6
    FROM   (
            SELECT SITE_CD
                 , CATE_GB
                 , BRAND_GROUP_NO
                 , CATE2_NO        AS CATE_NO
                 , MIN(CURR_PRICE) AS MIN_CURR_PRICE                    /*최소현재판매가*/
                 , MAX(CURR_PRICE) AS MAX_CURR_PRICE                    /*최대현재판매가*/
                 , (MAX(CURR_PRICE) - MIN(CURR_PRICE)) / 5 AS SLICE_VAL /*분할값*/
            FROM   TAB_FILTER
            WHERE  CATE2_NO IS NOT NULL
            AND  FORMAL_GB = 'G009_20'
            GROUP  BY SITE_CD, CATE_GB, BRAND_GROUP_NO, CATE2_NO
           ) Z
    UNION ALL
    SELECT SITE_CD
         , CATE_GB
         , 'G009_20' AS FORMAL_GB
         , BRAND_GROUP_NO
         , CATE_NO
         , FLOOR(MIN_CURR_PRICE / 1000) * 1000                   AS PRICE1
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 1) / 1000) * 1000 AS PRICE2
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 2) / 1000) * 1000 AS PRICE3
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 3) / 1000) * 1000 AS PRICE4
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 4) / 1000) * 1000 AS PRICE5
         , FLOOR(MAX_CURR_PRICE / 1000) * 1000                   AS PRICE6
    FROM   (
            SELECT SITE_CD
                 , CATE_GB
                 , BRAND_GROUP_NO
                 , CATE3_NO        AS CATE_NO
                 , MIN(CURR_PRICE) AS MIN_CURR_PRICE                    /*최소현재판매가*/
                 , MAX(CURR_PRICE) AS MAX_CURR_PRICE                    /*최대현재판매가*/
                 , (MAX(CURR_PRICE) - MIN(CURR_PRICE)) / 5 AS SLICE_VAL /*분할값*/
            FROM   TAB_FILTER
            WHERE  CATE3_NO IS NOT NULL
            AND  FORMAL_GB = 'G009_20'
            GROUP  BY SITE_CD, CATE_GB, BRAND_GROUP_NO, CATE3_NO
           ) Z
    UNION ALL
    SELECT SITE_CD
         , CATE_GB
         , 'G009_20' AS FORMAL_GB
         , BRAND_GROUP_NO
         , CATE_NO
         , FLOOR(MIN_CURR_PRICE / 1000) * 1000                   AS PRICE1
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 1) / 1000) * 1000 AS PRICE2
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 2) / 1000) * 1000 AS PRICE3
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 3) / 1000) * 1000 AS PRICE4
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 4) / 1000) * 1000 AS PRICE5
         , FLOOR(MAX_CURR_PRICE / 1000) * 1000                   AS PRICE6
    FROM   (
            SELECT SITE_CD
                 , CATE_GB
                 , BRAND_GROUP_NO
                 , CATE4_NO        AS CATE_NO
                 , MIN(CURR_PRICE) AS MIN_CURR_PRICE                    /*최소현재판매가*/
                 , MAX(CURR_PRICE) AS MAX_CURR_PRICE                    /*최대현재판매가*/
                 , (MAX(CURR_PRICE) - MIN(CURR_PRICE)) / 5 AS SLICE_VAL /*분할값*/
            FROM   TAB_FILTER
            WHERE  CATE4_NO IS NOT NULL
            AND  FORMAL_GB = 'G009_20'
            GROUP  BY SITE_CD, CATE_GB, BRAND_GROUP_NO, CATE4_NO
           ) Z
    UNION ALL
    SELECT SITE_CD
         , CATE_GB
         , 'G009_20' AS FORMAL_GB
         , BRAND_GROUP_NO
         , CATE_NO
         , FLOOR(MIN_CURR_PRICE / 1000) * 1000                   AS PRICE1
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 1) / 1000) * 1000 AS PRICE2
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 2) / 1000) * 1000 AS PRICE3
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 3) / 1000) * 1000 AS PRICE4
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 4) / 1000) * 1000 AS PRICE5
         , FLOOR(MAX_CURR_PRICE / 1000) * 1000                   AS PRICE6
    FROM   (
            SELECT SITE_CD
                 , CATE_GB
                 , BRAND_GROUP_NO
                 , CATE5_NO        AS CATE_NO
                 , MIN(CURR_PRICE) AS MIN_CURR_PRICE                    /*최소현재판매가*/
                 , MAX(CURR_PRICE) AS MAX_CURR_PRICE                    /*최대현재판매가*/
                 , (MAX(CURR_PRICE) - MIN(CURR_PRICE)) / 5 AS SLICE_VAL /*분할값*/
            FROM   TAB_FILTER
            WHERE  CATE5_NO IS NOT NULL
            AND  FORMAL_GB = 'G009_20'
            GROUP  BY SITE_CD, CATE_GB, BRAND_GROUP_NO, CATE5_NO
           ) Z
)
SELECT DISTINCT
       SITE_CD
     , CATE_GB
     , FORMAL_GB
     , BRAND_GROUP_NO
     , CATE_NO
     , 'PRICE'        AS FILTER_GB
     , FILTER_CD
     , FILTER_NM
     , RANK() OVER(PARTITION BY SITE_CD, CATE_GB, FORMAL_GB, BRAND_GROUP_NO, CATE_NO
                   ORDER BY FILTER_CD
                   )  AS DISP_ORD
FROM   (
        SELECT SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE_NO
             , PRICE1         AS FILTER_CD
             , PRICE1         AS FILTER_NM
        FROM   TAB_PRICE
        UNION ALL
        SELECT SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE_NO
             , PRICE2         AS FILTER_CD
             , PRICE2         AS FILTER_NM
        FROM   TAB_PRICE
        UNION ALL
        SELECT SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE_NO
             , PRICE3         AS FILTER_CD
             , PRICE3         AS FILTER_NM
        FROM   TAB_PRICE
        UNION ALL
        SELECT SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE_NO
             , PRICE4         AS FILTER_CD
             , PRICE4         AS FILTER_NM
        FROM   TAB_PRICE
        UNION ALL
        SELECT SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE_NO
             , PRICE5         AS FILTER_CD
             , PRICE5         AS FILTER_NM
        FROM   TAB_PRICE
        UNION ALL
        SELECT SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE_NO
             , PRICE6         AS FILTER_CD
             , PRICE6         AS FILTER_NM
        FROM   TAB_PRICE
       ) Z
;

-- 연령대 필터 생성
INSERT INTO TB_CATE_FILTER_TMP (
       SITE_CD
     , CATE_GB
     , FORMAL_GB
     , BRAND_GROUP_NO
     , CATE_NO
     , FILTER_GB
     , FILTER_CD
     , FILTER_NM
     , DISP_ORD
)
WITH TAB_FILTER AS (
    SELECT C4.SITE_CD
         , C4.CATE_GB
         , G.FORMAL_GB
         , CG.BRAND_GROUP_NO
         , C4.CATE1_NO
         , C4.CATE2_NO
         , C4.CATE3_NO
         , C4.CATE4_NO
         , C4.CATE5_NO
         , G.AGE_GRP_CD      AS FILTER_CD
    FROM   TB_CATE_4SRCH C4
         , TB_CATE_GOODS CG
         , TB_GOODS G
         , TB_GOODS_STOCK GS
         , TB_BRAND B
         , TB_SITE_BRAND SB
    WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
    AND    CG.BRAND_GROUP_NO = 0
    AND    CG.GOODS_CD = G.GOODS_CD
    AND    CG.GOODS_CD = GS.GOODS_CD
    AND    G.BRAND_CD = B.BRAND_CD
    AND    B.BRAND_CD = SB.BRAND_CD
    AND    C4.SITE_CD = 'G000_10'
    AND    C4.CATE_GB = 'G032_101'
    AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
    AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
    AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
    AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*판매기간*/
    AND    G.AGE_GRP_CD IS NOT NULL
    AND    GS.STOCK_QTY > 0 /*재고있는상품*/
    AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
    AND    SB.SITE_CD = 'G000_10'
    AND    SB.USE_YN = 'Y' /*사이트에서 사용하는 브랜드*/
)
SELECT A.SITE_CD
     , A.CATE_GB
     , A.FORMAL_GB
     , A.BRAND_GROUP_NO
     , A.CATE_NO
     , 'AGE'          AS FILTER_GB
     , A.FILTER_CD
     , CC.CD_NM       AS FILTER_NM
     , RANK() OVER(PARTITION BY A.SITE_CD, A.CATE_GB, A.FORMAL_GB, A.BRAND_GROUP_NO, A.CATE_NO
                   ORDER BY CC.DISP_ORD
                  )   AS DISP_ORD
FROM   (
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE_NO
             , FILTER_CD
        FROM   (
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE5_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE5_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE4_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE4_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE3_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE3_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE2_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE2_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE1_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE1_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL 
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE5_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE5_NO IS NOT NULL
                AND  FORMAL_GB  = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE4_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE4_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE3_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE3_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE2_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE2_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE1_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE1_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
               ) Z
        WHERE  CATE_NO IS NOT NULL
       ) A
     , TB_COMMON_CODE CC
WHERE  A.FILTER_CD = CC.CD
AND    CC.CD_GB = 'G023'
AND    CC.USE_YN = 'Y'
;

-- 시즌 필터 생성
INSERT INTO TB_CATE_FILTER_TMP (
       SITE_CD
     , CATE_GB
     , FORMAL_GB
     , BRAND_GROUP_NO
     , CATE_NO
     , FILTER_GB
     , FILTER_CD
     , FILTER_NM
     , DISP_ORD
)
WITH TAB_FILTER AS (
    SELECT C4.SITE_CD
         , C4.CATE_GB
         , G.FORMAL_GB
         , CG.BRAND_GROUP_NO
         , C4.CATE1_NO
         , C4.CATE2_NO
         , C4.CATE3_NO
         , C4.CATE4_NO
         , C4.CATE5_NO
         , G.SEASON_CD       AS FILTER_CD
    FROM   TB_CATE_4SRCH C4
         , TB_CATE_GOODS CG
         , TB_GOODS G
         , TB_GOODS_STOCK GS
         , TB_BRAND B
         , TB_SITE_BRAND SB
    WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
    AND    CG.BRAND_GROUP_NO = 0
    AND    CG.GOODS_CD = G.GOODS_CD
    AND    CG.GOODS_CD = GS.GOODS_CD
    AND    G.BRAND_CD = B.BRAND_CD
    AND    B.BRAND_CD = SB.BRAND_CD
    AND    C4.SITE_CD = 'G000_10'
    AND    C4.CATE_GB = 'G032_101'
    AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
    AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
    AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
    AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*판매기간*/
    AND    GS.STOCK_QTY > 0 /*재고있는상품*/
    AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
    AND    SB.SITE_CD = 'G000_10'
    AND    SB.USE_YN = 'Y' /*사이트에서 사용하는 브랜드*/
)
SELECT A.SITE_CD
     , A.CATE_GB
     , A.FORMAL_GB
     , A.BRAND_GROUP_NO
     , A.CATE_NO
     , 'SEASON'         AS FILTER_GB
     , A.FILTER_CD
     , CC.CD_NM         AS FILTER_NM
     , RANK() OVER(PARTITION BY A.SITE_CD, A.CATE_GB, A.FORMAL_GB, A.BRAND_GROUP_NO, A.CATE_NO
                   ORDER BY CC.DISP_ORD
                  )   AS DISP_ORD
FROM   (
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE_NO
             , FILTER_CD
        FROM   (
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE5_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE5_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE4_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE4_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE3_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE3_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE2_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE2_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE1_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE1_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL 
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE5_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE5_NO IS NOT NULL
                AND  FORMAL_GB  = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE4_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE4_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE3_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE3_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE2_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE2_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE1_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE1_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
               ) Z
        WHERE  CATE_NO IS NOT NULL
       ) A
     , TB_COMMON_CODE CC
WHERE  A.FILTER_CD = CC.CD
AND    CC.CD_GB = 'G006'
AND    CC.USE_YN = 'Y'
;

-- 색상 필터 생성
INSERT INTO TB_CATE_FILTER_TMP (
       SITE_CD
     , CATE_GB
     , FORMAL_GB
     , BRAND_GROUP_NO
     , CATE_NO
     , FILTER_GB
     , FILTER_CD
     , FILTER_NM
     , DISP_ORD
)
WITH TAB_FILTER AS (
    SELECT C4.SITE_CD
         , C4.CATE_GB
         , G.FORMAL_GB
         , CG.BRAND_GROUP_NO
         , C4.CATE1_NO
         , C4.CATE2_NO
         , C4.CATE3_NO
         , C4.CATE4_NO
         , C4.CATE5_NO
         , C.COLOR_GRP_CD    AS FILTER_CD
    FROM   TB_CATE_4SRCH C4
         , TB_CATE_GOODS CG
         , TB_GOODS G
         , TB_GOODS_STOCK GS
         , TB_OPTION O
         , TB_COLOR C
         , TB_BRAND B
         , TB_SITE_BRAND SB
    WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
    AND    CG.BRAND_GROUP_NO = 0
    AND    CG.GOODS_CD = G.GOODS_CD
    AND    CG.GOODS_CD = GS.GOODS_CD
    AND    CG.GOODS_CD = O.GOODS_CD
    AND    O.OPT_CD1 = C.COLOR_CD
    AND    G.BRAND_CD = B.BRAND_CD
    AND    B.BRAND_CD = SB.BRAND_CD
    AND    C4.SITE_CD = 'G000_10'
    AND    C4.CATE_GB = 'G032_101'
    AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
    AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
    AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
    AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*판매기간*/
    AND    GS.STOCK_QTY > 0 /*재고있는상품*/
    AND    O.SOLDOUT_YN = 'N' /*품절이 아닌 옵션*/
    AND    O.DISP_YN = 'Y' /*노출하는 옵션*/
    AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
    AND    SB.SITE_CD = 'G000_10'
    AND    SB.USE_YN = 'Y' /*사이트에서 사용하는 브랜드*/
)
SELECT A.SITE_CD
     , A.CATE_GB
     , A.FORMAL_GB
     , A.BRAND_GROUP_NO
     , A.CATE_NO
     , 'COLOR'          AS FILTER_GB
     , A.FILTER_CD
     , CC.CD_DESC       AS FILTER_NM
     , RANK() OVER(PARTITION BY A.SITE_CD, A.CATE_GB, A.FORMAL_GB, A.BRAND_GROUP_NO, A.CATE_NO
                   ORDER BY CC.DISP_ORD
                  )   AS DISP_ORD
FROM   (
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE_NO
             , FILTER_CD
        FROM   (
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE5_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE5_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE4_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE4_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE3_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE3_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE2_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE2_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE1_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE1_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL 
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE5_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE5_NO IS NOT NULL
                AND  FORMAL_GB  = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE4_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE4_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE3_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE3_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE2_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE2_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE1_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE1_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
               ) Z
        WHERE  CATE_NO IS NOT NULL
       ) A
     , TB_COMMON_CODE CC
WHERE  A.FILTER_CD = CC.CD
AND    CC.CD_GB = 'G072'
AND    CC.USE_YN = 'Y'
;

-- 혜택 필터 생성
INSERT INTO TB_CATE_FILTER_TMP (
       SITE_CD
     , CATE_GB
     , FORMAL_GB
     , BRAND_GROUP_NO
     , CATE_NO
     , FILTER_GB
     , FILTER_CD
     , FILTER_NM
     , DISP_ORD
)
WITH TAB_FILTER AS (
    SELECT C4.SITE_CD
         , C4.CATE_GB
         , G.FORMAL_GB
         , CG.BRAND_GROUP_NO
         , C4.CATE1_NO
         , C4.CATE2_NO
         , C4.CATE3_NO
         , C4.CATE4_NO
         , C4.CATE5_NO
         , G.GOODS_CD
         , GB.BENEFIT_GB
    FROM   TB_CATE_4SRCH C4
         , TB_CATE_GOODS CG
         , TB_GOODS G
         , TB_GOODS_STOCK GS
         , TB_GOODS_BENEFIT GB
         , TB_BRAND B
         , TB_SITE_BRAND SB
    WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
    AND    CG.BRAND_GROUP_NO = 0
    AND    CG.GOODS_CD = G.GOODS_CD
    AND    CG.GOODS_CD = GS.GOODS_CD
    AND    CG.GOODS_CD = GB.GOODS_CD
    AND    G.BRAND_CD = B.BRAND_CD
    AND    B.BRAND_CD = SB.BRAND_CD
    AND    C4.SITE_CD = 'G000_10'
    AND    C4.CATE_GB = 'G032_101'
    AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
    AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
    AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
    AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*판매기간*/
    AND    GS.STOCK_QTY > 0 /*재고있는상품*/
    AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
    AND    SB.SITE_CD = 'G000_10'
    AND    SB.USE_YN = 'Y' /*사이트에서 사용하는 브랜드*/
)
SELECT SITE_CD
     , CATE_GB
     , FORMAL_GB
     , BRAND_GROUP_NO
     , CATE_NO
     , 'BENEFIT'        AS FILTER_GB
     , FILTER_CD
     , CASE WHEN FILTER_CD = '10' THEN '쿠폰할인'
            WHEN FILTER_CD = '20' THEN '무료배송'
            WHEN FILTER_CD = '30' THEN '사은품'
            WHEN FILTER_CD = '40' THEN '신상'
			WHEN FILTER_CD = '50' THEN '총알배송'
       END              AS FILTER_NM
     , RANK() OVER(PARTITION BY SITE_CD, CATE_GB, FORMAL_GB, BRAND_GROUP_NO, CATE_NO
                   ORDER BY FILTER_CD
                  )   AS DISP_ORD
FROM   (
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , 'G009_10' AS FORMAL_GB
             , BRAND_GROUP_NO
             , CATE5_NO       AS CATE_NO
             , BENEFIT_GB     AS FILTER_CD
        FROM   TAB_FILTER
        WHERE  CATE5_NO IS NOT NULL
        AND  FORMAL_GB IN ('G009_10','G009_20')
        UNION ALL
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , 'G009_10' AS FORMAL_GB
             , BRAND_GROUP_NO
             , CATE4_NO       AS CATE_NO
             , BENEFIT_GB     AS FILTER_CD
        FROM   TAB_FILTER
        WHERE  CATE4_NO IS NOT NULL
        AND  FORMAL_GB IN ('G009_10','G009_20')
        UNION ALL
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , 'G009_10' AS FORMAL_GB
             , BRAND_GROUP_NO
             , CATE3_NO       AS CATE_NO
             , BENEFIT_GB     AS FILTER_CD
        FROM   TAB_FILTER
        WHERE  CATE3_NO IS NOT NULL
        AND  FORMAL_GB IN ('G009_10','G009_20')
        UNION ALL
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , 'G009_10' AS FORMAL_GB
             , BRAND_GROUP_NO
             , CATE2_NO       AS CATE_NO
             , BENEFIT_GB     AS FILTER_CD
        FROM   TAB_FILTER
        WHERE  CATE2_NO IS NOT NULL
        AND  FORMAL_GB IN ('G009_10','G009_20')
        UNION ALL
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , 'G009_10' AS FORMAL_GB
             , BRAND_GROUP_NO
             , CATE1_NO       AS CATE_NO
             , BENEFIT_GB     AS FILTER_CD
        FROM   TAB_FILTER
        WHERE  CATE1_NO IS NOT NULL
        AND  FORMAL_GB IN ('G009_10','G009_20')
        UNION ALL 
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , 'G009_20' AS FORMAL_GB
             , BRAND_GROUP_NO
             , CATE5_NO       AS CATE_NO
             , BENEFIT_GB     AS FILTER_CD
        FROM   TAB_FILTER
        WHERE  CATE5_NO IS NOT NULL
        AND  FORMAL_GB = 'G009_20'
        UNION ALL
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , 'G009_20' AS FORMAL_GB
             , BRAND_GROUP_NO
             , CATE4_NO       AS CATE_NO
             , BENEFIT_GB     AS FILTER_CD
        FROM   TAB_FILTER
        WHERE  CATE4_NO IS NOT NULL
        AND  FORMAL_GB = 'G009_20'
        UNION ALL
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , 'G009_20' AS FORMAL_GB
             , BRAND_GROUP_NO
             , CATE3_NO       AS CATE_NO
             , BENEFIT_GB     AS FILTER_CD
        FROM   TAB_FILTER
        WHERE  CATE3_NO IS NOT NULL
        AND  FORMAL_GB = 'G009_20'
        UNION ALL
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , 'G009_20' AS FORMAL_GB
             , BRAND_GROUP_NO
             , CATE2_NO       AS CATE_NO
             , BENEFIT_GB     AS FILTER_CD
        FROM   TAB_FILTER
        WHERE  CATE2_NO IS NOT NULL
        AND  FORMAL_GB = 'G009_20'
        UNION ALL
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , 'G009_20' AS FORMAL_GB
             , BRAND_GROUP_NO
             , CATE1_NO       AS CATE_NO
             , BENEFIT_GB     AS FILTER_CD
        FROM   TAB_FILTER
        WHERE  CATE1_NO IS NOT NULL
        AND  FORMAL_GB = 'G009_20'
       ) Z
;

TRUNCATE TABLE TB_CATE_FILTER;

INSERT INTO TB_CATE_FILTER
SELECT DISTINCT SITE_CD
    , CATE_GB
    , FORMAL_GB
    , BRAND_GROUP_NO
    , CATE_NO
    , FILTER_GB
    , FILTER_CD
    , FILTER_NM
    , DISP_ORD
    , 2 AS REG_NO
    , NOW() AS REG_DT
FROM   TB_CATE_FILTER_TMP
;


-- 브랜드별/ 카테고리별/ 정상이월 구분 o 
-- 사이즈 필터 생성
INSERT INTO TB_CATE_FILTER_TMP (
       SITE_CD
     , CATE_GB
     , FORMAL_GB
     , BRAND_GROUP_NO
     , CATE_NO
     , FILTER_GB
     , FILTER_CD
     , FILTER_NM
     , DISP_ORD
)
WITH TAB_FILTER AS (
    SELECT C4.SITE_CD
         , C4.CATE_GB
         , G.FORMAL_GB
         , B.BRAND_GROUP_NO
         , C4.CATE1_NO
         , C4.CATE2_NO
         , C4.CATE3_NO
         , C4.CATE4_NO
         , C4.CATE5_NO
         , CONCAT(I.SIZE_GB,'|',S.OPT_CD2) AS FILTER_CD
         , S.OPT_CD2                       AS FILTER_NM
    FROM   TB_CATE_4SRCH C4
         , TB_CATE_GOODS CG
         , TB_GOODS G
         , TB_ITEMKIND I
         , VW_STOCK S
         , TB_BRAND B
         , TB_SITE_BRAND SB
    WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
--     AND    CG.BRAND_GROUP_NO = 0
    AND    CG.GOODS_CD = G.GOODS_CD
    AND    G.ITEMKIND_CD = I.ITEMKIND_CD
    AND    G.GOODS_CD = S.GOODS_CD
    AND    G.BRAND_CD = B.BRAND_CD
    AND    B.BRAND_CD = SB.BRAND_CD
    AND    C4.SITE_CD = 'G000_10'
    AND    C4.CATE_GB = 'G032_101'
    AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
    AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
    AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
    AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*판매기간*/
    AND    I.SIZE_GB IS NOT NULL
    AND    S.CURR_STOCK_QTY - S.BASE_STOCK_QTY > 0 /*재고있는옵션*/
    AND    S.SOLDOUT_YN = 'N' /*품절이 아닌 옵션*/
    AND    S.DISP_YN = 'Y' /*노출하는 옵션*/
    AND    LENGTH(S.OPT_CD2) > 0
    AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
    AND    SB.SITE_CD = 'G000_10'
    AND    SB.USE_YN = 'Y' /*사이트에서 사용하는 브랜드*/
)
SELECT SITE_CD
     , CATE_GB
     , FORMAL_GB
     , BRAND_GROUP_NO
     , CATE_NO
     , 'SIZE'        AS FILTER_GB
     , FILTER_CD
     , FILTER_NM
     , RANK() OVER(PARTITION BY SITE_CD, CATE_GB, FORMAL_GB, BRAND_GROUP_NO, CATE_NO
                   ORDER BY CASE WHEN FILTER_CD LIKE 'T%' THEN 1
                                 WHEN FILTER_CD LIKE 'B%' THEN 2
                                 WHEN FILTER_CD LIKE 'S%' THEN 3
                                 ELSE 4
                            END, FILTER_NM
                  )   AS DISP_ORD
FROM   (
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE_NO
             , FILTER_CD
             , FILTER_NM
        FROM   (
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE5_NO       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE5_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE4_NO       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE4_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE3_NO       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE3_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE2_NO       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE2_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE1_NO       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE1_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL 
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE5_NO       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE5_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE4_NO       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE4_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE3_NO       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE3_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE2_NO       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE2_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE1_NO       AS CATE_NO
                     , FILTER_CD
                     , FILTER_NM
                FROM   TAB_FILTER
                WHERE  CATE1_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
               ) Z
        WHERE  CATE_NO IS NOT NULL
    ) Z
    ;
    
    
   -- 가격 필터 생성
INSERT INTO TB_CATE_FILTER_TMP (
       SITE_CD
     , CATE_GB
     , FORMAL_GB
     , BRAND_GROUP_NO
     , CATE_NO
     , FILTER_GB
     , FILTER_CD
     , FILTER_NM
     , DISP_ORD
)
WITH TAB_FILTER AS (
    SELECT C4.SITE_CD
         , C4.CATE_GB
         , G.FORMAL_GB
         , B.BRAND_GROUP_NO
         , C4.CATE1_NO
         , C4.CATE2_NO
         , C4.CATE3_NO
         , C4.CATE4_NO
         , C4.CATE5_NO
         , FN_GET_BENEFIT_PRICE('P',G.GOODS_CD,G.CURR_PRICE,'G100_10') AS CURR_PRICE
    FROM   TB_CATE_4SRCH C4
         , TB_CATE_GOODS CG
         , TB_GOODS G
         , TB_GOODS_STOCK GS
         , TB_BRAND B
         , TB_SITE_BRAND SB
    WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
--     AND    CG.BRAND_GROUP_NO = 0
    AND    CG.GOODS_CD = G.GOODS_CD
    AND    CG.GOODS_CD = GS.GOODS_CD
    AND    G.BRAND_CD = B.BRAND_CD
    AND    B.BRAND_CD = SB.BRAND_CD
    AND    C4.SITE_CD = 'G000_10'
    AND    C4.CATE_GB = 'G032_101'
    AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
    AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
    AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
    AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*판매기간*/
    AND    GS.STOCK_QTY > 0 /*재고있는옵션*/
    AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
    AND    SB.SITE_CD = 'G000_10'
    AND    SB.USE_YN = 'Y' /*사이트에서 사용하는 브랜드*/
)
, TAB_PRICE AS (
    SELECT SITE_CD
         , CATE_GB
         , 'G009_10' AS FORMAL_GB
         , BRAND_GROUP_NO
         , CATE_NO
         , FLOOR(MIN_CURR_PRICE / 1000) * 1000                   AS PRICE1
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 1) / 1000) * 1000 AS PRICE2
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 2) / 1000) * 1000 AS PRICE3
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 3) / 1000) * 1000 AS PRICE4
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 4) / 1000) * 1000 AS PRICE5
         , FLOOR(MAX_CURR_PRICE / 1000) * 1000                   AS PRICE6
    FROM   (
            SELECT SITE_CD
                 , CATE_GB
                 , BRAND_GROUP_NO
                 , CATE1_NO        AS CATE_NO
                 , MIN(CURR_PRICE) AS MIN_CURR_PRICE                    /*최소현재판매가*/
                 , MAX(CURR_PRICE) AS MAX_CURR_PRICE                    /*최대현재판매가*/
                 , (MAX(CURR_PRICE) - MIN(CURR_PRICE)) / 5 AS SLICE_VAL /*분할값*/
            FROM   TAB_FILTER
            WHERE  CATE1_NO IS NOT NULL
            AND  FORMAL_GB IN ('G009_10','G009_20')
            GROUP  BY SITE_CD, CATE_GB, BRAND_GROUP_NO, CATE1_NO
           ) Z
    UNION ALL
    SELECT SITE_CD
         , CATE_GB
         , 'G009_10' AS FORMAL_GB
         , BRAND_GROUP_NO
         , CATE_NO
         , FLOOR(MIN_CURR_PRICE / 1000) * 1000                   AS PRICE1
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 1) / 1000) * 1000 AS PRICE2
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 2) / 1000) * 1000 AS PRICE3
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 3) / 1000) * 1000 AS PRICE4
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 4) / 1000) * 1000 AS PRICE5
         , FLOOR(MAX_CURR_PRICE / 1000) * 1000                   AS PRICE6
    FROM   (
            SELECT SITE_CD
                 , CATE_GB
                 , BRAND_GROUP_NO
                 , CATE2_NO        AS CATE_NO
                 , MIN(CURR_PRICE) AS MIN_CURR_PRICE                    /*최소현재판매가*/
                 , MAX(CURR_PRICE) AS MAX_CURR_PRICE                    /*최대현재판매가*/
                 , (MAX(CURR_PRICE) - MIN(CURR_PRICE)) / 5 AS SLICE_VAL /*분할값*/
            FROM   TAB_FILTER
            WHERE  CATE2_NO IS NOT NULL
            AND  FORMAL_GB IN ('G009_10','G009_20')
            GROUP  BY SITE_CD, CATE_GB, BRAND_GROUP_NO, CATE2_NO
           ) Z
    UNION ALL
    SELECT SITE_CD
         , CATE_GB
         , 'G009_10' AS FORMAL_GB
         , BRAND_GROUP_NO
         , CATE_NO
         , FLOOR(MIN_CURR_PRICE / 1000) * 1000                   AS PRICE1
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 1) / 1000) * 1000 AS PRICE2
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 2) / 1000) * 1000 AS PRICE3
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 3) / 1000) * 1000 AS PRICE4
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 4) / 1000) * 1000 AS PRICE5
         , FLOOR(MAX_CURR_PRICE / 1000) * 1000                   AS PRICE6
    FROM   (
            SELECT SITE_CD
                 , CATE_GB
                 , BRAND_GROUP_NO
                 , CATE3_NO        AS CATE_NO
                 , MIN(CURR_PRICE) AS MIN_CURR_PRICE                    /*최소현재판매가*/
                 , MAX(CURR_PRICE) AS MAX_CURR_PRICE                    /*최대현재판매가*/
                 , (MAX(CURR_PRICE) - MIN(CURR_PRICE)) / 5 AS SLICE_VAL /*분할값*/
            FROM   TAB_FILTER
            WHERE  CATE3_NO IS NOT NULL
            AND  FORMAL_GB IN ('G009_10','G009_20')
            GROUP  BY SITE_CD, CATE_GB, BRAND_GROUP_NO, CATE3_NO
           ) Z
    UNION ALL
    SELECT SITE_CD
         , CATE_GB
         , 'G009_10' AS FORMAL_GB
         , BRAND_GROUP_NO
         , CATE_NO
         , FLOOR(MIN_CURR_PRICE / 1000) * 1000                   AS PRICE1
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 1) / 1000) * 1000 AS PRICE2
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 2) / 1000) * 1000 AS PRICE3
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 3) / 1000) * 1000 AS PRICE4
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 4) / 1000) * 1000 AS PRICE5
         , FLOOR(MAX_CURR_PRICE / 1000) * 1000                   AS PRICE6
    FROM   (
            SELECT SITE_CD
                 , CATE_GB
                 , BRAND_GROUP_NO
                 , CATE4_NO        AS CATE_NO
                 , MIN(CURR_PRICE) AS MIN_CURR_PRICE                    /*최소현재판매가*/
                 , MAX(CURR_PRICE) AS MAX_CURR_PRICE                    /*최대현재판매가*/
                 , (MAX(CURR_PRICE) - MIN(CURR_PRICE)) / 5 AS SLICE_VAL /*분할값*/
            FROM   TAB_FILTER
            WHERE  CATE4_NO IS NOT NULL
            AND  FORMAL_GB IN ('G009_10','G009_20')
            GROUP  BY SITE_CD, CATE_GB, BRAND_GROUP_NO, CATE4_NO
           ) Z
    UNION ALL
    SELECT SITE_CD
         , CATE_GB
         , 'G009_10' AS FORMAL_GB
         , BRAND_GROUP_NO
         , CATE_NO
         , FLOOR(MIN_CURR_PRICE / 1000) * 1000                   AS PRICE1
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 1) / 1000) * 1000 AS PRICE2
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 2) / 1000) * 1000 AS PRICE3
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 3) / 1000) * 1000 AS PRICE4
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 4) / 1000) * 1000 AS PRICE5
         , FLOOR(MAX_CURR_PRICE / 1000) * 1000                   AS PRICE6
    FROM   (
            SELECT SITE_CD
                 , CATE_GB
                 , BRAND_GROUP_NO
                 , CATE5_NO        AS CATE_NO
                 , MIN(CURR_PRICE) AS MIN_CURR_PRICE                    /*최소현재판매가*/
                 , MAX(CURR_PRICE) AS MAX_CURR_PRICE                    /*최대현재판매가*/
                 , (MAX(CURR_PRICE) - MIN(CURR_PRICE)) / 5 AS SLICE_VAL /*분할값*/
            FROM   TAB_FILTER
            WHERE  CATE5_NO IS NOT NULL
            AND  FORMAL_GB IN ('G009_10','G009_20')
            GROUP  BY SITE_CD, CATE_GB, BRAND_GROUP_NO, CATE5_NO
           ) Z
           
	UNION ALL
	SELECT SITE_CD
         , CATE_GB
         , 'G009_20' AS FORMAL_GB
         , BRAND_GROUP_NO
         , CATE_NO
         , FLOOR(MIN_CURR_PRICE / 1000) * 1000                   AS PRICE1
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 1) / 1000) * 1000 AS PRICE2
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 2) / 1000) * 1000 AS PRICE3
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 3) / 1000) * 1000 AS PRICE4
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 4) / 1000) * 1000 AS PRICE5
         , FLOOR(MAX_CURR_PRICE / 1000) * 1000                   AS PRICE6
    FROM   (
            SELECT SITE_CD
                 , CATE_GB
                 , BRAND_GROUP_NO
                 , CATE1_NO        AS CATE_NO
                 , MIN(CURR_PRICE) AS MIN_CURR_PRICE                    /*최소현재판매가*/
                 , MAX(CURR_PRICE) AS MAX_CURR_PRICE                    /*최대현재판매가*/
                 , (MAX(CURR_PRICE) - MIN(CURR_PRICE)) / 5 AS SLICE_VAL /*분할값*/
            FROM   TAB_FILTER
            WHERE  CATE1_NO IS NOT NULL
            AND  FORMAL_GB = 'G009_20'
            GROUP  BY SITE_CD, CATE_GB, BRAND_GROUP_NO, CATE1_NO
           ) Z
    UNION ALL
    SELECT SITE_CD
         , CATE_GB
         , 'G009_20' AS FORMAL_GB
         , BRAND_GROUP_NO
         , CATE_NO
         , FLOOR(MIN_CURR_PRICE / 1000) * 1000                   AS PRICE1
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 1) / 1000) * 1000 AS PRICE2
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 2) / 1000) * 1000 AS PRICE3
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 3) / 1000) * 1000 AS PRICE4
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 4) / 1000) * 1000 AS PRICE5
         , FLOOR(MAX_CURR_PRICE / 1000) * 1000                   AS PRICE6
    FROM   (
            SELECT SITE_CD
                 , CATE_GB
                 , BRAND_GROUP_NO
                 , CATE2_NO        AS CATE_NO
                 , MIN(CURR_PRICE) AS MIN_CURR_PRICE                    /*최소현재판매가*/
                 , MAX(CURR_PRICE) AS MAX_CURR_PRICE                    /*최대현재판매가*/
                 , (MAX(CURR_PRICE) - MIN(CURR_PRICE)) / 5 AS SLICE_VAL /*분할값*/
            FROM   TAB_FILTER
            WHERE  CATE2_NO IS NOT NULL
            AND  FORMAL_GB = 'G009_20'
            GROUP  BY SITE_CD, CATE_GB, BRAND_GROUP_NO, CATE2_NO
           ) Z
    UNION ALL
    SELECT SITE_CD
         , CATE_GB
         , 'G009_20' AS FORMAL_GB
         , BRAND_GROUP_NO
         , CATE_NO
         , FLOOR(MIN_CURR_PRICE / 1000) * 1000                   AS PRICE1
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 1) / 1000) * 1000 AS PRICE2
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 2) / 1000) * 1000 AS PRICE3
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 3) / 1000) * 1000 AS PRICE4
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 4) / 1000) * 1000 AS PRICE5
         , FLOOR(MAX_CURR_PRICE / 1000) * 1000                   AS PRICE6
    FROM   (
            SELECT SITE_CD
                 , CATE_GB
                 , BRAND_GROUP_NO
                 , CATE3_NO        AS CATE_NO
                 , MIN(CURR_PRICE) AS MIN_CURR_PRICE                    /*최소현재판매가*/
                 , MAX(CURR_PRICE) AS MAX_CURR_PRICE                    /*최대현재판매가*/
                 , (MAX(CURR_PRICE) - MIN(CURR_PRICE)) / 5 AS SLICE_VAL /*분할값*/
            FROM   TAB_FILTER
            WHERE  CATE3_NO IS NOT NULL
            AND  FORMAL_GB = 'G009_20'
            GROUP  BY SITE_CD, CATE_GB, BRAND_GROUP_NO, CATE3_NO
           ) Z
    UNION ALL
    SELECT SITE_CD
         , CATE_GB
         , 'G009_20' AS FORMAL_GB
         , BRAND_GROUP_NO
         , CATE_NO
         , FLOOR(MIN_CURR_PRICE / 1000) * 1000                   AS PRICE1
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 1) / 1000) * 1000 AS PRICE2
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 2) / 1000) * 1000 AS PRICE3
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 3) / 1000) * 1000 AS PRICE4
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 4) / 1000) * 1000 AS PRICE5
         , FLOOR(MAX_CURR_PRICE / 1000) * 1000                   AS PRICE6
    FROM   (
            SELECT SITE_CD
                 , CATE_GB
                 , BRAND_GROUP_NO
                 , CATE4_NO        AS CATE_NO
                 , MIN(CURR_PRICE) AS MIN_CURR_PRICE                    /*최소현재판매가*/
                 , MAX(CURR_PRICE) AS MAX_CURR_PRICE                    /*최대현재판매가*/
                 , (MAX(CURR_PRICE) - MIN(CURR_PRICE)) / 5 AS SLICE_VAL /*분할값*/
            FROM   TAB_FILTER
            WHERE  CATE4_NO IS NOT NULL
            AND  FORMAL_GB = 'G009_20'
            GROUP  BY SITE_CD, CATE_GB, BRAND_GROUP_NO, CATE4_NO
           ) Z
    UNION ALL
    SELECT SITE_CD
         , CATE_GB
         , 'G009_20' AS FORMAL_GB
         , BRAND_GROUP_NO
         , CATE_NO
         , FLOOR(MIN_CURR_PRICE / 1000) * 1000                   AS PRICE1
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 1) / 1000) * 1000 AS PRICE2
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 2) / 1000) * 1000 AS PRICE3
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 3) / 1000) * 1000 AS PRICE4
         , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 4) / 1000) * 1000 AS PRICE5
         , FLOOR(MAX_CURR_PRICE / 1000) * 1000                   AS PRICE6
    FROM   (
            SELECT SITE_CD
                 , CATE_GB
                 , BRAND_GROUP_NO
                 , CATE5_NO        AS CATE_NO
                 , MIN(CURR_PRICE) AS MIN_CURR_PRICE                    /*최소현재판매가*/
                 , MAX(CURR_PRICE) AS MAX_CURR_PRICE                    /*최대현재판매가*/
                 , (MAX(CURR_PRICE) - MIN(CURR_PRICE)) / 5 AS SLICE_VAL /*분할값*/
            FROM   TAB_FILTER
            WHERE  CATE5_NO IS NOT NULL
            AND  FORMAL_GB = 'G009_20'
            GROUP  BY SITE_CD, CATE_GB, BRAND_GROUP_NO, CATE5_NO
           ) Z
)
SELECT DISTINCT
       SITE_CD
     , CATE_GB
     , FORMAL_GB
     , BRAND_GROUP_NO
     , CATE_NO
     , 'PRICE'        AS FILTER_GB
     , FILTER_CD
     , FILTER_NM
     , RANK() OVER(PARTITION BY SITE_CD, CATE_GB, FORMAL_GB, BRAND_GROUP_NO, CATE_NO
                   ORDER BY FILTER_CD
                   )  AS DISP_ORD
FROM   (
        SELECT SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE_NO
             , PRICE1         AS FILTER_CD
             , PRICE1         AS FILTER_NM
        FROM   TAB_PRICE
        UNION ALL
        SELECT SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE_NO
             , PRICE2         AS FILTER_CD
             , PRICE2         AS FILTER_NM
        FROM   TAB_PRICE
        UNION ALL
        SELECT SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE_NO
             , PRICE3         AS FILTER_CD
             , PRICE3         AS FILTER_NM
        FROM   TAB_PRICE
        UNION ALL
        SELECT SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE_NO
             , PRICE4         AS FILTER_CD
             , PRICE4         AS FILTER_NM
        FROM   TAB_PRICE
        UNION ALL
        SELECT SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE_NO
             , PRICE5         AS FILTER_CD
             , PRICE5         AS FILTER_NM
        FROM   TAB_PRICE
        UNION ALL
        SELECT SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE_NO
             , PRICE6         AS FILTER_CD
             , PRICE6         AS FILTER_NM
        FROM   TAB_PRICE
       ) Z
;


-- 연령대 필터 생성
INSERT INTO TB_CATE_FILTER_TMP (
       SITE_CD
     , CATE_GB
     , FORMAL_GB
     , BRAND_GROUP_NO
     , CATE_NO
     , FILTER_GB
     , FILTER_CD
     , FILTER_NM
     , DISP_ORD
)
WITH TAB_FILTER AS (
    SELECT C4.SITE_CD
         , C4.CATE_GB
         , G.FORMAL_GB
         , B.BRAND_GROUP_NO
         , C4.CATE1_NO
         , C4.CATE2_NO
         , C4.CATE3_NO
         , C4.CATE4_NO
         , C4.CATE5_NO
         , G.AGE_GRP_CD      AS FILTER_CD
    FROM   TB_CATE_4SRCH C4
         , TB_CATE_GOODS CG
         , TB_GOODS G
         , TB_GOODS_STOCK GS
         , TB_BRAND B
         , TB_SITE_BRAND SB
    WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
--     AND    CG.BRAND_GROUP_NO = 0
    AND    CG.GOODS_CD = G.GOODS_CD
    AND    CG.GOODS_CD = GS.GOODS_CD
    AND    G.BRAND_CD = B.BRAND_CD
    AND    B.BRAND_CD = SB.BRAND_CD
    AND    C4.SITE_CD = 'G000_10'
    AND    C4.CATE_GB = 'G032_101'
    AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
    AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
    AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
    AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*판매기간*/
    AND    G.AGE_GRP_CD IS NOT NULL
    AND    GS.STOCK_QTY > 0 /*재고있는상품*/
    AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
    AND    SB.SITE_CD = 'G000_10'
    AND    SB.USE_YN = 'Y' /*사이트에서 사용하는 브랜드*/
)
SELECT A.SITE_CD
     , A.CATE_GB
     , A.FORMAL_GB
     , A.BRAND_GROUP_NO
     , A.CATE_NO
     , 'AGE'          AS FILTER_GB
     , A.FILTER_CD
     , CC.CD_NM       AS FILTER_NM
     , RANK() OVER(PARTITION BY A.SITE_CD, A.CATE_GB, A.FORMAL_GB, A.BRAND_GROUP_NO, A.CATE_NO
                   ORDER BY CC.DISP_ORD
                  )   AS DISP_ORD
FROM   (
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE_NO
             , FILTER_CD
        FROM   (
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE5_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE5_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE4_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE4_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE3_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE3_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE2_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE2_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE1_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE1_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL 
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE5_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE5_NO IS NOT NULL
                AND  FORMAL_GB  = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE4_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE4_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE3_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE3_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE2_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE2_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE1_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE1_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
               ) Z
        WHERE  CATE_NO IS NOT NULL
       ) A
     , TB_COMMON_CODE CC
WHERE  A.FILTER_CD = CC.CD
AND    CC.CD_GB = 'G023'
AND    CC.USE_YN = 'Y'
;


-- 시즌 필터 생성
INSERT INTO TB_CATE_FILTER_TMP (
       SITE_CD
     , CATE_GB
     , FORMAL_GB
     , BRAND_GROUP_NO
     , CATE_NO
     , FILTER_GB
     , FILTER_CD
     , FILTER_NM
     , DISP_ORD
)
WITH TAB_FILTER AS (
    SELECT C4.SITE_CD
         , C4.CATE_GB
         , G.FORMAL_GB
         , B.BRAND_GROUP_NO
         , C4.CATE1_NO
         , C4.CATE2_NO
         , C4.CATE3_NO
         , C4.CATE4_NO
         , C4.CATE5_NO
         , G.SEASON_CD       AS FILTER_CD
    FROM   TB_CATE_4SRCH C4
         , TB_CATE_GOODS CG
         , TB_GOODS G
         , TB_GOODS_STOCK GS
         , TB_BRAND B
         , TB_SITE_BRAND SB
    WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
--     AND    CG.BRAND_GROUP_NO = 0
    AND    CG.GOODS_CD = G.GOODS_CD
    AND    CG.GOODS_CD = GS.GOODS_CD
    AND    G.BRAND_CD = B.BRAND_CD
    AND    B.BRAND_CD = SB.BRAND_CD
    AND    C4.SITE_CD = 'G000_10'
    AND    C4.CATE_GB = 'G032_101'
    AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
    AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
    AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
    AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*판매기간*/
    AND    GS.STOCK_QTY > 0 /*재고있는상품*/
    AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
    AND    SB.SITE_CD = 'G000_10'
    AND    SB.USE_YN = 'Y' /*사이트에서 사용하는 브랜드*/
)
SELECT A.SITE_CD
     , A.CATE_GB
     , A.FORMAL_GB
     , A.BRAND_GROUP_NO
     , A.CATE_NO
     , 'SEASON'         AS FILTER_GB
     , A.FILTER_CD
     , CC.CD_NM         AS FILTER_NM
     , RANK() OVER(PARTITION BY A.SITE_CD, A.CATE_GB, A.FORMAL_GB, A.BRAND_GROUP_NO, A.CATE_NO
                   ORDER BY CC.DISP_ORD
                  )   AS DISP_ORD
FROM   (
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE_NO
             , FILTER_CD
        FROM   (
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE5_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE5_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE4_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE4_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE3_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE3_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE2_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE2_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE1_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE1_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL 
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE5_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE5_NO IS NOT NULL
                AND  FORMAL_GB  = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE4_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE4_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE3_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE3_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE2_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE2_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE1_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE1_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
               ) Z
        WHERE  CATE_NO IS NOT NULL
       ) A
     , TB_COMMON_CODE CC
WHERE  A.FILTER_CD = CC.CD
AND    CC.CD_GB = 'G006'
AND    CC.USE_YN = 'Y'
;


-- 색상 필터 생성
INSERT INTO TB_CATE_FILTER_TMP (
       SITE_CD
     , CATE_GB
     , FORMAL_GB
     , BRAND_GROUP_NO
     , CATE_NO
     , FILTER_GB
     , FILTER_CD
     , FILTER_NM
     , DISP_ORD
)
WITH TAB_FILTER AS (
    SELECT C4.SITE_CD
         , C4.CATE_GB
         , G.FORMAL_GB
         , B.BRAND_GROUP_NO
         , C4.CATE1_NO
         , C4.CATE2_NO
         , C4.CATE3_NO
         , C4.CATE4_NO
         , C4.CATE5_NO
         , C.COLOR_GRP_CD    AS FILTER_CD
    FROM   TB_CATE_4SRCH C4
         , TB_CATE_GOODS CG
         , TB_GOODS G
         , TB_GOODS_STOCK GS
         , TB_OPTION O
         , TB_COLOR C
         , TB_BRAND B
         , TB_SITE_BRAND SB
    WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
--     AND    CG.BRAND_GROUP_NO = 0
    AND    CG.GOODS_CD = G.GOODS_CD
    AND    CG.GOODS_CD = GS.GOODS_CD
    AND    CG.GOODS_CD = O.GOODS_CD
    AND    O.OPT_CD1 = C.COLOR_CD
    AND    G.BRAND_CD = B.BRAND_CD
    AND    B.BRAND_CD = SB.BRAND_CD
    AND    C4.SITE_CD = 'G000_10'
    AND    C4.CATE_GB = 'G032_101'
    AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
    AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
    AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
    AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*판매기간*/
    AND    GS.STOCK_QTY > 0 /*재고있는상품*/
    AND    O.SOLDOUT_YN = 'N' /*품절이 아닌 옵션*/
    AND    O.DISP_YN = 'Y' /*노출하는 옵션*/
    AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
    AND    SB.SITE_CD = 'G000_10'
    AND    SB.USE_YN = 'Y' /*사이트에서 사용하는 브랜드*/
)
SELECT A.SITE_CD
     , A.CATE_GB
     , A.FORMAL_GB
     , A.BRAND_GROUP_NO
     , A.CATE_NO
     , 'COLOR'          AS FILTER_GB
     , A.FILTER_CD
     , CC.CD_DESC       AS FILTER_NM
     , RANK() OVER(PARTITION BY A.SITE_CD, A.CATE_GB, A.FORMAL_GB, A.BRAND_GROUP_NO, A.CATE_NO
                   ORDER BY CC.DISP_ORD
                  )   AS DISP_ORD
FROM   (
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE_NO
             , FILTER_CD
        FROM   (
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE5_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE5_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE4_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE4_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE3_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE3_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE2_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE2_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , 'G009_10' AS FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE1_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE1_NO IS NOT NULL
                AND  FORMAL_GB IN ('G009_10','G009_20')
                UNION ALL 
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE5_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE5_NO IS NOT NULL
                AND  FORMAL_GB  = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE4_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE4_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE3_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE3_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE2_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE2_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
                UNION ALL
                SELECT SITE_CD
                     , CATE_GB
                     , FORMAL_GB
                     , BRAND_GROUP_NO
                     , CATE1_NO       AS CATE_NO
                     , FILTER_CD
                FROM   TAB_FILTER
                WHERE  CATE1_NO IS NOT NULL
                AND  FORMAL_GB = 'G009_20'
               ) Z
        WHERE  CATE_NO IS NOT NULL
       ) A
     , TB_COMMON_CODE CC
WHERE  A.FILTER_CD = CC.CD
AND    CC.CD_GB = 'G072'
AND    CC.USE_YN = 'Y'
;


-- 혜택 필터 생성
INSERT INTO TB_CATE_FILTER_TMP (
       SITE_CD
     , CATE_GB
     , FORMAL_GB
     , BRAND_GROUP_NO
     , CATE_NO
     , FILTER_GB
     , FILTER_CD
     , FILTER_NM
     , DISP_ORD
)
WITH TAB_FILTER AS (
    SELECT C4.SITE_CD
         , C4.CATE_GB
         , G.FORMAL_GB
         , B.BRAND_GROUP_NO
         , C4.CATE1_NO
         , C4.CATE2_NO
         , C4.CATE3_NO
         , C4.CATE4_NO
         , C4.CATE5_NO
         , G.GOODS_CD
         , GB.BENEFIT_GB
    FROM   TB_CATE_4SRCH C4
         , TB_CATE_GOODS CG
         , TB_GOODS G
         , TB_GOODS_STOCK GS
         , TB_GOODS_BENEFIT GB
         , TB_BRAND B
         , TB_SITE_BRAND SB
    WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
--     AND    CG.BRAND_GROUP_NO = 0
    AND    CG.GOODS_CD = G.GOODS_CD
    AND    CG.GOODS_CD = GS.GOODS_CD
    AND    CG.GOODS_CD = GB.GOODS_CD
    AND    G.BRAND_CD = B.BRAND_CD
    AND    B.BRAND_CD = SB.BRAND_CD
    AND    C4.SITE_CD = 'G000_10'
    AND    C4.CATE_GB = 'G032_101'
    AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
    AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
    AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
    AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*판매기간*/
    AND    GS.STOCK_QTY > 0 /*재고있는상품*/
    AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
    AND    SB.SITE_CD = 'G000_10'
    AND    SB.USE_YN = 'Y' /*사이트에서 사용하는 브랜드*/
)
SELECT SITE_CD
     , CATE_GB
     , FORMAL_GB
     , BRAND_GROUP_NO
     , CATE_NO
     , 'BENEFIT'        AS FILTER_GB
     , FILTER_CD
     , CASE WHEN FILTER_CD = '10' THEN '쿠폰할인'
            WHEN FILTER_CD = '20' THEN '무료배송'
            WHEN FILTER_CD = '30' THEN '사은품'
            WHEN FILTER_CD = '40' THEN '신상'
			WHEN FILTER_CD = '50' THEN '총알배송'
       END              AS FILTER_NM
     , RANK() OVER(PARTITION BY SITE_CD, CATE_GB, FORMAL_GB, BRAND_GROUP_NO, CATE_NO
                   ORDER BY FILTER_CD
                  )   AS DISP_ORD
FROM   (
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , 'G009_10' AS FORMAL_GB
             , BRAND_GROUP_NO
             , CATE5_NO       AS CATE_NO
             , BENEFIT_GB     AS FILTER_CD
        FROM   TAB_FILTER
        WHERE  CATE5_NO IS NOT NULL
        AND  FORMAL_GB IN ('G009_10','G009_20')
        UNION ALL
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , 'G009_10' AS FORMAL_GB
             , BRAND_GROUP_NO
             , CATE4_NO       AS CATE_NO
             , BENEFIT_GB     AS FILTER_CD
        FROM   TAB_FILTER
        WHERE  CATE4_NO IS NOT NULL
        AND  FORMAL_GB IN ('G009_10','G009_20')
        UNION ALL
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , 'G009_10' AS FORMAL_GB
             , BRAND_GROUP_NO
             , CATE3_NO       AS CATE_NO
             , BENEFIT_GB     AS FILTER_CD
        FROM   TAB_FILTER
        WHERE  CATE3_NO IS NOT NULL
        AND  FORMAL_GB IN ('G009_10','G009_20')
        UNION ALL
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , 'G009_10' AS FORMAL_GB
             , BRAND_GROUP_NO
             , CATE2_NO       AS CATE_NO
             , BENEFIT_GB     AS FILTER_CD
        FROM   TAB_FILTER
        WHERE  CATE2_NO IS NOT NULL
        AND  FORMAL_GB IN ('G009_10','G009_20')
        UNION ALL
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , 'G009_10' AS FORMAL_GB
             , BRAND_GROUP_NO
             , CATE1_NO       AS CATE_NO
             , BENEFIT_GB     AS FILTER_CD
        FROM   TAB_FILTER
        WHERE  CATE1_NO IS NOT NULL
        AND  FORMAL_GB IN ('G009_10','G009_20')
        UNION ALL 
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE5_NO       AS CATE_NO
             , BENEFIT_GB     AS FILTER_CD
        FROM   TAB_FILTER
        WHERE  CATE5_NO IS NOT NULL
        AND  FORMAL_GB = 'G009_20'
        UNION ALL
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE4_NO       AS CATE_NO
             , BENEFIT_GB     AS FILTER_CD
        FROM   TAB_FILTER
        WHERE  CATE4_NO IS NOT NULL
        AND  FORMAL_GB = 'G009_20'
        UNION ALL
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE3_NO       AS CATE_NO
             , BENEFIT_GB     AS FILTER_CD
        FROM   TAB_FILTER
        WHERE  CATE3_NO IS NOT NULL
        AND  FORMAL_GB = 'G009_20'
        UNION ALL
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE2_NO       AS CATE_NO
             , BENEFIT_GB     AS FILTER_CD
        FROM   TAB_FILTER
        WHERE  CATE2_NO IS NOT NULL
        AND  FORMAL_GB = 'G009_20'
        UNION ALL
        SELECT DISTINCT
               SITE_CD
             , CATE_GB
             , FORMAL_GB
             , BRAND_GROUP_NO
             , CATE1_NO       AS CATE_NO
             , BENEFIT_GB     AS FILTER_CD
        FROM   TAB_FILTER
        WHERE  CATE1_NO IS NOT NULL
        AND  FORMAL_GB = 'G009_20'
       ) Z
;

TRUNCATE TABLE TB_CATE_FILTER;

INSERT INTO TB_CATE_FILTER
SELECT DISTINCT SITE_CD
    , CATE_GB
    , FORMAL_GB
    , BRAND_GROUP_NO
    , CATE_NO
    , FILTER_GB
    , FILTER_CD
    , FILTER_NM
    , DISP_ORD
    , 12 AS REG_NO
    , NOW() AS REG_DT
FROM   TB_CATE_FILTER_TMP
;




CREATE TABLE IF NOT EXISTS TB_CATE_FILTER2 LIKE TB_CATE_FILTER;
DROP  TABLE TB_CATE_FILTER2;