/******************************************************************************************************************************਀⨀⨀ 伀戀樀攀挀琀一愀洀攀㨀 嬀嬀倀开䤀䴀倀开䤀匀吀䔀倀匀开刀倀吀崀崀 **਀⨀⨀ 䄀甀琀栀漀爀㨀 䈀爀攀琀 匀洀椀琀栀 **਀⨀⨀ 䌀漀洀瀀愀渀礀 一愀洀攀㨀 圀椀渀搀猀漀爀 匀漀氀甀琀椀漀渀猀Ⰰ 䤀渀挀 **਀⨀⨀ 䐀攀猀挀爀椀瀀琀椀漀渀㨀ऀ䴀愀椀渀 瀀爀漀挀攀搀甀爀攀 昀漀爀 洀椀最爀愀琀椀渀最 愀 猀椀渀最氀攀 昀愀挀椀氀椀琀礀✀猀 搀愀琀愀 昀爀漀洀 椀ⴀ匀吀䔀倀匀 琀漀 匀䰀䔀䤀匀 **਀⨀⨀ ** Revision History:਀⨀⨀ ⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀ ** Date Name Description਀⨀⨀ ⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀ ** 09/11/2011 BSmith Original.਀⨀⨀ 　㈀⼀　㠀⼀㈀　㄀㈀ऀ䈀匀洀椀琀栀ऀऀ䌀栀愀渀最攀搀 䐀䔀 愀渀搀 䄀刀 昀漀爀洀愀琀 漀昀 䘀䄀䌀开䤀䐀䔀一 **਀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⨀⼀ ALTER PROCEDURE [dbo].[P_IMP_ISTEPS_RPT]਀ऀ䀀瀀开椀猀琀攀瀀猀开猀琀愀琀攀开挀搀ऀऀऀऀऀ䌀䠀䄀刀⠀㈀⤀Ⰰ @p_isteps_rpt_year NUMERIC(4,0),਀ऀ䀀瀀开爀攀瀀氀愀挀攀开洀猀琀爀开椀渀搀ऀऀऀऀऀ一唀䴀䔀刀䤀䌀⠀㄀Ⰰ　⤀ AS਀䈀䔀䜀䤀一 SET NOCOUNT ON;਀ऀ DECLARE @v_err_code INT,਀ऀ @v_isteps_idplant INT,਀ऀ @v_agn_rpt_id INT,਀ऀऀऀ䀀瘀开洀猀琀爀开昀愀挀开猀椀琀攀开椀搀ऀऀऀ䤀一吀Ⰰ @v_mstr_exists_ind NUMERIC(1,0),਀ऀऀऀ䀀瘀开渀攀眀开昀愀挀开猀椀琀攀开椀搀ऀऀऀ䤀一吀Ⰰ ਀ऀऀऀ䀀瘀开渀攀眀开爀攀氀开瀀琀开椀搀ऀऀऀ䤀一吀Ⰰ @v_isteps_idstack INT,਀ऀऀऀ @v_new_ctrl_devc_id INT,਀ऀऀऀ䀀瘀开椀猀琀攀瀀猀开椀搀挀漀渀琀爀漀氀ऀऀऀ䤀一吀Ⰰ ਀ऀऀऀ䀀瘀开渀攀眀开攀洀椀猀开甀渀椀琀开椀搀ऀऀऀ䤀一吀Ⰰ @v_isteps_idpoint INT,਀ऀऀऀ @v_new_unit_proc_id INT,਀ऀऀऀ䀀瘀开椀猀琀攀瀀猀开椀搀猀攀最洀攀渀琀ऀऀऀ䤀一吀Ⰰ @v_new_unit_proc_apch_id INT,਀ऀऀऀ䀀瘀开椀猀琀攀瀀猀开甀渀椀琀开瀀爀漀挀开愀瀀挀栀开椀搀ऀ䤀一吀㬀 ਀ऀऀऀ --Create temporary table for storing old and new id values needed਀ऀⴀⴀ眀栀攀渀 瀀漀瀀甀氀愀琀椀渀最 琀栀攀 愀猀猀漀挀椀愀琀椀瘀攀 攀渀琀椀琀椀攀猀 CREATE TABLE #TEMP਀ऀ⠀ AGN_RPT_ID INT,਀ऀऀ吀䄀䈀䰀䔀开一䄀䴀䔀ऀऀ嘀䄀刀䌀䠀䄀刀⠀㌀　⤀Ⰰ I_ID INT,਀ऀऀ䴀开䤀䐀ऀऀऀ䤀一吀 );਀ऀऀऀऀ ਀ऀ䈀䔀䜀䤀一 吀刀䄀一匀䄀䌀吀䤀伀一㬀 ਀ऀ匀䔀䰀䔀䌀吀 䀀瘀开攀爀爀开挀漀搀攀 㴀 䀀䀀䔀爀爀漀爀㬀 ਀ऀ DECLARE cur_FAC CURSOR FOR ਀ऀ匀䔀䰀䔀䌀吀 IDPLANT਀ऀ䘀刀伀䴀 I_PLANT਀ऀ圀䠀䔀刀䔀 YEAR_INV = @p_isteps_rpt_year;਀ OPEN cur_FAC;਀ऀ䘀䔀吀䌀䠀 一䔀堀吀 䘀刀伀䴀 挀甀爀开䘀䄀䌀 䤀一吀伀 䀀瘀开椀猀琀攀瀀猀开椀搀瀀氀愀渀琀㬀 WHILE @@FETCH_STATUS = 0਀ऀ䈀䔀䜀䤀一 SET @v_mstr_fac_site_id = NULL;਀ऀऀ匀䔀吀 䀀瘀开洀猀琀爀开攀砀椀猀琀猀开椀渀搀 㴀 一唀䰀䰀㬀 SET @v_agn_rpt_id = NULL;਀ऀऀ --Determine if master facility record has already been created਀ऀऀ䤀䘀 䀀瘀开攀爀爀开挀漀搀攀 㴀 　 BEGIN਀ऀऀऀ匀䔀䰀䔀䌀吀 @v_mstr_fac_site_id = FAC_SITE_ID਀ऀऀऀ䘀刀伀䴀 M_FAC_SITE਀ऀऀऀ圀䠀䔀刀䔀 FAC_SITE_MSTR_IND = 1 AND਀ऀऀऀऀ䄀䜀一开刀倀吀开䤀䐀 䤀匀 一唀䰀䰀 䄀一䐀 FAC_SITE_IDEN = (਀ऀऀऀऀऀऀऀऀऀ匀䔀䰀䔀䌀吀 CASE਀ऀऀऀऀऀऀऀऀऀऀऀ圀䠀䔀一 䀀瀀开椀猀琀攀瀀猀开猀琀愀琀攀开挀搀 䤀一 ⠀✀䄀刀✀⤀ 吀䠀䔀一 瀀⸀匀吀䄀吀䔀开䌀伀䐀䔀 ⬀ 瀀⸀䌀一吀夀开䌀伀䐀䔀 ⬀ ✀ⴀ✀ ⬀ 瀀⸀倀䰀䄀一吀开䤀䐀 WHEN @p_isteps_state_cd IN ('DE') THEN p.STATE_CODE + p.CNTY_CODE + p.PLANT_ID਀ऀऀऀऀऀऀऀऀऀऀऀ䔀䰀匀䔀 瀀⸀匀吀䄀吀䔀开䌀伀䐀䔀 ⬀ ✀ⴀ✀ ⬀ 瀀⸀䌀一吀夀开䌀伀䐀䔀 ⬀ ✀ⴀ✀ ⬀ 瀀⸀倀䰀䄀一吀开䤀䐀 END਀ऀऀऀऀऀऀऀऀऀ䘀刀伀䴀 I_PLANT p਀ऀऀऀऀऀऀऀऀऀ圀䠀䔀刀䔀 p.IDPLANT = @v_isteps_idplant਀ऀऀऀऀऀऀऀऀऀऀ䄀一䐀 瀀⸀夀䔀䄀刀开䤀一嘀 㴀 䀀瀀开椀猀琀攀瀀猀开爀瀀琀开礀攀愀爀 );਀ऀऀऀऀऀऀऀऀऀऀऀ --If needed, create Master record with bare minimum fields and get new master fac_site_id਀ऀऀऀ䤀䘀 䀀瘀开洀猀琀爀开昀愀挀开猀椀琀攀开椀搀 䤀匀 一唀䰀䰀  BEGIN਀ऀऀऀऀ匀䔀吀 䀀瘀开洀猀琀爀开攀砀椀猀琀猀开椀渀搀 㴀 　㬀 EXECUTE @v_err_code = P_IMP_ISTEPS_MSTR @p_isteps_state_cd, @v_isteps_idplant, @v_mstr_fac_site_id OUTPUT; ਀ऀऀऀ䔀一䐀 䔀䰀匀䔀 BEGIN਀ऀऀऀऀ匀䔀吀 䀀瘀开洀猀琀爀开攀砀椀猀琀猀开椀渀搀 㴀 ㄀㬀 END;਀ऀऀ䔀一䐀㬀 ਀ऀऀ IF @v_err_code = 0਀ऀऀ䈀䔀䜀䤀一 --Get @v_agn_rpt_id for reporting year and facility if it exists਀ऀऀऀ匀䔀䰀䔀䌀吀 @v_agn_rpt_id = ar.AGN_RPT_ID਀ऀऀऀ䘀刀伀䴀   M_AGN_RPT ar਀ऀऀऀऀ䤀一一䔀刀 䨀伀䤀一 䴀开䘀䄀䌀开匀䤀吀䔀 昀猀 伀一 愀爀⸀䘀䄀䌀开匀䤀吀䔀开䤀䐀 㴀 昀猀⸀䘀䄀䌀开匀䤀吀䔀开䤀䐀 WHERE਀ऀऀऀऀ愀爀⸀刀倀吀开夀䔀䄀刀 㴀 䀀瀀开椀猀琀攀瀀猀开爀瀀琀开礀攀愀爀 䄀一䐀 fs.FAC_SITE_IDEN = (਀ऀऀऀऀऀऀऀऀऀऀ匀䔀䰀䔀䌀吀 CASE਀ऀऀऀऀऀऀऀऀऀऀऀऀ圀䠀䔀一 䀀瀀开椀猀琀攀瀀猀开猀琀愀琀攀开挀搀 䤀一 ⠀✀䄀刀✀⤀ 吀䠀䔀一 瀀⸀匀吀䄀吀䔀开䌀伀䐀䔀 ⬀ 瀀⸀䌀一吀夀开䌀伀䐀䔀 ⬀ ✀ⴀ✀ ⬀ 瀀⸀倀䰀䄀一吀开䤀䐀 WHEN @p_isteps_state_cd IN ('DE') THEN p.STATE_CODE + p.CNTY_CODE + p.PLANT_ID਀ऀऀऀऀऀऀऀऀऀऀऀऀ䔀䰀匀䔀 瀀⸀匀吀䄀吀䔀开䌀伀䐀䔀 ⬀ ✀ⴀ✀ ⬀ 瀀⸀䌀一吀夀开䌀伀䐀䔀 ⬀ ✀ⴀ✀ ⬀ 瀀⸀倀䰀䄀一吀开䤀䐀 END਀ऀऀऀऀऀऀऀऀऀऀ䘀刀伀䴀 I_PLANT p਀ऀऀऀऀऀऀऀऀऀऀ圀䠀䔀刀䔀 p.IDPLANT = @v_isteps_idplant਀ऀऀऀऀऀऀऀऀऀऀऀ䄀一䐀 瀀⸀夀䔀䄀刀开䤀一嘀 㴀 䀀瀀开椀猀琀攀瀀猀开爀瀀琀开礀攀愀爀 );਀ऀऀऀ IF @v_agn_rpt_id IS NOT NULL ਀ऀऀऀ䈀䔀䜀䤀一 --Delete the current report data (keeping M_AGN_RPT record) from agency data store, if it exists, prior to re-loading਀ऀऀऀऀ䔀堀䔀䌀唀吀䔀 䀀瘀开攀爀爀开挀漀搀攀 㴀 倀开䐀䔀䰀开䴀开刀倀吀开䐀䄀吀䄀 䀀瘀开愀最渀开爀瀀琀开椀搀㬀 ਀ऀऀऀ䔀一䐀 䔀䰀匀䔀 BEGIN਀ऀऀऀऀⴀⴀ䤀渀猀攀爀琀 愀 爀攀瀀漀爀琀 猀琀甀戀 琀漀 栀愀渀最 爀攀瀀漀爀琀 搀愀琀愀 漀昀昀 漀昀 INSERT INTO M_AGN_RPT਀ऀऀऀऀ⠀ FAC_SITE_ID,਀ऀऀऀऀऀ倀唀䈀䰀䤀匀䠀䔀䐀开䤀一䐀Ⰰ RPT_YEAR,਀ऀऀऀऀऀ刀倀吀开倀刀䐀开匀吀䄀刀吀开䐀䄀吀䔀Ⰰ RPT_PRD_END_DATE,਀ऀऀऀऀऀ刀倀吀开䐀唀䔀开䐀䄀吀䔀Ⰰ RPT_RCVD_DATE,਀ऀऀऀऀऀ刀倀吀开䰀伀䄀䐀开䐀䄀吀䔀Ⰰ RPT_COMPLETE_DATE਀ऀऀऀऀ⤀ VALUES਀ऀऀऀऀ⠀ @v_mstr_fac_site_id,਀ऀऀऀऀऀ　Ⰰ @p_isteps_rpt_year,਀ऀऀऀऀऀ䌀䄀匀吀⠀✀㄀ⴀ䨀䄀一ⴀ✀ ⬀ 䌀䄀匀吀⠀䀀瀀开椀猀琀攀瀀猀开爀瀀琀开礀攀愀爀 䄀匀 嘀䄀刀䌀䠀䄀刀⠀㐀⤀⤀ 䄀匀 䐀䄀吀䔀吀䤀䴀䔀⤀Ⰰ CAST('31-DEC-' + CAST(@p_isteps_rpt_year AS VARCHAR(4)) AS DATETIME),਀ऀऀऀऀऀ䌀䄀匀吀⠀✀㌀㄀ⴀ䐀䔀䌀ⴀ✀ ⬀ 䌀䄀匀吀⠀䀀瀀开椀猀琀攀瀀猀开爀瀀琀开礀攀愀爀 䄀匀 嘀䄀刀䌀䠀䄀刀⠀㐀⤀⤀ 䄀匀 䐀䄀吀䔀吀䤀䴀䔀⤀Ⰰ CAST('31-DEC-' + CAST(@p_isteps_rpt_year AS VARCHAR(4)) AS DATETIME),਀ऀऀऀऀऀ䌀䄀匀吀⠀✀㌀㄀ⴀ䐀䔀䌀ⴀ✀ ⬀ 䌀䄀匀吀⠀䀀瀀开椀猀琀攀瀀猀开爀瀀琀开礀攀愀爀 䄀匀 嘀䄀刀䌀䠀䄀刀⠀㐀⤀⤀ 䄀匀 䐀䄀吀䔀吀䤀䴀䔀⤀Ⰰ CAST('31-DEC-' + CAST(@p_isteps_rpt_year AS VARCHAR(4)) AS DATETIME)਀ऀऀऀऀ⤀㬀 ਀ऀऀऀऀ SELECT @v_err_code = @@Error;਀ऀऀऀऀ匀䔀䰀䔀䌀吀 䀀瘀开愀最渀开爀瀀琀开椀搀 㴀 匀䌀伀倀䔀开䤀䐀䔀一吀䤀吀夀⠀⤀㬀ऀ END;਀ऀऀ䔀一䐀㬀ऀ ਀ऀऀⴀⴀ䤀一匀䔀刀吀 䴀开䘀䄀䌀开匀䤀吀䔀 IF @v_err_code = 0਀ऀऀ䈀䔀䜀䤀一 EXECUTE @v_err_code = P_IMP_ISTEPS_FAC @v_agn_rpt_id,@v_isteps_idplant,@p_isteps_state_cd,@v_new_fac_site_id OUTPUT;਀ऀऀ䔀一䐀㬀 ਀ऀऀ --Insert M_REL_PT਀ऀऀ䤀昀 䀀瘀开攀爀爀开挀漀搀攀 㴀 　 BEGIN ਀ऀऀऀ䐀䔀䌀䰀䄀刀䔀 挀甀爀开刀䔀䰀开倀吀 䌀唀刀匀伀刀 䘀伀刀  SELECT਀ऀऀऀऀ䤀䐀匀吀䄀䌀䬀 FROM਀ऀऀऀऀ䤀开匀吀䄀䌀䬀 猀 INNER JOIN I_PLANT p ON s.IDPLANT = p.IDPLANT਀ऀऀऀ圀䠀䔀刀䔀 p.IDPLANT = @v_isteps_idplant;਀ OPEN cur_REL_PT;਀ऀऀऀ FETCH NEXT FROM cur_REL_PT INTO @v_isteps_idstack;਀ऀऀऀ圀䠀䤀䰀䔀 䀀䀀䘀䔀吀䌀䠀开匀吀䄀吀唀匀 㴀 　 BEGIN਀ऀऀऀऀ䤀昀 䀀瘀开攀爀爀开挀漀搀攀 㴀 　 BEGIN਀ऀऀऀऀऀ匀䔀吀 䀀瘀开渀攀眀开爀攀氀开瀀琀开椀搀 㴀 一唀䰀䰀㬀 ਀ऀऀऀऀऀ䔀堀䔀䌀唀吀䔀 䀀瘀开攀爀爀开挀漀搀攀 㴀 倀开䤀䴀倀开䤀匀吀䔀倀匀开刀䔀䰀开倀吀 䀀瀀开椀猀琀攀瀀猀开猀琀愀琀攀开挀搀Ⰰ 䀀瘀开洀猀琀爀开昀愀挀开猀椀琀攀开椀搀Ⰰ 䀀瘀开渀攀眀开昀愀挀开猀椀琀攀开椀搀Ⰰ䀀瘀开椀猀琀攀瀀猀开椀搀猀琀愀挀欀Ⰰ䀀瘀开渀攀眀开爀攀氀开瀀琀开椀搀 伀唀吀倀唀吀㬀 ਀ऀऀऀऀऀ䤀一匀䔀刀吀 䤀一吀伀 ⌀吀䔀䴀倀 ⠀䄀䜀一开刀倀吀开䤀䐀Ⰰ吀䄀䈀䰀䔀开一䄀䴀䔀Ⰰ䤀开䤀䐀Ⰰ䴀开䤀䐀⤀ 嘀䄀䰀唀䔀匀 ⠀䀀瘀开愀最渀开爀瀀琀开椀搀Ⰰ✀刀䔀䰀开倀吀✀Ⰰ䀀瘀开椀猀琀攀瀀猀开椀搀猀琀愀挀欀Ⰰ䀀瘀开渀攀眀开爀攀氀开瀀琀开椀搀⤀㬀 FETCH NEXT FROM cur_REL_PT INTO @v_isteps_idstack;਀ऀऀऀऀ䔀一䐀 ELSE BEGIN਀ऀऀऀऀऀ䈀刀䔀䄀䬀㬀 END;਀ऀऀऀ䔀一䐀㬀 ਀ऀऀऀ䌀䰀伀匀䔀 挀甀爀开刀䔀䰀开倀吀㬀 DEALLOCATE cur_REL_PT;਀ऀऀ䔀一䐀㬀 ਀ऀऀ --Insert M_CTRL_DEVC਀ऀऀ䤀昀 䀀瘀开攀爀爀开挀漀搀攀 㴀 　 BEGIN ਀ऀऀऀ䐀䔀䌀䰀䄀刀䔀 挀甀爀开䌀吀刀䰀开䐀䔀嘀䌀 䌀唀刀匀伀刀 䘀伀刀  SELECT਀ऀऀऀऀ䤀䐀䌀伀一吀刀伀䰀 FROM਀ऀऀऀऀ䤀开䌀伀一吀刀伀䰀 挀 INNER JOIN I_PLANT p ON c.IDPLANT = p.IDPLANT਀ऀऀऀ圀䠀䔀刀䔀 p.IDPLANT = @v_isteps_idplant;਀ OPEN cur_CTRL_DEVC;਀ऀऀऀ FETCH NEXT FROM cur_CTRL_DEVC INTO @v_isteps_idcontrol;਀ऀऀऀ圀䠀䤀䰀䔀 䀀䀀䘀䔀吀䌀䠀开匀吀䄀吀唀匀 㴀 　 BEGIN਀ऀऀऀऀ䤀昀 䀀瘀开攀爀爀开挀漀搀攀 㴀 　 BEGIN਀ऀऀऀऀऀ匀䔀吀 䀀瘀开渀攀眀开挀琀爀氀开搀攀瘀挀开椀搀 㴀 一唀䰀䰀㬀 ਀ऀऀऀऀऀ䔀堀䔀䌀唀吀䔀 䀀瘀开攀爀爀开挀漀搀攀 㴀 倀开䤀䴀倀开䤀匀吀䔀倀匀开䌀吀刀䰀开䐀䔀嘀䌀 䀀瀀开椀猀琀攀瀀猀开猀琀愀琀攀开挀搀Ⰰ 䀀瘀开洀猀琀爀开昀愀挀开猀椀琀攀开椀搀Ⰰ 䀀瘀开渀攀眀开昀愀挀开猀椀琀攀开椀搀Ⰰ 䀀瘀开椀猀琀攀瀀猀开椀搀挀漀渀琀爀漀氀Ⰰ 䀀瘀开渀攀眀开挀琀爀氀开搀攀瘀挀开椀搀 伀唀吀倀唀吀㬀 ਀ऀऀऀऀऀ䤀一匀䔀刀吀 䤀一吀伀 ⌀吀䔀䴀倀 ⠀䄀䜀一开刀倀吀开䤀䐀Ⰰ吀䄀䈀䰀䔀开一䄀䴀䔀Ⰰ䤀开䤀䐀Ⰰ䴀开䤀䐀⤀ 嘀䄀䰀唀䔀匀 ⠀䀀瘀开愀最渀开爀瀀琀开椀搀Ⰰ✀䌀吀刀䰀开䐀䔀嘀䌀✀Ⰰ䀀瘀开椀猀琀攀瀀猀开椀搀挀漀渀琀爀漀氀Ⰰ䀀瘀开渀攀眀开挀琀爀氀开搀攀瘀挀开椀搀⤀㬀 FETCH NEXT FROM cur_CTRL_DEVC INTO @v_isteps_idcontrol;਀ऀऀऀऀ䔀一䐀 ELSE BEGIN਀ऀऀऀऀऀ䈀刀䔀䄀䬀㬀 END;਀ऀऀऀ䔀一䐀㬀 ਀ऀऀऀ䌀䰀伀匀䔀 挀甀爀开䌀吀刀䰀开䐀䔀嘀䌀㬀 DEALLOCATE cur_CTRL_DEVC;਀ऀऀ䔀一䐀㬀 ਀ऀऀ --Insert M_EMIS_UNIT਀ऀऀ䤀昀 䀀瘀开攀爀爀开挀漀搀攀 㴀 　 BEGIN ਀ऀऀऀ䐀䔀䌀䰀䄀刀䔀 挀甀爀开䔀䴀䤀匀开唀一䤀吀 䌀唀刀匀伀刀 䘀伀刀  SELECT਀ऀऀऀऀ䤀䐀倀伀䤀一吀 FROM਀ऀऀऀऀ䤀开倀伀䤀一吀 瀀琀 INNER JOIN I_PLANT p ON pt.IDPLANT = p.IDPLANT਀ऀऀऀ圀䠀䔀刀䔀 p.IDPLANT = @v_isteps_idplant;਀ OPEN cur_EMIS_UNIT;਀ऀऀऀ FETCH NEXT FROM cur_EMIS_UNIT INTO @v_isteps_idpoint;਀ऀऀऀ圀䠀䤀䰀䔀 䀀䀀䘀䔀吀䌀䠀开匀吀䄀吀唀匀 㴀 　 BEGIN਀ऀऀऀऀ䤀昀 䀀瘀开攀爀爀开挀漀搀攀 㴀 　 BEGIN਀ऀऀऀऀऀ匀䔀吀 䀀瘀开渀攀眀开攀洀椀猀开甀渀椀琀开椀搀 㴀 一唀䰀䰀㬀 ਀ऀऀऀऀऀ䔀堀䔀䌀唀吀䔀 䀀瘀开攀爀爀开挀漀搀攀 㴀 倀开䤀䴀倀开匀吀䔀倀匀开䔀䴀䤀匀开唀一䤀吀 䀀瀀开椀猀琀攀瀀猀开猀琀愀琀攀开挀搀Ⰰ 䀀瘀开洀猀琀爀开昀愀挀开猀椀琀攀开椀搀Ⰰ䀀瘀开渀攀眀开昀愀挀开猀椀琀攀开椀搀Ⰰ䀀瘀开椀猀琀攀瀀猀开椀搀瀀漀椀渀琀Ⰰ䀀瘀开渀攀眀开攀洀椀猀开甀渀椀琀开椀搀 伀唀吀倀唀吀㬀 ਀ऀऀऀऀऀⴀⴀ䤀渀猀攀爀琀 䴀开唀一䤀吀开倀刀伀䌀 If @v_err_code = 0਀ऀऀऀऀऀ䈀䔀䜀䤀一 DECLARE cur_UNIT_PROC CURSOR FOR ਀ऀऀऀऀऀऀ匀䔀䰀䔀䌀吀 IDSEGMENT਀ऀऀऀऀऀऀ䘀刀伀䴀 I_SEGMENT s਀ऀऀऀऀऀऀऀ䤀一一䔀刀 䨀伀䤀一 䤀开倀伀䤀一吀 瀀 伀一 猀⸀䤀䐀倀伀䤀一吀 㴀 瀀⸀䤀䐀倀伀䤀一吀 WHERE਀ऀऀऀऀऀऀऀ瀀⸀䤀䐀倀伀䤀一吀 㴀 䀀瘀开椀猀琀攀瀀猀开椀搀瀀漀椀渀琀㬀 ਀ऀऀऀऀऀऀ伀倀䔀一 挀甀爀开唀一䤀吀开倀刀伀䌀㬀 ਀ऀऀऀऀऀऀ䘀䔀吀䌀䠀 一䔀堀吀 䘀刀伀䴀 挀甀爀开唀一䤀吀开倀刀伀䌀 䤀一吀伀 䀀瘀开椀猀琀攀瀀猀开椀搀猀攀最洀攀渀琀㬀 WHILE @@FETCH_STATUS = 0਀ऀऀऀऀऀऀ䈀䔀䜀䤀一 If @v_err_code = 0਀ऀऀऀऀऀऀऀ䈀䔀䜀䤀一 SET @v_new_rel_pt_id = NULL;਀ऀऀऀऀऀऀऀऀ匀䔀吀 䀀瘀开渀攀眀开甀渀椀琀开瀀爀漀挀开椀搀 㴀 一唀䰀䰀㬀 SET @v_new_unit_proc_apch_id = NULL;਀ऀऀऀऀऀऀऀऀ EXECUTE @v_err_code = P_IMP_ISTEPS_UNIT_PROC @p_isteps_state_cd, @v_mstr_fac_site_id, @v_new_fac_site_id, @v_isteps_idpoint, @v_new_emis_unit_id, @v_isteps_idsegment, @v_new_unit_proc_id OUTPUT, @v_new_unit_proc_apch_id OUTPUT, @v_new_rel_pt_id OUTPUT;਀ऀऀऀऀऀऀऀऀ --Insert associative entity data M_UNIT_PROC_CTRL_DEVC਀ऀऀऀऀऀऀऀऀ䤀昀 䀀瘀开攀爀爀开挀漀搀攀 㴀 　 BEGIN਀ऀऀऀऀऀऀऀऀऀ䤀一匀䔀刀吀 䤀一吀伀 䴀开唀一䤀吀开倀刀伀䌀开䌀吀刀䰀开䐀䔀嘀䌀 (਀ऀऀऀऀऀऀऀऀऀऀ唀一䤀吀开倀刀伀䌀开䌀吀刀䰀开䄀倀䌀䠀开䤀䐀Ⰰ CTRL_DEVC_ID,਀ऀऀऀऀऀऀऀऀऀऀ匀䔀儀开一唀䴀 )਀ऀऀऀऀऀऀऀऀऀ⠀ SELECT਀ऀऀऀऀऀऀऀऀऀऀऀ䀀瘀开渀攀眀开甀渀椀琀开瀀爀漀挀开愀瀀挀栀开椀搀Ⰰ t.M_ID,਀ऀऀऀऀऀऀऀऀऀऀऀ挀渀⸀匀䔀儀唀䔀一䌀䔀 FROM਀ऀऀऀऀऀऀऀऀऀऀऀ䤀开䌀吀刀䰀匀䌀一伀 挀渀 INNER JOIN I_SEGMENT s ON cn.IDSEGMENT = s.IDSEGMENT਀ऀऀऀऀऀऀऀऀऀऀऀ䤀一一䔀刀 䨀伀䤀一 䤀开䌀伀一吀刀伀䰀 挀 伀一 挀渀⸀䤀䐀䌀伀一吀刀伀䰀 㴀 挀⸀䤀䐀䌀伀一吀刀伀䰀 INNER JOIN #TEMP t ON (cn.IDCONTROL = t.I_ID AND t.TABLE_NAME = 'CTRL_DEVC' AND t.AGN_RPT_ID = @v_agn_rpt_id)਀ऀऀऀऀऀऀऀऀऀऀ圀䠀䔀刀䔀 cn.IDSEGMENT = @v_isteps_idsegment਀ऀऀऀऀऀऀऀऀऀ⤀㬀 SELECT @v_err_code = @@Error;਀ऀऀऀऀऀऀऀऀ䔀一䐀㬀 ਀ऀऀ --Insert M_RPT_PRD and M_RPT_PRD_EMIS ਀ऀऀऀऀऀऀऀऀ䤀昀 䀀瘀开攀爀爀开挀漀搀攀 㴀 　 BEGIN਀ऀऀऀऀऀऀऀऀऀ䔀堀䔀䌀唀吀䔀 䀀瘀开攀爀爀开挀漀搀攀 㴀 倀开䤀䴀倀开䤀匀吀䔀倀匀开刀倀吀开倀刀䐀 䀀瀀开椀猀琀攀瀀猀开猀琀愀琀攀开挀搀Ⰰ䀀瘀开椀猀琀攀瀀猀开椀搀猀攀最洀攀渀琀Ⰰ䀀瘀开渀攀眀开甀渀椀琀开瀀爀漀挀开椀搀㬀 END;਀ऀऀऀ ਀ऀऀऀऀऀऀऀऀⴀⴀ䤀昀 愀 昀愀挀椀氀椀琀礀ⴀ眀椀搀攀 昀甀最椀琀椀瘀攀 爀攀氀攀愀猀攀 瀀漀椀渀琀 眀愀猀 椀渀猀攀爀琀攀搀 搀甀爀椀渀最 甀渀椀琀 瀀爀漀挀攀猀猀 洀椀最爀愀琀椀漀渀Ⰰ 愀搀搀 琀漀 琀攀洀瀀 琀愀戀氀攀 猀漀 椀琀 最攀琀猀 愀猀猀漀挀椀愀琀攀搀 眀椀琀栀 甀渀椀琀 瀀爀漀挀攀猀猀 氀愀琀攀爀 IF @v_new_rel_pt_id IS NOT NULL਀ऀऀऀऀऀऀऀऀ䈀䔀䜀䤀一 INSERT INTO #TEMP (AGN_RPT_ID,TABLE_NAME,I_ID,M_ID) VALUES (@v_agn_rpt_id,'REL_PT',0,@v_new_rel_pt_id);਀ऀऀऀऀऀऀऀऀ䔀一䐀㬀 ਀ऀऀऀऀऀऀऀऀ䤀一匀䔀刀吀 䤀一吀伀 ⌀吀䔀䴀倀 ⠀䄀䜀一开刀倀吀开䤀䐀Ⰰ吀䄀䈀䰀䔀开一䄀䴀䔀Ⰰ䤀开䤀䐀Ⰰ䴀开䤀䐀⤀ 嘀䄀䰀唀䔀匀 ⠀䀀瘀开愀最渀开爀瀀琀开椀搀Ⰰ✀唀一䤀吀开倀刀伀䌀✀Ⰰ䀀瘀开椀猀琀攀瀀猀开椀搀猀攀最洀攀渀琀Ⰰ䀀瘀开渀攀眀开甀渀椀琀开瀀爀漀挀开椀搀⤀㬀ऀ FETCH NEXT FROM cur_UNIT_PROC INTO @v_isteps_idsegment;਀ऀऀऀऀऀऀऀ䔀一䐀 ELSE BEGIN਀ऀऀऀऀऀऀऀऀ䈀刀䔀䄀䬀㬀 END;਀ऀऀऀऀऀऀ䔀一䐀㬀 ਀ऀऀऀऀऀऀ䌀䰀伀匀䔀 挀甀爀开唀一䤀吀开倀刀伀䌀㬀 DEALLOCATE cur_UNIT_PROC;਀ऀऀऀऀऀ䔀一䐀㬀 ਀ऀऀऀऀऀ䘀䔀吀䌀䠀 一䔀堀吀 䘀刀伀䴀 挀甀爀开䔀䴀䤀匀开唀一䤀吀 䤀一吀伀 䀀瘀开椀猀琀攀瀀猀开椀搀瀀漀椀渀琀㬀 END਀ऀऀऀऀ䔀䰀匀䔀 䈀䔀䜀䤀一 BREAK;਀ऀऀऀऀ䔀一䐀㬀 END;਀ऀऀऀ CLOSE cur_EMIS_UNIT;਀ऀऀऀ䐀䔀䄀䰀䰀伀䌀䄀吀䔀 挀甀爀开䔀䴀䤀匀开唀一䤀吀㬀 END;਀ऀऀ ਀ऀऀⴀⴀ䤀渀猀攀爀琀 愀猀猀漀挀椀愀琀椀瘀攀 攀渀琀椀琀礀 搀愀琀愀 䴀开唀一䤀吀开倀刀伀䌀开刀䔀䰀开倀吀 If @v_err_code = 0਀ऀऀ䈀䔀䜀䤀一 INSERT INTO M_UNIT_PROC_REL_PT਀ऀऀऀ⠀ REL_PT_ID,਀ऀऀऀऀ唀一䤀吀开倀刀伀䌀开䤀䐀Ⰰ AVG_PCT_EMIS਀ऀऀऀ⤀ (਀ऀऀऀऀ匀䔀䰀䔀䌀吀 t1.M_ID,਀ऀऀऀऀऀ琀㈀⸀䴀开䤀䐀Ⰰ 100਀ऀऀऀऀ䘀刀伀䴀 I_SEGMENT s਀ऀऀऀऀऀ䤀一一䔀刀 䨀伀䤀一 ⌀吀䔀䴀倀 琀㄀ 伀一 ⠀⠀猀⸀䤀䐀匀吀䄀䌀䬀 㴀 琀㄀⸀䤀开䤀䐀 伀刀 猀⸀䤀䐀匀吀䄀䌀䬀 䤀匀 一唀䰀䰀⤀ 䄀一䐀 琀㄀⸀吀䄀䈀䰀䔀开一䄀䴀䔀 㴀 ✀刀䔀䰀开倀吀✀ 䄀一䐀 琀㄀⸀䄀䜀一开刀倀吀开䤀䐀 㴀 䀀瘀开愀最渀开爀瀀琀开椀搀⤀ INNER JOIN #TEMP t2 ON (s.IDSEGMENT = t2.I_ID AND t2.TABLE_NAME = 'UNIT_PROC' AND t2.AGN_RPT_ID = @v_agn_rpt_id)਀ऀऀऀ⤀㬀 SELECT @v_err_code = @@Error;਀ऀऀ䔀一䐀㬀 ਀ऀऀ --Log LOAD in AGN_RPT_EVENT_LOG਀ऀऀ䤀昀 䀀瘀开攀爀爀开挀漀搀攀 㴀 　 BEGIN਀ऀऀऀ䤀一匀䔀刀吀 䤀一吀伀 䴀开䄀䜀一开刀倀吀开䔀嘀䔀一吀开䰀伀䜀 ⠀䄀䜀一开刀倀吀开䤀䐀Ⰰ 䔀嘀䔀一吀开吀夀倀䔀Ⰰ 䔀嘀䔀一吀开䐀䄀吀䔀⤀ 嘀䄀䰀唀䔀匀 ⠀䀀瘀开愀最渀开爀瀀琀开椀搀Ⰰ✀䴀䤀䜀刀䄀吀䔀䐀✀Ⰰ䜀䔀吀䐀䄀吀䔀⠀⤀⤀㬀 SELECT @v_err_code = @@Error;਀ऀऀ䔀一䐀㬀 ਀ऀऀ --Promote report to master if new facility, or for existing facility, if replace indicator is true਀ऀऀ䤀䘀 䀀瘀开攀爀爀开挀漀搀攀 㴀 　 BEGIN ਀ऀऀऀ䤀䘀 ⠀⠀䀀瘀开洀猀琀爀开攀砀椀猀琀猀开椀渀搀 㴀 ㄀ 䄀一䐀 䀀瀀开爀攀瀀氀愀挀攀开洀猀琀爀开椀渀搀 㴀 ㄀⤀ 伀刀 䀀瘀开洀猀琀爀开攀砀椀猀琀猀开椀渀搀 㴀 　⤀ BEGIN਀ऀऀऀऀⴀⴀ倀爀漀洀漀琀攀 爀攀瀀漀爀琀 琀漀 洀愀猀琀攀爀 EXECUTE P_PROMOTE_RPT @v_agn_rpt_id, @v_err_code;਀ऀऀऀ䔀一䐀㬀 END;਀ऀऀ FETCH NEXT FROM cur_FAC INTO @v_isteps_idplant;਀ऀ䔀一䐀㬀 ਀ऀ䌀䰀伀匀䔀 挀甀爀开䘀䄀䌀㬀 DEALLOCATE cur_FAC;਀ऀ ਀ऀ䤀䘀 䀀瘀开攀爀爀开挀漀搀攀 㴀 　  BEGIN਀ऀऀ䌀伀䴀䴀䤀吀 吀刀䄀一匀䄀䌀吀䤀伀一㬀 END਀ऀ䔀䰀匀䔀 䈀䔀䜀䤀一 ROLLBACK TRANSACTION;਀ऀ䔀一䐀㬀 ਀ऀ刀䔀吀唀刀一 䀀瘀开攀爀爀开挀漀搀攀㬀 END