/******************************************************************************************************************************਀⨀⨀ 伀戀樀攀挀琀一愀洀攀㨀 嬀倀开䤀䴀倀开䤀匀吀䔀倀匀开刀䔀䰀开倀吀崀
**਀⨀⨀ 䄀甀琀栀漀爀㨀 䈀爀攀琀 匀洀椀琀栀
**਀⨀⨀ 䌀漀洀瀀愀渀礀 一愀洀攀㨀圀椀渀搀猀漀爀 匀漀氀甀琀椀漀渀猀Ⰰ 䤀渀挀
**਀⨀⨀ 䐀攀猀挀爀椀瀀琀椀漀渀㨀ऀ䤀渀猀攀爀琀 愀 猀椀渀最氀攀 爀攀氀攀愀猀攀 瀀漀椀渀琀 愀渀搀 爀攀琀甀爀渀 渀攀眀 椀搀 琀漀 挀愀氀氀椀渀最 瀀爀漀挀攀搀甀爀攀⸀
**				Also insert child records where not associative entity (relating ਀⨀⨀ऀऀऀऀ愀渀漀琀栀攀爀 攀渀琀椀琀礀 琀礀瀀攀⤀⸀
**਀⨀⨀
** Revision History:਀⨀⨀ ⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀ
**  Date		Name        Description਀⨀⨀ ⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀ
** 09/01/2011	BSmith      Original.਀⨀⨀ 　㈀⼀㈀㐀⼀㈀　㄀㈀ऀ䈀匀洀椀琀栀ऀऀ䌀栀愀渀最攀搀 䐀䔀 䌀伀䴀䴀吀 爀攀琀爀椀攀瘀愀氀 氀漀最椀挀⸀ 一攀攀搀猀 琀漀 戀攀 甀渀挀漀洀洀攀渀琀攀搀 戀攀昀漀爀攀 䐀䔀 洀椀最爀愀琀椀漀渀 爀甀渀⸀
**਀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⼀
ALTER PROCEDURE [dbo].[P_IMP_ISTEPS_REL_PT]਀ऀ䀀瀀开椀猀琀攀瀀猀开猀琀愀琀攀开挀搀ऀऀऀ䌀䠀䄀刀⠀㈀⤀Ⰰ
	@p_mstr_fac_site_id			INT,਀ऀ䀀瀀开渀攀眀开昀愀挀开猀椀琀攀开椀搀ऀऀऀ䤀一吀Ⰰ
	@p_isteps_idstack			INT,਀ऀ䀀瀀开渀攀眀开爀攀氀开瀀琀开椀搀ऀऀऀ䤀一吀 伀唀吀倀唀吀
AS਀ऀ匀䔀吀 一伀䌀伀唀一吀 伀一㬀
BEGIN਀ऀ䐀䔀䌀䰀䄀刀䔀 䀀瘀开攀爀爀开挀漀搀攀ऀऀऀ䤀一吀㬀
	਀ऀ䤀一匀䔀刀吀 䤀一吀伀 䴀开刀䔀䰀开倀吀
	(਀ऀऀ䘀䄀䌀开匀䤀吀䔀开䤀䐀Ⰰ
		MSTR_REL_PT_ID,਀ऀऀ刀䔀䰀开倀吀开䤀䐀䔀一Ⰰ
		E_REL_PT_TYPE_CD,਀ऀऀ䔀开刀䔀䰀开倀吀开匀吀䄀吀开䌀䐀Ⰰ
		REL_PT_STAT_CD_DATE,਀ऀऀ刀䔀䰀开倀吀开䐀䔀匀䌀Ⰰ
		REL_PT_STK_HGT_MEAS,਀ऀऀ刀䔀䰀开倀吀开匀吀䬀开䠀䜀吀开唀伀䴀开䌀䐀Ⰰ
		REL_PT_STK_DIA_MEAS,਀ऀऀ刀䔀䰀开倀吀开匀吀䬀开䐀䤀䄀开唀伀䴀开䌀䐀Ⰰ
		REL_PT_GAS_VEL_MEAS,਀ऀऀ䔀开刀䔀䰀开倀吀开䜀䄀匀开嘀䔀䰀开唀伀䴀开䌀䐀Ⰰ
		REL_PT_GAS_FLOW_RATE_MEAS,਀ऀऀ䔀开刀䔀䰀开倀吀开䜀䄀匀开䘀䰀伀圀开刀䄀吀䔀开唀伀䴀开䌀䐀Ⰰ
		REL_PT_EXIT_GAS_TMP_MEAS,਀ऀऀ刀䔀䰀开倀吀开䘀一䌀开䰀䤀一䔀开䐀䤀匀吀开䴀䔀䄀匀Ⰰ
		REL_PT_FNC_LINE_DIST_UOM_CD,਀ऀऀ刀䔀䰀开倀吀开䘀䜀吀嘀开䠀䜀吀开䴀䔀䄀匀Ⰰ
		REL_PT_FGTV_HGT_UOM_CD,਀ऀऀ刀䔀䰀开倀吀开䘀䜀吀嘀开圀䤀䐀开䴀䔀䄀匀Ⰰ
		REL_PT_FGTV_WID_UOM_CD,਀ऀऀ刀䔀䰀开倀吀开䘀䜀吀嘀开䰀䔀一开䴀䔀䄀匀Ⰰ
		REL_PT_FGTV_LEN_UOM_CD,਀ऀऀ刀䔀䰀开倀吀开䘀䜀吀嘀开䄀一䜀䰀䔀开䴀䔀䄀匀Ⰰ
		REL_PT_CMNT,਀ऀऀ䘀䄀䌀开匀䤀吀䔀开䰀伀䌀开䤀一䐀Ⰰ
		LAT_MEAS,਀ऀऀ䰀伀一䜀开䴀䔀䄀匀Ⰰ
		UTM_EASTING,਀ऀऀ唀吀䴀开一伀刀吀䠀䤀一䜀Ⰰ
		UTM_ZONE,਀ऀऀ䔀开䠀伀刀娀开䌀伀䰀䰀开䴀吀䠀䐀开䌀䐀Ⰰ
		E_HORZ_REF_DATUM_CD,਀ऀऀ䔀开䜀䔀伀开刀䔀䘀开倀吀开䌀䐀Ⰰ
		DATA_COLL_DATE,਀ऀऀ䰀䄀匀吀开唀倀䐀䄀吀䔀䐀开䐀䄀吀䔀Ⰰ
		LAST_UPDATED_BY_USER_ID਀ऀ⤀
	(਀ऀऀ匀䔀䰀䔀䌀吀
			@p_new_fac_site_id,਀ऀऀऀ⠀
				SELECT REL_PT_ID FROM M_REL_PT਀ऀऀऀऀ圀䠀䔀刀䔀
					FAC_SITE_ID = @p_mstr_fac_site_id਀ऀऀऀऀऀ䄀一䐀 刀䔀䰀开倀吀开䤀䐀䔀一 㴀 䌀䄀匀吀⠀匀吀䄀䌀䬀开一伀 䄀匀 嘀䄀刀䌀䠀䄀刀⠀㈀　⤀⤀
			)													AS MSTR_REL_PT_ID,		਀ऀऀऀ䌀䄀匀吀⠀匀吀䄀䌀䬀开一伀 䄀匀 嘀䄀刀䌀䠀䄀刀⠀㈀　⤀⤀Ⰰ
			dbo.F_GET_XREF_VAL਀ऀऀऀ⠀
				@p_isteps_state_cd,਀ऀऀऀऀ✀刀倀吀夀倀䔀✀Ⰰ
				RPTYPE਀ऀऀऀ⤀ऀऀऀऀऀऀऀऀऀऀऀऀऀ䄀匀 䔀开刀䔀䰀开倀吀开吀夀倀䔀开䌀䐀Ⰰ
			'OP',਀ऀऀऀ一唀䰀䰀Ⰰ
			LTRIM(RTRIM(STACK_DESC)),਀ऀऀऀ匀吀䄀䌀䬀开䠀吀Ⰰ
			'FEET',਀ऀऀऀ匀吀䄀䌀䬀开䐀䤀䄀Ⰰ
			'FEET',਀ऀऀऀ䔀堀䤀吀嘀䔀䰀Ⰰ
			'FPS', ਀ऀऀऀ䜀䄀匀开䘀䰀伀圀刀吀Ⰰ
			'ACFM',਀ऀऀऀ䜀䄀匀开吀䔀䴀倀Ⰰ
			NULL,	--REL_PT_FNC_LINE_DIST_MEAS,਀ऀऀऀ一唀䰀䰀Ⰰऀⴀⴀ刀䔀䰀开倀吀开䘀一䌀开䰀䤀一䔀开䐀䤀匀吀开唀伀䴀开䌀䐀Ⰰ
			CASE਀ऀऀऀऀ圀䠀䔀一 搀戀漀⸀䘀开䜀䔀吀开堀刀䔀䘀开嘀䄀䰀 ⠀䀀瀀开椀猀琀攀瀀猀开猀琀愀琀攀开挀搀Ⰰ✀刀倀吀夀倀䔀✀Ⰰ刀倀吀夀倀䔀⤀ 㴀 ✀㄀✀ 吀䠀䔀一 䌀䄀匀吀⠀倀䰀唀䴀䔀开䠀吀 䄀匀 一唀䴀䔀刀䤀䌀⠀㌀Ⰰ　⤀⤀
				ELSE NULL਀ऀऀऀ䔀一䐀 䄀匀 刀䔀䰀开倀吀开䘀䜀吀嘀开䠀䜀吀开䴀䔀䄀匀Ⰰ
			CASE਀ऀऀऀऀ圀䠀䔀一 搀戀漀⸀䘀开䜀䔀吀开堀刀䔀䘀开嘀䄀䰀 ⠀䀀瀀开椀猀琀攀瀀猀开猀琀愀琀攀开挀搀Ⰰ✀刀倀吀夀倀䔀✀Ⰰ刀倀吀夀倀䔀⤀ 㴀 ✀㄀✀ 䄀一䐀 倀䰀唀䴀䔀开䠀吀 䤀匀 一伀吀 一唀䰀䰀 吀䠀䔀一 ✀䘀䔀䔀吀✀
				ELSE NULL਀ऀऀऀ䔀一䐀 䄀匀 刀䔀䰀开倀吀开䘀䜀吀嘀开䠀䜀吀开唀伀䴀开䌀䐀Ⰰ
			NULL,	--REL_PT_FGTV_WID_MEAS,਀ऀऀऀ一唀䰀䰀Ⰰऀⴀⴀ刀䔀䰀开倀吀开䘀䜀吀嘀开圀䤀䐀开唀伀䴀开䌀䐀Ⰰ
			NULL,	--REL_PT_FGTV_LEN_MEAS,਀ऀऀऀ一唀䰀䰀Ⰰऀⴀⴀ刀䔀䰀开倀吀开䘀䜀吀嘀开䰀䔀一开唀伀䴀开䌀䐀Ⰰ
			NULL,	--REL_PT_FGTV_ANGLE_MEAS,਀ऀऀऀ⠀
				SELECT						਀ऀऀऀऀऀ挀⸀䌀伀䴀䴀吀
				FROM਀ऀऀऀऀऀ䤀开䌀伀䴀䴀吀 挀
					INNER JOIN I_STACK s2 ON c.IDPARENT = s2.IDSTACK਀ऀऀऀऀ圀䠀䔀刀䔀
					c.IDPARENT = s.IDSTACK਀ऀऀऀऀऀ䄀一䐀 猀㈀⸀䤀䐀匀吀䄀䌀䬀 㴀 猀⸀䤀䐀匀吀䄀䌀䬀
					AND LTRIM(RTRIM(c.COMMTYPE)) = 'COMMENT'਀ऀऀऀ⤀ 䄀匀 刀䔀䰀开倀吀开䌀䴀一吀Ⰰ
			/* UNCOMMENT FOR DE਀ऀऀऀ⠀
				SELECT						਀ऀऀऀऀऀ挀⸀䌀伀䴀䴀吀
				FROM਀ऀऀऀऀऀ䤀开䌀伀䴀䴀吀 挀
					INNER JOIN I_STACK s2 ON c.IDPARENT = s2.IDSTACK਀ऀऀऀऀ圀䠀䔀刀䔀
					c.IDPARENT = s.IDSTACK਀ऀऀऀऀऀ䄀一䐀 猀㈀⸀䤀䐀匀吀䄀䌀䬀 㴀 猀⸀䤀䐀匀吀䄀䌀䬀
					AND LTRIM(RTRIM(c.COMMTYPE)) = 'ZX_ST_SK'਀ऀऀऀ⤀ 䄀匀 刀䔀䰀开倀吀开䌀䴀一吀Ⰰ
			*/਀ऀऀऀ　Ⰰ
			dbo.F_CONVERT_LAT_TO_DEC਀ऀऀऀऀ⠀
					SUBSTRING(CAST(STACK_LAT AS VARCHAR(255)),1,2),਀ऀऀऀऀऀ匀唀䈀匀吀刀䤀一䜀⠀䌀䄀匀吀⠀匀吀䄀䌀䬀开䰀䄀吀 䄀匀 嘀䄀刀䌀䠀䄀刀⠀㈀㔀㔀⤀⤀Ⰰ㌀Ⰰ㈀⤀Ⰰ
					SUBSTRING(CAST(STACK_LAT AS VARCHAR(255)),5,LEN(CAST(STACK_LAT AS VARCHAR(255))) - 5 + 1)਀ऀऀऀऀ⤀ऀऀऀऀऀऀऀऀऀऀऀऀ䄀匀 䰀䄀吀开䐀䔀䌀䤀䴀䄀䰀Ⰰ
				dbo.F_CONVERT_LONG_TO_DEC਀ऀऀऀऀ⠀
					SUBSTRING(CAST(STACK_LONG AS VARCHAR(255)),1,2),਀ऀऀऀऀऀ匀唀䈀匀吀刀䤀一䜀⠀䌀䄀匀吀⠀匀吀䄀䌀䬀开䰀伀一䜀 䄀匀 嘀䄀刀䌀䠀䄀刀⠀㈀㔀㔀⤀⤀Ⰰ㌀Ⰰ㈀⤀Ⰰ ⴀⴀ㐀Ⰰ㈀ 昀漀爀 猀漀洀攀 漀琀栀攀爀 猀琀愀琀攀猀
					SUBSTRING(CAST(STACK_LONG AS VARCHAR(255)),5,LEN(CAST(STACK_LONG AS VARCHAR(255))) - 5 + 1)  --6,LEN -6 +1 for some other states਀ऀऀऀऀ⤀ऀऀऀऀऀऀऀऀऀऀऀऀ䄀匀 䰀伀一䜀开䐀䔀䌀䤀䴀䄀䰀Ⰰ
				CASE਀ऀऀऀऀऀ圀䠀䔀一 唀吀䴀开䠀伀刀娀 䤀匀 一伀吀 一唀䰀䰀 吀䠀䔀一 唀吀䴀开䠀伀刀娀 ⨀ ㄀　　　
					ELSE NULL਀ऀऀऀऀ䔀一䐀Ⰰ
				CASE਀ऀऀऀऀऀ圀䠀䔀一 唀吀䴀开嘀䔀刀吀 䤀匀 一伀吀 一唀䰀䰀 吀䠀䔀一 唀吀䴀开嘀䔀刀吀 ⨀ ㄀　　　
					ELSE NULL਀ऀऀऀऀ䔀一䐀Ⰰ
			(਀ऀऀऀऀ匀䔀䰀䔀䌀吀
					p.UTM_ZONE਀ऀऀऀऀ䘀刀伀䴀
					I_STACK s਀ऀऀऀऀऀ䤀一一䔀刀 䨀伀䤀一 䤀开倀䰀䄀一吀 瀀 伀一 猀⸀䤀䐀倀䰀䄀一吀 㴀 瀀⸀䤀䐀倀䰀䄀一吀
				WHERE ਀ऀऀऀऀऀ䤀䐀匀吀䄀䌀䬀 㴀 䀀瀀开椀猀琀攀瀀猀开椀搀猀琀愀挀欀
			),਀ऀऀऀ一唀䰀䰀Ⰰ  ⴀⴀ䔀开䠀伀刀娀开䌀伀䰀䰀开䴀吀䠀䐀开䌀䐀Ⰰ
			NULL,  --E_HORZ_REF_DATUM_CD,਀ऀऀऀ一唀䰀䰀Ⰰ  ⴀⴀ䔀开䜀䔀伀开刀䔀䘀开倀吀开䌀䐀Ⰰ
			NULL,  --DATA_COLL_DATE,਀ऀऀऀ䜀䔀吀䐀䄀吀䔀⠀⤀Ⰰ
			1਀ऀऀ䘀刀伀䴀
			I_STACK s਀ऀऀ圀䠀䔀刀䔀
			s.IDSTACK = @p_isteps_idstack਀ऀ⤀㬀
