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


CREATE PROCEDURE RPTUSER.CICUSTCN
@P_USER_ID    CHAR (8)
,@P_FROM_DT   VARCHAR (10)
,@P_TO_DT     VARCHAR (10)
,@P_CC_TYPE_CD CHAR (17)

AS  

/******************************************************************************
   NAME:       CICUSTCN
   PURPOSE:    Supply report extract for Crystal Report - Customer Contacts

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

   DECLARE  @V_FROM_DT DATETIME;
   DECLARE  @V_TO_DT DATETIME;
   DECLARE  @V_LANGUAGE_CD CHAR(3);
   DECLARE  @V_RETURN_CD   INTEGER;
   DECLARE  @SQLSTATE CHAR(5);
   DECLARE  @SQLCODE INT;
   DECLARE  @V_CC_CL_CD CHAR(4);
   DECLARE  @V_CC_TYPE_CD  CHAR(12);

   SET @V_LANGUAGE_CD =  'ENG';
   SET @V_RETURN_CD = 0;
   
  BEGIN -- Main block

   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;

   IF @P_CC_TYPE_CD IS NULL OR @P_CC_TYPE_CD = ' '
   BEGIN
   	  SET @V_CC_CL_CD = NULL;
	  SET @V_CC_TYPE_CD = NULL;
   END   
   ELSE
   BEGIN

      SET @V_CC_CL_CD = LTRIM(RTRIM(SUBSTRING (@P_CC_TYPE_CD, 1, CHARINDEX ('|', @P_CC_TYPE_CD, 1) - 1)));
      SET @V_CC_TYPE_CD = LTRIM(RTRIM(SUBSTRING (@P_CC_TYPE_CD, CHARINDEX ('|', @P_CC_TYPE_CD, 1) + 1, LEN(@P_CC_TYPE_CD) - LEN(@V_CC_CL_CD) -1 )));

      SELECT @V_CC_CL_CD = CC_CL_CD
      FROM   CI_CC_CL
      WHERE  CC_CL_CD = @V_CC_CL_CD;
	   
      IF @@ROWCOUNT = 0
      BEGIN
           SET @V_LANGUAGE_CD = 'ENG';
	   SET @V_RETURN_CD = 1010; 
      END;
	      	   	   
      IF @V_CC_TYPE_CD IS NULL OR @V_CC_TYPE_CD = ' '
	   SET @V_CC_TYPE_CD = NULL;
      ELSE
      BEGIN 
           SELECT @V_CC_TYPE_CD = CC_TYPE_CD
           FROM   CI_CC_TYPE
	   WHERE  CC_CL_CD = @V_CC_CL_CD 
              AND CC_TYPE_CD = @V_CC_TYPE_CD;
           IF @@ROWCOUNT = 0 
           BEGIN
	      SET @V_LANGUAGE_CD = 'ENG';
	      SET @V_RETURN_CD = 1011; 
	   END;	   
      END;	   	   	   
   END;

------------------------------------------------------------------------------------
--   Checking for nulls in parameters and setting default values for select 
--   statements
------------------------------------------------------------------------------------
	 
    IF   @P_FROM_DT IS NULL OR @P_FROM_DT = '' 
        SET @V_FROM_DT = DATEDIFF(day, GETDATE(), 7);
    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(smalldatetime, @P_TO_DT);


    IF @V_RETURN_CD = 0
    BEGIN

      	   SELECT @V_RETURN_CD RETURN_CODE
	                        ,CAST (NULL AS VARCHAR)  MESSAGE_TEXT
                	        ,@V_FROM_DT V_FROM_DT
		                ,@V_TO_DT V_TO_DT
				 ,CI_CC.CC_DTTM CONTACT_TIME,
       			  CI_PER_NAME.ENTITY_NAME,
	   			  CI_CC.CC_CL_CD,
	   			  CI_CC.CC_TYPE_CD,
	   			  CI_CC_CL_L.DESCR CONTACT_CLASS,
	   			  CI_CC_TYPE_L.DESCR CONTACT_TYPE,
	   			  CI_CC.PRINT_LETTER_SW,
	   			  CI_CC.LETTER_PRINT_DTTM,
	   			  CI_CC.LTR_TMPL_CD,
	   			  CI_LETTER_TMPL_L.DESCR LTR_TMPL,
	   			  CI_CC.DESCR254 DETAILS,
	   			  CI_CC.USER_ID,
				  (SC_USER.LAST_NAME  + ', ' + SC_USER.FIRST_NAME) USER_NAME

	   FROM  SC_USER,
	        CI_CC INNER JOIN CI_CC_TYPE_L
	   		ON 	CI_CC_TYPE_L.CC_CL_CD = CI_CC.CC_CL_CD
	   		AND CI_CC_TYPE_L.CC_TYPE_CD = CI_CC.CC_TYPE_CD

	   		INNER JOIN CI_CC_CL_L
	   		ON CI_CC_CL_L.CC_CL_CD = CI_CC.CC_CL_CD

	   		INNER JOIN CI_PER_NAME
	   		ON CI_PER_NAME.PER_ID = CI_CC.PER_ID

	   		LEFT OUTER JOIN CI_LETTER_TMPL_L
			ON CI_CC.LTR_TMPL_CD = CI_LETTER_TMPL_L.LTR_TMPL_CD
			AND CI_LETTER_TMPL_L.LANGUAGE_CD = @V_LANGUAGE_CD
		    WHERE
	   		CI_CC.CC_DTTM BETWEEN @V_FROM_DT AND @V_TO_DT 
	   		AND
	   		CI_PER_NAME.NAME_TYPE_FLG = 'PRIM' 
	   		 AND
	   		CI_CC_CL_L.LANGUAGE_CD = @V_LANGUAGE_CD 
	   		 AND
	   		CI_CC_TYPE_L.LANGUAGE_CD = @V_LANGUAGE_CD 
	   		   	   		
			AND (@V_CC_CL_CD IS NULL OR CI_CC.CC_CL_CD = @V_CC_CL_CD)
			AND (@V_CC_TYPE_CD IS NULL OR CI_CC.CC_TYPE_CD = @V_CC_TYPE_CD)
			AND SC_USER.USER_ID = CI_CC.USER_ID
	  ORDER BY CI_CC_CL_L.DESCR, CI_CC_TYPE_L.DESCR, CI_PER_NAME.ENTITY_NAME, CI_CC.CC_DTTM DESC;		   

    END
    ELSE
    BEGIN

      SELECT MESSAGE_NBR RETURN_CODE
	  		 ,MESSAGE_TEXT
                	 ,CAST (NULL AS SMALLDATETIME)  V_FROM_DT
		         ,CAST (NULL AS SMALLDATETIME)  V_TO_DT
                         ,CAST (NULL AS TIMESTAMP) CONTACT_TIME,
       			  CAST (NULL AS VARCHAR) ENTITY_NAME,
	   			  CAST (NULL AS CHAR) CC_CL_CD,
	   			  CAST (NULL AS CHAR) CC_TYPE_CD,
	   			  CAST (NULL AS VARCHAR) CONTACT_CLASS,
	   			  CAST (NULL AS VARCHAR) CONTACT_TYPE,
	   			  CAST (NULL AS CHAR) PRINT_LETTER_SW,
	   			  CAST (NULL AS TIMESTAMP) LETTER_PRINT_DTTM,
	   			  CAST (NULL AS CHAR) LTR_TMPL_CD,
	   			  CAST (NULL AS VARCHAR) LTR_TMPL,
	   			  CAST (NULL AS VARCHAR)  DETAILS,
	   			  CAST (NULL AS CHAR) USER_ID,
				  CAST (NULL AS VARCHAR) USER_NAME

      FROM    CI_MSG_L
	  WHERE   MESSAGE_CAT_NBR = 30
	  AND	  MESSAGE_NBR = @V_RETURN_CD
	  AND	  LANGUAGE_CD = @V_LANGUAGE_CD;
    END;


END
	  

RETURN

END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

