SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'CICSEOPN' AND type = 'P')
   DROP PROCEDURE RPTUSER.CICSEOPN
GO



CREATE PROCEDURE RPTUSER.CICSEOPN
@P_USER_ID CHAR(8),
@P_FROM_DT VARCHAR(23),
@P_TO_DT VARCHAR(23),
@P_CASE_TYPE_CD CHAR(12),
@P_B1_LIMIT DECIMAL,
@P_B2_LIMIT DECIMAL,
@P_B3_LIMIT DECIMAL,
@P_CASE_OWNER CHAR(8)

AS

/******************************************************************************
   NAME:       CICSEOPN
   PURPOSE:    

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

DECLARE @V_FROM_DT DATETIME
DECLARE @V_TO_DT DATETIME
DECLARE @V_LANGUAGE_CD CHAR(3)
DECLARE @V_RETURN_CD DECIMAL(5)
DECLARE @V_CASE_TYPE_CD CHAR(12)
DECLARE @V_CASE_OWNER CHAR(8)
DECLARE @V_B1_LIMIT DECIMAL
DECLARE @V_B2_LIMIT DECIMAL
DECLARE @V_B3_LIMIT DECIMAL

SELECT @V_LANGUAGE_CD='ENG'
SELECT @V_RETURN_CD=0

			 
BEGIN	 -- MAIN BLOCK

    BEGIN
        SELECT @V_LANGUAGE_CD=LANGUAGE_CD
        FROM SC_USER
        WHERE SC_USER.USER_ID = @P_USER_ID
  
        IF (SELECT @@ROWCOUNT) = 0
            BEGIN
                SELECT @V_LANGUAGE_CD = 'ENG'
                SELECT @V_RETURN_CD = 1003
            END
    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 = ' '
                SELECT @V_TO_DT = GETDATE()
            ELSE
                SELECT @V_TO_DT = DATEADD(SECOND, -1, DATEADD(DAY, 1, CONVERT(DATETIME, @P_TO_DT, 120)))
				
            IF @P_FROM_DT IS NULL OR @P_FROM_DT = ' '
                SELECT @V_FROM_DT = CONVERT(CHAR(10),DATEADD(DAY, -7, CONVERT(CHAR(10),@V_TO_DT,101)),101)
            ELSE
                SELECT @V_FROM_DT = CONVERT(CHAR(10),@P_FROM_DT,101)

            IF @V_FROM_DT > @V_TO_DT
                SELECT @V_RETURN_CD = 1104

            IF (SELECT @@ERROR) <> 0
                BEGIN
                    SELECT @V_RETURN_CD = 1000
                    SELECT @V_TO_DT = NULL
                    SELECT @V_FROM_DT = NULL
                END
        END


/*
 ***    Check if the Case Type entered exists.
 ***    If not, set the return code.
 */
    IF @V_RETURN_CD = 0
        IF @P_CASE_TYPE_CD IS NULL OR DATALENGTH(LTRIM(RTRIM(@P_CASE_TYPE_CD))) = 0
            SELECT @V_CASE_TYPE_CD = NULL
        ELSE
            BEGIN
                SELECT @V_CASE_TYPE_CD = @P_CASE_TYPE_CD
                SELECT @V_CASE_TYPE_CD = CASE_TYPE_CD
                FROM   CI_CASE_TYPE
                WHERE  CASE_TYPE_CD = @V_CASE_TYPE_CD
                IF (SELECT @@ROWCOUNT) = 0
                    BEGIN
                        SELECT @V_LANGUAGE_CD = 'ENG'
                        SELECT @V_RETURN_CD = 1100 
                    END
            END


/*
 ***    Check if the User ID entered exists
 ***    If not, set the return code.
 */
    IF @V_RETURN_CD = 0
        IF @P_CASE_OWNER IS NULL OR DATALENGTH(LTRIM(RTRIM(@P_CASE_OWNER))) = 0
            SELECT @V_CASE_OWNER = NULL
        ELSE
            BEGIN
                SELECT @V_CASE_OWNER = @P_CASE_OWNER
                SELECT @V_CASE_OWNER=USER_ID
                FROM SC_USER
                WHERE USER_ID = @V_CASE_OWNER
                IF (SELECT @@ROWCOUNT) = 0
                    BEGIN
                        SELECT @V_LANGUAGE_CD = 'ENG'
                        SELECT @V_RETURN_CD = 1103
                    END
            END


