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

DROP FUNCTION IF EXISTS FN_GET_BENEFIT_PRICE;
create FUNCTION FN_GET_BENEFIT_PRICE
	(I_SITE_CD        VARCHAR(20)
	,I_FRONT_GB       VARCHAR(20)
	,I_GOODS_CD       VARCHAR(20)
	,I_CURR_PRICE     int
	,I_USABLE_CUST_GB VARCHAR(20)
	,I_FLOOR_UNIT     VARCHAR(20)
    ,I_USABLE_CUST_GRADE  VARCHAR(20)
	) RETURNS int 
/******************************************************************************
/*
/* Module       : 혜택가
/* Program Name : fn_get_benefit_price
/* Description  : 상품의 현재판매가에 즉시할인상품쿠폰을 적용한 판매가를 반환한다.
/*
/*                Input  : i_site_cd => 사이트코드
/*                         i_front_gb => 프론트구분(P:PC, M:모바일)
/*                         i_goods_cd => 상품코드
/*                         i_curr_price => 현재판매가
/*                         i_usable_cust_gb => 사용가능고객구분(G100_00:전체, G100_10:일반회원, G100_20:임직원)
/*                         i_floor_unit => 절사단위(10:일원단위절사, 100:십원단위절사, 1000:백원단위절사)
/*                         i_usable_cust_grade => 사용가능고객등급구분(00:전체, 그외 등급코드)
/*                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 V_USABLE_CUST_GRADE 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;
    SET V_USABLE_CUST_GRADE = I_USABLE_CUST_GRADE;
   
   IF V_USABLE_CUST_GB IS NULL OR V_USABLE_CUST_GB = '' THEN 
   		SET V_USABLE_CUST_GB = 'G100_00';
   END IF;

   IF V_USABLE_CUST_GRADE IS NULL OR V_USABLE_CUST_GRADE = '' THEN 
   		SET V_USABLE_CUST_GRADE = '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
                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