-- ******************************************************************
-- *
-- * Copyright (c) 2000, 2007, Oracle. All rights reserved.
-- *
-- ******************************************************************
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'CIPCSESG' AND type = 'P')
   DROP PROCEDURE RPTUSER.CIPCSESG
GO

CREATE PROCEDURE CIPCSESG
@P_USER_ID CHAR(8),
@P_FROM_DT VARCHAR(26),
@P_TO_DT VARCHAR(26),
@P_CASE_STATUS_CD CHAR(25)
AS
	DECLARE @V_FROM_DT DATETIME
	DECLARE	@V_TO_DT DATETIME
	DECLARE	@V_LANGUAGE_CD CHAR(3)
	DECLARE	@V_CASE_TYPE_CD CHAR(12)
	DECLARE	@V_CASE_STATUS_CD CHAR(12)
	DECLARE	@V_RETURN_CD INT

	SELECT @V_RETURN_CD = 0

BEGIN
        SELECT  @V_LANGUAGE_CD=LANGUAGE_CD
        FROM    SC_USER
        WHERE   SC_USER.USER_ID = @P_USER_ID;
	IF @@ROWCOUNT = 0
      	BEGIN
	  SELECT @V_LANGUAGE_CD = 'ENG'
      	END

	IF   @P_TO_DT IS NULL OR @P_TO_DT = ' '
		SELECT @V_TO_DT = getDate();
	ELSE 
		SELECT @V_TO_DT = CAST(@P_TO_DT AS DATETIME);
	
	IF   @P_FROM_DT IS NULL OR @P_FROM_DT = ' '
	   	SELECT @V_FROM_DT = @V_TO_DT - 7
	ELSE 
		SELECT @V_FROM_DT = CAST(@P_FROM_DT AS DATETIME);

	IF @@ERROR <> 0
	BEGIN
               SELECT @V_RETURN_CD = 1000  	-- Set return code to be invalid datetime parameters
	       SELECT @V_TO_DT = NULL
	       SELECT @V_FROM_DT = NULL
	END

   	IF @V_RETURN_CD = 0
	BEGIN
        	IF @P_CASE_STATUS_CD IS NULL OR LEN(LTRIM(RTRIM(@P_CASE_STATUS_CD))) = 0
		BEGIN
			SELECT @V_LANGUAGE_CD = 'ENG'
			SELECT @V_RETURN_CD = 1101
		END
        	ELSE
		BEGIN
        		SELECT @V_CASE_TYPE_CD = LTRIM(RTRIM(SUBSTRING (@P_CASE_STATUS_CD, 1, CHARINDEX ('|', @P_CASE_STATUS_CD) - 1)))
         		SELECT @V_CASE_STATUS_CD = LTRIM(RTRIM(SUBSTRING (@P_CASE_STATUS_CD, CHARINDEX ('|', @P_CASE_STATUS_CD) + 1, DATALENGTH(@P_CASE_STATUS_CD))))

			IF @V_CASE_TYPE_CD IS NULL OR LEN(LTRIM(RTRIM(@V_CASE_TYPE_CD))) = 0
			BEGIN
				SELECT @V_LANGUAGE_CD = 'ENG'
		  		SELECT @V_RETURN_CD = 1100
			END
			ELSE IF @V_CASE_STATUS_CD IS NULL OR LEN(LTRIM(RTRIM(@V_CASE_STATUS_CD))) = 0
			BEGIN
		  		SELECT @V_LANGUAGE_CD = 'ENG'
		  		SELECT @V_RETURN_CD = 1101
			END
			ELSE
			BEGIN
	   			SELECT @V_RETURN_CD RETURN_CODE
	  		  	       ,CAST (NULL AS VARCHAR) MESSAGE_TEXT
			  	       ,@V_FROM_DT V_FROM_DT
			  	       ,@V_TO_DT V_TO_DT
				       ,A.DESCR CASE_TYPE 
				       ,B.STATUS_LBL CASE_STATUS
				FROM   CI_CASE_TYPE_L A 
				       ,CI_CASE_STATUS_L B 
				WHERE  A.CASE_TYPE_CD = @V_CASE_TYPE_CD
				       AND	B.CASE_TYPE_CD = @V_CASE_TYPE_CD
				       AND	B.CASE_STATUS_CD = @V_CASE_STATUS_CD
				       AND	A.LANGUAGE_CD = @V_LANGUAGE_CD
				       AND	B.LANGUAGE_CD = @V_LANGUAGE_CD;
			END
		END
	END

    	IF @V_RETURN_CD <> 0
	BEGIN
		SELECT  MESSAGE_NBR RETURN_CODE
	  		,MESSAGE_TEXT
			,CAST(@V_FROM_DT AS DATETIME) V_FROM_DT
			,CAST(@V_TO_DT AS DATETIME) V_TO_DT
	 		,CAST (NULL AS VARCHAR) CASE_TYPE 
			,CAST (NULL AS VARCHAR) CASE_STATUS
      		FROM    CI_MSG_L
	  	WHERE   MESSAGE_CAT_NBR = 30
	  		AND MESSAGE_NBR = @V_RETURN_CD
	  		AND LANGUAGE_CD = @V_LANGUAGE_CD;	  
	END
RETURN
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
