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

CREATE PROCEDURE CIPMLSSL
@P_USER_ID CHAR(8),
@P_SERVICE_TYPE CHAR(2)


AS
   DECLARE   @V_LANGUAGE_CD  CHAR(3)
   DECLARE   @V_SVC_TYPE_CD  CHAR(2)
   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

      SELECT  @V_SVC_TYPE_CD=SVC_TYPE_CD
      FROM    CI_SVC_TYPE
      WHERE   CI_SVC_TYPE.SVC_TYPE_CD = @P_SERVICE_TYPE;
      IF @@ROWCOUNT = 0
      BEGIN 
	  SELECT @V_LANGUAGE_CD = 'ENG';
  	  SELECT @V_RETURN_CD = 1014;
      END

       
      IF @V_RETURN_CD = 0
      BEGIN
		

		SELECT @V_RETURN_CD RETURN_CODE
		  ,CAST(NULL AS VARCHAR) MESSAGE_TEXT
		  ,INNER_QUERY.SP_TYPE_CD,
		  INNER_QUERY.SVC_TYPE_CD,
		  CI_SVC_TYPE_L.DESCR SVC_DESC,
		  INNER_QUERY.FAC_LVL_1_CD,
		  INNER_QUERY.FAC_LVL_2_CD,
		  INNER_QUERY.FAC_LVL_3_CD,
		  CI_FAC_LVL_1_L.DESCR FL1_DESC,
		  CI_FAC_LVL_2_L.DESCR FL2_DESC,
		  CI_FAC_LVL_3_L.DESCR FL3_DESC,
		  CI_PREM.ADDRESS1,
		  CI_PREM.ADDRESS2,
		  CI_PREM.ADDRESS3,
		  CI_PREM.ADDRESS4,
		  CI_PREM.CITY,
		  CI_PREM.STATE,
		  CI_PREM.COUNTRY,
		  CI_PER_NAME.ENTITY_NAME,
		  INNER_QUERY.LS_SL_DESCR
		  ,CI_PREM.POSTAL
		FROM
		  CI_PREM,
		  CI_PER_NAME,
		  CI_FAC_LVL_1_L,
		  CI_FAC_LVL_2_L,
		  CI_FAC_LVL_3_L,
		  CI_SVC_TYPE_L,
		  (SELECT
		         CI_SP_TYPE.SP_TYPE_CD,
			 CI_SP_TYPE.SVC_TYPE_CD,
	  		 CI_SP.FAC_LVL_1_CD,
	  		 CI_SP.FAC_LVL_2_CD,
	  		 CI_SP.FAC_LVL_3_CD
			 ,CI_SP.PREM_ID
	  		 ,SP_WITH_LSSL.LS_SL_DESCR
			 ,SP_WITH_LSSL.PER_ID
		   FROM CI_SP_TYPE
		  	 ,CI_SP 
		     ,(SELECT DISTINCT
	  		 	 CI_PER.LS_SL_DESCR
			 	 ,CI_SA_SP.SP_ID
				 ,CI_PER.PER_ID
		      FROM
	  	  	  CI_PER,
	  	  	  CI_SA,
	  	  	  CI_ACCT_PER
		  	  ,CI_SA_SP
		      WHERE
	/*****************************************************************
  	 The logic of deciding if a premise is LS/SL (as replicated from
  	 CorDaptix) is:
  I	 if ANY person connected to an account has a LS/SL indication in his
  	 Person reocrd, then all the premises connected to this account will
  	 be treated as LS/SL.
  	 This means, for example, that even a premise connected to an account
  	 which has a 3rd party guarantor with LS/SL will be treated as LS/SL.
  	 ******************************************************************/
        
			CI_SA.ACCT_ID = CI_ACCT_PER.ACCT_ID
	   		AND	CI_ACCT_PER.PER_ID = CI_PER.PER_ID
	   		AND	CI_SA.SA_ID = CI_SA_SP.SA_ID
	   		AND	CI_PER.LS_SL_FLG = 'Y'
			AND CI_SA.SA_STATUS_FLG < '60')  SP_WITH_LSSL

		WHERE CI_SP_TYPE.SP_TYPE_CD = CI_SP.SP_TYPE_CD
		   AND	CI_SP.SP_ID = SP_WITH_LSSL.SP_ID
		   AND  CI_SP_TYPE.SVC_TYPE_CD = @P_SERVICE_TYPE
   
   	   
	      	UNION
    	        SELECT DISTINCT
		         CI_SP_TYPE.SP_TYPE_CD,
	  		 CI_SP_TYPE.SVC_TYPE_CD,
	  		 CI_SP.FAC_LVL_1_CD,
	  		 CI_SP.FAC_LVL_2_CD,
	  		 CI_SP.FAC_LVL_3_CD
			 ,CI_SP.PREM_ID
	  		 ,CI_PREM.LS_SL_DESCR
			 ,CI_ACCT_PER.PER_ID
	    	FROM CI_SP_TYPE
		  	 ,CI_SP 
		         ,CI_PREM
			 ,CI_SA_SP
			 ,CI_SA
			 ,CI_ACCT_PER
		WHERE CI_SP_TYPE.SP_TYPE_CD = CI_SP.SP_TYPE_CD
			AND	CI_SP.PREM_ID = CI_PREM.PREM_ID
			AND	CI_PREM.LS_SL_FLG = 'Y'
			AND  CI_SP_TYPE.SVC_TYPE_CD = @P_SERVICE_TYPE
			AND	CI_SA_SP.SP_ID = CI_SP.SP_ID
			AND	CI_SA.SA_ID = CI_SA_SP.SA_ID
			AND	CI_ACCT_PER.ACCT_ID = CI_SA.ACCT_ID
			AND 	CI_ACCT_PER.MAIN_CUST_SW = 'Y'
			AND CI_SA.SA_STATUS_FLG < '60')  INNER_QUERY
   
	   WHERE
		CI_PER_NAME.PER_ID = INNER_QUERY.PER_ID AND
		CI_PER_NAME.PRIM_NAME_SW = 'Y' AND
		INNER_QUERY.PREM_ID = CI_PREM.PREM_ID AND
		CI_FAC_LVL_1_L.SVC_TYPE_CD =* INNER_QUERY.SVC_TYPE_CD AND
		CI_FAC_LVL_1_L.FAC_LVL_1_CD =* INNER_QUERY.FAC_LVL_1_CD AND
		CI_FAC_LVL_2_L.SVC_TYPE_CD =* INNER_QUERY.SVC_TYPE_CD AND
		CI_FAC_LVL_2_L.FAC_LVL_2_CD =* INNER_QUERY.FAC_LVL_2_CD AND
		CI_FAC_LVL_3_L.SVC_TYPE_CD =* INNER_QUERY.SVC_TYPE_CD AND
		CI_FAC_LVL_3_L.FAC_LVL_3_CD =* INNER_QUERY.FAC_LVL_3_CD AND
		CI_FAC_LVL_1_L.LANGUAGE_CD = @V_LANGUAGE_CD AND
		CI_FAC_LVL_2_L.LANGUAGE_CD = @V_LANGUAGE_CD AND
		CI_FAC_LVL_3_L.LANGUAGE_CD = @V_LANGUAGE_CD AND
		CI_SVC_TYPE_L.SVC_TYPE_CD = INNER_QUERY.SVC_TYPE_CD AND
		CI_SVC_TYPE_L.LANGUAGE_CD = @V_LANGUAGE_CD
   
	  ORDER BY INNER_QUERY.SVC_TYPE_CD,
	         INNER_QUERY.FAC_LVL_1_CD,
	         INNER_QUERY.FAC_LVL_2_CD,
		 INNER_QUERY.FAC_LVL_3_CD,
	         CI_PREM.COUNTRY,
		 CI_PREM.STATE,
		 CI_PREM.CITY,
	         CI_PREM.ADDRESS1,
		 CI_PREM.ADDRESS2,
		 CI_PREM.ADDRESS3,
		 CI_PREM.ADDRESS4;
		 
		 
         END
   
	ELSE

		SELECT DISTINCT
		  MESSAGE_NBR RETURN_CODE
		  ,MESSAGE_TEXT
		  ,CAST(NULL AS CHAR) SP_TYPE_CD
		  ,CAST(NULL AS CHAR) SVC_TYPE_CD
		  ,CAST(NULL AS VARCHAR)  SVC_DESC
		  ,CAST(NULL AS CHAR) FAC_LVL_1_CD
		  ,CAST(NULL AS CHAR) FAC_LVL_2_CD
		  ,CAST(NULL AS CHAR) FAC_LVL_3_CD
		  ,CAST(NULL AS VARCHAR)  FL1_DESC
		  ,CAST(NULL AS VARCHAR)  FL2_DESC
		  ,CAST(NULL AS VARCHAR)  FL3_DESC
		  ,CAST(NULL AS VARCHAR) ADDRESS1
		  ,CAST(NULL AS VARCHAR) ADDRESS2
		  ,CAST(NULL AS VARCHAR) ADDRESS3
		  ,CAST(NULL AS VARCHAR) ADDRESS4
		  ,CAST(NULL AS VARCHAR) CITY
		  ,CAST(NULL AS CHAR) STATE
		  ,CAST(NULL AS CHAR) COUNTRY
		  ,CAST(NULL AS VARCHAR) ENTITY_NAME
		  ,CAST(NULL AS VARCHAR) LS_SL_DESCR
		  ,CAST(NULL AS CHAR) POSTAL
  
  		 FROM    CI_MSG_L
		 WHERE   MESSAGE_CAT_NBR = 30
		  AND	  MESSAGE_NBR = @V_RETURN_CD
		  AND	  LANGUAGE_CD = @V_LANGUAGE_CD;
	    




RETURN
END 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
