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



CREATE PROCEDURE CICSESTS
@P_USER_ID CHAR(8),
@P_FROM_DT VARCHAR(10),
@P_TO_DT VARCHAR(10),
@P_COND_FLG CHAR(4)

AS 
/******************************************************************************
   NAME:       CICSESTS 
   PURPOSE:    Supply report extract for Crystal Report - Case Statistics

******************************************************************************/
BEGIN

    DECLARE @V_FROM_DT       DATETIME;
    DECLARE @V_TO_DT         DATETIME;
    DECLARE @V_LANGUAGE_CD   CHAR(3);
    DECLARE @V_FIELD_NAME    CHAR(20);
    DECLARE @V_COND_FLG      CHAR(4);
    DECLARE @V_RETURN_CD     INTEGER;
    DECLARE @V_LOCATION      VARCHAR(10);

    SET @V_LANGUAGE_CD = 'ENG';
    SET @V_FIELD_NAME = 'CASE_COND_FLG';
    SET @V_RETURN_CD = 0;
    SET @V_LOCATION = '';

BEGIN -- Main

   SELECT @V_LANGUAGE_CD = LANGUAGE_CD
   FROM	SC_USER
   WHERE  SC_USER.USER_ID = @P_USER_ID;

   IF @@ROWCOUNT = 0 
   BEGIN
	  SET @V_LANGUAGE_CD = 'ENG';
	  SET @V_RETURN_CD = 1003; 
   END;

/*
 ***    Set default values for Start and End Dates if no value
 ***    were passed. Otherwise, check if values passed are
 ***    valid dates.If so, set the return code.
 ***
 ***    Afterwards, check if the Start Date is greater than the
 ***    End Date. If so, set the return code.  
 */

   IF @V_RETURN_CD = 0 
   BEGIN

      IF   @P_FROM_DT IS NULL OR @P_FROM_DT = ' ' 
          SET @V_FROM_DT = DATEADD ( DAY , -7, CONVERT(CHAR(10),GETDATE(),101) ) ;
      ELSE
          SET @V_FROM_DT = CONVERT(smalldatetime, @P_FROM_DT);
	 
      IF   @P_TO_DT IS NULL  OR @P_TO_DT = ' '
         SET @V_TO_DT = GETDATE();	
      ELSE 
         SET @V_TO_DT = CONVERT(datetime, @P_TO_DT + ' 23:59:59')
   
      IF @V_FROM_DT > @V_TO_DT 
	  SET @V_RETURN_CD = 1104;	

      IF @@ERROR > 0    
      BEGIN
          SET @V_RETURN_CD = 1000;	
          SET @V_TO_DT = NULL; 
          SET @V_FROM_DT = NULL; 
      END;

   END;

