ÿþ/******************************************************************************************************************************
** ObjectName: [P_IMP_ISTEPS_UNIT_PROC]
**
** Author: Bret Smith
**
** Company Name:Windsor Solutions, Inc
**
** Description:	Insert a single unit process and return new id to calling procedure.
**				Also insert child records where not associative entity (relating 
**				another entity type).
**
**
** Revision History:
** ----------------------------------------------------------------------------------------------------------------------------
**  Date		Name        Description
** ----------------------------------------------------------------------------------------------------------------------------
** 10/01/2011	BSmith      Original.
** 02/23/2012	BSmith		Changed DE COMMT retrieval logic. Needs to be uncommented before DE migration run.
** 02/17/2012	BSmith		Added additional custom fields for non-billable emissions. 
**
******************************************************************************************************************************/
ALTER PROCEDURE [dbo].[P_IMP_ISTEPS_UNIT_PROC]
	@p_isteps_state_cd				CHAR(2),
	@p_mstr_fac_site_id				INT,
	@p_new_fac_site_id				INT,
	@p_isteps_idpoint				INT,
	@p_new_emis_unit_id				INT,
	@p_isteps_idsegment				INT,
	@p_new_unit_proc_id				INT OUTPUT,
	@p_new_unit_proc_apch_id		INT OUTPUT,
	@p_new_rel_pt_id				INT OUTPUT
AS
	SET NOCOUNT ON;
