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

CREATE PROCEDURE RPTUSER.CICSESGS
@P_USER_ID CHAR(8),
@P_FROM_DT VARCHAR(10),
@P_TO_DT VARCHAR(10),
@P_CASE_STATUS_CD CHAR(30),
@P_B1_LIMIT NUMERIC,
@P_B2_LIMIT NUMERIC,
@P_B3_LIMIT NUMERIC,
@P_CASE_OWNER CHAR(8)
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_CASE_OWNER      CHAR(8)
   DECLARE   @V_FIELD_NAME      CHAR(20)
   DECLARE   @V_B1_LIMIT        NUMERIC
   DECLARE   @V_B2_LIMIT        NUMERIC
   DECLARE   @V_B3_LIMIT        NUMERIC
   DECLARE   @V_RETURN_CD       INT

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

BEGIN -- MAIN BLOCK

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

   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  -- ERROR: INVALID USER ID
      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_TO_DT IS NULL OR @P_TO_DT = ' '
            SET @V_TO_DT = GETDATE()    
         ELSE
            SET @V_TO_DT = CAST((@P_TO_DT + ' 23:59:59') AS DATETIME)     

         IF @P_FROM_DT IS NULL OR @P_FROM_DT = ' '
            SET @V_FROM_DT = GETDATE() - 7 
         ELSE
            SET @V_FROM_DT = CAST(@P_FROM_DT AS DATETIME) 
      
         IF @V_FROM_DT > @V_TO_DT
            SET @V_RETURN_CD = 1104
    
         IF @V_FROM_DT = NULL OR @V_TO_DT = NULL
            SET @V_RETURN_CD = 1000
      END   

/* 
 ***    Check if a Case Status was passed.
 ***    Afterwards, check if the Case Status exists.
 ***    If not, set the return code.
 */
   IF @V_RETURN_CD = 0 
      BEGIN
         IF @P_CASE_STATUS_CD IS NULL OR DATALENGTH(LTRIM(RTRIM(@P_CASE_STATUS_CD))) = 0
            BEGIN
	       SET @V_LANGUAGE_CD = 'ENG'
               SET @V_RETURN_CD = 1100 
            END
 
         ELSE
            BEGIN
               SET @V_CASE_TYPE_CD   = LTRIM(RTRIM(SUBSTRING(@P_CASE_STATUS_CD, 1, CHARINDEX('|', @P_CASE_STATUS_CD, 1) - 1)))
               SET @V_CASE_STATUS_CD = LTRIM(RTRIM(SUBSTRING(@P_CASE_STATUS_CD, CHARINDEX('|', @P_CASE_STATUS_CD, 1) + 1, DATALENGTH(@P_CASE_STATUS_CD))))

               SELECT @V_CASE_TYPE_CD = CASE_TYPE_CD
               FROM   CI_CASE_TYPE
               WHERE  CASE_TYPE_CD = @V_CASE_TYPE_CD

               IF @@ROWCOUNT = 0
                  BEGIN
                     SET @V_LANGUAGE_CD = 'ENG'
                     SET @V_RETURN_CD = 1100
                  END
               
               IF @V_RETURN_CD = 0
                  BEGIN  
                     SELECT @V_CASE_STATUS_CD = CASE_STATUS_CD
 	             FROM   CI_CASE_STATUS
		     WHERE  CASE_TYPE_CD = @V_CASE_TYPE_CD
  		            AND	CASE_STATUS_CD = @V_CASE_STATUS_CD
    
                     IF @@ROWCOUNT = 0
                        BEGIN
                           SET @V_LANGUAGE_CD = 'ENG'
                           SET @V_RETURN_CD = 1101
                        END
                  END 
            END
      END
   
/*
 ***    Check if the User ID entered exists
 ***    If not, set the return code.
 */

   IF @V_RETURN_CD = 0
      BEGIN
         IF @P_CASE_OWNER IS NULL OR DATALENGTH(LTRIM(RTRIM(@P_CASE_OWNER))) = 0
      	    SET @V_CASE_OWNER = NULL
     
         ELSE
            BEGIN
               SET @V_CASE_OWNER = @P_CASE_OWNER
               SELECT @V_CASE_OWNER = USER_ID
               FROM   SC_USER
               WHERE  USER_ID = @V_CASE_OWNER

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