/*
 ***    Perform defaulting of bucket limits and check if
 ***    all three limits constitute valid bucket ranges  
 */
    IF @V_RETURN_CD = 0
        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
		
        IF @V_B2_LIMIT <= @V_B1_LIMIT
            BEGIN
                SELECT @V_LANGUAGE_CD = 'ENG'
                SELECT @V_RETURN_CD = 1105
            END
        IF @V_B3_LIMIT <= @V_B2_LIMIT
            BEGIN
                SELECT @V_LANGUAGE_CD = 'ENG'
                SELECT @V_RETURN_CD = 1106
            END

    IF @V_RETURN_CD = 0

        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.STATUS_LBL,
            A.USER_ID,
            E.LOG_DTTM,
            DATEDIFF(DAY, (CONVERT(CHAR(10),E.LOG_DTTM,101)), (CONVERT(CHAR(10),GETDATE(),101))) AS AGE,
            CASE SIGN(@V_B1_LIMIT - (DATEDIFF(DAY, (CONVERT(CHAR(10),E.LOG_DTTM,101)), (CONVERT(CHAR(10),GETDATE(),101)))))
                WHEN 1 THEN 1
                WHEN 0 THEN 1
                ELSE CASE SIGN(@V_B2_LIMIT - (DATEDIFF(DAY, (CONVERT(CHAR(10),E.LOG_DTTM,101)), (CONVERT(CHAR(10),GETDATE(),101)))))
                    WHEN 1 THEN 2
                    WHEN 0 THEN 2
                    ELSE CASE SIGN(@V_B3_LIMIT - (DATEDIFF(DAY, (CONVERT(CHAR(10),E.LOG_DTTM,101)), (CONVERT(CHAR(10),GETDATE(),101)))))
                        WHEN 1 THEN 3 
                        WHEN 0 THEN 3
                        ELSE 4
                    END
                END
            END AS AGE_BUCKET
         FROM 
            CI_CASE A
                INNER JOIN CI_CASE_TYPE_L B
                    ON A.CASE_TYPE_CD = B.CASE_TYPE_CD
                INNER JOIN CI_CASE_STATUS_L C
                    ON A.CASE_TYPE_CD = C.CASE_TYPE_CD
                LEFT OUTER JOIN SC_USER F
                    ON A.USER_ID = F.USER_ID
                LEFT OUTER JOIN CI_PER_NAME D
                    ON A.PER_ID = D.PER_ID  
                INNER JOIN CI_CASE_LOG E
                    ON E.CASE_ID = A.CASE_ID
         WHERE 
            A.CASE_COND_FLG = 'OPEN'
            AND B.LANGUAGE_CD = @V_LANGUAGE_CD
            AND A.CASE_STATUS_CD = C.CASE_STATUS_CD
            AND C.LANGUAGE_CD = @V_LANGUAGE_CD
            AND D.PRIM_NAME_SW = 'Y'
            AND E.CASE_LOG_TYPE_FLG = 'CASC'
            AND E.LOG_DTTM BETWEEN @V_FROM_DT AND @V_TO_DT   
            AND (@V_CASE_TYPE_CD IS NULL OR ( A.CASE_TYPE_CD = @V_CASE_TYPE_CD))
            AND (@V_CASE_OWNER IS NULL OR ( A.USER_ID = @V_CASE_OWNER))
        ORDER BY B.DESCR, AGE

    ELSE

        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 VARCHAR) STATUS_LBL,
            CAST (NULL AS CHAR) USER_ID,
            CAST (NULL AS TIMESTAMP)  LOG_DTTM,
            CAST (NULL AS INTEGER)  AGE,
            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

