-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
/*
exec SP_COMMON_SEARCH_CODE 'COST_TYPE', ''
exec SP_COMMON_SEARCH_CODE 'INTERFACE_VALUE_CODE', ''
exec SP_COMMON_SEARCH_CODE 'COMPANY_NUM', ''
exec SP_COMMON_SEARCH_CODE 'QUOTE_CD', 'FWD_FO_180_3.5%_FOB_SINGAPORE_'
exec SP_COMMON_SEARCH_CODE 'BOOK_CD', ''
exec SP_COMMON_SEARCH_CODE 'BOOK_CD_NM', ''
exec SP_COMMON_SEARCH_CODE 'LOCATION_NM', ''
exec SP_COMMON_SEARCH_CODE 'INTRA_DATE', ''
exec SP_COMMON_SEARCH_CODE 'PORTFOLIO_CD', 'ASIA FO,ASIA LD,ASIA MD'
exec SP_COMMON_SEARCH_CODE 'TRADER', 'TRADER'
exec SP_COMMON_SEARCH_CODE 'CXL_USER_INC_INACT', ''
exec SP_COMMON_SEARCH_CODE 'COMMODITY', ''
exec SP_COMMON_SEARCH_CODE 'MKT_SNAPSHOT', ''
exec SP_COMMON_SEARCH_CODE '', ''
*/
-- =============================================
-- SQLINES LICENSE FOR EVALUATION USE ONLY
/* 
CREATE OR REPLACE PROCEDURE SP_COMMON_SEARCH_CODE (  
	-- Add the parameters for the stored procedure here
	P_CODE_GBN			NVARCHAR2(20),
	P_SQL_CONDITION	NVARCHAR(MAX))
AS
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
 V_SQL NVARCHAR(MAX);
 V_FIRST_SQL NVARCHAR(MAX);
 V_LAST_SQL NVARCHAR(MAX) := '';
 V_WHERE_SQL NVARCHAR(MAX) := '';
 V_PARAM_DEFIN NVARCHAR2(1000);
BEGIN
 */

