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

CREATE
    DEFINER = style@`%` FUNCTION FN_GET_FREEGIFT_GOODS_YN(I_GOODS_CD VARCHAR(20)) RETURNS CHAR DETERMINISTIC
    /******************************************************************************
    /*
    /* Module       : 상품 사은품 여부 조회
    /* Program Name : FN_GET_FREEGIFT_GOODS_YN
    /* Description  : 해당 상품 구매시 지급하는 사은품이 있는지 조회한다.
    /*
    /*                Input  : I_GOODS_CD => 상품코드
    /*                OutPut : 'Y' OR 'N'
    /*
    /* Program History
    /*
    /*----------------------------------------------------------------------------
    /*   Date        CSR NO.             Name      Description
    /*----------------------------------------------------------------------------
    /* 2021-04-08                        xodud1202    Initial Release
    /******************************************************************************/
BEGIN
    DECLARE O_RETURN_VAL CHAR(1);

    SELECT CASE WHEN COUNT(A.GOODS_CD) > 0 THEN 'Y'
                ELSE 'N' END INTO   O_RETURN_VAL
    FROM   (SELECT G.GOODS_CD
            FROM   TB_FREEGIFT F
            INNER  JOIN TB_FREEGIFT_SECTION FS
            ON     F.FREEGIFT_SQ = FS.FREEGIFT_SQ
            INNER  JOIN TB_FREEGIFT_GOODS FGA
            ON     F.FREEGIFT_SQ = FGA.FREEGIFT_SQ
            AND    FGA.GOODS_GB = 'G800_20'
            AND    FGA.TARGET_GB = 'G260_10'
            INNER  JOIN TB_GOODS G
            ON     FGA.TARGET_VAL = G.GOODS_CD
            LEFT   OUTER JOIN TB_FREEGIFT_GOODS NOT_APPLY
            ON     F.FREEGIFT_SQ = NOT_APPLY.FREEGIFT_SQ
            AND    G.GOODS_CD = NOT_APPLY.TARGET_VAL
            AND    NOT_APPLY.GOODS_GB = 'G800_30'
            AND    NOT_APPLY.TARGET_GB = 'G260_10'
            WHERE  NOT_APPLY.TARGET_VAL IS NULL
            AND    F.DEL_YN = 'N'
            AND    FS.DEL_YN = 'N'
            AND    FGA.DEL_YN = 'N'
            AND    NOW() BETWEEN F.FREEGIFT_STDT AND F.FREEGIFT_EDDT
            AND    F.SELF_YN = 'Y'
            AND    F.FREEGIFT_STAT = 'G232_11'
            AND    G.GOODS_CD = I_GOODS_CD) A
    ;

    IF O_RETURN_VAL = 'Y' THEN
        RETURN O_RETURN_VAL;
    END IF;

    SELECT CASE WHEN COUNT(A.GOODS_CD) > 0 THEN 'Y'
                ELSE 'N' END INTO   O_RETURN_VAL
    FROM   (SELECT G.GOODS_CD
            FROM   TB_FREEGIFT F
            INNER  JOIN TB_FREEGIFT_SECTION FS
            ON     F.FREEGIFT_SQ = FS.FREEGIFT_SQ
            INNER  JOIN TB_FREEGIFT_GOODS_APPLY BRAND_FGA
            ON     F.FREEGIFT_SQ = BRAND_FGA.FREEGIFT_SQ
            AND    BRAND_FGA.TARGET_GB = 'G260_12'          -- 브랜드
            INNER  JOIN TB_FREEGIFT_GOODS_APPLY COMPANY_FGA
            ON     F.FREEGIFT_SQ = COMPANY_FGA.FREEGIFT_SQ
            AND    COMPANY_FGA.TARGET_GB = 'G260_13'        -- 업체
            INNER  JOIN TB_GOODS G
            ON     BRAND_FGA.TARGET_VAL = G.BRAND_CD
            AND    COMPANY_FGA.TARGET_VAL = G.SUPPLY_COMP_CD
            LEFT   OUTER JOIN TB_FREEGIFT_GOODS NOT_APPLY
            ON     F.FREEGIFT_SQ = NOT_APPLY.FREEGIFT_SQ
            AND    G.GOODS_CD = NOT_APPLY.TARGET_VAL
            AND    NOT_APPLY.GOODS_GB = 'G800_30'
            WHERE  NOT_APPLY.TARGET_VAL IS NULL
            AND    F.DEL_YN = 'N'
            AND    FS.DEL_YN = 'N'
            AND    NOW() BETWEEN F.FREEGIFT_STDT AND F.FREEGIFT_EDDT
            AND    F.SELF_YN = 'Y'
            AND    F.FREEGIFT_STAT = 'G232_11'
            AND    G.GOODS_CD = I_GOODS_CD) A
    ;

    RETURN O_RETURN_VAL;

END;