਀ऀ匀䔀䰀䔀䌀吀 䀀瘀开攀爀爀开挀漀搀攀 㴀 䀀䀀䔀爀爀漀爀㬀
	SELECT @p_new_rel_pt_id = SCOPE_IDENTITY();਀ऀऀऀऀ
	਀ऀⴀⴀ䤀渀猀攀爀琀 䴀开刀䔀䰀开倀吀开䌀匀吀䴀开䘀䰀䐀开嘀䄀䰀
	If @v_err_code = 0਀ऀ䈀䔀䜀䤀一
		--ReleasePointSiteStack਀ऀऀ䤀一匀䔀刀吀 䤀一吀伀 䴀开刀䔀䰀开倀吀开䌀匀吀䴀开䘀䰀䐀开嘀䄀䰀
		(਀ऀऀऀ刀䔀䰀开倀吀开䤀䐀
		   ,A_CSTM_FLD_ID਀ऀऀ   Ⰰ䌀匀吀䴀开䘀䰀䐀开嘀䄀䰀
		)਀ऀऀ⠀
			SELECT਀ऀऀऀऀ䀀瀀开渀攀眀开爀攀氀开瀀琀开椀搀Ⰰ
				(SELECT A_CSTM_FLD_ID FROM A_CSTM_FLD WHERE FLD_NAME = 'ReleasePointSiteStackId' AND FLD_ENT_TYPE = 'ReleasePoint'),਀ऀऀऀऀ䰀吀刀䤀䴀⠀刀吀刀䤀䴀⠀匀䤀吀䔀开匀吀䄀䌀䬀⤀⤀
			FROM਀ऀऀऀऀ䤀开匀吀䄀䌀䬀
			WHERE਀ऀऀऀऀ䤀䐀匀吀䄀䌀䬀 㴀 䀀瀀开椀猀琀攀瀀猀开椀搀猀琀愀挀欀
				AND਀ऀऀऀऀ⠀
					SITE_STACK IS NOT NULL਀ऀऀऀऀऀ䄀一䐀 䰀吀刀䤀䴀⠀刀吀刀䤀䴀⠀匀䤀吀䔀开匀吀䄀䌀䬀⤀⤀ 㰀㸀 ✀✀
				)਀ऀऀ⤀㬀
		਀ऀऀ匀䔀䰀䔀䌀吀 䀀瘀开攀爀爀开挀漀搀攀 㴀 䀀䀀䔀爀爀漀爀㬀
	END;਀ऀ
	RETURN @v_err_code;਀䔀一䐀㬀