/*     -- Insert statements for procedure here
    IF P_CODE_GBN = 'COST_TYPE' 
    THEN
		V_FIRST_SQL :=
		' */
	SELECT
		  'COST_TYPE'    AS CODE_GBN
		, COST_TYPE_CD   AS CODE
		, COST_TYPE_NAME AS CODE_NM
		, COST_TYPE_NAME AS ORDER_COL
	FROM REF_COST_TYPE
	UNION ALL
	SELECT
		  'INTERFACE_VALUE_CODE'    AS CODE_GBN
		, INTERFACE_VALUE_CODE AS CODE
		, INTERFACE_VALUE_CODE AS CODE_NM
		, INTERFACE_VALUE_CODE AS ORDER_COL
	FROM REF_PAYMENT_TERM
	GROUP BY INTERFACE_VALUE_CODE
	UNION ALL
	SELECT
		  'COMPANY_NUM'		AS CODE_GBN
		, COMPANY_NUM 		AS CODE
		, COMPANY_CD 		AS CODE_NM
		, COMPANY_CD 		AS ORDER_COL
	FROM REF_COMPANY
	WHERE COMPANY_TYPE_IND = '0'
	/* P_SQL_CONDITION 값이 있는 경우 */
	  AND COMPANY_NUM IN (
			SELECT REGEXP_SUBSTR(NVL(#{P_SQL_CONDITION},''),'[^,]+', 1, LEVEL) FROM DUAL
			CONNECT BY REGEXP_SUBSTR(NVL(#{P_SQL_CONDITION},''), '[^,]+', 1, LEVEL) IS NOT NULL )
	UNION ALL
	SELECT 
	  'QUOTE_CD'		AS CODE_GBN
	, QUOTE_DEF_CD      AS CODE
	, QUOTE_DEF_CD      AS CODE_NM
	, QUOTE_DEF_CD 		AS ORDER_COL
	FROM MKT_QUOTE_DEFINITION
	UNION ALL
	SELECT 
	  'BOOK_CD'		    AS CODE_GBN
	, BOOK_CD           AS CODE
	, BOOK_CD           AS CODE_NM
	, BOOK_CD 		    AS ORDER_COL
	FROM ORG_STRATEGY
	WHERE NVL(BOOK_CD, '') <> ''
	GROUP BY BOOK_CD
	UNION ALL
	SELECT 
	  'INTRA_DATE'    AS CODE_GBN
	, MAX(CONVERT(NVARCHAR(10), COB_DT, 23)) AS CODE
	, MKT_SNAPSHOT_CD AS CODE_NM
	, MKT_SNAPSHOT_CD AS ORDER_COL
	FROM EOD_LAST_RUN
	WHERE 1=1
	GROUP BY MKT_SNAPSHOT_CD
	UNION ALL
	SELECT 
	  'PORTFOLIO_CD'  AS CODE_GBN
	, PORTFOLIO_CD    AS CODE
	, PORTFOLIO_CD    AS CODE_NM
	, PORTFOLIO_CD    AS ORDER_COL
	FROM ORG_STRATEGY
	WHERE NVL(PORTFOLIO_CD, '') <> ''
	GROUP BY PORTFOLIO_CD
	UNION ALL
	SELECT 
	  'BOOK_CD_NM'  AS CODE_GBN
	, BOOK_CD       AS CODE
	, BOOK_NAME     AS CODE_NM
	, BOOK_CD       AS ORDER_COL
	FROM ORG_BOOK
	UNION ALL
	SELECT
		  'MKT_SNAPSHOT'		  AS CODE_GBN
		, MKT_SNAPSHOT_CD		  AS CODE
		, MKT_SNAPSHOT_NAME		  AS CODE_NM
		, MKT_SNAPSHOT_NAME       AS ORDER_COL
	FROM MKT_SNAPSHOT
	WHERE 1=1
	  AND STATUS_IND = '1'
	
	ORDER BY CODE_GBN, ORDER_COL

---------------------------------------------------------------------------------------------------------------

	SELECT
		  'LOCATION_NM'  AS CODE_GBN
		, LOCATION       AS CODE_NM
	FROM ETRM_MIDDLEWARE.DBO.MD_LOCATION
	ORDER BY LOCATION
		 
	SELECT DISTINCT
		  'PLANT_NM'  AS CODE_GBN
		,  VAL_LEV AS PLANT_NM
	FROM ETRM_MIDDLEWARE.DBO.STORAGE_VAL
	ORDER BY PLANT_NM
---------------------------------------------------------------------------------------------------------------

	SELECT 
		  'CXL_USER_INC_INACT'                 AS CODE_GBN
		, (LAST_NAME + SPACE(1) + FIRST_NAME)  AS TRADER_NM
		, PERSON_NUM                           AS TRADER_NUM
		, LOGIN_CD                             AS LOGIN_CD
	FROM CXL_PROD_DB.DBO.REF_PERSON
	WHERE 1 = 1
	AND	LEFT(LOGIN_CD, 7) NOT IN ('SUPPORT')
	AND LEFT(LOGIN_CD, 5) NOT IN ('ADMIN')
	AND PERSON_NUM <> '4'
	ORDER BY LAST_NAME

---------------------------------------------------------------------------------------------------------------

	SELECT
		  'TRADER'                       AS CODE_GBN
		, (LAST_NAME + ' ' + FIRST_NAME) AS TRADER_NM
		, PERSON_NUM                     AS TRADER_NUM
		, LOGIN_CD                       AS LOGIN_CD
	FROM CXL_PROD_DB.DBO.REF_PERSON
	WHERE 1 = 1
	AND STATUS_IND = '1'
	AND	LEFT(LOGIN_CD, 2) IN ('SA', 'AG', 'SI')
	AND LOGIN_CD != 'SAPINTERFACE' 
	AND JOB_TITLE_IND = CASE WHEN  NVL(#{P_SQL_CONDITION},'') = ''          THEN '1'
							 WHEN  NVL(#{P_SQL_CONDITION},'') = 'TRADER'    THEN '1'
							 WHEN  NVL(#{P_SQL_CONDITION},'') = 'CXL_USER'  THEN JOB_TITLE_IND
							 WHEN  NVL(#{P_SQL_CONDITION},'') = 'SCHEDULER' THEN '3'
						ELSE JOB_TITLE_IND
						END
---------------------------------------------------------------------------------------------------------------

	SELECT
		  'COMMODITY'		  AS CODE_GBN
		, CMDTY_CD            AS CMDTY_CD
		, CMDTY_NAME		  AS CMDTY_NM
	FROM CXL_PROD_DB.DBO.REF_COMMODITY
	WHERE	1=1
	AND STATUS_IND = '1'
	ORDER BY CMDTY_CD

/