style24.doc/산출물/4.구축/데이터처리/07.카테고리재고생성(TB_CATE_STOCK).sql

TRUNCATE TABLE TB_CATE_STOCK_TMP;
INSERT INTO TB_CATE_STOCK_TMP
SELECT B.SITE_CD
     , B.CATE1_NO
     , B.CATE2_NO
     , B.CATE3_NO
     , B.CATE4_NO
     , B.CATE5_NO
     , A.GOODS_CD
     , C.BRAND_GROUP_NO
     , C.FORMAL_GB
     , C.STOCK_QTY
FROM   TB_CATE_GOODS A
     , TB_CATE_4SRCH B
     , (
        SELECT G.GOODS_CD
             , B.BRAND_GROUP_NO
             , G.FORMAL_GB
             , SUM(S.CURR_STOCK_QTY - S.BASE_STOCK_QTY) AS STOCK_QTY
        FROM   TB_GOODS G USE INDEX (IX_GOODS_11)
             , VW_STOCK S
             , TB_BRAND B
        WHERE  G.GOODS_CD = S.GOODS_CD
        AND    G.BRAND_CD = B.BRAND_CD
        AND    G.GOODS_TYPE = 'G056_N' /*일반상품*/
        AND    G.GOODS_STAT = 'G008_90' /*승인된상품*/
        AND    G.SELF_MALL_YN = 'Y' /*자사몰에노출하는넘만*/
        AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*유효한판매기간*/
        AND    S.SOLDOUT_YN = 'N' /*품절이아닌넘*/
        AND    S.DISP_YN = 'Y' /*사이즈노출하는넘만*/
        AND    S.CURR_STOCK_QTY - S.BASE_STOCK_QTY > 0 /*재고있는넘만*/
        GROUP  BY G.GOODS_CD, B.BRAND_GROUP_NO, G.FORMAL_GB
        UNION ALL
        SELECT G.GOODS_CD
             , B.BRAND_GROUP_NO
             , G.FORMAL_GB
             , SUM(S.CURR_STOCK_QTY - S.BASE_STOCK_QTY) AS STOCK_QTY
        FROM   TB_GOODS G USE INDEX (IX_GOODS_11)
             , TB_GOODS_COMPOSE GC
             , VW_STOCK S
             , TB_BRAND B
        WHERE  G.GOODS_CD = GC.GOODS_CD
        AND    GC.COMPS_GOODS_CD = S.GOODS_CD
        AND    G.BRAND_CD = B.BRAND_CD
        AND    G.GOODS_TYPE != 'G056_N' /*일반상품이 아닌넘*/
        AND    G.GOODS_STAT = 'G008_90' /*승인된상품*/
        -- AND    G.SELF_GOODS_YN = 'Y' /*자사상품만(세트상품과 딜상품은 자사상품만)*/
        AND    G.SELF_MALL_YN = 'Y' /*자사몰에노출하는넘만*/
        AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*유효한판매기간*/
        AND    GC.USE_YN = 'Y' /*사용하는구성상품*/
        AND    S.SOLDOUT_YN = 'N' /*품절이아닌넘*/
        AND    S.DISP_YN = 'Y' /*사이즈노출하는넘만*/
        AND    S.CURR_STOCK_QTY - S.BASE_STOCK_QTY > 0 /*재고있는넘만*/
        GROUP  BY G.GOODS_CD, B.BRAND_GROUP_NO, G.FORMAL_GB
       ) C
WHERE  A.GOODS_CD = C.GOODS_CD
AND    A.CATE_NO = B.LEAF_CATE_NO
AND    B.CATE_TYPE = 'G031_10' /*상품분류카테고리*/
;

TRUNCATE TABLE TB_CATE_STOCK;
INSERT INTO TB_CATE_STOCK (
       SITE_CD
     , CATE_NO
     , BRAND_GROUP_NO
     , FORMAL_GB
     , STOCK_QTY
     , GOODS_QTY
     , REG_NO
     , REG_DT
)
SELECT SITE_CD
     , CATE_NO
     , BRAND_GROUP_NO
     , FORMAL_GB
     , STOCK_QTY
     , GOODS_QTY
     , 0              AS REG_NO
     , NOW()          AS REG_DT
FROM   (
        SELECT SITE_CD
             , CATE5_NO                 AS CATE_NO
             , BRAND_GROUP_NO
             , FORMAL_GB
             , SUM(STOCK_QTY)           AS STOCK_QTY
             , COUNT(DISTINCT GOODS_CD) AS GOODS_QTY
        FROM   TB_CATE_STOCK_TMP
        WHERE  CATE5_NO IS NOT NULL
        GROUP  BY SITE_CD, CATE5_NO, BRAND_GROUP_NO, FORMAL_GB
        HAVING SUM(STOCK_QTY) > 0
        UNION ALL
        SELECT SITE_CD
             , CATE4_NO                 AS CATE_NO
             , BRAND_GROUP_NO
             , FORMAL_GB
             , SUM(STOCK_QTY)           AS STOCK_QTY
             , COUNT(DISTINCT GOODS_CD) AS GOODS_QTY
        FROM   TB_CATE_STOCK_TMP
        WHERE  CATE4_NO IS NOT NULL
        GROUP  BY SITE_CD, CATE4_NO, BRAND_GROUP_NO, FORMAL_GB
        HAVING SUM(STOCK_QTY) > 0
        UNION ALL
        SELECT SITE_CD
             , CATE3_NO                 AS CATE_NO
             , BRAND_GROUP_NO
             , FORMAL_GB
             , SUM(STOCK_QTY)           AS STOCK_QTY
             , COUNT(DISTINCT GOODS_CD) AS GOODS_QTY
        FROM   TB_CATE_STOCK_TMP
        WHERE  CATE3_NO IS NOT NULL
        GROUP  BY SITE_CD, CATE3_NO, BRAND_GROUP_NO, FORMAL_GB
        HAVING SUM(STOCK_QTY) > 0
        UNION ALL
        SELECT SITE_CD
             , CATE2_NO                 AS CATE_NO
             , BRAND_GROUP_NO
             , FORMAL_GB
             , SUM(STOCK_QTY)           AS STOCK_QTY
             , COUNT(DISTINCT GOODS_CD) AS GOODS_QTY
        FROM   TB_CATE_STOCK_TMP
        WHERE  CATE2_NO IS NOT NULL
        GROUP  BY SITE_CD, CATE2_NO, BRAND_GROUP_NO, FORMAL_GB
        HAVING SUM(STOCK_QTY) > 0
        UNION ALL
        SELECT SITE_CD
             , CATE1_NO                 AS CATE_NO
             , BRAND_GROUP_NO
             , FORMAL_GB
             , SUM(STOCK_QTY)           AS STOCK_QTY
             , COUNT(DISTINCT GOODS_CD) AS GOODS_QTY
        FROM   TB_CATE_STOCK_TMP
        WHERE  CATE1_NO IS NOT NULL
        GROUP  BY SITE_CD, CATE1_NO, BRAND_GROUP_NO, FORMAL_GB
        HAVING SUM(STOCK_QTY) > 0
       ) Z
WHERE  CATE_NO IS NOT NULL
;

SELECT * FROM TB_CATE_STOCK;