BEGIN
	DECLARE @v_err_code				INT;
	
	DECLARE @v_isteps_idstack1		INT;
	DECLARE @v_isteps_idstack2		INT;
	DECLARE @v_tmp_id				INT = NULL;
	
	
	--Insert UNIT_PROC record based on POINT and SEGMENT records
	INSERT INTO M_UNIT_PROC
	(
		 EMIS_UNIT_ID
		,MSTR_UNIT_PROC_ID
		,E_SRC_CLASS_CD
		,UNIT_PROC_IDEN
		,PROC_DESC
		,PROC_CMNT
		,LAST_UPDATED_DATE
		,LAST_UPDATED_BY_USER_ID
	)
	(
		SELECT
			@p_new_emis_unit_id,
			(
				SELECT
					UNIT_PROC_ID
				FROM
					M_UNIT_PROC up
					INNER JOIN M_EMIS_UNIT eu ON up.EMIS_UNIT_ID = eu.EMIS_UNIT_ID
				WHERE
					eu.FAC_SITE_ID = @p_mstr_fac_site_id
					AND eu.EMIS_UNIT_IDEN = LTRIM(RTRIM(pt.POINT))
					AND up.UNIT_PROC_IDEN = LTRIM(RTRIM(CAST(s.SEGMENT AS VARCHAR(20))))
			),
			dbo.F_GET_XREF_VAL(@p_isteps_state_cd,'SCC',s.SCC),
			LTRIM(RTRIM(CAST(s.SEGMENT AS VARCHAR(20)))),
			LTRIM(RTRIM(SEGMT_DESC)),
			(
			SELECT						
				c.COMMT
			FROM
				I_COMMT c
				INNER JOIN I_SEGMENT s2 ON c.IDPARENT = s2.IDSEGMENT
			WHERE
				c.IDPARENT = s.IDSEGMENT
				AND s2.IDSEGMENT = s.IDSEGMENT
				AND RTRIM(LTRIM(c.COMMTYPE)) = 'COMMENT'
			) AS RPT_PRD_CMNT,
			/* UNCOMMENT FOR DE
			(
			SELECT
				c.COMMT
			FROM
				I_COMMT c
				INNER JOIN I_SEGMENT s2 ON c.IDPARENT = s2.IDSEGMENT
			WHERE
				c.IDPARENT = s.IDSEGMENT
				AND s2.IDSEGMENT = s.IDSEGMENT
				AND RTRIM(LTRIM(c.COMMTYPE)) = 'ZX_SEGDOC'
			) AS RPT_PRD_CMNT,
			*/
			GETDATE(),
			1
		FROM
			I_SEGMENT s
			INNER JOIN I_POINT pt ON s.IDPOINT = pt.IDPOINT
		WHERE
			s.IDSEGMENT = @p_isteps_idsegment
	);

	SELECT @v_err_code = @@Error;
	SELECT @p_new_unit_proc_id = SCOPE_IDENTITY();
	
	
	--Get stack, if any, associated with segment/process
	IF @v_err_code = 0
	BEGIN
		--NOTE: When STACKNO and/or IDSTACK is 0, that means there is no stack, so fugitive emissions
		SELECT
			@v_isteps_idstack1 = s.IDSTACK,
			@v_isteps_idstack2 = st.IDSTACK
		FROM
			I_SEGMENT s
			LEFT JOIN I_STACK st ON s.IDSTACK = st.IDSTACK
		WHERE
			s.IDSEGMENT = @p_isteps_idsegment;
		
		
		--No stack exists for the unit process, so create one for
		--the virtual fugitive stack for plant wide emissions	
		IF ((@v_isteps_idstack1 IS NULL OR @v_isteps_idstack1 = 0) AND @v_isteps_idstack2 IS NULL)
		BEGIN
			--Check if stack 0 already inserted for the facility during this migration run
			SELECT @v_tmp_id = REL_PT_ID FROM M_REL_PT rp WHERE rp.FAC_SITE_ID = @p_new_fac_site_id AND rp.REL_PT_IDEN = '0';
			
			--If release point 0 not created yet
			IF @v_tmp_id IS NULL
			BEGIN			
				INSERT INTO M_REL_PT
				(
					FAC_SITE_ID,
					MSTR_REL_PT_ID,
					REL_PT_IDEN,
					E_REL_PT_TYPE_CD,
					E_REL_PT_STAT_CD,
					REL_PT_STAT_CD_DATE,
					REL_PT_DESC,
					REL_PT_FGTV_HGT_MEAS,
					REL_PT_FGTV_HGT_UOM_CD,
					REL_PT_FGTV_WID_MEAS,
					REL_PT_FGTV_WID_UOM_CD,
					REL_PT_FGTV_LEN_MEAS,
					REL_PT_FGTV_LEN_UOM_CD,
					REL_PT_FGTV_ANGLE_MEAS,
					REL_PT_CMNT,
					FAC_SITE_LOC_IND,
					LAST_UPDATED_DATE,
					LAST_UPDATED_BY_USER_ID
				)
				VALUES
				(
					@p_new_fac_site_id,
					(
						SELECT REL_PT_ID FROM M_REL_PT
						WHERE
							FAC_SITE_ID = @p_mstr_fac_site_id
							AND REL_PT_IDEN ='0'
					),
					'0',
					'1',
					'OP',
					NULL,
					'PLANTWIDE FUGITIVES',
					NULL,
					NULL,
					NULL,
					NULL,
					NULL,
					NULL,
					NULL,
					'Inserted during iSTEPS data migration.',
					1,
					GETDATE(),
					1
				);
				
				SELECT @v_err_code = @@Error;
				SELECT @p_new_rel_pt_id = SCOPE_IDENTITY();
			END;
		END;
	END;
		
	
	--Insert control approach for the unit process
	If @v_err_code = 0
	BEGIN
		IF (EXISTS (SELECT 1 FROM I_CTRLSCNO WHERE IDSEGMENT = @p_isteps_idsegment))
		BEGIN
			--Create a control approach ready to accept control devices previously created
			INSERT INTO M_UNIT_PROC_CTRL_APCH
			(
				 UNIT_PROC_ID
				,NO_CTRL_IND
				,CTRL_APCH_DESC
				,PCT_CTRL_APCH_CAP_EFCY
				,PCT_CTRL_APCH_EFCT
				,FIRST_INV_YEAR
			)
			VALUES
			(
				 @p_new_unit_proc_id
				,0
				,'Unit Process Controls'
				,(SELECT TOP 1 cn.CAPTURE FROM I_CTRLSCNO cn WHERE cn.IDSEGMENT = @p_isteps_idsegment ORDER BY cn.SEQUENCE ASC)
				,100
				,NULL
			);			
		END ELSE
		BEGIN
			--Create a control approach indicating no controls are associated with process
			INSERT INTO M_UNIT_PROC_CTRL_APCH
			(
				 UNIT_PROC_ID
				,NO_CTRL_IND
				,CTRL_APCH_DESC
				,PCT_CTRL_APCH_CAP_EFCY
				,PCT_CTRL_APCH_EFCT
				,FIRST_INV_YEAR
			)
			VALUES
			(
				 @p_new_unit_proc_id
				,1
				,'No Process Controls'
				,0
				,100
				,NULL
			);
		END;
		
		SELECT @v_err_code = @@Error;
		SELECT @p_new_unit_proc_apch_id = SCOPE_IDENTITY();
	END;
	
	
	--CUSTOM FIELDS
	--
	--UnitProcessNonBillableEmissions
	IF @v_err_code = 0
	BEGIN
		INSERT INTO M_UNIT_PROC_CSTM_FLD_VAL
		(
			UNIT_PROC_ID,
			A_CSTM_FLD_ID,
			CSTM_FLD_VAL
		)
		(
			SELECT
				@p_new_unit_proc_id,
				(
					SELECT
						A_CSTM_FLD_ID
					FROM
						A_CSTM_FLD
					WHERE
						FLD_NAME = 'UnitProcessNonBillableEmissions'
						AND FLD_ENT_TYPE = 'UnitProcess'
				),
				CASE
					WHEN s.[INCLUDE] IS NOT NULL AND LTRIM(RTRIM(s.[INCLUDE])) = 'T' THEN 'Y'
					ELSE 'N'
				END
			FROM
				I_SEGMENT s
			WHERE
				s.IDSEGMENT = @p_isteps_idsegment
				
		);
	END;
	
	
	--UnitProcessAgencyApprovedNonBillableEmissions
	IF @v_err_code = 0
	BEGIN
		INSERT INTO M_UNIT_PROC_CSTM_FLD_VAL
		(
			UNIT_PROC_ID,
			A_CSTM_FLD_ID,
			CSTM_FLD_VAL
		)
		(
			SELECT
				@p_new_unit_proc_id,
				(
					SELECT
						A_CSTM_FLD_ID
					FROM
						A_CSTM_FLD
					WHERE
						FLD_NAME = 'UnitProcessAgencyApprovedNonBillableEmissions'
						AND FLD_ENT_TYPE = 'UnitProcess'
				),
				'N'
			FROM
				I_SEGMENT
			WHERE
				IDSEGMENT = @p_isteps_idsegment
		);
	END;
	
	
	--UnitProcessSiteProcessId
	IF @v_err_code = 0
	BEGIN
		INSERT INTO M_UNIT_PROC_CSTM_FLD_VAL
		(
			UNIT_PROC_ID
		   ,A_CSTM_FLD_ID
		   ,CSTM_FLD_VAL
		)
		(
			SELECT
				@p_new_unit_proc_id
				,(SELECT A_CSTM_FLD_ID FROM A_CSTM_FLD WHERE FLD_NAME = 'UnitProcessSiteProcessId' AND FLD_ENT_TYPE = 'UnitProcess')
				,LTRIM(RTRIM(SITE_SEGMT))
			FROM
				I_SEGMENT
			WHERE
				IDSEGMENT = @p_isteps_idsegment
				AND
				(
					SITE_SEGMT IS NOT NULL
					AND LTRIM(RTRIM(SITE_SEGMT)) <> ''
				)
		);
		
		SELECT @v_err_code = @@Error;
	END;	
	
	RETURN @v_err_code;
END;