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



CREATE PROCEDURE RPTUSER.CITDENTR
@P_USER_ID CHAR(8),
@P_ENTRY_STATUS_FLG CHAR(4),
@P_TD_TYPE_CD CHAR(8)
AS

/******************************************************************************
   NAME:       CITDENTR
   PURPOSE:    Supply report extract for Crystal Report - To Do Entry

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

DECLARE @V_LANGUAGE_CD CHAR(3)
DECLARE @V_RETURN_CD   DECIMAL(5)
DECLARE @V_ENTRY_STATUS_FLG CHAR (4)
DECLARE @V_TD_TYPE_CD  CHAR (8)
DECLARE @V_AGE_DAYS_1 CHAR(3)
DECLARE @V_AGE_DAYS_2 CHAR(3)

SELECT @V_LANGUAGE_CD = 'ENG'
SELECT @V_RETURN_CD = 0
SELECT @V_ENTRY_STATUS_FLG = NULL
SELECT @V_TD_TYPE_CD = NULL
SELECT @V_AGE_DAYS_1 = ''
SELECT @V_AGE_DAYS_2 = ''

			 
BEGIN	 -- MAIN BLOCK
   
    BEGIN
        SELECT 
            @V_LANGUAGE_CD=LANGUAGE_CD,
            @V_AGE_DAYS_1=CAST (TD_ENTRY_AGE_DAYS1 AS CHAR),
            @V_AGE_DAYS_2=CAST (TD_ENTRY_AGE_DAYS2 AS CHAR)
        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

    IF @P_TD_TYPE_CD IS NULL OR @P_TD_TYPE_CD = ' '
   	  SELECT @V_TD_TYPE_CD = NULL
    ELSE
        BEGIN
            SELECT @V_TD_TYPE_CD=TD_TYPE_CD
            FROM   CI_TD_TYPE
            WHERE  TD_TYPE_CD = @P_TD_TYPE_CD
            IF (SELECT @@ROWCOUNT) = 0
                BEGIN
                    SELECT @V_LANGUAGE_CD = 'ENG'
                    SELECT @V_RETURN_CD = 1012
                END
        END


    IF @P_ENTRY_STATUS_FLG IS NULL OR @P_ENTRY_STATUS_FLG = ' '
        SELECT @V_ENTRY_STATUS_FLG = NULL
    ELSE
        BEGIN
            SELECT @V_ENTRY_STATUS_FLG = FIELD_VALUE
            FROM CI_LOOKUP_VAL_L
            WHERE  
                FIELD_NAME = 'ENTRY_STATUS_FLG'
                AND FIELD_VALUE = @P_ENTRY_STATUS_FLG
                AND LANGUAGE_CD = 'ENG'
            IF (SELECT @@ROWCOUNT) = 0
                BEGIN
                    SELECT @V_LANGUAGE_CD = 'ENG'
                    SELECT @V_RETURN_CD = 1013
                END
       END


    IF @V_RETURN_CD = 0

	        SELECT 
	            @V_RETURN_CD RETURN_CODE,
	            CAST (NULL AS VARCHAR) MESSAGE_TEXT,
	            TE.TD_ENTRY_ID,
	            TP.MSG_PARM_VAL,
	            TP.SEQ_NUM, 
	            TT.TD_TYPE_CD,
	            TTL.DESCR TD_TYPE_DESCR,
	            LO.DESCR STATUS_DESCR,
	            TE.CRE_DTTM ,
	            ML.MESSAGE_TEXT TD_MESSAGE_TEXT,
	            CASE (  SIGN( @V_AGE_DAYS_2 - (DATEDIFF(DAY, TE.CRE_DTTM, GETDATE())) )  )
	                WHEN -1 THEN (@V_AGE_DAYS_2 +'>')
	                WHEN 0 THEN (@V_AGE_DAYS_1 + '<= & <=' + @V_AGE_DAYS_2)
	                WHEN 1  THEN 
	                    CASE (SIGN(@V_AGE_DAYS_1 - (DATEDIFF(DAY, GETDATE(), TE.CRE_DTTM))))
	                        WHEN -1 THEN (@V_AGE_DAYS_1 + '<= & <=' + @V_AGE_DAYS_2)
	                        WHEN 0 THEN (@V_AGE_DAYS_1 + '<= & <=' + @V_AGE_DAYS_2)
	                        WHEN 1 THEN ('<'+ @V_AGE_DAYS_1)
	                    END
	            END AS AGE
	        FROM 
	            CI_TD_TYPE TT, 
	            CI_TD_TYPE_L TTL,
	            CI_MSG_L ML, 
	            CI_LOOKUP_VAL_L LO, 
	     	    CI_TD_ENTRY TE 
	                LEFT OUTER JOIN CI_TD_MSG_PARM TP
		            ON TE.TD_ENTRY_ID = TP.TD_ENTRY_ID
	        WHERE   
	            TT.TD_TYPE_CD = TTL.TD_TYPE_CD
	            AND ( @V_TD_TYPE_CD IS NULL OR TT.TD_TYPE_CD =@V_TD_TYPE_CD)
	            AND TTL.LANGUAGE_CD = @V_LANGUAGE_CD
	            AND TE.TD_TYPE_CD = TT.TD_TYPE_CD
	            AND TE.ENTRY_STATUS_FLG <> 'C'
	            AND ( @V_ENTRY_STATUS_FLG  IS NULL OR TE.ENTRY_STATUS_FLG = @V_ENTRY_STATUS_FLG)
	            AND LO.FIELD_NAME = 'ENTRY_STATUS_FLG' AND LO.FIELD_VALUE = TE.ENTRY_STATUS_FLG 
	            AND LO.LANGUAGE_CD = @V_LANGUAGE_CD
	            AND TE.MESSAGE_CAT_NBR = ML.MESSAGE_CAT_NBR 
	            AND TE.MESSAGE_NBR  = ML.MESSAGE_NBR 
	            AND ML.LANGUAGE_CD = @V_LANGUAGE_CD 
	        ORDER BY 
	            TTL.DESCR, 
	            TE.TD_TYPE_CD, 
	            LO.DESCR, 
		    TE.CRE_DTTM DESC, 
		    TP.TD_ENTRY_ID, 
	            TP.SEQ_NUM

    ELSE

        SELECT 
            MESSAGE_NBR RETURN_CODE,
            MESSAGE_TEXT,
            CAST (NULL AS CHAR) TD_ENTRY_ID,
            CAST (NULL AS VARCHAR) MSG_PARM_VAL,
            CAST (NULL AS DECIMAL) SEQ_NUM, 
            CAST (NULL AS CHAR) TD_TYPE_CD,
            CAST (NULL AS VARCHAR) TD_TYPE_DESCR,
            CAST (NULL AS VARCHAR) STATUS_DESCR,
            CAST (NULL AS DATETIME) CRE_DTTM,
            CAST (NULL AS VARCHAR) TD_MESSAGE_TEXT,
            CAST (NULL AS CHAR) AGE     
        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