/*
 ***    Check if a Case Condition was passed.
 ***    Afterwards, check if the Case Condition exists.
 ***    If not, set the return code.
 */
   IF @V_RETURN_CD = 0
   BEGIN 
       IF @P_COND_FLG IS NULL OR LEN(LTRIM(RTRIM(@P_COND_FLG))) = 0 
           SET @V_COND_FLG = NULL;
       ELSE
       BEGIN
           SET @V_COND_FLG = @P_COND_FLG;

	   SELECT @V_COND_FLG = FIELD_VALUE
	   FROM   CI_LOOKUP_VAL_L
	   WHERE  FIELD_NAME = @V_FIELD_NAME
	     AND  FIELD_VALUE = @V_COND_FLG
	     AND  LANGUAGE_CD = @V_LANGUAGE_CD;

	   IF @@ROWCOUNT = 0
	   BEGIN
	       SET @V_LANGUAGE_CD = 'ENG';
	       SET @V_RETURN_CD = 1102; 
	   END;
       END;
   END;

   IF @V_RETURN_CD = 0
   BEGIN
      IF @V_COND_FLG IS NULL OR @V_COND_FLG = ' '
      BEGIN
          SELECT  @V_RETURN_CD RETURN_CODE
	   	 ,CAST(NULL AS VARCHAR) MESSAGE_TEXT
		 ,CT.DESCR CASE_TYPE
		 ,COUNT (CS.CASE_ID) CASE_CNT
		 ,CS.CASE_COND_FLG
		 ,LU.DESCR COND_DESCR
	  FROM  CI_CASE_TYPE_L CT, 
	        CI_CASE CS,
		CI_CASE_LOG CL,
		CI_LOOKUP_VAL_L	LU
          WHERE CL.CASE_LOG_TYPE_FLG = 'CASC'   
	     AND CL.LOG_DTTM BETWEEN @V_FROM_DT AND @V_TO_DT
	     AND CL.CASE_ID = CS.CASE_ID
	     AND CS.CASE_TYPE_CD = CT.CASE_TYPE_CD
	     AND CS.CASE_COND_FLG = 'OPEN'
	     AND CS.CASE_COND_FLG = LU.FIELD_VALUE
	     AND LU.LANGUAGE_CD = @V_LANGUAGE_CD
	     AND LU.FIELD_NAME = @V_FIELD_NAME
	     AND CT.LANGUAGE_CD = @V_LANGUAGE_CD
	     GROUP BY
	    	CT.DESCR,CS.CASE_COND_FLG, LU.DESCR
			
	UNION
   	  SELECT @V_RETURN_CD RETURN_CODE
	        ,CAST(NULL AS VARCHAR) MESSAGE_TEXT
		,CT.DESCR CASE_TYPE  
                ,COUNT (CS.CASE_ID) CASE_CNT
		,CS.CASE_COND_FLG
		,LU.DESCR COND_DESCR
	   FROM CI_CASE_TYPE_L CT, 
	 	CI_CASE CS,
		CI_CASE_LOG CL,
		CI_LOOKUP_VAL_L LU
          WHERE CL.CASE_LOG_TYPE_FLG = 'STAT'   
	     AND CL.LOG_DTTM BETWEEN @V_FROM_DT AND @V_TO_DT
	     AND CL.LOG_DTTM = (SELECT MAX(CL2.LOG_DTTM) 
		                  FROM CI_CASE_LOG CL2 
				  WHERE CL2.CASE_ID = CS.CASE_ID) 
	     AND CS.CASE_TYPE_CD = CT.CASE_TYPE_CD
	     AND CS.CASE_COND_FLG = 'CLSD'
	     AND CT.LANGUAGE_CD = @V_LANGUAGE_CD
             AND CS.CASE_COND_FLG = LU.FIELD_VALUE
	     AND LU.LANGUAGE_CD = @V_LANGUAGE_CD
	     AND LU.FIELD_NAME = @V_FIELD_NAME
	     GROUP BY
	    	CT.DESCR,CS.CASE_COND_FLG, LU.DESCR;
      END;
      
      IF @V_COND_FLG = 'OPEN' 
      BEGIN	     
   	 SELECT  @V_RETURN_CD RETURN_CODE
	  	,CAST(NULL AS VARCHAR) MESSAGE_TEXT
		,CT.DESCR    CASE_TYPE
		,COUNT (CS.CASE_ID)  CASE_CNT
		,CS.CASE_COND_FLG
		,LU.DESCR COND_DESCR
	  FROM CI_CASE_TYPE_L CT, 
               CI_CASE CS,
	       CI_CASE_LOG CL,
	       CI_LOOKUP_VAL_L LU
          WHERE CL.CASE_LOG_TYPE_FLG = 'CASC'   
	     AND CL.LOG_DTTM BETWEEN @V_FROM_DT AND @V_TO_DT
	     AND CL.CASE_ID = CS.CASE_ID
	     AND CS.CASE_TYPE_CD = CT.CASE_TYPE_CD
	     AND CS.CASE_COND_FLG = 'OPEN'
	     AND CT.LANGUAGE_CD = @V_LANGUAGE_CD
	     AND CS.CASE_COND_FLG = LU.FIELD_VALUE
	     AND LU.LANGUAGE_CD = @V_LANGUAGE_CD
	     AND LU.FIELD_NAME = @V_FIELD_NAME
	  GROUP BY
	    	CT.DESCR,CS.CASE_COND_FLG, LU.DESCR;
      END;
			
      IF @V_COND_FLG = 'CLSD' 
      BEGIN
   	 SELECT @V_RETURN_CD RETURN_CODE
	       ,CAST(NULL AS VARCHAR) MESSAGE_TEXT
	       ,CT.DESCR    CASE_TYPE
	       ,COUNT (CS.CASE_ID)  CASE_CNT
	       ,CS.CASE_COND_FLG
	       ,LU.DESCR COND_DESCR 
          FROM CI_CASE_TYPE_L CT, 
	       CI_CASE CS,
	       CI_CASE_LOG CL,
	       CI_LOOKUP_VAL_L LU
          WHERE  CL.CASE_LOG_TYPE_FLG = 'STAT'   
	     AND CL.LOG_DTTM BETWEEN @V_FROM_DT AND @V_TO_DT
	     AND CL.LOG_DTTM = (SELECT MAX(CL2.LOG_DTTM) 
		                        FROM CI_CASE_LOG CL2 
				        WHERE CL2.CASE_ID = CS.CASE_ID) 
	     AND CS.CASE_TYPE_CD = CT.CASE_TYPE_CD
	     AND CS.CASE_COND_FLG = 'CLSD'
	     AND CT.LANGUAGE_CD = @V_LANGUAGE_CD
	     AND CS.CASE_COND_FLG = LU.FIELD_VALUE
	     AND LU.LANGUAGE_CD = @V_LANGUAGE_CD
	     AND LU.FIELD_NAME = @V_FIELD_NAME
	 GROUP BY
	    	CT.DESCR,CS.CASE_COND_FLG, LU.DESCR;
      END;
   END;
   ELSE
   BEGIN

      SELECT  MESSAGE_NBR RETURN_CODE
	  		  ,MESSAGE_TEXT
	  		  ,CAST(NULL AS VARCHAR) CASE_TYPE
			  ,CAST (NULL AS INTEGER) CASE_CNT
			  ,CAST (NULL AS CHAR) CASE_COND_FLG
			  ,CAST (NULL AS VARCHAR) COND_DESCR
      FROM CI_MSG_L
      WHERE   MESSAGE_CAT_NBR = 30
	  AND	  MESSAGE_NBR = @V_RETURN_CD
	  AND	  LANGUAGE_CD = @V_LANGUAGE_CD;
	  
   END;

END;		-- main
END; -- Cicsests
