SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'CIVACANT' AND type = 'P')
   DROP PROCEDURE CIVACANT 
GO

CREATE PROCEDURE CIVACANT
@P_USER_ID CHAR(8)

AS
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	(SELECT @@ROWCOUNT) = 0
	BEGIN
		SELECT @V_LANGUAGE_CD = 'ENG'
		SELECT @V_RETURN_CD = 1003
	END

	IF @V_RETURN_CD = 0
	BEGIN
		SELECT	@V_RETURN_CD = @@ROWCOUNT
	    	SELECT	@V_RETURN_CD RETURN_CODE
  		   	,CAST (NULL AS VARCHAR) MESSAGE_TEXT
		   	,CNCL_OR_CLS.SP_ID
			,ISNULL(CNCL_OR_CLS.STOP_DTTM, CI_MTR_CONFIG.EFF_DTTM) STOP_DTTM
		   	,CI_REG.REG_ID
		   	,REG_READING
		   	,CI_REG_READ.READ_SEQ
		   	,CI_MR.READ_DTTM
		   	,CI_PREM.ADDRESS1
		   	,CI_PREM.ADDRESS2
		   	,CI_PREM.ADDRESS3
		   	,CI_PREM.ADDRESS4
		   	,CI_PREM.COUNTY
		   	,CI_PREM.CITY
		   	,CI_PREM.STATE
		   	,CI_PREM.POSTAL
		   	,CI_PREM.PREM_ID
	   	   	,CI_SP.SP_TYPE_CD
		   	,CI_SP.MR_CYC_CD
        	   	,CI_REG.UOM_CD
		   	,CI_SP_TYPE_L.DESCR SP_TYPE
        	   	,CI_REG.CONSUM_SUB_FLG
	           	,POWER (10, CI_REG.NBR_OF_DGTS_LFT) NBR_OF_DGTS_LFT
	    	FROM	CI_MR
	       		,CI_SP_MTR_HIST
	       		,CI_REG_READ
	       		,CI_PREM
	       		,CI_SP
	       		,CI_REG
	       		,CI_SP_TYPE_L
	       		,CI_MTR_CONFIG
	       		,(SELECT SP_ID
   		   		,MAX(CI_SA_SP.STOP_DTTM) STOP_DTTM
	           		FROM CI_SA_SP
 		        		,CI_SA
	           		WHERE CI_SA_SP.SA_ID = CI_SA.SA_ID
        	   		GROUP BY SP_ID
	           	    HAVING MIN (SA_STATUS_FLG) IN ('40','60','70')
			UNION
	      		SELECT	CI_SP.SP_ID
   		  		,CAST (NULL AS DATETIME) STOP_DTTM
	      		FROM	  CI_SP
      			WHERE NOT EXISTS
	   	 		(SELECT 1
		    		FROM CI_SA_SP
			 	WHERE CI_SA_SP.SP_ID = CI_SP.SP_ID)
	         		) CNCL_OR_CLS

		WHERE	CNCL_OR_CLS.SP_ID = CI_SP_MTR_HIST.SP_ID
	      	AND  CI_SP_MTR_HIST.MTR_CONFIG_ID = CI_MR.MTR_CONFIG_ID
	      	AND  CI_MR.MR_ID = CI_REG_READ.MR_ID
	      	AND  CI_SP.PREM_ID = CI_PREM.PREM_ID
	      	AND  CNCL_OR_CLS.SP_ID = CI_SP.SP_ID
	      	AND  CI_REG_READ.REG_ID = CI_REG.REG_ID
	      	AND  CI_SP.SP_TYPE_CD = CI_SP_TYPE_L.SP_TYPE_CD
	      	AND   CI_SP_MTR_HIST.MTR_CONFIG_ID = CI_MTR_CONFIG.MTR_CONFIG_ID
	      	AND   LANGUAGE_CD = @V_LANGUAGE_CD
	     	ORDER BY
 			CI_PREM.CITY
			,CI_PREM.STATE
			,CI_PREM.POSTAL
			,CI_PREM.ADDRESS1
			,CI_PREM.ADDRESS2
			,CI_PREM.ADDRESS3
			,CI_PREM.ADDRESS4
			,CI_PREM.COUNTY
			,CI_REG.UOM_CD
			,CI_REG.REG_ID
			,CI_MR.READ_DTTM
        	 	;

	END
	ELSE
	BEGIN
		 SELECT MESSAGE_NBR RETURN_CODE
	  	   ,MESSAGE_TEXT
		   ,CAST (NULL AS CHAR) SP_ID
		   ,CAST (NULL AS DATETIME) STOP_DTTM
		   ,CAST (NULL AS CHAR) REG_ID
		   ,CAST (NULL AS NUMERIC) REG_READING
		   ,CAST (NULL AS NUMERIC) READ_SEQ
		   ,CAST (NULL AS DATETIME) READ_DTTM
		   ,CAST (NULL AS VARCHAR) ADDRESS1
		   ,CAST (NULL AS VARCHAR) ADDRESS2
		   ,CAST (NULL AS VARCHAR) ADDRESS3
		   ,CAST (NULL AS VARCHAR) ADDRESS4
		   ,CAST (NULL AS CHAR) COUNTY
		   ,CAST (NULL AS VARCHAR) CITY
		   ,CAST (NULL AS CHAR) STATE
		   ,CAST (NULL AS CHAR) POSTAL
		   ,CAST (NULL AS CHAR) PREM_ID
	   	   ,CAST (NULL AS CHAR) SP_TYPE_CD
		   ,CAST (NULL AS CHAR) MR_CYC_CD
		   ,CAST (NULL AS CHAR) UOM_CD
		   ,CAST (NULL AS VARCHAR) SP_TYPE
		   ,CAST (NULL AS CHAR) CONSUM_SUB_FLG
	           ,CAST (NULL AS NUMERIC) NBR_OF_DGTS_LFT

	          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

