if exists ( select * from sys.all_objects ao where ao.name = 'sp_MtrSchetLoadisAdditional_Bulk_3_3' ) DROP PROCEDURE [dbo].[sp_MtrSchetLoadisAdditional_Bulk_3_3]
GO

CREATE PROCEDURE [dbo].[sp_MtrSchetLoadisAdditional_Bulk_3_3]
  @SchetID uniqueidentifier
AS 
BEGIN

INSERT INTO [IES].[T_SCHET_SLUCH_SANK] (
[SchetSluchSankID], [SchetSluchAccomplished], [S_CODE], [S_SUM], [S_TIP], [S_OSN], [S_COM], [S_IST], [DATE_ACT], [NUM_ACT], [S_TIP_F006], [SchetSluch]
)
SELECT 
 sank.SchetSluchSankID AS [SchetSluchSankID]
,zSL.[SchetSluchAccomplishedID] AS [SchetSluchAccomplished]
,sank.[S_CODE] AS [S_CODE]
,sank.[S_SUM] AS [S_SUM]
,sank.[S_TIP] AS [S_TIP]
,(select top 1 f014.[F014DenyReasonID] from [IES].[T_F014_DENY_REASON] f014 where f014.[kod] = sank.[S_OSN] and f014.DATEBEG<=isnull(sank.DATE_ACT,zSL.[DATE_Z_2]) and (f014.[DATEEND] is null or f014.[DATEEND] >= isnull(sank.DATE_ACT,zSL.[DATE_Z_2]))) AS [S_OSN]
,sank.[S_COM] AS [S_COM]
,sank.[S_IST] AS [S_IST]
,sank.[DATE_ACT] AS [DATE_ACT]
,sank.[NUM_ACT] AS [NUM_ACT]
,(select top 1 f006.[F006ControlTypeID] from [IES].[T_F006_CONTROL_TYPE] f006 where f006.[IDVID] = sank.[S_TIP] and f006.DATEBEG<=isnull(sank.DATE_ACT,zSL.[DATE_Z_2]) and (f006.[DATEEND] is null or f006.[DATEEND] >= isnull(sank.DATE_ACT,zSL.[DATE_Z_2]))) AS [S_TIP_F006]
,slid.[SchetSluchID] AS [SchetSluch]
FROM IES_TEMP_DB..TEMP_SANK_MTR sank
JOIN ies.T_SCHET_SLUCH_ACCOMPLISHED zSL ON (zSL.[IDCASE] = sank.[IDCASE])
JOIN ies.T_SCHET_ZAP sz ON sz.SchetZapID = zSL.SchetZap
outer apply(select top 1 sl.[SchetSluchID] from IES_TEMP_DB..TEMP_SANK_SL_ID_MTR slid JOIN ies.T_SCHET_SLUCH sl ON zSL.SchetSluchAccomplishedID = sl.SchetSluchAccomplished and slid.[SL_ID] = sl.[SL_ID] where zSL.[IDCASE] = slid.[IDCASE] and sank.[S_CODE] = slid.[S_CODE] and sank.[S_SUM] = slid.[S_SUM] order by sl.SUMV desc)slid
WHERE sz.Schet = @SchetID
and not exists(select * from ies.T_SCHET_SLUCH_SANK sss where sss.S_CODE = sank.S_CODE and sss.S_SUM = sank.S_SUM and sss.SchetSluchAccomplished = zSL.SchetSluchAccomplishedID)


INSERT INTO [IES].[T_SCHET_SLUCH_SANK_EXP](
[SchetSluchSankExpID], [SchetSluchSank], [F004Expert]
)
SELECT 
 NEWID() AS [SchetSluchSankExpID]
,sss.SchetSluchSankID AS [SchetSluchSank]
,codeexp.[CODE_EXP] AS [F004Expert]
FROM IES_TEMP_DB..TEMP_CODE_EXP_MTR codeexp  
JOIN IES_TEMP_DB..TEMP_SANK_MTR sank ON (sank.[IDCASE] = codeexp.[IDCASE] and sank.[S_CODE] = codeexp.[S_CODE])
JOIN [IES].[T_SCHET_SLUCH_SANK] sss ON sank.SchetSluchSankID = sss.SchetSluchSankID and sss.S_SUM = sank.S_SUM
JOIN ies.T_SCHET_SLUCH_ACCOMPLISHED zSL ON zSL.SchetSluchAccomplishedID = sss.SchetSluchAccomplished
JOIN ies.T_SCHET_ZAP sz ON sz.SchetZapID = zSL.SchetZap
WHERE sz.Schet = @SchetID
and not exists(select * from ies.T_SCHET_SLUCH_SANK_EXP sse where sse.F004Expert = codeexp.[CODE_EXP] and sse.SchetSluchSank = sss.SchetSluchSankID)

exec sp_SchetRecalc @SchetID

END