style24.doc/산출물/3.설계/05.DB-Object/03.db_function/FN_GET_APPLY_CPN1_ID.sql

CREATE DEFINER=`style`@`%` FUNCTION `style`.`FN_GET_APPLY_CPN1_ID`(I_GOODS_CD varchar(50), I_FRONT_GB char) RETURNS int
BEGIN
    DECLARE O_RET_VALUE INT;
    DECLARE O_CURR_PRICE INT;
    DECLARE O_DC_VAL INT;

    SET O_RET_VALUE = 0;
    SET O_DC_VAL = 0;

    SELECT CURR_PRICE
    INTO   O_CURR_PRICE -- 상품금액
    FROM   TB_GOODS
    WHERE  GOODS_CD = I_GOODS_CD
    ;

    WITH GOODS_DATA AS (
        SELECT G.GOODS_CD
             , G.CURR_PRICE
             , G.BRAND_CD
             , G.SUPPLY_COMP_CD
        FROM   TB_GOODS G
        WHERE  1=1
        AND    G.GOODS_CD = I_GOODS_CD
    )
    SELECT Y.CPN_ID
    INTO   O_RET_VALUE
    FROM (
        SELECT CP.CPN_ID
             , I_FRONT_GB AS FRONT_GB
             , CASE WHEN CP.DC_WAY IN ('G240_11')
                    THEN CASE WHEN CP.MAX_DC_AMT > (Z.CURR_PRICE * CP.DC_PVAL) / 100
                              THEN (Z.CURR_PRICE * CP.DC_PVAL) / 100
                              ELSE CP.MAX_DC_AMT
                              END
                    ELSE CP.DC_PVAL
                    END  DC_PVAL
             , CASE WHEN CP.DC_WAY IN ('G240_11')
                    THEN CASE WHEN CP.MAX_DC_AMT > (Z.CURR_PRICE * CP.DC_MVAL) / 100
                              THEN (Z.CURR_PRICE * CP.DC_MVAL) / 100
                              ELSE CP.MAX_DC_AMT
                              END
                    ELSE CP.DC_MVAL
                    END  DC_MVAL
             , CASE WHEN CP.DC_WAY IN ('G240_11')
                    THEN CASE WHEN CP.MAX_DC_AMT > (Z.CURR_PRICE * CP.DC_AVAL) / 100
                              THEN (Z.CURR_PRICE * CP.DC_AVAL) / 100
                              ELSE CP.MAX_DC_AMT
                              END
                    ELSE CP.DC_AVAL
                    END  DC_AVAL
        FROM   TB_COUPON CP
        INNER  JOIN TB_COUPON_CUST_GBN CGB
        ON     CP.CPN_ID = CGB.CPN_ID
        AND    CGB.USABLE_CUST_GB IN ('G100_10') -- 일반회원
        INNER  JOIN (
            SELECT X.*
            FROM (
                SELECT CR.CPN_ID
                     , GD.GOODS_CD
                     , GD.CURR_PRICE
                FROM   TB_COUPON_REFVAL CR
                INNER  JOIN GOODS_DATA GD
                ON     GD.GOODS_CD = CR.REF_VAL
                WHERE  1=1
                AND    CR.CPN_TARGET = 'G260_10' -- 상품
                AND    CR.DEL_YN = 'N'
                UNION
                SELECT CR.CPN_ID
                     , GD.GOODS_CD
                     , GD.CURR_PRICE
                FROM   (
                    SELECT C4.LEAF_CATE_NO
                         , CASE WHEN X = 1 THEN 'CATE1_NO'
                                WHEN X = 2 THEN 'CATE2_NO'
                                WHEN X = 3 THEN 'CATE3_NO'
                                WHEN X = 4 THEN 'CATE4_NO'
                                ELSE 'CATE5_NO'
                                END  CATE_LVL
                         , CASE WHEN X = 1 THEN CATE1_NO
                                WHEN X = 2 THEN CATE2_NO
                                WHEN X = 3 THEN CATE3_NO
                                WHEN X = 4 THEN CATE4_NO
                                ELSE CATE5_NO
                                END  CATE_NO
                    FROM   TB_CATE_4SRCH C4
                    LEFT   OUTER JOIN (
                        SELECT 1 AS X
                        UNION  ALL
                        SELECT 2 AS X
                        UNION  ALL
                        SELECT 3 AS X
                        UNION  ALL
                        SELECT 4 AS X
                        UNION  ALL
                        SELECT 5 AS X
                    ) B
                    ON     1=1
                    WHERE  1=1
                    AND    C4.SITE_CD = 'G000_10'
                    AND    C4.CATE_TYPE = 'G031_10'
                    ORDER  BY LEAF_CATE_NO
                         , CATE_LVL
                ) C4SRCH
                INNER  JOIN TB_COUPON_REFVAL CR
                ON     C4SRCH.CATE_NO = CR.REF_VAL
                INNER  JOIN TB_CATE_GOODS CG
                ON     C4SRCH.LEAF_CATE_NO = CG.CATE_NO
                INNER  JOIN GOODS_DATA GD
                ON     CG.GOODS_CD = GD.GOODS_CD
                WHERE  1=1
                AND    C4SRCH.CATE_NO IS NOT NULL
                AND    CR.CPN_TARGET = 'G260_11' -- 카테고리
                AND    CR.DEL_YN = 'N'
                UNION
                SELECT CR1.CPN_ID
                     , GD.GOODS_CD
                     , GD.CURR_PRICE
                FROM   GOODS_DATA GD
                INNER  JOIN TB_COUPON_REFVAL CR1
                ON     GD.BRAND_CD = CR1.REF_VAL
                AND    CR1.CPN_TARGET = 'G260_12' -- 브랜드
                AND    CR1.DEL_YN = 'N'
                UNION
                SELECT CR2.CPN_ID
                     , GD.GOODS_CD
                     , GD.CURR_PRICE
                FROM   GOODS_DATA GD
                INNER  JOIN TB_COUPON_REFVAL CR2
                ON     GD.SUPPLY_COMP_CD = CR2.REF_VAL
                AND    CR2.CPN_TARGET = 'G260_13' -- 공급업체
                AND    CR2.DEL_YN = 'N'
            ) X
            LEFT   OUTER JOIN TB_COUPON_REFVAL CR
            ON     X.CPN_ID = CR.CPN_ID
            AND    X.GOODS_CD = CR.REF_VAL
            AND    CR.CPN_TARGET = 'G260_14' -- 쿠폰제외상품
            AND    CR.DEL_YN = 'N'
            LEFT   OUTER JOIN TB_COUPON_BAN_GOODS CBG -- 전체제외상품
            ON     X.GOODS_CD = CBG.GOODS_CD
            AND    CBG.DEL_YN = 'N'
            WHERE  1=1
            AND    CR.CPN_ID IS NULL
            AND    CBG.CPN_BAN_GOODS_SQ IS NULL
        ) Z
        ON     Z.CPN_ID = CP.CPN_ID
        WHERE  1=1
        AND    CP.SITE_CD = 'G000_10' -- 자사몰
        AND    CP.CPN_TYPE = 'G230_10' -- 즉시할인
        AND    CP.CPN_STAT = 'G232_11' -- 진행중
        AND    CP.AVAIL_STDT < CURRENT_TIMESTAMP
        AND    CP.AVAIL_EDDT > CURRENT_TIMESTAMP
        ORDER  BY (CASE WHEN I_FRONT_GB IN ('P') THEN CP.DC_PVAL
                        WHEN I_FRONT_GB IN ('M') THEN CP.DC_MVAL
                        WHEN I_FRONT_GB IN ('A') THEN CP.DC_AVAL
                        END) DESC
    ) Y
    LIMIT   1
    ;

    RETURN O_RET_VALUE;
END