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

CREATE PROCEDURE RPTUSER.CICLLSUM
@P_USER_ID CHAR(8),
@P_FROM_DT VARCHAR(10),
@P_TO_DT VARCHAR(10)
AS

/******************************************************************************
   NAME:       CICLLSUM 
   PURPOSE:    Supply report extract for Crystal Report - Collection summary

******************************************************************************/

   DECLARE   @V_FROM_DT       DATETIME
   DECLARE   @V_TO_DT         DATETIME
   DECLARE   @V_LANGUAGE_CD   CHAR(3)
   DECLARE   @V_RETURN_CD     INT

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

BEGIN


/******************************************************************************
   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

   IF @P_FROM_DT IS NULL OR @P_FROM_DT = ' '	
      SET @V_FROM_DT = DATEADD(month, -1, GETDATE())   
   
   ELSE
      SET @V_FROM_DT = CAST(@P_FROM_DT AS DATETIME)

   IF @P_TO_DT IS NULL OR @P_FROM_DT = ' '
      SET @V_TO_DT = DATEADD(month, 1, GETDATE())   

   ELSE
      SET @V_TO_DT = CAST(@P_TO_DT AS DATETIME)

   IF @V_TO_DT = NULL OR @V_FROM_DT = NULL
      SET @V_RETURN_CD = 1000  	-- Set return code to be invalid datetime parameters
   
   IF @V_RETURN_CD = 0
      BEGIN
         SELECT @V_RETURN_CD RETURN_CODE
	  	,CAST (NULL AS VARCHAR) MESSAGE_TEXT
	  	,L.DESCR COLL_EVT_STAT
	        ,TYP.DESCR COLL_EVT_TYPE
		,COUNT(DISTINCT SA_ID) SA_ID_CNT
  		,CAST(@V_FROM_DT AS DATETIME) V_FROM_DT
		,CAST(@V_TO_DT AS DATETIME) V_TO_DT
         FROM   CI_COLL_EVT EVT
       	        ,CI_LOOKUP_VAL_L L 
	 	,CI_COLL_PROC_SA SA
	 	,CI_COLL_EVT_TYP_L TYP
         WHERE  EVT.TRIGGER_DT BETWEEN @V_FROM_DT AND @V_TO_DT
	        AND EVT.COLL_EVT_STAT_FLG IN ('10', '30')
                AND EVT.COLL_EVT_STAT_FLG  = L.FIELD_VALUE
        	AND L.FIELD_NAME = 'COLL_EVT_STAT_FLG'
	        AND EVT.COLL_PROC_ID = SA.COLL_PROC_ID
	        AND SA.COLL_SA_STAT_FLG = '10'
	        AND EVT.COLL_EVT_TYP_CD = TYP.COLL_EVT_TYP_CD
	        AND TYP.LANGUAGE_CD = @V_LANGUAGE_CD
	        AND L.LANGUAGE_CD = @V_LANGUAGE_CD
	 GROUP BY L.DESCR
	     	  ,TYP.DESCR
     ORDER BY L.DESCR, TYP.DESCR
      END
  
   ELSE  
      BEGIN
         SELECT MESSAGE_NBR RETURN_CODE
                ,MESSAGE_TEXT
                ,CAST (NULL AS VARCHAR) COLL_EVT_STAT
                ,CAST (NULL AS VARCHAR) COLL_EVT_TYPE
                ,CAST (NULL AS INT) SA_ID_CNT
                ,CAST (NULL AS DATETIME) V_FROM_DT
                ,CAST (NULL AS DATETIME) V_TO_DT
         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
