PreProcess sample

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author.
-- Create date: 19.1.2018
-- Description: 
--- In pending ERP batches, searches for ITEM lined to FLDR.
--- If ITEM is not found in the selected storage,
--- sets their status to IGNORED
-- =============================================
CREATE PROCEDURE EcIgnoredItemForSpace 
AS
BEGIN
     SET NOCOUNT ON;


     DECLARE @comm_dir BIT = 1;
     DECLARE @assoc_link INT = 30;
     DECLARE @status_pending INT = 0;
     DECLARE @status_ignore INT = 8;


     UPDATE EcENTITY SET comm_status = @status_ignore
     WHERE id IN (
        SELECT ITEM. id FROM EcASSOC AS ASSOC INNER JOIN
                (SELECT id, ent_class_id, name, comm_dir, comm_status FROM EcENTITY AS ENTITY
                  WHERE (comm_dir = @comm_dir) AND (comm_status = @status_pending) AND (ent_class_id = N'ITEM')) AS ITEM
                          ON ASSOC. ent_id_child = ITEM. id LEFT OUTER JOIN
                (SELECT DISTINCT ItemNumber FROM [STORE]. dbo. vw_ItemIteration) AS ITER
                          ON ITEM. name = ITER. ItemNumber COLLATE Czech_CI_AS
         WHERE (ASSOC. ent_id_parent IN (
                     SELECT id FROM EcENTITY AS FLDR
                 WHERE (comm_dir = @comm_dir) AND (comm_status = @status_pending) AND (ent_class_id = N'FLDR')) AND (ITER. ItemNumber IS NULL) AND (ASSOC. assoc_type = @assoc_link)
          )
END
GO