/******************************************************************************************************************************
** ObjectName: [P_IMP_ISTEPS_FAC]
**
** Author: Bret Smith
**
** Company Name: Windsor Solutions, Inc
**
** Description:  Insert a master facility record for a new facility (never been migrated).
**
**
** Revision History:
** ----------------------------------------------------------------------------------------------------------------------------
**  Date		Name        Description
** ----------------------------------------------------------------------------------------------------------------------------
** 09/01/2011	BSmith      Original.
** 02/08/2012	BSmith		Changed DE and AR FAC_IDEN format.
**
******************************************************************************************************************************/
ALTER PROCEDURE [dbo].[P_IMP_ISTEPS_MSTR]
	@p_isteps_state_cd			CHAR(2),
	@p_isteps_idplant			DECIMAL(20,0),
	@p_mstr_fac_site_id			INT OUTPUT
AS
	SET NOCOUNT ON;
BEGIN
	DECLARE @v_err_code			INT;
	
	INSERT INTO M_FAC_SITE
	(
		FAC_SITE_MSTR_IND,
		FAC_SITE_IDEN,
		FAC_SITE_NAME,
		E_FAC_SITE_STAT_CD,
		LAT_MEAS,
		LONG_MEAS
	)
	(
		SELECT
			1,
			CASE
				WHEN @p_isteps_state_cd IN ('AR') THEN p.STATE_CODE + p.CNTY_CODE + '-' + p.PLANT_ID
				WHEN @p_isteps_state_cd IN ('DE') THEN p.STATE_CODE + p.CNTY_CODE + p.PLANT_ID
				ELSE p.STATE_CODE + '-' + p.CNTY_CODE + '-' + p.PLANT_ID
			END,
			LTRIM(RTRIM(p.NAME)),
			'UNK',
			0,
			0
		FROM
			I_PLANT p
		WHERE
			p.IDPLANT = @p_isteps_idplant
	);
	

	SELECT @v_err_code = @@Error;
	SELECT @p_mstr_fac_site_id = SCOPE_IDENTITY();
	
	
	If @v_err_code = 0
	BEGIN
		INSERT INTO M_FAC_IDEN
		(
			FAC_SITE_ID,
			FAC_SITE_IDEN,
			A_PROG_SYS_CD,
			E_ST_CNTY_FIPS_CD,
			EFF_DATE
		)
		(
			SELECT
				@p_mstr_fac_site_id,
				CASE
					WHEN @p_isteps_state_cd IN ('AR') THEN p.STATE_CODE + p.CNTY_CODE + '-' + p.PLANT_ID
					WHEN @p_isteps_state_cd IN ('DE') THEN p.STATE_CODE + p.CNTY_CODE + p.PLANT_ID
					ELSE p.STATE_CODE + '-' + p.CNTY_CODE + '-' + p.PLANT_ID
				END,
				'SLEIS',
				p.STATE_CODE + p.CNTY_CODE,
				GETDATE()
			FROM
				I_PLANT p
			WHERE
				p.IDPLANT = @p_isteps_idplant
		);	
		
		SELECT @v_err_code = @@Error;
	END;
	
	
	--Comment
	If @v_err_code = 0
	BEGIN	
		INSERT INTO M_FAC_SITE_CMNT
		(
			FAC_SITE_ID,
			CMNT_TXT,
			CMNT_DATE
		)
		VALUES
		(
			@p_mstr_fac_site_id,
			'Master record created.',
			GETDATE()
		);
				
		SELECT @v_err_code = @@Error;
	END;	
	
	RETURN @v_err_code;
END;
