ALTER proc [dbo].[SALES_WRITEBACK_INVEST_COUNT_ALL]
 @HIERARCHY_CC_ID TINYINT,
 @PROC_ID BIGINT
 AS
SET XACT_ABORT ON;
BEGIN TRANSACTION
DECLARE @SESS_NO INT, @STEP_ID TINYINT, @INSERT_NUMBER_ROWS INT = 0, @RESULT_STRING NVARCHAR(MAX),  @REZ INT

exec [dbo].[REGISTRY_NEW_STEP] N'Transform Opening plan.SALES', @PROC_ID, @SESS_NO OUTPUT, @STEP_ID OUTPUT /*1*/

SET XACT_ABORT OFF;

begin try /*2*/
--TRUNCATE TABLE ODI_OUT.dbo.T_INVEST_COUNT_ALL 
DELETE FROM ODI_OUT.dbo.T_INVEST_COUNT_ALL

INSERT 
  INTO ODI_OUT.dbo.T_INVEST_COUNT_ALL
  (
    MONTH_ID ,
    MVZ_ID ,
    CFO_ID ,
    FRMT_ID ,
    SUBFRMT_ID ,
    ACCOUNT_ID ,
    HYPE_VERSION_ID ,
    DATA ,
    SESSION_ID 
  ) 
SELECT 1,1,1,1,1,1,1,1,@SESS_NO
  
SET @INSERT_NUMBER_ROWS = @@ROWCOUNT

select  @REZ = case 
	  when  T1.SUM_TARGET +  T2.SUM_SOURCE>0 then ROUND(T1.SUM_TARGET-T2.SUM_SOURCE, 0, 1)
	  else -1  
	  end 
FROM
(SELECT SUM(DATA) as SUM_TARGET from ODI_OUT.dbo.T_INVEST_COUNT_ALL where session_id = @SESS_NO ) T1,
(SELECT SUM(DATA) as SUM_SOURCE from dbo.T_SALES_OUT_STAGE where session_id = @SESS_NO) T2



IF  @REZ <> 0  
THROW 51000, N'Transform with errors. Summs did not match', 1;


end try
begin catch
 select @RESULT_STRING = cast(ERROR_LINE() as nvarchar(20)) + ': ' + ERROR_MESSAGE()   
end catch

SET XACT_ABORT ON;

EXEC DBO.UPDATE_STEP_DETAILS @SESS_NO, @STEP_ID, @INSERT_NUMBER_ROWS, @RESULT_STRING /*3*/

COMMIT TRANSACTION
GO

ALTER PROC [dbo].[REGISTRY_NEW_STEP] --создание нового шага (записи)  в таблице лога
@STEP_NAME NVARCHAR(100),
@PROC_ID BIGINT,
@SESS_NO BIGINT OUTPUT,
@STEP_ID TINYINT OUTPUT
AS
BEGIN
DECLARE @LAST_PROC_ID BIGINT
SELECT @LAST_PROC_ID = MAX(PROC_ID) FROM [dbo].[T_SESSION_REGISTRY]
SET @LAST_PROC_ID = COALESCE(@PROC_ID, @LAST_PROC_ID)
SELECT @SESS_NO = MAX(SESSION_ID) FROM [dbo].[T_SESSION_REGISTRY] WHERE PROC_ID = @LAST_PROC_ID
SELECT @STEP_ID = coalesce(MAX(STEP_ID),0)+1 FROM  DBO.T_SESSION_DETAILS WHERE SESSION_ID = @SESS_NO
INSERT INTO DBO.T_SESSION_DETAILS(SESSION_ID, STEP_ID, STEP_NAME, STEP_START, PROC_ID)VALUES(@SESS_NO, @STEP_ID, @STEP_NAME, GETDATE(), @LAST_PROC_ID)
END
GO

ALTER PROC [dbo].[UPDATE_STEP_DETAILS] -- обновление инфы по шагу
@SESS_NO BIGINT,
@STEP_ID TINYINT,
@CNT_ROWS INT,
@ER_MSG NVARCHAR(MAX)
AS
BEGIN
--UPDATE LAST STEP 
UPDATE  DBO.T_SESSION_DETAILS
SET STEP_END = GETDATE(), NUMBER_ROWS = @CNT_ROWS, ERROR_MSG = @ER_MSG
WHERE SESSION_ID = @SESS_NO AND STEP_ID = @STEP_ID
END
GO

TRUNCATE TABLE ODI_OUT.dbo.T_SALES_PLAN
print xact_state()