-- ******************************************************************
-- *
-- * 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 = 'CIPCLLSU' AND type = 'P')
   DROP PROCEDURE RPTUSER.CIPCLLSU
GO

CREATE PROCEDURE CIPCLLSU 
@P_USER_ID CHAR(8),
@P_FROM_DT VARCHAR(26),
@P_TO_DT VARCHAR(26)
AS

	DECLARE @V_FROM_DT DATETIME
	DECLARE @V_TO_DT DATETIME
	DECLARE @V_LANGUAGE_CD CHAR(3)
	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

/******************************************************************************
   Checking for nulls in parameters and setting default values for select 
   statements
******************************************************************************/

        BEGIN
		SELECT @V_FROM_DT = NULL;
		SELECT @V_TO_DT = NULL;
  	END

	IF   @P_FROM_DT IS NULL OR @P_FROM_DT = ' '
	   	SELECT @V_FROM_DT = DATEADD(month, -1, getDate())
	ELSE 
		SELECT @V_FROM_DT = CAST(@P_FROM_DT AS DATETIME);

	IF   @P_TO_DT IS NULL OR @P_TO_DT = ' '
		SELECT @V_TO_DT = DATEADD(month, 1, getDate())
	ELSE 
		SELECT @V_TO_DT = CAST(@P_TO_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
		SELECT  @V_RETURN_CD RETURN_CODE
	  		,CAST (NULL AS VARCHAR) MESSAGE_TEXT
			,@V_FROM_DT V_FROM_DT
			,@V_TO_DT V_TO_DT;
	END
   	ELSE
	BEGIN
	  	SELECT  MESSAGE_NBR RETURN_CODE
	  		,MESSAGE_TEXT
			,CAST (NULL AS DATETIME) V_FROM_DT
			,CAST (NULL AS DATETIME) V_TO_DT
      		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