/*
 ***    Perform defaulting of bufcket limits and check if
 ***    all three limits constitute valid bucket ranges  
 */

   IF @V_RETURN_CD = 0
      BEGIN
         IF @P_B1_LIMIT IS NULL 
            SET @V_B1_LIMIT = 30
         ELSE 
            SET @V_B1_LIMIT = @P_B1_LIMIT

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

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

         IF @V_B2_LIMIT <= @V_B1_LIMIT
            BEGIN
               SET @V_LANGUAGE_CD = 'ENG'
               SET @V_RETURN_CD = 1105
            END

        ELSE
           BEGIN
              IF @V_B3_LIMIT <= @V_B2_LIMIT
                 BEGIN
		    SET @V_LANGUAGE_CD = 'ENG'
	  	    SET @V_RETURN_CD = 1106
		 END
           END
      END  



   IF @V_RETURN_CD = 0
      BEGIN
         SELECT @V_RETURN_CD RETURN_CODE
                ,CAST (NULL AS VARCHAR) MESSAGE_TEXT
                ,A.CASE_ID
                ,B.CASE_TYPE_CD
		,B.DESCR
		,A.PER_ID
                ,ENTITY_NAME
		,F.LAST_NAME
		,F.FIRST_NAME
		,C.CASE_STATUS_CD
		,C.STATUS_LBL
		,C2.STATUS_LBL CURR_STATUS_LBL
		,A.CASE_COND_FLG
                ,G.DESCR COND_DESCR
                ,A.USER_ID
                ,E2.LOG_DTTM CREATE_DATE
		,E.LOG_DTTM STATUS_DATE
		,DATEDIFF(mi, E2.LOG_DTTM, E.LOG_DTTM)  ELAPSED_MINS
                ,(CASE WHEN (@V_B1_LIMIT - (DATEDIFF(dd, E2.LOG_DTTM, E.LOG_DTTM))) >=0 THEN 1
                       WHEN (@V_B2_LIMIT - (DATEDIFF(dd, E2.LOG_DTTM, E.LOG_DTTM))) >=0 THEN 2
                       WHEN (@V_B3_LIMIT - (DATEDIFF(dd, E2.LOG_DTTM, E.LOG_DTTM))) >=0 THEN 3
                  ELSE 4 
                  END) AGE_BUCKET
         FROM   CI_CASE A
                LEFT OUTER JOIN CI_PER_NAME D
                   ON A.PER_ID = D.PER_ID
                AND D.PRIM_NAME_SW = 'Y'
                LEFT OUTER JOIN SC_USER F
                   ON F.USER_ID = A.USER_ID
                LEFT OUTER JOIN CI_LOOKUP_VAL_L G
                   ON A.CASE_COND_FLG = G.FIELD_VALUE
                AND G.LANGUAGE_CD = @V_LANGUAGE_CD
                AND G.FIELD_NAME = @V_FIELD_NAME
                ,CI_CASE_TYPE_L B
                ,CI_CASE_STATUS_L C
                ,CI_CASE_STATUS_L C2
                ,CI_CASE_LOG E
                ,CI_CASE_LOG E2
         WHERE  B.CASE_TYPE_CD = A.CASE_TYPE_CD
                AND B.LANGUAGE_CD = @V_LANGUAGE_CD
                AND C2.CASE_TYPE_CD = A.CASE_TYPE_CD
                AND C2.CASE_STATUS_CD = A.CASE_STATUS_CD
                AND C2.LANGUAGE_CD = @V_LANGUAGE_CD
                AND E.CASE_ID = A.CASE_ID
                AND E.CASE_LOG_TYPE_FLG IN ('CASC','STAT')
                AND E.LOG_DTTM BETWEEN @V_FROM_DT AND @V_TO_DT
                AND E.CASE_TYPE_CD = @V_CASE_TYPE_CD
                AND E.CASE_STATUS_CD = @V_CASE_STATUS_CD
                AND C.CASE_TYPE_CD = E.CASE_TYPE_CD
                AND C.CASE_STATUS_CD = E.CASE_STATUS_CD
                AND C.LANGUAGE_CD = @V_LANGUAGE_CD
                AND E2.CASE_ID = A.CASE_ID
                AND E2.CASE_LOG_TYPE_FLG = 'CASC'
                AND (@V_CASE_OWNER IS NULL OR DATALENGTH(LTRIM(RTRIM(@V_CASE_OWNER))) = 0 OR ( A.USER_ID = @V_CASE_OWNER))
         ORDER BY 5,18,13 DESC
      END
  
   ELSE  
      BEGIN
         SELECT MESSAGE_NBR RETURN_CODE
         	,MESSAGE_TEXT
               	,CAST (NULL AS CHAR) CASE_ID
		,CAST (NULL AS CHAR) CASE_TYPE_CD
         	,CAST (NULL AS VARCHAR) DESCR
                ,CAST (NULL AS CHAR) PER_ID
                ,CAST (NULL AS VARCHAR) ENTITY_NAME
                ,CAST (NULL AS VARCHAR) LAST_NAME
                ,CAST (NULL AS VARCHAR) FIRST_NAME
       		,CAST (NULL AS CHAR) CASE_STATUS_CD
		,CAST (NULL AS VARCHAR) STATUS_LBL
		,CAST (NULL AS VARCHAR) CURR_STATUS_LBL
		,CAST (NULL AS CHAR) CASE_COND_FLG
		,CAST (NULL AS VARCHAR) COND_DESCR
                ,CAST (NULL AS CHAR) USER_ID
		,CAST (NULL AS DATETIME) CREATE_DATE
		,CAST (NULL AS DATETIME) STATUS_DATE
                ,CAST (NULL AS INTEGER) ELAPSED_MINS
                ,CAST (NULL AS INTEGER) AGE_BUCKET
         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
