
/*
exec SP_COMMON_SEARCH_CODE2 'STRATEGY_NUM', '', ''
exec SP_COMMON_SEARCH_CODE2 'INDICATOR', 'trade_status_ind', '10'
*/
/* 
-- =============================================
-- SQLINES LICENSE FOR EVALUATION USE ONLY

CREATE OR REPLACE PROCEDURE SP_COMMON_SEARCH_CODE2 (
	-- Add the parameters for the stored procedure here
	P_CODE_GBN			NVARCHAR2(20),
	P_SQL_CONDITION1	NVARCHAR(MAX),
	P_SQL_CONDITION2	NVARCHAR(MAX))
AS
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.

    -- Insert statements for procedure here
	
 V_SQL NVARCHAR(MAX);
 V_FIRST_SQL NVARCHAR(MAX);
 V_LAST_SQL NVARCHAR(MAX);
 V_WHERE_SQL1 NVARCHAR(MAX) := '';
 V_WHERE_SQL2 NVARCHAR(MAX) := '';
 V_PARAM_DEFIN NVARCHAR2(1000) := '';BEGIN

 */
 
 ---------------------------------------------------------------------------------------------------------------

	SELECT 
	  'STRATEGY_NUM'   AS CODE_GBN
	, STRATEGY_NUM     AS CODE
	, STRATEGY_NAME    AS CODE_NM
	, STRATEGY_NAME    AS ORDER_COL
	FROM ORG_STRATEGY
	WHERE 1 = 1
	  /* #{P_SQL_CONDITION1} NOT NULL 인 경우 */
	  AND  BOOK_CD  IN (
			SELECT REGEXP_SUBSTR(NVL(#{P_SQL_CONDITION1},''),'[^,]+', 1, LEVEL) FROM DUAL
			CONNECT BY REGEXP_SUBSTR(NVL(#{P_SQL_CONDITION1},''), '[^,]+', 1, LEVEL) IS NOT NULL )
	  /* #{P_SQL_CONDITION2} NOT NULL 인 경우 */
	  AND  PORTFOLIO_CD  IN (
			SELECT REGEXP_SUBSTR(NVL(#{P_SQL_CONDITION2},''),'[^,]+', 1, LEVEL) FROM DUAL
			CONNECT BY REGEXP_SUBSTR(NVL(#{P_SQL_CONDITION2},''), '[^,]+', 1, LEVEL) IS NOT NULL )
---------------------------------------------------------------------------------------------------------------


	SELECT
		  'INDICATOR'      AS CODE_GBN
		, IND_VALUE_NAME   AS VALUE
	FROM	CXL_PROD_DB.DBO.GEN_INDICATOR_VALUE
	WHERE 1=1
	  /* #{P_SQL_CONDITION1} NOT NULL 인 경우 */
	  AND IND_NAME  = #{P_SQL_CONDITION1}
	  /* #{P_SQL_CONDITION2} NOT NULL 인 경우 */
	  AND IND_VALUE = #{P_SQL_CONDITION2}
	 
---------------------------------------------------------------------------------------------------------------
