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

DROP FUNCTION IF EXISTS FN_GET_BENEFIT_PRICE;
DELIMITER $$
create FUNCTION FN_GET_BENEFIT_PRICE
	(I_FRONT_GB       VARCHAR(20)
	,I_GOODS_CD       VARCHAR(20)
	,I_CURR_PRICE     int
	,I_USABLE_CUST_GB VARCHAR(20)
	) RETURNS INT 
/******************************************************************************
/*
/* Module       : 즉시쿠폰 적용가
/* Program Name : fn_get_benefit_price
/* Description  : 상품의 현재판매가에 즉시할인상품쿠폰을 적용한 판매가를 반환한다.
/*
/*                Input  : i_front_gb => 프론트구분(P:PC, M:모바일, A:APP)
/*                         i_goods_cd => 상품코드
/*                         i_curr_price => 현재판매가
/*                         i_usable_cust_gb => 사용가능고객구분(G100_00:전체, G100_10:일반회원, G100_20:임직원)
/*                OutPut : 즉시할인판매가
/*
/* Program History
/*
/*----------------------------------------------------------------------------
/*   Date        CSR NO.             Name      Description
/*----------------------------------------------------------------------------
/* 2020-12-02                        eskim    Initial Release
/******************************************************************************/
BEGIN
    DECLARE O_RET_VALUE INT;
	DECLARE V_USABLE_CUST_GB VARCHAR(20);

    DECLARE CONTINUE HANDLER FOR NOT FOUND 
    BEGIN
	    SET O_RET_VALUE = I_CURR_PRICE;
	    RETURN O_RET_VALUE;
	END;

	SET O_RET_VALUE = 0;
    SET V_USABLE_CUST_GB = I_USABLE_CUST_GB;
   
   IF V_USABLE_CUST_GB IS NULL OR V_USABLE_CUST_GB = '' THEN 
   		SET V_USABLE_CUST_GB = 'G100_00';
   END IF;

   SELECT CASE WHEN I_USABLE_CUST_GB = '99' THEN I_CURR_PRICE
                WHEN I_FRONT_GB = 'P' AND I_USABLE_CUST_GB = 'G100_20'  THEN STAFF_PC_CURR_PRICE
                WHEN I_FRONT_GB = 'P' AND I_USABLE_CUST_GB <> 'G100_20' THEN PC_CURR_PRICE
                WHEN I_FRONT_GB = 'M' AND I_USABLE_CUST_GB = 'G100_20'  THEN STAFF_MO_CURR_PRICE
                WHEN I_FRONT_GB = 'M' AND I_USABLE_CUST_GB <> 'G100_20' THEN MO_CURR_PRICE
                WHEN I_FRONT_GB = 'A' AND I_USABLE_CUST_GB = 'G100_20'  THEN STAFF_APP_CURR_PRICE
                WHEN I_FRONT_GB = 'A' AND I_USABLE_CUST_GB <> 'G100_20' THEN APP_CURR_PRICE
                ELSE I_CURR_PRICE END
    INTO   O_RET_VALUE            
    FROM TB_GOODS_BENEFIT_PRICE
    WHERE GOODS_CD = I_GOODS_CD
    AND  CUST_GB = 'G100_00'
	;

	RETURN O_RET_VALUE;
END	$$
DELIMITER ;