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

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

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

/****************************************************************************
This is a Crystal Sub-Report Select statement.
This select statement is built to return the following information:
For each service type (Elec,Gas,Water, etc) it displays a row with the
level-1/2/3 facility field titles (like Sub-station, feeder, node for elec).
This is done thru a join of 3 temp tables (create by sub-selects) for each
facility level number(1-3).
This select is used to provide dynaminc field titles in the report according
to different service types.
****************************************************************************/

	IF @V_RETURN_CD = 0
	BEGIN
		SELECT 
  			@V_RETURN_CD RETURN_CODE
			, CAST (NULL AS VARCHAR) MESSAGE_TEXT
			, LVL1_SELECT.SVC_TYPE_CD
			, LEVEL1
			, LEVEL2
			, LEVEL3 
		FROM
			(SELECT 
				SVC_TYPE_CD
				, DESCR LEVEL1
 			 FROM 
				CI_FAC_LVL_NAME_L
			 WHERE 
				FAC_LVL_FLG = '1' AND
    				LANGUAGE_CD = @V_LANGUAGE_CD) LVL1_SELECT,

			(SELECT 
				SVC_TYPE_CD
				, DESCR LEVEL2
			 FROM 
				CI_FAC_LVL_NAME_L
 			 WHERE FAC_LVL_FLG = '2' AND
     			 LANGUAGE_CD = @V_LANGUAGE_CD) LVL2_SELECT,

			(SELECT 
				SVC_TYPE_CD
				, DESCR LEVEL3
			FROM 
				CI_FAC_LVL_NAME_L
 			WHERE 
				FAC_LVL_FLG = '3' AND
 				LANGUAGE_CD = @V_LANGUAGE_CD) LVL3_SELECT
		WHERE
			LVL1_SELECT.SVC_TYPE_CD = @P_SERVICE_TYPE AND
 			LVL1_SELECT.SVC_TYPE_CD = LVL2_SELECT.SVC_TYPE_CD AND
			LVL1_SELECT.SVC_TYPE_CD = LVL3_SELECT.SVC_TYPE_CD;	
	END
	ELSE
	BEGIN
		SELECT 
			MESSAGE_NBR RETURN_CODE
			, MESSAGE_TEXT
  			, CAST (NULL AS CHAR) SVC_TYPE_CD
  			, CAST (NULL AS VARCHAR) LEVEL1
  			, CAST (NULL AS VARCHAR) LEVEL2
  			, CAST (NULL AS VARCHAR) LEVEL3
      		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
