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

CREATE PROCEDURE CIPRCVAG
@P_USER_ID CHAR(8),
@P_CUTOFF_DATE VARCHAR(26),
@P_B1_LIMIT NUMERIC,
@P_B2_LIMIT NUMERIC,
@P_B3_LIMIT NUMERIC
AS
	DECLARE @V_B1_LIMIT NUMERIC
	DECLARE	@V_B2_LIMIT NUMERIC;
	DECLARE @V_B3_LIMIT NUMERIC
	DECLARE	@V_LANGUAGE_CD CHAR(3)
	DECLARE @V_CUTOFF_DATE DATETIME
	DECLARE	@V_RETURN_CD INT

	SELECT @V_RETURN_CD = 0

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

   IF   @P_CUTOFF_DATE IS NULL OR @P_CUTOFF_DATE = ' '
	SELECT @V_CUTOFF_DATE = getdate() - 7
   ELSE 
	SELECT @V_CUTOFF_DATE = CAST(@P_CUTOFF_DATE AS DATETIME)
	 
   IF @@ERROR <> 0
   BEGIN
	    SELECT @V_RETURN_CD = 1000	-- Set return code to be invalid datetime parameters
	    SELECT @V_CUTOFF_DATE = NULL 
   END

   IF   @P_CUTOFF_DATE IS NULL OR @P_CUTOFF_DATE = ' '
	SELECT @V_CUTOFF_DATE = getdate() - 7;
   ELSE 
	SELECT @V_CUTOFF_DATE = CAST(@P_CUTOFF_DATE AS DATETIME)
	 
   IF @@ERROR <> 0
   BEGIN
	SELECT @V_RETURN_CD = 1000	-- Set return code to be invalid datetime parameters
	SELECT @V_CUTOFF_DATE = NULL
   END

   IF @P_B1_LIMIT IS NULL
	SELECT @V_B1_LIMIT = 30
   ELSE 
	SELECT @V_B1_LIMIT = @P_B1_LIMIT

   IF @P_B2_LIMIT IS NULL
	SELECT @V_B2_LIMIT = 60
   ELSE 
	SELECT @V_B2_LIMIT = @P_B2_LIMIT

   IF @P_B3_LIMIT IS NULL
	SELECT @V_B3_LIMIT = 90
   ELSE 
	SELECT @V_B3_LIMIT = @P_B3_LIMIT

   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 @V_RETURN_CD = 0
   BEGIN
   	SELECT @V_RETURN_CD RETURN_CODE
	       ,CAST (NULL AS VARCHAR) MESSAGE_TEXT
	       ,@V_CUTOFF_DATE V_CUTOFF_DATE
	       ,@V_B1_LIMIT V_B1_LIMIT
	       ,@V_B2_LIMIT V_B2_LIMIT
	       ,@V_B3_LIMIT V_B3_LIMIT;
   END
   ELSE -- CHECK FOR THE GOOD ERROR CODE
   BEGIN
      	SELECT MESSAGE_NBR RETURN_CODE
	       ,MESSAGE_TEXT
	       ,CAST (NULL AS DATETIME) V_CUTOFF_DATE
	       ,CAST (NULL AS NUMERIC) V_B1_LIMIT
	       ,CAST (NULL AS NUMERIC) V_B2_LIMIT
	       ,CAST (NULL AS NUMERIC) V_B3_LIMIT
      	FROM    CI_MSG_L
	WHERE   MESSAGE_CAT_NBR = 30
	  	AND	  MESSAGE_NBR = @V_RETURN_CD
	  	AND	  LANGUAGE_CD = @V_LANGUAGE_CD;	  
    END -- Check for the good error code

RETURN
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
