DROP FUNCTION IF EXISTS FN_GET_GOODS_NM;

CREATE FUNCTION FN_GET_GOODS_NM
    (I_GOODS_NM           VARCHAR(200)
    ,I_GOODS_GB           VARCHAR(20)
    ,I_FOREIGN_BUY_YN     CHAR(1)
    ,I_PARALLEL_IMPORT_YN CHAR(1)
    ,I_ORDER_MADE_YN      CHAR(1)
    ) RETURNS VARCHAR(500) DETERMINISTIC
/******************************************************************************
/*
/* Module       : 상품명 조회
/* Program Name : FN_GET_GOODS_NM
/* Description  : 상품명을 반환한다.
/*
/*                Input  : I_GOODS_NM => 상품명
/*                         I_GOODS_GB => 상품구분
/*                         I_FOREIGN_BUY_YN => 해외구매대행여부
/*                         I_PARALLEL_IMPORT_YN => 병행수입여부
/*                         I_ORDER_MADE_YN => 주문제작여부
/*                OutPut : 코드명
/*
/* Program History
/*
/*----------------------------------------------------------------------------
/*   Date        CSR NO.             Name      Description
/*----------------------------------------------------------------------------
/* 2021-03-25                       gagamel    Initial Release
/******************************************************************************/

BEGIN

	DECLARE O_RET_VALUE VARCHAR(500);
	
	SELECT CONCAT(IF(IFNULL(I_GOODS_GB,'') IN ('G073_11','G073_15',''),'',IFNULL(CONCAT('[',FN_GET_CODE_NM('G073',I_GOODS_GB),']'),''))
	             ,IF(I_FOREIGN_BUY_YN = 'Y','[해외구매대행]','')
	             ,IF(I_PARALLEL_IMPORT_YN = 'Y','[병행수입]','')
	             ,IF(I_ORDER_MADE_YN = 'Y','[주문제작]','')
	             ,I_GOODS_NM) AS GOODS_FULL_NM
	INTO   O_RET_VALUE
	FROM   DUAL
	;
	
	RETURN O_RET_VALUE;

END;