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

CREATE PROCEDURE RPTUSER.CIACSVPR
@P_USER_ID CHAR(8),
@P_FROM_DT VARCHAR(26),
@P_TO_DT   VARCHAR(26)

AS
   DECLARE   @V_FROM_DT DATETIME
   DECLARE   @V_TO_DT DATETIME   
   DECLARE   @V_LANGUAGE_CD  CHAR(3)
   DECLARE   @V_RETURN_CD    INT
   SELECT @V_RETURN_CD = 0

	
BEGIN


      SELECT	@V_LANGUAGE_CD=LANGUAGE_CD
      FROM	SC_USER
      WHERE  SC_USER.USER_ID = @P_USER_ID;

      IF @@ROWCOUNT = 0
      BEGIN 
	  SELECT @V_LANGUAGE_CD = 'ENG'
	  SELECT @V_RETURN_CD = 1003
      END
       
      IF @V_RETURN_CD = 0
      BEGIN
            BEGIN
               SELECT @V_FROM_DT = NULL;
               SELECT @V_TO_DT = NULL;
            END
	    IF   @P_FROM_DT IS NULL OR @P_FROM_DT = ' ' 
 	       SELECT @V_FROM_DT = CAST('1900-01-01' AS DATETIME)
	    ELSE 
               SELECT @V_FROM_DT = CAST(@P_FROM_DT AS DATETIME)

	    IF   @P_TO_DT IS NULL  OR @P_TO_DT = ' '
	       SELECT @V_TO_DT = getDate()
	    ELSE
               SELECT @V_TO_DT = CAST(@P_TO_DT AS DATETIME)
             
            IF @V_TO_DT = NULL OR @V_FROM_DT = NULL
               SELECT @V_RETURN_CD = 1000  	-- Set return code to be invalid datetime parameters
           
     END

     IF @V_RETURN_CD = 0
     BEGIN
        SELECT   @V_RETURN_CD RETURN_CODE
	  		   ,CAST (NULL AS VARCHAR) MESSAGE_TEXT
		           ,SVC_TYPE.DESCR SVC_TYPE
		 	   ,CUST_CL.DESCR CUST_CL
		 	   ,SEVPROC.SEV_PROC_ID
		 	   ,SEVPROC.CRE_DTTM
		 	   ,SEVPROC.CURRENCY_CD CURRENCY
		 	   ,SEVPROC.ARS_AMT
		 	   ,SEVPROC.SEV_ARS_DT
		 	   ,SA.ACCT_ID
		 	   ,PER.ENTITY_NAME
		 	   ,SEV_EVT.COMPLETED/SEV_EVT.TOTAL * 100 PERCENT_COMPLETED
		 	   ,TOTAL
		 	   ,COMPLETED
			   ,CURR.CUR_SYMBOL
			   ,CURR.DECIMAL_POSITIONS
			   ,CURR.SCALE_POSITIONS
			   ,CURR.CUR_POS_FLG
			   ,CONVERT(VARCHAR,@V_FROM_DT,102) V_FROM_DT
			   ,CONVERT (VARCHAR,@V_TO_DT,102) V_TO_DT
			   ,CI_CURRENCY_CD_L.DESCR CURR_DESC
      FROM	 CI_SEV_PROC 	  	   SEVPROC
		 	 ,CI_SA		 	 	   SA
		 	 ,CI_SA_TYPE   		   SA_TYPE
		 	 ,CI_ACCT	 		   ACCT
		 	 ,CI_ACCT_PER	 	   	 ACCT_PER
		 	 ,CI_PER_NAME	 		 PER
		 	 ,CI_CUST_CL_L	 		 CUST_CL
		 	 ,CI_SVC_TYPE_L	 		 SVC_TYPE
		 	 ,CI_CURRENCY_CD		 CURR
			 ,CI_CURRENCY_CD_L
		 	 , (SELECT  T1.SEV_PROC_ID
		   			,SUM (CASE T2.SEV_EVT_STAT_FLG  WHEN '30' THEN 1  WHEN '40' THEN 1  ELSE 0 END)  COMPLETED
					,COUNT (*)	 TOTAL
		        FROM 	CI_SEV_PROC	 T1 ,CI_SEV_EVT  T2
				WHERE	T1.CRE_DTTM BETWEEN @V_FROM_DT AND @V_TO_DT
				AND		T1.SEV_STATUS_FLG = '10'
				AND		T1.SEV_PROC_ID = T2.SEV_PROC_ID
				GROUP BY T1.SEV_PROC_ID)	SEV_EVT
      WHERE	     SEVPROC.SA_ID = SA.SA_ID
   	  AND	     SA.SA_TYPE_CD = SA_TYPE.SA_TYPE_CD
   	  AND	     SA.CIS_DIVISION = SA_TYPE.CIS_DIVISION
   	  AND	     SA.ACCT_ID = ACCT.ACCT_ID
   	  AND	     ACCT.ACCT_ID = ACCT_PER.ACCT_ID
   	  AND	     ACCT_PER.PER_ID = PER.PER_ID
   	  AND	     PER.PRIM_NAME_SW = 'Y'
	  AND        ACCT_PER.MAIN_CUST_SW = 'Y' 
   	  AND	     SEVPROC.CRE_DTTM BETWEEN @V_FROM_DT AND @V_TO_DT
   	  AND	     ACCT.CUST_CL_CD = CUST_CL.CUST_CL_CD
   	  AND	     CUST_CL.LANGUAGE_CD = @V_LANGUAGE_CD
   	  AND        SA_TYPE.SVC_TYPE_CD = SVC_TYPE.SVC_TYPE_CD
   	  AND	     SVC_TYPE.LANGUAGE_CD = @V_LANGUAGE_CD
   	  AND	     SEVPROC.CURRENCY_CD = CURR.CURRENCY_CD
   	  AND	     SEVPROC.SEV_STATUS_FLG = '10'
          AND	     SEVPROC.SEV_PROC_ID = SEV_EVT.SEV_PROC_ID
	  AND	     SEVPROC.CURRENCY_CD = CI_CURRENCY_CD_L.CURRENCY_CD
	  AND	     CI_CURRENCY_CD_L.LANGUAGE_CD = @V_LANGUAGE_CD;
     END
    ELSE
     BEGIN

        SELECT   MESSAGE_NBR RETURN_CODE
	  		   ,MESSAGE_TEXT
		           ,CAST (NULL AS CHAR) SVC_TYPE
	  		   ,CAST (NULL AS VARCHAR) CUST_CL
		 	   ,CAST (NULL AS CHAR) SEV_PROC_ID
		 	   ,CAST (NULL AS DATETIME) CRE_DTTM
		 	   ,CAST (NULL AS NUMERIC) CURRENCY
		 	   ,CAST (NULL AS NUMERIC) ARS_AMT
		 	   ,CAST (NULL AS DATETIME) SEV_ARS_DT
		 	   ,CAST (NULL AS CHAR) ACCT_ID
		 	   ,CAST (NULL AS VARCHAR) ENTITY_NAME
		 	   ,CAST (NULL AS NUMERIC) PERCENT_COMPLETED
		 	   ,CAST (NULL AS NUMERIC) TOTAL
		 	   ,CAST (NULL AS NUMERIC) COMPLETED
			   ,CAST (NULL AS CHAR) CUR_SYMBOL
			   ,CAST (NULL AS NUMERIC) DECIMAL_POSITIONS
			   ,CAST (NULL AS NUMERIC) SCALE_POSITIONS
			   ,CAST (NULL AS CHAR) CUR_POS_FLG
			   ,CAST (NULL AS DATETIME) V_FROM_DT
			   ,CAST (NULL AS DATETIME) V_TO_DT
			   ,CAST (NULL AS VARCHAR) CURR_DESC
 
        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
